GPSG_MOM_PR_VW

(SQL View)
 Index Back

FWL Reconcilitation Data

Record for FWL Reconcilitation Data

SELECT A.GPSG_CPF_EMPLR_REF ,A.CAL_PRD_ID ,A.GPSG_UPLOAD_DT ,A.FILENAME ,A.NATIONAL_ID ,A.SEQ_NBR ,B.DEPTID , C.DESCR ,B.SETID_DEPT ,B.EMPLID ,A.EMPLNAME ,B.GP_PAYGROUP ,B.CALC_RSLT_VAL ,A.GPSG_MOM_AMOUNT ,B.CAL_RUN_ID ,B.GPSG_MOM_VAR_AMT ,A.GPSG_MOM_LEVY_RATE ,A.GPSG_MOM_LEVY_DT ,A.GPSG_MOM_LV_EDT ,A.WORKER_TYPE_SGP ,B.GPSG_FWL_ERR_FLAG FROM PS_GPSG_MOM_DATA A , PS_GPSG_MOM_PR_DAT B LEFT JOIN PS_DEPT_TBL C ON (C.DEPTID=B.DEPTID AND C.SETID=B.SETID_DEPT ) WHERE B.GPSG_MOM_VAR_AMT<>0 AND A.GPSG_CPF_EMPLR_REF=B.GPSG_CPF_EMPLR_REF AND A.CAL_PRD_ID = B.CAL_PRD_ID AND A.GPSG_UPLOAD_DT =B.GPSG_UPLOAD_DT AND A.FILENAME=B.FILENAME AND A.NATIONAL_ID=B.NATIONAL_ID AND A.GPSG_MOM_LEVY_DT=B.GPSG_MOM_LEVY_DT AND ((B.DEPTID<>' ' AND C.EFFDT=( SELECT MAX(D.EFFDT) FROM PS_DEPT_TBL D WHERE D.DEPTID=B.DEPTID AND D.SETID=B.SETID_DEPT ) OR B.DEPTID=' ')) UNION SELECT B.GPSG_CPF_EMPLR_REF , B.CAL_PRD_ID , B.GPSG_UPLOAD_DT , B.FILENAME , B.NATIONAL_ID , 99999 , B.DEPTID , C.DESCR , B.SETID_DEPT , B.EMPLID , A.NAME , B.GP_PAYGROUP , B.CALC_RSLT_VAL , 0 , B.CAL_RUN_ID , B.GPSG_MOM_VAR_AMT , 0 ,%DateNull ,%DateNull , W.WORKER_TYPE_SGP , B.GPSG_FWL_ERR_FLAG FROM PS_NAMES_VW A , PS_GPSG_MOM_PR_DAT B LEFT JOIN PS_DEPT_TBL C ON (C.DEPTID = B.DEPTID AND C.SETID = B.SETID_DEPT) , PS_GPSG_CITIZENSHP W WHERE A.EMPLID = B.EMPLID AND A.NAME_TYPE = 'PRI' AND A.EMPLID = W.EMPLID AND B.GPSG_MOM_VAR_AMT <> 0 AND B.GPSG_FWL_ERR_FLAG = 'C' AND ((B.DEPTID <> ' ' AND C.EFFDT = ( SELECT MAX(D.EFFDT) FROM PS_DEPT_TBL D WHERE D.DEPTID = B.DEPTID AND D.SETID = B.SETID_DEPT) OR B.DEPTID = ' '))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 GPSG_CPF_EMPLR_REF Character(15) VARCHAR2(15) NOT NULL CPF Employer Reference no.
2 CAL_PRD_ID Character(10) VARCHAR2(10) NOT NULL Calendar Period ID
3 GPSG_UPLOAD_DT Date(10) DATE GPSG_UPLOAD_DT
4 FILENAME Character(80) VARCHAR2(80) NOT NULL File Name
5 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
6 SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number
7 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
8 DESCR Character(30) VARCHAR2(30) NOT NULL Description
9 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
10 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
11 EMPLNAME Character(50) VARCHAR2(50) NOT NULL EmplName
12 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
13 CALC_RSLT_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Calculation Numeric Result
14 GPSG_MOM_AMOUNT Signed Number(20,6) DECIMAL(18,6) NOT NULL Singapore Payroll MoM Amount
15 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id
16 GPSG_MOM_VAR_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL GPSG_MOM_VAR_AMT
17 GPSG_MOM_LEVY_RATE Signed Number(20,6) DECIMAL(18,6) NOT NULL Singapore Payroll MoM Levy Rate
18 GPSG_MOM_LEVY_DT Date(10) DATE Singapore Payroll MoM Levy Date
19 GPSG_MOM_LV_EDT Date(10) DATE Global Payroll Singapore MoM Levy End Date
20 WORKER_TYPE_SGP Character(2) VARCHAR2(2) NOT NULL Worker's Type - Singapore
21 GPSG_FWL_ERR_FLAG Character(1) VARCHAR2(1) NOT NULL FWL Error flag
A=FWL amount mismatch
B=Missing FWL deduction
C=Missing FWL file entry