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;