[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pa_forecast_items_s.NEXTVAL
INTO li_forecast_item_id
FROM DUAL;
CURSOR cur_res_asgn IS SELECT sch.schedule_id schedule_id,
sch.monday_hours monday_hours,
sch.tuesday_hours tuesday_hours,
sch.wednesday_hours wednesday_hours,
sch.thursday_hours thursday_hours,
sch.friday_hours friday_hours,
sch.saturday_hours saturday_hours,
sch.sunday_hours sunday_hours,
sch.status_code status_code,
sch.start_date start_date,
sch.end_date end_date,
sch.forecast_txn_version_number forecast_txn_version_number,
sch.forecast_txn_generated_flag forecast_txn_generated_flag,
pst.project_system_status_code system_status_code
FROM pa_schedules sch,
pa_project_assignments prasgn,
pa_project_statuses pst
WHERE ( ( p_start_date BETWEEN sch.start_date AND sch.end_date)
OR ( p_end_date BETWEEN sch.start_date AND sch.end_date)
OR ( p_start_date < sch.start_date AND p_end_date > sch.end_date) )
AND sch.assignment_id = prasgn.assignment_id
AND prasgn.resource_id = p_resource_id
AND sch.status_code = pst.project_status_code
AND pst.project_system_status_code <> 'STAFFED_ASGMT_CANCEL'
AND PST.STATUS_TYPE = 'STAFFED_ASGMT' --Bug 7301626
ORDER BY sch.start_date;
CURSOR cur_asgn_sch IS SELECT sch.schedule_id schedule_id,
sch.monday_hours monday_hours,
sch.tuesday_hours tuesday_hours,
sch.wednesday_hours wednesday_hours,
sch.thursday_hours thursday_hours,
sch.friday_hours friday_hours,
sch.saturday_hours saturday_hours,
sch.sunday_hours sunday_hours,
sch.status_code status_code,
sch.start_date start_date,
sch.end_date end_date,
sch.forecast_txn_version_number forecast_txn_version_number,
sch.forecast_txn_generated_flag forecast_txn_generated_flag,
pst.project_system_status_code system_status_code
FROM pa_schedules sch, pa_project_statuses pst
WHERE p_start_date IS NOT NULL
AND sch.status_code = pst.project_status_code
AND p_end_date IS NOT NULL
AND sch.assignment_id=p_assignment_id
/** commented out as the FIs were not generated between the schedules
* when two or more wf - process launched concurrently
--AND sch.forecast_txn_generated_flag=
-- DECODE(p_process_mode,'GENERATE','N',sch.forecast_txn_generated_flag)
**/
AND ( ( p_start_date BETWEEN sch.start_date AND sch.end_date)
OR ( p_end_date BETWEEN sch.start_date AND sch.end_date)
OR ( p_start_date < sch.start_date AND p_end_date > sch.end_date) )
UNION
SELECT sch.schedule_id schedule_id,
sch.monday_hours monday_hours,
sch.tuesday_hours tuesday_hours,
sch.wednesday_hours wednesday_hours,
sch.thursday_hours thursday_hours,
sch.friday_hours friday_hours,
sch.saturday_hours saturday_hours,
sch.sunday_hours sunday_hours,
sch.status_code status_code,
sch.start_date start_date,
sch.end_date end_date,
sch.forecast_txn_version_number forecast_txn_version_number,
sch.forecast_txn_generated_flag forecast_txn_generated_flag,
pst.project_system_status_code system_status_code
FROM pa_schedules sch, pa_project_statuses pst
WHERE p_start_date IS NULL
AND p_end_date IS NULL
AND sch.assignment_id=p_assignment_id
AND sch.status_code = pst.project_status_code
/** commented out as the FIs were not generated between the schedules
* when two or more wf - process launched concurrently
--AND sch.forecast_txn_generated_flag=
-- DECODE(p_process_mode,'GENERATE','N',sch.forecast_txn_generated_flag)
**/
ORDER BY start_date;
SELECT gl.period_set_name
INTO lv_period_set_name
FROM gl_sets_of_books gl,
pa_implementations_all imp
WHERE imp.set_of_books_id=gl.set_of_books_id
AND nvl(imp.org_id,-99) = nvl(p_org_id,-99);
SELECT imp.period_set_name
INTO lv_period_set_name
FROM pa_implementations_all imp
WHERE imp.org_id = p_org_id;
SELECT wk.billable_capitalizable_flag,
wk.res_utilization_percentage,
wk.org_utilization_percentage,
wk.res_util_category_id,
wk.org_util_category_id,
wk.reduce_capacity_flag
INTO x_BillableFlag,
x_ResUtilPercentage,
x_OrgUtilPercentage,
x_ResUtilCategoryID,
x_OrgUtilCategoryID,
x_ReduceCapacityFlag
FROM pa_work_types_b wk
WHERE wk.work_type_id=p_work_type_id;
SELECT glper.start_date,
glper.end_date,
glper.period_name
BULK COLLECT INTO x_StartDateTab,x_EndDateTab,x_PAPeriodNameTab
FROM pa_implementations_all imp,
gl_sets_of_books gl,
gl_periods glper,
gl_date_period_map glmaps
WHERE nvl(imp.org_id,-99) = nvl(p_org_id,-99)
AND imp.set_of_books_id = gl.set_of_books_id
AND gl.period_set_name = glper.period_set_name
AND imp.pa_period_type = glper.period_type
AND glmaps.period_type = glper.period_type
AND glmaps.period_name = glper.period_name
AND glmaps.period_set_name = glper.period_set_name
AND ( (p_start_date BETWEEN glper.start_date AND glper.end_date)
OR (p_end_date BETWEEN glper.start_date AND glper.end_date)
OR ( p_start_date < glper.start_date AND
p_end_date > glper.end_date ))
order by glper.start_date;
SELECT pp.start_date,
pp.end_date,
pp.period_name
BULK COLLECT
INTO x_StartDateTab,x_EndDateTab,x_PAPeriodNameTab
FROM pa_periods_all pp
WHERE pp.org_id = p_org_id
--Bug 4276273 - trunc added
AND ( (trunc(p_start_date) BETWEEN pp.start_date AND
pp.end_date)
OR (trunc(p_end_date) BETWEEN pp.start_date AND pp.end_date)
OR ( trunc(p_start_date) < pp.start_date AND
trunc(p_end_date) > pp.end_date ))
order by pp.start_date;
SELECT glper.start_date,
glper.end_date,
glper.period_name
BULK COLLECT
INTO x_StartDateTab,x_EndDateTab,x_PAPeriodNameTab
FROM pa_implementations_all imp,
gl_sets_of_books gl,
gl_periods glper,
gl_date_period_map glmaps
WHERE nvl(imp.org_id,-99) = nvl(p_org_id,-99)
AND imp.set_of_books_id = gl.set_of_books_id
AND gl.period_set_name = glper.period_set_name
AND imp.pa_period_type = glper.period_type
AND glmaps.period_type = glper.period_type
AND glmaps.period_name = glper.period_name
AND glmaps.period_set_name = glper.period_set_name
AND p_start_date BETWEEN glper.start_date AND glper.end_date
order by glper.end_date;
SELECT pp.start_date,
pp.end_date,
pp.period_name
BULK COLLECT
INTO x_StartDateTab,x_EndDateTab,x_PAPeriodNameTab
FROM pa_periods_all pp
WHERE pp.org_id = p_org_id
--Bug 4276273 - trunc added
AND trunc(p_start_date) BETWEEN pp.start_date and pp.end_date
order by pp.end_date;
x_StartDateTab.delete;
x_EndDateTab.delete;
x_PAPeriodNameTab.delete ;
SELECT glper.start_date,
glper.end_date,
glper.period_name
BULK COLLECT
INTO x_StartDateTab,x_EndDateTab,x_PAPeriodNameTab
FROM pa_implementations_all imp,
gl_sets_of_books gl,
gl_periods glper,
gl_date_period_map glmaps
WHERE imp.org_id = p_org_id
AND imp.set_of_books_id = gl.set_of_books_id
AND gl.period_set_name = glper.period_set_name
AND gl.accounted_period_type = glper.period_type
AND glmaps.period_type = glper.period_type
AND glmaps.period_name = glper.period_name
AND glmaps.period_set_name = glper.period_set_name
--Bug 4276273 - trunc added
AND ( (trunc(p_start_date) BETWEEN glper.start_date AND
glper.end_date)
OR (trunc(p_end_date) BETWEEN glper.start_date AND
glper.end_date)
OR ( trunc(p_start_date) < glper.start_date AND
trunc(p_end_date) > glper.end_date ))
order by glper.start_date;
SELECT glper.start_date,
glper.end_date,
glper.period_name
BULK COLLECT
INTO x_StartDateTab,x_EndDateTab,x_PAPeriodNameTab
FROM pa_implementations_all imp,
gl_sets_of_books gl,
gl_periods glper,
gl_date_period_map glmaps
WHERE imp.org_id = p_org_id
AND imp.set_of_books_id = gl.set_of_books_id
AND gl.period_set_name = glper.period_set_name
AND gl.accounted_period_type = glper.period_type
AND glmaps.period_type = glper.period_type
AND glmaps.period_name = glper.period_name
AND glmaps.period_set_name = glper.period_set_name
--Bug 4276273 - trunc added
AND trunc(p_start_date) BETWEEN glper.start_date AND
glper.end_date
order by glper.start_date;
x_StartDateTab.delete;
x_EndDateTab.delete;
x_PAPeriodNameTab.delete ;
SELECT nvl(rou.resource_org_id,-99),
rou.resource_effective_start_date,
NVL(rou.resource_effective_end_date,SYSDATE)
BULK COLLECT INTO
x_ResourceOUTab,x_StartDateTab,x_EndDateTab
FROM pa_resources_denorm rou
WHERE rou.resource_id= p_resource_id
AND ld_start_date <= NVL(rou.resource_effective_end_date,SYSDATE)
AND ld_end_date >= rou.resource_effective_start_date
ORDER BY rou.resource_effective_start_date;
x_ResourceOUTab.delete;
x_StartDateTab.delete;
x_EndDateTab.delete;
select nvl(RESOURCE_ORGANIZATION_ID, -99) resource_Organization_id,
RESOURCE_EFFECTIVE_START_DATE effective_start_date,
RESOURCE_EFFECTIVE_END_DATE effective_end_date,
job_id job_id
BULK COLLECT INTO
x_ResourceOrganizationIDTab,
x_StartDateTab,
x_EndDateTab,
x_ResourceJobIDTab
from pa_resources_denorm rou
where person_id = p_person_id
AND ld_start_date <= NVL(rou.resource_effective_end_date,SYSDATE)
AND ld_end_date >= rou.resource_effective_start_date
ORDER BY rou.resource_effective_start_date;
x_ResourceOrganizationIDTab.delete;
x_ResourceJobIDTab.delete;
x_StartDateTab.delete;
x_EndDateTab.delete;
SELECT person_id
INTO li_person_id
FROM pa_resource_txn_attributes
WHERE resource_id = p_resource_id
AND rownum = 1; --Bug 3086960. Adde by Sachin.
SELECT resource_id
INTO li_resource_id
FROM pa_resource_txn_attributes
WHERE person_id = p_person_id;
SELECT typ.RESOURCE_TYPE_CODE
INTO lv_resource_type
FROM pa_resource_types typ,
pa_resources res
WHERE res.resource_type_id= typ.resource_type_id
AND res.resource_id= p_resource_id;
SELECT -- include_admin_proj_flag, Bug 4576715
bill_unassign_proj_id,
bill_unassign_exp_type_class,bill_unassign_exp_type,
nonbill_unassign_proj_id,nonbill_unassign_exp_typ_cls,
nonbill_unassign_exp_type,default_tp_amount_type,
util_calc_method
INTO -- x_include_admin_proj_flag, Bug 4576715
x_bill_unassign_proj_id,
x_bill_unassign_exp_type_class,x_bill_unassign_exp_type,
x_nonbill_unassign_proj_id,x_nonbill_unassign_exp_typ_cls,
x_nonbill_unassign_exp_type,x_default_tp_amount_type,
x_util_cal_method
FROM pa_forecasting_options_all
WHERE org_id = p_org_id;
/*Commenting below for Bug 7012687 : not use Select from dual to evaluate expression*/
/* Added for Bug 5549814*/
/* Utilizing the fact that 01-01-1950 was a Sunday and PA lookups value for a Sunday is 1 */
--Select (trim(to_char((to_date('01-01-1950','dd-mm-yyyy')+(l_week_starting_day_index - 1)),'DAY')))
--into l_week_starting_day
--from dual;
/*Commenting below for Bug 7012687 : not use Select from dual to evaluate expression*/
--SELECT (NEXT_DAY(l_week_starting_date,l_week_starting_day)-1) /*Bug 5549814 - Changed l_week_starting_day_index to l_week_starting_day*/
--INTO l_week_ending_date
--FROM dual;
SELECT lookup_code, meaning
INTO x_tp_amount_type_code,
x_tp_amount_type_desc
FROM pa_lookups
WHERE lookup_type = 'TP_AMOUNT_TYPE'
AND lookup_code = p_tp_amount_type_code;
SELECT lookup_code
INTO x_tp_amount_type_code
FROM pa_lookups
WHERE lookup_type = 'TP_AMOUNT_TYPE'
AND meaning = p_tp_amount_type_desc;
SELECT work_type_id,
org_id,
carrying_out_organization_id
INTO x_work_type_id,
x_org_id,
x_carrying_out_organization_id
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT default_assign_exp_type,
default_assign_exp_type_class
INTO x_default_assign_exp_type,
x_default_assign_exp_type_cls
FROM pa_forecasting_options_all
WHERE org_id = x_org_id;
SELECT b.job_group_id,
a.default_job_id
INTO x_default_job_group_id,
x_default_job_id
FROM (select project_role_id,
pa_role_job_bg_utils.get_job_id(project_role_id) default_job_id
from pa_project_role_types_b
where role_party_class = 'PERSON'
and project_role_id = p_project_role_id) a,
per_jobs b
WHERE b.job_id = a.default_job_id;