[Home] [Help]
PACKAGE BODY: APPS.CSM_NOTES_TYPE_MAP_EVENT_PKG
Source
1 PACKAGE BODY CSM_NOTES_TYPE_MAP_EVENT_PKG AS
2 /* $Header: csmenmpb.pls 120.1 2008/02/07 08:18:32 anaraman ship $ */
3
4 /*** Globals ***/
5 g_notes_type_map_acc_tab_name CONSTANT VARCHAR2(30) := 'CSM_NOTES_TYPE_MAPPING_ACC';
6 g_notes_type_map_seq_name CONSTANT VARCHAR2(30) := 'CSM_NOTES_TYPE_MAPPING_ACC_S' ;
7 g_notes_type_map_pubi_name CONSTANT VARCHAR2(30) := 'CSM_NOTES_TYPE_MAPPING';
8
9 g_notes_type_map_pkg_name CONSTANT VARCHAR2(30) := 'CSM_NOTES_TYPE_MAP_EVENT_PKG';
10 g_notes_type_map_api_name CONSTANT VARCHAR2(30) := 'REFRESH_ACC';
11
12
13
14 PROCEDURE REFRESH_ACC(p_status OUT NOCOPY VARCHAR2,
15 p_message OUT NOCOPY VARCHAR2) IS
16 PRAGMA AUTONOMOUS_TRANSACTION;
17 /*** get the last run date of the concurent program ***/
18 CURSOR c_LastRundate IS
19 SELECT NVL(LAST_RUN_DATE, to_date('1','J')) LAST_RUN_DATE
20 FROM JTM_CON_REQUEST_DATA
21 WHERE package_name = g_notes_type_map_pkg_name
22 AND procedure_name = g_notes_type_map_api_name;
23
24 --DELETE--
25 CURSOR c_delete IS
26 SELECT ACC.ACCESS_ID
27 FROM CSM_NOTES_TYPE_MAPPING_ACC ACC
28 WHERE NOT EXISTS (SELECT 1
29 FROM JTF_OBJECT_MAPPINGS B
30 WHERE B.MAPPING_ID = ACC.MAPPING_ID
31 AND NVL(B.END_DATE,SYSDATE)>= SYSDATE);
32
33 --UPDATE--
34 CURSOR c_update(b_lastrundate DATE) IS
35 SELECT ACC.ACCESS_ID
36 FROM CSM_NOTES_TYPE_MAPPING_ACC ACC
37 WHERE EXISTS (SELECT 1
38 FROM JTF_OBJECT_MAPPINGS B
39 WHERE B.MAPPING_ID = ACC.MAPPING_ID
40 AND B.LAST_UPDATE_DATE > b_lastrundate );
41
42
43 --INSERT--
44 CURSOR c_insert IS
45 SELECT MAPPING_ID
46 FROM JTF_OBJECT_MAPPINGS B
47 WHERE NVL(B.END_DATE,SYSDATE)>= SYSDATE
48 AND B.OBJECT_CODE = 'JTF_NOTE_TYPE'
49 AND NOT EXISTS (SELECT 1
50 FROM CSM_NOTES_TYPE_MAPPING_ACC ACC
51 WHERE ACC.MAPPING_ID = B.MAPPING_ID);
52
53
54 CURSOR c_user_id IS
55 SELECT USER_ID
56 FROM ASG_USER AU ,
57 ASG_USER_PUB_RESPS AUPR
58 WHERE AU.USER_NAME = AUPR.USER_NAME
59 AND AU.USER_ID = AU.OWNER_ID
60 AND AUPR.PUB_NAME ='SERVICEP';
61
62 TYPE tab_type IS TABLE OF NUMBER;
63
64 l_user_List asg_download.user_list;
65 l_access_list asg_download.access_list;
66 l_id_tab TAB_TYPE;
67 l_lastrundate c_LastRundate%ROWTYPE;
68 l_sqlerrno VARCHAR2(20);
69 l_sqlerrmsg VARCHAR2(4000);
70 l_access_id NUMBER;
71 l_dummy BOOLEAN;
72
73 BEGIN
74
75 CSM_UTIL_PKG.LOG('Entering REFRESH_ACC: ',
76 'CSM_NOTES_TYPE_MAP_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
77
78 OPEN c_lastrundate;
79 FETCH c_lastrundate INTO l_lastrundate;
80 CLOSE c_lastrundate;
81
82 CSM_UTIL_PKG.LOG('Got LASTRUNDATE ',
83 'CSM_NOTES_TYPE_MAP_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
84
85 OPEN c_user_id;
86 FETCH c_user_id BULK COLLECT INTO l_user_list;
87 CLOSE c_user_id;
88
89 --delete--
90 OPEN c_delete;
91 FETCH c_delete BULK COLLECT INTO l_access_list;
92 CLOSE c_delete;
93
94
95 CSM_UTIL_PKG.LOG('Entering DELETE to remove ' || l_access_list.count||' records',
96 'CSM_NOTES_TYPE_MAP_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
97
98 IF l_access_list.COUNT > 0 THEN
99 l_dummy := asg_download.mark_dirty(g_notes_type_map_pubi_name,l_access_list, l_user_list, 'D', sysdate,TRUE );
100 END IF;
101
102 FORALL I IN 1..l_access_list.COUNT
103 DELETE FROM CSM_NOTES_TYPE_MAPPING_ACC WHERE ACCESS_ID=l_access_list(I);
104
105
106
107 CSM_UTIL_PKG.LOG('DELETION successful',
108 'CSM_NOTES_TYPE_MAP_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
109
110 l_access_list.DELETE;
111
112 --update--
113 OPEN c_update(l_lastrundate.LAST_RUN_DATE);
114 FETCH c_update BULK COLLECT INTO l_access_list;
115 CLOSE c_update;
116
117 CSM_UTIL_PKG.LOG('Entering UPDATE to update ' || l_access_list.count||' records',
118 'CSM_NOTES_TYPE_MAP_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
119
120 IF l_access_list.COUNT >0 THEN
121 l_dummy := asg_download.mark_dirty(g_notes_type_map_pubi_name,l_access_list, l_user_list, 'U', sysdate,TRUE );
122 END IF;
123
124
125 CSM_UTIL_PKG.LOG('UPDATE Successful ',
126 'CSM_NOTES_TYPE_MAP_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
127
128 l_access_list.DELETE;
129
130
131 --insert--
132 OPEN c_insert;
133 FETCH c_insert BULK COLLECT INTO l_id_tab;
134 CLOSE c_insert;
135
136 CSM_UTIL_PKG.LOG('Entering INSERT to add ' || l_id_tab.count||' records',
137 'CSM_NOTES_TYPE_MAP_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
138
139 FORALL I IN 1..l_id_tab.COUNT
140 INSERT INTO CSM_NOTES_TYPE_MAPPING_ACC
141 ( ACCESS_ID,
142 MAPPING_ID,
143 COUNTER,
144 CREATED_BY,
145 CREATION_DATE,
146 LAST_UPDATED_BY,
147 LAST_UPDATE_DATE,
148 LAST_UPDATE_LOGIN
149 )
150 VALUES
151 ( CSM_NOTES_TYPE_MAPPING_ACC_S.NEXTVAL,
152 l_id_tab(I),
153 1,
154 1,
155 SYSDATE,
156 1,
157 SYSDATE,
158 1
159 ) RETURNING ACCESS_ID BULK COLLECT INTO l_access_list;
160
161
162 IF l_access_list.COUNT >0 THEN
163 l_dummy := asg_download.mark_dirty(g_notes_type_map_pubi_name,l_access_list, l_user_list, 'I', sysdate,TRUE );
164 END IF;
165
166
167 CSM_UTIL_PKG.LOG('INSERTION Successful ',
168 'CSM_NOTES_TYPE_MAP_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
169
170 UPDATE JTM_CON_REQUEST_DATA
171 SET LAST_RUN_DATE = SYSDATE
172 WHERE package_name = g_notes_type_map_pkg_name
173 AND procedure_name = g_notes_type_map_api_name;
174
175 COMMIT;
176
177 p_status := 'FINE';
178 p_message := 'CSM_NOTES_TYPE_MAP_EVENT_PKG.REFRESH_ACC executed successfully';
179
180 CSM_UTIL_PKG.LOG('Leaving REFRESH_ACC: ',
181 'CSM_NOTES_TYPE_MAP_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
182
183 EXCEPTION
184 WHEN OTHERS THEN
185 l_sqlerrno := TO_CHAR(SQLCODE);
186 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
187 p_status := 'ERROR';
188 p_message := 'Error in CSM_NOTES_TYPE_MAP_EVENT_PKG.REFRESH_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;
189 ROLLBACK;
190 csm_util_pkg.log('CSM_NOTES_TYPE_MAP_EVENT_PKG.REFRESH_ACC ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
191
192 END REFRESH_ACC;
193
194 END CSM_NOTES_TYPE_MAP_EVENT_PKG;