Absence Management - Debugging

Absence Management - Debugging

Element Resolution Chain is a nice way to view how the process is working.
It shows all the elements that are executed which are configured in a Section under Process List under Run Type.

We can play with the sqls to get results we need.

select * from ps_GP_AUDIT_SEG_VW a1 where cal_run_id like '19USA%'; -- list of employees processed
select * from ps_GP_AUDIT_PRC_VW a2 where cal_run_id like '19USA%' ; -- list of elements processed (parent level)

select * from ps_GP_AUDIT_DTL_VW a3 where cal_run_id like '19USA%' ; -- list of elements processed (parent & child level)
select * from ps_GP_AUDIT_tbl where emplid  = 'xxx'; -- list of elements processed (all levels)

Using the above, we can add more conditions as we want.

Adding PS_GP_PIN gives us the name, description, etc, of the element. 

select a.*,  PIN_CODE, PIN_NM, DESCR,PIN_PARENT_NUM from ps_GP_AUDIT_PRC_VW a, PS_GP_PIN b where a.pin_num = b.pin_num and  cal_run_id  like '19USAS113%';


select  a.*,  PIN_CODE, PIN_NM, DESCR,PIN_PARENT_NUM from ps_GP_AUDIT_DTL_VW  a, PS_GP_PIN b where a.pin_num = b.pin_num and cal_run_id like '19USA%' 
order by emplid;

We can add employees too.

select HIRE_DT, TERMINATION_DT,REG_TEMP, FULL_PART_TIME,reg_region,state,a1.*,  PIN_CODE, PIN_NM, DESCR,PIN_PARENT_NUM 
from ps_GP_AUDIT_PRC_VW a1, PS_GP_PIN b , ps_employees c1 
where a1.pin_num = b.pin_num and  cal_run_id  like '19USAS113%'
and a1.emplid = c1.emplid

order by a1.emplid,AUDIT_SORT_KEY;

select  a1.*,  HIRE_DT, TERMINATION_DT,REG_TEMP, FULL_PART_TIME,reg_region,state,PIN_CODE, PIN_NM, DESCR,PIN_PARENT_NUM,  PIN_CODE, PIN_NM, DESCR,PIN_PARENT_NUM  
from ps_GP_AUDIT_DTL_VW  a1, PS_GP_PIN b1, ps_employees c1 
where a1.pin_num = b1.pin_num and cal_run_id like '19USA%' 
and a1.emplid = c1.emplid

order by a1.emplid,AUDIT_SORT_KEY;

select distinct pin_nm,

 CAL_RUN_ID, EMPLID, 

  GP_PAYGROUP, CAL_ID, ORIG_CAL_RUN_ID, 

RSLT_SEG_NUM, AUDIT_SORT_KEY,PIN_CHAIN_LVL_NUM,PIN_CHAIN_RSLT_NUM,a.PIN_NUM,--PIN_NM,

 -- CALC_RSLT_VAL, CALC_ADJ_VAL, CALC_RAW_VAL,DATE_PIN_VAL, CHR_PIN_VAL, PIN_VAL_NUM,

PIN_CHAIN_SEQ_NUM, INSTANCE_NUM, SLICE_BGN_DT, SLICE_END_DT, SUB_PRC_ITERATION, 

   PIN_STATUS_IND, CALC_RSLT_VAL, CALC_ADJ_VAL, CALC_RAW_VAL, DATE_PIN_VAL, CHR_PIN_VAL, PIN_VAL_NUM,

BAD_TRACE_IND, SUM_INSTANCE_IND

PIN_CODE,  DESCR,PIN_PARENT_NUM 

from ps_GP_AUDIT_tbl a, PS_GP_PIN b1 

where a.pin_num = b1.pin_num 

-- and  cal_run_id   like '19USA%30' --  and pin_nm like '%PTO%' 

and emplid  like '02257025%'

-- and  cal_run_id like 'TEST%'

-- and calc_rslt_val <> 0 and pin_type = 'AC'

-- and pin_nm in ('GP PRD BGN DT EXTR','ANF AR TERM DATE')

-- and cal_id  like '%USA%'  and cal_id not like '%SWE%' and cal_id not like '%BEL%'

-- and PIN_NM like '%ABS%' and (calc_rslt_val > 0 or calc_adj_val > 0 or calc_raw_val > 0)

and PIN_NM like '%USAPTOAE_BAL' -- USATAKE RUN%'--VR NEWHIRE%' -- BR USAPTOXFR%'-- %ANF%MODEL%' -- '%VR PTOPAYF%' 

order by CAL_RUN_ID, EMPLID, 

 GP_PAYGROUP, CAL_ID, ORIG_CAL_RUN_ID,

-- calc_rslt_val desc,emplid, 

AUDIT_SORT_KEY,        PIN_CHAIN_LVL_NUM,  PIN_CHAIN_RSLT_NUM;