1 package body FND_SEQNUM as
2 /* $Header: AFSQNUMB.pls 120.2 2005/11/03 08:12:59 fskinner ship $ */
3
4
5 /* Private structure for storing a cache of the info retrieved by get_seq_info()
6 it is likey that during a given session this function may be called 100's of
7 times with the same input parameters - thus the same Doc_Seq info */
8
9 TYPE t_SeqInfo IS RECORD (
10 initFlag boolean := FALSE,
11 app_id number,
12 cat_code varchar2(30),
13 sob_id number,
14 met_code char,
15 docseq_id number,
16 docseq_type char,
17 docseq_name varchar2(30),
18 db_seq_name varchar2(30),
19 seq_ass_id number,
20 prd_tab_name varchar2(30),
21 aud_tab_name varchar2(30),
22 msg_flag char,
23 startDate date,
24 endDate date,
25 retStat number );
26
27 v_SeqInfoCache t_SeqInfo; -- declare my global cache buffer
28
29 function get_next_sequence (appid in number,
30 cat_code in varchar2,
31 sobid in number,
32 met_code in char,
33 trx_date in date,
34 dbseqnm in out nocopy varchar2,
35 dbseqid in out nocopy integer) return number
36 is
37 t varchar2(1);
38 seqval integer; /* the next sequence value */
39 seqassid integer; /* sequence assignment id */
40 begin
41
42 /* Get DB_SEQUENCE_NAME, DOC_SEQUENCE_ID and SEQUENCE_ASSIGNMENT_ID. */
43 get_seq_name(appid,cat_code,sobid,met_code,trx_date,
44 dbseqnm,dbseqid,seqassid);
45
46 /* Bug 701013 - change where clause to use DOC_SEQUENCE_ID instead of
47 DB_SEQUENCE_NAME user and gapless don't use db sequences */
48 select type into t
49 from FND_DOCUMENT_SEQUENCES
50 where DOC_SEQUENCE_ID = dbseqid;
51
52 /* Bug 701013 - added 't = G' to if statement to get Gapless */
53 if ( (t = 'U') OR (t = 'G') ) then
54 seqval := get_next_user_sequence(0,seqassid,dbseqid);
55 elsif (t = 'A') then
56 seqval := get_next_auto_seq(dbseqnm);
57 end if;
58
59 return(seqval);
60
61 end get_next_sequence;
62
63 procedure get_seq_name (appid in number,
64 cat_code in varchar2,
65 sobid in number,
66 met_code in char,
67 trx_date in date,
68 dbseqnm out nocopy varchar2,
69 dbseqid out nocopy integer,
70 seqassid out nocopy integer)
71 is
72 begin
73
74 SELECT SEQ.DB_SEQUENCE_NAME,
75 SEQ.DOC_SEQUENCE_ID,
76 SA.doc_sequence_assignment_id
77 INTO dbseqnm, dbseqid, seqassid
78 FROM FND_DOCUMENT_SEQUENCES SEQ,
79 FND_DOC_SEQUENCE_ASSIGNMENTS SA
80 WHERE SEQ.DOC_SEQUENCE_ID = SA.DOC_SEQUENCE_ID
81 AND SA.APPLICATION_ID = appid
82 AND SA.CATEGORY_CODE = cat_code
83 AND (SA.METHOD_CODE = met_code or SA.METHOD_CODE is NULL)
84 AND (SA.SET_OF_BOOKS_ID = sobid or SA.SET_OF_BOOKS_ID is NULL)
85 /* bug 1019289, 1295363 - change between to add .9999 to end date -- also removed extra
86 todate() tochar() and work with straight dates */
87 AND trx_date between SA.START_DATE and nvl( SA.END_DATE + .9999, trx_date + .9999 );
88 end get_seq_name;
89
90 /*
91 * This function gets the nextval for a db sequence given the seq name.
92 * Fix Bug 1073084 - changed to use EXECUTE IMMEDIATE instead of dbms_sql.
93 */
94 function get_next_auto_seq (dbseqnm in varchar2) return number
95 is
96 v_proc_stmt varchar2(100);
97 v_nextVal integer;
98
99 begin
100 v_proc_stmt := 'select ' || dbseqnm || '.nextval ' || 'into :next_val from sys.dual';
101
102 EXECUTE IMMEDIATE v_proc_stmt INTO v_nextVal;
103 return( v_nextVal );
104
105 exception
106 when others then
107 fnd_message.set_name( 'FND', 'SQL-GENERIC ERROR' );
108 fnd_message.set_token( 'ERRNO', sqlcode, FALSE );
109 fnd_message.set_token( 'ROUTINE', 'get_next_auto_seq', FALSE );
110 fnd_message.set_token( 'REASON', sqlerrm, FALSE );
111 fnd_message.set_token( 'ERRFILE', 'AFSQNUMB.pls', FALSE );
112 fnd_message.set_token( 'SQLSTMT', v_proc_stmt, FALSE );
113 app_exception.raise_exception;
114 return( NULL );
115
116 end get_next_auto_seq;
117
118 /*-------------------------------------------------------------------------+
119 | get_next_auto_sequence
120 |
121 | Determines the sequence used in the provided context and returns the
122 | next value. Sequence must exist, otherwise, an error message is issued.
123 |
124 | 'in' variables:
125 | appid = application_id
126 | sobid = set_of_books_id
127 | cat_code = category_code
128 | met_code = method_code
129 | trx_date = transaction_date
130 *-------------------------------------------------------------------------*/
131 function get_next_auto_sequence (appid in number,
132 cat_code in varchar2,
133 sobid in number,
134 met_code in char,
135 trx_date in varchar2) return number
136 is
137 proc_stmt varchar2(100);
138 dbseqnm varchar2(30);
139 c integer;
140 row_processed integer;
141 temp integer;
142 val integer;
143 next_val integer;
144
145 begin
146
147 SELECT SEQ.DB_SEQUENCE_NAME into dbseqnm
148 FROM FND_DOCUMENT_SEQUENCES SEQ,
149 FND_DOC_SEQUENCE_ASSIGNMENTS SA
150 WHERE SEQ.DOC_SEQUENCE_ID = SA.DOC_SEQUENCE_ID
151 AND SA.APPLICATION_ID = appid
152 AND SA.CATEGORY_CODE = cat_code
153 AND (SA.METHOD_CODE = met_code or SA.METHOD_CODE is NULL)
154 AND (SA.SET_OF_BOOKS_ID = sobid or SA.SET_OF_BOOKS_ID is NULL)
155 -- bug 1019289, 1295363 - change between to add .9999 to end date, also removed trunc's
156 AND to_date( trx_date, decode( length(trx_date),
157 9,'DD-MON-RR',
158 10,'DD-MM-YYYY',
159 11,'DD-MON-YYYY',
160 'YYYY/MM/DD') ) between
161 sa.start_date and nvl(sa.end_date + .9999,to_date(trx_date,decode(length(trx_date),
162 9,'DD-MON-RR',
163 10,'DD-MM-YYYY',
164 11,'DD-MON-YYYY',
165 'YYYY/MM/DD')) +.9999);
166
167 c := dbms_sql.open_cursor;
168
169 proc_stmt := 'begin select ' || dbseqnm || '.nextval ' ||
170 'into :next_val from sys.dual; end;';
171
172 dbms_sql.parse(c, proc_stmt, dbms_sql.native);
173 dbms_sql.bind_variable(c, 'next_val', next_val);
174
175 row_processed := dbms_sql.execute(c);
176 dbms_sql.variable_value(c,'next_val', val);
177
178 dbms_sql.close_cursor(c);
179 return (val);
180
181 exception
182 when NO_DATA_FOUND then
183 fnd_message.set_name('FND', 'GET_NEXT_SEQ_VALUE_ERROR');
184 fnd_message.set_token('SEQUENCE', dbseqnm, FALSE);
185 app_exception.raise_exception;
186
187 end get_next_auto_sequence;
188
189 function get_next_auto_sequence (appid in number,
190 cat_code in varchar2,
191 sobid in number,
192 met_code in char,
193 trx_date in date) return number
194 is
195 proc_stmt varchar2(100);
196 dbseqnm varchar2(30);
197 c integer;
198 row_processed integer;
199 temp integer;
200 val integer;
201 next_val integer;
202
203 begin
204
205 SELECT SEQ.DB_SEQUENCE_NAME into dbseqnm
206 FROM FND_DOCUMENT_SEQUENCES SEQ,
207 FND_DOC_SEQUENCE_ASSIGNMENTS SA
208 WHERE SEQ.DOC_SEQUENCE_ID = SA.DOC_SEQUENCE_ID
209 AND SA.APPLICATION_ID = appid
210 AND SA.CATEGORY_CODE = cat_code
211 AND (SA.METHOD_CODE = met_code or SA.METHOD_CODE is NULL)
212 AND (SA.SET_OF_BOOKS_ID = sobid or SA.SET_OF_BOOKS_ID is NULL)
213 /* bug 1019289, 1295363 - change between to add .9999 to end date also removed trunc
214 and date coversion routines not needed since this trx_date is a date */
215 AND trx_date between sa.start_date and nvl(sa.end_date + .9999, trx_date + .9999);
216
217 c := dbms_sql.open_cursor;
218
219 proc_stmt := 'begin select ' || dbseqnm || '.nextval ' ||
220 'into :next_val from sys.dual; end;';
221
222 dbms_sql.parse(c, proc_stmt, dbms_sql.native);
223 dbms_sql.bind_variable(c, 'next_val', next_val);
224
225 row_processed := dbms_sql.execute(c);
226 dbms_sql.variable_value(c,'next_val', val);
227
228 dbms_sql.close_cursor(c);
229 return (val);
230
231 exception
232 when NO_DATA_FOUND then
233 fnd_message.set_name('FND', 'GET_NEXT_SEQ_VALUE_ERROR');
234 fnd_message.set_token('SEQUENCE', dbseqnm, FALSE);
235 app_exception.raise_exception;
236
237 end get_next_auto_sequence;
238
239 procedure create_gapless_sequences
240 is
241 cursor c
242 is
243 select seq.doc_Sequence_id
244 from fnd_document_sequences seq
245 where seq.doc_sequence_id not in
246 (select su.doc_Sequence_id
247 from fnd_doc_sequence_users su)
248 and seq.type='G';
249 seqid number;
250 result number;
251 begin
252
253 open c;
254
255 loop
256 fetch c into seqid;
257 exit when c%notfound;
258
259 result := create_gapless_sequence(seqid);
260
261 end loop;
262 close c;
263
264 commit;
265
266 exception
267 when others then
268 fnd_message.set_name('FND', 'SQL-GENERIC ERROR');
269 fnd_message.set_token('ERRNO', sqlcode, FALSE);
270 fnd_message.set_token('ROUTINE', 'create_gapless_sequence', FALSE);
271 fnd_message.set_token('REASON', sqlerrm, FALSE);
272 fnd_message.set_token('ERRFILE', 'AFSQNUMB.pls', FALSE);
273 fnd_message.set_token('SQLSTMT', 'select seq.doc_Sequence_id ...', FALSE);
274 app_exception.raise_exception;
275
276 end create_gapless_sequences;
277
278 /* This function creates a row in the fnd_doc_sequence_users
279 * to keep track of the sequence value of a gapless sequence.
280 * This is called when a sequence is assigned to a document in
281 * Assign Document Sequence form (FNDSNASQ).
282 *
283 * Bug 494345: FND_DOC_SEQUENCE_USERS should only record one sequence
284 * per entry, instead of one assignment per entry. Therefore,
285 * the sequence_assignment_id in this table is not really used
286 * in determining the sequence next value. The assignment_id
287 * of the first assignment that uses that particular sequence
288 * will be recorded.
289 */
290 function create_gapless_sequence (seqid in number) return number
291 is
292 begin
293
294 insert into fnd_doc_sequence_users
295 (doc_sequence_id,
296 doc_sequence_assignment_id,
297 user_id,
298 nextval,
299 creation_date,
300 created_by,
301 last_update_date,
302 last_updated_by,
303 last_update_login)
304 select seq.doc_sequence_id,
305 sa.doc_sequence_assignment_id,
306 0,
307 /* Bug 508093: removed '+1' that was added to the NVL of initial_value
308 not sure why it was there in the first place it
309 prevented the value of '1' from ever being used by
310 a gapless sequence - the forms now protects against
311 the zero value - which may have been why we had it
312 there - so now the default will be 1 instead of 2
313
314 Replaced NVL with Decode - now we proctect the API from
315 zero or NULL (as per request from MWARREN) and do not
316 depend on forms.
317 */
318 DECODE(seq.initial_value, NULL, 1, 0, 1, seq.initial_value),
319 sysdate, 0, sysdate, 0, 0
320 from fnd_document_sequences seq,
321 fnd_doc_sequence_assignments sa
322 where seq.doc_sequence_id = sa.doc_sequence_id
323 and sa.doc_sequence_id = seqid
324 and sa.doc_Sequence_assignment_id =
325 (select min(doc_sequence_assignment_id)
326 from fnd_doc_sequence_assignments
327 where doc_sequence_id = seqid);
328
329 return(1);
330
331 exception
332 when others then
333 fnd_message.set_name('FND', 'SQL-GENERIC ERROR');
334 fnd_message.set_token('ERRNO', sqlcode, FALSE);
335 fnd_message.set_token('ROUTINE', 'create_gapless_sequence( '|| seqid || ' )', FALSE);
336 fnd_message.set_token('REASON', sqlerrm, FALSE);
337 fnd_message.set_token('ERRFILE', 'AFSQNUMB.pls', FALSE);
338 fnd_message.set_token('SQLSTMT', 'insert into fnd_doc_sequence_users ...', FALSE);
339 app_exception.raise_exception;
340 return(0);
341
342 end create_gapless_sequence;
343
344
345
346 /* This function gets the nextval of a gapless/user sequence and update the
347 * next nextval.
348 * fds_user_id = 0 for gapless sequence, userid for user sequence
349 * seqassid = sequence_assignment_id - not used since bug 494345
350 * seqid = doc_sequence_id
351 */
352 function get_next_user_sequence ( fds_user_id in number,
353 seqassid in number, -- not used
354 seqid in number) return number
355 is
356 v_nextVal number;
357 begin
358 update fnd_doc_sequence_users
359 set nextval = nextval + 1
360 where user_id = fds_user_id and doc_sequence_id = seqid;
361
362 select distinct nextval-1
363 into v_nextVal
364 from fnd_doc_sequence_users
365 where doc_sequence_id = seqid and user_id = fds_user_id;
366
367 return(v_nextVal);
368
369 exception
370 when no_data_found then
371 fnd_message.set_name( 'FND','SQL-ERROR SELECTING' );
372 fnd_message.set_token( 'TABLENAME', 'FND_DOC_SEQUENCE_USERS' ,FALSE);
373 app_exception.raise_exception;
374 return(0);
375
376 when others then
377 fnd_message.set_name( 'FND', 'SQL-GENERIC ERROR' );
378 fnd_message.set_token( 'ERRNO', sqlcode, FALSE );
379 fnd_message.set_token( 'ROUTINE', 'get_next_user_sequence', FALSE );
380 fnd_message.set_token( 'REASON', sqlerrm, FALSE );
381 fnd_message.set_token( 'ERRFILE', 'AFSQNUMB.pls', FALSE );
382 fnd_message.set_token( 'SQLSTMT', 'update fnd_doc_sequence_users ... nextval', FALSE );
383 app_exception.raise_exception;
384 return(0);
385 end get_next_user_sequence;
386
387
388
389 /* This procedure is added for bug 586077 and creates data base sequences
390 for all AUTOMATIC sequences defined in FND_DOCUMENT_SEQUENCES but does
391 not yet have a real DB sequences in ALL_SEQUENCES. This is done by the
392 this server side PLSQL rather than spawning a Concurrent Request. */
393 /* Fix for bug 1106208 -- behavior is changed from above to remove the outer
394 join to ALL_SEQUENCES -- instead we now call this routine at the PRE-INSERT
395 trigger of the define form for each record inserted. Thus we can pass the
396 new db_sequence_name and initial_value and create the DB Seq directly. The
397 only bad effect of calling from PRE-INSERT is that if something wierd happens
398 and the commit fails and AD_DDL does not the DB Seq has still been created,
399 this will not cause any functional problems since the call to create the name
400 is called before this call - FND_DOCUMENT_SEQUENCES_S.nextval has already been
401 fired and a duplicate name will not be generated - we do have an unused DB Seq
402 hanging around, but the odds of this happing are low since this the last thing
403 we do before the insert */
404 procedure create_db_seq ( db_seq_name in fnd_document_sequences.db_sequence_name%TYPE,
405 init_value in fnd_document_sequences.initial_value%TYPE )
406 is
407 v_ddl_sql varchar2(150);
408 v_fnd_schema fnd_oracle_userid.oracle_username%TYPE;
409 v_stage number := 0; /* sets the stage of execution for exception processing */
410
411 begin
412 -- First get the FND schema name
413 SELECT fou.oracle_username into v_fnd_schema
414 FROM fnd_product_installations fpi,
415 fnd_oracle_userid fou,
416 fnd_application fa
417 WHERE fpi.application_id = fa.application_id
418 AND fpi.oracle_id = fou.oracle_id
419 AND fa.application_short_name = 'FND';
420
421 -- actually create the DB seq using the magic of AD_DDL
422 v_stage := 1;
423 v_ddl_sql := 'CREATE SEQUENCE ' || db_seq_name ||
424 ' MINVALUE 1 NOMAXVALUE START WITH ' || init_value ||
425 ' NOCACHE ORDER NOCYCLE';
426 ad_ddl.do_ddl( v_fnd_schema, 'FND', ad_ddl.create_sequence,
427 v_ddl_sql, db_seq_name );
428
429 exception
430 when others then
431 fnd_message.set_name('FND', 'SQL-GENERIC ERROR');
432 fnd_message.set_token('ERRNO', sqlcode, FALSE);
433 fnd_message.set_token('ROUTINE', 'create_db_seq', FALSE);
434 fnd_message.set_token('REASON', sqlerrm, FALSE);
435 fnd_message.set_token('ERRFILE', 'AFSQNUMB.pls', FALSE);
436 IF v_stage = 0 THEN
437 fnd_message.set_token('SQLSTMT', 'SELECT fou.oracle_username into v_fnd_schema ...', FALSE);
438 ELSE
439 fnd_message.set_token('SQLSTMT', ad_ddl.error_buf, FALSE);
440 END IF;
441 app_exception.raise_exception;
442
443 end create_db_seq;
444
445 /*
446 * This function replaces or performs the actions of the 'C' code function fdssop()
447 * Please see the comments in the spec file for usage information
448 */
449 function get_seq_info ( app_id in number,
450 cat_code in varchar2,
451 sob_id in number,
452 met_code in char,
453 trx_date in date,
454 docseq_id out nocopy number,
455 docseq_type out nocopy char,
456 docseq_name out nocopy varchar2,
457 db_seq_name out nocopy varchar2,
458 seq_ass_id out nocopy number,
459 prd_tab_name out nocopy varchar2,
460 aud_tab_name out nocopy varchar2,
461 msg_flag out nocopy char,
462 suppress_error in char default 'N',
463 suppress_warn in char default 'N'
464 ) return number
465 is
466 v_profVal varchar2(40);
467 begin
468
469 /* Check to if the cache has been initialized */
470 IF v_SeqInfoCache.initFlag THEN
471 /* Check to see if the data in the cache has the 4 key values and the transaction date
472 is still in range for this assignment */
473 IF ( v_SeqInfoCache.app_id = app_id ) AND ( v_SeqInfoCache.cat_code = cat_code ) AND
474 ( v_SeqInfoCache.sob_id = sob_id ) AND ( v_SeqInfoCache.met_code = met_code ) AND
475 ( trx_date >= v_SeqInfoCache.startDate ) AND ( trx_date <= v_SeqInfoCache.endDate ) THEN
476 IF v_SeqInfoCache.retStat = FND_SEQNUM.SEQSUCC THEN
477 /* if we had a good status from the first select use the cache to populate this
478 request's output parameters */
479 docseq_id := v_SeqInfoCache.docseq_id;
480 docseq_type := v_SeqInfoCache.docseq_type;
481 docseq_name := v_SeqInfoCache.docseq_name;
482 db_seq_name := v_SeqInfoCache.db_seq_name;
483 seq_ass_id := v_SeqInfoCache.seq_ass_id;
484 prd_tab_name := v_SeqInfoCache.prd_tab_name;
485 aud_tab_name := v_SeqInfoCache.aud_tab_name;
486 msg_flag := v_SeqInfoCache.msg_flag;
487 END IF; /* retStat */
488 /* if the status was bad last time, it still is and the data is not used
489 so we just pass the same bad status again - i am not sure the
490 functional app would call us twice in the same session with the
491 same parameters after the first fail but if they do lets not waste
492 the select time ... */
493 return( v_SeqInfoCache.retStat );
494 END IF; /* parameter compare */
495 /* During a session the initFlag will always be TRUE after the first call but
496 since the parameters have changed we will just drop through and start anew */
497 END IF; /* initFlag */
498
499 /* No cache or new parameters - so load the parameters into the cache */
500 v_SeqInfoCache.app_id := app_id;
501 v_SeqInfoCache.cat_code := cat_code;
502 v_SeqInfoCache.sob_id := sob_id;
503 v_SeqInfoCache.met_code := met_code;
504 v_SeqInfoCache.startDate := trx_date + 1;
505 v_SeqInfoCache.endDate := trx_date - 1;
506 v_SeqInfoCache.initFlag := TRUE;
507
508 /* This call will retrieve the value of the "Sequential Numbering" profile option
509 'A' = Always Used, 'N' = Not Used, 'P' = Partially Used */
510 FND_PROFILE.GET( 'UNIQUE:SEQ_NUMBERS', v_profVal );
511 IF v_profVal = 'N' THEN
512 v_SeqInfoCache.retStat := FND_SEQNUM.NOTUSED;
513 return( v_SeqInfoCache.retStat );
514 ELSIF v_profVal <> 'P' AND v_profVal <> 'A' THEN
515 v_SeqInfoCache.retStat := FND_SEQNUM.BADPROF;
516 fnd_message.set_name( 'FND', 'PROFILES-VALUES' ); -- this should never happen, BUT...
517 app_exception.raise_exception;
518 return( v_SeqInfoCache.retStat );
519 END IF; /* v_profVal */
520
521 /* do our select into the cache */
522 select SEQ.DOC_SEQUENCE_ID, SEQ.TYPE, SEQ.NAME,
523 SEQ.AUDIT_TABLE_NAME, SEQ.DB_SEQUENCE_NAME, SEQ.TABLE_NAME,
524 SA.DOC_SEQUENCE_ASSIGNMENT_ID, SEQ.MESSAGE_FLAG,
525 SA.START_DATE, SA.END_DATE
526 into v_SeqInfoCache.docseq_id, v_SeqInfoCache.docseq_type, v_SeqInfoCache.docseq_name,
527 v_SeqInfoCache.aud_tab_name, v_SeqInfoCache.db_seq_name, v_SeqInfoCache.prd_tab_name,
528 v_SeqInfoCache.seq_ass_id, v_SeqInfoCache.msg_flag,
529 v_SeqInfoCache.startDate, v_SeqInfoCache.endDate
530 from FND_DOCUMENT_SEQUENCES SEQ, FND_DOC_SEQUENCE_ASSIGNMENTS SA
531 where SEQ.DOC_SEQUENCE_ID = SA.DOC_SEQUENCE_ID and
532 SA.APPLICATION_ID = v_SeqInfoCache.app_id and
533 SA.CATEGORY_CODE = v_SeqInfoCache.cat_code and
534 ( SA.SET_OF_BOOKS_ID = v_SeqInfoCache.sob_id or SA.SET_OF_BOOKS_ID is NULL ) and
535 /* bug 1354846 - add the NULL compare to v_SeqInfoCache.met_code so the
536 assignment query can check for either type */
537 ( SA.METHOD_CODE = v_SeqInfoCache.met_code or SA.METHOD_CODE is NULL
538 or v_SeqInfoCache.met_code is NULL ) and
539 /* bug 1019289, 1295363 - change between to add .9999 to end date and trx_date */
540 trx_date between SA.START_DATE and nvl( SA.END_DATE + .9999, trx_date + .9999 );
541
542 /* Load the output parameters from the newly filled cache */
543 docseq_id := v_SeqInfoCache.docseq_id;
544 docseq_type := v_SeqInfoCache.docseq_type;
545 docseq_name := v_SeqInfoCache.docseq_name;
546 db_seq_name := v_SeqInfoCache.db_seq_name;
547 seq_ass_id := v_SeqInfoCache.seq_ass_id;
548 prd_tab_name := v_SeqInfoCache.prd_tab_name;
549 aud_tab_name := v_SeqInfoCache.aud_tab_name;
550 msg_flag := v_SeqInfoCache.msg_flag;
551 v_SeqInfoCache.retStat := FND_SEQNUM.SEQSUCC;
552
553 return( v_SeqInfoCache.retStat );
554
555 exception
556 when no_data_found then
557 /* 'A' = Always Used, 'N' = Not Used, 'P' = Partially Used */
558 IF v_profVal = 'P' THEN
559 v_SeqInfoCache.retStat := FND_SEQNUM.NOASSIGN;
560 IF upper(suppress_warn) = 'N' THEN
561 fnd_message.set_name( 'FND', 'UNIQUE-NO ASSIGNMENT' );
562 app_exception.raise_exception;
563 END IF;
564 ELSIF v_profVal = 'A' THEN
565 v_SeqInfoCache.retStat := FND_SEQNUM.ALWAYS;
566 IF upper(suppress_error) = 'N' THEN
567 fnd_message.set_name( 'FND', 'UNIQUE-ALWAYS USED' );
568 app_exception.raise_exception;
569 END IF;
570 END IF;
571 return( v_SeqInfoCache.retStat );
572
573 when others then
574 v_SeqInfoCache.retStat := FND_SEQNUM.ORAFAIL;
575 fnd_message.set_name( 'FND', 'SQL-GENERIC ERROR' );
576 fnd_message.set_token( 'ERRNO', sqlcode, FALSE );
577 fnd_message.set_token( 'ROUTINE', 'get_seq_info', FALSE );
578 fnd_message.set_token( 'REASON', sqlerrm, FALSE );
579 fnd_message.set_token( 'ERRFILE', 'AFSQNUMB.pls', FALSE );
580 fnd_message.set_token( 'SQLSTMT', 'select SEQ.DOC_SEQUENCE_ID, SEQ.TYPE, SEQ.NAME ...', FALSE );
581 app_exception.raise_exception;
582 return( v_SeqInfoCache.retStat );
583
584 end get_seq_info;
585
586 /*
587 * This finction creates an audit record in the audit table for the current DocSeq
588 * Fix Bug 1073084 - changed to use EXECUTE IMMEDIATE instead of dbms_sql.
589 */
590 function create_audit_rec ( aud_tab_name in varchar2,
591 docseq_id in number,
592 seq_val in number,
593 seq_ass_id in number,
594 user_id in number
595 ) return number
596 is
597 v_proc_stmt varchar2(500);
598 v_row_processed integer;
599 begin
600
601
602 v_proc_stmt := 'INSERT INTO ' || aud_tab_name || ' (DOC_SEQUENCE_ID, DOC_SEQUENCE_VALUE, ' ||
603 'DOC_SEQUENCE_ASSIGNMENT_ID, CREATION_DATE, CREATED_BY) VALUES ( :seq_id, :val, ' ||
604 ':asgn_id, sysdate, :cr_by)';
605 EXECUTE IMMEDIATE v_proc_stmt USING docseq_id, seq_val, seq_ass_id, user_id;
606 return( FND_SEQNUM.SEQSUCC );
607
608 exception
609 when dup_val_on_index then
610 fnd_message.set_name( 'FND', 'UNIQUE-DUPLICATE SEQUENCE' );
611 app_exception.raise_exception;
612 return( FND_SEQNUM.NOTUNIQ );
613 when others then
614 fnd_message.set_name( 'FND', 'SQL-GENERIC ERROR' );
615 fnd_message.set_token( 'ERRNO', sqlcode, FALSE );
616 fnd_message.set_token( 'ROUTINE', 'create_audit_rec', FALSE );
617 fnd_message.set_token( 'REASON', sqlerrm, FALSE );
618 fnd_message.set_token( 'ERRFILE', 'AFSQNUMB.pls', FALSE );
619 fnd_message.set_token( 'SQLSTMT', v_proc_stmt, FALSE );
620 app_exception.raise_exception;
621 return( FND_SEQNUM.ORAFAIL );
622
623 end create_audit_rec;
624
625 /*
626 * This function replaces/performs the actions of the user exit #FND SEQVAL
627 * Please see the comments in the spec file for usage information
628 */
629 function get_seq_val ( app_id in number,
630 cat_code in varchar2,
631 sob_id in number,
632 met_code in char,
633 trx_date in date,
634 seq_val in out nocopy number,
635 docseq_id out nocopy number,
636 suppress_error in char default 'N',
637 suppress_warn in char default 'N'
638 ) return number
639 is
640 v_seqType char;
641 v_seqName varchar2(30);
642 v_dbSeqNm varchar2(30);
643 v_seqAssID number;
644 v_prdTabNm varchar2(30);
645 v_audTabNm varchar2(30);
646 v_msgFlg char;
647 v_stat number;
648 v_profVal varchar2(70);
649 v_docSeqId number;
650 begin
651
652 /* Get all the needed Doc_Seq info */
653 v_stat := get_seq_info( app_id, cat_code, sob_id, met_code, trx_date, v_docSeqId, v_seqType,
654 v_seqName, v_dbSeqNm, v_seqAssID, v_prdTabNm, v_audTabNm, v_msgFlg, suppress_error, suppress_warn );
655 docseq_id := v_docSeqId;
656 IF v_stat <> FND_SEQNUM.SEQSUCC THEN
657 IF v_stat = FND_SEQNUM.NOTUSED THEN
658 /* the profile is set to not used so we just return peacefully */
659 return( FND_SEQNUM.SEQSUCC );
660 ELSIF v_stat = FND_SEQNUM.NOASSIGN THEN
661 /* we found nothing but the profile is only set to partial so that is OK BUT,
662 we would have given a warning in get_seq_info() based on the suppress_warn flag */
663 return( FND_SEQNUM.SEQSUCC );
664 ELSE
665 return( v_stat );
666 END IF;
667 END IF;
668
669 /* This call will retrieve the value of the "USER_ID" profile option */
670 FND_PROFILE.GET( 'USER_ID', v_profVal );
671 IF v_profVal IS NULL THEN
672 fnd_message.set_name( 'FND', 'USER' ); -- this should never happen, BUT...
673 app_exception.raise_exception;
674 return( FND_SEQNUM.BADPROF );
675 END IF;
676 /* we found a good Doc_Seq assignment so we proceed ...
677 first we check the Seq Type from the FND_DOCUMENT_SEQUENCES.TYPE - valid values are :
678 'A' = Automatic, 'G' = Gapless and 'M' = Manual
679 'U' = Auto by User - but it is not really working - never has(??) */
680 IF v_seqType = 'A' THEN
681 seq_val := get_next_auto_seq( v_dbSeqNm );
682 ELSIF v_seqType = 'G' THEN
683 seq_val := get_next_user_sequence( 0, v_seqAssID, v_docSeqId );
684 ELSIF v_seqType = 'U' THEN
685 seq_val := get_next_user_sequence( to_number(v_profVal), v_seqAssID, v_docSeqId );
686 ELSIF v_seqType = 'M' THEN
687 IF seq_val IS NULL THEN
688 fnd_message.set_name( 'FND', 'UNIQUE-NO VALUE' );
689 fnd_message.set_token( 'SEQUENCE', v_seqName, FALSE );
690 app_exception.raise_exception;
691 return( FND_SEQNUM.NOVALUE );
692 END IF;
693 /* i don't actually have a routine to check the uniqueness of the Manual DocSeq
694 like they did in the 'C' code since the very next call from #FND SEQVAL updates
695 the audit record - where we can trap the error and report it. If there is a
696 requirement for an equivalent to #FND SEQCHK we can write it seperately */
697 ELSE
698 -- this should never happen, unless someone uses sqlplus on our tables form validates value...
699 return( FND_SEQNUM.BADTYPE );
700 END IF;
701
702 v_stat := create_audit_rec( v_audTabNm, v_docSeqId, seq_val, v_seqAssID, to_number(v_profVal) );
703 /* we could/should test this return stat but every thing we can handle is handled
704 in the routine so lets just pass it on, and use it for debug on the wierd error */
705 return( v_stat );
706
707 end get_seq_val;
708
709 /*
710 * This function is for special internal Applications use to create new Document
711 * Sequences in batch form by the Product teams for upgrades or coversions
712 */
713 function define_doc_seq (
714 app_id in number,
715 docseq_name in fnd_document_sequences.name%TYPE,
716 docseq_type in fnd_document_sequences.type%TYPE,
717 msg_flag in fnd_document_sequences.message_flag%TYPE,
718 init_value in fnd_document_sequences.initial_value%TYPE,
719 p_startDate in date,
720 p_endDate in date default NULL
721 ) return number
722 is
723 v_numRows number;
724 v_audTabNm fnd_document_sequences.audit_table_name%TYPE;
725 v_docSeqId number;
726 v_dbSeqNm fnd_document_sequences.db_sequence_name%TYPE;
727 v_startDate date;
728 v_endDate date;
729 begin
730
731 begin /* APPLICATION_ID check block */
732 select count( APPLICATION_ID ) into v_numRows
733 from FND_APPLICATION where APPLICATION_ID = app_id;
734 IF v_numRows = 0 THEN
735 return ( FND_SEQNUM.BADAPPID );
736 END IF;
737 exception
738 when others then
739 fnd_message.set_name( 'FND', 'SQL-GENERIC ERROR' );
740 fnd_message.set_token( 'ERRNO', sqlcode, FALSE );
741 fnd_message.set_token( 'ROUTINE', 'define_doc_seq', FALSE );
742 fnd_message.set_token( 'REASON', sqlerrm, FALSE );
743 fnd_message.set_token( 'ERRFILE', 'AFSQNUMB.pls', FALSE );
744 fnd_message.set_token( 'SQLSTMT', 'select count from FND_APPLICATION where APPLICATION_ID = ' || to_char(app_id), FALSE );
745 app_exception.raise_exception;
746 return( FND_SEQNUM.ORAFAIL );
747 end; /* APPLICATION_ID check block */
748
749 begin /* FND_DOCUMENT_SEQUENCES.NAME uniqueness check block */
750 select count( NAME ) into v_numRows
751 from FND_DOCUMENT_SEQUENCES where NAME = docseq_name;
752 IF v_numRows > 0 THEN
753 return ( FND_SEQNUM.DUPNAME );
754 END IF;
755 exception
756 when others then
757 fnd_message.set_name( 'FND', 'SQL-GENERIC ERROR' );
758 fnd_message.set_token( 'ERRNO', sqlcode, FALSE );
759 fnd_message.set_token( 'ROUTINE', 'define_doc_seq', FALSE );
760 fnd_message.set_token( 'REASON', sqlerrm, FALSE );
761 fnd_message.set_token( 'ERRFILE', 'AFSQNUMB.pls', FALSE );
762 fnd_message.set_token( 'SQLSTMT', 'select count from FND_DOCUMENT_SEQUENCES where NAME = ' || docseq_name, FALSE );
763 app_exception.raise_exception;
764 return( FND_SEQNUM.ORAFAIL );
765 end; /* NAME check block */
766 /* validate the type */
767 -- IF docseq_type NOT IN ( 'A', 'G', 'M', 'U' ) THEN /* we do not do User any more ?? */
768 IF docseq_type NOT IN ( 'A', 'G', 'M' ) THEN
769 return( FND_SEQNUM.BADTYPE );
770 END IF;
771 /* validate the message_flag */
772 IF msg_flag NOT IN ( 'Y', 'N' ) THEN
773 return( FND_SEQNUM.BADFLAG );
774 END IF;
775 /* basic sanity on the dates only ???
776 first the forms format mask only allows whole dates no hours so ... */
777 v_startDate := trunc( p_startDate );
778 v_endDate := trunc( p_endDate );
779 IF p_endDate <> NULL THEN
780 IF v_startDate >= v_endDate THEN
781 return( FND_SEQNUM.BADDATE );
782 END IF;
783 END IF;
784 /* this Audit Table name decode is also in the form FNDSNDSQ so change both if any */
785 begin /* make Audit Table name block */
786 select DECODE(app_id,0,'FND', 1,'FND', 101,'GL', 111,'RA', 140,'FA',
787 160,'ALR', 168,'RG', 200,'AP', 201,'PO', 222,'AR', 260,'CE',
788 300,'OE', 401,'INV', 500,'SA', 700,'MFG', 702,'BOM', 703,'ENG',
789 704,'MRP', 705,'CRP', 706,'WIP', 800,'PER', 801,'PAY', 802,'FF',
790 803,'DT', 804,'SSP', 7000,'JA', 7002,'JE', 7003,'JG', 7004,'JL',
791 'FND') || '_DOC_SEQUENCE_AUDIT' into v_audTabNm from dual;
792 exception
793 when others then
794 fnd_message.set_name( 'FND', 'SQL-GENERIC ERROR' );
795 fnd_message.set_token( 'ERRNO', sqlcode, FALSE );
796 fnd_message.set_token( 'ROUTINE', 'define_doc_seq', FALSE );
797 fnd_message.set_token( 'REASON', sqlerrm, FALSE );
798 fnd_message.set_token( 'ERRFILE', 'AFSQNUMB.pls', FALSE );
799 fnd_message.set_token( 'SQLSTMT', 'select DECODE(' || to_char(app_id) || '...) from dual', FALSE );
800 app_exception.raise_exception;
801 return( FND_SEQNUM.ORAFAIL );
802 end; /* Audit Table name block */
803
804 /* hopefully we have checked enough to do an insert so lets pop the id sequence */
805 begin /* get next DOC_SEQUENCE_ID block */
806 select FND_DOCUMENT_SEQUENCES_S.nextval into v_docSeqId from dual;
807 exception
808 when others then
809 fnd_message.set_name( 'FND', 'SQL-GENERIC ERROR' );
810 fnd_message.set_token( 'ERRNO', sqlcode, FALSE );
811 fnd_message.set_token( 'ROUTINE', 'define_doc_seq', FALSE );
812 fnd_message.set_token( 'REASON', sqlerrm, FALSE );
813 fnd_message.set_token( 'ERRFILE', 'AFSQNUMB.pls', FALSE );
814 fnd_message.set_token( 'SQLSTMT', 'select FND_DOCUMENT_SEQUENCES_S.nextval from dual', FALSE );
815 app_exception.raise_exception;
816 return( FND_SEQNUM.ORAFAIL );
817 end; /* FND_DOCUMENT_SEQUENCES_S block */
818
819 IF docseq_type = 'A' THEN
820 v_dbSeqNm := 'FND_DOC_SEQ_'|| to_char(v_docSeqId) || '_S';
821 FND_SEQNUM.create_db_seq( v_dbSeqNm, init_value );
822 ELSE
823 v_dbSeqNm := NULL;
824 END IF;
825
826 insert into FND_DOCUMENT_SEQUENCES ( DOC_SEQUENCE_ID, NAME, APPLICATION_ID,
827 AUDIT_TABLE_NAME, DB_SEQUENCE_NAME, MESSAGE_FLAG, TYPE, INITIAL_VALUE,
828 START_DATE, END_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
829 CREATED_BY, LAST_UPDATE_LOGIN )
830 values ( v_docSeqId, docseq_name, app_id, v_audTabNm, v_dbSeqNm, msg_flag,
831 docseq_type, init_value, v_startDate, v_endDate, SYSDATE, FND_GLOBAL.USER_ID,
832 SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID );
833
834 -- ok i assume all is well let's return SEQSUCC
835 return( FND_SEQNUM.SEQSUCC );
836
837 exception
838 when others then
839 fnd_message.set_name( 'FND', 'SQL-GENERIC ERROR' );
840 fnd_message.set_token( 'ERRNO', sqlcode, FALSE );
841 fnd_message.set_token( 'ROUTINE', 'define_doc_seq', FALSE );
842 fnd_message.set_token( 'REASON', sqlerrm, FALSE );
843 fnd_message.set_token( 'ERRFILE', 'AFSQNUMB.pls', FALSE );
844 fnd_message.set_token( 'SQLSTMT', 'insert into FND_DOCUMENT_SEQUENCES ..' || docseq_name || '...', FALSE );
845 app_exception.raise_exception;
846 return( FND_SEQNUM.ORAFAIL );
847
848 end define_doc_seq;
849
850 /*
851 * This function is for special internal Applications use to create new Doc_Seq
852 * Assignments in batch form by the Product teams for upgrades or coversions
853 */
854 function assign_doc_seq (
855 app_id in number,
856 docseq_name in fnd_document_sequences.name%TYPE,
857 cat_code in fnd_doc_sequence_assignments.category_code%TYPE,
858 sob_id in fnd_doc_sequence_assignments.set_of_books_id%TYPE,
859 met_code in fnd_doc_sequence_assignments.method_code%TYPE,
860 p_startDate in date,
861 p_endDate in date default NULL
862 ) return number
863 is
864 v_seqAssID number;
865 v_docSeqId number;
866 v_type fnd_document_sequences.type%TYPE;
867 v_prdTabNm fnd_document_sequences.table_name%TYPE;
868 v_enabled fnd_descr_flex_column_usages.enabled_flag%TYPE;
869 v_numRows number;
870 v_startDate date;
871 v_endDate date;
872 begin
873
874 begin /* APPLICATION_ID check block */
875 select count( APPLICATION_ID ) into v_numRows
876 from FND_APPLICATION where APPLICATION_ID = app_id;
877 IF v_numRows = 0 THEN
878 return ( FND_SEQNUM.BADAPPID );
879 END IF;
880 exception
881 when others then
882 fnd_message.set_name( 'FND', 'SQL-GENERIC ERROR' );
883 fnd_message.set_token( 'ERRNO', sqlcode, FALSE );
884 fnd_message.set_token( 'ROUTINE', 'assign_doc_seq', FALSE );
885 fnd_message.set_token( 'REASON', sqlerrm, FALSE );
886 fnd_message.set_token( 'ERRFILE', 'AFSQNUMB.pls', FALSE );
887 fnd_message.set_token( 'SQLSTMT', 'select count from FND_APPLICATION where APPLICATION_ID = ' || to_char(app_id), FALSE );
888 app_exception.raise_exception;
889 return( FND_SEQNUM.ORAFAIL );
890 end; /* APPLICATION_ID check block */
891
892 begin /* FND_DOCUMENT_SEQUENCES.NAME check block */
893 select DOC_SEQUENCE_ID, TYPE into v_docSeqId, v_type
894 from FND_DOCUMENT_SEQUENCES where NAME = docseq_name;
895 exception
896 when NO_DATA_FOUND then
897 return( FND_SEQNUM.BADNAME );
898 when others then
899 fnd_message.set_name( 'FND', 'SQL-GENERIC ERROR' );
900 fnd_message.set_token( 'ERRNO', sqlcode, FALSE );
901 fnd_message.set_token( 'ROUTINE', 'assign_doc_seq', FALSE );
902 fnd_message.set_token( 'REASON', sqlerrm, FALSE );
903 fnd_message.set_token( 'ERRFILE', 'AFSQNUMB.pls', FALSE );
904 fnd_message.set_token( 'SQLSTMT', 'select DOC_SEQUENCE_ID from FND_DOCUMENT_SEQUENCES where NAME = ' || docseq_name, FALSE );
905 app_exception.raise_exception;
906 return( FND_SEQNUM.ORAFAIL );
907 end; /* NAME check block */
908
909 begin /* get the table name for category_code */
910 select TABLE_NAME into v_prdTabNm from FND_DOC_SEQUENCE_CATEGORIES
911 where CODE = cat_code and APPLICATION_ID = app_id;
912 exception
913 when NO_DATA_FOUND then
914 return( FND_SEQNUM.BADCODE );
915 when others then
916 fnd_message.set_name( 'FND', 'SQL-GENERIC ERROR' );
917 fnd_message.set_token( 'ERRNO', sqlcode, FALSE );
918 fnd_message.set_token( 'ROUTINE', 'assign_doc_seq', FALSE );
919 fnd_message.set_token( 'REASON', sqlerrm, FALSE );
920 fnd_message.set_token( 'ERRFILE', 'AFSQNUMB.pls', FALSE );
921 fnd_message.set_token( 'SQLSTMT', 'select TABLE_NAME from FND_DOC_SEQUENCE_CATEGORIES where CODE = ' || cat_code, FALSE );
922 app_exception.raise_exception;
923 return( FND_SEQNUM.ORAFAIL );
924 end; /* table name for category_code block */
925
926 begin /* check for Set of Books in Document Flexfield */
927 select ENABLED_FLAG into v_enabled from FND_DESCR_FLEX_COLUMN_USAGES
928 where APPLICATION_ID = 0 and APPLICATION_COLUMN_NAME = 'SET_OF_BOOKS_ID'
929 and DESCRIPTIVE_FLEXFIELD_NAME = 'Document Flexfield'
930 and DESCRIPTIVE_FLEX_CONTEXT_CODE = 'Global Data Elements';
931 IF v_enabled = 'Y' THEN
932 begin /* verify the Set of Books ID */
933 select count( SET_OF_BOOKS_ID ) into v_numRows
934 from GL_SETS_OF_BOOKS where SET_OF_BOOKS_ID = sob_id;
935 IF v_numRows = 0 THEN
936 return ( FND_SEQNUM.BADSOB );
937 END IF;
938 exception
939 when others then
940 fnd_message.set_name( 'FND', 'SQL-GENERIC ERROR' );
941 fnd_message.set_token( 'ERRNO', sqlcode, FALSE );
942 fnd_message.set_token( 'ROUTINE', 'assign_doc_seq', FALSE );
943 fnd_message.set_token( 'REASON', sqlerrm, FALSE );
944 fnd_message.set_token( 'ERRFILE', 'AFSQNUMB.pls', FALSE );
945 fnd_message.set_token( 'SQLSTMT', 'select count from GL_SETS_OF_BOOKS where SET_OF_BOOKS_ID = ' || to_char( sob_id ), FALSE );
946 app_exception.raise_exception;
947 return( FND_SEQNUM.ORAFAIL );
948 end; /* SOB block */
949 END IF;
950 exception
951 when others then
952 fnd_message.set_name( 'FND', 'SQL-GENERIC ERROR' );
953 fnd_message.set_token( 'ERRNO', sqlcode, FALSE );
954 fnd_message.set_token( 'ROUTINE', 'assign_doc_seq', FALSE );
955 fnd_message.set_token( 'REASON', sqlerrm, FALSE );
956 fnd_message.set_token( 'ERRFILE', 'AFSQNUMB.pls', FALSE );
957 fnd_message.set_token( 'SQLSTMT', 'select ENABLED_FLAG from ... SET_OF_BOOKS_ID', FALSE );
958 app_exception.raise_exception;
959 return( FND_SEQNUM.ORAFAIL );
960 end; /* Document Flexfield block */
961
962 begin /* check for Method Code in Document Flexfield */
963 select ENABLED_FLAG into v_enabled from FND_DESCR_FLEX_COLUMN_USAGES
964 where APPLICATION_ID = 0 and APPLICATION_COLUMN_NAME = 'METHOD_CODE'
965 and DESCRIPTIVE_FLEXFIELD_NAME = 'Document Flexfield'
966 and DESCRIPTIVE_FLEX_CONTEXT_CODE = 'Global Data Elements';
967 IF v_enabled = 'Y' THEN
968 IF met_code NOT IN ( 'A', 'M', NULL ) THEN
969 return( FND_SEQNUM.BADMTHD );
970 END IF;
971 END IF;
972 exception
973 when others then
974 fnd_message.set_name( 'FND', 'SQL-GENERIC ERROR' );
975 fnd_message.set_token( 'ERRNO', sqlcode, FALSE );
976 fnd_message.set_token( 'ROUTINE', 'assign_doc_seq', FALSE );
977 fnd_message.set_token( 'REASON', sqlerrm, FALSE );
978 fnd_message.set_token( 'ERRFILE', 'AFSQNUMB.pls', FALSE );
979 fnd_message.set_token( 'SQLSTMT', 'select ENABLED_FLAG from ... METHOD_CODE', FALSE );
980 app_exception.raise_exception;
981 return( FND_SEQNUM.ORAFAIL );
982 end; /* Document Flexfield block */
983
984 begin /* DATE check - again most of this code in forms so dual maintain */
985 -- first the forms format mask only allows whole dates no hours so ...
986 v_startDate := trunc( p_startDate );
987 v_endDate := trunc( p_endDate );
988 IF v_endDate is NULL THEN
989 -- see if any rows of this document exist with a null end date.
990 select count( DOC_SEQUENCE_ASSIGNMENT_ID ) into v_numRows
991 from FND_DOC_SEQUENCE_ASSIGNMENTS
992 where CATEGORY_CODE = cat_code
993 and NVL( METHOD_CODE, 'NONE' ) = NVL( met_code, 'NONE' )
994 and APPLICATION_ID = app_id
995 and NVL( SET_OF_BOOKS_ID, 0 ) = NVL( sob_id, 0 )
996 and END_DATE IS NULL;
997 IF v_numRows > 0 THEN
998 -- ERROR! there is another row with a null end_date!
999 FND_MESSAGE.SET_NAME('FND','UNIQUE-NULL END DATE');
1000 return( FND_SEQNUM.BADDATE ); /* comment out this line and uncomment the next for debugging */
1001 -- app_exception.raise_exception;
1002 ELSE
1003 -- See if there is another row for this document with dates that overlap
1004 select count( DOC_SEQUENCE_ASSIGNMENT_ID ) into v_numRows
1005 from FND_DOC_SEQUENCE_ASSIGNMENTS
1006 where CATEGORY_CODE = cat_code
1007 and NVL( METHOD_CODE, 'NONE' ) = NVL( met_code, 'NONE' )
1008 and APPLICATION_ID = app_id
1009 and NVL( SET_OF_BOOKS_ID, 0 ) = NVL( sob_id, 0 )
1010 and v_startDate <= END_DATE;
1011 IF v_numRows > 0 THEN
1012 -- ERROR! there is a date overlap problem.
1013 FND_MESSAGE.SET_NAME('FND','UNIQUE-DATE OVERLAP');
1014 return( FND_SEQNUM.BADDATE ); /* comment out this line and uncomment the next for debugging */
1015 -- app_exception.raise_exception;
1016 END IF;
1017 END IF;
1018 ELSE
1019 -- see if there is another row in the database for the same document
1020 select count( DOC_SEQUENCE_ASSIGNMENT_ID ) into v_numRows
1021 from FND_DOC_SEQUENCE_ASSIGNMENTS
1022 where CATEGORY_CODE = cat_code
1023 and NVL( METHOD_CODE, 'NONE' ) = NVL( met_code, 'NONE' )
1024 and APPLICATION_ID = app_id
1025 and NVL( SET_OF_BOOKS_ID, 0 ) = NVL( sob_id, 0 );
1026 IF v_numRows > 0 THEN
1027 select count( DOC_SEQUENCE_ASSIGNMENT_ID ) into v_numRows
1028 from FND_DOC_SEQUENCE_ASSIGNMENTS
1029 where CATEGORY_CODE = cat_code
1030 and NVL( METHOD_CODE, 'NONE' ) = NVL( met_code, 'NONE' )
1031 and APPLICATION_ID = app_id
1032 and NVL( SET_OF_BOOKS_ID, 0 ) = NVL( sob_id, 0 )
1033 and
1034 ( ( v_startDate >= START_DATE and v_startDate <= NVL(END_DATE, v_endDate) )
1035 or ( v_endDate >= START_DATE and v_endDate <= NVL(END_DATE, v_endDate) )
1036 or ( v_endDate <= START_DATE and v_endDate >= NVL(END_DATE, v_endDate + 1) ) );
1037 IF v_numRows > 0 THEN
1038 -- ERROR! there is a date overlap problem.
1039 FND_MESSAGE.SET_NAME('FND','UNIQUE-DATE OVERLAP');
1040 return( FND_SEQNUM.BADDATE ); /* comment out this line and uncomment the next for debugging */
1041 -- app_exception.raise_exception;
1042 END IF;
1043 END IF;
1044 END IF;
1045 exception
1046 when others then
1047 fnd_message.set_name( 'FND', 'SQL-GENERIC ERROR' );
1048 fnd_message.set_token( 'ERRNO', sqlcode, FALSE );
1049 fnd_message.set_token( 'ROUTINE', 'assign_doc_seq', FALSE );
1050 fnd_message.set_token( 'REASON', sqlerrm, FALSE );
1051 fnd_message.set_token( 'ERRFILE', 'AFSQNUMB.pls', FALSE );
1052 fnd_message.set_token( 'SQLSTMT', 'select count( DOC_SEQUENCE_ASSIGNMENT_ID ) .. DATE checks', FALSE );
1053 app_exception.raise_exception;
1054 return( FND_SEQNUM.ORAFAIL );
1055 end; /* DATE check block */
1056
1057 /* hopefully we have checked enough to do an insert so lets pop the id sequence */
1058 begin /* get next DOC_SEQUENCE_ASSIGNMENT_ID block */
1059 select FND_DOC_SEQUENCE_ASSIGNMENTS_S.nextval into v_seqAssID from dual;
1060 exception
1061 when others then
1062 fnd_message.set_name( 'FND', 'SQL-GENERIC ERROR' );
1063 fnd_message.set_token( 'ERRNO', sqlcode, FALSE );
1064 fnd_message.set_token( 'ROUTINE', 'define_doc_seq', FALSE );
1065 fnd_message.set_token( 'REASON', sqlerrm, FALSE );
1066 fnd_message.set_token( 'ERRFILE', 'AFSQNUMB.pls', FALSE );
1067 fnd_message.set_token( 'SQLSTMT', 'select FND_DOC_SEQUENCE_ASSIGNMENTS_S.nextval from dual', FALSE );
1068 app_exception.raise_exception;
1069 return( FND_SEQNUM.ORAFAIL );
1070 end; /* FND_DOC_SEQUENCE_ASSIGNMENTS_S block */
1071
1072 insert into FND_DOC_SEQUENCE_ASSIGNMENTS ( DOC_SEQUENCE_ASSIGNMENT_ID, DOC_SEQUENCE_ID,
1073 APPLICATION_ID, CATEGORY_CODE, SET_OF_BOOKS_ID, METHOD_CODE, START_DATE, END_DATE,
1074 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN )
1075 values ( v_seqAssID, v_docSeqId, app_id, cat_code, sob_id, met_code, v_startDate, v_endDate,
1076 SYSDATE, FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID );
1077
1078 begin /* update the table name for the Doc_Seq */
1079 UPDATE FND_DOCUMENT_SEQUENCES
1080 set TABLE_NAME = v_prdTabNm
1081 where DOC_SEQUENCE_ID = v_docSeqId;
1082 exception
1083 when others then
1084 fnd_message.set_name( 'FND', 'SQL-GENERIC ERROR' );
1085 fnd_message.set_token( 'ERRNO', sqlcode, FALSE );
1086 fnd_message.set_token( 'ROUTINE', 'assign_doc_seq', FALSE );
1087 fnd_message.set_token( 'REASON', sqlerrm, FALSE );
1088 fnd_message.set_token( 'ERRFILE', 'AFSQNUMB.pls', FALSE );
1089 fnd_message.set_token( 'SQLSTMT', 'update FND_DOCUMENT_SEQUENCES set TABLE_NAME (..' || docseq_name || '..)', FALSE );
1090 app_exception.raise_exception;
1091 return( FND_SEQNUM.ORAFAIL );
1092 end; /* Doc_Seq update block */
1093
1094 IF v_type = 'G' THEN
1095 begin /* Create Gapless check block */
1096 select count( DOC_SEQUENCE_ID ) into v_numRows
1097 from FND_DOC_SEQUENCE_USERS where DOC_SEQUENCE_ID = v_docSeqId;
1098 IF v_numRows = 0 THEN
1099 v_numRows := FND_SEQNUM.create_gapless_sequence( v_docSeqId );
1100 IF v_numRows <> 1 THEN
1101 return( FND_SEQNUM.ORAFAIL );
1102 END IF;
1103 END IF;
1104 exception
1105 when others then
1106 fnd_message.set_name( 'FND', 'SQL-GENERIC ERROR' );
1107 fnd_message.set_token( 'ERRNO', sqlcode, FALSE );
1108 fnd_message.set_token( 'ROUTINE', 'assign_doc_seq', FALSE );
1109 fnd_message.set_token( 'REASON', sqlerrm, FALSE );
1110 fnd_message.set_token( 'ERRFILE', 'AFSQNUMB.pls', FALSE );
1111 fnd_message.set_token( 'SQLSTMT', 'select count(DOC_SEQUENCE_ID) from FND_DOC_SEQUENCE_USERS', FALSE );
1112 app_exception.raise_exception;
1113 return( FND_SEQNUM.ORAFAIL );
1114 end; /* Gapless check block */
1115 END IF;
1116
1117 -- ok i assume all is well let's return SEQSUCC
1118 return( FND_SEQNUM.SEQSUCC );
1119
1120 exception
1121 when others then
1122 fnd_message.set_name( 'FND', 'SQL-GENERIC ERROR' );
1123 fnd_message.set_token( 'ERRNO', sqlcode, FALSE );
1124 fnd_message.set_token( 'ROUTINE', 'assign_doc_seq', FALSE );
1125 fnd_message.set_token( 'REASON', sqlerrm, FALSE );
1126 fnd_message.set_token( 'ERRFILE', 'AFSQNUMB.pls', FALSE );
1127 fnd_message.set_token( 'SQLSTMT', 'insert into FND_DOC_SEQUENCE_ASSIGNMENTS (..' || docseq_name || '..)', FALSE );
1128 app_exception.raise_exception;
1129 return( FND_SEQNUM.ORAFAIL );
1130
1131 end assign_doc_seq;
1132
1133 end FND_SEQNUM;
1134
[Home] [Help]