[Home] [Help]
PACKAGE BODY: APPS.ASG_CONS_QPKG
Source
1 PACKAGE BODY asg_cons_qpkg AS
2 /*$Header: asgconqb.pls 120.8.12020000.3 2013/04/09 11:55:31 saradhak ship $*/
3
4 -- DESCRIPTION
5 -- This package contains callbacks registered with Oracle Lite
6 --
7 --
8 -- HISTORY
9 -- 15-mar-2011 saradhak Added Synchronous upload logic
10 -- 12-aug-2009 saradhak Process Synchronous Mobile queries in upload_complete
11 -- 15-May-2008 saradhak 12.1- Auto Sync
12 -- 01-jun-2004 ssabesan Fix bug 3666810
13 -- 01-jun-2004 ssabesan Merge 115.24.1158.29 to main line(11.5.9.6)
14 -- Change literal to bind variables.
15 -- 26-mar-2004 rsripada Fix bug 3536657
16 -- 19-mar-2004 ssabesan Fix bug 3518589
17 -- 04-jan-2004 ssabesan changed the method process_compref_table
18 -- 27-jan-2004 ssabesan Comment off the code in populate_q_rec_count
19 -- 01-oct-2003 ssabesan Purge SDQ changes (bug 3170790)
20 -- 12-jun-2003 rsripada Added support to store device type
21 -- 10-apr-2003 ssabesan use last_wireless_contact_date for error logging
22 -- 31-mar-2003 rsripada Fix Online-query bug: 2878674
23 -- 28-mar-2003 rsripada Store synctime end in asg_user table
24 -- 25-mar-2003 ssabesan update synch_errors column with synch time errors
25 -- 25-feb-2003 rsripada Added validate_login method
26 -- 24-feb-2003 rsripada update hwm_tranid so that it can handle
27 -- exceptions in download
28 -- 19-feb-2003 pkanukol Support for processing asg_purge_sdq at synch time
29 -- 11-feb-2003 rsripada Support for conflict detection
30 -- 10-feb-2003 rsripada change asg_disable_custom
31 -- to asg_disable_custom_synch
32 -- 06-jan-2003 ssabesan Added NOCOPY in function definition
33 -- 06-jan-2003 ssabesan Check whether logging is enabled before invoking
34 -- logging procedure.
35 -- 12-dec-2002 rsripada Added support to disable download of custom pis
36 -- 11-nov-2002 ssabesan added code for pub items upgrade
37 -- 04-oct-2002 ssabesan commented out logging in download size estimate
38 -- procedures
39 -- 09-sep-2002 rsripada Raise exception if an user synch is disabled
40 -- 06-sep-2002 ssabesan added code for determining num of rows downloaded
41 -- 17-jul-2002 rsripada Raise exception in upload for any errors
42 -- 27-jun-2002 rsripada Added support for UI to track synch errors
43 -- 26-jun-2002 rsripada Remove Olite dependencies
44 -- 29-may-2002 rsripada Logging Support
45 -- 24-may-2002 rsripada Added sequence processing during upload
46 -- 15-may-2002 rsripada Modified download_init
47 -- 14-may-2002 vekrishn Increased the mesg in LOG to 4000
48 -- 25-apr-2002 rsripada Added final api for download_init
49 -- 16-apr-2002 rsripada Created
50
51 g_stmt_level NUMBER := FND_LOG.LEVEL_STATEMENT;
52 g_err_level NUMBER := FND_LOG.LEVEL_ERROR;
53
54 g_first_synch BOOLEAN := FALSE;
55 g_auto_synch CHAR := 'N';
56 g_last_synch_successful VARCHAR2(1) := FND_API.G_TRUE;
57 g_device_type VARCHAR2(30):= NULL;
58
59 PROCEDURE get_pubitem_list(p_pubitem_tbl IN OUT NOCOPY asg_base.pub_item_tbl_type)
60 IS
61 counter PLS_INTEGER;
62 l_cursor_id NUMBER;
63 l_cursor_ret NUMBER;
64 l_select_pi_sqlstring VARCHAR2(4000);
65 l_pubitem_name VARCHAR2(30);
66 l_comp_ref VARCHAR2(1);
67 BEGIN
68 --set the pub item table to empty
69 p_pubitem_tbl:=asg_base.g_empty_pub_item_tbl;
70 l_select_pi_sqlstring :=
71 'SELECT name, comp_ref ' ||
72 'FROM ' || asg_base.G_OLITE_SCHEMA ||'.' ||'c$pub_list_q';
73
74 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
75 DBMS_SQL.PARSE (l_cursor_id, l_select_pi_sqlstring, DBMS_SQL.v7);
76 DBMS_SQL.DEFINE_COLUMN (l_cursor_id, 1, l_pubitem_name, 30);
77 DBMS_SQL.DEFINE_COLUMN (l_cursor_id, 2, l_comp_ref, 1);
78
79 l_cursor_ret := DBMS_SQL.EXECUTE (l_cursor_id);
80 counter := 1;
81 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id) > 0 ) LOOP
82 DBMS_SQL.COLUMN_VALUE (l_cursor_id, 1, l_pubitem_name);
83 DBMS_SQL.COLUMN_VALUE (l_cursor_id, 2, l_comp_ref);
84 p_pubitem_tbl(counter).name := l_pubitem_name;
85 p_pubitem_tbl(counter).comp_ref := l_comp_ref;
86 counter := counter +1;
87 END LOOP;
88
89 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
90
91 END get_pubitem_list;
92
93 FUNCTION is_previous_synch_successful(p_user_name IN VARCHAR2,
94 p_last_tranid IN NUMBER)
95 RETURN VARCHAR2 IS
96 l_stored_last_tranid NUMBER;
97 BEGIN
98 SELECT nvl(last_tranid, 0) into l_stored_last_tranid
99 FROM asg_user
100 WHERE user_name = p_user_name;
101
102 IF (p_last_tranid > l_stored_last_tranid) THEN
103 return FND_API.G_TRUE;
104 ELSE
105 return FND_API.G_FALSE;
106 END IF;
107
108 END is_previous_synch_successful;
109
110
111 --12.1
112 PROCEDURE insert_auto_sync_tranids(p_user_name IN VARCHAR2,
113 p_upload_tranid IN NUMBER)
114 IS
115 PRAGMA AUTONOMOUS_TRANSACTION;
116 BEGIN
117 insert into asg_auto_sync_tranids
118 (user_name, upload_tranid, sync_id,
119 creation_date, created_by, last_update_date, last_updated_by )
120 values
121 (p_user_name, p_upload_tranid, NULL, sysdate,1, sysdate,1);
122 COMMIT;
123 EXCEPTION
124 WHEN OTHERS THEN
125 ROLLBACK;
126 RAISE;
127 END insert_auto_sync_tranids;
128
129 --12.1
130 FUNCTION set_sync_id (p_user_name IN VARCHAR2,
131 p_upload_tranid IN NUMBER)
132 RETURN NUMBER
133 IS --PRAGMA AUTONOMOUS_TRANSACTION;
134 CURSOR c_sync_id(b_user_name VARCHAR2)
135 IS
136 SELECT MAX(upload_tranid)
137 FROM asg_auto_sync_tranids
138 WHERE USER_NAME=b_user_name
139 AND SYNC_ID IS NULL;
140
141 l_sync_id NUMBER;
142
143 BEGIN
144
145 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
146 asg_helper.log('Entering set_sync_id: Clientid: ' ||
147 p_user_name || ' tranid: ' || p_upload_tranid,
148 'asg_cons_qpkg',g_stmt_level);
149 END IF;
150
151 IF p_upload_tranid IS NULL THEN
152 OPEN c_sync_id(p_user_name);
153 FETCH c_sync_id INTO l_sync_id;
154 CLOSE c_sync_id;
155 ELSE
156 l_sync_id:=p_upload_tranid;
157 END IF;
158
159 UPDATE asg_auto_sync_tranids SET SYNC_ID= l_sync_id
160 WHERE USER_NAME=p_user_name
161 AND SYNC_ID IS NULL;
162 -- COMMIT;
163
164 RETURN l_sync_id;
165 EXCEPTION
166 WHEN OTHERS THEN
167 -- ROLLBACK;
168 RETURN l_sync_id;
169 END set_sync_id;
170
171
172 -- Notifies that inq has a new transaction
173 PROCEDURE upload_complete(p_clientid IN VARCHAR2,
174 p_tranid IN NUMBER)
175 IS
176 l_return_status VARCHAR2(1);
177 l_sqlerror_message VARCHAR2(512);
178 l_disabled_synch_message VARCHAR2(2000);
179 synch_disabled EXCEPTION;
180 l_sync_id NUMBER;
181 BEGIN
182 -- This call will be made after all the uploaded data is committed.
183 -- Since during apply processing we account for the case when sync
184 -- fails after commit and before this call completed, we do not need
185 -- to do any processing.
186 IF(asg_helper.check_is_log_enabled(g_stmt_level))
187 THEN
188 asg_helper.log('Upload complete called for client: ' ||
189 p_clientid || ' tranid: ' || p_tranid,
190 'asg_cons_qpkg',g_stmt_level);
191 END IF;
192
193 BEGIN
194 -- Check if user's synch is disabled
195 IF asg_helper.is_user_synch_enabled(p_clientid,l_disabled_synch_message)
196 = FND_API.G_FALSE THEN
197 raise synch_disabled;
198 END IF;
199 asg_base.set_upload_tranid(p_tranid);
200 --set hwm_tranid to tranid-1 and make synch_errors null
201 -- Moved to download_init Because we shouldn't process uploads when it is in auto sync
202 -- asg_helper.set_synch_errmsg(p_clientid,(p_tranid-1),null,null);
203
204 asg_apply.process_sequences(p_clientid, p_tranid, l_return_status);
205
206 asg_apply.process_mobile_queries(p_clientid, p_tranid, l_return_status);
207
208 asg_apply.setup_inq_info(p_clientid, p_tranid, l_return_status);
209 --12.1
210 insert_auto_sync_tranids(p_clientid, p_tranid);
211 COMMIT;
212
213 -- Synchronous Upload logic
214 IF (asg_base.is_auto_sync() = 'N')
215 AND (fnd_profile.value_specific('ASG_SYNCHRONOUS_UPLOAD',asg_base.get_user_id(p_clientid))='Y') THEN
216
217 l_sync_id:=set_sync_id(p_clientid, p_tranid);
218 UPDATE asg_user SET hwm_tranid = nvl(l_sync_id,hwm_tranid) WHERE user_name = p_clientid;
219 asg_apply.synchronous_process_upload(p_clientid);
220
221 END IF;
222
223 EXCEPTION
224 WHEN synch_disabled THEN
225 IF(asg_helper.check_is_log_enabled(g_err_level))
226 THEN
227 asg_helper.log('User Synch Error: ' || p_clientid || ' ' ||
228 to_char(sysdate, 'yyyy-mm-dd') ||
229 ' Synch is not enabled.',
230 'asg_cons_qpkg',g_err_level);
231 END IF;
232 IF l_disabled_synch_message IS NULL THEN
233 l_disabled_synch_message := 'Synch is not enabled.';
234 END IF;
235 asg_helper.set_synch_errmsg(p_clientid,null,g_device_type,
236 'User Synch Error: '||l_disabled_synch_message);
237 RAISE_APPLICATION_ERROR(-20994, l_disabled_synch_message);
238 WHEN OTHERS THEN
239 l_sqlerror_message := SQLERRM;
240 IF(asg_helper.check_is_log_enabled(g_err_level))
241 THEN
242 asg_helper.log(
243 'User Synch Error: ' || p_clientid || ' ' ||
244 to_char(sysdate, 'yyyy-mm-dd') ||
245 ' Exception in upload_complete. ' || l_sqlerror_message,
246 'asg_cons_qpkg', g_err_level);
247 END IF;
248 asg_helper.set_synch_errmsg(p_clientid,null,g_device_type,
249 'User Synch Error: ' ||
250 ' Exception in upload_complete. ' ||
251 l_sqlerror_message);
252 RAISE_APPLICATION_ERROR(-20995, 'Exception during upload ' ||
253 l_sqlerror_message);
254 END;
255 END upload_complete;
256
257 -- Initialize data for download
258 -- Final API
259 PROCEDURE download_init(p_clientid IN VARCHAR2,
260 p_last_tranid IN NUMBER,
261 p_curr_tranid IN NUMBER,
262 p_high_prty IN VARCHAR2)
263 IS
264 l_upload_tranid NUMBER;
265 l_last_synch_date DATE;
266 l_first_synch BOOLEAN;
267 l_disabled_synch_message VARCHAR2(2000);
268 l_sqlerror_message VARCHAR2(512);
269 synch_disabled EXCEPTION;
270 password_expired EXCEPTION;
271 l_pub_item_tbl asg_base.pub_item_tbl_type;
272 l_bool_ret BOOLEAN;
273 l_ret_msg varchar2(512);
274 l_pwd_expired VARCHAR2(1);
275 l_is_auto_sync VARCHAR2(1);
276 l_is_download_only_sync VARCHAR2(1);
277 l_sync_id NUMBER;
278 BEGIN
279 IF(asg_helper.check_is_log_enabled(g_stmt_level))
280 THEN
281 asg_helper.log('Start Download ' || p_clientid ||
282 ' last tranid: ' || p_last_tranid ||
283 ' current tranid: ' || p_curr_tranid,
284 'asg_cons_qpkg',g_stmt_level);
285 END IF;
286 BEGIN
287 l_upload_tranid := asg_base.get_upload_tranid();
288
289 -- Set Sync Id only if sync is NORMAL
290 -- or not a download only sync
291 l_is_auto_sync := asg_base.is_auto_sync();
292 l_is_download_only_sync := asg_base.is_download_only_sync(p_clientid, p_curr_tranid);
293
294 asg_helper.log('Auto Sync: ' || l_is_auto_sync || ' and Download only sync: ' || l_is_download_only_sync
295 ,'asg_cons_qpkg',g_stmt_level);
296
297 IF (l_is_auto_sync = 'N'
298 OR l_is_download_only_sync = 'N') THEN
299 l_sync_id:=set_sync_id(p_clientid, l_upload_tranid);
300 END IF;
301
302 -- This is relevant only for first synch
303 -- Raise error if password has expired.
304 -- Subsequent synchs raise error during authentication itself.
305 SELECT nvl(password_expired, 'N') into l_pwd_expired
306 FROM asg_user
307 WHERE user_name = p_clientid;
308 IF l_pwd_expired = 'Y' THEN
309 raise password_expired;
310 END IF;
311 -- Check if user's synch is disabled
312 IF asg_helper.is_user_synch_enabled(p_clientid,l_disabled_synch_message)
313 = FND_API.G_FALSE THEN
314 raise synch_disabled;
315 END IF;
316 -- Initialize all the session information except the list of pubitems
317 -- to be downloaded
318 g_first_synch := asg_download.isFirstSync();
319 g_auto_synch := asg_base.is_auto_sync();
320 l_last_synch_date := find_last_synch_date(p_clientid,
321 p_last_tranid);
322 g_device_type := find_device_type(p_clientid);
323 asg_base.init(p_clientid, p_last_tranid, p_curr_tranid,
324 l_last_synch_date, asg_base.g_empty_pub_item_tbl);
325
326 -- check out if this user needs complete
327 -- refresh since he's been a dormant user.
328 asg_helper.log('Checking if user: '
329 || p_clientid || ' needs complete refresh.',
330 'asg_cons_qpkg',g_stmt_level);
331 process_purge_Sdq(p_clientid, p_last_tranid, p_curr_tranid);
332
333 IF(g_first_synch) THEN
334 set_synch_completed(p_clientid);
335 delete_row(p_clientid);
336 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
337 asg_helper.log('First synch..deleted all rows from asg_complete_refresh'
338 ,'asg_cons_qpkg',g_stmt_level);
339 END IF;
340 ELSE
341 process_compref_table(p_clientid,p_last_tranid);
342 set_complete_refresh;
343 END IF;
344
345 -- Check if customization is disabled
346 process_custom_pub_items(p_clientid);
347
348 l_bool_ret := asg_download.processsdq(p_clientid, p_last_tranid,
349 p_curr_tranid, p_high_prty,
350 l_ret_msg);
351 IF l_bool_ret = FALSE THEN
352 if l_ret_msg is null
353 then
354 l_sqlerror_message := SQLERRM;
355 else
359 THEN
356 l_sqlerror_message := l_ret_msg;
357 end if;
358 IF(asg_helper.check_is_log_enabled(g_err_level))
360 asg_helper.log('processsdq returned FALSE', 'asg_cons_qpkg',
361 g_err_level);
362 END IF;
363 asg_helper.set_synch_errmsg(p_clientid, null, g_device_type,'Error during download in asg_download.processsdq: '||
364 l_sqlerror_message);
365
366 RAISE_APPLICATION_ERROR(-20999, 'Error during download in ' ||
367 'asg_download.processsdq ' ||
368 l_sqlerror_message);
369 END IF;
370
371 -- Check for conflicts
372 process_conflicts(p_clientid);
373
374 --12.1
375 -- Start processing uploads when synchronized manually(i.e,not auto sync)
376 -- and when download is successful
377 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
378 asg_helper.log('Set ASG_USER hwm_tranid to '||l_sync_id||' for Process Upload to pick it up.','asg_cons_qpkg',g_stmt_level);
379 END IF;
380 asg_helper.set_synch_errmsg(p_clientid,l_sync_id,g_device_type, null);
381 EXCEPTION
382
383 WHEN synch_disabled THEN
384 asg_helper.set_synch_errmsg(
385 p_clientid,null,g_device_type,
386 'User Synch Error: '||
387 nvl(l_disabled_synch_message,'Synch is not enabled.'));
388
389 IF(asg_helper.check_is_log_enabled(g_err_level)) THEN
390 asg_helper.log('User Synch Error: ' || p_clientid || ' ' ||
391 to_char(sysdate, 'yyyy-mm-dd') ||
392 ' Synch is not enabled.',
393 'asg_cons_qpkg',g_err_level);
394 END IF;
395 IF l_disabled_synch_message IS NULL THEN
396 l_disabled_synch_message := 'Synch is not enabled.';
397 END IF;
398 RAISE_APPLICATION_ERROR(-20994, l_disabled_synch_message);
399 WHEN password_expired THEN
400 asg_helper.set_synch_errmsg(
401 p_clientid,null,g_device_type,
402 'User Synch Error: User Password Expired.');
403 IF(asg_helper.check_is_log_enabled(g_err_level)) THEN
404 asg_helper.log('User Synch Error: ' || p_clientid || ' ' ||
405 to_char(sysdate, 'yyyy-mm-dd') ||
406 ' User Password Expired.',
407 'asg_cons_qpkg',g_err_level);
408 END IF;
409 RAISE_APPLICATION_ERROR(-20993, 'Your password has expired. ' ||
410 'Please contact your System Administrator to reset the password.');
411 WHEN OTHERS THEN
412 l_sqlerror_message := SQLERRM;
413 asg_helper.set_synch_errmsg(
414 p_clientid,null,g_device_type,
415 'User Synch Error: Exception in processsdq '||
416 l_sqlerror_message);
417 IF(asg_helper.check_is_log_enabled(g_err_level))
418 THEN
419 asg_helper.log('User Synch Error: ' ||p_clientid || ' ' ||
420 to_char(sysdate, 'yyyy-mm-dd') ||
421 ' transaction-id: ' || p_curr_tranid ||
422 ' Exception in processsdq ' || l_sqlerror_message,
423 'asg_cons_qpkg',g_err_level);
424 END IF;
425 RAISE_APPLICATION_ERROR(-20998, 'Exception during download ' ||
426 l_sqlerror_message);
427 END;
428
429 -- The final pub items list should be in l_pub_item_tbl
430 get_pubitem_list(l_pub_item_tbl);
431
432 -- Store the list of pubitems to be refreshed
433 asg_base.set_pub_items(l_pub_item_tbl);
434
435 -- Write to log all the session information for this user
436 asg_base.print_all_globals();
437 asg_helper.log('download_init', 'asg_consq_pkg',g_err_level);
438 END download_init;
439
440 -- Notifies when all the client's data is sent
441 PROCEDURE download_complete(p_clientid IN VARCHAR2)
442 IS
443 l_bool_ret BOOLEAN;
444 l_last_tranid NUMBER;
445 l_sqlerror_message VARCHAR2(512);
446 BEGIN
447 IF(asg_helper.check_is_log_enabled(g_stmt_level))
448 THEN
449 asg_helper.log('Download complete called for client: ' || p_clientid,
450 'asg_cons_qpkg',g_stmt_level);
451 END IF;
452 --new code
453 set_synch_completed(p_clientid);
454
455 BEGIN
456 l_bool_ret := asg_download.purgesdq();
457 IF l_bool_ret = FALSE THEN
458 l_sqlerror_message := SQLERRM;
459 IF(asg_helper.check_is_log_enabled(g_err_level))
460 THEN
461 asg_helper.log('purgesdq returned FALSE',
462 'asg_cons_qpkg',g_err_level);
463 END IF;
464 RAISE_APPLICATION_ERROR(-20997, 'Error during download in ' ||
465 'asg_download.purgesdq ' || l_sqlerror_message);
466 END IF;
467
468 -- Update the synctime end in asg_user table
469 l_last_tranid := asg_base.get_last_tranid();
470 IF ((l_last_tranid <= -1) OR
471 (g_first_synch = TRUE)) THEN
472 UPDATE asg_user
473 SET last_tranid = l_last_tranid,
474 last_synch_date_end = sysdate,
475 prior_synch_date_end = null
476 WHERE user_name = p_clientid;
477 ELSE
478 IF(g_last_synch_successful = FND_API.G_TRUE) THEN
479 UPDATE asg_user
480 SET last_tranid = l_last_tranid,
481 prior_synch_date_end = asg_base.get_last_synch_date(),
482 last_synch_date_end = sysdate
483 WHERE user_name = p_clientid;
484 ELSE
485 UPDATE asg_user
486 SET last_tranid = l_last_tranid,
487 last_synch_date_end = sysdate
488 WHERE user_name = p_clientid;
489 END IF;
490 END IF;
494 IF(asg_helper.check_is_log_enabled(g_stmt_level))
491 -- Reset all session information
492 asg_base.reset_all_globals();
493
495 THEN
496 asg_helper.log('Download complete DONE for client: ' || p_clientid,
497 'asg_cons_qpkg',g_stmt_level);
498 END IF;
499 EXCEPTION
500 WHEN OTHERS THEN
501 l_sqlerror_message := SQLERRM;
502 asg_helper.set_synch_errmsg(
503 p_clientid,null,g_device_type,
504 'User Synch Error: Exception in purgesdq '||
505 l_sqlerror_message);
506
507 IF(asg_helper.check_is_log_enabled(g_err_level))
508 THEN
509 asg_helper.log('User Synch Error: ' ||p_clientid || ' ' ||
510 to_char(sysdate, 'yyyy-mm-dd') ||
511 ' transaction-id: ' || asg_base.get_current_tranid ||
512 ' Exception in purgesdq ' || l_sqlerror_message,
513 'asg_cons_qpkg',g_err_level);
514 END IF;
515 -- Reset all session information
516 asg_base.reset_all_globals();
517 RAISE_APPLICATION_ERROR(-20996, 'Exception during download ' ||
518 l_sqlerror_message);
519 END ;
520 /**/
521 EXCEPTION
522 WHEN OTHERS THEN
523 l_sqlerror_message := SQLERRM;
524 asg_helper.set_synch_errmsg(
525 p_clientid,null,g_device_type,
526 'User Synch Error: Exception in download_complete '||
527 l_sqlerror_message);
528
529 IF(asg_helper.check_is_log_enabled(g_err_level))
530 THEN
531 asg_helper.log('User Synch Error: ' ||p_clientid || ' ' ||
532 to_char(sysdate, 'yyyy-mm-dd') ||
533 ' transaction-id: ' || asg_base.get_current_tranid ||
534 ' Exception in download_complete ' || l_sqlerror_message,
535 'asg_cons_qpkg',g_err_level);
536 END IF;
537 -- Reset all session information
538 asg_base.reset_all_globals();
539 RAISE_APPLICATION_ERROR(-20996, 'Exception during download ' ||
540 l_sqlerror_message);
541 END download_complete;
542
543 --PROCEDURE FOR FINDING WHETHER FIRST SYNCH OR NOT
544
545 PROCEDURE is_first_synch(p_is_first_synch OUT NOCOPY VARCHAR2)
546 IS
547 l_rec_count1 NUMBER;
548 l_rec_count2 NUMBER;
549 l_qry_string1 VARCHAR2(512);
550 BEGIN
551 l_qry_string1:='select count(*) from '||asg_base.G_OLITE_SCHEMA
552 ||'.c$pub_list_q';
553 EXECUTE IMMEDIATE l_qry_string1 into l_rec_count1;
554 l_qry_string1:='select count(*) from '||asg_base.G_OLITE_SCHEMA
555 ||'.c$pub_list_q where comp_ref=''Y''';
556 EXECUTE IMMEDIATE l_qry_string1 into l_rec_count2;
557 IF(l_rec_count1 = l_rec_count2) THEN
558 p_is_first_synch:='Y';
559 ELSE
560 p_is_first_synch:='N';
561 END IF;
562 END is_first_synch;
563
564
565 --PROCEDURE FOR PERFORMING BATCH UPDATES ON MOBILEADMIN.C$PUB_LIST_Q
566
567 PROCEDURE update_rec_count(p_pubitem_tbl IN asg_base.pub_item_tbl_type,
568 p_clientid IN VARCHAR2)
569 IS
570 l_loopvar NUMBER;
571 l_qry_string VARCHAR2(1024);
572 BEGIN
573 IF(asg_helper.check_is_log_enabled(g_stmt_level))
574 THEN
575 asg_helper.log('Performing batch update for: '||p_clientid,
576 'asg_cons_qpkg',g_stmt_level);
577 END IF;
578 FOR l_loopvar IN 1..p_pubitem_tbl.count
579 LOOP
580 IF(p_pubitem_tbl(l_loopvar).rec_count is not null OR
581 p_pubitem_tbl(l_loopvar).name not like 'C$%')
582 THEN
583 l_qry_string:='update '||asg_base.G_OLITE_SCHEMA||'.c$pub_list_q '||
584 ' set rec_count= :1 ' ||
585 ' where name = :2';
586 EXECUTE IMMEDIATE l_qry_string
587 USING p_pubitem_tbl(l_loopvar).rec_count, p_pubitem_tbl(l_loopvar).name;
588 /* asg_helper.log('Update: '||p_pubitem_tbl(l_loopvar).name||' count: '
589 ||p_pubitem_tbl(l_loopvar).rec_count,
590 'asg.asg_cons_qpkg');
591 */
592 END IF;
593 END LOOP;
594 END update_rec_count;
595
596
597 --PROCEDURE FOR POPULATING rec_count MOBILEADMIN.C$PUB_LIST_Q FOR FIRST TIME SYNCH
598
599 PROCEDURE process_first_synch(p_pubitem_tbl IN asg_base.pub_item_tbl_type,
600 p_clientid IN VARCHAR2)
601 IS
602 l_curr_pubitem VARCHAR2(128);
603 l_loopvar NUMBER;
604 l_view_name VARCHAR2(128);
605 l_owner_name VARCHAR2(128);
606 l_total NUMBER;
607 l_rec_count1 NUMBER;
608 l_qry_string1 VARCHAR2(1024);
609 l_qry_string2 VARCHAR2(1024);
610 l_pubitem_tbl asg_base.pub_item_tbl_type;
611
612 BEGIN
613 l_total:=0;
614 l_pubitem_tbl := p_pubitem_tbl;
615 IF(asg_helper.check_is_log_enabled(g_stmt_level))
616 THEN
617 asg_helper.log('Inside process_first_synch for :'||p_clientid,
618 'asg_cons_qpkg',g_stmt_level);
619 END IF;
620 asg_base.init(p_clientid,-1,0,null,p_pubitem_tbl);
621
622 FOR l_loopvar in 1..l_pubitem_tbl.count
623 LOOP
624 l_curr_pubitem:=l_pubitem_tbl(l_loopvar).name;
625 IF(l_curr_pubitem like 'C$%')
626 THEN
627 IF(asg_helper.check_is_log_enabled(g_stmt_level))
628 THEN
629 asg_helper.log('Ignoring : '||l_curr_pubitem,
630 'asg_cons_qpkg',g_stmt_level);
631 END IF;
632 ELSE
633 select base_object_name,base_owner into l_view_name,l_owner_name
634 from asg_pub_item where item_id=l_curr_pubitem;
635 l_curr_pubitem:=l_pubitem_tbl(l_loopvar).name;
636 l_qry_string2:='select count(*) from '||l_owner_name||'.'||l_view_name;
640 END IF;
637 EXECUTE IMMEDIATE l_qry_string2 into l_rec_count1;
638 l_total:=l_total+l_rec_count1;
639 l_pubitem_tbl(l_loopvar).rec_count:=l_rec_count1;
641 /* asg_helper.log('Pub Name '||l_curr_pubitem||' Count: '||l_rec_count1,
642 'asg.asg_cons_qpkg');
643 */
644 END LOOP;
645 update_rec_count(l_pubitem_tbl,p_clientid);
646 IF(asg_helper.check_is_log_enabled(g_stmt_level))
647 THEN
648 asg_helper.log('Total num of rows(First time synch): '||l_total,
649 'asg_cons_qpkg',g_stmt_level);
650 END IF;
651 END process_first_synch;
652
653 -- Populates the number of records for each publication item downloaded
654
655 PROCEDURE populate_q_rec_count(p_clientid IN VARCHAR2)
656 IS
657 BEGIN
658 null;
659 END populate_q_rec_count;
660
661
662 --routine that sets PI's in c$pub_list_q that have
663 --synch_completed (in asg_complete_refresh) set to 'N' for complete refresh
664 PROCEDURE set_complete_refresh
665 IS
666 l_user_name VARCHAR2(100);
667 l_qry_string VARCHAR2(1024);
668 BEGIN
669 l_user_name := asg_base.get_user_name;
670 l_qry_string := 'update '||asg_base.G_OLITE_SCHEMA||'.c$pub_list_q '
671 ||'set comp_ref = ''Y'' '||' where name IN ('
672 ||'SELECT publication_item FROM asg_complete_refresh '
673 ||' WHERE synch_completed=''N'' AND user_name = :1 '
674 ||' AND publication_item IN '
675 ||' ( SELECT name FROM '||asg_base.G_OLITE_SCHEMA
676 ||'.c$pub_list_q ))' ;
677 EXECUTE IMMEDIATE l_qry_string
678 USING l_user_name;
679 END set_complete_refresh;
680
681
682
683 --sets synch_completed flag in asg_complete_refresh to 'Y' for
684 --a particular pub_item and user_name
685 PROCEDURE set_synch_completed(p_user_name VARCHAR2,p_pub_item VARCHAR2)
686 IS
687 BEGIN
688 UPDATE asg_complete_refresh
689 SET synch_completed='Y' , last_update_date = sysdate
690 WHERE user_name=p_user_name
691 AND publication_item=p_pub_item;
692 END set_synch_completed;
693
694 --sets synch_completed flag in asg_complete_refresh to 'Y' for
695 --pub_item for a given user_name
696 PROCEDURE set_synch_completed(p_user_name VARCHAR2)
697 IS
698 l_qry_string VARCHAR2(1024);
699 BEGIN
700 l_qry_string := ' UPDATE asg_complete_refresh SET ' ||
701 ' synch_completed=''Y'',last_update_date=sysdate ' ||
702 ' WHERE user_name= :1 ' ||
703 ' AND ' ||
704 ' publication_item IN ' ||
705 ' (SELECT name FROM '||asg_base.G_OLITE_SCHEMA||
706 '.c$pub_list_q)';
707 EXECUTE IMMEDIATE l_qry_string
708 USING p_user_name;
709
710 END set_synch_completed;
711
712 --removes the row corresponding to a user_name and pub_item
713 --from asg_complete_refresh
714 PROCEDURE delete_row(p_user_name VARCHAR2,p_pub_item VARCHAR2)
715 IS
716 BEGIN
717 DELETE FROM asg_complete_refresh
718 WHERE user_name = p_user_name AND
719 publication_item = p_pub_item;
720 END delete_row;
721
722 --removes all rows for user_name from asg_complete_refresh
723 --for the current publication items with synch_completed='Y'.
724 PROCEDURE delete_row(p_user_name VARCHAR2)
725 IS
726 l_qry_string VARCHAR2(1024);
727 BEGIN
728 l_qry_string:= ' DELETE FROM asg_complete_refresh '||
729 ' WHERE user_name = :1 ' ||
730 ' AND synch_completed = ''Y'' AND '||
731 ' publication_item IN ' ||
732 '(SELECT name FROM '||asg_base.G_OLITE_SCHEMA||
733 '.c$pub_list_q)';
734 EXECUTE IMMEDIATE l_qry_string
735 USING p_user_name;
736 END delete_row;
737
738
739
740 --ROUTINE FOR REMOVING RECORDS FROM asg_complete_refresh
741 -- if the previous synch was successful
742 PROCEDURE process_compref_table(p_user_name VARCHAR2,p_last_tranid NUMBER)
743 IS
744 l_tranid NUMBER;
745 l_str VARCHAR2(1024);
746 l_ret varchar2(2);
747 BEGIN
748 l_ret := is_previous_synch_successful(p_user_name,p_last_tranid);
749 IF( l_ret = FND_API.G_TRUE ) THEN
750 --previous synch was successful
751 IF(asg_helper.check_is_log_enabled(g_stmt_level))
752 THEN
753 asg_helper.log('Prev synch successful ',
754 'asg_cons_qpkg',g_stmt_level);
755 END IF;
756 delete_row(p_user_name);
757 ELSE
758 --previous synch was not successful
759 --so set all PI's of the current user to complete_synch
760 l_str:= 'UPDATE asg_complete_refresh SET synch_completed=''N'' , '
761 ||' last_update_date = sysdate WHERE user_name = :1 '
762 ||' AND publication_item in '
763 ||'(SELECT name FROM '||asg_base.G_OLITE_SCHEMA
764 ||'.c$pub_list_q)';
765 EXECUTE IMMEDIATE l_str
766 USING p_user_name;
767 END IF;
768
769 END process_compref_table;
770
771 -- Routine for removing records from c$pub_list_q
772 -- If customization is disabled
773 PROCEDURE process_custom_pub_items (p_user_name IN VARCHAR2)
774 IS
775 l_customProfValue VARCHAR2(2);
776 l_dml VARCHAR2(512);
777 l_user_id NUMBER;
778 BEGIN
779 -- If custom publication item download is disabled
780 -- then remove all custom entries from c$pub_list_q
781 l_user_id := asg_base.get_user_id(p_user_name);
782 l_customProfValue := fnd_profile.VALUE_SPECIFIC(
783 name => 'ASG_DISABLE_CUSTOM_SYNCH',
784 user_id => l_user_id,
788 IF (l_customProfValue = 'Y') THEN
785 responsibility_id => null,
786 application_id => 689);
787
789 IF(asg_helper.check_is_log_enabled(g_stmt_level))
790 THEN
791 asg_helper.log('Disabling download of custom pub items ',
792 'asg_cons_qpkg', g_stmt_level);
793 END IF;
794 l_dml := 'DELETE FROM '||asg_base.G_OLITE_SCHEMA||'.c$pub_list_q ' ||
795 ' WHERE name IN ' ||
796 ' (select a.name from asg_pub_item a,asg_pub b' ||
797 ' where a.pub_name=b.name and b.custom=''Y'')';
798 EXECUTE IMMEDIATE l_dml;
799 END IF;
800
801 END process_custom_pub_items;
802
803
804 -- Routine for processing conflicts
805 PROCEDURE process_conflicts(p_user_name IN VARCHAR2)
806 IS
807 l_upload_tranid NUMBER;
808 l_detect_conflict VARCHAR2(1);
809 l_pubitem VARCHAR2(30);
810 l_pubitem_tbl asg_base.pub_item_tbl_type;
811 l_counter NUMBER;
812 BEGIN
813
814 l_upload_tranid := asg_base.get_upload_tranid();
815
816 -- Check if conflict detection is needed other wise return
817 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
818 asg_helper.log('Checking if conflicts should be detected.',
819 'asg_cons_qpkg',g_stmt_level);
820 END IF;
821 is_conflict_detection_needed (p_user_name,
822 l_upload_tranid,
823 l_detect_conflict,
824 l_pubitem_tbl);
825
826 IF (l_detect_conflict = FND_API.G_FALSE) THEN
827 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
828 asg_helper.log('No need to check for conflicts.',
829 'asg_cons_qpkg',g_stmt_level);
830 END IF;
831 return;
832 END IF;
833
834 -- Ok, conflicts need to be detected, process one pubitem at a time
835 FOR curr_index in 1..l_pubitem_tbl.count LOOP
836 l_pubitem := l_pubitem_tbl(curr_index).name;
837 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
838 asg_helper.log('Processing ' ||l_pubitem || ' for conflicts.',
839 'asg_cons_qpkg',g_stmt_level);
840 END IF;
841 process_pubitem_conflicts(p_user_name, l_upload_tranid, l_pubitem);
842 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
843 asg_helper.log('Done processing ' ||l_pubitem || ' for conflicts.',
844 'asg_cons_qpkg',g_stmt_level);
845 END IF;
846 END LOOP;
847
848 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
849 asg_helper.log('Done processing all conflicts.',
850 'asg_cons_qpkg',g_stmt_level);
851 END IF;
852 EXCEPTION
853 WHEN OTHERS THEN
854 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
855 asg_helper.log('Exception when processing for conflicts. ' || sqlerrm,
856 'asg_cons_qpkg',g_stmt_level);
857 END IF;
858 END process_conflicts;
859
860 -- Routine to determine if conflicts should be detected
861 PROCEDURE is_conflict_detection_needed(
862 p_user_name IN VARCHAR2,
863 p_upload_tranid IN NUMBER,
864 p_detect_conflict IN OUT NOCOPY VARCHAR2,
865 p_pubitem_tbl IN OUT NOCOPY asg_base.pub_item_tbl_type)
866 IS
867 l_conf_pis_exist VARCHAR2(1);
868 l_query_string VARCHAR2(512);
869 l_query_string2 VARCHAR2(512);
870 l_counter PLS_INTEGER;
871 l_cursor_id NUMBER;
872 l_cursor_id2 NUMBER;
873 l_cursor_ret NUMBER;
874 l_cursor_ret2 NUMBER;
875 l_pub_name VARCHAR2(30);
876 l_pub_callback VARCHAR2(100);
877 l_pub_detect_conflict VARCHAR2(1);
878 l_conf_pubs VARCHAR2(2000) := NULL;
879 BEGIN
880
881 p_detect_conflict := FND_API.G_FALSE;
882 l_conf_pis_exist := conflict_pub_items_exist(p_user_name,
883 p_upload_tranid);
884 IF (l_conf_pis_exist = asg_base.G_NO) THEN
885 return;
886 END IF;
887
888 -- Ok, some pub items are uploaded which need conflict detection
889 -- Call the publication level wrapper.
890 /* l_query_string := 'SELECT distinct api.pub_name ' ||
891 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ci, ' ||
892 ' asg_pub_item api ' ||
893 'WHERE ci.clid$$cs = ''' || p_user_name || ''' AND ' ||
894 ' ci.tranid$$ = ' || p_upload_tranid || ' AND ' ||
895 ' ci.store = api.name';*/
896 l_query_string := 'SELECT distinct api.pub_name ' ||
897 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ci, ' ||
898 ' asg_pub_item api ' ||
899 'WHERE ci.clid$$cs = :1 AND ' ||
900 ' ci.tranid$$ = :2 AND ' ||
901 ' ci.store = api.name';
902
903 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
904 DBMS_SQL.PARSE (l_cursor_id, l_query_string, DBMS_SQL.v7);
905 DBMS_SQL.bind_variable(l_cursor_id,':1',p_user_name);
906 DBMS_SQL.bind_variable(l_cursor_id,':2',p_upload_tranid);
907 DBMS_SQL.DEFINE_COLUMN (l_cursor_id, 1, l_pub_name, 30);
908
909 l_cursor_ret := DBMS_SQL.EXECUTE (l_cursor_id);
910 l_counter := 1;
911
912 -- Go through all the publications whose pis were uploaded
913 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id) > 0 ) LOOP
914 DBMS_SQL.COLUMN_VALUE (l_cursor_id, 1, l_pub_name);
915
916 SELECT wrapper_name into l_pub_callback
920 -- Find the callback return value
917 FROM asg_pub
918 WHERE name = l_pub_name;
919
921 /*
922 l_query_string2 :=
923 'begin ' ||
924 ' :1 := ' || l_pub_callback || '.detect_conflict(''' ||
925 p_user_name || '''); ' ||
926 ' end;';
927 */
928 l_query_string2 := 'SELECT ' || l_pub_callback ||
929 '.detect_conflict( :1 ) from dual';
930
931 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
932 asg_helper.log('SQL Command: ' || replace(l_query_string2,'''',''''''),
933 'asg_cons_qpkg',g_stmt_level);
934 END IF;
935 BEGIN
936 /*
937 l_cursor_id2 := DBMS_SQL.OPEN_CuRSOR();
938 DBMS_SQL.PARSE(l_cursor_id2, l_query_string2, DBMS_SQL.v7);
939 DBMS_SQL.DEFINE_COLUMN(l_cursor_id2, 1, l_pub_detect_conflict, 1);
940 l_cursor_ret2 := DBMS_SQL.EXECUTE(l_cursor_id2);
941 DBMS_SQL.COLUMN_VALUE(l_cursor_id2, 1, l_pub_detect_conflict);
942 DBMS_SQL.CLOSE_CURSOR(l_cursor_id2);
943 */
944 EXECUTE IMMEDIATE l_query_string2
945 INTO l_pub_detect_conflict
946 USING p_user_name;
947
948 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
949 asg_helper.log('Publication callback returned: ' ||
950 l_pub_detect_conflict,
951 'asg_cons_qpkg',g_stmt_level);
952 END IF;
953 IF (l_pub_detect_conflict = asg_base.G_YES) THEN
954 -- Conflicts should be detected for this publication
955 -- Build a comma separated list for use later.
956 IF (l_conf_pubs IS NULL) THEN
957 l_conf_pubs := '''' || l_pub_name || '''';
958 ELSE
959 l_conf_pubs := l_conf_pubs || ',''' || l_pub_name || '''';
960 END IF;
961 l_counter := l_counter +1;
962 END IF;
963 EXCEPTION
964 WHEN OTHERS THEN
965 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
966 asg_helper.log('Exception in wrapper callback ' ||SQLERRM,
967 'asg_cons_qpkg',g_stmt_level);
968 END IF;
969 END;
970 END LOOP;
971 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
972
973 -- Some pubs need conflict detection
974 IF (l_counter > 1) THEN
975 p_detect_conflict := FND_API.G_TRUE;
976 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
977 asg_helper.log('Need to detect conflicts.',
978 'asg_cons_qpkg',g_stmt_level);
979 END IF;
980 get_conf_pub_items_list(p_user_name, p_upload_tranid,
981 l_conf_pubs, p_pubitem_tbl);
982 END IF;
983
984 END is_conflict_detection_needed;
985
986 FUNCTION conflict_pub_items_exist(p_user_name IN VARCHAR2,
987 p_upload_tran_id IN NUMBER)
988 RETURN VARCHAR2
989 IS
990 l_query_string VARCHAR2(512);
991 l_conf_pi_count NUMBER;
992 l_conf_pis_exist VARCHAR2(1) := asg_base.G_NO;
993 BEGIN
994 -- As an optimization, first check the inq to see if there are
995 -- any pub items uploaded that have detect_conflict set to yes.
996 -- and will not be complete refreshed
997 l_query_string := 'SELECT count(*) ' ||
998 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ci, ' ||
999 asg_base.G_OLITE_SCHEMA || '.c$pub_list_q cpq, ' ||
1000 ' asg_pub_item api ' ||
1001 'WHERE ci.clid$$cs = :1 AND ' ||
1002 ' ci.tranid$$ = :2 AND ' ||
1003 ' ci.store = api.name AND ' ||
1004 ' ci.store = cpq.name AND ' ||
1005 ' cpq.comp_ref <> ''Y'' AND ' ||
1006 ' api.detect_conflict = ''Y''';
1007
1008 IF(asg_helper.check_is_log_enabled(g_stmt_level))
1009 THEN
1010 asg_helper.log('SQL Command: ' || replace(l_query_string,'''',''''''),
1011 'asg_cons_qpkg',g_stmt_level);
1012 END IF;
1013
1014 EXECUTE IMMEDIATE l_query_string
1015 INTO l_conf_pi_count
1016 USING p_user_name, p_upload_tran_id;
1017 IF (l_conf_pi_count > 0) THEN
1018 l_conf_pis_exist := asg_base.G_YES;
1019 END IF;
1020 return l_conf_pis_exist;
1021
1022 END conflict_pub_items_exist;
1023
1024 PROCEDURE get_conf_pub_items_list(
1025 p_user_name IN VARCHAR2,
1026 p_upload_tranid IN NUMBER,
1027 l_conf_pubs IN VARCHAR2,
1028 p_pubitem_tbl IN OUT NOCOPY asg_base.pub_item_tbl_type)
1029 IS
1030 l_query_string VARCHAR2(4000);
1031 l_pubitem VARCHAR2(30);
1032 l_cursor_id NUMBER;
1033 l_cursor_ret NUMBER;
1034 l_counter NUMBER;
1035 BEGIN
1036 /* l_query_string := 'SELECT ci.store ' ||
1037 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ci , ' ||
1038 asg_base.G_OLITE_SCHEMA || '.c$pub_list_q cpq, ' ||
1039 ' asg_pub_item api ' ||
1040 'WHERE ci.clid$$cs = ''' || p_user_name || ''' AND ' ||
1041 ' ci.tranid$$ = ' || p_upload_tranid || ' AND ' ||
1042 ' ci.store = api.name AND ' ||
1043 ' ci.store = cpq.name AND ' ||
1044 ' cpq.comp_ref <> ''Y'' AND ' ||
1045 ' api.detect_conflict = ''Y'' AND ' ||
1046 ' api.pub_name in (' || l_conf_pubs || ')';*/
1047 l_query_string := 'SELECT ci.store ' ||
1051 'WHERE ci.clid$$cs = :1 AND ' ||
1048 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ci , ' ||
1049 asg_base.G_OLITE_SCHEMA || '.c$pub_list_q cpq, ' ||
1050 ' asg_pub_item api ' ||
1052 ' ci.tranid$$ = :2 AND ' ||
1053 ' ci.store = api.name AND ' ||
1054 ' ci.store = cpq.name AND ' ||
1055 ' cpq.comp_ref <> ''Y'' AND ' ||
1056 ' api.detect_conflict = ''Y'' AND ' ||
1057 ' api.pub_name in (' || l_conf_pubs || ')';
1058
1059 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1060 DBMS_SQL.PARSE (l_cursor_id, l_query_string, DBMS_SQL.v7);
1061 DBMS_SQL.bind_variable(l_cursor_id,':1',p_user_name);
1062 DBMS_SQL.bind_variable(l_cursor_id,':2',p_upload_tranid);
1063 DBMS_SQL.DEFINE_COLUMN (l_cursor_id, 1, l_pubitem, 30);
1064
1065 l_cursor_ret := DBMS_SQL.EXECUTE (l_cursor_id);
1066 l_counter := 1;
1067
1068 -- Go through all the publications whose pis were uploaded
1069 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id) > 0 ) LOOP
1070 DBMS_SQL.COLUMN_VALUE (l_cursor_id, 1, l_pubitem);
1071 p_pubitem_tbl(l_counter).name := l_pubitem;
1072 l_counter := l_counter +1;
1073 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
1074 asg_helper.log('Pub item that will be checked for conflicts: ' ||
1075 l_pubitem, 'asg_cons_qpkg',g_stmt_level);
1076 END IF;
1077 END LOOP;
1078
1079 END get_conf_pub_items_list;
1080
1081
1082 --procedure for processing asg_purge_sdq at synch time
1083 PROCEDURE process_purge_Sdq ( p_clientid IN VARCHAR2,
1084 p_last_tranid IN NUMBER,
1085 p_curr_tranid IN NUMBER)
1086 IS
1087 CURSOR c_purgeSdq(c_username varchar2,c_pub_name varchar2) is
1088 SELECT NVL(transaction_id,-100)
1089 FROM asg_purge_sdq
1090 WHERE user_name = c_username AND pub_name = c_pub_name;
1091 l_tran_id NUMBER;
1092 l_dml VARCHAR2(255);
1093 l_qry_string varchar2(4000);
1094 l_qry_string1 varchar2(4000);
1095 l_cursor_id NUMBER;
1096 l_pub_name varchar2(30);
1097 l_ret_val NUMBER;
1098 BEGIN
1099 asg_helper.log('Processing Purge SDQ','asg_cons_qpkg',g_stmt_level);
1100
1101 l_qry_string := 'select distinct pub_name from asg_pub_item where item_id in '||
1102 ' ( select name from '||asg_base.G_OLITE_SCHEMA||'.c$pub_list_q )';
1103 l_cursor_id := dbms_sql.open_cursor();
1104 DBMS_SQL.parse(l_cursor_id,l_qry_string,DBMS_SQL.v7);
1105 DBMS_SQL.define_column(l_cursor_id,1,l_pub_name,30);
1106 l_ret_val := DBMS_SQL.execute(l_cursor_id);
1107
1108 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id) > 0 )
1109 LOOP
1110 DBMS_SQL.COLUMN_VALUE (l_cursor_id, 1, l_pub_name);
1111
1112 l_tran_id := -200;
1113
1114 OPEN c_purgeSdq(p_clientid,l_pub_name);
1115 FETCH c_purgeSdq INTO l_tran_id;
1116
1117 IF(c_purgeSDq%FOUND)
1118 THEN
1119 IF(l_tran_id = -100 )
1120 THEN
1121 -- process first time .set publication to complete refresh
1122 asg_helper.log('Setting user '||p_clientid||' to complete refresh for '||
1123 ' publication '||l_pub_name,'asg_cons_qpkg',g_stmt_level);
1124 l_qry_string1 := 'update '||asg_base.G_OLITE_SCHEMA||'.c$pub_list_q '||
1125 ' set comp_ref = ''Y'' where name in '||
1126 ' ( select item_id from asg_pub_item where '||
1127 ' pub_name = :1 )';
1128 EXECUTE IMMEDIATE l_qry_string1
1129 USING l_pub_name;
1130 UPDATE asg_purge_sdq
1131 SET transaction_id=p_curr_tranid,last_update_date=sysdate
1132 WHERE user_name=p_clientid AND pub_name = l_pub_name;
1133 ELSIF ( p_last_tranid < l_tran_id )
1134 THEN
1135 -- last synch failed. ..so again set to complete refresh .
1136 asg_helper.log('Re-setting user '||p_clientid||' to complete refresh for '||
1137 ' publication '||l_pub_name,'asg_cons_qpkg',g_stmt_level);
1138 l_qry_string1 := 'update '||asg_base.G_OLITE_SCHEMA||'.c$pub_list_q '||
1139 ' set comp_ref = ''Y'' where name in '||
1140 ' ( select item_id from asg_pub_item where '||
1141 ' pub_name = :1 )';
1142 EXECUTE IMMEDIATE l_qry_string1
1143 USING l_pub_name;
1144 UPDATE asg_purge_sdq
1145 SET transaction_id=p_curr_tranid,last_update_date=sysdate
1146 WHERE user_name=p_clientid AND pub_name = l_pub_name;
1147 ELSE
1148 --previous synch succeded ..so delete from purge_sdq
1149 asg_helper.log('Deleting asg_purge_sdq for user '||p_clientid||' and '||
1150 ' publication '||l_pub_name,'asg_cons_qpkg',g_stmt_level);
1151 DELETE FROM asg_purge_sdq
1152 WHERE user_name = p_clientid AND pub_name = l_pub_name;
1153 END IF;
1154 ELSE
1155 null;
1156 END if;
1157 CLOSE c_purgeSdq;
1158 END LOOP;
1159
1160 dbms_sql.close_cursor(l_cursor_id);
1161 asg_helper.log('End processing purgeSDQ','asg_cons_qpkg',g_stmt_level);
1162 EXCEPTION
1163 WHEN OTHERS THEN
1164 asg_helper.log('Exception in process_purge_sdq: ' || sqlerrm,
1165 'asg_cons_qpkg',g_err_level);
1166 RAISE;
1167 END process_purge_Sdq;
1168
1169 FUNCTION get_pk_predicate(l_primary_key_columns IN VARCHAR2)
1170 RETURN VARCHAR2 IS
1171 l_start NUMBER;
1172 l_end NUMBER;
1173 l_curr_col VARCHAR2(30);
1174 l_predicate VARCHAR2(2000);
1175 BEGIN
1176 IF( instr(l_primary_key_columns, ',') = 0 ) THEN
1177 -- single column primary key
1178 l_predicate := ' inq.' || l_primary_key_columns ||
1182 l_start := 1;
1179 ' = ' || 'piv.' || l_primary_key_columns || ' ';
1180 ELSE
1181
1183 l_end :=1;
1184 LOOP
1185 -- Find out if there is a comma delimiter
1186 l_end := instr(l_primary_key_columns, ',', l_start);
1187 -- Extract the string until the comma
1188 IF (l_end <> 0) THEN
1189 l_curr_col := substr(l_primary_key_columns, l_start, (l_end-l_start));
1190 ELSE
1191 l_curr_col := substr(l_primary_key_columns, l_start);
1192 END IF;
1193
1194 l_curr_col := ltrim(rtrim(l_curr_col));
1195 IF (l_start = 1) THEN
1196 l_predicate := ' inq.' || l_curr_col ||
1197 ' = ' || 'piv.' || l_curr_col || ' ';
1198 ELSE
1199 l_predicate := l_predicate || ' AND inq.' || l_curr_col ||
1200 ' = ' || 'piv.' || l_curr_col || ' ';
1201 END IF;
1202 IF(l_end =0) THEN
1203 EXIT;
1204 END IF;
1205 l_start := l_end +1;
1206
1207 END LOOP;
1208 END IF;
1209 return l_predicate;
1210
1211 END get_pk_predicate;
1212
1213 -- Routine for processing conflicts
1214 PROCEDURE process_pubitem_conflicts(p_user_name IN VARCHAR2,
1215 p_upload_tranid IN NUMBER,
1216 p_pubitem IN VARCHAR2)
1217 IS
1218 CURSOR c_conf_rows (p_user_name VARCHAR2,
1219 p_upload_tranid NUMBER,
1220 p_pubitem VARCHAR2) IS
1221 SELECT sequence
1222 FROM asg_conf_info
1223 WHERE user_name = p_user_name AND
1224 transaction_id = p_upload_tranid AND
1225 pub_item = p_pubitem AND
1226 sequence IS NOT NULL;
1227 l_client_wins VARCHAR2(1);
1228 l_server_wins VARCHAR2(1);
1229 l_conf_resolution VARCHAR2(1);
1230 l_inqtable_name VARCHAR2(60);
1231 l_piv VARCHAR2(30);
1232 l_conflict_callout VARCHAR2(100);
1233 l_primary_key_columns VARCHAR2(2000);
1234 l_pk_predicate VARCHAR2(2000);
1235 l_query_string VARCHAR2(2000);
1236 l_sequence NUMBER;
1237 l_download_tranid NUMBER;
1238 l_client_update_count NUMBER;
1239 l_server_update_count NUMBER;
1240 BEGIN
1241
1242 -- One more optimization before checking for conflicts
1243 -- Check if there any UPD DML from client
1244 -- and then check if there is any UPD DML from server
1245 l_inqtable_name := asg_base.G_OLITE_SCHEMA ||
1246 '.' || 'cfm$' || p_pubitem || ' ';
1247 l_query_string := 'SELECT count(*) ' ||
1248 'FROM ' || l_inqtable_name ||
1249 'WHERE clid$$cs = :1 AND ' ||
1250 ' tranid$$ = :2 AND ' ||
1251 ' dmltype$$ = ''U''';
1252 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
1253 asg_helper.log('SQL Command: ' || replace(l_query_string, '''', ''''''),
1254 'asg_cons_qpkg',g_stmt_level);
1255 END IF;
1256
1257 EXECUTE IMMEDIATE l_query_string
1258 INTO l_client_update_count
1259 USING p_user_name, p_upload_tranid;
1260
1261 IF (l_client_update_count =0) THEN
1262 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
1263 asg_helper.log('No conflicts exist.',
1264 'asg_cons_qpkg',g_stmt_level);
1265 END IF;
1266 return;
1267 END IF;
1268
1269 SELECT base_object_name, primary_key_column, conflict_callout
1270 INTO l_piv, l_primary_key_columns, l_conflict_callout
1271 FROM asg_pub_item
1272 WHERE name = p_pubitem;
1273
1274 l_client_wins := asg_base.G_CLIENT_WINS;
1275 l_server_wins := asg_base.G_SERVER_WINS;
1276 l_download_tranid := asg_base.get_current_tranid();
1277 -- Get the access_id of updated DMLs
1278 insert into asg_conf_info (user_name,
1279 pub_item,
1280 transaction_id,
1281 access_id,
1282 resolution,
1283 creation_date,
1284 created_by,
1285 last_update_date,
1286 last_updated_by)
1287 SELECT p_user_name, p_pubitem, p_upload_tranid, access_id, l_client_wins,
1288 sysdate, 1, sysdate, 1
1289 FROM asg_system_dirty_queue
1290 WHERE client_id = p_user_name AND
1291 pub_item = p_pubitem AND
1292 transaction_id = l_download_tranid AND
1293 download_flag = 'Y' AND
1294 dml_type = 2;
1295 l_server_update_count := SQL%ROWCOUNT;
1296
1297 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
1298 asg_helper.log('Number of updates in server: ' || l_server_update_count,
1299 'asg_cons_qpkg',g_stmt_level);
1300 END IF;
1301
1302 -- No updates from server. Return. Only conflicts between updates from
1303 -- client and updates from server are detected.
1304 IF (l_server_update_count = 0) THEN
1305 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
1306 asg_helper.log('No conflicts exist.',
1307 'asg_cons_qpkg',g_stmt_level);
1308 END IF;
1309 return;
1310 END IF;
1311
1312 -- Link the access-ids with sequence no in inq table
1313 l_pk_predicate := get_pk_predicate(l_primary_key_columns);
1314 l_query_string := 'UPDATE asg_conf_info ' ||
1315 'SET (sequence, access_id) = ' ||
1316 '(SELECT seqno$$, access_id ' ||
1317 ' FROM ' || l_inqtable_name || ' inq, ' ||
1321 ' inq.dmltype$$ = ''U'' AND ' ||
1318 l_piv || ' piv ' ||
1319 ' WHERE inq.clid$$cs = :1 AND ' ||
1320 ' inq.tranid$$ = :2 AND ' ||
1322 l_pk_predicate || ' AND ' ||
1323 ' piv.access_id in ' ||
1324 '(SELECT access_id ' ||
1325 ' FROM asg_conf_info ' ||
1326 ' WHERE user_name = :3 AND ' ||
1327 ' transaction_id = :4 AND ' ||
1328 ' pub_item = :5)) ' ||
1329 ' WHERE user_name = :6 AND ' ||
1330 ' transaction_id = :7 AND ' ||
1331 ' pub_item = :8';
1332
1333 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
1334 asg_helper.log('SQL Command: ' || replace(l_query_string,'''',''''''),
1335 'asg_cons_qpkg',g_stmt_level);
1336 END IF;
1337 EXECUTE IMMEDIATE l_query_string
1338 USING p_user_name, p_upload_tranid,
1339 p_user_name, p_upload_tranid, p_pubitem,
1340 p_user_name, p_upload_tranid, p_pubitem;
1341
1342 -- Ready to call the pubitem callback
1343 -- If conflict callout is not specified client wins
1344 IF (l_conflict_callout IS NOT NULL) THEN
1345 FOR ccr in c_conf_rows(p_user_name, p_upload_tranid, p_pubitem) LOOP
1346 l_sequence := ccr.sequence;
1347 /* l_query_string := 'SELECT ' || l_conflict_callout ||
1348 '(''' || p_user_name || ''', ' || p_upload_tranid ||
1349 ', ' || l_sequence || ') from dual';*/
1350 l_query_string := 'SELECT ' || l_conflict_callout ||
1351 '(:1,:2,:3) from dual';
1352 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
1353 asg_helper.log('SQL Command: ' || replace(l_query_string,'''',''''''),
1354 'asg_cons_qpkg',g_stmt_level);
1355 END IF;
1356 BEGIN
1357 l_conf_resolution := l_client_wins;
1358 EXECUTE IMMEDIATE l_query_string
1359 INTO l_conf_resolution
1360 USING p_user_name,p_upload_tranid,l_sequence ;
1361 EXCEPTION
1362 WHEN OTHERS THEN
1363 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
1364 asg_helper.log('Exception in pub item level callback. ' || sqlerrm,
1365 'asg_cons_qpkg',g_stmt_level);
1366 END IF;
1367 END;
1368 IF (l_conf_resolution = l_server_wins) THEN
1369 UPDATE asg_conf_info
1370 SET resolution = l_conf_resolution
1371 WHERE user_name = p_user_name AND
1372 transaction_id = p_upload_tranid AND
1373 pub_item = p_pubitem AND
1374 sequence = l_sequence;
1375 END IF;
1376 END LOOP;
1377 END IF;
1378
1379 -- Ok, all the conflict rows are processed. We need to
1380 -- reset download flag for those rows where client wins applies
1381 UPDATE asg_system_dirty_queue
1382 SET download_flag = NULL
1383 WHERE client_id = p_user_name AND
1384 pub_item = p_pubitem AND
1385 transaction_id = l_download_tranid AND
1386 dml_type = 2 AND
1387 access_id in (select access_id
1388 FROM asg_conf_info
1389 WHERE user_name = p_user_name AND
1390 transaction_id = p_upload_tranid AND
1391 pub_item = p_pubitem AND
1392 sequence IS NOT NULL AND
1393 resolution = l_client_wins);
1394
1395
1396 END process_pubitem_conflicts;
1397
1398 PROCEDURE set_user_hwm_tranid(p_user_name IN VARCHAR2,
1399 p_upload_tranid IN NUMBER)
1400 IS
1401 PRAGMA AUTONOMOUS_TRANSACTION;
1402 BEGIN
1403 asg_helper.update_hwm_tranid(p_user_name, p_upload_tranid);
1404 commit;
1405 EXCEPTION
1406 WHEN OTHERS THEN
1407 rollback;
1408 END set_user_hwm_tranid;
1409
1410 FUNCTION set_user_pwd_expired (p_user_name IN VARCHAR2,
1411 p_pwd_expired IN VARCHAR2)
1412 RETURN NUMBER
1413 IS
1414 PRAGMA AUTONOMOUS_TRANSACTION;
1415 l_first_synch NUMBER := 0;
1416 BEGIN
1417 UPDATE asg_user
1418 SET password_expired = p_pwd_expired
1419 WHERE user_name = p_user_name;
1420
1421 -- Find out if this is the very first synch for this user.
1422 SELECT count(*) into l_first_synch
1423 FROM asg_user
1424 WHERE user_name = p_user_name AND
1425 hwm_tranid IS NULL AND
1426 NOT EXISTS (SELECT 1
1427 FROM asg_purge_sdq
1428 WHERE user_name = p_user_name and
1429 transaction_id is NOT null);
1430 COMMIT;
1431 return l_first_synch;
1432 EXCEPTION
1433 WHEN OTHERS THEN
1434 rollback;
1435 return l_first_synch;
1436 END set_user_pwd_expired;
1437
1438 -- Wrapper procedure on fnd_user_pkg
1439 FUNCTION validate_login(p_user_name IN VARCHAR2,
1440 p_password IN VARCHAR2)
1441 RETURN VARCHAR2 IS
1442 l_user_authenticated VARCHAR2(1);
1443 l_loginID NUMBER;
1444 l_first_synch NUMBER;
1445 l_pwd_expired VARCHAR2(1);
1446 l_ret_status VARCHAR2(1);
1447 BEGIN
1448 l_user_authenticated := 'N';
1449 l_ret_status := fnd_web_sec.validate_login(p_user => p_user_name,
1450 p_pwd => p_password,
1454 IF (l_ret_status = 'Y') THEN
1451 p_loginID => l_loginID,
1452 p_expired => l_pwd_expired);
1453
1455 l_user_authenticated := 'Y';
1456 -- check if this is the user's very first synch
1457 -- Exception is raised in download_init incase of very first synch
1458 -- All other synchs, authentication is set to 'N' if
1459 -- password expired.
1460 l_first_synch := set_user_pwd_expired(p_user_name, l_pwd_expired);
1461 IF(l_first_synch = 0 AND l_pwd_expired = 'Y') THEN
1462 l_user_authenticated := 'N';
1463 END IF;
1464 END IF;
1465 return l_user_authenticated;
1466
1467 END validate_login;
1468
1469
1470 FUNCTION find_last_synch_date(p_user_name IN VARCHAR2,
1471 p_last_tranid IN NUMBER)
1472 RETURN DATE IS
1473 l_last_synch_date DATE;
1474 BEGIN
1475 IF ((p_last_tranid <= -1) OR
1476 (g_first_synch = TRUE)) THEN
1477 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
1478 asg_helper.log('First Synch detected.',
1479 'asg_cons_qpkg',g_stmt_level);
1480 END IF;
1481 return NULL;
1482 ELSE
1483 g_last_synch_successful := is_previous_synch_successful(p_user_name,
1484 p_last_tranid);
1485 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
1486 asg_helper.log('Last Synch Successful: ' || g_last_synch_successful,
1487 'asg_cons_qpkg',g_stmt_level);
1488 END IF;
1489 IF (g_last_synch_successful = FND_API.G_TRUE) THEN
1490 SELECT last_synch_date_end into l_last_synch_date
1491 FROM asg_user
1492 WHERE user_name = p_user_name;
1493 ELSE
1494 SELECT prior_synch_date_end into l_last_synch_date
1495 FROM asg_user
1496 WHERE user_name = p_user_name;
1497 END IF;
1498 return l_last_synch_date;
1499 END IF;
1500
1501 END find_last_synch_date;
1502
1503 FUNCTION find_device_type(p_user_name VARCHAR2)
1504 RETURN VARCHAR2 IS
1505 l_device_type VARCHAR2(30) := null;
1506 BEGIN
1507 -- Find the device type and detect device switch as well
1508 asg_base.detect_device_switch(p_user_name, l_device_type);
1509 RETURN l_device_type;
1510 EXCEPTION
1511 WHEN OTHERS THEN
1512 return l_device_type;
1513 END find_device_type;
1514
1515
1516 END asg_cons_qpkg;