PO Contract Status

➜ Back to Library
Description
Categories: BI Publisher
Application: Purchasing
Source: Contract Status Report (XML)
Short Name: POXCONST_XML
DB package: PO_POXCONST_XMLP_PKG
Run PO Contract Status and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT poh.segment1                                          PO_Number
,      pol.line_num                                          Line
,      null                                           C_FLEX_CAT
,      null                                          C_FLEX_ITEM
,      pol.item_description                                 Item_Description
,      pol.unit_meas_lookup_code                             Unit
,      pol.quantity              Quantity
,      decode(polt.order_type_lookup_code,'AMOUNT', 
           null,(decode(poh.currency_code,:c_currency,
                   nvl(pol.unit_price,0),
            (nvl(pol.unit_price,0) * nvl(poh.rate,1) / nvl(:Contract_Rate1,1) ))  ))          Unit_Price
,      sum(decode(poh.currency_code,:c_currency,nvl(pll.price_override,0) *
        (nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0)),
         nvl(pll.price_override,0) * 
                (nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0)) * 
                nvl(poh.rate,1) / nvl(:Contract_Rate1,1)) )  C_AMOUNT_1
,      pll.shipment_num                                               Shipment
,      lot.location_code                                                Location
,      plc1.displayed_field ||', ' || plc2.displayed_field || 
       decode(pll.cancel_flag, 'Y', ', Cancelled','')    Status_concat
,      pll.quantity               Ordered
,      pll.quantity_received    Received
,      pll.quantity_billed                                   Billed
,      pll.po_line_id 
,      poh.po_header_id
,      pol.po_line_id
,      pol.contract_num
--,      pol.contract_id CONTRACT_ID
,      pol.contract_id CONTRACT_ID1
,      decode(psp.manual_po_num_type,'NUMERIC',
                                     null, poh.segment1)
,      decode(psp.manual_po_num_type,'NUMERIC',
                                     to_number(poh.segment1), null)
,       plc_sta.displayed_field || ' ' ||
        decode(poh.cancel_flag,
                      'Y',plc_can.displayed_field,NULL) || ' ' ||
        decode(nvl(poh.closed_code,'OPEN'),
                      'OPEN',NULL,plc_clo.displayed_field) || ' ' || 
        decode(poh.frozen_flag,
                      'Y',plc_fro.displayed_field,NULL) || ' ' ||  
        decode(poh.user_hold_flag,
                      'Y',plc_hld.displayed_field,NULL)    Status
,        fc.precision                     c_fnd_precision
,       hou.name    Purchasing_Org, 
	PO_POXCONST_XMLP_PKG.c_total_po_amount_round(:C_TOTAL_PO_AMOUNT, :C_PRECISION) C_TOTAL_PO_AMOUNT_ROUND, 
	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, 
	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_POXCONST_XMLP_PKG.c_unit_price_roundformula(decode ( polt.order_type_lookup_code , 'AMOUNT' , null , ( decode ( poh.currency_code , :c_currency , nvl ( pol.unit_price , 0 ) , ( nvl ( pol.unit_price , 0 ) * nvl ( poh.rate , 1 ) / nvl ( :Contract_Rate1 , 1 ) ) ) ) ), :C_EXTENDED_PRECISION) C_UNIT_PRICE_ROUND, 
	PO_POXCONST_XMLP_PKG.c_amount_1_round(sum ( decode ( poh.currency_code , :c_currency , nvl ( pll.price_override , 0 ) * ( nvl ( pll.quantity , 0 ) - nvl ( pll.quantity_cancelled , 0 ) ) , nvl ( pll.price_override , 0 ) * ( nvl ( pll.quantity , 0 ) - nvl ( pll.quantity_cancelled , 0 ) ) * nvl ( poh.rate , 1 ) / nvl ( :Contract_Rate1 , 1 ) ) ), :C_PRECISION) C_AMOUNT_1_ROUND
