Form development skills 1 --- the same org only allows one session job at a time

Requirements:

1. Develop a batch release ticket Program

2. At the same time, there can only be one session release for the same org ticket

Solution:

1. (when-New-form-instance) when the program is opened, delete the records that are not cleared due to program exceptions.

Refer to SQL:

Delete program_lock_control

Where program_name = 'release _ wo'

And not exists (

Select 1

From v $ session B

Where B. Sid = A. lock_sid

And B. Serial # = A. lock_serial

And B. audsid = A. lock_audsid );

2. (When-New-form-instance) determines whether the program has been opened. If the program has been opened, the system prompts that the program has been opened and closes the program. Otherwise, insert a record to the control table.

Refer to SQL:

Select count (1)

Into: parameter. v_runner

From program_lock_control

Where program_name = 'release _ wo'

And user_id =: parameter. user_id;

If: parameter. v_runner> 0

Then

Show_alert_message ('you cannot open this program multiple times at the same time! ');

Do_key ('exit _ form ');

Else

Select Sid, serial #, audsid

Into v_sid, v_serial, v_audsid

From v $ session

Where audsid = userenv ('sessionid ');

 

Select user_name

Into v_runner

From fnd_user

Where user_id =: parameter. user_id;

 

Insert into program_lock_control

(Program_name, organization_id, lock_flag, user_id,

User_name, login_time, lock_sid, lock_serial, lock_audsid

)

Values ('release _ wo', 0, 'y',: parameter. user_id,

V_runner, sysdate, v_sid, v_serial, v_audsid

);

 

Commit;

End if;

3. (find when-button-pressed) press the find release ticket button to delete the record caused by an abnormal exit Program (to prevent another user from opening the program and exiting unexpectedly)

Refer to SQL:

Delete program_lock_control

Where program_name = 'release _ wo'

And not exists (

Select 1

From v $ session B

Where B. Sid = A. lock_sid

And B. Serial # = A. lock_serial

And B. audsid = A. lock_audsid );

4. (find when-button-pressed) Determine whether the selected Org has been determined by others. If yes, the user is prompted not to operate. If not, modify and control the table, change the org field to the selected org field and lock the org.

Refer to SQL:

Select count ('*')

Into v_count

From program_lock_control

Where organization_id =: header.org _ id

And program_name = 'release _ wo'

And lock_flag = 'y ';

 

If v_count> 0

Then

Show_alert_message

('Only one person can release the same org ticket at the same time. Currently'

| V_runner

| 'Running this program! '

);

Raise form_trigger_failure;

End if;

Else

Update program_lock_control

Set organization_id =: header.org _ id

Where program_name = 'batch _ release_wo'

And user_id =: parameter. user_id;

End if;

5. When the user exits, the operation is unlocked.

Refer to SQL:

Delete program_lock_control

Where program_name = 'release _ wo'

And user_id =: parameter. user_id;

: System. message_level: = 25;

Commit;

Do_key ('exit _ form ');