Happy New Year 2010

Wishing all a very Happy and a Prosperous New Year

How to deal with AP/AR Netting related issues

Download PDF

The first and foremost step to ensure that the netting process does not error out is to check the following pre-requisites.

1. Ensure that all the setups pertaining to Netting are in place.
   Please see: http://oracledollarschennai.blogspot.com/2008/10/apar-netting.html

2. Ensure that all the packages related to FUN% are Valid and none are in Invalid Status. Once can use the below mentioned SQL to identify the netting related Invalid objects:
   
   SELECT object_name, object_type, owner, status
   FROM dba_objects
   WHERE status = 'INVALID'
   AND object_name LIKE 'FUN%'’
   
   

There are a few known issues with Netting. The error is normally encountered while submitting the “Create Netting Batch” process or while running the “Submit Netting Batch” concurrent program.

Once either of these concurrent program errors out, check the log file from the Submit Concurrent Program window. Normally, the log file will give a generic message as ‘Unable to Create AR Receipt’ or ‘Error applying a Receipt’.

The cause of the issue will be normally based on some wrong setups or some invalid value passed via the Netting Agreement.

Step 1:
======

The first step in the process of identifying the issue is by getting the debug log file. The debug messages from Netting will give a clear cut idea as of which place the program fails. Once the debug file is reviewed, the cause of the issue will normally be quite clear.

Please see: http://oracledollarschennai.blogspot.com/2009/04/download-pdf-how-to-debug-oracle.html

If the debug file does not give much information (Normally, the debug file will give the actual error), then resort to the second step.

Step 2:
======

The second step in debugging the issue is to get the version of the file ‘FUNNTTXB.pls’.
You can use the below mentioned command to get the version of the file.

cd $FUN_TOP/patch/115/sql
Strings –a ‘FUNNTTXB.pls’ |grep ‘$Header’

Ensure that this file is on the current highest version available via metalink. Apply the patch to get to the latest version if you are not at the highest level.

If a Netting Batch is stuck in Error, it is normally not possible to change the status, in such cases, you can resort to a datafix by deleting the netting batch from the Backend.

Please see: http://oracledollarschennai.blogspot.com/2008/12/deleting-netting-batch-from-backend.html

Step 3:
======

Another issue which normally happens with Netting is the creation of another Receipt Method, AP/AR Netting(1) by the application of some of the Netting related Patches. This can be fixed with the help of a Datafix.

Please see the steps mentioned below to perform a Datafix once you are able to identify the existence of another Receipt method ‘AP/AR Netting(1)’.

By default, the Receipt method for Netting is ‘AP/AR Netting’ and the id associated with it is ‘-1’. The below mentioned datafix will remove the ‘AP/AR Netting(1)’ and will replace the id of ‘AP/AR Netting’ as –1. Also, it will remove the reference of ‘AP/AR Netting(1)’ from the dependent tables.

The datafix is as below:

--Backup

CREATE TABLE receipt_class_bkp2 AS
SELECT *
FROM ar_receipt_classes
WHERE receipt_class_id = -1;

CREATE TABLE receipt_method_bkp2 AS
SELECT *
FROM ar_receipt_methods
WHERE receipt_method_id = -1;

CREATE TABLE rec_method_acct_bkp2 AS
SELECT *
FROM ar_receipt_method_accounts_all
WHERE receipt_method_id = -1

--Delete

DELETE FROM ar_receipt_classes
WHERE receipt_class_id = -1;

DELETE FROM ar_receipt_methods
WHERE receipt_method_id = -1;

DELETE FROM ar_receipt_method_accounts_all
WHERE receipt_method_id = -1;

COMMIT;

--Update

UPDATE ar_receipt_methods
SET name = 'AP/AR Netting',
receipt_method_id = -1,
receipt_class_id = -1
WHERE receipt_method_id = 1;

UPDATE ar_receipt_method_accounts_all
SET receipt_method_id = -1
WHERE receipt_method_id = 1;

UPDATE ar_receipt_classes
SET receipt_class_id = -1
WHERE receipt_class_id = 1

COMMIT;

This will ensure that you do not have any occurrence of ‘AP/AR Netting(1)’ in the instance.
Now, the next step is to identify any receipt with the ‘AP/AR Netting(1)’ in the receipt tables.
You can use the below mentioned query to identify the same.

