po queries technical Queries related to Oracle Purchasing
1] TO LIST OUT ALL CANCEL REQUISITIONS:->>list My cancel Requistion:
SELECT
prh.requisition_header_id,
prh.preparer_id,
prh.segment1 "REQ NUM",
trunc(prh.creation_date),
prh.description,
prh.note_to_authorizer
from
apps.PO_REQUISITION_HEADERS_ALL PRH ,
APPS . PO_ACTION_HISTORY PAH
WHERE
action_code = 'CANCEL'
AND pah.object_type_code = 'REQUISITION'
AND pah.object_id = prh.requisition_header_id
2] TO LIST ALL INTERNAL REQUISITIONS THAT DO NOT HAVE AN ASSOCIATED INTERNAL SALES ORDER >>
SELECT
rqh.segment1 req_num,
rql.line_num,
rql.requisition_header_id,
rql.requisition_line_id,
rql.item_id,
rql.unit_meas_lookup_code,
rql.unit_price,
rql.quantity,
rql.quantity_cancelled,
rql.quantity_delivered,
rql.cancel_flag,
rql.source_type_code,
rql.source_organization_id,
rql.destination_organization_id,
rqh.transferred_to_oe_flag
from
po_requisition_lines_all rql,
po_requisition_headers_all rqh
where
rql.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_IDand RQL.SOURCE_TYPE_CODE = 'INVENTORY'
and RQL.SOURCE_ORGANIZATION_ID is not nulland not exists
(select
'existing internal order'
from
OE_ORDER_LINES_ALL LIN
where
LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_ID
and lin.source_document_type_id = 10)
ORDER BY
rqh.requisition_header_id,
rql.line_num;
3] Display what requisition and PO are linked(Relation with Requisition and PO )>>
SELECT
r.segment1 "Req Num",
p.segment1 "PO Num"
FROM
po_headers_all p,
po_distributions_all d,
po_req_distributions_all rd,
po_requisition_lines_all rl,
po_requisition_headers_all r
WHERE
p.po_header_id = d.po_header_id
AND d.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = rl.requisition_line_id
AND rl.requisition_header_id = r.requisition_header_id
4]List all Purchase Requisition without a Purchase Order that means a PR has not been autocreated to PO. (Purchase Requisition without a Purchase Order)>>
SELECT
prh.segment1 "PR NUM",
trunc(prh.creation_date) "CREATED ON",
trunc(prl.creation_date) "Line Creation Date",
prl.line_num "Seq #",
msi.segment1 "Item Num",
prl.item_description "Description",
prl.quantity "Qty",
trunc(prl.need_by_date) "Required By",
ppf1.full_name "REQUESTOR",
ppf2.agent_name "BUYER"
FROM
po.po_requisition_headers_all prh,
po.po_requisition_lines_all prl,
apps.per_people_f ppf1,
(
SELECT DISTINCT
agent_id,
agent_name
FROM
apps.po_agents_v
) ppf2,
po.po_req_distributions_all prd,
inv.mtl_system_items_b msi,
po.po_line_locations_all pll,
po.po_lines_all pl,
po.po_headers_all ph
WHERE
prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
AND ppf1.person_id = prh.preparer_id
AND prh.creation_date BETWEEN ppf1.effective_start_date AND ppf1.effective_end_date
AND ppf2.agent_id (+) = msi.buyer_id
AND msi.inventory_item_id = prl.item_id
AND msi.organization_id = prl.destination_organization_id
AND pll.line_location_id (+) = prl.line_location_id
AND pll.po_header_id = ph.po_header_id (+)
AND pll.po_line_id = pl.po_line_id (+)
AND prh.authorization_status = 'APPROVED'
AND pll.line_location_id IS NULL
AND prl.closed_code IS NULL
AND nvl(prl.cancel_flag, '') <> 'Y'
ORDER BY
1,
2
5]list all information form PR to PO as a requisition moved from different stages till converting into PR. This query capture all details related to that PR to PO.>> LIST AND ALL DATA ENTRY FROM PR TILL PO
SELECT DISTINCT
u.description "Requestor",
porh.segment1 AS "Req Number",
trunc(porh.creation_date) "Created On",
pord.last_updated_by,
porh.authorization_status "Status",
porh.description "Description",
poh.segment1 "PO Number",
trunc(poh.creation_date) "PO Creation Date",
poh.authorization_status "PO Status",
trunc(poh.approved_date) "Approved Date"
FROM
apps.po_headers_all poh,
apps.po_distributions_all pod,
apps.po_req_distributions_all pord,
apps.po_requisition_lines_all porl,
apps.po_requisition_headers_all porh,
apps.fnd_user u
WHERE
porh.requisition_header_id = porl.requisition_header_id
AND porl.requisition_line_id = pord.requisition_line_id
AND pord.distribution_id = pod.req_distribution_id (+)
AND pod.po_header_id = poh.po_header_id (+)
AND porh.created_by = u.user_id
ORDER BY
2
6] Identifying all PO's which does not have any PR's>> LIST ALL PURCHASE REQUISITION WITHOUT A PURCHASE ORDER THAT MEANS A PR HAS NOT BEEN AUTOCREATED TO PO.
SELECT
prh.segment1 "PR NUM",
trunc(prh.creation_date) "CREATED ON",
trunc(prl.creation_date) "Line Creation Date",
prl.line_num "Seq #",
msi.segment1 "Item Num",
prl.item_description "Description",
prl.quantity "Qty",
trunc(prl.need_by_date) "Required By",
ppf1.full_name "REQUESTOR",
ppf2.agent_name "BUYER"
FROM
po.po_requisition_headers_all prh,
po.po_requisition_lines_all prl,
apps.per_people_f ppf1,
(
SELECT DISTINCT
agent_id,
agent_name
FROM
apps.po_agents_v
) ppf2,
po.po_req_distributions_all prd,
inv.mtl_system_items_b msi,
po.po_line_locations_all pll,
po.po_lines_all pl,
po.po_headers_all ph
WHERE
prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
AND ppf1.person_id = prh.preparer_id
AND prh.creation_date BETWEEN ppf1.effective_start_date AND ppf1.effective_end_date
AND ppf2.agent_id (+) = msi.buyer_id
AND msi.inventory_item_id = prl.item_id
AND msi.organization_id = prl.destination_organization_id
AND pll.line_location_id (+) = prl.line_location_id
AND pll.po_header_id = ph.po_header_id (+)
AND pll.po_line_id = pl.po_line_id (+)
AND prh.authorization_status = 'APPROVED'
AND pll.line_location_id IS NULL
AND prl.closed_code IS NULL
AND nvl(prl.cancel_flag, '') <> 'Y'
ORDER BY
1,
2
7] There are different authorization_status can a requisition have.
Approved
Cancelled
In Process
Incomplete
Pre-Approved
Rejected
and you should note: When we finally close the requisition from Requisition Summary form the authorization_status of the requisition does not change. Instead it's closed_code becomes FINALLY CLOSED.
ALL INTERNAL REQUISITIONS THAT DO NOT HAVE AN ASSOCIATED INTERNAL SALES ORDER >>
SELECT
rqh.segment1 req_num,
rql.line_num,
rql.requisition_header_id,
rql.requisition_line_id,
rql.item_id,
rql.unit_meas_lookup_code,
rql.unit_price,
rql.quantity,
rql.quantity_cancelled,
rql.quantity_delivered,
rql.cancel_flag,
rql.source_type_code,
rql.source_organization_id,
rql.destination_organization_id,
rqh.transferred_to_oe_flag
FROM
po_requisition_lines_all rql,
po_requisition_headers_all rqh
WHERE
rql.requisition_header_id = rqh.requisition_header_id
AND rql.source_type_code = 'INVENTORY'
AND rql.source_organization_id IS not nulland NOT EXISTS (
SELECT
'existing internal order'
FROM
oe_order_lines_all lin
WHERE
lin.source_document_line_id = rql.requisition_line_id
AND lin.source_document_type_id = 10
) ORDER BY RQH . REQUISITION_HEADER_ID , RQL . LINE_NUM ;
3] Display what requisition and PO are linked(Relation with Requisition and PO )>>
SELECT
r.segment1 "Req Num",
p.segment1 "PO Num"
FROM
po_headers_all p,
po_distributions_all d,
po_req_distributions_all rd,
po_requisition_lines_all rl,
po_requisition_headers_all r
WHERE
p.po_header_id = d.po_header_id
AND d.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = rl.requisition_line_id
AND rl.requisition_header_id = r.requisition_header_id
4]List all Purchase Requisition without a Purchase Order that means a PR has not been autocreated to PO. (Purchase Requisition without a Purchase Order)>>
SELECT
prh.segment1 "PR NUM",
trunc(prh.creation_date) "CREATED ON",
trunc(prl.creation_date) "Line Creation Date",
prl.line_num "Seq #",
msi.segment1 "Item Num",
prl.item_description "Description",
prl.quantity "Qty",
trunc(prl.need_by_date) "Required By",
ppf1.full_name "REQUESTOR",
ppf2.agent_name "BUYER"
FROM
po.po_requisition_headers_all prh,
po.po_requisition_lines_all prl,
apps.per_people_f ppf1,
(
SELECT DISTINCT
agent_id,
agent_name
FROM
apps.po_agents_v
) ppf2,
po.po_req_distributions_all prd,
inv.mtl_system_items_b msi,
po.po_line_locations_all pll,
po.po_lines_all pl,
po.po_headers_all ph
WHERE
prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
AND ppf1.person_id = prh.preparer_id
AND prh.creation_date BETWEEN ppf1.effective_start_date AND ppf1.effective_end_date
AND ppf2.agent_id (+) = msi.buyer_id
AND msi.inventory_item_id = prl.item_id
AND msi.organization_id = prl.destination_organization_id
AND pll.line_location_id (+) = prl.line_location_id
AND pll.po_header_id = ph.po_header_id (+)
AND pll.po_line_id = pl.po_line_id (+)
AND prh.authorization_status = 'APPROVED'
AND pll.line_location_id IS NULL
AND prl.closed_code IS NULL
AND nvl(prl.cancel_flag, 'N') <> 'Y'
ORDER BY
1,
2
5]list all information form PR to PO as a requisition moved from different stages till converting into PR. This query capture all details related to that PR to PO.>> LIST AND ALL DATA ENTRY FROM PR TILL PO
SELECT DISTINCT
u.description "Requestor",
porh.segment1 AS "Req Number",
trunc(porh.creation_date) "Created On",
pord.last_updated_by,
porh.authorization_status "Status",
porh.description "Description",
poh.segment1 "PO Number",
trunc(poh.creation_date) "PO Creation Date",
poh.authorization_status "PO Status",
trunc(poh.approved_date) "Approved Date"
FROM
apps.po_headers_all poh,
apps.po_distributions_all pod,
apps.po_req_distributions_all pord,
apps.po_requisition_lines_all porl,
apps.po_requisition_headers_all porh,
apps.fnd_user u
WHERE
porh.requisition_header_id = porl.requisition_header_id
AND porl.requisition_line_id = pord.requisition_line_id
AND pord.distribution_id = pod.req_distribution_id (+)
AND pod.po_header_id = poh.po_header_id (+)
AND porh.created_by = u.user_id
ORDER BY
2
6] Identifying all PO's which does not have any PR's>> LIST ALL PURCHASE REQUISITION WITHOUT A PURCHASE ORDER THAT MEANS A PR HAS NOT BEEN AUTOCREATED TO PO.
SELECT
prh.segment1 "PR NUM",
trunc(prh.creation_date) "CREATED ON",
trunc(prl.creation_date) "Line Creation Date",
prl.line_num "Seq #",
msi.segment1 "Item Num",
prl.item_description "Description",
prl.quantity "Qty",
trunc(prl.need_by_date) "Required By",
ppf1.full_name "REQUESTOR",
ppf2.agent_name "BUYER"
FROM
po.po_requisition_headers_all prh,
po.po_requisition_lines_all prl,
apps.per_people_f ppf1,
(
SELECT DISTINCT
agent_id,
agent_name
FROM
apps.po_agents_v
) ppf2,
po.po_req_distributions_all prd,
inv.mtl_system_items_b msi,
po.po_line_locations_all pll,
po.po_lines_all pl,
po.po_headers_all ph
WHERE
prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
AND ppf1.person_id = prh.preparer_id
AND prh.creation_date BETWEEN ppf1.effective_start_date AND ppf1.effective_end_date
AND ppf2.agent_id (+) = msi.buyer_id
AND msi.inventory_item_id = prl.item_id
AND msi.organization_id = prl.destination_organization_id
AND pll.line_location_id (+) = prl.line_location_id
AND pll.po_header_id = ph.po_header_id (+)
AND pll.po_line_id = pl.po_line_id (+)
AND prh.authorization_status = 'APPROVED'
AND pll.line_location_id IS NULL
AND prl.closed_code IS NULL
AND nvl(prl.cancel_flag, 'N') <> 'Y'
ORDER BY
1,
2
7] There are different authorization_status can a requisition have.
Approved
Cancelled
In Process
Incomplete
Pre-Approved
Rejected
and you should note: When we changeCLOSEinstead it FROM Requisition Summary form the authorization_status of the requisition does not change. Instead it's closed_code becomes FINALLY CLOSED.
No comments:
Post a Comment