|
SELECT A.gpbr_driver_seq , ROW_NUMBER() OVER ( PARTITION BY A.COMPANY , A.GPBR_EVENT_CD , CASE WHEN S1300.gpbr_event_cd IS NOT NULL AND S1300.GPBR_EVENT_CD = 'S-1300' THEN A.COMPANY WHEN S1300.gpbr_event_cd IS NOT NULL AND S1300.gpbr_event_cd = 'S-3000' THEN A.COMPANY %Concat '-1300' WHEN S1280.gpbr_event_cd IS NOT NULL AND S1280.GPBR_EVENT_CD = 'S-1280' THEN A.COMPANY WHEN S1280.gpbr_event_cd IS NOT NULL AND S1280.gpbr_event_cd = 'S-3000' THEN A.COMPANY %Concat '-1280' WHEN S12x0.gpbr_event_cd IS NOT NULL AND (S12x0.GPBR_EVENT_CD = 'S-1200' OR S12x0.GPBR_EVENT_CD = 'S-1210') THEN A.COMPANY %Concat '_' %Concat S12x0.EMPLID WHEN S12x0.gpbr_event_cd IS NOT NULL AND S12x0.GPBR_EVENT_CD = 'S-3000' THEN CASE WHEN 0 < ( SELECT COUNT(*) FROM PS_GPBR_DRIVER_DAT B WHERE B.GPBR_DRIVER_SEQ = S12x0.GPBR_PRE_DRV_SEQ) THEN A.COMPANY %Concat '_' %Concat S12x0.EMPLID %Concat ( SELECT CASE WHEN B.GPBR_EVENT_CD = 'S-1200' THEN '-1200' ELSE '-1210' END FROM PS_GPBR_DRIVER_DAT B WHERE B.GPBR_DRIVER_SEQ = A.GPBR_PRE_DRV_SEQ) ELSE ' ' END ELSE ' ' END, CASE WHEN S1300.gpbr_event_cd IS NOT NULL THEN CASE WHEN S1300.GPBR_ES_PERIOD_TP = '1' THEN '_M' ELSE '_A' END WHEN S1280.gpbr_event_cd IS NOT NULL THEN CASE WHEN S1280.GPBR_ES_PERIOD_TP = '1' THEN '_M' ELSE '_A' END WHEN S12x0.gpbr_event_cd IS NOT NULL THEN CASE WHEN S12x0.GPBR_PAYROLL_TP = '1' THEN '_M' ELSE '_A' END ELSE ' ' END, A.GPBR_ES_PS ORDER BY A.COMPANY , A.GPBR_EVENT_CD , CASE WHEN S1300.gpbr_event_cd IS NOT NULL AND S1300.GPBR_EVENT_CD = 'S-1300' THEN A.COMPANY WHEN S1300.gpbr_event_cd IS NOT NULL AND S1300.gpbr_event_cd = 'S-3000' THEN A.COMPANY %Concat '-1300' WHEN S1280.gpbr_event_cd IS NOT NULL AND S1280.GPBR_EVENT_CD = 'S-1280' THEN A.COMPANY WHEN S1280.gpbr_event_cd IS NOT NULL AND S1280.gpbr_event_cd = 'S-3000' THEN A.COMPANY %Concat '-1280' WHEN S12x0.gpbr_event_cd IS NOT NULL AND (S12x0.GPBR_EVENT_CD = 'S-1200' OR S12x0.GPBR_EVENT_CD = 'S-1210') THEN A.COMPANY %Concat '_' %Concat S12x0.EMPLID WHEN S12x0.gpbr_event_cd IS NOT NULL AND S12x0.GPBR_EVENT_CD = 'S-3000' THEN CASE WHEN 0 < ( SELECT COUNT(*) FROM PS_GPBR_DRIVER_DAT B WHERE B.GPBR_DRIVER_SEQ = A.GPBR_PRE_DRV_SEQ) THEN A.COMPANY %Concat '_' %Concat S12x0.EMPLID %Concat ( SELECT CASE WHEN B.GPBR_EVENT_CD = 'S-1200' THEN '-1200' ELSE '-1210' END FROM PS_GPBR_DRIVER_DAT B WHERE B.GPBR_DRIVER_SEQ = A.GPBR_PRE_DRV_SEQ) ELSE ' ' END ELSE ' ' END, CASE WHEN S1300.gpbr_event_cd IS NOT NULL THEN CASE WHEN S1300.GPBR_ES_PERIOD_TP = '1' THEN '_M' ELSE '_A' END WHEN S1280.gpbr_event_cd IS NOT NULL THEN CASE WHEN S1280.GPBR_ES_PERIOD_TP = '1' THEN '_M' ELSE '_A' END WHEN S12x0.gpbr_event_cd IS NOT NULL THEN CASE WHEN S12x0.GPBR_PAYROLL_TP = '1' THEN '_M' ELSE '_A' END ELSE ' ' END, A.GPBR_ES_PS , A.GPBR_TIMESTAMP ASC) , Replace( Replace( Replace (Replace (Replace (Replace (Replace (Replace( Replace( Replace( Replace( A.company %Concat '_' %Concat A.gpbr_event_cd %Concat '_' %Concat CASE WHEN S1300.gpbr_event_cd IS NOT NULL AND S1300.GPBR_EVENT_CD = 'S-1300' THEN A.COMPANY WHEN S1300.gpbr_event_cd IS NOT NULL AND S1300.gpbr_event_cd = 'S-3000' THEN A.COMPANY %Concat '-1300' WHEN S1280.gpbr_event_cd IS NOT NULL AND S1280.GPBR_EVENT_CD = 'S-1280' THEN A.COMPANY WHEN S1280.gpbr_event_cd IS NOT NULL AND S1280.gpbr_event_cd = 'S-3000' THEN A.COMPANY %Concat '-1280' WHEN S12x0.gpbr_event_cd IS NOT NULL AND (S12x0.GPBR_EVENT_CD = 'S-1200' OR S12x0.GPBR_EVENT_CD = 'S-1210') THEN A.COMPANY %Concat '_' %Concat S12x0.EMPLID WHEN S12x0.gpbr_event_cd IS NOT NULL AND S12x0.GPBR_EVENT_CD = 'S-3000' THEN CASE WHEN 0 < ( SELECT COUNT(*) FROM PS_GPBR_DRIVER_DAT B WHERE B.GPBR_DRIVER_SEQ = A.GPBR_PRE_DRV_SEQ) THEN A.COMPANY %Concat '_' %Concat S12x0.EMPLID %Concat ( SELECT CASE WHEN B.GPBR_EVENT_CD = 'S-1200' THEN '-1200' ELSE '-1210' END FROM PS_GPBR_DRIVER_DAT B WHERE B.GPBR_DRIVER_SEQ = A.GPBR_PRE_DRV_SEQ) ELSE ' ' END ELSE ' ' END %Concat CASE WHEN S1300.gpbr_event_cd IS NOT NULL THEN CASE WHEN S1300.GPBR_ES_PERIOD_TP = '1' THEN '_M' ELSE '_A' END WHEN S1280.gpbr_event_cd IS NOT NULL THEN CASE WHEN S1280.GPBR_ES_PERIOD_TP = '1' THEN '_M' ELSE '_A' END WHEN S12x0.gpbr_event_cd IS NOT NULL THEN CASE WHEN S12x0.GPBR_PAYROLL_TP = '1' THEN '_M' ELSE '_A' END ELSE ' ' END %Concat '_' %Concat %Sql(funclib_hr_date_yymmdd, A.gpbr_es_ps) %Concat '_' %Concat ROW_NUMBER() OVER ( PARTITION BY A.company , A.gpbr_event_cd , CASE WHEN S1300.gpbr_event_cd IS NOT NULL AND S1300.GPBR_EVENT_CD = 'S-1300' THEN A.COMPANY WHEN S1300.gpbr_event_cd IS NOT NULL AND S1300.gpbr_event_cd = 'S-3000' THEN A.COMPANY %Concat '-1300' WHEN S1280.gpbr_event_cd IS NOT NULL AND S1280.GPBR_EVENT_CD = 'S-1280' THEN A.COMPANY WHEN S1280.gpbr_event_cd IS NOT NULL AND S1280.gpbr_event_cd = 'S-3000' THEN A.COMPANY %Concat '-1280' WHEN S12x0.gpbr_event_cd IS NOT NULL AND (S12x0.GPBR_EVENT_CD = 'S-1200' OR S12x0.GPBR_EVENT_CD = 'S-1210') THEN A.COMPANY %Concat '_' %Concat S12x0.EMPLID WHEN S12x0.gpbr_event_cd IS NOT NULL AND S12x0.GPBR_EVENT_CD = 'S-3000' THEN CASE WHEN 0 < ( SELECT COUNT(*) FROM PS_GPBR_DRIVER_DAT B WHERE B.GPBR_DRIVER_SEQ = A.GPBR_PRE_DRV_SEQ) THEN A.COMPANY %Concat '_' %Concat S12x0.EMPLID %Concat ( SELECT CASE WHEN B.GPBR_EVENT_CD = 'S-1200' THEN '-1200' ELSE '-1210' END FROM PS_GPBR_DRIVER_DAT B WHERE B.GPBR_DRIVER_SEQ = A.GPBR_PRE_DRV_SEQ) ELSE ' ' END ELSE ' ' END, CASE WHEN S1300.gpbr_event_cd IS NOT NULL THEN CASE WHEN S1300.GPBR_ES_PERIOD_TP = '1' THEN '_M' ELSE '_A' END WHEN S1280.gpbr_event_cd IS NOT NULL THEN CASE WHEN S1280.GPBR_ES_PERIOD_TP = '1' THEN '_M' ELSE '_A' END WHEN S12x0.gpbr_event_cd IS NOT NULL THEN CASE WHEN S12x0.GPBR_PAYROLL_TP = '1' THEN '_M' ELSE '_A' END ELSE ' ' END, A.gpbr_es_ps ORDER BY A.GPBR_DRIVER_SEQ , A.company , A.gpbr_event_cd , CASE WHEN S1300.gpbr_event_cd IS NOT NULL AND S1300.gpbr_event_cd = 'S-1300' THEN A.COMPANY WHEN S1300.gpbr_event_cd IS NOT NULL AND S1300.gpbr_event_cd = 'S-3000' THEN A.COMPANY %Concat '-1300' WHEN S1280.gpbr_event_cd IS NOT NULL AND S1280.GPBR_EVENT_CD = 'S-1280' THEN A.COMPANY WHEN S1280.gpbr_event_cd IS NOT NULL AND S1280.gpbr_event_cd = 'S-3000' THEN A.COMPANY %Concat '-1280' WHEN S12x0.gpbr_event_cd IS NOT NULL AND (S12x0.GPBR_EVENT_CD = 'S-1200' OR S12x0.GPBR_EVENT_CD = 'S-1210') THEN A.COMPANY %Concat '_' %Concat S12x0.EMPLID WHEN S12x0.gpbr_event_cd IS NOT NULL AND S12x0.GPBR_EVENT_CD = 'S-3000' THEN CASE WHEN 0 < ( SELECT COUNT(*) FROM PS_GPBR_DRIVER_DAT B WHERE B.GPBR_DRIVER_SEQ = A.GPBR_PRE_DRV_SEQ) THEN A.COMPANY %Concat '_' %Concat S12x0.EMPLID %Concat ( SELECT CASE WHEN B.GPBR_EVENT_CD = 'S-1200' THEN '-1200' ELSE '-1210' END FROM PS_GPBR_DRIVER_DAT B WHERE B.GPBR_DRIVER_SEQ = A.GPBR_PRE_DRV_SEQ) ELSE ' ' END ELSE ' ' END, CASE WHEN S1300.gpbr_event_cd IS NOT NULL THEN CASE WHEN S1300.GPBR_ES_PERIOD_TP = '1' THEN '_M' ELSE '_A' END WHEN S1280.gpbr_event_cd IS NOT NULL THEN CASE WHEN S1280.GPBR_ES_PERIOD_TP = '1' THEN '_M' ELSE '_A' END WHEN S12x0.gpbr_event_cd IS NOT NULL THEN CASE WHEN S12x0.GPBR_PAYROLL_TP = '1' THEN '_M' ELSE '_A' END ELSE ' ' END, A.gpbr_es_ps , A.gpbr_timestamp ASC) %Concat '.XML' , '/' , '-') , '\' , '-' ) , '>' , '-') , '<' , '-' ) , ' ' , '-') , '�' , 'A') , '�' , 'E') , '�' , 'I') , '�' , 'O') , '�' , 'U') , '�' , 'C') FROM ps_gpbr_driver_dat A LEFT OUTER JOIN ps_gpbr_drveuc_xrf S1300 ON S1300.gpbr_driver_seq = A.gpbr_driver_seq LEFT OUTER JOIN ps_gpbr_drvcip_xrf S1280 ON S1280.gpbr_driver_seq = A.gpbr_driver_seq LEFT OUTER JOIN PS_GPBR_DRVCMP_XRF S12x0 ON S12x0.gpbr_driver_seq = A.gpbr_driver_seq WHERE (S1300.gpbr_event_cd IS NOT NULL OR S1280.gpbr_event_cd IS NOT NULL OR S12x0.gpbr_event_cd IS NOT NULL) AND ( S1300.gpbr_event_cd IS NULL OR (S1300.gpbr_union_cd1 = ( SELECT MIN(S1300_2.gpbr_union_cd1) FROM ps_gpbr_drveuc_xrf S1300_2 WHERE S1300_2.gpbr_driver_seq = S1300.gpbr_driver_seq) AND S1300.GPBR_CONTRIB_TYPE = ( SELECT MIN(S1300_3.GPBR_CONTRIB_TYPE) FROM ps_gpbr_drveuc_xrf S1300_3 WHERE S1300_3.gpbr_driver_seq = S1300.gpbr_driver_seq AND S1300_3.gpbr_union_cd1 = S1300.gpbr_union_cd1) ) )
|
GPBR_DRIVER_SEQ