1 PACKAGE BODY GMO_DOCUMENT_NUMBERS_PVT
2 /* $Header: GMOVDNMB.pls 120.5 2006/01/19 09:23 swasubra noship $ */
3
4 AS
5
6
7 --This procedure is used to get the org name and transaction name for the specified
8 --org ID and transaction type.
9 PROCEDURE GET_ORG_TRANS_DETAILS
10 (P_ORGANIZATION_ID IN NUMBER,
11 P_TRANSACTION_TYPE IN VARCHAR2,
12 X_ORGANIZATION_NAME OUT NOCOPY VARCHAR2,
13 X_TRANSACTION_NAME OUT NOCOPY VARCHAR2)
14
15 IS
16
17 --This cursor is used to obtain the organization name for the specified organization ID.
18 --This cursor will be used only if no document number exists for the specified organization ID and transaction type.
19 CURSOR GET_ORG_NAME_C IS
20 SELECT DISTINCT(OV.ORGANIZATION_CODE || ' ' || OV.ORGANIZATION_NAME)
21 FROM ORG_ACCESS_VIEW OV
22 WHERE OV.ORGANIZATION_ID = P_ORGANIZATION_ID;
23
24 --This cursor is used to obtain the transaction name for the specified transaction type code.
25 --This cursor will be used only if there exists no document numbers for the specified organization ID and transaction type.
26 CURSOR GET_TRANS_NAME_C IS
27 SELECT LK.MEANING
28 FROM FND_LOOKUP_VALUES_VL LK
29 WHERE LK.LOOKUP_TYPE = 'GMO_TRANSACTION_TYPES'
30 AND LK.LOOKUP_CODE = P_TRANSACTION_TYPE;
31
32
33 BEGIN
34
35 --Obtain the organization name only if organization ID exists.
36 IF P_ORGANIZATION_ID IS NOT NULL THEN
37
38 --Open the cursor that obtains the organization name.
39 OPEN GET_ORG_NAME_C;
40
41 --Fetch the organization name value.
42 FETCH GET_ORG_NAME_C INTO X_ORGANIZATION_NAME;
43
44 --If no row was found, then set the specified organization ID as tbe organization name.
45 IF GET_ORG_NAME_C%NOTFOUND THEN
46 X_ORGANIZATION_NAME := P_ORGANIZATION_ID;
47 END IF;
48
49 --CLose the cursor.
50 CLOSE GET_ORG_NAME_C;
51
52 ELSE
53
54 --The organization ID is null.
55 --Hence set organization name to null.
56 X_ORGANIZATION_NAME := NULL;
57
58 END IF;
59
60 --Open the cursor that obtains the transaction name.
61 OPEN GET_TRANS_NAME_C;
62
63 --Fetch the transaction name value.
64 FETCH GET_TRANS_NAME_C INTO X_TRANSACTION_NAME;
65
66 --If no row was found, then set the specified transaction type code and the transaction name;
67 IF GET_TRANS_NAME_C%NOTFOUND THEN
68 X_TRANSACTION_NAME := P_TRANSACTION_TYPE;
69 END IF;
70
71 --Close the cursor;
72 CLOSE GET_TRANS_NAME_C;
73
74 END GET_ORG_TRANS_DETAILS;
75
76
77 --This PROCEDURE is used to obtain the document number type associated with the
78 --specified organization ID and transaction type.
79 PROCEDURE GET_DOCUMENT_NUMBER_TYPE
80 (P_ORGANIZATION_ID IN NUMBER,
81 P_TRANSACTION_TYPE IN VARCHAR2,
82 P_INIT_MSG_LIST IN VARCHAR2,
83 X_DOCUMENT_NUMBER_TYPE OUT NOCOPY VARCHAR2,
84 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
85 X_MSG_COUNT OUT NOCOPY NUMBER,
86 X_MSG_DATA OUT NOCOPY VARCHAR2)
87
88 IS
89
90 --The API name.
91 L_API_NAME CONSTANT VARCHAR2(30) := 'GET_DOCUMENT_NUMBER_TYPE';
92
93 --This variable would hold the value of the organization name returned if no document number exists for the specified
94 --organization ID and transaction type code.
95 L_INVALID_ORG_NAME VARCHAR2(250);
96
97 --This variable would hold transaction name returned if no document number exists for the specified
98 --organization ID and transaction type code.
99 L_INVALID_TRANS_NAME VARCHAR2(100);
100
101 --This variable is used to add the return messages into the message queue.
102 L_MESG_TEXT VARCHAR2(2000);
103
104 --This cursor obtains the document number type for the specified organization ID and transaction type.
105 CURSOR GET_DOC_NUM_TYPE_C IS
106 SELECT DOC_TYPE
107 FROM GMO_DOCUMENT_NUMBERS
108 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
109 AND TRANSACTION_TYPE = P_TRANSACTION_TYPE;
110
111 --This cursor obtains the document number type for the specified transaction type which is not bound to any organization.
112 CURSOR GET_DOC_NUM_TYPE_NO_ORG_C IS
113 SELECT DOC_TYPE
114 FROM GMO_DOCUMENT_NUMBERS
115 WHERE ORGANIZATION_ID IS NULL
116 AND TRANSACTION_TYPE = P_TRANSACTION_TYPE;
117
118 --This exception will be raised if no document number exists for the specified organization ID and transaction type.
119 INVALID_PARAMS_ERROR EXCEPTION;
120
121 BEGIN
122
123
124 --Initialize the message list if specified so.
125 IF FND_API.TO_BOOLEAN( P_INIT_MSG_LIST ) THEN
126
127 FND_MSG_PUB.INITIALIZE;
128
129 END IF;
130
131 --The cursor is chosen based on the availability of organization ID.
132 IF P_ORGANIZATION_ID IS NOT NULL THEN
133
134 --Open the cursor that obtains the document number type for a transaction type bound to a organization.
135 OPEN GET_DOC_NUM_TYPE_C;
136
137 --Fetch the document type value.
138 FETCH GET_DOC_NUM_TYPE_C INTO X_DOCUMENT_NUMBER_TYPE;
139
140 --If no row was found, then no document number exists for the specified organization ID and transaction type.
141 IF GET_DOC_NUM_TYPE_C%NOTFOUND THEN
142
143 --Obtain the organization and transaction names.
144 GET_ORG_TRANS_DETAILS
145 (P_ORGANIZATION_ID => P_ORGANIZATION_ID,
146 P_TRANSACTION_TYPE => P_TRANSACTION_TYPE,
147 X_ORGANIZATION_NAME => L_INVALID_ORG_NAME,
148 X_TRANSACTION_NAME => L_INVALID_TRANS_NAME);
149
150 --Close the cursor used to obtain the document number type.
151 CLOSE GET_DOC_NUM_TYPE_C;
152
153 RAISE INVALID_PARAMS_ERROR;
154
155 END IF;
156
157 --Close the cursor.
158 CLOSE GET_DOC_NUM_TYPE_C;
159
160 ELSE
161
162 --Open the cursor that obtains the document number type for a transaction type that is not bound to any organization.
163 OPEN GET_DOC_NUM_TYPE_NO_ORG_C;
164
165 --Fetch the document type value.
166 FETCH GET_DOC_NUM_TYPE_NO_ORG_C INTO X_DOCUMENT_NUMBER_TYPE;
167 --If no row was found, then no document number exists for the specified organization ID and transaction type.
168
169 IF GET_DOC_NUM_TYPE_NO_ORG_C%NOTFOUND THEN
170
171 --Obtain the transaction name.
172 GET_ORG_TRANS_DETAILS
173 (P_ORGANIZATION_ID => P_ORGANIZATION_ID,
174 P_TRANSACTION_TYPE => P_TRANSACTION_TYPE,
175 X_ORGANIZATION_NAME => L_INVALID_ORG_NAME,
176 X_TRANSACTION_NAME => L_INVALID_TRANS_NAME);
177
178 --Close the cursor used to obtain the document number type.
179 CLOSE GET_DOC_NUM_TYPE_NO_ORG_C;
180
181 RAISE INVALID_PARAMS_ERROR;
182
183 END IF;
184
185 --Close the cursor.
186 CLOSE GET_DOC_NUM_TYPE_NO_ORG_C;
187
188 END IF;
189
190 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
191
192 --Get the message count.
193 --If count is 1, then get the message data.
194 FND_MSG_PUB.COUNT_AND_GET
195 (P_COUNT => X_MSG_COUNT,
196 P_DATA => X_MSG_DATA);
197
198 EXCEPTION
199
200
201 WHEN INVALID_PARAMS_ERROR THEN
202
203 X_DOCUMENT_NUMBER_TYPE := NULL;
204 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR ;
205
206 IF L_INVALID_ORG_NAME IS NOT NULL THEN
207
208 FND_MESSAGE.SET_NAME('GMO','GMO_DOC_NUM_PARAMS_ERR');
209 FND_MESSAGE.SET_TOKEN('ORG',L_INVALID_ORG_NAME);
210 FND_MESSAGE.SET_TOKEN('TRANS',L_INVALID_TRANS_NAME);
211
212 ELSE
213
214 FND_MESSAGE.SET_NAME('GMO','GMO_DOC_NUM_PARAMS_ERR1');
215 FND_MESSAGE.SET_TOKEN('TRANS',L_INVALID_TRANS_NAME);
216
217 END IF;
218
219 FND_MSG_PUB.ADD;
220
221 FND_MSG_PUB.COUNT_AND_GET
222 (P_COUNT => X_MSG_COUNT,
223 P_DATA => X_MSG_DATA);
224
225 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
226 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
227 'gmo.plsql.GMO_DOCUMENT_NUMBERS_PVT.GET_DOCUMENT_NUMBER_TYPE',
228 FALSE);
229 END IF;
230
231 WHEN OTHERS THEN
232
233
234 IF GET_DOC_NUM_TYPE_C%ISOPEN THEN
235
236 --Close the cursor.
237 CLOSE GET_DOC_NUM_TYPE_C;
238
239 END IF;
240
241
242 IF GET_DOC_NUM_TYPE_NO_ORG_C%ISOPEN THEN
243
244 --Close the cursor.
245 CLOSE GET_DOC_NUM_TYPE_NO_ORG_C;
246
247 END IF;
248
249 X_DOCUMENT_NUMBER_TYPE := NULL;
250 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
251
252
253 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
254 FND_MSG_PUB.ADD_EXC_MSG
255 (G_PKG_NAME,
256 L_API_NAME);
257 END IF;
258
259 FND_MSG_PUB.COUNT_AND_GET
260 (P_COUNT => X_MSG_COUNT,
261 P_DATA => X_MSG_DATA);
262
263 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
264 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
265 'gmo.plsql.GMO_DOCUMENT_NUMBERS_PVT.GET_DOCUMENT_NUMBER_TYPE',
266 FALSE);
267 END IF;
268
269 END GET_DOCUMENT_NUMBER_TYPE;
270
271
272 --This PROCEDURE is used to obtain the next document number in sequence associated with the
273 --specified organization ID and transaction type.
274 PROCEDURE GET_NEXT_VALUE
275 (P_ORGANIZATION_ID IN NUMBER,
276 P_TRANSACTION_TYPE IN VARCHAR2,
277 P_INIT_MSG_LIST IN VARCHAR2,
278 X_VALUE OUT NOCOPY VARCHAR2,
279 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
280 X_MSG_COUNT OUT NOCOPY NUMBER,
281 X_MSG_DATA OUT NOCOPY VARCHAR2)
282
283 IS
284
285 --The API name.
286 L_API_NAME CONSTANT VARCHAR2(30) := 'GET_NEXT_VALUE';
287
288 --This variable is used to add the return messages into the message queue.
289 L_MESG_TEXT VARCHAR2(2000);
290
291 L_CURRENTVAL NUMBER;
292
293 L_INCREMENT NUMBER;
294
295 L_LENGTH NUMBER;
296
297 L_DOC_TYPE VARCHAR2(1);
298
299 L_ZERO_PAD_OPTION VARCHAR2(1);
300
301 L_PREFIX VARCHAR2(10);
302
303 L_SUFFIX VARCHAR2(10);
304
305 L_SEPARATOR VARCHAR2(1);
306
307 L_START NUMBER;
308
309 L_NEXTVAL NUMBER;
310
311 L_NO_OF_DIGITS NUMBER;
312
313 L_IDENTIFIER NUMBER;
314
315 L_NO_OF_ZEROS NUMBER;
316
317 L_INVALID_ORG_NAME VARCHAR2(250);
318
319 L_INVALID_TRANS_NAME VARCHAR2(100);
320
321 --This exception if no document number exists for the specified organization ID and transaction type.
322 INVALID_PARAMS_ERROR EXCEPTION;
323
324 --This exception is raised if the document identified by the orgsanization ID and transaction type has reached its
325 --maximum allowed value.
326 MAX_VALUE_ERROR EXCEPTION;
327
328 --This exception is raised if the type of the document number identified by the organization ID and transaction type is
329 --set to manual.
330 DOC_TYPE_ERROR EXCEPTION;
331
332 --This cursor obtains the details of the document number associated with the organization ID and
333 --transaction type.
334 CURSOR GET_DOC_NUM_DETAILS_C IS
335 SELECT DOC_TYPE,
336 DOC_ZERO_PAD,
337 DOC_START,
338 DOC_CURRENTVAL,
339 DOC_INCREMENT,
340 DOC_LENGTH,
341 DOC_PREFIX,
342 DOC_SUFFIX,
343 DOC_SEPARATOR
344 FROM GMO_DOCUMENT_NUMBERS
345 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
346 AND TRANSACTION_TYPE = P_TRANSACTION_TYPE;
347
348 --This cursor obtains the details of the document number associated with the
349 --transaction type with no organization.
350 CURSOR GET_DOC_NUM_DETAILS_NO_ORG_C IS
351 SELECT DOC_TYPE,
352 DOC_ZERO_PAD,
353 DOC_START,
354 DOC_CURRENTVAL,
355 DOC_INCREMENT,
356 DOC_LENGTH,
357 DOC_PREFIX,
358 DOC_SUFFIX,
359 DOC_SEPARATOR
360 FROM GMO_DOCUMENT_NUMBERS
361 WHERE ORGANIZATION_ID IS NULL
362 AND TRANSACTION_TYPE = P_TRANSACTION_TYPE;
363
364 PRAGMA AUTONOMOUS_TRANSACTION;
365
366 BEGIN
367
368 --Initialize the message list if specified so.
369 IF FND_API.TO_BOOLEAN( P_INIT_MSG_LIST ) THEN
370
371 FND_MSG_PUB.INITIALIZE;
372
373 END IF;
374
375 --Check if organization ID exists.
376 IF P_ORGANIZATION_ID IS NOT NULL THEN
377
378 --Open the cursor.
379 OPEN GET_DOC_NUM_DETAILS_C;
380
381 --Fetch the document details.
382 FETCH GET_DOC_NUM_DETAILS_C
383 INTO L_DOC_TYPE,
384 L_ZERO_PAD_OPTION,
385 L_START,
386 L_CURRENTVAL,
387 L_INCREMENT,
388 L_LENGTH,
389 L_PREFIX,
390 L_SUFFIX,
391 L_SEPARATOR;
392
393
394 IF GET_DOC_NUM_DETAILS_C%NOTFOUND THEN
395
396 --Obtain the organization and transaction names.
397 GET_ORG_TRANS_DETAILS
398 (P_ORGANIZATION_ID => P_ORGANIZATION_ID,
399 P_TRANSACTION_TYPE => P_TRANSACTION_TYPE,
400 X_ORGANIZATION_NAME => L_INVALID_ORG_NAME,
401 X_TRANSACTION_NAME => L_INVALID_TRANS_NAME);
402
403 --Close the cursor.
404 CLOSE GET_DOC_NUM_DETAILS_C;
405
406 --No document number was found. Hence raise an exception.
407 RAISE INVALID_PARAMS_ERROR;
408
409 END IF;
410
411 --Close the cursor.
412 CLOSE GET_DOC_NUM_DETAILS_C;
413
414 ELSE
415
416 --Open the cursor.
417 OPEN GET_DOC_NUM_DETAILS_NO_ORG_C;
418
419 --Fetch the document details.
420 FETCH GET_DOC_NUM_DETAILS_NO_ORG_C
421 INTO L_DOC_TYPE,
422 L_ZERO_PAD_OPTION,
423 L_START,
424 L_CURRENTVAL,
425 L_INCREMENT,
426 L_LENGTH,
427 L_PREFIX,
428 L_SUFFIX,
429 L_SEPARATOR;
430
431
432 IF GET_DOC_NUM_DETAILS_NO_ORG_C%NOTFOUND THEN
433
434 --Obtain the organization and transaction names.
435 GET_ORG_TRANS_DETAILS
436 (P_ORGANIZATION_ID => P_ORGANIZATION_ID,
437 P_TRANSACTION_TYPE => P_TRANSACTION_TYPE,
438 X_ORGANIZATION_NAME => L_INVALID_ORG_NAME,
439 X_TRANSACTION_NAME => L_INVALID_TRANS_NAME);
440
441 --Close the cursor.
442 CLOSE GET_DOC_NUM_DETAILS_NO_ORG_C;
443
444 --No document number was found. Hence raise an exception.
445 RAISE INVALID_PARAMS_ERROR;
446
447 END IF;
448
449 --Close the cursor.
450 CLOSE GET_DOC_NUM_DETAILS_NO_ORG_C;
451
452 END IF;
453
454
455 IF L_DOC_TYPE = GMO_DOCUMENT_NUMBERS_GRP.G_DOC_TYPE_MANUAL THEN
456
457 --The document type is set to Manual.
458
459 --Obtain the organization and transaction names.
460 GET_ORG_TRANS_DETAILS
461 (P_ORGANIZATION_ID => P_ORGANIZATION_ID,
462 P_TRANSACTION_TYPE => P_TRANSACTION_TYPE,
463 X_ORGANIZATION_NAME => L_INVALID_ORG_NAME,
464 X_TRANSACTION_NAME => L_INVALID_TRANS_NAME);
465
466 --Hence raise an exception.
467 RAISE DOC_TYPE_ERROR;
468
469 ELSE
470
471 --Set the value to an empty field.
472 X_VALUE := '';
473
474 --If the prefix exists then append the same to the next value
475 IF LENGTH(L_PREFIX) > 0 THEN
476 X_VALUE := X_VALUE || L_PREFIX;
477
478 --If the separator exists then append the same to the next value.
479 IF LENGTH(L_SEPARATOR) > 0 THEN
480 X_VALUE := X_VALUE || L_SEPARATOR;
481 END IF;
482
483 END IF;
484
485
486 --If the current val is -1 then the sequence has not started.
487 IF L_CURRENTVAL = -1 THEN
488
489 --The sequencing has not started for this document number.
490 --Set the next val sequence to the start value.
491 L_NEXTVAL := L_START;
492
493 ELSE
494
495 --The sequencing has started for this document number.
496 --Set the next val sequence by incrementing the current val appropriately.
497 L_NEXTVAL := L_CURRENTVAL + L_INCREMENT;
498
499 END IF;
500
501 --Count the number of digits used in the next val sequence if length value is greater than zero.
502 IF L_LENGTH > 0 THEN
503 L_NO_OF_DIGITS := 0;
504
505 L_IDENTIFIER := L_NEXTVAL;
506
507 WHILE L_IDENTIFIER >= 1 LOOP
508 L_NO_OF_DIGITS := L_NO_OF_DIGITS + 1;
509 L_IDENTIFIER := L_IDENTIFIER / 10;
510 END LOOP;
511
512 IF L_NO_OF_DIGITS > L_LENGTH THEN
513
514 --Obtain the organization and transaction names.
515 GET_ORG_TRANS_DETAILS
516 (P_ORGANIZATION_ID => P_ORGANIZATION_ID,
517 P_TRANSACTION_TYPE => P_TRANSACTION_TYPE,
518 X_ORGANIZATION_NAME => L_INVALID_ORG_NAME,
519 X_TRANSACTION_NAME => L_INVALID_TRANS_NAME);
520
521 --The document number has reached its maximum value. Hence raise an exception.
522 RAISE MAX_VALUE_ERROR;
523
524 ELSE
525
526 --If the zero pad is used then set the zero padding appropriately.
527 IF L_ZERO_PAD_OPTION = GMO_DOCUMENT_NUMBERS_GRP.G_ZERO_PAD_YES THEN
528
529 L_NO_OF_ZEROS := L_LENGTH - L_NO_OF_DIGITS;
530
531 FOR i IN 1..L_NO_OF_ZEROS LOOP
532
533 X_VALUE := X_VALUE || '0';
534
535 END LOOP;
536 END IF;
537 END IF;
538 END IF;
539
540 --Append the computed next val sequence to the next value variable.
541 X_VALUE := X_VALUE || L_NEXTVAL;
542
543 --Append the suffix if it exists.
544 IF LENGTH(L_SUFFIX) > 0 THEN
545
546 --Append the separator if it exists.
547 IF LENGTH(L_SEPARATOR) > 0 THEN
548 X_VALUE := X_VALUE || L_SEPARATOR;
549 END IF;
550
551 X_VALUE := X_VALUE || L_SUFFIX;
552
553 END IF;
554
555 --Update the table with the newly computed next val sequence.
556 IF P_ORGANIZATION_ID IS NOT NULL THEN
557
558 UPDATE GMO_DOCUMENT_NUMBERS
559 SET DOC_CURRENTVAL = L_NEXTVAL,
560 LAST_UPDATE_DATE = SYSDATE,
561 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
562 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
563 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
564 AND TRANSACTION_TYPE = P_TRANSACTION_TYPE;
565
566 ELSE
567
568 UPDATE GMO_DOCUMENT_NUMBERS
569 SET DOC_CURRENTVAL = L_NEXTVAL,
570 LAST_UPDATE_DATE = SYSDATE,
571 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
572 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
573 WHERE ORGANIZATION_ID IS NULL
574 AND TRANSACTION_TYPE = P_TRANSACTION_TYPE;
575
576 END IF;
577
578 --Commit the transaction.
579 COMMIT;
580
581 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
582
583 END IF;
584
585
586 EXCEPTION
587
588 WHEN INVALID_PARAMS_ERROR THEN
589 ROLLBACK;
590 X_VALUE := NULL;
591 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
592
593 IF L_INVALID_ORG_NAME IS NOT NULL THEN
594
595 FND_MESSAGE.SET_NAME('GMO','GMO_DOC_NUM_PARAMS_ERR');
596 FND_MESSAGE.SET_TOKEN('ORG',L_INVALID_ORG_NAME);
597 FND_MESSAGE.SET_TOKEN('TRANS',L_INVALID_TRANS_NAME);
598
599 ELSE
600
601 FND_MESSAGE.SET_NAME('GMO','GMO_DOC_NUM_PARAMS_ERR1');
602 FND_MESSAGE.SET_TOKEN('TRANS',L_INVALID_TRANS_NAME);
603
604 END IF;
605
606 FND_MSG_PUB.ADD;
607
608 FND_MSG_PUB.COUNT_AND_GET
609 (P_COUNT => X_MSG_COUNT,
610 P_DATA => X_MSG_DATA);
611
612 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
613 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
614 'gmo.plsql.GMO_DOCUMENT_NUMBERS_PVT.GET_NEXT_VALUE',
615 FALSE);
616 END IF;
617
618 WHEN DOC_TYPE_ERROR THEN
619
620 ROLLBACK;
621
622 X_VALUE := NULL;
623 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
624
625 IF L_INVALID_ORG_NAME IS NOT NULL THEN
626
627 FND_MESSAGE.SET_NAME('GMO','GMO_DOC_NUM_DOC_TYPE_ERR');
628 FND_MESSAGE.SET_TOKEN('ORG',L_INVALID_ORG_NAME);
629 FND_MESSAGE.SET_TOKEN('TRANS',L_INVALID_TRANS_NAME);
630
631 ELSE
632
633 FND_MESSAGE.SET_NAME('GMO','GMO_DOC_NUM_DOC_TYPE_ERR1');
634 FND_MESSAGE.SET_TOKEN('TRANS',L_INVALID_TRANS_NAME);
635
636 END IF;
637
638
639 FND_MSG_PUB.ADD;
640
641 FND_MSG_PUB.COUNT_AND_GET
642 (P_COUNT => X_MSG_COUNT,
643 P_DATA => X_MSG_DATA);
644
645 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
646 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
647 'gmo.plsql.GMO_DOCUMENT_NUMBERS_PVT.GET_NEXT_VALUE',
648 FALSE);
649 END IF;
650
651 WHEN MAX_VALUE_ERROR THEN
652
653 ROLLBACK;
654 X_VALUE := NULL;
655 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
656
657 IF L_INVALID_ORG_NAME IS NOT NULL THEN
658
659 FND_MESSAGE.SET_NAME('GMO','GMO_DOC_NUM_MAX_VALUE_ERR');
660 FND_MESSAGE.SET_TOKEN('ORG',L_INVALID_ORG_NAME);
661 FND_MESSAGE.SET_TOKEN('TRANS',L_INVALID_TRANS_NAME);
662
663 ELSE
664
665 FND_MESSAGE.SET_NAME('GMO','GMO_DOC_NUM_MAX_VALUE_ERR1');
666 FND_MESSAGE.SET_TOKEN('TRANS',L_INVALID_TRANS_NAME);
667
668 END IF;
669
670 FND_MSG_PUB.ADD;
671
672 FND_MSG_PUB.COUNT_AND_GET
673 (P_COUNT => X_MSG_COUNT,
674 P_DATA => X_MSG_DATA);
675
676 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
677 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
678 'gmo.plsql.GMO_DOCUMENT_NUMBERS_PVT.GET_NEXT_VALUE',
679 FALSE);
680 END IF;
681
682 WHEN OTHERS THEN
683 ROLLBACK;
684
685 X_VALUE := NULL;
686 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR ;
687
688 IF GET_DOC_NUM_DETAILS_C%ISOPEN THEN
689
690 --Close the cursor.
691 CLOSE GET_DOC_NUM_DETAILS_C;
692
693 END IF;
694
695
696 IF GET_DOC_NUM_DETAILS_NO_ORG_C%ISOPEN THEN
697
698 --Close the cursor.
699 CLOSE GET_DOC_NUM_DETAILS_NO_ORG_C;
700
701 END IF;
702
703
704 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
705 FND_MSG_PUB.ADD_EXC_MSG
706 (G_PKG_NAME,
707 L_API_NAME);
708 END IF;
709
710 FND_MSG_PUB.COUNT_AND_GET
711 (P_COUNT => X_MSG_COUNT,
712 P_DATA => X_MSG_DATA);
713
714 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
715 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
716 'gmo.plsql.GMO_DOCUMENT_NUMBERS_PVT.GET_NEXT_VALUE',
717 FALSE);
718 END IF;
719
720 END GET_NEXT_VALUE;
721
722 --This procedure is used to check if a document number entry exists for the specified
723 --organization and transaction type.
724 PROCEDURE DOES_DOCUMENT_NUMBER_EXIST
725 (P_ORGANIZATION_ID IN NUMBER,
726 P_TRANSACTION_TYPE IN VARCHAR2,
727 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
728 X_DOCUMENT_NUMBER_EXISTS OUT NOCOPY VARCHAR2,
729 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
730 X_MSG_COUNT OUT NOCOPY NUMBER,
731 X_MSG_DATA OUT NOCOPY VARCHAR2)
732
733 IS
734
735 --This is a sandbox variable to store a count of the document numbers that exist
736 --for the specified organization ID and transaction type.
737 --Its value must be either 0 or 1.
738 L_COUNT NUMBER;
739
740 --The API name.
741 L_API_NAME CONSTANT VARCHAR2(30) := 'DOES_DOCUMENT_NUMBER_EXIST';
742
743 BEGIN
744
745
746 --Obtain a count of the document numbers that exist based on the specified
747 --organization ID and transaction type.
748 IF P_ORGANIZATION_ID IS NULL THEN
749
750 SELECT COUNT(*) INTO L_COUNT
751 FROM GMO_DOCUMENT_NUMBERS
752 WHERE ORGANIZATION_ID IS NULL
753 AND TRANSACTION_TYPE = P_TRANSACTION_TYPE;
754
755 ELSE
756
757 SELECT COUNT(*) INTO L_COUNT
758 FROM GMO_DOCUMENT_NUMBERS
759 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
760 AND TRANSACTION_TYPE = P_TRANSACTION_TYPE;
761
762 END IF;
763
764 --Return the status value based on the count parameter.
765 IF L_COUNT > 0 THEN
766 X_DOCUMENT_NUMBER_EXISTS := FND_API.G_TRUE;
767 ELSE
768 X_DOCUMENT_NUMBER_EXISTS := FND_API.G_FALSE;
769 END IF;
770
771 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
772
773 EXCEPTION
774
775 WHEN OTHERS THEN
776
777 X_DOCUMENT_NUMBER_EXISTS := FND_API.G_FALSE;
778
779 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR ;
780
781 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
782 FND_MSG_PUB.ADD_EXC_MSG
783 (G_PKG_NAME,
784 L_API_NAME);
785 END IF;
786
787 FND_MSG_PUB.COUNT_AND_GET
788 (P_COUNT => X_MSG_COUNT,
789 P_DATA => X_MSG_DATA);
790
791 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
792 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
793 'gmo.plsql.GMO_DOCUMENT_NUMBERS_PVT.DOES_DOCUMENT_NUMBER_EXIST',
794 FALSE);
795 END IF;
796
797 END DOES_DOCUMENT_NUMBER_EXIST;
798
799 END GMO_DOCUMENT_NUMBERS_PVT;
[Home] [Help]