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 ');