FROM   mtl_categories                mca
,      mtl_system_items              msi
,      financials_system_parameters  fsp
,      hr_locations_all_tl            lot
,      po_line_locations_all             pll
,      po_lines_all                      pol
,      po_headers_all                   poh
,      po_lookup_codes               plc1
,      po_lookup_codes               plc2
,      po_system_parameters          psp
,      po_line_types                          polt
,      po_lookup_codes          plc_sta
,      po_lookup_codes          plc_can
,      po_lookup_codes          plc_clo
,      po_lookup_codes          plc_fro
,      po_lookup_codes          plc_hld
,      fnd_currencies	         fc
,      hr_all_organization_units hou
WHERE pol.contract_id = :Contract_ID
AND    poh.currency_code = fc.currency_code
AND    pol.category_id       = mca.category_id
AND    nvl(msi.organization_id,:c_organization_id) = :c_organization_id
AND    pol.item_id           = msi.inventory_item_id(+)
AND    lot.location_id (+)      = pll.ship_to_location_id
AND LOT.LANGUAGE(+) = USERENV('LANG')
AND    pll.po_line_id        = pol.po_line_id
AND    pll.shipment_type in ('STANDARD')
AND    pol.po_header_id      = poh.po_header_id
AND    hou.organization_id(+) = poh.org_id
AND    plc1.lookup_type      = 'AUTHORIZATION STATUS'
AND    plc1.lookup_code      = decode(pll.approved_flag, 'Y', 'APPROVED',  'INCOMPLETE')
AND    plc2.lookup_type      = 'DOCUMENT STATE'
AND    plc2.lookup_code      = nvl(pll.closed_code, 'OPEN')
AND    polt.line_type_id = pol.line_type_id
AND    plc_sta.lookup_code      =
            decode(poh.approved_flag,
                          'R',poh.approved_flag,
                           nvl(poh.authorization_status,'INCOMPLETE'))
AND    plc_sta.lookup_type  in ('PO APPROVAL','DOCUMENT STATE')
AND    plc_can.lookup_code = 'CANCELLED'
AND    plc_can.lookup_type  = 'DOCUMENT STATE'
AND    plc_clo.lookup_code  = nvl(poh.closed_code,'OPEN')
AND    plc_clo.lookup_type =  'DOCUMENT STATE'
AND    plc_fro.lookup_code  = 'FROZEN'
AND    plc_fro.lookup_type   = 'DOCUMENT STATE'
AND    plc_hld.lookup_code = 'ON HOLD'
AND    plc_hld.lookup_type =  'DOCUMENT STATE' 
 and pol.contract_id=:Contract_id
GROUP BY poh.segment1
,        pol.line_num
,        &P_FLEX_CAT
,        &P_FLEX_ITEM
,        pol.item_description
,        pol.unit_meas_lookup_code
,        pol.quantity
,        decode(polt.order_type_lookup_code,'AMOUNT',
                     null,(decode(poh.currency_code,:c_currency,nvl(pol.unit_price,0),
               (nvl(pol.unit_price,0) * nvl(poh.rate,1) / nvl(:Contract_Rate1,1)))  ))
,        pll.shipment_num
,        lot.location_code
,        plc1.displayed_field
,        plc2.displayed_field
,        pll.cancel_flag
,        pll.quantity
,        pll.quantity_received
,        pll.quantity_billed
,        pll.po_line_id
,        poh.po_header_id
,        pol.po_line_id 
,        pol.contract_num
,        pol.contract_id
,        psp.manual_po_num_type
,        plc_sta.displayed_field || ' ' ||
        decode(poh.cancel_flag,
                      'Y',plc_can.displayed_field,NULL) || ' ' ||
        decode(nvl(poh.closed_code,'OPEN'),
                      'OPEN',NULL,plc_clo.displayed_field) || ' ' || 
        decode(poh.frozen_flag,
                      'Y',plc_fro.displayed_field,NULL) || ' ' ||  
        decode(poh.user_hold_flag,
                      'Y',plc_hld.displayed_field,NULL) 
,        fc.precision 
,       hou.name  
,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'),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')
ORDER BY &orderby_clause
Parameter Name SQL text Validation
Title 
 
 Char
PO Numbers From 
 
 LOV Oracle
To 
 
 LOV Oracle
To 
 
 LOV Oracle
Vendors From 
 
 LOV Oracle
Download
Related Blitz Reports
PO Blanket and Planned PO Status PO Receiving Transactions Pipeline PO Receiving Transactions Register PO Overshipments PO Purchase Requisition Status PO Substitute Receipts PO Overdue Vendor Shipments ECC Procurement, Requisitions PO Headers and Lines
Blitz Report™

Blitz Report™ provides multiple benefits: