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

Custom Account Generation Process

Hi,

I am going to explain you about the customization of Account Generation Processes/Account generator workflows in various modules in Oracle Applications.

For all modules the basic profile option to run account generator workflow is "Account Generator:Run in Debug Mode". So Set the profile "Account Generator:Run in Debug Mode" to "Yes"

Account Payables:

 Workflow Name: Project Supplier Invoice Account Generation Process

 Internal Name:     PAAPINVW

   If the new invoice is created in the AP module (Oracle Payables à Invoice à Entry à Invoices) with the Project details. OR Invoice has interfaced from the other module like i-expense with Project details, we should definitely customize the ‘Project Supplier Invoice Account Generation Process (PAAPINVW.wft)’ workflow to create account in distributions.

What will happen if the workflow is not customized?

       You will get the error saying “APP-SQLAP-97734: The system can not generate distribution lines…..”. Please find the screen shot of the error below.

 

Error comes whenever you click on distributions button.

  

Solution:

   Need to customize the workflow PAAPINVW, create a custom process where in remove all dummy default account generator functions and create/assign your accounting structure using custom functions OR we can use standard functions as well.

Find the below image for the Original Process “Generate default account (DEFAULT_ACCOUNT_GENERATION)”

Steps to customize the workflow:

1. Copy the process and create another as it is
2. Remove all Dummy default account generator functions
3. Say if you have the accounting structure i.e code combination with 5 segments.

You need to assign values 5 times i.e you need to create 5 functions to assign each value.

4. You can directly assign each value using “Assign Value to Segment” function in the “Standard Flexfield Workflow (FNDFFWK)”. You usually get the Standard Flexfield Workflow () along with “Standard (WFSTD)” workflow whenever you open the PAAPINVW workflow.
5. Assign values in Node attributes of the function “Assign Value to Segement”
6. OR if you have a lookup to get the values of segments, then use the function “Segment Lookup Set value” to get the lookup values and then assign the value to segment. Use Node attributes here as well.
7. Use the standard function “Validate Code Combination” after assigning all segment values
8. Look at the screenshot below for the better understanding

How to set values in ‘Node Attributes’

  Double click on each function OR right click – Properties

  Click on Node attributes tab and assign the values. If you are going to get values from lookups get lookup name and the source (Expenditure Operating Unit) is the source in the screen shot. Use this lookup value while assigning the value. 

Where Expenditure Operating Unit is the standard item attribute, we can use values of standard item attributes to link in lookup value sets.

Node Attributes:

Lookup:

 *** You can create your custom activities depends upon the customization

After customizing the workflow assign the custom process in accounting flexfiled processes.

1.    Go to General ledger responsibility à Ctrl+L à Account Generation Processes à Find your custom accounting flexfiled structure

     Change the Process name to your custom process where the item type is ‘Project Supplier Invoice Account Generation’

2.  Set the profile option “PA: Allow Override of PA Distributions in AP/PO” to ‘No’.

     To override using the custom account generation process which we have created

Even after all customizations if you still gets the token error, please refer the following notes in Oracle Metalink for support.

R12: APXINWKB: APP-SQLAP-97734 Error Message Displayed At Invoice Distributions Generation Time: Distributions Cannot Be Generated For Line Because &TOKEN2 (Doc ID 850743.1)

Projects Supplier Invoice Account Generation Error: APP-SQLAP-97734: The System Cannot Generate Distributions (Doc ID 823787.1)

R12: APXINWKB: APP-SQLAP-97734 WHEN ENTERING INVOICE DISTRIBUTION (Doc ID 1360580.1)

i-Expenses:

 Workflow Name: Project Expense Report Account Generator

 Internal Name:     PAAPWEBX

Follow the same steps as above for customizing the workflow. Change the process in the accounting flexfiled structure in GL.

This workflow is going to fire/trigger whenever the user select the project while raising the expense in i-expenses module. After click on next the workflow triggers and creates the new/custom code combination before submitting the expense report.

Note: After submitting the expense report ‘Expenses (APEXP)’ workflow is going to trigger.

Project, Expenditure Orgainzation and task are going to enable in JSP page whenever we set the OIE profile options.

   Ex: OIE:Enable Project Allocations

         OIE:Enable Project Expenditure Organization

