ECC Lease Contracts, Lease Vendor Cash Flow
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: okl-vendor-cash
Query Procedure: OKL_ECC_VCF_PVT.GET_ECC_VCF_DATA_LOAD
Security Procedure: okl_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Dataset Key: okl-vendor-cash
Query Procedure: OKL_ECC_VCF_PVT.GET_ECC_VCF_DATA_LOAD
Security Procedure: okl_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Run
ECC Lease Contracts, Lease Vendor Cash Flow and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( SELECT * FROM ( SELECT spec_id ECC_SPEC_ID, vcf.contract_id, vcf.contract_number, nvl(khr_sts.language,' ') language_code, vcf.name, vcf.account_number, khr_sts.meaning sts_code, vcf.currency_code, vcf.org_id, vcf.cle_id, vcf.parent_cle_id, decode(vcf.lse_id,57,OKL_ECC_VCF_PVT.get_usage_line_name(vcf.parent_cle_id,vcf.lse_id), 49,OKL_ECC_VCF_PVT.get_usage_line_name(vcf.parent_cle_id,vcf.lse_id),khr_line.name) line_name, vcf.line_id, vcf.lse_id, nvl(vcf.asset_number,' ') asset_number, vcf.sel_id, vcf.passthru_stream_type_id, (SELECT nvl(max(sttl.name),' ') name FROM okl_strm_type_tl sttl WHERE sttl.id = vcf.passthru_stream_type_id AND sttl.language = khr_sts.language) passthru_stream_purpose, ( SELECT fnd.meaning FROM fnd_lookup_values fnd WHERE fnd.lookup_type ='OKL_PAYOUT_BASIS' AND fnd.lookup_code = vcf.payout_basis AND fnd.language = khr_sts.language ) payout_basis, ( SELECT fnd.meaning FROM fnd_lookup_values fnd WHERE fnd.lookup_type = 'OKL_PAYMENT_BASIS' AND fnd.lookup_code = vcf.payment_basis AND fnd.language = khr_sts.language ) payment_basis, vcf.line_start_date, vcf.line_end_date, vcf.line_type, vcf.vendor_name, vcf.vendor_id, to_number(decode(vcf.passthru_stream_type_purpose,'PASS_THROUGH_ADVANCE',to_number(0),vcf.stream_amount)) stream_amount, decode(vcf.passthru_stream_type_purpose,'PASS_THROUGH_ADVANCE',null,vcf.stream_period) stream_period, vcf.ap_period, vcf.ar_period, to_date(decode(vcf.passthru_stream_type_purpose,'PASS_THROUGH_ADVANCE',null ,to_char(vcf.stream_element_date,'DD-MON-YYYY'))) STREAM_ELEMENT_DATE, ( SELECT fnd.meaning FROM fnd_lookup_values fnd WHERE fnd.lookup_type = 'OKL_PASSTHROUGH_TERM' AND fnd.lookup_code = vcf.passthru_term AND fnd.language = khr_sts.language ) passthru_term, ( SELECT fnd.meaning FROM fnd_lookup_values fnd WHERE fnd.lookup_type = 'OKL_TRANSACTION_STATUS' AND fnd.lookup_code = vcf.ar_pay_status_code AND fnd.language = khr_sts.language ) ar_pay_status_code, vcf.ar_inv_format_type, vcf.ar_inv_format_line_type, vcf.ar_inv_date_disbursed, vcf.ar_inv_date_invoiced, vcf.ar_inv_amount, vcf.tld_id, vcf.ar_inv_number, vcf.line_number, nvl(vcf.ar_inv_line_amt,0) ar_inv_line_amt, OKL_ECC_VCF_PVT.get_calc_strm_line_amt(vcf.contract_id, vcf.sel_id, vcf.tld_id, vcf.stream_amount, 'N') calc_strm_line_amt, OKL_ECC_VCF_PVT.get_calc_ar_line_amt(vcf.contract_id, vcf.sel_id, vcf.tld_id, vcf.ar_inv_line_amt, 'N') calc_ar_line_amt, OKL_ECC_VCF_PVT.get_calc_rec_appl_amt(vcf.contract_id, vcf.sel_id, vcf.tld_id, vcf.receivable_application_id , vcf.receipt_amt_applied, 'N') calc_rec_appl_amt, OKL_ECC_VCF_PVT.get_calc_ap_ln_amt_paid(vcf.contract_id, vcf.sel_id, vcf.tld_id, vcf.check_id , vcf.check_amount, 'N') calc_ap_ln_amt_paid, vcf.customer_trx_id, vcf.customer_trx_line_id, vcf.receipt_number, vcf.receipt_amt_applied, vcf.remaining_balance, vcf.invoice_age, vcf.invoice_num, ( SELECT fnd.meaning FROM fnd_lookup_values fnd WHERE fnd.lookup_type = 'OKL_TRANSACTION_STATUS' AND fnd.lookup_code = vcf.ap_invoice_status AND fnd.language = khr_sts.language ) ap_invoice_status, ( SELECT fnd.meaning FROM fnd_lookup_values fnd WHERE fnd.lookup_type = 'OKL_PAYABLES_INVOICE_TYPE' AND fnd.lookup_code = vcf.ap_invoice_type_lookup_code AND fnd.language = khr_sts.language ) ap_invoice_type, vcf.invoice_date, (vcf.ap_inv_amount-vcf.ap_inv_total_tax_amt) ap_inv_amount, vcf.applied_inv_number, vcf.applied_inv_amount, vcf.check_number, vcf.check_date, vcf.check_amount, vcf.ap_invoice_age, vcf.number_of_vendors, vcf.vendor_site, vcf.expected_recv_flag, vcf.billed_flag, vcf.received_flag, vcf.disbursed_flag, vcf.clawback_flag, vcf.adjustment_flag, vcf.payment_status_flag, vcf.due_date, vcf.ap_pay_amt, vcf.hold_count, vcf.sel_last_update_date, vcf.ar_line_last_update_date, vcf.ap_line_last_update_date, vcf.payables_inv_number, vcf.pay_count, vcf.payment_number, vcf.payment_date, vcf.payment_amount, vcf.line_nxt_anniv, 'Forced Pass-through' action, vcf.receipt_date, vcf.payables_inv_date, vcf.ap_inv_total_tax_amt, vcf.invoice_id, nvl(vcf.installment,1) installment, vcf.inv_amount_paid, vcf.ap_invoice_balance Balance_ap_inv_amt, vcf.check_id, vcf.cash_receipt_id, vcf.receivable_application_id, vcf.invoice_payment_id, vcf.eligible_not_disb_amt, nvl(vcf.eligible_not_disb_flag,'u') eligible_not_disb_flag, vcf.sgn_code, (vcf.ap_inv_line_amount-nvl(ap_line_amount_paid,0)) open_payables, vcf.ap_inv_line_number, vcf.ap_inv_line_amount, vcf.ap_line_amount_paid, vcf.receipt_appl_date, nvl(vcf.ap_flag,'N') ap_flag, nvl(vcf.ap_inv_line_amount,0)-nvl(vcf.ap_line_amount_paid,0) Balance_ap_line_amount, nvl(vcf.ap_invoice_type_lookup_code,' ') ap_invoice_type_lookup_code, vcf.say_code say_code FROM okl_ecc_vcf_details_t vcf, okc_statuses_tl khr_sts, okc_k_lines_tl khr_line WHERE vcf.sts_code = khr_sts.code AND vcf.line_id = khr_line.id AND khr_sts.language = khr_line.language AND vcf.processing_status_flag ='N' AND khr_sts.language in('US') ) PIVOT (MAX(sts_code) AS sts_code, MAX(line_name) AS line_name, MAX(passthru_stream_purpose) AS passthru_stream_purpose, MAX(payout_basis) AS payout_basis, MAX(payment_basis) AS payment_basis, MAX(passthru_term) AS passthru_term, MAX(ar_pay_status_code) AS ar_pay_status_code, MAX(ap_invoice_status) AS ap_invoice_status, MAX(ap_invoice_type) AS ap_invoice_type for language_code in ('US' "US")) ) x where 2=2 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV |