GHR CPDF Dynamics

Description

Application: US Federal Human Resources
Source: CPDF Dynamics Report (XML)
Short Name: GHRCPDFD_XML
DB package: GHR_GHRCPDFD_XMLP_PKG

GHR CPDF Dynamics and other Oracle EBS reports with Blitz Report™ on our demo environment

Copy

			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

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 Name

SQL text

Validation

Report Filename

Char

Report From Date

Date

Report To Date

Date

Agency Code

LOV Oracle

Subelement

LOV Oracle

Download

Related Blitz Reports

GHR CPDF Organizational Component Translation GHR CPDF Status GL Currency - Historical Rates Listing INV Move Order Pick Slip WSH Pick Slip ECC Depot Repair, Depot Service Orders CST Margin Analysis Report with Order Management ECC Payables, Installments, SQL1 ECC Depot Repair, Depot Resolved Service Orders

Blitz Report™

Blitz Report™ provides multiple benefits:

Perfectly formatted Excel reports
Create new or ad-hoc reports in minutes
User configurable reports
Includes seeded reports for all core areas
Toolkits for tuning, management and support
Data governance and audit tracking
Central report repository & version control
Imports BI-Publisher and Discoverer reports
EBS forms interface for user adoption

Find out more

Features

Download

FAQ