[Home] [Help]
1564:
1565: /*------------------------------------------------------------------------------------------
1566: Procedure GET_SYSTEM_LOCK
1567: Due to the fact that not all the BSC modules will uptake the new locking scheme
1568: immediately, we will temporaily keep populating the BSC_CURRENT_SESSIONS table
1569: for backward compatibility issues. In this procedure, we will check for whole
1570: system exclusive locks acquired by modules that haven't uptaken the new
1571: locking scheme. Only when none of these modules have acquired locks that
1572: the new Object-Level locking will be proceeded. Next, a row will be inserted
1569: for backward compatibility issues. In this procedure, we will check for whole
1570: system exclusive locks acquired by modules that haven't uptaken the new
1571: locking scheme. Only when none of these modules have acquired locks that
1572: the new Object-Level locking will be proceeded. Next, a row will be inserted
1573: into BSC_CURRENT_SESSIONS. It will be seen as a whole system exclusive lock by
1574: modules haven't implemented the new locking scheme. For modules that have uptaken
1575: the new locking scheme, those entries in BSC_CURRENT_SESSION will be ignored.
1576:
1577: p_program_id: -100 = Data Loader UI
1603: l_session_id NUMBER;
1604:
1605: CURSOR c_conflict_session(c_program_id NUMBER) IS
1606: SELECT c.program_id, u.user_name, s.machine, s.terminal
1607: FROM bsc_current_sessions c, v$session s, bsc_apps_users_v u
1608: WHERE c.session_id = s.audsid
1609: AND ((c_program_id = -100 AND c.program_id IN (-300, -400, -500, -700, -800, -802))
1610: OR (c_program_id = -101 AND c.program_id IN (-300, -400, -500, -700, -800, -802))
1611: OR (c_program_id = -200 AND c.program_id IN (-300, -400, -500, -700, -800, -802))
1623: AND c.user_id = u.user_id (+);
1624:
1625: CURSOR c_existing_session(c_program_id NUMBER, c_icx_session_id NUMBER) IS
1626: SELECT SESSION_ID
1627: FROM BSC_CURRENT_SESSIONS
1628: WHERE SESSION_ID = USERENV('SESSIONID')
1629: AND ICX_SESSION_ID = c_icx_session_id
1630: AND PROGRAM_ID = c_program_id;
1631:
1636: BSC_LOCKS_PVT.Initialize;
1637:
1638: BSC_SECURITY.Refresh_System_Lock(p_program_id);
1639: --Delete all orphan the sessions
1640: --DELETE BSC_CURRENT_SESSIONS
1641: --WHERE SESSION_ID NOT IN
1642: -- (SELECT VS.AUDSID
1643: -- FROM V$SESSION VS);
1644: --
1642: -- (SELECT VS.AUDSID
1643: -- FROM V$SESSION VS);
1644: --
1645: --Delete all the session not being reused by FND
1646: --DELETE BSC_CURRENT_SESSIONS
1647: --WHERE ICX_SESSION_ID IN (
1648: -- SELECT SESSION_ID
1649: -- FROM ICX_SESSIONS
1650: -- WHERE (FND_SESSION_MANAGEMENT.CHECK_SESSION(SESSION_ID,NULL,NULL,'N') <> 'VALID'));
1649: -- FROM ICX_SESSIONS
1650: -- WHERE (FND_SESSION_MANAGEMENT.CHECK_SESSION(SESSION_ID,NULL,NULL,'N') <> 'VALID'));
1651: --
1652: --Delete all sessions, which have their concurrent programs in invalid or hang status
1653: --DELETE BSC_CURRENT_SESSIONS
1654: --WHERE SESSION_ID IN (
1655: -- SELECT NVL(ORACLE_SESSION_ID, -1)
1656: -- FROM FND_CONCURRENT_REQUESTS
1657: -- WHERE PHASE_CODE = 'C');
1658: --
1659: -- Kill IViewer Sessions that have been INACTIVE more than 20 minutes
1660: --IF p_program_id <> -600 THEN
1661: -- IF BSC_APPS.APPS_ENV THEN
1662: -- DELETE BSC_CURRENT_SESSIONS
1663: -- WHERE PROGRAM_ID = -600
1664: -- AND SESSION_ID IN (
1665: -- SELECT s.audsid
1666: -- FROM v$session s, v$session_wait w
1669: -- END IF;
1670: --END IF;
1671: --
1672: --Delete all the Killed Sessions
1673: --DELETE BSC_CURRENT_SESSIONS
1674: --WHERE SESSION_ID IN (
1675: -- SELECT VS.AUDSID
1676: -- FROM V$SESSION VS
1677: -- WHERE VS.STATUS = 'KILLED');
1689:
1690: OPEN c_existing_session(p_program_id, p_icx_session_id);
1691: FETCH c_existing_session INTO l_session_id;
1692: IF (c_existing_session%NOTFOUND) THEN
1693: INSERT INTO BSC_CURRENT_SESSIONS (
1694: SESSION_ID,
1695: PROGRAM_ID,
1696: CREATED_BY,
1697: CREATION_DATE,
1747:
1748: /*------------------------------------------------------------------------------------------
1749: Procedure REMOVE_SYSTEM_LOCK
1750: Due to the fact that not all the BSC modules will uptake the new locking scheme
1751: immediately, we will temporaily keep populating the BSC_CURRENT_SESSIONS table
1752: for backward compatibility issues. This procedure will be called at the end
1753: of the process. The entry in BSC_CURRENT_SESSIONS for the current session
1754: will be deleted
1755:
1749: Procedure REMOVE_SYSTEM_LOCK
1750: Due to the fact that not all the BSC modules will uptake the new locking scheme
1751: immediately, we will temporaily keep populating the BSC_CURRENT_SESSIONS table
1752: for backward compatibility issues. This procedure will be called at the end
1753: of the process. The entry in BSC_CURRENT_SESSIONS for the current session
1754: will be deleted
1755:
1756: none
1757: -------------------------------------------------------------------------------------------*/
1759: IS
1760: PRAGMA AUTONOMOUS_TRANSACTION;
1761: BEGIN
1762: --DBMS_OUTPUT.PUT_LINE('REMOVE_SYSTEM_LOCK');
1763: DELETE BSC_CURRENT_SESSIONS
1764: WHERE SESSION_ID = USERENV('SESSIONID');
1765: COMMIT;
1766: EXCEPTION
1767: WHEN OTHERS THEN