PO Contract Status

Description
Categories: BI Publisher
Application: Purchasing
Source: Contract Status Report (XML)
Short Name: POXCONST_XML
DB package: PO_POXCONST_XMLP_PKG
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
 
PO Numbers From
 
LOV Oracle
To
 
LOV Oracle
Vendors From
 
LOV Oracle
To
 
LOV Oracle