Query to get AR and AGIS Invoices

The below given SQL Query will retrieve the set of data for 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’

Query to get AR and AGIS Invoices
Scroll to top
Copy Protected by Chetan's WP-Copyprotect.