[Home] [Help]
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
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
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
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
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';
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 =
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
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,
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;
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 */
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
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 );
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,
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
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
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
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;
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:
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 );
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
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 */
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 );
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:
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,
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;
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,
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;
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;
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
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
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:
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
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: