[Home] [Help]
PACKAGE BODY: APPS.PAY_PST_UTILS
Source
1 PACKAGE BODY pay_pst_utils AS
2 /* $Header: pypstutl.pkb 120.0 2006/06/07 02:14:56 exjones noship $ */
3 --
4 g_last_batch_id NUMBER := -1;
5 g_last_api_id NUMBER := -1;
6 g_last_time NUMBER := dbms_utility.get_time;
7 g_cache_age NUMBER := 1000; -- In hundredths of a second
8 --
9 g_batch_status VARCHAR2(30) := 'BATCH_STATUS_UNKNOWN';
10 g_percent_complete NUMBER := -1;
11 g_process_status VARCHAR2(30) := 'PROCESS_STATUS_UNKNOWN';
12 g_total_lines NUMBER := -1;
13 g_can_update VARCHAR2(30) := 'CAN_UPDATE_UNKNOWN';
14 g_date_started DATE := hr_general.start_of_time;
15 g_parameter_group VARCHAR2(240) := 'PARAMETER_GROUP_UNKNOWN';
16 g_validate_only VARCHAR2(30) := 'VALIDATE_ONLY_UNKNOWN';
17 g_date_completed DATE := hr_general.end_of_time;
18 g_completion_text VARCHAR2(2000) := 'COMPLETION_TEXT_UNKNOWN';
19 g_process_phase VARCHAR2(240) := 'PROCESS_PHASE_UNKNOWN';
20 g_last_batch_exception VARCHAR2(2000) := 'LAST_BATCH_EXCEPTION_UNKNOWN';
21 g_display_view_errors NUMBER := 0;
22 g_last_process_date DATE := hr_general.start_of_time;
23 --
24 g_module_name VARCHAR2(240) := 'MODULE_NAME_UNKNOWN';
25 g_module_status VARCHAR2(30) := 'MODULE_STATUS_UNKNOWN';
26 g_api_unprocessed NUMBER := -1;
27 g_api_validated NUMBER := -1;
28 g_api_error NUMBER := -1;
29 g_api_complete NUMBER := -1;
30 --
31 PROCEDURE cache_batch_data(p_batch_id IN NUMBER) IS
32 --
33 CURSOR csr_count(cp_batch_id IN NUMBER) IS
34 SELECT SUM(DECODE(bl.line_status,'U',1,0)),
35 SUM(DECODE(bl.line_status,'V',1,0)),
36 SUM(DECODE(bl.line_status,'E',1,0)),
37 SUM(DECODE(bl.line_status,'C',1,0))
38 FROM hr_pump_batch_lines bl
39 WHERE bl.batch_id = cp_batch_id;
40 --
41 CURSOR csr_except(cp_batch_id IN NUMBER) IS
42 SELECT be.exception_text
43 FROM hr_pump_batch_exceptions be
44 WHERE be.source_type = 'BATCH_HEADER'
45 AND be.source_id = cp_batch_id
46 AND be.exception_sequence = (
47 SELECT MAX(bx.exception_sequence)
48 FROM hr_pump_batch_exceptions bx
49 WHERE bx.source_type = 'BATCH_HEADER'
50 AND bx.source_id = cp_batch_id
51 );
52 --
53 CURSOR csr_process(cp_batch_id IN NUMBER) IS
54 SELECT cr.actual_start_date,
55 nvl(pg.action_parameter_group_name,hr_general.decode_lookup('NAME_TRANSLATIONS','DEFAULT')),
56 yn.meaning,
57 cr.actual_completion_date,
58 cr.completion_text,
59 fl.meaning,
60 cr.phase_code,
61 cr.request_date
62 FROM fnd_concurrent_requests cr,
63 fnd_concurrent_programs cp,
64 fnd_application fa,
65 (select action_parameter_group_name,to_char(action_parameter_group_id) action_parameter_group_id from pay_action_parameter_groups) pg,
66 fnd_lookups fl,
67 fnd_lookups yn
68 WHERE fa.application_short_name = 'PER'
69 AND cp.application_id = fa.application_id
70 AND cp.concurrent_program_name = 'DATAPUMP'
71 AND cr.program_application_id = cp.application_id
72 AND cr.concurrent_program_id = cp.concurrent_program_id
73 AND cr.argument1 = to_char(cp_batch_id)
74 AND pg.action_parameter_group_id (+)= cr.argument3
75 AND fl.lookup_type = 'CP_PHASE_CODE'
76 AND fl.enabled_flag = 'Y'
77 AND fl.lookup_code = cr.phase_code
78 AND yn.lookup_type = 'YES_NO'
79 AND yn.enabled_flag = 'Y'
80 AND yn.lookup_code = cr.argument2
81 AND cr.request_id = (
82 SELECT MAX(crx.request_id)
83 FROM fnd_concurrent_requests crx,
84 fnd_concurrent_programs cpx,
85 fnd_application fax
86 WHERE fax.application_short_name = 'PER'
87 AND cpx.application_id = fax.application_id
88 AND cpx.concurrent_program_name = 'DATAPUMP'
89 AND crx.program_application_id = cpx.application_id
90 AND crx.concurrent_program_id = cpx.concurrent_program_id
91 AND crx.argument1 = to_char(cp_batch_id)
92 );
93 --
94 l_unprocessed NUMBER;
95 l_validated NUMBER;
96 l_error NUMBER;
97 l_complete NUMBER;
98 --
99 l_phase VARCHAR2(30);
100 --
101 BEGIN
102 IF p_batch_id <> g_last_batch_id OR
103 g_last_time + g_cache_age < dbms_utility.get_time
104 THEN
105 g_last_time := dbms_utility.get_time;
106 g_last_batch_id := p_batch_id;
107 --
108 OPEN csr_count(p_batch_id);
109 FETCH csr_count
110 INTO l_unprocessed,
111 l_validated,
112 l_error,
113 l_complete;
114 CLOSE csr_count;
115 --
116 OPEN csr_except(p_batch_id);
117 FETCH csr_except
118 INTO g_last_batch_exception;
119 --
120 IF csr_except%NOTFOUND THEN
121 g_last_batch_exception := NULL;
122 END IF;
123 CLOSE csr_except;
124 --
125 OPEN csr_process(p_batch_id);
126 FETCH csr_process
127 INTO g_date_started,
128 g_parameter_group,
129 g_validate_only,
130 g_date_completed,
131 g_completion_text,
132 g_process_phase,
133 l_phase,
134 g_last_process_date;
135 --
136 IF csr_process%NOTFOUND THEN
137 g_date_started := NULL;
138 g_parameter_group := NULL;
139 g_validate_only := NULL;
140 g_date_completed := NULL;
141 g_completion_text := NULL;
142 g_process_phase := NULL;
143 l_phase := NULL;
144 g_last_process_date := NULL;
145 END IF;
146 CLOSE csr_process;
147 --
148 g_total_lines := l_unprocessed + l_validated + l_error + l_complete;
149 g_percent_complete := TRUNC(((l_complete + l_validated + l_error) * 100) / g_total_lines,2);
150 --
151 IF l_phase = 'R' THEN
152 g_batch_status := 'BATCH_STATUS_INPROGRESS';
153 ELSIF l_error > 0 THEN
154 g_batch_status := 'BATCH_STATUS_ERROR';
155 ELSIF l_unprocessed > 0 AND l_validated + l_error + l_complete = 0 THEN
156 g_batch_status := 'BATCH_STATUS_NOTSTARTED';
157 ELSIF l_validated > 0 AND l_unprocessed + l_error + l_complete = 0 THEN
158 g_batch_status := 'BATCH_STATUS_VALIDATED';
159 ELSIF l_complete > 0 AND l_unprocessed + l_error + l_validated = 0 THEN
160 g_batch_status := 'BATCH_STATUS_COMPLETE';
161 ELSIF l_unprocessed + l_validated + l_error + l_complete = 0 THEN
162 g_batch_status := 'BATCH_STATUS_EMPTY';
163 ELSE
164 g_batch_status := 'BATCH_STATUS_PARTIAL';
165 END IF;
166 --
167 IF l_validated + l_error + l_complete + l_unprocessed = 0 THEN
168 g_process_status := 'PROCESS_STATUS_EMPTY';
169 ELSIF l_unprocessed > 0 AND l_validated + l_error + l_complete = 0 THEN
170 g_process_status := 'PROCESS_STATUS_NOTSTARTED';
171 ELSE
172 g_process_status := 'PROCESS_STATUS_PROCESSED';
173 END IF;
174 --
175 IF g_batch_status = 'BATCH_STATUS_ERROR' THEN
176 g_display_view_errors := 1;
177 ELSE
178 g_display_view_errors := 0;
179 END IF;
180 --
181 END IF;
182 END cache_batch_data;
183 --
184 PROCEDURE cache_api_data(p_batch_id IN NUMBER,p_api_id IN NUMBER) IS
185 --
186 l_last_batch_id NUMBER := g_last_batch_id;
187 --
188 CURSOR csr_count(cp_batch_id IN NUMBER,cp_api_id IN NUMBER) IS
189 SELECT am.module_package||'.'||am.module_name,
190 SUM(DECODE(bl.line_status,'U',1,0)),
191 SUM(DECODE(bl.line_status,'V',1,0)),
192 SUM(DECODE(bl.line_status,'E',1,0)),
193 SUM(DECODE(bl.line_status,'C',1,0))
194 FROM hr_pump_batch_lines bl,hr_api_modules am
195 WHERE bl.batch_id = cp_batch_id
196 AND bl.api_module_id = cp_api_id
197 AND am.api_module_id = bl.api_module_id
198 GROUP BY
199 am.module_package||'.'||am.module_name;
200 --
201 BEGIN
202 cache_batch_data(p_batch_id);
203 --
204 IF p_batch_id <> l_last_batch_id OR
205 p_api_id <> g_last_api_id OR
206 g_last_time + g_cache_age < dbms_utility.get_time
207 THEN
208 g_last_time := dbms_utility.get_time;
209 g_last_batch_id := p_batch_id;
210 g_last_api_id := p_api_id;
211 --
212 OPEN csr_count(p_batch_id,p_api_id);
213 FETCH csr_count
214 INTO g_module_name,
215 g_api_unprocessed,
216 g_api_validated,
217 g_api_error,
218 g_api_complete;
219 CLOSE csr_count;
220 --
221 IF g_api_error > 0 THEN
222 g_module_status := 'MODULE_STATUS_ERROR';
223 ELSIF g_api_unprocessed > 0 AND g_api_validated + g_api_error + g_api_complete = 0 THEN
224 g_module_status := 'MODULE_STATUS_NOTSTARTED';
225 ELSIF g_api_validated > 0 AND g_api_unprocessed + g_api_error + g_api_complete = 0 THEN
226 g_module_status := 'MODULE_STATUS_VALIDATED';
227 ELSIF g_api_complete > 0 AND g_api_unprocessed + g_api_error + g_api_validated = 0 THEN
228 g_module_status := 'MODULE_STATUS_COMPLETE';
229 ELSIF g_api_unprocessed + g_api_validated + g_api_error + g_api_complete = 0 THEN
230 g_module_status := 'MODULE_STATUS_EMPTY';
231 ELSE
232 g_module_status := 'MODULE_STATUS_PARTIAL';
233 END IF;
234 --
235 END IF;
236 END cache_api_data;
237 --
238 FUNCTION batch_status(p_batch_id IN NUMBER) RETURN VARCHAR2 IS
239 BEGIN
240 cache_batch_data(p_batch_id);
241 RETURN g_batch_status;
242 END batch_status;
243 --
244 FUNCTION process_status(p_batch_id IN NUMBER) RETURN VARCHAR2 IS
245 BEGIN
246 cache_batch_data(p_batch_id);
247 RETURN g_process_status;
248 END process_status;
249 --
250 FUNCTION percent_complete(p_batch_id IN NUMBER) RETURN NUMBER IS
251 BEGIN
252 cache_batch_data(p_batch_id);
253 RETURN g_percent_complete;
254 END percent_complete;
255 --
256 FUNCTION total_lines(p_batch_id IN NUMBER) RETURN NUMBER IS
257 BEGIN
258 cache_batch_data(p_batch_id);
259 RETURN g_total_lines;
260 END total_lines;
261 --
262 FUNCTION can_update(p_batch_id IN NUMBER) RETURN VARCHAR2 IS
263 BEGIN
264 cache_batch_data(p_batch_id);
265 RETURN g_can_update;
266 END can_update;
267 --
268 FUNCTION date_started(p_batch_id IN NUMBER) RETURN DATE IS
269 BEGIN
270 cache_batch_data(p_batch_id);
271 RETURN g_date_started;
272 END date_started;
273 --
274 FUNCTION parameter_group(p_batch_id IN NUMBER) RETURN VARCHAR2 IS
275 BEGIN
276 cache_batch_data(p_batch_id);
277 RETURN g_parameter_group;
278 END parameter_group;
279 --
280 FUNCTION validate_only(p_batch_id IN NUMBER) RETURN VARCHAR2 IS
281 BEGIN
282 cache_batch_data(p_batch_id);
283 RETURN g_validate_only;
284 END validate_only;
285 --
286 FUNCTION date_completed(p_batch_id IN NUMBER) RETURN DATE IS
287 BEGIN
288 cache_batch_data(p_batch_id);
289 RETURN g_date_completed;
290 END date_completed;
291 --
292 FUNCTION completion_text(p_batch_id IN NUMBER) RETURN VARCHAR2 IS
293 BEGIN
294 cache_batch_data(p_batch_id);
295 RETURN g_completion_text;
296 END completion_text;
297 --
298 FUNCTION process_phase(p_batch_id IN NUMBER) RETURN VARCHAR2 IS
299 BEGIN
300 cache_batch_data(p_batch_id);
301 RETURN g_process_phase;
302 END process_phase;
303 --
304 FUNCTION last_batch_exception(p_batch_id IN NUMBER) RETURN VARCHAR2 IS
305 BEGIN
306 cache_batch_data(p_batch_id);
307 RETURN g_last_batch_exception;
308 END last_batch_exception;
309 --
310 FUNCTION display_view_errors(p_batch_id IN NUMBER) RETURN NUMBER IS
311 BEGIN
312 cache_batch_data(p_batch_id);
313 RETURN g_display_view_errors;
314 END display_view_errors;
315 --
316 FUNCTION last_process_date(p_batch_id IN NUMBER) RETURN DATE IS
317 BEGIN
318 cache_batch_data(p_batch_id);
319 RETURN g_last_process_date;
320 END last_process_date;
321 --
322 FUNCTION module_name(p_batch_id IN NUMBER,p_api_id IN NUMBER) RETURN VARCHAR2 IS
323 BEGIN
324 cache_api_data(p_batch_id,p_api_id);
325 RETURN g_module_name;
326 END module_name;
327 --
328 FUNCTION module_status(p_batch_id IN NUMBER,p_api_id IN NUMBER) RETURN VARCHAR2 IS
329 BEGIN
330 cache_api_data(p_batch_id,p_api_id);
331 RETURN g_module_status;
332 END module_status;
333 --
334 FUNCTION api_unprocessed(p_batch_id IN NUMBER,p_api_id IN NUMBER) RETURN NUMBER IS
335 BEGIN
336 cache_api_data(p_batch_id,p_api_id);
337 RETURN g_api_unprocessed;
338 END api_unprocessed;
339 --
340 FUNCTION api_validated(p_batch_id IN NUMBER,p_api_id IN NUMBER) RETURN NUMBER IS
341 BEGIN
342 cache_api_data(p_batch_id,p_api_id);
343 RETURN g_api_validated;
344 END api_validated;
345 --
346 FUNCTION api_error(p_batch_id IN NUMBER,p_api_id IN NUMBER) RETURN NUMBER IS
347 BEGIN
348 cache_api_data(p_batch_id,p_api_id);
349 RETURN g_api_error;
350 END api_error;
351 --
352 FUNCTION api_complete(p_batch_id IN NUMBER,p_api_id IN NUMBER) RETURN NUMBER IS
353 BEGIN
354 cache_api_data(p_batch_id,p_api_id);
355 RETURN g_api_complete;
356 END api_complete;
357 --
358 END pay_pst_utils;