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;

Real time code:

Copy from here and paste it in your text pads.

FUNCTION GetIsCustomBuildOnly RETURN NUMBER

IS

BEGIN

    -- if you want to enable custom builds

    return 1;

    -- if you do not want to enable custom builds

    --return 0;

END;

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_chart_of_accounts_id NUMBER := 0;

l_flex_segment_delimiter VARCHAR2(2) := '';

l_concatenated_segments   varchar2(2000);

l_exp_line_acct_segs_array    FND_FLEX_EXT.SEGMENTARRAY;

l_default_emp_segments        FND_FLEX_EXT.SEGMENTARRAY;

l_FlexConcactenated           AP_EXPENSE_REPORT_PARAMS.FLEX_CONCACTENATED%TYPE;

l_exp_type_template_array     FND_FLEX_EXT.SEGMENTARRAY;

l_num_segments                NUMBER:=NULL;

l_default_emp_ccid            AP_WEB_DB_EXPRPT_PKG.expHdr_employeeCCID;

l_segment4 VARCHAR2(25) := NULL;

lc_flex_conc VARCHAR2(1000) := NULL;

lc_seg VARCHAR2(25) := NULL;

l_segment1 VARCHAR2(25) := NULL;

l_company_segment VARCHAR2(25) := NULL;

BEGIN

  p_new_segments := AP_OIE_KFF_SEGMENTS_T('');

   p_new_segments.extend(7);

  -- Get Employee default CCID

  IF (NOT AP_WEB_DB_EXPRPT_PKG.GetDefaultEmpCCID(

         p_employee_id          => p_employee_id,

         p_default_emp_ccid     => l_default_emp_ccid)) THEN

      NULL;

  END IF;

  IF (l_default_emp_ccid is null) THEN

    FND_MESSAGE.Set_Name('SQLAP', 'AP_WEB_EXP_MISSING_EMP_CCID');

    RAISE AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;

  END IF;

  --Get Chart of Accounts Id

  IF (NOT AP_WEB_DB_EXPRPT_PKG.GetChartOfAccountsID(

         p_employee_id          => p_employee_id,

         p_chart_of_accounts_id => l_chart_of_accounts_id)) THEN

      NULL;

  END IF;

  --Get default employee segments

  IF (l_default_emp_ccid IS NOT NULL) THEN

    IF (NOT FND_FLEX_EXT.GET_SEGMENTS(

                                'SQLGL',

                                'GL#',

                                l_chart_of_accounts_id,

                                l_default_emp_ccid,

                                l_num_segments,

                                l_default_emp_segments)) THEN

     RAISE AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;

       NULL;

    END IF; /* GET_SEGMENTS */

  END IF;

  IF (l_chart_of_accounts_id is null) THEN

    FND_MESSAGE.Set_Name('SQLAP', 'OIE_MISS_CHART_OF_ACC_ID');

    RAISE AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;

  END IF;

  -- Get segment delimiter

  l_flex_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(

                                        'SQLGL',

                                        'GL#',

                                        l_chart_of_accounts_id);

  IF (l_flex_segment_delimiter IS NULL) THEN

    FND_MSG_PUB.Add;

    RAISE AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;

  END IF;

  -- New code starts here

    if (p_ccid is not null) then

      IF (NOT FND_FLEX_EXT.GET_SEGMENTS('SQLGL',

                                      'GL#',

                                      l_chart_of_accounts_id,

                                      p_ccid,

                                      l_num_segments,

                                      l_exp_line_acct_segs_array)) THEN

--        return;

              Null;

      END IF;

    elsif (p_segments is not null and p_segments.count > 0) then

       IF (l_num_segments IS NULL) THEN

         l_num_segments := p_segments.count;

       END IF;

      FOR i IN 1..l_num_segments LOOP

            l_exp_line_acct_segs_array(i) := p_segments(i);

      END LOOP;

  end if /* p_ccid is not null or p_segments is not null */;

  -----------------------------------------------------------------------------

   if (p_exp_type_parameter_id is not null) then

    IF (AP_WEB_DB_EXPRPT_PKG.GetFlexConcactenated(

               p_parameter_id => p_exp_type_parameter_id,

               p_FlexConcactenated => l_FlexConcactenated)) THEN

  lc_flex_conc := TO_CHAR(l_FlexConcactenated);

  -- Getting Account segment from the concactenated expense report template accounting structure

   SELECT SUBSTR(lc_flex_conc,(INSTR(lc_flex_conc,l_flex_segment_delimiter,1,3)+1),(instr(lc_flex_conc,l_flex_segment_delimiter,1,4))-((instr(lc_flex_conc,l_flex_segment_delimiter,1,3)+1)))

   INTO   lc_seg

   FROM   DUAL;

   SELECT SUBSTR(lc_flex_conc,1,(instr(lc_flex_conc,l_flex_segment_delimiter,1,1)-1))

   INTO   l_company_segment   FROM   DUAL;

 -- l_segment4 := SUBSTR(l_FlexConcactenated,(INSTR(l_FlexConcactenated,l_flex_segment_delimiter,1,3)+1),(instr(l_FlexConcactenated,'-',1,4))-((instr(l_FlexConcactenated,'-',1,3)+1)));

 -- INSERT INTO temp_rajesh VALUES ('l_segment4',l_segment4);

     /*  IF l_FlexConcactenated is not null THEN

          l_num_segments := FND_FLEX_EXT.Breakup_Segments(l_FlexConcactenated,                                                          l_flex_segment_delimiter,                                                          l_exp_type_template_array);

       END IF; */

         NULL;

    END IF;

  end if; /* p_exp_type_parameter_id is not null */

  -----------------------------------------------------------------------------

     -- Overlay the incoming segment values with the segment values

     -- defined in expense type template IF the incoming segment value

     -- is NULL.

     /*   FOR i IN 1..l_num_segments LOOP

          -- If the incoming segment is not null, then keep this value, do nothing.

          IF (p_segments IS NOT NULL AND

              p_segments.EXISTS(i) AND

              p_segments(i) IS NOT NULL) THEN

            NULL;

          ELSIF (l_exp_type_template_array is not null and

              l_exp_type_template_array.count > 0 and

              l_exp_type_template_array(i) IS NOT NULL) THEN

            l_exp_line_acct_segs_array(i) := l_exp_type_template_array(i);

          ELSE

                   INSERT INTO temp_rajesh VALUES ('ELSE-Check it',NULL);

          END IF; -- l_exp_type_template_array(i) IS NOT NULL

       END LOOP; -- 1..l_num_segments

*/

