[Home] [Help]
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)
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)
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)
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)
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
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
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 );
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:
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 )