SELECT cash_receipt_id,
receipt_number
FROM ar_cash_receipts_all
WHERE receipt_method_id = 1;

If this returns any rows, then probably the receipt will have Receipt Method as ‘AP/AR Netting(1)’.

In order to rectify this receipt, perform the below mentioned datafix.

--Backup

CREATE TABLE bkup_tbl_cr_method AS
SELECT *
FROM ar_cash_receipts_all
WHERE cash_receipt_id IN
(SELECT cash_receipt_id
FROM ar_cash_receipts_all
WHERE receipt_method_id = 1);

--Update

UPDATE ar_cash_receipts_all
SET receipt_method_id = -1
WHERE cash_receipt_id IN
(SELECT cash_receipt_id
FROM ar_cash_receipts_all
WHERE receipt_method_id = 1);

COMMIT;

This should rectify all the receipts.

Most of the issues with Netting will get resolved once the above steps are followed. If you are still not able to resolve the issue, then log an SR with Oracle Support.

More on this to follow in my coming posts.

How to Debug Oracle Applications:

Download PDF

There are three major ways in which one can debug the Oracle Applications. This is applicable to all the released versions in Oracle. Let me give a brief on how to proceed with each of them.

Pre-Requisites:

- You must have the backend access to database and the Unix Box.

- Must be well versed with SQL and PL/SQL

- Must have a good idea about the functional flow of the Applications.

- Access to System Administrator Responsibility in Oracle Applications.

Lets begin with the methods. As already mentioned, there are three ways of debugging the Applications and they are:

1. Debug Log
2. Trace and TkProf
3. FRD Log

Lets discuss the ways in which this can be obtained and how to read them.

Debug Log Messages:

This is one of the most commonly used debug methods. This gives a fair idea of the application as of which package is failing and which value is not getting passed to which parameter and so on.. more importantly, the backend data flow.

Step 1:

========

The first and foremost thing in this process is to enable the debug messages. This can be achieved by setting the following Profile Options at the User Level.

Please note that you can set the profile options only if you have the access to “System Administrator Responsibility”.

FND: Debug Log Enabled YES -- Enables Debugging messages.

FND: Debug Log Filename NULL -- To store the file in an external file, if NULL,

the values are written in a table

FND: Debug Log Level STATEMENT -- Most detailed log

FND: Debug Log Module % -- Application specific, if %, it is enabled for all

applications, else values can be ‘ar%’, ‘ap%’

Step 2:

======

Once the Profile options are set, the next step is to find the initial value from where our debugging routine will start. To identify this value, you will have to login to the Database by using SQL Developer/TOAD and then execute the following Query:

SELECT MAX(LOG_SEQUENCE)
FROM FND_LOG_MESSAGES

This will give a value. Keep this value for future references.

Step 3:

======

Now, try to reproduce the issue and ensure that you try to stay on track in reproducing the issue and leave out extraneous steps so that you don't end up with debug messages that are not relevant to your issue. It is ideal to not have anyone else using the Responsibility you have enabled debug for, so that only messages pertaining to your testcase are picked up.

Step 4:

======

Once the issue is reproduced, stop at the step next to the error, like Click OK to the error and then the next step is to ensure that you retrieve the debug log messages generated by your test case. To retrieve the messages, execute the below query in SQL Developer/TOAD.

SELECT MESSAGE_TEXT, MODULE
FROM FND_LOG_MESSAGES
WHERE LOG_SEQUENCE > &max_log_from_step2
ORDER BY LOG_SEQUENCE;

Now, spool this file to an excel sheet and save it in your system.

Step 5:

======

The next step in the process is to read the retrieved messages. Here is how you can interpret the messages generated.

Here is how a sample debug file looks like:

create_person (+)

LoadDataSources (+)

cacheSetupForOtherEntities (+)

G_DATASOURCE_LOADED = N

l_str = 'USER_ENTERED'

l_str = 'USER_ENTERED'

l_str = 'USER_ENTERED'

l_str = 'USER_ENTERED'

l_str = 'USER_ENTERED'

l_start = 5

i = 1

l_entity_name = 'HZ_PERSON_PROFILES'

l_entity_id = -1

i_content_source_type.COUNT = 0

l_str =

