11 Setting Up Document Sequences

This chapter describes how to set up document sequences, which uniquely number documents, provide proof of completeness, and create audit trails.

This chapter contains the following sections:

Section 11.1, "Introduction to Document Sequences"
Section 11.2, "Defining Document Sequence Categories"
Section 11.3, "Assigning a Document Sequence"
Section 11.4, "Striping Document Sequence Assignments"
Section 11.5, "Defining a Document Sequence Audit Table"
Section 11.6, "Enabling Document Sequences in ADF Business Components"
Section 11.7, "Managing PL/SQL APIs"
Section 11.8, "Integrating Document Sequence Task Flows into Oracle Fusion Functional Setup Manager"

11.1 Introduction to Document Sequences

A document sequence uniquely numbers documents generated by an Oracle Fusion application. Using Oracle Fusion applications, you initiate a transaction by entering data through a form and generating a document, for example, an invoice. A document sequence generates an audit trail that identifies the application that created the transaction, for example, Oracle Receivables, and the original document that was generated, for example, invoice number 1234.

Document sequences can provide proof of completeness. For example, document sequences can be used to account for every transaction, even transactions that fail. Document sequences generate audit data, so even if documents are deleted, their audit records remain.

Document sequences can also provide an audit trail. For example, a document sequence can provide an audit trail from the general ledger into the subsidiary ledger, and to the document that originally affected the account balance.

There are three types of document sequence numbering:

Automatic - Assigns a unique number to each document as it is generated. Automatic numbering is sequential by date and time of creation.
Gapless - Automatically generates a unique number for each document, but ensures that the document was successfully generated before assigning the number. With gapless numbering, no sequence numbers are lost due to incomplete or failed document creation.

Note:

It is recommend that you choose this type only when gapless numbering is essential, as it may affect the performance of your system.
Manual - Requires a user to assign a unique number to each document before it is generated. With manual numbering, numerical ordering and completeness is not enforced. Users can skip or omit numbers when entering the sequence value.

Table 11-1 defines document-sequence terminology.

Table 11-1 Document Sequence Terminology

Term	Description
Document Sequences	Document sequences are owned by a product and can be assigned to categories that belong to the same product as the sequence. Sequences can be automatic, manual, or gapless, and are effective within a date range.
Document Sequence Categories	A document sequence category belongs to a table, which is owned by a product. Document sequence categories are entered with either the System Administrator form (Payables and Cash Management) or product forms (General Ledger and Receivables).
Sequence Assignments	The user assigns a sequence for each category. The assignments are owned by a set of books and are effective within a date range. Manual document entry through a form and automatic document creation through a batch process can have separate sequence assignments. Currently, legal entities for the same set of books must share document sequences. If each legal entity requires its own numbering sequence, a separate set of books must be created for each legal entity.

11.2 Defining Document Sequence Categories

Document sequence categories organize documents into logical groups.

A document sequence category is one of the rules you use to define which documents a sequence assigns numbers to.
You can separately number each document sequence category by assigning a different sequence to each category.

A document sequence category identifies the database table that stores documents resulting from transactions your users enter. When you assign a sequence to a category, the sequence numbers the documents that are stored in a particular table.

11.3 Assigning a Document Sequence

Before you can assign a sequence to number documents, you must define which documents are to be numbered.

Defining a sequence is different from assigning a sequence to a series of documents.

A sequence's definition determines whether a document's number is automatically generated or manually entered by the user.
A sequence's assignment, that is, the documents a sequence is assigned to, is defined in the Sequence Assignments form.

11.4 Striping Document Sequence Assignments

Document sequences can optionally be given a determinant type when defined. This determinant type specifies the context "dimension" that will be used to stripe assignments of this document sequence. If a determinant type has been specified, then each assignment of this document sequence must include a particular value (or "determinant value") for this context. At runtime, only those document sequence assignments with a determinant value matching the current value for this dimension in the user's session context will be visible. If no determinant type is defined, then all assignments are considered global and are always visible.

