AP Payables Open Interface Import - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Payables Open Interface Import (XML) - Not Supported: Reserved For Future Use
Short Name: APXIIMPT_XML
DB package: AP_APXIIMPT_XMLP_PKG
SELECT v.vendor_name,
       i.invoice_num,
       i.invoice_currency_code,
       i.invoice_amount,
       substr(i.invoice_num, 5, (instr(i.invoice_num,'-', 1, 2) - 5)) corrected_inv_num,
       NULL corrected_line_numer,
       c.description,
       c.displayed_field,
	&CF_Invoice_Amount3 CF_invoice_amount3
FROM ap_ppa_invoices_gt i,
       ap_invoices_interface ii,
       ap_interface_rejections r,
       po_vendors v,
       ap_lookup_codes c
 WHERE i.invoice_id = r.parent_id
   AND i.instr_status_flag = 'N'
   AND r.parent_table = 'AP_PPA_INVOICES_GT'
   AND i.instruction_id = ii.invoice_id
   AND ii.status = 'REJECTED'
   AND ii.source = :P_SOURCE_T
   AND ((:p_group_id_t IS NULL) OR (ii.group_id = :p_group_id_t))
   AND v.vendor_id = i.vendor_id
   AND ((i.org_id IS NOT NULL
         AND mo_global.check_access(i.org_id) = 'Y')
          OR i.org_id IS NULL)
   AND c.lookup_type = 'REJECT CODE'
   AND r.reject_lookup_code = c.lookup_code
UNION ALL
SELECT v.vendor_name,
       i.invoice_num,
       i.invoice_currency_code,
       l.amount,
       AP_RETRO_PRICING_UTIL_PKG.get_invoice_num(
                  l.corrected_inv_id)  corrected_inv_num,
       l.corrected_line_number,
       c.description,
       c.displayed_field,
	&CF_Invoice_Amount3 CF_invoice_amount3
FROM ap_ppa_invoices_gt i,
       ap_ppa_invoice_lines_gt l,
       ap_lookup_codes c,
       po_vendors v,
       ap_interface_rejections r,
       ap_invoices_interface ii
 WHERE l.invoice_line_id = r.parent_id
   AND r.parent_table = 'AP_PPA_INVOICE_LINES_GT'
   AND i.invoice_id = l.invoice_id
   AND i.instr_status_flag = 'N'
   AND i.instruction_id = ii.invoice_id
   AND ii.status = 'REJECTED'
   AND ii.source = :P_SOURCE_T
   AND ((  :p_group_id_t IS NULL ) OR ( ii.group_id =  :p_group_id_t))
   AND v.vendor_id = i.vendor_id
   AND mo_global.check_access(i.org_id) = 'Y'
   AND c.lookup_type = 'REJECT CODE'
   AND r.reject_lookup_code = c.lookup_code
UNION ALL
SELECT v.vendor_name,
       ai.invoice_num,
       NULL invoice_currency_code,
       NULL invoice_amount,
       NULL corrected_inv_num,
       NULL corrected_line_number,
       c.description,
       c.displayed_field,
	&CF_Invoice_Amount3 CF_invoice_amount3
FROM ap_invoices_interface ii,
       ap_invoices_all ai,
       ap_interface_rejections r,
       po_vendors v,
       ap_lookup_codes c
 WHERE ii.invoice_id = r.parent_id
   AND r.parent_table = 'AP_PPA_INSTRUCTIONS_GT'
   AND ii.status = 'REJECTED'
   AND ai.vendor_id = ii.vendor_id
   AND ai.invoice_id IN (SELECT distinct L.invoice_id
                           FROM ap_invoice_lines_interface IL,
                                ap_invoice_lines_all L
                          WHERE IL.invoice_id = ii.invoice_id
                            AND IL.po_line_location_id = L.po_line_location_id
                            AND L.org_id = ai.org_id
                            AND L.invoice_id = ai.invoice_id
                            AND L.match_type IN ('ITEM_TO_PO', 'ITEM_TO_RECEIPT',
                                                 'QTY_CORRECTION', 'PRICE_CORRECTION')
                            AND L.discarded_flag <> 'Y'
                            AND L.cancelled_flag <> 'Y'
                            AND (NVL(L.generate_dists, 'Y') <> 'D'
                                 OR EXISTS (SELECT 'Unapproved matched dist'
                                              FROM  ap_invoice_distributions_all D
                                             WHERE  D.invoice_id = L.invoice_id
                                               AND  D.invoice_line_number = L.line_number
                                               AND  NVL(D.match_status_flag, 'X') NOT IN ('A', 'T'))
                                 OR EXISTS (SELECT 'Holds other than Price Hold'
                                              FROM  ap_holds_all H
                                             WHERE  H.invoice_id = L.invoice_id
                                               AND  H.release_lookup_code is null
                                               AND  H.hold_lookup_code <> 'PRICE')
                                 )
                          )
   AND ii.source = :P_SOURCE_T
   AND ((:p_group_id_t IS NULL) OR (ii.group_id = :p_group_id_t))
   AND v.vendor_id = ii.vendor_id
   AND ((ii.org_id IS NOT NULL
         AND mo_global.check_access(ii.org_id) = 'Y')
          OR ii.org_id IS NULL)
   AND c.lookup_type = 'REJECT CODE'
   AND r.reject_lookup_code = c.lookup_code
ORDER BY 1 ASC,2 ASC
Parameter Name SQL text Validation
Login ID
 
Number
User ID
 
Number
Commit Batch Size
 
Number
Summarize Report
 
LOV Oracle
Purge
 
LOV Oracle
GL Date
 
Date
Hold Reason
 
Hold Name
 
LOV Oracle
Batch Name
 
Group
 
Source
 
LOV Oracle
Operating Unit
 
LOV Oracle
Ask a question