Query to Fetch Invoice details, HSN Code, Third party Registration number and GST Details.

The below query fetches the details of an Invoice along with the HSN Code of  a respective item present in an Invoice. GST details can also be fetched.


SELECT a.org_id,a.customer_trx_id,a.trx_number invno,a.trx_date, c.trx_line_id,
                         a.cust_trx_type_id,a.bill_to_customer_id,a.bill_to_site_use_id, b.line_number AS linenum,
                         b.inventory_item_id,d.segment1 itm,b.interface_line_attribute6 ,
                         SUBSTR(b.description,1,40) description1,SUBSTR(b.description,40,55)  description2,
                         (SELECT SUBSTR (REPLACE(f.template_name,' ',''),4,11) FROM jai_item_templ_hdr e,
                                                       jai_item_templ_hdr f
                                                 WHERE e.inventory_item_id = b.inventory_item_id
                                                   AND e.entity_id = f.template_hdr_id AND ROWNUM =1) hsn_code,
                          (SELECT SUBSTR (REPLACE(f.template_name,' ', ''),12,18) FROM jai_item_templ_hdr e,
                                                       jai_item_templ_hdr f
                                                 WHERE e.inventory_item_id = b.inventory_item_id
                                                   AND e.entity_id = f.template_hdr_id AND ROWNUM =1) hsn_code1,                       
                         b.uom_code,a.primary_salesrep_id,b.sales_order_line,a.interface_header_attribute1 ordernum,
                         TO_NUMBER(a.interface_header_attribute10) organization_id,B.quantity_invoiced,
                         b.unit_standard_price sprice,b.unit_selling_price,b.revenue_amount,b.extended_amount,
                         SUM(c.actual_tax_rate)tax_rate,SUM(c.unround_tax_amt_fun_curr)tax_amount,b.line_type,b.line_number,
--              DECODE(TAX_AMOUNT,0,0,null,0,0.00,0,VAT_ASSESSABLE_VALUE) taxgross,
                         NULL taxgross,warehouse_id, SUBSTR(NVL(d.attribute5,'   '),1,3) AS sch_category
               FROM      ra_customer_trx_all a,
                         ra_customer_trx_lines_all b,
                         jai_tax_lines_all c,
                         (SELECT * FROM mtl_system_items WHERE organization_id = 83) d
                 WHERE    a.customer_trx_id          = b.customer_trx_id
                   AND    a.trx_number                     = :p_trx_number
--                         AND b.interface_line_attribute6= c.line_id
                   AND    d.inventory_item_id               = b.inventory_item_id
                   AND    a.customer_trx_id                   = c.trx_id           
                   AND    b.customer_trx_line_id           = c.trx_line_id       
                   AND    b.line_type                              = 'LINE'
--                  AND a.trx_number = '11111700004'
                  AND a.interface_header_attribute1 IS NOT NULL
       GROUP BY a.org_id,a.customer_trx_id,a.trx_number ,a.trx_date, c.trx_line_id,
                         a.cust_trx_type_id,a.bill_to_customer_id,a.bill_to_site_use_id, b.line_number,
                         b.inventory_item_id,d.segment1,b.description,b.uom_code,B.quantity_invoiced,
                         b.unit_standard_price,b.unit_selling_price,b.revenue_amount,b.extended_amount,
                         c.actual_tax_rate,c.unround_tax_amt_fun_curr,b.interface_line_attribute6,
                         a.primary_salesrep_id,b.sales_order_line,a.interface_header_attribute1,a.interface_header_attribute10,
                         b.line_type,b.line_number,warehouse_id,d.attribute5



Query to fetch Third Party Registration Number(GST Number) :-


SELECT DISTINCT TRIM(TO_CHAR(B.registration_number))GST_NUM
       INTO  mcustvatno
       FROM  jai_party_regs a,
             jai_party_reg_lines b,
             ra_customer_trx_all rct,
             hz_cust_accounts bill_cus,
             hz_parties bill_party,
             hz_cust_site_uses_all hcs_bill,
             hz_cust_acct_sites_all hca_bill,
             hz_party_sites hps_bill,
             hz_locations hl_bill
    WHERE    1 = 1
      AND    a.party_reg_id                 = b.party_reg_id
      AND    a.party_type_code            = 'THIRD_PARTY_SITE'
      AND    b.regime_id                       = 10000
      AND    b.registration_type_code   = 'GST'
      AND    a.party_id                          = rct.bill_to_customer_id
      AND    rct.bill_to_customer_id    = bill_cus.cust_account_id
      AND    bill_party.party_id            = bill_cus.party_id
      AND    rct.bill_to_site_use_id      = hcs_bill.site_use_id
      AND    hcs_bill.site_use_code      = 'BILL_TO'
      AND    hca_bill.cust_acct_site_id = hcs_bill.cust_acct_site_id
      AND    hps_bill.party_site_id     =  hca_bill.party_site_id
      AND    a.party_site_id                =  hca_bill.cust_acct_site_id
      AND    hl_bill.location_id          =  hps_bill.location_id
      AND    rct.customer_trx_id        = :p_customer_trx_id;



Note:    Similarly we can get First Party Registration Number. In the above query replace the party_type_code as 'I/O' which will fetch you first party registration number.

Return To Vendor Summay-GST

The below query fetches the data of purchase order wherein those items present in purchase order needs to be returned to Supplier because of some damage caused to goods etc..

SELECT rch.shipment_header_id,TRUNC(RCH.Creation_Date)Rec_Date,  rcl.shipment_line_id,

       RCH.receipt_num,RCT.organization_id,

       RCH.Attribute4 DC_No,RCH.Attribute6 DC_Date,NVL(RCL.QUANTITY_SHIPPED,0)Recd_Qty ,

       (RCT.Quantity)Returned_qty,(RCT.Transaction_date)Return_date ,

       RCT.unit_of_measure,RCT.vendor_id,RCT.Reason_id,RCT.Vendor_id,

--Lpad(RCT.ATTRIBUTE7,4,0)Del_Slip_no,

       RCT.ATTRIBUTE7 Del_Slip_no,

       RCL.item_id,RCL.item_Description||' '||hsnn.template_name item_Description, B.Segment1,VEN.Vendor_Name,LPAD(VEN.Segment1,5,0) Vendor_Code ,

       (RCT.Attribute5)No_Of_Pack,(RCT.ATTRIBUTE6)Qty_Per_Pack ,MTR.Description  Reason,

       SIT.vendor_site_id,SIT.vendor_site_code,SIT.Address_line1,SIT.Address_line2,SIT.Address_line3,SIT.city,SIT.state,SIT.zip,(

       SELECT jprl.registration_number

  FROM jai_party_regs jpr, ja.jai_party_reg_lines jprl

 where 1 = 1

   and party_id = SIT.vendor_id

   and party_site_id = SIT.vendor_site_id

   and jpr.party_reg_id = jprl.party_reg_id

   and jprl.regime_id=(SELECT jrc.regime_id

                       FROM JA.jai_regimes jrc

                       WHERE jrc.regime_code = 'GST'

                      )

                      )"GST Registration Number",

   RCT.PO_UNIT_PRICE, sum(jtla.ACTUAL_TAX_RATE) "GST%", (RCT.Quantity)*RCT.PO_UNIT_PRICE "RTV Value", sum(UNROUND_TAX_AMT_TAX_CURR) "Tax Amount"             

