GHR CPDF Status

➜ Back to Library
Description
Categories: BI Publisher
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 NameSQL textValidation
Report Filename
 
Char
Report Date
 
Date
Agency Code
 
LOV Oracle
Subelement
 
LOV Oracle
Download
Related Blitz Reports
GHR CPDF Dynamics GHR CPDF Organizational Component Translation GL Currency - Historical Rates Listing INV Move Order Pick Slip ECC Depot Repair, Depot Service Orders (1) WSH Pick Slip ECC Depot Repair, Depot Service Orders (2) ECC Depot Repair, Depot Service Orders CST Margin Analysis Report with Order Management
Blitz Report™