DBA Data[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