[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_INTEGRATOR_COPY
Source
1 Package Body BEN_CWB_INTEGRATOR_COPY as
2 /* $Header: bencwbic.pkb 120.2 2010/09/22 06:59:22 sgnanama noship $ */
3
4 g_package Varchar2(30) := 'BEN_CWB_INTEGRATOR_COPY.';
5 g_debug boolean := hr_utility.debug_enabled;
6
7 --
8 -------------------------- copy_integrator ----------------------
9 --
10
11 PROCEDURE copy_integrator( p_group_pl_id IN NUMBER,
12 p_integrator_code IN VARCHAR2
13 )
14 IS
15
16 l_suffix varchar2(100) := '_' || trim(p_group_pl_id);
17 l_new_integrator_code varchar2(100) := 'BEN_CWB_WS_INTG' || l_suffix;
18 l_new_interface_code varchar2(100) := 'BEN_CWB_WS_INTF' || l_suffix;
19 l_new_content_code varchar2(100) := 'BEN_CWB_WS_CNT' || l_suffix;
20 l_new_mapping_code varchar2(100) := 'BEN_CWB_WS_MAP' || l_suffix;
21 l_new_layout_code varchar2(100) ;
22 l_layout_count number := 1;
23 l_step varchar2(100);
24 l_plan_name varchar2(100);
25 l_created varchar2(1) := 'N';
26 cursor c_plan_name is
27 select name || ' - ' from ben_cwb_pl_dsgn
28 where pl_id = p_group_pl_id and pl_id = group_pl_id
29 and oipl_id = -1 and group_oipl_id = -1;
30
31 cursor c_bne_integrators is
32 select a.application_short_name,
33 v.integrator_code,
34 substr(fnd_load_util.owner_name(v.last_updated_by),1,30) owner,
35 v.object_version_number,
36 v.enabled_flag,
37 substr(bne_lct_tools_pkg.app_id_to_asn(v.upload_param_list_app_id),1,30) upl_param_list_asn,
38 v.upload_param_list_code upl_param_list_code,
39 substr(bne_lct_tools_pkg.app_id_to_asn(v.upload_serv_param_list_app_id),1,30) upl_serv_param_list_asn,
40 v.upload_serv_param_list_code upl_serv_param_list_code,
41 substr(bne_lct_tools_pkg.app_id_to_asn(v.import_param_list_app_id),1,30) import_param_list_asn,
42 v.import_param_list_code,
43 v.date_format,
44 v.import_type,
45 v.uploader_class,
46 v.user_name,
47 v.upload_header,
48 v.upload_title_bar,
49 to_char(v.last_update_date, 'yyyy/mm/dd') last_update_date,
50 substr(bne_lct_tools_pkg.app_id_to_asn(v.create_doc_list_app_id),1,30) create_doc_param_list_asn,
51 v.create_doc_list_code create_doc_param_list_code,
52 v.new_session_flag,
53 v.layout_resolver_class,
54 v.layout_verifier_class,
55 v.session_config_class,
56 substr(bne_lct_tools_pkg.app_id_to_asn(v.session_param_list_app_id),1,30) session_param_list_asn,
57 v.session_param_list_code,
58 substr(bne_lct_tools_pkg.app_id_to_asn(s.application_id),1,30),
59 s.object_code,
60 s.object_type,
61 v.display_flag
62 from bne_integrators_vl v, bne_secured_objects s, fnd_application a
63 where v.application_id = a.application_id
64 and v.application_id = s.application_id (+)
65 and v.integrator_code = s.object_code (+)
66 and s.object_type (+) = 'INTEGRATOR'
67 and v.integrator_code = p_integrator_code
68 and a.application_short_name = 'PER'
69 and not exists (select null from bne_integrators_b b where b.integrator_code = l_new_integrator_code)
70 order by a.application_short_name, v.integrator_code;
71
72 cursor c_bne_interfaces is
73 select a.application_short_name,
74 v.interface_code,
75 substr(fnd_load_util.owner_name(v.last_updated_by),1,30) owner,
76 v.object_version_number,
77 v.interface_name,
78 v.upload_type,
79 v.upload_obj_name,
80 substr(bne_lct_tools_pkg.app_id_to_asn(v.upload_param_list_app_id),1,30) upload_param_list_asn,
81 v.upload_param_list_code,
82 v.upload_order,
86 where v.application_id = a.application_id
83 v.user_name,
84 to_char(v.last_update_date, 'yyyy/mm/dd') last_update_date
85 from bne_interfaces_vl v, fnd_application a
87 and v.integrator_code = p_integrator_code
88 and a.application_short_name = 'PER'
89 order by a.application_short_name, v.interface_code;
90
91 cursor c_bne_interface_cols (
92 v_interface_code IN VARCHAR2 ) is
93 select v.sequence_num,
94 substr(fnd_load_util.owner_name(v.last_updated_by),1,30) owner,
95 v.object_version_number,
96 v.interface_col_type,
97 v.interface_col_name,
98 v.enabled_flag,
99 v.required_flag,
100 v.display_flag,
101 v.read_only_flag,
102 v.not_null_flag,
103 v.summary_flag,
104 v.mapping_enabled_flag,
105 v.data_type,
106 v.field_size,
107 v.default_type,
108 v.default_value,
109 v.segment_number,
110 v.group_name,
111 v.oa_flex_code,
112 v.oa_concat_flex,
113 v.val_type,
114 v.val_id_col,
115 v.val_mean_col,
116 v.val_desc_col,
117 v.val_obj_name,
118 v.val_addl_w_c,
119 substr(bne_lct_tools_pkg.app_id_to_asn(v.val_component_app_id),1,30) val_component_asn,
120 v.val_component_code,
121 v.oa_flex_num,
122 v.oa_flex_application_id,
123 v.display_order,
124 v.upload_param_list_item_num,
125 v.expanded_sql_query,
126 v.user_hint,
127 v.prompt_left,
128 v.prompt_above,
129 v.user_help_text,
130 to_char(v.last_update_date, 'yyyy/mm/dd') last_update_date,
131 v.lov_type,
132 v.offline_lov_enabled_flag,
133 v.variable_data_type_class,
134 v.viewer_group,
135 v.edit_type,
136 v.display_width,
137 substr(bne_lct_tools_pkg.app_id_to_asn(v.val_query_app_id),1,30) val_query_asn,
138 v.val_query_code,
139 substr(bne_lct_tools_pkg.app_id_to_asn(v.expanded_sql_query_app_id),1,30) expanded_sql_query_asn,
140 v.expanded_sql_query_code
141 from bne_interface_cols_vl v, fnd_application a
142 where v.application_id = a.application_id
143 and v.interface_code = v_interface_code
144 and a.application_short_name = 'PER'
145 order by a.application_short_name, v.interface_code, v.sequence_num;
146
147 cursor c_bne_contents is
148 select a.application_short_name,
149 v.content_code,
150 v.object_version_number,
151 substr(fnd_load_util.owner_name(v.last_updated_by),1,30) owner,
152 substr(bne_lct_tools_pkg.app_id_to_asn(v.param_list_app_id),1,30) param_list_asn,
153 v.param_list_code,
154 v.content_class,
155 v.user_name,
156 to_char(v.last_update_date, 'yyyy/mm/dd') last_update_date,
157 v.once_only_download_flag
158 from bne_contents_vl v, fnd_application a
159 where v.integrator_app_id = a.application_id
160 and v.integrator_code = p_integrator_code
161 and a.application_short_name = 'PER'
162 order by a.application_short_name, v.content_code;
163
164 cursor c_bne_content_cols (
165 v_content_code IN VARCHAR2 ) is
166 select v.sequence_num,
167 v.object_version_number,
168 substr(fnd_load_util.owner_name(v.last_updated_by),1,30) owner,
169 v.col_name,
170 v.user_name,
171 to_char(v.last_update_date, 'yyyy/mm/dd') last_update_date,
172 v.read_only_flag
173 from bne_content_cols_vl v, fnd_application a
174 where v.application_id = a.application_id
175 and v.content_code = v_content_code
176 and a.application_short_name = 'PER'
177 order by a.application_short_name, v.content_code, v.sequence_num;
178
179 cursor c_bne_stored_sql (
180 v_content_code IN VARCHAR2 ) is
181 select v.object_version_number,
182 v.query,
183 substr(bne_lct_tools_pkg.app_id_to_asn(v.query_app_id),1,30) bne_query_query_asn,
184 v.query_code,
185 substr(fnd_load_util.owner_name(v.last_updated_by),1,30) owner,
186 to_char(v.last_update_date, 'yyyy/mm/dd') last_update_date
187 from bne_stored_sql v, fnd_application a
188 where v.application_id = a.application_id
189 and v.content_code = v_content_code
190 and a.application_short_name = 'PER';
191
192 cursor c_bne_mappings is
193 select a.application_short_name,
194 v.mapping_code,
195 v.reporting_flag,
196 substr(bne_lct_tools_pkg.app_id_to_asn(v.reporting_interface_app_id),1,30) reporting_interface_asn,
197 v.reporting_interface_code,
198 v.user_name,
199 substr(fnd_load_util.owner_name(v.last_updated_by),1,30) owner,
200 v.object_version_number,
201 to_char(v.last_update_date, 'yyyy/mm/dd') last_update_date
202 from bne_mappings_vl v, fnd_application a
203 where v.application_id = a.application_id
204 and v.integrator_code = p_integrator_code
205 and a.application_short_name = 'PER'
206 order by a.application_short_name, v.mapping_code;
207
208 cursor c_bne_mapping_lines (
209 v_mapping_code IN VARCHAR2 ) is
210 select v.sequence_num,
211 substr(bne_lct_tools_pkg.app_id_to_asn(v.interface_app_id),1,30) interface_asn,
212 v.interface_code,
213 v.interface_seq_num,
217 substr(bne_lct_tools_pkg.app_id_to_asn(v.content_app_id),1,30) content_asn,
214 substr(fnd_load_util.owner_name(v.last_updated_by),1,30) owner,
215 v.decode_flag,
216 v.object_version_number,
218 v.content_code,
219 v.content_seq_num,
220 to_char(v.last_update_date, 'yyyy/mm/dd') last_update_date
221 from bne_mapping_lines v, fnd_application a
222 where v.application_id = a.application_id
223 and v.mapping_code = v_mapping_code
224 and a.application_short_name = 'PER'
225 order by a.application_short_name, v.mapping_code, v.sequence_num;
226
227 cursor c_bne_layouts is
228 select a.application_short_name,
229 v.layout_code,
230 substr(fnd_load_util.owner_name(v.last_updated_by),1,30) owner,
231 v.object_version_number,
232 substr(bne_lct_tools_pkg.app_id_to_asn(v.stylesheet_app_id),1,30) stylesheet_asn,
233 v.stylesheet_code,
234 v.style,
235 v.style_class,
236 v.reporting_flag,
237 substr(bne_lct_tools_pkg.app_id_to_asn(v.reporting_interface_app_id),1,30) reporting_interface_asn,
238 v.reporting_interface_code,
239 v.user_name,
240 to_char(v.last_update_date, 'yyyy/mm/dd') last_update_date,
241 substr(bne_lct_tools_pkg.app_id_to_asn(v.create_doc_list_app_id),1,30) create_doc_list_asn,
242 v.create_doc_list_code
243 from bne_layouts_vl v, fnd_application a
244 where v.integrator_app_id = a.application_id
245 and v.integrator_code like p_integrator_code
246 and a.application_short_name like 'PER'
247 order by a.application_short_name, v.layout_code desc;
248
249 cursor c_bne_layout_blocks(
250 v_layout_code IN varchar2) is
251 select v.block_id,
252 substr(fnd_load_util.owner_name(v.last_updated_by),1,30) owner,
253 v.object_version_number,
254 v.parent_id,
255 v.layout_element,
256 v.style_class,
257 v.style,
258 v.row_style_class,
259 v.row_style,
260 v.col_style_class,
261 v.col_style,
262 v.prompt_displayed_flag,
263 v.prompt_style_class,
264 v.prompt_style,
265 v.hint_displayed_flag,
266 v.hint_style_class,
267 v.hint_style,
268 v.orientation,
269 v.layout_control,
270 v.display_flag,
271 v.blocksize,
272 v.minsize,
273 v.maxsize,
274 v.sequence_num,
275 v.prompt_colspan,
276 v.hint_colspan,
277 v.row_colspan,
278 v.summary_style_class,
279 v.summary_style,
280 v.user_name,
281 to_char(v.last_update_date, 'yyyy/mm/dd') last_update_date,
282 v.title_style_class,
283 v.title_style,
284 v.prompt_above
285 from bne_layout_blocks_vl v, fnd_application a
286 where v.application_id = a.application_id
287 and v.layout_code = v_layout_code
288 and a.application_short_name = 'PER'
289 order by a.application_short_name, v.layout_code, v.block_id;
290
291 cursor c_bne_layout_cols (
292 v_layout_code IN varchar2,
293 v_block_id IN varchar2) is
294 select v.sequence_num,
295 substr(fnd_load_util.owner_name(v.last_updated_by),1,30) owner,
296 v.object_version_number,
297 substr(bne_lct_tools_pkg.app_id_to_asn(v.interface_app_id),1,30) interface_asn,
298 v.interface_code,
299 v.interface_seq_num,
300 v.style_class,
301 v.style,
302 v.prompt_style_class,
303 v.prompt_style,
304 v.hint_style_class,
305 v.hint_style,
306 v.default_value,
307 v.default_type,
308 v.display_width,
309 to_char(v.last_update_date, 'yyyy/mm/dd') last_update_date,
310 v.read_only_flag
311 from bne_layout_cols v, fnd_application a
312 where v.application_id = a.application_id
313 and v.layout_code = v_layout_code
314 and a.application_short_name = 'PER'
315 and v.block_id = v_block_id
316 order by a.application_short_name, v.layout_code, v.block_id, v.sequence_num;
317
318 cursor c_bne_secured_objects is
319 select a.application_short_name,
320 v.object_code,
321 v.object_type,
322 v.object_version_number,
323 substr(bne_lct_tools_pkg.app_id_to_asn(v.security_rule_app_id),1,30) rule_security_rule_asn,
324 v.security_rule_code rule_security_rule_code,
325 substr(fnd_load_util.owner_name(v.last_updated_by),1,30) owner,
326 to_char(v.last_update_date, 'yyyy/mm/dd') last_update_date,
327 v.security_rule_app_id
328 from bne_secured_objects v, fnd_application a
329 where v.application_id = a.application_id
330 and v.object_code = p_integrator_code
331 and v.object_type = 'INTEGRATOR'
332 and a.application_short_name = 'PER'
333 order by a.application_short_name, v.object_code, v.object_type;
334
335 cursor c_bne_security_rules(
336 v_security_rule_code IN VARCHAR2) is
337 select a.application_short_name,
338 v.security_code,
339 v.object_version_number,
340 v.security_type,
341 v.security_value,
342 substr(fnd_load_util.owner_name(v.last_updated_by),1,30) owner,
343 to_char(v.last_update_date, 'yyyy/mm/dd') last_update_date
344 from bne_security_rules v, fnd_application a
345 where v.application_id = a.application_id
349
346 and v.security_code = v_security_rule_code
347 and a.application_short_name = 'PER'
348 order by a.application_short_name, v.security_code;
350
351 BEGIN
352
353 --g_proc := 'Copy_Integrator';
354 --g_actn := 'Copy_Integrator...';
355 --dbms_output.put_line('Copy_Integrator');
356 SAVEPOINT copy_integrator;
357
358 if (p_group_pl_id is null or p_integrator_code is null) then
359 return;
360 end if;
361 open c_plan_name;
362 fetch c_plan_name into l_plan_name;
363 close c_plan_name;
364
365 l_step := 'Integrator Creation';
366 FOR l_bne_integrator in c_bne_integrators
367 LOOP
368 fnd_file.put_line(Fnd_file.LOG,'1.Creating the integrator...');
369 l_created := 'Y';
370 bne_integrators_pkg.load_row(
371 x_integrator_asn => l_bne_integrator.application_short_name,
372 x_integrator_code => l_new_integrator_code ,
373 x_object_version_number => 1 ,
374 x_enabled_flag => l_bne_integrator.enabled_flag ,
375 x_upload_param_list_asn => l_bne_integrator.upl_param_list_asn ,
376 x_upload_param_list_code => l_bne_integrator.upl_param_list_code ,
377 x_upload_serv_param_list_asn => l_bne_integrator.upl_serv_param_list_asn ,
378 x_upload_serv_param_list_code => l_bne_integrator.upl_serv_param_list_code ,
379 x_import_param_list_asn => l_bne_integrator.import_param_list_asn ,
380 x_import_param_code => l_bne_integrator.import_param_list_code ,
381 x_date_format => l_bne_integrator.date_format ,
382 x_import_type => l_bne_integrator.import_type ,
383 x_uploader_class => l_bne_integrator.uploader_class ,
384 x_user_name => l_plan_name || l_bne_integrator.user_name,
385 x_upload_header => l_bne_integrator.upload_header ,
386 x_upload_title_bar => l_bne_integrator.upload_title_bar ,
387 x_owner => l_bne_integrator.owner,
388 x_last_update_date => l_bne_integrator.last_update_date,
389 x_custom_mode => null ,
390 x_create_doc_list_asn => l_bne_integrator.create_doc_param_list_asn ,
391 x_create_doc_list_code => l_bne_integrator.create_doc_param_list_code ,
392 x_new_session_flag => l_bne_integrator.new_session_flag ,
393 x_layout_resolver_class => l_bne_integrator.layout_resolver_class ,
394 x_layout_verifier_class => l_bne_integrator.layout_verifier_class ,
395 x_session_config_class => l_bne_integrator.session_config_class ,
396 x_session_param_list_asn => l_bne_integrator.session_param_list_asn ,
397 x_session_param_list_code => l_bne_integrator.session_param_list_code ,
398 x_display_flag => l_bne_integrator.display_flag
399 );
400 fnd_file.put_line(Fnd_file.LOG,'2.Creating the interface...');
401 l_step := 'Interface Creation';
402 FOR l_bne_interface in c_bne_interfaces
403 LOOP
404 bne_interfaces_pkg.load_row(
405 x_interface_asn => l_bne_interface.application_short_name ,
406 x_interface_code => l_new_interface_code ,
407 x_object_version_number => 1 ,
408 x_integrator_asn => 'PER' ,
409 x_integrator_code => l_new_integrator_code ,
410 x_interface_name => l_bne_interface.interface_name ,
411 x_upload_type => l_bne_interface.upload_type ,
412 x_upload_obj_name => l_bne_interface.upload_obj_name ,
413 x_upload_param_list_asn => l_bne_interface.upload_param_list_asn ,
414 x_upload_param_list_code => l_bne_interface.upload_param_list_code ,
415 x_upload_order => l_bne_interface.upload_order ,
416 x_user_name => l_bne_interface.user_name ,
417 x_owner => l_bne_interface.owner ,
418 x_last_update_date => l_bne_interface.last_update_date ,
419 x_custom_mode => null
420 );
421 --fnd_file.put_line(Fnd_file.LOG,'2.2.Creating the interface columns...');
422 l_step := 'Interface Columns Creation';
423 FOR l_bne_interface_col in c_bne_interface_cols(l_bne_interface.interface_code)
424 LOOP
425 bne_interface_cols_pkg.load_row(
426 x_interface_asn => 'PER' ,
427 x_interface_code => l_new_interface_code ,
428 x_sequence_num => l_bne_interface_col.sequence_num ,
429 x_interface_col_type => l_bne_interface_col.interface_col_type ,
430 x_interface_col_name => l_bne_interface_col.interface_col_name ,
431 x_enabled_flag => l_bne_interface_col.enabled_flag ,
432 x_required_flag => l_bne_interface_col.required_flag ,
433 x_display_flag => l_bne_interface_col.display_flag ,
434 x_read_only_flag => l_bne_interface_col.read_only_flag ,
435 x_not_null_flag => l_bne_interface_col.not_null_flag ,
436 x_summary_flag => l_bne_interface_col.summary_flag ,
437 x_mapping_enabled_flag => l_bne_interface_col.mapping_enabled_flag ,
438 x_data_type => l_bne_interface_col.data_type ,
439 x_field_size => l_bne_interface_col.field_size ,
440 x_default_type => l_bne_interface_col.default_type ,
441 x_default_value => l_bne_interface_col.default_value ,
445 x_oa_concat_flex => l_bne_interface_col.oa_concat_flex ,
442 x_segment_number => l_bne_interface_col.segment_number ,
443 x_group_name => l_bne_interface_col.group_name ,
444 x_oa_flex_code => l_bne_interface_col.oa_flex_code ,
446 x_val_type => l_bne_interface_col.val_type ,
447 x_val_id_col => l_bne_interface_col.val_id_col ,
448 x_val_mean_col => l_bne_interface_col.val_mean_col ,
449 x_val_desc_col => l_bne_interface_col.val_desc_col ,
450 x_val_obj_name => l_bne_interface_col.val_obj_name ,
451 x_val_addl_w_c => l_bne_interface_col.val_addl_w_c ,
452 x_val_component_asn => l_bne_interface_col.val_component_asn ,
453 x_val_component_code => l_bne_interface_col.val_component_code ,
454 x_oa_flex_num => l_bne_interface_col.oa_flex_num ,
455 x_oa_flex_application_id => l_bne_interface_col.oa_flex_application_id ,
456 x_display_order => l_bne_interface_col.display_order ,
457 x_upload_param_list_item_num => l_bne_interface_col.upload_param_list_item_num ,
458 x_expanded_sql_query => l_bne_interface_col.expanded_sql_query ,
459 x_object_version_number => l_bne_interface_col.object_version_number ,
460 x_user_hint => l_bne_interface_col.user_hint ,
461 x_prompt_left => l_bne_interface_col.prompt_left ,
462 x_user_help_text => l_bne_interface_col.user_help_text ,
463 x_prompt_above => l_bne_interface_col.prompt_above ,
464 x_owner => l_bne_interface_col.owner ,
465 x_last_update_date => l_bne_interface_col.last_update_date ,
466 x_lov_type => l_bne_interface_col.lov_type ,
467 x_offline_lov_enabled_flag => l_bne_interface_col.offline_lov_enabled_flag ,
468 x_custom_mode => null ,
469 x_variable_data_type_class => l_bne_interface_col.variable_data_type_class ,
470 x_viewer_group => l_bne_interface_col.viewer_group ,
471 x_edit_type => l_bne_interface_col.edit_type ,
472 x_display_width => l_bne_interface_col.display_width ,
473 x_val_query_asn => l_bne_interface_col.val_query_asn ,
474 x_val_query_code => l_bne_interface_col.val_query_code ,
475 x_expanded_sql_query_asn => l_bne_interface_col.expanded_sql_query_asn ,
476 x_expanded_sql_query_code => l_bne_interface_col.expanded_sql_query_code
477 );
478
479 END LOOP; -- interface columns
480
481 END LOOP; -- interface
482
483 fnd_file.put_line(Fnd_file.LOG,'3.Creating the contents...');
484 l_step := 'Contents Creation';
485 FOR l_bne_content in c_bne_contents
486 LOOP
487 bne_contents_pkg.load_row(
488 x_content_asn => l_bne_content.application_short_name ,
489 x_content_code => l_new_content_code ,
490 x_object_version_number => 1 ,
491 x_integrator_asn => 'PER' ,
492 x_integrator_code => l_new_integrator_code ,
493 x_param_list_asn => l_bne_content.param_list_asn ,
494 x_param_list_code => l_bne_content.param_list_code ,
495 x_content_class => l_bne_content.content_class ,
496 x_user_name => l_bne_content.user_name ,
497 x_owner => l_bne_content.owner ,
498 x_last_update_date => l_bne_content.last_update_date ,
499 x_custom_mode => null ,
500 x_once_only_download_flag => l_bne_content.once_only_download_flag
501 );
502
503 -- fnd_file.put_line(Fnd_file.LOG,'3.2.Creating the contents columns...');
504 l_step := 'Contents Columns Creation';
505 FOR l_bne_content_col in c_bne_content_cols (l_bne_content.content_code)
506 LOOP
507 bne_content_cols_pkg.load_row(
508 x_content_asn => 'PER' ,
509 x_content_code => l_new_content_code ,
510 x_sequence_num => l_bne_content_col.sequence_num ,
511 x_object_version_number=> 1 ,
512 x_col_name => l_bne_content_col.col_name ,
513 x_user_name => l_bne_content_col.user_name ,
514 x_owner => l_bne_content_col.owner ,
515 x_last_update_date => l_bne_content_col.last_update_date ,
516 x_custom_mode => null ,
517 x_read_only_flag => l_bne_content_col.read_only_flag
518 );
519
520 END LOOP; -- contents columns
521
522 -- fnd_file.put_line(Fnd_file.LOG,'3.3.Creating the bne_stored_sql...');
523 l_step := 'Stored Sql Creation';
524 FOR l_bne_stored_sql in c_bne_stored_sql (l_bne_content.content_code)
525 LOOP
526 bne_stored_sql_pkg.load_row(
527 x_content_asn => 'PER' ,
531 x_owner => l_bne_stored_sql.owner ,
528 x_content_code => l_new_content_code ,
529 x_object_version_number=> 1 ,
530 x_query => l_bne_stored_sql.query ,
532 x_last_update_date => l_bne_stored_sql.last_update_date ,
533 x_custom_mode => null ,
534 x_query_app_asn => l_bne_stored_sql.bne_query_query_asn ,
535 x_query_code => l_bne_stored_sql.query_code
536 );
537
538 END LOOP; -- bne_stored_sql
539
540 END LOOP; -- contents
541
542 fnd_file.put_line(Fnd_file.LOG,'4.Creating the mapping...');
543 l_step := 'Mapping Creation';
544 FOR l_bne_mapping in c_bne_mappings
545 LOOP
546 bne_mappings_pkg.load_row(
547 x_mapping_asn => l_bne_mapping.application_short_name ,
548 x_mapping_code => l_new_mapping_code ,
549 x_integrator_asn => 'PER' ,
550 x_integrator_code => l_new_integrator_code ,
551 x_reporting_flag => l_bne_mapping.reporting_flag ,
552 x_reporting_interface_asn => l_bne_mapping.reporting_interface_asn ,
553 x_reporting_interface_code=> l_bne_mapping.reporting_interface_code ,
554 x_user_name => l_bne_mapping.user_name ,
555 x_object_version_number => 1 ,
556 x_owner => l_bne_mapping.owner ,
557 x_last_update_date => l_bne_mapping.last_update_date ,
558 x_custom_mode => null
559 );
560 -- fnd_file.put_line(Fnd_file.LOG,'4.2.Creating the mapping lines...');
561 l_step := 'Mapping Lines Creation';
562 FOR l_bne_mapping_line in c_bne_mapping_lines (l_bne_mapping.mapping_code)
563 LOOP
564 bne_mapping_lines_pkg.load_row(
565 x_mapping_asn => 'PER' ,
566 x_mapping_code => l_new_mapping_code ,
567 x_interface_asn => l_bne_mapping_line.interface_asn ,
568 x_interface_code => l_new_interface_code ,
569 x_interface_seq_num => l_bne_mapping_line.interface_seq_num ,
570 x_decode_flag => l_bne_mapping_line.decode_flag ,
571 x_object_version_number=> 1 ,
572 x_sequence_num => l_bne_mapping_line.sequence_num ,
573 x_content_asn => l_bne_mapping_line.content_asn ,
574 x_content_code => l_new_content_code ,
575 x_content_seq_num => l_bne_mapping_line.content_seq_num ,
576 x_owner => l_bne_mapping_line.owner ,
577 x_last_update_date => l_bne_mapping_line.last_update_date ,
578 x_custom_mode => null
579 );
580
581 END LOOP; -- mapping lines.
582
583 END LOOP; --mapping
584
585 fnd_file.put_line(Fnd_file.LOG,'5.Creating the layouts...');
586 l_step := 'Layout Creation';
587 l_layout_count := 1;
588 FOR l_bne_layout in c_bne_layouts
589 LOOP
590 l_new_layout_code := 'BEN_CWB_WS_LYT' || l_layout_count || l_suffix;
591 l_layout_count := l_layout_count + 1;
592 bne_layouts_pkg.load_row(
593 x_layout_asn => l_bne_layout.application_short_name ,
594 x_layout_code => l_new_layout_code ,
595 x_object_version_number => 1 ,
596 x_stylesheet_asn => l_bne_layout.stylesheet_asn ,
597 x_stylesheet_code => l_bne_layout.stylesheet_code ,
598 x_integrator_asn => 'PER' ,
599 x_integrator_code => l_new_integrator_code ,
600 x_style => l_bne_layout.style ,
601 x_style_class => l_bne_layout.style_class ,
602 x_reporting_flag => l_bne_layout.reporting_flag ,
603 x_reporting_interface_asn => l_bne_layout.reporting_interface_asn ,
604 x_report_interface_code => l_bne_layout.reporting_interface_code ,
605 x_user_name => l_bne_layout.user_name ,
606 x_owner => l_bne_layout.owner ,
607 x_last_update_date => l_bne_layout.last_update_date ,
608 x_custom_mode => null ,
609 x_create_doc_list_asn => l_bne_layout.create_doc_list_asn ,
610 x_create_doc_list_code => l_bne_layout.create_doc_list_code
611 );
612 l_step := 'Layout Block Creation';
613 FOR l_bne_layout_block in c_bne_layout_blocks(l_bne_layout.layout_code)
614 LOOP
615 bne_layout_blocks_pkg.load_row(
616 x_layout_asn => l_bne_layout.application_short_name ,
617 x_layout_code => l_new_layout_code ,
618 x_block_id => l_bne_layout_block.block_id ,
619 x_object_version_number => 1 ,
620 x_parent_id => l_bne_layout_block.parent_id ,
621 x_layout_element => l_bne_layout_block.layout_element ,
622 x_style_class => l_bne_layout_block.style_class ,
626 x_col_style_class => l_bne_layout_block.col_style_class ,
623 x_style => l_bne_layout_block.style ,
624 x_row_style_class => l_bne_layout_block.row_style_class ,
625 x_row_style => l_bne_layout_block.row_style ,
627 x_col_style => l_bne_layout_block.col_style ,
628 x_prompt_displayed_flag => l_bne_layout_block.prompt_displayed_flag ,
629 x_prompt_style_class => l_bne_layout_block.prompt_style_class ,
630 x_prompt_style => l_bne_layout_block.prompt_style ,
631 x_hint_displayed_flag => l_bne_layout_block.hint_displayed_flag ,
632 x_hint_style_class => l_bne_layout_block.hint_style_class ,
633 x_hint_style => l_bne_layout_block.hint_style ,
634 x_orientation => l_bne_layout_block.orientation ,
635 x_layout_control => l_bne_layout_block.layout_control ,
636 x_display_flag => l_bne_layout_block.display_flag ,
637 x_blocksize => l_bne_layout_block.blocksize ,
638 x_minsize => l_bne_layout_block.minsize ,
639 x_maxsize => l_bne_layout_block.maxsize ,
640 x_sequence_num => l_bne_layout_block.sequence_num ,
641 x_prompt_colspan => l_bne_layout_block.prompt_colspan ,
642 x_hint_colspan => l_bne_layout_block.hint_colspan ,
643 x_row_colspan => l_bne_layout_block.row_colspan ,
644 x_summary_style_class => l_bne_layout_block.summary_style_class ,
645 x_summary_style => l_bne_layout_block.summary_style ,
646 x_title_style_class => l_bne_layout_block.title_style_class ,
647 x_title_style => l_bne_layout_block.title_style ,
648 x_user_name => l_bne_layout_block.user_name ,
649 x_prompt_above => l_bne_layout_block.prompt_above ,
650 x_owner => l_bne_layout_block.owner ,
651 x_last_update_date => l_bne_layout_block.last_update_date ,
652 x_custom_mode => null
653 );
654 l_step := 'Layout Columns Creation';
655 FOR l_bne_layout_col in c_bne_layout_cols(l_bne_layout.layout_code,l_bne_layout_block.block_id)
656 LOOP
657 BNE_LAYOUT_COLS_PKG.LOAD_ROW(
658 x_layout_asn => l_bne_layout.application_short_name ,
659 x_layout_code => l_new_layout_code ,
660 x_block_id => l_bne_layout_block.block_id ,
661 x_sequence_num => l_bne_layout_col.sequence_num ,
662 x_object_version_number => 1 ,
663 x_interface_asn => l_bne_layout_col.interface_asn ,
664 x_interface_code => l_new_interface_code ,
665 x_interface_seq_num => l_bne_layout_col.interface_seq_num ,
666 x_style_class => l_bne_layout_col.style_class ,
667 x_hint_style => l_bne_layout_col.hint_style ,
668 x_hint_style_class => l_bne_layout_col.hint_style_class ,
669 x_prompt_style => l_bne_layout_col.prompt_style ,
670 x_prompt_style_class => l_bne_layout_col.prompt_style_class ,
671 x_default_type => l_bne_layout_col.default_type ,
672 x_default_value => l_bne_layout_col.default_value ,
673 x_display_width => l_bne_layout_col.display_width ,
674 x_style => l_bne_layout_col.style ,
675 x_read_only_flag => l_bne_layout_col.read_only_flag ,
676 x_owner => l_bne_layout_col.owner ,
677 x_last_update_date => l_bne_layout_col.last_update_date ,
678 x_custom_mode => null
679 );
680
681 END LOOP; -- layout cols
682
683 END LOOP; -- layout block
684
685 END LOOP; -- layouts
686
687 fnd_file.put_line(Fnd_file.LOG,'6.Creating the secured objects...');
688 l_step := 'Secured objects Creation';
689 FOR l_bne_secured_object in c_bne_secured_objects
690 LOOP
691 bne_secured_objects_pkg.load_row(
692 x_secured_object_asn => l_bne_secured_object.application_short_name ,
693 x_secured_object_code => l_new_integrator_code ,
694 x_secured_object_type => l_bne_secured_object.object_type ,
695 x_object_version_number=> 1 ,
696 x_security_rule_app_id => l_bne_secured_object.rule_security_rule_asn ,
697 x_security_rule_code => l_new_integrator_code ,
698 x_owner => l_bne_secured_object.owner ,
699 x_last_update_date => l_bne_secured_object.last_update_date ,
700 x_custom_mode => null
701 );
702 l_step := 'Security rule Creation';
703 FOR l_bne_security_rule in c_bne_security_rules (l_bne_secured_object.rule_security_rule_code)
704 LOOP
705 bne_security_rules_pkg.load_row(
706 x_security_rule_asn => l_bne_security_rule.application_short_name ,
707 x_security_rule_code =>
711 x_security_value => l_bne_security_rule.security_value ,
708 l_new_integrator_code,
709 x_object_version_number=> 1 ,
710 x_security_type => l_bne_security_rule.security_type ,
712 x_owner => l_bne_security_rule.owner ,
713 x_last_update_date => l_bne_security_rule.last_update_date ,
714 x_custom_mode => null
715 );
716 END LOOP; -- security_rule
717
718 END LOOP; -- secured objects.
719
720 END LOOP; -- integrator
721
722 update ben_cwb_pl_dsgn set custom_integrator = l_new_integrator_code
723 where pl_id = p_group_pl_id and pl_id = group_pl_id
724 and oipl_id = -1 and group_oipl_id = -1;
725
726 --COMMIT;
727 IF(l_created = 'Y') THEN
728 fnd_file.put_line(Fnd_file.LOG,'Successfully created the Integrator Setup - ' || l_new_integrator_code);
729 END IF;
730
731 EXCEPTION
732 WHEN OTHERS
733 THEN
734 fnd_file.put_line(Fnd_file.LOG,'Error at step : '||l_step);
735 fnd_file.put_line(Fnd_file.LOG,'Error in copy_integrator : '||SQLERRM);
736 ROLLBACK TO copy_integrator;
737 END;
738 --
739 -------------------------- copy_integrator ----------------------
740 --
741
742
743
744 END ben_cwb_integrator_copy;
745