SELECT
prh.segment1 Req_Number
, trunc(prl.creation_date) CDate
, papf.full_name Requestor
, prl.source_subinventory Subinventory
, null C_FLEX_ITEM
, prl.item_revision Rev
, prl.item_description Description
, prl.unit_meas_lookup_code Unit
, round(prl.quantity,:P_qty_precision) Required
, round(nvl(prl.quantity_delivered,null),:P_qty_precision) Delivered
, round(sum(&C_backordered),
:P_qty_precision) Backordered
, decode(nvl(prl.cancel_flag,'N'),
'Y',
round((prl.quantity - nvl(prl.quantity_delivered,0)),:P_qty_precision),
to_number(NULL)) Cancelled
, nvl(prl.unit_price, 0) unit_price
, round(&C_ship_qty, :P_qty_precision) Shipped,
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_POXRQSIN_XMLP_PKG.c_ship_amountformula(round ( prl.quantity , :P_qty_precision ), nvl ( prl.unit_price , 0 )) C_ship_amount
FROM po_requisition_lines prl
, po_requisition_headers prh
, mtl_system_items msi
, per_all_people_f papf
&C_from
, po_system_parameters_all psp1
WHERE
&P_WHERE_QUERY
AND 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 psp1.ORG_ID = prh.ORG_ID
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 msi.inventory_item_id = prl.item_id
AND nvl(msi.organization_id,prl.source_organization_id) = prl.source_organization_id
&C_where
AND papf.person_id = prl.to_person_id
AND prh.authorization_status = nvl(:P_status,prh.authorization_status)
AND papf.business_group_id = (select nvl(max(fsp.business_group_id),0)
from financials_system_parameters fsp)
AND trunc(sysdate) between papf.effective_start_date AND papf.effective_end_date
AND decode(hr_security.view_all,'Y','TRUE',hr_security.show_record('PER_ALL_PEOPLE_F',papf.person_id,papf.person_type_id,papf.employee_number,papf.applicant_number
,papf.npw_number ))='TRUE' AND decode(hr_general.get_xbg_profile,'Y',papf.business_group_id,hr_general.get_business_group_id)=papf.business_group_id
AND &P_WHERE_ITEM
GROUP BY prh.segment1, prl.creation_date, papf.full_name,
prl.source_subinventory, 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'), prl.item_revision,
prl.item_description, prl.unit_meas_lookup_code, prl.quantity,
prl.quantity_delivered, &C_backordered, prl.cancel_flag, prl.unit_price, &C_ship_qty
UNION
SELECT
prh.segment1 Req_Number
, trunc(prl.creation_date) CDate
, papf.full_name Requestor
, prl.source_subinventory Subinventory
, null C_FLEX_ITEM
, prl.item_revision Rev
, prl.item_description Description
, prl.unit_meas_lookup_code Unit
, round(prl.quantity,:P_qty_precision) Required
, round(nvl(prl.quantity_delivered,0),:P_qty_precision) Delivered
, to_number(null) Backordered
, decode(nvl(prl.cancel_flag,'N'),
'Y',
round((prl.quantity - nvl(prl.quantity_delivered,0)),:P_qty_precision),
to_number(NULL)) Cancelled
, nvl(prl.unit_price, 0) unit_price
, to_number(NULL) Shipped,
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_POXRQSIN_XMLP_PKG.c_ship_amountformula(round ( prl.quantity , :P_qty_precision ), nvl ( prl.unit_price , 0 )) C_ship_amount
FROM po_requisition_lines prl
, po_requisition_headers prh
, mtl_system_items msi
, per_all_people_f papf
, po_system_parameters_all psp1
WHERE
&P_WHERE_QUERY
AND psp1.ORG_ID = prh.ORG_ID
AND 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') = 'N'
OR (nvl(prh.transferred_to_oe_flag,'N') = 'Y' &C_interface_where))
AND msi.inventory_item_id(+) = prl.item_id
AND nvl(msi.organization_id,prl.source_organization_id) = prl.source_organization_id
AND papf.person_id = prl.to_person_id
AND prh.authorization_status = nvl(:P_status,prh.authorization_status)
AND papf.business_group_id = (select nvl(max(fsp.business_group_id),0)
from financials_system_parameters fsp)
AND trunc(sysdate) between papf.effective_start_date AND papf.effective_end_date
AND decode(hr_security.view_all,'Y','TRUE',hr_security.show_record('PER_ALL_PEOPLE_F',papf.person_id,papf.person_type_id,papf.employee_number,papf.applicant_number
,papf.npw_number))='TRUE' AND
decode(hr_general.get_xbg_profile,'Y',papf.business_group_id,hr_general.get_business_group_id)=papf.business_group_id
AND &P_WHERE_ITEM
ORDER BY &ORDERBY_CLAUSE |