i = 2

l_entity_name = 'HZ_ORGANIZATION_PROFILES'

l_entity_id = -2

i_content_source_type.COUNT = 1

l_str = 'USER_ENTERED'

total = 7

cacheSetupForOtherEntities (-)

p_mixnmatch_enabled = N

p_selected_datasources = 'USER_ENTERED'

LoadDataSources (-)

do_create_party (+)

validate_person (+)

first_name or last_name is mandatory. x_return_status = S

head_of_household_flag in lookup YES/NO. x_return_status = S

deceased_flag in lookup YES/NO. x_return_status = S

after validating date_of_birth and date_of_death against SYSDATE x_return_status = S

(+) after validating the date_of_death and date_of_birth... x_return_status = S

created_by_module is madatory field. x_return_status = S

validate_person (-)

l_mixnmatch_enabled = N

l_selected_datasources = 'USER_ENTERED'

do_create_update_party_only (+)

do_create_party_name (+)

l_party_name = ALLY TIONG

do_create_party_name (-)

Generated person key : TNG.ALC

validate_party (+)

validate_party (-)

hz_parties_pkg.Insert_Row (+)

hz_parties_pkg.Insert_Row (-)

do_create_update_party_only (-)

do_create_party_profile (+)

do_create_person_profile (+)

hz_person_profiles_pkg.Insert_Row (+)

do_create_person_name (+)

do_create_person_name (-)

l_person_name = ALLY TIONG

hz_person_profiles_pkg.Insert_Row (-) x_profile_id = 2783499

do_create_person_profile (-)

do_create_party_profile (-)

do_process_classification (+)

party_id =

do_process_classification (-)

Event Name:oracle.apps.ar.hz.Person.create Event Key:oracle.apps.ar.hz.Person.create382123Maximum nested raise count:100Nested raise count: 0

For every ‘(+)’, the control is getting into that Package and for every “(-)”, the control is coming out of that package. It also depends on what kind of debugging messages are coded in those packages where the control enters and processes the code and then passes to the next level.

P.S: Almost all the packages in Oracle Applications, has debugging messages enabled, however, there are exceptions, where packages may not have debugging enabled, hence, will not be shown in the debug file.

To find the cause of the issue, the first thing to find in a debug file, is the word ‘Exception’. This will be showing the package name where the error occurs. Just, open the package in SQL Developer/TOAD and see the SQL being fired.

Check for the values which are being passed to this SQL and see the exception block of the script. On analyzing further, you will mostly be able to find the value which is not getting passed or the wrong value being passed to this SQL and the reason for the error.

Once this is identified, you have to check the Application Settings and modify it accordingly to have the correct value to be passed to the SQL to prevent it from erroring out.

I will be posting the other two debugging methods in my coming posts.

Balance Forward Billing in R12

Download PDF

Balance Forward Billing is just a new name of "Consolidation Invoice billing" in Release 12, which provides enhanced billing options with a more complete and flexible solution.

Lets try to create the basic setups required for Balance Forward billing and have a sample run.

Step 1: Create the Balance forward billing Cycle.

Navigate to : Receivables => Setup => Print => Balance Forward Billing Cycles

1. Enter the name, description, start date, and frequency of the balance forward billing cycle.

2. Enter the frequency of the balance forward billing cycle: daily, weekly, or monthly.

To define daily cycles:

1. Enter the number of days the billing should repeat.

2. (Optional) Select Exclude Saturdays and Sundays.

To define weekly cycles:

3. Enter the number of weeks the billing should repeat.

4. Select the day of the week the billing should occur.

To define monthly cycles:

5. Enter the number of months the billing should repeat.

6. Select the day of the month the billing should occur.

Billing can occur on more than one day. For cycles with a billing day between 29 and 31, Receivables considers the last day of the month as the billing day for months with fewer days.

7. Select either All Days or Exclude Saturdays and Sundays

The importance of creating a Balance Forward Billing Cycle is as below:

• To determine the billing date of a balance forward bill.

• To select the transactions to include in the balance forward bill.

Step 2: Define Balance Forward Billing Payment Terms.

Navigate to : Receivables => Setup => Transactions => Payment Terms

