|
SELECT A.EMPLID
, A.CALC_NAME
, B.BENEFIT_PLAN
, C.PROC_EXEC_ORD_NUM
, C.PROC_NAME
, C.FUNCTN_NAME
, C.DESCR
, D.FLD_NAME
, D.FLD_TYPE
FROM PS_PA_CALCULATION A
, PS_PA_SUMMARY_PLAN B
, PS_PA_PROC_USE C
, PS_PA_FUNCTN_FLD_1 D
WHERE A.CALCULATION_TYPE = 'I'
AND A.CALC_REASON = 'WEB'
AND B.CALC_NAME = A.CALC_NAME
AND B.EMPLID = A.EMPLID
AND C.BENEFIT_PLAN = B.BENEFIT_PLAN
AND C.EFFDT =
(SELECT MAX(D.EFFDT)
FROM PS_PA_PROC_USE D
WHERE D.BENEFIT_PLAN = C.BENEFIT_PLAN
AND D.EFFDT <= A.ASOFDATE)
AND D.FUNCTN_NAME = C.FUNCTN_NAME
AND D.FLD_NAME IN ('PARTICIPATION_DATE',
'SERVICE_AMOUNT',
'VESTING_PERCENT',
'FAE_AMOUNT',
'ACCUM_AMT',
'EE_BEN_AMT',
'PIA_ADJ_AMT',
'COMP_AMT',
'ADJ_FACTOR',
'BEN_AMT')
AND (EXISTS (SELECT 'X'
FROM PS_PA_CLC_VAL_DEC F
WHERE F.BENEFIT_PLAN = B.BENEFIT_PLAN
AND F.CALC_NAME = A.CALC_NAME
AND F.FUNCTN_RESULT_NM = C.PROC_NAME
AND F.FLD_VAL_DEC_NOVRD <> 0
AND F.FLD_NAME = D.FLD_NAME)
OR EXISTS (SELECT 'X'
FROM PS_PA_CLC_VAL_DT F
WHERE F.BENEFIT_PLAN = B.BENEFIT_PLAN
AND F.CALC_NAME = A.CALC_NAME
AND F.FUNCTN_RESULT_NM = C.PROC_NAME
AND F.FLD_VAL_DT_NOVRD IS NOT NULL
AND F.FLD_NAME = D.FLD_NAME))
|
EMPLID