After Adding Custom Validation in OKL_BILL_GROUP_CLIENT_EXTN Package, Process Bill Group Update Requests Errors with ORA-06502 (Doc ID 2882147.1)

Last updated on JULY 08, 2022

Applies to:

Symptoms

On : 12.2.9 version, Customer Service

After adding custom validation in OKL_BILL_GROUP_CLIENT_EXTN package to not allow contracts with certain criteria to be added to existing billing group with contracts, the concurrent program "Process Bill Group Update Requests" is run to insert contract to Billing Group.

While validating scenario 3 below, program errors in Package OKL_BL_GRP_UPD_PVT.BILL_GRP_UPDATES (Which is causing to program to error ORA-06502: PL/SQL: numeric or value error: character to number conversion error in Package OKL_BL_GRP_UPD_PVT Procedure BILL_GRP_UPDATES)

The following issues are observed:

Issue-1:
--------
OKL_BILL_GROUP_CLIENT_EXTN.validate_BG_contract_extn (
  x_return_status => x_return_status
  ,x_msg_count => x_msg_count
  ,x_msg_data =>x_msg_count ---------------------------- This is Oracle Bug Should be x_msg_data
  ,p_khr_id => c_bill_group_lines_csr_rec.khr_id
  ,p_bgrp_hdr_rec => lx_bghv_rec);

Issue-2:
---------
When raising exception

IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
  RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR; ------------ Oracle Bug should be RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
  ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
  RAISE OKC_API.G_EXCEPTION_ERROR; ------------ Oracle Bug should be RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
  END IF;

Issue-3:
---------

Even after correcting these code, if OKL_BILL_GROUP_CLIENT_EXTN.validate_BG_contract_extn is returning error that contract is not valid and cannot be associated to billing group, the concurrent program "Process Bill Group Update Requests" ends in following Error:

1: User-Defined Exception in Package OKL_BL_GRP_UPD_PVT Procedure BILL_GRP_UPDATES
2: User-Defined Exception in Package OKL_BL_GRP_UPD_PUB Procedure OKL_BL_GRP_UPD_PUB

On billing group screen the following error is shown and cannot be updated with any contract.

"Bill Group Associations cannot be added as Bill Group Revision request is in Process."

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Put any custom validation failure in package okl_bill_group_client_extn. validate_bg_contract_extn.
2. Navigate to Lease Super User -> Operations -> Billing Group Search for existing Active Billing Group
3. Update Billing Group and add contract at Billing Group Line Level.
4. Program “Process Bill Group Update Requests” ends in error
5. Come back to Billing Group page and try to add another contract Billing Group Lines is locked with above message.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.