1 package body per_za_abs_duration as
2 /* $Header: perzaabd.pkb 120.8 2008/02/07 06:53:26 rbabla noship $ */
3 function za_daysoff (p_DateFrom IN DATE,
4 p_DateTo IN DATE)
5 return number
6 is
7 ------------------------- FUNCTION za_daysoff --------------------------
8 -- The following function returns the number of days off between parameters.
9 -- Function checks if the date is on a Sat/Sun and if not then checks if
10 -- it is a public holiday
11 ------------------------------------------------------------------------
12 v_DaysOff number := 0;
13 v_DateFrom date;
14 v_index number := 0;
15 v_count number := 0;
16 l_count number := 0;
17 l_hol_date date;
18 type date_table_type is table of date
19 index by binary_integer;
20 publichol_table date_table_type;
21 cursor c1 is
22 select puci.value
23 , puci.user_column_instance_id
24 , puci.effective_start_date
25 , puci.effective_end_date
26 , puc.user_column_name
27 from pay_user_tables put, pay_user_columns puc, pay_user_column_instances_f puci
28 where put.user_table_name = 'ZA_PUBLIC_HOLIDAY_CALENDAR'
29 and put.user_table_id = puc.user_table_id
30 and puc.user_column_id = puci.user_column_id
31 and puci.effective_start_date <= p_DateTo
32 and puci.effective_end_date >= p_DateFrom;
33
34 begin
35 v_DateFrom := p_DateFrom;
36 -- open c1;
37 /* Retrieve all public holidays between parameter dates and load into PL/SQL table for
38 subsequent search. Check if the day is a Sat/Sun then increment counter, if not
39 then check the pl/sql public holiday table and if match found then increment */
40 -- loop
41 -- v_index := v_index + 1;
42 -- fetch c1 into publichol_table(v_index);
43 -- exit when c1%notfound or c1%notfound is null;
44 -- end loop;
45 -- close c1;
46 for rec_hol_dt in c1
47 loop
48 begin
49 l_hol_date := fnd_date.CANONICAL_TO_DATE(rec_hol_dt.value);
50 if l_hol_date >= p_DateFrom and l_hol_date <= p_DateTo -- holiday falls within period
51 and l_hol_date >= rec_hol_dt.effective_start_date
52 and l_hol_date <= rec_hol_dt.effective_end_date then
53 v_index := v_index + 1;
54 publichol_table(v_index):= l_hol_date;
55 end if;
56 exception
57 when others then
58 l_count := l_count +1;
59 hr_utility.set_location('************************',9999);
60 hr_utility.trace(substr(SQLERRM,1,254));
61 hr_utility.trace('SQLCODE' || SQLCODE);
62 hr_utility.set_location('Count of wrong record ' || l_count,9999);
63 hr_utility.set_location('rec_value.value ' || rec_hol_dt.value,9999);
64 hr_utility.set_location('user_column_instance_id ' || rec_hol_dt.user_column_instance_id,9999);
65 hr_utility.set_location('Effective_End_date ' || rec_hol_dt.effective_start_date,9999);
66 hr_utility.set_location('Effective_End_date ' || rec_hol_dt.effective_end_date,9999);
67 hr_utility.set_location('user_column_name ' || rec_hol_dt.user_column_name,9999);
68 hr_utility.set_location('************************',9999);
69 end;
70
71 end loop;
72 v_count := publichol_table.count;
73 v_index := 1;
74 while v_DateFrom <= p_DateTo LOOP
75 if to_char(v_DateFrom, 'DY') IN ('SAT','SUN') THEN
76 v_DaysOff := v_DaysOff + 1;
77 else
78 for v_index IN 1..(v_count) loop
79 if v_DateFrom = publichol_table (v_index) then
80 v_DaysOff := v_DaysOff + 1;
81 end if;
82 end loop;
83 end if;
84 v_DateFrom := v_DateFrom + 1;
85 end loop;
86 return v_DaysOff;
87 end ZA_DaysOff;
88
89 function get_canonical_Dt_format
90 return varchar2 is
91 l_format varchar2(100);
92 begin
93 l_format := FND_DATE.canonical_DT_mask;
94
95 return l_format;
96 end get_canonical_Dt_format;
97
98 function za_canonical_Dt_format (p_date varchar2)
99 return varchar2 is
100 l_status varchar2(4);
101 l_date date;
102 l_year number(4);
103 l_month number(2);
104 l_date1 number(4);
105 l_datewo_time varchar2(25);
106 l_space number(2);
107 l_year_dlmt number(2);
108 l_month_dlmt number(2);
109 l_date_dlmt number(2);
110 begin
111 l_date := fnd_date.canonical_to_date(p_date);
112 l_space := instr(p_date,' ',1,1);
113 if l_space<>0 then
114 l_datewo_time:=substr(p_date,1,l_space-1);
115 else
116 l_datewo_time:=p_date;
117 l_space:=length(p_date)+1;
118 end if;
119
120 l_year_dlmt := instr(p_date,'/',1,1);
121 l_month_dlmt := instr(p_date,'/',1,2);
122
123 if l_year_dlmt = 0 or l_month_dlmt = 0 then
124 l_status := 'E';
125 return l_status;
126 end if;
127
128 l_year := substr(l_datewo_time,1,l_year_dlmt-1);
129 l_month := substr(l_datewo_time,l_year_dlmt+1,(l_month_dlmt - l_year_dlmt)-1);
130 l_date1 := substr(l_datewo_time,l_month_dlmt+1,(l_space-l_month_dlmt)-1);
131
132 if l_year > 0 and l_month <= 12 and l_date1 <= 31 and l_year_dlmt=5 then
133 l_status := 'S';
134 else
135 l_status := 'E';
136 end if;
137 return l_status;
138 exception
139 when others then
140 l_status := 'E';
141 return l_status;
142
143 end za_canonical_Dt_format;
144
145 end per_za_abs_duration;
[Home] [Help]