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 |