ISO Receipt Register -GST Query

SELECT shipment_num, shipped_date, stnno, receipt_num, rcvd_date, send_orgid,

       send_orgname, recv_orgid, recv_org_name, gst_no, segment1, description,

       lot_num, pri_rec_uom, uomcode, LINK, rcv_qty, iso_price, iso_price_uom,

       NVL (tax_amount, 0) tax_amount,

       ((rcv_qty * iso_price) + NVL (tax_amount, 0)) stnvalue, option1, lrno,

       lrdate, freight

  FROM (SELECT shipment_num, shipped_date, stnno, receipt_num, rcvd_date,

               send_orgid, send_orgname, recv_orgid, recv_org_name, gst_no,

               segment1, description, lot_num, pri_rec_uom, uomcode, rcv_qty,

               iso_price_uom,

               CASE

                  WHEN

                      --NVL(iso_price_uom, 'NA') <> NVL(UOMCODE, 'NA') AND

                      NVL (iso_price_uom, 'NA') =

                                                NVL (uommain, 'NA')

                     THEN iso_price / LINK

                  ELSE iso_price

               END iso_price,

               tax_amount,

               CASE

                  WHEN UPPER (:opt1) = 'SENDORG'

                     THEN send_orgname

                  WHEN UPPER (:opt1) = 'SHIPNO'

                     THEN shipment_num

                  WHEN UPPER (:opt1) = 'RCPTNO'

                     THEN receipt_num

                  WHEN UPPER (:opt1) = 'RCPTDATE'

                     THEN TO_CHAR (rcvd_date, 'dd-mon-yyyy')

               END option1,

               lrno, lrdate, freight, LINK

          FROM (SELECT a.shipment_num, a.receipt_num,

                       c.transaction_date rcvd_date,

                       a.ship_to_org_id recv_orgid, e.NAME recv_org_name,

                       i.gst_no, f.organization_id send_orgid,

                       f.NAME send_orgname, a.shipped_date, d.segment1,

                       d.description, g.primary_quantity rcvd_qty, g.lot_num,

                       c.quantity rcv_qty, c.unit_of_measure rec_uom,

                       c.primary_unit_of_measure pri_rec_uom,

                       a.shipment_header_id, b.shipment_line_id,

                       c.transaction_id, b.requisition_line_id,

                       b.item_id AS inventory_item_id, h.LINK, h.uomcode,

                       i.tax_amount, h.uommain

                  FROM rcv_shipment_headers a,

                       rcv_shipment_lines b,

                       rcv_transactions c,

                       (SELECT inventory_item_id, segment1, description

                          FROM mtl_system_items

                         WHERE organization_id = 83) d,

                       hr_all_organization_units e,

                       (SELECT a.vat_reg_no tin_no, a.organization_id,

                               b.location_code NAME, a.location_id

                          FROM jai_cmn_inventory_orgs a,

                               hr_locations b,

                               hr_all_organization_units c

                         -- ja_in_hr_organization_units changed for R12

                        WHERE  a.organization_id = b.inventory_organization_id

                           AND b.inactive_date IS NULL

                           AND a.location_id = b.location_id

                           AND a.organization_id = c.organization_id

                           AND a.location_id = c.location_id) f,

                       rcv_lot_transactions g

                       (SELECT   trx_id, trx_line_id, trx_number,

                                 first_party_primary_reg_num gst_no,

                                 organization_id,

                                 SUM (rounded_tax_amt_tax_curr) tax_amount,

                                 trx_loc_line_id

                            FROM jai_tax_lines_all

                           WHERE trx_type = 'RECEIVE'

                             AND data_source = 'BASE_TABLE'

                             AND entity_code = 'RCV_TRANSACTION'

                             AND event_class_code = 'RECEIVING'

                             AND applied_from_entity_code =

                                                           'SALES_ORDER_ISSUE'

                             AND applied_from_event_class_code =

                                                           'SALES_ORDER_ISSUE'

                             AND TRUNC (trx_date) BETWEEN :fdate AND :tdate

                        GROUP BY trx_id,

                                 trx_line_id,

                                 trx_number,

                                 first_party_primary_reg_num,

                                 organization_id,

                                 trx_loc_line_id) i

                 ---wsh_new_deliveries  g

                WHERE  a.shipment_header_id = b.shipment_header_id

                   AND b.shipment_header_id = c.shipment_header_id

                   AND b.shipment_line_id = c.shipment_line_id

                   AND i.trx_id (+)= c.shipment_header_id

                   AND i.trx_line_id (+)= c.shipment_line_id

--                AND i.trx_line_id = c.shipment_line_id

--                AND i.trx_number = a.receipt_num

                   AND d.inventory_item_id = b.item_id

--                AND i.trx_number(+) = a.receipt_num

                   AND e.organization_id = a.ship_to_org_id

                   AND a.organization_id = f.organization_id

                   AND b.from_organization_id = f.organization_id

                   AND b.to_organization_id = e.organization_id

--                AND b.shipment_line_id=g.shipment_line_id

--                AND c.shipment_line_id=g.shipment_line_id

                   AND c.transaction_id = g.transaction_id(+)

                   AND c.transaction_id = i.trx_loc_line_id(+)

                   AND d.segment1 = h.appscode

                   AND a.ship_to_org_id = :orgid

                   AND c.transaction_type = 'RECEIVE'

                   AND TRUNC (c.transaction_date) BETWEEN :fdate AND :tdate

                   AND TRUNC (c.transaction_date) >= '04-nov-2011'

                   AND a.receipt_source_code = 'INTERNAL ORDER'

--                and a.receipt_num in ('216040')--206397

               )order by option1

                )

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

