[Home] [Help]
PACKAGE BODY: APPS.FEM_WEBADI_TABLES_UTILS_PVT
Source
1 PACKAGE BODY FEM_WEBADI_TABLES_UTILS_PVT AS
2 /* $Header: FEMVADITABUTILB.pls 120.2.12010000.2 2008/09/22 11:01:22 lkiran ship $ */
3 --Bug#7423745. Added procs that make entries into TL tables
4 --for Integrator, Content and Content Cols.
5
6 procedure ADD_INTEGRATOR_LANGUAGE
7 is
8 begin
9 delete from BNE_INTEGRATORS_TL T
10 where not exists
11 (select NULL
12 from BNE_INTEGRATORS_B B
13 where B.APPLICATION_ID = T.APPLICATION_ID
14 and B.INTEGRATOR_CODE = T.INTEGRATOR_CODE
15 ) and t.application_id=274 and t.integrator_code not in ('FEM_BALANCES_INTG','FEM_DIM_MEMBER_INTG','FEM_HIERARCHY_INTG');
16
17 update BNE_INTEGRATORS_TL T set (
18 USER_NAME,
19 UPLOAD_TITLE_BAR,
20 UPLOAD_HEADER
21 ) = (select
22 B.USER_NAME,
23 B.UPLOAD_TITLE_BAR,
24 B.UPLOAD_HEADER
25 from BNE_INTEGRATORS_TL B
26 where B.APPLICATION_ID = T.APPLICATION_ID
27 and B.INTEGRATOR_CODE = T.INTEGRATOR_CODE
28 and B.LANGUAGE = T.SOURCE_LANG)
29 where (
30 T.APPLICATION_ID,
31 T.INTEGRATOR_CODE,
32 T.LANGUAGE
33 ) in (select
34 SUBT.APPLICATION_ID,
35 SUBT.INTEGRATOR_CODE,
36 SUBT.LANGUAGE
37 from BNE_INTEGRATORS_TL SUBB, BNE_INTEGRATORS_TL SUBT
38 where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
39 and SUBB.INTEGRATOR_CODE = SUBT.INTEGRATOR_CODE
40 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
41 and (SUBB.USER_NAME <> SUBT.USER_NAME
42 or SUBB.UPLOAD_TITLE_BAR <> SUBT.UPLOAD_TITLE_BAR
43 or (SUBB.UPLOAD_TITLE_BAR is null and SUBT.UPLOAD_TITLE_BAR is not null)
44 or (SUBB.UPLOAD_TITLE_BAR is not null and SUBT.UPLOAD_TITLE_BAR is null)
45 or SUBB.UPLOAD_HEADER <> SUBT.UPLOAD_HEADER
46 or (SUBB.UPLOAD_HEADER is null and SUBT.UPLOAD_HEADER is not null)
47 or (SUBB.UPLOAD_HEADER is not null and SUBT.UPLOAD_HEADER is null)
48 )) and t.application_id = 274 and t.integrator_code not in ('FEM_BALANCES_INTG','FEM_DIM_MEMBER_INTG','FEM_HIERARCHY_INTG') ;
49
50 insert into BNE_INTEGRATORS_TL (
51 APPLICATION_ID,
52 INTEGRATOR_CODE,
53 USER_NAME,
54 UPLOAD_HEADER,
55 UPLOAD_TITLE_BAR,
56 CREATED_BY,
57 CREATION_DATE,
58 LAST_UPDATED_BY,
59 LAST_UPDATE_LOGIN,
60 LAST_UPDATE_DATE,
61 LANGUAGE,
62 SOURCE_LANG
63 ) select
64 B.APPLICATION_ID,
65 B.INTEGRATOR_CODE,
66 B.USER_NAME,
67 B.UPLOAD_HEADER,
68 B.UPLOAD_TITLE_BAR,
69 B.CREATED_BY,
70 B.CREATION_DATE,
71 B.LAST_UPDATED_BY,
72 B.LAST_UPDATE_LOGIN,
73 B.LAST_UPDATE_DATE,
74 L.LANGUAGE_CODE,
75 B.SOURCE_LANG
76 from BNE_INTEGRATORS_TL B, FND_LANGUAGES L
77 where L.INSTALLED_FLAG in ('I', 'B')
78 and B.LANGUAGE = userenv('LANG')
79 and not exists
80 (select NULL
81 from BNE_INTEGRATORS_TL T
82 where T.APPLICATION_ID = B.APPLICATION_ID
83 and T.INTEGRATOR_CODE = B.INTEGRATOR_CODE
84 and T.LANGUAGE = L.LANGUAGE_CODE) and b.application_id=274 and b.integrator_code not in ('FEM_BALANCES_INTG','FEM_DIM_MEMBER_INTG','FEM_HIERARCHY_INTG');
85 end ADD_INTEGRATOR_LANGUAGE;
86
87
88 procedure ADD_CONTENT_LANGUAGE
89 is
90 begin
91
92 delete from BNE_CONTENTS_TL T
93 where not exists
94 (select NULL
95 from BNE_CONTENTS_B B
96 where B.APPLICATION_ID = T.APPLICATION_ID
97 and B.CONTENT_CODE = T.CONTENT_CODE
98 ) and t.application_id=274 and t.content_code not in ('FEM_BALANCES_CNT','FEM_DIM_MEMBER_CNT','FEM_HIERARCHY_CNT');
99
100 update BNE_CONTENTS_TL T set (
101 USER_NAME
102 ) = (select
103 B.USER_NAME
104 from BNE_CONTENTS_TL B
105 where B.APPLICATION_ID = T.APPLICATION_ID
106 and B.CONTENT_CODE = T.CONTENT_CODE
107 and B.LANGUAGE = T.SOURCE_LANG)
108 where (
109 T.APPLICATION_ID,
110 T.CONTENT_CODE,
111 T.LANGUAGE
112 ) in (select
113 SUBT.APPLICATION_ID,
114 SUBT.CONTENT_CODE,
115 SUBT.LANGUAGE
116 from BNE_CONTENTS_TL SUBB, BNE_CONTENTS_TL SUBT
117 where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
118 and SUBB.CONTENT_CODE = SUBT.CONTENT_CODE
119 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
120 and (SUBB.USER_NAME <> SUBT.USER_NAME))
121 and t.application_id=274 and t.content_code not in ('FEM_BALANCES_CNT','FEM_DIM_MEMBER_CNT','FEM_HIERARCHY_CNT');
122
123
124 insert into BNE_CONTENTS_TL (
125 CONTENT_CODE,
126 USER_NAME,
127 LAST_UPDATE_DATE,
128 LAST_UPDATED_BY,
129 CREATION_DATE,
130 CREATED_BY,
131 LAST_UPDATE_LOGIN,
132 APPLICATION_ID,
133 LANGUAGE,
134 SOURCE_LANG
135 ) select
136 B.CONTENT_CODE,
137 B.USER_NAME,
138 B.LAST_UPDATE_DATE,
139 B.LAST_UPDATED_BY,
140 B.CREATION_DATE,
141 B.CREATED_BY,
142 B.LAST_UPDATE_LOGIN,
143 B.APPLICATION_ID,
144 L.LANGUAGE_CODE,
145 B.SOURCE_LANG
146 from BNE_CONTENTS_TL B, FND_LANGUAGES L
147 where L.INSTALLED_FLAG in ('I', 'B')
148 and B.LANGUAGE = userenv('LANG')
149 and not exists
150 (select NULL
151 from BNE_CONTENTS_TL T
152 where T.APPLICATION_ID = B.APPLICATION_ID
153 and T.CONTENT_CODE = B.CONTENT_CODE
154 and T.LANGUAGE = L.LANGUAGE_CODE) and b.application_id=274 and b.content_code not in ('FEM_BALANCES_CNT','FEM_DIM_MEMBER_CNT','FEM_HIERARCHY_CNT');
155
156 end ADD_CONTENT_LANGUAGE;
157
158 procedure ADD_CONTENT_COLS_LANGUAGE
159 is
160 begin
161 delete from BNE_CONTENT_COLS_TL T
162 where not exists
163 (select NULL
164 from BNE_CONTENT_COLS_B B
165 where B.APPLICATION_ID = T.APPLICATION_ID
166 and B.CONTENT_CODE = T.CONTENT_CODE
167 and B.SEQUENCE_NUM = T.SEQUENCE_NUM
168 ) and t.application_id=274 and t.content_code not in ('FEM_BALANCES_CNT','FEM_DIM_MEMBER_CNT','FEM_HIERARCHY_CNT');
169
170 update BNE_CONTENT_COLS_TL T set (
171 USER_NAME
172 ) = (select
173 B.USER_NAME
174 from BNE_CONTENT_COLS_TL B
175 where B.APPLICATION_ID = T.APPLICATION_ID
176 and B.CONTENT_CODE = T.CONTENT_CODE
177 and B.SEQUENCE_NUM = T.SEQUENCE_NUM
178 and B.LANGUAGE = T.SOURCE_LANG)
179 where (
180 T.APPLICATION_ID,
181 T.CONTENT_CODE,
182 T.SEQUENCE_NUM,
183 T.LANGUAGE
184 ) in (select
185 SUBT.APPLICATION_ID,
186 SUBT.CONTENT_CODE,
187 SUBT.SEQUENCE_NUM,
188 SUBT.LANGUAGE
189 from BNE_CONTENT_COLS_TL SUBB, BNE_CONTENT_COLS_TL SUBT
190 where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
191 and SUBB.CONTENT_CODE = SUBT.CONTENT_CODE
192 and SUBB.SEQUENCE_NUM = SUBT.SEQUENCE_NUM
193 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
194 and (SUBB.USER_NAME <> SUBT.USER_NAME))
195 and t.application_id=274 and t.content_code not in ('FEM_BALANCES_CNT','FEM_DIM_MEMBER_CNT','FEM_HIERARCHY_CNT');
196
197 insert into BNE_CONTENT_COLS_TL (
198 APPLICATION_ID,
199 CONTENT_CODE,
200 SEQUENCE_NUM,
201 USER_NAME,
202 CREATED_BY,
203 CREATION_DATE,
204 LAST_UPDATED_BY,
205 LAST_UPDATE_LOGIN,
206 LAST_UPDATE_DATE,
207 LANGUAGE,
208 SOURCE_LANG
209 ) select
210 B.APPLICATION_ID,
211 B.CONTENT_CODE,
212 B.SEQUENCE_NUM,
213 B.USER_NAME,
214 B.CREATED_BY,
215 B.CREATION_DATE,
216 B.LAST_UPDATED_BY,
217 B.LAST_UPDATE_LOGIN,
218 B.LAST_UPDATE_DATE,
219 L.LANGUAGE_CODE,
220 B.SOURCE_LANG
221 from BNE_CONTENT_COLS_TL B, FND_LANGUAGES L
222 where L.INSTALLED_FLAG in ('I', 'B')
223 and B.LANGUAGE = userenv('LANG')
224 and not exists
225 (select NULL
226 from BNE_CONTENT_COLS_TL T
227 where T.APPLICATION_ID = B.APPLICATION_ID
228 and T.CONTENT_CODE = B.CONTENT_CODE
229 and T.SEQUENCE_NUM = B.SEQUENCE_NUM
230 and T.LANGUAGE = L.LANGUAGE_CODE) and b.application_id=274 and b.content_code not in ('FEM_BALANCES_CNT','FEM_DIM_MEMBER_CNT','FEM_HIERARCHY_CNT');
231 end ADD_CONTENT_COLS_LANGUAGE;
232
233 -- Bug#7423745. -- End
234
235 PROCEDURE POPULATE_METADATA_CP(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY NUMBER, P_TABLE_NAME IN VARCHAR2)
236 IS
237 l_object_code varchar2(50);
238 l_interface_table varchar2(50);
239 l_ret_status varchar2(30);
240 l_msg_count number;
241 l_msg_data varchar2(30);
242 BEGIN
243
244 POPULATE_METADATA(P_TABLE_NAME,'F',l_object_code,null,l_ret_status,l_msg_count,l_msg_data);
245 if(l_ret_status = FND_API.G_RET_STS_ERROR) then
246 retcode :=2;
247 end if;
248
249 END POPULATE_METADATA_CP;
250
251 PROCEDURE POPULATE_METADATA(P_TABLE_NAME IN VARCHAR2,P_MODE IN VARCHAR2, X_OBJECT_CODE OUT NOCOPY VARCHAR2,
252 P_INIT_MSG_LIST IN VARCHAR2,
253 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
254 X_MSG_COUNT OUT NOCOPY NUMBER,
255 X_MSG_DATA OUT NOCOPY VARCHAR2)
256 IS
257
258 l_integrator_exists varchar2(1) := null;
259 l_interface_exists varchar2(1) := null;
260 l_integrator_code varchar2(50) := P_TABLE_NAME || '_INTG';
261 l_interface_table varchar2(50) := null;
262 l_interface_code varchar2(50) := null;
263 l_param_list_code varchar2(50) := null;
264 l_order_seq number;
265 l_intf_upl_param_list_code varchar2(50) := 'FEM_LIST';
266 l_user_id number(15) := 2; -- (user name : initial setup)
267 l_intg_upl_param_list_code varchar2(50) := 'FEM_TABLES_UPL_LIST';
268 l_intg_imp_param_list_code varchar2(50) := 'FEM_TABLES_IMP_LIST';
269 l_table_display_name varchar2(150);
270 l_object_code varchar2(30);
271 l_object_code_prefix number := 1;
272 l_no_intf_col_map_flag varchar2(1) := null;
273 e_normal_mode exception;
274 e_no_intf_col_map exception;
275 l_no_map_columns varchar2(1000);
276 l_log_string varchar2(5000);
277 begin
278
279 select display_name into l_table_display_name from fem_tables_vl
280 where table_name = p_table_name;
281
282 l_object_code := p_table_name;
283
284 l_interface_code := p_table_name || '_INTF';
285
286 ------------------------ populating maping for corresponding interface table params
287 begin
288 select interface_table_name into l_interface_table from fem_tables_b
289 where table_name = p_table_name;
290 exception
291 when NO_DATA_FOUND then null;
292 end;
293
294 POPULATE_TABLE_COLUMN_MAPS(l_interface_table);
295 ----------------------------------------------------------------------------------------------------------------------------------------------------------
296
297 -- Right now we assume that there won't be any conflict while generating interface/integrator codes are tables having length(tableName) > 20.
298
299 if(length(l_object_code) >20) then
300 l_object_code := substr(l_object_code,1,19) || l_object_code_prefix;
301 l_integrator_code := l_object_code || '_INTG';
302 l_interface_code := l_object_code || '_INTF';
303 end if;
304
305 x_object_code := l_object_code;
306
307 ------------------- Checking if all interface table required columns have entry in FEM_TAB_COLUMNS_B ----------------------------------------------------
308
309 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
310 FND_MSG_PUB.initialize ;
311 END IF;
312
313 for missing_rec in (select M.interface_column_name interface_column_name
314 from FEM_WEBADI_TABLE_COLS_MAPS M
315 where M.INTERFACE_TABLE_NAME = l_interface_table
316 and M.NULLABLE = 'N'
317 and trim(interface_column_name) not in ('CAL_PERIOD_END_DATE','CALP_DIM_GRP_DISPLAY_CODE','CAL_PERIOD_NUMBER','STATUS','LEDGER_DISPLAY_CODE','SOURCE_SYSTEM_DISPLAY_CODE',
318 'DATASET_DISPLAY_CODE')
319 and not exists (SELECT 1 FROM FEM_TAB_COLUMNS_B T WHERE TABLE_NAME = p_table_name AND T.INTERFACE_COLUMN_NAME = M.INTERFACE_COLUMN_NAME))
320 loop
321 l_no_map_columns := missing_rec.interface_column_name || ' ,' || l_no_map_columns;
322 l_no_intf_col_map_flag := 'Y';
323 end loop;
324
325 l_no_map_columns := substr(l_no_map_columns,1,length(l_no_map_columns)-2);
326
327 if (l_no_intf_col_map_flag = 'Y') then
328 if(p_mode <> 'N')then
329 FND_MESSAGE.SET_NAME('FEM','FEM_ADI_MISSING_MAPPING');
330 FND_MESSAGE.SET_TOKEN('COLUMNS',l_no_map_columns);
331 l_log_string := FND_MESSAGE.GET;
332 fnd_file.put_line(fnd_file.log,l_log_string);
333 else
334 FND_MESSAGE.SET_NAME('FEM','FEM_ADI_MISSING_MAPPING');
335 FND_MESSAGE.SET_TOKEN('COLUMNS',l_no_map_columns);
336 FND_MSG_PUB.add;
337 end if;
338 raise e_no_intf_col_map;
339 end if;
340
341 ---------------------- Checking if an integrator is already available for normal mode
342 begin
343 select 'Y' into l_integrator_exists from dual
344 where exists(select integrator_code from bne_integrators_b where
345 integrator_code = l_integrator_code);
346 exception
347 when NO_DATA_FOUND then null;
348 end;
349
350 if(l_integrator_exists is not null and p_mode = 'N') then --- p_mode = 'N' says that its a normal mode and proc got executed from create from spreadsheet.
351 raise e_normal_mode;
352 end if;
353
354 ----------------------------------------------------------------------------------------------------------------------------------------------------------
355 if(l_integrator_exists is null) then
356
357 ------ The BNE integrator utility function takes object code of only 20 max charecters. As our object code is nothing but the table name so if in case some
358 ------ table name has greater than 20 chars so we need to resolve our object_name.
359
360 ----------------------------------------------------------------------------------------------------------------------------------------------------------
361
362 bne_integrator_utils.create_integrator(p_application_id => 274,
363 p_object_code => l_object_code,
364 p_integrator_user_name => 'Enterprise Performance Foundation: ' || l_table_display_name || ' Integrator' ,
365 p_language => USERENV('LANG'),
366 p_source_language => USERENV('LANG'),
367 p_user_id => 2,
368 p_integrator_code => l_integrator_code
369 );
370 -- Bug#7423745
371 add_integrator_language;
372
373 update bne_integrators_b
374 set upload_param_list_app_id = 274,
375 upload_param_list_code = 'FEM_TABLES_UPL_LIST',
376 upload_serv_param_list_app_id = 231,
377 upload_serv_param_list_code = 'UPL_SERV_INTERF_COLS',
378 import_param_list_app_id = 274,
379 import_param_list_code = 'FEM_TABLES_IMP_LIST',
380 uploader_class = 'oracle.apps.bne.integrator.upload.BneUploader',
381 import_type = 1
382 where integrator_code = l_integrator_code;
383
384 end if;
385
386 -------------------- creating an interface if its not available for the P_TABLE_NAME
387
388 begin
389 select 'Y' into l_interface_exists from dual
390 where exists(select interface_code from bne_interfaces_b where
391 interface_code = l_object_code || '_INTF');
392 exception
393 when NO_DATA_FOUND then null;
394 end;
395
396 if(l_interface_exists is null) then
397
398 SELECT NVL(MAX(UPLOAD_ORDER), 0) + 1
399 INTO l_order_seq
400 FROM BNE_INTERFACES_B
401 WHERE APPLICATION_ID = 274
402 AND INTEGRATOR_APP_ID = 274
403 AND INTEGRATOR_CODE = P_TABLE_NAME || '_INTG';
404
405 IF SQL%NOTFOUND THEN
406 RAISE NO_DATA_FOUND;
407 END IF;
408
409 ---- Create the interface in the BNE_INTERFACE_B table
410
411 INSERT INTO BNE_INTERFACES_B
412 (APPLICATION_ID,
413 INTERFACE_CODE,
414 OBJECT_VERSION_NUMBER,
415 INTEGRATOR_APP_ID,
416 INTEGRATOR_CODE,
417 INTERFACE_NAME,
418 UPLOAD_TYPE,
419 UPLOAD_PARAM_LIST_APP_ID,
420 UPLOAD_PARAM_LIST_CODE,
421 UPLOAD_ORDER,
422 CREATED_BY,
423 CREATION_DATE,
424 LAST_UPDATED_BY,
425 LAST_UPDATE_DATE)
426 VALUES
427 (274,
428 l_interface_code,
429 1,
430 274,
431 l_integrator_code,
432 'UPLOAD_FEM_TABLES_INTERFACE',
433 2,
434 274,
435 l_intf_upl_param_list_code,
436 NULL,
437 l_user_id,
438 SYSDATE,
439 l_user_id,
440 SYSDATE);
441
442 ----- Create the interface in the BNE_INTERFACES_TL table
443
444 INSERT INTO BNE_INTERFACES_TL
445 ( APPLICATION_ID,
446 INTERFACE_CODE,
447 LANGUAGE,
448 SOURCE_LANG,
449 USER_NAME,
450 CREATED_BY,
451 CREATION_DATE,
452 LAST_UPDATED_BY,
453 LAST_UPDATE_DATE)
454 SELECT 274
455 , l_interface_code
456 , T.LANGUAGE
457 , T.SOURCE_LANG
458 , SUBSTR(M.MESSAGE_TEXT,0, INSTR(M.MESSAGE_TEXT, 'TABLE_NAME')-2) ||
459 T.DISPLAY_NAME ||
460 SUBSTR(M.MESSAGE_TEXT,INSTR(M.MESSAGE_TEXT, 'TABLE_NAME')+10)
461 , l_user_id
462 , SYSDATE
463 , l_user_id
464 , SYSDATE
465 FROM FEM_TABLES_TL T, FND_NEW_MESSAGES M, FND_LANGUAGES L
466 WHERE T.TABLE_NAME = p_table_name
467 AND M.APPLICATION_ID= 274
468 AND M.MESSAGE_NAME = 'FEM_ADI_TABLES_INTERFACE'
469 AND M.LANGUAGE_CODE = T.LANGUAGE
470 AND M.LANGUAGE_CODE = L.LANGUAGE_CODE
471 AND L.INSTALLED_FLAG IN ('I', 'B');
472
473 end if;
474
475 -------------------- populate/create security,content and mapping when new interface is created
476 if(l_interface_exists is null) then
477
478 POPULATE_OTHER_SETUP(l_object_code,p_table_name);
479
480 end if;
481
482 -------------------- populating bne_interface_cols tables for common columns
483
484 -- if(l_interface_exists is null ) then
485
486 POPULATE_INTERFACE_COMM_COLS(l_interface_code,p_table_name);
487
488 -- end if;
489
490 ------------------- populating bne_interface_cols tables for other columns
491
492 POPULATE_INTERFACE_PARAM_COLS(l_interface_code,p_table_name);
493
494 ------------------ populating bne_layouts for other columns
495
496 POPULATE_LAYOUT(l_object_code,p_table_name);
497
498 --------------------------------------------------------------------------
499
500 COMMIT;
501
502 EXCEPTION
503 --
504 WHEN E_NORMAL_MODE THEN
505 NULL;
506 WHEN E_NO_INTF_COL_MAP THEN
507 x_return_status := FND_API.G_RET_STS_ERROR;
508 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
509 p_data => x_msg_data );
510
511 END POPULATE_METADATA;
512
513 PROCEDURE POPULATE_OTHER_SETUP(P_OBJECT_CODE IN VARCHAR2, P_TABLE_NAME IN VARCHAR2)
514 IS
515 l_integrator_code varchar2(50) := p_object_code || '_INTG';
516 l_interface_code varchar2(50) := p_object_code || '_INTF';
517 l_content_code varchar2(50) := p_object_code || '_CNT';
518 l_mapping_code varchar2(50) := p_object_code || '_MAP';
519 l_security_rule_code varchar2(50) := p_object_code || '_SR';
520 l_user_id number := 2;
521 l_table_display_name varchar2(150) := null;
522 begin
523
524 select display_name into l_table_display_name from fem_tables_vl
525 where table_name = p_table_name;
526
527
528 ---- Create a security rule ---------------------------
529
530 BNE_SECURITY_UTILS_PKG.ADD_OBJECT_RULES
531 ( P_APPLICATION_ID => 274,
532 P_OBJECT_CODE => l_integrator_code,
533 P_OBJECT_TYPE => 'INTEGRATOR',
534 P_SECURITY_CODE => l_security_rule_code,
535 P_SECURITY_TYPE => 'FUNCTION',
536 P_SECURITY_VALUE => 'FEM_WEBADI_TABLES,BNE_ADI_DEFINE_MAPPING,BNE_TEXT_MAP_DEFINE,BNE_ADI_DEFINE_LAYOUT,BNE_LAYOUT_DEFINITION,BNE_ADI_LOB_MANAGEMENT,BNE_LOB_MANAGEMENT',
537 P_USER_ID => 2);
538
539 ---- Update content ----------------------------------
540 -- We say update content because as an when we create an integrator, a corresponding row is populated in bne_contents_b / tl. This procedure call will simply update some fields
541 -- for that already populated row. Moreover this makes a new row entry in the bne_content_cols_b / tl.
542
543 BNE_CONTENT_UTILS.CREATE_CONTENT_STORED_SQL(
544 P_APPLICATION_ID => 274,
545 P_OBJECT_CODE => p_object_code,
546 P_INTEGRATOR_CODE => l_integrator_code,
547 P_CONTENT_DESC => 'Enterprise Performace Foundation: ' || l_table_display_name || ' Content',
548 P_COL_LIST => 'DISPLAY_NAME',
549 P_QUERY => 'select display_name from fem_tables_vl where table_name = $PARAM$.table_name',
550 P_LANGUAGE => USERENV('LANG'),
551 P_SOURCE_LANGUAGE => USERENV('LANG'),
552 P_USER_ID => l_user_id,
553 P_CONTENT_CODE => l_content_code);
554
555 -- Bug#7423745
556 add_content_cols_language;
557 add_content_language;
558
559 update bne_contents_b
560 set param_list_app_id = 274,
561 param_list_code = 'FEM_TABLE_DNLD_LIST'
562 where content_code = l_content_code;
563
564 ---- Create mapping ---------------------------------
565
566 insert into bne_mappings_b(APPLICATION_ID,
567 MAPPING_CODE,
568 OBJECT_VERSION_NUMBER,
569 INTEGRATOR_APP_ID,
570 INTEGRATOR_CODE,
571 REPORTING_FLAG,
572 REPORTING_INTERFACE_APP_ID,
573 REPORTING_INTERFACE_CODE,
574 CREATED_BY,
575 CREATION_DATE,
576 LAST_UPDATED_BY,
577 LAST_UPDATE_LOGIN,
578 LAST_UPDATE_DATE)
579 values
580 (274,l_mapping_code,1,274,l_integrator_code,'N',NULL,NULL,l_user_id,SYSDATE,l_user_id,0,SYSDATE);
581
582 insert into bne_mappings_tl(APPLICATION_ID,
583 MAPPING_CODE,
584 LANGUAGE,
585 SOURCE_LANG,
586 USER_NAME,
587 CREATED_BY,
588 CREATION_DATE,
589 LAST_UPDATED_BY,
590 LAST_UPDATE_LOGIN,
591 LAST_UPDATE_DATE)
592 SELECT 274
593 , l_mapping_code
594 , T.LANGUAGE
595 , T.SOURCE_LANG
596 , SUBSTR(M.MESSAGE_TEXT,0, INSTR(M.MESSAGE_TEXT, 'TABLE_NAME')-2) ||
597 T.DISPLAY_NAME ||
598 SUBSTR(M.MESSAGE_TEXT,INSTR(M.MESSAGE_TEXT, 'TABLE_NAME')+10)
599 , l_user_id
600 , SYSDATE
601 , l_user_id
602 , 0
603 , SYSDATE
604 FROM FEM_TABLES_TL T, FND_NEW_MESSAGES M, FND_LANGUAGES L
605 WHERE T.TABLE_NAME = p_table_name
606 AND M.APPLICATION_ID= 274
607 AND M.MESSAGE_NAME = 'FEM_ADI_TABLES_MAPPING'
608 AND M.LANGUAGE_CODE = T.LANGUAGE
609 AND M.LANGUAGE_CODE = L.LANGUAGE_CODE
610 AND L.INSTALLED_FLAG IN ('I', 'B');
611
612
613 insert into bne_mapping_lines
614 (APPLICATION_ID,
615 MAPPING_CODE,
616 SEQUENCE_NUM,
617 CONTENT_APP_ID,
618 CONTENT_CODE,
619 CONTENT_SEQ_NUM,
620 INTERFACE_APP_ID,
621 INTERFACE_CODE,
622 INTERFACE_SEQ_NUM,
623 OBJECT_VERSION_NUMBER,
624 CREATED_BY,
625 CREATION_DATE,
626 LAST_UPDATED_BY,
627 LAST_UPDATE_LOGIN,
628 LAST_UPDATE_DATE,
629 DECODE_FLAG)
630 values
631 (274,l_mapping_code,1,274,l_content_code,1,274,l_interface_code,1,1,2,SYSDATE,2,0,SYSDATE,'N');
632
633 END POPULATE_OTHER_SETUP;
634
635 PROCEDURE POPULATE_LAYOUT(P_OBJECT_CODE IN VARCHAR2,P_TABLE_NAME IN VARCHAR2)
636 IS
637 l_layout_code varchar2(50) := p_object_code || '_LAYOUT';
638 l_layout_exists varchar2(1);
639 l_integrator_code varchar2(50) := p_object_code || '_INTG';
640 l_header_block_id BNE_LAYOUT_BLOCKS_B.BLOCK_ID%TYPE;
641 l_line_block_id BNE_LAYOUT_BLOCKS_B.BLOCK_ID%TYPE;
642 l_interface_code varchar2(50) := p_object_code || '_INTF';
643 l_sequence_num number;
644 l_list_item_num number;
645 l_layout_seq number := 1;
646
647 l_user_id NUMBER(15) := 2;
648 l_login_id NUMBER := NVL(Fnd_Global.Login_Id, 0);
649
650 begin
651
652 BEGIN
653 SELECT 'Y'
654 INTO l_layout_exists
655 FROM BNE_LAYOUTS_B
656 WHERE APPLICATION_ID = 274
657 AND LAYOUT_CODE = l_layout_code;
658 EXCEPTION
659 WHEN NO_DATA_FOUND THEN NULL;
660 END;
661
662 if(l_layout_exists is null) then
663
664 ---------------------------------------------------------------------------
665 -- Create Layout in BNE_LAYOUTS_B and BNE_LAYOUTS_TL
666 ---------------------------------------------------------------------------
667 INSERT INTO BNE_LAYOUTS_B
668 ( APPLICATION_ID
669 , LAYOUT_CODE
670 , OBJECT_VERSION_NUMBER
671 , STYLESHEET_APP_ID
672 , STYLESHEET_CODE
673 , INTEGRATOR_APP_ID
674 , INTEGRATOR_CODE
675 , STYLE
676 , STYLE_CLASS
677 , REPORTING_FLAG
678 , REPORTING_INTERFACE_APP_ID
679 , REPORTING_INTERFACE_CODE
680 , CREATION_DATE
681 , CREATED_BY
682 , LAST_UPDATE_DATE
683 , LAST_UPDATED_BY
684 , LAST_UPDATE_LOGIN
685 , CREATE_DOC_LIST_APP_ID
686 , CREATE_DOC_LIST_CODE
687 )
688 VALUES
689 ( 274
690 , l_layout_code
691 , 1
692 , 231
693 , 'DEFAULT'
694 , 274
695 , l_integrator_code
696 , NULL
697 , 'BNE_PAGE'
698 , 'N'
699 , NULL
700 , NULL
701 , SYSDATE
702 , l_user_id
703 , SYSDATE
704 , l_user_id
705 , l_login_id
706 , NULL
707 , NULL
708 );
709
710 INSERT INTO BNE_LAYOUTS_TL
711 ( APPLICATION_ID
712 , LAYOUT_CODE
713 , USER_NAME
714 , CREATED_BY
715 , CREATION_DATE
716 , LAST_UPDATED_BY
717 , LAST_UPDATE_LOGIN
718 , LAST_UPDATE_DATE
719 , LANGUAGE
720 , SOURCE_LANG
721 )
722 SELECT 274
723 , l_layout_code
724 , SUBSTR(M.MESSAGE_TEXT,0, INSTR(M.MESSAGE_TEXT, 'TABLE_NAME')-2) ||
725 T.DISPLAY_NAME ||
726 SUBSTR(M.MESSAGE_TEXT,INSTR(M.MESSAGE_TEXT, 'TABLE_NAME')+10)
727 , l_user_id
728 , SYSDATE
729 , l_user_id
730 , l_login_id
731 , SYSDATE
732 , T.LANGUAGE
733 , T.SOURCE_LANG
734 FROM FEM_TABLES_TL T,FND_NEW_MESSAGES M,FND_LANGUAGES L
735 WHERE T.TABLE_NAME = p_table_name
736 AND M.APPLICATION_ID= 274
737 AND M.MESSAGE_NAME = 'FEM_ADI_TABLES_LAYOUT'
738 AND M.LANGUAGE_CODE = T.LANGUAGE
739 AND M.LANGUAGE_CODE = L.LANGUAGE_CODE
740 AND L.INSTALLED_FLAG IN ('I', 'B');
741
742 END IF;
743
744 -----------------------------------------------------------------------------
745 -- Creaate header block within the layout
746 -----------------------------------------------------------------------------
747
748 BEGIN
749 SELECT B.BLOCK_ID
750 INTO l_header_block_id
751 FROM BNE_LAYOUT_BLOCKS_B B
752 WHERE B.APPLICATION_ID = 274
753 AND B.LAYOUT_CODE = l_layout_code
754 AND B.LAYOUT_ELEMENT = 'HEADER';
755 EXCEPTION
756 WHEN NO_DATA_FOUND THEN NULL;
757 END;
758
759 IF (l_header_block_id IS NULL) THEN
760 ---------------------------------------------------------------------------
761 -- Insert a new block into BNE_LAYOUT_BLOCKS_B
762 ---------------------------------------------------------------------------
763
764 l_header_block_id := 1;
765
766 INSERT INTO BNE_LAYOUT_BLOCKS_B
767 ( APPLICATION_ID
768 , LAYOUT_CODE
769 , BLOCK_ID
770 , OBJECT_VERSION_NUMBER
771 , PARENT_ID
772 , LAYOUT_ELEMENT
773 , STYLE_CLASS
774 , STYLE
775 , ROW_STYLE_CLASS
776 , ROW_STYLE
777 , COL_STYLE_CLASS
778 , COL_STYLE
779 , PROMPT_DISPLAYED_FLAG
780 , PROMPT_STYLE_CLASS
781 , PROMPT_STYLE
782 , HINT_DISPLAYED_FLAG
783 , HINT_STYLE_CLASS
784 , HINT_STYLE
785 , ORIENTATION
786 , LAYOUT_CONTROL
787 , DISPLAY_FLAG
788 , BLOCKSIZE
789 , MINSIZE
790 , MAXSIZE
791 , SEQUENCE_NUM
792 , PROMPT_COLSPAN
793 , HINT_COLSPAN
794 , ROW_COLSPAN
795 , SUMMARY_STYLE_CLASS
796 , SUMMARY_STYLE
797 , CREATION_DATE
798 , CREATED_BY
799 , LAST_UPDATE_DATE
800 , LAST_UPDATED_BY
801 , LAST_UPDATE_LOGIN
802 ) VALUES
803 ( 274
804 , l_layout_code
805 , l_header_block_id
806 , 1
807 , NULL
808 , 'HEADER'
809 , 'BNE_HEADER'
810 , NULL
811 , 'BNE_HEADER_ROW'
812 , NULL
813 , NULL
814 , NULL
815 , 'Y'
816 , 'BNE_HEADER_HEADER'
817 , NULL
818 , 'Y'
819 , 'BNE_HEADER_HINT'
820 , NULL
821 , 'HORIZONTAL'
822 , 'COLUMN_FLOW'
823 , 'Y'
824 , 1
825 , 1
826 , 1
827 , 10
828 , 3
829 , 1
830 , 2
831 , 'BNE_LINES_TOTAL'
832 , NULL
833 , SYSDATE
834 , l_user_id
835 , SYSDATE
836 , l_user_id
837 , l_login_id
838 );
839
840 INSERT INTO BNE_LAYOUT_BLOCKS_TL
841 ( APPLICATION_ID
842 , LAYOUT_CODE
843 , BLOCK_ID
844 , USER_NAME
845 , CREATED_BY
846 , CREATION_DATE
847 , LAST_UPDATED_BY
848 , LAST_UPDATE_LOGIN
849 , LAST_UPDATE_DATE
850 , LANGUAGE
851 , SOURCE_LANG
852 )
853 SELECT 274
854 , l_layout_code
855 , l_header_block_id
856 , M.MESSAGE_TEXT
857 , l_user_id
858 , SYSDATE
859 , l_user_id
860 , l_login_id
861 , SYSDATE
862 , L.LANGUAGE_CODE
863 , USERENV('LANG')
864 FROM FND_NEW_MESSAGES M,
865 FND_LANGUAGES L
866 WHERE M.MESSAGE_NAME = 'LAY_LB_HEADER'
867 AND M.LANGUAGE_CODE = L.LANGUAGE_CODE
868 AND L.INSTALLED_FLAG IN ('I', 'B');
869
870 END IF;
871
872 -----------------------------------------------------------------------------
873 -- Creaate line block within the layout
874 -----------------------------------------------------------------------------
875 BEGIN
876 SELECT B.BLOCK_ID
877 INTO l_line_block_id
878 FROM BNE_LAYOUT_BLOCKS_B B
879 WHERE B.APPLICATION_ID = 274
880 AND B.LAYOUT_CODE = l_layout_code
881 AND B.LAYOUT_ELEMENT = 'LINE'
882 AND B.PARENT_ID =
883 (
884 SELECT BLOCK_ID
885 FROM BNE_LAYOUT_BLOCKS_B
886 WHERE APPLICATION_ID = B.APPLICATION_ID
887 AND LAYOUT_CODE = B.LAYOUT_CODE
888 AND LAYOUT_ELEMENT = 'HEADER'
889 );
890 EXCEPTION
891 WHEN NO_DATA_FOUND THEN NULL;
892 END;
893
894 IF (l_line_block_id IS NULL) THEN
895 ---------------------------------------------------------------------------
896 -- Insert Line block into the layout
897 ---------------------------------------------------------------------------
898 l_line_block_id := 2;
899
900 INSERT INTO BNE_LAYOUT_BLOCKS_B
901 ( APPLICATION_ID
902 , LAYOUT_CODE
903 , BLOCK_ID
904 , OBJECT_VERSION_NUMBER
905 , PARENT_ID
906 , LAYOUT_ELEMENT
907 , STYLE_CLASS
908 , STYLE
909 , ROW_STYLE_CLASS
910 , ROW_STYLE
911 , COL_STYLE_CLASS
912 , COL_STYLE
913 , PROMPT_DISPLAYED_FLAG
914 , PROMPT_STYLE_CLASS
915 , PROMPT_STYLE
916 , HINT_DISPLAYED_FLAG
917 , HINT_STYLE_CLASS
918 , HINT_STYLE
919 , ORIENTATION
920 , LAYOUT_CONTROL
921 , DISPLAY_FLAG
922 , BLOCKSIZE
923 , MINSIZE
924 , MAXSIZE
925 , SEQUENCE_NUM
926 , PROMPT_COLSPAN
927 , HINT_COLSPAN
928 , ROW_COLSPAN
929 , SUMMARY_STYLE_CLASS
930 , SUMMARY_STYLE
931 , CREATION_DATE
932 , CREATED_BY
933 , LAST_UPDATE_DATE
934 , LAST_UPDATED_BY
935 , LAST_UPDATE_LOGIN
936 ) VALUES
937 ( 274
938 , l_layout_code
939 , l_line_block_id
940 , 1
941 , l_header_block_id
942 , 'LINE'
943 , 'BNE_LINES'
944 , NULL
945 , 'BNE_LINES_ROW'
946 , NULL
947 , NULL
948 , NULL
949 , 'Y'
950 , 'BNE_LINES_HEADER'
951 , NULL
952 , 'Y'
953 , 'BNE_LINES_HINT'
954 , NULL
955 , 'VERTICAL'
956 , 'TABLE_FLOW'
957 , 'Y'
958 , 10
959 , 1
960 , 1
961 , 20
962 , NULL
963 , NULL
964 , NULL
965 , 'BNE_LINES_TOTAL'
966 , NULL
967 , SYSDATE
968 , l_user_id
969 , SYSDATE
970 , l_user_id
971 , l_login_id
972 );
973
974 INSERT INTO BNE_LAYOUT_BLOCKS_TL
975 ( APPLICATION_ID
976 , LAYOUT_CODE
977 , BLOCK_ID
978 , USER_NAME
979 , CREATED_BY
980 , CREATION_DATE
981 , LAST_UPDATED_BY
982 , LAST_UPDATE_LOGIN
983 , LAST_UPDATE_DATE
984 , LANGUAGE
985 , SOURCE_LANG
986 )
987 SELECT 274
988 , l_layout_code
989 , l_line_block_id
990 , M.MESSAGE_TEXT
991 , l_user_id
992 , SYSDATE
993 , l_user_id
994 , l_login_id
995 , SYSDATE
996 , L.LANGUAGE_CODE
997 , USERENV('LANG')
998 FROM FND_NEW_MESSAGES M,
999 FND_LANGUAGES L
1000 WHERE M.MESSAGE_NAME = 'LAY_LB_LINE'
1001 AND M.LANGUAGE_CODE = L.LANGUAGE_CODE
1002 AND L.INSTALLED_FLAG IN ('I', 'B');
1003
1004 END IF;
1005
1006
1007 -----------------------------------------------------------------------------
1008 -- Delete and Insert into BNE_LAYOUT_COLS
1009 -----------------------------------------------------------------------------
1010 delete from
1011 bne_layout_cols
1012 where
1013 layout_code = l_layout_code;
1014
1015 for layout_cols in
1016 (
1017 select * from bne_interface_cols_b where interface_code = l_interface_code
1018 and application_id = 274 and interface_col_type = 1 and enabled_flag = 'Y'
1019 and display_flag = 'Y'
1020 )
1021 loop
1022
1023 INSERT INTO BNE_LAYOUT_COLS
1024 ( APPLICATION_ID
1025 , LAYOUT_CODE
1026 , BLOCK_ID
1027 , OBJECT_VERSION_NUMBER
1028 , INTERFACE_APP_ID
1029 , INTERFACE_CODE
1030 , INTERFACE_SEQ_NUM
1031 , SEQUENCE_NUM
1032 , STYLE
1033 , STYLE_CLASS
1034 , HINT_STYLE
1035 , HINT_STYLE_CLASS
1036 , PROMPT_STYLE
1037 , PROMPT_STYLE_CLASS
1038 , DEFAULT_TYPE
1039 , DEFAULT_VALUE
1040 , CREATED_BY
1041 , CREATION_DATE
1042 , LAST_UPDATED_BY
1043 , LAST_UPDATE_LOGIN
1044 , LAST_UPDATE_DATE
1045 )
1046 VALUES(
1047 274
1048 ,l_layout_code
1049 ,decode(layout_cols.interface_col_name,'P_TABLE_NAME',1,'P_LEDGER_DISPLAY_CODE',1,'P_CAL_PERIOD',1,'P_DATASET_CODE',1,'P_SOURCE_SYSTEM_DISPLAY_CODE',1,2)
1050 ,1
1051 ,274
1052 ,l_interface_code
1053 ,layout_cols.sequence_num
1054 ,l_layout_seq * 10
1055 ,NULL
1056 ,NULL
1057 ,NULL
1058 ,NULL
1059 ,NULL
1060 ,NULL
1061 ,NULL
1062 ,NULL
1063 ,l_user_id
1064 ,SYSDATE
1065 ,l_user_id
1066 ,l_login_id
1067 ,SYSDATE);
1068
1069 l_layout_seq := l_layout_seq + 1;
1070 end loop;
1071
1072 update bne_layout_cols b
1073 set interface_seq_num = (select sequence_num from bne_interface_cols_b where interface_code = l_interface_code and display_order = b.sequence_num)
1074 where layout_code = l_layout_code
1075 and block_id =2;
1076
1077
1078 --------------------------- Populating remaining params from the API into bne_interface_cols_b / tl
1079
1080 select max(sequence_num) into l_sequence_num from
1081 bne_interface_cols_b where interface_code = l_interface_code;
1082
1083 for extra_params in
1084 (
1085 select bne_interface_col_name from fem_webadi_table_cols_maps
1086 where interface_table_name = (select interface_table_name from fem_tables_b where table_name = p_table_name)
1087 and bne_interface_col_name not in(select interface_col_name from
1088 bne_interface_cols_b where interface_code = l_interface_code)
1089 )
1090 loop
1091
1092 INSERT INTO BNE_INTERFACE_COLS_B (
1093 INTERFACE_COL_TYPE,
1094 INTERFACE_COL_NAME,
1095 ENABLED_FLAG,
1096 REQUIRED_FLAG,
1097 DISPLAY_FLAG,
1098 READ_ONLY_FLAG,
1099 NOT_NULL_FLAG,
1100 SUMMARY_FLAG,
1101 MAPPING_ENABLED_FLAG,
1102 DATA_TYPE,
1103 FIELD_SIZE,
1104 DEFAULT_TYPE,
1105 DEFAULT_VALUE,
1106 SEGMENT_NUMBER,
1107 GROUP_NAME,
1108 OA_FLEX_CODE,
1109 OA_CONCAT_FLEX,
1110 VAL_TYPE,
1111 VAL_ID_COL,
1112 VAL_MEAN_COL,
1113 VAL_DESC_COL,
1114 VAL_OBJ_NAME,
1115 VAL_ADDL_W_C,
1116 VAL_COMPONENT_APP_ID,
1117 VAL_COMPONENT_CODE,
1118 OA_FLEX_NUM,
1119 OA_FLEX_APPLICATION_ID,
1120 DISPLAY_ORDER,
1121 UPLOAD_PARAM_LIST_ITEM_NUM,
1122 EXPANDED_SQL_QUERY,
1123 APPLICATION_ID,
1124 INTERFACE_CODE,
1125 OBJECT_VERSION_NUMBER,
1126 SEQUENCE_NUM,
1127 LOV_TYPE,
1128 OFFLINE_LOV_ENABLED_FLAG,
1129 CREATION_DATE,
1130 CREATED_BY,
1131 LAST_UPDATE_DATE,
1132 LAST_UPDATED_BY,
1133 LAST_UPDATE_LOGIN,
1134 VARIABLE_DATA_TYPE_CLASS
1135 )
1136 VALUES
1137 ( 1, -- interface col type
1138 extra_params.bne_interface_col_name, -- interface col name
1139 'N', -- enabled flag
1140 'N', -- initially 'N' required flag
1141 'N', -- display flag 'Y' initially
1142 'N', -- read only flag
1143 'N', -- not null flag
1144 'N', -- summary flag
1145 'N', -- mapping enabled flag
1146 2, -- Data Type Varchar for all
1147 NULL, -- field size
1148 NULL, -- default type
1149 NULL, -- default value
1150 NULL, -- segment number
1151 NULL, -- group name
1152 NULL, -- oa flex code
1153 NULL, -- oa concat flex
1154 NULL, -- validation type
1155 NULL, -- val id col
1156 NULL, -- val mean col
1157 NULL, -- val desc col
1158 NULL, -- val object name
1159 NULL, -- vsl where clause
1160 NULL, -- val component app id
1161 NULL, -- val component code
1162 NULL, -- oa flex num
1163 NULL, -- oa flex app id
1164 NULL, -- display order
1165 TO_NUMBER(SUBSTR(extra_params.bne_interface_col_name, 8)) + 5, -- upload param list item number
1166 NULL, -- expanded sql query
1167 274, -- application id
1168 l_interface_code, -- interface code
1169 1, -- object version number
1170 l_sequence_num + 1, -- sequence number (max(seq) +1)
1171 NULL, -- lov type
1172 NULL, -- offline enabled lov flag
1173 SYSDATE, -- creation date
1174 l_user_id, -- created by
1175 SYSDATE, -- last updated by
1176 l_user_id, -- last updated by
1177 l_login_id, -- last update login
1178 NULL -- variable data type class
1179 );
1180
1181 l_sequence_num := l_sequence_num + 1;
1182
1183 end loop;
1184
1185 select max(sequence_num) into l_sequence_num from
1186 bne_interface_cols_b where interface_code = l_interface_code;
1187
1188 select (max(UPLOAD_PARAM_LIST_ITEM_NUM) - 5) into l_list_item_num from bne_interface_cols_b
1189 where interface_code = l_interface_code;
1190
1191 l_list_item_num := l_list_item_num + 1;
1192 l_sequence_num := l_sequence_num + 1;
1193
1194 for l_interface_code_postfix in l_list_item_num..300
1195 loop
1196
1197 INSERT INTO BNE_INTERFACE_COLS_B (
1198 INTERFACE_COL_TYPE,
1199 INTERFACE_COL_NAME,
1200 ENABLED_FLAG,
1201 REQUIRED_FLAG,
1202 DISPLAY_FLAG,
1203 READ_ONLY_FLAG,
1204 NOT_NULL_FLAG,
1205 SUMMARY_FLAG,
1206 MAPPING_ENABLED_FLAG,
1207 DATA_TYPE,
1208 FIELD_SIZE,
1209 DEFAULT_TYPE,
1210 DEFAULT_VALUE,
1211 SEGMENT_NUMBER,
1212 GROUP_NAME,
1213 OA_FLEX_CODE,
1214 OA_CONCAT_FLEX,
1215 VAL_TYPE,
1216 VAL_ID_COL,
1217 VAL_MEAN_COL,
1218 VAL_DESC_COL,
1219 VAL_OBJ_NAME,
1220 VAL_ADDL_W_C,
1221 VAL_COMPONENT_APP_ID,
1222 VAL_COMPONENT_CODE,
1223 OA_FLEX_NUM,
1224 OA_FLEX_APPLICATION_ID,
1225 DISPLAY_ORDER,
1226 UPLOAD_PARAM_LIST_ITEM_NUM,
1227 EXPANDED_SQL_QUERY,
1228 APPLICATION_ID,
1229 INTERFACE_CODE,
1230 OBJECT_VERSION_NUMBER,
1231 SEQUENCE_NUM,
1232 LOV_TYPE,
1233 OFFLINE_LOV_ENABLED_FLAG,
1234 CREATION_DATE,
1235 CREATED_BY,
1236 LAST_UPDATE_DATE,
1237 LAST_UPDATED_BY,
1238 LAST_UPDATE_LOGIN,
1239 VARIABLE_DATA_TYPE_CLASS
1240 )
1241 VALUES
1242 ( 1, -- interface col type
1243 'P_PARAM' || l_list_item_num , -- interface col name
1244 'N', -- enabled flag
1245 'N', -- initially 'N' required flag
1246 'N', -- display flag 'Y' initially
1247 'N', -- read only flag
1248 'N', -- not null flag
1249 'N', -- summary flag
1250 'N', -- mapping enabled flag
1251 2, -- Data Type Varchar for all
1252 NULL, -- field size
1253 NULL, -- default type
1254 NULL, -- default value
1255 NULL, -- segment number
1256 NULL, -- group name
1257 NULL, -- oa flex code
1258 NULL, -- oa concat flex
1259 NULL, -- validation type
1260 NULL, -- val id col
1261 NULL, -- val mean col
1262 NULL, -- val desc col
1263 NULL, -- val object name
1264 NULL, -- vsl where clause
1265 NULL, -- val component app id
1266 NULL, -- val component code
1267 NULL, -- oa flex num
1268 NULL, -- oa flex app id
1269 NULL, -- display order
1270 l_list_item_num + 5, -- upload param list item number
1271 NULL, -- expanded sql query
1272 274, -- application id
1273 l_interface_code, -- interface code
1274 1, -- object version number
1275 l_sequence_num, -- sequence number (max(seq) +1)
1276 NULL, -- lov type
1277 NULL, -- offline enabled lov flag
1278 SYSDATE, -- creation date
1279 l_user_id, -- created by
1280 SYSDATE, -- last updated by
1281 l_user_id, -- last updated by
1282 l_login_id, -- last update login
1283 NULL -- variable data type class
1284 );
1285
1286 l_sequence_num := l_sequence_num + 1;
1287 l_list_item_num := l_list_item_num + 1;
1288
1289 end loop;
1290
1291
1292 END POPULATE_LAYOUT;
1293
1294 PROCEDURE POPULATE_INTERFACE_PARAM_COLS(P_INTERFACE_CODE IN VARCHAR2, P_TABLE_NAME IN VARCHAR2)
1295 IS
1296
1297 l_table_name varchar2(50) := p_table_name; --substr(p_interface_code,0,instr(p_interface_code,'INTF')-2);
1298 l_java_val_class varchar2(500) := 'oracle.apps.fem.integrator.tables.validators.FemMemberValidator';
1299 --l_java_group_val_class varchar2(500) := 'oracle.apps.fem.integrator.tables.validators.FemTablesGroupValidator';
1300 l_java_group_val_class varchar2(500) := null;
1301 l_component_code varchar2(50) := 'FEM_DIMENSION_MEMBER';
1302 l_group_name varchar(50) := 'TABLE_GROUP_VALIDATOR';
1303 l_sequence_num number := 7; -- 1 to 6 are already assigned to req cols and grp validator rows
1304 l_segment_num number := 6; -- 1-5 are assigned to req cols and grp validator but table name
1305 l_user_id number := 2;
1306 l_login_id number := NVL(Fnd_Global.Login_Id, 0);
1307 l_interface_table_name varchar2(50);
1308 l_display_order number := 6;
1309
1310 begin
1311
1312 select interface_table_name into l_interface_table_name from
1313 fem_tables_b where table_name = l_table_name;
1314
1315 --------- First populating bne_interface_cols ( 'delete insert' those records for which we have info in fem_tab_columns_b table
1316
1317 ------------------------------- For bne_interface_cols_b
1318
1319 for new_rec in
1320 (
1321 select M.bne_interface_col_name bne_interface_col_name,M.interface_column_name interface_column_name,M.data_type data_type ,M.nullable nullable,
1322 T.fem_data_type_code fem_data_type,T.column_name column_name
1323 from
1324 FEM_WEBADI_TABLE_COLS_MAPS M,FEM_TAB_COLUMNS_B T
1325 where T.TABLE_NAME = l_table_name
1326 and M.INTERFACE_TABLE_NAME = l_interface_table_name
1327 and T.INTERFACE_COLUMN_NAME = M.INTERFACE_COLUMN_NAME
1328 )
1329
1330 loop -- Starting the loop to insert entries one by one
1331
1332
1333 INSERT INTO BNE_INTERFACE_COLS_B (
1334 INTERFACE_COL_TYPE,
1335 INTERFACE_COL_NAME,
1336 ENABLED_FLAG,
1337 REQUIRED_FLAG,
1338 DISPLAY_FLAG,
1339 READ_ONLY_FLAG,
1340 NOT_NULL_FLAG,
1341 SUMMARY_FLAG,
1342 MAPPING_ENABLED_FLAG,
1343 DATA_TYPE,
1344 FIELD_SIZE,
1345 DEFAULT_TYPE,
1346 DEFAULT_VALUE,
1347 SEGMENT_NUMBER,
1348 GROUP_NAME,
1349 OA_FLEX_CODE,
1350 OA_CONCAT_FLEX,
1351 VAL_TYPE,
1352 VAL_ID_COL,
1353 VAL_MEAN_COL,
1354 VAL_DESC_COL,
1355 VAL_OBJ_NAME,
1356 VAL_ADDL_W_C,
1357 VAL_COMPONENT_APP_ID,
1358 VAL_COMPONENT_CODE,
1359 OA_FLEX_NUM,
1360 OA_FLEX_APPLICATION_ID,
1361 DISPLAY_ORDER,
1362 UPLOAD_PARAM_LIST_ITEM_NUM,
1363 EXPANDED_SQL_QUERY,
1364 APPLICATION_ID,
1365 INTERFACE_CODE,
1366 OBJECT_VERSION_NUMBER,
1367 SEQUENCE_NUM,
1368 LOV_TYPE,
1369 OFFLINE_LOV_ENABLED_FLAG,
1370 CREATION_DATE,
1371 CREATED_BY,
1372 LAST_UPDATE_DATE,
1373 LAST_UPDATED_BY,
1374 LAST_UPDATE_LOGIN,
1375 VARIABLE_DATA_TYPE_CLASS
1376 )
1377 VALUES
1378 ( 1, -- interface col type
1379 new_rec.bne_interface_col_name, -- interface col name
1380 'Y', -- enabled flag
1381 decode(new_rec.nullable,'Y','N','N','Y','N'), -- initially 'N' required flag
1382 'Y', -- display flag 'Y' initially
1383 'N', -- read only flag
1384 decode(new_rec.nullable,'Y','N','N','Y','N'), -- not null flag
1385 'N', -- summary flag
1386 'N',-- mapping enabled flag
1387 2, -- decode(new_rec.data_type,'DATE',3,2), -- Data Type Varchar for all
1388 2000, -- field size
1389 NULL, -- default type
1390 NULL, -- default value
1391 NULL, -- decode(new_rec.nullable,'N',l_segment_num,NULL), -- segment number
1392 NULL, -- decode(new_rec.nullable,'N','TABLE_GROUP_VALIDATOR',NULL), -- group name
1393 NULL, -- oa flex code
1394 NULL, -- oa concat flex
1395 DECODE(new_rec.fem_data_type, 'DIMENSION', 'JAVA', NULL), -- validation type
1396 NULL, -- val id col
1397 NULL, -- val mean col
1398 NULL, -- val desc col
1399 DECODE(new_rec.fem_data_type, 'DIMENSION', 'oracle.apps.fem.integrator.tables.validators.FemMemberValidator', NULL), -- val object name
1400 NULL, -- vsl where clause
1401 DECODE(new_rec.fem_data_type, 'DIMENSION', 274, NULL), -- val component app id
1402 DECODE(new_rec.fem_data_type, 'DIMENSION', l_component_code, NULL), -- val component code
1403 NULL, -- oa flex num
1404 NULL, -- oa flex app id
1405 l_sequence_num*10, -- display order
1406 TO_NUMBER(SUBSTR(new_rec.bne_interface_col_name, 8)) + 5, -- upload param list item number
1407 NULL, -- expanded sql query
1408 274, -- application id
1409 p_interface_code, -- interface code
1410 1, -- object version number
1411 l_sequence_num, -- sequence number
1412 DECODE(new_rec.fem_data_type, 'DIMENSION', 'STANDARD', NULL), -- lov type
1413 DECODE(new_rec.fem_data_type, 'DIMENSION', 'N', NULL), -- offline enabled lov flag
1414 SYSDATE, -- creation date
1415 l_user_id, -- created by
1416 SYSDATE, -- last updated by
1417 l_user_id, -- last updated by
1418 l_login_id, -- last update login
1419 DECODE(new_rec.DATA_TYPE, 'DATE',
1420 'oracle.apps.fem.integrator.dimension.validators.FemAttributeDateTypeValidator',
1421 DECODE(new_rec.DATA_TYPE, 'NUMBER',NULL,
1422 --'oracle.apps.fem.integrator.dimension.validators.FemAttributeNumericTypeValidator',
1423 NULL)) -- variable data type class
1424 );
1425
1426 ----------------- For bne_interface_cols_tl
1427
1428 if(new_rec.fem_data_type = 'DIMENSION') then
1429
1430 INSERT INTO BNE_INTERFACE_COLS_TL
1431 (
1432 CREATED_BY,
1433 CREATION_DATE,
1434 LAST_UPDATED_BY,
1435 LAST_UPDATE_LOGIN,
1436 LAST_UPDATE_DATE,
1437 USER_HINT,
1438 PROMPT_LEFT,
1439 USER_HELP_TEXT,
1440 PROMPT_ABOVE,
1441 INTERFACE_CODE,
1442 SEQUENCE_NUM,
1443 APPLICATION_ID,
1444 LANGUAGE,
1445 SOURCE_LANG
1446 )
1447 SELECT l_user_id
1448 , SYSDATE
1449 , l_user_id
1450 , l_login_id
1451 , SYSDATE
1452 , M.MESSAGE_TEXT
1453 , TL.DISPLAY_NAME
1454 , NULL
1455 , TL.DISPLAY_NAME
1456 , p_interface_code
1457 , l_sequence_num
1458 , 274
1459 , L.LANGUAGE_CODE
1460 , TL.SOURCE_LANG
1461 FROM FEM_TAB_COLUMNS_B TB
1462 , FEM_TAB_COLUMNS_TL TL
1463 , FND_NEW_MESSAGES M
1464 , FND_LANGUAGES L
1465 WHERE L.INSTALLED_FLAG IN ('I', 'B')
1466 AND TB.COLUMN_NAME = new_rec.column_name
1467 AND TB.TABLE_NAME = l_table_name -- substr(p_interface_code,0,instr(p_interface_code,'_INTF')-1)
1468 AND TL.COLUMN_NAME = TB.COLUMN_NAME
1469 AND TL.TABLE_NAME = TB.TABLE_NAME
1470 AND TB.FEM_DATA_TYPE_CODE = 'DIMENSION'
1471 AND TL.LANGUAGE (+) = L.LANGUAGE_CODE
1472 AND M.MESSAGE_NAME (+) =
1473 DECODE(new_rec.fem_data_type,'DIMENSION',DECODE(new_rec.nullable, 'N', 'FEM_ADI_USER_HINT_LOV_REQ', 'FEM_ADI_USER_HINT_LOV'),NULL)
1474 AND M.LANGUAGE_CODE (+) = L.LANGUAGE_CODE;
1475
1476 else
1477
1478 INSERT INTO BNE_INTERFACE_COLS_TL
1479 (
1480 CREATED_BY,
1481 CREATION_DATE,
1482 LAST_UPDATED_BY,
1483 LAST_UPDATE_LOGIN,
1484 LAST_UPDATE_DATE,
1485 USER_HINT,
1486 PROMPT_LEFT,
1487 USER_HELP_TEXT,
1488 PROMPT_ABOVE,
1489 INTERFACE_CODE,
1490 SEQUENCE_NUM,
1491 APPLICATION_ID,
1492 LANGUAGE,
1493 SOURCE_LANG
1494 )
1495 SELECT l_user_id
1496 , SYSDATE
1497 , l_user_id
1498 , l_login_id
1499 , SYSDATE
1500 , M.MESSAGE_TEXT
1501 , TL.DISPLAY_NAME
1502 , NULL
1503 , TL.DISPLAY_NAME
1504 , p_interface_code
1505 , l_sequence_num
1506 , 274
1507 , L.LANGUAGE_CODE
1508 , TL.SOURCE_LANG
1509 FROM FEM_TAB_COLUMNS_B TB
1510 , FEM_TAB_COLUMNS_TL TL
1511 , FND_NEW_MESSAGES M
1512 , FND_LANGUAGES L
1513 WHERE L.INSTALLED_FLAG IN ('I', 'B')
1514 AND TB.COLUMN_NAME = new_rec.column_name
1515 AND TB.TABLE_NAME = l_table_name -- substr(p_interface_code,0,instr(p_interface_code,'_INTF')-1)
1516 AND TL.COLUMN_NAME = TB.COLUMN_NAME
1517 AND TL.TABLE_NAME = TB.TABLE_NAME
1518 AND TB.FEM_DATA_TYPE_CODE <> 'DIMENSION'
1519 AND TL.LANGUAGE (+) = L.LANGUAGE_CODE
1520 AND M.MESSAGE_NAME (+)=
1521 DECODE(new_rec.DATA_TYPE, 'VARCHAR2',
1522 DECODE(new_rec.nullable, 'N', 'FEM_ADI_USER_HINT_TEXT_REQ', 'FEM_ADI_USER_HINT_TEXT'),
1523 DECODE(new_rec.DATA_TYPE, 'NUMBER',
1524 DECODE(new_rec.nullable, 'N', 'FEM_ADI_USER_HINT_NUMBER_REQ', 'FEM_ADI_USER_HINT_NUMBER'),
1525 DECODE(new_rec.DATA_TYPE, 'DATE',
1526 DECODE(new_rec.nullable, 'N', 'FEM_ADI_USER_HINT_DATE_REQ', 'FEM_ADI_USER_HINT_DATE'), NULL)))
1527 AND M.LANGUAGE_CODE (+)= L.LANGUAGE_CODE;
1528
1529 end if;
1530
1531 l_sequence_num := l_sequence_num + 1;
1532
1533 -- if(new_rec.nullable = 'N') then
1534 -- l_segment_num := l_segment_num + 1;
1535 -- end if;
1536
1537 --if (SQL%NOTFOUND) then
1538 -- RAISE NO_DATA_FOUND;
1539 -- end if;
1540
1541 end loop; -- Ending loop for inseting into bne_interface_cols tables.
1542
1543 -- A row in the bne_interface_cols_tl only, ensures that the interface column info is fine.
1544 -- now disable the columns in bne_interface_cols_b for which there is no entry in bne_interface_cols_tl.
1545 -- and disable any ledger, source_system, cal_period, dataset, cal period number, cal period grp display code, cal period end date.
1546
1547 update bne_interface_cols_b
1548 set enabled_flag = 'N',
1549 display_flag = 'N',
1550 required_flag = 'N',
1551 not_null_flag = 'N'
1552 where
1553 interface_code = p_interface_code and
1554 interface_col_name in
1555 (
1556 select interface_col_name from bne_interface_cols_b tb,fem_webadi_table_cols_maps m, fem_tab_columns_b b where
1557 tb.interface_col_name = m.bne_interface_col_name and
1558 m.interface_table_name = l_interface_table_name and
1559 tb.interface_code = p_interface_code and
1560 m.interface_column_name = b.interface_column_name and
1561 b.table_name = l_table_name and
1562 (b.dimension_id in (select dimension_id from fem_dimensions_b where dimension_varchar_label in
1563 ('LEDGER','CAL_PERIOD','DATASET','SOURCE_SYSTEM'))
1564 or not exists( select interface_col_name from bne_interface_cols_vl tl
1565 where tl.interface_col_name = tb.interface_col_name and interface_code = p_interface_code) or
1566 m.interface_column_name in ('CALP_DIM_GRP_DISPLAY_CODE','CAL_PERIOD_NUMBER','CAL_PERIOD_END_DATE'))
1567 );
1568
1569 -------------- Updating the display order so that required columns are shown first -------------------
1570
1571 update bne_interface_cols_b
1572 set display_order = null
1573 where interface_code = p_interface_code and sequence_num > 6;
1574
1575
1576 for bne_cols in
1577 (
1578 select interface_col_name from bne_interface_cols_vl
1579 where interface_code = p_interface_code
1580 and display_flag = 'Y' and enabled_flag = 'Y'
1581 and sequence_num > 6
1582 order by not_null_flag desc,upper(prompt_above)
1583 )
1584
1585 loop -- Starting the loop to update entries one by one
1586
1587 update bne_interface_cols_b
1588 set display_order = l_display_order * 10
1589 where interface_code = p_interface_code
1590 and interface_col_name = bne_cols.interface_col_name;
1591
1592 l_display_order := l_display_order + 1;
1593
1594 end loop;
1595 -------------------------------------------------------------------------------------------------------
1596
1597 END POPULATE_INTERFACE_PARAM_COLS;
1598
1599 PROCEDURE POPULATE_INTERFACE_COMM_COLS(P_INTERFACE_CODE IN VARCHAR2, P_TABLE_NAME IN VARCHAR2)
1600 IS
1601
1602 TYPE l_interface_col_rec IS RECORD
1603 ( INTERFACE_COL_NAME VARCHAR2(30) ,
1604 INTERFACE_COL_TYPE NUMBER(15) ,
1605 DISPLAY_FLAG VARCHAR2(1) ,
1606 READ_ONLY_FLAG VARCHAR2(1) ,
1607 MAPPING_ENABLED_FLAG VARCHAR2(1) ,
1608 DATA_TYPE NUMBER(15) ,
1609 FIELD_SIZE NUMBER(15) ,
1610 DEFAULT_VALUE VARCHAR(100) ,
1611 DEFAULT_TYPE VARCHAR2(30) ,
1612 SEGMENT_VALUE NUMBER(15) ,
1613 GROUP_NAME VARCHAR2(30) ,
1614 VAL_TYPE VARCHAR2(20) ,
1615 VAL_ID_COL VARCHAR2(240) ,
1616 VAL_MEAN_COL VARCHAR2(240) ,
1617 VAL_DESC_COL VARCHAR2(240) ,
1618 VAL_OBJ_NAME VARCHAR2(240) ,
1619 VAL_ADDL_W_C VARCHAR2(2000),
1620 VAL_COMPONENT_APP_ID NUMBER(15) ,
1621 VAL_COMPONENT_CODE VARCHAR2(30) ,
1622 DISPLAY_ORDER NUMBER(15) ,
1623 UPLOAD_PARAM_LIST_ITEM_NUM NUMBER(15) ,
1624 SEQUENCE_NUM NUMBER(15) ,
1625 LOV_TYPE VARCHAR2(30) ,
1626 OFFLINE_LOV_ENABLED_FLAG VARCHAR2(1) ,
1627 FND_MESSAGE_NAME VARCHAR2(30) ,
1628 USER_HINT_FND_MESSAGE_NAME VARCHAR2(30)
1629 );
1630
1631 TYPE l_interface_cols_typ IS TABLE OF l_interface_col_rec
1632 INDEX BY BINARY_INTEGER;
1633
1634 l_interface_cols_tbl l_interface_cols_typ;
1635 l_user_id NUMBER(15) := 2; -- (user name : initial setup)
1636 l_login_id NUMBER := NVL(Fnd_Global.Login_Id, 0);
1637 l_table_name varchar2(50) := p_table_name; --substr(p_interface_code,0,instr(p_interface_code,'_INTF')-1);
1638 l_table_display_name varchar2(150);
1639
1640 begin
1641 delete from bne_interface_cols_b
1642 where interface_code = p_interface_code;
1643
1644 delete from bne_interface_cols_tl
1645 where interface_code = p_interface_code;
1646
1647 select display_name into l_table_display_name from fem_tables_vl
1648 where table_name = l_table_name;
1649
1650 -----------------------------------------------------------------------------
1651 -- Set up plsql table for interface column definition
1652 -----------------------------------------------------------------------------
1653
1654 -----------------------------------------------------------------------------
1655 -- Set up for column P_TABLE_NAME
1656 -----------------------------------------------------------------------------
1657 l_interface_cols_tbl(1).INTERFACE_COL_NAME := 'P_TABLE_NAME';
1658 l_interface_cols_tbl(1).INTERFACE_COL_TYPE := 1;
1659 l_interface_cols_tbl(1).DISPLAY_FLAG := 'Y';
1660 l_interface_cols_tbl(1).READ_ONLY_FLAG := 'Y';
1661 l_interface_cols_tbl(1).MAPPING_ENABLED_FLAG := 'Y';
1662 l_interface_cols_tbl(1).DATA_TYPE := 2; --VARCHAR
1663 l_interface_cols_tbl(1).FIELD_SIZE := 80;
1664 l_interface_cols_tbl(1).DEFAULT_VALUE := 'select display_name from fem_tables_vl where table_name = $PARAM$.table_name';
1665 l_interface_cols_tbl(1).DEFAULT_TYPE := 'SQL';
1666 l_interface_cols_tbl(1).SEGMENT_VALUE := NULL;
1667 l_interface_cols_tbl(1).GROUP_NAME := NULL; -- 'TABLE_GROUP_VALIDATOR';
1668 l_interface_cols_tbl(1).VAL_TYPE := NULL;
1669 l_interface_cols_tbl(1).VAL_ID_COL := NULL;
1670 l_interface_cols_tbl(1).VAL_MEAN_COL := NULL;
1671 l_interface_cols_tbl(1).VAL_DESC_COL := NULL;
1672 l_interface_cols_tbl(1).VAL_OBJ_NAME := NULL;
1673 l_interface_cols_tbl(1).VAL_ADDL_W_C := NULL;
1674 l_interface_cols_tbl(1).VAL_COMPONENT_APP_ID := NULL;
1675 l_interface_cols_tbl(1).VAL_COMPONENT_CODE := NULL;
1676 l_interface_cols_tbl(1).DISPLAY_ORDER := 10; -- sequence_num * 10
1677 l_interface_cols_tbl(1).UPLOAD_PARAM_LIST_ITEM_NUM := 1;
1678 l_interface_cols_tbl(1).SEQUENCE_NUM := 1;
1679 l_interface_cols_tbl(1).LOV_TYPE := NULL;
1680 l_interface_cols_tbl(1).OFFLINE_LOV_ENABLED_FLAG := NULL;
1681 l_interface_cols_tbl(1).FND_MESSAGE_NAME := 'FEM_ADI_TABLE_NAME';
1682 l_interface_cols_tbl(1).USER_HINT_FND_MESSAGE_NAME := NULL;
1683
1684 -----------------------------------------------------------------------------
1685 -- Set up for column TABLE_GROUP_VALIDATOR
1686 -----------------------------------------------------------------------------
1687 l_interface_cols_tbl(2).INTERFACE_COL_NAME := 'TABLE_GROUP_VALIDATOR';
1688 l_interface_cols_tbl(2).INTERFACE_COL_TYPE := 2;
1689 l_interface_cols_tbl(2).DISPLAY_FLAG := 'N';
1690 l_interface_cols_tbl(2).READ_ONLY_FLAG := 'N';
1691 l_interface_cols_tbl(2).MAPPING_ENABLED_FLAG := 'N';
1692 l_interface_cols_tbl(2).DATA_TYPE := 2; -- VARCHAR
1693 l_interface_cols_tbl(2).FIELD_SIZE := 1;
1694 l_interface_cols_tbl(2).DEFAULT_VALUE := NULL;
1695 l_interface_cols_tbl(2).DEFAULT_TYPE := NULL;
1696 l_interface_cols_tbl(2).SEGMENT_VALUE := 1;
1697 l_interface_cols_tbl(2).GROUP_NAME := 'TABLE_GROUP_VALIDATOR';
1698 l_interface_cols_tbl(2).VAL_TYPE := 'GROUP';
1699 l_interface_cols_tbl(2).VAL_ID_COL := NULL;
1700 l_interface_cols_tbl(2).VAL_MEAN_COL := NULL;
1701 l_interface_cols_tbl(2).VAL_DESC_COL := NULL;
1702 l_interface_cols_tbl(2).VAL_OBJ_NAME := NULL;
1703 l_interface_cols_tbl(2).VAL_ADDL_W_C := NULL;
1704 l_interface_cols_tbl(2).VAL_COMPONENT_APP_ID := NULL;
1705 l_interface_cols_tbl(2).VAL_COMPONENT_CODE := NULL;
1706 l_interface_cols_tbl(2).DISPLAY_ORDER := NULL;
1707 l_interface_cols_tbl(2).UPLOAD_PARAM_LIST_ITEM_NUM := NULL;
1708 l_interface_cols_tbl(2).SEQUENCE_NUM := 2;
1709 l_interface_cols_tbl(2).LOV_TYPE := 'NONE';
1710 l_interface_cols_tbl(2).OFFLINE_LOV_ENABLED_FLAG := 'N';
1711 l_interface_cols_tbl(2).FND_MESSAGE_NAME := NULL;
1712 l_interface_cols_tbl(2).USER_HINT_FND_MESSAGE_NAME := NULL;
1713
1714 -----------------------------------------------------------------------------
1715 -- Set up for column P_LEDGER_DISPLAY_CODE
1716 -----------------------------------------------------------------------------
1717 l_interface_cols_tbl(3).INTERFACE_COL_NAME := 'P_LEDGER_DISPLAY_CODE';
1718 l_interface_cols_tbl(3).INTERFACE_COL_TYPE := 1;
1719 l_interface_cols_tbl(3).DISPLAY_FLAG := 'Y';
1720 l_interface_cols_tbl(3).READ_ONLY_FLAG := 'N';
1721 l_interface_cols_tbl(3).MAPPING_ENABLED_FLAG := 'N';
1722 l_interface_cols_tbl(3).DATA_TYPE := 2; --VARCHAR
1723 l_interface_cols_tbl(3).FIELD_SIZE := 150;
1724 l_interface_cols_tbl(3).DEFAULT_VALUE := NULL;
1725 l_interface_cols_tbl(3).DEFAULT_TYPE := NULL;
1726 l_interface_cols_tbl(3).SEGMENT_VALUE := 2;
1727 l_interface_cols_tbl(3).GROUP_NAME := NULL; -- 'TABLE_GROUP_VALIDATOR';
1728 l_interface_cols_tbl(3).VAL_TYPE := 'TABLE';
1729 l_interface_cols_tbl(3).VAL_ID_COL := 'LEDGER_DISPLAY_CODE';
1730 l_interface_cols_tbl(3).VAL_MEAN_COL := 'LEDGER_NAME';
1731 l_interface_cols_tbl(3).VAL_DESC_COL := 'DESCRIPTION';
1732 l_interface_cols_tbl(3).VAL_OBJ_NAME := 'FEM_LEDGERS_VL';
1733 l_interface_cols_tbl(3).VAL_ADDL_W_C := 'ENABLED_FLAG = ''Y'' AND PERSONAL_FLAG = ''N''';
1734 l_interface_cols_tbl(3).VAL_COMPONENT_APP_ID := 274;
1735 l_interface_cols_tbl(3).VAL_COMPONENT_CODE := 'FEM_LEDGER_CODE';
1736 l_interface_cols_tbl(3).DISPLAY_ORDER := 20;
1737 l_interface_cols_tbl(3).UPLOAD_PARAM_LIST_ITEM_NUM := 2;
1738 l_interface_cols_tbl(3).SEQUENCE_NUM := 3;
1739 l_interface_cols_tbl(3).LOV_TYPE := 'STANDARD';
1740 l_interface_cols_tbl(3).OFFLINE_LOV_ENABLED_FLAG := 'Y';
1741 l_interface_cols_tbl(3).FND_MESSAGE_NAME := 'FEM_ADI_LEDGER_NAME';
1742 l_interface_cols_tbl(3).USER_HINT_FND_MESSAGE_NAME := 'FEM_ADI_USER_HINT_LOV_REQ';
1743
1744 -----------------------------------------------------------------------------
1745 -- Set up for column P_CAL_PERIOD
1746 -----------------------------------------------------------------------------
1747 l_interface_cols_tbl(4).INTERFACE_COL_NAME := 'P_CAL_PERIOD';
1748 l_interface_cols_tbl(4).INTERFACE_COL_TYPE := 1;
1749 l_interface_cols_tbl(4).DISPLAY_FLAG := 'Y';
1750 l_interface_cols_tbl(4).READ_ONLY_FLAG := 'N';
1751 l_interface_cols_tbl(4).MAPPING_ENABLED_FLAG := 'N';
1752 l_interface_cols_tbl(4).DATA_TYPE := 2; --VARCHAR
1753 l_interface_cols_tbl(4).FIELD_SIZE := 150;
1754 l_interface_cols_tbl(4).DEFAULT_VALUE := NULL;
1755 l_interface_cols_tbl(4).DEFAULT_TYPE := NULL;
1756 l_interface_cols_tbl(4).SEGMENT_VALUE := 3;
1757 l_interface_cols_tbl(4).GROUP_NAME := NULL; -- 'TABLE_GROUP_VALIDATOR';
1758 l_interface_cols_tbl(4).VAL_TYPE := 'JAVA';
1759 l_interface_cols_tbl(4).VAL_ID_COL := NULL;
1760 l_interface_cols_tbl(4).VAL_MEAN_COL := NULL;
1761 l_interface_cols_tbl(4).VAL_DESC_COL := NULL;
1762 l_interface_cols_tbl(4).VAL_OBJ_NAME := 'oracle.apps.fem.integrator.tables.validators.FemMemberValidator';
1763 l_interface_cols_tbl(4).VAL_ADDL_W_C := NULL;
1764 l_interface_cols_tbl(4).VAL_COMPONENT_APP_ID := 274;
1765 l_interface_cols_tbl(4).VAL_COMPONENT_CODE := 'FEM_DIMENSION_MEMBER';
1766 l_interface_cols_tbl(4).DISPLAY_ORDER := 30;
1767 l_interface_cols_tbl(4).UPLOAD_PARAM_LIST_ITEM_NUM := 3;
1768 l_interface_cols_tbl(4).SEQUENCE_NUM := 4;
1769 l_interface_cols_tbl(4).LOV_TYPE := 'STANDARD';
1770 l_interface_cols_tbl(4).OFFLINE_LOV_ENABLED_FLAG := 'Y';
1771 l_interface_cols_tbl(4).FND_MESSAGE_NAME := 'FEM_ADI_CAL_PERIOD_NAME';
1772 l_interface_cols_tbl(4).USER_HINT_FND_MESSAGE_NAME := 'FEM_ADI_USER_HINT_LOV_REQ';
1773
1774 -----------------------------------------------------------------------------
1775 -- Set up for column P_DATASET_DISPLAY_CODE
1776 -----------------------------------------------------------------------------
1777 l_interface_cols_tbl(5).INTERFACE_COL_NAME := 'P_DATASET_CODE';
1778 l_interface_cols_tbl(5).INTERFACE_COL_TYPE := 1;
1779 l_interface_cols_tbl(5).DISPLAY_FLAG := 'Y';
1780 l_interface_cols_tbl(5).READ_ONLY_FLAG := 'N';
1781 l_interface_cols_tbl(5).MAPPING_ENABLED_FLAG := 'N';
1782 l_interface_cols_tbl(5).DATA_TYPE := 2; --VARCHAR
1783 l_interface_cols_tbl(5).FIELD_SIZE := 150;
1784 l_interface_cols_tbl(5).DEFAULT_VALUE := NULL;
1785 l_interface_cols_tbl(5).DEFAULT_TYPE := NULL;
1786 l_interface_cols_tbl(5).SEGMENT_VALUE := 4;
1787 l_interface_cols_tbl(5).GROUP_NAME := NULL; -- 'TABLE_GROUP_VALIDATOR';
1788 l_interface_cols_tbl(5).VAL_TYPE := 'TABLE';
1789 l_interface_cols_tbl(5).VAL_ID_COL := 'DATASET_CODE';
1790 l_interface_cols_tbl(5).VAL_MEAN_COL := 'DATASET_NAME';
1791 l_interface_cols_tbl(5).VAL_DESC_COL := 'DESCRIPTION';
1792 l_interface_cols_tbl(5).VAL_OBJ_NAME := 'FEM_DATASETS_VL';
1793 l_interface_cols_tbl(5).VAL_ADDL_W_C := 'ENABLED_FLAG = ''Y'' AND PERSONAL_FLAG = ''N''';
1794 l_interface_cols_tbl(5).VAL_COMPONENT_APP_ID := 274;
1795 l_interface_cols_tbl(5).VAL_COMPONENT_CODE := 'FEM_DATASET';
1796 l_interface_cols_tbl(5).DISPLAY_ORDER := 40;
1797 l_interface_cols_tbl(5).UPLOAD_PARAM_LIST_ITEM_NUM := 4;
1798 l_interface_cols_tbl(5).SEQUENCE_NUM := 5;
1799 l_interface_cols_tbl(5).LOV_TYPE := 'STANDARD';
1800 l_interface_cols_tbl(5).OFFLINE_LOV_ENABLED_FLAG := 'Y';
1801 l_interface_cols_tbl(5).FND_MESSAGE_NAME := 'FEM_ADI_DATASET_NAME';
1802 l_interface_cols_tbl(5).USER_HINT_FND_MESSAGE_NAME := 'FEM_ADI_USER_HINT_LOV_REQ';
1803
1804 -----------------------------------------------------------------------------
1805 -- Set up for column P_SOURCE_SYSTEM_DISPLAY_CODE
1806 -----------------------------------------------------------------------------
1807 l_interface_cols_tbl(6).INTERFACE_COL_NAME := 'P_SOURCE_SYSTEM_DISPLAY_CODE';
1808 l_interface_cols_tbl(6).INTERFACE_COL_TYPE := 1;
1809 l_interface_cols_tbl(6).DISPLAY_FLAG := 'Y';
1810 l_interface_cols_tbl(6).READ_ONLY_FLAG := 'N';
1811 l_interface_cols_tbl(6).MAPPING_ENABLED_FLAG := 'N';
1812 l_interface_cols_tbl(6).DATA_TYPE := 2; --VARCHAR
1813 l_interface_cols_tbl(6).FIELD_SIZE := 150;
1814 l_interface_cols_tbl(6).DEFAULT_VALUE := NULL;
1815 l_interface_cols_tbl(6).DEFAULT_TYPE := NULL;
1816 l_interface_cols_tbl(6).SEGMENT_VALUE := 5;
1817 l_interface_cols_tbl(6).GROUP_NAME := NULL; -- 'TABLE_GROUP_VALIDATOR';
1818 l_interface_cols_tbl(6).VAL_TYPE := 'TABLE';
1819 l_interface_cols_tbl(6).VAL_ID_COL := 'SOURCE_SYSTEM_DISPLAY_CODE';
1820 l_interface_cols_tbl(6).VAL_MEAN_COL := 'SOURCE_SYSTEM_NAME';
1821 l_interface_cols_tbl(6).VAL_DESC_COL := 'DESCRIPTION';
1822 l_interface_cols_tbl(6).VAL_OBJ_NAME := 'FEM_SOURCE_SYSTEMS_VL';
1823 l_interface_cols_tbl(6).VAL_ADDL_W_C := 'ENABLED_FLAG = ''Y'' AND PERSONAL_FLAG = ''N''';
1824 l_interface_cols_tbl(6).VAL_COMPONENT_APP_ID := 274;
1825 l_interface_cols_tbl(6).VAL_COMPONENT_CODE := 'FEM_SOURCE_SYSTEM';
1826 l_interface_cols_tbl(6).DISPLAY_ORDER := 50;
1827 l_interface_cols_tbl(6).UPLOAD_PARAM_LIST_ITEM_NUM := 5;
1828 l_interface_cols_tbl(6).SEQUENCE_NUM := 6;
1829 l_interface_cols_tbl(6).LOV_TYPE := 'STANDARD';
1830 l_interface_cols_tbl(6).OFFLINE_LOV_ENABLED_FLAG := 'Y';
1831 l_interface_cols_tbl(6).FND_MESSAGE_NAME := 'FEM_ADI_SOURCE_SYSTEM_NAME';
1832 l_interface_cols_tbl(6).USER_HINT_FND_MESSAGE_NAME := 'FEM_ADI_USER_HINT_LOV_REQ';
1833
1834 -----------------------------------------------------------------------------
1835 -- Inserting into BNE_INTERFACE_COLS and BNE_INTERFACE_COLS_TL
1836 -----------------------------------------------------------------------------
1837 FOR i IN l_interface_cols_tbl.FIRST .. l_interface_cols_tbl.LAST
1838 LOOP
1839 INSERT INTO BNE_INTERFACE_COLS_B (
1840 INTERFACE_COL_TYPE,
1841 INTERFACE_COL_NAME,
1842 ENABLED_FLAG,
1843 REQUIRED_FLAG,
1844 DISPLAY_FLAG,
1845 READ_ONLY_FLAG,
1846 NOT_NULL_FLAG,
1847 SUMMARY_FLAG,
1848 MAPPING_ENABLED_FLAG,
1849 DATA_TYPE,
1850 FIELD_SIZE,
1851 DEFAULT_TYPE,
1852 DEFAULT_VALUE,
1853 SEGMENT_NUMBER,
1854 GROUP_NAME,
1855 OA_FLEX_CODE,
1856 OA_CONCAT_FLEX,
1857 VAL_TYPE,
1858 VAL_ID_COL,
1859 VAL_MEAN_COL,
1860 VAL_DESC_COL,
1861 VAL_OBJ_NAME,
1862 VAL_ADDL_W_C,
1863 VAL_COMPONENT_APP_ID,
1864 VAL_COMPONENT_CODE,
1865 OA_FLEX_NUM,
1866 OA_FLEX_APPLICATION_ID,
1867 DISPLAY_ORDER,
1868 UPLOAD_PARAM_LIST_ITEM_NUM,
1869 EXPANDED_SQL_QUERY,
1870 APPLICATION_ID,
1871 INTERFACE_CODE,
1872 OBJECT_VERSION_NUMBER,
1873 SEQUENCE_NUM,
1874 LOV_TYPE,
1875 OFFLINE_LOV_ENABLED_FLAG,
1876 CREATION_DATE,
1877 CREATED_BY,
1878 LAST_UPDATE_DATE,
1879 LAST_UPDATED_BY,
1880 LAST_UPDATE_LOGIN,
1881 VARIABLE_DATA_TYPE_CLASS
1882 )
1883 VALUES
1884 ( l_interface_cols_tbl(i).INTERFACE_COL_TYPE,
1885 l_interface_cols_tbl(i).INTERFACE_COL_NAME,
1886 'Y',
1887 decode(l_interface_cols_tbl(i).INTERFACE_COL_TYPE,2,'N','Y'),
1888 l_interface_cols_tbl(i).DISPLAY_FLAG,
1889 l_interface_cols_tbl(i).READ_ONLY_FLAG,
1890 decode(l_interface_cols_tbl(i).INTERFACE_COL_TYPE,2,'N','Y'),
1891 'N',
1892 l_interface_cols_tbl(i).MAPPING_ENABLED_FLAG,
1893 l_interface_cols_tbl(i).DATA_TYPE,
1894 l_interface_cols_tbl(i).FIELD_SIZE,
1895 l_interface_cols_tbl(i).DEFAULT_TYPE,
1896 l_interface_cols_tbl(i).DEFAULT_VALUE,
1897 l_interface_cols_tbl(i).SEGMENT_VALUE,
1898 l_interface_cols_tbl(i).GROUP_NAME,
1899 NULL,
1900 NULL,
1901 l_interface_cols_tbl(i).VAL_TYPE,
1902 l_interface_cols_tbl(i).VAL_ID_COL,
1903 l_interface_cols_tbl(i).VAL_MEAN_COL,
1904 l_interface_cols_tbl(i).VAL_DESC_COL,
1905 l_interface_cols_tbl(i).VAL_OBJ_NAME,
1906 l_interface_cols_tbl(i).VAL_ADDL_W_C,
1907 l_interface_cols_tbl(i).VAL_COMPONENT_APP_ID,
1908 l_interface_cols_tbl(i).VAL_COMPONENT_CODE,
1909 NULL,
1910 NULL,
1911 l_interface_cols_tbl(i).DISPLAY_ORDER,
1912 l_interface_cols_tbl(i).UPLOAD_PARAM_LIST_ITEM_NUM,
1913 NULL,
1914 274,
1915 p_interface_code,
1916 1,
1917 l_interface_cols_tbl(i).SEQUENCE_NUM,
1918 l_interface_cols_tbl(i).LOV_TYPE,
1919 l_interface_cols_tbl(i).OFFLINE_LOV_ENABLED_FLAG,
1920 SYSDATE,
1921 l_user_id,
1922 SYSDATE,
1923 l_user_id,
1924 l_login_id,
1925 NULL
1926 );
1927
1928 INSERT INTO BNE_INTERFACE_COLS_TL (
1929 CREATED_BY,
1930 CREATION_DATE,
1931 LAST_UPDATED_BY,
1932 LAST_UPDATE_LOGIN,
1933 LAST_UPDATE_DATE,
1934 USER_HINT,
1935 PROMPT_LEFT,
1936 USER_HELP_TEXT,
1937 PROMPT_ABOVE,
1938 INTERFACE_CODE,
1939 SEQUENCE_NUM,
1940 APPLICATION_ID,
1941 LANGUAGE,
1942 SOURCE_LANG
1943 )
1944 SELECT l_user_id
1945 , SYSDATE
1946 , l_user_id
1947 , l_login_id
1948 , SYSDATE
1949 , M2.MESSAGE_TEXT
1950 , M1.MESSAGE_TEXT
1951 , NULL
1952 , M1.MESSAGE_TEXT
1953 , p_interface_code
1954 , l_interface_cols_tbl(i).SEQUENCE_NUM
1955 , 274
1956 , L.LANGUAGE_CODE
1957 , USERENV('LANG')
1958 FROM FND_NEW_MESSAGES M1,
1959 FND_NEW_MESSAGES M2,
1960 FND_LANGUAGES L
1961 WHERE L.INSTALLED_FLAG IN ('I', 'B')
1962 AND M1.MESSAGE_NAME (+)= l_interface_cols_tbl(i).FND_MESSAGE_NAME
1963 AND M1.LANGUAGE_CODE (+)= L.LANGUAGE_CODE
1964 AND M2.MESSAGE_NAME (+)= l_interface_cols_tbl(i).USER_HINT_FND_MESSAGE_NAME
1965 AND M2.LANGUAGE_CODE (+)= L.LANGUAGE_CODE;
1966
1967 END LOOP;
1968
1969 END POPULATE_INTERFACE_COMM_COLS;
1970
1971 PROCEDURE POPULATE_TABLE_COLUMN_MAPS(P_TABLE_NAME IN VARCHAR2)
1972 IS
1973
1974 l_bne_interface_col_name varchar2(50);
1975 l_interface_column_name varchar2(50);
1976 l_map_exists varchar2(1);
1977 l_param_postfix number := 1;
1978 l_nullable varchar2(1);
1979 l_data_type varchar2(20);
1980
1981 cursor CUR_INTF_COLUMNS is
1982 select column_name,data_type,nullable from dba_tab_columns
1983 where owner = (select table_owner from user_synonyms where synonym_name = p_table_name)
1984 and table_name = p_table_name
1985 order by nullable asc,data_type desc,column_name asc;
1986
1987 begin
1988
1989 begin
1990 select 'Y' into l_map_exists from dual
1991 where exists(select interface_table_name from FEM_WEBADI_TABLE_COLS_MAPS where
1992 interface_table_name = p_table_name);
1993 exception
1994 when NO_DATA_FOUND then null;
1995 end;
1996
1997 if (l_map_exists = 'Y') then
1998 delete from FEM_WEBADI_TABLE_COLS_MAPS
1999 where interface_table_name = p_table_name;
2000 end if;
2001
2002 for intf_col_rec in CUR_INTF_COLUMNS
2003 loop
2004
2005 l_bne_interface_col_name := 'P_PARAM' || l_param_postfix;
2006 l_interface_column_name := intf_col_rec.column_name;
2007 l_nullable := intf_col_rec.nullable;
2008 l_data_type := intf_col_rec.data_type;
2009
2010 insert into FEM_WEBADI_TABLE_COLS_MAPS(BNE_INTERFACE_COL_NAME,INTERFACE_TABLE_NAME,INTERFACE_COLUMN_NAME,DATA_TYPE,NULLABLE)
2011 values(l_bne_interface_col_name,p_table_name,l_interface_column_name,l_data_type,l_nullable);
2012
2013 l_param_postfix := l_param_postfix + 1;
2014 end loop;
2015
2016 END POPULATE_TABLE_COLUMN_MAPS;
2017
2018 PROCEDURE UPLOAD_FEM_TABLE_INTERFACE(
2019 P_TABLE_NAME VARCHAR2,
2020 P_LEDGER_DISPLAY_CODE VARCHAR2,
2021 P_CAL_PERIOD VARCHAR2,
2022 P_DATASET_DISPLAY_CODE VARCHAR2,
2023 P_SOURCE_SYSTEM_DISPLAY_CODE VARCHAR2,
2024 P_PARAM1 IN VARCHAR2 DEFAULT NULL,
2025 P_PARAM2 IN VARCHAR2 DEFAULT NULL,
2026 P_PARAM3 IN VARCHAR2 DEFAULT NULL,
2027 P_PARAM4 IN VARCHAR2 DEFAULT NULL,
2028 P_PARAM5 IN VARCHAR2 DEFAULT NULL,
2029 P_PARAM6 IN VARCHAR2 DEFAULT NULL,
2030 P_PARAM7 IN VARCHAR2 DEFAULT NULL,
2031 P_PARAM8 IN VARCHAR2 DEFAULT NULL,
2032 P_PARAM9 IN VARCHAR2 DEFAULT NULL,
2033 P_PARAM10 IN VARCHAR2 DEFAULT NULL,
2034 P_PARAM11 IN VARCHAR2 DEFAULT NULL,
2035 P_PARAM12 IN VARCHAR2 DEFAULT NULL,
2036 P_PARAM13 IN VARCHAR2 DEFAULT NULL,
2037 P_PARAM14 IN VARCHAR2 DEFAULT NULL,
2038 P_PARAM15 IN VARCHAR2 DEFAULT NULL,
2039 P_PARAM16 IN VARCHAR2 DEFAULT NULL,
2040 P_PARAM17 IN VARCHAR2 DEFAULT NULL,
2041 P_PARAM18 IN VARCHAR2 DEFAULT NULL,
2042 P_PARAM19 IN VARCHAR2 DEFAULT NULL,
2043 P_PARAM20 IN VARCHAR2 DEFAULT NULL,
2044 P_PARAM21 IN VARCHAR2 DEFAULT NULL,
2045 P_PARAM22 IN VARCHAR2 DEFAULT NULL,
2046 P_PARAM23 IN VARCHAR2 DEFAULT NULL,
2047 P_PARAM24 IN VARCHAR2 DEFAULT NULL,
2048 P_PARAM25 IN VARCHAR2 DEFAULT NULL,
2049 P_PARAM26 IN VARCHAR2 DEFAULT NULL,
2050 P_PARAM27 IN VARCHAR2 DEFAULT NULL,
2051 P_PARAM28 IN VARCHAR2 DEFAULT NULL,
2052 P_PARAM29 IN VARCHAR2 DEFAULT NULL,
2053 P_PARAM30 IN VARCHAR2 DEFAULT NULL,
2054 P_PARAM31 IN VARCHAR2 DEFAULT NULL,
2055 P_PARAM32 IN VARCHAR2 DEFAULT NULL,
2056 P_PARAM33 IN VARCHAR2 DEFAULT NULL,
2057 P_PARAM34 IN VARCHAR2 DEFAULT NULL,
2058 P_PARAM35 IN VARCHAR2 DEFAULT NULL,
2059 P_PARAM36 IN VARCHAR2 DEFAULT NULL,
2060 P_PARAM37 IN VARCHAR2 DEFAULT NULL,
2061 P_PARAM38 IN VARCHAR2 DEFAULT NULL,
2062 P_PARAM39 IN VARCHAR2 DEFAULT NULL,
2063 P_PARAM40 IN VARCHAR2 DEFAULT NULL,
2064 P_PARAM41 IN VARCHAR2 DEFAULT NULL,
2065 P_PARAM42 IN VARCHAR2 DEFAULT NULL,
2066 P_PARAM43 IN VARCHAR2 DEFAULT NULL,
2067 P_PARAM44 IN VARCHAR2 DEFAULT NULL,
2068 P_PARAM45 IN VARCHAR2 DEFAULT NULL,
2069 P_PARAM46 IN VARCHAR2 DEFAULT NULL,
2070 P_PARAM47 IN VARCHAR2 DEFAULT NULL,
2071 P_PARAM48 IN VARCHAR2 DEFAULT NULL,
2072 P_PARAM49 IN VARCHAR2 DEFAULT NULL,
2073 P_PARAM50 IN VARCHAR2 DEFAULT NULL,
2074 P_PARAM51 IN VARCHAR2 DEFAULT NULL,
2075 P_PARAM52 IN VARCHAR2 DEFAULT NULL,
2076 P_PARAM53 IN VARCHAR2 DEFAULT NULL,
2077 P_PARAM54 IN VARCHAR2 DEFAULT NULL,
2078 P_PARAM55 IN VARCHAR2 DEFAULT NULL,
2079 P_PARAM56 IN VARCHAR2 DEFAULT NULL,
2080 P_PARAM57 IN VARCHAR2 DEFAULT NULL,
2081 P_PARAM58 IN VARCHAR2 DEFAULT NULL,
2082 P_PARAM59 IN VARCHAR2 DEFAULT NULL,
2083 P_PARAM60 IN VARCHAR2 DEFAULT NULL,
2084 P_PARAM61 IN VARCHAR2 DEFAULT NULL,
2085 P_PARAM62 IN VARCHAR2 DEFAULT NULL,
2086 P_PARAM63 IN VARCHAR2 DEFAULT NULL,
2087 P_PARAM64 IN VARCHAR2 DEFAULT NULL,
2088 P_PARAM65 IN VARCHAR2 DEFAULT NULL,
2089 P_PARAM66 IN VARCHAR2 DEFAULT NULL,
2090 P_PARAM67 IN VARCHAR2 DEFAULT NULL,
2091 P_PARAM68 IN VARCHAR2 DEFAULT NULL,
2092 P_PARAM69 IN VARCHAR2 DEFAULT NULL,
2093 P_PARAM70 IN VARCHAR2 DEFAULT NULL,
2094 P_PARAM71 IN VARCHAR2 DEFAULT NULL,
2095 P_PARAM72 IN VARCHAR2 DEFAULT NULL,
2096 P_PARAM73 IN VARCHAR2 DEFAULT NULL,
2097 P_PARAM74 IN VARCHAR2 DEFAULT NULL,
2098 P_PARAM75 IN VARCHAR2 DEFAULT NULL,
2099 P_PARAM76 IN VARCHAR2 DEFAULT NULL,
2100 P_PARAM77 IN VARCHAR2 DEFAULT NULL,
2101 P_PARAM78 IN VARCHAR2 DEFAULT NULL,
2102 P_PARAM79 IN VARCHAR2 DEFAULT NULL,
2103 P_PARAM80 IN VARCHAR2 DEFAULT NULL,
2104 P_PARAM81 IN VARCHAR2 DEFAULT NULL,
2105 P_PARAM82 IN VARCHAR2 DEFAULT NULL,
2106 P_PARAM83 IN VARCHAR2 DEFAULT NULL,
2107 P_PARAM84 IN VARCHAR2 DEFAULT NULL,
2108 P_PARAM85 IN VARCHAR2 DEFAULT NULL,
2109 P_PARAM86 IN VARCHAR2 DEFAULT NULL,
2110 P_PARAM87 IN VARCHAR2 DEFAULT NULL,
2111 P_PARAM88 IN VARCHAR2 DEFAULT NULL,
2112 P_PARAM89 IN VARCHAR2 DEFAULT NULL,
2113 P_PARAM90 IN VARCHAR2 DEFAULT NULL,
2114 P_PARAM91 IN VARCHAR2 DEFAULT NULL,
2115 P_PARAM92 IN VARCHAR2 DEFAULT NULL,
2116 P_PARAM93 IN VARCHAR2 DEFAULT NULL,
2117 P_PARAM94 IN VARCHAR2 DEFAULT NULL,
2118 P_PARAM95 IN VARCHAR2 DEFAULT NULL,
2119 P_PARAM96 IN VARCHAR2 DEFAULT NULL,
2120 P_PARAM97 IN VARCHAR2 DEFAULT NULL,
2121 P_PARAM98 IN VARCHAR2 DEFAULT NULL,
2122 P_PARAM99 IN VARCHAR2 DEFAULT NULL,
2123 P_PARAM100 IN VARCHAR2 DEFAULT NULL,
2124 P_PARAM101 IN VARCHAR2 DEFAULT NULL,
2125 P_PARAM102 IN VARCHAR2 DEFAULT NULL,
2126 P_PARAM103 IN VARCHAR2 DEFAULT NULL,
2127 P_PARAM104 IN VARCHAR2 DEFAULT NULL,
2128 P_PARAM105 IN VARCHAR2 DEFAULT NULL,
2129 P_PARAM106 IN VARCHAR2 DEFAULT NULL,
2130 P_PARAM107 IN VARCHAR2 DEFAULT NULL,
2131 P_PARAM108 IN VARCHAR2 DEFAULT NULL,
2132 P_PARAM109 IN VARCHAR2 DEFAULT NULL,
2133 P_PARAM110 IN VARCHAR2 DEFAULT NULL,
2134 P_PARAM111 IN VARCHAR2 DEFAULT NULL,
2135 P_PARAM112 IN VARCHAR2 DEFAULT NULL,
2136 P_PARAM113 IN VARCHAR2 DEFAULT NULL,
2137 P_PARAM114 IN VARCHAR2 DEFAULT NULL,
2138 P_PARAM115 IN VARCHAR2 DEFAULT NULL,
2139 P_PARAM116 IN VARCHAR2 DEFAULT NULL,
2140 P_PARAM117 IN VARCHAR2 DEFAULT NULL,
2141 P_PARAM118 IN VARCHAR2 DEFAULT NULL,
2142 P_PARAM119 IN VARCHAR2 DEFAULT NULL,
2143 P_PARAM120 IN VARCHAR2 DEFAULT NULL,
2144 P_PARAM121 IN VARCHAR2 DEFAULT NULL,
2145 P_PARAM122 IN VARCHAR2 DEFAULT NULL,
2146 P_PARAM123 IN VARCHAR2 DEFAULT NULL,
2147 P_PARAM124 IN VARCHAR2 DEFAULT NULL,
2148 P_PARAM125 IN VARCHAR2 DEFAULT NULL,
2149 P_PARAM126 IN VARCHAR2 DEFAULT NULL,
2150 P_PARAM127 IN VARCHAR2 DEFAULT NULL,
2151 P_PARAM128 IN VARCHAR2 DEFAULT NULL,
2152 P_PARAM129 IN VARCHAR2 DEFAULT NULL,
2153 P_PARAM130 IN VARCHAR2 DEFAULT NULL,
2154 P_PARAM131 IN VARCHAR2 DEFAULT NULL,
2155 P_PARAM132 IN VARCHAR2 DEFAULT NULL,
2156 P_PARAM133 IN VARCHAR2 DEFAULT NULL,
2157 P_PARAM134 IN VARCHAR2 DEFAULT NULL,
2158 P_PARAM135 IN VARCHAR2 DEFAULT NULL,
2159 P_PARAM136 IN VARCHAR2 DEFAULT NULL,
2160 P_PARAM137 IN VARCHAR2 DEFAULT NULL,
2161 P_PARAM138 IN VARCHAR2 DEFAULT NULL,
2162 P_PARAM139 IN VARCHAR2 DEFAULT NULL,
2163 P_PARAM140 IN VARCHAR2 DEFAULT NULL,
2164 P_PARAM141 IN VARCHAR2 DEFAULT NULL,
2165 P_PARAM142 IN VARCHAR2 DEFAULT NULL,
2166 P_PARAM143 IN VARCHAR2 DEFAULT NULL,
2167 P_PARAM144 IN VARCHAR2 DEFAULT NULL,
2168 P_PARAM145 IN VARCHAR2 DEFAULT NULL,
2169 P_PARAM146 IN VARCHAR2 DEFAULT NULL,
2170 P_PARAM147 IN VARCHAR2 DEFAULT NULL,
2171 P_PARAM148 IN VARCHAR2 DEFAULT NULL,
2172 P_PARAM149 IN VARCHAR2 DEFAULT NULL,
2173 P_PARAM150 IN VARCHAR2 DEFAULT NULL,
2174 P_PARAM151 IN VARCHAR2 DEFAULT NULL,
2175 P_PARAM152 IN VARCHAR2 DEFAULT NULL,
2176 P_PARAM153 IN VARCHAR2 DEFAULT NULL,
2177 P_PARAM154 IN VARCHAR2 DEFAULT NULL,
2178 P_PARAM155 IN VARCHAR2 DEFAULT NULL,
2179 P_PARAM156 IN VARCHAR2 DEFAULT NULL,
2180 P_PARAM157 IN VARCHAR2 DEFAULT NULL,
2181 P_PARAM158 IN VARCHAR2 DEFAULT NULL,
2182 P_PARAM159 IN VARCHAR2 DEFAULT NULL,
2183 P_PARAM160 IN VARCHAR2 DEFAULT NULL,
2184 P_PARAM161 IN VARCHAR2 DEFAULT NULL,
2185 P_PARAM162 IN VARCHAR2 DEFAULT NULL,
2186 P_PARAM163 IN VARCHAR2 DEFAULT NULL,
2187 P_PARAM164 IN VARCHAR2 DEFAULT NULL,
2188 P_PARAM165 IN VARCHAR2 DEFAULT NULL,
2189 P_PARAM166 IN VARCHAR2 DEFAULT NULL,
2190 P_PARAM167 IN VARCHAR2 DEFAULT NULL,
2191 P_PARAM168 IN VARCHAR2 DEFAULT NULL,
2192 P_PARAM169 IN VARCHAR2 DEFAULT NULL,
2193 P_PARAM170 IN VARCHAR2 DEFAULT NULL,
2194 P_PARAM171 IN VARCHAR2 DEFAULT NULL,
2195 P_PARAM172 IN VARCHAR2 DEFAULT NULL,
2196 P_PARAM173 IN VARCHAR2 DEFAULT NULL,
2197 P_PARAM174 IN VARCHAR2 DEFAULT NULL,
2198 P_PARAM175 IN VARCHAR2 DEFAULT NULL,
2199 P_PARAM176 IN VARCHAR2 DEFAULT NULL,
2200 P_PARAM177 IN VARCHAR2 DEFAULT NULL,
2201 P_PARAM178 IN VARCHAR2 DEFAULT NULL,
2202 P_PARAM179 IN VARCHAR2 DEFAULT NULL,
2203 P_PARAM180 IN VARCHAR2 DEFAULT NULL,
2204 P_PARAM181 IN VARCHAR2 DEFAULT NULL,
2205 P_PARAM182 IN VARCHAR2 DEFAULT NULL,
2206 P_PARAM183 IN VARCHAR2 DEFAULT NULL,
2207 P_PARAM184 IN VARCHAR2 DEFAULT NULL,
2208 P_PARAM185 IN VARCHAR2 DEFAULT NULL,
2209 P_PARAM186 IN VARCHAR2 DEFAULT NULL,
2210 P_PARAM187 IN VARCHAR2 DEFAULT NULL,
2211 P_PARAM188 IN VARCHAR2 DEFAULT NULL,
2212 P_PARAM189 IN VARCHAR2 DEFAULT NULL,
2213 P_PARAM190 IN VARCHAR2 DEFAULT NULL,
2214 P_PARAM191 IN VARCHAR2 DEFAULT NULL,
2215 P_PARAM192 IN VARCHAR2 DEFAULT NULL,
2216 P_PARAM193 IN VARCHAR2 DEFAULT NULL,
2217 P_PARAM194 IN VARCHAR2 DEFAULT NULL,
2218 P_PARAM195 IN VARCHAR2 DEFAULT NULL,
2219 P_PARAM196 IN VARCHAR2 DEFAULT NULL,
2220 P_PARAM197 IN VARCHAR2 DEFAULT NULL,
2221 P_PARAM198 IN VARCHAR2 DEFAULT NULL,
2222 P_PARAM199 IN VARCHAR2 DEFAULT NULL,
2223 P_PARAM200 IN VARCHAR2 DEFAULT NULL,
2224 P_PARAM201 IN VARCHAR2 DEFAULT NULL,
2225 P_PARAM202 IN VARCHAR2 DEFAULT NULL,
2226 P_PARAM203 IN VARCHAR2 DEFAULT NULL,
2227 P_PARAM204 IN VARCHAR2 DEFAULT NULL,
2228 P_PARAM205 IN VARCHAR2 DEFAULT NULL,
2229 P_PARAM206 IN VARCHAR2 DEFAULT NULL,
2230 P_PARAM207 IN VARCHAR2 DEFAULT NULL,
2231 P_PARAM208 IN VARCHAR2 DEFAULT NULL,
2232 P_PARAM209 IN VARCHAR2 DEFAULT NULL,
2233 P_PARAM210 IN VARCHAR2 DEFAULT NULL,
2234 P_PARAM211 IN VARCHAR2 DEFAULT NULL,
2235 P_PARAM212 IN VARCHAR2 DEFAULT NULL,
2236 P_PARAM213 IN VARCHAR2 DEFAULT NULL,
2237 P_PARAM214 IN VARCHAR2 DEFAULT NULL,
2238 P_PARAM215 IN VARCHAR2 DEFAULT NULL,
2239 P_PARAM216 IN VARCHAR2 DEFAULT NULL,
2240 P_PARAM217 IN VARCHAR2 DEFAULT NULL,
2241 P_PARAM218 IN VARCHAR2 DEFAULT NULL,
2242 P_PARAM219 IN VARCHAR2 DEFAULT NULL,
2243 P_PARAM220 IN VARCHAR2 DEFAULT NULL,
2244 P_PARAM221 IN VARCHAR2 DEFAULT NULL,
2245 P_PARAM222 IN VARCHAR2 DEFAULT NULL,
2246 P_PARAM223 IN VARCHAR2 DEFAULT NULL,
2247 P_PARAM224 IN VARCHAR2 DEFAULT NULL,
2248 P_PARAM225 IN VARCHAR2 DEFAULT NULL,
2249 P_PARAM226 IN VARCHAR2 DEFAULT NULL,
2250 P_PARAM227 IN VARCHAR2 DEFAULT NULL,
2251 P_PARAM228 IN VARCHAR2 DEFAULT NULL,
2252 P_PARAM229 IN VARCHAR2 DEFAULT NULL,
2253 P_PARAM230 IN VARCHAR2 DEFAULT NULL,
2254 P_PARAM231 IN VARCHAR2 DEFAULT NULL,
2255 P_PARAM232 IN VARCHAR2 DEFAULT NULL,
2256 P_PARAM233 IN VARCHAR2 DEFAULT NULL,
2257 P_PARAM234 IN VARCHAR2 DEFAULT NULL,
2258 P_PARAM235 IN VARCHAR2 DEFAULT NULL,
2259 P_PARAM236 IN VARCHAR2 DEFAULT NULL,
2260 P_PARAM237 IN VARCHAR2 DEFAULT NULL,
2261 P_PARAM238 IN VARCHAR2 DEFAULT NULL,
2262 P_PARAM239 IN VARCHAR2 DEFAULT NULL,
2263 P_PARAM240 IN VARCHAR2 DEFAULT NULL,
2264 P_PARAM241 IN VARCHAR2 DEFAULT NULL,
2265 P_PARAM242 IN VARCHAR2 DEFAULT NULL,
2266 P_PARAM243 IN VARCHAR2 DEFAULT NULL,
2267 P_PARAM244 IN VARCHAR2 DEFAULT NULL,
2268 P_PARAM245 IN VARCHAR2 DEFAULT NULL,
2269 P_PARAM246 IN VARCHAR2 DEFAULT NULL,
2270 P_PARAM247 IN VARCHAR2 DEFAULT NULL,
2271 P_PARAM248 IN VARCHAR2 DEFAULT NULL,
2272 P_PARAM249 IN VARCHAR2 DEFAULT NULL,
2273 P_PARAM250 IN VARCHAR2 DEFAULT NULL,
2274 P_PARAM251 IN VARCHAR2 DEFAULT NULL,
2275 P_PARAM252 IN VARCHAR2 DEFAULT NULL,
2276 P_PARAM253 IN VARCHAR2 DEFAULT NULL,
2277 P_PARAM254 IN VARCHAR2 DEFAULT NULL,
2278 P_PARAM255 IN VARCHAR2 DEFAULT NULL,
2279 P_PARAM256 IN VARCHAR2 DEFAULT NULL,
2280 P_PARAM257 IN VARCHAR2 DEFAULT NULL,
2281 P_PARAM258 IN VARCHAR2 DEFAULT NULL,
2282 P_PARAM259 IN VARCHAR2 DEFAULT NULL,
2283 P_PARAM260 IN VARCHAR2 DEFAULT NULL,
2284 P_PARAM261 IN VARCHAR2 DEFAULT NULL,
2285 P_PARAM262 IN VARCHAR2 DEFAULT NULL,
2286 P_PARAM263 IN VARCHAR2 DEFAULT NULL,
2287 P_PARAM264 IN VARCHAR2 DEFAULT NULL,
2288 P_PARAM265 IN VARCHAR2 DEFAULT NULL,
2289 P_PARAM266 IN VARCHAR2 DEFAULT NULL,
2290 P_PARAM267 IN VARCHAR2 DEFAULT NULL,
2291 P_PARAM268 IN VARCHAR2 DEFAULT NULL,
2292 P_PARAM269 IN VARCHAR2 DEFAULT NULL,
2293 P_PARAM270 IN VARCHAR2 DEFAULT NULL,
2294 P_PARAM271 IN VARCHAR2 DEFAULT NULL,
2295 P_PARAM272 IN VARCHAR2 DEFAULT NULL,
2296 P_PARAM273 IN VARCHAR2 DEFAULT NULL,
2297 P_PARAM274 IN VARCHAR2 DEFAULT NULL,
2298 P_PARAM275 IN VARCHAR2 DEFAULT NULL,
2299 P_PARAM276 IN VARCHAR2 DEFAULT NULL,
2300 P_PARAM277 IN VARCHAR2 DEFAULT NULL,
2301 P_PARAM278 IN VARCHAR2 DEFAULT NULL,
2302 P_PARAM279 IN VARCHAR2 DEFAULT NULL,
2303 P_PARAM280 IN VARCHAR2 DEFAULT NULL,
2304 P_PARAM281 IN VARCHAR2 DEFAULT NULL,
2305 P_PARAM282 IN VARCHAR2 DEFAULT NULL,
2306 P_PARAM283 IN VARCHAR2 DEFAULT NULL,
2307 P_PARAM284 IN VARCHAR2 DEFAULT NULL,
2308 P_PARAM285 IN VARCHAR2 DEFAULT NULL,
2309 P_PARAM286 IN VARCHAR2 DEFAULT NULL,
2310 P_PARAM287 IN VARCHAR2 DEFAULT NULL,
2311 P_PARAM288 IN VARCHAR2 DEFAULT NULL,
2312 P_PARAM289 IN VARCHAR2 DEFAULT NULL,
2313 P_PARAM290 IN VARCHAR2 DEFAULT NULL,
2314 P_PARAM291 IN VARCHAR2 DEFAULT NULL,
2315 P_PARAM292 IN VARCHAR2 DEFAULT NULL,
2316 P_PARAM293 IN VARCHAR2 DEFAULT NULL,
2317 P_PARAM294 IN VARCHAR2 DEFAULT NULL,
2318 P_PARAM295 IN VARCHAR2 DEFAULT NULL,
2319 P_PARAM296 IN VARCHAR2 DEFAULT NULL,
2320 P_PARAM297 IN VARCHAR2 DEFAULT NULL,
2321 P_PARAM298 IN VARCHAR2 DEFAULT NULL,
2322 P_PARAM299 IN VARCHAR2 DEFAULT NULL,
2323 P_PARAM300 IN VARCHAR2 DEFAULT NULL)
2324 IS
2325
2326 l_interface_table_name varchar2(50);
2327 l_insert_query long;
2328 l_query_values long;
2329 l_query_columns long;
2330 l_dataset_display_code varchar2(100);
2331 l_cal_period_number number;
2332 l_cal_period_end_date date;
2333 l_cal_grp_display_code varchar2(50);
2334 l_interface_code varchar2(50);
2335 l_count number := 1;
2336 l_param_number number;
2337 l_column_value varchar2(500);
2338 l_table_name varchar2(30);
2339 e_no_table exception;
2340 l_canonical_format varchar2(20);
2341 l_end_date varchar2(20);
2342 l_adi_date_format varchar2(20);
2343 INVALID_NUMBER exception;
2344 pragma exception_init(INVALID_NUMBER,-01722);
2345 l_column_name varchar2(100);
2346 l_valid_number number;
2347
2348 TYPE l_param_value IS RECORD
2349 ( VALUE VARCHAR2(500));
2350
2351 TYPE l_param_values_typ IS TABLE OF l_param_value
2352 INDEX BY BINARY_INTEGER;
2353
2354 l_param_values_tbl l_param_values_typ;
2355
2356 begin
2357
2358 if(p_table_name is null) then
2359 raise e_no_table;
2360 end if;
2361
2362 --------------------------------- Inserting PARAM values in a PLSQL TABLE ----------------------
2363
2364 l_param_values_tbl(1).VALUE := P_PARAM1 ;
2365 l_param_values_tbl(2).VALUE := P_PARAM2 ;
2366 l_param_values_tbl(3).VALUE := P_PARAM3 ;
2367 l_param_values_tbl(4).VALUE := P_PARAM4 ;
2368 l_param_values_tbl(5).VALUE := P_PARAM5 ;
2369 l_param_values_tbl(6).VALUE := P_PARAM6 ;
2370 l_param_values_tbl(7).VALUE := P_PARAM7 ;
2371 l_param_values_tbl(8).VALUE := P_PARAM8 ;
2372 l_param_values_tbl(9).VALUE := P_PARAM9 ;
2373 l_param_values_tbl(10).VALUE := P_PARAM10 ;
2374 l_param_values_tbl(11).VALUE := P_PARAM11 ;
2375 l_param_values_tbl(12).VALUE := P_PARAM12 ;
2376 l_param_values_tbl(13).VALUE := P_PARAM13 ;
2377 l_param_values_tbl(14).VALUE := P_PARAM14 ;
2378 l_param_values_tbl(15).VALUE := P_PARAM15 ;
2379 l_param_values_tbl(16).VALUE := P_PARAM16 ;
2380 l_param_values_tbl(17).VALUE := P_PARAM17 ;
2381 l_param_values_tbl(18).VALUE := P_PARAM18 ;
2382 l_param_values_tbl(19).VALUE := P_PARAM19 ;
2383 l_param_values_tbl(20).VALUE := P_PARAM20 ;
2384 l_param_values_tbl(21).VALUE := P_PARAM21 ;
2385 l_param_values_tbl(22).VALUE := P_PARAM22 ;
2386 l_param_values_tbl(23).VALUE := P_PARAM23 ;
2387 l_param_values_tbl(24).VALUE := P_PARAM24 ;
2388 l_param_values_tbl(25).VALUE := P_PARAM25 ;
2389 l_param_values_tbl(26).VALUE := P_PARAM26 ;
2390 l_param_values_tbl(27).VALUE := P_PARAM27 ;
2391 l_param_values_tbl(28).VALUE := P_PARAM28 ;
2392 l_param_values_tbl(29).VALUE := P_PARAM29 ;
2393 l_param_values_tbl(30).VALUE := P_PARAM30 ;
2394 l_param_values_tbl(31).VALUE := P_PARAM31 ;
2395 l_param_values_tbl(32).VALUE := P_PARAM32 ;
2396 l_param_values_tbl(33).VALUE := P_PARAM33 ;
2397 l_param_values_tbl(34).VALUE := P_PARAM34 ;
2398 l_param_values_tbl(35).VALUE := P_PARAM35 ;
2399 l_param_values_tbl(36).VALUE := P_PARAM36 ;
2400 l_param_values_tbl(37).VALUE := P_PARAM37 ;
2401 l_param_values_tbl(38).VALUE := P_PARAM38 ;
2402 l_param_values_tbl(39).VALUE := P_PARAM39 ;
2403 l_param_values_tbl(40).VALUE := P_PARAM40 ;
2404 l_param_values_tbl(41).VALUE := P_PARAM41 ;
2405 l_param_values_tbl(42).VALUE := P_PARAM42 ;
2406 l_param_values_tbl(43).VALUE := P_PARAM43 ;
2407 l_param_values_tbl(44).VALUE := P_PARAM44 ;
2408 l_param_values_tbl(45).VALUE := P_PARAM45 ;
2409 l_param_values_tbl(46).VALUE := P_PARAM46 ;
2410 l_param_values_tbl(47).VALUE := P_PARAM47 ;
2411 l_param_values_tbl(48).VALUE := P_PARAM48 ;
2412 l_param_values_tbl(49).VALUE := P_PARAM49 ;
2413 l_param_values_tbl(50).VALUE := P_PARAM50 ;
2414 l_param_values_tbl(51).VALUE := P_PARAM51 ;
2415 l_param_values_tbl(52).VALUE := P_PARAM52 ;
2416 l_param_values_tbl(53).VALUE := P_PARAM53 ;
2417 l_param_values_tbl(54).VALUE := P_PARAM54 ;
2418 l_param_values_tbl(55).VALUE := P_PARAM55 ;
2419 l_param_values_tbl(56).VALUE := P_PARAM56 ;
2420 l_param_values_tbl(57).VALUE := P_PARAM57 ;
2421 l_param_values_tbl(58).VALUE := P_PARAM58 ;
2422 l_param_values_tbl(59).VALUE := P_PARAM59 ;
2423 l_param_values_tbl(60).VALUE := P_PARAM60 ;
2424 l_param_values_tbl(61).VALUE := P_PARAM61 ;
2425 l_param_values_tbl(62).VALUE := P_PARAM62 ;
2426 l_param_values_tbl(63).VALUE := P_PARAM63 ;
2427 l_param_values_tbl(64).VALUE := P_PARAM64 ;
2428 l_param_values_tbl(65).VALUE := P_PARAM65 ;
2429 l_param_values_tbl(66).VALUE := P_PARAM66 ;
2430 l_param_values_tbl(67).VALUE := P_PARAM67 ;
2431 l_param_values_tbl(68).VALUE := P_PARAM68 ;
2432 l_param_values_tbl(69).VALUE := P_PARAM69 ;
2433 l_param_values_tbl(70).VALUE := P_PARAM70 ;
2434 l_param_values_tbl(71).VALUE := P_PARAM71 ;
2435 l_param_values_tbl(72).VALUE := P_PARAM72 ;
2436 l_param_values_tbl(73).VALUE := P_PARAM73 ;
2437 l_param_values_tbl(74).VALUE := P_PARAM74 ;
2438 l_param_values_tbl(75).VALUE := P_PARAM75 ;
2439 l_param_values_tbl(76).VALUE := P_PARAM76 ;
2440 l_param_values_tbl(77).VALUE := P_PARAM77 ;
2441 l_param_values_tbl(78).VALUE := P_PARAM78 ;
2442 l_param_values_tbl(79).VALUE := P_PARAM79 ;
2443 l_param_values_tbl(80).VALUE := P_PARAM80 ;
2444 l_param_values_tbl(81).VALUE := P_PARAM81 ;
2445 l_param_values_tbl(82).VALUE := P_PARAM82 ;
2446 l_param_values_tbl(83).VALUE := P_PARAM83 ;
2447 l_param_values_tbl(84).VALUE := P_PARAM84 ;
2448 l_param_values_tbl(85).VALUE := P_PARAM85 ;
2449 l_param_values_tbl(86).VALUE := P_PARAM86 ;
2450 l_param_values_tbl(87).VALUE := P_PARAM87 ;
2451 l_param_values_tbl(88).VALUE := P_PARAM88 ;
2452 l_param_values_tbl(89).VALUE := P_PARAM89 ;
2453 l_param_values_tbl(90).VALUE := P_PARAM90 ;
2454 l_param_values_tbl(91).VALUE := P_PARAM91 ;
2455 l_param_values_tbl(92).VALUE := P_PARAM92 ;
2456 l_param_values_tbl(93).VALUE := P_PARAM93 ;
2457 l_param_values_tbl(94).VALUE := P_PARAM94 ;
2458 l_param_values_tbl(95).VALUE := P_PARAM95 ;
2459 l_param_values_tbl(96).VALUE := P_PARAM96 ;
2460 l_param_values_tbl(97).VALUE := P_PARAM97 ;
2461 l_param_values_tbl(98).VALUE := P_PARAM98 ;
2462 l_param_values_tbl(99).VALUE := P_PARAM99 ;
2463 l_param_values_tbl(100).VALUE :=P_PARAM100 ;
2464 l_param_values_tbl(101).VALUE :=P_PARAM101 ;
2465 l_param_values_tbl(102).VALUE :=P_PARAM102 ;
2466 l_param_values_tbl(103).VALUE :=P_PARAM103 ;
2467 l_param_values_tbl(104).VALUE :=P_PARAM104 ;
2468 l_param_values_tbl(105).VALUE :=P_PARAM105 ;
2469 l_param_values_tbl(106).VALUE :=P_PARAM106 ;
2470 l_param_values_tbl(107).VALUE :=P_PARAM107 ;
2471 l_param_values_tbl(108).VALUE :=P_PARAM108 ;
2472 l_param_values_tbl(109).VALUE :=P_PARAM109 ;
2473 l_param_values_tbl(110).VALUE :=P_PARAM110 ;
2474 l_param_values_tbl(111).VALUE :=P_PARAM111 ;
2475 l_param_values_tbl(112).VALUE :=P_PARAM112 ;
2476 l_param_values_tbl(113).VALUE :=P_PARAM113 ;
2477 l_param_values_tbl(114).VALUE :=P_PARAM114 ;
2478 l_param_values_tbl(115).VALUE :=P_PARAM115 ;
2479 l_param_values_tbl(116).VALUE :=P_PARAM116 ;
2480 l_param_values_tbl(117).VALUE :=P_PARAM117 ;
2481 l_param_values_tbl(118).VALUE :=P_PARAM118 ;
2482 l_param_values_tbl(119).VALUE :=P_PARAM119 ;
2483 l_param_values_tbl(120).VALUE :=P_PARAM120 ;
2484 l_param_values_tbl(121).VALUE :=P_PARAM121 ;
2485 l_param_values_tbl(122).VALUE :=P_PARAM122 ;
2486 l_param_values_tbl(123).VALUE :=P_PARAM123 ;
2487 l_param_values_tbl(124).VALUE :=P_PARAM124 ;
2488 l_param_values_tbl(125).VALUE :=P_PARAM125 ;
2489 l_param_values_tbl(126).VALUE :=P_PARAM126 ;
2490 l_param_values_tbl(127).VALUE :=P_PARAM127 ;
2491 l_param_values_tbl(128).VALUE :=P_PARAM128 ;
2492 l_param_values_tbl(129).VALUE :=P_PARAM129 ;
2493 l_param_values_tbl(130).VALUE :=P_PARAM130 ;
2494 l_param_values_tbl(131).VALUE :=P_PARAM131 ;
2495 l_param_values_tbl(132).VALUE :=P_PARAM132 ;
2496 l_param_values_tbl(133).VALUE :=P_PARAM133 ;
2497 l_param_values_tbl(134).VALUE :=P_PARAM134 ;
2498 l_param_values_tbl(135).VALUE :=P_PARAM135 ;
2499 l_param_values_tbl(136).VALUE :=P_PARAM136 ;
2500 l_param_values_tbl(137).VALUE :=P_PARAM137 ;
2501 l_param_values_tbl(138).VALUE :=P_PARAM138 ;
2502 l_param_values_tbl(139).VALUE :=P_PARAM139 ;
2503 l_param_values_tbl(140).VALUE :=P_PARAM140 ;
2504 l_param_values_tbl(141).VALUE :=P_PARAM141 ;
2505 l_param_values_tbl(142).VALUE :=P_PARAM142 ;
2506 l_param_values_tbl(143).VALUE :=P_PARAM143 ;
2507 l_param_values_tbl(144).VALUE :=P_PARAM144 ;
2508 l_param_values_tbl(145).VALUE :=P_PARAM145 ;
2509 l_param_values_tbl(146).VALUE :=P_PARAM146 ;
2510 l_param_values_tbl(147).VALUE :=P_PARAM147 ;
2511 l_param_values_tbl(148).VALUE :=P_PARAM148 ;
2512 l_param_values_tbl(149).VALUE :=P_PARAM149 ;
2513 l_param_values_tbl(150).VALUE :=P_PARAM150 ;
2514 l_param_values_tbl(151).VALUE :=P_PARAM151 ;
2515 l_param_values_tbl(152).VALUE :=P_PARAM152 ;
2516 l_param_values_tbl(153).VALUE :=P_PARAM153 ;
2517 l_param_values_tbl(154).VALUE :=P_PARAM154 ;
2518 l_param_values_tbl(155).VALUE :=P_PARAM155 ;
2519 l_param_values_tbl(156).VALUE :=P_PARAM156 ;
2520 l_param_values_tbl(157).VALUE :=P_PARAM157 ;
2521 l_param_values_tbl(158).VALUE :=P_PARAM158 ;
2522 l_param_values_tbl(159).VALUE :=P_PARAM159 ;
2523 l_param_values_tbl(160).VALUE :=P_PARAM160 ;
2524 l_param_values_tbl(161).VALUE :=P_PARAM161 ;
2525 l_param_values_tbl(162).VALUE :=P_PARAM162 ;
2526 l_param_values_tbl(163).VALUE :=P_PARAM163 ;
2527 l_param_values_tbl(164).VALUE :=P_PARAM164 ;
2528 l_param_values_tbl(165).VALUE :=P_PARAM165 ;
2529 l_param_values_tbl(166).VALUE :=P_PARAM166 ;
2530 l_param_values_tbl(167).VALUE :=P_PARAM167 ;
2531 l_param_values_tbl(168).VALUE :=P_PARAM168 ;
2532 l_param_values_tbl(169).VALUE :=P_PARAM169 ;
2533 l_param_values_tbl(170).VALUE :=P_PARAM170 ;
2534 l_param_values_tbl(171).VALUE :=P_PARAM171 ;
2535 l_param_values_tbl(172).VALUE :=P_PARAM172 ;
2536 l_param_values_tbl(173).VALUE :=P_PARAM173 ;
2537 l_param_values_tbl(174).VALUE :=P_PARAM174 ;
2538 l_param_values_tbl(175).VALUE :=P_PARAM175 ;
2539 l_param_values_tbl(176).VALUE :=P_PARAM176 ;
2540 l_param_values_tbl(177).VALUE :=P_PARAM177 ;
2541 l_param_values_tbl(178).VALUE :=P_PARAM178 ;
2542 l_param_values_tbl(179).VALUE :=P_PARAM179 ;
2543 l_param_values_tbl(180).VALUE :=P_PARAM180 ;
2544 l_param_values_tbl(181).VALUE :=P_PARAM181 ;
2545 l_param_values_tbl(182).VALUE :=P_PARAM182 ;
2546 l_param_values_tbl(183).VALUE :=P_PARAM183 ;
2547 l_param_values_tbl(184).VALUE :=P_PARAM184 ;
2548 l_param_values_tbl(185).VALUE :=P_PARAM185 ;
2549 l_param_values_tbl(186).VALUE :=P_PARAM186 ;
2550 l_param_values_tbl(187).VALUE :=P_PARAM187 ;
2551 l_param_values_tbl(188).VALUE :=P_PARAM188 ;
2552 l_param_values_tbl(189).VALUE :=P_PARAM189 ;
2553 l_param_values_tbl(190).VALUE :=P_PARAM190 ;
2554 l_param_values_tbl(191).VALUE :=P_PARAM191 ;
2555 l_param_values_tbl(192).VALUE :=P_PARAM192 ;
2556 l_param_values_tbl(193).VALUE :=P_PARAM193 ;
2557 l_param_values_tbl(194).VALUE :=P_PARAM194 ;
2558 l_param_values_tbl(195).VALUE :=P_PARAM195 ;
2559 l_param_values_tbl(196).VALUE :=P_PARAM196 ;
2560 l_param_values_tbl(197).VALUE :=P_PARAM197 ;
2561 l_param_values_tbl(198).VALUE :=P_PARAM198 ;
2562 l_param_values_tbl(199).VALUE :=P_PARAM199 ;
2563 l_param_values_tbl(200).VALUE :=P_PARAM200 ;
2564 l_param_values_tbl(201).VALUE :=P_PARAM201 ;
2565 l_param_values_tbl(202).VALUE :=P_PARAM202 ;
2566 l_param_values_tbl(203).VALUE :=P_PARAM203 ;
2567 l_param_values_tbl(204).VALUE :=P_PARAM204 ;
2568 l_param_values_tbl(205).VALUE :=P_PARAM205 ;
2569 l_param_values_tbl(206).VALUE :=P_PARAM206 ;
2570 l_param_values_tbl(207).VALUE :=P_PARAM207 ;
2571 l_param_values_tbl(208).VALUE :=P_PARAM208 ;
2572 l_param_values_tbl(209).VALUE :=P_PARAM209 ;
2573 l_param_values_tbl(210).VALUE :=P_PARAM210 ;
2574 l_param_values_tbl(211).VALUE :=P_PARAM211 ;
2575 l_param_values_tbl(212).VALUE :=P_PARAM212 ;
2576 l_param_values_tbl(213).VALUE :=P_PARAM213 ;
2577 l_param_values_tbl(214).VALUE :=P_PARAM214 ;
2578 l_param_values_tbl(215).VALUE :=P_PARAM215 ;
2579 l_param_values_tbl(216).VALUE :=P_PARAM216 ;
2580 l_param_values_tbl(217).VALUE :=P_PARAM217 ;
2581 l_param_values_tbl(218).VALUE :=P_PARAM218 ;
2582 l_param_values_tbl(219).VALUE :=P_PARAM219 ;
2583 l_param_values_tbl(220).VALUE :=P_PARAM220 ;
2584 l_param_values_tbl(221).VALUE :=P_PARAM221 ;
2585 l_param_values_tbl(222).VALUE :=P_PARAM222 ;
2586 l_param_values_tbl(223).VALUE :=P_PARAM223 ;
2587 l_param_values_tbl(224).VALUE :=P_PARAM224 ;
2588 l_param_values_tbl(225).VALUE :=P_PARAM225 ;
2589 l_param_values_tbl(226).VALUE :=P_PARAM226 ;
2590 l_param_values_tbl(227).VALUE :=P_PARAM227 ;
2591 l_param_values_tbl(228).VALUE :=P_PARAM228 ;
2592 l_param_values_tbl(229).VALUE :=P_PARAM229 ;
2593 l_param_values_tbl(230).VALUE :=P_PARAM230 ;
2594 l_param_values_tbl(231).VALUE :=P_PARAM231 ;
2595 l_param_values_tbl(232).VALUE :=P_PARAM232 ;
2596 l_param_values_tbl(233).VALUE :=P_PARAM233 ;
2597 l_param_values_tbl(234).VALUE :=P_PARAM234 ;
2598 l_param_values_tbl(235).VALUE :=P_PARAM235 ;
2599 l_param_values_tbl(236).VALUE :=P_PARAM236 ;
2600 l_param_values_tbl(237).VALUE :=P_PARAM237 ;
2601 l_param_values_tbl(238).VALUE :=P_PARAM238 ;
2602 l_param_values_tbl(239).VALUE :=P_PARAM239 ;
2603 l_param_values_tbl(240).VALUE :=P_PARAM240 ;
2604 l_param_values_tbl(241).VALUE :=P_PARAM241 ;
2605 l_param_values_tbl(242).VALUE :=P_PARAM242 ;
2606 l_param_values_tbl(243).VALUE :=P_PARAM243 ;
2607 l_param_values_tbl(244).VALUE :=P_PARAM244 ;
2608 l_param_values_tbl(245).VALUE :=P_PARAM245 ;
2609 l_param_values_tbl(246).VALUE :=P_PARAM246 ;
2610 l_param_values_tbl(247).VALUE :=P_PARAM247 ;
2611 l_param_values_tbl(248).VALUE :=P_PARAM248 ;
2612 l_param_values_tbl(249).VALUE :=P_PARAM249 ;
2613 l_param_values_tbl(250).VALUE :=P_PARAM250 ;
2614 l_param_values_tbl(251).VALUE :=P_PARAM251 ;
2615 l_param_values_tbl(252).VALUE :=P_PARAM252 ;
2616 l_param_values_tbl(253).VALUE :=P_PARAM253 ;
2617 l_param_values_tbl(254).VALUE :=P_PARAM254 ;
2618 l_param_values_tbl(255).VALUE :=P_PARAM255 ;
2619 l_param_values_tbl(256).VALUE :=P_PARAM256 ;
2620 l_param_values_tbl(257).VALUE :=P_PARAM257 ;
2621 l_param_values_tbl(258).VALUE :=P_PARAM258 ;
2622 l_param_values_tbl(259).VALUE :=P_PARAM259 ;
2623 l_param_values_tbl(260).VALUE :=P_PARAM260 ;
2624 l_param_values_tbl(261).VALUE :=P_PARAM261 ;
2625 l_param_values_tbl(262).VALUE :=P_PARAM262 ;
2626 l_param_values_tbl(263).VALUE :=P_PARAM263 ;
2627 l_param_values_tbl(264).VALUE :=P_PARAM264 ;
2628 l_param_values_tbl(265).VALUE :=P_PARAM265 ;
2629 l_param_values_tbl(266).VALUE :=P_PARAM266 ;
2630 l_param_values_tbl(267).VALUE :=P_PARAM267 ;
2631 l_param_values_tbl(268).VALUE :=P_PARAM268 ;
2632 l_param_values_tbl(269).VALUE :=P_PARAM269 ;
2633 l_param_values_tbl(270).VALUE :=P_PARAM270 ;
2634 l_param_values_tbl(271).VALUE :=P_PARAM271 ;
2635 l_param_values_tbl(272).VALUE :=P_PARAM272 ;
2636 l_param_values_tbl(273).VALUE :=P_PARAM273 ;
2637 l_param_values_tbl(274).VALUE :=P_PARAM274 ;
2638 l_param_values_tbl(275).VALUE :=P_PARAM275 ;
2639 l_param_values_tbl(276).VALUE :=P_PARAM276 ;
2640 l_param_values_tbl(277).VALUE :=P_PARAM277 ;
2641 l_param_values_tbl(278).VALUE :=P_PARAM278 ;
2642 l_param_values_tbl(279).VALUE :=P_PARAM279 ;
2643 l_param_values_tbl(280).VALUE :=P_PARAM280 ;
2644 l_param_values_tbl(281).VALUE :=P_PARAM281 ;
2645 l_param_values_tbl(282).VALUE :=P_PARAM282 ;
2646 l_param_values_tbl(283).VALUE :=P_PARAM283 ;
2647 l_param_values_tbl(284).VALUE :=P_PARAM284 ;
2648 l_param_values_tbl(285).VALUE :=P_PARAM285 ;
2649 l_param_values_tbl(286).VALUE :=P_PARAM286 ;
2650 l_param_values_tbl(287).VALUE :=P_PARAM287 ;
2651 l_param_values_tbl(288).VALUE :=P_PARAM288 ;
2652 l_param_values_tbl(289).VALUE :=P_PARAM289 ;
2653 l_param_values_tbl(290).VALUE :=P_PARAM290 ;
2654 l_param_values_tbl(291).VALUE :=P_PARAM291 ;
2655 l_param_values_tbl(292).VALUE :=P_PARAM292 ;
2656 l_param_values_tbl(293).VALUE :=P_PARAM293 ;
2657 l_param_values_tbl(294).VALUE :=P_PARAM294 ;
2658 l_param_values_tbl(295).VALUE :=P_PARAM295 ;
2659 l_param_values_tbl(296).VALUE :=P_PARAM296 ;
2660 l_param_values_tbl(297).VALUE :=P_PARAM297 ;
2661 l_param_values_tbl(298).VALUE :=P_PARAM298 ;
2662 l_param_values_tbl(299).VALUE :=P_PARAM299 ;
2663 l_param_values_tbl(300).VALUE :=P_PARAM300 ;
2664 ---------------------------------------- PARAM values inserted -----------------------------
2665
2666
2667 ------------------------ Deriving Cal Period End Date -------------------
2668
2669 select date_assign_value into l_cal_period_end_date from fem_cal_periods_attr
2670 where cal_period_id = P_CAL_PERIOD
2671 and attribute_id = (
2672 select a.attribute_id from fem_dim_attributes_b a,fem_dim_attr_versions_b v
2673 where a.attribute_id = v.attribute_id
2674 and a.attribute_varchar_label = 'CAL_PERIOD_END_DATE'
2675 and v.default_version_flag = 'Y' );
2676
2677 ------------------------ Deriving Cal Period Number ---------------------
2678
2679 select number_assign_value into l_cal_period_number from FEM_CAL_PERIODS_ATTR
2680 where cal_period_id = P_CAL_PERIOD
2681 and attribute_id = (
2682 select a.attribute_id from fem_dim_attributes_b a,fem_dim_attr_versions_b v
2683 where a.attribute_id = v.attribute_id
2684 and a.attribute_varchar_label = 'GL_PERIOD_NUM'
2685 and v.default_version_flag = 'Y' );
2686
2687 ----------------------- Deriving Cal Period Grp Display Code ---------------------
2688
2689 select dimension_group_display_code into l_cal_grp_display_code from FEM_DIMENSION_GRPS_B
2690 where dimension_group_id = (select dimension_group_id from fem_cal_periods_b where
2691 cal_period_id = P_CAL_PERIOD);
2692
2693 ---------------------- Deriving Dataset Display Code ------------------------
2694
2695 select dataset_display_code into l_dataset_display_code from fem_datasets_b
2696 where dataset_code = P_DATASET_DISPLAY_CODE;
2697
2698 ---------------------- Creating dynamic insert query ------------------------
2699 l_canonical_format:= 'RRRR/MM/DD';
2700 l_adi_date_format := FND_PROFILE.VALUE('FEM_INTF_ATTR_DATE_FORMAT_MASK');
2701 l_end_date := to_char(l_cal_period_end_date,l_canonical_format);
2702
2703 l_query_columns := '(STATUS,LEDGER_DISPLAY_CODE,DATASET_DISPLAY_CODE,SOURCE_SYSTEM_DISPLAY_CODE,CAL_PERIOD_NUMBER,CAL_PERIOD_END_DATE,CALP_DIM_GRP_DISPLAY_CODE';
2704 l_query_values := '(' || '''' || 'LOAD' || ''''
2705 || ',' || '''' || P_LEDGER_DISPLAY_CODE || ''''
2706 || ',' || '''' || l_dataset_display_code || ''''
2707 || ',' || '''' || P_SOURCE_SYSTEM_DISPLAY_CODE || ''''
2708 || ',' || '''' || l_cal_period_number || ''''
2709 || ',' || 'to_date(' || '''' || l_end_date || '''' || ',' || '''' ||l_canonical_format || '''' || ')'
2710 || ',' || '''' || l_cal_grp_display_code || '''';
2711
2712 select table_name into l_table_name from fem_tables_vl
2713 where display_name = p_table_name;
2714
2715 select interface_table_name,table_name || '_INTF' into l_interface_table_name,l_interface_code from
2716 fem_tables_vl where display_name = p_table_name;
2717
2718 -------------------------------------------------------------------------------------------------------------------------
2719 -- For table length with more than 20 chars the integrator/interface code won't be simply table_name || '_INTG' / '_INTF'
2720 -- We need to fetch that interface/integrator code with some other logic. One such logic used here
2721
2722 if(length(l_table_name)>20) then
2723 select interface_code into l_interface_code from bne_interfaces_vl where instr(user_name,p_table_name) <>0;
2724 end if;
2725 ------------------------------------------------------------------------------------------------------------------------
2726
2727 for columns_values in
2728 (
2729 select bne_interface_col_name bne_interface_col_name,interface_column_name,data_type from
2730 fem_webadi_table_cols_maps
2731 where
2732 interface_table_name = l_interface_table_name
2733 and bne_interface_col_name in
2734 (select interface_col_name from bne_interface_cols_b where
2735 interface_code = l_interface_code and enabled_flag = 'Y')
2736 )
2737 loop
2738
2739 l_query_columns := l_query_columns || ',' || columns_values.interface_column_name;
2740
2741 l_param_number := TO_NUMBER(SUBSTR(columns_values.bne_interface_col_name,8));
2742
2743 l_column_value := l_param_values_tbl(l_param_number).VALUE;
2744
2745 l_column_name := columns_values.bne_interface_col_name;
2746
2747 if(l_column_value is null) then
2748 l_query_values := l_query_values || ',' || 'NULL' ;
2749 elsif(columns_values.data_type = 'DATE') then
2750 l_query_values := l_query_values || ',' || 'to_date(' || '''' ||l_column_value || '''' || ',' || '''' || l_adi_date_format || '''' || ')';
2751 else
2752 if(columns_values.data_type = 'NUMBER') then
2753 select to_number(l_column_value) into l_valid_number from dual;
2754 end if;
2755 l_query_values := l_query_values || ',' || '''' || l_column_value || '''';
2756 end if;
2757
2758 end loop;
2759
2760 l_query_columns := l_query_columns || ')';
2761 l_query_values := l_query_values || ')';
2762
2763 l_insert_query := 'INSERT INTO ' || l_interface_table_name || l_query_columns || ' VALUES ' || l_query_values;
2764
2765 execute immediate l_insert_query;
2766
2767 EXCEPTION
2768 --
2769 WHEN DUP_VAL_ON_INDEX THEN
2770 FND_MESSAGE.SET_NAME('FEM','FEM_ADI_DUPLICATE_ROWS');
2771 APP_EXCEPTION.Raise_Exception ;
2772
2773 WHEN INVALID_NUMBER THEN
2774
2775 select PROMPT_ABOVE into l_column_name from bne_interface_cols_vl where
2776 interface_code = l_interface_code and interface_col_name = l_column_name;
2777
2778 FND_MESSAGE.SET_NAME('FEM','FEM_ADI_INVALID_NUMBER');
2779 FND_MESSAGE.SET_TOKEN('COLUMN_NAME',l_column_name);
2780 APP_EXCEPTION.Raise_Exception ;
2781
2782
2783 WHEN E_NO_TABLE THEN
2784 NULL;
2785
2786 END UPLOAD_FEM_TABLE_INTERFACE;
2787
2788
2789 PROCEDURE CHECK_VALID_LAYOUT(P_TABLE_NAME IN VARCHAR2, X_VALID_CODE OUT NOCOPY VARCHAR2)
2790 IS
2791
2792 l_total_columns number;
2793 l_intf_table_name varchar2(50);
2794 begin
2795
2796 begin
2797 select interface_table_name into l_intf_table_name from fem_tables_b
2798 where table_name = p_table_name;
2799
2800 select count(1) into l_total_columns from dba_tab_columns
2801 where owner = (select table_owner from user_synonyms where synonym_name = l_intf_table_name)
2802 and table_name = l_intf_table_name;
2803
2804 exception
2805 when NO_DATA_FOUND then
2806 x_valid_code := 'N';
2807 return;
2808 end;
2809
2810 if(l_total_columns > 259) then
2811 x_valid_code := 'N';
2812 else
2813 x_valid_code := 'Y';
2814 end if;
2815
2816 END CHECK_VALID_LAYOUT;
2817
2818 PROCEDURE DELETE_METADATA
2819 IS
2820
2821 begin
2822
2823 delete from bne_security_rules where application_id = 274
2824 and security_code not in ('FEM_BALANCES_SECURITY_RULE');
2825
2826 delete from bne_secured_objects where application_id = 274
2827 and object_code not in ('FEM_BALANCES_INTG');
2828
2829 delete from bne_layouts_b where application_id = 274
2830 and layout_code not in ('FEM_BALANCES_LAYOUT');
2831
2832 delete from bne_layouts_tl where application_id = 274
2833 and layout_code not in ('FEM_BALANCES_LAYOUT');
2834
2835 delete from bne_layout_cols where application_id = 274
2836 and layout_code not in ('FEM_BALANCES_LAYOUT');
2837
2838 delete from bne_layout_blocks_tl where application_id = 274
2839 and layout_code not in ('FEM_BALANCES_LAYOUT');
2840
2841 delete from bne_layout_blocks_b where application_id = 274
2842 and layout_code not in ('FEM_BALANCES_LAYOUT');
2843
2844 delete from bne_content_cols_tl where application_id = 274
2845 and content_code not in ('FEM_BALANCES_CNT');
2846
2847 delete from bne_content_cols_b where application_id = 274
2848 and content_code not in ('FEM_BALANCES_CNT');
2849
2850 delete from bne_contents_tl where application_id = 274
2851 and content_code not in ('FEM_BALANCES_CNT');
2852
2853 delete from bne_contents_b where application_id = 274
2854 and content_code not in ('FEM_BALANCES_CNT');
2855
2856 delete from bne_mapping_lines where application_id = 274
2857 and mapping_code not in ('FEM_BALANCES_MAP');
2858
2859 delete from bne_mappings_tl where application_id = 274
2860 and mapping_code not in ('FEM_BALANCES_MAP');
2861
2862 delete from bne_mappings_b where application_id = 274
2863 and mapping_code not in ('FEM_BALANCES_MAP');
2864
2865 delete from bne_interfaces_b where application_id = 274
2866 and interface_code not in ('FEM_BALANCES_INTF');
2867
2868 delete from bne_interfaces_tl where application_id = 274
2869 and interface_code not in ('FEM_BALANCES_INTF');
2870
2871 delete from bne_interface_cols_b where application_id = 274
2872 and interface_code not in ('FEM_BALANCES_INTF');
2873
2874 delete from bne_interface_cols_tl where application_id = 274
2875 and interface_code not in ('FEM_BALANCES_INTF');
2876
2877 delete from bne_integrators_tl where application_id = 274
2878 and integrator_code not in ('FEM_BALANCES_INTG');
2879
2880 delete from bne_integrators_b where application_id = 274
2881 and integrator_code not in ('FEM_BALANCES_INTG');
2882
2883 delete from bne_stored_sql where application_id = 274
2884 and content_code not in ('FEM_BALANCES_CNT');
2885
2886 commit;
2887
2888 END DELETE_METADATA;
2889
2890
2891 END FEM_WEBADI_TABLES_UTILS_PVT;
2892