FROM rcv_shipment_headers    RCH,

     rcv_shipment_lines      RCL,

     rcv_transactions        RCT,

     mtl_system_items        B,

     HR_ORGANIZATION_UNITS_V HR ,

     po_vendors              VEN,

     po_vendor_sites_all SIT ,

     mtl_transaction_reasons MTR,

     jai_item_templ_hdr jith,

     jai_item_templ_hdr hsnn,

     jai_tax_lines_all jtla

WHERE RCH.shipment_header_id = RCL.shipment_header_id

AND   RCT.shipment_header_id = RCL.shipment_header_id

AND   RCH.shipment_header_id = RCT.shipment_header_id

AND   RCL.SHIPMENT_LINE_ID = RCT.SHIPMENT_LINE_ID

AND  TRUNC(RCT.CREATION_DATE)  BETWEEN :P_FROM_DT  AND   :P_TO_DATE

AND  RCT.ORGANIZATION_ID = :P_ORG_ID

--AND RCT.ATTRIBUTE7 BETWEEN NVL(:P_DC_NUM ,RCT.ATTRIBUTE7) AND NVL(:P_DC_NUM1 ,RCT.ATTRIBUTE7)

AND  RCL.item_id  =   B.Inventory_Item_id

AND  RCT.transaction_type = 'RETURN TO VENDOR'

AND  RCL.TO_ORGANIZATION_ID =B.ORGANIZATION_ID

AND  RCT.VENDOR_ID = VEN.vendor_id

AND  RCT.VENDOR_ID = SIT.vendor_id

AND rct.vendor_site_id=sit.vendor_site_id   

AND  RCH.receipt_source_code  ='VENDOR'

AND  RCT.organization_id = HR.Organization_id

AND  RCH.SHIP_TO_ORG_ID= HR.Organization_id

AND  RCT.reason_id = MTR.Reason_id (+)

AND  sit.inactive_date IS NULL

and  jith.inventory_item_id(+) =  B.Inventory_Item_id

and    jith.entity_type_code = 'ITEM_TEMPL_ASGN'

and    jith.entity_id = hsnn.template_hdr_id

and    hsnn.entity_type_code = 'ITEM_TEMPL'

--and    jith.organization_id = RCH.SHIP_TO_ORG_ID

and    jtla.entity_code = 'RCV_TRANSACTION'

--and    rch.shipment_header_id = rct.shipment_header_id

--AND    jtla.organization_id = rch.ship_to_org_id

--and    jtla.trx_number = rch.receipt_num

AND    jtla.trx_loc_line_id(+) = rct.transaction_id

AND jtla.trx_id(+) = rct.shipment_header_id

AND jtla.trx_line_id(+) = rct.shipment_line_id

GROUP BY SIT.vendor_id, hsnn.template_name, RCT.PO_UNIT_PRICE,

         rch.shipment_header_id,TRUNC(RCH.Creation_Date),

         RCH.receipt_num, RCT.organization_id,

         RCH.Attribute4 ,RCH.Attribute6,(RCT.Transaction_date),

         (RCT.Attribute5) ,(RCT.ATTRIBUTE6) ,RCT.ATTRIBUTE7,MTR.Description ,

          RCT.unit_of_measure,RCT.vendor_id,RCT.Reason_id,RCT.Vendor_id,

         RCL.item_id,RCL.item_Description,B.Segment1,VEN.Vendor_Name,LPAD(VEN.Segment1,5,0) ,

         SIT.vendor_site_id,SIT.vendor_site_code,SIT.Address_line1,SIT.Address_line2,SIT.Address_line3,SIT.city,SIT.state,SIT.zip, RCT.Quantity, RCL.QUANTITY_SHIPPED, rcl.shipment_line_id

ORDER BY RCH.receipt_num

Delivery Challan with GST Details

Query retrieves data of those inventory items which are used for promotional purposes or given as a sample items for a sales executive in order to promote the products along with the GST Details(GSTIN Number, etc...)

SELECT dcno, dcdate, description, to_organization, transport, lrno, lrdate, shippers, subinventory_code, qty,attribute10,

transaction_uom, transaction_cost,actual_cost, appscode, item_description, lot_number, mfgdt, expdt, to_org, to_add1, to_add2,

to_add3, to_city, to_pin, to_state, to_dl1, to_dl2, fm_org, fm_add1, fm_add2, fm_add3, fm_city, fm_pin, fm_state,

fm_dl1, fm_dl2, fm_lst_no, fm_cst_no, to_lst_no, to_cst_no,(actual_cost* qty) value,

(select registration_number from jai_party_regs a, jai_party_reg_lines b, hr_locations c

where 1=1

and a.party_reg_id = b.party_reg_id

and a.party_type_code = 'IO'

and b.regime_id = 10000

and b.registration_type_code = 'GST'

and a.party_id  = c.inventory_organization_id

and a.party_site_id = c.location_id

and c.inventory_organization_id = :orgid)gst_in,hsn_code

from

