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