The blog provides Oracle EBS R12 SQL to get AR 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 INTEF.ORG_ID,
INTEF.INTERFACE_LINE_ID,
INTEF.INTERFACE_LINE_ATTRIBUTE1 BATCH_ID,
INTEF.INTERFACE_LINE_ATTRIBUTE2 TRX_ID,
INTEF.INTERFACE_LINE_ATTRIBUTE3 LINE_ID,
INTEF.INTERFACE_LINE_ATTRIBUTE4 BATCH_NUMBER,
HEADERS.ATTRIBUTE1  ,   --trx_number in case of non-consolidated batches
FDL.ATTRIBUTE1, -- trx_number Null in case of AGIS manual batches and will have data in case of consolidated batches
BATCH.BATCH_NUMBER,
BATCH.BATCH_DATE,
LINE1.FLEX_VALUE TRADING_PARTNER_BC,
LINE2.FLEX_VALUE TRANSACTION_BC,
HOU1.NAME TRADING_ORG,
HOU2.NAME TRANSACTION_ORG,
HEADERS.BATCH_ID,
INTEF.CUST_TRX_TYPE_ID,
FTR.TRANSACTION_ORG_ID,
FTR.TP_ORG_ID,
FTR.TRANSACTION_LE_PARTY_ID,
FTR.TP_LE_PARTY_ID,
HEADERS.TRX_ID
FROM RA_INTERFACE_LINES INTEF,
FUN_TRX_BATCHES BATCH,
FUN_TRX_HEADERS HEADERS,
FUN_TRX_LINES FTL,
FUN_DIST_LINES FDL,
FUN_CUSTOMER_MAPS FCM,
FUN_TRADE_RELATIONS FTR,
FND_FLEX_VALUES LINE1,
FND_FLEX_VALUES LINE2,
FND_FLEX_VALUE_SETS HDR1,
FND_FLEX_VALUE_SETS HDR2,
HR_OPERATING_UNITS HOU1,
HR_OPERATING_UNITS HOU2
WHERE     1 = 1
AND INTEF.BATCH_SOURCE_NAME = :<ENTER_RBATCH_SOURCE_NAME>
AND INTEF.INTERFACE_LINE_ATTRIBUTE4 = BATCH.BATCH_NUMBER
AND INTEF.INTERFACE_LINE_ATTRIBUTE2 = HEADERS.TRX_ID
AND INTEF.ORG_ID = :<ENTER_-ORG_ID>
AND FCM.CUST_ACCOUNT_ID = INTEF.ORIG_SYSTEM_BILL_CUSTOMER_ID
AND HOU1.ORGANIZATION_ID = FTR.TRANSACTION_ORG_ID
AND HOU2.ORGANIZATION_ID = FTR.TP_ORG_ID
AND FTR.RELATION_ID = FCM.RELATION_ID
AND HDR1.FLEX_VALUE_SET_NAME = '<enter_flex_value_set_name>'
AND HDR1.FLEX_VALUE_SET_ID = LINE1.FLEX_VALUE_SET_ID
AND LINE1.ATTRIBUTE9 = HOU1.NAME
AND HDR2.FLEX_VALUE_SET_NAME = '<enter_flex_value_set_name>'
AND HDR2.FLEX_VALUE_SET_ID = LINE2.FLEX_VALUE_SET_ID
AND LINE2.ATTRIBUTE9 = HOU2.NAME
AND SYSDATE BETWEEN NVL (LINE1.START_DATE_ACTIVE, SYSDATE - 1)
AND NVL (LINE1.END_DATE_ACTIVE, SYSDATE)
AND SYSDATE BETWEEN NVL (LINE2.START_DATE_ACTIVE, SYSDATE - 1)
AND NVL (LINE2.END_DATE_ACTIVE, SYSDATE)
AND LINE1.ENABLED_FLAG = 'Y'
AND LINE2.ENABLED_FLAG = 'Y'
AND FTR.TP_ORG_ID = INTEF.ORG_ID
AND BATCH.BATCH_ID = HEADERS.BATCH_ID
AND HEADERS.TRX_ID = FTL.TRX_ID
AND FTL.LINE_ID = FDL.LINE_ID
AND FDL.PARTY_TYPE_FLAG = 'I'
AND FDL.DIST_TYPE_FLAG = 'L'
AND FDL.AUTO_GENERATE_FLAG = 'N'
AND INTEF.INTERFACE_LINE_CONTEXT = 'INTERNAL_ALLOCATIONS'