HOW TO EASILY MODIFY ACCOUNT GENERATOR WORKFLOWS

ORACLE APPS TUTORIAL: HOW TO EASILY MODIFY ACCOUNT GENERATOR WORKFLOWS BY USING CUSTOM FUNCTIONS

If you are using any of the Purchasing, Payables, or Order Management and don’t like the accounting, you can modify the Account Generator workflow to achieve your desired results.  Unfortunately modifying a workflow is not a common skill set amongst Oracle developers.  To overcome this, I have put together a simple way to modify the workflow and keep the logic in PL/SQL (a skill all Oracle developers should know).  This allows you to get your desire results without hiring a workflow consultant and allows you to easily maintain and modify the workflow logic.  So, where are these Account Generator workflows used?

Account Generator Workflows in “Order to Cash” and “Procure to Pay”

There are two business flows where I have found it useful to customize the Account Generator workflows.  The first is the Procure to Pay cycle and the second is the Order to Cash Cycle.

In the Procure to Pay cycle, Purchasing and Payables use the following workflow processes to automatically derive the GL accounts in the purchasing distributions and payables distributions forms. Any vendor invoice or purchase order that references a project calls a special workflow process that must be customized because the seeded version contains a dummy process.  This dummy process returns an error by design.

In the 11i Order to Cash cycle, the Cost Manger program creates material distributions for Sales Order Issue transactions (shipments from Inventory to Customers).  The COGS workflow is called during this process to determine the Cost of Goods GL account.  If you don’t modify this workflow, the seeded version of this workflow will return the Cost of Goods account associated with the item.

In the R12 Order to Cash cycle, the Cost Manger program creates material distributions for Sales Order Issue transactions (shipments from Inventory to Customers).  The Sales Order Issue is booked to deferred Cost of Goods and reversed until you run the COGS recognition program in the Cost Management module.  This program will create a COGS recognition transaction.  It is this transaction that calls the COGS workflow to get the GL accounts.  The COGS recognition transaction is a logical transaction – meaning it does not affect inventory value.  Now that we know when and where we would modify the Account Generator workflows, let’s take a look at an example.

Modifying a Payables Account Generator Workflow Example

I’m using the Payables Project Supplier process because that is a workflow that needs to be modified for you to process project-related invoices in AP.  This example will work for any Account Generator workflow, you just need to know the workflow item type name.  I’ll provide a list of them at the end of this post.

In workflow builder, perform the following steps:

1.1.    Find the Project Supplier Invoice Account Generation workflow item type

1.2.    Create a copy of the Generate Default Account process

1.3.    Create custom attributes (one for each segment in your GL accounting flexfield)

Oracle Workflow Custom Attributes

1.4.    Create a custom workflow function

Oracle Workflow Custom Function

1.5.    Associate the attributes you wish to pass into the custom function

Oracle Workflow Navigator for Account Generator

1.6.    Modify the flow to first call the custom function then assign values to each attribute and then call the Validate Code Combination process

Oracle Account Generator Workflow Flow

Oracle Workflow Node Attributes

In a SQL tool, perform the following steps:

2.1.    Create a custom package with a procedure defined in 1.4.  Make sure the  parameters of the procedure follow the workflow standard:

(  itemtype IN VARCHAR2

        ,itemkey  IN VARCHAR2

        ,actid    IN NUMBER

        ,funcmode IN VARCHAR2

                        ,resultout   IN OUT VARCHAR2)

2.2.    In the procedure use the “wf_engine.GetItemAttrText “ procedure or the “wf_engine.GetItemAttrNumber” procedure to get the attributes from step 1.5

2.3.    Add the custom logic to derive the GL account segments

2.4.    In the procedure use the “wf_engine.setitemattrtext “ procedure to set the attribute values defined in step 1.3

In Oracle Applications, perform the following steps:

3.1.    In the Account Generator Process form, update the Process Name for the Project Supplier Invoice Account Generation Item Type with the same name as defined in step 1.2

Oracle Account Generator Processes