View: AR_DISTRIBUTIONS_L_V

Product:	AR - Receivables
Description:	distribution line extract
Implementation/DBA Data:	APPS.AR_DISTRIBUTIONS_L_V

View Text

SELECT /*+INDEX(L AR_XLA_LINES_EXTRACT_N1) LEADING(L)*/ L.EVENT_ID EVENT_ID
, DAT.LINE_ID DIST_LINE_ID
, 'AR_DISTRIBUTIONS_ALL' DISTRIBUTION_TYPE
, DAT.CODE_COMBINATION_ID DIST_CODE_COMBINATION_ID
, DAT.SOURCE_ID DIST_SOURCE_ID
, DAT.SOURCE_ID_SECONDARY DIST_SOURCE_ID_SECONDARY
, DAT.SOURCE_TABLE DIST_SOURCE_TABLE
, DAT.SOURCE_TABLE_SECONDARY DIST_SOURCE_TABLE_SECONDARY
, DAT.SOURCE_TYPE DIST_SOURCE_TYPE
, DAT.SOURCE_TYPE_SECONDARY DIST_SOURCE_TYPE_SECONDARY
, DAT.TAX_LINK_ID DIST_TAX_LINK_ID
, NVL(DAT.AMOUNT_CR
, 0) - NVL(DAT.AMOUNT_DR
, 0) DIST_ENT_AMT_FROM
, DAT.CURRENCY_CODE DIST_CURRENCY_CODE_FROM
, NVL(DAT.AMOUNT_CR
, 0) - NVL(DAT.AMOUNT_DR
, 0) DIST_ENT_AMT
, DAT.CURRENCY_CODE DIST_CURRENCY_CODE
, L.LINE_NUMBER LINE_NUMBER
, L.LANGUAGE LANGUAGE
, L.LEDGER_ID LEDGER_ID
, L.PAIRED_CCID DIST_PAIRED_CCID
, 'REC' DIST_PAIRED_SOURCE_TYPE
, DECODE(DAT.THIRD_PARTY_ID
, NULL
, NULL
, 'C') DIST_PARTY_TYPE
, DAT.THIRD_PARTY_ID DIST_PARTY_ID
, TO_NUMBER(DECODE(DAT.THIRD_PARTY_ID
, NULL
, NULL
, DAT.THIRD_PARTY_SUB_ID)) DIST_PARTY_SITE_ID
, DAT.REF_DIST_CCID DIST_REF_DIST_CCID
, 'N'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DAT.REF_MF_DIST_FLAG DIST_REF_MF_DIST_FLAG
, TO_NUMBER(NULL) GAIN_LOSS_CCID
, NULL CREATE_REC_FLAG
, NULL CM_APP_TRX_NUMBER
, NULL CM_APP_TRX_TYPE_NAME
, NULL CM_APP_TRX_DOC_SEQ_VALUE
FROM AR_DISTRIBUTIONS_ALL DAT
, AR_XLA_LINES_EXTRACT L
WHERE L.POSTING_ENTITY = 'ADJ'
AND L.SOURCE_ID = DAT.SOURCE_ID
AND L.SOURCE_TABLE = DAT.SOURCE_TABLE
AND L.LINE_ID = DAT.LINE_ID
AND L.LEVEL_FLAG = 'L'
AND L.MFAR_ADDITIONAL_ENTRY = 'N' UNION SELECT /*+INDEX(L AR_XLA_LINES_EXTRACT_N1) LEADING(L)*/ L.EVENT_ID EVENT_ID
, DAF.LINE_ID DIST_LINE_ID
, 'AR_DISTRIBUTIONS_ALL' DISTRIBUTION_TYPE
, DAF.CODE_COMBINATION_ID DIST_CODE_COMBINATION_ID
, DAF.SOURCE_ID DIST_SOURCE_ID
, DAF.SOURCE_ID_SECONDARY DIST_SOURCE_ID_SECONDARY
, DAF.SOURCE_TABLE DIST_SOURCE_TABLE
, DAF.SOURCE_TABLE_SECONDARY DIST_SOURCE_TABLE_SECONDARY
, DAF.SOURCE_TYPE DIST_SOURCE_TYPE
, DAF.SOURCE_TYPE_SECONDARY DIST_SOURCE_TYPE_SECONDARY
, DAF.TAX_LINK_ID DIST_TAX_LINK_ID
, NVL(DAF.AMOUNT_CR
, 0) - NVL(DAF.AMOUNT_DR
, 0) DIST_ENT_AMT_FROM
, DAF.CURRENCY_CODE DIST_CURRENCY_CODE_FROM
, NVL(DAF.AMOUNT_CR
, 0) - NVL(DAF.AMOUNT_DR
, 0) DIST_ENT_AMT
, DAF.CURRENCY_CODE DIST_CURRENCY_CODE
, L.LINE_NUMBER LINE_NUMBER
, L.LANGUAGE LANGUAGE
, L.LEDGER_ID LEDGER_ID
, NULL DIST_PAIRED_CCID
, NVL(L.CRH_PRV_STATUS
, 'UNAPP') DIST_PAIRED_SOURCE_TYPE
, 'C' DIST_PARTY_TYPE
, DAF.THIRD_PARTY_ID DIST_PARTY_ID
, TO_NUMBER(DECODE(DAF.THIRD_PARTY_ID
, NULL
, NULL
, DAF.THIRD_PARTY_SUB_ID)) DIST_PARTY_SITE_ID
, DAF.REF_DIST_CCID DIST_REF_DIST_CCID
, 'N'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DAF.REF_MF_DIST_FLAG DIST_REF_MF_DIST_FLAG
, TO_NUMBER(NULL) GAIN_LOSS_CCID
, NULL CREATE_REC_FLAG
, NULL CM_APP_TRX_NUMBER
, NULL CM_APP_TRX_TYPE_NAME
, NULL CM_APP_TRX_DOC_SEQ_VALUE
FROM AR_DISTRIBUTIONS_ALL DAF
, AR_XLA_LINES_EXTRACT L
WHERE L.POSTING_ENTITY = 'CRH'
AND L.SOURCE_ID = DAF.SOURCE_ID
AND L.SOURCE_TABLE = DAF.SOURCE_TABLE
AND L.LINE_ID = DAF.LINE_ID
AND L.LEVEL_FLAG = 'L'
AND L.MFAR_ADDITIONAL_ENTRY = 'N' UNION SELECT /*+INDEX(L AR_XLA_LINES_EXTRACT_N1) LEADING(L)*/ L.EVENT_ID EVENT_ID
, DAF.LINE_ID DIST_LINE_ID
, 'AR_DISTRIBUTIONS_ALL' DISTRIBUTION_TYPE
, DAF.CODE_COMBINATION_ID DIST_CODE_COMBINATION_ID
, DAF.SOURCE_ID DIST_SOURCE_ID
, DAF.SOURCE_ID_SECONDARY DIST_SOURCE_ID_SECONDARY
, DAF.SOURCE_TABLE DIST_SOURCE_TABLE
, DAF.SOURCE_TABLE_SECONDARY DIST_SOURCE_TABLE_SECONDARY
, DAF.SOURCE_TYPE DIST_SOURCE_TYPE
, DAF.SOURCE_TYPE_SECONDARY DIST_SOURCE_TYPE_SECONDARY
, DAF.TAX_LINK_ID DIST_TAX_LINK_ID
, NVL(DAF.AMOUNT_CR
, 0) - NVL(DAF.AMOUNT_DR
, 0) DIST_ENT_AMT_FROM
, DAF.CURRENCY_CODE DIST_CURRENCY_CODE_FROM
, NVL(DAF.AMOUNT_CR
, 0) - NVL(DAF.AMOUNT_DR
, 0) DIST_ENT_AMT
, DAF.CURRENCY_CODE DIST_CURRENCY_CODE
, L.LINE_NUMBER LINE_NUMBER
, L.LANGUAGE LANGUAGE
, L.LEDGER_ID LEDGER_ID
, L.PAIRED_CCID DIST_PAIRED_CCID
, 'UNAPP' DIST_PAIRED_SOURCE_TYPE
, DECODE(DAF.THIRD_PARTY_ID
, NULL
, NULL
, 'C') DIST_PARTY_TYPE
, DAF.THIRD_PARTY_ID DIST_PARTY_ID
, TO_NUMBER(DECODE(DAF.THIRD_PARTY_ID
, NULL
, NULL
, DAF.THIRD_PARTY_SUB_ID)) DIST_PARTY_SITE_ID
, DAF.REF_DIST_CCID DIST_REF_DIST_CCID
, 'N'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL DIST_REF_MF_DIST_FLAG
, TO_NUMBER(NULL) GAIN_LOSS_CCID
, NULL CREATE_REC_FLAG
, NULL CM_APP_TRX_NUMBER
, NULL CM_APP_TRX_TYPE_NAME
, NULL CM_APP_TRX_DOC_SEQ_VALUE
FROM AR_DISTRIBUTIONS_ALL DAF
, AR_XLA_LINES_EXTRACT L
WHERE L.POSTING_ENTITY = 'TH'
AND L.SOURCE_ID = DAF.SOURCE_ID
AND L.SOURCE_TABLE = DAF.SOURCE_TABLE
AND L.LINE_ID = DAF.LINE_ID
AND L.LEVEL_FLAG = 'L'
AND L.MFAR_ADDITIONAL_ENTRY = 'N' UNION SELECT /*+INDEX(LICR AR_XLA_LINES_EXTRACT_N1) LEADING(LICR)*/ LICR.EVENT_ID EVENT_ID
, DAT.LINE_ID DIST_LINE_ID
, 'AR_DISTRIBUTIONS_ALL' DISTRIBUTION_TYPE
, DAT.CODE_COMBINATION_ID DIST_CODE_COMBINATION_ID
, DAT.SOURCE_ID DIST_SOURCE_ID
, DAT.SOURCE_ID_SECONDARY DIST_SOURCE_ID_SECONDARY
, DAT.SOURCE_TABLE DIST_SOURCE_TABLE
, DAT.SOURCE_TABLE_SECONDARY DIST_SOURCE_TABLE_SECONDARY
, DAT.SOURCE_TYPE DIST_SOURCE_TYPE
, DAT.SOURCE_TYPE_SECONDARY DIST_SOURCE_TYPE_SECONDARY
, DAT.TAX_LINK_ID DIST_TAX_LINK_ID
, DECODE(DAT.FROM_AMOUNT_CR
, ''
, DECODE(DAT.FROM_AMOUNT_DR
, ''
, DECODE(DAT.SOURCE_TYPE
, 'REC'
, DECODE(RA.AMOUNT_APPLIED + NVL(RA.EARNED_DISCOUNT_TAKEN
, 0)+NVL(RA.UNEARNED_DISCOUNT_TAKEN
, 0)
, NVL(DAT.AMOUNT_DR
, 0)*-1+NVL(DAT.AMOUNT_CR
, 0)
, NVL(RA.AMOUNT_APPLIED_FROM
, RA.AMOUNT_APPLIED)
, NVL(DAT.FROM_AMOUNT_CR
, 0) - NVL(DAT.FROM_AMOUNT_DR
, 0))
, NVL(DAT.FROM_AMOUNT_CR
, 0) - NVL(DAT.FROM_AMOUNT_DR
, 0))
, NVL(DAT.FROM_AMOUNT_CR
, 0) - NVL(DAT.FROM_AMOUNT_DR
, 0))
, NVL(DAT.FROM_AMOUNT_CR
, 0) - NVL(DAT.FROM_AMOUNT_DR
, 0)) DIST_ENT_AMT_FROM
, CR.CURRENCY_CODE DIST_CURRENCY_CODE_FROM
, NVL(DAT.AMOUNT_CR
, 0)- NVL(DAT.AMOUNT_DR
, 0) DIST_ENT_AMT
, DAT.CURRENCY_CODE DIST_CURRENCY_CODE
, LICR.LINE_NUMBER LINE_NUMBER
, LICR.LANGUAGE LANGUAGE
, LICR.LEDGER_ID LEDGER_ID
, NULL DIST_PAIRED_CCID
, 'UNAPP' DIST_PAIRED_SOURCE_TYPE
, 'C' DIST_PARTY_TYPE
, DAT.THIRD_PARTY_ID DIST_PARTY_ID
, TO_NUMBER(DECODE(DAT.THIRD_PARTY_ID
, NULL
, NULL
, DAT.THIRD_PARTY_SUB_ID)) DIST_PARTY_SITE_ID
, DAT.REF_DIST_CCID DIST_REF_DIST_CCID
, 'N'
, DECODE(SIGN(NVL(RA.ACCTD_AMOUNT_APPLIED_FROM
, 0)-NVL(RA.ACCTD_AMOUNT_APPLIED_TO
, 0))
, 1
, ARP_XLA_EXTRACT_MAIN_PKG.GET_GLR_CCID(RA.RECEIVABLE_APPLICATION_ID
, 'EXCH_GAIN')
, TO_NUMBER(NULL) )
, DECODE(SIGN(NVL(RA.ACCTD_AMOUNT_APPLIED_FROM
, 0)-NVL(RA.ACCTD_AMOUNT_APPLIED_TO
, 0))
, -1
, ARP_XLA_EXTRACT_MAIN_PKG.GET_GLR_CCID(RA.RECEIVABLE_APPLICATION_ID
, 'EXCH_LOSS')
, TO_NUMBER(NULL) )
, DAT.REF_MF_DIST_FLAG DIST_REF_MF_DIST_FLAG
, TO_NUMBER(NULL) GAIN_LOSS_CCID
, DECODE(DAT.SOURCE_TYPE
, 'EDISC'
, 'Y'
, 'UNEDISC'
, 'Y'
, 'EDISC_NON_REC_TAX'
, 'Y'
, 'UNEDISC_NON_REC_TAX'
, 'Y'
, 'DEFERRED_TAX'
, DECODE(DAT.SOURCE_TYPE_SECONDARY
, 'PAYMENT'
, NULL
, 'RECONCILE'
, NULL
, 'Y')
, 'TAX'
, DECODE(DAT.SOURCE_TYPE_SECONDARY
, 'PAYMENT'
, NULL
, 'RECONCILE'
, NULL
, 'Y')) CREATE_REC_FLAG
, NULL CM_APP_TRX_NUMBER
, NULL CM_APP_TRX_TYPE_NAME
, NULL CM_APP_TRX_DOC_SEQ_VALUE
FROM AR_XLA_LINES_EXTRACT LICR
, AR_DISTRIBUTIONS_ALL DAT
, AR_RECEIVABLE_APPLICATIONS_ALL RA
, AR_CASH_RECEIPTS_ALL CR
WHERE LICR.POSTING_ENTITY = 'APP'
AND LICR.LEVEL_FLAG = 'L'
AND LICR.SOURCE_ID = DAT.SOURCE_ID
AND LICR.SOURCE_TABLE = DAT.SOURCE_TABLE
AND LICR.LINE_ID = DAT.LINE_ID
AND DAT.SOURCE_TYPE NOT IN ('EXCH_GAIN'
, 'EXCH_LOSS')
AND RA.RECEIVABLE_APPLICATION_ID = LICR.HEADER_TABLE_ID
AND RA.STATUS = 'APP'
AND RA.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(RA.UPGRADE_METHOD
, 'XX') NOT IN ('R12_11ICASH_POST')
AND LICR.FROM_TO_FLAG = 'T'
AND LICR.MFAR_ADDITIONAL_ENTRY = 'N' UNION SELECT /*+INDEX(LICR AR_XLA_LINES_EXTRACT_N1) LEADING(LICR)*/ LICR.EVENT_ID EVENT_ID
, DAT.LINE_ID DIST_LINE_ID
, 'AR_DISTRIBUTIONS_ALL' DISTRIBUTION_TYPE
, DAT.CODE_COMBINATION_ID DIST_CODE_COMBINATION_ID
, DAT.SOURCE_ID DIST_SOURCE_ID
, DAT.SOURCE_ID_SECONDARY DIST_SOURCE_ID_SECONDARY
, DAT.SOURCE_TABLE DIST_SOURCE_TABLE
, DAT.SOURCE_TABLE_SECONDARY DIST_SOURCE_TABLE_SECONDARY
, DAT.SOURCE_TYPE DIST_SOURCE_TYPE
, DAT.SOURCE_TYPE_SECONDARY DIST_SOURCE_TYPE_SECONDARY
, DAT.TAX_LINK_ID DIST_TAX_LINK_ID
, NVL(DAT.FROM_AMOUNT_CR
, 0) - NVL(DAT.FROM_AMOUNT_DR
, 0) DIST_ENT_AMT_FROM
, CR.CURRENCY_CODE DIST_CURRENCY_CODE_FROM
, NVL(DAT.AMOUNT_CR
, 0)- NVL(DAT.AMOUNT_DR
, 0) DIST_ENT_AMT
, APPTRX.INVOICE_CURRENCY_CODE DIST_CURRENCY_CODE
, LICR.LINE_NUMBER LINE_NUMBER
, LICR.LANGUAGE LANGUAGE
, LICR.LEDGER_ID LEDGER_ID
, NULL DIST_PAIRED_CCID
, 'UNAPP' DIST_PAIRED_SOURCE_TYPE
, 'C' DIST_PARTY_TYPE
, DAT.THIRD_PARTY_ID DIST_PARTY_ID
, TO_NUMBER(DECODE(DAT.THIRD_PARTY_ID
, NULL
, NULL
, DAT.THIRD_PARTY_SUB_ID)) DIST_PARTY_SITE_ID
, DAT.REF_DIST_CCID DIST_REF_DIST_CCID
, 'N'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DAT.REF_MF_DIST_FLAG DIST_REF_MF_DIST_FLAG
, TO_NUMBER(NULL) GAIN_LOSS_CCID
, NULL CREATE_REC_FLAG
, NULL CM_APP_TRX_NUMBER
, NULL CM_APP_TRX_TYPE_NAME
, NULL CM_APP_TRX_DOC_SEQ_VALUE
FROM AR_XLA_LINES_EXTRACT LICR
, AR_DISTRIBUTIONS_ALL DAT
, AR_RECEIVABLE_APPLICATIONS_ALL RA
, AR_CASH_RECEIPTS_ALL CR
, RA_CUSTOMER_TRX_ALL APPTRX
WHERE LICR.POSTING_ENTITY = 'APP'
AND LICR.LEVEL_FLAG = 'L'
AND LICR.CASH_RECEIPT_ID IS NULL
AND LICR.CUSTOMER_TRX_ID IS NOT NULL
AND LICR.SOURCE_ID = DAT.SOURCE_ID
AND LICR.SOURCE_TABLE = DAT.SOURCE_TABLE
AND LICR.LINE_ID = DAT.LINE_ID
AND DAT.SOURCE_TYPE IN ('EXCH_GAIN'
, 'EXCH_LOSS')
AND RA.RECEIVABLE_APPLICATION_ID = LICR.HEADER_TABLE_ID
AND RA.STATUS = 'APP'
AND NVL(RA.UPGRADE_METHOD
, 'XX') NOT IN ('R12_11ICASH_POST')
AND LICR.EVENT_TYPE_CODE IN ('RECP_CREATE'
, 'RECP_UPDATE'
, 'RECP_RATE_ADJUST'
, 'RECP_REVERSE')
AND RA.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND RA.APPLIED_CUSTOMER_TRX_ID = APPTRX.CUSTOMER_TRX_ID
AND LICR.FROM_TO_FLAG = 'T'
AND LICR.MFAR_ADDITIONAL_ENTRY = 'N' UNION SELECT /*+INDEX(LICM AR_XLA_LINES_EXTRACT_N1) LEADING(LICM)*/ LICM.EVENT_ID EVENT_ID
, DAT.LINE_ID DIST_LINE_ID
, 'AR_DISTRIBUTIONS_ALL' DISTRIBUTION_TYPE
, DAT.CODE_COMBINATION_ID DIST_CODE_COMBINATION_ID
, DAT.SOURCE_ID DIST_SOURCE_ID
, DAT.SOURCE_ID_SECONDARY DIST_SOURCE_ID_SECONDARY
, DAT.SOURCE_TABLE DIST_SOURCE_TABLE
, DAT.SOURCE_TABLE_SECONDARY DIST_SOURCE_TABLE_SECONDARY
, DAT.SOURCE_TYPE DIST_SOURCE_TYPE
, DAT.SOURCE_TYPE_SECONDARY DIST_SOURCE_TYPE_SECONDARY
, DAT.TAX_LINK_ID DIST_TAX_LINK_ID
, NVL(DAT.FROM_AMOUNT_CR
, 0)-NVL(DAT.FROM_AMOUNT_DR
, 0) DIST_ENT_AMT_FROM
, CM.INVOICE_CURRENCY_CODE DIST_CURRENCY_CODE_FROM
, NVL(DAT.AMOUNT_CR
, 0) - NVL(DAT.AMOUNT_DR
, 0) DIST_ENT_AMT
, DAT.CURRENCY_CODE DIST_CURRENCY_CODE
, LICM.LINE_NUMBER LINE_NUMBER
, LICM.LANGUAGE LANGUAGE
, LICM.LEDGER_ID LEDGER_ID
, NULL DIST_PAIRED_CCID
, 'UNAPP' DIST_PAIRED_SOURCE_TYPE
, DECODE(DAT.THIRD_PARTY_ID
, NULL
, NULL
, 'C') DIST_PARTY_TYPE
, DAT.THIRD_PARTY_ID DIST_PARTY_ID
, TO_NUMBER(DECODE(DAT.THIRD_PARTY_ID
, NULL
, NULL
, DAT.THIRD_PARTY_SUB_ID)) DIST_PARTY_SITE_ID
, DAT.REF_DIST_CCID DIST_REF_DIST_CCID
, 'N'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DAT.REF_MF_DIST_FLAG DIST_REF_MF_DIST_FLAG
, TO_NUMBER(NULL) GAIN_LOSS_CCID
, NULL CREATE_REC_FLAG
, INVCM.TRX_NUMBER CM_APP_TRX_NUMBER
, CTTAF.NAME CM_APP_TRX_TYPE_NAME
, INVCM.DOC_SEQUENCE_VALUE CM_APP_TRX_DOC_SEQ_VALUE
FROM AR_XLA_LINES_EXTRACT LICM
, AR_DISTRIBUTIONS_ALL DAT
, AR_RECEIVABLE_APPLICATIONS_ALL RA
, RA_CUSTOMER_TRX_ALL CM
, RA_CUSTOMER_TRX_ALL INVCM
, RA_CUST_TRX_TYPES_ALL CTTAF
WHERE LICM.POSTING_ENTITY = 'APP'
AND LICM.LEVEL_FLAG = 'L'
AND LICM.CUSTOMER_TRX_ID IS NOT NULL
AND LICM.SOURCE_ID = DAT.SOURCE_ID
AND LICM.SOURCE_TABLE = DAT.SOURCE_TABLE
AND LICM.LINE_ID = DAT.LINE_ID
AND DAT.SOURCE_TYPE NOT IN ('EXCH_GAIN'
, 'EXCH_LOSS')
AND RA.RECEIVABLE_APPLICATION_ID = LICM.HEADER_TABLE_ID
AND RA.STATUS IN ('APP'
, 'ACTIVITY')
AND NVL(RA.UPGRADE_METHOD
, 'XX') NOT IN ('R12_11ICASH_POST')
AND RA.CUSTOMER_TRX_ID IS NOT NULL
AND (RA.APPLIED_CUSTOMER_TRX_ID = LICM.CUSTOMER_TRX_ID OR RA.CUSTOMER_TRX_ID = LICM.CUSTOMER_TRX_ID)
AND RA.CUSTOMER_TRX_ID = CM.CUSTOMER_TRX_ID
AND LICM.EVENT_TYPE_CODE IN ('CM_CREATE'
, 'CM_UPDATE')
AND LICM.MFAR_ADDITIONAL_ENTRY = 'N'
AND RA.APPLIED_CUSTOMER_TRX_ID = INVCM.CUSTOMER_TRX_ID(+)
AND INVCM.CUST_TRX_TYPE_ID = CTTAF.CUST_TRX_TYPE_ID (+)
AND INVCM.ORG_ID = CTTAF.ORG_ID (+) UNION SELECT /*+INDEX(L AR_XLA_LINES_EXTRACT_N1) LEADING(L)*/ L.EVENT_ID EVENT_ID
, DAT.LINE_ID DIST_LINE_ID
, 'AR_DISTRIBUTIONS_ALL' DISTRIBUTION_TYPE
, DAT.CODE_COMBINATION_ID DIST_CODE_COMBINATION_ID
, DAT.SOURCE_ID DIST_SOURCE_ID
, DAT.SOURCE_ID_SECONDARY DIST_SOURCE_ID_SECONDARY
, DAT.SOURCE_TABLE DIST_SOURCE_TABLE
, DAT.SOURCE_TABLE_SECONDARY DIST_SOURCE_TABLE_SECONDARY
, DAT.SOURCE_TYPE DIST_SOURCE_TYPE
, DAT.SOURCE_TYPE_SECONDARY DIST_SOURCE_TYPE_SECONDARY
, DAT.TAX_LINK_ID DIST_TAX_LINK_ID
, NVL(DAT.AMOUNT_CR
, 0) - NVL(DAT.AMOUNT_DR
, 0) DIST_ENT_AMT_FROM
, DAT.CURRENCY_CODE DIST_CURRENCY_CODE_FROM
, NVL(DAT.AMOUNT_CR
, 0) - NVL(DAT.AMOUNT_DR
, 0) DIST_ENT_AMT
, DAT.CURRENCY_CODE DIST_CURRENCY_CODE
, L.LINE_NUMBER LINE_NUMBER
, L.LANGUAGE LANGUAGE
, L.LEDGER_ID LEDGER_ID
, NULL DIST_PAIRED_CCID
, NULL DIST_PAIRED_SOURCE_TYPE
, DECODE(DAT.THIRD_PARTY_ID
, NULL
, NULL
, 'C') DIST_PARTY_TYPE
, DAT.THIRD_PARTY_ID DIST_PARTY_ID
, TO_NUMBER(DECODE(DAT.THIRD_PARTY_ID
, NULL
, NULL
, DAT.THIRD_PARTY_SUB_ID)) DIST_PARTY_SITE_ID
, DAT.REF_DIST_CCID DIST_REF_DIST_CCID
, 'N'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL DIST_REF_MF_DIST_FLAG
, TO_NUMBER(NULL) GAIN_LOSS_CCID
, NULL CREATE_REC_FLAG
, NULL CM_APP_TRX_NUMBER
, NULL CM_APP_TRX_TYPE_NAME
, NULL CM_APP_TRX_DOC_SEQ_VALUE
FROM AR_DISTRIBUTIONS_ALL DAT
, AR_XLA_LINES_EXTRACT L
WHERE L.POSTING_ENTITY = 'MCD'
AND L.SOURCE_ID = DAT.SOURCE_ID
AND L.SOURCE_TABLE = DAT.SOURCE_TABLE
AND L.LINE_ID = DAT.LINE_ID
AND L.LEVEL_FLAG = 'L'
AND L.MFAR_ADDITIONAL_ENTRY = 'N' UNION SELECT /*+INDEX(LICR AR_XLA_LINES_EXTRACT_N1) LEADING(LICR)*/ LICR.EVENT_ID EVENT_ID
, DAT.CASH_BASIS_DISTRIBUTION_ID DIST_LINE_ID
, 'AR_CASH_BASIS_DISTS_ALL' DISTRIBUTION_TYPE
, DAT.CODE_COMBINATION_ID DIST_CODE_COMBINATION_ID
, DAT.RECEIVABLE_APPLICATION_ID DIST_SOURCE_ID
, DAT.RECEIVABLE_APPLICATION_ID_CASH DIST_SOURCE_ID_SECONDARY
, 'RA' DIST_SOURCE_TABLE
, NULL DIST_SOURCE_TABLE_SECONDARY
, 'REC' DIST_SOURCE_TYPE
, DAT.ACTIVITY_BUCKET DIST_SOURCE_TYPE_SECONDARY
, NULL DIST_TAX_LINK_ID
, DAT.FROM_AMOUNT DIST_ENT_AMT_FROM
, CR.CURRENCY_CODE DIST_CURRENCY_CODE_FROM
, DAT.AMOUNT DIST_ENT_AMT
, DAT.CURRENCY_CODE DIST_CURRENCY_CODE
, LICR.LINE_NUMBER LINE_NUMBER
, LICR.LANGUAGE LANGUAGE
, LICR.LEDGER_ID LEDGER_ID
, NULL DIST_PAIRED_CCID
, 'UNAPP' DIST_PAIRED_SOURCE_TYPE
, DECODE(CR.PAY_FROM_CUSTOMER
, NULL
, NULL
, 'C') DIST_PARTY_TYPE
, CR.PAY_FROM_CUSTOMER DIST_PARTY_ID
, NULL DIST_PARTY_SITE_ID
, DAT.CODE_COMBINATION_ID DIST_REF_DIST_CCID
, 'N'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL DIST_REF_MF_DIST_FLAG
, TO_NUMBER(NULL) GAIN_LOSS_CCID
, NULL CREATE_REC_FLAG
, NULL CM_APP_TRX_NUMBER
, NULL CM_APP_TRX_TYPE_NAME
, NULL CM_APP_TRX_DOC_SEQ_VALUE
FROM AR_XLA_LINES_EXTRACT LICR
, AR_CASH_BASIS_DISTS_ALL DAT
, AR_RECEIVABLE_APPLICATIONS_ALL RA
, AR_CASH_RECEIPTS_ALL CR
WHERE LICR.POSTING_ENTITY = 'APP'
AND LICR.LEVEL_FLAG = 'L'
AND LICR.SOURCE_ID = DAT.RECEIVABLE_APPLICATION_ID
AND LICR.LINE_ID = DAT.CASH_BASIS_DISTRIBUTION_ID
AND RA.RECEIVABLE_APPLICATION_ID = LICR.HEADER_TABLE_ID
AND RA.STATUS = 'APP'
AND RA.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND RA.UPGRADE_METHOD = 'R12_11ICASH_POST'
AND LICR.FROM_TO_FLAG = 'T'
AND LICR.MFAR_ADDITIONAL_ENTRY = 'N' UNION SELECT /*+INDEX(LICM AR_XLA_LINES_EXTRACT_N1) LEADING(LICM)*/ LICM.EVENT_ID EVENT_ID
, DAT.CASH_BASIS_DISTRIBUTION_ID DIST_LINE_ID
, 'AR_CASH_BASIS_DISTS_ALL' DISTRIBUTION_TYPE
, DAT.CODE_COMBINATION_ID DIST_CODE_COMBINATION_ID
, DAT.RECEIVABLE_APPLICATION_ID DIST_SOURCE_ID
, DAT.RECEIVABLE_APPLICATION_ID_CASH DIST_SOURCE_ID_SECONDARY
, 'RA' DIST_SOURCE_TABLE
, NULL DIST_SOURCE_TABLE_SECONDARY
, 'REC' DIST_SOURCE_TYPE
, DAT.ACTIVITY_BUCKET DIST_SOURCE_TYPE_SECONDARY
, NULL DIST_TAX_LINK_ID
, DAT.FROM_AMOUNT DIST_ENT_AMT_FROM
, CM.INVOICE_CURRENCY_CODE DIST_CURRENCY_CODE_FROM
, DAT.AMOUNT DIST_ENT_AMT
, DAT.CURRENCY_CODE DIST_CURRENCY_CODE
, LICM.LINE_NUMBER LINE_NUMBER
, LICM.LANGUAGE LANGUAGE
, LICM.LEDGER_ID LEDGER_ID
, NULL DIST_PAIRED_CCID
, 'UNAPP' DIST_PAIRED_SOURCE_TYPE
, DECODE(CM.BILL_TO_CUSTOMER_ID
, NULL
, NULL
, 'C') DIST_PARTY_TYPE
, CM.BILL_TO_CUSTOMER_ID DIST_PARTY_ID
, NULL DIST_PARTY_SITE_ID
, DAT.CODE_COMBINATION_ID DIST_REF_DIST_CCID
, 'N'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL DIST_REF_MF_DIST_FLAG
, TO_NUMBER(NULL) GAIN_LOSS_CCID
, NULL CREATE_REC_FLAG
, NULL CM_APP_TRX_NUMBER
, NULL CM_APP_TRX_TYPE_NAME
, NULL CM_APP_TRX_DOC_SEQ_VALUE
FROM AR_XLA_LINES_EXTRACT LICM
, AR_CASH_BASIS_DISTS_ALL DAT
, AR_RECEIVABLE_APPLICATIONS_ALL RA
, RA_CUSTOMER_TRX_ALL CM
WHERE LICM.POSTING_ENTITY = 'APP'
AND LICM.LEVEL_FLAG = 'L'
AND LICM.CUSTOMER_TRX_ID IS NOT NULL
AND LICM.SOURCE_ID = DAT.RECEIVABLE_APPLICATION_ID
AND LICM.LINE_ID = DAT.CASH_BASIS_DISTRIBUTION_ID
AND RA.RECEIVABLE_APPLICATION_ID = LICM.HEADER_TABLE_ID
AND RA.STATUS = 'APP'
AND RA.UPGRADE_METHOD = 'R12_11ICASH_POST'
AND RA.CUSTOMER_TRX_ID IS NOT NULL
AND (RA.APPLIED_CUSTOMER_TRX_ID = LICM.CUSTOMER_TRX_ID OR RA.CUSTOMER_TRX_ID = LICM.CUSTOMER_TRX_ID)
AND RA.CUSTOMER_TRX_ID = CM.CUSTOMER_TRX_ID
AND LICM.EVENT_TYPE_CODE IN ('CM_CREATE'
, 'CM_UPDATE')
AND LICM.MFAR_ADDITIONAL_ENTRY = 'N' UNION SELECT EVENT_ID EVENT_ID
, LINE_ID DIST_LINE_ID
, 'MFAR_DISTRIBUTIONS_ALL' DISTRIBUTION_TYPE
, REF_CTLGD_CCID DIST_CODE_COMBINATION_ID
, SOURCE_ID DIST_SOURCE_ID
, NULL DIST_SOURCE_ID_SECONDARY
, SOURCE_TABLE DIST_SOURCE_TABLE
, NULL DIST_SOURCE_TABLE_SECONDARY
, DECODE(CRH_STATUS
, 'BANK_CHARGES'
, 'BANK_CHARGES'
, 'REMITTED'
, 'REMITTANCE'
, 'CONFIRMED'
, 'CONFIRMATION'
, 'CLEARED'
, DECODE(SOURCE_TABLE
, 'MCD'
, 'MISCCASH'
, 'CASH')) DIST_SOURCE_TYPE
, ADDITIONAL_CHAR1 DIST_SOURCE_TYPE_SECONDARY
, NULL DIST_TAX_LINK_ID
, FROM_AMOUNT DIST_ENT_AMT_FROM
, FROM_CURRENCY_CODE DIST_CURRENCY_CODE_FROM
, AMOUNT DIST_ENT_AMT
, TO_CURRENCY_CODE DIST_CURRENCY_CODE
, LINE_NUMBER LINE_NUMBER
, 'US' LANGUAGE
, LEDGER_ID LEDGER_ID
, NULL DIST_PAIRED_CCID
, CRH_STATUS DIST_PAIRED_SOURCE_TYPE
, DECODE(THIRD_PARTY_ID
, NULL
, NULL
, 'C') DIST_PARTY_TYPE
, THIRD_PARTY_ID DIST_PARTY_ID
, THIRD_PARTY_SITE_ID DIST_PARTY_SITE_ID
, REF_DIST_CCID DIST_REF_DIST_CCID
, 'Y'
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL DIST_REF_MF_DIST_FLAG
, TO_NUMBER(NULL) GAIN_LOSS_CCID
, NULL CREATE_REC_FLAG
, NULL CM_APP_TRX_NUMBER
, NULL CM_APP_TRX_TYPE_NAME
, NULL CM_APP_TRX_DOC_SEQ_VALUE
FROM AR_XLA_LINES_EXTRACT
WHERE MFAR_ADDITIONAL_ENTRY = 'Y'

Columns