Oracle® Fusion Applications Developer's Guide 11g Release 7 (11.1.7) Part Number E15524-08 |
|
|
PDF · Mobi · ePub |
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.6, "Enabling Document Sequences in ADF Business Components"
Section 11.8, "Integrating Document Sequence Task Flows into Oracle Fusion Functional Setup Manager"
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 determinant type and determinant value and are effective within a date range. |
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.
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.
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.
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) |
This section focuses on ADF Business Components integration of document sequences provided by Fusion Middleware extensions for Oracle Applications base classes.
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 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_DETERMINANT_VALUE (Ledger Id), * fnd:DOC_SEQ_DETERMINANT_TYPE * 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_DETERMINANT_VALUE (Ledger Id), * fnd:DOC_SEQ_DETERMINANT_TYPE * 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 detType Determinant Type * @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) {...}
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.
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 |
Category Code |
The document sequence category code. The Groovy expression should return an object of type |
Method Code |
The document sequence method code. Select from the following: |
Determinant Type |
The determinant type to use for this document sequence. The Groovy expression should return an object of type |
Determinant Value |
The determinant value to use for this document sequence. The Groovy expression should return an object of type |
Transaction Date |
The document transaction date. The Groovy expression should return an object of type |
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
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 det_type => 'BU'); -- Determinant Type value 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;
Example 11-5 Add Determinant Type
function define_doc_seq ( app_id in number, mod_id in varchar2, docseq_name in fnd_document_sequences.name%TYPE, docseq_type in fnd_document_sequences.type%TYPE, msg_flag in fnd_document_sequences.message_ flag%TYPE, init_value in fnd_document_sequences.initial_ value%TYPE, p_startDate in date, p_endDate in date default NULL, det_type in fnd_document_sequences.determinant_ type%TYPE default NULL, p_audit in boolean default true ) return number;
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' |
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' mode='edit' [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.