Stage 1: PO Creation :
PO_HEADERS_ALL
select po_header_id from po_headers_all where segment1 =; select * from po_headers_all where po_header_id =; select * from po_lines_all where po_header_id =; select * from po_line_locations_all where po_header_id =; select * from po_distributions_all where po_header_id =; SELECT * FROM po_releases_all WHERE po_header_id =;
Stage 2: Once PO is received data is moved to respective receving tables and inventory tables
select * from rcv_shipment_headers where shipment_header_id in
(select shipment_header_id from rcv_shipment_lines
where po_header_id =); select * from rcv_shipment_lines where po_header_id =; select * from rcv_transactions where po_header_id =; SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN
(select transaction_id from rcv_transactions
where po_header_id =); select * from rcv_receiving_sub_ledger where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =); select * from rcv_sub_ledger_details
where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =); select * from mtl_material_transactions where transaction_source_id =; select * from mtl_transaction_accounts where transaction_id in ( select transaction_id from mtl_material_transactions where transaction_source_id = =);
Stage 3: Invoicing details
select * from ap_invoice_distributions_all where po_distribution_id in ( select po_distribution_id from po_distributions_all where po_header_id =); select * from ap_invoices_all where invoice_id in
(select invoice_id from ap_invoice_distributions_all where po_distribution_id in
( select po_distribution_id from po_distributions_all where po_header_id =));
Stage 4 : Many Time there is tie up with Project related PO
select * from pa_expenditure_items_all peia where peia.orig_transaction_reference in
( select to_char(transaction_id) from mtl_material_transactions
where transaction_source_id =);
Stage 5 : General Ledger
SELECT * FROM gl_bc_packets WHERE reference2 IN (''); SELECT *
FROM GL_INTERFACE GLI
WHERE user_je_source_name ='Purchasing'
AND gl_sl_link_table ='RSL'
AND reference21='PO'
AND EXISTS
( SELECT 1
FROM rcv_receiving_sub_ledger RRSL
WHERE GLI.reference22 =RRSL.reference2
AND GLI.reference23 =RRSL.reference3
AND GLI.reference24 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id)); SELECT *
FROM gl_import_references GLIR
WHERE reference_1='PO'
AND gl_sl_link_table ='RSL'
AND EXISTS
( SELECT 1
FROM rcv_receiving_sub_ledger RRSL
WHERE GLIR.reference_2 =RRSL.reference2
AND GLIR.reference_3 =RRSL.reference3
AND GLIR.reference_4 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id =)
The consultant life while working at client site is not easy during ERP transformation projects, many times it's required to provide some adhoc query for extract to ends users, therefore it is important to have a cheat sheet so that such untimely things can be easily handled in sort span. Hope these query and tips useful to all Inhouse IT personals who is part of Implementation Project team.
1. You need to list out all Internal Requisitions that do not have an associated Internal Sales order.
---used 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_ID
and RQL.SOURCE_TYPE_CODE = 'INVENTORY'
and RQL.SOURCE_ORGANIZATION_ID is not null
and 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;
2. You want to display what requisition and PO are linked(Relation with Requisition and PO )
-----Relation with Requistion 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
3. You need 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
4. You need to list those PR which havn't auto created to PO.(Purchase Requisition without a Purchase Order)
-----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
5. You need to 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. Relation between Requisition and PO tables
Here is link:
PO_DISTRIBUTIONS_ALL =>PO_HEADER_ID, REQ_DISTRIBUTION_ID
PO_HEADERS_ALL=>PO_HEADER_ID, SEGMENT1
PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID, REQUISITION_LINE_ID
PO_REQUISITION_LINES_ALL =>REQUISITION_LINE_ID)
PO_REQUISITION_HEADERS_ALL =>REQUISITION_HEADER_ID, REQUISITION_LINE_ID, SEGMENT1
PO_HEADERS_ALL=>PO_HEADER_ID, SEGMENT1
PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID, REQUISITION_LINE_ID
PO_REQUISITION_LINES_ALL =>REQUISITION_LINE_ID)
PO_REQUISITION_HEADERS_ALL =>REQUISITION_HEADER_ID, REQUISITION_LINE_ID, SEGMENT1
What you have to make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) and PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a PO for the req.
8.You need to find table which hold PO Approval path...
These two table keeps the data:
- PO_APPROVAL_LIST_HEADERS
- PO_APPROVAL_LIST_LINES
9. List all the PO's with there approval ,invoice and Payment Details
10.You need to know the link to GL_JE_LINES table for purchasing accrual and budgetary control actions..
The budgetary (encumbrance) and accrual actions in the purchasing module generate records that will be imported into GL for the corresponding accrual and budgetary journals.
The following reference fields are used to capture and keep PO information in the GL_JE_LINES table.
These reference fields are populated when the Journal source (JE_SOURCE in GL_JE_HEADERS) is
Purchasing.
Purchasing.
Budgetary Records from PO (These include reservations, reversals and cancellations):
- REFERENCE_1- Source (PO or REQ)
- REFERENCE_2- PO Header ID or Requisition Header ID (from po_headers_all.po_header_id or
po_requisition_headers_all.requisition_header_id) - REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id or
po_req_distributions_all.distribution_id) - REFERENCE_4- Purchase Order or Requisition number (from po_headers_all.segment1 or
po_requisition_headers_all.segment1) - REFERENCE_5- (Autocreated Purchase Orders only) Backing requisition number (from po_requisition_headers_all.segment1)
Accrual Records from PO:
- REFERENCE_1- Source (PO)
- REFERENCE_2- PO Header ID (from po_headers_all.po_header_id)
- REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id
- REFERENCE_4- Purchase Order number (from po_headers_all.segment1)
- REFERENCE_5- (ON LINE ACCRUALS ONLY) Receiving Transaction ID (from rcv_receiving_sub_ledger.rcv_transaction_id)
Take a note for Period end accruals, the REFERENCE_5 column is not used.
11. List me all open PO's
12.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'.
----- List all open PO'S
select
h.segment1 "PO NUM",
h.authorization_status "STATUS",
l.line_num "SEQ NUM",
ll.line_location_id,
d.po_distribution_id ,
h.type_lookup_code "TYPE"
from
po.po_headers_all h,
po.po_lines_all l,
po.po_line_locations_all ll,
po.po_distributions_all d
where h.po_header_id = l.po_header_id
and ll.po_line_id = l.po_Line_id
and ll.line_location_id = d.line_location_id
and h.closed_date is null
and h.type_lookup_code not in ('QUOTATION')
13. A standard Quotations one that you can tie back to a PO.
Navigate to RFQ -> Auto create -> enter a PO and reference it back.
Navigate to RFQ -> Auto create -> enter a PO and reference it back.
14. I want to debug for a PO , where should I start.
Thats is possible, your PO get stuck somewhere, so what you have to do is to analyze which stage it stucked.Get po_header_id first and run each query and then analyze the data.For better understanding this is splited into 5 major stages.
Purchase Requisition details
SELECT prh.segment1 "Req #", prh.creation_date, prh.created_by, poh.segment1 "PO #", ppx.full_name "Requestor Name", prh.description "Req Description", prh.authorization_status, prh.note_to_authorizer, prh.type_lookup_code, prl.line_num, prl.line_type_id, prl.item_description, prl.unit_meas_lookup_code, prl.unit_price, prl.quantity, prl.quantity_delivered, prl.need_by_date, prl.note_to_agent, prl.currency_code, prl.rate_type, prl.rate_date, prl.quantity_cancelled, prl.cancel_date, prl.cancel_reason FROM po_requisition_headers_all prh, po_requisition_lines_all prl, po_req_distributions_all prd, per_people_x ppx, po_headers_all poh, po_distributions_all pda WHERE prh.requisition_header_id = prl.requisition_header_id AND ppx.person_id = prh.preparer_id AND prh.type_lookup_code = 'PURCHASE' AND prd.requisition_line_id = prl.requisition_line_id AND pda.req_distribution_id = prd.distribution_id AND pda.po_header_id = poh.po_header_id
Internal Requisition details
SELECT prh.segment1 "Req #", prh.creation_date, prh.created_by, poh.segment1 "PO #", ppx.full_name "Requestor Name",
prh.description "Req Description", prh.authorization_status, prh.note_to_authorizer, prl.line_num,
prl.line_type_id, prl.source_type_code, prl.item_description, prl.unit_meas_lookup_code, prl.unit_price, prl.quantity, prl.quantity_delivered,
prl.need_by_date, prl.note_to_agent, prl.currency_code, prl.rate_type, prl.rate_date, prl.quantity_cancelled, prl.cancel_date,
prl.cancel_reason
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd,
per_people_x ppx,
po_headers_all poh,
po_distributions_all pda
WHERE prh.requisition_header_id = prl.requisition_header_id
AND ppx.person_id = prh.preparer_id
AND prh.type_lookup_code = 'INTERNAL'
AND prd.requisition_line_id = prl.requisition_line_id
AND pda.req_distribution_id (+) = prd.distribution_id
AND pda.po_header_id = poh.po_header_id (+)
Purchase Order details
-- Purchase Orders for non inventory items like service
SELECT
ph.SEGMENT1 po_num
, ph.CREATION_DATE
, hou.name "Operating Unit"
, ppx.full_name "Buyer Name"
, ph.type_lookup_code "PO Type"
, plc.displayed_field "PO Status"
, ph.COMMENTS
, pl.line_num
, plt.order_type_lookup_code "Line Type"
, NULL "Item Code"
, pl.item_description
, pl.unit_meas_lookup_code "UOM"
, pl.base_unit_price
, pl.unit_price
, pl.quantity
, ood.organization_code "Shipment Org Code"
, ood.organization_name "Shipment Org Name"
, pv.vendor_name supplier
, pvs.vendor_site_code
, (pl.unit_price * pl.quantity) "Line Amount"
, prh.segment1 req_num
, prh.type_lookup_code req_method
, ppx1.full_name "Requisition requestor"
FROM po_headers_all ph
, po_lines_all pl
, po_distributions_all pda
, po_vendors pv
, po_vendor_sites_all pvs
, po_distributions_all pd
, po_req_distributions_all prd
, po_requisition_lines_all prl
, po_requisition_headers_all prh
, hr_operating_units hou
, per_people_x ppx
, po_line_types_b plt
, org_organization_definitions ood
, per_people_x ppx1
, po_lookup_codes plc
WHERE
1=1
AND TO_CHAR(ph.creation_date, 'YYYY') IN (2010, 2011)
AND ph.vendor_id = pv.vendor_id
AND ph.po_header_id = pl.po_header_id
AND ph.vendor_site_id = pvs.vendor_site_id
AND ph.po_header_id = pd.po_header_id
and pl.po_line_id = pd.po_line_id
AND pd.req_distribution_id = prd.distribution_id (+)
AND prd.requisition_line_id = prl.requisition_line_id (+)
AND prl.requisition_header_id = prh.requisition_header_id (+)
and hou.organization_id = ph.org_id
and ph.agent_id = ppx.person_id
and pda.po_header_id = ph.po_header_id
and pda.po_line_id = pl.po_line_id
and pl.line_type_id = plt.line_type_id
and ood.organization_id = pda.destination_organization_id
and ppx1.person_id (+) = prh.preparer_id
and plc.lookup_type = 'DOCUMENT STATE'
and plc.LOOKUP_CODE = ph.closed_code
and pl.item_id is null
UNION
-- Purchase Orders for inventory items
SELECT
ph.SEGMENT1 po_num
, ph.CREATION_DATE
, hou.name "Operating Unit"
, ppx.full_name "Buyer Name"
, ph.type_lookup_code "PO Type"
, plc.displayed_field "PO Status"
, ph.COMMENTS
, pl.line_num
, plt.order_type_lookup_code "Line Type"
, msi.segment1 "Item Code"
, pl.item_description
, pl.unit_meas_lookup_code "UOM"
, pl.base_unit_price
, pl.unit_price
, pl.quantity
, ood.organization_code "Shipment Org Code"
, ood.organization_name "Shipment Org Name"
, pv.vendor_name supplier
, pvs.vendor_site_code
, (pl.unit_price * pl.quantity) "Line Amount"
, prh.segment1 req_num
, prh.type_lookup_code req_method
, ppx1.full_name "Requisition requestor"
FROM po_headers_all ph
, po_lines_all pl
, po_distributions_all pda
, po_vendors pv
, po_vendor_sites_all pvs
, po_distributions_all pd
, po_req_distributions_all prd
, po_requisition_lines_all prl
, po_requisition_headers_all prh
, hr_operating_units hou
, per_people_x ppx
, mtl_system_items_b msi
, po_line_types_b plt
, org_organization_definitions ood
, per_people_x ppx1
, po_lookup_codes plc
WHERE
1=1
AND TO_CHAR(ph.creation_date, 'YYYY') IN (2010, 2011)
AND ph.vendor_id = pv.vendor_id
AND ph.po_header_id = pl.po_header_id
AND ph.vendor_site_id = pvs.vendor_site_id
AND ph.po_header_id = pd.po_header_id
and pl.po_line_id = pd.po_line_id
AND pd.req_distribution_id = prd.distribution_id (+)
AND prd.requisition_line_id = prl.requisition_line_id (+)
AND prl.requisition_header_id = prh.requisition_header_id (+)
and hou.organization_id = ph.org_id
and ph.agent_id = ppx.person_id
and pda.po_header_id = ph.po_header_id
and pda.po_line_id = pl.po_line_id
and pl.line_type_id = plt.line_type_id
and ood.organization_id = pda.destination_organization_id
and ppx1.person_id (+) = prh.preparer_id
and pda.destination_organization_id = msi.organization_id (+)
and msi.inventory_item_id = nvl(pl.item_id, msi.inventory_item_id)-- OR pl.item_id is null)
and plc.lookup_type = 'DOCUMENT STATE'
and plc.LOOKUP_CODE = ph.closed_code
and pl.item_id is not null
No comments:
Post a Comment