Note: A profile option OIE: Enable Project Allocations if set to "Yes with account allocations", then the Division and Cost center are enabled in Account Allocations tab of Expense Allocations Page of iExpenses. But with this if we update the Division this is not getting updated in the Expense report tables.

How to Build Accouting structure or Code combination id for Non-Project Expense Reports ? 

If the user is not selected the project while raising the expense report, this workflow will not launch/trigger.

Then how will the accounting structure/code combination creates in non project expense reports? With our triggering the accounting generator workflow?

   This is different concept altogether. In non project expense reports the accounting structure/code combination is going to build using the standard package (AP_WEB_CUS_ACCTG_PKG) with our customizations.

   This package has two main procedure for this customization. Those are

      ap_web_cus_acctg_pkg.getiscustombuildonly
          If this function returns 1, then your custom code in PL/SQL    ap_web_cus_acctg_pkgbuildaccount will be called.

    ap_web_cus_acctg_pkg.buildaccount
        This is where you actually build the default account for non-project expense line.

getiscustombuildonly API this must return 1 if you want your custom logic to kick-off.
If this API does not return 1, then iExpenses will use the person level default accounts from HRMS.

Sample code you can use is (from apps2fusion). You can find the real time code below.

  FUNCTION buildaccount(p_report_header_id      IN NUMBER
                       ,p_report_line_id        IN NUMBER
                       ,p_employee_id           IN NUMBER
                       ,p_cost_center           IN VARCHAR2
                       ,p_exp_type_parameter_id IN NUMBER
                       ,p_segments              IN ap_oie_kff_segments_t
                       ,p_ccid                  IN NUMBER
                       ,p_build_mode            IN VARCHAR2
                       ,p_new_segments          OUT NOCOPY ap_oie_kff_segments_t
                       ,p_new_ccid              OUT NOCOPY NUMBER
                       ,p_return_error_message  OUT NOCOPY VARCHAR2)
    RETURN BOOLEAN IS
    l_account_array fnd_flex_ext.segmentarray;
  BEGIN
    p_new_ccid := 240694; --do not hard code in realiy, this is just sample
    l_account_array(1) := 'FOCUST';
    l_account_array(2) := 'CONSULTANCY';
    l_account_array(3) := 'R12';
    l_account_array(4) := '165122';
    l_account_array(5) := '0';
    l_account_array(6) := '0';
    l_account_array(7) := '0';
    p_new_segments := ap_oie_kff_segments_t('');
    p_new_segments.EXTEND(l_account_array.COUNT);
    FOR n_ctr IN 1 .. l_account_array.COUNT LOOP
      p_new_segments(n_ctr) := l_account_array(n_ctr);
    END LOOP;
    RETURN TRUE;
  END buildaccount;

ORACLE ACCOUNT GENERATOR WORKFLOW EXAMPLE

ORACLE WORKFLOWS

ORACLE ACCOUNT GENERATOR WORKFLOW EXAMPLE

 

This article illustrates the steps one needs to implement to build an Account Generator Oracle Workflow for eBusiness Suite.

For this exercise I am using iProcurement & core Purchasing Account generation workflows. However, the concepts remain the same for all the account generator workflows, regardless of the module.

Assume that we have two Accounting Flexfield segments in Oracle General Ledger:-
1.            Company Account
2.            Cost Centre

Important: In real life, you will have more than mere two segments in the GL Accounting Flexfield. These pseudo Two GL Segments are for demo purposes only.

Assuming that you have implemented Oracle Projects Accounting, the Requisition or a Purchase Order can either be Project based or can be Non Project based. For this exercise, for simplicity, lets restrict ourselves to Project Based Requisition.

Lets assume that the business requirement for Project Based Requisitions is:-
Company Segment for Projects based Requisition
Company Account Segment will be Defaulted to a constant value of GO4GOLD

Cost Centre Segment for Projects based Requisition
This will be defaulted from the Attribute1 against Primary Organization for Project Task . 

OK, here we go with all the steps that are required to build such an Account Generator Workflow.

Step 1
Open the account generator Workflow


Step 2.
Assign default value to Company segment.

Double click to open the process “Build Expense Project Charge Account”
After the Start Icon, Right Click to create a new Function.
As in screenshot below, in “Item Type” field, select “Standard Flexfield Workflow” from the drop down list. In the Display Name select “Assign Value to Segment” and click on OK.

