0058149: Alerts Performance in 25Q1

Description

Problems in the performance of the Alert, since the entire business partner table has to be traversed.

Steps To Reproduce

Create this alert with this query:
select bp.c_bpartner_id AS REFERENCEKEY_ID,
'0' AS AD_ROLE_ID,
NULL AS AD_USER_ID,
'Business Partner: "' || AD_COLUMN_IDENTIFIER('C_BPartner', bp.C_BPARTNER_ID, 'en_US')
|| '" has defined different countries as Tax Location. Only one country should be defined as Tax Location' AS DESCRIPTION,
'Y' AS ISACTIVE,
bp.AD_ORG_ID AS AD_ORG_ID,
bp.AD_CLIENT_ID AS AD_CLIENT_ID,
now() AS CREATED,
'0' AS CREATEDBY,
now() AS UPDATED,
'0' AS UPDATEDBY,
AD_COLUMN_IDENTIFIER('C_BPartner', bp.C_BPARTNER_ID, 'en_US') AS RECORD_ID
from c_bpartner bp
where exists (select 1
              from c_bpartner_location bpl
              inner join c_location l on (bpl.c_location_id = l.c_location_id)
              where bpl.c_bpartner_id = bp.c_bpartner_id
              and bpl.istaxlocation = 'Y'
              group by bpl.c_bpartner_id, bpl.istaxlocation
              having count(distinct(l.c_country_id)) > 1
              )
n the field "Tab" put Bussines Partner
and run the Alert Process

Proposed Solution

creation of an index:
c_bpartner_location(istaxlocation);
select bp.c_bpartner_id AS REFERENCEKEY_ID,
'0' AS AD_ROLE_ID,
NULL AS AD_USER_ID,
'Business Partner: "' || AD_COLUMN_IDENTIFIER('C_BPartner', bp.C_BPARTNER_ID, 'en_US')
|| '" has defined different countries as Tax Location. Only one country should be defined as Tax Location' AS DESCRIPTION,
'Y' AS ISACTIVE,
bp.AD_ORG_ID AS AD_ORG_ID,
bp.AD_CLIENT_ID AS AD_CLIENT_ID,
now() AS CREATED,
'0' AS CREATEDBY,
now() AS UPDATED,
'0' AS UPDATEDBY,
AD_COLUMN_IDENTIFIER('C_BPartner', bp.C_BPARTNER_ID, 'en_US') AS RECORD_ID
from c_bpartner bp
inner join ( select c_bpartner_id
from c_bpartner_location bpl
inner join c_location l on (bpl.c_location_id = l.c_location_id)
where bpl.istaxlocation = 'Y'
group by bpl.c_bpartner_id, bpl.istaxlocation
having count(distinct(l.c_country_id)) > 1
) aux
on aux.c_bpartner_id = bp.c_bpartner_id

Issue History
Date Modified	Username	Field	Change
2025-03-05 17:02	sofidossant	New Issue
2025-03-05 17:02	sofidossant	Assigned To	=> Triage Platform Base
2025-03-05 17:02	sofidossant	Triggers an Emergency Pack	=> No

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
There are no notes attached to this issue.