[Home] [Help]
PACKAGE BODY: APPS.BEN_PLAN_DESIGN_COPY_PROCESS
Source
1 PACKAGE BODY ben_plan_design_copy_process AS
2 /* $Header: bepdcprc.pkb 120.13 2008/05/15 06:24:16 pvelvano noship $ */
3 --
4 -- Global Variable Declaration
5 --
6 g_package VARCHAR2 (80) := 'ben_plan_design_copy_process';
7 g_debug boolean := hr_utility.debug_enabled;
8 --
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< create_process_log >-------------------------|
12 -- ----------------------------------------------------------------------------
13 PROCEDURE create_process_log (
14 p_copy_entity_txn_id IN NUMBER,
15 p_row_type_cd IN VARCHAR2
16 )
17 IS
18 --
19 -- Local variable declaration.
20 --
21 l_count NUMBER (15) := 0;
22
23 CURSOR c_proc_log
24 IS
25 SELECT RPAD (SUBSTR (MESSAGE_TEXT,
26 INSTR (MESSAGE_TEXT, '<SMALL>')
27 + 7,
28 INSTR (MESSAGE_TEXT, '</SMALL>')
29 - INSTR (MESSAGE_TEXT, '<SMALL>')
30 - 7
31 ),
32 30
33 )
34 || ' : '
35 || SUBSTR (MESSAGE_TEXT,
36 INSTR (MESSAGE_TEXT, '<SMALL>', 1, 2)
37 + 7,
38 INSTR (MESSAGE_TEXT, '</SMALL>', 1, 2)
39 - INSTR (MESSAGE_TEXT, '<SMALL>', 1, 2)
40 - 7
41 ) text
42 FROM pqh_process_log
43 WHERE txn_id = p_copy_entity_txn_id
44 ORDER BY process_log_id;
45 --
46 BEGIN
47 --
48 FOR l_proc_log IN c_proc_log
49 LOOP
50 --
51 IF ( p_row_type_cd = 'PDC' AND l_count = 15 ) OR
52 ( p_row_type_cd = 'PDW' AND l_count = 8 )
53 THEN
54 EXIT;
55 END IF;
56 --
57 IF l_proc_log.text <> ' : '
58 THEN
59 --
60 ben_batch_utils.WRITE (p_text => l_proc_log.text);
61 --
62 END IF;
63 --
64 l_count := l_count + 1;
65 --
66 END LOOP;
67 --
68 END create_process_log;
69 --
70 -- 5097567 Added the following procedure
71 -- ----------------------------------------------------------------------------
72 -- |--------------------< compile_modified_ff >-------------------------------|
73 -- ----------------------------------------------------------------------------
74 -- This procedure is for compilation of fast formulas created or updated
75 -- by the 'process' procedure.
76 --
77 --
78 PROCEDURE compile_modified_ff (
79 errbuf OUT NOCOPY VARCHAR2,
80 retcode OUT NOCOPY NUMBER,
81 p_copy_entity_txn_id IN NUMBER,
82 p_effective_date IN VARCHAR2
83 )
84 IS
85 --
86 cursor c_fff_rows is
87 select cpe.information1 formula_id,
88 rpad(substr(cpe.information112,1,30),30) formula_name,
89 cpe.information112 full_formula_name,
90 ff_typ.formula_type_name formula_type_name,
91 cpe.information2 effective_start_date,
92 cpe.information3 effective_end_date
93 from ben_copy_entity_results cpe,
94 pqh_copy_entity_txns cet,
95 ff_formula_types ff_typ
96 where cet.copy_entity_txn_id = p_copy_entity_txn_id
97 and cet.copy_entity_txn_id = cpe.copy_entity_txn_id
98 and ff_typ.formula_type_id = cpe.information160
99 and cet.status = 'COMPLETE'
100 and cpe.table_alias = 'FFF'
101 and cpe.number_of_copies = 1
102 and cpe.dml_operation in ('INSERT','UPDATE')
103 and (cpe.datetrack_mode IN ('INSERT','CORRECTION')
104 or cpe.datetrack_mode like 'UPDATE%')
105 order by 1,2;
106 --
107 l_return_status VARCHAR2(2000);
108 l_fff_rows c_fff_rows%rowtype;
109 l_count number := 0;
110 l_effective_date DATE;
111 l_request_id NUMBER;
112 --
113 l_proc varchar2(80) := g_package ||'compile_modified_ff';
114 --
115 BEGIN
116 --
117 l_effective_date := TO_DATE(p_effective_date,'DD-MM-YYYY');
118 --
119 open c_fff_rows;
120 fetch c_fff_rows into l_fff_rows;
121 if c_fff_rows%found then
122 --
123 ben_batch_utils.WRITE (p_text => ' # | Formula Name | Request ');
124 ben_batch_utils.WRITE (p_text => '-------------------------------------------------');
125 --
126 loop
127 begin
128 --
129 l_count := l_count + 1;
130 --l_return_status := ben_pd_formula_pkg.compile_formula(l_fff_rows.formula_id, l_effective_date);
131 --
132 -- 5199512 - Instead of online compilation, spawn a concurrent request
133
134 l_request_id := fnd_request.submit_request
135 (application => 'FF'
136 ,program => 'SINGLECOMPILE'
137 ,description => NULL
138 ,sub_request => FALSE
139 ,argument1 => l_fff_rows.formula_type_name
140 ,argument2 => l_fff_rows.full_formula_name
141 );
142
143 ben_batch_utils.WRITE (p_text => lpad(to_char(l_count),4)|| ' | '|| l_fff_rows.formula_name || ' | '|| l_request_id);
144 --
145 exception
146 when others then
147 --
148 ben_batch_utils.WRITE (p_text => lpad(to_char(l_count),4)|| ' | '|| l_fff_rows.formula_name || l_request_id);
149 ben_batch_utils.WRITE (p_text => SQLERRM );
150 errbuf := SQLERRM;
151 --
152 end;
153 --
154 fetch c_fff_rows into l_fff_rows;
155 exit when c_fff_rows%notfound;
156 --
157 end loop;
158 --
159 else
160 ben_batch_utils.WRITE (p_text => 'No Fast Formualas found for compilation.');
161 end if;
162 --
163 EXCEPTION
164 WHEN others THEN
165 ben_batch_utils.WRITE (p_text => SQLERRM );
166 errbuf := SQLERRM;
167 --
168 END compile_modified_ff;
169 --
170 -- ----------------------------------------------------------------------------
171 -- |--------------------------------< process >-------------------------------|
172 -- ----------------------------------------------------------------------------
173 --
174 -- This is the main batch procedure to be called from the concurrent manager.
175 --
176 PROCEDURE process (
177 errbuf OUT NOCOPY VARCHAR2,
178 retcode OUT NOCOPY NUMBER,
179 p_validate IN NUMBER DEFAULT 0,
180 p_copy_entity_txn_id IN NUMBER,
181 p_effective_date IN VARCHAR2,
182 p_prefix_suffix_text IN VARCHAR2 DEFAULT NULL,
183 p_reuse_object_flag IN VARCHAR2 DEFAULT NULL,
184 p_target_business_group_id IN VARCHAR2 DEFAULT NULL,
185 p_prefix_suffix_cd IN VARCHAR2 DEFAULT NULL,
186 p_effective_date_to_copy IN VARCHAR2 DEFAULT NULL
187 )
188 IS
189 --
190 -- Local variable declaration.
191 --
192 l_proc VARCHAR2 (80) := g_package || '.process';
193 l_cet_object_version_number NUMBER (15);
194 l_target_typ_cd VARCHAR2(30);
195 l_row_typ_cd PQH_COPY_ENTITY_ATTRIBS.ROW_TYPE_CD%TYPE;
196 l_start_with PQH_COPY_ENTITY_TXNS.START_WITH%TYPE;
197 l_effective_date DATE;
198 l_effective_date_to_copy DATE;
199 l_exception VARCHAR2(3000);
200 l_delete_failed VARCHAR2(30);
201 l_txn_effective_date DATE;
202 l_encoded_message varchar2(2000);
203 l_reuse_object_flag VARCHAR2(30);
204 l_prefix_suffix_text VARCHAR2(30);
205 --
206 -- Cursor Declaration.
207 --
208 CURSOR c_cet_ovn
209 IS
210 SELECT cet.object_version_number,
211 cet.src_effective_date,
212 cea.row_type_cd,
213 cea.information3 target_typ_cd,
214 cea.information4 reuse_object_flag,
215 cea.information1 prefix_suffix_text
216 FROM pqh_copy_entity_txns cet, pqh_copy_entity_attribs cea
217 WHERE cet.copy_entity_txn_id = cea.copy_entity_txn_id
218 AND cet.copy_entity_txn_id = p_copy_entity_txn_id;
219 --
220
221 --
222 BEGIN
223 --
224 hr_utility.set_location ('Entering ' || l_proc, 5);
225 hr_utility.set_location ('Entering process package', 5);
226 --
227 SAVEPOINT plan_design_copy_process;
228 --
229 --Added for Bug 6881417
230 ben_abr_bus.g_ssben_call:=true;
231 ben_abr_bus.g_ssben_var := '';
232 --Endof Code for Bug 6881417
233
234 l_effective_date := to_date(p_effective_date, 'DD-MM-YYYY');
235 l_effective_date_to_copy := to_date(p_effective_date_to_copy, 'DD-MM-YYYY');
236 --
237 OPEN c_cet_ovn;
238 --
239 FETCH c_cet_ovn INTO l_cet_object_version_number,
240 l_txn_effective_date,
241 l_row_typ_cd,
242 l_target_typ_cd,
243 l_reuse_object_flag,
244 l_prefix_suffix_text;
245 --
246 CLOSE c_cet_ovn;
247 --
248 --Bug 4365133 and 4368942. resetting the globals
249 --
250 if l_reuse_object_flag = 'YO' then
251 --
252 BEN_PLAN_DESIGN_TXNS_API.g_pgm_pl_prefix_suffix_text := l_prefix_suffix_text;
253 --
254 end if;
255 --
256 IF l_row_typ_cd = 'PDW' or l_row_typ_cd = 'ELP'
257 then
258 --
259 l_start_with := NULL;
260 --
261 ELSE
262 --
263 if l_target_typ_cd = 'BEN_PDIMPT'
264 then
265 --
266 l_start_with := 'BEN_PDC_TRGT_DTL_PAGE';
267 --
268 else
269 --
270 l_start_with := 'BEN_PDC_SLCT_TRGT_PAGE';
271 --
272 end if;
273 --
274 END IF;
275 --
276 --
277 IF l_row_typ_cd = 'PDW' or l_row_typ_cd = 'ELP' then
278 BEN_PDW_COPY_BEN_TO_STG.pre_Processor(p_validate =>p_validate,
279 p_copy_entity_txn_id => p_copy_entity_txn_id,
280 p_business_group_id => to_number(p_target_business_group_id),
281 p_effective_date => l_effective_date,
282 P_exception => l_exception
283 );
284
285 ---- Copied portion from pdw_submit_copy_request in bepdwapi.pkb ----
286 hr_utility.set_location('After preProcessor: '||l_proc,10);
287 -- write the table_route_id
288 ben_plan_design_wizard_api.write_route_and_hierarchy(p_copy_entity_txn_id);
289 -- this is for making the number of copies 0 for those rows falling outside of effective date
290 ben_plan_design_wizard_api.update_result_rows(p_copy_entity_txn_id);
291
292 BEGIN
293 savepoint SUBMIT_REQUEST;
294 -- first call delete so that if any row needs to be end dated before submit
295 -- this may fail because these rows which we are trying to delete may be
296 -- present as foriegn keys before the submit api updates them.
297 BEGIN
298 savepoint DELETE_REQUEST;
299 ben_plan_design_delete_api.call_delete_apis
300 ( p_process_validate => p_validate
301 ,p_copy_entity_txn_id => p_copy_entity_txn_id
302 ,p_delete_failed => l_delete_failed
303 );
304
305 -- submit api is failing if it picks up the end-dated ben entities.
306 -- added nvl for non-date tracked entities
307 UPDATE ben_copy_entity_results cer
308 set number_of_copies = 0
309 where cer.copy_entity_txn_id = p_copy_entity_txn_id
310 and l_txn_effective_date between nvl(information2,l_txn_effective_date) and nvl(information3,l_txn_effective_date)
311 and cer.dml_operation = 'DELETE';
312
313 EXCEPTION
314 when others then
315 -- we are not raising them at this time but remove it from stack
316 l_encoded_message:= fnd_message.get;
317 l_encoded_message:=null;
318 rollback to DELETE_REQUEST;
319 l_delete_failed :='Y';
320 END;
321
322 ---- Copied portion from pdw_submit_copy_request in bepdwapi.pkb ----
323
324 ben_pd_copy_to_ben_two.create_stg_to_ben_rows (p_validate =>p_validate,
325 p_copy_entity_txn_id => p_copy_entity_txn_id,
326 p_effective_date => l_effective_date,
327 p_prefix_suffix_text => p_prefix_suffix_text,
328 p_reuse_object_flag => p_reuse_object_flag,
329 p_target_business_group_id => p_target_business_group_id,
330 p_prefix_suffix_cd => p_prefix_suffix_cd,
331 p_effective_date_to_copy => l_effective_date_to_copy
332 );
333 --
334
335
336 -- call delete again if the delete failed previously
337 if(l_delete_failed ='Y') then
338 l_delete_failed:='N';
339 ben_plan_design_delete_api.call_delete_apis
340 ( p_process_validate => p_validate
341 ,p_copy_entity_txn_id => p_copy_entity_txn_id
342 ,p_delete_failed => l_delete_failed
343 );
344 end if;
345
346 -- p_validate is true
347 if p_validate = 1 then
348 raise hr_API.validate_enabled;
349 end if;
350 hr_utility.set_location('Leaving: '||l_proc,20);
351 EXCEPTION
352 when hr_API.validate_enabled then
353 ROLLBACK TO SUBMIT_REQUEST;
354 when app_exception.application_exception then
355 raise;
356 when others then
357 ROLLBACK TO SUBMIT_REQUEST;
358 raise;
359 END;
360
361 ELSE
362
363 ben_pd_copy_to_ben_two.create_stg_to_ben_rows (p_validate =>p_validate,
364 p_copy_entity_txn_id => p_copy_entity_txn_id,
365 p_effective_date => l_effective_date,
366 p_prefix_suffix_text => p_prefix_suffix_text,
367 p_reuse_object_flag => p_reuse_object_flag,
368 p_target_business_group_id => p_target_business_group_id,
369 p_prefix_suffix_cd => p_prefix_suffix_cd,
370 p_effective_date_to_copy => l_effective_date_to_copy
371 );
372 END IF;
373 -- Write Log Data to PQH_PROCESS_LOG
374 --
375 ben_plan_design_txns_api.create_log (p_copy_entity_txn_id);
376 --
377 -- Write Process Information to Concurrent Program Log
378 --
379 create_process_log (p_copy_entity_txn_id => p_copy_entity_txn_id,
380 p_row_type_cd => l_row_typ_cd );
381 --
382 pqh_copy_entity_txns_api.update_copy_entity_txn (p_copy_entity_txn_id => p_copy_entity_txn_id,
383 p_datetrack_mode => hr_api.g_correction,
384 p_status => 'COMPLETE', /* To enable View Log Icon */
385 p_start_with => l_start_with,
386 p_object_version_number => l_cet_object_version_number,
387 p_effective_date => TRUNC (l_effective_date)
388 );
389 --
390 COMMIT;
391
392 --Added for Bug 6881417
393 ben_abr_bus.g_ssben_call:=false;
394 ben_abr_bus.g_ssben_var := '';
395 --
396 hr_utility.set_location ('Leaving ' || l_proc, 10);
397 --
398 EXCEPTION
399 --
400 WHEN OTHERS
401 THEN
402 --
403 ROLLBACK TO plan_design_copy_process;
404
405 --Added for Bug 6881417
406 ben_abr_bus.g_ssben_call:=false;
407 ben_abr_bus.g_ssben_var := '';
408
409 -- if this is ELP then raise the error this is online submission
410 if l_row_typ_cd = 'ELP' then
411 raise;
412 end if;
413 --
414 ben_batch_utils.WRITE (p_text => SQLERRM);
415 --
416 BEGIN
417 --
418 SAVEPOINT update_cet;
419 --
420 -- Bug 4415568
421 IF l_row_typ_cd = 'PDW'
422 THEN
423 --
424 l_start_with := 'BEN_PDW_PLN_OVVW_FUNC';
425 --
426 END IF;
427 --
428 pqh_copy_entity_txns_api.update_copy_entity_txn (p_copy_entity_txn_id => p_copy_entity_txn_id,
429 p_datetrack_mode => hr_api.g_correction,
430 p_status => 'ERROR', /* To disable View Log Icon */
431 p_start_with => l_start_with, /* Bug 4415568 Enable Continue*/
432 p_object_version_number => l_cet_object_version_number,
433 p_effective_date => TRUNC (l_effective_date)
434 );
435 --
436 COMMIT;
437 --
438 EXCEPTION
439 --
440 WHEN OTHERS
441 THEN
442 --
443 ROLLBACK TO update_cet;
444 --
445 ben_batch_utils.WRITE (p_text => SQLERRM);
446 --
447 COMMIT;
448 --
449 END;
450 --
451 COMMIT;
452 --
453 --
454 END process;
455 --
456 END ben_plan_design_copy_process;