By doing the above step, we have just created a workflow Function that is capable of assigning a desired value to GL Flexfield Segment. Now we need to configure this Function further, so that it knows the Segment upon which it operates, and also the default value that is assigned to the segment. 
As per our business requirement, we need to default a constant value of GO4GOLD for our Segment1 ( Company Segment ) . For this, click on the function created above, and click on the Node Attributes.

Do the steps, as in above screenshot.

Step 3 ( Part a).
Default the cost centre from Attribute1 against Primary Organization for Project Task.

In the Part-a of this step, we will assign the Cost Centre to an Attribute Named “TEMP_CC_VALUE”.

For this, we can define a new function named “Assign Cost Centre to Attribute”, by right clicking on the process.

This function calls PL/SQL XX_account_generator_pkg.set_cost_centre_attribute
PROCEDURE set_cost_centre_attribute
(
  itemtype IN VARCHAR2
 ,itemkey  IN VARCHAR2
 ,actid    IN NUMBER
 ,funcmode IN VARCHAR2
 ,RESULT   OUT VARCHAR2
) AS
  CURSOR c_get_cc(p_org_id IN NUMBER) IS
    SELECT attribute1 org_cc
    FROM   hr_all_organization_units hrou
    WHERE  hrou.organization_id = p_org_id;
  rec_get_cc c_get_cc%ROWTYPE;
BEGIN
  IF NOT (funcmode = 'RUN')
  THEN
    RETURN;
  END IF;
  BEGIN
--First nullify the value, so that accidentaly it does not get used in the next step.
    wf_engine.setitemattrtext(itemtype, itemkey, 'TEMP_CC_VALUE', '');
    OPEN c_get_cc(wf_engine.getactivityattrnumber(itemtype
                                                 ,itemkey
                                                 ,actid
                                                 ,'EXPENDITURE_ORGANIZATION_ID'));
    FETCH c_get_cc
      INTO rec_get_cc;
    IF c_get_cc%NOTFOUND
    THEN
      RESULT := 'COMPLETE:FAILURE';
    ELSE
      wf_engine.setitemattrtext(itemtype
                               ,itemkey
                               ,'TEMP_CC_VALUE'
                               ,rec_get_cc.org_cc);
      RESULT := 'COMPLETE:SUCCESS';
    END IF;
    CLOSE c_get_cc;
    RETURN;
  END;
EXCEPTION
  WHEN OTHERS THEN
    -- Record error using generic error message routine for debugging and
    -- raise it
    wf_core.CONTEXT(pkg_name  => 'ic_account_generator_pkg'
                   ,proc_name => 'GMS_GET_CC'
                   ,arg5      => NULL);
    RAISE;
END set_cost_centre_attribute;
In the above function, we are fetching the Attribute1 against the Organization Record. The organization_id is being passed as a parameter to the Newly defined Function( as in screenshot below).

Step 3 ( Part b).
In this part of the step, we will assign the value in Attribute “TEMP_CC_VALUE” to the  Segment2 ( i.e. the Cost Centre). This is done as per the screenshot below.

Step 4. Check to see if the Code Combination is Complete
Right click to create a function. From Item Type dropdown list, select “Standard Flexfield Workflow”. In the Display Name dropdown list, select “Is Code Combination Complete?”


Next, click on Node Attributes, and assign a value of TRUE to “Check only required Segments”.


Step 5. By implementing the above steps, we have been able to complete the Account Generation of our two-segment Flexfield using Oracle Workflow Account Generator.


The last and final step will already be there within the Pre-Installed Account Generation. This workflow step validates the Code Combination Id, for the Oracle’s Security Rules that are defined in General Ledger, against users responsibility. For reference purposes this is pasted below. 

A final note, for debugging the Account Generator workflow. Account Generator workflows are designed to run efficiently and quickly, hence they always in synchronous mode. This means that no inserts are made into the Oracle Workflow tables when generating the accounting.
Hence by default it is not possible to debug the Account Generator workflow using Status Monitor screen in Oracle Workflow.
However, Oracle delivers a profile option named “Account Generator:Run in Debug Mode”. Setting this profile to Yes, will make Account Genration processes insert records in workflow tables. Hence by setting this profile to Yes, you will be able to debug your account generation workflow. Further debugging of FND_FLEX API’s is made possible by enabling the FND Logging.