1 PACKAGE BODY MSC_RepPeriods_PUB AS
2 /* $Header: MSCPRPDB.pls 120.2 2007/10/16 17:06:24 navgoel ship $ */
3
4 Procedure Maintain_Rep_Periods(arg_org_id IN NUMBER,
5 arg_instance_id IN NUMBER,
6 arg_user_id IN NUMBER) IS
7 /*---------------------------+
8 | Variable delarations |
9 +---------------------------*/
10 var_calendar_code VARCHAR2(14);
11 var_exception_set_id NUMBER;
12 param msc_parameters%ROWTYPE;
13 var_max_date DATE;
14 var_curr_date DATE;
15 var_curr_bucket NUMBER := 1;
16 var_end_date1 DATE;
17 var_end_date2 DATE;
18 var_last_date1 DATE;
19 var_last_date2 DATE;
20 var_last_date3 DATE;
21 var_counter NUMBER := 0;
22 var_prev_workday DATE := TO_DATE(1, 'J');
23 var_curr_workday DATE := TO_DATE(1, 'J');
24 WORKDATE_PERIODS CONSTANT NUMBER := 1;
25 CALENDAR_PERIODS CONSTANT NUMBER := 2;
26 BUCKET_TYPE CONSTANT NUMBER := 1;
27 VERSION CONSTANT CHAR(80) :=
28 '$Header: MSCPRPDB.pls 120.2 2007/10/16 17:06:24 navgoel ship $';
29 BEGIN
30
31 -- Lock table msc_repetitive_periods before insert/delete/update
32 LOCK TABLE msc_repetitive_periods IN EXCLUSIVE MODE;
33
34 -- Select the organization information
35 SELECT *
36 INTO param
37 FROM msc_parameters
38 WHERE organization_id = arg_org_id
39 AND sr_instance_id = arg_instance_id;
40
41 -- Select the calendar code and exception set id for this org
42 msc_calendar.select_calendar_defaults(
43 arg_org_id,
44 arg_instance_id,
45 var_calendar_code,
46 var_exception_set_id);
47
48 -- Get the last date in this calendar
49 SELECT MAX(calendar_date)
50 INTO var_max_date
51 FROM msc_calendar_dates
52 WHERE calendar_code = var_calendar_code
53 AND exception_set_id = var_exception_set_id
54 AND seq_num IS NOT NULL;
55
56 DELETE FROM msc_repetitive_periods
57 WHERE organization_id = arg_org_id
58 AND sr_instance_id = arg_instance_id;
59
60 var_curr_date := param.repetitive_anchor_date;
61
62 -- Calculate the end date for each bucket
63 IF param.period_type = WORKDATE_PERIODS THEN
64 -- Set the end dates according to workdates
65 var_end_date1 := msc_calendar.date_offset(
66 arg_org_id,
67 arg_instance_id,
68 BUCKET_TYPE,
69 param.repetitive_anchor_date,
70 param.repetitive_horizon1);
71
72 var_end_date2 := msc_calendar.date_offset(
73 arg_org_id,
74 arg_instance_id,
75 BUCKET_TYPE,
76 var_end_date1,
77 param.repetitive_horizon2);
78
79 var_last_date1 := msc_calendar.date_offset(
80 arg_org_id,
81 arg_instance_id,
82 BUCKET_TYPE,
83 var_max_date,
84 -1 * param.repetitive_bucket_size1);
85
86 var_last_date2 := msc_calendar.date_offset(
87 arg_org_id,
88 arg_instance_id,
89 BUCKET_TYPE,
90 var_max_date,
91 -1 * param.repetitive_bucket_size2);
92
93 var_last_date3 := msc_calendar.date_offset(
94 arg_org_id,
95 arg_instance_id,
96 BUCKET_TYPE,
97 var_max_date,
98 -1 * param.repetitive_bucket_size3);
99
100 ELSE
101 var_end_date1 := param.repetitive_anchor_date +
102 param.repetitive_horizon1;
103 var_end_date2 := var_end_date1 + param.repetitive_horizon2;
104 var_last_date1 := var_max_date - param.repetitive_bucket_size1;
105 var_last_date2 := var_max_date - param.repetitive_bucket_size2;
106 var_last_date3 := var_max_date - param.repetitive_bucket_size3;
107 END IF;
108
109 WHILE TRUE LOOP
110 -- Keep looping, until you reach the end of the calendar...for each
111 -- period, insert into the table msc_repetitive_periods
112
113 -- Get the current workday
114
115 SELECT next_date
116 INTO var_curr_workday
117 FROM msc_calendar_dates
118 WHERE calendar_code = var_calendar_code
119 AND exception_set_id = var_exception_set_id
120 AND calendar_date = var_curr_date
121 AND sr_instance_id = arg_instance_id;
122
123 IF var_curr_workday <> var_prev_workday THEN
124 INSERT INTO msc_repetitive_periods
125 (
126 period_start_date,
127 organization_id,
128 sr_instance_id,
129 last_update_date,
130 last_updated_by,
131 creation_date,
132 created_by,
133 last_update_login
134 )
135 VALUES
136 (
137 var_curr_workday,
138 arg_org_id,
139 arg_instance_id,
140 SYSDATE,
141 arg_user_id,
142 SYSDATE,
143 arg_user_id,
144 -1
145 );
146 END IF;
147 var_prev_workday := var_curr_workday;
148
149 -- Adjust the var_current_date variable to the next date
150 -- If that brings you to the next bucket type, then shift the
151 -- variable var_curr_bucket
152 IF var_curr_bucket = 1 THEN -- First bucket
153 IF param.period_type = WORKDATE_PERIODS THEN
154 var_curr_date := msc_calendar.date_offset(
155 arg_org_id,
156 arg_instance_id,
157 BUCKET_TYPE,
158 var_curr_date,
159 param.repetitive_bucket_size1);
160 ELSE
161 var_curr_date := var_curr_date + param.repetitive_bucket_size1;
162 END IF;
163
164 IF var_curr_date >= var_end_date1 THEN
165 var_curr_bucket := 2;
166 END IF;
167 ELSIF var_curr_bucket = 2 THEN -- Second bucket
168 IF param.period_type = WORKDATE_PERIODS THEN
169 var_curr_date := msc_calendar.date_offset(
170 arg_org_id,
171 arg_instance_id,
172 BUCKET_TYPE,
173 var_curr_date,
174 param.repetitive_bucket_size2);
175 ELSE
176 var_curr_date := var_curr_date + param.repetitive_bucket_size2;
177 END IF;
178
179 IF var_curr_date >= var_end_date2 THEN
180 var_curr_bucket := 3;
181 END IF;
182 ELSE
183 IF param.period_type = WORKDATE_PERIODS THEN
184 var_curr_date := msc_calendar.date_offset(
185 arg_org_id,
186 arg_instance_id,
187 BUCKET_TYPE,
188 var_curr_date,
189 param.repetitive_bucket_size3);
190 ELSE
191 var_curr_date := var_curr_date + param.repetitive_bucket_size3;
192 END IF;
193 END IF;
194
195 -- Have we reached the end of the calendar?
196 IF ( var_curr_bucket = 1 AND var_curr_date < var_last_date1) OR
197 ( var_curr_bucket = 2 AND var_curr_date < var_last_date2) OR
198 ( var_curr_bucket = 3 AND var_curr_date < var_last_date3)
199 THEN
200 NULL; -- No...keep going
201 ELSE
202 EXIT; -- Yes...break out of the loop
203 END IF;
204
205 END LOOP;
206
207 -- We want to adjust all non-workday periods to the next valid workday.
208 -- However, we want to make sure that this does not result in duplicates.
209 -- Therefore only update those where a row does not exist for the next
210 -- workday. We'll delete those that are not updated in the next step.
211 UPDATE msc_repetitive_periods s
212 SET period_start_date =
213 (SELECT next_date
214 FROM msc_calendar_dates
215 WHERE calendar_code = var_calendar_code
216 AND exception_set_id = var_exception_set_id
217 AND calendar_date = s.period_start_date)
218 WHERE EXISTS
219 (SELECT NULL
220 FROM msc_calendar_dates d
221 WHERE NOT EXISTS
222 (SELECT NULL
223 FROM msc_repetitive_periods
224 WHERE organization_id = s.organization_id
225 AND sr_instance_id = s.sr_instance_id
226 AND period_start_date = d.next_date)
227 AND seq_num IS NULL
228 AND calendar_code = var_calendar_code
229 AND exception_set_id = var_exception_set_id
230 AND calendar_date = s.period_start_date)
231 AND sr_instance_id = arg_instance_id
232 AND organization_id = arg_org_id;
233
234 DELETE FROM msc_repetitive_periods s
235 WHERE organization_id = arg_org_id
236 AND sr_instance_id = arg_instance_id
237 AND EXISTS
238 (SELECT NULL
239 FROM msc_calendar_dates d
240 WHERE seq_num IS NULL
241 AND calendar_code = var_calendar_code
242 AND exception_set_id = var_exception_set_id
243 AND calendar_date = s.period_start_date);
244 COMMIT;
245
246 END Maintain_Rep_Periods;
247 END MSC_RepPeriods_PUB;
[Home] [Help]