Section access with partial reload leads to lock-out

dirk_fischer — Wed, 15 May 2024 13:23:19 GMT

Hi all,

we are using a partial reload to update the section access of an app. This has been working nicely yesterday, but all of a sudden, it does not work any longer.

We are doing a sign-in procedure to sign-in at the shift of a machine. We are writing the result of the OSUser() as crew member and then, we want to update the section access, so that the assigned users have each access to the data of the machine, they are signed in. I have attached the code of the sub, I'm using. If I run this code in a full reload, it works as expected.

If I run it in a partial reload and I want to sign-in, it fails with an Error: Unknown LOAD statement error. If I then execute it in a full load, it works as expected.

If I run it in a partial reload and I want to sign-out, it does not fail, but I don't have access to the app afterwards anymore. Here it is the same, if I execute the code in a full load, it works as expected.

Does anybody have an idea, what is causing this and how to overcome the problem?

Already in advance, I want to thank you for your support.

Best regards,

Dirk

Sub ActivateSectionAccess
vStartOfSub = Now();
Let vTraceMsg = 'Start of sub activateSectionAccess at ' & Timestamp('$(vStartOfSub)', 'YYYY-MM-DD hh:mm:ss');
Trace '$(vTraceMsg)';

Call DropTable('ACCESS_TABLE');

Call TraceBeforeTableOp('Adding the default users to table ACCESS_TABLE', 'ACCESS_TABLE');
ACCESS_TABLE:
Add Load
ACCESS
,USERID
,REDUCTION
Inline [ACCESS, USERID, REDUCTION
ADMIN, INTERNAL\SA_REPOSITORY,
ADMIN, INTERNAL\SA_SCHEDULER,
ADMIN, INTERNAL\SA_API,
ADMIN, ACCOUNTS\ADMHUETTMAC,
ADMIN, ACCOUNTS\HUETTMAC,
USER, *, SHIFTS_OF_TODAY
]
;
Call TraceAfterTableOp('Adding the default users to table ACCESS_TABLE', 'ACCESS_TABLE');

If FieldNumber('USER_CREW_MEMBER', 'FACT_TABLE') > 0 Then
Call DropTable('TEMP');
Call TraceBeforeTableOp('Extracting all users, who are signed-in to a shift of a machine', 'TEMP');
TEMP:
Add NoConcatenate Load Distinct
'USER' As ACCESS
,'ACCOUNTS\' & Upper(USER_CREW_MEMBER) As USERID
,%ID_SAP_WORKPLACE
Resident FACT_TABLE
Where WildMatch(Num(%ID_DATASOURCE), 302)
And
Len(USER_CREW_MEMBER)
And
IsNull(TIMESTAMP_SIGN_OUT)
And
Len([%ID_SAP_WORKPLACE])
;
Call TraceAfterTableOp('Extracting all users, who are signed-in to a shift of a machine', 'TEMP');

Call TraceBeforeTableOp('Adding all possible datasources from table FACT_TABLE to table TEMP', 'TEMP');
Left Join (TEMP)
Add Load Distinct
[%ID_DATASOURCE]
Inline [%ID_DATASOURCE
OEE_SHEET_EVENTS
SHIFTS_OF_TODAY
MACHINE_STATE
NETPLAN_CONFIG
]
;
Call TraceAfterTableOp('Adding all possible datasources from table FACT_TABLE to table TEMP', 'TEMP');

Call TraceBeforeTableOp('Adding the necessary REDUCTION values for all users, who are signed-in, to table TEMP', 'TEMP');
Inner Join (TEMP)
Add Load Distinct
USERID
,[%ID_DATASOURCE] & '_' & [%ID_SAP_WORKPLACE] As REDUCTION
Resident TEMP
;
Call TraceAfterTableOp('Adding the necessary REDUCTION values for all users, who are signed-in, to table TEMP', 'TEMP');

Call TraceBeforeTableOp('Adding the all datasets from table TEMP to table ACCESS_TABLE', 'ACCESS_TABLE');
Concatenate (ACCESS_TABLE)
Add Load Distinct
ACCESS
,USERID
,REDUCTION
Resident TEMP
;
Call TraceAfterTableOp('Adding the all datasets from table TEMP to table ACCESS_TABLE', 'ACCESS_TABLE');

Call DropTable('TEMP');
Else
Let vTraceMsg = 'The field USER_CREW_MEMBER does not exist in the table FACT_TABLE, there are no signed-in users to add to the ACCESS_TABLE';
Trace '$(vTraceMsg)';
End If

Section Access;
Replace Load
*
Resident ACCESS_TABLE
;
Section Application;

Call DropTable('ACCESS_TABLE');

Let vTraceMsg = 'End of sub activateSectionAccess - execution took ' & Interval(RangeSum(Now(), -Num('$(vStartOfSub)')), 'hh:mm:ss');
Trace '$(vTraceMsg)';
End Sub

20240515T151405.489+0200 0149 Section Access
20240515T151405.489+0200 0150 Replace Load
20240515T151405.489+0200 0151 *
20240515T151405.489+0200 0152 Resident ACCESS_TABLE
20240515T151405.489+0200 3 fields found: ACCESS, USERID, REDUCTION,
20240515T151405.490+0200 Error: Unknown LOAD statement error

Re: Section access with partial reload leads to lock-out

dirk_fischer — Thu, 16 May 2024 06:35:52 GMT

Good morning experts

I found the root cause for the problem I had. In the section access I modified my code and now use the code below

Section Access;
Replace NoConcatenate Load
*
Resident ACCESS_TABLE
;
Section Application;

Call DropTable('ACCESS_TABLE');

and now, the problem is gone and the reload does, what it is supposed to do. So it looks like in the partial reload, the NoConcatenate is needed to make sure, the data is written into the correct target (=section access table).

Best regards,

Dirk

Best regards,

Dirk

topic Re: Section access with partial reload leads to lock-out in App Development

Section access with partial reload leads to lock-out

Re: Section access with partial reload leads to lock-out