(select a.header_id as dcno,  trunc(b.transaction_date) as dcdate, a.description, 

a.attribute1 as to_organization, a.attribute15 as transport, substr(a.attribute2,1,15) as lrno,

substr(a.attribute3,1,15) as lrdate, nvl(to_number(attribute4),0) as shippers,

b.subinventory_code, (d.transaction_quantity * -1) as qty, b.transaction_uom,b.transaction_cost, b.actual_cost,a.attribute10,

c.segment1 as appscode, c.description as item_description,

e.lot_number, (e.expiration_date - c.shelf_life_days) as mfgdt, e.expiration_date as expdt,

f.location_code as to_org, f.address_line_1 as to_add1, f.address_line_2 as to_add2, f.address_line_3 as to_add3,

f.town_or_city as to_city, f.postal_code as to_pin, f.region_1 as to_state,

f.loc_information14 as to_dl1, f.loc_information16 as to_dl2,

g.location_code as fm_org, g.address_line_1 as fm_add1, g.address_line_2 as fm_add2, g.address_line_3 as fm_add3,

g.town_or_city as fm_city, g.postal_code as fm_pin, g.region_1 as fm_state,

g.loc_information14 as fm_dl1, g.loc_information16 as fm_dl2,

h.st_reg_no as fm_lst_no, h.cst_reg_no as fm_cst_no,

to_lst_no, to_cst_no,

(SELECT SUBSTR (REPLACE(f.template_name,' ',''),4,15) FROM jai_item_templ_hdr e,

                                                       jai_item_templ_hdr f

                                                 WHERE 1=1

                                                   AND e.inventory_item_id = b.inventory_item_id

                                                   AND e.entity_id = f.template_hdr_id

                                                   AND e.entity_type_code = 'ITEM_TEMPL_ASGN'

                                                   AND e.organization_id = :orgid

                                                   AND ROWNUM =1)hsn_code

from mtl_txn_request_headers a,

(select transaction_id, transaction_source_id, inventory_item_id, subinventory_code,  transaction_date,

transaction_quantity, transaction_uom, transaction_cost,actual_cost,attribute10

from mtl_material_transactions

where organization_id = :orgid

and transaction_type_id = 63) b,

(select segment1, description, inventory_item_id, shelf_life_days from mtl_system_items

where organization_id = :orgid) c,

(select transaction_id, transaction_source_id, inventory_item_id, lot_number, transaction_quantity

from mtl_transaction_lot_numbers where organization_id = :orgid) d,

(select inventory_item_id, lot_number, expiration_date

from mtl_lot_numbers

where organization_id = :orgid) e,

(select location_id, location_code, address_line_1, address_line_2, address_line_3, town_or_city, postal_code, region_1,

loc_information14, loc_information16

from hr_locations) f,

(select location_code, address_line_1, address_line_2, address_line_3, town_or_city, postal_code, region_1,

loc_information14, loc_information16, inventory_organization_id

from hr_locations) g,

(select organization_id, st_reg_no, cst_reg_no

from jai_cmn_inventory_orgs  

where location_id = 0) h,

(select location_id, organization_id, st_reg_no as to_lst_no, cst_reg_no as to_cst_no

from jai_cmn_inventory_orgs ) i 

where a.organization_id = :orgid

and a.header_id between :fdcno and :tdcno

and a.transaction_type_id = 63

and a.header_id = b.transaction_source_id

and b.inventory_item_id = c.inventory_item_id

and d.transaction_id = b.transaction_id

and d.transaction_source_id = a.header_id

and d.inventory_item_id = e.inventory_item_id

and d.lot_number = e.lot_number

and ltrim(a.attribute1) = ltrim(f.location_code)

and a.organization_id = g.inventory_organization_id

and h.organization_id = a.organization_id

and f.location_id = i.location_id

union

select a.header_id as dcno,  b.transaction_date as dcdate, a.description, 

a.attribute1 as to_organization, a.attribute15 as transport, a.attribute2 as lrno, a.attribute3 as lrdate, to_number(a.attribute4) as shippers,

b.subinventory_code, (d.transaction_quantity * -1) as qty, b.transaction_uom, b.transaction_cost,b.actual_cost,a.attribute10,

c.segment1 as appscode, c.description as item_description,

e.lot_number, (e.expiration_date - c.shelf_life_days) as mfgdt, e.expiration_date as expdt,

f.location_code as to_org, f.address_line_1 as to_add1, f.address_line_2 as to_add2, f.address_line_3 as to_add3,

f.town_or_city as to_city, f.postal_code as to_pin, f.region_1 as to_state,

f.loc_information14 as to_dl1, f.loc_information16 as to_dl2,

g.location_code as fm_org, g.address_line_1 as fm_add1, g.address_line_2 as fm_add2, g.address_line_3 as fm_add3,

g.town_or_city as fm_city, g.postal_code as fm_pin, g.region_1 as fm_state,

g.loc_information14 as fm_dl1, g.loc_information16 as fm_dl2,

h.st_reg_no as fm_lst_no, h.cst_reg_no as fm_cst_no,

' ' to_lst_no, ' ' to_cst_no,

(SELECT SUBSTR (REPLACE(f.template_name,' ',''),4,15) FROM jai_item_templ_hdr e,

                                                       jai_item_templ_hdr f

                                                 WHERE 1=1

                                                   AND e.inventory_item_id = b.inventory_item_id

                                                   AND e.entity_id = f.template_hdr_id

                                                   AND e.entity_type_code = 'ITEM_TEMPL_ASGN'

                                                   AND e.organization_id = :orgid

                                                   AND ROWNUM =1)hsn_code

from mtl_txn_request_headers a,

(select transaction_id, transaction_source_id, inventory_item_id, subinventory_code,  transaction_date,

transaction_quantity, transaction_uom, transaction_cost,actual_cost,attribute10

from mtl_material_transactions

where organization_id = :orgid

and transaction_type_id = 63) b,

(select segment1, description, inventory_item_id, shelf_life_days from mtl_system_items

where organization_id = :orgid) c,

(select transaction_id, transaction_source_id, inventory_item_id, lot_number, transaction_quantity

from mtl_transaction_lot_numbers where organization_id = :orgid) d,

(select inventory_item_id, lot_number, expiration_date

from mtl_lot_numbers

where organization_id = :orgid) e,

(select location_id, location_code, address_line_1, address_line_2, address_line_3, town_or_city, postal_code, region_1,

loc_information14, loc_information16, inventory_organization_id, object_version_number

from hr_locations) f,

(select location_code, address_line_1, address_line_2, address_line_3, town_or_city, postal_code, region_1,

loc_information14, loc_information16, inventory_organization_id

from hr_locations) g,

(select organization_id, st_reg_no, cst_reg_no

from jai_cmn_inventory_orgs  

where location_id = 0) h

where a.organization_id = :orgid

and a.header_id between :fdcno and :tdcno

and a.transaction_type_id = 63

and a.header_id = b.transaction_source_id

and b.inventory_item_id = c.inventory_item_id

and d.transaction_id = b.transaction_id

and d.transaction_source_id = a.header_id

and d.inventory_item_id = e.inventory_item_id

and d.lot_number = e.lot_number

and ltrim(a.attribute1) = ltrim(f.location_code)

and a.organization_id = g.inventory_organization_id

and h.organization_id = a.organization_id

and f.inventory_organization_id is null)

order by dcno

Find the query of Receivable (AR) for the Invoice Number (TRX_NUMBER) Wise, Customer wise, Sales Order Wise, Transaction Date and GL Date Wise in Oracle Apps EBS R12

Find the query of Receivable (AR) for the Invoice Number (TRX_NUMBER) Wise,
Customer wise, Sales Order Wise, Transaction Date and
GL Date Wise in Oracle Apps EBS R12.

SELECT   rct.trx_number invoice_number,
                                       -- ARPS.AMOUNT_DUE_ORIGINAL BALANCE,
                                       arps.amount_due_remaining balance,
         hp.party_name bill_to_customer,
         DECODE (rctt.TYPE,
                 'CB', 'Chargeback',
                 'CM', 'Credit Memo',
                 'DM', 'Debit Memo',
                 'DEP', 'Deposit',
                 'GUAR', 'Guarantee',
                 'INV', 'Invoice',
                 'PMT', 'Receipt',
                 'Invoice'
                ) invoice_class,
         rct.invoice_currency_code currency, rct.trx_date inv_date,
         rctd.gl_date gl_date, (SELECT NAME
                                  FROM apps.ra_terms rat
                                 WHERE rat.term_id = rct.term_id) terms,
         rctt.NAME order_type
    FROM ra_customer_trx_all rct,
         -- RA_CUSTOMER_TRX_LINES_ALL      RCTL,
         ra_cust_trx_line_gl_dist_all rctd,
         hz_parties hp,
         hz_cust_accounts_all hca,
         ra_cust_trx_types_all rctt,
         hr_operating_units hou,
         ar_payment_schedules_all arps
   WHERE rct.customer_trx_id = rctd.customer_trx_id
