The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_asg_select long;
l_kff_select long;
l_rating_select long;
l_pos_grp1_select long;
l_pos_grp2_select long;
l_pos_grd_select long;
l_loc_ddf_select long;
Select effective_date
from ghr_mass_awards
where mass_award_id = p_mass_award_id;
select gdf.segment1 pay_plan,
gdf.segment2 grade_or_level
from per_grades grd,
per_grade_definitions gdf
where grd.grade_id = c_grade_id
and grd.grade_definition_id = gdf.grade_definition_id;
Select ppf.business_group_id
from per_people_f ppf
where ppf.person_id = l_per_id
and l_effective_date
between ppf.effective_start_date
and ppf.effective_end_date;
Select dsf.duty_station_code
from ghr_duty_stations_f dsf
where dsf.duty_station_id =
(select lei.lei_information3
from hr_location_extra_info lei
where lei.location_id = l_loc_id
)
and l_effective_date
between dsf.effective_start_date
and dsf.effective_end_date;
select pos.position_extra_info_id
from per_position_extra_info pos
where pos.position_id = c_position_id
and pos.information_type = 'GHR_US_POS_VALID_GRADE';
p_asg_select out NOCOPY long) is
Cursor c_eff_date is
Select n.code
from ghr_mass_awards m,
ghr_nature_of_actions n
where mass_award_id = p_mass_award_id
and n.nature_of_action_id = m.nature_of_action_id;
select val.relational_operator,
val.value
from ghr_mass_award_criteria_cols col,
ghr_mass_award_criteria_vals val
Where col.table_name = 'ASSIGNMENT'
and col.column_name = 'Organization'
and val.mass_award_id = p_mass_award_id
and val.mass_award_criteria_col_id = col.mass_award_criteria_col_id
order by val.relational_operator;
l_select long;
l_select := ' Select asg1.person_id,asg1.assignment_id,asg1.position_id , asg1.location_id,' ||
' asg1.job_id , asg1.grade_id , org.name ' ||
' from per_assignments_f asg1, ' ||
' hr_organization_units org ' ||
' where ' || 'to_date(' || '''' || l_effective_date || '''' ||
',' || '''' || 'DD-MON-YY' || ''''|| ')' ||
' between asg1.effective_start_date and asg1.effective_end_date ' ||
' and asg1.organization_id = org.organization_id ' ||
' and asg1.assignment_type = ' ||
'''' || 'E' || '''' ||
' and asg1.position_id is not null ' ;
l_select := l_select || ' and (' ;
l_select := l_select || ' and ';
l_select := l_select || ' or ' ;
l_select := l_select ||
'upper(org.name)' || ' ' || l_operator || ' ' ||
'upper(' || l_pre || asg_values.value || l_suf ||
')' ;
l_Select := l_select || ') or 1 = 0 ' ;
l_select := l_select || ' and 1 = 1 ';
l_select := l_select ||' and ghr_pa_requests_pkg.get_personnel_system_indicator ('||
' asg1.position_id,'||
'to_date(' || '''' || l_effective_date || '''' ||
',' || '''' || 'DD-MON-YY' || ''''|| '))'||
'<>'||''''||'00'||'''';
l_select := l_select ||' and ghr_pa_requests_pkg.get_personnel_system_indicator ('||
' asg1.position_id,'||
'to_date(' || '''' || l_effective_date || '''' ||
',' || '''' || 'DD-MON-YY' || ''''|| '))'||
'='||''''||'00'||'''';
p_asg_select := l_select;
p_kff_select out nocopy long,
p_pos_exists out nocopy boolean,
p_job_exists out nocopy boolean
) is
l_select long;
select val.relational_operator,
val.value,
col.table_name,
col.column_name
from ghr_mass_award_criteria_cols col,
ghr_mass_award_criteria_vals val
Where val.mass_award_id = p_mass_award_id
and col.table_name = 'POSITION_KFF'
and val.mass_award_criteria_col_id = col.mass_award_criteria_col_id
union all
Select val.relational_operator,
val.value,
col.table_name,
col.column_name
from ghr_mass_award_criteria_cols col,
ghr_mass_award_criteria_vals val
Where val.mass_award_id = p_mass_award_id
and col.table_name = 'JOB_KFF'
and val.mass_award_criteria_col_id = col.mass_award_criteria_col_id
order by 3,1 ;
l_select := 'Select 1 from dual where ( 1 = 1 ' ;
l_select := l_select || ' ) ' || ' and ( '
|| 'substr(ghr_api.get_position_agency_code_pos('
|| ':pos_id'
|| ',' || ':business_group_id'
|| ',' || ':effective_date'
|| '),1,2 )'
|| l_operator || ' ' || l_pre || pos_kff_values.value || l_suf;
l_select := l_select || ' ) ' || ' and ( '
|| l_col_name || '(:pos_id' || ', ' || ':business_group_id' || ',' || ':effective_date' || ' ) ' || ' '
|| l_operator || ' ' || l_pre || pos_kff_values.value || l_suf;
l_select := l_select || ' )' || ' and ( '
|| l_col_name || '(:job_id' || ', ' || ':business_group_id' || ' ) ' || ' '
|| l_operator || ' ' || l_pre || pos_kff_values.value || l_suf;
l_select := l_select || ' or '
|| 'substr(ghr_api.get_position_agency_code_pos('
|| ':pos_id'
|| ', ' || ':business_group_id'
|| ', '
|| ' :effective_date' ||'),1,2 )'
|| l_operator || ' ' || l_pre || pos_kff_values.value || l_suf ;
l_select := l_select || ' or '
|| l_col_name || '(:pos_id' || ', ' || ':business_group_id' || ',' || ':effective_date' || ' ) ' || ' '
|| l_operator || ' ' || l_pre || pos_kff_values.value || l_suf ;
l_select := l_select || ' or '
|| l_col_name || '(:job_id' || ', ' || ':business_group_id' || ' ) ' || ' '
|| l_operator || ' ' || l_pre || pos_kff_values.value || l_suf ;
l_select := l_select || ' ) ';
p_kff_select := l_select;
p_kff_select := null;
p_pos_grp2_select out nocopy long,
p_poc_exists out nocopy boolean
) is
l_select long;
Select val.relational_operator,
val.value,
col.column_name
from ghr_mass_award_criteria_cols col,
ghr_mass_award_criteria_vals val
Where val.mass_award_id = p_mass_award_id
and col.table_name = 'POSITION_EXTRA_INFO'
and val.mass_award_criteria_col_id = col.mass_award_criteria_col_id
and ( col.column_name = 'Position Occupied'
) order by 3,1;
l_select := 'select 1 from dual where (1 = 1';
l_select := l_select || ' ) and (' ||
':POC' || l_operator || ' ' || l_pre || pos_grp2_rec.value || l_suf ;
l_select := l_select || ' or ' ||
':POC' || l_operator || ' ' || l_pre || pos_grp2_rec.value || l_suf ;
l_select := l_select || ' ) ';
p_pos_grp2_select := l_select;
p_pos_grp2_select := null;
p_pos_grp1_select out nocopy long,
p_poi_exists out nocopy boolean,
p_ofs_exists out nocopy boolean,
p_ors_exists out nocopy boolean
) is
l_select long;
Select val.relational_operator,
val.value,
col.column_name
from ghr_mass_award_criteria_cols col,
ghr_mass_award_criteria_vals val
Where val.mass_award_id = p_mass_award_id
and col.table_name = 'POSITION_EXTRA_INFO'
and val.mass_award_criteria_col_id = col.mass_award_criteria_col_id
and ( col.column_name = 'Personnel Office ID'
or col.column_name = 'Office Symbol'
or col.column_name = 'Organization Structure ID'
) order by 3,1;
l_select := 'select 1 from dual where (1 = 1';
l_select := l_select || ' ) and (' ||
':POI' || l_operator || ' ' || l_pre || pos_grp1_rec.value || l_suf ;
l_select := l_select || ' ) and (' ||
':OFS' || l_operator || ' ' || l_pre || pos_grp1_rec.value || l_suf ;
l_select := l_select || ' ) and (' ||
':ORS' || l_operator || ' ' || l_pre || pos_grp1_rec.value || l_suf ;
l_select := l_select || ' or ' ||
':POI' || l_operator || ' ' || l_pre || pos_grp1_rec.value || l_suf ;
l_select := l_select || ' or ' ||
':OFS' || l_operator || ' ' || l_pre || pos_grp1_rec.value || l_suf ;
l_select := l_select || ' or ' ||
':ORS' || l_operator || ' ' || l_pre || pos_grp1_rec.value || l_suf ;
l_select := l_select || ' ) ';
p_pos_grp1_select := l_select;
p_pos_grp1_select := null;
p_pos_grd_select out nocopy long,
p_pay_plan_exists out nocopy boolean,
p_grade_exists out nocopy boolean
) is
l_select long;
Select val.relational_operator,
val.value,
col.column_name
from ghr_mass_award_criteria_cols col,
ghr_mass_award_criteria_vals val
Where val.mass_award_id = p_mass_award_id
and col.table_name = 'POSITION_EXTRA_INFO'
and val.mass_award_criteria_col_id = col.mass_award_criteria_col_id
and ( col.column_name = 'Pay Plan'
or col.column_name = 'Grade Or Level'
) order by 3,1;
l_select := 'select 1 from dual where (1 = 1';
l_select := l_select || ' ) and (' ||
':PP' || l_operator || ' ' || l_pre || pos_grp1_rec.value || l_suf ;
l_select := l_select || ' ) and (' ||
':GRD' || l_operator || ' ' || l_pre || pos_grp1_rec.value || l_suf ;
l_select := l_select || ' or ' ||
':PP' || l_operator || ' ' || l_pre || pos_grp1_rec.value || l_suf ;
l_select := l_select || ' or ' ||
':GRD' || l_operator || ' ' || l_pre || pos_grp1_rec.value || l_suf ;
l_select := l_select || ' ) ';
p_pos_grd_select := l_select;
p_pos_grd_select := null;
p_loc_ddf_select out nocopy long,
p_lei_exists out nocopy boolean
)
is
l_select long;
Select val.relational_operator,
val.value,
col.column_name
from ghr_mass_award_criteria_cols col,
ghr_mass_award_criteria_vals val
Where val.mass_award_id = p_mass_award_id
and col.table_name = 'LOCATION_EXTRA_INFO'
and val.mass_award_criteria_col_id = col.mass_award_criteria_col_id
order by 3,1;
l_select := 'Select 1 from dual where 1 = 1 ' ;
l_select := l_select || ' and ' ;
l_select := l_select || ' or ' ;
l_select := l_select ||
'upper( ' || ':DSC' || ')' || ' ' || l_operator || ' ' ||
'upper(' || l_pre || loc_ddf_values.value || l_suf ||
')' ;
l_Select := l_select || ' or 1 = 0 ' ;
l_select := l_select || ' and 1 = 1 ';
p_loc_ddf_select := l_select;
p_loc_ddf_select := null;
p_rating_select out nocopy long,
p_rating_exists out nocopy boolean
)
is
l_select long;
Select val.relational_operator,
val.value,
col.column_name
from ghr_mass_award_criteria_cols col,
ghr_mass_award_criteria_vals val
Where val.mass_award_id = p_mass_award_id
and col.table_name = 'PERSON_SIT'
and val.mass_award_criteria_col_id = col.mass_award_criteria_col_id
order by 3,1;
l_select := 'Select 1 from dual where 1 = 1 ' ;
l_select := l_select || ' and ' ;
l_select := l_select || ' or ' ;
l_select := l_select ||
'upper( ' || ':RATING' || ')' || ' ' || l_operator || ' ' ||
'upper(' || l_pre || rating_values.value || l_suf ||
')' ;
l_Select := l_select || ' or 1 = 0 ' ;
l_select := l_select || ' and 1 = 1 ';
p_rating_select := l_select;
p_rating_select := null;
p_asg_select => l_asg_select
);
p_kff_select => l_kff_select,
p_pos_exists => l_pos_exists,
p_job_exists => l_job_exists
);
p_pos_grp1_select => l_pos_grp1_select,
p_poi_exists => l_poi_exists,
p_ofs_exists => l_ofs_exists,
p_ors_exists => l_ors_exists
);
p_pos_grp2_select => l_pos_grp2_select,
p_poc_exists => l_poc_exists
);
p_pos_grd_select => l_pos_grd_select,
p_pay_plan_exists => l_pay_plan_exists,
p_grade_exists => l_grade_exists
);
p_loc_ddf_select => l_loc_ddf_select,
p_lei_exists => l_lei_exists
);
p_rating_select => l_rating_select,
p_rating_exists => l_rating_exists);
DBMS_SQL.PARSE(l_cursor_id,l_asg_select,DBMS_SQL.NATIVE);
DBMS_SQL.PARSE(l_cursor_kff_id,l_kff_select,DBMS_SQL.NATIVE);
DBMS_SQL.PARSE(l_cursor_pos_grp1_id,l_pos_grp1_select,DBMS_SQL.NATIVE);
DBMS_SQL.PARSE(l_cursor_pos_grp2_id,l_pos_grp2_select,DBMS_SQL.NATIVE);
DBMS_SQL.PARSE(l_cursor_pos_grd_id,l_pos_grd_select,DBMS_SQL.NATIVE);
DBMS_SQL.PARSE(l_cursor_loc_ddf_id,l_loc_ddf_select,DBMS_SQL.NATIVE);
DBMS_SQL.PARSE(l_cursor_rating_id,l_rating_select,DBMS_SQL.NATIVE);
Elsif l_status = 'DESELECTED' OR l_status = 'DESELECTED PRG:' then
l_desel_ctr := l_desel_ctr + 1;
'Deselected : ' || to_char(l_desel_ctr) || ' ' || l_new_line ||
'Failure : ' || to_char(l_err_man_ctr) || ' ' || l_new_line ||
'Failure - Retained for Resubmission : ' || to_char(l_err_mass_ctr);