[Home] [Help]
PACKAGE BODY: APPS.CSM_HA_EVENT_PKG
Source
1 PACKAGE BODY CSM_HA_EVENT_PKG AS
2 /* $Header: csmehab.pls 120.0.12010000.21 2010/06/25 06:39:01 saradhak noship $*/
3 g_debug_level NUMBER; -- debug level
4 G_IS_END_TRACKING_CALL BOOLEAN := FALSE;
5
6 PROCEDURE GET_XML_PAYLOAD
7 ( p_TABLE_NAME IN VARCHAR2,
8 p_PK_NAME_LIST IN CSM_VARCHAR_LIST,
9 p_PK_TYPE_LIST IN CSM_VARCHAR_LIST,
10 p_PK_CHAR_LIST IN CSM_VARCHAR_LIST,
11 x_XML_PAYLOAD OUT NOCOPY CLOB,
12 x_XML_CONTEXT OUT NOCOPY CLOB,
13 x_RETURN_STATUS OUT NOCOPY VARCHAR2,
14 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
15 )
16 AS
17 l_QUERY_TEXT1 VARCHAR2(4000);
18 l_xml CLOB;
19 qrycontext DBMS_XMLGEN.ctxHandle;
20
21 BEGIN
22 CSM_UTIL_PKG.LOG
23 ( 'Entering GET_XML_PAYLOAD for TAble Name : ' || p_TABLE_NAME ,
24 FND_LOG.LEVEL_PROCEDURE);
25
26 --Query Execution status update
27 l_QUERY_TEXT1 := 'SELECT * FROM ' || p_TABLE_NAME ||' WHERE ';
28
29 FOR i in 1..p_PK_NAME_LIST.COUNT LOOP
30 IF(i >1) THEN
31 l_QUERY_TEXT1 := l_QUERY_TEXT1 || ' AND ';
32 END IF;
33 IF p_PK_TYPE_LIST(i) = 'NUMBER' THEN
34 l_QUERY_TEXT1 := l_QUERY_TEXT1 || p_PK_NAME_LIST(i) || ' = ' || p_PK_CHAR_LIST(i) || ' ';
35 ELSE
36 l_QUERY_TEXT1 := l_QUERY_TEXT1 || p_PK_NAME_LIST(i) || ' = ''' ||p_PK_CHAR_LIST(i)|| ''' ';
37 END IF;
38 END LOOP;
39
40 --Execute the SQL query
41 qrycontext := DBMS_XMLGEN.newcontext(l_QUERY_TEXT1) ;
42
43 DBMS_XMLGEN.setnullhandling (qrycontext, DBMS_XMLGEN.empty_tag);
44 l_xml := DBMS_XMLGEN.getxml (qrycontext);
45 dbms_xmlgen.closeContext(qrycontext);
46 x_XML_PAYLOAD := l_xml;
47
48 qrycontext := DBMS_XMLGEN.newcontext('SELECT FND_GLOBAL.user_id,FND_GLOBAL.resp_id,FND_GLOBAL.resp_appl_id,FND_GLOBAL.server_id FROM DUAL') ;
49 DBMS_XMLGEN.setnullhandling (qrycontext, DBMS_XMLGEN.empty_tag);
50 l_xml := DBMS_XMLGEN.getxml (qrycontext);
51 dbms_xmlgen.closeContext(qrycontext);
52
53 x_XML_CONTEXT := l_xml;
54
55 x_return_status := FND_API.G_RET_STS_SUCCESS;
56 x_error_message := 'XML Retrieving Successfully completed ';
57 CSM_UTIL_PKG.LOG
58 ( 'Leaving GET_XML_PAYLOAD after successfully Executing Query-> ' || l_QUERY_TEXT1 , 'CSM_HA_EVENT_PKG.GET_XML_PAYLOAD',
59 FND_LOG.LEVEL_PROCEDURE);
60
61
62 EXCEPTION WHEN OTHERS THEN
63 /*** catch and log exceptions ***/
64 CSM_UTIL_PKG.LOG
65 ( 'Exception occurred in GET_XML_PAYLOAD for Query : ' || l_QUERY_TEXT1 || SUBSTR(SQLERRM,1,3000), 'CSM_HA_EVENT_PKG.GET_XML_PAYLOAD',
66 FND_LOG.LEVEL_EXCEPTION);
67 x_return_status := FND_API.G_RET_STS_ERROR;
68 x_error_message := 'XML Retrieve Failed With Message : ' || SUBSTR(SQLERRM,1,3000) ;
69 END GET_XML_PAYLOAD;
70
71 FUNCTION get_stringfrom_list (pk_list IN CSM_VARCHAR_LIST)
72 RETURN VARCHAR2
73 IS
74 l_string VARCHAR2(1000):='';
75 BEGIN
76 FOR I in 1..pk_list.COUNT
77 LOOP
78 IF(I=1) THEN
79 l_string:=l_string ||pk_list(I);
80 ELSE
81 l_string:=l_string ||','||pk_list(I);
82 END IF;
83 END LOOP;
84
85 RETURN l_string;
86 END get_stringfrom_list;
87
88
89 PROCEDURE TRACK_TABLE(p_table_name IN VARCHAR2,p_PK_NAME_LIST IN CSM_VARCHAR_LIST,
90 p_PK_TYPE_LIST CSM_VARCHAR_LIST,p_mobile_data IN VARCHAR2)
91 IS
92 l_stmt VARCHAR2(4000);
93 l_pk_names VARCHAR2(1000);
94 type t_curs is ref cursor;
95 cur t_curs;
96 l_cnt NUMBER;
97 p_pk_value_list CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST();
98
99 BEGIN
100
101 CSM_UTIL_PKG.LOG
102 ( 'Tracking entire table : '|| p_TABLE_NAME , 'CSM_HA_EVENT_PKG.TRACK_TABLE', FND_LOG.LEVEL_PROCEDURE);
103
104 l_pk_names:=get_stringfrom_list(p_PK_NAME_LIST);
105 l_cnt := p_PK_NAME_LIST.COUNT;
106
107 p_pk_value_list.EXTEND(l_cnt);
108
109 OPEN cur FOR 'SELECT '||l_pk_names||' FROM '||p_table_name;
110 LOOP
111 IF(l_cnt=1) THEN
112 FETCH cur INTO p_pk_value_list(1);
113 ELSIF(l_cnt=2) THEN
114 FETCH cur INTO p_pk_value_list(1),p_pk_value_list(2);
115 ELSIF(l_cnt=3) THEN
116 FETCH cur INTO p_pk_value_list(1),p_pk_value_list(2),p_pk_value_list(3);
117 ELSIF(l_cnt=4) THEN
118 FETCH cur INTO p_pk_value_list(1),p_pk_value_list(2),p_pk_value_list(3),p_pk_value_list(4);
119 ELSE
120 RAISE_APPLICATION_ERROR(-20222,'HA Table Tracking failed since incompatible number of PKs passed');
121 END IF;
122
123 EXIT WHEN cur%NOTFOUND;
124
125 TRACK_HA_RECORD(p_table_name,p_pk_name_list,p_pk_type_list,p_pk_value_list,'U',p_mobile_data);
126
127 END LOOP;
128 END TRACK_TABLE;
129
130 /*
131 This api is used internally and it is called only by RECORD_MFS_DATA api
132 to handle the corner case involved in tracking SDQ data that is still
133 pending for download to Mobile Users at the end of the HA recording session.
134 'Cos of this api we need not process all access tables/SDQ XML Payloads.
135 */
136 PROCEDURE TRACK_MFS_REC_NO_PLD(p_TABLE_NAME VARCHAR2,p_pk_value VARCHAR2)
137 IS
138 l_pld_id NUMBER;
139 BEGIN
140
141 SELECT CSM_HA_PAYLOAD_DATA_S.nextval INTO l_pld_id FROM DUAL;
142
143 INSERT INTO CSM_HA_PAYLOAD_DATA(HA_PAYLOAD_ID ,OBJECT_NAME, PK_VALUE, PARENT_PAYLOAD_ID,DML_TYPE ,MOBILE_DATA, PROCESSED ,
144 CREATION_DATE , CREATED_BY , LAST_UPDATE_DATE ,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
145 VALUES(l_pld_id,p_TABLE_NAME,p_pk_value,l_pld_id,'I','Y','N',SYSDATE,1,SYSDATE,1,1);
146
147 CSM_HA_AUDIT_PKG.AUDIT_RECORD(l_pld_id,'RECORD');
148
149 END TRACK_MFS_REC_NO_PLD;
150
151 PROCEDURE RECORD_CSM_TABLES
152 IS
153 l_PK_NAME_LIST CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST('');
154 l_PK_TYPE_LIST CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST('');
155 l_pk_value_list CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST('');
156
157 type t_curs is ref cursor;
158 cur t_curs;
159 BEGIN
160
161 /**************CSM_AUTO_SYNC_NFN *************/
162 CSM_UTIL_PKG.LOG
163 ( 'Record CSM_AUTO_SYNC_NFN' , 'CSM_HA_EVENT_PKG.RECORD_CSM_TABLES', FND_LOG.LEVEL_PROCEDURE);
164
165 l_pk_name_list(1):= 'NOTIFICATION_ID'; l_pk_type_list(1):= 'NUMBER';
166
167 FOR rec IN (SELECT NOTIFICATION_ID FROM CSM_AUTO_SYNC_NFN
168 WHERE CREATION_DATE >= G_HA_START_TIME)
169 LOOP
170 l_pk_value_list(1):=rec.NOTIFICATION_ID;
171 TRACK_HA_RECORD('CSM_AUTO_SYNC_NFN',l_pk_name_list,l_pk_type_list,l_pk_value_list,'I','Y');
172 END LOOP;
173
174
175 /**************CSM_DEFERRED_NFN_INFO *************/
176 CSM_UTIL_PKG.LOG
177 ( 'Record CSM_DEFERRED_NFN_INFO' , 'CSM_HA_EVENT_PKG.RECORD_CSM_TABLES', FND_LOG.LEVEL_PROCEDURE);
178
179 l_pk_name_list(1):= 'TRACKING_ID'; l_pk_type_list(1):= 'NUMBER';
180
181 FOR rec IN (SELECT TRACKING_ID FROM CSM_DEFERRED_NFN_INFO
182 WHERE CREATION_DATE >= G_HA_START_TIME)
183 LOOP
184 l_pk_value_list(1):=rec.TRACKING_ID;
185 TRACK_HA_RECORD('CSM_DEFERRED_NFN_INFO',l_pk_name_list,l_pk_type_list,l_pk_value_list,'I','Y');
186 END LOOP;
187
188 /**************CSM_SYNC_ERROR_NFN_INFO *************/
189 CSM_UTIL_PKG.LOG
190 ( 'Record CSM_SYNC_ERROR_NFN_INFO' , 'CSM_HA_EVENT_PKG.RECORD_CSM_TABLES', FND_LOG.LEVEL_PROCEDURE);
191
192 l_pk_name_list(1):= 'NOTIFICATION_ID'; l_pk_type_list(1):= 'NUMBER';
193
194 OPEN cur FOR ' SELECT NOTIFICATION_ID FROM CSM_SYNC_ERROR_NFN_INFO '||
195 ' WHERE SYNC_SESSION_ID IN (SELECT SESSION_ID FROM '||asg_base.G_OLITE_SCHEMA||'.C$SYNC_HISTORY '||
196 ' WHERE START_TIME >= :1)' USING G_HA_START_TIME;
197 LOOP
198 FETCH cur INTO l_pk_value_list(1);
199 EXIT WHEN cur%NOTFOUND;
200 TRACK_HA_RECORD('CSM_SYNC_ERROR_NFN_INFO',l_pk_name_list,l_pk_type_list,l_pk_value_list,'I','Y');
201 END LOOP;
202 CLOSE cur;
203
204 /**************CSM INQ tables *************/
205
206 --Copy ALL INQ DATA that are DEFERRED in Standby- 3-PK
207
208 CSM_UTIL_PKG.LOG
209 ( 'Tracking ALL INQ HA records' ,
210 'CSM_HA_EVENT_PKG.RECORD_CSM_TABLES', FND_LOG.LEVEL_PROCEDURE);
211
212 l_pk_name_list.extend(2);l_pk_type_list.extend(2);l_pk_value_list.extend(2);
213 l_pk_name_list(1):= 'CLID$$CS'; l_pk_name_list(2):= 'TRANID$$'; l_pk_name_list(3):= 'SEQNO$$';
214 l_pk_type_list(1):= 'VARCHAR'; l_pk_type_list(2):= 'NUMBER'; l_pk_type_list(3):= 'NUMBER';
215
216
217 FOR rec IN (SELECT INQ_OWNER,INQ_NAME, DEVICE_USER_NAME, DEFERRED_TRAN_ID,SEQUENCE
218 FROM ASG_DEFERRED_TRANINFO info, ASG_PUB_ITEM pi
219 WHERE info.OBJECT_NAME = pi.ITEM_ID
220 AND INQ_NAME IS NOT NULL
221 AND info.CREATION_DATE >= G_HA_START_TIME)
222 LOOP
223 l_pk_value_list(1):= rec.DEVICE_USER_NAME;
224 l_pk_value_list(2):= to_char(rec.DEFERRED_TRAN_ID);
225 l_pk_value_list(3):= to_char(rec.SEQUENCE);
226 TRACK_HA_RECORD(rec.INQ_OWNER||'.'||rec.INQ_NAME,l_pk_name_list,l_pk_type_list,l_pk_value_list,'I','Y');
227 END LOOP;
228
229
230 END RECORD_CSM_TABLES;
231
232 PROCEDURE RECORD_MFS_DATA
233 IS
234 l_PK_NAME_LIST CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST();
235 l_PK_TYPE_LIST CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST();
236 l_pk_value_list CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST();
237 type t_curs is ref cursor;
238 cur t_curs;
239
240 BEGIN
241
242 RECORD_CSM_TABLES; --Record CSM business Tables
243
244
245 /*********************************Record MAF Tables***************************************/
246
247 ---------------- TRACK ENTIRE TABLES ------------------
248 l_PK_NAME_LIST.EXTEND(1); l_pk_type_list.EXTEND(1);
249
250 /*C$ALL_CLIENTS 1-PK */
251 CSM_UTIL_PKG.LOG
252 ( 'Record MOBILEADMIN.C$ALL_CLIENTS' , 'CSM_HA_EVENT_PKG.RECORD_MFS_DATA', FND_LOG.LEVEL_PROCEDURE);
253 l_pk_name_list(1):= 'CLIENTID'; l_pk_type_list(1):= 'VARCHAR';
254 TRACK_TABLE('MOBILEADMIN.C$ALL_CLIENTS',l_pk_name_list,l_pk_type_list,'Y');
255
256
257 /* ASG_USER 1-PK */
258 CSM_UTIL_PKG.LOG
259 ( 'Record ASG_USER' , 'CSM_HA_EVENT_PKG.RECORD_MFS_DATA', FND_LOG.LEVEL_PROCEDURE);
260 l_pk_name_list(1):= 'USER_NAME'; l_pk_type_list(1):= 'VARCHAR';
261 TRACK_TABLE('ASG_USER',l_pk_name_list,l_pk_type_list,'Y');
262
263
264 /* ASG_SEQUENCE_PARTITIONS 2-PK */
265
266 l_PK_NAME_LIST.EXTEND(1); l_pk_type_list.EXTEND(1);
267 CSM_UTIL_PKG.LOG
268 ( 'Record ASG_SEQUENCE_PARTITIONS' , 'CSM_HA_EVENT_PKG.RECORD_MFS_DATA', FND_LOG.LEVEL_PROCEDURE);
269 l_pk_name_list(1):= 'CLIENTID'; l_pk_name_list(2):= 'NAME';
270 l_pk_type_list(1):= 'VARCHAR'; l_pk_type_list(2):= 'VARCHAR';
271 TRACK_TABLE('ASG_SEQUENCE_PARTITIONS',l_pk_name_list,l_pk_type_list,'Y');
272
273
274 -----------------TRACK SPECIFIC RECORDS-------------------
275
276 /*Track Specific MFS records in ASG_DEFERRED_TRANINFO, ASG_USERS_INQINFO,ASG_USERS_INQARCHIVE */
277 CSM_UTIL_PKG.LOG
278 ( 'Track ASG_DEFERRED_TRANINFO HA records' ,
279 'CSM_HA_EVENT_PKG.RECORD_MFS_DATA', FND_LOG.LEVEL_PROCEDURE);
280
281
282
283 --ASG_DEFERRED_TRANINFO -- 3PKS
284
285 l_pk_name_list.DELETE;l_pk_type_list.DELETE; l_pk_value_list.DELETE;
286 l_pk_name_list.EXTEND(3);l_pk_type_list.EXTEND(3); l_pk_value_list.EXTEND(3);
287
288 l_pk_name_list(1):= 'DEVICE_USER_NAME'; l_pk_name_list(2):= 'DEFERRED_TRAN_ID'; l_pk_name_list(3):= 'SEQUENCE';
289 l_pk_type_list(1):= 'VARCHAR'; l_pk_type_list(2):= 'NUMBER'; l_pk_type_list(3):= 'NUMBER';
290
291 FOR rec IN (SELECT DEVICE_USER_NAME,DEFERRED_TRAN_ID,SEQUENCE
292 FROM ASG_DEFERRED_TRANINFO
293 WHERE CREATION_DATE >= G_HA_START_TIME)
294 LOOP
295 l_pk_value_list(1):=rec.DEVICE_USER_NAME;
296 l_pk_value_list(2):=rec.DEFERRED_TRAN_ID;
297 l_pk_value_list(3):=rec.SEQUENCE;
298 TRACK_HA_RECORD('ASG_DEFERRED_TRANINFO',l_pk_name_list,l_pk_type_list,l_pk_value_list,'I','Y');
299 END LOOP;
300
301 --ASG_USERS_INQINFO and ASG_USERS_INQARCHIVE --2 PKS
302
303 CSM_UTIL_PKG.LOG
304 ( 'Track ASG_USERS_INQINFO and ASG_USERS_INQARCHIVE HA records' ,
305 'CSM_HA_EVENT_PKG.RECORD_MFS_DATA', FND_LOG.LEVEL_PROCEDURE);
306
307 l_pk_name_list.DELETE;l_pk_type_list.DELETE; l_pk_value_list.DELETE;
308 l_pk_name_list.extend(2);l_pk_type_list.extend(2);l_pk_value_list.extend(2);
309
310 l_pk_name_list(1):= 'DEVICE_USER_NAME'; l_pk_name_list(2):= 'TRANID';
311 l_pk_type_list(1):= 'VARCHAR'; l_pk_type_list(2):= 'NUMBER';
312
313 FOR rec IN (SELECT DEVICE_USER_NAME,TRANID
314 FROM ASG_USERS_INQINFO
315 WHERE CREATION_DATE >= G_HA_START_TIME)
316 LOOP
317 l_pk_value_list(1):=rec.DEVICE_USER_NAME;
318 l_pk_value_list(2):=rec.TRANID;
319 TRACK_HA_RECORD('ASG_USERS_INQINFO',l_pk_name_list,l_pk_type_list,l_pk_value_list,'I','Y');
320 TRACK_HA_RECORD('ASG_USERS_INQARCHIVE',l_pk_name_list,l_pk_type_list,l_pk_value_list,'I','Y');
321 END LOOP;
322
323 --MOBILEADMIN.C$SYNC_HISTORY 1-PK
324
325 CSM_UTIL_PKG.LOG
326 ( 'Tracking MOBILEADMIN.C$SYNC_HISTORY HA records' ,
327 'CSM_HA_EVENT_PKG.RECORD_MFS_DATA', FND_LOG.LEVEL_PROCEDURE);
328
329 l_pk_name_list.DELETE;l_pk_type_list.DELETE; l_pk_value_list.DELETE;
330 l_pk_name_list.EXTEND(1);l_pk_type_list.EXTEND(1); l_pk_value_list.EXTEND(1);
331
332 l_pk_name_list(1):= 'SESSION_ID';
333 l_pk_type_list(1):= 'NUMBER';
334
335 OPEN cur FOR 'SELECT SESSION_ID FROM '||asg_base.G_OLITE_SCHEMA||'.C$SYNC_HISTORY '
336 ||'WHERE START_TIME >= :1' USING G_HA_START_TIME;
337 LOOP
338 FETCH cur INTO l_pk_value_list(1);
339 EXIT WHEN cur%NOTFOUND;
340 TRACK_HA_RECORD('MOBILEADMIN.C$SYNC_HISTORY',l_pk_name_list,l_pk_type_list,l_pk_value_list,'I','Y');
341 END LOOP;
342 CLOSE cur;
343
344
345 --MOBILEADMIN.C$SYNC_HIS_PUB_ITEMS 3-PK
346
347 CSM_UTIL_PKG.LOG
348 ( 'Tracking MOBILEADMIN.C$SYNC_HIS_PUB_ITEMS HA records' ,
349 'CSM_HA_EVENT_PKG.RECORD_MFS_DATA', FND_LOG.LEVEL_PROCEDURE);
350
351 l_pk_name_list.DELETE;l_pk_type_list.DELETE; l_pk_value_list.DELETE;
352 l_pk_name_list.EXTEND(3);l_pk_type_list.EXTEND(3); l_pk_value_list.EXTEND(3);
353
354 l_pk_name_list(1):= 'SESSION_ID'; l_pk_name_list(2):= 'PUB_ITEM'; l_pk_name_list(3):= 'PHASE';
355 l_pk_type_list(1):= 'NUMBER'; l_pk_type_list(2):= 'VARCHAR'; l_pk_type_list(3):= 'VARCHAR';
356
357 OPEN cur FOR 'SELECT SESSION_ID,PUB_ITEM,PHASE FROM '
358 ||asg_base.G_OLITE_SCHEMA||'.C$SYNC_HIS_PUB_ITEMS '
359 ||'WHERE ROWNUM<2 AND START_TIME >= :1' USING G_HA_START_TIME;
360 LOOP
361 FETCH cur INTO l_pk_value_list(1),l_pk_value_list(2),l_pk_value_list(3);
362 EXIT WHEN cur%NOTFOUND;
363 TRACK_HA_RECORD('MOBILEADMIN.C$SYNC_HIS_PUB_ITEMS',l_pk_name_list,l_pk_type_list,l_pk_value_list,'I','Y');
364 END LOOP;
365 CLOSE cur;
366
367 -- Process ASG_SYSTEM_DIRTY_QUEUE - pending download data
368 CSM_UTIL_PKG.LOG
369 ( 'No Pld Track of ASG_SYSTEM_DIRTY_QUEUE' , 'CSM_HA_EVENT_PKG.RECORD_MFS_DATA', FND_LOG.LEVEL_PROCEDURE);
370
371 FOR rec IN (SELECT DISTINCT USER_ID,HA_PARENT_PAYLOAD_ID
372 FROM ASG_SYSTEM_DIRTY_QUEUE a, ASG_USER b
373 WHERE HA_PARENT_PAYLOAD_ID IS NOT NULL
374 AND (DOWNLOAD_FLAG IS NULL OR TRANSACTION_ID IS NULL)
375 AND a.CLIENT_ID=b.USER_NAME)
376 LOOP
377 TRACK_MFS_REC_NO_PLD('ASG_SYSTEM_DIRTY_QUEUE', rec.HA_PARENT_PAYLOAD_ID||','||rec.USER_ID);
378 END LOOP;
379
380 END RECORD_MFS_DATA;
381
382 PROCEDURE ASSIGN_HA_RESPONSIBILITIES
383 IS
384 BEGIN
385 /*
386 Activate all HA responsibilities mentioned in CSM_HA_RESP_MAPPINGS table
387 -> remove the end-date if present
388 -> reset future start_dates back to sysdate-1
389 */
390 UPDATE FND_RESPONSIBILITY SET END_DATE=null
391 WHERE (APPLICATION_ID,RESPONSIBILITY_ID) IN (SELECT HA_APPLICATION_ID,HA_RESPONSIBILITY_ID
392 FROM CSM_HA_RESP_MAPPINGS)
393 AND END_DATE IS NOT NULL;
394
395 UPDATE FND_RESPONSIBILITY SET START_DATE=sysdate-1
396 WHERE (APPLICATION_ID,RESPONSIBILITY_ID) IN (SELECT HA_APPLICATION_ID,HA_RESPONSIBILITY_ID
397 FROM CSM_HA_RESP_MAPPINGS)
398 AND NVL(START_DATE,SYSDATE-1) > SYSDATE;
399
400
401 /*
402 Assign HA responsibility to users mapped to corresponding non-HA responsibility
403 */
404 FOR rec IN (SELECT usr.USER_NAME,app.APPLICATION_SHORT_NAME app_name,mapp.HA_RESP_KEY resp_key
405 FROM FND_USER_RESP_GROUPS usr_rsp,
406 CSM_HA_RESP_MAPPINGS mapp,
407 FND_APPLICATION app,
408 FND_USER usr
409 WHERE SYSDATE BETWEEN nvl(usr_rsp.START_DATE,SYSDATE-1) AND nvl(usr_rsp.END_DATE,SYSDATE+1)
410 AND usr_rsp.USER_ID=usr.USER_ID
411 AND usr_rsp.RESPONSIBILITY_ID=mapp.RESPONSIBILITY_ID
412 AND usr_rsp.RESPONSIBILITY_APPLICATION_ID= mapp.APPLICATION_ID
413 AND mapp.HA_APPLICATION_ID=app.APPLICATION_ID
414 AND NOT EXISTS (SELECT 1 FROM FND_USER_RESP_GROUPS
415 WHERE USER_ID=usr.USER_ID
416 AND RESPONSIBILITY_ID=mapp.HA_RESPONSIBILITY_ID
417 AND RESPONSIBILITY_APPLICATION_ID=mapp.HA_APPLICATION_ID))
418 LOOP
419 FND_USER_PKG.ADDRESP(rec.USER_NAME,rec.APP_NAME,rec.RESP_KEY,'STANDARD',NULL,sysdate,null);
420 END LOOP;
421
422 END ASSIGN_HA_RESPONSIBILITIES;
423
424 PROCEDURE DEACTIVATE_NON_HA_RESPS
425 IS
426 BEGIN
427
428 UPDATE FND_RESPONSIBILITY SET END_DATE=SYSDATE-1
429 WHERE (APPLICATION_ID,RESPONSIBILITY_ID) NOT IN (SELECT HA_APPLICATION_ID,HA_RESPONSIBILITY_ID
430 FROM CSM_HA_RESP_MAPPINGS)
431 AND NVL(END_DATE, SYSDATE+1) > SYSDATE-1
432 AND APPLICATION_ID not in (0,1);
433
434 END DEACTIVATE_NON_HA_RESPS;
435
436 PROCEDURE MANAGE_CONCURRENT_PROGRAMS
437 IS
438 l_app_id NUMBER;
439 l_grp_id NUMBER;
440 BEGIN
441
442 /*following SQL used only while development as new HA conc progs can be added*/
443 --------------------------------------------------------
444 UPDATE FND_CONCURRENT_PROGRAMS SET ENABLED_FLAG='Y'
445 WHERE (APPLICATION_ID,CONCURRENT_PROGRAM_ID) IN
446 (SELECT APPLICATION_ID,CONCURRENT_PROGRAM_ID FROM CSM_HA_ACTIVE_CONC_DATA);
447 ------------------------------------------------------
448
449 /*
450 --Choose: Api call or the below direct update
451 FOR rec in (SELECT B.APPLICATION_SHORT_NAME APP_NAME, CONCURRENT_PROGRAM_NAME
452 FROM FND_CONCURRENT_PROGRAMS a, FND_APPLICATION b
453 WHERE (APPLICATION_ID,CONCURRENT_PROGRAM_ID) NOT IN
454 (SELECT APPLICATION_ID,CONCURRENT_PROGRAM_ID FROM CSM_HA_ACTIVE_CONC_DATA)
455 AND APPLICATION_ID NOT IN (0,1) -- non AOL
456 AND a.APPLICATION_ID = b.APPLICATION_ID)
457 LOOP
458 FND_PROGRAM.enable_program(rec.CONCURRENT_PROGRAM_NAME,rec.APP_NAME,'N');
459 END LOOP;
460 */
461
462 --direct update
463
464 UPDATE FND_CONCURRENT_PROGRAMS SET ENABLED_FLAG='N'
465 WHERE (APPLICATION_ID,CONCURRENT_PROGRAM_ID) NOT IN
466 (SELECT APPLICATION_ID,CONCURRENT_PROGRAM_ID FROM CSM_HA_ACTIVE_CONC_DATA)
467 AND APPLICATION_ID NOT IN (0,1); -- non AOL
468
469
470 --create a resp group and assign all active concurrent programs to it
471 IF NOT FND_PROGRAM.request_group_exists('CSM_HA_REQUEST_GROUP','CSM') THEN
472 FND_PROGRAM.request_group('CSM_HA_REQUEST_GROUP','CSM');
473 END IF;
474
475 SELECT application_id,request_group_id INTO l_app_id,l_grp_id from fnd_request_groups
476 where request_group_name='CSM_HA_REQUEST_GROUP';
477
478 FOR rec in (SELECT B.APPLICATION_SHORT_NAME APP_NAME, CONCURRENT_PROGRAM_NAME
479 FROM CSM_HA_ACTIVE_CONC_DATA a, FND_APPLICATION b
480 WHERE a.APPLICATION_ID = b.APPLICATION_ID
481 AND a.APPLICATION_ID NOT IN (0,1)
482 AND NOT EXISTS (SELECT 1 FROM FND_REQUEST_GROUP_UNITS
483 WHERE UNIT_APPLICATION_ID=a.APPLICATION_ID
484 AND REQUEST_UNIT_ID=a.CONCURRENT_PROGRAM_ID
485 AND APPLICATION_ID=l_app_id
486 AND REQUEST_GROUP_ID=l_grp_id))
487 LOOP
488 FND_PROGRAM.add_to_group(rec.CONCURRENT_PROGRAM_NAME,rec.APP_NAME,'CSM_HA_REQUEST_GROUP','CSM');
489 END LOOP;
490
491 --assign this request grp to all active non-FND resps
492 UPDATE FND_RESPONSIBILITY SET GROUP_APPLICATION_ID=l_app_id, REQUEST_GROUP_ID=l_grp_id
493 WHERE APPLICATION_ID NOT IN (0,1)
494 AND SYSDATE BETWEEN nvl(START_DATE,SYSDATE-1) AND nvl(END_DATE,SYSDATE+1)
495 AND REQUEST_GROUP_ID<>l_grp_id;
496
497 END MANAGE_CONCURRENT_PROGRAMS;
498
499
500 -- Copy of wfrmitt.sql
501 PROCEDURE WFRMITT(p_item_type IN VARCHAR2)
502 IS
503 BEGIN
504 delete from WF_ITEM_ACTIVITY_STATUSES_H
505 where PROCESS_ACTIVITY in
506 (select INSTANCE_ID from WF_PROCESS_ACTIVITIES
507 where PROCESS_ITEM_TYPE = p_item_type
508 or ACTIVITY_ITEM_TYPE = p_item_type);
509
510 delete from WF_ITEM_ACTIVITY_STATUSES
511 where PROCESS_ACTIVITY in
512 (select INSTANCE_ID from WF_PROCESS_ACTIVITIES
513 where PROCESS_ITEM_TYPE = p_item_type
514 or ACTIVITY_ITEM_TYPE = p_item_type);
515
516 delete from wf_item_attribute_values
517 where ITEM_TYPE = p_item_type;
518
519 delete from wf_items
520 where ITEM_TYPE = p_item_type;
521
522 delete from wf_notification_attributes NA
523 where exists (select 'X' from wf_notifications N
524 where N.notification_id = NA.notification_id
525 and N.message_type = p_item_type);
526
527 delete from wf_comments WC
528 where exists (select 'X' from wf_notifications N
529 where N.notification_id = WC.notification_id
530 and N.message_type = p_item_type);
531
532 delete from wf_notifications
533 where message_type = p_item_type;
534
535 delete from wf_routing_rule_attributes RA
536 where exists (select 'X' from wf_routing_rules R
537 where R.rule_id = RA.rule_id
538 and R.message_type = p_item_type);
539
540 delete from wf_routing_rules
541 where message_type = p_item_type;
542
543 delete from wf_activity_transitions PAT
544 where exists (select 'X' from wf_process_activities PAC
545 where PAT.FROM_PROCESS_ACTIVITY = PAC.instance_id
546 and PAC.PROCESS_ITEM_TYPE = p_item_type);
547
548 delete from wf_activity_attr_values ATV
549 where exists (select 'X' from wf_process_activities PAC
550 where ATV.PROCESS_ACTIVITY_ID = PAC.instance_id
551 and PAC.PROCESS_ITEM_TYPE = p_item_type);
552
553 delete from wf_process_activities
554 where PROCESS_ITEM_TYPE = p_item_type;
555
556 delete from wf_activity_attributes_tl
557 where ACTIVITY_ITEM_TYPE = p_item_type;
558
559 delete from wf_activity_attributes
560 where ACTIVITY_ITEM_TYPE = p_item_type;
561
562 delete from wf_activities_tl ACTL
563 where ACTL.ITEM_TYPE = p_item_type
564 and not exists(select 'X' from wf_process_activities PAC
565 where PAC.ACTIVITY_ITEM_TYPE='WFSTD'
566 and PAC.ACTIVITY_ITEM_TYPE=p_item_type
567 and PAC.ACTIVITY_NAME = ACTL.NAME);
568
569 delete from wf_activities ACT
570 where ACT.ITEM_TYPE = p_item_type
571 and not exists(select 'X' from wf_process_activities PAC
572 where PAC.ACTIVITY_ITEM_TYPE='WFSTD'
573 and PAC.ACTIVITY_ITEM_TYPE=p_item_type
574 and PAC.ACTIVITY_NAME = ACT.NAME);
575
576 delete from wf_message_attributes_tl
577 where message_type = p_item_type;
578
579 delete from wf_message_attributes
580 where message_type = p_item_type;
581
582 delete from wf_messages_tl
583 where type = p_item_type;
584
585 delete from wf_messages
586 where type = p_item_type;
587
588 delete from wf_item_attributes_tl
589 where ITEM_TYPE = p_item_type;
590
591 delete from wf_item_attributes
592 where ITEM_TYPE = p_item_type;
593
594 delete from wf_lookups_tl LUC
595 where exists (select 'X' from WF_LOOKUP_TYPES_TL LUT
596 where LUT.ITEM_TYPE = p_item_type
597 and LUT.LOOKUP_TYPE = LUC.LOOKUP_TYPE);
598
599 delete from wf_lookup_types_tl
600 where ITEM_TYPE = p_item_type;
601
602 delete from wf_item_types_tl
603 where NAME = p_item_type;
604
605 delete from wf_item_types
606 where NAME = p_item_type;
607
608 END WFRMITT;
609
610
611 PROCEDURE DEACTIVATE_WF_COMPONENTS
612 IS
613 l_t NUMBER;
614 BEGIN
615 FOR rec IN (SELECT WF_ITEM_TYPE,WF_EVENT_NAME,WF_EVENT_SUBSCRIPTION_GUID
616 FROM CSM_HA_ACTIVE_WF_COMPONENTS
617 WHERE AUTO_DISABLE_FLAG='Y' AND ENABLED_ON_RECORD='N')
618 LOOP
619 IF rec.WF_EVENT_NAME IS NOT NULL THEN
620 IF rec.WF_EVENT_SUBSCRIPTION_GUID IS NULL THEN
621 UPDATE wf_events SET STATUS='DISABLED' WHERE lower(name)=lower(rec.WF_EVENT_NAME);
622 ELSE
623 UPDATE wf_event_subscriptions SET STATUS='DISABLED'
624 WHERE GUID=rec.WF_EVENT_SUBSCRIPTION_GUID
625 AND EVENT_FILTER_GUID = (select guid from wf_events where lower(name)=lower(rec.WF_EVENT_NAME));
626 END IF;
627 END IF;
628
629 IF rec.WF_ITEM_TYPE IS NOT NULL THEN
630 BEGIN
631 SELECT 1 into l_t
632 from wf_item_types
633 where upper(NAME) = upper(rec.WF_ITEM_TYPE);
634
635 WFRMITT(rec.WF_ITEM_TYPE);
636 -- COMMIT; --as the volume of data might be huge
637 EXCEPTION
638 WHEN OTHERS THEN
639 NULL;
640 END;
641 END IF;
642 END LOOP;
643 END DEACTIVATE_WF_COMPONENTS;
644
645 PROCEDURE SET_HA_PROFILE(p_value IN VARCHAR2)
646 IS
647 PRAGMA AUTONOMOUS_TRANSACTION;
648 x boolean;
649 BEGIN
650
651 CSM_UTIL_PKG.LOG('Setting HA Profile to value :'||p_value,
652 'CSM_HA_EVENT_PKG.SET_HA_PROFILE', FND_LOG.LEVEL_PROCEDURE);
653
654 x:=FND_PROFILE.SAVE('CSM_HA_MODE',p_value,'SITE');
655 COMMIT;
656 END SET_HA_PROFILE;
657
658 FUNCTION GET_HA_PROFILE_VALUE return VARCHAR2
659 IS
660 l_prf VARCHAR2(20);
661 BEGIN
662
663 BEGIN
664 SELECT trim(PROFILE_OPTION_VALUE) INTO l_prf
665 FROM FND_PROFILE_OPTION_VALUES
666 WHERE (APPLICATION_ID,PROFILE_OPTION_ID) IN (SELECT APPLICATION_ID,PROFILE_OPTION_ID
667 FROM FND_PROFILE_OPTIONS
668 WHERE PROFILE_OPTION_NAME ='CSM_HA_MODE')
669 AND LEVEL_ID=10001 AND LEVEL_VALUE=0;
670 EXCEPTION
671 WHEN NO_DATA_FOUND THEN
672 l_prf:=NULL;
673 END;
674
675 CSM_UTIL_PKG.LOG('Current HA Profile value :'||l_prf,
676 'CSM_HA_EVENT_PKG.GET_HA_PROFILE_VALUE', FND_LOG.LEVEL_PROCEDURE);
677
678 return l_prf;
679 END GET_HA_PROFILE_VALUE;
680
681 PROCEDURE SET_SESSION(p_create_flag boolean:=true)
682 IS
683 CURSOR c_get_session
684 IS
685 SELECT CSM_HA_SESSION_INFO_S.NEXTVAL FROM DUAL;
686
687 l_ha_session_id NUMBER;
688 BEGIN
689
690 CSM_UTIL_PKG.LOG('Setting Date format to DD-MON-RR HH24:MI:SS',
691 'CSM_HA_EVENT_PKG.SET_SESSION', FND_LOG.LEVEL_PROCEDURE);
692 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''DD-MON-RR HH24:MI:SS''';
693
694
695
696 BEGIN
697 SELECT session_id,session_start_time,ha_payload_start
698 INTO G_HA_SESSION_SEQUENCE,G_HA_START_TIME,G_HA_PAYLOAD_SEQUENCE_START
699 FROM CSM_HA_SESSION_INFO
700 WHERE SESSION_ID = (SELECT MIN(SESSION_ID) FROM CSM_HA_SESSION_INFO WHERE SESSION_END_TIME IS NULL);
701
702
703 IF p_create_flag THEN -- concurrent request case : to reuse existing session
704
705 CSM_UTIL_PKG.LOG('Re-Using session id '||G_HA_SESSION_SEQUENCE||' created by a concurrent race condition.',
706 'CSM_HA_EVENT_PKG.SET_SESSION', FND_LOG.LEVEL_PROCEDURE);
707
708 G_HA_START_TIME := SYSTIMESTAMP;
709 SELECT CSM_HA_PAYLOAD_DATA_S.nextval INTO G_HA_PAYLOAD_SEQUENCE_START FROM DUAL;
710
711 UPDATE CSM_HA_SESSION_INFO
712 SET SESSION_START_TIME=G_HA_START_TIME,
713 HA_PAYLOAD_START=G_HA_PAYLOAD_SEQUENCE_START
714 WHERE SESSION_ID=G_HA_SESSION_SEQUENCE;
715 END IF;
716
717 CSM_UTIL_PKG.LOG('Current Session set to Id :'||G_HA_SESSION_SEQUENCE,
718 'CSM_HA_EVENT_PKG.SET_SESSION', FND_LOG.LEVEL_PROCEDURE);
719
720 RETURN;
721 EXCEPTION
722 WHEN NO_DATA_FOUND THEN
723 NULL;
724 END;
725
726 IF NOT p_create_flag THEN
727 CSM_UTIL_PKG.LOG('No open session found.Leaving without creating session since session creation is switched OFF',
728 'CSM_HA_EVENT_PKG.SET_SESSION', FND_LOG.LEVEL_PROCEDURE);
729 RETURN;
730 END IF;
731
732 OPEN c_get_session;
733 FETCH c_get_session INTO l_ha_session_id;
734 CLOSE c_get_session;
735
736 CSM_UTIL_PKG.LOG('Creating new Session with Id :'||l_ha_session_id,
737 'CSM_HA_EVENT_PKG.SET_SESSION', FND_LOG.LEVEL_PROCEDURE);
738
739 --Set Session Variables
740 G_HA_SESSION_SEQUENCE := l_ha_session_id;
741 G_HA_START_TIME := SYSTIMESTAMP;
742 G_HA_END_TIME := NULL;
743
744 SELECT CSM_HA_PAYLOAD_DATA_S.nextval INTO G_HA_PAYLOAD_SEQUENCE_START FROM DUAL;
745
746 G_HA_PAYLOAD_SEQUENCE_END := NULL;
747
748
749 INSERT INTO CSM_HA_SESSION_INFO(SESSION_ID, SESSION_START_TIME, SESSION_END_TIME,
750 HA_PAYLOAD_START, HA_PAYLOAD_END, STATUS,
751 COMMENTS, CREATION_DATE, CREATED_BY,
752 LAST_UPDATE_DATE, LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
753 VALUES( G_HA_SESSION_SEQUENCE, G_HA_START_TIME, G_HA_END_TIME,
754 G_HA_PAYLOAD_SEQUENCE_START, G_HA_PAYLOAD_SEQUENCE_END, 'STARTED',
755 'HA Session in Progress', SYSDATE, 1,
756 SYSDATE, 1,1);
757
758 END SET_SESSION;
759
760 --Actions
761 --'A' Apply
762 --'R' Record
763 PROCEDURE SAVE_SEQUENCE(p_session_id IN NUMBER, p_action IN VARCHAR2,
764 p_payload_start IN NUMBER ,p_payload_end IN NUMBER)
765 AS
766
767 CURSOR c_get_limits(b_session_id NUMBER)
768 IS
769 SELECT HA_PAYLOAD_START,HA_PAYLOAD_END FROM CSM_HA_SESSION_INFO WHERE SESSION_ID = b_session_id;
770
771 CURSOR c_get_sequences(b_payload_start NUMBER,b_payload_end NUMBER)
772 IS
773 SELECT SEQ_MAPPING_ID,SEQUENCE_OWNER||'.'||SEQUENCE_NAME ,INCREMENT_BY
774 FROM CSM_HA_SEQ_MAPPINGS
775 WHERE BUSINESS_OBJECT_NAME IN (SELECT OBJECT_NAME
776 FROM CSM_HA_PAYLOAD_DATA
777 WHERE HA_PAYLOAD_ID between b_payload_start AND b_payload_end
778 AND DML_TYPE='I');
779
780 l_start_seq_value NUMBER;
781 l_end_seq_value NUMBER :=0;
782 l_inc_by_value NUMBER :=0;
783 L_SQL_QUERY varchar2(4000);
784
785 l_sequence_list CSM_VARCHAR_LIST;
786 l_curr_seq_value number;
787 L_APPLIED_SEQUENCE_VALUE NUMBER;
788
789 TYPE l_num_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
790 l_seq_map_list l_num_type;
791 l_inc_list l_num_type;
792
793 l_payload_start NUMBER:=p_payload_start;
794 l_payload_end NUMBER:=p_payload_end;
795
796 BEGIN
797
798 IF p_payload_end IS NULL THEN
799 OPEN c_get_limits (p_session_id);
800 FETCH c_get_limits INTO l_payload_start,l_payload_end;
801 CLOSE c_get_limits;
802 END IF;
803
804 OPEN c_get_sequences(l_payload_start,l_payload_end);
805 FETCH c_get_sequences BULK COLLECT INTO l_seq_map_list,l_sequence_list,l_inc_list;
806 CLOSE c_get_sequences;
807
808
809 IF l_seq_map_list.COUNT = 0 THEN
810 CSM_UTIL_PKG.log( 'No Business Object Sequence seems to have been updated in this recording session',
811 'CSM_HA_EVENT_PKG.SAVE_SEQUENCE', FND_LOG.LEVEL_STATEMENT);
812 RETURN;
813 END IF;
814
815 FOR j in 1..l_seq_map_list.COUNT
816 LOOP
817
818 IF p_action ='R' THEN
819 execute immediate 'SELECT ' ||l_sequence_list(j) || '.NEXTVAL - '||l_inc_list(j)||' FROM dual'
820 INTO l_curr_seq_value;
821
822 CSM_UTIL_PKG.log( 'RECORD: SESSION_ID: ' || P_SESSION_ID || ' SEQUENCE_NAME: '
823 ||l_sequence_list(j) || ' Sequence Value: ' || l_curr_seq_value,
824 'CSM_HA_EVENT_PKG.SAVE_SEQUENCE', FND_LOG.LEVEL_STATEMENT);
825
826 BEGIN
827 INSERT INTO CSM_HA_SESSION_SEQ_VALUES(SESSION_ID,SEQ_MAPPING_ID,
828 RECORDED_SEQUENCE,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
829 VALUES(p_session_id,l_seq_map_list(j),l_curr_seq_value,sysdate,1,sysdate,1,1);
830 EXCEPTION
831 WHEN Others THEN
832 CSM_UTIL_PKG.log('Rare Case: Caused by concurrent calls - mostly reported by QA'
833 ,'CSM_HA_EVENT_PKG.SAVE_SEQUENCE', FND_LOG.LEVEL_STATEMENT);
834 UPDATE CSM_HA_SESSION_SEQ_VALUES
835 SET RECORDED_SEQUENCE = l_curr_seq_value
836 WHERE SESSION_ID=p_session_id
837 AND SEQ_MAPPING_ID = L_SEQ_MAP_LIST(J)
838 AND RECORDED_SEQUENCE < l_curr_seq_value;
839 END;
840
841 ELSE
842 execute immediate 'SELECT ' ||l_sequence_list(j) || '.NEXTVAL FROM dual'
843 INTO l_curr_seq_value;
844
845 CSM_UTIL_PKG.log( 'APPLY: SESSION_ID: ' || P_SESSION_ID || ' SEQUENCE_NAME: '
846 ||l_sequence_list(j) || ' Sequence Value: ' || l_curr_seq_value,
847 'CSM_HA_EVENT_PKG.SAVE_SEQUENCE', FND_LOG.LEVEL_STATEMENT);
848
849 UPDATE CSM_HA_SESSION_SEQ_VALUES
850 SET APPLY_SEQUENCE = l_curr_seq_value
851 WHERE SESSION_ID=p_session_id
852 and SEQ_MAPPING_ID = L_SEQ_MAP_LIST(J)
853 RETURNING APPLY_SEQUENCE,RECORDED_SEQUENCE INTO l_start_seq_value,l_end_seq_value;
854
855 l_inc_by_value := l_end_seq_value - l_start_seq_value;
856
857 IF(l_inc_by_value > 0) THEN
858 L_SQL_QUERY := 'ALTER SEQUENCE '||L_SEQUENCE_LIST(J) ||' INCREMENT BY ' || l_inc_by_value;
859 CSM_UTIL_PKG.log(L_SQL_QUERY, 'CSM_HA_EVENT_PKG.SAVE_SEQUENCE', FND_LOG.LEVEL_STATEMENT);
860 execute immediate L_SQL_QUERY;
861
862 /* This select actually increments the sequence value */
863 execute immediate 'SELECT ' ||L_SEQUENCE_LIST(J) || '.NEXTVAL FROM dual'
864 into L_APPLIED_SEQUENCE_VALUE;
865
866 CSM_UTIL_PKG.log('Sequence: ' || L_SEQUENCE_LIST(J) ||
867 ' Modified Sequence Value: ' || l_applied_sequence_value,
868 'CSM_HA_EVENT_PKG.SAVE_SEQUENCE', FND_LOG.LEVEL_STATEMENT);
869
870 UPDATE CSM_HA_SESSION_SEQ_VALUES
871 set AFTER_APPLY_SEQUENCE = L_APPLIED_SEQUENCE_VALUE
872 WHERE SESSION_ID=p_session_id
873 and SEQ_MAPPING_ID = L_SEQ_MAP_LIST(J);
874
875 /* Set the increment_by back to what it was */
876 L_SQL_QUERY := 'ALTER SEQUENCE '||L_SEQUENCE_LIST(J) ||' INCREMENT BY ' || l_inc_list(J);
877 CSM_UTIL_PKG.log(L_SQL_QUERY, 'CSM_HA_EVENT_PKG.SAVE_SEQUENCE',FND_LOG.LEVEL_STATEMENT);
878 execute immediate L_SQL_QUERY;
879
880 end if;
881 END IF;
882
883 END LOOP;
884
885 EXCEPTION WHEN OTHERS THEN
886 /*** catch and log exceptions ***/
887 CSM_UTIL_PKG.LOG
888 ( 'Exception occurred in SAVE_SEQUENCE : ' || SUBSTR(SQLERRM,1,3000), 'CSM_HA_EVENT_PKG.SAVE_SEQUENCE',
889 FND_LOG.LEVEL_EXCEPTION);
890 RAISE;
891 END SAVE_SEQUENCE;
892
893 PROCEDURE MANAGE_MFS_CONC
894 IS
895 BEGIN
896
897 UPDATE JTM_CON_REQUEST_DATA
898 SET EXECUTE_FLAG='N'
899 WHERE PRODUCT_CODE='CSM'
900 AND CATEGORY='LOOKUP'
901 AND (PACKAGE_NAME,PROCEDURE_NAME) NOT IN
902 (('CSM_LOBS_EVENT_PKG','CONC_DOWNLOAD_ATTACHMENTS'));
903
904 END MANAGE_MFS_CONC;
905
906 PROCEDURE RECORD_MISC_DATA
907 IS
908 l_PK_NAME_LIST CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST('');
909 l_PK_TYPE_LIST CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST('');
910 l_pk_value_list CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST('');
911
912 BEGIN
913
914 CSM_UTIL_PKG.LOG('Recording Incident Links','CSM_HA_EVENT_PKG.RECORD_MISC_DATA', FND_LOG.LEVEL_PROCEDURE);
915 --CS_INCIDENT_LINKS
916
917 l_pk_name_list(1):= 'LINK_ID';
918 l_pk_type_list(1):= 'NUMBER';
919
920 FOR rec IN (SELECT LINK_ID
921 FROM CS_INCIDENT_LINKS
922 WHERE CREATION_DATE >= G_HA_START_TIME)
923 LOOP
924 l_pk_value_list(1):=rec.LINK_ID;
925 TRACK_HA_RECORD('CS_INCIDENT_LINKS',l_pk_name_list,l_pk_type_list,l_pk_value_list,'I','N');
926 END LOOP;
927
928 FOR rec IN (SELECT LINK_ID
929 FROM CS_INCIDENT_LINKS
930 WHERE CREATION_DATE < G_HA_START_TIME
931 AND LAST_UPDATE_DATE >= G_HA_START_TIME)
932 LOOP
933 l_pk_value_list(1):=rec.LINK_ID;
934 TRACK_HA_RECORD('CS_INCIDENT_LINKS',l_pk_name_list,l_pk_type_list,l_pk_value_list,'U','N');
935 END LOOP;
936
937 CSM_UTIL_PKG.LOG('Recording Related Objects','CSM_HA_EVENT_PKG.RECORD_MISC_DATA', FND_LOG.LEVEL_PROCEDURE);
938 --CS_INCIDENT_LINKS_EXT
939
940 l_pk_name_list(1):= 'LINK_ID';
941 l_pk_type_list(1):= 'NUMBER';
942
943 FOR rec IN (SELECT LINK_ID
944 FROM CS_INCIDENT_LINKS_EXT
945 WHERE CREATION_DATE >= G_HA_START_TIME)
946 LOOP
947 l_pk_value_list(1):=rec.LINK_ID;
948 TRACK_HA_RECORD('CS_INCIDENT_LINKS_EXT',l_pk_name_list,l_pk_type_list,l_pk_value_list,'I','N');
949 END LOOP;
950
951 FOR rec IN (SELECT LINK_ID
952 FROM CS_INCIDENT_LINKS
953 WHERE CREATION_DATE < G_HA_START_TIME
954 AND LAST_UPDATE_DATE >= G_HA_START_TIME)
955 LOOP
956 l_pk_value_list(1):=rec.LINK_ID;
957 TRACK_HA_RECORD('CS_INCIDENT_LINKS_EXT',l_pk_name_list,l_pk_type_list,l_pk_value_list,'U','N');
958 END LOOP;
959
960
961 CSM_UTIL_PKG.LOG('Recording CUG INCIDENT ATTRIBUTES Data','CSM_HA_EVENT_PKG.RECORD_MISC_DATA', FND_LOG.LEVEL_PROCEDURE);
962 -- CUG_INCIDNT_ATTR_VALS_B
963 l_pk_name_list(1):= 'INCIDNT_ATTR_VAL_ID';
964 l_pk_type_list(1):= 'NUMBER';
965
966 FOR rec IN (SELECT INCIDNT_ATTR_VAL_ID
967 FROM CUG_INCIDNT_ATTR_VALS_B
968 WHERE CREATION_DATE >= G_HA_START_TIME)
969 LOOP
970 l_pk_value_list(1):=rec.INCIDNT_ATTR_VAL_ID;
971 TRACK_HA_RECORD('CUG_INCIDNT_ATTR_VALS_B',l_pk_name_list,l_pk_type_list,l_pk_value_list,'I','N');
972 END LOOP;
973
974 FOR rec IN (SELECT INCIDNT_ATTR_VAL_ID
975 FROM CUG_INCIDNT_ATTR_VALS_B
976 WHERE CREATION_DATE < G_HA_START_TIME
977 AND LAST_UPDATE_DATE >= G_HA_START_TIME)
978 LOOP
979 l_pk_value_list(1):=rec.INCIDNT_ATTR_VAL_ID;
980 TRACK_HA_RECORD('CUG_INCIDNT_ATTR_VALS_B',l_pk_name_list,l_pk_type_list,l_pk_value_list,'U','N');
981 END LOOP;
982
983 CSM_UTIL_PKG.LOG('Recording Access Hours','CSM_HA_EVENT_PKG.RECORD_MISC_DATA', FND_LOG.LEVEL_PROCEDURE);
984 --CSF_ACCESS_HOURS_B
985
986 l_pk_name_list(1):= 'ACCESS_HOUR_ID';
987 l_pk_type_list(1):= 'NUMBER';
988
989 FOR rec IN (SELECT ACCESS_HOUR_ID
990 FROM CSF_ACCESS_HOURS_B
991 WHERE CREATION_DATE >= G_HA_START_TIME)
992 LOOP
993 l_pk_value_list(1):=rec.ACCESS_HOUR_ID;
994 TRACK_HA_RECORD('CSF_ACCESS_HOURS_B',l_pk_name_list,l_pk_type_list,l_pk_value_list,'I','N');
995 END LOOP;
996
997 FOR rec IN (SELECT ACCESS_HOUR_ID
998 FROM CSF_ACCESS_HOURS_B
999 WHERE CREATION_DATE < G_HA_START_TIME
1000 AND LAST_UPDATE_DATE >= G_HA_START_TIME)
1001 LOOP
1002 l_pk_value_list(1):=rec.ACCESS_HOUR_ID;
1003 TRACK_HA_RECORD('CSF_ACCESS_HOURS_B',l_pk_name_list,l_pk_type_list,l_pk_value_list,'U','N');
1004 END LOOP;
1005
1006 CSM_UTIL_PKG.LOG('Recording Required Skills','CSM_HA_EVENT_PKG.RECORD_MISC_DATA', FND_LOG.LEVEL_PROCEDURE);
1007 --CSF_REQUIRED_SKILLS_B
1008
1009 l_pk_name_list(1):= 'REQUIRED_SKILL_ID';
1010 l_pk_type_list(1):= 'NUMBER';
1011
1012 FOR rec IN (SELECT REQUIRED_SKILL_ID
1013 FROM CSF_REQUIRED_SKILLS_B
1014 WHERE CREATION_DATE >= G_HA_START_TIME)
1015 LOOP
1016 l_pk_value_list(1):=rec.REQUIRED_SKILL_ID;
1017 TRACK_HA_RECORD('CSF_REQUIRED_SKILLS_B',l_pk_name_list,l_pk_type_list,l_pk_value_list,'I','N');
1018 END LOOP;
1019
1020 FOR rec IN (SELECT REQUIRED_SKILL_ID
1021 FROM CSF_REQUIRED_SKILLS_B
1022 WHERE CREATION_DATE < G_HA_START_TIME
1023 AND LAST_UPDATE_DATE >= G_HA_START_TIME)
1024 LOOP
1025 l_pk_value_list(1):=rec.REQUIRED_SKILL_ID;
1026 TRACK_HA_RECORD('CSF_REQUIRED_SKILLS_B',l_pk_name_list,l_pk_type_list,l_pk_value_list,'U','N');
1027 END LOOP;
1028
1029 CSM_UTIL_PKG.LOG('Recording MFS Data','CSM_HA_EVENT_PKG.RECORD_MISC_DATA', FND_LOG.LEVEL_PROCEDURE);
1030
1031 RECORD_MFS_DATA;
1032
1033 END RECORD_MISC_DATA;
1034
1035 PROCEDURE END_SESSION
1036 IS
1037 BEGIN
1038 G_HA_SESSION_SEQUENCE := NULL;
1039
1040 UPDATE CSM_HA_SESSION_INFO
1041 SET SESSION_END_TIME = SYSTIMESTAMP,
1042 HA_PAYLOAD_END = G_HA_PAYLOAD_SEQUENCE_END,
1043 STATUS = 'COMPLETED',
1044 COMMENTS = 'HA Recording session successfully completed.',
1045 LAST_UPDATE_DATE = SYSDATE,
1046 LAST_UPDATED_BY = 1
1047 WHERE SESSION_ID = (SELECT MIN(SESSION_ID) FROM CSM_HA_SESSION_INFO WHERE SESSION_END_TIME IS NULL);
1048
1049 END END_SESSION;
1050
1051 PROCEDURE BEGIN_HA_TRACKING(x_RETURN_STATUS OUT NOCOPY VARCHAR2,
1052 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2)
1053 IS
1054 l_module VARCHAR2(500) :='Init';
1055 BEGIN
1056
1057 x_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1058 x_ERROR_MESSAGE := 'HA Recording Successfully Started';
1059
1060 IF GET_HA_PROFILE_VALUE = 'HA_RECORD' THEN
1061 CSM_UTIL_PKG.LOG( 'Call End HA Tracking to end existing session.',
1062 'CSM_HA_EVENT_PKG.BEGIN_HA_TRACKING', FND_LOG.LEVEL_PROCEDURE);
1063 x_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1064 x_ERROR_MESSAGE := 'Another Start HA Recording session is already in progress.';
1065 RETURN;
1066 END IF;
1067
1068 /* To limit concurrent requests: moved this to TOP */
1069 l_module := 'Setting HA profile to Record';
1070 SET_HA_PROFILE('HA_RECORD');
1071
1072 /* SetUp recording*/
1073 CSM_UTIL_PKG.LOG( 'Assigning HA Responsibilities to FND Users with non-HA responsibilities',
1074 'CSM_HA_EVENT_PKG.BEGIN_HA_TRACKING', FND_LOG.LEVEL_PROCEDURE);
1075 l_module := 'Assigning HA Responsibilities';
1076
1077 ASSIGN_HA_RESPONSIBILITIES;
1078 COMMIT;
1079
1080
1081 CSM_UTIL_PKG.LOG( 'Deactivating non-HA and non-FND Responsibilities',
1082 'CSM_HA_EVENT_PKG.BEGIN_HA_TRACKING', FND_LOG.LEVEL_PROCEDURE);
1083 l_module := 'Deactivating non-HA Responsibilities';
1084
1085 DEACTIVATE_NON_HA_RESPS;
1086 COMMIT;
1087
1088 CSM_UTIL_PKG.LOG('Managing Concurrent Programs',
1089 'CSM_HA_EVENT_PKG.BEGIN_HA_TRACKING', FND_LOG.LEVEL_PROCEDURE);
1090 l_module := 'Managing HA Concurrent Programs';
1091
1092 MANAGE_CONCURRENT_PROGRAMS;
1093 COMMIT;
1094
1095 CSM_UTIL_PKG.LOG('Deactivating non-HA WF Components',
1096 'CSM_HA_EVENT_PKG.BEGIN_HA_TRACKING', FND_LOG.LEVEL_PROCEDURE);
1097 l_module := 'Deactivating non-HA WF Components';
1098
1099 DEACTIVATE_WF_COMPONENTS;
1100 COMMIT;
1101
1102
1103 CSM_UTIL_PKG.LOG('Managing JTM Concurrent Programs',
1104 'CSM_HA_EVENT_PKG.BEGIN_HA_TRACKING', FND_LOG.LEVEL_PROCEDURE);
1105 l_module := 'Managing MFS Concurrent Programs';
1106
1107 MANAGE_MFS_CONC;
1108 COMMIT;
1109
1110 l_module := 'Creating a new HA session';
1111 SET_SESSION;
1112
1113 COMMIT;
1114 EXCEPTION
1115 WHEN OTHERS THEN
1116 /*** catch and log exceptions ***/
1117 CSM_UTIL_PKG.LOG
1118 ( 'Exception occurred in BEGIN_HA_TRACKING while '||l_module||':'|| SUBSTR(SQLERRM,1,3000), 'CSM_HA_EVENT_PKG.BEGIN_HA_TRACKING',
1119 FND_LOG.LEVEL_EXCEPTION);
1120 x_return_status := FND_API.G_RET_STS_ERROR;
1121 x_error_message := 'BEGIN_HA_TRACKING failed while '||l_module||' with error:' || SUBSTR(SQLERRM,1,3000) ;
1122 ROLLBACK;
1123 SET_HA_PROFILE('HA_STOP');
1124 RAISE;
1125 END BEGIN_HA_TRACKING;
1126
1127 PROCEDURE END_HA_TRACKING(x_RETURN_STATUS OUT NOCOPY VARCHAR2,
1128 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2)
1129 IS
1130 l_module VARCHAR2(500):='Init';
1131 BEGIN
1132
1133 x_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1134 x_ERROR_MESSAGE := 'Stop HA Recording Successfully Completed';
1135 G_IS_END_TRACKING_CALL := TRUE;
1136
1137 /*to limit concurrent requests: setting profile first */
1138 IF GET_HA_PROFILE_VALUE = 'HA_STOP' THEN
1139 CSM_UTIL_PKG.LOG('Another End recording is already in progress..',
1140 'CSM_HA_EVENT_PKG.END_HA_TRACKING', FND_LOG.LEVEL_PROCEDURE);
1141 x_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1142 x_ERROR_MESSAGE := 'Another Stop HA Recording is already in progress..';
1143 RETURN;
1144 END IF;
1145
1146 l_module := 'Setting HA profile to STOP';
1147 SET_HA_PROFILE('HA_STOP');
1148
1149 SET_SESSION(false); -- to set global variables if not set in this DB session
1150
1151 CSM_UTIL_PKG.LOG('Tracking FND attachments missed by LOOKUP program',
1152 'CSM_HA_EVENT_PKG.END_HA_TRACKING', FND_LOG.LEVEL_PROCEDURE);
1153 l_module := 'Tracking FND attachments missed by LOOKUP program';
1154
1155 TRACK_HA_ATTACHMENTS; --it commits while end_tracking to improve perf
1156
1157 CSM_UTIL_PKG.LOG('Recording Miscellaneous Data',
1158 'CSM_HA_EVENT_PKG.END_HA_TRACKING', FND_LOG.LEVEL_PROCEDURE);
1159 l_module := 'Tracking Miscellaneous Data';
1160
1161 RECORD_MISC_DATA;
1162
1163 l_module := 'Saving Sequence Values of Business Objects';
1164
1165 SELECT CSM_HA_PAYLOAD_DATA_S.nextval INTO G_HA_PAYLOAD_SEQUENCE_END FROM DUAL;
1166
1167 SAVE_SEQUENCE(G_HA_SESSION_SEQUENCE,'R',G_HA_PAYLOAD_SEQUENCE_START,G_HA_PAYLOAD_SEQUENCE_END);
1168
1169
1170 CSM_UTIL_PKG.LOG('End Session with Id: '||G_HA_SESSION_SEQUENCE,
1171 'CSM_HA_EVENT_PKG.END_HA_TRACKING', FND_LOG.LEVEL_PROCEDURE);
1172 l_module := 'Terminating Current Session';
1173 END_SESSION;
1174
1175 G_IS_END_TRACKING_CALL := FALSE;
1176 COMMIT;
1177 EXCEPTION
1178 WHEN OTHERS THEN
1179 /*** catch and log exceptions ***/
1180 CSM_UTIL_PKG.LOG
1181 ( 'Exception occurred in END_HA_TRACKING while '||l_module||':'|| SUBSTR(SQLERRM,1,3000), 'CSM_HA_EVENT_PKG.END_HA_TRACKING',
1182 FND_LOG.LEVEL_EXCEPTION);
1183 x_return_status := FND_API.G_RET_STS_ERROR;
1184 x_error_message := 'END_HA_TRACKING failed while '||l_module||' with error:' || SUBSTR(SQLERRM,1,3000) ;
1185 ROLLBACK;
1186 G_IS_END_TRACKING_CALL := FALSE;
1187 SET_HA_PROFILE('HA_RECORD');
1188 RAISE;
1189 END END_HA_TRACKING;
1190
1191 FUNCTION get_listfrom_String(p_object_name IN VARCHAR2) return CSM_VARCHAR_LIST
1192 IS
1193 l_temp VARCHAR2(1000);
1194 list CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST();
1195 l_item1 VARCHAR2(500);
1196 l_item2 VARCHAR2(500);
1197 l_cnt NUMBER :=1;
1198 BEGIN
1199 l_temp:=p_object_name;
1200 IF instr(l_temp,',') > 0 THEN
1201 LOOP
1202 l_item1 := trim(substr(l_temp,1,instr(l_temp,',')-1));
1203 list.extend(1);
1204 list(l_cnt) := l_item1;
1205 l_cnt := l_cnt+1;
1206 l_item2 := trim(substr(l_temp,instr(l_temp,',')+1));
1207 l_temp:= l_item2;
1208 EXIT WHEN instr(l_temp,',') = 0;
1209 END LOOP;
1210 ELSE
1211 l_item2:=l_temp;
1212 END IF;
1213
1214 IF(length(l_item2)>0) THEN
1215 list.extend(1);
1216 list(l_cnt) := l_item2;
1217 END IF;
1218
1219 RETURN list;
1220
1221 END get_listfrom_String;
1222
1223 FUNCTION get_pk_column_name(p_object_name IN VARCHAR2) return VARCHAR2
1224 IS
1225 CURSOR c_pk
1226 IS
1227 SELECT FOREIGN_KEY_COLUMN
1228 FROM CSM_HA_AUX_MAPPINGS
1229 WHERE BO_TABLE_NAME=AO_TABLE_NAME
1230 AND BO_TABLE_NAME=p_object_name
1231 AND AUX='N';
1232
1233 l_pk VARCHAR2(500);
1234 BEGIN
1235
1236 OPEN c_pk;
1237 FETCH c_pk INTO l_pk;
1238 CLOSE c_pk;
1239
1240 IF l_pk IS NOT NULL THEN
1241 RETURN l_pk;
1242 END IF;
1243
1244 IF SUBSTR(p_object_name,-4)='_INQ' THEN
1245 RETURN 'CLID$$CS,TRANID$$,SEQNO$$';
1246 ELSIF p_object_name='CSM_AUTO_SYNC_NFN' THEN
1247 RETURN 'NOTIFICATION_ID';
1248 ELSIF p_object_name='CSM_DEFERRED_NFN_INFO' THEN
1249 RETURN 'TRACKING_ID';
1250 ELSIF p_object_name='CSM_SYNC_ERROR_NFN_INFO' THEN
1251 RETURN 'NOTIFICATION_ID';
1252 ELSIF p_object_name='MOBILEADMIN.C$ALL_CLIENTS' THEN
1253 RETURN 'CLIENTID';
1254 ELSIF p_object_name='MOBILEADMIN.C$SYNC_HISTORY' THEN
1255 RETURN 'SESSION_ID';
1256 ELSIF p_object_name='MOBILEADMIN.C$SYNC_HIS_PUB_ITEMS' THEN
1257 RETURN 'SESSION_ID,PUB_ITEM,PHASE';
1258 ELSIF p_object_name='ASG_USER' THEN
1259 RETURN 'USER_NAME';
1260 ELSIF p_object_name='ASG_DEFERRED_TRANINFO' THEN
1261 RETURN 'DEVICE_USER_NAME,DEFERRED_TRAN_ID,SEQUENCE';
1262 ELSIF p_object_name='ASG_USERS_INQINFO' THEN
1263 RETURN 'DEVICE_USER_NAME,TRANID';
1264 ELSIF p_object_name='ASG_USERS_INQARCHIVE' THEN
1265 RETURN 'DEVICE_USER_NAME,TRANID';
1266 ELSIF p_object_name='ASG_SEQUENCE_PARTITIONS' THEN
1267 RETURN 'CLIENTID,NAME';
1268 END IF;
1269
1270 RETURN NULL;
1271
1272 END get_pk_column_name;
1273
1274 FUNCTION get_predicate_clause(p_cols IN VARCHAR2,p_values IN VARCHAR2) return VARCHAR2
1275 IS
1276 l_pk_col_list CSM_VARCHAR_LIST;
1277 l_pk_value_list CSM_VARCHAR_LIST;
1278 l_clause VARCHAR2(3000);
1279 BEGIN
1280 l_pk_col_list:=get_listfrom_String(p_cols);
1281 l_pk_value_list:=get_listfrom_String(p_values);
1282
1283 IF l_pk_col_list.COUNT=0 THEN
1284 RETURN NULL;
1285 END IF;
1286
1287 l_clause:= ' WHERE '||l_pk_col_list(1)||'='''||l_pk_value_list(1)||''' ';
1288
1289 FOR j IN 2..l_pk_col_list.COUNT
1290 LOOP
1291 l_clause:= l_clause||' AND '||l_pk_col_list(j)||'='''||l_pk_value_list(j)||''' ';
1292 END LOOP;
1293
1294 RETURN l_clause;
1295 END get_predicate_clause;
1296
1297 PROCEDURE TRACK_AUX_TABLE_RECORDS(p_parent_payload_id IN NUMBER)
1298 IS
1299 l_ax_pk_column VARCHAR2(100);
1300 l_ax_pk_value VARCHAR2(100);
1301 type t_curs is ref cursor;
1302 cur t_curs;
1303
1304 l_bo_name VARCHAR2(100);
1305 l_pk_value VARCHAR2(100);
1306
1307 l_PK_NAME_LIST CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST();
1308 l_PK_TYPE_LIST CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST();
1309 l_pk_value_list CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST();
1310
1311 BEGIN
1312
1313 SELECT OBJECT_NAME,PK_VALUE INTO l_bo_name,l_pk_value
1314 FROM CSM_HA_PAYLOAD_DATA
1315 WHERE HA_PAYLOAD_ID=p_parent_payload_id;
1316
1317 CSM_UTIL_PKG.LOG
1318 ( 'Check for aux with parent payload Id:'||p_parent_payload_id
1319 ||' of TAB/PK:'||l_bo_name||'/'||l_pk_value,
1320 'CSM_HA_EVENT_PKG.TRACK_AUX_TABLE_RECORDS', FND_LOG.LEVEL_PROCEDURE);
1321
1322 FOR rec IN (SELECT AO_TABLE_NAME,FETCH_SQL
1323 FROM CSM_HA_AUX_MAPPINGS
1324 WHERE BO_TABLE_NAME=l_bo_name
1325 AND AUX='Y'
1326 AND BO_TABLE_NAME <> AO_TABLE_NAME
1327 AND FETCH_SQL IS NOT NULL
1328 AND ENABLED_FLAG='Y' ORDER BY AUX_MAPPING_ID)
1329 LOOP
1330
1331 l_ax_pk_column := get_pk_column_name(rec.AO_TABLE_NAME);
1332
1333 l_pk_name_list:=get_listfrom_String(l_ax_pk_column);
1334 l_pk_type_list.extend(l_pk_name_list.COUNT);
1335 FOR J IN 1..l_pk_name_list.COUNT
1336 LOOP
1337 l_pk_type_list(J):='VARCHAR';
1338 END LOOP;
1339
1340 OPEN CUR FOR rec.FETCH_SQL USING l_pk_value;
1341 LOOP
1342 FETCH CUR INTO l_ax_pk_value;
1343 EXIT WHEN CUR%NOTFOUND;
1344
1345 CSM_UTIL_PKG.LOG
1346 ( 'Tracking Aux table:'||rec.AO_TABLE_NAME||' with ('||l_ax_pk_column||')=('||l_ax_pk_value||')',
1347 'CSM_HA_EVENT_PKG.TRACK_AUX_TABLE_RECORDS', FND_LOG.LEVEL_PROCEDURE);
1348
1349 l_pk_value_list := get_listfrom_String(l_ax_pk_value);
1350
1351 TRACK_HA_RECORD(rec.AO_TABLE_NAME ,l_pk_name_list,l_pk_type_list,l_pk_value_list,'I','N',p_parent_payload_id);
1352 END LOOP;
1353 CLOSE cur;
1354 END LOOP;
1355
1356 END TRACK_AUX_TABLE_RECORDS;
1357
1358 PROCEDURE TRACK_HA_RECORD(p_TABLE_NAME VARCHAR2,p_PK_NAME_LIST CSM_VARCHAR_LIST, p_PK_TYPE_LIST CSM_VARCHAR_LIST,p_PK_VALUE_LIST CSM_VARCHAR_LIST,
1359 p_dml_type VARCHAR2,p_mobile_data VARCHAR2,p_parent_payload_id IN NUMBER)
1360 IS
1361 l_XML_PAYLOAD CLOB;
1362 l_XML_CONTEXT CLOB;
1363 l_RETURN_STATUS VARCHAR2(100);
1364 l_ERROR_MESSAGE VARCHAR2(4000);
1365 l_tracking_ON varchar2(20);
1366 l_pk_values VARCHAR2(1000);
1367 l_pld_id NUMBER;
1368 BEGIN
1369
1370
1371 l_pk_values:=get_stringfrom_list(p_PK_VALUE_LIST);
1372
1373 CSM_UTIL_PKG.LOG
1374 ( 'Entering TRACK_HA_RECORD with '||p_TABLE_NAME||'-'||l_pk_values||'-'||p_dml_type||'-'||p_mobile_data||'-'||p_parent_payload_id ,
1375 'CSM_HA_EVENT_PKG.TRACK_HA_RECORD', FND_LOG.LEVEL_PROCEDURE);
1376
1377
1378 l_tracking_ON := GET_HA_PROFILE_VALUE;
1379
1380 IF (NOT G_IS_END_TRACKING_CALL) AND (l_tracking_ON IS NULL OR l_tracking_ON <> 'HA_RECORD') THEN
1381 CSM_UTIL_PKG.LOG
1382 ( 'Leaving TRACK_HA_RECORD as tracking is switched OFF' , 'CSM_HA_EVENT_PKG.TRACK_HA_RECORD', FND_LOG.LEVEL_PROCEDURE);
1383 RETURN;
1384 END IF;
1385
1386 SET_SESSION(false);
1387
1388 IF(G_HA_SESSION_SEQUENCE IS NULL) THEN -- never happens
1389 CSM_UTIL_PKG.LOG
1390 ( 'No Open session found. Leaving without tracking.' ,
1391 'CSM_HA_EVENT_PKG.TRACK_HA_RECORD', FND_LOG.LEVEL_PROCEDURE);
1392 RETURN;
1393 END IF;
1394
1395 GET_XML_PAYLOAD(p_TABLE_NAME,p_PK_NAME_LIST,p_PK_TYPE_LIST,p_PK_VALUE_LIST,l_XML_PAYLOAD,l_XML_CONTEXT,l_RETURN_STATUS,l_ERROR_MESSAGE);
1396
1397 IF(l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS) THEN
1398 RAISE_APPLICATION_ERROR(-20222,'HA Tracking failed :'||l_ERROR_MESSAGE);
1399 ELSE
1400 IF (l_XML_PAYLOAD IS NULL) THEN
1401 CSM_UTIL_PKG.LOG('PAYLOAD IS NULL', 'CSM_HA_EVENT_PKG.TRACK_HA_RECORD',FND_LOG.LEVEL_PROCEDURE);
1402 RAISE_APPLICATION_ERROR(-20222,'HA Tracking failed : XML Payload is null');
1403 END IF;
1404
1405
1406 SELECT CSM_HA_PAYLOAD_DATA_S.nextval INTO l_pld_id FROM DUAL;
1407
1408 INSERT INTO CSM_HA_PAYLOAD_DATA(HA_PAYLOAD_ID ,OBJECT_NAME , PK_VALUE, PARENT_PAYLOAD_ID,DML_TYPE , PAYLOAD, CONTEXT ,MOBILE_DATA, PROCESSED ,
1409 STATUS ,COMMENTS ,CREATION_DATE , CREATED_BY , LAST_UPDATE_DATE ,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
1410 VALUES(l_pld_id,p_TABLE_NAME,l_pk_values,NVL(p_parent_payload_id,l_pld_id),p_dml_type,xmltype(l_XML_PAYLOAD),xmltype(l_XML_CONTEXT),p_mobile_data,'N',NULL,NULL,SYSDATE,1,SYSDATE,1,1);
1411
1412 CSM_HA_AUDIT_PKG.AUDIT_RECORD(l_pld_id,'RECORD');
1413
1414 IF p_parent_payload_id IS NULL THEN
1415 G_CURRENT_PAYLOAD_ID:=l_pld_id;
1416 END IF;
1417
1418 IF(p_mobile_data='N' and p_PK_NAME_LIST.count=1) THEN
1419 TRACK_AUX_TABLE_RECORDS(l_pld_id);
1420 END IF;
1421 END IF;
1422
1423 EXCEPTION
1424 WHEN OTHERS THEN
1425 CSM_UTIL_PKG.LOG
1426 ( 'Exception occurred in TRACK_HA_RECORD -' || SUBSTR(SQLERRM,1,3000), 'CSM_HA_EVENT_PKG.TRACK_HA_RECORD',FND_LOG.LEVEL_EXCEPTION);
1427 RAISE;
1428 END TRACK_HA_RECORD;
1429
1430 PROCEDURE TRACK_HA_ATTACHMENTS
1431 IS
1432 l_PK_NAME_LIST CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST('');
1433 l_PK_TYPE_LIST CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST('');
1434 l_pk_value_list CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST('');
1435
1436 CURSOR c_last_run_date
1437 IS
1438 SELECT NVL(last_run_date,to_date(1,'J'))
1439 FROM jtm_con_request_data
1440 WHERE package_name = 'CSM_LOBS_EVENT_PKG'
1441 AND procedure_name = 'CONC_DOWNLOAD_ATTACHMENTS';
1442
1443 l_last_run_date DATE;
1444
1445 BEGIN
1446
1447 SET_SESSION(false);
1448 IF G_HA_SESSION_SEQUENCE IS NULL THEN
1449 RETURN;
1450 END IF;
1451
1452 OPEN c_last_run_date;
1453 FETCH c_last_run_date INTO l_last_run_date;
1454 CLOSE c_last_run_date;
1455
1456 IF G_HA_START_TIME > l_last_run_date THEN
1457 l_last_run_date:= G_HA_START_TIME;
1458 END IF;
1459
1460 FOR rec IN (SELECT DOCUMENT_ID FROM FND_DOCUMENTS a WHERE CREATION_DATE > l_last_run_date
1461 AND NOT EXISTS(SELECT 1 FROM CSM_HA_PAYLOAD_DATA b
1462 WHERE HA_PAYLOAD_ID > G_HA_PAYLOAD_SEQUENCE_START
1463 AND OBJECT_NAME='FND_DOCUMENTS'
1464 AND PK_VALUE=to_char(a.DOCUMENT_ID)
1465 AND DML_TYPE='I'))
1466 LOOP
1467 l_PK_NAME_LIST(1):='DOCUMENT_ID'; l_PK_TYPE_LIST(1):='NUMBER'; l_pk_value_list(1):= to_char(rec.DOCUMENT_ID);
1468 CSM_HA_EVENT_PKG.TRACK_HA_RECORD('FND_DOCUMENTS',l_PK_NAME_LIST,l_PK_TYPE_LIST,l_pk_value_list,'I');
1469 END LOOP;
1470
1471 FOR rec IN (SELECT DOCUMENT_ID FROM FND_DOCUMENTS a WHERE CREATION_DATE < l_last_run_date
1472 AND LAST_UPDATE_DATE > l_last_run_date
1473 AND NOT EXISTS(SELECT 1 FROM CSM_HA_PAYLOAD_DATA b
1474 WHERE HA_PAYLOAD_ID > G_HA_PAYLOAD_SEQUENCE_START
1475 AND OBJECT_NAME='FND_DOCUMENTS'
1476 AND PK_VALUE=to_char(a.DOCUMENT_ID)
1477 AND DML_TYPE='U'))
1478 LOOP
1479 l_PK_NAME_LIST(1):='DOCUMENT_ID'; l_PK_TYPE_LIST(1):='NUMBER'; l_pk_value_list(1):= to_char(rec.DOCUMENT_ID);
1480 CSM_HA_EVENT_PKG.TRACK_HA_RECORD('FND_DOCUMENTS',l_PK_NAME_LIST,l_PK_TYPE_LIST,l_pk_value_list,'U');
1481 END LOOP;
1482
1483 IF(G_IS_END_TRACKING_CALL) THEN
1484 COMMIT;
1485 END IF;
1486
1487 END TRACK_HA_ATTACHMENTS;
1488
1489 END CSM_HA_EVENT_PKG;