1 PACKAGE BODY pay_progress_pkg AS
2 -- $Header: pyprogpk.pkb 120.1.12020000.2 2012/07/05 02:30:06 amnaraya ship $
3 /*
4 *** ------------------------------------------------------------------------+
5 *** Program: pay_progress_pkg (Package Body)
6 ***
7 *** Change History
8 ***
9 *** Date Changed By Version Description of Change
10 *** --------- ---------- ------- -----------------------------------------------------------+
11 *** 23 FEB 2010 priupadh 120.0.12010000.2 Bug 9274304 Modified cursor csr_action added dbms_lob.substr
12 *** to comments column .
13 *** -------------------------------------------------------------------------------------------------------+
14 */
15 -- Buffer previous progress update requests.
16 -- We may use this in future to create a progress summary graph
17 -- e.g. showing the processing rate over time using the timestamp field
18 -- which holds the time that the progress update was requested in
19 -- 100ths of a second.
20 --
21 TYPE progress_info_t IS TABLE OF progress_info_r INDEX BY BINARY_INTEGER;
22 --
23 -- Global information about the progress info buffer table and the last
24 -- progress update we requested.
25 --
26 g_num_samples NUMBER;
27 g_action_id NUMBER;
28 g_sample_list progress_info_t;
29 g_first_stamp NUMBER;
30 --
31 -- Get the progress of the specified payroll action, or get an update
32 -- on the last action we explicitly requested info for.
33 -- Buffer the progress information in a global table before sending it back.
34 --
35 FUNCTION current_progress(p_payroll_action_id IN NUMBER DEFAULT NULL) RETURN progress_info_r IS
36 --
37 -- Calculate counts of records for the action based on their status
38 CURSOR csr_summary(cp_id IN NUMBER) IS
39 SELECT NVL(SUM(DECODE(action_status,'C',1,'S',1,0)),0) completed,
40 NVL(SUM(DECODE(action_status,'E',1,0)),0) in_error,
41 NVL(SUM(DECODE(action_status,'M',1,0)),0) marked_for_retry,
42 NVL(SUM(DECODE(action_status,'U',1,0)),0) unprocessed
43 FROM pay_assignment_actions
44 WHERE payroll_action_id = cp_id
45 AND source_action_id IS NULL;
46 --
47 -- Determine various statistics used for calculating timing information
48 CURSOR csr_time(cp_id IN NUMBER,cp_done IN VARCHAR2) IS
49 SELECT ppa.creation_date start_time,
50 NVL(fcr.actual_completion_date,
51 DECODE(cp_done,'Y',ppa.last_update_date,SYSDATE)
52 ) end_time,
53 SYSDATE current_time
54 FROM fnd_concurrent_requests fcr,pay_payroll_actions ppa
55 WHERE fcr.request_id(+) = ppa.request_id
56 AND ppa.payroll_action_id = cp_id;
57 --
58 -- Get the information we need to pass to the existing function
59 -- in the pay_payroll_actions_pkg package, which works out the
60 -- name of the run.
61 CURSOR csr_action(cp_id IN NUMBER) IS
62 SELECT payroll_action_id,
63 action_type,
64 action_status,
65 consolidation_set_id,
66 display_run_number,
67 element_set_id,
68 assignment_set_id,
69 effective_date,
70 dbms_lob.substr(comments,4000,1) comments_1
71 FROM pay_payroll_actions
72 WHERE payroll_action_id = cp_id;
73 --
74 l_progress progress_info_r;
75 l_action csr_action%ROWTYPE;
76 l_end_time DATE;
77 l_current_time DATE;
78 l_done VARCHAR2(1);
79 --
80 BEGIN
81 --
82 -- Timestamp this progress update in 100ths of a second
83 IF g_first_stamp IS NULL THEN
84 g_first_stamp := dbms_utility.get_time;
85 l_progress.timestamp := 0;
86 ELSE
87 l_progress.timestamp := dbms_utility.get_time - g_first_stamp;
88 END IF;
89 --
90 -- If we didn't pass an action ID and we haven't previously
91 -- passed one then we can't do anything more
92 IF p_payroll_action_id IS NULL AND g_action_id = -1 THEN
93 RETURN l_progress;
94 END IF;
95 --
96 -- If we passed an action ID and it's different to the
97 -- last one we passed then reset the result buffer table
98 IF p_payroll_action_id IS NOT NULL
99 AND p_payroll_action_id <> g_action_id
100 THEN
101 g_action_id := p_payroll_action_id;
102 g_num_samples := 0;
103 g_sample_list.Delete;
104 ELSE
105 g_num_samples := g_num_samples + 1;
106 END IF;
107 --
108 -- Fetch the count of records processed into the progress record
109 OPEN csr_summary(g_action_id);
110 FETCH csr_summary
111 INTO l_progress.completed,
112 l_progress.in_error,
113 l_progress.marked_for_retry,
114 l_progress.unprocessed;
115 CLOSE csr_summary;
116 --
117 IF l_progress.unprocessed + l_progress.marked_for_retry > 0 THEN
118 l_done := 'N';
119 ELSE
120 l_done := 'Y';
121 END IF;
122 --
123 -- Get the start time and other intermediate timing info
124 OPEN csr_time(g_action_id,l_done);
125 FETCH csr_time
126 INTO l_progress.start_time,
127 l_end_time,
128 l_current_time;
129 CLOSE csr_time;
130 --
131 --Added check 'l_done' for bug 14068750
132 -- In multi-threaded mode, actual_completion_date is getting populated even before the completion of the process.
133 -- So l_current_time is wrongly updated to l_end_time which is actual_completion_date from csr_time
134
135 IF l_done = 'Y' and l_end_time IS NOT NULL THEN
136 l_current_time := l_end_time;
137 END IF;
138 l_progress.elapsed_time := (l_current_time - l_progress.start_time) * (24*60*60);
139 IF l_progress.completed + l_progress.in_error > 0 THEN
140 l_progress.process_rate := l_progress.elapsed_time / (l_progress.completed + l_progress.in_error);
141 l_progress.time_remaining := (((l_progress.unprocessed + l_progress.marked_for_retry) * l_progress.process_rate) / (60*60));
142 l_progress.completion_time := l_current_time + (l_progress.time_remaining / 24);
143 l_progress.time_remaining := ROUND(l_progress.time_remaining);
144 END IF;
145 l_progress.time_per_assignment := ROUND(l_progress.process_rate);
146 IF l_progress.process_rate > 0 THEN
147 l_progress.assignments_per_hour := ROUND((60*60)/l_progress.process_rate);
148 END IF;
149 --
150 OPEN csr_action(g_action_id);
151 FETCH csr_action INTO l_action;
152 CLOSE csr_action;
153 --
154 l_progress.run_description := pay_payroll_actions_pkg.v_name(
155 l_action.payroll_action_id,
156 l_action.action_type,
157 l_action.consolidation_set_id,
158 l_action.display_run_number,
159 l_action.element_set_id,
160 l_action.assignment_set_id,
161 l_action.effective_date
162 );
163 l_progress.message := l_action.comments_1;
164 --
165 -- Work out the total percent complete and in error
166 if (l_progress.in_error + l_progress.completed +
167 l_progress.marked_for_retry + l_progress.unprocessed) > 0 then
168 l_progress.percent_complete := ((l_progress.in_error +
169 l_progress.completed) / (l_progress.in_error + l_progress.completed +
170 l_progress.marked_for_retry + l_progress.unprocessed)) * 100;
171 l_progress.percent_in_error := (l_progress.in_error /
172 (l_progress.in_error + l_progress.completed +
173 l_progress.marked_for_retry + l_progress.unprocessed)) * 100;
174 else
175 l_progress.percent_complete := 0;
176 l_progress.percent_in_error := 0;
177 end if;
178 --
179 g_sample_list(g_num_samples) := l_progress;
180 RETURN l_progress;
181 END current_progress;
182 --
183 BEGIN
184 g_num_samples := 0;
185 g_action_id := -1;
186 g_sample_list.Delete;
187 END pay_progress_pkg;
[Home] [Help]