--AND RCT.CUSTOMER_TRX_ID       = RCTL.CUSTOMER_TRX_ID
--AND RCTL.CUSTOMER_TRX_LINE_ID = RCTD.CUSTOMER_TRX_LINE_ID
     AND rct.bill_to_customer_id = hca.cust_account_id
     AND hp.party_id = hca.party_id
     AND rct.cust_trx_type_id = rctt.cust_trx_type_id
     AND rct.org_id = rctt.org_id
     AND rct.org_id = hou.organization_id
     AND arps.customer_trx_id(+) = rct.customer_trx_id
     AND rct.org_id = NVL (:p_org_id, rct.org_id)
     AND rct.trx_number = NVL (:trx_number, rct.trx_number)
                                                           -- KAL/2017/DE/1114
     AND rct.trx_date BETWEEN NVL (:p_trx_date_from, rct.trx_date)
                          AND NVL (:p_trx_date_to, rct.trx_date)
     AND rctd.gl_date BETWEEN NVL (:p_gl_date_from, rctd.gl_date)
                          AND NVL (:p_gl_date_to, rctd.gl_date)
     AND hp.party_name = NVL (:p_cust_name, hp.party_name)
     AND NVL (rct.ct_reference, 'XX') =
                         NVL (NVL (:p_sales_order_no, rct.ct_reference), 'XX')
GROUP BY rct.trx_number,
         -- ARPS.AMOUNT_DUE_ORIGINAL,
         arps.amount_due_remaining,
         hp.party_name,
         rctt.TYPE,
         rct.invoice_currency_code,
         rct.trx_date,
         rctd.gl_date,
         rct.term_id,
         rctt.NAME;

Query to get transactions of inventory items with available onhand quantity

SELECT   recs.doc_type, recs.item_number, recs.doc_no, recs.po_number,
         recs.organization_id, recs.inventory_item_id, recs.primary_quantity,
         recs.transaction_date
    FROM (
/* receipt transactions*/
          SELECT '2-REC' doc_type, msi.segment1 item_number,
                 rsh.receipt_num doc_no, poh.segment1 po_number,
                 mmt.organization_id, mmt.inventory_item_id,
                 TO_NUMBER (NVL (mmt.primary_quantity, 0)) primary_quantity,
                 TRUNC (rt.transaction_date) transaction_date
            FROM inv.mtl_material_transactions mmt,
                 inv.mtl_system_items_b msi,
                 po.rcv_transactions rt,
                 po.rcv_shipment_headers rsh,
                 po.po_headers_all poh
           WHERE mmt.transaction_type_id IN (,,,) --Enter your transaction type ids
             AND mmt.inventory_item_id = msi.inventory_item_id
             AND mmt.organization_id = msi.organization_id
             AND mmt.primary_quantity > 0
             AND mmt.rcv_transaction_id = rt.transaction_id
             AND rt.shipment_header_id = rsh.shipment_header_id
             AND rt.po_header_id = poh.po_header_id
          UNION ALL
/*production transactions*/
          SELECT '1-PROD' doc_type, msi.segment1 item_number,
                 we.wip_entity_name doc_no, '' po_number, mmt.organization_id,
                 mmt.inventory_item_id,
                 NVL (mmt.primary_quantity, 0) primary_quantity,
                 TRUNC (mmt.transaction_date) transaction_date
            FROM inv.mtl_material_transactions mmt,
                 inv.mtl_system_items_b msi,
                 wip.wip_discrete_jobs jobs,
                 wip.wip_entities we
           WHERE mmt.transaction_type_id IN (,,,) --Enter your transaction type ids
             AND mmt.inventory_item_id = msi.inventory_item_id
             AND mmt.organization_id = msi.organization_id
             AND mmt.transaction_source_id = jobs.wip_entity_id
             AND mmt.inventory_item_id = jobs.primary_item_id
             AND mmt.organization_id = jobs.organization_id
             AND jobs.wip_entity_id = we.wip_entity_id
             AND jobs.organization_id = we.organization_id
             AND mmt.primary_quantity > 0
/*adjustment transactions*/
          UNION ALL
          SELECT '3-ADJ' doc_type, msi.segment1 item_number,
                 TO_CHAR (mmt.transaction_id) doc_no, '' po_number,
                 mmt.organization_id, mmt.inventory_item_id,
                 TO_NUMBER (NVL (mmt.primary_quantity, 0)) primary_quantity,
                 TRUNC (mmt.transaction_date) transaction_date
            FROM inv.mtl_material_transactions mmt,
                 inv.mtl_system_items_b msi
           WHERE mmt.transaction_type_id IN (,,,) --Enter your transaction type ids
             AND mmt.inventory_item_id = msi.inventory_item_id
             AND mmt.organization_id = msi.organization_id
             AND mmt.primary_quantity > 0) recs
   WHERE (SELECT SUM (motv.on_hand)
            FROM apps.mtl_onhand_total_v motv
           WHERE recs.inventory_item_id = motv.inventory_item_id
             AND recs.organization_id = motv.organization_id) > 0
     AND recs.organization_id = :p_org_id
     AND recs.inventory_item_id = :p_item_id

Query to get current onhand quantity of inventory Items.

SELECT ALL mtl.organization_id, orgs.NAME organization_name,
           mtl.inventory_item_id, mtl.segment1 item_number,
           mtl.description item_description, mtl.item_type,
           mtl.inventory_item_status_code, mtl.primary_uom_code,
           mtl.secondary_uom_code,
-- get current onhand
                      (  TO_NUMBER ((SELECT SUM (NVL (motv.on_hand, 0))
                            FROM apps.mtl_onhand_total_v motv
                           WHERE mtl.inventory_item_id =
                                                        motv.inventory_item_id
                             AND mtl.organization_id = motv.organization_id)
                        )
            + NVL ((SELECT -1 * SUM (NVL (primary_quantity, 0))
                                 FROM   mtl_material_transactions mmt
                     WHERE mmt.inventory_item_id = mtl.inventory_item_id
                       AND mmt.organization_id = mtl.organization_id
                       AND mmt.costed_flag IN ('N', 'E')
                       AND mmt.transaction_action_id NOT IN
                                                     (24, 40, 41, 50, 51, 52)
                       AND NVL (mmt.owning_tp_type, 2) = 2
                       AND mmt.organization_id =
                              NVL (mmt.owning_organization_id,
                                   mmt.organization_id
                                  )
                       AND NVL (mmt.logical_transaction, -1) <> 1),
                   0
                  )
           ) current_onhand  ,
           TO_NUMBER (NVL ((SELECT SUM (cict.item_cost)
                              FROM apps.cst_item_cost_type_v cict
                             WHERE mtl.inventory_item_id = cict.inventory_item_id(+)
                               AND mtl.organization_id = cict.organization_id(+)
                               AND cict.cost_type = 'Frozen'),
                           0
                          )
                     ) current_cost,
           DECODE (mtl.planning_make_buy_code,
                   2, 'Buy',
                   1, 'Make',
                   'Other'
                  ) make_buy_code,
           (SELECT organization_code
              FROM org_organization_definitions ood
             WHERE orgs.organization_id =
                                        ood.organization_id)
                                                            organization_code
      FROM apps.mtl_system_items_b mtl,
           apps.hr_all_organization_units orgs,
           apps.mtl_item_categories_v cat
     WHERE orgs.organization_id = mtl.organization_id
       AND (    mtl.inventory_item_id = cat.inventory_item_id(+)
            AND mtl.organization_id = cat.organization_id(+)
            AND cat.category_set_id = 1
           )
       AND (SELECT SUM (motv.on_hand)
              FROM apps.mtl_onhand_total_v motv
             WHERE mtl.inventory_item_id = motv.inventory_item_id
               AND mtl.organization_id = motv.organization_id) > 0;

Payroll Employer Costs Extract

SELECT SUM(peevf.screen_entry_value)
FROM pay_element_types_f petf
,pay_element_entry_values_f peevf
,pay_element_entries_f peef
,per_all_assignments_f paaf
,pay_input_values_f pivf
WHERE paaf.assignment_id = p_assignment_id
AND p_effective_date between paaf.effective_start_date and paaf.effective_end_date
AND peef.assignment_id = paaf.assignment_id
AND peef.effective_start_date
between trunc(p_effective_date,'YY') and p_effective_date
AND peef.effective_end_date is not null
AND peevf.element_entry_id = peef.element_entry_id
AND peevf.effective_start_date = peef.effective_start_date
AND peevf.input_value_id = pivf.input_value_id
AND pivf.name = 'Pay Value'
AND peevf.screen_entry_value IS NOT NULL
AND peevf.effective_start_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
AND petf.element_type_id = pivf.element_type_id
AND petf.element_name = 'Total Month Employer Cost'
AND pivf.effective_start_date BETWEEN petf.effective_start_date AND petf.effective_end_date;

AP Supplier Banks Payments Query

SELECT   pvs.org_id org_id, iep.default_payment_method_code, alc.lookup_code,
         hou.NAME org_name, pv.vendor_id supplier_id,
         pv.segment1 supplier_number, plc.description supplier_type,
         pv.vendor_name supplier_name,
         (CASE
             WHEN TRUNC (NVL (pv.end_date_active, SYSDATE + 1)) >=
                                                               TRUNC (SYSDATE)
                THEN 'Active'
             ELSE 'Inactive'
          END
         ) status,
         pvs.vendor_site_code site_name,
         (SELECT TO_CHAR (MAX (check_date), 'DD-MON-RRRR')
            FROM apps.ap_checks
           WHERE vendor_id = pv.vendor_id
             AND vendor_site_id = pvs.vendor_site_id) last_payment_date,
         pv.vat_registration_num tax_registration_number,
         fu.user_name updated_by,
         TO_CHAR (pvs.last_update_date, 'DD-MON-RRRR') update_date,
         pvs.address_line1 site_address1, pvs.address_line2 site_address2,
         pvs.address_line3 site_address3,
         pvs.address_lines_alt alternate_address, pvs.city city,
         pvs.state state, pvs.zip postal_code,
         pvs.area_code || ' ' || pvs.phone telephone,
         pvs.fax_area_code || ' ' || pvs.fax fax,
         pvs.email_address site_email, pvs.remittance_email remittance_email,
         pvs.supplier_notif_method notify_method, pvs.attribute13 remit_type,
         apt.NAME payment_terms, pvs.payment_currency_code currency,
         ieb.bank_account_name, ieb.bank_account_number bank_account_num,
         ieb.branch_number bank_branch_name,
         TO_CHAR (ipi.start_date, 'DD-MON-RRRR') effective_date,
         pvs.hold_unmatched_invoices_flag hold_unmatched_invoices,
         pvs.hold_future_payments_flag hold_unvalidated_invoices,
         pvs.hold_all_payments_flag hold_all_payments
    FROM apps.ap_suppliers pv,
         apps.ap_supplier_sites_all pvs,
         apps.hz_parties party_supp,
         apps.hz_party_sites site_supp,
         applsys.fnd_user fu,
         apps.hr_operating_units hou,
         apps.po_lookup_codes plc,
         apps.ap_lookup_codes alc,
         apps.ap_terms apt,
         iby.iby_external_payees_all iep,
         iby.iby_pmt_instr_uses_all ipi,
         apps.iby_ext_bank_accounts_v ieb
   WHERE pv.vendor_id = pvs.vendor_id
     AND party_supp.party_id = pv.party_id
     AND party_supp.party_id = site_supp.party_id
     AND site_supp.party_site_id = pvs.party_site_id
     AND pvs.last_updated_by = fu.user_id(+)
     AND pvs.org_id = hou.organization_id
     AND pv.vendor_type_lookup_code = plc.lookup_code(+)
     AND plc.lookup_type(+) = 'VENDOR TYPE'
     AND pvs.terms_id = apt.term_id
     AND iep.supplier_site_id = pvs.vendor_site_id
     AND iep.payee_party_id = pv.party_id
     AND iep.default_payment_method_code = alc.lookup_code
     AND alc.lookup_type = 'PAYMENT METHOD'
     AND iep.payee_party_id = party_supp.party_id
     AND iep.party_site_id = site_supp.party_site_id
     AND iep.ext_payee_id = ipi.ext_pmt_party_id(+)
     AND ipi.instrument_id = ieb.ext_bank_account_id(+)
ORDER BY pv.vendor_name, pvs.vendor_site_code;

Sales Order details with India Localization tax

