VIEW: APPS.AR_POSTED_TRANSACTIONS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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