[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Selecting distribution_rule from project all table only if the passed value is null
otherwise storing passed values */
/*
IF (p_distribution_rule IS NULL) THEN
ELSE
l_distribution_rule := p_distribution_rule ;
SELECT proj.distribution_rule,typ.project_type_class_code
INTO l_distribution_rule,l_class_code
FROM pa_project_types_all typ, pa_projects_all proj
WHERE proj.project_id = p_project_id
AND proj.project_type = typ.project_type
AND proj.org_id = typ.org_id; -- bug 7413961 skkoppul : removed NVL function
/* Selecting expenditure org id , type ,organization id , forecast job id and forecast job group
id from project assignments table only if the passed value is null otherwise storing passed
values */
IF (p_expenditure_org_id IS NULL) OR (p_expenditure_type IS NULL ) OR
(p_expenditure_organization_id IS NULL) OR (p_forecast_job_id IS NULL) OR
(p_forecast_job_group_id IS NULL) THEN
IF ( p_calling_mode = 'ROLE') THEN
SELECT NVL(expenditure_org_id,-99), expenditure_organization_id, expenditure_type,
fcst_job_id, fcst_job_group_id
INTO l_expenditure_org_id,l_expenditure_organization_id,l_expenditure_type,
l_forecast_job_id,l_forecast_job_group_id
FROM pa_project_assignments
WHERE project_id = p_project_id
AND assignment_id = p_item_id;
SELECT NVL(resource_org_id,-99),resource_organization_id,
job_id
INTO l_expenditure_org_id,l_expenditure_organization_id,
l_forecast_job_id
FROM pa_resources_denorm
WHERE person_id = p_person_id
AND ( p_rate_calc_date BETWEEN TRUNC(resource_effective_start_date) AND
NVL(TRUNC(Resource_effective_end_date),p_rate_calc_date));
SELECT job_group_id
INTO l_forecast_job_group_id
FROM per_jobs
WHERE job_id = l_forecast_job_id;
SELECT expenditure_type
INTO l_expenditure_type
FROM pa_project_assignments
WHERE project_id = p_project_id
AND assignment_id = p_item_id;
/*LCE Changes : Selecting override organization if any. */
IF g1_debug_mode = 'Y' THEN
PA_DEBUG.g_err_stage := 'RTS13.1 : Selecting override organization if any .';
PA_DEBUG.g_err_stage := 'RTS13.2 : No override ...selecting expenditure organization id ';
SELECT organization_id
INTO l_expenditure_organization_id
FROM PER_ALL_ASSIGNMENTS_F -- Bug 4358495 : per_assignments_f
WHERE person_id = p_person_id
AND primary_flag ='Y'
-- AND assignment_type ='E'
AND assignment_type IN ('E','C') -- Modified for CWK impacts
AND TRUNC (p_rate_calc_date) BETWEEN TRUNC(Effective_start_date)
AND TRUNC(Effective_End_date); /* Removed nvl on effective_end_date
/* Selecting expenditure currency code from project set of books and implementations table
only if the passed value is null otherwise storing passed values */
IF ( p_expenditure_currency_code IS NULL) THEN
SELECT glsb.currency_code
INTO l_expenditure_currency_code
FROM gl_sets_of_books glsb, pa_implementations_all paimp
WHERE glsb.set_of_books_id = paimp.set_of_books_id
AND paimp.org_id = l_expenditure_org_id; -- bug 7413961 skkoppul: removed NVL function
/* Selecting labor cost mult name from tasks table only if the passed value is null and task id
is not null otherwise storing passed values */
IF ( p_task_id IS NOT NULL ) THEN
IF ( p_labor_cost_multi_name IS NULL ) THEN
SELECT labor_cost_multiplier_name
INTO l_labor_cost_mult_name
FROM pa_tasks
WHERE task_id = p_task_id;
/* Selecting project type from project types table only if the
passed value is null otherwise storing passed values */
IF ( p_project_type IS NULL) THEN
SELECT typ.project_type
INTO l_project_type
FROM pa_project_types_all typ, pa_projects_all proj
WHERE proj.project_id = p_project_id
AND proj.project_type = typ.project_type
AND proj.org_id = typ.org_id; -- bug 7413961 skkoppul: removed NVL function
/* Selecting project org id, project currency code, project bill job
group id, employee bill rate schedule id and job bill rate schedule id from project all table
only if the passed value is null otherwise storing passed values */
IF (p_project_org_id IS NULL) OR (p_projfunc_currency_code IS NULL) OR (p_project_bill_job_group_id IS NULL) OR
(p_emp_bill_rate_schedule_id IS NULL) OR (p_job_bill_rate_schedule_id IS NULL) THEN
SELECT NVL(org_id,-99), bill_job_group_id,
emp_bill_rate_schedule_id,job_bill_rate_schedule_id,
labor_schedule_fixed_date,
projfunc_currency_code,
projfunc_bil_rate_date_code, /* Added the following column for MCB2 */
projfunc_bil_rate_type,
projfunc_bil_rate_date,
projfunc_bil_exchange_rate,
projfunc_cost_rate_date,
projfunc_cost_rate_type,
NVL(assign_precedes_task,'1'),/* Added for Asgmt overide */
project_currency_code, /* Added for Org Forecasting */
project_bil_rate_date_code, /* Added for Org Forecasting */
project_bil_rate_type, /* Added for Org Forecasting */
project_bil_rate_date, /* Added for Org Forecasting */
project_bil_exchange_rate, /* Added for Org Forecasting */
project_rate_date, /* Added for Org Forecasting */
project_rate_type, /* Added for Org Forecasting */
labor_schedule_discount, /* Added for Org Forecasting */
labor_bill_rate_org_id, /* Added for Org Forecasting */
labor_std_bill_rate_schdl, /* Added for Org Forecasting */
labor_schedule_fixed_date, /* Added for Org Forecasting */
labor_sch_type /* Added for Org Forecasting */
INTO l_project_org_id,l_project_bill_job_group_id,
l_emp_bill_rate_schedule_id,l_job_bill_rate_schedule_id ,
l_labor_schedule_fixed_date,
l_projfunc_currency_code,
l_projfunc_bil_rate_date_code, /* Added the following columns for MCB2 */
l_projfunc_bil_rate_type,
l_projfunc_bil_rate_date,
l_projfunc_bil_exchange_rate,
l_projfunc_cost_rate_date,
l_projfunc_cost_rate_type,
l_assignment_precedes_task,
l_project_currency_code,
l_project_bil_rate_date_code,
l_project_bil_rate_type,
l_project_bil_rate_date,
l_project_bil_exchange_rate,
l_project_cost_rate_date,
l_project_cost_rate_type,
l_labor_schedule_discount,
l_labor_bill_rate_org_id,
l_labor_std_bill_rate_schedule,
l_labor_schedule_fixed_date,
l_labor_schedule_type
FROM pa_projects_all
WHERE project_id = p_project_id;
/* Selecting project cost job group id,job cost rate schedule id from forecasting options and
pa std billrate table only if the passed value is null otherwise storing passed values */
IF ( p_proj_cost_job_group_id IS NULL) OR ( p_job_cost_rate_schedule_id IS NULL) THEN
SELECT bschal.job_group_id,foptal.job_cost_rate_schedule_id
INTO l_proj_cost_job_grp_id,l_job_cost_rate_schedule_id
FROM pa_std_bill_rate_schedules_all bschal,pa_forecasting_options_all foptal
WHERE bschal.bill_rate_sch_id = foptal.job_cost_rate_schedule_id
/* For Bug 4101595: Reverted the fix done for 3786192 */
/* AND nvl(foptal.org_id, -99) = nvl(l_expenditure_org_id, -99) */ /* Added for 3786192 */
AND foptal.org_id = l_project_org_id; -- bug 7413961 skkoppul: removed NVL function, changed bschal.org_id to foptal.org_id
SELECT labor_schedule_fixed_date,
projfunc_currency_code,
projfunc_bil_rate_date_code, -- Added the following column for MCB2
projfunc_bil_rate_type,
projfunc_bil_rate_date,
projfunc_bil_exchange_rate,
projfunc_cost_rate_date,
projfunc_cost_rate_type,
NVL(assign_precedes_task,'1') -- Added for Asgmt overide
INTO l_labor_schedule_fixed_date,
l_projfunc_currency_code,l_projfunc_bil_rate_date_code, -- Added the following columns for MCB2
l_projfunc_bil_rate_type,l_projfunc_bil_rate_date,l_projfunc_bil_exchange_rate,
l_projfunc_cost_rate_date,l_projfunc_cost_rate_type,
l_assignment_precedes_task
FROM pa_projects_all
WHERE project_id = p_project_id;
select name
into pa_pay_util.g_job_name
from per_jobs_v
where job_id = pa_utils.getempjobid(X_person_id => p_person_id, X_date => p_rate_calc_date);
select location_code
into pa_pay_util.g_location_code
from hr_locations a, per_all_assignments_f b
where b.person_id = p_person_id
and a.location_use = 'HR'
and a.location_id = b.location_id
and p_rate_calc_date between b.effective_start_date and nvl(p_rate_calc_date, b.effective_end_date);
select segment1
into pa_pay_util.g_project_number
from pa_projects
where project_id = p_project_id;
select task_number
into pa_pay_util.g_task_number
from pa_tasks
where task_id = p_task_id;
select name
into pa_pay_util.g_work_type_name
from pa_work_types_vl a, pa_tasks b
where a.work_type_id = b.work_type_id
and b.task_id = p_task_id;
/* Deleted this proc PA_COST.get_projfunc_raw_burdened_cost() for Org Forecasting */
IF g1_debug_mode = 'Y' THEN
PA_DEBUG.g_err_stage := 'RT19 : Leaving PA_COST.get_proj_raw_burdened_cost';
SELECT MAX(end_date)
INTO l_completion_date
FROM pa_schedules
WHERE project_id = p_project_id
AND DECODE (schedule_type_code, 'OPEN_ASSIGNMENT',
pa_assignment_utils.Is_Asgmt_In_Open_Status(status_code,'OPEN_ASGMT'),
'STAFFED_ASSIGNMENT',DECODE(
pa_assignment_utils.Is_Staffed_Asgmt_Cancelled(status_code,'STAFFED_ASGMT'),'Y','N','Y'),'N') = 'Y'
AND DECODE(schedule_type_code,
'OPEN_ASSIGNMENT', pa_project_utils.check_prj_stus_action_allowed(
status_code, 'OPEN_ASGMT_PROJ_FORECASTING'),
'STAFFED_ASSIGNMENT', pa_project_utils.check_prj_stus_action_allowed(
status_code, 'STAFFED_ASGMT_PROJ_FORECASTING'),
'STAFFED_ADMIN_ASSIGNMENT', pa_project_utils.check_prj_stus_action_allowed(
status_code, 'STAFFED_ASGMT_PROJ_FORECASTING'),'N') = 'Y';
SELECT period_name,start_date,end_date
INTO l_period_name,l_start_date,l_end_date
FROM pa_rep_period_dates_v
WHERE period_type = l_period_type
AND l_completion_date BETWEEN start_date AND end_date;
/* Selecting distribution rule for checking wheather the project is Fixed Price or T and M */
BEGIN
SELECT proj.distribution_rule,typ.project_type_class_code
INTO l_rule,l_class_code
FROM pa_project_types_all typ, pa_projects_all proj
WHERE proj.project_id = p_project_id
AND proj.project_type = typ.project_type
AND proj.org_id = typ.org_id; -- bug 7413961 skkoppul : removed NVL function
/* Selecting expenditure org id , type ,organization id , forecast job id and forecast job group
id from project assignments table only if the passed value is null otherwise storing passed
values */
IF (p_assignment_type = 'R') THEN
IF (p_forecast_job_id IS NULL) OR (p_forecast_job_group_id IS NULL) THEN
RAISE l_job_not_found;
SELECT job_id
INTO l_forecast_job_id
FROM pa_resources_denorm
WHERE person_id = p_person_id
AND ( p_asgn_start_date BETWEEN TRUNC(resource_effective_start_date) AND
NVL(TRUNC(resource_effective_end_date),p_asgn_start_date));
/* Selecting distribution rule for calculation rate */
BEGIN
SELECT proj.distribution_rule,typ.project_type_class_code,proj.labor_sch_type
INTO l_distribution_rule,l_class_code,l_labor_schedule_type
FROM pa_project_types_all typ, pa_projects_all proj
WHERE proj.project_id = p_project_id
AND proj.project_type = typ.project_type
AND proj.org_id = typ.org_id; -- bug 7413961 skkoppul : removed NVL function
/* Selecting expenditure currency code from project set of books and implementations table
only if the passed value is null otherwise storing passed values */
IF ( p_expenditure_currency_code IS NULL) THEN
BEGIN
SELECT glsb.currency_code
INTO l_expenditure_currency_code
FROM gl_sets_of_books glsb, pa_implementations_all paimp
WHERE glsb.set_of_books_id = paimp.set_of_books_id
AND paimp.org_id = p_expenditure_org_id; -- bug 7413961 skkoppul: removed NVL function
/* Selecting labor cost mult name from tasks table only if the passed value is null and task id
is not null otherwise storing passed values */
IF ( p_task_id IS NOT NULL ) THEN
BEGIN
SELECT labor_cost_multiplier_name
INTO l_labor_cost_mult_name
FROM pa_tasks
WHERE task_id = p_task_id;
/* Selecting project type from project types table only if the
passed value is null otherwise storing passed values */
IF ( p_project_type IS NULL) THEN
SELECT typ.project_type
INTO l_project_type
FROM pa_project_types_all typ, pa_projects_all proj
WHERE proj.project_id = p_project_id
AND proj.project_type = typ.project_type
AND proj.org_id = typ.org_id; -- bug 7413961 skkoppul: removed NVL function
/* Selecting project org id, project currency code, project bill job
group id, employee bill rate schedule id and job bill rate schedule id from project all table
only if the passed value is null otherwise storing passed values */
IF (p_project_org_id IS NULL) OR (p_project_bill_job_group_id IS NULL) OR
(p_emp_bill_rate_schedule_id IS NULL) OR (p_job_bill_rate_schedule_id IS NULL)
OR (p_labor_schedule_fixed_date IS NULL) THEN
BEGIN
SELECT NVL(org_id,-99), bill_job_group_id,
emp_bill_rate_schedule_id,job_bill_rate_schedule_id,
labor_schedule_fixed_date,
projfunc_currency_code,
projfunc_bil_rate_date_code, /* Added the following column for MCB2 */
projfunc_bil_rate_type,
projfunc_bil_rate_date,
projfunc_bil_exchange_rate,
projfunc_cost_rate_date,
projfunc_cost_rate_type,
NVL(assign_precedes_task,'1'),/* Added for Asgmt overide */
project_currency_code, /* Added for Org Forecasting */
project_bil_rate_date_code, /* Added for Org Forecasting */
project_bil_rate_type, /* Added for Org Forecasting */
project_bil_rate_date, /* Added for Org Forecasting */
project_bil_exchange_rate, /* Added for Org Forecasting */
project_rate_date, /* Added for Org Forecasting */
project_rate_type, /* Added for Org Forecasting */
labor_schedule_discount, /* Added for Org Forecasting */
labor_bill_rate_org_id, /* Added for Org Forecasting */
labor_std_bill_rate_schdl, /* Added for Org Forecasting */
labor_schedule_fixed_date, /* Added for Org Forecasting */
labor_sch_type /* Added for Org Forecasting */
INTO l_project_org_id,l_project_bill_job_group_id,
l_emp_bill_rate_schedule_id,l_job_bill_rate_schedule_id ,
l_labor_schedule_fixed_date,
l_projfunc_currency_code,
l_projfunc_bil_rate_date_code, /* Added the following columns for MCB2 */
l_projfunc_bil_rate_type,
l_projfunc_bil_rate_date,
l_projfunc_bil_exchange_rate,
l_projfunc_cost_rate_date,
l_projfunc_cost_rate_type,
l_assignment_precedes_task,
l_project_currency_code,
l_project_bil_rate_date_code,
l_project_bil_rate_type,
l_project_bil_rate_date,
l_project_bil_exchange_rate,
l_project_cost_rate_date,
l_project_cost_rate_type,
l_labor_schedule_discount,
l_labor_bill_rate_org_id,
l_labor_std_bill_rate_schedule,
l_labor_schedule_fixed_date,
l_labor_schedule_type
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT default_assign_exp_type_class
INTO l_system_linkage
FROM pa_forecasting_options_all
WHERE NVL(org_id,-99) = nvl(l_project_org_id,-99);
/* Selecting project cost job group id,job cost rate schedule id from forecasting options and
pa std billrate table only if the passed value is null otherwise storing passed values */
IF ( p_proj_cost_job_group_id IS NULL) OR ( p_job_cost_rate_schedule_id IS NULL) THEN
SELECT bschal.job_group_id,foptal.job_cost_rate_schedule_id
INTO l_proj_cost_job_grp_id,l_job_cost_rate_schedule_id
FROM pa_std_bill_rate_schedules_all bschal,pa_forecasting_options_all foptal
WHERE bschal.bill_rate_sch_id = foptal.job_cost_rate_schedule_id
/* For bug 4101595: Reverted the fix done for bug 3786192 */
/* AND nvl(foptal.org_id, -99) = nvl(p_expenditure_org_id, -99) */ /* Added for 3786192 */
AND bschal.org_id = l_project_org_id; -- bug 7413961 skkoppul: removed NVL function
SELECT labor_schedule_fixed_date,
projfunc_currency_code,projfunc_bil_rate_date_code, -- Added the following column for MCB2
projfunc_bil_rate_type,projfunc_bil_rate_date,projfunc_bil_exchange_rate,
projfunc_cost_rate_date,projfunc_cost_rate_type,
NVL(assign_precedes_task,'1'), -- Added for Asgmt overide
labor_sch_type
INTO l_labor_schedule_fixed_date,
l_projfunc_currency_code,l_projfunc_bil_rate_date_code, -- Added the following columns for MCB2
l_projfunc_bil_rate_type,l_projfunc_bil_rate_date,l_projfunc_bil_exchange_rate,
l_projfunc_cost_rate_date,l_projfunc_cost_rate_type,
l_assignment_precedes_task,
l_labor_schedule_type
FROM pa_projects_all
WHERE project_id = p_project_id;
select name
into pa_pay_util.g_job_name
from per_jobs_v
where job_id = pa_utils.getempjobid(X_person_id => p_person_id, X_date => p_asgn_start_date);
select location_code
into pa_pay_util.g_location_code
from hr_locations a, per_all_assignments_f b
where b.person_id = p_person_id
and a.location_use = 'HR'
and a.location_id = b.location_id
and p_asgn_start_date between b.effective_start_date and nvl(p_asgn_start_date, b.effective_end_date);
select name
into pa_pay_util.g_job_name
from per_jobs_v
where job_id = p_forecast_job_id;
select segment1
into pa_pay_util.g_project_number
from pa_projects
where project_id = p_project_id;
select task_number
into pa_pay_util.g_task_number
from pa_tasks
where task_id = p_task_id;
select name
into pa_pay_util.g_work_type_name
from pa_work_types_vl a, pa_tasks b
where a.work_type_id = b.work_type_id
and b.task_id = p_task_id;
/* Deleted this proc PA_COST.get_projfunc_raw_burdened_cost() for Org Forecasting */
END IF; /* end of calculate cost flag */
/* If the passed table does not have the specific index then inserting null at that possition
so that other part of code should not execute for Org Forecasting */
IF (p_rate_calc_date_tab.EXISTS(l_j)) THEN
l_rate_calc_date_tab(l_j) := p_rate_calc_date_tab(l_j);
PA_DEBUG.g_err_stage := 'RT 102 : get_rep_period_dates-> Inside GL select prd typ '||p_period_type;
SELECT
glper.period_year,
glper.period_name,
glper.start_date,
glper.end_date
INTO
l_period_year,
l_period_name,
l_start_date,
l_end_date
FROM pa_implementations imp,
gl_sets_of_books gl,
gl_periods glper,
gl_period_statuses glpersts
WHERE 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 glpersts.set_of_books_id = gl.set_of_books_id
AND glpersts.period_type = glper.period_type
AND glpersts.period_name = glper.period_name
AND glpersts.period_year = glper.period_year
AND glpersts.application_id = PA_Period_Process_Pkg.Application_ID
AND p_completion_date BETWEEN glper.start_date AND glper.end_date
AND EXISTS ( SELECT NULL
FROM gl_date_period_map glmaps
WHERE glmaps.period_type = glper.period_type
AND glmaps.period_name = glper.period_name
AND glmaps.period_set_name = glper.period_set_name )
AND EXISTS ( SELECT NULL
FROM gl_lookups prsts
WHERE prsts.lookup_code IN('C','F','N','O','P')
AND prsts.lookup_type ='CLOSING_STATUS'
AND glpersts.closing_status = prsts.lookup_code);
PA_DEBUG.g_err_stage := 'RT 103 : get_rep_period_dates-> Passed GL select prd nam '||l_period_name;
PA_DEBUG.g_err_stage := 'RT 104 : get_rep_period_dates-> Inside PA select prd typ '||p_period_type;
SELECT
glp.period_year
, pap.period_name
, pap.start_date
, pap.end_date
INTO
l_period_year,
l_period_name,
l_start_date,
l_end_date
FROM pa_periods pap,
gl_period_statuses glp,
pa_implementations paimp
WHERE pap.gl_period_name = glp.period_name
AND glp.set_of_books_id = paimp.set_of_books_id
AND glp.application_id = Pa_Period_Process_Pkg.Application_id
AND glp.adjustment_period_flag = 'N'
AND p_completion_date BETWEEN pap.start_date and pap.end_date
AND EXISTS (SELECT NULL
FROM pa_lookups pal
WHERE pal.lookup_type = 'CLOSING STATUS'
AND pal.lookup_code = pap.status);
PA_DEBUG.g_err_stage := 'RT 104 : get_rep_period_dates-> Passed PA select prd nam '||l_period_name;
PA_DEBUG.g_err_stage := 'RT 105 : get_rep_period_dates-> Inside QR select prd typ '||p_period_type;
SELECT
period_yr
, period_nam
, start_dt
, end_dt
INTO
l_period_year,
l_period_name,
l_start_date,
l_end_date
FROM (
SELECT
glper.period_year period_yr,
TO_CHAR(glper.quarter_num) period_nam,
MIN(glper.start_date) start_dt,
MAX(glper.end_date) end_dt
FROM pa_implementations imp,
gl_sets_of_books gl,
gl_periods glper,
gl_period_statuses glpersts,
gl_date_period_map glmaps
WHERE 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 glpersts.set_of_books_id = gl.set_of_books_id
AND glpersts.period_type = glper.period_type
AND glpersts.period_name = glper.period_name
AND glpersts.period_year = glper.period_year
AND glmaps.period_type = glper.period_type
AND glmaps.period_name = glper.period_name
AND glmaps.period_set_name = glper.period_set_name
AND glpersts.application_id = PA_Period_Process_Pkg.Application_ID
AND EXISTS (SELECT null
FROM gl_lookups prsts
WHERE prsts.lookup_code IN('C','F','N','O','P')
AND prsts.lookup_type ='CLOSING_STATUS'
AND glpersts.closing_status = prsts.lookup_code)
GROUP BY glper.period_year,
glper.quarter_num
)
WHERE p_completion_date BETWEEN start_dt AND end_dt;
PA_DEBUG.g_err_stage := 'RT 106 : get_rep_period_dates-> Passed QR select prd nam '||l_period_name;
PA_DEBUG.g_err_stage := 'RT 107 : get_rep_period_dates-> Inside YR select prd typ '||p_period_type;
SELECT
period_yr
, period_nam
, start_dt
, end_dt
INTO
l_period_year,
l_period_name,
l_start_date,
l_end_date
FROM (
SELECT
glper.period_year period_yr,
TO_CHAR(glper.period_year) period_nam,
MIN(glper.start_date) start_dt,
MAX(glper.end_date) end_dt
FROM pa_implementations imp,
gl_sets_of_books gl,
gl_periods glper,
gl_period_statuses glpersts,
gl_date_period_map glmaps
WHERE 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 glpersts.set_of_books_id = gl.set_of_books_id
AND glpersts.period_type = glper.period_type
AND glpersts.period_name = glper.period_name
AND glpersts.period_year = glper.period_year
AND glmaps.period_type = glper.period_type
AND glmaps.period_name = glper.period_name
AND glmaps.period_set_name = glper.period_set_name
AND glpersts.application_id = PA_Period_Process_Pkg.Application_ID
AND EXISTS (SELECT null
FROM gl_lookups prsts
WHERE prsts.lookup_code IN('C','F','N','O','P')
AND prsts.lookup_type ='CLOSING_STATUS'
AND glpersts.closing_status = prsts.lookup_code)
GROUP BY glper.period_year
)
WHERE p_completion_date BETWEEN start_dt AND end_dt;
PA_DEBUG.g_err_stage := 'RT 108 : get_rep_period_dates-> Passed YR select prd nam '||l_period_name;
PA_DEBUG.g_err_stage := 'RT 109 : get_rep_period_dates-> Inside GE select prd typ '||p_period_type;
SELECT
period_yr
, period_nam
, start_dt
, end_dt
INTO
l_period_year,
l_period_name,
l_start_date,
l_end_date
FROM (
SELECT
period_year period_yr,
TO_CHAR((NEXT_DAY(TO_DATE('01/01/'||TO_CHAR(period_Year),'MM/DD/YYYY'),
TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY'))) -1 ) + (seq_number-1) * 7) period_nam,
( ((NEXT_DAY(TO_DATE('01/01/'||TO_CHAR(period_Year),'MM/DD/YYYY'),
TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY'))) -1 ) + (seq_number-1) * 7) - 6 ) start_dt,
( (NEXT_DAY(TO_DATE('01/01/'||TO_CHAR(period_Year),'MM/DD/YYYY'),
TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY'))) -1 ) + (seq_number-1) * 7 ) end_dt
FROM pa_rep_year_cal_v,
pa_rep_seq_number
WHERE seq_number BETWEEN 1 AND 53
)
WHERE p_completion_date BETWEEN start_dt AND end_dt;
PA_DEBUG.g_err_stage := 'RT 110 : get_rep_period_dates-> Passed GE select prd nam '||l_period_name;