GPSG_IR8S_AM_VW

(SQL View)
 Index Back

Sgp IR8S data

Data extracted for Singapore IR8S reporting

SELECT A.EMPLID , A.BALANCE_GRP_NUM , A.GPSG_CPY_TAX_REF , A.GPSG_TAX_YEAR , A.SEQNBR , A.GPSG_MONTHCD , A.GPSG_A_ORD_WAGES , A.GPSG_A_EE_ORD_CPF , A.GPSG_A_ER_ORD_CPF , A.GPSG_A_APP_FND_ORD , A.GPSG_ADD_WAGES , A.GPSG_A_EE_ADD_CPF , A.GPSG_A_ER_ADD_CPF , A.GPSG_A_APP_FND_ADD FROM PS_GPSG_IR8S_AM_MT A WHERE A.SEQNBR = ( SELECT MAX(A_S.SEQNBR) FROM PS_GPSG_IR8S_AM_MT A_S WHERE A_S.GPSG_CPY_TAX_REF = A.GPSG_CPY_TAX_REF AND A_S.GPSG_TAX_YEAR = A.GPSG_TAX_YEAR AND A_S.EMPLID = A.EMPLID AND A_S.BALANCE_GRP_NUM = A.BALANCE_GRP_NUM AND A_S.GPSG_MONTHCD = A.GPSG_MONTHCD AND A_S.SEQNBR < ( SELECT MAX(B_S.SEQNBR) FROM PS_GPSG_IR8S_AM_MT B_S WHERE B_S.GPSG_CPY_TAX_REF = A.GPSG_CPY_TAX_REF AND B_S.GPSG_TAX_YEAR = A.GPSG_TAX_YEAR AND B_S.EMPLID = A.EMPLID AND B_S.BALANCE_GRP_NUM = A.BALANCE_GRP_NUM AND B_S.GPSG_MONTHCD = A.GPSG_MONTHCD)) AND EXISTS( SELECT C.EMPLID FROM PS_GPSG_IR8A_A_TBL C WHERE C.GPSG_CPY_TAX_REF = A.GPSG_CPY_TAX_REF AND C.GPSG_TAX_YEAR = A.GPSG_TAX_YEAR AND C.EMPLID = A.EMPLID AND C.BALANCE_GRP_NUM = A.BALANCE_GRP_NUM AND C.GPSG_8A_STATUS = 'W')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 BALANCE_GRP_NUM Character(3) VARCHAR2(3) NOT NULL This field will be used to determine the grouping of balances from the different jobs of the employee.
3 GPSG_CPY_TAX_REF Character(11) VARCHAR2(11) NOT NULL Singapore Company Tax Reference Number
4 GPSG_TAX_YEAR Number(4,0) SMALLINT NOT NULL Tax Year for Singapore IRAS Reporting
5 SEQNBR Number(5,0) INTEGER NOT NULL Sequence Number
6 GPSG_MONTHCD Character(2) VARCHAR2(2) NOT NULL Month code, 01 thru 12, plus 99 for totals.
01=January
02=February
03=March
04=April
05=May
06=June
07=July
08=August
09=September
10=October
11=November
12=December
99=Total
7 GPSG_A_ORD_WAGES Signed Number(17,2) DECIMAL(15,2) NOT NULL Ordinary Wages
8 GPSG_A_EE_ORD_CPF Signed Number(17,2) DECIMAL(15,2) NOT NULL MTD employee ordinary CPF for the CPF reporting.
9 GPSG_A_ER_ORD_CPF Signed Number(17,2) DECIMAL(15,2) NOT NULL Employer contribution to CPF for Ordinary Wages
10 GPSG_A_APP_FND_ORD Signed Number(17,2) DECIMAL(15,2) NOT NULL Employer ordinary contributions to an approved fund other than CPF.
11 GPSG_ADD_WAGES Signed Number(17,2) DECIMAL(15,2) NOT NULL MTD additional wages for the CPF reporting.
12 GPSG_A_EE_ADD_CPF Signed Number(17,2) DECIMAL(15,2) NOT NULL MTD employee additional CPF for the CPF reporting.
13 GPSG_A_ER_ADD_CPF Signed Number(17,2) DECIMAL(15,2) NOT NULL Employer Additional CPF Contributions
14 GPSG_A_APP_FND_ADD Signed Number(17,2) DECIMAL(15,2) NOT NULL Employer additional contributions to an approved fund other than CPF.