[Home] [Help]
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
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
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,
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
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
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
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;
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;
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;
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
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
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
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);
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 || ' )';
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
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,
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';