1 package body psp_matrix_driver2_pkg as
2 --$Header: PSPLSM2B.pls 115.8 2002/09/25 02:15:48 vdharmap ship $
3
4 start_period BINARY_INTEGER:=1;
5 TYPE mat_tab IS TABLE OF DATE
6 INDEX BY BINARY_INTEGER;
7 dat mat_tab;
8
9 procedure set_start_period(n NUMBER) is
10 BEGIN
11 start_period:=n;
12 END set_start_period;
13
14 FUNCTION get_max_periods RETURN NUMBER is
15 BEGIN
16 RETURN dat.COUNT;
17 END;
18
19 FUNCTION get_dynamic_prompt(n NUMBER) RETURN VARCHAR2 IS
20 prompt varchar2(30);
21 BEGIN
22 --if (start_period+n > get_max_periods) then ** commented and intro following line for 2365076
23 if (start_period+n < 1) then
24 return null;
25 else
26 prompt:=to_char(dat(start_period+n),'MON-YY');
27 RETURN prompt;
28 end if;
29 END;
30
31 FUNCTION get_start_period(n NUMBER) RETURN DATE is
32 i BINARY_INTEGER;
33 BEGIN
34 i:=start_period+n;
35 /*if (i > get_max_periods) then
36 RETURN null;
37 else*/
38 RETURN dat(i);
39 --end if;
40 END;
41
42 /* the function returns the total scheduled percentage of each distinct period in the temp table*/
43
44 FUNCTION get_dynamic_totals(n NUMBER) RETURN NUMBER IS
45 total NUMBER;
46 st_date DATE;
47 BEGIN
48 st_date:= dat(start_period+n);
49 --if (start_period+n > get_max_periods) then -- replaced this condn with below cond for 2365076
50 if (start_period+n < 1) then
51 return null;
52 else
53 select sum(PERIOD_SCHEDULE_PERCENT)
54 into total
55 from psp_matrix_driver2
56 where period = st_date;
57 return total;
58 end if;
59 END;
60
61 procedure purge_table is
62 begin
63 dat.DELETE;
64 end purge_table;
65
66
67 procedure load_table(sch_id number,begin_date date,end_date date) is
68 CURSOR sched_lines(s_id NUMBER) IS
69 SELECT schedule_line_id l_id,
70 schedule_begin_date sbd,
71 schedule_end_date sed,
72 schedule_percent sp
73 FROM psp_schedule_lines
74 WHERE schedule_hierarchy_id = s_id;
75
76 i BINARY_INTEGER :=0;
77
78 sch_rec sched_lines%ROWTYPE;
79 per number;
80 st_date date;
81 en_date date;
82 num number:=0;
83 BEGIN
84 st_date:=trunc(begin_date,'MONTH');
85 en_date:=trunc(end_date,'MONTH');
86 LOOP
87 i:=i+1;
88 dat(i):=add_months(st_date,num);
89 EXIT WHEN (dat(i)>=en_date);
90 num:=num+1;
91 --dbms_output.put_line('date is:'||to_char(temp));
92 END LOOP;
93 --dbms_output.put_line('reached here');
94 OPEN sched_lines(sch_id);
95 LOOP
96 FETCH sched_lines INTO sch_rec;
97 EXIT WHEN sched_lines%NOTFOUND;
98 num:=dat.COUNT;
99 FOR i in 1 .. num LOOP
100 if ((sch_rec.sbd between dat(i) and last_day(dat(i))AND(sch_rec.sed>=last_day(dat(i)))))then
101 per:= sch_rec.sp*psp_general.business_days(sch_rec.sbd,last_day(dat(i)))/psp_general.business_days(dat(i),last_day(dat(i)));
102 elsif ((dat(i) between sch_rec.sbd and sch_rec.sed)AND(last_day(dat(i))>=sch_rec.sed))then
103 per:= sch_rec.sp*psp_general.business_days(dat(i),sch_rec.sed)/psp_general.business_days(dat(i),last_day(dat(i)));
104 elsif((sch_rec.sbd between dat(i) and last_day(dat(i)))and(sch_rec.sed<=last_day(dat(i))))then
105 per:= sch_rec.sp*psp_general.business_days(sch_rec.sbd,sch_rec.sed)/psp_general.business_days(dat(i),last_day(dat(i)));
106 elsif ((dat(i) between sch_rec.sbd and sch_rec.sed)AND(last_day(dat(i))<=sch_rec.sed))then
107 per:= sch_rec.sp;
108 else
109 per:=0;
110 --dbms_output.put_line('reached here'||to_char(per));
111 end if;
112 per:=round(per,2);
113 --dbms_output.put_line(to_char(per));
114 insert into psp_matrix_driver2(SCHEDULE_LINE_ID,
115 PERIOD,
116 PERIOD_SCHEDULE_PERCENT) values
117 (sch_rec.l_id,
118 dat(i),
119 per);
120 END LOOP;
121 END LOOP;
122
123 END load_table;
124
125 end psp_matrix_driver2_pkg;
[Home] [Help]