[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_LAST_UPDATE_DATE IN DATE := FND_API.G_MISS_DATE,
p_LAST_UPDATED_BY IN NUMBER := FND_API.G_MISS_NUM,
p_CREATION_DATE IN DATE := FND_API.G_MISS_DATE,
p_CREATED_BY IN NUMBER := FND_API.G_MISS_NUM,
p_LAST_UPDATE_LOGIN IN NUMBER := FND_API.G_MISS_NUM,
p_REQUEST_ID IN NUMBER := FND_API.G_MISS_NUM,
p_PROGRAM_APPLICATION_ID IN NUMBER := FND_API.G_MISS_NUM,
p_PROGRAM_ID IN NUMBER := FND_API.G_MISS_NUM,
p_PROGRAM_UPDATE_DATE IN DATE := FND_API.G_MISS_DATE,
p_INVENTORY_ITEM_ID IN NUMBER := FND_API.G_MISS_NUM,
p_REVISION IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ORGANIZATION_ID IN NUMBER := FND_API.G_MISS_NUM,
p_SUBINVENTORY_CODE IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_LOCATOR_ID IN NUMBER := FND_API.G_MISS_NUM,
p_TRANSACTION_QUANTITY IN NUMBER := FND_API.G_MISS_NUM,
p_PRIMARY_QUANTITY IN NUMBER := FND_API.G_MISS_NUM,
p_TRANSACTION_UOM IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_TRANSACTION_COST IN NUMBER := FND_API.G_MISS_NUM,
p_TRANSACTION_TYPE_ID IN NUMBER := FND_API.G_MISS_NUM,
p_TRANSACTION_ACTION_ID IN NUMBER := FND_API.G_MISS_NUM,
p_TRANSACTION_SOURCE_TYPE_ID IN NUMBER := FND_API.G_MISS_NUM,
p_TRANSACTION_SOURCE_ID IN NUMBER := FND_API.G_MISS_NUM,
p_TRANSACTION_SOURCE_NAME IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_TRANSACTION_DATE IN DATE := FND_API.G_MISS_DATE,
p_ACCT_PERIOD_ID IN NUMBER := FND_API.G_MISS_NUM,
p_DISTRIBUTION_ACCOUNT_ID IN NUMBER := FND_API.G_MISS_NUM,
p_TRANSACTION_REFERENCE IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_REQUISITION_LINE_ID IN NUMBER := FND_API.G_MISS_NUM,
p_REQUISITION_DISTRIBUTION_ID IN NUMBER := FND_API.G_MISS_NUM,
p_REASON_ID IN NUMBER := FND_API.G_MISS_NUM,
p_LOT_NUMBER IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_LOT_EXPIRATION_DATE IN DATE := FND_API.G_MISS_DATE,
p_SERIAL_NUMBER IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_RECEIVING_DOCUMENT IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_DEMAND_ID IN NUMBER := FND_API.G_MISS_NUM,
p_RCV_TRANSACTION_ID IN NUMBER := FND_API.G_MISS_NUM,
p_MOVE_TRANSACTION_ID IN NUMBER := FND_API.G_MISS_NUM,
p_COMPLETION_TRANSACTION_ID IN NUMBER := FND_API.G_MISS_NUM,
p_WIP_ENTITY_TYPE IN NUMBER := FND_API.G_MISS_NUM,
p_SCHEDULE_ID IN NUMBER := FND_API.G_MISS_NUM,
p_REPETITIVE_LINE_ID IN NUMBER := FND_API.G_MISS_NUM,
p_EMPLOYEE_CODE IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_PRIMARY_SWITCH IN NUMBER := FND_API.G_MISS_NUM,
p_SCHEDULE_UPDATE_CODE IN NUMBER := FND_API.G_MISS_NUM,
p_SETUP_TEARDOWN_CODE IN NUMBER := FND_API.G_MISS_NUM,
p_ITEM_ORDERING IN NUMBER := FND_API.G_MISS_NUM,
p_NEGATIVE_REQ_FLAG IN NUMBER := FND_API.G_MISS_NUM,
p_OPERATION_SEQ_NUM IN NUMBER := FND_API.G_MISS_NUM,
p_PICKING_LINE_ID IN NUMBER := FND_API.G_MISS_NUM,
p_TRX_SOURCE_LINE_ID IN NUMBER := FND_API.G_MISS_NUM,
p_TRX_SOURCE_DELIVERY_ID IN NUMBER := FND_API.G_MISS_NUM,
p_PHYSICAL_ADJUSTMENT_ID IN NUMBER := FND_API.G_MISS_NUM,
p_CYCLE_COUNT_ID IN NUMBER := FND_API.G_MISS_NUM,
p_RMA_LINE_ID IN NUMBER := FND_API.G_MISS_NUM,
p_CUSTOMER_SHIP_ID IN NUMBER := FND_API.G_MISS_NUM,
p_CURRENCY_CODE IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_CURRENCY_CONVERSION_RATE IN NUMBER := FND_API.G_MISS_NUM,
p_CURRENCY_CONVERSION_TYPE IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_CURRENCY_CONVERSION_DATE IN DATE := FND_API.G_MISS_DATE,
p_USSGL_TRANSACTION_CODE IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_VENDOR_LOT_NUMBER IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ENCUMBRANCE_ACCOUNT IN NUMBER := FND_API.G_MISS_NUM,
p_ENCUMBRANCE_AMOUNT IN NUMBER := FND_API.G_MISS_NUM,
p_SHIP_TO_LOCATION IN NUMBER := FND_API.G_MISS_NUM,
p_SHIPMENT_NUMBER IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_TRANSFER_COST IN NUMBER := FND_API.G_MISS_NUM,
p_TRANSPORTATION_COST IN NUMBER := FND_API.G_MISS_NUM,
p_TRANSPORTATION_ACCOUNT IN NUMBER := FND_API.G_MISS_NUM,
p_FREIGHT_CODE IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_CONTAINERS IN NUMBER := FND_API.G_MISS_NUM,
p_WAYBILL_AIRBILL IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_EXPECTED_ARRIVAL_DATE IN DATE := FND_API.G_MISS_DATE,
p_TRANSFER_SUBINVENTORY IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_TRANSFER_ORGANIZATION IN NUMBER := FND_API.G_MISS_NUM,
p_TRANSFER_TO_LOCATION IN NUMBER := FND_API.G_MISS_NUM,
p_NEW_AVERAGE_COST IN NUMBER := FND_API.G_MISS_NUM,
p_VALUE_CHANGE IN NUMBER := FND_API.G_MISS_NUM,
p_PERCENTAGE_CHANGE IN NUMBER := FND_API.G_MISS_NUM,
p_MATERIAL_ALLOCATION_TEMP_ID IN NUMBER := FND_API.G_MISS_NUM,
p_DEMAND_SOURCE_HEADER_ID IN NUMBER := FND_API.G_MISS_NUM,
p_DEMAND_SOURCE_LINE IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_DEMAND_SOURCE_DELIVERY IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ITEM_SEGMENTS IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ITEM_DESCRIPTION IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ITEM_TRX_ENABLED_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ITEM_LOCATION_CONTROL_CODE IN NUMBER := FND_API.G_MISS_NUM,
p_ITEM_RESTRICT_SUBINV_CODE IN NUMBER := FND_API.G_MISS_NUM,
p_ITEM_RESTRICT_LOCATORS_CODE IN NUMBER := FND_API.G_MISS_NUM,
p_ITEM_REV_QTY_CONTROL_CODE IN NUMBER := FND_API.G_MISS_NUM,
p_ITEM_PRIMARY_UOM_CODE IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ITEM_UOM_CLASS IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ITEM_SHELF_LIFE_CODE IN NUMBER := FND_API.G_MISS_NUM,
p_ITEM_SHELF_LIFE_DAYS IN NUMBER := FND_API.G_MISS_NUM,
p_ITEM_LOT_CONTROL_CODE IN NUMBER := FND_API.G_MISS_NUM,
p_ITEM_SERIAL_CONTROL_CODE IN NUMBER := FND_API.G_MISS_NUM,
p_ITEM_INVENTORY_ASSET_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ALLOWED_UNITS_LOOKUP_CODE IN NUMBER := FND_API.G_MISS_NUM,
p_DEPARTMENT_ID IN NUMBER := FND_API.G_MISS_NUM,
p_DEPARTMENT_CODE IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_WIP_SUPPLY_TYPE IN NUMBER := FND_API.G_MISS_NUM,
p_SUPPLY_SUBINVENTORY IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_SUPPLY_LOCATOR_ID IN NUMBER := FND_API.G_MISS_NUM,
p_VALID_SUBINVENTORY_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_VALID_LOCATOR_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_LOCATOR_SEGMENTS IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_CURRENT_LOCATOR_CONTROL_CODE IN NUMBER := FND_API.G_MISS_NUM,
p_NUMBER_OF_LOTS_ENTERED IN NUMBER := FND_API.G_MISS_NUM,
p_WIP_COMMIT_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_NEXT_LOT_NUMBER IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_LOT_ALPHA_PREFIX IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_NEXT_SERIAL_NUMBER IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_SERIAL_ALPHA_PREFIX IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_SHIPPABLE_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_POSTING_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_REQUIRED_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_PROCESS_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ERROR_CODE IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ERROR_EXPLANATION IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ATTRIBUTE_CATEGORY IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ATTRIBUTE1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ATTRIBUTE2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ATTRIBUTE3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ATTRIBUTE4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ATTRIBUTE5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ATTRIBUTE6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ATTRIBUTE7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ATTRIBUTE8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ATTRIBUTE9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ATTRIBUTE10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ATTRIBUTE11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ATTRIBUTE12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ATTRIBUTE13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ATTRIBUTE14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_ATTRIBUTE15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_MOVEMENT_ID IN NUMBER := FND_API.G_MISS_NUM,
p_RESERVATION_QUANTITY IN NUMBER := FND_API.G_MISS_NUM,
p_SHIPPED_QUANTITY IN NUMBER := FND_API.G_MISS_NUM,
p_TRANSACTION_LINE_NUMBER IN NUMBER := FND_API.G_MISS_NUM,
p_TASK_ID IN NUMBER := FND_API.G_MISS_NUM,
p_TO_TASK_ID IN NUMBER := FND_API.G_MISS_NUM,
p_SOURCE_TASK_ID IN NUMBER := FND_API.G_MISS_NUM,
p_PROJECT_ID IN NUMBER := FND_API.G_MISS_NUM,
p_SOURCE_PROJECT_ID IN NUMBER := FND_API.G_MISS_NUM,
p_PA_EXPENDITURE_ORG_ID IN NUMBER := FND_API.G_MISS_NUM,
p_TO_PROJECT_ID IN NUMBER := FND_API.G_MISS_NUM,
p_EXPENDITURE_TYPE IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_FINAL_COMPLETION_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_TRANSFER_PERCENTAGE IN NUMBER := FND_API.G_MISS_NUM,
p_TRANSACTION_SEQUENCE_ID IN NUMBER := FND_API.G_MISS_NUM,
p_MATERIAL_ACCOUNT IN NUMBER := FND_API.G_MISS_NUM,
p_MATERIAL_OVERHEAD_ACCOUNT IN NUMBER := FND_API.G_MISS_NUM,
p_RESOURCE_ACCOUNT IN NUMBER := FND_API.G_MISS_NUM,
p_OUTSIDE_PROCESSING_ACCOUNT IN NUMBER := FND_API.G_MISS_NUM,
p_OVERHEAD_ACCOUNT IN NUMBER := FND_API.G_MISS_NUM,
p_FLOW_SCHEDULE IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_COST_GROUP_ID IN NUMBER := FND_API.G_MISS_NUM,
p_DEMAND_CLASS IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_QA_COLLECTION_ID IN NUMBER := FND_API.G_MISS_NUM,
p_KANBAN_CARD_ID IN NUMBER := FND_API.G_MISS_NUM,
p_OVERCOMPLETION_TXN_ID IN NUMBER := FND_API.G_MISS_NUM,
p_OVERCOMPLETION_PRIMARY_QTY IN NUMBER := FND_API.G_MISS_NUM,
p_OVERCOMPLETION_TXN_QTY IN NUMBER := FND_API.G_MISS_NUM,
--p_PROCESS_TYPE IN NUMBER := FND_API.G_MISS_NUM,
p_END_ITEM_UNIT_NUMBER IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_SCHEDULED_PAYBACK_DATE IN DATE := FND_API.G_MISS_DATE,
p_LINE_TYPE_CODE IN NUMBER := FND_API.G_MISS_NUM,
p_PARENT_TRANSACTION_TEMP_ID IN NUMBER := FND_API.G_MISS_NUM,
p_PUT_AWAY_STRATEGY_ID IN NUMBER := FND_API.G_MISS_NUM,
p_PUT_AWAY_RULE_ID IN NUMBER := FND_API.G_MISS_NUM,
p_PICK_STRATEGY_ID IN NUMBER := FND_API.G_MISS_NUM,
p_PICK_RULE_ID IN NUMBER := FND_API.G_MISS_NUM,
p_COMMON_BOM_SEQ_ID IN NUMBER := FND_API.G_MISS_NUM,
p_COMMON_ROUTING_SEQ_ID IN NUMBER := FND_API.G_MISS_NUM,
p_COST_TYPE_ID IN NUMBER := FND_API.G_MISS_NUM,
p_ORG_COST_GROUP_ID IN NUMBER := FND_API.G_MISS_NUM,
p_MOVE_ORDER_LINE_ID IN NUMBER := FND_API.G_MISS_NUM,
p_TASK_GROUP_ID IN NUMBER := FND_API.G_MISS_NUM,
p_PICK_SLIP_NUMBER IN NUMBER := FND_API.G_MISS_NUM,
p_RESERVATION_ID IN NUMBER := FND_API.G_MISS_NUM,
p_TRANSACTION_STATUS IN NUMBER := FND_API.G_MISS_NUM,
P_STANDARD_OPERATION_ID IN NUMBER := FND_API.G_MISS_NUM,
P_TASK_PRIORITY IN NUMBER := FND_API.G_MISS_NUM,
P_WMS_TASK_TYPE IN NUMBER := FND_API.G_MISS_NUM,
P_PARENT_LINE_ID IN NUMBER := FND_API.G_MISS_NUM,
-- P_SOURCE_LOT_NUMBER IN VARCHAR2 := FND_API.G_MISS_CHAR,
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_last_update_date DATE := p_last_update_date;
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 transaction_temp_id = px_transaction_temp_id
AND organization_id = p_organization_id;
select transaction_temp_id into l_check_existence
from mtl_material_transactions_temp
where organization_id = p_organization_id
and transaction_temp_id = px_transaction_temp_id;
select inventory_item_id into l_check_existence
from mtl_system_items_kfv
where inventory_item_id = p_inventory_item_id
and organization_id = p_organization_id;
IF l_last_update_date IS NULL THEN
l_last_update_date := sysdate;
l_csp_mtltxn_rec.LAST_UPDATE_DATE := p_LAST_UPDATE_DATE;
l_csp_mtltxn_rec.LAST_UPDATED_BY := p_LAST_UPDATED_BY;
l_csp_mtltxn_rec.LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN;
l_csp_mtltxn_rec.PROGRAM_UPDATE_DATE := p_PROGRAM_UPDATE_DATE;
l_csp_mtltxn_rec.SCHEDULE_UPDATE_CODE := p_SCHEDULE_UPDATE_CODE;
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.Delete_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
);
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 = p_transaction_temp_id
AND organization_id = p_organization_id;
SELECT mtl_material_transactions_s.nextval
FROM dual;
select organization_id into l_check_existence
from mtl_parameters
where organization_id = p_organization_id;
select profile_option_id into l_profile_option_id
from fnd_profile_options_vl
where profile_option_name = 'CSP_INTRANSIT_SUBINVENTORY';
select profile_option_value into l_csp_intransit_subinventory
from fnd_profile_option_values
where profile_option_id = l_profile_option_id;
select organization_id into l_check_existence
from mtl_secondary_inventories
where secondary_inventory_name = l_csp_intransit_subinventory
and organization_id = p_organization_id
and nvl(disable_date, sysdate + 1) > sysdate;
l_csp_mtltxn_new_rec.LAST_UPDATE_DATE := sysdate;
select restrict_subinventories_code into l_restrict_sub_code
from mtl_system_items
where inventory_item_id = l_inventory_item_id
and organization_id = l_csp_mtltxn_rec.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 = l_csp_intransit_subinventory;
CSP_ITEM_SUB_INVENTORIES_PKG.Insert_Row(
px_INVENTORY_ITEM_ID => l_inventory_item_id,
p_ORGANIZATION_ID => p_organization_id,
p_SECONDARY_INVENTORY => l_csp_intransit_subinventory,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATED_BY => G_LOGIN_ID,
p_CREATION_DATE => sysdate,
p_CREATED_BY => G_USER_ID ,
p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
p_PRIMARY_SUBINVENTORY_FLAG => NULL,
p_PICKING_ORDER => NULL,
p_MIN_MINMAX_QUANTITY => NULL,
p_MAX_MINMAX_QUANTITY => NULL,
p_INVENTORY_PLANNING_CODE => 6, -- Not Planned
p_FIXED_LOT_MULTIPLE => NULL,
p_MINIMUM_ORDER_QUANTITY => NULL,
p_MAXIMUM_ORDER_QUANTITY => NULL,
p_SOURCE_TYPE => NULL,
p_SOURCE_ORGANIZATION_ID => NULL,
p_SOURCE_SUBINVENTORY => NULL,
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_ENCUMBRANCE_ACCOUNT => NULL,
p_PREPROCESSING_LEAD_TIME => NULL,
p_PROCESSING_LEAD_TIME => NULL,
p_POSTPROCESSING_LEAD_TIME => NULL);
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 => l_validation_level,
P_CSP_Rec => l_csp_mtltxn_new_rec,
-- X_TRANSACTION_TEMP_ID => l_transaction_temp_id,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
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 => 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_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 = p_transaction_temp_id
AND organization_id = p_organization_id;
select organization_id into l_check_existence
from mtl_parameters
where organization_id = p_organization_id;
select move_order_line_id into l_move_order_line_id
from mtl_material_transactions_temp
where transaction_temp_id = p_transaction_temp_id
and organization_id = p_organization_id;
select header_id into l_header_id
from csp_moveorder_lines
where line_id = l_move_order_line_id;
select autoreceipt_flag into l_autoreceipt_flag
from csp_moveorder_headers
where header_id = l_header_id;
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 => 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
);