Reports2017-11-18T12:27:27+00:00

AP Matching Detail - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Matching Detail Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXMTDTR_XML
DB package: AP_APXMTDTR_XMLP_PKG
SELECT  AI.invoice_id     C_INVOICE_ID,
        AI.invoice_currency_code C_INV_CURRENCY_CODE,
        AI.invoice_num    C_INVOICE_NUM ,
        AI.vendor_id      C_VENDOR_ID , 
        PV.vendor_name    C_VENDOR_NAME , 
        PV.segment1       C_VENDOR_NUM,
        PVS.vendor_site_code C_VENDOR_SITE_CODE,
        ALC1.displayed_field   C_INVOICE_TYPE,
        AI.invoice_date   C_INVOICE_DATE ,
        nvl(AI.voucher_num,AI.doc_sequence_value)    C_VOUCHER_NUM,          AI.exchange_rate_type  C_EXCHANGE_RATE_TYPE,
        AI.exchange_rate  C_EXCHANGE_RATE,
        ALC.displayed_field   C_LINE_TYPE,
        AIL.line_number  C_LINE_NUMBER,
        to_char(AIL.amount, Fnd_Currency.get_format_mask(AI.invoice_currency_code, 17))
                                           C_LINE_AMOUNT_FMT,
        to_char(AIL.creation_date,'DD-MON-YYYY')     C_LINE_CREATION_DATE,
        AIL.quantity_invoiced   C_LINE_QUANTITY_INVOICED,
        to_char(AIL.unit_price, Fnd_Currency.get_format_mask(AI.invoice_currency_code, 17))
                                           C_LINE_UNIT_PRICE_FMT,
        AIL.unit_meas_lookup_code  C_LINE_UOM,
       AP_INVOICE_LINES_UTILITY_PKG.Get_Posting_Status(AI.invoice_id, AIL.line_number) 
                                         C_LINE_ACCOUNTED,
        PH.po_header_id     C_PO_HEADER_ID,
        PH.segment1         C_PO_NUM,
        PR.release_num      C_PO_RELEASE_NUM,
        PL.line_num         C_PO_LINE_NUM,
        PLL.shipment_num    C_PO_SHIPMENT_NUM,
        PD.distribution_num    C_PO_DISTRIBUTION_NUM,
        DECODE(AIL.po_distribution_id, null, PLL.quantity,
                    PD.quantity_ordered) C_QUANTITY_ORDERED,
        PL.unit_price  C_PO_UNIT_PRICE,
        PL.unit_meas_lookup_code  C_PO_UOM,
        RSH.receipt_num   C_RECEIPT_NUM,
        RSL.line_num      C_RECEIPT_LINE_NUM,
        RSL.quantity_received  C_QUANTITY_RECEIVED,
        RSL.unit_of_measure   C_REC_UOM, 
	AP_APXMTDTR_XMLP_PKG.cf_exchange_rate_type_desc_inv(AI.exchange_rate_type) CF_EXCHANGE_RATE_TYPE_DESC_INV
FROM  ap_invoices AI,
      ap_invoice_lines AIL,
      ap_lookup_codes ALC,
      ap_lookup_codes ALC1,
      po_vendor_sites PVS,
      po_vendors PV,
      po_distributions PD,
      po_releases PR,
      po_headers PH,
      po_lines PL,
      po_line_locations PLL,      
      rcv_transactions RTXN,
      rcv_shipment_headers RSH,
      rcv_shipment_lines RSL
  WHERE AI.invoice_id = :p_invoice_id
  AND AI.invoice_id = AIL.invoice_id      
  AND AI.vendor_id  = PV.vendor_id
  AND AI.vendor_site_id = PVS.vendor_site_id
  AND ALC.lookup_type (+) = 'INVOICE LINE TYPE'
  AND ALC.lookup_code (+) = AIL.line_type_lookup_code
  AND ALC1.lookup_type (+) = 'INVOICE TYPE'
  AND ALC1.lookup_code (+) = AI.invoice_type_lookup_code  
  AND AIL.po_distribution_id = PD.po_distribution_id (+)
  AND AIL.po_header_id = PH.po_header_id (+)  
  AND AIL.po_line_location_id = PLL.line_location_id (+)
  AND AIL.po_line_id = PL.po_line_id (+)
  AND AIL.po_release_id = PR.po_release_id (+)
  AND AIL.rcv_transaction_id = RTXN.transaction_id (+)
  AND RTXN.shipment_line_id = RSL.shipment_line_id (+)
  AND RSL.shipment_header_id = RSH.shipment_header_id (+)
ORDER BY  AI.invoice_num, AIL.line_number, 
          PH.segment1, PL.line_num, PLL.shipment_num, PD.distribution_num,
          RSH.receipt_num, RSL.line_num
Parameter Name SQL text Validation
Ledger Id
 
Receipt Number
 
LOV Oracle
Release Number
 
LOV Oracle
PO Number
 
LOV Oracle
Invoice Number
 
LOV Oracle
By continuing to use the site, you agree to the use of cookies. Accept