FND Document Sequence Assignment From Back-end with a Script

Assign document sequences to multiple transaction types with a script. It will make it easier for you to assign a document sequences to transaction types in one go.

Use the below API to assign fnd document sequences to customer trx types:-

API Name :- FND_SEQNUM.assign_doc_seq

Package Name :- FND_SEQNUM

Function name and parameters:- 

function assign_doc_seq (

app_id in number,

docseq_name in fnd_document_sequences.name%TYPE,

cat_code         in fnd_doc_sequence_assignments.category_code%TYPE,

sob_id in fnd_doc_sequence_assignments.set_of_books_id%TYPE,

met_code in fnd_doc_sequence_assignments.method_code%TYPE,

p_startDate in date,

p_endDate in date default NULL

                );

You can call it with the below parameters :- 

ln_ret_num :=

            apps.FND_SEQNUM.assign_doc_seq (ln_appl_id,

                                         lc_seq_name,

                                         name,                                --lc_cat_code

                                         SET_OF_BOOKS_ID,     --ln_sob_id,

                                         NULL,                                 --lc_met_code,

                                         ld_start_date                        --Start Date of Duocument sequence assignment

                                            );

For an example I have written a script to assign sequence based on transaction types criteria.

-- Start of the script 

DECLARE

   ln_appl_id      NUMBER :=  222;    --   Fnd_application.application_id

   lc_seq_name     fnd_document_sequences.name%TYPE;          -- Sequence Name

   ln_ret_num      NUMBER;

   ld_start_date   DATE := SYSDATE;     -- Start Date of the sequence as SYSDATE

-- Below cursor can be modified based on the criteria you want to assign the sequence with

DECLARE

   CURSOR lcu_seq_assignemt

   IS

      SELECT rct.name,

             rct.TYPE,

             rct.set_of_books_id,

             rct.org_id

        FROM apps.ra_cust_Trx_types_All rct

       WHERE     rct.creation_date >= '01-AUG-2017'

             AND rct.end_date IS NULL

             AND NOT EXISTS

                        (SELECT 1

                           FROM APPS.FND_DOC_SEQUENCE_ASSIGNMENTS fd

                          WHERE     fd.category_code = rct.name

                                AND fd.set_of_books_id = rct.set_of_books_id

                                AND fd.end_date IS NULL);

BEGIN

   DBMS_OUTPUT.put_line ('START');

   FOR i IN lcu_seq_assignemt

   LOOP

      IF i.name = 'XYZ'

      THEN

         lc_seq_name := 'XYZ Sequene';

      ELSE

         lc_seq_name := 'NO_SEQ_ASSIGNED';

      END IF;

      IF lc_seq_name <> 'NO_SEQ_ASSIGNED'

      THEN

         ln_ret_num :=

            apps.FND_SEQNUM.assign_doc_seq (ln_appl_id,

                                            lc_seq_name,

                                            i.name,              --lc_cat_code

                                            i.SET_OF_BOOKS_ID,    --ln_sob_id,

                                            NULL,               --lc_met_code,

                                            ld_start_date);

         DBMS_OUTPUT.put_line ('Post API ');

         IF ln_ret_num = 0 -- For successful sequence creation this will return 0.

         THEN

            COMMIT;

            DBMS_OUTPUT.put_line ('Success ');

         ELSE

            DBMS_OUTPUT.put_line ('Fail :' || ln_ret_num);

            ROLLBACK;

         END IF;

      ELSE

         DBMS_OUTPUT.PUT_LINE (

            'No asssignment done for - i.BU_CODE::' || SQLERRM);

      END IF;

   END LOOP;

   DBMS_OUTPUT.put_line ('END ');

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Érror : ' || SQLERRM);

END;

/

Disclaimer :- Please modify the criteria based on your requirement.