EBS销售订单挑库发放处理程序

来源：互联网发布：linux exec作用编辑：程序博客网时间：2024/04/28 20:08

在EBS实施中，经常遇到从外部传进来一个被登记的销售订单，需要通过程序进行销售订单的挑库发放

下面是对SO挑库发放的实现步骤的详细实现：

1. 对销售订单的有效性验证

1）检查销售订单的行是否被完全传回客户化表

2）验证销售订单的关键字段

3）检查子库存是否启用了货位控制，如果启用了货位控制，没有生成货位，则调用API生成货位

4）调用API检查子库存中的某一个物料的现用量和可用量

2. 销售订单发放

调用API进行销售订单发放

发放成功：返回一个批号，这个批号作为挑库发放的参数

3. 销售订单挑库发放

调用API进行挑库发放

发放前行状态：准备发放下一步：挑库发放判断依据：销售订单发放生成的批号作为挑库发放的参数

发放成功行状态：已发放至仓库下一步：处理物料搬运单判断依据：返回一个批号，这个批号作为挑库发放的参数

发放失败行状态：已延交下一步：挑库发放判断依据：未成功返回一个批号

说明：订单发放是按照整个订单发放的。

4. 处理物料搬运单

处理成功：行状态：发运下一步：判断依据：返回成功的处理状态

处理成失败行状态：下一步：处理物料搬运单判断依据：未返回成功的处理状态

5. 得到销售订单的交货号(delivery_id)

调用API 查看是否产生发运号

成功：返回发运号

失败：未返回发运号

6. 发运确认

处理成功订单状态为：关闭

说明：如果发运确认已经成功，则会自动调用连接形成停靠站接口程序

具体实现代码

1. 对销售订单的有效性验证

调用API验证货位的代码：

fnd_profile.put('MFG_ORGANIZATION_ID',p_organization_id); l_locator_type := get_locator_type(p_sub_inv,p_organization_id); IF l_locator_type != 1 THEN l_locator := p_sub_inv || '.' || p_project || '.' || p_task; inv_loc_wms_pub.create_locator(x_return_status => l_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, x_inventory_location_id => l_locator_id, x_locator_exists => l_locator_exists, p_organization_id => p_organization_id, p_organization_code => p_organization_code, p_concatenated_segments => l_locator, p_description => NULL, p_inventory_location_type => l_locator_type, p_picking_order => NULL, p_location_maximum_units => NULL, p_subinventory_code => p_sub_inv, p_location_weight_uom_code => NULL, p_max_weight => NULL, p_volume_uom_code => NULL, p_max_cubic_area => NULL, p_x_coordinate => NULL, p_y_coordinate => NULL, p_z_coordinate => NULL, p_physical_location_id => NULL, p_pick_uom_code => NULL, p_dimension_uom_code => NULL, p_length => NULL, p_width => NULL, p_height => NULL, p_status_id => 1, p_dropping_order => NULL); IF l_locator_id IS NULL THEN o_err_code := g_error; o_err_msg := '创建(获取)货位弹性域发生错误'; ELSE o_locator_id := l_locator_id; END IF;

调用API验证库存现用量和可用量的代码：

inv_quantity_tree_pub.query_quantities(p_api_version_number => 1.0, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_organization_id => l_organization_id, p_inventory_item_id => rec_data.item_id,--item id p_tree_mode => 2, p_is_revision_control => FALSE, p_is_lot_control => l_lot_control_flag, p_lot_expiration_date => rec_data.transaction_date, p_is_serial_control => FALSE, p_grade_code => NULL, -- INVCONV NOT NEEDED NOW p_revision => NULL, p_lot_number => NULL, p_subinventory_code => rec_data.pick_from_subinventory, p_locator_id => l_locator_id, x_qoh => l_qoh, x_rqoh => l_rqoh, x_qr => l_qr, x_qs => l_qs, x_att => l_att, x_atr => l_atr, x_sqoh => l_sqoh, -- INVCONV x_srqoh => l_srqoh, -- INVCONV x_sqr => l_sqr, -- INVCONV x_sqs => l_sqs, -- INVCONV x_satt => l_satt, -- INVCONV x_satr => l_satr); -- INVCONV IF nvl(l_atr,0) = 0 THEN GOTO end_loop; END IF;

2. 销售订单发放

调用API进行销售订单发放的代码：

