[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Order_Line_Status(
/* $Header: cspgtmub.pls 120.1 2006/07/20 05:39:33 hhaugeru noship $ */
-- Start of Comments
-- Procedure name : update_order_line_status
-- Purpose : This procedure updates the line status of a move order in the mtl_txn_request_lines table.
-- History :
-- Person Date Descriptions
-- ------ ---- --------------
-- klou 03-Jan-2000 Added options for NONE or FULL validations.
-- klou 01-Jan-2000 created.
--
-- NOTES: If validations have been done in the precedent procedure from which this one is being called, doing a
-- full validation here is unnecessary. To avoid repeating the same validations, you can set the
-- p_validation_level to fnd_api.g_valid_level_none when making the procedure call. However, it is your
-- responsibility to make sure all proper validations have been done before calling this procedure.
-- You are recommended to let this procedure handle the validations if you are not sure.
--
-- CAUTIONS: This procedure *ALWAYS* calls other procedures with validation_level set to FND_API.G_VALID_LEVEL_NONE.
-- If you do not do your own validations before calling this procedure, you should set the p_validation_level
-- to FND_API.G_VALID_LEVEL_FULL when making the call.
-- End of Comments
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
P_Commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_organization_id IN NUMBER,
p_move_order_line_id IN NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(50) := 'Update_Order_Line_Sts';
l_last_update_date DATE;
SAVEPOINT Update_Order_Line_Sts_PUB;
select organization_id into l_check_existence
from mtl_parameters
where organization_id = p_organization_id ;
SELECT transaction_temp_id into l_check_existence
FROM mtl_material_transactions_temp
WHERE move_order_line_id = p_move_order_line_id
AND organization_id = p_organization_id;
select header_id into l_header_id
from csp_moveorder_lines
where line_id = p_move_order_line_id;
INV_Trolin_Util.Update_Row_Status
( p_line_id => p_move_order_line_id,
p_status => 5 -- update status to 5 = closed
);
update mtl_txn_request_lines
set line_status = 5
where line_id = p_move_order_line_id;
Rollback to Update_Order_Line_Sts_PUB;
Rollback to Update_Order_Line_Sts_PUB;
END Update_Order_Line_Status;
SELECT line_id
FROM CSP_MOVEORDER_LINES
WHERE header_id = p_move_order_header_id;
SELECT line_status INTO l_line_status
FROM mtl_txn_request_lines
WHERE line_id = l_line_id;
l_last_update_date DATE;
SELECT
TRANSACTION_HEADER_ID ,
TRANSACTION_TEMP_ID ,
SOURCE_CODE ,
SOURCE_LINE_ID ,
TRANSACTION_MODE ,
LOCK_FLAG ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
INVENTORY_ITEM_ID ,
REVISION ,
ORGANIZATION_ID ,
SUBINVENTORY_CODE ,
LOCATOR_ID ,
TRANSACTION_QUANTITY ,
PRIMARY_QUANTITY ,
TRANSACTION_UOM ,
TRANSACTION_COST ,
TRANSACTION_TYPE_ID ,
TRANSACTION_ACTION_ID ,
TRANSACTION_SOURCE_TYPE_ID ,
TRANSACTION_SOURCE_ID ,
TRANSACTION_SOURCE_NAME ,
TRANSACTION_DATE ,
ACCT_PERIOD_ID ,
DISTRIBUTION_ACCOUNT_ID ,
TRANSACTION_REFERENCE ,
REQUISITION_LINE_ID ,
REQUISITION_DISTRIBUTION_ID ,
REASON_ID ,
LOT_NUMBER ,
LOT_EXPIRATION_DATE ,
SERIAL_NUMBER ,
RECEIVING_DOCUMENT ,
DEMAND_ID ,
RCV_TRANSACTION_ID ,
MOVE_TRANSACTION_ID ,
COMPLETION_TRANSACTION_ID ,
WIP_ENTITY_TYPE ,
SCHEDULE_ID ,
REPETITIVE_LINE_ID ,
EMPLOYEE_CODE ,
PRIMARY_SWITCH ,
SCHEDULE_UPDATE_CODE ,
SETUP_TEARDOWN_CODE ,
ITEM_ORDERING ,
NEGATIVE_REQ_FLAG ,
OPERATION_SEQ_NUM ,
PICKING_LINE_ID ,
TRX_SOURCE_LINE_ID ,
TRX_SOURCE_DELIVERY_ID ,
PHYSICAL_ADJUSTMENT_ID ,
CYCLE_COUNT_ID ,
RMA_LINE_ID ,
CUSTOMER_SHIP_ID ,
CURRENCY_CODE ,
CURRENCY_CONVERSION_RATE ,
CURRENCY_CONVERSION_TYPE ,
CURRENCY_CONVERSION_DATE ,
USSGL_TRANSACTION_CODE ,
VENDOR_LOT_NUMBER ,
ENCUMBRANCE_ACCOUNT ,
ENCUMBRANCE_AMOUNT ,
SHIP_TO_LOCATION ,
SHIPMENT_NUMBER ,
TRANSFER_COST ,
TRANSPORTATION_COST ,
TRANSPORTATION_ACCOUNT ,
FREIGHT_CODE ,
CONTAINERS ,
WAYBILL_AIRBILL ,
EXPECTED_ARRIVAL_DATE ,
TRANSFER_SUBINVENTORY ,
TRANSFER_ORGANIZATION ,
TRANSFER_TO_LOCATION ,
NEW_AVERAGE_COST ,
VALUE_CHANGE ,
PERCENTAGE_CHANGE ,
MATERIAL_ALLOCATION_TEMP_ID ,
DEMAND_SOURCE_HEADER_ID ,
DEMAND_SOURCE_LINE ,
DEMAND_SOURCE_DELIVERY ,
ITEM_SEGMENTS ,
ITEM_DESCRIPTION ,
ITEM_TRX_ENABLED_FLAG ,
ITEM_LOCATION_CONTROL_CODE ,
ITEM_RESTRICT_SUBINV_CODE ,
ITEM_RESTRICT_LOCATORS_CODE ,
ITEM_REVISION_QTY_CONTROL_CODE ,
ITEM_PRIMARY_UOM_CODE ,
ITEM_UOM_CLASS ,
ITEM_SHELF_LIFE_CODE ,
ITEM_SHELF_LIFE_DAYS ,
ITEM_LOT_CONTROL_CODE ,
ITEM_SERIAL_CONTROL_CODE ,
ITEM_INVENTORY_ASSET_FLAG ,
ALLOWED_UNITS_LOOKUP_CODE ,
DEPARTMENT_ID ,
DEPARTMENT_CODE ,
WIP_SUPPLY_TYPE ,
SUPPLY_SUBINVENTORY ,
SUPPLY_LOCATOR_ID ,
VALID_SUBINVENTORY_FLAG ,
VALID_LOCATOR_FLAG ,
LOCATOR_SEGMENTS ,
CURRENT_LOCATOR_CONTROL_CODE ,
NUMBER_OF_LOTS_ENTERED ,
WIP_COMMIT_FLAG ,
NEXT_LOT_NUMBER ,
LOT_ALPHA_PREFIX ,
NEXT_SERIAL_NUMBER ,
SERIAL_ALPHA_PREFIX ,
SHIPPABLE_FLAG ,
POSTING_FLAG ,
REQUIRED_FLAG ,
PROCESS_FLAG ,
ERROR_CODE ,
ERROR_EXPLANATION ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
MOVEMENT_ID ,
RESERVATION_QUANTITY ,
SHIPPED_QUANTITY ,
TRANSACTION_LINE_NUMBER ,
TASK_ID ,
TO_TASK_ID ,
SOURCE_TASK_ID ,
PROJECT_ID ,
SOURCE_PROJECT_ID ,
PA_EXPENDITURE_ORG_ID ,
TO_PROJECT_ID ,
EXPENDITURE_TYPE ,
FINAL_COMPLETION_FLAG ,
TRANSFER_PERCENTAGE ,
TRANSACTION_SEQUENCE_ID ,
MATERIAL_ACCOUNT ,
MATERIAL_OVERHEAD_ACCOUNT ,
RESOURCE_ACCOUNT ,
OUTSIDE_PROCESSING_ACCOUNT ,
OVERHEAD_ACCOUNT ,
FLOW_SCHEDULE ,
COST_GROUP_ID ,
DEMAND_CLASS ,
QA_COLLECTION_ID ,
KANBAN_CARD_ID ,
OVERCOMPLETION_TRANSACTION_ID ,
OVERCOMPLETION_PRIMARY_QTY ,
OVERCOMPLETION_TRANSACTION_QTY ,
--PROCESS_TYPE , --removed 01/13/00. process_type does not exist in the mmtt table.
END_ITEM_UNIT_NUMBER ,
SCHEDULED_PAYBACK_DATE ,
LINE_TYPE_CODE ,
PARENT_TRANSACTION_TEMP_ID ,
PUT_AWAY_STRATEGY_ID ,
PUT_AWAY_RULE_ID ,
PICK_STRATEGY_ID ,
PICK_RULE_ID ,
COMMON_BOM_SEQ_ID ,
COMMON_ROUTING_SEQ_ID ,
COST_TYPE_ID ,
ORG_COST_GROUP_ID ,
MOVE_ORDER_LINE_ID ,
TASK_GROUP_ID ,
PICK_SLIP_NUMBER ,
RESERVATION_ID ,
TRANSACTION_STATUS ,
STANDARD_OPERATION_ID ,
TASK_PRIORITY ,
-- ADDED by phegde 02/23
WMS_TASK_TYPE ,
PARENT_LINE_ID
-- SOURCE_LOT_NUMBER
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_transaction_temp_id
AND organization_id = p_organization_id;
SELECT nvl(quantity_shipped, 0), nvl(quantity_received, 0)
FROM CSP_Packlist_Lines
WHERE packlist_line_id = p_packlist_line_id
and organization_id = p_organization_id;
SELECT distinct header_id
FROM csp_moveorder_lines
WHERE line_id = l_line_id;
SELECT mtl_material_transactions_s.nextval
FROM dual;
SELECT packlist_status
FROM csp_packlist_headers
WHERE packlist_header_id = l_packlist_header_id;
SELECT acct_period_id
FROM org_acct_periods
WHERE trunc(period_start_date) <= trunc(sysdate)
AND trunc(schedule_close_date) >= trunc(sysdate)
AND organization_id = p_organization_id
AND period_close_date is null
AND nvl(open_flag,'Y') = 'Y';
SELECT MTL_MATERIAL_TRANSACTIONS_S.nextval FROM dual;
SELECT
PACKLIST_HEADER_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
ORGANIZATION_ID ,
PACKLIST_NUMBER ,
SUBINVENTORY_CODE ,
PACKLIST_STATUS ,
DATE_CREATED ,
DATE_PACKED ,
DATE_SHIPPED ,
DATE_RECEIVED ,
CARRIER ,
SHIPMENT_METHOD ,
WAYBILL ,
COMMENTS ,
LOCATION_ID,
PARTY_SITE_ID,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15
From CSP_PACKLIST_HEADERS
WHERE organization_id = p_organization_id
AND packlist_header_id = l_packlist_header_id;
Select nvl(quantity_shipped,0)-nvl(quantity_received,0)
From csp_packlist_lines
Where packlist_line_id = p_packlist_line_id
And organization_id = p_organization_id;
select organization_id into l_check_existence
from mtl_parameters
where organization_id = p_organization_id;
select packlist_header_id into l_packlist_header_id
from csp_packlist_lines
where organization_id = p_organization_id
and packlist_line_id = p_packlist_line_id;
select packlist_line_id into l_check_existence
from csp_packlist_lines
where packlist_line_id = p_packlist_line_id
and organization_id = p_organization_id;
CSP_PL_SHIP_UTIL.Update_Packlist_Sts_Qty (
P_Api_Version_Number => l_api_version_number,
P_Init_Msg_List => FND_API.G_true,
P_Commit => l_commit,
p_validation_level => l_validation_level,
p_organization_id => p_organization_id,
p_packlist_line_id => p_packlist_line_id,
p_line_status => '4',
p_quantity_packed => NULL,
p_quantity_shipped => NULL,
p_quantity_received => 0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
CSP_PL_SHIP_UTIL.update_packlist_header_sts (
P_Api_Version_Number => l_api_version_number,
P_Init_Msg_List => FND_API.G_true,
P_Commit => l_commit,
p_validation_level => l_validation_level,
p_packlist_header_id => l_packlist_header_id,
p_organization_id => p_organization_id,
p_packlist_status => '3',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
INV_Trohdr_Util.Update_Row_Status
( p_header_id => l_header_id,
p_status => 5);
SELECT secondary_inventory_name into l_subinventory_code
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_to_subinventory_code
AND organization_id = p_organization_id
AND nvl(disable_date, sysdate+1) > sysdate;
SELECT inventory_location_id into l_check_existence
FROM mtl_item_locations
WHERE inventory_location_id = p_to_locator_id
AND organization_id = organization_id
AND subinventory_code = p_to_subinventory_code
AND nvl(disable_date, sysdate+1) > sysdate;
SELECT line_id, inventory_item_id, to_subinventory_code, to_locator_id, uom_code
INTO l_csp_mtltxn_rec.move_order_line_id, l_csp_mtltxn_rec.inventory_item_id,
l_csp_mtltxn_rec.transfer_subinventory, l_csp_mtltxn_rec.transfer_to_location,
l_csp_mtltxn_rec.transaction_uom
FROM mtl_txn_request_lines
WHERE line_id = (SELECT line_id FROM csp_packlist_lines WHERE packlist_line_id = p_packlist_line_id)
AND organization_id = p_organization_id;
SELECT primary_uom_code, serial_number_control_code, lot_control_code
INTO l_csp_mtltxn_rec.item_primary_uom_code, l_csp_mtltxn_rec.item_serial_control_code,
l_csp_mtltxn_rec.item_lot_control_code
FROM mtl_system_items
WHERE inventory_item_id = l_csp_mtltxn_rec.inventory_item_id
AND organization_id = p_organization_id;
l_csp_mtltxn_rec.LAST_UPDATE_DATE := sysdate;
l_csp_mtltxn_rec.last_update_login := g_login_id;
l_csp_mtltxn_rec.last_updated_by := g_user_id;
l_mtlt_tbl(l_index).last_update_date := sysdate;
l_mtlt_tbl(l_index).last_updated_by := g_user_id;
l_mtlt_tbl(l_index).last_update_login := g_login_id;
SELECT expiration_date INTO l_mtlt_tbl(l_index).lot_expiration_date
FROM mtl_lot_numbers
WHERE inventory_item_id = l_csp_mtltxn_rec.inventory_item_id
AND organization_id = p_organization_id
AND lot_number = p_lot_number;
csp_pp_util.insert_mtlt(
x_return_status => l_return_status
,p_mtlt_tbl => l_mtlt_tbl
,p_mtlt_tbl_size => 1
);
update mtl_transaction_lots_temp
set serial_transaction_temp_id = l_mtlt_tbl(l_index).serial_transaction_temp_id
where transaction_temp_id = l_mtlt_tbl(l_index).transaction_temp_id
and lot_number = l_mtlt_tbl(l_index).lot_number;
l_msnt_tbl(l_index).last_update_date := sysdate;
l_msnt_tbl(l_index).last_updated_by := g_user_id;
l_msnt_tbl(l_index).last_update_login := g_login_id;
csp_pp_util.insert_msnt(
x_return_status => l_return_status
,p_msnt_tbl => l_msnt_tbl
,p_msnt_tbl_size => 1
);
l_msnt_tbl(l_index).last_update_date := sysdate;
l_msnt_tbl(l_index).last_updated_by := g_user_id;
l_msnt_tbl(l_index).last_update_login := g_login_id;
csp_pp_util.insert_msnt(
x_return_status => l_return_status
,p_msnt_tbl => l_msnt_tbl
,p_msnt_tbl_size => 1
);
select restrict_subinventories_code into l_restrict_sub_code
from mtl_system_items
where inventory_item_id = l_inventory_item_id
and organization_id = p_organization_id;
select secondary_inventory into l_restricted_sub
from mtl_item_sub_inventories
where organization_id = p_organization_id
and inventory_item_id = l_inventory_item_id
and secondary_inventory = p_to_subinventory_code;
SELECT restrict_locators_code INTO l_restrict_locators_code
FROM mtl_system_items
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = p_organization_id;
SELECT locator_id INTO l_check_existence
FROM mtl_item_loc_defaults
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = p_organization_id
AND locator_id = p_to_locator_id
AND subinventory_code = p_to_subinventory_code;
l_csp_mtltxn_rec.last_update_date := sysdate;
CSP_Material_Transactions_PVT.Update_material_transactions(
P_Api_Version_Number => p_api_version_number,
P_Init_Msg_List => p_init_msg_list,
P_Commit => l_commit,
p_validation_level => p_validation_level,
P_CSP_Rec => l_csp_mtltxn_rec,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data);
CSP_Material_Transactions_PVT.Update_material_transactions(
P_Api_Version_Number => p_api_version_number,
P_Init_Msg_List => p_init_msg_list,
P_Commit => fnd_api.g_false,
p_validation_level => l_validation_level,
P_CSP_Rec => l_csp_mtltxn_rec,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
l_csp_mtltxn_over_rec.last_update_date := sysdate;
SELECT * FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_csp_mtltxn_rec.transaction_temp_id -- Get the lot record form the original temp id
ORDER BY TRANSACTION_QUANTITY DESC;
csp_pp_util.insert_mtlt(
x_return_status => l_return_status
,p_mtlt_tbl => l_mtlt_tbl
,p_mtlt_tbl_size => 1
);
select nvl(quantity, 0) into l_trolin_rec.quantity
from mtl_txn_request_lines
where line_id = l_move_order_line_id;
select nvl(quantity_delivered,0) into l_trolin_rec.quantity_delivered
from mtl_txn_request_lines
where line_id = l_move_order_line_id;
INV_Trolin_Util.Update_Row_Status
( p_line_id => l_move_order_line_id,
p_status => 5 ); -- update status to 5 = closed
INV_Trohdr_Util.Update_Row_Status
( p_header_id =>l_header_id,
p_status => 5); -- update status to 5 = closed
END IF; -- end the update_line_status block
CSP_Material_Transactions_PVT.Update_material_transactions(
P_Api_Version_Number => p_api_version_number,
P_Init_Msg_List => p_init_msg_list,
P_Commit => fnd_api.g_false,
p_validation_level => l_validation_level,
P_CSP_Rec => l_csp_mtltxn_rec,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
select nvl(quantity, 0) into l_trolin_rec.quantity
from mtl_txn_request_lines
where line_id = l_move_order_line_id;
select nvl(quantity_delivered,0) into l_trolin_rec.quantity_delivered
from mtl_txn_request_lines
where line_id = l_move_order_line_id;
INV_Trolin_Util.Update_Row_Status
( p_line_id => l_move_order_line_id,
p_status => 5 ); -- update status to 5 = closed
INV_Trohdr_Util.Update_Row_Status
( p_header_id => l_header_id,
p_status => 5); -- update status to 5 = closed
END IF; -- end the update_line_status block
SELECT PACKLIST_SERIAL_LOT_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PACKLIST_LINE_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
QUANTITY,
LOT_NUMBER,
SERIAL_NUMBER
FROM CSP_Packlist_Serial_Lots
WHERE packlist_line_id = p_packlist_line_id
AND organization_id = p_organization_id;
l_csp_mtltxn_bak_rec.last_update_date := sysdate;
CSP_Material_Transactions_PVT.Update_material_transactions(
P_Api_Version_Number => p_api_version_number,
P_Init_Msg_List => p_init_msg_list,
P_Commit => fnd_api.g_false,
p_validation_level => l_validation_level,
P_CSP_Rec => l_csp_mtltxn_bak_rec,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
INV_Trohdr_Util.Update_Row_Status ( p_header_id => l_header_id,
p_status => 5);
l_csp_mtltxn_bak_rec.last_update_date := sysdate;
CSP_Material_Transactions_PVT.Update_material_transactions(
P_Api_Version_Number => p_api_version_number,
P_Init_Msg_List => p_init_msg_list,
P_Commit => fnd_api.g_false,
p_validation_level => l_validation_level,
P_CSP_Rec => l_csp_mtltxn_bak_rec,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
CSP_PL_SHIP_UTIL.Update_Packlist_Sts_Qty (
P_Api_Version_Number => l_api_version_number,
P_Init_Msg_List => FND_API.G_true,
P_Commit => l_commit,
p_validation_level => l_validation_level,
p_organization_id => p_organization_id,
p_packlist_line_id => p_packlist_line_id,
p_line_status => l_packlist_line_status,
p_quantity_packed => NULL,
p_quantity_shipped => NULL,
p_quantity_received => (l_quantity_received + p_quantity_received),
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
SELECT count(packlist_line_status)
INTO l_count
FROM csp_packlist_lines
WHERE packlist_header_id = l_packlist_header_id
AND packlist_line_status = 4;
l_packlist_header_status := fnd_api.g_miss_char; --fnd_api.g_miss_char = not to update the status
l_packlist_header_status := fnd_api.g_miss_char; --fnd_api.g_miss_char = not to update the status
l_packlist_header_status := fnd_api.g_miss_char; --fnd_api.g_miss_char = not to update the status
l_packlist_headers_rec.last_update_date := sysdate;
CSP_Packlist_Headers_PVT.Update_packlist_headers(
P_Api_Version_Number => l_api_version_number,
P_Init_Msg_List => p_init_msg_list,
P_Commit => FND_API.G_FALSE,
p_validation_level => l_validation_level,
P_Identity_Salesforce_Id => NULL,
P_PLH_Rec => l_packlist_headers_rec,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data);
SELECT
TRANSACTION_HEADER_ID ,
TRANSACTION_TEMP_ID ,
SOURCE_CODE ,
SOURCE_LINE_ID ,
TRANSACTION_MODE ,
LOCK_FLAG ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
INVENTORY_ITEM_ID ,
REVISION ,
ORGANIZATION_ID ,
SUBINVENTORY_CODE ,
LOCATOR_ID ,
TRANSACTION_QUANTITY ,
PRIMARY_QUANTITY ,
TRANSACTION_UOM ,
TRANSACTION_COST ,
TRANSACTION_TYPE_ID ,
TRANSACTION_ACTION_ID ,
TRANSACTION_SOURCE_TYPE_ID ,
TRANSACTION_SOURCE_ID ,
TRANSACTION_SOURCE_NAME ,
TRANSACTION_DATE ,
ACCT_PERIOD_ID ,
DISTRIBUTION_ACCOUNT_ID ,
TRANSACTION_REFERENCE ,
REQUISITION_LINE_ID ,
REQUISITION_DISTRIBUTION_ID ,
REASON_ID ,
LOT_NUMBER ,
LOT_EXPIRATION_DATE ,
SERIAL_NUMBER ,
RECEIVING_DOCUMENT ,
DEMAND_ID ,
RCV_TRANSACTION_ID ,
MOVE_TRANSACTION_ID ,
COMPLETION_TRANSACTION_ID ,
WIP_ENTITY_TYPE ,
SCHEDULE_ID ,
REPETITIVE_LINE_ID ,
EMPLOYEE_CODE ,
PRIMARY_SWITCH ,
SCHEDULE_UPDATE_CODE ,
SETUP_TEARDOWN_CODE ,
ITEM_ORDERING ,
NEGATIVE_REQ_FLAG ,
OPERATION_SEQ_NUM ,
PICKING_LINE_ID ,
TRX_SOURCE_LINE_ID ,
TRX_SOURCE_DELIVERY_ID ,
PHYSICAL_ADJUSTMENT_ID ,
CYCLE_COUNT_ID ,
RMA_LINE_ID ,
CUSTOMER_SHIP_ID ,
CURRENCY_CODE ,
CURRENCY_CONVERSION_RATE ,
CURRENCY_CONVERSION_TYPE ,
CURRENCY_CONVERSION_DATE ,
USSGL_TRANSACTION_CODE ,
VENDOR_LOT_NUMBER ,
ENCUMBRANCE_ACCOUNT ,
ENCUMBRANCE_AMOUNT ,
SHIP_TO_LOCATION ,
SHIPMENT_NUMBER ,
TRANSFER_COST ,
TRANSPORTATION_COST ,
TRANSPORTATION_ACCOUNT ,
FREIGHT_CODE ,
CONTAINERS ,
WAYBILL_AIRBILL ,
EXPECTED_ARRIVAL_DATE ,
TRANSFER_SUBINVENTORY ,
TRANSFER_ORGANIZATION ,
TRANSFER_TO_LOCATION ,
NEW_AVERAGE_COST ,
VALUE_CHANGE ,
PERCENTAGE_CHANGE ,
MATERIAL_ALLOCATION_TEMP_ID ,
DEMAND_SOURCE_HEADER_ID ,
DEMAND_SOURCE_LINE ,
DEMAND_SOURCE_DELIVERY ,
ITEM_SEGMENTS ,
ITEM_DESCRIPTION ,
ITEM_TRX_ENABLED_FLAG ,
ITEM_LOCATION_CONTROL_CODE ,
ITEM_RESTRICT_SUBINV_CODE ,
ITEM_RESTRICT_LOCATORS_CODE ,
ITEM_REVISION_QTY_CONTROL_CODE ,
ITEM_PRIMARY_UOM_CODE ,
ITEM_UOM_CLASS ,
ITEM_SHELF_LIFE_CODE ,
ITEM_SHELF_LIFE_DAYS ,
ITEM_LOT_CONTROL_CODE ,
ITEM_SERIAL_CONTROL_CODE ,
ITEM_INVENTORY_ASSET_FLAG ,
ALLOWED_UNITS_LOOKUP_CODE ,
DEPARTMENT_ID ,
DEPARTMENT_CODE ,
WIP_SUPPLY_TYPE ,
SUPPLY_SUBINVENTORY ,
SUPPLY_LOCATOR_ID ,
VALID_SUBINVENTORY_FLAG ,
VALID_LOCATOR_FLAG ,
LOCATOR_SEGMENTS ,
CURRENT_LOCATOR_CONTROL_CODE ,
NUMBER_OF_LOTS_ENTERED ,
WIP_COMMIT_FLAG ,
NEXT_LOT_NUMBER ,
LOT_ALPHA_PREFIX ,
NEXT_SERIAL_NUMBER ,
SERIAL_ALPHA_PREFIX ,
SHIPPABLE_FLAG ,
POSTING_FLAG ,
REQUIRED_FLAG ,
PROCESS_FLAG ,
ERROR_CODE ,
ERROR_EXPLANATION ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
MOVEMENT_ID ,
RESERVATION_QUANTITY ,
SHIPPED_QUANTITY ,
TRANSACTION_LINE_NUMBER ,
TASK_ID ,
TO_TASK_ID ,
SOURCE_TASK_ID ,
PROJECT_ID ,
SOURCE_PROJECT_ID ,
PA_EXPENDITURE_ORG_ID ,
TO_PROJECT_ID ,
EXPENDITURE_TYPE ,
FINAL_COMPLETION_FLAG ,
TRANSFER_PERCENTAGE ,
TRANSACTION_SEQUENCE_ID ,
MATERIAL_ACCOUNT ,
MATERIAL_OVERHEAD_ACCOUNT ,
RESOURCE_ACCOUNT ,
OUTSIDE_PROCESSING_ACCOUNT ,
OVERHEAD_ACCOUNT ,
FLOW_SCHEDULE ,
COST_GROUP_ID ,
DEMAND_CLASS ,
QA_COLLECTION_ID ,
KANBAN_CARD_ID ,
OVERCOMPLETION_TRANSACTION_ID ,
OVERCOMPLETION_PRIMARY_QTY ,
OVERCOMPLETION_TRANSACTION_QTY ,
--PROCESS_TYPE , --removed 01/13/00. process_type does not exist in the mmtt table.
END_ITEM_UNIT_NUMBER ,
SCHEDULED_PAYBACK_DATE ,
LINE_TYPE_CODE ,
PARENT_TRANSACTION_TEMP_ID ,
PUT_AWAY_STRATEGY_ID ,
PUT_AWAY_RULE_ID ,
PICK_STRATEGY_ID ,
PICK_RULE_ID ,
COMMON_BOM_SEQ_ID ,
COMMON_ROUTING_SEQ_ID ,
COST_TYPE_ID ,
ORG_COST_GROUP_ID ,
MOVE_ORDER_LINE_ID ,
TASK_GROUP_ID ,
PICK_SLIP_NUMBER ,
RESERVATION_ID ,
TRANSACTION_STATUS ,
STANDARD_OPERATION_ID ,
TASK_PRIORITY ,
-- ADDED by phegde 02/23
WMS_TASK_TYPE ,
PARENT_LINE_ID
-- SOURCE_LOT_NUMBER
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
delete from mtl_transaction_lots_temp
where transaction_temp_id = l_temp_id_to_be_processed;
delete from mtl_material_transactions_temp
where transaction_temp_id = l_temp_id_to_be_processed;
select serial_transaction_temp_id from mtl_transaction_lots_temp
where transaction_temp_id = l_temp_id_to_be_processed;
delete from mtl_serial_numbers_temp
where transaction_temp_id = l_Serial_Temp_Id_Del;
delete from mtl_transaction_lots_temp
where transaction_temp_id = l_temp_id_to_be_processed;
delete from mtl_material_transactions_temp
where transaction_temp_id = l_temp_id_to_be_processed;
delete from mtl_serial_numbers_temp
where transaction_temp_id = l_temp_id_to_be_processed;
delete from mtl_material_transactions_temp
where transaction_temp_id = l_temp_id_to_be_processed;
delete from mtl_material_transactions_temp
where transaction_temp_id = l_temp_id_to_be_processed;
SELECT transaction_source_id, trx_source_line_id, organization_id
FROM mtl_transactions_interface
WHERE transaction_header_id = p_transaction_header_id;
update mtl_material_transactions
set move_order_line_id = l_Interface_Rec.trx_source_line_id
where transaction_source_id = l_Interface_Rec.transaction_source_id
and trx_source_line_id = l_Interface_Rec.trx_source_line_id
and organization_id = l_Interface_Rec.organization_id;
SELECT mtl_material_transactions_s.nextval
FROM dual;
SELECT
TRANSACTION_HEADER_ID ,
TRANSACTION_TEMP_ID ,
SOURCE_CODE ,
SOURCE_LINE_ID ,
TRANSACTION_MODE ,
LOCK_FLAG ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
INVENTORY_ITEM_ID ,
REVISION ,
ORGANIZATION_ID ,
SUBINVENTORY_CODE ,
LOCATOR_ID ,
TRANSACTION_QUANTITY ,
PRIMARY_QUANTITY ,
TRANSACTION_UOM ,
TRANSACTION_COST ,
TRANSACTION_TYPE_ID ,
TRANSACTION_ACTION_ID ,
TRANSACTION_SOURCE_TYPE_ID ,
TRANSACTION_SOURCE_ID ,
TRANSACTION_SOURCE_NAME ,
TRANSACTION_DATE ,
ACCT_PERIOD_ID ,
DISTRIBUTION_ACCOUNT_ID ,
TRANSACTION_REFERENCE ,
REQUISITION_LINE_ID ,
REQUISITION_DISTRIBUTION_ID ,
REASON_ID ,
LOT_NUMBER ,
LOT_EXPIRATION_DATE ,
SERIAL_NUMBER ,
RECEIVING_DOCUMENT ,
DEMAND_ID ,
RCV_TRANSACTION_ID ,
MOVE_TRANSACTION_ID ,
COMPLETION_TRANSACTION_ID ,
WIP_ENTITY_TYPE ,
SCHEDULE_ID ,
REPETITIVE_LINE_ID ,
EMPLOYEE_CODE ,
PRIMARY_SWITCH ,
SCHEDULE_UPDATE_CODE ,
SETUP_TEARDOWN_CODE ,
ITEM_ORDERING ,
NEGATIVE_REQ_FLAG ,
OPERATION_SEQ_NUM ,
PICKING_LINE_ID ,
TRX_SOURCE_LINE_ID ,
TRX_SOURCE_DELIVERY_ID ,
PHYSICAL_ADJUSTMENT_ID ,
CYCLE_COUNT_ID ,
RMA_LINE_ID ,
CUSTOMER_SHIP_ID ,
CURRENCY_CODE ,
CURRENCY_CONVERSION_RATE ,
CURRENCY_CONVERSION_TYPE ,
CURRENCY_CONVERSION_DATE ,
USSGL_TRANSACTION_CODE ,
VENDOR_LOT_NUMBER ,
ENCUMBRANCE_ACCOUNT ,
ENCUMBRANCE_AMOUNT ,
SHIP_TO_LOCATION ,
SHIPMENT_NUMBER ,
TRANSFER_COST ,
TRANSPORTATION_COST ,
TRANSPORTATION_ACCOUNT ,
FREIGHT_CODE ,
CONTAINERS ,
WAYBILL_AIRBILL ,
EXPECTED_ARRIVAL_DATE ,
TRANSFER_SUBINVENTORY ,
TRANSFER_ORGANIZATION ,
TRANSFER_TO_LOCATION ,
NEW_AVERAGE_COST ,
VALUE_CHANGE ,
PERCENTAGE_CHANGE ,
MATERIAL_ALLOCATION_TEMP_ID ,
DEMAND_SOURCE_HEADER_ID ,
DEMAND_SOURCE_LINE ,
DEMAND_SOURCE_DELIVERY ,
ITEM_SEGMENTS ,
ITEM_DESCRIPTION ,
ITEM_TRX_ENABLED_FLAG ,
ITEM_LOCATION_CONTROL_CODE ,
ITEM_RESTRICT_SUBINV_CODE ,
ITEM_RESTRICT_LOCATORS_CODE ,
ITEM_REVISION_QTY_CONTROL_CODE ,
ITEM_PRIMARY_UOM_CODE ,
ITEM_UOM_CLASS ,
ITEM_SHELF_LIFE_CODE ,
ITEM_SHELF_LIFE_DAYS ,
ITEM_LOT_CONTROL_CODE ,
ITEM_SERIAL_CONTROL_CODE ,
ITEM_INVENTORY_ASSET_FLAG ,
ALLOWED_UNITS_LOOKUP_CODE ,
DEPARTMENT_ID ,
DEPARTMENT_CODE ,
WIP_SUPPLY_TYPE ,
SUPPLY_SUBINVENTORY ,
SUPPLY_LOCATOR_ID ,
VALID_SUBINVENTORY_FLAG ,
VALID_LOCATOR_FLAG ,
LOCATOR_SEGMENTS ,
CURRENT_LOCATOR_CONTROL_CODE ,
NUMBER_OF_LOTS_ENTERED ,
WIP_COMMIT_FLAG ,
NEXT_LOT_NUMBER ,
LOT_ALPHA_PREFIX ,
NEXT_SERIAL_NUMBER ,
SERIAL_ALPHA_PREFIX ,
SHIPPABLE_FLAG ,
POSTING_FLAG ,
REQUIRED_FLAG ,
PROCESS_FLAG ,
ERROR_CODE ,
ERROR_EXPLANATION ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
MOVEMENT_ID ,
RESERVATION_QUANTITY ,
SHIPPED_QUANTITY ,
TRANSACTION_LINE_NUMBER ,
TASK_ID ,
TO_TASK_ID ,
SOURCE_TASK_ID ,
PROJECT_ID ,
SOURCE_PROJECT_ID ,
PA_EXPENDITURE_ORG_ID ,
TO_PROJECT_ID ,
EXPENDITURE_TYPE ,
FINAL_COMPLETION_FLAG ,
TRANSFER_PERCENTAGE ,
TRANSACTION_SEQUENCE_ID ,
MATERIAL_ACCOUNT ,
MATERIAL_OVERHEAD_ACCOUNT ,
RESOURCE_ACCOUNT ,
OUTSIDE_PROCESSING_ACCOUNT ,
OVERHEAD_ACCOUNT ,
FLOW_SCHEDULE ,
COST_GROUP_ID ,
DEMAND_CLASS ,
QA_COLLECTION_ID ,
KANBAN_CARD_ID ,
OVERCOMPLETION_TRANSACTION_ID ,
OVERCOMPLETION_PRIMARY_QTY ,
OVERCOMPLETION_TRANSACTION_QTY ,
-- PROCESS_TYPE , --removed 01/13/00. process_type does not exist in the mmtt table.
END_ITEM_UNIT_NUMBER ,
SCHEDULED_PAYBACK_DATE ,
LINE_TYPE_CODE ,
PARENT_TRANSACTION_TEMP_ID ,
PUT_AWAY_STRATEGY_ID ,
PUT_AWAY_RULE_ID ,
PICK_STRATEGY_ID ,
PICK_RULE_ID ,
COMMON_BOM_SEQ_ID ,
COMMON_ROUTING_SEQ_ID ,
COST_TYPE_ID ,
ORG_COST_GROUP_ID ,
MOVE_ORDER_LINE_ID ,
TASK_GROUP_ID ,
PICK_SLIP_NUMBER ,
RESERVATION_ID ,
TRANSACTION_STATUS ,
STANDARD_OPERATION_ID ,
TASK_PRIORITY ,
-- ADDED by phegde 02/23
WMS_TASK_TYPE ,
PARENT_LINE_ID
-- SOURCE_LOT_NUMBER
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
SELECT * FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id
ORDER BY TRANSACTION_QUANTITY DESC;
/* 06/08/2000 klou: do not need to update the quantity delivered because this will mess up the move in quantity
in the move order status form.
-- Update the quantity_delivered to reflect the over-received quantity.
l_trolin_rec := INV_Trolin_util.Query_Row(l_csp_mtltxn_rec.move_order_line_id );
l_trolin_rec.last_update_date := SYSDATE;
l_trolin_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_trolin_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
INV_Trolin_Util.Update_Row(l_trolin_rec);
CSP_Material_Transactions_PVT.Update_material_transactions(
P_Api_Version_Number => l_api_version_number,
P_Init_Msg_List => FND_API.G_TRUE,
P_Commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
P_CSP_Rec => l_csp_mtltxn_rec,
X_Return_Status => l_return_status ,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data);
Select distribution_account
From mtl_generic_dispositions
Where upper(segment1) = 'CSP_RECEIPT'
And organization_id = p_organization_id
And trunc(nvl(effective_date, sysdate-1)) <= trunc(sysdate)
And trunc(nvl(disable_date, sysdate+1)) >= trunc(sysdate);
SELECT * FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_old_transaction_temp_id
AND lot_number = p_lot_number
ORDER BY TRANSACTION_QUANTITY DESC;
CURSOR l_Get_Serial_Temp_id_Csr IS SELECT MTL_MATERIAL_TRANSACTIONS_S.nextval FROM dual;
SELECT * FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_transaction_temp_id; --p_old_transaction_temp_id;
update mtl_serial_numbers_temp
set to_serial_number = l_prefix||lpad(to_char(l_new_to_num),l_number_length, '0'),
last_update_date = sysdate,
serial_prefix = l_new_to_num - to_number(l_temp_fm_num) + 1
where transaction_temp_id = l_msnt_tbl(l_index).transaction_temp_id
and fm_serial_number = l_msnt_tbl(l_index).fm_serial_number
and to_serial_number = l_msnt_tbl(l_index).to_serial_number;
l_msnt_tbl(l_index).last_update_date := sysdate;
csp_pp_util.insert_msnt(
x_return_status => l_return_status
,p_msnt_tbl => l_msnt_tbl
,p_msnt_tbl_size => 1
);
l_msnt_tbl(l_index).last_update_date := sysdate;
csp_pp_util.insert_msnt(
x_return_status => l_return_status
,p_msnt_tbl => l_msnt_tbl
,p_msnt_tbl_size => 1
);
update mtl_serial_numbers_temp
set fm_serial_number = l_prefix||lpad(to_char(l_new_fm_num),l_number_length, '0'),
last_update_date = sysdate,
serial_prefix = to_number(l_temp_to_num) - l_new_fm_num +1
where transaction_temp_id = l_msnt_tbl(l_index).transaction_temp_id
and fm_serial_number = l_msnt_tbl(l_index).fm_serial_number
and to_serial_number = l_msnt_tbl(l_index).to_serial_number;
l_msnt_tbl(l_index).last_update_date := sysdate;
csp_pp_util.insert_msnt(
x_return_status => l_return_status
,p_msnt_tbl => l_msnt_tbl
,p_msnt_tbl_size => 1
);
update mtl_serial_numbers_temp
set to_serial_number = l_prefix||lpad(to_char(l_new_to_num),l_number_length, '0'),
last_update_date = sysdate,
serial_prefix = l_new_to_num - to_number(l_temp_fm_num) + 1
where transaction_temp_id = l_msnt_tbl(l_index).transaction_temp_id
and fm_serial_number = l_msnt_tbl(l_index).fm_serial_number
and to_serial_number = l_msnt_tbl(l_index).to_serial_number;
l_msnt_tbl(l_index).last_update_date := sysdate;
csp_pp_util.insert_msnt(
x_return_status => l_return_status
,p_msnt_tbl => l_msnt_tbl
,p_msnt_tbl_size => 1
);
update mtl_serial_numbers_temp
set transaction_temp_id = l_new_temp_id
where transaction_temp_id = l_msnt_tbl(l_index).transaction_temp_id
and fm_serial_number = l_msnt_tbl(l_index).fm_serial_number
and to_serial_number = l_msnt_tbl(l_index).to_serial_number;
update mtl_serial_numbers_temp
set transaction_temp_id = l_new_temp_id
where transaction_temp_id = l_msnt_tbl(l_index).transaction_temp_id
and fm_serial_number = l_msnt_tbl(l_index).fm_serial_number
and to_serial_number = l_msnt_tbl(l_index).to_serial_number;
SELECT * FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_old_transaction_temp_id
AND lot_number = p_lot_number;
update mtl_transaction_lots_temp
set transaction_quantity = l_quantity_remained,
primary_quantity = l_quantity_remained
where transaction_temp_id = l_mtlt_tbl(l_index).transaction_temp_id
and serial_transaction_temp_id = l_mtlt_tbl(l_index).serial_transaction_temp_id
and lot_number = l_mtlt_tbl(l_index).lot_number;
csp_pp_util.insert_mtlt(
x_return_status => l_return_status
,p_mtlt_tbl => l_mtlt_tbl
,p_mtlt_tbl_size => 1
);
update mtl_transaction_lots_temp
set transaction_quantity = l_quantity_remained,
primary_quantity = l_quantity_remained
where transaction_temp_id = l_mtlt_tbl(l_index).transaction_temp_id
and lot_number = l_mtlt_tbl(l_index).lot_number;
csp_pp_util.insert_mtlt(
x_return_status => l_return_status
,p_mtlt_tbl => l_mtlt_tbl
,p_mtlt_tbl_size => 1
);
update mtl_transaction_lots_temp
set transaction_temp_id = p_new_transaction_temp_id
where transaction_temp_id = l_mtlt_tbl(l_index).transaction_temp_id
and lot_number = l_mtlt_tbl(l_index).lot_number;