0058148: Performance Alerts 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 country addresses. Please define one of them 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 not exists (select 1
              from c_bpartner_location bpl0
              where bpl0.c_bpartner_id = bp.c_bpartner_id
              and bpl0.istaxlocation = 'Y'
              )
and 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
              group by bpl.c_bpartner_id
              having count(distinct(l.c_country_id)) > 1
            )
In the field "Tab" put Bussines Partner
and run the Alert Process

Proposed Solution

creation of an index:
c_bpartner_location(istaxlocation);
and change the 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 country addresses. Please define one of them 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)
group by bpl.c_bpartner_id
having count(distinct(l.c_country_id)) > 1
) aux
on bp.c_bpartner_id = aux.c_bpartner_id
where not exists (select 1
from c_bpartner_location bpl0
where bpl0.c_bpartner_id = bp.c_bpartner_id
and bpl0.istaxlocation = 'Y'
)

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

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
There are no notes attached to this issue.