GPBR_ES_FILE_VW

(SQL View)
 Index Back

XML File Name Record View

Determine the XML File Name for eSocial events.

SELECT A.gpbr_driver_seq , Replace (Replace (Replace (Replace (Replace(A.company %Concat '_' %Concat A.gpbr_event_cd %Concat '_' %Concat CASE WHEN A.gpbr_event_cd = 'S-1000' THEN S1000.estabid WHEN A.gpbr_event_cd = 'S-1005' THEN S1005.estabid WHEN A.gpbr_event_cd = 'S-1010' THEN S1010.pin_nm WHEN A.gpbr_event_cd = 'S-1020' THEN S1020.estabid %Concat S1020.gpbr_service_taker %Concat S1020.tp_locat_bra WHEN A.gpbr_event_cd = 'S-1030' THEN S1030.setid %Concat S1030.jobcode WHEN A.gpbr_event_cd = 'S-1050' THEN S1050.setid %Concat S1050.shift_id WHEN A.gpbr_event_cd = 'S-1060' THEN S1060.wplace_loc_bra %Concat '_' %Concat CASE WHEN S1060.wplace_loc_bra = '1' THEN S1060.estabid %Concat '_' %Concat S1060.wplace_code_bra WHEN S1060.wplace_loc_bra = '2' THEN S1060.gpbr_service_taker %Concat '_' %Concat S1060.estabid %Concat '_' %Concat S1060.wplace_code_bra ELSE S1060.wplace_code_bra END WHEN A.gpbr_event_cd = 'S-1070' THEN S1070.PRC_TYPE_BRA %Concat '_' %Concat S1070.PROC_NBR_BRA WHEN A.gpbr_event_cd = 'S-2240' THEN S2240.emplid WHEN A.GPBR_EVENT_CD ='S-2220' THEN S2220.EMPLID %Concat '_' %Concat CASE WHEN S2220.PPP_EXAM_TYPE_BRA= 'A' THEN '0' WHEN S2220.PPP_EXAM_TYPE_BRA= 'P' THEN '1' WHEN S2220.PPP_EXAM_TYPE_BRA= 'R' THEN '2' WHEN S2220.PPP_EXAM_TYPE_BRA= 'M' THEN '3' WHEN S2220.PPP_EXAM_TYPE_BRA= 'S' THEN '4' WHEN S2220.PPP_EXAM_TYPE_BRA= 'D' THEN '9' ELSE ' ' END WHEN A.GPBR_EVENT_CD = 'S-2500' THEN S2500.GPBR_PROC_SOURCE %Concat '_' %Concat S2500.GPBR_PROC_NUM %Concat '_' %Concat S2500.GPBR_CPF_ID WHEN A.GPBR_EVENT_CD = 'S-2501' THEN S2501.GPBR_PROC_NUM %Concat '_' %Concat %NumToChar(S2501.GPBR_ESOC_YEAR) %Concat '-' %Concat S2501.GPBR_ESOC_MONTHCD WHEN A.GPBR_EVENT_CD = 'S-3500' THEN S3500.GPBR_EVENT_CD %Concat '_' %Concat S3500.GPBR_PROC_NUM %Concat '_' %Concat CASE WHEN S3500.GPBR_EVENT_CD = 'S-2500' THEN S3500.GPBR_CPF_ID ELSE S3500.GPBR_PER_REF END ELSE S2100.emplid END %Concat '_' %Concat %Sql(funclib_hr_date_yymmdd, A.gpbr_es_ps) %Concat '_' %Concat %NumToChar(ROW_NUMBER() OVER ( PARTITION BY A.company,A.gpbr_event_cd, CASE WHEN A.gpbr_event_cd = 'S-1000' THEN S1000.estabid WHEN A.gpbr_event_cd = 'S-1005' THEN S1005.estabid WHEN A.gpbr_event_cd = 'S-1010' THEN S1010.pin_nm WHEN A.gpbr_event_cd = 'S-1020' THEN S1020.estabid %CONCAT S1020.gpbr_service_taker %CONCAT S1020.tp_locat_bra WHEN A.gpbr_event_cd = 'S-1030' THEN S1030.setid %CONCAT S1030.jobcode WHEN A.gpbr_event_cd = 'S-1050' THEN S1050.setid %CONCAT S1050.shift_id WHEN A.gpbr_event_cd = 'S-1060' THEN S1060.wplace_loc_bra %CONCAT '_' %CONCAT CASE WHEN S1060.wplace_loc_bra = '1' THEN S1060.estabid %CONCAT '_' %CONCAT S1060.wplace_code_bra WHEN S1060.wplace_loc_bra = '2' THEN S1060.gpbr_service_taker %CONCAT '_' %CONCAT S1060.estabid %CONCAT '_' %CONCAT S1060.wplace_code_bra ELSE S1060.wplace_code_bra END WHEN A.gpbr_event_cd = 'S-1070' THEN S1070.PRC_TYPE_BRA %CONCAT '_' %CONCAT S1070.PROC_NBR_BRA WHEN A.gpbr_event_cd = 'S-2240' THEN S2240.emplid WHEN A.GPBR_EVENT_CD = 'S-2220' THEN S2220.EMPLID %CONCAT '_' %CONCAT CASE WHEN S2220.PPP_EXAM_TYPE_BRA= 'A' THEN '0' WHEN S2220.PPP_EXAM_TYPE_BRA= 'P' THEN '1' WHEN S2220.PPP_EXAM_TYPE_BRA= 'R' THEN '2' WHEN S2220.PPP_EXAM_TYPE_BRA= 'M' THEN '3' WHEN S2220.PPP_EXAM_TYPE_BRA= 'S' THEN '4' WHEN S2220.PPP_EXAM_TYPE_BRA= 'D' THEN '9' ELSE ' ' END WHEN A.GPBR_EVENT_CD = 'S-2500' THEN S2500.GPBR_PROC_SOURCE %Concat '_' %Concat S2500.GPBR_PROC_NUM %Concat '_' %Concat S2500.GPBR_CPF_ID WHEN A.GPBR_EVENT_CD = 'S-2501' THEN S2501.GPBR_PROC_NUM %Concat '_' %Concat %NumToChar(S2501.GPBR_ESOC_YEAR) %Concat '-' %Concat S2501.GPBR_ESOC_MONTHCD WHEN A.GPBR_EVENT_CD = 'S-3500' THEN S3500.GPBR_EVENT_CD %Concat '_' %Concat S3500.GPBR_PROC_NUM %Concat '_' %Concat CASE WHEN S3500.GPBR_EVENT_CD = 'S-2500' THEN S3500.GPBR_CPF_ID ELSE S3500.GPBR_PER_REF END ELSE S2100.emplid END , A.gpbr_es_ps ORDER BY A.company, A.gpbr_event_cd, CASE WHEN A.gpbr_event_cd = 'S-1000' THEN S1000.estabid WHEN A.gpbr_event_cd = 'S-1005' THEN S1005.estabid WHEN A.gpbr_event_cd = 'S-1010' THEN S1010.pin_nm WHEN A.gpbr_event_cd = 'S-1020' THEN S1020.estabid %CONCAT S1020.gpbr_service_taker %CONCAT S1020.tp_locat_bra WHEN A.gpbr_event_cd = 'S-1030' THEN S1030.setid %CONCAT S1030.jobcode WHEN A.gpbr_event_cd = 'S-1050' THEN S1050.setid %CONCAT S1050.shift_id WHEN A.gpbr_event_cd = 'S-1060' THEN S1060.wplace_loc_bra %CONCAT '_' %CONCAT CASE WHEN S1060.wplace_loc_bra = '1' THEN S1060.estabid %CONCAT '_' %CONCAT S1060.wplace_code_bra WHEN S1060.wplace_loc_bra = '2' THEN S1060.gpbr_service_taker %CONCAT '_' %CONCAT S1060.estabid %CONCAT '_' %CONCAT S1060.wplace_code_bra ELSE S1060.wplace_code_bra END WHEN A.gpbr_event_cd = 'S-1070' THEN S1070.PRC_TYPE_BRA %CONCAT '_' %CONCAT S1070.PROC_NBR_BRA WHEN A.gpbr_event_cd = 'S-2240' THEN S2240.emplid WHEN A.GPBR_EVENT_CD = 'S-2220' THEN S2220.EMPLID %CONCAT '_' %CONCAT CASE WHEN S2220.PPP_EXAM_TYPE_BRA= 'A' THEN '0' WHEN S2220.PPP_EXAM_TYPE_BRA= 'P' THEN '1' WHEN S2220.PPP_EXAM_TYPE_BRA= 'R' THEN '2' WHEN S2220.PPP_EXAM_TYPE_BRA= 'M' THEN '3' WHEN S2220.PPP_EXAM_TYPE_BRA= 'S' THEN '4' WHEN S2220.PPP_EXAM_TYPE_BRA= 'D' THEN '9' ELSE ' ' END WHEN A.GPBR_EVENT_CD = 'S-2500' THEN S2500.GPBR_PROC_SOURCE %Concat '_' %Concat S2500.GPBR_PROC_NUM %Concat '_' %Concat S2500.GPBR_CPF_ID WHEN A.GPBR_EVENT_CD = 'S-2501' THEN S2501.GPBR_PROC_NUM %Concat '_' %Concat %NumToChar(S2501.GPBR_ESOC_YEAR) %Concat '-' %Concat S2501.GPBR_ESOC_MONTHCD WHEN A.GPBR_EVENT_CD = 'S-3500' THEN S3500.GPBR_EVENT_CD %Concat '_' %Concat S3500.GPBR_PROC_NUM %Concat '_' %Concat CASE WHEN S3500.GPBR_EVENT_CD = 'S-2500' THEN S3500.GPBR_CPF_ID ELSE S3500.GPBR_PER_REF END ELSE S2100.emplid END, A.gpbr_es_ps, A.gpbr_timestamp asc)) %Concat '.XML' , '/' , '-') , '\' , '-') , '>' , '-') , '<' , '-') , ' ' , '-') FROM ps_gpbr_driver_dat A LEFT OUTER JOIN ps_gpbr_drvhq_xrf S1000 ON S1000.gpbr_driver_seq = A.gpbr_driver_seq LEFT OUTER JOIN ps_gpbr_drvest_xrf S1005 ON S1005.gpbr_driver_seq = A.gpbr_driver_seq LEFT OUTER JOIN ps_gpbr_drvwag_xrf S1010 ON S1010.gpbr_driver_seq = A.gpbr_driver_seq LEFT OUTER JOIN ps_gpbr_drvloc_xrf S1020 ON S1020.gpbr_driver_seq = A.gpbr_driver_seq LEFT OUTER JOIN ps_gpbr_drvjcd_xrf S1030 ON S1030.gpbr_driver_seq = A.gpbr_driver_seq LEFT OUTER JOIN ps_gpbr_drvshf_xrf S1050 ON S1050.gpbr_driver_seq = A.gpbr_driver_seq LEFT OUTER JOIN PS_GPBR_DRVWPL_XRF S1060 ON S1060.gpbr_driver_seq = A.gpbr_driver_seq LEFT OUTER JOIN ps_gpbr_drvjud_xrf S1070 ON S1070.gpbr_driver_seq = A.gpbr_driver_seq LEFT OUTER JOIN ps_gpbr_drvemp_xrf S2100 ON S2100.gpbr_driver_seq = A.gpbr_driver_seq LEFT OUTER JOIN ps_gpbr_drvris_xrf S2240 ON S2240.gpbr_driver_seq = A.gpbr_driver_seq LEFT OUTER JOIN PS_GPBR_DRVHS_XRF S2220 ON S2220.gpbr_driver_seq = A.gpbr_driver_seq LEFT OUTER JOIN PS_GPBR_DRVLBR_XRF S2500 ON S2500.GPBR_DRIVER_SEQ = A.GPBR_DRIVER_SEQ LEFT OUTER JOIN PS_GPBR_DRVLPT_XRF S2501 ON S2501.GPBR_DRIVER_SEQ = A.GPBR_DRIVER_SEQ LEFT OUTER JOIN PS_GPBR_DRVEXC_XRF S3500 ON S3500.GPBR_DRIVER_SEQ = A.GPBR_DRIVER_SEQ

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 GPBR_DRIVER_SEQ Number(30,0) DECIMAL(30) NOT NULL Driver Sequence
2 FILE_NAME1 Character(200) VARCHAR2(200) NOT NULL File Name