fnd_profile.PUT('MFG_ORGANIZATION_ID',l_organization_id); wsh_picking_batches_pkg.insert_row(x_rowid => l_row_id, x_batch_id => l_batch_id, p_creation_date => g_sysdate, p_created_by => g_user_id, p_last_update_date => g_sysdate, p_last_updated_by => g_user_id, p_last_update_login => g_login_id, p_batch_name_prefix => NULL, x_name => l_name, p_backorders_only_flag => l_rule_rec.backorders_only_flag, p_document_set_id => l_rule_rec.document_set_id, p_existing_rsvs_only_flag => l_rule_rec.existing_rsvs_only_flag, p_shipment_priority_code => l_rule_rec.shipment_priority_code, p_ship_method_code => l_rule_rec.ship_method_code, p_customer_id => l_customer_id, p_order_header_id => rec_data.order_header_id, p_ship_set_number => l_rule_rec.ship_set_number, p_inventory_item_id => NULL, p_order_type_id => l_order_type_id, p_from_requested_date => NULL, p_to_requested_date => NULL, p_from_scheduled_ship_date => NULL, p_to_scheduled_ship_date => NULL, p_ship_to_location_id => l_rule_rec.ship_to_location_id, p_ship_from_location_id => l_rule_rec.ship_from_location_id/*l_stage_locator_id*/, p_trip_id => NULL, p_delivery_id => NULL, p_include_planned_lines => l_rule_rec.include_planned_lines, p_pick_grouping_rule_id => l_rule_rec.pick_grouping_rule_id, p_pick_sequence_rule_id => l_rule_rec.pick_sequence_rule_id, p_autocreate_delivery_flag => 'Y', --l_rule_rec.autocreate_delivery_flag; --无论规则是否自动创建交货号，都让系统自动创建(用shipment_number做交货号) p_attribute_category => NULL, p_attribute1 => NULL, p_attribute2 => NULL, p_attribute3 => NULL, p_attribute4 => NULL, p_attribute5 => NULL, p_attribute6 => NULL, p_attribute7 => NULL, p_attribute8 => NULL, p_attribute9 => NULL, p_attribute10 => NULL, p_attribute11 => NULL, p_attribute12 => NULL, p_attribute13 => NULL, p_attribute14 => NULL, p_attribute15 => NULL, p_autodetail_pr_flag => l_rule_rec.autodetail_pr_flag, p_carrier_id => l_rule_rec.carrier_id, p_trip_stop_id => NULL, p_default_stage_subinventory => l_rule_rec.default_stage_subinventory,--ERP临时发货区 p_default_stage_locator_id => l_stage_locator_id, p_pick_from_subinventory => rec_data.pick_from_subinventory,/*l_rule_rec.pick_from_subinventory,*/--ERP提货区 p_pick_from_locator_id => l_locator_id,--l_rule_rec.pick_from_locator_id, p_auto_pick_confirm_flag => 'N'/*l_rule_rec.auto_pick_confirm_flag*/, --无论规则是否自动确认挑库，都不让系统自动挑库(可能会修改分配数) p_delivery_detail_id => NULL, p_project_id => rec_data.project_id, p_task_id => rec_data.task_id, p_organization_id => l_organization_id, p_ship_confirm_rule_id => l_rule_rec.ship_confirm_rule_id, p_autopack_flag => l_rule_rec.autopack_flag, p_autopack_level => l_rule_rec.autopack_level, p_task_planning_flag => l_rule_rec.task_planning_flag, p_non_picking_flag => NULL, p_regionid => l_rule_rec.region_id, p_zoneid => l_rule_rec.zone_id, p_categoryid => l_rule_rec.category_id, p_categorysetid => l_rule_rec.category_set_id, p_acdelivcriteria => l_rule_rec.ac_delivery_criteria, p_relsubinventory => l_rule_rec.rel_subinventory, --如果规则不做限制则可以任意子库中挑库 --nvl(p_subinventory_from,l_rule_rec.rel_subinventory), --如果在此处修改来源子库会导致发放不成功不能创建物料搬运单 p_append_flag => l_rule_rec.append_flag, p_task_priority => l_rule_rec.task_priority, p_ship_set_smc_flag => NULL, p_actual_departure_date => NULL, p_allocation_method => l_rule_rec.allocation_method, p_crossdock_criteria_id => l_rule_rec.crossdock_criteria_id, p_delivery_name_lo => NULL, p_delivery_name_hi => NULL, p_bol_number_lo => NULL, p_bol_number_hi => NULL, p_intmed_ship_to_loc_id => NULL, p_pooled_ship_to_loc_id => NULL, p_fob_code => NULL, p_freight_terms_code => NULL, p_pickup_date_lo => NULL, p_pickup_date_hi => NULL, p_dropoff_date_lo => NULL, p_dropoff_date_hi => NULL, p_planned_flag => NULL, p_selected_batch_id => NULL); IF l_batch_id IS NULL THEN l_err_code := g_warning; l_err_msg := '销售订单发放没有产生批号'; GOTO next_line; END IF;

销售订单发放生成的批号作为步骤3的参数

