商用营销型网站建设,自动推广引流app,微信小程序模版,seo sem区别有时候#xff0c;需要实现强制按照PR行来自动创建PO。举个例子#xff1a;假设2张PR#xff0c;同一个物料#xff0c;同一个需求日期等等的#xff0c;在合并采购订单的时候#xff0c;希望是2个PO行。按照标准功能的处理逻辑#xff0c;如果是同一个物料需求日期(配置…有时候需要实现强制按照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 -- 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 -- 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) -- -- 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 -- -- 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)) -- -- 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 -- AND ((pli.contract_id IS NULL AND l_contract_id IS NULL) OR (pli.contract_id l_contract_id)) -- -- AND ((pli.supplier_ref_number IS NULL AND l_supplier_ref_number IS NULL) OR (pli.supplier_ref_number l_supplier_ref_number)) -- ;当前前提是这个栏位po_requisition_lines_all.SUPPLIER_REF_NUMBER您的系统没使用。Trm上查询了这个栏位的作用就是供应商的参考编号而且在申请界面上也没发现维护这个值的栏位。所以猜测应该是一个参考的值的栏位。借用它来实现分组的效果应该问题不大。大不了在新增之后如果是属于客户化分组用的再用触发器什么的将它清掉即可。接着说一下具体如何实现1 首先功能要考虑扩展性所以添加一个Profile的配置是必须的XYG_ALP_REQ_LINE_GROUPINGXYG-客户化应用XXX:使用默认自动创建PO依据申请行分组使用默认自动创建PO依据申请行分组SQL”select lookup_code, meaning \”Grouping\”into :profile_option_value, :visible_option_valuefrom fnd_lookupswhere 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;测试基本上就可以达到效果后台看数据—大功告成
