原文链接:https://blog.csdn.net/samt007/article/details/60877398
有时候,需要实现强制按照PR行来自动创建PO。
举个例子:假设2张PR,同一个物料,同一个需求日期等等的,在合并采购订单的时候,希望是2个PO行。
按照标准功能的处理逻辑,如果是同一个物料+需求日期(配置PO_NEED_BY_GROUPING=’Y’),在PR自动创建PO的时候,会自动合并为一行。然后发运行是2行。
如何实现?
实现的逻辑也不难,最近通过研究自动创建的包:PO_INTERFACE_S.create_documents
发现在PR自动创建PO的时候,如果po_requisition_lines_all.SUPPLIER_REF_NUMBER给的值不一样,则在自动创建的时候,PO会自动分组。这样子就可以比较简单地实现了让PO拆行的效果。
下面的代码注意这里:
AND ((pli.supplier_ref_number IS NULL AND l_supplier_ref_number IS NULL)
OR (pli.supplier_ref_number = l_supplier_ref_number))
SELECT MIN(pli.line_num)
INTO x_po_line_num
FROM po_lines_interface pli
,po_requisition_lines_all prl
,po_line_types_b PLT -- <SERVICES FPJ>
WHERE pli.interface_header_id = x_interface_header_id
AND pli.line_num IS NOT NULL
AND prl.requisition_line_id <> x_requisition_line_id
AND prl.requisition_line_id = pli.requisition_line_id
AND pli.line_type_id = x_line_type_id
-- <SERVICES FPJ START> Any new Service line types should
-- cause the SELECT to fail (i.e. should not be matched).
--
AND PLI.line_type_id = PLT.line_type_id
AND PLT.order_type_lookup_code NOT IN ('RATE', 'FIXED PRICE')
--
-- <SERVICES FPJ END>
AND NVL(pli.ITEM_ID, -1) = NVL(x_item_id, -1)
AND NVL(pli.ITEM_DESCRIPTION, 'null') = NVL(x_item_description, 'null')
AND ((pli.ITEM_REVISION IS NULL AND x_item_revision IS NULL)
OR pli.ITEM_REVISION = x_item_revision)
AND pli.UNIT_OF_MEASURE = x_unit_meas_lookup_code
--<INVCONV R12 START>
-- replace x_preferred_grade to l_line_grade and
-- removed secondary unit comparison.
AND ((pli.PREFERRED_GRADE IS NULL AND l_line_grade IS NULL)
OR (pli.PREFERRED_GRADE = l_line_grade))
--<INVCONV R12 END>
-- FPI GA start
AND ((pli.from_header_id IS NULL AND x_source_doc_id IS NULL)
OR (pli.from_header_id = x_source_doc_id))
AND ((pli.from_line_id IS NULL AND x_source_doc_line_id IS NULL)
OR (pli.from_line_id = x_source_doc_line_id))
AND (NVL(l_needby_prf, 'Y') = 'N' -- Bug 3201308
OR ((pli.need_by_date IS NULL AND x_need_by_date IS NULL)
OR (TO_CHAR(
pli.need_by_date - (TO_NUMBER(
SUBSTR (
TO_CHAR (pli.need_by_date
,'DD-MM-YYYY HH24:MI:SS')
,18
,2)) / 86400)
,'DD-MM-YYYY HH24:MI:SS') =
TO_CHAR(
x_need_by_date - (TO_NUMBER(
SUBSTR (
TO_CHAR (x_need_by_date
,'DD-MM-YYYY HH24:MI:SS')
,18
,2)) / 86400)
,'DD-MM-YYYY HH24:MI:SS'))))
AND (NVL(l_shipto_prf, 'Y') = 'N' -- Bug 3201308
OR EXISTS
(SELECT 'x'
FROM HR_LOCATIONS HRL
WHERE PRL.deliver_to_location_id = HRL.location_id
AND NVL(HRL.ship_to_location_id, HRL.location_id) =
x_ship_to_location_id
UNION ALL
SELECT 'x'
FROM HZ_LOCATIONS HZ
WHERE PRL.deliver_to_location_id = HZ.location_id
AND HZ.location_id = x_ship_to_location_id))
AND (NVL(l_shipto_prf, 'Y') = 'N' -- Bug 3201308
OR ((pli.ship_to_organization_id IS NULL
AND x_destination_org_id IS NULL)
OR (pli.ship_to_organization_id = x_destination_org_id)))
-- FPI GA end
-- CONSIGNED FPI start
AND ((pli.consigned_flag IS NULL AND x_consigned_flag IS NULL)
OR (pli.consigned_flag = x_consigned_flag))
-- CONSIGNED FPI End
AND (pli.TRANSACTION_REASON_CODE IS NULL
OR pli.TRANSACTION_REASON_CODE =
NVL(x_transaction_reason_code, pli.TRANSACTION_REASON_CODE))
AND NVL(pli.oke_contract_header_id, -1) =
NVL(x_oke_contract_header_id, -1)
AND NVL(pli.oke_contract_version_id, -1) =
NVL(x_oke_contract_version_id, -1)
AND NVL(pli.vendor_product_num, -1) = NVL(x_vendor_product_num, -1)
AND NVL(pli.bid_number, -1) = NVL(x_bid_number, -1)
AND NVL(pli.bid_line_number, -1) = NVL(x_bid_line_number, -1)
AND NVL(pli.orig_from_req_flag, 'Y') <> 'N'
-- <GC FPJ START>
AND ((pli.contract_id IS NULL AND l_contract_id IS NULL)
OR (pli.contract_id = l_contract_id))
-- <GC FPJ END>
--<CONFIG_ID FPJ START>
AND ((pli.supplier_ref_number IS NULL AND l_supplier_ref_number IS NULL)
OR (pli.supplier_ref_number = l_supplier_ref_number)) --<CONFIG_ID FPJ END>
;
当前,前提是这个栏位po_requisition_lines_all.SUPPLIER_REF_NUMBER您的系统没使用。
Trm上查询了这个栏位的作用:
![](https://i-blog.csdnimg.cn/blog_migrate/dc88b523fdabb4967e3e6e1a212ec5e6.jpeg)
就是:供应商的参考编号
而且在申请界面上也没发现维护这个值的栏位。所以猜测应该是一个参考的值的栏位。借用它来实现分组的效果应该问题不大。
大不了在新增之后,如果是属于客户化分组用的,再用触发器什么的将它清掉即可。
接着说一下具体如何实现:
1 首先,功能要考虑扩展性,所以,添加一个Profile的配置是必须的:
XYG_ALP_REQ_LINE_GROUPING
XYG-客户化应用
XXX:使用默认自动创建PO依据申请行分组
使用默认自动创建PO依据申请行分组
SQL=”select lookup_code, meaning \”Grouping\”
into :profile_option_value, :visible_option_value
from fnd_lookups
where lookup_type = ‘YES_NO’”
Column=”\”Grouping\”(*)”
![](https://i-blog.csdnimg.cn/blog_migrate/a9a14aef8723fb7964e8d5bccc145d06.jpeg)
2 接着,在申请行添加一个触发器:
CREATE OR REPLACE TRIGGER XYG_ALP_REQ_LINES_TRG01
/******************************************************************************
NAME: XYG_ALP_REQ_LINES_TRG01
PURPOSE: 主要是利用supplier_ref_number栏位强制拆分采购订单行用
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2017/03/08 Sam.T 1. Created this Trigger.
******************************************************************************/
BEFORE INSERT
ON PO.po_requisition_lines_all
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
---
BEGIN
IF NVL(fnd_profile.VALUE('XYG_ALP_REQ_LINE_GROUPING'),'N')='Y' THEN
:NEW.supplier_ref_number:=CASE WHEN :NEW.supplier_ref_number IS NOT NULL THEN
:NEW.supplier_ref_number||'->'||:NEW.REQUISITION_LINE_ID
ELSE :NEW.REQUISITION_LINE_ID
END;
END IF;
END XYG_ALP_REQ_LINES_TRG01;
测试:
基本上就可以达到效果:
![](https://i-blog.csdnimg.cn/blog_migrate/1684d075a917ad94d08ca5624220d860.jpeg)
后台看数据:
![](https://i-blog.csdnimg.cn/blog_migrate/cb87fd7d6ec8e6e4148676873d400512.jpeg)
—大功告成!