华为MetaERP 将映射表与PO审批工作流集成，实现按库存组织路由审批，是多OU精细化管控的关键能力。下面给出完整的集成方案设计，涵盖数据模型、工作流配置、PL/SQL实现及注意事项。

将映射表与PO审批工作流集成实现按库存组织路由审批是多OU精细化管控的关键能力。下面给出完整的集成方案设计涵盖数据模型、工作流配置、PL/SQL实现及注意事项。将映射表与PO审批工作流集成实现按库存组织路由审批是多OU精细化管控的关键能力。下面给出完整的集成方案设计涵盖数据模型、工作流配置、PL/SQL实现及注意事项。一、集成架构总览┌─────────────────────────────────────────────────────────────┐ │ PO审批工作流AME │ │ ┌──────────┐ ┌──────────┐ ┌──────────────────────┐ │ │ │ 发起审批 │───▶│ 规则引擎 │───▶│ 按库存组织路由审批节点 │ │ │ └──────────┘ └──────────┘ └──────────────────────┘ │ │ │ │ │ ▼ │ │ ┌──────────────┐ │ │ │ 映射表查询 │ ←── xx_po_supplier_site_inv_map │ └──────────────┘ │ └─────────────────────────────────────────────────────────────┘核心思路​ 在PO审批工作流中根据PO行上的SHIP_TO_ORGANIZATION_ID库存组织查询映射表获得该库存组织对应的审批链从而动态决定审批人。二、数据模型扩展1. 在映射表中增加审批链字段ALTER TABLE xx_po_supplier_site_inv_map ADD ( approval_chain_id NUMBER, -- 审批链ID关联自定义审批链表 approval_group_id NUMBER, -- 审批组IDAME分组 buyer_id NUMBER, -- 指定采购员可选覆盖 buyer_group_id NUMBER, -- 采购员组 supervisor_id NUMBER, -- 主管审批人直接指定 cost_center_approver_id NUMBER -- 成本中心审批人 );2. 审批链主表CREATE TABLE xx_po_approval_chain ( chain_id NUMBER PRIMARY KEY, chain_code VARCHAR2(30) NOT NULL, -- 审批链编码 chain_name VARCHAR2(240) NOT NULL, -- 审批链名称 description VARCHAR2(2000), -- 审批层级定义JSON格式存储便于扩展 approval_levels CLOB, -- [{level:1,type:BUYER},{level:2,type:SUPERVISOR},...] -- 适用范围 applicable_org_id NUMBER, -- 适用OUNULL全局 applicable_category VARCHAR2(30), -- 适用采购分类 -- 状态控制 enable_flag VARCHAR2(1) DEFAULT Y, effective_from DATE DEFAULT SYSDATE, effective_to DATE, -- 审计字段 creation_date DATE DEFAULT SYSDATE, created_by NUMBER, last_update_date DATE DEFAULT SYSDATE, last_updated_by NUMBER, -- 约束 CONSTRAINT xx_po_chain_uk1 UNIQUE (chain_code) ); -- 审批层级明细表更规范的范式设计 CREATE TABLE xx_po_approval_level ( level_id NUMBER PRIMARY KEY, chain_id NUMBER NOT NULL, sequence_num NUMBER NOT NULL, -- 审批顺序 approver_type VARCHAR2(30) NOT NULL, -- BUYER/SUPERVISOR/COST_CENTER/MANAGER/JOB_LEVEL/SPECIFIC_USER approver_value VARCHAR2(240), -- 具体值如JOB_LEVEL3, SPECIFIC_USERUSER_ID approval_threshold NUMBER, -- 金额阈值超过此金额才需本层审批 approval_limit NUMBER, -- 最大审批金额超过则转下一层 escalation_days NUMBER DEFAULT 3, -- 升级天数 enable_flag VARCHAR2(1) DEFAULT Y, -- 约束 CONSTRAINT xx_po_level_fk1 FOREIGN KEY (chain_id) REFERENCES xx_po_approval_chain(chain_id), CONSTRAINT xx_po_level_uk1 UNIQUE (chain_id, sequence_num) );三、工作流集成实现方案一基于AMEApproval Management Engine的自定义规则AME是Oracle EBS标准的审批引擎可以通过自定义规则实现按库存组织路由。步骤1创建AME审批属性-- 注册自定义属性库存组织 BEGIN ame_util.register_attribute( p_attribute_name XX_INVENTORY_ORG_ID, p_description Inventory Organization ID from PO Line, p_attribute_type NUMBER, p_application_id 201, -- PO Application p_attribute_table_name PO_LINE_LOCATIONS_ALL, p_attribute_column_name SHIP_TO_ORGANIZATION_ID ); -- 注册自定义属性审批链ID ame_util.register_attribute( p_attribute_name XX_APPROVAL_CHAIN_ID, p_description Approval Chain ID from Mapping Table, p_attribute_type NUMBER, p_application_id 201 ); END;步骤2创建PL/SQL函数——获取审批链IDCREATE OR REPLACE FUNCTION get_approval_chain_for_po( p_po_header_id IN NUMBER ) RETURN NUMBER IS l_chain_id NUMBER; l_vendor_id NUMBER; l_vendor_site_id NUMBER; l_inv_org_id NUMBER; l_purchasing_org_id NUMBER; BEGIN -- 获取PO关键信息 SELECT pha.vendor_id, pha.vendor_site_id, pha.org_id INTO l_vendor_id, l_vendor_site_id, l_purchasing_org_id FROM po_headers_all pha WHERE pha.po_header_id p_po_header_id; -- 获取第一个行的库存组织PO所有行应使用相同库存组织否则需按行拆分审批 SELECT MIN(plla.ship_to_organization_id) INTO l_inv_org_id FROM po_line_locations_all plla WHERE plla.po_header_id p_po_header_id; -- 查询映射表获取审批链 SELECT NVL(psim.approval_chain_id, get_default_chain_for_org(l_purchasing_org_id, l_inv_org_id)) INTO l_chain_id FROM xx_po_supplier_site_inv_map psim WHERE psim.vendor_site_id l_vendor_site_id AND psim.inventory_org_id l_inv_org_id AND psim.purchasing_org_id l_purchasing_org_id AND psim.enable_flag Y AND NVL(psim.effective_to, SYSDATE 1) SYSDATE AND ROWNUM 1; RETURN l_chain_id; EXCEPTION WHEN NO_DATA_FOUND THEN -- 降级使用OU默认审批链 RETURN get_default_chain_for_org(l_purchasing_org_id, l_inv_org_id); WHEN OTHERS THEN RETURN NULL; END get_approval_chain_for_po;步骤3AME规则配置在AME管理界面中配置规则名称条件动作Route by Inventory OrgXX_INVENTORY_ORG_IDIS NOT NULL调用get_approvers_by_chain获取审批人列表Default RouteAlways True使用标准职位层级审批方案二基于WF工作流Workflow Builder的自定义审批流程适用于需要更复杂审批逻辑的场景。步骤1创建工作流属性在Oracle Workflow Builder中为PO审批流程增加Attribute: XX_INVENTORY_ORG_ID (Number) Attribute: XX_APPROVAL_CHAIN (String) Attribute: XX_APPROVER_LIST (String, comma-separated user IDs)步骤2自定义审批节点PL/SQLCREATE OR REPLACE PACKAGE xx_po_wf_custom IS -- 工作流函数获取按库存组织路由的审批人 FUNCTION get_approvers_by_inv_org( p_itemtype IN VARCHAR2, p_itemkey IN VARCHAR2, p_actid IN NUMBER, p_funcmode IN VARCHAR2, x_resultout OUT VARCHAR2 ) RETURN VARCHAR2; -- 工作流函数动态设置审批层级 FUNCTION set_approval_levels( p_itemtype IN VARCHAR2, p_itemkey IN VARCHAR2, p_actid IN NUMBER, p_funcmode IN VARCHAR2, x_resultout OUT VARCHAR2 ) RETURN VARCHAR2; END xx_po_wf_custom; / CREATE OR REPLACE PACKAGE BODY xx_po_wf_custom IS FUNCTION get_approvers_by_inv_org( p_itemtype IN VARCHAR2, p_itemkey IN VARCHAR2, p_actid IN NUMBER, p_funcmode IN VARCHAR2, x_resultout OUT VARCHAR2 ) RETURN VARCHAR2 IS l_po_header_id NUMBER; l_chain_id NUMBER; l_approver_list VARCHAR2(4000); l_org_id NUMBER; BEGIN IF p_funcmode RUN THEN -- 从工作流上下文获取PO Header ID l_po_header_id : wf_engine.GetItemAttrNumber( p_itemtype, p_itemkey, PO_HEADER_ID); -- 获取审批链 l_chain_id : get_approval_chain_for_po(l_po_header_id); IF l_chain_id IS NOT NULL THEN -- 获取该审批链的所有审批人 SELECT LISTAGG(approver_id, ,) WITHIN GROUP (ORDER BY sequence_num) INTO l_approver_list FROM ( -- 第一层采购员本人 SELECT 1 AS seq, pha.agent_id AS approver_id FROM po_headers_all pha WHERE pha.po_header_id l_po_header_id UNION ALL -- 第二层主管 SELECT 2, pe.supervisor_id FROM po_headers_all pha JOIN per_employees_current_x pe ON pe.employee_id pha.agent_id WHERE pha.po_header_id l_po_header_id UNION ALL -- 第三层映射表指定的成本中心审批人 SELECT 3, psim.cost_center_approver_id FROM xx_po_supplier_site_inv_map psim JOIN po_headers_all pha ON pha.vendor_site_id psim.vendor_site_id WHERE pha.po_header_id l_po_header_id AND psim.cost_center_approver_id IS NOT NULL ) ORDER BY seq; END IF; -- 设置工作流属性 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, XX_APPROVER_LIST, l_approver_list); x_resultout : COMPLETE; ELSIF p_funcmode CANCEL THEN x_resultout : COMPLETE; END IF; RETURN ; END get_approvers_by_inv_org; FUNCTION set_approval_levels( p_itemtype IN VARCHAR2, p_itemkey IN VARCHAR2, p_actid IN NUMBER, p_funcmode IN VARCHAR2, x_resultout OUT VARCHAR2 ) RETURN VARCHAR2 IS l_po_header_id NUMBER; l_chain_id NUMBER; l_total_amount NUMBER; l_current_level NUMBER : 1; l_max_level NUMBER; BEGIN IF p_funcmode RUN THEN l_po_header_id : wf_engine.GetItemAttrNumber( p_itemtype, p_itemkey, PO_HEADER_ID); -- 获取PO总金额 SELECT SUM(NVL(plla.quantity, 0) * NVL(plla.price_override, 0)) INTO l_total_amount FROM po_line_locations_all plla WHERE plla.po_header_id l_po_header_id; -- 获取审批链的最大层级数 l_chain_id : get_approval_chain_for_po(l_po_header_id); SELECT MAX(sequence_num) INTO l_max_level FROM xx_po_approval_level WHERE chain_id l_chain_id AND enable_flag Y AND NVL(approval_threshold, 0) l_total_amount; -- 设置审批层级数 wf_engine.SetItemAttrNumber(p_itemtype, p_itemkey, XX_MAX_APPROVAL_LEVEL, l_max_level); x_resultout : COMPLETE; ELSIF p_funcmode CANCEL THEN x_resultout : COMPLETE; END IF; RETURN ; END set_approval_levels; END xx_po_wf_custom;步骤3工作流流程图设计[Start] → [Get Approvers by Inv Org] → [Level 1 Approval: Buyer] ↓ (Approved) [Check Amount Threshold] ↓ (Exceeds) [Level 2 Approval: Supervisor] ↓ (Approved) [Level 3 Approval: Cost Center] ↓ (Approved) [Final Approval: Manager] ↓ [End: PO Approved]四、映射表与审批链的动态绑定1. 审批链自动匹配逻辑CREATE OR REPLACE FUNCTION match_approval_chain( p_purchasing_org_id IN NUMBER, p_inventory_org_id IN NUMBER, p_category_id IN NUMBER DEFAULT NULL, p_total_amount IN NUMBER DEFAULT NULL ) RETURN NUMBER IS l_chain_id NUMBER; BEGIN -- 优先级1精确匹配OU 库存组织 采购分类 SELECT chain_id INTO l_chain_id FROM xx_po_approval_chain WHERE applicable_org_id p_purchasing_org_id AND chain_id IN ( SELECT approval_chain_id FROM xx_po_supplier_site_inv_map WHERE inventory_org_id p_inventory_org_id AND enable_flag Y ) AND (applicable_category IS NULL OR applicable_category p_category_id) AND enable_flag Y AND NVL(effective_to, SYSDATE 1) SYSDATE AND ROWNUM 1; IF l_chain_id IS NOT NULL THEN RETURN l_chain_id; END IF; -- 优先级2OU 库存组织忽略分类 SELECT chain_id INTO l_chain_id FROM xx_po_approval_chain WHERE applicable_org_id p_purchasing_org_id AND chain_id IN ( SELECT approval_chain_id FROM xx_po_supplier_site_inv_map WHERE inventory_org_id p_inventory_org_id AND enable_flag Y ) AND applicable_category IS NULL AND enable_flag Y AND NVL(effective_to, SYSDATE 1) SYSDATE AND ROWNUM 1; IF l_chain_id IS NOT NULL THEN RETURN l_chain_id; END IF; -- 优先级3OU级别默认链 SELECT chain_id INTO l_chain_id FROM xx_po_approval_chain WHERE applicable_org_id p_purchasing_org_id AND applicable_category IS NULL AND enable_flag Y AND NVL(effective_to, SYSDATE 1) SYSDATE AND ROWNUM 1; RETURN l_chain_id; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END match_approval_chain;2. 审批人动态解析CREATE OR REPLACE FUNCTION resolve_approver( p_chain_id IN NUMBER, p_level_seq IN NUMBER, p_po_header_id IN NUMBER ) RETURN NUMBER IS l_approver_type VARCHAR2(30); l_approver_value VARCHAR2(240); l_user_id NUMBER; BEGIN -- 获取该层级配置 SELECT al.approver_type, al.approver_value INTO l_approver_type, l_approver_value FROM xx_po_approval_level al WHERE al.chain_id p_chain_id AND al.sequence_num p_level_seq AND al.enable_flag Y; -- 按类型解析具体审批人 CASE l_approver_type WHEN BUYER THEN -- PO的采购员 SELECT agent_id INTO l_user_id FROM po_headers_all WHERE po_header_id p_po_header_id; WHEN SUPERVISOR THEN -- 采购员的上级主管 SELECT pe.supervisor_id INTO l_user_id FROM po_headers_all pha JOIN per_employees_current_x pe ON pe.employee_id pha.agent_id WHERE pha.po_header_id p_po_header_id; WHEN COST_CENTER THEN -- 映射表中指定的成本中心审批人 SELECT psim.cost_center_approver_id INTO l_user_id FROM xx_po_supplier_site_inv_map psim JOIN po_headers_all pha ON pha.vendor_site_id psim.vendor_site_id WHERE pha.po_header_id p_po_header_id AND psim.inventory_org_id IN ( SELECT ship_to_organization_id FROM po_line_locations_all WHERE po_header_id p_po_header_id ) AND ROWNUM 1; WHEN MANAGER THEN -- 特定级别的管理者 SELECT employee_id INTO l_user_id FROM per_employees_current_x WHERE person_id ( SELECT supervisor_id FROM per_employees_current_x WHERE employee_id ( SELECT agent_id FROM po_headers_all WHERE po_header_id p_po_header_id ) ); WHEN SPECIFIC_USER THEN -- 直接指定用户 l_user_id : TO_NUMBER(l_approver_value); WHEN JOB_LEVEL THEN -- 按职位级别路由 SELECT pe.employee_id INTO l_user_id FROM per_employees_current_x pe JOIN per_jobs pj ON pj.job_id pe.job_id WHERE pj.job_level TO_NUMBER(l_approver_value) AND pe.organization_id ( SELECT organization_id FROM po_headers_all WHERE po_header_id p_po_header_id ) AND ROWNUM 1; END CASE; RETURN l_user_id; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END resolve_approver;五、配置界面设计1. 审批链维护OAF页面功能区域内容审批链头​编码、名称、描述、适用OU、生效日期审批层级Grid​序号、审批人类型下拉Buyer/Supervisor/Manager/Specific User、审批人值、金额阈值、升级天数预览按钮​输入测试PO模拟显示完整审批链测试按钮​实际调用resolve_approver查看解析结果2. 映射表维护增强在已有的供应商地点→库存组织映射页面上增加字段控件说明审批链Lov选择过滤可用审批链选择该映射适用的审批链成本中心审批人Lov选择员工直接指定成本中心负责人采购员组Lov选择采购员组可选覆盖默认采购员六、测试与验证方案1. 单元测试-- 测试审批链匹配 DECLARE l_chain_id NUMBER; BEGIN l_chain_id : match_approval_chain( p_purchasing_org_id 204, -- 深圳OU p_inventory_org_id 115, -- 深圳成品仓 p_category_id 10, -- 原材料分类 p_total_amount 50000 -- 5万金额 ); DBMS_OUTPUT.PUT_LINE(Matched Chain ID: || l_chain_id); -- 打印该链的完整审批层级 FOR rec IN ( SELECT * FROM xx_po_approval_level WHERE chain_id l_chain_id ORDER BY sequence_num ) LOOP DBMS_OUTPUT.PUT_LINE(Level || rec.sequence_num || : || rec.approver_type || (Threshold: || rec.approval_threshold || )); END LOOP; END;2. 集成测试场景测试场景预期结果普通物料PO金额1万只需采购员审批高价值物料PO金额10万需采购员→主管→经理三层审批危险品物料PO增加安全主管审批节点进口物料PO增加关务审批节点紧急采购PO跳过部分层级直接到经理审批七、部署与运维建议关注点建议性能优化​审批链查询结果缓存到内存表或Redis避免每次审批都查数据库监控告警​对审批超时超过escalation_days发送邮件/企微通知变更管理​审批链修改建议走变更管理流程保留历史版本备份恢复​定期导出审批链配置为LDT文件便于灾难恢复灰度发布​先在测试OU启用新审批链验证无误后再推广到生产OU