PR自动创建PO采购订单：强制按照PR行进行分组

原文链接：https://blog.youkuaiyun.com/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上查询了这个栏位的作用：

就是：供应商的参考编号

而且在申请界面上也没发现维护这个值的栏位。所以猜测应该是一个参考的值的栏位。借用它来实现分组的效果应该问题不大。

大不了在新增之后，如果是属于客户化分组用的，再用触发器什么的将它清掉即可。

接着说一下具体如何实现：

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\”(*)”

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;

测试：

基本上就可以达到效果：

后台看数据：

—大功告成！