when role have limit access to a user, can't select any bpartner have no contact
Description
BPartner JoeBlock have no contact.
step 1: login with role gardenword admin 1. open payment window 2. in bpartner field open info window 3. can select JoeBlock normal
step 2: 1. make any "private record lock" of user for role gardenword admin 2. open payment window 3. in bpartner field open info window 4. can't select JoeBlock normal
reason: (data from my env) 1. function InfoPanel.testCount will use below
FROM C_BPartner bp LEFT OUTER JOIN C_BPartner_Location l ON (bp.C_BPartner_ID=l.C_BPartner_ID AND l.IsActive='Y') LEFT OUTER JOIN AD_User c ON (bp.C_BPartner_ID=c.C_BPartner_ID AND (c.C_BPartner_Location_ID IS NULL OR c.C_BPartner_Location_ID=l.C_BPartner_Location_ID) AND c.IsActive='Y') LEFT OUTER JOIN C_Location a ON (l.C_Location_ID=a.C_Location_ID) WHERE bp.IsActive='Y' AND bp.IsSummary='N' AND bp.IsActive='Y' AND Upper(bp.Value) Like '%BIALE.VH%' AND bp.IsCustomer = 'Y' AND bp.AD_Client_ID IN(0,1000000) AND bp.AD_Org_ID IN(1000007,0,1000005,1000006) AND (bp.C_BPartner_ID IS NULL OR bp.C_BPartner_ID NOT IN ( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 291 AND AD_User_ID <> 100 AND IsActive = 'Y' ) ) AND (l.C_BPartner_Location_ID IS NULL OR l.C_BPartner_Location_ID NOT IN ( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 293 AND AD_User_ID <> 100 AND IsActive = 'Y' ) ) AND c.AD_User_ID NOT IN (1000757,1000762,1000789,1000758,1000764) AND (c.AD_User_ID IS NULL OR c.AD_User_ID NOT IN ( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 114 AND AD_User_ID <> 100 AND IsActive = 'Y' ) ) AND (a.C_Location_ID IS NULL OR a.C_Location_ID NOT IN ( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 162 AND AD_User_ID <> 100 AND IsActive = 'Y' ) )
2. in function MRole.getRecordWhere make clause AND c.AD_User_ID NOT IN (1000757,1000762,1000789,1000758,1000764)
3. AD_User is left join because in case bpartner have no contact select make a record will AD_User_ID = null with AD_User_ID = null then below logic is false c.AD_User_ID NOT IN (1000757,1000762,1000789,1000758,1000764) => can't query bpartner have no contact
solution: change MRole.getRecordWhere to make where clause to
AND (c.AD_User_ID NOT IN (1000757,1000762,1000789,1000758,1000764) OR c.AD_User_ID IS NULL)
BPartner JoeBlock have no contact.
step 1:
login with role gardenword admin
1. open payment window
2. in bpartner field open info window
3. can select JoeBlock normal
step 2:
1. make any "private record lock" of user for role gardenword admin
2. open payment window
3. in bpartner field open info window
4. can't select JoeBlock normal
reason: (data from my env)
1. function InfoPanel.testCount will use below
FROM
C_BPartner bp LEFT OUTER JOIN C_BPartner_Location l ON (bp.C_BPartner_ID=l.C_BPartner_ID AND l.IsActive='Y')
LEFT OUTER JOIN AD_User c ON (bp.C_BPartner_ID=c.C_BPartner_ID AND (c.C_BPartner_Location_ID IS NULL OR c.C_BPartner_Location_ID=l.C_BPartner_Location_ID) AND c.IsActive='Y')
LEFT OUTER JOIN C_Location a ON (l.C_Location_ID=a.C_Location_ID)
WHERE
bp.IsActive='Y' AND bp.IsSummary='N' AND bp.IsActive='Y' AND Upper(bp.Value) Like '%BIALE.VH%' AND
bp.IsCustomer = 'Y' AND bp.AD_Client_ID IN(0,1000000) AND bp.AD_Org_ID IN(1000007,0,1000005,1000006)
AND (bp.C_BPartner_ID IS NULL OR
bp.C_BPartner_ID NOT IN (
SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 291 AND AD_User_ID <> 100 AND IsActive = 'Y' )
)
AND (l.C_BPartner_Location_ID IS NULL OR l.C_BPartner_Location_ID NOT IN (
SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 293 AND AD_User_ID <> 100 AND IsActive = 'Y' )
)
AND c.AD_User_ID NOT IN (1000757,1000762,1000789,1000758,1000764)
AND (c.AD_User_ID IS NULL OR c.AD_User_ID NOT IN (
SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 114 AND AD_User_ID <> 100 AND IsActive = 'Y' )
)
AND (a.C_Location_ID IS NULL OR a.C_Location_ID NOT IN (
SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 162 AND AD_User_ID <> 100 AND IsActive = 'Y' )
)
2. in function MRole.getRecordWhere make clause
AND c.AD_User_ID NOT IN (1000757,1000762,1000789,1000758,1000764)
3. AD_User is left join because in case bpartner have no contact
select make a record will AD_User_ID = null
with AD_User_ID = null then below logic is false c.AD_User_ID NOT IN (1000757,1000762,1000789,1000758,1000764)
=> can't query bpartner have no contact
solution:
change MRole.getRecordWhere to make where clause to
AND (c.AD_User_ID NOT IN (1000757,1000762,1000789,1000758,1000764) OR c.AD_User_ID IS NULL)
@Carlos Ruiz WDYT about this case and solution?