1 package body psp_matrix_driver_pkg as
2 /* $Header: PSPLSMTB.pls 120.5.12010000.4 2009/01/27 07:54:38 sabvenug 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;
406
407 FORALL rowno IN 1..schedule_chunk.schedule_begin_date.COUNT
408 INSERT INTO psp_matrix_driver
409 (RUN_ID, SCHEDULE_LINE_ID,
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
477 -- Bug 7634722
478 -- Added cursor sum_percent_cur_left to include newly created schedule periods
479 --(whose end-dates < payroll_last_run_date) to be included in the check for LS>100%
480 CURSOR sum_percent_cur_left IS
481 SELECT sum(PERIOD_SCHEDULE_PERCENT)
482 FROM psp_matrix_driver
483 WHERE run_id = global_run_id
484 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
485 GROUP BY period_start_date , period_end_date;
486
487
488
489 l_sum_percent NUMBER(5,2);
490
491 BEGIN
492 -- Added for Bug no 2836176 by tbalacha
493
494 OPEN payroll_end_date_cur;
495 FETCH payroll_end_date_cur INTO l_payroll_end_date;
496 CLOSE payroll_end_date_cur;
497
498 -- end of changes for 2836176
499
500 OPEN sum_percent_cur;
501 LOOP
502 FETCH sum_percent_cur INTO l_sum_percent;
503 EXIT WHEN sum_percent_cur%NOTFOUND;
504 IF (l_sum_percent >100 ) THEN
505 CLOSE sum_percent_cur;
506 RETURN FALSE;
507 END IF;
508
509 END LOOP;
510 CLOSE sum_percent_cur;
511
512 -- Bug 7634722
513 -- Added cursor sum_percent_cur_left to include newly created schedule periods
514 --(whose end-dates < payroll_last_run_date) to be included in the check for LS>100%
515
516 OPEN sum_percent_cur_left;
517 LOOP
518 FETCH sum_percent_cur_left INTO l_sum_percent;
519 EXIT WHEN sum_percent_cur_left%NOTFOUND;
520 IF (l_sum_percent >100 ) THEN
521 CLOSE sum_percent_cur_left;
522 RETURN FALSE;
523 END IF;
524
525 END LOOP;
526 CLOSE sum_percent_cur_left;
527
528
529
530
531
532 RETURN TRUE;
533 END check_exceedence;
534
535 /* End of code for bug no 2836176 */
536
537
538 /* 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
539 00 percent limit in the SC_COPY form.*/
540
541 FUNCTION check_exceedence_sc_copy RETURN BOOLEAN IS
542 --For bug Fix 1779346
543 CURSOR sums IS
544 select sum(PERIOD_SCHEDULE_PERCENT)
545 from psp_matrix_driver
546 where run_id = global_run_id
547 group by PERIOD_START_DATE, PERIOD_END_DATE;
548 per number(5,2);
549
550 begin
551 OPEN sums;
552 LOOP
553 FETCH sums INTO per;
554 EXIT WHEN sums%NOTFOUND;
555 if (per >50) then
556 RETURN FALSE;
557 end if;
558 END LOOP;
559 RETURN TRUE;
560 CLOSE sums ;
561
562 --REM ================================================================
563 end check_exceedence_sc_copy;
564
565 /* The following procedure loads all the schedule lines associated with respective schedule hierarchies
566 for all those persons who are employees for the selected organizations.
567 The procedure finds all the schedule hierarchies and internally calls the load_table function to load
568 the corresponding schedule lines */
569
570 PROCEDURE load_organizations (retcode OUT NOCOPY NUMBER,
571 p_organization_id IN VARCHAR2,
572 p_period_from IN DATE,
573 p_period_to IN DATE,
574 p_report_type IN VARCHAR2,
575 p_business_group_id IN NUMBER,
576 p_set_of_books_id IN NUMBER) IS
577
578 CURSOR sch_hier_cur(v_organization_id NUMBER) IS
579 SELECT distinct psh.schedule_hierarchy_id
580 FROM psp_schedule_hierarchy psh,
581 psp_schedule_lines psl,
582 per_assignments_f paf
583 WHERE psh.schedule_hierarchy_id = psl.schedule_hierarchy_id
584 AND paf.assignment_id = psh.assignment_id
585 AND paf.organization_id = v_organization_id
586 AND psl.schedule_begin_date <= p_period_to
587 AND psl.schedule_end_date >= p_period_from
588 AND psl.business_group_id = p_business_group_id
589 AND psl.set_of_books_id = p_set_of_books_id
590 -- Included the following condition for bug fix 2020596 to prevent duplicate import of schedule data
591 -- for assignments that are assigned to more than one organization
592 AND NOT EXISTS (SELECT 1 FROM psp_matrix_driver pmd
593 WHERE pmd.run_id = global_run_id
594 AND pmd.schedule_line_id = psl.schedule_line_id);
595
596 sch_hier_rec sch_hier_cur%ROWTYPE;
597 l_run_id number;
598 l_req_id number;
599 l_organization_id NUMBER;
600 l_start_position NUMBER DEFAULT 1;
601 l_end_position NUMBER DEFAULT 1;
602 l_org_length NUMBER;
603 l_report_type VARCHAR2(10); -- Moved default value initialization to pl/sql block for bug fix 3697471
604
605 -- Introduced the following for bug fix 3697471
606 CURSOR sched_lines(schedule_hierarchy_id NUMBER) IS
607 SELECT schedule_line_id l_id,
608 schedule_begin_date sbd,
609 schedule_end_date sed,
610 schedule_percent sp
611 FROM psp_schedule_lines
612 WHERE schedule_hierarchy_id = schedule_hierarchy_id
613 AND schedule_end_date >= p_period_from
614 AND schedule_begin_date <= p_period_to;
615
616 CURSOR dates(p_schedule_hierarchy_id NUMBER) IS
617 SELECT schedule_begin_date dat , 'B'
618 FROM psp_schedule_lines
619 WHERE schedule_hierarchy_id = p_schedule_hierarchy_id
620 AND schedule_end_date >= p_period_from
621 AND schedule_begin_date <= p_period_to
622 UNION
623 SELECT schedule_end_date dat , 'E'
624 FROM psp_schedule_lines
625 WHERE schedule_hierarchy_id = p_schedule_hierarchy_id
626 AND schedule_end_date >= p_period_from
627 AND schedule_begin_date <= p_period_to
628 ORDER BY 1, 2 ;
629
630 k BINARY_INTEGER;
631 -- End of changes for bug fix 3697471
632
633 /* Changes for bug 2863953 */
634
635 Cursor c_all_org is
636 SELECT distinct paf.organization_id
637 FROM psp_schedule_hierarchy psh,
638 psp_schedule_lines psl,
639 per_assignments_f paf
640 WHERE psh.schedule_hierarchy_id = psl.schedule_hierarchy_id
641 AND paf.assignment_id = psh.assignment_id
642 AND psl.schedule_begin_date <= p_period_to
643 AND psl.schedule_end_date >= p_period_from
644 AND psl.business_group_id = p_business_group_id
645 AND psl.set_of_books_id = p_set_of_books_id;
646
647
648 /* End of changes for bug 2863953 */
649 BEGIN
650 l_report_type := 'Regular'; -- Introduced for bug fix 3697471 to fix GSCC Warning file.Sql.35
651
652 -- Initialize the fnd message routine
653 -- errbuf := 'Message Initialization failed';
654 fnd_msg_pub.initialize;
655
656 -- set the run id for this run
657 -- errbuf := 'Run ID Initialization failed';
658 psp_matrix_driver_pkg.set_runid;
659
660 -- Introduced the if condition for bug 2863953
661 If p_organization_id is null then
662
663 FOR org_rec in c_all_org Loop
664 OPEN sch_hier_cur(org_rec.organization_id);
665 LOOP -- Looping for all schedule hierarchies
666 FETCH sch_hier_cur INTO sch_hier_rec;
667 IF (sch_hier_cur%NOTFOUND) THEN
668 EXIT;
669 END IF;
670 -- call the matrix driver procedure to load the respective schedule lines
671 -- errbuf := 'Loading Matrix Driver for Schedule Hierarchy: ' ||
672 -- sch_hier_rec.schedule_hierarchy_id || ' had Failed';
673
674
675 k := 1;
676
677 OPEN dates(sch_hier_rec.schedule_hierarchy_id);
678 FETCH dates BULK COLLECT INTO dat, type_dat;
679 CLOSE dates;
680
681 FOR rowno IN 1..(dat.COUNT - 1) LOOP
682 IF (type_dat(rowno) = 'B' AND type_dat(rowno+1) = 'B') THEN
683 schedule_chunk.schedule_begin_date(k) := dat(rowno);
684 schedule_chunk.schedule_end_date(k) := dat(rowno+1) - 1;
685 k := k+1;
686 ELSIF (type_dat(rowno) = 'B' AND type_dat(rowno+1) = 'E') THEN
687 schedule_chunk.schedule_begin_date(k) := dat(rowno);
688 schedule_chunk.schedule_end_date(k) := dat(rowno+1);
689 k := k+1;
690 ELSIF (type_dat(rowno) = 'E' AND type_dat(rowno+1) = 'E') THEN
691 schedule_chunk.schedule_begin_date(k) := dat(rowno) + 1;
692 schedule_chunk.schedule_end_date(k) := dat(rowno+1);
693 k := k+1;
694 END IF;
695 END LOOP;
696
697 FORALL rowno IN 1..schedule_chunk.schedule_begin_date.COUNT
698 INSERT INTO psp_matrix_driver
699 (RUN_ID, SCHEDULE_LINE_ID,
700 PERIOD_START_DATE,
701 PERIOD_END_DATE,
702 PERIOD_SCHEDULE_PERCENT)
703 SELECT global_run_id, schedule_line_id,
704 GREATEST(p_period_from, schedule_chunk.schedule_begin_date(rowno)),
705 LEAST(p_period_to, schedule_chunk.schedule_end_date(rowno)),
706 schedule_percent
707 FROM psp_schedule_lines psl
708 WHERE schedule_hierarchy_id = sch_hier_rec.schedule_hierarchy_id
709 AND psl.schedule_begin_date <= p_period_to
710 AND psl.schedule_end_date >= p_period_from
711 AND psl.schedule_begin_date <= schedule_chunk.schedule_end_date(rowno)
712 AND psl.schedule_end_date >= schedule_chunk.schedule_begin_date(rowno);
713
714 dat.delete;
715 type_dat.delete;
716 schedule_chunk.schedule_end_date.delete;
717 schedule_chunk.schedule_begin_date.delete;
718
719 END LOOP; -- End Schedule Hierarchies
720 CLOSE sch_hier_cur;
721
722
723 END LOOP; -- End loop for for c_all_org cursor
724
725
726 ELSE
727
728 l_org_length := LENGTH(p_organization_id);
729
730 -- Split the selected organizations into individual Organizations and retrieve Schedule Hierarchies for them
731 LOOP -- Loop for splitting Organization ID
732
733 l_end_position := INSTR(p_organization_id, ',', l_start_position);
734
735 IF (l_end_position = 0) THEN
736 l_end_position := l_org_length + 1;
737 END IF;
738
739 -- errbuf := 'Retrieving Organizations failed';
740 l_organization_id := TO_NUMBER(SUBSTR(p_organization_id, l_start_position,
741 (l_end_position - l_start_position)));
742
743 -- errbuf := 'Retrieving Schedule Hierarchies failed for Organization: ' || TO_CHAR(l_organization_id);
744 OPEN sch_hier_cur(l_organization_id);
745 LOOP -- Looping for all schedule hierarchies
746 FETCH sch_hier_cur INTO sch_hier_rec;
747 IF (sch_hier_cur%NOTFOUND) THEN
748 EXIT;
749 END IF;
750 -- call the matrix driver procedure to load the respective schedule lines
751 -- errbuf := 'Loading Matrix Driver for Schedule Hierarchy: ' ||
752 -- sch_hier_rec.schedule_hierarchy_id || ' had Failed';
753
754 -- psp_matrix_driver_pkg.load_table(sch_hier_rec.schedule_hierarchy_id); Commented for bug fix 3697471
755 -- psp_matrix_driver_pkg.purge_table; Commented for bug fix 3697471
756
757
758 -- Introduced the following for bug fix 3697471
759 k := 1;
760
761 OPEN dates(sch_hier_rec.schedule_hierarchy_id);
762 FETCH dates BULK COLLECT INTO dat, type_dat;
763 CLOSE dates;
764
765 FOR rowno IN 1..(dat.COUNT - 1) LOOP
766 IF (type_dat(rowno) = 'B' AND type_dat(rowno+1) = 'B') THEN
767 schedule_chunk.schedule_begin_date(k) := dat(rowno);
768 schedule_chunk.schedule_end_date(k) := dat(rowno+1) - 1;
769 k := k+1;
770 ELSIF (type_dat(rowno) = 'B' AND type_dat(rowno+1) = 'E') THEN
771 schedule_chunk.schedule_begin_date(k) := dat(rowno);
772 schedule_chunk.schedule_end_date(k) := dat(rowno+1);
773 k := k+1;
774 ELSIF (type_dat(rowno) = 'E' AND type_dat(rowno+1) = 'E') THEN
775 schedule_chunk.schedule_begin_date(k) := dat(rowno) + 1;
776 schedule_chunk.schedule_end_date(k) := dat(rowno+1);
777 k := k+1;
778 END IF;
779 END LOOP;
780
781
782 FORALL rowno IN 1..schedule_chunk.schedule_begin_date.COUNT
783 INSERT INTO psp_matrix_driver
784 (RUN_ID, SCHEDULE_LINE_ID,
785 PERIOD_START_DATE,
786 PERIOD_END_DATE,
787 PERIOD_SCHEDULE_PERCENT)
788 SELECT global_run_id, schedule_line_id,
789 GREATEST(p_period_from, schedule_chunk.schedule_begin_date(rowno)),
790 LEAST(p_period_to, schedule_chunk.schedule_end_date(rowno)),
791 schedule_percent
792 FROM psp_schedule_lines psl
793 WHERE schedule_hierarchy_id = sch_hier_rec.schedule_hierarchy_id
794 AND psl.schedule_begin_date <= p_period_to
795 AND psl.schedule_end_date >= p_period_from
796 AND psl.schedule_begin_date <= schedule_chunk.schedule_end_date(rowno)
797 AND psl.schedule_end_date >= schedule_chunk.schedule_begin_date(rowno);
798
799 dat.delete;
800 type_dat.delete;
801 schedule_chunk.schedule_end_date.delete;
802 schedule_chunk.schedule_begin_date.delete;
803 -- End of changes for bug fix 3697471
804
805 END LOOP; -- End Schedule Hierarchies
806 CLOSE sch_hier_cur;
807
808 IF (l_end_position > l_org_length) THEN
809 EXIT;
810 END IF;
811
812 l_start_position := l_end_position + 1;
813 END LOOP; -- End Organization Id split
814
815 End if ; /* Introduced for bug 2863953 */
816
817 /***** Commented for bug fix 3697471
818 -- Update the psp_matrix_driver table for the current run, deleting zero schedule percent records
819 -- errbuf := 'Deleting zero schedule percent records failed';
820 DELETE psp_matrix_driver pmd
821 WHERE run_id = global_run_id
822 AND period_schedule_percent = 0;
823
824 / * Commented for bug fix 2368498
825 UPDATE psp_matrix_driver pmd
826 SET period_start_date = period_start_date + 1
827 WHERE run_id = global_run_id
828 AND EXISTS (SELECT 1
829 FROM psp_schedule_lines psl,
830 psp_schedule_lines psl2
831 WHERE psl.schedule_line_id = pmd.schedule_line_id
832 AND psl2.schedule_hierarchy_id = psl.schedule_hierarchy_id
833 AND psl2.schedule_end_date = pmd.period_start_date
834 AND psl2.schedule_line_id <> psl.schedule_line_id);
835
836 UPDATE psp_matrix_driver pmd
837 SET period_end_date = period_end_date - 1
838 WHERE run_id = global_run_id
839 AND EXISTS (SELECT 1
840 FROM psp_schedule_lines psl,
841 psp_schedule_lines psl2
842 WHERE psl.schedule_line_id = pmd.schedule_line_id
843 AND psl2.schedule_hierarchy_id = psl.schedule_hierarchy_id
844 AND psl2.schedule_begin_date = pmd.period_end_date
845 AND psl2.schedule_line_id <> psl.schedule_line_id);
846 End of comment for bug 2368498 * /
847
848 -- Introduced for bug fix 2368498
849 -- Updating the period_start_date for periods in between schedule begin and end dates
850 -- errbuf := 'Period End Date Update of Matrix Driver failed';
851 UPDATE psp_matrix_driver pmd
852 SET period_end_date = period_end_date - 1
853 WHERE run_id = global_run_id
854 AND period_start_date < period_end_date
855 AND period_start_date = (SELECT MIN(psl1.schedule_begin_date)
856 FROM psp_schedule_lines psl1
857 WHERE psl1.schedule_hierarchy_id = (SELECT psl2.schedule_hierarchy_id
858 FROM psp_schedule_lines psl2
859 WHERE psl2.schedule_line_id = pmd.schedule_line_id))
860 AND EXISTS (SELECT 1
861 FROM psp_schedule_lines psl1
862 WHERE psl1.schedule_line_id <> pmd.schedule_line_id
863 AND psl1.schedule_begin_date = pmd.period_end_date
864 AND psl1.schedule_hierarchy_id = (SELECT psl2.schedule_hierarchy_id
865 FROM psp_schedule_lines psl2
866 WHERE psl2.schedule_line_id = pmd.schedule_line_id));
867
868 UPDATE psp_matrix_driver pmd
869 SET period_end_date = period_end_date - 1
870 WHERE run_id = global_run_id
871 AND period_start_date < period_end_date
872 AND NOT (NOT EXISTS (SELECT 1
873 FROM psp_schedule_lines psl1
874 WHERE psl1.schedule_line_id <> pmd.schedule_line_id
875 AND psl1.schedule_begin_date = pmd.period_end_date
876 AND psl1.schedule_hierarchy_id = (SELECT psl2.schedule_hierarchy_id
877 FROM psp_schedule_lines psl2
878 where psl2.schedule_line_id = pmd.schedule_line_id))
879 AND EXISTS (SELECT 1
880 FROM psp_schedule_lines psl1
881 WHERE psl1.schedule_end_date = pmd.period_end_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 period_start_date <> (SELECT MIN(psl1.schedule_begin_date)
886 FROM psp_schedule_lines psl1
887 WHERE psl1.schedule_hierarchy_id = (SELECT psl2.schedule_hierarchy_id
888 FROM psp_schedule_lines psl2
889 WHERE psl2.schedule_line_id = pmd.schedule_line_id));
890
891 -- Updating the period_start_date for periods in between schedule begin and end dates
892 -- errbuf := 'Period Start Date Update of Matrix Driver failed';
893 UPDATE psp_matrix_driver pmd
894 SET period_start_date = period_start_date + 1
895 WHERE run_id = global_run_id
896 AND period_start_date < period_end_date
897 AND NOT EXISTS (SELECT 1
898 FROM psp_schedule_lines psl1
899 WHERE psl1.schedule_begin_date = pmd.period_start_date
900 AND psl1.schedule_hierarchy_id = (SELECT psl2.schedule_hierarchy_id
901 FROM psp_schedule_lines psl2
902 WHERE psl2.schedule_line_id = pmd.schedule_line_id))
903 AND period_start_date <> (SELECT MIN(psl1.schedule_begin_date)
904 FROM psp_schedule_lines psl1
905 WHERE psl1.schedule_hierarchy_id = (SELECT psl2.schedule_hierarchy_id
906 FROM psp_schedule_lines psl2
907 WHERE psl2.schedule_line_id = pmd.schedule_line_id));
908
909 UPDATE psp_matrix_driver pmd
910 SET period_start_date = period_start_date + 1
911 WHERE run_id = global_run_id
912 AND period_start_date < period_end_date
913 AND EXISTS (SELECT 1
914 FROM psp_schedule_lines psl1
915 WHERE psl1.schedule_begin_date = pmd.period_start_date
916 AND psl1.schedule_hierarchy_id = (SELECT psl2.schedule_hierarchy_id
917 FROM psp_schedule_lines psl2
918 WHERE psl2.schedule_line_id = pmd.schedule_line_id))
919 AND EXISTS (SELECT 1
920 FROM psp_schedule_lines psl1
921 WHERE psl1.schedule_line_id <> pmd.schedule_line_id
922 AND psl1.schedule_end_date = pmd.period_start_date
923 AND psl1.schedule_hierarchy_id = (SELECT psl2.schedule_hierarchy_id
924 FROM psp_schedule_lines psl2
925 WHERE psl2.schedule_line_id = pmd.schedule_line_id))
926 AND period_start_date <> (SELECT MIN(psl1.schedule_begin_date)
927 FROM psp_schedule_lines psl1
928 WHERE psl1.schedule_hierarchy_id = (SELECT psl2.schedule_hierarchy_id
929 FROM psp_schedule_lines psl2
930 WHERE psl2.schedule_line_id = pmd.schedule_line_id));
931 -- End of Bug fix 2368498
932 End of changes for bug fix 3697471 *****/
933
934 -- For Exception Report delete records in psp_matrix_driver for which the schedule percentage is 100
935 IF (p_report_type = 'E') THEN
936 -- errbuf := 'Deleting schedules equal to 100 percent failed';
937 l_report_type := 'Exception';
938 DELETE psp_matrix_driver pmd
939 WHERE run_id = global_run_id
940 AND EXISTS (SELECT 1
941 FROM psp_schedule_lines psl,
942 psp_schedule_lines psl2
943 WHERE psl2.schedule_hierarchy_id = psl.schedule_hierarchy_id
944 AND psl.schedule_line_id = pmd.schedule_line_id
945 AND psl2.schedule_begin_date <= pmd.period_end_date
946 AND psl2.schedule_end_date >= pmd.period_start_date
947 GROUP BY psl2.schedule_hierarchy_id
948 HAVING SUM(psl2.schedule_percent) = 100);
949 END IF;
950
951 /***** Commented for bug fix 3697471
952 -- Update the period_end_date to p_period_to if period_end_date > p_period_to
953 UPDATE psp_matrix_driver
954 SET period_end_date = p_period_to
955 WHERE run_id = global_run_id
956 AND period_end_date > p_period_to;
957
958 UPDATE psp_matrix_driver
959 SET period_start_date = p_period_from
960 WHERE run_id = global_run_id
961 AND period_start_date < p_period_from;
962 End of comment for bug fix 3697471 *****/
963
964 retcode := 0;
965
966 EXCEPTION
967 WHEN OTHERS THEN
968 -- psp_message_s.print_error( p_mode => FND_FILE.LOG,
969 -- p_print_header => FND_API.G_FALSE);
970 retcode := 2;
971 END load_organizations;
972
973
974
975 /**********************************************************
976 Created By : lveerubh
977
978 Date Created By : 05-SEP-2001
979
980 Purpose : This procedure does the following jobs :
981 X=10:Sets the Run id for the psp_matrix_table
982 X=20:For each organization chosen by the user (p_list_organization_id)
983 it loads the table psp_matrix_Driver by calling procedure
984 load_table_schedule
985 X=30:Updates the period_start_date of the schedule_lines in matrix_driver table
986 ,for each organization,except the schedule lines with minimum period_start_date
987 so that data in the table becomes as required for the report PSPLSODR.rdf
988 X=40:Deletes the schedule lines with zero schedule percent for that run_id.
989 X=50:Checks if the user has asked for an Exception report. If yes, then it deletes
990 for a particular organization and particular period_start_date,period_end_date ,
991 all those schedule lines whose sum of period_schedule_percent equals 100
992
993
994 Know limitations, enhancements or remarks
995
996 Change History
997
998 Who When What
999 Lveerubh 05-SEP-2001 Created the procedure
1000 Lveerubh 03-OCT-2001 1. Removing the call for the report from the package -Bug 2022193
1001
1002 ***************************************************************/
1003 procedure load_org_schedule(p_return_status OUT NOCOPY NUMBER,
1004 p_log_message OUT NOCOPY VARCHAR2,
1005 p_list_organization_id IN VARCHAR2,
1006 p_period_from IN VARCHAR2,
1007 p_period_to IN VARCHAR2,
1008 p_report_type IN VARCHAR2,
1009 p_business_group_id IN NUMBER,
1010 p_set_of_books_id IN NUMBER
1011 )
1012 IS
1013
1014
1015 CURSOR c_schedule_percent(F_run_id NUMBER)
1016 IS
1017 SELECT pdls.organization_id,
1018 pmd.period_start_date,
1019 pmd.period_end_date
1020 FROM psp_default_labor_schedules pdls,
1021 psp_matrix_driver pmd
1022 WHERE pdls.org_schedule_id = pmd.schedule_line_id
1023 AND pmd.run_id = F_run_id
1024 GROUP BY pdls.organization_id,
1025 pmd.period_start_date,
1026 pmd.period_end_date
1027 HAVING SUM(pmd.period_schedule_percent) = 100;
1028
1029
1030 CURSOR c_schedule_line_id( F_organization_id NUMBER,
1031 F_period_start_date DATE,
1032 F_period_end_date DATE,
1033 F_run_id NUMBER)
1034 IS
1035 SELECT pdls.org_schedule_id
1036 FROM psp_default_labor_schedules pdls,
1037 psp_matrix_driver pmd
1038 WHERE pdls.organization_id = F_organization_id
1039 AND pdls.schedule_begin_date <= F_period_end_date
1040 AND pdls.schedule_end_date >= F_period_start_date
1041 AND pmd.schedule_line_id = pdls.org_Schedule_id
1042 AND pmd.run_id = F_run_id;
1043
1044
1045 CURSOR c_period_start_date(F_run_id NUMBER)
1046 IS
1047 SELECT min(pmd1.period_start_date) period_start_date,
1048 pdls.organization_id
1049 FROM psp_matrix_driver pmd1,
1050 psp_default_labor_schedules pdls
1051 WHERE pmd1.run_id = F_run_id
1052 AND pmd1.schedule_line_id = pdls.org_schedule_id
1053 GROUP BY pdls.organization_id;
1054
1055 rec_sch_percent c_schedule_percent%ROWTYPE;
1056 rec_sch_line_id c_schedule_line_id%ROWTYPE;
1057 rec_period_start_date c_period_start_date%ROWTYPE;
1058
1059 l_req_id NUMBER;
1060 l_call_status BOOLEAN;
1061 l_rphase VARCHAR2(30);
1062 l_rstatus VARCHAR2(30);
1063 l_dphase VARCHAR2(30);
1064 l_dstatus VARCHAR2(30);
1065 l_message VARCHAR2(240);
1066 l_run_id NUMBER;
1067 l_period_start_date DATE;
1068 l_end_position NUMBER DEFAULT 1;
1069 l_begin_position NUMBER DEFAULT 1;
1070 l_new_position NUMBER ;
1071 l_org_id VARCHAR2(10);
1072 l_org_length NUMBER;
1073 l_report_type VARCHAR2(10); -- Moved default value initialization to pl/sql block for bug fix 3697471
1074
1075
1076 CURSOR c_all_org
1077 IS
1078 SELECT distinct organization_id
1079 FROM psp_default_labor_schedules
1080 WHERE business_group_id = p_business_group_id
1081 AND set_of_books_id = p_set_of_books_id;
1082
1083
1084 BEGIN
1085 l_report_type := 'Regular'; -- Introduced for bug fix 3697471 to fix GSCC Warning file.Sql.35
1086 -- Initialize the fnd message routine
1087 fnd_msg_pub.initialize;
1088
1089 --set the Run Id for the current request
1090 --dbms_output.put_line('Organization List '||p_list_organization_id);
1091
1092 --X=10
1093 psp_matrix_driver_pkg.set_runid;
1094 l_run_id := psp_matrix_driver_pkg.get_run_id;
1095 --dbms_output.put_line(l_run_id);
1096 --errbuf := 'Run id '||global_run_id;
1097 psp_matrix_driver_pkg.purge_table;
1098
1099 --X=20
1100
1101 If p_list_organization_id is null then
1102
1103 For l_org_rec in c_all_org
1104 loop
1105 psp_matrix_driver_pkg.load_table_schedule(to_number(l_org_rec.organization_id)
1106 ,p_business_group_id,p_set_of_books_id);
1107 psp_matrix_driver_pkg.purge_table;
1108 end loop;
1109
1110
1111 else
1112
1113 l_org_length := length(p_list_organization_id) ;
1114 LOOP -- Loop for splitting Organization ID
1115 l_end_position := INSTR(p_list_organization_id, ',', l_begin_position);
1116 IF (l_end_position = 0) THEN
1117 l_end_position := l_org_length + 1;
1118 END IF;
1119 l_org_id := TO_NUMBER(SUBSTR(p_list_organization_id, l_begin_position,(l_end_position - l_begin_position)));
1120 psp_matrix_driver_pkg.load_table_schedule(to_number(l_org_id),p_business_group_id,p_set_of_books_id);
1121 psp_matrix_driver_pkg.purge_table;
1122 IF (l_end_position > l_org_length) THEN
1123 EXIT;
1124 END IF;
1125 l_begin_position := l_end_position + 1;
1126 END LOOP; -- End Organization Id split
1127
1128 end if;
1129 --X=30
1130 --Updating the records created in psp_matrix_driver in the manner required to be displayed in the report
1131 UPDATE psp_matrix_driver pmd
1132 SET period_start_date = period_start_date + 1
1133 WHERE run_id =l_run_id
1134 AND EXISTS (SELECT 1
1135 FROM psp_default_labor_schedules pdls1,
1136 psp_default_labor_schedules pdls2
1137 WHERE pdls1.org_schedule_id=pmd.schedule_line_id
1138 AND pdls1.organization_id=pdls2.organization_id
1139 AND pdls1.org_schedule_id<>pdls2.org_schedule_id
1140 AND pdls2.schedule_end_date=pmd.period_start_date);
1141
1142 UPDATE psp_matrix_driver pmd
1143 SET period_end_date = period_end_date - 1
1144 WHERE run_id =l_run_id
1145 AND EXISTS (SELECT pmd.period_end_date
1146 FROM psp_default_labor_schedules pdls1,
1147 psp_default_labor_schedules pdls2
1148 WHERE pdls1.org_schedule_id=pmd.schedule_line_id
1149 AND pdls2.organization_id=pdls1.organization_id
1150 AND pdls2.schedule_begin_date=pmd.period_end_date
1151 AND pdls1.org_schedule_id<>pdls2.org_schedule_id);
1152
1153 --X=40
1154 -- Delete the Zero Schedule Percent
1155 DELETE psp_matrix_driver
1156 WHERE run_id = global_run_id
1157 AND period_schedule_percent = 0;
1158
1159 --X=50
1160 ---Deleting Schedule Percent equal to 100
1161 IF p_report_type = 'E' THEN
1162 -- l_report_type:='Exception';
1163 OPEN c_schedule_percent(l_run_id);
1164 LOOP
1165 FETCH c_schedule_percent INTO rec_sch_percent ;
1166 EXIT WHEN c_schedule_percent%NOTFOUND;
1167
1168 OPEN c_schedule_line_id(rec_sch_percent.organization_id,
1169 rec_sch_percent.period_start_date,
1170 rec_sch_percent.period_end_date,
1171 l_run_id);
1172 LOOP
1173 FETCH c_schedule_line_id INTO rec_sch_line_id;
1174 EXIT WHEN c_schedule_line_id%NOTFOUND;
1175
1176 DELETE psp_matrix_driver pmd
1177 WHERE pmd.run_id = l_run_id
1178 And pmd.schedule_line_id = rec_sch_line_id.org_schedule_id
1179 And pmd.period_start_date = rec_sch_percent.period_start_date
1180 And pmd.period_end_date = rec_sch_percent.period_end_date;
1181
1182 END LOOP; --End of c_schedule_line_id
1183 CLOSE c_schedule_line_id;
1184 END LOOP; --End of c_schedule_percent
1185 CLOSE c_schedule_percent;
1186 END IF;
1187
1188 --Updating the records greater than p_period_to to NUll ,so that in Schedule Summary in report these will be
1189 --displayed with end date as p_period_to
1190 UPDATE psp_matrix_driver
1191 SET period_end_date = p_period_to
1192 WHERE run_id = l_run_id
1193 AND period_end_date > p_period_to;
1194
1195 UPDATE psp_matrix_driver
1196 SET period_start_date = p_period_from
1197 WHERE run_id = l_run_id
1198 AND period_start_date < p_period_from;
1199
1200 p_return_status := 0;
1201 p_log_message := 'Success';
1202
1203 EXCEPTION
1204 WHEN OTHERS THEN
1205 p_log_message := sqlerrm;
1206 p_return_status :=2;
1207 END load_org_schedule;
1208
1209 /**********************************************************
1210 Created By : lveerubh
1211
1212 Date Created By : 05-SEP-2001
1213
1214 Purpose : This procedure is introduced to populate psp_matrix_driver with the
1215 data from psp_default_labor_schedules.It sorts the pool of begin and end dates
1216 specified on the various schedule lines. After the sort it inserts the dates into
1217 pl/sql table thereby forming distinct periods of consistent charging instructions.
1218 Once the pl/sql table is loaded, the start and end dates of the distinct periods are
1219 inserted into the temp table.As the cursor parses through each record a correponding
1220 schedule percentage is inserted according to the overlap of these distinct periods with
1221 the periods specified on each schedule line
1222 Know limitations, enhancements or remarks
1223
1224 Change History
1225
1226 Who When What
1227 Lveerubh 05-SEP-2001 Created the procedure
1228
1229 ***************************************************************/
1230
1231
1232 PROCEDURE load_table_schedule(sch_id NUMBER,
1233 p_business_group_id NUMBER,
1234 p_set_of_books_id NUMBER)
1235 IS
1236 CURSOR sched_lines(s_id NUMBER)
1237 IS
1238 SELECT org_schedule_id l_id,
1239 schedule_begin_date sbd,
1240 schedule_end_date sed,
1241 schedule_percent sp
1242 FROM psp_default_labor_schedules
1243 WHERE organization_id = s_id
1244 AND business_group_id = p_business_group_id
1245 AND set_of_books_id = p_set_of_books_id;
1246
1247 --Get the dates (schedule begin dates and end dates) in ascending order of dates. If a date is present in
1248 --begin as well as the end date, bring the End Date first.
1249 CURSOR dates(s_id NUMBER) IS
1250 SELECT schedule_begin_date dat , 'B'
1251 FROM psp_default_labor_schedules
1252 WHERE organization_id = s_id
1253 AND business_group_id = p_business_group_id
1254 AND set_of_books_id = p_set_of_books_id
1255 UNION
1256 SELECT schedule_end_date dat , 'E'
1257 FROM psp_default_labor_schedules
1258 WHERE organization_id = s_id
1259 AND business_group_id = p_business_group_id
1260 AND set_of_books_id = p_set_of_books_id
1261 ORDER BY 1, 2 ;
1262 i BINARY_INTEGER :=0;
1263 j BINARY_INTEGER :=0;
1264
1265 sch_rec sched_lines%ROWTYPE;
1266 per number;
1267 temp date;
1268 num number;
1269 dummy char(1);
1270 v_count1 number;
1271 v_count2 number;
1272 l_count3 number;
1273 BEGIN
1274 --From the cursor, get the dates in pl/sql table dat1.
1275 OPEN dates(sch_id);
1276 LOOP
1277 i := i+1;
1278 FETCH dates INTO temp, dummy;
1279 EXIT WHEN dates%NOTFOUND;
1280 -- dbms_output.put_line('date is:'||to_char(temp)||' the organization_id '||sch_id);
1281 dat1(i) := temp;
1282 type_dat(i) := dummy;
1283 END LOOP;
1284 CLOSE dates;
1285 --
1286 --Copy dates from table 'dat1' to table 'dat'. If there is a Begin Date which is exactly 1 day greater
1287 --than the End Date, DO NOT include this Begin Date in table 'dat'.
1288 i := 1;
1289 dat(i) := dat1(i);
1290 j := 2;
1291 FOR i IN 2..dat1.COUNT LOOP
1292
1293 IF dat1(i) = dat1(i-1) + 1 THEN
1294 IF type_dat(i) = 'B' and type_dat(i-1) = 'E' THEN
1295 null;
1296 ELSE
1297 dat(j) := dat1(i);
1298 j := j + 1;
1299 END IF;
1300 ELSE
1301 dat(j) := dat1(i);
1302 j := j + 1;
1303 END IF;
1304
1305 END LOOP;
1306
1307 --
1308 --Insert records in temporary table PSP_MATRIX_DRIVER. There may be some dates in 'dat1' which were not
1309 --included in 'dat' because Begin Date was exactly 1 day greater than the End Date. In such a case,
1310 --instead of comparing dates of 'dat' with those of psp_schedule_lines, compare (Begin Date+1) and End
1311 --Date of 'dat' with those of psp_schedule_lines.
1312 --
1313 OPEN sched_lines(sch_id);
1314 LOOP
1315 FETCH sched_lines INTO sch_rec;
1316 EXIT WHEN sched_lines%NOTFOUND;
1317 num:=dat.COUNT-1;
1318 FOR i in 1 .. num LOOP
1319
1320 IF dat(i+1) = dat1(i+1) THEN
1321
1322 IF ((dat(i) between sch_rec.sbd and sch_rec.sed) AND (dat(i+1) between sch_rec.sbd and sch_rec.sed)) THEN
1323 per:= sch_rec.sp;
1324
1325 ELSE
1326 per:=0;
1327
1328 END IF;
1329
1330 ELSE
1331 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
1332 per:= sch_rec.sp;
1333
1334 ELSE
1335 per:=0;
1336
1337 END IF;
1338
1339 END IF;
1340
1341 INSERT INTO psp_matrix_driver(RUN_ID,
1342 SCHEDULE_LINE_ID,
1343 PERIOD_START_DATE,
1344 PERIOD_END_DATE,
1345 PERIOD_SCHEDULE_PERCENT) values
1346 (global_run_id,
1347 sch_rec.l_id,
1348 dat(i),
1349 dat(i+1),
1350 per);
1351 END LOOP;
1352 END LOOP;
1353 EXCEPTION
1354 WHEN NO_DATA_FOUND THEN
1355 null;
1356 WHEN OTHERS THEN
1357 null;
1358 END load_table_schedule;
1359
1360
1361 /*****************************************************************************************
1362 Procedure name: check_sch_hierarchy
1363 Purpose : This Procedure checks the hierarchy for the current asignment and prevents
1364 that user from committing any new changes when there are any existing / new errors.
1365 Description: this procedure checks for schedules exceeding 100% issue for al the hierarchies
1366 and for the respective assignment.It takes assignment_id and payroll_id as parameters to
1367 validate the schedule lines that are valid after the last imported payroll_date.
1368 Creation date :14-APR-2003
1369 *****************************************************************************************/
1370
1371 PROCEDURE check_sch_hierarchy(p_assignment_id IN NUMBER,
1372 p_payroll_id IN NUMBER,
1373 p_hierarchy_id OUT NOCOPY NUMBER,
1374 p_invalid_count OUT NOCOPY NUMBER) IS
1375
1376
1377 CURSOR sch_hier_cur IS
1378 SELECT schedule_hierarchy_id
1379 FROM psp_schedule_hierarchy
1380 WHERE assignment_id = p_assignment_id;
1381
1382 l_schedule_hierarchy_id NUMBER ( 15 );
1383 l_invalid_count NUMBER DEFAULT 0;
1384
1385 BEGIN
1386 OPEN sch_hier_cur;
1387 LOOP
1388 FETCH sch_hier_cur INTO l_schedule_hierarchy_id;
1389 EXIT WHEN sch_hier_cur%NOTFOUND;
1390 psp_matrix_driver_pkg.clear_table('REFRESH');
1391 psp_matrix_driver_pkg.purge_table;
1392 psp_matrix_driver_pkg.load_table(l_schedule_hierarchy_id);
1393 IF (NOT psp_matrix_driver_pkg.check_exceedence(p_assignment_id)) THEN
1394 l_invalid_count := l_invalid_count + 1;
1395 p_hierarchy_id :=l_schedule_hierarchy_id;
1396 END IF;
1397 END LOOP;
1398 CLOSE sch_hier_cur;
1399 psp_matrix_driver_pkg.clear_table('REFRESH');
1400 psp_matrix_driver_pkg.purge_table;
1401 p_invalid_count := l_invalid_count;
1402 END check_sch_hierarchy;
1403
1404 /* End of code for Bug no 2836176 By tbalacha*/
1405
1406
1407 end psp_matrix_driver_pkg;
[Home] [Help]