zondag 25 september 2016

Autoinvoice Import Fusion R11

In the past we've been using Autoinvoice import for importing receivables invoices. You fill the RA_INTERFACE_LINES_ALL table and the RA_INTERFACE_DISTRIBUTIONS_ALL as well if you want to create your own distributions and run Autoinvoice Import to validate and import your transactions.

In Fusion we have several options to do the same.


First we have the File Based Loader option. We can use this option manually (for conversion for example) or create a process in your middleware layer to construct the zipped CSV and upload it to UCM for processing.






Information on how to use the file based data import can be found in the Oracle Enterprise Repository (http://docs.oracle.com/cloud/latest/financialscs_gs/OEFBF/AutoInvoice_Import_4016_fbdi_1.htm#4016). You can download the excel template there as well.

In our case we however wanted to use the web services that are available. We have been investigating the services processInterfaceLine and processInterfaceDistribution next to createSimpleInvoice.
The latter can be used to directly create an invoice, but it's, as the service already says, a simple invoice with a header and some lines.

You can also use the createInterfaceLine or processInterfaceLine to create one or multiple lines in the open interface table.

Transaction Flexfield
Important is how to pass the correct context for grouping of the invoices. Usually you create a new transaction descriptive flex field context for each of your imported invoices.
You pass these in the element TransactionInterfaceLineDff.

 <inv:TransactionInterfaceLineDff xsi:type="ns3:CPQCloud" xmlns:ns3="http://xmlns.oracle.com/apps/flex/financials/receivables/transactions/autoInvoices/TransactionLineInterfaceLineDff/">
               <ns3:__FLEX_Context>CPQ Cloud</ns3:__FLEX_Context>
               <!--Optional:-->
               <ns3:_FLEX_NumOfSegments>6</ns3:_FLEX_NumOfSegments>
               <!--Optional:-->
               <ns3:orderNumber>1100</ns3:orderNumber>
               <ns3:orderLineNumber>1</ns3:orderLineNumber>
...
            </inv:TransactionInterfaceLineDff>

In order to use these elements you have to check your setup for the API Names of the context and the segments in your context. Check your setup in Manage Descriptive Flexfields. Search for flex field code RA_INTERFACE_LINES.

Open your context and note the API Name for the context.
Each element also has an API name which you must use in the flex field.


Line flex field
The line descriptive flex field can be passed in the element TransactionLineDff. Here the elements equal the API names again of the flex field attributes. For example an attribute for Service Type could be passed using <tran5:serviceType>...</tran5:serviceType>

Header flex field 
One thing we encountered is that although the createSimpleInvoice service contains a transaction header, and thus the transaction header flex field, but the processInterfaceLine service does not have this element. So currently (R11) it does not seem to be possible to pass the header flex field using this service.

Distributions
Another thing we encountered is that using the processDistributionLine does not contain elements to pass the individual segments for your accounting structure. It only accepts the code combination id. This means we either have to keep a duplicate record of the code combinations to be able to pass the internal id or we fetch the code combination first using a service.


Now there is no standard webservice to fetch the code combination id using the segments and chart of accounts id, so we need to create our own BI report/query to fetch the code combination given the segments and pass that ID to our processInterfaceDistribution service.

A detailed explanation on how to achieve this can be found in this blog http://hakanbiroglu.blogspot.nl/2014/11/leverage-power-of-bi-publisher-in-cloud.html#.V-gR6Ds5ZHQ.

Note that you have to decode the output of the XML from Base64 before you can use the XML that is retrieved.

Submit Autoinvoice Import
Now you can either schedule the Autoinvoice process or submit it from your middle layer. To find the job package name you need to pass navigate to Manage Custom Enterprise Scheduler Jobs for Receivables and Related Applications.


If you open it you will see the name AutoInvoiceMasterEss and the path /oracle/apps/ess/financials/receivables/transactions/autoInvoices/Receivables

When you call the ERP Integration service you pass parameters for example as follows

<?xml version="1.0" encoding="UTF-8" ?>
    <procesparam>
        <username>{Username}</username>
        <pswd>{Password}</pswd>
        <batchid>{batchid}</batchid>
        <businessunit>{BU Id}</businessunit>
    </procesparam>
    
    <jobPackageName>/oracle/apps/ess/financials/receivables/transactions/autoInvoices/Receivables</jobPackageName>
        <jobDefinitionName>AutoInvoiceMasterEss</jobDefinitionName>
        <paramList>1</paramList>
        <paramList>{Your transaction source}</paramList>
        <paramList>19-09-2016</paramList>
    </essparam>



References
Usage notes on the invoice service
http://www.oracle.com/webfolder/technetwork/docs/fbdi-r11/fbdi/supportingdocs/finArTrxnsInvoices-InvoiceService.pdf