View: AR_POSTED_TRANSACTIONS_V

Product:	AR - Receivables
Description:	(Release 11)
Implementation/DBA Data:	APPS.AR_POSTED_TRANSACTIONS_V

View Text

SELECT ADJ.ADJUSTMENT_ID PK_1
, ADJ.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, NVL(L.CODE_COMBINATION_ID
, ADJ.CODE_COMBINATION_ID) CCID
, L.STATUS JE_STATUS
, L.EFFECTIVE_DATE JE_EFFECTIVE_DATE
, L.PERIOD_NAME JE_PERIOD_NAME
, B.NAME JE_BATCH_NAME
, H.NAME JE_HEADER_NAME
, L.JE_LINE_NUM JE_LINE_NUM
, L.DESCRIPTION JE_DESCRIPTION
, H.DOC_SEQUENCE_VALUE JE_DOC_SEQUENCE_VALUE
, F2.NAME JE_DOC_SEQUENCE_NAME
, (NVL(L.ACCOUNTED_DR
, 0)+NVL(L.ACCOUNTED_CR
, 0)) / DECODE(NVL(L.ENTERED_DR
, 0) + NVL(L.ENTERED_CR
, 0)
, 0
, 1
, NVL(L.ENTERED_DR
, 0)+NVL(L.ENTERED_CR
, 0) ) JE_EXCHANGE_RATE
, H.CURRENCY_CODE JE_CURRENCY_CODE
, L.ENTERED_DR JE_ENTERED_DR
, L.ENTERED_CR JE_ENTERED_CR
, L.ACCOUNTED_DR JE_ACCOUNTED_DR
, L.ACCOUNTED_CR JE_ACCOUNTED_CR
, ADJ.ADJUSTMENT_NUMBER TRX_NUMBER
, TO_NUMBER(NULL) TRX_LINE_NUMBER
, ADJ.APPLY_DATE TRX_DATE
, ADJ.GL_DATE TRX_GL_DATE
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) TRX_VEND_CUST_NAME
, CS.LOCATION TRX_VEND_CUST_SITE
, ADJ.DOC_SEQUENCE_VALUE TRX_DOC_SEQUENCE_VALUE
, F.NAME TRX_DOC_SEQUENCE_NAME
, G.DESCRIPTION TRX_TRANSACTION
, CT.TRX_NUMBER TRX_ASSO_TRANSACTION
, CT.EXCHANGE_RATE TRX_EXCHANGE_RATE
, TO_NUMBER(DECODE(R.REFERENCE_9
, 'ADJ_REC'
, ADJ.AMOUNT
, NULL)) TRX_ENTERED_DR
, TO_NUMBER(DECODE(R.REFERENCE_9
, 'ADJ_ADJ'
, ADJ.AMOUNT
, NULL)) TRX_ENTERED_CR
, TO_NUMBER(DECODE(R.REFERENCE_9
, 'ADJ_REC'
, ADJ.ACCTD_AMOUNT
, NULL)) TRX_ACCOUNTED_DR
, TO_NUMBER(DECODE(R.REFERENCE_9
, 'ADJ_ADJ'
, ADJ.ACCTD_AMOUNT
, NULL)) TRX_ACCOUNTED_CR
FROM FND_DOCUMENT_SEQUENCES F2
, FND_DOCUMENT_SEQUENCES F
, GL_JE_LINES L
, GL_JE_HEADERS H
, GL_JE_BATCHES B
, GL_LOOKUPS G
, GL_IMPORT_REFERENCES R
, HZ_CUST_SITE_USES_ALL CS
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, RA_CUSTOMER_TRX_ALL CT
, AR_ADJUSTMENTS_ALL ADJ
WHERE CT.CUSTOMER_TRX_ID = ADJ.CUSTOMER_TRX_ID
AND ADJ.ADJUSTMENT_ID = TO_NUMBER(R.REFERENCE_3)
AND CUST_ACCT.CUST_ACCOUNT_ID(+) = CT.BILL_TO_CUSTOMER_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
AND CS.SITE_USE_ID(+) = CT.BILL_TO_SITE_USE_ID
AND NVL(R.REFERENCE_10
, 'NULL')= 'AR_ADJUSTMENTS'
AND L.JE_HEADER_ID = H.JE_HEADER_ID(+)
AND R.JE_HEADER_ID = L.JE_HEADER_ID(+)
AND R.JE_LINE_NUM = L.JE_LINE_NUM(+)
AND B.JE_BATCH_ID(+) = H.JE_BATCH_ID
AND F2.DOC_SEQUENCE_ID(+) = H.DOC_SEQUENCE_ID
AND F.DOC_SEQUENCE_ID(+) = CT.DOC_SEQUENCE_ID
AND G.LOOKUP_CODE(+) = R.REFERENCE_9
AND G.LOOKUP_TYPE(+) = 'SUBLDGR_DRILLDOWN_TRANS_TYPE'
AND NVL(H.ACTUAL_FLAG
, 'A') = 'A'
AND ADJ.GL_POSTED_DATE IS NOT NULL
AND ADJ.SET_OF_BOOKS_ID = L.LEDGER_ID UNION SELECT CRH.CASH_RECEIPT_HISTORY_ID PK_1
, CR.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, NVL(L.CODE_COMBINATION_ID
, CRH.ACCOUNT_CODE_COMBINATION_ID) CCID
, L.STATUS JE_STATUS
, L.EFFECTIVE_DATE JE_EFFECTIVE_DATE
, L.PERIOD_NAME JE_PERIOD_NAME
, B.NAME JE_BATCH_NAME
, H.NAME JE_HEADER_NAME
, L.JE_LINE_NUM JE_LINE_NUM
, L.DESCRIPTION JE_DESCRIPTION
, H.DOC_SEQUENCE_VALUE JE_DOC_SEQUENCE_VALUE
, F2.NAME JE_DOC_SEQUENCE_NAME
, (NVL(L.ACCOUNTED_DR
, 0) + NVL(L.ACCOUNTED_CR
, 0)) / DECODE(NVL(L.ENTERED_DR
, 0) + NVL(L.ENTERED_CR
, 0)
, 0
, 1
, NVL(L.ENTERED_DR
, 0) + NVL(L.ENTERED_CR
, 0)) JE_EXCHANGE_RATE
, H.CURRENCY_CODE JE_CURRENCY_CODE
, L.ENTERED_DR JE_ENTERED_DR
, L.ENTERED_CR JE_ENTERED_CR
, L.ACCOUNTED_DR JE_ACCOUNTED_DR
, L.ACCOUNTED_CR JE_ACCOUNTED_CR
, CR.RECEIPT_NUMBER TRX_NUMBER
, TO_NUMBER(NULL) TRX_LINE_NUMBER
, CR.RECEIPT_DATE TRX_DATE
, DECODE(R.REFERENCE_9
, 'MISC_CONFIRMATION'
, CRH.GL_DATE
, 'TRADE_REMITTANCE'
, CRH.GL_DATE
, 'TRADE_BANK_CHARGES'
, CRH.GL_DATE
, 'MISC_CASH'
, CRH.GL_DATE
, 'TRADE_CONFIRMATION'
, CRH.GL_DATE
, 'TRADE_CASH'
, CRH.GL_DATE
, 'MISC_BANK'
, CRH.GL_DATE
, 'TRADE_BANK'
, CRH.GL_DATE
, 'MISC_REV'
, CRH.REVERSAL_GL_DATE
, 'TRADE_REV'
, CRH.REVERSAL_GL_DATE
, 'MISC_BANKREV'
, CRH.REVERSAL_GL_DATE
, 'TRADE_BANKREV'
, CRH.REVERSAL_GL_DATE
, NULL
, CRH.GL_DATE) TRX_GL_DATE
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) TRX_VEND_CUST_NAME
, SU.LOCATION TRX_VEND_CUST_SITE
, CR.DOC_SEQUENCE_VALUE TRX_DOC_SEQUENCE_VALUE
, F.NAME TRX_DOC_SEQUENCE__NAME
, G.DESCRIPTION TRX_TRANSACTION
, TO_CHAR(NULL) TRX_ASSO_TRANSACTION
, CR.EXCHANGE_RATE TRX_EXCHANGE_RATE
, TO_NUMBER(DECODE(R.REFERENCE_9
, 'MISC_CONFIRMATION'
, CRH.AMOUNT
, 'TRADE_CONFIRMATION'
, L.ENTERED_DR
, 'TRADE_REMITTANCE'
, CRH.AMOUNT
, 'TRADE_BANK_CHARGES'
, CRH.AMOUNT
, 'MISC_CASH'
, L.ENTERED_DR
, 'TRADE_CASH'
, L.ENTERED_DR
, 'MISC_BANK'
, CRH.FACTOR_DISCOUNT_AMOUNT
, 'TRADE_BANK'
, CRH.FACTOR_DISCOUNT_AMOUNT
, NULL)) TRX_ENTERED_DR
, TO_NUMBER(DECODE(R.REFERENCE_9
, 'MISC_REV'
, CRH.AMOUNT
, 'TRADE_REV'
, CRH.AMOUNT
, 'TRADE_CASH'
, L.ENTERED_CR
, 'TRADE_CONFIRMATION'
, L.ENTERED_CR
, 'MISC_CASH'
, L.ENTERED_CR
, 'MISC_BANKREV'
, CRH.FACTOR_DISCOUNT_AMOUNT
, 'TRADE_BANKREV'
, CRH.FACTOR_DISCOUNT_AMOUNT
, NULL)) TRX_ENTERED_CR
, TO_NUMBER(DECODE(R.REFERENCE_9
, 'MISC_CONFIRMATION'
, CRH.AMOUNT
, 'TRADE_CONFIRMATION'
, L.ACCOUNTED_DR
, 'TRADE_REMITTANCE'
, CRH.AMOUNT
, 'TRADE_BANK_CHARGES'
, CRH.AMOUNT
, 'MISC_CASH'
, L.ACCOUNTED_DR
, 'TRADE_CASH'
, L.ACCOUNTED_DR
, 'MISC_BANK'
, CRH.FACTOR_DISCOUNT_AMOUNT
, 'TRADE_BANK'
, CRH.FACTOR_DISCOUNT_AMOUNT
, NULL)) TRX_ACCOUNTED_DR
, TO_NUMBER(DECODE(R.REFERENCE_9
, 'MISC_REV'
, CRH.AMOUNT
, 'TRADE_REV'
, CRH.AMOUNT
, 'TRADE_CASH'
, L.ACCOUNTED_CR
, 'TRADE_CONFIRMATION'
, L.ACCOUNTED_CR
, 'MISC_CASH'
, L.ACCOUNTED_CR
, 'MISC_BANKREV'
, CRH.FACTOR_DISCOUNT_AMOUNT
, 'TRADE_BANKREV'
, CRH.FACTOR_DISCOUNT_AMOUNT
, NULL)) TRX_ACCOUNTED_CR
FROM GL_JE_LINES L
, GL_JE_HEADERS H
, GL_JE_BATCHES B
, GL_LOOKUPS G
, FND_DOCUMENT_SEQUENCES F
, FND_DOCUMENT_SEQUENCES F2
, GL_IMPORT_REFERENCES R
, HZ_CUST_SITE_USES_ALL SU
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, AR_CASH_RECEIPTS_ALL CR
, AR_CASH_RECEIPT_HISTORY_ALL CRH
WHERE SU.SITE_USE_ID(+) = CR.CUSTOMER_SITE_USE_ID
AND CUST_ACCT.CUST_ACCOUNT_ID(+) = TO_NUMBER(R.REFERENCE_7)
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+) /* BUG 1015167 MODIFIED THE JOIN CONDITION. */
AND CRH.CASH_RECEIPT_ID = TO_NUMBER(SUBSTR(R.REFERENCE_2
, 0
, INSTR(R.REFERENCE_2
, 'C')-1))
AND CRH.CASH_RECEIPT_HISTORY_ID = TO_NUMBER(R.REFERENCE_3)
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND NVL(R.REFERENCE_10
, 'NULL') = 'AR_CASH_RECEIPT_HISTORY'
AND L.JE_HEADER_ID = H.JE_HEADER_ID(+)
AND R.JE_HEADER_ID = L.JE_HEADER_ID(+)
AND R.JE_LINE_NUM = L.JE_LINE_NUM(+)
AND B.JE_BATCH_ID(+) = H.JE_BATCH_ID
AND F2.DOC_SEQUENCE_ID(+) = H.DOC_SEQUENCE_ID
AND F.DOC_SEQUENCE_ID(+) = CR.DOC_SEQUENCE_ID
AND G.LOOKUP_CODE(+) = R.REFERENCE_9
AND G.LOOKUP_TYPE(+) = 'SUBLDGR_DRILLDOWN_TRANS_TYPE'
AND NVL(H.ACTUAL_FLAG
, 'A') = 'A'
AND CRH.GL_POSTED_DATE IS NOT NULL
AND CR.SET_OF_BOOKS_ID = L.LEDGER_ID UNION SELECT CTLGD.CUST_TRX_LINE_GL_DIST_ID PK_1
, CTLGD.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, NVL(L.CODE_COMBINATION_ID
, CTLGD.CODE_COMBINATION_ID) CCID
, L.STATUS JE_STATUS
, L.EFFECTIVE_DATE JE_EFFECTIVE_DATE
, L.PERIOD_NAME JE_PERIOD_NAME
, B.NAME JE_BATCH_NAME
, H.NAME JE_HEADER_NAME
, L.JE_LINE_NUM JE_LINE_NUM
, L.DESCRIPTION JE_DESCRIPTION
, H.DOC_SEQUENCE_VALUE JE_DOC_SEQUENCE_VALUE
, F2.NAME JE_DOC_SEQUENCE_NAME
, (NVL(L.ACCOUNTED_DR
, 0)+NVL(L.ACCOUNTED_CR
, 0)) / DECODE(NVL(L.ENTERED_DR
, 0) +NVL(L.ENTERED_CR
, 0)
, 0
, 1
, NVL(L.ENTERED_DR
, 0)+NVL(L.ENTERED_CR
, 0) ) JE_EXCHANGE_RATE
, H.CURRENCY_CODE JE_CURRENCY_CODE
, L.ENTERED_DR JE_ENTERED_DR
, L.ENTERED_CR JE_ENTERED_CR
, L.ACCOUNTED_DR JE_ACCOUNTED_DR
, L.ACCOUNTED_CR JE_ACCOUNTED_CR
, CT.TRX_NUMBER TRX_NUMBER
, CTL2.LINE_NUMBER TRX_LINE_NUMBER
, CT.TRX_DATE TRX_DATE
, CTLGD.GL_DATE TRX_GL_DATE
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) TRX_VEND_CUST_NAME
, CS.LOCATION TRX_VEND_CUST_SITE
, CT.DOC_SEQUENCE_VALUE TRX_DOC_SEQUENCE_VALUE
, F.NAME TRX_DOC_SEQUENCE_NAME
, G.DESCRIPTION TRX_TRANSACTION
, TO_CHAR(NULL) TRX_ASSO_TRANSACTION
, CT.EXCHANGE_RATE TRX_EXCHANGE_RATE
, TO_NUMBER(DECODE(CTLGD.AMOUNT
, NULL
, NULL
, DECODE(R.REFERENCE_9
, 'CM_REC'
, CTLGD.AMOUNT
, 'DM_REC'
, CTLGD.AMOUNT
, 'CB_REC'
, CTLGD.AMOUNT
, 'INV_REC'
, CTLGD.AMOUNT
, NULL))) TRX_ENTERED_DR
, TO_NUMBER(DECODE(CTLGD.AMOUNT
, NULL
, NULL
, DECODE(R.REFERENCE_9
, 'CM_REC'
, NULL
, 'DM_REC'
, NULL
, 'CB_REC'
, NULL
, 'INV_REC'
, NULL
, NULL
, NULL
, CTLGD.AMOUNT))) TRX_ENTERED_CR
, TO_NUMBER(DECODE(CTLGD.ACCTD_AMOUNT
, NULL
, NULL
, DECODE(R.REFERENCE_9
, 'CM_REC'
, CTLGD.ACCTD_AMOUNT
, 'DM_REC'
, CTLGD.ACCTD_AMOUNT
, 'CB_REC'
, CTLGD.ACCTD_AMOUNT
, 'INV_REC'
, CTLGD.ACCTD_AMOUNT
, NULL))) TRX_ACCOUNTED_DR
, TO_NUMBER(DECODE(CTLGD.ACCTD_AMOUNT
, NULL
, NULL
, DECODE(R.REFERENCE_9
, 'CM_REC'
, NULL
, 'DM_REC'
, NULL
, 'CB_REC'
, NULL
, 'INV_REC'
, NULL
, NULL
, NULL
, CTLGD.ACCTD_AMOUNT))) TRX_ACCOUNTED_CR
FROM GL_JE_LINES L
, GL_JE_HEADERS H
, GL_JE_BATCHES B
, GL_LOOKUPS G
, FND_DOCUMENT_SEQUENCES F
, FND_DOCUMENT_SEQUENCES F2
, GL_IMPORT_REFERENCES R
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, HZ_CUST_SITE_USES_ALL CS
, RA_CUSTOMER_TRX_LINES_ALL CTL
, RA_CUSTOMER_TRX_LINES_ALL CTL2
, RA_CUSTOMER_TRX_ALL CT
, RA_CUST_TRX_LINE_GL_DIST_ALL CTLGD
WHERE CUST_ACCT.CUST_ACCOUNT_ID(+)= CT.BILL_TO_CUSTOMER_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
AND CT.CUSTOMER_TRX_ID = CTLGD.CUSTOMER_TRX_ID
AND CTL.CUSTOMER_TRX_LINE_ID(+) = CTLGD.CUSTOMER_TRX_LINE_ID
AND NVL(CTL.LINK_TO_CUST_TRX_LINE_ID
, CTL.CUSTOMER_TRX_LINE_ID) = CTL2.CUSTOMER_TRX_LINE_ID(+)
AND CTLGD.CUST_TRX_LINE_GL_DIST_ID = TO_NUMBER(R.REFERENCE_3)
AND NVL(R.REFERENCE_10
, 'NULL')= 'RA_CUST_TRX_LINE_GL_DIST'
AND L.JE_HEADER_ID = H.JE_HEADER_ID(+)
AND R.JE_HEADER_ID = L.JE_HEADER_ID(+)
AND R.JE_LINE_NUM = L.JE_LINE_NUM(+)
AND B.JE_BATCH_ID(+) = H.JE_BATCH_ID
AND CS.SITE_USE_ID(+) = CT.BILL_TO_SITE_USE_ID
AND F.DOC_SEQUENCE_ID(+) = CT.DOC_SEQUENCE_ID
AND F2.DOC_SEQUENCE_ID(+) = H.DOC_SEQUENCE_ID
AND G.LOOKUP_CODE(+) = R.REFERENCE_9
AND G.LOOKUP_TYPE(+) = 'SUBLDGR_DRILLDOWN_TRANS_TYPE'
AND NVL(H.ACTUAL_FLAG
, 'A') = 'A'
AND CTLGD.GL_POSTED_DATE IS NOT NULL
AND CTLGD.SET_OF_BOOKS_ID = L.LEDGER_ID UNION SELECT MCD.MISC_CASH_DISTRIBUTION_ID PK_1
, MCD.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, NVL(L.CODE_COMBINATION_ID
, MCD.CODE_COMBINATION_ID) CCID
, L.STATUS JE_STATUS
, L.EFFECTIVE_DATE JE_EFFECTIVE_DATE
, L.PERIOD_NAME JE_PERIOD_NAME
, B.NAME JE_BATCH_NAME
, H.NAME JE_HEADER_NAME
, L.JE_LINE_NUM JE_LINE_NUM
, L.DESCRIPTION JE_DESCRIPTION
, H.DOC_SEQUENCE_VALUE JE_DOC_SEQUENCE_VALUE
, F2.NAME JE_DOC_SEQUENCE_NAME
, DECODE(NVL(L.ENTERED_DR
, 0)+NVL(L.ENTERED_CR
, 0)
, 0
, 1
, NVL(L.ENTERED_DR
, 0)+NVL(L.ENTERED_CR
, 0) ) JE_EXCHANGE_RATE
, H.CURRENCY_CODE JE_CURRENCY_CODE
, L.ENTERED_DR JE_ENTERED_DR
, L.ENTERED_CR JE_ENTERED_CR
, L.ACCOUNTED_DR JE_ACCOUNTED_DR
, L.ACCOUNTED_CR JE_ACCOUNTED_CR
, CR.RECEIPT_NUMBER TRX_NUMBER
, TO_NUMBER(NULL) TRX_LINE_NUMBER
, CR.RECEIPT_DATE TRX_DATE
, MCD.GL_DATE TRX_GL_DATE
, TO_CHAR(NULL) TRX_VEND_CUST_NAME
, SU.LOCATION TRX_VEND_CUST_SITE
, CR.DOC_SEQUENCE_VALUE TRX_DOC_SEQUENCE_VALUE
, F.NAME TRX_DOC_SEQUENCE_NAME
, G.DESCRIPTION TRX_TRANSACTION
, TO_CHAR(NULL) TRX_ASSO_TRANSACTION
, CR.EXCHANGE_RATE TRX_EXCHANGE_RATE
, TO_NUMBER(NULL) TRX_ENTERED_DR
, MCD.AMOUNT TRX_ENTERED_CR
, TO_NUMBER(NULL) TRX_ACCOUNTED_DR
, MCD.ACCTD_AMOUNT TRX_ACCOUNTED_CR
FROM GL_JE_LINES L
, GL_JE_HEADERS H
, GL_JE_BATCHES B
, GL_LOOKUPS G
, FND_DOCUMENT_SEQUENCES F
, FND_DOCUMENT_SEQUENCES F2
, GL_IMPORT_REFERENCES R
, HZ_CUST_SITE_USES_ALL SU
, AR_CASH_RECEIPTS_ALL CR
, AR_MISC_CASH_DISTRIBUTIONS_ALL MCD
WHERE SU.SITE_USE_ID(+) = CR.CUSTOMER_SITE_USE_ID
AND CR.CASH_RECEIPT_ID = MCD.CASH_RECEIPT_ID
AND MCD.MISC_CASH_DISTRIBUTION_ID = TO_NUMBER(R.REFERENCE_3)
AND NVL(R.REFERENCE_10
, 'NULL') = 'AR_MISC_CASH_DISTRIBUTIONS'
AND L.JE_HEADER_ID = H.JE_HEADER_ID(+)
AND R.JE_HEADER_ID = L.JE_HEADER_ID(+)
AND R.JE_LINE_NUM = L.JE_LINE_NUM(+)
AND B.JE_BATCH_ID(+) = H.JE_BATCH_ID
AND F.DOC_SEQUENCE_ID(+) = CR.DOC_SEQUENCE_ID
AND F2.DOC_SEQUENCE_ID(+) = H.DOC_SEQUENCE_ID
AND G.LOOKUP_CODE(+) = R.REFERENCE_9
AND G.LOOKUP_TYPE(+) = 'SUBLDGR_DRILLDOWN_TRANS_TYPE'
AND NVL(H.ACTUAL_FLAG
, 'A') = 'A'
AND MCD.GL_POSTED_DATE IS NOT NULL
AND MCD.SET_OF_BOOKS_ID = L.LEDGER_ID UNION SELECT RA.RECEIVABLE_APPLICATION_ID PK_1
, RA.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, NVL(L.CODE_COMBINATION_ID
, RA.CODE_COMBINATION_ID) CCID
, L.STATUS JE_STATUS
, L.EFFECTIVE_DATE JE_EFFECTIVE_DATE
, L.PERIOD_NAME JE_PERIOD_NAME
, B.NAME JE_BATCH_NAME
, H.NAME JE_HEADER_NAME
, L.JE_LINE_NUM JE_LINE_NUM
, L.DESCRIPTION JE_DESCRIPTION
, H.DOC_SEQUENCE_VALUE JE_DOC_SEQUENCE_VALUE
, F2.NAME JE_DOC_SEQUENCE_NAME
, (NVL(L.ACCOUNTED_DR
, 0)+NVL(L.ACCOUNTED_CR
, 0)) / DECODE(NVL(L.ENTERED_DR
, 0) +NVL(L.ENTERED_CR
, 0)
, 0
, 1
, NVL(L.ENTERED_DR
, 0)+NVL(L.ENTERED_CR
, 0) ) JE_EXCHANGE_RATE
, H.CURRENCY_CODE JE_CURRENCY_CODE
, L.ENTERED_DR JE_ENTERED_DR
, L.ENTERED_CR JE_ENTERED_CR
, L.ACCOUNTED_DR JE_ACCOUNTED_DR
, L.ACCOUNTED_CR JE_ACCOUNTED_CR
, DECODE(R.REFERENCE_8
, 'TRADE'
, CR.RECEIPT_NUMBER
, 'CMAPP'
, CT.TRX_NUMBER
, NULL
, DECODE(RA.APPLICATION_TYPE
, 'CASH'
, CR.RECEIPT_NUMBER
, 'CM'
, CT.TRX_NUMBER)) TRX_NUMBER
, CT_APP_LINES.LINE_NUMBER TRX_LINE_NUMBER
, TRUNC(DECODE(R.REFERENCE_8
, 'TRADE'
, CR.RECEIPT_DATE
, 'CMAPP'
, CT.TRX_DATE
, NULL
, DECODE(RA.APPLICATION_TYPE
, 'CASH'
, CR.RECEIPT_DATE
, 'CM'
, CT.TRX_DATE))) TRX_DATE
, RA.GL_DATE TRX_GL_DATE
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) TRX_VEND_CUST_NAME
, NVL(SU.LOCATION
, NVL(SU2.LOCATION
, SU3.LOCATION)) TRX_VEND_CUST_SITE
, TO_NUMBER(DECODE(R.REFERENCE_8
, 'TRADE'
, CR.DOC_SEQUENCE_VALUE
, 'CMAPP'
, CT.DOC_SEQUENCE_VALUE
, NULL
, DECODE(RA.APPLICATION_TYPE
, 'CASH'
, CR.DOC_SEQUENCE_VALUE
, 'CM'
, CT.DOC_SEQUENCE_VALUE))) TRX_DOC_SEQUENCE_VALUE
, DECODE(R.REFERENCE_8
, 'TRADE'
, F.NAME
, F3.NAME) TRX_DOC_SEQUENCE_NAME
, G.DESCRIPTION TRX_TRANSACTION
, CT_APP.TRX_NUMBER TRX_ASSO_TRANSACTION
, TO_NUMBER(DECODE(R.REFERENCE_8
, 'TRADE'
, CR.EXCHANGE_RATE
, 'CMAPP'
, CT.EXCHANGE_RATE
, NULL
, DECODE(RA.APPLICATION_TYPE
, 'CASH'
, CR.EXCHANGE_RATE
, 'CM'
, CT.EXCHANGE_RATE))) TRX_EXCHANGE_RATE
, TO_NUMBER(DECODE(R.REFERENCE_9
, 'TRADE_APP'
, DECODE(SIGN(RA.AMOUNT_APPLIED)
, -1
, -1 * RA.AMOUNT_APPLIED
, NULL)
, 'TRADE_DISC'
, DECODE(RA.EARNED_DISCOUNT_CCID
, L.CODE_COMBINATION_ID
, NVL(RA.EARNED_DISCOUNT_TAKEN
, 0)
, NVL(RA.UNEARNED_DISCOUNT_TAKEN
, 0))
, 'TRADE_DISCAPP'
, NULL
, 'TRADE_GL'
, DECODE(SIGN(RA.ACCTD_AMOUNT_APPLIED_FROM- RA.ACCTD_AMOUNT_APPLIED_TO)
, -1
, 0
, NULL)
, 'CMAPP_REC'
, RA.AMOUNT_APPLIED
, 'CMAPP_APP'
, NULL
, 'CMAPP_GL'
, DECODE(SIGN(RA.ACCTD_AMOUNT_APPLIED_FROM- RA.ACCTD_AMOUNT_APPLIED_TO)
, -1
, 0
, NULL)
, NULL)) TRX_ENTERED_DR
, TO_NUMBER(DECODE(R.REFERENCE_9
, 'TRADE_APP'
, DECODE(SIGN(RA.AMOUNT_APPLIED)
, -1
, NULL
, RA.AMOUNT_APPLIED)
, 'TRADE_DISC'
, NULL
, 'TRADE_DISCAPP'
, NVL(RA.EARNED_DISCOUNT_TAKEN
, 0)+ NVL(RA.UNEARNED_DISCOUNT_TAKEN
, 0)
, 'TRADE_GL'
, DECODE(SIGN(RA.ACCTD_AMOUNT_APPLIED_FROM- RA.ACCTD_AMOUNT_APPLIED_TO)
, -1
, NULL
, 0)
, 'CMAPP_REC'
, NULL
, 'CMAPP_APP'
, RA.AMOUNT_APPLIED
, 'CMAPP_GL'
, DECODE(SIGN(RA.ACCTD_AMOUNT_APPLIED_FROM- RA.ACCTD_AMOUNT_APPLIED_TO)
, -1
, NULL
, 0)
, NULL)) TRX_ENTERED_CR
, TO_NUMBER(DECODE(R.REFERENCE_9
, 'TRADE_APP'
, DECODE(R.REFERENCE_6
, NULL
, DECODE(SIGN(RA.ACCTD_AMOUNT_APPLIED_FROM)
, -1
, -1 * RA.ACCTD_AMOUNT_APPLIED_FROM
, NULL)
, DECODE(SIGN(RA.ACCTD_AMOUNT_APPLIED_TO)
, -1
, -1 * RA.ACCTD_AMOUNT_APPLIED_TO
, NULL))
, 'TRADE_DISC'
, DECODE(RA.EARNED_DISCOUNT_CCID
, L.CODE_COMBINATION_ID
, NVL(RA.ACCTD_EARNED_DISCOUNT_TAKEN
, 0)
, NVL(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN
, 0))
, 'TRADE_DISCAPP'
, NULL
, 'TRADE_GL'
, NULL
, 'CMAPP_REC'
, RA.ACCTD_AMOUNT_APPLIED_FROM
, 'CMAPP_APP'
, NULL
, 'CMAPP_GL'
, NULL
, NULL)) TRX_ACCOUNTED_DR
, TO_NUMBER(DECODE(R.REFERENCE_9
, 'TRADE_APP'
, DECODE(R.REFERENCE_6
, NULL
, DECODE(SIGN(RA.ACCTD_AMOUNT_APPLIED_FROM)
, -1
, NULL
, RA.ACCTD_AMOUNT_APPLIED_FROM)
, DECODE(SIGN(RA.ACCTD_AMOUNT_APPLIED_TO)
, -1
, NULL
, RA.ACCTD_AMOUNT_APPLIED_TO))
, 'TRADE_DISC'
, NULL
, 'TRADE_DISCAPP'
, NVL(RA.ACCTD_EARNED_DISCOUNT_TAKEN
, 0)+ NVL(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN
, 0)
, 'TRADE_GL'
, RA.ACCTD_AMOUNT_APPLIED_FROM- RA.ACCTD_AMOUNT_APPLIED_TO
, 'CMAPP_REC'
, NULL
, 'CMAPP_APP'
, RA.ACCTD_AMOUNT_APPLIED_FROM
, 'CMAPP_GL'
, RA.ACCTD_AMOUNT_APPLIED_FROM- RA.ACCTD_AMOUNT_APPLIED_TO
, NULL)) TRX_ACCOUNTED_CR
FROM GL_JE_LINES L
, GL_JE_HEADERS H
, GL_JE_BATCHES B
, GL_LOOKUPS G
, FND_DOCUMENT_SEQUENCES F2
, FND_DOCUMENT_SEQUENCES F3
, FND_DOCUMENT_SEQUENCES F
, RA_CUSTOMER_TRX_LINES_ALL CT_APP_LINES
, RA_CUSTOMER_TRX_ALL CT
, RA_CUSTOMER_TRX_ALL CT_APP
, HZ_CUST_SITE_USES_ALL SU
, HZ_CUST_SITE_USES_ALL SU2
, HZ_CUST_SITE_USES_ALL SU3
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, GL_IMPORT_REFERENCES R
, AR_CASH_RECEIPTS_ALL CR
, AR_RECEIVABLE_APPLICATIONS_ALL RA
WHERE CT_APP_LINES.CUSTOMER_TRX_LINE_ID(+) = RA.APPLIED_CUSTOMER_TRX_LINE_ID
AND CT_APP.CUSTOMER_TRX_ID(+) = RA.APPLIED_CUSTOMER_TRX_ID
AND CUST_ACCT.CUST_ACCOUNT_ID(+) = TO_NUMBER(R.REFERENCE_7)
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
AND SU.SITE_USE_ID(+) = CR.CUSTOMER_SITE_USE_ID
AND SU2.SITE_USE_ID(+) = CT.BILL_TO_SITE_USE_ID
AND SU3.SITE_USE_ID(+) = CT_APP.BILL_TO_SITE_USE_ID
AND CT.CUSTOMER_TRX_ID(+) = RA.CUSTOMER_TRX_ID
AND CR.CASH_RECEIPT_ID(+) = RA.CASH_RECEIPT_ID
AND RA.RECEIVABLE_APPLICATION_ID = TO_NUMBER(R.REFERENCE_3)
AND NVL(R.REFERENCE_10
, 'NULL') = 'AR_RECEIVABLE_APPLICATIONS'
AND L.JE_HEADER_ID = H.JE_HEADER_ID(+)
AND R.JE_HEADER_ID = L.JE_HEADER_ID(+)
AND R.JE_LINE_NUM = L.JE_LINE_NUM(+)
AND B.JE_BATCH_ID(+) = H.JE_BATCH_ID
AND F2.DOC_SEQUENCE_ID(+) = H.DOC_SEQUENCE_ID
AND G.LOOKUP_CODE(+) = R.REFERENCE_9
AND G.LOOKUP_TYPE(+) = 'SUBLDGR_DRILLDOWN_TRANS_TYPE'
AND NVL(H.ACTUAL_FLAG
, 'A') = 'A'
AND F.DOC_SEQUENCE_ID(+) = CR.DOC_SEQUENCE_ID
AND F3.DOC_SEQUENCE_ID(+) = CT.DOC_SEQUENCE_ID
AND RA.GL_POSTED_DATE IS NOT NULL
AND RA.SET_OF_BOOKS_ID = L.LEDGER_ID

Columns