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