OracleEBS: Common PO queries and Tips

Author: SanjitAnand Source: www. oracleappshub. comoracle-purchasingpo-tips-and-useful-querywww.dezai.cnblogarticle.asp? Id3041. list internal purchase orders without sales orders-usedtolistallInternalRequisi

Author: Sanjit Anand Source: http://www.oracleappshub.com/oracle-purchasing/po-tips-and-useful-query/ http://www.dezai.cn/blog/article.asp? Id = 304 1. list Internal purchase orders without sales orders-used to list all Internal Requisi

Original article: PO: Tips and useful Query
Author: Sanjit Anand
Source: http://www.oracleappshub.com/oracle-purchasing/po-tips-and-useful-query/

Http://www.dezai.cn/blog/article.asp? Id = 304

1. List internal purchase orders without sales orders

-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 = 'ventory'
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. PO associated with PR

-- 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. All canceled PR

-- 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 APIs
Where Action_code = 'cancel'
And PHA. object_type_code = 'requisition'
And APIs. object_id = prh. REQUISITION_HEADER_ID

4. Pr without PO

-- 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 (prolactin. creation_date) "Line Creation Date ",
Prolactin. line_num "Seq #",
Msi. segment1 "Item Num ",
Prolactin. item_description "Description ",
Prolactin. quantity "Qty ",
Trunc (prolactin. need_by_date) "Required ",
Ppf1.full _ name "REQUESTOR ",
Ppf2.agent _ name "BUYER"
From
Po. po_requisition_headers_all prh,
Po. po_requisition_lines_all prolactin,
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 = prolactin. requisition_header_id
And prolactin. requisition_line_id = prd. requisition_line_id
And ppf1.person _ id = prh. preparer_id
And prh. creation_date between ppf1.1_tive _ start_date and ppf1.1_tive _ end_date
And ppf2.agent _ id (+) = msi. buyer_id
And msi. inventory_item_id = prolactin. item_id
And msi. organization_id = prolactin. destination_organization_id
And pll. line_location_id (+) = prolactin. 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 = 'apache'
And pll. LINE_LOCATION_ID IS NULL
AND prolactin. CLOSED_CODE IS NULL
And nvl (prolactin. CANCEL_FLAG, 'n') <> 'y'
OrDER BY 1, 2

5. In the PR to PO process (it should be to automatically create the data in it)

-- 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. No PR is successfully created automatically.

-- 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 (prolactin. creation_date) "Line Creation Date ",
Prolactin. line_num "Seq #",
Msi. segment1 "Item Num ",
Prolactin. item_description "Description ",
Prolactin. quantity "Qty ",
Trunc (prolactin. need_by_date) "Required ",
Ppf1.full _ name "REQUESTOR ",
Ppf2.agent _ name "BUYER"
From
Po. po_requisition_headers_all prh,
Po. po_requisition_lines_all prolactin,
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 = prolactin. requisition_header_id
And prolactin. requisition_line_id = prd. requisition_line_id
And ppf1.person _ id = prh. preparer_id
And prh. creation_date between ppf1.1_tive _ start_date and ppf1.1_tive _ end_date
And ppf2.agent _ id (+) = msi. buyer_id
And msi. inventory_item_id = prolactin. item_id
And msi. organization_id = prolactin. destination_organization_id
And pll. line_location_id (+) = prolactin. 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 = 'apache'
And pll. LINE_LOCATION_ID IS NULL
AND prolactin. CLOSED_CODE IS NULL
And nvl (prolactin. CANCEL_FLAG, 'n') <> 'y'
OrDER BY 1, 2

7. Associate tables of PR and PO

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

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.

All you need to do is associate the REQ_DISTRIBUTION_ID of PO_DISTRIBUTIONS_ALL with the DISTRIBUTION_ID in PO_REQ_DISTRIBUTIONS_ALL to check whether the PR has a corresponding PO.

8. uncompleted PO

-- 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 ')

9. List and PO With there approval, invoice and payment details

-- List and PO With there approval, invoice and payment details
Select
A.org _ id "org id ",
E. SEGMENT1 "vendor num ",
E. vendor_name "supplier name ",
UPPER (e. vendor_type_lookup_code) "vendor type ",
F. vendor_site_code "vendor site code ",
F. ADDRESS_LINE1 "ADDRESS ",
F. city "CITY ",
F. country "COUNTRY ",
To_char (trunc (d. CREATION_DATE) "PO Date ",
D. segment1 "po num ",
D. type_lookup_code "PO Type ",
C. quantity_ordered "QTY orDERED ",
C. quantity_cancelled "qty cancelled ",
G. item_id "item id ",
G. item_description "item description ",
G. unit_price "unit price ",
(NVL (c. quantity_ordered, 0)-NVL (c. quantity_cancelled, 0) * NVL (g. unit_price, 0) "PO Line Amount ",
(Select
Decode (ph. approved_FLAG, 'y', 'apache ')
From po. po_headers_all ph
Where ph. po_header_ID = d. po_header_id) "PO Approved ?",
A. invoice_type_lookup_code "invoice type ",
A. invoice_amount "invoice amount ",
To_char (trunc (a. INVOICE_DATE) "invoice date ",
A. invoice_num "invoice number ",
(Select
Decode (x. MATCH_STATUS_FLAG, 'A', 'apache ')
From ap. ap_invoice_distributions_all x
Where x. INVOICE_DISTRIBUTION_ID = B. invoice_distribution_id) "Invoice Approved ?",
A. amount_paid,
H. amount,
H. check_id,
H. invoice_payment_id "Payment Id ",
I. check_number "Cheque Number ",
To_char (trunc (I. check_DATE) "PAYMENT DATE"

From ap. AP_INVOICES_ALL,
AP. AP_INVOICE_DISTRIBUTIONS_ALL B,
PO. PO_DISTRIBUTIONS_ALL C,
PO. PO_HEADERS_ALL D,
PO. PO_VENDORS E,
PO. PO_VENDOR_SITES_ALL F,
PO. PO_LINES_ALL G,
AP. AP_INVOICE_PAYMENTS_ALL H,
AP. AP_CHECKS_ALL I
Where a. invoice_id = B. invoice_id
And B. po_distribution_id = c. po_distribution_id (+)
And c. po_header_id = d. po_header_id (+)
And e. vendor_id (+) = d. VENDOR_ID
And f. vendor_site_id (+) = d. vendor_site_id
And d. po_header_id = g. po_header_id
And c. po_line_id = g. po_line_id
And a. invoice_id = h. invoice_id
And h. check_id = I. check_id
And f. vendor_site_id = I. vendor_site_id
And c. PO_HEADER_ID is not null
And a. payment_status_flag = 'y'
And d. type_lookup_code! = 'Blancet'