[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM ghr_cpdf_temp
WHERE report_type = 'STATUS'
AND session_id = userenv('SESSIONID');
SELECT per.hire_date
FROM per_people_v per
WHERE per.person_id = p_person_id; */
SELECT
DECODE(PER.CURRENT_EMPLOYEE_FLAG, 'Y', PPS.DATE_START, DECODE(PER.CURRENT_NPW_FLAG, 'Y', PPP.DATE_START, NULL)) hire_date
FROM
per_all_people PER ,
PER_PERIODS_OF_SERVICE PPS ,
PER_PERIODS_OF_PLACEMENT PPP
WHERE
PPS.PERSON_ID (+) = PER.PERSON_ID AND
PPP.PERSON_ID (+) = PER.PERSON_ID AND
PER.PERSON_ID = p_person_id AND
(
(PER.EMPLOYEE_NUMBER IS NULL) OR
(PER.EMPLOYEE_NUMBER IS NOT NULL AND
PPS.DATE_START = (SELECT MAX(PPS1.DATE_START) FROM PER_PERIODS_OF_SERVICE PPS1 WHERE PPS1.PERSON_ID = PER.PERSON_ID AND
PPS1.DATE_START <= PER.EFFECTIVE_END_DATE))) AND
((PER.NPW_NUMBER IS NULL) OR (PER.NPW_NUMBER IS NOT NULL AND
PPP.DATE_START = (SELECT MAX(PPP1.DATE_START) FROM PER_PERIODS_OF_PLACEMENT PPP1 WHERE PPP1.PERSON_ID = PER.PERSON_ID AND
PPP1.DATE_START <= PER.EFFECTIVE_END_DATE)));
SELECT SEX,
DATE_OF_BIRTH,
NATIONAL_IDENTIFIER,
EMPLOYEE_NUMBER,
EMAIL_ADDRESS
FROM per_all_people
WHERE (TRUNC(p_sr_report_date) between effective_start_date
and effective_end_date)
AND PERSON_ID = g_person_id;
SELECT pho.phone_number, pho.phone_type
FROM per_phones pho
WHERE pho.parent_table = 'PER_ALL_PEOPLE_F'
AND pho.parent_id = g_person_id
AND pho.phone_type IN ('W1','H1','M');
SELECT contact.last_name, contact.first_name, contact.middle_names, contact.suffix
,pho.phone_number, rel.date_start
FROM per_contact_relationships_v2 rel
,per_all_people contact
,per_phones pho
WHERE rel.person_id = g_person_id
AND contact_type in ('EMRG','EC')
AND rel.contact_person_id = contact.person_id
AND p_sr_report_date between contact.effective_start_date AND contact.effective_end_date
AND p_sr_report_date BETWEEN NVL(rel.date_start,p_sr_report_date) AND NVL(rel.date_end,p_sr_report_date)
AND pho.parent_id(+) = contact.person_id
AND p_sr_report_date BETWEEN NVL(pho.date_from,p_sr_report_date) AND NVL(pho.date_to,p_sr_report_date)
AND ((pho.phone_type = 'M'
AND NOT EXISTS (SELECT 1
FROM per_phones pho2
WHERE pho2.parent_id = pho.parent_id
AND pho2.phone_type IN ('H1','W1')
)
)
OR (pho.phone_type = 'W1'
AND NOT EXISTS (SELECT 1
FROM per_phones pho2
WHERE pho2.parent_id = pho.parent_id
AND pho2.phone_type = 'H1'
)
)
OR (pho.phone_type = 'H1')
OR (pho.phone_type IS NULL)
)
ORDER BY DECODE(rel.primary_contact_flag,'P',0,rel.sequence_number);
SELECT per.employee_number
FROM per_all_people per
WHERE per.person_id = p_sr_person_id
AND NVL(p_sr_report_date, TRUNC(sysdate)) BETWEEN per.effective_start_date
AND per.effective_end_date;
select flx.id_flex_num
from fnd_id_flex_structures_tl flx
where flx.id_flex_code = 'PEA' --
and flx.application_id = 800 --
and flx.id_flex_structure_name = l_flex_struct_name
and flx.language = 'US';
select pea.analysis_criteria_id,
pan.date_from, -- added for bug fix : 609285
pan.person_analysis_id,
pan.object_version_number,
pea.start_date_active,
pea.segment1,
pea.segment2,
pea.segment3,
pea.segment4,
pea.segment5,
pea.segment6,
pea.segment7
from per_analysis_criteria pea,
per_person_analyses pan
where pan.person_id = g_person_id
and pan.id_flex_num = l_id_flex_num
and pea.analysis_criteria_id = pan.analysis_criteria_id
and p_sr_report_date
between nvl(pan.date_from,p_sr_report_date)
and nvl(pan.date_to,p_sr_report_date)
and p_sr_report_date
between nvl(pea.start_date_active,p_sr_report_date)
and nvl(pea.end_date_active,p_sr_report_date)
order by 1 asc;
select pan.date_from,
pan.analysis_criteria_id,
pac.segment3,
pac.segment4,
pac.segment5,
pac.segment6,
pac.segment17
from per_person_analyses pan,
per_analysis_Criteria pac
where pan.person_id = g_person_id
and pan.id_flex_num = l_id_flex_num
and pan.analysis_criteria_id = pac.analysis_criteria_id
and p_sr_report_date
between nvl(pan.date_from,p_sr_report_date)
and nvl(pan.date_to,p_sr_report_date)
and p_sr_report_date
between nvl(pac.start_date_active,p_sr_report_date)
and nvl(pac.end_date_active,p_sr_report_date)
order by pan.date_from desc, pan.analysis_criteria_id desc; -- Latest From Date, Most Recent Record.
select pei.person_extra_info_id
from per_people_extra_info pei
where pei.person_id = p_sr_person_id
and pei.information_type = 'GHR_US_PER_SERVICE_OBLIGATION'
order by pei.person_extra_info_id;
select substr(user_table_name,1,4) user_table_name
from pay_user_tables
where user_table_id = p_user_table_id;
select substr(user_table_name,1,4)
into l_to_pay_table_id
from pay_user_tables
where user_table_id = l_POSIEI_DATA.POEI_INFORMATION5;
SELECT SEGMENT1,
SEGMENT2
FROM PER_GRADE_DEFINITIONS
WHERE GRADE_DEFINITION_ID =
(SELECT MAX(GRADE_DEFINITION_ID)
FROM PER_GRADES
WHERE GRADE_ID = g_grade_id);
SELECT SEGMENT1
FROM PER_JOB_DEFINITIONS
WHERE JOB_DEFINITION_ID =
(SELECT JOB_DEFINITION_ID
FROM PER_JOBS
WHERE JOB_ID = g_job_id);
SELECT DUTY_STATION_CODE, DUTY_STATION_ID
FROM GHR_DUTY_STATIONS_F
WHERE trunc(p_sr_report_date) between effective_start_date and
nvl(effective_end_date, p_sr_report_date)
AND DUTY_STATION_ID =
(SELECT LEI_INFORMATION3
FROM HR_LOCATION_EXTRA_INFO
WHERE INFORMATION_TYPE = 'GHR_US_LOC_INFORMATION'
AND LOCATION_ID = g_location_id);
SELECT substr(address_line_1,1,35) address_line_1,
substr(address_line_2,1,35) address_line_2,
substr(address_line_3,1,35) address_line_3,
substr(postal_code,1,35) postal_code,
substr(town_or_city,1,35) town_or_city,
substr(country,1,4) country,
substr(region_2,1,2) region_2
-- FROM hr_locations_v Bug 4863608 Performance
FROM hr_locations_all
WHERE location_id = g_location_id;
select address_line1,address_line2, address_line3, region_3 address_line4,
COUNTRY, REGION_2, TOWN_OR_CITY CITY,POSTAL_CODE, REGION_1 County
FROM PER_ADDRESSES
Where Address_type= 'FED_WA'
AND Person_id = p_sr_person_id
And trunc(p_sr_report_date) between date_from and
nvl(date_to, p_sr_report_date);
SELECT STATE_OR_COUNTRY_CODE
FROM GHR_DUTY_STATIONS_F
WHERE trunc(p_report_date) between effective_start_date and
nvl(effective_end_date, p_report_date)
AND DUTY_STATION_ID =
(SELECT LEI_INFORMATION3
FROM HR_LOCATION_EXTRA_INFO
WHERE INFORMATION_TYPE = 'GHR_US_LOC_INFORMATION'
AND LOCATION_ID = g_location_id);
PROCEDURE insert_row
IS
l_proc varchar2(30) := 'insert_row';
hr_utility.set_location('Inside insert row Locality adj' || g_ghr_cpdf_temp.to_locality_adj,111 );
INSERT INTO ghr_cpdf_temp (
report_type
,session_id
,academic_discipline
,agency_code
,annuitant_indicator
,award_amount
,bargaining_unit_status
,benefit_amount
,citizenship
,creditable_military_service
,current_appointment_auth1
,current_appointment_auth2
,to_duty_station_code
,education_level
,effective_date
,employee_date_of_birth
,employee_first_name
,employee_last_name
,employee_middle_names
,from_national_identifier
,fegli
,fers_coverage
,first_action_la_code1
,first_action_la_code2
,first_noa_code
,flsa_category
,from_basic_pay
,from_duty_station_code
,from_grade_or_level
,from_locality_adj
,from_occ_code
,from_pay_table_id
,from_pay_basis
,from_pay_plan
,from_pay_rate_determinant
,from_retirement_coverage
,from_step_or_rate
,from_total_salary
,from_work_schedule
,frozen_service
,functional_class
,handicap_code
,health_plan
,individual_group_award
,organizational_component
,pay_status
,personnel_office_id
,position_occupied
,race_national_origin
,rating_of_record
,rating_of_record_level
,rating_of_record_pattern
,rating_of_record_period_starts
,rating_of_record_period_ends
,retained_grade_or_level
,retained_pay_plan
,retained_step_or_rate
,retirement_plan
,second_noa_code
,service_comp_date
,sex
,supervisory_status
,tenure
,to_basic_pay
,to_grade_or_level
,to_locality_adj
,to_national_identifier
,to_occ_code
,to_pay_basis
,to_pay_plan
,to_pay_rate_determinant
,to_pay_table_id
,to_retention_allowance
,to_staffing_differential
,to_step_or_rate
,to_supervisory_differential
,to_total_salary
,to_work_schedule
,veterans_preference
,veterans_status
,year_degree_attained,
name_title,
position_title,
award_dollars,
award_hours,
award_percentage,
SCD_retirement,
SCD_rif,
-- New EHRI Starts Here ---
ehri_employee_id,
agency_employee_id,
world_citizenship,
slct_serv_regi_indicator,
svc_oblig_type_code1,
svc_oblig_type_end_date1,
svc_oblig_type_code2,
svc_oblig_type_end_date2,
svc_oblig_type_code3,
svc_oblig_type_end_date3,
svc_oblig_type_code4,
svc_oblig_type_end_date4,
appoint_type_code,
part_time_hours,
to_adj_basic_pay,
spcl_pay_tbl_type,
act_svc_indicator,
appropriation_code,
comp_pos_indicator,
mil_char_svc_code,
mil_svc_sno,
mil_svc_start_date,
mil_svc_end_date,
mil_branch_code,
mil_discharge_code,
career_tenure_code,
fegli_life_change_code,
fegli_life_event_date,
fegli_elect_date,
fehb_event_code,
tsp_eligibility_date,
tsp_effective_date,
tsp_elect_contrib_pct,
tsp_emp_amount,
fers_elect_date,
fers_elect_indicator,
alb_indicator,
alb_elect_date,
alb_notify_date,
fegli_indicator,
--fegli_elect_date,
fegli_notify_date,
fehb_indicator,
fehb_elect_date,
--bug# 6158983
fehb_elect_eff_date,
appointment_nte_date,
--6158983
fehb_notify_date,
retire_indicator,
retire_elect_date,
retire_notify_date,
cont_elect_date,
cont_notify_date,
cont_term_elect_date,
cont_ins_pay_notify_date,
cont_pay_type_code,
scd_ses,
scd_spcl_retire,
leave_scd,
tsp_scd,
disability_retire_notify,
work_address_line1,
work_address_line2,
work_address_line3,
work_address_line4,
work_city,
work_region, --Bug# 4725292
work_state_code,
work_postal_code,
work_country_code,
work_employee_email,
work_phone_number,
home_phone_number,
cell_phone_number,
emrgncy_cntct_family_name1,
emrgncy_cntct_given_name1,
emrgncy_cntct_middle_name1,
emrgncy_cntct_suffix1,
emrgncy_cntct_infrm_upd_dt1,
emrgncy_cntct_phone1,
emrgncy_cntct_family_name2,
emrgncy_cntct_given_name2,
emrgncy_cntct_middle_name2,
emrgncy_cntct_suffix2,
emrgncy_cntct_infrm_upd_dt2,
emrgncy_cntct_phone2,
language_code1,
lang_prof_type1,
lang_prof_level1,
language_code2,
lang_prof_type2,
lang_prof_level2,
language_code3,
lang_prof_type3,
lang_prof_level3,
language_code4,
lang_prof_type4,
lang_prof_level4,
language_code5,
lang_prof_type5,
lang_prof_level5,
language_code6,
lang_prof_type6,
lang_prof_level6,
language_code7,
lang_prof_type7,
lang_prof_level7,
language_code8,
lang_prof_type8,
lang_prof_level8,
spcl_salary_rate,
race_ethnic_info,
to_spl_rate_supplement
-- created_by,
-- creation_Date,
-- last_updated_by,
-- last_update_date,
-- last_update_login
)
values (
'STATUS'
,userenv('SESSIONID')
,g_ghr_cpdf_temp.academic_discipline
,g_ghr_cpdf_temp.agency_code
,g_ghr_cpdf_temp.annuitant_indicator
,g_ghr_cpdf_temp.award_amount
,g_ghr_cpdf_temp.bargaining_unit_status
,g_ghr_cpdf_temp.benefit_amount
,g_ghr_cpdf_temp.citizenship
,g_ghr_cpdf_temp.creditable_military_service
,g_ghr_cpdf_temp.current_appointment_auth1
,g_ghr_cpdf_temp.current_appointment_auth2
,g_ghr_cpdf_temp.to_duty_station_code
,g_ghr_cpdf_temp.education_level
,g_ghr_cpdf_temp.effective_date
,g_ghr_cpdf_temp.employee_date_of_birth
,g_ghr_cpdf_temp.employee_first_name
,g_ghr_cpdf_temp.employee_last_name
,g_ghr_cpdf_temp.employee_middle_names
,g_ghr_cpdf_temp.from_national_identifier
,g_ghr_cpdf_temp.fegli
,g_ghr_cpdf_temp.fers_coverage
,g_ghr_cpdf_temp.first_action_la_code1
,g_ghr_cpdf_temp.first_action_la_code2
,g_ghr_cpdf_temp.first_noa_code
,g_ghr_cpdf_temp.flsa_category
,g_ghr_cpdf_temp.from_basic_pay
,g_ghr_cpdf_temp.from_duty_station_code
,g_ghr_cpdf_temp.from_grade_or_level
,g_ghr_cpdf_temp.from_locality_adj
,g_ghr_cpdf_temp.from_occ_code
,g_ghr_cpdf_temp.from_pay_table_id
,g_ghr_cpdf_temp.from_pay_basis
,g_ghr_cpdf_temp.from_pay_plan
,g_ghr_cpdf_temp.from_pay_rate_determinant
,g_ghr_cpdf_temp.from_retirement_coverage
,g_ghr_cpdf_temp.from_step_or_rate
,g_ghr_cpdf_temp.from_total_salary
,g_ghr_cpdf_temp.from_work_schedule
,g_ghr_cpdf_temp.frozen_service
,g_ghr_cpdf_temp.functional_class
,g_ghr_cpdf_temp.handicap_code
,g_ghr_cpdf_temp.health_plan
,g_ghr_cpdf_temp.individual_group_award
,g_ghr_cpdf_temp.organizational_component
,g_ghr_cpdf_temp.pay_status
,g_ghr_cpdf_temp.personnel_office_id
,g_ghr_cpdf_temp.position_occupied
,g_ghr_cpdf_temp.race_national_origin
,g_ghr_cpdf_temp.rating_of_record
,g_ghr_cpdf_temp.rating_of_record_level
,g_ghr_cpdf_temp.rating_of_record_pattern
,g_ghr_cpdf_temp.rating_of_record_period_starts
,g_ghr_cpdf_temp.rating_of_record_period_ends
,g_ghr_cpdf_temp.retained_grade_or_level
,g_ghr_cpdf_temp.retained_pay_plan
,g_ghr_cpdf_temp.retained_step_or_rate
,g_ghr_cpdf_temp.retirement_plan
,g_ghr_cpdf_temp.second_noa_code
,g_ghr_cpdf_temp.service_comp_date
,g_ghr_cpdf_temp.sex
,g_ghr_cpdf_temp.supervisory_status
,g_ghr_cpdf_temp.tenure
,g_ghr_cpdf_temp.to_basic_pay
,g_ghr_cpdf_temp.to_grade_or_level
,g_ghr_cpdf_temp.to_locality_adj
,g_ghr_cpdf_temp.to_national_identifier
,g_ghr_cpdf_temp.to_occ_code
,g_ghr_cpdf_temp.to_pay_basis
,g_ghr_cpdf_temp.to_pay_plan
,g_ghr_cpdf_temp.to_pay_rate_determinant
,g_ghr_cpdf_temp.to_pay_table_id
,g_ghr_cpdf_temp.to_retention_allowance
,g_ghr_cpdf_temp.to_staffing_differential
,g_ghr_cpdf_temp.to_step_or_rate
,g_ghr_cpdf_temp.to_supervisory_differential
,g_ghr_cpdf_temp.to_total_salary
,g_ghr_cpdf_temp.to_work_schedule
,g_ghr_cpdf_temp.veterans_preference
,g_ghr_cpdf_temp.veterans_status
,g_ghr_cpdf_temp.year_degree_attained,
-- p_ghr_cpdf_temp_rec.employee_first_name,
-- p_ghr_cpdf_temp_rec.employee_middle_names,
g_ghr_cpdf_temp.name_title,
g_ghr_cpdf_temp.position_title,
g_ghr_cpdf_temp.award_dollars,
g_ghr_cpdf_temp.award_hours,
g_ghr_cpdf_temp.award_percentage,
g_ghr_cpdf_temp.SCD_retirement,
g_ghr_cpdf_temp.SCD_rif,
-- JH NEW EHRI
g_ghr_cpdf_temp.ehri_employee_id,
g_ghr_cpdf_temp.agency_employee_id,
g_ghr_cpdf_temp.world_citizenship,
g_ghr_cpdf_temp.slct_serv_regi_indicator,
g_ghr_cpdf_temp.svc_oblig_type_code1,
g_ghr_cpdf_temp.svc_oblig_type_end_date1,
g_ghr_cpdf_temp.svc_oblig_type_code2,
g_ghr_cpdf_temp.svc_oblig_type_end_date2,
g_ghr_cpdf_temp.svc_oblig_type_code3,
g_ghr_cpdf_temp.svc_oblig_type_end_date3,
g_ghr_cpdf_temp.svc_oblig_type_code4,
g_ghr_cpdf_temp.svc_oblig_type_end_date4,
g_ghr_cpdf_temp.appoint_type_code,
g_ghr_cpdf_temp.part_time_hours,
g_ghr_cpdf_temp.to_adj_basic_pay,
g_ghr_cpdf_temp.spcl_pay_tbl_type,
g_ghr_cpdf_temp.act_svc_indicator,
g_ghr_cpdf_temp.appropriation_code,
g_ghr_cpdf_temp.comp_pos_indicator,
g_ghr_cpdf_temp.mil_char_svc_code,
g_ghr_cpdf_temp.mil_svc_sno,
g_ghr_cpdf_temp.mil_svc_start_date,
g_ghr_cpdf_temp.mil_svc_end_date,
g_ghr_cpdf_temp.mil_branch_code,
g_ghr_cpdf_temp.mil_discharge_code,
g_ghr_cpdf_temp.career_tenure_code,
g_ghr_cpdf_temp.fegli_life_change_code,
g_ghr_cpdf_temp.fegli_life_event_date,
g_ghr_cpdf_temp.fegli_elect_date,
g_ghr_cpdf_temp.fehb_event_code,
g_ghr_cpdf_temp.tsp_eligibility_date,
g_ghr_cpdf_temp.tsp_effective_date,
g_ghr_cpdf_temp.tsp_elect_contrib_pct,
g_ghr_cpdf_temp.tsp_emp_amount,
g_ghr_cpdf_temp.fers_elect_date,
g_ghr_cpdf_temp.fers_elect_indicator,
g_ghr_cpdf_temp.alb_indicator,
g_ghr_cpdf_temp.alb_elect_date,
g_ghr_cpdf_temp.alb_notify_date,
g_ghr_cpdf_temp.fegli_indicator,
--g_ghr_cpdf_temp.fegli_elect_date,
g_ghr_cpdf_temp.fegli_notify_date,
g_ghr_cpdf_temp.fehb_indicator,
g_ghr_cpdf_temp.fehb_elect_date,
--Bug# 6158983
g_ghr_cpdf_temp.fehb_elect_eff_date,
g_ghr_cpdf_temp.appointment_nte_date,
--Bug# 6158983
g_ghr_cpdf_temp.fehb_notify_date,
g_ghr_cpdf_temp.retire_indicator,
g_ghr_cpdf_temp.retire_elect_date,
g_ghr_cpdf_temp.retire_notify_date,
g_ghr_cpdf_temp.cont_elect_date,
g_ghr_cpdf_temp.cont_notify_date,
g_ghr_cpdf_temp.cont_term_elect_date,
g_ghr_cpdf_temp.cont_ins_pay_notify_date,
g_ghr_cpdf_temp.cont_pay_type_code,
g_ghr_cpdf_temp.scd_ses,
g_ghr_cpdf_temp.scd_spcl_retire,
g_ghr_cpdf_temp.leave_scd,
g_ghr_cpdf_temp.tsp_scd,
g_ghr_cpdf_temp.disability_retire_notify,
g_ghr_cpdf_temp.work_address_line1,
g_ghr_cpdf_temp.work_address_line2,
g_ghr_cpdf_temp.work_address_line3,
g_ghr_cpdf_temp.work_address_line4,
g_ghr_cpdf_temp.work_city,
g_ghr_cpdf_temp.work_region, --Bug# 4725292
g_ghr_cpdf_temp.work_state_code,
g_ghr_cpdf_temp.work_postal_code,
g_ghr_cpdf_temp.work_country_code,
g_ghr_cpdf_temp.work_employee_email,
g_ghr_cpdf_temp.work_phone_number,
g_ghr_cpdf_temp.home_phone_number,
g_ghr_cpdf_temp.cell_phone_number,
g_ghr_cpdf_temp.emrgncy_cntct_family_name1,
g_ghr_cpdf_temp.emrgncy_cntct_given_name1,
g_ghr_cpdf_temp.emrgncy_cntct_middle_name1,
g_ghr_cpdf_temp.emrgncy_cntct_suffix1,
g_ghr_cpdf_temp.emrgncy_cntct_infrm_upd_dt1,
g_ghr_cpdf_temp.emrgncy_cntct_phone1,
g_ghr_cpdf_temp.emrgncy_cntct_family_name2,
g_ghr_cpdf_temp.emrgncy_cntct_given_name2,
g_ghr_cpdf_temp.emrgncy_cntct_middle_name2,
g_ghr_cpdf_temp.emrgncy_cntct_suffix2,
g_ghr_cpdf_temp.emrgncy_cntct_infrm_upd_dt2,
g_ghr_cpdf_temp.emrgncy_cntct_phone2,
g_ghr_cpdf_temp.language_code1,
g_ghr_cpdf_temp.lang_prof_type1,
g_ghr_cpdf_temp.lang_prof_level1,
g_ghr_cpdf_temp.language_code2,
g_ghr_cpdf_temp.lang_prof_type2,
g_ghr_cpdf_temp.lang_prof_level2,
g_ghr_cpdf_temp.language_code3,
g_ghr_cpdf_temp.lang_prof_type3,
g_ghr_cpdf_temp.lang_prof_level3,
g_ghr_cpdf_temp.language_code4,
g_ghr_cpdf_temp.lang_prof_type4,
g_ghr_cpdf_temp.lang_prof_level4,
g_ghr_cpdf_temp.language_code5,
g_ghr_cpdf_temp.lang_prof_type5,
g_ghr_cpdf_temp.lang_prof_level5,
g_ghr_cpdf_temp.language_code6,
g_ghr_cpdf_temp.lang_prof_type6,
g_ghr_cpdf_temp.lang_prof_level6,
g_ghr_cpdf_temp.language_code7,
g_ghr_cpdf_temp.lang_prof_type7,
g_ghr_cpdf_temp.lang_prof_level7,
g_ghr_cpdf_temp.language_code8,
g_ghr_cpdf_temp.lang_prof_type8,
g_ghr_cpdf_temp.lang_prof_level8,
g_ghr_cpdf_temp.spcl_salary_rate,
g_ghr_cpdf_temp.race_ethnic_info,
g_ghr_cpdf_temp.to_spl_rate_supplement
);
l_log_text := 'Unhandled Error under procedure insert_row'||
'; ** Error Message ** : ' ||substr(sqlerrm,1,1000);
END insert_row;
DELETE FROM ghr_cpdf_temp
WHERE (report_type='STATUS')
AND (
-- *** SUPPRESS NON APPROPRIATED EMPLOYEES / COMMISSIONED OFFICERS
( to_pay_plan IN ('NA','NL','NS','CC') )
-- *** EXCLUDE NON US CITIZENS WORKING IN FOREIGN DUTY STATIONS
OR ( from_duty_station_code = 'Y'
AND decode(citizenship, NULL, ' ', citizenship) <> 'Y' )
-- *** EXCLUDE CERTAIN AGENCIES
OR ( agency_code IN ('CI00','DD05','DD28','FR00',
'PO00','PJ00','TV00','WH01') )
-- *** EXCLUDE CERTAIN SUBELEMENTS
OR ( substr(agency_code,1,2) IN ('LL','LB','LA','LD','LG','LC') )
-- *** EXCLUDE NON SELECTED AGENCY CODE
-- OR ( decode(agency_code, NULL, ' ', agency_code)
-- not like DECODE(g_agency,NULL,'%',rtrim(g_agency)||'%'))
)
;
SELECT INSTR(TRANSLATE(UPPER(p_last_name),',.',' '),' '||UPPER(LOOKUP_CODE),-1),
LENGTH(p_last_name)
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'GHR_US_NAME_SUFFIX'
AND TRUNC(p_report_date) BETWEEN NVL(START_DATE_ACTIVE,p_report_date)
AND NVL(END_DATE_ACTIVE,p_report_date)
AND RTRIM(SUBSTR(TRANSLATE(UPPER(p_last_name),',.',' '),
INSTR(TRANSLATE(UPPER(p_last_name),',.',' '),' '||UPPER(LOOKUP_CODE),-1),
LENGTH(p_last_name)),' ') = ' '||UPPER(LOOKUP_CODE)
AND ROWNUM = 1;
SELECT *
FROM GHR_CPDF_TEMP
WHERE SESSION_ID = c_session_id
AND report_type = 'STATUS';
SELECT outfile_name
FROM FND_CONCURRENT_REQUESTS
WHERE request_id = c_request_id;
select value
into l_audit_log_dir
from v$parameter
where name = 'utl_file_dir';
p_tags(l_count).tagname := 'Selective_Service_Registration_Indicator';
p_tags(l_count).tagname := 'Emergency_Contact_Information_Update_Date_1';
p_tags(l_count).tagname := 'Emergency_Contact_Information_Update_Date_2';
SELECT asg.assignment_id,
asg.person_id,
asg.position_id,
asg.grade_id,
asg.job_id,
asg.location_id,
asg.effective_start_date,
asg.business_group_id,
ast.per_system_status assignment_status_type,
ghr_api.get_position_agency_code_pos(asg.position_id,asg.business_group_id) agency_code
FROM PER_ALL_ASSIGNMENTS asg, per_assignment_status_types ast -- Changing from per_assignments_f
WHERE ast.assignment_status_type_id = asg.assignment_status_type_id
-- only consider "Active" assignments as defined by below, also only look at
-- assignments that are assigned to a valid person as of the report date.
AND g_report_date between asg.effective_start_date and asg.effective_end_date
AND ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
AND asg.assignment_type <> 'B'
AND asg.position_id IS NOT NULL
AND ghr_api.get_position_agency_code_pos(asg.position_id,asg.business_group_id) like p_agency
AND decode(hr_general.get_xbg_profile,'Y',asg.business_group_id , hr_general.get_business_group_id) = asg.business_group_id
ORDER BY assignment_id;
SELECT pap.full_name name ,pap.national_identifier ssn,pap.last_name,pap.first_name
,pap.middle_names, pap.title
FROM per_all_people pap
WHERE p_person_id = pap.person_id
AND p_report_date between pap.effective_start_date AND pap.effective_end_date
AND g_business_group_id = pap.business_group_id;
SELECT first_noa_information1
FROM ghr_pa_requests
WHERE employee_assignment_id = g_assignment_id
AND pa_notification_id is not null
AND person_id = g_person_id
AND to_position_id = g_position_id
AND noa_family_code = 'APP'
AND first_noa_information1 like '____%__%__ __:__:__'
AND fnd_date.canonical_to_date(first_noa_information1) >= g_report_date;*/
SELECT aei_information4
FROM ghr_assignment_extra_info_h_v
WHERE pa_history_id = (SELECT max(pa_history_id)
FROM ghr_assignment_extra_info_h_v ASG,
ghr_nature_of_actions NAT
WHERE information_type = 'GHR_US_ASG_NTE_DATES'
AND asg.nature_of_action_id = nat.nature_of_action_id
AND (code LIKE '1%' OR code LIKE '5%' OR code IN ('750','760','761','762','765'))
AND aei_information4 IS NOT NULL
AND assignment_id = g_assignment_id
AND person_id = g_person_id)
AND fnd_date.canonical_to_date(aei_information4) >= g_report_date;
SELECT SUBSTR(user_table_name,1,4) user_table_name
FROM pay_user_tables
WHERE user_table_id = p_user_table_id;
SELECT 1
FROM ghr_pay_plans ppl
WHERE ppl.pay_plan = p_pay_plan
AND ppl.equivalent_pay_plan = 'GS';
insert_row;
INSERT INTO fnd_sessions
(session_id
,effective_date)
VALUES
(userenv('sessionid')
,l_report_date);
DELETE FROM fnd_sessions
WHERE session_id = userenv('sessionid');