TAX INVOICE RCM(REVERSE CHARGE MECHANISM) - GST

The below query fetches the details of a tax invocie  where in Reverse charge mechanism(RCM) is applied in order to claim the extra tax paid by an organization along with the GST details .

SELECT AIL.description,AIL.amount,1 quantity,AIA.invoice_num,AIA.invoice_date,APS.vendor_name,ASSA.address_line1,ASSA.address_line2,ASSA.address_line3,ASSA.city,ASSA.state, ASSA.zip, JTL.first_party_primary_reg_num,J TL.third_party_primary_reg_num,

 OOD.organization_id,  OOD.organization_name, AIA.invoice_amount, HL.address_line_1 address_1, HL.address_line_2 address_2, HL.address_line_3 address_3,HL.town_or_city, HL.postal_code, HL.region_1,(SELECT TAR.rev_seqno  FROM 

                                                      ,org_organization_definitions OOD

                                                                              ,ap_invoices_all AIA

                                                                               WHERE 1=1

                                                                               AND AIA.invoice_id           = TAR.ap_invoice_id

                                                                              AND OOD.organization_id   = TAR.organization_id

                                                                              AND OOD.ORGANIZATION_ID  = :p_org_id

                                                                              AND AIA.INVOICE_NUM            = :p_invnum ) rev_seqno

FROM ap_invoices_all AIA

    ,ap_invoice_lines_all AIL

    ,ap_suppliers APS

    ,ap_supplier_sites_all ASSA

    ,jai_tax_lines_all JTL

    ,org_organization_definitions OOD

    ,hr_locations HL

WHERE 1=1

AND AIA.invoice_id         = AIL.invoice_id

AND AIA.vendor_id          = ASSA.vendor_id

AND APS.vendor_id          = ASSA.vendor_id

AND AIA.vendor_site_id   = ASSA.vendor_site_id

AND AIA.vendor_id           = JTL.party_id

AND AIA.invoice_id          =  JTL.trx_id

AND AIL.line_number        = JTL.trx_line_number

AND JTL.organization_id   =  OOD.organization_id

AND OOD.organization_id  = HL.inventory_organization_id

-- AND AIA.invoice_id        =3904571

AND OOD.ORGANIZATION_ID  = :p_org_id

AND AIA.INVOICE_NUM      = :p_invnum

GROUP BY AIL.description, AIL.amount, AIA.invoice_num, AIA.invoice_date, APS.vendor_name, ASSA.address_line1,ASSA.address_line2,ASSA.address_line3,ASSA.city,ASSA.state,

ASSA.zip,JTL.first_party_primary_reg_num,JTL.third_party_primary_reg_num,

OOD.organization_id, OOD.organization_name, AIA.invoice_amount, HL.address_line_1, HL.address_line_2,HL.address_line_3,HL.town_or_city,  HL.postal_code, HL.region_1

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

Oracle Fusion HCM Data Loader (HDL) Keys Significance

HCM Data Loader (HDL) Keys

Supported Key Types:

HCM Data Loader (HDL) supports 4 different types of keys as listed below 

1. GUID – Oracle Fusion Global Unique ID
2. Oracle Fusion Surrogate ID
3. Source Keys
4. User Keys 

Key Resolution Sequence (Key references are resolved) in the following order:

1. Oracle Fusion GUID

2. Source key

3. Oracle Fusion surrogate ID

4. User key

