View: AR_AEL_GL_REC_V_OLD

View Text

SELECT /* $HEADER: ARAEGREC.SQL 115.8 99/09/28 11:12:18 MSABAPAT NOSHIP $ */ JEH.JE_HEADER_ID JE_HEADER_ID
, R.JE_LINE_NUM JE_LINE_NUM
, 222 APPLICATION_ID
, CR.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, CR.ORG_ID ORG_ID
, CR.TYPE TRX_CLASS
, L1.MEANING TRX_CLASS_NAME
, CR.RECEIPT_METHOD_ID TRX_TYPE_N
, RM.NAME TRX_TYPE_NAME
, CR.RECEIPT_NUMBER TRX_NUMBER_DISPLAYED
, CR.RECEIPT_NUMBER TRX_NUMBER_C
, CR.RECEIPT_DATE TRX_DATE
, CR.COMMENTS COMMENTS
, CR.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, SEQ.NAME DOC_SEQUENCE_NAME
, CR.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, 'CR' TRX_HDR_TABLE
, CR.CASH_RECEIPT_ID TRX_HDR_ID
, ARD.SOURCE_TYPE ACCT_LINE_TYPE
, L3.MEANING ACCT_LINE_TYPE_NAME
, ARD.CODE_COMBINATION_ID CODE_COMBINATION_ID
, ARD.CURRENCY_CODE CURRENCY_CODE
, ARD.AMOUNT_DR ENTERED_DR
, ARD.AMOUNT_CR ENTERED_CR
, ARD.ACCTD_AMOUNT_DR ACCOUNTED_DR
, ARD.ACCTD_AMOUNT_CR ACCOUNTED_CR
, ARD.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, ARD.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, ARD.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, 'C' THIRD_PARTY_TYPE
, ARD.THIRD_PARTY_ID THIRD_PARTY_ID
, C.CUSTOMER_NUMBER THIRD_PARTY_NUMBER
, C.CUSTOMER_NAME THIRD_PARTY_NAME
, ARD.THIRD_PARTY_SUB_ID THIRD_PARTY_SUB_ID
, SU.LOCATION THIRD_PARTY_SUB_NAME
, CRH.GL_DATE ACCOUNTING_DATE
, L2.MEANING GL_TRANSFER_STATUS_NAME
, DECODE(CRH.POSTABLE_FLAG
, 'N'
, 'Y'
, 'N') GL_TRANSFER_STATUS
, ARD.SOURCE_TABLE SOURCE_TABLE
, ARD.SOURCE_ID SOURCE_ID
, ARD.LINE_ID AEL_ID
, 'ARD' AEL_TABLE
, /* LINE REF: TRANS CLASS + ' ' + TRANS NUMBER */ L1.MEANING||' '||CR.RECEIPT_NUMBER AE_LINE_REFERENCE
, RPAD(CR.RECEIPT_NUMBER
, 30) AE_LINE_REFERENCE_INTERNAL
, ARD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, ARD.LAST_UPDATED_BY LAST_UPDATED_BY
, ARD.CREATION_DATE CREATION_DATE
, ARD.CREATED_BY CREATED_BY
, ARD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, /* THE FOLLOWING COLUMNS ARE SPECIFIC TO AR RECEIPTS */ 'ACCRUAL' ACCOUNTING_BASIS
, VAT.TAX_CODE TAX_CODE
, ARD.TAX_CODE_ID TAX_CODE_ID
, ARD.TAX_LINK_ID TAX_LINK_ID
, ARD.TAXABLE_ENTERED_DR TAXABLE_ENTERED_DR
, ARD.TAXABLE_ENTERED_CR TAXABLE_ENTERED_CR
, ARD.TAXABLE_ACCOUNTED_DR TAXABLE_ACCOUNTED_DR
, ARD.TAXABLE_ACCOUNTED_CR TAXABLE_ACCOUNTED_CR
, CR.CURRENCY_CODE TRX_HDR_CURRENCY
, APBA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME
, CR.REVERSAL_COMMENTS REVERSAL_COMMENTS
, CR.REVERSAL_DATE REVERSAL_DATE
, TO_DATE(NULL) APPLICATION_DATE
, CR.DEPOSIT_DATE PAYMENT_DEPOSIT_DATE
, NULL APPLIED_TO_TRX_HDR_TABLE
, TO_NUMBER(NULL) APPLIED_TO_TRX_HDR_ID
, NULL APPLIED_TO_TRX_HDR_NUMBER_C
, NULL APPLIED_TO_TRX_HDR_NUMBER_DISP
, NULL APPLIED_TO_TRX_HDR_CURRENCY
, TO_DATE(NULL) APPLIED_TO_TRX_HDR_DATE
, NULL APPLIED_TO_TRX_LINE_TYPE_NAME
, NULL APPLIED_TO_TRX_LINE_TYPE
, TO_NUMBER(NULL) APPLIED_TO_TRX_LINE_NUMBER
, RCVTRX.NAME AR_ACTIVITY_NAME
, NULL DISTRIBUTION_SET_NAME
FROM AR_LOOKUPS L1
, AR_LOOKUPS L2
, AR_LOOKUPS L3
, FND_DOCUMENT_SEQUENCES SEQ
, GL_DAILY_CONVERSION_TYPES GLCT
, AP_BANK_ACCOUNTS_ALL APBA
, RA_CUSTOMERS C
, RA_SITE_USES_ALL SU
, AR_RECEIVABLES_TRX_ALL RCVTRX
, AR_VAT_TAX_ALL VAT
, AR_RECEIPT_METHODS RM
, AR_DISTRIBUTIONS_ALL ARD
, AR_CASH_RECEIPT_HISTORY_ALL CRH
, AR_CASH_RECEIPTS_ALL CR
, GL_IMPORT_REFERENCES R
, GL_JE_HEADERS JEH
WHERE L1.LOOKUP_TYPE = 'PAYMENT_CATEGORY_TYPE'
AND L1.LOOKUP_CODE = CR.TYPE AND L2.LOOKUP_TYPE = 'YES/NO' AND L2.LOOKUP_CODE = DECODE(CRH.POSTABLE_FLAG
, 'N'
, 'Y'
, 'N') AND L3.LOOKUP_TYPE = 'DISTRIBUTION_SOURCE_TYPE'
AND L3.LOOKUP_CODE = ARD.SOURCE_TYPE
AND CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID AND NVL(CRH.ORG_ID
, -99) = NVL(CR.ORG_ID
, -99) AND ARD.SOURCE_TABLE = 'CRH' AND ARD.SOURCE_ID = CRH.CASH_RECEIPT_HISTORY_ID AND NVL(ARD.ORG_ID
, -99) = NVL(CRH.ORG_ID
, -99) AND GLCT.CONVERSION_TYPE(+) = ARD.CURRENCY_CONVERSION_TYPE
AND CR.DOC_SEQUENCE_ID = SEQ.DOC_SEQUENCE_ID (+) AND CR.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID AND ARD.TAX_CODE_ID = VAT.VAT_TAX_ID(+)
AND NVL(ARD.ORG_ID
, -99) = NVL(VAT.ORG_ID(+)
, -99) AND ARD.THIRD_PARTY_ID = C.CUSTOMER_ID(+)
AND ARD.THIRD_PARTY_SUB_ID = SU.SITE_USE_ID(+)
AND NVL(ARD.ORG_ID
, -99) = NVL(SU.ORG_ID(+)
, -99) AND CR.REMITTANCE_BANK_ACCOUNT_ID = APBA.BANK_ACCOUNT_ID(+) AND NVL(CR.ORG_ID
, -99) = NVL(APBA.ORG_ID(+)
, -99) AND CR.RECEIVABLES_TRX_ID = RCVTRX.RECEIVABLES_TRX_ID(+) AND NVL(CR.ORG_ID
, -99) = NVL(RCVTRX.ORG_ID(+)
, -99) AND ARD.LINE_ID = TO_NUMBER(R.REFERENCE_3) AND R.JE_HEADER_ID = JEH.JE_HEADER_ID
AND JEH.JE_CATEGORY IN ('MISC RECEIPTS'
, 'TRADE RECEIPTS'
, 'RATE ADJUSTMENTS'
, 'CROSS CURRENCY') UNION ALL /*------------------------------------------------------------------------+ TRADE RECEIPT APPLICATIONS(NON-CASH AE LINES) JE CATEGORIES: TRADE RECEIPTS
, RATE ADJUSTMENTS
, CROSS CURRENCY +------------------------------------------------------------------------*/ SELECT JEH.JE_HEADER_ID JE_HEADER_ID
, R.JE_LINE_NUM JE_LINE_NUM
, 222 APPLICATION_ID
, CR.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, CR.ORG_ID ORG_ID
, CR.TYPE TRX_CLASS
, L1.MEANING TRX_CLASS_NAME
, CR.RECEIPT_METHOD_ID TRX_TYPE_N
, RM.NAME TRX_TYPE_NAME
, CR.RECEIPT_NUMBER TRX_NUMBER_DISPLAYED
, CR.RECEIPT_NUMBER TRX_NUMBER_C
, CR.RECEIPT_DATE TRX_DATE
, CR.COMMENTS COMMENTS
, CR.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, SEQ.NAME DOC_SEQUENCE_NAME
, CR.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, 'CR' TRX_HDR_TABLE
, CR.CASH_RECEIPT_ID TRX_HDR_ID
, ARD.SOURCE_TYPE ACCT_LINE_TYPE
, L6.MEANING ACCT_LINE_TYPE_NAME
, ARD.CODE_COMBINATION_ID CODE_COMBINATION_ID
, ARD.CURRENCY_CODE CURRENCY_CODE
, ARD.AMOUNT_DR ENTERED_DR
, ARD.AMOUNT_CR ENTERED_CR
, ARD.ACCTD_AMOUNT_DR ACCOUNTED_DR
, ARD.ACCTD_AMOUNT_CR ACCOUNTED_CR
, ARD.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, ARD.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, ARD.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, 'C' THIRD_PARTY_TYPE
, ARD.THIRD_PARTY_ID THIRD_PARTY_ID
, C.CUSTOMER_NUMBER THIRD_PARTY_NUMBER
, C.CUSTOMER_NAME THIRD_PARTY_NAME
, ARD.THIRD_PARTY_SUB_ID THIRD_PARTY_SUB_ID
, SU.LOCATION THIRD_PARTY_SUB_NAME
, RA.GL_DATE ACCOUNTING_DATE
, L2.MEANING GL_TRANSFER_STATUS_NAME
, DECODE(NVL(RA.POSTABLE
, 'Y')
, 'Y'
, 'N'
, 'Y') GL_TRANSFER_STATUS
, ARD.SOURCE_TABLE SOURCE_TABLE
, ARD.SOURCE_ID SOURCE_ID
, ARD.LINE_ID AEL_ID
, 'ARD' AEL_TABLE
, /* LINE REF: IF APPLIED TO TRANS 'APPLIED TO ' + ' ' + APPLIED TO TRANS CLASS + ' ' + APPLIED TO TRANS NUMBER ELSE TRANS CLASS + ' ' + TRANS NUMBER */ DECODE(RA.APPLIED_CUSTOMER_TRX_ID
, NULL
, L1.MEANING||' '||CR.RECEIPT_NUMBER
, L5.MEANING||' '||L4.MEANING||' '|| CT.TRX_NUMBER) AE_LINE_REFERENCE
, DECODE(RA.APPLIED_CUSTOMER_TRX_ID
, NULL
, RPAD(CR.RECEIPT_NUMBER
, 30)
, RPAD(CT.TRX_NUMBER
, 20)) AE_LINE_REFERENCE_INTERNAL
, ARD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, ARD.LAST_UPDATED_BY LAST_UPDATED_BY
, ARD.CREATION_DATE CREATION_DATE
, ARD.CREATED_BY CREATED_BY
, ARD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, /* THE FOLLOWING COLUMNS ARE SPECIFIC TO AR ADJUSTMENTS */ 'ACCRUAL' ACCOUNTING_BASIS
, VAT.TAX_CODE TAX_CODE
, ARD.TAX_CODE_ID TAX_CODE_ID
, ARD.TAX_LINK_ID TAX_LINK_ID
, ARD.TAXABLE_ENTERED_DR TAXABLE_ENTERED_DR
, ARD.TAXABLE_ENTERED_CR TAXABLE_ENTERED_CR
, ARD.TAXABLE_ACCOUNTED_DR TAXABLE_ACCOUNTED_DR
, ARD.TAXABLE_ACCOUNTED_CR TAXABLE_ACCOUNTED_CR
, CR.CURRENCY_CODE TRX_HDR_CURRENCY
, APBA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME
, CR.REVERSAL_COMMENTS REVERSAL_COMMENTS
, CR.REVERSAL_DATE REVERSAL_DATE
, RA.APPLY_DATE APPLICATION_DATE
, CR.DEPOSIT_DATE PAYMENT_DEPOSIT_DATE
, 'CT' APPLIED_TO_TRX_HDR_TABLE
, RA.APPLIED_CUSTOMER_TRX_ID APPLIED_TO_TRX_HDR_ID
, CT.TRX_NUMBER APPLIED_TO_TRX_HDR_NUMBER_C
, CT.TRX_NUMBER APPLIED_TO_TRX_HDR_NUMBER_DISP
, CT.INVOICE_CURRENCY_CODE APPLIED_TO_TRX_HDR_CURRENCY
, CT.TRX_DATE APPLIED_TO_TRX_HDR_DATE
, L3.MEANING APPLIED_TO_TRX_LINE_TYPE_NAME
, CTL.LINE_TYPE APPLIED_TO_TRX_LINE_TYPE
, CTL.LINE_NUMBER APPLIED_TO_TRX_LINE_NUMBER
, RCVTRX.NAME AR_ACTIVITY_NAME
, NULL DISTRIBUTION_SET_NAME
FROM AR_LOOKUPS L1
, AR_LOOKUPS L2
, AR_LOOKUPS L3
, AR_LOOKUPS L4
, AR_LOOKUPS L5
, AR_LOOKUPS L6
, FND_DOCUMENT_SEQUENCES SEQ
, GL_DAILY_CONVERSION_TYPES GLCT
, AP_BANK_ACCOUNTS_ALL APBA
, RA_CUSTOMERS C
, RA_SITE_USES_ALL SU
, RA_CUST_TRX_TYPES_ALL CTT
, AR_VAT_TAX_ALL VAT
, AR_RECEIVABLES_TRX_ALL RCVTRX
, AR_RECEIPT_METHODS RM
, RA_CUSTOMER_TRX_ALL CT
, RA_CUSTOMER_TRX_LINES_ALL CTL
, AR_DISTRIBUTIONS_ALL ARD
, AR_RECEIVABLE_APPLICATIONS_ALL RA
, AR_CASH_RECEIPTS_ALL CR
, GL_IMPORT_REFERENCES R
, GL_JE_HEADERS JEH
WHERE L1.LOOKUP_TYPE = 'PAYMENT_CATEGORY_TYPE'
AND L1.LOOKUP_CODE = CR.TYPE AND L2.LOOKUP_TYPE = 'YES/NO' AND L2.LOOKUP_CODE = DECODE(NVL(RA.POSTABLE
, 'Y')
, 'Y'
, 'N'
, 'Y') AND L3.LOOKUP_TYPE(+) = 'STD_LINE_TYPE' AND L3.LOOKUP_CODE(+) = CTL.LINE_TYPE AND L4.LOOKUP_TYPE(+) = 'INV/CM'
AND L4.LOOKUP_CODE(+) = CTT.TYPE
AND L5.LOOKUP_TYPE = 'VIEW_ACCOUNTING'
AND L5.LOOKUP_CODE = 'APPLIED_TO'
AND L6.LOOKUP_TYPE = 'DISTRIBUTION_SOURCE_TYPE'
AND L6.LOOKUP_CODE = ARD.SOURCE_TYPE
AND RA.APPLICATION_TYPE = 'CASH' AND NVL(RA.POSTABLE
, 'Y') = 'Y' AND RA.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID AND NVL(RA.ORG_ID
, -99) = NVL(CR.ORG_ID
, -99) AND ARD.SOURCE_TABLE = 'RA' AND ARD.SOURCE_ID = RA.RECEIVABLE_APPLICATION_ID AND NVL(ARD.ORG_ID
, -99) = NVL(RA.ORG_ID
, -99) AND GLCT.CONVERSION_TYPE(+) = ARD.CURRENCY_CONVERSION_TYPE
AND CR.DOC_SEQUENCE_ID = SEQ.DOC_SEQUENCE_ID (+) AND CR.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID AND ARD.TAX_CODE_ID = VAT.VAT_TAX_ID(+)
AND NVL(ARD.ORG_ID
, -99) = NVL(VAT.ORG_ID(+)
, -99) AND ARD.THIRD_PARTY_ID = C.CUSTOMER_ID(+)
AND ARD.THIRD_PARTY_SUB_ID = SU.SITE_USE_ID(+)
AND NVL(ARD.ORG_ID
, -99) = NVL(SU.ORG_ID(+)
, -99) AND CR.REMITTANCE_BANK_ACCOUNT_ID = APBA.BANK_ACCOUNT_ID(+) AND NVL(CR.ORG_ID
, -99) = NVL(APBA.ORG_ID(+)
, -99) AND CR.RECEIVABLES_TRX_ID = RCVTRX.RECEIVABLES_TRX_ID(+) AND NVL(CR.ORG_ID
, -99) = NVL(RCVTRX.ORG_ID(+)
, -99) AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID(+)
AND NVL(CT.ORG_ID
, -99) = NVL(CTT.ORG_ID(+)
, -99)
AND RA.APPLIED_CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID(+) AND NVL(RA.ORG_ID
, -99) = NVL(CT.ORG_ID(+)
, -99)
AND RA.APPLIED_CUSTOMER_TRX_LINE_ID = CTL.CUSTOMER_TRX_LINE_ID(+) AND NVL(RA.ORG_ID
, -99) = NVL(CTL.ORG_ID(+)
, -99)
AND ARD.LINE_ID = TO_NUMBER(R.REFERENCE_3) AND R.JE_HEADER_ID = JEH.JE_HEADER_ID
AND JEH.JE_CATEGORY IN ('TRADE RECEIPTS'
, 'RATE ADJUSTMENTS'
, 'CROSS CURRENCY') UNION ALL /*------------------------------------------------------------------------+ MISC CASH DISTRIBUTIONS(NON-CASH
AND TAX AE LINES) JE CATEGORIES: MISC RECEIPTS
, RATE ADJUSTMENTS +------------------------------------------------------------------------*/ SELECT JEH.JE_HEADER_ID JE_HEADER_ID
, R.JE_LINE_NUM JE_LINE_NUM
, 222 APPLICATION_ID
, CR.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, CR.ORG_ID ORG_ID
, CR.TYPE TRX_CLASS
, L1.MEANING TRX_CLASS_NAME
, CR.RECEIPT_METHOD_ID TRX_TYPE_N
, RM.NAME TRX_TYPE_NAME
, CR.RECEIPT_NUMBER TRX_NUMBER_DISPLAYED
, CR.RECEIPT_NUMBER TRX_NUMBER_C
, CR.RECEIPT_DATE TRX_DATE
, CR.COMMENTS COMMENTS
, CR.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, SEQ.NAME DOC_SEQUENCE_NAME
, CR.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, 'CR' TRX_HDR_TABLE
, CR.CASH_RECEIPT_ID TRX_HDR_ID
, ARD.SOURCE_TYPE ACCT_LINE_TYPE
, L3.MEANING ACCT_LINE_TYPE_NAME
, ARD.CODE_COMBINATION_ID CODE_COMBINATION_ID
, ARD.CURRENCY_CODE CURRENCY_CODE
, ARD.AMOUNT_DR ENTERED_DR
, ARD.AMOUNT_CR ENTERED_CR
, ARD.ACCTD_AMOUNT_DR ACCOUNTED_DR
, ARD.ACCTD_AMOUNT_CR ACCOUNTED_CR
, ARD.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, ARD.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, ARD.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, 'C' THIRD_PARTY_TYPE
, ARD.THIRD_PARTY_ID THIRD_PARTY_ID
, NULL THIRD_PARTY_NUMBER
, NULL THIRD_PARTY_NAME
, ARD.THIRD_PARTY_SUB_ID THIRD_PARTY_SUB_ID
, NULL THIRD_PARTY_SUB_NAME
, MCD.GL_DATE ACCOUNTING_DATE
, L2.MEANING GL_TRANSFER_STATUS_NAME
, DECODE(MCD.POSTING_CONTROL_ID
, -3
, 'N'
, 'Y') GL_TRANSFER_STATUS
, ARD.SOURCE_TABLE SOURCE_TABLE
, ARD.SOURCE_ID SOURCE_ID
, ARD.LINE_ID AEL_ID
, 'ARD' AEL_TABLE
, /* LINE REF: TRANS CLASS + ' ' + TRANS NUMBER */ L1.MEANING||' '||CR.RECEIPT_NUMBER AE_LINE_REFERENCE
, RPAD(CR.RECEIPT_NUMBER
, 30) AE_LINE_REFERENCE_INTERNAL
, ARD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, ARD.LAST_UPDATED_BY LAST_UPDATED_BY
, ARD.CREATION_DATE CREATION_DATE
, ARD.CREATED_BY CREATED_BY
, ARD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, /* THE FOLLOWING COLUMNS ARE SPECIFIC TO AR ADJUSTMENTS */ 'ACCRUAL' ACCOUNTING_BASIS
, VAT.TAX_CODE TAX_CODE
, ARD.TAX_CODE_ID TAX_CODE_ID
, ARD.TAX_LINK_ID TAX_LINK_ID
, ARD.TAXABLE_ENTERED_DR TAXABLE_ENTERED_DR
, ARD.TAXABLE_ENTERED_CR TAXABLE_ENTERED_CR
, ARD.TAXABLE_ACCOUNTED_DR TAXABLE_ACCOUNTED_DR
, ARD.TAXABLE_ACCOUNTED_CR TAXABLE_ACCOUNTED_CR
, CR.CURRENCY_CODE TRX_HDR_CURRENCY
, APBA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME
, CR.REVERSAL_COMMENTS REVERSAL_COMMENTS
, CR.REVERSAL_DATE REVERSAL_DATE
, TO_DATE(NULL) APPLICATION_DATE
, CR.DEPOSIT_DATE PAYMENT_DEPOSIT_DATE
, NULL APPLIED_TO_TRX_HDR_TABLE
, TO_NUMBER(NULL) APPLIED_TO_TRX_HDR_ID
, NULL APPLIED_TO_TRX_HDR_NUMBER_C
, NULL APPLIED_TO_TRX_HDR_NUMBER_DISP
, NULL APPLIED_TO_TRX_HDR_CURRENCY
, TO_DATE(NULL) APPLIED_TO_TRX_HDR_DATE
, NULL APPLIED_TO_TRX_LINE_TYPE_NAME
, NULL APPLIED_TO_TRX_LINE_TYPE
, TO_NUMBER(NULL) APPLIED_TO_TRX_LINE_NUMBER
, RCVTRX.NAME AR_ACTIVITY_NAME
, DSET.DISTRIBUTION_SET_NAME DISTRIBUTION_SET_NAME
FROM AR_LOOKUPS L1
, AR_LOOKUPS L2
, AR_LOOKUPS L3
, FND_DOCUMENT_SEQUENCES SEQ
, GL_DAILY_CONVERSION_TYPES GLCT
, AP_BANK_ACCOUNTS_ALL APBA
, AR_RECEIVABLES_TRX_ALL RCVTRX
, AR_DISTRIBUTION_SETS_ALL DSET
, AR_VAT_TAX_ALL VAT
, AR_RECEIPT_METHODS RM
, AR_DISTRIBUTIONS_ALL ARD
, AR_MISC_CASH_DISTRIBUTIONS_ALL MCD
, AR_CASH_RECEIPTS_ALL CR
, GL_IMPORT_REFERENCES R
, GL_JE_HEADERS JEH
WHERE L1.LOOKUP_TYPE = 'PAYMENT_CATEGORY_TYPE'
AND L1.LOOKUP_CODE = CR.TYPE AND L2.LOOKUP_TYPE = 'YES/NO' AND L2.LOOKUP_CODE = DECODE(MCD.POSTING_CONTROL_ID
, -3
, 'N'
, 'Y') AND L3.LOOKUP_TYPE = 'DISTRIBUTION_SOURCE_TYPE'
AND L3.LOOKUP_CODE = ARD.SOURCE_TYPE
AND MCD.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID AND NVL(MCD.ORG_ID
, -99) = NVL(CR.ORG_ID
, -99) AND ARD.SOURCE_TABLE = 'MCD' AND ARD.SOURCE_ID = MCD.MISC_CASH_DISTRIBUTION_ID AND NVL(ARD.ORG_ID
, -99) = NVL(MCD.ORG_ID
, -99) AND ARD.TAX_CODE_ID = VAT.VAT_TAX_ID(+)
AND NVL(ARD.ORG_ID
, -99) = NVL(VAT.ORG_ID(+)
, -99) AND GLCT.CONVERSION_TYPE(+) = ARD.CURRENCY_CONVERSION_TYPE
AND CR.DOC_SEQUENCE_ID = SEQ.DOC_SEQUENCE_ID (+) AND CR.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID AND CR.REMITTANCE_BANK_ACCOUNT_ID = APBA.BANK_ACCOUNT_ID(+) AND NVL(CR.ORG_ID
, -99) = NVL(APBA.ORG_ID(+)
, -99) AND CR.RECEIVABLES_TRX_ID = RCVTRX.RECEIVABLES_TRX_ID(+) AND NVL(CR.ORG_ID
, -99) = NVL(RCVTRX.ORG_ID(+)
, -99) AND CR.DISTRIBUTION_SET_ID = DSET.DISTRIBUTION_SET_ID(+) AND NVL(CR.ORG_ID
, -99) = NVL(DSET.ORG_ID(+)
, -99) AND ARD.LINE_ID = TO_NUMBER(R.REFERENCE_3) AND R.JE_HEADER_ID = JEH.JE_HEADER_ID
AND JEH.JE_CATEGORY IN ('MISC RECEIPTS'
, 'RATE ADJUSTMENTS')

Columns