VIEW: APPS.FII_AR_REC_FCV

Source

View Text - Preformatted

SELECT inv_h.customer_trx_id, inv.set_of_books_id, inv.set_of_books_id||'-'||inst.instance_code, decode(inv_h.bill_to_customer_id,to_number(NULL),'NA_EDW', inv_h.bill_to_customer_id||'-'||inst.instance_code|| '-CUST_ACCT-TPRT'), inv.org_id||'-'||inst.instance_code, trunc(inv.gl_date), inv.gl_date, inv_h.trx_date, inst.instance_code, sob.currency_code, 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, adj.set_of_books_id, adj.set_of_books_id||'-'||inst.instance_code, decode(ct.bill_to_customer_id,to_number(NULL),'NA_EDW', ct.bill_to_customer_id||'-'||inst.instance_code|| '-CUST_ACCT-TPRT'), adj.org_id||'-'||inst.instance_code, trunc(adj.gl_date), adj.gl_date, ct.trx_date, inst.instance_code, sob.currency_code, 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
, INV.SET_OF_BOOKS_ID
, INV.SET_OF_BOOKS_ID||'-'||INST.INSTANCE_CODE
, DECODE(INV_H.BILL_TO_CUSTOMER_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, INV_H.BILL_TO_CUSTOMER_ID||'-'||INST.INSTANCE_CODE|| '-CUST_ACCT-TPRT')
, INV.ORG_ID||'-'||INST.INSTANCE_CODE
, TRUNC(INV.GL_DATE)
, INV.GL_DATE
, INV_H.TRX_DATE
, INST.INSTANCE_CODE
, SOB.CURRENCY_CODE
, 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
, ADJ.SET_OF_BOOKS_ID
, ADJ.SET_OF_BOOKS_ID||'-'||INST.INSTANCE_CODE
, DECODE(CT.BILL_TO_CUSTOMER_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, CT.BILL_TO_CUSTOMER_ID||'-'||INST.INSTANCE_CODE|| '-CUST_ACCT-TPRT')
, ADJ.ORG_ID||'-'||INST.INSTANCE_CODE
, TRUNC(ADJ.GL_DATE)
, ADJ.GL_DATE
, CT.TRX_DATE
, INST.INSTANCE_CODE
, SOB.CURRENCY_CODE
, 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'