SELECT XI.sequence,
       XI.x_invoice_id,
       SELECTED = XI.dropship,
       XI.dropship,
       XI.item_code,
       XI.quote_vend,
       Isnull(C.onhold, 'F')      ONHOLD,
       INV.terms,
       INV.currcode,
       BALANCE = INV.inv_amount - INV.ship_amt - INV.paid_amt,
       BALANCE_FEX = INV.inv_amt_fex - INV.ship_amt_fex - INV.pd_amt_fex,
       DESCRIPT = CASE XI.desc_type
                    WHEN 1 THEN I.descript
                    ELSE XI.note
                  END,
       DUMMYFRACTION = CASE
                         WHEN M.entry_type = 1 THEN Floor(1)
                         ELSE
                           CASE
                             WHEN Isnull(M.sub_meas, '') = '' THEN Floor(1)
                             ELSE Floor(MF.conv_factor)
                           END
                       END,
       FRACTION = I.min_needed,
       REQUIRED1 = ( CASE
                       WHEN serialize = 'T' THEN XI.item_qty - (
                                                 (SELECT Isnull(Count(
                                                         serial_no), 0
                                                         )
                                                  FROM   inventor
                                                  WHERE  unique_id =
                                                         x_invoice_id
                                                         AND docnum =
                                                         XI.order_no
                                                         AND
                                                 type = XI.status)
                                                 + XI.qty_ship )
                       ELSE dbo.Convert_measure_qty((
                                   dbo.Convert_measure_qty(
                                   Isnull(XI.item_qty, 0),
                                   Isnull(MF.conv_factor,
       1), 1,
       Isnull(MF1.conv_factor, 1), 1) -
(
dbo.Get_reqd_qty_for_dropship(x_invoice_id, XI.status, XI.order_no, Isnull(MF.conv_factor, 1), Isnull
(MF1.conv_factor, 1))
+ dbo.Convert_measure_qty(Isnull(XI.qty_ship, 0), Isnull(MF.conv_factor,
1), 1
, Isnull(MF1.conv_factor, 1), 1) ) ), 1,
Isnull(MF.conv_factor, 1), Isnull(MF2.conv_factor, 1), 0)
END ),
DUE_DATE = Isnull(Isnull(XI.due_date, INV.estdate), INV.order_date),
INV.order_date,
INV.ship_code,
VENDNAME = I.video,
VENDNAME1 = CASE
              WHEN Isnull(XI.quote_vend, '') = '' THEN
              IR.vend_code + ':' + Isnull(V.NAME, '')
              ELSE XI.quote_vend + ':' + Isnull(V.NAME, '')
            END,
V.shipcode,
REQUIRED = item_qty,
COST = quote_cost,
PURC_FRAC = M.dec_accuracy,
M.entry_type,
INV.deliv_meth,
INV.po,
INV.po_date,
INV.estdate,
XI.order_no,
INV.cust_code,
TOTALSTOCK = XI.item_qty,
AVAILABLE = XI.item_qty,
TOTALSTOCK1 = dbo.Convert_measure_qty(
                            dbo.Convert_measure_qty(Isnull(qty_stk, 0),
                            Isnull(MF.conv_factor, 1), 1,
                            Isnull(MF1.conv_factor, 1), 1) -
(
dbo.Convert_measure_qty(Isnull(q_on_reserve_comm, 0), Isnull(MF.conv_factor, 1), 1, Isnull(MF1.conv_factor, 1), 1)
+
dbo.Convert_measure_qty(Isnull(q_on_rma_comm, 0), Isnull(MF.conv_factor, 1), 1, Isnull(MF1.conv_factor, 1), 1) ), 1, Isnull(MF.conv_factor, 1),
Isnull(MF2.conv_factor, 1), 0),
AVAILABLE1 = dbo.Convert_measure_qty(
                          dbo.Convert_measure_qty(Isnull(qty_stk, 0),
                          Isnull(MF.conv_factor, 1), 1,
                          Isnull(MF1.conv_factor, 1), 1) -
(
dbo.Convert_measure_qty(Isnull(q_on_reser, 0), Isnull(MF.conv_factor, 1), 1, Isnull(MF1.conv_factor, 1), 1)
  +
dbo.Convert_measure_qty(Isnull(q_on_rma, 0), Isnull(MF.conv_factor, 1), 1, Isnull(MF1.conv_factor, 1), 1) ), 1, Isnull(MF.conv_factor, 1),
             Isnull(MF2.conv_factor, 1), 0),
I.q_on_reser,
I.q_on_rma,
I.q_on_order,
I.q_on_credi,
I.category,
SALE_MEAS = XI.meas_code,
I.inventored,
I.serialize,
I.autoserial,
MIN_NEEDED = IL.min_quantity,
SALE_WEIGHT = IM.weight,
IM.sellpric,
LASTCOST = IM1.last_cost,
ILASTCOST = I.last_cost,
I.purcprice_dec_accuracy,
Isnull(MF3.conv_factor, 1) CONV_FACTOR,
I.avg_cost,
I.brand,
I.model,
XI.part_no,
IM.markup,
IR.vend_code
FROM   x_invoic XI WITH(nolock)
       INNER JOIN invoices INV WITH(nolock)
               ON XI.order_no = INV.doc_no
                  AND XI.status = INV.status
       INNER JOIN cust C WITH(nolock)
               ON C.cust_code = INV.cust_code
       INNER JOIN items I WITH(nolock)
               ON XI.item_code = I.itemno
       INNER JOIN measure M WITH(nolock)
               ON XI.meas_code = M.code
       LEFT OUTER JOIN item_replenish_vendor IR WITH(nolock)
                    ON IR.item_code = XI.item_code
                       AND IR.primary_vendor = 'T'
       LEFT OUTER JOIN vendors V WITH(nolock)
                    ON V.vend_code = CASE
                                       WHEN Isnull(XI.quote_vend, '') = '' THEN
                                       IR.vend_code
                                       ELSE XI.quote_vend
                                     END
       LEFT OUTER JOIN measure_factor MF WITH(nolock)
                    ON MF.meas_code = M.code
                       AND MF.conv_meas_code = M.sub_meas
       LEFT OUTER JOIN measure_factor MF1 WITH(nolock)
                    ON MF1.meas_code = M.code
                       AND MF1.conv_meas_code = M.least_sub_meas
       LEFT OUTER JOIN measure_factor MF2 WITH(nolock)
                    ON MF2.meas_code = M.least_sub_meas
                       AND MF2.conv_meas_code = M.code
       LEFT OUTER JOIN measure_factor MF3 WITH(nolock)
                    ON MF3.meas_code = XI.meas_code
                       AND MF3.conv_meas_code = I.stock_meas
       LEFT OUTER JOIN item_measure IM WITH(nolock)
                    ON XI.item_code = IM.itemno
                       AND XI.meas_code = IM.meas_code
                       AND IM.meas_type = 1
       LEFT OUTER JOIN item_measure IM1 WITH(nolock)
                    ON XI.item_code = IM1.itemno
                       AND XI.meas_code = IM1.meas_code
                       AND IM1.meas_type = 2
       LEFT OUTER JOIN item_replenish_depart IL WITH(nolock)
                    ON IL.item_code = XI.item_code
                       AND IL.depart = 'MAIN'
WHERE  XI.status = 8
       AND Isnull(XI.drop_ship_id, 0) <= 0
       AND INV.shipped <> 'T'
       AND Isnull(XI.item_code, '') <> ''
       AND XI.service_type = 'F'
       AND matrix_item_type <> 3
       AND Isnull(XI.dropship, 'F') = 'F'
ORDER  BY XI.order_no,
          sequence