EBS采用API生成标准采购订单
程序头
create or replace package cux_po_mould_std_order_pub is /*=============================================== copyright (c) ibm global business services allrights reserved ================================================ * =============================================== * program name: * cux_po_mould_std_order_pub * description: * EBS回复BPM模具标准采购订单接口 * history: * 1.00 2019-08-27 iven.lin creation * * ==============================================*/ g_transaction_id number; --记录调用历史记录ID type rec_header is record( vend_code varchar2(100), vend_name varchar2(1000), tax_rate varchar2(1000), bmp_no varchar2(1000), org_name varchar2(1000), agent_number varchar2(100)); type rec_line is record( /* 物料编码 item_code 含税单价 market_price*/ item_code varchar2(1000), market_price varchar2(100)); procedure trx_parse_json(p_json_clob in clob, -- x_header_tbl out t_header_tbl, -- x_line_tbl out t_line_tbl, x_return_code out varchar2, x_return_mesg out varchar2); procedure trx_parse(p_transaction_id in number, p_request_data in clob, p_bacth_number in varchar2, x_return_code out varchar2, x_return_mesg out varchar2, x_response_data out clob); procedure trx_import(p_transaction_id in number, x_return_code out varchar2, x_return_mesg out varchar2, x_response_data out clob); function get_po_number(p_org_id number, p_type varchar2, p_organization_id number) return varchar2; /* =============================================================== * procedure * name : final_close_pr * * description: 关闭请购单，在创建请购单成功后，再关闭请购单 * argument: p_req_header_id 请购单头ID p_po_type 请购单行ID x_return_status 返回状态码 x_return_mesg 返回信息 * * * history: * 1.00 2019-10-17 iven.lin creation * ===============================================================*/ procedure final_close_pr(p_req_header_id in number, p_req_line_id in number, x_return_status out varchar2, x_return_mesg out varchar2); end cux_po_mould_std_order_pub;
程序包体
create or replace package body cux_po_mould_std_order_pub as g_pkg_name constant varchar2(30) := 'cux_po_mould_std_order_pub'; g_debug varchar2(1) := nvl(fnd_profile.value('CUX_WS_ALLOW_TRANSFER_LOG'), 'Y'); g_success constant varchar2(8) := cux_ws_common_utl.g_ret_sts_success; g_error constant varchar2(8) := cux_ws_common_utl.g_ret_sts_exception; g_app_name fnd_application.application_short_name%type := cux_fnd_api.g_app_name; g_iface_param cux_ws_common_utl.cux_ws_param; g_err_xml constant varchar2(8) := cux_ws_common_utl.g_err_xml; g_api_type constant varchar2(30) := ''; g_moudle varchar2(2) := 'OM'; g_iface_code varchar2(200) := 'cux_po_mould_std_order_pub'; ---定义调用接口代码 g_user_id number := fnd_global.user_id; g_login_id number := fnd_global.login_id; g_employee_id number := fnd_global.employee_id; g_document_number varchar2(100); --g_transaction_id number; --记录调用历史记录ID --g_order_info order_info_tal; procedure write_to_clob(p_buffer in varchar2, x_clob in out clob) is v_writing_position number := 0; v_amount number; begin v_amount := length(p_buffer); v_writing_position := dbms_lob.getlength(x_clob) + 1; dbms_lob.write(x_clob, v_amount, v_writing_position, p_buffer); end; function replace_string_json(p_string in varchar2) return varchar2 is l_temp varchar2(30000); begin l_temp := p_string; l_temp := replace(l_temp, '\', '\\'); l_temp := replace(l_temp, '''', '\'''); l_temp := replace(l_temp, '"', '\"'); return l_temp; end replace_string_json; procedure trx_parse_json(p_json_clob in clob, x_return_code out varchar2, x_return_mesg out varchar2) is temparray json_list; level_1 json_list; header_j json; line_j json; v_return_status varchar2(40) := 'S'; v_return_mesg varchar2(4000); l_exception_msg varchar2(4000); v_po_headers_all_tbl po.po_headers_interface%rowtype; v_po_lines_all_tbl po_lines_interface%rowtype; v_po_distributions_interface po_distributions_interface%rowtype; v_po_line_locations_interface po_line_locations_interface%rowtype; l_line_count number; v_header_id number; v_vend_id number; v_vend_site_id number; v_org_id number; v_org_shortname varchar2(20); v_bill_to_id number; v_ship_to_id number; v_agent_id number; --采购员ID v_accrual_account_id number; v_currency_code varchar2(20); v_document_number varchar2(100); v_item_id number; v_batch_id number; v_po_header_id number; v_bmp_no varchar2(100); v_bmp_count number; v_interface_count number; v_agent_count number; --判断是不是采购员 v_req_id number; --采购申请ID v_req_line number; --采购申请行ID v_req_line_num number; --采购申请行号 x_return_status varchar2(100); v_char varchar2(200); v_api_name varchar2(100) := upper('TRX_PARSE_JSON'); v_err_mseg varchar2(100) := g_pkg_name || '.' || v_api_name || ':'; v_num number := 0; l_org_code VARCHAR2(100); l_retcode VARCHAR2(100); l_errbuff VARCHAR2(200); l_cnt NUMBER:=0; l_request_id NUMBER; l_b_result BOOLEAN; l_chr_phase VARCHAR2(500); l_chr_status VARCHAR2(500); l_chr_dev_phase VARCHAR2(500); l_chr_dev_status VARCHAR2(500); l_chr_message VARCHAR2(500); begin x_return_code := fnd_api.g_ret_sts_success; --json格式：p_request_data :='[{"vend_code":"B041","vend_name":"东莞市贤华实业有限公司","tax_rate":"3","bmp_no":"11", --"org_name":"OU_101_深圳新桥厂","agent_number":"15328", --"bmp_type":"01","req_number":"1012000018", --"table_line_table":[{"item_code":"4020000008011304","market_price":"11","quantity":"1000,"need_by_date":"2019-09-01","po_list_number":"123456789""}, --{"item_code":"2020050800041532","market_price":"11","need_by_date":"2019-09-01","po_list_number":"123456789"}]}]'; --返加结果： [ {"PO_NUMBER":"XQ1011124332","RETURN_STATUS":"S"}] temparray := json_list(p_json_clob); l_line_count := 0; --最外层的[],有几个header就有几个 for i in 1 .. temparray.count loop select po_headers_interface_s.nextval into v_header_id from dual; header_j := json(temparray.get(i)); --v_po_headers_all_tbl.interface_header_id; v_po_headers_all_tbl.creation_date := sysdate; v_po_headers_all_tbl.created_by := g_user_id; v_po_headers_all_tbl.last_update_date := sysdate; v_po_headers_all_tbl.last_updated_by := g_user_id; v_po_headers_all_tbl.last_update_login := g_login_id; v_po_headers_all_tbl.interface_header_id := v_header_id; v_batch_id := v_header_id; v_po_headers_all_tbl.batch_id := v_batch_id; --根据ORG NAME取ORG ID v_org_shortname := substr(json_ext.get_string(header_j, 'org_name'), 1, 6); select hou.organization_id into v_org_id from hr_operating_units hou where hou.short_code = v_org_shortname; v_po_headers_all_tbl.org_id := v_org_id; v_bmp_no := json_ext.get_string(header_j, 'bpm_no');
SELECT organization_code INTO l_org_code FROM org_organization_definitions t WHERE t.OPERATING_UNIT = v_org_id AND rownum = 1; l_cnt := l_cnt + 1; IF l_cnt = 1 THEN cux_common_pkg.init_application(p_org_code => l_org_code, p_moudle => 'PO', p_user_id => 1430, x_return_status => l_retcode, x_return_mesg => l_errbuff); END IF; --根据流程编号判断是否已经生成采购订单 begin select count(1) into v_bmp_count from po_headers_all pla where pla.org_id = v_org_id and pla.attribute2 = v_bmp_no and pla.type_lookup_code = 'STANDARD'; exception when no_data_found then v_bmp_count := 0; end; if v_bmp_count > 0 then v_return_mesg := v_return_mesg || '该流程号已经生成采购订单!'; raise fnd_api.g_exc_error; end if; begin select count(1) into v_interface_count from po_lines_interface pli, po_headers_all pha where 1 = 1 and pli.po_header_id = pha.po_header_id and pha.org_id = v_org_id and pha.type_lookup_code = 'STANDARD' and pli.line_attribute5 = v_bmp_no; exception when no_data_found then v_interface_count := 0; end; if v_interface_count > 0 then v_return_mesg := v_return_mesg || '该流程号已经存在于接口表!'; raise fnd_api.g_exc_error; end if; --根据供应商代码取VEND_ID,SITE_ID begin select ap.vendor_id, aps.vendor_site_id, aps.bill_to_location_id, aps.ship_to_location_id, aps.invoice_currency_code into v_vend_id, v_vend_site_id, v_bill_to_id, v_ship_to_id, v_currency_code from ap_suppliers ap, ap_supplier_sites_all aps where ap.vendor_id = aps.vendor_id and aps.vendor_site_code = '费用采购' and aps.org_id = v_org_id and ap.segment1 = json_ext.get_string(header_j, 'vend_code'); exception when no_data_found then v_return_mesg := v_return_mesg || '供应商代码有误!'; raise fnd_api.g_exc_error; end; v_po_headers_all_tbl.vendor_id := v_vend_id; v_po_headers_all_tbl.vendor_site_id := v_vend_site_id; v_po_headers_all_tbl.bill_to_location_id := v_bill_to_id; v_po_headers_all_tbl.ship_to_location_id := v_ship_to_id; begin select f.person_id into v_agent_id from per_people_f f where f.employee_number = json_ext.get_string(header_j, 'agent_number'); exception when no_data_found then v_return_mesg := v_return_mesg || '采购员错误!'; raise fnd_api.g_exc_error; end; --判断是不是采购员 begin select count(1) into v_agent_count from po_agents poagenteo, per_all_people_f papf, po_ship_to_loc_org_v psl, mtl_categories_kfv mkfv, po_user_warrants pw where poagenteo.agent_id = papf.person_id and poagenteo.category_id = mkfv.category_id(+) and poagenteo.warrant_id = pw.warrant_id(+) and (papf.employee_number is not null or papf.npw_number is not null) and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and decode(hr_general.get_xbg_profile, 'Y', papf.business_group_id, hr_general.get_business_group_id) = papf.business_group_id and poagenteo.location_id = psl.location_id(+) and poagenteo.agent_id = v_agent_id; exception when no_data_found then v_return_mesg := v_return_mesg || '您还不是采购员!'; raise fnd_api.g_exc_error; end; v_po_headers_all_tbl.agent_id := v_agent_id; v_po_headers_all_tbl.currency_code := v_currency_code; v_po_headers_all_tbl.attribute_category := 'STANDARD'; v_po_headers_all_tbl.interface_source_code := 'BPM API'; v_document_number := get_po_number(v_org_id, 'PO_HEADERS', v_org_id); g_document_number := v_document_number; v_po_headers_all_tbl.document_num := v_document_number; v_po_headers_all_tbl.process_code := 'PENDING'; v_po_headers_all_tbl.action := 'ORIGINAL'; v_po_headers_all_tbl.document_type_code := 'STANDARD'; -- v_po_headers_all_tbl.approval_status := 'INCOMPLETE'; v_po_headers_all_tbl.approval_status := 'APPROVED'; v_po_headers_all_tbl.attribute_category := 'STANDARD'; v_po_headers_all_tbl.attribute2 := v_bmp_no; if json_ext.get_string(header_j, 'bpm_type') = '01' then v_po_headers_all_tbl.attribute1 := '模具采购'; else v_po_headers_all_tbl.attribute1 := '改模合同'; end if; v_po_headers_all_tbl.attribute7 := json_ext.get_string(header_j, 'reserve_qty'); v_po_headers_all_tbl.attribute8 := json_ext.get_string(header_j, 'reserve_amount'); --2020-11-16 task#4435 liu Add Start v_po_headers_all_tbl.attribute10 := json_ext.get_string(header_j, 'reserve_year'); --2020-11-16 task#4435 liu Add End insert into po.po_headers_interface values v_po_headers_all_tbl; --行循环 level_1 := json_list(header_j.get('table_line_table')); for k in 1 .. level_1.count loop v_num := 0; l_line_count := l_line_count + 1; line_j := json(level_1.get(k)); v_po_lines_all_tbl.interface_header_id := v_header_id; select po_lines_interface_s.nextval into v_po_lines_all_tbl.interface_line_id from dual; --set who v_po_lines_all_tbl.creation_date := sysdate; v_po_lines_all_tbl.created_by := g_user_id; v_po_lines_all_tbl.last_update_date := sysdate; v_po_lines_all_tbl.last_updated_by := g_user_id; v_po_lines_all_tbl.last_update_login := g_login_id; v_po_lines_all_tbl.line_num := l_line_count; v_po_lines_all_tbl.shipment_num := 1; v_po_lines_all_tbl.quantity := json_ext.get_string(line_j, 'quantity'); begin select msi.inventory_item_id into v_item_id from mtl_system_items_b msi where msi.segment1 = json_ext.get_string(line_j, 'item_code') and msi.organization_id = 122; exception when no_data_found then v_return_mesg := v_return_mesg || '物料编码错误!'; raise fnd_api.g_exc_error; end; v_po_lines_all_tbl.item_id := v_item_id; -- v_po_lines_all_tbl.market_price := 1; -- v_po_lines_all_tbl.UNIT_PRICE := 1; v_po_lines_all_tbl.market_price := json_ext.get_string(line_j, 'market_price'); v_po_lines_all_tbl.unit_price := json_ext.get_string(line_j, 'market_price') / (1 + replace(json_ext.get_string(header_j, 'tax_rate'), '%') / 100); -- v_po_lines_all_tbl.UNIT_PRICE :=json_ext.get_string(header_j,'tax_rate'); --v_po_lines_all_tbl.AMOUNT := v_po_lines_all_tbl.market_price * v_po_lines_all_tbl.quantity; v_po_lines_all_tbl.need_by_date := to_date(json_ext.get_string(line_j, 'need_by_date'), 'yyyy-mm-dd'); v_po_lines_all_tbl.line_attribute_category_lines := 'STANDARD'; v_po_lines_all_tbl.line_attribute1 := json_ext.get_string(line_j, 'po_list_number'); --根据申请单号和物料编号，取申请号ID begin select prh.requisition_header_id into v_req_id from po_requisition_headers_all prh where prh.segment1 = json_ext.get_string(line_j, 'req_number'); exception when no_data_found then v_req_id := null; v_return_mesg := v_return_mesg || '已生成采购订单或采购申请单号不存在!'; raise fnd_api.g_exc_error; end; --取申请行ID v_accrual_account_id := null; if v_req_id is not null then begin -- add by iven.lin 2023-07-03 select prl.requisition_line_id, prl.line_num into v_req_line, v_req_line_num from po_requisition_lines_all prl where prl.requisition_header_id = v_req_id and prl.item_id = v_item_id and nvl(prl.closed_code, 'OPEN') <> 'FINALLY CLOSED'; -- design by leejun select prda.code_combination_id into v_accrual_account_id from po_req_distributions_all prda where prda.requisition_line_id = v_req_line; --end by leejun exception when no_data_found then v_req_line := null; --12345678; --null; v_return_mesg := v_return_mesg || '已生成采购订单或采购申请单号不存在!'; raise fnd_api.g_exc_error; when Too_many_rows then v_num := v_num + 1; select min(prl.requisition_line_id) --, min(prl.line_num) into v_req_line --, v_req_line_num from po_requisition_lines_all prl where prl.requisition_header_id = v_req_id and prl.item_id = v_item_id and nvl(prl.closed_code, 'OPEN') <> 'FINALLY CLOSED'; select p.line_num into v_req_line_num from po_requisition_lines_all p where p.REQUISITION_LINE_ID = v_req_line; v_req_line_num := v_req_line_num + v_num; end; end if; -- v_po_line_locations_interface.ACCRUE_ON_RECEIPT_FLAG='N'; v_po_lines_all_tbl.line_attribute4 := v_req_line; -- v_po_lines_all_tbl.c -- v_po_lines_all_tbl.ITEM_ATTRIBUTE5:= v_org_id; insert into po_lines_interface values v_po_lines_all_tbl; v_po_distributions_interface.interface_header_id := v_header_id; v_po_distributions_interface.interface_line_id := v_po_lines_all_tbl.interface_line_id; v_po_distributions_interface.interface_distribution_id := po_distributions_interface_s.nextval; v_po_distributions_interface.quantity_ordered := json_ext.get_string(line_j, 'quantity'); v_po_distributions_interface.req_header_reference_num := json_ext.get_string(line_j, 'req_number'); v_po_distributions_interface.req_line_reference_num := v_req_line_num; v_po_distributions_interface.charge_account_id := v_accrual_account_id; --v_po_distributions_interface.DEST_CHARGE_ACCOUNT_ID := v_ACCRUAL_ACCOUNT_ID;--add by lwd at 20191214 --v_po_distributions_interface.VARIANCE_ACCOUNT_ID := v_ACCRUAL_ACCOUNT_ID; v_po_distributions_interface.last_update_date := sysdate; v_po_distributions_interface.last_updated_by := g_user_id; v_po_distributions_interface.creation_date := sysdate; v_po_distributions_interface.created_by := g_user_id; v_po_distributions_interface.last_update_login := g_login_id; v_po_distributions_interface.attribute_category := null; v_po_distributions_interface.attribute1 := null; v_po_distributions_interface.attribute2 := null; v_po_distributions_interface.attribute3 := null; v_po_distributions_interface.attribute4 := null; v_po_distributions_interface.attribute5 := null; v_po_distributions_interface.attribute6 := null; v_po_distributions_interface.attribute7 := null; v_po_distributions_interface.attribute8 := null; v_po_distributions_interface.attribute9 := null; v_po_distributions_interface.attribute10 := null; v_po_distributions_interface.attribute11 := null; v_po_distributions_interface.attribute12 := null; v_po_distributions_interface.attribute13 := null; v_po_distributions_interface.attribute14 := null; v_po_distributions_interface.attribute15 := null; insert into po_distributions_interface values v_po_distributions_interface; end loop; --END LOOP; mo_global.init('PO'); mo_global.set_policy_context('S', v_org_id); -- added by yinzj 20240329 越南科目问题用请求 IF v_org_id = 709 THEN l_request_id := fnd_request.submit_request('PO', 'POXPOPDOI', '', to_char(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'), FALSE, NULL, 'STANDARD', NULL, 'N', null, 'APPROVED', NULL, v_batch_id, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N', NULL, chr(0)); COMMIT; l_b_result := fnd_concurrent.wait_for_request(l_request_id, 5, 0, l_chr_phase, l_chr_status, l_chr_dev_phase, l_chr_dev_status, l_chr_message); IF l_b_result THEN IF l_chr_dev_phase = 'COMPLETE' AND l_chr_dev_status = 'NORMAL' THEN v_return_status := 'S'; ELSE v_return_status := 'E'; END IF; END IF; ELSE -- end po_docs_interface_sv5.process_po_headers_interface(x_selected_batch_id => v_batch_id, x_buyer_id => null, x_document_type => 'STANDARD', x_document_subtype => null, x_create_items => 'N', x_create_sourcing_rules_flag => null, x_rel_gen_method => null, x_approved_status => 'APPROVED', x_commit_interval => 1, x_process_code => 'PENDING', x_interface_header_id => v_header_id, x_org_id_param => null, x_ga_flag => null); END IF; if v_return_status = 'S' then begin select pa.po_header_id into v_po_header_id from po_headers_all pa where pa.segment1 = v_document_number and pa.org_id = v_org_id; exception when no_data_found then v_po_header_id := null; v_return_mesg := v_batch_id || v_return_mesg || '采购订单创建失败！' || v_org_id || v_document_number || 'qq' || v_return_status; x_return_code := fnd_api.g_ret_sts_error; -- raise fnd_api.g_exc_error; end; /* INSERT INTO CUX_MOULD_PO_FLAG (PO_NUMBER, PO_STATUS,PO_MSG) VALUES (v_po_header_id, v_document_number,'读取ID');*/ if v_po_header_id is not null then po_document_action_pvt.do_approve(p_document_id => v_po_header_id, p_document_type => 'PO', p_document_subtype => 'STANDARD', p_note => 'success', p_approval_path_id => 1, x_return_status => v_return_status, x_exception_msg => l_exception_msg); end if; end if; delete cux_mould_po_flag cm where cm.po_number = v_document_number; insert into cux_mould_po_flag (po_number, po_status, po_msg) values (v_document_number, v_return_status, l_exception_msg || ',' || v_return_mesg); commit; v_char := l_exception_msg || ',' || v_return_mesg; --- v_document_number add by iven.lin 2021-05-19 if v_po_header_id is not null and v_char = ',' then for rec_q in (select to_number(pll.attribute4) attribute4, pha.po_header_id from po_headers_all pha, po_lines_all pll where pha.po_header_id = pll.po_header_id and pha.segment1 = v_document_number and pll.attribute4 is not null) loop update po_line_locations_all set accrue_on_receipt_flag = 'N' where po_header_id = rec_q.po_header_id; update po_distributions_all pda set pda.destination_type_code = 'EXPENSE', pda.accrue_on_receipt_flag = 'N', pda.destination_context = 'EXPENSE' where pda.po_header_id = rec_q.po_header_id; commit; --根据atrribute4 取requisition_header_id for rec_close_po in (select prl.requisition_line_id, prl.requisition_header_id, prl.org_id from po_requisition_lines_all prl where prl.requisition_line_id = to_number(rec_q.attribute4)) loop po_moac_utils_pvt.set_org_context(v_org_id); final_close_pr(p_req_header_id => rec_close_po.requisition_header_id, p_req_line_id => rec_close_po.requisition_line_id, x_return_status => x_return_status, x_return_mesg => x_return_mesg); if x_return_status <> 'S' then x_return_mesg := '关闭PR错误：' || x_return_mesg; --raise fnd_api.g_exc_error; update cux_mould_po_flag set po_msg = po_msg || x_return_mesg where po_number = v_document_number; else for rec_l in (select pha.org_id, pla.item_id, to_number(pla.attribute4) attribute4, pda.line_location_id from po_headers_all pha, po_lines_all pla, po_distributions_all pda where pha.po_header_id = pla.po_header_id and pha.org_id = pla.org_id and pla.po_line_id = pda.po_line_id and pla.org_id = pda.org_id and pha.po_header_id = rec_q.po_header_id) loop update po_requisition_lines_all prel --set CLOSED_CODE = null set prel.line_location_id = rec_l.line_location_id where prel.requisition_line_id = to_number(rec_l.attribute4) and prel.item_id = rec_l.item_id; end loop; end if; end loop; /* INSERT INTO CUX_MOULD_PO_FLAG (PO_NUMBER, PO_STATUS, PO_MSG) VALUES (v_document_number, 'd', rec_q.po_header_id);*/ commit; end loop; end if; end loop; -- x_header_tbl := l_header_tbl; -- x_line_tbl := l_line_tbl; exception when others then x_return_code := fnd_api.g_ret_sts_error; x_return_mesg := 'cux_po_mould_std_order_pub' || ': ' || Dbms_Utility.Format_Error_Stack || ' ' || Dbms_Utility.Format_Error_Backtrace || ',' || v_err_mseg || '--' || v_return_mesg || sqlerrm; end; procedure log(p_log_flag in varchar2, p_transaction_id in number, p_log_message in varchar2, p_call_stack in clob default null) is pragma autonomous_transaction; begin cux_ws_common_utl.ws_log(p_log_flag => p_log_flag, p_transaction_id => p_transaction_id, p_log_message => p_log_message, p_call_stack => p_call_stack); commit; end log; /*=============================================== Copyright (C) IBM Global Business Services AllRights Reserved ================================================ * =============================================== * PROGRAM NAME: * trx_parse * DESCRIPTION: * BPM接口程序 * HISTORY: * 1.00 2019-08-27 iven.lin Creation * * ==============================================*/ procedure trx_parse(p_transaction_id in number, p_request_data in clob, p_bacth_number in varchar2, x_return_code out varchar2, x_return_mesg out varchar2, x_response_data out clob) is l_api_name varchar2(20) := 'TRX_PARSE'; l_json_return_code varchar2(30); begin x_return_code := g_success; g_transaction_id := p_transaction_id; --l_req_data := cux_ws_common_utl.decode_base64(p_request_data); --得到bpm请求的json数据 --解析json数据，插入接口表 /* fnd_global.apps_initialize(user_id => 1430, resp_id => 51037, resp_appl_id => 50202); mo_global.init('M');*/ trx_parse_json(p_json_clob => p_request_data, x_return_code => l_json_return_code, x_return_mesg => x_return_mesg); if l_json_return_code = fnd_api.g_ret_sts_success then null; else raise fnd_api.g_exc_error; end if; commit; exception when fnd_api.g_exc_error then x_return_code := g_error; return; when others then x_return_code := g_error; x_return_mesg := g_app_name || '.' || l_api_name || ':' || sqlerrm; end trx_parse; procedure trx_import(p_transaction_id in number, x_return_code out varchar2, x_return_mesg out varchar2, x_response_data out clob) is l_api_name varchar2(20) := 'trx_import'; l_report_json clob; l_rep_value clob; v_msg_count number; v_oe_header_id number; v_order_number varchar2(100); v_org_id number; begin mo_global.init('M'); x_return_code := g_success; ----数据预处理 select cmp.po_status, cmp.po_number, cmp.po_msg into x_return_code, v_order_number, x_return_mesg from cux_mould_po_flag cmp where rownum = 1 and cmp.po_number = g_document_number; if x_return_code <> 'S' then x_return_code := g_error; x_return_mesg := '创建正式订单失败:' || x_return_mesg; rollback; else l_report_json := ' '; l_rep_value := '['; write_to_clob(l_rep_value, l_report_json); l_rep_value := ' {'; write_to_clob(l_rep_value, l_report_json); l_report_json := l_report_json || '"PO_NUMBER":"' || v_order_number || '","RETURN_STATUS":"' || x_return_code || '"'; l_rep_value := '}'; write_to_clob(l_rep_value, l_report_json); l_rep_value := ']'; write_to_clob(l_rep_value, l_report_json); x_response_data := cux_ws_common_utl.encode_base64(l_report_json); x_return_code := g_success; x_return_mesg := '创建正式订单成功：' || v_order_number || ',' || x_return_mesg; end if; exception when others then rollback; x_return_code := g_error; x_return_mesg := g_app_name || '.' || l_api_name || ':' || sqlerrm; end; function get_po_number(p_org_id number, p_type varchar2, p_organization_id number) return varchar2 is v_po_number varchar2(100); cursor cur_po_number is select p.current_max_unique_identifier from po_unique_identifier_cont_all p where p.table_name = p_type and p.org_id = p_org_id for update; v_data_rec cur_po_number%rowtype; begin open cur_po_number; fetch cur_po_number into v_data_rec; if cur_po_number%notfound then close cur_po_number; return null; end if; v_po_number := v_data_rec.current_max_unique_identifier + 1; update po_unique_identifier_cont_all p set p.current_max_unique_identifier = v_po_number, p.last_update_date = sysdate, p.last_updated_by = g_user_id, p.creation_date = sysdate, p.created_by = g_user_id, p.last_update_login = g_login_id where p.table_name = p_type and p.org_id = p_org_id; close cur_po_number; if p_organization_id = 82 then v_po_number := 'XQ' || v_po_number; elsif p_organization_id = 83 then v_po_number := 'TC' || v_po_number; else v_po_number := v_po_number; end if; return v_po_number; exception when others then return null; end; procedure final_close_pr(p_req_header_id in number, p_req_line_id in number, x_return_status out varchar2, x_return_mesg out varchar2) is v_api_name varchar2(20) := 'final_close_pr'; x_req_control_error_rc varchar2(1); begin x_return_status := fnd_api.g_ret_sts_success; x_return_mesg := null; po_reqs_control_sv.update_reqs_status(p_req_header_id, p_req_line_id, '', 'REQUISITION', 'PURCHASE', 'FINALLY CLOSE', '', -- to_date(sysdate, 'DD-MON-YYYY'), --to_date(sysdate, 'YYYY-MM-DD'), sysdate, 'N', 'Y', x_req_control_error_rc --y/n ); commit; if x_req_control_error_rc = 'Y' then x_return_mesg := '关闭请购单出错，请手工关闭'; raise fnd_api.g_exc_error; end if; exception when fnd_api.g_exc_error then x_return_status := fnd_api.g_ret_sts_error; x_return_mesg := g_pkg_name || '.' || v_api_name || x_return_mesg; when others then x_return_status := fnd_api.g_ret_sts_unexp_error; x_return_mesg := g_pkg_name || '.' || v_api_name || '其他错误，请联系系统管理员:' || sqlerrm; end; end cux_po_mould_std_order_pub;
本文来自博客园，作者：Iven_lin，转载请注明原文链接：https://www.cnblogs.com/ivenlin/p/18120993
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了