Query to get AGIS – Intercompany Matched or unmatched Balances for a particular Account level details

Please find below the SQL Query to identify the Intercompany Balances for a particular Account whether they have been MATCHED or UNMATCHED to resolve the Reconciliation Issues.

SELECT DISTINCT
A.SEGMENT1 INITIATOR_RECEIVABLE,
A.SEGMENT5 RECEIPIENT_RECEIVABLE,
(   A.SEGMENT1|| ‘.’|| A.SEGMENT2|| ‘.’|| A.SEGMENT3|| ‘.’|| A.SEGMENT4|| ‘.’|| A.SEGMENT5|| ‘.’|| A.SEGMENT6|| ‘.’|| A.SEGMENT7) REC_ACCOUNT,
(A.RECEIVABLES),
B.SEGMENT1 INITIATOR_PAYABLES,
B.SEGMENT5 RECEIPIENT_PAYABLES,
(   B.SEGMENT1|| ‘.’|| B.SEGMENT2|| ‘.’|| B.SEGMENT3|| ‘.’|| B.SEGMENT4|| ‘.’|| B.SEGMENT5|| ‘.’|| B.SEGMENT6|| ‘.’|| B.SEGMENT7) PAY_ACCOUNT,
(B.PAYABLES),
(ABS (A.RECEIVABLES) – ABS (B.PAYABLES)) DIFFERENCE,
DECODE ( (ABS (A.RECEIVABLES) – ABS (B.PAYABLES)),
0, ‘Matched’,
‘Not Matched’) MATCHED_UNMATCHED
FROM ( (SELECT GCC1.SEGMENT1,
GCC1.SEGMENT2,
GCC1.SEGMENT3,
GCC1.SEGMENT4,
GCC1.SEGMENT5,
GCC1.SEGMENT6,
GCC1.SEGMENT7,
GCC1.CODE_COMBINATION_ID,
(PERIOD_NET_DR – PERIOD_NET_CR) RECEIVABLES,
GL.LEDGER_ID,
GB.CURRENCY_CODE,
GL.CURRENCY_CODE GL_CURR
FROM GL_BALANCES GB,
GL_CODE_COMBINATIONS GCC1,
GL_LEDGERS GL,
FND_FLEX_VALUES FFV
WHERE     GB.CODE_COMBINATION_ID = GCC1.CODE_COMBINATION_ID
AND GB.LEDGER_ID = GL.LEDGER_ID
AND GB.CURRENCY_CODE = GL.CURRENCY_CODE
AND FFV.FLEX_VALUE = GCC1.SEGMENT1
AND SEGMENT4 = ‘<ENTER_ACCOUNT_DTLS>’
AND SEGMENT6 = ‘<ENTER_INTERCOMAPNY_DTLS>’
AND SEGMENT5 <> ‘000’
AND PERIOD_NAME = ‘DEC-15’
AND GB.CURRENCY_CODE = FFV.ATTRIBUTE16) A),
(SELECT GCC2.SEGMENT1,
GCC2.SEGMENT2,
GCC2.SEGMENT3,
GCC2.SEGMENT4,
GCC2.SEGMENT5,
GCC2.SEGMENT6,
GCC2.SEGMENT7,
GCC2.CODE_COMBINATION_ID,
(PERIOD_NET_DR – PERIOD_NET_CR) PAYABLES,
GL.LEDGER_ID,
GB.CURRENCY_CODE,
GL.CURRENCY_CODE GL_CURR
FROM GL_BALANCES GB, GL_CODE_COMBINATIONS GCC2, GL_LEDGERS GL
WHERE     GB.CODE_COMBINATION_ID = GCC2.CODE_COMBINATION_ID
AND GB.LEDGER_ID = GL.LEDGER_ID
AND GB.CURRENCY_CODE = GL.CURRENCY_CODE
AND SEGMENT4 = ‘<ENTER_ACCOUNT_DTLS>’
AND SEGMENT6 = ‘<ENTER_INTERCOMAPNY_DTLS>’
AND SEGMENT5 <> ‘000’
AND PERIOD_NAME = ‘DEC-15’) B
WHERE     A.SEGMENT1 = B.SEGMENT5
AND B.SEGMENT1 = A.SEGMENT5
AND A.CURRENCY_CODE = B.CURRENCY_CODE
AND A.CURRENCY_CODE = A.GL_CURR
AND A.LEDGER_ID = B.LEDGER_ID

Related posts:

Query to get the Receipt Class and Receipt Method setup in the system Query to get all records from GL_CODE_COMBINATIONS which are updated after last_run_date derived AR Invoice detailed query to identify if transactions has been posted to GL tables SQL Query to drill down from the Oracle apps R12 FA Assets mapping to GL Extract fixed asset and depreciation details in Oracle apps R12 Oracle Apps R12 Account Receivables (AR) Transaction Type Setup Query to get AP and AGIS Invoices Query to get AR and AGIS Invoices