The purpose of this function is the same as the UC function ELPL_OPERATOR. The difference is that this function will cross employments for the employee and ELPL_OPERATOR 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.
After the function is loaded into the database an entry will be required in the IMLN function for the lexicon X_UC_FUNCTIONS. Once this is completed it is then accessible in the IMUC for the CF operator.

Aditional setup is required in the usercalc to define and populate three global variables before the CF is called.
G_EL_BEGIN_DATE - Date
G_EL_END_DATE -Date
G_ELEMENT -Char


This function will return a number value of the element specified in the global variable G_ELEMENT.

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;

/