We can define balance forward payment terms to bill customers periodically (daily, weekly, or monthly) at the account or site level using balance forward billing. The balance forward bill for a billing period shows the previous balance carried over from the last billing period, payment received, current charges and activities, and current total outstanding balance.

1. If you want to use a payment term for balance forward billing, select an appropriate balance forward billing cycle from the Billing Cycle LOV.

Note: You cannot update the billing cycle, once a balance forward billing payment term is attached to a profile.

Because balance forward bills cannot be split across installments, in the case of a balance forward payment term:

o Any value entered in Base Amount defaults to 100.

o Installment Options becomes disabled and any data entered before selecting a cycle defaults to Include tax and freight in first installment.

o You can populate only one row in the Payment Schedule section and the Sequence Number and Relative Amount values for the row default respectively to 1 and 100.

o Date Due becomes disabled. However, you can populate Days, Day of Month, and Months Ahead.

Note: You cannot change existing payment terms from regular payment terms to balance forward billing payment terms and vice versa.

Step 3: Enable Balance Forward Billing

Enabling balance forward billing is a two step process:

• Setting Up Customer Profile Classes

• Setting Up Account and Site Profiles

Depending on setup, customers can generate balance forward bills consolidated at either the account or site level:

Step a: Setting up Customer Profile Classes for BFB

Navigate to : Receivables => Customer => Profile Classes

In the Balance Forward Billing region, select Enable.

Note: When we select Enable, Bill Level and Type become required and the Payment Terms list of values displays only balance forward billing payment terms. If you do not select Enable, then Bill Level and Type are not required and the Payment Terms list of values displays only non-balance forward billing payment terms

Not selecting Override Terms ensures that all transactions for a particular customer account or site share the same balance forward billing payment term, due date, and aging.

Step b: Setting Account and Site Profiles for BFB

Navigate to : Receivables => Customer => Customers

The profile is attached at the Account Level. This is indicated in the screen shot below.

Important: We must enable balance forward billing for a customer both at the account and site profile level in order for Receivables to pick any transactions of the customer. If balance forward billing is enabled at the site profile level but not at the account profile level, the application does not recognize the site as a balance forward billing site. Although transactions for the site continue to inherit the balance forward billing payment term, Receivables never picks them up.

Profile Class is attached at the Site Level also.



Change the System Option Settings to enable the Billing Number to be displayed in the Transactions screen.

Navigate to: Receivables => Setup => System => System Options

In the Trans and Customers Tab, check ‘Show Billing Number’.

Once done, create a transaction with the same customer. The important point to note here is to have the transaction with the same Payment Term. The payment term should be the one attached with the Billing Cycle. Also, ensure that the transaction has the Print option (More tab) set to ‘Print’. The transactions marked with ‘Do Not Print’ are not included in the Balance Forward Billing program.

now, the next step is to Run the ‘Generate Balance Forward Bill program’

The output for the program is as shown below.

When the transaciton is queried, we can see the Consolidated Billing number generated in the Transaction, just besides the Transaction Number.

The following tables are involved in the creation of a Balance Forward Billing Program.

AR_CONS_BILL_CYCLES_B

AR_CONS_BILL_CYCLES_TL

AR_CONS_BILL_CYCLE_DATES

AR_CONS_INV_ALL

AR_CONS_INV_TRX_ALL

AR_CONS_INV_TRX_LINES_ALL

Note: This also refers the payment terms table where the Bill_Cycle_ID is used.

This finishes a cycle for Balance Forward Billing. Your comments and queries are always welcome.

Happy New year- 2009

*Wishing you a New Year filled with good health, new hope and new beginnings!

* May Y ear 2009 open up for you more opportunities, lead you onto the path of continued success, happiness and prosperity!

* I wish ... that the coming year showers on you all that you ever wished for!

* Good luck and smiles, good cheer and ...

Deleting a Netting batch from backend

Download PDF

Whenever there is an issue with Netting Batch where the batch is in error and the user is unable to Update or Delete the Netting batch, one can use the following SQL Statements to delete the Batch from the Backend.

1. Select CHECKRUN_ID, Batch_id from Fun_net_batches_all where Batch_Name = '&Your_batch_name'

2. Delete from fun_net_batches_all where Batch_Name = '&Your_batch_name'

3. Delete from FUN_NET_AR_TXNS_ALL where Batch_id= &Batch_id_from_step1

