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;

Query to get AP and AGIS Invoices

Related posts: