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