Table 11-2 shows the possible values for the determinant type.

Table 11-2 Determinant Type Values

Determinant Type Code	Name
LEDGER	Ledger
LE	Legal Entity
BU	Business Unit

For example, if a document sequence is defined with determinant type of "Ledger", then each assignment of this document sequence must include a particular Ledger value. At runtime, only assignments that match the current Ledger value will be visible.

11.5 Defining a Document Sequence Audit Table

Each time a C or PL/SQL call is made to request the next document sequence value, this audit data is inserted into the corresponding product team's document sequence audit table.

Product teams using FND Document Sequence need to create an audit table with a name whose format is application_short_name_DOC_SEQUENCE_AUDIT, where application_short_name is the name of the application. For example, "AR_DOC_SEQUENCE_AUDIT."

The audit table must contain the columns and types shown in Table 11-3.

Table 11-3 Audit Table Columns and Types

Name	Null?	Type
DOC_SEQUENCE_ID	NOT NULL	NUMBER(18)
DOC_SEQUENCE_VALUE	NOT NULL	NUMBER(15)
DOC_SEQUENCE_ASSIGNMENT_ID	NOT NULL	NUMBER(18)
CREATION_DATE	NOT NULL	TIMESTAMP(6)
CREATED_BY	NOT NULL	VARCHAR2(64 CHAR)
LAST_UPDATE_DATE	NOT NULL	TIMESTAMP(6)
LAST_UPDATED_BY	NOT NULL	VARCHAR2(64 CHAR)
LAST_UPDATE_LOGIN		VARCHAR2(32 CHAR)
ENTERPRISE_ID	NOT NULL	NUMBER(18)

11.6 Enabling Document Sequences in ADF Business Components

This section focuses on ADF Business Components integration of document sequences provided by Fusion Middleware extensions for Oracle Applications base classes.

11.6.1 Using the Document-Sequence Extension

The document sequence is generated and validated in the postChanges() method of the OAEntityImpl class. In automatic mode, it is generated by calling the public API Long getDocSequence(Long appId, String categoryCode, Long sobId, String methodCode, Timestamp txnDate, Long seqVal, String suppressWarn, String suppressError) in the OAEntityImpl class. In manual mode, it is validated by calling public void validateDocSequence(Long appId, String categoryCode, Long sobId, String methodCode, Timestamp txnDate, Long seqVal, String suppressWarn, String suppressError) in the same class.

You do not need to do anything in order to get the default behavior of generation and validation of a document sequence. However, if you require some special behavior, such as additional validation or adding an additional prefix or suffix, you can override these methods.

The Javadoc for the key methods in OAEntityImpl is shown in Example 11-1.

Example 11-1 Javadoc for OAEntityImpl

