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