GPBR_RISK03_VW

(SQL View)
 Index Back

Non Periodic Event S-2240

eSocial Stage Table This record is used to generate informations for eSocial layout S-2240

SELECT DISTINCT B.GPBR_DRIVER_SEQ ,B.GPBR_DRV_SPLIT_SEQ ,A.GPBR_EVENT_ID ,HAZ.HAZARD_ID ,HAZ.HAZARD_CODE_BRA ,CASE WHEN HAZ.HAZARD_CODE_BRA = '01.01.999' OR HAZ.HAZARD_CODE_BRA = '02.01.999' OR HAZ.HAZARD_CODE_BRA = '03.01.999' OR HAZ.HAZARD_CODE_BRA = '04.01.999' OR HAZ.HAZARD_CODE_BRA = '04.02.999' OR HAZ.HAZARD_CODE_BRA = '04.03.999' OR HAZ.HAZARD_CODE_BRA = '04.04.999' OR HAZ.HAZARD_CODE_BRA = '04.05.999' OR HAZ.HAZARD_CODE_BRA = '05.01.999' THEN %Substring(%Cast(A.DESCRLONG, LONG, CHARACTER), 1, 999) ELSE NULL END ,CASE WHEN IPE.HAZ_EVAL_TYPE_BRA = 'N' THEN '1' ELSE '2' END AS HAZ_EVAL_TYPE_BRA ,CASE WHEN IPE.HAZ_EVAL_TYPE_BRA = 'N' THEN %Truncate(IPE.HAZARD_UNITS_BRA,4) ELSE 0 END AS HAZARD_UNITS_BRA ,CASE WHEN IPE.HAZ_EVAL_TYPE_BRA = 'N' THEN CASE WHEN HAZ.HAZARD_CODE_BRA = '02.01.687' OR HAZ.HAZARD_CODE_BRA = '02.01.788' OR HAZ.HAZARD_CODE_BRA = '01.01.014' OR HAZ.HAZARD_CODE_BRA = '01.01.018' THEN %Truncate(IPE.HAZ_TOLERANCE_BRA,4) ELSE 0 END ELSE 0 END AS HAZ_TOLERANCE_BRA ,CASE WHEN IPE.HAZ_EVAL_TYPE_BRA = 'N' THEN %Substring(IPE.HAZ_UNIT_ID_BRA,1,2) ELSE ' ' END AS HAZ_UNIT_ID_BRA ,CASE WHEN IPE.HAZ_EVAL_TYPE_BRA = 'N' THEN IPE.HAZARD_TECHNIC_BRA ELSE ' ' END AS HAZARD_TECHNIC_BRA ,CASE WHEN RK.GPBR_REG_ESOCIAL <> ' ' THEN CASE WHEN IPE.QUESTION1_BRA = 'Y' THEN 'S' ELSE IPE.QUESTION1_BRA END WHEN RK.GPBR_LABOR_CAT_CD = '201' OR RK.GPBR_LABOR_CAT_CD = '202' OR RK.GPBR_LABOR_CAT_CD = '901' THEN CASE WHEN IPE.QUESTION1_BRA = 'Y' THEN 'S' ELSE IPE.QUESTION1_BRA END ELSE ' ' END AS QUESTION1_BRA ,CASE WHEN RK.GPBR_REG_ESOCIAL <> ' ' THEN CASE WHEN IPE.QUESTION2_BRA = 'Y' THEN 'S' ELSE IPE.QUESTION2_BRA END WHEN RK.GPBR_LABOR_CAT_CD = '201' OR RK.GPBR_LABOR_CAT_CD = '202' OR RK.GPBR_LABOR_CAT_CD = '901' THEN CASE WHEN IPE.QUESTION2_BRA = 'Y' THEN 'S' ELSE IPE.QUESTION2_BRA END ELSE ' ' END AS QUESTION2_BRA ,CASE WHEN RK.GPBR_REG_ESOCIAL <> ' ' THEN CASE WHEN IPE.QUESTION3_BRA = 'Y' THEN 'S' ELSE IPE.QUESTION3_BRA END WHEN RK.GPBR_LABOR_CAT_CD = '201' OR RK.GPBR_LABOR_CAT_CD = '202' THEN CASE WHEN IPE.QUESTION3_BRA = 'Y' THEN 'S' ELSE IPE.QUESTION3_BRA END ELSE ' ' END AS QUESTION3_BRA ,A.CPE_INFO_QU1_BRA ,CASE WHEN A.CPE_INFO_QU2_BRA = 'Y' THEN 'S' ELSE A.CPE_INFO_QU2_BRA END AS CPE_INFO_QU2_BRA ,A.CPE_INFO_QU3_BRA FROM PS_GPBR_STG_RISK03 A , PS_GPBR_DRIVER_DAT B , PS_HAZARD_TBL_BRA HAZ , PS_GPBR_STG_RISK RK , PS_GPBR_DRVRIS_XRF XRF , PS_EMPL_HA_IPE_DTL IPE WHERE A.GPBR_DRIVER_SEQ = B.GPBR_DRIVER_SEQ AND A.GPBR_DRV_SPLIT_SEQ = B.GPBR_DRV_SPLIT_SEQ AND XRF.GPBR_DRIVER_SEQ = B.GPBR_DRIVER_SEQ AND A.GPBR_DRIVER_SEQ = RK.GPBR_DRIVER_SEQ AND A.GPBR_DRV_SPLIT_SEQ = RK.GPBR_DRV_SPLIT_SEQ AND IPE.EMPLID = XRF.EMPLID AND IPE.EMPL_RCD = XRF.EMPL_RCD AND IPE.EFFDT = ( SELECT MAX(IPE2.EFFDT) FROM PS_EMPL_HA_IPE_DTL IPE2 WHERE IPE2.EMPLID = IPE.EMPLID AND IPE2.EMPL_RCD = IPE.EMPL_RCD AND IPE2.EFFDT <= A.GPBR_ES_PS) AND HAZ.SETID = IPE.SETID AND HAZ.HAZARD_ID = IPE.HAZARD_ID AND HAZ.EFFDT = ( SELECT MAX(HAZ2.EFFDT) FROM PS_HAZARD_TBL_BRA HAZ2 WHERE HAZ2.SETID = HAZ.SETID AND HAZ2.HAZARD_ID = HAZ.HAZARD_ID AND HAZ2.EFFDT <= IPE.EFFDT) AND B.GPBR_STATUS = 'XRDY'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 GPBR_DRIVER_SEQ Number(30,0) DECIMAL(30) NOT NULL Driver Sequence
2 GPBR_DRV_SPLIT_SEQ Number(20,0) DECIMAL(20) NOT NULL eSocial - Split Sequence
3 GPBR_EVENT_ID Character(40) VARCHAR2(40) NOT NULL eSocial - Generate ID Number Governant
4 HAZARD_ID Character(5) VARCHAR2(5) NOT NULL Hazard ID
5 HAZARD_CODE_BRA Character(9) VARCHAR2(9) NOT NULL Hazard Code BRA
6 DESCRLONG Long Character CLOB Long Description
7 HAZ_EVAL_TYPE_BRA Character(1) VARCHAR2(1) NOT NULL Hazard Evaluation Type Brazil
L=Qualitative
N=Quantitative
8 HAZARD_UNITS_BRA Number(14,5) DECIMAL(13,5) NOT NULL Hazard Units
9 HAZ_TOLERANCE_BRA Number(14,5) DECIMAL(13,5) NOT NULL Tolerance Limit
10 HAZ_UNIT_ID_BRA Character(10) VARCHAR2(10) NOT NULL Hazard Unit
11 HAZARD_TECHNIC_BRA Character(40) VARCHAR2(40) NOT NULL Hazard Technique Used Brazil
12 QUESTION1_BRA Character(1) VARCHAR2(1) NOT NULL Question 1 BRA
N=No
Y=Yes
13 QUESTION2_BRA Character(1) VARCHAR2(1) NOT NULL Question 1 BRA
N=No
Y=Yes
14 QUESTION3_BRA Character(1) VARCHAR2(1) NOT NULL Question 1 BRA
N=No
Y=Yes
15 CPE_INFO_QU1_BRA Character(1) VARCHAR2(1) NOT NULL CPE Information Question 1 Does the Company implement collective protection measures (CPEs) to eliminate or reduce worker�s exposure to the risk factor?
0=Not Applicable
1=Not Implement
2=Implement
16 CPE_INFO_QU2_BRA Character(1) VARCHAR2(1) NOT NULL CPE Information Question 2 Are CPEs effective in neutralizing risks to the worker?
N=No
Y=Yes
17 CPE_INFO_QU3_BRA Character(1) VARCHAR2(1) NOT NULL CPE Information Question 3 Utilization of IPE: Mandatory
0=Not Applicable
1=Not Used
2=Used