FUNCTION period_value
( account_period IN CHAR,
budget_centre IN CHAR,
staff_ref IN CHAR,
pay_type IN CHAR
)
RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES ( period_value, WNDS, WNPS );
FUNCTION period_value_nom
( account_period IN CHAR,
budget_centre IN CHAR,
nominal IN CHAR,
staff_ref IN CHAR
)
RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES ( period_value_nom, WNDS, WNPS );
END mis_payroll_pack;
/
rem
rem
rem Define Function Body.
rem ---------------------
rem
rem
CREATE or REPLACE Package body mis_payroll_pack AS
/*
FUNCTION period_value
This Function will return a value which represents the amount
of Gross pay for a member of staff for a given accounting period.
The Function is driven by the Budget Centre the staff member belongs
to si_analysis_code3. There are occasions when there is no Budget
centre, value is Null. This occurs when the payments are against a
Balance Sheet code. Where this happens, a DECODE is used to identify
the Balance Sheet code and use that as a Psyudo Budget Centre!!!
*/
FUNCTION period_value
( account_period IN CHAR,
budget_centre IN CHAR,
staff_ref IN CHAR,
pay_type IN CHAR
)
RETURN NUMBER
IS
return_period_value NUMBER := 0 ;
BEGIN
SELECT nvl(sum(si.si_value), 0)
INTO return_period_value
FROM mis_salary_items si
WHERE si.si_account_period = account_period
AND decode(si.si_analysis_code3,
null , si.si_analysis_code1 , si.si_analysis_code3)
= budget_centre
AND si.si_pay_num = staff_ref
AND substr(si.si_analysis_code1,1,2) = pay_type
AND si.si_month_available = 'Y'
AND si.si_control_ind = 'D';
RETURN ( return_period_value );
EXCEPTION
WHEN NO_DATA_FOUND
THEN RETURN(0);
END;
/*
*/
FUNCTION period_value_nom
( account_period IN CHAR,
budget_centre IN CHAR,
nominal IN CHAR,
staff_ref IN CHAR
)
RETURN NUMBER
IS
return_period_value_nom NUMBER := 0 ;
BEGIN
SELECT nvl(sum(si.si_value), 0)
INTO return_period_value_nom
FROM mis_salary_items si
WHERE si.si_account_period = account_period
AND decode(si.si_analysis_code3,
null , si.si_analysis_code1 , si.si_analysis_code3)
= budget_centre
AND si.si_pay_num= staff_ref
AND si.si_analysis_code1 = nominal
AND si.si_month_available = 'Y'
AND si.si_control_ind = 'D';
RETURN ( return_period_value_nom );
EXCEPTION
WHEN NO_DATA_FOUND
THEN RETURN(0);
Reply With Quote
Bookmarks