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.

=============================

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;

/