Important Notes 
1. While  Create/First time Loading any object(s) we have to consider only SOURCE KEY / USER KEY no need to think about GUID or Surrogate ID (because those are created only after an object created in fusion).

2.While Modify/Updating/DELETE any object then all four keys comes into picture.

If you supply multiple key values, then they are used in this order with no cross-validation. For example, if you supply both a GUID and a source key, then the GUID is used to identify the record and the source key is ignored. If the source key references a different record from the GUID, then no error is raised.

These key types are explained below:

Oracle Fusion GUID Integration Key Generated by Fusion Generated in Oracle Fusion when a record is created Hexadecimal value Unique across all objects Held in Integration Key Map	Oracle Fusion Surrogate ID Fusion Generated Unique ID Generated in Oracle Fusion when the record is created Numeric value Unique only for the object type Held on the object
Source Keys Source System Key Information Two values combined: SourceSystemOwner SourceSystemID Held in Integration Key Map	User Keys User Readable and Generated Keys Natural values One or many attributes Sometimes alternatives Sometimes updateable Held on the object definition

Key Type	Create	Update	Held on Object	Type	Generated Automatically
GUID	No	Yes	No	Hexadecimal	Yes
Surrogate ID	No	Yes (see note #1)	Yes	Numeric	Yes
Source Key	Yes	Yes	No	Alphanumeric	Conditionally (see note #2)
User Key	Yes	Yes (see note #3)	Yes	Alphanumeric	No

Notes:-

1. You can use surrogate IDs when updating objects, but the IDs may not be readily available to Oracle HCM Cloud users.
2. Default source keys are generated only if you don't supply a source key when creating an object.
3. You can't use user keys alone when updating some objects because their values are subject to change.
4. Keys that aren't held on the object exist in the Integration Key Map table.

Integration Key Map table

Keys that aren’t held on the object are stored in the HDL integration key map table - HRC_INTEGRATION_KEY_MAP. You should be able to use BIP to fire up a SQL statement and view the contents of this table. e.g. 

/* Formatted on 2017/09/12 14:25 (Formatter Plus v4.8.8) */

SELECT object_name

     ,source_system_id

     ,source_system_owner

     ,surrogate_id

     ,RAWTOHEX (guid) guid

FROM fusion.hrc_integration_key_map

WHERE source_system_owner = 'STUDENT1'

Business Object	Fusion GUID	Source Key	Surrage ID	User Key
Location	88ABCD164738983	STUDENT1_LOC1	8899933355517	(Set code and Location Code) COMMON, HQ1

• Fusion GUID: System generated GUID
• Source Key:  Source System Owner is the reference to source application like PS or EBS, Source System Key is the actual key\id provided in the Location.dat file. 
• Surrogate ID: System generated. In this case it is the primary key from locations record. e.g. select * from PER_LOCATION_DETAILS_F_VL  where location code = 'STUDENT1 Location1‘   (Result Location ID = 8899933355517)
• User Key:- Best way to get this info is the Business Object Documentation from MOS or other option is UI as shown below. Online page should highlight user keys with

Oracle Apps(EBS) - AR Receipt Register Query with Bank statement Header and Line Details

Below query is useful when you required  Non Misc Receipts Along with Bank Statement Header , Line Details and Activity name ( like Receipt Write off)

SELECT ACRA.RECEIPT_DATE
,( select distinct CSH.STATEMENT_NUMBER from  apps.ce_statement_reconcils_all CSRA,
                                    apps.ce_statement_lines CSL,
                                    apps.ce_statement_headers CSH 
                            where  CSRA.REFERENCE_ID=ACRHA.CASH_RECEIPT_HISTORY_ID
                                    AND CSRA.STATEMENT_LINE_ID=CSL.STATEMENT_LINE_ID
                                    AND CSL.STATEMENT_HEADER_ID=CSH.STATEMENT_HEADER_ID)  STATEMENT_NUMBER
,( select CSL.LINE_NUMBER from  apps.ce_statement_reconcils_all CSRA,
                                    apps.ce_statement_lines CSL,
                                    apps.ce_statement_headers CSH 
                            where  CSRA.REFERENCE_ID=ACRHA.CASH_RECEIPT_HISTORY_ID
                                    AND CSRA.STATEMENT_LINE_ID=CSL.STATEMENT_LINE_ID
                                    AND CSL.STATEMENT_HEADER_ID=CSH.STATEMENT_HEADER_ID)  LINE_NUMBER
,ACRA.RECEIPT_NUMBER
,DECODE(ARCAA.applied_payment_schedule_id, -1,NVL(SUBSTR(HCA_ONACC.ACCOUNT_NUMBER, INSTR(HCA_ONACC.ACCOUNT_NUMBER, '.')+1), HCA_ONACC.ACCOUNT_NUMBER), NVL(SUBSTR(HCA.ACCOUNT_NUMBER, INSTR(HCA.ACCOUNT_NUMBER, '.')+1), HCA.ACCOUNT_NUMBER)) CUSTOMER_NUMBER
,DECODE(ARCAA.applied_payment_schedule_id, -1,HP_ONACC.PARTY_NAME,HP.PARTY_NAME ) CUSTOMER_NAME
,DECODE(ARCAA.applied_payment_schedule_id, -1, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'ON_ACC'), -3,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'RCPT_WRITE_OFF'), -4,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CLAIM_INV'), -6,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_REFUND'), -8,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'REFUND'), -9,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_CHARGEBACK') ,RCTA.TRX_NUMBER) APPLIED_TO
,ARCAA.APPLY_DATE
,art.name ACTIVITY_NAME
,ARCAA.AMOUNT_APPLIED
,ACRA.AMOUNT RECEIPT_AMOUNT
,ACRA.CURRENCY_CODE TRANSACTION_CURRENCY
,FU.USER_NAME APPLIED_USER
,ACRA.CREATION_DATE
                    FROM apps.ar_receivable_applications_all ARCAA,
