Absence Management Calender consept

Query to find calendar run id.

Note:-a calendar can be in 4 stages (unopened,identify,calculate,finalized)

flag A.RUN_FINALIZED_IND = 'N'     means not finalized.

SELECT A.CAL_RUN_ID  ,B.cal_id ,D.RUN_TYPE , D.CALC_TYPE

  FROM ps_gp_cal_run A   , ps_gp_cal_run_dtl B  , ps_gp_calendar C   ,ps_gp_run_type D

 WHERE A.CAL_RUN_ID=B.CAL_RUN_ID    AND  A.USE_AS_TMPLT_IND='N'

   AND A.RUN_FINALIZED_IND = 'N'    AND C.GP_PAYGROUP = B.GP_PAYGROUP

   AND C.CAL_ID = B.CAL_ID    AND C.RUN_TYPe=D.RUN_TYPE    AND D.CALC_TYPE = 'A'

With  this cal_run_id we can identify the period begin and end date.

sELECT %dateout(PRD_BGN_DT),%dateout(PRD_END_DT) FROM PS_GP_CAL_PRD WHERE CAL_PRD_ID=(SELECT CAL_PRD_ID FROM PS_GP_CALENDAR WHERE CAL_ID =(SELECT A.CAL_ID FROM PS_GP_CAL_RUN_DTL A WHERE  A.CAL_RUN_ID=:1))", OI_ABS_CONV_AET.CAL_RUN_ID.Value, &PRD_BGN_DT, &PRD_END_DT);