SELECT prh.segment1 Req_Number
, prl.creation_date CDate
, prl.line_num Line
, prl.item_id Item_ID
, prl.source_organization_id Org_ID
, null C_FLEX_ITEM
, null C_FLEX_CAT
, prl.item_description Item_Description
, papf.full_name Requestor
, ood.organization_code Organization
, prl.source_subinventory Subinventory
, (prl.quantity - nvl(prl.quantity_cancelled,0)) Ordered_Quantity
, round(SUM(&C_backordered),:P_qty_precision) Backordered
, prl.unit_meas_lookup_code Unit
, round(SUM(&C_ship_quantity), :P_qty_precision) Shipped
, decode(psp1.manual_req_num_type,'NUMERIC',
null,prh.segment1)
, decode(psp1.manual_req_num_type,'NUMERIC',
to_number(prh.segment1),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,
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
FROM po_requisition_headers prh
, po_requisition_lines prl
, mtl_system_items msi
, per_all_people_f papf
&C_from
, mtl_categories mca
, org_organization_definitions ood
, po_system_parameters psp1
WHERE trunc(prl.creation_date)
BETWEEN nvl(:P_creation_date_from,trunc(prl.creation_date)-1)
AND nvl(:P_creation_date_to,trunc(prl.creation_date)+1)
AND papf.full_name = nvl(:P_requestor,papf.full_name)
AND nvl(prl.source_subinventory,'-1')
BETWEEN nvl(:P_subinventory_from, nvl(prl.source_subinventory,'-1'))
AND nvl(:P_subinventory_to,nvl(prl.source_subinventory,'-1'))
AND prl.requisition_header_id = prh.requisition_header_id
AND prl.source_type_code = 'INVENTORY'
AND nvl(prh.transferred_to_oe_flag,'N') = 'Y'
AND prl.source_organization_id = ood.organization_id
AND papf.person_id = prl.to_person_id
AND msi.inventory_item_id = prl.item_id
AND msi.organization_id = prl.source_organization_id
AND mca.category_id = prl.category_id
AND trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
AND decode(hr_general.get_xbg_profile,
'Y',papf.business_group_id,
hr_general.get_business_group_id)=papf.business_group_id
&C_where
GROUP BY prh.segment1
, prl.creation_date
, prl.line_num
, prl.item_id
, prl.source_organization_id
, 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')
, 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')
, prl.item_description
, papf.full_name
, ood.organization_code
, prl.source_subinventory
, &C_backordered
, prl.unit_meas_lookup_code
, &C_ship_quantity
, psp1.manual_req_num_type
, (prl.quantity - nvl(prl.quantity_cancelled,0))
ORDER BY decode(psp1.manual_req_num_type,'NUMERIC',
null,prh.segment1)
, decode(psp1.manual_req_num_type,'NUMERIC',
to_number(prh.segment1), null)
, prl.line_num |