apps.ar_cash_receipts_all ACRA,
apps.ar_cash_receipt_history_all ACRHA,
apps.ra_customer_trx_all RCTA,
apps.hz_cust_accounts HCA,
apps.hz_parties HP,
apps.hz_cust_accounts HCA_ONACC,
apps.hz_parties HP_ONACC,
apps.fnd_user FU
,ar_receivables_trx_ALL art                         
                  WHERE ARCAA.CASH_RECEIPT_ID=ACRA.CASH_RECEIPT_ID(+)
AND ACRHA.CASH_RECEIPT_ID(+)=ACRA.CASH_RECEIPT_ID
AND ARCAA.APPLIED_CUSTOMER_TRX_ID=RCTA.CUSTOMER_TRX_ID(+)
AND RCTA.BILL_TO_CUSTOMER_ID=HCA.CUST_ACCOUNT_ID(+)
AND HCA.PARTY_ID=HP.PARTY_ID(+)
AND HCA_ONACC.CUST_ACCOUNT_ID(+)=ARCAA.ON_ACCT_CUST_ID
AND HCA_ONACC.PARTY_ID=HP_ONACC.PARTY_ID(+)
AND FU.USER_ID(+)=ARCAA.CREATED_BY
AND ARCAA.STATUS NOT IN ('UNAPP','UNID')
                            AND art.receivables_trx_id(+) = ARCAA.receivables_trx_id
                            AND ARCAA.GL_DATE BETWEEN NVL(TO_DATE(SUBSTR(:p_gl_date_from,1,10),'yyyy/mm/dd'),ARCAA.GL_DATE) AND NVL(TO_DATE(SUBSTR(:p_gl_date_to,1,10),'yyyy/mm/dd'),ARCAA.GL_DATE)
                            AND ARCAA.APPLY_DATE BETWEEN NVL(TO_DATE(SUBSTR(:p_apply_date_from,1,10),'yyyy/mm/dd'),ARCAA.APPLY_DATE) AND NVL(TO_DATE(SUBSTR(:p_apply_date_to,1,10),'yyyy/mm/dd'),ARCAA.APPLY_DATE)
                            AND(( HP.PARTY_NAME between NVL(:p_customer_name_low,HP.PARTY_NAME) AND NVL(:p_customer_name_high,HP.PARTY_NAME)) or
(HP_ONACC.PARTY_NAME between NVL(:p_customer_name_low,HP_ONACC.PARTY_NAME) AND NVL(:p_customer_name_high,HP_ONACC.PARTY_NAME))
   )
                            AND( ( HCA.ACCOUNT_NUMBER between NVL(:p_customer_number_low,HCA.ACCOUNT_NUMBER) AND NVL(:p_customer_number_high,HCA.ACCOUNT_NUMBER) or
   (HCA_ONACC.ACCOUNT_NUMBER between NVL(:p_customer_number_low,HCA_ONACC.ACCOUNT_NUMBER) AND NVL(:p_customer_number_high,HCA_ONACC.ACCOUNT_NUMBER))
  )
   )
                            AND ARCAA.ORG_ID=:p_org AND
                            ACRA.ORG_ID=:p_org AND
                            ACRHA.ORG_ID=:p_org
