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
Ask a question