DROP VIEW APPS.AR_AEL_GL_INV_V; /* Formatted on 6/13/2013 2:58:01 PM (QP5 v5.139.911.3011) */ CREATE OR REPLACE FORCE VIEW APPS.AR_AEL_GL_INV_V ( JE_HEADER_ID, JE_LINE_NUM, APPLICATION_ID, SET_OF_BOOKS_ID, ORG_ID, TRX_CLASS, TRX_CLASS_NAME, TRX_TYPE_N, TRX_TYPE_NAME, TRX_NUMBER_DISPLAYED, TRX_NUMBER_C, TRX_DATE, COMMENTS, DOC_SEQUENCE_ID, DOC_SEQUENCE_NAME, DOC_SEQUENCE_VALUE, TRX_HDR_TABLE, TRX_HDR_ID, ACCT_LINE_TYPE, ACCT_LINE_TYPE_NAME, CODE_COMBINATION_ID, CURRENCY_CODE, ENTERED_DR, ENTERED_CR, ACCOUNTED_DR, ACCOUNTED_CR, CURRENCY_CONVERSION_DATE, CURRENCY_CONVERSION_TYPE, CURRENCY_USER_CONVERSION_TYPE, CURRENCY_CONVERSION_RATE, THIRD_PARTY_TYPE, THIRD_PARTY_ID, THIRD_PARTY_NUMBER, THIRD_PARTY_NAME, THIRD_PARTY_SUB_ID, THIRD_PARTY_SUB_NAME, ACCOUNTING_DATE, GL_TRANSFER_STATUS_NAME, GL_TRANSFER_STATUS, SOURCE_TABLE, SOURCE_ID, ACCOUNTING_RULE_NAME, AEL_ID, AE_LINE_REFERENCE, AE_LINE_REFERENCE_INTERNAL, AEL_TABLE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, TRX_SOURCE_NAME, TRX_SOURCE_ID, TAX_EXEMPT_NUMBER, INVENTORY_ITEM_ID, TRX_DETAIL_LINE_NUMBER, TRX_LINE_NUMBER, TRX_LINE_TYPE, TRX_LINE_TYPE_NAME, TRX_QUANTITY, SALES_ORDER_NUMBER, SALESREP_NAME, TAX_CODE, TAX_CODE_ID, TAX_RATE, UNIT_SELLING_PRICE, TRX_UOM, APPLICATION_DATE, APPLIED_TO_TRX_HDR_TABLE, APPLIED_TO_TRX_HDR_ID, APPLIED_TO_TRX_HDR_NUMBER_C, APPLIED_TO_TRX_HDR_NUMBER_DISP, APPLIED_TO_TRX_HDR_CURRENCY, APPLIED_TO_TRX_HDR_DATE, APPLIED_TO_TRX_LINE_TYPE_NAME, APPLIED_TO_TRX_LINE_TYPE, APPLIED_TO_TRX_LINE_NUMBER ) AS SELECT /*+ORDERED */ /* $Header: araeginv.sql 115.9 2000/12/19 18:22:40 djancis noship $ */ /* R.je_batch_id JE_BATCH_ID,*/ JEH.je_header_id JE_HEADER_ID, R.je_line_num JE_LINE_NUM, 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, /* The following columns are specific to AR Transaction */ 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, DECODE (CTLSR.salesrep_id, NULL, NULL, 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 gl_je_headers JEH, gl_import_references R, ra_cust_trx_line_gl_dist_all CTLGD, ra_cust_trx_line_salesreps_all CTLSR, ra_customer_trx_all CT, ra_batch_sources_all BS, ra_cust_trx_types_all CTT, hz_cust_site_uses_all SU, hz_cust_accounts CUST_ACCT, hz_parties PARTY, ra_customer_trx_lines_all CTL2, ra_customer_trx_lines_all CTL, ar_vat_tax_all AVT, mtl_units_of_measure MUOM, ra_rules RR, ra_salesreps_all S, fnd_document_sequences FD, gl_daily_conversion_types GLCT, ar_lookups L1, ar_lookups L3, ar_lookups L4, ar_lookups L2, ar_lookups L5, ar_lookups L6 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 /* Bug 2087048 */ CTLSR.cust_trx_line_salesrep_id(+) = CTLGD.cust_trx_line_salesrep_id AND NVL (CTLSR.salesrep_id, -3) = S.salesrep_id AND NVL (CT.org_id, -99) = NVL (S.org_id, -99) AND /* end 2087048 */ 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 NVL (CT.org_id, -99) = NVL (CTT.org_id, -99) AND CT.cust_trx_type_id = CTT.cust_trx_type_id 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 NVL (CT.org_id, -99) = NVL (BS.org_id, -99) AND CT.batch_source_id = BS.batch_source_id 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) AND CTLGD.cust_trx_line_gl_dist_id = TO_NUMBER (R.reference_3) AND R.reference_10 = 'RA_CUST_TRX_LINE_GL_DIST' AND R.je_header_id = JEH.je_header_id AND JEH.je_category IN ('Sales Invoices', 'Credit Memos', 'Debit Memos', 'Chargebacks') UNION ALL /*------------------------------------------------------------------------+ Credit Memo Applications +------------------------------------------------------------------------*/ SELECT /*+ORDERED */ JEH.je_header_id JE_HEADER_ID, R.je_line_num JE_LINE_NUM, 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, 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, CUST_ACCT.cust_account_id THIRD_PARTY_ID, CUST_ACCT.account_number THIRD_PARTY_NUMBER, SUBSTRB (PARTY.party_name, 1, 50) THIRD_PARTY_NAME, SU.site_use_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, 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, /* The following columns are specific to AR Credit memos */ 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 gl_je_headers JEH, gl_import_references R, ar_distributions_all ARD, ar_receivable_applications_all RA, ra_customer_trx_all CTCM, ra_customer_trx_all CTINV, hz_cust_accounts CUST_ACCT, hz_parties PARTY, ra_cust_trx_types_all CTTCM, ra_cust_trx_types_all CTTCM2, ra_customer_trx_lines_all CTLINV, hz_cust_site_uses_all SU, fnd_document_sequences SEQ, gl_daily_conversion_types GLCT, ar_lookups L1, ar_lookups L4, ar_lookups L3, 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 (NVL (RA.postable, 'Y'), 'Y', '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 CTCM.bill_to_site_use_id = SU.site_use_id AND NVL (ard.org_id, -99) = NVL (SU.org_id, -99) AND CTCM.bill_to_customer_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 NVL (CTTCM.org_id, -99) = NVL (CTCM.org_id, -99) AND CTTCM.cust_trx_type_id = CTCM.cust_trx_type_id AND ARD.line_id = TO_NUMBER (R.reference_3) AND R.je_header_id = JEH.je_header_id AND JEH.je_category = 'Credit Memo Applications'; DROP SYNONYM OHS_ACCNT.AR_AEL_GL_INV_V; CREATE SYNONYM OHS_ACCNT.AR_AEL_GL_INV_V FOR APPS.AR_AEL_GL_INV_V; DROP SYNONYM RAC_ACCNT.AR_AEL_GL_INV_V; CREATE SYNONYM RAC_ACCNT.AR_AEL_GL_INV_V FOR APPS.AR_AEL_GL_INV_V; DROP SYNONYM BOLINF.AR_AEL_GL_INV_V; CREATE SYNONYM BOLINF.AR_AEL_GL_INV_V FOR APPS.AR_AEL_GL_INV_V; DROP SYNONYM APPSRO.AR_AEL_GL_INV_V; CREATE SYNONYM APPSRO.AR_AEL_GL_INV_V FOR APPS.AR_AEL_GL_INV_V; GRANT SELECT ON APPS.AR_AEL_GL_INV_V TO BOLINF; GRANT SELECT ON APPS.AR_AEL_GL_INV_V TO OHS_ACCNT; GRANT SELECT ON APPS.AR_AEL_GL_INV_V TO RAC_ACCNT;