ECC Lease Contracts, Lease Vendor Cash Flow

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