Scribd
Upload
20 views

Build 2 Functions Inside Your Package

1. The document describes two functions - calculate_working_days and CALCULATE_CALENDAR_DAYS. 2. The calculate_working_days function takes in start and end dates as parameters and returns the number of working days between those dates by excluding weekends and holidays. 3. The CALCULATE_CALENDAR_DAYS function takes in start and end dates as parameters and returns the total number of calendar days between those dates by querying a calendar table.

Uploaded by

Dhinakaran
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
20 views

Build 2 Functions Inside Your Package

1. The document describes two functions - calculate_working_days and CALCULATE_CALENDAR_DAYS. 2. The calculate_working_days function takes in start and end dates as parameters and returns the number of working days between those dates by excluding weekends and holidays. 3. The CALCULATE_CALENDAR_DAYS function takes in start and end dates as parameters and returns the total number of calendar days between those dates by querying a calendar table.

Uploaded by

Dhinakaran
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

1.

Build 2 functions inside your package

FUNCTION calculate_working_days (p_sd DATE, p_ed DATE)


RETURN NUMBER
IS
v_days_between NUMBER;
v_count NUMBER;
v_count_off_days NUMBER := 0;
l_grade_cat VARCHAR2 (1000);
l_territory VARCHAR2 (1000);
v_working_days NUMBER := 0;

CURSOR crs_off (p_column_name VARCHAR2, p_territory_name VARCHAR2)


IS
SELECT DECODE (p_territory_name,
'AMERICA', DECODE (c.row_low_range_or_name,
'Saturday', '7',
'Sunday', '1',
'Monday', '2',
'Tuesday', '3',
'Wednesday', '4',
'Thursday', '5',
'Friday', '6',
c.row_low_range_or_name
),
DECODE (c.row_low_range_or_name,
'Saturday', '1',
'Sunday', '2',
'Monday', '3',
'Tuesday', '4',
'Wednesday', '5',
'Thursday', '6',
'Friday', '7',
c.row_low_range_or_name
)
) weekday_name
FROM pay_user_tables a,
pay_user_columns b,
pay_user_rows_f c,
pay_user_column_instances_f d
WHERE a.user_table_name = 'IHCC_WEEKDAYS'
AND b.user_column_name = 'WORKDAYS'
AND a.user_table_id = b.user_table_id
AND a.user_table_id = c.user_table_id
AND d.user_column_id = b.user_column_id
AND d.user_row_id = c.user_row_id
AND d.VALUE = '0';
BEGIN
SELECT VALUE
INTO l_territory
FROM nls_session_parameters
WHERE parameter = 'NLS_TERRITORY';
v_days_between := fffunc.days_between (TRUNC (p_ed), TRUNC (p_sd)) + 1;

FOR i IN 1 .. v_days_between
LOOP
FOR rcrd_off IN crs_off (l_grade_cat, l_territory)
LOOP
BEGIN
IF TO_NUMBER
(LOWER (TRIM (BOTH ' ' FROM TO_CHAR ( TRUNC
(p_sd)
+ (i - 1),
'D'
)
)
)
) = TO_NUMBER (LOWER (rcrd_off.weekday_name))
THEN
v_count_off_days := v_count_off_days + 1;
END IF;
END;
END LOOP;
END LOOP;
v_working_days := v_days_between - v_count_off_days;
RETURN NVL (v_working_days, 0);
END calculate_working_days;

FUNCTION CALCULATE_CALENDAR_DAYS(p_sd DATE, p_ed DATE) RETURN NUMBER AS


V_CALENDAR NUMBER := 0;
BEGIN
select max(a) INTO V_CALENDAR FROM (SELECT NVL(cal.end_date -
cal.START_DATE +1,0) A
from per_calendar_entries_V cal
where TRUNC(cal.START_DATE) between TRUNC(p_sd) and (p_ed)
UNION ALL SELECT 0 FROM DUAL) ;
RETURN V_CALENDAR;
EXCEPTION
WHEN OTHERS
THEN
V_CALENDAR := 0;
RETURN V_CALENDAR;
END CALCULATE_CALENDAR_DAYS;

You might also like