[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT project_asset_id,
0, --asset_basis_amount
0 --total_basis_amount
FROM pa_project_assets_all
WHERE project_id = p_project_id
AND capital_event_id = p_capital_event_id
AND project_asset_type = DECODE(x_line_type,'C','AS-BUILT','R','RETIREMENT_ADJUSTMENT','AS-BUILT')
AND nvl(asset_category_id, -99) = nvl(p_asset_category_id, nvl(asset_category_id, -99)) /* Bug#3211946 */
AND capital_hold_flag = 'N';
SELECT paa.project_asset_id,
0, --asset_basis_amount
0 --total_basis_amount
FROM pa_project_assets_all pa,
pa_project_asset_assignments paa
WHERE pa.project_asset_id = paa.project_asset_id
AND pa.project_id = p_project_id
AND paa.project_id = p_project_id
AND pa.capital_event_id = p_capital_event_id
AND paa.task_id = 0
AND pa.project_asset_type = DECODE(x_line_type,'C','AS-BUILT','R','RETIREMENT_ADJUSTMENT','AS-BUILT')
AND nvl(pa.asset_category_id, -99) = nvl(p_asset_category_id, nvl(pa.asset_category_id, -99)) /* Bug#3211946 */
AND pa.capital_hold_flag = 'N';
SELECT paa.project_asset_id,
0, --asset_basis_amount
0 --total_basis_amount
FROM pa_project_assets_all pa,
pa_project_asset_assignments paa
WHERE pa.project_asset_id = paa.project_asset_id
AND pa.project_id = p_project_id
AND paa.project_id = p_project_id
AND pa.capital_event_id = p_capital_event_id
AND paa.task_id = x_task_id
AND pa.project_asset_type = DECODE(x_line_type,'C','AS-BUILT','R','RETIREMENT_ADJUSTMENT','AS-BUILT')
AND nvl(pa.asset_category_id, -99) = nvl(p_asset_category_id, nvl(pa.asset_category_id, -99)) /* Bug#3211946 */
AND pa.capital_hold_flag = 'N';
SELECT paa.project_asset_id,
0, --asset_basis_amount
0 --total_basis_amount
FROM pa_project_assets_all pa,
pa_project_asset_assignments paa
WHERE pa.project_asset_id = paa.project_asset_id
AND pa.project_id = p_project_id
AND paa.project_id = p_project_id
AND pa.capital_event_id = p_capital_event_id
AND paa.task_id = v_top_task_id
AND pa.project_asset_type = DECODE(x_line_type,'C','AS-BUILT','R','RETIREMENT_ADJUSTMENT','AS-BUILT')
AND nvl(pa.asset_category_id, -99) = nvl(p_asset_category_id, nvl(pa.asset_category_id, -99)) /* Bug#3211946 */
AND pa.capital_hold_flag = 'N';
SELECT task_id,
task_number
FROM pa_tasks
WHERE task_id <> x_parent_task_id
AND task_id <> p_task_id
CONNECT BY parent_task_id = PRIOR task_id
START WITH task_id = x_parent_task_id;
error_calling_update_cost EXCEPTION;
SELECT DECODE(COUNT(*),0,'N','Y')
INTO v_common_project
FROM pa_project_asset_assignments
WHERE project_id = p_project_id
AND task_id = 0
AND project_asset_id = 0;
SELECT top_task_id,
parent_task_id
INTO v_top_task_id,
v_parent_task_id
FROM pa_tasks
WHERE task_id = p_task_id;
SELECT DECODE(COUNT(*),0,'N','Y')
INTO v_common_task
FROM pa_project_asset_assignments
WHERE project_id = p_project_id
AND task_id = v_top_task_id
AND project_asset_id = 0;
SELECT DECODE(COUNT(*),0,'N','Y')
INTO v_common_lowest_task
FROM pa_project_asset_assignments
WHERE project_id = p_project_id
AND task_id = p_task_id
AND task_id <> v_top_task_id --Only Lowest "Leaf" Tasks that are not also Top Tasks
AND project_asset_id = 0;
SELECT COUNT(*)
INTO v_assignment_count
FROM pa_project_asset_assignments paa
WHERE paa.project_id = p_project_id
AND paa.task_id = 0;
SELECT COUNT(*)
INTO v_assignment_count
FROM pa_project_asset_assignments paa
WHERE paa.project_id = p_project_id
AND paa.task_id = p_task_id;
SELECT COUNT(*)
INTO v_assignment_count
FROM pa_project_asset_assignments paa
WHERE paa.project_id = p_project_id
AND paa.task_id = v_top_task_id;
SELECT COUNT(*)
INTO v_asset_count
FROM pa_project_assets_all pa
WHERE pa.project_id = p_project_id
AND pa.project_asset_id = asset_basis_table(i).PROJECT_ASSET_ID;
SELECT project_asset_type,
date_placed_in_service,
capital_hold_flag
INTO v_project_asset_type,
v_date_placed_in_service,
v_capital_hold_flag
FROM pa_project_assets_all pa
WHERE pa.project_id = p_project_id
AND pa.project_asset_id = asset_basis_table(i).PROJECT_ASSET_ID;
UPDATE pa_project_asset_lines_all
SET project_asset_id = asset_basis_table(i).PROJECT_ASSET_ID,
last_update_date = SYSDATE,
last_updated_by = v_user,
last_update_login = v_login,
request_id = v_request_id,
program_application_id = v_program_application_id,
program_id = v_program_id,
program_update_date = SYSDATE
WHERE project_asset_line_id = p_project_asset_line_id;
SELECT current_asset_cost
INTO v_current_asset_cost
FROM pa_project_asset_lines_all
WHERE project_asset_line_id = p_project_asset_line_id;
PA_FAXFACE.update_asset_cost
(asset_basis_table(i).PROJECT_ASSET_ID,
v_current_asset_cost,
0, --- capitalized_cost
v_err_stage,
v_err_code);
RAISE error_calling_update_cost;
SELECT asset_units
INTO v_asset_basis_amount
FROM pa_project_assets_all
WHERE project_asset_id = asset_basis_table(i).PROJECT_ASSET_ID;
SELECT grouped_cip_cost
INTO v_asset_basis_amount
FROM pa_project_assets_all
WHERE project_asset_id = asset_basis_table(i).PROJECT_ASSET_ID;
SELECT estimated_cost
INTO v_asset_basis_amount
FROM pa_project_assets_all
WHERE project_asset_id = asset_basis_table(i).PROJECT_ASSET_ID;
SELECT NVL(asset_units,0),
NVL(asset_category_id,-99),
NVL(book_type_code,'X')
INTO v_asset_units,
v_asset_category_id,
v_book_type_code
FROM pa_project_assets_all
WHERE project_asset_id = asset_basis_table(i).PROJECT_ASSET_ID;
SELECT COUNT(*)
INTO v_std_cost_count
FROM pa_standard_unit_costs
WHERE asset_category_id = v_asset_category_id
AND book_type_code = v_book_type_code;
SELECT standard_unit_cost
INTO v_std_unit_cost
FROM pa_standard_unit_costs
WHERE asset_category_id = v_asset_category_id
AND book_type_code = v_book_type_code;
SELECT current_asset_cost --NOT original_asset_cost, in case of prior manual splits
INTO v_original_asset_cost
FROM pa_project_asset_lines_all
WHERE project_asset_line_id = p_project_asset_line_id;
UPDATE pa_project_asset_lines_all
SET project_asset_id = asset_basis_table(i).PROJECT_ASSET_ID,
last_update_date = SYSDATE,
last_updated_by = v_user,
last_update_login = v_login,
request_id = v_request_id,
program_application_id = v_program_application_id,
program_id = v_program_id,
program_update_date = SYSDATE
WHERE project_asset_line_id = v_project_asset_line_id;
SELECT current_asset_cost
INTO v_current_asset_cost
FROM pa_project_asset_lines_all
WHERE project_asset_line_id = v_project_asset_line_id;
PA_FAXFACE.update_asset_cost
(asset_basis_table(i).PROJECT_ASSET_ID,
v_current_asset_cost,
0, --- capitalized_cost
v_err_stage,
v_err_code);
RAISE error_calling_update_cost;
SELECT current_asset_cost, project_asset_line_detail_id, rev_proj_asset_line_id, original_asset_cost /*Bug 4914051*/
INTO v_current_asset_cost, v_project_asset_line_detail_id, v_rev_proj_asset_line_id, v_orig_cost
FROM pa_project_asset_lines_all
WHERE project_asset_line_id = v_project_asset_line_id;
UPDATE pa_project_asset_lines_all
SET project_asset_id = asset_basis_table(i).PROJECT_ASSET_ID,
current_asset_cost = v_current_cost,
last_update_date = SYSDATE,
last_updated_by = v_user,
last_update_login = v_login,
request_id = v_request_id,
program_application_id = v_program_application_id,
program_id = v_program_id,
program_update_date = SYSDATE
WHERE project_asset_line_id = v_project_asset_line_id;
PA_FAXFACE.update_alc_proj_asset_lines(v_project_asset_line_id, v_orig_cost,
v_current_cost);
PA_FAXFACE.update_asset_cost
(asset_basis_table(i).PROJECT_ASSET_ID,
v_current_cost,
0, --- capitalized_cost
v_err_stage,
v_err_code);
RAISE error_calling_update_cost;
SELECT pa_project_asset_lines_s.NEXTVAL
INTO v_project_asset_line_id
FROM SYS.DUAL;
INSERT INTO pa_project_asset_lines_all(
project_asset_line_id,
description,
project_asset_id,
project_id,
task_id,
cip_ccid,
asset_cost_ccid,
original_asset_cost,
current_asset_cost,
project_asset_line_detail_id,
gl_date,
transfer_status_code,
transfer_rejection_reason,
amortize_flag,
asset_category_id,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
rev_proj_asset_line_id,
rev_from_proj_asset_line_id,
org_id,
invoice_number,
vendor_number,
po_vendor_id,
po_number,
invoice_date,
invoice_created_by,
invoice_updated_by,
invoice_id,
payables_batch_name,
ap_distribution_line_number,
original_asset_id,
line_type,
capital_event_id,
retirement_cost_type
)
SELECT
v_project_asset_line_id,
pal_rec.description,
0, --project_asset_id
pal_rec.project_id,
pal_rec.task_id,
pal_rec.cip_ccid,
pal_rec.asset_cost_ccid,
pal_rec.original_asset_cost,
v_remaining_cost,
pal_rec.project_asset_line_detail_id,
pal_rec.gl_date,
pal_rec.transfer_status_code,
pal_rec.transfer_rejection_reason,
pal_rec.amortize_flag,
pal_rec.asset_category_id,
SYSDATE, --last_update_date
v_user, --last_updated_by
v_user, --created_by
SYSDATE, --creation_date
v_login,
v_request_id,
v_program_application_id,
v_program_id,
SYSDATE, --program_update_date
pal_rec.rev_proj_asset_line_id,
pal_rec.rev_from_proj_asset_line_id,
pal_rec.org_id,
pal_rec.invoice_number,
pal_rec.vendor_number,
pal_rec.po_vendor_id,
pal_rec.po_number,
pal_rec.invoice_date,
pal_rec.invoice_created_by,
pal_rec.invoice_updated_by,
pal_rec.invoice_id,
pal_rec.payables_batch_name,
pal_rec.ap_distribution_line_number,
pal_rec.original_asset_id,
pal_rec.line_type,
pal_rec.capital_event_id,
pal_rec.retirement_cost_type
FROM pa_project_asset_lines_all pal_rec
WHERE project_asset_line_id = v_src_project_asset_line_id;
WHEN error_calling_update_cost THEN
x_return_status := 'U';
x_msg_data := v_err_code||' Error calling update_asset_cost for project asset id '||v_project_asset_id||' '||SQLERRM;