4. Delete from FUN_NET_AP_INVS_ALL where Batch_id= &Batch_id_from_step1

5. To remove the transactions from AP that was picked up in the Netting Batch, you may please run the following script.

select * from AP_PAYMENT_SCHEDULES_ALL where checkrun_id= &Checkrun_Id_from_step1;

If this returns any rows, then use the below statement, else no action is required

UPDATE AP_PAYMENT_SCHEDULES_ALL
SET checkrun_id = NULL
WHERE invoice_id =&invoice_id_from_prev_sel_stmnt and
checkrun_id=&Checkrun_Id_from_step1;

Once this is done, then issue a COMMIT.

AP/AR Netting

Download PDF

The Payables and Receivables Netting feature enables the automatic netting of Payable
and Receivable transactions within a business enterprise. You can predefine a netting
agreement that incorporates the netting business rules and transaction criteria needed
to run your tailored netting process. The netting process automatically creates the
Payables payments and Receivables receipts required to clear a selected number of
Payables and Receivables transactions.
You can view the receipts that the Netting process creates by querying the netted
receipts in the Receipts workbench. To view additional details about the netting batch,
select AP/AR Netting from the Actions menu.

Note: You cannot update netted receipts in the Receipts workbench.

1 Tables

1.1 New Tables

Table Name	Feature Area
FUN_NET_AR_TRX_TYPES_ALL	Netting Agreements
FUN_NET_AP_INV_TYPES_ALL	Netting Agreements
FUN_NET_AGREEMENTS_ALL	Netting Agreements
FUN_NET_SUPPLIERS_ALL	Netting Agreements
FUN_NET_CUSTOMERS_ALL	Netting Agreements
FUN_NET_BATCHES_ALL	Netting Batches
FUN_NET_AR_TXNS_ALL	Netting Batches
FUN_NET_AP_INVS_ALL	Netting Batches

1.2 Views

1.2.1 New Views

View Name	Feature Area
FUN_NET_RELATED_CUSTOMERS_V	Netting Related Customers

---

2. Implementation Considerations

This section identifies the new/changed setup steps for Oracle Netting that were introduced in Release 12. For detailed step-by-step implementation instructions, please refer to one of the Oracle Finanicials Implementation Guide, Oracle Financials User Guide or the Oracle Applications Upgrade Guide.

1. New Setups

2.1 New Setup Steps

1. Netting Bank Account
2. Receivables System Options
3. Netting Batch Approver
4. Chargeable Subcontracting
5. Netting Agreement

2.1.1 Netting Bank Account

Set up a Bank Account in Oracle Cash Management which can be used by the Netting process. Set the "Netting Account" flag to "Yes" for the bank account. Assign this bank account to the seeded Receivables Receipt Class "AP/AR Netting".

2.1.2 Receivables System Options

Check the 'Allow payment of Unrelated Transactions' checkbox in Receivables System Options if you want to create a netting agreement across multiple unrelated customers.

2.1.3 Netting Batch Approver

If approval is required for a netting agreement, the approver must be set up as a contact for the trading partner (customer or supplier). The contact must be set up with an email address.

2.1.4 Profile: Chargeable Subcontracting Enabled

Setting the profile "Chargeable Subcontracting Enabled" to "Yes" would mean only invoices that were matched to purchase orders with outsourced assemblies would be selected by the netting process. If the profile option is set to "No", only invoices that were matched to purchase orders without outsourced assemblies would be selected by the netting process. If the profile option is set to "D", all eligible invoices will be selected for netting irrespective of whether or not they are matched to purchase orders.

2.1.5 Netting Agreement

Netting agreements control how payables and receivables transactions are netted for a group of trading partners. Create netting agreements to set up the rules that will decide which transactions are selected for netting and how they are ordered and processed once selected.

---

3. Performance & Sizing: Considerations & Optimizations

3.1 Upgrade

Netting does not upgrade any transactional data. It upgrades only setup data which will not be of high volume. Therefore no special consideration needs to be given for the table sizes during upgrade.

---

4. Minimum Technical Requirements

The minimum technical requirements for Netting are included in the Release 12 Rapid Install.

---

5. Diagnostics

5.1 FND Logging