wsh_pick_list.online_release(p_batch_id => l_batch_id, p_pick_result => l_pick_result, p_pick_phase => l_pick_phase, p_pick_skip => l_pick_skip); --F:Failure IF nvl(l_pick_result,'F') = 'F' THEN IF (nvl(l_pick_phase,'START') = 'MOVE_ORDER_LINES') THEN l_err_code := g_warning; l_err_msg := '联机挑库发放已部分完成。未完全处理物料搬运单:' || l_batch_id; ELSE l_err_code := g_warning; l_err_msg := '联机挑库发放失败。请在并发模式下发放订单:' || l_batch_id; END IF; ELSIF nvl(l_pick_skip,'Y') = 'Y' THEN l_err_code := g_warning; l_err_msg := '联机挑库发放已结束但出现警告。请检查“发运例外报表”'; --即使部分挑库成功也不继续处理 ELSIF nvl(l_pick_result,'F') = 'W' THEN IF nvl(l_pick_phase,'START') = 'MOVE_ORDER_LINES' THEN l_err_code := g_warning; l_err_msg := '联机挑库发放已部分完成，但出现警告。'; ELSE l_err_code := g_warning; l_err_msg := '联机挑库发放已结束但出现警告。请检查“发运例外报表”'; END IF; ELSIF nvl(l_pick_result,'F') = 'S' THEN NULL; END IF;

4. 处理物料搬运单

调用API处理物料搬运单的代码：

get_move_order_line_id(p_order_line_id => rec_data.line_id, o_move_order_line_id => l_mv_line_id, o_mv_status => l_mv_status, o_err_code => l_err_code, o_err_msg => l_err_msg); IF l_err_code IS NOT NULL THEN GOTO next_line; END IF; <<move_order>> l_trolin_tbl := inv_trolin_util.query_rows(p_line_id => l_mv_line_id); l_mold_tbl := inv_mo_line_detail_util.query_rows(p_line_id => l_mv_line_id); inv_pick_wave_pick_confirm_pub.pick_confirm(p_api_version_number => 1.0, p_init_msg_list => fnd_api.g_false, p_commit => fnd_api.g_false, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_move_order_type => 3, p_transaction_mode => 1, p_trolin_tbl => l_trolin_tbl, p_mold_tbl => l_mold_tbl, x_mmtt_tbl => l_mold_tbl, x_trolin_tbl => l_trolin_tbl); IF l_return_status != fnd_api.g_ret_sts_success THEN l_err_code := g_warning; l_err_msg := '处理物料搬运单失败'; GOTO next_line; END IF;

5. 得到销售订单的交货号(delivery_id)

得到交货号的代码：

PROCEDURE get_delivery_line_id(p_order_line_id IN NUMBER, o_delivery_id OUT NUMBER, o_err_code OUT number, o_err_msg OUT VARCHAR2) IS BEGIN SELECT wda.delivery_id INTO o_delivery_id FROM wsh_delivery_details wdd, wsh_delivery_assignments wda WHERE wdd.source_line_id = p_order_line_id AND wdd.delivery_detail_id = wda.delivery_detail_id AND rownum = 1; EXCEPTION WHEN NO_DATA_FOUND THEN o_err_code := g_warning; o_err_msg := '无法获取订单行发运号'; WHEN OTHERS THEN o_err_code := g_error; o_err_msg := '获取发运行ID时发生意外错误：' || SQLERRM; END; --调用过程得到交货号 get_delivery_line_id(p_order_line_id => rec_data.line_id, o_delivery_id => l_delivery_id, o_err_code => l_err_code, o_err_msg => l_err_msg); IF l_delivery_id IS NULL THEN l_err_code := g_warning; l_err_msg := '没有为该订单成功创建发运号'; GOTO next_line; END IF;

6. 发运确认

根据步骤5中返回的delivery_id确认是否进行发运确认，

如果delivery_id不为空，并且没有返回错误信息。

调用API进行发运确认的代码：

WSH_NEW_DELIVERY_ACTIONS.Confirm_Delivery(p_del_rows => l_delivery_id_tab, p_action_flag => l_action_flag, p_intransit_flag => l_intransit_flag, p_close_flag => l_close_trip_flag, p_stage_del_flag => l_stage_del_flag, p_report_set_id => l_report_set_id, p_ship_method => l_trip_ship_method, p_actual_dep_date => l_actual_dep_date, p_bol_flag => l_create_bol_flag, p_mc_bol_flag => l_mc_bill_of_lading_flag, p_defer_interface_flag => l_defer_interface_flag, p_send_945_flag => l_send_945_flag, x_return_status => l_return_status); IF l_return_status NOT IN (fnd_api.g_ret_sts_success,'') THEN l_err_code := g_warning; l_err_msg := '销售订单发放确认失败'; GOTO next_head; END IF;

结果：通过以上步骤，可以成功的将一个外围系统导入的销售订单进行挑库发放，成功处理此销售订单，并将销售订单行的状态变成“关闭”

3. 销售订单挑库发放

调用API进行挑库发放的代码：