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