[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,p_action_code IN NUMBER -- 0 = insert, 1 = update, 2 = delete
,px_CSP_INV_LOC_ASSIGNMENT_ID IN OUT NOCOPY NUMBER
,p_CREATED_BY IN NUMBER
,p_CREATION_DATE IN DATE
,p_LAST_UPDATED_BY IN NUMBER
,p_LAST_UPDATE_DATE IN DATE
,p_LAST_UPDATE_LOGIN IN NUMBER
,p_RESOURCE_ID IN NUMBER
,p_ORGANIZATION_ID IN NUMBER
,p_SUBINVENTORY_CODE IN VARCHAR2
,p_LOCATOR_ID IN NUMBER
,p_RESOURCE_TYPE IN VARCHAR2
,p_EFFECTIVE_DATE_START IN DATE
,p_EFFECTIVE_DATE_END IN DATE
,p_DEFAULT_CODE IN VARCHAR2
,p_ATTRIBUTE_CATEGORY IN VARCHAR2 := NULL
,p_ATTRIBUTE1 IN VARCHAR2 := NULL
,p_ATTRIBUTE2 IN VARCHAR2 := NULL
,p_ATTRIBUTE3 IN VARCHAR2 := NULL
,p_ATTRIBUTE4 IN VARCHAR2 := NULL
,p_ATTRIBUTE5 IN VARCHAR2 := NULL
,p_ATTRIBUTE6 IN VARCHAR2 := NULL
,p_ATTRIBUTE7 IN VARCHAR2 := NULL
,p_ATTRIBUTE8 IN VARCHAR2 := NULL
,p_ATTRIBUTE9 IN VARCHAR2 := NULL
,p_ATTRIBUTE10 IN VARCHAR2 := NULL
,p_ATTRIBUTE11 IN VARCHAR2 := NULL
,p_ATTRIBUTE12 IN VARCHAR2 := NULL
,p_ATTRIBUTE13 IN VARCHAR2 := NULL
,p_ATTRIBUTE14 IN VARCHAR2 := NULL
,p_ATTRIBUTE15 IN VARCHAR2 := NULL
,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;
select organization_id into l_check_existence
from mtl_parameters
where organization_id = p_organization_id;
SELECT SECONDARY_INVENTORY_ID INTO l_check_existence
FROM csp_sec_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory_code;
SELECT inventory_location_id INTO l_check_existence
FROM mtl_item_locations
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND inventory_location_id = p_locator_id;
select csf_util_pvt.get_object_name(p_resource_type, p_resource_id) into l_resource_name
from dual;
SELECT condition_type
FROM csp_sec_inventories
WHERE secondary_inventory_name = p_subinventory_code
AND organization_id = p_organization_id;
SELECT csp_inv_loc_assignment_id INTO l_check_existence
FROM csp_inv_loc_assignments
WHERE csp_inv_loc_assignment_id = px_csp_inv_loc_assignment_id;
l_csp_rec.LAST_UPDATED_BY := p_LAST_UPDATED_BY;
l_csp_rec.LAST_UPDATE_DATE := p_LAST_UPDATE_DATE;
l_csp_rec.LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN;
SELECT csp_inv_loc_assignment_id INTO l_check_existence
FROM csp_inv_loc_assignments
WHERE csp_inv_loc_assignment_id = px_csp_inv_loc_assignment_id;
l_csp_rec.LAST_UPDATED_BY := p_LAST_UPDATED_BY;
l_csp_rec.LAST_UPDATE_DATE := p_LAST_UPDATE_DATE;
l_csp_rec.LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN;
l_csp_rec_update CSP_RESOURCE_PVT.CSP_Rec_Type;
SELECT CSP_INV_LOC_ASSIGNMENT_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
RESOURCE_ID ,
ORGANIZATION_ID ,
SUBINVENTORY_CODE ,
LOCATOR_ID ,
RESOURCE_TYPE ,
EFFECTIVE_DATE_START ,
EFFECTIVE_DATE_END ,
DEFAULT_CODE ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15
FROM csp_inv_loc_assignments
WHERE csp_inv_loc_assignment_id = px_csp_inv_loc_assignment_id;
FETCH C_Get_Inv_Loc_Assignments INTO l_csp_rec_update;
SELECT csp_inv_loc_assignment_id into l_result
FROM csp_inv_loc_assignments
WHERE resource_id = p_resource_id
AND resource_type = p_resource_type
AND organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND default_code = p_default_code
AND csp_inv_loc_assignment_id <> px_csp_inv_loc_assignment_id;
SELECT condition_type
FROM csp_sec_inventories
WHERE secondary_inventory_name = decode(p_subinventory_code, null, l_csp_rec_update.subinventory_code, p_subinventory_code)
AND organization_id = decode(p_organization_id, null, l_csp_rec_update.organization_id, p_organization_id);
IF upper(nvl(p_default_code, l_csp_rec_update.default_code)) = 'IN' THEN
IF fnd_api.to_boolean(l_bad_type) AND NOT fnd_api.to_boolean(l_good_type) THEN
l_msg_data := 'Only a Good subinventory is allowed to be assigned as a Part-In subinventory.';
ELSIF upper(nvl(p_default_code, l_csp_rec_update.default_code)) = 'OUT' THEN
IF fnd_api.to_boolean(l_good_type) AND NOT fnd_api.to_boolean(l_bad_type)THEN
l_msg_data := 'Only a Bad subinventory is allowed to be assigned as a Part-Out subinventory.';
SELECT resource_id INTO l_check_existence
FROM jtf_rs_all_resources_vl
WHERE resource_id = decode(p_resource_id, null, l_csp_rec_update.resource_id, p_resource_id)
AND resource_type = decode(p_resource_type, null, l_csp_rec_update.resource_type, p_resource_type);
IF l_csp_rec.last_update_date IS NULL THEN
l_csp_rec.last_update_date := sysdate;
SELECT creation_date INTO l_csp_rec.creation_date
FROM csp_inv_loc_assignments
WHERE csp_inv_loc_assignment_id = l_csp_rec.csp_inv_loc_assignment_id;
CSP_RESOURCE_PVT.Update_resource(
P_Api_Version_Number => p_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_NONE,
P_CSP_Rec => l_csp_rec,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
CSP_RESOURCE_PVT.Delete_resource(
P_Api_Version_Number => p_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_NONE,
P_CSP_Rec => l_csp_rec,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
SELECT csp_inv_loc_assignment_id into l_result
FROM csp_inv_loc_assignments
WHERE resource_id = p_resource_id
AND resource_type = p_resource_type
AND organization_id = p_organization_id
AND subinventory_code = p_subinventory_code;
SELECT csp_inv_loc_assignment_id into l_result
FROM csp_inv_loc_assignments
WHERE resource_id = p_resource_id
AND resource_type = p_resource_type
AND organization_id = p_organization_id
AND subinventory_code = p_subinventory_code;