GPES_SEG_TRG_VW

(SQL View)
Index Back

Payee Segmentation Triggers

Payee Segmentation Triggers

SELECT A.EMPLID , A.EMPL_RCD , A.COUNTRY , CASE WHEN A.TRGR_EVENT_ID = 'CATEGORDUM' THEN 'CATEGORZN' WHEN A.TRGR_EVENT_ID = 'RISK_CODUM' THEN 'RISK_CODE' WHEN A.TRGR_EVENT_ID = 'COMPRATDUM' THEN 'COMPRATE' WHEN A.TRGR_EVENT_ID = 'TAX_LOCDUM' THEN 'TAX_LOCTN' ELSE A.TRGR_EVENT_ID END , A.TRGR_EFFDT , MIN(A.TRGR_CREATE_TS) , MIN(X.SEG_TRGR_STATUS) , A.CAL_RUN_ID FROM PS_GP_SEG_TRGR A , PS_GP_SEG_TRGR X WHERE A.COUNTRY ='ESP' AND A.COUNTRY = X.COUNTRY AND A.EMPLID = X.EMPLID AND A.EMPL_RCD = X.EMPL_RCD AND A.TRGR_EVENT_ID = X.TRGR_EVENT_ID AND A.TRGR_EFFDT = X.TRGR_EFFDT AND NOT EXISTS ( SELECT DISTINCT 'X' FROM PS_GP_SEG_TRGR B , PS_GP_CAL_RUN C WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.COUNTRY = B.COUNTRY AND A.TRGR_EVENT_ID = B.TRGR_EVENT_ID AND A.TRGR_EFFDT = B.TRGR_EFFDT AND B.CAL_RUN_ID = C.CAL_RUN_ID AND A.TRGR_CREATE_TS > B.TRGR_CREATE_TS AND C.RUN_FINALIZED_IND = 'Y') GROUP BY A.EMPLID, A.EMPL_RCD, A.COUNTRY, A.TRGR_EVENT_ID, A.TRGR_EFFDT, A.CAL_RUN_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
3 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
4 TRGR_EVENT_ID Character(10) VARCHAR2(10) NOT NULL Trigger Event ID
5 TRGR_EFFDT Date(10) DATE NOT NULL Trigger Effective Date

Default Value: %Date

6 TRGR_CREATE_TS DateTime(26) TIMESTAMP NOT NULL Trigger Creation Timestamp
7 SEG_TRGR_STATUS Character(1) VARCHAR2(1) NOT NULL Segmentation Trigger Status
A=Active
C=Cancelled

Default Value: A

8 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id