ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223EFB53869E053B46B63588994 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 GHR GHRCPDFD_XML US GHR CPDF Dynamics Application: US Federal Human Resources Source: CPDF Dynamics Report (XML) Short Name: GHRCPDFD_XML DB package: GHR_GHRCPDFD_XMLP_PKG BI Publisher :filename :p_agency_code :p_agency_subelement :p_conc_request_id :p_report_date_from :p_report_date_to 1 10 :filename Char US Report Filename 2 20 :p_report_date_from Date Y US Report From Date 3 30 :p_report_date_to Date Y US Report To Date 4 40 :p_agency_code LOV Oracle GHR_US_AGENCY_CODE_2 Y Y select lookup_code id, lookup_code value, meaning description from hr_lookups hrl,fnd_sessions s where s.session_id=userenv('sessionid') and hrl.lookup_type='GHR_US_AGENCY_CODE_2' and hrl.enabled_flag='Y' and s.effective_date between nvl(start_date_active,s.effective_date) and nvl(end_date_active,s.effective_date) order by hrl.lookup_code Y US Agency Code 5 50 :p_agency_subelement LOV Oracle GHR_US_AGENCY_SUBELEMENT Y Y select substr(lookup_code,3) id, substr(lookup_code,3) value, meaning description from hr_lookups hrl,fnd_sessions s where s.session_id=userenv('sessionid') and hrl.lookup_type='GHR_US_AGENCY_CODE' and hrl.enabled_flag='Y' and lookup_code like :$flex$.ghr_us_agency_code_2 || '%' and s.effective_date between nvl(start_date_active,s.effective_date) and nvl(end_date_active,s.effective_date) order by substr(hrl.lookup_code,3) US Subelement :$flex$.ghr_us_agency_code_2 Agency Code Subelement