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