AND ACRA.TYPE != 'MISC'
AND ARCAA.REVERSAL_GL_DATE is NULL
and ACRHA.REVERSAL_GL_DATE is NULL
            UNION
                    SELECT ACRA.RECEIPT_DATE
                            ,NULL STATEMENT_NUMBER
                            ,NULL LINE_NUMBER
                            ,ACRA.RECEIPT_NUMBER
                            ,NVL(SUBSTR(HCA_ONACC.ACCOUNT_NUMBER, INSTR(HCA_ONACC.ACCOUNT_NUMBER, '.')+1), HCA_ONACC.ACCOUNT_NUMBER) CUSTOMER_NUMBER
                            ,HP_ONACC.PARTY_NAME CUSTOMER_NAME
                            ,DECODE(ARCAA.applied_payment_schedule_id, -1, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'ON_ACC'), -3,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'RCPT_WRITE_OFF'), -4,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CLAIM_INV'), -6,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_REFUND'), -8,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'REFUND'), -9,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_CHARGEBACK') ,null) APPLIED_TO
                            ,ARCAA.APPLY_DATE
                            ,art.name ACTIVITY_NAME
                            ,ARCAA.AMOUNT_APPLIED
                            ,ACRA.AMOUNT RECEIPT_AMOUNT
                            ,ACRA.CURRENCY_CODE TRANSACTION_CURRENCY
                            ,FU.USER_NAME APPLIED_USER
                            ,ACRA.CREATION_DATE
                    FROM apps.ar_receivable_applications_all ARCAA,
                            apps.ar_cash_receipts_all ACRA,
                            apps.ar_cash_receipt_history_all ACRHA,
                            apps.hz_cust_accounts HCA_ONACC,
                            apps.hz_parties HP_ONACC,
                            apps.fnd_user FU
                            ,ar_receivables_trx_ALL art
                    WHERE ARCAA.CASH_RECEIPT_ID=ACRA.CASH_RECEIPT_ID
AND ACRHA.CASH_RECEIPT_ID=ACRA.CASH_RECEIPT_ID
AND ARCAA.APPLIED_CUSTOMER_TRX_ID IS NULL
AND (HCA_ONACC.CUST_ACCOUNT_ID=ARCAA.ON_ACCT_CUST_ID or HCA_ONACC.CUST_ACCOUNT_ID=ACRA.PAY_FROM_CUSTOMER )
AND HCA_ONACC.PARTY_ID=HP_ONACC.PARTY_ID(+)
AND FU.USER_ID(+)=ARCAA.CREATED_BY
                            AND ARCAA.STATUS NOT IN ('UNAPP','UNID')
                            AND art.receivables_trx_id(+) = ARCAA.receivables_trx_id AND
ARCAA.ORG_ID=:p_org
AND ACRA.ORG_ID=:p_org
AND ACRHA.ORG_ID=:p_org
AND ACRA.TYPE != 'MISC'                         
                            AND ARCAA.APPLIED_PAYMENT_SCHEDULE_ID <0
                            AND ARCAA.AMOUNT_APPLIED >0
                            AND ARCAA.REVERSAL_GL_DATE IS NULL
                            AND ACRHA.REVERSAL_GL_DATE IS NULL
                            AND ARCAA.GL_DATE BETWEEN NVL(TO_DATE(SUBSTR(:p_gl_date_from,1,10),'yyyy/mm/dd'),ARCAA.GL_DATE) AND NVL(TO_DATE(SUBSTR(:p_gl_date_to,1,10),'yyyy/mm/dd'),ARCAA.GL_DATE)
                            AND ARCAA.APPLY_DATE BETWEEN NVL(TO_DATE(SUBSTR(:p_apply_date_from,1,10),'yyyy/mm/dd'),ARCAA.APPLY_DATE) AND NVL(TO_DATE(SUBSTR(:p_apply_date_to,1,10),'yyyy/mm/dd'),ARCAA.APPLY_DATE)
                            AND HP_ONACC.PARTY_NAME between NVL(:p_customer_name_low,HP_ONACC.PARTY_NAME) AND NVL(:p_customer_name_high,HP_ONACC.PARTY_NAME)
                            AND HCA_ONACC.ACCOUNT_NUMBER between NVL(:p_customer_number_low,HCA_ONACC.ACCOUNT_NUMBER) AND NVL(:p_customer_number_high,HCA_ONACC.ACCOUNT_NUMBER)
                            order by RECEIPT_NUMBER,CREATION_DATE ASC  ;