AP Matching Hold Detail Report- Not Supported: Reserved For Future Use
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Matching Hold Detail Report
Application: Payables
Source: Matching Hold Detail Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXINMHD_XML
DB package: AP_APXINMHD_XMLP_PKG
Description: Matching Hold Detail Report
Application: Payables
Source: Matching Hold Detail Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXINMHD_XML
DB package: AP_APXINMHD_XMLP_PKG
Run
AP Matching Hold Detail Report- Not Supported: Reserved For Future Use and other Oracle EBS reports with Blitz Report™ on our demo environment
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 | |
---|---|---|---|
Operating Unit |
|
LOV | |
Matching Hold Status (skip for All) |
|
LOV Oracle | |
Supplier Name (skip for All) |
|
LOV Oracle | |
Active Period Start Date |
|
Date | |
Active Period End Date |
|
Date | |
Matching Hold Detail Report |
|
LOV Oracle |