VIEW: APPS.AR_CORRECT_GL_LINES_V

Source

View Text - Preformatted

SELECT ARPT_SQL_FUNC_UTIL.get_lookup_meaning( DECODE(acc.distribution_type, 'REC', 'DISTRIBUTION_SOURCE_TYPE', 'AUTOGL_TYPE'),acc.distribution_type), acc.distribution_type, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INV/CM/ADJ',acc.category_type), acc.category_type, acc.distribution_id, acc.parent_id, acc.source_table, acc.code_combination_id, trx.trx_number, ttype.name, acc.gl_date, src.name, srep.name, party.party_name, NULL, DECODE(IBY.INSTRUMENT_TYPE,'BANKACCOUNT',IBY.ACCOUNT_NUMBER, 'CREDITCARD', IBY.CARD_NUMBER , NULL) from ar_ccid_corrections acc, ra_customer_trx trx, ra_cust_trx_line_gl_dist gld, ra_cust_trx_types ttype, ra_batch_sources src, iby_trxn_extensions_v iby, ra_cust_trx_line_salesreps sr, ra_salesreps srep, hz_parties party, hz_cust_accounts cust WHERE acc.parent_id = trx.customer_trx_id and acc.source_table = 'GLD' and acc.distribution_id = gld.cust_trx_line_gl_dist_id and trx.cust_trx_type_id = ttype.cust_trx_type_id and src.batch_source_id (+) = trx.batch_source_id and trx.payment_trxn_extension_id = iby.trxn_extension_id(+) and gld.cust_trx_line_salesrep_id = sr.cust_trx_line_salesrep_id (+) and nvl(nvl(sr.salesrep_id, trx.primary_salesrep_id), -3) = srep.salesrep_id and cust.cust_account_id = trx.bill_to_customer_id and cust.party_id = party.party_id UNION ALL select ARPT_SQL_FUNC_UTIL.get_lookup_meaning( 'DISTRIBUTION_SOURCE_TYPE',acc.distribution_type), acc.distribution_type, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ARRGTA_CATEGORIES',acc.category_type), acc.category_type, acc.distribution_id, acc.parent_id, acc.source_table, acc.code_combination_id, adj.adjustment_number, adj.reason_code, acc.gl_date, null, null, party.party_name, null, null from ar_ccid_corrections acc, ar_adjustments adj, ra_customer_trx trx, hz_parties party, hz_cust_accounts cust where acc.source_table = 'ADJ' and acc.parent_id = adj.adjustment_id and adj.customer_trx_id = trx.customer_trx_id and cust.cust_account_id = trx.bill_to_customer_id and cust.party_id = party.party_id UNION ALL select ARPT_SQL_FUNC_UTIL.get_lookup_meaning('DISTRIBUTION_SOURCE_TYPE',acc.distribution_type), acc.distribution_type, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ARRGTA_CATEGORIES',acc.category_type), acc.category_type, acc.distribution_id, acc.parent_id, acc.source_table, acc.code_combination_id, cr.receipt_number, null, acc.gl_date, null, null, party.party_name, NULL, DECODE(IBY.INSTRUMENT_TYPE,'BANKACCOUNT',IBY.ACCOUNT_NUMBER, 'CREDITCARD', IBY.CARD_NUMBER , NULL) from ar_ccid_corrections acc, ar_cash_receipt_history crh, ar_cash_receipts cr, iby_trxn_extensions_v iby, hz_parties party, hz_cust_accounts cust where acc.source_table = 'CRH' and acc.parent_id = crh.cash_receipt_history_id and crh.cash_receipt_id = cr.cash_receipt_id and cust.cust_account_id (+) = cr.pay_from_customer and cust.party_id = party.party_id (+) and cr.payment_trxn_extension_id = iby.trxn_extension_id(+) UNION ALL select ARPT_SQL_FUNC_UTIL.get_lookup_meaning( 'DISTRIBUTION_SOURCE_TYPE',acc.distribution_type), acc.distribution_type, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ARRGTA_CATEGORIES',acc.category_type), acc.category_type, acc.distribution_id, acc.parent_id, acc.source_table, acc.code_combination_id, cr.receipt_number, null, acc.gl_date, null, null, party.party_name, NULL, DECODE(IBY.INSTRUMENT_TYPE,'BANKACCOUNT',IBY.ACCOUNT_NUMBER, 'CREDITCARD', IBY.CARD_NUMBER , NULL) from ar_ccid_corrections acc, ar_receivable_applications ra, ar_cash_receipts cr, iby_trxn_extensions_v iby, hz_parties party, hz_cust_accounts cust where acc.source_table = 'RA' and acc.parent_id = ra.receivable_application_id and ra.cash_receipt_id = cr.cash_receipt_id and cust.cust_account_id (+) = cr.pay_from_customer and cust.party_id = party.party_id (+) and cr.payment_trxn_extension_id = iby.trxn_extension_id(+) UNION ALL select ARPT_SQL_FUNC_UTIL.get_lookup_meaning( 'DISTRIBUTION_SOURCE_TYPE',acc.distribution_type), acc.distribution_type, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ARRGTA_CATEGORIES',acc.category_type), acc.category_type, acc.distribution_id, acc.parent_id, acc.source_table, acc.code_combination_id, cm.trx_number, ttype.name, acc.gl_date, null, null, party.party_name, NULL, DECODE(IBY.INSTRUMENT_TYPE,'BANKACCOUNT',IBY.ACCOUNT_NUMBER, 'CREDITCARD', IBY.CARD_NUMBER , NULL) from ar_ccid_corrections acc, ra_customer_trx cm, ra_cust_trx_types ttype, ar_receivable_applications ra, iby_trxn_extensions_v iby, hz_parties party, hz_cust_accounts cust where acc.source_table = 'RA' and acc.parent_id = ra.receivable_application_id and ra.customer_trx_id = cm.customer_trx_id and cm.cust_trx_type_id = ttype.cust_trx_type_id and cust.cust_account_id = cm.bill_to_customer_id and cust.party_id = party.party_id and cm.payment_trxn_extension_id = iby.trxn_extension_id(+) UNION ALL select ARPT_SQL_FUNC_UTIL.get_lookup_meaning('DISTRIBUTION_SOURCE_TYPE',acc.distribution_type), acc.distribution_type, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ARRGTA_CATEGORIES',acc.category_type), acc.category_type, acc.distribution_id, acc.parent_id, acc.source_table, acc.code_combination_id, cr.receipt_number, null, acc.gl_date, null, null, null, null, null from ar_ccid_corrections acc, ar_misc_cash_distributions mcd, ar_cash_receipts cr where acc.source_table = 'MCD' and acc.parent_id = mcd.misc_cash_distribution_id and mcd.cash_receipt_id = cr.cash_receipt_id UNION ALL select ARPT_SQL_FUNC_UTIL.get_lookup_meaning( 'DISTRIBUTION_SOURCE_TYPE',acc.distribution_type), acc.distribution_type, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ARRGTA_CATEGORIES',acc.category_type), acc.category_type, acc.distribution_id, acc.parent_id, acc.source_table, acc.code_combination_id, trx.trx_number, ttype.name, acc.gl_date, null, null, party.party_name, NULL, DECODE(IBY.INSTRUMENT_TYPE,'BANKACCOUNT',IBY.ACCOUNT_NUMBER, 'CREDITCARD', IBY.CARD_NUMBER , NULL) from ar_ccid_corrections acc, ar_transaction_history th, ra_customer_trx trx, ra_cust_trx_types ttype, hz_parties party, hz_cust_accounts cust, iby_trxn_extensions_v iby where acc.source_table = 'TH' and acc.parent_id = th.transaction_history_id and th.customer_trx_id = trx.customer_trx_id and trx.cust_trx_type_id = ttype.cust_trx_type_id and cust.cust_account_id = trx.drawee_id and cust.party_id = party.party_id and trx.payment_trxn_extension_id = iby.trxn_extension_id(+)

