Integrating With Fusion Application Using Services (.Net: Service Reference)

Fusion Applications provides Web services that allow external systems to integrate with Fusion Applications. There are two types of services: ADF services and composite services. ADF services are created for a logical business object and provide functionality to access and manipulate these objects. The composite services are mostly process oriented and provide an orchestration of multiple steps.

Information about the web services provided by Fusion Applications is hosted in Oracle Enterprise Repository (OER). The information provided by OER can be used to understand the functionality provided by the service and how the service can be called.

This series of articles describes how one can invoke SOAP web services provided by Fusion Applications using various technologies. In this article we will cover how to invoke a Fusion Application web service using Service Reference for.Net framework.

 Prerequisites

.Net development tool such as Visual Studio 2012 needs to be installed and configured

Implementing Web Service Call

.Net framework is a software framework for Microsoft Windows commonly used by the customers. We can generate a proxy through the Visual Studio by creating Service Reference. To integrate with a service first generate a Service Reference, in the address field enter the URL for the WSDL of the Fusion Application Service and click "Go", in this example I used CreditRuleService. The "Namespace" entered can then be used to access the objects generated in the code. In the example case the "Namespace" entered was "FusionServiceReference". In order to call a Fusion Applications web service we need to create a binding that matches the OWSM policy that the service is secured with. In this example the service is secured with "oracle/wss_username_token_over_ssl_service_policy" OWSM policy so we create a class for the custom binding as follows:

    public class UsernameTokenOverSslBinding : CustomBinding
    {
        public override BindingElementCollection CreateBindingElements()
       {
            BindingElementCollection bindingElements = new BindingElementCollection();
            bindingElements.Add(SecurityBindingElement.CreateUserNameOverTransportBindingElement());
            MtomMessageEncodingBindingElement messageEncoding = new MtomMessageEncodingBindingElement();
            messageEncoding.MessageVersion = MessageVersion.Soap11;
            bindingElements.Add(messageEncoding);
            HttpsTransportBindingElement transport = new HttpsTransportBindingElement();
            bindingElements.Add(transport);
            return bindingElements.Clone();
        }
    }

Do note that the binding configuration above is the minimum required for the given policy, depending on the policy additional configuration may be required.  The following is a complete example on how use the reference objects generated to find, create and delete a rule by calling a Fusion Applications Web Service:

using System;
using System.ServiceModel.Channels;
using System.ServiceModel;
using ServiceReferenceExample1Client.FusionServiceReference;

namespace ServiceReferenceExample1Client
{
    /// <summary>
    /// Custom binding that can be used to invoke Fusion Application services secured with OWSM policy
    /// </summary>
    /// <remarks>
    /// This custom binding can be used to invoke Fusion Application services secured with 
    /// "oracle/wss_username_token_over_ssl_service_policy" OWSM policy
    /// </remarks>
    public class UsernameTokenOverSslBinding : CustomBinding
    {
        public override BindingElementCollection CreateBindingElements()
        {
            BindingElementCollection bindingElements = new BindingElementCollection();
            bindingElements.Add(SecurityBindingElement.CreateUserNameOverTransportBindingElement());
            MtomMessageEncodingBindingElement messageEncoding = new MtomMessageEncodingBindingElement();
            messageEncoding.MessageVersion = MessageVersion.Soap11;
            bindingElements.Add(messageEncoding);
            HttpsTransportBindingElement transport = new HttpsTransportBindingElement();
            bindingElements.Add(transport);
            return bindingElements.Clone();
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
           
 EndpointAddress endpointAddress = new EndpointAddress(new 
Uri("https://host:port/icCnSetupCreditRulesPublicService/CreditRuleService"));

            // Get instance of the service to be invoked
            CreditRuleServiceClient crs = new CreditRuleServiceClient(new UsernameTokenOverSslBinding(), endpointAddress);

            // Set the authentication details to be used for the service call
            crs.ClientCredentials.UserName.UserName = "username";
            crs.ClientCredentials.UserName.Password = "password";

            // Run the test case which includes queries, creates and deletes a rule
            callFindRule(crs);
            callCreateRule(crs);
            Rule rule = callFindRule(crs);
            callDeleteRule(crs, rule);
            callFindRule(crs);

        }

