How to upload Sequences Definition & Assignment?

[admin]

A new topic has been added in the document to demonstrate the way to create document sequences and assignment using insert statements from back end. Please use the following URL to download the document,

topic.asp?TOPIC_ID=16

[admin]

It is a common requirement to create sequences for each financial years and there is no interface available to create and assign document sequences. Here I am going to share a solution and it is strongly recommended that test it on TEST server and extensively test it.

1-- We will create new sequences based on previous year sequences start_date between '01-JUL-2007' and '30-JUN-2008'. My assumption is that in each sequence name year is included like 'XX_2008_YYYY' so I am replacing it with 2009, REPLACE (fds.NAME, '2008', '2009').

INSERT INTO fnd_document_sequences
(doc_sequence_id, NAME, last_update_date, last_updated_by, creation_date, created_by, last_update_login,
application_id, audit_table_name, message_flag, start_date, TYPE,
db_sequence_name, end_date, initial_value)
(SELECT fnd_document_sequences_s.NEXTVAL,
REPLACE (fds.NAME,
'2008',
'2009'
),
SYSDATE, 1110, SYSDATE, 1110, 1625801,
fds.application_id, audit_table_name, 'Y', '01-JUL-2008', TYPE,
'FND_DOC_SEQ_' || fnd_document_sequences_s.CURRVAL || '_S', '30-JUN-2009', '80000001'
FROM fnd_document_sequences fds
WHERE start_date between '01-JUL-2007' and '30-JUN-2008' and fds.name not in (select name from fnd_document_sequences
where start_date between '01-JUL-2008' and '30-JUN-2009'));

2-- Commit;
3-- Direct output of the following query into a spool file.

SELECT 'CREATE SEQUENCE APPLSYS.' || db_sequence_name ||
' START WITH ' || INITIAL_VALUE || ' MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE NOCACHE ORDER;' || chr(10) ||
'CREATE SYNONYM APPS.' || db_sequence_name || ' FOR APPLSYS.' || db_sequence_name || ';'
FROM fnd_document_sequences fds
WHERE start_date between '01-JUL-2007' and '30-JUN-2008';

4-- Now execute this spool file in sqlplus SQL > sequences.sql.
5-- Our sequences creation is complete and now we have to assign to document categories.

INSERT INTO fnd_doc_sequence_assignments
(doc_sequence_assignment_id, last_update_date, last_updated_by,
creation_date, created_by, last_update_login, application_id,
doc_sequence_id, category_code, start_date, end_date,
set_of_books_id, method_code)
( SELECT fnd_doc_sequence_assignments_s.NEXTVAL,
SYSDATE, 1110, SYSDATE,
1110, 1110, sq.application_id,
(SELECT fds.doc_sequence_id
FROM fnd_document_sequences fds
WHERE fds.NAME = REPLACE (sq.NAME, '2008', '2009')), sc.code,
(SELECT fds.start_date
FROM fnd_document_sequences fds
WHERE fds.NAME = REPLACE (sq.NAME, '2008', '2009')),
(SELECT fds.end_date
FROM fnd_document_sequences fds
WHERE fds.NAME = REPLACE (sq.NAME, '2008', '2009')), 1001,
ds.method_code
FROM fnd_doc_sequence_categories sc,
fnd_doc_sequence_assignments ds,
fnd_document_sequences sq
WHERE sc.code = ds.category_code
AND ds.doc_sequence_id = sq.doc_sequence_id
AND sq.start_date between '01-JUL-2007' and '30-JUN-2008'

6- Commit;

<b>Warning:</b> We will not be responsible for any mistake made or not properly using the script and not testing on TEST instance. Right now this is only for TEST so do not apply on PROD and if you will then at your own risk and testing.

Please give your feedback and let us know if any improvement is possible. Thanks