PO Matching Holds Report by Buyer

Description
Categories: BI Publisher
Application: Purchasing
Source: Matching Holds Report by Buyer Report (XML)
Short Name: POXPOSMH_XML
DB package: PO_POXPOSMH_XMLP_PKG
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
Title
 
Buyer Name
 
LOV Oracle
Vendors From
 
LOV Oracle
To
 
LOV Oracle
Invoice Dates From
 
Date
To
 
Date
Categories From
 
To
 
Items From
 
To
 
Price Hold
 
LOV Oracle
Quantity Ordered Hold
 
LOV Oracle
Quantity Received Hold
 
LOV Oracle
Quality Hold
 
LOV Oracle
Dynamic Precision Option
 
LOV Oracle
P_STRUCT_NUM
 
Number