1 package body psp_matrix_driver3_pkg as
2 --$Header: PSPLSP3B.pls 115.6 2002/09/25 02:17:44 vdharmap ship $
3
4 start_period BINARY_INTEGER:=1;
5 TYPE mat_tab IS TABLE OF DATE
6 INDEX BY BINARY_INTEGER;
7 dat mat_tab;
8
9 p_payroll_id Number;
10 PROCEDURE add_periods(st_date date, en_date date);
11 FUNCTION last_period_day(st_date DATE) return DATE;
12 FUNCTION get_payroll_id RETURN NUMBER;
13
14 procedure set_start_period(n NUMBER) is
15 BEGIN
16 start_period:=n;
17 END set_start_period;
18
19 FUNCTION get_max_periods RETURN NUMBER is
20 BEGIN
21 RETURN dat.COUNT;
22 END;
23
24 FUNCTION get_payroll_id RETURN NUMBER is
25 BEGIN
26 Return p_payroll_id;
27 END get_payroll_id;
28
29 PROCEDURE set_payroll_id(n NUMBER) is
30 BEGIN
31 p_payroll_id := n;
32 END set_payroll_id;
33
34 FUNCTION get_dynamic_prompt(n NUMBER) RETURN VARCHAR2 IS
35 prompt varchar2(30);
36 v_period_num varchar2(10);
37 v_start_date varchar2(4);
38 st_Date DATE;
39 en_Date DATE;
40 v_Payroll_ID Number;
41 v_new_line_char varchar2(30) :='
42 ';
43 BEGIN
44 if (start_period+n) < 1 then --- replaced "> get_max_periods)" -- 2365076
45 return null;
46 else
47 v_Payroll_ID := get_payroll_id;
48 st_Date := dat(start_period+n);
49 en_Date := Last_Period_Day(st_Date);
50
51 Begin
52 Select to_char(Period_Num) , /* to_char(Start_Date, 'YYYY') Bug fix 1901631 */
53 substr(period_name, instr(period_name, ' ', 1, 1) + 1, 4)
54 into v_period_num, v_start_date
55 from PER_TIME_PERIODS
56 where Start_Date = st_Date
57 and Payroll_ID = v_Payroll_ID;
58 Exception
59 when OTHERS Then
60 return null;
61 End;
62
63 -- prompt:=to_char(dat(start_period+n),'DD-MON-YYYY');
64 -- prompt := v_period_num||chr(13)||chr(10)||v_start_date;
65 prompt := v_period_num || v_new_line_char || v_start_date;
66 RETURN prompt;
67 end if;
68 END;
69
70 FUNCTION get_start_period(n NUMBER) RETURN DATE is
71 i BINARY_INTEGER;
72 BEGIN
73 i:=start_period+n;
74 /*if (i > get_max_periods) then
75 RETURN null;
76 else*/
77 RETURN dat(i);
78 --end if;
79 END;
80
81 procedure add_periods(st_date date, en_date date) is
82 /**************************************************
83 This procedure calculates the various periods in the
84 user desired period.
85 **************************************************/
86 cursor C1 is
87 Select a.time_period_id, a.start_date
88 from PER_TIME_PERIODS a
89 where a.payroll_id = p_payroll_id
90 and (a.start_date >= st_date and a.end_date <= en_date)
91 order by a.start_date;
92
93 C1_Row C1%RowType;
94 i BINARY_INTEGER := 0;
95 n_First_Date DATE;
96 n_Last_date DATE;
97 n_Start_Date_Match NUMBER := 0;
98 b_Calculated_First_Period Boolean := FALSE;
99 n_End_Date_Match NUMBER := 0;
100
101 BEGIN
102
103 Select count(*)
104 into n_Start_Date_Match
105 from PER_TIME_PERIODS a
106 where a.START_DATE = st_date
107 and a.payroll_id = p_payroll_id;
108
109 Select count(*)
110 into n_End_Date_Match
111 from PER_TIME_PERIODS a
112 where a.END_DATE = en_date
113 and a.payroll_id = p_payroll_id;
114
115 Open C1;
116 LOOP
117 i := i+1;
118 FETCH C1 INTO C1_Row;
119 EXIT WHEN C1%NOTFOUND;
120 If NOT(b_Calculated_First_Period) AND n_Start_Date_Match = 0 Then
121 Begin
122 Select a.START_DATE
123 into n_First_Date
124 from PER_TIME_PERIODS a
125 where a.PAYROLL_ID = p_payroll_id
126 and a.TIME_PERIOD_ID = C1_Row.Time_Period_ID - 1;
127 dat(i) := n_First_Date;
128 b_Calculated_First_Period := TRUE;
129 i := i+1;
130 Exception
131 when OTHERS Then
132 b_Calculated_First_Period := TRUE;
133 null;
134 End;
135 End If;
136 dat(i) := C1_Row.Start_Date;
137 END LOOP;
138
139 If i > 1 and n_End_Date_Match = 0 Then
140 -- If there is atleast one record and the End Date does not match exactly, then manually add
141 -- the last period's Start Date
142 Begin
143 Select a.START_DATE
144 into n_Last_Date
145 from PER_TIME_PERIODS a
146 where a.PAYROLL_ID = p_payroll_id
147 and a.TIME_PERIOD_ID = C1_Row.Time_Period_ID + 1;
148 dat(i) := n_Last_Date;
149 Exception
150 when OTHERS Then
151 null;
152 End;
153 End If;
154
155 if i <=1 Then
156 -- There were no records obtained from the cursor.
157 -- Obtain the first period where the user's start date falls between a pay period's st and en date
158 -- Obtain the first period where the user's end date falls between a pay period's st and en date AND
159 -- where the pay period does not equal to the User-Start-Date-Pay-Period
160 Begin
161 Select a.START_DATE
162 into n_First_Date
163 from PER_TIME_PERIODS a
164 where a.PAYROLL_ID = p_payroll_id
165 and st_date between a.START_DATE and a.END_DATE;
166 dat(i) := n_First_Date;
167 i := i+1;
168 Exception
169 when OTHERS Then
170 null;
171 End;
172
173 Begin
174 Select a.START_DATE
175 into n_Last_Date
176 from PER_TIME_PERIODS a
177 where a.PAYROLL_ID = p_payroll_id
178 and en_date between a.START_DATE and a.END_DATE
179 and a.START_DATE <> n_First_Date;
180 dat(i) := n_Last_Date;
181 Exception
182 when OTHERS Then
183 null;
184 End;
185 End If;
186
187 return;
188 Exception
189 When OTHERS Then
190 return;
191 END add_periods;
192
193 function last_period_day(st_date DATE) return DATE is
194 v_Payroll_ID Number;
195 ret_Val DATE;
196 BEGIN
197 v_payroll_id := get_payroll_id;
198
199 Select END_DATE
200 into ret_Val
201 from PER_TIME_PERIODS
202 where PAYROLL_ID = v_Payroll_ID
203 and START_DATE = st_date;
204
205 return ret_Val;
206
207 EXCEPTION
208 when OTHERS Then
209 return NULL;
210 END last_period_day;
211
212 /* the function returns the total scheduled percentage of each distinct period in the temp table*/
213
214 FUNCTION get_dynamic_totals(n NUMBER) RETURN NUMBER IS
215 total NUMBER;
216 st_date DATE;
217 BEGIN
218 st_date:= dat(start_period+n);
219 if (start_period+n < 1) then --- replaced "> get_max_periods" for 2365076
220 return null;
221 else
222 select sum(PERIOD_SCHEDULE_PERCENT)
223 into total
224 from psp_matrix_driver3
225 where period = st_date;
226 return total;
227 end if;
228 END;
229
230 procedure purge_table is
231 begin
232 dat.DELETE;
233 end purge_table;
234
235
236 procedure load_table(sch_id number,begin_date date,end_date date) is
237 CURSOR sched_lines(s_id NUMBER) IS
238 SELECT schedule_line_id l_id,
239 schedule_begin_date sbd,
240 schedule_end_date sed,
241 schedule_percent sp
242 FROM psp_schedule_lines
243 WHERE schedule_hierarchy_id = s_id;
244
245 i BINARY_INTEGER :=0;
246
247 sch_rec sched_lines%ROWTYPE;
248 per number;
249 st_date date;
250 en_date date;
251 num number:=0;
252
253 BEGIN
254 st_date:= begin_date; --trunc(begin_date,'MONTH');
255 en_date:= end_date; --trunc(end_date,'MONTH');
256
257 Add_Periods(st_Date, en_date);
258
259 --dbms_output.put_line('reached here');
260 OPEN sched_lines(sch_id);
261 LOOP
262 FETCH sched_lines INTO sch_rec;
263 EXIT WHEN sched_lines%NOTFOUND;
264 num:=dat.COUNT;
265 FOR i in 1 .. num LOOP
266 if ((sch_rec.sbd between dat(i) and last_period_day(dat(i))AND(sch_rec.sed >= last_period_day(dat(i)))))then
267 per:= sch_rec.sp*psp_general.business_days(sch_rec.sbd,last_period_day(dat(i)))/psp_general.business_days(dat(i),last_period_day(dat(i)));
268 elsif ((dat(i) between sch_rec.sbd and sch_rec.sed)AND(last_period_day(dat(i)) >= sch_rec.sed))then
269 per:= sch_rec.sp*psp_general.business_days(dat(i),sch_rec.sed)/psp_general.business_days(dat(i),last_period_day(dat(i)));
270 elsif((sch_rec.sbd between dat(i) and last_period_day(dat(i)))and(sch_rec.sed <= last_period_day(dat(i))))then
271 per:= sch_rec.sp*psp_general.business_days(sch_rec.sbd,sch_rec.sed)/psp_general.business_days(dat(i),last_period_day(dat(i)));
272 elsif ((dat(i) between sch_rec.sbd and sch_rec.sed)AND(last_period_day(dat(i)) <= sch_rec.sed))then
273 per:= sch_rec.sp;
274 else
275 per:=0;
276 --dbms_output.put_line('reached here'||to_char(per));
277 end if;
278 per:=round(per,2);
279 --dbms_output.put_line(to_char(per));
280 insert into psp_matrix_driver3(SCHEDULE_LINE_ID,
281 PERIOD,
282 PERIOD_SCHEDULE_PERCENT) values
283 (sch_rec.l_id,
284 dat(i),
285 per);
286 END LOOP;
287 END LOOP;
288 -- commit;
289
290 EXCEPTION
291 When OTHERS Then
292 return;
293 END load_table;
294
295 end psp_matrix_driver3_pkg;
[Home] [Help]