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.