eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AR_AEL_SL_INV_V

Source

View Text - Preformatted


SELECT 222 APPLICATION_ID , CT.set_of_books_id SET_OF_BOOKS_ID , CT.org_id ORG_ID , CTT.type TRX_CLASS , L1.meaning TRX_CLASS_NAME , CT.cust_trx_type_id TRX_TYPE_N , CTT.name TRX_TYPE_NAME , CT.trx_number TRX_NUMBER_DISPLAYED , CT.trx_number TRX_NUMBER_C , CT.trx_date TRX_DATE , CTLGD.comments COMMENTS , CT.doc_sequence_id DOC_SEQUENCE_ID , FD.name DOC_SEQUENCE_NAME , CT.doc_sequence_value DOC_SEQUENCE_VALUE, 'CT' TRX_HDR_TABLE, CT.customer_trx_id TRX_HDR_ID, CTLGD.account_class ACCT_LINE_TYPE, L3.meaning ACCT_LINE_TYPE_NAME, ctlgd.code_combination_id CODE_COMBINATION_ID, ct.invoice_currency_code CURRENCY_CODE, to_number(decode(ctlgd.account_class, 'REC', decode(sign(nvl(ctlgd.amount,0)), -1,null,nvl(ctlgd.amount,0)), decode(sign(nvl(ctlgd.amount,0)), -1,-nvl(ctlgd.amount,0),null))) ENTERED_DR, to_number(decode(ctlgd.account_class, 'REC', decode(sign(nvl(ctlgd.amount,0)), -1,-nvl(ctlgd.amount,0),null), decode(sign(nvl(ctlgd.amount,0)), -1,null,nvl(ctlgd.amount,0)))) ENTERED_CR, to_number(decode(ctlgd.account_class, 'REC', decode(sign(nvl(ctlgd.amount,0)), -1,null,nvl(ctlgd.acctd_amount,0)), decode(sign(nvl(ctlgd.amount,0)), -1,-nvl(ctlgd.acctd_amount,0),null))) ACCOUNTED_DR, to_number(decode(ctlgd.account_class, 'REC', decode(sign(nvl(ctlgd.amount,0)), -1,-nvl(ctlgd.acctd_amount,0),null), decode(sign(nvl(ctlgd.amount,0)), -1,null,nvl(ctlgd.acctd_amount,0)))) ACCOUNTED_CR, ct.exchange_date CURRENCY_CONVERSION_DATE, ct.exchange_rate_type CURRENCY_CONVERSION_TYPE, glct.user_conversion_type CURRENCY_USER_CONVERSION_TYPE, ct.exchange_rate CURRENCY_CONVERSION_RATE, 'C' THIRD_PARTY_TYPE, ct.bill_to_customer_id THIRD_PARTY_ID, cust_acct.account_number THIRD_PARTY_NUMBER, substrb(party.party_name,1,50) THIRD_PARTY_NAME, ct.bill_to_site_use_id THIRD_PARTY_SUB_ID, SU.location THIRD_PARTY_SUB_NAME, ctlgd.gl_date ACCOUNTING_DATE, L4.meaning GL_TRANSFER_STATUS_NAME, decode(ctlgd.posting_control_id, -3, 'N', 'Y') GL_TRANSFER_STATUS, decode(ctlgd.account_class, 'REC','CT','CTLGD') SOURCE_TABLE, decode(ctlgd.account_class, 'REC',ct.customer_trx_id, ctlgd.cust_trx_line_gl_dist_id) SOURCE_ID, RR.name ACCOUNTING_RULE_NAME, ctlgd.cust_trx_line_gl_dist_id AEL_ID, decode(CTL.line_number,null, L1.meaning || ' ' || CT.trx_number, decode(CTL2.line_number,null, decode(CTL.line_number,null,null, L5.meaning || ' ' || to_number(CTL.line_number) ), L5.meaning || ' ' || to_number(CTL2.line_number) || ', ' || L6.meaning || ' ' || CTL.line_number ) ) AE_LINE_REFERENCE, decode(CTL.line_number,null, rpad(L1.meaning,80) || ' ' || rpad(CT.trx_number,20), decode(CTL2.line_number,null, decode(CTL.line_number,null,null, L5.meaning || ' ' || lpad(to_number(CTL.line_number),15,'0') ), L5.meaning || ' ' || lpad(to_number(CTL2.line_number),15,'0') || ', ' || L6.meaning || ' ' || lpad(to_number(CTL.line_number),15,'0')) ) AE_LINE_REFERENCE_INTERNAL, 'CTLGD' AEL_TABLE, CTLGD.last_update_date LAST_UPDATE_DATE, CTLGD.last_updated_by LAST_UPDATED_BY, CTLGD.creation_date CREATION_DATE, CTLGD.created_by CREATED_BY, CTLGD.last_update_login LAST_UPDATE_LOGIN, CTLGD.request_id REQUEST_ID, CTLGD.program_application_id PROGRAM_APPLICATION_ID, CTLGD.program_id PROGRAM_ID, CTLGD.program_update_date PROGRAM_UPDATE_DATE, BS.name TRX_SOURCE_NAME, CT.batch_source_id TRX_SOURCE_ID, CTL.tax_exempt_number TAX_EXEMPT_NUMBER, CTL.inventory_item_id INVENTORY_ITEM_ID, decode(CTL2.line_number, null,to_number(null),CTL.line_number) TRX_DETAIL_LINE_NUMBER, decode(CTL2.line_number, null,CTL.line_number, CTL2.line_number) TRX_LINE_NUMBER, CTL.line_type TRX_LINE_TYPE, L2.meaning TRX_LINE_TYPE_NAME, CTL.quantity_invoiced TRX_QUANTITY, CTL.sales_order SALES_ORDER_NUMBER, S.name SALESREP_NAME, AVT.tax_code TAX_CODE, CTL.vat_tax_id TAX_CODE_ID, CTL.tax_rate TAX_RATE, CTL.unit_selling_price UNIT_SELLING_PRICE, MUOM.unit_of_measure TRX_UOM, to_date(null) APPLICATION_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 FROM ra_customer_trx_all CT, ra_customer_trx_lines_all CTL , ra_cust_trx_line_gl_dist_all CTLGD , ra_customer_trx_lines_all CTL2 , ar_vat_tax_all AVT, mtl_units_of_measure MUOM, ra_rules RR, ra_salesreps_all S, ra_cust_trx_types_all CTT , gl_daily_conversion_types GLCT, hz_cust_site_uses_all SU, hz_cust_accounts CUST_ACCT, hz_parties PARTY, ra_batch_sources_all BS, fnd_document_sequences FD, ar_lookups L6, ar_lookups L2, ar_lookups L5, ar_lookups L4 , ar_lookups L1 , ar_lookups L3 WHERE L6.lookup_code = 'DETAIL_LINE' AND L6.lookup_type = 'VIEW_ACCOUNTING' AND L5.lookup_code = 'LINE' AND L5.lookup_type = 'VIEW_ACCOUNTING' AND L3.lookup_type= decode(ctlgd.collected_tax_ccid, null, 'AUTOGL_TYPE', 'DISTRIBUTION_SOURCE_TYPE') AND L3.lookup_code = decode(ctlgd.collected_tax_ccid, null, nvl(CTLGD.account_class,'REV'), 'DEFERRED_TAX') AND S.salesrep_id(+) = CTLGD.cust_trx_line_salesrep_id AND nvl(CT.org_id,-99) = decode(S.salesrep_id, null, nvl(CT.org_id, -99), nvl(S.org_id, -99)) AND AVT.vat_tax_id(+) = CTL.vat_tax_id AND nvl(AVT.org_id(+),-99) = nvl(CTL.org_id,-99) AND MUOM.uom_code(+) = CTL.uom_code AND RR.rule_id(+) = CTL.accounting_rule_id AND L4.lookup_code = decode(ctlgd.posting_control_id,-3, 'N', 'Y') AND L4.lookup_type = 'YES/NO' AND L2.lookup_code(+) = CTL.line_type AND L2.lookup_type(+) = 'STD_LINE_TYPE' AND CT.doc_sequence_id = FD.doc_sequence_id (+) AND L1.lookup_code = CTT.type AND L1.lookup_type = 'INV/CM' AND CT.cust_trx_type_id = CTT.cust_trx_type_id AND nvl(CT.org_id,-99) = nvl(CTT.org_id,-99) AND CT.exchange_rate_type = GLCT.conversion_type(+) AND CT.bill_to_site_use_id = SU.site_use_id AND CT.bill_to_customer_id = CUST_ACCT.cust_account_id AND CUST_ACCT.party_id = party.party_id AND CT.batch_source_id = BS.batch_source_id AND nvl(CT.org_id,-99) = nvl(BS.org_id,-99) AND CTL.link_to_cust_trx_line_id = CTL2.customer_trx_line_id(+) AND nvl(CTL.org_id,-99) = nvl(CTL2.org_id(+),-99) AND CTLGD.customer_trx_line_id = CTL.customer_trx_line_id(+) AND nvl(CTLGD.org_id,-99) = nvl(CTL.org_id(+),-99) AND CTLGD.account_set_flag = 'N' AND CT.customer_trx_id = CTLGD.customer_trx_id AND nvl(CT.org_id,-99) = nvl(CTLGD.org_id,-99) UNION ALL select 222 APPLICATION_ID , CTCM.set_of_books_id SET_OF_BOOKS_ID , CTCM.org_id ORG_ID , CTTCM.type TRX_CLASS , L1.meaning TRX_CLASS_NAME , CTTCM.cust_trx_type_id TRX_TYPE_N , CTTCM.name TRX_TYPE_NAME , CTCM.trx_number TRX_NUMBER_DISPLAYED , CTCM.trx_number TRX_NUMBER_C , CTCM.trx_date TRX_DATE , null COMMENTS , CTCM.doc_sequence_id DOC_SEQUENCE_ID , SEQ.name DOC_SEQUENCE_NAME , CTCM.doc_sequence_value DOC_SEQUENCE_VALUE, 'CT' TRX_HDR_TABLE, CTCM.customer_trx_id TRX_HDR_ID, ARD.source_type ACCT_LINE_TYPE, l6.meaning ACCT_LINE_TYPE_NAME, ARD.code_combination_id CODE_COMBINATION_ID, decode(ARD.currency_code, NULL,ARPT_SQL_FUNC_UTIL.get_currency_code(ra.application_type, ra.status, ARD.source_type, null, CTINV.invoice_currency_code), 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, CUST_ACCT.account_number THIRD_PARTY_NUMBER, substrb(party.party_name,1,50) 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(RA.posting_control_id,-3,'N','Y') GL_TRANSFER_STATUS, ARD.source_table SOURCE_TABLE, ARD.source_id SOURCE_ID, null ACCOUNTING_RULE_NAME, ARD.line_id AEL_ID, L1.meaning||' '||CTCM.trx_number || ', ' || L5.meaning || ' ' || L4.meaning || ' ' || CTINV.trx_number AE_LINE_REFERENCE, L1.meaning||' '||rpad(CTCM.trx_number,20) || ', ' || L5.meaning || ' ' || rpad(L4.meaning,80) || ' ' || rpad(CTINV.trx_number,20) AE_LINE_REFERENCE_INTERNAL, 'ARD' AEL_TABLE, 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, to_number(null) REQUEST_ID, to_number(null) PROGRAM_APPLICATION_ID, to_number(null) PROGRAM_ID, to_date(null) PROGRAM_UPDATE_DATE, null TRX_SOURCE_NAME, to_number(null) TRX_SOURCE_ID, null TAX_EXEMPT_NUMBER, to_number(null) INVENTORY_ITEM_ID, to_number(null) TRX_DETAIL_LINE_NUMBER, to_number(null) TRX_LINE_NUMBER, null TRX_LINE_TYPE, null TRX_LINE_TYPE_NAME, to_number(null) TRX_QUANTITY, null SALES_ORDER_NUMBER, null SALESREP_NAME, null TAX_CODE, to_number(null) TAX_CODE_ID, to_number(null) TAX_RATE, to_number(null) UNIT_SELLING_PRICE, null TRX_UOM, ra.apply_date APPLICATION_DATE, 'CT' APPLIED_TO_TRX_HDR_TABLE, CTINV.customer_trx_id APPLIED_TO_TRX_HDR_ID, CTINV.trx_number APPLIED_TO_TRX_HDR_NUMBER_C, CTINV.trx_number APPLIED_TO_TRX_HDR_NUMBER_DISP, CTINV.invoice_currency_code APPLIED_TO_TRX_HDR_CURRENCY, CTINV.trx_date APPLIED_TO_TRX_HDR_DATE, L3.meaning APPLIED_TO_TRX_LINE_TYPE_NAME, CTLINV.line_type APPLIED_TO_TRX_LINE_TYPE, CTLINV.line_number APPLIED_TO_TRX_LINE_NUMBER FROM ra_customer_trx_all CTCM, ra_cust_trx_types_all CTTCM, ra_cust_trx_types_all CTTCM2, ra_customer_trx_all CTINV, ra_customer_trx_lines_all CTLINV, hz_cust_site_uses_all SU, hz_cust_accounts CUST_ACCT, hz_parties PARTY, ar_distributions_all ARD, ar_receivable_applications_all RA, fnd_document_sequences SEQ, gl_daily_conversion_types GLCT, ar_lookups L1, ar_lookups L2 , ar_lookups L3 , ar_lookups L4 , ar_lookups L5 , ar_lookups L6 WHERE L1.lookup_type = 'INV/CM' AND L1.lookup_code = CTTCM.type AND L2.lookup_type = 'YES/NO' AND L2.lookup_code = decode(RA.posting_control_id,-3,'N','Y') AND L3.lookup_type(+) = 'STD_LINE_TYPE' AND L3.lookup_code(+) = CTLINV.line_type AND L4.lookup_type = 'INV/CM' AND L4.lookup_code = CTTCM2.type AND L5.lookup_code = 'APPLIED_TO' AND L5.lookup_type = 'VIEW_ACCOUNTING' AND L6.lookup_code = ARD.source_type AND L6.lookup_type = 'DISTRIBUTION_SOURCE_TYPE' AND RA.application_type = 'CM' AND nvl(RA.postable,'Y') = 'Y' AND nvl(RA.confirmed_flag,'Y') = 'Y' AND RA.customer_trx_id = CTCM.customer_trx_id AND nvl(RA.org_id,-99) = nvl(CTCM.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 SEQ.doc_sequence_id(+) = CTCM.doc_sequence_id AND CTINV.customer_trx_id = RA.applied_customer_trx_id AND nvl(CTINV.org_id,-99) = nvl(RA.org_id,-99) AND CTLINV.customer_trx_line_id(+) = RA.applied_customer_trx_line_id AND nvl(CTLINV.org_id(+),-99) = nvl(RA.org_id,-99) AND ard.third_party_sub_id = SU.site_use_id (+) AND decode(ard.third_party_sub_id,NULL, -99, nvl(ard.org_id,-99)) = nvl(SU.org_id,-99) AND ard.third_party_id = CUST_ACCT.cust_account_id (+) AND CUST_ACCT.party_id = PARTY.party_id (+) AND CTTCM2.cust_trx_type_id = CTINV.cust_trx_type_id AND nvl(CTTCM2.org_id,-99) = nvl(CTINV.org_id,-99) AND CTTCM.cust_trx_type_id = CTCM.cust_trx_type_id AND nvl(CTTCM.org_id,-99) = nvl(CTCM.org_id,-99) UNION ALL select 222 APPLICATION_ID , CTCM.set_of_books_id SET_OF_BOOKS_ID , CTCM.org_id ORG_ID , CTTCM.type TRX_CLASS , L1.meaning TRX_CLASS_NAME , CTTCM.cust_trx_type_id TRX_TYPE_N , CTTCM.name TRX_TYPE_NAME , CTCM.trx_number TRX_NUMBER_DISPLAYED , CTCM.trx_number TRX_NUMBER_C , CTCM.trx_date TRX_DATE , null COMMENTS , CTCM.doc_sequence_id DOC_SEQUENCE_ID , SEQ.name DOC_SEQUENCE_NAME , CTCM.doc_sequence_value DOC_SEQUENCE_VALUE, 'CT' TRX_HDR_TABLE, CTCM.customer_trx_id TRX_HDR_ID, ARD.source_type ACCT_LINE_TYPE, l6.meaning ACCT_LINE_TYPE_NAME, ARD.code_combination_id CODE_COMBINATION_ID, decode(ARD.currency_code, NULL,ARPT_SQL_FUNC_UTIL.get_currency_code(ra.application_type, ra.status, ARD.source_type, null, CTCM.invoice_currency_code), 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, CUST_ACCT.account_number THIRD_PARTY_NUMBER, substrb(party.party_name,1,50) 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(RA.posting_control_id,-3,'N','Y') GL_TRANSFER_STATUS, ARD.source_table SOURCE_TABLE, ARD.source_id SOURCE_ID, null ACCOUNTING_RULE_NAME, ARD.line_id AEL_ID, L1.meaning||' '||CTCM.trx_number || ', ' || L5.meaning || ' ' || PS.trx_number || ' ' || PS.trx_number AE_LINE_REFERENCE, L1.meaning||' '||rpad(CTCM.trx_number,20) || ', ' || L5.meaning || ' ' || rpad(PS.trx_number,80) || ' ' || rpad(PS.trx_number,20) AE_LINE_REFERENCE_INTERNAL, 'ARD' AEL_TABLE, 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, to_number(null) REQUEST_ID, to_number(null) PROGRAM_APPLICATION_ID, to_number(null) PROGRAM_ID, to_date(null) PROGRAM_UPDATE_DATE, null TRX_SOURCE_NAME, to_number(null) TRX_SOURCE_ID, null TAX_EXEMPT_NUMBER, to_number(null) INVENTORY_ITEM_ID, to_number(null) TRX_DETAIL_LINE_NUMBER, to_number(null) TRX_LINE_NUMBER, null TRX_LINE_TYPE, null TRX_LINE_TYPE_NAME, to_number(null) TRX_QUANTITY, null SALES_ORDER_NUMBER, null SALESREP_NAME, null TAX_CODE, to_number(null) TAX_CODE_ID, to_number(null) TAX_RATE, to_number(null) UNIT_SELLING_PRICE, null TRX_UOM, ra.apply_date APPLICATION_DATE, 'CT' APPLIED_TO_TRX_HDR_TABLE, PS.customer_trx_id APPLIED_TO_TRX_HDR_ID, PS.trx_number APPLIED_TO_TRX_HDR_NUMBER_C, PS.trx_number APPLIED_TO_TRX_HDR_NUMBER_DISP, CTCM.invoice_currency_code APPLIED_TO_TRX_HDR_CURRENCY, CTCM.trx_date APPLIED_TO_TRX_HDR_DATE, NULL APPLIED_TO_TRX_LINE_TYPE_NAME, NULL APPLIED_TO_TRX_LINE_TYPE, NULL APPLIED_TO_TRX_LINE_NUMBER FROM ra_customer_trx_all CTCM, ar_payment_schedules PS, ra_cust_trx_types_all CTTCM, hz_cust_site_uses_all SU, hz_cust_accounts CUST_ACCT, hz_parties PARTY, ar_distributions_all ARD, ar_receivable_applications_all RA, fnd_document_sequences SEQ, gl_daily_conversion_types GLCT, ar_lookups L1, ar_lookups L2 , ar_lookups L5 , ar_lookups L6 WHERE L1.lookup_type = 'INV/CM' AND L1.lookup_code = CTTCM.type AND L2.lookup_type = 'YES/NO' AND L2.lookup_code = decode(RA.posting_control_id,-3,'N','Y') AND L5.lookup_code = 'APPLIED_TO' AND L5.lookup_type = 'VIEW_ACCOUNTING' AND L6.lookup_code = ARD.source_type AND L6.lookup_type = 'DISTRIBUTION_SOURCE_TYPE' AND RA.application_type = 'CM' AND nvl(RA.postable,'Y') = 'Y' AND nvl(RA.confirmed_flag,'Y') = 'Y' AND RA.customer_trx_id = CTCM.customer_trx_id AND nvl(RA.org_id,-99) = nvl(CTCM.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 SEQ.doc_sequence_id(+) = CTCM.doc_sequence_id AND PS.payment_schedule_id = RA.applied_payment_schedule_id AND ard.third_party_sub_id = SU.site_use_id (+) AND decode(ard.third_party_sub_id,NULL, -99, nvl(ard.org_id,-99)) = nvl(SU.org_id,-99) AND ard.third_party_id = CUST_ACCT.cust_account_id (+) AND CUST_ACCT.party_id = PARTY.party_id (+) AND CTTCM.cust_trx_type_id = CTCM.cust_trx_type_id AND nvl(CTTCM.org_id,-99) = nvl(CTCM.org_id,-99) AND SIGN(RA.applied_payment_schedule_id) <> 1

View Text - HTML Formatted

SELECT 222 APPLICATION_ID
, CT.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, CT.ORG_ID ORG_ID
, CTT.TYPE TRX_CLASS
, L1.MEANING TRX_CLASS_NAME
, CT.CUST_TRX_TYPE_ID TRX_TYPE_N
, CTT.NAME TRX_TYPE_NAME
, CT.TRX_NUMBER TRX_NUMBER_DISPLAYED
, CT.TRX_NUMBER TRX_NUMBER_C
, CT.TRX_DATE TRX_DATE
, CTLGD.COMMENTS COMMENTS
, CT.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, FD.NAME DOC_SEQUENCE_NAME
, CT.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, 'CT' TRX_HDR_TABLE
, CT.CUSTOMER_TRX_ID TRX_HDR_ID
, CTLGD.ACCOUNT_CLASS ACCT_LINE_TYPE
, L3.MEANING ACCT_LINE_TYPE_NAME
, CTLGD.CODE_COMBINATION_ID CODE_COMBINATION_ID
, CT.INVOICE_CURRENCY_CODE CURRENCY_CODE
, TO_NUMBER(DECODE(CTLGD.ACCOUNT_CLASS
, 'REC'
, DECODE(SIGN(NVL(CTLGD.AMOUNT
, 0))
, -1
, NULL
, NVL(CTLGD.AMOUNT
, 0))
, DECODE(SIGN(NVL(CTLGD.AMOUNT
, 0))
, -1
, -NVL(CTLGD.AMOUNT
, 0)
, NULL))) ENTERED_DR
, TO_NUMBER(DECODE(CTLGD.ACCOUNT_CLASS
, 'REC'
, DECODE(SIGN(NVL(CTLGD.AMOUNT
, 0))
, -1
, -NVL(CTLGD.AMOUNT
, 0)
, NULL)
, DECODE(SIGN(NVL(CTLGD.AMOUNT
, 0))
, -1
, NULL
, NVL(CTLGD.AMOUNT
, 0)))) ENTERED_CR
, TO_NUMBER(DECODE(CTLGD.ACCOUNT_CLASS
, 'REC'
, DECODE(SIGN(NVL(CTLGD.AMOUNT
, 0))
, -1
, NULL
, NVL(CTLGD.ACCTD_AMOUNT
, 0))
, DECODE(SIGN(NVL(CTLGD.AMOUNT
, 0))
, -1
, -NVL(CTLGD.ACCTD_AMOUNT
, 0)
, NULL))) ACCOUNTED_DR
, TO_NUMBER(DECODE(CTLGD.ACCOUNT_CLASS
, 'REC'
, DECODE(SIGN(NVL(CTLGD.AMOUNT
, 0))
, -1
, -NVL(CTLGD.ACCTD_AMOUNT
, 0)
, NULL)
, DECODE(SIGN(NVL(CTLGD.AMOUNT
, 0))
, -1
, NULL
, NVL(CTLGD.ACCTD_AMOUNT
, 0)))) ACCOUNTED_CR
, CT.EXCHANGE_DATE CURRENCY_CONVERSION_DATE
, CT.EXCHANGE_RATE_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, CT.EXCHANGE_RATE CURRENCY_CONVERSION_RATE
, 'C' THIRD_PARTY_TYPE
, CT.BILL_TO_CUSTOMER_ID THIRD_PARTY_ID
, CUST_ACCT.ACCOUNT_NUMBER THIRD_PARTY_NUMBER
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) THIRD_PARTY_NAME
, CT.BILL_TO_SITE_USE_ID THIRD_PARTY_SUB_ID
, SU.LOCATION THIRD_PARTY_SUB_NAME
, CTLGD.GL_DATE ACCOUNTING_DATE
, L4.MEANING GL_TRANSFER_STATUS_NAME
, DECODE(CTLGD.POSTING_CONTROL_ID
, -3
, 'N'
, 'Y') GL_TRANSFER_STATUS
, DECODE(CTLGD.ACCOUNT_CLASS
, 'REC'
, 'CT'
, 'CTLGD') SOURCE_TABLE
, DECODE(CTLGD.ACCOUNT_CLASS
, 'REC'
, CT.CUSTOMER_TRX_ID
, CTLGD.CUST_TRX_LINE_GL_DIST_ID) SOURCE_ID
, RR.NAME ACCOUNTING_RULE_NAME
, CTLGD.CUST_TRX_LINE_GL_DIST_ID AEL_ID
, DECODE(CTL.LINE_NUMBER
, NULL
, L1.MEANING || ' ' || CT.TRX_NUMBER
, DECODE(CTL2.LINE_NUMBER
, NULL
, DECODE(CTL.LINE_NUMBER
, NULL
, NULL
, L5.MEANING || ' ' || TO_NUMBER(CTL.LINE_NUMBER) )
, L5.MEANING || ' ' || TO_NUMBER(CTL2.LINE_NUMBER) || '
, ' || L6.MEANING || ' ' || CTL.LINE_NUMBER ) ) AE_LINE_REFERENCE
, DECODE(CTL.LINE_NUMBER
, NULL
, RPAD(L1.MEANING
, 80) || ' ' || RPAD(CT.TRX_NUMBER
, 20)
, DECODE(CTL2.LINE_NUMBER
, NULL
, DECODE(CTL.LINE_NUMBER
, NULL
, NULL
, L5.MEANING || ' ' || LPAD(TO_NUMBER(CTL.LINE_NUMBER)
, 15
, '0') )
, L5.MEANING || ' ' || LPAD(TO_NUMBER(CTL2.LINE_NUMBER)
, 15
, '0') || '
, ' || L6.MEANING || ' ' || LPAD(TO_NUMBER(CTL.LINE_NUMBER)
, 15
, '0')) ) AE_LINE_REFERENCE_INTERNAL
, 'CTLGD' AEL_TABLE
, CTLGD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CTLGD.LAST_UPDATED_BY LAST_UPDATED_BY
, CTLGD.CREATION_DATE CREATION_DATE
, CTLGD.CREATED_BY CREATED_BY
, CTLGD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CTLGD.REQUEST_ID REQUEST_ID
, CTLGD.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, CTLGD.PROGRAM_ID PROGRAM_ID
, CTLGD.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, BS.NAME TRX_SOURCE_NAME
, CT.BATCH_SOURCE_ID TRX_SOURCE_ID
, CTL.TAX_EXEMPT_NUMBER TAX_EXEMPT_NUMBER
, CTL.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, DECODE(CTL2.LINE_NUMBER
, NULL
, TO_NUMBER(NULL)
, CTL.LINE_NUMBER) TRX_DETAIL_LINE_NUMBER
, DECODE(CTL2.LINE_NUMBER
, NULL
, CTL.LINE_NUMBER
, CTL2.LINE_NUMBER) TRX_LINE_NUMBER
, CTL.LINE_TYPE TRX_LINE_TYPE
, L2.MEANING TRX_LINE_TYPE_NAME
, CTL.QUANTITY_INVOICED TRX_QUANTITY
, CTL.SALES_ORDER SALES_ORDER_NUMBER
, S.NAME SALESREP_NAME
, AVT.TAX_CODE TAX_CODE
, CTL.VAT_TAX_ID TAX_CODE_ID
, CTL.TAX_RATE TAX_RATE
, CTL.UNIT_SELLING_PRICE UNIT_SELLING_PRICE
, MUOM.UNIT_OF_MEASURE TRX_UOM
, TO_DATE(NULL) APPLICATION_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
FROM RA_CUSTOMER_TRX_ALL CT
, RA_CUSTOMER_TRX_LINES_ALL CTL
, RA_CUST_TRX_LINE_GL_DIST_ALL CTLGD
, RA_CUSTOMER_TRX_LINES_ALL CTL2
, AR_VAT_TAX_ALL AVT
, MTL_UNITS_OF_MEASURE MUOM
, RA_RULES RR
, RA_SALESREPS_ALL S
, RA_CUST_TRX_TYPES_ALL CTT
, GL_DAILY_CONVERSION_TYPES GLCT
, HZ_CUST_SITE_USES_ALL SU
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, RA_BATCH_SOURCES_ALL BS
, FND_DOCUMENT_SEQUENCES FD
, AR_LOOKUPS L6
, AR_LOOKUPS L2
, AR_LOOKUPS L5
, AR_LOOKUPS L4
, AR_LOOKUPS L1
, AR_LOOKUPS L3
WHERE L6.LOOKUP_CODE = 'DETAIL_LINE'
AND L6.LOOKUP_TYPE = 'VIEW_ACCOUNTING'
AND L5.LOOKUP_CODE = 'LINE'
AND L5.LOOKUP_TYPE = 'VIEW_ACCOUNTING'
AND L3.LOOKUP_TYPE= DECODE(CTLGD.COLLECTED_TAX_CCID
, NULL
, 'AUTOGL_TYPE'
, 'DISTRIBUTION_SOURCE_TYPE')
AND L3.LOOKUP_CODE = DECODE(CTLGD.COLLECTED_TAX_CCID
, NULL
, NVL(CTLGD.ACCOUNT_CLASS
, 'REV')
, 'DEFERRED_TAX')
AND S.SALESREP_ID(+) = CTLGD.CUST_TRX_LINE_SALESREP_ID
AND NVL(CT.ORG_ID
, -99) = DECODE(S.SALESREP_ID
, NULL
, NVL(CT.ORG_ID
, -99)
, NVL(S.ORG_ID
, -99))
AND AVT.VAT_TAX_ID(+) = CTL.VAT_TAX_ID
AND NVL(AVT.ORG_ID(+)
, -99) = NVL(CTL.ORG_ID
, -99)
AND MUOM.UOM_CODE(+) = CTL.UOM_CODE
AND RR.RULE_ID(+) = CTL.ACCOUNTING_RULE_ID
AND L4.LOOKUP_CODE = DECODE(CTLGD.POSTING_CONTROL_ID
, -3
, 'N'
, 'Y')
AND L4.LOOKUP_TYPE = 'YES/NO'
AND L2.LOOKUP_CODE(+) = CTL.LINE_TYPE
AND L2.LOOKUP_TYPE(+) = 'STD_LINE_TYPE'
AND CT.DOC_SEQUENCE_ID = FD.DOC_SEQUENCE_ID (+)
AND L1.LOOKUP_CODE = CTT.TYPE
AND L1.LOOKUP_TYPE = 'INV/CM'
AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND NVL(CT.ORG_ID
, -99) = NVL(CTT.ORG_ID
, -99)
AND CT.EXCHANGE_RATE_TYPE = GLCT.CONVERSION_TYPE(+)
AND CT.BILL_TO_SITE_USE_ID = SU.SITE_USE_ID
AND CT.BILL_TO_CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
AND NVL(CT.ORG_ID
, -99) = NVL(BS.ORG_ID
, -99)
AND CTL.LINK_TO_CUST_TRX_LINE_ID = CTL2.CUSTOMER_TRX_LINE_ID(+)
AND NVL(CTL.ORG_ID
, -99) = NVL(CTL2.ORG_ID(+)
, -99)
AND CTLGD.CUSTOMER_TRX_LINE_ID = CTL.CUSTOMER_TRX_LINE_ID(+)
AND NVL(CTLGD.ORG_ID
, -99) = NVL(CTL.ORG_ID(+)
, -99)
AND CTLGD.ACCOUNT_SET_FLAG = 'N'
AND CT.CUSTOMER_TRX_ID = CTLGD.CUSTOMER_TRX_ID
AND NVL(CT.ORG_ID
, -99) = NVL(CTLGD.ORG_ID
, -99) UNION ALL SELECT 222 APPLICATION_ID
, CTCM.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, CTCM.ORG_ID ORG_ID
, CTTCM.TYPE TRX_CLASS
, L1.MEANING TRX_CLASS_NAME
, CTTCM.CUST_TRX_TYPE_ID TRX_TYPE_N
, CTTCM.NAME TRX_TYPE_NAME
, CTCM.TRX_NUMBER TRX_NUMBER_DISPLAYED
, CTCM.TRX_NUMBER TRX_NUMBER_C
, CTCM.TRX_DATE TRX_DATE
, NULL COMMENTS
, CTCM.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, SEQ.NAME DOC_SEQUENCE_NAME
, CTCM.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, 'CT' TRX_HDR_TABLE
, CTCM.CUSTOMER_TRX_ID TRX_HDR_ID
, ARD.SOURCE_TYPE ACCT_LINE_TYPE
, L6.MEANING ACCT_LINE_TYPE_NAME
, ARD.CODE_COMBINATION_ID CODE_COMBINATION_ID
, DECODE(ARD.CURRENCY_CODE
, NULL
, ARPT_SQL_FUNC_UTIL.GET_CURRENCY_CODE(RA.APPLICATION_TYPE
, RA.STATUS
, ARD.SOURCE_TYPE
, NULL
, CTINV.INVOICE_CURRENCY_CODE)
, 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
, CUST_ACCT.ACCOUNT_NUMBER THIRD_PARTY_NUMBER
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) 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(RA.POSTING_CONTROL_ID
, -3
, 'N'
, 'Y') GL_TRANSFER_STATUS
, ARD.SOURCE_TABLE SOURCE_TABLE
, ARD.SOURCE_ID SOURCE_ID
, NULL ACCOUNTING_RULE_NAME
, ARD.LINE_ID AEL_ID
, L1.MEANING||' '||CTCM.TRX_NUMBER || '
, ' || L5.MEANING || ' ' || L4.MEANING || ' ' || CTINV.TRX_NUMBER AE_LINE_REFERENCE
, L1.MEANING||' '||RPAD(CTCM.TRX_NUMBER
, 20) || '
, ' || L5.MEANING || ' ' || RPAD(L4.MEANING
, 80) || ' ' || RPAD(CTINV.TRX_NUMBER
, 20) AE_LINE_REFERENCE_INTERNAL
, 'ARD' AEL_TABLE
, 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
, TO_NUMBER(NULL) REQUEST_ID
, TO_NUMBER(NULL) PROGRAM_APPLICATION_ID
, TO_NUMBER(NULL) PROGRAM_ID
, TO_DATE(NULL) PROGRAM_UPDATE_DATE
, NULL TRX_SOURCE_NAME
, TO_NUMBER(NULL) TRX_SOURCE_ID
, NULL TAX_EXEMPT_NUMBER
, TO_NUMBER(NULL) INVENTORY_ITEM_ID
, TO_NUMBER(NULL) TRX_DETAIL_LINE_NUMBER
, TO_NUMBER(NULL) TRX_LINE_NUMBER
, NULL TRX_LINE_TYPE
, NULL TRX_LINE_TYPE_NAME
, TO_NUMBER(NULL) TRX_QUANTITY
, NULL SALES_ORDER_NUMBER
, NULL SALESREP_NAME
, NULL TAX_CODE
, TO_NUMBER(NULL) TAX_CODE_ID
, TO_NUMBER(NULL) TAX_RATE
, TO_NUMBER(NULL) UNIT_SELLING_PRICE
, NULL TRX_UOM
, RA.APPLY_DATE APPLICATION_DATE
, 'CT' APPLIED_TO_TRX_HDR_TABLE
, CTINV.CUSTOMER_TRX_ID APPLIED_TO_TRX_HDR_ID
, CTINV.TRX_NUMBER APPLIED_TO_TRX_HDR_NUMBER_C
, CTINV.TRX_NUMBER APPLIED_TO_TRX_HDR_NUMBER_DISP
, CTINV.INVOICE_CURRENCY_CODE APPLIED_TO_TRX_HDR_CURRENCY
, CTINV.TRX_DATE APPLIED_TO_TRX_HDR_DATE
, L3.MEANING APPLIED_TO_TRX_LINE_TYPE_NAME
, CTLINV.LINE_TYPE APPLIED_TO_TRX_LINE_TYPE
, CTLINV.LINE_NUMBER APPLIED_TO_TRX_LINE_NUMBER
FROM RA_CUSTOMER_TRX_ALL CTCM
, RA_CUST_TRX_TYPES_ALL CTTCM
, RA_CUST_TRX_TYPES_ALL CTTCM2
, RA_CUSTOMER_TRX_ALL CTINV
, RA_CUSTOMER_TRX_LINES_ALL CTLINV
, HZ_CUST_SITE_USES_ALL SU
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, AR_DISTRIBUTIONS_ALL ARD
, AR_RECEIVABLE_APPLICATIONS_ALL RA
, FND_DOCUMENT_SEQUENCES SEQ
, GL_DAILY_CONVERSION_TYPES GLCT
, AR_LOOKUPS L1
, AR_LOOKUPS L2
, AR_LOOKUPS L3
, AR_LOOKUPS L4
, AR_LOOKUPS L5
, AR_LOOKUPS L6
WHERE L1.LOOKUP_TYPE = 'INV/CM'
AND L1.LOOKUP_CODE = CTTCM.TYPE
AND L2.LOOKUP_TYPE = 'YES/NO'
AND L2.LOOKUP_CODE = DECODE(RA.POSTING_CONTROL_ID
, -3
, 'N'
, 'Y')
AND L3.LOOKUP_TYPE(+) = 'STD_LINE_TYPE'
AND L3.LOOKUP_CODE(+) = CTLINV.LINE_TYPE
AND L4.LOOKUP_TYPE = 'INV/CM'
AND L4.LOOKUP_CODE = CTTCM2.TYPE
AND L5.LOOKUP_CODE = 'APPLIED_TO'
AND L5.LOOKUP_TYPE = 'VIEW_ACCOUNTING'
AND L6.LOOKUP_CODE = ARD.SOURCE_TYPE
AND L6.LOOKUP_TYPE = 'DISTRIBUTION_SOURCE_TYPE'
AND RA.APPLICATION_TYPE = 'CM'
AND NVL(RA.POSTABLE
, 'Y') = 'Y'
AND NVL(RA.CONFIRMED_FLAG
, 'Y') = 'Y'
AND RA.CUSTOMER_TRX_ID = CTCM.CUSTOMER_TRX_ID
AND NVL(RA.ORG_ID
, -99) = NVL(CTCM.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 SEQ.DOC_SEQUENCE_ID(+) = CTCM.DOC_SEQUENCE_ID
AND CTINV.CUSTOMER_TRX_ID = RA.APPLIED_CUSTOMER_TRX_ID
AND NVL(CTINV.ORG_ID
, -99) = NVL(RA.ORG_ID
, -99)
AND CTLINV.CUSTOMER_TRX_LINE_ID(+) = RA.APPLIED_CUSTOMER_TRX_LINE_ID
AND NVL(CTLINV.ORG_ID(+)
, -99) = NVL(RA.ORG_ID
, -99)
AND ARD.THIRD_PARTY_SUB_ID = SU.SITE_USE_ID (+)
AND DECODE(ARD.THIRD_PARTY_SUB_ID
, NULL
, -99
, NVL(ARD.ORG_ID
, -99)) = NVL(SU.ORG_ID
, -99)
AND ARD.THIRD_PARTY_ID = CUST_ACCT.CUST_ACCOUNT_ID (+)
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID (+)
AND CTTCM2.CUST_TRX_TYPE_ID = CTINV.CUST_TRX_TYPE_ID
AND NVL(CTTCM2.ORG_ID
, -99) = NVL(CTINV.ORG_ID
, -99)
AND CTTCM.CUST_TRX_TYPE_ID = CTCM.CUST_TRX_TYPE_ID
AND NVL(CTTCM.ORG_ID
, -99) = NVL(CTCM.ORG_ID
, -99) UNION ALL SELECT 222 APPLICATION_ID
, CTCM.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, CTCM.ORG_ID ORG_ID
, CTTCM.TYPE TRX_CLASS
, L1.MEANING TRX_CLASS_NAME
, CTTCM.CUST_TRX_TYPE_ID TRX_TYPE_N
, CTTCM.NAME TRX_TYPE_NAME
, CTCM.TRX_NUMBER TRX_NUMBER_DISPLAYED
, CTCM.TRX_NUMBER TRX_NUMBER_C
, CTCM.TRX_DATE TRX_DATE
, NULL COMMENTS
, CTCM.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, SEQ.NAME DOC_SEQUENCE_NAME
, CTCM.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, 'CT' TRX_HDR_TABLE
, CTCM.CUSTOMER_TRX_ID TRX_HDR_ID
, ARD.SOURCE_TYPE ACCT_LINE_TYPE
, L6.MEANING ACCT_LINE_TYPE_NAME
, ARD.CODE_COMBINATION_ID CODE_COMBINATION_ID
, DECODE(ARD.CURRENCY_CODE
, NULL
, ARPT_SQL_FUNC_UTIL.GET_CURRENCY_CODE(RA.APPLICATION_TYPE
, RA.STATUS
, ARD.SOURCE_TYPE
, NULL
, CTCM.INVOICE_CURRENCY_CODE)
, 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
, CUST_ACCT.ACCOUNT_NUMBER THIRD_PARTY_NUMBER
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) 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(RA.POSTING_CONTROL_ID
, -3
, 'N'
, 'Y') GL_TRANSFER_STATUS
, ARD.SOURCE_TABLE SOURCE_TABLE
, ARD.SOURCE_ID SOURCE_ID
, NULL ACCOUNTING_RULE_NAME
, ARD.LINE_ID AEL_ID
, L1.MEANING||' '||CTCM.TRX_NUMBER || '
, ' || L5.MEANING || ' ' || PS.TRX_NUMBER || ' ' || PS.TRX_NUMBER AE_LINE_REFERENCE
, L1.MEANING||' '||RPAD(CTCM.TRX_NUMBER
, 20) || '
, ' || L5.MEANING || ' ' || RPAD(PS.TRX_NUMBER
, 80) || ' ' || RPAD(PS.TRX_NUMBER
, 20) AE_LINE_REFERENCE_INTERNAL
, 'ARD' AEL_TABLE
, 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
, TO_NUMBER(NULL) REQUEST_ID
, TO_NUMBER(NULL) PROGRAM_APPLICATION_ID
, TO_NUMBER(NULL) PROGRAM_ID
, TO_DATE(NULL) PROGRAM_UPDATE_DATE
, NULL TRX_SOURCE_NAME
, TO_NUMBER(NULL) TRX_SOURCE_ID
, NULL TAX_EXEMPT_NUMBER
, TO_NUMBER(NULL) INVENTORY_ITEM_ID
, TO_NUMBER(NULL) TRX_DETAIL_LINE_NUMBER
, TO_NUMBER(NULL) TRX_LINE_NUMBER
, NULL TRX_LINE_TYPE
, NULL TRX_LINE_TYPE_NAME
, TO_NUMBER(NULL) TRX_QUANTITY
, NULL SALES_ORDER_NUMBER
, NULL SALESREP_NAME
, NULL TAX_CODE
, TO_NUMBER(NULL) TAX_CODE_ID
, TO_NUMBER(NULL) TAX_RATE
, TO_NUMBER(NULL) UNIT_SELLING_PRICE
, NULL TRX_UOM
, RA.APPLY_DATE APPLICATION_DATE
, 'CT' APPLIED_TO_TRX_HDR_TABLE
, PS.CUSTOMER_TRX_ID APPLIED_TO_TRX_HDR_ID
, PS.TRX_NUMBER APPLIED_TO_TRX_HDR_NUMBER_C
, PS.TRX_NUMBER APPLIED_TO_TRX_HDR_NUMBER_DISP
, CTCM.INVOICE_CURRENCY_CODE APPLIED_TO_TRX_HDR_CURRENCY
, CTCM.TRX_DATE APPLIED_TO_TRX_HDR_DATE
, NULL APPLIED_TO_TRX_LINE_TYPE_NAME
, NULL APPLIED_TO_TRX_LINE_TYPE
, NULL APPLIED_TO_TRX_LINE_NUMBER
FROM RA_CUSTOMER_TRX_ALL CTCM
, AR_PAYMENT_SCHEDULES PS
, RA_CUST_TRX_TYPES_ALL CTTCM
, HZ_CUST_SITE_USES_ALL SU
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, AR_DISTRIBUTIONS_ALL ARD
, AR_RECEIVABLE_APPLICATIONS_ALL RA
, FND_DOCUMENT_SEQUENCES SEQ
, GL_DAILY_CONVERSION_TYPES GLCT
, AR_LOOKUPS L1
, AR_LOOKUPS L2
, AR_LOOKUPS L5
, AR_LOOKUPS L6
WHERE L1.LOOKUP_TYPE = 'INV/CM'
AND L1.LOOKUP_CODE = CTTCM.TYPE
AND L2.LOOKUP_TYPE = 'YES/NO'
AND L2.LOOKUP_CODE = DECODE(RA.POSTING_CONTROL_ID
, -3
, 'N'
, 'Y')
AND L5.LOOKUP_CODE = 'APPLIED_TO'
AND L5.LOOKUP_TYPE = 'VIEW_ACCOUNTING'
AND L6.LOOKUP_CODE = ARD.SOURCE_TYPE
AND L6.LOOKUP_TYPE = 'DISTRIBUTION_SOURCE_TYPE'
AND RA.APPLICATION_TYPE = 'CM'
AND NVL(RA.POSTABLE
, 'Y') = 'Y'
AND NVL(RA.CONFIRMED_FLAG
, 'Y') = 'Y'
AND RA.CUSTOMER_TRX_ID = CTCM.CUSTOMER_TRX_ID
AND NVL(RA.ORG_ID
, -99) = NVL(CTCM.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 SEQ.DOC_SEQUENCE_ID(+) = CTCM.DOC_SEQUENCE_ID
AND PS.PAYMENT_SCHEDULE_ID = RA.APPLIED_PAYMENT_SCHEDULE_ID
AND ARD.THIRD_PARTY_SUB_ID = SU.SITE_USE_ID (+)
AND DECODE(ARD.THIRD_PARTY_SUB_ID
, NULL
, -99
, NVL(ARD.ORG_ID
, -99)) = NVL(SU.ORG_ID
, -99)
AND ARD.THIRD_PARTY_ID = CUST_ACCT.CUST_ACCOUNT_ID (+)
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID (+)
AND CTTCM.CUST_TRX_TYPE_ID = CTCM.CUST_TRX_TYPE_ID
AND NVL(CTTCM.ORG_ID
, -99) = NVL(CTCM.ORG_ID
, -99)
AND SIGN(RA.APPLIED_PAYMENT_SCHEDULE_ID) <> 1