本文详细介绍了使用Oracle E-Business Suite API创建销售订单的过程。包括环境变量初始化、订单头及行记录设置、订单状态控制等关键步骤，并提供了一段完整的PL/SQL示例代码。
创建销售订单API主要注意几点：
- 初始化环境变量：fnd_global.apps_initialize(); mo_global.init('ONT'); mo_global.set_policy_context('S', OU_ID);
- 订单状态：订单状态在给值的时候给：booked_flag:='N'; flow_status_code:='ENTERED';
然后在行上要记得控制订单状态：
--登記銷售訂單
l_action_request_tbl(l_line_tbl_index).entity_code := oe_globals.g_entity_header;
l_action_request_tbl(l_line_tbl_index).request_type := oe_globals.g_book_order;
- 提交API时参数要正确：输入的参数和输出的参数不能用同一个变量名，即便是类型一样，也要取两个变量名
创建销售订单(SO)-API 示例代码如下：
PROCEDURE main(errbuf OUT VARCHAR2, retcode OUT VARCHAR2) IS /********************銷售訂單參數********************/ l_header_rec oe_order_pub.header_rec_type; l_line_tbl oe_order_pub.line_tbl_type; l_action_request_tbl oe_order_pub.request_tbl_type; l_header_adj_tbl oe_order_pub.header_adj_tbl_type; l_line_adj_tbl oe_order_pub.line_adj_tbl_type; l_header_scr_tbl oe_order_pub.header_scredit_tbl_type; l_line_scredit_tbl oe_order_pub.line_scredit_tbl_type; l_request_rec oe_order_pub.request_rec_type; l_return_status VARCHAR2(1000); l_msg_count NUMBER; l_msg_data VARCHAR2(1000); p_api_version_number NUMBER := 1.0; p_init_msg_list VARCHAR2(10) := fnd_api.g_true; p_return_values VARCHAR2(10) := fnd_api.g_false; p_action_commit VARCHAR2(10) := fnd_api.g_false; x_return_status VARCHAR2(1); x_msg_count NUMBER; x_msg_data VARCHAR2(100); p_header_rec oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec; p_old_header_rec oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec; p_header_val_rec oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec; p_old_header_val_rec oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec; p_header_adj_tbl oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl; p_old_header_adj_tbl oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl; p_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl; p_old_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl; p_header_price_att_tbl oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl; p_old_header_price_att_tbl oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl; p_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl; p_old_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl; p_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl; p_old_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl; p_header_scredit_tbl oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl; p_old_header_scredit_tbl oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl; p_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl; p_old_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl; p_line_tbl oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl; p_old_line_tbl oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl; p_line_val_tbl oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl; p_old_line_val_tbl oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl; p_line_adj_tbl oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl; p_old_line_adj_tbl oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl; p_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl; p_old_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl; p_line_price_att_tbl oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl; p_old_line_price_att_tbl oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl; p_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl; p_old_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl; p_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl; p_old_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl; p_line_scredit_tbl oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl; p_old_line_scredit_tbl oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl; p_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl; p_old_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl; p_lot_serial_tbl oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl; p_old_lot_serial_tbl oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl; p_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl; p_old_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl; p_action_request_tbl oe_order_pub.request_tbl_type := oe_order_pub.g_miss_request_tbl; l_header_rec_out oe_order_pub.header_rec_type; l_line_tbl_out oe_order_pub.line_tbl_type; x_header_val_rec oe_order_pub.header_val_rec_type; x_header_adj_tbl oe_order_pub.header_adj_tbl_type; x_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type; x_header_price_att_tbl oe_order_pub.header_price_att_tbl_type; x_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type; x_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type; x_header_scredit_tbl oe_order_pub.header_scredit_tbl_type; x_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type; x_line_val_tbl oe_order_pub.line_val_tbl_type; x_line_adj_tbl oe_order_pub.line_adj_tbl_type; x_line_adj_val_tbl oe_order_pub.line_adj_val_tbl_type; x_line_price_att_tbl oe_order_pub.line_price_att_tbl_type; x_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type; x_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type; x_line_scredit_tbl oe_order_pub.line_scredit_tbl_type; x_line_scredit_val_tbl oe_order_pub.line_scredit_val_tbl_type; x_lot_serial_tbl oe_order_pub.lot_serial_tbl_type; x_lot_serial_val_tbl oe_order_pub.lot_serial_val_tbl_type; x_action_request_tbl oe_order_pub.request_tbl_type; x_debug_file VARCHAR2(100); l_line_tbl_index NUMBER; l_msg_index_out NUMBER(10); --参数 v_so_msg VARCHAR2(2000) := NULL; v_return_code VARCHAR2(10) := 'S'; v_return_msg VARCHAR2(2000) := NULL; BEGIN dbms_output.put_line('***************創建銷售訂單开始***************'); --初始化SO环境变量 BEGIN -- Get the user_id SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = l_user_name; -- Get the application_id and responsibility_id SELECT application_id, responsibility_id INTO l_application_id, l_resp_id FROM fnd_responsibility_vl WHERE responsibility_name = l_resp_name; --oe_msg_pub.initialize; fnd_global.apps_initialize(user_id => /*fnd_global.user_id,*/ l_user_id, resp_id => /*fnd_global.resp_id,*/ l_resp_id, resp_appl_id => /*fnd_global.resp_appl_id*/ l_application_id); mo_global.init('ONT'); mo_global.set_policy_context('S', 349); --OU_ID END; --Create Header record --Initialize header record to missing l_header_rec := oe_order_pub.g_miss_header_rec; l_header_rec.org_id := v_org_id; --業務實體OU_ID l_header_rec.ship_to_customer_id := v_account_id; --客戶Account_ID l_header_rec.ship_to_org_id := v_so_head.ship_to_site_id; --收貨地點 --437137 l_header_rec.invoice_to_org_id := v_so_head.bill_to_site_id; --收單地點 --437135 l_header_rec.sold_to_org_id := v_account_id; --ACCOUNT_ID l_header_rec.order_type_id := v_order_type_id; --訂單類型ID l_header_rec.ordered_date := SYSDATE; --訂購日期 l_header_rec.price_list_id := 453581; --價目表(可以由訂單類型出，可以不赋值) l_header_rec.salesrep_id := v_salesrep_id; --銷售人員ID --100073147 l_header_rec.booked_flag := 'N'; l_header_rec.flow_status_code := 'ENTERED'; --狀態 说明：销售订单在创建的时候，API都默认给订单只能为ENTERED的状态，如赋值BOOKED也不行，系统也会是ENDTERED，这个状态只能在下面行赋值后控制 l_header_rec.booked_date := SYSDATE; l_header_rec.transactional_curr_code := v_so_head.currency_code; --幣種 l_header_rec.payment_term_id := v_so_head.payment_term_id; --付款條件ID l_header_rec.packing_instructions := v_so_head.customer_item_name; --包裝說明（主品名） l_header_rec.sales_document_name := v_so_head.sales_document_name; --客戶料號 l_header_rec.cust_po_number := v_so_head.purchase_order_num; --客戶採購單 l_header_rec.request_date := SYSDATE; --交貨日期 l_header_rec.ship_from_org_id := 122; --库存组织ID l_header_rec.order_source_id := v_order_source_id; --訂單來源 l_header_rec.orig_sys_document_ref := 'CPQ'; --訂單來源參考 l_header_rec.CONTEXT := 'TWGV_INV'; --上下文 l_header_rec.attribute10 := v_so_head.cpq_transaction_id; --CPQ_transaction_id l_header_rec.attribute9 := v_so_head.is_f; --是否為F單 l_header_rec.attribute4 := v_so_head.is_partial_shipment; --可分批出貨 l_header_rec.shipping_instructions := v_so_head.shipping_instructure; --發貨說明 l_header_rec.attribute7 := v_so_head.attribute3; --發票開立方式 l_header_rec.attribute5 := v_taxes_code; --稅籍編號 l_header_rec.order_category_code := 'ORDER'; l_header_rec.operation := oe_globals.g_opr_create; --关键字：创建订单 /* **说明：如果要创建多行，则可以用游标循环 l_line_tbl_index 行号，循环行就可以 */ --CREAETE LINE RECORD l_line_tbl.DELETE; l_line_tbl_index := 1; -- Initialize record to missing l_line_tbl(l_line_tbl_index) := oe_order_pub.g_miss_line_rec; -- Line attributes l_line_tbl(l_line_tbl_index).inventory_item_id := v_inventory_item_id; --物料id l_line_tbl(l_line_tbl_index).ordered_quantity := v_so_line.order_qty; --订购数量 l_line_tbl(l_line_tbl_index).order_quantity_uom := v_so_line.inventory_item_uom; --订购单位 l_line_tbl(l_line_tbl_index).calculate_price_flag := 'N'; --控制是否從 价目表 price-list 帶出价格，N：不带出，自行赋值； Y：销售单价由价目表带出，不用赋值 l_line_tbl(l_line_tbl_index).unit_selling_price := 12; --銷售單價 l_line_tbl(l_line_tbl_index).request_date := l_header_rec.request_date; --请求日期 l_line_tbl(l_line_tbl_index).schedule_ship_date := l_header_rec.request_date; --workflow 將 SO Line Request date 跳過去(若不是假日) l_line_tbl(l_line_tbl_index).line_type_id := v_line_type_id; --行類型 l_line_tbl(l_line_tbl_index).tax_code := v_tax_rate_code; --税码 l_line_tbl(l_line_tbl_index).cust_po_number := v_so_line.purchase_order_num; --客戶採購單單號 l_line_tbl(l_line_tbl_index).customer_line_number := v_so_line.requisition_num; --客戶請購單號 l_line_tbl(l_line_tbl_index).subinventory := 'BTC'; --库存编码 l_line_tbl(l_line_tbl_index).ship_from_org_id := 385; --子庫存385(库存组织id) l_line_tbl(l_line_tbl_index).CONTEXT := v_org_id; --行信息上下文(OU_ID) l_line_tbl(l_line_tbl_index).attribute17 := v_so_line.cpq_transaction_lineid; --CPQ_TXN_Line_ID l_line_tbl(l_line_tbl_index).booked_flag := 'N'; --这个应该也可以不用赋值，应该会跟着头走 l_line_tbl(l_line_tbl_index).schedule_arrival_date := SYSDATE; l_line_tbl(l_line_tbl_index).orig_sys_line_ref := v_so_line.cpq_transaction_lineno; --客制标识 --l_line_tbl(l_line_tbl_index).flow_status_code := 'SUPPLY_ELIGIBLE'; --不用赋值行状态 /* **说明：如果前面的calculate_price_flag为N,就是不从价目表带出价格，则需要手动赋值行的unit_list_price，如果前面为Y，则不需要 */ --賦值：unit_list_price IF l_line_tbl(l_line_tbl_index).price_list_id IS NOT NULL AND l_line_tbl(l_line_tbl_index).calculate_price_flag = 'N' AND l_line_tbl(l_line_tbl_index).unit_selling_price IS NOT NULL THEN l_line_tbl(l_line_tbl_index).unit_list_price := 0; END IF; l_line_tbl(l_line_tbl_index).operation := oe_globals.g_opr_create; /* **说明：控制订单是否登记:BOOKED */ --登記銷售訂單 l_action_request_tbl(l_line_tbl_index).entity_code := oe_globals.g_entity_header; l_action_request_tbl(l_line_tbl_index).request_type := oe_globals.g_book_order; --控制登記動作 /* **说明：如果需要生成多行，则可以再循环里递增这个行号 **l_line_tbl_index := l_line_tbl_index + 1; */ -- CALL API oe_order_pub.process_order(p_api_version_number => p_api_version_number, p_init_msg_list => p_init_msg_list, --是否每次初始化返回信息的list：fnd_api.g_true (TRUE则每调用一次清空，false则不清空) p_return_values => p_return_values, --fnd_api.g_false p_action_commit => p_action_commit, --是否自动提交：fnd_api.g_false x_return_status => l_return_status, --API 返回是否成功状态 x_msg_count => l_msg_count, --API 返回错误信息的条数 x_msg_data => l_msg_data, --API 返回错误信息的数据 p_header_rec => l_header_rec, --输入：头信息 p_line_tbl => l_line_tbl, --输入：行信息 p_action_request_tbl => l_action_request_tbl, --输入：控制状态信息 -- OUT PARAMETERS x_header_rec => l_header_rec_out, --输出：头信息；注意：不能和输入的头信息参数一样，必须用新变量 x_header_val_rec => x_header_val_rec, x_header_adj_tbl => x_header_adj_tbl, x_header_adj_val_tbl => x_header_adj_val_tbl, x_header_price_att_tbl => x_header_price_att_tbl, x_header_adj_att_tbl => x_header_adj_att_tbl, x_header_adj_assoc_tbl => x_header_adj_assoc_tbl, x_header_scredit_tbl => x_header_scredit_tbl, x_header_scredit_val_tbl => x_header_scredit_val_tbl, x_line_tbl => l_line_tbl_out, --输出：行信息；注意：不能和输入的行信息参数一样，必须用新变量 x_line_val_tbl => x_line_val_tbl, x_line_adj_tbl => x_line_adj_tbl, x_line_adj_val_tbl => x_line_adj_val_tbl, x_line_price_att_tbl => x_line_price_att_tbl, x_line_adj_att_tbl => x_line_adj_att_tbl, x_line_adj_assoc_tbl => x_line_adj_assoc_tbl, x_line_scredit_tbl => x_line_scredit_tbl, x_line_scredit_val_tbl => x_line_scredit_val_tbl, x_lot_serial_tbl => x_lot_serial_tbl, x_lot_serial_val_tbl => x_lot_serial_val_tbl, x_action_request_tbl => x_action_request_tbl); --检查返回状态 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
v_return_msg := NULL; --初始化信息 -- Retrieve messages FOR i IN 1 .. l_msg_count LOOP l_msg_data := oe_msg_pub.get(p_msg_index => i, p_encoded => 'F'); v_so_msg := v_so_msg || l_msg_data; END LOOP; v_return_code := 'E'; v_return_msg := v_so_msg; /* **说明：按需要回滚，如果和物料一起在一个session ，则可以回滚物料，如果单独的销售订单创建则可以不需要 **v_so_count_e := v_so_count_e + 1; --按需要可记录失败的条数 **ROLLBACK; */ dbms_output.put_line('創建銷售訂單失敗：' || v_return_code || ':' || v_return_msg); ELSE v_return_code := 'S'; v_return_msg := '訂單編號(' || l_header_rec_out.order_number || '); 销售订单ID(' || l_header_rec_out.header_id || ')'; /* **v_so_count_s := v_so_count_s + 1; --按需要可记录成功的条数 */ COMMIT; dbms_output.put_line('***************創建銷售訂單成功：銷售訂單編號(' || l_header_rec_out.order_number || ')'); /* **说明：可以获取头行信息 --头信息 dbms_output.put_line('销售订单头ID:' || l_header_rec_out.header_id); dbms_output.put_line('销售订单头编号:' || l_header_rec_out.order_number); dbms_output.put_line('销售订单头状态:' || l_header_rec_out.flow_status_code); --循環獲取行 FOR v_line_count IN 1 .. l_line_tbl_out.COUNT LOOP dbms_output.put_line('销售订单行ID:' || l_line_tbl_out(v_line_count).line_id); dbms_output.put_line('销售订单行状态:' || l_line_tbl_out(v_line_count).flow_status_code); END LOOP; */ END IF; EXCEPTION WHEN OTHERS THEN v_return_code := 'E'; v_return_msg := '程序出現異常(MAIN),請聯繫管理員：' || SQLCODE || ':' || SQLERRM; retcode := 2; dbms_output.put_line(v_return_code || ':' || v_return_msg); /* **v_so_count_s := v_so_count_s + 1; --按需要可记录成功的条数 **ROLLBACK; */ END;
扫一扫
1341
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