        /// <summary>
        /// Logic to find a object using a web service call
        /// </summary>
        /// <param name="crs">Instance of the credit rule service client</param>
        /// <returns>Rule with a given name in this case "JRAUTIAI_TEST_RULE1"</returns>
        static Rule callFindRule(CreditRuleServiceClient crs)
        {
            Rule result = null;
            try
            {
                // Populate the objects to be used as parameter
                FindCriteria findCriteria = new FindCriteria();
                ViewCriteria viewCriteria = new ViewCriteria();
                viewCriteria.conjunction = Conjunction.And;
                ViewCriteriaRow viewCriteriaRow = new ViewCriteriaRow();
                viewCriteriaRow.conjunction = Conjunction.And;
                viewCriteriaRow.upperCaseCompare = false;
                ViewCriteriaItem viewCriteriaItem = new ViewCriteriaItem();
                viewCriteriaItem.conjunction = Conjunction.And;
                viewCriteriaItem.upperCaseCompare = false;
                viewCriteriaItem.attribute = "Name";
                viewCriteriaItem.@operator = "=";
                string[] ruleNames = new string[1] { "JRAUTIAI_TEST_RULE1" };
                viewCriteriaItem.Items = ruleNames;
 
                ViewCriteriaItem[] vcis = new ViewCriteriaItem[1] { viewCriteriaItem };
                viewCriteriaRow.item = vcis;
                ViewCriteriaRow[] vcrs = new ViewCriteriaRow[1] { viewCriteriaRow };
                viewCriteria.group = vcrs;
                findCriteria.filter = viewCriteria;
 
                findCriteria.fetchStart = 0;
                findCriteria.fetchSize = -1;
                FindControl findControl = new FindControl();
 
                // Call the service with the appropriate parameters
                Rule[] rules = crs.findRule(findCriteria, findControl);
                if (null != rules && rules.Length > 0)
                {
                    result = rules[0];
                    foreach (Rule rule in rules)
                    {
                        Console.WriteLine("ruleId: " + rule.RuleId + " - orgId: " + rule.OrgId + " - name: " + rule.Name);
                    }
                }
                else
                {
                    Console.WriteLine("Rule JRAUTIAI_TEST_RULE1 not found ");
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
            return result;
        }
 
        /// <summary>
        /// Logic to create a rule, in this case we have hard coded the content of the new rule to simplify the example
        /// </summary>
        /// <param name="crs">Instance of the credit rule service client</param>
        static void callCreateRule(CreditRuleServiceClient crs)
        {
            try
            {
                // Populate the object to be used as parameter
                Rule rule = new Rule();
                rule.EnabledFlag = true;
                rule.OrgId = 300000000678473;
                rule.OrgIdSpecified = true;
                rule.UsageId = -1001;
                rule.UsageIdSpecified = true;
                rule.StartDate = new DateTime(2012, 1, 1);
                rule.StartDateSpecified = true;
                rule.EndDate = new DateTime(2012, 1, 31);
                rule.EndDateSpecified = true;
                rule.Name = "JRAUTIAI_TEST_RULE1";
 
                // Call the service with the appropriate parameters
                Rule result = crs.createRule(rule);
                Console.WriteLine("Rule JRAUTIAI_TEST_RULE1 created ");
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
        }
 
        /// <summary>
        /// Logic to delete a specific Rule
        /// </summary>
        /// <param name="crs">Instance of the credit rule service client</param>
        /// <param name="rule">Instance of the Rule to be deleted</param>
        static void callDeleteRule(CreditRuleServiceClient crs, Rule rule)
        {
            try
            {
                // Call the service with the appropriate parameters
                crs.deleteRule(rule);
                Console.WriteLine("Rule JRAUTIAI_TEST_RULE1 deleted ");
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
        }
    }
} 

The above code does the following:

1. Get instance of the service reference client to be invoked using custom binding and an endpoint address
2. Set the authentication details to be used for the service call
3. Run the test case which includes calls to create, delete and query a rule

Summary

In this article we covered an example using Service Reference for .Net framework to integrate with Fusion Applications using web services. In future articles other technologies for invoking Fusion Applications web services will be covered. 

References

• .Net framework
• How to: Add, Update, or Remove a Service Reference
• Visual Studio 2012
• Oracle Enterprise Repository (OER)

Creation of Item using Item Interface

Item can be created by using Item Import Program.

Prerequistic is to insert record in Mtl_System_Items_Interface table.

Following are the important columns in Interface table
a. Segment1 
b. Organization_id
c. set_process_id
d. Process_flag
e. Transaction_type

Segment1 -- It holds the Item Name.
Organization_id -- To which Organization an item belongs to. Normally items will be added to the Master Organization. Then it will be assigned to Inventory Organization.
set_process_id -- Used to speed up the Import Program process.
Process_flag -- Process_flag=1 will be picked by the Item import program. If it is error out due to any diffrence, value of process_flag will be changed to 3.
Transaction_type -- It can be either 'CREATE' or 'UPDATE'


Code to Populate Item Interface Table
---------------------------------------------------------

declare
v_organization_id NUMBER := 0;
Begin
--Getting the Organization id
BEGIN
SELECT Organization_id
INTO v_organization_id 
FROM mtl_parameters mp
WHERE mp.organization_code = 'V1'; --V1 is the Master Organization Code
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in getting the Organization id for Organization code V1 and error is '||SUBSTR(SQLERRM,1,200));
END;

--Inserting into Item interface table
BEGIN
INSERT INTO mtl_system_items_interface
(segment1,
organization_id,
process_flag,
set_process_id,
transaction_type
)
values
('New Item Name',
v_organization_id,
1,
1,
'CREATE'
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in inserting record in interface table and error is '||SUBSTR(SQLERRM,1,200));
END;
END;

Once item is inserted into inteface table, item import program needs to be called to Create item. Following is the code used to call the item import program

Code to Call Item Import Program
--------------------------------------------------

DECLARE
v_organization_id NUMBER := 0;
v_request_id NUMBER := 0;
v_phase VARCHAR2(240);
v_status VARCHAR2(240);
v_request_phase VARCHAR2(240);
v_request_status VARCHAR2(240);
v_finished BOOLEAN;
v_message VARCHAR2(240);
BEGIN

--Submit the item import program in Create Mode to Create New Item
BEGIN

dbms_output.put_line('--Submitting Item Import Program for Item--'); 
v_request_id := Fnd_Request.submit_request (
application => 'INV',
program => 'INCOIN',
description => NULL,
start_time => SYSDATE,
sub_request => FALSE,
argument1 => 1,
argument2 => 1, 
argument3 => 1, --Group ID option (All)
argument4 => 1, -- Group ID Dummy
argument5 => 1, -- Delete processed Record 
argument6 => 1, -- Set Process id 
argument7 => 1 -- Create item 
);
COMMIT;
dbms_output.put_line('Item Import Program submitted');

IF ( v_request_id = 0 ) THEN 
dbms_output.put_line( 'Item Import Program Not Submitted'); 
END IF;

-- Wait for request to run the import Program to Finish
v_finished := fnd_concurrent.wait_for_request (request_id => v_request_id,
interval => 0,
max_wait => 0,
phase => v_phase,
status => v_status,
dev_phase => v_request_phase,
dev_status => v_request_status,
message => v_message);

dbms_output.put_line('Request Phase : '|| v_request_phase );
dbms_output.put_line('Request Status : ' || v_request_status );
dbms_output.put_line('Request id : '||v_request_id );

--Testing end status
IF ( UPPER(v_request_status) = 'NORMAL') THEN
dbms_output.put_line( 'Item Import Program Completed Normally'); 
ELSE 
dbms_output.put_line( 'Item Import Program completed with error. Check Mtl_interface_error table for the transaction_id'); 
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in Submitting Item Import Program and error is '||SUBSTR(SQLERRM,1,200));
END;
END;

Account Receivables

1.      Invoice

Invoice in AR is used to bill the customer for the goods and the services offered (rendered).

2.      Debit Memo

Debit Memos in AR have the similar concept as that of an invoice. A debit memo could be used to invoice the customer for certain miscellaneous expenses incurred which were not included in the actual invoice for the product/service. Debit memo information is stored in ra_customer_trx_all, the type of transaction a particular row represents can be found out from the field class of ar_payment_schedules_all by linking the customer_trx_id field in the two tables.

Debit Memo is not created like credit memo. Debit memo is usually not linked to any other Invoice like credit memos; the whole idea behind creating a debit memo is to increase the customer outstanding balance.

Why create a debit memo instead of invoice then,

      Debit memo would mean that we billed you short in our invoice and with reference our previous transaction we are billing you with the remaining amount. You can enter the source invoice number in “Reference field”

3.      Credit Memo

Credit Memos with negative amount are typically used for crediting the customer’s account with some balance. Credit memo information is also stored in ra_customer_trx_all

Credit memo can be created in two different ways; the idea behind creating a credit memo is to reduce the customer invoice balance.

                        Credit Memo can be created in two ways

1.      Manually creating a credit memo like any other AR manual invoice.

                                                i.      Invoice Class: Credit Memo

                                                ii.      Invoice Type: OM Credit Memo, Credit Memo

2.      Querying an existing AR Invoice and calling the Credit function from Actions menu

Query the AR Invoice for which you want to create a credit memo

                                                i.      Click on “Actions menu”

                                                ii.      Specify the “Reason for Credit Memo”

                                                iii.      Enter the Line % for Credit memo (Eg: 25% entered)

                                                iv.      Enter the Tax % for Credit memo (Eg: 10% entered)

                                                v.      Click the Save button

4.      Deposit/Advance Creation Process and Apply to Invoices

Deposits are entered in the AR system to capture the customer advances and apply them to the AR invoices.

APPLY DEPOSITS TO AR INVOICES

Query or Enter the AR Invoice for which you want to apply the deposit

a.       Click on Actions menu

b.      Click Apply Deposit, Select the Deposit , Click Ok

c.       Deposit is applied to the Invoice.

5.      LOCKBOX

Auto Lockbox automatically creates receipts in Receivables using electronic information that your bank provides. Receivables let you specify the payment method for each Lockbox you define. Payment methods provide the default accounting information for receipts you create through Auto Lockbox. Receivables display active Lockboxes as list of values choices in the Submit Lockbox Processing window. You can disable a Lockbox by un-checking the Active box, and then saving your work.

Remittance        - Payment

6.      ADJUSTMENTS

An adjustment as the name signifies is used for adjusting some of the amount in invoices. For e.g. if some insignificant amount is outstanding a particular invoice, it can be adjusted for the purpose of closing the invoice.  Adjustment information is stored in the table ar_adjustments_all and can be linked to ra_customer_trx_all by the field customer_trx_id.

7.      AGING BUCKETS

Aging buckets are time periods we can use to review and report on your open receivables. For example, the 4–Bucket Aging bucket that Receivables provides consists of four periods: –999 to 0 days past due, 1 to 30 days past due, 31–61 days past due, and 61–91 days past due. When you create your Collections reports or view your customer accounts, you can specify an aging bucket and ’as of date’, and Receivables will group the transactions and their amounts in the appropriate days past due period. You can define an unlimited number of aging buckets and lines (time periods) within an aging bucket. However, all Receivables aging reports include a maximum of the first seven time periods for an aging bucket. If you want to report on additional time periods, you must create custom aging reports. You can also customize the aging buckets that Receivables provides.

8.      DUNNING LETTERS

These are letters that are used to chase up receivables from customers. Receivables let you create dunning letters using two different methods. You can use one of the ten dunning letters that Receivables provides, or create your own, custom dunning letters in the Dunning Letters window. The ten dunning letters that Receivables provides include text files that you can customize to suit your dunning needs. Each of these letters (entitled USER1 – 10) includes two flat files: one for the body of your dunning letter text and the other for footer information. These files reside in the Receivables $AR_TOP/reports directory. For example, the dunning letter USER1 has a body file of ardl1b.txt and a footer file of ardl1f.txt. The ’b’ and the ’f’ identify these files as body and footer files, respectively.

9.      MEMO LINES

          Standard memo lines are lines that you assign to a transaction when the item is not an inventory item (for example, ’Consulting Services’). You can assign memo lines to debit memos, on–account credits, debit memo reversals, charge backs, commitments, and invoices. Receivables display your standard memo lines as list of values choices during credit memo entry in the Credit Transactions window and during invoice entry in the Lines window. When you create charge backs and debit memo reversals, you can either use the standard line that Receivables provides or enter your own. You can create an unlimited number of standard memo lines.

AR Technical Process

Start with creating customers in Accounts Receivables. Customers in AR (or Accounts Receivables) can be of two types namely:

1. Person
2. Organization

As is evident from the names a customer type is defined as a Person when the customer is a single person and while creating a customer, which is organization, the customer type is kept as Organization. From the back-end point of view, a customer can be distinguished as a person or organization from the field party_type of the table hz_parties which is linked to the table hz_cust_accounts by the field party_id.

The main attributes of a customer are:

1. Customer Name (Stored as party_name in the table hz_parties, first name, middle name and last name are also present for a customer type as Person)
2. Customer Number (Stored as account_number in the table hz_cust_accounts)
3. Address: There can be multiple addresses defined for any customer and these addresses can be for various purposes for eg. Billing or Shipping the goods. Also, we can specify if a particular address is active at any point of time or not. The primary flag indicates whether a particular address is primary or not. Moreover, within an operating unit there can be only one primary bill to address (active) for any customer. Information regarding all this can be derived from the tables hz_cust_site_uses_all and hz_cust_acct_sites_all, where the cust_account_id of hz_cust_accounts acts as a foreign key.

Main Tables

hz_parties -> hz_cust_accounts -> hz_cust_acct_sites_all -> hz_cust_site_uses_all

A.    Creating Invoice

Transaction lines, tax, Freight and Discount

Now since we are ready with a customer in AR, we need to Invoice him/her for the goods/services rendered. This brings us to the next step of AR i.e. invoicing the customers.

The main attributes of creating an invoice in AR are:

1. Number (can be entered manually or can be generated automatically based on the source of the invoice).
2. Date
3. Currency
4. Source
5. Class (Invoice)
6. Type (Transaction type name e.g. INV_TRX_TYPE1)
7. Ship to Customer (Ship to address of the customer).
8. Bill to Customer (Customer to be invoiced)
9. Payment Terms (The system generates Due Date based on this).

Each invoice can have multiple lines describing the Item, Quantity, Price of the product/services for which the customer is being invoiced. The system calculates line Amount based on the Quantity and Price. The sum of line amounts of all the lines in an invoice makes the Invoice Amount.

After entering the lines for an invoice and saving it, an invoice needs to be completed for it to appear for any payment application. Pressing the Complete button at the lower most left corner of the transaction screen does this. Any further changes to the invoice can only be done by again incompleting the invoice.

The invoice information is stored in the table ra_customer_trx_all where the trx_number corresponds to the invoice number entered from the front end. Once an invoice is completed the record appears in ar_payment_schedules_all table, where the customer_trx_id from ra_customer_trx_all acts as a foreign key.

B.     CREATING MANUAL RECEIPTS

Now since we have invoiced our customer, we expect to receive payment from the customer against an invoice. This brings us to the next step of AR, where we have to create receipts in AR for the payment that we received from the customer in form of Check, DD etc.

The main attributes of receipt in AR are: -

1. Receipt Number
2. Currency
3. Net Receipt Amount
4. Receipt Date
5. GL Date
6. Payment Method
7. Transaction Number (to which the payment has to be applied)

C.    Receipt application

After entering this information we proceed to applying the receipt to the invoice, here we can change the amount to be applied to the invoice and also the apply date on which the receipt should be applied. Also we have control over the transaction to which the receipt should be applied, a receipt can also be keptunapplied or onaccount if so desired. After entering the information in the applications screen, save the application.

Receipts information is stored in table ar_cash_receipts_all, where receipt_number corresponds to the receipt number entered from the front end. Each receipt record is also stored in ar_payment_schedules_all where cash_receipt_id from ar_cash_receipts_all acts as a foreign key.

A receipt created in AR can have any one of the following statuses: -

1. Unidentified - When the receipt is created without linking it to customer or invoice. Unidentified receipts could be created when the source of the incoming payment is unclear.
2. Unapplied – When the payment is linked to a particular customer but has not been applied to any of the transactions.
3. Onaccount - When the amount of the receipt is not applied to any of the transactions and is kept on-account of the customer.

A receipt can also have some amount as applied, some as unapplied and some as on-account or a combination of any two of these.

This completes one basic cycle of AR i.e.

1. Creating a customer
2. Invoicing the customer
3. Applying payment to the invoice and closing it.

Note: While this illustrates the brief functionality of AR, appropriate accounting entries need to be passed by setting the accounts for the customer.

The application of a receipt to an invoice can take various other forms for e.g.

1. To an invoice of amount $100, a receipt of amount $50 is applied. In this case the invoice remains open with an outstanding amount of  $50 while the receipt amount gets exhausted.
2. A receipt of amount $200 is applied to an invoice of amount $100. In this case the remaining receipt amount i.e. $100 can be kept unapplied or on-account.

10.  RECEIPT REVERSAL

If you apply a receipt against an invoice whose revenue was automatically deferred upon import, and you later reverse that receipt, then the impact of the receipt reversal differs depending on the original reason for the revenue deferral:

If revenue on an invoice was deferred due to unmet header level collectibility requirements, then Receivables initiates revenue recognition whenever you apply a receipt to the invoice. If you

reverse a previously applied receipt, then Receivables automatically unearns the previously earned revenue. In some cases, you might apply a receipt against an invoice line, but Receivables cannot recognize revenue for that line due to unmet line level collectibility requirements. Therefore, Receivables leaves the receipt amount as unearned revenue, but flags the amount as pending revenue recognition at a later date. If you later reverse the receipt, then Receivables reflects the receipt reversal by simply removing that pending flag from the receipt amount.

If revenue on an invoice was deferred due to unmet line level collectibility requirements only, then the reversal of a receipt does not impact the amount and timing of revenue recognition.

11.  AUTO INVOICE OPEN INTERFACE

The process for creating invoices in AR manually has been demonstrated in the earlier sections. However, considering the volume of transactions involved, it is not always feasible to create all the invoices manually. To overcome this AR provides a standard program called ‘Autoinvoice Master Program’. Whenever shipment of certain items happen from the Order Management Module, Workflow background process is run to populate data into the interface tables provided by AR and then the autoinvoice program can be run to automatically generate invoices in AR. Optionally, data from some legacy systems (if applicable) can also be populated into the interface tables by building customized interfaces.  The following interface tables need to be populated before running autoinvoice

1. RA_INTERFACE_LINES_ALL
2. RA_INTERFACE_DISTRIBUTIONS_ALL (Optional depending on the specific project requirements)
3. RA_INTERFACE_SALESCREDITS_ALL (Optional depending on the specific project requirements)

Autoinvoice program can also be used for creating debit memos and credit memos apart from invoices. Also, applying a credit memo to invoice can also be done using autoinvoice

Auto invoice is the process used for importing the transactions from feeder moudles like project accounting, order entry etc. and also from existing applications/systems if the receivable module is installed for the first time.

Auto-Invoice

Navigation: Receivable >> Interface >> Auto-Invoice

Chargeback Creation Process

Chargeback is postponement of payment date. It can be done only in the receipts workbench. In this process the old invoice is cancelled and a new transaction is created for the postponed date.

Or, Adjusting remaining balance of the existing debit item to zero, and create a new debit item to bill customer for unpaid balance of original invoices.

After apply the receipts to any invoice, if you want to re-activate the Invoice by also keeping the Receipt in place. Then click the Chargeback button.

Adjustment Creation Process

Adjustments are created for Receipts in case of applying some extra value to the transaction, like Bank charges, bad debts etc. Click the Adjustment button on the Receipt apply window.

Credit Memo Usage

In order Management, sometimes goods may return to supplier because of damage. To refund that material amount, supplier will create Credit Memo to customer, to credit customer balance.

Auto Invoice

Create invoices from other sources like Order Management, Service Contracts, Projects Billing, etc,. This will be done using Auto Invoice master program.

Tables in AR

1.      The RA_CUSTOMER_TRX_ALL table stores invoice, debit memo, commitment, bills receivable, and credit memo header information. Each row in this table includes general invoice information such as customer, transaction type, and printing instructions. One row exists for each invoice, debit memo, commitment, bill receivable, and credit memo that you create in Oracle Receivables. Invoices, debit memos, credit memos, bills receivable, and commitments are distinguished by their associated transaction types, which are stored in the RA_CUST_TRX_TYPES_ALLtable.


2. The RA_CUSTOMER_TRX_LINES_ALL table stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines. For example, an invoice can have one line for Product A and another line for Product B. Each line requires one row in this table.


3. The RA_CUST_TRX_LINE_GL_DIST_ALL table stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line. Oracle Receivables creates one row for each accounting distribution, and at least one accounting distribution must exist for each invoice or credit memo line. Each row in this table includes the General Ledger account and the amount of the accounting entry. The AMOUNT column is required even though this column is null allowed. Receivable uses this information to post the proper amounts to General Ledger.


4. The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table

5. The RA_CUST_TRX_TYPES_ALL table stores information about each transaction type that is used for invoices, commitments, bills receivable, and credit memos. Each row includes Auto Accounting information as well as standard defaults for the invoices that result.

6. AR_PAYMENT_SCHEDULES_ALL holds the payment schedules for the transactions

Modifying Positive Payment File in Oracle EBS R12

Overview

A positive pay file is a security measure in the form of a document that the deploying company sends to its payment system or bank to inform it of payments made by check.

In Oracle EBS R12, Oracle has introduced Oracle Payments as a new module that is comprised of funds capture (receipt of funds electronically from customers) and funds disbursement (payment of funds owed to suppliers).

Within the Oracle Payment Administrator function, there is a seeded positive pay file format that is available to be associated with a payment process profile. However, most of the time, Oracle users have to end up modifying the Positive Pay File formation Oracle EBS R12 to match up to their Bank’s specifications.

Common examples from Banks that deviate from the standard seeded Oracle Positive Pay file format are:

• Revised column width and ordering/display of information
• Fixed width vs comma delimited formats
• Displaying specific coding to determine the status of a check e.g. N = Negotiable and V = Voided

Upon receiving such requests, either a developer or a techno functional resource can go in and make the desired modifications. General points in approaching the matter are described below.

The work is primarily done within the Payment Administrator function which can be accessed as under Payables Manager > Setup > Payments > Payment Administrator.

1)      Download the seeded Positive Pay File Template:

You can download a copy of the seeded positive Pay File Template as follows:

Navigation: Payables Manager > Setup > Payments > Payment Administrator > XML Publisher Format Templates

Search for the Template “Positive Pay File” and click on Go.

On the next page that opens up, click on the link “Positive Pay File” and click on the “Download” button to download the .rtf file to your local machine (IBYPOS_PAY_en.rtf). Note that the template type is “etext – Outbound”

Note that when you run the Positive Pay Program out of the box, the output of the original positive pay file that comes out of Oracle (which is mapped to the above “Positive Pay File” template) is below.

Example format/layout of the downloaded Positive Pay File (.rtf file) is shown below:

 Note that when you run the Positive Pay Program out of the box, the output of the original positive pay file that comes out of Oracle (which is mapped to the above “Positive Pay File” template) is below.

2)      Examine the Bank Requirements and make the necessary modifications on the .rtf file

Assume that the Bank requests the following changes to meet with their internal requirements:

a)      The Branch number should be taken out

b)      If a void record is being sent, indicate this as “V” otherwise pass a value of “I” between the check date and the supplier name

Thus, the expected positive pay output file for the above data should be provided to the Bank in the following format:

To make the above modifications, simply do the following on a copy of the seeded IBYPOS_PAY_en.rtf that you downloaded in step 1 above. Proceed to modify as follows:

a)      Requirement 1 – the Branch number record to be taken out:

