oracle 删除po查询,Oracle EBS:PO 常用的查询及Tips

原文:PO: Tips and useful Query

作者:Sanjit Anand

来源:http://www.oracleappshub.com/oracle-purchasing/po-tips-and-useful-query/

1.列出没有销售订单的内部采购订单

---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.关联PR的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.所有取消的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 pah

where Action_code='CANCEL'

and pah.object_type_code='REQUISITION'

and pah.object_id=prh.REQUISITION_HEADER_ID

4.没有PO的Pr

-----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.在PR转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.没有自动创建PO成功的PR

-----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.PR与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.

你要做的就是将PO_DISTRIBUTIONS_ALL的REQ_DISTRIBUTION_ID与PO_REQ_DISTRIBUTIONS_ALL中的DISTRIBUTION_ID关联，查看看PR是否有对应的PO

8.未结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