WIP工序移动和完工接口开发.doc

WIP工序移动和完工接口开发文档控制变更记录11日期作者版本变更说明审阅姓名职位分发拷贝编号姓名位置/岗位1234备注：WIP工序移动和工单完工接口开发 在某个项目中，需要对WIP的工单的某工序进行移动，完工，同时可能涉及到超量完工。Release之后的工单可以进行工序移动事务处理，在工序移动的同时可以选择报废、（超量）完工。接口开发简洁接口开发简述大量的期初数据如何进入ERP？如何使ERP的系统流程更加自动化？如何迁移/合并两个Oracle ERP系统？这些纷繁复杂的海量工作不可能全部通过手工Key到系统中，必须通过某种方式灌入ERP、再现源系统数据风貌。处理这些业务需求的方式就是“接口”：通过一组程序把数据批量写入Oracle ERP相关表中，达到和在界面输入同样的效果：数据一致性，数据有效性，数据集成性（比如插入PO数据，要反馈到MRP的需求/供应）。接口开发主要步骤 采集数据 验证数据：按接口要求格式化数据，验证各个字段数据的合法性 往相应的接口表中插入验证后的数据 ERP接口处理请求（将数据插入ERP数据表中）工序移动接口和完工接口接口：该方法是往移动事务处理接口表wip_move_txn_interface 中插入验证后的数据，然后调用标准API wip_movproc_cessinterface 来进行移动和完工。但是往移动事务处理接口表中插数据的时候我们怎么知道要往哪些字段插数据呢？首先我们可以导航到EBS中的移动事务处理界面路径：车间管理-移动事务处理-移动事务处理就可以查知那些字段代表了什么意思，该插什么值了。一下是一些关键字段：字段说明transaction_type1 工序移动（缺省）2 移动完工入库3 移动入库返回process_phase给11 Move Validation2 Move Processing3 Operation Backflush Setupprocess_status给11 Pending2 Running3 Errorwip_entity_name任务名称organization_idCurrent Inv Organizationorganization_code组织代码，这个字段必须给transaction_date事务日期，必须between Release Date and Sysdatetransaction_quantity移动数量transaction_uom单位，可以不是主单位fm_operation_seq_num起始工序号fm_intraoperation_step_type起始工序内部步骤to_operation_seq_num终止工序号to_intraoperation_step_type终止工序内部步骤source_code随便给，一般用来追踪last_updated_by_name必须给，比较特殊created_by_name必须给，比较特殊5个who字段习惯上都给在处理的时候，可以判断当前的工序是否是最后一道工序，然后在设置transaction_type的值，确定是移动还是入库当往接口表中插入数据之后，就可以调用wip_movproc_cessinterface来进行后续的处理了;超量完工对于超量完工的情况，只需增加多一个字段即可，往接口表中插数据的时候，同时设置overcompletion_transaction_qty的值为超量的部分的值即可；代码范例 一下是一个代码范例CREATE OR REPLACE PACKAGE BODY cux_wip_dispatch_utl IS g_mmt_iface mtl_transactions_interface%ROWTYPE; g_wmti_iface wip_move_txn_interface%ROWTYPE; g_user_name VARCHAR2(30) := fnd_global.user_name; PROCEDURE test_mmt(p_id IN NUMBER) IS l_result NUMBER; BEGIN SELECT mti.transaction_quantity INTO l_result FROM mtl_transactions_interface mti WHERE mti.transaction_header_id = p_id; END test_mmt; FUNCTION get_wip_entity_name(p_wip_entity_id IN NUMBER, p_org_id IN NUMBER) RETURN VARCHAR2 IS l_return wip_entities.wip_entity_name%TYPE; BEGIN SELECT wip_entity_name INTO l_return FROM wip_entities t WHERE wip_entity_id = p_wip_entity_id AND organization_id = p_org_id; RETURN l_return; END get_wip_entity_name; FUNCTION get_work_order_item_id(p_wip_entity_id IN NUMBER, p_organization_id IN NUMBER) RETURN NUMBER AS CURSOR c_work_order IS SELECT t.primary_item_id FROM wip_entities t WHERE t.wip_entity_id = p_wip_entity_id AND anization_id = p_organization_id; l_item_id NUMBER; BEGIN OPEN c_work_order; FETCH c_work_order INTO l_item_id; CLOSE c_work_order; RETURN l_item_id; END get_work_order_item_id; FUNCTION check_txn_date_in_period(p_transaction_date IN DATE, p_organization_id IN NUMBER) RETURN VARCHAR2 AS CURSOR c_period IS SELECT Y FROM org_acct_periods t WHERE t.open_flag = Y AND anization_id = p_organization_id AND trunc(p_transaction_date) BETWEEN t.period_start_date AND t.schedule_close_date; l_valid_flag VARCHAR2(1); BEGIN OPEN c_period; FETCH c_period INTO l_valid_flag; CLOSE c_period; RETURN l_valid_flag; END check_txn_date_in_period; PROCEDURE validate_wip_complete(p_wip_entity_id IN NUMBER, p_organization_id IN NUMBER, p_transaction_date IN DATE, p_complete_qty IN NUMBER, p_uom IN VARCHAR2, x_return_status OUT NOCOPY VARCHAR2, x_msg_data OUT NOCOPY VARCHAR2) IS l_exists NUMBER; l_primary_item_id NUMBER; BEGIN -Validate oraganization BEGIN SELECT organization_id INTO g_wmti_anization_id FROM org_organization_definitions o WHERE organization_id = p_organization_id; g_wmti_anization_id := p_organization_id; EXCEPTION WHEN OTHERS THEN x_return_status := fnd_api.g_ret_sts_error; x_msg_data := 非法的库存组织: | p_organization_id; RETURN; END; -Validate dj_name BEGIN SELECT t.wip_entity_id, t.primary_item_id INTO g_wmti_iface.wip_entity_id, l_primary_item_id FROM wip_entities t WHERE t.wip_entity_id = p_wip_entity_id AND organization_id = p_organization_id; EXCEPTION WHEN OTHERS THEN x_return_status := fnd_api.g_ret_sts_error; x_msg_data := Invalid wip entity: | p_wip_entity_id; RETURN; END; -Validate wip entity must have ASSEMBLY IF l_primary_item_id IS NULL THEN x_return_status := fnd_api.g_ret_sts_error; x_msg_data := Wip entity assembly is null: | p_wip_entity_id; RETURN; END IF; -Validate Transaction date must after release date and in open inv period IF check_txn_date_in_period(p_transaction_date, p_organization_id) = Y THEN g_wmti_iface.transaction_date := p_transaction_date; ELSE x_return_status := fnd_api.g_ret_sts_error; x_msg_data := 非法的事务处理日期: | to_char(p_transaction_date, yyyy-mm-dd hh24:mi:ss); RETURN; END IF; -Validate DJ status must be released SELECT COUNT(1) INTO l_exists FROM wip_discrete_jobs WHERE wip_entity_id = g_wmti_iface.wip_entity_id AND organization_id = p_organization_id AND status_type = 3; IF l_exists = 0 THEN x_return_status := fnd_api.g_ret_sts_error; x_msg_data := DJ status must be released.; RETURN; END IF; -Validate quantity IF p_complete_qty IS NULL OR p_complete_qty 1.0, x_return_status = l_return_status, x_msg_count = l_msg_count, x_msg_data = l_msg_data, -error message x_trans_count = l_trans_count, -record processed p_table = 1, -interface p_header_id = g_mmt_iface.transaction_header_id); IF l_result = 0 THEN o_success_flag := Y; o_error_mssg := NULL; ELSE o_success_flag := N; IF p_table = 1 THEN -retreve error from interface FOR i IN (SELECT DISTINCT error_explanation FROM mtl_transactions_interface WHERE transaction_header_id = g_mmt_iface.transaction_header_id) LOOP o_error_mssg := o_error_mssg | .Error: | i.error_explanation; END LOOP; ELSE o_error_mssg := l_msg_data; END IF; END IF; EXCEPTION WHEN OTHERS THEN o_success_flag := N; o_error_mssg := Occur Exception: | SQLERRM; END handle_mmt_iface; PROCEDURE handle_wmti_iface(x_return_status OUT NOCOPY VARCHAR2, x_msg_data OUT NOCOPY VARCHAR2) IS BEGIN -call api process wmti wip_movproc_cessinterface(p_txn_id = g_wmti_iface.transaction_id, p_do_backflush = fnd_api.g_true, p_commit = fnd_api.g_false, x_returnstatus = x_return_status, x_errormsg = x_msg_data); dbms_output.put_line(x_return_status: | x_return_status); EXCEPTION WHEN OTHERS THEN x_return_status := fnd_api.g_ret_sts_unexp_error; x_msg_data := cux_wip_dispatch_utl.handle_wmti_iface Unhandled exception: | SQLERRM; END handle_wmti_iface; PROCEDURE process_wip_complete(p_wip_entity_id IN NUMBER, p_organization_id IN NUMBER, p_transaction_date IN DATE, p_complete_qty IN NUMBER, p_uom IN VARCHAR2, p_is_max_operation_seq_num IN VARCHAR2, p_is_overcome IN NUMBER, p_sourcr_line_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2, x_msg_data OUT NOCOPY VARCHAR2) IS BEGIN x_return_status := fnd_api.g_ret_sts_success; -setp 1 : validate data validate_wip_complete(p_wip_entity_id = p_wip_entity_id, p_organization_id = p_organization_id, p_transaction_date = p_transaction_date, p_complete_qty = p_complete_qty, p_uom = p_uom, x_return_status = x_return_status, x_msg_data = x_msg_data); IF x_return_status fnd_api.g_ret_sts_success THEN RETURN; END IF; g_wmti_iface.sou