VIEW: APPS.IEX_CS_BILLINGTRX_UV1

Source

View Text - Preformatted

SELECT dtls.lsm_id lsm_id, dtls.contract_number contract_number, styt.name transaction_type, dtls.invoice_number invoice_number, dtls.invoice_amount invoice_amount, dtls.line_identifier line_identifier, faa.asset_number asset_number, dtls.invoice_date invoice_date, dtls.due_date due_date, dtls.amount_original transaction_amount, dtls.amount_applied amount_applied, dtls.amount_remaining amount_remaining, dtls.customer_acct_id customer_account_id, dtls.org_id contract_authoring_org_id, dtls.chr_id khr_id, dtls.kle_id kle_id, faa.asset_id asset_id, dtls.invoice_id invoice_id, styt.id sty_id, dtls.invoice_currency_code currency_code, aps.exchange_rate_type ar_exchange_rate_type, aps.exchange_rate ar_exchange_rate, aps.exchange_date ar_exchange_date, aps.trx_date ar_trx_date, dtls.line_amount amount_line_items_original, dtls.amount_remaining amount_line_items_remaining, dtls.tax_amount tax_original, dtls.tax_remaining tax_remaining, dtls.amount_credited amount_credited, dtls.ar_invoice_id customer_trx_id, dtls.invoice_line_id invoice_line_id, dtls.payment_term payment_term, dtls.invoice_type invoice_type, aps.trx_number trx_number, IEX_UTILITIES.get_lookup_meaning('IEX_DELINQUENCY_STATE',DLN.status) del_status FROM (SELECT cnr.id invoice_id, khr.id chr_id, lsm.id lsm_id, cnr.consolidated_invoice_number invoice_number, cnr.currency_code invoice_currency_code, cnr.due_date due_date, cnr.amount invoice_amount, lsm.receivables_invoice_id ar_invoice_id, cnr.date_consolidated invoice_date, cnr.ibt_id bill_to_site_use_id, cnr.ixx_id customer_acct_id, cnr.org_id org_id, 'OKL-CONS-INV' invoice_type, khr.contract_number contract_number, 'IMMEDIATE' payment_term, lsm.sty_id sty_id, lsm.kle_id kle_id, lsm.amount line_amount, lsm.tax_amount tax_amount, aps.tax_remaining tax_remaining, to_number(aps.trx_number) line_identifier, nvl(lsm.amount + lsm.tax_amount, 0) amount_original, ractrl.customer_trx_line_id invoice_line_id, okl_billing_util_pvt.invoice_amount_applied(lsm.receivables_invoice_id) amount_applied, okl_billing_util_pvt.invoice_amount_credited(lsm.receivables_invoice_id) amount_credited, okl_billing_util_pvt.invoice_amount_remaining(lsm.receivables_invoice_id) amount_remaining FROM okc_k_headers_b khr, okl_cnsld_ar_hdrs_b cnr, okl_cnsld_ar_lines_b lln, okl_cnsld_ar_strms_b lsm, ar_payment_schedules_all aps, ra_customer_trx_lines_all ractrl WHERE cnr.id = lln.cnr_id AND lln.id = lsm.lln_id AND lsm.receivables_invoice_id = aps.customer_trx_id AND lsm.khr_id = khr.id AND lsm.receivables_invoice_id = ractrl.customer_trx_id AND ractrl.line_type = 'LINE') dtls, ar_payment_schedules_all aps, okl_strm_type_tl styt, fa_additions_b faa, okc_k_items cim, okc_line_styles_b lse, okc_k_lines_b cle, okl_k_headers chl, iex_delinquencies dln WHERE cle.lse_id = lse.id AND lse.lty_code = 'FIXED_ASSET' AND cle.id = cim.cle_id AND cim.jtot_object1_code = 'OKX_ASSET' AND cim.object1_id1 = faa.asset_id AND dtls.chr_id = chl.id AND chl.deal_type <> 'LOAN' AND dtls.kle_id IS NOT NULL AND dtls.ar_invoice_id = aps.customer_trx_id AND dtls.sty_id = styt.id AND styt.LANGUAGE = userenv('LANG') AND dtls.kle_id = cle.cle_id and aps.payment_schedule_id = dln.payment_schedule_id(+) UNION ALL SELECT dtls.lsm_id lsm_id, dtls.contract_number contract_number, styt.name transaction_type, dtls.invoice_number invoice_number, dtls.invoice_amount invoice_amount, dtls.line_identifier line_identifier, faa.asset_number asset_number, dtls.invoice_date invoice_date, dtls.due_date due_date, dtls.amount_original transaction_amount, dtls.amount_applied amount_applied, dtls.amount_remaining amount_remaining, dtls.customer_acct_id customer_account_id, dtls.org_id contract_authoring_org_id, dtls.chr_id khr_id, dtls.kle_id kle_id, faa.asset_id asset_id, dtls.invoice_id invoice_id, styt.id sty_id, dtls.invoice_currency_code currency_code, aps.exchange_rate_type ar_exchange_rate_type, aps.exchange_rate ar_exchange_rate, aps.exchange_date ar_exchange_date, aps.trx_date ar_trx_date, dtls.line_amount amount_line_items_original, dtls.amount_remaining amount_line_items_remaining, dtls.tax_amount tax_original, dtls.tax_remaining tax_remaining, dtls.amount_credited amount_credited, dtls.ar_invoice_id customer_trx_id, dtls.invoice_line_id invoice_line_id, dtls.payment_term payment_term, dtls.invoice_type invoice_type, aps.trx_number trx_number, IEX_UTILITIES.get_lookup_meaning('IEX_DELINQUENCY_STATE',DLN.status) del_status FROM (SELECT cust_trx.customer_trx_id invoice_id, CHR.id chr_id, tld.id lsm_id, cust_trx.trx_number invoice_number, cust_trx.invoice_currency_code invoice_currency_code, pay_sch.due_date due_date, pay_sch.amount_due_original invoice_amount, cust_trx.customer_trx_id ar_invoice_id, cust_trx.trx_date invoice_date, cust_trx.bill_to_site_use_id bill_to_site_use_id, cust_trx.bill_to_customer_id customer_acct_id, cust_trx.org_id org_id, 'AR-INVOICE' invoice_type, cust_trx_lines.interface_line_attribute6 contract_number, 'IMMEDIATE' payment_term, tld.sty_id sty_id, tld.kle_id kle_id, extended_amount line_amount, (SELECT SUM(nvl(extended_amount, 0)) FROM ra_customer_trx_lines_all b WHERE b.link_to_cust_trx_line_id = cust_trx_lines.customer_trx_line_id) tax_amount, (SELECT SUM(nvl(amount_due_remaining, 0)) FROM ra_customer_trx_lines_all b WHERE b.link_to_cust_trx_line_id = cust_trx_lines.customer_trx_line_id) tax_remaining, cust_trx_lines.line_number line_identifier, okl_billing_util_pvt.invoice_line_amount_orig(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id) amount_original, cust_trx_lines.customer_trx_line_id invoice_line_id, okl_billing_util_pvt.invoice_line_amount_applied(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id) amount_applied, okl_billing_util_pvt.invoice_line_amount_credited(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id) amount_credited, okl_billing_util_pvt.invoice_line_amount_remaining(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id) amount_remaining FROM ra_customer_trx_all cust_trx, ar_payment_schedules_all pay_sch, ra_customer_trx_lines_all cust_trx_lines, okl_txd_ar_ln_dtls_b tld, okc_k_headers_b CHR WHERE cust_trx.customer_trx_id = pay_sch.customer_trx_id AND cust_trx.customer_trx_id = cust_trx_lines.customer_trx_id AND tld.id = cust_trx_lines.interface_line_attribute14 AND cust_trx_lines.interface_line_attribute1 IS NULL AND cust_trx_lines.line_type = 'LINE' AND CHR.contract_number = cust_trx_lines.interface_line_attribute6 AND cust_trx_lines.interface_line_context = 'OKL_CONTRACTS' AND CHR.scs_code = 'LEASE') dtls, ar_payment_schedules_all aps, okl_strm_type_tl styt, fa_additions_b faa, okc_k_items cim, okc_line_styles_b lse, okc_k_lines_b cle, okl_k_headers chl, iex_delinquencies dln WHERE cle.lse_id = lse.id AND lse.lty_code = 'FIXED_ASSET' AND cle.id = cim.cle_id AND cim.jtot_object1_code = 'OKX_ASSET' AND cim.object1_id1 = faa.asset_id AND dtls.chr_id = chl.id AND chl.deal_type <> 'LOAN' AND dtls.kle_id IS NOT NULL AND dtls.ar_invoice_id = aps.customer_trx_id AND dtls.sty_id = styt.id AND styt.LANGUAGE = userenv('LANG') AND dtls.kle_id = cle.cle_id and aps.payment_schedule_id = dln.payment_schedule_id(+) UNION ALL SELECT dtls.lsm_id lsm_id, dtls.contract_number contract_number, styt.name transaction_type, dtls.invoice_number invoice_number, dtls.invoice_amount invoice_amount, dtls.line_identifier line_identifier, okl_cs_lc_contract_pvt.get_asset_number(dtls.kle_id) asset_number, dtls.invoice_date invoice_date, dtls.due_date due_date, dtls.amount_original transaction_amount, dtls.amount_applied amount_applied, dtls.amount_remaining amount_remaining, dtls.customer_acct_id customer_account_id, dtls.org_id contract_authoring_org_id, dtls.chr_id khr_id, dtls.kle_id kle_id, to_number(okl_cs_lc_contract_pvt.get_cov_asset_id(dtls.kle_id)) asset_id, dtls.invoice_id invoice_id, styt.id sty_id, dtls.invoice_currency_code currency_code, aps.exchange_rate_type ar_exchange_rate_type, aps.exchange_rate ar_exchange_rate, aps.exchange_date ar_exchange_date, aps.trx_date ar_trx_date, dtls.line_amount amount_line_items_original, dtls.amount_remaining amount_line_items_remaining, dtls.tax_amount tax_original, dtls.tax_remaining tax_remaining, dtls.amount_credited amount_credited, dtls.ar_invoice_id customer_trx_id, dtls.invoice_line_id invoice_line_id, dtls.payment_term payment_term, dtls.invoice_type invoice_type, aps.trx_number trx_number, IEX_UTILITIES.get_lookup_meaning('IEX_DELINQUENCY_STATE',DLN.status) del_status FROM ar_payment_schedules_all aps, okl_strm_type_tl styt, (SELECT cnr.id invoice_id, khr.id chr_id, lsm.id lsm_id, cnr.consolidated_invoice_number invoice_number, cnr.currency_code invoice_currency_code, cnr.due_date due_date, cnr.amount invoice_amount, lsm.receivables_invoice_id ar_invoice_id, cnr.date_consolidated invoice_date, cnr.ibt_id bill_to_site_use_id, cnr.ixx_id customer_acct_id, cnr.org_id org_id, 'OKL-CONS-INV' invoice_type, khr.contract_number contract_number, 'IMMEDIATE' payment_term, lsm.sty_id sty_id, lsm.kle_id kle_id, lsm.amount line_amount, lsm.tax_amount tax_amount, aps.tax_remaining tax_remaining, to_number(aps.trx_number) line_identifier, nvl(lsm.amount + lsm.tax_amount, 0) amount_original, ractrl.customer_trx_line_id invoice_line_id, okl_billing_util_pvt.invoice_amount_applied(lsm.receivables_invoice_id) amount_applied, okl_billing_util_pvt.invoice_amount_credited(lsm.receivables_invoice_id) amount_credited, okl_billing_util_pvt.invoice_amount_remaining(lsm.receivables_invoice_id) amount_remaining FROM okc_k_headers_b khr, okl_cnsld_ar_hdrs_b cnr, okl_cnsld_ar_lines_b lln, okl_cnsld_ar_strms_b lsm, ar_payment_schedules_all aps, ra_customer_trx_lines_all ractrl WHERE cnr.id = lln.cnr_id AND lln.id = lsm.lln_id AND lsm.receivables_invoice_id = aps.customer_trx_id AND lsm.khr_id = khr.id AND lsm.receivables_invoice_id = ractrl.customer_trx_id AND ractrl.line_type = 'LINE') dtls, iex_delinquencies dln WHERE dtls.kle_id IS NOT NULL AND dtls.ar_invoice_id = aps.customer_trx_id AND dtls.sty_id = styt.id AND styt.LANGUAGE = userenv('LANG') AND NOT EXISTS (SELECT /*+ no_unnest */ 1 FROM okc_k_lines_b cle2, okc_line_styles_b lse WHERE cle2.cle_id = dtls.kle_id AND cle2.lse_id = lse.id AND lse.lty_code = 'FIXED_ASSET') and aps.payment_schedule_id = dln.payment_schedule_id(+) UNION ALL SELECT dtls.lsm_id lsm_id, dtls.contract_number contract_number, styt.name transaction_type, dtls.invoice_number invoice_number, dtls.invoice_amount invoice_amount, dtls.line_identifier line_identifier, okl_cs_lc_contract_pvt.get_asset_number(dtls.kle_id) asset_number, dtls.invoice_date invoice_date, dtls.due_date due_date, dtls.amount_original transaction_amount, dtls.amount_applied amount_applied, dtls.amount_remaining amount_remaining, dtls.customer_acct_id customer_account_id, dtls.org_id contract_authoring_org_id, dtls.chr_id khr_id, dtls.kle_id kle_id, to_number(okl_cs_lc_contract_pvt.get_cov_asset_id(dtls.kle_id)) asset_id, dtls.invoice_id invoice_id, styt.id sty_id, dtls.invoice_currency_code currency_code, aps.exchange_rate_type ar_exchange_rate_type, aps.exchange_rate ar_exchange_rate, aps.exchange_date ar_exchange_date, aps.trx_date ar_trx_date, dtls.line_amount amount_line_items_original, dtls.amount_remaining amount_line_items_remaining, dtls.tax_amount tax_original, dtls.tax_remaining tax_remaining, dtls.amount_credited amount_credited, dtls.ar_invoice_id customer_trx_id, dtls.invoice_line_id invoice_line_id, dtls.payment_term payment_term, dtls.invoice_type invoice_type, aps.trx_number trx_number, IEX_UTILITIES.get_lookup_meaning('IEX_DELINQUENCY_STATE',DLN.status) del_status FROM ar_payment_schedules_all aps, okl_strm_type_tl styt, iex_delinquencies dln, (SELECT cust_trx.customer_trx_id invoice_id, CHR.id chr_id, tld.id lsm_id, cust_trx.trx_number invoice_number, cust_trx.invoice_currency_code invoice_currency_code, pay_sch.due_date due_date, pay_sch.amount_due_original invoice_amount, cust_trx.customer_trx_id ar_invoice_id, cust_trx.trx_date invoice_date, cust_trx.bill_to_site_use_id bill_to_site_use_id, cust_trx.bill_to_customer_id customer_acct_id, cust_trx.org_id org_id, 'AR-INVOICE' invoice_type, cust_trx_lines.interface_line_attribute6 contract_number, 'IMMEDIATE' payment_term, tld.sty_id sty_id, tld.kle_id kle_id, extended_amount line_amount, (SELECT SUM(nvl(extended_amount, 0)) FROM ra_customer_trx_lines_all b WHERE b.link_to_cust_trx_line_id = cust_trx_lines.customer_trx_line_id) tax_amount, (SELECT SUM(nvl(amount_due_remaining, 0)) FROM ra_customer_trx_lines_all b WHERE b.link_to_cust_trx_line_id = cust_trx_lines.customer_trx_line_id) tax_remaining, cust_trx_lines.line_number line_identifier, okl_billing_util_pvt.invoice_line_amount_orig(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id) amount_original, cust_trx_lines.customer_trx_line_id invoice_line_id, okl_billing_util_pvt.invoice_line_amount_applied(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id) amount_applied, okl_billing_util_pvt.invoice_line_amount_credited(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id) amount_credited, okl_billing_util_pvt.invoice_line_amount_remaining(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id) amount_remaining FROM ra_customer_trx_all cust_trx, ar_payment_schedules_all pay_sch, ra_customer_trx_lines_all cust_trx_lines, okl_txd_ar_ln_dtls_b tld, okc_k_headers_b CHR WHERE cust_trx.customer_trx_id = pay_sch.customer_trx_id AND cust_trx.customer_trx_id = cust_trx_lines.customer_trx_id AND tld.id = cust_trx_lines.interface_line_attribute14 AND cust_trx_lines.interface_line_attribute1 IS NULL AND cust_trx_lines.line_type = 'LINE' AND CHR.contract_number = cust_trx_lines.interface_line_attribute6 AND cust_trx_lines.interface_line_context = 'OKL_CONTRACTS' AND CHR.scs_code = 'LEASE') dtls WHERE dtls.kle_id IS NOT NULL AND dtls.ar_invoice_id = aps.customer_trx_id AND dtls.sty_id = styt.id AND styt.LANGUAGE = userenv('LANG') AND NOT EXISTS (SELECT /*+ no_unnest */ 1 FROM okc_k_lines_b cle2, okc_line_styles_b lse WHERE cle2.cle_id = dtls.kle_id AND cle2.lse_id = lse.id AND lse.lty_code = 'FIXED_ASSET') and aps.payment_schedule_id = dln.payment_schedule_id(+) UNION ALL SELECT dtls.lsm_id lsm_id, dtls.contract_number contract_number, styt.name transaction_type, dtls.invoice_number invoice_number, dtls.invoice_amount invoice_amount, dtls.line_identifier line_identifier, NULL asset_number, dtls.invoice_date invoice_date, dtls.due_date due_date, dtls.amount_original transaction_amount, dtls.amount_applied amount_applied, dtls.amount_remaining amount_remaining, dtls.customer_acct_id customer_account_id, dtls.org_id contract_authoring_org_id, dtls.chr_id khr_id, to_number(NULL) kle_id, to_number(NULL) asset_id, dtls.invoice_id invoice_id, styt.id sty_id, dtls.invoice_currency_code currency_code, aps.exchange_rate_type ar_exchange_rate_type, aps.exchange_rate ar_exchange_rate, aps.exchange_date ar_exchange_date, aps.trx_date ar_trx_date, dtls.line_amount amount_line_items_original, dtls.amount_remaining amount_line_items_remaining, dtls.tax_amount tax_original, dtls.tax_remaining tax_remaining, dtls.amount_credited amount_credited, dtls.ar_invoice_id customer_trx_id, dtls.invoice_line_id invoice_line_id, dtls.payment_term payment_term, dtls.invoice_type invoice_type, aps.trx_number trx_number, IEX_UTILITIES.get_lookup_meaning('IEX_DELINQUENCY_STATE',DLN.status) del_status FROM (SELECT cnr.id invoice_id, khr.id chr_id, lsm.id lsm_id, cnr.consolidated_invoice_number invoice_number, cnr.currency_code invoice_currency_code, cnr.due_date due_date, cnr.amount invoice_amount, lsm.receivables_invoice_id ar_invoice_id, cnr.date_consolidated invoice_date, cnr.ibt_id bill_to_site_use_id, cnr.ixx_id customer_acct_id, cnr.org_id org_id, 'OKL-CONS-INV' invoice_type, khr.contract_number contract_number, 'IMMEDIATE' payment_term, lsm.sty_id sty_id, lsm.kle_id kle_id, lsm.amount line_amount, lsm.tax_amount tax_amount, aps.tax_remaining tax_remaining, to_number(aps.trx_number) line_identifier, nvl(lsm.amount + lsm.tax_amount, 0) amount_original, ractrl.customer_trx_line_id invoice_line_id, okl_billing_util_pvt.invoice_amount_applied(lsm.receivables_invoice_id) amount_applied, okl_billing_util_pvt.invoice_amount_credited(lsm.receivables_invoice_id) amount_credited, okl_billing_util_pvt.invoice_amount_remaining(lsm.receivables_invoice_id) amount_remaining FROM okc_k_headers_b khr, okl_cnsld_ar_hdrs_b cnr, okl_cnsld_ar_lines_b lln, okl_cnsld_ar_strms_b lsm, ar_payment_schedules_all aps, ra_customer_trx_lines_all ractrl WHERE cnr.id = lln.cnr_id AND lln.id = lsm.lln_id AND lsm.receivables_invoice_id = aps.customer_trx_id AND lsm.khr_id = khr.id AND lsm.receivables_invoice_id = ractrl.customer_trx_id AND ractrl.line_type = 'LINE') dtls, ar_payment_schedules_all aps, okl_strm_type_tl styt, iex_delinquencies dln WHERE dtls.kle_id IS NULL AND dtls.ar_invoice_id = aps.customer_trx_id AND dtls.sty_id = styt.id AND styt.LANGUAGE = userenv('LANG') and aps.payment_schedule_id = dln.payment_schedule_id(+) UNION ALL SELECT dtls.lsm_id lsm_id, dtls.contract_number contract_number, styt.name transaction_type, dtls.invoice_number invoice_number, dtls.invoice_amount invoice_amount, dtls.line_identifier line_identifier, NULL asset_number, dtls.invoice_date invoice_date, dtls.due_date due_date, dtls.amount_original transaction_amount, dtls.amount_applied amount_applied, dtls.amount_remaining amount_remaining, dtls.customer_acct_id customer_account_id, dtls.org_id contract_authoring_org_id, dtls.chr_id khr_id, to_number(NULL) kle_id, to_number(NULL) asset_id, dtls.invoice_id invoice_id, styt.id sty_id, dtls.invoice_currency_code currency_code, aps.exchange_rate_type ar_exchange_rate_type, aps.exchange_rate ar_exchange_rate, aps.exchange_date ar_exchange_date, aps.trx_date ar_trx_date, dtls.line_amount amount_line_items_original, dtls.amount_remaining amount_line_items_remaining, dtls.tax_amount tax_original, dtls.tax_remaining tax_remaining, dtls.amount_credited amount_credited, dtls.ar_invoice_id customer_trx_id, dtls.invoice_line_id invoice_line_id, dtls.payment_term payment_term, dtls.invoice_type invoice_type, aps.trx_number trx_number, IEX_UTILITIES.get_lookup_meaning('IEX_DELINQUENCY_STATE',DLN.status) del_status FROM (SELECT cust_trx.customer_trx_id invoice_id, CHR.id chr_id, tld.id lsm_id, cust_trx.trx_number invoice_number, cust_trx.invoice_currency_code invoice_currency_code, pay_sch.due_date due_date, pay_sch.amount_due_original invoice_amount, cust_trx.customer_trx_id ar_invoice_id, cust_trx.trx_date invoice_date, cust_trx.bill_to_site_use_id bill_to_site_use_id, cust_trx.bill_to_customer_id customer_acct_id, cust_trx.org_id org_id, 'AR-INVOICE' invoice_type, cust_trx_lines.interface_line_attribute6 contract_number, 'IMMEDIATE' payment_term, tld.sty_id sty_id, tld.kle_id kle_id, extended_amount line_amount, (SELECT SUM(nvl(extended_amount, 0)) FROM ra_customer_trx_lines_all b WHERE b.link_to_cust_trx_line_id = cust_trx_lines.customer_trx_line_id) tax_amount, (SELECT SUM(nvl(amount_due_remaining, 0)) FROM ra_customer_trx_lines_all b WHERE b.link_to_cust_trx_line_id = cust_trx_lines.customer_trx_line_id) tax_remaining, cust_trx_lines.line_number line_identifier, okl_billing_util_pvt.invoice_line_amount_orig(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id) amount_original, cust_trx_lines.customer_trx_line_id invoice_line_id, okl_billing_util_pvt.invoice_line_amount_applied(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id) amount_applied, okl_billing_util_pvt.invoice_line_amount_credited(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id) amount_credited, okl_billing_util_pvt.invoice_line_amount_remaining(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id) amount_remaining FROM ra_customer_trx_all cust_trx, ar_payment_schedules_all pay_sch, ra_customer_trx_lines_all cust_trx_lines, okl_txd_ar_ln_dtls_b tld, okc_k_headers_b CHR WHERE cust_trx.customer_trx_id = pay_sch.customer_trx_id AND cust_trx.customer_trx_id = cust_trx_lines.customer_trx_id AND tld.id = cust_trx_lines.interface_line_attribute14 AND cust_trx_lines.interface_line_attribute1 IS NULL AND cust_trx_lines.line_type = 'LINE' AND CHR.contract_number = cust_trx_lines.interface_line_attribute6 AND cust_trx_lines.interface_line_context = 'OKL_CONTRACTS' AND CHR.scs_code = 'LEASE') dtls, ar_payment_schedules_all aps, okl_strm_type_tl styt, iex_delinquencies dln WHERE dtls.kle_id IS NULL AND dtls.ar_invoice_id = aps.customer_trx_id AND dtls.sty_id = styt.id AND styt.LANGUAGE = userenv('LANG') and aps.payment_schedule_id = dln.payment_schedule_id(+) UNION ALL SELECT dtls.lsm_id lsm_id, dtls.contract_number contract_number, styt.name transaction_type, dtls.invoice_number invoice_number, dtls.invoice_amount invoice_amount, dtls.line_identifier line_identifier, clt.name asset_number, dtls.invoice_date invoice_date, dtls.due_date due_date, dtls.amount_original transaction_amount, dtls.amount_applied amount_applied, dtls.amount_remaining amount_remaining, dtls.customer_acct_id customer_account_id, dtls.org_id contract_authoring_org_id, dtls.chr_id khr_id, dtls.kle_id kle_id, to_number(NULL) asset_id, dtls.invoice_id invoice_id, styt.id sty_id, dtls.invoice_currency_code currency_code, aps.exchange_rate_type ar_exchange_rate_type, aps.exchange_rate ar_exchange_rate, aps.exchange_date ar_exchange_date, aps.trx_date ar_trx_date, dtls.line_amount amount_line_items_original, dtls.amount_remaining amount_line_items_remaining, dtls.tax_amount tax_original, dtls.tax_remaining tax_remaining, dtls.amount_credited amount_credited, dtls.ar_invoice_id customer_trx_id, dtls.invoice_line_id invoice_line_id, dtls.payment_term payment_term, dtls.invoice_type invoice_type, aps.trx_number trx_number, IEX_UTILITIES.get_lookup_meaning('IEX_DELINQUENCY_STATE',DLN.status) del_status FROM (SELECT cnr.id invoice_id, khr.id chr_id, lsm.id lsm_id, cnr.consolidated_invoice_number invoice_number, cnr.currency_code invoice_currency_code, cnr.due_date due_date, cnr.amount invoice_amount, lsm.receivables_invoice_id ar_invoice_id, cnr.date_consolidated invoice_date, cnr.ibt_id bill_to_site_use_id, cnr.ixx_id customer_acct_id, cnr.org_id org_id, 'OKL-CONS-INV' invoice_type, khr.contract_number contract_number, 'IMMEDIATE' payment_term, lsm.sty_id sty_id, lsm.kle_id kle_id, lsm.amount line_amount, lsm.tax_amount tax_amount, aps.tax_remaining tax_remaining, to_number(aps.trx_number) line_identifier, nvl(lsm.amount + lsm.tax_amount, 0) amount_original, ractrl.customer_trx_line_id invoice_line_id, okl_billing_util_pvt.invoice_amount_applied(lsm.receivables_invoice_id) amount_applied, okl_billing_util_pvt.invoice_amount_credited(lsm.receivables_invoice_id) amount_credited, okl_billing_util_pvt.invoice_amount_remaining(lsm.receivables_invoice_id) amount_remaining FROM okc_k_headers_b khr, okl_cnsld_ar_hdrs_b cnr, okl_cnsld_ar_lines_b lln, okl_cnsld_ar_strms_b lsm, ar_payment_schedules_all aps, ra_customer_trx_lines_all ractrl WHERE cnr.id = lln.cnr_id AND lln.id = lsm.lln_id AND lsm.receivables_invoice_id = aps.customer_trx_id AND lsm.khr_id = khr.id AND lsm.receivables_invoice_id = ractrl.customer_trx_id AND ractrl.line_type = 'LINE') dtls, ar_payment_schedules_all aps, okl_strm_type_tl styt, okl_k_headers chl, okc_k_lines_b cle, okc_line_styles_b lse, okc_k_lines_tl clt, iex_delinquencies dln WHERE dtls.kle_id IS NOT NULL AND dtls.ar_invoice_id = aps.customer_trx_id AND dtls.sty_id = styt.id AND styt.LANGUAGE = userenv('LANG') AND dtls.chr_id = chl.id AND chl.deal_type = 'LOAN' AND dtls.kle_id = cle.id AND cle.lse_id = lse.id AND lse.lty_code = 'FREE_FORM1' AND cle.id = clt.id AND clt.LANGUAGE = userenv('LANG') and aps.payment_schedule_id = dln.payment_schedule_id(+) UNION ALL SELECT dtls.lsm_id lsm_id, dtls.contract_number contract_number, styt.name transaction_type, dtls.invoice_number invoice_number, dtls.invoice_amount invoice_amount, dtls.line_identifier line_identifier, clt.name asset_number, dtls.invoice_date invoice_date, dtls.due_date due_date, dtls.amount_original transaction_amount, dtls.amount_applied amount_applied, dtls.amount_remaining amount_remaining, dtls.customer_acct_id customer_account_id, dtls.org_id contract_authoring_org_id, dtls.chr_id khr_id, dtls.kle_id kle_id, to_number(NULL) asset_id, dtls.invoice_id invoice_id, styt.id sty_id, dtls.invoice_currency_code currency_code, aps.exchange_rate_type ar_exchange_rate_type, aps.exchange_rate ar_exchange_rate, aps.exchange_date ar_exchange_date, aps.trx_date ar_trx_date, dtls.line_amount amount_line_items_original, dtls.amount_remaining amount_line_items_remaining, dtls.tax_amount tax_original, dtls.tax_remaining tax_remaining, dtls.amount_credited amount_credited, dtls.ar_invoice_id customer_trx_id, dtls.invoice_line_id invoice_line_id, dtls.payment_term payment_term, dtls.invoice_type invoice_type, aps.trx_number trx_number, IEX_UTILITIES.get_lookup_meaning('IEX_DELINQUENCY_STATE',DLN.status) del_status FROM (SELECT cust_trx.customer_trx_id invoice_id, CHR.id chr_id, tld.id lsm_id, cust_trx.trx_number invoice_number, cust_trx.invoice_currency_code invoice_currency_code, pay_sch.due_date due_date, pay_sch.amount_due_original invoice_amount, cust_trx.customer_trx_id ar_invoice_id, cust_trx.trx_date invoice_date, cust_trx.bill_to_site_use_id bill_to_site_use_id, cust_trx.bill_to_customer_id customer_acct_id, cust_trx.org_id org_id, 'AR-INVOICE' invoice_type, cust_trx_lines.interface_line_attribute6 contract_number, 'IMMEDIATE' payment_term, tld.sty_id sty_id, tld.kle_id kle_id, extended_amount line_amount, (SELECT SUM(nvl(extended_amount, 0)) FROM ra_customer_trx_lines_all b WHERE b.link_to_cust_trx_line_id = cust_trx_lines.customer_trx_line_id) tax_amount, (SELECT SUM(nvl(amount_due_remaining, 0)) FROM ra_customer_trx_lines_all b WHERE b.link_to_cust_trx_line_id = cust_trx_lines.customer_trx_line_id) tax_remaining, cust_trx_lines.line_number line_identifier, okl_billing_util_pvt.invoice_line_amount_orig(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id) amount_original, cust_trx_lines.customer_trx_line_id invoice_line_id, okl_billing_util_pvt.invoice_line_amount_applied(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id) amount_applied, okl_billing_util_pvt.invoice_line_amount_credited(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id) amount_credited, okl_billing_util_pvt.invoice_line_amount_remaining(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id) amount_remaining FROM ra_customer_trx_all cust_trx, ar_payment_schedules_all pay_sch, ra_customer_trx_lines_all cust_trx_lines, okl_txd_ar_ln_dtls_b tld, okc_k_headers_b CHR WHERE cust_trx.customer_trx_id = pay_sch.customer_trx_id AND cust_trx.customer_trx_id = cust_trx_lines.customer_trx_id AND tld.id = cust_trx_lines.interface_line_attribute14 AND cust_trx_lines.interface_line_attribute1 IS NULL AND cust_trx_lines.line_type = 'LINE' AND CHR.contract_number = cust_trx_lines.interface_line_attribute6 AND cust_trx_lines.interface_line_context = 'OKL_CONTRACTS' AND CHR.scs_code = 'LEASE') dtls, ar_payment_schedules_all aps, okl_strm_type_tl styt, okl_k_headers chl, okc_k_lines_b cle, okc_line_styles_b lse, okc_k_lines_tl clt, iex_delinquencies dln WHERE dtls.kle_id IS NOT NULL AND dtls.ar_invoice_id = aps.customer_trx_id AND dtls.sty_id = styt.id AND styt.LANGUAGE = userenv('LANG') AND dtls.chr_id = chl.id AND chl.deal_type = 'LOAN' AND dtls.kle_id = cle.id AND cle.lse_id = lse.id AND lse.lty_code = 'FREE_FORM1' AND cle.id = clt.id AND clt.LANGUAGE = userenv('LANG') and aps.payment_schedule_id = dln.payment_schedule_id(+)

