XLA to AP link

select xdl.* from apps.XLA_DISTRIBUTION_LINKS xdl, apps.ap_invoices_all aia, apps. ap_invoice_distributions_all aida
where APPLIED_TO_ENTITY_CODE = 'AP_INVOICES'
and applied_to_source_id_num_1 = aia.invoice_id
AND xdl.source_distribution_type = 'AP_INV_DIST'
and SOURCE_DISTRIBUTION_ID_NUM_1 = aida.invoice_distribution_id
and aida.invoice_id=aia.invoice_id
and aia.invoice_num = &Inv_num
and aia.org_id = &org_id

Long running SQL Query

   SELECT CLIENT_IDENTIFIER

             USER_NAME,

         SYSDATE,

         ROUND (((S.LAST_CALL_ET / 86400)) * 24 * 60, 2)

             WAIT_TIME,

         S.SID,

         S.SERIAL#,

         S.Sql_id,

         s.inst_id,

         DECODE (s.sql_hash_value, 743038952, 'SAME', 'DIFFERENT')

             Hash_value,

         s.sql_hash_value,                                         --743038952

         S.STATUS,

         S.plsql_entry_object_id,

         S.MODULE,

         S.ACTION,

         SYSDATE - (S.LAST_CALL_ET / 86400)

             LAST_CALL,

         S.TYPE,

         S.USERNAME

             DB_USERNAME,

         S.OSUSER

             CLIENT_USERNAME,

         S.MACHINE,

         S.CLIENT_INFO,

         S.TERMINAL,

         S.LOGON_TIME,

         S.PROCESS,

         P.SPID,

         P.PID,

         s.*

    FROM GV$SESSION S, GV$PROCESS P

   WHERE     S.PADDR = P.ADDR(+)

         AND s.inst_id = p.inst_id

         AND S.MODULE IS NOT NULL

         AND S.STATUS = 'ACTIVE'

         AND NVL (S.TYPE, 'XXXXX') <> 'BACKGROUND'

         AND S.MODULE LIKE '%007%'                            -- CP Short name

--and sql_id = 'd1u4rr2gkcq33'

--  S.ACTION LIKE :ACTIONTYPE  AND

--S.OSUSER LIKE  :USR

--AND ROUND(((S.LAST_CALL_ET / 86400))*24*60,2) > :WAIT_TIME

ORDER BY ((S.LAST_CALL_ET / 86400)) * 24 * 60 DESC;

Table size query

 select  segment_name,sum(bytes)/1024/1024/1024 GB 

from dba_segments 

where segment_type='TABLE' 

and segment_name like 'XX%'

and segment_name=upper('XX_INV_ITEMS_STG') 

group by segment_name

PO Receipt with Lot details query

  SELECT poh.segment1

             po_number,

         asp.segment1

             vendor_number,

         asp.vendor_name,

         TRUNC (rt.transaction_date)

             receipt_date,

         (SELECT ood.organization_name

            FROM apps.org_organization_definitions ood

           WHERE organization_id = mmt.organization_id)

             "Receipt Org",

         TO_CHAR (TRUNC (rt.transaction_date), 'MON-YY')

             "Month",

         rsh.receipt_num,

         msi.segment1

             sku,

         rsl.item_revision,

         msi.description,

         rt.quantity

             line_quantity,

         mtln.lot_number,

         mtln.transaction_quantity

             lot_qty,

         msi.lot_control_code

    FROM apps.rcv_transactions           rt,

         apps.rcv_shipment_headers       rsh,

         apps.rcv_shipment_lines         rsl,

         apps.mtl_system_items_b         msi,

         apps.po_headers_all             poh,

         apps.ap_suppliers               asp,

         apps.mtl_transaction_lot_numbers mtln,

         apps.mtl_material_transactions  mmt

   WHERE     1 = 1

         AND rsh.shipment_header_id = rt.shipment_header_id

         AND rsl.shipment_header_id = rsh.shipment_header_id

         AND rsl.po_header_id = rt.po_header_id

         AND rsl.po_line_id = rt.po_line_id

         AND rsl.po_line_location_id = rt.po_line_location_id

         AND poh.vendor_id = asp.vendor_id

         AND rt.po_header_id = poh.po_header_id

         AND rsl.item_id = msi.inventory_item_id

         AND rsl.to_organization_id = msi.organization_id

         AND rt.transaction_date > '01-JAN-21'

         AND rt.source_document_code = 'PO'

         AND rt.transaction_type = 'DELIVER'

         AND mmt.transaction_id = mtln.transaction_id(+)

         AND mmt.rcv_transaction_id = rt.transaction_id

         AND mmt.organization_id = rt.organization_id

         AND mmt.inventory_item_id = rsl.item_id

         --and   mmt.transaction_type_id=18

         AND poh.org_id = &P_ORG_ID

         AND poh.attribute15 IS NULL

ORDER BY rt.transaction_date,

         receipt_num,

         sku,

         lot_number 

Oracle Financials - India Localization Notes (GST E-invoice)

 NOTE:2628076.1 - Oracle E-Business Suite: India GST E-Invoice, October 1, 2020

NOTE:2710671.1 - Oracle Financials for India (OFI) - Goods and Services Tax(GST) - E-Invoice Functionality

NOTE:2709910.1 - Oracle Financials for India (OFI) - Goods and Services Tax(GST) - E-Invoice Functionality Technical Flow

NOTE:2640446.2 - E-Business Suite Release 12: Oracle Financials for India (OFI) Information Center

fnd_conc_req_outputs - To View XML Publisher generated output files

Initial output file generated by Concurrent Request will be stored in FND_CONCURRENT_REQUESTS table (logfile_name and outfile_name) and all remaining output files generated Concurrent Request including XML Publisher generated output will be stored in fnd_conc_req_outputs table.

Query to find Credit memo and debit memo of a particular Order

select rca_cm.interface_header_attribute1 so_number,rca_cm.trx_number cm_number, rca_dm.trx_number dm_number,ps_cm.AMOUNT_DUE_ORIGINAL cm_amt_original, ps_cm.amount_due_remaining cm_amt_due_remaining, ps_dm.AMOUNT_DUE_ORIGINAL dm_amt_original, ps_dm.amount_due_remaining dm_amt_remaining

 from apps.ra_customer_trx_all rca_cm

    , apps.ra_customer_trx_all rca_dm   

    , apps.RA_CUST_TRX_TYPES_ALL  rtt_cm

    , apps.RA_CUST_TRX_TYPES_ALL  rtt_dm

    , apps.AR_PAYMENT_SCHEDULES_ALL ps_cm

    , apps.AR_PAYMENT_SCHEDULES_ALL ps_dm

 where rca_cm.interface_header_attribute1 = '40642183'

 and   rca_cm.org_id=rtt_cm.org_id

 and   rtt_cm.type='CM'

 and   rca_cm.cust_trx_type_id=rtt_cm.CUST_TRX_TYPE_ID

 and   rca_cm.interface_header_attribute1=rca_dm.interface_header_attribute1

 and   rtt_dm.type='DM'

 and   rca_dm.org_id=rtt_dm.org_id

 and   rca_dm.cust_trx_type_id=rtt_dm.CUST_TRX_TYPE_ID

 and   ps_cm.class='CM'

 and   ps_cm.status='OP'

 and   ps_cm.customer_trx_id=rca_cm.customer_trx_id

 and   ps_dm.class='DM'

 and   ps_dm.status='OP'

 and   ps_dm.customer_trx_id=rca_dm.customer_trx_id