NDT_FCST_SVGS(SQL View) |
Index Back |
|---|---|
NDT Forecast SavingsNDT_FCST_SVGS is a view that joins data from the Personal Data, Current Job, Savings Plan, Pay Calendar and NDT Run Control Tables. It is an internal select-only view used by NDT002 and NDT003 when projecting 401K and 401M deferred amounts. |
| SELECT A.EMPLID ,B.ANNL_BENEF_BASE_RT ,B.ANNUAL_RT ,C.SAVINGS_COVRG ,C.FLAT_DED_AMT ,C.PCT_GROSS ,C.SAVINGS_COVRG_ATAX ,C.FLAT_DED_AMT_ATAX ,C.PCT_GROSS_ATAX ,D.PAY_PDS_PER_YEAR FROM PS_PERSON A , PS_BN_PERSON BN , PS_JOB B , PS_SAVINGS_PLAN C , PS_PAY_CALENDAR D , PS_NDT_RUNCTL E , PS_PRIMARY_JOBS PJ , PS_PER_ORG_ASGN EMP , PSASOFDATE AOD WHERE EMP.PER_ORG = 'EMP' AND EMP.EMPLID = A.EMPLID AND B.EMPLID = A.EMPLID AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_JOB B1 WHERE B1.EMPLID=B.EMPLID AND B1.EMPL_RCD=B.EMPL_RCD AND B1.EFFDT<=AOD.ASOFDATE) AND B.EFFSEQ = ( SELECT MAX(B2.EFFSEQ) FROM PS_JOB B2 WHERE B2.EMPLID=B.EMPLID AND B2.EMPL_RCD=B.EMPL_RCD AND B2.EFFDT=B.EFFDT) AND EXISTS ( SELECT 'X' FROM PS_BENEF_PLAN_TBL F WHERE F.PLAN_TYPE=C.PLAN_TYPE AND F.BENEFIT_PLAN= C.BENEFIT_PLAN AND F.EFFDT = ( SELECT MAX(F1.EFFDT) FROM PS_BENEF_PLAN_TBL F1 WHERE F1.PLAN_TYPE=F.PLAN_TYPE AND F1.BENEFIT_PLAN=F.BENEFIT_PLAN AND F1.EFFDT<=AOD.ASOFDATE) AND F.INCLUDE_IN_NDT='Y') AND EXISTS ( SELECT 'X' FROM PS_NDT_CO_PAYGROUP G WHERE G.COMPANY=B.COMPANY AND G.PAYGROUP=B.PAYGROUP) AND BN.EMPLID = A.EMPLID AND BN.HIGHLY_COMP_EMPL_C <> 'N' AND B.HR_STATUS = 'A' AND B.EMPLID = A.EMPLID AND C.EMPLID = B.EMPLID AND B.EMPL_RCD = PJ.EMPL_RCD AND EMP.EMPLID = A.EMPLID AND PJ.EMPLID = EMP.EMPLID AND PJ.PRIMARY_JOB_APP = 'BN' AND PJ.PRIMARY_JOB_IND = 'Y' AND PJ.EMPL_RCD = EMP.EMPL_RCD AND PJ.EFFDT = ( SELECT MAX(PJ1.EFFDT) FROM PS_PRIMARY_JOBS PJ1 WHERE PJ1.EMPLID=PJ.EMPLID AND PJ1.EMPL_RCD=PJ.EMPL_RCD AND PJ1.PRIMARY_JOB_APP='BN' AND PJ1.EFFDT<=AOD.ASOFDATE) AND C.EMPL_RCD= EMP.BENEFIT_RCD_NBR AND C.COVERAGE_ELECT = 'E' AND (C.COVERAGE_END_DT IS NULL OR C.COVERAGE_END_DT > AOD.ASOFDATE) AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_SAVINGS_PLAN C1 WHERE C1.EMPLID=C.EMPLID AND C1.EMPL_RCD=C.EMPL_RCD AND C1.PLAN_TYPE=C.PLAN_TYPE AND C1.BENEFIT_PLAN= C.BENEFIT_PLAN AND C1.BENEFIT_NBR= C.BENEFIT_NBR AND C1.EFFDT<=AOD.ASOFDATE) AND D.COMPANY = B.COMPANY AND D.PAYGROUP = B.PAYGROUP AND D.CHECK_DT = ( SELECT MIN(D1.CHECK_DT) FROM PS_PAY_CALENDAR D1 WHERE D1.COMPANY=D.COMPANY AND D1.PAYGROUP=D.PAYGROUP AND D1.CHECK_DT >= E.PLAN_YEAR_BEGIN_DT) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | ANNL_BENEF_BASE_RT | Number(19,3) | DECIMAL(18,3) NOT NULL | Specifies a compensation amount used to calculate benefits and benefit deductions for an employee. The base compensation amount may include commissions and bonuses in addition to regular compensation. If no Annual Benefits Base Rate is entered, then benefits calculations are based on an employee's regular compensation. |
| 3 | ANNUAL_RT | Number(19,3) | DECIMAL(18,3) NOT NULL | Annual Rate |
| 4 | SAVINGS_COVRG | Character(1) | VARCHAR2(1) NOT NULL |
Savings Coverage
1=Flat Amount 2=Percent of Gross |
| 5 | FLAT_DED_AMT | Number(9,2) | DECIMAL(8,2) NOT NULL | Flat Deduction Amount |
| 6 | PCT_GROSS | Number(7,3) | DECIMAL(6,3) NOT NULL | Percent of Gross |
| 7 | SAVINGS_COVRG_ATAX | Character(1) | VARCHAR2(1) NOT NULL |
Savings Coverage After-Tax
1=Flat Amount 2=Percent of Gross |
| 8 | FLAT_DED_AMT_ATAX | Number(9,2) | DECIMAL(8,2) NOT NULL | Flat Deduction Amt After-Tax |
| 9 | PCT_GROSS_ATAX | Number(7,3) | DECIMAL(6,3) NOT NULL | Percent of Gross After-Tax |
| 10 | PAY_PDS_PER_YEAR | Number(3,0) | SMALLINT NOT NULL | Pay Periods Per Year |