Wiki: P 2 K_CF

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;