[Home] [Help]
PACKAGE BODY: APPS.WMS_STRATEGY_MEMBERS_PKG
Source
1 PACKAGE BODY WMS_STRATEGY_MEMBERS_PKG AS
2 /* $Header: WMSPPSMB.pls 120.1 2005/06/20 02:41:10 appldev ship $ */
3 --
4 PROCEDURE INSERT_ROW (
5 x_rowid IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6 ,x_strategy_id IN NUMBER
7 ,x_sequence_number IN NUMBER
8 ,x_last_updated_by IN NUMBER
9 ,x_last_update_date IN DATE
10 ,x_created_by IN NUMBER
11 ,x_creation_date IN DATE
12 ,x_last_update_login IN NUMBER
13 ,x_rule_id IN NUMBER
14 ,x_partial_success_allowed_flag IN VARCHAR2
15 ,x_effective_from IN DATE
16 ,x_effective_to IN DATE
17 ,x_attribute_category IN VARCHAR2
18 ,x_attribute1 IN VARCHAR2
19 ,x_attribute2 IN VARCHAR2
20 ,x_attribute3 IN VARCHAR2
21 ,x_attribute4 IN VARCHAR2
22 ,x_attribute5 IN VARCHAR2
23 ,x_attribute6 IN VARCHAR2
24 ,x_attribute7 IN VARCHAR2
25 ,x_attribute8 IN VARCHAR2
26 ,x_attribute9 IN VARCHAR2
27 ,x_attribute10 IN VARCHAR2
28 ,x_attribute11 IN VARCHAR2
29 ,x_attribute12 IN VARCHAR2
30 ,x_attribute13 IN VARCHAR2
31 ,x_attribute14 IN VARCHAR2
32 ,x_attribute15 IN VARCHAR2
33 ,x_date_type_code IN VARCHAR2
34 ,x_date_type_lookup_type IN VARCHAR2
35 ,x_date_type_from IN NUMBER
36 ,x_date_type_to IN NUMBER
37 )IS
38 CURSOR C IS SELECT ROWID FROM WMS_STRATEGY_MEMBERS
39 WHERE strategy_id = x_strategy_id
40 AND sequence_number = x_sequence_number;
41 BEGIN
42
43 INSERT INTO WMS_STRATEGY_MEMBERS (
44 strategy_id
45 ,sequence_number
46 ,last_updated_by
47 ,last_update_date
48 ,created_by
49 ,creation_date
50 ,last_update_login
51 ,rule_id
52 ,partial_success_allowed_flag
53 ,effective_from
54 ,effective_to
55 ,attribute_category
56 ,attribute1
57 ,attribute2
58 ,attribute3
59 ,attribute4
60 ,attribute5
61 ,attribute6
62 ,attribute7
63 ,attribute8
64 ,attribute9
65 ,attribute10
66 ,attribute11
67 ,attribute12
68 ,attribute13
69 ,attribute14
70 ,attribute15
71 ,date_type_code
72 ,date_type_lookup_type
73 ,date_type_from
74 ,date_type_to
75 ) values (
76 x_strategy_id
77 ,x_sequence_number
78 ,x_last_updated_by
79 ,x_last_update_date
80 ,x_created_by
81 ,x_creation_date
82 ,x_last_update_login
83 ,x_rule_id
84 ,x_partial_success_allowed_flag
85 ,x_effective_from
86 ,x_effective_to
87 ,x_attribute_category
88 ,x_attribute1
89 ,x_attribute2
90 ,x_attribute3
91 ,x_attribute4
92 ,x_attribute5
93 ,x_attribute6
94 ,x_attribute7
95 ,x_attribute8
96 ,x_attribute9
97 ,x_attribute10
98 ,x_attribute11
99 ,x_attribute12
100 ,x_attribute13
101 ,x_attribute14
102 ,x_attribute15
103 ,x_date_type_code
104 ,x_date_type_lookup_type
105 ,x_date_type_from
106 ,x_date_type_to
107 );
108
109 OPEN C;
110 FETCH C INTO x_rowid;
111 IF (C%NOTFOUND) THEN
112 CLOSE C;
113 RAISE NO_DATA_FOUND;
114 END IF;
115 CLOSE C;
116 END INSERT_ROW;
117 --
118 PROCEDURE LOCK_ROW (
119 x_rowid IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
120 ,x_strategy_id IN NUMBER
121 ,x_sequence_number IN NUMBER
122 ,x_rule_id IN NUMBER
123 ,x_partial_success_allowed_flag IN VARCHAR2
124 ,x_effective_from IN DATE
125 ,x_effective_to IN DATE
126 ,x_attribute_category IN VARCHAR2
127 ,x_attribute1 IN VARCHAR2
128 ,x_attribute2 IN VARCHAR2
129 ,x_attribute3 IN VARCHAR2
130 ,x_attribute4 IN VARCHAR2
131 ,x_attribute5 IN VARCHAR2
132 ,x_attribute6 IN VARCHAR2
133 ,x_attribute7 IN VARCHAR2
134 ,x_attribute8 IN VARCHAR2
135 ,x_attribute9 IN VARCHAR2
136 ,x_attribute10 IN VARCHAR2
137 ,x_attribute11 IN VARCHAR2
138 ,x_attribute12 IN VARCHAR2
139 ,x_attribute13 IN VARCHAR2
140 ,x_attribute14 IN VARCHAR2
141 ,x_attribute15 IN VARCHAR2
142 ,x_date_type_code IN VARCHAR2
143 ,x_date_type_lookup_type IN VARCHAR2
144 ,x_date_type_from IN NUMBER
145 ,x_date_type_to IN NUMBER
146 )IS
147 CURSOR C IS SELECT
148 strategy_id
149 ,sequence_number
150 ,rule_id
151 ,partial_success_allowed_flag
152 ,effective_from
153 ,effective_to
154 ,attribute_category
155 ,attribute1
156 ,attribute2
157 ,attribute3
158 ,attribute4
159 ,attribute5
160 ,attribute6
161 ,attribute7
162 ,attribute8
163 ,attribute9
164 ,attribute10
165 ,attribute11
166 ,attribute12
167 ,attribute13
168 ,attribute14
169 ,attribute15
170 ,date_type_code
171 ,date_type_lookup_type
172 ,date_type_from
173 ,date_type_to
174 FROM WMS_STRATEGY_MEMBERS
175 WHERE rowid = x_rowid
176 FOR UPDATE OF strategy_id NOWAIT;
177
178 recinfo c%ROWTYPE;
179 BEGIN
180 OPEN c;
181 FETCH c INTO recinfo;
182 IF (c%notfound) THEN
183 CLOSE c;
184 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
185 app_exception.raise_exception;
186 END IF;
187 CLOSE c;
188 IF ( (recinfo.strategy_id = x_strategy_id)
189 AND (recinfo.sequence_number = x_sequence_number)
190 AND (recinfo.rule_id = x_rule_id)
191 AND (recinfo.partial_success_allowed_flag = x_partial_success_allowed_flag)
192 AND ((recinfo.effective_from = x_effective_from)
193 OR ((recinfo.effective_from IS NULL)
194 AND (x_effective_from IS NULL)))
195 AND ((recinfo.effective_to = x_effective_to)
196 OR ((recinfo.effective_to IS NULL)
197 AND (x_effective_to IS NULL)))
198 AND ((recinfo.attribute_category = x_attribute_category)
199 OR ((recinfo.attribute_category IS NULL)
200 AND (x_attribute_category IS NULL)))
201 AND ((recinfo.attribute1 = x_attribute1)
202 OR ((recinfo.attribute1 IS NULL)
203 AND (x_attribute1 IS NULL)))
204 AND ((recinfo.attribute2 = x_attribute2)
205 OR ((recinfo.attribute2 IS NULL)
206 AND (x_attribute2 IS NULL)))
207 AND ((recinfo.attribute3 = x_attribute3)
208 OR ((recinfo.attribute3 IS NULL)
209 AND (x_attribute3 IS NULL)))
210 AND ((recinfo.attribute4 = x_attribute4)
211 OR ((recinfo.attribute4 IS NULL)
212 AND (x_attribute4 IS NULL)))
213 AND ((recinfo.attribute5 = x_attribute5)
214 OR ((recinfo.attribute5 IS NULL)
215 AND (x_attribute5 IS NULL)))
216 AND ((recinfo.attribute6 = x_attribute6)
217 OR ((recinfo.attribute6 IS NULL)
218 AND (x_attribute6 IS NULL)))
219 AND ((recinfo.attribute7 = x_attribute7)
220 OR ((recinfo.attribute7 IS NULL)
221 AND (x_attribute7 IS NULL)))
222 AND ((recinfo.attribute8 = x_attribute8)
223 OR ((recinfo.attribute8 IS NULL)
224 AND (x_attribute8 IS NULL)))
225 AND ((recinfo.attribute9 = x_attribute9)
226 OR ((recinfo.attribute9 IS NULL)
227 AND (x_attribute9 IS NULL)))
228 AND ((recinfo.attribute10 = x_attribute10)
229 OR ((recinfo.attribute10 IS NULL)
230 AND (x_attribute10 IS NULL)))
231 AND ((recinfo.attribute11 = x_attribute11)
232 OR ((recinfo.attribute11 IS NULL)
233 AND (x_attribute11 IS NULL)))
234 AND ((recinfo.attribute12 = x_attribute12)
235 OR ((recinfo.attribute12 IS NULL)
236 AND (x_attribute12 IS NULL)))
237 AND ((recinfo.attribute13 = x_attribute13)
238 OR ((recinfo.attribute13 IS NULL)
239 AND (x_attribute13 IS NULL)))
240 AND ((recinfo.attribute14 = x_attribute14)
241 OR ((recinfo.attribute14 IS NULL)
242 AND (x_attribute14 IS NULL)))
243 AND ((recinfo.attribute15 = x_attribute15)
244 OR ((recinfo.attribute15 IS NULL)
245 AND (x_attribute15 IS NULL)))
246 AND ((recinfo.date_type_code = x_date_type_code)
247 OR ((recinfo.date_type_code IS NULL)
248 AND (x_date_type_code IS NULL)))
249 AND ((recinfo.date_type_lookup_type = x_date_type_lookup_type)
250 OR ((recinfo.date_type_lookup_type IS NULL)
251 AND (x_date_type_lookup_type IS NULL)))
252 AND ((recinfo.date_type_from = x_date_type_from)
253 OR ((recinfo.date_type_from IS NULL)
254 AND (x_date_type_from IS NULL)))
255 AND ((recinfo.date_type_to = x_date_type_to)
256 OR ((recinfo.date_type_to IS NULL)
257 AND (x_date_type_to IS NULL)))
258
259 ) THEN
260 NULL;
261 ELSE
262 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
263 app_exception.raise_exception;
264 END IF;
265 END LOCK_ROW;
266 --
267 PROCEDURE UPDATE_ROW (
268 x_rowid IN VARCHAR2
269 ,x_strategy_id IN NUMBER
270 ,x_sequence_number IN NUMBER
271 ,x_last_updated_by IN NUMBER
272 ,x_last_update_date IN DATE
273 ,x_last_update_login IN NUMBER
274 ,x_rule_id IN NUMBER
275 ,x_partial_success_allowed_flag IN VARCHAR2
276 ,x_effective_from IN DATE
277 ,x_effective_to IN DATE
278 ,x_attribute_category IN VARCHAR2
279 ,x_attribute1 IN VARCHAR2
280 ,x_attribute2 IN VARCHAR2
281 ,x_attribute3 IN VARCHAR2
282 ,x_attribute4 IN VARCHAR2
283 ,x_attribute5 IN VARCHAR2
284 ,x_attribute6 IN VARCHAR2
285 ,x_attribute7 IN VARCHAR2
286 ,x_attribute8 IN VARCHAR2
287 ,x_attribute9 IN VARCHAR2
288 ,x_attribute10 IN VARCHAR2
289 ,x_attribute11 IN VARCHAR2
290 ,x_attribute12 IN VARCHAR2
291 ,x_attribute13 IN VARCHAR2
292 ,x_attribute14 IN VARCHAR2
293 ,x_attribute15 IN VARCHAR2
294 ,x_date_type_code IN VARCHAR2
295 ,x_date_type_lookup_type IN VARCHAR2
296 ,x_date_type_from IN NUMBER
297 ,x_date_type_to IN NUMBER
298 )IS
299 BEGIN
300 IF (x_rowid IS NOT NULL) THEN
301 UPDATE WMS_STRATEGY_MEMBERS SET
302 strategy_id = x_strategy_id
303 ,sequence_number = x_sequence_number
304 ,last_updated_by = x_last_updated_by
305 ,last_update_date = x_last_update_date
306 ,last_update_login = x_last_update_login
307 ,rule_id = x_rule_id
308 ,partial_success_allowed_flag = x_partial_success_allowed_flag
309 ,effective_from = x_effective_from
310 ,effective_to = x_effective_to
311 ,attribute_category = x_attribute_category
312 ,attribute1 = x_attribute1
313 ,attribute2 = x_attribute2
314 ,attribute3 = x_attribute3
315 ,attribute4 = x_attribute4
316 ,attribute5 = x_attribute5
317 ,attribute6 = x_attribute6
318 ,attribute7 = x_attribute7
319 ,attribute8 = x_attribute8
320 ,attribute9 = x_attribute9
321 ,attribute10 = x_attribute10
322 ,attribute11 = x_attribute11
323 ,attribute12 = x_attribute12
324 ,attribute13 = x_attribute13
325 ,attribute14 = x_attribute14
326 ,attribute15 = x_attribute15
327 ,date_type_code = x_date_type_code
328 ,date_type_lookup_type = x_date_type_lookup_type
329 ,date_type_from = x_date_type_from
330 ,date_type_to = x_date_type_to
331 WHERE rowid = x_rowid;
332 ELSE
333 UPDATE WMS_STRATEGY_MEMBERS SET
334 strategy_id = x_strategy_id
335 ,sequence_number = x_sequence_number
336 ,last_updated_by = x_last_updated_by
337 ,last_update_date = x_last_update_date
338 ,last_update_login = x_last_update_login
339 ,rule_id = x_rule_id
340 ,partial_success_allowed_flag = x_partial_success_allowed_flag
341 ,effective_from = x_effective_from
342 ,effective_to = x_effective_to
343 ,attribute_category = x_attribute_category
344 ,attribute1 = x_attribute1
345 ,attribute2 = x_attribute2
346 ,attribute3 = x_attribute3
347 ,attribute4 = x_attribute4
348 ,attribute5 = x_attribute5
349 ,attribute6 = x_attribute6
350 ,attribute7 = x_attribute7
351 ,attribute8 = x_attribute8
352 ,attribute9 = x_attribute9
353 ,attribute10 = x_attribute10
354 ,attribute11 = x_attribute11
355 ,attribute12 = x_attribute12
356 ,attribute13 = x_attribute13
357 ,attribute14 = x_attribute14
358 ,attribute15 = x_attribute15
359 ,date_type_code = x_date_type_code
360 ,date_type_lookup_type = x_date_type_lookup_type
361 ,date_type_from = x_date_type_from
362 ,date_type_to = x_date_type_to
363 WHERE strategy_id = x_strategy_id
364 AND sequence_number = x_sequence_number;
365 END IF;
366
367 IF (SQL%NOTFOUND) THEN
368 RAISE NO_DATA_FOUND;
369 END IF;
370 END UPDATE_ROW;--
371 PROCEDURE DELETE_ROW (
372 x_rowid IN VARCHAR2
373 )IS
374 BEGIN
375
376 DELETE FROM WMS_STRATEGY_MEMBERS
377 WHERE rowid = x_rowid;
378
379 IF (SQL%NOTFOUND) THEN
380 RAISE NO_DATA_FOUND;
381 END IF;
382 END DELETE_ROW;
383
384 PROCEDURE LOAD_ROW
385 (
386 x_strategy_id IN NUMBER
387 ,x_owner IN VARCHAR2
388 ,x_SEQUENCE_NUMBER IN NUMBER
389 ,x_RULE_ID IN NUMBER
390 ,x_PARTIAL_SUCCESS_ALLOWED_FLAG IN VARCHAR2
391 ,x_EFFECTIVE_FROM IN DATE
392 ,x_EFFECTIVE_TO IN DATE
393 ,x_DATE_TYPE_CODE IN VARCHAR2
394 ,x_DATE_TYPE_LOOKUP_TYPE IN VARCHAR2
395 ,x_DATE_TYPE_FROM IN NUMBER
396 ,x_DATE_TYPE_TO IN NUMBER
397 ,x_ATTRIBUTE_CATEGORY IN VARCHAR2
401 ,x_ATTRIBUTE4 IN VARCHAR2
398 ,x_ATTRIBUTE1 IN VARCHAR2
399 ,x_ATTRIBUTE2 IN VARCHAR2
400 ,x_ATTRIBUTE3 IN VARCHAR2
402 ,x_ATTRIBUTE5 IN VARCHAR2
403 ,x_ATTRIBUTE6 IN VARCHAR2
404 ,x_ATTRIBUTE7 IN VARCHAR2
405 ,x_ATTRIBUTE8 IN VARCHAR2
406 ,x_ATTRIBUTE9 IN VARCHAR2
407 ,x_ATTRIBUTE10 IN VARCHAR2
408 ,x_ATTRIBUTE11 IN VARCHAR2
409 ,x_ATTRIBUTE12 IN VARCHAR2
410 ,x_ATTRIBUTE13 IN VARCHAR2
411 ,x_ATTRIBUTE14 IN VARCHAR2
412 ,x_ATTRIBUTE15 IN VARCHAR2
413 ) IS
414 BEGIN
415 DECLARE
416 l_strategy_id NUMBER;
417 l_rule_id NUMBER;
418 l_sequence_number NUMBER;
419 l_user_id NUMBER := 0;
420 l_row_id VARCHAR2(64);
421 l_sysdate DATE;
422 l_date_type_from NUMBER := 0;
423 l_date_type_to NUMBER := 0;
424 BEGIN
425 IF (x_owner = 'SEED') THEN
426 l_user_id := 1;
427 END IF;
428 --
429 SELECT Sysdate INTO l_sysdate FROM dual;
430 l_strategy_id := fnd_number.canonical_to_number(x_strategy_id);
431 l_rule_id := fnd_number.canonical_to_number(x_rule_id );
432 l_sequence_number :=
433 fnd_number.canonical_to_number(x_sequence_number);
434 l_date_type_from :=
435 fnd_number.canonical_to_number(x_date_type_from );
436 l_date_type_to :=
437 fnd_number.canonical_to_number(x_date_type_to );
438
439 wms_strategy_members_pkg.update_row
440 (
441 x_rowid => NULL
442 ,x_strategy_id => l_strategy_id
443 ,x_sequence_number => l_sequence_number
444 ,x_last_updated_by => l_user_id
445 ,x_last_update_date => l_sysdate
446 ,x_last_update_login => 0
447 ,x_rule_id => l_rule_id
448 ,x_partial_success_allowed_flag => x_partial_success_allowed_flag
449 ,x_effective_from => x_effective_from
450 ,x_effective_to => x_effective_to
451 ,x_attribute_category => x_attribute_category
452 ,x_attribute1 => x_attribute1
453 ,x_attribute2 => x_attribute2
454 ,x_attribute3 => x_attribute3
455 ,x_attribute4 => x_attribute4
456 ,x_attribute5 => x_attribute5
457 ,x_attribute6 => x_attribute6
458 ,x_attribute7 => x_attribute7
459 ,x_attribute8 => x_attribute8
460 ,x_attribute9 => x_attribute9
461 ,x_attribute10 => x_attribute10
462 ,x_attribute11 => x_attribute11
463 ,x_attribute12 => x_attribute12
464 ,x_attribute13 => x_attribute13
465 ,x_attribute14 => x_attribute14
466 ,x_attribute15 => x_attribute15
467 ,x_date_type_code => x_date_type_code
468 ,x_date_type_lookup_type => x_date_type_lookup_type
469 ,x_date_type_from => l_date_type_from
470 ,x_date_type_to => l_date_type_to
471 );
472 EXCEPTION
473 WHEN no_data_found THEN
474 wms_strategy_members_pkg.insert_row
475 (
476 x_rowid => l_row_id
477 ,x_strategy_id => l_strategy_id
478 ,x_sequence_number => l_sequence_number
479 ,x_last_updated_by => l_user_id
480 ,x_last_update_date => l_sysdate
481 ,x_created_by => l_user_id
482 ,x_creation_date => l_sysdate
483 ,x_last_update_login => 0
484 ,x_rule_id => l_rule_id
485 ,x_partial_success_allowed_flag => x_partial_success_allowed_flag
486 ,x_effective_from => x_effective_from
487 ,x_effective_to => x_effective_to
488 ,x_attribute_category => x_attribute_category
489 ,x_attribute1 => x_attribute1
490 ,x_attribute2 => x_attribute2
491 ,x_attribute3 => x_attribute3
492 ,x_attribute4 => x_attribute4
493 ,x_attribute5 => x_attribute5
494 ,x_attribute6 => x_attribute6
495 ,x_attribute7 => x_attribute7
496 ,x_attribute8 => x_attribute8
497 ,x_attribute9 => x_attribute9
498 ,x_attribute10 => x_attribute10
499 ,x_attribute11 => x_attribute11
500 ,x_attribute12 => x_attribute12
501 ,x_attribute13 => x_attribute13
502 ,x_attribute14 => x_attribute14
503 ,x_attribute15 => x_attribute15
504 ,x_date_type_code => x_date_type_code
505 ,x_date_type_lookup_type => x_date_type_lookup_type
506 ,x_date_type_from => l_date_type_from
507 ,x_date_type_to => l_date_type_to
508 );
509 END;
510 END load_row;
511 END WMS_STRATEGY_MEMBERS_PKG;
512