程序代码
---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;下载本文
