AP Invoice History Report- Not Supported: Reserved For Future Use

SELECT DISTINCT
    pv1.vendor_name C_VENDOR,
    pv1.vendor_id C_VENDOR_ID,
    pvs.vendor_site_code C_VENDOR_SITE,
    i.invoice_num C_INVOICE_NUMBER,
    i.invoice_id C_INVOICE_ID1,     i.invoice_date C_TRANSACTION_DATE,
    i.invoice_type_lookup_code C_TRANSACTION_TYPE,
    alc.displayed_field C_TRANSACTION_TYPE_FIELD,
    i.payment_currency_code C_CURR,
    DECODE (i.doc_sequence_value, NULL,i.voucher_num, i.doc_sequence_value) C_DOC_SEQUENCE_NUMBER,
    f2.name C_DOC_SEQUENCE_NAME,
    DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', nvl(ap_utilities_pkg.ap_round_currency(i.invoice_amount* i.payment_cross_rate , i.payment_currency_code) ,0),
           nvl(nvl(i.pay_curr_invoice_amount, i.invoice_amount),0)) - nvl(ap_invoices_pkg.get_amount_withheld(i.invoice_id),0) C_TOTAL_INVOICE_AMT,
    ck.doc_sequence_value C_DOC_SEQUENCE_NUMBER1,
    ck.check_number C_TRANSACTION_NUMBER,
    f.name C_DOC_SEQUENCE_NAME1,
    aip.accounting_date C_TRANSACTION_DATE2,
    ael.accounting_class_code C_LINE_TYPE_CODE,
    DECODE(ael.accounting_class_code, 'CASH', 'Cash',
                                      'DISCOUNT','Discount',
                                      'GAIN','Gain',
                                      'LOSS','Loss',
                                      'CASH CLEARING','Cash Clearing' ) C_TRANSACTION_TYPE2,
    ck.currency_code C_CURR2,
    DECODE(ael.accounting_class_code, 'CASH',-1*aip.amount,
                                      'CASH CLEARING',-1*aip.amount,
                                      'GAIN',nvl(aip.invoice_base_amount,aip.amount)- nvl(aip.payment_base_amount,aip.amount),
                                      'LOSS',nvl(aip.invoice_base_amount,aip.amount)- nvl(aip.payment_base_amount,aip.amount),
                                      'DISCOUNT',ap_utilities_pkg.ap_round_currency(-1*nvl(aip.discount_taken,0)*i.payment_cross_rate ,i.payment_currency_code)
)C_TRANSACTION_AMT ,
invoice_payment_id     C_INVOICE_PAYMENT_ID,
	AP_APXINHIS_XMLP_PKG.c_vendor_site_totalformula(:C_TOTAL_INVOICE_AMOUNT_SUM, :C_TOTAL_PAYMENT_AMOUNT_SUM, :C_TOTAL_PREPAY_AMT_SUM) C_VENDOR_SITE_TOTAL,
	AP_APXINHIS_XMLP_PKG.c_currency_totalformula(:C_TOTAL_INVOICE_AMT_CURR, :C_TOTAL_PAYMENT_AMT_CURR, :C_TOTAL_PREPAY_AMT) C_CURRENCY_TOTAL,
	AP_APXINHIS_XMLP_PKG.c_prepay_amt_fformula(i.invoice_id) C_PREPAY_AMT_F,
	AP_APXINHIS_XMLP_PKG.c_transaction_amt1_dumformula(:C_TRANSACTION_TYPE3, DECODE ( ael.accounting_class_code , 'CASH' , - 1 * aip.amount , 'CASH CLEARING' , - 1 * aip.amount , 'GAIN' , nvl ( aip.invoice_base_amount , aip.amount ) - nvl ( aip.payment_base_amount , aip.amount ) , 'LOSS' , nvl ( aip.invoice_base_amount , aip.amount ) - nvl ( aip.payment_base_amount , aip.amount ) , 'DISCOUNT' , ap_utilities_pkg.ap_round_currency ( - 1 * nvl ( aip.discount_taken , 0 ) * i.payment_cross_rate , i.payment_currency_code ) )) C_TRANSACTION_AMT1_DUM,
	AP_APXINHIS_XMLP_PKG.c_open_balance_amt1formula(:C_TRANSACTION_TYPE3, :C_OPEN_BALANCE_AMT, :C_TRX_AMOUNT_SUM, :C_PREPAY_AMT_F, :C_TRANSACTION_AMT1_DUMMY_SUM) C_open_balance_amt1
FROM po_vendors pv1,
    po_vendor_sites pvs,
    ap_invoices i,
    ap_checks ck,
    fnd_document_sequences f,
    fnd_document_sequences f2,
    ap_invoice_payments aip,
    xla_transaction_entities ATE,
    xla_events AAE ,
    xla_ae_headers AEH,
    xla_ae_lines AEL,
    ap_lookup_codes alc
