[Home] [Help]
PACKAGE BODY: APPS.WSH_DOC_SEQ_CTG_S
Source
1 PACKAGE BODY wsh_doc_seq_ctg_s AS
2 -- $Header: WSHVDOCB.pls 115.9 2002/11/12 02:04:50 nparikh ship $
3
4 --------------------
5 -- TYPE DECLARATIONS
6 --------------------
7
8 ------------
9 -- CONSTANTS
10 ------------
11
12 -------------------
13 -- PUBLIC VARIABLES
14 -------------------
15
16 category_exists EXCEPTION;
17
18 -----------------------------------
19 -- PRIVATE PROCEDURES AND FUNCTIONS
20 -----------------------------------
21
22 -- validate_category procedure raises the following user exceptions
23 -----------------------------------------------------------------
24 -- Exception | What it means
25 -----------------------|-----------------------------------------
26 -- category_exists | The category definition (combination
27 -- | of location, document type, document
28 -- | code) is not unique as it is either
29 -- | identical to or is part of another
30 -- | category definition that already exists
31 -----------------------------------------------------------------
32
33 --
34 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DOC_SEQ_CTG_S';
35 --
36 PROCEDURE validate_category
37 ( p_document_type VARCHAR2
38 , p_document_code VARCHAR2
39 , p_location_id NUMBER
40 , p_enabled_flag VARCHAR2
41 , p_rowid VARCHAR2
42 )
43 IS
44 -- cursor to check that the combination of document_type, document_code
45 -- and location is valid (not a duplicate and does not overlap any
46 -- other existing category definition)
47 CURSOR category_csr
48 IS
49 SELECT
50 doc_sequence_category_id
51 FROM
52 wsh_doc_sequence_categories
53 WHERE ((p_rowid IS NULL) OR (p_rowid <> rowid))
54 AND document_type = p_document_type
55 AND NVL(enabled_flag,'N') = 'Y'
56 AND NVL(p_enabled_flag,'N') = 'Y'
57 AND ((location_id = p_location_id AND document_code = p_document_code)
58 OR
59 (location_id = p_location_id AND document_code IS NULL)
60 OR
61 (location_id = p_location_id AND document_code IS NOT NULL
62 AND p_document_code IS NULL)
63 OR
64 (document_code = p_document_code AND location_id IS NOT NULL
65 AND (nvl(p_location_id,-99) = -99) )
66 OR
67 ((nvl(location_id,-99) = -99) AND document_code = p_document_code)
68 OR
69 ((nvl(location_id,-99) = -99) AND document_code IS NULL)
70 OR
71 ((nvl(p_location_id,-99) = -99) AND p_document_code IS NULL));
72
73 category_rec category_csr%rowtype;
74 --
75 l_debug_on BOOLEAN;
76 --
77 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_CATEGORY';
78 --
79 BEGIN
80 --
81 -- Debug Statements
82 --
83 --
84 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
85 --
86 IF l_debug_on IS NULL
87 THEN
88 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
89 END IF;
90 --
91 IF l_debug_on THEN
92 WSH_DEBUG_SV.push(l_module_name);
93 --
94 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
95 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_CODE',P_DOCUMENT_CODE);
96 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
97 WSH_DEBUG_SV.log(l_module_name,'P_ENABLED_FLAG',P_ENABLED_FLAG);
98 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
99 END IF;
100 --
101 OPEN category_csr;
102 FETCH category_csr INTO category_rec;
103 IF category_csr%FOUND
104 THEN
105 CLOSE category_csr;
106 RAISE category_exists;
107 END IF;
108 CLOSE category_csr;
109 --
110 -- Debug Statements
111 --
112 IF l_debug_on THEN
113 WSH_DEBUG_SV.pop(l_module_name);
114 END IF;
115 --
116 END;
117
118
119 ---------------------------
120 -- PROCEDURES AND FUNCTIONS
121 ---------------------------
122
123 PROCEDURE insert_row
124 ( x_rowid IN OUT NOCOPY VARCHAR2
125 , x_doc_sequence_category_id NUMBER
126 , x_location_id NUMBER
127 , x_document_type VARCHAR2
128 , x_document_code VARCHAR2
129 , x_application_id VARCHAR2
130 , x_category_code VARCHAR2
131 , x_name VARCHAR2
132 , x_description VARCHAR2
133 , x_prefix VARCHAR2
134 , x_suffix VARCHAR2
135 , x_delimiter VARCHAR2
136 , x_enabled_flag VARCHAR2
137 , x_created_by NUMBER
138 , x_creation_date DATE
139 , x_last_updated_by NUMBER
140 , x_last_update_date DATE
141 , x_last_update_login NUMBER
142 , x_program_application_id NUMBER
143 , x_program_id NUMBER
144 , x_program_update_date DATE
145 , x_request_id NUMBER
146 , x_attribute_category VARCHAR2
147 , x_attribute1 VARCHAR2
148 , x_attribute2 VARCHAR2
149 , x_attribute3 VARCHAR2
150 , x_attribute4 VARCHAR2
151 , x_attribute5 VARCHAR2
152 , x_attribute6 VARCHAR2
153 , x_attribute7 VARCHAR2
154 , x_attribute8 VARCHAR2
155 , x_attribute9 VARCHAR2
156 , x_attribute10 VARCHAR2
157 , x_attribute11 VARCHAR2
158 , x_attribute12 VARCHAR2
159 , x_attribute13 VARCHAR2
160 , x_attribute14 VARCHAR2
161 , x_attribute15 VARCHAR2
162 )
163 IS
164
165 -- cursor to check successful insert of the row based on primary key
166 CURSOR insert_csr (p_doc_sequence_category_id NUMBER) IS
167 SELECT
168 rowid
169 FROM
170 wsh_doc_sequence_categories
171 WHERE doc_sequence_category_id = p_doc_sequence_category_id;
172
173 -- cursor selects a sequence for the primary key
174 CURSOR sequence_csr IS
175 SELECT wsh_doc_sequence_categories_s.nextval
176 FROM dual;
177
178 -- cursor selects a sequence for category code
179 CURSOR category_code_csr IS
180 SELECT wsh_doc_categories_s.nextval category_code
181 FROM dual;
182
183 l_doc_sequence_category_id NUMBER;
184 l_category_code wsh_doc_sequence_categories.category_code%type;
185 category_code_rec category_code_csr%rowtype;
186
187 --
188 l_debug_on BOOLEAN;
189 --
190 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_ROW';
191 --
192 BEGIN
193
194 ----------------------------------------------------
195 -- First check if category definition exists --
196 -- in Shipping document definition repository. --
197 -- If invalid this will raise category_exists --
198 -- exception --
199 ----------------------------------------------------
200
201 --
202 -- Debug Statements
203 --
204 --
205 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
206 --
207 IF l_debug_on IS NULL
208 THEN
209 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
210 END IF;
211 --
212 IF l_debug_on THEN
213 WSH_DEBUG_SV.push(l_module_name);
214 --
215 WSH_DEBUG_SV.log(l_module_name,'X_ROWID',X_ROWID);
216 WSH_DEBUG_SV.log(l_module_name,'X_DOC_SEQUENCE_CATEGORY_ID',X_DOC_SEQUENCE_CATEGORY_ID);
217 WSH_DEBUG_SV.log(l_module_name,'X_LOCATION_ID',X_LOCATION_ID);
218 WSH_DEBUG_SV.log(l_module_name,'X_DOCUMENT_TYPE',X_DOCUMENT_TYPE);
219 WSH_DEBUG_SV.log(l_module_name,'X_DOCUMENT_CODE',X_DOCUMENT_CODE);
220 WSH_DEBUG_SV.log(l_module_name,'X_APPLICATION_ID',X_APPLICATION_ID);
221 WSH_DEBUG_SV.log(l_module_name,'X_CATEGORY_CODE',X_CATEGORY_CODE);
222 WSH_DEBUG_SV.log(l_module_name,'X_NAME',X_NAME);
223 WSH_DEBUG_SV.log(l_module_name,'X_DESCRIPTION',X_DESCRIPTION);
224 WSH_DEBUG_SV.log(l_module_name,'X_PREFIX',X_PREFIX);
225 WSH_DEBUG_SV.log(l_module_name,'X_SUFFIX',X_SUFFIX);
226 WSH_DEBUG_SV.log(l_module_name,'X_DELIMITER',X_DELIMITER);
227 WSH_DEBUG_SV.log(l_module_name,'X_ENABLED_FLAG',X_ENABLED_FLAG);
228 WSH_DEBUG_SV.log(l_module_name,'X_CREATED_BY',X_CREATED_BY);
229 WSH_DEBUG_SV.log(l_module_name,'X_CREATION_DATE',X_CREATION_DATE);
230 WSH_DEBUG_SV.log(l_module_name,'X_LAST_UPDATED_BY',X_LAST_UPDATED_BY);
231 WSH_DEBUG_SV.log(l_module_name,'X_LAST_UPDATE_DATE',X_LAST_UPDATE_DATE);
232 WSH_DEBUG_SV.log(l_module_name,'X_LAST_UPDATE_LOGIN',X_LAST_UPDATE_LOGIN);
233 WSH_DEBUG_SV.log(l_module_name,'X_PROGRAM_APPLICATION_ID',X_PROGRAM_APPLICATION_ID);
234 WSH_DEBUG_SV.log(l_module_name,'X_PROGRAM_ID',X_PROGRAM_ID);
235 WSH_DEBUG_SV.log(l_module_name,'X_PROGRAM_UPDATE_DATE',X_PROGRAM_UPDATE_DATE);
236 WSH_DEBUG_SV.log(l_module_name,'X_REQUEST_ID',X_REQUEST_ID);
237 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE_CATEGORY',X_ATTRIBUTE_CATEGORY);
238 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE1',X_ATTRIBUTE1);
239 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE2',X_ATTRIBUTE2);
240 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE3',X_ATTRIBUTE3);
241 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE4',X_ATTRIBUTE4);
242 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE5',X_ATTRIBUTE5);
243 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE6',X_ATTRIBUTE6);
244 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE7',X_ATTRIBUTE7);
245 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE8',X_ATTRIBUTE8);
246 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE9',X_ATTRIBUTE9);
247 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE10',X_ATTRIBUTE10);
248 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE11',X_ATTRIBUTE11);
249 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE12',X_ATTRIBUTE12);
250 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE13',X_ATTRIBUTE13);
251 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE14',X_ATTRIBUTE14);
252 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE15',X_ATTRIBUTE15);
253 END IF;
254 --
255 validate_category ( x_document_type
256 , x_document_code
257 , x_location_id
258 , x_enabled_flag
259 , x_rowid );
260
261 ----------------------------------------------------
262 -- If success, create a new category. --
263 -- category_code is a varchar2(30) column and --
264 -- hence the sequence value will be truncated to --
265 -- 25 char and prefixed with Shipping shortname --
266 -- This may have to be replaced by a cleaner --
267 -- solution later --
268 ----------------------------------------------------
269
270 OPEN category_code_csr;
271 FETCH category_code_csr INTO category_code_rec;
272 l_category_code := FND_GLOBAL.application_short_name||': '||
273 substr(to_char(category_code_rec.category_code),1,25);
274 CLOSE category_code_csr;
275
276 ----------------------------------------------------
277 -- Next check if category definition exists --
278 -- in the FND document definition repository --
279 -- In case of failure FND API raises exception --
280 -- APP_EXCEPTIONS.application_exception --
281 -- which is passed to the calling program --
282 ----------------------------------------------------
283
284 BEGIN
285 IF l_debug_on THEN
286 WSH_DEBUG_SV.log(l_module_name,'X_APPLICATION_ID',x_application_id);
287 WSH_DEBUG_SV.log(l_module_name,'CATEGORY CODE',l_category_code);
288 END IF;
289
290 FND_SEQ_CATEGORIES_PKG.check_unique_cat
291 ( x_application_id => x_application_id
292 , x_category_code => l_category_code
293 );
294 EXCEPTION
295 WHEN others THEN
296 RAISE;
297 END;
298
299 OPEN sequence_csr;
300 FETCH sequence_csr INTO l_doc_sequence_category_id;
301 CLOSE sequence_csr;
302
303 INSERT INTO wsh_doc_sequence_categories
304 ( doc_sequence_category_id
305 , location_id
306 , document_type
307 , document_code
308 , application_id
309 , category_code
310 , prefix
311 , suffix
312 , delimiter
313 , enabled_flag
314 , created_by
315 , creation_date
316 , last_updated_by
317 , last_update_date
318 , last_update_login
319 , program_application_id
320 , program_id
321 , program_update_date
322 , request_id
323 , attribute_category
324 , attribute1
325 , attribute2
326 , attribute3
327 , attribute4
328 , attribute5
329 , attribute6
330 , attribute7
331 , attribute8
332 , attribute9
333 , attribute10
334 , attribute11
335 , attribute12
336 , attribute13
337 , attribute14
338 , attribute15
339 ) VALUES (
340 l_doc_sequence_category_id
341 , x_location_id
342 , x_document_type
343 , x_document_code
344 , x_application_id
345 , l_category_code
346 , x_prefix
347 , x_suffix
348 , x_delimiter
349 , x_enabled_flag
350 , x_created_by
351 , x_creation_date
352 , x_last_updated_by
353 , x_last_update_date
354 , x_last_update_login
355 , x_program_application_id
356 , x_program_id
357 , x_program_update_date
358 , x_request_id
359 , x_attribute_category
360 , x_attribute1
361 , x_attribute2
362 , x_attribute3
363 , x_attribute4
364 , x_attribute5
365 , x_attribute6
366 , x_attribute7
367 , x_attribute8
368 , x_attribute9
369 , x_attribute10
370 , x_attribute11
371 , x_attribute12
372 , x_attribute13
373 , x_attribute14
374 , x_attribute15 );
375
376 OPEN insert_csr (l_doc_sequence_category_id);
377 FETCH insert_csr INTO x_rowid;
378 IF insert_csr%NOTFOUND
379 THEN
380 CLOSE insert_csr;
381 RAISE no_data_found;
382 END IF;
383
384 CLOSE insert_csr;
385
386 ----------------------------------------------------
387 -- Once the category is successfully inserted --
388 -- insert a corresponding row in the FND document --
389 -- repository by calling the FND API --
390 ----------------------------------------------------
391
392 IF l_debug_on THEN
393 WSH_DEBUG_SV.log(l_module_name,'Call to Fnd_Seq_Categories X_APPLICATION_ID',x_application_id);
394 WSH_DEBUG_SV.log(l_module_name,'X_CATEGORY_CODE',l_category_code);
395 WSH_DEBUG_SV.log(l_module_name,'NAME',x_name);
396 WSH_DEBUG_SV.log(l_module_name,'DESCRIPTION',x_description);
397 END IF;
398
399 FND_SEQ_CATEGORIES_PKG.insert_cat
400 ( x_application_id => x_application_id
401 , x_category_code => l_category_code
402 , x_category_name => x_name
403 , x_description => x_description
404 , x_table_name => 'WSH_DOCUMENT_INSTANCES'
405 , x_last_updated_by => x_last_updated_by
406 , x_created_by => x_created_by
407 , x_last_update_login => x_last_update_login
408 );
409
410 --
411 -- Debug Statements
412 --
413 IF l_debug_on THEN
414 WSH_DEBUG_SV.pop(l_module_name);
415 END IF;
416 --
417 EXCEPTION
418 WHEN category_exists THEN
419 FND_MESSAGE.set_name('WSH','WSH_DOC_CATEGORY_EXISTS');
420 APP_EXCEPTION.raise_exception;
421 --
422 -- Debug Statements
423 --
424 IF l_debug_on THEN
425 WSH_DEBUG_SV.logmsg(l_module_name,'CATEGORY_EXISTS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
426 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:CATEGORY_EXISTS');
427 END IF;
428 --
429 END insert_row;
430
431 PROCEDURE update_row
432 ( x_rowid VARCHAR2
433 , x_doc_sequence_category_id NUMBER
434 , x_location_id NUMBER
435 , x_document_type VARCHAR2
436 , x_document_code VARCHAR2
437 , x_application_id VARCHAR2
438 , x_category_code VARCHAR2
439 , x_name VARCHAR2
440 , x_description VARCHAR2
441 , x_prefix VARCHAR2
442 , x_suffix VARCHAR2
443 , x_delimiter VARCHAR2
444 , x_enabled_flag VARCHAR2
445 , x_created_by NUMBER
446 , x_creation_date DATE
447 , x_last_updated_by NUMBER
448 , x_last_update_date DATE
449 , x_last_update_login NUMBER
450 , x_program_application_id NUMBER
451 , x_program_id NUMBER
452 , x_program_update_date DATE
453 , x_request_id NUMBER
454 , x_attribute_category VARCHAR2
455 , x_attribute1 VARCHAR2
456 , x_attribute2 VARCHAR2
457 , x_attribute3 VARCHAR2
458 , x_attribute4 VARCHAR2
459 , x_attribute5 VARCHAR2
460 , x_attribute6 VARCHAR2
461 , x_attribute7 VARCHAR2
462 , x_attribute8 VARCHAR2
463 , x_attribute9 VARCHAR2
464 , x_attribute10 VARCHAR2
465 , x_attribute11 VARCHAR2
466 , x_attribute12 VARCHAR2
467 , x_attribute13 VARCHAR2
468 , x_attribute14 VARCHAR2
469 , x_attribute15 VARCHAR2
470 )
471 IS
472 --
473 l_debug_on BOOLEAN;
474 --
475 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ROW';
476 --
477 BEGIN
478
479 ----------------------------------------------------
480 -- First check if category definition exists --
481 -- in Shipping document definition repository. --
482 -- If invalid this will raise category_exists --
483 -- exception --
484 ----------------------------------------------------
485
486 --
487 -- Debug Statements
488 --
489 --
490 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
491 --
492 IF l_debug_on IS NULL
493 THEN
494 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
495 END IF;
496 --
497 IF l_debug_on THEN
498 WSH_DEBUG_SV.push(l_module_name);
499 --
500 WSH_DEBUG_SV.log(l_module_name,'X_ROWID',X_ROWID);
501 WSH_DEBUG_SV.log(l_module_name,'X_DOC_SEQUENCE_CATEGORY_ID',X_DOC_SEQUENCE_CATEGORY_ID);
502 WSH_DEBUG_SV.log(l_module_name,'X_LOCATION_ID',X_LOCATION_ID);
503 WSH_DEBUG_SV.log(l_module_name,'X_DOCUMENT_TYPE',X_DOCUMENT_TYPE);
504 WSH_DEBUG_SV.log(l_module_name,'X_DOCUMENT_CODE',X_DOCUMENT_CODE);
505 WSH_DEBUG_SV.log(l_module_name,'X_APPLICATION_ID',X_APPLICATION_ID);
506 WSH_DEBUG_SV.log(l_module_name,'X_CATEGORY_CODE',X_CATEGORY_CODE);
507 WSH_DEBUG_SV.log(l_module_name,'X_NAME',X_NAME);
508 WSH_DEBUG_SV.log(l_module_name,'X_DESCRIPTION',X_DESCRIPTION);
509 WSH_DEBUG_SV.log(l_module_name,'X_PREFIX',X_PREFIX);
510 WSH_DEBUG_SV.log(l_module_name,'X_SUFFIX',X_SUFFIX);
511 WSH_DEBUG_SV.log(l_module_name,'X_DELIMITER',X_DELIMITER);
512 WSH_DEBUG_SV.log(l_module_name,'X_ENABLED_FLAG',X_ENABLED_FLAG);
513 WSH_DEBUG_SV.log(l_module_name,'X_CREATED_BY',X_CREATED_BY);
514 WSH_DEBUG_SV.log(l_module_name,'X_CREATION_DATE',X_CREATION_DATE);
515 WSH_DEBUG_SV.log(l_module_name,'X_LAST_UPDATED_BY',X_LAST_UPDATED_BY);
516 WSH_DEBUG_SV.log(l_module_name,'X_LAST_UPDATE_DATE',X_LAST_UPDATE_DATE);
517 WSH_DEBUG_SV.log(l_module_name,'X_LAST_UPDATE_LOGIN',X_LAST_UPDATE_LOGIN);
518 WSH_DEBUG_SV.log(l_module_name,'X_PROGRAM_APPLICATION_ID',X_PROGRAM_APPLICATION_ID);
519 WSH_DEBUG_SV.log(l_module_name,'X_PROGRAM_ID',X_PROGRAM_ID);
520 WSH_DEBUG_SV.log(l_module_name,'X_PROGRAM_UPDATE_DATE',X_PROGRAM_UPDATE_DATE);
521 WSH_DEBUG_SV.log(l_module_name,'X_REQUEST_ID',X_REQUEST_ID);
522 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE_CATEGORY',X_ATTRIBUTE_CATEGORY);
523 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE1',X_ATTRIBUTE1);
524 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE2',X_ATTRIBUTE2);
525 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE3',X_ATTRIBUTE3);
526 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE4',X_ATTRIBUTE4);
527 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE5',X_ATTRIBUTE5);
528 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE6',X_ATTRIBUTE6);
529 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE7',X_ATTRIBUTE7);
530 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE8',X_ATTRIBUTE8);
531 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE9',X_ATTRIBUTE9);
532 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE10',X_ATTRIBUTE10);
533 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE11',X_ATTRIBUTE11);
534 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE12',X_ATTRIBUTE12);
535 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE13',X_ATTRIBUTE13);
536 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE14',X_ATTRIBUTE14);
537 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE15',X_ATTRIBUTE15);
538 END IF;
539 --
540 validate_category ( x_document_type
541 , x_document_code
542 , x_location_id
543 , x_enabled_flag
544 , x_rowid );
545
546 ------------------------------------------------------
547 -- update the shipping document category repository --
548 ------------------------------------------------------
549
550 UPDATE wsh_doc_sequence_categories SET
551 doc_sequence_category_id = x_doc_sequence_category_id
552 , location_id = x_location_id
553 , document_type = x_document_type
554 , document_code = x_document_code
555 , application_id = x_application_id
556 , category_code = x_category_code
557 , prefix = x_prefix
558 , suffix = x_suffix
559 , delimiter = x_delimiter
560 , enabled_flag = x_enabled_flag
561 , created_by = x_created_by
562 , creation_date = x_creation_date
563 , last_updated_by = x_last_updated_by
564 , last_update_date = x_last_update_date
565 , last_update_login = x_last_update_login
566 , program_application_id = x_program_application_id
567 , program_id = x_program_id
568 , program_update_date = x_program_update_date
569 , request_id = x_request_id
570 , attribute_category = x_attribute_category
571 , attribute1 = x_attribute1
572 , attribute2 = x_attribute2
573 , attribute3 = x_attribute3
574 , attribute4 = x_attribute4
575 , attribute5 = x_attribute5
576 , attribute6 = x_attribute6
577 , attribute7 = x_attribute7
578 , attribute8 = x_attribute8
579 , attribute9 = x_attribute9
580 , attribute10 = x_attribute10
581 , attribute11 = x_attribute11
582 , attribute12 = x_attribute12
583 , attribute13 = x_attribute13
584 , attribute14 = x_attribute14
585 , attribute15 = x_attribute15
586 WHERE rowid = x_rowid;
587 IF sql%NOTFOUND
588 THEN
589 RAISE no_data_found;
590 END IF;
591
592 --------------------------------------------------
593 -- update the AOL document category repository. --
594 -- This inturn calls is_duplicat_cat function --
595 -- that does the necessary check ( this may be --
596 -- redundant because category definitions are --
597 -- not updateable in shipping doc seq form ) --
598 --------------------------------------------------
599
600 --
601 IF l_debug_on THEN
602 WSH_DEBUG_SV.log(l_module_name,'Call to Fnd_Seq_Categories X_APPLICATION_ID',x_application_id);
603 WSH_DEBUG_SV.log(l_module_name,'X_CATEGORY_CODE',x_category_code);
604 WSH_DEBUG_SV.log(l_module_name,'NAME',x_name);
605 WSH_DEBUG_SV.log(l_module_name,'DESCRIPTION',x_description);
606 END IF;
607 --
608
609 FND_SEQ_CATEGORIES_PKG.update_cat
610 ( x_application_id
611 , x_category_code
612 , x_name
613 , x_description
614 , x_last_updated_by
615 );
616
617 --
618 -- Debug Statements
619 --
620 IF l_debug_on THEN
621 WSH_DEBUG_SV.pop(l_module_name);
622 END IF;
623 --
624 EXCEPTION
625 WHEN category_exists THEN
626 FND_MESSAGE.set_name('WSH','WSH_PACK_CATEGORY_EXISTS');
627 APP_EXCEPTION.raise_exception;
628 --
629 -- Debug Statements
630 --
631 IF l_debug_on THEN
632 WSH_DEBUG_SV.logmsg(l_module_name,'CATEGORY_EXISTS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
633 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:CATEGORY_EXISTS');
634 END IF;
635 --
636 END update_row;
637
638
639 PROCEDURE lock_row
640 ( x_rowid VARCHAR2
641 , x_doc_sequence_category_id NUMBER
642 , x_location_id NUMBER
643 , x_document_type VARCHAR2
644 , x_document_code VARCHAR2
645 , x_application_id VARCHAR2
646 , x_category_code VARCHAR2
647 , x_prefix VARCHAR2
648 , x_suffix VARCHAR2
649 , x_delimiter VARCHAR2
650 , x_enabled_flag VARCHAR2
651 , x_created_by NUMBER
652 , x_creation_date DATE
653 , x_last_updated_by NUMBER
654 , x_last_update_date DATE
655 , x_last_update_login NUMBER
656 , x_program_application_id NUMBER
657 , x_program_id NUMBER
658 , x_program_update_date DATE
659 , x_request_id NUMBER
660 , x_attribute_category VARCHAR2
661 , x_attribute1 VARCHAR2
662 , x_attribute2 VARCHAR2
663 , x_attribute3 VARCHAR2
664 , x_attribute4 VARCHAR2
665 , x_attribute5 VARCHAR2
666 , x_attribute6 VARCHAR2
667 , x_attribute7 VARCHAR2
668 , x_attribute8 VARCHAR2
669 , x_attribute9 VARCHAR2
670 , x_attribute10 VARCHAR2
671 , x_attribute11 VARCHAR2
672 , x_attribute12 VARCHAR2
673 , x_attribute13 VARCHAR2
674 , x_attribute14 VARCHAR2
675 , x_attribute15 VARCHAR2
676 )
677 IS
678 counter NUMBER;
679 CURSOR lock_csr IS
680 SELECT
681 doc_sequence_category_id
682 , location_id
683 , document_type
684 , document_code
685 , application_id
686 , category_code
687 , prefix
688 , suffix
689 , delimiter
690 , enabled_flag
691 , created_by
692 , creation_date
693 , last_updated_by
694 , last_update_date
695 , last_update_login
696 , program_application_id
697 , program_id
698 , program_update_date
699 , request_id
700 , attribute_category
701 , attribute1
702 , attribute2
703 , attribute3
704 , attribute4
705 , attribute5
706 , attribute6
707 , attribute7
708 , attribute8
709 , attribute9
710 , attribute10
711 , attribute11
712 , attribute12
713 , attribute13
714 , attribute14
715 , attribute15
716 FROM
717 wsh_doc_sequence_categories
718 WHERE rowid = x_rowid
719 FOR UPDATE OF doc_sequence_category_id NOWAIT;
720 lock_rec lock_csr%rowtype;
721 --
722 l_debug_on BOOLEAN;
723 --
724 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_ROW';
725 --
726 BEGIN
727 --
728 -- Debug Statements
729 --
730 --
731 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
732 --
733 IF l_debug_on IS NULL
734 THEN
735 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
736 END IF;
737 --
738 IF l_debug_on THEN
739 WSH_DEBUG_SV.push(l_module_name);
740 --
741 WSH_DEBUG_SV.log(l_module_name,'X_ROWID',X_ROWID);
742 WSH_DEBUG_SV.log(l_module_name,'X_DOC_SEQUENCE_CATEGORY_ID',X_DOC_SEQUENCE_CATEGORY_ID);
743 WSH_DEBUG_SV.log(l_module_name,'X_LOCATION_ID',X_LOCATION_ID);
744 WSH_DEBUG_SV.log(l_module_name,'X_DOCUMENT_TYPE',X_DOCUMENT_TYPE);
745 WSH_DEBUG_SV.log(l_module_name,'X_DOCUMENT_CODE',X_DOCUMENT_CODE);
746 WSH_DEBUG_SV.log(l_module_name,'X_APPLICATION_ID',X_APPLICATION_ID);
747 WSH_DEBUG_SV.log(l_module_name,'X_CATEGORY_CODE',X_CATEGORY_CODE);
748 WSH_DEBUG_SV.log(l_module_name,'X_PREFIX',X_PREFIX);
749 WSH_DEBUG_SV.log(l_module_name,'X_SUFFIX',X_SUFFIX);
750 WSH_DEBUG_SV.log(l_module_name,'X_DELIMITER',X_DELIMITER);
751 WSH_DEBUG_SV.log(l_module_name,'X_ENABLED_FLAG',X_ENABLED_FLAG);
752 WSH_DEBUG_SV.log(l_module_name,'X_CREATED_BY',X_CREATED_BY);
753 WSH_DEBUG_SV.log(l_module_name,'X_CREATION_DATE',X_CREATION_DATE);
754 WSH_DEBUG_SV.log(l_module_name,'X_LAST_UPDATED_BY',X_LAST_UPDATED_BY);
755 WSH_DEBUG_SV.log(l_module_name,'X_LAST_UPDATE_DATE',X_LAST_UPDATE_DATE);
756 WSH_DEBUG_SV.log(l_module_name,'X_LAST_UPDATE_LOGIN',X_LAST_UPDATE_LOGIN);
757 WSH_DEBUG_SV.log(l_module_name,'X_PROGRAM_APPLICATION_ID',X_PROGRAM_APPLICATION_ID);
758 WSH_DEBUG_SV.log(l_module_name,'X_PROGRAM_ID',X_PROGRAM_ID);
759 WSH_DEBUG_SV.log(l_module_name,'X_PROGRAM_UPDATE_DATE',X_PROGRAM_UPDATE_DATE);
760 WSH_DEBUG_SV.log(l_module_name,'X_REQUEST_ID',X_REQUEST_ID);
761 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE_CATEGORY',X_ATTRIBUTE_CATEGORY);
762 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE1',X_ATTRIBUTE1);
763 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE2',X_ATTRIBUTE2);
764 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE3',X_ATTRIBUTE3);
765 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE4',X_ATTRIBUTE4);
766 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE5',X_ATTRIBUTE5);
767 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE6',X_ATTRIBUTE6);
768 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE7',X_ATTRIBUTE7);
769 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE8',X_ATTRIBUTE8);
770 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE9',X_ATTRIBUTE9);
771 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE10',X_ATTRIBUTE10);
772 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE11',X_ATTRIBUTE11);
773 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE12',X_ATTRIBUTE12);
774 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE13',X_ATTRIBUTE13);
775 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE14',X_ATTRIBUTE14);
776 WSH_DEBUG_SV.log(l_module_name,'X_ATTRIBUTE15',X_ATTRIBUTE15);
777 END IF;
778 --
779 OPEN lock_csr;
780 FETCH lock_csr INTO lock_rec;
781 IF lock_csr%NOTFOUND
782 THEN
783 CLOSE lock_csr;
784 FND_MESSAGE.set_name ('FND', 'FORM_RECORD_DELETED');
785 APP_EXCEPTION.raise_exception;
786 END IF;
787 CLOSE lock_csr;
788 -- verify the not null columns are identical
789 IF ( lock_rec.doc_sequence_category_id = x_doc_sequence_category_id
790 AND lock_rec.document_type = x_document_type
791 AND lock_rec.application_id = x_application_id
792 AND lock_rec.category_code = x_category_code
793 AND lock_rec.created_by = x_created_by
794 AND lock_rec.creation_date = x_creation_date
795 AND lock_rec.last_updated_by = x_last_updated_by
796 AND lock_rec.last_update_date = x_last_update_date
797 -- verify the nullable columns are either identical or both null
798 AND ((lock_rec.document_code = x_document_code)
799 OR
800 (lock_rec.document_code IS NULL AND x_document_code IS NULL))
801 AND ((lock_rec.location_id = x_location_id)
802 OR
803 (lock_rec.location_id IS NULL AND x_location_id IS NULL))
804 AND ((lock_rec.prefix = x_prefix)
805 OR
806 (lock_rec.prefix IS NULL AND x_prefix IS NULL))
807 AND ((lock_rec.suffix = x_suffix)
808 OR
809 (lock_rec.suffix IS NULL AND x_suffix IS NULL))
810 AND ((lock_rec.delimiter = x_delimiter)
811 OR
812 (lock_rec.delimiter IS NULL AND x_delimiter IS NULL))
813 AND ((lock_rec.enabled_flag = x_enabled_flag)
814 OR
815 (lock_rec.enabled_flag IS NULL AND x_enabled_flag IS NULL))
816 AND ((lock_rec.last_update_login = x_last_update_login)
817 OR
818 (lock_rec.last_update_login IS NULL AND x_last_update_login IS NULL))
819 AND ((lock_rec.program_application_id = x_program_application_id)
820 OR
821 (lock_rec.program_application_id IS NULL
822 AND x_program_application_id IS NULL))
823 AND ((lock_rec.program_id = x_program_id)
824 OR
825 (lock_rec.program_id IS NULL AND x_program_id IS NULL))
826 AND ((lock_rec.request_id = x_request_id)
827 OR
828 (lock_rec.request_id IS NULL AND x_request_id IS NULL))
829 AND ((lock_rec.attribute_category = x_attribute_category)
830 OR
831 (lock_rec.attribute_category IS NULL AND x_attribute_category IS NULL))
832 AND ((lock_rec.attribute1 = x_attribute1)
833 OR
834 (lock_rec.attribute1 IS NULL AND x_attribute1 IS NULL))
835 AND ((lock_rec.attribute2 = x_attribute2)
836 OR
837 (lock_rec.attribute2 IS NULL AND x_attribute2 IS NULL))
838 AND ((lock_rec.attribute3 = x_attribute3)
839 OR
840 (lock_rec.attribute3 IS NULL AND x_attribute3 IS NULL))
841 AND ((lock_rec.attribute4 = x_attribute4)
842 OR
843 (lock_rec.attribute4 IS NULL AND x_attribute4 IS NULL))
844 AND ((lock_rec.attribute5 = x_attribute5)
845 OR
846 (lock_rec.attribute5 IS NULL AND x_attribute5 IS NULL))
847 AND ((lock_rec.attribute6 = x_attribute6)
848 OR
849 (lock_rec.attribute6 IS NULL AND x_attribute6 IS NULL))
850 AND ((lock_rec.attribute7 = x_attribute7)
851 OR
852 (lock_rec.attribute7 IS NULL AND x_attribute7 IS NULL))
853 AND ((lock_rec.attribute8 = x_attribute8)
854 OR
855 (lock_rec.attribute8 IS NULL AND x_attribute8 IS NULL))
856 AND ((lock_rec.attribute9 = x_attribute9)
857 OR
858 (lock_rec.attribute9 IS NULL AND x_attribute9 IS NULL))
859 AND ((lock_rec.attribute10 = x_attribute10)
860 OR
861 (lock_rec.attribute10 IS NULL AND x_attribute10 IS NULL))
862 AND ((lock_rec.attribute11 = x_attribute11)
863 OR
864 (lock_rec.attribute11 IS NULL AND x_attribute11 IS NULL))
865 AND ((lock_rec.attribute12 = x_attribute12)
866 OR
867 (lock_rec.attribute12 IS NULL AND x_attribute12 IS NULL))
868 AND ((lock_rec.attribute13 = x_attribute13)
869 OR
870 (lock_rec.attribute13 IS NULL AND x_attribute13 IS NULL))
871 AND ((lock_rec.attribute14 = x_attribute14)
872 OR
873 (lock_rec.attribute14 IS NULL AND x_attribute14 IS NULL))
874 AND ((lock_rec.attribute15 = x_attribute15)
875 OR
876 (lock_rec.attribute15 IS NULL AND x_attribute15 IS NULL))
877 )
878 THEN
879 --
880 -- Debug Statements
881 --
882 IF l_debug_on THEN
883 WSH_DEBUG_SV.pop(l_module_name);
884 END IF;
885 --
886 return;
887 ELSE
888 FND_MESSAGE.set_name('FND','FORM_RECORD_CHANGED');
889 APP_EXCEPTION.raise_exception;
890 END IF;
891 --
892 -- Debug Statements
893 --
894 IF l_debug_on THEN
895 WSH_DEBUG_SV.pop(l_module_name);
896 END IF;
897 --
898 END lock_row;
899
900 PROCEDURE delete_row ( x_rowid VARCHAR2 )
901 IS
902 --
903 l_debug_on BOOLEAN;
904 --
905 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_ROW';
906 --
907 BEGIN
908 -- currently document categories cannot be deleted.
909 -- They can only be disabled by setting the enabled_flag to 'N'
910 --
911 -- Debug Statements
912 --
913 --
914 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
915 --
916 IF l_debug_on IS NULL
917 THEN
918 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
919 END IF;
920 --
921 IF l_debug_on THEN
922 WSH_DEBUG_SV.push(l_module_name);
923 --
924 WSH_DEBUG_SV.log(l_module_name,'X_ROWID',X_ROWID);
925 END IF;
926 --
927 null;
928 --
929 -- Debug Statements
930 --
931 IF l_debug_on THEN
932 WSH_DEBUG_SV.pop(l_module_name);
933 END IF;
934 --
935 END delete_row;
936
937 END wsh_doc_seq_ctg_s;