/* IMPORTANT NOTE:

   Below values are hard code, coz workflow is failing in validation when we capture the same from select statement

   This will work for all existing CTC elements

   For other CTC elements the account will default to 420011 (which is wrong)

   For new CTC elements add some more IF conditions like below */

  IF l_company_segment = 'SSL' THEN

      p_new_segments(1) := 'SSL';

  ELSIF  l_company_segment = 'SEL' THEN

    p_new_segments(1) := 'SEL';

  ELSIF  l_company_segment = 'SSLDubai' THEN

    p_new_segments(1) := 'SSLDubai';

  ELSIF  l_company_segment = 'TUI' THEN

    p_new_segments(1) := 'TUI';

  ELSIF  l_company_segment = 'SITL' THEN

    p_new_segments(1) := 'SITL';

  ELSIF  l_company_segment = 'SSLUS' THEN

    p_new_segments(1) := 'SSLUS';

  ELSIF  l_company_segment = 'SSLUK' THEN

    p_new_segments(1) := 'SSLUK';

  ELSIF  l_company_segment = 'SSGmbH' THEN

    p_new_segments(1) := 'SSGmbH';

  ELSIF  l_company_segment = 'ODSi' THEN

    p_new_segments(1) := 'ODSi';

  ELSIF  l_company_segment = 'SSLSING' THEN

    p_new_segments(1) := 'SSLSING';

  ELSE

    p_new_segments(1) := 'SSL'; -- If the company not exists then use default value as SSL

  END IF;

   --p_new_segments(1) := l_default_emp_segments(1); --'SSL'; --p_segments(1); --'SSL';

   p_new_segments(2) := l_default_emp_segments(2); --p_segments(2); --'000000';

   p_new_segments(3) := l_default_emp_segments(3);--'PGERP'; --p_segments(3); --'PGERP';

  IF lc_seg = '423002' THEN -- Medical  Allowance

     p_new_segments(4) := '423002';

  ELSIF lc_seg = '420016' THEN -- Education Allowance

     p_new_segments(4) := '420016';

  ELSIF lc_seg = '441007' THEN -- Fuel Allowance

     p_new_segments(4) := '441007';

  ELSIF lc_seg = '423001' THEN -- LTA Allowance

     p_new_segments(4) := '423001';

  ELSIF lc_seg = '441006' THEN -- Car Allowance

     p_new_segments(4) := '441006';

  ELSIF lc_seg = '420011' THEN

     p_new_segments(4) := '420011'; -- Local Deputation Allowance

  ELSE

      p_new_segments(4) := '423002';--NULL; -- Do not generate accounting combinate

  END IF;

   --p_new_segments(4) := l_segment4;--l_exp_line_acct_segs_array(4);--l_exp_type_template_array(4); --'423002'; --p_segments(4); --'

   p_new_segments(5) := l_default_emp_segments(5); --'000000'; --p_segments(5); --'000000';

   p_new_segments(6) := l_default_emp_segments(6); --'000'; --p_segments(6); --'000';

   p_new_segments(7) := l_default_emp_segments(7); --'000000'; --p_segments(7); --'000000';

  l_concatenated_segments :=   p_new_segments(1)||l_flex_segment_delimiter                             ||p_new_segments(2)||l_flex_segment_delimiter                                       ||p_new_segments(3)||l_flex_segment_delimiter                                       ||p_new_segments(4)||l_flex_segment_delimiter                           ||p_new_segments(5)||l_flex_segment_delimiter                           ||p_new_segments(6)||l_flex_segment_delimiter

                                           ||p_new_segments(7);

  IF (FND_FLEX_KEYVAL.validate_segs('CREATE_COMBINATION',

                                        'SQLGL',

                                        'GL#',

                                        l_chart_of_accounts_id,

                                        l_concatenated_segments)) THEN --'SSL-BG1-PGERP-423002-000000-000-000000')) THEN

        p_new_ccid := FND_FLEX_KEYVAL.combination_id;

      ELSE

        p_return_error_message := FND_FLEX_KEYVAL.error_message;

        FND_MESSAGE.set_encoded(FND_FLEX_KEYVAL.encoded_error_message);

        fnd_msg_pub.add();

      END IF;

  return TRUE;

  --return FALSE;

END BuildAccount;

 

Thanks,

Rajesh