AP Matching Hold Detail - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Matching Hold Detail Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXINMHD_XML
DB package: AP_APXINMHD_XMLP_PKG
SELECT
       M.INVOICE_ID                   C_M_INVOICE_ID, 
       P.SEGMENT1                      C_PO_NUM,
       PL.LINE_LOCATION_ID   C_LINE_LOCATION_ID, 
      L.LINE_NUM                        C_LINE_NUM, 
       H.LOCATION_CODE         C_LOCATION_CODE, 
       L.ITEM_ID                           C_ITEM_ID, 
       ''                                            C_ITEM_NUM,
       L.ITEM_DESCRIPTION     C_DESCRIPTION, 
       L.UNIT_MEAS_LOOKUP_CODE 
                                                     C_UNIT_MEAS, 
       PL.PRICE_OVERRIDE                                                                                                                                C_PRINT_PO_UNIT_PRICE,    
ROUND( 
      NVL(SUM(D.AMOUNT) / 
               DECODE(SUM(
NVL(d.quantity_invoiced,0)
), 0, NULL,
            SUM(
NVL(d.quantity_invoiced,0)
)
              ),0) ,5)                                  C_INVOICE_UNIT_PRICE,
     DECODE(PLT.MATCHING_BASIS, 'AMOUNT', PL.AMOUNT, 
                    PL.QUANTITY) C_ORDERED,
     DECODE(PLT.MATCHING_BASIS, 'AMOUNT',
       NVL(PL.AMOUNT_RECEIVED, 0), 
          NVL(PL.QUANTITY_RECEIVED, 0)) C_RECEIVED,
     DECODE(PLT.MATCHING_BASIS, 'AMOUNT', 
      NVL(PL.AMOUNT_BILLED, 0), 
        NVL(PL.QUANTITY_BILLED, 0)) C_BILLED,
     DECODE(PLT.MATCHING_BASIS, 'AMOUNT', 'N/A', 
        NVL(PL.QUANTITY_ACCEPTED, 0)) C_ACCEPTED, 
       DECODE(M.RELEASE_LOOKUP_CODE, NULL, 
       M.HOLD_LOOKUP_CODE, M.RELEASE_LOOKUP_CODE)
                                                    C_MATCH_STATUS,
       ALC.DISPLAYED_FIELD                                                                                                                         C_NLS_MATCH_STATUS,
       M.LAST_UPDATE_DATE        C_MATCH_DATE, 
       NVL(PL.CANCEL_FLAG, 'N')  C_CANCEL_FLAG,
       P.CURRENCY_CODE        C_PO_CURRENCY_CODE,
      ATC.NAME                         C_PO_TAX_CODE,
       D.VAT_CODE             C_INVOICE_TAX_CODE,
       INV.INVOICE_CURRENCY_CODE
                         C_INVOICE_CURRENCY_CODE,
       R.RELEASE_NUM          C_PO_RELEASE_NUM,
       PLT.MATCHING_BASIS C_MATCHING_BASIS, 
	AP_APXINMHD_XMLP_PKG.c_accepted_fmtformula(PLT.MATCHING_BASIS, DECODE ( PLT.MATCHING_BASIS , 'AMOUNT' , 'N/A' , NVL ( PL.QUANTITY_ACCEPTED , 0 ) )) C_ACCEPTED_FMT
FROM   PO_HEADERS P, 
       PO_LINES L, 
       HR_LOCATIONS H,
       AP_INVOICE_DISTRIBUTIONS D, 
       AP_HOLDS M, 
       PO_DISTRIBUTIONS_AP_V PD, 
       PO_LINE_LOCATIONS PL,
       AP_INVOICES INV, 
       PO_RELEASES R, 
       AP_LOOKUP_CODES ALC,
       AP_TAX_CODES  ATC ,
       PO_LINE_TYPES PLT
  WHERE ( M.INVOICE_ID = D.INVOICE_ID AND M.INVOICE_ID = INV.INVOICE_ID AND M.LINE_LOCATION_ID = PL.LINE_LOCATION_ID AND L.PO_LINE_ID = PL.PO_LINE_ID AND L.TAX_CODE_ID = ATC.TAX_ID (+) AND PL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID AND PD.PO_DISTRIBUTION_ID = D.PO_DISTRIBUTION_ID AND P.PO_HEADER_ID = L.PO_HEADER_ID AND H.LOCATION_ID = PL.SHIP_TO_LOCATION_ID AND R.PO_RELEASE_ID (+) = PL.PO_RELEASE_ID AND ALC.LOOKUP_TYPE = 'HOLD CODE' AND ALC.LOOKUP_CODE = DECODE ( M.RELEASE_LOOKUP_CODE , NULL , M.HOLD_LOOKUP_CODE , M.RELEASE_LOOKUP_CODE ) AND ( D.LINE_TYPE_LOOKUP_CODE = 'ITEM' ) ) AND ( :C_INVOICE_ID = M.INVOICE_ID)
GROUP BY   D.INVOICE_ID, 
          PL.LINE_LOCATION_ID, 
          P.segment1, L.line_num, l.item_id, '',            H.location_code,L.item_description,                  
          L.unit_meas_lookup_code,                          PL.price_override, 
          decode(PLT.MATCHING_BASIS, 'AMOUNT', PL.AMOUNT, PL.QUANTITY), 
          decode(PLT.MATCHING_BASIS, 'AMOUNT',
           NVL(PL.AMOUNT_RECEIVED, 0), NVL(PL.QUANTITY_RECEIVED, 0)),
          decode(M.release_lookup_code, null, 
          m.hold_lookup_code,
          m.release_lookup_code),
          alc.displayed_field,
          M.last_update_date, 
          decode(PLT.MATCHING_BASIS, 'AMOUNT', 
              NVL(PL.AMOUNT_BILLED, 0), NVL(PL.QUANTITY_BILLED, 0)), 
          decode(PLT.MATCHING_BASIS, 'AMOUNT', 'N/A',
             NVL(PL.QUANTITY_ACCEPTED, 0)),
          PL.cancel_flag,
          p.currency_code, ATC.name, d.vat_code,
          inv.invoice_currency_code,
          r.release_num,
         M.INVOICE_ID,
         PLT.MATCHING_BASIS,
         DECODE(PLT.MATCHING_BASIS, 'AMOUNT',
                 PL.AMOUNT, PL.QUANTITY), 
         DECODE(PLT.MATCHING_BASIS, 'AMOUNT',
           NVL(PL.AMOUNT_RECEIVED, 0), NVL(PL.QUANTITY_RECEIVED, 0)), 
         DECODE(PLT.MATCHING_BASIS, 'AMOUNT', 
           NVL(PL.AMOUNT_BILLED, 0), NVL(PL.QUANTITY_BILLED, 0)), 
         DECODE(PLT.MATCHING_BASIS, 'AMOUNT', 'N/A',
           NVL(PL.QUANTITY_ACCEPTED, 0)), 
         DECODE(M.RELEASE_LOOKUP_CODE, NULL, 
            M.HOLD_LOOKUP_CODE, M.RELEASE_LOOKUP_CODE)
ORDER BY UPPER(P.segment1),  L.line_num
Parameter Name SQL text Validation
Ledger
 
Matching Hold Detail Report
 
LOV Oracle
Active Period End Date
 
Date
Active Period Start Date
 
Date
Supplier Name (skip for All)
 
LOV Oracle
Matching Hold Status (skip for All)
 
LOV Oracle