View Text - HTML Formatted

SELECT DTLS.LSM_ID LSM_ID
, DTLS.CONTRACT_NUMBER CONTRACT_NUMBER
, STYT.NAME TRANSACTION_TYPE
, DTLS.INVOICE_NUMBER INVOICE_NUMBER
, DTLS.INVOICE_AMOUNT INVOICE_AMOUNT
, DTLS.LINE_IDENTIFIER LINE_IDENTIFIER
, FAA.ASSET_NUMBER ASSET_NUMBER
, DTLS.INVOICE_DATE INVOICE_DATE
, DTLS.DUE_DATE DUE_DATE
, DTLS.AMOUNT_ORIGINAL TRANSACTION_AMOUNT
, DTLS.AMOUNT_APPLIED AMOUNT_APPLIED
, DTLS.AMOUNT_REMAINING AMOUNT_REMAINING
, DTLS.CUSTOMER_ACCT_ID CUSTOMER_ACCOUNT_ID
, DTLS.ORG_ID CONTRACT_AUTHORING_ORG_ID
, DTLS.CHR_ID KHR_ID
, DTLS.KLE_ID KLE_ID
, FAA.ASSET_ID ASSET_ID
, DTLS.INVOICE_ID INVOICE_ID
, STYT.ID STY_ID
, DTLS.INVOICE_CURRENCY_CODE CURRENCY_CODE
, APS.EXCHANGE_RATE_TYPE AR_EXCHANGE_RATE_TYPE
, APS.EXCHANGE_RATE AR_EXCHANGE_RATE
, APS.EXCHANGE_DATE AR_EXCHANGE_DATE
, APS.TRX_DATE AR_TRX_DATE
, DTLS.LINE_AMOUNT AMOUNT_LINE_ITEMS_ORIGINAL
, DTLS.AMOUNT_REMAINING AMOUNT_LINE_ITEMS_REMAINING
, DTLS.TAX_AMOUNT TAX_ORIGINAL
, DTLS.TAX_REMAINING TAX_REMAINING
, DTLS.AMOUNT_CREDITED AMOUNT_CREDITED
, DTLS.AR_INVOICE_ID CUSTOMER_TRX_ID
, DTLS.INVOICE_LINE_ID INVOICE_LINE_ID
, DTLS.PAYMENT_TERM PAYMENT_TERM
, DTLS.INVOICE_TYPE INVOICE_TYPE
, APS.TRX_NUMBER TRX_NUMBER
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DELINQUENCY_STATE'
, DLN.STATUS) DEL_STATUS
FROM (SELECT CNR.ID INVOICE_ID
, KHR.ID CHR_ID
, LSM.ID LSM_ID
, CNR.CONSOLIDATED_INVOICE_NUMBER INVOICE_NUMBER
, CNR.CURRENCY_CODE INVOICE_CURRENCY_CODE
, CNR.DUE_DATE DUE_DATE
, CNR.AMOUNT INVOICE_AMOUNT
, LSM.RECEIVABLES_INVOICE_ID AR_INVOICE_ID
, CNR.DATE_CONSOLIDATED INVOICE_DATE
, CNR.IBT_ID BILL_TO_SITE_USE_ID
, CNR.IXX_ID CUSTOMER_ACCT_ID
, CNR.ORG_ID ORG_ID
, 'OKL-CONS-INV' INVOICE_TYPE
, KHR.CONTRACT_NUMBER CONTRACT_NUMBER
, 'IMMEDIATE' PAYMENT_TERM
, LSM.STY_ID STY_ID
, LSM.KLE_ID KLE_ID
, LSM.AMOUNT LINE_AMOUNT
, LSM.TAX_AMOUNT TAX_AMOUNT
, APS.TAX_REMAINING TAX_REMAINING
, TO_NUMBER(APS.TRX_NUMBER) LINE_IDENTIFIER
, NVL(LSM.AMOUNT + LSM.TAX_AMOUNT
, 0) AMOUNT_ORIGINAL
, RACTRL.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_APPLIED(LSM.RECEIVABLES_INVOICE_ID) AMOUNT_APPLIED
, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_CREDITED(LSM.RECEIVABLES_INVOICE_ID) AMOUNT_CREDITED
, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_REMAINING(LSM.RECEIVABLES_INVOICE_ID) AMOUNT_REMAINING
FROM OKC_K_HEADERS_B KHR
, OKL_CNSLD_AR_HDRS_B CNR
, OKL_CNSLD_AR_LINES_B LLN
, OKL_CNSLD_AR_STRMS_B LSM
, AR_PAYMENT_SCHEDULES_ALL APS
, RA_CUSTOMER_TRX_LINES_ALL RACTRL
WHERE CNR.ID = LLN.CNR_ID
AND LLN.ID = LSM.LLN_ID
AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND LSM.KHR_ID = KHR.ID
AND LSM.RECEIVABLES_INVOICE_ID = RACTRL.CUSTOMER_TRX_ID
AND RACTRL.LINE_TYPE = 'LINE') DTLS
, AR_PAYMENT_SCHEDULES_ALL APS
, OKL_STRM_TYPE_TL STYT
, FA_ADDITIONS_B FAA
, OKC_K_ITEMS CIM
, OKC_LINE_STYLES_B LSE
, OKC_K_LINES_B CLE
, OKL_K_HEADERS CHL
, IEX_DELINQUENCIES DLN
WHERE CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET'
AND CLE.ID = CIM.CLE_ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND CIM.OBJECT1_ID1 = FAA.ASSET_ID
AND DTLS.CHR_ID = CHL.ID
AND CHL.DEAL_TYPE <> 'LOAN'
AND DTLS.KLE_ID IS NOT NULL
AND DTLS.AR_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND DTLS.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG')
AND DTLS.KLE_ID = CLE.CLE_ID
AND APS.PAYMENT_SCHEDULE_ID = DLN.PAYMENT_SCHEDULE_ID(+) UNION ALL SELECT DTLS.LSM_ID LSM_ID
, DTLS.CONTRACT_NUMBER CONTRACT_NUMBER
, STYT.NAME TRANSACTION_TYPE
, DTLS.INVOICE_NUMBER INVOICE_NUMBER
, DTLS.INVOICE_AMOUNT INVOICE_AMOUNT
, DTLS.LINE_IDENTIFIER LINE_IDENTIFIER
, FAA.ASSET_NUMBER ASSET_NUMBER
, DTLS.INVOICE_DATE INVOICE_DATE
, DTLS.DUE_DATE DUE_DATE
, DTLS.AMOUNT_ORIGINAL TRANSACTION_AMOUNT
, DTLS.AMOUNT_APPLIED AMOUNT_APPLIED
, DTLS.AMOUNT_REMAINING AMOUNT_REMAINING
, DTLS.CUSTOMER_ACCT_ID CUSTOMER_ACCOUNT_ID
, DTLS.ORG_ID CONTRACT_AUTHORING_ORG_ID
, DTLS.CHR_ID KHR_ID
, DTLS.KLE_ID KLE_ID
, FAA.ASSET_ID ASSET_ID
, DTLS.INVOICE_ID INVOICE_ID
, STYT.ID STY_ID
, DTLS.INVOICE_CURRENCY_CODE CURRENCY_CODE
, APS.EXCHANGE_RATE_TYPE AR_EXCHANGE_RATE_TYPE
, APS.EXCHANGE_RATE AR_EXCHANGE_RATE
, APS.EXCHANGE_DATE AR_EXCHANGE_DATE
, APS.TRX_DATE AR_TRX_DATE
, DTLS.LINE_AMOUNT AMOUNT_LINE_ITEMS_ORIGINAL
, DTLS.AMOUNT_REMAINING AMOUNT_LINE_ITEMS_REMAINING
, DTLS.TAX_AMOUNT TAX_ORIGINAL
, DTLS.TAX_REMAINING TAX_REMAINING
, DTLS.AMOUNT_CREDITED AMOUNT_CREDITED
, DTLS.AR_INVOICE_ID CUSTOMER_TRX_ID
, DTLS.INVOICE_LINE_ID INVOICE_LINE_ID
, DTLS.PAYMENT_TERM PAYMENT_TERM
, DTLS.INVOICE_TYPE INVOICE_TYPE
, APS.TRX_NUMBER TRX_NUMBER
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DELINQUENCY_STATE'
, DLN.STATUS) DEL_STATUS
FROM (SELECT CUST_TRX.CUSTOMER_TRX_ID INVOICE_ID
, CHR.ID CHR_ID
, TLD.ID LSM_ID
, CUST_TRX.TRX_NUMBER INVOICE_NUMBER
, CUST_TRX.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, PAY_SCH.DUE_DATE DUE_DATE
, PAY_SCH.AMOUNT_DUE_ORIGINAL INVOICE_AMOUNT
, CUST_TRX.CUSTOMER_TRX_ID AR_INVOICE_ID
, CUST_TRX.TRX_DATE INVOICE_DATE
, CUST_TRX.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, CUST_TRX.BILL_TO_CUSTOMER_ID CUSTOMER_ACCT_ID
, CUST_TRX.ORG_ID ORG_ID
, 'AR-INVOICE' INVOICE_TYPE
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE6 CONTRACT_NUMBER
, 'IMMEDIATE' PAYMENT_TERM
, TLD.STY_ID STY_ID
, TLD.KLE_ID KLE_ID
, EXTENDED_AMOUNT LINE_AMOUNT
, (SELECT SUM(NVL(EXTENDED_AMOUNT
, 0))
FROM RA_CUSTOMER_TRX_LINES_ALL B
WHERE B.LINK_TO_CUST_TRX_LINE_ID = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) TAX_AMOUNT
, (SELECT SUM(NVL(AMOUNT_DUE_REMAINING
, 0))
FROM RA_CUSTOMER_TRX_LINES_ALL B
WHERE B.LINK_TO_CUST_TRX_LINE_ID = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) TAX_REMAINING
, CUST_TRX_LINES.LINE_NUMBER LINE_IDENTIFIER
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_ORIG(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_ORIGINAL
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_APPLIED(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_APPLIED
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_CREDITED(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_CREDITED
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_REMAINING(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_REMAINING
FROM RA_CUSTOMER_TRX_ALL CUST_TRX
, AR_PAYMENT_SCHEDULES_ALL PAY_SCH
, RA_CUSTOMER_TRX_LINES_ALL CUST_TRX_LINES
, OKL_TXD_AR_LN_DTLS_B TLD
, OKC_K_HEADERS_B CHR
WHERE CUST_TRX.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
AND CUST_TRX.CUSTOMER_TRX_ID = CUST_TRX_LINES.CUSTOMER_TRX_ID
AND TLD.ID = CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE14
AND CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE1 IS NULL
AND CUST_TRX_LINES.LINE_TYPE = 'LINE'
AND CHR.CONTRACT_NUMBER = CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE6
AND CUST_TRX_LINES.INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS'
AND CHR.SCS_CODE = 'LEASE') DTLS
, AR_PAYMENT_SCHEDULES_ALL APS
, OKL_STRM_TYPE_TL STYT
, FA_ADDITIONS_B FAA
, OKC_K_ITEMS CIM
, OKC_LINE_STYLES_B LSE
, OKC_K_LINES_B CLE
, OKL_K_HEADERS CHL
, IEX_DELINQUENCIES DLN
WHERE CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET'
AND CLE.ID = CIM.CLE_ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND CIM.OBJECT1_ID1 = FAA.ASSET_ID
AND DTLS.CHR_ID = CHL.ID
AND CHL.DEAL_TYPE <> 'LOAN'
AND DTLS.KLE_ID IS NOT NULL
AND DTLS.AR_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND DTLS.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG')
AND DTLS.KLE_ID = CLE.CLE_ID
AND APS.PAYMENT_SCHEDULE_ID = DLN.PAYMENT_SCHEDULE_ID(+) UNION ALL SELECT DTLS.LSM_ID LSM_ID
, DTLS.CONTRACT_NUMBER CONTRACT_NUMBER
, STYT.NAME TRANSACTION_TYPE
, DTLS.INVOICE_NUMBER INVOICE_NUMBER
, DTLS.INVOICE_AMOUNT INVOICE_AMOUNT
, DTLS.LINE_IDENTIFIER LINE_IDENTIFIER
, OKL_CS_LC_CONTRACT_PVT.GET_ASSET_NUMBER(DTLS.KLE_ID) ASSET_NUMBER
, DTLS.INVOICE_DATE INVOICE_DATE
, DTLS.DUE_DATE DUE_DATE
, DTLS.AMOUNT_ORIGINAL TRANSACTION_AMOUNT
, DTLS.AMOUNT_APPLIED AMOUNT_APPLIED
, DTLS.AMOUNT_REMAINING AMOUNT_REMAINING
, DTLS.CUSTOMER_ACCT_ID CUSTOMER_ACCOUNT_ID
, DTLS.ORG_ID CONTRACT_AUTHORING_ORG_ID
, DTLS.CHR_ID KHR_ID
, DTLS.KLE_ID KLE_ID
, TO_NUMBER(OKL_CS_LC_CONTRACT_PVT.GET_COV_ASSET_ID(DTLS.KLE_ID)) ASSET_ID
, DTLS.INVOICE_ID INVOICE_ID
, STYT.ID STY_ID
, DTLS.INVOICE_CURRENCY_CODE CURRENCY_CODE
, APS.EXCHANGE_RATE_TYPE AR_EXCHANGE_RATE_TYPE
, APS.EXCHANGE_RATE AR_EXCHANGE_RATE
, APS.EXCHANGE_DATE AR_EXCHANGE_DATE
, APS.TRX_DATE AR_TRX_DATE
, DTLS.LINE_AMOUNT AMOUNT_LINE_ITEMS_ORIGINAL
, DTLS.AMOUNT_REMAINING AMOUNT_LINE_ITEMS_REMAINING
, DTLS.TAX_AMOUNT TAX_ORIGINAL
, DTLS.TAX_REMAINING TAX_REMAINING
, DTLS.AMOUNT_CREDITED AMOUNT_CREDITED
, DTLS.AR_INVOICE_ID CUSTOMER_TRX_ID
, DTLS.INVOICE_LINE_ID INVOICE_LINE_ID
, DTLS.PAYMENT_TERM PAYMENT_TERM
, DTLS.INVOICE_TYPE INVOICE_TYPE
, APS.TRX_NUMBER TRX_NUMBER
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DELINQUENCY_STATE'
, DLN.STATUS) DEL_STATUS
FROM AR_PAYMENT_SCHEDULES_ALL APS
, OKL_STRM_TYPE_TL STYT
, (SELECT CNR.ID INVOICE_ID
, KHR.ID CHR_ID
, LSM.ID LSM_ID
, CNR.CONSOLIDATED_INVOICE_NUMBER INVOICE_NUMBER
, CNR.CURRENCY_CODE INVOICE_CURRENCY_CODE
, CNR.DUE_DATE DUE_DATE
, CNR.AMOUNT INVOICE_AMOUNT
, LSM.RECEIVABLES_INVOICE_ID AR_INVOICE_ID
, CNR.DATE_CONSOLIDATED INVOICE_DATE
, CNR.IBT_ID BILL_TO_SITE_USE_ID
, CNR.IXX_ID CUSTOMER_ACCT_ID
, CNR.ORG_ID ORG_ID
, 'OKL-CONS-INV' INVOICE_TYPE
, KHR.CONTRACT_NUMBER CONTRACT_NUMBER
, 'IMMEDIATE' PAYMENT_TERM
, LSM.STY_ID STY_ID
, LSM.KLE_ID KLE_ID
, LSM.AMOUNT LINE_AMOUNT
, LSM.TAX_AMOUNT TAX_AMOUNT
, APS.TAX_REMAINING TAX_REMAINING
, TO_NUMBER(APS.TRX_NUMBER) LINE_IDENTIFIER
, NVL(LSM.AMOUNT + LSM.TAX_AMOUNT
, 0) AMOUNT_ORIGINAL
, RACTRL.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_APPLIED(LSM.RECEIVABLES_INVOICE_ID) AMOUNT_APPLIED
, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_CREDITED(LSM.RECEIVABLES_INVOICE_ID) AMOUNT_CREDITED
, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_REMAINING(LSM.RECEIVABLES_INVOICE_ID) AMOUNT_REMAINING
FROM OKC_K_HEADERS_B KHR
, OKL_CNSLD_AR_HDRS_B CNR
, OKL_CNSLD_AR_LINES_B LLN
, OKL_CNSLD_AR_STRMS_B LSM
, AR_PAYMENT_SCHEDULES_ALL APS
, RA_CUSTOMER_TRX_LINES_ALL RACTRL
WHERE CNR.ID = LLN.CNR_ID
AND LLN.ID = LSM.LLN_ID
AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND LSM.KHR_ID = KHR.ID
AND LSM.RECEIVABLES_INVOICE_ID = RACTRL.CUSTOMER_TRX_ID
AND RACTRL.LINE_TYPE = 'LINE') DTLS
, IEX_DELINQUENCIES DLN
WHERE DTLS.KLE_ID IS NOT NULL
AND DTLS.AR_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND DTLS.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG')
AND NOT EXISTS (SELECT /*+ NO_UNNEST */ 1
FROM OKC_K_LINES_B CLE2
, OKC_LINE_STYLES_B LSE
WHERE CLE2.CLE_ID = DTLS.KLE_ID
AND CLE2.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET')
AND APS.PAYMENT_SCHEDULE_ID = DLN.PAYMENT_SCHEDULE_ID(+) UNION ALL SELECT DTLS.LSM_ID LSM_ID
, DTLS.CONTRACT_NUMBER CONTRACT_NUMBER
, STYT.NAME TRANSACTION_TYPE
, DTLS.INVOICE_NUMBER INVOICE_NUMBER
, DTLS.INVOICE_AMOUNT INVOICE_AMOUNT
, DTLS.LINE_IDENTIFIER LINE_IDENTIFIER
, OKL_CS_LC_CONTRACT_PVT.GET_ASSET_NUMBER(DTLS.KLE_ID) ASSET_NUMBER
, DTLS.INVOICE_DATE INVOICE_DATE
, DTLS.DUE_DATE DUE_DATE
, DTLS.AMOUNT_ORIGINAL TRANSACTION_AMOUNT
, DTLS.AMOUNT_APPLIED AMOUNT_APPLIED
, DTLS.AMOUNT_REMAINING AMOUNT_REMAINING
, DTLS.CUSTOMER_ACCT_ID CUSTOMER_ACCOUNT_ID
, DTLS.ORG_ID CONTRACT_AUTHORING_ORG_ID
, DTLS.CHR_ID KHR_ID
, DTLS.KLE_ID KLE_ID
, TO_NUMBER(OKL_CS_LC_CONTRACT_PVT.GET_COV_ASSET_ID(DTLS.KLE_ID)) ASSET_ID
, DTLS.INVOICE_ID INVOICE_ID
, STYT.ID STY_ID
, DTLS.INVOICE_CURRENCY_CODE CURRENCY_CODE
, APS.EXCHANGE_RATE_TYPE AR_EXCHANGE_RATE_TYPE
, APS.EXCHANGE_RATE AR_EXCHANGE_RATE
, APS.EXCHANGE_DATE AR_EXCHANGE_DATE
, APS.TRX_DATE AR_TRX_DATE
, DTLS.LINE_AMOUNT AMOUNT_LINE_ITEMS_ORIGINAL
, DTLS.AMOUNT_REMAINING AMOUNT_LINE_ITEMS_REMAINING
, DTLS.TAX_AMOUNT TAX_ORIGINAL
, DTLS.TAX_REMAINING TAX_REMAINING
, DTLS.AMOUNT_CREDITED AMOUNT_CREDITED
, DTLS.AR_INVOICE_ID CUSTOMER_TRX_ID
, DTLS.INVOICE_LINE_ID INVOICE_LINE_ID
, DTLS.PAYMENT_TERM PAYMENT_TERM
, DTLS.INVOICE_TYPE INVOICE_TYPE
, APS.TRX_NUMBER TRX_NUMBER
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DELINQUENCY_STATE'
, DLN.STATUS) DEL_STATUS
FROM AR_PAYMENT_SCHEDULES_ALL APS
, OKL_STRM_TYPE_TL STYT
, IEX_DELINQUENCIES DLN
, (SELECT CUST_TRX.CUSTOMER_TRX_ID INVOICE_ID
, CHR.ID CHR_ID
, TLD.ID LSM_ID
, CUST_TRX.TRX_NUMBER INVOICE_NUMBER
, CUST_TRX.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, PAY_SCH.DUE_DATE DUE_DATE
, PAY_SCH.AMOUNT_DUE_ORIGINAL INVOICE_AMOUNT
, CUST_TRX.CUSTOMER_TRX_ID AR_INVOICE_ID
, CUST_TRX.TRX_DATE INVOICE_DATE
, CUST_TRX.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, CUST_TRX.BILL_TO_CUSTOMER_ID CUSTOMER_ACCT_ID
, CUST_TRX.ORG_ID ORG_ID
, 'AR-INVOICE' INVOICE_TYPE
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE6 CONTRACT_NUMBER
, 'IMMEDIATE' PAYMENT_TERM
, TLD.STY_ID STY_ID
, TLD.KLE_ID KLE_ID
, EXTENDED_AMOUNT LINE_AMOUNT
, (SELECT SUM(NVL(EXTENDED_AMOUNT
, 0))
FROM RA_CUSTOMER_TRX_LINES_ALL B
WHERE B.LINK_TO_CUST_TRX_LINE_ID = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) TAX_AMOUNT
, (SELECT SUM(NVL(AMOUNT_DUE_REMAINING
, 0))
FROM RA_CUSTOMER_TRX_LINES_ALL B
WHERE B.LINK_TO_CUST_TRX_LINE_ID = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) TAX_REMAINING
, CUST_TRX_LINES.LINE_NUMBER LINE_IDENTIFIER
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_ORIG(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_ORIGINAL
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_APPLIED(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_APPLIED
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_CREDITED(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_CREDITED
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_REMAINING(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_REMAINING
FROM RA_CUSTOMER_TRX_ALL CUST_TRX
, AR_PAYMENT_SCHEDULES_ALL PAY_SCH
, RA_CUSTOMER_TRX_LINES_ALL CUST_TRX_LINES
, OKL_TXD_AR_LN_DTLS_B TLD
, OKC_K_HEADERS_B CHR
WHERE CUST_TRX.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
AND CUST_TRX.CUSTOMER_TRX_ID = CUST_TRX_LINES.CUSTOMER_TRX_ID
AND TLD.ID = CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE14
AND CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE1 IS NULL
AND CUST_TRX_LINES.LINE_TYPE = 'LINE'
AND CHR.CONTRACT_NUMBER = CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE6
AND CUST_TRX_LINES.INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS'
AND CHR.SCS_CODE = 'LEASE') DTLS
WHERE DTLS.KLE_ID IS NOT NULL
AND DTLS.AR_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND DTLS.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG')
AND NOT EXISTS (SELECT /*+ NO_UNNEST */ 1
FROM OKC_K_LINES_B CLE2
, OKC_LINE_STYLES_B LSE
WHERE CLE2.CLE_ID = DTLS.KLE_ID
AND CLE2.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET')
AND APS.PAYMENT_SCHEDULE_ID = DLN.PAYMENT_SCHEDULE_ID(+) UNION ALL SELECT DTLS.LSM_ID LSM_ID
, DTLS.CONTRACT_NUMBER CONTRACT_NUMBER
, STYT.NAME TRANSACTION_TYPE
, DTLS.INVOICE_NUMBER INVOICE_NUMBER
, DTLS.INVOICE_AMOUNT INVOICE_AMOUNT
, DTLS.LINE_IDENTIFIER LINE_IDENTIFIER
, NULL ASSET_NUMBER
, DTLS.INVOICE_DATE INVOICE_DATE
, DTLS.DUE_DATE DUE_DATE
, DTLS.AMOUNT_ORIGINAL TRANSACTION_AMOUNT
, DTLS.AMOUNT_APPLIED AMOUNT_APPLIED
, DTLS.AMOUNT_REMAINING AMOUNT_REMAINING
, DTLS.CUSTOMER_ACCT_ID CUSTOMER_ACCOUNT_ID
, DTLS.ORG_ID CONTRACT_AUTHORING_ORG_ID
, DTLS.CHR_ID KHR_ID
, TO_NUMBER(NULL) KLE_ID
, TO_NUMBER(NULL) ASSET_ID
, DTLS.INVOICE_ID INVOICE_ID
, STYT.ID STY_ID
, DTLS.INVOICE_CURRENCY_CODE CURRENCY_CODE
, APS.EXCHANGE_RATE_TYPE AR_EXCHANGE_RATE_TYPE
, APS.EXCHANGE_RATE AR_EXCHANGE_RATE
, APS.EXCHANGE_DATE AR_EXCHANGE_DATE
, APS.TRX_DATE AR_TRX_DATE
, DTLS.LINE_AMOUNT AMOUNT_LINE_ITEMS_ORIGINAL
, DTLS.AMOUNT_REMAINING AMOUNT_LINE_ITEMS_REMAINING
, DTLS.TAX_AMOUNT TAX_ORIGINAL
, DTLS.TAX_REMAINING TAX_REMAINING
, DTLS.AMOUNT_CREDITED AMOUNT_CREDITED
, DTLS.AR_INVOICE_ID CUSTOMER_TRX_ID
, DTLS.INVOICE_LINE_ID INVOICE_LINE_ID
, DTLS.PAYMENT_TERM PAYMENT_TERM
, DTLS.INVOICE_TYPE INVOICE_TYPE
, APS.TRX_NUMBER TRX_NUMBER
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DELINQUENCY_STATE'
, DLN.STATUS) DEL_STATUS
FROM (SELECT CNR.ID INVOICE_ID
, KHR.ID CHR_ID
, LSM.ID LSM_ID
, CNR.CONSOLIDATED_INVOICE_NUMBER INVOICE_NUMBER
, CNR.CURRENCY_CODE INVOICE_CURRENCY_CODE
, CNR.DUE_DATE DUE_DATE
, CNR.AMOUNT INVOICE_AMOUNT
, LSM.RECEIVABLES_INVOICE_ID AR_INVOICE_ID
, CNR.DATE_CONSOLIDATED INVOICE_DATE
, CNR.IBT_ID BILL_TO_SITE_USE_ID
, CNR.IXX_ID CUSTOMER_ACCT_ID
, CNR.ORG_ID ORG_ID
, 'OKL-CONS-INV' INVOICE_TYPE
, KHR.CONTRACT_NUMBER CONTRACT_NUMBER
, 'IMMEDIATE' PAYMENT_TERM
, LSM.STY_ID STY_ID
, LSM.KLE_ID KLE_ID
, LSM.AMOUNT LINE_AMOUNT
, LSM.TAX_AMOUNT TAX_AMOUNT
, APS.TAX_REMAINING TAX_REMAINING
, TO_NUMBER(APS.TRX_NUMBER) LINE_IDENTIFIER
, NVL(LSM.AMOUNT + LSM.TAX_AMOUNT
, 0) AMOUNT_ORIGINAL
, RACTRL.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_APPLIED(LSM.RECEIVABLES_INVOICE_ID) AMOUNT_APPLIED
, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_CREDITED(LSM.RECEIVABLES_INVOICE_ID) AMOUNT_CREDITED
, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_REMAINING(LSM.RECEIVABLES_INVOICE_ID) AMOUNT_REMAINING
FROM OKC_K_HEADERS_B KHR
, OKL_CNSLD_AR_HDRS_B CNR
, OKL_CNSLD_AR_LINES_B LLN
, OKL_CNSLD_AR_STRMS_B LSM
, AR_PAYMENT_SCHEDULES_ALL APS
, RA_CUSTOMER_TRX_LINES_ALL RACTRL
WHERE CNR.ID = LLN.CNR_ID
AND LLN.ID = LSM.LLN_ID
AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND LSM.KHR_ID = KHR.ID
AND LSM.RECEIVABLES_INVOICE_ID = RACTRL.CUSTOMER_TRX_ID
AND RACTRL.LINE_TYPE = 'LINE') DTLS
, AR_PAYMENT_SCHEDULES_ALL APS
, OKL_STRM_TYPE_TL STYT
, IEX_DELINQUENCIES DLN
WHERE DTLS.KLE_ID IS NULL
AND DTLS.AR_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND DTLS.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG')
AND APS.PAYMENT_SCHEDULE_ID = DLN.PAYMENT_SCHEDULE_ID(+) UNION ALL SELECT DTLS.LSM_ID LSM_ID
, DTLS.CONTRACT_NUMBER CONTRACT_NUMBER
, STYT.NAME TRANSACTION_TYPE
, DTLS.INVOICE_NUMBER INVOICE_NUMBER
, DTLS.INVOICE_AMOUNT INVOICE_AMOUNT
, DTLS.LINE_IDENTIFIER LINE_IDENTIFIER
, NULL ASSET_NUMBER
, DTLS.INVOICE_DATE INVOICE_DATE
, DTLS.DUE_DATE DUE_DATE
, DTLS.AMOUNT_ORIGINAL TRANSACTION_AMOUNT
, DTLS.AMOUNT_APPLIED AMOUNT_APPLIED
, DTLS.AMOUNT_REMAINING AMOUNT_REMAINING
, DTLS.CUSTOMER_ACCT_ID CUSTOMER_ACCOUNT_ID
, DTLS.ORG_ID CONTRACT_AUTHORING_ORG_ID
, DTLS.CHR_ID KHR_ID
, TO_NUMBER(NULL) KLE_ID
, TO_NUMBER(NULL) ASSET_ID
, DTLS.INVOICE_ID INVOICE_ID
, STYT.ID STY_ID
, DTLS.INVOICE_CURRENCY_CODE CURRENCY_CODE
, APS.EXCHANGE_RATE_TYPE AR_EXCHANGE_RATE_TYPE
, APS.EXCHANGE_RATE AR_EXCHANGE_RATE
, APS.EXCHANGE_DATE AR_EXCHANGE_DATE
, APS.TRX_DATE AR_TRX_DATE
, DTLS.LINE_AMOUNT AMOUNT_LINE_ITEMS_ORIGINAL
, DTLS.AMOUNT_REMAINING AMOUNT_LINE_ITEMS_REMAINING
, DTLS.TAX_AMOUNT TAX_ORIGINAL
, DTLS.TAX_REMAINING TAX_REMAINING
, DTLS.AMOUNT_CREDITED AMOUNT_CREDITED
, DTLS.AR_INVOICE_ID CUSTOMER_TRX_ID
, DTLS.INVOICE_LINE_ID INVOICE_LINE_ID
, DTLS.PAYMENT_TERM PAYMENT_TERM
, DTLS.INVOICE_TYPE INVOICE_TYPE
, APS.TRX_NUMBER TRX_NUMBER
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DELINQUENCY_STATE'
, DLN.STATUS) DEL_STATUS
FROM (SELECT CUST_TRX.CUSTOMER_TRX_ID INVOICE_ID
, CHR.ID CHR_ID
, TLD.ID LSM_ID
, CUST_TRX.TRX_NUMBER INVOICE_NUMBER
, CUST_TRX.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, PAY_SCH.DUE_DATE DUE_DATE
, PAY_SCH.AMOUNT_DUE_ORIGINAL INVOICE_AMOUNT
, CUST_TRX.CUSTOMER_TRX_ID AR_INVOICE_ID
, CUST_TRX.TRX_DATE INVOICE_DATE
, CUST_TRX.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, CUST_TRX.BILL_TO_CUSTOMER_ID CUSTOMER_ACCT_ID
, CUST_TRX.ORG_ID ORG_ID
, 'AR-INVOICE' INVOICE_TYPE
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE6 CONTRACT_NUMBER
, 'IMMEDIATE' PAYMENT_TERM
, TLD.STY_ID STY_ID
, TLD.KLE_ID KLE_ID
, EXTENDED_AMOUNT LINE_AMOUNT
, (SELECT SUM(NVL(EXTENDED_AMOUNT
, 0))
FROM RA_CUSTOMER_TRX_LINES_ALL B
WHERE B.LINK_TO_CUST_TRX_LINE_ID = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) TAX_AMOUNT
, (SELECT SUM(NVL(AMOUNT_DUE_REMAINING
, 0))
FROM RA_CUSTOMER_TRX_LINES_ALL B
WHERE B.LINK_TO_CUST_TRX_LINE_ID = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) TAX_REMAINING
, CUST_TRX_LINES.LINE_NUMBER LINE_IDENTIFIER
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_ORIG(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_ORIGINAL
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_APPLIED(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_APPLIED
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_CREDITED(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_CREDITED
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_REMAINING(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_REMAINING
FROM RA_CUSTOMER_TRX_ALL CUST_TRX
, AR_PAYMENT_SCHEDULES_ALL PAY_SCH
, RA_CUSTOMER_TRX_LINES_ALL CUST_TRX_LINES
, OKL_TXD_AR_LN_DTLS_B TLD
, OKC_K_HEADERS_B CHR
WHERE CUST_TRX.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
AND CUST_TRX.CUSTOMER_TRX_ID = CUST_TRX_LINES.CUSTOMER_TRX_ID
AND TLD.ID = CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE14
AND CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE1 IS NULL
AND CUST_TRX_LINES.LINE_TYPE = 'LINE'
AND CHR.CONTRACT_NUMBER = CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE6
AND CUST_TRX_LINES.INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS'
AND CHR.SCS_CODE = 'LEASE') DTLS
, AR_PAYMENT_SCHEDULES_ALL APS
, OKL_STRM_TYPE_TL STYT
, IEX_DELINQUENCIES DLN
WHERE DTLS.KLE_ID IS NULL
AND DTLS.AR_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND DTLS.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG')
AND APS.PAYMENT_SCHEDULE_ID = DLN.PAYMENT_SCHEDULE_ID(+) UNION ALL SELECT DTLS.LSM_ID LSM_ID
, DTLS.CONTRACT_NUMBER CONTRACT_NUMBER
, STYT.NAME TRANSACTION_TYPE
, DTLS.INVOICE_NUMBER INVOICE_NUMBER
, DTLS.INVOICE_AMOUNT INVOICE_AMOUNT
, DTLS.LINE_IDENTIFIER LINE_IDENTIFIER
, CLT.NAME ASSET_NUMBER
, DTLS.INVOICE_DATE INVOICE_DATE
, DTLS.DUE_DATE DUE_DATE
, DTLS.AMOUNT_ORIGINAL TRANSACTION_AMOUNT
, DTLS.AMOUNT_APPLIED AMOUNT_APPLIED
, DTLS.AMOUNT_REMAINING AMOUNT_REMAINING
, DTLS.CUSTOMER_ACCT_ID CUSTOMER_ACCOUNT_ID
, DTLS.ORG_ID CONTRACT_AUTHORING_ORG_ID
, DTLS.CHR_ID KHR_ID
, DTLS.KLE_ID KLE_ID
, TO_NUMBER(NULL) ASSET_ID
, DTLS.INVOICE_ID INVOICE_ID
, STYT.ID STY_ID
, DTLS.INVOICE_CURRENCY_CODE CURRENCY_CODE
, APS.EXCHANGE_RATE_TYPE AR_EXCHANGE_RATE_TYPE
, APS.EXCHANGE_RATE AR_EXCHANGE_RATE
, APS.EXCHANGE_DATE AR_EXCHANGE_DATE
, APS.TRX_DATE AR_TRX_DATE
, DTLS.LINE_AMOUNT AMOUNT_LINE_ITEMS_ORIGINAL
, DTLS.AMOUNT_REMAINING AMOUNT_LINE_ITEMS_REMAINING
, DTLS.TAX_AMOUNT TAX_ORIGINAL
, DTLS.TAX_REMAINING TAX_REMAINING
, DTLS.AMOUNT_CREDITED AMOUNT_CREDITED
, DTLS.AR_INVOICE_ID CUSTOMER_TRX_ID
, DTLS.INVOICE_LINE_ID INVOICE_LINE_ID
, DTLS.PAYMENT_TERM PAYMENT_TERM
, DTLS.INVOICE_TYPE INVOICE_TYPE
, APS.TRX_NUMBER TRX_NUMBER
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DELINQUENCY_STATE'
, DLN.STATUS) DEL_STATUS
FROM (SELECT CNR.ID INVOICE_ID
, KHR.ID CHR_ID
, LSM.ID LSM_ID
, CNR.CONSOLIDATED_INVOICE_NUMBER INVOICE_NUMBER
, CNR.CURRENCY_CODE INVOICE_CURRENCY_CODE
, CNR.DUE_DATE DUE_DATE
, CNR.AMOUNT INVOICE_AMOUNT
, LSM.RECEIVABLES_INVOICE_ID AR_INVOICE_ID
, CNR.DATE_CONSOLIDATED INVOICE_DATE
, CNR.IBT_ID BILL_TO_SITE_USE_ID
, CNR.IXX_ID CUSTOMER_ACCT_ID
, CNR.ORG_ID ORG_ID
, 'OKL-CONS-INV' INVOICE_TYPE
, KHR.CONTRACT_NUMBER CONTRACT_NUMBER
, 'IMMEDIATE' PAYMENT_TERM
, LSM.STY_ID STY_ID
, LSM.KLE_ID KLE_ID
, LSM.AMOUNT LINE_AMOUNT
, LSM.TAX_AMOUNT TAX_AMOUNT
, APS.TAX_REMAINING TAX_REMAINING
, TO_NUMBER(APS.TRX_NUMBER) LINE_IDENTIFIER
, NVL(LSM.AMOUNT + LSM.TAX_AMOUNT
, 0) AMOUNT_ORIGINAL
, RACTRL.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_APPLIED(LSM.RECEIVABLES_INVOICE_ID) AMOUNT_APPLIED
, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_CREDITED(LSM.RECEIVABLES_INVOICE_ID) AMOUNT_CREDITED
, OKL_BILLING_UTIL_PVT.INVOICE_AMOUNT_REMAINING(LSM.RECEIVABLES_INVOICE_ID) AMOUNT_REMAINING
FROM OKC_K_HEADERS_B KHR
, OKL_CNSLD_AR_HDRS_B CNR
, OKL_CNSLD_AR_LINES_B LLN
, OKL_CNSLD_AR_STRMS_B LSM
, AR_PAYMENT_SCHEDULES_ALL APS
, RA_CUSTOMER_TRX_LINES_ALL RACTRL
WHERE CNR.ID = LLN.CNR_ID
AND LLN.ID = LSM.LLN_ID
AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND LSM.KHR_ID = KHR.ID
AND LSM.RECEIVABLES_INVOICE_ID = RACTRL.CUSTOMER_TRX_ID
AND RACTRL.LINE_TYPE = 'LINE') DTLS
, AR_PAYMENT_SCHEDULES_ALL APS
, OKL_STRM_TYPE_TL STYT
, OKL_K_HEADERS CHL
, OKC_K_LINES_B CLE
, OKC_LINE_STYLES_B LSE
, OKC_K_LINES_TL CLT
, IEX_DELINQUENCIES DLN
WHERE DTLS.KLE_ID IS NOT NULL
AND DTLS.AR_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND DTLS.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG')
AND DTLS.CHR_ID = CHL.ID
AND CHL.DEAL_TYPE = 'LOAN'
AND DTLS.KLE_ID = CLE.ID
AND CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FREE_FORM1'
AND CLE.ID = CLT.ID
AND CLT.LANGUAGE = USERENV('LANG')
AND APS.PAYMENT_SCHEDULE_ID = DLN.PAYMENT_SCHEDULE_ID(+) UNION ALL SELECT DTLS.LSM_ID LSM_ID
, DTLS.CONTRACT_NUMBER CONTRACT_NUMBER
, STYT.NAME TRANSACTION_TYPE
, DTLS.INVOICE_NUMBER INVOICE_NUMBER
, DTLS.INVOICE_AMOUNT INVOICE_AMOUNT
, DTLS.LINE_IDENTIFIER LINE_IDENTIFIER
, CLT.NAME ASSET_NUMBER
, DTLS.INVOICE_DATE INVOICE_DATE
, DTLS.DUE_DATE DUE_DATE
, DTLS.AMOUNT_ORIGINAL TRANSACTION_AMOUNT
, DTLS.AMOUNT_APPLIED AMOUNT_APPLIED
, DTLS.AMOUNT_REMAINING AMOUNT_REMAINING
, DTLS.CUSTOMER_ACCT_ID CUSTOMER_ACCOUNT_ID
, DTLS.ORG_ID CONTRACT_AUTHORING_ORG_ID
, DTLS.CHR_ID KHR_ID
, DTLS.KLE_ID KLE_ID
, TO_NUMBER(NULL) ASSET_ID
, DTLS.INVOICE_ID INVOICE_ID
, STYT.ID STY_ID
, DTLS.INVOICE_CURRENCY_CODE CURRENCY_CODE
, APS.EXCHANGE_RATE_TYPE AR_EXCHANGE_RATE_TYPE
, APS.EXCHANGE_RATE AR_EXCHANGE_RATE
, APS.EXCHANGE_DATE AR_EXCHANGE_DATE
, APS.TRX_DATE AR_TRX_DATE
, DTLS.LINE_AMOUNT AMOUNT_LINE_ITEMS_ORIGINAL
, DTLS.AMOUNT_REMAINING AMOUNT_LINE_ITEMS_REMAINING
, DTLS.TAX_AMOUNT TAX_ORIGINAL
, DTLS.TAX_REMAINING TAX_REMAINING
, DTLS.AMOUNT_CREDITED AMOUNT_CREDITED
, DTLS.AR_INVOICE_ID CUSTOMER_TRX_ID
, DTLS.INVOICE_LINE_ID INVOICE_LINE_ID
, DTLS.PAYMENT_TERM PAYMENT_TERM
, DTLS.INVOICE_TYPE INVOICE_TYPE
, APS.TRX_NUMBER TRX_NUMBER
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DELINQUENCY_STATE'
, DLN.STATUS) DEL_STATUS
FROM (SELECT CUST_TRX.CUSTOMER_TRX_ID INVOICE_ID
, CHR.ID CHR_ID
, TLD.ID LSM_ID
, CUST_TRX.TRX_NUMBER INVOICE_NUMBER
, CUST_TRX.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, PAY_SCH.DUE_DATE DUE_DATE
, PAY_SCH.AMOUNT_DUE_ORIGINAL INVOICE_AMOUNT
, CUST_TRX.CUSTOMER_TRX_ID AR_INVOICE_ID
, CUST_TRX.TRX_DATE INVOICE_DATE
, CUST_TRX.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, CUST_TRX.BILL_TO_CUSTOMER_ID CUSTOMER_ACCT_ID
, CUST_TRX.ORG_ID ORG_ID
, 'AR-INVOICE' INVOICE_TYPE
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE6 CONTRACT_NUMBER
, 'IMMEDIATE' PAYMENT_TERM
, TLD.STY_ID STY_ID
, TLD.KLE_ID KLE_ID
, EXTENDED_AMOUNT LINE_AMOUNT
, (SELECT SUM(NVL(EXTENDED_AMOUNT
, 0))
FROM RA_CUSTOMER_TRX_LINES_ALL B
WHERE B.LINK_TO_CUST_TRX_LINE_ID = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) TAX_AMOUNT
, (SELECT SUM(NVL(AMOUNT_DUE_REMAINING
, 0))
FROM RA_CUSTOMER_TRX_LINES_ALL B
WHERE B.LINK_TO_CUST_TRX_LINE_ID = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) TAX_REMAINING
, CUST_TRX_LINES.LINE_NUMBER LINE_IDENTIFIER
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_ORIG(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_ORIGINAL
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_APPLIED(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_APPLIED
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_CREDITED(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_CREDITED
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_REMAINING(CUST_TRX.CUSTOMER_TRX_ID
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID) AMOUNT_REMAINING
FROM RA_CUSTOMER_TRX_ALL CUST_TRX
, AR_PAYMENT_SCHEDULES_ALL PAY_SCH
, RA_CUSTOMER_TRX_LINES_ALL CUST_TRX_LINES
, OKL_TXD_AR_LN_DTLS_B TLD
, OKC_K_HEADERS_B CHR
WHERE CUST_TRX.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
AND CUST_TRX.CUSTOMER_TRX_ID = CUST_TRX_LINES.CUSTOMER_TRX_ID
AND TLD.ID = CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE14
AND CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE1 IS NULL
AND CUST_TRX_LINES.LINE_TYPE = 'LINE'
AND CHR.CONTRACT_NUMBER = CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE6
AND CUST_TRX_LINES.INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS'
AND CHR.SCS_CODE = 'LEASE') DTLS
, AR_PAYMENT_SCHEDULES_ALL APS
, OKL_STRM_TYPE_TL STYT
, OKL_K_HEADERS CHL
, OKC_K_LINES_B CLE
, OKC_LINE_STYLES_B LSE
, OKC_K_LINES_TL CLT
, IEX_DELINQUENCIES DLN
WHERE DTLS.KLE_ID IS NOT NULL
AND DTLS.AR_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND DTLS.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG')
AND DTLS.CHR_ID = CHL.ID
AND CHL.DEAL_TYPE = 'LOAN'
AND DTLS.KLE_ID = CLE.ID
AND CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FREE_FORM1'
AND CLE.ID = CLT.ID
AND CLT.LANGUAGE = USERENV('LANG')
AND APS.PAYMENT_SCHEDULE_ID = DLN.PAYMENT_SCHEDULE_ID(+)