/**
   * Override of EntityImpl.postChanges() to handle document sequencing.
   * If an entity attribute has been identified that it should be populated
   * using a document sequence (in the Applications Property Inspector panel),
   * then at this point in the entity life cycle, we will populate the attribute
   * with a document sequence based on the inputs, provided the sequence method
   * is automatic. If the document sequence is manual, we will validate the
   * document sequence.
   * See parent class for complete documentation
   * @param e this Entity Object's transaction event.
   * @see #validateDocSequence
   * @see #getDocSequence
   * @see EntityImpl#postChanges
   */
   public void postChanges(TransactionEvent e){...}
  
  /**
   * Will populate the entity attribute with a document sequence in Automatic
   * mode, based on the schema based properties being set on the attribute using
   * Applications Property Inspector in the Entity Attribute editor in JDev for
   * fnd:DOC_SEQUENCE (Document Sequence),
   * fnd:DOC_SEQ_APPLICATION_ID (Application Id),
   * fnd:DOC_SEQ_METHOD_CODE (Method Code),
   * fnd:DOC_SEQ_CATEGORY_CODE (Category Code),
   * fnd:DOC_SEQ_SET_OF_BOOKS_ID (Ledger Id),
   * fnd:DOC_SEQ_TXN_DATE (Transaction Date)
   * Application Id, Method Code, Category Code, Ledger Id and Transaction Code,
   * should be populated with valid Groovy expressions.
   * The Groovy expressions when evaluated should return a Long for Application Id,
   * and Ledger Id, String for Method Code and Category Code, Timestamp for Transaction Date fields.
   *
   * This method will be invoked by postChanges() method on the entity, when
   * posting the data to the database.
   *
   * Override this if you want a different behavior/way of populating the document sequence.
   *
   * @param appId Application Id
   * @param categoryCode Document Sequence Category Code
   * @param sobId Ledger Id to use for this Document Sequence.
   * @param methodCode Document sequence Method Code (Automatic ("A"), Manual ("M") or null for both modes).
   * @param txnDate Document Transaction Date
   * @param seqVal Document Sequence Value to use in Manual Mode
   * @param suppressWarn Suppress warning (Y/N/null)
   * @param suppressError Suppress Error (valid values are Y/N/null)
   * @return Document Sequence Value
   * @see #postChanges
   */
    public Long getDocSequence(Long appId, String categoryCode, Long sobId, String methodCode, Timestamp txnDate, Long seqVal, String suppressWarn, String suppressError)
    {...}
   
    /**
     * Will validate the entity attribute with a document sequence value to use in Manual
     * mode, based on the schema based properties being set on the attribute using
     * Applications Property Inspector in the Entity Attribute editor in JDev for
     * fnd:DOC_SEQUENCE (Document Sequence),
     * fnd:DOC_SEQ_APPLICATION_ID (Application Id),
     * fnd:DOC_SEQ_METHOD_CODE (Method Code),
     * fnd:DOC_SEQ_CATEGORY_CODE (Category Code),
     * fnd:DOC_SEQ_SET_OF_BOOKS_ID (Ledger Id),
     * fnd:DOC_SEQ_TXN_DATE (Transaction Date)
     * Application Id, Method Code, Category Code, Ledger Id and Transaction Code,
     * should be populated with valid Groovy expressions.
     * The Groovy expressions when evaluated should return a Long for Application Id,
     * and Ledger Id, String for Method Code and Category Code, Timestamp for Transaction Date fields.
     *
     * This method will be invoked by postChanges() method on the entity, when
     * posting the data to the database.
     *
     * Exception will be raised if document sequence value validation fails.
     *
     * Override this if you want a different behavior/way of validating the document sequence.
     *
     * @param appId Application Id
     * @param categoryCode Document Sequence Category Code
     * @param sobId Ledger Id to use for this Document Sequence.
     * @param methodCode Document sequence Method Code (Automatic ("A"), Manual ("M") or null for both modes).
     * @param txnDate Document Transaction Date
     * @param seqVal Document Sequence Value in Manual Mode
     * @param suppressWarn Suppress warning (Y/N/null)
     * @param suppressError Suppress Error (valid values are Y/N/null)
     * @see #postChanges
     */
    public void validateDocSequence(Long appId, String categoryCode, Long sobId, String methodCode, Timestamp txnDate, Long seqVal, String suppressWarn, String suppressError)
    {...}

11.6.1.1 What Happens with Document Sequences at Design Time

Oracle Fusion Middleware extensions provide the ability to identify if an entity attribute needs a document sequence. This is accomplished by setting Document Sequence to true in the entity attribute's Property Inspector window, as shown in Figure 11-1.

Figure 11-1 Property Inspector: Document Sequence

By default, the entity attribute property is not set because it is not a document sequence field.

Fusion Middleware extensions also capture the additional metadata (as Groovy expressions) needed to generate a document sequence. Table 11-4 lists the metadata fields in the Property Inspector window and their descriptions.

Table 11-4 Additional Metadata

