SELECT agency_code,
organizational_component,
personnel_office_id,
to_national_identifier,
to_char(employee_date_of_birth,'YYYYMM') dob,
veterans_preference,
tenure,
to_char(service_comp_date,'YYYYMM') scd,
retirement_plan,
creditable_military_service,
frozen_service,
from_retirement_coverage,
veterans_status,
sex,
race_national_origin,
handicap_code,
first_noa_code,
second_noa_code,
first_action_la_code1,
first_action_la_code2,
TO_CHAR(effective_date,'YYYYMMDD') eff_date,
to_pay_plan,
to_occ_code,
to_grade_or_level,
to_step_or_rate,
ghr_cpdf_dynrpt.format_basic_pay (to_basic_pay,to_pay_basis,6) to_basic_pay,
to_pay_basis,
to_work_schedule,
to_pay_rate_determinant,
position_occupied,
supervisory_status,
to_duty_station_code,
current_appointment_auth1,
current_appointment_auth2,
rating_of_record_level,
rating_of_record_pattern,
TO_CHAR(rating_of_record_period_ends,'YYYYMM') rating_of_record_period_ends,
LTRIM(TO_CHAR(individual_group_award, '0000')) individual_group_award,
ghr_cpdf_dynrpt.format_amount(award_amount,6) award_amount,
nvl(substr(race_ethnic_info,1,6),' ') || ' ' benefit_amount,
/* decode(employee_last_name,NULL,'',SUBSTR(employee_last_name||','||employee_first_name||' '||employee_middle_names,1,23) ) name,
from_pay_plan,
from_occ_code,
from_grade_or_level,
from_step_or_rate,
ghr_cpdf_dynrpt.format_basic_pay (from_basic_pay,from_pay_basis,6) from_basic_pay,
from_pay_basis,
from_work_schedule,
from_pay_rate_determinant,
NULL noa_corrected,
NULL date_corrected,
from_national_identifier ssn_corrected,
ghr_cpdf_dynrpt.format_amount(from_locality_adj,5) from_locality_adj,
from_duty_station_code,
ghr_cpdf_dynrpt.format_amount(to_locality_adj,5) to_locality_adj
,DECODE(to_staffing_differential,0,NULL,LPAD(to_staffing_differential,5,'0'))
to_staffing_differential
,DECODE(to_supervisory_differential,0,NULL,LPAD(to_supervisory_differential,5,'0'))
to_supervisory_differential
,DECODE(to_retention_allowance,0,NULL,LPAD(to_retention_allowance,5,'0'))
to_retention_allowance,
education_level,
academic_discipline,
TO_CHAR(year_degree_attained) year_degree_attained,
' ' A1,
employee_last_name,
Employee_first_name,
employee_middle_names,
name_title,
position_title,
TO_CHAR(SCD_retirement,'YYYYMMDD') service_comp_Date_ret,
TO_CHAR(SCD_rif,'YYYYMMDD') service_comp_date_rif,
ghr_cpdf_dynrpt.format_amount(award_dollars,9) award_dollars,
ghr_cpdf_dynrpt.format_amount(award_hours,9) award_hours,
ghr_cpdf_dynrpt.format_amount(award_percentage,9) award_percentage,
' ' A2,
GHR_GHRCPDFD_XMLP_PKG.cf_super_diffformula(first_noa_code, first_action_la_code1, first_action_la_code2, DECODE ( to_supervisory_differential , 0 , NULL , LPAD ( to_supervisory_differential , 5 , '0' ) )) CF_Super_diff,
GHR_GHRCPDFD_XMLP_PKG.cf_retn_allowformula(first_noa_code, first_action_la_code1, first_action_la_code2, DECODE ( to_retention_allowance , 0 , NULL , LPAD ( to_retention_allowance , 5 , '0' ) )) CF_retn_allow
from_retirement_coverage,
veterans_status,
sex,
race_national_origin,
handicap_code,
first_noa_code,
second_noa_code,
first_action_la_code1,
first_action_la_code2,
TO_CHAR(effective_date,'YYYYMMDD') eff_date,
to_pay_plan,
to_occ_code,
to_grade_or_level,
to_step_or_rate,
ghr_cpdf_dynrpt.format_basic_pay (to_basic_pay,to_pay_basis,6) to_basic_pay,
to_pay_basis,
to_work_schedule,
to_pay_rate_determinant,
position_occupied,
supervisory_status,
to_duty_station_code,
current_appointment_auth1,
current_appointment_auth2,
rating_of_record_level,
rating_of_record_pattern,
TO_CHAR(rating_of_record_period_ends,'YYYYMM') rating_of_record_period_ends,
LTRIM(TO_CHAR(individual_group_award, '0000')) individual_group_award,
ghr_cpdf_dynrpt.format_amount(award_amount,6) award_amount,
nvl(substr(race_ethnic_info,1,6),' ') || ' ' benefit_amount,*/
decode(employee_last_name,NULL,'',SUBSTR(employee_last_name||','||employee_first_name||' '||employee_middle_names,1,23) ) name,
from_pay_plan,
from_occ_code,
from_grade_or_level,
from_step_or_rate,
ghr_cpdf_dynrpt.format_basic_pay (from_basic_pay,from_pay_basis,6) from_basic_pay,
from_pay_basis,
from_work_schedule,
from_pay_rate_determinant,
NULL noa_corrected,
NULL date_corrected,
from_national_identifier ssn_corrected,
ghr_cpdf_dynrpt.format_amount(from_locality_adj,5) from_locality_adj,
from_duty_station_code,
ghr_cpdf_dynrpt.format_amount(to_locality_adj,5) to_locality_adj
,DECODE(to_staffing_differential,0,NULL,LPAD(to_staffing_differential,5,'0'))
to_staffing_differential
,DECODE(to_supervisory_differential,0,NULL,LPAD(to_supervisory_differential,5,'0'))
to_supervisory_differential
,DECODE(to_retention_allowance,0,NULL,LPAD(to_retention_allowance,5,'0'))
to_retention_allowance,
education_level,
academic_discipline,
TO_CHAR(year_degree_attained) year_degree_attained,
' ' A1,
employee_last_name,
Employee_first_name,
employee_middle_names,
name_title,
position_title,
TO_CHAR(SCD_retirement,'YYYYMMDD') service_comp_Date_ret,
TO_CHAR(SCD_rif,'YYYYMMDD') service_comp_date_rif,
ghr_cpdf_dynrpt.format_amount(award_dollars,9) award_dollars,
ghr_cpdf_dynrpt.format_amount(award_hours,9) award_hours,
ghr_cpdf_dynrpt.format_amount(award_percentage,9) award_percentage,
' ' A2,
GHR_GHRCPDFD_XMLP_PKG.cf_super_diffformula(first_noa_code, first_action_la_code1, first_action_la_code2, DECODE ( to_supervisory_differential , 0 , NULL , LPAD ( to_supervisory_differential , 5 , '0' ) )) CF_Super_diff,
GHR_GHRCPDFD_XMLP_PKG.cf_retn_allowformula(first_noa_code, first_action_la_code1, first_action_la_code2, DECODE ( to_retention_allowance , 0 , NULL , LPAD ( to_retention_allowance , 5 , '0' ) )) CF_retn_allow
FROM ghr_cpdf_temp
WHERE report_type ='DYNAMICS'
AND session_id = USERENV('sessionid')
ORDER BY effective_date, agency_code,to_national_identifier |