This is achieved by simply deleting the row that denotes Branch number on the .rtf file

b)      Requirement 2 – if a void record is being sent, indicate this as “V” otherwise pass a value of “I” between the check date and the supplier name:

This is achieved by utilizing the “if” functionality within BI Publisher to check on the “payment status code”

The modified Positive Pay File (.rtf file), now appears as follows:

3)      Create a new Positive Pay File Template

Navigate as follows: Payables Manager > Setup > Payments > Payment Administrator > XML Publisher Format Templates. Click on Create Template and provide the following information:

• Name
• Code
• Application – Payments
• Type – eText – Outbound
• Data definition – Oracle Payments Funds Disbursement Positive Pay
• Start Date (always best practice to backdate)
• Click on the Browse button and attach the newly created positive pay (.rtf) file
• Language
• Click Apply

4)      Create a new Positive Pay File Format:

This is of particular importance because it has been seen in the past that a customized template that is associated to the seeded Positive Pay File Format can get overwritten when patches are applied e.g. CPU patches.

Navigate as follows: Payables Manager > Setup > Payments > Payment Administrator > Formats.

Choose the Type as “Disbursement Positive Pay File” and click on Create

A new Payment Formats form opens up. Provide the following information:

• Code
• Name
• Data extract – Oracle Payments Funds Disbursement Positive Pay
• Choose your newly created XML Publisher Template from the list of values in the “XML Publisher Template” field.
• Click on Apply

5) Update your Payment Process Profile with the newly created Positive Pay Format

Lastly, you need to update your Payment Process Profile that is used to print checks with the newly created Positive Pay Format.

Navigation: Payables Manager > Setup > Payments > Payment Administrator > Payment Process Profiles.

Query your Payment Process Profile and in update mode and select the Reporting Tab. In the Positive Pay section, select your newly created Positive Pay Format from the list of values and click on Apply.

You are now ready to submit your Positive Pay program.

This is done by running the “Positive Pay File with Additional Parameters” program which can be scheduled to run nightly at a given time. You can also opt to transmit the positive pay file immediately upon completion of a Payment Process Request.

Be sure to provide both the Format and the Bank Account for which you are running the Positive Pay program.

Click on View Output once your request completes.

Additional notes:

a)      Always get the complete specs and a sample file format from the Bank before doing any development work. This is to avoid rework/going back and forth with the Bank

b)      Always create a custom template and attach it to a custom format. When large patches are applied, there is a likelihood that the seeded templates/formats may get overwritten with new code that is delivered by the patches.