[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT segment1
FROM pa_projects p
WHERE p.project_id = x_project_id;
SELECT 'Project is CAPITAL'
FROM pa_projects p,
pa_project_types t
WHERE p.project_id = x_project_id
AND p.project_type = t.project_type
AND t.project_type_class_code = 'CAPITAL';
SELECT 'Asset Ref Exists'
FROM pa_project_assets_all
WHERE project_id = x_project_id
AND pm_asset_reference = l_asset_in_rec.pm_asset_reference;
SELECT 'Asset Name Exists'
FROM pa_project_assets_all
WHERE project_id = x_project_id
AND asset_name = l_asset_in_rec.pa_asset_name;
SELECT 'Asset Number Exists'
FROM pa_project_assets_all
WHERE asset_number = p_asset_number;
SELECT meaning
FROM pa_lookups
WHERE lookup_type = 'PROJECT_ASSET_TYPES'
AND lookup_code = p_project_asset_type;
SELECT 'Asset Location Exists'
FROM fa_locations
WHERE location_id = p_location_id
AND enabled_flag = 'Y';
SELECT 'Person Exists'
FROM per_people_x
WHERE person_id = p_assigned_to_person_id
--CWK changes, added the below condition
AND nvl(current_employee_flag, 'N') = 'Y';
SELECT 'Book Type Code is valid'
FROM fa_book_controls fb,
pa_implementations pi
WHERE fb.set_of_books_id = pi.set_of_books_id
AND fb.book_type_code = p_book_type_code
AND fb.book_class = 'CORPORATE';
SELECT pi.book_type_code
FROM pa_implementations pi
WHERE pi.book_type_code IS NOT NULL;
SELECT 'Asset Category is valid'
FROM fa_categories
WHERE category_id = p_asset_category_id
AND enabled_flag = 'Y';
SELECT 'Category/Books combination is valid'
FROM fa_category_books
WHERE category_id = p_asset_category_id
AND book_type_code = l_asset_in_rec.book_type_code;
SELECT SUBSTR(depreciate_flag,1,1)
FROM fa_category_book_defaults
WHERE category_id = p_asset_category_id
AND book_type_code = l_asset_in_rec.book_type_code
AND NVL(p_date_placed_in_service,NVL(p_estimated_in_service_date,TRUNC(SYSDATE)))
BETWEEN start_dpis AND NVL(end_dpis,NVL(p_date_placed_in_service,NVL(p_estimated_in_service_date,TRUNC(SYSDATE))));
SELECT 'Deprn Expense Acct code combination is valid'
FROM gl_code_combinations gcc,
gl_sets_of_books gsob,
pa_implementations pi
WHERE gcc.code_combination_id = l_asset_in_rec.depreciation_expense_ccid
AND gcc.chart_of_accounts_id = gsob.chart_of_accounts_id
AND gsob.set_of_books_id = pi.set_of_books_id
AND gcc.account_type = 'E';
SELECT 'Asset Key is valid'
FROM fa_asset_keywords
WHERE code_combination_id = p_asset_key_ccid
AND enabled_flag = 'Y';
SELECT 'Tag Number Exists'
FROM fa_additions
WHERE tag_number = p_tag_number;
SELECT 'Tag Number Exists'
FROM pa_project_assets_all
WHERE tag_number = p_tag_number;
SELECT 'Parent Asset Number Exists'
FROM fa_additions
WHERE asset_id = p_parent_asset_id
AND asset_type <> 'GROUP';
SELECT 'Parent Asset Number Exists in Book'
FROM fa_additions fa,
fa_books fb
WHERE fa.asset_id = p_parent_asset_id
AND fa.asset_type <> 'GROUP'
AND fa.asset_id = fb.asset_id
AND fb.book_type_code = l_asset_in_rec.book_type_code
AND fb.date_ineffective IS NULL;
SELECT fa.asset_category_id
FROM fa_books fb,
fa_additions fa
WHERE fa.asset_id = p_ret_target_asset_id
AND fa.asset_type = 'GROUP'
AND fa.asset_id = fb.asset_id
AND fb.book_type_code = l_asset_in_rec.book_type_code
AND fb.date_ineffective IS NULL;
IF pa_security.allow_update (x_project_id => l_project_id ) = 'N' THEN
-- The user does not have update privileges on this project
-- Hence , raise error
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_PROJECT_SECURITY_ENFORCED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'Y'
,p_msg_context => 'GENERAL'
,p_attribute1 => ''
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
SELECT NVL(pt.interface_complete_asset_flag,'N')
INTO v_intf_complete_asset_flag
FROM pa_project_types pt,
pa_projects p
WHERE p.project_type = pt.project_type
AND p.project_id = l_project_id;
SELECT asset_key_flex_structure
INTO structnum
FROM fa_system_controls;
SELECT pa_project_assets_s.NEXTVAL
INTO l_asset_in_rec.pa_project_asset_id
FROM SYS.DUAL;
INSERT INTO pa_project_assets_all(
project_asset_id,
project_id,
asset_number,
asset_name,
asset_description,
pm_product_code,
pm_asset_reference,
location_id,
assigned_to_person_id,
date_placed_in_service,
asset_category_id,
book_type_code,
asset_units,
depreciate_flag,
depreciation_expense_ccid,
amortize_flag,
capitalized_flag,
reverse_flag,
capital_hold_flag,
estimated_in_service_date,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id,
asset_key_ccid,
project_asset_type,
estimated_cost,
estimated_asset_units,
manufacturer_name,
model_number,
tag_number,
serial_number,
ret_target_asset_id,
parent_asset_id
)
VALUES (
l_asset_in_rec.pa_project_asset_id,
l_project_id,
l_asset_in_rec.asset_number,
l_asset_in_rec.pa_asset_name,
RTRIM(SUBSTR(l_asset_in_rec.asset_description,1,80)),
p_pm_product_code,
l_asset_in_rec.pm_asset_reference,
l_asset_in_rec.location_id,
l_asset_in_rec.assigned_to_person_id,
l_asset_in_rec.date_placed_in_service,
l_asset_in_rec.asset_category_id,
l_asset_in_rec.book_type_code,
--l_asset_in_rec.asset_units,
--Adding TRUNC until Oracle Assets allows fractional Units
GREATEST(TRUNC(l_asset_in_rec.asset_units),1),
NVL(l_asset_in_rec.depreciate_flag,'Y'),
l_asset_in_rec.depreciation_expense_ccid,
NVL(l_asset_in_rec.amortize_flag,'N'),
'N', --capitalized_flag
'N', --reverse_flag
'N', --capital_hold_flag
l_asset_in_rec.estimated_in_service_date,
SYSDATE, --last_update_date
FND_GLOBAL.user_id, --last_updated_by
FND_GLOBAL.user_id, --created_by
SYSDATE, --creation_date
FND_GLOBAL.login_id, --last_update_login
l_asset_in_rec.attribute_category,
l_asset_in_rec.attribute1,
l_asset_in_rec.attribute2,
l_asset_in_rec.attribute3,
l_asset_in_rec.attribute4,
l_asset_in_rec.attribute5,
l_asset_in_rec.attribute6,
l_asset_in_rec.attribute7,
l_asset_in_rec.attribute8,
l_asset_in_rec.attribute9,
l_asset_in_rec.attribute10,
l_asset_in_rec.attribute11,
l_asset_in_rec.attribute12,
l_asset_in_rec.attribute13,
l_asset_in_rec.attribute14,
l_asset_in_rec.attribute15,
mo_global.get_current_org_id ,
l_asset_in_rec.asset_key_ccid,
l_asset_in_rec.project_asset_type,
l_asset_in_rec.estimated_cost,
--l_asset_in_rec.estimated_asset_units,
--Adding TRUNC until Oracle Assets allows fractional Units
GREATEST(TRUNC(l_asset_in_rec.estimated_asset_units),1),
l_asset_in_rec.manufacturer_name,
l_asset_in_rec.model_number,
l_asset_in_rec.tag_number,
l_asset_in_rec.serial_number,
l_asset_in_rec.ret_target_asset_id,
l_asset_in_rec.parent_asset_id
);
SELECT segment1
FROM pa_projects p
WHERE p.project_id = x_project_id;
SELECT asset_name
FROM pa_project_assets p
WHERE p.project_asset_id = x_project_asset_id;
SELECT 'Project is CAPITAL'
FROM pa_projects p,
pa_project_types t
WHERE p.project_id = x_project_id
AND p.project_type = t.project_type
AND t.project_type_class_code = 'CAPITAL';
SELECT 'Task Assignments Exist'
FROM pa_project_asset_assignments
WHERE project_id = l_project_id
AND task_id <> 0;
SELECT 'Project Assignments Exist'
FROM pa_project_asset_assignments
WHERE project_id = l_project_id
AND task_id = 0;
SELECT 'Project Specific Assignments Exist'
FROM pa_project_asset_assignments
WHERE project_id = l_project_id
AND task_id = 0
AND project_asset_id <> 0;
SELECT 'Project Common Assignments Exist'
FROM pa_project_asset_assignments
WHERE project_id = l_project_id
AND task_id = 0
AND project_asset_id = 0;
SELECT 'Project Specific Assignments Exist'
FROM pa_project_asset_assignments
WHERE project_id = l_project_id
AND task_id = x_task_id
AND project_asset_id <> 0;
SELECT 'Project Common Assignments Exist'
FROM pa_project_asset_assignments
WHERE project_id = l_project_id
AND task_id = x_task_id
AND project_asset_id = 0;
SELECT top_task_id
FROM pa_tasks
WHERE task_id = x_task_id;
SELECT 'Child Tasks Exist'
FROM pa_tasks
WHERE parent_task_id = x_task_id;
SELECT 'Lowest Task Assignments Exist'
FROM pa_project_asset_assignments p,
pa_tasks t
WHERE p.project_id = l_project_id
AND p.task_id = t.task_id
AND t.top_task_id = x_task_id
AND t.top_task_id <> t.task_id; --We don't care if other assignments exist for the top task itself
SELECT 'Top Task Assignments Exist'
FROM pa_project_asset_assignments p,
pa_tasks t
WHERE p.project_id = l_project_id
AND p.task_id = t.top_task_id
AND t.task_id = x_task_id
AND p.task_id <> x_task_id; --We don't care if other assignments exist for the lowest task itself
SELECT 'Assignment Already Exists'
FROM pa_project_asset_assignments
WHERE project_id = x_project_id
AND task_id = x_task_id
AND project_asset_id = x_project_asset_id;
IF pa_security.allow_update (x_project_id => l_project_id ) = 'N' THEN
-- The user does not have update privileges on this project
-- Hence , raise error
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_PROJECT_SECURITY_ENFORCED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'Y'
,p_msg_context => 'GENERAL'
,p_attribute1 => ''
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
INSERT INTO pa_project_asset_assignments
(project_asset_id,
task_id,
project_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
VALUES
(l_project_asset_id,
l_task_id,
l_project_id,
SYSDATE, --last_update_date
FND_GLOBAL.user_id, --last_updated_by
SYSDATE, --creation_date
FND_GLOBAL.user_id, --created_by
FND_GLOBAL.login_id, --last_update_login
l_attribute_category,
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_attribute11,
l_attribute12,
l_attribute13,
l_attribute14,
l_attribute15
);
PROCEDURE update_project_asset
( p_api_version_number IN NUMBER
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_msg_count OUT NOCOPY NUMBER
,p_msg_data OUT NOCOPY VARCHAR2
,p_return_status OUT NOCOPY VARCHAR2
,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_pm_asset_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_project_asset_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_pa_asset_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_asset_number IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_asset_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_project_asset_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_location_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_assigned_to_person_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_date_placed_in_service IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_asset_category_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_book_type_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_asset_units IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_estimated_asset_units IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_estimated_cost IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_depreciate_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_depreciation_expense_ccid IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_amortize_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_estimated_in_service_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_asset_key_ccid IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_attribute_category IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute1 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute2 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute3 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute4 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute5 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute6 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute7 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute8 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute9 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute10 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute11 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute12 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute13 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute14 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute15 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_parent_asset_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_manufacturer_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_model_number IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_serial_number IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_tag_number IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_ret_target_asset_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_pa_project_id_out OUT NOCOPY NUMBER
,p_pa_project_number_out OUT NOCOPY VARCHAR2
,p_pa_project_asset_id_out OUT NOCOPY NUMBER
,p_pm_asset_reference_out OUT NOCOPY VARCHAR2) IS
--Used to get the project number for AMG messages
CURSOR l_amg_project_csr(x_project_id NUMBER) IS
SELECT segment1
FROM pa_projects p
WHERE p.project_id = x_project_id;
SELECT asset_name
FROM pa_project_assets p
WHERE p.project_asset_id = x_project_asset_id;
SELECT 'Project is CAPITAL'
FROM pa_projects p,
pa_project_types t
WHERE p.project_id = x_project_id
AND p.project_type = t.project_type
AND t.project_type_class_code = 'CAPITAL';
SELECT 'x'
FROM pa_project_assets_all
WHERE project_asset_id = x_project_asset_id
FOR UPDATE NOWAIT;
SELECT *
FROM pa_project_assets_all
WHERE project_id = x_project_id
AND project_asset_id = x_project_asset_id;
SELECT meaning
FROM pa_lookups
WHERE lookup_type = 'PROJECT_ASSET_TYPES'
AND lookup_code = p_project_asset_type;
SELECT 'Asset Ref Exists'
FROM pa_project_assets_all
WHERE project_id = x_project_id
AND pm_asset_reference = p_pm_asset_reference;
SELECT 'Asset Name Exists'
FROM pa_project_assets_all
WHERE project_id = x_project_id
AND asset_name = p_pa_asset_name;
SELECT 'Asset Number Exists'
FROM pa_project_assets_all
WHERE asset_number = p_asset_number;
SELECT 'Tag Number Exists'
FROM fa_additions
WHERE tag_number = p_tag_number;
SELECT 'Tag Number Exists'
FROM pa_project_assets_all
WHERE tag_number = p_tag_number;
SELECT 'Asset Location Exists'
FROM fa_locations
WHERE location_id = p_location_id
AND enabled_flag = 'Y';
SELECT 'Person Exists'
FROM per_people_x
WHERE person_id = p_assigned_to_person_id
--CWK changes, added the below condition
AND nvl(current_employee_flag, 'N') = 'Y';
SELECT 'Book Type Code is valid'
FROM fa_book_controls fb,
pa_implementations pi
WHERE fb.set_of_books_id = pi.set_of_books_id
AND fb.book_type_code = p_book_type_code
AND fb.book_class = 'CORPORATE';
SELECT pi.book_type_code
FROM pa_implementations pi
WHERE pi.book_type_code IS NOT NULL;
SELECT 'Asset Category is valid'
FROM fa_categories
WHERE category_id = p_asset_category_id
AND enabled_flag = 'Y';
SELECT 'Parent Asset Number Exists'
FROM fa_additions
WHERE asset_id = p_parent_asset_id
AND asset_type <> 'GROUP';
SELECT 'Parent Asset Number Exists in Book'
FROM fa_additions fa,
fa_books fb
WHERE fa.asset_id = x_parent_asset_id
AND fa.asset_type <> 'GROUP'
AND fa.asset_id = fb.asset_id
AND fb.book_type_code = l_book_type_code
AND fb.date_ineffective IS NULL;
SELECT 'Category/Books combination is valid'
FROM fa_category_books
WHERE category_id = x_asset_category_id
AND book_type_code = l_book_type_code;
SELECT SUBSTR(depreciate_flag,1,1)
FROM fa_category_book_defaults
WHERE category_id = p_asset_category_id
AND book_type_code = l_book_type_code
AND NVL(l_date_placed_in_service,NVL(l_estimated_in_service_date,TRUNC(SYSDATE)))
BETWEEN start_dpis AND NVL(end_dpis,NVL(l_date_placed_in_service,NVL(l_estimated_in_service_date,TRUNC(SYSDATE))));
SELECT 'Deprn Expense Acct code combination is valid'
FROM gl_code_combinations gcc,
gl_sets_of_books gsob,
pa_implementations pi
WHERE gcc.code_combination_id = l_depreciation_expense_ccid
AND gcc.chart_of_accounts_id = gsob.chart_of_accounts_id
AND gsob.set_of_books_id = pi.set_of_books_id
AND gcc.account_type = 'E';
SELECT 'Asset Key is valid'
FROM fa_asset_keywords
WHERE code_combination_id = p_asset_key_ccid
AND enabled_flag = 'Y';
SELECT fa.asset_category_id
FROM fa_books fb,
fa_additions fa
WHERE fa.asset_id = p_ret_target_asset_id
AND fa.asset_type = 'GROUP'
AND fa.asset_id = fb.asset_id
AND fb.book_type_code = l_book_type_code
AND fb.date_ineffective IS NULL;
l_api_name CONSTANT VARCHAR2(30) := 'update_project_asset';
l_update_yes_flag VARCHAR2(1) := 'N';
l_updated_dpis VARCHAR2(1) := 'N';
l_updated_category_id VARCHAR2(1) := 'N';
l_updated_book_type_code VARCHAR2(1) := 'N';
l_updated_proj_asset_type VARCHAR2(1) := 'N';
l_updated_deprn_expense_ccid VARCHAR2(1) := 'N';
l_updated_location_id VARCHAR2(1) := 'N';
l_updated_asset_key_ccid VARCHAR2(1) := 'N';
SAVEPOINT update_project_asset_pub;
l_module_name := 'PA_PM_UPDATE_PROJECT_ASSET';
p_function_name => 'PA_PM_UPDATE_PROJECT_ASSET',
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_function_allowed => l_function_allowed);
IF pa_security.allow_update (x_project_id => l_project_id ) = 'N' THEN
-- The user does not have update privileges on this project
-- Hence , raise error
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_PROJECT_SECURITY_ENFORCED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'Y'
,p_msg_context => 'GENERAL'
,p_attribute1 => ''
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
l_update_yes_flag := 'N';
l_statement := 'UPDATE PA_PROJECT_ASSETS SET ';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
( p_old_message_code => 'PA_CANNOT_UPDATE_RET_AS'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'ASSET'
,p_attribute1 => l_amg_project_number
,p_attribute2 => l_amg_pa_asset_name
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
( p_old_message_code => 'PA_CANNOT_UPDATE_RET_AS'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'ASSET'
,p_attribute1 => l_amg_project_number
,p_attribute2 => l_amg_pa_asset_name
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
l_update_yes_flag := 'Y';
l_updated_dpis := 'Y';
l_update_yes_flag := 'Y';
l_updated_dpis := 'Y';
l_update_yes_flag := 'Y';
l_updated_proj_asset_type := 'Y';
IF l_updated_dpis = 'N' --DPIS may have already been processed in the previous validations
AND (p_date_placed_in_service <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE OR p_date_placed_in_service IS NULL)
AND nvl(p_date_placed_in_service,PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE) <>
nvl(l_asset_rec.date_placed_in_service,PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE ) THEN
l_statement := l_statement ||
' DATE_PLACED_IN_SERVICE = :b_date_placed_in_service'||',';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_updated_location_id := 'Y';
l_update_yes_flag := 'Y';
l_updated_location_id := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_updated_book_type_code := 'Y';
l_update_yes_flag := 'Y';
l_updated_book_type_code := 'Y';
IF l_updated_book_type_code = 'Y' AND l_book_type_code IS NOT NULL
AND p_parent_asset_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
AND l_asset_rec.parent_asset_id IS NOT NULL THEN
OPEN parent_asset_book_cur(l_asset_rec.parent_asset_id);
IF l_updated_book_type_code = 'Y' AND l_book_type_code IS NOT NULL
AND p_asset_category_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
AND l_asset_rec.asset_category_id IS NOT NULL THEN
OPEN category_books_cur(l_asset_rec.asset_category_id);
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_updated_category_id := 'Y';
l_update_yes_flag := 'Y';
l_updated_category_id := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
IF ((l_updated_proj_asset_type = 'Y' AND p_project_asset_type = 'AS-BUILT')
OR (l_updated_proj_asset_type = 'N' AND l_asset_rec.project_asset_type = 'AS-BUILT')) THEN
--Determine parameters for client extension call
IF l_updated_dpis = 'Y' THEN
l_dpis := p_date_placed_in_service;
IF l_updated_category_id = 'Y' THEN
l_asset_category_id := p_asset_category_id;
END IF; --Override value must be used during update
l_update_yes_flag := 'Y';
l_updated_deprn_expense_ccid := 'Y';
l_update_yes_flag := 'Y';
l_updated_deprn_expense_ccid := 'Y';
l_update_yes_flag := 'Y';
l_updated_asset_key_ccid := 'Y';
l_update_yes_flag := 'Y';
l_updated_asset_key_ccid := 'Y';
IF l_updated_category_id = 'N'
AND (p_asset_category_id IS NULL OR p_asset_category_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
l_statement := l_statement ||
' ASSET_CATEGORY_ID = :b_asset_category_id'||',';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
IF l_update_yes_flag = 'Y' AND l_asset_rec.project_asset_type IN ('AS-BUILT','RETIREMENT_ADJUSTMENT')
AND l_asset_rec.capitalized_flag = 'Y' THEN
-- No asset attributes may be updated after capitalization. Raise error
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_CANNOT_UPDATE_ASSET_AS'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'ASSET'
,p_attribute1 => l_amg_project_number
,p_attribute2 => l_amg_pa_asset_name
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
SELECT NVL(pt.interface_complete_asset_flag,'N')
INTO v_intf_complete_asset_flag
FROM pa_project_types pt,
pa_projects p
WHERE p.project_type = pt.project_type
AND p.project_id = l_project_id;
((l_updated_proj_asset_type = 'Y' AND p_project_asset_type = 'AS-BUILT')
OR (l_updated_proj_asset_type = 'N' AND l_asset_rec.project_asset_type = 'AS-BUILT')) THEN
IF (l_updated_category_id = 'Y' AND (p_asset_category_id IS NULL OR p_asset_category_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM))
OR (l_updated_category_id = 'N' AND l_asset_rec.asset_category_id IS NULL) THEN
-- The Asset Category is required for 'AS-BUILT' assets. Raise error
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_CATEGORY_MISSING_AS'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'ASSET'
,p_attribute1 => l_amg_project_number
,p_attribute2 => l_amg_pa_asset_name
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
IF (l_updated_location_id = 'Y' AND (p_location_id IS NULL OR p_location_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM))
OR (l_updated_location_id = 'N' AND l_asset_rec.location_id IS NULL) THEN
-- The Asset Location is required for 'AS-BUILT' assets. Raise error
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_ASSET_LOC_MISSING_AS'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'ASSET'
,p_attribute1 => l_amg_project_number
,p_attribute2 => l_amg_pa_asset_name
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
IF (l_updated_deprn_expense_ccid = 'Y' AND (l_depreciation_expense_ccid IS NULL OR l_depreciation_expense_ccid = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM))
OR (l_updated_deprn_expense_ccid = 'N' AND l_asset_rec.depreciation_expense_ccid IS NULL) THEN
-- The Depreciation Expense CCID is required for 'AS-BUILT' assets. Raise error
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_DEPRN_EXP_MISSING_AS'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'ASSET'
,p_attribute1 => l_amg_project_number
,p_attribute2 => l_amg_pa_asset_name
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
IF (l_updated_asset_key_ccid = 'Y' AND (p_asset_key_ccid IS NULL OR p_asset_key_ccid = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM))
OR (l_updated_asset_key_ccid = 'N' AND l_asset_rec.asset_key_ccid IS NULL) THEN
--Asset Key CCID must be specified if any of the segments are specified
BEGIN
SELECT asset_key_flex_structure
INTO structnum
FROM fa_system_controls;
IF l_update_yes_flag = 'Y' THEN
l_statement := l_statement ||
' LAST_UPDATE_DATE = SYSDATE'||',';
' LAST_UPDATED_BY = '||FND_GLOBAL.USER_ID||',';
' LAST_UPDATE_LOGIN = '||FND_GLOBAL.LOGIN_ID;
END IF; --update flag = yes
ROLLBACK TO update_project_asset_pub;
ROLLBACK TO update_project_asset_pub;
ROLLBACK TO update_project_asset_pub;
ROLLBACK TO update_project_asset_pub;
END update_project_asset;
SELECT 'X'
FROM pa_projects
where project_id = p_pa_project_id;
SELECT 'X'
FROM pa_project_assets
WHERE project_asset_id = p_pa_project_asset_id
AND project_id = p_pa_project_id;
SELECT project_asset_id
FROM pa_project_assets_all
WHERE project_id = p_pa_project_id
AND pm_asset_reference = p_pm_asset_reference;
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT project_asset_id
FROM pa_project_assets
WHERE project_id = p_pa_project_id
AND pm_asset_reference = p_pm_asset_reference;
SELECT segment1
FROM pa_projects p
WHERE p.project_id = x_project_id;
SELECT asset_name
FROM pa_project_assets p
WHERE p.project_asset_id = x_project_asset_id
AND p.project_id = x_project_id;
SELECT task_number
FROM pa_tasks
WHERE project_id = l_project_id
AND task_id = l_task_id;
update_project_asset
(p_api_version_number => p_api_version_number
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_msg_count => p_msg_count
,p_msg_data => l_msg_data
,p_return_status => l_return_status
,p_pm_product_code => p_pm_product_code
,p_pm_project_reference => p_pm_project_reference
,p_pa_project_id => l_project_id
,p_pm_asset_reference => p_assets_in(i).pm_asset_reference
,p_pa_project_asset_id => p_assets_in(i).pa_project_asset_id
,p_pa_asset_name => p_assets_in(i).pa_asset_name
,p_asset_number => p_assets_in(i).asset_number
,p_asset_description => p_assets_in(i).asset_description
,p_project_asset_type => p_assets_in(i).project_asset_type
,p_location_id => p_assets_in(i).location_id
,p_assigned_to_person_id => p_assets_in(i).assigned_to_person_id
,p_date_placed_in_service => p_assets_in(i).date_placed_in_service
,p_asset_category_id => p_assets_in(i).asset_category_id
,p_book_type_code => p_assets_in(i).book_type_code
,p_asset_units => p_assets_in(i).asset_units
,p_estimated_asset_units => p_assets_in(i).estimated_asset_units
,p_estimated_cost => p_assets_in(i).estimated_cost
,p_depreciate_flag => p_assets_in(i).depreciate_flag
,p_depreciation_expense_ccid => p_assets_in(i).depreciation_expense_ccid
,p_amortize_flag => p_assets_in(i).amortize_flag
,p_estimated_in_service_date => p_assets_in(i).estimated_in_service_date
,p_asset_key_ccid => p_assets_in(i).asset_key_ccid
,p_attribute_category => p_assets_in(i).attribute_category
,p_attribute1 => p_assets_in(i).attribute1
,p_attribute2 => p_assets_in(i).attribute2
,p_attribute3 => p_assets_in(i).attribute3
,p_attribute4 => p_assets_in(i).attribute4
,p_attribute5 => p_assets_in(i).attribute5
,p_attribute6 => p_assets_in(i).attribute6
,p_attribute7 => p_assets_in(i).attribute7
,p_attribute8 => p_assets_in(i).attribute8
,p_attribute9 => p_assets_in(i).attribute9
,p_attribute10 => p_assets_in(i).attribute10
,p_attribute11 => p_assets_in(i).attribute11
,p_attribute12 => p_assets_in(i).attribute12
,p_attribute13 => p_assets_in(i).attribute13
,p_attribute14 => p_assets_in(i).attribute14
,p_attribute15 => p_assets_in(i).attribute15
,p_parent_asset_id => p_assets_in(i).parent_asset_id
,p_manufacturer_name => p_assets_in(i).manufacturer_name
,p_model_number => p_assets_in(i).model_number
,p_serial_number => p_assets_in(i).serial_number
,p_tag_number => p_assets_in(i).tag_number
,p_ret_target_asset_id => p_assets_in(i).ret_target_asset_id
,p_pa_project_id_out => l_project_id
,p_pa_project_number_out => l_project_number
,p_pa_project_asset_id_out => l_project_asset_id
,p_pm_asset_reference_out => l_pm_asset_reference );
SELECT COUNT(*)
INTO v_assignment_count
FROM pa_project_asset_assignments
WHERE project_id = l_project_id
AND task_id = l_task_id
AND project_asset_id = l_project_asset_id;
PROCEDURE delete_project_asset
( p_api_version_number IN NUMBER
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_msg_count OUT NOCOPY NUMBER
,p_msg_data OUT NOCOPY VARCHAR2
,p_return_status OUT NOCOPY VARCHAR2
,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_pm_asset_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_project_asset_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ) IS
--Used to get the project number for AMG messages
CURSOR l_amg_project_csr(x_project_id NUMBER) IS
SELECT segment1
FROM pa_projects p
WHERE p.project_id = x_project_id;
SELECT asset_name
FROM pa_project_assets p
WHERE p.project_asset_id = x_project_asset_id;
SELECT 'Project is CAPITAL'
FROM pa_projects p,
pa_project_types t
WHERE p.project_id = x_project_id
AND p.project_type = t.project_type
AND t.project_type_class_code = 'CAPITAL';
SELECT 'x'
FROM pa_project_asset_assignments
WHERE project_id = x_project_id
AND project_asset_id = x_project_asset_id
FOR UPDATE NOWAIT;
SELECT 'x'
FROM pa_project_assets
WHERE project_id = x_project_id
AND project_asset_id = x_project_asset_id
FOR UPDATE NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'delete_project_asset';
v_asset_can_be_deleted NUMBER := 0;
SAVEPOINT delete_project_asset_pub;
l_module_name := 'PA_PM_DELETE_PROJECT_ASSET';
p_function_name => 'PA_PM_DELETE_PROJECT_ASSET',
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_function_allowed => l_function_allowed);
IF pa_security.allow_update (x_project_id => l_project_id ) = 'N' THEN
-- The user does not have update privileges on this project
-- Hence , raise error
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_PROJECT_SECURITY_ENFORCED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'Y'
,p_msg_context => 'GENERAL'
,p_attribute1 => ''
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
(p_old_message_code => 'PA_DELETE_ASSET_FAILED_AS'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'ASSET'
,p_attribute1 => l_amg_project_number
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
v_asset_can_be_deleted := PA_ASSET_UTILS.CHECK_ASSET_REFERENCES(l_project_asset_id);
IF v_asset_can_be_deleted = 0 THEN
--Cannot delete asset, since asset lines exist
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
pa_interface_utils_pub.map_new_amg_msg
(p_old_message_code => 'PA_ASSET_CANNOT_DELETE_AS'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'ASSET'
,p_attribute1 => l_amg_project_number
,p_attribute2 => l_amg_pa_asset_name
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
ELSE --Delete asset and assignments
--Delete all associated asset assignments prior to deleting the project asset
OPEN lock_assignment_cur(l_project_id, l_project_asset_id);
DELETE pa_project_asset_assignments
WHERE project_id = l_project_id
AND project_asset_id = l_project_asset_id;
DELETE pa_project_assets
WHERE project_id = l_project_id
AND project_asset_id = l_project_asset_id;
ROLLBACK TO delete_project_asset_pub;
ROLLBACK TO delete_project_asset_pub;
ROLLBACK TO delete_project_asset_pub;
END delete_project_asset;
PROCEDURE delete_asset_assignment
( p_api_version_number IN NUMBER
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_msg_count OUT NOCOPY NUMBER
,p_msg_data OUT NOCOPY VARCHAR2
,p_return_status OUT NOCOPY VARCHAR2
,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_pm_task_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_pm_asset_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_project_asset_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ) IS
--Used to get the project number for AMG messages
CURSOR l_amg_project_csr(x_project_id NUMBER) IS
SELECT segment1
FROM pa_projects p
WHERE p.project_id = x_project_id;
SELECT 'Project is CAPITAL'
FROM pa_projects p,
pa_project_types t
WHERE p.project_id = x_project_id
AND p.project_type = t.project_type
AND t.project_type_class_code = 'CAPITAL';
SELECT 'Assignment Already Exists'
FROM pa_project_asset_assignments
WHERE project_id = x_project_id
AND task_id = x_task_id
AND project_asset_id = x_project_asset_id;
SELECT 'x'
FROM pa_project_asset_assignments
WHERE project_id = x_project_id
AND task_id = x_task_id
AND project_asset_id = x_project_asset_id
FOR UPDATE NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'delete_asset_assignment';
SAVEPOINT delete_asset_assignment_pub;
l_module_name := 'PA_PM_DELETE_ASSET_ASSIGNMENT';
p_function_name => 'PA_PM_DELETE_ASSET_ASSIGNMENT',
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_function_allowed => l_function_allowed);
IF pa_security.allow_update (x_project_id => l_project_id ) = 'N' THEN
-- The user does not have update privileges on this project
-- Hence , raise error
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_PROJECT_SECURITY_ENFORCED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'Y'
,p_msg_context => 'GENERAL'
,p_attribute1 => ''
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
(p_old_message_code => 'PA_DELETE_AS_ASSIGN_FAILED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'PROJ'
,p_attribute1 => l_amg_project_number
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
DELETE pa_project_asset_assignments
WHERE project_id = l_project_id
AND task_id = l_task_id
AND project_asset_id = l_project_asset_id;
ROLLBACK TO delete_asset_assignment_pub;
ROLLBACK TO delete_asset_assignment_pub;
ROLLBACK TO delete_asset_assignment_pub;
END delete_asset_assignment;