ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223EFB73869E053B46B63588994 SELECT substr(AGENCY_CODE ,1,04) l_AGENCY_SUB_ELEMENT_CODE , substr(ORGANIZATIONAL_COMPONENT ,1,18) l_ORGANIZATIONAL_COMPONENT , substr(PERSONNEL_OFFICE_ID ,1,04) l_PERSONNEL_OFFICE_IDENTIFIER , substr(TO_NATIONAL_IDENTIFIER ,1,09) l_SOCIAL_SECURITY_NUMBER ,to_char(EMPLOYEE_DATE_OF_BIRTH,'YYYYMM' ) l_DATE_OF_BIRTH , substr(VETERANS_PREFERENCE ,1,01) l_VETERANS_PREFERENCE , substr(TENURE ,1,01) l_TENURE ,to_char(SERVICE_COMP_DATE,'YYYYMM' ) l_SERVICE_COMPUTATION_DATE , substr(RETIREMENT_PLAN ,1,01) l_RETIREMENT_PLAN , substr(CREDITABLE_MILITARY_SERVICE ,1,04) l_CREDITABLE_MILITARY_SERVICE , substr(FROZEN_SERVICE ,1,04) l_FROZEN_SERVICE ,' ' I_FILLER_FEGLI , substr(HEALTH_PLAN ,1,03) l_HEALTH_PLAN , substr(VETERANS_STATUS ,1,01) l_VETERANS_STATUS , substr(ANNUITANT_INDICATOR ,1,01) l_ANNUITANT_INDICATOR , substr(CITIZENSHIP ,1,01) l_US_CITIZENSHIP , substr(SEX ,1,01) l_SEX , substr(RACE_NATIONAL_ORIGIN ,1,01) l_RACE_OR_NATIONAL_ORIGIN , substr(HANDICAP_CODE ,1,02) l_HANDICAP_CODE , substr(TO_PAY_PLAN ,1,02) l_PAY_PLAN , substr(TO_OCC_CODE ,1,04) l_OCCUPATION , substr(TO_GRADE_OR_LEVEL ,1,02) l_GRADE_LEVEL , substr(TO_STEP_OR_RATE ,1,02) l_STEP_OR_RATE , LTRIM(TO_CHAR(NVL(to_basic_pay, 0), DECODE(to_pay_basis, 'PH', '0000V00', 'PD', '0000V00', '000000'))) I_BASIC_PAY , substr(FUNCTIONAL_CLASS ,1,02) l_FUNCTIONAL_CLASSIFICATION , substr(TO_PAY_BASIS ,1,02) l_PAY_BASIS , substr(TO_WORK_SCHEDULE ,1,01) l_WORK_SCHEDULE , substr(TO_PAY_RATE_DETERMINANT ,1,01) l_PAY_RATE_DETERMINANT , substr(POSITION_OCCUPIED ,1,01) l_POSITION_OCCUPIED , substr(SUPERVISORY_STATUS ,1,01) l_SUPERVISORY_STATUS , substr(FLSA_CATEGORY ,1,01) l_FLSA_CATEGORY , DECODE(SIGN(5 - LENGTH(BARGAINING_UNIT_STATUS)), 1, BARGAINING_UNIT_STATUS, SUBSTR(BARGAINING_UNIT_STATUS, 3, 4)) l_BARGAINING_UNIT , substr(TO_DUTY_STATION_CODE ,1,09) l_DUTY_STATION , substr(CURRENT_APPOINTMENT_AUTH1 ,1,03) l_CURRENT_APPOINTMENT_AUTH_1 , substr(CURRENT_APPOINTMENT_AUTH2 ,1,03) l_CURRENT_APPOINTMENT_AUTH_2 , substr(RETAINED_PAY_PLAN ,1,02) l_RETAINED_PAY_PLAN , substr(RETAINED_GRADE_OR_LEVEL ,1,02) l_RETAINED_GRADE , substr(RETAINED_STEP_OR_RATE ,1,02) l_RETAINED_STEP , substr(RATING_OF_RECORD_LEVEL ,1,01) l_RATING_OF_RECORD_LEVEL , substr(RATING_OF_RECORD_PATTERN ,1,01) l_RATING_OF_RECORD_PATTERN ,to_char(RATING_OF_RECORD_PERIOD_ENDS,'YYYYMM') l_RATING_OF_RECORD_PERIOD , substr(PAY_STATUS ,1,01) l_PAY_STATUS , substr(FERS_COVERAGE ,1,01) l_FERS_COVERAGE , decode(TO_LOCALITY_ADJ,null,' ',LTRIM(TO_CHAR(NVL(TO_LOCALITY_ADJ, 0), '00000'))) I_LOCALITY_ADJUSTMENT , NVL(substr(race_ethnic_info,2,6) ,' ') l_STAFFING_DIFFERENTIAL , NVL(substr(race_ethnic_info,1,1),' ') race2 ,DECODE(to_supervisory_differential,0,NULL,LPAD(to_supervisory_differential,5,'0')) l_supervisory_differential ,DECODE(to_retention_allowance,0,NULL,LPAD(to_retention_allowance,5,'0')) l_retention_allowance , substr(TO_PAY_TABLE_ID ,1,04) l_SPECIAL_PAY_TABLE_ID , substr(EDUCATION_LEVEL ,1,02) l_EDUCATION_LEVEL ,to_char(YEAR_DEGREE_ATTAINED ) l_YR_DEGREE_OR_CERT_ATTAINED , substr(ACADEMIC_DISCIPLINE ,1,06) l_INSTRUCTIONAL_PROGRAM , substr(FEGLI ,1,02) l_FEDERAL_EMPLOYEE_GLI , ' ' , 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, ' ' A2 FROM ghr_cpdf_temp WHERE report_type = 'STATUS' AND session_id = USERENV('sessionid') ORDER by agency_code,to_national_identifier --; GHR GHRCPDFS_XML US GHR CPDF Status Application: US Federal Human Resources Source: CPDF Status Report (XML) Short Name: GHRCPDFS_XML DB package: GHR_GHRCPDFS_XMLP_PKG BI Publisher :filename :p_agency_code :p_agency_subelement :p_conc_request_id :p_report_date 1 10 :filename Char US Report Filename 2 20 :p_report_date Date Y US Report Date 3 30 :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 4 40 :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