GHR CPDF Dynamics

Description
Categories: BI Publisher, Human Resources
Application: US Federal Human Resources
Source: CPDF Dynamics Report (XML)
Short Name: GHRCPDFD_XML
DB package: GHR_GHRCPDFD_XMLP_PKG
			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
Parameter NameSQL textValidation
Subelement
 
LOV Oracle
Agency Code
 
LOV Oracle
Report To Date
 
Date
Report From Date
 
Date
Report Filename