[Home] [Help]
PACKAGE BODY: APPS.ASG_APPLY
Source
1 PACKAGE BODY asg_apply AS
2 /*$Header: asgaplyb.pls 120.13.12020000.2 2013/04/09 11:55:15 saradhak ship $*/
3
4 -- DESCRIPTION
5 -- This package is used to process records changed by the mobile
6 -- client. During synch, these records are placed in Olite's INQ.
7 -- We process the inq meta data to construct a consistent, necessary set
8 -- of information for application teams to process and apply these
9 -- records into the apps tables.
10 --
11 -- The main function process_upload is called by a concurrent program.
12 -- It processes all the changes for all clients. For every client,
13 -- a call back <asg_pub.wrapper_name>.apply_client_changes(...,...) is
14 -- called to process changes for a specific <client, tranid> which
15 -- are passed as parameters to that procedure. Applications
16 -- can also use the other APIs in this package to aid in their
17 -- processing. At the end of the call, all the client's records
18 -- for that publication and tranid should either be applied to the
19 -- applications table or deferred. We will defer any records remaining
20 -- in the INQ.
21 --
22 --
23 -- HISTORY
24 -- 03-Apr-2012 saradhak fix for html5 process upload
25 -- 15-mar-2011 saradhak Added synchronous_process_upload api
26 -- 23-oct-2009 saradhak Bug 9018969 - updated process_sequences api
27 -- 12-aug-2009 saradhak Added process_mobile_queries api
28 -- 15-May-2008 saradhak 12.1- Auto Sync
29 -- 15-sep-2004 ssabesan Changes for delivery notification
30 -- 01-jun-2004 ssabesan Merge 115.33.1158.10 to main line(11.5.9.6)
31 -- Add logging level in call to log() method.
32 -- 01-jun-2004 ssabesan Added table alias in purge_pubitems method
33 -- 18-feb-2003 ssabesan Modifed get_first_tranid and get_next_tranid to
34 -- return values <= asg_user.hwm_tranid
35 -- 24-jan-2003 ssabesan Fix bug # 2737613 ( Upload performance tuning)
36 -- 06-jan-2003 ssabesan Added NOCOPY in function definition
37 -- 06-jan-2003 ssabesan Check whether logging is enabled before invoking
38 -- logging procedure.
39 -- 10-dec-2002 rsripada Added support for not processing custom PIs
40 -- 14-aug-2002 rsripada Fixed 2508703
41 -- 26-jun-2002 rsripada Removed build dependencies on Olite schema
42 -- 25-jun-2002 rsripada Fixed bug 2432320
43 -- 05-jun-2002 rsripada Pubitems are returned based on weight
47 -- 25-apr-2002 rsripada Added deferred transaction support etc
44 -- 04-jun-2002 rsripada Changed logging to use asg_helper.log
45 -- 29-may-2002 rsripada Streamlined some of the procedures
46 -- 24-may-2002 rsripada Added sequence processing support
48 -- 03-mar-2002 rsripada Use wrapper_name for call backs
49 -- 19-feb-2002 rsripada Created
50
51 g_stmt_level NUMBER := FND_LOG.LEVEL_STATEMENT;
52 g_err_level NUMBER := FND_LOG.LEVEL_ERROR;
53 g_conc_start_time date;
54 g_is_conc_program varchar2(1) := 'N';
55
56 -- Logging procedure
57 PROCEDURE log(debug_msg IN VARCHAR2,
58 log_level IN NUMBER)
59 IS
60 sql_string VARCHAR2(4000);
61 log_msg VARCHAR2(3900);
62 start_string VARCHAR2(128);
63 log_msg_length PLS_INTEGER;
64 BEGIN
65 IF(asg_helper.check_is_log_enabled(log_level))
66 THEN
67 IF(g_user_name IS NOT NULL) THEN
68 start_string := 'upload_' || g_user_name || ': ';
69 ELSE
70 start_string := 'upload_log: ';
71 END IF;
72 log_msg := start_string || debug_msg;
73 asg_helper.log(log_msg, 'asg_apply', log_level);
74 END IF;
75 END log;
76
77 PROCEDURE print_string(p_string IN VARCHAR2)
78 IS
79 l_multiple PLS_INTEGER;
80 l_line_length PLS_INTEGER := 4000;
81 l_string_length PLS_INTEGER;
82 start_pos PLS_INTEGER;
83 BEGIN
84 IF p_string IS NULL THEN
85 return;
86 END IF;
87
88 IF (asg_helper.check_is_log_enabled(g_stmt_level)) THEN
89 l_string_length := length(p_string);
90 l_multiple := l_string_length/l_line_length+1 ;
91
92 IF l_multiple = 1 THEN
93 log(p_string,g_stmt_level);
94 ELSE
95 FOR curr_iter in 1..l_multiple LOOP
96 start_pos := (curr_iter-1)*l_line_length + 1;
97 log(substr(p_string, start_pos, l_line_length),g_stmt_level);
98 END LOOP;
99 END IF;
100 END IF;
101 END print_string;
102
103
104 function raise_row_deferred(p_start_time date)
105 return boolean
106 is
107 l_ctx dbms_xmlquery.ctxType;
108 l_clob clob;
109 l_seq number;
110 l_qry varchar2(2048);
111 begin
112 log('Begin raise_row_deferred',g_stmt_level);
113 l_qry := 'select DEVICE_USER_NAME user_name,DEFERRED_TRAN_ID tran_id ,'
114 ||'ERROR_DESCRIPTION ,OBJECT_NAME pub_item,SEQUENCE '
115 ||'from asg_deferred_traninfo where CREATION_DATE >= to_date('''
116 ||to_char(p_start_time,'mm-dd-yyyy hh24:mi:ss')
117 ||''',''mm-dd-yyyy hh24:mi:ss'') ';
118 log('Query :'||l_qry,g_stmt_level);
119 l_ctx := dbms_xmlquery.newContext(l_qry);
120 dbms_lob.createtemporary(l_clob,true,dbms_lob.session);
121 l_clob := dbms_xmlquery.getXml(l_ctx);
122 log('Raising event oracle.apps.asg.upload.datadeferred',g_stmt_level);
123 select asg_events_s.nextval into l_seq from dual;
124 wf_event.raise(p_event_name=>'oracle.apps.asg.upload.datadeferred',
125 p_event_key=>l_seq,p_parameters=>null,
126 p_event_data=>l_clob,p_send_date=>null);
127 log('Successfully raised event oracle.apps.asg.upload.datadeferred',g_stmt_level);
128 return true;
129 exception
130 when others then
131 log('Error raising oracle.apps.asg.upload.datadeferred :'||SQLERRM,g_err_level);
132 return false;
133 end raise_row_deferred;
134
135 -- Sort the publication item list by weight stored in
136 -- asg_pub_item table
137 PROCEDURE sort_by_weight(p_pub_name IN VARCHAR2,
138 x_pub_items_tbl IN OUT NOCOPY vc2_tbl_type)
139 IS
140 l_pub_items_tbl vc2_tbl_type;
141 l_all_pub_items vc2_tbl_type;
142 counter PLS_INTEGER;
143 CURSOR c_pub_items(p_pub_name IN VARCHAR2) IS
144 SELECT /*+ index (asg_pub_item, asg_pub_item_n1) */ name
145 FROM asg_pub_item
146 WHERE pub_name = p_pub_name
147 ORDER BY nvl(table_weight, 0);
148 BEGIN
149
150 IF(x_pub_items_tbl IS NULL) OR
151 (x_pub_items_tbl.count = 0) THEN
152 return;
153 END IF;
154
155 -- Make a copy of pubitem list
156 FOR curr_index in 1..x_pub_items_tbl.count LOOP
157 l_pub_items_tbl(curr_index) := x_pub_items_tbl(curr_index);
158 END LOOP;
159
160 -- Get the ordered list of pub items from the asg_pub_item table
161 counter := 1;
162 FOR cpi in c_pub_items(p_pub_name) LOOP
163 l_all_pub_items(counter) := cpi.name;
164 counter := counter +1;
165 END LOOP;
166
167 -- For each pub item, check if it is part of the initial list
168 -- At the end of this iteration, x_pub_items_tbl will contain
169 -- the ordered list
170 counter :=1;
171 FOR curr_index in 1..l_all_pub_items.count LOOP
172 FOR curr_index2 in 1..l_pub_items_tbl.count LOOP
173 IF (l_all_pub_items(curr_index) = l_pub_items_tbl(curr_index2)) THEN
174 x_pub_items_tbl(counter) := l_all_pub_items(curr_index);
175 counter := counter +1;
176 END IF;
177 END LOOP;
178 END LOOP;
179
180 END sort_by_weight;
181
182 PROCEDURE get_sync_state(p_user_name IN VARCHAR2,
183 p_sync_tables OUT NOCOPY NUMBER)
184 IS
185 l_select_sync_sqlstring VARCHAR2(512);
186 BEGIN
187 l_select_sync_sqlstring :=
188 'SELECT count(distinct clid$$cs) ' ||
192 '(SELECT tranid ' ||
189 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
190 'WHERE clid$$cs = :1 AND ' ||
191 'tranid$$ NOT IN ' ||
193 ' FROM asg_users_inqinfo ' ||
194 ' WHERE device_user_name = :2)';
195 EXECUTE IMMEDIATE l_select_sync_sqlstring
196 INTO p_sync_tables
197 USING p_user_name, p_user_name;
198 END get_sync_state;
199
200 --12.1
201 PROCEDURE compact_cinq(p_user_name IN VARCHAR2,
202 p_start_tranid IN NUMBER,
203 p_end_tranid IN NUMBER,
204 p_compact_tranid IN NUMBER)
205 IS
206 l_sql VARCHAR2(1024);
207 l_cinq_table VARCHAR2(60);
208 BEGIN
209 log('compact_cinq: Compacting c$inq for User: ' || p_user_name ||
210 ' Start TranID: ' || p_start_tranid ||
211 ' End TranID: ' || p_end_tranid ||
212 ' Compact TranID: ' || p_compact_tranid);
213
214 l_cinq_table := asg_base.G_OLITE_SCHEMA || '.c$inq ';
215 l_sql := 'UPDATE ' || l_cinq_table ||
216 'SET tranid$$ = :1 ' ||
217 'WHERE clid$$cs = :2 AND ' ||
218 ' tranid$$ >= :3 AND ' ||
219 ' tranid$$ <= :4';
220 log('compact_cinq: SQL Command: ' || l_sql);
221 EXECUTE IMMEDIATE l_sql
222 USING p_compact_tranid, p_user_name, p_start_tranid, p_end_tranid;
223 log('compact_cinq: No of Records Updated : ' || SQL%ROWCOUNT);
224
225 -- Remove duplicate entries
226 l_sql := 'DELETE FROM ' || l_cinq_table || ' a ' ||
227 'WHERE a.clid$$cs = :1 AND ' ||
228 ' a.tranid$$ = :2 AND ' ||
229 ' rowid > (select min(rowid) ' ||
230 ' from ' || l_cinq_table || ' b ' ||
231 ' where b.clid$$cs = a.clid$$cs AND ' ||
232 ' b.tranid$$ = a.tranid$$ AND ' ||
233 ' b.store = a.store)';
234 log('compact_cinq: SQL Command: ' || l_sql);
235 EXECUTE IMMEDIATE l_sql
236 USING p_user_name, p_compact_tranid;
237 log('compact_cinq: No of Records Deleted: ' || SQL%ROWCOUNT);
238
239 END compact_cinq;
240
241 --12.1
242 PROCEDURE compact_asginq(p_user_name IN VARCHAR2,
243 p_start_tranid IN NUMBER,
244 p_end_tranid IN NUMBER,
245 p_compact_tranid IN NUMBER)
246 IS
247 counter NUMBER;
248 counter2 NUMBER;
249 l_cursor_id1 NUMBER;
250 l_cursor_ret1 NUMBER;
251 l_store VARCHAR2(30);
252 l_select_store_sqlstring VARCHAR2(512);
253 l_pubitems_tbl vc2_tbl_type;
254 curr_pubitem VARCHAR2(30);
255 curr_pubitem_length NUMBER;
256 l_pubitems_max_length NUMBER:= 4000;
257 l_pubitems_1 VARCHAR2(4000);
258 l_pubitems_2 VARCHAR2(4000);
259 BEGIN
260
261 log('compact_asginq: Compacting asg inq info tables for User: ' || p_user_name ||
262 ' Start TranID: ' || p_start_tranid ||
263 ' End TranID: ' || p_end_tranid ||
264 ' Compact TranID: ' || p_compact_tranid);
265
266 -- Remove all records except compact tranID
267 DELETE FROM asg_users_inqinfo
268 WHERE device_user_name = p_user_name AND
269 tranid >= p_start_tranid AND
270 tranid <= p_end_tranid AND
271 tranid <> p_compact_tranid;
272
273 -- Update asg_users_inqarchive also
274
275 -- Get the list of pub-items for this tranid
276 -- This is the list of all publication items uploaded for that tranid
277 l_pubitems_1 := null;
278 l_pubitems_2 := null;
279 counter := 1;
280 counter2:= 1;
281 curr_pubitem_length := 0;
282 l_select_store_sqlstring :=
283 'SELECT store ' ||
284 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||
285 'WHERE clid$$cs = :1 AND ' ||
286 ' tranid$$ = :2 ' ||
287 ' ORDER BY store';
288
289 l_cursor_id1 := DBMS_SQL.OPEN_CURSOR;
290 DBMS_SQL.PARSE (l_cursor_id1, l_select_store_sqlstring, DBMS_SQL.v7);
291 DBMS_SQL.DEFINE_COLUMN (l_cursor_id1, 1, l_store, 30);
292 DBMS_SQL.BIND_VARIABLE(l_cursor_id1, ':1', p_user_name);
293 DBMS_SQL.BIND_VARIABLE(l_cursor_id1, ':2', p_compact_tranid);
294 l_cursor_ret1 := DBMS_SQL.EXECUTE (l_cursor_id1);
295
296 counter := 1;
297 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id1) > 0 ) LOOP
298 DBMS_SQL.COLUMN_VALUE (l_cursor_id1, 1, l_store);
299 l_pubitems_tbl(counter) := l_store;
300 counter := counter +1;
301 END LOOP;
302
303 DBMS_SQL.CLOSE_CURSOR(l_cursor_id1);
304
305 counter := 1;
306 counter2:= 1;
307 FOR curr_index2 IN 1..l_pubitems_tbl.count LOOP
308 curr_pubitem := l_pubitems_tbl(curr_index2);
309 curr_pubitem_length := curr_pubitem_length + length(curr_pubitem);
310 IF curr_pubitem_length >= 8000 THEN
311 EXIT;
312 END IF;
313 IF curr_pubitem_length < 4000 THEN
314 IF counter >1 THEN
315 l_pubitems_1 := l_pubitems_1 || ',';
316 curr_pubitem_length := curr_pubitem_length + 1; -- length of ','
317 END IF;
318 l_pubitems_1 := l_pubitems_1 || curr_pubitem;
319 counter := counter +1;
320 ELSE
321 IF counter2 >1 THEN
322 l_pubitems_2 := l_pubitems_2 || ',';
326 counter2 := counter2 +1;
323 curr_pubitem_length := curr_pubitem_length + 1; -- length of ','
324 END IF;
325 l_pubitems_2 := l_pubitems_2 || curr_pubitem;
327 END IF;
328 END LOOP;
329
330 log('compact_asginq: Pub-Items to be processed during this auto sync: ' ||
331 l_pubitems_1 || l_pubitems_2);
332 UPDATE asg_users_inqarchive
333 SET pub_items1 = l_pubitems_1, pub_items2 = l_pubitems_2
334 WHERE device_user_name = p_user_name AND
335 tranid = p_compact_tranid;
336
337 -- Remove all records except compact tranID
338 DELETE FROM asg_users_inqarchive
339 WHERE device_user_name = p_user_name AND
340 tranid >= p_start_tranid AND
341 tranid <= p_end_tranid AND
342 tranid <> p_compact_tranid;
343
344 END compact_asginq;
345
346 --12.1
347 PROCEDURE compact_curr_inqtable(p_user_name IN VARCHAR2,
348 p_start_tranid IN NUMBER,
349 p_end_tranid IN NUMBER,
350 p_compact_tranid IN NUMBER,
351 p_curr_pubitem IN VARCHAR2)
352 IS
353 l_inq_table VARCHAR2(60);
354 l_pk_columns VARCHAR2(2000);
355 l_pk_list asg_download.pk_list;
356 l_sql VARCHAR2(1024);
357 l_pk_clause VARCHAR2(1024) := NULL;
358 l_sql_count NUMBER;
359 BEGIN
360 l_inq_table := asg_base.G_OLITE_SCHEMA || '.cfm$' || p_curr_pubitem || ' ' ;
361 log('compact_curr_inqtable: Compacting inq table for User: ' || p_user_name ||
362 ' Start TranID: ' || p_start_tranid ||
363 ' End TranID: ' || p_end_tranid ||
364 ' Compact TranID: ' || p_compact_tranid ||
365 ' Pub-Item: ' || p_curr_pubitem);
366
367 -- Update the inq table to make (clid$$cs, seqno$$) a PK
368 -- Assuming not more than 1,000,000 records per pub-item per sync.
369 l_sql := 'UPDATE ' || l_inq_table ||
370 'SET seqno$$ = tranid$$*1000000 + seqno$$ ' ||
371 'WHERE clid$$cs = :1 AND ' ||
372 ' tranid$$ >= :2 AND ' ||
373 ' tranid$$ <= :3';
374 log('compact_curr_inqtable: SQL Command: ' || l_sql);
375 EXECUTE IMMEDIATE l_sql
376 USING p_user_name, p_start_tranid, p_end_tranid;
377 log('compact_curr_inqtable: No of Records Updated : ' || SQL%ROWCOUNT);
378
379 -- Update the inq table to the same tranid$$
380 l_sql := 'UPDATE ' || l_inq_table ||
381 'SET tranid$$ = :1 ' ||
382 'WHERE clid$$cs = :2 AND ' ||
383 ' tranid$$ >= :3 AND ' ||
384 ' tranid$$ <= :4';
385 log('compact_curr_inqtable: SQL Command: ' || l_sql);
386 EXECUTE IMMEDIATE l_sql
387 USING p_compact_tranid, p_user_name, p_start_tranid, p_end_tranid;
388 log('compact_curr_inqtable: No of Records Updated : ' || SQL%ROWCOUNT);
389
390 -- OK, all inq records to be processed have the same tranid$$
391 -- We need to update dmltype$$ now
392 SELECT primary_key_column INTO l_pk_columns
393 FROM asg_pub_item
394 WHERE name = p_curr_pubitem;
395
396 l_pk_list := asg_download.get_listfrom_string(l_pk_columns);
397 FOR curr_index IN 1..l_pk_list.count LOOP
398 IF(curr_index >1) THEN
399 l_pk_clause := l_pk_clause || ' AND ';
400 END IF;
401 l_pk_clause := l_pk_clause || ' b.' || l_pk_list(curr_index) || ' = ' ||
402 ' a.' || l_pk_list(curr_index) || ' ';
403 END LOOP;
404 log('compact_curr_inqtable: PK Clause: ' || l_pk_clause);
405
406 /*to remove records that have dml types Insert and Deletes and/or Updates in INQ in one sync*/
407 l_sql :=
408 'DELETE FROM '||l_inq_table ||' WHERE ('||l_pk_columns||',clid$$cs,tranid$$) IN(' ||
409 'SELECT '||l_pk_columns||',clid$$cs,tranid$$ FROM '||l_inq_table ||' a '||
410 'WHERE clid$$cs = :1
411 AND tranid$$ = :2
412 AND dmltype$$=''D''
413 AND EXISTS( select 1' ||
414 ' from ' || l_inq_table || ' b ' ||
415 ' where b.dmltype$$ =''I'' and
416 b.clid$$cs = a.clid$$cs and ' ||
417 ' b.tranid$$ = a.tranid$$ and ' ||
418 l_pk_clause || ' ))';
419
420 log('compact_curr_inqtable: SQL Command: ' || l_sql);
421 EXECUTE IMMEDIATE l_sql USING p_user_name, p_compact_tranid;
422 l_sql_count:=SQL%ROWCOUNT;
423 log('compact_curr_inqtable: No of Records Deleted : ' || l_sql_count);
424
425 IF (l_sql_count > 0) THEN
426 /*remove C_inq record if INQ table curr_tranid records are purged by above query */
427 l_sql := 'DELETE FROM '||asg_base.G_OLITE_SCHEMA||'.c$inq a '||
428 'WHERE STORE= '||''''||p_curr_pubitem||''' '||
429 'AND tranid$$=:1 '||
430 'AND NOT EXISTS (SELECT 1 FROM '||l_inq_table||' b WHERE a.tranid$$=b.tranid$$)' ;
431
432 log('compact_curr_inqtable: SQL Command: ' || l_sql);
433 EXECUTE IMMEDIATE l_sql USING p_compact_tranid;
434 l_sql_count:=SQL%ROWCOUNT;
435 log('compact_curr_inqtable: No of Records Deleted IN C$INQ : ' || l_sql_count);
436
437 IF l_sql_count>0 THEN
438 BEGIN
439 l_sql:='UPDATE ASG_USERS_INQARCHIVE '||
440 'SET PUB_ITEMS1=replace(PUB_ITEMS1,'''||p_curr_pubitem||''',''*'||p_curr_pubitem||'''),
441 PUB_ITEMS2=replace(PUB_ITEMS2,'''||p_curr_pubitem||''',''*'||p_curr_pubitem||''') '||
442 'WHERE DEVICE_USER_NAME=:2 AND TRANID=:3';
443 log('compact_curr_inqtable: SQL Command: ' || l_sql);
444 EXECUTE IMMEDIATE l_sql USING p_user_name,p_compact_tranid;
445 log('compact_curr_inqtable: No of Records Updated in INQARCHIVE : ' || SQL%ROWCOUNT);
446 EXCEPTION
447 WHEN Others THEN
448 l_sql:=sqlerrm;
449 log('compact_curr_inqtable:Ignoring Exception while Updating INQARCHIVE -'||l_sql);
450 END;
451 END IF;
452 END IF;
453
454
455 -- Update the inq with the correct dmltype$$
456 l_sql := 'UPDATE ' || l_inq_table || ' a ' ||
457 'SET dmltype$$ = (select min(dmltype$$) ' ||
458 ' from ' || l_inq_table || ' b ' ||
459 ' where b.clid$$cs = a.clid$$cs and ' ||
460 ' b.tranid$$ = a.tranid$$ and ' ||
461 l_pk_clause || ' ) '||
462 'WHERE clid$$cs = :1 AND tranid$$ = :2';
463
464 log('compact_curr_inqtable: SQL Command: ' || l_sql);
465 EXECUTE IMMEDIATE l_sql USING p_user_name, p_compact_tranid;
466 log('compact_curr_inqtable: No of Records Updated : ' || SQL%ROWCOUNT);
467
468 -- Remove duplicate records
469 l_sql := 'DELETE FROM ' || l_inq_table || ' a ' ||
470 'WHERE seqno$$ < (select max(seqno$$) ' ||
471 ' from ' || l_inq_table || ' b ' ||
472 ' where b.clid$$cs = a.clid$$cs and ' ||
473 ' b.tranid$$ = a.tranid$$ and ' ||
474 l_pk_clause || ' ) '||
475 'AND clid$$cs = :1 AND tranid$$ = :2';
476
477 log('compact_curr_inqtable: SQL Command: ' || l_sql);
478 EXECUTE IMMEDIATE l_sql USING p_user_name, p_compact_tranid;
479 log('compact_curr_inqtable: No of Records Deleted: ' || SQL%ROWCOUNT);
480
481
482 END compact_curr_inqtable;
483
484 --12.1
485 PROCEDURE compact_inqtables(p_user_name IN VARCHAR2,
486 p_start_tranid IN NUMBER,
487 p_end_tranid IN NUMBER,
488 p_compact_tranid IN NUMBER)
489 IS
490 counter NUMBER;
491 l_cursor_id1 NUMBER;
492 l_cursor_ret1 NUMBER;
493 l_store VARCHAR2(30);
494 l_select_store_sqlstring VARCHAR2(512);
495 l_pubitems_tbl vc2_tbl_type;
496 curr_pubitem VARCHAR2(30);
497 BEGIN
498
499 log('compact_inqtables: Compacting inq tables for User: ' || p_user_name ||
500 ' Start TranID: ' || p_start_tranid ||
501 ' End TranID: ' || p_end_tranid ||
502 ' Compact TranID: ' || p_compact_tranid);
503
504 counter := 1;
505 l_select_store_sqlstring :=
506 'SELECT store ' ||
507 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||
508 'WHERE clid$$cs = :1 AND ' ||
509 ' tranid$$ = :2 AND ' ||
510 ' store in (select item_id from asg_pub_item) ' ||
511 ' ORDER BY store';
512
513 l_cursor_id1 := DBMS_SQL.OPEN_CURSOR;
514 DBMS_SQL.PARSE (l_cursor_id1, l_select_store_sqlstring, DBMS_SQL.v7);
515 DBMS_SQL.DEFINE_COLUMN (l_cursor_id1, 1, l_store, 30);
516 DBMS_SQL.BIND_VARIABLE(l_cursor_id1, ':1', p_user_name);
517 DBMS_SQL.BIND_VARIABLE(l_cursor_id1, ':2', p_compact_tranid);
518 l_cursor_ret1 := DBMS_SQL.EXECUTE (l_cursor_id1);
519
520 counter := 1;
521 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id1) > 0 ) LOOP
522 DBMS_SQL.COLUMN_VALUE (l_cursor_id1, 1, l_store);
523 l_pubitems_tbl(counter) := l_store;
524 counter := counter +1;
525 END LOOP;
526
527 DBMS_SQL.CLOSE_CURSOR(l_cursor_id1);
528
529 FOR curr_index IN 1..l_pubitems_tbl.count LOOP
530 curr_pubitem := l_pubitems_tbl(curr_index);
531 compact_curr_inqtable(p_user_name, p_start_tranid, p_end_tranid,
532 p_compact_tranid, curr_pubitem);
533 END LOOP;
534
535 END compact_inqtables;
536
537 /* Procedure to retrieve transactions that are not yet added */
538 /* to asg_users_inqinfo table */
539 PROCEDURE get_new_tranids(p_user_name IN VARCHAR2,
540 l_tranids_tbl OUT NOCOPY num_tbl_type)
541 IS
542 counter PLS_INTEGER;
543 l_cursor_id NUMBER;
544 l_cursor_ret NUMBER;
545 l_select_tranid_sqlstring VARCHAR2(512);
546 l_tranid NUMBER;
547 BEGIN
548 l_select_tranid_sqlstring :=
549 'SELECT distinct tranid$$ ' ||
550 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
551 'WHERE clid$$cs = :user_name AND '||
552 'tranid$$ NOT IN ' ||
553 '(SELECT tranid ' ||
554 ' FROM asg_users_inqinfo ' ||
555 ' WHERE device_user_name = :user_name)';
556 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
557 DBMS_SQL.PARSE (l_cursor_id, l_select_tranid_sqlstring, DBMS_SQL.v7);
558 DBMS_SQL.BIND_VARIABLE (l_cursor_id, ':user_name', p_user_name );
559 DBMS_SQL.DEFINE_COLUMN (l_cursor_id, 1, l_tranid);
560 l_cursor_ret := DBMS_SQL.EXECUTE (l_cursor_id);
561
562 counter := 1;
563 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id) > 0 ) LOOP
564 DBMS_SQL.COLUMN_VALUE (l_cursor_id, 1, l_tranid);
565 l_tranids_tbl(counter) := l_tranid;
566 counter := counter +1;
567 END LOOP;
568
569 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
570 END get_new_tranids;
571
572 -- Returns the list of all clients with the specified inq record types
573 -- dirty for unprocessed new records
574 -- deferred for processed but deferred records.
575 -- x_return_status should be FND_API.G_RET_STS_SUCCESS for the client's
576 -- to be processed.
577 PROCEDURE get_all_clients(p_dirty IN VARCHAR2,
578 p_deferred IN VARCHAR2,
579 x_clients_tbl OUT NOCOPY vc2_tbl_type,
580 x_return_status OUT NOCOPY VARCHAR2)
581 IS
582 counter PLS_INTEGER;
583 CURSOR c_clients_yy IS
584 SELECT DISTINCT a.user_name user_name
585 FROM asg_user a, asg_users_inqinfo b
586 WHERE a.user_name = b.device_user_name AND
587 b.processed in ('I', 'N') AND
588 a.enabled = 'Y'
589 ORDER BY a.user_name;
590 CURSOR c_clients_yn IS
591 SELECT DISTINCT a.user_name user_name
592 FROM asg_user a, asg_users_inqinfo b
593 WHERE a.user_name = b.device_user_name AND
594 b.deferred = 'N' AND b.processed <> 'Y' AND
595 a.enabled = 'Y'
596 ORDER BY a.user_name;
597 CURSOR c_clients_ny IS
598 SELECT DISTINCT a.user_name user_name
599 FROM asg_user a, asg_users_inqinfo b
600 WHERE a.user_name = b.device_user_name AND
601 b.deferred <> 'N' and b.processed <> 'Y'
602 ORDER BY a.user_name;
603 BEGIN
604
605 -- Trivial case
606 IF ((p_dirty = FND_API.G_MISS_CHAR) OR (p_dirty IS NULL)) AND
607 ((p_deferred = FND_API.G_MISS_CHAR) OR (p_deferred IS NULL)) THEN
608 x_return_status := FND_API.G_RET_STS_ERROR;
609 return;
610 END IF;
611
612 IF ( (p_dirty NOT IN ('Y', 'N')) OR (p_deferred NOT IN ('Y', 'N')) ) OR
613 ( (p_dirty = 'N') AND (p_deferred = 'N') ) THEN
614 x_return_status := FND_API.G_RET_STS_ERROR;
615 return;
616 END IF;
617
618 x_return_status := FND_API.G_RET_STS_SUCCESS;
619 counter :=1;
620 IF (p_dirty = 'Y') THEN
621 IF(p_deferred = 'Y') THEN
622 FOR cclnt_yy IN c_clients_yy LOOP
623 x_clients_tbl(counter) := cclnt_yy.user_name;
624 counter := counter+1;
625 END LOOP;
626 ELSE
627 log('get_all_clients: Getting all users with ' ||
628 'dirty tranids',g_stmt_level);
629 FOR cclnt_yn IN c_clients_yn LOOP
630 x_clients_tbl(counter) := cclnt_yn.user_name;
631 counter := counter+1;
632 END LOOP;
633 END IF;
634 ELSIF (p_dirty = 'N') THEN
635 FOR cclnt_ny IN c_clients_ny LOOP
636 x_clients_tbl(counter) := cclnt_ny.user_name;
637 counter := counter+1;
638 END LOOP;
639 END IF;
640
641 END get_all_clients;
642
643 PROCEDURE get_all_tranids(p_user_name IN VARCHAR2,
644 x_tranids_tbl OUT NOCOPY num_tbl_type,
645 x_return_status OUT NOCOPY VARCHAR2)
646 IS
647 counter PLS_INTEGER;
648 CURSOR c_all_tran (p_user_name VARCHAR2) IS
649 SELECT tranid
650 FROM asg_users_inqinfo a
651 WHERE a.device_user_name = p_user_name
652 ORDER BY tranid;
653 BEGIN
654 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL))THEN
655 x_return_status := FND_API.G_RET_STS_ERROR;
656 return;
657 END IF;
658
659 x_return_status := FND_API.G_RET_STS_SUCCESS;
660 counter :=1;
661 FOR cat IN c_all_tran(p_user_name) LOOP
662 x_tranids_tbl(counter) := cat.tranid;
663 counter := counter +1;
664 END LOOP;
665
666 END get_all_tranids;
667
668 -- get the names of all publication items that have
669 -- records for the specified tran_id
670 PROCEDURE get_all_pub_items(p_user_name IN VARCHAR2,
671 p_tranid IN NUMBER,
672 x_pubitems_tbl OUT NOCOPY vc2_tbl_type,
673 x_return_status OUT NOCOPY VARCHAR2)
674 IS
675 counter PLS_INTEGER;
676 l_cursor_id NUMBER;
677 l_cursor_ret NUMBER;
678 l_cursor_id1 NUMBER;
679 l_cursor_ret1 NUMBER;
680 l_store VARCHAR2(30);
681 l_select_store_sqlstring VARCHAR2(512);
682 l_select_obj_sqlstring VARCHAR2(4000);
683 l_obj_name VARCHAR2(30);
684 BEGIN
685 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
686 ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) THEN
687 x_return_status := FND_API.G_RET_STS_ERROR;
688 return;
689 END IF;
690
691 x_return_status := FND_API.G_RET_STS_SUCCESS;
692 l_select_store_sqlstring :=
693 'SELECT store ' ||
694 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||
695 'WHERE clid$$cs = :1 AND ' ||
696 ' tranid$$ = :2 AND '||
697 ' store in (select item_id from asg_pub_item) ' ||
698 ' ORDER BY store';
699
700 l_cursor_id1 := DBMS_SQL.OPEN_CURSOR;
701 DBMS_SQL.PARSE (l_cursor_id1, l_select_store_sqlstring, DBMS_SQL.v7);
702 DBMS_SQL.DEFINE_COLUMN (l_cursor_id1, 1, l_store, 30);
703 DBMS_SQL.BIND_VARIABLE(l_cursor_id1, ':1', p_user_name);
704 DBMS_SQL.BIND_VARIABLE(l_cursor_id1, ':2', p_tranid);
705 l_cursor_ret1 := DBMS_SQL.EXECUTE (l_cursor_id1);
706
707 counter := 1;
708 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id1) > 0 ) LOOP
709 DBMS_SQL.COLUMN_VALUE (l_cursor_id1, 1, l_store);
710 x_pubitems_tbl(counter) := l_store;
711 counter := counter +1;
712 END LOOP;
713
714 DBMS_SQL.CLOSE_CURSOR(l_cursor_id1);
715
716 IF (x_pubitems_tbl IS NULL) THEN
717 counter :=1;
718 ELSE
719 counter := x_pubitems_tbl.count +1;
720 END IF;
721
722 l_select_obj_sqlstring :=
723 'SELECT object_name ' ||
724 'FROM asg_deferred_traninfo ' ||
725 'WHERE device_user_name = :user_name AND ' ||
726 ' deferred_tran_id = :tranid AND ' ||
727 ' object_name not in ' ||
728 ' (SELECT store ' ||
729 ' FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
730 ' WHERE clid$$cs = :user_name AND ' ||
731 ' tranid$$ = :tranid) ' ||
732 ' ORDER BY object_name';
733
734 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
735 DBMS_SQL.PARSE (l_cursor_id, l_select_obj_sqlstring, DBMS_SQL.v7);
736 DBMS_SQL.BIND_VARIABLE (l_cursor_id, ':user_name', p_user_name );
737 DBMS_SQL.BIND_VARIABLE (l_cursor_id, ':tranid', p_tranid );
738
739 DBMS_SQL.DEFINE_COLUMN (l_cursor_id, 1, l_obj_name, 30);
740 l_cursor_ret := DBMS_SQL.EXECUTE (l_cursor_id);
741
742 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id) > 0 ) LOOP
743 DBMS_SQL.COLUMN_VALUE (l_cursor_id, 1, l_obj_name);
744 x_pubitems_tbl(counter) := l_obj_name;
745 counter := counter +1;
746 END LOOP;
747
748 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
749
750 END get_all_pub_items;
751
752 -- get the names of all publication items that have
753 -- records for the specified tran_id
754 PROCEDURE get_all_pub_items(p_user_name IN VARCHAR2,
755 p_tranid IN NUMBER,
756 p_pubname IN VARCHAR2,
757 x_pubitems_tbl OUT NOCOPY vc2_tbl_type,
758 x_return_status OUT NOCOPY VARCHAR2)
759 IS
760 counter PLS_INTEGER;
761 l_cursor_id NUMBER;
762 l_cursor_ret NUMBER;
763 l_cursor_id1 NUMBER;
764 l_cursor_ret1 NUMBER;
765 l_store VARCHAR2(30);
766 l_select_store_sqlstring VARCHAR2(512);
767 l_select_obj_sqlstring VARCHAR2(4000);
768 l_obj_name VARCHAR2(30);
769 BEGIN
770 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
771 ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) OR
772 ((p_pubname = FND_API.G_MISS_CHAR) OR (p_pubname IS NULL)) THEN
773 x_return_status := FND_API.G_RET_STS_ERROR;
774 return;
775 END IF;
776
777 x_return_status := FND_API.G_RET_STS_SUCCESS;
778 l_select_store_sqlstring :=
779 'SELECT store ' ||
780 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||
781 'WHERE clid$$cs = :1 AND ' ||
782 ' tranid$$ = :2 AND ' ||
783 ' store in ' ||
784 ' (SELECT name ' ||
785 ' FROM asg_pub_item ' ||
786 ' WHERE pub_name = :3) ' ||
787 ' ORDER BY store';
788
789 l_cursor_id1 := DBMS_SQL.OPEN_CURSOR;
790 DBMS_SQL.PARSE (l_cursor_id1, l_select_store_sqlstring, DBMS_SQL.v7);
791 DBMS_SQL.DEFINE_COLUMN (l_cursor_id1, 1, l_store, 30);
792 DBMS_SQL.BIND_VARIABLE(l_cursor_id1, ':1', p_user_name);
793 DBMS_SQL.BIND_VARIABLE(l_cursor_id1, ':2', p_tranid);
794 DBMS_SQL.BIND_VARIABLE(l_cursor_id1, ':3', p_pubname);
795 l_cursor_ret1 := DBMS_SQL.EXECUTE (l_cursor_id1);
796
797 counter := 1;
798 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id1) > 0 ) LOOP
799 DBMS_SQL.COLUMN_VALUE (l_cursor_id1, 1, l_store);
800 x_pubitems_tbl(counter) := l_store;
801 counter := counter +1;
802 END LOOP;
803
804 DBMS_SQL.CLOSE_CURSOR(l_cursor_id1);
805
806 IF (x_pubitems_tbl IS NULL) THEN
807 counter :=1;
808 ELSE
809 counter := x_pubitems_tbl.count +1;
810 END IF;
811
812 l_select_obj_sqlstring :=
813 'SELECT object_name ' ||
814 'FROM asg_deferred_traninfo ' ||
815 'WHERE device_user_name = :user_name AND ' ||
816 ' deferred_tran_id = :tranid AND ' ||
817 ' object_name IN ' ||
818 ' (SELECT name ' ||
819 ' FROM asg_pub_item ' ||
820 ' WHERE pub_name = :pubname) AND ' ||
821 ' object_name not in ' ||
822 ' (SELECT store ' ||
823 ' FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
824 ' WHERE clid$$cs = :user_name AND ' ||
825 ' tranid$$ = :tranid) ' ||
826 ' ORDER BY object_name';
827
828 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
829 DBMS_SQL.PARSE (l_cursor_id, l_select_obj_sqlstring, DBMS_SQL.v7);
830 DBMS_SQL.BIND_VARIABLE (l_cursor_id, ':user_name', p_user_name );
831 DBMS_SQL.BIND_VARIABLE (l_cursor_id, ':tranid', p_tranid );
832 DBMS_SQL.BIND_VARIABLE (l_cursor_id, ':pubname', p_pubname );
833
834 DBMS_SQL.DEFINE_COLUMN (l_cursor_id, 1, l_obj_name, 30);
835 l_cursor_ret := DBMS_SQL.EXECUTE (l_cursor_id);
836
837 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id) > 0 ) LOOP
838 DBMS_SQL.COLUMN_VALUE (l_cursor_id, 1, l_obj_name);
839 x_pubitems_tbl(counter) := l_obj_name;
840 counter := counter +1;
841 END LOOP;
842
843 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
844
845 sort_by_weight(p_pubname, x_pubitems_tbl);
846
847 END get_all_pub_items;
848
849 -- get the names of all publication items that have dirty
850 -- records for the specified tran_id
851 PROCEDURE get_all_dirty_pub_items(p_user_name IN VARCHAR2,
852 p_tranid IN NUMBER,
853 p_pubname IN VARCHAR2,
854 x_pubitems_tbl OUT NOCOPY vc2_tbl_type,
855 x_return_status OUT NOCOPY VARCHAR2)
856 IS
857 counter PLS_INTEGER;
858 l_cursor_id1 NUMBER;
859 l_cursor_ret1 NUMBER;
860 l_store VARCHAR2(30);
861 l_select_store_sqlstring VARCHAR2(512);
862 BEGIN
863 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
864 ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) OR
865 ((p_pubname = FND_API.G_MISS_CHAR) OR (p_pubname IS NULL)) THEN
866 x_return_status := FND_API.G_RET_STS_ERROR;
867 return;
868 END IF;
869
870 x_return_status := FND_API.G_RET_STS_SUCCESS;
871 l_select_store_sqlstring :=
872 'SELECT store ' ||
873 'FROM ' || asg_base.G_OLITE_SCHEMA ||'.c$inq '||
874 'WHERE clid$$cs = :1 AND ' ||
875 ' tranid$$ = :2 AND ' ||
876 ' store not in ' ||
877 ' (SELECT object_name ' ||
878 ' FROM asg_deferred_traninfo ' ||
879 ' WHERE device_user_name = :3 AND ' ||
880 ' deferred_tran_id = :4) AND ' ||
881 ' store in ' ||
882 ' (SELECT name ' ||
883 ' FROM asg_pub_item ' ||
884 ' WHERE pub_name = :5) ' ||
885 ' ORDER BY store';
886
887 l_cursor_id1 := DBMS_SQL.OPEN_CURSOR;
888 DBMS_SQL.PARSE (l_cursor_id1, l_select_store_sqlstring, DBMS_SQL.v7);
889 DBMS_SQL.DEFINE_COLUMN (l_cursor_id1, 1, l_store, 30);
890 DBMS_SQL.BIND_VARIABLE(l_cursor_id1, ':1', p_user_name);
891 DBMS_SQL.BIND_VARIABLE(l_cursor_id1, ':2', p_tranid);
892 DBMS_SQL.BIND_VARIABLE(l_cursor_id1, ':3', p_user_name);
893 DBMS_SQL.BIND_VARIABLE(l_cursor_id1, ':4', p_tranid);
894 DBMS_SQL.BIND_VARIABLE(l_cursor_id1, ':5', p_pubname);
895 l_cursor_ret1 := DBMS_SQL.EXECUTE (l_cursor_id1);
896
897 counter := 1;
898 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id1) > 0 ) LOOP
899 DBMS_SQL.COLUMN_VALUE (l_cursor_id1, 1, l_store);
900 x_pubitems_tbl(counter) := l_store;
901 counter := counter +1;
902 END LOOP;
903
904 DBMS_SQL.CLOSE_CURSOR(l_cursor_id1);
905
906 sort_by_weight(p_pubname, x_pubitems_tbl);
907
908 END get_all_dirty_pub_items;
909
910 -- Will set x_return_status to FND_API.G_RET_STS_ERROR if no tranid exists
911 -- Returns both dirty and deferred tranids
912 PROCEDURE get_first_tranid(p_user_name IN VARCHAR2,
913 x_tranid OUT NOCOPY NUMBER,
914 x_return_status OUT NOCOPY VARCHAR2)
915 IS
916 CURSOR c_first_tran (p_user_name VARCHAR2) IS
917 SELECT min(tranid) tran_id
918 FROM asg_users_inqinfo a
919 WHERE a.device_user_name = p_user_name AND
920 a.deferred='N'
921 AND a.tranid <=
922 (SELECT nvl(hwm_tranid,1000000000000)
923 FROM asg_user
924 WHERE user_name=p_user_name);
925 l_compacted_tranid NUMBER;
926 BEGIN
927 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL))THEN
928 x_return_status := FND_API.G_RET_STS_ERROR;
929 return;
930 END IF;
931
932 --12.1
933 x_return_status := FND_API.G_RET_STS_SUCCESS;
934 OPEN c_first_tran(p_user_name);
935 FETCH c_first_tran into x_tranid;
936 IF c_first_tran%NOTFOUND THEN
937 x_return_status := FND_API.G_RET_STS_ERROR;
938 CLOSE c_first_tran;
939 RETURN;
940 END IF;
941 CLOSE c_first_tran;
942
943 get_compacted_tranid(p_user_name, x_tranid,
944 l_compacted_tranid, x_return_status);
945 x_tranid := l_compacted_tranid;
946 log('get_first_tranid: Returning UserName: ' || p_user_name ||
947 ' TranID: ' || x_tranid);
948
949 END get_first_tranid;
950
951 -- Will set x_return_status to FND_API.G_RET_STS_ERROR if no tranid exists
952 -- Returns both dirty and deferred tranids
953 PROCEDURE get_next_tranid(p_user_name IN VARCHAR2,
954 p_curr_tranid IN NUMBER,
955 x_tranid OUT NOCOPY NUMBER,
956 x_return_status OUT NOCOPY VARCHAR2)
957 IS
958 CURSOR c_next_tran (p_user_name VARCHAR2, p_tranid VARCHAR2) IS
959 SELECT min(tranid) tran_id
960 FROM asg_users_inqinfo a
961 WHERE tranid > p_tranid AND
962 a.device_user_name = p_user_name AND
963 a.deferred='N'
964 AND a.tranid <=
965 (SELECT nvl(hwm_tranid,1000000000000)
966 FROM asg_user
967 WHERE user_name=p_user_name);
968 l_compacted_tranid NUMBER;
969 BEGIN
970 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
971 ((p_curr_tranid = FND_API.G_MISS_NUM) OR (p_curr_tranid IS NULL)) THEN
972 x_return_status := FND_API.G_RET_STS_ERROR;
973 return;
974 END IF;
975
976 --12.1
977 x_return_status := FND_API.G_RET_STS_SUCCESS;
978 OPEN c_next_tran(p_user_name, p_curr_tranid);
979 FETCH c_next_tran into x_tranid;
980 IF x_tranid IS NULL THEN
981 -- When the current tranid is the last one, set the next tranid
982 -- also to the last one.
983 x_tranid := p_curr_tranid;
984 x_return_status := FND_API.G_RET_STS_ERROR;
985 CLOSE c_next_tran;
986 RETURN;
987 END IF;
988 CLOSE c_next_tran;
989
990 get_compacted_tranid(p_user_name, x_tranid,
991 l_compacted_tranid, x_return_status);
992 x_tranid := l_compacted_tranid;
993 log('get_next_tranid: Returning UserName: ' || p_user_name ||
994 ' Current TranID: ' || p_curr_tranid ||
995 ' Next TranID: ' || x_tranid);
996
997 END get_next_tranid;
998
999 --12.1
1000 -- Will set x_return_status to FND_API.G_RET_STS_ERROR if no tranid exists
1001 -- Returns both dirty and deferred tranids
1002 PROCEDURE get_compacted_tranid(p_user_name IN VARCHAR2,
1003 p_tranid IN NUMBER,
1004 x_compacted_tranid OUT NOCOPY NUMBER,
1005 x_return_status OUT NOCOPY VARCHAR2)
1006 IS
1007 CURSOR c_auto_sync_tranid(p_user_name VARCHAR2, p_tranid NUMBER) IS
1008 SELECT sync_id
1009 FROM asg_auto_sync_tranids
1010 WHERE user_name = p_user_name
1011 AND upload_tranid = p_tranid;
1012 l_sync_id NUMBER;
1013
1014 BEGIN
1015 log('get_compacted_tranid: UserName: ' || p_user_name ||
1016 ' Current TranID: ' || p_tranid);
1017
1018 x_return_status := FND_API.G_RET_STS_SUCCESS;
1019
1020 OPEN c_auto_sync_tranid(p_user_name, p_tranid);
1021 FETCH c_auto_sync_tranid into l_sync_id;
1025 x_compacted_tranid := p_tranid;
1022 IF c_auto_sync_tranid%NOTFOUND OR l_sync_id is null THEN
1023 -- If there is no record in asg_auto_sync_tranids for a particular tranid
1024 -- assume it is NOT auto sync
1026 CLOSE c_auto_sync_tranid;
1027 -- Remove the asg_sync_info record
1028 DELETE FROM asg_auto_sync_tranids
1029 WHERE user_name = p_user_name
1030 AND upload_tranid <= x_compacted_tranid;
1031 return;
1032 END IF;
1033 CLOSE c_auto_sync_tranid;
1034
1035
1036
1037 process_auto_sync(p_user_name, p_tranid,
1038 x_compacted_tranid, x_return_status);
1039 log('get_compacted_tranid: UserName: ' || p_user_name ||
1040 ' Current TranID: ' || p_tranid ||
1041 ' Compacted TranID: ' || x_compacted_tranid ||
1042 ' Return Status: ' || x_return_status);
1043
1044 END get_compacted_tranid;
1045
1046 --12.1
1047 -- Will set x_return_status to FND_API.G_RET_STS_ERROR if there is an error
1048 PROCEDURE process_auto_sync(p_user_name IN VARCHAR2,
1049 p_tranid IN NUMBER,
1050 x_compacted_tranid OUT NOCOPY NUMBER,
1051 x_return_status OUT NOCOPY VARCHAR2)
1052 IS
1053 CURSOR c_end_auto_sync(p_user_name VARCHAR2,
1054 p_tranid NUMBER) IS
1055 SELECT sync_id
1056 FROM asg_auto_sync_tranids
1057 WHERE user_name = p_user_name
1058 AND upload_tranid = p_tranid;
1059
1060 l_end_tranid NUMBER;
1061 l_compact_tranid NUMBER;
1062 BEGIN
1063 log('process_auto_sync: UserName: ' || p_user_name ||
1064 ' Current TranID: ' || p_tranid);
1065 x_return_status := FND_API.G_RET_STS_SUCCESS;
1066
1067 OPEN c_end_auto_sync(p_user_name, p_tranid);
1068 FETCH c_end_auto_sync into l_end_tranid;
1069 -- Shouldn't happen normally. Handle gracefully.
1070 IF l_end_tranid IS NULL THEN
1071 x_compacted_tranid := p_tranid;
1072 x_return_status := FND_API.G_RET_STS_ERROR;
1073 CLOSE c_end_auto_sync;
1074 RETURN;
1075 END IF;
1076 CLOSE c_end_auto_sync;
1077
1078 l_compact_tranid := l_end_tranid;
1079 x_compacted_tranid := l_compact_tranid;
1080
1081 log('process_auto_sync: UserName: ' || p_user_name ||
1082 ' Current TranID: ' || p_tranid ||
1083 ' End TranID: ' || l_end_tranid||
1084 ' Compacted TranID: ' || x_compacted_tranid);
1085
1086 IF p_tranid<>l_end_tranid THEN
1087 -- OK, we now know the tranid to use for all synchs with start tranid of
1088 -- p_tranid and end_tranid of l_end_tranid. Start Compacting
1089
1090 -- Process c$inq first
1091 log('process_auto_sync: Processing c$inq');
1092 compact_cinq(p_user_name, p_tranid, l_end_tranid, l_compact_tranid);
1093
1094 -- Process asg_users_inqinfo/asg_users_inqarchive
1095 log('process_auto_sync: Processing asg inq info tables');
1096 compact_asginq(p_user_name, p_tranid, l_end_tranid, l_compact_tranid);
1097
1098 -- Process inq tables
1099 log('process_auto_sync: Processing inq tables');
1100 compact_inqtables(p_user_name, p_tranid, l_end_tranid, l_compact_tranid);
1101
1102 END IF;
1103 -- Delete from asg_auto_sync_tranids table.
1104
1105 DELETE FROM asg_auto_sync_tranids
1106 WHERE user_name = p_user_name
1107 AND upload_tranid <= l_end_tranid;
1108
1109 log('process_auto_synch: Done Processing auto sync.');
1110
1111 END process_auto_sync;
1112
1113
1114 -- Procedure to delete a row that is not deferred
1115 PROCEDURE delete_row(p_user_name IN VARCHAR2,
1116 p_tranid IN NUMBER,
1117 p_pubitem IN VARCHAR2,
1118 p_sequence IN NUMBER,
1119 x_return_status OUT NOCOPY VARCHAR2)
1120 IS
1121 l_deferred_row VARCHAR2(1);
1122 inq_tbl_name VARCHAR2(30);
1123 sql_string VARCHAR2(512);
1124 BEGIN
1125
1126 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
1127 ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) OR
1128 ((p_pubitem = FND_API.G_MISS_CHAR) OR (p_pubitem IS NULL)) OR
1129 ((p_sequence = FND_API.G_MISS_NUM) OR (p_sequence IS NULL)) THEN
1130 x_return_status := FND_API.G_RET_STS_ERROR;
1131 return;
1132 END IF;
1133
1134 x_return_status := FND_API.G_RET_STS_SUCCESS;
1135
1136 IF CSM_UTIL_PKG.is_html5_user(p_user_name) THEN
1137 sql_string := 'DELETE FROM '|| csm_util_pkg.get_inq_tab(p_pubitem) ||
1138 ' WHERE clid$$cs = :1 AND ' ||' tranid$$ = :2 AND ' ||' seqno$$ = :3';
1139 ELSE
1140 inq_tbl_name := 'CFM$' || p_pubitem;
1141 sql_string := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||
1142 '.' || inq_tbl_name ||
1143 ' WHERE clid$$cs = :1 AND ' ||
1144 ' tranid$$ = :2 AND ' ||
1145 ' seqno$$ = :3';
1146 END IF;
1147 print_string('delete_row: SQL Command: ' || sql_string);
1148
1149 BEGIN
1150 EXECUTE IMMEDIATE sql_string
1151 USING p_user_name, p_tranid, p_sequence;
1152 EXCEPTION
1153 WHEN OTHERS THEN
1154 -- Ignore exceptions
1155 x_return_status := FND_API.G_RET_STS_ERROR;
1156 log('delete_row: Exception: ', g_err_level);
1157 END;
1158
1159 l_deferred_row := asg_defer.is_deferred(p_user_name, p_tranid,
1160 p_pubitem, p_sequence);
1161 -- Update status to processed or passed
1162 IF l_deferred_row = FND_API.G_TRUE THEN
1163 BEGIN
1167 deferred_tran_id = p_tranid AND
1164 UPDATE asg_deferred_traninfo
1165 SET status = 0
1166 WHERE device_user_name = p_user_name AND
1168 object_name = p_pubitem AND
1169 sequence = p_sequence;
1170 EXCEPTION
1171 WHEN OTHERS THEN
1172 -- Ignore exceptions
1173 log('delete_row: Exception: tranid not deferred',g_err_level);
1174 END;
1175 END IF;
1176
1177 END delete_row;
1178
1179
1180 -- Procedure to purge all the dirty INQ records for
1181 -- the specified user/transid/publication-item(s)
1182 PROCEDURE purge_pubitems_internal(p_user_name IN VARCHAR2,
1183 p_tranid IN NUMBER,
1184 p_pubitems_tbl IN vc2_tbl_type,
1185 x_return_status OUT NOCOPY VARCHAR2)
1186 IS
1187 num_pubitems PLS_INTEGER;
1188 counter PLS_INTEGER;
1189 curr_pubitem VARCHAR2(30);
1190 inq_tbl_name VARCHAR2(30);
1191 sql_string VARCHAR2(512);
1192 BEGIN
1193 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
1194 ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) OR
1195 (p_pubitems_tbl IS NULL) THEN
1196 x_return_status := FND_API.G_RET_STS_ERROR;
1197 return;
1198 END IF;
1199
1200 x_return_status := FND_API.G_RET_STS_SUCCESS;
1201 num_pubitems := p_pubitems_tbl.count;
1202 FOR curr_index in 1..num_pubitems LOOP
1203 curr_pubitem := p_pubitems_tbl(curr_index);
1204 inq_tbl_name := 'CFM$' || curr_pubitem;
1205 -- Should change this statement to use bind-variables
1206 sql_string := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||
1207 '.' || inq_tbl_name ||
1208 ' WHERE clid$$cs = :1 AND ' ||
1209 ' tranid$$ = :2 AND ' ||
1210 ' seqno$$ NOT IN ' ||
1211 ' (SELECT sequence ' ||
1212 ' FROM asg_deferred_traninfo ' ||
1213 ' WHERE device_user_name = :3 AND '||
1214 ' object_name = :4)';
1215 print_string('purge_pubitems_internal: SQL Command: ' || sql_string);
1216 EXECUTE IMMEDIATE sql_string
1217 USING p_user_name, p_tranid,
1218 p_user_name, curr_pubitem;
1219
1220 sql_string := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||
1221 '.' || 'c$inq ' ||
1222 'WHERE clid$$cs = :1 AND ' ||
1223 ' tranid$$ = :2 AND ' ||
1224 ' store = :3';
1225 print_string('purge_pubitems_internal: SQLCommand: ' || sql_string);
1226 EXECUTE IMMEDIATE sql_string
1227 USING p_user_name, p_tranid, curr_pubitem;
1228 END LOOP;
1229
1230
1231 END purge_pubitems_internal;
1232
1233 -- Procedure to purge all the dirty INQ records for
1234 -- the specified user/transid/publication-item(s)
1235 PROCEDURE purge_pubitems(p_user_name IN VARCHAR2,
1236 p_tranid IN NUMBER,
1237 p_pubitems_tbl IN vc2_tbl_type,
1238 x_return_status OUT NOCOPY VARCHAR2)
1239 IS
1240 num_pubitems PLS_INTEGER;
1241 counter PLS_INTEGER;
1242 curr_pubitem VARCHAR2(30);
1243 inq_tbl_name VARCHAR2(30);
1244 sql_string VARCHAR2(1024);
1245 l_deferred_trans BOOLEAN := FALSE;
1246 l_resource_id NUMBER;
1247 CURSOR c_resource_id (p_user_name VARCHAR2) IS
1248 SELECT resource_id
1249 FROM asg_user
1250 WHERE user_name = p_user_name;
1251 BEGIN
1252 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
1253 ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) OR
1254 (p_pubitems_tbl IS NULL) THEN
1255 x_return_status := FND_API.G_RET_STS_ERROR;
1256 return;
1257 END IF;
1258
1259 x_return_status := FND_API.G_RET_STS_SUCCESS;
1260 num_pubitems := p_pubitems_tbl.count;
1261 FOR curr_index in 1..num_pubitems LOOP
1262 curr_pubitem := p_pubitems_tbl(curr_index);
1263 -- Change to <pubitem_name>_inq once that synonym exists
1264 inq_tbl_name := 'CFM$' || curr_pubitem;
1265 log('Deferring unprocessed records in publication item: ' || curr_pubitem,
1266 g_stmt_level);
1267
1268 OPEN c_resource_id(p_user_name);
1269 FETCH c_resource_id INTO l_resource_id;
1270 IF c_resource_id%NOTFOUND THEN
1271 CLOSE c_resource_id;
1272 log('purge_pubitems: User: ' || p_user_name ||
1273 ' not found in asg_user table',g_err_level);
1274 x_return_status := FND_API.G_RET_STS_ERROR;
1275 return;
1276 END IF;
1277 CLOSE c_resource_id;
1278
1279 -- Defer those records that were not already deferred or deleted during
1280 -- processing by wrapper
1281 sql_string := 'INSERT INTO asg_deferred_traninfo ('||
1282 'DEVICE_USER_NAME, ' ||
1283 'RESOURCE_ID, ' ||
1284 'DEFERRED_TRAN_ID, ' ||
1285 'MOBILE_ERROR_ID, ' ||
1286 'ERROR_DESCRIPTION, ' ||
1287 'OBJECT_NAME, ' ||
1288 'SEQUENCE, ' ||
1289 'STATUS, ' ||
1290 'SYNC_TIME, ' ||
1291 'FAILURES, ' ||
1292 'LAST_UPDATE_DATE, ' ||
1296 'SELECT :1, :2, :3, ' ||
1293 'LAST_UPDATED_BY, ' ||
1294 'CREATION_DATE, ' ||
1295 'CREATED_BY) ' ||
1297 ' NULL,' ||
1298 '''Row deferred because it was left unprocessed'',' ||
1299 ' :4,' ||
1300 'seqno$$, ' ||
1301 '1,' ||
1302 'NULL,' ||
1303 '1,' ||
1304 'SYSDATE,' ||
1305 '1,' ||
1306 'SYSDATE,'||
1307 '1 ' ||
1308 'FROM ' || asg_base.G_OLITE_SCHEMA || '.' || inq_tbl_name ||
1309 ' b WHERE b.clid$$cs = :5 AND ' ||
1310 ' tranid$$ = :6 AND ' ||
1311 ' b.seqno$$ not in (SELECT sequence ' ||
1312 'FROM asg_deferred_traninfo ' ||
1313 'WHERE device_user_name = :7 AND ' ||
1314 ' deferred_tran_id = :8 '||
1315 ' AND object_name = :9)';
1316 --print_string('purge_pubitems: SQL Command: ' || sql_string);
1317 BEGIN
1318 EXECUTE IMMEDIATE sql_string
1319 USING p_user_name, l_resource_id, p_tranid,
1320 curr_pubitem, p_user_name,
1321 p_tranid, p_user_name, p_tranid, curr_pubitem;
1322
1323 log('Number of rows deferred: ' || SQL%ROWCOUNT,g_stmt_level);
1324 IF SQL%ROWCOUNT >0 THEN
1325 l_deferred_trans := TRUE;
1326 END IF;
1327 EXCEPTION
1328 WHEN OTHERS THEN
1329 log('purge_pubitems: Exception executing the SQL Command ' ||
1330 SQLERRM,g_err_level);
1331 END;
1332
1333 sql_string := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||
1334 '.' || 'c$inq ' ||
1335 'WHERE clid$$cs = :1 AND ' ||
1336 ' tranid$$ = :2 AND ' ||
1337 ' store = :3';
1338 print_string('purge_pubitems: SQLCommand: ' || sql_string);
1339 EXECUTE IMMEDIATE sql_string
1340 USING p_user_name, p_tranid, curr_pubitem;
1341
1342 END LOOP;
1343
1344 IF (l_deferred_trans = TRUE) THEN
1345 UPDATE asg_users_inqinfo
1346 SET deferred = 'Y', processed = 'I',
1347 last_update_date = SYSDATE, last_updated_by = 1
1348 WHERE device_user_name = p_user_name AND
1349 tranid = p_tranid;
1350 END IF;
1351
1352 END purge_pubitems;
1353
1354 -- Procedure to purge all the dirty INQ records for
1355 -- the specified user/transid
1356 PROCEDURE purge_pubitems(p_user_name IN VARCHAR2,
1357 p_tranid IN NUMBER,
1358 x_return_status OUT NOCOPY VARCHAR2)
1359 IS
1360 counter PLS_INTEGER;
1361 l_cursor_id1 NUMBER;
1362 l_cursor_ret1 NUMBER;
1363 l_store VARCHAR2(30);
1364 l_select_store_sqlstring VARCHAR2(512);
1365 l_pubitems_tbl vc2_tbl_type;
1366 BEGIN
1367
1368 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
1369 ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) THEN
1370 x_return_status := FND_API.G_RET_STS_ERROR;
1371 return;
1372 END IF;
1373
1374 x_return_status := FND_API.G_RET_STS_SUCCESS;
1375 l_select_store_sqlstring :=
1376 'SELECT store ' ||
1377 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq '||
1378 'WHERE clid$$cs = :1 AND ' ||
1379 ' tranid$$ = :2 AND ' ||
1380 ' store in (select item_id from asg_pub_item) ' ||
1381 ' ORDER BY store';
1382
1383 l_cursor_id1 := DBMS_SQL.OPEN_CURSOR;
1384 DBMS_SQL.PARSE (l_cursor_id1, l_select_store_sqlstring, DBMS_SQL.v7);
1385 DBMS_SQL.DEFINE_COLUMN (l_cursor_id1, 1, l_store, 30);
1386 DBMS_SQL.BIND_VARIABLE(l_cursor_id1, ':1', p_user_name);
1387 DBMS_SQL.BIND_VARIABLE(l_cursor_id1, ':2', p_tranid);
1388 l_cursor_ret1 := DBMS_SQL.EXECUTE (l_cursor_id1);
1389
1390 counter := 1;
1391 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id1) > 0 ) LOOP
1392 DBMS_SQL.COLUMN_VALUE (l_cursor_id1, 1, l_store);
1393 l_pubitems_tbl(counter) := l_store;
1394 counter := counter +1;
1395 END LOOP;
1396
1397 DBMS_SQL.CLOSE_CURSOR(l_cursor_id1);
1398
1399 -- Found some dirty pub-items for this <p_user_name, p_tranid>
1400 IF (l_pubitems_tbl IS NOT NULL) AND
1401 (l_pubitems_tbl.count > 0) THEN
1402 purge_pubitems(p_user_name, p_tranid, l_pubitems_tbl, x_return_status);
1403 END IF;
1404
1405 -- If no deferred rows, set processed = 'Y'
1406 UPDATE asg_users_inqinfo
1407 SET processed = 'Y', last_update_date=SYSDATE, last_updated_by=1
1408 WHERE device_user_name = p_user_name AND
1409 tranid = p_tranid AND
1410 tranid not IN
1411 (SELECT distinct deferred_tran_id
1412 FROM asg_deferred_traninfo
1413 WHERE device_user_name = p_user_name AND
1414 deferred_tran_id = p_tranid);
1415
1416 END purge_pubitems;
1417
1418 -- Procedure to purge all the dirty INQ records for
1419 -- the specified user
1420 PROCEDURE purge_pubitems(p_user_name IN VARCHAR2,
1421 x_return_status OUT NOCOPY VARCHAR2)
1422 IS
1423
1424 counter PLS_INTEGER;
1425 curr_tranid NUMBER;
1426 l_tranid_tbl num_tbl_type;
1427 cursor c_tranids (p_user_name VARCHAR2, p_max_tranid NUMBER) IS
1431 tranid <= p_max_tranid;
1428 SELECT tranid
1429 FROM asg_users_inqinfo
1430 WHERE device_user_name = p_user_name AND
1432 BEGIN
1433 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) THEN
1434 x_return_status := FND_API.G_RET_STS_ERROR;
1435 return;
1436 END IF;
1437
1438 counter :=1;
1439 x_return_status := FND_API.G_RET_STS_SUCCESS;
1440 -- Get all the tranids for this user
1441 FOR cti in c_tranids(p_user_name, g_current_tranid) LOOP
1442 l_tranid_tbl(counter) := cti.tranid;
1443 counter := counter+1;
1444 END LOOP;
1445
1446 -- Process one tranid at a time.
1447 IF counter >1 THEN
1448 FOR curr_index in 1..l_tranid_tbl.count LOOP
1449 curr_tranid := l_tranid_tbl(curr_index);
1450 log('purge_pubitems: Purging tranid: ' || curr_tranid,g_stmt_level);
1451 purge_pubitems(p_user_name, curr_tranid, x_return_status);
1452 END LOOP;
1453 END IF;
1454
1455 END purge_pubitems;
1456
1457 -- Signal the beginning of inq processing for an user
1458 -- returns FND_API.G_FALSE if no inq processing is necessary for this user
1459 PROCEDURE begin_client_apply(p_user_name IN VARCHAR2,
1460 x_begin_client_apply OUT NOCOPY VARCHAR2,
1461 x_return_status OUT NOCOPY VARCHAR2)
1462 IS
1463 l_sync_tables PLS_INTEGER;
1464 l_tranids_tbl num_tbl_type;
1465 curr_tranid NUMBER;
1466 BEGIN
1467 x_return_status := FND_API.G_RET_STS_SUCCESS;
1468 x_begin_client_apply := FND_API.G_TRUE;
1469
1470 -- Check if the c$inq and asg_users_inqinfo are in sync.
1471 get_sync_state(p_user_name, l_sync_tables);
1472 log('begin_client_apply: l_sync_tables: ' || l_sync_tables,g_stmt_level);
1473
1474 IF l_sync_tables = 1 THEN
1475 -- Get all the tranids in c$inq that are not in asg_users_inqinfo
1476 get_new_tranids(p_user_name, l_tranids_tbl);
1477
1478 FOR curr_index in 1..l_tranids_tbl.count LOOP
1479 curr_tranid := l_tranids_tbl(curr_index);
1480 setup_inq_info(p_user_name, curr_tranid, x_return_status);
1481 END LOOP;
1482
1483 END IF;
1484 COMMIT;
1485
1486 END begin_client_apply;
1487
1488 -- Signal the end of inq processing for an user
1489 -- All dirty records processed in this session will be removed.
1490 PROCEDURE end_client_apply(p_user_name IN VARCHAR2,
1491 x_return_status OUT NOCOPY VARCHAR2)
1492 IS
1493 curr_tranid NUMBER;
1494 curr_tran_processed VARCHAR2(1);
1495 curr_tran_deferred VARCHAR2(1);
1496 curr_tran_archive VARCHAR2(1);
1497 CURSOR c_archive_asg_users(p_user_name VARCHAR2) IS
1498 SELECT tranid, processed, deferred, archive
1499 FROM asg_users_inqinfo
1500 WHERE device_user_name = p_user_name;
1501 BEGIN
1502
1503 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) THEN
1504 x_return_status := FND_API.G_RET_STS_ERROR;
1505 return;
1506 END IF;
1507
1508 x_return_status := FND_API.G_RET_STS_SUCCESS;
1509 purge_pubitems(p_user_name, x_return_status);
1510 FOR caau in c_archive_asg_users(p_user_name) LOOP
1511 curr_tranid := caau.tranid;
1512 curr_tran_processed := caau.processed;
1513 curr_tran_deferred := caau.deferred;
1514 curr_tran_archive := caau.archive;
1515 IF (curr_tran_processed = 'Y') AND (curr_tran_archive = 'Y') THEN
1516 UPDATE asg_users_inqarchive
1517 SET processed = 'Y', deferred = curr_tran_deferred,
1518 last_update_date = SYSDATE, last_updated_by = 1
1519 WHERE device_user_name = p_user_name AND
1520 tranid = curr_tranid;
1521 END IF;
1522 END LOOP;
1523
1524 DELETE FROM asg_users_inqinfo
1525 WHERE device_user_name = p_user_name AND processed = 'Y';
1526 COMMIT;
1527
1528 END end_client_apply;
1529
1530 -- Should be called before any user's transactions are processed
1531 -- returns FND_API.G_FALSE if no user has dirty/deferred data in inq.
1532 PROCEDURE begin_apply(x_begin_apply OUT NOCOPY VARCHAR2,
1533 x_return_status OUT NOCOPY VARCHAR2)
1534 IS
1535 counter PLS_INTEGER;
1536 l_cursor_id NUMBER;
1537 l_cursor_ret NUMBER;
1538 l_select_users_sqlstring VARCHAR2(512);
1539 l_user_name VARCHAR2(100);
1540 l_begin_client_apply VARCHAR2(1);
1541 l_return_status VARCHAR2(1);
1542 curr_user VARCHAR2(100);
1543 l_users_tbl vc2_tbl_type;
1544 sql_string VARCHAR2(30);
1545 l_def_count PLS_INTEGER;
1546 CURSOR c_deferred IS
1547 SELECT count(*) def_trans
1548 FROM asg_deferred_traninfo;
1549 BEGIN
1550
1551 x_begin_apply := FND_API.G_TRUE;
1552 x_return_status := FND_API.G_RET_STS_SUCCESS;
1553
1554 -- Get the list of users with dirty data
1555 l_select_users_sqlstring :=
1556 'SELECT distinct clid$$cs clientid ' ||
1557 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
1558 ' WHERE store in (select item_id from asg_pub_item) ';
1559
1560 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1561 DBMS_SQL.PARSE (l_cursor_id, l_select_users_sqlstring, DBMS_SQL.v7);
1562 DBMS_SQL.DEFINE_COLUMN (l_cursor_id, 1, l_user_name, 100);
1563 l_cursor_ret := DBMS_SQL.EXECUTE (l_cursor_id);
1564
1565 counter := 1;
1566 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id) > 0 ) LOOP
1567 IF counter =1 THEN
1568 log('begin_apply: Following users have uploaded new data',g_stmt_level);
1569 END IF;
1570 DBMS_SQL.COLUMN_VALUE (l_cursor_id, 1, l_user_name);
1571 l_users_tbl(counter) := l_user_name;
1575
1572 log('begin_apply: ' || l_users_tbl(counter),g_stmt_level);
1573 counter := counter +1;
1574 END LOOP;
1576 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
1577
1578 IF counter = 1 THEN
1579 log('begin_apply: Did not find any user with dirty data',g_stmt_level);
1580 OPEN c_deferred;
1581 FETCH c_deferred into l_def_count;
1582 CLOSE c_deferred;
1583 IF l_def_count = 0 THEN
1584 -- No uploaded data, dirty or deferred
1585 x_begin_apply := FND_API.G_FALSE;
1586 g_only_deferred_trans := FND_API.G_FALSE;
1587 ELSE
1588 x_begin_apply := FND_API.G_TRUE;
1589 g_only_deferred_trans := FND_API.G_TRUE;
1590 END IF;
1591 ELSIF (counter >1) THEN
1592 g_only_deferred_trans := FND_API.G_FALSE;
1593 FOR curr_index IN 1..l_users_tbl.count LOOP
1594 curr_user := l_users_tbl(curr_index);
1595 begin_client_apply(curr_user, l_begin_client_apply, l_return_status);
1596 END LOOP;
1597 END IF;
1598
1599 END begin_apply;
1600
1601 -- Should be called at the end of the apply for all clients in that
1602 -- session. Always returns TRUE.
1603 PROCEDURE end_apply(x_return_status OUT NOCOPY VARCHAR2)
1604 IS
1605 BEGIN
1606 x_return_status := FND_API.G_RET_STS_SUCCESS;
1607 END end_apply;
1608
1609 -- Procedure to process synchronous mobile queries from client
1610 PROCEDURE process_mobile_queries(p_user_name IN VARCHAR2,
1611 p_tranid IN NUMBER,
1612 x_return_status OUT NOCOPY VARCHAR2)
1613 IS
1614 l_sql VARCHAR2(1000);
1615 l_pi_name VARCHAR2(100) := 'CSM_QUERY_INSTANCES';
1616 l_exists NUMBER;
1617 l_sqlerrno VARCHAR2(20);
1618 l_sqlerrmsg VARCHAR2(2000);
1619
1620 BEGIN
1621
1622 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
1623 ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) THEN
1624 log('process_mobile_queries: Invalid user or tranid passed: ' ||
1625 'user: ' || p_user_name || ' tranid: ' || p_tranid,g_err_level);
1626 x_return_status := FND_API.G_RET_STS_ERROR;
1627 return;
1628 END IF;
1629
1630 x_return_status := FND_API.G_RET_STS_SUCCESS;
1631
1632 l_sql := 'SELECT 1 FROM '|| asg_base.G_OLITE_SCHEMA
1633 ||'.C$INQ c_inq WHERE CLID$$CS=:1 AND TRANID$$=:2 AND STORE = :3'
1634 ||' AND exists (select 1 from '||asg_base.G_OLITE_SCHEMA||'.cfm$'||l_pi_name||' inq, CSM_QUERY_B b '
1635 ||' WHERE tranid$$ = c_inq.tranid$$ '
1636 ||' AND clid$$cs = c_inq.clid$$cs '
1637 ||' AND inq.QUERY_ID=b.QUERY_ID '
1638 ||' AND b.EXECUTION_MODE=''SYNCHRONOUS'')';
1639
1640 EXECUTE IMMEDIATE l_sql INTO l_exists USING p_user_name, p_tranid, l_pi_name;
1641
1642 log('process_mobile_queries: Calling CSM api to process Synchronous Mobile Queries');
1643
1644 l_sql := 'Begin CSM_QUERY_INSTANCE_PKG.APPLY_CLIENT_CHANGES(:1,:2,:3,:4,:5); end;';
1645
1646 EXECUTE IMMEDIATE l_sql USING p_user_name,p_tranid,g_stmt_level,'Y',in out x_return_status;
1647
1648 log('process_mobile_queries: status returned by CSM api - '||x_return_status);
1649
1650 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1651
1652 l_sql := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA
1653 ||'.c$inq c_inq WHERE CLID$$CS = :1 AND TRANID$$ = :2 AND STORE = :3'
1654 ||' AND not exists (select 1 from '||asg_base.G_OLITE_SCHEMA||'.cfm$'||l_pi_name||' inq '
1655 ||' WHERE tranid$$ = c_inq.tranid$$ '
1656 ||' AND clid$$cs = c_inq.clid$$cs )';
1657
1658 EXECUTE IMMEDIATE l_sql USING p_user_name, p_tranid, l_pi_name;
1659
1660 l_sql := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA
1661 ||'.c$inq c_inq WHERE CLID$$CS = :1 AND TRANID$$ = :2 AND STORE = :3'
1662 ||' AND not exists (select 1 from '||asg_base.G_OLITE_SCHEMA||'.cfm$CSM_QUERY_VARIABLE_VALUES inq '
1663 ||' WHERE tranid$$ = c_inq.tranid$$ '
1664 ||' AND clid$$cs = c_inq.clid$$cs )';
1665
1666 EXECUTE IMMEDIATE l_sql USING p_user_name, p_tranid, 'CSM_QUERY_VARIABLE_VALUES';
1667
1668 END IF;
1669
1670 -- if error in CSM api, let Process Upload report error
1671 EXCEPTION
1672 WHEN NO_DATA_FOUND THEN
1673 NULL;
1674 WHEN Others THEN
1675 l_sqlerrno := to_char(SQLCODE);
1676 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1677 log('process_mobile_queries: Exception occured for : ' ||
1678 'user: ' || p_user_name || ' tranid: ' || p_tranid||' - '||l_sqlerrno||':'||l_sqlerrmsg,g_err_level);
1679 END process_mobile_queries;
1680
1681 -- Backward Compatibility: Procedure to process sequence updates from client on C$ALL_SEQUENCE_PARTITIONS
1682 PROCEDURE process_sequences_bc(p_user_name IN VARCHAR2,
1683 p_tranid IN NUMBER)
1684 IS
1685 counter PLS_INTEGER;
1686 l_cursor_id NUMBER;
1687 l_cursor_ret NUMBER;
1688 l_sequence VARCHAR2(30) := 'C$ALL_SEQUENCE_PARTITIONS';
1689 l_select_seq_sqlstring VARCHAR2(4000);
1690 l_update_seq_sqlstring VARCHAR2(4000);
1691 l_delete_sqlstring VARCHAR2(4000);
1692 l_seq_name VARCHAR2(30);
1693 l_curr_val NUMBER(38);
1694 l_client_num NUMBER;
1695 BEGIN
1696
1697 counter := 1;
1698
1699 SELECT client_number INTO l_client_num
1700 FROM asg_user
1701 WHERE user_name = p_user_name;
1702
1703 l_select_seq_sqlstring :=
1707 asg_base.G_OLITE_SCHEMA || '.' || 'c$inq b ' ||
1704 'SELECT a.name name, a.curr_val curr_val '||
1705 'FROM ' || asg_base.G_OLITE_SCHEMA ||
1706 '.' || 'cfm$c$all_sequence_partitions a, ' ||
1708 'WHERE b.clid$$cs = :user_name AND ' ||
1709 'b.tranid$$ = :tranid AND ' ||
1710 'b.store = :seq_name AND ' ||
1711 'a.clid$$cs = b.clid$$cs AND ' ||
1712 'a.tranid$$ = b.tranid$$';
1713
1714 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1715 DBMS_SQL.PARSE (l_cursor_id, l_select_seq_sqlstring, DBMS_SQL.v7);
1716 DBMS_SQL.BIND_VARIABLE (l_cursor_id, ':user_name', p_user_name );
1717 DBMS_SQL.BIND_VARIABLE (l_cursor_id, ':tranid', p_tranid );
1718 DBMS_SQL.BIND_VARIABLE (l_cursor_id, ':seq_name', l_sequence );
1719
1720 DBMS_SQL.DEFINE_COLUMN (l_cursor_id, 1, l_seq_name, 30);
1721 DBMS_SQL.DEFINE_COLUMN (l_cursor_id, 2, l_curr_val);
1722 l_cursor_ret := DBMS_SQL.EXECUTE (l_cursor_id);
1723
1724 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id) > 0 ) LOOP
1725 DBMS_SQL.COLUMN_VALUE (l_cursor_id, 1, l_seq_name);
1726 DBMS_SQL.COLUMN_VALUE (l_cursor_id, 2, l_curr_val);
1727
1728 IF(MOD(l_curr_val, 1000000) = l_client_num) THEN
1729 l_update_seq_sqlstring := 'UPDATE asg_sequence_partitions ' ||
1730 'SET curr_val = :1 ' ||
1731 'WHERE CLIENTID = :2 AND ' ||
1732 ' name = :3 AND ' ||
1733 ' curr_val < :4';
1734 EXECUTE IMMEDIATE l_update_seq_sqlstring
1735 USING l_curr_val, p_user_name, l_seq_name, l_curr_val;
1736 log ('process_sequences_bc: Updating sequence for user: ' || p_user_name ||
1737 ' sequence: ' || l_seq_name || ' Seq value: ' || l_curr_val);
1738 ELSE
1739 log('process_sequences_bc: Users sequence mismatch! Sequence Name: ' || l_seq_name ||
1740 ' Sequence Value: ' ||l_curr_val || ' Client_Number: ' ||
1741 l_client_num, FND_LOG.LEVEL_UNEXPECTED);
1742 END IF;
1743
1744 counter := counter +1;
1745 log ('process_sequences_bc:Updating sequence for user: ' || p_user_name ||
1746 ' sequence: ' || l_seq_name);
1747 END LOOP;
1748
1749 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
1750 IF counter =1 THEN
1751 log('process_sequences_bc: No sequences need to be updated',g_stmt_level);
1752 END IF;
1753
1754 IF counter >1 THEN
1755 -- Updated sequences
1756 l_delete_sqlstring := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||
1757 '.' || 'c$inq ' ||
1758 'WHERE CLID$$CS = :1 AND ' ||
1759 'TRANID$$ = :2 AND ' ||
1760 'STORE = :3';
1761 EXECUTE IMMEDIATE l_delete_sqlstring
1762 USING p_user_name, p_tranid, l_sequence;
1763
1764 l_delete_sqlstring := 'DELETE FROM ' ||
1765 asg_base.G_OLITE_SCHEMA ||
1766 '.' ||'cfm$c$all_sequence_partitions ' ||
1767 'WHERE CLID$$CS = :1 AND ' ||
1768 ' TRANID$$ = :2';
1769 EXECUTE IMMEDIATE l_delete_sqlstring
1770 USING p_user_name, p_tranid;
1771 END IF;
1772
1773 END process_sequences_bc;
1774
1775 -- Procedure to process sequence updates from client
1776 PROCEDURE process_sequences(p_user_name IN VARCHAR2,
1777 p_tranid IN NUMBER,
1778 x_return_status OUT NOCOPY VARCHAR2)
1779 IS
1780 counter PLS_INTEGER;
1781 l_cursor_id NUMBER;
1782 l_cursor_ret NUMBER;
1783 l_sequence VARCHAR2(30) := 'CSM_SEQUENCES';
1784 l_select_seq_sqlstring VARCHAR2(4000);
1785 l_update_seq_sqlstring VARCHAR2(4000);
1786 l_delete_sqlstring VARCHAR2(4000);
1787 l_seq_name VARCHAR2(30);
1788 l_curr_val NUMBER(38);
1789 l_client_num NUMBER;
1790 BEGIN
1791
1792 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
1793 ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) THEN
1794 x_return_status := FND_API.G_RET_STS_ERROR;
1795 log('process_sequences: Invalid user or tranid passed: ' ||
1796 'user: ' || p_user_name || ' tranid: ' || p_tranid,g_err_level);
1797 x_return_status := FND_API.G_RET_STS_ERROR;
1798 return;
1799 END IF;
1800
1801 process_sequences_bc(p_user_name,p_tranid);
1802
1803 x_return_status := FND_API.G_RET_STS_SUCCESS;
1804 counter := 1;
1805
1806 SELECT client_number INTO l_client_num
1807 FROM asg_user
1808 WHERE user_name = p_user_name;
1809
1810 l_select_seq_sqlstring :=
1811 'SELECT a.name name, a.curr_val curr_val '||
1812 'FROM ' || asg_base.G_OLITE_SCHEMA ||
1813 '.' || 'cfm$CSM_SEQUENCES a, ' ||
1814 asg_base.G_OLITE_SCHEMA || '.' || 'c$inq b ' ||
1815 'WHERE b.clid$$cs = :user_name AND ' ||
1816 'b.tranid$$ = :tranid AND ' ||
1817 'b.store = :seq_name AND ' ||
1818 'a.clid$$cs = b.clid$$cs AND ' ||
1819 'a.tranid$$ = b.tranid$$';
1820
1821 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1822 DBMS_SQL.PARSE (l_cursor_id, l_select_seq_sqlstring, DBMS_SQL.v7);
1823 DBMS_SQL.BIND_VARIABLE (l_cursor_id, ':user_name', p_user_name );
1824 DBMS_SQL.BIND_VARIABLE (l_cursor_id, ':tranid', p_tranid );
1825 DBMS_SQL.BIND_VARIABLE (l_cursor_id, ':seq_name', l_sequence );
1826
1830
1827 DBMS_SQL.DEFINE_COLUMN (l_cursor_id, 1, l_seq_name, 30);
1828 DBMS_SQL.DEFINE_COLUMN (l_cursor_id, 2, l_curr_val);
1829 l_cursor_ret := DBMS_SQL.EXECUTE (l_cursor_id);
1831 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id) > 0 ) LOOP
1832 DBMS_SQL.COLUMN_VALUE (l_cursor_id, 1, l_seq_name);
1833 DBMS_SQL.COLUMN_VALUE (l_cursor_id, 2, l_curr_val);
1834
1835 IF(MOD(l_curr_val, 1000000) = l_client_num) THEN
1836 l_update_seq_sqlstring := 'UPDATE asg_sequence_partitions ' ||
1837 'SET curr_val = :1 ' ||
1838 'WHERE CLIENTID = :2 AND ' ||
1839 ' name = :3 AND ' ||
1840 ' curr_val < :4';
1841 EXECUTE IMMEDIATE l_update_seq_sqlstring
1842 USING l_curr_val, p_user_name, l_seq_name, l_curr_val;
1843 log ('Updating sequence for user: ' || p_user_name ||
1844 ' sequence: ' || l_seq_name || ' Seq value: ' || l_curr_val);
1845 ELSE
1846 log('Users sequence mismatch! Sequence Name: ' || l_seq_name ||
1847 ' Sequence Value: ' ||l_curr_val || ' Client_Number: ' ||
1848 l_client_num, FND_LOG.LEVEL_UNEXPECTED);
1849 END IF;
1850
1851 counter := counter +1;
1852 log ('Updating sequence for user: ' || p_user_name ||
1853 ' sequence: ' || l_seq_name);
1854 END LOOP;
1855
1856 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
1857 IF counter =1 THEN
1858 log('No sequences need to be updated',g_stmt_level);
1859 END IF;
1860
1861 IF counter >1 THEN
1862 -- Updated sequences
1863 l_delete_sqlstring := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||
1864 '.' || 'c$inq ' ||
1865 'WHERE CLID$$CS = :1 AND ' ||
1866 'TRANID$$ = :2 AND ' ||
1867 'STORE = :3';
1868 EXECUTE IMMEDIATE l_delete_sqlstring
1869 USING p_user_name, p_tranid, l_sequence;
1870
1871 l_delete_sqlstring := 'DELETE FROM ' ||
1872 asg_base.G_OLITE_SCHEMA ||
1873 '.' ||'cfm$CSM_SEQUENCES ' ||
1874 'WHERE CLID$$CS = :1 AND ' ||
1875 ' TRANID$$ = :2';
1876 EXECUTE IMMEDIATE l_delete_sqlstring
1877 USING p_user_name, p_tranid;
1878 END IF;
1879
1880 END process_sequences;
1881
1882
1883 -- Procedure to update the upload information
1884 PROCEDURE setup_inq_info(p_user_name IN VARCHAR2,
1885 p_tranid IN NUMBER,
1886 x_return_status OUT NOCOPY VARCHAR2)
1887 IS
1888 counter PLS_INTEGER;
1889 counter2 PLS_INTEGER;
1890 l_cursor_id1 NUMBER;
1891 l_cursor_ret1 NUMBER;
1892 l_store VARCHAR2(30);
1893 l_inq_count NUMBER;
1894 l_resource_id NUMBER;
1895 l_select_store_sqlstring VARCHAR2(512);
1896 l_select_inqcnt_sqlstring VARCHAR2(512);
1897 l_delete_inq_sqlstring VARCHAR2(512);
1898 l_pubitems_tbl vc2_tbl_type;
1899 curr_tranid NUMBER;
1900 curr_pubitem VARCHAR2(30);
1901 curr_pubitem_length PLS_INTEGER;
1902 l_pubitems_max_length PLS_INTEGER := 4000;
1903 l_pubitems_1 VARCHAR2(4000);
1904 l_pubitems_2 VARCHAR2(4000);
1905 CURSOR c_resource_id (p_user_name VARCHAR2) IS
1906 SELECT resource_id
1907 FROM asg_user
1908 WHERE user_name = p_user_name;
1909 BEGIN
1910 x_return_status := FND_API.G_RET_STS_SUCCESS;
1911 curr_tranid := p_tranid;
1912
1913 -- Remove any custom pub items from c$inq
1914 -- Custom pub items are processed directly by custom implementor.
1915 l_delete_inq_sqlstring :=
1916 'DELETE from ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
1917 'WHERE store in ' ||
1918 ' (SELECT api.name ' ||
1919 ' FROM asg_pub ap, asg_pub_item api ' ||
1920 ' WHERE ap.custom = ''Y'' AND ' ||
1921 ' ap.name = api.pub_name)';
1922 EXECUTE IMMEDIATE l_delete_inq_sqlstring;
1923
1924 l_select_inqcnt_sqlstring :=
1925 'SELECT count(*) ' ||
1926 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ci ' ||
1927 'WHERE clid$$cs = :1 AND ' ||
1928 ' tranid$$ = :2 AND ' ||
1929 ' store in (select item_id from asg_pub_item) ' ||
1930 'AND NOT EXISTS (SELECT 1 FROM asg_users_inqinfo ' ||
1931 ' WHERE device_user_name = ci.clid$$cs AND TRANID =ci.tranid$$) ' ;
1932 EXECUTE IMMEDIATE l_select_inqcnt_sqlstring
1933 INTO l_inq_count
1934 USING p_user_name, p_tranid;
1935
1936 IF (l_inq_count > 0) THEN
1937
1938 OPEN c_resource_id(p_user_name);
1939 FETCH c_resource_id into l_resource_id;
1940 CLOSE c_resource_id;
1941 IF l_resource_id IS NULL THEN
1942 log('setup_inq_info: Did not find the user: '
1943 || p_user_name || ' in asg_user table',g_err_level);
1944 x_return_status := FND_API.G_RET_STS_ERROR;
1945 return;
1946 END IF;
1947
1948 INSERT INTO asg_users_inqinfo (device_user_name,
1949 resource_id,
1950 tranid,
1951 sync_date,
1952 processed,
1953 deferred,
1954 archive,
1955 last_update_date,
1956 last_updated_by,
1957 creation_date,
1958 created_by)
1959 VALUES (p_user_name,
1963 'N',
1960 l_resource_id,
1961 p_tranid,
1962 sysdate,
1964 'N',
1965 'Y',
1966 SYSDATE,
1967 1,
1968 SYSDATE,
1969 1);
1970
1971 -- Get the list of pub-items for this tranid
1972 -- This is the list of all publication items uploaded for that tranid
1973 l_pubitems_1 := null;
1974 l_pubitems_2 := null;
1975 counter := 1;
1976 counter2:= 1;
1977 curr_pubitem_length := 0;
1978 l_select_store_sqlstring :=
1979 'SELECT store ' ||
1980 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ci '||
1981 'WHERE clid$$cs = :1 AND ' ||
1982 ' tranid$$ = :2 AND ' ||
1983 ' store in (select item_id from asg_pub_item) '||
1984 ' AND NOT EXISTS (SELECT 1 FROM asg_users_inqarchive ' ||
1985 ' WHERE device_user_name = ci.clid$$cs AND TRANID =ci.tranid$$) '||
1986 ' ORDER BY store';
1987
1988 l_cursor_id1 := DBMS_SQL.OPEN_CURSOR;
1989 DBMS_SQL.PARSE (l_cursor_id1, l_select_store_sqlstring, DBMS_SQL.v7);
1990 DBMS_SQL.DEFINE_COLUMN (l_cursor_id1, 1, l_store, 30);
1991 DBMS_SQL.BIND_VARIABLE(l_cursor_id1, ':1', p_user_name);
1992 DBMS_SQL.BIND_VARIABLE(l_cursor_id1, ':2', p_tranid);
1993 l_cursor_ret1 := DBMS_SQL.EXECUTE (l_cursor_id1);
1994
1995 counter := 1;
1996 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id1) > 0 ) LOOP
1997 DBMS_SQL.COLUMN_VALUE (l_cursor_id1, 1, l_store);
1998 l_pubitems_tbl(counter) := l_store;
1999 counter := counter +1;
2000 END LOOP;
2001
2002 DBMS_SQL.CLOSE_CURSOR(l_cursor_id1);
2003 --12.1
2004 counter := 1;
2005 counter2:= 1;
2006 FOR curr_index2 IN 1..l_pubitems_tbl.count LOOP
2007 curr_pubitem := l_pubitems_tbl(curr_index2);
2008 curr_pubitem_length := curr_pubitem_length + length(curr_pubitem);
2009 IF curr_pubitem_length >= 8000 THEN
2010 EXIT;
2011 END IF;
2012 IF curr_pubitem_length < 4000 THEN
2013 IF counter >1 THEN
2014 l_pubitems_1 := l_pubitems_1 || ',';
2015 curr_pubitem_length := curr_pubitem_length + 1; -- length of ','
2016 END IF;
2017 l_pubitems_1 := l_pubitems_1 || curr_pubitem;
2018 counter := counter +1;
2019 ELSE
2020 IF counter2 >1 THEN
2021 l_pubitems_2 := l_pubitems_2 || ',';
2022 curr_pubitem_length := curr_pubitem_length + 1; -- length of ','
2023 END IF;
2024 l_pubitems_2 := l_pubitems_2 || curr_pubitem;
2025 counter2 := counter2 +1;
2026 END IF;
2027 END LOOP;
2028
2029 -- Replace with call to table-handler for asg_users_inqarchive
2030 INSERT INTO asg_users_inqarchive (device_user_name,
2031 resource_id,
2032 tranid,
2033 sync_date,
2034 processed,
2035 deferred,
2036 pub_items1,
2037 pub_items2,
2038 last_update_date,
2039 last_updated_by,
2040 creation_date,
2041 created_by)
2042 VALUES (p_user_name,
2043 l_resource_id,
2044 p_tranid,
2045 sysdate,
2046 'N',
2047 'N',
2048 l_pubitems_1,
2049 l_pubitems_2,
2050 SYSDATE,
2051 1,
2052 SYSDATE,
2053 1);
2054 END IF;
2055
2056 END setup_inq_info;
2057
2058 PROCEDURE process_user(p_user_name IN VARCHAR2,
2059 p_tranid IN NUMBER,
2060 x_return_status OUT NOCOPY VARCHAR2)
2061 IS
2062 counter PLS_INTEGER;
2063 l_def_trans VARCHAR2(1);
2064 l_cursor_id NUMBER;
2065 l_cursor_ret NUMBER;
2066 l_return_status VARCHAR2(1);
2067 curr_pub VARCHAR2(30);
2068 curr_pubhandler VARCHAR2(30);
2069 l_pub_name VARCHAR2(30);
2070 l_wrapper_name VARCHAR2(30);
2071 l_callback_sqlstring VARCHAR2(512);
2072 l_select_inqcnt_sqlstring VARCHAR2(512);
2073 l_select_pub_sqlstring VARCHAR2(512);
2074 l_userpub_tbl vc2_tbl_type;
2075 l_pubitems_tbl vc2_tbl_type;
2076 l_pubhandler_tbl vc2_tbl_type;
2077 l_inq_count NUMBER;
2078
2079 l_respid NUMBER;
2080 l_appid NUMBER;
2081 l_select_resp_sqlstring VARCHAR2(512);
2082 l_select_userid_sqlstring VARCHAR2(512);
2083 l_userid NUMBER;
2084
2085 BEGIN
2086
2087 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
2088 ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) THEN
2089 x_return_status := FND_API.G_RET_STS_ERROR;
2090 return;
2091 END IF;
2092
2093 x_return_status := FND_API.G_RET_STS_SUCCESS;
2094
2095
2096 -- check if there are any entries in C$inq for this <user, tranid>
2097 -- If there aren't any then this means there was some error and we
2098 -- will mark this transaction as not to be processed further
2102 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$inq ' ||
2099 -- Users can later rectify/purge through the def txn UI.
2100 l_select_inqcnt_sqlstring :=
2101 'SELECT count(*) ' ||
2103 'WHERE clid$$cs = :1 AND ' ||
2104 ' tranid$$ = :2';
2105 EXECUTE IMMEDIATE l_select_inqcnt_sqlstring
2106 INTO l_inq_count
2107 USING p_user_name, p_tranid;
2108
2109 IF (l_inq_count = 0) THEN
2110 log('process_user: Unknown exception. No inq records found for user: '
2111 || p_user_name || ' for tranid: ' || p_tranid, FND_LOG.LEVEL_ERROR);
2112 log('process_user: Possible cause: Olite was reinstalled while ' ||
2113 'there were unprocessed inq transactions',g_stmt_level);
2114 UPDATE asg_users_inqinfo
2115 SET processed = 'U', deferred = 'Y'
2116 WHERE device_user_name = p_user_name AND
2117 tranid = p_tranid;
2118 return ;
2119 END IF;
2120
2121 l_select_pub_sqlstring :=
2122 'SELECT template, wrapper_name ' ||
2123 'FROM ' || asg_base.G_OLITE_SCHEMA || '.c$all_subscriptions a, ' ||
2124 ' asg_pub b ' ||
2125 'WHERE a.clientid = :user_name AND ' ||
2126 ' a.template = b.name';
2127
2128 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
2129 DBMS_SQL.PARSE (l_cursor_id, l_select_pub_sqlstring, DBMS_SQL.v7);
2130 DBMS_SQL.BIND_VARIABLE (l_cursor_id, ':user_name', p_user_name );
2131
2132 DBMS_SQL.DEFINE_COLUMN (l_cursor_id, 1, l_pub_name, 30);
2133 DBMS_SQL.DEFINE_COLUMN (l_cursor_id, 2, l_wrapper_name, 30);
2134 l_cursor_ret := DBMS_SQL.EXECUTE (l_cursor_id);
2135
2136 counter :=1;
2137 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id) > 0 ) LOOP
2138 DBMS_SQL.COLUMN_VALUE (l_cursor_id, 1, l_pub_name);
2139 DBMS_SQL.COLUMN_VALUE (l_cursor_id, 2, l_wrapper_name);
2140 l_userpub_tbl(counter) := l_pub_name;
2141 l_pubhandler_tbl(counter) := l_wrapper_name;
2142 counter := counter +1;
2143 END LOOP;
2144
2145 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
2146
2147 -- User is subscribed to atleast one publication we know of
2148 IF counter >1 THEN
2149 FOR curr_index IN 1..l_userpub_tbl.count LOOP
2150 curr_pub := l_userpub_tbl(curr_index);
2151 curr_pubhandler := l_pubhandler_tbl(curr_index);
2152 log('process_user: current pub : ' || curr_pub ||
2153 ' current pub handler: ' || curr_pubhandler,g_stmt_level);
2154 l_pubitems_tbl := g_empty_vc2_tbl;
2155 get_all_pub_items(p_user_name, p_tranid, curr_pub,
2156 l_pubitems_tbl, l_return_status);
2157 -- Check if there is any data for this publication
2158 IF(l_return_status = FND_API.G_RET_STS_SUCCESS) AND
2159 (l_pubitems_tbl.count >0) THEN
2160
2161 -- For current user and current publication get the
2162 -- responsibility ID and application ID.
2163 SELECT USER_ID into l_userid
2164 FROM asg_user
2165 WHERE user_name = p_user_name;
2166
2167 SELECT pr.responsibility_id, pr.app_id
2168 INTO l_respid, l_appid
2169 FROM asg_user_pub_resps pr
2170 WHERE pr.user_name = upper(p_user_name) AND
2171 pr.pub_name = upper(curr_pub) AND
2172 ROWNUM =1;
2173
2174 fnd_global.apps_initialize(l_userid, l_respid, l_appid);
2175 log('process_user: apps_initialize() invoked for responsibility :'
2176 || l_respid || 'and application :'||l_appid,g_stmt_level);
2177
2178
2179 -- apps initialize is called.it has to be reset after wrapper call
2180 -- USING p_user_name, p_tranid;
2181
2182
2183 log('process_user: Calling handler package for ' ||
2184 'user: ' || p_user_name,g_stmt_level);
2185 l_callback_sqlstring := 'begin ' ||
2186 curr_pubhandler ||
2187 '.apply_client_changes( :1, :2); ' ||' end;';
2188 BEGIN
2189 log('process_user: SQL Command: ' || l_callback_sqlstring
2190 ,g_stmt_level);
2191 EXECUTE IMMEDIATE l_callback_sqlstring
2192 USING p_user_name, p_tranid;
2193 EXCEPTION
2194 WHEN OTHERS THEN
2195 log('process_user: Exception in wrapper call. ' ||
2196 'Check if valid wrapper exists ' ||
2197 SQLERRM,g_err_level);
2198 x_return_status := FND_API.G_RET_STS_SUCCESS;
2199 --reset
2200 fnd_global.apps_initialize(g_conc_userid, g_conc_respid, g_conc_appid);
2201 return;
2202 END;
2203 ELSE
2204 log('No pubitems from publication: ' || curr_pub || ' to process',
2205 g_stmt_level);
2206 END IF;
2207 --reset
2208 fnd_global.apps_initialize(g_conc_userid, g_conc_respid, g_conc_appid);
2209 END LOOP;
2210 END IF;
2211 END process_user;
2212
2213 PROCEDURE synchronous_process_upload(curr_user IN VARCHAR2)
2214 IS
2215 curr_tranid NUMBER;
2216 next_tranid NUMBER;
2217 l_return_status VARCHAR2(1);
2218 BEGIN
2219 g_user_name := curr_user;
2220 log('synchronous_process_upload: applying changes for user: '|| curr_user,g_stmt_level);
2221 get_first_tranid(p_user_name => curr_user,
2222 x_tranid => curr_tranid,
2223 x_return_status => l_return_status);
2224 g_current_tranid := curr_tranid;
2225 WHILE l_return_status = FND_API.G_RET_STS_SUCCESS LOOP
2226 log('synchronous_process_upload: Processing tranid: ' || curr_tranid,g_stmt_level);
2227 process_user(p_user_name => curr_user,
2228 p_tranid => curr_tranid,
2229 x_return_status => l_return_status);
2230 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2231 -- Make the call to get the next tranid
2232 -- This means if upload processing for one transaction failed
2233 -- upload processing for the rest of the transactions is stopped.
2234 get_next_tranid(p_user_name => curr_user,
2235 p_curr_tranid => curr_tranid,
2236 x_tranid => next_tranid,
2237 x_return_status => l_return_status);
2238 curr_tranid := next_tranid;
2239 g_current_tranid := curr_tranid;
2240 END IF;
2241 END LOOP;
2242 end_client_apply(p_user_name => curr_user, x_return_status => l_return_status);
2243 log('synchronous_process_upload: Return status from end_client_apply: ' ||l_return_status,g_stmt_level);
2244 log('synchronous_process_upload: Finished applying changes for user: ' ||curr_user,g_stmt_level);
2245
2246 g_user_name := null;
2247
2248 END synchronous_process_upload;
2249
2250 -- Main procedure to process all upload transactions
2251 PROCEDURE process_upload(errbuf OUT NOCOPY VARCHAR2,
2252 RETCODE OUT NOCOPY VARCHAR2,
2253 p_mode IN VARCHAR2)
2254 IS
2255 counter PLS_INTEGER;
2256 l_begin_apply VARCHAR2(1);
2257 l_return_status VARCHAR2(1);
2258 curr_user VARCHAR2(100);
2259 curr_tranid NUMBER;
2260 next_tranid NUMBER;
2261 l_tranid_tbl num_tbl_type;
2262 l_users_tbl vc2_tbl_type;
2263 l_row_count number;
2264 l_prof_value varchar2(5);
2265 L_BOOL_RET BOOLEAN;
2266 l_err_msg VARCHAR2(4000);
2267 BEGIN
2268
2269 g_user_name := null;
2270 g_is_conc_program := 'Y';
2271 retcode := FND_API.G_RET_STS_SUCCESS;
2272 /*remember the conc program start time*/
2273 g_conc_start_time:=null;
2274 select sysdate into g_conc_start_time from dual;
2275
2276 -- Get the conc program's user id, respid and appid
2277 g_conc_userid := fnd_global.user_id();
2278 IF g_conc_userid IS NULL or g_conc_userid = -1 THEN
2279 g_conc_userid := 5;
2280 END IF;
2281 g_conc_respid := fnd_global.resp_id();
2282 IF g_conc_respid IS NULL or g_conc_respid = -1 THEN
2283 g_conc_respid := 20420;
2284 END IF;
2285 g_conc_appid := fnd_global.resp_appl_id();
2286 IF g_conc_appid IS NULL or g_conc_appid = -1 THEN
2287 g_conc_appid := 1;
2288 END IF;
2289
2290 IF p_mode = 'CSM_MULTI_PLATFORM' THEN
2291 CSM_UTIL_PKG.LOG( 'Processing upload of multi platform users', 'ASG_APPLY.process_upload',FND_LOG.LEVEL_PROCEDURE);
2292 CSM_HTML5_PKG.CONC_PROCESS_UPLOAD(ERRBUF,RETCODE);
2293 ELSE
2294 begin_apply(l_begin_apply, l_return_status);
2295 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2296 log('Error in begin_apply',g_err_level);
2297 retcode := l_return_status;
2298 return;
2299 END IF;
2300 IF (l_begin_apply = FND_API.G_FALSE) THEN
2301 -- No users to process
2302 log('No users with uploaded data',g_stmt_level);
2303 return;
2304 END IF;
2305 IF (g_only_deferred_trans = FND_API.G_TRUE) THEN
2306 log('Only deferred uploaded data is available for processing',
2307 g_stmt_level);
2308 ELSE
2309 log('Both dirty and deferred uploaded data is available for processing',
2310 g_stmt_level);
2311 END IF;
2312
2313 -- Get the list of all users
2314 get_all_clients(p_dirty => 'Y',
2315 p_deferred => 'N',
2316 x_clients_tbl => l_users_tbl,
2317 x_return_status => l_return_status);
2318 log('process_upload: Num of users to process: ' || l_users_tbl.count,
2319 g_stmt_level);
2320
2321 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2322 return;
2323 END IF;
2324
2325 -- Fill in the client's subscriptions and handlers
2326 FOR curr_index in 1..l_users_tbl.count LOOP
2327 curr_user := l_users_tbl(curr_index);
2328 g_user_name := curr_user;
2329 log('process_upload: applying changes for user: '
2330 || curr_user,g_stmt_level);
2331 get_first_tranid(p_user_name => curr_user,
2332 x_tranid => curr_tranid,
2333 x_return_status => l_return_status);
2334 g_current_tranid := curr_tranid;
2335 WHILE l_return_status = FND_API.G_RET_STS_SUCCESS LOOP
2336 log('process_upload: Processing tranid: ' || curr_tranid,g_stmt_level);
2337 process_user(p_user_name => curr_user,
2338 p_tranid => curr_tranid,
2339 x_return_status => l_return_status);
2340 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2341 -- Make the call to get the next tranid
2342 -- This means if upload processing for one transaction failed
2343 -- upload processing for the rest of the transactions is stopped.
2344 get_next_tranid(p_user_name => curr_user,
2345 p_curr_tranid => curr_tranid,
2346 x_tranid => next_tranid,
2347 x_return_status => l_return_status);
2348 curr_tranid := next_tranid;
2349 g_current_tranid := curr_tranid;
2350 END IF;
2351 END LOOP;
2352 end_client_apply(p_user_name => curr_user,
2353 x_return_status => l_return_status);
2354 log('process_upload: Return status from end_client_apply: ' ||
2355 l_return_status,g_stmt_level);
2356 log('process_upload: Finished applying changes for user: ' ||
2357 curr_user,g_stmt_level);
2358 END LOOP;
2359 g_user_name := null;
2360 end_apply(l_return_status);
2361
2362 /*Check profile value*/
2363 select nvl(fnd_profile.value_specific('ASG_ENABLE_UPLOAD_EVENTS'),'N')
2364 into l_prof_value from dual;
2365 /*Check if any rows were deferred in the current run*/
2366 select count(*) into l_row_count
2367 from asg_deferred_traninfo
2368 where creation_date >= g_conc_start_time;
2369 if(l_prof_value = 'Y')
2370 then
2371 if(l_row_count > 0 )
2372 then
2373 log('Raising oracle.apps.asg.upload.datadeferred');
2374 l_bool_ret := raise_row_deferred(g_conc_start_time);
2375 else
2376 log('No data to raise oracle.apps.asg.upload.datadeferred');
2377 end if;
2378 else
2379 log('Not raising oracle.apps.asg.upload.datadeferred since the profile '
2380 ||' ASG_ENABLE_UPLOAD_EVENTS is not set to ''Y''',g_stmt_level);
2381 END IF;
2382 END IF;--Check for HA
2383 G_CONC_START_TIME := NULL;
2384 G_IS_CONC_PROGRAM := NULL;
2385
2386 END process_upload;
2387
2388 function is_conc_program_running
2389 return varchar2
2390 is
2391 begin
2392 if(g_is_conc_program = 'Y')
2393 then
2394 return 'Y';
2395 else
2396 return 'N';
2397 end if;
2398 end is_conc_program_running;
2399 END asg_apply;