PO Matching Holds Report by Buyer
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Matching Holds Report by Buyer Report
Application: Purchasing
Source: Matching Holds Report by Buyer Report (XML)
Short Name: POXPOSMH_XML
DB package: PO_POXPOSMH_XMLP_PKG
Description: Matching Holds Report by Buyer Report
Application: Purchasing
Source: Matching Holds Report by Buyer Report (XML)
Short Name: POXPOSMH_XML
DB package: PO_POXPOSMH_XMLP_PKG
Run
PO Matching Holds Report by Buyer and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT pol.po_header_id po_header_id , poh.agent_id agent_id , pov.vendor_name Vendor , poh.segment1 PO_Number , poh.currency_code C_CURRENCY , pol.line_num Line , null C_FLEX_CAT , null C_FLEX_ITEM , pol.item_revision Rev , pol.item_description Description , pol.unit_meas_lookup_code Unit , pll.shipment_num Shipment , decode(polt.order_type_lookup_code,'AMOUNT', null,pll.price_override) Unit_Price , round(pll.quantity,:P_qty_precision) Ordered , round(pll.quantity_billed,:P_qty_precision) Billed , round(pll.quantity_received,:P_qty_precision) Received , round(pll.quantity_accepted,:P_qty_precision) Accepted , alc.displayed_field Holds_Placed , api.invoice_num Invoice_Number , api.invoice_date I_Date , aid.distribution_line_number Line1 , aid.description Description1 , round(aid.quantity_invoiced,:P_qty_precision) Invoiced , aid.unit_price Invoice_Price , api.invoice_id, DECODE(pol.category_id,null,'',fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE')) C_FLEX_CAT_DISP, DECODE(pol.item_id,null,'',fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')) C_FLEX_ITEM_DISP, PO_POXPOSMH_XMLP_PKG.c_unit_price_round(decode ( polt.order_type_lookup_code , 'AMOUNT' , null , pll.price_override ), :PARENT_CURRENCY_PRECISION) C_UNIT_PRICE_ROUND, PO_POXPOSMH_XMLP_PKG.c_invoice_price_round(aid.unit_price, :PARENT_CURRENCY_PRECISION) C_INVOICE_PRICE_ROUND FROM ap_invoices api , mtl_system_items msi , mtl_categories mca , ap_invoice_distributions aid , po_distributions pod , po_line_locations pll , po_lines pol , po_vendors pov , po_headers poh , ap_lookup_codes alc , po_line_types polt , po_system_parameters psp , ap_holds aph WHERE pol.po_line_id = pll.po_line_id AND pov.vendor_id = poh.vendor_id AND poh.po_header_id = pol.po_header_id AND pol.item_id = msi.inventory_item_id(+) AND nvl(msi.organization_id,:organization_id) = :organization_id AND pol.category_id = mca.category_id AND pll.line_location_id = pod.line_location_id AND aid.po_distribution_id = pod.po_distribution_id AND pll.line_location_id = aph.line_location_id AND api.invoice_id = aph.invoice_id AND pll.shipment_type in ('STANDARD','BLANKET','SCHEDULED') AND (:P_vendor_from is NULL OR pov.vendor_name >= :P_vendor_from) AND (:P_vendor_to is NULL OR pov.vendor_name <= :P_vendor_to) AND api.invoice_date BETWEEN nvl(:P_invoice_date_from, api.invoice_date - 1) AND nvl(:P_invoice_date_to, api.invoice_date + 1) AND &P_WHERE_ITEM AND &P_WHERE_CAT AND api.invoice_id = aid.invoice_id AND aph.release_lookup_code is NULL AND (aph.hold_lookup_code = DECODE(:P_price_hold,'Y','PRICE','N','X') OR aph.hold_lookup_code = DECODE(:P_qty_ord_hold,'Y','QTY ORD','N','X') OR aph.hold_lookup_code = DECODE(:P_qty_rec_hold,'Y','QTY REC','N','X') OR aph.hold_lookup_code = DECODE(:P_quality_hold,'Y','QUALITY','N','X') OR (:P_price_hold is null AND :P_qty_ord_hold is null AND :P_qty_rec_hold is null AND :P_quality_hold is null AND aph.hold_lookup_code in ('PRICE','QTY ORD','QTY REC','QUALITY'))) AND alc.lookup_type = 'HOLD CODE' AND alc.lookup_code = aph.hold_lookup_code AND polt.line_type_id = pol.line_type_id and poh.currency_code=:Parent_Currency_code and poh.agent_id=:Parent_Employee_id /*ORDER BY decode(psp.manual_po_num_type,'NUMERIC', null,poh.segment1) , decode(psp.manual_po_num_type,'NUMERIC', to_number(poh.segment1),null) , pol.line_num , pll.shipment_num*/ ORDER BY 1 ASC,6 ASC,7 ASC,8 ASC,9 ASC,10 ASC,2 ASC,3 ASC,4 ASC,5 ASC,11 ASC,12 ASC,13 ASC,14 ASC,15 ASC,16 ASC,17 ASC,18 ASC , decode ( psp.manual_po_num_type , 'NUMERIC' , null , poh.segment1 ) , decode ( psp.manual_po_num_type , 'NUMERIC' , to_number ( poh.segment1 ) , null ) , pol.line_num , pll.shipment_num |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
Title |
|
Char | |
Buyer Name |
|
LOV Oracle | |
Vendors From |
|
LOV Oracle | |
To |
|
LOV Oracle | |
Invoice Dates From |
|
Date | |
To 2 |
|
Date | |
Categories From |
|
Char | |
To 3 |
|
Char | |
Items From |
|
Char | |
To 4 |
|
Char | |
Price Hold |
|
LOV Oracle | |
Quantity Ordered Hold |
|
LOV Oracle | |
Quantity Received Hold |
|
LOV Oracle | |
Quality Hold |
|
LOV Oracle | |
Dynamic Precision Option |
|
LOV Oracle |