procedure IMPORT_OM(p_unid varchar2, --流程ID p_CUSTOMER_PO varchar2, --合同编号 p_xmlstr varchar2, --clob,XML格式 v_out_message out varchar2, v_err_code out number) is v_xml xmltype; x number := 0; L_IFACE_REC ONT.OE_HEADERS_IFACE_ALL%ROWTYPE; L_IFACE_LINES_REC ONT.OE_LINES_IFACE_ALL%ROWTYPE; V_LINE_NUMBER NUMBER := 1; V_PRIMARY_UOM_CODE VARCHAR2(10); V_ORDER_CATEGORY_CODE VARCHAR2(30); L_SYSTIMESTAMP VARCHAR2(50); o_err_message varchar2(20000); v_phase varchar2(30); v_dev_phase varchar2(30); v_dev_status varchar2(30); v_status varchar2(30); v_request_flag boolean; v_req_id number; v_message varchar2(20000); v_new_order_number varchar2(30); -- CUX_EIP_TO_ORACLE_ORDER cursor c_parse(p_xml xmltype) is select trim(extractValue(value(a), 'BOM/CUSTOMER_NAME')) CUSTOMER_NAME, --客户名称 trim(extractValue(value(a), 'BOM/ORDERED_ITEM')) ORDERED_ITEM, --料号 to_number(trim(extractValue(value(a), 'BOM/ORDERED_QUANTITY'))) ORDERED_QUANTITY, --订购数量 trim(extractValue(value(a), 'BOM/ORDER_QUANTITY_UOM')) ORDER_QUANTITY_UOM, --单位 to_number(trim(extractValue(value(a), 'BOM/UNIT_SELLING_PRICE'))) UNIT_SELLING_PRICE --分摊价格 from table(xmlsequence(extract(p_xml, 'Root/BOM'))) a; begin --delete from CUX_EIP_TO_ORACLE_ORDER xt where xt.unid = p_unid; SELECT TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF') INTO L_SYSTIMESTAMP FROM DUAL; if p_xmlstr is null then v_err_code := 1; v_out_message := '1.导入的记录不能为空!'; --raise_application_error(-20104, err_Message); return; raise_application_error(-20104, v_out_message || sqlerrm); end if; begin select sys.xmltype(p_xmlstr) into v_xml from dual; exception when others then v_err_code := 2; v_out_message := '2.导入的记录不能为空!' || sqlerrm; return; raise_application_error(-20104, v_out_message || sqlerrm); end; for l_cur in c_parse(v_xml) loop --验证客户名称 BEGIN SELECT CUST_ACCOUNT_ID, SUM(DECODE(SITE_USE_CODE, 'BILL_TO', SITE_USE_ID)), SUM(DECODE(SITE_USE_CODE, 'SHIP_TO', SITE_USE_ID)) INTO L_IFACE_REC.SOLD_TO_ORG_ID, L_IFACE_REC.INVOICE_TO_ORG_ID, L_IFACE_REC.SHIP_TO_ORG_ID FROM (SELECT HCA.CUST_ACCOUNT_ID, HCSU.SITE_USE_CODE, HCSU.SITE_USE_ID FROM HZ_PARTIES HP, HZ_CUST_ACCOUNTS HCA, HZ_PARTY_SITES HPS, HZ_CUST_ACCT_SITES_ALL HCAS, HZ_CUST_SITE_USES_ALL HCSU WHERE HP.PARTY_ID = HCA.PARTY_ID AND HP.PARTY_ID = HPS.PARTY_ID AND HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID AND HPS.PARTY_SITE_ID = HCAS.PARTY_SITE_ID AND HP.PARTY_NAME = l_cur.CUSTOMER_NAME AND HCAS.ORG_ID = 239 --焊机 L_IFACE_REC.ORG_ID AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID) GROUP BY CUST_ACCOUNT_ID; IF L_IFACE_REC.INVOICE_TO_ORG_ID IS NULL THEN v_err_code := 4; v_out_message := '-客户【' || l_cur.CUSTOMER_NAME || '】收单方不存在,请维护好后重新导入-'; return; raise_application_error(-20104, v_out_message || sqlerrm); END IF; IF L_IFACE_REC.SHIP_TO_ORG_ID IS NULL THEN v_err_code := 5; v_out_message := '-客户【' || l_cur.CUSTOMER_NAME || '】收货方不存在,请维护好后重新导入-'; return; raise_application_error(-20104, v_out_message || sqlerrm); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN v_err_code := 6; v_out_message := '-客户名称【' || l_cur.CUSTOMER_NAME || '】不存在-'; return; raise_application_error(-20104, v_out_message || sqlerrm); END; --验证业务实体 SELECT ORG.ORGANIZATION_ID INTO L_IFACE_REC.SHIP_FROM_ORG_ID FROM ORG_ORGANIZATION_DEFINITIONS ORG WHERE NVL(ORG.INVENTORY_ENABLED_FLAG, 'Y') = 'Y' AND ORG.ORGANIZATION_CODE = 'H71' AND ORG.OPERATING_UNIT = 239; --焊机 SELECT HAO.ORGANIZATION_ID INTO L_IFACE_REC.ORG_ID FROM HR_ALL_ORGANIZATION_UNITS HAO WHERE HAO.NAME = '深圳麦格米特焊机_OU' AND HAO.TYPE = 'OPERATION UNIT' AND ROWNUM = 1; --验证料号 BEGIN SELECT MSI.INVENTORY_ITEM_ID, MSI.PRIMARY_UOM_CODE INTO L_IFACE_LINES_REC.INVENTORY_ITEM_ID, V_PRIMARY_UOM_CODE FROM MTL_SYSTEM_ITEMS_B MSI, MTL_CUSTOMER_ITEM_XREFS_V MCI WHERE (MSI.SEGMENT1 = l_cur.ORDERED_ITEM OR MCI.CUSTOMER_ITEM_NUMBER = l_cur.ORDERED_ITEM) AND MSI.INVENTORY_ITEM_ID = MCI.INVENTORY_ITEM_ID(+) AND MSI.ORGANIZATION_ID = MCI.MASTER_ORGANIZATION_ID(+) AND MCI.CUSTOMER_ID(+) = L_IFACE_REC.SOLD_TO_ORG_ID AND MCI.INACTIVE_FLAG(+) = 'N' AND MSI.ENABLED_FLAG = 'Y' AND MSI.ORGANIZATION_ID = L_IFACE_REC.SHIP_FROM_ORG_ID; EXCEPTION WHEN NO_DATA_FOUND THEN v_err_code := 7; v_out_message := '-订购项目【' || l_cur.ORDERED_ITEM || '】不存在或已失效-'; return; raise_application_error(-20104, v_out_message || sqlerrm); END; --验证订购数量 IF l_cur.ORDERED_QUANTITY <= 0 THEN v_err_code := 8; v_out_message := '-订购数量【' || l_cur.ORDERED_QUANTITY || '】必须大于或等于0-'; return; raise_application_error(-20104, v_out_message || sqlerrm); ELSE L_IFACE_LINES_REC.ORDERED_QUANTITY := l_cur.ORDERED_QUANTITY; END IF; --验证单位 IF l_cur.ORDER_QUANTITY_UOM IS NOT NULL THEN BEGIN SELECT T.UOM_CODE INTO L_IFACE_LINES_REC.ORDER_QUANTITY_UOM FROM INV.MTL_UNITS_OF_MEASURE_TL T WHERE T.UOM_CODE = l_cur.ORDER_QUANTITY_UOM AND T.LANGUAGE = 'ZHS'; EXCEPTION WHEN NO_DATA_FOUND THEN v_err_code := 9; v_out_message := '-单位【' || l_cur.ORDER_QUANTITY_UOM || '】不存在'; return; raise_application_error(-20104, v_out_message || sqlerrm); END; ELSE L_IFACE_LINES_REC.ORDER_QUANTITY_UOM := V_PRIMARY_UOM_CODE; END IF; --验证单价 IF l_cur.UNIT_SELLING_PRICE < 0 THEN v_err_code := 10; v_out_message := '-单价【' || l_cur.UNIT_SELLING_PRICE || '】必须大于或等于0-'; return; raise_application_error(-20104, v_out_message || sqlerrm); ELSE L_IFACE_LINES_REC.UNIT_SELLING_PRICE := l_cur.UNIT_SELLING_PRICE; END IF; L_IFACE_LINES_REC.TAX_CODE := '13%(含)'; --税分类代码 --插入订单行 L_IFACE_LINES_REC.LAST_UPDATE_DATE := SYSDATE; L_IFACE_LINES_REC.LAST_UPDATED_BY := 4467; --FND_GLOBAL.USER_ID; L_IFACE_LINES_REC.CREATION_DATE := SYSDATE; L_IFACE_LINES_REC.CREATED_BY := 4467; --FND_GLOBAL.USER_ID; L_IFACE_LINES_REC.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID; L_IFACE_LINES_REC.ORG_ID := 239; -- L_IFACE_REC.ORG_ID; L_IFACE_LINES_REC.ORDER_SOURCE_ID := 0; --L_IFACE_REC.ORDER_SOURCE_ID; L_IFACE_LINES_REC.ORIG_SYS_DOCUMENT_REF := L_SYSTIMESTAMP; L_IFACE_LINES_REC.ORIG_SYS_LINE_REF := L_SYSTIMESTAMP || V_LINE_NUMBER; L_IFACE_LINES_REC.OPERATION_CODE := 'INSERT'; L_IFACE_LINES_REC.LINE_NUMBER := V_LINE_NUMBER; L_IFACE_LINES_REC.SHIPMENT_NUMBER := 1; L_IFACE_LINES_REC.SOURCE_TYPE_CODE := 'INTERNAL'; L_IFACE_LINES_REC.PRICING_QUANTITY := l_cur.ORDERED_QUANTITY; L_IFACE_LINES_REC.PRICING_QUANTITY_UOM := l_cur.ORDER_QUANTITY_UOM; L_IFACE_LINES_REC.UNIT_LIST_PRICE := 0; L_IFACE_LINES_REC.CALCULATE_PRICE_FLAG := 'N'; L_IFACE_LINES_REC.SCHEDULE_SHIP_DATE := SYSDATE; INSERT INTO OE_LINES_IFACE_ALL VALUES L_IFACE_LINES_REC; V_LINE_NUMBER := V_LINE_NUMBER + 1; end loop; SELECT ORG.ORGANIZATION_ID INTO L_IFACE_REC.SHIP_FROM_ORG_ID FROM ORG_ORGANIZATION_DEFINITIONS ORG WHERE NVL(ORG.INVENTORY_ENABLED_FLAG, 'Y') = 'Y' AND ORG.ORGANIZATION_CODE = 'H71' AND ORG.OPERATING_UNIT = 239; --焊机 SELECT HAO.ORGANIZATION_ID INTO L_IFACE_REC.ORG_ID FROM HR_ALL_ORGANIZATION_UNITS HAO WHERE HAO.NAME = '深圳麦格米特焊机_OU' AND HAO.TYPE = 'OPERATION UNIT' AND ROWNUM = 1; --价目表 SELECT QSL.LIST_HEADER_ID, QSL.CURRENCY_CODE INTO L_IFACE_REC.PRICE_LIST_ID, L_IFACE_REC.TRANSACTIONAL_CURR_CODE FROM QP_SECU_LIST_HEADERS_V QSL WHERE QSL.NAME = 'CNY_Megmeet Price List'; --订单类型 SELECT OTT.TRANSACTION_TYPE_ID, OTTA.DEFAULT_OUTBOUND_LINE_TYPE_ID, OTTA.ORDER_CATEGORY_CODE INTO L_IFACE_REC.ORDER_TYPE_ID, L_IFACE_LINES_REC.LINE_TYPE_ID, V_ORDER_CATEGORY_CODE FROM OE_TRANSACTION_TYPES_TL OTT, OE_TRANSACTION_TYPES_ALL OTTA WHERE OTT.NAME = '7011_内销-出货订单' AND OTT.TRANSACTION_TYPE_ID = OTTA.TRANSACTION_TYPE_ID AND OTTA.ORG_ID = 239 AND LANGUAGE = 'ZHS' AND ROWNUM = 1; --业务员 SELECT SR.SALESREP_ID INTO L_IFACE_REC.SALESREP_ID FROM JTF_RS_SALESREPS SR, JTF_RS_RESOURCE_EXTNS_VL RES WHERE SR.RESOURCE_ID = RES.RESOURCE_ID AND SYSDATE BETWEEN SR.START_DATE_ACTIVE AND NVL(SR.END_DATE_ACTIVE, SYSDATE) AND RES.RESOURCE_NAME = '刘杰,' AND SR.ORG_ID = 239; --付款条件 SELECT TERM.TERM_ID INTO L_IFACE_REC.PAYMENT_TERM_ID FROM RA_TERMS TERM WHERE SYSDATE BETWEEN TERM.START_DATE_ACTIVE AND NVL(TERM.END_DATE_ACTIVE, SYSDATE) AND TERM.NAME = '月结90天'; --CNY_Megmeet Price List L_IFACE_REC.LAST_UPDATE_DATE := SYSDATE; L_IFACE_REC.LAST_UPDATED_BY := 4467; --FND_GLOBAL.USER_ID; L_IFACE_REC.CREATION_DATE := SYSDATE; L_IFACE_REC.CREATED_BY := 4467; --FND_GLOBAL.USER_ID; L_IFACE_REC.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID; L_IFACE_REC.ORDER_SOURCE_ID := 0; --ONLINE, SEE ONT.OE_ORDER_SOURCES L_IFACE_REC.ORIG_SYS_DOCUMENT_REF := L_SYSTIMESTAMP; L_IFACE_REC.OPERATION_CODE := 'INSERT'; L_IFACE_REC.READY_FLAG := 'Y'; L_IFACE_REC.ORDERED_DATE := SYSDATE; L_IFACE_REC.TAX_EXEMPT_FLAG := 'S'; L_IFACE_REC.BOOKED_FLAG := 'N'; L_IFACE_REC.CLOSED_FLAG := 'N'; L_IFACE_REC.SOLD_FROM_ORG_ID := L_IFACE_REC.ORG_ID; L_IFACE_REC.ATTRIBUTE15 := p_CUSTOMER_PO; --l_cur.CUSTOMER_PO;合同编号 INSERT INTO OE_HEADERS_IFACE_ALL VALUES L_IFACE_REC; commit; /* v_err_code := 9; v_out_message := '导入成功';*/ begin fnd_global.apps_initialize(4467, --fnd_global.user_id, 52268, --职责id-通过诊断$PROFILES$的resp_id 20003 --通过诊断$PROFILES$的RESP_APPL_ID ); fnd_request.set_org_id(239); v_req_id := fnd_request.submit_request('ONT', 'OEOIMP', '', sysdate, false, l_iface_rec.org_id, 0, l_iface_rec.orig_sys_document_ref, '', 'N', 1, 4, l_iface_rec.sold_to_org_id, '', '', 'Y', 'Y', 'Y', l_iface_rec.org_id, 'Y'); commit; v_request_flag := fnd_concurrent.wait_for_request(request_id => v_req_id, --返回的请求ID INTERVAL => 5, --重复检测时间差 max_wait => 0, --最长等待时间,0为一直等待 phase => v_phase, status => v_status, dev_phase => v_dev_phase, dev_status => v_dev_status, message => v_message); if v_request_flag then if v_dev_status = 'NORMAL' then begin select ooh.order_number into v_new_order_number from oe_order_headers_all ooh where ooh.ORIG_SYS_DOCUMENT_REF = l_iface_rec.orig_sys_document_ref; v_out_message := '订单生成成功,新订单编号为:' || v_new_order_number; return; exception when no_data_found then v_out_message := '请求运行成功,订单未成功创建,请求ID=' || v_req_id || sqlerrm; return; raise_application_error(-20104, v_out_message || sqlerrm); end; else v_out_message := '请求运行失败,请求ID=' || v_req_id || sqlerrm; return; raise_application_error(-20104, v_out_message || sqlerrm); end if; end if; exception WHEN OTHERS THEN v_out_message := '订单复制失败,请联系系统管理员处理,错误信息:' || o_err_message; return; end; end; end PKG_OM_IMPORT_FROM_EIP;
|