Field	Description
Application Id	The application ID. The Groovy expression should return an object of type `Long`.
Category Code	The document sequence category code. The Groovy expression should return an object of type `String`.
Method Code	The document sequence method code. Select from the following: `"A"` (Automatic), `"M"` (Manual), or `null` (both modes). The Groovy expression should return an object of type `String`.
Ledger Id	The ledger ID to use for this document sequence. The Groovy expression should return an object of type `Long`.
Transaction Date	The document transaction date. The Groovy expression should return an object of type `Timestamp`.

11.6.1.2 What Happens with Document Sequences at Runtime

Based on the design time setting and additional metadata, Fusion Middleware extensions invoke document sequencing APIs and populate the attribute with a document sequence (in automatic mode) and validate the document sequence (in manual mode) in the postChanges() phase of the entity in ADF Business Components lifecycle. Document sequence processing is done at this phase so that the document sequence generation can be delayed as much as possible when in automatic mode. This is to avoid the potential wasting of document sequence if generated earlier.

If Document Sequence is not set or is set to false, nothing is done. If Document Sequence is set to true, Fusion Middleware extensions populate the entity attribute with a document sequence value if the method code is automatic.

This is accomplished by doing the following.

Evaluating the Groovy expressions corresponding to the additional required metadata
Invoking the document sequence PL/SQL APIs to do one of the following:
- Generate a document sequence value when invoked in automatic mode
- Validate the document sequence when invoked in manual mode

11.7 Managing PL/SQL APIs

Document sequence public PL/SQL APIs can be found in the FND_SEQNUM package. The package can be used to retrieve information about document sequences and assignments, create new document sequences or assignments, and to verify or retrieve the next sequence value for a particular document sequence assignment. Sample APIs are shown in the examples that follow. For complete documentation, see comments in the package header.

Example 11-2 Define a New Document Sequence

declare
  ret number;
begin
  -- Define a new document sequence
  ret := fnd_seqnum.define_doc_seq(
           app_id      => 222,               -- Application ID
           docseq_name => 'MY_DOC_SEQUENCE', -- Unique Doc_Seq Name
           docseq_type => 'A',               -- Sequence Type 
                                            ('A'=automatic,'G'=gapless,'M'=manual)
           msg_flag    => 'Y',               -- Message Flag
           init_value => 1,                  -- Initial sequence value
           start_date => sysdate,            -- Effective Start date
           end_date   => null);              -- Effective End date
  if (ret <> FND_SEQNUM.SEQSUCC) then
    dbms_output.put_line('Fail: '||to_char(ret));
  end if;
end;

Example 11-3 Retrieve the Next Sequence Value for an Automatic Sequence

declare
  ret number;
  docseq_val number;
  docseq_id number;
begin 
  -- Retrieve the next sequence value for an Automatic sequence
  ret := fnd_seqnum.get_seq_val(
           app_id      => 222,               -- Application ID
           cat_code    => 'MY_CAT',          -- Category code
           sob_id      => 12345,             -- Determinant value
           met_code    => 'A',               -- Method Code ('A'=automatic/batch, 
                                                'M'=manual)
           trx_date    => sysdate,           -- Transaction date
           seq_val     => docseq_val,        -- Doc Seq value (output value for 
                                                automatic)
           docseq_id   => docseq_id);        -- Doc Seq ID (output)
  if (ret <> FND_SEQNUM.SEQSUCC) then
    dbms_output.put_line('Fail: '||to_char(ret));
  else
    dbms_output.put_line('Next sequence value is '||to_char(docseq_val));
  end if;
end;

Example 11-4 Verify a Sequence Value for a Manual Sequence

declare
  ret number;
  docseq_val number;
  docseq_id number;
begin 
  -- Verify a sequence value for an Manual sequence
  docseq_val := 54321;
  ret := fnd_seqnum.get_seq_val(
           app_id      => 222,               -- Application ID
           cat_code    => 'MY_CAT',          -- Category code
           sob_id      => 12345,             -- Determinant value
           met_code    => 'A',               -- Method Code ('A'=automatic/batch, 
                                                'M'=manual)
           trx_date    => sysdate,           -- Transaction date
           seq_val     => docseq_val,        -- Doc Seq value (output value for 
                                                automatic)
           docseq_id   => docseq_id);        -- Doc Seq ID (output)
  if (ret <> FND_SEQNUM.SEQSUCC) then
    dbms_output.put_line('Fail: '||to_char(ret));
  else
    dbms_output.put_line('Value '||to_char(docseq_val)||' is valid');
  end if;
