GHR CPDF Status

Description
Categories: BI Publisher, Human Resources
Application: US Federal Human Resources
Source: CPDF Status Report (XML)
Short Name: GHRCPDFS_XML
DB package: GHR_GHRCPDFS_XMLP_PKG
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
              --;
Parameter Name SQL text Validation
Subelement
 
LOV Oracle
Agency Code
 
LOV Oracle
Report Date
 
Date
Report Filename