To check log messages added by the Netting processes, set the following profile options at user level
FND: Debug Log Enabled – Yes
FND: Debug Log Level – Statement
FND: Debug Log Module –FUN%NET

Then execute query
SELECT *
FROM fnd_log_messages
WHERE module like 'FUN%NET%'
AND trunc(timestamp) = trunc(sysdate)

---

6. Technical Architecture Considerations

1. Netting Batch Status
2. Netting Batch Creation Process
3. Netting Batch Submission Process
4. Netting Batch Settlement Process
5. Customer Merge
6. Supplier Merge

6.1 Netting Batch Status

Following is the sequence of events and the corresponding status for the netting batch.

Event	Netting Batch Status
User creates the netting batch	RUNNING
"Review Batch" option is set to "Yes"	SELECTED
Error encountered while validating, selecting transactions	ERROR
Current date has gone past batch settlement date	SUSPENDED
"Review Batch" option is set to "No" but no approval is required	CLEARING
"Review Batch" option is set to "No" and approval is required	SUBMITTED
User rejects batch requiring approval	REJECTED
User approves batch requiring approval	APPROVED
Netting process completed for transactions	COMPLETE
User chooses to reverse batch	REVERSING
Batch has been reversed	REVERSED

6.2 Netting Batch Creation Process

Criteria used for selecting AP invoices
Dates

AP Payment schedule due date < = FUN_NET_BATCHES.transaction_due_date

AP Payment schedule due date between Agreement Start Date and Agreement End Date ( if null ,Sysdate)

Only Invoices for supplier and supplier sites ( if specified) in the agreement are selected.
Only invoices with the selected invoice types in the agreement are included.
Operating Unit of the Invoices must equal Operating Unit of the batch
Invoices must be approved.
Invoices must not be on hold ( Payment schedule.hold_flag = ‘N’)
Invoices that have already been included in a batch that is not yet in status ‘COMPLETE’ are not included.
Shikyu Processing :

Additional processing is done for Shikyu Invoices based on the Shikyu Rule setup in the Netting Agreement.

Check Shikyu rule code on the Agreement

Y - "Yes" - Only invoices lines that were matched to PO lines with the OSA flag checked are selected for Netting

N - "No" -Only invoices lines that were matched to PO lines with the OSA flag not checked are selected for Netting

D - "Disregard"- All AP invoices selected for Netting; no filtering, therefore all eligible invoices are selected, as if the profile is "Off"

Lock AP Invoices
Selected AP invoices are locked by populating AP_PAYMENT_SCHEDULES.checkrun_id column for the
selected invoices.
The same checkrun_id value is populated in FUN_NET_BATCHES.CHECKRUN_ID for the given batch.

Criteria used for selecting AR Transactions
Dates

AR Payment Schedule due date <= FUN_NET_BATCHES.transaction_due_date

AR Payment Schedule date should be between Agreement Start Date and end date ( if null,Sysdate)

Transactions must be Complete.
Only transactions for customer and customer sites ( if specified) in the agreement are selected.
Only transactions with the selected transaction types in the agreement are included.
Operating Unit of the Transactions must be equal to the Operating Unit of the batch
Transactions that have already been included in a batch that is not yet in status ‘COMPLETE’ will not be included in the new batch.
The Receipt method associated with the transaction should not have a Payment_type_code = ‘CREDIT_CARD’
Transaction must not be in dispute.
Payment schedule status must be open (payment schedule.status = ‘OP’)
Transaction must not be a prepayment (ra_customer_trx.prepayment_flag = ‘N’)

Criteria for ordering the transactions
Invoices and transactions are first ordered by supplier and customer priority associated in the agreement.They are then ordered by the Netting Order Rule selected in the agreement. The priority is important as this decides which transaction / invoice will be netted first.

Netting Currency Rule
The Netting Currency Rule is checked to see if multiple batches need to be created.
If NET_CURRENCY_RULE_CODE = ‘SINGLE_CURRENCY’ then Invoices & transactions are filtered based on Invoice/transaction currency code = FUN_NET_AGREEMENTS.CURRENCY_CODE

If NET_CURRENCY_RULE_CODE = ‘WITHIN_CURRENCY’ then the invoices/transactions are grouped based on their invoice/transaction currency code .
For every group of transactions a new batch is created.
The netting currency of the batch = invoice currency code of the grouped transactions.

