Validate Fusion GL Account Code Combinations with APEX
Jon Dixon
Introduction
If you read this blog post on Interfacing Data from APEX to Oracle Fusion Cloud ERP over REST, you will know that APEX is an excellent platform for integrating with and extending Oracle Cloud ERP (Fusion). In this post, I will show you how to use the AccountCombinationService SOAP-based web service to validate GL Account Code Combinations from APEX.
Use Case
If you are interfacing journals with Fusion, it makes for a better user experience if you can be sure that 99% of journals will be imported successfully. Failed journal import means cleaning up the GL interface table, which can get complicated. One of the most frequent issues in Journal Import is invalid GL Code Combinations. Oracle offers the Account Combination Validation Service SOAP Web Service to allow you to validate GL Code Combinations.
Dynamic Insertion
Before we proceed, we need to understand how Dynamic Insertion works in Oracle Cloud ERP. Dynamic Insertion applies to all key flex fields (including GL Code Combinations).
With Dynamic Insertion turned on: When you try to import a Journal Line with a GL Code Combination that does not already exist, then (assuming it passes cross-validation rules) the GL Code Combination will be created automatically.
With Dynamic Insertion turned off: When you try to import a Journal Line with a GL Code Combination that does not already exist, you will get an error. In this scenario, all GL Code Combinations must be created in advance.
Account Combination Validation Service
The Account Combination Validation Service, like all the other SOAP Web Services in Oracle Cloud ERP, is imperfect.
For now, we have to deal with SOAP for the Account Combination Validation Service. To do so, we need to understand how it works.
Web Service URL
Assuming your instance Base URL is:
https://demo-dev.login.us2.oraclecloud.com
The URL for the AccountCombinationService service will be:
https://demo-dev.login.us2.oraclecloud.com/fscmService/AccountCombinationService
Sample Payload
In this example, I am sending two code combinations for validation. The first one is valid; the second is not.
<soapenv:Envelope
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:typ="http://xmlns.oracle.com/apps/financials/generalLedger/accounts/codeCombinations/accountCombinationService/types/"
xmlns:acc="http://xmlns.oracle.com/apps/financials/generalLedger/accounts/codeCombinations/accountCombinationService/">
<soapenv:Header/>
<soapenv:Body>
<typ:validateAndCreateAccounts>
<typ:validationInputRowList>
<acc:DynamicInsertion>y</acc:DynamicInsertion>
<acc:Segment1>2117</acc:Segment1>
<acc:Segment2>000</acc:Segment2>
<acc:Segment3>190267</acc:Segment3>
<acc:Segment4>121012</acc:Segment4>
<acc:Segment5>0000</acc:Segment5>
<acc:Segment6>00000</acc:Segment6>
<acc:LedgerName>Demo Ledger</acc:LedgerName>
<acc:EnabledFlag>true</acc:EnabledFlag>
</typ:validationInputRowList>
<typ:validationInputRowList>
<acc:DynamicInsertion>y</acc:DynamicInsertion>
<acc:Segment1>1101</acc:Segment1>
<acc:Segment2>101</acc:Segment2>
<acc:Segment3>200942</acc:Segment3>
<acc:Segment4>411030</acc:Segment4>
<acc:Segment5>1005</acc:Segment5>
<acc:Segment6>00000</acc:Segment6>
<acc:LedgerName>Demo Ledger</acc:LedgerName>
<acc:EnabledFlag>true</acc:EnabledFlag>
</typ:validationInputRowList>
</typ:validateAndCreateAccounts>
</soapenv:Body>
</soapenv:Envelope>
The DynamicInsertion tag works like this:
If you set it to 'y' and Dynamic Insertion is turned on, the web service will create the code combination if it is valid. If it is invalid, it will return an error message detailing the failed cross-validation rule.
If you set it to 'n' and Dynamic Insertion is turned on, the web service will not create the code combination if it is valid. The problem is that it will not tell you the error if it is invalid; it will just tell you: "The account combination can't be created because dynamic combination creation isn't allowed."
Sample Response
You will receive a 200 (OK) response whether the code combinations you send are valid or invalid. You must check the Status tag [Valid, Invalid] for each returned code combination to see if it is valid. The Error tag provides details of the error if the Status is Invalid.
The response below was returned from the Sample Payload above. You will notice the first record has a Status of Valid, and the second has a status of Invalid, along with details of the error in the Error tag.
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://www.w3.org/2005/08/addressing">
<env:Header>
<wsa:Action>http://xmlns.oracle.com/apps/financials/generalLedger/accounts/codeCombinations/accountCombinationService/AccountCombinationService/validateAndCreateAccountsResponse</wsa:Action>
<wsa:MessageID>urn:uuid:b6036073-63b7-4ad4-8fa7-366ca14fdca3</wsa:MessageID>
</env:Header>
<env:Body>
<ns0:validateAndCreateAccountsResponse xmlns:ns0="http://xmlns.oracle.com/apps/financials/generalLedger/accounts/codeCombinations/accountCombinationService/types/">
<ns2:result xmlns:ns0="http://xmlns.oracle.com/adf/svc/types/" xmlns:ns1="http://xmlns.oracle.com/apps/financials/generalLedger/accounts/codeCombinations/accountCombinationService/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns2="http://xmlns.oracle.com/apps/financials/generalLedger/accounts/codeCombinations/accountCombinationService/types/" xsi:type="ns1:AccountValidationOutput">
<ns1:Segment1>2117</ns1:Segment1>
<ns1:Segment2>000</ns1:Segment2>
<ns1:Segment3>190267</ns1:Segment3>
<ns1:Segment4>121012</ns1:Segment4>
<ns1:Segment5>0000</ns1:Segment5>
<ns1:Segment6>00000</ns1:Segment6>
<ns1:Segment7 xsi:nil="true"/>
<ns1:Segment8 xsi:nil="true"/>
<ns1:Segment9 xsi:nil="true"/>
<ns1:Segment10 xsi:nil="true"/>
<ns1:Segment11 xsi:nil="true"/>
<ns1:Segment12 xsi:nil="true"/>
<ns1:Segment13 xsi:nil="true"/>
<ns1:Segment14 xsi:nil="true"/>
<ns1:Segment15 xsi:nil="true"/>
<ns1:Segment16 xsi:nil="true"/>
<ns1:Segment17 xsi:nil="true"/>
<ns1:Segment18 xsi:nil="true"/>
<ns1:Segment19 xsi:nil="true"/>
<ns1:Segment20 xsi:nil="true"/>
<ns1:Segment21 xsi:nil="true"/>
<ns1:Segment22 xsi:nil="true"/>
<ns1:Segment23 xsi:nil="true"/>
<ns1:Segment24 xsi:nil="true"/>
<ns1:Segment25 xsi:nil="true"/>
<ns1:Segment26 xsi:nil="true"/>
<ns1:Segment27 xsi:nil="true"/>
<ns1:Segment28 xsi:nil="true"/>
<ns1:Segment29 xsi:nil="true"/>
<ns1:Segment30 xsi:nil="true"/>
<ns1:Status>Valid</ns1:Status>
<ns1:LedgerName>Demo Ledger</ns1:LedgerName>
<ns1:CcId>11475281</ns1:CcId>
<ns1:Error xsi:nil="true"/>
<ns1:ErrorCode xsi:nil="true"/>
<ns1:EnabledFlag>true</ns1:EnabledFlag>
<ns1:FromDate>1951-01-01</ns1:FromDate>
<ns1:ToDate xsi:nil="true"/>
</ns2:result>
<ns2:result xmlns:ns0="http://xmlns.oracle.com/adf/svc/types/" xmlns:ns1="http://xmlns.oracle.com/apps/financials/generalLedger/accounts/codeCombinations/accountCombinationService/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns2="http://xmlns.oracle.com/apps/financials/generalLedger/accounts/codeCombinations/accountCombinationService/types/" xsi:type="ns1:AccountValidationOutput">
<ns1:Segment1>1101</ns1:Segment1>
<ns1:Segment2>101</ns1:Segment2>
<ns1:Segment3>200942</ns1:Segment3>
<ns1:Segment4>411030</ns1:Segment4>
<ns1:Segment5>1005</ns1:Segment5>
<ns1:Segment6>00000</ns1:Segment6>
<ns1:Segment7 xsi:nil="true"/>
<ns1:Segment8 xsi:nil="true"/>
<ns1:Segment9 xsi:nil="true"/>
<ns1:Segment10 xsi:nil="true"/>
<ns1:Segment11 xsi:nil="true"/>
<ns1:Segment12 xsi:nil="true"/>
<ns1:Segment13 xsi:nil="true"/>
<ns1:Segment14 xsi:nil="true"/>
<ns1:Segment15 xsi:nil="true"/>
<ns1:Segment16 xsi:nil="true"/>
<ns1:Segment17 xsi:nil="true"/>
<ns1:Segment18 xsi:nil="true"/>
<ns1:Segment19 xsi:nil="true"/>
<ns1:Segment20 xsi:nil="true"/>
<ns1:Segment21 xsi:nil="true"/>
<ns1:Segment22 xsi:nil="true"/>
<ns1:Segment23 xsi:nil="true"/>
<ns1:Segment24 xsi:nil="true"/>
<ns1:Segment25 xsi:nil="true"/>
<ns1:Segment26 xsi:nil="true"/>
<ns1:Segment27 xsi:nil="true"/>
<ns1:Segment28 xsi:nil="true"/>
<ns1:Segment29 xsi:nil="true"/>
<ns1:Segment30 xsi:nil="true"/>
<ns1:Status>Invalid</ns1:Status>
<ns1:LedgerName>Demo Ledger</ns1:LedgerName>
<ns1:CcId>-1</ns1:CcId>
<ns1:Error>Cross-Validation Rules: CO-CC2.1101: Invalid Company 1101 with Cost Center (GL-104517367)</ns1:Error>
<ns1:ErrorCode>GL_CVR_VIOLATION_REP_VIO_CVRS (GL_CVR_1002001_CO-CC2.1101)</ns1:ErrorCode>
<ns1:EnabledFlag>true</ns1:EnabledFlag>
<ns1:FromDate xsi:nil="true"/>
<ns1:ToDate xsi:nil="true"/>
</ns2:result>
</ns0:validateAndCreateAccountsResponse>
</env:Body>
</env:Envelope>
Postman Example Using Basic Auth
HTTP Headers
The complete SOAPAction is: http://xmlns.oracle.com/apps/financials/generalLedger/accounts/codeCombinations/accountCombinationService/validateAndCreateAccounts
Calling the service from APEX
The PL/SQL block below shows how we can call the Account Combination Validation Service from APEX:
DECLARE
CURSOR cr_validation (cp_response IN CLOB) IS
SELECT x.*
FROM XMLTABLE(xmlnamespaces
('http://schemas.xmlsoap.org/soap/envelope/' AS "env",
'http://xmlns.oracle.com/apps/financials/generalLedger/accounts/codeCombinations/accountCombinationService/types/' AS "ns0",
'http://xmlns.oracle.com/apps/financials/generalLedger/accounts/codeCombinations/accountCombinationService/' AS "ns1"),
'/env:Envelope/env:Body/ns0:validateAndCreateAccountsResponse/ns0:result'
PASSING XMLTYPE(cp_response)
COLUMNS
ledger_name VARCHAR2(100) PATH 'ns1:LedgerName',
segment1 VARCHAR2(100) PATH 'ns1:Segment1',
segment2 VARCHAR2(100) PATH 'ns1:Segment2',
segment3 VARCHAR2(100) PATH 'ns1:Segment3',
segment4 VARCHAR2(100) PATH 'ns1:Segment4',
segment5 VARCHAR2(100) PATH 'ns1:Segment5',
segment6 VARCHAR2(100) PATH 'ns1:Segment6',
segment7 VARCHAR2(100) PATH 'ns1:Segment7',
segment8 VARCHAR2(100) PATH 'ns1:Segment8',
status_code VARCHAR2(100) PATH 'ns1:Status',
error VARCHAR2(1000) PATH 'ns1:Error',
errorcode VARCHAR2(1000) PATH 'ns1:ErrorCode'
) x;
l_response CLOB;
l_url VARCHAR2(1000);
l_payload CLOB;
BEGIN
-- Set HTTP Headers.
apex_web_service.set_request_headers
(p_name_01 => 'soapAction',
p_value_01 => 'http://xmlns.oracle.com/apps/financials/generalLedger/accounts/codeCombinations/accountCombinationService/validateAndCreateAccounts',
p_name_02 => 'Content-Type',
p_value_02 => 'text/xml;charset=UTF-8',
p_reset => true);
-- Build the URL to the AccountCombinationService API in the current instance.
l_url := 'https://demo-dev.fa.us2.oraclecloud.com/fscmService/AccountCombinationService';
-- Build the Payload
l_payload := TO_CLOB('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/generalLedger/accounts/codeCombinations/accountCombinationService/types/" xmlns:acc="http://xmlns.oracle.com/apps/financials/generalLedger/accounts/codeCombinations/accountCombinationService/"><soapenv:Header/><soapenv:Body><typ:validateAndCreateAccounts>') ||
TO_CLOB('<typ:validationInputRowList><acc:DynamicInsertion>n</acc:DynamicInsertion><acc:Segment1>2117</acc:Segment1><acc:Segment2>000</acc:Segment2><acc:Segment3>190267</acc:Segment3><acc:Segment4>121012</acc:Segment4><acc:Segment5>0000</acc:Segment5><acc:Segment6>00000</acc:Segment6><acc:Segment7>0000</acc:Segment7><acc:Segment8>0000</acc:Segment8><acc:LedgerName>Demo Ledger</acc:LedgerName><acc:EnabledFlag>true</acc:EnabledFlag></typ:validationInputRowList>') ||
TO_CLOB('</typ:validateAndCreateAccounts></soapenv:Body></soapenv:Envelope>');
-- Call the Web Service.
l_response := apex_web_service.make_rest_request
(p_url => l_url,
p_http_method => 'POST',
p_transfer_timeout => 30,
p_body => l_payload,
-- Use an APEX Web Credential to Authenticate using Basic Auth.
p_credential_static_id => 'SERVICE_ACCOUNT');
dbms_output.put_line('HTTP Status: ' || apex_web_service.g_status_code);
IF apex_web_service.g_status_code = 200 THEN
-- Parse the Response using XMLTABLE
FOR r_combination IN cr_validation (cp_response => l_response) LOOP
dbms_output.put_line('> Status:' || r_combination.status_code);
dbms_output.put_line('> Error Message:' || r_combination.error);
-- TBD add your own code to handle a Valid or Invalid response.
END LOOP;
ELSE
dbms_output.put_line('The Web Service Call Failed with status: ' || apex_web_service.g_status_code);
END IF;
END;
Volume Considerations
The AccountCombinationService is not particularly fast. It takes about 30 seconds to validate 250 code combinations. Because of this, you should consider strategies to limit the number of code combinations you send to it.
Validating Segment Values
If you sync the valid segment values to a table in your local instance, you can ensure that at least the individual segments are valid.
Validating Code Existing Code Combinations
Consider syncing all existing code combinations (from GL_CODE_COMBINATIONS) in Fusion to a table in your APEX environment. This will allow you to check if the code combinations you want to validate have already been created. In this case, you know they are valid and do not need to be sent to the validation web service.
Off-Line Validation
You may also want to consider performing the validations offline via an APEX Automation or an APEX Background Page Process.
Batching
Finally, consider batching your calls to the web service. For example, I found it was about 20% faster to process six batches of 250 records rather than one batch of 1,500 records.
Conclusion
The Account Combination Validation Service is imperfect, but it allows you to validate GL Account Code Combinations and ensure only journals with valid Account Code Combinations are sent to Fusion.
Subscribe to my newsletter
Read articles from Jon Dixon directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by
Jon Dixon
Jon Dixon
Hi, thanks for stopping by! I am focused on designing and building innovative solutions using the Oracle Database, Oracle APEX, and Oracle REST Data Services (ORDS). I hope you enjoy my blog.