Query to get AP and AGIS Invoices

The below given SQL Query will retrieve the set of data for AP and AGIS Invoices with consolidated and non consolidated batches based on the AGIS Batch Source Name , Org_id and Flex Value Set Name defined

SELECT AII.INVOICE_ID,
AII.GROUP_ID,
AII.INVOICE_NUM,
AII.INVOICE_AMOUNT,
AII.ORG_ID AP_ORG_ID,
RCTA.ATTRIBUTE9,
FTR.TRANSACTION_LE_ID,
FTR.TP_LE_ID,
FTH.TRX_ID,
RCTA.ORG_ID AR_ORG_ID,
RT.NAME,
FTH.BATCH_ID,
AII.SOURCE,
FTH.TO_LEDGER_ID
FROM AP_INVOICES_INTERFACE AII,
FUN_TRX_HEADERS FTH,
RA_CUSTOMER_TRX_ALL RCTA,
FUN_SUPPLIER_MAPS FSM,
FUN_TRADE_RELATIONS FTR,
RA_BATCH_SOURCES_ALL RBS,
RA_TERMS RT,
FUN_DIST_LINES FDL,
AP_INVOICE_LINES_INTERFACE AILI
WHERE     FTH.TRX_ID = AII.GROUP_ID
AND RCTA.TRX_NUMBER = AII.INVOICE_NUM
AND aii.SOURCE = :<enter_source_name>
AND FSM.RELATION_ID = FTR.RELATION_ID
AND RBS.NAME = 'Global Intercompany'
AND RCTA.BATCH_SOURCE_ID = RBS.BATCH_SOURCE_ID
AND RCTA.ORG_ID = RBS.ORG_ID
AND RCTA.TERM_ID = RT.TERM_ID(+)
AND FSM.VENDOR_ID = AII.VENDOR_ID
AND FSM.VENDOR_SITE_ID = AII.VENDOR_SITE_ID
AND RCTA.ORG_ID = FTR.TRANSACTION_ORG_ID
AND AII.ORG_ID = FTR.TP_ORG_ID
AND AII.WORKFLOW_FLAG IS NULL
AND aii.org_id = :<enter_org_id>
AND AII.INVOICE_ID = AILI.INVOICE_ID
AND FDL.DIST_NUMBER = AILI.LINE_NUMBER
AND AII.GROUP_ID = FTH.TRX_ID
AND FTH.TRX_ID = FDL.TRX_ID
AND FDL.DIST_TYPE_FLAG = 'L'
AND FDL.PARTY_TYPE_FLAG = 'R'
AND FDL.AUTO_GENERATE_FLAG = 'N'
GROUP BY AII.INVOICE_ID,
AII.GROUP_ID,
AII.INVOICE_NUM,
AII.INVOICE_AMOUNT,
FTH.ATTRIBUTE1,
AII.ORG_ID,
RCTA.ATTRIBUTE9,
FTH.ATTRIBUTE2,
FTH.ATTRIBUTE12,
FTR.TRANSACTION_LE_ID,
FTR.TP_LE_ID,
FTH.TRX_ID,
RCTA.ORG_ID,
RT.NAME,
FTH.BATCH_ID,
AII.SOURCE,
FTH.TO_LEDGER_ID,
AII.INVOICE_CURRENCY_CODE,
AII.GL_DATE
ORDER BY AII.ORG_ID, AII.INVOICE_ID;

Related posts:

Query to get AR and AGIS Invoices Oracle apps R12 Query to check Customer Transaction Balances SQL to get the Invoice Status in Account Payables SQL Query to check if AP invoices is posted or not Query to get AGIS – Intercompany Matched or unmatched Balances for a particular Account level details SQL query to identify AP cancelled invoices having distribution lines AR SQL Query to get Open Invoices where Customer payment status is Open Oracle apps R12 Query to get the Credit Memo Balance Due