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 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;
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;
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;
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;
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;