PO 常用的查询及Tips

程序代码

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

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

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

.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 != 'BLANKET'

===========================================================

PO部分常用table 总结 PO01A(采购单)sql 语句

po_requisition_headers_all --请购单头

po_requisition_headers_all --请购单行

po_vendors --供应商

po_vendor_sites_all --供应商地址

po_vendor_contacts --供应商联系人

po_requisition_headers_v --请购单view

po_headers_v --采购单view

PO_REQUISITION_LINES_INQ_V --请购单行

比如,对于一个请购单, 请购买的地址不会在请购单的头,而在行,原因是:

一个请购单可以有多个行,每个行可以由不同单位购买,比较灵活.

PO01A报表的语句:(请购单,目前只有部门代号那个栏位没有找到)

select

prh.segment1 prhsegment1,

upper(to_char(prh.creation_date,'dd-mon-yy')) creation_date,

papf.full_name,

hl1.location_code devliver_to,

msi.segment1,

prl.line_num,

prl.item_description,

prl.unit_meas_lookup_code,

prl.quantity,

prl.need_by_date,

hout.name,

prl.*

from po_requisition_headers_all prh,

po_requisition_lines_all prl,

per_all_people_f papf,

mtl_system_items msi,

hr_locations_all_tl hl1,

hr_all_organization_units_tl hout

where prh.preparer_id = papf.person_id

and prh.requisition_header_id=prl.requisition_header_id

and prh.segment1=10600014

and prh.preparer_id = papf.person_id

and msi.inventory_item_id=prl.item_id

and msi.organization_id = prl.destination_organization_id

AND hl1.location_id = prl.deliver_to_location_id

and hout.organization_id=prl.org_id

=========================================

SQL 失效一揽子PO协议

1.查询一揽子PO：

这里要注意的是：不同的付款条件,币种，税率，都会可能产生一张新的一揽子PO协议

复制内容到剪贴板程序代码 程序代码

select pla.PO_LINE_ID,

pla.PO_HEADER_ID,

pla.ITEM_ID,

msib.SEGMENT1 ITEM_CODE,

pla.UNIT_PRICE,

pla.ITEM_DESCRIPTION,

pla.START_DATE,

pla.EXPIRATION_DATE,

pla.CANCEL_FLAG,

pla.CLOSED_CODE,

pha.PO_HEADER_ID,

pha.VENDOR_ID,

pha.VENDOR_SITE_ID,

pha.TERMS_ID,

pla.TAX_CODE_ID

from PO_LINES_ALL pla, PO_HEADERS_ALL pha,MTL_SYSTEM_ITEMS_B msib

where nvl(pla.closed_code, 'OPEN') != 'FINALLY CLOSED'

and nvl(pla.CANCEL_FLAG, 'N') = 'N'

and pla.PO_HEADER_ID=pha.PO_HEADER_ID

and nvl(pha.closed_code, 'OPEN') != 'FINALLY CLOSED'

and nvl(pha.CANCEL_FLAG,'N')='N'

and pha.ENABLED_FLAG='Y'

and pha.TYPE_LOOKUP_CODE = 'BLANKET'

and msib.INVENTORY_ITEM_ID=pla.ITEM_ID

2.通过查找对应的ID，失效对应的价格，我这里好像少了付款条件跟币种，税率这几个条件，

复制内容到

剪贴板程序代码 程序代码

procedure SetPriceExpireDate(P_ITEM_CODE IN VARCHAR2,

P_VENDOR_CODE IN VARCHAR2,

P_VENDOR_SITE_CODE IN VARCHAR2,

P_ORG_ID IN VARCHAR2,

v_retval out number) is

begin

Update PO_LINES_ALL pla

set pla.EXPIRATION_DATE = sysdate

where pla.ITEM_ID = GetItemID(P_ITEM_CODE, P_ORG_ID)

and pla.ORG_ID = P_ORG_ID

and nvl(pla.cancel_flag, 'N') = 'N'

and nvl(closed_code, 'OPEN') != 'FINALLY CLOSED'

and exists

(select *

from PO_HEADERS_ALL pha

where pha.PO_HEADER_ID = pla.PO_HEADER_ID

and pha.TYPE_LOOKUP_CODE = 'BLANKET'

and pha.VENDOR_ID = GetVendorID(P_VENDOR_CODE)

and pha.VENDOR_SITE_ID =

GetVendorSiteID(P_VENDOR_SITE_CODE, P_ORG_ID));

v_retval := 1;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

v_retval := 0;

end SetPriceExpireDate;

3.辅助的几个函数

复制内容到剪贴板程序代码 程序代码

Function GetVendorID(P_Vendor_Code IN VARCHAR2) RETURN NUMBER as

v_vendor_id number;

begin

select pv.VENDOR_ID

into v_vendor_id

from po_vendors pv

where pv.SEGMENT1 = P_Vendor_Code;

return v_vendor_id;

end;

Function GetVendorSiteID(P_Vendor_SITE_CODE IN VARCHAR2,

P_ORG_ID IN NUMBER) RETURN NUMBER as

v_vendor_site_id number;

begin

select pvsa.VENDOR_SITE_ID

into v_vendor_site_id

from PO_VENDOR_SITES_ALL pvsa

where pvsa.VENDOR_SITE_CODE = P_Vendor_SITE_CODE

and pvsa.ORG_ID = P_ORG_ID;

return v_vendor_site_id;

end;

FUNCTION GetItemID(P_ITEM_CODE IN VARCHAR2, P_ORG_ID IN NUMBER)

RETURN NUMBER as

v_item_id number;

begin

select msib.INVENTORY_ITEM_ID

into v_item_id

from MTL_SYSTEM_ITEMS_B msib

where msib.SEGMENT1 = P_ITEM_CODE

AND msib.ORGANIZATION_ID = P_ORG_ID;

return v_item_id;

end;