OracleEBS: Common PO queries and Tips

Source: Internet
Author: User
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'

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.