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.