Performance Issue Using The Public API "OZF_CLAIM_PUB.Asso_Accruals_To_Claim_Line" (Doc ID 2870063.1)

Last updated on JULY 11, 2022

Applies to:

Symptoms

On : 12.2.6 version,

ACTUAL BEHAVIOR
---------------
Performance issue using the Public API "OZF_CLAIM_PUB.Asso_Accruals_To_Claim_Line"

Query identified as per tkprof:

  SELECT SUM(plan_curr_amount_remaining)
  FROM ozf_funds_utilized_all_b
  WHERE utilization_type IN('ACCRUAL','ADJUSTMENT', 'CHARGEBACK')
  AND org_id = cv_org_id
  AND gl_posted_flag = 'Y'
  AND plan_type = cv_plan_type
  AND plan_id = cv_offer_id
  AND (cv_product_id IS NULL OR product_id = cv_product_id)
  AND (cv_product_level_type IS NULL OR product_level_type = cv_product_level_type)
  AND plan_currency_code = cv_plan_currency_code
  AND cust_account_id = cv_cust_account_id;

EXPECTED BEHAVIOR
-----------------------
Associate earning should not cause performance issue

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
We built a custom package named "AUTOPAY" that is calling 3 APIs to create claims.
For the second API (OZF_CLAIM_PUB.Asso_Accruals_To_Claim_Line) we have performance issue.

1) OZF_CLAIM_PUB.create_claim -- create header and lines
2) OZF_CLAIM_PUB.Asso_Accruals_To_Claim_Line -- Associate claim lines one by one
3) OZF_CLAIM_PUB.update_claim -- Set claim status to CLOSED

The second API (Asso_Accruals_To_Claim_Line) is calling the following private API: APPS.OZF_CLAIM_ACCRUAL_PVT.Update_Group_Line_Util.

 

Cause

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