|
|
马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。如果您注册时有任何问题请联系客服QQ： 83569622 。
您需要 登录 才可以下载或查看，没有帐号？注册
x
/*=======================================================
* FUNCTION / PROCEDURE
* get_acct_period_id
* DESCRIPTION:
* 得到库存会计期id
* ARGUMENT:
* p_organization_id :库存组织id
* p_transaction_date:事务处理日期
* RETURN:
* 库存会计期id
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
FUNCTION get_acct_period_id(p_organization_id IN NUMBER,
p_transaction_date IN DATE) RETURN NUMBER IS
l_acct_period_id NUMBER;--库存会计期id
BEGIN
SELECT oap.acct_period_id
INTO l_acct_period_id
FROM org_acct_periods oap
WHERE oap.organization_id = p_organization_id
AND (trunc(p_transaction_date) BETWEEN oap.period_start_date AND
oap.schedule_close_date);
RETURN l_acct_period_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN TOO_MANY_ROWS THEN
put_log('得到库存会计期id时返回的多个行!');
WHEN OTHERS THEN
put_log('得到库存会计期id时出现异常:' || SQLERRM);
END get_acct_period_id;
/*=======================================================
* FUNCTION / PROCEDURE
* get_transaction_action_id
* DESCRIPTION:
* 得到事务处理的活动id
* ARGUMENT:
* p_transaction_type_id:事务处理类型id
* RETURN:
* 事务处理的活动id
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
FUNCTION get_transaction_action_id(p_transaction_type_id IN NUMBER)
RETURN NUMBER IS
l_transaction_action_id NUMBER;--事务处理的活动id
BEGIN
SELECT mtt.transaction_action_id
INTO l_transaction_action_id
FROM mtl_transaction_types mtt
WHERE mtt.transaction_type_id = p_transaction_type_id;
RETURN l_transaction_action_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN TOO_MANY_ROWS THEN
put_log('得到事务处理的活动id时返回的多个行!');
WHEN OTHERS THEN
put_log('得到物料搬运单活动id时出现异常:' || SQLERRM);
END get_transaction_action_id;
/*=======================================================
* FUNCTION / PROCEDURE
* get_move_order
* DESCRIPTION:
* 得到物料搬运单的一条记录(一个物料搬运单头和对应的所有的物表搬运单的行)
* ARGUMENT:
* x_return_status :状态
* x_msg_count :错误信息数量
* x_msg_data :错误信息
* x_trohdr_rec :物料搬运单头的一行记录
* x_trohdr_val_rec :物料搬运单头的一行记录对应的值
* x_trolin_tbl :一个物料搬运单头对应的所有物料搬运单行
* x_trolin_val_tbl :一个物料搬运单头对应的所有物料搬运单行的值
* p_header_id :物料搬运单头id
* RETURN:
* N/A
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
PROCEDURE get_move_order(x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_trohdr_rec OUT NOCOPY INV_MOVE_ORDER_PUB.Trohdr_Rec_Type,
x_trohdr_val_rec OUT NOCOPY INV_MOVE_ORDER_PUB.Trohdr_Val_Rec_Type,
x_trolin_tbl OUT NOCOPY INV_MOVE_ORDER_PUB.Trolin_Tbl_Type,
x_trolin_val_tbl OUT NOCOPY INV_MOVE_ORDER_PUB.Trolin_Val_Tbl_Type,
p_header_id IN NUMBER) IS
BEGIN
--调用系统标准的API得到物料搬运单的相关信息
INV_Move_Order_PUB.Get_Move_Order(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.G_FALSE,
p_return_values => fnd_api.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_header_id => p_header_id,
p_header => fnd_api.G_MISS_CHAR,
x_trohdr_rec => x_trohdr_rec,
x_trohdr_val_rec => x_trohdr_val_rec,
x_trolin_tbl => x_trolin_tbl,
x_trolin_val_tbl => x_trolin_val_tbl);
EXCEPTION
WHEN OTHERS THEN
put_log('得到物料搬运单时出现异常:' || SQLERRM);
END get_move_order;
/*=======================================================
* FUNCTION / PROCEDURE
* get_trolin_rec
* DESCRIPTION:
* 得到物料搬运单的一行记录
* ARGUMENT:
* x_return_status :状态
* x_trolin_rec :物料搬运单行的一行记录
* p_trolin_tbl :一条物料搬运单的所有搬运单行
* p_line_id :物料搬运单行id
* RETURN:
* N/A
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
PROCEDURE get_trolin_rec(x_return_status OUT NOCOPY VARCHAR2,
x_trolin_rec OUT NOCOPY INV_MOVE_ORDER_PUB.Trolin_Rec_Type,
p_trolin_tbl IN INV_MOVE_ORDER_PUB.Trolin_Tbl_Type,
p_line_id IN NUMBER) IS
l_table_index NUMBER := 0;
BEGIN
FOR l_table_index IN 1 .. p_trolin_tbl.COUNT LOOP
IF p_line_id = p_trolin_tbl(l_table_index).line_id THEN
x_trolin_rec := p_trolin_tbl(l_table_index);
x_return_status := fnd_api.G_RET_STS_SUCCESS;
RETURN;
END IF;
END LOOP; --FOR x_table_index IN x_trolin_tbl.COUNT LOOP
x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
EXCEPTION
WHEN OTHERS THEN
put_log('得到物料搬运单时出现异常:' || SQLERRM);
END get_trolin_rec;
/*=======================================================
* FUNCTION / PROCEDURE
* insert_mmtt_row
* DESCRIPTION:
* 插入某一物料搬运单事务处理临时表的一条记录
* ARGUMENT:
* x_return_status :状态
* x_transaction_temp_id :临时表id
* x_msg_data :错误信息
* x_trohdr_rec :物料搬运单头的一行记录
* x_trohdr_val_rec :物料搬运单头的一行记录对应的值
* x_trolin_tbl :一个物料搬运单头对应的所有物料搬运单行
* x_trolin_val_tbl :一个物料搬运单头对应的所有物料搬运单行的值
* p_header_id :物料搬运单头id
* p_line_id :物料搬运单行id
* p_transaction_quantity :事务处理数量
* p_transaction_date :事务处理日期
* RETURN:
* N/A
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
PROCEDURE insert_mmtt_row(x_return_status OUT NOCOPY VARCHAR2,
x_transaction_temp_id OUT NUMBER,
p_trohdr_rec IN INV_MOVE_ORDER_PUB.Trohdr_Rec_Type,
p_trohdr_val_rec IN INV_MOVE_ORDER_PUB.Trohdr_Val_Rec_Type,
p_trolin_tbl IN INV_MOVE_ORDER_PUB.Trolin_Tbl_Type,
p_trolin_val_tbl IN INV_MOVE_ORDER_PUB.Trolin_Val_Tbl_Type,
p_header_id IN NUMBER,
p_line_id IN NUMBER,
p_transaction_quantity IN NUMBER,
p_transaction_date IN DATE) IS
l_mo_line_detail_rec inv_mo_line_detail_util.g_mmtt_rec;
l_transaction_temp_id NUMBER; --临时表的id
l_trolin_rec INV_MOVE_ORDER_PUB.Trolin_Rec_Type;
x_locator_type VARCHAR2(60); --货位控制类型
x_from_locator_id NUMBER; --来源货位
x_to_locator_id NUMBER; --目标货位
x_msg_data VARCHAR2(2000); --错误信息
BEGIN
--得到某一物料搬运单行的信息
get_trolin_rec(x_return_status => x_return_status,
x_trolin_rec => l_trolin_rec,
p_trolin_tbl => p_trolin_tbl,
p_line_id => p_line_id);
--检查是否启用货位控制,如果启用，则动态生成货位
SELECT mtl_material_transactions_s.nextval
INTO l_transaction_temp_id
FROM DUAL;
-- l_mo_line_detail_rec.locator_id :=l_trolin_rec.from_locator_id;--x_from_locator_id; --源子库存货位
l_mo_line_detail_rec.transfer_to_location := l_trolin_rec.to_locator_id;--x_to_locator_id; --目标子库存货位
l_mo_line_detail_rec.transaction_header_id := NULL;
l_mo_line_detail_rec.transaction_temp_id := l_transaction_temp_id;
l_mo_line_detail_rec.source_code := NULL;
l_mo_line_detail_rec.source_line_id := NULL;
l_mo_line_detail_rec.transaction_mode := null;--1;
l_mo_line_detail_rec.lock_flag := null;--'N';
l_mo_line_detail_rec.last_update_date := SYSDATE;
l_mo_line_detail_rec.last_updated_by := fnd_global.user_id;
l_mo_line_detail_rec.creation_date := SYSDATE;
l_mo_line_detail_rec.created_by := fnd_global.user_id;
l_mo_line_detail_rec.last_update_login := fnd_global.user_id;
l_mo_line_detail_rec.request_id := NULL;
l_mo_line_detail_rec.program_application_id := NULL;
l_mo_line_detail_rec.program_id := NULL;
l_mo_line_detail_rec.program_update_date := NULL;
--从物料搬运单得到item_id
l_mo_line_detail_rec.inventory_item_id := l_trolin_rec.inventory_item_id;
l_mo_line_detail_rec.revision := NULL;
l_mo_line_detail_rec.organization_id := l_trolin_rec.organization_id;
l_mo_line_detail_rec.subinventory_code :='0101' ;--l_trolin_rec.from_subinventory_code;
dbms_output.put_line('l_trolin_rec.from_locator_id:='||to_char(l_trolin_rec.from_locator_id));
l_mo_line_detail_rec.locator_id := 1;--l_trolin_rec.from_locator_id;
l_mo_line_detail_rec.transaction_quantity := p_transaction_quantity;
l_mo_line_detail_rec.primary_quantity := p_transaction_quantity;
l_mo_line_detail_rec.transaction_uom := l_trolin_rec.uom_code;
l_mo_line_detail_rec.transaction_cost := NULL;
l_mo_line_detail_rec.transaction_type_id := l_trolin_rec.transaction_type_id;
l_mo_line_detail_rec.transaction_action_id := get_transaction_action_id(l_trolin_rec.transaction_type_id);
l_mo_line_detail_rec.transaction_source_type_id := l_trolin_rec.transaction_source_type_id;
l_mo_line_detail_rec.transaction_source_id := p_header_id;
l_mo_line_detail_rec.transaction_source_name := NULL;
l_mo_line_detail_rec.transaction_date := p_transaction_date;
l_mo_line_detail_rec.acct_period_id := get_acct_period_id(l_trolin_rec.organization_id,
p_transaction_date); --账户期间id
l_mo_line_detail_rec.distribution_account_id := NULL;
l_mo_line_detail_rec.transaction_reference := NULL;
l_mo_line_detail_rec.requisition_line_id := NULL;
l_mo_line_detail_rec.requisition_distribution_id := NULL;
l_mo_line_detail_rec.reason_id := l_trolin_rec.reason_id;
l_mo_line_detail_rec.lot_number := l_trolin_rec.lot_number;
l_mo_line_detail_rec.lot_expiration_date := NULL;
l_mo_line_detail_rec.serial_number := NULL;
l_mo_line_detail_rec.receiving_document := NULL;
l_mo_line_detail_rec.demand_id := NULL;
l_mo_line_detail_rec.rcv_transaction_id := NULL;
l_mo_line_detail_rec.move_transaction_id := NULL;
l_mo_line_detail_rec.completion_transaction_id := NULL;
l_mo_line_detail_rec.wip_entity_type := NULL;
l_mo_line_detail_rec.schedule_id := NULL;
l_mo_line_detail_rec.repetitive_line_id := NULL;
l_mo_line_detail_rec.employee_code := NULL;
l_mo_line_detail_rec.primary_switch := NULL;
l_mo_line_detail_rec.schedule_update_code := NULL;
l_mo_line_detail_rec.setup_teardown_code := NULL;
l_mo_line_detail_rec.item_ordering := NULL;
l_mo_line_detail_rec.negative_req_flag := NULL;
l_mo_line_detail_rec.operation_seq_num := NULL;
l_mo_line_detail_rec.picking_line_id := NULL;
l_mo_line_detail_rec.trx_source_line_id := p_line_id;
l_mo_line_detail_rec.trx_source_delivery_id := NULL;
l_mo_line_detail_rec.physical_adjustment_id := NULL;
l_mo_line_detail_rec.cycle_count_id := NULL;
l_mo_line_detail_rec.rma_line_id := NULL;
l_mo_line_detail_rec.customer_ship_id := NULL;
l_mo_line_detail_rec.currency_code := NULL;
l_mo_line_detail_rec.currency_conversion_rate := NULL;
l_mo_line_detail_rec.currency_conversion_type := NULL;
l_mo_line_detail_rec.currency_conversion_date := NULL;
l_mo_line_detail_rec.ussgl_transaction_code := NULL;
l_mo_line_detail_rec.vendor_lot_number := NULL;
l_mo_line_detail_rec.encumbrance_account := NULL;
l_mo_line_detail_rec.encumbrance_amount := NULL;
l_mo_line_detail_rec.ship_to_location := NULL;
l_mo_line_detail_rec.shipment_number := NULL;
l_mo_line_detail_rec.transfer_cost := NULL;
l_mo_line_detail_rec.transportation_cost := NULL;
l_mo_line_detail_rec.transportation_account := NULL;
l_mo_line_detail_rec.freight_code := NULL;
l_mo_line_detail_rec.containers := NULL;
l_mo_line_detail_rec.waybill_airbill := NULL;
l_mo_line_detail_rec.expected_arrival_date := NULL;
l_mo_line_detail_rec.transfer_subinventory := l_trolin_rec.to_subinventory_code;
l_mo_line_detail_rec.transfer_organization := 81;--NULL;
l_mo_line_detail_rec.new_average_cost := NULL;
l_mo_line_detail_rec.value_change := NULL;
l_mo_line_detail_rec.percentage_change := NULL;
l_mo_line_detail_rec.material_allocation_temp_id := NULL;
l_mo_line_detail_rec.demand_source_header_id := p_header_id;
l_mo_line_detail_rec.demand_source_line := to_char(p_line_id);
l_mo_line_detail_rec.demand_source_delivery := NULL;
l_mo_line_detail_rec.item_segments := NULL;
l_mo_line_detail_rec.item_description := NULL;
l_mo_line_detail_rec.item_trx_enabled_flag := NULL;
l_mo_line_detail_rec.item_location_control_code := 1;--NULL;
l_mo_line_detail_rec.item_restrict_subinv_code := 2;--NULL;
l_mo_line_detail_rec.item_restrict_locators_code := 2;--NULL;
l_mo_line_detail_rec.item_revision_qty_control_code := 1;--NULL;
l_mo_line_detail_rec.item_primary_uom_code := 'Ton';--NULL;
l_mo_line_detail_rec.item_uom_class := NULL;
l_mo_line_detail_rec.item_shelf_life_code := 1;--NULL;
l_mo_line_detail_rec.item_shelf_life_days := 0;--NULL;
l_mo_line_detail_rec.item_lot_control_code := 1;--NULL;
l_mo_line_detail_rec.item_serial_control_code := 1;--NULL;
l_mo_line_detail_rec.item_inventory_asset_flag := 'Y';--NULL;
l_mo_line_detail_rec.allowed_units_lookup_code := 3;--NULL;
l_mo_line_detail_rec.department_id := NULL;
l_mo_line_detail_rec.department_code := NULL;
l_mo_line_detail_rec.wip_supply_type := NULL;
l_mo_line_detail_rec.supply_subinventory := NULL;
l_mo_line_detail_rec.supply_locator_id := NULL;
l_mo_line_detail_rec.valid_subinventory_flag := NULL;
l_mo_line_detail_rec.valid_locator_flag := NULL;
l_mo_line_detail_rec.locator_segments := NULL;
l_mo_line_detail_rec.current_locator_control_code := NULL;
l_mo_line_detail_rec.number_of_lots_entered := NULL;
l_mo_line_detail_rec.wip_commit_flag := NULL;
l_mo_line_detail_rec.next_lot_number := NULL;
l_mo_line_detail_rec.lot_alpha_prefix := NULL;
l_mo_line_detail_rec.next_serial_number := NULL;
l_mo_line_detail_rec.serial_alpha_prefix := NULL;
l_mo_line_detail_rec.shippable_flag := NULL;
l_mo_line_detail_rec.posting_flag := 'Y';
l_mo_line_detail_rec.required_flag := NULL;
l_mo_line_detail_rec.process_flag := 'Y';
l_mo_line_detail_rec.error_code := NULL;
l_mo_line_detail_rec.error_explanation := NULL;
l_mo_line_detail_rec.attribute_category := '4';
l_mo_line_detail_rec.attribute1 := NULL;
l_mo_line_detail_rec.attribute2 := NULL;
l_mo_line_detail_rec.attribute3 := NULL;
l_mo_line_detail_rec.attribute4 := NULL;
l_mo_line_detail_rec.attribute5 := NULL;
l_mo_line_detail_rec.attribute6 := NULL;
l_mo_line_detail_rec.attribute7 := NULL;
l_mo_line_detail_rec.attribute8 := '汽运';
l_mo_line_detail_rec.attribute9 := NULL;
l_mo_line_detail_rec.attribute10 := NULL;
l_mo_line_detail_rec.attribute11 := NULL;
l_mo_line_detail_rec.attribute12 := NULL;
l_mo_line_detail_rec.attribute13 := NULL;
l_mo_line_detail_rec.attribute14 := NULL;
l_mo_line_detail_rec.attribute15 := NULL;
l_mo_line_detail_rec.movement_id := NULL;
l_mo_line_detail_rec.reservation_quantity := NULL;
l_mo_line_detail_rec.shipped_quantity := NULL;
l_mo_line_detail_rec.transaction_line_number := NULL;
l_mo_line_detail_rec.task_id := NULL;
l_mo_line_detail_rec.to_task_id := NULL;
l_mo_line_detail_rec.source_task_id := NULL;
l_mo_line_detail_rec.project_id := NULL;
l_mo_line_detail_rec.source_project_id := NULL;
l_mo_line_detail_rec.pa_expenditure_org_id := NULL;
l_mo_line_detail_rec.to_project_id := NULL;
l_mo_line_detail_rec.expenditure_type := NULL;
l_mo_line_detail_rec.final_completion_flag := null;--'N';
l_mo_line_detail_rec.transfer_percentage := NULL;
l_mo_line_detail_rec.transaction_sequence_id := NULL;
l_mo_line_detail_rec.material_account := NULL;
l_mo_line_detail_rec.material_overhead_account := NULL;
l_mo_line_detail_rec.resource_account := NULL;
l_mo_line_detail_rec.outside_processing_account := NULL;
l_mo_line_detail_rec.overhead_account := NULL;
l_mo_line_detail_rec.flow_schedule := NULL;
l_mo_line_detail_rec.cost_group_id := NULL;
l_mo_line_detail_rec.demand_class := NULL;
l_mo_line_detail_rec.qa_collection_id := NULL;
l_mo_line_detail_rec.kanban_card_id := NULL;
l_mo_line_detail_rec.overcompletion_transaction_id := NULL;
l_mo_line_detail_rec.overcompletion_primary_qty := NULL;
l_mo_line_detail_rec.overcompletion_transaction_qty := NULL;
l_mo_line_detail_rec.end_item_unit_number := NULL;
l_mo_line_detail_rec.scheduled_payback_date := NULL;
l_mo_line_detail_rec.line_type_code := NULL;
l_mo_line_detail_rec.parent_transaction_temp_id := NULL;
l_mo_line_detail_rec.put_away_strategy_id := NULL;
l_mo_line_detail_rec.put_away_rule_id := NULL;
l_mo_line_detail_rec.pick_strategy_id := l_trolin_rec.pick_strategy_id;
l_mo_line_detail_rec.pick_rule_id := 2;--NULL;
l_mo_line_detail_rec.common_bom_seq_id := NULL;
l_mo_line_detail_rec.common_routing_seq_id := NULL;
l_mo_line_detail_rec.cost_type_id := NULL;
l_mo_line_detail_rec.org_cost_group_id := NULL;
l_mo_line_detail_rec.move_order_line_id := p_line_id;
l_mo_line_detail_rec.task_group_id := NULL;
l_mo_line_detail_rec.pick_slip_number := l_trolin_rec.pick_slip_number;
l_mo_line_detail_rec.reservation_id := NULL;
l_mo_line_detail_rec.transaction_status := 2; -- --Indicates if it has to be processed or just saved. NULL or 1 is default behavior. 2 is save only. 3 is ready to process
-- l_mo_line_detail_rec.WMS_TASK_TYPE := 1;
--l_mo_line_detail_rec.wms_task_status := 1;
--l_mo_line_detail_rec.move_order_header_id := l_trolin_rec.header_id;
--l_mo_line_detail_rec.trx_source_delivery_id :=1;
--l_mo_line_detail_rec.physical_adjustment_id :=1;
l_mo_line_detail_rec.transfer_cost_group_id := NULL;
l_mo_line_detail_rec.lpn_id := NULL;
l_mo_line_detail_rec.transfer_lpn_id := NULL;
l_mo_line_detail_rec.pick_slip_date := l_trolin_rec.pick_slip_date;
l_mo_line_detail_rec.content_lpn_id := NULL;
l_mo_line_detail_rec.secondary_transaction_quantity := NULL;
l_mo_line_detail_rec.secondary_uom_code := NULL;
--调用标准的API对MMTT进行插入
inv_mo_line_detail_util.insert_row(x_return_status => x_return_status,
p_mo_line_detail_rec => l_mo_line_detail_rec);
IF x_return_status = fnd_api.G_RET_STS_SUCCESS THEN
x_transaction_temp_id := l_transaction_temp_id; --回传mmtt的唯一性标识
END IF;
EXCEPTION
WHEN OTHERS THEN
put_log('插入mmtt处理行时出现异常:' || SQLERRM);
END insert_mmtt_row;
/*=======================================================
* FUNCTION / PROCEDURE
* delete_mmtt_row
* DESCRIPTION:
* 删除某一物料搬运单事务处理临时表的一条记录
* ARGUMENT:
* p_header_id:物料搬运单头id
* p_line_id :物料搬运单行id
* RETURN:
* N/A
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
PROCEDURE delete_mmtt_row(x_return_status OUT VARCHAR2,
p_line_id IN NUMBER,
p_transaction_temp_id IN NUMBER) IS
BEGIN
inv_mo_line_detail_util.delete_row(x_return_status => x_return_status,
p_line_id => p_line_id,
p_line_detail_id => p_transaction_temp_id);
EXCEPTION
WHEN OTHERS THEN
put_log('删除某一物料搬运单的处理行时出现异常:' || SQLERRM);
END delete_mmtt_row;
/*=======================================================
* FUNCTION / PROCEDURE
* process_data
* DESCRIPTION:
* 自动处理物料搬运单
* ARGUMENT:
* x_return_status :返回状态
* x_msg_count :返回的行数
* x_msg_data :返回的信息
* p_line_id :物料搬运单行id
* RETURN:
* N/A
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
PROCEDURE process_data(x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_line_id IN NUMBER
) IS
x_number_of_rows NUMBER;
x_detailed_qty NUMBER;
x_revision VARCHAR2(3);
x_locator_id NUMBER;
x_transfer_to_location NUMBER;
x_lot_number VARCHAR2(80);
x_expiration_date DATE;
x_transaction_temp_id NUMBER;
l_trolin_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
l_mold_tbl INV_MO_LINE_DETAIL_UTIL.g_mmtt_tbl_type;
x_mmtt_tbl INV_MO_LINE_DETAIL_UTIL.g_mmtt_tbl_type;
x_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
l_mo_line_detail_rec inv_mo_line_detail_util.g_mmtt_rec;
o_success VARCHAR2(50);
o_message VARCHAR2(500);
BEGIN
INV_Replenish_Detail_PUB.line_details_pub(p_line_id => p_line_id,
x_number_of_rows => x_number_of_rows,
x_detailed_qty => x_detailed_qty,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_revision => x_revision,
x_locator_id => x_locator_id,
x_transfer_to_location => x_transfer_to_location,
x_lot_number => x_lot_number,
x_expiration_date => x_expiration_date,
x_transaction_temp_id => x_transaction_temp_id,
p_transaction_header_id => null ,
p_transaction_mode => NULL,
p_move_order_type => 1,
p_serial_flag => FND_API.G_TRUE,
p_plan_tasks => NULL,
p_auto_pick_confirm => true,
p_commit => true);
put_log('x_number_of_rows:' || x_number_of_rows);
put_log('x_return_status:' || x_return_status);
put_log('--------创建物料搬运单分配行结束----------');
IF x_number_of_rows > 0 AND x_return_status = fnd_api.G_RET_STS_SUCCESS THEN
--得到物料搬运单行信息
IF l_mo_debug_falg = 'Y' THEN --输出调试信息
put_log('--------获得物料搬运单行信息开始----------');
END IF;
l_trolin_tbl := inv_trolin_util.query_rows(p_line_id => p_line_id);
IF l_mo_debug_falg = 'Y' THEN--输出调试信息
put_log('--------获得物料搬运单行信息结束----------');
put_log( l_trolin_tbl.count);
put_log('--------自动挑库确认开始----------');
END IF;
--自动挑库确认
jhzh_mobil_pohen_pkg.move_order_pick_release(529259,--p_move_order_line_id NUMBER,
o_success ,
o_message );
dbms_output.put_line(o_success);
dbms_output.put_line(o_message);
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_TRUE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_move_order_type => null,--1,
p_transaction_mode => null,--1,
p_trolin_tbl => l_trolin_tbl,
p_mold_tbl => l_mold_tbl,
x_mmtt_tbl => x_mmtt_tbl,
x_trolin_tbl => x_trolin_tbl,
p_transaction_date => sysdate);
IF l_mo_debug_falg = 'Y' THEN--输出调试信息
put_log('x_return_status:' || x_return_status);
put_log('x_msg_count:' || x_msg_count);
put_log('x_msg_data:' || x_msg_data);
put_log('--------自动挑库确认结束----------');
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
put_log('自动处理物料搬运单时出错:' || SQLERRM);
END process_data;
/* =============================================
* FUNCTION / PROCEDURE
* NAME : MOVE_ORDER_PICK_RELEASE
*
* DESCRIPTION: 分配物料搬运单数量
*
* ARGUMENT:
*
* RETURN:
*
* HISTORY:
* 1.00 mm/dd/yyyy Author Name
* Creation Description
* =============================================*/
PROCEDURE move_order_pick_release(p_move_order_line_id NUMBER,
o_success OUT VARCHAR2,
o_message OUT VARCHAR2) IS
p_api_version NUMBER;
p_init_msg_list VARCHAR2(15);
x_return_status VARCHAR2(1);
p_commit VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
v_mo_line_tbl inv_move_order_pub.trolin_tbl_type;
v_pick_release_status inv_pick_release_pub.inv_release_status_tbl_type;
BEGIN
v_mo_line_tbl := inv_trolin_util.query_rows(p_line_id => p_move_order_line_id); --Move Order Line ID
inv_pick_release_pub.pick_release(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_mo_line_tbl => v_mo_line_tbl,
p_auto_pick_confirm => 1, --1 (yes) or 2 (no)
p_grouping_rule_id => NULL,
p_allow_partial_pick => fnd_api.g_true,
x_pick_release_status => v_pick_release_status,
p_plan_tasks => FALSE);
IF x_return_status NOT IN (fnd_api.g_ret_sts_success, 'W') THEN
o_success := 'N';
o_message := '分配物料搬运单数量失败';
ELSE
o_success := 'Y';
o_message := '分配物料搬运单数量成功';
END IF;
/*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_TRUE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_move_order_type => null,--1,
p_transaction_mode => null,--1,
p_trolin_tbl => l_trolin_tbl,
p_mold_tbl => l_mold_tbl,
x_mmtt_tbl => x_mmtt_tbl,
x_trolin_tbl => x_trolin_tbl,
p_transaction_date => sysdate); */
EXCEPTION
WHEN OTHERS THEN
o_success := 'N';
o_message := '分配物料搬运单数量出现意外';
END;
/*=======================================================
* FUNCTION / PROCEDURE
* main
* DESCRIPTION:
* 并发程序入口
* ARGUMENT:
* errbuf :并发程序的系统参数，返回错误信息
* retcode :并发程序的系统参数, 执行状态
* RETURN:
* N/A
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
/*================================================================
* 主程序的步骤：
* 10 初始化
* 20 处理历史记录
* 30 验证数据有效性
* 40 在MMTT表中插入一条记录
* 50 自动处理物料搬运单
* 60 更新客户化表的状态和错误信息
* 70 输出处理结果
=================================================================*/
PROCEDURE main IS
--PROCEDURE main(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2) is
--使用cursor取出客户化事务处理表中的所有物料搬运单头信息,不取重复的记录
CURSOR cur_item_move_imp_line(p_header_id NUMBER) IS
/*SELECT ceimi.header_id header_id, --物料搬运单头id
ceimi.request_number request_number, --物料搬运单编号
ceimi.line_id line_id, --物料搬运单行id
ceimi.transaction_quantity transaction_quantity, --事务处理数量
ceimi.transaction_date transaction_date --事务处理日期
FROM cux_ediint_item_move_in ceimi
WHERE ceimi.process_id IN (0, 3) --处理新增或者上次出错的记录,0表示没有处理的记录,3表示上一次处理出错的记录
AND ceimi.header_id = p_header_id
ORDER BY ceimi.line_id; */
select mtl.header_id,mtl.line_id,mtl.quantity transaction_quantity, sysdate transaction_date
from mtl_txn_request_lines mtl
where mtl.header_id = p_header_id
;
x_return_status VARCHAR2(10); --处理状态
x_msg_count NUMBER; --错误信息数量
x_msg_data VARCHAR2(2000); --错误信息
x_trohdr_rec INV_MOVE_ORDER_PUB.Trohdr_Rec_Type; --物料搬运单头记录
x_trohdr_val_rec INV_MOVE_ORDER_PUB.Trohdr_Val_Rec_Type; --物料搬运单头对应的记录
x_trolin_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type; --物料搬运单的所有行记录
x_trolin_val_tbl INV_MOVE_ORDER_PUB.Trolin_Val_Tbl_Type; --物料搬运单的所有行对应的记录
x_trolin_rec INV_MOVE_ORDER_PUB.Trolin_Rec_Type; --一条物料搬运单行
l_line_count NUMBER := 0; --处理的总行数
l_line_success_count NUMBER := 0; --处理成功的行数
x_transaction_temp_id NUMBER; --取得mmtt表的唯一性标识
x_processd_flag VARCHAR2(1); --上一次的历史记录是否被手工处理
p_header_id number;----物料搬运单头id
BEGIN
--循环所有的物料搬运单
-- FOR rec_item_move_imp_header IN cur_item_move_imp_header LOOP
/*============================================================
* 10 初始化
* ------------------------------------------------------------
* 描述：初始化变量,并得到物料搬运单的相关信息
=============================================================*/
mo_global.set_policy_context ('S', 81);
inv_globals.set_org_id (81);
FND_GLOBAL.APPS_INITIALIZE(user_id => 2004,--user_id,
resp_id =>50620, --resp_id,
resp_appl_id =>401 --resp_appl_id
);
p_header_id:=496536; --物料搬运单头id
x_trohdr_rec := NULL; --物料搬运单头记录
x_trohdr_val_rec := NULL;
--得到物料搬运单信息
x_return_status := NULL; --处理状态
get_move_order(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_trohdr_rec => x_trohdr_rec,
x_trohdr_val_rec => x_trohdr_val_rec,
x_trolin_tbl => x_trolin_tbl,
x_trolin_val_tbl => x_trolin_val_tbl,
p_header_id => p_header_id);
--物料搬运单头状态正常,则处理此物料搬运单的所有行记录
IF x_return_status = fnd_api.G_RET_STS_SUCCESS THEN
--循环物料搬运单的行,自动处理物料搬运单按照行进行处理
FOR rec_item_move_imp_line IN cur_item_move_imp_line(p_header_id) LOOP
begin
--进行初始化
x_return_status := NULL; --处理状态
x_msg_count := NULL; --错误信息条数
x_msg_data := NULL; --错误信息
--得到某一物料搬运单行的信息
get_trolin_rec(x_return_status => x_return_status,
x_trolin_rec => x_trolin_rec,
p_trolin_tbl => x_trolin_tbl,
p_line_id => rec_item_move_imp_line.line_id);
IF x_return_status = fnd_api.G_RET_STS_SUCCESS THEN
l_line_count := l_line_count + 1; --此次处理的总条数
/*=============================================================
* 40 在MMTT表中插入一条记录
* -------------------------------------------------------------
* 描述：数据验证通过后，给MMTT表中的字段赋值，并
* 在MMTT表中插入一条记录。
===============================================================*/
--插入一条MMTT的记录
insert_mmtt_row(x_return_status => x_return_status,
x_transaction_temp_id => x_transaction_temp_id,
p_trohdr_rec => x_trohdr_rec,
p_trohdr_val_rec => x_trohdr_val_rec,
p_trolin_tbl => x_trolin_tbl,
p_trolin_val_tbl => x_trolin_val_tbl,
p_header_id => rec_item_move_imp_line.header_id,
p_line_id => rec_item_move_imp_line.line_id,
p_transaction_quantity => rec_item_move_imp_line.transaction_quantity,
p_transaction_date => sysdate--rec_item_move_imp_line.transaction_date
);
/*===============================================================
* 50 自动处理物料搬运单
* ---------------------------------------------------------------
* 描述：调用API自动处理物料搬运单
================================================================*/
IF x_return_status = fnd_api.G_RET_STS_SUCCESS THEN
--处理物料搬运单
process_data(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_line_id => rec_item_move_imp_line.line_id );
ELSE
put_log('错误信息为:' || x_msg_data);
put_log('CUX:物料搬运单事务处理时,发生异常:' || SQLERRM);
END IF;
--删除MMTT表中未被成功处理的记录
/*delete_mmtt_row(x_return_status => x_return_status,
p_line_id => rec_item_move_imp_line.line_id,
p_transaction_temp_id => x_transaction_temp_id); */
END IF;
COMMIT;
EXCEPTION --物料搬运行出现异常
WHEN OTHERS THEN
--如果此条记录出现异常,则回滚到SAVEPOINT cux_item_move_line
--ROLLBACK TO cux_item_move_line;
put_log('处理物料搬运单行时,发生异常:' || SQLERRM);
END; --end begin
END LOOP; --FOR rec_item_move_imp_line IN cur_item_move_imp_line LOOP
ELSE
-- retcode := 1; --如果存在没有成功处理的物料搬运单，则显示警告
put_log('得到物料搬运单头时发生异常:' || x_msg_data);
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
--retcode := 2;
put_log('错误信息为:' || x_msg_data);
put_log('CUX:物料搬运单事务处理时,发生异常:' || SQLERRM);
END main;
|
|
|Archiver|小黑屋|手机版|壹佰网 ERP100
( 京ICP备19053597号-2 )
IP卡
狗仔卡
发表于 2011/3/31 14:22:28
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
抢沙发
千斤顶
显身卡
楼主