View Text - HTML Formatted

SELECT ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( DECODE(ACC.DISTRIBUTION_TYPE
, 'REC'
, 'DISTRIBUTION_SOURCE_TYPE'
, 'AUTOGL_TYPE')
, ACC.DISTRIBUTION_TYPE)
, ACC.DISTRIBUTION_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM/ADJ'
, ACC.CATEGORY_TYPE)
, ACC.CATEGORY_TYPE
, ACC.DISTRIBUTION_ID
, ACC.PARENT_ID
, ACC.SOURCE_TABLE
, ACC.CODE_COMBINATION_ID
, TRX.TRX_NUMBER
, TTYPE.NAME
, ACC.GL_DATE
, SRC.NAME
, SREP.NAME
, PARTY.PARTY_NAME
, NULL
, DECODE(IBY.INSTRUMENT_TYPE
, 'BANKACCOUNT'
, IBY.ACCOUNT_NUMBER
, 'CREDITCARD'
, IBY.CARD_NUMBER
, NULL)
FROM AR_CCID_CORRECTIONS ACC
, RA_CUSTOMER_TRX TRX
, RA_CUST_TRX_LINE_GL_DIST GLD
, RA_CUST_TRX_TYPES TTYPE
, RA_BATCH_SOURCES SRC
, IBY_TRXN_EXTENSIONS_V IBY
, RA_CUST_TRX_LINE_SALESREPS SR
, RA_SALESREPS SREP
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST
WHERE ACC.PARENT_ID = TRX.CUSTOMER_TRX_ID
AND ACC.SOURCE_TABLE = 'GLD'
AND ACC.DISTRIBUTION_ID = GLD.CUST_TRX_LINE_GL_DIST_ID
AND TRX.CUST_TRX_TYPE_ID = TTYPE.CUST_TRX_TYPE_ID
AND SRC.BATCH_SOURCE_ID (+) = TRX.BATCH_SOURCE_ID
AND TRX.PAYMENT_TRXN_EXTENSION_ID = IBY.TRXN_EXTENSION_ID(+)
AND GLD.CUST_TRX_LINE_SALESREP_ID = SR.CUST_TRX_LINE_SALESREP_ID (+)
AND NVL(NVL(SR.SALESREP_ID
, TRX.PRIMARY_SALESREP_ID)
, -3) = SREP.SALESREP_ID
AND CUST.CUST_ACCOUNT_ID = TRX.BILL_TO_CUSTOMER_ID
AND CUST.PARTY_ID = PARTY.PARTY_ID UNION ALL SELECT ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'DISTRIBUTION_SOURCE_TYPE'
, ACC.DISTRIBUTION_TYPE)
, ACC.DISTRIBUTION_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ARRGTA_CATEGORIES'
, ACC.CATEGORY_TYPE)
, ACC.CATEGORY_TYPE
, ACC.DISTRIBUTION_ID
, ACC.PARENT_ID
, ACC.SOURCE_TABLE
, ACC.CODE_COMBINATION_ID
, ADJ.ADJUSTMENT_NUMBER
, ADJ.REASON_CODE
, ACC.GL_DATE
, NULL
, NULL
, PARTY.PARTY_NAME
, NULL
, NULL
FROM AR_CCID_CORRECTIONS ACC
, AR_ADJUSTMENTS ADJ
, RA_CUSTOMER_TRX TRX
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST
WHERE ACC.SOURCE_TABLE = 'ADJ'
AND ACC.PARENT_ID = ADJ.ADJUSTMENT_ID
AND ADJ.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND CUST.CUST_ACCOUNT_ID = TRX.BILL_TO_CUSTOMER_ID
AND CUST.PARTY_ID = PARTY.PARTY_ID UNION ALL SELECT ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('DISTRIBUTION_SOURCE_TYPE'
, ACC.DISTRIBUTION_TYPE)
, ACC.DISTRIBUTION_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ARRGTA_CATEGORIES'
, ACC.CATEGORY_TYPE)
, ACC.CATEGORY_TYPE
, ACC.DISTRIBUTION_ID
, ACC.PARENT_ID
, ACC.SOURCE_TABLE
, ACC.CODE_COMBINATION_ID
, CR.RECEIPT_NUMBER
, NULL
, ACC.GL_DATE
, NULL
, NULL
, PARTY.PARTY_NAME
, NULL
, DECODE(IBY.INSTRUMENT_TYPE
, 'BANKACCOUNT'
, IBY.ACCOUNT_NUMBER
, 'CREDITCARD'
, IBY.CARD_NUMBER
, NULL)
FROM AR_CCID_CORRECTIONS ACC
, AR_CASH_RECEIPT_HISTORY CRH
, AR_CASH_RECEIPTS CR
, IBY_TRXN_EXTENSIONS_V IBY
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST
WHERE ACC.SOURCE_TABLE = 'CRH'
AND ACC.PARENT_ID = CRH.CASH_RECEIPT_HISTORY_ID
AND CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CUST.CUST_ACCOUNT_ID (+) = CR.PAY_FROM_CUSTOMER
AND CUST.PARTY_ID = PARTY.PARTY_ID (+)
AND CR.PAYMENT_TRXN_EXTENSION_ID = IBY.TRXN_EXTENSION_ID(+) UNION ALL SELECT ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'DISTRIBUTION_SOURCE_TYPE'
, ACC.DISTRIBUTION_TYPE)
, ACC.DISTRIBUTION_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ARRGTA_CATEGORIES'
, ACC.CATEGORY_TYPE)
, ACC.CATEGORY_TYPE
, ACC.DISTRIBUTION_ID
, ACC.PARENT_ID
, ACC.SOURCE_TABLE
, ACC.CODE_COMBINATION_ID
, CR.RECEIPT_NUMBER
, NULL
, ACC.GL_DATE
, NULL
, NULL
, PARTY.PARTY_NAME
, NULL
, DECODE(IBY.INSTRUMENT_TYPE
, 'BANKACCOUNT'
, IBY.ACCOUNT_NUMBER
, 'CREDITCARD'
, IBY.CARD_NUMBER
, NULL)
FROM AR_CCID_CORRECTIONS ACC
, AR_RECEIVABLE_APPLICATIONS RA
, AR_CASH_RECEIPTS CR
, IBY_TRXN_EXTENSIONS_V IBY
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST
WHERE ACC.SOURCE_TABLE = 'RA'
AND ACC.PARENT_ID = RA.RECEIVABLE_APPLICATION_ID
AND RA.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CUST.CUST_ACCOUNT_ID (+) = CR.PAY_FROM_CUSTOMER
AND CUST.PARTY_ID = PARTY.PARTY_ID (+)
AND CR.PAYMENT_TRXN_EXTENSION_ID = IBY.TRXN_EXTENSION_ID(+) UNION ALL SELECT ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'DISTRIBUTION_SOURCE_TYPE'
, ACC.DISTRIBUTION_TYPE)
, ACC.DISTRIBUTION_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ARRGTA_CATEGORIES'
, ACC.CATEGORY_TYPE)
, ACC.CATEGORY_TYPE
, ACC.DISTRIBUTION_ID
, ACC.PARENT_ID
, ACC.SOURCE_TABLE
, ACC.CODE_COMBINATION_ID
, CM.TRX_NUMBER
, TTYPE.NAME
, ACC.GL_DATE
, NULL
, NULL
, PARTY.PARTY_NAME
, NULL
, DECODE(IBY.INSTRUMENT_TYPE
, 'BANKACCOUNT'
, IBY.ACCOUNT_NUMBER
, 'CREDITCARD'
, IBY.CARD_NUMBER
, NULL)
FROM AR_CCID_CORRECTIONS ACC
, RA_CUSTOMER_TRX CM
, RA_CUST_TRX_TYPES TTYPE
, AR_RECEIVABLE_APPLICATIONS RA
, IBY_TRXN_EXTENSIONS_V IBY
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST
WHERE ACC.SOURCE_TABLE = 'RA'
AND ACC.PARENT_ID = RA.RECEIVABLE_APPLICATION_ID
AND RA.CUSTOMER_TRX_ID = CM.CUSTOMER_TRX_ID
AND CM.CUST_TRX_TYPE_ID = TTYPE.CUST_TRX_TYPE_ID
AND CUST.CUST_ACCOUNT_ID = CM.BILL_TO_CUSTOMER_ID
AND CUST.PARTY_ID = PARTY.PARTY_ID
AND CM.PAYMENT_TRXN_EXTENSION_ID = IBY.TRXN_EXTENSION_ID(+) UNION ALL SELECT ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('DISTRIBUTION_SOURCE_TYPE'
, ACC.DISTRIBUTION_TYPE)
, ACC.DISTRIBUTION_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ARRGTA_CATEGORIES'
, ACC.CATEGORY_TYPE)
, ACC.CATEGORY_TYPE
, ACC.DISTRIBUTION_ID
, ACC.PARENT_ID
, ACC.SOURCE_TABLE
, ACC.CODE_COMBINATION_ID
, CR.RECEIPT_NUMBER
, NULL
, ACC.GL_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
FROM AR_CCID_CORRECTIONS ACC
, AR_MISC_CASH_DISTRIBUTIONS MCD
, AR_CASH_RECEIPTS CR
WHERE ACC.SOURCE_TABLE = 'MCD'
AND ACC.PARENT_ID = MCD.MISC_CASH_DISTRIBUTION_ID
AND MCD.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID UNION ALL SELECT ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'DISTRIBUTION_SOURCE_TYPE'
, ACC.DISTRIBUTION_TYPE)
, ACC.DISTRIBUTION_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ARRGTA_CATEGORIES'
, ACC.CATEGORY_TYPE)
, ACC.CATEGORY_TYPE
, ACC.DISTRIBUTION_ID
, ACC.PARENT_ID
, ACC.SOURCE_TABLE
, ACC.CODE_COMBINATION_ID
, TRX.TRX_NUMBER
, TTYPE.NAME
, ACC.GL_DATE
, NULL
, NULL
, PARTY.PARTY_NAME
, NULL
, DECODE(IBY.INSTRUMENT_TYPE
, 'BANKACCOUNT'
, IBY.ACCOUNT_NUMBER
, 'CREDITCARD'
, IBY.CARD_NUMBER
, NULL)
FROM AR_CCID_CORRECTIONS ACC
, AR_TRANSACTION_HISTORY TH
, RA_CUSTOMER_TRX TRX
, RA_CUST_TRX_TYPES TTYPE
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST
, IBY_TRXN_EXTENSIONS_V IBY
WHERE ACC.SOURCE_TABLE = 'TH'
AND ACC.PARENT_ID = TH.TRANSACTION_HISTORY_ID
AND TH.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND TRX.CUST_TRX_TYPE_ID = TTYPE.CUST_TRX_TYPE_ID
AND CUST.CUST_ACCOUNT_ID = TRX.DRAWEE_ID
AND CUST.PARTY_ID = PARTY.PARTY_ID
AND TRX.PAYMENT_TRXN_EXTENSION_ID = IBY.TRXN_EXTENSION_ID(+)