[Home] [Help]
PACKAGE BODY: APPS.BSC_WEB_ADI_LOADER_PKG
Source
1 package body BSC_WEB_ADI_LOADER_PKG AS
2 /*$Header: BSCWADIB.pls 120.8.12000000.2 2007/06/08 12:41:25 karthmoh ship $*/
3
4 g_bsc_schema VARCHAR2(32);
5
6 /*---------------------------------------------------------------------------
7 API to create the WebADI Integrator, an integrator will be created with the
8 code = <table_name>_INTG, also creates a Security Rule so that this
9 integrator is accessible from PMA <table_name>_SEC
10 Security Rule is required so that the Integrator is available for PMA Responsibility
11 This is possible by adding PMA functions to the security rule
12 ----------------------------------------------------------------------------*/
13
14 PROCEDURE CREATE_INTEGRATOR( TABLE_NAME VARCHAR2 )
15 IS
16 integrator_code VARCHAR2(100);
17 l_stmt VARCHAR2(3000);
18 BEGIN
19 l_stmt:='begin
20 BNE_INTEGRATOR_UTILS.CREATE_INTEGRATOR
21 (271, ''' || TABLE_NAME ||''' ,''Balanced Scorecard Loader '||TABLE_NAME ||''', USERENV(''LANG''),USERENV(''LANG''),0, :1);
22 end;';
23
24 EXECUTE IMMEDIATE l_stmt using OUT integrator_code;
25
26 l_stmt:='begin
27 BNE_SECURITY_UTILS_PKG.ADD_OBJECT_RULES(271,''' || integrator_code || ''' ,''INTEGRATOR'',
28 '''|| TABLE_NAME ||'_SEC'',''FUNCTION'',''BSC_PMD_LDR_INPUT_TBL,BSC_PMD_LDR_DIM,BNE_ADI_CREATE_DOCUMENT,BSC_PMD_LDR_INPUT_TBL_DB,BSC_PMD_LDR_DIM_DB'',1355);
29 end;';
30 EXECUTE IMMEDIATE l_stmt;
31 END;
32
33
34 /*---------------------------------------------------------------------------
35 API to create the WebADI Interface for each table with interface code =
36 <table_name>_INTF, and assigns this interface to an already created Integrator
37 for this table( <table_name>_INTG)
38 ----------------------------------------------------------------------------*/
39 PROCEDURE CREATE_INTERFACE(TABLE_NAME VARCHAR2 ) IS
40 l_stmt VARCHAR2(3000);
41 BEGIN
42 l_stmt := 'INSERT INTO BNE_INTERFACES_B (APPLICATION_ID, INTERFACE_CODE,
43 OBJECT_VERSION_NUMBER, INTEGRATOR_APP_ID, INTEGRATOR_CODE,
44 INTERFACE_NAME, UPLOAD_TYPE, UPLOAD_OBJ_NAME, UPLOAD_PARAM_LIST_APP_ID,
45 UPLOAD_PARAM_LIST_CODE, UPLOAD_ORDER, CREATED_BY, CREATION_DATE,
46 LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE )
47 VALUES ( 271,'''|| TABLE_NAME ||'_INTF'', 1, 271, '''|| TABLE_NAME ||'_INTG'', '''||TABLE_NAME ||''', 1, NULL, NULL
48 , NULL, NULL, 1355, sysdate, 1355, NULL, sysdate)';
49
50 EXECUTE IMMEDIATE L_STMT;
51
52 l_stmt := 'INSERT INTO BNE_INTERFACES_TL ( APPLICATION_ID, INTERFACE_CODE, LANGUAGE,
53 SOURCE_LANG, USER_NAME, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
54 LAST_UPDATE_LOGIN,LAST_UPDATE_DATE )
55 VALUES ( 271, '''|| TABLE_NAME ||'_INTF'', USERENV(''LANG''),USERENV(''LANG'') ,''Interface: BSC Loader'',
56 1355, sysdate, 1355, NULL, sysdate)';
57
58 EXECUTE IMMEDIATE L_STMT;
59 END;
60
61 /*---------------------------------------------------------------------------
62 API to create the WebADI Interface Columns corresponding to each Column in
63 table, plus an additional column as a place holder for the Interface Table
64 Name to be displayed in the Excel as the context
65 ----------------------------------------------------------------------------*/
66 PROCEDURE CREATE_INTERFACE_COLUMNS( TAB_NAME VARCHAR2 )
67 IS
68 l_stmt VARCHAR2(3000);
69 sequence_num NUMBER;
70 CURSOR TAB_COL IS
71 SELECT COLUMN_NAME,
72 DECODE(DATA_TYPE, 'VARCHAR2', 2, 'NUMBER', 1, 'DATE', 3) TYPE ,
73 (DECODE( NULLABLE, 'N', '* ')) || (SELECT Meaning FROM bsc_lookups WHERE lookup_type = 'BSC_PMA_WEBADI_HINTS' and lookup_code = DATA_TYPE) HINT,
74 DECODE( NULLABLE, 'N', 'Y', 'N') NOT_NULL_FLAG,
75 DATA_LENGTH
76 FROM ALL_TAB_COLUMNS
77 WHERE OWNER=g_bsc_schema AND TABLE_NAME = TAB_NAME
78 order by COLUMN_ID;
79 val_type VARCHAR2(8);
80 val_obj_name VARCHAR2(100);
81 field_size VARCHAR2(8);
82 BEGIN
83 l_stmt :='INSERT INTO BNE_INTERFACE_COLS_B ( APPLICATION_ID, INTERFACE_CODE, OBJECT_VERSION_NUMBER,
84 SEQUENCE_NUM, INTERFACE_COL_TYPE, INTERFACE_COL_NAME, ENABLED_FLAG, REQUIRED_FLAG, DISPLAY_FLAG,
85 READ_ONLY_FLAG, NOT_NULL_FLAG, SUMMARY_FLAG, MAPPING_ENABLED_FLAG, DATA_TYPE, FIELD_SIZE,
86 DEFAULT_TYPE, DEFAULT_VALUE, SEGMENT_NUMBER, GROUP_NAME, OA_FLEX_CODE, OA_CONCAT_FLEX, VAL_TYPE,
87 VAL_ID_COL, VAL_MEAN_COL, VAL_DESC_COL, VAL_OBJ_NAME, VAL_ADDL_W_C, VAL_COMPONENT_APP_ID,
88 VAL_COMPONENT_CODE, OA_FLEX_NUM, OA_FLEX_APPLICATION_ID, DISPLAY_ORDER, UPLOAD_PARAM_LIST_ITEM_NUM,
89 EXPANDED_SQL_QUERY, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE,
90 LOV_TYPE, OFFLINE_LOV_ENABLED_FLAG, VARIABLE_DATA_TYPE_CLASS )
91 VALUES ( 271,'''|| TAB_NAME ||'_INTF'', 1,
92 1, 2, ''TAB_NAME_HEADER'', ''Y'', ''N'', ''Y'',
93 ''Y'', ''N'', ''N'', ''Y'', 2, NULL,
94 NULL, NULL, NULL, NULL, NULL, NULL , NULL,
95 NULL, NULL, NULL, NULL , NULL, NULL,
96 NULL, NULL, NULL, 1, NULL,
97 NULL, 1355, sysdate, 1355, 0, sysdate,
98 NULL , ''N'',NULL )';
99 EXECUTE IMMEDIATE L_STMT;
100
101 l_stmt :='INSERT INTO BNE_INTERFACE_COLS_TL ( APPLICATION_ID, INTERFACE_CODE, SEQUENCE_NUM, LANGUAGE,
102 SOURCE_LANG, USER_HINT, PROMPT_LEFT, USER_HELP_TEXT, PROMPT_ABOVE, CREATED_BY, CREATION_DATE,
103 LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE )
104 VALUES ( 271, '''|| TAB_NAME ||'_INTF'', 1, USERENV(''LANG''), USERENV(''LANG''), null,
105 (select Meaning from bsc_lookups where lookup_type =''BSC_PMA_WEBADI_HINTS''
106 and lookup_Code = ''INTERFACE_TAB''), null, null, 1355,
107 sysdate, 1355, 0, sysdate)';
108
109 EXECUTE IMMEDIATE L_STMT;
110
111 sequence_num := 2;
112 val_type := 'JAVA';
113 val_obj_name := 'oracle.apps.bsc.locking.BSCWebADILockValidator';
114 FOR TAB_COL_REC IN TAB_COL LOOP
115 IF (TAB_COL_REC.TYPE = 3) THEN
116 field_size := 'NULL';
117 ELSE
118 field_size := TAB_COL_REC.DATA_LENGTH;
119 END IF;
120 l_stmt :='INSERT INTO BNE_INTERFACE_COLS_B ( APPLICATION_ID, INTERFACE_CODE, OBJECT_VERSION_NUMBER,
121 SEQUENCE_NUM, INTERFACE_COL_TYPE, INTERFACE_COL_NAME, ENABLED_FLAG, REQUIRED_FLAG, DISPLAY_FLAG,
122 READ_ONLY_FLAG, NOT_NULL_FLAG, SUMMARY_FLAG, MAPPING_ENABLED_FLAG, DATA_TYPE, FIELD_SIZE,
123 DEFAULT_TYPE, DEFAULT_VALUE, SEGMENT_NUMBER, GROUP_NAME, OA_FLEX_CODE, OA_CONCAT_FLEX, VAL_TYPE,
124 VAL_ID_COL, VAL_MEAN_COL, VAL_DESC_COL, VAL_OBJ_NAME, VAL_ADDL_W_C, VAL_COMPONENT_APP_ID,
125 VAL_COMPONENT_CODE, OA_FLEX_NUM, OA_FLEX_APPLICATION_ID, DISPLAY_ORDER, UPLOAD_PARAM_LIST_ITEM_NUM,
126 EXPANDED_SQL_QUERY, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE,
127 LOV_TYPE, OFFLINE_LOV_ENABLED_FLAG, VARIABLE_DATA_TYPE_CLASS )
128 VALUES ( 271,'''|| TAB_NAME ||'_INTF'', 1,
129 '||sequence_num ||', 1, '''|| TAB_COL_REC.COLUMN_NAME ||''', ''Y'', ''Y'', ''Y'',
130 ''N'', '''||TAB_COL_REC.NOT_NULL_FLAG||''', ''N'', ''Y'', '|| TAB_COL_REC.TYPE ||', ' ||field_size||',
131 NULL, NULL, NULL, NULL, NULL, NULL, ''' || val_type || ''',
132 NULL, NULL, NULL, '''|| val_obj_name ||''' , NULL, NULL,
133 NULL, NULL, NULL, '||sequence_num||', NULL,
134 NULL, 1355, sysdate, 1355, 0, sysdate,
135 NULL, ''N'', NULL )';
136
137 EXECUTE IMMEDIATE L_STMT;
138
139 l_stmt :='INSERT INTO BNE_INTERFACE_COLS_TL ( APPLICATION_ID, INTERFACE_CODE, SEQUENCE_NUM, LANGUAGE,
140 SOURCE_LANG, USER_HINT, PROMPT_LEFT, USER_HELP_TEXT, PROMPT_ABOVE, CREATED_BY, CREATION_DATE,
141 LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE )
142 VALUES ( 271, '''|| TAB_NAME ||'_INTF'', '||sequence_num ||', USERENV(''LANG''), USERENV(''LANG''), ''' || TAB_COL_REC.HINT ||''',
143 '''|| TAB_COL_REC.COLUMN_NAME ||''', ''' || TAB_COL_REC.HINT ||''', '''|| TAB_COL_REC.COLUMN_NAME ||''', 1355,
144 sysdate, 1355, 0, sysdate)';
145
146 EXECUTE IMMEDIATE L_STMT;
147
148 sequence_num := sequence_num +1;
149 -- reseting val_type and val_obj_name for remaining columns
150 val_type := null;
151 val_obj_name := null;
152 END LOOP;
153 END;
154
155 /*---------------------------------------------------------------------------
156 API to create metadata about Layouts, Layout Blocks, Layout Columns
157 Two Layout blocks are created one for the Context(Interface Table Name) and
158 other for the actual table
159 Layour code = <table_name>_L
160 ----------------------------------------------------------------------------*/
161 PROCEDURE CREATE_LAYOUT( TAB_NAME VARCHAR2 )
162 IS
163 l_stmt VARCHAR2(3000);
164 counter number;
165 CURSOR TAB_COL IS
166 SELECT COLUMN_NAME,
167 DECODE(DATA_TYPE, 'VARCHAR2', 2, 'NUMBER', 1, 'DATE', 3) TYPE
168 FROM ALL_TAB_COLUMNS WHERE OWNER=g_bsc_schema AND TABLE_NAME = TAB_NAME
169 order by COLUMN_ID;
170
171 BEGIN
172 l_stmt:='INSERT INTO BNE_LAYOUTS_B ( APPLICATION_ID, LAYOUT_CODE, OBJECT_VERSION_NUMBER, STYLESHEET_APP_ID,
173 STYLESHEET_CODE, INTEGRATOR_APP_ID, INTEGRATOR_CODE, STYLE, STYLE_CLASS, REPORTING_FLAG,
174 REPORTING_INTERFACE_APP_ID, REPORTING_INTERFACE_CODE, CREATION_DATE, CREATED_BY, LAST_UPDATED_BY,
175 LAST_UPDATE_LOGIN, LAST_UPDATE_DATE, CREATE_DOC_LIST_APP_ID, CREATE_DOC_LIST_CODE )
176 VALUES ( 271, '''|| TAB_NAME ||'_L'', 1, 231, ''DEFAULT'', 271, '''|| TAB_NAME ||'_INTG'', NULL,
177 ''BNE_PAGE'', ''N'', NULL, NULL, sysdate, 1355, 1355, NULL, sysdate, 271, NULL)';
178
179 EXECUTE IMMEDIATE L_STMT;
180
181 l_stmt:='INSERT INTO BNE_LAYOUTS_TL ( APPLICATION_ID, LAYOUT_CODE, LANGUAGE, SOURCE_LANG, USER_NAME,
182 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE )
183 VALUES ( 271,'''|| TAB_NAME ||'_L'', USERENV(''LANG''), USERENV(''LANG''), ''BSC Default Layout'', 1355,
184 sysdate, 1355, 0, sysdate)';
185
186 EXECUTE IMMEDIATE L_STMT;
187
188 -- Creating Layout Block for the Context i.e Interface Table Name
189 l_stmt:='INSERT INTO BNE_LAYOUT_BLOCKS_B ( APPLICATION_ID, LAYOUT_CODE, BLOCK_ID, OBJECT_VERSION_NUMBER,
190 PARENT_ID, LAYOUT_ELEMENT, STYLE_CLASS, STYLE, ROW_STYLE_CLASS, ROW_STYLE, COL_STYLE_CLASS,
191 COL_STYLE, PROMPT_DISPLAYED_FLAG, PROMPT_STYLE_CLASS, PROMPT_STYLE, HINT_DISPLAYED_FLAG,
192 HINT_STYLE_CLASS, HINT_STYLE, ORIENTATION, LAYOUT_CONTROL, DISPLAY_FLAG, BLOCKSIZE, MINSIZE,
193 MAXSIZE, SEQUENCE_NUM, PROMPT_COLSPAN, HINT_COLSPAN, ROW_COLSPAN, SUMMARY_STYLE_CLASS,
194 SUMMARY_STYLE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE )
195 VALUES ( 271, '''|| TAB_NAME ||'_L'', 1, 1, NULL, ''CONTEXT'', ''BNE_CONTEXT'', NULL, ''BNE_CONTEXT_ROW'',
196 NULL, NULL, NULL, ''Y'', ''BNE_CONTEXT_HEADER'', NULL, ''Y'', ''BNE_CONTEXT_HINT'', NULL, ''HORIZONTAL'',
197 ''COLUMN_FLOW'', ''Y'', 1, 1, 1, 20, 2, 2, 2, ''BNE_LINES_TOTAL'', NULL, 1355,
198 sysdate, 1355, 1355, sysdate)';
199 EXECUTE IMMEDIATE L_STMT;
200
201 l_stmt:='INSERT INTO BNE_LAYOUT_BLOCKS_TL ( APPLICATION_ID, LAYOUT_CODE, BLOCK_ID, USER_NAME, LANGUAGE,
202 SOURCE_LANG, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE )
203 VALUES ( 271, '''|| TAB_NAME ||'_L'', 1, ''Context'', USERENV(''LANG''), USERENV(''LANG''), 1355,
204 sysdate, 1355, 0, sysdate)';
205 EXECUTE IMMEDIATE L_STMT;
206
207 -- Creating Layout Block for the Data
208 l_stmt:='INSERT INTO BNE_LAYOUT_BLOCKS_B ( APPLICATION_ID, LAYOUT_CODE, BLOCK_ID, OBJECT_VERSION_NUMBER,
209 PARENT_ID, LAYOUT_ELEMENT, STYLE_CLASS, STYLE, ROW_STYLE_CLASS, ROW_STYLE, COL_STYLE_CLASS,
210 COL_STYLE, PROMPT_DISPLAYED_FLAG, PROMPT_STYLE_CLASS, PROMPT_STYLE, HINT_DISPLAYED_FLAG,
211 HINT_STYLE_CLASS, HINT_STYLE, ORIENTATION, LAYOUT_CONTROL, DISPLAY_FLAG, BLOCKSIZE, MINSIZE,
212 MAXSIZE, SEQUENCE_NUM, PROMPT_COLSPAN, HINT_COLSPAN, ROW_COLSPAN, SUMMARY_STYLE_CLASS,
213 SUMMARY_STYLE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE )
214 VALUES ( 271, '''|| TAB_NAME ||'_L'', 2, 1, NULL, ''LINE'', ''BNE_LINES'', NULL, ''BNE_LINES_ROW'',
215 NULL, NULL, NULL, ''Y'', ''BNE_LINES_HEADER'', NULL, ''Y'', ''BNE_LINES_HINT'', NULL, ''VERTICAL'',
216 ''TABLE_FLOW'', ''Y'', 10, 1, 1, 10, NULL, NULL, NULL, ''BNE_LINES_TOTAL'', NULL, 1355,
217 sysdate, 1355, 1355, sysdate)';
218 EXECUTE IMMEDIATE L_STMT;
219
220 l_stmt:='INSERT INTO BNE_LAYOUT_BLOCKS_TL ( APPLICATION_ID, LAYOUT_CODE, BLOCK_ID, USER_NAME, LANGUAGE,
221 SOURCE_LANG, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE )
222 VALUES ( 271, '''|| TAB_NAME ||'_L'', 2, ''Line'', USERENV(''LANG''),USERENV(''LANG''), 1355,
223 sysdate, 1355, 0, sysdate)';
224 EXECUTE IMMEDIATE L_STMT;
225
226 -- Creating Layout Column for the Context i.e Interface Table Name
227 l_stmt:='INSERT INTO BNE_LAYOUT_COLS ( APPLICATION_ID, LAYOUT_CODE, BLOCK_ID, OBJECT_VERSION_NUMBER,
228 INTERFACE_APP_ID, INTERFACE_CODE, INTERFACE_SEQ_NUM, SEQUENCE_NUM, STYLE, STYLE_CLASS,
229 HINT_STYLE, HINT_STYLE_CLASS, PROMPT_STYLE, PROMPT_STYLE_CLASS, DEFAULT_TYPE, DEFAULT_VALUE,
230 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE )
231 VALUES ( 271, '''|| TAB_NAME ||'_L'', 1, 1, 271, '''|| TAB_NAME ||'_INTF'',1 ,
232 10, NULL, NULL, NULL, NULL, NULL, NULL, ''CONSTANT'', '''|| TAB_NAME ||''', 1355,
233 sysdate, 1355, 1355, sysdate)';
234 EXECUTE IMMEDIATE L_STMT;
235
236 counter :=2;
237 FOR TAB_COL_REC IN TAB_COL LOOP
238 l_stmt:='INSERT INTO BNE_LAYOUT_COLS ( APPLICATION_ID, LAYOUT_CODE, BLOCK_ID, OBJECT_VERSION_NUMBER,
239 INTERFACE_APP_ID, INTERFACE_CODE, INTERFACE_SEQ_NUM, SEQUENCE_NUM, STYLE, STYLE_CLASS,
240 HINT_STYLE, HINT_STYLE_CLASS, PROMPT_STYLE, PROMPT_STYLE_CLASS, DEFAULT_TYPE, DEFAULT_VALUE,
241 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE )
242 VALUES ( 271, '''|| TAB_NAME ||'_L'', 2, 1, 271, '''|| TAB_NAME ||'_INTF'',' || counter ||' ,
243 ' || (counter*10)||', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1355,
244 sysdate, 1355, 1355, sysdate)';
245
246 EXECUTE IMMEDIATE L_STMT;
247 counter := counter + 1;
248 END LOOP;
249
250 END;
251
252 /*---------------------------------------------------------------------------
253 API to create Content Metadata for the Integrator.
254 COntent code = <table_name>_CNT
255 ----------------------------------------------------------------------------*/
256 PROCEDURE CREATE_CONTENT(
257 TAB_NAME VARCHAR2
258 , x_errbuf OUT NOCOPY VARCHAR2
259 , x_retcode OUT NOCOPY VARCHAR2)
260 IS
261 CURSOR TAB_COL IS
262 SELECT COLUMN_NAME
263 FROM ALL_TAB_COLUMNS WHERE OWNER=g_bsc_schema AND TABLE_NAME = TAB_NAME
264 order by COLUMN_ID;
265 col_list VARCHAR2(5000);
266 query VARCHAR2(5000);
267 content_code VARCHAR2(100);
268 l_stmt VARCHAR2(3000);
269 BEGIN
270 x_retcode := FND_API.G_RET_STS_SUCCESS;
271 col_list := '';
272 FOR TAB_COL_REC IN TAB_COL LOOP
273 col_list := col_list || TAB_COL_REC.COLUMN_NAME || ',';
274 IF(LENGTH(col_list) > 1957) THEN
275 x_errbuf := 'BSC_PMA_TOO_MANY_COLS';
276 x_retcode := FND_API.G_RET_STS_ERROR;
277 RETURN;
278 END IF;
279 END LOOP;
280 col_list := SUBSTR(col_list,1,LENGTH(col_list)-1);
281 query := ' SELECT ' || col_list || ' FROM ' || TAB_NAME ;
282
283 l_stmt:='BEGIN
284 BNE_CONTENT_UTILS.CREATE_CONTENT_STORED_SQL(271, '''|| TAB_NAME ||''','''|| TAB_NAME || '_INTG'',
285 ''BSC LOADER CONTENT'', :1, :2, USERENV(''LANG''), USERENV(''LANG''), 2, :3);
286 END;';
287 EXECUTE IMMEDIATE l_stmt using IN col_list, IN query, OUT content_code;
288 END;
289
290 /*---------------------------------------------------------------------------
291 API to create Mapping Metadata for the Integrator.
292 COntent code = <table_name>_MAP
293 ----------------------------------------------------------------------------*/
294 PROCEDURE CREATE_MAPPING( TAB_NAME VARCHAR2 )
295 IS
296 l_stmt VARCHAR2(3000);
297 CURSOR TAB_COL IS
298 SELECT COLUMN_NAME
299 FROM ALL_TAB_COLUMNS WHERE OWNER=g_bsc_schema AND TABLE_NAME = TAB_NAME
300 order by COLUMN_ID;
301 seq NUMBER;
302 BEGIN
303 -- Creating Mapping to download data from the Interface Table to the Excel Sheet
304 l_stmt := 'INSERT INTO BNE_MAPPINGS_B (APPLICATION_ID, MAPPING_CODE, OBJECT_VERSION_NUMBER, INTEGRATOR_APP_ID,
305 INTEGRATOR_CODE, REPORTING_FLAG, REPORTING_INTERFACE_APP_ID, REPORTING_INTERFACE_CODE, LAST_UPDATE_DATE,
306 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN) VALUES (
307 271, '''|| TAB_NAME ||'_MAP'' , 1, 271, '''|| TAB_NAME ||'_INTG'', ''N'', NULL, NULL, sysdate
308 , 2, sysdate, 2, 0)';
309 EXECUTE IMMEDIATE L_STMT;
310
311 l_stmt := 'INSERT INTO BNE_MAPPINGS_tl ( APPLICATION_ID, MAPPING_CODE, LANGUAGE, SOURCE_LANG, USER_NAME,
312 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
313 271, '''|| TAB_NAME ||'_MAP'', USERENV(''LANG''), USERENV(''LANG''), ''None'', sysdate, 2, sysdate , 2, 0)';
314 EXECUTE IMMEDIATE L_STMT;
315
316 -- Mapping columns from Content to Interface
317 seq :=1;
318 FOR TAB_COL_REC IN TAB_COL LOOP
319 l_stmt := 'INSERT INTO BNE_MAPPING_LINES ( APPLICATION_ID, MAPPING_CODE, SEQUENCE_NUM, CONTENT_APP_ID, CONTENT_CODE,
320 CONTENT_SEQ_NUM, INTERFACE_APP_ID, INTERFACE_CODE, INTERFACE_SEQ_NUM, OBJECT_VERSION_NUMBER,
321 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES
322 ( 271, '''|| TAB_NAME ||'_MAP'', ' || seq || ', 271, '''|| TAB_NAME ||'_CNT'', ' || seq || ', 271
323 , '''||TAB_NAME ||'_INTF'', ('||seq||'+1) ,1, sysdate, 2, sysdate , 2, 0)';
324 EXECUTE IMMEDIATE L_STMT;
325 seq := seq+1;
326 END LOOP;
327 END;
328
329
330
331 /*---------------------------------------------------------------------------
332 API to create Query for Duplicate Key Management for the Integrator.
333 Query Code = <table_name>_Q
334 ----------------------------------------------------------------------------*/
335 PROCEDURE CREATE_QUERY( TAB_NAME VARCHAR2 )
336 IS
337 l_stmt VARCHAR2(3000);
338 query VARCHAR2(1000);
339 BEGIN
340 -- the query will fetch all the Duplicate profiles defined for a particular Integrator
341 query := ' SELECT APPLICATION_ID ||'''':''''||DUP_PROFILE_CODE, USER_NAME ' ||
342 ' FROM BNE_DUPLICATE_PROFILES_VL ' ||
343 ' WHERE INTEGRATOR_APP_ID = 271 AND INTEGRATOR_CODE = '''''|| TAB_NAME ||'_INTG''''';
344
345 l_stmt := 'INSERT INTO BNE_RAW_QUERY (APPLICATION_ID, QUERY_CODE, SEQUENCE_NUM, OBJECT_VERSION_NUMBER, QUERY,
346 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN) VALUES (
347 271,'''|| TAB_NAME ||'_Q'', 1, 1, '''|| query ||''', sysdate, 2, sysdate, 2, 0)';
348 EXECUTE IMMEDIATE L_STMT;
349
350 l_stmt := 'INSERT INTO BNE_QUERIES_B( APPLICATION_ID, QUERY_CODE, OBJECT_VERSION_NUMBER, QUERY_CLASS,
351 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
352 271,'''|| TAB_NAME ||'_Q'', 1, ''oracle.apps.bne.query.BneRawSQLQuery'', sysdate, 2, sysdate, 2, 0)';
353 EXECUTE IMMEDIATE L_STMT;
354
355 l_stmt := 'INSERT INTO BNE_QUERIES_TL( APPLICATION_ID, QUERY_CODE, LANGUAGE, SOURCE_LANG, USER_NAME,
356 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
357 271,'''|| TAB_NAME ||'_Q'', USERENV(''LANG''), USERENV(''LANG''), ''Duplicate row Management'', sysdate, 2, sysdate , 2, 0)';
358 EXECUTE IMMEDIATE L_STMT;
359 END;
360
361
362 /*---------------------------------------------------------------------------
363 API to create 2 Parameters
364 - Rows
365 - Duplicate management
366 ----------------------------------------------------------------------------*/
367 PROCEDURE CREATE_PARAM_DEFN( TAB_NAME VARCHAR2, key_cols NUMBER )
368 IS
369 l_stmt VARCHAR2(3000);
370 BEGIN
371 -- Parameter Definition for the Upload Parameter List
372 l_stmt := 'INSERT INTO BNE_PARAM_DEFNS_B( APPLICATION_ID, PARAM_DEFN_CODE, OBJECT_VERSION_NUMBER, PARAM_NAME,
376 DISPLAY_TYPE, DISPLAY_STYLE, DISPLAY_SIZE, HELP_URL, FORMAT_MASK,
373 PARAM_SOURCE, PARAM_CATEGORY, DATATYPE, ATTRIBUTE_APP_ID, ATTRIBUTE_CODE, PARAM_RESOLVER,
374 DEFAULT_REQUIRED_FLAG, DEFAULT_VISIBLE_FLAG, DEFAULT_USER_MODIFYABLE_FLAG, DEFAULT_STRING,
375 DEFAULT_DATE, DEFAULT_NUMBER, DEFAULT_BOOLEAN_FLAG, DEFAULT_FORMULA, VAL_TYPE, VAL_VALUE, MAX_SIZE,
377 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
378 271, '''|| TAB_NAME ||'_ROW'', 2, ''bne:rows'', ''WEBADI:Upload'',6,1,null,null,null, ''N'',''Y'',''Y'',''FLAGGED'',
379 null, null, null, null, 3, ''BNE_ROWS'', 100, 3, 2, 100, null, null, sysdate, 2, sysdate , 2, 0)';
380 EXECUTE IMMEDIATE L_STMT;
381
382 IF (key_cols > 0) then
383 l_stmt := 'INSERT INTO BNE_PARAM_DEFNS_B( APPLICATION_ID, PARAM_DEFN_CODE, OBJECT_VERSION_NUMBER, PARAM_NAME,
384 PARAM_SOURCE, PARAM_CATEGORY, DATATYPE, ATTRIBUTE_APP_ID, ATTRIBUTE_CODE, PARAM_RESOLVER,
385 DEFAULT_REQUIRED_FLAG, DEFAULT_VISIBLE_FLAG, DEFAULT_USER_MODIFYABLE_FLAG, DEFAULT_STRING,
386 DEFAULT_DATE, DEFAULT_NUMBER, DEFAULT_BOOLEAN_FLAG, DEFAULT_FORMULA, VAL_TYPE, VAL_VALUE, MAX_SIZE,
387 DISPLAY_TYPE, DISPLAY_STYLE, DISPLAY_SIZE, HELP_URL, FORMAT_MASK,
388 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
389 271, '''|| TAB_NAME ||'_DUP'' ,1 , ''Duplicate Parameter Query'', ''WEBADI: Upload'', 5, 1, null, null, null,
390 ''N'', ''Y'', ''Y'', null, null, null, null, null, 4, ''271:'|| TAB_NAME ||'_Q'', 100,
391 3, 2, 100, null, null, sysdate, 2, sysdate, 2, 0)';
392 EXECUTE IMMEDIATE L_STMT;
393 END IF;
394
395 l_stmt := 'INSERT INTO BNE_PARAM_DEFNS_B( APPLICATION_ID, PARAM_DEFN_CODE, OBJECT_VERSION_NUMBER, PARAM_NAME,
396 PARAM_SOURCE, PARAM_CATEGORY, DATATYPE, ATTRIBUTE_APP_ID, ATTRIBUTE_CODE, PARAM_RESOLVER,
397 DEFAULT_REQUIRED_FLAG, DEFAULT_VISIBLE_FLAG, DEFAULT_USER_MODIFYABLE_FLAG, DEFAULT_STRING,
398 DEFAULT_DATE, DEFAULT_NUMBER, DEFAULT_BOOLEAN_FLAG, DEFAULT_FORMULA, VAL_TYPE, VAL_VALUE, MAX_SIZE,
399 DISPLAY_TYPE, DISPLAY_STYLE, DISPLAY_SIZE, HELP_URL, FORMAT_MASK,
400 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
401 271, ''OBJECT_ID'', 1, ''OBJECT_ID'', ''WEBADI: Upload'',6,1,null,null,null, ''N'',''N'',''N'',
402 null, null, null, null, null, ''1'', null, 100, 2, 2, 100, null, null, sysdate, 2, sysdate , 2, 0)';
403 EXECUTE IMMEDIATE L_STMT;
404
405 l_stmt := 'INSERT INTO BNE_PARAM_DEFNS_B( APPLICATION_ID, PARAM_DEFN_CODE, OBJECT_VERSION_NUMBER, PARAM_NAME,
406 PARAM_SOURCE, PARAM_CATEGORY, DATATYPE, ATTRIBUTE_APP_ID, ATTRIBUTE_CODE, PARAM_RESOLVER,
407 DEFAULT_REQUIRED_FLAG, DEFAULT_VISIBLE_FLAG, DEFAULT_USER_MODIFYABLE_FLAG, DEFAULT_STRING,
408 DEFAULT_DATE, DEFAULT_NUMBER, DEFAULT_BOOLEAN_FLAG, DEFAULT_FORMULA, VAL_TYPE, VAL_VALUE, MAX_SIZE,
409 DISPLAY_TYPE, DISPLAY_STYLE, DISPLAY_SIZE, HELP_URL, FORMAT_MASK,
410 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
411 271, ''OBJECT_TYPE'', 1, ''OBJECT_TYPE'', ''WEBADI: Upload'',6,1,null,null,null, ''N'',''N'',''N'',
412 null, null, null, null, null, ''1'', null, 100, 2, 2, 100, null, null, sysdate, 2, sysdate , 2, 0)';
413 EXECUTE IMMEDIATE L_STMT;
414
415 l_stmt := 'INSERT INTO BNE_PARAM_DEFNS_B( APPLICATION_ID, PARAM_DEFN_CODE, OBJECT_VERSION_NUMBER, PARAM_NAME,
416 PARAM_SOURCE, PARAM_CATEGORY, DATATYPE, ATTRIBUTE_APP_ID, ATTRIBUTE_CODE, PARAM_RESOLVER,
417 DEFAULT_REQUIRED_FLAG, DEFAULT_VISIBLE_FLAG, DEFAULT_USER_MODIFYABLE_FLAG, DEFAULT_STRING,
418 DEFAULT_DATE, DEFAULT_NUMBER, DEFAULT_BOOLEAN_FLAG, DEFAULT_FORMULA, VAL_TYPE, VAL_VALUE, MAX_SIZE,
419 DISPLAY_TYPE, DISPLAY_STYLE, DISPLAY_SIZE, HELP_URL, FORMAT_MASK,
420 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
421 271, ''TABLE_NAME'', 1, ''TABLE_NAME'', ''WEBADI: Upload'',6,1,null,null,null, ''N'',''N'',''N'',
422 null, null, null, null, null, ''1'', null, 100, 2, 2, 100, null, null, sysdate, 2, sysdate , 2, 0)';
423 EXECUTE IMMEDIATE L_STMT;
424
425 l_stmt := 'INSERT INTO BNE_PARAM_DEFNS_B( APPLICATION_ID, PARAM_DEFN_CODE, OBJECT_VERSION_NUMBER, PARAM_NAME,
426 PARAM_SOURCE, PARAM_CATEGORY, DATATYPE, ATTRIBUTE_APP_ID, ATTRIBUTE_CODE, PARAM_RESOLVER,
427 DEFAULT_REQUIRED_FLAG, DEFAULT_VISIBLE_FLAG, DEFAULT_USER_MODIFYABLE_FLAG, DEFAULT_STRING,
428 DEFAULT_DATE, DEFAULT_NUMBER, DEFAULT_BOOLEAN_FLAG, DEFAULT_FORMULA, VAL_TYPE, VAL_VALUE, MAX_SIZE,
429 DISPLAY_TYPE, DISPLAY_STYLE, DISPLAY_SIZE, HELP_URL, FORMAT_MASK,
430 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
431 271, ''QUERY_TIME'', 1, ''QUERY_TIME'', ''WEBADI: Upload'',6,1,null,null,null, ''N'',''N'',''N'',
432 null, null, null, null, null, ''1'', null, 100, 2, 2, 100, null, null, sysdate, 2, sysdate , 2, 0)';
433 EXECUTE IMMEDIATE L_STMT;
434
435 -- Parameter Definition TL
436 l_stmt := 'INSERT INTO BNE_PARAM_DEFNS_TL(APPLICATION_ID, PARAM_DEFN_CODE, LANGUAGE, SOURCE_LANG, USER_NAME,
437 DEFAULT_STRING, DEFAULT_DESC, PROMPT_LEFT, PROMPT_ABOVE, USER_TIP, ACCESS_KEY,
438 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
439 271,'''|| TAB_NAME ||'_ROW'',USERENV(''LANG''),USERENV(''LANG''),''Flagged rows'',null,''flagged rows'',
440 BSC_WEB_ADI_LOADER_PKG.get_lookup_value(''BSC_PMA_WEBADI_UPL_ROW'',''ROWS_UPL''),null,
441 null,null,sysdate, 2, sysdate, 2, 0)';
442 EXECUTE IMMEDIATE L_STMT;
443
444 IF (key_cols > 0) then
448 271,'''|| TAB_NAME ||'_DUP'',USERENV(''LANG''),USERENV(''LANG''),''Duplicate Parameter Query'' ,null,''Update Columns'',
445 l_stmt := 'INSERT INTO BNE_PARAM_DEFNS_TL(APPLICATION_ID, PARAM_DEFN_CODE, LANGUAGE, SOURCE_LANG, USER_NAME,
446 DEFAULT_STRING, DEFAULT_DESC, PROMPT_LEFT, PROMPT_ABOVE, USER_TIP, ACCESS_KEY,
447 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
449 BSC_WEB_ADI_LOADER_PKG.get_lookup_value(''BSC_PMA_WEBADI_UPL_DUP'',''DUPLICATE_REC''),
450 null,null,null,sysdate, 2, sysdate, 2, 0)'; --BSC_LOOKUPS
451 EXECUTE IMMEDIATE L_STMT;
452 end if;
453
454 l_stmt := 'INSERT INTO BNE_PARAM_DEFNS_TL(APPLICATION_ID, PARAM_DEFN_CODE, LANGUAGE, SOURCE_LANG, USER_NAME,
455 DEFAULT_STRING, DEFAULT_DESC, PROMPT_LEFT, PROMPT_ABOVE, USER_TIP, ACCESS_KEY,
456 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
457 271,''OBJECT_ID'',USERENV(''LANG''),USERENV(''LANG''),''Object Id'' ,null,''Object Id'',
458 ''Object Id'',null,''Object Id'',null,sysdate, 2, sysdate, 2, 0)';
459 EXECUTE IMMEDIATE L_STMT;
460
461 l_stmt := 'INSERT INTO BNE_PARAM_DEFNS_TL(APPLICATION_ID, PARAM_DEFN_CODE, LANGUAGE, SOURCE_LANG, USER_NAME,
462 DEFAULT_STRING, DEFAULT_DESC, PROMPT_LEFT, PROMPT_ABOVE, USER_TIP, ACCESS_KEY,
463 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
464 271,''OBJECT_TYPE'',USERENV(''LANG''),USERENV(''LANG''),''Object Type'' ,null,''Object Type'',
465 ''Object Type'',null,''Object Type'',null,sysdate, 2, sysdate, 2, 0)';
466 EXECUTE IMMEDIATE L_STMT;
467
468 l_stmt := 'INSERT INTO BNE_PARAM_DEFNS_TL(APPLICATION_ID, PARAM_DEFN_CODE, LANGUAGE, SOURCE_LANG, USER_NAME,
469 DEFAULT_STRING, DEFAULT_DESC, PROMPT_LEFT, PROMPT_ABOVE, USER_TIP, ACCESS_KEY,
470 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
471 271,''TABLE_NAME'',USERENV(''LANG''),USERENV(''LANG''),''Table Name'' ,null,''Table Name'',
472 ''Table Name'',null,''Table Name'',null,sysdate, 2, sysdate, 2, 0)';
473 EXECUTE IMMEDIATE L_STMT;
474
475 l_stmt := 'INSERT INTO BNE_PARAM_DEFNS_TL(APPLICATION_ID, PARAM_DEFN_CODE, LANGUAGE, SOURCE_LANG, USER_NAME,
476 DEFAULT_STRING, DEFAULT_DESC, PROMPT_LEFT, PROMPT_ABOVE, USER_TIP, ACCESS_KEY,
477 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
478 271,''QUERY_TIME'',USERENV(''LANG''),USERENV(''LANG''),''Query Time'' ,null,''Query Time'',
479 ''Query Time'',null,''Query Time'',null,sysdate, 2, sysdate, 2, 0)';
480 EXECUTE IMMEDIATE L_STMT;
481 END;
482
483 /*---------------------------------------------------------------------------
484 API to create a Parameter List
485 Parameter List Code = <table_name>_PL
486 ----------------------------------------------------------------------------*/
487 PROCEDURE CREATE_PARAM_LIST( TAB_NAME VARCHAR2, key_cols NUMBER )
488 IS
489 l_stmt VARCHAR2(3000);
490 l_upload_param_list VARCHAR2(50);
491 l_integrator VARCHAR2(50);
492 BEGIN
493 -- Create Upload Parameter List
494 l_stmt := 'INSERT INTO BNE_PARAM_LISTS_B( APPLICATION_ID, PARAM_LIST_CODE, OBJECT_VERSION_NUMBER, PERSISTENT_FLAG,
495 COMMENTS, ATTRIBUTE_APP_ID, ATTRIBUTE_CODE, LIST_RESOLVER,
496 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
497 271,'''|| TAB_NAME ||'_UPL'',3,''Y'',''WebADI: Upload Parameter List'',null,null,null,
498 sysdate, 2, sysdate , 2, 0)';
499 EXECUTE IMMEDIATE L_STMT;
500
501 -- Parameter List TL
502 l_stmt := 'INSERT INTO BNE_PARAM_LISTS_TL(APPLICATION_ID, PARAM_LIST_CODE, LANGUAGE, SOURCE_LANG,
503 USER_NAME, USER_TIP, PROMPT_LEFT, PROMPT_ABOVE,
504 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
505 271,'''|| TAB_NAME ||'_UPL'',USERENV(''LANG''),USERENV(''LANG''),''Web ADI: Upload Parameter List'', null, null,
506 ''Upload Parameters'',sysdate, 2, sysdate, 2, 0)';
507 EXECUTE IMMEDIATE L_STMT;
508
509 -- Associating ROW parameter with Parameter list
510 l_stmt := 'INSERT INTO BNE_PARAM_LIST_ITEMS(APPLICATION_ID, PARAM_LIST_CODE, SEQUENCE_NUM, OBJECT_VERSION_NUMBER,
511 PARAM_DEFN_APP_ID, PARAM_DEFN_CODE, PARAM_NAME, ATTRIBUTE_APP_ID, ATTRIBUTE_CODE, STRING_VALUE,
512 DATE_VALUE, NUMBER_VALUE, BOOLEAN_VALUE_FLAG, FORMULA_VALUE, DESC_VALUE,
513 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
514 271,'''|| TAB_NAME ||'_UPL'',1,2,271,'''|| TAB_NAME ||'_ROW'',''bne:rows'',null,null,null,null,null,null,null,null
515 ,sysdate, 2, sysdate, 2, 0)';
516 EXECUTE IMMEDIATE L_STMT;
517
518 -- Associating Duplicate parameter with Parameter list
519 IF (key_cols > 0) then
520 l_stmt := 'INSERT INTO BNE_PARAM_LIST_ITEMS(APPLICATION_ID, PARAM_LIST_CODE, SEQUENCE_NUM, OBJECT_VERSION_NUMBER,
521 PARAM_DEFN_APP_ID, PARAM_DEFN_CODE, PARAM_NAME, ATTRIBUTE_APP_ID, ATTRIBUTE_CODE, STRING_VALUE,
522 DATE_VALUE, NUMBER_VALUE, BOOLEAN_VALUE_FLAG, FORMULA_VALUE, DESC_VALUE,
523 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
524 271,'''|| TAB_NAME ||'_UPL'', 2, 1, 271, '''|| TAB_NAME ||'_DUP'', ''bne:duplicateProfile'', null, null,
525 null, null, null, null, null, null, sysdate, 2, sysdate, 2, 0)';
526 EXECUTE IMMEDIATE L_STMT;
527 END IF;
528
529 -- Associating OBJECT_TYPE with Parameter list
530 l_stmt := 'INSERT INTO BNE_PARAM_LIST_ITEMS(APPLICATION_ID, PARAM_LIST_CODE, SEQUENCE_NUM, OBJECT_VERSION_NUMBER,
531 PARAM_DEFN_APP_ID, PARAM_DEFN_CODE, PARAM_NAME, ATTRIBUTE_APP_ID, ATTRIBUTE_CODE, STRING_VALUE,
532 DATE_VALUE, NUMBER_VALUE, BOOLEAN_VALUE_FLAG, FORMULA_VALUE, DESC_VALUE,
533 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
534 271,'''|| TAB_NAME ||'_UPL'', 3, 1, 271, ''OBJECT_TYPE'', ''OBJECT_TYPE'', null, null,
535 null, null, null, null, null, null, sysdate, 2, sysdate, 2, 0)';
536 EXECUTE IMMEDIATE L_STMT;
537
538 -- Associating ObjectID with Parameter list
539 l_stmt := 'INSERT INTO BNE_PARAM_LIST_ITEMS(APPLICATION_ID, PARAM_LIST_CODE, SEQUENCE_NUM, OBJECT_VERSION_NUMBER,
540 PARAM_DEFN_APP_ID, PARAM_DEFN_CODE, PARAM_NAME, ATTRIBUTE_APP_ID, ATTRIBUTE_CODE, STRING_VALUE,
541 DATE_VALUE, NUMBER_VALUE, BOOLEAN_VALUE_FLAG, FORMULA_VALUE, DESC_VALUE,
542 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
543 271,'''|| TAB_NAME ||'_UPL'', 4, 1, 271, ''OBJECT_ID'', ''OBJECT_ID'', null, null,
544 null, null, null, null, null, null, sysdate, 2, sysdate, 2, 0)';
545 EXECUTE IMMEDIATE L_STMT;
546
547 -- Associating TABLE_NAME with Parameter list
548 l_stmt := 'INSERT INTO BNE_PARAM_LIST_ITEMS(APPLICATION_ID, PARAM_LIST_CODE, SEQUENCE_NUM, OBJECT_VERSION_NUMBER,
549 PARAM_DEFN_APP_ID, PARAM_DEFN_CODE, PARAM_NAME, ATTRIBUTE_APP_ID, ATTRIBUTE_CODE, STRING_VALUE,
550 DATE_VALUE, NUMBER_VALUE, BOOLEAN_VALUE_FLAG, FORMULA_VALUE, DESC_VALUE,
551 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
552 271,'''|| TAB_NAME ||'_UPL'', 5, 1, 271, ''TABLE_NAME'', ''TABLE_NAME'', null, null,
553 null, null, null, null, null, null, sysdate, 2, sysdate, 2, 0)';
554 EXECUTE IMMEDIATE L_STMT;
555
556 -- Associating QUERY_TIME with Parameter list
557 l_stmt := 'INSERT INTO BNE_PARAM_LIST_ITEMS(APPLICATION_ID, PARAM_LIST_CODE, SEQUENCE_NUM, OBJECT_VERSION_NUMBER,
558 PARAM_DEFN_APP_ID, PARAM_DEFN_CODE, PARAM_NAME, ATTRIBUTE_APP_ID, ATTRIBUTE_CODE, STRING_VALUE,
559 DATE_VALUE, NUMBER_VALUE, BOOLEAN_VALUE_FLAG, FORMULA_VALUE, DESC_VALUE,
560 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
561 271,'''|| TAB_NAME ||'_UPL'', 6, 1, 271, ''QUERY_TIME'', ''QUERY_TIME'', null, null,
562 null, null, null, null, null, null, sysdate, 2, sysdate, 2, 0)';
563 EXECUTE IMMEDIATE L_STMT;
564
565 -- Update the Integrator to use this Upload List
566 l_upload_param_list := TAB_NAME ||'_UPL';
567 l_integrator := TAB_NAME ||'_INTG';
568 l_stmt := 'update bne_integrators_b set UPLOAD_PARAM_LIST_APP_ID=271, UPLOAD_PARAM_LIST_CODE= :1
569 where integrator_code = :2 and APPLICATION_ID = 271';
570 EXECUTE IMMEDIATE L_STMT USING l_upload_param_list, l_integrator;
571 END;
572
573
574 /*---------------------------------------------------------------------------
575 API to create a Duplicate Profile
576 Parameter List Code = <table_name>_REP / _ERR
577 ----------------------------------------------------------------------------*/
578 PROCEDURE CREATE_DUP_PROFILE( TAB_NAME VARCHAR2 )
579 IS
580 l_stmt VARCHAR2(3000);
584 l_seq_num VARCHAR2(10);
581 l_non_uniq_cols VARCHAR2(3000);
582 TYPE curType IS REF CURSOR ;
583 c_non_uniq_cols curType;
585 l_interface_code VARCHAR2(100);
586 BEGIN
587 l_non_uniq_cols :=
588 'SELECT SEQUENCE_NUM FROM BNE_INTERFACE_COLS_B
589 WHERE APPLICATION_ID=271 AND INTERFACE_CODE= :1 AND SEQUENCE_NUM >1
590 AND SEQUENCE_NUM NOT IN(SELECT INTERFACE_SEQ_NUM
591 FROM BNE_INTERFACE_KEY_COLS WHERE INTERFACE_APP_ID=271 AND INTERFACE_CODE= :2)
592 order by SEQUENCE_NUM';
593 l_interface_code := tab_name||'_INTF';
594
595 --Error Profile
596 l_stmt := 'INSERT INTO BNE_DUPLICATE_PROFILES_B( APPLICATION_ID, DUP_PROFILE_CODE, OBJECT_VERSION_NUMBER,
597 INTEGRATOR_APP_ID, INTEGRATOR_CODE,
598 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
599 271,'''|| TAB_NAME ||'_ERR'',1,271,'''|| TAB_NAME ||'_INTG'',sysdate, 2, sysdate , 2, 0)';
600 EXECUTE IMMEDIATE L_STMT;
601
602 -- Duplicate Profile TL
603 l_stmt := 'INSERT INTO BNE_DUPLICATE_PROFILES_TL(APPLICATION_ID, DUP_PROFILE_CODE, LANGUAGE, SOURCE_LANG, USER_NAME,
604 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
605 271,'''|| TAB_NAME ||'_ERR'',USERENV(''LANG''),USERENV(''LANG''),
606 BSC_WEB_ADI_LOADER_PKG.get_lookup_value(''BSC_PMA_WEBADI_UPL_ERR'',''ERROR_DUP''),sysdate, 2, sysdate, 2, 0)';
607 EXECUTE IMMEDIATE L_STMT;
608
609 -- Associating Duplicate Profile with Interface list
610 l_stmt := 'INSERT INTO BNE_DUP_INTERFACE_PROFILES(INTERFACE_APP_ID, INTERFACE_CODE, DUP_PROFILE_APP_ID,
611 DUP_PROFILE_CODE, OBJECT_VERSION_NUMBER, DUP_HANDLING_CODE, DEFAULT_RESOLVER_CLASSNAME,
612 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
613 271,'''|| TAB_NAME ||'_INTF'',271,'''|| TAB_NAME ||'_ERR'',1,''ROW_ERROR'',
614 NULL, sysdate, 2, sysdate, 2, 0)';
615 EXECUTE IMMEDIATE L_STMT;
616
617 --Replace Profile
618 l_stmt := 'INSERT INTO BNE_DUPLICATE_PROFILES_B( APPLICATION_ID, DUP_PROFILE_CODE, OBJECT_VERSION_NUMBER,
619 INTEGRATOR_APP_ID, INTEGRATOR_CODE,
620 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
621 271,'''|| TAB_NAME ||'_REP'',1,271,'''|| TAB_NAME ||'_INTG'',sysdate, 2, sysdate , 2, 0)';
622 EXECUTE IMMEDIATE L_STMT;
623
624 -- Duplicate Profile TL
625 l_stmt := 'INSERT INTO BNE_DUPLICATE_PROFILES_TL(APPLICATION_ID, DUP_PROFILE_CODE, LANGUAGE, SOURCE_LANG, USER_NAME,
626 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
627 271,'''|| TAB_NAME ||'_REP'',USERENV(''LANG''),USERENV(''LANG''),
628 BSC_WEB_ADI_LOADER_PKG.get_lookup_value(''BSC_PMA_WEBADI_UPL_REP'',''REPLACE_DUP''),sysdate, 2, sysdate, 2, 0)'; --BSC_LOOKUPS
629 EXECUTE IMMEDIATE L_STMT;
630
631 -- Associating Duplicate Profile with Interface list
632 l_stmt := 'INSERT INTO BNE_DUP_INTERFACE_PROFILES(INTERFACE_APP_ID, INTERFACE_CODE, DUP_PROFILE_APP_ID,
633 DUP_PROFILE_CODE, OBJECT_VERSION_NUMBER, DUP_HANDLING_CODE, DEFAULT_RESOLVER_CLASSNAME,
634 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
635 271,'''|| TAB_NAME ||'_INTF'',271,'''|| TAB_NAME ||'_REP'',1,''TRANSFORM'',
636 ''oracle.apps.bne.integrator.upload.BneKeepResolver'', sysdate, 2, sysdate, 2, 0)';
637 EXECUTE IMMEDIATE L_STMT;
638
639 -- Associating Non-unique columns for updation
640 OPEN c_non_uniq_cols FOR l_non_uniq_cols USING l_interface_code, l_interface_code;
641 LOOP
642 FETCH c_non_uniq_cols INTO l_seq_num;
643 EXIT WHEN c_non_uniq_cols%NOTFOUND;
644 l_stmt := 'INSERT INTO BNE_DUP_INTERFACE_COLS( INTERFACE_APP_ID, INTERFACE_CODE, INTERFACE_SEQ_NUM,
645 DUP_PROFILE_APP_ID, DUP_PROFILE_CODE, OBJECT_VERSION_NUMBER, RESOLVER_CLASSNAME,
646 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
647 271,'''|| TAB_NAME ||'_INTF'','||l_seq_num||',271, '''|| TAB_NAME ||'_REP'',
648 1,''oracle.apps.bne.integrator.upload.BneReplaceResolver'', sysdate, 2, sysdate, 2, 0)';
649 EXECUTE IMMEDIATE L_STMT;
650 END LOOP;
651 CLOSE c_non_uniq_cols;
652 END;
653
654 /*---------------------------------------------------------------------------
655 API to create a Interface Keys
656 Interface Key Code = <table_name>_UK
657 ----------------------------------------------------------------------------*/
658 FUNCTION CREATE_INTERFACE_KEYS( TAB_NAME VARCHAR2 ) RETURN NUMBER
659 IS
660 l_stmt VARCHAR2(3000);
661 CURSOR unique_ind_col is
662 SELECT COLUMN_POSITION+1 Interface_seq
663 FROM ALL_INDEXES i, ALL_IND_COLUMNS c
664 WHERE i.TABLE_OWNER=g_bsc_schema AND i.TABLE_NAME=TAB_NAME AND
665 i.UNIQUENESS ='UNIQUE' AND i.OWNER=c.INDEX_OWNER AND I.INDEX_NAME = C.INDEX_NAME;
666 key_cols NUMBER;
667 BEGIN
668 key_cols := 0;
669 -- Associate the Columns in the Unique index to the Unique Key created for an Interface
670 FOR unique_ind_col_rec IN unique_ind_col LOOP
671 IF (key_cols = 0) THEN
672 -- Create the Unique Key for an Interface
673 l_stmt := 'INSERT INTO BNE_INTERFACE_KEYS( APPLICATION_ID, KEY_CODE, OBJECT_VERSION_NUMBER, INTERFACE_APP_ID,
674 INTERFACE_CODE, KEY_TYPE, KEY_CLASS,
675 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
676 271,'''|| TAB_NAME ||'_UK'',1,271,'''|| TAB_NAME ||'_INTF'',''DUP_UNIQUE'',
680 l_stmt := 'INSERT INTO BNE_INTERFACE_KEY_COLS( APPLICATION_ID, KEY_CODE, SEQUENCE_NUM, OBJECT_VERSION_NUMBER,
677 ''oracle.apps.bne.integrator.upload.BneTableInterfaceKey'',sysdate, 2, sysdate , 2, 0)';
678 EXECUTE IMMEDIATE L_STMT;
679 END IF;
681 INTERFACE_APP_ID, INTERFACE_CODE, INTERFACE_SEQ_NUM,
682 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) VALUES (
683 271,'''|| TAB_NAME ||'_UK'','||to_char(unique_ind_col_rec.Interface_seq-1) ||', 1, 271, '''|| TAB_NAME ||'_INTF'','
684 ||to_char(unique_ind_col_rec.Interface_seq)||',sysdate, 2, sysdate , 2, 0)';
685 EXECUTE IMMEDIATE L_STMT;
686 key_cols := key_Cols + 1;
687 END LOOP;
688 RETURN key_cols;
689 END;
690
691 /*---------------------------------------------------------------------------
692 API to clear all the WebADI metadata for application=BSC and pertaining to
693 a particular Interface Table
694 ----------------------------------------------------------------------------*/
695 PROCEDURE clear_metadata( TAB_NAME VARCHAR2 )
696 IS
697 l_stmt VARCHAR2(3000);
698 l_code VARCHAR2(50);
699 l_code_2 VARCHAR2(50);
700 BEGIN
701 l_code := TAB_NAME||'_INTG';
702 l_stmt := 'delete bne_integrators_b where application_id = 271 and INTEGRATOR_CODE = :1';
703 EXECUTE IMMEDIATE L_STMT USING l_code;
704
705 l_stmt := 'delete bne_integrators_tl
706 where application_id = 271 and INTEGRATOR_CODE = :1' ;
707 EXECUTE IMMEDIATE L_STMT USING l_code;
708
709 l_stmt := 'delete BNE_SECURED_OBJECTS
710 where application_id = 271 and OBJECT_CODE = :1' ;
711 EXECUTE IMMEDIATE L_STMT USING l_code;
712
713 l_code := TAB_NAME||'_SEC';
714
715 l_stmt := 'delete BNE_SECURITY_RULES
716 where application_id = 271 and SECURITY_CODE = :1' ;
717 EXECUTE IMMEDIATE L_STMT USING l_code;
718
719 l_code := TAB_NAME||'_INTF';
720
721 l_stmt := 'delete bne_interfaces_b
722 where application_id = 271 and INTERFACE_CODE = :1' ;
723 EXECUTE IMMEDIATE L_STMT USING l_code;
724
725 l_stmt := 'delete bne_interfaces_tl
726 where application_id = 271 and INTERFACE_CODE = :1' ;
727 EXECUTE IMMEDIATE L_STMT USING l_code;
728
729 l_stmt := 'delete bne_interface_cols_b
730 where application_id = 271 and INTERFACE_CODE = :1' ;
731 EXECUTE IMMEDIATE L_STMT USING l_code;
732
733 l_stmt := 'delete bne_interface_cols_tl
734 where application_id = 271 and INTERFACE_CODE = :1' ;
735 EXECUTE IMMEDIATE L_STMT USING l_code;
736
737 l_code := TAB_NAME||'_L';
738
739 l_stmt := 'delete bne_layouts_b
740 where application_id = 271 and LAYOUT_CODE = :1' ;
741 EXECUTE IMMEDIATE L_STMT USING l_code;
742
743 l_stmt := 'delete bne_layouts_tl
744 where application_id = 271 and LAYOUT_CODE = :1' ;
745 EXECUTE IMMEDIATE L_STMT USING l_code;
746
747 l_stmt := 'delete bne_layout_blocks_b
748 where application_id = 271 and LAYOUT_CODE = :1' ;
749 EXECUTE IMMEDIATE L_STMT USING l_code;
750
751 l_stmt := 'delete bne_layout_blocks_tl
752 where application_id = 271 and LAYOUT_CODE = :1' ;
753 EXECUTE IMMEDIATE L_STMT USING l_code;
754
755 l_stmt := 'delete bne_layout_cols
756 where application_id = 271 and LAYOUT_CODE = :1' ;
757 EXECUTE IMMEDIATE L_STMT USING l_code;
758
759 l_code := TAB_NAME||'_CNT';
760
761 l_stmt := 'delete BNE_CONTENTS_b
762 where application_id = 271 and CONTENT_CODE = :1' ;
763 EXECUTE IMMEDIATE L_STMT USING l_code;
764
765 l_stmt := 'delete BNE_CONTENTS_TL
766 where application_id = 271 and CONTENT_CODE = :1' ;
767 EXECUTE IMMEDIATE L_STMT USING l_code;
768
769 l_code := TAB_NAME||'_MAP';
770
771 l_stmt := 'delete BNE_MAPPINGS_B
772 where application_id = 271 and MAPPING_CODE = :1' ;
773 EXECUTE IMMEDIATE L_STMT USING l_code;
774
775 l_stmt := 'delete BNE_MAPPINGS_TL
776 where application_id = 271 and MAPPING_CODE = :1' ;
777 EXECUTE IMMEDIATE L_STMT USING l_code;
778
779 l_stmt := 'delete BNE_MAPPING_LINES
780 where application_id = 271 and MAPPING_CODE = :1' ;
781 EXECUTE IMMEDIATE L_STMT USING l_code;
782
783 l_code := TAB_NAME||'_Q';
784
785 l_stmt := 'delete BNE_RAW_QUERY
786 where application_id = 271 and QUERY_CODE = :1' ;
787 EXECUTE IMMEDIATE L_STMT USING l_code;
788
789 l_stmt := 'delete BNE_QUERIES_B
790 where application_id = 271 and QUERY_CODE = :1' ;
791 EXECUTE IMMEDIATE L_STMT USING l_code;
792
793 l_stmt := 'delete BNE_QUERIES_TL
794 where application_id = 271 and QUERY_CODE = :1' ;
795 EXECUTE IMMEDIATE L_STMT USING l_code;
796
797 l_code := TAB_NAME||'_ROW';
798 l_code_2 := TAB_NAME||'_DUP';
799
800 l_stmt := 'delete BNE_PARAM_DEFNS_B
801 where application_id = 271 and (PARAM_DEFN_CODE = :1 OR
802 PARAM_DEFN_CODE = :2 OR
803 PARAM_DEFN_CODE = ''OBJECT_TYPE'' OR
804 PARAM_DEFN_CODE = ''OBJECT_ID'' OR
805 PARAM_DEFN_CODE = ''TABLE_NAME'' OR
809
806 PARAM_DEFN_CODE = ''QUERY_TIME'')' ;
807
808 EXECUTE IMMEDIATE L_STMT USING l_code, l_code_2;
810 l_stmt := 'delete BNE_PARAM_DEFNS_TL
811 where application_id = 271 and (PARAM_DEFN_CODE = :1 OR
812 PARAM_DEFN_CODE = :2 OR
813 PARAM_DEFN_CODE = ''OBJECT_TYPE'' OR
814 PARAM_DEFN_CODE = ''OBJECT_ID'' OR
815 PARAM_DEFN_CODE = ''TABLE_NAME'' OR
816 PARAM_DEFN_CODE = ''QUERY_TIME'')' ;
817 EXECUTE IMMEDIATE L_STMT USING l_code, l_code_2;
818
819 l_code := TAB_NAME||'_UPL';
820
821 l_stmt := 'delete BNE_PARAM_LISTS_B
822 where application_id = 271 and PARAM_LIST_CODE = :1' ;
823 EXECUTE IMMEDIATE L_STMT USING l_code;
824
825 l_stmt := 'delete BNE_PARAM_LISTS_TL
826 where application_id = 271 and PARAM_LIST_CODE = :1' ;
827 EXECUTE IMMEDIATE L_STMT USING l_code;
828
829 l_stmt := 'delete BNE_PARAM_LIST_ITEMS
830 where application_id = 271 and PARAM_LIST_CODE = :1' ;
831 EXECUTE IMMEDIATE L_STMT USING l_code;
832
833 l_code := TAB_NAME||'_REP';
834 l_code_2 := TAB_NAME||'_ERR';
835
836 l_stmt := 'delete BNE_DUPLICATE_PROFILES_B
837 where application_id = 271 and (DUP_PROFILE_CODE = :1
838 or DUP_PROFILE_CODE = :2)' ;
839 EXECUTE IMMEDIATE L_STMT USING l_code, l_code_2;
840
841 l_stmt := 'delete BNE_DUPLICATE_PROFILES_TL
842 where application_id = 271 and (DUP_PROFILE_CODE = :1
843 or DUP_PROFILE_CODE = :2)' ;
844 EXECUTE IMMEDIATE L_STMT USING l_code, l_code_2;
845
846 l_code := TAB_NAME||'_INTF';
847
848 l_stmt := 'delete BNE_DUP_INTERFACE_PROFILES
849 where INTERFACE_APP_ID = 271 and INTERFACE_CODE = :1' ;
850 EXECUTE IMMEDIATE L_STMT USING l_code;
851
852 l_stmt := 'delete BNE_DUP_INTERFACE_COLS
853 where INTERFACE_APP_ID = 271 and INTERFACE_CODE = :1' ;
854 EXECUTE IMMEDIATE L_STMT USING l_code;
855
856 l_code := TAB_NAME||'_UK';
857
858 l_stmt := 'delete BNE_INTERFACE_KEYS
859 where application_id = 271 and KEY_CODE = :1' ;
860 EXECUTE IMMEDIATE L_STMT USING l_code;
861
862 l_stmt := 'delete BNE_INTERFACE_KEY_COLS
863 where application_id = 271 and KEY_CODE = :1' ;
864 EXECUTE IMMEDIATE L_STMT USING l_code;
865
866 END;
867
868 /*---------------------------------------------------------------------------
869 API to get the BSC Schema name
870 ----------------------------------------------------------------------------*/
871 FUNCTION get_bsc_schema return varchar2 is
872 dummy1 VARCHAR2(32) := null;
873 dummy2 VARCHAR2(32) := null;
874 l_bsc_schema VARCHAR2(32) := null;
875 begin
876 IF (FND_INSTALLATION.GET_APP_INFO('BSC', dummy1, dummy2, l_bsc_schema)) THEN
877 NULL;
878 END IF;
879 return l_bsc_schema;
880 end;
881
882 /*---------------------------------------------------------------------------
883 API to get the BSC Lookups
884 ----------------------------------------------------------------------------*/
885 FUNCTION get_lookup_value(type VARCHAR2, code VARCHAR2) return varchar2 is
886 lookup_value VARCHAR2(80);
887 BEGIN
888 SELECT meaning
889 INTO lookup_value
893 RETURN lookup_value;
890 FROM bsc_lookups
891 WHERE APPLICATION_ID=271 and lookup_type = type AND lookup_code = code ;
892
894 END;
895
896 /*---------------------------------------------------------------------------
897 API to create all the WebADI metadata for application=BSC and pertaining to
898 a particular Interface Table, this api will be called from the JAVA layer
899 ----------------------------------------------------------------------------*/
900 PROCEDURE Create_Metadata(TAB_NAME VARCHAR2, ERRBUF OUT NOCOPY VARCHAR2,RETCODE OUT NOCOPY VARCHAR2)
901 IS
902 key_cols NUMBER;
903 BEGIN
904 g_bsc_schema := get_bsc_schema();
905 CLEAR_METADATA(TAB_NAME); --Clear
906 CREATE_INTEGRATOR(TAB_NAME);
907 CREATE_INTERFACE(TAB_NAME);
908 CREATE_INTERFACE_COLUMNS(TAB_NAME);
909 CREATE_LAYOUT(TAB_NAME);
910 CREATE_CONTENT(TAB_NAME, ERRBUF,RETCODE);
911 IF RETCODE <> FND_API.G_RET_STS_SUCCESS THEN
912 RETURN;
913 END IF;
914 CREATE_MAPPING(TAB_NAME);
915 key_cols := CREATE_INTERFACE_KEYS( TAB_NAME );
916 IF (key_cols > 0) then
917 CREATE_QUERY(TAB_NAME);
918 CREATE_DUP_PROFILE( TAB_NAME);
919 END IF;
920 CREATE_PARAM_DEFN( TAB_NAME, key_cols );
921 CREATE_PARAM_LIST( TAB_NAME, key_cols );
922 COMMIT;
923 RETCODE := FND_API.G_RET_STS_SUCCESS;
924 ERRBUF := ' ';
925 null;
926 END;
927
928
929
930 /*---------------------------------------------------------------------------
931 API to clear all the WebADI metadata for application=BSC.
932 Used during development
933 ----------------------------------------------------------------------------*/
934 /*PROCEDURE clear_all_metadata
935 IS
936 BEGIN
937 delete bne_integrators_b where application_id = 271;
938 delete bne_integrators_tl where application_id = 271;
939 delete BNE_SECURITY_RULES WHERE application_id = 271;
940 delete BNE_SECURED_OBJECTS WHERE application_id = 271;
941 delete bne_interfaces_b where application_id = 271;
942 delete bne_interfaces_tl where application_id = 271;
943 delete bne_interface_cols_b where application_id = 271;
944 delete bne_interface_cols_tl where application_id = 271;
945 delete bne_layouts_b where application_id = 271;
946 delete bne_layouts_tl where application_id = 271;
947 delete bne_contents_b where application_id = 271;
948 delete bne_contents_tl where application_id = 271;
949 delete bne_layout_blocks_b where application_id = 271;
950 delete bne_layout_blocks_tl where application_id = 271;
951 delete bne_layout_cols where application_id = 271;
952 END;*/
953
954 END BSC_WEB_ADI_LOADER_PKG;