How To Restrict The Query Generated By IntegrityCheck Based On Run-time Data? (Doc ID 2174087.1)

Last updated on APRIL 01, 2024

Applies to:

Goal

There is an issue using IntegrityCheck for large customers. The query generated by IntegrityCheck over the asset results in fetching over 400k records which lead the Object Manager to crash.

Below are the input for calling IntegrityCheck

• Master Account Id: (Primary Account Id)
• Account Id: (Service Account Id)
• Target Document: "Quote"
• All Assets: "Y"
• Active Document Id: (Quote Id)

and here also the clause applied over S_ASSET responsible for such huge query:

• (T2.MASTER_OU_ID = :5 AND T25.STATUS_CD != 'Inactive' AND T16.NAME IS NOT NULL AND T25.STATUS_CD != 'Inactive')
where T2 represents the S_ORG_EXT and T25 the S_ASSET

This query retrieves all the assets of each Account belonging to the primary Account in focus, finally 400k records.

Looking at the Doc ID 1375349.1 a solution has been already provided by Oracle for the same issue. Such solution offers a way to control the query done by the BS VORD Projected Asset Cache but does not explain how to control the same query when it's invoked within IntegrityCheck.

In other words, customer is looking for a way to restrict the query over the assets/Quote/Order generated by Integrity Check (via VORD) based on run-time data (e.g. [Service Account Id] = [&ServiceAccountId]).

Solution

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