end;

11.8 Integrating Document Sequence Task Flows into Oracle Fusion Functional Setup Manager

Every Oracle application registers task flows with a product called Oracle Fusion Functional Setup Manager. Functional Setup Manager provides a single, unified user interface that allows customers and implementers to configure all Oracle applications by defining custom configuration templates or tasks based on their business needs.

The Functional Setup Manager UI enables customers and implementers to select the business processes or products that they want to implement. For example, an Accounts Payable application can register a setup activity like "Create Invoice" with Functional Setup Manager. After you define a category, for example, "Invoices," in the Categories task flow, document sequence task flows then provide the mechanism that allows you to define a sequence and specify its properties. You then can assign the sequence to the "Invoices" category in the Assignments region in the same flow.

Table 11-5 lists the task flows and their parameters.

Table 11-5 Task Flow Parameters

Task Flow Name	Task Flow XML	Parameters Passed	Behavior	Comments
Manage Document Sequence Categories	/WEB-INF/oracle/apps/fnd/applcore/docseq/ui/flow/ManageDocSeqCategoriesTF.xml#ManageDocSeqCategoriesTF	mode='search' [moduleType] [moduleKey] mode='edit' applicationId code [pageTitle]	Allows you to create and edit document sequence categories. Search mode allows you to search and edit categories. The moduleType and moduleKey parameters are optional, and if passed restrict the categories that can be queried and edited. Edit mode allows you to query and edit a single category. The applicationId and code parameters are required, and specify the category to edit.	Search and edit document sequence categories.
Manage Document Sequences	/WEB-INF/oracle/apps/fnd/applcore/docseq/ui/flow/ManageDocSequencesTF.xml#ManageDocSequencesTF	mode='search' [moduleType] [moduleKey] mode='edit' name [pageTitle]	Allows you to search and edit document sequences, and assignments of those sequences. Search mode allows you to query and edit sequences. The moduleType and moduleKey parameters are optional; if passed they restrict the sequences that can be queried. Edit mode allows you to edit a single document sequence and its assignments. The name parameter is required, and specifies the sequence to edit.	Search and edit document sequences (and the assignments belonging to a sequence).

Task Flow Name

Task Flow XML

Parameters Passed

Behavior

Comments

Manage Document Sequence Categories

/WEB-INF/oracle/apps/fnd/applcore/docseq/ui/flow/ManageDocSeqCategoriesTF.xml#ManageDocSeqCategoriesTF

mode='search'
[moduleType]
[moduleKey] mode='edit'
applicationId
code [pageTitle]

Allows you to create and edit document sequence categories. Search mode allows you to search and edit categories. The moduleType and moduleKey parameters are optional, and if passed restrict the categories that can be queried and edited. Edit mode allows you to query and edit a single category. The applicationId and code parameters are required, and specify the category to edit.

Search and edit document sequence categories.

Manage Document Sequences

/WEB-INF/oracle/apps/fnd/applcore/docseq/ui/flow/ManageDocSequencesTF.xml#ManageDocSequencesTF

mode='search'
[moduleType]
[moduleKey]

mode='edit'
name

[pageTitle]

Allows you to search and edit document sequences, and assignments of those sequences. Search mode allows you to query and edit sequences. The moduleType and moduleKey parameters are optional; if passed they restrict the sequences that can be queried. Edit mode allows you to edit a single document sequence and its assignments. The name parameter is required, and specifies the sequence to edit.

Search and edit document sequences (and the assignments belonging to a sequence).

For more information about task flows, see Oracle Fusion Applications Common Implementation Guide.