[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select type into t
from FND_DOCUMENT_SEQUENCES
where DOC_SEQUENCE_ID = dbseqid;
SELECT SEQ.DB_SEQUENCE_NAME,
SEQ.DOC_SEQUENCE_ID,
SA.doc_sequence_assignment_id
INTO dbseqnm, dbseqid, seqassid
FROM FND_DOCUMENT_SEQUENCES SEQ,
FND_DOC_SEQUENCE_ASSIGNMENTS SA
WHERE SEQ.DOC_SEQUENCE_ID = SA.DOC_SEQUENCE_ID
AND SA.APPLICATION_ID = appid
AND SA.CATEGORY_CODE = cat_code
AND (SA.METHOD_CODE = met_code or SA.METHOD_CODE is NULL)
AND (SA.SET_OF_BOOKS_ID = sobid or SA.SET_OF_BOOKS_ID is NULL)
/* bug 1019289, 1295363 - change between to add .9999 to end date -- also removed extra
todate() tochar() and work with straight dates */
AND trx_date between SA.START_DATE and nvl( SA.END_DATE + .9999, trx_date + .9999 );
v_proc_stmt := 'select ' || dbseqnm || '.nextval ' || 'into :next_val from sys.dual';
SELECT SEQ.DB_SEQUENCE_NAME into dbseqnm
FROM FND_DOCUMENT_SEQUENCES SEQ,
FND_DOC_SEQUENCE_ASSIGNMENTS SA
WHERE SEQ.DOC_SEQUENCE_ID = SA.DOC_SEQUENCE_ID
AND SA.APPLICATION_ID = appid
AND SA.CATEGORY_CODE = cat_code
AND (SA.METHOD_CODE = met_code or SA.METHOD_CODE is NULL)
AND (SA.SET_OF_BOOKS_ID = sobid or SA.SET_OF_BOOKS_ID is NULL)
-- bug 1019289, 1295363 - change between to add .9999 to end date, also removed trunc's
AND to_date( trx_date, decode( length(trx_date),
9,'DD-MON-RR',
10,'DD-MM-YYYY',
11,'DD-MON-YYYY',
'YYYY/MM/DD') ) between
sa.start_date and nvl(sa.end_date + .9999,to_date(trx_date,decode(length(trx_date),
9,'DD-MON-RR',
10,'DD-MM-YYYY',
11,'DD-MON-YYYY',
'YYYY/MM/DD')) +.9999);
proc_stmt := 'begin select ' || dbseqnm || '.nextval ' ||
'into :next_val from sys.dual; end;';
SELECT SEQ.DB_SEQUENCE_NAME into dbseqnm
FROM FND_DOCUMENT_SEQUENCES SEQ,
FND_DOC_SEQUENCE_ASSIGNMENTS SA
WHERE SEQ.DOC_SEQUENCE_ID = SA.DOC_SEQUENCE_ID
AND SA.APPLICATION_ID = appid
AND SA.CATEGORY_CODE = cat_code
AND (SA.METHOD_CODE = met_code or SA.METHOD_CODE is NULL)
AND (SA.SET_OF_BOOKS_ID = sobid or SA.SET_OF_BOOKS_ID is NULL)
/* bug 1019289, 1295363 - change between to add .9999 to end date also removed trunc
and date coversion routines not needed since this trx_date is a date */
AND trx_date between sa.start_date and nvl(sa.end_date + .9999, trx_date + .9999);
proc_stmt := 'begin select ' || dbseqnm || '.nextval ' ||
'into :next_val from sys.dual; end;';
select seq.doc_Sequence_id
from fnd_document_sequences seq
where seq.doc_sequence_id not in
(select su.doc_Sequence_id
from fnd_doc_sequence_users su)
and seq.type='G';
fnd_message.set_token('SQLSTMT', 'select seq.doc_Sequence_id ...', FALSE);
insert into fnd_doc_sequence_users
(doc_sequence_id,
doc_sequence_assignment_id,
user_id,
nextval,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
select seq.doc_sequence_id,
sa.doc_sequence_assignment_id,
0,
/* Bug 508093: removed '+1' that was added to the NVL of initial_value
not sure why it was there in the first place it
prevented the value of '1' from ever being used by
a gapless sequence - the forms now protects against
the zero value - which may have been why we had it
there - so now the default will be 1 instead of 2
Replaced NVL with Decode - now we proctect the API from
zero or NULL (as per request from MWARREN) and do not
depend on forms.
*/
DECODE(seq.initial_value, NULL, 1, 0, 1, seq.initial_value),
sysdate, 0, sysdate, 0, 0
from fnd_document_sequences seq,
fnd_doc_sequence_assignments sa
where seq.doc_sequence_id = sa.doc_sequence_id
and sa.doc_sequence_id = seqid
and sa.doc_Sequence_assignment_id =
(select min(doc_sequence_assignment_id)
from fnd_doc_sequence_assignments
where doc_sequence_id = seqid);
fnd_message.set_token('SQLSTMT', 'insert into fnd_doc_sequence_users ...', FALSE);
/* This function gets the nextval of a gapless/user sequence and update the
* next nextval.
* fds_user_id = 0 for gapless sequence, userid for user sequence
* seqassid = sequence_assignment_id - not used since bug 494345
* seqid = doc_sequence_id
*/
function get_next_user_sequence ( fds_user_id in number,
seqassid in number, -- not used
seqid in number) return number
is
v_nextVal number;
update fnd_doc_sequence_users
set nextval = nextval + 1
where user_id = fds_user_id and doc_sequence_id = seqid;
select distinct nextval-1
into v_nextVal
from fnd_doc_sequence_users
where doc_sequence_id = seqid and user_id = fds_user_id;
fnd_message.set_name( 'FND','SQL-ERROR SELECTING' );
fnd_message.set_token( 'SQLSTMT', 'update fnd_doc_sequence_users ... nextval', FALSE );
join to ALL_SEQUENCES -- instead we now call this routine at the PRE-INSERT
trigger of the define form for each record inserted. Thus we can pass the
new db_sequence_name and initial_value and create the DB Seq directly. The
only bad effect of calling from PRE-INSERT is that if something wierd happens
and the commit fails and AD_DDL does not the DB Seq has still been created,
this will not cause any functional problems since the call to create the name
is called before this call - FND_DOCUMENT_SEQUENCES_S.nextval has already been
fired and a duplicate name will not be generated - we do have an unused DB Seq
hanging around, but the odds of this happing are low since this the last thing
we do before the insert */
procedure create_db_seq ( db_seq_name in fnd_document_sequences.db_sequence_name%TYPE,
init_value in fnd_document_sequences.initial_value%TYPE )
is
v_ddl_sql varchar2(150);
SELECT fou.oracle_username into v_fnd_schema
FROM fnd_product_installations fpi,
fnd_oracle_userid fou,
fnd_application fa
WHERE fpi.application_id = fa.application_id
AND fpi.oracle_id = fou.oracle_id
AND fa.application_short_name = 'FND';
fnd_message.set_token('SQLSTMT', 'SELECT fou.oracle_username into v_fnd_schema ...', FALSE);
/* if we had a good status from the first select use the cache to populate this
request's output parameters */
docseq_id := v_SeqInfoCache.docseq_id;
the select time ... */
return( v_SeqInfoCache.retStat );
/* do our select into the cache */
select SEQ.DOC_SEQUENCE_ID, SEQ.TYPE, SEQ.NAME,
SEQ.AUDIT_TABLE_NAME, SEQ.DB_SEQUENCE_NAME, SEQ.TABLE_NAME,
SA.DOC_SEQUENCE_ASSIGNMENT_ID, SEQ.MESSAGE_FLAG,
SA.START_DATE, SA.END_DATE
into v_SeqInfoCache.docseq_id, v_SeqInfoCache.docseq_type, v_SeqInfoCache.docseq_name,
v_SeqInfoCache.aud_tab_name, v_SeqInfoCache.db_seq_name, v_SeqInfoCache.prd_tab_name,
v_SeqInfoCache.seq_ass_id, v_SeqInfoCache.msg_flag,
v_SeqInfoCache.startDate, v_SeqInfoCache.endDate
from FND_DOCUMENT_SEQUENCES SEQ, FND_DOC_SEQUENCE_ASSIGNMENTS SA
where SEQ.DOC_SEQUENCE_ID = SA.DOC_SEQUENCE_ID and
SA.APPLICATION_ID = v_SeqInfoCache.app_id and
SA.CATEGORY_CODE = v_SeqInfoCache.cat_code and
( SA.SET_OF_BOOKS_ID = v_SeqInfoCache.sob_id or SA.SET_OF_BOOKS_ID is NULL ) and
/* bug 1354846 - add the NULL compare to v_SeqInfoCache.met_code so the
assignment query can check for either type */
( SA.METHOD_CODE = v_SeqInfoCache.met_code or SA.METHOD_CODE is NULL
or v_SeqInfoCache.met_code is NULL ) and
/* bug 1019289, 1295363 - change between to add .9999 to end date and trx_date */
trx_date between SA.START_DATE and nvl( SA.END_DATE + .9999, trx_date + .9999 );
fnd_message.set_token( 'SQLSTMT', 'select SEQ.DOC_SEQUENCE_ID, SEQ.TYPE, SEQ.NAME ...', FALSE );
v_proc_stmt := 'INSERT INTO ' || aud_tab_name || ' (DOC_SEQUENCE_ID, DOC_SEQUENCE_VALUE, ' ||
'DOC_SEQUENCE_ASSIGNMENT_ID, CREATION_DATE, CREATED_BY) VALUES ( :seq_id, :val, ' ||
':asgn_id, sysdate, :cr_by)';
like they did in the 'C' code since the very next call from #FND SEQVAL updates
the audit record - where we can trap the error and report it. If there is a
requirement for an equivalent to #FND SEQCHK we can write it seperately */
ELSE
-- this should never happen, unless someone uses sqlplus on our tables form validates value...
return( FND_SEQNUM.BADTYPE );
select count( APPLICATION_ID ) into v_numRows
from FND_APPLICATION where APPLICATION_ID = app_id;
fnd_message.set_token( 'SQLSTMT', 'select count from FND_APPLICATION where APPLICATION_ID = ' || to_char(app_id), FALSE );
select count( NAME ) into v_numRows
from FND_DOCUMENT_SEQUENCES where NAME = docseq_name;
fnd_message.set_token( 'SQLSTMT', 'select count from FND_DOCUMENT_SEQUENCES where NAME = ' || docseq_name, FALSE );
select DECODE(app_id,0,'FND', 1,'FND', 101,'GL', 111,'RA', 140,'FA',
160,'ALR', 168,'RG', 200,'AP', 201,'PO', 222,'AR', 260,'CE',
300,'OE', 401,'INV', 500,'SA', 700,'MFG', 702,'BOM', 703,'ENG',
704,'MRP', 705,'CRP', 706,'WIP', 800,'PER', 801,'PAY', 802,'FF',
803,'DT', 804,'SSP', 7000,'JA', 7002,'JE', 7003,'JG', 7004,'JL',
'FND') || '_DOC_SEQUENCE_AUDIT' into v_audTabNm from dual;
fnd_message.set_token( 'SQLSTMT', 'select DECODE(' || to_char(app_id) || '...) from dual', FALSE );
/* hopefully we have checked enough to do an insert so lets pop the id sequence */
begin /* get next DOC_SEQUENCE_ID block */
select FND_DOCUMENT_SEQUENCES_S.nextval into v_docSeqId from dual;
fnd_message.set_token( 'SQLSTMT', 'select FND_DOCUMENT_SEQUENCES_S.nextval from dual', FALSE );
insert into FND_DOCUMENT_SEQUENCES ( DOC_SEQUENCE_ID, NAME, APPLICATION_ID,
AUDIT_TABLE_NAME, DB_SEQUENCE_NAME, MESSAGE_FLAG, TYPE, INITIAL_VALUE,
START_DATE, END_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN )
values ( v_docSeqId, docseq_name, app_id, v_audTabNm, v_dbSeqNm, msg_flag,
docseq_type, init_value, v_startDate, v_endDate, SYSDATE, FND_GLOBAL.USER_ID,
SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID );
fnd_message.set_token( 'SQLSTMT', 'insert into FND_DOCUMENT_SEQUENCES ..' || docseq_name || '...', FALSE );
select count( APPLICATION_ID ) into v_numRows
from FND_APPLICATION where APPLICATION_ID = app_id;
fnd_message.set_token( 'SQLSTMT', 'select count from FND_APPLICATION where APPLICATION_ID = ' || to_char(app_id), FALSE );
select DOC_SEQUENCE_ID, TYPE into v_docSeqId, v_type
from FND_DOCUMENT_SEQUENCES where NAME = docseq_name;
fnd_message.set_token( 'SQLSTMT', 'select DOC_SEQUENCE_ID from FND_DOCUMENT_SEQUENCES where NAME = ' || docseq_name, FALSE );
select TABLE_NAME into v_prdTabNm from FND_DOC_SEQUENCE_CATEGORIES
where CODE = cat_code and APPLICATION_ID = app_id;
fnd_message.set_token( 'SQLSTMT', 'select TABLE_NAME from FND_DOC_SEQUENCE_CATEGORIES where CODE = ' || cat_code, FALSE );
select ENABLED_FLAG into v_enabled from FND_DESCR_FLEX_COLUMN_USAGES
where APPLICATION_ID = 0 and APPLICATION_COLUMN_NAME = 'SET_OF_BOOKS_ID'
and DESCRIPTIVE_FLEXFIELD_NAME = 'Document Flexfield'
and DESCRIPTIVE_FLEX_CONTEXT_CODE = 'Global Data Elements';
select count( SET_OF_BOOKS_ID ) into v_numRows
from GL_SETS_OF_BOOKS where SET_OF_BOOKS_ID = sob_id;
fnd_message.set_token( 'SQLSTMT', 'select count from GL_SETS_OF_BOOKS where SET_OF_BOOKS_ID = ' || to_char( sob_id ), FALSE );
fnd_message.set_token( 'SQLSTMT', 'select ENABLED_FLAG from ... SET_OF_BOOKS_ID', FALSE );
select ENABLED_FLAG into v_enabled from FND_DESCR_FLEX_COLUMN_USAGES
where APPLICATION_ID = 0 and APPLICATION_COLUMN_NAME = 'METHOD_CODE'
and DESCRIPTIVE_FLEXFIELD_NAME = 'Document Flexfield'
and DESCRIPTIVE_FLEX_CONTEXT_CODE = 'Global Data Elements';
fnd_message.set_token( 'SQLSTMT', 'select ENABLED_FLAG from ... METHOD_CODE', FALSE );
select count( DOC_SEQUENCE_ASSIGNMENT_ID ) into v_numRows
from FND_DOC_SEQUENCE_ASSIGNMENTS
where CATEGORY_CODE = cat_code
and NVL( METHOD_CODE, 'NONE' ) = NVL( met_code, 'NONE' )
and APPLICATION_ID = app_id
and NVL( SET_OF_BOOKS_ID, 0 ) = NVL( sob_id, 0 )
and END_DATE IS NULL;
select count( DOC_SEQUENCE_ASSIGNMENT_ID ) into v_numRows
from FND_DOC_SEQUENCE_ASSIGNMENTS
where CATEGORY_CODE = cat_code
and NVL( METHOD_CODE, 'NONE' ) = NVL( met_code, 'NONE' )
and APPLICATION_ID = app_id
and NVL( SET_OF_BOOKS_ID, 0 ) = NVL( sob_id, 0 )
and v_startDate <= END_DATE;
select count( DOC_SEQUENCE_ASSIGNMENT_ID ) into v_numRows
from FND_DOC_SEQUENCE_ASSIGNMENTS
where CATEGORY_CODE = cat_code
and NVL( METHOD_CODE, 'NONE' ) = NVL( met_code, 'NONE' )
and APPLICATION_ID = app_id
and NVL( SET_OF_BOOKS_ID, 0 ) = NVL( sob_id, 0 );
select count( DOC_SEQUENCE_ASSIGNMENT_ID ) into v_numRows
from FND_DOC_SEQUENCE_ASSIGNMENTS
where CATEGORY_CODE = cat_code
and NVL( METHOD_CODE, 'NONE' ) = NVL( met_code, 'NONE' )
and APPLICATION_ID = app_id
and NVL( SET_OF_BOOKS_ID, 0 ) = NVL( sob_id, 0 )
and
( ( v_startDate >= START_DATE and v_startDate <= NVL(END_DATE, v_endDate) )
or ( v_endDate >= START_DATE and v_endDate <= NVL(END_DATE, v_endDate) )
or ( v_endDate <= START_DATE and v_endDate >= NVL(END_DATE, v_endDate + 1) ) );
fnd_message.set_token( 'SQLSTMT', 'select count( DOC_SEQUENCE_ASSIGNMENT_ID ) .. DATE checks', FALSE );
/* hopefully we have checked enough to do an insert so lets pop the id sequence */
begin /* get next DOC_SEQUENCE_ASSIGNMENT_ID block */
select FND_DOC_SEQUENCE_ASSIGNMENTS_S.nextval into v_seqAssID from dual;
fnd_message.set_token( 'SQLSTMT', 'select FND_DOC_SEQUENCE_ASSIGNMENTS_S.nextval from dual', FALSE );
insert into FND_DOC_SEQUENCE_ASSIGNMENTS ( DOC_SEQUENCE_ASSIGNMENT_ID, DOC_SEQUENCE_ID,
APPLICATION_ID, CATEGORY_CODE, SET_OF_BOOKS_ID, METHOD_CODE, START_DATE, END_DATE,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN )
values ( v_seqAssID, v_docSeqId, app_id, cat_code, sob_id, met_code, v_startDate, v_endDate,
SYSDATE, FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID );
begin /* update the table name for the Doc_Seq */
UPDATE FND_DOCUMENT_SEQUENCES
set TABLE_NAME = v_prdTabNm
where DOC_SEQUENCE_ID = v_docSeqId;
fnd_message.set_token( 'SQLSTMT', 'update FND_DOCUMENT_SEQUENCES set TABLE_NAME (..' || docseq_name || '..)', FALSE );
end; /* Doc_Seq update block */
select count( DOC_SEQUENCE_ID ) into v_numRows
from FND_DOC_SEQUENCE_USERS where DOC_SEQUENCE_ID = v_docSeqId;
fnd_message.set_token( 'SQLSTMT', 'select count(DOC_SEQUENCE_ID) from FND_DOC_SEQUENCE_USERS', FALSE );
fnd_message.set_token( 'SQLSTMT', 'insert into FND_DOC_SEQUENCE_ASSIGNMENTS (..' || docseq_name || '..)', FALSE );