[Home] [Help]
PACKAGE BODY: APPS.ASG_DEFER
Source
1 PACKAGE BODY asg_defer AS
2 /*$Header: asgdfrb.pls 120.5.12020000.2 2013/04/09 11:55:59 saradhak ship $*/
3
4 -- DESCRIPTION
5 -- This package supports deferred transactions.
6 --
7 --
8 -- HISTORY
9 -- 03-Apr-2012 saradhak fix for html5 reapply/discard txn
10 -- 13-jul-2009 saradhak added commit flag to reapply_txn & discard_txn apis
11 -- 29-Jan-2009 trajasek Change delete logic in delete deferred
12 -- 15-sep-2004 ssabesan Changes for delivery notification
13 -- 01-jun-2004 ssabesan Merge 115.20.1158.4 into main line(11.5.9.6)
14 -- Change literal to bind variables.
15 -- 06-jan-2003 ssabesan Check whether logging is enabled before invoking
16 -- logging procedure.
17 -- 23-jul-2002 rsripada Do not remove deferred rows during reject_row
18 -- 26-jun-2002 rsripada Remove Olite dependencies
19 -- 31-may-2002 rsripada Added logging support
20 -- 24-may-2002 rsripada Implemented reject row
21 -- 17-may-2002 rsripada Modified defer_row
22 -- 19-feb-2002 rsripada Created
23
24 -- Defers a row. Returns FND_API.G_RET_STS_SUCCESS if the row was
25 -- successfully deferred. FND_API.G_RET_STS_ERROR otherwise. Will
26 -- commit any work done as part of this proceduer using autonomous
27 -- transaction. sequence is a column in the inq that together with
28 -- the user_name, tran_id, pub_item can uniquely identify a record
29 -- in the inq.
30
31 g_stmt_level NUMBER := FND_LOG.LEVEL_STATEMENT;
32 g_err_level NUMBER := FND_LOG.LEVEL_ERROR;
33 g_apply_start_time date;
34
35 function raise_row_deferred(p_user_name VARCHAR2,
36 p_tranid NUMBER,
37 p_pubitem VARCHAR2,
38 p_sequence NUMBER,
39 p_error_msg VARCHAR2)
40 return boolean
41 is
42 l_ctx dbms_xmlquery.ctxType;
43 l_clob clob;
44 l_seq number;
45 l_qry varchar2(2048);
46 begin
47 if(asg_helper.check_is_log_enabled(g_stmt_level))
48 then
49 asg_helper.log('Begin raise_row_deferred','asg_defer',g_stmt_level);
50 end if;
51 l_qry := 'select '''||p_user_name||''' user_name,'''||to_char(p_tranid)
52 ||''' tran_id, '''||p_error_msg||''' ERROR_DESCRIPTION ,'
53 ||''''||p_pubitem||''' pub_item ,'''||p_sequence||''' SEQUENCE '
54 ||' from dual';
55 /*l_qry := 'select DEVICE_USER_NAME user_name,DEFERRED_TRAN_ID tran_id ,'
56 ||'ERROR_DESCRIPTION ,OBJECT_NAME pub_item,SEQUENCE '
57 ||'from asg_deferred_traninfo where CREATION_DATE >= to_date('''
58 ||to_char(p_start_time,'mm-dd-yyyy hh24:mi:ss')
59 ||''',''mm-dd-yyyy hh24:mi:ss'') ';*/
60 if(asg_helper.check_is_log_enabled(g_stmt_level))
61 then
62 asg_helper.log('Query :'||l_qry,'asg_defer',g_stmt_level);
63 end if;
64 l_ctx := dbms_xmlquery.newContext(l_qry);
65 dbms_lob.createtemporary(l_clob,true,dbms_lob.session);
66 l_clob := dbms_xmlquery.getXml(l_ctx);
67 if(asg_helper.check_is_log_enabled(g_stmt_level))
68 then
69 asg_helper.log('Raising event oracle.apps.asg.upload.datadeferred',
70 'asg_defer',g_stmt_level);
71 end if;
72 select asg_events_s.nextval into l_seq from dual;
73 wf_event.raise(p_event_name=>'oracle.apps.asg.upload.datadeferred',
74 p_event_key=>l_seq,p_parameters=>null,
75 p_event_data=>l_clob,p_send_date=>null);
76 if(asg_helper.check_is_log_enabled(g_stmt_level))
77 then
78 asg_helper.log('Successfully raised event oracle.apps.asg.upload.data'
79 ||'deferred','asg_defer',g_stmt_level);
80 end if;
81 return true;
82 exception
83 when others then
84 asg_helper.log('Error raising oracle.apps.asg.upload.datadeferred :'||SQLERRM,'asg_defer',g_err_level);
85 return false;
86 end raise_row_deferred;
87
88 PROCEDURE defer_row(p_user_name IN VARCHAR2,
89 p_tranid IN NUMBER,
90 p_pubitem IN VARCHAR2,
91 p_sequence IN NUMBER,
92 p_error_msg IN VARCHAR2,
93 x_return_status OUT NOCOPY VARCHAR2)
94 IS
95 PRAGMA AUTONOMOUS_TRANSACTION;
96 l_resource_id NUMBER;
97 CURSOR c_resource_id (p_user_name VARCHAR2) IS
98 SELECT resource_id
99 FROM asg_user
100 WHERE user_name = p_user_name;
101 l_error_msg VARCHAR2(4000);
102 l_msg_data VARCHAR2(2000);
103 l_msg_dummy NUMBER;
104 l_msg_length PLS_INTEGER;
105 l_errmsg_length PLS_INTEGER;
106 l_prof_value varchar2(4);
107 l_bool_ret boolean;
108 l_cp_run varchar2(1);
109 BEGIN
110 l_cp_run := 'N';
111 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
112 ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) OR
113 ((p_pubitem = FND_API.G_MISS_CHAR) OR (p_pubitem IS NULL)) OR
114 ((p_sequence = FND_API.G_MISS_NUM) OR (p_sequence IS NULL)) THEN
115 x_return_status := FND_API.G_RET_STS_ERROR;
116 COMMIT;
117 return;
118 END IF;
119
120 x_return_status := FND_API.G_RET_STS_SUCCESS;
121
122 -- Construct the error message if it is not passed in
123 IF (p_error_msg = FND_API.G_MISS_CHAR) OR
124 (p_error_msg IS NULL) THEN
125 l_error_msg := 'Error Msg: ';
126
127 FOR j in 1 .. fnd_msg_pub.count_msg LOOP
128 fnd_msg_pub.get(j, FND_API.G_FALSE, l_msg_data, l_msg_dummy);
129 l_errmsg_length := length(l_error_msg);
130 l_msg_length := l_errmsg_length + length(l_msg_data);
131 IF l_msg_length < 4000 THEN
132 l_error_msg := l_error_msg || l_msg_data;
133 ELSE
134 l_error_msg := l_error_msg ||
135 substr(l_msg_data, 1, 4000-length(l_errmsg_length));
136 EXIT;
137 END IF;
138 END LOOP;
139
140 ELSE
141 l_error_msg := p_error_msg;
142 END IF;
143 select nvl(fnd_profile.value_specific('ASG_ENABLE_UPLOAD_EVENTS'),'N')
144 into l_prof_value from dual;
145 l_cp_run := asg_apply.is_conc_program_running;
146
147 if(l_prof_value = 'Y')
148 then
149 if(l_cp_run = 'N' )
150 then
151 if(asg_helper.check_is_log_enabled(g_stmt_level))
152 then
153 asg_helper.log('Raising oracle.apps.asg.upload.datadeferred',
154 'asg_defer',g_stmt_level);
155 end if;
156 l_bool_ret:=raise_row_deferred(p_user_name,p_tranid,p_pubitem,
157 p_sequence,l_error_msg);
158 if(asg_helper.check_is_log_enabled(g_stmt_level))
159 then
160 asg_helper.log('Done raising oracle.apps.asg.upload.datadeferred',
161 'asg_defer',g_stmt_level);
162 end if;
163 else
164 if(asg_helper.check_is_log_enabled(g_stmt_level))
165 then
166 asg_helper.log('Not Raising oracle.apps.asg.upload.datadeferred '
167 ||'since call is made from CP','asg_defer',
168 g_stmt_level);
169 end if;
170 end if;
171 else
172 if(asg_helper.check_is_log_enabled(g_stmt_level))
173 then
174 asg_helper.log('Not raising oracle.apps.asg.upload.datadeferred since '
175 ||' the profile '||' ASG_ENABLE_UPLOAD_EVENTS is not '
176 ||'set to ''Y''','asg_defer',g_stmt_level);
177 end if;
178 end if;
179 -- First try to update if that fails, insert.
180 -- #$% Should use table handler
181 UPDATE asg_deferred_traninfo
182 SET failures = failures +1, error_description = l_error_msg,
183 last_update_date = SYSDATE
184 WHERE device_user_name = p_user_name AND
185 deferred_tran_id = p_tranid AND
186 object_name = p_pubitem AND
187 sequence = p_sequence;
188
189 IF (SQL%ROWCOUNT = 0) THEN
190 OPEN c_resource_id(p_user_name);
191 FETCH c_resource_id INTO l_resource_id;
192 IF c_resource_id%NOTFOUND THEN
193 CLOSE c_resource_id;
194 x_return_status := FND_API.G_RET_STS_ERROR;
195 COMMIT;
196 return;
197 END IF;
198 CLOSE c_resource_id;
199 INSERT INTO asg_deferred_traninfo (DEVICE_USER_NAME,
200 RESOURCE_ID,
201 DEFERRED_TRAN_ID,
202 MOBILE_ERROR_ID,
203 ERROR_DESCRIPTION,
204 OBJECT_NAME,
205 SEQUENCE,
206 STATUS,
207 SYNC_TIME,
208 FAILURES,
209 LAST_UPDATE_DATE,
210 LAST_UPDATED_BY,
211 CREATION_DATE,
212 CREATED_BY)
213 VALUES (p_user_name,
214 l_resource_id,
215 p_tranid,
216 NULL,
217 l_error_msg,
218 p_pubitem,
219 p_sequence,
220 1,
221 NULL,
222 1,
223 SYSDATE,
224 1,
225 SYSDATE,
229 UPDATE asg_users_inqinfo
226 1);
227
228
230 SET deferred = 'Y', processed = 'I',
231 last_update_date = SYSDATE, last_updated_by = 1
232 WHERE device_user_name = p_user_name AND
233 tranid = p_tranid;
234
235 END IF;
236 IF(asg_helper.check_is_log_enabled(g_stmt_level))
237 THEN
238 asg_helper.log('defer_row: Deferred a row for user: '|| p_user_name ||
239 ' tranid: ' || p_tranid || ' publication item: ' ||
240 p_pubitem || ' and sequence: ' || p_sequence,
241 'asg_defer',g_stmt_level);
242 END IF;
243 COMMIT;
244 END defer_row;
245
246 -- Removes the deferred row from inq and removes references
247 -- to it as a deferred row.
248 PROCEDURE purge_deferred_rows(p_user_name IN VARCHAR2,
249 p_tranid IN NUMBER,
250 p_pubitem IN VARCHAR2,
251 x_return_status OUT NOCOPY VARCHAR2)
252 IS
253 inq_tbl_name VARCHAR2(30);
254 sql_string VARCHAR2(512);
255 BEGIN
256
257 x_return_status := FND_API.G_RET_STS_SUCCESS;
258 IF CSM_UTIL_PKG.is_html5_user(p_user_name) THEN
259 sql_string := 'DELETE FROM '|| csm_util_pkg.get_inq_tab(p_pubitem) ||
260 ' WHERE clid$$cs = :1 AND ' ||' tranid$$ = :2 AND ' ||' seqno$$ = :3';
261 ELSE
262
263 inq_tbl_name := 'CFM$' || p_pubitem;
264 sql_string := 'DELETE FROM '|| asg_base.G_OLITE_SCHEMA ||
265 '.' || inq_tbl_name ||
266 ' WHERE clid$$cs = :1 AND ' ||
267 ' tranid$$ =:2';
268 END IF;
269 IF(asg_helper.check_is_log_enabled(g_stmt_level))
270 THEN
271 asg_helper.log('purge_deferred_rows: SQL Command: ' || sql_string,
272 'asg_defer',g_stmt_level);
273 END IF;
274 BEGIN
275 EXECUTE IMMEDIATE sql_string
276 USING p_user_name, p_tranid;
277 EXCEPTION
278 WHEN OTHERS THEN
279 -- Ignore exceptions
280 x_return_status := FND_API.G_RET_STS_ERROR;
281 IF(asg_helper.check_is_log_enabled(g_err_level))
282 THEN
283 asg_helper.log('purge_deferred_rows: Exception: ',
284 'asg_defer', g_err_level);
285 END IF;
286 END;
287
288 -- Delete any reference in asg_deferred_traninfo
289 -- #$% Should use table handler
290 -- Should also optimize based on whether tranid is deferred or not
291 BEGIN
292 DELETE FROM asg_deferred_traninfo
293 WHERE device_user_name = p_user_name AND
294 deferred_tran_id = p_tranid AND
295 object_name = p_pubitem;
296 EXCEPTION
297 WHEN OTHERS THEN
298 -- Ignore exceptions
299 IF(asg_helper.check_is_log_enabled(g_err_level))
300 THEN
301 asg_helper.log('purge_deferred_rows: Exception: ' || SQLERRM,
302 'asg_defer', g_err_level);
303 END IF;
304 END;
305 IF(asg_helper.check_is_log_enabled(g_stmt_level))
306 THEN
307 asg_helper.log('purge_deferred_rows: ' ||
308 'Deleted deferred rows for user: '||
309 p_user_name ||
310 ' tranid: ' || p_tranid || ' publication item: ' ||
311 p_pubitem,
312 'asg_defer',g_stmt_level);
313 END IF;
314 END purge_deferred_rows;
315
316
317 -- Removes the deferred row from inq and removes references
318 -- to it as a deferred row.
319 PROCEDURE delete_deferred_row_internal(p_user_name IN VARCHAR2,
320 p_tranid IN NUMBER,
321 p_pubitem IN VARCHAR2,
322 p_sequence IN NUMBER,
323 x_return_status OUT NOCOPY VARCHAR2)
324 IS
325 inq_tbl_name VARCHAR2(30);
326 sql_string VARCHAR2(512);
327 BEGIN
328
329 x_return_status := FND_API.G_RET_STS_SUCCESS;
330 IF CSM_UTIL_PKG.is_html5_user(p_user_name) THEN
331 sql_string := 'DELETE FROM '|| csm_util_pkg.get_inq_tab(p_pubitem) ||
332 ' WHERE clid$$cs = :1 AND ' ||' tranid$$ = :2 AND ' ||' seqno$$ = :3';
333 ELSE
334 inq_tbl_name := 'CFM$' || p_pubitem;
335 sql_string := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||
336 '.' || inq_tbl_name ||
337 ' WHERE clid$$cs = :1 AND ' ||
338 ' tranid$$ = :2 AND ' ||
339 ' seqno$$ = :3';
340 END IF;
341
342 IF(asg_helper.check_is_log_enabled(g_stmt_level))
343 THEN
344 asg_helper.log('delete_deferred_row_internal: SQL Command: ' || sql_string,
345 'asg_defer',g_stmt_level);
346 END IF;
347 BEGIN
348 EXECUTE IMMEDIATE sql_string
349 USING p_user_name, p_tranid, p_sequence;
350 EXCEPTION
351 WHEN OTHERS THEN
352 -- Ignore exceptions
353 x_return_status := FND_API.G_RET_STS_ERROR;
354 IF(asg_helper.check_is_log_enabled(g_err_level))
355 THEN
356 asg_helper.log('delete_deferred_row_internal: Exception: ',
357 'asg_defer', g_err_level);
358 END IF;
359 END;
360
361 -- Delete any reference in asg_deferred_traninfo
362 -- #$% Should use table handler
363 -- Should also optimize based on whether tranid is deferred or not
364 BEGIN
365 DELETE FROM asg_deferred_traninfo
366 WHERE device_user_name = p_user_name AND
367 deferred_tran_id = p_tranid AND
368 object_name = p_pubitem AND
369 sequence = p_sequence;
370 EXCEPTION
371 WHEN OTHERS THEN
372 -- Ignore exceptions
373 IF(asg_helper.check_is_log_enabled(g_err_level))
374 THEN
375 asg_helper.log('delete_deferred_row_internal: Exception: ' || SQLERRM,
376 'asg_defer', g_err_level);
377 END IF;
378 END;
379 IF(asg_helper.check_is_log_enabled(g_stmt_level))
380 THEN
381 asg_helper.log('delete_deferred_row_internal: ' ||
382 'Deleted a deferred row for user: '||
383 p_user_name ||
384 ' tranid: ' || p_tranid || ' publication item: ' ||
385 p_pubitem || ' and sequence: ' || p_sequence,
386 'asg_defer',g_stmt_level);
387 END IF;
388
389 END delete_deferred_row_internal;
390
391 -- Removes the deferred row from inq and removes references
392 -- to it as a deferred row.
393 PROCEDURE delete_deferred_row(p_user_name IN VARCHAR2,
394 p_tranid IN NUMBER,
395 p_pubitem IN VARCHAR2,
396 p_sequence IN NUMBER,
397 x_return_status OUT NOCOPY VARCHAR2)
398 IS
399 l_deferred_row VARCHAR2(1);
400 BEGIN
401 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
402 ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) OR
403 ((p_pubitem = FND_API.G_MISS_CHAR) OR (p_pubitem IS NULL)) OR
404 ((p_sequence = FND_API.G_MISS_NUM) OR (p_sequence IS NULL)) THEN
405 x_return_status := FND_API.G_RET_STS_ERROR;
406 return;
407 END IF;
408
409 l_deferred_row := asg_defer.is_deferred(p_user_name, p_tranid,
410 p_pubitem, p_sequence);
411 -- Row is not deferred do not delete.
412 IF l_deferred_row = FND_API.G_FALSE THEN
413 IF(asg_helper.check_is_log_enabled(g_err_level))
414 THEN
415 asg_helper.log('delete_deferred_row: Row is not deferred. Returning...',
416 'asg_defer',g_err_level);
417 END IF;
418 return;
419 END IF;
420
421 delete_deferred_row_internal(p_user_name, p_tranid, p_pubitem,
422 p_sequence, x_return_status);
423
424 END delete_deferred_row;
425
426 -- Marks this records for delete in the client's Olite database.
427 PROCEDURE reject_row(p_user_name IN VARCHAR2,
428 p_tranid IN NUMBER,
429 p_pubitem IN VARCHAR2,
430 p_sequence IN NUMBER,
431 p_error_msg IN VARCHAR2,
432 x_return_status OUT NOCOPY VARCHAR2)
433 IS
434 l_ret_status BOOLEAN;
435 l_def_trans VARCHAR2(1);
436 BEGIN
437 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
438 ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) OR
439 ((p_pubitem = FND_API.G_MISS_CHAR) OR (p_pubitem IS NULL)) OR
440 ((p_sequence = FND_API.G_MISS_NUM) OR (p_sequence IS NULL)) THEN
441 x_return_status := FND_API.G_RET_STS_ERROR;
442 return;
443 END IF;
444
445 x_return_status := FND_API.G_RET_STS_SUCCESS;
446
447 l_ret_status := asg_download.markdirty(p_pubitem, p_user_name,
448 p_tranid, p_sequence);
449 IF (l_ret_status = FALSE) THEN
450 x_return_status := FND_API.G_RET_STS_ERROR;
451 IF(asg_helper.check_is_log_enabled(g_err_level))
452 THEN
453 asg_helper.log('reject_row: Error in call to markdirty',
454 'asg_defer', g_err_level);
455 END IF;
456 RETURN;
457 END IF;
458
459 -- Check if this transaction is deferred
460 l_def_trans := is_deferred(p_user_name, p_tranid, p_pubitem, p_sequence);
461 IF l_def_trans = FND_API.G_FALSE THEN
462 -- Delete the row from inq
463 asg_apply.delete_row(p_user_name, p_tranid, p_pubitem, p_sequence,
464 x_return_status);
465 END IF;
466 IF(asg_helper.check_is_log_enabled(g_stmt_level))
467 THEN
468 asg_helper.log('reject_row: rejected a row for user: '|| p_user_name ||
469 ' tranid: ' || p_tranid || ' publication item: ' ||
470 p_pubitem || ' and sequence: ' || p_sequence,
471 'asg_defer',g_stmt_level);
472 END IF;
473 END reject_row;
474
475 -- Returns FND_API.G_TRUE if the transaction is deferred
476 FUNCTION is_deferred(p_user_name IN VARCHAR2,
477 p_tranid IN NUMBER)
478 RETURN VARCHAR2 IS
479 l_retcode VARCHAR2(1);
480 l_user_name VARCHAR2(100);
481 CURSOR c_isdeferred(p_user_name VARCHAR2, p_tranid NUMBER) IS
482 SELECT device_user_name
483 FROM asg_users_inqinfo
484 WHERE device_user_name = p_user_name AND
485 tranid = p_tranid AND
486 deferred <> 'N';
487 BEGIN
488
489 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
490 ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) THEN
491 return FND_API.G_FALSE;
492 END IF;
493
494 l_retcode := FND_API.G_TRUE;
495 OPEN c_isdeferred(p_user_name, p_tranid);
496 FETCH c_isdeferred INTO l_user_name;
497 IF c_isdeferred%NOTFOUND THEN
498 l_retcode := FND_API.G_FALSE;
499 END IF;
500 CLOSE c_isdeferred;
501 IF(asg_helper.check_is_log_enabled(g_stmt_level))
502 THEN
503 asg_helper.log('is_deferred: ' || p_user_name || ' transaction: ' ||
504 p_tranid || ' is deferred: ' || l_retcode,
505 'asg_defer',g_stmt_level);
506 END IF;
507 return l_retcode;
508
509 END is_deferred;
510
511 -- Returns FND_API.G_TRUE if the record is deferred
512 FUNCTION is_deferred(p_user_name IN VARCHAR2,
513 p_tranid IN NUMBER,
514 p_pubitem IN VARCHAR2,
515 p_sequence IN NUMBER)
516 RETURN VARCHAR2 IS
517 l_retcode VARCHAR2(1);
518 l_user_name VARCHAR2(100);
519 CURSOR c_isdeferred(p_user_name VARCHAR2, p_tranid NUMBER,
520 p_pubitem VARCHAR2, p_sequence NUMBER) IS
521 SELECT device_user_name
522 FROM asg_deferred_traninfo
523 WHERE device_user_name = p_user_name AND
524 deferred_tran_id = p_tranid AND
525 object_name = p_pubitem AND
526 sequence = p_sequence;
527 BEGIN
528
529 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
530 ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) OR
531 ((p_pubitem = FND_API.G_MISS_CHAR) OR (p_pubitem IS NULL)) OR
532 ((p_sequence = FND_API.G_MISS_NUM) OR (p_sequence IS NULL)) THEN
533 return FND_API.G_FALSE;
534 END IF;
535
536 l_retcode := FND_API.G_TRUE;
537 OPEN c_isdeferred(p_user_name, p_tranid, p_pubitem, p_sequence);
538 FETCH c_isdeferred INTO l_user_name;
539 IF c_isdeferred%NOTFOUND THEN
540 l_retcode := FND_API.G_FALSE;
541 END IF;
542 CLOSE c_isdeferred;
543 IF(asg_helper.check_is_log_enabled(g_stmt_level))
544 THEN
545 asg_helper.log('is_deferred: ' || p_user_name || ' transaction: ' ||
546 p_tranid || ' and for publication item: ' || p_pubitem ||
547 ' and sequence: ' || p_sequence ||
548 ' is deferred: ' || l_retcode,
549 'asg_defer',g_stmt_level);
550 END IF;
551
552 return l_retcode;
553 END is_deferred;
554
555 -- Set transaction status to discarded
556 PROCEDURE discard_transaction(p_user_name IN VARCHAR2,
557 p_tranid IN NUMBER,
558 x_return_status OUT NOCOPY VARCHAR2)
559 IS
560 BEGIN
561
562 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
563 ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) THEN
564 x_return_status := FND_API.G_RET_STS_ERROR;
565 return;
566 END IF;
567
568 x_return_status := FND_API.G_RET_STS_SUCCESS;
569 UPDATE asg_users_inqinfo
570 SET deferred = 'D'
571 WHERE device_user_name = p_user_name AND
572 tranid = p_tranid;
573 COMMIT;
574 IF(asg_helper.check_is_log_enabled(g_stmt_level))
575 THEN
576 asg_helper.log('discard_transaction: Setting user: '||p_user_name ||
577 ' transaction: ' || p_tranid || ' to discarded status.',
578 'asg_defer',g_stmt_level);
579 END IF;
580 END discard_transaction;
581
582 -- Discard the specified deferred row
583 PROCEDURE discard_transaction(p_user_name IN VARCHAR2,
584 p_tranid IN NUMBER,
585 p_pubitem IN VARCHAR2,
586 p_sequence IN NUMBER,
587 x_return_status OUT NOCOPY VARCHAR2,
588 p_commit_flag IN BOOLEAN)
589 IS
590 l_def_count PLS_INTEGER;
591 CURSOR c_deferred_discarded (p_user_name VARCHAR2, p_tranid NUMBER) IS
592 SELECT count(*) count
593 FROM asg_deferred_traninfo
594 WHERE device_user_name = p_user_name AND
595 deferred_tran_id = p_tranid;
596 BEGIN
597 x_return_status := FND_API.G_RET_STS_SUCCESS;
598 delete_deferred_row(p_user_name, p_tranid, p_pubitem,
599 p_sequence, x_return_status);
600
601 OPEN c_deferred_discarded(p_user_name, p_tranid);
602 FETCH c_deferred_discarded INTO l_def_count;
603 CLOSE c_deferred_discarded;
604
605 -- If All the deferred records are discarded
606 -- then set the state to discarded
607 IF l_def_count = 0 THEN
608 UPDATE asg_users_inqinfo
609 SET deferred = 'D'
610 WHERE device_user_name = p_user_name AND
611 tranid = p_tranid;
612 END IF;
613
614 IF p_commit_flag THEN
615 COMMIT;
616 END IF;
617 END discard_transaction;
618
619 -- Reapply the given transaction
620 PROCEDURE reapply_transaction(p_user_name IN VARCHAR2,
621 p_tranid IN NUMBER,
622 x_return_status OUT NOCOPY VARCHAR2,
623 p_commit_flag IN BOOLEAN)
624 IS
625 counter PLS_INTEGER;
626 sql_string VARCHAR2(4000);
627 l_pub_handler asg_pub.wrapper_name%type;
628 l_pubname VARCHAR2(30);
629 l_pubitems_tbl asg_apply.vc2_tbl_type;
630 l_def_trans VARCHAR2(1);
631 l_return_status VARCHAR2(1);
632 l_pubs_tbl asg_apply.vc2_tbl_type;
633 l_pubhandlers_tbl asg_apply.vc2_tbl_type;
634 l_def_count NUMBER;
635 l_user_id NUMBER;
636 l_resp_id NUMBER;
637 l_app_id NUMBER;
638 l_orig_user_id NUMBER;
639 l_orig_resp_id NUMBER;
640 l_orig_app_id NUMBER;
641 CURSOR c_pub_wrapper(p_user_name VARCHAR2, p_tranid NUMBER) IS
642 SELECT distinct a.wrapper_name, a.name
643 FROM asg_pub a, asg_pub_item b, asg_deferred_traninfo c
644 WHERE device_user_name = p_user_name AND
645 deferred_tran_id = p_tranid AND
646 c.object_name = b.name AND
647 b.pub_name = a.name
648 ORDER BY a.name;
649 CURSOR c_deferred_processed (p_user_name VARCHAR2, p_tranid NUMBER) IS
650 SELECT count(*) count
651 FROM asg_deferred_traninfo
652 WHERE device_user_name = p_user_name AND
653 deferred_tran_id = p_tranid AND
654 status <> 0;
655 BEGIN
656 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
657 ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) THEN
658 x_return_status := FND_API.G_RET_STS_ERROR;
659 return;
660 END IF;
661
662 IF CSM_UTIL_PKG.is_html5_user(p_user_name) THEN
663 CSM_HTML5_PKG.reapply_transaction(p_user_name,p_tranid,x_return_status,p_commit_flag);
664 RETURN;
665 END IF;
666
667 -- Check if this transaction is deferred
668 l_def_trans := is_deferred(p_user_name, p_tranid);
669 IF l_def_trans = FND_API.G_FALSE THEN
670 x_return_status := FND_API.G_RET_STS_ERROR;
671 return;
672 END IF;
673
674 x_return_status := FND_API.G_RET_STS_SUCCESS;
675 -- Get all the publications that have deferred pub items
676 counter :=1;
677 FOR cpw in c_pub_wrapper(p_user_name, p_tranid) LOOP
678 l_pubhandlers_tbl(counter) := cpw.wrapper_name;
679 l_pubs_tbl(counter) := cpw.name;
680 counter := counter +1;
681 END LOOP;
682
683 IF counter >1 THEN
684 FOR curr_index in 1..l_pubs_tbl.count LOOP
685
686 l_pubname := l_pubs_tbl(curr_index);
687 l_pub_handler := l_pubhandlers_tbl(curr_index);
688 IF(asg_helper.check_is_log_enabled(g_stmt_level))
689 THEN
690 asg_helper.log('reapply_transaction: user: ' || p_user_name ||
691 ' transaction id: ' || p_tranid ||
692 ' current pub : ' || l_pubname ||
693 ' current pub handler: ' || l_pub_handler,
694 'asg_defer',g_stmt_level);
695 END IF;
696 l_pubitems_tbl := asg_apply.g_empty_vc2_tbl;
697 asg_apply.get_all_pub_items(p_user_name, p_tranid, l_pubname,
698 l_pubitems_tbl, l_return_status);
699 -- Check if there is any data for this publication
700 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) AND
701 (l_pubitems_tbl.count >0) THEN
702 IF(asg_helper.check_is_log_enabled(g_stmt_level))
703 THEN
704 asg_helper.log('reapply_transaction: Calling handler package',
705 'asg_defer',g_stmt_level);
706 END IF;
707 sql_string := 'begin ' ||
708 l_pub_handler || '.apply_client_changes( ''' ||
709 p_user_name || ''',' || p_tranid || '); ' ||
710 'end;';
711 BEGIN
712 l_orig_user_id := fnd_global.user_id();
713 l_orig_resp_id := fnd_global.resp_id();
714 l_orig_app_id := fnd_global.resp_appl_id();
715
716 SELECT user_id INTO l_user_id
717 FROM asg_user
718 WHERE user_name = p_user_name;
719
720 SELECT responsibility_id, app_id INTO l_resp_id, l_app_id
721 FROM asg_user_pub_resps
722 WHERE user_name = p_user_name AND
723 pub_name = l_pubname;
724
725 fnd_global.apps_initialize(l_user_id, l_resp_id, l_app_id);
726 EXECUTE IMMEDIATE sql_string;
727 fnd_global.apps_initialize(l_orig_user_id, l_orig_resp_id,
728 l_orig_app_id);
729 EXCEPTION
730 WHEN OTHERS THEN
731 IF(asg_helper.check_is_log_enabled(g_err_level))
732 THEN
733 asg_helper.log('reapply_transaction: Exception in ' ||
734 'wrapper call. Check if valid wrapper exists',
735 'asg_defer',g_err_level);
736 END IF;
737 x_return_status := FND_API.G_RET_STS_SUCCESS;
738 fnd_global.apps_initialize(l_orig_user_id, l_orig_resp_id,
739 l_orig_app_id);
740 return;
741 END;
742 ELSE
743 IF(asg_helper.check_is_log_enabled(g_stmt_level))
744 THEN
745 asg_helper.log('No pubitems from publication: ' ||
746 l_pubname || ' to process',
747 'asg_defer',g_stmt_level);
748 END IF;
749 END IF;
750 END LOOP;
751 END IF;
752
753 OPEN c_deferred_processed(p_user_name, p_tranid);
754 FETCH c_deferred_processed INTO l_def_count;
755 CLOSE c_deferred_processed;
756 -- All the deferred records are processed
757 IF l_def_count = 0 THEN
758 UPDATE asg_users_inqinfo
759 SET deferred = 'S'
760 WHERE device_user_name = p_user_name AND
761 tranid = p_tranid;
762 END IF;
763
764 IF p_commit_flag THEN
765 COMMIT;
766 END IF;
767 IF(asg_helper.check_is_log_enabled(g_stmt_level))
768 THEN
769 asg_helper.log('reapply_transaction: Done reapplying the transaction',
770 'asg_defer',g_stmt_level);
771 END IF;
772 END reapply_transaction;
773
774 -- Purge all the inq entries
775 PROCEDURE purge_transaction(p_user_name IN VARCHAR2,
776 p_tranid IN NUMBER,
777 x_return_status OUT NOCOPY VARCHAR2)
778 IS
779 l_def_trans VARCHAR2(1);
780 l_curr_pubitem VARCHAR2(30);
781 l_pubitems_tbl asg_apply.vc2_tbl_type;
782 l_return_status VARCHAR2(1);
783 BEGIN
784 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
785 ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) THEN
786 x_return_status := FND_API.G_RET_STS_ERROR;
787 return;
788 END IF;
789
790 -- Check if this transaction is deferred
791 l_def_trans := is_deferred(p_user_name, p_tranid);
792 IF l_def_trans = FND_API.G_FALSE THEN
793 x_return_status := FND_API.G_RET_STS_ERROR;
794 return;
795 END IF;
796 IF(asg_helper.check_is_log_enabled(g_stmt_level))
797 THEN
798 asg_helper.log('purge_transaction: Purging user: ' || p_user_name ||
799 ' transaction: ' || p_tranid,
800 'asg_defer',g_stmt_level);
801 END IF;
802 x_return_status := FND_API.G_RET_STS_SUCCESS;
803 asg_apply.get_all_pub_items(p_user_name, p_tranid,
804 l_pubitems_tbl, l_return_status);
805 -- Check if there is any data for this publication
806 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) AND
807 (l_pubitems_tbl.count >0) THEN
808 FOR curr_index in 1..l_pubitems_tbl.count LOOP
809 l_curr_pubitem := l_pubitems_tbl(curr_index);
810 IF(asg_helper.check_is_log_enabled(g_stmt_level))
811 THEN
812 asg_helper.log('purge_transaction: Purging pub item : '||
813 l_curr_pubitem || ' entries',
814 'asg_defer',g_stmt_level);
815 END IF;
816 purge_deferred_rows(p_user_name, p_tranid,
817 l_curr_pubitem, l_return_status);
818 END LOOP;
819 END IF;
820
821 UPDATE asg_users_inqarchive
822 SET processed = 'Y', deferred = 'Y',
823 last_update_date = SYSDATE, last_updated_by = 1
824 WHERE device_user_name = p_user_name AND
825 tranid = p_tranid;
826
827 DELETE FROM asg_users_inqinfo
828 WHERE device_user_name = p_user_name AND tranid = p_tranid;
829 COMMIT;
830 IF(asg_helper.check_is_log_enabled(g_stmt_level))
831 THEN
832 asg_helper.log('purge_transaction: Done purging all items in this transaction',
833 'asg_defer',g_stmt_level);
834 END IF;
835 END purge_transaction;
836
837 -- Delete rows in asg_deferred_traninfo/asg_users_inqinfo with no data in INQ.
838 PROCEDURE delete_deferred(p_status OUT NOCOPY VARCHAR2,
839 p_message OUT NOCOPY VARCHAR2)
840 IS
841 CURSOR c_deferred_lines
842 IS
843 select distinct def.object_name,pub.enabled
844 from asg_deferred_traninfo def,
845 asg_pub_item pub
846 where pub.item_id = def.object_name;
847
848 l_sql VARCHAR2(512);
849 l_row_count NUMBER;
850 l_inq_table_name VARCHAR2(128);
851 BEGIN
852
853
854 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
855 asg_helper.log('delete_deferred: Entering asg_defer.delete_deferred.',
856 'asg_defer',g_stmt_level);
857 END IF;
858
859 FOR cdl in c_deferred_lines LOOP
860
861 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
862 asg_helper.log('delete_deferred: Processing object: ' || cdl.object_name,
863 'asg_defer',g_stmt_level);
864 END IF;
865 IF cdl.enabled = 'Y' THEN
866
867 l_inq_table_name := asg_base.G_OLITE_SCHEMA || '.CFM$' || cdl.object_name;
868 l_sql := 'DELETE FROM asg_deferred_traninfo ' ||
869 'WHERE object_name = :1 AND ' ||
870 '(device_user_name, deferred_tran_id, sequence) NOT IN ' ||
871 ' (SELECT clid$$cs, tranid$$, seqno$$ ' ||
872 ' FROM ' || l_inq_table_name || ' )';
873
874
875 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
876 asg_helper.log('delete_deferred: SQL Command: ' || l_sql,
877 'asg_defer',g_stmt_level);
878 END IF;
879
880 EXECUTE IMMEDIATE l_sql USING cdl.object_name;
881 l_row_count := SQL%ROWCOUNT;
882
883 ELSE --For disable pub items blindly delete from asg deferred traninfo table
884 DELETE FROM asg_deferred_traninfo WHERE object_name = cdl.object_name;
885 l_row_count := SQL%ROWCOUNT;
886
887 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
888 asg_helper.log('delete_deferred: for the PIV that is disabled ' || cdl.object_name,
889 'asg_defer',g_stmt_level);
890 END IF;
891
892 END IF;
893
894 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
895 asg_helper.log('delete_deferred: : Deleted ' || l_row_count || ' row(s)',
896 'asg_defer',g_stmt_level);
897 END IF;
898
899 -- Commit after each object.
900 COMMIT;
901
902 END LOOP;
903
904 -- Delete any deferred headers
905 DELETE FROM asg_users_inqinfo
906 WHERE (device_user_name, tranid) NOT IN
907 (SELECT device_user_name, deferred_tran_id
908 FROM asg_deferred_traninfo);
909
910 COMMIT;
911
912 p_status := 'Fine';
913 p_message := 'Purging deferred transaction metadata completed successfully.';
914 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
915 asg_helper.log('delete_deferred: Exiting asg_defer.delete_deferred.',
916 'asg_defer',g_stmt_level);
917 END IF;
918 EXCEPTION
919 WHEN OTHERS THEN
920 p_status := 'Error';
921 p_message := 'Error deleting deferred transaction metadata.';
922 IF(asg_helper.check_is_log_enabled(g_stmt_level)) THEN
923 asg_helper.log('delete_deferred: Error Message: ' || SQLERRM,
924 'asg_defer',g_stmt_level);
925 END IF;
926
927 END delete_deferred;
928
929 END asg_defer;