GPTH_WCF_VW

(SQL View)
 Index Back

Used in the WCF Query

GPTH_WCF_VW is used in the WCF Query

SELECT PYMT_DT ,0 AS GPTH_WCF_NUM_EMP ,PROCESS_INSTANCE ,COMPANY ,GPTH_SI_BRANCH_AP , GPTH_SI_ER_ACCT ,DESCR ,GPTH_SI_BUS_CODE ,GPTH_SI_EE_PER ,PIN_CUSTOM1 ,COMP_FREQUENCY ,SUM(CALC_RSLT_VAL) AS CALC_RSLT_VAL FROM PS_GPTH_WCF_DTL_VW GROUP BY PYMT_DT ,PIN_CUSTOM1 ,COMP_FREQUENCY ,PROCESS_INSTANCE ,COMPANY ,GPTH_SI_BRANCH_AP ,GPTH_SI_ER_ACCT ,DESCR ,GPTH_SI_BUS_CODE ,GPTH_SI_EE_PER UNION SELECT MIN(D.PYMT_DT) ,0 AS GPTH_WCF_NUM_EMP ,D.PROCESS_INSTANCE ,D.COMPANY ,D.GPTH_SI_BRANCH_AP ,D.GPTH_SI_ER_ACCT ,D.DESCR ,D.GPTH_SI_BUS_CODE ,D.GPTH_SI_EE_PER ,'SAL BAS EARNMI' AS PIN_CUSTOM1 ,'M' ,MIN(C.COMPRATE) FROM PS_COMPENSATION C , PS_GPTH_WCF_DTL_VW D WHERE C.EMPLID=D.EMPLID AND C.COMP_FREQUENCY='M' AND C.COMP_RATECD='KTBS01' AND C.EFFDT=( SELECT MAX(C2.EFFDT) FROM PS_COMPENSATION C2 WHERE C.EMPLID=C2.EMPLID AND C2.EFFDT<=D.PYMT_DT) AND D.PIN_CUSTOM1='SAL BAS EARN' GROUP BY D.PROCESS_INSTANCE,D.COMPANY,D.GPTH_SI_BRANCH_AP,D.GPTH_SI_ER_ACCT ,D.DESCR ,D.GPTH_SI_BUS_CODE ,D.GPTH_SI_EE_PER UNION SELECT MIN(D.PYMT_DT) ,0 AS GPTH_WCF_NUM_EMP ,D.PROCESS_INSTANCE ,D.COMPANY ,D.GPTH_SI_BRANCH_AP ,D.GPTH_SI_ER_ACCT ,D.DESCR ,D.GPTH_SI_BUS_CODE ,D.GPTH_SI_EE_PER ,'SAL BAS EARNMI_D' AS PIN_CUSTOM1 ,'M' ,MIN(C.COMPRATE) FROM PS_COMPENSATION C , PS_GPTH_WCF_DTL_VW D WHERE C.EMPLID=D.EMPLID AND C.COMP_FREQUENCY='D' AND C.COMP_RATECD='KTBS01' AND C.EFFDT=( SELECT MAX(C2.EFFDT) FROM PS_COMPENSATION C2 WHERE C.EMPLID=C2.EMPLID AND C2.EFFDT<=D.PYMT_DT) AND D.PIN_CUSTOM1='SAL BAS EARN' GROUP BY D.PROCESS_INSTANCE,D.COMPANY,D.GPTH_SI_BRANCH_AP,D.GPTH_SI_ER_ACCT ,D.DESCR ,D.GPTH_SI_BUS_CODE ,D.GPTH_SI_EE_PER UNION SELECT PYMT_DT ,COUNT(DISTINCT EMPLID) AS GPTH_WCF_NUM_EMP ,PROCESS_INSTANCE ,COMPANY ,GPTH_SI_BRANCH_AP , GPTH_SI_ER_ACCT ,DESCR ,GPTH_SI_BUS_CODE ,GPTH_SI_EE_PER ,'TOTAL WAGE' AS PIN_CUSTOM1 ,'M' ,SUM(CALC_RSLT_VAL) AS CALC_RSLT_VAL FROM PS_GPTH_WCF_DTL_VW GROUP BY PYMT_DT ,PROCESS_INSTANCE ,COMPANY , GPTH_SI_BRANCH_AP ,GPTH_SI_ER_ACCT ,DESCR ,GPTH_SI_BUS_CODE ,GPTH_SI_EE_PER UNION SELECT MIN(PYMT_DT) ,COUNT(DISTINCT EMPLID) AS GPTH_WCF_NUM_EMP ,PROCESS_INSTANCE ,COMPANY ,GPTH_SI_BRANCH_AP ,GPTH_SI_ER_ACCT ,DESCR ,GPTH_SI_BUS_CODE ,GPTH_SI_EE_PER ,'TOTAL EE' AS PIN_CUSTOM1 ,'M' ,SUM(CALC_RSLT_VAL) AS CALC_RSLT_VAL FROM PS_GPTH_WCF_DTL_VW GROUP BY PROCESS_INSTANCE ,COMPANY ,GPTH_SI_BRANCH_AP ,GPTH_SI_ER_ACCT ,DESCR ,GPTH_SI_BUS_CODE ,GPTH_SI_EE_PER UNION SELECT PYMT_DT ,0 AS GPTH_WCF_NUM_EMP ,PROCESS_INSTANCE ,COMPANY ,GPTH_SI_BRANCH_AP , GPTH_SI_ER_ACCT ,DESCR ,GPTH_SI_BUS_CODE ,GPTH_SI_EE_PER , 'EXCEED 20000' AS PIN_CUSTOM1 ,'M' , SUM(CALC_RSLT_VAL-20000) AS CALC_RSLT_VAL FROM PS_GPTH_WCF_TAL_VW WHERE CALC_RSLT_VAL > 20000 AND PIN_CUSTOM1='SAL RIR TAL MTD' GROUP BY PYMT_DT,PROCESS_INSTANCE,COMPANY, GPTH_SI_BRANCH_AP,GPTH_SI_ER_ACCT,DESCR,GPTH_SI_BUS_CODE,GPTH_SI_EE_PER

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 PYMT_DT Date(10) DATE Payment Date
2 GPTH_WCF_NUM_EMP Number(10,0) DECIMAL(10) NOT NULL This field will be used to store number information for employees
3 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
4 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
5 GPTH_SI_BRANCH_AP Character(40) VARCHAR2(40) NOT NULL This field will be used to store social security area/province information.
6 GPTH_SI_ER_ACCT Character(10) VARCHAR2(10) NOT NULL GPTH_SI_ER_ACCT
7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
8 GPTH_SI_BUS_CODE Character(10) VARCHAR2(10) NOT NULL This field will be used to store total business type code information.
9 GPTH_SI_EE_PER Number(5,2) DECIMAL(4,2) NOT NULL Employee Contribution Rate
10 PIN_CUSTOM1 Character(20) VARCHAR2(20) NOT NULL Customer FIelds
11 COMP_FREQUENCY Character(5) VARCHAR2(5) NOT NULL Compensation Frequency
A=Annual
B=Biweekly
C=Contract
D=Daily
H=Hourly
M=Monthly
S=Semimonthly
W=Weekly

Prompt Table: COMP_FREQ_VW

12 CALC_RSLT_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Calculation Numeric Result