The purpose of this function is the same as the UC function [ELPL].
The difference is that this function will cross employments for the employee and [ELPL] will only look at amounts inside of the current employment.

This function will need to be added to each database manually in order to be used. 


=============================
--------------------------------------------------------
--  DDL for Function P2K_CF_FMLAHRS
--------------------------------------------------------

  CREATE OR REPLACE FUNCTION "P2K"."P2K_CF_FMLAHRS" RETURN NUMBER AS 
 --20140716 - KHIGSS - Modify to pick up globals. Reworked to focus on Paylines 
  --                 - and not Headers
 --20140417 - IH     - modify to pick up globals and make use of an 
 --                  - element in place of hard coded PPC_IDs
 --20140411 - - initial creation
 
 --Purpose    - The purpose of this function is the same as the UC function ELPL
 --           - The difference is that this function will cross employments for 
 --           - the employee and elpl will only look at amounts inside of the 
 --           - current employment.
 
 --Setup      - The following set up is requried in the referencing UC
 --           - Global variables - setup and initialized
 --           -          G_EL_BEGIN_DATE - Date
 --           -          G_EL_END_DATE   -Date
 --           -          G_ELEMENT       -Char
 
 --Return     - This function will return a NUMBER of hours in the element that
 --           -have occured between the begin and end dates.
 --           -If the NUMBER returned is negative then an error condition has
 --           -occured.
 --           -     -999 problem wiht the begin date
 --           -     -998 problem with the end date
 --           -     -997 problem with the element
 --           -     -996 problem with the return select

 v_fmlahrs NUMBER;
 v_begin_id Number;
 v_end_id Number;
 v_pel_id Number;
 v_eid Number;

 BEGIN 
 p2k_smerl(P2K_PMWRKFLW.kmex_id,'P2K_CF_FMLAHRS',1,NULL,NULL,NULL,NULL,'Version: 20140716');

 BEGIN
	SELECT ID 
	  INTO v_begin_id
	  FROM p2k_am_user_calc_globals mucg
   WHERE GLOBAL_NAME = 'G_EL_BEGIN_DATE';
 
 EXCEPTION
	WHEN OTHERS THEN RETURN -999;
 END;

 BEGIN
	SELECT ID
	  INTO v_end_id
	  FROM p2k_am_user_calc_globals mucg
     WHERE GLOBAL_NAME = 'G_EL_END_DATE';
 
 EXCEPTION
	WHEN OTHERS THEN RETURN -998;
 END;

 BEGIN
	SELECT id
	  INTO v_pel_id
	  FROM p2k_am_user_calc_globals mucg
	 WHERE GLOBAL_NAME = 'G_ELEMENT';
 EXCEPTION
	WHEN OTHERS THEN RETURN -997;
 END;

 v_eid := p2k_pmucutil.eid.id;
 
BEGIN
 SELECT SUM(ENTERED_VALUE)
 INTO v_fmlahrs
FROM P2K_PR_PAY_HEADERS PPH,
     P2K_PR_PAY_LINES PPL,
     P2K_PR_PAY_LINE_DETAILS PPLD
WHERE PPH.ID = PPL.PPH_ID
AND PPL.ID = PPLD.PPL_ID
AND PPH.EEM_ID in (SELECT EEM.ID FROM P2K_HR_EMPLOYMENTS EEM WHERE EEM.EID_ID = v_eid)
AND PPL.START_DATE BETWEEN p2k_pmucutil.m_global(v_begin_id).date_field and p2k_pmucutil.m_global(v_end_id).date_field
AND PPLD.PPC_ID IN 
(  SELECT PELD.PPC_ID
     FROM P2K_PR_ELEMENTS PEL,
          P2K_PR_ELEMENT_DETAILS PELD
    WHERE PEL.ID = PELD.PEL_ID
      AND PEL.ELEMENT_CODE = p2k_pmucutil.m_global(v_pel_id).char_field);
 
 EXCEPTION
  WHEN OTHERS THEN RETURN -996;
END;

 RETURN v_fmlahrs;

END P2K_CF_FMLAHRS;

/