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

Inventory Pending Transactions (MTL_MATERIAL_TRANSACTIONS_TEMP)

The inventory pending transactions can be found using below navigation

Inventory  -->  Transactions --> Pending Transactions.

Alternately directly in table, MTL_MATERIAL_TRANSACTIONS_TEMP

The transaction modes have following meaning

NULL or 1:  Online Processing
Online processing is used by the Oracle Applications to immediately process records. This does not require that a concurrent program be run. The Transaction Manager does not process transactions marked for online processing.

2: Concurrent Processing
Transactions marked as concurrent transaction mode are processed by a dedicated transaction worker to explicitly process a set of transactions. The Transaction Manager does not process transactions marked for concurrent processing.

3: Background Processing
Interface transactions marked for Background processing will be picked up by the transaction manager polling process and assigned to a transaction worker. These transactions will not be processed unless the transaction manager is running.

8:  Internal Processing
Transaction mode 8 is not a mode normally visible to the user as it is used for internal processing.  Transactions with this mode are not visible in the Pending transactions form. Transaction_mode of 8 is used internally to identify if records came from the interface table to the pending table or came directly into the pending table.


Some useful oracle support notes:

Master Note for Inventory (Doc ID 1192649.1)
Note 568012.1 - FAQ: Inventory Standard Datafixes
Note 1472074.1 - Inventory Standard Datafix Instruction #17: Fixing Duplicate Sales Order Transactions In MTI, MMTT and MMT
Note 1177935.1 - for Inventory and WIP transactions

SQL query to convert Comma/Colon separated List to Rows

        select  regexp_substr('san@abc.com; san@xyz.com','[^; ]+', 1, level) 
from dual
        connect by regexp_substr('san@abc.com; san@xyz.com', '[^; ]+', 1, level) is not null;

Query to fetch Prepayment amount used from a Prepayment invoice

Query to fetch Prepayment used from a Prepayment invoice
  SELECT SUM(total_dist_amount -
         NVL(prepay_amount_remaining, total_dist_amount))
    FROM ap_invoice_distributions_all aid,
         ap_invoice_lines_all ail
   WHERE aid.invoice_id = &P_invoice_id
     AND aid.invoice_id = ail.invoice_id
     AND aid.invoice_line_number = ail.line_number
     AND ail.line_type_lookup_code <> 'TAX'
     AND aid.line_type_lookup_code IN
         ('ITEM', 'ACCRUAL', 'REC_TAX', 'NONREC_TAX')
     AND NVL(reversal_flag,'N') <> 'Y';

Query to fetch Prepaid amount for a Standard Invoice

Query to fetch Prepaid amount for a Standard Invoice

 SELECT  (0 - SUM(NVL(aid.amount,0))) prepaid_amount
    FROM ap_invoice_distributions_all aid,
         ap_invoice_lines_all         ail
     WHERE ail.invoice_id = &P_invoice_id -- invoice id of std invoice
     AND   ail.invoice_id = aid.invoice_id
     AND   ail.line_number = aid.invoice_line_number
     AND   aid.line_type_lookup_code = 'PREPAY'
     AND   aid.prepay_distribution_id IS NOT NULL
     AND   NVL(ail.invoice_includes_prepay_flag, 'N') = 'N';

OR

  SELECT ABS(SUM(amount))
    FROM ap_invoice_lines_all ail
   WHERE ail.invoice_id = &P_invoice_id -- invoice id of std invoice
     AND ail.line_type_lookup_code = 'PREPAY';