在多OU环境下通过PL/SQL实现PO自动创建核心难点不在于PDOI接口表填充本身而在于多OU会话初始化、MOAC安全上下文切换、以及并发请求提交时的职责绑定。下面给出完整的多OU自动化实现方案。一、多OU PO自动创建的架构挑战挑战原因解决方案会话只能有一个当前OUMO_GLOBAL.SET_POLICY_CONTEXT一次只能设一个OU循环遍历OU每次重新初始化并发程序只能在一个职责下运行Import程序以提交请求的用户职责运行Security Profile限制可见OU要么单次提交处理一个OU要么用超级职责覆盖所有OU供应商地点必须在同一OUAP_SUPPLIER_SITES_ALL.ORG_ID必须等于接口表ORG_ID填充接口表时确保匹配科目CCID可能跨OU不同不同OU可能有不同Chart of Accounts按OU查各自的CODE_COMBINATION_ID二、完整实现多OU批量自动创建PO步骤1OU循环初始化与接口表填充CREATE OR REPLACE PROCEDURE multi_ou_auto_create_po( p_batch_size IN NUMBER DEFAULT 500 ) IS -- 游标获取所有需要处理的OU CURSOR c_ou IS SELECT hou.organization_id, hou.name AS ou_name, gl.ledger_id, gl.chart_of_accounts_id, gl.currency_code FROM hr_operating_units hou JOIN gl_ledgers gl ON hou.set_of_books_id gl.ledger_id WHERE hou.date_disabled IS NULL AND EXISTS ( -- 只处理有待处理需求的OU SELECT 1 FROM po_requisition_headers_all prh WHERE prh.org_id hou.organization_id AND prh.authorization_status APPROVED AND prh.interface_source_code EXTERNAL ); -- 记录类型外部系统传入的待处理数据 TYPE t_external_req IS RECORD ( ext_ref_no VARCHAR2(50), vendor_id NUMBER, vendor_site_id NUMBER, item_id NUMBER, quantity NUMBER, unit_price NUMBER, need_by_date DATE, ccid NUMBER, -- 科目组合ID按OU预计算 project_id NUMBER, task_id NUMBER, org_id NUMBER ); TYPE t_req_tab IS TABLE OF t_external_req; l_req_data t_req_tab; l_interface_header_id NUMBER; l_interface_line_id NUMBER; l_interface_shipment_id NUMBER; l_interface_dist_id NUMBER; l_request_id NUMBER; l_return_status VARCHAR2(10); l_error_msg VARCHAR2(4000); l_total_count NUMBER : 0; BEGIN -- 遍历每个OU FOR ou_rec IN c_ou LOOP -- 1. 获取该OU下待处理的外部请求 SELECT er.ext_ref_no, er.vendor_id, er.vendor_site_id, er.item_id, er.quantity, er.unit_price, er.need_by_date, -- 按OU获取科目CCID不同OU Chart可能不同 get_ccid_for_ou(ou_rec.chart_of_accounts_id, er.expense_account_segment) AS ccid, er.project_id, er.task_id, ou_rec.organization_id BULK COLLECT INTO l_req_data FROM external_requests er WHERE er.status PENDING AND er.org_id ou_rec.organization_id AND ROWNUM p_batch_size; IF l_req_data.COUNT 0 THEN CONTINUE; -- 该OU无待处理数据跳到下一个 END IF; -- 2. 初始化该OU的会话上下文 fnd_global.apps_initialize( user_id 1804, -- 实际系统用户ID resp_id 20718, -- MOAC超级采购员职责可访问所有OU resp_appl_id 201 -- PO应用ID ); mo_global.init(PO); mo_global.set_policy_context(S, ou_rec.organization_id); -- 3. 批量填充接口表使用FORALL提升性能 FORALL i IN 1..l_req_data.COUNT INSERT INTO PO_HEADERS_INTERFACE ( INTERFACE_HEADER_ID, ACTION, HEADER_INTERFACE_STATUS_CODE, PROCESS_CODE, DOCUMENT_TYPE_CODE, DOCUMENT_SUBTYPE, VENDOR_ID, VENDOR_SITE_ID, ORG_ID, CURRENCY_CODE, TERMS_ID, SHIP_TO_LOCATION_ID, BILL_TO_LOCATION_ID, ATTRIBUTE_CATEGORY, ATTRIBUTE1, -- 存储外部引用号 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE ) VALUES ( PO_HEADERS_INTERFACE_S.NEXTVAL, ORIGINAL, NEW, PROCESS, STANDARD, STANDARD, l_req_data(i).vendor_id, l_req_data(i).vendor_site_id, ou_rec.organization_id, ou_rec.currency_code, get_default_term_id(l_req_data(i).vendor_site_id), -- 取供应商默认付款条件 101, -- 收货地址ID按实际配置 102, -- 开票地址ID按实际配置 EXTERNAL_REF, l_req_data(i).ext_ref_no, fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE ); -- 4. 填充PO行同样使用FORALL -- 注意这里需要先获取上一步插入的INTERFACE_HEADER_ID序列值 -- 实际生产环境建议先批量生成序列号再并行插入 FOR i IN 1..l_req_data.COUNT LOOP -- 获取当前批次的序列号简化写法实际可用序列号缓存 SELECT PO_HEADERS_INTERFACE_S.CURRVAL INTO l_interface_header_id FROM DUAL; l_interface_header_id : l_interface_header_id i - 1; INSERT INTO PO_LINES_INTERFACE ( INTERFACE_HEADER_ID, INTERFACE_LINE_ID, LINE_NUM, ITEM_ID, UNIT_MEAS_LOOKUP_CODE, QUANTITY, UNIT_PRICE, NEED_BY_DATE, PROMISED_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE ) VALUES ( l_interface_header_id, PO_LINES_INTERFACE_S.NEXTVAL, 1, l_req_data(i).item_id, EA, l_req_data(i).quantity, l_req_data(i).unit_price, l_req_data(i).need_by_date, l_req_data(i).need_by_date - 5, fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE ); -- 获取LINE_ID用于后续插入Shipment和Distribution SELECT PO_LINES_INTERFACE_S.CURRVAL INTO l_interface_line_id FROM DUAL; -- 填充Shipment INSERT INTO PO_LINE_LOCATIONS_INTERFACE ( INTERFACE_HEADER_ID, INTERFACE_LINE_ID, INTERFACE_LINE_LOCATION_ID, SHIP_TO_ORGANIZATION_ID, QUANTITY, PRICE_OVERRIDE, MATCH_OPTION, NEED_BY_DATE, PROMISED_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE ) VALUES ( l_interface_header_id, l_interface_line_id, PO_LINE_LOCATIONS_INTERFACE_S.NEXTVAL, get_inventory_org_for_ou(ou_rec.organization_id), -- 按OU获取库存组织 l_req_data(i).quantity, l_req_data(i).unit_price, RECEIPT, -- 三单匹配 l_req_data(i).need_by_date, l_req_data(i).need_by_date - 5, fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE ); SELECT PO_LINE_LOCATIONS_INTERFACE_S.CURRVAL INTO l_interface_shipment_id FROM DUAL; -- 填充会计分配 INSERT INTO PO_DISTRIBUTIONS_INTERFACE ( INTERFACE_HEADER_ID, INTERFACE_LINE_ID, INTERFACE_LINE_LOCATION_ID, INTERFACE_DISTRIBUTION_ID, QUANTITY, DISTRIBUTION_QUANTITY, SET_OF_BOOKS_ID, CODE_COMBINATION_ID, PROJECT_ID, TASK_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE ) VALUES ( l_interface_header_id, l_interface_line_id, l_interface_shipment_id, PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL, l_req_data(i).quantity, l_req_data(i).quantity, ou_rec.ledger_id, l_req_data(i).ccid, l_req_data(i).project_id, l_req_data(i).task_id, fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE ); END LOOP; -- 5. 提交并发请求Import Standard Purchase Orders -- 注意此时会话上下文仍在该OU下 l_request_id : FND_REQUEST.SUBMIT_REQUEST( application PO, program POXPOPDOI, description Auto Create PO for || ou_rec.ou_name, start_time SYSDATE, sub_request FALSE, argument1 N, -- Purge after import argument2 Y, -- Debug mode (首次运行设为Y便于排查) argument3 0 -- Process all rows ); IF l_request_id 0 THEN COMMIT; l_total_count : l_total_count l_req_data.COUNT; -- 记录成功日志 apps.fnd_file.put_line(FND_FILE.LOG, OU || ou_rec.ou_name || : Submitted || l_req_data.COUNT || POs (Request ID: || l_request_id || )); ELSE ROLLBACK; apps.fnd_file.put_line(FND_FILE.LOG, ERROR: Failed to submit request for OU || ou_rec.ou_name); END IF; END LOOP; apps.fnd_file.put_line(FND_FILE.OUTPUT, Total POs created across all OUs: || l_total_count); EXCEPTION WHEN OTHERS THEN ROLLBACK; apps.fnd_file.put_line(FND_FILE.LOG, FATAL ERROR: || SQLERRM); RAISE; END multi_ou_auto_create_po;三、关键辅助函数1. 按OU获取库存组织CREATE OR REPLACE FUNCTION get_inventory_org_for_ou( p_org_id IN NUMBER ) RETURN NUMBER IS l_inv_org_id NUMBER; BEGIN -- 假设每个OU有一个主库存组织可根据实际映射表调整 SELECT mp.organization_id INTO l_inv_org_id FROM mtl_parameters mp JOIN hr_organization_information hoi ON hoi.organization_id mp.organization_id WHERE hoi.org_information1 OPERATING_UNIT AND hoi.org_information_context CLASS AND hoi.organization_id p_org_id AND ROWNUM 1; RETURN l_inv_org_id; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END get_inventory_org_for_ou;2. 按Chart of Accounts获取科目CCIDCREATE OR REPLACE FUNCTION get_ccid_for_ou( p_chart_of_accounts_id IN NUMBER, p_expense_segment IN VARCHAR2 ) RETURN NUMBER IS l_ccid NUMBER; BEGIN -- 根据COA结构动态拼接科目段查询GL_CODE_COMBINATIONS SELECT gcc.code_combination_id INTO l_ccid FROM gl_code_combinations gcc WHERE gcc.chart_of_accounts_id p_chart_of_accounts_id AND gcc.segment1 p_expense_segment -- 费用段 AND gcc.enabled_flag Y AND NVL(gcc.end_date_active, SYSDATE 365) SYSDATE; RETURN l_ccid; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END get_ccid_for_ou;四、性能优化批量序列号预生成对于大批量处理数千行以上上述逐行INSERT效率低建议改用序列号预分配 BULK INSERT-- 预分配序列号 TYPE t_seq_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER; PROCEDURE pre_allocate_seqs( p_count IN NUMBER, x_header_ids OUT t_seq_tab, x_line_ids OUT t_seq_tab, x_ship_ids OUT t_seq_tab, x_dist_ids OUT t_seq_tab ) IS BEGIN FOR i IN 1..p_count LOOP x_header_ids(i) : PO_HEADERS_INTERFACE_S.NEXTVAL; x_line_ids(i) : PO_LINES_INTERFACE_S.NEXTVAL; x_ship_ids(i) : PO_LINE_LOCATIONS_INTERFACE_S.NEXTVAL; x_dist_ids(i) : PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL; END LOOP; END pre_allocate_seqs; -- 然后使用 FORALL 批量插入 FORALL i IN 1..l_req_data.COUNT INSERT INTO PO_HEADERS_INTERFACE ( INTERFACE_HEADER_ID, ... ) VALUES ( x_header_ids(i), ... ); FORALL i IN 1..l_req_data.COUNT INSERT INTO PO_LINES_INTERFACE ( INTERFACE_HEADER_ID, INTERFACE_LINE_ID, ... ) VALUES ( x_header_ids(i), x_line_ids(i), ... );五、多OU自动化最佳实践总结要点做法职责选择​使用具有全OU安全配置文件Security Profile All Operating Units​ 的超级采购员职责提交并发请求会话初始化​每个OU循环开始时重新MO_GLOBAL.SET_POLICY_CONTEXT(S, org_id)供应商校验​填充接口表前校验AP_SUPPLIER_SITES_ALL.ORG_ID PO_HEADERS_INTERFACE.ORG_ID科目解析​不同OU可能使用不同Chart of Accounts需按OU分别解析CCID并发提交​每个OU提交一次Import请求不可混在不同OU行到同一请求错误隔离​每个OU的事务独立COMMIT/ROLLBACK一个OU失败不影响其他OU日志追踪​记录每个OU处理的PO数量和请求ID便于后续核对