PO Matching Holds Report by Buyer

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