[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT p.project_id,
p.segment1 project_number,
p.name project_name,
p.asset_allocation_method
FROM pa_projects p,
pa_project_types pt
WHERE p.project_type = pt.project_type
AND pt.project_type_class_code = 'CAPITAL'
AND NVL(p.capital_event_processing,'N') = 'P'
AND p.project_id = NVL(p_project_id, p.project_id)
AND p.template_flag = 'N'
ORDER BY p.segment1;
SELECT pe.project_number,
pe.project_id,
p.name project_name,
pe.capital_type,
pl2.meaning capital_type_desc,
pe.context,
pe.sub_context,
pe.capital_event_id,
DECODE(sub_context,'A',UPPER(asset_name)||' ',
'AT',UPPER(asset_name)||' ',
NULL)||
pl.meaning||' '||
DECODE(sub_context,'E',capital_event_number||' '||event_name,
'AE',capital_event_number||' '||event_name,
'CE',capital_event_number||' '||event_name,
'P',project_number,
'T',task_number,
'AT',task_number) formatted_message
FROM pa_cap_event_creation_v pe,
pa_lookups pl,
pa_lookups pl2,
pa_projects p
WHERE pe.request_id = x_conc_request_id
AND pl.lookup_type = 'PERIODIC_EVENT_CREATION'
AND pl.lookup_code = pe.message_code
AND pl2.lookup_type = 'CAPITAL_TYPE'
AND pl2.lookup_code = pe.capital_type
AND pe.project_id = p.project_id (+)
ORDER BY pe.project_id,
pe.capital_type,
pe.context;
SELECT asset_name,
asset_description
FROM pa_project_assets_all
WHERE project_id = x_project_id
AND capital_event_id = x_capital_event_id
AND request_id = v_request_id;
SELECT SUM(DECODE(x_cost_type,'R',NVL(raw_cost,0),NVL(burden_cost,0))) total_cost
FROM pa_expenditure_items_all
WHERE project_id = x_project_id
AND capital_event_id = x_capital_event_id
AND request_id = v_request_id;
SELECT SUM(DECODE(x_cost_type,'R',NVL(raw_cost,0),NVL(burden_cost,0))) total_cost
FROM pa_expenditure_items_all
WHERE project_id = x_project_id
AND capital_event_id = x_capital_event_id;
SELECT meaning
INTO v_report_title
FROM pa_lookups
WHERE lookup_type = 'PERIODIC_EVENT_CREATION'
AND lookup_code = 'REPORT_TITLE';
SELECT meaning
INTO v_proj_heading1
FROM pa_lookups
WHERE lookup_type = 'PERIODIC_EVENT_CREATION'
AND lookup_code = 'PROJ_HEADING_1';
SELECT meaning
INTO v_proj_heading2
FROM pa_lookups
WHERE lookup_type = 'PERIODIC_EVENT_CREATION'
AND lookup_code = 'PROJ_HEADING_2';
SELECT meaning
INTO v_event_information
FROM pa_lookups
WHERE lookup_type = 'PERIODIC_EVENT_CREATION'
AND lookup_code = 'EVENT_INFO';
SELECT meaning
INTO v_assets_included
FROM pa_lookups
WHERE lookup_type = 'PERIODIC_EVENT_CREATION'
AND lookup_code = 'ASSETS_INCLUDED';
SELECT meaning
INTO v_cost_included
FROM pa_lookups
WHERE lookup_type = 'PERIODIC_EVENT_CREATION'
AND lookup_code = 'COST_INCLUDED';
SELECT meaning
INTO v_assets_added
FROM pa_lookups
WHERE lookup_type = 'PERIODIC_EVENT_CREATION'
AND lookup_code = 'ASSETS_ADDED';
SELECT meaning
INTO v_cost_added
FROM pa_lookups
WHERE lookup_type = 'PERIODIC_EVENT_CREATION'
AND lookup_code = 'COST_ADDED';
SELECT meaning
INTO v_total_cost
FROM pa_lookups
WHERE lookup_type = 'PERIODIC_EVENT_CREATION'
AND lookup_code = 'TOTAL_COST';
SELECT pt.capital_cost_type_code
INTO v_cost_type
FROM pa_projects p,
pa_project_types pt
WHERE p.project_type = pt.project_type
AND p.project_id = report_rec.project_id;
SELECT segment1
INTO v_project_number
FROM pa_projects_all
WHERE project_id = p_project_id;
INSERT INTO pa_cap_event_creation_v
(request_id,
module,
context,
sub_context,
capital_type,
project_id,
project_number,
project_asset_id,
asset_name,
task_id,
task_number,
capital_event_id,
capital_event_number,
event_name,
event_type,
message_code,
created_by,
creation_date,
org_id
)
VALUES
(v_request_id,
'PERIODIC_EVENT_CREATION', --module
'1', --context (1 = Message)
'N', --sub_context
'C', --capital_type,
p_project_id, --project_id,
v_project_number, --project_number,
NULL, --project_asset_id,
NULL, --asset_name,
NULL, --task_id,
NULL, --task_number,
NULL, --capital_event_id,
NULL, --capital_event_number,
NULL, --event_name,
NULL, --event_type,
'PROJECT_NOT_FOUND',
v_user_id, --created_by,
SYSDATE, --creation_date,
v_org_id --org_id
);
INSERT INTO pa_cap_event_creation_v
(request_id,
module,
context,
sub_context,
capital_type,
project_id,
project_number,
project_asset_id,
asset_name,
task_id,
task_number,
capital_event_id,
capital_event_number,
event_name,
event_type,
message_code,
created_by,
creation_date,
org_id
)
VALUES
(v_request_id,
'PERIODIC_EVENT_CREATION', --module
'1', --context (1 = Message)
'N', --sub_context
'C', --capital_type,
NULL, --project_id,
NULL, --project_number,
NULL, --project_asset_id,
NULL, --asset_name,
NULL, --task_id,
NULL, --task_number,
NULL, --capital_event_id,
NULL, --capital_event_number,
NULL, --event_name,
NULL, --event_type,
'NO_PROJECTS_FOUND',
v_user_id, --created_by,
SYSDATE, --creation_date,
v_org_id --org_id
);
SELECT COUNT(*)
INTO v_ret_tasks_count
FROM pa_tasks
WHERE project_id = ac_projects_rec.project_id
AND NVL(retirement_cost_flag,'N') = 'Y';
SELECT COUNT(*)
INTO v_ret_assets_count
FROM pa_project_assets
WHERE project_id = ac_projects_rec.project_id
AND project_asset_type = 'RETIREMENT_ADJUSTMENT';
SELECT capital_event_id,
capital_event_number,
event_name
FROM pa_capital_events
WHERE project_id = x_project_id
AND event_period = p_event_period_name
AND event_type = x_event_type
ORDER BY capital_event_id;
SELECT task_id
FROM pa_tasks
WHERE project_id = x_project_id
AND NVL(retirement_cost_flag,'N') = DECODE(x_event_type,'R','Y','N')
ORDER BY task_id;
SELECT pa.project_asset_id,
paa.task_id, --Grouping Level: If = 0, asset is assigned to project
t.retirement_cost_flag task_retirement_cost_flag --Will be NULL if Task ID = 0
FROM pa_project_assets_all pa,
pa_project_asset_assignments paa,
pa_tasks t
WHERE pa.project_id = x_project_id
AND pa.project_asset_id = paa.project_asset_id
AND pa.capital_event_id IS NULL
AND pa.project_asset_type = DECODE(x_event_type,'C','AS-BUILT','RETIREMENT_ADJUSTMENT')
AND pa.date_placed_in_service IS NOT NULL
AND pa.date_placed_in_service <= p_asset_date_through
AND paa.task_id = t.task_id (+)
ORDER BY pa.project_asset_id, paa.task_id;
SELECT t.task_id,
paa.task_id assignment_task_id,
t.top_task_id,
t.parent_task_id
FROM pa_project_asset_assignments paa,
pa_tasks t
WHERE paa.project_id = x_project_id
AND NVL(t.retirement_cost_flag,'N') = DECODE(x_event_type,'R','Y','N')
AND paa.project_asset_id = 0
AND (paa.task_id = t.task_id
OR paa.task_id = t.top_task_id);
SELECT peia.task_id
FROM pa_expenditure_items_all peia,
pa_tasks t
WHERE peia.project_id = x_project_id
AND peia.task_id = t.task_id
AND t.project_id = x_project_id
AND NVL(t.retirement_cost_flag,'N') = DECODE(x_event_type,'R','Y','N')
AND peia.billable_flag = DECODE(x_event_type,'C','Y','N')
AND peia.capital_event_id IS NULL
AND peia.expenditure_item_date <= NVL(p_ei_date_through, peia.expenditure_item_date)
AND peia.revenue_distributed_flag = 'N'
AND peia.cost_distributed_flag = 'Y'
GROUP BY peia.task_id;
SELECT pa.project_asset_id,
pa.asset_name,
paa.task_id --Grouping Level: If = 0, asset is assigned to project
FROM pa_project_assets_all pa,
pa_project_asset_assignments paa
WHERE pa.project_id = x_project_id
AND pa.project_asset_id = paa.project_asset_id (+)
AND pa.capital_event_id IS NULL
AND pa.project_asset_type = DECODE(x_event_type,'C','AS-BUILT','RETIREMENT_ADJUSTMENT')
AND pa.date_placed_in_service IS NOT NULL
AND pa.date_placed_in_service <= p_asset_date_through
ORDER BY pa.project_asset_id, paa.task_id;
SELECT p.segment1 project_number,
p.name project_name,
c.capital_event_id,
c.capital_event_number,
c.event_name,
c.event_type
FROM pa_capital_events c,
pa_projects p
WHERE c.project_id = x_project_id
AND c.event_type = x_event_type
AND c.request_id = x_request_id
AND p.project_id = x_project_id
ORDER BY p.segment1, c.capital_event_number;
SELECT p.segment1 project_number,
p.name project_name,
c.project_id,
c.capital_event_id,
c.capital_event_number,
c.event_name,
c.event_type
FROM pa_capital_events c,
pa_projects p
WHERE c.project_id = p.project_id
AND c.project_id = x_project_id
AND c.event_period = p_event_period_name
AND c.event_type = x_event_type
AND c.request_id <> x_request_id
ORDER BY p.segment1, c.capital_event_number;
SELECT 'Additional Costs Added'
FROM SYS.DUAL
WHERE EXISTS
(SELECT 'X'
FROM pa_expenditure_items_all
WHERE project_id = x_project_id
AND capital_event_id = x_capital_event_id
AND request_id = x_request_id);
SELECT 'Additional Assets Added'
FROM SYS.DUAL
WHERE EXISTS
(SELECT 'X'
FROM pa_project_assets_all
WHERE project_id = x_project_id
AND capital_event_id = x_capital_event_id
AND request_id = x_request_id);
SELECT task_id,
task_number
FROM pa_tasks
WHERE task_id <> x_parent_task_id
AND task_id <> x_current_task_id
AND NVL(retirement_cost_flag,'N') = DECODE(x_event_type,'R','Y','N')
CONNECT BY parent_task_id = PRIOR task_id
START WITH task_id = x_parent_task_id;
SELECT paa.project_asset_id
FROM pa_project_assets_all pa,
pa_project_asset_assignments paa
WHERE pa.project_asset_id = paa.project_asset_id
AND pa.project_id = x_project_id
AND paa.project_id = x_project_id
AND pa.capital_event_id = x_capital_event_id
AND paa.task_id = x_task_id
AND pa.project_asset_type = DECODE(x_event_type,'C','AS-BUILT','R','RETIREMENT_ADJUSTMENT','AS-BUILT');
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;
UPDATE pa_project_assets_all
SET capital_event_id = capital_event_rec.capital_event_id,
last_update_date = SYSDATE,
last_updated_by = v_user_id,
request_id = v_request_id,
program_application_id = v_program_application_id,
program_id = v_program_id,
program_update_date = SYSDATE
WHERE project_id = p_project_id
AND capital_event_id IS NULL
AND project_asset_type = DECODE(p_event_type,'C','AS-BUILT','RETIREMENT_ADJUSTMENT')
AND date_placed_in_service <= p_asset_date_through;
UPDATE pa_expenditure_items_all
SET capital_event_id = capital_event_rec.capital_event_id,
last_update_date = SYSDATE,
last_updated_by = v_user_id,
request_id = v_request_id,
program_application_id = v_program_application_id,
program_id = v_program_id,
program_update_date = SYSDATE
WHERE project_id = p_project_id
AND billable_flag = DECODE(p_event_type,'C','Y','N')
AND capital_event_id IS NULL
AND expenditure_item_date <= NVL(p_ei_date_through, expenditure_item_date)
AND revenue_distributed_flag = 'N'
AND cost_distributed_flag = 'Y'
AND task_id = add_ei_tasks_rec.task_id;
SELECT COUNT(*)
INTO v_asset_count
FROM pa_project_assets_all
WHERE project_id = p_project_id
AND capital_event_id IS NULL
AND project_asset_type = DECODE(p_event_type,'C','AS-BUILT','RETIREMENT_ADJUSTMENT')
AND date_placed_in_service <= p_asset_date_through;
SELECT COUNT(*)
INTO v_ei_count
FROM pa_expenditure_items_all ei,
pa_tasks t
WHERE ei.project_id = p_project_id
AND t.task_id = ei.task_id
AND NVL(t.retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N')
AND ei.billable_flag = DECODE(p_event_type,'C','Y','N')
AND ei.capital_event_id IS NULL
AND ei.expenditure_item_date <= NVL(p_ei_date_through, ei.expenditure_item_date)
AND ei.revenue_distributed_flag = 'N'
AND ei.cost_distributed_flag = 'Y';
SELECT COUNT(*)
INTO v_ei_count
From Dual Where Exists
(Select 1
FROM pa_expenditure_items_all ei,
pa_tasks t
WHERE ei.project_id = p_project_id
AND t.task_id = ei.task_id
AND NVL(t.retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N')
AND ei.billable_flag = DECODE(p_event_type,'C','Y','N')
AND ei.capital_event_id IS NULL
AND ei.expenditure_item_date <= NVL(p_ei_date_through, ei.expenditure_item_date)
AND ei.revenue_distributed_flag = 'N'
AND ei.cost_distributed_flag = 'Y'
);
SELECT NVL(MAX(capital_event_number),0)
INTO v_event_number
FROM pa_capital_events
WHERE project_id = p_project_id;
SELECT meaning
INTO v_capital_type
FROM pa_lookups
WHERE lookup_type = 'CAPITAL_TYPE'
AND lookup_code = p_event_type;
PA_CAPITAL_EVENTS_PKG.INSERT_ROW
(x_rowid => v_null_rowid,
x_capital_event_id => v_new_event_id,
x_project_id => p_project_id,
x_capital_event_number => v_event_number,
x_event_type => p_event_type,
x_event_name => p_event_period_name||' '||v_capital_type,
x_asset_allocation_method => p_asset_allocation_method,
x_event_period => p_event_period_name,
x_last_update_date => SYSDATE,
x_last_updated_by => v_user_id,
x_creation_date => SYSDATE,
x_created_by => v_user_id,
x_last_update_login => v_login_id,
x_request_id => v_request_id,
x_program_application_id => v_program_application_id,
x_program_id => v_program_id,
x_program_update_date => SYSDATE);
UPDATE pa_project_assets_all
SET capital_event_id = capital_event_rec.capital_event_id,
last_update_date = SYSDATE,
last_updated_by = v_user_id,
request_id = v_request_id,
program_application_id = v_program_application_id,
program_id = v_program_id,
program_update_date = SYSDATE
WHERE project_id = p_project_id
AND capital_event_id IS NULL
AND project_asset_type = DECODE(p_event_type,'C','AS-BUILT','RETIREMENT_ADJUSTMENT')
AND date_placed_in_service <= p_asset_date_through;
UPDATE pa_expenditure_items_all
SET capital_event_id = capital_event_rec.capital_event_id,
last_update_date = SYSDATE,
last_updated_by = v_user_id,
request_id = v_request_id,
program_application_id = v_program_application_id,
program_id = v_program_id,
program_update_date = SYSDATE
WHERE project_id = p_project_id
AND billable_flag = DECODE(p_event_type,'C','Y','N')
AND capital_event_id IS NULL
AND expenditure_item_date <= NVL(p_ei_date_through, expenditure_item_date)
AND revenue_distributed_flag = 'N'
AND cost_distributed_flag = 'Y'
AND task_id = add_ei_tasks_rec.task_id;
INSERT INTO pa_cap_event_creation_v
(request_id,
module,
context,
sub_context,
capital_type,
project_id,
project_number,
project_asset_id,
asset_name,
task_id,
task_number,
capital_event_id,
capital_event_number,
event_name,
event_type,
message_code,
created_by,
creation_date,
org_id
)
VALUES
(v_request_id,
'PERIODIC_EVENT_CREATION', --module
'1', --context (1 = Message)
'P', --sub_context
p_event_type, --capital_type,
p_project_id, --project_id,
p_project_number, --project_number,
NULL, --project_asset_id,
NULL, --asset_name,
NULL, --task_id,
NULL, --task_number,
NULL, --capital_event_id,
NULL, --capital_event_number,
NULL, --event_name,
NULL, --event_type,
'NO_ASSETS_OR_COSTS_PROJ',
v_user_id, --created_by,
SYSDATE, --creation_date,
v_org_id --org_id
);
INSERT INTO pa_cap_event_creation_v
(request_id,
module,
context,
sub_context,
capital_type,
project_id,
project_number,
project_asset_id,
asset_name,
task_id,
task_number,
capital_event_id,
capital_event_number,
event_name,
event_type,
message_code,
created_by,
creation_date,
org_id
)
VALUES
(v_request_id,
'PERIODIC_EVENT_CREATION', --module
'1', --context (1 = Message)
'P', --sub_context
p_event_type, --capital_type,
p_project_id, --project_id,
p_project_number, --project_number,
NULL, --project_asset_id,
NULL, --asset_name,
NULL, --task_id,
NULL, --task_number,
NULL, --capital_event_id,
NULL, --capital_event_number,
NULL, --event_name,
NULL, --event_type,
'ASSETS_BUT_NO_COSTS_PROJ',
v_user_id, --created_by,
SYSDATE, --creation_date,
v_org_id --org_id
);
INSERT INTO pa_cap_event_creation_v
(request_id,
module,
context,
sub_context,
capital_type,
project_id,
project_number,
project_asset_id,
asset_name,
task_id,
task_number,
capital_event_id,
capital_event_number,
event_name,
event_type,
message_code,
created_by,
creation_date,
org_id
)
VALUES
(v_request_id,
'PERIODIC_EVENT_CREATION', --module
'1', --context (1 = Message)
'P', --sub_context
p_event_type, --capital_type,
p_project_id, --project_id,
p_project_number, --project_number,
NULL, --project_asset_id,
NULL, --asset_name,
NULL, --task_id,
NULL, --task_number,
NULL, --capital_event_id,
NULL, --capital_event_number,
NULL, --event_name,
NULL, --event_type,
'COSTS_BUT_NO_ASSETS_PROJ',
v_user_id, --created_by,
SYSDATE, --creation_date,
v_org_id --org_id
);
SELECT COUNT(*)
INTO v_ei_count
FROM pa_expenditure_items_all
WHERE capital_event_id = capital_event_rec.capital_event_id
AND project_id = p_project_id;
SELECT COUNT(*)
INTO v_ei_count
From Dual Where Exists
(Select 1
FROM pa_expenditure_items_all
WHERE capital_event_id = capital_event_rec.capital_event_id
AND project_id = p_project_id
);
SELECT COUNT(*)
INTO v_ei_count
FROM pa_expenditure_items_all peia,
pa_tasks t
WHERE peia.task_id = t.task_id
AND peia.capital_event_id = capital_event_rec.capital_event_id
AND peia.project_id = p_project_id
AND (new_assets_rec.task_id = t.task_id
OR new_assets_rec.task_id = t.top_task_id);
SELECT COUNT(*)
INTO v_ei_count
From Dual Where Exists
( Select 1
FROM pa_expenditure_items_all peia,
pa_tasks t
WHERE peia.task_id = t.task_id
AND peia.capital_event_id = capital_event_rec.capital_event_id
AND peia.project_id = p_project_id
AND (new_assets_rec.task_id = t.task_id
OR new_assets_rec.task_id = t.top_task_id)
);
UPDATE pa_project_assets_all
SET capital_event_id = capital_event_rec.capital_event_id,
last_update_date = SYSDATE,
last_updated_by = v_user_id,
request_id = v_request_id,
program_application_id = v_program_application_id,
program_id = v_program_id,
program_update_date = SYSDATE
WHERE project_asset_id = new_assets_rec.project_asset_id
AND capital_event_id IS NULL;
SELECT COUNT(*)
INTO v_ei_count
FROM pa_expenditure_items_all peia,
pa_tasks t
WHERE peia.project_id = p_project_id
AND peia.task_id = t.task_id
AND NVL(t.retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N')
AND peia.billable_flag = DECODE(p_event_type,'C','Y','N')
AND peia.capital_event_id IS NULL
AND peia.expenditure_item_date <= NVL(p_ei_date_through, peia.expenditure_item_date)
AND peia.revenue_distributed_flag = 'N'
AND peia.cost_distributed_flag = 'Y';
SELECT COUNT(*)
INTO v_ei_count
FROM pa_expenditure_items_all peia,
pa_tasks t
WHERE peia.task_id = t.task_id
AND NVL(t.retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N')
AND peia.project_id = p_project_id
AND peia.billable_flag = DECODE(p_event_type,'C','Y','N')
AND peia.capital_event_id IS NULL
AND peia.expenditure_item_date <= NVL(p_ei_date_through, expenditure_item_date)
AND peia.revenue_distributed_flag = 'N'
AND peia.cost_distributed_flag = 'Y'
AND (new_assets_rec.task_id = t.task_id
OR new_assets_rec.task_id = t.top_task_id);
UPDATE pa_project_assets_all
SET capital_event_id = capital_event_rec.capital_event_id,
last_update_date = SYSDATE,
last_updated_by = v_user_id,
request_id = v_request_id,
program_application_id = v_program_application_id,
program_id = v_program_id,
program_update_date = SYSDATE
WHERE project_asset_id = new_assets_rec.project_asset_id
AND capital_event_id IS NULL;
UPDATE pa_expenditure_items_all
SET capital_event_id = capital_event_rec.capital_event_id,
last_update_date = SYSDATE,
last_updated_by = v_user_id,
request_id = v_request_id,
program_application_id = v_program_application_id,
program_id = v_program_id,
program_update_date = SYSDATE
WHERE project_id = p_project_id
AND capital_event_id IS NULL
AND billable_flag = DECODE(p_event_type,'C','Y','N')
AND expenditure_item_date <= NVL(p_ei_date_through, expenditure_item_date)
AND revenue_distributed_flag = 'N'
AND cost_distributed_flag = 'Y'
AND (
((new_assets_rec.task_id = 0) AND task_id IN
(SELECT task_id
FROM pa_tasks
WHERE project_id = p_project_id
AND NVL(retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N')))
OR (task_id = new_assets_rec.task_id
AND NVL(new_assets_rec.task_retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N'))
OR task_id IN
(SELECT task_id
FROM pa_tasks
WHERE top_task_id = new_assets_rec.task_id
AND NVL(retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N'))
);
SELECT COUNT(*)
INTO v_ei_count
FROM pa_expenditure_items_all peia,
pa_tasks t
WHERE peia.project_id = p_project_id
AND peia.task_id = t.task_id
AND NVL(t.retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N')
AND peia.billable_flag = DECODE(p_event_type,'C','Y','N')
AND peia.capital_event_id IS NULL
AND peia.expenditure_item_date <= NVL(p_ei_date_through, peia.expenditure_item_date)
AND peia.revenue_distributed_flag = 'N'
AND peia.cost_distributed_flag = 'Y';
SELECT COUNT(*)
INTO v_ei_count
FROM pa_expenditure_items_all peia,
pa_tasks t
WHERE peia.task_id = t.task_id
AND NVL(t.retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N')
AND peia.project_id = p_project_id
AND peia.billable_flag = DECODE(p_event_type,'C','Y','N')
AND peia.capital_event_id IS NULL
AND peia.expenditure_item_date <= NVL(p_ei_date_through, expenditure_item_date)
AND peia.revenue_distributed_flag = 'N'
AND peia.cost_distributed_flag = 'Y'
AND (new_assets_rec.task_id = t.task_id
OR new_assets_rec.task_id = t.top_task_id);
SELECT NVL(MAX(capital_event_number),0)
INTO v_event_number
FROM pa_capital_events
WHERE project_id = p_project_id;
SELECT meaning
INTO v_capital_type
FROM pa_lookups
WHERE lookup_type = 'CAPITAL_TYPE'
AND lookup_code = p_event_type;
PA_CAPITAL_EVENTS_PKG.INSERT_ROW
(x_rowid => v_null_rowid,
x_capital_event_id => v_new_event_id,
x_project_id => p_project_id,
x_capital_event_number => v_event_number,
x_event_type => p_event_type,
x_event_name => p_event_period_name||' '||v_capital_type,
x_asset_allocation_method => p_asset_allocation_method,
x_event_period => p_event_period_name,
x_last_update_date => SYSDATE,
x_last_updated_by => v_user_id,
x_creation_date => SYSDATE,
x_created_by => v_user_id,
x_last_update_login => v_login_id,
x_request_id => v_request_id,
x_program_application_id => v_program_application_id,
x_program_id => v_program_id,
x_program_update_date => SYSDATE);
UPDATE pa_project_assets_all
SET capital_event_id = capital_event_rec.capital_event_id,
last_update_date = SYSDATE,
last_updated_by = v_user_id,
request_id = v_request_id,
program_application_id = v_program_application_id,
program_id = v_program_id,
program_update_date = SYSDATE
WHERE project_asset_id = new_assets_rec.project_asset_id
AND capital_event_id IS NULL;
UPDATE pa_expenditure_items_all
SET capital_event_id = capital_event_rec.capital_event_id,
last_update_date = SYSDATE,
last_updated_by = v_user_id,
request_id = v_request_id,
program_application_id = v_program_application_id,
program_id = v_program_id,
program_update_date = SYSDATE
WHERE project_id = p_project_id
AND capital_event_id IS NULL
AND billable_flag = DECODE(p_event_type,'C','Y','N')
AND expenditure_item_date <= NVL(p_ei_date_through, expenditure_item_date)
AND revenue_distributed_flag = 'N'
AND cost_distributed_flag = 'Y'
AND (
((new_assets_rec.task_id = 0) AND task_id IN
(SELECT task_id
FROM pa_tasks
WHERE project_id = p_project_id
AND NVL(retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N')))
OR (task_id = new_assets_rec.task_id
AND NVL(new_assets_rec.task_retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N'))
OR task_id IN
(SELECT task_id
FROM pa_tasks
WHERE top_task_id = new_assets_rec.task_id
AND NVL(retirement_cost_flag,'N') = DECODE(p_event_type,'R','Y','N'))
);
UPDATE pa_expenditure_items_all
SET capital_event_id = capital_event_rec.capital_event_id,
last_update_date = SYSDATE,
last_updated_by = v_user_id,
request_id = v_request_id,
program_application_id = v_program_application_id,
program_id = v_program_id,
program_update_date = SYSDATE
WHERE project_id = p_project_id
AND capital_event_id IS NULL
AND billable_flag = DECODE(p_event_type,'C','Y','N')
AND expenditure_item_date <= NVL(p_ei_date_through, expenditure_item_date)
AND revenue_distributed_flag = 'N'
AND cost_distributed_flag = 'Y'
AND task_id = common_tasks_rec.task_id;
UPDATE pa_expenditure_items_all
SET capital_event_id = capital_event_rec.capital_event_id,
last_update_date = SYSDATE,
last_updated_by = v_user_id,
request_id = v_request_id,
program_application_id = v_program_application_id,
program_id = v_program_id,
program_update_date = SYSDATE
WHERE project_id = p_project_id
AND capital_event_id IS NULL
AND billable_flag = DECODE(p_event_type,'C','Y','N')
AND expenditure_item_date <= NVL(p_ei_date_through, expenditure_item_date)
AND revenue_distributed_flag = 'N'
AND cost_distributed_flag = 'Y'
AND task_id = common_tasks_rec.task_id; --NOTE: We are attaching the costs under the original task,
SELECT top_task_id
INTO v_top_task_id
FROM pa_tasks
WHERE task_id = remaining_costs_rec.task_id;
SELECT COUNT(*)
INTO v_asset_count
FROM pa_project_assets_all pa,
pa_project_asset_assignments paa
WHERE pa.project_asset_id = paa.project_asset_id
AND pa.capital_event_id = capital_event_rec.capital_event_id
AND (paa.task_id = remaining_costs_rec.task_id
OR paa.task_id = v_top_task_id
OR paa.task_id = 0);
SELECT COUNT(*)
INTO v_asset_count
From Dual Where Exists
(Select 1
FROM pa_project_assets_all pa,
pa_project_asset_assignments paa
WHERE pa.project_asset_id = paa.project_asset_id
AND pa.capital_event_id = capital_event_rec.capital_event_id
AND (paa.task_id = remaining_costs_rec.task_id
OR paa.task_id = v_top_task_id
OR paa.task_id = 0)
);
UPDATE pa_expenditure_items_all
SET capital_event_id = capital_event_rec.capital_event_id,
last_update_date = SYSDATE,
last_updated_by = v_user_id,
request_id = v_request_id,
program_application_id = v_program_application_id,
program_id = v_program_id,
program_update_date = SYSDATE
WHERE project_id = p_project_id
AND capital_event_id IS NULL
AND billable_flag = DECODE(p_event_type,'C','Y','N')
AND expenditure_item_date <= NVL(p_ei_date_through, expenditure_item_date)
AND revenue_distributed_flag = 'N'
AND cost_distributed_flag = 'Y'
AND task_id = remaining_costs_rec.task_id;
SELECT task_number
INTO v_task_number
FROM pa_tasks
WHERE task_id = remaining_costs_rec.task_id;
INSERT INTO pa_cap_event_creation_v
(request_id,
module,
context,
sub_context,
capital_type,
project_id,
project_number,
project_asset_id,
asset_name,
task_id,
task_number,
capital_event_id,
capital_event_number,
event_name,
event_type,
message_code,
created_by,
creation_date,
org_id
)
VALUES
(v_request_id,
'PERIODIC_EVENT_CREATION', --module
'1', --context (1 = Message)
'T', --sub_context
p_event_type, --capital_type,
p_project_id, --project_id,
p_project_number, --project_number,
NULL, --project_asset_id,
NULL, --asset_name,
remaining_costs_rec.task_id, --task_id,
v_task_number, --task_number,
NULL, --capital_event_id,
NULL, --capital_event_number,
NULL, --event_name,
NULL, --event_type,
'TASK_COSTS_BUT_NO_ASSETS',
v_user_id, --created_by,
SYSDATE, --creation_date,
v_org_id --org_id
);
INSERT INTO pa_cap_event_creation_v
(request_id,
module,
context,
sub_context,
capital_type,
project_id,
project_number,
project_asset_id,
asset_name,
task_id,
task_number,
capital_event_id,
capital_event_number,
event_name,
event_type,
message_code,
created_by,
creation_date,
org_id
)
VALUES
(v_request_id,
'PERIODIC_EVENT_CREATION', --module
'1', --context (1 = Message)
'A', --sub_context
p_event_type, --capital_type,
p_project_id, --project_id,
p_project_number, --project_number,
remaining_assets_rec.project_asset_id, --project_asset_id,
remaining_assets_rec.asset_name, --asset_name,
NULL, --task_id,
NULL, --task_number,
NULL, --capital_event_id,
NULL, --capital_event_number,
NULL, --event_name,
NULL, --event_type,
'ASSET_WITH_NO_ASSIGNMENT',
v_user_id, --created_by,
SYSDATE, --creation_date,
v_org_id --org_id
);
INSERT INTO pa_cap_event_creation_v
(request_id,
module,
context,
sub_context,
capital_type,
project_id,
project_number,
project_asset_id,
asset_name,
task_id,
task_number,
capital_event_id,
capital_event_number,
event_name,
event_type,
message_code,
created_by,
creation_date,
org_id
)
VALUES
(v_request_id,
'PERIODIC_EVENT_CREATION', --module
'1', --context (1 = Message)
'A', --sub_context
p_event_type, --capital_type,
p_project_id, --project_id,
p_project_number, --project_number,
remaining_assets_rec.project_asset_id, --project_asset_id,
remaining_assets_rec.asset_name, --asset_name,
NULL, --task_id,
NULL, --task_number,
NULL, --capital_event_id,
NULL, --capital_event_number,
NULL, --event_name,
NULL, --event_type,
'ASSET_WITH_NO_COSTS',
v_user_id, --created_by,
SYSDATE, --creation_date,
v_org_id --org_id
);
SELECT task_number
INTO v_task_number
FROM pa_tasks
WHERE task_id = remaining_assets_rec.task_id;
INSERT INTO pa_cap_event_creation_v
(request_id,
module,
context,
sub_context,
capital_type,
project_id,
project_number,
project_asset_id,
asset_name,
task_id,
task_number,
capital_event_id,
capital_event_number,
event_name,
event_type,
message_code,
created_by,
creation_date,
org_id
)
VALUES
(v_request_id,
'PERIODIC_EVENT_CREATION', --module
'1', --context (1 = Message)
'AT', --sub_context
p_event_type, --capital_type,
p_project_id, --project_id,
p_project_number, --project_number,
remaining_assets_rec.project_asset_id, --project_asset_id,
remaining_assets_rec.asset_name, --asset_name,
remaining_assets_rec.task_id, --task_id,
v_task_number, --task_number,
NULL, --capital_event_id,
NULL, --capital_event_number,
NULL, --event_name,
NULL, --event_type,
'ASSET_WITH_NO_COSTS_FOR_TASK',
v_user_id, --created_by,
SYSDATE, --creation_date,
v_org_id --org_id
);
INSERT INTO pa_cap_event_creation_v
(request_id,
module,
context,
sub_context,
capital_type,
project_id,
project_number,
project_asset_id,
asset_name,
task_id,
task_number,
capital_event_id,
capital_event_number,
event_name,
event_type,
message_code,
created_by,
creation_date,
org_id
)
VALUES
(v_request_id,
'PERIODIC_EVENT_CREATION', --module
'2', --context (2 = New Event Creation)
'NE', --sub_context
p_event_type, --capital_type,
p_project_id, --project_id,
p_project_number, --project_number,
NULL, --project_asset_id,
NULL, --asset_name,
NULL, --task_id,
NULL, --task_number,
NULL, --capital_event_id,
NULL, --capital_event_number,
NULL, --event_name,
NULL, --event_type,
'NO_EVENTS_CREATED',
v_user_id, --created_by,
SYSDATE, --creation_date,
v_org_id --org_id
);
INSERT INTO pa_cap_event_creation_v
(request_id,
module,
context,
sub_context,
capital_type,
project_id,
project_number,
project_asset_id,
asset_name,
task_id,
task_number,
capital_event_id,
capital_event_number,
event_name,
event_type,
message_code,
created_by,
creation_date,
org_id
)
VALUES
(v_request_id,
'PERIODIC_EVENT_CREATION', --module
'2', --context (2 = New Event Creation)
'E', --sub_context
p_event_type, --capital_type,
p_project_id, --project_id,
p_project_number, --project_number,
NULL, --project_asset_id,
NULL, --asset_name,
NULL, --task_id,
NULL, --task_number,
print_events_rec.capital_event_id, --capital_event_id,
print_events_rec.capital_event_number, --capital_event_number,
print_events_rec.event_name, --event_name,
print_events_rec.event_type, --event_type,
'EVENT_CREATED',
v_user_id, --created_by,
SYSDATE, --creation_date,
v_org_id --org_id
);
INSERT INTO pa_cap_event_creation_v
(request_id,
module,
context,
sub_context,
capital_type,
project_id,
project_number,
project_asset_id,
asset_name,
task_id,
task_number,
capital_event_id,
capital_event_number,
event_name,
event_type,
message_code,
created_by,
creation_date,
org_id
)
VALUES
(v_request_id,
'PERIODIC_EVENT_CREATION', --module
'3', --context (2 = Additions to Existing Events)
'NE', --sub_context
p_event_type, --capital_type,
p_project_id, --project_id,
p_project_number, --project_number,
NULL, --project_asset_id,
NULL, --asset_name,
NULL, --task_id,
NULL, --task_number,
NULL, --capital_event_id,
NULL, --capital_event_number,
NULL, --event_name,
NULL, --event_type,
'NO_EVENT_ITEMS_ADDED',
v_user_id, --created_by,
SYSDATE, --creation_date,
v_org_id --org_id
);
INSERT INTO pa_cap_event_creation_v
(request_id,
module,
context,
sub_context,
capital_type,
project_id,
project_number,
project_asset_id,
asset_name,
task_id,
task_number,
capital_event_id,
capital_event_number,
event_name,
event_type,
message_code,
created_by,
creation_date,
org_id
)
VALUES
(v_request_id,
'PERIODIC_EVENT_CREATION', --module
'3', --context (3 = Additions to Existing Events)
'AE', --sub_context
p_event_type, --capital_type,
p_project_id, --project_id,
p_project_number, --project_number,
NULL, --project_asset_id,
NULL, --asset_name,
NULL, --task_id,
NULL, --task_number,
existing_events_rec.capital_event_id, --capital_event_id,
existing_events_rec.capital_event_number, --capital_event_number,
existing_events_rec.event_name, --event_name,
existing_events_rec.event_type, --event_type,
'EVENT_ASSETS_ADDED',
v_user_id, --created_by,
SYSDATE, --creation_date,
v_org_id --org_id
);
INSERT INTO pa_cap_event_creation_v
(request_id,
module,
context,
sub_context,
capital_type,
project_id,
project_number,
project_asset_id,
asset_name,
task_id,
task_number,
capital_event_id,
capital_event_number,
event_name,
event_type,
message_code,
created_by,
creation_date,
org_id
)
VALUES
(v_request_id,
'PERIODIC_EVENT_CREATION', --module
'3', --context (3 = Additions to Existing Events)
'CE', --sub_context
p_event_type, --capital_type,
p_project_id, --project_id,
p_project_number, --project_number,
NULL, --project_asset_id,
NULL, --asset_name,
NULL, --task_id,
NULL, --task_number,
existing_events_rec.capital_event_id, --capital_event_id,
existing_events_rec.capital_event_number, --capital_event_number,
existing_events_rec.event_name, --event_name,
existing_events_rec.event_type, --event_type,
'EVENT_COSTS_ADDED',
v_user_id, --created_by,
SYSDATE, --creation_date,
v_org_id --org_id
);
INSERT INTO pa_cap_event_creation_v
(request_id,
module,
context,
sub_context,
capital_type,
project_id,
project_number,
project_asset_id,
asset_name,
task_id,
task_number,
capital_event_id,
capital_event_number,
event_name,
event_type,
message_code,
created_by,
creation_date,
org_id
)
VALUES
(v_request_id,
'PERIODIC_EVENT_CREATION', --module
'3', --context (3 = Additions to Existing Events)
'NE', --sub_context
p_event_type, --capital_type,
p_project_id, --project_id,
p_project_number, --project_number,
NULL, --project_asset_id,
NULL, --asset_name,
NULL, --task_id,
NULL, --task_number,
NULL, --capital_event_id,
NULL, --capital_event_number,
NULL, --event_name,
NULL, --event_type,
'NO_EVENT_ITEMS_ADDED',
v_user_id, --created_by,
SYSDATE, --creation_date,
v_org_id --org_id
);
SELECT ppa.project_asset_id
FROM pa_project_assets_all ppa
WHERE ppa.project_id = p_project_id
AND ppa.capital_event_id IS NULL
AND ppa.project_asset_type = DECODE(x_event_type,'C','AS-BUILT','R','RETIREMENT_ADJUSTMENT','X')
AND ppa.date_placed_in_service IS NOT NULL
AND ppa.date_placed_in_service
BETWEEN NVL(p_asset_date_from, ppa.date_placed_in_service)
AND NVL(p_asset_date_to, ppa.date_placed_in_service)
AND NVL(ppa.book_type_code,'X') = NVL(p_book_type_code,NVL(ppa.book_type_code,'X'))
AND ppa.asset_name = NVL(p_asset_name,ppa.asset_name)
AND NVL(ppa.asset_category_id,-99) = NVL(p_asset_category_id,NVL(ppa.asset_category_id,-99))
AND NVL(ppa.location_id,-99) = NVL(p_location_id,NVL(ppa.location_id,-99))
AND NVL(ppa.ret_target_asset_id,-99) = NVL(p_ret_target_asset_id,NVL(ppa.ret_target_asset_id,-99))
AND EXISTS
(SELECT 'Assignment Exists'
FROM pa_project_asset_assignments paa
WHERE paa.project_id = p_project_id
AND (
(paa.project_asset_id = ppa.project_asset_id) --Asset is specifically assigned to project or task(s)
OR
(paa.project_asset_id = 0 AND paa.task_id = 0) --There is a Project-Level Common Assignment
)
);
SELECT paa.project_asset_id,
paa.task_id
FROM pa_project_asset_assignments paa,
pa_tasks pt
WHERE pt.project_id = p_project_id
AND paa.project_id = p_project_id
AND paa.project_asset_id = x_project_asset_id
AND pt.task_id = paa.task_id
AND pt.task_number
BETWEEN NVL(p_task_number_from, pt.task_number)
AND NVL(p_task_number_to, pt.task_number);
SELECT event_type
INTO v_event_type
FROM pa_capital_events
WHERE capital_event_id = p_capital_event_id;
UPDATE pa_project_assets_all
SET capital_event_id = p_capital_event_id,
last_update_date = SYSDATE,
last_updated_by = v_user_id,
last_update_login = v_login_id
WHERE project_asset_id = assets_rec.project_asset_id
AND capital_event_id IS NULL;
UPDATE pa_project_assets_all
SET capital_event_id = p_capital_event_id,
last_update_date = SYSDATE,
last_updated_by = v_user_id,
last_update_login = v_login_id
WHERE project_asset_id = assets_rec.project_asset_id
AND capital_event_id IS NULL;
SELECT peia.expenditure_item_id,
peia.task_id,
t.top_task_id,
t.parent_task_id
FROM pa_expenditure_items_all peia,
pa_tasks t
WHERE t.project_id = p_project_id
AND peia.task_id = t.task_id
AND t.task_number
BETWEEN NVL(p_task_number_from, t.task_number)
AND NVL(p_task_number_to, t.task_number)
AND peia.capital_event_id IS NULL
AND peia.billable_flag = DECODE(x_event_type,'C','Y','N')
AND peia.revenue_distributed_flag = 'N'
AND peia.cost_distributed_flag = 'Y'
AND NVL(t.retirement_cost_flag,'N') = DECODE(x_event_type,'R','Y','N')
AND peia.expenditure_type = NVL(p_expenditure_type, peia.expenditure_type)
AND NVL(peia.transaction_source,'X') = NVL(p_transaction_source, NVL(peia.transaction_source,'X'))
AND peia.expenditure_item_date
BETWEEN NVL(p_ei_date_from, peia.expenditure_item_date)
AND NVL(p_ei_date_to, peia.expenditure_item_date)
ORDER BY peia.task_id;
SELECT paa.task_id
FROM pa_project_asset_assignments paa
WHERE paa.project_id = p_project_id
AND paa.task_id IN (x_task_id,x_top_task_id)
AND paa.project_asset_id = 0;
SELECT task_id,
task_number
FROM pa_tasks
WHERE task_id <> x_parent_task_id
AND task_id <> x_current_task_id
AND NVL(retirement_cost_flag,'N') = DECODE(x_event_type,'R','Y','N')
CONNECT BY parent_task_id = PRIOR task_id
START WITH task_id = x_parent_task_id;
SELECT paa.project_asset_id
FROM pa_project_assets_all pa,
pa_project_asset_assignments paa
WHERE pa.project_asset_id = paa.project_asset_id
AND pa.project_id = x_project_id
AND paa.project_id = x_project_id
AND pa.capital_event_id = x_capital_event_id
AND paa.task_id = x_task_id
AND pa.project_asset_type = DECODE(x_event_type,'C','AS-BUILT','R','RETIREMENT_ADJUSTMENT','AS-BUILT');
SELECT event_type
INTO v_event_type
FROM pa_capital_events
WHERE capital_event_id = p_capital_event_id;
SELECT DECODE(COUNT(*),0,'N','Y')
INTO v_project_assignment
FROM pa_project_asset_assignments
WHERE project_id = p_project_id
AND task_id = 0;
SELECT DECODE(COUNT(*),0,'N','Y')
INTO v_task_assignment
FROM pa_project_asset_assignments paa,
pa_project_assets_all ppa
WHERE paa.project_id = p_project_id
AND paa.project_asset_id = ppa.project_asset_id
AND ppa.capital_event_id = p_capital_event_id
AND paa.task_id IN (costs_rec.task_id,costs_rec.top_task_id);
UPDATE pa_expenditure_items_all
SET capital_event_id = p_capital_event_id,
last_update_date = SYSDATE,
last_updated_by = v_user_id,
last_update_login = v_login_id
WHERE expenditure_item_id = costs_rec.expenditure_item_id
AND capital_event_id IS NULL;