SELECT po_req.company,
po_req.organization_code,
po_req.LINE_NUM,
po_req.PO_RELEASE_ID,
po_req.po_line_id,
po_req.organization_name,
po_req.requisition_number,
po_req.ER_Number,
po_req.segment1,
po_req.requisition_date,
po_req.requisition_type,
po_req.requester,
po_req.person_id,
po_req.org_id,
po_req.requisition_header_id,
po_req.inventory_item_id,
po_req.authorization_status,
po_req.approved_date,
po_req.item,
po_req.item_description,
po_req.quantity_required,
po_req.quantity_delivered,
po_req.unit_price,
po_req.buyer_name,
po_req.buyer_id,
po_req.PO_UNIT_PRICE,--------------------
po_req.QUOTE_PRICE,
po_req.PO_QUANTITY,--------------------
po_req.PO_ITEM_TOTAL_VALUE,-----------------------
po_req.PO_APPROVAL_DATE,-----------------
po_req.status,
po_req.req_need_by_date,
po_req.full_lead_time,
po_req.vendor_name,
po_req.order_number,
po_req.order_date,
po_req.order_need_by_date,
po_req.receipt_num,
po_req.shipment_header_id,
po_req.receipt_date,
po_req.days_from_pr_to_po,
po_req.days_from_pr_to_grn
FROM (( SELECT hou.NAME company, ood.organization_code,pl.LINE_NUM,pll.PO_RELEASE_ID,pl.po_line_id,
ood.organization_name, prh.segment1 requisition_number,
prh.attribute10 ER_Number,
nvl(pap.segment1,' ') segment1,
ppf.full_name requester, ppf.person_id, prh.org_id,
prh.requisition_header_id, msik.inventory_item_id,
TRUNC (prh.creation_date) requisition_date,
podt.type_name requisition_type, prh.authorization_status,
TRUNC (prh.approved_date) approved_date,
msik.concatenated_segments item,
prl.item_description item_description,
SUM (prl.quantity) quantity_required,
SUM (prl.quantity_delivered) quantity_delivered,
NVL (prl.unit_price, 0) unit_price,
ppf1.full_name buyer_name,
nvl(ppf1.person_id,1) buyer_id,
NVL(PL.UNIT_PRICE,0) PO_UNIT_PRICE,--------------------
NVL(PL.ATTRIBUTE2,0) QUOTE_PRICE,
NVL (PL.QUANTITY,0) PO_QUANTITY,--------------------
NVL(pl.unit_price*PL.QUANTITY,0) PO_ITEM_TOTAL_VALUE,-----------------------
TRUNC(PH.APPROVED_DATE) PO_APPROVAL_DATE,------------------
PH.CLOSED_CODE STATUS,----------------
TRUNC (prl.need_by_date) req_need_by_date,
msik.full_lead_time, pve.vendor_name,
ph.segment1 order_number,
TRUNC (ph.creation_date) order_date,
TRUNC (pll.need_by_date) order_need_by_date,
NVL (rcv.receipt_num, inl.receipt_num) receipt_num,
NVL (rcv.shipment_header_id, inl.shipment_header_id) shipment_header_id,
NVL (rcv.receipt_date, inl.receipt_date) receipt_date,
TRUNC ( (TRUNC (ph.creation_date))
- (TRUNC (prh.creation_date))
) days_from_pr_to_po,
TRUNC ( (NVL (rcv.receipt_date, inl.receipt_date))
- (TRUNC (prh.creation_date))
) days_from_pr_to_grn
FROM po_requisition_headers_all prh,
po_document_types_all_tl podt,
po_requisition_lines_all prl,
po_req_distributions_all prd,
pa_projects_all pap,
per_all_people_f ppf,
per_all_people_f ppf1,
mtl_system_items_kfv msik,
hr_operating_units hou,
po_line_locations_all pll,
po_lines_all pl,
po_headers_all ph,
po_vendors pve,
org_organization_definitions ood,
(SELECT rsl.po_line_location_id, rsl.shipment_line_id,
rsh.receipt_num,rsh.shipment_header_id,
NVL2 (rsh.receipt_num,
TRUNC (rsh.creation_date),
NULL
) receipt_date
FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl
WHERE rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.lcm_shipment_line_id IS NULL
GROUP BY rsl.po_line_location_id,
rsl.shipment_line_id,
rsh.receipt_num,
rsh.shipment_header_id,
NVL2 (rsh.receipt_num,
TRUNC (rsh.creation_date),
NULL
)) rcv,
(SELECT isl.ship_line_source_id,
isl.ship_to_organization_id, rsl.shipment_line_id,
rsh.receipt_num,rsh.shipment_header_id,
NVL2 (rsh.receipt_num,
TRUNC (rsh.creation_date),
NULL
) receipt_date
FROM inl_ship_headers_all ish,
inl_ship_lines_all isl,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl
WHERE ish.ship_header_id = isl.ship_header_id
AND isl.ship_line_id =
(SELECT MAX (sl1.ship_line_id)
FROM inl_ship_lines_all sl1
WHERE sl1.ship_header_id = isl.ship_header_id
AND sl1.ship_line_group_id =
isl.ship_line_group_id
AND sl1.ship_line_num = isl.ship_line_num)
AND NVL (isl.parent_ship_line_id, isl.ship_line_id) =
rsl.lcm_shipment_line_id(+)
AND rsl.shipment_header_id = rsh.shipment_header_id(+)
GROUP BY isl.ship_line_source_id,
isl.ship_to_organization_id,
rsl.shipment_line_id,
rsh.shipment_header_id,
rsh.receipt_num,
NVL2 (rsh.receipt_num,
TRUNC (rsh.creation_date),
NULL
)) inl
WHERE prh.requisition_header_id = prl.requisition_header_id
AND podt.document_type_code = 'REQUISITION'
AND podt.document_subtype = prh.type_lookup_code
AND podt.org_id = prh.org_id
and prl.requisition_line_id=prd.requisition_line_id
and prd.project_id=pap.project_id(+)
AND pve.vendor_id = ph.vendor_id
AND prl.to_person_id = ppf.person_id
AND ph.agent_id=ppf1.person_id
AND prl.item_id = msik.inventory_item_id(+)
AND prh.org_id = hou.organization_id
AND prl.destination_organization_id = msik.organization_id(+)
--AND prl.destination_organization_id = msik.organization_id
AND prl.line_location_id = pll.line_location_id
AND pll.po_line_id = pl.po_line_id
AND pl.po_header_id = ph.po_header_id
AND prl.destination_organization_id = ood.organization_id
AND pll.line_location_id = rcv.po_line_location_id(+)
AND pll.line_location_id = inl.ship_line_source_id(+)
AND podt.language='US'
AND pll.ship_to_organization_id = inl.ship_to_organization_id(+)
--AND prh.segment1 = '21400049'
GROUP BY hou.NAME,
ood.organization_code,
pl.LINE_NUM,pll.PO_RELEASE_ID,pl.po_line_id,
ood.organization_name,
prh.segment1,
prh.attribute10 ,
-- pap.segment1,
nvl(pap.segment1,' '),
ppf.full_name,
ppf.person_id,
prh.org_id,
prh.requisition_header_id,
ppf1.full_name,
nvl(ppf1.person_id,1),
-- ppf1.person_id,
msik.inventory_item_id,
TRUNC (prh.creation_date),
TRUNC(PH.APPROVED_DATE),
podt.type_name,
prh.authorization_status,
TRUNC (prh.approved_date),
msik.concatenated_segments,
prl.item_description,
TRUNC (prl.need_by_date),
msik.full_lead_time,
ph.segment1,
pve.vendor_name,
PH.CLOSED_CODE,
NVL (prl.unit_price, 0),
NVL(PL.ATTRIBUTE2,0),
TRUNC(PH.APPROVED_DATE),
NVL(PL.UNIT_PRICE,0),
TRUNC (ph.creation_date),
NVL (PL.QUANTITY,0),
NVL(pl.unit_price*PL.QUANTITY,0),
TRUNC (pll.need_by_date),
NVL (rcv.receipt_num, inl.receipt_num),
NVL (rcv.shipment_header_id, inl.shipment_header_id) ,
NVL (rcv.receipt_date, inl.receipt_date)
)
UNION
(SELECT hou.NAME company, ood.organization_code,NULL LINE_NUM,NULL PO_RELEASE_ID,NULL po_line_id,
ood.organization_name, prh.segment1 requisition_number,
prh.attribute10 ER_Number,
nvl(pap.segment1,' ') segment1,--pap.segment1,
ppf.full_name requester, ppf.person_id, prh.org_id,
prh.requisition_header_id, msik.inventory_item_id,
TRUNC (prh.creation_date) requisition_date,
podt.type_name requisition_type, prh.authorization_status,
TRUNC (prh.approved_date) approved_date,
msik.concatenated_segments item,
prl.item_description item_description,
SUM (prl.quantity) quantity_required,
SUM (prl.quantity_delivered) quantity_delivered,
NVL (prl.unit_price, 0) unit_price,
null buyer_name,
NULL BUYER_ID,
-- null person_id,
NULL PO_UNIT_PRICE,--------------------
NULL QUOTE_PRICE,
NULL PO_QUANTITY,--------------------
NULL PO_ITEM_TOTAL_VALUE,-----------------------
NULL PO_APPROVAL_DATE,-----------------
NULL STATUS,
TRUNC (prl.need_by_date) req_need_by_date,
msik.full_lead_time, NULL vendor_name,
TO_CHAR (ooh.order_number) order_number,
TRUNC (ooh.ordered_date) order_date,
TRUNC (ool.request_date) order_need_by_date,
wnd.NAME receipt_num,
null shipment_header_id,
wnd.ultimate_dropoff_date receipt_date,
TRUNC ( (TRUNC (ooh.ordered_date))
- (TRUNC (prh.creation_date))
) days_from_pr_to_po,
TRUNC
( (TRUNC (wnd.ultimate_dropoff_date))
- (TRUNC (prh.creation_date))
) days_from_pr_to_grn
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd,
pa_projects_all pap,
per_all_people_f ppf,
po_document_types_all_tl podt,
mtl_system_items_kfv msik,
hr_operating_units hou,
org_organization_definitions ood,
oe_order_headers_all ooh,
oe_order_lines_all ool,
wsh_delivery_details wdd,
-- po_vendors pve,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
WHERE prh.requisition_header_id = prl.requisition_header_id
and prl.requisition_line_id=prd.requisition_line_id
and prd.project_id=pap.project_id(+)
AND podt.document_type_code = 'REQUISITION'
AND podt.document_subtype = prh.type_lookup_code
AND podt.org_id = prh.org_id
-- and ph.vendor_id=pve.vendor_id
AND prl.to_person_id = ppf.person_id
AND prl.item_id = msik.inventory_item_id(+)
AND prl.destination_organization_id = msik.organization_id(+)
AND prl.destination_organization_id = ood.organization_id
AND prh.org_id = hou.organization_id
AND prl.requisition_header_id = ooh.source_document_id
AND prh.segment1 = ooh.orig_sys_document_ref
AND ooh.header_id = ool.header_id
AND ool.orig_sys_line_ref = TO_CHAR (prl.line_num)
AND ool.header_id = wdd.source_header_id(+)
AND ool.line_id = wdd.source_line_id(+)
AND wdd.delivery_detail_id = wda.delivery_detail_id(+)
AND podt.language='US'
AND TO_CHAR (wda.delivery_id) = TO_CHAR (wnd.delivery_id(+))
--AND prh.segment1 = '21400004'
GROUP BY hou.NAME,
ood.organization_code,
ood.organization_name,
prh.segment1,
prh.attribute10,
nvl(pap.segment1,' '),--pap.segment1,
-- PVE.vendor_name,
ppf.full_name,
ppf.person_id,
prh.org_id,
prh.requisition_header_id,
msik.inventory_item_id,
TRUNC (prh.creation_date),
podt.type_name,
NVL (prl.unit_price, 0),
prh.authorization_status,
TRUNC (prh.approved_date),
msik.concatenated_segments,
prl.item_description,
TRUNC (prl.need_by_date),
msik.full_lead_time,
ooh.order_number,
TRUNC (ooh.ordered_date),
TRUNC (ool.request_date),
wnd.NAME,
wnd.ultimate_dropoff_date)
UNION
(SELECT hou.NAME company, ood.organization_code, NULL LINE_NUM,NULL PO_RELEASE_ID,NULL po_line_id,
ood.organization_name, prh.segment1 requisition_number,
prh.attribute10 ER_Number,
nvl(pap.segment1,' ') segment1,--pap.segment1,
ppf.full_name requester, ppf.person_id, prh.org_id,
prh.requisition_header_id, msik.inventory_item_id,
TRUNC (prh.creation_date) requisition_date,
podt.type_name requisition_type, prh.authorization_status,
TRUNC (prh.approved_date) approved_date,
msik.concatenated_segments item,
prl.item_description item_description,
SUM (prl.quantity) quantity_required,
SUM (prl.quantity_delivered) quantity_delivered,
NVL (prl.unit_price, 0) unit_price,
null buyer_name,
NULL BUYER_ID,
-- null person_id,
NULL PO_UNIT_PRICE,--------------------
NULL QUOTE_PRICE,
NULL PO_QUANTITY,--------------------
NULL PO_ITEM_TOTAL_VALUE,-----------------------
NULL PO_APPROVAL_DATE,-----------------
NULL STATUS,
TRUNC (prl.need_by_date) req_need_by_date,
msik.full_lead_time, NULL vendor_name, NULL order_number,
NULL order_date, NULL order_need_by_date, NULL receipt_num,
null shipment_header_id,
NULL receipt_date, NULL days_from_pr_to_po,
NULL days_from_pr_to_grn
FROM po_requisition_headers_all prh,
po_document_types_all_tl podt,
po_requisition_lines_all prl,
po_req_distributions_all prd,
pa_projects_all pap,
per_all_people_f ppf,
mtl_system_items_kfv msik,
hr_operating_units hou,
org_organization_definitions ood
WHERE prh.requisition_header_id = prl.requisition_header_id
and prl.requisition_line_id=prd.requisition_line_id
and prd.project_id=pap.project_id(+)
AND podt.document_type_code = 'REQUISITION'
AND podt.document_subtype = prh.type_lookup_code
AND podt.org_id = prh.org_id
AND prl.to_person_id = ppf.person_id
AND prl.item_id = msik.inventory_item_id(+)
AND prl.destination_organization_id = msik.organization_id(+)
AND prh.org_id = hou.organization_id
AND podt.language='US'
AND prl.destination_organization_id = ood.organization_id
AND prh.authorization_status = 'APPROVED'
--AND prh.segment1 = '12400031'
AND NOT EXISTS (
SELECT 1
FROM po_line_locations_all pll
WHERE pll.line_location_id =
prl.line_location_id)
AND NOT EXISTS (SELECT 1
FROM oe_order_headers_all ooh
WHERE ooh.orig_sys_document_ref = prh.segment1)
GROUP BY hou.NAME,
ood.organization_code,
ood.organization_name,
prh.attribute10 ,
prh.segment1,
nvl(pap.segment1,' '),--pap.segment1,
ppf.full_name,
ppf.person_id,
prh.org_id,
NVL (prl.unit_price, 0),
prh.requisition_header_id,
msik.inventory_item_id,
TRUNC (prh.creation_date),
podt.type_name,
prh.authorization_status,
TRUNC (prh.approved_date),
msik.concatenated_segments,
prl.item_description,
TRUNC (prl.need_by_date),
msik.full_lead_time)) po_req
WHERE po_req.org_id = :p_org_id
AND po_req.requisition_date BETWEEN NVL(:P_FROM_DATE,po_req.requisition_date) AND nvl(:P_TO_DATE,po_req.requisition_date)
--and po_req.segment1 = nvl(:p_project_num,po_req.segment1)
-- AND po_req.item between NVL (:p_from_item_id, po_req.item) and NVL (:p_to_item_id, po_req.item)
AND po_req.organization_code = NVL (:p_organization_code, po_req.organization_code)
-- AND po_req.requisition_type= NVL (:p_req_TYPE, po_req.requisition_type)
-- AND po_req.requisition_header_id = NVL (:p_header_id, po_req.requisition_header_id)
AND po_req.requisition_number between NVL (:p_from_req_no, po_req.requisition_number) and NVL (:p_to_req_no, po_req.requisition_number)
-- AND po_req.authorization_status = NVL (:p_authorization_status, po_req.authorization_status)
AND po_req.person_id = NVL (:p_person_id, po_req.person_id)
-- or po_req.buyer_id = NVL (:p_buyer_id, po_req.buyer_id))
and po_req.segment1 = nvl(nvl(:p_project_num,po_req.segment1),' ')
-- and po_req.buyer_id = nvl(NVL (:p_buyer_id, po_req.buyer_id),1)
--AND po_req.item = '560001-000028'
-- and
order by 7,4
No comments:
Post a Comment