[Home] [Help]
PACKAGE BODY: APPS.WMS_SORT_CRITERIA_PKG
Source
1 PACKAGE BODY WMS_SORT_CRITERIA_PKG AS
2 /* $Header: WMSHPCRB.pls 120.1 2005/06/21 02:06:21 appldev ship $ */
3
4 PROCEDURE INSERT_ROW (
5 x_rowid IN OUT NOCOPY VARCHAR2
6 ,x_rule_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_parameter_id IN NUMBER
14 ,x_order_code IN NUMBER
15 ,x_attribute_category IN VARCHAR2
16 ,x_attribute1 IN VARCHAR2
17 ,x_attribute2 IN VARCHAR2
18 ,x_attribute3 IN VARCHAR2
19 ,x_attribute4 IN VARCHAR2
20 ,x_attribute5 IN VARCHAR2
21 ,x_attribute6 IN VARCHAR2
22 ,x_attribute7 IN VARCHAR2
23 ,x_attribute8 IN VARCHAR2
24 ,x_attribute9 IN VARCHAR2
25 ,x_attribute10 IN VARCHAR2
26 ,x_attribute11 IN VARCHAR2
27 ,x_attribute12 IN VARCHAR2
28 ,x_attribute13 IN VARCHAR2
29 ,x_attribute14 IN VARCHAR2
30 ,x_attribute15 IN VARCHAR2
31 )IS
32 CURSOR C IS SELECT ROWID FROM WMS_SORT_CRITERIA
33 WHERE rule_id = x_rule_id
34 AND sequence_number = x_sequence_number;
35 BEGIN
36
37 INSERT INTO WMS_SORT_CRITERIA (
38 rule_id
39 ,sequence_number
40 ,last_updated_by
41 ,last_update_date
42 ,created_by
43 ,creation_date
44 ,last_update_login
45 ,parameter_id
46 ,order_code
47 ,attribute_category
48 ,attribute1
49 ,attribute2
50 ,attribute3
51 ,attribute4
52 ,attribute5
53 ,attribute6
54 ,attribute7
55 ,attribute8
56 ,attribute9
57 ,attribute10
58 ,attribute11
59 ,attribute12
60 ,attribute13
61 ,attribute14
62 ,attribute15
63 ) values (
64 x_rule_id
65 ,x_sequence_number
66 ,x_last_updated_by
67 ,x_last_update_date
68 ,x_created_by
69 ,x_creation_date
70 ,x_last_update_login
71 ,x_parameter_id
72 ,x_order_code
73 ,x_attribute_category
74 ,x_attribute1
75 ,x_attribute2
76 ,x_attribute3
77 ,x_attribute4
78 ,x_attribute5
79 ,x_attribute6
80 ,x_attribute7
81 ,x_attribute8
82 ,x_attribute9
83 ,x_attribute10
84 ,x_attribute11
85 ,x_attribute12
86 ,x_attribute13
87 ,x_attribute14
88 ,x_attribute15
89 );
90
91 OPEN C;
92 FETCH C INTO x_rowid;
93 IF (C%NOTFOUND) THEN
94 CLOSE C;
95 RAISE NO_DATA_FOUND;
96 END IF;
97 CLOSE C;
98 END INSERT_ROW;
99
100 PROCEDURE LOCK_ROW (
101 x_rowid IN VARCHAR2
102 ,x_rule_id IN NUMBER
103 ,x_sequence_number IN NUMBER
104 ,x_parameter_id IN NUMBER
105 ,x_order_code IN NUMBER
106 ,x_attribute_category IN VARCHAR2
107 ,x_attribute1 IN VARCHAR2
108 ,x_attribute2 IN VARCHAR2
109 ,x_attribute3 IN VARCHAR2
110 ,x_attribute4 IN VARCHAR2
111 ,x_attribute5 IN VARCHAR2
112 ,x_attribute6 IN VARCHAR2
113 ,x_attribute7 IN VARCHAR2
114 ,x_attribute8 IN VARCHAR2
115 ,x_attribute9 IN VARCHAR2
116 ,x_attribute10 IN VARCHAR2
117 ,x_attribute11 IN VARCHAR2
118 ,x_attribute12 IN VARCHAR2
119 ,x_attribute13 IN VARCHAR2
120 ,x_attribute14 IN VARCHAR2
121 ,x_attribute15 IN VARCHAR2
122 )IS
123 CURSOR C IS SELECT
124 rule_id
125 ,sequence_number
126 ,last_updated_by
127 ,last_update_date
128 ,created_by
129 ,creation_date
130 ,last_update_login
131 ,parameter_id
132 ,order_code
133 ,attribute_category
134 ,attribute1
135 ,attribute2
136 ,attribute3
137 ,attribute4
138 ,attribute5
139 ,attribute6
140 ,attribute7
141 ,attribute8
142 ,attribute9
143 ,attribute10
144 ,attribute11
145 ,attribute12
146 ,attribute13
147 ,attribute14
148 ,attribute15
149 FROM WMS_SORT_CRITERIA
150 WHERE rowid = x_rowid
151 FOR UPDATE OF rule_id NOWAIT;
152
153 recinfo c%ROWTYPE;
154 BEGIN
155 OPEN c;
156 FETCH c INTO recinfo;
157 IF (c%notfound) THEN
158 CLOSE c;
159 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
160 app_exception.raise_exception;
161 END IF;
162 CLOSE c;
163 IF ( (recinfo.rule_id = x_rule_id)
164 AND (recinfo.sequence_number = x_sequence_number)
165 AND (recinfo.parameter_id = x_parameter_id)
166 AND (recinfo.order_code = x_order_code)
167 AND ((recinfo.attribute_category = x_attribute_category)
168 OR ((recinfo.attribute_category IS NULL)
169 AND (x_attribute_category IS NULL)))
170 AND ((recinfo.attribute1 = x_attribute1)
171 OR ((recinfo.attribute1 IS NULL)
172 AND (x_attribute1 IS NULL)))
173 AND ((recinfo.attribute2 = x_attribute2)
174 OR ((recinfo.attribute2 IS NULL)
175 AND (x_attribute2 IS NULL)))
176 AND ((recinfo.attribute3 = x_attribute3)
177 OR ((recinfo.attribute3 IS NULL)
178 AND (x_attribute3 IS NULL)))
179 AND ((recinfo.attribute4 = x_attribute4)
180 OR ((recinfo.attribute4 IS NULL)
181 AND (x_attribute4 IS NULL)))
182 AND ((recinfo.attribute5 = x_attribute5)
183 OR ((recinfo.attribute5 IS NULL)
184 AND (x_attribute5 IS NULL)))
185 AND ((recinfo.attribute6 = x_attribute6)
186 OR ((recinfo.attribute6 IS NULL)
187 AND (x_attribute6 IS NULL)))
188 AND ((recinfo.attribute7 = x_attribute7)
189 OR ((recinfo.attribute7 IS NULL)
190 AND (x_attribute7 IS NULL)))
191 AND ((recinfo.attribute8 = x_attribute8)
192 OR ((recinfo.attribute8 IS NULL)
193 AND (x_attribute8 IS NULL)))
194 AND ((recinfo.attribute9 = x_attribute9)
195 OR ((recinfo.attribute9 IS NULL)
196 AND (x_attribute9 IS NULL)))
197 AND ((recinfo.attribute10 = x_attribute10)
198 OR ((recinfo.attribute10 IS NULL)
199 AND (x_attribute10 IS NULL)))
200 AND ((recinfo.attribute11 = x_attribute11)
201 OR ((recinfo.attribute11 IS NULL)
202 AND (x_attribute11 IS NULL)))
203 AND ((recinfo.attribute12 = x_attribute12)
204 OR ((recinfo.attribute12 IS NULL)
205 AND (x_attribute12 IS NULL)))
206 AND ((recinfo.attribute13 = x_attribute13)
207 OR ((recinfo.attribute13 IS NULL)
208 AND (x_attribute13 IS NULL)))
209 AND ((recinfo.attribute14 = x_attribute14)
210 OR ((recinfo.attribute14 IS NULL)
211 AND (x_attribute14 IS NULL)))
212 AND ((recinfo.attribute15 = x_attribute15)
213 OR ((recinfo.attribute15 IS NULL)
214 AND (x_attribute15 IS NULL)))
215 ) THEN
216 NULL;
217 ELSE
218 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
219 app_exception.raise_exception;
220 END IF;
221 END LOCK_ROW;
222
223 PROCEDURE UPDATE_ROW (
224 x_rowid IN VARCHAR2
225 ,x_rule_id IN NUMBER
226 ,x_sequence_number IN NUMBER
227 ,x_last_updated_by IN NUMBER
228 ,x_last_update_date IN DATE
229 ,x_last_update_login IN NUMBER
230 ,x_parameter_id IN NUMBER
231 ,x_order_code IN NUMBER
232 ,x_attribute_category IN VARCHAR2
233 ,x_attribute1 IN VARCHAR2
234 ,x_attribute2 IN VARCHAR2
235 ,x_attribute3 IN VARCHAR2
236 ,x_attribute4 IN VARCHAR2
237 ,x_attribute5 IN VARCHAR2
238 ,x_attribute6 IN VARCHAR2
239 ,x_attribute7 IN VARCHAR2
240 ,x_attribute8 IN VARCHAR2
241 ,x_attribute9 IN VARCHAR2
242 ,x_attribute10 IN VARCHAR2
243 ,x_attribute11 IN VARCHAR2
244 ,x_attribute12 IN VARCHAR2
245 ,x_attribute13 IN VARCHAR2
246 ,x_attribute14 IN VARCHAR2
247 ,x_attribute15 IN VARCHAR2
248 )IS
249 BEGIN
250 IF (x_rowid IS NOT NULL) THEN
251
252 UPDATE WMS_SORT_CRITERIA SET
253 rule_id = x_rule_id
254 ,sequence_number = x_sequence_number
255 ,last_updated_by = x_last_updated_by
256 ,last_update_date = x_last_update_date
257 ,last_update_login = x_last_update_login
258 ,parameter_id = x_parameter_id
259 ,order_code = x_order_code
260 ,attribute_category = x_attribute_category
261 ,attribute1 = x_attribute1
262 ,attribute2 = x_attribute2
263 ,attribute3 = x_attribute3
264 ,attribute4 = x_attribute4
265 ,attribute5 = x_attribute5
266 ,attribute6 = x_attribute6
267 ,attribute7 = x_attribute7
268 ,attribute8 = x_attribute8
269 ,attribute9 = x_attribute9
270 ,attribute10 = x_attribute10
271 ,attribute11 = x_attribute11
272 ,attribute12 = x_attribute12
273 ,attribute13 = x_attribute13
274 ,attribute14 = x_attribute14
275 ,attribute15 = x_attribute15
276 WHERE rowid = x_rowid;
277 ELSE
278 UPDATE WMS_SORT_CRITERIA SET
279 rule_id = x_rule_id
280 ,sequence_number = x_sequence_number
281 ,last_updated_by = x_last_updated_by
282 ,last_update_date = x_last_update_date
283 ,last_update_login = x_last_update_login
284 ,parameter_id = x_parameter_id
285 ,order_code = x_order_code
286 ,attribute_category = x_attribute_category
287 ,attribute1 = x_attribute1
288 ,attribute2 = x_attribute2
289 ,attribute3 = x_attribute3
290 ,attribute4 = x_attribute4
291 ,attribute5 = x_attribute5
292 ,attribute6 = x_attribute6
293 ,attribute7 = x_attribute7
294 ,attribute8 = x_attribute8
295 ,attribute9 = x_attribute9
296 ,attribute10 = x_attribute10
297 ,attribute11 = x_attribute11
298 ,attribute12 = x_attribute12
299 ,attribute13 = x_attribute13
300 ,attribute14 = x_attribute14
301 ,attribute15 = x_attribute15
302 WHERE rule_id = x_rule_id
303 AND sequence_number = x_sequence_number;
304 END IF;
305
306 IF (SQL%NOTFOUND) THEN
307 RAISE NO_DATA_FOUND;
308 END IF;
309 END UPDATE_ROW;
310
311 PROCEDURE DELETE_ROW (
312 x_rowid IN VARCHAR2
313 )IS
314 BEGIN
315
316 DELETE FROM WMS_SORT_CRITERIA
317 WHERE rowid = x_rowid;
318
319 IF (SQL%NOTFOUND) THEN
320 RAISE NO_DATA_FOUND;
321 END IF;
322 END DELETE_ROW;
323
324 PROCEDURE LOAD_ROW (
325 X_RULE_ID IN NUMBER
326 ,x_OWNER IN VARCHAR2
327 ,X_SEQUENCE_NUMBER IN NUMBER
328 ,X_PARAMETER_ID IN NUMBER
329 ,X_ORDER_CODE IN NUMBER
330 ,X_ATTRIBUTE_CATEGORY IN VARCHAR2
331 ,X_ATTRIBUTE1 IN VARCHAR2
332 ,X_ATTRIBUTE2 IN VARCHAR2
333 ,X_ATTRIBUTE3 IN VARCHAR2
334 ,X_ATTRIBUTE4 IN VARCHAR2
335 ,X_ATTRIBUTE5 IN VARCHAR2
336 ,X_ATTRIBUTE6 IN VARCHAR2
337 ,X_ATTRIBUTE7 IN VARCHAR2
338 ,X_ATTRIBUTE8 IN VARCHAR2
339 ,X_ATTRIBUTE9 IN VARCHAR2
340 ,X_ATTRIBUTE10 IN VARCHAR2
341 ,X_ATTRIBUTE11 IN VARCHAR2
342 ,X_ATTRIBUTE12 IN VARCHAR2
343 ,X_ATTRIBUTE13 IN VARCHAR2
344 ,X_ATTRIBUTE14 IN VARCHAR2
345 ,X_ATTRIBUTE15 IN VARCHAR2
346 ) IS
347 BEGIN
348 DECLARE
349 l_rule_id NUMBER;
350 l_sequence_number NUMBER;
351 l_parameter_id NUMBER;
352 l_order_code NUMBER;
353 l_user_id NUMBER := 0;
354 l_row_id VARCHAR2(64);
355 l_sysdate DATE;
356 l_date_type_from NUMBER := 0;
357 l_date_type_to NUMBER := 0;
358 BEGIN
362 --
359 IF (x_owner = 'SEED') THEN
360 l_user_id := 1;
361 END IF;
363 SELECT Sysdate INTO l_sysdate FROM dual;
364 l_rule_id := fnd_number.canonical_to_number(x_rule_id);
365 l_parameter_id := fnd_number.canonical_to_number(x_parameter_id );
366 l_sequence_number :=
367 fnd_number.canonical_to_number(x_sequence_number);
368 l_order_code := fnd_number.canonical_to_number(x_order_code );
369
370 wms_sort_criteria_pkg.update_row
371 (
372 x_rowid => NULL
373 ,x_rule_id => l_rule_id
374 ,x_sequence_number => l_sequence_number
375 ,x_last_updated_by => l_user_id
376 ,x_last_update_date => l_sysdate
377 ,x_last_update_login => 0
378 ,x_parameter_id => l_parameter_id
379 ,x_order_code => x_order_code
380 ,x_attribute_category => x_attribute_category
381 ,x_attribute1 => x_attribute1
382 ,x_attribute2 => x_attribute2
383 ,x_attribute3 => x_attribute3
384 ,x_attribute4 => x_attribute4
385 ,x_attribute5 => x_attribute5
386 ,x_attribute6 => x_attribute6
387 ,x_attribute7 => x_attribute7
388 ,x_attribute8 => x_attribute8
389 ,x_attribute9 => x_attribute9
390 ,x_attribute10 => x_attribute10
391 ,x_attribute11 => x_attribute11
392 ,x_attribute12 => x_attribute12
393 ,x_attribute13 => x_attribute13
394 ,x_attribute14 => x_attribute14
395 ,x_attribute15 => x_attribute15
396 );
397 EXCEPTION
398 WHEN no_data_found THEN
399 wms_sort_criteria_pkg.insert_row
400 (
401 x_rowid => l_row_id
402 ,x_rule_id => l_rule_id
403 ,x_sequence_number => l_sequence_number
404 ,x_last_updated_by => l_user_id
405 ,x_last_update_date => l_sysdate
406 ,x_created_by => l_user_id
407 ,x_creation_date => l_sysdate
408 ,x_last_update_login => 0
409 ,x_parameter_id => l_parameter_id
410 ,x_order_code => l_order_code
411 ,x_attribute_category => x_attribute_category
412 ,x_attribute1 => x_attribute1
413 ,x_attribute2 => x_attribute2
414 ,x_attribute3 => x_attribute3
415 ,x_attribute4 => x_attribute4
416 ,x_attribute5 => x_attribute5
417 ,x_attribute6 => x_attribute6
418 ,x_attribute7 => x_attribute7
419 ,x_attribute8 => x_attribute8
420 ,x_attribute9 => x_attribute9
421 ,x_attribute10 => x_attribute10
422 ,x_attribute11 => x_attribute11
423 ,x_attribute12 => x_attribute12
424 ,x_attribute13 => x_attribute13
425 ,x_attribute14 => x_attribute14
426 ,x_attribute15 => x_attribute15
427 );
428 END;
429 END load_row;
430 END WMS_SORT_CRITERIA_PKG;