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 ALL 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 ALL 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 ALL 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 ALL 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 ALL SELECT A.PYMT_DT ,0 AS GPTH_WCF_NUM_EMP ,A.PROCESS_INSTANCE ,A.COMPANY ,A.GPTH_SI_BRANCH_AP , A.GPTH_SI_ER_ACCT ,A.DESCR ,A.GPTH_SI_BUS_CODE ,A.GPTH_SI_EE_PER , 'EXCEED 20000' AS PIN_CUSTOM1 ,'M' , SUM(A.CALC_RSLT_VAL+B.CALC_RSLT_VAL-20000) AS CALC_RSLT_VAL FROM PS_GPTH_WCF_DTL_VW A , PS_GPTH_WCF_TRO_VW B WHERE A.PIN_CUSTOM1='SAL REG BAS MTD' AND A.COMPANY=B.COMPANY AND A.EMPLID=B.EMPLID AND A.PROCESS_INSTANCE=B.PROCESS_INSTANCE AND A.PYMT_DT=B.PYMT_DT AND A.GPTH_SI_BRANCH_AP=B.GPTH_SI_BRANCH_AP AND B.COMP_FREQUENCY='M' AND A.CALC_RSLT_VAL+B.CALC_RSLT_VAL>20000 GROUP BY A.PYMT_DT,A.PROCESS_INSTANCE,A.COMPANY, A.GPTH_SI_BRANCH_AP,A.GPTH_SI_ER_ACCT,A.DESCR,A.GPTH_SI_BUS_CODE,A.GPTH_SI_EE_PER UNION ALL SELECT A.PYMT_DT ,0 AS GPTH_WCF_NUM_EMP ,A.PROCESS_INSTANCE ,A.COMPANY ,A.GPTH_SI_BRANCH_AP , A.GPTH_SI_ER_ACCT ,A.DESCR ,A.GPTH_SI_BUS_CODE ,A.GPTH_SI_EE_PER ,'EXCEED 20000' AS PIN_CUSTOM1 ,'M' ,SUM(A.CALC_RSLT_VAL-20000) AS CALC_RSLT_VAL FROM PS_GPTH_WCF_DTL_VW A WHERE A.PIN_CUSTOM1='SAL REG BAS MTD' AND A.CALC_RSLT_VAL>20000 AND NOT EXISTS ( SELECT DISTINCT EMPLID FROM PS_GPTH_WCF_TRO_VW B WHERE A.COMPANY=B.COMPANY AND A.EMPLID=B.EMPLID AND A.PROCESS_INSTANCE=B.PROCESS_INSTANCE AND A.PYMT_DT=B.PYMT_DT AND A.GPTH_SI_BRANCH_AP=B.GPTH_SI_BRANCH_AP) 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 ALL SELECT A.PYMT_DT ,0 AS GPTH_WCF_NUM_EMP ,A.PROCESS_INSTANCE ,A.COMPANY ,A.GPTH_SI_BRANCH_AP , A.GPTH_SI_ER_ACCT ,A.DESCR ,A.GPTH_SI_BUS_CODE ,A.GPTH_SI_EE_PER ,'RETRO SALARY' AS PIN_CUSTOM1 ,'M' ,SUM(A.CALC_RSLT_VAL) AS CALC_RSLT_VAL FROM PS_GPTH_WCF_TRO_VW A GROUP BY PYMT_DT ,PROCESS_INSTANCE ,COMPANY ,GPTH_SI_BRANCH_AP ,GPTH_SI_ER_ACCT ,DESCR ,GPTH_SI_BUS_CODE ,GPTH_SI_EE_PER

  • Related Language Record: GPTH_WCF_VW_LNG

    • # 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