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_ADJ_MRC_V

Source

View Text - Preformatted


SELECT 222 APPLICATION_ID , ADJ.set_of_books_id SET_OF_BOOKS_ID , ADJ.org_id ORG_ID , L5.lookup_code TRX_CLASS , L5.meaning TRX_CLASS_NAME , ADJ.receivables_trx_id TRX_TYPE_N , TRXACT.name TRX_TYPE_NAME , ADJ.adjustment_number TRX_NUMBER_DISPLAYED , ADJ.adjustment_number TRX_NUMBER_C , ADJ.apply_date TRX_DATE , ADJ.comments COMMENTS , ADJ.doc_sequence_id DOC_SEQUENCE_ID , SEQ.name DOC_SEQUENCE_NAME , ADJ.doc_sequence_value DOC_SEQUENCE_VALUE, 'ADJ' TRX_HDR_TABLE, ADJ.adjustment_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.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, ADJ.gl_date ACCOUNTING_DATE, L4.meaning GL_TRANSFER_STATUS_NAME, decode(nvl(ADJ.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, L5.meaning||' '||ADJ.adjustment_number AE_LINE_REFERENCE, rpad(ADJ.adjustment_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, L2.meaning AR_ADJUSTMENT_CREATION_TYPE, L3.meaning AR_ADJUSTMENT_TYPE, CT_CHG.trx_number CHARGEBACK_NUMBER, 'CT' APPLIED_TO_TRX_HDR_TABLE, CT.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 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, hz_cust_site_uses_all SU, hz_cust_accounts c, hz_parties party, ra_cust_trx_types_all CTT, ar_receivables_trx_all TRXACT, ar_vat_tax_all VAT, ra_customer_trx_all_mrc_v CT, ra_customer_trx_all_mrc_v CT_CHG, ar_distributions_all_mrc_v ARD, ar_adjustments_all_mrc_v ADJ WHERE L1.lookup_type = 'INV/CM' AND L1.lookup_code = CTT.type AND L2.lookup_type = 'ADJUSTMENT_CREATION_TYPE' AND L2.lookup_code = ADJ.adjustment_type AND L3.lookup_type = 'ADJUSTMENT_TYPE' AND L3.lookup_code = ADJ.type AND L4.lookup_type = 'YES/NO' AND L4.lookup_code = decode(nvl(ADJ.postable,'Y'),'Y','N','Y') AND L5.lookup_type = 'ADJUSTMENT_TRX_CLASS' AND L5.lookup_code = decode(L1.lookup_code, 'CB', 'CB_ADJUSTMENT', 'CM', 'CM_ADJUSTMENT', 'DEP', 'DEP_ADJUSTMENT', 'DM', 'DM_ADJUSTMENT', 'GUAR', 'GUAR_ADJUSTMENT', 'INV', 'INV_ADJUSTMENT', 'PMT', 'PMT_ADJUSTMENT' ) AND L6.lookup_type = 'DISTRIBUTION_SOURCE_TYPE' AND L6.lookup_code = ARD.source_type AND ARD.source_table = 'ADJ' AND ARD.source_id = ADJ.adjustment_id AND nvl(ARD.org_id,-99) = nvl(ADJ.org_id,-99) AND GLCT.conversion_type(+) = ARD.currency_conversion_type AND SEQ.doc_sequence_id(+) = ADJ.doc_sequence_id AND CT.customer_trx_id = ADJ.customer_trx_id AND nvl(CT.org_id,-99) = nvl(ADJ.org_id,-99) AND CTT.cust_trx_type_id = CT.cust_trx_type_id AND nvl(CTT.org_id,-99) = nvl(CT.org_id,-99) AND VAT.vat_tax_id(+) = ARD.tax_code_id AND nvl(VAT.org_id(+),-99) = nvl(ARD.org_id,-99) AND SU.site_use_id = ARD.third_party_sub_id AND nvl(SU.org_id,-99) = nvl(ARD.org_id,-99) AND C.cust_account_id = ARD.third_party_id and c.party_id = party.party_id AND CT_CHG.customer_trx_id(+) = ADJ.chargeback_customer_trx_id AND nvl(CT_CHG.org_id(+),-99) = nvl(ADJ.org_id,-99) AND TRXACT.receivables_trx_id(+) = ADJ.receivables_trx_id AND nvl(TRXACT.org_id(+),-99) = nvl(ADJ.org_id,-99)

View Text - HTML Formatted

SELECT 222 APPLICATION_ID
, ADJ.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, ADJ.ORG_ID ORG_ID
, L5.LOOKUP_CODE TRX_CLASS
, L5.MEANING TRX_CLASS_NAME
, ADJ.RECEIVABLES_TRX_ID TRX_TYPE_N
, TRXACT.NAME TRX_TYPE_NAME
, ADJ.ADJUSTMENT_NUMBER TRX_NUMBER_DISPLAYED
, ADJ.ADJUSTMENT_NUMBER TRX_NUMBER_C
, ADJ.APPLY_DATE TRX_DATE
, ADJ.COMMENTS COMMENTS
, ADJ.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, SEQ.NAME DOC_SEQUENCE_NAME
, ADJ.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, 'ADJ' TRX_HDR_TABLE
, ADJ.ADJUSTMENT_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.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
, ADJ.GL_DATE ACCOUNTING_DATE
, L4.MEANING GL_TRANSFER_STATUS_NAME
, DECODE(NVL(ADJ.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
, L5.MEANING||' '||ADJ.ADJUSTMENT_NUMBER AE_LINE_REFERENCE
, RPAD(ADJ.ADJUSTMENT_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
, L2.MEANING AR_ADJUSTMENT_CREATION_TYPE
, L3.MEANING AR_ADJUSTMENT_TYPE
, CT_CHG.TRX_NUMBER CHARGEBACK_NUMBER
, 'CT' APPLIED_TO_TRX_HDR_TABLE
, CT.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
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
, HZ_CUST_SITE_USES_ALL SU
, HZ_CUST_ACCOUNTS C
, HZ_PARTIES PARTY
, RA_CUST_TRX_TYPES_ALL CTT
, AR_RECEIVABLES_TRX_ALL TRXACT
, AR_VAT_TAX_ALL VAT
, RA_CUSTOMER_TRX_ALL_MRC_V CT
, RA_CUSTOMER_TRX_ALL_MRC_V CT_CHG
, AR_DISTRIBUTIONS_ALL_MRC_V ARD
, AR_ADJUSTMENTS_ALL_MRC_V ADJ
WHERE L1.LOOKUP_TYPE = 'INV/CM'
AND L1.LOOKUP_CODE = CTT.TYPE
AND L2.LOOKUP_TYPE = 'ADJUSTMENT_CREATION_TYPE'
AND L2.LOOKUP_CODE = ADJ.ADJUSTMENT_TYPE
AND L3.LOOKUP_TYPE = 'ADJUSTMENT_TYPE'
AND L3.LOOKUP_CODE = ADJ.TYPE
AND L4.LOOKUP_TYPE = 'YES/NO'
AND L4.LOOKUP_CODE = DECODE(NVL(ADJ.POSTABLE
, 'Y')
, 'Y'
, 'N'
, 'Y')
AND L5.LOOKUP_TYPE = 'ADJUSTMENT_TRX_CLASS'
AND L5.LOOKUP_CODE = DECODE(L1.LOOKUP_CODE
, 'CB'
, 'CB_ADJUSTMENT'
, 'CM'
, 'CM_ADJUSTMENT'
, 'DEP'
, 'DEP_ADJUSTMENT'
, 'DM'
, 'DM_ADJUSTMENT'
, 'GUAR'
, 'GUAR_ADJUSTMENT'
, 'INV'
, 'INV_ADJUSTMENT'
, 'PMT'
, 'PMT_ADJUSTMENT' )
AND L6.LOOKUP_TYPE = 'DISTRIBUTION_SOURCE_TYPE'
AND L6.LOOKUP_CODE = ARD.SOURCE_TYPE
AND ARD.SOURCE_TABLE = 'ADJ'
AND ARD.SOURCE_ID = ADJ.ADJUSTMENT_ID
AND NVL(ARD.ORG_ID
, -99) = NVL(ADJ.ORG_ID
, -99)
AND GLCT.CONVERSION_TYPE(+) = ARD.CURRENCY_CONVERSION_TYPE
AND SEQ.DOC_SEQUENCE_ID(+) = ADJ.DOC_SEQUENCE_ID
AND CT.CUSTOMER_TRX_ID = ADJ.CUSTOMER_TRX_ID
AND NVL(CT.ORG_ID
, -99) = NVL(ADJ.ORG_ID
, -99)
AND CTT.CUST_TRX_TYPE_ID = CT.CUST_TRX_TYPE_ID
AND NVL(CTT.ORG_ID
, -99) = NVL(CT.ORG_ID
, -99)
AND VAT.VAT_TAX_ID(+) = ARD.TAX_CODE_ID
AND NVL(VAT.ORG_ID(+)
, -99) = NVL(ARD.ORG_ID
, -99)
AND SU.SITE_USE_ID = ARD.THIRD_PARTY_SUB_ID
AND NVL(SU.ORG_ID
, -99) = NVL(ARD.ORG_ID
, -99)
AND C.CUST_ACCOUNT_ID = ARD.THIRD_PARTY_ID
AND C.PARTY_ID = PARTY.PARTY_ID
AND CT_CHG.CUSTOMER_TRX_ID(+) = ADJ.CHARGEBACK_CUSTOMER_TRX_ID
AND NVL(CT_CHG.ORG_ID(+)
, -99) = NVL(ADJ.ORG_ID
, -99)
AND TRXACT.RECEIVABLES_TRX_ID(+) = ADJ.RECEIVABLES_TRX_ID
AND NVL(TRXACT.ORG_ID(+)
, -99) = NVL(ADJ.ORG_ID
, -99)