IF NET_CURRENCY_CODE = ‘CONVERT_TO_ACCOUNTING’ then the open balance on each transaction is converted to the the Accounting currency.
The netting currency of the batch = Accounting currency

Validate AP and AR Balances
For every batch that has been created.
AP balance is calculated as (sum of the OPEN_AMT in FUN_NET_AP_INVS_ALL for the given batch)
AR balance is calculated as (sum of the OPEN_AMT in FUN_NET_AR_TXNS_ALL for the given batch) .

When the Netting balance Rule = NET_PAYABLES ,
AP balance should be > the AR balance to continue the netting process. If not the current batch
status is set to ‘ERROR’.
If the AP balance or the AR balance <= 0 then the current batch is set to ‘ERROR’. If AP balance < amount =" AP" amount =" AR">

6.3 Netting Batch Submission Process

The following validations are performed when a netting batch as submitted as there might be a time lag between when the batch is created and when the batch is submitted. It is also possible that the batch might have been modified.
If the current date has gone past the batch settlement date ( sysdate > settlement_date) then the batch is set to status ‘SUSPENDED’ and the AP invoices are unlocked.
The AP and AR balances for the transactions are validated again. If the validation fails the batch is set status ‘ERROR’and the AP invoices are unlocked.
If the batch has passed all validations, it is sent for approval if approval is required else the Settle Netting Batch process is initiated.

6.4 Netting Batch Settlement Process

Validation
The process starts off with the validation of the AR transactions. This is necessary as the AR transactions are not locked when the netting batch is created and therefore there is a possibility that the AR transasction may be have been modified since it was included in the netting batch.
If any of the validations fail , then the batch is set to status ‘CANCELLED’.
Validations are also performed to check whether the GL, AP and AR periods are open for the batch settlement date. If not the batch is set to status ‘ERROR’ and an error message is displayed in the Report Log.

Derive Netting Bank Details
The netting bank details like bank account name, bank account owner and number derived based on the FUN_AGREEMENTS.bank_account_id for the given batch.

Settle AP Invoices
Invoices in each batch are grouped by vendor , vendor site and invoice currency code. The AP Payment APIs are called to process the payments. These API return the check id which is updated in FUN_NET_AP_INVS_ALL.CHECK_ID.

Settle AR Transactions
AR Receipt APIs are called to create and apply the receipts. These APIs return the receipt id which is updated in FUN_NET_AR_TXNS.RECEIPT_ID

If all the processing goes through successfully, the batch status is set to COMPLETE and the AP invoices and netting agreements are unlocked.

6.5 Customer Merge

The TCA Account Merge process calls a netting API to propogate the customer merge updates to the Netting tables.
If the merge results in duplicate accounts and /or account site uses for a netting agreement , then the priority of these records is updated to the highest priority among the records.
All duplicate records for a given agreement are deleted except for one record.

6.6 Supplier Merge

The Payables Supplier Merge process calls a netting API to propagate the supplier merge updates to the Netting tables.
If the merge results in duplicate supplier and /or supplier site for a netting agreement, then the priority of these records is updated to the highest priority among the records.
All duplicate records for a given agreement are deleted except for one record

More on netting in future posts.

Your comments and queries are most welcome.

AutoInvoice

Download PDF

What is AutoInvoice?

Auto Invoice is a tool that can be used to import and validate transaction data from other financial systems from which one can create invoices, debit memos, credit memos, and on-account credits. It rejects transactions with invalid information to insure the integrity of the data. This fits well with in Oracle ERP or to integrate with any third party application.

What Module data can be integrated?
Oracle Order Management
Oracle Project Accounting
Oracle services

To make fully functional what else required?
Loader program
Validation program

Top 10 reasons for using Auto Invoice

1. Powerful Interface Tool
2. Supports Oracle & Non-Oracle Systems
3. Import Large Amount of Data
4. Calculate or Import Tax
5. Group Lines & Invoices
6. Online Error Correction
7 .Lines Validation
8. Derive GL Date
9 .Import Flex fields
10.Import or Derive Accounting Info

What is inside AutoInvoice

AutoInvoice is a tool consists of 3 main programs. Each program will have unique nature of work to do and they are called internally except Purge program whose execution is derived on the setup otherwise ready to execute stand alone.