SELECT h.header_id header_id, party.party_name sold_to,
       h.order_number order_number,
       NVL (TO_CHAR (h.ordered_date, 'DD-MON-YYYY'),
            TO_CHAR (h.request_date, 'DD-MON-YYYY')
           ) ordered_date,
       (SELECT TO_CHAR (MAX (oola.schedule_ship_date),
                        'DD-MON-YYYY'
                       )
          FROM oe_order_lines_all oola
         WHERE oola.header_id = h.header_id) dispatch_date,
       bill_su.LOCATION invoice_to_location,
       bill_loc.address1 invoice_to_address1,
       bill_loc.address2 invoice_to_address2,
       bill_loc.address3 invoice_to_address3,
       bill_loc.address4 invoice_to_address4,
          DECODE (bill_loc.city,
                  NULL, NULL,
                  bill_loc.city || ', '
                 )
       || DECODE (bill_loc.state,
                  NULL, bill_loc.province || ', ',
                  bill_loc.state || ', '
                 )
       || DECODE (bill_loc.postal_code,
                  NULL, NULL,
                  bill_loc.postal_code || ', '
                 )
       || DECODE (ft.territory_short_name,
                  NULL, NULL,
                  ft.territory_short_name || '.'
                 ) invoice_to_address5,
       ship_su.LOCATION ship_to_location, ship_loc.address1 ship_to_address1,
       ship_loc.address2 ship_to_address2, ship_loc.address3 ship_to_address3,
       ship_loc.address4 ship_to_address4,
          DECODE (ship_loc.city,
                  NULL, NULL,
                  ship_loc.city || ', '
                 )
       || DECODE (ship_loc.state,
                  NULL, ship_loc.province || ', ',
                  ship_loc.state || ', '
                 )
       || DECODE (ship_loc.postal_code,
                  NULL, NULL,
                  ship_loc.postal_code || ', '
                 )
       || DECODE (ft.territory_short_name,
                  NULL, NULL,
                  ft.territory_short_name || '.'
                 ) ship_to_address5,
       h.cust_po_number reference_number, h.attribute1 transporter_name,
       term.NAME terms, cust_acct.cust_account_id cust_account_id,
       l.line_number line_no,
       (SELECT NVL (l1.user_item_description, msi.description)
          FROM oe_order_lines_all l1, mtl_system_items_b msi
         WHERE l1.inventory_item_id = msi.inventory_item_id
           AND l1.ship_from_org_id = msi.organization_id
           AND l1.header_id = l.header_id
           AND l1.line_id = l.line_id
           AND l1.inventory_item_id = l.inventory_item_id) particulars,
       l.unit_selling_price unit_selling_price,
       l.ordered_quantity ordered_quantity,
      -- l.order_quantity_uom order_quantity_uom,
      (SELECT primary_unit_of_measure
          FROM oe_order_lines_all l2, mtl_system_items_b msi1
         WHERE l2.inventory_item_id = msi1.inventory_item_id
           AND l2.ship_from_org_id = msi1.organization_id
           AND l2.header_id = l.header_id
           AND l2.line_id = l.line_id
           AND l2.inventory_item_id = l.inventory_item_id) order_quantity_uom,
       (l.ordered_quantity * l.unit_selling_price) line_amount,
       NVL (jcta.tax_rate, 0) tax_rate,
       (SELECT NVL (SUM (  ABS (NVL (amount_due_remaining, 0))
                         + ABS (NVL (tax_remaining, 0))
                        ),
                    0
                   )
          FROM ar_payment_schedules_all apsa1
         WHERE 1 = 1
           AND apsa1.CLASS IN ('PMT')
           AND apsa1.customer_id = h.sold_to_org_id) customer_balance,
       h.attribute2 remarks, fu.user_name prepared_by,
       l.ordered_item item_code, jcca.pan_no pan_no,
       jcca.cst_reg_no cst_reg_no, jcca.vat_reg_no vat_reg_no,
       jcca.service_tax_regno service_tax_regno
  FROM hz_parties party,
       hz_cust_accounts cust_acct,
       oe_order_headers_all h,
       oe_order_lines_all l,
       hz_cust_site_uses_all bill_su,
       hz_locations bill_loc,
       hz_cust_acct_sites_all bill_cas,
       hz_party_sites bill_ps,
       hz_cust_site_uses_all ship_su,
       hz_party_sites ship_ps,
       hz_locations ship_loc,
       hz_cust_acct_sites_all ship_cas,
       ra_terms_tl term,
       jai_om_oe_so_taxes jst,
       jai_cmn_taxes_all jcta,
       fnd_user fu,
       jai_cmn_cus_addresses jcca,
       fnd_territories_vl ft
 WHERE 1 = 1
   AND h.header_id = l.header_id
   AND h.org_id = l.org_id
   AND cust_acct.party_id = party.party_id
   AND h.sold_to_org_id = cust_acct.cust_account_id
   AND h.invoice_to_org_id = bill_su.site_use_id(+)
   AND bill_su.cust_acct_site_id = bill_cas.cust_acct_site_id(+)
   AND bill_cas.party_site_id = bill_ps.party_site_id(+)
   AND bill_loc.location_id(+) = bill_ps.location_id
   AND h.ship_to_org_id = ship_su.site_use_id(+)
   AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
   AND ship_cas.party_site_id = ship_ps.party_site_id(+)
   AND ship_loc.location_id(+) = ship_ps.location_id
   AND h.payment_term_id = term.term_id(+)
   AND term.LANGUAGE(+) = USERENV ('LANG')
   AND l.header_id = jst.header_id(+)
   AND l.line_id = jst.line_id(+)
   AND h.created_by = fu.user_id
   AND jst.tax_id = jcta.tax_id(+)
   AND jcta.tax_type IN ('CST', 'VALUE ADDED TAX')
   AND cust_acct.cust_account_id = jcca.customer_id
   AND ship_su.cust_acct_site_id = jcca.address_id
   AND ship_su.site_use_code = 'SHIP_TO'
   AND h.flow_status_code IN ('BOOKED')
   AND bill_loc.country = ft.territory_code(+)
   AND h.order_number = :p_sales_order

   ---Pradipta Behera

Query to get opening,purchasing,receive,sales and closing quantity of items

