[Home] [Help]
PACKAGE BODY: APPS.ASG_DOWNLOAD
Source
1 PACKAGE BODY asg_download AS
2 /* $Header: asgdwldb.pls 120.10.12020000.5 2013/04/09 11:56:11 saradhak ship $*/
3
4 /** CONSTANTS */
5 CONS_SCHEMA CONSTANT VARCHAR2(30) := ASG_BASE.G_OLITE_SCHEMA;
6 OLITE_SEQUENCE CONSTANT VARCHAR2(30) := 'C$ALL_SEQUENCE_PARTITIONS';
7 LOG_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
8
9
10 /** Global Variables */
11 g_clientid VARCHAR2(100);
12 g_last_tranid NUMBER;
13 g_log_table VARCHAR2(30);
14 g_complete_ref_pub_items VARCHAR2(32767) := NULL;
15 g_pub_items_list VARCHAR2(32767) := NULL;
16
17 g_purge_log_enabled VARCHAR2(1) := NULL;
18
19 function get_pk(pi_name varchar2,p_qid number)
20 return varchar2
21 is
22 l_pk varchar2(128);
27 l_res varchar2(128);
23 l_pk_ct number;
24 l_ret boolean;
25 l_pkstr varchar2(128);
26 l_qry varchar2(512);
28 begin
29 l_pkstr := null;
30 l_ret := getPrimaryKeys(pi_name,l_pk,l_pk_ct);
31 --dbms_output.put_line('NUM OF PK: '||pk_ct);
32 --dbms_output.put_line('PK: '||pk);
33 for i in 1..l_pk_ct
34 loop
35 if l_pkstr is null
36 then
37 l_pkstr := 'ATTRIBUTE'||i||'';
38 else
39 l_pkstr := l_pkstr||'||'||''',''||'||'ATTRIBUTE'||i||'';
40 end if;
41 end loop;
42 --dbms_output.put_line('str: '||l_pkstr);
43 l_qry := 'select '||l_pkstr||' from asg_delete_queue where qid = :1';
44 execute immediate l_qry into l_res using p_qid;
45 --dbms_output.put_line('ret PK: '||l_res);
46 return l_res;
47 end get_pk;
48
49 function raise_data_downloaded
50 return boolean
51 is
52 l_qry varchar2(4000);
53 l_ctx dbms_xmlquery.ctxType;
54 l_clob clob;
55 l_ct number;
56 l_seq number;
57 begin
58 log('Start raise_data_downloaded');
59 l_qry := 'select client_id,pub_item,access_id,dml_type, '||
60 ' transaction_id,null pk_val,sysdate synch_time '||
61 ' from asg_system_dirty_queue '||
62 ' where download_flag=''Y'' and client_id=asg_base.get_user_name'||
63 ' and transaction_id = asg_base.get_current_tranid '||
64 ' and dml_type <> 0 '||
65 ' and pub_item in ( select item_id from asg_pub_item '||
66 ' where nvl(enable_download_events,''N'') = ''Y'' )'||
67 ' UNION ALL '||
68 ' select client_id,pub_item,access_id,dml_type, '||
69 ' transaction_id , asg_download.get_pk(pub_item,sdq.qid) pk_val, '||
70 ' sysdate synch_time '||
71 ' from asg_system_dirty_queue sdq,asg_delete_queue dq '||
72 ' where download_flag=''Y'' and client_id=asg_base.get_user_name'||
73 ' and transaction_id = asg_base.get_current_tranid '||
74 ' and dml_type = 0 '||
75 ' and pub_item in ( select item_id from asg_pub_item '||
76 ' where nvl(enable_download_events,''N'') = ''Y'' ) '||
77 ' and sdq.qid=dq.qid ';
78 select count(*) into l_ct
79 from asg_system_dirty_queue
80 where download_flag='Y'
81 and client_id = asg_base.get_user_name
82 and transaction_id = asg_base.get_current_tranid
83 and pub_item in
84 ( select item_id from asg_pub_item
85 where nvl(enable_download_events,'N') = 'Y');
86
87 if(l_ct <> 0 )
88 then
89 log('Query :'||l_qry);
90 l_ctx := dbms_xmlquery.newContext(l_qry);
91 dbms_lob.createtemporary(l_clob,true,dbms_lob.session);
92 l_clob := dbms_xmlquery.getXml(l_ctx);
93 log('Finished building clob. Num of records :'||l_ct);
94 log('Raising event oracle.apps.asg.download.datasynched');
95 select asg_events_s.nextval into l_seq from dual;
96 wf_event.raise(p_event_name=>'oracle.apps.asg.download.datasynched',
97 p_event_key=>l_seq,p_parameters=>null,
98 p_event_data=>l_clob,p_send_date=>null);
99 log('Successfully raised event oracle.apps.asg.download.datasynched');
100 else
101 log('No data to raise the event oracle.apps.asg.download.datasynched');
102 end if;
103 log('End raise_data_downloaded');
104 return true;
105 exception
106 when others then
107 log('Error raising event oracle.apps.asg.download.datasynched');
108 return false;
109 end raise_data_downloaded;
110
111
112 function raise_data_download_confirmed
113 return boolean
114 is
115 l_seq number;
116 l_qry varchar2(4000);
117 l_ctx dbms_xmlquery.ctxType;
118 l_clob clob;
119 l_ct number;
120 begin
121 log('Start raise_data_download_confirmed');
122 l_qry := 'select client_id,pub_item,access_id,dml_type, '||
123 ' transaction_id last_tran_id,asg_base.get_current_tranid '||
124 ' curr_tran_id ,null pk_val,sysdate synch_time '||
125 ' from asg_system_dirty_queue '||
126 ' where download_flag=''Y'' and client_id=asg_base.get_user_name'||
127 ' and transaction_id <= asg_base.get_last_tranid '||
128 ' and dml_type <> 0 '||
129 ' and pub_item in ( select item_id from asg_pub_item '||
130 ' where nvl(enable_download_events,''N'') = ''Y'' )'||
131 ' UNION ALL '||
132 ' select client_id,pub_item,access_id,dml_type, '||
133 ' transaction_id last_tran_id,asg_base.get_current_tranid '||
134 ' curr_tran_id, asg_download.get_pk(pub_item,sdq.qid) pk_val, '||
135 ' sysdate synch_time '||
136 ' from asg_system_dirty_queue sdq,asg_delete_queue dq '||
137 ' where download_flag=''Y'' and client_id=asg_base.get_user_name'||
138 ' and transaction_id <= asg_base.get_last_tranid '||
139 ' and dml_type = 0 '||
140 ' and pub_item in ( select item_id from asg_pub_item '||
141 ' where nvl(enable_download_events,''N'') = ''Y'' )'||
142 ' and sdq.qid=dq.qid ';
143 select count(*) into l_ct
144 from asg_system_dirty_queue
145 where download_flag='Y'
146 and client_id = asg_base.get_user_name
147 and transaction_id <= asg_base.get_last_tranid
148 and pub_item in
149 ( select item_id from asg_pub_item
150 where nvl(enable_download_events,'N') = 'Y');
151
152 if(l_ct <> 0)
156 dbms_lob.createtemporary(l_clob,true,dbms_lob.session);
153 then
154 log('Query :'||l_qry);
155 l_ctx := dbms_xmlquery.newContext(l_qry);
157 l_clob := dbms_xmlquery.getXml(l_ctx);
158 log('Finished building clob. Num of records :'||l_ct);
159 log('Raising event oracle.apps.asg.download.datasynchconfirmed');
160 select asg_events_s.nextval into l_seq from dual;
161 wf_event.raise(p_event_name=>'oracle.apps.asg.download.datasynchconfirmed',
162 p_event_key=>l_seq,p_parameters=>null,
163 p_event_data=>l_clob,p_send_date=>null);
164 log('Successfully raised event oracle.apps.asg.download.datasynchconfirmed');
165 else
166 log('No data to raise the event oracle.apps.asg.download.datasynchconfirmed');
167 end if;
168 log('End raise_data_download_confirmed');
169 return true;
170 exception
171 when others then
172 log('Error raising event oracle.apps.asg.download.datasynchconfirmed');
173 return false;
174 end raise_data_download_confirmed;
175
176
177 /*
178 Given a comma seperated char literals, this routine returns
179 a char list
180 The input can also have literals enclosed in single quotes.
181 */
182 FUNCTION get_listfrom_string (p_string1 IN varchar2)
183 RETURN pk_list
184 IS
185 l_temp1 NUMBER;
186 l_temp2 NUMBER;
187 l_temp3 NUMBER;
188 str VARCHAR2(30);
189 len NUMBER;
190 ind number;
191 l_list pk_list;
192 l_string varchar2(32767);
193 BEGIN
194 l_temp1:=1;
195 l_temp2:=1;
196 l_temp3:=1;
197 ind := 1;
198 l_string := replace(p_string1,'''','');
199 len:=nvl(length(l_string),-1);
200 IF (len = -1) THEN
201 return l_list;
202 END IF;
203 LOOP
204 l_temp2:=instr(l_string,',',1,l_temp1);
205 IF( l_temp2=0 ) THEN
206 l_list(ind):=rtrim(ltrim(substr(l_string,l_temp3,len)));
207 --dbms_output.put_line(l_list(ind));
208 ind := ind+1;
209 EXIT;
210 END IF;
211 l_list(ind):=rtrim(ltrim(substr(l_string,l_temp3,l_temp2-l_temp3)));
212 --dbms_output.put_line(l_list(ind));
213 ind:=ind+1;
214 l_temp3:=l_temp2+1;
215 l_temp1:=l_temp1+1;
216 END LOOP;
217 RETURN l_list;
218 END get_listfrom_string;
219
220 FUNCTION get_predicate_clause(p_predicate_list IN VARCHAR2)
221 RETURN VARCHAR2 IS
222 l_predicate_clause VARCHAR2(512) := NULL;
223 l_predicate_list VARCHAR2(150);
224 BEGIN
225 /* We support three predicate clauses
226 -- resource_id, user_id, language */
227 l_predicate_list := upper(p_predicate_list);
228 IF (instr(l_predicate_list, 'RESOURCE_ID') <> 0) THEN
229 l_predicate_clause := ' resource_id = asg_base.get_resource_id() ';
230 END IF;
231 IF (instr(l_predicate_list, 'USER_ID') <> 0) THEN
232 IF(l_predicate_clause IS NOT NULL) THEN
233 l_predicate_clause := l_predicate_clause || ' AND ' ||
234 ' user_id = asg_base.get_user_id() ';
235 ELSE
236 l_predicate_clause := ' user_id = asg_base.get_user_id() ';
237 END IF;
238 END IF;
239 IF (instr(l_predicate_list, 'LANGUAGE') <> 0) THEN
240 IF(l_predicate_clause IS NOT NULL) THEN
241 l_predicate_clause := l_predicate_clause || ' AND ' ||
242 ' language = asg_base.get_language() ';
243 ELSE
244 l_predicate_clause := ' language = asg_base.get_language() ';
245 END IF;
246 END IF;
247
248 return l_predicate_clause;
249 END get_predicate_clause;
250
251 PROCEDURE reset_all_globals
252 IS
253 BEGIN
254
255 g_clientid := NULL;
256 g_last_tranid := NULL;
257 g_log_table := NULL;
258 g_complete_ref_pub_items := NULL;
259 g_pub_items_list := NULL;
260
261 END reset_all_globals;
262
263
264 /** Function to tell if it's a 1st Sync */
265 FUNCTION isFirstSync RETURN BOOLEAN IS
266 l_query VARCHAR2 (300);
267 l_cnt NUMBER := 0;
268 BEGIN
269 l_query := 'SELECT COUNT(*) FROM '||CONS_SCHEMA||'.'||'c$pub_list_q '
270 ||' WHERE comp_ref <> ''Y''';
271 EXECUTE IMMEDIATE l_query INTO l_cnt;
272
273 IF (l_cnt = 0) THEN
274 log('First Sync');
275 RETURN TRUE;
276 ELSE
277 log('Subsequent Sync');
278 RETURN FALSE;
279 END IF;
280 END isFirstSync;
281
282
283 /** Function to Capture the PK of a Deleted Record
284 * in asg_delete_queue */
285 FUNCTION storeDeletedPK ( p_pub_item IN VARCHAR2,
286 p_accessList IN access_list,
287 p_qidList IN qid_list
288 ) RETURN BOOLEAN IS
289
290 l_pk_list VARCHAR2(500);
291 l_pos NUMBER := 1;
292 l_pk_cnt NUMBER := 0;
293 l_att_col_list VARCHAR2(500);
294 l_rc BOOLEAN;
295 l_dml VARCHAR2(2000);
296 l_base_owner asg_pub_item.base_owner%TYPE;
297 l_base_object asg_pub_item.base_object_name%TYPE;
298 l_access_owner asg_pub_item.access_owner%TYPE;
299 l_access_name asg_pub_item.access_name%TYPE;
300 l_accessList dbms_sql.Number_table;
301 l_qidList dbms_sql.Number_table;
302 l_cur_id NUMBER;
303 l_cur_rc NUMBER;
304
305 BEGIN
306
307 log ('Function storeDeletedPK');
308
309 -- Get PK Columns
313 FOR i IN 1..l_pk_cnt
310 l_rc := getPrimaryKeys (p_pub_item, l_pk_list, l_pk_cnt);
311
312 -- Attribute Col List
314 LOOP
315 l_att_col_list := l_att_col_list || ', attribute'||i;
316 END LOOP;
317
318 -- Copy Values to DBMS_SQL Table Structure
319 FOR i IN 1..p_accessList.COUNT
320 LOOP
321 l_accessList (i) := p_accessList(i);
322 l_qidList (i) := p_qidList(i);
323 END LOOP;
324
325 -- Get the Base Table to Select From
326 l_dml := 'SELECT base_owner, base_object_name, access_owner, access_name '||
327 ' FROM asg_pub_item WHERE name=:1';
328 EXECUTE IMMEDIATE l_dml INTO l_base_owner, l_base_object, l_access_owner,
329 l_access_name USING p_pub_item;
330
331 l_cur_id := DBMS_SQL.OPEN_CURSOR;
332
333 IF ( (l_access_owner IS NULL) AND (l_access_name IS NULL) ) THEN
334 DBMS_SQL.PARSE (l_cur_id, 'INSERT INTO asg_delete_queue '
335 || '(qid, creation_date, created_by, '
336 || 'last_update_date, last_updated_by '
337 || l_att_col_list
338 || ') SELECT :1, sysdate, '
339 || '1, sysdate, 1, '||l_pk_list
340 || ' FROM '||l_base_owner||'.'||l_base_object
341 || ' WHERE access_id = :2', DBMS_SQL.v7);
342 ELSE
343 DBMS_SQL.PARSE (l_cur_id, 'INSERT INTO asg_delete_queue '
344 || ' (qid, creation_date, '
345 || 'created_by, last_update_date, '
346 || 'last_updated_by '||l_att_col_list
347 || ') SELECT :1, sysdate, '
348 || '1, sysdate, 1, '||l_pk_list
349 || ' FROM '||l_access_owner||'.'||l_access_name
350 || ' WHERE access_id = :2', DBMS_SQL.v7);
351 END IF;
352
353 DBMS_SQL.BIND_ARRAY (l_cur_id, ':1', l_qidList, 1, l_qidList.COUNT);
354 DBMS_SQL.BIND_ARRAY (l_cur_id, ':2', l_accessList, 1, l_accessList.COUNT);
355
356 l_cur_rc := DBMS_SQL.EXECUTE ( l_cur_id );
357 DBMS_SQL.CLOSE_CURSOR (l_cur_id);
358
359 log ('END Function storeDeletedPK');
360
361 RETURN OK;
362 EXCEPTION
363 WHEN OTHERS THEN
364 log(sqlerrm);
365 RAISE;
366 END storeDeletedPK;
367
368
369 /** Function to Capture the PK of a Deleted Record
370 * in asg_delete_queue given the PK List */
371 FUNCTION storeDeletedPK ( p_pub_item IN VARCHAR2,
372 p_qid IN NUMBER,
373 p_pkvalList IN pk_list
374 ) RETURN BOOLEAN IS
375
376 l_att_col_list VARCHAR2(500);
377 l_pk_val_list VARCHAR2(4000);
378 l_rc BOOLEAN;
379 l_dml VARCHAR2(4000);
380
381 BEGIN
382
383 log ('Function storeDeletedPK - with PK Values Given');
384
385 IF (p_pkvalList.COUNT > 0 ) THEN
386
387 log ('PK Values Given');
388 FOR i IN 1..p_pkvalList.COUNT
389 LOOP
390 l_att_col_list := l_att_col_list || ', attribute'||i;
391 l_pk_val_list := l_pk_val_list || ',''' || p_pkvalList(i) || '''';
392 END LOOP;
393
394 l_dml := 'INSERT INTO asg_delete_queue (qid, creation_date, '
395 || 'created_by, last_update_date, last_updated_by '
396 || l_att_col_list || ') VALUES '
397 || ' ( ' || p_qid || ', sysdate, 1, sysdate, 1 '||l_pk_val_list
398 || ')';
399
400 EXECUTE IMMEDIATE l_dml;
401
402 END IF;
403 log ('END Function storeDeletedPK - with PK Values Given');
404
405 RETURN OK;
406 EXCEPTION
407 WHEN OTHERS THEN
408 log(sqlerrm);
409 RAISE;
410 END storeDeletedPK;
411
412
413 /** Function to store the PK of a Deleted Record
414 * in asg_delete_queue - For Reject Record */
415 FUNCTION storeDeletedPK ( p_pub_item IN VARCHAR2,
416 p_client_name IN VARCHAR2,
417 p_tran_id IN NUMBER,
418 p_seq_no IN NUMBER,
419 p_qid IN NUMBER
420 ) RETURN BOOLEAN IS
421
422 l_pk_list VARCHAR2(500);
423 l_pos NUMBER := 1;
424 l_pk_cnt NUMBER := 0;
425 l_att_col_list VARCHAR2(500);
426 l_rc BOOLEAN;
427 l_dml VARCHAR2(4000);
428 l_inq_owner asg_pub_item.inq_owner%TYPE;
429 l_inq_name asg_pub_item.inq_name%TYPE;
430
431 BEGIN
432
433 log ('Function storeDeletedPK - Reject Record');
434
435 -- Get PK Columns
436 l_rc := getPrimaryKeys (p_pub_item, l_pk_list, l_pk_cnt);
437
438 -- Attribute Col List
439 FOR i IN 1..l_pk_cnt
440 LOOP
441 l_att_col_list := l_att_col_list || ', attribute'||i;
442 END LOOP;
443
444 l_dml := 'SELECT inq_owner, inq_name FROM asg_pub_item '||
445 ' WHERE name=:1';
446 EXECUTE IMMEDIATE l_dml INTO l_inq_owner, l_inq_name USING p_pub_item;
447
448 IF (l_inq_owner IS NULL) THEN
449 l_inq_owner := CONS_SCHEMA;
450 END IF;
451
452 IF (l_inq_name IS NULL) THEN
453 l_inq_name := 'CFM$'||p_pub_item;
454 END IF;
455 log ('Inqueue is '||l_inq_owner||'.'||l_inq_name);
456
457 l_dml := 'INSERT INTO asg_delete_queue (qid, creation_date, '
461 || ' FROM '||l_inq_owner||'.'||l_inq_name
458 || 'created_by, last_update_date, last_updated_by '||l_att_col_list
459 || ') SELECT :1, sysdate, '
460 || '1, sysdate, 1, '||l_pk_list
462 || ' WHERE clid$$cs = :2 AND TRANID$$ = :3 AND seqno$$ = :4';
463 EXECUTE IMMEDIATE l_dml USING p_qid, p_client_name, p_tran_id, p_seq_no;
464
465 log ('END Function storeDeletedPK - Reject Record');
466
467 RETURN OK;
468 EXCEPTION
469 WHEN OTHERS THEN
470 log(sqlerrm);
471 RAISE;
472 END storeDeletedPK;
473
474
475 /** Mark Dirty a Publication Item
476 * Support for Reject Record */
477 --ver 1
478 FUNCTION markDirty ( p_pub_item IN VARCHAR2,
479 p_user_name IN VARCHAR2,
480 p_tran_id IN NUMBER,
481 p_seq_no IN NUMBER ) RETURN BOOLEAN IS
482
483 l_mobile_user VARCHAR2(100);
484 l_dml VARCHAR2(2000);
485 l_rc BOOLEAN;
486 l_qid NUMBER;
487 BEGIN
488
489 log ('Function markDirty - Reject Record ');
490
491 -- Mark Publication Item Dirty
492 IF (insert_sdq(p_pub_item,p_user_name) AND
493 is_exists(p_user_name,p_pub_item,p_seq_no,'D'))
494 THEN
495 INSERT INTO asg_system_dirty_queue (
496 qid, creation_date, created_by, last_update_date, last_updated_by,
497 pub_item, access_id, client_id, transaction_id
498 , dml_type, download_flag)
499 VALUES (
500 asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,
501 p_pub_item, p_seq_no, p_user_name, NULL , 0, NULL)
502 RETURNING qid INTO l_qid;
503
504 l_rc := storeDeletedPK(p_pub_item, p_user_name, p_tran_id,
505 p_seq_no, l_qid);
506 END if;
507 log ('END Function markDirty');
508
509 RETURN OK;
510 EXCEPTION
511 WHEN OTHERS THEN
512 log(sqlerrm);
513 RAISE;
514 END markDirty;
515
516
517 /** Mark dirty a Publication Item
518 * Given a access_id, user_id and DML Type (internal)*/
519 --ver 2 internal
520 FUNCTION mark_dirty_internal ( p_pub_item IN VARCHAR2,
521 p_accessid IN NUMBER,
522 p_username IN VARCHAR2,
523 p_dml IN CHAR,
524 p_timestamp IN DATE ) RETURN BOOLEAN IS
525 l_mobile_user VARCHAR2(100);
526 l_dml VARCHAR2(2000);
527 l_rc BOOLEAN;
528 l_accesslist access_list;
529 l_qidlist qid_list;
530 l_qid NUMBER;
531 BEGIN
532 log ('Function mark_dirty_internal - Reject Record ');
533 --l_mobile_user := asg_base.get_user_name; ---modify this logic
534 --select user_name into l_mobile_user from asg_user where user_id=p_userid;
535 -- Mark Publication Item Dirty
536 IF (insert_sdq(p_pub_item,p_username) AND
537 is_exists(p_username,p_pub_item,p_accessid,p_dml))
538 THEN
539 INSERT INTO asg_system_dirty_queue (
540 qid, creation_date, created_by, last_update_date, last_updated_by,
541 pub_item, access_id, client_id, transaction_id
542 , dml_type, download_flag)
543 VALUES (
544 asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,
545 p_pub_item, p_accessid, p_username, NULL ,
546 DECODE(p_dml,'D',0,'I',1,'U',2), NULL)
547 RETURNING qid INTO l_qid;
548
549 IF (p_dml = 'D') THEN
550 l_accesslist(1) := p_accessid;
551 l_qidlist(1) := l_qid;
552 l_rc := storeDeletedPK(p_pub_item, l_accesslist, l_qidlist);
553 END IF;
554 END IF;
555 log ('END Function mark_dirty_internal');
556
557 RETURN OK;
558 EXCEPTION
559 WHEN OTHERS THEN
560 log(sqlerrm);
561 RAISE;
562 END mark_dirty_internal;
563
564
565 /** Mark Dirty a Publication Item
566 * Given a access_id, resource_id and DML Type */
567 --ver 2
568 FUNCTION markDirty ( p_pub_item IN VARCHAR2,
569 p_accessid IN NUMBER,
570 p_resourceid IN NUMBER,
571 p_dml IN CHAR,
572 p_timestamp IN DATE ) RETURN BOOLEAN IS
573
574
575 l_mobile_user VARCHAR2(100);
576 l_retval BOOLEAN;
577 BEGIN
578 log ('Function markDirty - single row ');
579 l_mobile_user := asg_base.get_user_name(p_resourceid);
580 if(l_mobile_user is null) then
581 log('Invalid mobile user with resource ID : '||p_resourceid||' ');
582 return FAIL;
583 end if;
584 l_retval:=mark_dirty_internal(p_pub_item,p_accessid,l_mobile_user,p_dml,
585 p_timestamp);
586 RETURN OK;
587 EXCEPTION
588 WHEN OTHERS THEN
589 log(sqlerrm);
590 RAISE;
591 END markDirty;
592
593
594 /** Mark Dirty a Publication Item
595 * Given a access_id, user_id and DML Type (internal)*/
596 --ver 3 internal
597 FUNCTION mark_dirty_internal ( p_pub_item IN VARCHAR2,
598 p_accessid IN NUMBER,
599 p_username IN VARCHAR2,
600 p_dml IN CHAR,
601 p_timestamp IN DATE,
602 p_pkvalues IN pk_list ) RETURN BOOLEAN IS
603 l_dml VARCHAR2(2000);
604 l_rc BOOLEAN;
605 l_accesslist access_list;
609 -- Mark Publication Item Dirty
606 l_qidlist qid_list;
607 l_qid NUMBER;
608 BEGIN
610 IF (insert_sdq(p_pub_item,p_username) AND
611 is_exists(p_username,p_pub_item,p_accessid,p_dml))
612 THEN
613 INSERT INTO asg_system_dirty_queue (
614 qid, creation_date, created_by, last_update_date, last_updated_by,
615 pub_item, access_id, client_id, transaction_id
616 , dml_type, download_flag)
617 VALUES (
618 asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,
619 p_pub_item, p_accessid, p_username, NULL ,
620 DECODE(p_dml,'D',0,'I',1,'U',2), NULL)
621 RETURNING qid INTO l_qid;
622 IF (p_dml = 'D') THEN
623 /* l_accesslist(1) := p_accessid;
624 l_qidlist(1) := l_qid; */
625 l_rc := storeDeletedPK(p_pub_item, l_qid, p_pkvalues);
626 END IF;
627 END IF;
628 RETURN OK;
629 EXCEPTION
630 WHEN OTHERS THEN
631 log(sqlerrm);
632 RAISE;
633 END mark_dirty_internal;
634
635
636 /** Mark Dirty a Publication Item
637 * Given a access_id, resource_id and DML Type */
638 --ver 3
639 FUNCTION markDirty ( p_pub_item IN VARCHAR2,
640 p_accessid IN NUMBER,
641 p_resourceid IN NUMBER,
642 p_dml IN CHAR,
643 p_timestamp IN DATE,
644 p_pkvalues IN pk_list ) RETURN BOOLEAN IS
645
646 l_mobile_user VARCHAR2(100);
647 l_retval BOOLEAN;
648 BEGIN
649 log ('Function markDirty - single row with PK Values given');
650 l_mobile_user := asg_base.get_user_name(p_resourceid);
651 if(l_mobile_user is null) then
652 log('Invalid mobile user with resource ID : '||p_resourceid||' ');
653 return FAIL;
654 end if;
655 l_retval:=mark_dirty_internal(p_pub_item, p_accessid, l_mobile_user, p_dml,
656 p_timestamp,p_pkvalues);
657 RETURN OK;
658 EXCEPTION
659 WHEN OTHERS THEN
660 log(sqlerrm);
661 RAISE;
662 END markDirty;
663
664
665 /** Mark Dirty for a Publication Item
666 * Given a List of access_id, resource_id and dml_types */
667 --ver 4 internal
668 FUNCTION mark_dirty_internal ( p_pub_item IN VARCHAR2,
669 p_accessList IN access_list,
670 p_username_list IN username_list,
671 p_dmlList IN dml_list,
672 p_timestamp IN DATE) RETURN BOOLEAN IS
673
674 l_qid_comp_list qid_list;
675 l_qid_pruned_list qid_list;
676 l_accesslist access_list;
677 l_rc BOOLEAN;
678 l_tab_ind NUMBER := 0;
679
680 l_tmp_access_list access_list;
681 l_tmp_username_list username_list;
682 l_tmp_dml_list dml_list;
683 l_ctr NUMBER;
684 BEGIN
685 IF ( (p_accessList.count <> p_username_list.count ) OR
686 (p_accessList.count <> p_dmlList.count) ) THEN
687 RAISE PARAMETER_COUNT_MISMATCH;
688 END IF;
689 l_ctr := 1;
690
691 --compares each element in the list with asg_purge_sdq ,asg_complete_Refresh
692 -- and SDQ and constructs a new list.. which is used for further processing.
693 FOR i IN p_accessList.FIRST..p_accessList.LAST
694 LOOP
695 IF (insert_sdq(p_pub_item,p_username_list(i))
696 AND is_exists(p_username_list(i),p_pub_item,
697 p_accessList(i),p_dmlList(i)))
698 THEN
699 l_tmp_access_list(l_ctr) := p_accessList(i);
700 l_tmp_username_list(l_ctr) := p_username_list(i);
701 l_tmp_dml_list(l_ctr) := p_dmlList(i);
702 l_ctr := l_ctr + 1;
703 END IF ;
704 END LOOP;
705
706 log ('Function markdirty internal - Accessid-Resourceid-DML 1-1-1');
707 -- Mark Records Dirty for a Mobile User
708 IF( nvl(l_tmp_access_list.COUNT,0) <> 0 )
709 THEN
710 FORALL i IN l_tmp_access_list.FIRST..l_tmp_access_list.LAST
711 INSERT INTO asg_system_dirty_queue (
712 qid, creation_date, created_by, last_update_date, last_updated_by,
713 pub_item, access_id, client_id, transaction_id
714 , dml_type, download_flag)
715 VALUES (
716 asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,
717 p_pub_item, l_tmp_access_list(i), l_tmp_username_list(i),
718 NULL , DECODE(l_tmp_dml_list(i),'D',0,'I',1,'U',2), NULL)
719 RETURNING qid BULK COLLECT INTO l_qid_comp_list;
720
721 -- Get the access_id and qid's for records with Delete operation
722 FOR i IN 1..l_qid_comp_list.COUNT LOOP
723 IF (l_tmp_dml_list(i) = 'D') THEN
724 l_tab_ind := l_tab_ind + 1;
725 l_qid_pruned_list(l_tab_ind) := l_qid_comp_list(i);
726 l_accesslist(l_tab_ind) := l_tmp_access_list(i);
727 END IF;
728 END LOOP;
729
730 IF (NOT (l_qid_pruned_list IS NULL) ) AND
731 (l_tab_ind > 0) THEN
732 l_rc := storeDeletedPK(p_pub_item, l_accesslist, l_qid_pruned_list);
733 END IF;
734 END IF;
735
736 log ('END Function markDirty - Accessid-Resourceid-DML 1-1-1');
737 RETURN OK;
738 EXCEPTION
739 WHEN OTHERS THEN
740 log(sqlerrm);
741 RAISE;
742 END mark_dirty_internal;
743
744
745 /** Mark Dirty for a Publication Item
746 * Given a List of access_id, resource_id and dml_types */
747 --ver 4
748 FUNCTION markDirty ( p_pub_item IN VARCHAR2,
749 p_accessList IN access_list,
753
750 p_resourceList IN user_list,
751 p_dmlList IN dml_list,
752 p_timestamp IN DATE) RETURN BOOLEAN IS
754 l_username_list username_list;
755 l_retval BOOLEAN;
756 l_temp_ptr number;
757 l_invalid_reslist varchar2(3900);
758 l_ctr number;
759 l_flag number;
760 l_user_name varchar2(100);
761 l_accessList access_list;
762 l_dmlList dml_list;
763 l_sri_count NUMBER;
764 BEGIN
765 l_ctr := 1;
766 l_flag := 0;
767 IF ( (p_accessList.count <> p_resourceList.count ) OR
768 (p_accessList.count <> p_dmlList.count) ) THEN
769 RAISE PARAMETER_COUNT_MISMATCH;
770 END IF;
771
772 FOR i in 1..p_resourceList.count
773 LOOP
774 l_user_name := asg_base.get_user_name(p_resourceList(i));
775 if(l_user_name is null) then
776 if(l_flag = 0 ) then
777 l_invalid_reslist := ''''||p_resourceList(i)||'''';
778 l_flag := 1;
779 else
780 l_invalid_reslist := l_invalid_reslist||','''||p_resourceList(i)||'''';
781 end if;
782 else
783 l_username_list(l_ctr) := l_user_name;
784 l_accessList(l_ctr) := p_accessList(i);
785 l_dmlList(l_ctr) := p_dmlList(i);
786 l_ctr := l_ctr + 1;
787 end if;
788 l_user_name := null;
789 END LOOP;
790 l_retval:=mark_dirty_internal(p_pub_item, l_accessList, l_username_list,
791 l_dmlList,p_timestamp);
792 if(l_flag = 1 ) then
793 log('Invalid mobile user(s) with resource ID(s) : '
794 ||l_invalid_reslist);
795 return FAIL;
796 end if;
797 RETURN OK;
798 EXCEPTION
799 WHEN OTHERS THEN
800 log(sqlerrm);
801 RAISE;
802 END markDirty;
803
804
805 /** Mark Dirty for a Publication Item
806 * Given a List of access_id and resource_id with a Single DML Type */
807 --ver 5 internal
808 FUNCTION mark_dirty_internal ( p_pub_item IN VARCHAR2,
809 p_accessList IN access_list,
810 p_username_list IN username_list,
811 p_dml_type IN CHAR,
812 p_timestamp IN DATE) RETURN BOOLEAN IS
813
814 l_qid_list qid_list;
815 l_rc BOOLEAN;
816
817 l_tmp_access_list access_list;
818 l_tmp_username_list username_list;
819 l_tmp_dml_list dml_list;
820 l_ctr NUMBER;
821 BEGIN
822 log ('Function mark dirty internal - Accessid-Resourceid - ' ||
823 '1-1 - Single DML');
824 IF (p_accessList.count <> p_username_list.count ) THEN
825 RAISE PARAMETER_COUNT_MISMATCH;
826 END IF;
827
828 l_ctr := 1;
829 FOR i IN p_accessList.FIRST..p_accessList.LAST
830 LOOP
831 IF ( insert_sdq(p_pub_item,p_username_list(i)) AND
832 is_exists(p_username_list(i),p_pub_item,p_accessList(i),p_dml_type))
833 THEN
834 l_tmp_access_list(l_ctr) := p_accessList(i);
835 l_tmp_username_list(l_ctr) := p_username_list(i);
836 l_ctr := l_ctr + 1;
837 END IF ;
838 END LOOP;
839
840 -- Mark Records Dirty for a Mobile User
841 IF( nvl(l_tmp_access_list.COUNT,0) <> 0 )
842 THEN
843 FORALL i IN l_tmp_access_list.FIRST..l_tmp_access_list.LAST
844 INSERT INTO asg_system_dirty_queue (
845 qid, creation_date, created_by, last_update_date, last_updated_by,
846 pub_item, access_id, client_id, transaction_id
847 , dml_type, download_flag)
848 VALUES (
849 asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,
850 p_pub_item, l_tmp_access_list(i), l_tmp_username_list(i),
851 NULL , DECODE(p_dml_type,'D',0,'I',1,'U',2), NULL)
852 RETURNING qid BULK COLLECT INTO l_qid_list;
853 IF (p_dml_type = 'D') THEN
854 l_rc := storeDeletedPK(p_pub_item, l_tmp_access_list, l_qid_list);
855 END IF;
856 END IF;
857
858 log ('END Function mark dirty internal- Accessid-Resourceid - ' ||
859 '1-1 - Single DML');
860
861 RETURN OK;
862 EXCEPTION
863 WHEN OTHERS THEN
864 log(sqlerrm);
865 RAISE ;
866 END mark_dirty_internal;
867
868
869 /** Mark Dirty for a Publication Item
870 * Given a List of access_id and resource_id with a Single DML Type */
871 --ver 5
872 FUNCTION markDirty ( p_pub_item IN VARCHAR2,
873 p_accessList IN access_list,
874 p_resourceList IN user_list,
875 p_dml_type IN CHAR,
876 p_timestamp IN DATE) RETURN BOOLEAN IS
877
878 l_username_list username_list;
879 l_retval BOOLEAN;
880 l_invalid_reslist varchar2(3900);
881 l_ctr number;
882 l_flag number;
883 l_user_name varchar2(100);
884 l_accessList access_list;
885 BEGIN
886 log ('Function markDirty - Accessid-Resourceid - 1-1 - Single DML');
887 l_ctr := 1;
888 l_flag := 0;
889 IF (p_accessList.count <> p_resourceList.count ) THEN
890 RAISE PARAMETER_COUNT_MISMATCH;
891 END IF;
892 FOR i in 1..p_resourceList.count
893 LOOP
894 l_user_name := asg_base.get_user_name(p_resourceList(i));
895 if(l_user_name is null) then
896 if(l_flag = 0 ) then
897 l_invalid_reslist := ''''||p_resourceList(i)||'''';
898 l_flag := 1;
899 else
900 l_invalid_reslist := l_invalid_reslist||','''||p_resourceList(i)||'''';
901 end if;
905 l_ctr := l_ctr + 1;
902 else
903 l_username_list(l_ctr) := l_user_name;
904 l_accessList(l_ctr) := p_accessList(i);
906 end if;
907 l_user_name := null;
908 END LOOP;
909 l_retval:=mark_dirty_internal(p_pub_item, l_accessList, l_username_list,
910 p_dml_type,p_timestamp);
911 if(l_flag = 1 ) then
912 log('Invalid mobile user(s) with resource ID(s) : '||l_invalid_reslist);
913 return FAIL;
914 end if;
915 log ('END Function markDirty- Accessid-Resourceid - 1-1 - Single DML');
916 RETURN OK;
917 EXCEPTION
918 WHEN OTHERS THEN
919 log(sqlerrm);
920 RAISE ;
921 END markDirty;
922
923
924 /** Mark Dirty for a Publication Item
925 * Given a List of access_id and resource_id with a Single DML Type */
926 --ver 6 internal
927 FUNCTION mark_dirty_internal ( p_pub_item IN VARCHAR2,
928 p_accessList IN access_list,
929 p_username_list IN username_list,
930 p_dml_type IN CHAR,
931 p_timestamp IN DATE,
932 p_bulk_flag IN BOOLEAN) RETURN BOOLEAN IS
933
934 l_mobile_user VARCHAR2(100);
935 l_qid_list qid_list;
936 l_rc BOOLEAN;
937
938 l_tmp_access_list access_list;
939 l_empty_access_list access_list;
940 l_empty_qid_list qid_list;
941 l_ctr NUMBER;
942 BEGIN
943 IF (p_bulk_flag = true)
944 THEN
945 log ('Function mark dirty internal - Accessid-Resourceid - Many-Many ');
946 FOR i IN 1..p_username_list.COUNT
947 LOOP
948 -- l_mobile_user := asg_base.get_user_name(p_resourceList(i));
949 l_mobile_user:=p_username_list(i);
950 l_ctr := 1;
951 l_tmp_access_list := l_empty_access_list;
952 l_qid_list := l_empty_qid_list;
953
954 --prune the access ID list for each user and prepare a temp list
955 FOR k IN p_accessList.FIRST..p_accessList.LAST
956 LOOP
957 IF( insert_sdq(p_pub_item,l_mobile_user) AND
958 is_exists(l_mobile_user,p_pub_item,p_accessList(k),p_dml_type))
959 THEN
960 l_tmp_access_list(l_ctr) := p_accessList(k);
961 l_ctr := l_ctr + 1;
962 END IF;
963 END LOOP;
964
965 --FORALL j IN l_tmp_access_list.FIRST..l_tmp_access_list.LAST
966 IF( nvl(l_tmp_access_list.COUNT,0) <> 0 )
967 THEN
968 FORALL j IN 1..l_tmp_access_list.COUNT
969 -- Mark Publication Item Dirty
970 INSERT INTO asg_system_dirty_queue (
971 qid, creation_date, created_by, last_update_date, last_updated_by,
972 pub_item, access_id, client_id, transaction_id
973 , dml_type, download_flag)
974 VALUES (
975 asg_system_dirty_queue_s.nextval, SYSDATE, 1, SYSDATE, 1,
976 p_pub_item, l_tmp_access_list(j), l_mobile_user, NULL ,
977 DECODE(p_dml_type,'D',0,'I',1,'U',2), NULL)
978 RETURNING qid BULK COLLECT INTO l_qid_list;
979 IF (p_dml_type = 'D') THEN
980 l_rc := storeDeletedPK(p_pub_item, l_tmp_access_list, l_qid_list);
981 END IF;
982 END IF;
983 END LOOP;
984
985 ELSE
986 -- l_rc := markDirty (p_pub_item, p_accessList, p_resourceList,
987 -- p_dml_type, p_timestamp);
988 l_rc := mark_dirty_internal(p_pub_item, p_accessList, p_username_list,
989 p_dml_type, p_timestamp);
990
991 END IF;
992
993 log ('END Function mark dirty internal - Accessid-Resourceid - Many-Many ');
994
995 RETURN OK;
996 EXCEPTION
997 WHEN OTHERS THEN
998 log(sqlerrm);
999 RAISE ;
1000 END mark_dirty_internal;
1001
1002 /* Mark Dirty for a Publication Item
1003 * Given a List of access_id and resource_id with a Single DML Type */
1004 --ver 6
1005 FUNCTION markDirty ( p_pub_item IN VARCHAR2,
1006 p_accessList IN access_list,
1007 p_resourceList IN user_list,
1008 p_dml_type IN CHAR,
1009 p_timestamp IN DATE,
1010 p_bulk_flag IN BOOLEAN) RETURN BOOLEAN IS
1011
1012 l_username_list username_list;
1013 l_retval BOOLEAN;
1014 l_invalid_reslist varchar2(3900);
1015 l_ctr number;
1016 l_flag number;
1017 l_user_name varchar2(100);
1018 -- l_accessList access_list;
1019 BEGIN
1020 log ('Function markDirty - Accessid-Resourceid - Many-Many ');
1021 l_ctr := 1;
1022 l_flag := 0;
1023 FOR i in 1..p_resourceList.count
1024 LOOP
1025 l_user_name := asg_base.get_user_name(p_resourceList(i));
1026 if(l_user_name is null) then
1027 if(l_flag = 0 ) then
1028 l_invalid_reslist := ''''||p_resourceList(i)||'''';
1029 l_flag := 1;
1030 else
1031 l_invalid_reslist := l_invalid_reslist||','''||p_resourceList(i)||'''';
1032 end if;
1033 else
1034 l_username_list(l_ctr) := l_user_name;
1035 -- l_accessList(l_ctr) := p_accessList(i);
1036 l_ctr := l_ctr + 1;
1037 end if;
1038 l_user_name := null;
1039 END LOOP;
1040 l_retval:=mark_dirty_internal(p_pub_item,p_accessList,l_username_list,
1041 p_dml_type,p_timestamp,p_bulk_flag);
1042 if(l_flag = 1 ) then
1043 log('Invalid mobile user(s) with resource ID(s) : '||l_invalid_reslist);
1047 RETURN OK;
1044 return fail;
1045 end if;
1046 log ('END Function markDirty - Accessid-Resourceid - Many-Many ');
1048 EXCEPTION
1049 WHEN OTHERS THEN
1050 log(sqlerrm);
1051 RAISE ;
1052 END markDirty;
1053
1054
1055 /* Function to get Primary Key columns and their Data Types
1056 * for a Publication Item */
1057
1058 FUNCTION getPrimaryKeys ( p_pub_item IN VARCHAR2,
1059 x_pk_list OUT NOCOPY VARCHAR2,
1060 x_pk_cnt OUT NOCOPY NUMBER) RETURN BOOLEAN IS
1061
1062 l_pk_list VARCHAR2(4000);
1063 l_base_owner VARCHAR2(30);
1064 l_base_object_name VARCHAR2(30);
1065 l_dml VARCHAR2(4000);
1066 l_col_name VARCHAR2(30);
1067 l_col_data_type VARCHAR2(106);
1068 l_rc NUMBER;
1069 l_pos_1 NUMBER := 0;
1070 l_pos_2 NUMBER := 0;
1071 l_repl_str VARCHAR2(32);
1072 orig_list VARCHAR2(4000);
1073 col_list pk_list;
1074 BEGIN
1075
1076 log('Function getPrimaryKeys ');
1077 EXECUTE IMMEDIATE 'SELECT primary_key_column, base_owner, base_object_name '
1078 || ' FROM ASG_PUB_ITEM WHERE name = :pi '
1079 INTO l_pk_list, l_base_owner, l_base_object_name
1080 USING upper(p_pub_item);
1081
1082 x_pk_cnt := 0;
1083 orig_list:=l_pk_list;
1084 l_pk_list := ''''||replace(l_pk_list, ',', ''',''')||'''';
1085
1086 l_dml := 'SELECT column_name, data_type FROM all_tab_columns '
1087 || ' WHERE owner = :1 AND table_name = :2 '
1088 || ' AND COLUMN_NAME = :3';
1089
1090 col_list := get_listfrom_string(orig_list);
1091
1092 FOR i IN 1..col_list.COUNT
1093 LOOP
1094 EXECUTE IMMEDIATE l_dml into l_col_name,l_col_data_type
1095 USING l_base_owner,l_base_object_name,col_list(i) ;
1096 x_pk_cnt := x_pk_cnt + 1;
1097 IF (l_col_data_type = 'NUMBER') THEN
1098 IF (x_pk_list IS NULL) THEN
1099 x_pk_list := 'to_char('||l_col_name||')';
1100 ELSE
1101 x_pk_list := x_pk_list || ',' || 'to_char('||l_col_name||')';
1102 END IF;
1103
1104 ELSIF (l_col_data_type = 'DATE') THEN
1105 IF (x_pk_list IS NULL) THEN
1106 x_pk_list := 'to_char('||l_col_name||',''dd-mon-yyyy hh24:mi:ss'')';
1107 ELSE
1108 x_pk_list := x_pk_list || ',' ||
1109 'to_char('||l_col_name||',''dd-mon-yyyy hh24:mi:ss'')';
1110 END IF;
1111
1112 ELSIF (l_col_data_type = 'CHAR' OR l_col_data_type = 'VARCHAR2') THEN
1113 IF (x_pk_list IS NULL) THEN
1114 x_pk_list := l_col_name;
1115 ELSE
1116 x_pk_list := x_pk_list || ',' || l_col_name;
1117 END IF;
1118 END IF;
1119 END LOOP;
1120 log('END Function getPrimaryKeys ');
1121 RETURN OK;
1122 EXCEPTION
1123 WHEN OTHERS THEN
1124 log(sqlerrm);
1125 RETURN FAIL;
1126 END getPrimaryKeys;
1127
1128 PROCEDURE find_pubitem_lists (p_online_item_list IN OUT NOCOPY VARCHAR2,
1129 p_complete_ref_pub_items IN OUT NOCOPY VARCHAR2,
1130 p_incr_ref_pub_items IN OUT NOCOPY VARCHAR2,
1131 p_compref_list IN OUT NOCOPY pk_list)
1132 IS
1133 l_cursor_id NUMBER;
1134 l_dml VARCHAR2(32767);
1135 l_pub_item VARCHAR2(30);
1136 l_comp_ref_flag CHAR(1);
1137 l_online_query_flag CHAR(1);
1138 l_rc NUMBER;
1139 l_ctr NUMBER;
1140 BEGIN
1141
1142 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1143
1144 l_dml := 'SELECT a.name, a.comp_ref, b.online_query ' ||
1145 'FROM ' || CONS_SCHEMA || '.c$pub_list_q a, asg_pub_item b ' ||
1146 'WHERE a.name = b.name ' ||
1147 'ORDER by online_query desc, comp_ref desc';
1148
1149 DBMS_SQL.PARSE (l_cursor_id, l_dml, DBMS_SQL.v7);
1150 DBMS_SQL.DEFINE_COLUMN (l_cursor_id, 1, l_pub_item, 30);
1151 DBMS_SQL.DEFINE_COLUMN (l_cursor_id, 2, l_comp_ref_flag, 1);
1152 DBMS_SQL.DEFINE_COLUMN (l_cursor_id, 3, l_online_query_flag, 1);
1153 l_rc := DBMS_SQL.EXECUTE (l_cursor_id);
1154 l_ctr := 1;
1155
1156 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id) > 0 )
1157 LOOP
1158
1159 DBMS_SQL.COLUMN_VALUE (l_cursor_id, 1, l_pub_item);
1160 DBMS_SQL.COLUMN_VALUE (l_cursor_id, 2, l_comp_ref_flag);
1161 DBMS_SQL.COLUMN_VALUE (l_cursor_id, 3, l_online_query_flag);
1162
1163 /* All Pub Items List */
1164 IF (g_pub_items_list IS NULL) THEN
1165 g_pub_items_list := ''''||l_pub_item;
1166 ELSE
1167 g_pub_items_list := g_pub_items_list||''','''
1168 ||l_pub_item;
1169 END IF;
1170 IF(l_online_query_flag = 'Y') THEN
1171 IF (p_online_item_list IS NULL) THEN
1172 p_online_item_list := ''''||l_pub_item;
1173 ELSE
1174 p_online_item_list := p_online_item_list||''','''
1175 ||l_pub_item;
1176 END IF;
1177 log (' Online Pub Item: ' || l_pub_item);
1178 ELSE
1179 /* Remembering Complete Refresh Pub Items */
1180 IF (l_comp_ref_flag = 'Y') THEN
1181 IF (p_complete_ref_pub_items IS NULL) THEN
1182 p_complete_ref_pub_items := ''''||l_pub_item;
1183 ELSE
1187 p_compref_list(l_ctr) := l_pub_item;
1184 p_complete_ref_pub_items := p_complete_ref_pub_items||''','''
1185 ||l_pub_item;
1186 END IF;
1188 l_ctr := l_ctr + 1;
1189 log (' Complete Refresh Pub Item: ' || l_pub_item);
1190 ELSE
1191 /* Remembering incremental refresh Pub Items */
1192 IF (p_incr_ref_pub_items IS NULL) THEN
1193 p_incr_ref_pub_items := ''''||l_pub_item;
1194 ELSE
1195 p_incr_ref_pub_items := p_incr_ref_pub_items||''','''
1196 ||l_pub_item;
1197 END IF;
1198 log (' Incremental Refresh Pub Item: ' || l_pub_item);
1199 END IF;
1200 END IF;
1201 END LOOP;
1202 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
1203
1204 END find_pubitem_lists;
1205
1206
1207 /** Function to Prepare the Out Queue Data */
1208 FUNCTION processSdq ( p_clientid IN VARCHAR2,
1209 p_last_tranid IN NUMBER,
1210 p_curr_tranid IN NUMBER,
1211 p_high_prty IN VARCHAR2,
1212 x_ret_msg OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
1213 l_cursor_id NUMBER;
1214 l_dml VARCHAR2(32767);
1215 l_pub_item VARCHAR2(30);
1216 l_base_owner VARCHAR2(30);
1217 l_base_object VARCHAR2(30);
1218 l_query_access VARCHAR2(1);
1219 l_predicate_list VARCHAR2(150);
1220 l_access_owner VARCHAR2(30);
1221 l_access_name VARCHAR2(30);
1222 l_predicate_clause VARCHAR2(512);
1223 l_complete_ref_pub_items VARCHAR2(32767) := NULL;
1224 l_incr_ref_pub_items VARCHAR2(32767) := NULL;
1225 l_changed_pub_items VARCHAR2(32767) := NULL;
1226 l_webtogo_list VARCHAR2(32767) := NULL;
1227 l_online_item_list VARCHAR2(32767) := NULL;
1228 l_compref_list pk_list;
1229 l_user_name asg_system_dirty_queue.client_id%TYPE;
1230 l_c_client_id asg_system_dirty_queue.client_id%TYPE;
1231 l_c_pub_item asg_system_dirty_queue.pub_item%TYPE;
1232 l_c_access_id asg_system_dirty_queue.access_id%TYPE;
1233 l_c_dml_type asg_system_dirty_queue.dml_type%TYPE;
1234 l_cur_rc NUMBER;
1235 l_rc NUMBER;
1236 l_qid_tmp_list DBMS_SQL.NUMBER_TABLE;
1237 l_tmpqry VARCHAR2(32767);
1238 l_qid_list NUMBER_TABLE;
1239 l_cur NUMBER;
1240 l_qid NUMBER;
1241 l_curid NUMBER;
1242 l_counter NUMBER;
1243
1244 l_complete_ref_pub_items_lst pk_list;
1245 l_changed_pub_items_lst pk_list;
1246 BEGIN
1247
1248 log ('BEGIN Function processSDQ '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1249 reset_all_globals();
1250 x_ret_msg := null;
1251 g_last_tranid := p_last_tranid;
1252 g_clientid := p_clientid;
1253 l_webtogo_list := ''''|| OLITE_SEQUENCE ||'''';
1254
1255 log ('Processing for '||p_clientid);
1256
1257 /* Determine the pub-item list for this synch */
1258 find_pubitem_lists(l_online_item_list, l_complete_ref_pub_items,
1259 l_incr_ref_pub_items, l_compref_list);
1260
1261 IF(g_pub_items_list IS NULL) THEN
1262 -- No pub-items to process!
1263 x_ret_msg := 'Synch disallowed. User not configured to synch from this '||
1264 'device. Please contact your administrator.';
1265 RETURN FAIL;
1266 ELSE
1267 g_pub_items_list := g_pub_items_list || '''';
1268 END IF;
1269
1270 IF ( l_online_item_list IS NOT NULL ) THEN
1271 l_online_item_list := ',' || l_online_item_list || '''';
1272 l_webtogo_list := l_webtogo_list || l_online_item_list;
1273 END IF;
1274
1275 -- Complete Refresh Pub Items List
1276 -- Remove all the entries in sdq for complete refresh pub-items
1277 IF ( l_complete_ref_pub_items IS NOT NULL ) THEN
1278 l_complete_ref_pub_items := l_complete_ref_pub_items || '''';
1279 IF ( l_webtogo_list IS NOT NULL ) THEN
1280 l_webtogo_list := l_webtogo_list||','||l_complete_ref_pub_items;
1281 ELSE
1282 l_webtogo_list := l_complete_ref_pub_items;
1283 END IF;
1284 g_complete_ref_pub_items := l_complete_ref_pub_items;
1285
1286 --Fix For Bug 3075299
1287 l_counter:=1;
1288 /*
1289 l_tmpqry := 'select qid from asg_system_dirty_queue where client_id='''||
1290 p_clientid|| ''' and dml_type=0 and ' ||
1291 ' pub_item in ('||l_complete_ref_pub_items||')';
1292 */
1293 l_tmpqry := 'select qid from asg_system_dirty_queue where '||
1294 'client_id = :1 and dml_type=0 and ' ||
1295 ' pub_item in ('||l_complete_ref_pub_items||')';
1296 l_curid := DBMS_SQL.OPEN_CURSOR;
1297 DBMS_SQL.PARSE(l_curid,l_tmpqry, DBMS_SQL.v7);
1298 DBMS_SQL.bind_variable(l_curid,':1',p_clientid);
1299 DBMS_SQL.DEFINE_COLUMN (l_curid, 1, l_qid);
1300 l_rc :=DBMS_SQL.EXECUTE ( l_curid );
1301 WHILE ( DBMS_SQL.FETCH_ROWS(l_curid) > 0 ) LOOP
1302 DBMS_SQL.COLUMN_VALUE (l_curid, 1, l_qid);
1303 l_qid_tmp_list(l_counter) := l_qid;
1304 l_counter := l_counter+1;
1305 END LOOP;
1306
1307 /*
1308 l_tmpqry := 'delete from asg_system_dirty_queue where client_id= :1 ' ||
1309 ' and pub_item in ('||l_complete_ref_pub_items||')';
1310 EXECUTE IMMEDIATE l_tmpqry
1311 USING p_clientid;
1312 */
1313 l_complete_ref_pub_items_lst := get_listfrom_string(
1317 where client_id = p_clientid
1314 l_complete_ref_pub_items);
1315 forall i in 1..l_complete_ref_pub_items_lst.count
1316 delete from asg_system_dirty_queue
1318 and pub_item = l_complete_ref_pub_items_lst(i);
1319
1320
1321
1322 log(' After Delete SDQ : '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1323 IF (l_qid_tmp_list.COUNT > 0) THEN
1324 l_cur := DBMS_SQL.OPEN_CURSOR;
1325 DBMS_SQL.PARSE (l_cur, 'DELETE FROM asg_delete_queue '
1326 || ' WHERE qid in (:2)', DBMS_SQL.v7);
1327 DBMS_SQL.BIND_ARRAY (l_cur, ':2', l_qid_tmp_list, 1,
1328 l_qid_tmp_list.COUNT);
1329 l_cur_rc := DBMS_SQL.EXECUTE ( l_cur );
1330 DBMS_SQL.CLOSE_CURSOR (l_cur);
1331 END IF;
1332
1333 log(' After Delete delQ : '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1334 END IF;
1335
1336
1337 -- Set up the rows to be downloaded for incremental refresh
1338 IF(l_incr_ref_pub_items IS NOT NULL) THEN
1339 l_incr_ref_pub_items := l_incr_ref_pub_items || '''';
1340 /* Determine pub items dirty since last Sync */
1341 log (' Changed Pub Items List ');
1342
1343 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1344 /*
1345 l_dml := 'SELECT DISTINCT pub_item FROM asg_system_dirty_queue '
1346 || ' WHERE client_id='''||p_clientid
1347 ||''' AND (transaction_id IS NULL '
1348 || ' OR transaction_id > '||p_last_tranid
1349 ||') AND pub_item IN ('||l_incr_ref_pub_items||')';
1350 */
1351 l_dml := 'SELECT DISTINCT pub_item FROM asg_system_dirty_queue '
1352 || ' WHERE client_id= :1 AND (transaction_id IS NULL '
1353 || ' OR transaction_id > :2 ) '
1354 || ' AND pub_item IN ('||l_incr_ref_pub_items||')';
1355
1356 DBMS_SQL.PARSE (l_cursor_id, l_dml, DBMS_SQL.v7);
1357 dbms_sql.bind_variable(l_cursor_id,':1',p_clientid);
1358 dbms_sql.bind_variable(l_cursor_id,':2',p_last_tranid);
1359 DBMS_SQL.DEFINE_COLUMN (l_cursor_id, 1, l_pub_item, 30);
1360 l_rc := DBMS_SQL.EXECUTE (l_cursor_id);
1361
1362 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id) > 0 )
1363 LOOP
1364
1365 DBMS_SQL.COLUMN_VALUE (l_cursor_id, 1, l_pub_item);
1366 log (' '||l_pub_item);
1367 IF ((l_complete_ref_pub_items IS NULL) OR
1368 (INSTR(l_complete_ref_pub_items, l_pub_item) = 0)) THEN
1369 IF ( l_changed_pub_items IS NULL ) THEN
1370 l_changed_pub_items := ''''||l_pub_item;
1371 ELSE
1372 l_changed_pub_items := l_changed_pub_items || ''','''
1373 || l_pub_item;
1374 END IF;
1375 END IF;
1376 END LOOP;
1377 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
1378
1379 -- Changed/Dirty Pub Items List
1380 IF ( l_changed_pub_items IS NOT NULL ) THEN
1381 l_changed_pub_items := l_changed_pub_items || '''';
1382 IF ( l_webtogo_list IS NOT NULL ) THEN
1383 l_webtogo_list := l_webtogo_list || ',' || l_changed_pub_items;
1384 ELSE
1385 l_webtogo_list := l_changed_pub_items;
1386 END IF;
1387
1388 /** Mark ALL records modified since last Successful Sync of Client
1389 * with Curr Tran ID */
1390 log (' Before marking records with Tran id '||p_curr_tranid
1391 ||' '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1392 /*
1393 l_dml := 'UPDATE asg_system_dirty_queue SET '
1394 || ' transaction_id = :1, download_flag=NULL, '
1395 || ' last_update_date = sysdate '
1396 || ' WHERE client_id = :2 AND ( transaction_id IS NULL '
1397 || ' OR transaction_id > :3 ) AND '
1398 || ' pub_item IN ( :4 )';
1399 EXECUTE IMMEDIATE l_dml
1400 USING p_curr_tranid, p_clientid, p_last_tranid,
1401 replace(l_changed_pub_items, '''', '');
1402 */
1403 l_changed_pub_items_lst := get_listfrom_string(l_changed_pub_items);
1404 forall j in 1..l_changed_pub_items_lst.count
1405 update asg_system_dirty_queue
1406 set transaction_id = p_curr_tranid,
1407 download_flag = null,
1408 last_update_date = sysdate
1409 where client_id = p_clientid
1410 and ( transaction_id is null or transaction_id > p_last_tranid )
1411 and pub_item = l_changed_pub_items_lst(j);
1412
1413
1414
1415 log (' After marking record with Tran id '||p_curr_tranid
1416 ||' '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1417
1418 /** Mark RECORDS with the right DML Operation to Send to client */
1419 log (' Before setting download_flag '
1420 ||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1421 UPDATE ASG_SYSTEM_DIRTY_QUEUE
1422 SET download_flag = 'Y'
1423 WHERE qid IN (select qid from ASG_SDQ_UPDATE_V );
1424 log (' After setting download_flag '
1425 ||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1426 END IF;
1427
1428 END IF; /* End for IF (l_incr_ref_pub_items IS NOT NULL) */
1429
1430 /** Insert the complete refresh pubitems into the dirty queue */
1431 IF ( l_compref_list.COUNT > 0 ) THEN
1432 log(' Before Complete Refresh Insert: '||
1433 to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1434 FOR i IN 1..l_compref_list.COUNT
1435 LOOP
1436 SELECT base_owner, base_object_name,
1437 NVL(QUERY_ACCESS_TABLE, 'N'), ACCESS_TABLE_PREDICATE_LIST,
1438 access_owner, access_name
1439 INTO l_base_owner, l_base_object, l_query_access, l_predicate_list,
1440 l_access_owner, l_access_name
1441 FROM asg_pub_item WHERE name=l_compref_list(i);
1445 (l_access_owner IS NULL OR l_access_name IS NULL)) THEN
1442 /* The second condition actually implies an error in seed data but we
1443 tolerate it */
1444 IF ((l_query_access = 'N') OR
1446 l_dml := 'INSERT INTO asg_system_dirty_queue ( ' ||
1447 'qid, creation_date, created_by, last_update_date,' ||
1448 'last_updated_by, pub_item, access_id, client_id, ' ||
1449 'transaction_id, dml_type, download_flag) '||
1450 ' SELECT asg_system_dirty_queue_s.nextval, SYSDATE, 1, ' ||
1451 ' SYSDATE, 1, :1, ' ||
1452 ' uniqpiv.access_id, ' ||
1453 ' :2, :3, ' ||
1454 ' 1, ''Y'' FROM (SELECT DISTINCT ACCESS_ID FROM ' ||
1455 l_base_owner ||'.' ||l_base_object ||
1456 ' ) uniqpiv';
1457
1458 ELSE
1459 l_predicate_clause := get_predicate_clause(l_predicate_list);
1460 l_dml := 'INSERT INTO asg_system_dirty_queue ( ' ||
1461 'qid, creation_date, created_by, last_update_date,' ||
1462 'last_updated_by, pub_item, access_id, client_id, ' ||
1463 'transaction_id, dml_type, download_flag) '||
1464 ' SELECT asg_system_dirty_queue_s.nextval, SYSDATE, 1, ' ||
1465 ' SYSDATE, 1, :1, ' ||
1466 ' uniqacc.access_id, ' ||
1467 ' :2, :3, ' ||
1468 ' 1, ''Y'' FROM (SELECT DISTINCT ACCESS_ID FROM ' ||
1469 l_access_owner || '.' || l_access_name;
1470 -- Null l_predicate_clause means no where condition on access table
1471 IF (l_predicate_clause IS NOT NULL) THEN
1472 l_dml := l_dml || ' WHERE ' || l_predicate_clause ||
1473 ' ) uniqacc';
1474 ELSE
1475 l_dml := l_dml || ' ) uniqacc';
1476 END IF;
1477 END IF;
1478
1479 EXECUTE IMMEDIATE l_dml
1480 USING l_compref_list(i), p_clientid, p_curr_tranid;
1481
1482 END LOOP;
1483 log(' After Complete Refresh Insert: '||
1484 to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1485 END IF;
1486
1487 /** Delete pub items which are not in l_changed_pub_items
1488 * , l_complete_ref_pub_items AND l_online_item_list */
1489 IF ( l_webtogo_list IS NULL ) THEN
1490
1491 l_dml := 'DELETE FROM '||CONS_SCHEMA||'.c$pub_list_q ' ||
1492
1493 'WHERE name in (select name from asg_pub_item)';
1494
1495 ELSE
1496
1497 l_dml := 'DELETE FROM '||CONS_SCHEMA||'.c$pub_list_q '
1498
1499 || ' WHERE name NOT IN (' || l_webtogo_list || ') AND ' ||
1500
1501 ' name in (select name from asg_pub_item)';
1502
1503 END IF;
1504 EXECUTE IMMEDIATE l_dml;
1505
1506 log (' Processing completed for '||p_clientid);
1507 log ('END Function processSDQ '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1508 RETURN OK;
1509 EXCEPTION
1510 WHEN OTHERS THEN
1511 IF ( l_cursor_id <> 0 ) THEN
1512 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
1513 END IF;
1514 log(sqlerrm);
1515 RAISE;
1516 END processSdq;
1517
1518 /** Function to Purge the Out Queue Data */
1519 FUNCTION purgeSdq RETURN BOOLEAN IS
1520 l_ret boolean;
1521 l_prof_value varchar2(5);
1522 BEGIN
1523
1524 log('PurgeSDQ for '||g_clientid||' with '||g_last_tranid ||' : '
1525 || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1526
1527 /* Clean the System Dirty Queue */
1528 IF (isFirstSync) THEN
1529 /*First synch. So no need to raise events*/
1530 DELETE FROM asg_system_dirty_queue
1531 WHERE client_id = g_clientid AND
1532 transaction_id IS NOT NULL;
1533 ELSE
1534 select nvl(fnd_profile.value_specific('ASG_ENABLE_DELIVERY_EVENTS'),'N')
1535 into l_prof_value from dual;
1536 /*Raise delivery events only if the profile is set to 'Y'*/
1537 if(l_prof_value = 'Y')
1538 then
1539 log('Raising data download event');
1540 /* raise data downloaded event for records downloaed during current
1541 synch*/
1542 l_ret := raise_data_downloaded();
1543 log('Raising data downloaded confirmed event');
1544 /*raise download confirmed for records that were successfully downloaded
1545 in the previous synch*/
1546 l_ret := raise_data_download_confirmed();
1547 else
1548 log('Not raising download events since the profile '
1549 ||' ASG_ENABLE_DELIVERY_EVENTS is not set to ''Y''');
1550 end if;
1551 /* Clean the Delete Queue */
1552 DELETE FROM asg_delete_queue
1553 WHERE qid IN (SELECT qid
1554 FROM asg_system_dirty_queue
1555 WHERE client_id = g_clientid AND
1556 transaction_id <= g_last_tranid );
1557
1558 DELETE FROM asg_system_dirty_queue
1559 WHERE client_id = g_clientid AND
1560 transaction_id <= g_last_tranid;
1561 END IF;
1562
1563 log('PurgeSDQ done for '||g_clientid || ' : '
1564 || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1565
1566 RETURN OK;
1567 EXCEPTION
1568 WHEN OTHERS THEN
1569 log(sqlerrm);
1570 RAISE;
1571 END purgeSdq;
1572
1573
1574 /** Function to Purge the Out Queue Data for a User */
1575 FUNCTION purgeSdq (p_clientid VARCHAR2) RETURN BOOLEAN IS
1576 BEGIN
1577
1578 /* Clean the Delete Queue */
1579 log('PurgeSDQ for '||p_clientid);
1580 DELETE FROM asg_delete_queue
1581 WHERE qid IN (SELECT qid
1582 FROM asg_system_dirty_queue
1586 DELETE FROM asg_system_dirty_queue
1583 WHERE client_id = p_clientid);
1584
1585 /* Clean the System Dirty Queue */
1587 WHERE client_id = p_clientid;
1588
1589 log('PurgeSDQ done for '||p_clientid);
1590
1591 RETURN OK;
1592 EXCEPTION
1593 WHEN OTHERS THEN
1594 log(sqlerrm);
1595 RAISE;
1596 END purgeSdq;
1597
1598
1599 /* Log Routine */
1600 PROCEDURE log (p_mesg VARCHAR2 ) IS
1601 BEGIN
1602 IF(asg_helper.check_is_log_enabled(LOG_LEVEL))
1603 THEN
1604 asg_helper.log(p_mesg, 'asg_download', FND_LOG.LEVEL_STATEMENT);
1605 END IF;
1606 END log;
1607
1608
1609
1610 -- to be replaced with asg_base.get_user_name(user_id)
1611 FUNCTION get_username_from_userid ( p_userid IN NUMBER )
1612 RETURN VARCHAR2 IS
1613 CURSOR C_USER_NAME(p_userid NUMBER) IS
1614 SELECT user_name
1615 FROM asg_user
1616 WHERE user_id = p_userid
1617 AND ENABLED ='Y';
1618 l_user_name asg_user.user_name%type;
1619 BEGIN
1620 OPEN C_USER_NAME(p_userid);
1621 FETCH C_USER_NAME into l_user_name;
1622 CLOSE C_USER_NAME;
1623 return l_user_name;
1624 END get_username_from_userid;
1625
1626
1627 --ver 4 mark_dirty
1628 FUNCTION mark_dirty ( p_pub_item IN VARCHAR2,
1629 p_accessList IN access_list,
1630 p_userid_list IN user_list,
1631 p_dmlList IN dml_list,
1632 p_timestamp IN DATE) RETURN BOOLEAN IS
1633
1634 l_username_list username_list;
1635 l_accessList access_list;
1636 l_retval BOOLEAN;
1637 l_user_name asg_user.user_name%type;
1638 l_dmlList dml_list;
1639 l_ctr NUMBER;
1640 BEGIN
1641 IF ( (p_accessList.count <> p_userid_list.count ) OR
1642 (p_accessList.count <> p_dmlList.count) ) THEN
1643 RAISE PARAMETER_COUNT_MISMATCH;
1644 END IF;
1645 l_ctr := 1;
1646 FOR i in 1..p_userid_list.count
1647 LOOP
1648 l_user_name := get_username_from_userid(p_userid_list(i));
1649 IF l_user_name IS NOT NULL THEN
1650 l_username_list(l_ctr) := l_user_name;
1651 l_accessList(l_ctr) := p_accessList(i);
1652 l_dmlList(l_ctr) := p_dmlList(i);
1653 l_ctr := l_ctr + 1; --increment counter for the new list
1654 END IF;
1655 l_user_name := NULL;
1656 END LOOP;
1657 --only if valid rows are present do call mark dirty internal
1658 IF l_accessList.COUNT > 0 THEN
1659 l_retval:=mark_dirty_internal(p_pub_item,l_accessList,l_username_list,
1660 l_dmlList,p_timestamp);
1661 END IF;
1662
1663 RETURN OK;
1664 EXCEPTION
1665 WHEN OTHERS THEN
1666 log(sqlerrm);
1667 RAISE;
1668 END mark_dirty;
1669
1670 --ver 5 mark_dirty
1671 FUNCTION mark_dirty ( p_pub_item IN VARCHAR2,
1672 p_accessList IN access_list,
1673 p_userid_list IN user_list,
1674 p_dml_type IN CHAR,
1675 p_timestamp IN DATE) RETURN BOOLEAN IS
1676 l_username_list username_list;
1677 l_retval BOOLEAN;
1678 l_accessList access_list;
1679 l_user_name asg_user.user_name%type;
1680 l_ctr NUMBER;
1681 BEGIN
1682 log ('Function markDirty - Accessid-Resourceid - 1-1 - Single DML');
1683 IF (p_accessList.count <> p_userid_list.count ) THEN
1684 RAISE PARAMETER_COUNT_MISMATCH;
1685 END IF;
1686 l_ctr := 1;
1687 FOR i in 1..p_userid_list.count
1688 LOOP
1689 l_user_name := get_username_from_userid(p_userid_list(i));
1690
1691 IF l_user_name IS NOT NULL THEN
1692 l_username_list(l_ctr) := l_user_name;
1693 l_accessList(l_ctr) := p_accessList(i);
1694 l_ctr := l_ctr + 1; --increment counter for the new list
1695 END IF;
1696 l_user_name := NULL;
1697 END LOOP;
1698 --only if valid rows are present do call mark dirty internal
1699 IF l_accessList.COUNT > 0 THEN
1700 l_retval:=mark_dirty_internal(p_pub_item,l_accessList,l_username_list,
1701 p_dml_type,p_timestamp);
1702 END IF;
1703 log ('END Function markDirty- Accessid-Resourceid - 1-1 - Single DML');
1704 RETURN OK;
1705 EXCEPTION
1706 WHEN OTHERS THEN
1707 log(sqlerrm);
1708 RAISE ;
1709 END mark_dirty;
1710
1711 --ver 2 mark_dirty
1712 FUNCTION mark_dirty ( p_pub_item IN VARCHAR2,
1713 p_accessid IN NUMBER,
1714 p_userid IN NUMBER,
1715 p_dml IN CHAR,
1716 p_timestamp IN DATE )RETURN BOOLEAN IS
1717
1718 l_mobile_user VARCHAR2(100);
1719 l_retval BOOLEAN;
1720 BEGIN
1721 log ('Function markDirty - single row');
1722 l_mobile_user := get_username_from_userid(p_userid);
1723 --Do markdirty only if valid username is present
1724 IF l_mobile_user IS NOT NULL THEN
1725 l_retval:=mark_dirty_internal(p_pub_item,p_accessid,l_mobile_user,
1726 p_dml,p_timestamp);
1727 END IF;
1728
1729 RETURN OK;
1730 EXCEPTION
1731 WHEN OTHERS THEN
1732 log(sqlerrm);
1733 RAISE;
1734 END mark_dirty;
1735
1736
1737 --ver 3 mark_dirty
1738 FUNCTION mark_dirty ( p_pub_item IN VARCHAR2,
1742 p_timestamp IN DATE,
1739 p_accessid IN NUMBER,
1740 p_userid IN NUMBER,
1741 p_dml IN CHAR,
1743 p_pkvalues IN pk_list ) RETURN BOOLEAN IS
1744 l_mobile_user VARCHAR2(100);
1745 l_retval BOOLEAN;
1746 BEGIN
1747 log ('Function markDirty - single row with PK Values given');
1748 l_mobile_user := get_username_from_userid(p_userid);
1749 --Do markdirty only if valid username is present
1750 IF l_mobile_user IS NOT NULL THEN
1751 l_retval:=mark_dirty_internal(p_pub_item,p_accessid,l_mobile_user,
1752 p_dml,p_timestamp,p_pkvalues);
1753 END IF;
1754
1755 RETURN OK;
1756 EXCEPTION
1757 WHEN OTHERS THEN
1758 log(sqlerrm);
1759 RAISE;
1760 END mark_dirty;
1761
1762 -- ver 6 mark_dirty
1763 FUNCTION mark_dirty ( p_pub_item IN VARCHAR2,
1764 p_accessList IN access_list,
1765 p_userid_list IN user_list,
1766 p_dml_type IN CHAR,
1767 p_timestamp IN DATE,
1768 p_bulk_flag IN BOOLEAN) RETURN BOOLEAN IS
1769 l_username_list username_list;
1770 l_retval BOOLEAN;
1771 l_user_name asg_user.user_name%type;
1772 l_ctr NUMBER;
1773
1774 BEGIN
1775 log ('Function markDirty - Accessid-Resourceid - Many-Many ');
1776 l_ctr :=1;
1777 FOR i in 1..p_userid_list.count
1778 LOOP
1779 l_user_name := get_username_from_userid(p_userid_list(i));
1780
1781 IF l_user_name IS NOT NULL THEN
1782 l_username_list(l_ctr) := l_user_name;
1783 l_ctr := l_ctr + 1; --increment counter for the new list
1784 END IF;
1785 l_user_name := NULL;
1786
1787 END LOOP;
1788 --only if valid rows are present do call mark dirty internal
1789 IF p_accessList.COUNT > 0 THEN
1790 l_retval:=mark_dirty_internal(p_pub_item,p_accessList,l_username_list,
1791 p_dml_type,p_timestamp,p_bulk_flag);
1792 END IF;
1793 log ('END Function markDirty - Accessid-Resourceid - Many-Many ');
1794 RETURN OK;
1795 EXCEPTION
1796 WHEN OTHERS THEN
1797 log(sqlerrm);
1798 RAISE ;
1799 END mark_dirty;
1800
1801 PROCEDURE log_concprogram(l_msg VARCHAR2 ,l_mod varchar2,l_level number)
1802 is
1803 begin
1804 asg_helper.log(l_msg, l_mod,l_level);
1805 IF(g_purge_log_enabled IS null)
1806 then
1807 begin
1808 SELECT nvl(value,'N') INTO g_purge_log_enabled
1809 FROM asg_config WHERE name='ENABLE_PURGE_LOGGING';
1810 exception
1811 when no_data_found
1812 then
1813 g_purge_log_enabled := 'N';
1814 end;
1815 END IF;
1816 IF(g_purge_log_enabled = 'Y')
1817 THEN
1818 fnd_file.put_line(fnd_file.log,l_msg);
1819 END IF;
1820 END log_concprogram;
1821
1822 /*
1823 takes a user name and deletes all duplicate records for that user
1824 from SDQ and DQ
1825 */
1826 PROCEDURE delete_duplicate_records(l_user_name varchar2)
1827 is
1828 l_dml VARCHAR2(1000);
1829 l_count NUMBER;
1830 PRAGMA AUTONOMOUS_TRANSACTION;
1831 begin
1832 -- delete from asg_delete_queue
1833 l_dml := 'Delete /*+ INDEX(asg_delete_queue ASG_DELETE_QUEUE_U1) */ from '||
1834 ' asg_delete_queue where qid in '||
1835 ' ( Select qid from '||
1836 ' ( select qid, pub_item, access_id, dml_type, '||
1837 ' count(*) over (partition by pub_item, access_id, dml_type)'||
1838 ' as total_rows, '||
1839 ' min(qid) over (partition by pub_item, access_id, dml_type)'||
1840 ' as min_qid '||
1841 ' from asg_system_dirty_queue where client_id = :1 AND '||
1842 ' TRANSACTION_ID IS NULL and download_flag is null '||
1843 ' ) '||
1844 ' where qid <> min_qid and total_rows >1 )';
1845 EXECUTE IMMEDIATE l_dml USING l_user_name;
1846 l_count := SQL%ROWCOUNT;
1847 /* log_concprogram('Removed '||l_count||' duplicate rows from Delete queue '||
1848 ' for user : '||l_user_name,
1849 'asg_download',
1850 FND_LOG.LEVEL_STATEMENT);*/
1851 -- delete from asg_system_dirty_queue
1852 l_dml := 'Delete /*+ INDEX(asg_system_dirty_queue ASG_SYSTEM_DIRTY_QUEUE_U1) */ from asg_system_dirty_queue where qid in '||
1853 ' ( Select qid from '||
1854 ' ( select qid, pub_item, access_id, dml_type, '||
1855 ' count(*) over (partition by pub_item, access_id, dml_type)'||
1856 ' as total_rows, '||
1857 ' min(qid) over (partition by pub_item, access_id, dml_type)'||
1858 ' as min_qid '||
1859 ' from asg_system_dirty_queue where client_id = :1 AND '||
1860 ' TRANSACTION_ID IS NULL and download_flag is null '||
1861 ' ) '||
1862 ' where qid <> min_qid and total_rows >1 )';
1863 EXECUTE IMMEDIATE l_dml USING l_user_name;
1864 l_count := SQL%ROWCOUNT;
1865 /*log_concprogram('Removed '||l_count||' duplicate rows from Dirty queue '||
1866 ' for user : '||l_user_name,
1867 'asg_download',
1868 FND_LOG.LEVEL_STATEMENT);*/
1869 commit;
1870 EXCEPTION
1871 WHEN OTHERS THEN
1872 /*log_concprogram('Error deleting duplicate record from SDQ for '||
1873 l_user_name||' : '||SQLERRM,
1874 'asg_download',FND_LOG.LEVEL_UNEXPECTED);*/
1875 rollback;
1876 -- raise;
1877 END delete_duplicate_records;
1878
1879 /*
1883 PROCEDURE set_user_first_synch(l_user_name varchar2)
1880 takes a user name and inserts records for each publication subscribed
1881 by the user into asg_purge_sdq
1882 */
1884 is
1885 PRAGMA autonomous_transaction;
1886 BEGIN
1887 log_concprogram('Setting user '||l_user_name ||' to first synch '||
1888 l_user_name,'asg_download',
1889 FND_LOG.LEVEL_STATEMENT);
1890
1891 INSERT INTO asg_purge_sdq(user_name,pub_name,creation_date,created_by,
1892 last_update_date,last_updated_by )
1893 ( SELECT user_name,pub_name ,sysdate,1,sysdate,1
1894 FROM asg_user_pub_resps
1895 WHERE user_name = l_user_name
1896 AND pub_name IN
1897 (select name from asg_pub where nvl(custom,'N') = 'N' )
1898 );
1899 commit;
1900 log_concprogram('Done setting user '||l_user_name ||' to first synch '||
1901 l_user_name,'asg_download',
1902 FND_LOG.LEVEL_STATEMENT);
1903 EXCEPTION
1904 WHEN OTHERS then
1905 log_concprogram('Error setting user '||l_user_name||' to first synch: '||
1906 SQLERRM,'asg_download',
1907 FND_LOG.LEVEL_UNEXPECTED);
1908 rollback;
1909 raise;
1910 END set_user_first_synch;
1911
1912 /*
1913 takes a user name and inserts records for each publication subscribed
1914 by the user into asg_purge_sdq
1915 */
1916 PROCEDURE set_user_first_synch_pub(l_user_name varchar2,l_pub_name varchar2)
1917 is
1918 PRAGMA autonomous_transaction;
1919 l_count NUMBER;
1920 BEGIN
1921 /*log_concprogram('Setting user '||l_user_name ||' to first synch '||
1922 l_user_name,'asg_download',
1923 FND_LOG.LEVEL_STATEMENT);*/
1924 SELECT COUNT(*) INTO l_count
1925 FROM asg_purge_sdq
1926 WHERE user_name = l_user_name
1927 AND pub_name = l_pub_name;
1928
1929 IF l_count =0 THEN
1930 INSERT INTO asg_purge_sdq(user_name,pub_name,creation_date,created_by,
1931 last_update_date,last_updated_by)
1932 VALUES (l_user_name,l_pub_name,sysdate,1,sysdate,1);
1933 commit;
1934 END IF;
1935 /*log_concprogram('Done setting user '||l_user_name ||' to first synch '||
1936 l_user_name,'asg_download',
1937 FND_LOG.LEVEL_STATEMENT);*/
1938 EXCEPTION
1939 WHEN OTHERS THEN
1940 /*log_concprogram('Error setting user '||l_user_name||' to first synch: '||
1941 SQLERRM,'asg_download',
1942 FND_LOG.LEVEL_UNEXPECTED);*/
1943 ROLLBACK;
1944 --raise;
1945 END set_user_first_synch_pub;
1946
1947
1948 -- to be used by the JTM master concurrent program.
1949 -- Deletes the rows in asg_system_dirty_queue for all the dormant users.
1950 -- Forces these users to do a complete refresh.
1951 PROCEDURE delete_Sdq( P_status OUT NOCOPY VARCHAR2,
1952 P_message OUT NOCOPY VARCHAR2)
1953 IS
1954
1955 l_user_id NUMBER;
1956 l_user_name VARCHAR2(100);
1957 l_days NUMBER;
1958 l_temp NUMBER;
1959 l_profileValue NUMBER;
1960
1961 CURSOR c_dormant_users(l_dormancy_period NUMBER,
1962 l_last_processed VARCHAR2,l_max_num NUMBER,
1963 l_last_user varchar2)
1964 IS
1965 SELECT user_name,pub_name
1966 FROM asg_user_pub_resps
1967 WHERE trunc( sysdate - NVL(synch_date,to_date('1', 'J')) )
1968 > l_dormancy_period
1969 AND pub_name IN ( SELECT NAME FROM asg_pub WHERE nvl(custom,'N') = 'N' )
1970 and user_name > l_last_processed
1971 and user_name <=l_last_user
1972 ORDER BY user_name;
1973
1974 l_dormant_rec c_dormant_users%ROWTYPE;
1975
1976 CURSOR c_last_processed_user
1977 IS
1978 SELECT value FROM asg_config
1979 WHERE name='ASG_SDQ_PURGE_LAST_USER';
1980
1981 CURSOR c_last_processed_dupdel_user
1982 IS
1983 SELECT value FROM asg_config
1984 WHERE NAME='ASG_SDQ_PURGE_LAST_DUPDEL';
1985
1986 CURSOR c_all_users(l_last_processed VARCHAR2,l_max_num NUMBER)
1987 IS
1988 SELECT user_name FROM
1989 (
1990 SELECT user_name FROM asg_user
1991 WHERE user_name > l_last_processed
1992 and user_name not in
1993 ( select distinct user_name
1994 from asg_purge_sdq where TRANSACTION_ID IS NULL )
1995 ORDER BY user_name
1996 ) WHERE ROWNUM <= l_max_num;
1997
1998 CURSOR c_get_last_user(l_dormancy_period NUMBER,
1999 l_last_processed VARCHAR2,l_num_users number)
2000 IS
2001 SELECT user_name FROM (
2002 SELECT ROWNUM pos,user_name FROM
2003 (
2004 SELECT DISTINCT user_name
2005 FROM asg_user_pub_resps
2006 WHERE user_name > l_last_processed
2007 AND TRUNC( SYSDATE - NVL(synch_date,TO_DATE('1', 'J')) )
2008 > l_dormancy_period
2009 ORDER BY user_name
2010 )
2011 ) WHERE pos = l_num_users;
2012
2013 CURSOR c_get_count(l_dormancy_period NUMBER,
2014 l_last_processed VARCHAR2)
2015 IS
2016 SELECT COUNT(*) FROM (
2017 SELECT ROWNUM pos,user_name FROM
2018 (
2019 SELECT DISTINCT user_name
2020 FROM asg_user_pub_resps
2021 WHERE user_name > l_last_processed
2022 AND TRUNC( SYSDATE - NVL(synch_date,TO_DATE('1', 'J')) )
2023 > l_dormancy_period
2024 ORDER BY user_name
2025 ) );
2026
2027
2028 l_last_processed_user VARCHAR2(100);
2029 l_dormancy_period NUMBER;
2030 l_max_users NUMBER;
2031 l_tmp_user VARCHAR2(100);
2032 l_summary VARCHAR2(4000);
2036 l_final_user VARCHAR2(100);
2033 l_dml VARCHAR2(1000);
2034 l_last_user VARCHAR2(100);
2035 l_last_dupdel_user VARCHAR2(100);
2037 l_pub_name VARCHAR2(30);
2038 l_purge_conf_interval NUMBER := 30;
2039 l_date date;
2040 l_total NUMBER;
2041 BEGIN
2042 log_concprogram('Start SDQ purge ','asg_download',
2043 FND_LOG.LEVEL_STATEMENT);
2044 l_final_user := null;
2045
2046 -- Fix to 3536657
2047 -- Later, allow customer to specify the interval via a separate
2048 -- profile option
2049 l_dormancy_period := fnd_profile.VALUE('ASG_SDQ_PURGE_DAYS');
2050 if(l_dormancy_period is not null) then
2051 l_purge_conf_interval := l_dormancy_period;
2052 end if;
2053 delete from asg_conf_info
2054 where (sysdate-creation_date) >l_purge_conf_interval;
2055 commit;
2056
2057 l_max_users :=
2058 fnd_profile.VALUE('ASG_SDQ_PURGE_USER_COUNT');
2059 if(l_max_users is null )
2060 then
2061 log_concprogram('Profile ASG: Purge User Count is set to null. Exiting',
2062 'asg_download',FND_LOG.LEVEL_STATEMENT);
2063 log_concprogram('Please set the profile ASG: Purge User Count to a '||
2064 'non-null value and resubmit the concurrent program',
2065 'asg_download',FND_LOG.LEVEL_STATEMENT);
2066 P_status := 'Warning';
2067 p_message := 'Profile ASG: Purge User Count is set to null';
2068 return;
2069 end if;
2070 log_concprogram('Number of users to be processed : '||l_max_users,
2071 'asg_download',FND_LOG.LEVEL_STATEMENT);
2072
2073 if(l_dormancy_period is null )
2074 then
2075 log_concprogram('Profile ASG: Dormancy Period is set to null. Exiting',
2076 'asg_download',FND_LOG.LEVEL_STATEMENT);
2077 log_concprogram('Please set the profile ASG: Dormancy Period to a '||
2078 'non-null value and resubmit the concurrent program',
2079 'asg_download',FND_LOG.LEVEL_STATEMENT);
2080 P_status := 'Warning';
2081 p_message := 'Profile ASG: Dormancy Period is set to null';
2082 return;
2083 end if;
2084 log_concprogram('Dormancy period : '||l_dormancy_period,
2085 'asg_download',FND_LOG.LEVEL_STATEMENT);
2086
2087
2088 OPEN c_last_processed_dupdel_user;
2089 FETCH c_last_processed_dupdel_user INTO l_last_dupdel_user;
2090 CLOSE c_last_processed_dupdel_user;
2091
2092 IF( l_last_dupdel_user IS NULL )
2093 THEN
2094 l_last_dupdel_user := to_char(0);
2095 ELSE
2096 --chk whether the last user is hit
2097 OPEN c_all_users(l_last_dupdel_user,l_max_users);
2098 FETCH c_all_users INTO l_user_name;
2099 IF(c_all_users%NOTFOUND) --we have hit last user.so reset to to_char(0);
2100 THEN
2101 l_last_dupdel_user := to_char(0);
2102 log_concprogram('Last user hit for deleting duplicates..'||
2103 'starting from first user',
2104 'asg_download',FND_LOG.LEVEL_STATEMENT);
2105
2106 END if;
2107 CLOSE c_all_users;
2108 END IF;
2109 --delete duplicate records to start off...
2110 --loop thru all users in asg_user and delete duplicate records.
2111 l_last_user := null;
2112 SELECT SYSDATE INTO l_date FROM dual;
2113 log_concprogram('Starting to delete duplicate records : '||
2114 to_char(l_date,'dd-mon-yyyy hh24:mi:ss'),
2115 'asg_download',FND_LOG.LEVEL_STATEMENT);
2116 OPEN c_all_users(l_last_dupdel_user,l_max_users);
2117 LOOP
2118 FETCH c_all_users INTO l_user_name;
2119 EXIT WHEN c_all_users%NOTFOUND;
2120 l_last_user := l_user_name;
2121 SELECT SYSDATE INTO l_date FROM dual;
2122 log_concprogram('Deleting duplicate records for '||l_user_name||' : '||
2123 to_char(l_date,'dd-mon-yyyy hh24:mi:ss'),
2124 'asg_download',FND_LOG.LEVEL_STATEMENT);
2125 delete_duplicate_records(l_user_name);
2126 END loop;
2127 CLOSE c_all_users;
2128
2129 UPDATE asg_config SET value=nvl(l_last_user,value)
2130 WHERE NAME='ASG_SDQ_PURGE_LAST_DUPDEL';
2131 COMMIT;
2132
2133 SELECT SYSDATE INTO l_date FROM dual;
2134 log_concprogram('End of deleting duplicate records'||' : '||
2135 to_char(l_date,'dd-mon-yyyy hh24:mi:ss'),
2136 'asg_download',FND_LOG.LEVEL_STATEMENT);
2137
2138 OPEN c_last_processed_user();
2139 FETCH c_last_processed_user INTO l_last_processed_user;
2140 CLOSE c_last_processed_user;
2141
2142 IF(l_last_processed_user = NULL )
2143 THEN
2144 l_last_processed_user := to_char(0);
2145 ELSE
2146 --check whether there are any more users to be processed
2147 --after l_last_processed_user ...
2148 --if no reset value of asg_config parameter to null
2149 --set l_last_processed_user to to_char(0)
2150 OPEN c_get_last_user(l_dormancy_period,l_last_processed_user,1);
2151 FETCH c_get_last_user INTO l_tmp_user;
2152 IF(c_get_last_user%NOTFOUND) --we have hit last user.so reset to to_char(0)
2153 THEN
2154 l_last_processed_user := to_char(0);
2155 log_concprogram('Last user hit..starting from first user',
2156 'asg_download',FND_LOG.LEVEL_STATEMENT);
2157
2158 END if;
2159 CLOSE c_get_last_user;
2160 END IF;
2161
2162 log_concprogram('Last User to be processed in previous run : '||
2163 l_last_processed_user,
2164 'asg_download',FND_LOG.LEVEL_STATEMENT);
2165
2166 -- get last user
2170 THEN
2167 OPEN c_get_last_user(l_dormancy_period,l_last_processed_user,l_max_users);
2168 FETCH c_get_last_user INTO l_final_user;
2169 IF(c_get_last_user%NOTFOUND)
2171 l_final_user := to_char(0);
2172 END IF;
2173 CLOSE c_get_last_user;
2174
2175 IF(l_final_user = to_char(0) )
2176 THEN
2177 OPEN c_get_count(l_dormancy_period,l_last_processed_user);
2178 FETCH c_get_count INTO l_total;
2179 CLOSE c_get_count;
2180 log_concprogram('Processing : '||l_total||' users',
2181 'asg_download',FND_LOG.LEVEL_STATEMENT);
2182
2183 OPEN c_get_last_user(l_dormancy_period,l_last_processed_user,l_total);
2184 FETCH c_get_last_user INTO l_final_user;
2185 CLOSE c_get_last_user;
2186 END IF;
2187
2188 log_concprogram('Final user processed in this run : '||l_final_user,
2189 'asg_download',FND_LOG.LEVEL_STATEMENT);
2190
2191 l_tmp_user := null;
2192 l_last_user := null;
2193 OPEN c_dormant_users(l_dormancy_period,l_last_processed_user,
2194 l_max_users,l_final_user);
2195 LOOP
2196 FETCH c_dormant_users INTO l_dormant_rec;
2197 EXIT WHEN c_dormant_users%NOTFOUND;
2198 l_last_user := l_dormant_rec.user_name;
2199 log_concprogram('Purging SDQ for dormant user : '||
2200 l_dormant_rec.user_name||' and publication :'||
2201 l_dormant_rec.pub_name,
2202 'asg_download',FND_LOG.LEVEL_STATEMENT);
2203 BEGIN
2204 DELETE /*+ INDEX(asg_delete_queue ASG_DELETE_QUEUE_U1) */ FROM
2205 asg_delete_queue
2206 WHERE qid IN
2207 ( SELECT qid
2208 FROM asg_system_dirty_queue
2209 WHERE client_id = l_dormant_rec.user_name
2210 AND pub_item in
2211 (SELECT item_id FROM asg_pub_item
2212 WHERE pub_name=l_dormant_rec.pub_name));
2213
2214 log_concprogram('Done deleting DQ for '||l_tmp_user||' '||
2215 SQL%ROWCOUNT||' rows',
2216 'asg_download',FND_LOG.LEVEL_STATEMENT);
2217
2218
2219 DELETE FROM asg_system_dirty_queue
2220 WHERE client_id = l_dormant_rec.user_name
2221 AND pub_item in
2222 (SELECT item_id FROM asg_pub_item
2223 WHERE pub_name=l_dormant_rec.pub_name);
2224 log_concprogram('Done deleting SDQ for '||l_tmp_user||' '||
2225 SQL%ROWCOUNT||' rows',
2226 'asg_download',FND_LOG.LEVEL_STATEMENT);
2227
2228 DELETE FROM asg_complete_refresh
2229 WHERE user_name = l_dormant_rec.user_name
2230 AND publication_item IN
2231 (SELECT item_id FROM asg_pub_item
2232 WHERE pub_name = l_dormant_rec.pub_name);
2233
2234 DELETE FROM asg_purge_sdq
2235 WHERE user_name = l_dormant_rec.user_name
2236 AND pub_name = l_dormant_rec.pub_name;
2237 commit;
2238 /* change this .. has to insert for current publicatino..*/
2239 set_user_first_synch_pub(l_dormant_rec.user_name,
2240 l_dormant_rec.pub_name);
2241 END;
2242 -- delete SDQ and DQ , asg_complete_refresh , asg_purge_sdq and commit.
2243 -- set to complete ref in asg_purge_sdq..
2244 END LOOP;
2245 CLOSE c_dormant_users;
2246
2247 log_concprogram('Last user to be processed in this run : '||l_last_user,
2248 'asg_download',FND_LOG.LEVEL_STATEMENT);
2249
2250 UPDATE asg_config
2251 SET value = nvl(l_last_user,value)
2252 WHERE NAME ='ASG_SDQ_PURGE_LAST_USER';
2253 commit;
2254
2255 UPDATE jtm_con_request_data
2256 SET last_run_date = SYSDATE
2257 WHERE package_name = 'ASG_DOWNLOAD'
2258 AND procedure_name = 'DELETE_SDQ';
2259 COMMIT;
2260
2261 log_concprogram('End SDQ purge ','asg_download',
2262 FND_LOG.LEVEL_STATEMENT);
2263 p_status := 'Fine';
2264 p_message := 'Purging asg_system_dirty_queue and asg_delete_queue completed successfully.';
2265
2266 EXCEPTION
2267 WHEN OTHERS THEN
2268 p_status := 'Error';
2269 p_message := 'Exception in purge :'||SQLERRM;
2270 log_concprogram('Exception in purge :'||SQLERRM,
2271 'asg_download',FND_LOG.LEVEL_UNEXPECTED);
2272 END delete_Sdq;
2273
2274 /*
2275 function to verify whether record shd be inserted into SDQ or not
2276 return values :
2277 If it returns "false" then the record need not be inserted into SDQ ..
2278 it may be that:
2279 1. records for the pub item or the corresponding publication
2280 exists in asg_complete_refresh or asg_purge_sdq
2281 If the return value is "true" then record is inserted.
2282 --
2283 */
2284
2285 FUNCTION insert_sdq(p_pub_item varchar2,p_user_name varchar2) RETURN boolean
2286 IS
2287 CURSOR c_pub_name(l_pub_item varchar2)
2288 IS
2289 SELECT pub_name FROM asg_pub_item WHERE item_id = p_pub_item;
2290 CURSOR c_exists_compref(l_pi varchar2,l_un varchar2)
2291 IS
2292 SELECT user_name FROM asg_complete_refresh
2293 WHERE user_name = l_un AND publication_item = l_pi
2294 AND synch_completed = 'N';
2295 CURSOR c_exists_purge(l_pub varchar2,l_un varchar2)
2296 IS
2297 SELECT user_name FROM asg_purge_Sdq
2298 WHERE user_name = l_un
2299 AND pub_name = l_pub
2300 AND transaction_id IS null;
2301 l_pub_name varchar2(30);
2302 l_tmp varchar2(100);
2303
2304 BEGIN
2305 OPEN c_exists_compref(p_pub_item,p_user_name);
2306 FETCH c_exists_compref INTO l_tmp;
2307 IF(c_exists_compref%FOUND)
2308 THEN
2309 CLOSE c_exists_compref;
2310 RETURN false;
2311 END IF;
2312 CLOSE c_exists_compref;
2313
2314 OPEN c_pub_name(p_pub_item);
2315 FETCH c_pub_name INTO l_pub_name;
2316 CLOSE c_pub_name;
2317
2318 OPEN c_exists_purge(l_pub_name,p_user_name);
2322 CLOSE c_exists_purge;
2319 FETCH c_exists_purge INTO l_tmp;
2320 IF(c_exists_purge%FOUND)
2321 THEN
2323 RETURN false;
2324 END IF;
2325 CLOSE c_exists_purge;
2326 -- we have reached here.. so record can be inserted into SDQ
2327 RETURN TRUE;
2328 END insert_sdq;
2329
2330
2331 /*
2332 checks whether the record exists in SDQ
2333 return value:
2334 if record exists then "false" - so need not be inserted again
2335 if record doesn't exist then "true" - insert into SDQ
2336 */
2337 FUNCTION is_exists(p_clientid varchar2, p_pub_item varchar2,
2338 p_access_id number,p_dml_type char)
2339 RETURN boolean
2340 IS
2341 CURSOR c_is_exists_in_sdq(p_clientid varchar2, p_pub_item varchar2,
2342 p_access_id number,p_dml_type varchar2)
2343 IS
2344 SELECT client_id FROM asg_system_dirty_queue
2345 WHERE client_id = p_clientid AND pub_item = p_pub_item
2346 AND access_id = p_access_id
2347 AND dml_type = DECODE(p_dml_type,'D',0,'I',1,'U',2)
2348 AND transaction_id IS NULL AND download_flag IS null;
2349 l_tmp_user varchar2(100);
2350 BEGIN
2351 OPEN c_is_exists_in_sdq(p_clientid,p_pub_item,p_access_id,p_dml_type);
2352 FETCH c_is_exists_in_sdq INTO l_tmp_user;
2353 if(c_is_exists_in_sdq%FOUND)
2354 THEN
2355 CLOSE c_is_exists_in_sdq;
2356 RETURN false;
2357 END IF;
2358 CLOSE c_is_exists_in_sdq;
2359 RETURN true;
2360 END is_exists;
2361
2362
2363 PROCEDURE delete_synch_history( P_status OUT NOCOPY VARCHAR2,
2364 P_message OUT NOCOPY VARCHAR2)
2365 IS
2366 l_purge_interval NUMBER ;
2367 l_qry varchar2(4000);
2368 l_row_count number;
2369 l_purge_session_data session_id_list;
2370 c_hist c_purge_session;
2371 BEGIN
2372 log_concprogram('Starting to purge synch history data',
2373 'asg_download',
2374 FND_LOG.LEVEL_STATEMENT);
2375 l_purge_interval := fnd_profile.VALUE('ASG_SYNCH_HIST_PURGE_PERIOD');
2376 if( l_purge_interval is null )
2377 then
2378 log_concprogram('Synch history purge interval is set to NULL ',
2379 'asg_download',
2380 FND_LOG.LEVEL_STATEMENT);
2381 log_concprogram('Please set profile ASG:Synch Histoy Purge Period to '||
2382 'a non-null value and resubmit the concurrent program',
2383 'asg_download',
2384 FND_LOG.LEVEL_STATEMENT);
2385 P_status := 'Warning';
2386 p_message := 'Profile ASG:Synch Histoy Purge Period is set to null';
2387 return;
2388 else
2389 log_concprogram('Synch history purge interval : '||l_purge_interval,
2390 'asg_download',
2391 FND_LOG.LEVEL_STATEMENT);
2392
2393 /*l_qry := 'delete from '||CONS_SCHEMA||'.'||'c$sync_history where '
2394 ||' (sysdate-start_time) > '||l_purge_interval||' ';
2395 log_concprogram('Query : '||l_qry,
2396 'asg_download',
2397 FND_LOG.LEVEL_STATEMENT);
2398 EXECUTE IMMEDIATE l_qry;
2399 l_row_count := SQL%ROWCOUNT;
2400 log_concprogram('Deleted '||l_row_count||' row(s)',
2401 'asg_download',
2402 FND_LOG.LEVEL_STATEMENT);
2403 COMMIT;
2404 */
2405
2406 l_row_count := 0;
2407 l_qry := 'SELECT session_id ' ||
2408 'FROM ' || CONS_SCHEMA || '.' || 'c$sync_history ' ||
2409 'WHERE start_time < (trunc(sysdate) - ' || l_purge_interval || ')';
2410 open c_hist for l_qry;
2411 LOOP
2412
2413 if (l_purge_session_data.count > 0 ) then
2414 l_purge_session_data.delete;
2415 end if;
2416
2417 fetch c_hist BULK COLLECT INTO l_purge_session_data LIMIT 100;
2418 exit when l_purge_session_data.count = 0;
2419
2420 IF l_purge_session_data.COUNT > 0 THEN
2421 l_row_count := l_row_count + l_purge_session_data.count;
2422 begin
2423 l_qry := 'delete from '||CONS_SCHEMA||'.'||'c$sync_history where ' || ' session_id = :1 ';
2424 FORALL i IN 1 .. l_purge_session_data.count SAVE EXCEPTIONS
2425 EXECUTE IMMEDIATE l_qry using l_purge_session_data(i);
2426 EXCEPTION
2427 WHEN others THEN
2428 log_concprogram
2429 ('Error occured when deleting from sync history table: ' ||SQLERRM ,
2430 'asg_download',
2431 FND_LOG.LEVEL_STATEMENT);
2432 end;
2433 commit;
2434 END IF;
2435 END LOOP;
2436 close c_hist;
2437
2438 log_concprogram('Deleted '||l_row_count||' row(s)',
2439 'asg_download',
2440 FND_LOG.LEVEL_STATEMENT);
2441
2442 log_concprogram('Done purging synch history data',
2443 'asg_download',
2444 FND_LOG.LEVEL_STATEMENT);
2445 UPDATE jtm_con_request_data
2446 SET last_run_date = SYSDATE
2447 WHERE package_name = 'ASG_DOWNLOAD'
2448 AND procedure_name = 'DELETE_SYNCH_HISTORY';
2449 COMMIT;
2450
2451 p_status := 'Fine';
2452 p_message := 'Purging synch history tables completed successfully';
2453 end if;
2454 exception
2455 when others then
2456 p_status := 'Error';
2457 p_message := 'Error purging synch history data '||SQLERRM;
2458 log_concprogram('Error purging synch history data '||SQLERRM,
2459 'asg_download',
2460 FND_LOG.LEVEL_STATEMENT);
2461 END delete_synch_history;
2462
2463
2464 procedure user_incompatibility_test(P_status OUT NOCOPY VARCHAR2,
2465 P_message OUT NOCOPY VARCHAR2)
2466 is
2467 cursor c_all_asg_user
2468 is
2469 select user_name,user_id,resource_id from asg_user where
2470 enabled='Y' and nvl(DISABLE_USER_SYNCH,'N') = 'N';
2471 l_asg_user_rec c_all_asg_user%rowtype;
2472 cursor c_chk_fnd_user_id(p_user_id number)
2473 is
2474 select user_name from fnd_user where user_id = p_user_id;
2475 l_user_name varchar2(100);
2476 l_user_id number;
2477 l_err_msg varchar2(2000);
2478 cursor c_chk_fnd_user_name(p_user_name varchar2)
2479 is
2480 select user_id from fnd_user where user_name = p_user_name;
2481 cursor c_chk_jtf_resource(p_res_id number)
2482 is
2483 select user_name from jtf_rs_resource_extns
2484 where resource_id = p_res_id
2485 and ( trunc(END_DATE_ACTIVE) is null
2486 or trunc(END_DATE_ACTIVE) > trunc(sysdate) );
2487 begin
2488 log('Starting to identify user incompatibility information');
2489 open c_all_asg_user;
2490 loop
2491 fetch c_all_asg_user into l_asg_user_rec;
2492 exit when c_all_asg_user%notfound;
2493 log('Processing user name : '||l_asg_user_rec.user_name);
2494 open c_chk_fnd_user_id(l_asg_user_rec.user_id);
2495 fetch c_chk_fnd_user_id into l_user_name;
2496 if (l_user_name is null) then
2497 /*check if user_id in asg_user exists in fnd_user table */
2498 l_err_msg := 'The user ID : '||l_asg_user_rec.user_id||
2499 ' in asg_user does not exist in fnd_user';
2500 log(l_err_msg);
2501 update asg_user
2502 set DISABLE_USER_SYNCH='Y',DISABLE_SYNCH_ERROR = l_err_msg
2503 where user_name = l_asg_user_rec.user_name;
2504 elsif(l_user_name <> l_asg_user_rec.user_name ) then
2505 /*Check for the user_id in asg_user, the user_name in asg_user
2506 and fnd_user match*/
2507 l_err_msg := 'For the user ID : '||l_asg_user_rec.user_id||
2508 ' the user names'||
2509 ' in asg_user and fnd_user do not match';
2510 log(l_err_msg);
2511 update asg_user
2512 set DISABLE_USER_SYNCH='Y',DISABLE_SYNCH_ERROR = l_err_msg
2513 where user_name = l_asg_user_rec.user_name;
2514 else
2515 /*Check for the user_name in asg_user, the user_id in fnd_user matches*/
2516 open c_chk_fnd_user_name(l_asg_user_rec.user_name);
2517 fetch c_chk_fnd_user_name into l_user_id;
2518 close c_chk_fnd_user_name;
2519 if(l_user_id <> l_asg_user_rec.user_id ) then
2520 l_err_msg := 'For the user name : '||l_asg_user_rec.user_name||
2521 ' the '||' user ID''s do not match in '||
2522 'asg_user and fnd_user ';
2523 log(l_err_msg);
2524 update asg_user set DISABLE_USER_SYNCH='Y',DISABLE_SYNCH_ERROR = l_err_msg
2525 where user_name = l_asg_user_rec.user_name;
2526 end if;
2527 end if;
2528 close c_chk_fnd_user_id;
2529
2530 l_user_name := NULL;
2531 open c_chk_jtf_resource(l_asg_user_rec.resource_id);
2532 fetch c_chk_jtf_resource into l_user_name;
2533 if(l_user_name is null) then
2534 /* Check if a record exists in jtf_rs_res* table with the
2535 same resource_id as asg_user.resource_id*/
2536 l_err_msg := 'For the resource ID '||l_asg_user_rec.resource_id||' no'||
2537 ' record exists in jtf_rs_resource_extns ';
2538 log(l_err_msg);
2539 update asg_user
2540 set DISABLE_USER_SYNCH='Y',DISABLE_SYNCH_ERROR = l_err_msg
2541 where user_name = l_asg_user_rec.user_name;
2542 elsif( l_user_name <> l_asg_user_rec.user_name ) then
2543 /*Check if the resource-name matches asg_user.user_name.*/
2544 l_err_msg := 'For the resource ID '||l_asg_user_rec.resource_id||
2545 ' the user names in asg_user and resource name do not match';
2546 log(l_err_msg);
2547 update asg_user
2548 set DISABLE_USER_SYNCH='Y',DISABLE_SYNCH_ERROR = l_err_msg
2549 where user_name = l_asg_user_rec.user_name;
2550 end if;
2551 close c_chk_jtf_resource;
2552 end loop;
2553 close c_all_asg_user;
2554 commit;
2555 log('Done identifying user incompatibility information');
2556
2557 p_status := 'Fine';
2558 p_message := 'Successfully identified user incompatibility information';
2559 exception
2560 when others then
2561 p_status := 'Error';
2562 p_message := 'Error identifying user incompatibility information '||SQLERRM;
2563 log('Error identifying user incompatibility information'||SQLERRM);
2564
2565 end user_incompatibility_test;
2566
2567 END asg_download;