WHERE pv1.vendor_id = pvs.vendor_id
    AND i.vendor_id = pv1.vendor_id
    AND i.vendor_site_id = pvs.vendor_site_id
    AND i.invoice_id = aip.invoice_id (+)
    AND i.invoice_type_lookup_code = alc.lookup_code
    AND alc.lookup_type = 'INVOICE TYPE'
    AND aip.accounting_event_id = AEH.event_id(+)
    AND AEH.event_id = AAE.event_id(+)
    AND AAE.entity_id = ATE.entity_id(+)
    AND AEH.ae_header_id = AEL.ae_header_id(+)
    AND ATE.entity_code(+) = 'PAYMENTS'
    AND
    (
        (
            (
                AEL.accounting_class_code = 'DISCOUNT'
            )
            AND
            (
                AIP.invoice_payment_id = ATE.source_id_int_1
            )
        )
        OR
        (   AEL.accounting_class_code IS NULL
            OR AEL.accounting_class_code <> 'DISCOUNT'
        )
    )
    AND aeh.ledger_id(+) = :P_BOOk
    AND ck.check_id (+)= aip.check_id
    AND f.doc_sequence_id (+) = ck.doc_sequence_id
    AND f2.doc_sequence_id(+)= i.doc_sequence_id
    AND DECODE(i.invoice_type_lookup_code, 'PREPAYMENT',NVL(aip.invoice_payment_type, 'X'),1) <>
        DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', 'PREPAY' , 2)
   &LP_VENDOR_ID
   &LP_VENDOR_SITE
   &LP_INVOICES
   &LP_INVOICES_NUMBER_FROM
   &LP_INVOICES_NUMBER_TO
   &LP_DOC_SEQUENCE_NAME
   &LP_DOC_SEQUENCE_NUMBER_FROM
   &LP_DOC_SEQUENCE_NUMBER_TO
   &LP_INVOICE_DATE_FROM
   &LP_INVOICE_DATE_TO
UNION
SELECT DISTINCT
    pv1.vendor_name C_VENDOR,
    pv1.vendor_id C_VENDOR_ID,
    pvs.vendor_site_code C_VENDOR_SITE,
    i.invoice_num C_INVOICE_NUMBER,
    i.invoice_id C_INVOICE_ID1,     i.invoice_date C_TRANSACTION_DATE,
    i.invoice_type_lookup_code C_TRANSACTION_TYPE,
    alc.displayed_field C_TRANSACTION_TYPE_FIELD,
    i.payment_currency_code C_CURR,
    DECODE (i.doc_sequence_value, NULL,i.voucher_num, i.doc_sequence_value) C_DOC_SEQUENCE_NUMBER,
    f2.name C_DOC_SEQUENCE_NAME,
    DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', nvl(ap_utilities_pkg.ap_round_currency(i.invoice_amount* i.payment_cross_rate , i.payment_currency_code) ,0),
           nvl(nvl(i.pay_curr_invoice_amount, i.invoice_amount),0)) - nvl(ap_invoices_pkg.get_amount_withheld(i.invoice_id),0) C_TOTAL_INVOICE_AMT,
    ck.doc_sequence_value C_DOC_SEQUENCE_NUMBER1,
    ck.check_number C_TRANSACTION_NUMBER,
    f.name C_DOC_SEQUENCE_NAME1,
    aip.accounting_date C_TRANSACTION_DATE2,
    ael.accounting_class_code C_LINE_TYPE_CODE,
    DECODE(ael.accounting_class_code, 'CASH', 'Cash',
                                      'DISCOUNT','Discount',
                                      'GAIN','Gain',
                                      'LOSS','Loss',
                                      'CASH CLEARING','Cash Clearing' ) C_TRANSACTION_TYPE2,
    ck.currency_code C_CURR2,
    DECODE(ael.accounting_class_code, 'CASH',-1*aip.amount,
                                      'CASH CLEARING',-1*aip.amount,
                                      'GAIN',nvl(aip.invoice_base_amount,aip.amount)- nvl(aip.payment_base_amount,aip.amount),
                                      'LOSS',nvl(aip.invoice_base_amount,aip.amount)- nvl(aip.payment_base_amount,aip.amount),
                                      'DISCOUNT',ap_utilities_pkg.ap_round_currency(-1*nvl(aip.discount_taken,0)*i.payment_cross_rate ,i.payment_currency_code)
)C_TRANSACTION_AMT ,
invoice_payment_id     C_INVOICE_PAYMENT_ID,
	AP_APXINHIS_XMLP_PKG.c_vendor_site_totalformula(:C_TOTAL_INVOICE_AMOUNT_SUM, :C_TOTAL_PAYMENT_AMOUNT_SUM, :C_TOTAL_PREPAY_AMT_SUM) C_VENDOR_SITE_TOTAL,
	AP_APXINHIS_XMLP_PKG.c_currency_totalformula(:C_TOTAL_INVOICE_AMT_CURR, :C_TOTAL_PAYMENT_AMT_CURR, :C_TOTAL_PREPAY_AMT) C_CURRENCY_TOTAL,
	AP_APXINHIS_XMLP_PKG.c_prepay_amt_fformula(i.invoice_id) C_PREPAY_AMT_F,
	AP_APXINHIS_XMLP_PKG.c_transaction_amt1_dumformula(:C_TRANSACTION_TYPE3, DECODE ( ael.accounting_class_code , 'CASH' , - 1 * aip.amount , 'CASH CLEARING' , - 1 * aip.amount , 'GAIN' , nvl ( aip.invoice_base_amount , aip.amount ) - nvl ( aip.payment_base_amount , aip.amount ) , 'LOSS' , nvl ( aip.invoice_base_amount , aip.amount ) - nvl ( aip.payment_base_amount , aip.amount ) , 'DISCOUNT' , ap_utilities_pkg.ap_round_currency ( - 1 * nvl ( aip.discount_taken , 0 ) * i.payment_cross_rate , i.payment_currency_code ) )) C_TRANSACTION_AMT1_DUM,
	AP_APXINHIS_XMLP_PKG.c_open_balance_amt1formula(:C_TRANSACTION_TYPE3, :C_OPEN_BALANCE_AMT, :C_TRX_AMOUNT_SUM, :C_PREPAY_AMT_F, :C_TRANSACTION_AMT1_DUMMY_SUM) C_open_balance_amt1