SELECT *
  FROM (SELECT   b.item_code, b.item_description item_description,
                 b.primary_uom, b.sub_inventory, b.LOCATOR,
                 b.opening_qty opening_qty, b.opening_value opening_value,
                 SUM (b.purchase_qty) purchase_qty,
                 SUM (b.purchasing_value) purchasing_value,
                 SUM (b.receive_qty) receive_qty,
                 SUM (b.receive_value) receive_value,
                 SUM (b.sales_qty) sales_qty, SUM (b.sale_value) sale_value,
                 SUM (b.other_plant_qty) other_plant_qty,
                 SUM (b.other_plant_value) other_plant_value,
                   (SUM (b.purchase_qty) + SUM (b.receive_qty) + b.opening_qty
                   )
                 - (ABS (SUM (b.sales_qty)) - SUM (b.other_plant_qty))
                                                                  closing_qty,
                 (  (  (  SUM (b.purchase_qty)
                        + SUM (b.receive_qty)
                        + b.opening_qty
                       )
                     - (ABS (SUM (b.sales_qty)) - SUM (b.other_plant_qty))
                    )
                  * cmpnt_cost
                 ) closing_value,
                 cmpnt_cost
            FROM (SELECT a.item_code, a.item_description, a.primary_uom,
                         a.sub_inventory, a.LOCATOR,
                         NVL (a.opening_qty, 0) opening_qty,
                         (NVL (opening_qty, 0) * cmpnt_cost) opening_value,
                         a.purchase_qty,
                         NVL (purchase_qty, 0) * cmpnt_cost purchasing_value,
                         a.receive_qty,
                         NVL (receive_qty, 0) * cmpnt_cost receive_value,
                         a.sales_qty,
                         NVL (sales_qty, 0) * cmpnt_cost sale_value,
                         a.other_plant_qty,
                           NVL (other_plant_qty, 0)
                         * cmpnt_cost other_plant_value,
                         cmpnt_cost
                    FROM (SELECT   msib.segment1 item_code,
                                   msib.description item_description,
                                   msib.primary_uom_code primary_uom,
                                   msi.secondary_inventory_name sub_inventory,
                                   (SELECT SUM
                                              (CASE
                                                  WHEN mmt1.transaction_type_id IN
                                                                     (36,15,63,52,21)
                                                     THEN DECODE
                                                           (mmt1.primary_quantity
                                                                ,
                                                             1, 0,
                                                             mmt1.primary_quantity
                                                            )
                                                  WHEN mmt1.transaction_type_id IN
                                                         (18, 12, 42, 43, 61,
                                                          33,35, 32, 34, 54,
                                                          62,21)
                                                     THEN mmt1.primary_quantity
                                                  ELSE 0
                                               END
                                              )
                                      FROM mtl_material_transactions mmt1,
                                           mtl_transaction_types mtt1
                                     WHERE 1 = 1
                                       AND mmt1.transaction_type_id =
                                                      mtt1.transaction_type_id
                                       AND mmt1.organization_id =
                                                          msib.organization_id
                                       AND mmt1.subinventory_code =
                                                  msi.secondary_inventory_name
                                       AND mmt1.inventory_item_id =
                                                        msib.inventory_item_id
                                       AND (mmt1.locator_id =
                                                     mil.inventory_location_id OR mil.inventory_location_id IS NULL)
                                       AND TRUNC (mmt1.transaction_date) <
                                                TRUNC (TO_DATE (:p_from_date)))
                                                                  opening_qty,
                                   mil.concatenated_segments LOCATOR,
                                   TRUNC
                                       (mmt.transaction_date)
                                                             transaction_date,
                                   SUM
                                      (CASE
                                          WHEN mmt.transaction_type_id IN
                                                                         (36)
                                             THEN DECODE
                                                       (primary_quantity,
                                                        1, 0,
                                                        primary_quantity
                                                       )
                                          WHEN mmt.transaction_type_id = 18
                                             THEN primary_quantity
                                          ELSE 0
                                       END
                                      ) purchase_qty,
                                   SUM
                                      (CASE
                                          WHEN mmt.subinventory_code LIKE
                                                                  'Staging_SI'
                                             THEN
                                             CASE
                                                    WHEN mmt.transaction_type_id IN
                                                           (12, 42, 43, 61,
                                                            15, 52)
                                                 THEN primary_quantity
                                                    ELSE 0
                                             END
                                          ELSE
                                           CASE
                                              WHEN mmt.transaction_type_id IN
                                                             (12, 42, 43, 61, 15)
                                           THEN primary_quantity
                                              ELSE 0
                                           END
                                       END
                                      ) receive_qty,
                                   (SUM
                                       (CASE
                                           WHEN mmt.subinventory_code LIKE
                                                                  'Staging_SI'
                                              THEN CASE
                                                   
                                                  WHEN mmt.transaction_type_id IN
                                                          (33, 35)
                                                        THEN primary_quantity
                                                     ELSE 0
                                              END
                                                ELSE CASE
                                                 
                                                WHEN mmt.transaction_type_id IN
                                                                         (33, 35, 52,21)
                                                      THEN primary_quantity
                                                   ELSE 0
                                                END
                                        END
                                       )
                                   ) sales_qty,
                                   (SELECT (SUM
                                               (CASE
                                                   WHEN mmt1.transaction_type_id IN
                                                                     (36, 15)
                                                      THEN DECODE
                                                             (mmt1.primary_quantity
                                                                 ,
                                                              1, 0,
                                                              mmt1.primary_quantity
                                                             )
                                                   WHEN mmt1.transaction_type_id IN
                                                          (18, 12, 42, 43, 61,
                                                           33, 35, 32, 34, 54,
                                                           62, 52)
                                                      THEN mmt1.primary_quantity
                                                   ELSE 0
                                                END
                                               )
                                           )
                                      FROM mtl_material_transactions mmt1,
                                           mtl_transaction_types mtt1
                                     WHERE 1 = 1
                                       AND mmt1.transaction_type_id =
                                                      mtt1.transaction_type_id
                                       AND mmt1.organization_id =
                                                          msib.organization_id
                                       AND mmt1.subinventory_code =
                                                         mil.subinventory_code
                                       AND mmt1.inventory_item_id =
                                                        msib.inventory_item_id
                                       AND mmt1.subinventory_code = msi.secondary_inventory_name
                                       AND mmt1.locator_id =
                                                     mil.inventory_location_id
                                       AND TRUNC (mmt1.transaction_date) =
                                                  TRUNC (mmt.transaction_date))
                                                                  closing_qty,
                                   SUM
                                      (CASE
                                          WHEN mmt.subinventory_code LIKE
                                                                  'SLT_SI'
                                             THEN CASE
                                                   
                                                  WHEN mmt.transaction_type_id IN
                                                          (32, 34, 54, 62, 63)
                                                        THEN primary_quantity
                                                     ELSE 0
                                              END
                                           
                                              WHEN mmt.subinventory_code LIKE
                                                                  'RM_SI'
                                             THEN CASE
                                                   
                                                  WHEN mmt.transaction_type_id IN
                                                          (34, 54, 62, 63)
                                                        THEN primary_quantity
                                                     ELSE 0
                                              END
                                           
                                              ELSE CASE
                                                 
                                                WHEN mmt.transaction_type_id IN
                                                                         (32, 34, 54, 62, 63,64)
                                                      THEN primary_quantity
                                                   ELSE 0
                                                END
                                       END
                                      ) other_plant_qty,
                                   xx_item_costs
                                          (msib.inventory_item_id,
                                           msib.organization_id,
                                           :p_to_date,
                                           :p_from_date
                                          ) cmpnt_cost
                              FROM mtl_system_items_b msib,
                                   mtl_secondary_inventories msi,
                                   mtl_item_locations_kfv mil,
                                   mtl_item_categories mic,
                                   mtl_categories_kfv mcb,
                                   org_organization_definitions ood,
                                   mtl_material_transactions mmt,
                                   mtl_transaction_types mtt
                             WHERE 1 = 1
                               AND ood.organization_id = msib.organization_id
                               AND msib.organization_id = msi.organization_id
                               AND msi.secondary_inventory_name(+) =
                                                         mmt.subinventory_code
                               AND mic.inventory_item_id =
                                                        msib.inventory_item_id
                               AND mic.organization_id = msib.organization_id
                               AND mic.category_id = mcb.category_id
                               AND msib.organization_id = :p_organization_id
                               AND msi.secondary_inventory_name =
                                      NVL (:p_sub_inventory,
                                           msi.secondary_inventory_name
                                          )
                               AND (   mil.concatenated_segments =
                                          NVL (:p_locator,
                                               mil.concatenated_segments
                                              )
                                    OR mil.concatenated_segments IS NULL
                                   )
                               AND mcb.segment1 =
                                               NVL (:p_segment1, mcb.segment1)
                               AND mcb.segment2 =
                                               NVL (:p_segment2, mcb.segment2)
                               AND mcb.segment3 =
                                               NVL (:p_segment3, mcb.segment3)
                               AND mcb.segment4 =
                                               NVL (:p_segment4, mcb.segment4)
                               AND ood.operating_unit = :p_org_id
                               AND mmt.transaction_type_id =
                                                       mtt.transaction_type_id
                               AND mmt.transaction_type_id IN
                                      (18, 12, 42, 43, 61, 33, 35, 32, 34, 54,
                                       62, 36, 15, 52, 63, 64,21)
                               AND mmt.organization_id = msib.organization_id(+)
                               AND mmt.subinventory_code = mil.subinventory_code(+)
                               AND mmt.locator_id = mil.inventory_location_id(+)
                               AND mmt.inventory_item_id = msib.inventory_item_id(+)
                               AND TRUNC (mmt.transaction_date)
                                      BETWEEN TRUNC (TO_DATE (:p_from_date))
                                          AND TRUNC (TO_DATE (:p_to_date))
                               AND category_set_id = 1
                             --  AND MMT.INVENTORY_ITEM_ID=24256
                          GROUP BY msib.segment1,
                                   msib.description,
                                   msib.primary_uom_code,
                                   msi.secondary_inventory_name,
                                   mil.concatenated_segments,
                                   TRUNC (mmt.transaction_date),
                                   msib.organization_id,
                                   mil.subinventory_code,
                                   mil.inventory_location_id,
                                   msib.inventory_item_id) a) b
        GROUP BY b.item_code,
                 b.item_description,
                 b.primary_uom,
                 b.sub_inventory,
                 b.LOCATOR,
                 b.opening_qty,
                 b.opening_value,
                 cmpnt_cost
          HAVING (   b.opening_qty <> 0
                  OR SUM (b.purchase_qty) <> 0
                  OR SUM (b.receive_qty) <> 0
                  OR SUM (b.sales_qty) <> 0
                  OR SUM (b.other_plant_qty) <> 0
                  OR   (  SUM (b.purchase_qty)
                        + SUM (b.receive_qty)
                        + b.opening_qty
                       )
                     - (ABS (SUM (b.sales_qty)) - SUM (b.other_plant_qty)) <>
                                                                             0
                 )
        ORDER BY b.item_code, b.sub_inventory, b.LOCATOR)
        UNION ALL
SELECT a.item_code, a.item_description, a.primary_uom, a.sub_inventory,
       a.LOCATOR, NVL (a.opening_qty, 0) opening_qty, NVL(a.opening_qty * a.cmpnt_cost,0) opening_value,
       NVL(a.purchase_qty,0) purchase_qty, NVL(a.purchasing_value,0) purchasing_value, NVL(a.receive_qty,0) receive_qty,
       NVL(a.receive_value,0)receive_value,
       NVL(a.sales_qty,0) sales_qty, NVL(a.sale_value,0)sale_value, NVL(a.other_plant_qty,0) other_plant_qty, NVL(a.other_plant_value,0)other_plant_value,
       NVL (a.closing_qty, 0) closing_qty, NVL(a.closing_qty * a.cmpnt_cost,0) closing_value,
       a.cmpnt_cost
       FROM(SELECT   msib.segment1 item_code, msib.description item_description,
         msib.primary_uom_code primary_uom,
         msi.secondary_inventory_name sub_inventory,
         mil.concatenated_segments LOCATOR,
         (SELECT SUM
                    (CASE
                        WHEN mmt1.transaction_type_id IN
                                                   (36,15,52,63,21)
                           THEN DECODE (mmt1.primary_quantity,
                                        1, 0,
                                        mmt1.primary_quantity
                                       )
                        WHEN mmt1.transaction_type_id IN
                                 (18, 12, 42, 43, 61, 33, 35, 32, 34, 54,62)
                           THEN mmt1.primary_quantity
                        ELSE 0
                     END
                    )
            FROM mtl_material_transactions mmt1, mtl_transaction_types mtt1
          WHERE  1 = 1
             AND mmt1.transaction_type_id = mtt1.transaction_type_id
             AND mmt1.organization_id = msib.organization_id
             AND mmt1.subinventory_code = msi.secondary_inventory_name
             AND msi.organization_id = :p_organization_id
             AND mmt1.inventory_item_id = msib.inventory_item_id
             AND mmt1.locator_id = mil.inventory_location_id
             AND mmt1.organization_id = :p_organization_id
             AND TRUNC (mmt1.transaction_date) <
                                                TRUNC (TO_DATE (:p_from_date)))
                                                                  opening_qty,
         NULL purchase_qty, NULL purchasing_value, NULL receive_qty,
         NULL receive_value, NULL sales_qty, NULL sale_value,
         NULL other_plant_qty, NULL other_plant_value,
         (SELECT (SUM
                                               (CASE
                                                   WHEN mmt1.transaction_type_id IN
                                                                     (36, 15,21)
                                                      THEN DECODE
                                                             (mmt1.primary_quantity,
                                                              1, 0,
                                                              mmt1.primary_quantity
                                                             )
                                                   WHEN mmt1.transaction_type_id IN
                                                          (18, 12, 42, 43, 61,
                                                           33, 35, 32, 34, 54,
                                                           62,52)
                                                      THEN mmt1.primary_quantity
                                                   ELSE 0
                                                END
                                               )
                                           )
                                           FROM mtl_material_transactions mmt1,
                                           mtl_transaction_types mtt1
                                     WHERE 1 = 1
                                       AND mmt1.transaction_type_id =
                                                      mtt1.transaction_type_id
                                       AND mmt1.organization_id =msib.organization_id
                                       AND mmt1.subinventory_code =mil.subinventory_code
                                       AND mmt1.inventory_item_id = msib.inventory_item_id
                                       AND mmt1.subinventory_code = msi.secondary_inventory_name
                                       AND mmt1.locator_id = mil.inventory_location_id
                                       AND TRUNC (mmt1.transaction_date) <= TRUNC (TO_DATE (:p_to_date)))closing_qty,
         xx_item_costs (msib.inventory_item_id,
                        msib.organization_id,
                        :p_to_date,
                        :p_from_date
                       ) cmpnt_cost
    FROM mtl_secondary_inventories msi,
         mtl_item_locations_kfv mil,
         mtl_system_items_b msib,
         org_organization_definitions ood,
         mtl_item_categories mic,
         mtl_categories_kfv mcb,
         mtl_material_transactions mmt,
         mtl_transaction_types mtt      
   WHERE 1 = 1
     AND ood.organization_id = msib.organization_id
     AND msib.organization_id = msi.organization_id
     AND msi.secondary_inventory_name = mil.subinventory_code
     AND msib.organization_id = :p_organization_id
     AND ood.operating_unit = :p_org_id
     AND mic.inventory_item_id = msib.inventory_item_id
     AND mic.organization_id = msib.organization_id
     AND mic.category_id = mcb.category_id
     AND msi.secondary_inventory_name =
                          NVL (:p_sub_inventory, msi.secondary_inventory_name)
     AND mil.concatenated_segments =
                                   NVL (:p_locator, mil.concatenated_segments)
     AND mcb.segment1 = NVL (:p_segment1, mcb.segment1)
     AND mcb.segment2 = NVL (:p_segment2, mcb.segment2)
     AND mcb.segment3 = NVL (:p_segment3, mcb.segment3)
     AND mcb.segment4 = NVL (:p_segment4, mcb.segment4)
     AND category_set_id = 1
     AND msib.inventory_item_id NOT IN (
                    SELECT inventory_item_id
                      FROM mtl_material_transactions mmt
                     WHERE 1 = 1
                       AND TRUNC (mmt.transaction_date)
                              BETWEEN TRUNC (TO_DATE (:p_from_date))
                                  AND TRUNC (TO_DATE (:p_to_date))
                       AND mmt.organization_id = :p_organization_id
                       AND mmt.subinventory_code= mil.subinventory_code)
   AND mmt.transaction_type_id = mtt.transaction_type_id
   AND mmt.subinventory_code =mil.subinventory_code
   AND mmt.inventory_item_id = msib.inventory_item_id
   AND mmt.subinventory_code = msi.secondary_inventory_name
   AND mmt.locator_id = mil.inventory_location_id
  -- AND MMT.INVENTORY_ITEM_ID=24256        
GROUP BY msib.segment1,
         msib.description,
         msib.primary_uom_code,
         msib.inventory_item_id,
         msib.organization_id,
         msi.secondary_inventory_name,
         mil.inventory_location_id,
         msi.organization_id,
         mil.subinventory_code,
         mil.concatenated_segments)a
         where ( a.opening_qty <> 0
               OR a.closing_qty <> 0 )

      ---Pradipta Behera