AP Invoice History - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Invoice History Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXINHIS_XML
DB package: AP_APXINHIS_XMLP_PKG
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
Ledger Id
 
Number
To Invoice Date
 
Date
From Invoice Date
 
Date
Voucher Number To
 
Number
Voucher Number From
 
Number
Sequence Name
 
LOV Oracle
Invoice Number To
 
Invoice Number From
 
Prepayments Only
 
LOV Oracle
Supplier Site
 
LOV Oracle
Supplier Name
 
LOV Oracle