实现PO模块自动化流程的核心思路是区分单据类型、选择正确的API路径、做好事务控制和错误处理。下面从三个典型自动化场景出发给出完整的PL/SQL实现方案。一、自动化流程架构全景场景1外部SRM → 自动创建POPDOI接口表 场景2PO到期/完成 → 自动关单/取消Control API 场景3PR审批通过 → 自动合并生成POAutoCreate API每个场景对应不同的API组合下面逐一展开。二、场景1自动创建POPDOI接口表 并发请求这是最推荐的批量创建方式适合SRM/MES/自开发系统对接。步骤1填充接口表-- 填充PO头 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, SHIP_TO_LOCATION_ID, BILL_TO_LOCATION_ID, CURRENCY_CODE, TERMS_ID, ORG_ID, ATTRIBUTE_CATEGORY, ATTRIBUTE1 -- 可存外部系统编号 ) VALUES ( PO_HEADERS_INTERFACE_S.NEXTVAL, ORIGINAL, -- ORIGINAL/UPDATE/CANCEL NEW, PROCESS, -- PROCESS/PENDING/VALIDATE_ONLY STANDARD, -- STANDARD/BLANKET/CONTRACT/PLANNED STANDARD, l_vendor_id, l_vendor_site_id, l_ship_to_loc_id, l_bill_to_loc_id, CNY, l_payment_term_id, l_org_id, EXTERNAL_REF, l_external_po_number ); -- 填充PO行 INSERT INTO PO_LINES_INTERFACE ( INTERFACE_HEADER_ID, INTERFACE_LINE_ID, LINE_NUM, ITEM_ID, ITEM_DESCRIPTION, UNIT_MEAS_LOOKUP_CODE, QUANTITY, UNIT_PRICE, NEED_BY_DATE, PROMISED_DATE ) VALUES ( l_interface_header_id, PO_LINES_INTERFACE_S.NEXTVAL, 1, l_item_id, NULL, -- 如果ITEM_ID有值则忽略描述 EA, 100, 50.00, SYSDATE 30, SYSDATE 25 ); -- 填充发运行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, -- RECEIPT三单匹配, PO两单匹配 ACCEPTANCE_DUE_DATE, NEED_BY_DATE, PROMISED_DATE ) VALUES ( l_interface_header_id, l_interface_line_id, PO_LINE_LOCATIONS_INTERFACE_S.NEXTVAL, l_inv_org_id, 100, NULL, RECEIPT, SYSDATE 35, SYSDATE 30, SYSDATE 25 ); -- 填充会计分配 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, -- 费用科目CCID PROJECT_ID, -- 项目核算时填写 TASK_ID ) VALUES ( l_interface_header_id, l_interface_line_id, l_interface_line_location_id, PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL, 100, 100, l_set_of_books_id, l_ccid, NULL, NULL );步骤2提交并发请求DECLARE l_request_id NUMBER; BEGIN l_request_id : FND_REQUEST.SUBMIT_REQUEST( application PO, program POXPOPDOI, -- Import Standard Purchase Orders description Auto Create PO from External System, start_time SYSDATE, sub_request FALSE, argument1 N, -- Purge records after import? Y/N argument2 N, -- Print debug log? argument3 0 -- Batch size, 0all ); COMMIT; IF l_request_id 0 THEN DBMS_OUTPUT.PUT_LINE(Request submitted: || l_request_id); ELSE RAISE_APPLICATION_ERROR(-20001, Failed to submit request); END IF; END;步骤3检查导入结果-- 查询错误 SELECT * FROM PO_INTERFACE_ERRORS WHERE INTERFACE_HEADER_ID l_interface_header_id; -- 查询生成的PO编号 SELECT PO_HEADER_ID, SEGMENT1 FROM PO_HEADERS_ALL WHERE ATTRIBUTE1 l_external_po_number;三、场景2自动关单/取消Control API适用于PO到期自动取消、全部收货后自动关闭、月末批量清理。完整函数自动关闭已完成的POCREATE OR REPLACE FUNCTION auto_close_completed_pos( p_org_id IN NUMBER, p_days_after_complete IN NUMBER DEFAULT 7 ) RETURN NUMBER IS -- 返回处理的PO数量 CURSOR c_po IS SELECT pha.po_header_id, pha.segment1 FROM po_headers_all pha WHERE pha.org_id p_org_id AND pha.closed_code OPEN AND pha.type_lookup_code IN (STANDARD, PLANNED) AND EXISTS ( SELECT 1 FROM po_line_locations_all plla WHERE plla.po_header_id pha.po_header_id AND plla.closed_code CLOSED AND NVL(plla.quantity_received, 0) NVL(plla.quantity, 0) AND plla.last_update_date SYSDATE - p_days_after_complete ) AND NOT EXISTS ( -- 排除仍有未收货行的PO SELECT 1 FROM po_line_locations_all plla2 WHERE plla2.po_header_id pha.po_header_id AND plla2.closed_code OPEN AND NVL(plla2.quantity_received, 0) NVL(plla2.quantity, 0) ); l_return_status VARCHAR2(10); l_count NUMBER : 0; BEGIN -- 初始化环境 fnd_global.apps_initialize( user_id 1804, -- 替换为实际用户 resp_id 20709, -- PO管理员职责 resp_appl_id 201 ); mo_global.init(PO); mo_global.set_policy_context(S, p_org_id); FOR rec IN c_po LOOP BEGIN po_document_control_pub.control_document( p_api_version 1.0, p_init_msg_list FND_API.G_TRUE, p_commit FND_API.G_FALSE, x_return_status l_return_status, p_doc_type PO, p_doc_subtype STANDARD, p_doc_id rec.po_header_id, p_action FINALLY_CLOSE, -- 彻底关闭 p_action_date SYSDATE, p_cancel_reason Auto close after completion, p_cancel_reqs_flag N, p_org_id p_org_id ); IF l_return_status FND_API.G_RET_STS_SUCCESS THEN l_count : l_count 1; COMMIT; -- 记录日志 apps.fnd_file.put_line(FND_FILE.LOG, PO || rec.segment1 || auto closed successfully.); ELSE ROLLBACK; apps.fnd_file.put_line(FND_FILE.LOG, FAILED to close PO || rec.segment1); END IF; EXCEPTION WHEN OTHERS THEN ROLLBACK; apps.fnd_file.put_line(FND_FILE.LOG, ERROR closing PO || rec.segment1 || : || SQLERRM); END; END LOOP; RETURN l_count; END auto_close_completed_pos;四、场景3PR审批后自动合并生成POAutoCreate利用PO_INTERFACE_S.CREATE_DOCUMENTS实现PR→PO自动化。批量AutoCreate PR为POCREATE OR REPLACE PROCEDURE auto_create_po_from_pr( p_org_id IN NUMBER, p_buyer_id IN NUMBER DEFAULT NULL ) IS TYPE t_pr_rec IS RECORD ( req_header_id PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID%TYPE, req_number PO_REQUISITION_HEADERS_ALL.SEGMENT1%TYPE, line_count NUMBER ); TYPE t_pr_tab IS TABLE OF t_pr_rec; l_pr_data t_pr_tab; l_return_status VARCHAR2(10); l_msg_count NUMBER; l_msg_data VARCHAR2(4000); BEGIN -- 1. 查找已审批但未完全转换的PR SELECT prh.requisition_header_id, prh.segment1, COUNT(prl.requisition_line_id) AS line_count BULK COLLECT INTO l_pr_data FROM po_requisition_headers_all prh JOIN po_requisition_lines_all prl ON prl.requisition_header_id prh.requisition_header_id LEFT JOIN po_req_distributions_all prd ON prd.requisition_line_id prl.requisition_line_id WHERE prh.org_id p_org_id AND prh.authorization_status APPROVED AND prh.type PURCHASE AND NOT EXISTS ( SELECT 1 FROM po_distributions_all pda WHERE pda.req_distribution_id prd.distribution_id ) AND (p_buyer_id IS NULL OR prh.preparer_id p_buyer_id) GROUP BY prh.requisition_header_id, prh.segment1; -- 2. 对每个PR执行AutoCreate FOR i IN 1..l_pr_data.COUNT LOOP BEGIN -- 设置上下文 MO_GLOBAL.SET_POLICY_CONTEXT(S, p_org_id); -- 调用AutoCreate核心过程 PO_INTERFACE_S.CREATE_DOCUMENTS( p_req_header_id l_pr_data(i).req_header_id, p_doc_type STANDARD, p_merge Y, -- 合并同一供应商行 p_batch_id NULL, x_return_status l_return_status, x_msg_count l_msg_count, x_msg_data l_msg_data ); IF l_return_status S THEN COMMIT; DBMS_OUTPUT.PUT_LINE(PR || l_pr_data(i).req_number || - PO created successfully); ELSE ROLLBACK; DBMS_OUTPUT.PUT_LINE(FAILED: PR || l_pr_data(i).req_number || Error: || l_msg_data); END IF; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE(ERROR: PR || l_pr_data(i).req_number || Exception: || SQLERRM); END; END LOOP; END auto_create_po_from_pr;五、通用自动化框架错误处理与监控每次调用API必须捕获错误推荐统一封装CREATE OR REPLACE PACKAGE PO_AUTO_ENGINE IS -- 通用API调用包装器 FUNCTION call_control_document( p_po_header_id IN NUMBER, p_action IN VARCHAR2, -- CANCEL/CLOSE/FINALLY_CLOSE p_reason IN VARCHAR2 DEFAULT NULL ) RETURN VARCHAR2; -- SUCCESS/FAILURE -- 批量处理入口 PROCEDURE batch_process( p_mode IN VARCHAR2, -- CLOSE_COMPLETED / CANCEL_EXPIRED / AUTO_CREATE_PR p_org_id IN NUMBER ); END PO_AUTO_ENGINE; / CREATE OR REPLACE PACKAGE BODY PO_AUTO_ENGINE IS FUNCTION call_control_document( p_po_header_id IN NUMBER, p_action IN VARCHAR2, p_reason IN VARCHAR2 DEFAULT NULL ) RETURN VARCHAR2 IS l_return_status VARCHAR2(10); l_msg_data VARCHAR2(4000); BEGIN po_document_control_pub.control_document( p_api_version 1.0, p_init_msg_list FND_API.G_TRUE, p_commit FND_API.G_FALSE, x_return_status l_return_status, p_doc_type PO, p_doc_subtype STANDARD, p_doc_id p_po_header_id, p_action p_action, p_action_date SYSDATE, p_cancel_reason p_reason, p_cancel_reqs_flag N, p_org_id NULL ); IF l_return_status FND_API.G_RET_STS_SUCCESS THEN COMMIT; RETURN SUCCESS; ELSE ROLLBACK; -- 收集错误消息 FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP FND_MSG_PUB.Get(p_msg l_msg_data, p_encoded F); FND_FILE.PUT_LINE(FND_FILE.LOG, l_msg_data); END LOOP; RETURN FAILURE: || l_msg_data; END IF; EXCEPTION WHEN OTHERS THEN ROLLBACK; RETURN EXCEPTION: || SQLERRM; END call_control_document; PROCEDURE batch_process( p_mode IN VARCHAR2, p_org_id IN NUMBER ) IS BEGIN -- 初始化会话 FND_GLOBAL.APPS_INITIALIZE(...); MO_GLOBAL.SET_POLICY_CONTEXT(S, p_org_id); CASE p_mode WHEN CLOSE_COMPLETED THEN DECLARE l_count NUMBER; BEGIN l_count : auto_close_completed_pos(p_org_id); FND_FILE.PUT_LINE(FND_FILE.OUTPUT, Closed || l_count || POS.); END; WHEN CANCEL_EXPIRED THEN -- 取消超过有效期的PO FOR rec IN ( SELECT po_header_id, segment1 FROM po_headers_all WHERE org_id p_org_id AND closed_code OPEN AND NVL(end_date, SYSDATE - 1) SYSDATE ) LOOP FND_FILE.PUT_LINE(FND_FILE.LOG, call_control_document(rec.po_header_id, CANCEL, Expired)); END LOOP; WHEN AUTO_CREATE_PR THEN auto_create_po