View: AR_DISTRIBUTIONS_BASE_V

Product:	AR - Receivables
Description:	distribution base extract
Implementation/DBA Data:	APPS.AR_DISTRIBUTIONS_BASE_V

View Text

SELECT /*+INDEX (LI AR_XLA_LINES_EXTRACT_N1)*/ LI.EVENT_ID EVENT_ID
, LI.BASE_CURRENCY_CODE BASE_CURRENCY
, LI.LINE_ID LINE_ID
, LI.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LI.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LI.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, LI.ACCTD_AMOUNT DIST_ACCTD_AMT
, LI.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LI.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LI.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LI.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, NULL GAIN_LOSS_AMT
, NULL GAIN_LOSS_SIGN
, LI.LINE_NUMBER LINE_NUMBER
, LI.LANGUAGE LANGUAGE
, LI.LEDGER_ID LEDGER_ID
, 'Y' RECP_OVERRIDE_ACCTD_AMT
, NULL DIST_ACTIVITY_BUCKET
FROM AR_XLA_LINES_EXTRACT LI
WHERE LI.POSTING_ENTITY = 'ADJ'
AND LI.LEVEL_FLAG = 'L'
AND LI.MFAR_ADDITIONAL_ENTRY = 'N' UNION /*********************** * 2 CASH RECEIPTS * ***********************/ SELECT /*+INDEX (LI AR_XLA_LINES_EXTRACT_N1)*/ LI.EVENT_ID EVENT_ID
, LI.BASE_CURRENCY_CODE BASE_CURRENCY
, LI.LINE_ID LINE_ID
, LI.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LI.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LI.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, DECODE(NVL(LI.RECEIVABLES_TRX_ID
, 0)
, -16
, FROM_ACCTD_AMOUNT
, LI.ACCTD_AMOUNT) DIST_ACCTD_AMT
, LI.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LI.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LI.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LI.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, NULL GAIN_LOSS_AMT
, NULL GAIN_LOSS_SIGN
, LI.LINE_NUMBER LINE_NUMBER
, LI.LANGUAGE LANGUAGE
, LI.LEDGER_ID LEDGER_ID
, NULL RECP_OVERRIDE_ACCTD_AMT
, NULL DIST_ACTIVITY_BUCKET
FROM AR_XLA_LINES_EXTRACT LI
WHERE LI.POSTING_ENTITY = 'CRH'
AND LI.LEVEL_FLAG = 'L'
AND LI.MFAR_ADDITIONAL_ENTRY = 'N' UNION /*********************** * 3 BILL RECEIVABLES * ***********************/ SELECT /*+INDEX (LI AR_XLA_LINES_EXTRACT_N1)*/ LI.EVENT_ID EVENT_ID
, LI.BASE_CURRENCY_CODE BASE_CURRENCY
, LI.LINE_ID LINE_ID
, LI.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LI.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LI.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, LI.ACCTD_AMOUNT DIST_ACCTD_AMT
, LI.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LI.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LI.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LI.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, NULL GAIN_LOSS_AMT
, NULL GAIN_LOSS_SIGN
, LI.LINE_NUMBER LINE_NUMBER
, LI.LANGUAGE LANGUAGE
, LI.LEDGER_ID LEDGER_ID
, NULL RECP_OVERRIDE_ACCTD_AMT
, NULL DIST_ACTIVITY_BUCKET
FROM AR_XLA_LINES_EXTRACT LI
WHERE LI.POSTING_ENTITY = 'TH'
AND LI.LEVEL_FLAG = 'L'
AND LI.MFAR_ADDITIONAL_ENTRY = 'N' UNION /************************ * 4 TO DOC FOR REC APP * ************************/ SELECT /*+ LEADING(LICR
, DIST
, LITRX) INDEX(LICR AR_XLA_LINES_EXTRACT_N1) INDEX(LITRX AR_XLA_LINES_EXTRACT_N2) */ LICR.EVENT_ID EVENT_ID
, LICR.BASE_CURRENCY_CODE BASE_CURRENCY
, LICR.LINE_ID LINE_ID
, LICR.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LICR.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LICR.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, DECODE(DIST.ACCTD_AMOUNT_APPLIED_TO
, 0
, DECODE(DIST.ACCTD_AMOUNT_APPLIED_FROM
, 0
, DIST.FROM_AMOUNT
, DECODE(NVL(LICR.EXCHANGE_RATE
, 1)
, NVL(LITRX.EXCHANGE_RATE
, 1)
, DECODE(CR.CURRENCY_CODE
, CT.INVOICE_CURRENCY_CODE
, DIST.FROM_AMOUNT
, LICR.FROM_ACCTD_AMOUNT)
, LICR.FROM_ACCTD_AMOUNT))
, DIST.FROM_AMOUNT) DIST_ACCTD_AMT
, LITRX.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LITRX.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LITRX.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LITRX.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, DIST.GAIN_LOSS GAIN_LOSS_AMT
, DECODE(SIGN(DIST.GAIN_LOSS)
, -1
, '-'
, '+') GAIN_LOSS_SIGN
, LICR.LINE_NUMBER LINE_NUMBER
, LICR.LANGUAGE LANGUAGE
, LICR.LEDGER_ID LEDGER_ID
, 'Y' RECP_OVERRIDE_ACCTD_AMT
, DIST.ACTIVITY_BUCKET DIST_ACTIVITY_BUCKET
FROM AR_XLA_LINES_EXTRACT LICR
, AR_XLA_LINES_EXTRACT LITRX
, AR_CASH_RECEIPTS_ALL CR
, RA_CUSTOMER_TRX_ALL CT
, (SELECT DECODE(NVL(RA.ACCTD_AMOUNT_APPLIED_FROM
, 0)
, NVL(RA.ACCTD_AMOUNT_APPLIED_TO
, 0)
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, DECODE(D.FROM_ACCTD_AMOUNT_CR
, ''
, DECODE(D.FROM_ACCTD_AMOUNT_DR
, ''
, DECODE(D.SOURCE_TYPE
, 'TAX'
, DECODE(D.SOURCE_TYPE_SECONDARY
, 'RECONCILE'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) - NVL(D.FROM_ACCTD_AMOUNT_DR
, 0))
, 'DEFERRED_TAX'
, DECODE(D.SOURCE_TYPE_SECONDARY
, 'RECONCILE'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) - NVL(D.FROM_ACCTD_AMOUNT_DR
, 0))
, DECODE(DECODE(D.SOURCE_TYPE
, 'REC'
, NVL(RA.ACCTD_AMOUNT_APPLIED_TO
, 0)+NVL(RA.ACCTD_EARNED_DISCOUNT_TAKEN
, 0)+NVL(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN
, 0)
, 'OTHER ACC'
, NVL(RA.ACCTD_AMOUNT_APPLIED_FROM
, 0)
, 'ACC'
, NVL(RA.ACCTD_AMOUNT_APPLIED_FROM
, 0)
, NVL(D.FROM_ACCTD_AMOUNT_CR
, 0)- NVL(D.FROM_ACCTD_AMOUNT_DR
, 0))
, NVL(D.ACCTD_AMOUNT_DR
, 0)*-1+NVL(D.ACCTD_AMOUNT_CR
, 0)
, NVL(RA.ACCTD_AMOUNT_APPLIED_FROM
, 0)
, NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) -NVL(D.FROM_ACCTD_AMOUNT_DR
, 0)))
, DECODE(D.SOURCE_TYPE
, 'EDISC'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'UNEDISC'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'EDISC_NON_REC_TAX'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'UNEDISC_NON_REC_TAX'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'DEFERRED_TAX'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'TAX'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'REC'
, DECODE(D.REF_MF_DIST_FLAG
, 'D'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) -NVL(D.FROM_ACCTD_AMOUNT_DR
, 0))
, NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) -NVL(D.FROM_ACCTD_AMOUNT_DR
, 0)))
, DECODE(D.SOURCE_TYPE
, 'EDISC'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'UNEDISC'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'EDISC_NON_REC_TAX'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'UNEDISC_NON_REC_TAX'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'DEFERRED_TAX'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'TAX'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'REC'
, DECODE(D.REF_MF_DIST_FLAG
, 'D'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) -NVL(D.FROM_ACCTD_AMOUNT_DR
, 0))
, NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) -NVL(D.FROM_ACCTD_AMOUNT_DR
, 0)))) FROM_AMOUNT
, DECODE(NVL(RA.ACCTD_AMOUNT_APPLIED_FROM
, 0)
, NVL(RA.ACCTD_AMOUNT_APPLIED_TO
, 0)
, TO_NUMBER(NULL)
, DECODE(D.FROM_ACCTD_AMOUNT_CR
, ''
, DECODE(D.FROM_ACCTD_AMOUNT_DR
, ''
, DECODE(D.SOURCE_TYPE
, 'REC'
, NVL(RA.ACCTD_AMOUNT_APPLIED_FROM
, 0)-NVL(RA.ACCTD_AMOUNT_APPLIED_TO
, 0)
, 0)
, (NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) -NVL(D.FROM_ACCTD_AMOUNT_DR
, 0)) - (NVL(D.ACCTD_AMOUNT_CR
, 0) -NVL(D.ACCTD_AMOUNT_DR
, 0)))
, (NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) -NVL(D.FROM_ACCTD_AMOUNT_DR
, 0)) - (NVL(D.ACCTD_AMOUNT_CR
, 0) -NVL(D.ACCTD_AMOUNT_DR
, 0))) ) GAIN_LOSS
, D.SOURCE_ID SOURCE_ID
, D.LINE_ID LINE_ID
, D.ACTIVITY_BUCKET ACTIVITY_BUCKET
, RA.ACCTD_AMOUNT_APPLIED_TO ACCTD_AMOUNT_APPLIED_TO
, RA.ACCTD_AMOUNT_APPLIED_FROM ACCTD_AMOUNT_APPLIED_FROM
FROM AR_DISTRIBUTIONS_ALL D
, AR_RECEIVABLE_APPLICATIONS_ALL RA
WHERE D.SOURCE_ID = RA.RECEIVABLE_APPLICATION_ID
AND D.SOURCE_TABLE = 'RA'
AND RA.POSTING_CONTROL_ID = -3
AND SOURCE_TYPE IN ('REC'
, 'OTHER ACC'
, 'ACC'
, 'BANK_CHARGES'
, 'ACTIVITY'
, 'FACTOR'
, 'REMITTANCE'
, 'TAX'
, 'DEFERRED_TAX'
, 'UNEDISC'
, 'EDISC'
, 'CURR_ROUND'
, 'SHORT_TERM_DEBT'
, 'EXCH_GAIN'
, 'EXCH_LOSS'
, 'EDISC_NON_REC_TAX'
, 'UNEDISC_NON_REC_TAX') ) DIST
WHERE LICR.POSTING_ENTITY = 'APP'
AND LICR.LEVEL_FLAG = 'L'
AND LICR.CASH_RECEIPT_ID IS NOT NULL
AND LICR.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND LICR.CUSTOMER_TRX_ID IS NULL
AND LICR.FROM_TO_FLAG = 'F'
AND LITRX.POSTING_ENTITY = 'APP'
AND LITRX.LEVEL_FLAG = 'L'
AND LITRX.CASH_RECEIPT_ID IS NULL
AND LITRX.CUSTOMER_TRX_ID IS NOT NULL
AND LITRX.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND LITRX.FROM_TO_FLAG = 'T'
AND LICR.HEADER_TABLE_ID = LITRX.HEADER_TABLE_ID
AND LICR.EVENT_ID = LITRX.EVENT_ID
AND DIST.SOURCE_ID = LITRX.HEADER_TABLE_ID
AND DIST.LINE_ID = LICR.LINE_ID
AND DIST.LINE_ID = LITRX.LINE_ID
AND LICR.EVENT_TYPE_CODE IN ('RECP_CREATE'
, 'RECP_UPDATE'
, 'RECP_RATE_ADJUST'
, 'RECP_REVERSE')
AND NVL(LICR.ADDITIONAL_CHAR1
, 'XX') <> 'R12_11ICASH_POST'
AND NVL(LITRX.ADDITIONAL_CHAR1
, 'XX') <> 'R12_11ICASH_POST'
AND LICR.MFAR_ADDITIONAL_ENTRY = 'N'
AND LITRX.MFAR_ADDITIONAL_ENTRY = 'N' UNION /******************** * CM APP DOC * ********************/ SELECT /*+INDEX (LINE_EXT AR_XLA_LINES_EXTRACT_N1)*/ LINE_EXT.EVENT_ID EVENT_ID
, LINE_EXT.BASE_CURRENCY_CODE BASE_CURRENCY
, LINE_EXT.LINE_ID LINE_ID
, LINE_EXT.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LINE_EXT.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LINE_EXT.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, DIST_LAYER.FROM_AMOUNT DIST_ACCTD_AMT
, LINE_EXT.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LINE_EXT.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LINE_EXT.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LINE_EXT.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, DIST_LAYER.GAIN_LOSS GAIN_LOSS_AMT
, DECODE(SIGN(DIST_LAYER.GAIN_LOSS)
, -1
, '-'
, '+') GAIN_LOSS_SIGN
, LINE_EXT.LINE_NUMBER LINE_NUMBER
, LINE_EXT.LANGUAGE LANGUAGE
, LINE_EXT.LEDGER_ID LEDGER_ID
, NULL RECP_OVERRIDE_ACCTD_AMT
, NULL DIST_ACTIVITY_BUCKET
FROM AR_XLA_LINES_EXTRACT LINE_EXT
, (SELECT NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) - NVL(D.FROM_ACCTD_AMOUNT_DR
, 0) FROM_AMOUNT
, (NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) -NVL(D.FROM_ACCTD_AMOUNT_DR
, 0)) - (NVL(D.ACCTD_AMOUNT_CR
, 0) -NVL(D.ACCTD_AMOUNT_DR
, 0)) GAIN_LOSS
, D.SOURCE_ID SOURCE_ID
, D.LINE_ID LINE_ID
FROM AR_DISTRIBUTIONS_ALL D
WHERE D.SOURCE_TABLE = 'RA'
AND D.SOURCE_TYPE IN ('REC') ) DIST_LAYER
WHERE LINE_EXT.POSTING_ENTITY = 'APP'
AND LINE_EXT.LEVEL_FLAG = 'L'
AND LINE_EXT.CUSTOMER_TRX_ID IS NOT NULL
AND LINE_EXT.FROM_TO_FLAG = 'F'
AND DIST_LAYER.LINE_ID= LINE_EXT.LINE_ID
AND LINE_EXT.EVENT_TYPE_CODE IN ('CM_CREATE'
, 'CM_UPDATE')
AND NVL(LINE_EXT.ADDITIONAL_CHAR1
, 'XX') <> 'R12_11ICASH_POST'
AND LINE_EXT.MFAR_ADDITIONAL_ENTRY = 'N' UNION SELECT /*+INDEX (LINE_EXT AR_XLA_LINES_EXTRACT_N1)*/ LINE_EXT.EVENT_ID EVENT_ID
, LINE_EXT.BASE_CURRENCY_CODE BASE_CURRENCY
, LINE_EXT.LINE_ID LINE_ID
, LINE_EXT.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LINE_EXT.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LINE_EXT.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, DIST_LAYER.FROM_AMOUNT DIST_ACCTD_AMT
, LINE_EXT.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LINE_EXT.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LINE_EXT.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LINE_EXT.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, DIST_LAYER.GAIN_LOSS GAIN_LOSS_AMT
, DECODE(SIGN(DIST_LAYER.GAIN_LOSS)
, -1
, '-'
, '+') GAIN_LOSS_SIGN
, LINE_EXT.LINE_NUMBER LINE_NUMBER
, LINE_EXT.LANGUAGE LANGUAGE
, LINE_EXT.LEDGER_ID LEDGER_ID
, NULL RECP_OVERRIDE_ACCTD_AMT
, NULL DIST_ACTIVITY_BUCKET
FROM AR_XLA_LINES_EXTRACT LINE_EXT
, (SELECT NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) - NVL(D.FROM_ACCTD_AMOUNT_DR
, 0) FROM_AMOUNT
, (NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) -NVL(D.FROM_ACCTD_AMOUNT_DR
, 0)) - (NVL(D.ACCTD_AMOUNT_CR
, 0) -NVL(D.ACCTD_AMOUNT_DR
, 0)) GAIN_LOSS
, D.SOURCE_ID SOURCE_ID
, D.LINE_ID LINE_ID
FROM AR_DISTRIBUTIONS_ALL D
WHERE D.SOURCE_TABLE = 'RA'
AND D.SOURCE_TYPE IN ('REC'
, 'ACTIVITY' ) ) DIST_LAYER
WHERE LINE_EXT.POSTING_ENTITY = 'APP'
AND LINE_EXT.LEVEL_FLAG = 'L'
AND LINE_EXT.CUSTOMER_TRX_ID IS NOT NULL
AND LINE_EXT.FROM_TO_FLAG = 'T'
AND DIST_LAYER.LINE_ID= LINE_EXT.LINE_ID
AND LINE_EXT.EVENT_TYPE_CODE IN ('CM_CREATE'
, 'CM_UPDATE')
AND NVL(LINE_EXT.ADDITIONAL_CHAR1
, 'XX') <> 'R12_11ICASH_POST'
AND LINE_EXT.MFAR_ADDITIONAL_ENTRY = 'N' /****************************** * MISC CASH DISTRIBUTIONS * ******************************/ UNION SELECT /*+INDEX (LI AR_XLA_LINES_EXTRACT_N1)*/ LI.EVENT_ID EVENT_ID
, LI.BASE_CURRENCY_CODE BASE_CURRENCY
, LI.LINE_ID LINE_ID
, LI.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LI.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LI.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, LI.ACCTD_AMOUNT DIST_ACCTD_AMT
, LI.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LI.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LI.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LI.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, NULL GAIN_LOSS_AMT
, NULL GAIN_LOSS_SIGN
, LI.LINE_NUMBER LINE_NUMBER
, LI.LANGUAGE LANGUAGE
, LI.LEDGER_ID LEDGER_ID
, NULL RECP_OVERRIDE_ACCTD_AMT
, NULL DIST_ACTIVITY_BUCKET
FROM AR_XLA_LINES_EXTRACT LI
WHERE LI.POSTING_ENTITY = 'MCD'
AND LI.LEVEL_FLAG = 'L'
AND LI.MFAR_ADDITIONAL_ENTRY = 'N' UNION /******************************* * CASH BASIS UPGRADE RECP APP * *******************************/ SELECT /*+INDEX(LICR AR_XLA_LINES_EXTRACT_N1) INDEX(LITRX AR_XLA_LINES_EXTRACT_N1)*/ LICR.EVENT_ID EVENT_ID
, LICR.BASE_CURRENCY_CODE BASE_CURRENCY
, LICR.LINE_ID LINE_ID
, LICR.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LICR.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LICR.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, D.FROM_ACCTD_AMOUNT DIST_ACCTD_AMT
, LITRX.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LITRX.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LITRX.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LITRX.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, NVL(D.FROM_ACCTD_AMOUNT
, 0) - NVL(D.ACCTD_AMOUNT
, 0) GAIN_LOSS_AMT
, DECODE(SIGN(NVL(D.FROM_ACCTD_AMOUNT
, 0)- NVL(D.ACCTD_AMOUNT
, 0))
, 1
, '+'
, '-') GAIN_LOSS_SIGN
, LICR.LINE_NUMBER LINE_NUMBER
, LICR.LANGUAGE LANGUAGE
, LICR.LEDGER_ID LEDGER_ID
, NULL RECP_OVERRIDE_ACCTD_AMT
, NULL DIST_ACTIVITY_BUCKET
FROM AR_XLA_LINES_EXTRACT LICR
, AR_XLA_LINES_EXTRACT LITRX
, AR_CASH_BASIS_DISTS_ALL D
WHERE LICR.POSTING_ENTITY = 'APP'
AND LICR.LEVEL_FLAG = 'L'
AND LICR.CASH_RECEIPT_ID IS NOT NULL
AND LICR.CUSTOMER_TRX_ID IS NULL
AND LICR.FROM_TO_FLAG = 'F'
AND LITRX.POSTING_ENTITY = 'APP'
AND LITRX.LEVEL_FLAG = 'L'
AND LITRX.CASH_RECEIPT_ID IS NULL
AND LITRX.CUSTOMER_TRX_ID IS NOT NULL
AND LITRX.FROM_TO_FLAG = 'T'
AND LICR.HEADER_TABLE_ID = LITRX.HEADER_TABLE_ID
AND LICR.EVENT_ID = LITRX.EVENT_ID
AND D.RECEIVABLE_APPLICATION_ID = LITRX.HEADER_TABLE_ID
AND D.CASH_BASIS_DISTRIBUTION_ID = LICR.LINE_ID
AND D.CASH_BASIS_DISTRIBUTION_ID = LITRX.LINE_ID
AND LICR.EVENT_TYPE_CODE IN ('RECP_CREATE'
, 'RECP_UPDATE'
, 'RECP_RATE_ADJUST'
, 'RECP_REVERSE')
AND LICR.ADDITIONAL_CHAR1 = 'R12_11ICASH_POST'
AND LITRX.ADDITIONAL_CHAR1 = 'R12_11ICASH_POST'
AND LICR.MFAR_ADDITIONAL_ENTRY = 'N'
AND LITRX.MFAR_ADDITIONAL_ENTRY = 'N' UNION /***************************** * CASH BASIS UPGRADE CM APP * *****************************/ SELECT /*+INDEX (LINE_EXT AR_XLA_LINES_EXTRACT_N1)*/ LINE_EXT.EVENT_ID EVENT_ID
, LINE_EXT.BASE_CURRENCY_CODE BASE_CURRENCY
, LINE_EXT.LINE_ID LINE_ID
, LINE_EXT.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LINE_EXT.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LINE_EXT.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, DIST_LAYER.FROM_ACCTD_AMOUNT DIST_ACCTD_AMT
, LINE_EXT.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LINE_EXT.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LINE_EXT.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LINE_EXT.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, NVL(DIST_LAYER.FROM_ACCTD_AMOUNT
, 0) - NVL(DIST_LAYER.ACCTD_AMOUNT
, 0) GAIN_LOSS_AMT
, DECODE(SIGN( NVL(DIST_LAYER.FROM_ACCTD_AMOUNT
, 0) - NVL(DIST_LAYER.ACCTD_AMOUNT
, 0))
, -1
, '-'
, '+') GAIN_LOSS_SIGN
, LINE_EXT.LINE_NUMBER LINE_NUMBER
, LINE_EXT.LANGUAGE LANGUAGE
, LINE_EXT.LEDGER_ID LEDGER_ID
, NULL RECP_OVERRIDE_ACCTD_AMT
, NULL DIST_ACTIVITY_BUCKET
FROM AR_XLA_LINES_EXTRACT LINE_EXT
, AR_CASH_BASIS_DISTS_ALL DIST_LAYER
WHERE LINE_EXT.POSTING_ENTITY = 'APP'
AND LINE_EXT.LEVEL_FLAG = 'L'
AND LINE_EXT.CUSTOMER_TRX_ID IS NOT NULL
AND LINE_EXT.FROM_TO_FLAG = 'F'
AND DIST_LAYER.CASH_BASIS_DISTRIBUTION_ID = LINE_EXT.LINE_ID
AND LINE_EXT.EVENT_TYPE_CODE IN ('CM_CREATE'
, 'CM_UPDATE')
AND LINE_EXT.ADDITIONAL_CHAR1 = 'R12_11ICASH_POST'
AND LINE_EXT.MFAR_ADDITIONAL_ENTRY = 'N' UNION SELECT /*+INDEX (LINE_EXT AR_XLA_LINES_EXTRACT_N1)*/ LINE_EXT.EVENT_ID EVENT_ID
, LINE_EXT.BASE_CURRENCY_CODE BASE_CURRENCY
, LINE_EXT.LINE_ID LINE_ID
, LINE_EXT.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LINE_EXT.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LINE_EXT.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, DIST_LAYER.FROM_ACCTD_AMOUNT DIST_ACCTD_AMT
, LINE_EXT.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LINE_EXT.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LINE_EXT.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LINE_EXT.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, NVL(DIST_LAYER.FROM_ACCTD_AMOUNT
, 0) - NVL(DIST_LAYER.ACCTD_AMOUNT
, 0) GAIN_LOSS_AMT
, DECODE(SIGN(NVL(DIST_LAYER.FROM_ACCTD_AMOUNT
, 0) - NVL(DIST_LAYER.ACCTD_AMOUNT
, 0))
, -1
, '-'
, '+') GAIN_LOSS_SIGN
, LINE_EXT.LINE_NUMBER LINE_NUMBER
, LINE_EXT.LANGUAGE LANGUAGE
, LINE_EXT.LEDGER_ID LEDGER_ID
, NULL RECP_OVERRIDE_ACCTD_AMT
, NULL DIST_ACTIVITY_BUCKET
FROM AR_XLA_LINES_EXTRACT LINE_EXT
, AR_CASH_BASIS_DISTS_ALL DIST_LAYER
WHERE LINE_EXT.POSTING_ENTITY = 'APP'
AND LINE_EXT.LEVEL_FLAG = 'L'
AND LINE_EXT.CUSTOMER_TRX_ID IS NOT NULL
AND LINE_EXT.FROM_TO_FLAG = 'T'
AND DIST_LAYER.CASH_BASIS_DISTRIBUTION_ID = LINE_EXT.LINE_ID
AND LINE_EXT.EVENT_TYPE_CODE IN ('CM_CREATE'
, 'CM_UPDATE')
AND LINE_EXT.ADDITIONAL_CHAR1 = 'R12_11ICASH_POST'
AND LINE_EXT.MFAR_ADDITIONAL_ENTRY = 'N' UNION /*MFAR ADDITIONAL ENTRIES*/ SELECT EVENT_ID EVENT_ID
, BASE_CURRENCY_CODE BASE_CURRENCY
, LINE_ID LINE_ID
, FROM_EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, FROM_EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, FROM_EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, FROM_ACCTD_AMOUNT DIST_ACCTD_AMT
, EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, FROM_ACCTD_AMOUNT - ACCTD_AMOUNT GAIN_LOSS_AMT
, '+' GAIN_LOSS_SIGN
, LINE_NUMBER LINE_NUMBER
, 'US' LANGUAGE
, LEDGER_ID LEDGER_ID
, NULL RECP_OVERRIDE_ACCTD_AMT
, NULL DIST_ACTIVITY_BUCKET
FROM AR_XLA_LINES_EXTRACT
WHERE MFAR_ADDITIONAL_ENTRY = 'Y'

Columns