VIEW: APPS.FII_AR_OLTP_OPEN_INV_V

Source

View Text - Preformatted

SELECT inst.instance_code, ps.org_id, trx.set_of_books_id, trx.bill_to_site_use_id, edw_currency.convert_global_amount( ps.amount_due_remaining, ps.acctd_amount_due_remaining, ps.invoice_currency_code, sob.currency_code, nvl(ps.trx_date,ps.gl_date), ps.exchange_rate_type), ps.acctd_amount_due_remaining, ps.amount_due_remaining, to_number(null), to_number(null), to_number(null), sob.currency_code, ps.invoice_currency_code, ps.trx_number, 1, ps.trx_date, ps.due_date, 'I' FROM edw_local_instance inst, ar_payment_schedules_all ps, ra_customer_trx_all trx, gl_sets_of_books sob WHERE ps.status = 'OP' and ps.class <> 'PMT' and trx.customer_trx_id = ps.customer_trx_id and sob.set_of_books_id = trx.set_of_books_id union all select inst.instance_code, ps.org_id, cr.set_of_books_id, ps.customer_site_use_id, to_number(null), to_number(null), to_number(null), edw_currency.convert_global_amount( ps.amount_due_remaining * -1, ps.acctd_amount_due_remaining * -1, ps.invoice_currency_code, sob.currency_code, nvl(ps.trx_date,ps.gl_date), ps.exchange_rate_type), ps.acctd_amount_due_remaining * -1, ps.amount_due_remaining * -1, sob.currency_code, ps.invoice_currency_code, ps.trx_number, 1, ps.trx_date, to_date(null), 'P' FROM edw_local_instance inst, ar_payment_schedules_all ps, gl_sets_of_books sob, ar_cash_receipts_all cr where ps.status = 'OP' and ps.class = 'PMT' and cr.cash_receipt_id = ps.cash_receipt_id and sob.set_of_books_id = cr.set_of_books_id and cr.status in ('UNAPP','APP') and nvl(cr.confirmed_flag, 'Y') = 'Y'

View Text - HTML Formatted

SELECT INST.INSTANCE_CODE
, PS.ORG_ID
, TRX.SET_OF_BOOKS_ID
, TRX.BILL_TO_SITE_USE_ID
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( PS.AMOUNT_DUE_REMAINING
, PS.ACCTD_AMOUNT_DUE_REMAINING
, PS.INVOICE_CURRENCY_CODE
, SOB.CURRENCY_CODE
, NVL(PS.TRX_DATE
, PS.GL_DATE)
, PS.EXCHANGE_RATE_TYPE)
, PS.ACCTD_AMOUNT_DUE_REMAINING
, PS.AMOUNT_DUE_REMAINING
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, SOB.CURRENCY_CODE
, PS.INVOICE_CURRENCY_CODE
, PS.TRX_NUMBER
, 1
, PS.TRX_DATE
, PS.DUE_DATE
, 'I'
FROM EDW_LOCAL_INSTANCE INST
, AR_PAYMENT_SCHEDULES_ALL PS
, RA_CUSTOMER_TRX_ALL TRX
, GL_SETS_OF_BOOKS SOB
WHERE PS.STATUS = 'OP'
AND PS.CLASS <> 'PMT'
AND TRX.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID
AND SOB.SET_OF_BOOKS_ID = TRX.SET_OF_BOOKS_ID UNION ALL SELECT INST.INSTANCE_CODE
, PS.ORG_ID
, CR.SET_OF_BOOKS_ID
, PS.CUSTOMER_SITE_USE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( PS.AMOUNT_DUE_REMAINING * -1
, PS.ACCTD_AMOUNT_DUE_REMAINING * -1
, PS.INVOICE_CURRENCY_CODE
, SOB.CURRENCY_CODE
, NVL(PS.TRX_DATE
, PS.GL_DATE)
, PS.EXCHANGE_RATE_TYPE)
, PS.ACCTD_AMOUNT_DUE_REMAINING * -1
, PS.AMOUNT_DUE_REMAINING * -1
, SOB.CURRENCY_CODE
, PS.INVOICE_CURRENCY_CODE
, PS.TRX_NUMBER
, 1
, PS.TRX_DATE
, TO_DATE(NULL)
, 'P'
FROM EDW_LOCAL_INSTANCE INST
, AR_PAYMENT_SCHEDULES_ALL PS
, GL_SETS_OF_BOOKS SOB
, AR_CASH_RECEIPTS_ALL CR
WHERE PS.STATUS = 'OP'
AND PS.CLASS = 'PMT'
AND CR.CASH_RECEIPT_ID = PS.CASH_RECEIPT_ID
AND SOB.SET_OF_BOOKS_ID = CR.SET_OF_BOOKS_ID
AND CR.STATUS IN ('UNAPP'
, 'APP')
AND NVL(CR.CONFIRMED_FLAG
, 'Y') = 'Y'