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

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Invoice History Report
Application: Payables
Source: Invoice History Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXINHIS_XML
DB package: AP_APXINHIS_XMLP_PKG
Run AP Invoice History Report- Not Supported: Reserved For Future Use and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Operating Unit 
 
 LOV
Supplier Name 
 
 LOV Oracle
Supplier Site 
 
 LOV Oracle
Prepayments Only 
 
 LOV Oracle
Invoice Number From 
 
 Char
Invoice Number To 
 
 Char
Sequence Name 
 
 LOV Oracle
Voucher Number From 
 
 Number
Voucher Number To 
 
 Number
From Invoice Date 
 
 Date
To Invoice Date 
 
 Date
Download
Related Blitz Reports
GL Account Analysis GL Account Analysis 11g GL Account Analysis (Distributions) 11g GL Account Analysis (Distributions) JE Finnish Payables Account by Detail- Not Supported: Reserved For Future Use JA India - Creditors Ledger- Not Supported: Reserved For Future Use ECC Payables, AP Closing, SQL2 JA Third Party Detail Trial Balance with Contra Accounts CAC Invoice Price Variance
Blitz Report™

Blitz Report™ provides multiple benefits: