RY_EE_STG_VW

(SQL View)
 Index Back

EE Fact table input view

Large input view to populate RY_EE_FACT table, used in AE RY_EE_STG

SELECT D.BUSINESS_UNIT , D.DIALOG_ID , FI.RY_IND_ID , TFPQ.TIMEFRAME_ID %Concat '.' %Concat TFPQ.PERIOD_ID , TFPA.TIMEFRAME_ID %Concat '.' %Concat TFPA.PERIOD_ID , FI.RY_FLOW_INS_ID , CASE WHEN BBI.BO_ID IS NULL THEN 0 ELSE BBI.BO_ID END , CASE WHEN BBI.ROLE_TYPE_ID IS NULL THEN 0 ELSE BBI.ROLE_TYPE_ID END , CASE WHEN W.DEPTID IS NULL THEN ' ' ELSE W.SETID_DEPT %Concat '.' %Concat W.DEPTID END , CASE WHEN W.JOBCODE IS NULL THEN ' ' ELSE W.SETID_JOBCODE %Concat '.' %Concat W.JOBCODE END , CASE WHEN W.LOCATION IS NULL THEN ' ' ELSE W.SETID_LOCATION %Concat '.' %Concat W.LOCATION END , CASE WHEN P.SEX IS NULL THEN ' ' ELSE P.SEX END , CASE WHEN L.COUNTRY IS NULL THEN ' ' ELSE L.COUNTRY END , ' ' , FI.RY_SRVY_SCORE , CASE WHEN A.AGE_GRP_ID IS NULL THEN ' ' ELSE A.AGE_GRP_ID END , S.SCORE_RG_ID , S.SCORE_RG_DESCR , S.RY_SVY_RATING , CASE WHEN Y.YRS_SVC_GRP_ID IS NULL THEN ' ' ELSE Y.YRS_SVC_GRP_ID END , FI.ROW_LASTMANT_DTTM FROM PS_RY_DIALOG D , PS_RY_SVY_SETUP SU , PS_RY_SVY_SETUP_OB SUO , PS_SET_CNTRL_REC SRC , PS_RSF_TF_PERIOD TFPQ , PS_RSF_TF_PERIOD TFPA , PS_RY_SVY_SCORES S , PS_RY_FLOW_INST FI LEFT OUTER JOIN PS_BO_BASIC_IND BBI ON FI.RY_IND_ID = BBI.BAS_SYSID LEFT OUTER JOIN PS_RD_PERSON P ON BBI.BO_ID = P.BO_ID LEFT OUTER JOIN PS_RB_WORKER W ON P.BO_ID = W.BO_ID LEFT OUTER JOIN PS_RY_SVY_LOC_VW L ON W.SETID_LOCATION = L.SETID AND W.LOCATION = L.LOCATION LEFT OUTER JOIN PS_RY_SVY_ESRVC_VW E ON P.EMPLID = E.EMPLID LEFT OUTER JOIN PS_RY_SVY_AGE A ON %Truncate(%DecDiv(%DateDiff(P.BIRTHDATE, %CurrentDateIn), 365.2422), 0) BETWEEN A.MIN_AGE AND A.MAX_AGE LEFT OUTER JOIN PS_RY_SVY_YRS Y ON %Truncate(%DecDiv(%DateDiff(E.HIRE_DT, %CurrentDateIn), 365.2422), 0) BETWEEN Y.MIN_YEARS AND Y.MAX_YEARS WHERE FI.RY_STATUS_SRVY = 'C' AND D.RY_DIALOG_STATUS = 'A' AND SU.RY_SVY_TYPE = '1' AND SU.RY_SVY_TYPE = SUO.RY_SVY_TYPE AND SRC.SETCNTRLVALUE = D.BUSINESS_UNIT AND SRC.RECNAME = 'RA_UD_CMPGN_OBJ' AND SUO.SETID = SRC.SETID AND SUO.RA_OBJECTIVE_CD = D.RA_OBJECTIVE_CD AND NOT EXISTS ( SELECT 1 FROM PS_RY_FLOW_INST FI2 , PS_RSF_TF_PERIOD TFPQ2 WHERE FI.DIALOG_ID = FI2.DIALOG_ID AND FI.RY_IND_ID = FI2.RY_IND_ID AND FI2.RY_STATUS_SRVY = 'C' AND %DatePart(FI2.ROW_LASTMANT_DTTM) BETWEEN TFPQ2.BEGIN_DT AND TFPQ2.END_DT AND TFPQ2.TIMEFRAME_ID = SU.TIMEFRAME_ID AND TFPQ2.PERIOD_TYPE = 'Q' AND TFPQ2.RSF_PERIOD_USE IN ('A', 'U') AND TFPQ2.STATUS = 'A' AND TFPQ.TIMEFRAME_ID = TFPQ2.TIMEFRAME_ID AND TFPQ.PERIOD_ID = TFPQ2.PERIOD_ID AND FI2.ROW_LASTMANT_DTTM > FI.ROW_LASTMANT_DTTM) AND FI.DIALOG_ID = D.DIALOG_ID AND %DatePart(FI.ROW_LASTMANT_DTTM) BETWEEN TFPQ.BEGIN_DT AND TFPQ.END_DT AND TFPQ.TIMEFRAME_ID = SU.TIMEFRAME_ID AND TFPQ.PERIOD_TYPE = 'Q' AND TFPQ.RSF_PERIOD_USE IN ('A', 'U') AND TFPQ.STATUS = 'A' AND %DatePart(FI.ROW_LASTMANT_DTTM) BETWEEN TFPA.BEGIN_DT AND TFPA.END_DT AND TFPA.TIMEFRAME_ID = SU.TIMEFRAME_ID AND TFPA.PERIOD_TYPE = 'A' AND TFPA.RSF_PERIOD_USE IN ('A', 'U') AND TFPA.STATUS = 'A' AND D.DIALOG_ID = S.DIALOG_ID AND FI.RY_SRVY_SCORE BETWEEN S.RY_SVY_MIN_SCORE AND S.RY_SVY_MAX_SCORE

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 DIALOG_ID Number(18,0) DECIMAL(18) NOT NULL Dialog ID field to identify both design time and run time dialog.
3 BAS_SYSID Number(18,0) DECIMAL(18) NOT NULL Basic Data System ID
4 SID_TF_QTR Character(31) VARCHAR2(31) NOT NULL Workforce Connect - SetID Timeframe Quarter
5 SID_TF_ANN Character(31) VARCHAR2(31) NOT NULL Workforce Connect - SetID Timeframe Annual
6 RY_FLOW_INS_ID Number(18,0) DECIMAL(18) NOT NULL Dialog Flow Instance ID
7 BO_ID Signed Number(32,0) DECIMAL(31) NOT NULL Business Object ID
8 ROLE_TYPE_ID Number(12,0) DECIMAL(12) NOT NULL Role Type ID
9 SID_DEPT Character(21) VARCHAR2(21) NOT NULL Workforce Connect - SetID_Department
10 SID_JOBCODE Character(21) VARCHAR2(21) NOT NULL Workforce Connect - SetID JobCode
11 SID_LOCATION Character(21) VARCHAR2(21) NOT NULL Workforce Connect - Concatenated key for LOCATION_TBL
12 SEX Character(1) VARCHAR2(1) NOT NULL Gender of the person.
F=Female
M=Male
U=Unknown
13 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
14 REVIEW_RATING Character(1) VARCHAR2(1) NOT NULL REVIEW_RATING field used in the HCM/ CRM sync Application Messages.
15 RY_SRVY_SCORE Signed Number(19,0) DECIMAL(18) NOT NULL Survey Overall score -- overall running score for a survey instance.
16 AGE_GRP_ID Character(3) VARCHAR2(3) NOT NULL Workforce Connect - Age Group ID
17 SCORE_RG_ID Number(3,0) SMALLINT NOT NULL Workforce Connect - Score Range
18 SCORE_RG_DESCR Character(10) VARCHAR2(10) NOT NULL Workforce Connec - Years of service description.
19 RY_SVY_RATING Character(2) VARCHAR2(2) NOT NULL Survey Rating code field
20 YRS_SVC_GRP_ID Character(3) VARCHAR2(3) NOT NULL Workforce Connect - Years Employed
21 ROW_LASTMANT_DTTM DateTime(26) TIMESTAMP The date and time that the row was last updated.