1 PACKAGE BODY ont_activity_sum_order_purge AS
2 /* $Header: OEXACSUB.pls 120.2 2005/11/10 04:20:14 ddey noship $ */
3
4 PROCEDURE activity_summarizer IS
5
6 l_count number := 0;
7 l_temp_name varchar2(100);
8 l_requested_by fnd_concurrent_requests.requested_by%TYPE;
9 l_count_logged_user number := 0;
10
11 CURSOR c_concurrent_request IS
12 SELECT count(*) total
13 FROM fnd_concurrent_requests
14 WHERE phase_code = 'C'
15 AND program_application_id = 660
16 AND concurrent_program_id = (SELECT concurrent_program_id
17 FROM fnd_concurrent_programs
18 WHERE concurrent_program_name = 'ORDPUR'
19 AND application_id = 660)
20 AND requested_by = FND_GLOBAL.USER_ID;
21
22
23
24 BEGIN
25
26 --
27 -- FND_CONCURRENT_REQUEST table count
28 -- The Number of times the Order Purge Concurrect Request is Submitted
29 --
30
31 l_temp_name := FND_MESSAGE.GET_STRING('ONT', 'ONT_CONC_REQUESTS');
32
33
34 SELECT count(*)
35 INTO l_count
36 FROM fnd_concurrent_requests
37 WHERE phase_code = 'C'
38 AND program_application_id = 660
39 AND concurrent_program_id = (SELECT concurrent_program_id
40 FROM fnd_concurrent_programs
41 WHERE concurrent_program_name = 'ORDPUR'
42 AND application_id = 660);
43
44 -- Insert this name, value pair in summarizer table by using summarizer API 'insert_row'
45
46 fnd_conc_summarizer.insert_row(l_temp_name, to_char(l_count));
47
48
49 --
50 -- FND_CONCURRENT_REQUEST
51 -- The Number of times the Purge Order Request was run by logged in user.
52 --
53
54 l_temp_name := fnd_message.get_string('ONT', 'ONT_PURGE_ORD_USER_COUNT');
55
56 OPEN c_concurrent_request;
57 FETCH c_concurrent_request INTO l_count_logged_user;
58
59 -- Insert this name, value pair in summarizer table by using summarizer API 'insert_row'
60 fnd_conc_summarizer.insert_row(l_temp_name, to_char(l_count_logged_user));
61 CLOSE c_concurrent_request;
62
63 --
64 -- FND_CONCURRENT_PROCESSES
65 -- The Number Concurrect Process is Submitted
66 --
67
68
69 l_temp_name := fnd_message.get_string('ONT', 'ONT_CONC_PROCESSES');
70
71
72 SELECT COUNT (*)
73 INTO l_count
74 FROM fnd_concurrent_processes
75 WHERE process_status_code NOT IN ('A', 'C', 'T', 'M')
76 AND concurrent_process_id IN
77 (SELECT controlling_manager
78 FROM fnd_concurrent_requests
79 WHERE phase_code = 'C'
80 AND program_application_id = 660
81 AND concurrent_program_id =
82 (SELECT concurrent_program_id
83 FROM fnd_concurrent_programs
84 WHERE concurrent_program_name = 'ORDPUR'
85 AND application_id = 660));
86
87
88 -- Insert this name, value pair in summarizer table by using summarizer API 'insert_row'
89
90 fnd_conc_summarizer.insert_row(l_temp_name, to_char(l_count));
91
92 --
93 -- FND_CRM_HISTORY
94 -- The Conflict Resolution History Count
95 --
96
97 l_temp_name := fnd_message.get_string('FND', 'FND_CRM_HISTORY');
98
99 SELECT count(*)
100 INTO l_count
101 FROM fnd_crm_history
102 WHERE work_start < sysdate -1 ;
103
104 fnd_conc_summarizer.insert_row(l_temp_name, to_char(l_count));
105
106 --
107 -- FND_TM_EVENTS
108 -- The Transaction Management Events Count
109 --
110
111 l_temp_name := fnd_message.get_string('FND', 'FND_TM_EVENTS');
112
113 SELECT COUNT (*)
114 INTO l_count
115 FROM fnd_tm_events
116 WHERE TIMESTAMP < SYSDATE - 1
117 AND (program_application_id, concurrent_program_id) IN
118 (SELECT application_id, concurrent_program_id
119 FROM fnd_concurrent_programs
120 WHERE concurrent_program_name = 'ORDPUR'
121 AND application_id = 660);
122
123
124 fnd_conc_summarizer.insert_row(l_temp_name, to_char(l_count));
125
126 EXCEPTION WHEN OTHERS THEN
127 null;
128 END activity_summarizer;
129
130 END ont_activity_sum_order_purge;