CREATE OR REPLACE
Function     P2K_CF_SENIORITYDATE RETURN  DATE IS
    PRAGMA AUTONOMOUS_TRANSACTION;
--
-- Purpose: To calculate and return a Date that could be used to replace the Seniority Date
--
/*
This custom function is tied to WF_EEM Work Flows.

1.	Upon insert of an EEM record check for any other EEM records for this EID/DEN combination
2.	Read the Seniority Date and Term Date from the EEM with the latest Term Date on it.
3.	Subtract the prior Term Date from the new Hire Date to get the number of days.
4.	Add the number of days to the prior Seniority Date to get the new Seniority Date.
5.	Return to the calling WF the adjusted Seniority Date to be updated to the record.

*/
-- MODIFICATION HISTORY
-- ----------------------------------------------------------
-- 20080721-JA-#40101 - Use values in Global 'SENDT_EEM_INCLUDE' to restrict list of Employment Types
-- 20080721-JA-#40100 - Ignore Employments with Null Term Dates
-- 20080422-JA-#Q2967 Initial Version

  CURSOR c_glbl_1 IS
    SELECT *
      FROM p2k_am_user_calc_globals mucg
     WHERE GLOBAL_NAME = 'SENDT_PRIOR';
  r_glbl_1 c_glbl_1%ROWTYPE;

   CURSOR c_glbl_2 IS
    SELECT *
      FROM p2k_am_user_calc_globals mucg
     WHERE GLOBAL_NAME = 'SENDT_DAYS';
  r_glbl_2 c_glbl_2%ROWTYPE;

  --JA20080721 added this cursor
   CURSOR c_glbl_3 IS
    SELECT *
      FROM p2k_am_user_calc_globals mucg
     WHERE GLOBAL_NAME = 'SENDT_EEM_INCLUD';
  r_glbl_3 c_glbl_3%ROWTYPE;

  CURSOR c_eem1(p_EEM_include VARCHAR2) IS  --JA20080721
    SELECT *
      FROM p2k_hr_employments eem
     WHERE eem.den_id = p2k_pmwrkflw.m_new_EEM.den_id
       AND eem.eid_id = p2k_pmwrkflw.m_new_EEM.eid_id
       AND eem.id <> p2k_pmwrkflw.m_new_EEM.id
       AND eem.termination_date IS NOT NULL  --JA20080721
       AND (p_EEM_include IS NULL OR INSTR(p_EEM_include, employment_type) <> 0)  --JA20080721
     ORDER BY eem.termination_date DESC;
  r_eem1 c_eem1%ROWTYPE;

  v_days_diff       NUMBER;
  v_new_seniority   DATE;
  v_EEM_include     VARCHAR2(50);  --JA20080721

BEGIN

  IF NOT p2k_pmwrkflw.INSERTING THEN
    RETURN p2k_pmwrkflw.m_new_EEM.seniority_date;
  END IF;

  --JA20080721 addeded this section.
-- If the global SENDT_EEM_INCLUDE exists use it to specify what Employment Types to include
  OPEN c_glbl_3;
  FETCH c_glbl_3 INTO r_glbl_3;
  IF c_glbl_3%FOUND THEN
    v_EEM_include := p2k_pmucutil.m_global(r_glbl_3.id).char_field;
  END IF;
  CLOSE c_glbl_3;

  p2k_smerl(P2K_PMWRKFLW.kmex_id,'P2K_CF_SENIORITYDATE',0,NULL,NULL,NULL,NULL,'Version: 20080721');
  OPEN c_eem1(v_EEM_include);  --JA20080721
  FETCH c_eem1 INTO r_eem1;
  IF c_eem1%NOTFOUND THEN
    p2k_smerl(P2K_PMWRKFLW.kmex_id,'P2K_CF_SENIORITYDATE',0,NULL,NULL,NULL,NULL,'No former EEM record found');
    CLOSE c_eem1;
    RETURN p2k_pmwrkflw.m_new_EEM.seniority_date;
  END IF;

  v_days_diff       := p2k_pmwrkflw.m_new_EEM.hire_date - r_eem1.termination_date;
  p2k_smerl(P2K_PMWRKFLW.kmex_id,'P2K_CF_SENIORITYDATE',0,NULL,NULL,NULL,NULL,
    'Former Term Date: ' || r_eem1.termination_date ||
    ' From new Hire: ' || p2k_pmwrkflw.m_new_EEM.hire_date ||
    ' gives: ' || v_days_diff || ' days.');
  v_new_seniority   := r_eem1.seniority_date + v_days_diff;
  p2k_smerl(P2K_PMWRKFLW.kmex_id,'P2K_CF_SENIORITYDATE',0,NULL,NULL,NULL,NULL,
    'Add days above to old Seniority: ' || r_eem1.seniority_date ||
    ' gives: ' || v_new_seniority || ' for new date.');

-- If the global SENDT_PRIOR exists populate it with the prior seniority date
  OPEN c_glbl_1;
  FETCH c_glbl_1 INTO r_glbl_1;
  IF c_glbl_1%FOUND THEN
    p2k_pmucutil.m_global(r_glbl_2.id).date_field := r_eem1.seniority_date;
  END IF;
  CLOSE c_glbl_1;

-- If the global SENDT_DAYS exists populate it with the days between value
  OPEN c_glbl_2;
  FETCH c_glbl_2 INTO r_glbl_2;
  IF c_glbl_2%FOUND THEN
    p2k_pmucutil.m_global(r_glbl_2.id).number_field := v_days_diff;
  END IF;
  CLOSE c_glbl_2;

  CLOSE c_eem1;
  RETURN v_new_seniority;
/*
EXCEPTION
  WHEN others THEN
    RETURN p2k_pmwrkflw.m_new_EEM.seniority_date; */
END; -- Function P2K_CF_SENIORITYDATE