FROM po_vendors pv1,
    po_vendor_sites pvs,
    ap_invoices i,
    ap_checks ck,
    fnd_document_sequences f,
    fnd_document_sequences f2,
    ap_invoice_payments aip,
    xla_transaction_entities ATE,
    xla_events AAE ,
    xla_ae_headers AEH,
    xla_ae_lines AEL,
    ap_lookup_codes alc,
    ap_payment_history aph
WHERE pv1.vendor_id = pvs.vendor_id
    AND i.vendor_id = pv1.vendor_id
    AND i.vendor_site_id = pvs.vendor_site_id
    AND i.invoice_id = aip.invoice_id
    AND i.invoice_type_lookup_code = alc.lookup_code
    AND alc.lookup_type = 'INVOICE TYPE'
    AND aip.accounting_event_id = AEH.event_id
    AND AEH.event_id = AAE.event_id
    AND AAE.entity_id = ATE.entity_id
    AND AEH.ae_header_id = AEL.ae_header_id
    AND ATE.entity_code = 'PAYMENTS'
    AND
    (
        (
            (
                AEL.accounting_class_code = 'DISCOUNT'
            )
            AND
            (
                AIP.invoice_payment_id = ATE.source_id_int_1
            )
        )
        OR
        (   AEL.accounting_class_code IS NULL
            OR AEL.accounting_class_code <> 'DISCOUNT'
        )
    )
    AND aeh.ledger_id = :P_BOOk
    AND ck.check_id = aip.check_id
    AND f.doc_sequence_id (+) = ck.doc_sequence_id
    AND f2.doc_sequence_id(+)= i.doc_sequence_id
    AND DECODE(i.invoice_type_lookup_code, 'PREPAYMENT',NVL(aip.invoice_payment_type, 'X'),1) <>
        DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', 'PREPAY' , 2)
    AND   aph.accounting_event_id = aeh.event_id
    AND   aip.check_id = aph.check_id
    AND   aph.transaction_type = 'PAYMENT MATURITY'
   &LP_VENDOR_ID
   &LP_VENDOR_SITE
   &LP_INVOICES
   &LP_INVOICES_NUMBER_FROM
   &LP_INVOICES_NUMBER_TO
   &LP_DOC_SEQUENCE_NAME
   &LP_DOC_SEQUENCE_NUMBER_FROM
   &LP_DOC_SEQUENCE_NUMBER_TO
   &LP_INVOICE_DATE_FROM
   &LP_INVOICE_DATE_TO
ORDER BY C_VENDOR,
    C_VENDOR_SITE,
    C_INVOICE_NUMBER,
    C_TRANSACTION_DATE,
    C_TRANSACTION_TYPE,
    C_TRANSACTION_DATE2,
    C_LINE_TYPE_CODE,
    C_INVOICE_PAYMENT_ID