Note: Oracle Financials Accounting Hub provides workflow business events only to support customizations at the customer site. Therefore, they may not be used for integration between Oracle Applications and the Financials Accounting Hub.
These business events are seeded with the status Disabled. Customers need to enable them at their installations if they want to use them for customizations.
The Financials Accounting Hub enables customers to extend and customize the integration with the Financials Accounting Hub's Create Accounting program by providing business events they can subscribe to.
This chapter describes how to integrate with the Create Accounting program and the technical specifications on how to subscribe to the Accounting Program workflow business events. The workflow based business events architecture enables users to make Create Accounting program extensions more generic and extensible.
The Business Events System uses the Oracle Advanced Queuing (AQ) infrastructure to communicate business events between systems. The Business Events System consists of the Event Manager which enables users to do the following:
Register subscriptions to events that are significant to their systems
Model business events within workflow processes
When a local event occurs, the subscribing code is executed in the same transaction as the code that raised the event. Subscription processing can include the following:
Executing custom code on the event information
Sending event information to a workflow process
Sending event information to other queues or systems
Currently, the Create Accounting program raises the following workflow business events described in the table below which shows the available business events in each mode.
Business Events | Document Mode | Batch Mode |
---|---|---|
Pre-accounting | X | |
Transaction Objects | X | X |
Post-processing | X | X |
Post-accounting | X |
Implementers of the Financials Accounting Hub at the customer site can subscribe to these workflow business events to perform the following application-specific tasks:
Preaccounting
Accounting program extract
Postprocessing
Postaccounting
Note: This is only required if customers need to extend or customize the integration with the Accounting Program.
When the Create Accounting program is submitted, the preaccounting, extract, and postprocessing workflow business events are raised only if the Create Accounting flag is set to Yes. The postaccounting workflow business event is always raised no matter what the value of the Create Accounting flag is.
This section includes the following:
This section describes the life cycle of the Create Accounting program and the location of workflow business events.
The figure below shows the life cycle of the Create Accounting program and the location of workflow business events and is described in this chapter.
Create Accounting Process Life Cycle and Workflow Business Event:
The preaccounting event is raised at the beginning of the Create Accounting program after selecting accounting events for processing.
This section describes the following events:
oracle.apps.xla.accounting.extract (batch and document mode)
oracle.apps.xla.accounting.postprocessing (batch and document mode)
The preaccounting event is raised at the beginning of the Create Accounting program after selecting accounting events for processing.
Example: Custom applications can use this business event to identify transactions for processing.
This event is raised for all modes of accounting, final and draft, in batch as well as document mode. Events selected for the processing are passed to the subscription routine through the global temporary table XLA_EVENTS_GT. Implementers subscribing to this event can use it to populate transaction objects based on information in the XLA_EVENTS_GT table.
Example: Use this business event to populate transaction objects.
The postprocessing event is raised after creating subledger journal entries for each processing unit (commit unit). This event is raised if the Create Accounting parameter is set to Yes. The subscription to the postprocessing event uses the view XLA_POST_ACCTG_EVENTS_V based on XLA_EVENTS_GT to derive accounting events successfully accounted by the Create Accounting program.
Example: Use this business event to update posted flags on the transaction distributions to indicate whether the transaction was accounted successfully.
This event is raised after subledger journal entries are successfully committed in the database. If the Transfer to GL process is submitted as part of the accounting, then this workflow business event is raised after the transfer to General Ledger is completed successfully.
Example: A project is financially executed based on the funding that is set up according to the project's agreement. If funding is set up in foreign currency, the amount could change over time due to the fluctuation of the currency. In this example, the customer is maintaining the foreign currency amount in a separate custom table. Therefore, when the funding revaluation occurs, current funding is recalculated from the accounted amounts in the functional currency. In this case, the Funding Revaluation API uses the postaccounting business events.
The table below describes the view structure.
Column Name | Null? | Data Type | Description |
---|---|---|---|
EVENT_ID | Null | NUMBER(15) | Event internal identifier |
EVENT_NUMBER | Null | NUMBER(15) | Event number assigned to the event within the document |
APPLICATION_ID | Not Null | NUMBER(15) | Internal identifier for the application to which the event belongs |
EVENT_TYPE_CODE | Null | VARCHAR2(30) | Code for the event type that classifies the event being created |
EVENT_DATE | Null | DATE | Event or accounting date for the event |
ENTITY_ID | Null | NUMBER(15) | Internal identifier for the entity representing the actual document |
EVENT_STATUS_CODE | Null | VARCHAR2(1) | Event status code |
PROCESS_STATUS_CODE | Null | VARCHAR2(1) | Event process code |
TRANSACTION_NUMBER | Null | VARCHAR2(240) | Transaction number given to the document by the products owning the document |
LEDGER_ID | Not Null | NUMBER(15) | Ledger internal identifier to which event the belongs |
LEGAL_ENTITY_ID | Null | NUMBER(15) | Internal identifier for the legal entity |
ENTITY_CODE | Not Null | VARCHAR2(30) | Event entity type code |
SOURCE_ID_INT_1 | Null | NUMBER(15) | Placeholder column that stores internal identifier of the document being represented by the entity |
SOURCE_ID_INT_2 | Null | NUMBER(15) | Placeholder column that stores internal identifier of the document being represented by the entity |
SOURCE_ID_INT_3 | Null | NUMBER(15) | Placeholder column that stores internal identifier of the document being represented by the entity |
SOURCE_ID_INT_4 | Null | NUMBER(15) | Placeholder column that stores internal identifier of the document being represented by the entity |
SOURCE_ID_CHAR_1 | Null | VARCHAR2(30) | Placeholder column that stores internal identifier of the document being represented by the entity |
SOURCE_ID_CHAR_2 | Null | VARCHAR2(30) | Placeholder column that stores internal identifier of the document being represented by the entity |
SOURCE_ID_CHAR_3 | Null | VARCHAR2(30) | Placeholder column that stores internal identifier of the document being represented by the entity |
SOURCE_ID_CHAR_4 | Null | VARCHAR2(30) | Placeholder column that stores internal identifier of the document being represented by the entity |
REFERENCE_NUM_1 | Null | NUMBER | Reference information |
REFERENCE_NUM_2 | Null | NUMBER | Reference information |
REFERENCE_NUM_3 | Null | NUMBER | Reference information |
REFERENCE_NUM_4 | Null | NUMBER | Reference information |
REFERENCE_CHAR_1 | Null | VARCHAR2(240) | Reference information |
REFERENCE_CHAR_2 | Null | VARCHAR2(240) | Reference information |
REFERENCE_CHAR_3 | Null | VARCHAR2(240) | Reference information |
REFERENCE_CHAR_4 | Null | VARCHAR2(240) | Reference information |
REFERENCE_DATE_1 | Null | DATE | Reference information |
REFERENCE_DATE_2 | Null | DATE | Reference information |
REFERENCE_DATE_3 | Null | DATE | Reference information |
REFERENCE_DATE_4 | Null | DATE | Reference information |
EVENT_CREATED_BY | Null | VARCHAR2 (100) | Standard Who column |
This section describes parameters for the Create Accounting program workflow business events.
The table below describes the parameters for oracle.apps.xla.accounting.preaccounting.
Parameter Name | Description |
---|---|
APPLICATION_ID | Application identifier for which the Create Accounting program is submitted. The subscription routine checks this parameter first to see if it is the desired application. |
LEDGER_ID | Ledger identifier for which the Create Accounting program is submitted |
PROCESS_CATEGORY | Process category specified by users when launching the Create Accounting request |
END_DATE | End date specified by users |
ACCOUNTING_MODE | Indicates the mode in which the Create Accounting request is submitted |
VALUATION_METHOD | Valuation method specified by users |
SECURITY_ID_INT_1 SECURITY_ID_INT_2 SECURITY_ID_INT_3 SECURITY_ID_CHAR_1 SECURITY_ID_CHAR_2 SECURITY_ID_CHAR_3 |
Security context values as passed in as a parameter for the Create Accounting program |
The table below describes the parameters for oracle.apps.xla.accounting.extract.
Parameter Name | Description |
---|---|
APPLICATION_ID | Application identifier for which the Create Accounting program is submitted. The subscription checks this parameter first to see if it is the desired application. |
ACCOUNTING_MODE | Indicates the mode in which the Create Accounting program is submitted |
The table below describes the parameters for oracle.apps.xla.accounting.extract.
Parameter Name | Description |
---|---|
APPLICATION_ID | Application identifier for which the accounting program is submitted. The subscription checks this parameter first to see if it is the desired application. |
ACCOUNTING_MODE | Indicates the mode in which the Create Accounting program is submitted |
These parameters are the same as the parameters defined for oracle.apps.xla.accounting.extract as described in the oracle.apps.xla.accounting.extract table .
These parameters are the same as the parameters defined for oracle.apps.xla.accounting.preaccounting as described above in the oracle.apps.xla.accounting preaccounting table.
To integrate non-Oracle applications or customizations with the Financials Accounting Hub, you should subscribe to the workflow business event, if needed.
In the subscription routine, you can use the parameters passed with the workflow business event.
When applying a subscription to a workflow business event, you should use a phase number less than 99 for an immediate or a synchronous execution. Those subscriptions with phase number less than 99 are executed in the same database session as the Create Accounting program. Uncommitted data created by the Create Accounting program is visible in the subscription routine. You can use a phase number greater than 100. In this case, the subscription routine is deferred, and it can only see the committed data.
In order to improve the performance, it is recommended that you check the application ID as the first step in the subscription and exit if it does not match.
Transaction data should not be changed when the document is being processed by the Create Accounting program. One way to achieve this is by marking the documents or distributions with an In Process status during the execution of the preaccounting business event. Another way to prevent documents from updating is to issue a row level lock on the documents during the execution of accounting transaction objects business event. In the second approach, the transaction objects procedure locks documents selected for the processing in the current processing unit in the NOWAIT mode to prevent any modifications to the document. The lock is released once the Create Accounting program issues the commit for the processing unit.
The workflow does not allow a commit in the subscription routine. If a subscription raises an exception, the Workflow Business Event Manager rolls back all the subscriptions that were executed for that event and raises the exception to the Create Accounting program.
In case of an error, the product routine should raise the standard application exception by calling APP_EXCEPTION.RAISE_EXCEPTION and stack the corresponding error message using FND_MESSAGE APIs. The Create Accounting Program handles this exception and either stores this message as Error for the event or prints this message in the concurrent log.
If the preaccounting subscription fails, the Create Accounting program exits without creating journal entries. If the postaccounting subscription fails, it does not affect journal entries created in that batch since the postaccounting workflow business event is raised after the changes are saved in the database.
If extract and postprocessing subscriptions fail, then the thread that encountered the exception rolls back the processing unit and comes out. Previously processed processing units remain committed. However, the other threads continue their processing.
The Financials Accounting Hub team has created a default subscription workflow to postaccounting workflow business event to notify the user of the status of the Create Accounting request.
This section describes the Create Accounting program online and the batch mode API specifications.
The Create Accounting program generates subledger journal entries for a subledger application by processing accounting events.
See: Creating Subledger Journal Entries from Accounting Events
The Create Accounting program has the following execution modes:
batch
document
budgetary control
In batch mode, the Create Accounting program creates journal entries for a batch of documents or transactions. In this mode, the Create Accounting program is always submitted as a concurrent request.
In document mode, subledger journal entries are created for events belonging to a single document or a transaction. In this mode, the Create Accounting program can be executed synchronously or asynchronously as a concurrent request.
In budgetary control mode, the Create Accounting program processes only those events from the events table that require budgetary control. When called in budgetary control mode, the funds availability API is called and the result is populated to the journal entries. If a line fails budgetary control for an encumbrance entry, the original amount is replaced by zero and the reserve amount for the encumbrance line is adjusted according.
Note: The budgetary control mode is only available through APIs and cannot be requested through a concurrent request submission.
The Create Accounting program executes the accounting program API for a single document or for a batch of documents. The program has input parameters that determine the execution mode and the selection criteria for events. Some of the parameters are specific to batch mode and some are specific to document mode.
The Create Accounting program is registered as a concurrent program. It can be submitted as a request from the standard request submission form for batch mode accounting. The figure below shows the create accounting submission process submitted by the concurrent program and the Create Accounting Document Mode API.
Create Accounting Program Submission
The table below describes the input parameters in Batch mode for the Create Accounting program.
Parameter Name | Description | Prompt | Standard Concurrent Program |
---|---|---|---|
P_ACCOUNTING_MODE | Accounting mode; D for draft, F for final | Mode | Displayed to users; value from a value set |
P_APPLICATION_ID | Application identifier for which the Create Accounting program is going to execute | Not displayed to users; defaults from the profile option | |
P_CREATE_ACCOUNTING_FLAG | Indicates if the Create Accounting program is executed to create accounting or not; Y for yes, N for No | Create Accounting | Displayed to users. Value comes from a value set. |
P_END_DATE | End date puts a filter on the selection of events. Only events having event date before the end date are selected for accounting. | End Date | Displayed to users; defaults to the SYSDATE |
P_ERROR_ONLY_FLAG | Indicates if the accounting program should process only those entities that have failed events; Y for yes, N for no | Error Only | Displayed to users; value from a value set |
P_GL_BATCH_NAME | Batch name used by Transfer to GL to decide on the batch name for the batch created in General Ledger | General Ledger Batch Name | Displayed to users; a free text field |
P_LEDGER_ID | Internal identifier for the ledger for which the Create Accounting program is being executed | Ledger | Displayed to users; value from a value set |
P_POST_IN_GL_FLAG | Indicates if users want to submit General Ledger posting; Y for yes, N for no | Post in General Ledger | Displayed to users; value from a value set |
P_PROCESS_CATEGORY_CODE | Adds another filter criteria for the Create Accounting program to select events | Process Category | Displayed to users; value from a value set |
P_REPORT_STYLE_FLAG | Users can choose to decide on the details of the report. The report can be printed in Summary (S) or Detail (D). | Report | Displayed to users; value from a value set |
P_SECURITY_CHAR_1 | Adds filter criteria for the event selection | N/A | N/A |
P_SECURITY_CHAR_2 | Adds filter criteria for the event selection | N/A | N/A |
P_SECURITY_CHAR_3 | Adds filter criteria for the event selection | N/A | N/A |
P_SECURITY_INT_1 | This adds filter criteria for the event selection. | N/A | N/A |
P_SECURITY_INT_2 | Adds filter criteria for the event selection | N/A | N/A |
P_SECURITY_INT_3 | Adds filter criteria for the event selection | N/A | N/A |
P_TRANSFER_TO_GL_FLAG | Indicates whether the Create Accounting program should submit the Transfer to GL process | Transfer to General Ledger | Displayed to users; value from a value set |
P_VALUATION_METHOD_CODE | Adds filter criteria for the event selection | N/A | N/A |
P_SOURCE_APPLICATION_ID | Source application identifier. If specified, only accounting events created by this source application will be processed. |
See: Common Parameters
The Financials Accounting Hub has defined two APIs to submit the Create Accounting program for a single document. These APIs can be called to execute the Create Accounting program synchronously or asynchronously as a concurrent request.
PROCEDURE accounting_program_document
(p_event_source_info IN xla_events_pub_pkg.t_event_source_info
,p_application ID IN NUMBER DEFAULT NULL
,p_entity_id IN NUMBER
,p_accounting_flag IN VARCHAR2 DEFAULT 'Y'
,p_accounting_mode IN VARCHAR2
,p_transfer_flag IN VARCHAR2
,p_gl_posting_flag IN VARCHAR2
,p_offline_flag IN VARCHAR2
,p_accounting_batch_id OUT NOCOPY NUMBER
,p_errbuf OUT NOCOPY VARCHAR2
,p_retcode OUT NOCOPY NUMBER
,p_request_id OUT NOCOPY NUMBER)IS
PROCEDURE accounting_program_document
(p_event_source_info IN xla_events_pub_pkg.t_event_source_info
,p_application_id IN NUMBER DEFAULT NULL
,p_valuation_method IN VARCHAR2
,p_entity_id IN NUMBER
,p_accounting_flag IN VARCHAR2 DEFAULT 'Y'
,p_accounting_mode IN VARCHAR2
,p_transfer_flag IN VARCHAR2
,p_gl_posting_flag IN VARCHAR2
,p_offline_flag IN VARCHAR2
,p_accounting_batch_id OUT NOCOPY NUMBER
,p_errbuf OUT NOCOPY VARCHAR2
,p_retcode OUT NOCOPY NUMBER
,p_request_id OUT NOCOPY NUMBER)IS
The table below describes the parameters for the Accounting Program API, Document Mode.
Parameter | Description |
---|---|
p_event_source_info | Record type gives the source information of the event and identifies the source transaction that needs accounting. If passed NULL, then a NOT NULL value should be passed in p_entity_id. |
p_application_id | Application identifier for which the Create Accounting program is going to execute |
p_entity_id | Internal identifier for the information represented by p_event_source_info. If a value is passed in this parameter, then the events of the document, represented by this ID, is accounted. If NULL is passed, then a valid value is expected in p_event_source_info. |
p_accounting_flag | Indicates if the Create Accounting program should create accounting; Y for yes, N for no |
p_accounting_mode | Indicates if the accounting being built is in Final (F) or Draft (D) mode |
p_transfer_flag | Indicates if the Create Accounting program should transfer the entries to General Ledger; Y for yes, N for no |
p_gl_posting_flag | Indicates if the GL Posting should be submitted for the entries created; Y for yes, N for no |
p_offline_flag | Indicates whether the Create Accounting program should be submitted as a concurrent request (asynchronously) or should it be called as an API (synchronously); Y for asynchronous execution and N for synchronous execution |
p_accounting_batch_id | OUT parameter; returns the accounting batch ID to the caller. The accounting batch ID is stamped on the journal entries created. |
p_errbuf | OUT parameter; returns the completion message to the caller. The message can be an error message if the Create Accounting program encounters any fatal error. |
p_retcode | This OUT parameter returns the success code back to the caller.
|
p_request_id | If the Create Accounting program API is called with p_offline_flag = Y and it is submitted for asynchronous execution, this OUT parameter returns the request ID. |
p_valuation_method | Valuation method context for the transaction that corresponds to p_entity_id |
The Financials Accounting Hub has two APIs to create accounting online for a batch of documents or events called in batch mode. These APIs are also available in budgetary control mode.
XLA_ACCOUNTING_PUB_PKG.accounting_program_events (API)
Use this API to create accounting for a list of events specified in the XLA_ACCT_PROG_EVENTS_GT table below. When this API is called in budgetary control mode, all events in the batch must be budgetary control enabled.
PROCEDURE accounting_program_doc_batch
(p_application id IN INTEGER
,p_accounting_mode IN VARCHAR2
,p_gl_posting_flag IN VARCHAR2
,p_accounting_batch_id IN OUT NOCOPY INTEGER
,p_errbuf IN OUT NOCOPY VARCHAR2
,p_retcode IN OUT NOCOPY INTEGER);
Column Name | Data Type | Size | Required |
---|---|---|---|
EVENT_ID | NUMBER | 15 | Yes |
The table below describes the parameters for the XLA_ACCOUNTING_PUB_PKG.accounting_program_events API.
Parameter | Description |
---|---|
p_application_id | Application identifier |
p_accounting_mode | Accounting mode. Valid values include:
|
p_accounting_batch_id | Accounting batch identifier |
p_errbuf | Error buffer |
p_retcode | Return code. Values include:
|
XLA_ACCOUNTING_PUB_PKG.accounting_program_doc_batch (API)
Use this API to create accounting for a list of documents specified in the XLA_ACCT_PROG_DOCS_GT table below.
PROCEDURE accounting_program_events
(p_application id IN INTEGER
,p_accounting_mode IN VARCHAR2
,p_gl_posting_flag IN VARCHAR2
,p_accounting_batch_id IN OUT NOCOPY INTEGER
,p_errbuf IN OUT NOCOPY VARCHAR2
,p_retcode IN OUT NOCOPY INTEGER);
Column Name | Data Type | Size |
---|---|---|
ENTITY_ID | NUMBER | 15 |
LEDGER_ID | NUMBER | 15 |
ENTITY_TYPE_CODE | VARCHAR2 | 30 |
VALUATION_METHOD | VARCHAR2 | 30 |
SOURCE_ID_INT_1 | NUMBER | 15 |
SOURCE_ID_INT_2 | NUMBER | 15 |
SOURCE_ID_INT_3 | NUMBER | 15 |
SOURCE_ID_INT_4 | NUMBER | 15 |
SOURCE_ID_CHAR_1 | VARCHAR2 | 30 |
SOURCE_ID_CHAR_2 | VARCHAR2 | 30 |
SOURCE_ID_CHAR_3 | VARCHAR2 | 30 |
SOURCE_ID_CHAR_4 | VARCHAR2 | 30 |
Each row in the XLA_ACCT_PROG_DOCS_GT represents one transaction.
If the entity_id is populated, it is used to identify the transaction. The entity_id must be a valid entity_id in the xla_transaction_entities table or an exception is raised.
If the entity_id is not populated, the rest of the columns are used to identify the transaction. In this case, ledger_id and entity_type_code are required and the information should represent a valid entity in the xla_transaction_entities table. If the entity is not found, an exception is raised.
The table below describes the parameters for the Accounting Program API, Batch Mode.
Parameter | Description |
---|---|
p_application_id | Application identifier |
p_accounting_mode | Accounting mode. Valid values include:
|
p_gl_posting_flag | Indicates if the GL Posting should be submitted for the entries created; Y for Yes, N for No |
p_accounting_batch_id | Accounting batch identifier |
p_errbuf | Error buffer |
p_retcode | Return code. Values include:
|
The XLA_EVENTS_PUB_PKG.PERIOD_CLOSE API is called by subledger application teams registered with the Financials Accounting Hub to check for any unaccounted events or untransferred journal entries for the application, ledger, and period.
See: Subledger Period Close Exceptions Report, Oracle Subledger Accounting Implementation Guide
PROCEDURE XLA_EVENTS_PUB_PKG.PERIOD_CLOSE
(p_api_version
,x_return_status
,p_application_id
,p_ledger_id
,p_period_name);
Parameter | Description | Data Type | Type | Required |
---|---|---|---|---|
p_api_version | Standard business object API parameter; compares the version number of incoming calls to its current version The current version is 1.0. |
NUMBER | IN | YES |
x_return_status | Standard business object API parameter; represents the result of all the operations performed by the API and it has one of the following values:
|
VARCHAR | OUT | N/A |
p_application_id | Application identifier; must be a valid registered subledger application | INTEGER | IN | Yes |
p_ledger_id | Subledger application ledger identifier | INTEGER | IN | Yes |
p_period_name | Period name derived from the accounting date for which the exceptions need to be checked | VARCHAR2 | OUT | Yes |