VIEW: APPS.FII_AR_OLTP_INV_V

Source

View Text - Preformatted

SELECT inv_h.customer_trx_id, inst.instance_code, inv.org_id, inv.set_of_books_id, sob.period_set_name, sob.accounted_period_type, inv.gl_date, sob.currency_code, edw_currency.convert_global_amount( inv.amount, inv.acctd_amount, inv_h.invoice_currency_code, sob.currency_code, inv_h.trx_date, inv_h.exchange_rate_type), inv.acctd_amount FROM edw_local_instance inst, ra_cust_trx_line_gl_dist_all inv, ra_customer_trx_all inv_h, gl_sets_of_books sob, ra_cust_trx_types_all inv_t WHERE sob.set_of_books_id = inv.set_of_books_id and inv_h.customer_trx_id = inv.customer_trx_id and inv.account_class = 'REC' and inv.account_set_flag = 'N' and inv_h.complete_flag = 'Y' and inv_t.cust_trx_type_id (+) = inv_h.cust_trx_type_id and inv_t.org_id(+) = inv_h.org_id and nvl(inv_t.post_to_gl,'Y') = 'Y' and nvl(inv_t.accounting_affect_flag, 'Y') = 'Y' union all select ct.customer_trx_id, inst.instance_code, adj.org_id, adj.set_of_books_id, sob.period_set_name, sob.accounted_period_type, adj.gl_date, sob.currency_code, edw_currency.convert_global_amount( nvl(ad.amount_dr,0) - nvl(ad.amount_cr,0), nvl(ad.acctd_amount_dr,0) - nvl(ad.acctd_amount_cr,0), ct.invoice_currency_code, sob.currency_code, adj.gl_date, ad.currency_conversion_type), nvl(ad.acctd_amount_dr,0) - nvl(acctd_amount_cr,0) from edw_local_instance inst, ar_adjustments_all adj, ra_customer_trx_all ct, gl_sets_of_books sob, ar_distributions_all ad where sob.set_of_books_id = adj.set_of_books_id and nvl(adj.status, 'A') = 'A' and nvl(adj.postable,'Y') = 'Y' and ct.customer_trx_id = adj.customer_trx_id and ct.complete_flag = 'Y' and ad.source_id = adj.adjustment_id and ad.source_table = 'ADJ' and ad.source_type = 'REC'

View Text - HTML Formatted

SELECT INV_H.CUSTOMER_TRX_ID
, INST.INSTANCE_CODE
, INV.ORG_ID
, INV.SET_OF_BOOKS_ID
, SOB.PERIOD_SET_NAME
, SOB.ACCOUNTED_PERIOD_TYPE
, INV.GL_DATE
, SOB.CURRENCY_CODE
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( INV.AMOUNT
, INV.ACCTD_AMOUNT
, INV_H.INVOICE_CURRENCY_CODE
, SOB.CURRENCY_CODE
, INV_H.TRX_DATE
, INV_H.EXCHANGE_RATE_TYPE)
, INV.ACCTD_AMOUNT
FROM EDW_LOCAL_INSTANCE INST
, RA_CUST_TRX_LINE_GL_DIST_ALL INV
, RA_CUSTOMER_TRX_ALL INV_H
, GL_SETS_OF_BOOKS SOB
, RA_CUST_TRX_TYPES_ALL INV_T
WHERE SOB.SET_OF_BOOKS_ID = INV.SET_OF_BOOKS_ID
AND INV_H.CUSTOMER_TRX_ID = INV.CUSTOMER_TRX_ID
AND INV.ACCOUNT_CLASS = 'REC'
AND INV.ACCOUNT_SET_FLAG = 'N'
AND INV_H.COMPLETE_FLAG = 'Y'
AND INV_T.CUST_TRX_TYPE_ID (+) = INV_H.CUST_TRX_TYPE_ID
AND INV_T.ORG_ID(+) = INV_H.ORG_ID
AND NVL(INV_T.POST_TO_GL
, 'Y') = 'Y'
AND NVL(INV_T.ACCOUNTING_AFFECT_FLAG
, 'Y') = 'Y' UNION ALL SELECT CT.CUSTOMER_TRX_ID
, INST.INSTANCE_CODE
, ADJ.ORG_ID
, ADJ.SET_OF_BOOKS_ID
, SOB.PERIOD_SET_NAME
, SOB.ACCOUNTED_PERIOD_TYPE
, ADJ.GL_DATE
, SOB.CURRENCY_CODE
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( NVL(AD.AMOUNT_DR
, 0) - NVL(AD.AMOUNT_CR
, 0)
, NVL(AD.ACCTD_AMOUNT_DR
, 0) - NVL(AD.ACCTD_AMOUNT_CR
, 0)
, CT.INVOICE_CURRENCY_CODE
, SOB.CURRENCY_CODE
, ADJ.GL_DATE
, AD.CURRENCY_CONVERSION_TYPE)
, NVL(AD.ACCTD_AMOUNT_DR
, 0) - NVL(ACCTD_AMOUNT_CR
, 0)
FROM EDW_LOCAL_INSTANCE INST
, AR_ADJUSTMENTS_ALL ADJ
, RA_CUSTOMER_TRX_ALL CT
, GL_SETS_OF_BOOKS SOB
, AR_DISTRIBUTIONS_ALL AD
WHERE SOB.SET_OF_BOOKS_ID = ADJ.SET_OF_BOOKS_ID
AND NVL(ADJ.STATUS
, 'A') = 'A'
AND NVL(ADJ.POSTABLE
, 'Y') = 'Y'
AND CT.CUSTOMER_TRX_ID = ADJ.CUSTOMER_TRX_ID
AND CT.COMPLETE_FLAG = 'Y'
AND AD.SOURCE_ID = ADJ.ADJUSTMENT_ID
AND AD.SOURCE_TABLE = 'ADJ'
AND AD.SOURCE_TYPE = 'REC'