[Home] [Help]
PACKAGE BODY: APPS.PQH_GENERIC
Source
4 -- Created by : Sanej Nair (SCNair)
1 package body PQH_GENERIC as
2 /* $Header: pqgnfnb.pkb 120.3 2006/05/02 03:04:19 ghshanka noship $ */
3 --
5 -- Version Date Author Comment
6 -- -------+-----------+--------------+---------------------------------------+
7 -- 115.1 27-Feb-2000 Sanej Nair Initial Version
8 -- ==========================================================================
9 -- ---------------------------------------------------------------------------+
10 -- | Private Global Definitions |
11 -- ---------------------------------------------------------------------------+
12 --
13 g_package varchar2(33) := ' PQH_GENERIC.'; -- Global package name
14 -- added for the bug 5052820
15 --
16 l_eff_date date ;
17 upd_where varchar2(1) :='N';
18 --
19 -- end of bug 5052820
20 --
21 -- ---------------------------------------------------------------------------+
22 -- |---------------------------< generic_fn >-------------------------------|
23 -- ---------------------------------------------------------------------------+
24 --
25 -- Description:
26 -- This handles transactions like Positions copy, Jobs updates etc.
27 --
28 -- Access Status:
29 -- Internal Use Only.
30 --
31 Procedure generic_fn( p_copy_entity_txn_id in number ,
32 p_master_required_flag in varchar2 default 'Y' ) is
33 --
34 -- local variables
35 --
36 l_proc varchar2(72) := g_package||'generic_fn';
37 l_reset_flag varchar2(10) ;
38 l_cet number ;
39 l_cer1 number ;
40 l_cer2 number ;
41 l_status pqh_copy_entity_txns.status%TYPE;
42 l_transaction_category_id pqh_transaction_categories.transaction_category_id%TYPE;
43 l_transaction_short_name pqh_transaction_categories.short_name%TYPE;
44 l_name pqh_copy_entity_txns.display_name%TYPE;
45 l_master_table_route_id pqh_table_route.table_route_id%TYPE;
46 l_effective_date varchar2(30); -- would hold date in char format
50 l_pre_copy_function_name pqh_copy_entity_functions.pre_copy_function_name%TYPE;
47 l_function_context pqh_copy_entity_contexts.context%TYPE;
48 l_context pqh_copy_entity_txns.context%TYPE;
49 l_function_type_cd pqh_copy_entity_functions.function_type_cd%TYPE;
51 l_copy_function_name pqh_copy_entity_functions.copy_function_name%TYPE;
52 l_post_copy_function_name pqh_copy_entity_functions.post_copy_function_name%TYPE;
53 l_dt_mode varchar2(50):=null;
54 --
55 -- cursor for master table id
56 --
57 cursor c_info (v_copy_entity_txn_id number) is
58 select tct.transaction_category_id
59 , cet.copy_entity_txn_id
60 , tct.short_name
61 , cet.display_name name
62 , cet.datetrack_mode dt_mode
63 , tct.master_table_route_id
64 , to_char(nvl(cet.src_effective_date,sysdate),'RRRR/MM/DD HH24:MI:SS') effective_date
65 , cet.context -- application_id
66 , cec.context gbl_context
67 from pqh_copy_entity_txns cet
68 , pqh_transaction_categories_vl tct
69 , pqh_copy_entity_contexts cec
70 where tct.transaction_category_id = cet.transaction_category_id
71 and cec.transaction_short_name = tct.short_name
72 and cec.application_short_name is null
73 and cec.legislation_code is null
74 and cec.responsibility_key is null
75 and cet.copy_entity_txn_id = v_copy_entity_txn_id
76 for update of copy_entity_txn_id;
77 --
78 -- Cursor for table details
79 --
80 cursor c_tab_details(v_table_route_id number ) is
81 select trt.from_clause
82 , trt.where_clause
83 , trt.table_alias
84 from pqh_table_route trt
85 where trt.table_route_id = v_table_route_id;
86 --
87 -- Cursor for dependent table details
88 --
89 cursor c_dep_details(v_copy_entity_txn_id number) is
90 select trt.from_clause
91 , trt.table_route_id
92 , trt.where_clause
93 , trt.table_alias
94 from pqh_table_route trt
95 , pqh_copy_entity_prefs cep
96 where cep.copy_entity_txn_id = v_copy_entity_txn_id
97 and cep.table_route_id = trt.table_route_id
98 and cep.select_flag = 'Y' ;
99 --
100 -- Cursor for all selected target records
101 --
102 cursor c_target is
103 select cer.* , src.information1 src_information1
104 from pqh_copy_entity_results cer ,
105 pqh_copy_entity_results src
106 where cer.copy_entity_txn_id = p_copy_entity_txn_id
107 and cer.src_copy_entity_result_id = src.copy_entity_result_id
108 and src.number_of_copies <> 0
109 and cer.number_of_copies = 1
110 and cer.result_type_cd = 'TARGET'
111 order by cer.src_copy_entity_result_id; -- to group targets by source.
112 -- for update of cer.status ; ora 1002 out of sequence error.
113
114 --
115 -- Cursor for transaction attributes
116 --
117 cursor c_attribs(v_copy_entity_txn_id number,v_table_route_id number,v_attribute_type varchar2) is
118 select upper(att.column_name) column_name
119 , upper(att.column_type) column_type
120 , upper(sat.ddf_column_name) ddf_column_name
121 from pqh_attributes att
122 , pqh_special_attributes sat
123 , pqh_txn_category_attributes tca
124 , pqh_copy_entity_txns cet
125 where att.attribute_id = tca.attribute_id
126 and att.master_table_route_id = v_table_route_id
127 and tca.txn_category_attribute_id = sat.txn_category_attribute_id
128 and cet.transaction_category_id = tca.transaction_category_id
129 and sat.attribute_type_cd = v_attribute_type
130 and cet.copy_entity_txn_id = v_copy_entity_txn_id
131 and sat.ddf_column_name is not null
132 and att.enable_flag = 'Y'
133 and tca.select_flag = 'Y'
134 and sat.enable_flag = 'Y'
135 and sat.context = pqh_generic.g_gbl_context; --application_id
136 --and cet.context = sat.context; --application_id
137 --
138 -- Cursor for changeable attributes
139 --
140 cursor c_change(v_copy_entity_txn_id number, v_table_route_id number) is
141 select pqh_generic.get_alias(upper(att.column_name)) column_name
142 , upper(att.column_type) column_type
143 , upper(sat1.ddf_column_name) ddf_column_name
144 , sat1.context context
145 , sat.context context_s
146 from pqh_attributes att
147 , pqh_special_attributes sat
148 , pqh_special_attributes sat1
149 , pqh_txn_category_attributes tca
150 , pqh_copy_entity_txns cet
151 where att.attribute_id = tca.attribute_id
152 and att.master_table_route_id = v_table_route_id
153 and tca.txn_category_attribute_id = sat.txn_category_attribute_id
154 and cet.transaction_category_id = tca.transaction_category_id
155 and sat.attribute_type_cd = 'CHANGEABLE'
156 and sat1.attribute_type_cd in ('DISPLAY','SEGMENT')
157 and sat.txn_category_attribute_id = sat1.txn_category_attribute_id
158 and att.enable_flag = 'Y'
159 and tca.select_flag = 'Y'
160 and sat.enable_flag = 'Y'
164 and sat.context = pqh_generic.g_gbl_context
161 and sat1.enable_flag = 'Y'
162 and cet.copy_entity_txn_id = v_copy_entity_txn_id
163 and sat.context = sat1.context -- application_id
165 and sat1.ddf_column_name is not null ;
166 --
167 cursor c_status(v_status in varchar2) is
168 select status
169 from pqh_copy_entity_results
170 where result_type_cd = 'TARGET'
171 and copy_entity_txn_id = p_copy_entity_txn_id
172 and status = v_status
173 and number_of_copies <> 0
174 and rownum < 2 ;
175 --
176 cursor c_table_route (v_alias in varchar2) is
177 select table_route_id
178 from pqh_table_route
179 where table_alias like v_alias ;
180 --
181 cursor c_dt is
182 select ddf_column_name
183 from pqh_special_attributes s
184 ,pqh_txn_category_attributes c
185 ,pqh_attributes a
186 where a.attribute_id = c.attribute_id
187 and c.txn_category_attribute_id = s.txn_category_attribute_id
188 and a.enable_flag = 'Y'
189 and c.select_flag = 'Y'
190 and s.enable_flag = 'Y'
191 and s.context = pqh_generic.g_gbl_context
192 and s.attribute_type_cd in ('SELECT', 'PARAMETER','DISPLAY')
193 and a.column_name like 'DATETRACK%MODE%'
194 and s.ddf_column_name is not null
195 and rownum < 2;
196 --
197 function get_function_details( p_table_route_id in number
198 , p_transaction_short_name in varchar2
199 , p_context in varchar2
200 , p_function_type_cd out nocopy varchar2
201 , p_pre_copy_function_name out nocopy varchar2
202 , p_copy_function_name out nocopy varchar2
203 , p_post_copy_function_name out nocopy varchar2 )
204 return varchar2 is
205 --
206 cursor c_con is
207 select cec.application_short_name, cec.legislation_code, cec.responsibility_key
208 from pqh_copy_entity_contexts cec
209 where cec.context = p_context
210 and cec.transaction_short_name = p_transaction_short_name;
211 --
212 cursor c_all is
213 select cec.context, cec.application_short_name, cec.legislation_code, cec.responsibility_key,
214 cef.function_type_cd, cef.pre_copy_function_name, cef.copy_function_name, cef.post_copy_function_name
215 from pqh_copy_entity_contexts cec, pqh_copy_entity_functions cef
216 where cec.context = cef.context
217 and cef.table_route_id = p_table_route_id
218 and cec.transaction_short_name = p_transaction_short_name;
219 begin
220 --
221 -- Context Assumption : context on the copy entity txns is the reference.
222 --
223 hr_utility.set_location('Get Fnc txn sht nam:'||p_transaction_short_name, 51);
224 hr_utility.set_location(' context :'||p_context, 52);
225 hr_utility.set_location(' table route:'||p_table_route_id, 53);
226 p_function_type_cd := '' ;
227 p_pre_copy_function_name := '' ;
228 p_copy_function_name := '' ;
229 p_post_copy_function_name := '' ;
230 --
231 for rec in c_con loop
232 --
233 for erec in c_all loop
234 hr_utility.set_location(' appl shrt nam :'||rec.application_short_name||'-'||erec.application_short_name, 53);
235 hr_utility.set_location(' Leg code :'||rec.legislation_code||'-'||erec.legislation_code, 54);
236 hr_utility.set_location(' Resp Key :'||rec.responsibility_key||'-'||erec.responsibility_key, 55);
237 if nvl(rec.application_short_name,hr_api.g_varchar2) = nvl(erec.application_short_name,hr_api.g_varchar2) and
238 nvl(rec.legislation_code,hr_api.g_varchar2) = nvl(erec.legislation_code,hr_api.g_varchar2) and
239 nvl(rec.responsibility_key,hr_api.g_varchar2) = nvl(erec.responsibility_key,hr_api.g_varchar2) then
240 --
241 p_function_type_cd := erec.function_type_cd ;
242 p_pre_copy_function_name := erec.pre_copy_function_name ;
243 p_copy_function_name := erec.copy_function_name ;
244 p_post_copy_function_name := erec.post_copy_function_name ;
245 return (erec.context) ;
246 end if;
247 end loop;
248 --
249 for erec in c_all loop
250 if nvl(rec.application_short_name,hr_api.g_varchar2) = nvl(erec.application_short_name,hr_api.g_varchar2) and
251 nvl(rec.legislation_code,hr_api.g_varchar2) = nvl(erec.legislation_code,hr_api.g_varchar2) and
252 erec.responsibility_key is null then
253 --
254 p_function_type_cd := erec.function_type_cd ;
255 p_pre_copy_function_name := erec.pre_copy_function_name ;
256 p_copy_function_name := erec.copy_function_name ;
257 p_post_copy_function_name := erec.post_copy_function_name ;
258 return (erec.context) ;
259 end if;
260 end loop;
261 --
262 for erec in c_all loop
263 if nvl(rec.application_short_name,hr_api.g_varchar2) = nvl(erec.application_short_name,hr_api.g_varchar2) and
264 erec.legislation_code is null and
265 nvl(rec.responsibility_key,hr_api.g_varchar2) = nvl(erec.responsibility_key,hr_api.g_varchar2) then
266 --
267 p_function_type_cd := erec.function_type_cd ;
268 p_pre_copy_function_name := erec.pre_copy_function_name ;
272 end if;
269 p_copy_function_name := erec.copy_function_name ;
270 p_post_copy_function_name := erec.post_copy_function_name ;
271 return (erec.context) ;
273 end loop;
274 --
275 for erec in c_all loop
276 if nvl(rec.application_short_name,hr_api.g_varchar2) = nvl(erec.application_short_name,hr_api.g_varchar2) and
277 erec.legislation_code is null and
278 erec.responsibility_key is null then
279 --
280 p_function_type_cd := erec.function_type_cd ;
281 p_pre_copy_function_name := erec.pre_copy_function_name ;
282 p_copy_function_name := erec.copy_function_name ;
283 p_post_copy_function_name := erec.post_copy_function_name ;
284 return (erec.context) ;
285 end if;
286 end loop;
287 --
288 for erec in c_all loop
289 if erec.application_short_name is null and
290 erec.legislation_code is null and
291 erec.responsibility_key is null then
292 --
293 p_function_type_cd := erec.function_type_cd ;
294 p_pre_copy_function_name := erec.pre_copy_function_name ;
295 p_copy_function_name := erec.copy_function_name ;
296 p_post_copy_function_name := erec.post_copy_function_name ;
297 return (erec.context) ;
298 end if;
299 end loop;
300 --
301 end loop; --c_con
302 --
303 exception when others then
304 p_function_type_cd := null;
305 p_pre_copy_function_name := null;
306 p_copy_function_name := null;
307 p_post_copy_function_name := null;
308 raise;
309 end get_function_details;
310 --
311 Begin
312 --
313 hr_utility.set_location('Entering:'||l_proc, 5);
314 --
315 -- populate local variables with
316 -- transaction_category_id => which identifies the transaction
317 -- master_table_route_id => the master table associated with the transaction
318 -- context => context associated with the transaction
319 --
320 for rec1 in c_info(p_copy_entity_txn_id) loop
321 --
322 l_transaction_category_id := rec1.transaction_category_id ;
323 --
324 l_transaction_short_name := rec1.short_name ;
325 --
326 l_name := rec1.name ;
327 --
328 l_master_table_route_id := rec1.master_table_route_id ;
329 --
330 l_effective_date := rec1.effective_date ;
331 --
332 l_context := rec1.context ;
333 --
334 pqh_generic.g_context := l_context ;
335 --
336 pqh_generic.g_txn_id := p_copy_entity_txn_id ;
337 --
338 pqh_generic.g_gbl_context := rec1.gbl_context ;
339 l_dt_mode := rec1.dt_mode;
340 --
341 for i in c_dt loop
342 execute immediate 'update pqh_copy_entity_results set '||i.ddf_column_name||' = '''||rec1.dt_mode
343 ||''' where copy_entity_txn_id = '||to_char(p_copy_entity_txn_id)
344 ||' and result_type_cd = ''TARGET'''
345 ||' and number_of_copies = 1 and status in (''TGT_P'',''TGT_ERR'')' ;
346 end loop; --c_dt
347 --
348 update pqh_copy_entity_results
349 set status = 'COMPLETED'
350 where copy_entity_txn_id = rec1.copy_entity_txn_id
351 and result_type_cd = 'SOURCE'
352 and number_of_copies <> 0
353 and copy_entity_result_id not in (select src_copy_entity_result_id from pqh_copy_entity_results
354 where copy_entity_txn_id = rec1.copy_entity_txn_id
355 and result_type_cd = 'TARGET'
356 and number_of_copies <> 1 ) ;
357 --
358 update pqh_copy_entity_results
359 set status = 'TGT_P'
360 where copy_entity_txn_id = rec1.copy_entity_txn_id
361 and result_type_cd = 'TARGET'
362 and number_of_copies = 0
363 and status = 'TGT_ERR' ;
364 --
365 update pqh_copy_entity_txns
366 set status = nvl(l_status, 'COMPLETED')
367 where copy_entity_txn_id = rec1.copy_entity_txn_id;
368 --
369 exit;
370 end loop; -- c_info
371 --
372 -- get cer /cet information for error log
373 for i in c_table_route ('CET') loop
374 l_cet := i.table_route_id ;
375 end loop;
376 --
377 for i in c_table_route ('CER1') loop
378 l_cer1 := i.table_route_id ;
379 end loop;
380 --
381 for i in c_table_route ('CER2') loop
382 l_cer2 := i.table_route_id ;
383 end loop;
384 --
385 -- start error log
386 --
387 pqh_process_batch_log.start_log (p_batch_id => p_copy_entity_txn_id,
388 p_module_cd => nvl(l_transaction_short_name, 'ERROR- GEN CPY') ,
389 p_log_context => l_name ) ;
390 --
391 hr_utility.set_location(' '||l_proc ||' rec2', 5);
392 --
393 for rec2 in c_target loop
394 --
395 update pqh_copy_entity_results
396 set status = 'COMPLETED'
397 where copy_entity_result_id = rec2.copy_entity_result_id ;
398 --
399 pqh_generic.g_result_id := rec2.copy_entity_result_id ;
400 l_reset_flag := 'Y' ;
401 --
405 p_log_context => rec2.src_information1 );
402 pqh_process_batch_log.set_context_level (p_txn_id => rec2.src_copy_entity_result_id,
403 p_txn_table_route_id => l_cer1,-- trt for source
404 p_level => 1,
406 --
407 assign_value(p_column_name => 'EFFECTIVE_DATE'
408 , p_column_type => 'D'
409 , p_value => l_effective_date
410 , p_reset_flag => l_reset_flag
411 , p_source_flag => 'Y' );
412 --
413 l_reset_flag := 'N' ;
414 --
415 -- for every target record repoplated PLtable with new PK details
416 --
417 hr_utility.set_location(' '||l_proc ||' rec3', 5);
418 --
419 for rec3 in c_attribs(rec2.copy_entity_txn_id, l_master_table_route_id, 'PRIMARY_KEY') loop
420 --
421 dynamic_pltab_populate (p_ddf_column_name => rec3.ddf_column_name
422 , p_copy_entity_result_id => rec2.src_copy_entity_result_id
423 , p_copy_entity_txn_id => rec2.copy_entity_txn_id
424 , p_column_name => get_alias(rec3.column_name)
425 , p_column_type => rec3.column_type
426 , p_reset_flag => l_reset_flag -- delete before populate flag
427 , p_source_flag => 'Y' ) ; -- specify source/target PLtable
428 --
429 -- Change reset flag to N
430 end loop; -- c_attribs
431 --
432 -- for every target record repoplated PLtable with column details of new rec
433 --
434 l_reset_flag := 'Y' ;
435 --
436 hr_utility.set_location(' '||l_proc ||' rec4', 5);
437 --
438 for rec4 in c_change(rec2.copy_entity_txn_id, l_master_table_route_id) loop
439 --
440 -- poplated PLtable with new changeable details which the user postentially could have changed
441 --
442 dynamic_pltab_populate (p_ddf_column_name => rec4.ddf_column_name
443 , p_copy_entity_result_id => rec2.copy_entity_result_id
444 , p_copy_entity_txn_id => rec2.copy_entity_txn_id
445 , p_column_name => get_alias(rec4.column_name)
446 , p_column_type => rec4.column_type
447 , p_reset_flag => l_reset_flag -- delete before populate flag
448 , p_source_flag => 'N' ) ; -- specify source/target PLtable
449 --
450 -- for every target record repoplated PLtable with column details of new rec
451 --
452 l_reset_flag := 'N' ;
453 --
454 end loop; -- c_change
455 --
456 hr_utility.set_location(l_proc||' : after change', 111);
457 --
458 for rec5 in c_attribs(rec2.copy_entity_txn_id, l_master_table_route_id, 'PARAMETER') loop
459 --
460 -- poplated PLtable with parameter details which the transaction expects on the global table
461 --
462 dynamic_pltab_populate (p_ddf_column_name => rec5.ddf_column_name
463 , p_copy_entity_result_id => rec2.copy_entity_result_id
464 , p_copy_entity_txn_id => rec2.copy_entity_txn_id
465 , p_column_name => get_alias(rec5.column_name)
466 , p_column_type => rec5.column_type
467 , p_reset_flag => 'N' -- delete before populate flag
468 , p_source_flag => 'N' ) ; -- specify source/target PLtable
469 end loop; -- c_attribs
470 --
471 hr_utility.set_location(l_proc||' : after attribs', 111);
472 --
473 -- the following loop is only for debugging purposes
474 --
475 for i in nvl(PQH_GENERIC.g_source_pk_table.first,0)..nvl(PQH_GENERIC.g_source_pk_table.last,-1) loop
476 begin
477 hr_utility.set_location(i||'S- '||PQH_GENERIC.g_source_pk_table(i).column_name||'- '
478 || PQH_GENERIC.g_source_pk_table(i).column_type||'- '
479 || PQH_GENERIC.g_source_pk_table(i).value,11);
480 hr_utility.set_location(i||'T- '||PQH_GENERIC.g_target_pk_table(i).column_name||'- '
481 || PQH_GENERIC.g_target_pk_table(i).column_type||'- '
482 || PQH_GENERIC.g_target_pk_table(i).value,11);
483 exception when others then null;
484 end;
485 end loop;
486 --
487 populate_table;
488 --
489 -- check if master copy is required (in case of duplicate rec on form, master would not be required)
490 --
491 if p_master_required_flag = 'Y' then
492 hr_utility.set_location('on master '||p_master_required_flag, 5);
493 -- start of bug 5052820
494 --
495 if (pqh_generic.g_gbl_context ='Global Position Update'
496 and l_dt_mode ='UPDATE_CHANGE_INSERT') then
497 upd_where :='Y';
498 end if;
499 --
500 -- end of bug 5052820
501 --
502 g_level := 1 ; -- 1 implying the master
503 g_success := true ;
504 l_function_context := get_function_details
505 ( p_table_route_id => l_master_table_route_id
506 , p_transaction_short_name => l_transaction_short_name
507 , p_context => l_context
511 , p_post_copy_function_name => l_post_copy_function_name );
508 , p_function_type_cd => l_function_type_cd
509 , p_pre_copy_function_name => l_pre_copy_function_name
510 , p_copy_function_name => l_copy_function_name
512 --
513 for rec6 in c_tab_details(l_master_table_route_id) loop
514 --
515 pqh_process_batch_log.set_context_level (p_txn_id => rec2.copy_entity_result_id,
516 p_txn_table_route_id => l_cer2 , -- trt for target
517 p_level => 2,
518 p_log_context => rec2.information1 );
519 begin
520 savepoint start_process;
521 --
522 process_copy( p_copy_entity_txn_id => rec2.copy_entity_txn_id
523 , p_table_route_id => l_master_table_route_id
524 , p_from_clause => rec6.from_clause
525 , p_table_alias => rec6.table_alias
526 , p_where_clause => rec6.where_clause
527 , p_pre_copy_proc => l_pre_copy_function_name
528 , p_copy_proc => l_copy_function_name
529 , p_post_copy_proc => l_post_copy_function_name);
530 --
531 hr_utility.set_location('on master heading for upd', 499);
532 update pqh_copy_entity_results
533 set number_of_copies = '0'
534 where copy_entity_result_id = rec2.copy_entity_result_id
535 and status in ('COMPLETED','DPT_ERR') ;
536 exception
537 when others then
538 hr_utility.set_location('on master exception', 500);
539 pqh_generic.v_err := sqlerrm;
540 Raise_Error(p_copy_entity_result_id => rec2.copy_entity_result_id,
541 p_msg_code => 'ERR:');
542 end ;
543 exit; -- master record is always just one
544 end loop; -- c_tab_details
545 --
546 end if; -- p_master_required_flag
547 -- Dependent starts
548 g_level := 2 ; -- 2 implying the first dependents
549 --
550 if ( p_master_required_flag = 'Y' and g_success) or p_master_required_flag <> 'Y' then
551 hr_utility.set_location('On Details ', 51);
552 --
553 for rec7 in c_dep_details(rec2.copy_entity_txn_id) loop
554 hr_utility.set_location('on dependent '||rec7.table_route_id, 5);
555 --
556 l_function_context := get_function_details
557 ( p_table_route_id => rec7.table_route_id
558 , p_transaction_short_name => l_transaction_short_name
559 , p_context => l_context
560 , p_function_type_cd => l_function_type_cd
561 , p_pre_copy_function_name => l_pre_copy_function_name
562 , p_copy_function_name => l_copy_function_name
563 , p_post_copy_function_name => l_post_copy_function_name );
564 --
565 -- Call copy process to copy dependents.
566 --
567 begin
568 savepoint start_process;
569 process_copy( p_copy_entity_txn_id => rec2.copy_entity_txn_id
570 , p_table_route_id => rec7.table_route_id
571 , p_from_clause => rec7.from_clause
572 , p_table_alias => rec7.table_alias
573 , p_where_clause => rec7.where_clause
574 , p_pre_copy_proc => l_pre_copy_function_name
575 , p_copy_proc => l_copy_function_name
576 , p_post_copy_proc => l_post_copy_function_name);
577 exception
578 when others then
579 Raise_Error(p_copy_entity_result_id => rec2.copy_entity_result_id,
580 p_msg_code => 'WRN:');
581 end ;
582 --
583 end loop; --c_dep_details
584 --
585 end if; -- dependent check for master success / no master
586 -- End Dependent
587 --
588 end loop; --c_target
589 --
590 hr_utility.set_location('Leaving:'||l_proc, 10);
591 --
592 pqh_process_batch_log.end_log ;
593 --
594 End generic_fn;
595 --
596 -- Generic Function to be called from concurrent program
597 --
598 Procedure generic_fn( errbuf out nocopy varchar2,
599 retcode out nocopy varchar2 ,
600 argument1 varchar2 ,
601 argument2 varchar2 default null ,
602 argument3 varchar2 default null ,
603 argument4 varchar2 default null ,
604 argument5 varchar2 default null ,
605 argument6 varchar2 default null ,
606 argument7 varchar2 default null ,
607 argument8 varchar2 default null ,
608 argument9 varchar2 default null ,
609 argument10 varchar2 default null ,
610 argument11 varchar2 default null ,
611 argument12 varchar2 default null ,
612 argument13 varchar2 default null ,
616 argument17 varchar2 default null ,
613 argument14 varchar2 default null ,
614 argument15 varchar2 default null ,
615 argument16 varchar2 default null ,
617 argument18 varchar2 default null ,
618 argument19 varchar2 default null ,
619 argument20 varchar2 default null ,
620 argument21 varchar2 default null ,
621 argument22 varchar2 default null ,
622 argument23 varchar2 default null ,
623 argument24 varchar2 default null ,
624 argument25 varchar2 default null ,
625 argument26 varchar2 default null ,
626 argument27 varchar2 default null ,
627 argument28 varchar2 default null ,
628 argument29 varchar2 default null ,
629 argument30 varchar2 default null ,
630 argument31 varchar2 default null ,
631 argument32 varchar2 default null ,
632 argument33 varchar2 default null ,
633 argument34 varchar2 default null ,
634 argument35 varchar2 default null ,
635 argument36 varchar2 default null ,
636 argument37 varchar2 default null ,
637 argument38 varchar2 default null ,
638 argument39 varchar2 default null ,
639 argument40 varchar2 default null ,
640 argument41 varchar2 default null ,
641 argument42 varchar2 default null ,
642 argument43 varchar2 default null ,
643 argument44 varchar2 default null ,
644 argument45 varchar2 default null ,
645 argument46 varchar2 default null ,
646 argument47 varchar2 default null ,
647 argument48 varchar2 default null ,
648 argument49 varchar2 default null ,
649 argument50 varchar2 default null ,
650 argument51 varchar2 default null ,
651 argument52 varchar2 default null ,
652 argument53 varchar2 default null ,
653 argument54 varchar2 default null ,
654 argument55 varchar2 default null ,
655 argument56 varchar2 default null ,
656 argument57 varchar2 default null ,
657 argument58 varchar2 default null ,
658 argument59 varchar2 default null ,
659 argument60 varchar2 default null ,
660 argument61 varchar2 default null ,
661 argument62 varchar2 default null ,
662 argument63 varchar2 default null ,
663 argument64 varchar2 default null ,
664 argument65 varchar2 default null ,
665 argument66 varchar2 default null ,
666 argument67 varchar2 default null ,
667 argument68 varchar2 default null ,
668 argument69 varchar2 default null ,
669 argument70 varchar2 default null ,
670 argument71 varchar2 default null ,
671 argument72 varchar2 default null ,
672 argument73 varchar2 default null ,
673 argument74 varchar2 default null ,
674 argument75 varchar2 default null ,
675 argument76 varchar2 default null ,
676 argument77 varchar2 default null ,
677 argument78 varchar2 default null ,
678 argument79 varchar2 default null ,
679 argument80 varchar2 default null ,
680 argument81 varchar2 default null ,
681 argument82 varchar2 default null ,
682 argument83 varchar2 default null ,
683 argument84 varchar2 default null ,
684 argument85 varchar2 default null ,
685 argument86 varchar2 default null ,
686 argument87 varchar2 default null ,
687 argument88 varchar2 default null ,
688 argument89 varchar2 default null ,
689 argument90 varchar2 default null ,
690 argument91 varchar2 default null ,
691 argument92 varchar2 default null ,
692 argument93 varchar2 default null ,
693 argument94 varchar2 default null ,
694 argument95 varchar2 default null ,
695 argument96 varchar2 default null ,
696 argument97 varchar2 default null ,
697 argument98 varchar2 default null ,
698 argument99 varchar2 default null ,
699 argument100 varchar2 default null
700 ) is
701 begin
702 hr_utility.trace('Starting.. ');
703 hr_utility.trace(argument1);
704 --
705 pqh_generic.g_calling_mode := 'CONCURRENT' ;
706 pqh_generic.v_err := '';
707 g_conc_warn_flag := false;
708 --
709 generic_fn ( p_copy_entity_txn_id => argument1 );
710 --
711 if g_conc_warn_flag then
712 retcode := 1 ;
713 else
717 exception
714 retcode := 0;
715 end if; --g_conc_warn_flag
716 --
718 when others then
719 errbuf := sqlerrm ;
720 retcode := 2 ;
721 end generic_fn;
722 --
723 -- Generic Function to be called from Forms etc. specifying the calling mode
724 --
725 function generic_fn( p_copy_entity_txn_id in number ,
726 p_txn_short_name in varchar2 ,
727 p_calling_mode in varchar2 ) return number is
728 l_req number := -1 ;
729 begin
730 --
731 if p_calling_mode = 'FORM' then
732 l_req := fnd_request.submit_request( application => 'PQH'
733 , program => 'PQHGNCPG'
734 , argument1 => p_copy_entity_txn_id
735 , argument2 => p_txn_short_name );
736 return (l_req);
737 end if;
738 --
739 pqh_generic.g_calling_mode := p_calling_mode ;
740 --
741 generic_fn ( p_copy_entity_txn_id => p_copy_entity_txn_id );
742 --
743 return (l_req);
744 --
745 end generic_fn;
746 --
747 Procedure process_copy(p_copy_entity_txn_id in varchar2 ,
748 p_table_route_id in varchar2 ,
749 p_from_clause in varchar2 ,
750 p_table_alias in varchar2 ,
751 p_where_clause in varchar2 ,
752 p_pre_copy_proc in varchar2 ,
753 p_copy_proc in varchar2 ,
754 p_post_copy_proc in varchar2 ,
755 p_validate in boolean default false ) is
756 --
757 -- local variables
758 --
759 l_proc varchar2(72) := g_package||'process_copy';
760 l_cursor integer;
761 l_exec integer;
762 l_set_session varchar2(32000) ;
763 l_string varchar2(32000) := ' ';
764 l_attribute varchar2(32000) := ' ';
765 l_parameter varchar2(32000) := ' ';
766 l_pk_val varchar2(32000) := ' ';
767 l_column varchar2(32000) := ' ';
768 l_where varchar2(32000) ;
769 l_pre_copy_proc varchar2(32000) ;
770 l_copy_proc varchar2(32000) ;
771 l_post_copy_proc varchar2(32000) ;
772 l_log varchar2(2000) ;
773 l_l varchar2(32000) ;
774 i number;
775 --
776 -- cursor to fetch specified attributes
777 --
778 cursor c_attrib(v_attrib_type varchar2) is
779 select pa.column_name
780 , pa.column_type
781 , pa.width
782 , get_alias(pa.column_name) param
783 , decode(upper(pa.column_type)
784 , 'D' ,'L_'||get_alias(pa.column_name)||' DATE'
785 , 'V' ,'L_'||get_alias(pa.column_name)||' VARCHAR2'||'('||pa.width||')'
786 , 'C' ,'L_'||get_alias(pa.column_name)||' CHAR' ||'('||pa.width||')'
787 , 'N' ,'L_'||get_alias(pa.column_name)||' NUMBER' ||decode(pa.width,'0','','('||pa.width||')')
788 , 'L' ,'L_'||get_alias(pa.column_name)||' LONG'
789 , 'B' ,'L_'||get_alias(pa.column_name)||' BOOLEAN'
790 , 'L_'||get_alias(pa.column_name)||' '||pa.column_type||'('||pa.width||')') var_def
791 from pqh_attributes pa
792 , pqh_txn_category_attributes tca
793 , pqh_copy_entity_txns cet
794 , pqh_special_attributes sat
795 where pa.master_table_route_id = p_table_route_id
796 and pa.attribute_id = tca.attribute_id
797 and cet.copy_entity_txn_id = p_copy_entity_txn_id
798 and cet.transaction_category_id = tca.transaction_category_id
799 and sat.txn_category_attribute_id = tca.txn_category_attribute_id
800 and sat.attribute_type_cd = v_attrib_type --SELECT/PARAMETER/PRIMARY_KEY
801 and pa.enable_flag = 'Y'
802 and tca.select_flag = 'Y'
803 and sat.enable_flag = 'Y'
804 and sat.context = pqh_generic.g_gbl_context ;
805 begin
806 --
807 hr_utility.set_location('Entering:'||l_proc, 5);
808 --
809 -- Build table attributes, parameters and variables
810 --
811 for e_rec in c_attrib('SELECT') loop
812 if c_attrib%rowcount = 1 then
813 l_column := e_rec.column_name ; -- columns for the cursor
814 l_parameter := 'P_'||e_rec.param ||'=>L_'||e_rec.param; -- parameter for the functions
815 l_attribute := e_rec.var_def ||'; ' ; -- Variable to be defined for the attributes
816 l_string := assign_part(e_rec.param, 'SELECT') ; -- assignment string
817 else
818 l_column := l_column ||',' ||e_rec.column_name ||' '; -- columns for the cursor
819 l_parameter := l_parameter||', P_'||e_rec.param ||'=>L_'||e_rec.param; -- parameter for functions
820 l_attribute := l_attribute||' ' ||e_rec.var_def ||'; '; -- Variables definition
821 l_string := l_string || assign_part(e_rec.param, 'SELECT') ; -- assignment string
822 end if; --c_attrib%rowcount = 1
823 --
824
825 if e_rec.param = 'EFFECTIVE_START_DATE' then
826 l_string := l_string||' hr_utility.set_location(''Rec.Eff strt dt''||rec.effective_start_date,1000); ';
827 end if ;
828
829 end loop; -- c_attrib loop
830 --
834 -- attributes are for variable definitions
831 --hr_utility.set_location(' '||l_proc||' param/attribs', 10);
832 --
833 -- parameters are for the functions
835 --
836 for e_rec in c_attrib('PARAMETER') loop
837 if l_parameter is not null then
838 l_parameter := l_parameter||', P_'||e_rec.param ||'=>L_'||e_rec.param; -- parameter for functions
839 else
840 l_parameter := ', P_' ||e_rec.param||'=>L_' ||e_rec.param;
841 end if; --l_parameter is not null
842 --
843 if l_attribute is not null then
844 l_attribute := l_attribute||' ' ||e_rec.var_def ||'; '; -- Variables definition
845 else
846 l_attribute := e_rec.var_def ||'; ';
847 end if; --l_attribute is not null
848 --
849 l_string := l_string || assign_part(e_rec.param, 'PARAMETER') ; -- assignment string
850 end loop; -- c_attrib loop
851 --
852 -- add l_effective_date to the variable list to facilitate the use while setting session_date
853 --
854 if instr(upper(l_attribute), 'L_EFFECTIVE_DATE') = 0 then
855 l_attribute := l_attribute ||' l_effective_date date;';
856 end if;
857 --
858 --hr_utility.set_location(' '||l_proc||' pri key', 10);
859 --
860 --
861 -- loop to store values on the target table which would potentially affect the child records
862 --
863
864 l_pk_val := 'if pqh_generic.g_level = 1 then PQH_GENERIC.g_target_pk_table.delete;'
865 || 'PQH_GENERIC.assign_value(p_column_name => ''L_EFFECTIVE_DATE'''
866 ||' , p_column_type => ''D'''
867 ||' , p_value => L_EFFECTIVE_DATE'
868 ||'); end if; ';
869 --
870 for e_rec in c_attrib('PRIMARY_KEY') loop
871 l_pk_val := l_pk_val ||
872 ' PQH_GENERIC.assign_value(p_column_name => '''||e_rec.param ||''''
873 ||' , p_column_type => '''||e_rec.column_type ||''''
874 ||' , p_value => ' ||'L_' ||e_rec.param
875 ||'); ' ;
876 end loop; -- c_attrib
877 --
878 for e_rec in c_attrib('ERROR_KEY') loop
879 l_log := ' pqh_generic.log_error(p_table_route_id => '||p_table_route_id||',p_err_key => L_'|| e_rec.param ||');';
880 --
881 end loop; -- c_attrib
882 --
883 hr_utility.set_location(' '||l_proc||' repl where', 10);
884 --
885 --
886 -- Replace where clause
887 --
888 pqh_refresh_data.replace_where_params(upper(p_where_clause),'N','',l_where);
889 --
890 -- Suffix parameters if function name is available for pre/copy/post
891 -- start of bug 5052820
892 --
893 if (upd_where='Y') then
894 l_where:=l_where||' and '''|| nvl(l_eff_date,trunc(sysdate)) ||''' between effective_start_date
895 and effective_end_date';
896 end if;
897 --
898 -- end of bug 5052820
899 --
900 if p_pre_copy_proc is null then
901 l_pre_copy_proc := 'null ';
902 else
903 l_pre_copy_proc := p_pre_copy_proc||'('||l_parameter||')';
904 end if; --p_pre_copy_proc is not null
905 hr_utility.set_location(l_proc||' pcp: '||l_pre_copy_proc,5);
906 --
907 if p_copy_proc is null then
908 l_copy_proc := 'null ';
909 else
910 l_copy_proc := p_copy_proc||'('||l_parameter||')';
911 end if; --p_copy_proc is not null
912 hr_utility.set_location(l_proc||' cp: '||p_copy_proc,5);
913 --
914 if p_post_copy_proc is null then
915 l_post_copy_proc := 'null ';
916 else
917 l_post_copy_proc := p_post_copy_proc||'('||l_parameter||')';
918 end if; --p_post_copy_proc is not null
919 hr_utility.set_location(l_proc||' pcp: '||l_post_copy_proc,5);
920 --
921 -- set session date incase the same is not set.
922 --
923 if (upd_where='N') then
924 l_set_session := 'declare v_eff_date date;'
925 ||'cursor c1 is select effective_date from fnd_sessions where session_id = userenv(''sessionid''); '
926 ||'begin '
927 ||'l_effective_date := pqh_generic.get_src_effective_date ; '
928 ||'if c1%isopen then close c1; end if; '
929 ||'open c1; fetch c1 into v_eff_date; '
930 ||'if c1%notfound then '
931 ||'insert into fnd_sessions(session_id, effective_date)'
932 ||' values (userenv(''sessionid''), nvl(l_effective_date, trunc(sysdate)) ); '
933 ||'elsif l_effective_date <> v_eff_date then '
934 ||'update fnd_sessions set effective_date=l_effective_date where session_id=userenv(''sessionid''); '
935 ||'end if; '
936 ||'close c1; '
937 ||'end ;' ;
938 --
939 else
940 l_set_session := 'declare v_eff_date date;'
941 ||'cursor c1 is select effective_date from fnd_sessions where session_id = userenv(''sessionid''); '
942 ||'begin '
943 ||'l_effective_date := pqh_generic.get_trg_effective_date ; '
944 ||'if c1%isopen then close c1; end if; '
945 ||'open c1; fetch c1 into v_eff_date; '
946 ||'if c1%notfound then '
947 ||'insert into fnd_sessions(session_id, effective_date)'
948 ||' values (userenv(''sessionid''), nvl(l_effective_date, trunc(sysdate)) ); '
949 ||'elsif l_effective_date <> v_eff_date then '
953 ||'end ;' ;
950 ||'update fnd_sessions set effective_date=l_effective_date where session_id=userenv(''sessionid''); '
951 ||'end if; '
952 ||'close c1; '
954
955 end if;
956 -- bug 5052820 modified the above l_set_session by changing the call to get_trg_effective_date
957
958 hr_utility.set_location(l_proc||' L_efffective_date: '||to_char(pqh_generic.get_src_effective_date,'MM/DD/RRRR'),51);
959 --
960 /**
961 * Bug Fix: 3032847
962 * Describe: to copy the work choice
963 **/
964 l_l := 'declare '
965 || l_attribute
966 || ' cursor c_at is select '
967 || nvl(l_column,'')
968 || ' from '
969 || p_from_clause
970 || ' where '
971 || l_where
972 || '; '
973 || 'begin '
974 || l_set_session
975 || ' for Rec in c_at loop '
976 || 'begin '
977 || l_string
978 || l_pre_copy_proc
979 || '; '
980 || l_copy_proc
981 || '; '
982 || l_post_copy_proc
983 || '; '
984 || l_pk_val
985 || 'exception '
986 || ' when others then '
987 || ' PQH_GENERIC.g_conc_warn_flag := true; '
988 || ' PQH_GENERIC.v_err := substr(sqlerrm, 1, 4000) ; '
989 || l_log
990 || 'end; '
991 || 'end loop; '
992 || 'end; ' ;
993 --
994 -- trace the dyn statement
995 --
996 hr_utility.trace('BEGIN SPOOL DYN STATEMENT-PQH_GENERIC ');
997
998 hr_utility.trace(substr(l_l,1,2000 ));
999 hr_utility.trace(substr(l_l,2001,2000 ));
1000 hr_utility.trace(substr(l_l,4001,2000 ));
1001 hr_utility.trace(substr(l_l,6001,2000 ));
1002 hr_utility.trace(substr(l_l,8001,2000 ));
1003 hr_utility.trace(substr(l_l,10001,2000 ));
1004 hr_utility.trace(substr(l_l,12001,2000 ));
1005 hr_utility.trace(substr(l_l,14001,2000 ));
1006 hr_utility.trace(substr(l_l,16001,2000 ));
1007 hr_utility.trace(substr(l_l,18001,2000 ));
1008 hr_utility.trace(substr(l_l,20001,2000 ));
1009 hr_utility.trace(substr(l_l,22001,2000 ));
1010 hr_utility.trace(substr(l_l,24001,2000 ));
1011 hr_utility.trace(substr(l_l,26001,2000 ));
1012 hr_utility.trace(substr(l_l,28001,2000 ));
1013 hr_utility.trace(substr(l_l,30001,2000 ));
1014 --hr_utility.trace(' ');
1015 hr_utility.trace('END SPOOL DYN STATEMENT-PQH_GENERIC ');
1016 --
1017 --
1018 -- Build PL/SQL Statement
1019 --
1020 hr_utility.set_location(l_proc||' : Starting function calls ',5);
1021 execute immediate l_l;
1022 /* 'declare '
1023
1024 || l_attribute
1025 || ' cursor c_at is select '
1026 || nvl(l_column,'')
1027 || ' from '
1028 || p_from_clause
1029 || ' where '
1030 || l_where
1031 || '; '
1032 || 'begin '
1033 || 'hr_utility.set_location(''Start dyn statement'',1); '
1034 || l_set_session
1035 || 'hr_utility.set_location(''after session_date: eff dt= ''||l_effective_date ,1); '
1036 || 'hr_utility.set_location(''effective start Date''||l_effective_start_date ,1); '
1037 || 'for Rec in c_at loop '
1038 || 'begin '
1039 || 'hr_utility.set_location(''inside loop'',1); '
1040 || l_string
1041 || l_pre_copy_proc
1042 || '; '
1043 || l_copy_proc
1044 || '; '
1045 || l_post_copy_proc
1046 || '; '
1047 || l_pk_val
1048 || 'exception '
1049 || ' when others then '
1050 || ' PQH_GENERIC.v_err := substr(sqlerrm, 1, 4000) ; '
1051 || l_log
1052 || 'end; '
1053 || 'end loop; '
1054 || 'end; ' ;
1055 */
1056 hr_utility.set_location(nvl(substr(PQH_GENERIC.v_err,1 ,100),'Completed...'),5);
1057 hr_utility.set_location(nvl(substr(PQH_GENERIC.v_err,100,100),'************'),5);
1058
1059 end process_copy;
1060
1061 procedure populate_table
1062 is
1063 l_sr_type varchar2(10);
1064 l_tg_type varchar2(10);
1065 l_tg_val varchar2(2000);
1066
1067 begin
1068 --
1069 -- to replace the where params.. a global plsql table is to be populated.
1070 --
1071 hr_utility.set_location('Entering :'||g_package||'populate_table ',25);
1072 pqh_refresh_data.g_refresh_tab.delete;
1073 --
1074 for i in nvl(PQH_GENERIC.g_source_pk_table.first,0)..nvl(PQH_GENERIC.g_source_pk_table.last,-1) loop
1075 --
1076 pqh_refresh_data.g_refresh_tab(i).column_name := upper(PQH_GENERIC.g_source_pk_table(i).column_name);
1077 --
1078 l_sr_type := substr(PQH_GENERIC.g_source_pk_table(i).column_type,1,1);
1079 --
1080 if l_sr_type = 'D' or l_sr_type = 'd' then
1081 --
1082 l_tg_type := 'N';
1083 --
1084 if length(PQH_GENERIC.g_source_pk_table(i).value) = 10 then
1085 l_tg_val := ' to_date('''||PQH_GENERIC.g_source_pk_table(i).value||''',''MM/DD/RRRR'') ';
1086 else
1087 l_tg_type := 'D';
1088 l_tg_val := PQH_GENERIC.g_source_pk_table(i).value ;
1089 end if; -- if length(g_source_pk_table)
1090 --
1094 l_tg_val := PQH_GENERIC.g_source_pk_table(i).value ;
1091 elsif l_sr_type = 'V' or l_sr_type = 'v' then
1092 --
1093 l_tg_type := 'V';
1095 else
1096 --
1097 l_tg_type := 'N';
1098 l_tg_val := PQH_GENERIC.g_source_pk_table(i).value ;
1099 end if; --if l_sr_type =
1100 --
1101 pqh_refresh_data.g_refresh_tab(i).column_type := l_tg_type;
1102 pqh_refresh_data.g_refresh_tab(i).txn_val := l_tg_val;
1103 pqh_refresh_data.g_refresh_tab(i).shadow_val := l_tg_val;
1104 pqh_refresh_data.g_refresh_tab(i).main_val := l_tg_val;
1105 pqh_refresh_data.g_refresh_tab(i).refresh_flag := 'N';
1106 pqh_refresh_data.g_refresh_tab(i).updt_flag := 'N';
1107 --
1108 end loop ; --for i in PQH_GENERIC.g_source_pk_table.first..PQH_GENERIC.g_source_pk_table.last loop
1109 hr_utility.set_location('Leaving :'||g_package||'populate_table ',25);
1110 end populate_table;
1111
1112 procedure Raise_Error(p_copy_entity_result_id in number,
1113 p_msg_code in varchar2)
1114 is
1115 begin
1116 --
1117 -- update the status with error/warning message
1118 --
1119 rollback ;
1120 --
1121 hr_utility.set_location(pqh_generic.v_err,10);
1122 -- update pqh_copy_entity_results
1123 -- set status = substr(substr(p_msg_code,1,1)||replace(pqh_generic.v_err,'ORA'),1,30)
1124 -- ,number_of_copies = '0'
1125 -- where copy_entity_result_id = p_copy_entity_result_id;
1126 -- commit;
1127 if pqh_generic.g_calling_mode = 'CONCURRENT' then
1128 fnd_file.new_line (fnd_file.log,1);
1129 fnd_file.put_line (fnd_file.log, p_msg_code);
1130 fnd_file.new_line (fnd_file.log,1);
1131 fnd_file.put_line (fnd_file.log, pqh_generic.v_err);
1132 end if;
1133 pqh_generic.v_err := '';
1134 hr_utility.set_location('**********************',10);
1135 hr_utility.set_location(sqlerrm,10);
1136 hr_utility.set_location('.......Oops..Error !!!',10);
1137 hr_utility.set_location('**********************',10);
1138 --hr_utility.raise_error;
1139 end Raise_Error;
1140 --
1141 function assign_part( p_column_name in varchar2 ,
1142 p_attrib_type in varchar2 ) return varchar2 is
1143 l_type varchar2(10);
1144 l_val varchar2(2000);
1145 begin
1146 --
1147 -- Assigning a variable with data selected from the database
1148 -- if the target global is populated with a value the same is used
1149 -- else the data from the selected source cursor is used.
1150 --
1151 for i in nvl(PQH_GENERIC.g_target_pk_table.first,0)..nvl(PQH_GENERIC.g_target_pk_table.last,-1) loop
1152 --
1153 -- if instr(upper(p_column_name),upper(PQH_GENERIC.g_target_pk_table(i).column_name)) > 0 then
1154 if upper(PQH_GENERIC.g_target_pk_table(i).column_name) = upper(p_column_name) then
1155 --
1156 l_type := substr(PQH_GENERIC.g_target_pk_table(i).column_type,1,1);
1157 --
1158 if l_type = 'D' or l_type = 'd' then
1159 --
1160 if length(PQH_GENERIC.g_target_pk_table(i).value) = 18 then
1161 l_val := ' to_date('''||PQH_GENERIC.g_target_pk_table(i).value||''',''RRRR/MM/DDHH24:MI:SS'') ';
1162 return('L_'||p_column_name||' := '||l_val||';' );
1163 elsif length(PQH_GENERIC.g_target_pk_table(i).value) = 19 then
1164 l_val := ' to_date('''||PQH_GENERIC.g_target_pk_table(i).value||''',''RRRR/MM/DD HH24:MI:SS'') ';
1165 return('L_'||p_column_name||' := '||l_val||';' );
1166 elsif length(PQH_GENERIC.g_target_pk_table(i).value) = 10 then
1167 l_val := ' to_date('''||PQH_GENERIC.g_target_pk_table(i).value||''',''MM/DD/RRRR'') ';
1168 return('L_'||p_column_name||' := '||l_val||';' );
1169 else
1170 l_val := PQH_GENERIC.g_target_pk_table(i).value ;
1171 return('L_'||p_column_name||' := '''||replace(l_val,'''','''''')||''';' );
1172 end if; -- if length(g_source_pk_table)
1173 --
1174 elsif (l_type = 'N' or l_type = 'n') and l_val is not null then
1175 -- addition of l_val is not null is vide bug 3373486
1176 --
1177 l_val := PQH_GENERIC.g_target_pk_table(i).value ;
1178 return('L_'||p_column_name||' := '||l_val||';' );
1179 --
1180 else
1181 --
1182 l_val := PQH_GENERIC.g_target_pk_table(i).value ;
1183 return('L_'||p_column_name||' := '''||replace(l_val,'''','''''')||''';' );
1184 end if; --if l_type =
1185 --
1186 hr_utility.set_location('assign :'||' := '''||l_val||'''',100);
1187 end if;
1188 end loop;
1189 --
1190 -- If no match was found then return assign only where the attribute type is not PARAMETER
1191 --
1192 if p_attrib_type = 'PARAMETER' then
1193 return('');
1194 else
1195 return('L_'||p_column_name||' := Rec.'||p_column_name||';');
1196 end if;
1197 end assign_part;
1198 --
1199 function get_src_effective_date return date is
1200 l_type varchar2(10);
1201 l_val date := trunc(sysdate);
1202 begin
1203 --
1204 -- Assigning a variable with data selected from the database
1205 -- if the source global is populated with a value the same is used
1206 -- else the data from the selected source cursor is used.
1207 --
1208 for i in nvl(PQH_GENERIC.g_source_pk_table.first,0)..nvl(PQH_GENERIC.g_source_pk_table.last,-1) loop
1209 --
1210 if upper(PQH_GENERIC.g_source_pk_table(i).column_name) = 'EFFECTIVE_DATE' then
1211 --
1212 l_type := substr(PQH_GENERIC.g_source_pk_table(i).column_type,1,1);
1213 --
1217 l_val := to_date(PQH_GENERIC.g_source_pk_table(i).value,'RRRR/MM/DDHH24:MI:SS') ;
1214 if l_type = 'D' or l_type = 'd' then
1215 --
1216 if length(PQH_GENERIC.g_source_pk_table(i).value) = 18 then
1218 return(l_val );
1219 elsif length(PQH_GENERIC.g_source_pk_table(i).value) = 19 then
1220 l_val := to_date(PQH_GENERIC.g_source_pk_table(i).value,'RRRR/MM/DD HH24:MI:SS');
1221 return(l_val );
1222 elsif length(PQH_GENERIC.g_source_pk_table(i).value) = 10 then
1223 l_val := to_date(PQH_GENERIC.g_source_pk_table(i).value,'MM/DD/RRRR') ;
1224 return(l_val );
1225 else
1226 l_val := PQH_GENERIC.g_source_pk_table(i).value ;
1227 return(l_val );
1228 end if; -- if length(g_source_pk_table)
1229 --
1230 else
1231 --
1232 return(l_val);
1233 end if; --if l_type =
1234 --
1235 end if;
1236 end loop;
1237 --
1238 return(l_val);
1239 exception when others then
1240 return(l_val);
1241 end get_src_effective_date ;
1242 --
1243 -- start of bug 5052820
1244 --
1245 function get_trg_effective_date return date is
1246 l_type varchar2(10);
1247 l_val date := trunc(sysdate);
1248 begin
1249 --
1250 IF ( l_eff_date IS NOT NULL ) THEN
1251 RETURN (l_eff_date);
1252 ELSE
1253 RETURN (pqh_generic.get_src_effective_date);
1254 end if;
1255 exception when others then
1256 return(l_val);
1257 end get_trg_effective_date ;
1258 --
1259 -- edn of bug 5052820
1260 procedure assign_value(p_column_name varchar2,
1261 p_column_type varchar2,
1262 p_value varchar2,
1263 p_reset_flag varchar2 default 'N',
1264 p_source_flag varchar2 default 'N')
1265 is
1266 l_count number;
1267 l_check boolean := true;
1268
1269 begin
1270 --
1271 -- This populated the source and target plsql table with details to be used at a later point and time
1272 --
1273 hr_utility.set_location('Entering : '||g_package||'assign_value '||p_source_flag,15);
1274 hr_utility.set_location(' : '||'column_name '||substr(p_column_name,1,60),15);
1275 hr_utility.set_location(' : '||'column_type '||substr(p_column_type,1,60),15);
1276 hr_utility.set_location(' : '||'value ' ||substr(p_value,1,60),15);
1277 hr_utility.set_location(' : '||'reset_flag ' ||substr(p_reset_flag,1,60),15);
1278 --
1279 if upper(p_source_flag) = 'Y' then
1280 --
1281 if p_reset_flag = 'Y' then
1282 PQH_GENERIC.g_target_pk_table.delete;
1283 --
1284 end if; -- p_reset_flag = 'Y'
1285 --
1286 if nvl(PQH_GENERIC.g_source_pk_table.last,0) <> 0 then
1287 for i in PQH_GENERIC.g_source_pk_table.first..PQH_GENERIC.g_source_pk_table.last loop
1288 if PQH_GENERIC.g_source_pk_table(i).column_name = upper(p_column_name) then
1289 PQH_GENERIC.g_source_pk_table(i).column_name := upper(p_column_name) ;
1290 PQH_GENERIC.g_source_pk_table(i).column_type := upper(p_column_type) ;
1291 PQH_GENERIC.g_source_pk_table(i).value := p_value ;
1292 l_check := false ;
1293 exit;
1294 end if; --PL Table Check
1295 end loop; -- source PL table loop
1296 end if;
1297 --
1298 if l_check then
1299 l_count := nvl(PQH_GENERIC.g_source_pk_table.last,0);
1300 PQH_GENERIC.g_source_pk_table(l_count+1).column_name := upper(p_column_name) ;
1301 PQH_GENERIC.g_source_pk_table(l_count+1).column_type := upper(p_column_type) ;
1302 PQH_GENERIC.g_source_pk_table(l_count+1).value := p_value ;
1303 end if; -- l_check
1304 else
1305 --
1306 -- start of the bug 5052820
1307 --
1308 if(substr(p_column_name,1,60)='EFFECTIVE_DATE') then
1309 l_eff_date :=fnd_date.canonical_to_date(p_value);
1310 end if;
1311 --
1312 -- end of the bug 5052820
1313 if p_reset_flag = 'Y' then
1314 PQH_GENERIC.g_target_pk_table.delete;
1315 --
1316 end if; -- p_reset_flag = 'Y'
1317 --
1318 if nvl(PQH_GENERIC.g_target_pk_table.last,0) <> 0 then
1319 for i in PQH_GENERIC.g_target_pk_table.first..PQH_GENERIC.g_target_pk_table.last loop
1320 if PQH_GENERIC.g_target_pk_table(i).column_name = upper(p_column_name) then
1321 PQH_GENERIC.g_target_pk_table(i).column_name := upper(p_column_name) ;
1322 PQH_GENERIC.g_target_pk_table(i).column_type := upper(p_column_type) ;
1323 PQH_GENERIC.g_target_pk_table(i).value := p_value ;
1324 l_check := false ;
1325 exit;
1326 end if; --PL Table Check
1327 end loop; --target PL table loop
1328 end if;
1329 --
1330 if l_check then
1331 l_count := nvl(PQH_GENERIC.g_target_pk_table.last,0);
1332 PQH_GENERIC.g_target_pk_table(l_count+1).column_name := upper(p_column_name) ;
1333 PQH_GENERIC.g_target_pk_table(l_count+1).column_type := upper(p_column_type) ;
1334 PQH_GENERIC.g_target_pk_table(l_count+1).value := p_value ;
1335 end if; --l_check
1336 end if; -- p_source_flag = 'Y'
1337 --
1338 hr_utility.set_location('Leaving : '||g_package||'assign_value ',15);
1339 end assign_value;
1340 --
1341 Procedure dynamic_pltab_populate (p_ddf_column_name in varchar2
1342 , p_copy_entity_result_id in number
1343 , p_copy_entity_txn_id in number
1347 , p_source_flag in varchar2) is
1344 , p_column_name in varchar2
1345 , p_column_type in varchar2
1346 , p_reset_flag in varchar2
1348 begin
1349 --
1350 -- Assigns values to the plsql table from identified ddf_column
1351 --
1352 hr_utility.set_location('Entering :'||g_package||'dynamic_pltab_pop '||p_source_flag,11);
1353 hr_utility.set_location(' : '||'ddf_column_name ='||p_ddf_column_name,15);
1354 hr_utility.set_location(' : '||'column_name ='||p_column_name,15);
1355 execute immediate 'declare '
1356 || 'cursor c1 is '
1357 || 'select cer.'||p_ddf_column_name ||' value '
1358 || 'from pqh_copy_entity_results cer '
1359 || 'where cer.copy_entity_result_id = '||p_copy_entity_result_id ||' '
1360 || 'and cer.copy_entity_txn_id = '||p_copy_entity_txn_id ||'; '
1361 || 'begin '
1362 || 'for i in c1 loop '
1363 || 'pqh_generic.assign_value( p_column_name => '''|| p_column_name ||''''
1364 || ',p_column_type => '''|| p_column_type ||''''
1365 || ',p_value => i.value '
1366 || ',p_reset_flag => '''|| p_reset_flag ||''''
1367 || ',p_source_flag => '''|| p_source_flag ||''' ); '
1368 || 'end loop; '
1369 || 'end; ';
1370
1371 hr_utility.set_location('Leaving :'||g_package||'dynamic_pltab_pop ',11);
1372 end dynamic_pltab_populate;
1373
1374
1375 function get_alias(p_column_name in varchar2) return varchar2
1376 is
1377 begin
1378 return( substr(substr(p_column_name,instr(rtrim(p_column_name), '.',1)+1),
1379 instr(rtrim(substr(p_column_name,instr(rtrim(p_column_name), '.',1)+1)),
1380 ' ', -1 ,1)+1) );
1381 end;
1382 --
1383 --
1384 function get_user_pref( p_user_id number
1385 , p_transaction_category_id number
1386 , p_table_route_id number )
1387 return boolean is
1388 --
1389 -- used from forms .. needs changes
1390 --
1391 l_pref1 number(15);
1392 l_pref2 number(15);
1393 l_pref3 number(15);
1394 l_pref4 number(15);
1395 l_pref5 number(15);
1396 l_pref6 number(15);
1397 l_pref7 number(15);
1398 l_pref8 number(15);
1399 l_pref9 number(15);
1400 l_pref10 number(15);
1401 l_pref11 number(15);
1402 l_pref12 number(15);
1403 --
1404 --cursor c_rec is
1405 -- select pref1, pref2, pref3, pref4, pref5, pref6,
1406 -- pref7, pref8, pref9, pref10, pref11, pref12
1407 -- from pqh_user_copy_preference
1408 -- where user_id = p_user_id
1409 -- and transaction_category_id = p_transaction_category_id ;
1410 --
1411 begin
1412 --
1413 -- open c_rec;
1414 --
1415 -- fetch c_rec into l_pref1, l_pref2, l_pref3, l_pref4, l_pref5, l_pref6,
1416 -- l_pref7, l_pref8, l_pref9, l_pref10, l_pref11, l_pref12 ;
1417 --
1418 -- if c_rec%notfound then
1419 -- return (true);
1420 -- end if;
1421 --
1422 if l_pref1 = p_table_route_id or
1423 l_pref2 = p_table_route_id or
1424 l_pref3 = p_table_route_id or
1425 l_pref4 = p_table_route_id or
1426 l_pref5 = p_table_route_id or
1427 l_pref6 = p_table_route_id or
1428 l_pref7 = p_table_route_id or
1429 l_pref8 = p_table_route_id or
1430 l_pref9 = p_table_route_id or
1431 l_pref10 = p_table_route_id or
1432 l_pref11 = p_table_route_id or
1433 l_pref12 = p_table_route_id then
1434 return (true);
1435 else
1436 return (false);
1437 end if;
1438 --
1439 -- close c_rec;
1440 --
1441 end get_user_pref;
1442 --
1443 procedure log_error (p_table_route_id in varchar2 ,
1444 p_err_key in varchar2 ) is
1445 --
1446 l_calling_mode varchar2(60) := pqh_generic.g_calling_mode;
1447 l_copy_entity_result_id number := nvl(pqh_generic.g_result_id,-999.00);
1448 l_src_copy_entity_result_id number ;
1449 l_status varchar2(30) ;
1450 --
1451 cursor c_tgt is
1452 select copy_entity_txn_id,
1453 src_copy_entity_result_id
1454 from pqh_copy_entity_results
1455 where copy_entity_result_id = l_copy_entity_result_id ;
1456 --
1457 cursor c_src (l_src_id in number, l_status in varchar2) is
1458 select copy_entity_txn_id,
1459 copy_entity_result_id
1460 from pqh_copy_entity_results
1461 where copy_entity_result_id = l_src_id
1462 and status in ( 'COMPLETED', 'DPT_ERR')
1463 and status <> l_status ;
1464 --
1465 begin
1466 g_success := false; -- set the flag to identify the fail (helps identify master fail)
1467 --
1468 -- This procedure needs to be completed with error logging mechanisms
1469 -- and status updates for TXN/SRC/TGT Records
1470 --
1471 hr_utility.trace('Result id :'||l_copy_entity_result_id);
1472 hr_utility.trace('Call mode :'||l_calling_mode);
1473 hr_utility.trace('Table id :'||p_table_route_id);
1474 hr_utility.trace('Err Key :'||p_err_key);
1475 hr_utility.trace('Err :'||substr(v_err,1,255));
1476 hr_utility.trace('Err :'||substr(v_err,255,255));
1477 hr_utility.trace('Err :'||substr(v_err,510,255));
1478 --
1479 if nvl(pqh_generic.g_level,1) = 2 then
1483 p_log_context => p_err_key);
1480 pqh_process_batch_log.set_context_level (p_txn_id => nvl(p_err_key, l_copy_entity_result_id),
1481 p_txn_table_route_id => p_table_route_id,
1482 p_level => 3,
1484 --
1485 pqh_process_batch_log.insert_log ( p_message_type_cd => 'ERROR',
1486 p_message_text => pqh_generic.v_err );
1487 --
1488 l_status := 'DPT_ERR' ;
1489 update pqh_copy_entity_results
1490 set status = 'DPT_ERR',
1491 number_of_copies = 0
1492 where copy_entity_result_id = l_copy_entity_result_id;
1493 else
1494 -- for target records the context is already set before starting the dyn calls
1495 --
1496 -- pqh_process_batch_log.set_context_level (p_txn_id => l_copy_entity_result_id,
1497 -- p_txn_table_route_id => p_table_route_id,
1498 -- p_level => 2,
1499 -- p_log_context => l_copy_entity_result_id );
1500 --
1501 pqh_process_batch_log.insert_log ( p_message_type_cd => 'ERROR',
1502 p_message_text => pqh_generic.v_err );
1503 --
1504 l_status := 'TGT_ERR' ;
1505 update pqh_copy_entity_results
1506 set status = 'TGT_ERR'
1507 where copy_entity_result_id = l_copy_entity_result_id;
1508 --
1509 end if;
1510 --
1511 for i in c_tgt loop
1512 --
1513 l_src_copy_entity_result_id := i.src_copy_entity_result_id ;
1514 end loop;
1515 --
1516 for i in c_src (l_src_copy_entity_result_id, 'NONE' ) loop
1517 update pqh_copy_entity_results
1518 set status = l_status
1519 where copy_entity_result_id = i.copy_entity_result_id;
1520 --
1521 update pqh_copy_entity_txns
1522 set status = l_status
1523 where copy_entity_txn_id = i.copy_entity_txn_id
1524 and status <> 'TGT_ERR';
1525 --
1526 end loop;
1527 --
1528 pqh_generic.v_err := '';
1529 --
1530 end log_error;
1531 --
1532 end PQH_GENERIC;