Skip Headers

Oracle Financial Services Accounting Hub Implementation Guide
Release 12.1
Part Number E13420-04
Go to Table of Contents
Contents
Go to previous page
Previous
Go to next page
Next

Accounting Program and Period Close Integration

Accounting Program Workflow Business Events for Custom Integration

Note: Oracle Financial Services 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 Financial Services 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 Financial Services Accounting Hub enables customers to extend and customize the integration with the Financial Services 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:

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:

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.

Available Business Events in Document and Batch Mode
Business Events Document Mode Batch Mode
Pre-accounting   X
Transaction Objects X X
Post-processing X X
Post-accounting   X

Implementers of the Financial Services Accounting Hub at the customer site can subscribe to these workflow business events to perform the following application-specific tasks:

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.

Technical Implementation Steps and Conventions

This section includes the following:

Timing and Position of Workflow Business Events

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 picture is described in the document text

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.preaccounting (batch mode only)

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.

oracle.apps.xla.accounting.extract (batch and document mode)

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.

oracle.apps.xla.accounting.postprocessing (batch and document mode)

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.

oracle.apps.xla.accounting.postaccounting (batch mode only)

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.

XLA_POST_ACCTG_EVENTS_V
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

Parameter Specifications

This section describes parameters for the Create Accounting program workflow business events.

The table below describes the parameters for oracle.apps.xla.accounting.preaccounting.

oracle.apps.xla.accounting.preaccounting Parameters
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.

oracle.apps.xla.accounting.extract Parameters
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.

oracle.apps.xla.accounting.extract Parameters
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

oracle.apps.xla.accounting.postprocessing Parameters

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 .

oracle.apps.xla.accounting.postaccounting Parameters

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.

Application Subscription to Workflow Business Events

To integrate non-Oracle applications or customizations with the Financial Services 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.

See: Parameter Specifications

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.

Locking

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.

Transaction Control Statements

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.

Exception Handling

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.

Default Subscription to Postaccounting Workflow Business Event

The Financial Services 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.

Accounting Program Specifications

This section describes the Create Accounting program online and the batch mode API specifications.

Accounting Program Submission

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:

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.

Create Accounting Concurrent Request

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 picture is described in the document text

The table below describes the input parameters in Batch mode for the Create Accounting program.

Input Parameters in Batch Mode
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

Accounting Program API (Document Mode)

The Financial Services 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.

  1. 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
    
  2. 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.

Accounting Program API (Document Mode) Parameters
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.
  • 0 indicates everything is normal and events are accounted successfully.

  • 1 indicates one or more events are accounted in error for the document.

  • 2 indicates there is some technical problem and the API has encountered an unexpected error. The API actions are rolled back.

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

Accounting Program API (Batch Mode and Budgetary Control)

The Financial Services 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.

  1. 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);
    
    XLA_ACCT_PROG_EVENTS_GT
    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:
    • DRAFT

    • FINAL

    • FUNDS_RESERVE

    • FUNDS_CHECK

    p_accounting_batch_id Accounting batch identifier
    p_errbuf Error buffer
    p_retcode Return code. Values include:
    • 0 - Process completed successfully.

    • 1 - Process completed with errors.

    • 2 - Exception occurred; process cannot be completed.

  2. 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);
    
    XLA_ACCT_PROG_DOCS_GT
    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:
  • DRAFT

  • FINAL

  • FUNDS_RESERVE

  • FUNDS_CHECK

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:
  • 0 - Process completed successfully.

  • 1 - Process completed with errors.

  • 2 - Exception occurred; process cannot be completed.

Period Close Exceptions Report API

The XLA_EVENTS_PUB_PKG.PERIOD_CLOSE API is called by subledger application teams registered with the Financial Services 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);
Period Close Exceptions API Parameters Description
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:
  • FND_API.G_RET_STS_SUCCESS: No period close exceptions

  • FND_API.G_EXC_ERROR: Period close exceptions exist

  • FND_API.G_EXC_UNEXPECTED_ ERROR: Technical error

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