1 package body psp_matrix_driver_pkg as
2 /* $Header: PSPLSMTB.pls 120.5.12010000.2 2008/08/05 10:13:30 ubhat ship $ */
3
4 start_period BINARY_INTEGER:=1;
5 global_run_id NUMBER(9);
6
7 TYPE mat_tab IS TABLE OF DATE
8 INDEX BY BINARY_INTEGER;
9 TYPE type_tab IS TABLE OF CHAR
10 INDEX BY BINARY_INTEGER;
11 dat mat_tab;
12 dat1 mat_tab;
13 type_dat type_tab;
14
15 -- Introduced the following for bug fix 3697471
16 TYPE schedule_chunk_rec IS RECORD
17 (schedule_begin_date mat_tab,
18 schedule_end_date mat_tab);
19
20 schedule_chunk schedule_chunk_rec;
21 -- End of changes for bug fix 3697471
22
23 /* The following procedure initializes the global variable run_id which distinguishes records inserted into the temporary table by each session*/
24
25 procedure set_runid is
26 BEGIN
27 select psp_ls_runid_s.nextval into global_run_id
28 from dual;
29 END;
30
31 /* this procedure deletes all the records that have been inserted into the temp table in that session*/
32
33 procedure clear_table (event VARCHAR2)is
34 BEGIN
35 delete from psp_matrix_driver
36 where run_id = global_run_id;
37 if (event = 'POST-FORM') then
38 --For Bug 2720395 : Uncommenting the Commit
39 COMMIT;
40 --null;
41 --End of bug Changes
42 end if;
43 END;
44
45 /* defines the upper limit of time periods that are displayed in the dynamic view*/
46
47 procedure set_start_period(n NUMBER) is
48 BEGIN
49 start_period:=n;
50 END set_start_period;
51
52 /* returns the maximum # of distinct periods existing in the temp table. Its also equal to the no. of records in the pl/sql table*/
53
54 FUNCTION get_max_periods RETURN NUMBER is
55 BEGIN
56 /* RETURN dat.COUNT-1; Commented for bug 4511249*/
57 RETURN schedule_chunk.schedule_begin_date.COUNT-1;
58 END;
59
60 /* the function formats the start and end period of each distict period in the temp table and returns the appropriate string to the corresponding prompt in the form*/
61
62 FUNCTION get_dynamic_prompt(n NUMBER, s_id number) RETURN VARCHAR2 IS
63 new_line varchar2(30); -- Moved default value assignment to pl/sql block as part of bug fix 3697471
64 prompt varchar2(30);
65 prompt1 varchar2(30);
66 prompt2 varchar2(30);
67 v_count1 number;
68 v_count2 number;
69 v_count3 number;
70 v_count4 number;
71 BEGIN
72 new_line := '
73 '; -- Introduced for bug fix 3697471 to fix GSCC warning File.Sql.35
74
75 ---if (start_period+n > get_max_periods) -- commented this line
76 if (start_period+n <0) then -- and added this condn for 2365076
77 return null;
78 else
79 /*
80 --
81 --If it is the first period, then if dat(i+1) is a Begin Date in schedule lines, period prompt would be
82 --from dat(i) to (dat(i+1)-1), else dat(i) to dat(i+1).
83 --
84 if ((start_period+n)=1) then
85 select count(*) into v_count1 from psp_schedule_lines
86 where schedule_begin_date = dat(start_period+n+1)
87 and schedule_hierarchy_id = s_id;
88 if v_count1 = 0 then
89 prompt1 := to_char(dat(start_period+n));
90 prompt2 := to_char(dat(start_period+n+1));
91 else
92 prompt1 := to_char(dat(start_period+n));
93 prompt2 := to_char(dat(start_period+n+1)-1);
94 end if;
95
96 prompt1:=substr(prompt1,1,2)||substr(prompt1,4,3)||substr(prompt1,8,2);
97 prompt2:=substr(prompt2,1,2)||substr(prompt2,4,3)||substr(prompt2,8,2);
98
99 prompt:= prompt1||new_line||prompt2;
100
101 --
102 --If it is not the first period, then...
103 --if dat(i) is a Begin Date in schedule lines, period prompt would start from dat(i), else from dat(i)+1.
104 --if dat(i) is a Begin Date as well as End Date in schedule lines, period prompt would start from
105 --(dat(i)+1).
106 --if dat(i+1) is not a Begin Date, but is an End Date in schedule lines, period prompt would end at
107 --dat(i+1), else at (dat(i+1)-1).
108 --
109 --if dat(i) = dat(i+1) or dat(i+1) = dat(i)+1, period prompt would be from dat(i) to dat(i+1).
110 --
111
112 else
113 select count(*) into v_count1 from psp_schedule_lines
114 where schedule_begin_date = dat(start_period+n)
115 and schedule_hierarchy_id = s_id;
116
117 select count(*) into v_count2 from psp_schedule_lines
118 where schedule_begin_date = dat(start_period+n+1)
119 and schedule_hierarchy_id = s_id;
120
121 select count(*) into v_count3 from psp_schedule_lines
122 where schedule_end_date = dat(start_period+n)
123 and schedule_hierarchy_id = s_id;
124
125 select count(*) into v_count4 from psp_schedule_lines
126 where schedule_end_date = dat(start_period+n+1)
127 and schedule_hierarchy_id = s_id;
128
129 if v_count1 = 0 then
130 prompt1 := to_char(dat(start_period+n)+1);
131 else
132 prompt1 := to_char(dat(start_period+n));
133 end if;
134
135 if v_count1 <> 0 and v_count3 <> 0 then
136 prompt1 := to_char(dat(start_period+n)+1);
137 end if;
138
139 if v_count2 = 0 and v_count4 <> 0 then
140 prompt2 := to_char(dat(start_period+n+1));
141 else
142 prompt2 := to_char(dat(start_period+n+1)-1);
143 end if;
144
145 if dat(start_period+n) = dat(start_period+n+1)
146 or dat(start_period+n+1) = dat(start_period+n)+1 then
147 prompt1 := to_char(dat(start_period+n));
148 prompt2 := to_char(dat(start_period+n+1));
149 end if;
150
151 prompt1:=substr(prompt1,1,2)||substr(prompt1,4,3)||substr(prompt1,8,2);
152 prompt2:=substr(prompt2,1,2)||substr(prompt2,4,3)||substr(prompt2,8,2);
153
154 prompt:= prompt1||new_line||prompt2;
155
156 end if;
157 Commented for Bug 4511249 */
158
159 /* Introduced the following for Bug 4511249 */
160
161 prompt1 := to_char(schedule_chunk.schedule_begin_date(start_period+n+1));
162 prompt2 := to_char(schedule_chunk.schedule_end_date(start_period+n+1));
163
164 prompt1:=substr(prompt1,1,2)||substr(prompt1,4,3)||substr(prompt1,8,2);
165 prompt2:=substr(prompt2,1,2)||substr(prompt2,4,3)||substr(prompt2,8,2);
166 prompt:= prompt1||new_line||prompt2;
167
168
169 /* End of code chages for Bug 4511249 */
170
171 RETURN prompt;
172 end if;
173 END;
174 /* the function returns the total scheduled percentage of each distinct period in the temp table*/
175
176 FUNCTION get_dynamic_totals(n NUMBER) RETURN NUMBER IS
177 total NUMBER;
178 st_date DATE;
179 BEGIN
180 -- st_date:= dat(start_period+n); Commented for bug 4511249
181 st_date:= schedule_chunk.schedule_begin_date(start_period+n+1);
182 if (start_period+n > get_max_periods) then
183 return null;
184 else
185 select sum(PERIOD_SCHEDULE_PERCENT)
186 into total
187 from psp_matrix_driver
188 where run_id = global_run_id
189 and period_start_date = st_date
190 and period_end_date = schedule_chunk.schedule_end_date(start_period+n+1);
191 return total;
192 end if;
193 END;
194
195
196 FUNCTION get_run_id RETURN NUMBER is
197 begin
198 return global_run_id;
199 end;
200
201 FUNCTION get_start_period(n NUMBER) RETURN DATE is
202 i BINARY_INTEGER;
203 BEGIN
204 i:=start_period+n;
205 /*if (i > get_max_periods) then
206 RETURN null;
207 else*/
208 -- RETURN dat(i); Commented for bug fix 4511249
209 RETURN schedule_chunk.schedule_begin_date(start_period + n + 1);
210 --end if;
211 END;
212
213 FUNCTION get_end_period(n NUMBER) RETURN DATE is
214 i BINARY_INTEGER;
215 BEGIN
216 i:=start_period+n+1;
217 /*if (i > get_max_periods) then
218 RETURN null;
219 else*/
220 -- RETURN dat(i); Commented for bug fix 4511249
221 RETURN schedule_chunk.schedule_end_date(start_period + n + 1);
222 --end if;
223 END;
224
225 -- deletes the records in the pl/sql table
226
227 procedure purge_table is
228 begin
229 dat.DELETE;
230 dat1.DELETE;
231 type_dat.DELETE;
232 schedule_chunk.schedule_end_date.delete; -- Commented for bug fix 4511249
233 schedule_chunk.schedule_begin_date.delete; -- Commented for bug fix 4511249
234 end purge_table;
235
236 /* this is the procedure that is responsible for the bulk of the work. It sorts the pool of begin and end dates specified on the various schedule lines.*/
237 /* After the sort it inserts the dates into pl/sql table thereby forming distinct periods of consistent charging instructions. */
238 /*Once the pl/sql table is loaded, the start and end dates of the distinct periods are inserted into the temp table. */
239 /*As the cursor parses through each record a correponding schedule percentage is inserted according to the overlap of these distinct periods with the periods specified on each schedule line*/
240
241 procedure load_table(sch_id number) is
242 CURSOR sched_lines(s_id NUMBER) IS
243 SELECT schedule_line_id l_id,
244 schedule_begin_date sbd,
245 schedule_end_date sed,
246 schedule_percent sp
247 FROM psp_schedule_lines
248 WHERE schedule_hierarchy_id = s_id;
249
250 --Get the dates (schedule begin dates and end dates) in ascending order of dates. If a date is present in
251 --begin as well as the end date, bring the End Date first.
252 CURSOR dates(s_id NUMBER) IS
253 SELECT schedule_begin_date dat , 'B'
254 FROM psp_schedule_lines
255 WHERE schedule_hierarchy_id = s_id
256 UNION
257 SELECT schedule_end_date dat , 'E'
258 FROM psp_schedule_lines
259 WHERE schedule_hierarchy_id = s_id
260 ORDER BY 1, 2 ;
261
262 i BINARY_INTEGER :=0;
263 j BINARY_INTEGER :=0;
264 k BINARY_INTEGER :=1;
265
266 sch_rec sched_lines%ROWTYPE;
267 per number;
268 temp date;
269 num number;
270 dummy char(1);
271 v_count1 number;
272 v_count2 number;
273 BEGIN
274 /***** Commented for Bug Fix 4511249
275 --From the cursor, get the dates in pl/sql table dat1.
276 OPEN dates(sch_id);
277 LOOP
278 i:=i+1;
279 FETCH dates INTO temp, dummy;
280 EXIT WHEN dates%NOTFOUND;
281 --dbms_output.put_line('date is:'||to_char(temp));
282 dat1(i):=temp;
283 type_dat(i) := dummy;
284 END LOOP;
285
286 -- Added the following for Bug no 2836176 by tbalacha
287 -- This code was added to avoid when No_DATA_FOUND exception for hierarchy that dont have schedule lines
288 -- defined
289
290 IF (dates%ROWCOUNT = 0) THEN
291 RETURN;
292 END IF;
293
294 -- End of code for Bug no 2836176
295 CLOSE dates;
296
297 --
298 --Copy dates from table 'dat1' to table 'dat'. If there is a Begin Date which is exactly 1 day greater
299 --than the End Date, DO NOT include this Begin Date in table 'dat'.
300 i := 1;
301 dat(i) := dat1(i);
302 j := 2;
303 FOR i IN 2..dat1.COUNT LOOP
304
305 if dat1(i) = dat1(i-1) + 1 then
306 if type_dat(i) = 'B' and type_dat(i-1) = 'E' then
307 null;
308 else
309 dat(j) := dat1(i);
310 j := j + 1;
311 end if;
312 else
313 dat(j) := dat1(i);
314 j := j + 1;
315 end if;
316
317 END LOOP;
318
319 --
320 --Insert records in temporary table PSP_MATRIX_DRIVER. There may be some dates in 'dat1' which were not
321 --included in 'dat' because Begin Date was exactly 1 day greater than the End Date. In such a case,
322 --instead of comparing dates of 'dat' with those of psp_schedule_lines, compare (Begin Date+1) and End
323 --Date of 'dat' with those of psp_schedule_lines.
324 --
325 OPEN sched_lines(sch_id);
326 LOOP
327 FETCH sched_lines INTO sch_rec;
328 EXIT WHEN sched_lines%NOTFOUND;
329 num:=dat.COUNT-1;
330 FOR i in 1 .. num LOOP
331
332 if dat(i+1) = dat1(i+1) then
333
334 if ((dat(i) between sch_rec.sbd and sch_rec.sed) AND (dat(i+1) between sch_rec.sbd and sch_rec.sed)) THEN
335 per:= sch_rec.sp;
336 else
337 per:=0;
338 end if;
339
340 else
341
342 if ((((dat(i)+1) between sch_rec.sbd and sch_rec.sed) AND (dat(i+1) between sch_rec.sbd and sch_rec.sed))
343 -- Added the following condition for bug 2267098
344 OR (dat(i) = dat(i + 1) AND(dat(i) between sch_rec.sbd AND sch_rec.sed))) THEN
345 per:= sch_rec.sp;
346 else
347 per:=0;
348 end if;
349
350 end if;
351
352 insert into psp_matrix_driver(RUN_ID,
353 SCHEDULE_LINE_ID,
354 PERIOD_START_DATE,
355 PERIOD_END_DATE,
356 PERIOD_SCHEDULE_PERCENT) values
357 (global_run_id,
358 sch_rec.l_id,
359 dat(i),
360 dat(i+1),
361 per);
362 END LOOP;
363 END LOOP;
364
365 End of comment for bug fix 4511249 *****/
366
367
368 /**** Introduced the following for bug fix 4511249 ****/
369
370 OPEN dates(sch_id);
371 FETCH dates BULK COLLECT INTO dat, type_dat;
372 CLOSE dates;
373
374 FOR rowno IN 1..(dat.COUNT - 1) LOOP
375 IF (type_dat(rowno) = 'B' AND type_dat(rowno+1) = 'B') THEN
376 schedule_chunk.schedule_begin_date(k) := dat(rowno);
377 schedule_chunk.schedule_end_date(k) := dat(rowno+1) - 1;
378 k := k+1;
379 ELSIF (type_dat(rowno) = 'B' AND type_dat(rowno+1) = 'E') THEN
380 schedule_chunk.schedule_begin_date(k) := dat(rowno);
381 schedule_chunk.schedule_end_date(k) := dat(rowno+1);
382 k := k+1;
383 ELSIF (type_dat(rowno) = 'E' AND type_dat(rowno+1) = 'E') THEN
384 schedule_chunk.schedule_begin_date(k) := dat(rowno) + 1;
385 schedule_chunk.schedule_end_date(k) := dat(rowno+1);
386 k := k+1;
387 ELSIF (dat(rowno+1) - dat(rowno) > 1) THEN -- Bug 6623195
388 schedule_chunk.schedule_begin_date(k) := dat(rowno) + 1;
389 schedule_chunk.schedule_end_date(k) := dat(rowno+1) - 1;
390 k := k+1;
391 END IF;
392 END LOOP;
393
394 k:=1;
395 FOR rowno IN 1..schedule_chunk.schedule_begin_date.COUNT
396 LOOP
397 dat(k) := schedule_chunk.schedule_begin_date(rowno);
398 IF schedule_chunk.schedule_begin_date(rowno) = schedule_chunk.schedule_end_date(rowno) THEN
399 dat(k) := schedule_chunk.schedule_end_date(rowno);
400 k := k + 1;
401 ELSE
402 dat(k+1) := schedule_chunk.schedule_end_date(rowno);
403 k := k + 2;
404 END IF;
405 END LOOP;
409 (RUN_ID, SCHEDULE_LINE_ID,
406
407 FORALL rowno IN 1..schedule_chunk.schedule_begin_date.COUNT
408 INSERT INTO psp_matrix_driver
410 PERIOD_START_DATE,
411 PERIOD_END_DATE,
412 PERIOD_SCHEDULE_PERCENT)
413 SELECT global_run_id, schedule_line_id,
414 schedule_chunk.schedule_begin_date(rowno),
415 schedule_chunk.schedule_end_date(rowno),
416 schedule_percent
417 FROM psp_schedule_lines psl
418 WHERE schedule_hierarchy_id = sch_id
419 AND psl.schedule_begin_date <= schedule_chunk.schedule_end_date(rowno)
420 AND psl.schedule_end_date >= schedule_chunk.schedule_begin_date(rowno);
421
422 FORALL rowno IN 1..schedule_chunk.schedule_begin_date.COUNT
423 INSERT INTO psp_matrix_driver
424 (RUN_ID, SCHEDULE_LINE_ID,
425 PERIOD_START_DATE,
426 PERIOD_END_DATE,
427 PERIOD_SCHEDULE_PERCENT)
428 SELECT global_run_id, schedule_line_id,
429 schedule_chunk.schedule_begin_date(rowno),
430 schedule_chunk.schedule_end_date(rowno),
431 0
432 FROM psp_schedule_lines psl
433 WHERE schedule_hierarchy_id = sch_id
434 AND (psl.schedule_begin_date > schedule_chunk.schedule_end_date(rowno)
435 OR psl.schedule_end_date < schedule_chunk.schedule_begin_date(rowno));
436
437 /**** End of changes for bug fix 4511249 ****/
438
439 END load_table;
440
441 /* this function is used to check the exceedence of the total of the schedule percentages of each distinct period in the temp table over the 100 percent limit*/
442 /* Added the following code for check exceedence for bug no 2836176 ,
443 Introduced the parameter p_payroll_id */
444 -- Note : The Below code to check for max timeperiod
445 -- shouldn't be changed as it may effect the existing customers
446
447 -- FUNCTION check_exceedence (p_payroll_id IN NUMBER) RETURN Commented Bug 4511249
448 FUNCTION check_exceedence (p_assignment_id IN NUMBER) RETURN
449 BOOLEAN IS
450
451 -- Introduced payroll_end_date_cur for Bug 2836176
452
453 CURSOR payroll_end_date_cur IS
454 SELECT max(ptp.end_date)
455 FROM per_time_periods ptp
456 WHERE (ptp.time_period_id,payroll_id) in (SELECT MAX(ppc.time_period_id),ppc.payroll_id
457 FROM psp_payroll_controls ppc ,
458 psp_payroll_lines ppl -- Introduced for Bug 4511249
459 -- WHERE ppc.payroll_id = p_payroll_id Commented for Bug 4511249
460 WHERE ppc.payroll_control_id = ppl.payroll_control_id
461 AND ppl.assignment_id = p_assignment_id
462 AND ppc.source_type IN ('O','N')
463 group by ppc.payroll_id);
464
465
466 l_payroll_end_date DATE; --For Bug no 2836176
467
468 -- for bug fix 1779346
469 CURSOR sum_percent_cur IS --changed from sum to sum_percent_cur
470 SELECT sum(PERIOD_SCHEDULE_PERCENT)
471 FROM psp_matrix_driver
472 WHERE run_id = global_run_id
473 AND period_end_date > TRUNC ( NVL ( l_payroll_end_date,fnd_date.canonical_to_date('1900/01/01')))-- added this and conditoin for bug 2836176
474 GROUP BY period_start_date , period_end_date;
475
476 l_sum_percent NUMBER(5,2);
477
478 BEGIN
479 -- Added for Bug no 2836176 by tbalacha
480
481 OPEN payroll_end_date_cur;
482 FETCH payroll_end_date_cur INTO l_payroll_end_date;
483 CLOSE payroll_end_date_cur;
484
485 -- end of changes for 2836176
486
487 OPEN sum_percent_cur;
488 LOOP
489 FETCH sum_percent_cur INTO l_sum_percent;
490 EXIT WHEN sum_percent_cur%NOTFOUND;
491 IF (l_sum_percent >100 ) THEN
492 CLOSE sum_percent_cur;
493 RETURN FALSE;
494 END IF;
495
496 END LOOP;
497 CLOSE sum_percent_cur;
498 RETURN TRUE;
499 END check_exceedence;
500
501 /* End of code for bug no 2836176 */
502
503
504 /* this function is used to check the exceedence of the total of the schedule percentages of each distinct period in the temp table over the 1
505 00 percent limit in the SC_COPY form.*/
506
507 FUNCTION check_exceedence_sc_copy RETURN BOOLEAN IS
508 --For bug Fix 1779346
509 CURSOR sums IS
510 select sum(PERIOD_SCHEDULE_PERCENT)
511 from psp_matrix_driver
512 where run_id = global_run_id
513 group by PERIOD_START_DATE, PERIOD_END_DATE;
514 per number(5,2);
515
516 begin
517 OPEN sums;
518 LOOP
519 FETCH sums INTO per;
520 EXIT WHEN sums%NOTFOUND;
521 if (per >50) then
522 RETURN FALSE;
523 end if;
524 END LOOP;
525 RETURN TRUE;
526 CLOSE sums ;
527
528 --REM ================================================================
529 end check_exceedence_sc_copy;
530
531 /* The following procedure loads all the schedule lines associated with respective schedule hierarchies
532 for all those persons who are employees for the selected organizations.
533 The procedure finds all the schedule hierarchies and internally calls the load_table function to load
534 the corresponding schedule lines */
535
536 PROCEDURE load_organizations (retcode OUT NOCOPY NUMBER,
537 p_organization_id IN VARCHAR2,
538 p_period_from IN DATE,
539 p_period_to IN DATE,
540 p_report_type IN VARCHAR2,
541 p_business_group_id IN NUMBER,
542 p_set_of_books_id IN NUMBER) IS
543
547 psp_schedule_lines psl,
544 CURSOR sch_hier_cur(v_organization_id NUMBER) IS
545 SELECT distinct psh.schedule_hierarchy_id
546 FROM psp_schedule_hierarchy psh,
548 per_assignments_f paf
549 WHERE psh.schedule_hierarchy_id = psl.schedule_hierarchy_id
550 AND paf.assignment_id = psh.assignment_id
551 AND paf.organization_id = v_organization_id
552 AND psl.schedule_begin_date <= p_period_to
553 AND psl.schedule_end_date >= p_period_from
554 AND psl.business_group_id = p_business_group_id
555 AND psl.set_of_books_id = p_set_of_books_id
556 -- Included the following condition for bug fix 2020596 to prevent duplicate import of schedule data
557 -- for assignments that are assigned to more than one organization
558 AND NOT EXISTS (SELECT 1 FROM psp_matrix_driver pmd
559 WHERE pmd.run_id = global_run_id
560 AND pmd.schedule_line_id = psl.schedule_line_id);
561
562 sch_hier_rec sch_hier_cur%ROWTYPE;
563 l_run_id number;
564 l_req_id number;
565 l_organization_id NUMBER;
566 l_start_position NUMBER DEFAULT 1;
567 l_end_position NUMBER DEFAULT 1;
568 l_org_length NUMBER;
569 l_report_type VARCHAR2(10); -- Moved default value initialization to pl/sql block for bug fix 3697471
570
571 -- Introduced the following for bug fix 3697471
572 CURSOR sched_lines(schedule_hierarchy_id NUMBER) IS
573 SELECT schedule_line_id l_id,
574 schedule_begin_date sbd,
575 schedule_end_date sed,
576 schedule_percent sp
577 FROM psp_schedule_lines
578 WHERE schedule_hierarchy_id = schedule_hierarchy_id
579 AND schedule_end_date >= p_period_from
580 AND schedule_begin_date <= p_period_to;
581
582 CURSOR dates(p_schedule_hierarchy_id NUMBER) IS
583 SELECT schedule_begin_date dat , 'B'
584 FROM psp_schedule_lines
585 WHERE schedule_hierarchy_id = p_schedule_hierarchy_id
586 AND schedule_end_date >= p_period_from
587 AND schedule_begin_date <= p_period_to
588 UNION
589 SELECT schedule_end_date dat , 'E'
590 FROM psp_schedule_lines
591 WHERE schedule_hierarchy_id = p_schedule_hierarchy_id
592 AND schedule_end_date >= p_period_from
593 AND schedule_begin_date <= p_period_to
594 ORDER BY 1, 2 ;
595
596 k BINARY_INTEGER;
597 -- End of changes for bug fix 3697471
598
599 /* Changes for bug 2863953 */
600
601 Cursor c_all_org is
602 SELECT distinct paf.organization_id
603 FROM psp_schedule_hierarchy psh,
604 psp_schedule_lines psl,
605 per_assignments_f paf
606 WHERE psh.schedule_hierarchy_id = psl.schedule_hierarchy_id
607 AND paf.assignment_id = psh.assignment_id
608 AND psl.schedule_begin_date <= p_period_to
609 AND psl.schedule_end_date >= p_period_from
610 AND psl.business_group_id = p_business_group_id
611 AND psl.set_of_books_id = p_set_of_books_id;
612
613
614 /* End of changes for bug 2863953 */
615 BEGIN
616 l_report_type := 'Regular'; -- Introduced for bug fix 3697471 to fix GSCC Warning file.Sql.35
617
618 -- Initialize the fnd message routine
619 -- errbuf := 'Message Initialization failed';
620 fnd_msg_pub.initialize;
621
622 -- set the run id for this run
623 -- errbuf := 'Run ID Initialization failed';
624 psp_matrix_driver_pkg.set_runid;
625
626 -- Introduced the if condition for bug 2863953
627 If p_organization_id is null then
628
629 FOR org_rec in c_all_org Loop
630 OPEN sch_hier_cur(org_rec.organization_id);
631 LOOP -- Looping for all schedule hierarchies
632 FETCH sch_hier_cur INTO sch_hier_rec;
633 IF (sch_hier_cur%NOTFOUND) THEN
634 EXIT;
635 END IF;
636 -- call the matrix driver procedure to load the respective schedule lines
637 -- errbuf := 'Loading Matrix Driver for Schedule Hierarchy: ' ||
638 -- sch_hier_rec.schedule_hierarchy_id || ' had Failed';
639
640
641 k := 1;
642
643 OPEN dates(sch_hier_rec.schedule_hierarchy_id);
644 FETCH dates BULK COLLECT INTO dat, type_dat;
645 CLOSE dates;
646
647 FOR rowno IN 1..(dat.COUNT - 1) LOOP
648 IF (type_dat(rowno) = 'B' AND type_dat(rowno+1) = 'B') THEN
649 schedule_chunk.schedule_begin_date(k) := dat(rowno);
650 schedule_chunk.schedule_end_date(k) := dat(rowno+1) - 1;
651 k := k+1;
652 ELSIF (type_dat(rowno) = 'B' AND type_dat(rowno+1) = 'E') THEN
653 schedule_chunk.schedule_begin_date(k) := dat(rowno);
654 schedule_chunk.schedule_end_date(k) := dat(rowno+1);
655 k := k+1;
656 ELSIF (type_dat(rowno) = 'E' AND type_dat(rowno+1) = 'E') THEN
657 schedule_chunk.schedule_begin_date(k) := dat(rowno) + 1;
658 schedule_chunk.schedule_end_date(k) := dat(rowno+1);
659 k := k+1;
660 END IF;
661 END LOOP;
662
663 FORALL rowno IN 1..schedule_chunk.schedule_begin_date.COUNT
664 INSERT INTO psp_matrix_driver
665 (RUN_ID, SCHEDULE_LINE_ID,
666 PERIOD_START_DATE,
667 PERIOD_END_DATE,
668 PERIOD_SCHEDULE_PERCENT)
669 SELECT global_run_id, schedule_line_id,
670 GREATEST(p_period_from, schedule_chunk.schedule_begin_date(rowno)),
671 LEAST(p_period_to, schedule_chunk.schedule_end_date(rowno)),
672 schedule_percent
673 FROM psp_schedule_lines psl
674 WHERE schedule_hierarchy_id = sch_hier_rec.schedule_hierarchy_id
675 AND psl.schedule_begin_date <= p_period_to
676 AND psl.schedule_end_date >= p_period_from
677 AND psl.schedule_begin_date <= schedule_chunk.schedule_end_date(rowno)
681 type_dat.delete;
678 AND psl.schedule_end_date >= schedule_chunk.schedule_begin_date(rowno);
679
680 dat.delete;
682 schedule_chunk.schedule_end_date.delete;
683 schedule_chunk.schedule_begin_date.delete;
684
685 END LOOP; -- End Schedule Hierarchies
686 CLOSE sch_hier_cur;
687
688
689 END LOOP; -- End loop for for c_all_org cursor
690
691
692 ELSE
693
694 l_org_length := LENGTH(p_organization_id);
695
696 -- Split the selected organizations into individual Organizations and retrieve Schedule Hierarchies for them
697 LOOP -- Loop for splitting Organization ID
698
699 l_end_position := INSTR(p_organization_id, ',', l_start_position);
700
701 IF (l_end_position = 0) THEN
702 l_end_position := l_org_length + 1;
703 END IF;
704
705 -- errbuf := 'Retrieving Organizations failed';
706 l_organization_id := TO_NUMBER(SUBSTR(p_organization_id, l_start_position,
707 (l_end_position - l_start_position)));
708
709 -- errbuf := 'Retrieving Schedule Hierarchies failed for Organization: ' || TO_CHAR(l_organization_id);
710 OPEN sch_hier_cur(l_organization_id);
711 LOOP -- Looping for all schedule hierarchies
712 FETCH sch_hier_cur INTO sch_hier_rec;
713 IF (sch_hier_cur%NOTFOUND) THEN
714 EXIT;
715 END IF;
716 -- call the matrix driver procedure to load the respective schedule lines
717 -- errbuf := 'Loading Matrix Driver for Schedule Hierarchy: ' ||
718 -- sch_hier_rec.schedule_hierarchy_id || ' had Failed';
719
720 -- psp_matrix_driver_pkg.load_table(sch_hier_rec.schedule_hierarchy_id); Commented for bug fix 3697471
721 -- psp_matrix_driver_pkg.purge_table; Commented for bug fix 3697471
722
723
724 -- Introduced the following for bug fix 3697471
725 k := 1;
726
727 OPEN dates(sch_hier_rec.schedule_hierarchy_id);
728 FETCH dates BULK COLLECT INTO dat, type_dat;
729 CLOSE dates;
730
731 FOR rowno IN 1..(dat.COUNT - 1) LOOP
732 IF (type_dat(rowno) = 'B' AND type_dat(rowno+1) = 'B') THEN
733 schedule_chunk.schedule_begin_date(k) := dat(rowno);
734 schedule_chunk.schedule_end_date(k) := dat(rowno+1) - 1;
735 k := k+1;
736 ELSIF (type_dat(rowno) = 'B' AND type_dat(rowno+1) = 'E') THEN
737 schedule_chunk.schedule_begin_date(k) := dat(rowno);
738 schedule_chunk.schedule_end_date(k) := dat(rowno+1);
739 k := k+1;
740 ELSIF (type_dat(rowno) = 'E' AND type_dat(rowno+1) = 'E') THEN
741 schedule_chunk.schedule_begin_date(k) := dat(rowno) + 1;
742 schedule_chunk.schedule_end_date(k) := dat(rowno+1);
743 k := k+1;
744 END IF;
745 END LOOP;
746
747
748 FORALL rowno IN 1..schedule_chunk.schedule_begin_date.COUNT
749 INSERT INTO psp_matrix_driver
750 (RUN_ID, SCHEDULE_LINE_ID,
751 PERIOD_START_DATE,
752 PERIOD_END_DATE,
753 PERIOD_SCHEDULE_PERCENT)
754 SELECT global_run_id, schedule_line_id,
755 GREATEST(p_period_from, schedule_chunk.schedule_begin_date(rowno)),
756 LEAST(p_period_to, schedule_chunk.schedule_end_date(rowno)),
757 schedule_percent
758 FROM psp_schedule_lines psl
759 WHERE schedule_hierarchy_id = sch_hier_rec.schedule_hierarchy_id
760 AND psl.schedule_begin_date <= p_period_to
761 AND psl.schedule_end_date >= p_period_from
762 AND psl.schedule_begin_date <= schedule_chunk.schedule_end_date(rowno)
763 AND psl.schedule_end_date >= schedule_chunk.schedule_begin_date(rowno);
764
765 dat.delete;
766 type_dat.delete;
767 schedule_chunk.schedule_end_date.delete;
768 schedule_chunk.schedule_begin_date.delete;
769 -- End of changes for bug fix 3697471
770
771 END LOOP; -- End Schedule Hierarchies
772 CLOSE sch_hier_cur;
773
774 IF (l_end_position > l_org_length) THEN
775 EXIT;
776 END IF;
777
778 l_start_position := l_end_position + 1;
779 END LOOP; -- End Organization Id split
780
781 End if ; /* Introduced for bug 2863953 */
782
783 /***** Commented for bug fix 3697471
784 -- Update the psp_matrix_driver table for the current run, deleting zero schedule percent records
785 -- errbuf := 'Deleting zero schedule percent records failed';
786 DELETE psp_matrix_driver pmd
787 WHERE run_id = global_run_id
788 AND period_schedule_percent = 0;
789
790 / * Commented for bug fix 2368498
791 UPDATE psp_matrix_driver pmd
792 SET period_start_date = period_start_date + 1
793 WHERE run_id = global_run_id
794 AND EXISTS (SELECT 1
795 FROM psp_schedule_lines psl,
796 psp_schedule_lines psl2
797 WHERE psl.schedule_line_id = pmd.schedule_line_id
798 AND psl2.schedule_hierarchy_id = psl.schedule_hierarchy_id
799 AND psl2.schedule_end_date = pmd.period_start_date
800 AND psl2.schedule_line_id <> psl.schedule_line_id);
801
802 UPDATE psp_matrix_driver pmd
803 SET period_end_date = period_end_date - 1
804 WHERE run_id = global_run_id
805 AND EXISTS (SELECT 1
806 FROM psp_schedule_lines psl,
807 psp_schedule_lines psl2
808 WHERE psl.schedule_line_id = pmd.schedule_line_id
809 AND psl2.schedule_hierarchy_id = psl.schedule_hierarchy_id
810 AND psl2.schedule_begin_date = pmd.period_end_date
811 AND psl2.schedule_line_id <> psl.schedule_line_id);
815 -- Updating the period_start_date for periods in between schedule begin and end dates
812 End of comment for bug 2368498 * /
813
814 -- Introduced for bug fix 2368498
816 -- errbuf := 'Period End Date Update of Matrix Driver failed';
817 UPDATE psp_matrix_driver pmd
818 SET period_end_date = period_end_date - 1
819 WHERE run_id = global_run_id
820 AND period_start_date < period_end_date
821 AND period_start_date = (SELECT MIN(psl1.schedule_begin_date)
822 FROM psp_schedule_lines psl1
823 WHERE psl1.schedule_hierarchy_id = (SELECT psl2.schedule_hierarchy_id
824 FROM psp_schedule_lines psl2
825 WHERE psl2.schedule_line_id = pmd.schedule_line_id))
826 AND EXISTS (SELECT 1
827 FROM psp_schedule_lines psl1
828 WHERE psl1.schedule_line_id <> pmd.schedule_line_id
829 AND psl1.schedule_begin_date = pmd.period_end_date
830 AND psl1.schedule_hierarchy_id = (SELECT psl2.schedule_hierarchy_id
831 FROM psp_schedule_lines psl2
832 WHERE psl2.schedule_line_id = pmd.schedule_line_id));
833
834 UPDATE psp_matrix_driver pmd
835 SET period_end_date = period_end_date - 1
836 WHERE run_id = global_run_id
837 AND period_start_date < period_end_date
838 AND NOT (NOT EXISTS (SELECT 1
839 FROM psp_schedule_lines psl1
840 WHERE psl1.schedule_line_id <> pmd.schedule_line_id
841 AND psl1.schedule_begin_date = pmd.period_end_date
842 AND psl1.schedule_hierarchy_id = (SELECT psl2.schedule_hierarchy_id
843 FROM psp_schedule_lines psl2
844 where psl2.schedule_line_id = pmd.schedule_line_id))
845 AND EXISTS (SELECT 1
846 FROM psp_schedule_lines psl1
847 WHERE psl1.schedule_end_date = pmd.period_end_date
848 AND psl1.schedule_hierarchy_id = (SELECT psl2.schedule_hierarchy_id
849 FROM psp_schedule_lines psl2
850 WHERE psl2.schedule_line_id = pmd.schedule_line_id)))
851 AND period_start_date <> (SELECT MIN(psl1.schedule_begin_date)
852 FROM psp_schedule_lines psl1
853 WHERE psl1.schedule_hierarchy_id = (SELECT psl2.schedule_hierarchy_id
854 FROM psp_schedule_lines psl2
855 WHERE psl2.schedule_line_id = pmd.schedule_line_id));
856
857 -- Updating the period_start_date for periods in between schedule begin and end dates
858 -- errbuf := 'Period Start Date Update of Matrix Driver failed';
859 UPDATE psp_matrix_driver pmd
860 SET period_start_date = period_start_date + 1
861 WHERE run_id = global_run_id
862 AND period_start_date < period_end_date
863 AND NOT EXISTS (SELECT 1
864 FROM psp_schedule_lines psl1
865 WHERE psl1.schedule_begin_date = pmd.period_start_date
866 AND psl1.schedule_hierarchy_id = (SELECT psl2.schedule_hierarchy_id
867 FROM psp_schedule_lines psl2
868 WHERE psl2.schedule_line_id = pmd.schedule_line_id))
869 AND period_start_date <> (SELECT MIN(psl1.schedule_begin_date)
870 FROM psp_schedule_lines psl1
871 WHERE psl1.schedule_hierarchy_id = (SELECT psl2.schedule_hierarchy_id
872 FROM psp_schedule_lines psl2
873 WHERE psl2.schedule_line_id = pmd.schedule_line_id));
874
875 UPDATE psp_matrix_driver pmd
876 SET period_start_date = period_start_date + 1
877 WHERE run_id = global_run_id
878 AND period_start_date < period_end_date
879 AND EXISTS (SELECT 1
880 FROM psp_schedule_lines psl1
881 WHERE psl1.schedule_begin_date = pmd.period_start_date
882 AND psl1.schedule_hierarchy_id = (SELECT psl2.schedule_hierarchy_id
883 FROM psp_schedule_lines psl2
884 WHERE psl2.schedule_line_id = pmd.schedule_line_id))
885 AND EXISTS (SELECT 1
886 FROM psp_schedule_lines psl1
887 WHERE psl1.schedule_line_id <> pmd.schedule_line_id
888 AND psl1.schedule_end_date = pmd.period_start_date
889 AND psl1.schedule_hierarchy_id = (SELECT psl2.schedule_hierarchy_id
890 FROM psp_schedule_lines psl2
891 WHERE psl2.schedule_line_id = pmd.schedule_line_id))
892 AND period_start_date <> (SELECT MIN(psl1.schedule_begin_date)
893 FROM psp_schedule_lines psl1
894 WHERE psl1.schedule_hierarchy_id = (SELECT psl2.schedule_hierarchy_id
895 FROM psp_schedule_lines psl2
896 WHERE psl2.schedule_line_id = pmd.schedule_line_id));
897 -- End of Bug fix 2368498
898 End of changes for bug fix 3697471 *****/
899
900 -- For Exception Report delete records in psp_matrix_driver for which the schedule percentage is 100
901 IF (p_report_type = 'E') THEN
902 -- errbuf := 'Deleting schedules equal to 100 percent failed';
903 l_report_type := 'Exception';
904 DELETE psp_matrix_driver pmd
905 WHERE run_id = global_run_id
906 AND EXISTS (SELECT 1
907 FROM psp_schedule_lines psl,
908 psp_schedule_lines psl2
909 WHERE psl2.schedule_hierarchy_id = psl.schedule_hierarchy_id
910 AND psl.schedule_line_id = pmd.schedule_line_id
911 AND psl2.schedule_begin_date <= pmd.period_end_date
912 AND psl2.schedule_end_date >= pmd.period_start_date
913 GROUP BY psl2.schedule_hierarchy_id
914 HAVING SUM(psl2.schedule_percent) = 100);
915 END IF;
916
917 /***** Commented for bug fix 3697471
918 -- Update the period_end_date to p_period_to if period_end_date > p_period_to
919 UPDATE psp_matrix_driver
920 SET period_end_date = p_period_to
921 WHERE run_id = global_run_id
922 AND period_end_date > p_period_to;
923
924 UPDATE psp_matrix_driver
925 SET period_start_date = p_period_from
926 WHERE run_id = global_run_id
927 AND period_start_date < p_period_from;
931
928 End of comment for bug fix 3697471 *****/
929
930 retcode := 0;
932 EXCEPTION
933 WHEN OTHERS THEN
934 -- psp_message_s.print_error( p_mode => FND_FILE.LOG,
935 -- p_print_header => FND_API.G_FALSE);
936 retcode := 2;
937 END load_organizations;
938
939
940
941 /**********************************************************
942 Created By : lveerubh
943
944 Date Created By : 05-SEP-2001
945
946 Purpose : This procedure does the following jobs :
947 X=10:Sets the Run id for the psp_matrix_table
948 X=20:For each organization chosen by the user (p_list_organization_id)
949 it loads the table psp_matrix_Driver by calling procedure
950 load_table_schedule
951 X=30:Updates the period_start_date of the schedule_lines in matrix_driver table
952 ,for each organization,except the schedule lines with minimum period_start_date
953 so that data in the table becomes as required for the report PSPLSODR.rdf
954 X=40:Deletes the schedule lines with zero schedule percent for that run_id.
955 X=50:Checks if the user has asked for an Exception report. If yes, then it deletes
956 for a particular organization and particular period_start_date,period_end_date ,
957 all those schedule lines whose sum of period_schedule_percent equals 100
958
959
960 Know limitations, enhancements or remarks
961
962 Change History
963
964 Who When What
965 Lveerubh 05-SEP-2001 Created the procedure
966 Lveerubh 03-OCT-2001 1. Removing the call for the report from the package -Bug 2022193
967
968 ***************************************************************/
969 procedure load_org_schedule(p_return_status OUT NOCOPY NUMBER,
970 p_log_message OUT NOCOPY VARCHAR2,
971 p_list_organization_id IN VARCHAR2,
972 p_period_from IN VARCHAR2,
973 p_period_to IN VARCHAR2,
974 p_report_type IN VARCHAR2,
975 p_business_group_id IN NUMBER,
976 p_set_of_books_id IN NUMBER
977 )
978 IS
979
980
981 CURSOR c_schedule_percent(F_run_id NUMBER)
982 IS
983 SELECT pdls.organization_id,
984 pmd.period_start_date,
985 pmd.period_end_date
986 FROM psp_default_labor_schedules pdls,
987 psp_matrix_driver pmd
988 WHERE pdls.org_schedule_id = pmd.schedule_line_id
989 AND pmd.run_id = F_run_id
990 GROUP BY pdls.organization_id,
991 pmd.period_start_date,
992 pmd.period_end_date
993 HAVING SUM(pmd.period_schedule_percent) = 100;
994
995
996 CURSOR c_schedule_line_id( F_organization_id NUMBER,
997 F_period_start_date DATE,
998 F_period_end_date DATE,
999 F_run_id NUMBER)
1000 IS
1001 SELECT pdls.org_schedule_id
1002 FROM psp_default_labor_schedules pdls,
1003 psp_matrix_driver pmd
1004 WHERE pdls.organization_id = F_organization_id
1005 AND pdls.schedule_begin_date <= F_period_end_date
1006 AND pdls.schedule_end_date >= F_period_start_date
1007 AND pmd.schedule_line_id = pdls.org_Schedule_id
1008 AND pmd.run_id = F_run_id;
1009
1010
1011 CURSOR c_period_start_date(F_run_id NUMBER)
1012 IS
1013 SELECT min(pmd1.period_start_date) period_start_date,
1014 pdls.organization_id
1015 FROM psp_matrix_driver pmd1,
1016 psp_default_labor_schedules pdls
1017 WHERE pmd1.run_id = F_run_id
1018 AND pmd1.schedule_line_id = pdls.org_schedule_id
1019 GROUP BY pdls.organization_id;
1020
1021 rec_sch_percent c_schedule_percent%ROWTYPE;
1022 rec_sch_line_id c_schedule_line_id%ROWTYPE;
1023 rec_period_start_date c_period_start_date%ROWTYPE;
1024
1025 l_req_id NUMBER;
1026 l_call_status BOOLEAN;
1027 l_rphase VARCHAR2(30);
1028 l_rstatus VARCHAR2(30);
1029 l_dphase VARCHAR2(30);
1030 l_dstatus VARCHAR2(30);
1031 l_message VARCHAR2(240);
1032 l_run_id NUMBER;
1033 l_period_start_date DATE;
1034 l_end_position NUMBER DEFAULT 1;
1035 l_begin_position NUMBER DEFAULT 1;
1036 l_new_position NUMBER ;
1037 l_org_id VARCHAR2(10);
1038 l_org_length NUMBER;
1039 l_report_type VARCHAR2(10); -- Moved default value initialization to pl/sql block for bug fix 3697471
1040
1041
1042 CURSOR c_all_org
1043 IS
1044 SELECT distinct organization_id
1045 FROM psp_default_labor_schedules
1046 WHERE business_group_id = p_business_group_id
1047 AND set_of_books_id = p_set_of_books_id;
1048
1049
1050 BEGIN
1051 l_report_type := 'Regular'; -- Introduced for bug fix 3697471 to fix GSCC Warning file.Sql.35
1052 -- Initialize the fnd message routine
1053 fnd_msg_pub.initialize;
1054
1055 --set the Run Id for the current request
1056 --dbms_output.put_line('Organization List '||p_list_organization_id);
1057
1058 --X=10
1059 psp_matrix_driver_pkg.set_runid;
1060 l_run_id := psp_matrix_driver_pkg.get_run_id;
1061 --dbms_output.put_line(l_run_id);
1062 --errbuf := 'Run id '||global_run_id;
1063 psp_matrix_driver_pkg.purge_table;
1064
1065 --X=20
1066
1067 If p_list_organization_id is null then
1068
1069 For l_org_rec in c_all_org
1070 loop
1074 end loop;
1071 psp_matrix_driver_pkg.load_table_schedule(to_number(l_org_rec.organization_id)
1072 ,p_business_group_id,p_set_of_books_id);
1073 psp_matrix_driver_pkg.purge_table;
1075
1076
1077 else
1078
1079 l_org_length := length(p_list_organization_id) ;
1080 LOOP -- Loop for splitting Organization ID
1081 l_end_position := INSTR(p_list_organization_id, ',', l_begin_position);
1082 IF (l_end_position = 0) THEN
1083 l_end_position := l_org_length + 1;
1084 END IF;
1085 l_org_id := TO_NUMBER(SUBSTR(p_list_organization_id, l_begin_position,(l_end_position - l_begin_position)));
1086 psp_matrix_driver_pkg.load_table_schedule(to_number(l_org_id),p_business_group_id,p_set_of_books_id);
1087 psp_matrix_driver_pkg.purge_table;
1088 IF (l_end_position > l_org_length) THEN
1089 EXIT;
1090 END IF;
1091 l_begin_position := l_end_position + 1;
1092 END LOOP; -- End Organization Id split
1093
1094 end if;
1095 --X=30
1096 --Updating the records created in psp_matrix_driver in the manner required to be displayed in the report
1097 UPDATE psp_matrix_driver pmd
1098 SET period_start_date = period_start_date + 1
1099 WHERE run_id =l_run_id
1100 AND EXISTS (SELECT 1
1101 FROM psp_default_labor_schedules pdls1,
1102 psp_default_labor_schedules pdls2
1103 WHERE pdls1.org_schedule_id=pmd.schedule_line_id
1104 AND pdls1.organization_id=pdls2.organization_id
1105 AND pdls1.org_schedule_id<>pdls2.org_schedule_id
1106 AND pdls2.schedule_end_date=pmd.period_start_date);
1107
1108 UPDATE psp_matrix_driver pmd
1109 SET period_end_date = period_end_date - 1
1110 WHERE run_id =l_run_id
1111 AND EXISTS (SELECT pmd.period_end_date
1112 FROM psp_default_labor_schedules pdls1,
1113 psp_default_labor_schedules pdls2
1114 WHERE pdls1.org_schedule_id=pmd.schedule_line_id
1115 AND pdls2.organization_id=pdls1.organization_id
1116 AND pdls2.schedule_begin_date=pmd.period_end_date
1117 AND pdls1.org_schedule_id<>pdls2.org_schedule_id);
1118
1119 --X=40
1120 -- Delete the Zero Schedule Percent
1121 DELETE psp_matrix_driver
1122 WHERE run_id = global_run_id
1123 AND period_schedule_percent = 0;
1124
1125 --X=50
1126 ---Deleting Schedule Percent equal to 100
1127 IF p_report_type = 'E' THEN
1128 -- l_report_type:='Exception';
1129 OPEN c_schedule_percent(l_run_id);
1130 LOOP
1131 FETCH c_schedule_percent INTO rec_sch_percent ;
1132 EXIT WHEN c_schedule_percent%NOTFOUND;
1133
1134 OPEN c_schedule_line_id(rec_sch_percent.organization_id,
1135 rec_sch_percent.period_start_date,
1136 rec_sch_percent.period_end_date,
1137 l_run_id);
1138 LOOP
1139 FETCH c_schedule_line_id INTO rec_sch_line_id;
1140 EXIT WHEN c_schedule_line_id%NOTFOUND;
1141
1142 DELETE psp_matrix_driver pmd
1143 WHERE pmd.run_id = l_run_id
1144 And pmd.schedule_line_id = rec_sch_line_id.org_schedule_id
1145 And pmd.period_start_date = rec_sch_percent.period_start_date
1146 And pmd.period_end_date = rec_sch_percent.period_end_date;
1147
1148 END LOOP; --End of c_schedule_line_id
1149 CLOSE c_schedule_line_id;
1150 END LOOP; --End of c_schedule_percent
1151 CLOSE c_schedule_percent;
1152 END IF;
1153
1154 --Updating the records greater than p_period_to to NUll ,so that in Schedule Summary in report these will be
1155 --displayed with end date as p_period_to
1156 UPDATE psp_matrix_driver
1157 SET period_end_date = p_period_to
1158 WHERE run_id = l_run_id
1159 AND period_end_date > p_period_to;
1160
1161 UPDATE psp_matrix_driver
1162 SET period_start_date = p_period_from
1163 WHERE run_id = l_run_id
1164 AND period_start_date < p_period_from;
1165
1166 p_return_status := 0;
1167 p_log_message := 'Success';
1168
1169 EXCEPTION
1170 WHEN OTHERS THEN
1171 p_log_message := sqlerrm;
1172 p_return_status :=2;
1173 END load_org_schedule;
1174
1175 /**********************************************************
1176 Created By : lveerubh
1177
1178 Date Created By : 05-SEP-2001
1179
1180 Purpose : This procedure is introduced to populate psp_matrix_driver with the
1181 data from psp_default_labor_schedules.It sorts the pool of begin and end dates
1182 specified on the various schedule lines. After the sort it inserts the dates into
1183 pl/sql table thereby forming distinct periods of consistent charging instructions.
1184 Once the pl/sql table is loaded, the start and end dates of the distinct periods are
1185 inserted into the temp table.As the cursor parses through each record a correponding
1186 schedule percentage is inserted according to the overlap of these distinct periods with
1187 the periods specified on each schedule line
1188 Know limitations, enhancements or remarks
1189
1190 Change History
1191
1192 Who When What
1193 Lveerubh 05-SEP-2001 Created the procedure
1194
1195 ***************************************************************/
1196
1197
1198 PROCEDURE load_table_schedule(sch_id NUMBER,
1199 p_business_group_id NUMBER,
1203 IS
1200 p_set_of_books_id NUMBER)
1201 IS
1202 CURSOR sched_lines(s_id NUMBER)
1204 SELECT org_schedule_id l_id,
1205 schedule_begin_date sbd,
1206 schedule_end_date sed,
1207 schedule_percent sp
1208 FROM psp_default_labor_schedules
1209 WHERE organization_id = s_id
1210 AND business_group_id = p_business_group_id
1211 AND set_of_books_id = p_set_of_books_id;
1212
1213 --Get the dates (schedule begin dates and end dates) in ascending order of dates. If a date is present in
1214 --begin as well as the end date, bring the End Date first.
1215 CURSOR dates(s_id NUMBER) IS
1216 SELECT schedule_begin_date dat , 'B'
1217 FROM psp_default_labor_schedules
1218 WHERE organization_id = s_id
1219 AND business_group_id = p_business_group_id
1220 AND set_of_books_id = p_set_of_books_id
1221 UNION
1222 SELECT schedule_end_date dat , 'E'
1223 FROM psp_default_labor_schedules
1224 WHERE organization_id = s_id
1225 AND business_group_id = p_business_group_id
1226 AND set_of_books_id = p_set_of_books_id
1227 ORDER BY 1, 2 ;
1228 i BINARY_INTEGER :=0;
1229 j BINARY_INTEGER :=0;
1230
1231 sch_rec sched_lines%ROWTYPE;
1232 per number;
1233 temp date;
1234 num number;
1235 dummy char(1);
1236 v_count1 number;
1237 v_count2 number;
1238 l_count3 number;
1239 BEGIN
1240 --From the cursor, get the dates in pl/sql table dat1.
1241 OPEN dates(sch_id);
1242 LOOP
1243 i := i+1;
1244 FETCH dates INTO temp, dummy;
1245 EXIT WHEN dates%NOTFOUND;
1246 -- dbms_output.put_line('date is:'||to_char(temp)||' the organization_id '||sch_id);
1247 dat1(i) := temp;
1248 type_dat(i) := dummy;
1249 END LOOP;
1250 CLOSE dates;
1251 --
1252 --Copy dates from table 'dat1' to table 'dat'. If there is a Begin Date which is exactly 1 day greater
1253 --than the End Date, DO NOT include this Begin Date in table 'dat'.
1254 i := 1;
1255 dat(i) := dat1(i);
1256 j := 2;
1257 FOR i IN 2..dat1.COUNT LOOP
1258
1259 IF dat1(i) = dat1(i-1) + 1 THEN
1260 IF type_dat(i) = 'B' and type_dat(i-1) = 'E' THEN
1261 null;
1262 ELSE
1263 dat(j) := dat1(i);
1264 j := j + 1;
1265 END IF;
1266 ELSE
1267 dat(j) := dat1(i);
1268 j := j + 1;
1269 END IF;
1270
1271 END LOOP;
1272
1273 --
1274 --Insert records in temporary table PSP_MATRIX_DRIVER. There may be some dates in 'dat1' which were not
1275 --included in 'dat' because Begin Date was exactly 1 day greater than the End Date. In such a case,
1276 --instead of comparing dates of 'dat' with those of psp_schedule_lines, compare (Begin Date+1) and End
1277 --Date of 'dat' with those of psp_schedule_lines.
1278 --
1279 OPEN sched_lines(sch_id);
1280 LOOP
1281 FETCH sched_lines INTO sch_rec;
1282 EXIT WHEN sched_lines%NOTFOUND;
1283 num:=dat.COUNT-1;
1284 FOR i in 1 .. num LOOP
1285
1286 IF dat(i+1) = dat1(i+1) THEN
1287
1288 IF ((dat(i) between sch_rec.sbd and sch_rec.sed) AND (dat(i+1) between sch_rec.sbd and sch_rec.sed)) THEN
1289 per:= sch_rec.sp;
1290
1291 ELSE
1292 per:=0;
1293
1294 END IF;
1295
1296 ELSE
1297 IF (((dat(i)+1) between sch_rec.sbd and sch_rec.sed) AND (dat(i+1) between sch_rec.sbd and sch_rec.sed)) THEN
1298 per:= sch_rec.sp;
1299
1300 ELSE
1301 per:=0;
1302
1303 END IF;
1304
1305 END IF;
1306
1307 INSERT INTO psp_matrix_driver(RUN_ID,
1308 SCHEDULE_LINE_ID,
1309 PERIOD_START_DATE,
1310 PERIOD_END_DATE,
1311 PERIOD_SCHEDULE_PERCENT) values
1312 (global_run_id,
1313 sch_rec.l_id,
1314 dat(i),
1315 dat(i+1),
1316 per);
1317 END LOOP;
1318 END LOOP;
1319 EXCEPTION
1320 WHEN NO_DATA_FOUND THEN
1321 null;
1322 WHEN OTHERS THEN
1323 null;
1324 END load_table_schedule;
1325
1326
1327 /*****************************************************************************************
1328 Procedure name: check_sch_hierarchy
1329 Purpose : This Procedure checks the hierarchy for the current asignment and prevents
1330 that user from committing any new changes when there are any existing / new errors.
1331 Description: this procedure checks for schedules exceeding 100% issue for al the hierarchies
1332 and for the respective assignment.It takes assignment_id and payroll_id as parameters to
1333 validate the schedule lines that are valid after the last imported payroll_date.
1334 Creation date :14-APR-2003
1335 *****************************************************************************************/
1336
1337 PROCEDURE check_sch_hierarchy(p_assignment_id IN NUMBER,
1338 p_payroll_id IN NUMBER,
1339 p_hierarchy_id OUT NOCOPY NUMBER,
1340 p_invalid_count OUT NOCOPY NUMBER) IS
1341
1342
1343 CURSOR sch_hier_cur IS
1344 SELECT schedule_hierarchy_id
1345 FROM psp_schedule_hierarchy
1346 WHERE assignment_id = p_assignment_id;
1347
1348 l_schedule_hierarchy_id NUMBER ( 15 );
1349 l_invalid_count NUMBER DEFAULT 0;
1350
1351 BEGIN
1352 OPEN sch_hier_cur;
1353 LOOP
1354 FETCH sch_hier_cur INTO l_schedule_hierarchy_id;
1355 EXIT WHEN sch_hier_cur%NOTFOUND;
1356 psp_matrix_driver_pkg.clear_table('REFRESH');
1357 psp_matrix_driver_pkg.purge_table;
1358 psp_matrix_driver_pkg.load_table(l_schedule_hierarchy_id);
1359 IF (NOT psp_matrix_driver_pkg.check_exceedence(p_assignment_id)) THEN
1360 l_invalid_count := l_invalid_count + 1;
1361 p_hierarchy_id :=l_schedule_hierarchy_id;
1362 END IF;
1363 END LOOP;
1364 CLOSE sch_hier_cur;
1365 psp_matrix_driver_pkg.clear_table('REFRESH');
1366 psp_matrix_driver_pkg.purge_table;
1367 p_invalid_count := l_invalid_count;
1368 END check_sch_hierarchy;
1369
1370 /* End of code for Bug no 2836176 By tbalacha*/
1371
1372
1373 end psp_matrix_driver_pkg;
[Home] [Help]