Master (RAXMTR)
Import (RAXTRX)
Purge (RAXDEL)

1. Auto Invoice Master program RAXMTR
Selects and marks records in the interface tables to be processed based on the parameters the user entered and then calls the AutoInvoice Import program. Auto Invoice Master program has no report output.

•Gathers statistics, it means it gathers the stats on interface tables and set the stats on certain indices on interface tables
•Marks interface records for processing by marking request_id
•Submits multiple workers for Parallel Processing by creating instances for request.

2. Auto Invoice Import Program
Validates the selected record and creates transaction if it passes validation. Any record that fails validation is left in the interface table with an error code. Depending on the setup, related records may be rejected as well. This program has an output file called Auto Invoice Execution report, which you can view by clicking the View Report button in the Requests window.

Working of Auto invoice

Validates data
Inserts records
Deletes interface data
Only when system option purge set to ‘Y’

3. Auto Invoice Purge Program

Deletes records from the interface tables. If you set the Purge Interface Table system option to No in Define System Option window, Auto Invoice does not delete processed records from the interface tables after each run,and we must submit Auto Invoice Purge Program periodically to clean up the interface tables. This program only deletes transaction lines that have been successfully imported.

•Deletes all rows where interface_status =‘P’
•Ra_interface_lines
•Ra_interface_distributions
•Ra_interface_salescredits

Oracle Receivable’s Auto Invoice program will be used to import and validate Invoices.

A custom feeder program is required to transfer data from the Advantage extract files and populate the Auto Invoice interface tables (RA_INTERFACE_LINES_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL).If there is need to run populate sales credit into RA_INTERFACE_SALESCREDITS_ALL table.

When run, AutoInvoice produces the AutoInvoice Execution Report and the AutoInvoice Validation Report. Any entries which failed validation can be reviewed in Oracle Receivables’ AutoInvoice Interface Exceptions window. Depending on the error, changes may need to be made in Receivables, the feeder program or the imported records in the interface tables.

How Autoinvoice Execution works

Normally, Auto Invoice can be divided into three major phases

Pre-grouping: here the validates all of the line level data takes place
Grouping: groups lines based on the grouping rules and validates header level data
Transfer :validates information that exists in Receivables tables


What happen when AutoInvoice run?

Once the Auto invoice Program gets called, the following activity takes place is part of execution process. This can be analyzed by debug options.

Line, accounting, and sales credit information for each line populates 3 interface tables
Lines are ordered and grouped
Tax is calculated
GL date is determined
GL accounts are assigned using Auto Accounting
Tax, freight, commitments, and credit memos are linked to transaction lines
All transactions are batched
Validated lines are used to create the transaction

How Data is flowing?

Select, insert and update and delete take place on certain tables once it is logged out.

Selects
– RA_INTERFACE_LINES_ALL
– RA_INTERFACE_DISTRIBUTIONS_ALL
– RA_INTERFACE_SALESCREDITS_ALL

Updates/Insert

– RA_INTERFACE_ERRORS_ALL
– RA_CUSTOMER_TRX_ALL
– RA_CUSTOMER_TRX_LINES_ALL
– AR_PAYMENT_SCHEDULES_ALL
– AR_RECEIVABLE_APPLICATIONS_ALL

Inserts
– RA_INTERFACE_ERRORS_ALL

AutoInvoice Exception Handling

Records that fail validation are called ‘Exceptions’

Exceptions stay in Interface Tables which is RA_INTERFACE_ERRORS_ALL
Errors can be corrected in the Exception Handling window
Once corrections are made, Auto invoice must be resubmitted
Records that pass validation get transferred to Receivables tables

AutoInvoice Exception Handling Windows

-Interface Exception window displays exception messages associated with all invalid records
-Interface Lines window displays records that fail validation, provides an error message and can be used to correct the errors
-The Line Errors windows displays errors associated with a specific line, and can only be opened from Interface Lines window
-Interface Exceptions window displays Interface Id, Exception Type, Error Message and Invalid Value associated to the error
-Data cannot be edited in this window, but error can be viewed and corrected by clicking the Details button
-Error Message and Column name with invalid data are displayed in the Message column, and the invalid value that needs to be corrected is displayed in the Invalid Value column

Courtesy: www.asoracle.blogspot.com