[Home] [Help]
PACKAGE BODY: APPS.FEM_WEBADI_MEMBER_UTILS_PVT
Source
1 PACKAGE BODY FEM_WEBADI_MEMBER_UTILS_PVT AS
2 /* $Header: FEMVADIMEMBUTILB.pls 120.3.12010000.2 2008/12/08 13:30:03 lkiran ship $ */
3
4 ------------------------------
5 -- Declare Global variables --
6 ------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FEM_WEBADI_MEMBER_UTILS_PVT' ;
8 --
9 g_session_language VARCHAR2(30) ;
10 --
11 g_cal_pr_end_date_col_value VARCHAR2(30) ;
12 g_cal_pr_num_col_name_value VARCHAR2(30) ;
13 g_date_end_date_value DATE;
14 --
15 --------------------------
16 -- Declare Object types --
17 --------------------------
18 --
19 -- Initialized table to clean above two collections.
20 g_initialized_table FND_TABLE_OF_VARCHAR2_30
21 := FND_TABLE_OF_VARCHAR2_30() ;
22 --
23 -- Declare variables to hold Map table column values.
24 g_dim_varchar_label_tbl FND_TABLE_OF_VARCHAR2_30
25 := FND_TABLE_OF_VARCHAR2_30() ;
26 g_interface_col_name_tbl FND_TABLE_OF_VARCHAR2_30
27 := FND_TABLE_OF_VARCHAR2_30() ;
28 g_attribute_name_tbl FND_TABLE_OF_VARCHAR2_30
29 := FND_TABLE_OF_VARCHAR2_30() ;
30 g_attribute_data_type_tbl FND_TABLE_OF_VARCHAR2_30
31 := FND_TABLE_OF_VARCHAR2_30() ;
32 g_not_null_attr_name_tbl FND_TABLE_OF_VARCHAR2_30
33 := FND_TABLE_OF_VARCHAR2_30() ;
34 TYPE
35 g_not_null_attr_val_type
36 IS TABLE OF VARCHAR2(4000)
37 INDEX BY PLS_INTEGER ;
38
39 g_not_null_attr_val_tbl g_not_null_attr_val_type ;
40
41 -- Bug#6446663 - Begin
42 g_attribute_vs_display_code FND_TABLE_OF_VARCHAR2_255
43 := FND_TABLE_OF_VARCHAR2_255() ;
44 g_version_display_code FND_TABLE_OF_VARCHAR2_255
45 := FND_TABLE_OF_VARCHAR2_255() ;
46 -- Bug#6446663 - End
47 --
48 --
49 /*===========================================================================+
50 | PROCEDURE pd |
51 +===========================================================================*/
52 -- API to print debug information used during only development.
53 PROCEDURE pd( p_message IN VARCHAR2)
54 IS
55 BEGIN
56 --DBMS_OUTPUT.Put_Line(p_message) ;
57 null;
58 END pd ;
59 /*---------------------------------------------------------------------------*/
60
61 /*===========================================================================+
62 | PROCEDURE log_m |
63 +===========================================================================*/
64 -- API to insert debug information in autonomous transaction mode.
65 -- Meant for development purposes only.
66 PROCEDURE log_m
67 ( p_debug_message IN VARCHAR2
68 )
69 IS
70 --
71 PRAGMA AUTONOMOUS_TRANSACTION ;
72 --
73 BEGIN
74 --
75 --INSERT INTO psbtest1 VALUES (test_seq.nextval, test_seq.nextval, p_debug_message) ;
76 NULL ;
77 --
78 --COMMIT ;
79 --
80 END ;
81 /*---------------------------------------------------------------------------*/
82
83 PROCEDURE Populate_Dim_Intf_Common_Cols (
84 p_api_version IN NUMBER ,
85 p_init_msg_list IN VARCHAR2,
86 p_commit IN VARCHAR2,
87 x_return_status OUT NOCOPY VARCHAR2,
88 x_msg_count OUT NOCOPY NUMBER ,
89 x_msg_data OUT NOCOPY VARCHAR2,
90 p_interface_code IN VARCHAR2,
91 p_dimension_varchar_label IN VARCHAR2,
92 p_group_use_code IN VARCHAR2
93 )
94 IS
95 --
96 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Dim_Intf_Common_Cols';
97 l_api_version CONSTANT NUMBER := 1.0;
98 --
99 TYPE l_interface_col_rec IS RECORD
100 ( INTERFACE_COL_NAME VARCHAR2(30) ,
101 INTERFACE_COL_TYPE NUMBER(15) ,
102 DISPLAY_FLAG VARCHAR2(1) ,
103 READ_ONLY_FLAG VARCHAR2(1) ,
104 DATA_TYPE NUMBER(15) ,
105 FIELD_SIZE NUMBER(15) ,
106 SEGMENT_VALUE NUMBER(15) ,
107 GROUP_NAME VARCHAR2(30) ,
108 VAL_TYPE VARCHAR2(20) ,
109 VAL_ID_COL VARCHAR2(240) ,
110 VAL_MEAN_COL VARCHAR2(240) ,
111 VAL_DESC_COL VARCHAR2(240) ,
112 VAL_OBJ_NAME VARCHAR2(240) ,
113 VAL_ADDL_W_C VARCHAR2(2000),
114 VAL_COMPONENT_APP_ID NUMBER(15) ,
115 VAL_COMPONENT_CODE VARCHAR2(30) ,
116 DISPLAY_ORDER NUMBER(15) ,
117 UPLOAD_PARAM_LIST_ITEM_NUM NUMBER(15) ,
118 SEQUENCE_NUM NUMBER(15) ,
119 LOV_TYPE VARCHAR2(30) ,
120 OFFLINE_LOV_ENABLED_FLAG VARCHAR2(1) ,
121 FND_MESSAGE_NAME VARCHAR2(30) ,
122 USER_HINT_FND_MESSAGE_NAME VARCHAR2(30)
123 );
124
125 TYPE l_interface_cols_typ IS TABLE OF l_interface_col_rec
126 INDEX BY BINARY_INTEGER;
127
128 l_interface_cols_tbl l_interface_cols_typ;
129 l_user_id NUMBER(15) := 2; -- (user name : initial setup)
130 l_login_id NUMBER := NVL(Fnd_Global.Login_Id, 0);
131
132 BEGIN
133
134 --
135 SAVEPOINT Dim_Intf_Common_Cols_Pvt ;
136 --
137 IF NOT FND_API.Compatible_API_Call ( l_api_version,
138 p_api_version,
139 l_api_name,
140 G_PKG_NAME )
141 THEN
142 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
143 END IF;
144 --
145
146 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
147 FND_MSG_PUB.initialize ;
148 END IF;
149 --
150 x_return_status := FND_API.G_RET_STS_SUCCESS ;
151 --
152
153 -----------------------------------------------------------------------------
154 -- Set up plsql table for interface column definition
155 -----------------------------------------------------------------------------
156
157 -----------------------------------------------------------------------------
158 -- Set up for column P_INTERFACE_DIMENSION_NAME
159 -----------------------------------------------------------------------------
160 l_interface_cols_tbl(1).INTERFACE_COL_NAME := 'P_INTERFACE_DIMENSION_NAME';
161 l_interface_cols_tbl(1).INTERFACE_COL_TYPE := 1;
162 l_interface_cols_tbl(1).DISPLAY_FLAG := 'Y';
163 l_interface_cols_tbl(1).READ_ONLY_FLAG := 'Y';
164 l_interface_cols_tbl(1).DATA_TYPE := 2; --VARCHAR
165 l_interface_cols_tbl(1).FIELD_SIZE := 80;
166 l_interface_cols_tbl(1).SEGMENT_VALUE := NULL;
167 l_interface_cols_tbl(1).GROUP_NAME := NULL;
168 l_interface_cols_tbl(1).VAL_TYPE := NULL;
169 l_interface_cols_tbl(1).VAL_ID_COL := NULL;
170 l_interface_cols_tbl(1).VAL_MEAN_COL := NULL;
171 l_interface_cols_tbl(1).VAL_DESC_COL := NULL;
172 l_interface_cols_tbl(1).VAL_OBJ_NAME := NULL;
173 l_interface_cols_tbl(1).VAL_ADDL_W_C := NULL;
174 l_interface_cols_tbl(1).VAL_COMPONENT_APP_ID := NULL;
175 l_interface_cols_tbl(1).VAL_COMPONENT_CODE := NULL;
176 l_interface_cols_tbl(1).DISPLAY_ORDER := 10; -- sequence_num * 10
177 l_interface_cols_tbl(1).UPLOAD_PARAM_LIST_ITEM_NUM := 1;
178 l_interface_cols_tbl(1).SEQUENCE_NUM := 1;
179 l_interface_cols_tbl(1).LOV_TYPE := NULL;
180 l_interface_cols_tbl(1).OFFLINE_LOV_ENABLED_FLAG := NULL;
181 l_interface_cols_tbl(1).FND_MESSAGE_NAME := 'FEM_ADI_DIMENSION_NAME';
182 l_interface_cols_tbl(1).USER_HINT_FND_MESSAGE_NAME := NULL;
183
184 -----------------------------------------------------
185 -- Set up for column P_DIMENSION_VARCHAR_LABEL
186 -----------------------------------------------------
187 l_interface_cols_tbl(2).INTERFACE_COL_NAME := 'P_DIMENSION_VARCHAR_LABEL';
188 l_interface_cols_tbl(2).INTERFACE_COL_TYPE := 1;
189 l_interface_cols_tbl(2).DISPLAY_FLAG := 'Y';
190 l_interface_cols_tbl(2).READ_ONLY_FLAG := 'Y';
191 l_interface_cols_tbl(2).DATA_TYPE := 2;
192 l_interface_cols_tbl(2).FIELD_SIZE := 30;
193 l_interface_cols_tbl(2).SEGMENT_VALUE := NULL;
194 l_interface_cols_tbl(2).GROUP_NAME := NULL;
195 l_interface_cols_tbl(2).VAL_TYPE := NULL;
196 l_interface_cols_tbl(2).VAL_ID_COL := NULL;
197 l_interface_cols_tbl(2).VAL_MEAN_COL := NULL;
198 l_interface_cols_tbl(2).VAL_DESC_COL := NULL;
199 l_interface_cols_tbl(2).VAL_OBJ_NAME := NULL;
200 l_interface_cols_tbl(2).VAL_ADDL_W_C := NULL;
201 l_interface_cols_tbl(2).VAL_COMPONENT_APP_ID := NULL;
202 l_interface_cols_tbl(2).VAL_COMPONENT_CODE := NULL;
203 l_interface_cols_tbl(2).DISPLAY_ORDER := 20; -- sequence_num * 10
204 l_interface_cols_tbl(2).UPLOAD_PARAM_LIST_ITEM_NUM := 2;
205 l_interface_cols_tbl(2).SEQUENCE_NUM := 2;
206 l_interface_cols_tbl(2).LOV_TYPE := NULL;
207 l_interface_cols_tbl(2).OFFLINE_LOV_ENABLED_FLAG := NULL;
208 l_interface_cols_tbl(2).FND_MESSAGE_NAME := 'FEM_ADI_DIMENSION_VARCHAR_LBL';
209 l_interface_cols_tbl(2).USER_HINT_FND_MESSAGE_NAME := NULL;
210
211 -----------------------------------------------------------------------------
212 -- Set up for column MEMBER_GROUP_VALIDATOR
213 -----------------------------------------------------------------------------
214 l_interface_cols_tbl(3).INTERFACE_COL_NAME := 'MEMBER_GROUP_VALIDATOR';
215 l_interface_cols_tbl(3).INTERFACE_COL_TYPE := 2;
216 l_interface_cols_tbl(3).DISPLAY_FLAG := 'N';
217 l_interface_cols_tbl(3).READ_ONLY_FLAG := 'N';
218 l_interface_cols_tbl(3).DATA_TYPE := 2; -- VARCHAR
219 l_interface_cols_tbl(3).FIELD_SIZE := 1;
220 l_interface_cols_tbl(3).SEGMENT_VALUE := 1;
221 l_interface_cols_tbl(3).GROUP_NAME := 'MEMBER_GROUP_VALIDATOR';
222 l_interface_cols_tbl(3).VAL_TYPE := 'GROUP';
223 l_interface_cols_tbl(3).VAL_ID_COL := NULL;
224 l_interface_cols_tbl(3).VAL_MEAN_COL := NULL;
225 l_interface_cols_tbl(3).VAL_DESC_COL := NULL;
226 l_interface_cols_tbl(3).VAL_OBJ_NAME := 'oracle.apps.fem.integrator.dimension.validators.FemMemberGroupValidator';
227 l_interface_cols_tbl(3).VAL_ADDL_W_C := NULL;
228 l_interface_cols_tbl(3).VAL_COMPONENT_APP_ID := NULL;
229 l_interface_cols_tbl(3).VAL_COMPONENT_CODE := NULL;
230 l_interface_cols_tbl(3).DISPLAY_ORDER := NULL;
231 l_interface_cols_tbl(3).UPLOAD_PARAM_LIST_ITEM_NUM := NULL;
232 l_interface_cols_tbl(3).SEQUENCE_NUM := 3;
233 l_interface_cols_tbl(3).LOV_TYPE := 'NONE';
234 l_interface_cols_tbl(3).OFFLINE_LOV_ENABLED_FLAG := 'N';
235 l_interface_cols_tbl(3).FND_MESSAGE_NAME := NULL;
236 l_interface_cols_tbl(3).USER_HINT_FND_MESSAGE_NAME := NULL;
237
238 -----------------------------------------------------------------------------
239 -- Set up for column P_LEDGER_ID
240 -----------------------------------------------------------------------------
241 l_interface_cols_tbl(4).INTERFACE_COL_NAME := 'P_LEDGER_ID';
242 l_interface_cols_tbl(4).INTERFACE_COL_TYPE := 1;
243 l_interface_cols_tbl(4).DISPLAY_FLAG := 'Y';
244 l_interface_cols_tbl(4).READ_ONLY_FLAG := 'N';
245 l_interface_cols_tbl(4).DATA_TYPE := 1; -- NUMBER
246 l_interface_cols_tbl(4).FIELD_SIZE := 150;
247 l_interface_cols_tbl(4).SEGMENT_VALUE := 4;
248 l_interface_cols_tbl(4).GROUP_NAME := 'MEMBER_GROUP_VALIDATOR';
249 l_interface_cols_tbl(4).VAL_TYPE := 'TABLE';
250 l_interface_cols_tbl(4).VAL_ID_COL := 'LEDGER_ID';
251 l_interface_cols_tbl(4).VAL_MEAN_COL := 'LEDGER_NAME';
252 l_interface_cols_tbl(4).VAL_DESC_COL := 'DESCRIPTION';
253 l_interface_cols_tbl(4).VAL_OBJ_NAME := 'FEM_LEDGERS_VL';
254 l_interface_cols_tbl(4).VAL_ADDL_W_C := 'ENABLED_FLAG = ''Y'' AND PERSONAL_FLAG = ''N''';
255 l_interface_cols_tbl(4).VAL_COMPONENT_APP_ID := 274;
256 l_interface_cols_tbl(4).VAL_COMPONENT_CODE := 'FEM_LEDGER';
257 l_interface_cols_tbl(4).DISPLAY_ORDER := 40;
258 l_interface_cols_tbl(4).UPLOAD_PARAM_LIST_ITEM_NUM := 3;
259 l_interface_cols_tbl(4).SEQUENCE_NUM := 4;
260 l_interface_cols_tbl(4).LOV_TYPE := 'POPLIST';
261 l_interface_cols_tbl(4).OFFLINE_LOV_ENABLED_FLAG := 'Y';
262 l_interface_cols_tbl(4).FND_MESSAGE_NAME := 'FEM_ADI_LEDGER_NAME';
263 l_interface_cols_tbl(4).USER_HINT_FND_MESSAGE_NAME := 'FEM_ADI_USER_HINT_LOV_REQ';
264
265 -----------------------------------------------------------------------------
266 -- Set up for column P_CALENDAR_DISPLAY_CODE
267 -----------------------------------------------------------------------------
268 l_interface_cols_tbl(5).INTERFACE_COL_NAME := 'P_CALENDAR_DISPLAY_CODE';
269 l_interface_cols_tbl(5).INTERFACE_COL_TYPE := 1;
270 l_interface_cols_tbl(5).DISPLAY_FLAG := 'Y';
271 l_interface_cols_tbl(5).READ_ONLY_FLAG := 'N';
272 l_interface_cols_tbl(5).DATA_TYPE := 2; --VARCHAR
273 l_interface_cols_tbl(5).FIELD_SIZE := 150;
274 l_interface_cols_tbl(5).SEGMENT_VALUE := 5;
275 l_interface_cols_tbl(5).GROUP_NAME := 'MEMBER_GROUP_VALIDATOR';
276 l_interface_cols_tbl(5).VAL_TYPE := 'TABLE';
277 l_interface_cols_tbl(5).VAL_ID_COL := 'CALENDAR_DISPLAY_CODE';
278 l_interface_cols_tbl(5).VAL_MEAN_COL := 'CALENDAR_NAME';
279 l_interface_cols_tbl(5).VAL_DESC_COL := 'DESCRIPTION';
280 l_interface_cols_tbl(5).VAL_OBJ_NAME := 'FEM_CALENDARS_VL';
281 l_interface_cols_tbl(5).VAL_ADDL_W_C := 'ENABLED_FLAG = ''Y'' AND PERSONAL_FLAG = ''N''';
282 l_interface_cols_tbl(5).VAL_COMPONENT_APP_ID := 274;
283 l_interface_cols_tbl(5).VAL_COMPONENT_CODE := 'FEM_CALENDAR';
284 l_interface_cols_tbl(5).DISPLAY_ORDER := 50;
285 l_interface_cols_tbl(5).UPLOAD_PARAM_LIST_ITEM_NUM := 4;
286 l_interface_cols_tbl(5).SEQUENCE_NUM := 5;
287 l_interface_cols_tbl(5).LOV_TYPE := 'POPLIST';
288 l_interface_cols_tbl(5).OFFLINE_LOV_ENABLED_FLAG := 'Y';
289 l_interface_cols_tbl(5).FND_MESSAGE_NAME := 'FEM_ADI_CALENDAR_NAME';
290 l_interface_cols_tbl(5).USER_HINT_FND_MESSAGE_NAME := 'FEM_ADI_USER_HINT_LOV_REQ';
291
292 -----------------------------------------------------------------------------
293 -- Set up for column P_MEMBER_NAME
294 -----------------------------------------------------------------------------
295 l_interface_cols_tbl(6).INTERFACE_COL_NAME := 'P_MEMBER_NAME';
296 l_interface_cols_tbl(6).INTERFACE_COL_TYPE := 1;
297 l_interface_cols_tbl(6).DISPLAY_FLAG := 'Y';
298 l_interface_cols_tbl(6).READ_ONLY_FLAG := 'N';
299 l_interface_cols_tbl(6).DATA_TYPE := 2;
300 l_interface_cols_tbl(6).FIELD_SIZE := 150;
301 l_interface_cols_tbl(6).SEGMENT_VALUE := 6;
302 l_interface_cols_tbl(6).GROUP_NAME := 'MEMBER_GROUP_VALIDATOR';
303 l_interface_cols_tbl(6).VAL_TYPE := NULL;
304 l_interface_cols_tbl(6).VAL_ID_COL := NULL;
305 l_interface_cols_tbl(6).VAL_MEAN_COL := NULL;
306 l_interface_cols_tbl(6).VAL_DESC_COL := NULL;
307 l_interface_cols_tbl(6).VAL_OBJ_NAME := NULL;
308 l_interface_cols_tbl(6).VAL_ADDL_W_C := NULL;
309 l_interface_cols_tbl(6).VAL_COMPONENT_APP_ID := NULL;
310 l_interface_cols_tbl(6).VAL_COMPONENT_CODE := NULL;
311 l_interface_cols_tbl(6).DISPLAY_ORDER := 60;
312 l_interface_cols_tbl(6).UPLOAD_PARAM_LIST_ITEM_NUM := 5;
313 l_interface_cols_tbl(6).SEQUENCE_NUM := 6;
314 l_interface_cols_tbl(6).LOV_TYPE := NULL;
315 l_interface_cols_tbl(6).OFFLINE_LOV_ENABLED_FLAG := NULL;
316 l_interface_cols_tbl(6).FND_MESSAGE_NAME := 'FEM_ADI_MEMBER_NAME';
317 l_interface_cols_tbl(6).USER_HINT_FND_MESSAGE_NAME := 'FEM_ADI_USER_HINT_TEXT_REQ';
318
319 -----------------------------------------------------------------------------
320 -- Set up for column P_MEMBER_DISPLAY_CODE
321 -----------------------------------------------------------------------------
322 l_interface_cols_tbl(7).INTERFACE_COL_NAME := 'P_MEMBER_DISPLAY_CODE';
323 l_interface_cols_tbl(7).INTERFACE_COL_TYPE := 1;
324 l_interface_cols_tbl(7).DISPLAY_FLAG := 'Y';
325 l_interface_cols_tbl(7).READ_ONLY_FLAG := 'N';
326 l_interface_cols_tbl(7).DATA_TYPE := 2;
327 l_interface_cols_tbl(7).FIELD_SIZE := 150;
328 l_interface_cols_tbl(7).SEGMENT_VALUE := 7;
329 l_interface_cols_tbl(7).GROUP_NAME := 'MEMBER_GROUP_VALIDATOR';
330 l_interface_cols_tbl(7).VAL_TYPE := NULL;
331 l_interface_cols_tbl(7).VAL_ID_COL := NULL;
332 l_interface_cols_tbl(7).VAL_MEAN_COL := NULL;
333 l_interface_cols_tbl(7).VAL_DESC_COL := NULL;
334 l_interface_cols_tbl(7).VAL_OBJ_NAME := NULL;
335 l_interface_cols_tbl(7).VAL_ADDL_W_C := NULL;
336 l_interface_cols_tbl(7).VAL_COMPONENT_APP_ID := NULL;
337 l_interface_cols_tbl(7).VAL_COMPONENT_CODE := NULL;
338 l_interface_cols_tbl(7).DISPLAY_ORDER := 70;
339 l_interface_cols_tbl(7).UPLOAD_PARAM_LIST_ITEM_NUM := 6;
340 l_interface_cols_tbl(7).SEQUENCE_NUM := 7;
341 l_interface_cols_tbl(7).LOV_TYPE := NULL;
342 l_interface_cols_tbl(7).OFFLINE_LOV_ENABLED_FLAG := NULL;
343 l_interface_cols_tbl(7).FND_MESSAGE_NAME := 'FEM_ADI_MEMBER_CODE';
344 l_interface_cols_tbl(7).USER_HINT_FND_MESSAGE_NAME := 'FEM_ADI_USER_HINT_TEXT_REQ';
345
346 -----------------------------------------------------------------------------
347 -- Set up for column P_MEMBER_DESCRIPTION
348 -----------------------------------------------------------------------------
349 l_interface_cols_tbl(8).INTERFACE_COL_NAME := 'P_MEMBER_DESCRIPTION';
350 l_interface_cols_tbl(8).INTERFACE_COL_TYPE := 1;
351 l_interface_cols_tbl(8).DISPLAY_FLAG := 'Y';
352 l_interface_cols_tbl(8).READ_ONLY_FLAG := 'N';
353 l_interface_cols_tbl(8).DATA_TYPE := 2;
354 l_interface_cols_tbl(8).FIELD_SIZE := 255;
355 l_interface_cols_tbl(8).SEGMENT_VALUE := NULL;
356 l_interface_cols_tbl(8).GROUP_NAME := NULL;
357 l_interface_cols_tbl(8).VAL_TYPE := NULL;
358 l_interface_cols_tbl(8).VAL_ID_COL := NULL;
359 l_interface_cols_tbl(8).VAL_MEAN_COL := NULL;
360 l_interface_cols_tbl(8).VAL_DESC_COL := NULL;
361 l_interface_cols_tbl(8).VAL_OBJ_NAME := NULL;
362 l_interface_cols_tbl(8).VAL_ADDL_W_C := NULL;
363 l_interface_cols_tbl(8).VAL_COMPONENT_APP_ID := NULL;
364 l_interface_cols_tbl(8).VAL_COMPONENT_CODE := NULL;
365 l_interface_cols_tbl(8).DISPLAY_ORDER := 80;
366 l_interface_cols_tbl(8).UPLOAD_PARAM_LIST_ITEM_NUM := 7;
367 l_interface_cols_tbl(8).SEQUENCE_NUM := 8;
368 l_interface_cols_tbl(8).LOV_TYPE := NULL;
369 l_interface_cols_tbl(8).OFFLINE_LOV_ENABLED_FLAG := NULL;
370 l_interface_cols_tbl(8).FND_MESSAGE_NAME := 'FEM_ADI_DESCRIPTION';
371 l_interface_cols_tbl(8).USER_HINT_FND_MESSAGE_NAME := 'FEM_ADI_USER_HINT_TEXT';
372
373 -----------------------------------------------------------------------------
374 -- Set up for column P_DIMENSION_GROUP_DISPLAY_CODE
375 -----------------------------------------------------------------------------
376 l_interface_cols_tbl(9).INTERFACE_COL_NAME := 'P_DIMENSION_GROUP_DISPLAY_CODE';
377 l_interface_cols_tbl(9).INTERFACE_COL_TYPE := 1;
378 l_interface_cols_tbl(9).DISPLAY_FLAG := 'Y';
379 l_interface_cols_tbl(9).READ_ONLY_FLAG := 'N';
380 l_interface_cols_tbl(9).DATA_TYPE := 2;
381 l_interface_cols_tbl(9).FIELD_SIZE := 80;
382 l_interface_cols_tbl(9).SEGMENT_VALUE := 9;
383 l_interface_cols_tbl(9).GROUP_NAME := 'MEMBER_GROUP_VALIDATOR';
384 l_interface_cols_tbl(9).VAL_TYPE := 'JAVA';
385 l_interface_cols_tbl(9).VAL_ID_COL := NULL;
386 l_interface_cols_tbl(9).VAL_MEAN_COL := NULL;
387 l_interface_cols_tbl(9).VAL_DESC_COL := NULL;
388 l_interface_cols_tbl(9).VAL_OBJ_NAME := 'oracle.apps.fem.integrator.dimension.validators.FemDimGroupValidator';
389 l_interface_cols_tbl(9).VAL_ADDL_W_C := NULL;
390 l_interface_cols_tbl(9).VAL_COMPONENT_APP_ID := 274;
391 l_interface_cols_tbl(9).VAL_COMPONENT_CODE := 'FEM_DIM_GROUP';
392 l_interface_cols_tbl(9).DISPLAY_ORDER := 90;
393 l_interface_cols_tbl(9).UPLOAD_PARAM_LIST_ITEM_NUM := 8;
394 l_interface_cols_tbl(9).SEQUENCE_NUM := 9;
395 l_interface_cols_tbl(9).LOV_TYPE := 'STANDARD';
396 l_interface_cols_tbl(9).OFFLINE_LOV_ENABLED_FLAG := 'N';
397 l_interface_cols_tbl(9).FND_MESSAGE_NAME := 'FEM_ADI_LEVEL_NAME';
398 IF (p_group_use_code = 'REQUIRED') THEN
399 l_interface_cols_tbl(9).USER_HINT_FND_MESSAGE_NAME := 'FEM_ADI_USER_HINT_LOV_REQ';
400 ELSE
401 l_interface_cols_tbl(9).USER_HINT_FND_MESSAGE_NAME := 'FEM_ADI_USER_HINT_LOV';
402 END IF;
403 /*
404 -----------------------------------------------------------------------------
405 -- Set up for column
406 -----------------------------------------------------------------------------
407 l_interface_cols_tbl().INTERFACE_COL_NAME :=
408 l_interface_cols_tbl().INTERFACE_COL_TYPE := 1;
409 l_interface_cols_tbl().DISPLAY_FLAG :=
410 l_interface_cols_tbl().READ_ONLY_FLAG :=
411 l_interface_cols_tbl().DATA_TYPE :=
412 l_interface_cols_tbl().FIELD_SIZE :=
413 l_interface_cols_tbl().SEGMENT_VALUE :=
414 l_interface_cols_tbl().GROUP_NAME :=
415 l_interface_cols_tbl().VAL_TYPE :=
416 l_interface_cols_tbl().VAL_ID_COL :=
417 l_interface_cols_tbl().VAL_MEAN_COL :=
418 l_interface_cols_tbl().VAL_DESC_COL :=
419 l_interface_cols_tbl().VAL_OBJ_NAME :=
420 l_interface_cols_tbl().VAL_ADDL_W_C :=
421 l_interface_cols_tbl().VAL_COMPONENT_APP_ID :=
422 l_interface_cols_tbl().VAL_COMPONENT_CODE :=
423 l_interface_cols_tbl().DISPLAY_ORDER :=
424 l_interface_cols_tbl().UPLOAD_PARAM_LIST_ITEM_NUM :=
425 l_interface_cols_tbl().SEQUENCE_NUM :=
426 l_interface_cols_tbl().LOV_TYPE := ;
427 l_interface_cols_tbl().OFFLINE_LOV_ENABLED_FLAG := ;
428 l_interface_cols_tbl().FND_MESSAGE_NAME :=
429 l_interface_cols_tbl().USER_HINT_FND_MESSAGE_NAME := ;
430 */
431 -----------------------------------------------------------------------------
432 -- Inserting into BNE_INTERFACE_COLS and BNE_INTERFACE_COLS_TL
433 -----------------------------------------------------------------------------
434 FOR i IN l_interface_cols_tbl.FIRST .. l_interface_cols_tbl.LAST
435 LOOP
436 INSERT INTO BNE_INTERFACE_COLS_B (
437 INTERFACE_COL_TYPE,
438 INTERFACE_COL_NAME,
439 ENABLED_FLAG,
440 REQUIRED_FLAG,
441 DISPLAY_FLAG,
442 READ_ONLY_FLAG,
443 NOT_NULL_FLAG,
444 SUMMARY_FLAG,
445 MAPPING_ENABLED_FLAG,
446 DATA_TYPE,
447 FIELD_SIZE,
448 DEFAULT_TYPE,
449 DEFAULT_VALUE,
450 SEGMENT_NUMBER,
451 GROUP_NAME,
452 OA_FLEX_CODE,
453 OA_CONCAT_FLEX,
454 VAL_TYPE,
455 VAL_ID_COL,
456 VAL_MEAN_COL,
457 VAL_DESC_COL,
458 VAL_OBJ_NAME,
459 VAL_ADDL_W_C,
460 VAL_COMPONENT_APP_ID,
461 VAL_COMPONENT_CODE,
462 OA_FLEX_NUM,
463 OA_FLEX_APPLICATION_ID,
464 DISPLAY_ORDER,
465 UPLOAD_PARAM_LIST_ITEM_NUM,
466 EXPANDED_SQL_QUERY,
467 APPLICATION_ID,
468 INTERFACE_CODE,
469 OBJECT_VERSION_NUMBER,
470 SEQUENCE_NUM,
471 LOV_TYPE,
472 OFFLINE_LOV_ENABLED_FLAG,
473 CREATION_DATE,
474 CREATED_BY,
475 LAST_UPDATE_DATE,
476 LAST_UPDATED_BY,
477 LAST_UPDATE_LOGIN,
478 VARIABLE_DATA_TYPE_CLASS
479 )
480 VALUES
481 ( l_interface_cols_tbl(i).INTERFACE_COL_TYPE,
482 l_interface_cols_tbl(i).INTERFACE_COL_NAME,
483 'Y',
484 'N',
485 l_interface_cols_tbl(i).DISPLAY_FLAG,
486 l_interface_cols_tbl(i).READ_ONLY_FLAG,
487 'Y',
488 'N',
489 'N',
490 l_interface_cols_tbl(i).DATA_TYPE,
491 l_interface_cols_tbl(i).FIELD_SIZE,
492 NULL,
493 NULL,
494 l_interface_cols_tbl(i).SEGMENT_VALUE,
495 l_interface_cols_tbl(i).GROUP_NAME,
496 NULL,
497 NULL,
498 l_interface_cols_tbl(i).VAL_TYPE,
499 l_interface_cols_tbl(i).VAL_ID_COL,
500 l_interface_cols_tbl(i).VAL_MEAN_COL,
501 l_interface_cols_tbl(i).VAL_DESC_COL,
502 l_interface_cols_tbl(i).VAL_OBJ_NAME,
503 l_interface_cols_tbl(i).VAL_ADDL_W_C,
504 l_interface_cols_tbl(i).VAL_COMPONENT_APP_ID,
505 l_interface_cols_tbl(i).VAL_COMPONENT_CODE,
506 NULL,
507 NULL,
508 l_interface_cols_tbl(i).DISPLAY_ORDER,
509 l_interface_cols_tbl(i).UPLOAD_PARAM_LIST_ITEM_NUM,
510 NULL,
511 274,
512 p_interface_code,
513 1,
514 l_interface_cols_tbl(i).SEQUENCE_NUM,
515 l_interface_cols_tbl(i).LOV_TYPE,
516 l_interface_cols_tbl(i).OFFLINE_LOV_ENABLED_FLAG,
517 SYSDATE,
518 l_user_id,
519 SYSDATE,
520 l_user_id,
521 l_login_id,
522 NULL
523 );
524
525 INSERT INTO BNE_INTERFACE_COLS_TL (
526 CREATED_BY,
527 CREATION_DATE,
528 LAST_UPDATED_BY,
529 LAST_UPDATE_LOGIN,
530 LAST_UPDATE_DATE,
531 USER_HINT,
532 PROMPT_LEFT,
533 USER_HELP_TEXT,
534 PROMPT_ABOVE,
535 INTERFACE_CODE,
536 SEQUENCE_NUM,
537 APPLICATION_ID,
538 LANGUAGE,
539 SOURCE_LANG
540 )
541 SELECT l_user_id
542 , SYSDATE
543 , l_user_id
544 , l_login_id
545 , SYSDATE
546 , M2.MESSAGE_TEXT
547 , M1.MESSAGE_TEXT
548 , NULL
549 , M1.MESSAGE_TEXT
550 , p_interface_code
551 , l_interface_cols_tbl(i).SEQUENCE_NUM
552 , 274
553 , L.LANGUAGE_CODE
554 , USERENV('LANG')
555 FROM FND_NEW_MESSAGES M1,
556 FND_NEW_MESSAGES M2,
557 FND_LANGUAGES L
558 WHERE L.INSTALLED_FLAG IN ('I', 'B')
559 AND M1.MESSAGE_NAME (+)= l_interface_cols_tbl(i).FND_MESSAGE_NAME
560 AND M1.LANGUAGE_CODE (+)= L.LANGUAGE_CODE
561 AND M2.MESSAGE_NAME (+)= l_interface_cols_tbl(i).USER_HINT_FND_MESSAGE_NAME
562 AND M2.LANGUAGE_CODE (+)= L.LANGUAGE_CODE;
563
564 END LOOP;
565
566 IF ( FND_API.To_Boolean( p_char => p_commit) ) THEN
567 COMMIT;
568 END IF;
569
570
571 EXCEPTION
572 --
573 WHEN FND_API.G_EXC_ERROR THEN
574 --
575 ROLLBACK TO Dim_Intf_Common_Cols_Pvt ;
576 x_return_status := FND_API.G_RET_STS_ERROR;
577 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
578 p_data => x_msg_data );
579 --
580 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
581 --
582 ROLLBACK TO Dim_Intf_Common_Cols_Pvt ;
583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
584 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
585 p_data => x_msg_data );
586 --
587 WHEN OTHERS THEN
588 --
589 ROLLBACK TO Dim_Intf_Common_Cols_Pvt ;
590 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
591 --
592 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
593 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
594 l_api_name);
595 END if;
596 --
597 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
598 p_data => x_msg_data );
599 --
600 END Populate_Dim_Intf_Common_Cols;
601
602
603 PROCEDURE Populate_Dim_Intf_Attr_Cols (
604 p_api_version IN NUMBER ,
605 p_init_msg_list IN VARCHAR2,
606 p_commit IN VARCHAR2,
607 x_return_status OUT NOCOPY VARCHAR2,
608 x_msg_count OUT NOCOPY NUMBER ,
609 x_msg_data OUT NOCOPY VARCHAR2,
610 p_interface_code IN VARCHAR2,
611 p_dimension_varchar_label IN VARCHAR2,
612 p_dimension_id IN NUMBER ,
613 x_updated_flag OUT NOCOPY VARCHAR2
614 )
615 IS
616 --
617 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Dim_Intf_Attr_Cols';
618 l_api_version CONSTANT NUMBER := 1.0;
619 --
620
621 l_user_id NUMBER(15) := 2; -- (user name : initial setup)
622 l_login_id NUMBER := NVL(Fnd_Global.Login_Id, 0);
623 l_index NUMBER ;
624
625 l_updated_flag VARCHAR2(1) := 'N';
626 l_max_sequence_num BNE_INTERFACE_COLS_B.SEQUENCE_NUM%TYPE;
627 l_sequence_num BNE_INTERFACE_COLS_B.SEQUENCE_NUM%TYPE;
628 l_val_component_code BNE_INTERFACE_COLS_B.VAL_COMPONENT_CODE%TYPE;
629
630 TYPE l_sequence_num_tbl_typ IS TABLE OF
631 BNE_INTERFACE_COLS_B.SEQUENCE_NUM%TYPE INDEX BY BINARY_INTEGER;
632
633 TYPE l_data_type_tbl_typ IS TABLE OF
634 FEM_WEBADI_DIM_ATTR_MAPS.DATA_TYPE%TYPE INDEX BY BINARY_INTEGER;
635
636 TYPE l_attr_varchar_label_tbl_typ IS TABLE OF
637 FEM_WEBADI_DIM_ATTR_MAPS.ATTRIBUTE_VARCHAR_LABEL%TYPE
638 INDEX BY BINARY_INTEGER;
639
640 l_map_sequence_num_tbl l_sequence_num_tbl_typ;
641 l_map_data_type_tbl l_data_type_tbl_typ;
642 l_map_attr_var_label_tbl l_attr_varchar_label_tbl_typ;
643
644
645 l_attr_intf_col_name_tbl FND_TABLE_OF_VARCHAR2_30 := FND_TABLE_OF_VARCHAR2_30
646 ( 'P_ATTRIBUTE1'
647 , 'P_ATTRIBUTE2'
648 , 'P_ATTRIBUTE3'
649 , 'P_ATTRIBUTE4'
650 , 'P_ATTRIBUTE5'
651 , 'P_ATTRIBUTE6'
652 , 'P_ATTRIBUTE7'
653 , 'P_ATTRIBUTE8'
654 , 'P_ATTRIBUTE9'
655 , 'P_ATTRIBUTE10'
656 , 'P_ATTRIBUTE11'
657 , 'P_ATTRIBUTE12'
658 , 'P_ATTRIBUTE13'
659 , 'P_ATTRIBUTE14'
660 , 'P_ATTRIBUTE15'
661 , 'P_ATTRIBUTE16'
662 , 'P_ATTRIBUTE17'
663 , 'P_ATTRIBUTE18'
664 , 'P_ATTRIBUTE19'
665 , 'P_ATTRIBUTE20'
666 , 'P_ATTRIBUTE21'
667 , 'P_ATTRIBUTE22'
668 , 'P_ATTRIBUTE23'
669 , 'P_ATTRIBUTE24'
670 , 'P_ATTRIBUTE25'
671 , 'P_ATTRIBUTE26'
672 , 'P_ATTRIBUTE27'
673 , 'P_ATTRIBUTE28'
674 , 'P_ATTRIBUTE29'
675 , 'P_ATTRIBUTE30'
676 , 'P_ATTRIBUTE31'
677 , 'P_ATTRIBUTE32'
678 , 'P_ATTRIBUTE33'
679 , 'P_ATTRIBUTE34'
680 , 'P_ATTRIBUTE35'
681 , 'P_ATTRIBUTE36'
682 , 'P_ATTRIBUTE37'
683 , 'P_ATTRIBUTE38'
684 , 'P_ATTRIBUTE39'
685 , 'P_ATTRIBUTE40'
686 , 'P_ATTRIBUTE41'
687 , 'P_ATTRIBUTE42'
688 , 'P_ATTRIBUTE43'
689 , 'P_ATTRIBUTE44'
690 , 'P_ATTRIBUTE45'
691 , 'P_ATTRIBUTE46'
692 , 'P_ATTRIBUTE47'
693 , 'P_ATTRIBUTE48'
694 , 'P_ATTRIBUTE49'
695 , 'P_ATTRIBUTE50');
696 BEGIN
697
698 --
699 SAVEPOINT Dim_Intf_Attr_Cols_Pvt ;
700 --
701 IF NOT FND_API.Compatible_API_Call ( l_api_version,
702 p_api_version,
703 l_api_name,
704 G_PKG_NAME )
705 THEN
706 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
707 END IF;
708 --
709
710 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
711 FND_MSG_PUB.initialize ;
712 END IF;
713 --
714 x_return_status := FND_API.G_RET_STS_SUCCESS ;
715 --
716
717 x_updated_flag := 'N';
718
719 -----------------------------------------------------------------------------
720 -- Update the records that are not stored in the mapping tables
721 -----------------------------------------------------------------------------
722 -- Bulk collect l_map_sequence_num_tbl
723
724
725 IF (l_map_sequence_num_tbl.COUNT > 0) THEN
726
727 IF (x_updated_flag = 'N') THEN
728 x_updated_flag := 'Y';
729 END IF;
730 END IF;
731
732
733 FOR del_rec IN
734 (
735 SELECT I.SEQUENCE_NUM
736 , I.INTERFACE_COL_NAME
737 FROM BNE_LAYOUT_COLS L
738 , BNE_INTERFACE_COLS_B I
739 WHERE L.APPLICATION_ID = 274
740 AND L.INTERFACE_APP_ID = 274
741 AND L.INTERFACE_CODE = p_interface_code
742 AND I.APPLICATION_ID = L.INTERFACE_APP_ID
743 AND I.INTERFACE_CODE = L.INTERFACE_CODE
744 AND I.SEQUENCE_NUM = L.INTERFACE_SEQ_NUM
745 AND I.INTERFACE_COL_NAME LIKE 'P_ATTRIBUTE%'
746 AND NOT EXISTS
747 (
748 SELECT 1
749 FROM FEM_WEBADI_DIM_ATTR_MAPS M
750 WHERE M.DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label
751 AND M.INTERFACE_COL = I.INTERFACE_COL_NAME
752 )
753 )
754 LOOP
755
756 IF (x_updated_flag = 'N') THEN
757 x_updated_flag := 'Y';
758 END IF;
759
760 UPDATE BNE_INTERFACE_COLS_B
761 SET VAL_TYPE = NULL
762 , VAL_OBJ_NAME = NULL
763 , VAL_COMPONENT_APP_ID = NULL
764 , VAL_COMPONENT_CODE = NULL
765 , LOV_TYPE = NULL
766 , OFFLINE_LOV_ENABLED_FLAG = NULL
767 , SEGMENT_NUMBER = NULL
768 , GROUP_NAME = NULL
769 , VARIABLE_DATA_TYPE_CLASS = NULL
770 WHERE APPLICATION_ID = 274
771 AND INTERFACE_CODE = p_interface_code
772 AND SEQUENCE_NUM = del_rec.SEQUENCE_NUM
773 AND INTERFACE_COL_NAME IS NOT NULL;
774
775 IF (SQL%NOTFOUND) THEN
776 RAISE NO_DATA_FOUND;
777 END IF;
778
779 UPDATE BNE_INTERFACE_COLS_TL
780 SET USER_HINT = NULL
781 , PROMPT_LEFT = del_rec.INTERFACE_COL_NAME
782 , USER_HELP_TEXT = NULL
783 , PROMPT_ABOVE = del_rec.INTERFACE_COL_NAME
784 WHERE APPLICATION_ID = 274
785 AND INTERFACE_CODE = p_interface_code
786 AND SEQUENCE_NUM = del_rec.SEQUENCE_NUM;
787
788 END LOOP;
789
790
791 -----------------------------------------------------------------------------
792 -- Update those interface column whose data type has changed in
793 -- FEM_WEBADI_DIM_ATTR_MAPS table.
794 -----------------------------------------------------------------------------
795
796 IF (g_changed_dt_intf_col_tbl.COUNT > 0) THEN
797
798 IF (x_updated_flag = 'N') THEN
799 x_updated_flag := 'Y';
800 END IF;
801
802
803 FOR dt_changed_rec IN
804 (
805 SELECT REF.column_value AS INTERFACE_COL
806 , DATA_TYPE
807 , MAP.ATTRIBUTE_VARCHAR_LABEL
808 , ATTRIBUTE_REQUIRED_FLAG
809 FROM TABLE(CAST(g_changed_dt_intf_col_tbl AS FND_TABLE_OF_VARCHAR2_30)) REF
810 , FEM_WEBADI_DIM_ATTR_MAPS MAP
811 , FEM_DIM_ATTRIBUTES_B A
812 WHERE MAP.DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label
813 AND MAP.INTERFACE_COL = REF.column_value
814 AND A.DIMENSION_ID = p_dimension_id
815 AND A.ATTRIBUTE_VARCHAR_LABEL = MAP.ATTRIBUTE_VARCHAR_LABEL
816 )
817 LOOP
818
819 l_sequence_num := NULL;
820 l_val_component_code := NULL;
821
822 FOR intf_rec IN (
823 SELECT SEQUENCE_NUM
824 , VAL_COMPONENT_CODE
825 FROM BNE_INTERFACE_COLS_B
826 WHERE APPLICATION_ID = 274
827 AND INTERFACE_CODE = p_interface_code
828 AND INTERFACE_COL_NAME = dt_changed_rec.INTERFACE_COL
829 )
830 LOOP
831 l_sequence_num := intf_rec.SEQUENCE_NUM;
832 l_val_component_code := intf_rec.VAL_COMPONENT_CODE;
833 END LOOP;
834
835 -----------------------------------------------------------------------
836 -- Delete records in BNE_INTERFACE_COLS_TL and insert the new records.
837 -----------------------------------------------------------------------
838 DELETE BNE_INTERFACE_COLS_TL
839 WHERE APPLICATION_ID = 274
840 AND INTERFACE_CODE = p_interface_code
841 AND SEQUENCE_NUM = l_sequence_num;
842
843 INSERT INTO BNE_INTERFACE_COLS_TL
844 (
845 CREATED_BY,
846 CREATION_DATE,
847 LAST_UPDATED_BY,
848 LAST_UPDATE_LOGIN,
849 LAST_UPDATE_DATE,
850 USER_HINT,
851 PROMPT_LEFT,
852 USER_HELP_TEXT,
853 PROMPT_ABOVE,
854 INTERFACE_CODE,
855 SEQUENCE_NUM,
856 APPLICATION_ID,
857 LANGUAGE,
858 SOURCE_LANG
859 )
860 SELECT l_user_id
861 , SYSDATE
862 , l_user_id
863 , l_login_id
864 , SYSDATE
865 , M.MESSAGE_TEXT
866 , B.ATTRIBUTE_NAME
867 , NULL
868 , B.ATTRIBUTE_NAME
869 , p_interface_code
870 , l_sequence_num
871 , 274
872 , L.LANGUAGE_CODE
873 , B.SOURCE_LANG
874 FROM FEM_XDIM_DIMENSIONS_VL D
875 , FEM_DIM_ATTRIBUTES_B A
876 , FEM_DIM_ATTRIBUTES_TL B
877 , FND_NEW_MESSAGES M
878 , FND_LANGUAGES L
879 WHERE L.INSTALLED_FLAG IN ('I', 'B')
880 AND D.DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label
881 AND D.DIMENSION_ID = A.DIMENSION_ID
882 AND A.ATTRIBUTE_VARCHAR_LABEL = dt_changed_rec.ATTRIBUTE_VARCHAR_LABEL
883 AND A.ATTRIBUTE_ID = B.ATTRIBUTE_ID
884 AND B.LANGUAGE (+) = L.LANGUAGE_CODE
885 AND M.MESSAGE_NAME (+)=
886 DECODE(dt_changed_rec.DATA_TYPE, 'DIMENSION',
887 DECODE(dt_changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_LOV_REQ', 'FEM_ADI_USER_HINT_LOV'),
888 DECODE(dt_changed_rec.DATA_TYPE, 'VARCHAR2',
889 DECODE(dt_changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_TEXT_REQ', 'FEM_ADI_USER_HINT_TEXT'),
890 DECODE(dt_changed_rec.DATA_TYPE, 'NUMBER',
891 DECODE(dt_changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_NUMBER_REQ', 'FEM_ADI_USER_HINT_NUMBER'),
892 DECODE(dt_changed_rec.DATA_TYPE, 'DATE',
893 DECODE(dt_changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_DATE_REQ', 'FEM_ADI_USER_HINT_DATE'), NULL))))
894 AND M.LANGUAGE_CODE (+)= L.LANGUAGE_CODE;
895
896 IF (dt_changed_rec.DATA_TYPE = 'DIMENSION') THEN
897
898 UPDATE BNE_INTERFACE_COLS_B
899 SET VAL_TYPE = 'JAVA'
900 , VAL_OBJ_NAME = 'oracle.apps.fem.integrator.dimension.validators.FemAttributeValidator'
901 , VAL_COMPONENT_APP_ID = 274
902 , VAL_COMPONENT_CODE = 'FEM_ATTRIBUTE'
903 , LOV_TYPE = 'STANDARD'
904 , OFFLINE_LOV_ENABLED_FLAG = 'N'
905 , VARIABLE_DATA_TYPE_CLASS = NULL
906 WHERE APPLICATION_ID = 274
907 AND INTERFACE_CODE = p_interface_code
908 AND INTERFACE_COL_NAME = dt_changed_rec.INTERFACE_COL;
909
910 IF (SQL%NOTFOUND) THEN
911 RAISE NO_DATA_FOUND;
912 END IF;
913
914 ELSE
915 UPDATE BNE_INTERFACE_COLS_B
916 SET VAL_TYPE = NULL
917 , VAL_OBJ_NAME = NULL
918 , VAL_COMPONENT_APP_ID = NULL
919 , VAL_COMPONENT_CODE = NULL
920 , LOV_TYPE = NULL
921 , OFFLINE_LOV_ENABLED_FLAG = NULL
922 , VARIABLE_DATA_TYPE_CLASS =
923 DECODE(dt_changed_rec.DATA_TYPE, 'DATE',
924 'oracle.apps.fem.integrator.dimension.validators.FemAttributeDateTypeValidator',
925 DECODE(dt_changed_rec.DATA_TYPE, 'NUMBER',
926 'oracle.apps.fem.integrator.dimension.validators.FemAttributeNumericTypeValidator', NULL))
927 WHERE APPLICATION_ID = 274
928 AND INTERFACE_CODE = p_interface_code
929 AND INTERFACE_COL_NAME = dt_changed_rec.INTERFACE_COL;
930
931 IF (SQL%NOTFOUND) THEN
932 RAISE NO_DATA_FOUND;
933 END IF;
934
935 END IF;
936
937 END LOOP;
938 END IF;
939
940 -----------------------------------------------------------------------------
941 -- Upsert BNE_INTERFACE_COLS_B and BNE_INTERFACE_COLS_TL table based on the
942 -- data populated in the g_changed_intf_col_tbl that is generated through
943 -- Populate_Dim_Attribute_Maps
944 -----------------------------------------------------------------------------
945
946 -- The interface col will not be found when the metadata is populated for the
947 -- first time.
948 -- The interface col will always be found when there is metadata populated
949 -- before.
950
951 SELECT MAX(SEQUENCE_NUM)
952 INTO l_max_sequence_num
953 FROM BNE_INTERFACE_COLS_B
954 WHERE APPLICATION_ID = 274
955 AND INTERFACE_CODE = p_interface_code;
956
957 IF (g_changed_intf_col_tbl.COUNT > 0) THEN
958
959 SELECT MAX(SEQUENCE_NUM)
960 INTO l_max_sequence_num
961 FROM BNE_INTERFACE_COLS_B
962 WHERE APPLICATION_ID = 274
963 AND INTERFACE_CODE = p_interface_code;
964
965 IF (x_updated_flag = 'N') THEN
966 x_updated_flag := 'Y';
967 END IF;
968
969 FOR changed_rec IN
970 (
971 SELECT REF.column_value AS INTERFACE_COL
972 , DATA_TYPE
973 , MAP.ATTRIBUTE_VARCHAR_LABEL
974 , ATTRIBUTE_REQUIRED_FLAG
975 FROM TABLE(CAST(g_changed_intf_col_tbl AS FND_TABLE_OF_VARCHAR2_30)) REF
976 , FEM_WEBADI_DIM_ATTR_MAPS MAP
977 , FEM_DIM_ATTRIBUTES_B A
978 WHERE MAP.DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label
979 AND MAP.INTERFACE_COL = REF.column_value
980 AND A.DIMENSION_ID = p_dimension_id
981 AND A.ATTRIBUTE_VARCHAR_LABEL = MAP.ATTRIBUTE_VARCHAR_LABEL
982 )
983 LOOP
984 l_sequence_num := NULL;
985 l_val_component_code := NULL;
986
987 FOR intf_rec IN (
988 SELECT SEQUENCE_NUM
989 , VAL_COMPONENT_CODE
990 FROM BNE_INTERFACE_COLS_B
991 WHERE APPLICATION_ID = 274
992 AND INTERFACE_CODE = p_interface_code
993 AND INTERFACE_COL_NAME = changed_rec.INTERFACE_COL
994 )
995 LOOP
996 l_sequence_num := intf_rec.SEQUENCE_NUM;
997 l_val_component_code := intf_rec.VAL_COMPONENT_CODE;
998 END LOOP;
999
1000 IF (l_sequence_num is not null) THEN
1001
1002 -----------------------------------------------------------------------
1003 -- Delete records in BNE_INTERFACE_COLS_TL and insert the new records.
1004 -----------------------------------------------------------------------
1005 DELETE BNE_INTERFACE_COLS_TL
1006 WHERE APPLICATION_ID = 274
1007 AND INTERFACE_CODE = p_interface_code
1008 AND SEQUENCE_NUM = l_sequence_num;
1009
1010 INSERT INTO BNE_INTERFACE_COLS_TL
1011 (
1012 CREATED_BY,
1013 CREATION_DATE,
1014 LAST_UPDATED_BY,
1015 LAST_UPDATE_LOGIN,
1016 LAST_UPDATE_DATE,
1017 USER_HINT,
1018 PROMPT_LEFT,
1019 USER_HELP_TEXT,
1020 PROMPT_ABOVE,
1021 INTERFACE_CODE,
1022 SEQUENCE_NUM,
1023 APPLICATION_ID,
1024 LANGUAGE,
1025 SOURCE_LANG
1026 )
1027 SELECT l_user_id
1028 , SYSDATE
1029 , l_user_id
1030 , l_login_id
1031 , SYSDATE
1032 , M.MESSAGE_TEXT
1033 , B.ATTRIBUTE_NAME
1034 , NULL
1035 , B.ATTRIBUTE_NAME
1036 , p_interface_code
1037 , l_sequence_num
1038 , 274
1039 , L.LANGUAGE_CODE
1040 , B.SOURCE_LANG
1041 FROM FEM_XDIM_DIMENSIONS_VL D
1042 , FEM_DIM_ATTRIBUTES_B A
1043 , FEM_DIM_ATTRIBUTES_TL B
1044 , FND_NEW_MESSAGES M
1045 , FND_LANGUAGES L
1046 WHERE L.INSTALLED_FLAG IN ('I', 'B')
1047 AND D.DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label
1048 AND D.DIMENSION_ID = A.DIMENSION_ID
1049 AND A.ATTRIBUTE_VARCHAR_LABEL = changed_rec.ATTRIBUTE_VARCHAR_LABEL
1050 AND A.ATTRIBUTE_ID = B.ATTRIBUTE_ID
1051 AND B.LANGUAGE (+) = L.LANGUAGE_CODE
1052 AND M.MESSAGE_NAME (+)=
1053 DECODE(changed_rec.DATA_TYPE, 'DIMENSION',
1054 DECODE(changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_LOV_REQ', 'FEM_ADI_USER_HINT_LOV'),
1055 DECODE(changed_rec.DATA_TYPE, 'VARCHAR2',
1056 DECODE(changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_TEXT_REQ', 'FEM_ADI_USER_HINT_TEXT'),
1057 DECODE(changed_rec.DATA_TYPE, 'NUMBER',
1058 DECODE(changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_NUMBER_REQ', 'FEM_ADI_USER_HINT_NUMBER'),
1059 DECODE(changed_rec.DATA_TYPE, 'DATE',
1060 DECODE(changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_DATE_REQ', 'FEM_ADI_USER_HINT_DATE'), NULL))))
1061 AND M.LANGUAGE_CODE (+)= L.LANGUAGE_CODE;
1062
1063 ---------------------------------------------------------------------
1064 -- Update BNE_INTERFACE_COLS_B when required
1065 ---------------------------------------------------------------------
1066
1067 IF (changed_rec.DATA_TYPE = 'DIMENSION' AND
1068 (l_val_component_code IS NULL OR
1069 l_val_component_code <> 'FEM_ATTRIBUTE')) THEN
1070
1071 UPDATE BNE_INTERFACE_COLS_B
1072 SET VAL_TYPE = 'JAVA'
1073 , VAL_OBJ_NAME = 'oracle.apps.fem.integrator.dimension.validators.FemAttributeValidator'
1074 , VAL_COMPONENT_APP_ID = 274
1075 , VAL_COMPONENT_CODE = 'FEM_ATTRIBUTE'
1076 , LOV_TYPE = 'STANDARD'
1077 , OFFLINE_LOV_ENABLED_FLAG = 'N'
1078 , SEGMENT_NUMBER = 100 + ((UPLOAD_PARAM_LIST_ITEM_NUM -8) * 10)
1079 , GROUP_NAME = 'MEMBER_GROUP_VALIDATOR'
1080 , VARIABLE_DATA_TYPE_CLASS = NULL
1081 WHERE APPLICATION_ID = 274
1082 AND INTERFACE_CODE = p_interface_code
1083 AND SEQUENCE_NUM = l_sequence_num;
1084
1085 ELSIF (changed_rec.DATA_TYPE <> 'DIMENSION' AND l_val_component_code = 'FEM_ATTRIBUTE') THEN
1086
1087 UPDATE BNE_INTERFACE_COLS_B
1088 SET VAL_TYPE = NULL
1089 , VAL_OBJ_NAME = NULL
1090 , VAL_COMPONENT_APP_ID = NULL
1091 , VAL_COMPONENT_CODE = NULL
1092 , LOV_TYPE = NULL
1093 , OFFLINE_LOV_ENABLED_FLAG = NULL
1094 , SEGMENT_NUMBER = 100 + ((UPLOAD_PARAM_LIST_ITEM_NUM -8) * 10)
1095 , GROUP_NAME = 'MEMBER_GROUP_VALIDATOR'
1096 , VARIABLE_DATA_TYPE_CLASS =
1097 DECODE(changed_rec.DATA_TYPE, 'DATE',
1098 'oracle.apps.fem.integrator.dimension.validators.FemAttributeDateTypeValidator',
1099 DECODE(changed_rec.DATA_TYPE, 'NUMBER',
1100 'oracle.apps.fem.integrator.dimension.validators.FemAttributeNumericTypeValidator',
1101 NULL))
1102 WHERE APPLICATION_ID = 274
1103 AND INTERFACE_CODE = p_interface_code
1104 AND SEQUENCE_NUM = l_sequence_num;
1105
1106 ELSE
1107 -- Need to update the segment and group name because they are not
1108 -- previously set.
1109 UPDATE BNE_INTERFACE_COLS_B
1110 SET SEGMENT_NUMBER = 100 + ((UPLOAD_PARAM_LIST_ITEM_NUM -8) * 10)
1111 , GROUP_NAME = 'MEMBER_GROUP_VALIDATOR'
1112 , VARIABLE_DATA_TYPE_CLASS =
1113 DECODE(changed_rec.DATA_TYPE, 'DATE',
1114 'oracle.apps.fem.integrator.dimension.validators.FemAttributeDateTypeValidator',
1115 DECODE(changed_rec.DATA_TYPE, 'NUMBER',
1116 'oracle.apps.fem.integrator.dimension.validators.FemAttributeNumericTypeValidator',
1117 NULL))
1118 WHERE APPLICATION_ID = 274
1119 AND INTERFACE_CODE = p_interface_code
1120 AND SEQUENCE_NUM = l_sequence_num;
1121
1122 END IF;
1123
1124 ELSE
1125
1126 ------------------------------------------------------------------------
1127 -- Insert records to both BNE_INTERFACE_COLS_B and BNE_INTERFACE_COLS_TL
1128 ------------------------------------------------------------------------
1129 l_max_sequence_num := l_max_sequence_num + 1;
1130
1131 INSERT INTO BNE_INTERFACE_COLS_B (
1132 INTERFACE_COL_TYPE,
1133 INTERFACE_COL_NAME,
1134 ENABLED_FLAG,
1135 REQUIRED_FLAG,
1136 DISPLAY_FLAG,
1137 READ_ONLY_FLAG,
1138 NOT_NULL_FLAG,
1139 SUMMARY_FLAG,
1140 MAPPING_ENABLED_FLAG,
1141 DATA_TYPE,
1142 FIELD_SIZE,
1143 DEFAULT_TYPE,
1144 DEFAULT_VALUE,
1145 SEGMENT_NUMBER,
1146 GROUP_NAME,
1147 OA_FLEX_CODE,
1148 OA_CONCAT_FLEX,
1149 VAL_TYPE,
1150 VAL_ID_COL,
1151 VAL_MEAN_COL,
1152 VAL_DESC_COL,
1153 VAL_OBJ_NAME,
1154 VAL_ADDL_W_C,
1155 VAL_COMPONENT_APP_ID,
1156 VAL_COMPONENT_CODE,
1157 OA_FLEX_NUM,
1158 OA_FLEX_APPLICATION_ID,
1159 DISPLAY_ORDER,
1160 UPLOAD_PARAM_LIST_ITEM_NUM,
1161 EXPANDED_SQL_QUERY,
1162 APPLICATION_ID,
1163 INTERFACE_CODE,
1164 OBJECT_VERSION_NUMBER,
1165 SEQUENCE_NUM,
1166 LOV_TYPE,
1167 OFFLINE_LOV_ENABLED_FLAG,
1168 CREATION_DATE,
1169 CREATED_BY,
1170 LAST_UPDATE_DATE,
1171 LAST_UPDATED_BY,
1172 LAST_UPDATE_LOGIN,
1173 VARIABLE_DATA_TYPE_CLASS
1174 )
1175 VALUES
1176 ( 1,
1177 changed_rec.INTERFACE_COL,
1178 'Y',
1179 'N',
1180 'Y',
1181 'N',
1182 'Y',
1183 'N',
1184 'N',
1185 2,
1186 2000,
1187 NULL,
1188 NULL,
1189 100+(TO_NUMBER(SUBSTR(changed_rec.INTERFACE_COL, 12))*10),
1190 'MEMBER_GROUP_VALIDATOR',
1191 NULL,
1192 NULL,
1193 DECODE(changed_rec.DATA_TYPE, 'DIMENSION', 'JAVA', NULL),
1194 NULL,
1195 NULL ,
1196 NULL,
1197 DECODE(changed_rec.DATA_TYPE, 'DIMENSION', 'oracle.apps.fem.integrator.dimension.validators.FemAttributeValidator', NULL),
1198 NULL,
1199 DECODE(changed_rec.DATA_TYPE, 'DIMENSION', 274, NULL),
1200 DECODE(changed_rec.DATA_TYPE, 'DIMENSION', 'FEM_ATTRIBUTE', NULL),
1201 NULL,
1202 NULL,
1203 l_max_sequence_num*10,
1204 TO_NUMBER(SUBSTR(changed_rec.INTERFACE_COL, 12)) + 8,
1205 NULL,
1206 274,
1207 p_interface_code,
1208 1,
1209 l_max_sequence_num,
1210 DECODE(changed_rec.DATA_TYPE, 'DIMENSION', 'STANDARD', NULL),
1211 DECODE(changed_rec.DATA_TYPE, 'DIMENSION', 'N', NULL),
1212 SYSDATE,
1213 l_user_id,
1214 SYSDATE,
1215 l_user_id,
1216 l_login_id,
1217 DECODE(changed_rec.DATA_TYPE, 'DATE',
1218 'oracle.apps.fem.integrator.dimension.validators.FemAttributeDateTypeValidator',
1219 DECODE(changed_rec.DATA_TYPE, 'NUMBER',
1220 'oracle.apps.fem.integrator.dimension.validators.FemAttributeNumericTypeValidator',
1221 NULL))
1222 );
1223
1224 INSERT INTO BNE_INTERFACE_COLS_TL
1225 (
1226 CREATED_BY,
1227 CREATION_DATE,
1228 LAST_UPDATED_BY,
1229 LAST_UPDATE_LOGIN,
1230 LAST_UPDATE_DATE,
1231 USER_HINT,
1232 PROMPT_LEFT,
1233 USER_HELP_TEXT,
1234 PROMPT_ABOVE,
1235 INTERFACE_CODE,
1236 SEQUENCE_NUM,
1237 APPLICATION_ID,
1238 LANGUAGE,
1239 SOURCE_LANG
1240 )
1241 SELECT l_user_id
1242 , SYSDATE
1243 , l_user_id
1244 , l_login_id
1245 , SYSDATE
1246 , M.MESSAGE_TEXT
1247 , B.ATTRIBUTE_NAME
1248 , NULL
1249 , B.ATTRIBUTE_NAME
1250 , p_interface_code
1251 , l_max_sequence_num
1252 , 274
1253 , L.LANGUAGE_CODE
1254 , B.SOURCE_LANG
1255 FROM FEM_XDIM_DIMENSIONS_VL D
1256 , FEM_DIM_ATTRIBUTES_B A
1257 , FEM_DIM_ATTRIBUTES_TL B
1258 , FND_NEW_MESSAGES M
1259 , FND_LANGUAGES L
1260 WHERE L.INSTALLED_FLAG IN ('I', 'B')
1261 AND D.DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label
1262 AND D.DIMENSION_ID = A.DIMENSION_ID
1263 AND A.ATTRIBUTE_VARCHAR_LABEL = changed_rec.ATTRIBUTE_VARCHAR_LABEL
1264 AND A.ATTRIBUTE_ID = B.ATTRIBUTE_ID
1265 AND B.LANGUAGE (+) = L.LANGUAGE_CODE
1266 AND M.MESSAGE_NAME (+)=
1267 DECODE(changed_rec.DATA_TYPE, 'DIMENSION',
1268 DECODE(changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_LOV_REQ', 'FEM_ADI_USER_HINT_LOV'),
1269 DECODE(changed_rec.DATA_TYPE, 'VARCHAR2',
1270 DECODE(changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_TEXT_REQ', 'FEM_ADI_USER_HINT_TEXT'),
1271 DECODE(changed_rec.DATA_TYPE, 'NUMBER',
1272 DECODE(changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_NUMBER_REQ', 'FEM_ADI_USER_HINT_NUMBER'),
1273 DECODE(changed_rec.DATA_TYPE, 'DATE',
1274 DECODE(changed_rec.ATTRIBUTE_REQUIRED_FLAG, 'Y', 'FEM_ADI_USER_HINT_DATE_REQ', 'FEM_ADI_USER_HINT_DATE'), NULL))))
1275 AND M.LANGUAGE_CODE (+)= L.LANGUAGE_CODE;
1276
1277 if (SQL%NOTFOUND) then
1278 RAISE NO_DATA_FOUND;
1279 end if;
1280 END IF;
1281
1282 END LOOP;
1283 END IF;
1284
1285 -----------------------------------------------------------------------------
1286 -- Maintain remaining interface columns if they are missing
1287 -----------------------------------------------------------------------------
1288 FOR intf_rec IN (
1289 SELECT REF.column_value AS INTERFACE_COL_NAME
1290 FROM TABLE(CAST(l_attr_intf_col_name_tbl AS FND_TABLE_OF_VARCHAR2_30)) REF
1291 WHERE NOT EXISTS
1292 (
1293 SELECT INTERFACE_COL_NAME
1294 FROM BNE_INTERFACE_COLS_B
1295 WHERE APPLICATION_ID = 274
1296 AND INTERFACE_CODE = p_interface_code
1297 AND INTERFACE_COL_NAME = REF.column_value
1298 ))
1299 LOOP
1300
1301 l_max_sequence_num := l_max_sequence_num + 1;
1302
1303 INSERT INTO BNE_INTERFACE_COLS_B (
1304 INTERFACE_COL_TYPE,
1305 INTERFACE_COL_NAME,
1306 ENABLED_FLAG,
1307 REQUIRED_FLAG,
1308 DISPLAY_FLAG,
1309 READ_ONLY_FLAG,
1310 NOT_NULL_FLAG,
1311 SUMMARY_FLAG,
1312 MAPPING_ENABLED_FLAG,
1313 DATA_TYPE,
1314 FIELD_SIZE,
1315 DISPLAY_ORDER,
1316 UPLOAD_PARAM_LIST_ITEM_NUM,
1317 APPLICATION_ID,
1318 INTERFACE_CODE,
1319 OBJECT_VERSION_NUMBER,
1320 SEQUENCE_NUM,
1321 CREATION_DATE,
1322 CREATED_BY,
1323 LAST_UPDATE_DATE,
1324 LAST_UPDATED_BY,
1325 LAST_UPDATE_LOGIN
1326 )
1327 VALUES
1328 ( 1,
1329 intf_rec.INTERFACE_COL_NAME,
1330 'Y',
1331 'N',
1332 'Y',
1333 'N',
1334 'Y',
1335 'N',
1336 'N',
1337 2,
1338 2000,
1339 l_max_sequence_num*10,
1340 TO_NUMBER(SUBSTR(intf_rec.INTERFACE_COL_NAME, 12)) + 8,
1341 274,
1342 p_interface_code,
1343 1,
1344 l_max_sequence_num,
1345 SYSDATE,
1346 l_user_id,
1347 SYSDATE,
1348 l_user_id,
1349 l_login_id
1350 );
1351
1352 INSERT INTO BNE_INTERFACE_COLS_TL
1353 (
1354 CREATED_BY,
1355 CREATION_DATE,
1356 LAST_UPDATED_BY,
1357 LAST_UPDATE_LOGIN,
1358 LAST_UPDATE_DATE,
1359 USER_HINT,
1360 PROMPT_LEFT,
1361 USER_HELP_TEXT,
1362 PROMPT_ABOVE,
1363 INTERFACE_CODE,
1364 SEQUENCE_NUM,
1365 APPLICATION_ID,
1366 LANGUAGE,
1367 SOURCE_LANG
1368 )
1369 SELECT l_user_id
1370 , SYSDATE
1371 , l_user_id
1372 , l_login_id
1373 , SYSDATE
1374 , NULL
1375 , intf_rec.INTERFACE_COL_NAME
1376 , NULL
1377 , intf_rec.INTERFACE_COL_NAME
1378 , p_interface_code
1379 , l_max_sequence_num
1380 , 274
1381 , L.LANGUAGE_CODE
1382 , USERENV('LANG')
1383 FROM FND_LANGUAGES L
1384 WHERE L.INSTALLED_FLAG IN ('I', 'B');
1385
1386 END LOOP;
1387
1388 IF ( FND_API.To_Boolean( p_char => p_commit) ) THEN
1389 COMMIT;
1390 END IF;
1391
1392 EXCEPTION
1393 --
1394 WHEN FND_API.G_EXC_ERROR THEN
1395 --
1396 ROLLBACK TO Dim_Intf_Attr_Cols_Pvt ;
1397 x_return_status := FND_API.G_RET_STS_ERROR;
1398 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1399 p_data => x_msg_data );
1400 --
1401 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1402 --
1403 ROLLBACK TO Dim_Intf_Attr_Cols_Pvt ;
1404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1405 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1406 p_data => x_msg_data );
1407 --
1408 WHEN OTHERS THEN
1409 --
1410 ROLLBACK TO Dim_Intf_Attr_Cols_Pvt ;
1411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1412 --
1413 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1414 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1415 l_api_name);
1416 END if;
1417 --
1418 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1419 p_data => x_msg_data );
1420 --
1421 END Populate_Dim_Intf_Attr_Cols;
1422
1423
1424 PROCEDURE Populate_Dim_Layout (
1425 p_api_version IN NUMBER ,
1426 p_init_msg_list IN VARCHAR2,
1427 p_commit IN VARCHAR2,
1428 x_return_status OUT NOCOPY VARCHAR2,
1429 x_msg_count OUT NOCOPY NUMBER ,
1430 x_msg_data OUT NOCOPY VARCHAR2,
1431 p_integrator_code IN VARCHAR2,
1432 p_interface_code IN VARCHAR2,
1433 p_dimension_varchar_label IN VARCHAR2,
1434 p_dimension_name IN VARCHAR2,
1435 p_object_code IN VARCHAR2,
1436 p_dimension_type_code IN VARCHAR2,
1437 p_value_set_required_flag IN VARCHAR2,
1438 p_group_use_code IN VARCHAR2
1439 )
1440 IS
1441 --
1442 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Dim_Layout';
1443 l_api_version CONSTANT NUMBER := 1.0;
1444 --
1445 l_user_id NUMBER(15) := 2; -- (user name : initial setup)
1446 l_login_id NUMBER := NVL(Fnd_Global.Login_Id, 0);
1447
1448 l_existed_flag VARCHAR2(1) := 'N';
1449 l_layout_code BNE_LAYOUTS_B.LAYOUT_CODE%TYPE;
1450 l_header_block_id BNE_LAYOUT_BLOCKS_B.BLOCK_ID%TYPE;
1451 l_line_block_id BNE_LAYOUT_BLOCKS_B.BLOCK_ID%TYPE;
1452 l_interface_seq_num BNE_LAYOUT_COLS.SEQUENCE_NUM%TYPE;
1453
1454 l_header_block_cols FND_TABLE_OF_VARCHAR2_30 := FND_TABLE_OF_VARCHAR2_30
1455 ( 'P_DIMENSION_VARCHAR_LABEL'
1456 , 'MEMBER_GROUP_VALIDATOR'
1457 , 'P_LEDGER_ID'
1458 , 'P_CALENDAR_DISPLAY_CODE');
1459
1460 l_line_block_cols FND_TABLE_OF_VARCHAR2_30 := FND_TABLE_OF_VARCHAR2_30
1461 ( 'P_MEMBER_NAME'
1462 , 'P_MEMBER_DISPLAY_CODE'
1463 , 'P_MEMBER_DESCRIPTION'
1464 , 'P_DIMENSION_GROUP_DISPLAY_CODE');
1465
1466 l_default_ledger_query VARCHAR2(150) := 'select ledger_name from fem_ledgers_vl where ledger_id =
1467 (select FND_PROFILE.VALUE_SPECIFIC(''FEM_LEDGER'') from dual)'; -- Bug#5533480
1468
1469
1470 BEGIN
1471 --
1472 SAVEPOINT Dim_Layout_Pvt ;
1473 --
1474 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1475 p_api_version,
1476 l_api_name,
1477 G_PKG_NAME )
1478 THEN
1479 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1480 END IF;
1481 --
1482
1483 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
1484 FND_MSG_PUB.initialize ;
1485 END IF;
1486 --
1487 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1488 --
1489 l_layout_code := p_object_code || '_LAYOUT';
1490
1491 BEGIN
1492 SELECT 'Y'
1493 INTO l_existed_flag
1494 FROM BNE_LAYOUTS_B
1495 WHERE APPLICATION_ID = 274
1496 AND LAYOUT_CODE = l_layout_code;
1497 EXCEPTION
1498 WHEN NO_DATA_FOUND THEN NULL;
1499 END;
1500
1501 IF (l_existed_flag = 'N') THEN
1502 ---------------------------------------------------------------------------
1503 -- Create Layout in BNE_LAYOUTS_B and BNE_LAYOUTS_TL
1504 ---------------------------------------------------------------------------
1505 INSERT INTO BNE_LAYOUTS_B
1506 ( APPLICATION_ID
1507 , LAYOUT_CODE
1508 , OBJECT_VERSION_NUMBER
1509 , STYLESHEET_APP_ID
1510 , STYLESHEET_CODE
1511 , INTEGRATOR_APP_ID
1512 , INTEGRATOR_CODE
1513 , STYLE
1514 , STYLE_CLASS
1515 , REPORTING_FLAG
1516 , REPORTING_INTERFACE_APP_ID
1517 , REPORTING_INTERFACE_CODE
1518 , CREATION_DATE
1519 , CREATED_BY
1520 , LAST_UPDATE_DATE
1521 , LAST_UPDATED_BY
1522 , LAST_UPDATE_LOGIN
1523 , CREATE_DOC_LIST_APP_ID
1524 , CREATE_DOC_LIST_CODE
1525 )
1526 VALUES
1527 ( 274
1528 , l_layout_code
1529 , 1
1530 , 231
1531 , 'DEFAULT'
1532 , 274
1533 , p_integrator_code
1534 , NULL
1535 , 'BNE_PAGE'
1536 , 'N'
1537 , NULL
1538 , NULL
1539 , SYSDATE
1540 , l_user_id
1541 , SYSDATE
1542 , l_user_id
1543 , l_login_id
1544 , NULL
1545 , NULL
1546 );
1547
1548 INSERT INTO BNE_LAYOUTS_TL
1549 ( APPLICATION_ID
1550 , LAYOUT_CODE
1551 , USER_NAME
1552 , CREATED_BY
1553 , CREATION_DATE
1554 , LAST_UPDATED_BY
1555 , LAST_UPDATE_LOGIN
1556 , LAST_UPDATE_DATE
1557 , LANGUAGE
1558 , SOURCE_LANG
1559 )
1560 SELECT 274
1561 , l_layout_code
1562 , SUBSTR(M.MESSAGE_TEXT,0, INSTR(M.MESSAGE_TEXT, 'DIM_NAME')-2) ||
1563 DT.DIMENSION_NAME ||
1564 SUBSTR(M.MESSAGE_TEXT,INSTR(M.MESSAGE_TEXT, 'DIM_NAME')+8)
1565 , l_user_id
1566 , SYSDATE
1567 , l_user_id
1568 , l_login_id
1569 , SYSDATE
1570 , DT.LANGUAGE
1571 , DT.SOURCE_LANG
1572 FROM FEM_DIMENSIONS_TL DT
1573 , FEM_DIMENSIONS_B DB
1574 , FND_NEW_MESSAGES M
1575 , FND_LANGUAGES L
1576 WHERE DB.DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label
1577 AND DT.DIMENSION_ID = DB.DIMENSION_ID
1578 AND M.APPLICATION_ID= 274
1579 AND M.MESSAGE_NAME = 'FEM_ADI_MEMBER_LAYOUT'
1580 AND M.LANGUAGE_CODE = DT.LANGUAGE
1581 AND M.LANGUAGE_CODE = L.LANGUAGE_CODE
1582 AND L.INSTALLED_FLAG IN ('I', 'B');
1583
1584 END IF;
1585
1586 -----------------------------------------------------------------------------
1587 -- Creaate header block within the layout
1588 -----------------------------------------------------------------------------
1589 BEGIN
1590 SELECT B.BLOCK_ID
1591 INTO l_header_block_id
1592 FROM BNE_LAYOUT_BLOCKS_B B
1593 WHERE B.APPLICATION_ID = 274
1594 AND B.LAYOUT_CODE = l_layout_code
1595 AND B.LAYOUT_ELEMENT = 'HEADER';
1596 EXCEPTION
1597 WHEN NO_DATA_FOUND THEN NULL;
1598 END;
1599
1600 IF (l_header_block_id IS NULL) THEN
1601 ---------------------------------------------------------------------------
1602 -- Insert a new block into BNE_LAYOUT_BLOCKS_B
1603 ---------------------------------------------------------------------------
1604 l_header_block_id := 1;
1605
1606 INSERT INTO BNE_LAYOUT_BLOCKS_B
1607 ( APPLICATION_ID
1608 , LAYOUT_CODE
1609 , BLOCK_ID
1610 , OBJECT_VERSION_NUMBER
1611 , PARENT_ID
1612 , LAYOUT_ELEMENT
1613 , STYLE_CLASS
1614 , STYLE
1615 , ROW_STYLE_CLASS
1616 , ROW_STYLE
1617 , COL_STYLE_CLASS
1618 , COL_STYLE
1619 , PROMPT_DISPLAYED_FLAG
1620 , PROMPT_STYLE_CLASS
1621 , PROMPT_STYLE
1622 , HINT_DISPLAYED_FLAG
1623 , HINT_STYLE_CLASS
1624 , HINT_STYLE
1625 , ORIENTATION
1626 , LAYOUT_CONTROL
1627 , DISPLAY_FLAG
1628 , BLOCKSIZE
1629 , MINSIZE
1630 , MAXSIZE
1631 , SEQUENCE_NUM
1632 , PROMPT_COLSPAN
1633 , HINT_COLSPAN
1634 , ROW_COLSPAN
1635 , SUMMARY_STYLE_CLASS
1636 , SUMMARY_STYLE
1637 , CREATION_DATE
1638 , CREATED_BY
1639 , LAST_UPDATE_DATE
1640 , LAST_UPDATED_BY
1641 , LAST_UPDATE_LOGIN
1642 ) VALUES
1643 ( 274
1644 , l_layout_code
1645 , l_header_block_id
1646 , 1
1647 , NULL
1648 , 'HEADER'
1649 , 'BNE_HEADER'
1650 , NULL
1651 , 'BNE_HEADER_ROW'
1652 , NULL
1653 , NULL
1654 , NULL
1655 , 'Y'
1656 , 'BNE_HEADER_HEADER'
1657 , NULL
1658 , 'Y'
1659 , 'BNE_HEADER_HINT'
1660 , NULL
1661 , 'HORIZONTAL'
1662 , 'COLUMN_FLOW'
1663 , 'Y'
1664 , 1
1665 , 1
1666 , 1
1667 , 10
1668 , 3
1669 , 1
1670 , 2
1671 , 'BNE_LINES_TOTAL'
1672 , NULL
1673 , SYSDATE
1674 , l_user_id
1675 , SYSDATE
1676 , l_user_id
1677 , l_login_id
1678 );
1679
1680 INSERT INTO BNE_LAYOUT_BLOCKS_TL
1681 ( APPLICATION_ID
1682 , LAYOUT_CODE
1683 , BLOCK_ID
1684 , USER_NAME
1685 , CREATED_BY
1686 , CREATION_DATE
1687 , LAST_UPDATED_BY
1688 , LAST_UPDATE_LOGIN
1689 , LAST_UPDATE_DATE
1690 , LANGUAGE
1691 , SOURCE_LANG
1692 )
1693 SELECT 274
1694 , l_layout_code
1695 , l_header_block_id
1696 , M.MESSAGE_TEXT
1697 , l_user_id
1698 , SYSDATE
1699 , l_user_id
1700 , l_login_id
1701 , SYSDATE
1702 , L.LANGUAGE_CODE
1703 , USERENV('LANG')
1704 FROM FND_NEW_MESSAGES M,
1705 FND_LANGUAGES L
1706 WHERE M.MESSAGE_NAME = 'LAY_LB_HEADER'
1707 AND M.LANGUAGE_CODE = L.LANGUAGE_CODE
1708 AND L.INSTALLED_FLAG IN ('I', 'B');
1709
1710 END IF;
1711
1712
1713 -----------------------------------------------------------------------------
1714 -- Creaate line block within the layout
1715 -----------------------------------------------------------------------------
1716 BEGIN
1717 SELECT B.BLOCK_ID
1718 INTO l_line_block_id
1719 FROM BNE_LAYOUT_BLOCKS_B B
1720 WHERE B.APPLICATION_ID = 274
1721 AND B.LAYOUT_CODE = l_layout_code
1722 AND B.LAYOUT_ELEMENT = 'LINE'
1723 AND B.PARENT_ID =
1724 (
1725 SELECT BLOCK_ID
1726 FROM BNE_LAYOUT_BLOCKS_B
1727 WHERE APPLICATION_ID = B.APPLICATION_ID
1728 AND LAYOUT_CODE = B.LAYOUT_CODE
1729 AND LAYOUT_ELEMENT = 'HEADER'
1730 );
1731 EXCEPTION
1732 WHEN NO_DATA_FOUND THEN NULL;
1733 END;
1734
1735 IF (l_line_block_id IS NULL) THEN
1736 ---------------------------------------------------------------------------
1737 -- Insert Line block into the layout
1738 ---------------------------------------------------------------------------
1739 l_line_block_id := 2;
1740 INSERT INTO BNE_LAYOUT_BLOCKS_B
1741 ( APPLICATION_ID
1742 , LAYOUT_CODE
1743 , BLOCK_ID
1744 , OBJECT_VERSION_NUMBER
1745 , PARENT_ID
1746 , LAYOUT_ELEMENT
1747 , STYLE_CLASS
1748 , STYLE
1749 , ROW_STYLE_CLASS
1750 , ROW_STYLE
1751 , COL_STYLE_CLASS
1752 , COL_STYLE
1753 , PROMPT_DISPLAYED_FLAG
1754 , PROMPT_STYLE_CLASS
1755 , PROMPT_STYLE
1756 , HINT_DISPLAYED_FLAG
1757 , HINT_STYLE_CLASS
1758 , HINT_STYLE
1759 , ORIENTATION
1760 , LAYOUT_CONTROL
1761 , DISPLAY_FLAG
1762 , BLOCKSIZE
1763 , MINSIZE
1764 , MAXSIZE
1765 , SEQUENCE_NUM
1766 , PROMPT_COLSPAN
1767 , HINT_COLSPAN
1768 , ROW_COLSPAN
1769 , SUMMARY_STYLE_CLASS
1770 , SUMMARY_STYLE
1771 , CREATION_DATE
1772 , CREATED_BY
1773 , LAST_UPDATE_DATE
1774 , LAST_UPDATED_BY
1775 , LAST_UPDATE_LOGIN
1776 ) VALUES
1777 ( 274
1778 , l_layout_code
1779 , l_line_block_id
1780 , 1
1781 , l_header_block_id
1782 , 'LINE'
1783 , 'BNE_LINES'
1784 , NULL
1785 , 'BNE_LINES_ROW'
1786 , NULL
1787 , NULL
1788 , NULL
1789 , 'Y'
1790 , 'BNE_LINES_HEADER'
1791 , NULL
1792 , 'Y'
1793 , 'BNE_LINES_HINT'
1794 , NULL
1795 , 'VERTICAL'
1796 , 'TABLE_FLOW'
1797 , 'Y'
1798 , 10
1799 , 1
1800 , 1
1801 , 20
1802 , NULL
1803 , NULL
1804 , NULL
1805 , 'BNE_LINES_TOTAL'
1806 , NULL
1807 , SYSDATE
1808 , l_user_id
1809 , SYSDATE
1810 , l_user_id
1811 , l_login_id
1812 );
1813
1814 INSERT INTO BNE_LAYOUT_BLOCKS_TL
1815 ( APPLICATION_ID
1816 , LAYOUT_CODE
1817 , BLOCK_ID
1818 , USER_NAME
1819 , CREATED_BY
1820 , CREATION_DATE
1821 , LAST_UPDATED_BY
1822 , LAST_UPDATE_LOGIN
1823 , LAST_UPDATE_DATE
1824 , LANGUAGE
1825 , SOURCE_LANG
1826 )
1827 SELECT 274
1828 , l_layout_code
1829 , l_line_block_id
1830 , M.MESSAGE_TEXT
1831 , l_user_id
1832 , SYSDATE
1833 , l_user_id
1834 , l_login_id
1835 , SYSDATE
1836 , L.LANGUAGE_CODE
1837 , USERENV('LANG')
1838 FROM FND_NEW_MESSAGES M,
1839 FND_LANGUAGES L
1840 WHERE M.MESSAGE_NAME = 'LAY_LB_LINE'
1841 AND M.LANGUAGE_CODE = L.LANGUAGE_CODE
1842 AND L.INSTALLED_FLAG IN ('I', 'B');
1843
1844 END IF;
1845
1846 -----------------------------------------------------------------------------
1847 -- Insert into BNE_LAYOUT_COLS
1848 -----------------------------------------------------------------------------
1849 BEGIN
1850 SELECT NVL(MAX(A.INTERFACE_SEQ_NUM), 0)
1851 INTO l_interface_seq_num
1852 FROM BNE_LAYOUT_COLS A
1853 WHERE A.APPLICATION_ID = 274
1854 AND A.BLOCK_ID = l_header_block_id
1855 AND A.LAYOUT_CODE = l_layout_code;
1856 EXCEPTION
1857 WHEN NO_DATA_FOUND THEN NULL;
1858 END;
1859
1860 IF(l_interface_seq_num = 0) THEN
1861 ---------------------------------------------------------------------------
1862 -- Insert into BNE_LAYOUT_COLS for the header block
1863 ---------------------------------------------------------------------------
1864 INSERT INTO BNE_LAYOUT_COLS
1865 ( APPLICATION_ID
1866 , LAYOUT_CODE
1867 , BLOCK_ID
1868 , OBJECT_VERSION_NUMBER
1869 , INTERFACE_APP_ID
1870 , INTERFACE_CODE
1871 , INTERFACE_SEQ_NUM
1872 , SEQUENCE_NUM
1873 , STYLE
1874 , STYLE_CLASS
1875 , HINT_STYLE
1876 , HINT_STYLE_CLASS
1877 , PROMPT_STYLE
1878 , PROMPT_STYLE_CLASS
1879 , DEFAULT_TYPE
1880 , DEFAULT_VALUE
1881 , CREATED_BY
1882 , CREATION_DATE
1883 , LAST_UPDATED_BY
1884 , LAST_UPDATE_LOGIN
1885 , LAST_UPDATE_DATE
1886 )
1887 VALUES
1888 ( 274
1889 , l_layout_code
1890 , l_header_block_id
1891 , 1
1892 , 274
1893 , 'FEM_DIM_MEMBER_HEADER_INTF'
1894 , 1
1895 , 1
1896 , NULL
1897 , NULL
1898 , NULL
1899 , NULL
1900 , NULL
1901 , NULL
1902 , NULL
1903 , NULL
1904 , l_user_id
1905 , SYSDATE
1906 , l_user_id
1907 , l_login_id
1908 , SYSDATE
1909 );
1910
1911 END IF;
1912
1913 ------------------------------------------------------------------------------
1914 -- Update the block id for header block
1915 ------------------------------------------------------------------------------
1916 UPDATE BNE_LAYOUT_COLS
1917 SET BLOCK_ID = l_header_block_id
1918 WHERE APPLICATION_ID = 274
1919 AND LAYOUT_CODE = l_layout_code
1920 AND INTERFACE_APP_ID = 274
1921 AND INTERFACE_CODE = p_interface_code
1922 AND INTERFACE_SEQ_NUM IN
1923 (
1924 SELECT SEQUENCE_NUM
1925 FROM BNE_INTERFACE_COLS_B I
1926 WHERE I.APPLICATION_ID = 274
1927 AND I.INTERFACE_CODE = p_interface_code
1928 AND I.INTERFACE_COL_NAME IN (
1929 SELECT column_value
1930 FROM TABLE(CAST(l_header_block_cols AS FND_TABLE_OF_VARCHAR2_30))
1931 )
1932 )
1933 AND BLOCK_ID <> l_header_block_id;
1934
1935 ------------------------------------------------------------------------------
1936 -- Insert into the header block if the columns are not existed
1937 ------------------------------------------------------------------------------
1938
1939 FOR intf_rec IN
1940 (
1941 SELECT INTERFACE_COL_NAME
1942 , SEQUENCE_NUM
1943 , DECODE (INTERFACE_COL_NAME, 'P_DIMENSION_VARCHAR_LABEL', 'CONSTANT','P_LEDGER_ID','SQL',
1944 NULL) AS DEFAULT_TYPE
1945 , DECODE (INTERFACE_COL_NAME, 'P_DIMENSION_VARCHAR_LABEL', p_dimension_varchar_label,'P_LEDGER_ID',l_default_ledger_query,
1946 NULL) AS DEFAULT_VALUE /* Bug#5533480 */
1947 FROM BNE_INTERFACE_COLS_B IC
1948 WHERE APPLICATION_ID = 274
1949 AND INTERFACE_CODE = p_interface_code
1950 AND INTERFACE_COL_NAME IN (
1951 SELECT column_value
1952 FROM TABLE(CAST(l_header_block_cols AS FND_TABLE_OF_VARCHAR2_30))
1953 )
1954 AND NOT EXISTS
1955 (
1956 SELECT 1
1957 FROM BNE_LAYOUT_COLS
1958 WHERE APPLICATION_ID = 274
1959 AND LAYOUT_CODE = l_layout_code
1960 AND INTERFACE_APP_ID = 274
1961 AND INTERFACE_CODE = p_interface_code
1962 AND INTERFACE_SEQ_NUM = IC.SEQUENCE_NUM
1963 AND BLOCK_ID = l_header_block_id
1964 )
1965 )
1966 LOOP
1967 IF NOT((p_dimension_type_code <> 'TIME' AND intf_rec.INTERFACE_COL_NAME = 'P_CALENDAR_DISPLAY_CODE') OR
1968 (p_value_set_required_flag = 'N' AND intf_rec.INTERFACE_COL_NAME = 'P_LEDGER_ID')) THEN
1969 INSERT INTO BNE_LAYOUT_COLS
1970 ( APPLICATION_ID
1971 , LAYOUT_CODE
1972 , BLOCK_ID
1973 , OBJECT_VERSION_NUMBER
1974 , INTERFACE_APP_ID
1975 , INTERFACE_CODE
1976 , INTERFACE_SEQ_NUM
1977 , SEQUENCE_NUM
1978 , STYLE
1979 , STYLE_CLASS
1980 , HINT_STYLE
1981 , HINT_STYLE_CLASS
1982 , PROMPT_STYLE
1983 , PROMPT_STYLE_CLASS
1984 , DEFAULT_TYPE
1985 , DEFAULT_VALUE
1986 , CREATED_BY
1987 , CREATION_DATE
1988 , LAST_UPDATED_BY
1989 , LAST_UPDATE_LOGIN
1990 , LAST_UPDATE_DATE
1991 )
1992 VALUES
1993 ( 274
1994 , l_layout_code
1995 , l_header_block_id
1996 , 1
1997 , 274
1998 , p_interface_code
1999 , intf_rec.SEQUENCE_NUM
2000 , intf_rec.SEQUENCE_NUM * 10
2001 , NULL
2002 , NULL
2003 , NULL
2004 , NULL
2005 , NULL
2006 , NULL
2007 , intf_rec.DEFAULT_TYPE
2008 , intf_rec.DEFAULT_VALUE
2009 , l_user_id
2010 , SYSDATE
2011 , l_user_id
2012 , l_login_id
2013 , SYSDATE
2014 );
2015 END IF;
2016
2017 END LOOP;
2018
2019 ------------------------------------------------------------------------------
2020 -- Update the block id for line block
2021 ------------------------------------------------------------------------------
2022 UPDATE BNE_LAYOUT_COLS
2023 SET BLOCK_ID = l_line_block_id
2024 WHERE APPLICATION_ID = 274
2025 AND LAYOUT_CODE = l_layout_code
2026 AND INTERFACE_APP_ID = 274
2027 AND INTERFACE_CODE = p_interface_code
2028 AND INTERFACE_SEQ_NUM IN
2029 (
2030 SELECT SEQUENCE_NUM
2031 FROM BNE_INTERFACE_COLS_B I
2032 WHERE I.APPLICATION_ID = 274
2033 AND I.INTERFACE_CODE = p_interface_code
2034 AND I.INTERFACE_COL_NAME IN (
2035 SELECT column_value
2036 FROM TABLE(CAST(l_line_block_cols AS FND_TABLE_OF_VARCHAR2_30))
2037 )
2038 )
2039 AND BLOCK_ID <> l_line_block_id;
2040
2041 -----------------------------------------------------------------------------
2042 -- Assign value to the BNE_LAYOUT_COLS for the line block
2043 -----------------------------------------------------------------------------
2044 -- Delete those columns that are not stored in the FEM_WEBADI_DIM_ATTR_MAPS
2045 DELETE BNE_LAYOUT_COLS
2046 WHERE APPLICATION_ID = 274
2047 AND INTERFACE_CODE = p_interface_code
2048 AND INTERFACE_SEQ_NUM IN
2049 ( SELECT I.SEQUENCE_NUM
2050 FROM BNE_INTERFACE_COLS_B I
2051 WHERE I.APPLICATION_ID = INTERFACE_APP_ID
2052 AND I.INTERFACE_CODE = INTERFACE_CODE
2053 AND I.INTERFACE_COL_NAME LIKE 'P_ATTRIBUTE%'
2054 AND NOT EXISTS
2055 (
2056 SELECT 1
2057 FROM FEM_WEBADI_DIM_ATTR_MAPS M
2058 WHERE M.DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label
2059 AND M.INTERFACE_COL = I.INTERFACE_COL_NAME
2060 )
2061 );
2062
2063
2064 ------------------------------------------------------------------------------
2065 -- Delete from layout_cols for both blocks
2066 ------------------------------------------------------------------------------
2067 DELETE BNE_LAYOUT_COLS
2068 WHERE APPLICATION_ID = 274
2069 AND LAYOUT_CODE = l_layout_code
2070 AND INTERFACE_APP_ID = 274
2071 AND INTERFACE_CODE = p_interface_code
2072 AND INTERFACE_SEQ_NUM NOT IN
2073 (
2074 SELECT SEQUENCE_NUM
2075 FROM BNE_INTERFACE_COLS_B I
2076 WHERE I.APPLICATION_ID = 274
2077 AND I.INTERFACE_CODE = p_interface_code
2078 AND (I.INTERFACE_COL_NAME IN (
2079 SELECT column_value
2080 FROM TABLE(CAST(l_header_block_cols AS FND_TABLE_OF_VARCHAR2_30))
2081 )
2082 OR
2083 I.INTERFACE_COL_NAME IN (
2084 SELECT column_value
2085 FROM TABLE(CAST(l_line_block_cols AS FND_TABLE_OF_VARCHAR2_30))
2086 )
2087 OR I.INTERFACE_COL_NAME LIKE 'P_ATTRIBUTE%'
2088 )
2089 );
2090
2091 -----------------------------------------------------------------------------
2092 -- Insert into BNE_LAYOUT_COLS for the line block
2093 -----------------------------------------------------------------------------
2094 -- Insert the common interface column first
2095 INSERT INTO BNE_LAYOUT_COLS
2096 ( APPLICATION_ID
2097 , LAYOUT_CODE
2098 , BLOCK_ID
2099 , OBJECT_VERSION_NUMBER
2100 , INTERFACE_APP_ID
2101 , INTERFACE_CODE
2102 , INTERFACE_SEQ_NUM
2103 , SEQUENCE_NUM
2104 , STYLE
2105 , STYLE_CLASS
2106 , HINT_STYLE
2107 , HINT_STYLE_CLASS
2108 , PROMPT_STYLE
2109 , PROMPT_STYLE_CLASS
2110 , DEFAULT_TYPE
2111 , DEFAULT_VALUE
2112 , CREATED_BY
2113 , CREATION_DATE
2114 , LAST_UPDATED_BY
2115 , LAST_UPDATE_LOGIN
2116 , LAST_UPDATE_DATE
2117 )
2118 SELECT 274
2119 , l_layout_code
2120 , l_line_block_id
2121 , 1
2122 , 274
2123 , p_interface_code
2124 , IC.SEQUENCE_NUM
2125 , IC.SEQUENCE_NUM * 10
2126 , NULL
2127 , NULL
2128 , NULL
2129 , NULL
2130 , NULL
2131 , NULL
2132 , NULL
2133 , NULL
2134 , l_user_id
2135 , SYSDATE
2136 , l_user_id
2137 , l_login_id
2138 , SYSDATE
2139 FROM BNE_INTERFACE_COLS_B IC
2140 WHERE APPLICATION_ID = 274
2141 AND IC.INTERFACE_CODE = p_interface_code
2142 AND ( ( p_group_use_code = 'NOT_SUPPORTED'
2143 AND NOT IC.INTERFACE_COL_NAME =
2144 DECODE(p_group_use_code, 'NOT_SUPPORTED', 'P_DIMENSION_GROUP_DISPLAY_CODE', IC.INTERFACE_COL_NAME)
2145 )
2146 OR
2147 ( p_group_use_code <> 'NOT_SUPPORTED'
2148 AND IC.INTERFACE_COL_NAME =
2149 DECODE(p_group_use_code, 'NOT_SUPPORTED', 'P_DIMENSION_GROUP_DISPLAY_CODE', IC.INTERFACE_COL_NAME)
2150 )
2151 )
2152 AND INTERFACE_COL_NAME IN (
2153 SELECT column_value
2154 FROM TABLE(CAST(l_line_block_cols AS FND_TABLE_OF_VARCHAR2_30))
2155 )
2156 AND NOT EXISTS
2157 ( SELECT 1
2158 FROM BNE_LAYOUT_COLS LC
2159 WHERE LC.APPLICATION_ID = IC.APPLICATION_ID
2160 AND LC.LAYOUT_CODE = l_layout_code
2161 AND LC.BLOCK_ID = l_line_block_id
2162 AND LC.INTERFACE_APP_ID = IC.APPLICATION_ID
2163 AND LC.INTERFACE_CODE = IC.INTERFACE_CODE
2164 AND LC.INTERFACE_SEQ_NUM = IC. SEQUENCE_NUM
2165 );
2166
2167 --Bug#6474936:Delete Member Display Code layout col for Calendar period
2168 IF(p_dimension_varchar_label = 'CAL_PERIOD') THEN
2169 DELETE FROM BNE_LAYOUT_COLS WHERE LAYOUT_CODE = l_layout_code AND INTERFACE_CODE = p_interface_code
2170 AND INTERFACE_SEQ_NUM = (SELECT SEQUENCE_NUM FROM BNE_INTERFACE_COLS_B WHERE INTERFACE_CODE = p_interface_code
2171 AND INTERFACE_COL_NAME = 'P_MEMBER_DISPLAY_CODE');
2172 END IF;
2173
2174 -- Inser the attribute interface columns
2175 INSERT INTO BNE_LAYOUT_COLS
2176 ( APPLICATION_ID
2177 , LAYOUT_CODE
2178 , BLOCK_ID
2179 , OBJECT_VERSION_NUMBER
2180 , INTERFACE_APP_ID
2181 , INTERFACE_CODE
2182 , INTERFACE_SEQ_NUM
2183 , SEQUENCE_NUM
2184 , STYLE
2185 , STYLE_CLASS
2186 , HINT_STYLE
2187 , HINT_STYLE_CLASS
2188 , PROMPT_STYLE
2189 , PROMPT_STYLE_CLASS
2190 , DEFAULT_TYPE
2191 , DEFAULT_VALUE
2192 , CREATED_BY
2193 , CREATION_DATE
2194 , LAST_UPDATED_BY
2195 , LAST_UPDATE_LOGIN
2196 , LAST_UPDATE_DATE
2197 )
2198 SELECT 274
2199 , l_layout_code
2200 , l_line_block_id
2201 , 1
2202 , 274
2203 , p_interface_code
2204 , IC.SEQUENCE_NUM
2205 , IC.SEQUENCE_NUM * 10
2206 , NULL
2207 , NULL
2208 , NULL
2209 , NULL
2210 , NULL
2211 , NULL
2212 , NULL
2213 , NULL
2214 , l_user_id
2215 , SYSDATE
2216 , l_user_id
2217 , l_login_id
2218 , SYSDATE
2219 FROM BNE_INTERFACE_COLS_B IC
2220 WHERE APPLICATION_ID = 274
2221 AND IC.INTERFACE_CODE = p_interface_code
2222 AND EXISTS
2223 ( SELECT 1
2224 FROM FEM_WEBADI_DIM_ATTR_MAPS M
2225 WHERE M.DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label
2226 AND M.INTERFACE_COL = INTERFACE_COL_NAME
2227 )
2228 AND NOT EXISTS
2229 ( SELECT 1
2230 FROM BNE_LAYOUT_COLS LC
2231 WHERE LC.APPLICATION_ID = IC.APPLICATION_ID
2232 AND LC.LAYOUT_CODE = l_layout_code
2233 AND LC.BLOCK_ID = l_line_block_id
2234 AND LC.INTERFACE_APP_ID = IC.APPLICATION_ID
2235 AND LC.INTERFACE_CODE = IC.INTERFACE_CODE
2236 AND LC.INTERFACE_SEQ_NUM = IC. SEQUENCE_NUM
2237 );
2238
2239 IF ( FND_API.To_Boolean( p_char => p_commit) ) THEN
2240 COMMIT;
2241 END IF;
2242
2243 EXCEPTION
2244 --
2245 WHEN FND_API.G_EXC_ERROR THEN
2246 --
2247 ROLLBACK TO Dim_Layout_Pvt ;
2248 x_return_status := FND_API.G_RET_STS_ERROR;
2249 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2250 p_data => x_msg_data );
2251 --
2252 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2253 --
2254 ROLLBACK TO Dim_Layout_Pvt ;
2255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2256 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2257 p_data => x_msg_data );
2258 --
2259 WHEN OTHERS THEN
2260 --
2261 ROLLBACK TO Dim_Layout_Pvt ;
2262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2263 --
2264 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2265 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
2266 l_api_name);
2267 END if;
2268 --
2269 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2270 p_data => x_msg_data );
2271 --
2272 END Populate_Dim_Layout;
2273
2274
2275 PROCEDURE Populate_Mem_WebADI_Metadata (
2276 x_return_status OUT NOCOPY VARCHAR2,
2277 x_msg_count OUT NOCOPY NUMBER ,
2278 x_msg_data OUT NOCOPY VARCHAR2,
2279 p_api_version IN NUMBER ,
2280 p_init_msg_list IN VARCHAR2,
2281 p_commit IN VARCHAR2,
2282 p_dimension_varchar_label IN VARCHAR2
2283 )
2284 IS
2285 --
2286 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Mem_WebADI_Metadata';
2287 l_api_version CONSTANT NUMBER := 1.0;
2288 --
2289 l_dimension_id NUMBER(9) ;
2290 l_user_id NUMBER(15) := 2; -- (user name : initial setup)
2291
2292 l_object_code VARCHAR2(30) ;
2293 l_dimension_type_code VARCHAR2(30) ;
2294 l_dimension_name VARCHAR2(80) ;
2295 l_group_use_code VARCHAR2(30) ;
2296 l_value_set_required_flag VARCHAR2(1) ;
2297 l_integrator_code VARCHAR2(30) := 'FEM_DIM_MEMBER_INTG';
2298 l_interface_code VARCHAR2(30) ;
2299 l_interface_name VARCHAR2(50) ;
2300 l_intg_upl_param_list_code VARCHAR2(30) := 'FEM_DIM_MEMBER_UPL_LIST';
2301 l_intg_imp_param_list_code VARCHAR2(30) := 'FEM_DIM_MEMBER_IMP_LIST';
2302 l_intf_upl_param_list_code VARCHAR2(30) := 'FEM_DIM_MEMBER';
2303 l_row_id VARCHAR2(30) ;
2304 l_existed_flag VARCHAR2(1) ;
2305 l_updated_flag VARCHAR2(10) ;
2306 l_order_seq NUMBER(9) ;
2307
2308 --
2309 l_return_status VARCHAR2(1) ;
2310 l_msg_count NUMBER ;
2311 l_msg_data VARCHAR2(2000) ;
2312
2313 TYPE l_dim_name_tbl_type is TABLE of
2314 FEM_DIMENSIONS_TL.DIMENSION_NAME%TYPE index by BINARY_INTEGER;
2315
2316 TYPE l_dim_lang_tbl_type is TABLE of
2317 FEM_DIMENSIONS_TL.LANGUAGE%TYPE index by BINARY_INTEGER;
2318
2319 TYPE l_dim_src_lang_tbl_type is TABLE of
2320 FEM_DIMENSIONS_TL.SOURCE_LANG%TYPE index by BINARY_INTEGER;
2321
2322 BEGIN
2323 --
2324 SAVEPOINT Dim_WebADI_Metadata_Pvt ;
2325 --
2326 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2327 p_api_version,
2328 l_api_name,
2329 G_PKG_NAME )
2330 THEN
2331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2332 END IF;
2333 --
2334
2335 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
2336 FND_MSG_PUB.initialize ;
2337 END IF;
2338 --
2339 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2340 --
2341
2342 SELECT DIMENSION_ID
2343 , DIMENSION_NAME
2344 , 'FEM_DIM_' || DIMENSION_ID
2345 , 'UPLOAD_MEMBER_INTERFACE' || DIMENSION_ID
2346 , DIMENSION_TYPE_CODE
2347 , VALUE_SET_REQUIRED_FLAG
2348 , GROUP_USE_CODE
2349 INTO l_dimension_id
2350 , l_dimension_name
2351 , l_object_code
2352 , l_interface_name
2353 , l_dimension_type_code
2354 , l_value_set_required_flag
2355 , l_group_use_code
2356 FROM FEM_XDIM_DIMENSIONS_VL
2357 WHERE DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label;
2358
2359
2360 IF SQL%NOTFOUND THEN
2361 RAISE NO_DATA_FOUND;
2362 END IF;
2363
2364 /*
2365 -- print out the fetched metadata
2366 IF SQL%FOUND THEN
2367 pd('l_dimension_id = ' || TO_CHAR(l_dimension_id));
2368 pd('l_object_code = ' || l_object_code);
2369 pd('l_dimension_type_code = ' || l_dimension_type_code);
2370 pd('l_value_set_required_flag = ' || l_value_set_required_flag);
2371 END IF;
2372 */
2373
2374 -----------------------------------------------------------------------------
2375 -- Handling Integrator
2376 -----------------------------------------------------------------------------
2377 BEGIN
2378 SELECT 'Y'
2379 INTO l_existed_flag
2380 FROM BNE_INTEGRATORS_B
2381 WHERE APPLICATION_ID = 274
2382 AND INTEGRATOR_CODE = l_integrator_code;
2383 EXCEPTION
2384 WHEN NO_DATA_FOUND THEN NULL;
2385 END;
2386
2387 -- If the Integrator does not exist then insert the integrator
2388
2389 IF (l_existed_flag IS NULL) THEN
2390
2391 INSERT INTO BNE_INTEGRATORS_B
2392 ( APPLICATION_ID
2393 , INTEGRATOR_CODE
2394 , OBJECT_VERSION_NUMBER
2395 , UPLOAD_PARAM_LIST_APP_ID
2396 , UPLOAD_PARAM_LIST_CODE
2397 , UPLOAD_SERV_PARAM_LIST_APP_ID
2398 , UPLOAD_SERV_PARAM_LIST_CODE
2399 , IMPORT_PARAM_LIST_APP_ID
2400 , IMPORT_PARAM_LIST_CODE
2401 , IMPORT_TYPE
2402 , UPLOADER_CLASS
2403 , DATE_FORMAT
2404 , CREATED_BY
2405 , CREATION_DATE
2406 , LAST_UPDATED_BY
2407 , LAST_UPDATE_DATE
2408 , ENABLED_FLAG)
2409 VALUES
2410 ( 274
2411 , l_integrator_code
2412 , 1
2413 , 274
2414 , l_intg_upl_param_list_code
2415 , 231
2416 , 'UPL_SERV_INTERF_COLS'
2417 , 274
2418 , l_intg_imp_param_list_code
2419 , 1
2420 , 'oracle.apps.bne.integrator.upload.BneUploader'
2421 , 'yyyy-MM-dd'
2422 , l_user_id
2423 , SYSDATE
2424 , l_user_id
2425 , SYSDATE
2426 , 'Y');
2427
2428 INSERT INTO BNE_INTEGRATORS_TL
2429 ( APPLICATION_ID
2430 , INTEGRATOR_CODE
2431 , LANGUAGE
2432 , SOURCE_LANG
2433 , USER_NAME
2434 , UPLOAD_HEADER
2435 , UPLOAD_TITLE_BAR
2436 , CREATED_BY
2437 , CREATION_DATE
2438 , LAST_UPDATED_BY
2439 , LAST_UPDATE_DATE)
2440 SELECT 274
2441 , l_integrator_code
2442 , M.LANGUAGE_CODE
2443 , userenv('LANG')
2444 , M.MESSAGE_TEXT
2445 , 'Upload Parameters'
2446 , 'Upload Parameters'
2447 , l_user_id
2448 , SYSDATE
2449 , l_user_id
2450 , SYSDATE
2451 FROM FND_NEW_MESSAGES M,
2452 FND_LANGUAGES L
2453 WHERE M.MESSAGE_NAME = 'FEM_ADI_MEMBER_INTEGRATOR'
2454 AND M.LANGUAGE_CODE = L.LANGUAGE_CODE
2455 AND L.INSTALLED_FLAG IN ('I', 'B');
2456 END IF;
2457
2458 -----------------------------------------------------------------------------
2459 -- Handling Interface
2460 -----------------------------------------------------------------------------
2461
2462 -- populate attribute maps
2463 Populate_Dim_Attribute_Maps
2464 ( x_return_status => l_return_status,
2465 x_msg_count => l_msg_count,
2466 x_msg_data => l_msg_data,
2467 p_api_version => 1.0,
2468 p_init_msg_list => FND_API.G_TRUE,
2469 p_commit => FND_API.G_FALSE,
2470 p_dimension_varchar_label => p_dimension_varchar_label
2471 );
2472
2473 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2474 RAISE FND_API.G_EXC_ERROR ;
2475 END IF;
2476
2477 l_interface_code := l_object_code || '_INTF';
2478 l_existed_flag := NULL;
2479
2480 BEGIN
2481 SELECT 'Y'
2482 INTO l_existed_flag
2483 FROM BNE_INTERFACES_B
2484 WHERE APPLICATION_ID = 274
2485 AND INTERFACE_CODE = l_interface_code;
2486 EXCEPTION
2487 WHEN NO_DATA_FOUND THEN NULL;
2488 END;
2489
2490 -- If the Interface does not exist then insert the interface
2491
2492 IF (l_existed_flag IS NULL) THEN
2493
2494 SELECT NVL(MAX(UPLOAD_ORDER), 0) + 1
2495 INTO l_order_seq
2496 FROM BNE_INTERFACES_B
2497 WHERE APPLICATION_ID = 274
2498 AND INTEGRATOR_APP_ID = 274
2499 AND INTEGRATOR_CODE = l_integrator_code;
2500
2501 IF SQL%NOTFOUND THEN
2502 RAISE NO_DATA_FOUND;
2503 END IF;
2504
2505 INSERT INTO BNE_INTERFACES_B
2506 (APPLICATION_ID,
2507 INTERFACE_CODE,
2508 OBJECT_VERSION_NUMBER,
2509 INTEGRATOR_APP_ID,
2510 INTEGRATOR_CODE,
2511 INTERFACE_NAME,
2512 UPLOAD_TYPE,
2513 UPLOAD_PARAM_LIST_APP_ID,
2514 UPLOAD_PARAM_LIST_CODE,
2515 UPLOAD_ORDER,
2516 CREATED_BY,
2517 CREATION_DATE,
2518 LAST_UPDATED_BY,
2519 LAST_UPDATE_DATE)
2520 VALUES
2521 (274,
2522 l_interface_code,
2523 1,
2524 274,
2525 l_integrator_code,
2526 l_interface_name,
2527 2,
2528 274,
2529 l_intf_upl_param_list_code,
2530 l_order_seq,
2531 l_user_id,
2532 SYSDATE,
2533 l_user_id,
2534 SYSDATE);
2535
2536 -- Create the interface in the BNE_INTERFACES_TL table
2537
2538 INSERT INTO BNE_INTERFACES_TL
2539 ( APPLICATION_ID,
2540 INTERFACE_CODE,
2541 LANGUAGE,
2542 SOURCE_LANG,
2543 USER_NAME,
2544 CREATED_BY,
2545 CREATION_DATE,
2546 LAST_UPDATED_BY,
2547 LAST_UPDATE_DATE)
2548 SELECT 274
2549 , l_interface_code
2550 , D.LANGUAGE
2551 , D.SOURCE_LANG
2552 , SUBSTR(M.MESSAGE_TEXT,0, INSTR(M.MESSAGE_TEXT, 'DIM_NAME')-2) ||
2553 D.DIMENSION_NAME ||
2554 SUBSTR(M.MESSAGE_TEXT,INSTR(M.MESSAGE_TEXT, 'DIM_NAME')+8)
2555 , l_user_id
2556 , SYSDATE
2557 , l_user_id
2558 , SYSDATE
2559 FROM FEM_DIMENSIONS_TL D, FND_NEW_MESSAGES M, FND_LANGUAGES L
2560 WHERE D.DIMENSION_ID = l_dimension_id
2561 AND M.APPLICATION_ID= 274
2562 AND M.MESSAGE_NAME = 'FEM_ADI_MEMBER_INTERFACE'
2563 AND M.LANGUAGE_CODE = D.LANGUAGE
2564 AND M.LANGUAGE_CODE = L.LANGUAGE_CODE
2565 AND L.INSTALLED_FLAG IN ('I', 'B');
2566
2567
2568 -- Update common interface columns
2569
2570 ---------------------------------------------------------------------------
2571 -- Populate the common interface columns
2572 ---------------------------------------------------------------------------
2573
2574 Populate_Dim_Intf_Common_Cols (
2575 p_api_version => 1.0,
2576 p_init_msg_list => FND_API.G_FALSE,
2577 p_commit => FND_API.G_FALSE,
2578 x_return_status => l_return_status,
2579 x_msg_count => l_msg_count,
2580 x_msg_data => l_msg_data,
2581 p_interface_code => l_interface_code,
2582 p_dimension_varchar_label => p_dimension_varchar_label,
2583 p_group_use_code => l_group_use_code
2584 );
2585
2586 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2587 RAISE FND_API.G_EXC_ERROR ;
2588 END IF;
2589
2590
2591 ---------------------------------------------------------------------------
2592 -- Populate the attribute related interface columns
2593 ---------------------------------------------------------------------------
2594
2595 Populate_Dim_Intf_Attr_Cols (
2596 p_api_version => 1.0,
2597 p_init_msg_list => FND_API.G_FALSE,
2598 p_commit => FND_API.G_FALSE,
2599 x_return_status => l_return_status,
2600 x_msg_count => l_msg_count,
2601 x_msg_data => l_msg_data,
2602 p_interface_code => l_interface_code,
2603 p_dimension_varchar_label => p_dimension_varchar_label,
2604 p_dimension_id => l_dimension_id,
2605 x_updated_flag => l_updated_flag
2606 );
2607
2608 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2609 RAISE FND_API.G_EXC_ERROR ;
2610 END IF;
2611
2612 l_updated_flag := 'Y';
2613
2614 ELSE
2615 -- Update the attribute interface columns
2616
2617 ---------------------------------------------------------------------------
2618 -- Populate the attribute related interface columns
2619 ---------------------------------------------------------------------------
2620 Populate_Dim_Intf_Attr_Cols (
2621 p_api_version => 1.0,
2622 p_init_msg_list => FND_API.G_FALSE,
2623 p_commit => FND_API.G_FALSE,
2624 x_return_status => l_return_status,
2625 x_msg_count => l_msg_count,
2626 x_msg_data => l_msg_data,
2627 p_interface_code => l_interface_code,
2628 p_dimension_varchar_label => p_dimension_varchar_label,
2629 p_dimension_id => l_dimension_id,
2630 x_updated_flag => l_updated_flag
2631 );
2632
2633 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2634 RAISE FND_API.G_EXC_ERROR ;
2635 END IF;
2636
2637 END IF;
2638
2639 -----------------------------------------------------------------------------
2640 -- Create Layout
2641 -----------------------------------------------------------------------------
2642 IF (l_updated_flag = 'Y') THEN
2643
2644 Populate_Dim_Layout (
2645 p_api_version => 1.0,
2646 p_init_msg_list => FND_API.G_FALSE,
2647 p_commit => FND_API.G_FALSE,
2648 x_return_status => l_return_status,
2649 x_msg_count => l_msg_count,
2650 x_msg_data => l_msg_data,
2651 p_integrator_code => l_integrator_code,
2652 p_interface_code => l_interface_code,
2653 p_dimension_varchar_label => p_dimension_varchar_label,
2654 p_dimension_name => l_dimension_name,
2655 p_object_code => l_object_code,
2656 p_dimension_type_code => l_dimension_type_code,
2657 p_value_set_required_flag => l_value_set_required_flag,
2658 p_group_use_code => l_group_use_code
2659 );
2660
2661
2662 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2663 RAISE FND_API.G_EXC_ERROR ;
2664 END IF;
2665
2666 -- update the object version number in the bne_integrators_b table.
2667 -- This will fix the new interface caching issue.
2668
2669 UPDATE BNE_INTEGRATORS_B
2670 SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
2671 --, LAST_UPDATE_DATE = last_update_date + 1
2672 WHERE APPLICATION_ID = 274
2673 AND INTEGRATOR_CODE = l_integrator_code;
2674
2675 END IF;
2676
2677 IF ( FND_API.To_Boolean( p_char => p_commit) ) THEN
2678 COMMIT;
2679 END IF;
2680
2681
2682 EXCEPTION
2683 --
2684 WHEN FND_API.G_EXC_ERROR THEN
2685 --
2686 ROLLBACK TO Dim_WebADI_Metadata_Pvt ;
2687 x_return_status := FND_API.G_RET_STS_ERROR;
2688 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2689 p_data => x_msg_data );
2690 --
2691 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2692 --
2693 ROLLBACK TO Dim_WebADI_Metadata_Pvt ;
2694 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2695 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2696 p_data => x_msg_data );
2697 --
2698 WHEN OTHERS THEN
2699 --
2700 ROLLBACK TO Dim_WebADI_Metadata_Pvt ;
2701 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2702 --
2703 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2704 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
2705 l_api_name);
2706 END if;
2707 --
2708 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2709 p_data => x_msg_data );
2710 --
2711 END Populate_Mem_WebADI_Metadata;
2712
2713 /*===========================================================================+
2714 Procedure Name : Populate_Dim_Metadata_Info
2715 Parameters :
2716 IN : p_dimension_varchar_label VARCHAR2
2717 OUT : x_return_status VARCHAR2
2718
2719 Description : Populates global variables with metadata information
2720 of the supplied p_dimension_varchar_label.
2721
2722 Modification History :
2723 Date Name Desc
2724 ---------- --------- -------------------------------------------------------
2725 09/23/2005 SHTRIPAT Created.
2726 ---------- --------- -------------------------------------------------------
2727 +===========================================================================*/
2728 PROCEDURE Populate_Dim_Metadata_Info
2729 ( x_return_status OUT NOCOPY VARCHAR2
2730 , p_dimension_varchar_label IN VARCHAR2
2731 )
2732 IS
2733 --
2734 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Dim_Metadata_Info';
2735 --
2736 l_param_info VARCHAR2(4000) ;
2737 l_curr_activity VARCHAR2(4000) ;
2738 --
2739 -- Retrieve the metadata information of
2740 -- the supplied p_dimension_varchar_label.
2741 CURSOR l_Ret_Dim_Metadata_csr
2742 IS
2743 SELECT
2744 dimension_id
2745 , intf_member_b_table_name
2746 , intf_member_tl_table_name
2747 , intf_attribute_table_name
2748 , member_b_table_name
2749 , member_display_code_col
2750 , member_name_col
2751 , hierarchy_table_name
2752 , dimension_type_code
2753 , group_use_code
2754 , value_set_required_flag
2755 FROM
2756 fem_xdim_dimensions_vl xDimVL
2757 WHERE
2758 xDimVL.dimension_varchar_label = p_dimension_varchar_label ;
2759
2760 -- Retrieve all the mappings for
2761 -- the supplied dimension to reuse
2762 -- accross the APIs.
2763 CURSOR l_Ret_Attr_Mapp_csr
2764 IS
2765 SELECT
2766 AttrMaps.interface_col
2767 , AttrMaps.attribute_varchar_label
2768 , AttrMaps.data_type
2769 FROM
2770 fem_webadi_dim_attr_maps AttrMaps
2771 WHERE
2772 AttrMaps.dimension_varchar_label = p_dimension_varchar_label ;
2773 --
2774 BEGIN
2775 --
2776 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2777 THEN
2778 --
2779 l_param_info := 'p_dimension_varchar_label='||p_dimension_varchar_label ;
2780 l_curr_activity := 'Starting Populate_Dim_Metadata_Info API ' ;
2781 --
2782 -- Put the param info into log.
2783 FND_LOG.String
2784 ( log_level => FND_LOG.LEVEL_STATEMENT
2785 , module => l_api_name
2786 , message => 'Parametr Info: ' || l_param_info
2787 ) ;
2788 --
2789 -- Put the current activity into log.
2790 FND_LOG.String
2791 ( log_level => FND_LOG.LEVEL_STATEMENT
2792 , module => l_api_name
2793 , message => 'Activity: ' || l_curr_activity
2794 ) ;
2795 --
2796 END IF ;
2797 --
2798 -- Initialize object types
2799 g_interface_col_name_tbl := FND_TABLE_OF_VARCHAR2_30() ;
2800 g_attribute_name_tbl := FND_TABLE_OF_VARCHAR2_30() ;
2801 g_attribute_data_type_tbl := FND_TABLE_OF_VARCHAR2_30() ;
2802 --
2803 g_global_val_tbl.DELETE ;
2804 --
2805 -- Bulk collect all attribute mappings into
2806 -- global PL/SQL tables for further usage.
2807 OPEN l_Ret_Attr_Mapp_csr ;
2808 LOOP
2809 FETCH l_Ret_Attr_Mapp_csr
2810 BULK COLLECT INTO
2811 g_interface_col_name_tbl
2812 , g_attribute_name_tbl
2813 , g_attribute_data_type_tbl ;
2814 EXIT WHEN l_Ret_Attr_Mapp_csr%NOTFOUND;
2815 END LOOP ;
2816 CLOSE l_Ret_Attr_Mapp_csr ;
2817 --
2818 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2819 THEN
2820 --
2821 l_curr_activity := 'Bulk operation completed successfully.' ;
2822 --
2823 -- Put the current activity into log.
2824 FND_LOG.String
2825 ( log_level => FND_LOG.LEVEL_STATEMENT
2826 , module => l_api_name
2827 , message => 'Activity: ' || l_curr_activity
2828 ) ;
2829 --
2830 END IF ;
2831 --
2832 -- Populate global variables with Dimemension metadata.
2833 FOR l_ret_dim_metadata_csr_rec IN l_Ret_Dim_Metadata_csr
2834 LOOP
2835 g_global_val_tbl(1).dimension_id :=
2836 l_ret_dim_metadata_csr_rec.dimension_id ;
2837 g_global_val_tbl(1).dimension_varchar_label :=
2838 p_dimension_varchar_label ;
2839 g_global_val_tbl(1).intf_member_b_table_name :=
2840 l_ret_dim_metadata_csr_rec.intf_member_b_table_name ;
2841 g_global_val_tbl(1).intf_member_tl_table_name :=
2842 l_ret_dim_metadata_csr_rec.intf_member_tl_table_name ;
2843 g_global_val_tbl(1).intf_attribute_table_name :=
2844 l_ret_dim_metadata_csr_rec.intf_attribute_table_name ;
2845 g_global_val_tbl(1).member_b_table_name :=
2846 l_ret_dim_metadata_csr_rec.member_b_table_name ;
2847 g_global_val_tbl(1).member_display_code_col :=
2848 l_ret_dim_metadata_csr_rec.member_display_code_col ;
2849 g_global_val_tbl(1).member_name_col :=
2850 l_ret_dim_metadata_csr_rec.member_name_col ;
2851 g_global_val_tbl(1).hierarchy_intf_table_name :=
2852 l_ret_dim_metadata_csr_rec.hierarchy_table_name || '_T' ;
2853 g_global_val_tbl(1).dimension_type_code :=
2854 NVL( l_ret_dim_metadata_csr_rec.dimension_type_code, 'XYZ' ) ;
2855 g_global_val_tbl(1).group_use_code :=
2856 NVL( l_ret_dim_metadata_csr_rec.group_use_code, 'NOT_SUPPORTED' ) ;
2857 g_global_val_tbl(1).value_set_required_flag :=
2858 NVL( l_ret_dim_metadata_csr_rec.value_set_required_flag, 'N' ) ;
2859 END LOOP ;
2860 --
2861 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2862 --
2863 --
2864 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2865 THEN
2866 --
2867 l_curr_activity := 'Populate_Dim_Metadata_Info API completed ' ||
2868 'successfully' ;
2869 --
2870 -- Put the current activity into log.
2871 FND_LOG.String
2872 ( log_level => FND_LOG.LEVEL_STATEMENT
2873 , module => l_api_name
2874 , message => 'Activity: ' || l_curr_activity
2875 ) ;
2876 --
2877 END IF ;
2878 --
2879 EXCEPTION
2880 WHEN FND_API.G_EXC_ERROR THEN
2881 --
2882 x_return_status := FND_API.G_RET_STS_ERROR ;
2883 --
2884 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2885 THEN
2886 -- Put exact error message into log.
2887 FND_LOG.String
2888 ( log_level => FND_LOG.LEVEL_STATEMENT
2889 , module => l_api_name
2890 , message => 'SQL Error ' || sqlerrm
2891 ) ;
2892 END IF ;
2893 --
2894 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2895 --
2896 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2897 --
2898 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2899 THEN
2900 -- Put exact error message into log.
2901 FND_LOG.String
2902 ( log_level => FND_LOG.LEVEL_STATEMENT
2903 , module => l_api_name
2904 , message => 'SQL Error ' || sqlerrm
2905 ) ;
2906 END IF ;
2907 --
2908 WHEN OTHERS THEN
2909 --
2910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2911 --
2912 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2913 THEN
2914 -- Put exact error message into log.
2915 FND_LOG.String
2916 ( log_level => FND_LOG.LEVEL_STATEMENT
2917 , module => l_api_name
2918 , message => 'SQL Error ' || sqlerrm
2919 ) ;
2920 END IF ;
2921 --
2922 END Populate_Dim_Metadata_Info ;
2923
2924 -----------------------------
2925 -- Write Public Procedures --
2926 -----------------------------
2927
2928 /*===========================================================================+
2929 Procedure Name : Populate_Dim_Attribute_Maps
2930 Parameters :
2931 IN : p_dimension_varchar_label VARCHAR2
2932 p_api_version NUMBER
2933 p_init_msg_list VARCHAR2
2934 p_commit VARCHAR2
2935 OUT : All standard parameters.
2936
2937 Description : This procedure stores attributes to the
2938 FEM_WebADI_attr_map table for a dimension.
2939 Note that this API will be called well
2940 before actual upload process to setup the
2941 mappings.
2942 Modification History :
2943 Date Name Desc
2944 ---------- --------- -------------------------------------------------------
2945 09/22/2005 SHTRIPAT Created.
2946 ---------- --------- -------------------------------------------------------
2947 +===========================================================================*/
2948 PROCEDURE Populate_Dim_Attribute_Maps
2949 ( x_return_status OUT NOCOPY VARCHAR2
2950 , x_msg_count OUT NOCOPY NUMBER
2951 , x_msg_data OUT NOCOPY VARCHAR2
2952 , p_api_version IN NUMBER
2953 , p_init_msg_list IN VARCHAR2
2954 , p_commit IN VARCHAR2
2955 , p_dimension_varchar_label IN VARCHAR2
2956 )
2957 IS
2958 --
2959 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Dim_Attribute_Maps' ;
2960 l_param_info VARCHAR2(4000) ;
2961 l_curr_activity VARCHAR2(4000) ;
2962 --
2963 l_return_status VARCHAR2(1) ;
2964 l_msg_count NUMBER ;
2965 l_msg_data VARCHAR2(2000) ;
2966 --
2967 l_record_count NUMBER ;
2968 --
2969 l_max_attr_seq NUMBER := 0 ;
2970 l_avlbl_attr_seq NUMBER := 0 ;
2971 l_start_seq NUMBER := 1 ;
2972 l_cnt_rec NUMBER := 0 ;
2973 l_gap_logic_req BOOLEAN := FALSE ;
2974 l_used_seq_flag BOOLEAN := FALSE ;
2975 --
2976 l_data_type_tbl FND_TABLE_OF_VARCHAR2_30 := FND_TABLE_OF_VARCHAR2_30() ;
2977 l_attr_label_tbl FND_TABLE_OF_VARCHAR2_30 := FND_TABLE_OF_VARCHAR2_30() ;
2978 l_avlbl_attrseq_tbl FND_TABLE_OF_NUMBER := FND_TABLE_OF_NUMBER() ;
2979 --
2980 -- Retrieve all mappings for which datatypes are not matching
2981 -- with corresponding record in dim attribute table for the
2982 -- input dimension varchar label.
2983 CURSOR l_changed_datatype_map_csr
2984 IS
2985 SELECT
2986 attrmap.interface_col
2987 , dimattr.attribute_data_type_code
2988 FROM
2989 fem_dim_attributes_b dimattr
2990 , fem_xdim_dimensions_vl xdim
2991 , fem_webadi_dim_attr_maps attrmap
2992 WHERE
2993 attrmap.dimension_varchar_label = p_dimension_varchar_label
2994 AND xdim.dimension_varchar_label = attrmap.dimension_varchar_label
2995 AND dimattr.dimension_id = xdim.dimension_id
2996 AND dimattr.attribute_varchar_label = attrmap.attribute_varchar_label
2997 AND dimattr.attribute_data_type_code <> attrmap.data_type ;
2998 --
2999 -- Retrieve all records present in dim attribute table but
3000 -- absent in attribute mapping table for the input
3001 -- dimension varchar label.
3002 CURSOR l_new_dim_attr_rec_csr
3003 IS
3004 SELECT
3005 dimattr.attribute_varchar_label
3006 , dimattr.attribute_data_type_code
3007 FROM
3008 fem_dim_attributes_b dimattr
3009 , fem_xdim_dimensions_vl xdim
3010 WHERE
3011 xdim.dimension_varchar_label = p_dimension_varchar_label
3012 AND xdim.dimension_id = dimattr.dimension_id
3013 AND NOT EXISTS
3014 ( SELECT
3015 attrmap.attribute_varchar_label
3016 FROM
3017 fem_webadi_dim_attr_maps attrmap
3018 WHERE
3019 attrmap.dimension_varchar_label = xdim.dimension_varchar_label
3020 AND attrmap.attribute_varchar_label = dimattr.attribute_varchar_label
3021 )
3022 ORDER BY dimattr.attribute_required_flag DESC;
3023 --
3024 -- Retrieve all available mapping sequences for given dimension
3025 CURSOR l_avlble_col_map_seq_csr
3026 IS
3027 SELECT
3028 VALUE(avlble_seq) col_map_seq
3029 FROM
3030 TABLE( CAST( g_sequences_tbl AS FND_TABLE_OF_NUMBER ) ) avlble_seq
3031 MINUS
3032 SELECT
3033 TO_NUMBER( SUBSTR( attrmap.interface_col, 12 ) ) col_map_seq
3034 FROM
3035 fem_webadi_dim_attr_maps attrmap
3036 WHERE
3037 attrmap.dimension_varchar_label = p_dimension_varchar_label ;
3038 --
3039 BEGIN
3040 --
3041 SAVEPOINT Populate_Dim_Attribute_Maps ;
3042 --
3043 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3044 THEN
3045 --
3046 l_param_info := 'p_dimension_varchar_label='||p_dimension_varchar_label ;
3047 l_curr_activity := 'Starting Populate_Dim_Attribute_Maps API ' ;
3048 --
3049 -- Put the param info into log.
3050 FND_LOG.String
3051 ( log_level => FND_LOG.LEVEL_STATEMENT
3052 , module => l_api_name
3053 , message => 'Parametr Info: ' || l_param_info
3054 ) ;
3055 --
3056 -- Put the current activity into log.
3057 FND_LOG.String
3058 ( log_level => FND_LOG.LEVEL_STATEMENT
3059 , module => l_api_name
3060 , message => 'Activity: ' || l_curr_activity
3061 ) ;
3062 --
3063 END IF ;
3064 --
3065 -- Initialize global PL/SQL tables being used by other APIs.
3066 g_changed_intf_col_tbl := g_initialized_table ;
3067 g_changed_dt_intf_col_tbl := g_initialized_table ;
3068 --
3069 -- Delete records present in Mapping table but not in
3070 -- FEM_DIM_ATTRIBUTES_B table.
3071 DELETE
3072 FROM
3073 fem_webadi_dim_attr_maps attrmap
3074 WHERE
3075 attrmap.dimension_varchar_label = p_dimension_varchar_label
3076 AND attrmap.attribute_varchar_label NOT IN
3077 ( SELECT
3078 attr.attribute_varchar_label
3079 FROM
3080 fem_dim_attributes_b attr
3081 , fem_xdim_dimensions_vl xdim
3082 WHERE
3083 xdim.dimension_varchar_label = attrmap.dimension_varchar_label
3084 AND attr.dimension_id = xdim.dimension_id
3085 ) ;
3086 --
3087 --
3088 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3089 THEN
3090 --
3091 l_curr_activity := 'Stale records deleted. Now Updating records with ' ||
3092 'changed datatype.' ;
3093 --
3094 -- Put the current activity into log.
3095 FND_LOG.String
3096 ( log_level => FND_LOG.LEVEL_STATEMENT
3097 , module => l_api_name
3098 , message => 'Activity: ' || l_curr_activity
3099 ) ;
3100 --
3101 END IF ;
3102 --
3103 -- Bulk collect records with changed data type.
3104 OPEN l_changed_datatype_map_csr ;
3105 LOOP
3106 FETCH l_changed_datatype_map_csr
3107 BULK COLLECT INTO
3108 g_changed_dt_intf_col_tbl
3109 , l_data_type_tbl
3110 LIMIT G_LIMIT_BULK_NUMROWS ;
3111 --
3112
3113 IF ( g_changed_dt_intf_col_tbl.COUNT >= 1 )
3114 THEN
3115 FORALL l_indx IN 1..g_changed_dt_intf_col_tbl.COUNT
3116 UPDATE
3117 fem_webadi_dim_attr_maps attrmap
3118 SET
3119 attrmap.data_type = l_data_type_tbl(l_indx)
3120 WHERE
3121 attrmap.dimension_varchar_label = p_dimension_varchar_label
3122 AND attrmap.interface_col = g_changed_dt_intf_col_tbl(l_indx) ;
3123 END IF ;
3124 --
3125 EXIT WHEN l_changed_datatype_map_csr%NOTFOUND ;
3126 END LOOP ;
3127 CLOSE l_changed_datatype_map_csr ;
3128 --
3129 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3130 THEN
3131 --
3132 l_curr_activity := 'Update of records with changed data type done.' ;
3133 --
3134 -- Put the current activity into log.
3135 FND_LOG.String
3136 ( log_level => FND_LOG.LEVEL_STATEMENT
3137 , module => l_api_name
3138 , message => 'Activity: ' || l_curr_activity
3139 ) ;
3140 --
3141 END IF ;
3142 --
3143 -- Find the maximum interface column number
3144 -- being used by mapping table for the input
3145 -- dimension varchar label.
3146 SELECT
3147 NVL( MAX ( TO_NUMBER( SUBSTR( attrmap.interface_col, 12 ) ) ), 0 )
3148 , COUNT(1)
3149 INTO
3150 l_max_attr_seq
3151 , l_cnt_rec
3152 FROM
3153 fem_webadi_dim_attr_maps attrmap
3154 WHERE
3155 attrmap.dimension_varchar_label = p_dimension_varchar_label ;
3156 --
3157 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3158 THEN
3159 --
3160 l_curr_activity := 'Maximum seq is ' || l_max_attr_seq ;
3161 --
3162 -- Put the current activity into log.
3163 FND_LOG.String
3164 ( log_level => FND_LOG.LEVEL_STATEMENT
3165 , module => l_api_name
3166 , message => 'Activity: ' || l_curr_activity
3167 ) ;
3168 --
3169 END IF ;
3170 --
3171 -- Reinitialize l_data_type_tbl
3172 l_data_type_tbl.DELETE ;
3173 --
3174 -- Bulk collect new mappings present in dim attr table only.
3175 OPEN l_new_dim_attr_rec_csr ;
3176 FETCH l_new_dim_attr_rec_csr
3177 BULK COLLECT INTO
3178 l_attr_label_tbl
3179 , l_data_type_tbl ;
3180 CLOSE l_new_dim_attr_rec_csr ;
3181 --
3182 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3183 THEN
3184 --
3185 FOR l_indx IN 1..l_attr_label_tbl.COUNT
3186 LOOP
3187 -- Put attribute details into log.
3188 FND_LOG.String
3189 ( log_level => FND_LOG.LEVEL_STATEMENT
3190 , module => l_api_name
3191 , message => 'Attr_Label: ' || l_attr_label_tbl(l_indx) ||
3192 'Attr_Datatype ' ||l_data_type_tbl(l_indx)
3193 ) ;
3194 --
3195 END LOOP ;
3196 END IF ;
3197 --
3198 -- Check whether there is any gap in P_ATTRIBUTE indexes.
3199 -- If count of records present in map table is lesser than
3200 -- the highest sequence being used, gap logic should be used.
3201 IF ( l_max_attr_seq > l_cnt_rec )
3202 THEN
3203 l_gap_logic_req := TRUE ;
3204 --
3205 --
3206 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3207 THEN
3208 --
3209 l_curr_activity := 'Gap logic is required' ;
3210 --
3211 -- Put the current activity into log.
3212 FND_LOG.String
3213 ( log_level => FND_LOG.LEVEL_STATEMENT
3214 , module => l_api_name
3215 , message => 'Activity: ' || l_curr_activity
3216 ) ;
3217 --
3218 END IF ;
3219 --
3220 END IF ;
3221 --
3222 -- If there is no gap, insert the records directly.
3223 IF ( l_gap_logic_req = FALSE )
3224 THEN
3225 --
3226 -- Populate global table with changed
3227 -- attributes
3228 FOR l_indx IN 1..l_attr_label_tbl.COUNT
3229 LOOP
3230 g_changed_intf_col_tbl.EXTEND ;
3231 g_changed_intf_col_tbl(l_indx) :=
3232 'P_ATTRIBUTE'||(l_max_attr_seq + l_indx) ;
3233 END LOOP ;
3234 --
3235 -- Now Bulk insert attributes in mapping table.
3236 FORALL l_indx IN 1..l_attr_label_tbl.COUNT
3237 INSERT INTO
3238 fem_webadi_dim_attr_maps
3239 ( interface_col
3240 , dimension_varchar_label
3241 , attribute_varchar_label
3242 , data_type
3243 )
3244 VALUES
3245 ( g_changed_intf_col_tbl( l_indx )
3246 , p_dimension_varchar_label
3247 , l_attr_label_tbl(l_indx)
3248 , l_data_type_tbl(l_indx)
3249 ) ;
3250 --
3251 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3252 THEN
3253 --
3254 l_curr_activity := 'Bulk inserting with no gap logic done.' ;
3255 --
3256 -- Put the current activity into log.
3257 FND_LOG.String
3258 ( log_level => FND_LOG.LEVEL_STATEMENT
3259 , module => l_api_name
3260 , message => 'Activity: ' || l_curr_activity
3261 ) ;
3262 --
3263 END IF ;
3264 --
3265 ELSE
3266 -- Retrieve all available mapping sequences for given dimension.
3267 OPEN l_avlble_col_map_seq_csr ;
3268 FETCH l_avlble_col_map_seq_csr
3269 BULK COLLECT INTO
3270 l_avlbl_attrseq_tbl ;
3271 CLOSE l_avlble_col_map_seq_csr ;
3272 --
3273 IF ( l_avlbl_attrseq_tbl.COUNT < l_attr_label_tbl.COUNT )
3274 THEN
3275 APP_EXCEPTION.Raise_Exception ;
3276 ELSE
3277 --
3278 -- Populate global table with changed
3279 -- attributes
3280 FOR l_indx IN 1..l_attr_label_tbl.COUNT
3281 LOOP
3282 g_changed_intf_col_tbl.EXTEND ;
3283 g_changed_intf_col_tbl(l_indx) :=
3284 'P_ATTRIBUTE'||( l_avlbl_attrseq_tbl( l_indx ) ) ;
3285 END LOOP ;
3286 -- Now bulk insert records in MApping table.
3287 FORALL l_indx IN 1..l_attr_label_tbl.COUNT
3288 INSERT INTO
3289 fem_webadi_dim_attr_maps
3290 ( interface_col
3291 , dimension_varchar_label
3292 , attribute_varchar_label
3293 , data_type
3294 )
3295 VALUES
3296 ( g_changed_intf_col_tbl(l_indx)
3297 , p_dimension_varchar_label
3298 , l_attr_label_tbl(l_indx)
3299 , l_data_type_tbl(l_indx)
3300 ) ;
3301 --
3302 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3303 THEN
3304 --
3305 l_curr_activity := 'Bulk insertion with gap logic done.' ;
3306 --
3307 -- Put the current activity into log.
3308 FND_LOG.String
3309 ( log_level => FND_LOG.LEVEL_STATEMENT
3310 , module => l_api_name
3311 , message => 'Activity: ' || l_curr_activity
3312 ) ;
3313 --
3314 END IF ;
3315 --
3316 END IF ;
3317 END IF ;
3318 --
3319 IF ( FND_API.To_Boolean (p_commit) )
3320 THEN
3321 COMMIT ;
3322 END IF ;
3323 --
3324 -- Initialize API return status to success
3325 x_return_status := FND_API.G_RET_STS_SUCCESS ;
3326 --
3327 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3328 THEN
3329 --
3330 l_curr_activity := 'Populate_Dim_Attribute_Maps completed successfully' ;
3331 --
3332 -- Put the current activity into log.
3333 FND_LOG.String
3334 ( log_level => FND_LOG.LEVEL_STATEMENT
3335 , module => l_api_name
3336 , message => 'Activity: ' || l_curr_activity
3337 ) ;
3338 --
3339 END IF ;
3340 --
3341 EXCEPTION
3342 WHEN FND_API.G_EXC_ERROR THEN
3343 --
3344 ROLLBACK TO Populate_Dim_Attribute_Maps ;
3345 --
3346 x_return_status := FND_API.G_RET_STS_ERROR ;
3347 --
3348 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3349 THEN
3350 -- Put exact error message into log.
3351 FND_LOG.String
3352 ( log_level => FND_LOG.LEVEL_STATEMENT
3353 , module => l_api_name
3354 , message => 'SQL Error ' || sqlerrm
3355 ) ;
3356 --
3357 END IF ;
3358 --
3359 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3360 --
3361 ROLLBACK TO Populate_Dim_Attribute_Maps ;
3362 --
3363 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3364 --
3365 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3366 THEN
3367 -- Put exact error message into log.
3368 FND_LOG.String
3369 ( log_level => FND_LOG.LEVEL_STATEMENT
3370 , module => l_api_name
3371 , message => 'SQL Error ' || sqlerrm
3372 ) ;
3373 --
3374 END IF ;
3375 --
3376 WHEN OTHERS THEN
3377 --
3378 ROLLBACK TO Populate_Dim_Attribute_Maps ;
3379 --
3380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3381 --
3382 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3383 THEN
3384 -- Put exact error message into log.
3385 FND_LOG.String
3386 ( log_level => FND_LOG.LEVEL_STATEMENT
3387 , module => l_api_name
3388 , message => 'SQL Error ' || sqlerrm
3389 ) ;
3390 --
3391 END IF ;
3392 --
3393 END Populate_Dim_Attribute_Maps ;
3394
3395 /*===========================================================================+
3396 Procedure Name : Pop_NonSimple_Dim_Intf_tables
3397 Parameters :
3398 IN : p_dim_grp_disp_code VARCHAR2
3399 p_value_set_required_flag VARCHAR2
3400 OUT : None
3401
3402 Description : This program populates member interface tables for
3403 non TIME dimensions. These tables involve B and TL
3404 tables only. The attribute interface table will be
3405 populated in Process_Atrribute API.
3406
3407 Modification History :
3408 Date Name Desc
3409 ---------- --------- -------------------------------------------------------
3410 09/23/2005 SHTRIPAT Created.
3411 ---------- --------- -------------------------------------------------------
3412 +===========================================================================*/
3413 PROCEDURE Pop_NonSimple_Dim_Intf_tables
3414 ( p_dim_grp_disp_code IN VARCHAR2
3415 , p_value_set_required_flag IN VARCHAR2
3416 )
3417 IS
3418 --
3419 l_api_name CONSTANT VARCHAR2(30) := 'Pop_NonSimple_Dim_Intf_tables';
3420 l_param_info VARCHAR2(4000) ;
3421 l_curr_activity VARCHAR2(4000) ;
3422 --
3423 l_b_table_str VARCHAR2(4000) := NULL ;
3424 l_tl_table_str VARCHAR2(4000) := NULL ;
3425 l_update_str VARCHAR2(4000) := NULL ;
3426 l_b_table_ins_clause VARCHAR2(4000) := NULL ;
3427 l_b_table_bind_clause VARCHAR2(4000) := NULL ;
3428 l_tl_table_ins_clause VARCHAR2(4000) := NULL ;
3429 l_tl_table_bind_clause VARCHAR2(4000) := NULL ;
3430 --
3431 l_record_found_count NUMBER := 0 ;
3432 --
3433 BEGIN
3434 --
3435 l_b_table_ins_clause := 'INSERT INTO ' ||
3436 g_global_val_tbl(1).intf_member_b_table_name ||
3437 '( ' ||
3438 g_global_val_tbl(1).member_display_code_col ||
3439 ', status' ;
3440
3441 l_b_table_bind_clause := 'VALUES' ||
3442 '( :b_member_display_code' ||
3443 ', :b_status' ;
3444 --
3445 l_tl_table_ins_clause := 'INSERT INTO ' ||
3446 g_global_val_tbl(1).intf_member_tl_table_name ||
3447 '( ' ||
3448 g_global_val_tbl(1).member_display_code_col ||
3449 ', language' ||
3450 ',' ||g_global_val_tbl(1).member_name_col ||
3451 ', description' ||
3452 ', status' ;
3453
3454 l_tl_table_bind_clause := 'VALUES' ||
3455 '( :b_member_display_code '||
3456 ', :b_language' ||
3457 ', :b_member_name' ||
3458 ', :b_description' ||
3459 ', :b_status' ;
3460 --
3461 IF ( p_value_set_required_flag = 'Y' ) -- VSR Yes
3462 THEN
3463 --
3464 -- 1st Case:: VSR Yes and Level Yes.
3465 --
3466 IF ( p_dim_grp_disp_code IS NOT NULL ) -- Levels Yes
3467 THEN
3468 --
3469 l_b_table_ins_clause := l_b_table_ins_clause ||
3470 ', value_set_display_code' ||
3471 ', dimension_group_display_code' ||
3472 ')' ;
3473 --
3474 l_b_table_bind_clause := l_b_table_bind_clause ||
3475 ', :b_value_set_display_code' ||
3476 ', :b_dimension_group_display_code' ||
3477 ')' ;
3478 --
3479 l_b_table_str := l_b_table_ins_clause || l_b_table_bind_clause ;
3480 --
3481 BEGIN
3482 --
3483 EXECUTE IMMEDIATE
3484 l_b_table_str
3485 USING
3486 g_global_val_tbl(1).member_display_code
3487 , 'LOAD'
3488 , g_global_val_tbl(1).value_set_display_code
3489 , g_global_val_tbl(1).dim_grp_disp_code ;
3490 --
3491 EXCEPTION
3492 --
3493 WHEN DUP_VAL_ON_INDEX THEN
3494 --
3495 l_update_str := 'UPDATE ' ||
3496 g_global_val_tbl(1).intf_member_b_table_name ||
3497 ' SET ' ||
3498 'status = :b_status' ||
3499 ', dimension_group_display_code = ' ||
3500 ':b_dimension_group_display_code' ||
3501 ' WHERE ' ||
3502 g_global_val_tbl(1).member_display_code_col ||
3503 ' = :b_member_display_code' ||
3504 ' AND value_set_display_code = ' ||
3505 ':b_value_set_display_code' ;
3506 --
3507 EXECUTE IMMEDIATE
3508 l_update_str
3509 USING
3510 'LOAD'
3511 , g_global_val_tbl(1).dim_grp_disp_code
3512 , g_global_val_tbl(1).member_display_code
3513 , g_global_val_tbl(1).value_set_display_code ;
3514 --
3515 END ;
3516 --
3517 ELSE --IF ( p_dim_grp_disp_code IS NULL ) -- Levels No
3518 --
3519 -- 2nd Case:: VSR Yes and Level No.
3520 --
3521 l_b_table_ins_clause := l_b_table_ins_clause ||
3522 ', value_set_display_code' ||
3523 ')' ;
3524 --
3525 l_b_table_bind_clause := l_b_table_bind_clause ||
3526 ', :b_value_set_display_code' ||
3527 ')' ;
3528 --
3529 l_b_table_str := l_b_table_ins_clause || l_b_table_bind_clause ;
3530 --
3531 BEGIN
3532 --
3533 EXECUTE IMMEDIATE
3534 l_b_table_str
3535 USING
3536 g_global_val_tbl(1).member_display_code
3537 , 'LOAD'
3538 , g_global_val_tbl(1).value_set_display_code ;
3539 --
3540 EXCEPTION
3541 --
3542 WHEN DUP_VAL_ON_INDEX THEN
3543 --
3544 l_update_str := 'UPDATE ' ||
3545 g_global_val_tbl(1).intf_member_b_table_name ||
3546 ' SET ' ||
3547 'status = :b_status' ||
3548 ' WHERE ' ||
3549 g_global_val_tbl(1).member_display_code_col ||
3550 ' = :b_member_display_code' ||
3551 ' AND value_set_display_code = ' ||
3552 ':b_value_set_display_code' ;
3553 --
3554 EXECUTE IMMEDIATE
3555 l_update_str
3556 USING
3557 'LOAD'
3558 , g_global_val_tbl(1).member_display_code
3559 , g_global_val_tbl(1).value_set_display_code ;
3560 --
3561 END ;
3562 --
3563 END IF ; --IF ( p_dim_grp_disp_code IS NOT NULL ) Ends
3564 --
3565 -- Now put the data into TL table.
3566 --
3567 l_tl_table_ins_clause := l_tl_table_ins_clause ||
3568 ', value_set_display_code' ||
3569 ')' ;
3570 --
3571 l_tl_table_bind_clause := l_tl_table_bind_clause ||
3572 ', :b_value_set_display_code' ||
3573 ')' ;
3574 --
3575 l_tl_table_str := l_tl_table_ins_clause || l_tl_table_bind_clause ;
3576 --
3577 BEGIN
3578 --
3579 EXECUTE IMMEDIATE
3580 l_tl_table_str
3581 USING
3582 g_global_val_tbl(1).member_display_code
3583 , g_session_language
3584 , g_global_val_tbl(1).member_name
3585 , g_global_val_tbl(1).member_description
3586 , 'LOAD'
3587 , g_global_val_tbl(1).value_set_display_code ;
3588 --
3589 EXCEPTION
3590 --
3591 WHEN DUP_VAL_ON_INDEX THEN
3592 --
3593 l_update_str := 'UPDATE ' ||
3594 g_global_val_tbl(1).intf_member_tl_table_name ||
3595 ' SET ' ||
3596 g_global_val_tbl(1).member_name_col ||
3597 ' = :b_member_name ' ||
3598 ', description = :b_description ' ||
3599 ', status = :b_status ' ||
3600 ' WHERE ' ||
3601 g_global_val_tbl(1).member_display_code_col ||
3602 ' = :b_member_display_code' ||
3603 ' AND value_set_display_code = ' ||
3604 ':b_value_set_display_code' ||
3605 ' AND language = :b_language ' ;
3606 --
3607 EXECUTE IMMEDIATE
3608 l_update_str
3609 USING
3610 g_global_val_tbl(1).member_name
3611 , g_global_val_tbl(1).member_description
3612 , 'LOAD'
3613 , g_global_val_tbl(1).member_display_code
3614 , g_global_val_tbl(1).value_set_display_code
3615 , g_session_language ;
3616 --
3617 IF ( SQL%ROWCOUNT = 0 )
3618 THEN
3619 --
3620 RAISE ;
3621 --
3622 END IF ;
3623 --
3624 END ;
3625 --
3626 ELSE -- IF ( p_value_set_required_flag <> 'Y' )
3627 --
3628 -- 3rd Case:: VSR No and Level Yes.
3629 --
3630 IF ( p_dim_grp_disp_code IS NOT NULL )
3631 THEN
3632 --
3633 l_b_table_ins_clause := l_b_table_ins_clause ||
3634 ', dimension_group_display_code' ||
3635 ')' ;
3636 --
3637 l_b_table_bind_clause := l_b_table_bind_clause ||
3638 ', :b_dimension_group_display_code' ||
3639 ')' ;
3640 --
3641 l_b_table_str := l_b_table_ins_clause || l_b_table_bind_clause ;
3642 --
3643 BEGIN
3644 --
3645 EXECUTE IMMEDIATE
3646 l_b_table_str
3647 USING
3648 g_global_val_tbl(1).member_display_code
3649 , 'LOAD'
3650 , g_global_val_tbl(1).dim_grp_disp_code ;
3651 --
3652 EXCEPTION
3653 --
3654 WHEN DUP_VAL_ON_INDEX THEN
3655 --
3656 l_update_str := 'UPDATE ' ||
3657 g_global_val_tbl(1).intf_member_b_table_name ||
3658 ' SET ' ||
3659 'status = :b_status' ||
3660 ', dimension_group_display_code = ' ||
3661 ':b_dimension_group_display_code' ||
3662 ' WHERE ' ||
3663 g_global_val_tbl(1).member_display_code_col ||
3664 ' = :b_member_display_code' ;
3665 --
3666 EXECUTE IMMEDIATE
3667 l_update_str
3668 USING
3669 'LOAD'
3670 , g_global_val_tbl(1).dim_grp_disp_code
3671 , g_global_val_tbl(1).member_display_code ;
3672 --
3673 END ;
3674 --
3675 ELSE -- IF ( p_dim_grp_disp_code IS NULL )
3676 --
3677 -- 4th Case:: VSR No and Level No.
3678 --
3679 l_b_table_ins_clause := l_b_table_ins_clause ||
3680 ')' ;
3681 --
3682 l_b_table_bind_clause := l_b_table_bind_clause ||
3683 ')' ;
3684 --
3685 l_b_table_str := l_b_table_ins_clause || l_b_table_bind_clause ;
3686 --
3687 BEGIN
3688 --
3689 EXECUTE IMMEDIATE
3690 l_b_table_str
3691 USING
3692 g_global_val_tbl(1).member_display_code
3693 , 'LOAD' ;
3694 --
3695 EXCEPTION
3696 --
3697 WHEN DUP_VAL_ON_INDEX THEN
3698 --
3699 l_update_str := 'UPDATE ' ||
3700 g_global_val_tbl(1).intf_member_b_table_name ||
3701 ' SET ' ||
3702 'status = :b_status' ||
3703 ' WHERE ' ||
3704 g_global_val_tbl(1).member_display_code_col ||
3705 ' = :b_member_display_code' ;
3706 --
3707 EXECUTE IMMEDIATE
3708 l_update_str
3709 USING
3710 'LOAD'
3711 , g_global_val_tbl(1).member_display_code ;
3712 --
3713 END ;
3714 --
3715 END IF ;
3716 -- Now for TL table.
3717 --
3718 l_tl_table_ins_clause := l_tl_table_ins_clause ||
3719 ')' ;
3720 --
3721 l_tl_table_bind_clause := l_tl_table_bind_clause ||
3722 ')' ;
3723 --
3724 l_tl_table_str := l_tl_table_ins_clause || l_tl_table_bind_clause ;
3725 --
3726 BEGIN
3727 --
3728 EXECUTE IMMEDIATE
3729 l_tl_table_str
3730 USING
3731 g_global_val_tbl(1).member_display_code
3732 , g_session_language
3733 , g_global_val_tbl(1).member_name
3734 , g_global_val_tbl(1).member_description
3735 , 'LOAD' ;
3736 --
3737 EXCEPTION
3738 --
3739 WHEN DUP_VAL_ON_INDEX THEN
3740 --
3741 l_update_str := 'UPDATE ' ||
3742 g_global_val_tbl(1).intf_member_tl_table_name ||
3743 ' SET ' ||
3744 g_global_val_tbl(1).member_name_col ||
3745 ' = :b_member_name ' ||
3746 ', description = :b_description ' ||
3747 ', status = :b_status ' ||
3748 ' WHERE ' ||
3749 g_global_val_tbl(1).member_display_code_col ||
3750 ' = :b_member_display_code' ||
3751 ' AND language = :b_language ' ;
3752 --
3753 EXECUTE IMMEDIATE
3754 l_update_str
3755 USING
3756 g_global_val_tbl(1).member_name
3757 , g_global_val_tbl(1).member_description
3758 , 'LOAD'
3759 , g_global_val_tbl(1).member_display_code
3760 , g_session_language ;
3761 --
3762 IF ( SQL%ROWCOUNT = 0 )
3763 THEN
3764 --
3765 RAISE ;
3766 --
3767 END IF ;
3768 --
3769 END ;
3770 --
3771 END IF ; -- IF ( p_value_set_required_flag <> 'Y' ) Ends
3772 --
3773 END Pop_NonSimple_Dim_Intf_tables ;
3774
3775 /*===========================================================================+
3776 Procedure Name : Pop_Other_Dim_Mem_Intf_table
3777 Parameters :
3778 IN : None
3779 OUT : x_cal_pr_end_date_col_name VARCHAR2
3780 x_gl_pr_num_col_name VARCHAR2
3781
3782 Description : This program populates member interface tables for
3783 non TIME dimensions. These tables involve B and TL
3784 tables only. The attribute interface table will be
3785 populated in Process_Atrribute API.
3786
3787 Modification History :
3788 Date Name Desc
3789 ---------- --------- -------------------------------------------------------
3790 09/23/2005 SHTRIPAT Created.
3791 ---------- --------- -------------------------------------------------------
3792 +===========================================================================*/
3793 PROCEDURE Pop_Other_Dim_Mem_Intf_table
3794 IS
3795 --
3796 l_api_name CONSTANT VARCHAR2(30) := 'Pop_Other_Dim_Mem_Intf_table';
3797 l_param_info VARCHAR2(4000) ;
3798 l_curr_activity VARCHAR2(4000) ;
3799 --
3800 l_b_table_str VARCHAR2(4000) := NULL ;
3801 l_tl_table_str VARCHAR2(4000) := NULL ;
3802 l_update_str VARCHAR2(4000) := NULL ;
3803 --
3804 l_record_found_count NUMBER := 0 ;
3805 --
3806 BEGIN
3807 --
3808 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3809 THEN
3810 --
3811 l_param_info := null ;
3812 l_curr_activity := 'Starting Pop_Other_Dim_Mem_Intf_table API ' ;
3813 --
3814 -- Put parameter information.
3815 FND_LOG.String
3816 ( log_level => FND_LOG.LEVEL_STATEMENT
3817 , module => l_api_name
3818 , message => 'Parametr Info: ' || l_param_info
3819 ) ;
3820 --
3821 -- Put the current activity into log.
3822 FND_LOG.String
3823 ( log_level => FND_LOG.LEVEL_STATEMENT
3824 , module => l_api_name
3825 , message => 'Activity: ' || l_curr_activity
3826 ) ;
3827 --
3828 END IF ;
3829 --
3830 --
3831 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3832 THEN
3833 --
3834 l_curr_activity := 'Not a time dimension. Insert in member b table' ;
3835 --
3836 -- Put the current activity into log.
3837 FND_LOG.String
3838 ( log_level => FND_LOG.LEVEL_STATEMENT
3839 , module => l_api_name
3840 , message => 'Activity: ' || l_curr_activity
3841 ) ;
3842 --
3843 END IF ;
3844 --
3845 -- Frame the dynamic insert sql
3846 -- to insert into B member table.
3847 -- ! IF the dimension is not of type "Simple Dimension"...
3848 IF ( g_global_val_tbl(1).intf_member_b_table_name <> 'FEM_SIMPLE_DIMS_B_T' )
3849 THEN
3850 --
3851 Pop_NonSimple_Dim_Intf_tables
3852 ( p_dim_grp_disp_code => g_global_val_tbl(1).dim_grp_disp_code
3853 , p_value_set_required_flag => g_global_val_tbl(1).value_set_required_flag
3854 ) ;
3855 --
3856 ELSE -- Dimension type is "Simple Dimension"
3857 l_b_table_str := 'INSERT INTO ' ||
3858 g_global_val_tbl(1).intf_member_b_table_name ||
3859 '( dimension_varchar_label' ||
3860 ', member_code' ||
3861 ', status' ||
3862 ')' ||
3863 'VALUES' ||
3864 '( :b_dim_varchar_label' ||
3865 ', :b_member_code' ||
3866 ', :b_status' ||
3867 ')' ;
3868 --
3869 BEGIN
3870 --
3871 EXECUTE IMMEDIATE
3872 l_b_table_str
3873 USING
3874 g_global_val_tbl(1).dimension_varchar_label
3875 , g_global_val_tbl(1).member_display_code
3876 , 'LOAD' ;
3877 --
3878 EXCEPTION
3879 --
3880 WHEN DUP_VAL_ON_INDEX THEN
3881 --
3882 l_update_str := 'UPDATE ' ||
3883 g_global_val_tbl(1).intf_member_b_table_name ||
3884 ' SET ' ||
3885 'status = :b_status' ||
3886 ' WHERE ' ||
3887 ' dimension_varchar_label' ||
3888 ' = :b_dimension_varchar_label' ||
3889 ' AND member_code = ' ||
3890 ':b_member_code' ;
3891 --
3892 EXECUTE IMMEDIATE
3893 l_update_str
3894 USING
3895 'LOAD'
3896 , g_global_val_tbl(1).dimension_varchar_label
3897 , g_global_val_tbl(1).member_display_code ;
3898 --
3899 END ;
3900 --
3901 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3902 THEN
3903 --
3904 l_curr_activity := 'Insert in member b table done, do it for TL table' ;
3905 --
3906 -- Put the current activity into log.
3907 FND_LOG.String
3908 ( log_level => FND_LOG.LEVEL_STATEMENT
3909 , module => l_api_name
3910 , message => 'Activity: ' || l_curr_activity
3911 ) ;
3912 --
3913 END IF ;
3914 --
3915 -- Now frame the dynamic insert sql
3916 -- to insert into TL member table.
3917 l_tl_table_str := 'INSERT INTO ' ||
3918 g_global_val_tbl(1).intf_member_tl_table_name ||
3919 '( dimension_varchar_label' ||
3920 ', member_code' ||
3921 ', language' ||
3922 ', member_name' ||
3923 ', description' ||
3924 ', status' ||
3925 ')' ||
3926 'VALUES' ||
3927 '( :b_dimension_varchar_label' ||
3928 ', :b_member_code' ||
3929 ', :b_language' ||
3930 ', :b_member_name' ||
3931 ', :b_description' ||
3932 ', :b_status' ||
3933 ')' ;
3934 --
3935 BEGIN
3936 --
3937 EXECUTE IMMEDIATE
3938 l_tl_table_str
3939 USING
3940 g_global_val_tbl(1).dimension_varchar_label
3941 , g_global_val_tbl(1).member_display_code
3942 , g_session_language
3943 , g_global_val_tbl(1).member_name
3944 , g_global_val_tbl(1).member_description
3945 , 'LOAD' ;
3946 --
3947 EXCEPTION
3948 --
3949 WHEN DUP_VAL_ON_INDEX THEN
3950 --
3951 l_update_str := 'UPDATE ' ||
3952 g_global_val_tbl(1).intf_member_tl_table_name ||
3953 ' SET ' ||
3954 'status = :b_status' ||
3955 ', description = :b_description' ||
3956 ', member_name' ||
3957 ' = :b_member_name ' ||
3958 ' WHERE ' ||
3959 ' dimension_varchar_label' ||
3960 ' = :b_dimension_varchar_label' ||
3961 ' AND member_code = :b_member_code ' ||
3962 ' AND language = :b_language';
3963 --
3964 BEGIN
3965 --
3966 EXECUTE IMMEDIATE --Update#1
3967 l_update_str
3968 USING
3969 'LOAD'
3970 , g_global_val_tbl(1).member_description
3971 , g_global_val_tbl(1).member_name
3972 , g_global_val_tbl(1).dimension_varchar_label
3973 , g_global_val_tbl(1).member_display_code
3974 , g_session_language ;
3975 --
3976 -- If no records updated.
3977 IF (SQL%ROWCOUNT = 0 )
3978 THEN
3979 RAISE ;
3980 END IF ;
3981 --
3982 -- Commenting the exception block. Will discuss it and finalize later.
3983 /*EXCEPTION
3984 --
3985 WHEN OTHERS THEN
3986 --
3987 -- Need to decide.
3988 APP_EXCEPTION.Raise_Exception ; */
3989 END ;
3990 --
3991 END ;
3992 --
3993 END IF ;
3994 --
3995 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3996 THEN
3997 --
3998 l_curr_activity := 'Pop_Other_Dim_Mem_Intf_table API completed ' ||
3999 'successfully' ;
4000 --
4001 -- Put the current activity into log.
4002 FND_LOG.String
4003 ( log_level => FND_LOG.LEVEL_STATEMENT
4004 , module => l_api_name
4005 , message => 'Activity: ' || l_curr_activity
4006 ) ;
4007 --
4008 END IF ;
4009 --
4010 END Pop_Other_Dim_Mem_Intf_table ;
4011
4012 /*===========================================================================+
4013 Procedure Name : Process_Attribute
4014 Parameters :
4015 IN : p_attribute_value VARCHAR2
4016 p_attribute_index NUMBER
4017 p_cal_pr_end_date_col_name VARCHAR2
4018 p_gl_pr_num_col_name VARCHAR2
4019 IN OUT : x_period_end_date_found BOOLEAN
4020 x_GL_period_num_found BOOLEAN
4021
4022 Description : This API does following tasks...
4023 1. Finds out attribute varchar label for each supplied
4024 attribute and its value.
4025 2. It populates TIME dimension's B and TL tables.
4026 3. It populates Non TIME dimensions' attribute intf
4027 table.
4028
4029 Modification History :
4030 Date Name Desc
4031 ---------- --------- -------------------------------------------------------
4032 09/23/2005 SHTRIPAT Created.
4033 ---------- --------- -------------------------------------------------------
4034 +===========================================================================*/
4035 PROCEDURE Process_Attribute
4036 ( x_period_end_date_found IN OUT NOCOPY BOOLEAN
4037 , x_GL_period_num_found IN OUT NOCOPY BOOLEAN
4038 , p_attribute_value IN VARCHAR2
4039 , p_attribute_index IN NUMBER
4040 , p_cal_pr_end_date_col_name IN VARCHAR2
4041 , p_gl_pr_num_col_name IN VARCHAR2
4042 )
4043 IS
4044 --
4045 l_api_name CONSTANT VARCHAR2(30) := 'Process_Attribute';
4046 l_param_info VARCHAR2(4000) ;
4047 l_curr_activity VARCHAR2(4000) ;
4048 --
4049 l_cal_pr_end_date_col_name VARCHAR2(30) ;
4050 l_cal_pr_num_col_name VARCHAR2(30) ;
4051 l_cal_pr_end_date_col_value VARCHAR2(30) ;
4052 l_cal_pr_num_col_name_value VARCHAR2(30) ;
4053 l_attribute_varchar_label VARCHAR2(30) ;
4054 --
4055 l_attr_t_str VARCHAR2(4000) := NULL ;
4056 l_b_table_str VARCHAR2(4000) := NULL ;
4057 l_tl_table_str VARCHAR2(4000) := NULL ;
4058 l_update_str VARCHAR2(4000) := NULL ;
4059 --
4060 l_match_found BOOLEAN ;
4061 l_default_version_flag VARCHAR2(1) := 'Y' ;
4062 --
4063 l_required_flag VARCHAR2(1) := 'N' ;
4064 --
4065 l_attribute_dimension_id fem_dim_attributes_b.attribute_dimension_id%TYPE ;
4066 l_value_set_required_flag VARCHAR2(1) ;
4067 --
4068 l_attr_asgn_vs_disp_code VARCHAR2(150) := NULL ;
4069 l_attr_value_set_id NUMBER ;
4070 --
4071 l_attribute_id NUMBER ;
4072 l_attribute_required_flag VARCHAR2(1) := 'Y' ;
4073 l_populate_attribute_table VARCHAR2(1) := 'Y' ;
4074 l_date_attribute_value DATE;
4075 l_adi_format_mask VARCHAR2(20) := FND_PROFILE.VALUE('FEM_INTF_ATTR_DATE_FORMAT_MASK');
4076 -- Find out attribute_dimension_id, version_display_code,
4077 -- attribute_value_set_required_flag.
4078 -- This information needs outer join with fem_xdim_dimensions
4079 -- table as attribute_dimension_id might be null in
4080 -- fem_dim_attributes_b table.
4081 CURSOR l_attr_csr
4082 ( dim_id NUMBER
4083 , attr_label VARCHAR2
4084 , requied_flag VARCHAR2
4085 )
4086 IS
4087 SELECT
4088 Attr.attribute_dimension_id
4089 , AttrVer.version_display_code
4090 , xDim.value_set_required_flag
4091 FROM
4092 fem_dim_attributes_b Attr
4093 , fem_dim_attr_versions_b AttrVer
4094 , fem_xdim_dimensions xDim
4095 WHERE
4096 Attr.dimension_id = dim_id
4097 AND Attr.attribute_varchar_label = attr_label
4098 AND Attr.attribute_id = AttrVer.attribute_id
4099 AND AttrVer.default_version_flag = requied_flag
4100 AND Attr.attribute_dimension_id = xDim.dimension_id(+) ; -- ** --
4101 --
4102 -- Retrieve attribute details.
4103 CURSOR l_retrieve_attr_details_csr
4104 ( attr_label VARCHAR2
4105 , dim_id NUMBER
4106 )
4107 IS
4108 SELECT
4109 dimattr.attribute_id
4110 , dimattr.attribute_required_flag
4111 FROM
4112 fem_dim_attributes_b dimattr
4113 WHERE
4114 dimattr.attribute_varchar_label = attr_label
4115 AND dimattr.dimension_id = dim_id ;
4116 --
4117 -- Check whether the attribute is associated
4118 -- with specified level.
4119 CURSOR l_chk_level_attr_existnce_csr
4120 ( attr_id NUMBER
4121 , dim_grp_disp_code VARCHAR2
4122 )
4123 IS
4124 SELECT
4125 1
4126 FROM
4127 fem_dim_attr_grps attrgrp
4128 , fem_dimension_grps_b dimgrp
4129 WHERE
4130 dimgrp.dimension_group_display_code = dim_grp_disp_code
4131 AND attrgrp.dimension_group_id = dimgrp.dimension_group_id
4132 AND attrgrp.attribute_id = attr_id ;
4133 -- **
4134 -- Just check whether inclusion of fem_dimension_grps_b.dimension_id
4135 -- in above cursor will quickly filter the records.
4136 -- **
4137 --
4138 -- Check whether the attribute is associated
4139 -- with any level.
4140 CURSOR l_check_attr_level_asso_csr
4141 ( attr_id NUMBER
4142 )
4143 IS
4144 SELECT
4145 1
4146 FROM
4147 fem_dim_attr_grps attrgrp
4148 WHERE
4149 attrgrp.attribute_id = attr_id ;
4150 --
4151 BEGIN
4152 --
4153 SAVEPOINT Process_Attribute ;
4154 --
4155 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
4156 THEN
4157 --
4158 l_param_info := null ;
4159 l_curr_activity := 'Starting Process_Attribute API for ' ||
4160 'p_attribute_'||p_attribute_index ;
4161 --
4162 -- Put parameter information.
4163 FND_LOG.String
4164 ( log_level => FND_LOG.LEVEL_STATEMENT
4165 , module => l_api_name
4166 , message => 'Parametr Info: ' || l_param_info
4167 ) ;
4168 --
4169 -- Put the current activity into log.
4170 FND_LOG.String
4171 ( log_level => FND_LOG.LEVEL_STATEMENT
4172 , module => l_api_name
4173 , message => 'Activity: ' || l_curr_activity
4174 ) ;
4175 --
4176 END IF ;
4177 --
4178 --
4179 -- Initialize the variable to FALSE.
4180 -- This variable will be used to exit
4181 -- out of the loop when the matching
4182 -- attribute_varchar_label is found.
4183 l_match_found := FALSE ;
4184
4185 -- Go ahead only if attribute value is null.
4186
4187 -- Loop through all defined mappings of dimension.
4188 -- If found then get the attribute varchar label.
4189 -- Common for both Time and other dimensions.
4190 FOR l_indx IN 1..g_interface_col_name_tbl.COUNT
4191 LOOP
4192 --
4193 IF ( g_interface_col_name_tbl( l_indx ) =
4194 'P_ATTRIBUTE'||p_attribute_index
4195 )
4196 THEN
4197 --
4198 l_attribute_varchar_label := g_attribute_name_tbl( l_indx ) ;
4199 l_match_found := TRUE ;
4200 --
4201 ------------- ********************** -------------------
4202 -- For Time Dimension, we need to populate attribute
4203 -- interface table along with two more records, one
4204 -- for CAL_PERIOD_END_DATE and another for GL_PERIOD_NUM
4205 -- , so populating global table here and will finally
4206 -- put into interface table in main API. this approach
4207 -- will avoid multiple execution of same code.
4208 ------------- ********************** -------------------
4209 g_not_null_attr_name_tbl.EXTEND ;
4210 --
4211 g_not_null_attr_name_tbl( g_not_null_attr_name_tbl.COUNT ) :=
4212 l_attribute_varchar_label ;
4213 g_not_null_attr_val_tbl( g_not_null_attr_val_tbl.COUNT + 1 ) :=
4214 p_attribute_value ;
4215 --
4216 -- Check whether the current attribute value is for
4217 -- Calander Period dimension's "CAL_PERIOD_END_DATE" attribute?
4218 IF ( x_period_end_date_found = FALSE
4219 AND
4220 l_attribute_varchar_label = 'CAL_PERIOD_END_DATE'
4221 )
4222 THEN
4223 --
4224 x_period_end_date_found := TRUE ;
4225 g_cal_pr_end_date_col_value := p_attribute_value ;
4226 g_date_end_date_value := to_date(g_cal_pr_end_date_col_value,l_adi_format_mask);
4227 --
4228 -- Check whether the current attribute value is for
4229 -- Calander Period dimension's "GL_PERIOD_NUM" attribute?
4230 ELSIF ( x_GL_period_num_found = FALSE
4231 AND
4232 l_attribute_varchar_label = 'GL_PERIOD_NUM'
4233 )
4234 THEN
4235 --
4236 x_GL_period_num_found := TRUE ;
4237 g_cal_pr_num_col_name_value := p_attribute_value ;
4238 --
4239 END IF ;
4240 --
4241 -- Corresponding attribute varchar label found,
4242 -- exit out of the loop now.
4243 EXIT ;
4244 --
4245 END IF ;
4246 END LOOP ;
4247 --
4248 --
4249 g_attribute_vs_display_code.EXTEND ;
4250 g_version_display_code.EXTEND ;
4251 --
4252 FOR l_attr_csr_rec IN l_attr_csr
4253 ( g_global_val_tbl(1).dimension_id
4254 , l_attribute_varchar_label
4255 , l_default_version_flag
4256 )
4257 LOOP
4258 --
4259 --
4260 l_attribute_dimension_id :=
4261 l_attr_csr_rec.attribute_dimension_id ;
4262 g_version_display_code(g_version_display_code.COUNT) :=
4263 l_attr_csr_rec.version_display_code ;
4264 l_value_set_required_flag :=
4265 l_attr_csr_rec.value_set_required_flag ;
4266 --
4267 END LOOP ;
4268
4269 -- If this is VSR attribute,
4270 -- get value set id and its display code.
4271 --
4272 IF ( l_value_set_required_flag = 'Y' )
4273 THEN
4274 --
4275 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
4276 THEN
4277 --
4278 l_curr_activity := 'Value Set Required Attribute. Get the display code';
4279 --
4280 -- Put the current activity into log.
4281 FND_LOG.String
4282 ( log_level => FND_LOG.LEVEL_STATEMENT
4283 , module => l_api_name
4284 , message => 'Activity: ' || l_curr_activity
4285 ) ;
4286 --
4287 END IF ;
4288 --
4289 l_attr_value_set_id := FEM_DIMENSION_UTIL_PKG.Dimension_Value_Set_Id
4290 ( l_attribute_dimension_id -- p_dimension_id
4291 , g_global_val_tbl(1).ledger_id -- p_ledger_id
4292 ) ;
4293 --
4294 g_attribute_vs_display_code(g_attribute_vs_display_code.COUNT) := NULL ;
4295 --
4296 SELECT
4297 ValSet.value_set_display_code
4298 INTO
4299 l_attr_asgn_vs_disp_code
4300 FROM
4301 fem_value_sets_b ValSet
4302 WHERE
4303 ValSet.value_set_id = l_attr_value_set_id ;
4304 --
4305 -- Bug#5056895
4306 -- Removed unnecessary IF condition.
4307 g_attribute_vs_display_code(g_attribute_vs_display_code.COUNT) :=
4308 l_attr_asgn_vs_disp_code ;
4309 --
4310 END IF ;
4311 --
4312 --
4313 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
4314 THEN
4315 --
4316 l_curr_activity := 'VS display code=' ||
4317 g_attribute_vs_display_code
4318 (g_attribute_vs_display_code.COUNT) ||
4319 ', attribute_dimension_id='||l_attribute_dimension_id||
4320 ', attr_asgn_vs_disp_code='||l_attr_asgn_vs_disp_code ;
4321 --
4322 -- Put the current activity into log.
4323 FND_LOG.String
4324 ( log_level => FND_LOG.LEVEL_STATEMENT
4325 , module => l_api_name
4326 , message => 'Activity: ' || l_curr_activity
4327 ) ;
4328 --
4329 END IF ;
4330 --
4331 -- ( ONLY FOR TIME DIMENSION )
4332 -- Now frame the dynamic insert sql
4333 -- to insert into B and TL member attribute tables.
4334
4335 -- If both values have been found then
4336 -- execute the insert statement.
4337 IF ( x_period_end_date_found AND x_GL_period_num_found ) --Condition#1 Start
4338 THEN
4339 l_b_table_str := 'INSERT INTO ' ||
4340 g_global_val_tbl(1).intf_member_b_table_name ||
4341 '( cal_period_end_date' ||
4342 ', cal_period_number' ||
4343 ', status' ||
4344 ', dimension_group_display_code' ||
4345 ', calendar_display_code' ||
4346 ')' ||
4347 'VALUES' ||
4348 '( :b_cal_period_end_date' ||
4349 ', :b_cal_period_number' ||
4350 ', :b_status' ||
4351 ', :b_dimension_group_display_code' ||
4352 ', :b_calendar_display_code' ||
4353 ')' ;
4354
4355 --
4356 BEGIN
4357 --
4358 EXECUTE IMMEDIATE
4359 l_b_table_str
4360 USING
4361 g_date_end_date_value
4362 , g_cal_pr_num_col_name_value
4363 , 'LOAD'
4364 , g_global_val_tbl(1).dim_grp_disp_code
4365 , g_global_val_tbl(1).calendar_display_code ;
4366 --
4367 EXCEPTION
4368 --
4369 WHEN DUP_VAL_ON_INDEX THEN
4370 --
4371 l_update_str := 'UPDATE ' ||
4372 g_global_val_tbl(1).intf_member_b_table_name ||
4373 ' SET ' ||
4374 'status = :b_status ' ||
4375 'WHERE ' ||
4376 'calendar_display_code = :b_calendar_display_code ' ||
4377 ' AND dimension_group_display_code = ' ||
4378 ' :b_dimension_group_display_code AND ' ||
4379 'cal_period_end_date = :b_cal_period_end_date AND ' ||
4380 'cal_period_number = :b_cal_period_number' ;
4381 --
4382 EXECUTE IMMEDIATE
4383 l_update_str
4384 USING
4385 'LOAD'
4386 , g_global_val_tbl(1).calendar_display_code
4387 , g_global_val_tbl(1).dim_grp_disp_code
4388 , g_date_end_date_value
4389 , g_cal_pr_num_col_name_value ;
4390 --
4391 END ;
4392 --
4393 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
4394 THEN
4395 --
4396 l_curr_activity := 'Insert in member b table done. Do it for TL table' ;
4397 --
4398 -- Put the current activity into log.
4399 FND_LOG.String
4400 ( log_level => FND_LOG.LEVEL_STATEMENT
4401 , module => l_api_name
4402 , message => 'Activity: ' || l_curr_activity
4403 ) ;
4404 --
4405 END IF ;
4406 --
4407 l_tl_table_str := 'INSERT INTO ' ||
4408 g_global_val_tbl(1).intf_member_tl_table_name ||
4409 '( cal_period_end_date' ||
4410 ', cal_period_number' ||
4411 ', language'||
4412 ', cal_period_name' ||
4413 ', description' ||
4414 ', status' ||
4415 ', calendar_display_code' ||
4416 ', dimension_group_display_code' ||
4417 ')' ||
4418 'VALUES' ||
4419 '( :b_cal_period_end_date' ||
4420 ', :b_cal_period_number' ||
4421 ', :b_language' ||
4422 ', :b_cal_period_name' ||
4423 ', :b_description'||
4424 ', :b_status' ||
4425 ', :b_calendar_display_code' ||
4426 ', :b_dimension_group_display_code' ||
4427 ')' ;
4428
4429 --
4430 BEGIN
4431 --
4432 EXECUTE IMMEDIATE
4433 l_tl_table_str
4434 USING
4435 g_date_end_date_value
4436 , g_cal_pr_num_col_name_value
4437 , g_session_language
4438 , g_global_val_tbl(1).member_name
4439 , g_global_val_tbl(1).member_description
4440 , 'LOAD'
4441 , g_global_val_tbl(1).calendar_display_code
4442 , g_global_val_tbl(1).dim_grp_disp_code ;
4443 --
4444 EXCEPTION
4445 --
4446 WHEN DUP_VAL_ON_INDEX THEN
4447 --
4448 l_update_str := 'UPDATE ' ||
4449 g_global_val_tbl(1).intf_member_tl_table_name ||
4450 ' SET ' ||
4451 'cal_period_name = :b_cal_period_name ' ||
4452 ', description = :b_description ' ||
4453 ', status = :b_status ' ||
4454 'WHERE ' ||
4455 'calendar_display_code = :b_calendar_display_code ' ||
4456 ' AND dimension_group_display_code = ' ||
4457 ' :b_dimension_group_display_code AND ' ||
4458 'cal_period_end_date = :b_cal_period_end_date AND ' ||
4459 'cal_period_number = :b_cal_period_number AND ' ||
4460 'language = :b_language' ;
4461 --
4462 BEGIN
4463 --
4464 EXECUTE IMMEDIATE
4465 l_update_str
4466 USING
4467 g_global_val_tbl(1).member_name
4468 , g_global_val_tbl(1).member_description
4469 , 'LOAD'
4470 , g_global_val_tbl(1).calendar_display_code
4471 , g_global_val_tbl(1).dim_grp_disp_code
4472 , g_date_end_date_value
4473 , g_cal_pr_num_col_name_value
4474 , g_session_language ;
4475 --
4476 -- If no records updated.
4477 IF (SQL%ROWCOUNT = 0 )
4478 THEN
4479 RAISE ;
4480 END IF ;
4481 --
4482 -- Commenting the exception block. Will discuss it and finalize later.
4483 /*EXCEPTION
4484 --
4485 -- Give up now. Report the exception back to Web ADI.
4486 WHEN OTHERS THEN
4487 --
4488 APP_EXCEPTION.Raise_Exception ;*/
4489 --
4490 END ;
4491 --
4492 END ;
4493 --
4494 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
4495 THEN
4496 --
4497 l_curr_activity := 'Insert in TL done. For time attrib intf table' ;
4498 --
4499 -- Put the current activity into log.
4500 FND_LOG.String
4501 ( log_level => FND_LOG.LEVEL_STATEMENT
4502 , module => l_api_name
4503 , message => 'Activity: ' || l_curr_activity
4504 ) ;
4505 --
4506 END IF ;
4507 --
4508 ELSIF ( g_global_val_tbl(1).dimension_type_code <> 'TIME' ) -- Non Time dim
4509 THEN
4510 --
4511 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
4512 THEN
4513 --
4514 l_curr_activity := 'Insert in TL done. For non-time attrib intf table' ;
4515 --
4516 -- Put the current activity into log.
4517 FND_LOG.String
4518 ( log_level => FND_LOG.LEVEL_STATEMENT
4519 , module => l_api_name
4520 , message => 'Activity: ' || l_curr_activity
4521 ) ;
4522 --
4523 END IF ;
4524 --
4525 -- Now frame the insert statement for
4526 -- member attribute interface table.
4527 --
4528 -- Bug in FEM, for some dimensions, Attribute table is
4529 -- absent. So putting the following check.
4530 -- Condition#2 Start
4531 IF ( g_global_val_tbl(1).intf_attribute_table_name IS NOT NULL )
4532 THEN
4533 --
4534 -- We will populate only those attributes into table which are
4535 -- 1.. Associated to a level in case dim_grp_disp_code is not null.
4536 -- 2.. Not associated to any level in case dim_grp_disp_code is null
4537 -- and group_use_code is 'OPTIONAL' for the dimension.
4538 --
4539 -- Check whether the attribute is required or not.
4540 -- Check it's association with level only when its optional.
4541 FOR l_attr_rec IN l_retrieve_attr_details_csr
4542 ( l_attribute_varchar_label
4543 , g_global_val_tbl(1).dimension_id
4544 )
4545 LOOP
4546 --
4547 l_attribute_id := l_attr_rec.attribute_id ;
4548 l_attribute_required_flag := l_attr_rec.attribute_required_flag ;
4549 --
4550 END LOOP ;
4551 --
4552 -- Optional Attribute, go ahead.
4553 IF ( l_attribute_required_flag = 'N' )
4554 THEN
4555 --
4556 -- CASE#1 --
4557 -- Level is being used.
4558 IF ( g_global_val_tbl(1).dim_grp_disp_code IS NOT NULL )
4559 THEN
4560 --
4561 l_populate_attribute_table := 'N' ;
4562 --
4563 FOR l_rec IN l_chk_level_attr_existnce_csr
4564 ( l_attribute_id
4565 , g_global_val_tbl(1).dim_grp_disp_code
4566 )
4567 LOOP
4568 --
4569 l_populate_attribute_table := 'Y' ;
4570 --
4571 END LOOP ;
4572 --
4573 -- CASE#2 --
4574 -- No Level is specified.
4575 ELSIF ( g_global_val_tbl(1).dim_grp_disp_code IS NULL
4576 AND
4577 g_global_val_tbl(1).group_use_code = 'OPTIONAL'
4578 )
4579 THEN
4580 --
4581 FOR l_rec IN l_check_attr_level_asso_csr
4582 ( l_attribute_id
4583 )
4584 LOOP
4585 --
4586 l_populate_attribute_table := 'N' ;
4587 --
4588 END LOOP ;
4589 --
4590 END IF ;
4591 --
4592 END IF ;
4593 --
4594 IF ( l_populate_attribute_table = 'Y' ) -- Condition#3 Start
4595 THEN
4596 -- If attribute table is not FEM_SHARED_ATTR_T
4597 --
4598 --Condition#4 Start
4599 IF ( g_global_val_tbl(1).intf_attribute_table_name
4600 <>
4601 'FEM_SHARED_ATTR_T'
4602 )
4603 THEN
4604 --
4605 IF ( g_global_val_tbl(1).value_set_required_flag = 'Y' )
4606 THEN
4607 --
4608 l_update_str := 'UPDATE ' ||
4609 g_global_val_tbl(1).intf_attribute_table_name ||
4610 ' SET ' ||
4611 ' status = :b_status ' ||
4612 ' , attribute_assign_value ' ||
4613 ' = :b_attrib_asgn_value ' ||
4614 'WHERE ' ||
4615 g_global_val_tbl(1).member_display_code_col ||
4616 ' = :b_member_disp_code ' ||
4617 'AND value_set_display_code ' ||
4618 ' = :b_vs_disp_code ' ||
4619 'AND attribute_varchar_label ' ||
4620 ' = :b_attrib_varchar_label '||
4621 'AND version_display_code ' ||
4622 ' = :b_ver_disp_code ' ||
4623 'AND NVL(attr_assign_vs_display_code, ''XYZ'') ' ||
4624 ' = NVL(:b_asgn_vs_disp_code, ''XYZ'')' ;
4625 --
4626 -- Bug#5056895
4627 -- Replaced g_attribute_vs_display_code by
4628 -- g_global_val_tbl(1).value_set_display_code.
4629 EXECUTE IMMEDIATE
4630 l_update_str
4631 USING
4632 'LOAD'
4633 , p_attribute_value
4634 , g_global_val_tbl(1).member_display_code
4635 , g_global_val_tbl(1).value_set_display_code
4636 , l_attribute_varchar_label
4637 , g_version_display_code(g_version_display_code.COUNT)
4638 , l_attr_asgn_vs_disp_code ;
4639 --
4640 -- No record exists for the condtion. Insert the record.
4641 IF ( SQL%ROWCOUNT = 0 )
4642 THEN
4643 --
4644 l_attr_t_str := 'INSERT INTO ' ||
4645 g_global_val_tbl(1).intf_attribute_table_name ||
4646 '( ' ||
4647 g_global_val_tbl(1).member_display_code_col ||
4648 ', attribute_varchar_label' ||
4649 ', value_set_display_code' ||
4650 ', attribute_assign_value' ||
4651 ', attr_assign_vs_display_code' ||
4652 ', status' ||
4653 ', version_display_code' ||
4654 ')' ||
4655 'VALUES' ||
4656 '( :b_member_display_code' ||
4657 ', :b_attribute_varchar_label' ||
4658 ', :b_value_set_display_code' ||
4659 ', :b_attribute_assign_value' ||
4660 ', :b_attr_assign_vs_display_code' ||
4661 ', :b_status' ||
4662 ', :b_version_display_code' ||
4663 ')' ;
4664 --
4665 -- Bug#5056895
4666 -- Replaced g_attribute_vs_display_code by
4667 -- g_global_val_tbl(1).value_set_display_code.
4668 EXECUTE IMMEDIATE
4669 l_attr_t_str
4670 USING
4671 g_global_val_tbl(1).member_display_code
4672 , l_attribute_varchar_label
4673 , g_global_val_tbl(1).value_set_display_code
4674 , p_attribute_value
4675 , l_attr_asgn_vs_disp_code
4676 , 'LOAD'
4677 , g_version_display_code(g_version_display_code.COUNT) ;
4678 --
4679 END IF ;
4680 --
4681 ELSE
4682 --
4683 l_update_str := 'UPDATE ' ||
4684 g_global_val_tbl(1).intf_attribute_table_name ||
4685 ' SET ' ||
4686 ' status = :b_status ' ||
4687 ' , attribute_assign_value ' ||
4688 ' = :b_attrib_asgn_value ' ||
4689 'WHERE ' ||
4690 g_global_val_tbl(1).member_display_code_col ||
4691 ' = :b_member_disp_code AND ' ||
4692 'attribute_varchar_label ' ||
4693 ' = :b_attrib_varchar_label '||
4694 'AND version_display_code ' ||
4695 ' = :b_ver_disp_code ' ||
4696 'AND NVL(attr_assign_vs_display_code, ''XYZ'') ' ||
4697 ' = NVL(:b_asgn_vs_disp_code, ''XYZ'') ' ;
4698 --
4699 EXECUTE IMMEDIATE
4700 l_update_str
4701 USING
4702 'LOAD'
4703 , p_attribute_value
4704 , g_global_val_tbl(1).member_display_code
4705 , l_attribute_varchar_label
4706 , g_version_display_code(g_version_display_code.COUNT)
4707 , l_attr_asgn_vs_disp_code ;
4708 --
4709 -- No record exists for the condtion. Insert the record.
4710 IF ( SQL%ROWCOUNT = 0 )
4711 THEN
4712 --
4713 l_attr_t_str := 'INSERT INTO ' ||
4714 g_global_val_tbl(1).intf_attribute_table_name ||
4715 '( ' ||
4716 g_global_val_tbl(1).member_display_code_col ||
4717 ', attribute_varchar_label' ||
4718 ', attribute_assign_value' ||
4719 ', attr_assign_vs_display_code' ||
4720 ', status' ||
4721 ', version_display_code' ||
4722 ')' ||
4723 'VALUES' ||
4724 '( :b_member_display_code' ||
4725 ', :b_attribute_varchar_label' ||
4726 ', :b_attribute_assign_value' ||
4727 ', :b_attr_assign_vs_display_code' ||
4728 ', :b_status' ||
4729 ', :b_version_display_code' ||
4730 ')' ;
4731 --
4732 EXECUTE IMMEDIATE
4733 l_attr_t_str
4734 USING
4735 g_global_val_tbl(1).member_display_code
4736 , l_attribute_varchar_label
4737 , p_attribute_value
4738 , l_attr_asgn_vs_disp_code
4739 , 'LOAD'
4740 , g_version_display_code(g_version_display_code.COUNT) ;
4741 --
4742 END IF ;
4743 --
4744 END IF ;
4745 --
4746 ELSE -- For Simple Dim
4747 --
4748 l_update_str := 'UPDATE ' ||
4749 'FEM_SHARED_ATTR_T' ||
4750 ' SET ' ||
4751 ' status = :b_status ' ||
4752 ' , attribute_assign_value ' ||
4753 ' = :b_attribute_assign_value ' ||
4754 'WHERE ' ||
4755 ' dimension_varchar_label = ' ||
4756 ':b_dimension_varchar_label' ||
4757 ' AND member_code = ' ||
4758 ':b_member_code' ||
4759 ' AND attribute_varchar_label = ' ||
4760 ':b_attribute_varchar_label '||
4761 ' AND version_display_code = ' ||
4762 ':b_version_display_code' ||
4763 ' AND NVL(attr_assign_vs_display_code, ''XYZ'')' ||
4764 ' = NVL(:b_attr_assign_vs_display_code, ''XYZ'') ' ;
4765
4766 --
4767 EXECUTE IMMEDIATE
4768 l_update_str
4769 USING
4770 'LOAD'
4771 , p_attribute_value
4772 , g_global_val_tbl(1).dimension_varchar_label
4773 , g_global_val_tbl(1).member_display_code
4774 , l_attribute_varchar_label
4775 , g_version_display_code(g_version_display_code.COUNT)
4776 , l_attr_asgn_vs_disp_code ;
4777 --
4778 -- No record exists for the condtion. Insert the record.
4779 IF ( SQL%ROWCOUNT = 0 )
4780 THEN
4781 --
4782 l_attr_t_str := 'INSERT INTO ' ||
4783 'FEM_SHARED_ATTR_T' ||
4784 '( dimension_varchar_label' ||
4785 ', member_code' ||
4786 ', attribute_varchar_label' ||
4787 ', version_display_code' ||
4788 ', attribute_assign_value' ||
4789 ', attr_assign_vs_display_code' ||
4790 ', status' ||
4791 ')' ||
4792 'VALUES' ||
4793 '( :dimension_varchar_label' ||
4794 ', :b_member_code' ||
4795 ', :b_attribute_varchar_label' ||
4796 ', :b_version_display_code' ||
4797 ', :b_attribute_assign_value' ||
4798 ', :b_attr_assign_vs_display_code' ||
4799 ', :b_status' ||
4800 ')' ;
4801 --
4802 EXECUTE IMMEDIATE
4803 l_attr_t_str
4804 USING
4805 g_global_val_tbl(1).dimension_varchar_label
4806 , g_global_val_tbl(1).member_display_code
4807 , l_attribute_varchar_label
4808 , g_version_display_code(g_version_display_code.COUNT)
4809 , p_attribute_value
4810 , l_attr_asgn_vs_disp_code
4811 , 'LOAD' ;
4812 --
4813 END IF ;
4814 --
4815 END IF ; --Condition#4 End ( Check for attribute table type )
4816 --
4817 END IF ; --Condition#3 End ( Conditional attribute population )
4818 --
4819 END IF ; --Condition#2 End ( Existence of attribute table )
4820 --
4821 END IF ; --Condition#1 End ( TIME or Non TIME dimension )
4822 --
4823 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
4824 THEN
4825 --
4826 l_curr_activity := 'Process_Attribute API for ' ||
4827 'p_attribute_'||p_attribute_index ||
4828 ' completed successfully.' ;
4829 --
4830 -- Put the current activity into log.
4831 FND_LOG.String
4832 ( log_level => FND_LOG.LEVEL_STATEMENT
4833 , module => l_api_name
4834 , message => 'Activity: ' || l_curr_activity
4835 ) ;
4836 --
4837 END IF ;
4838 --
4839 END Process_Attribute ;
4840
4841 /*===========================================================================+
4842 Procedure Name : Upload_Member_Interface
4843 Parameters :
4844 IN : p_interface_dimension_name VARCHAR2
4845 p_dimension_varchar_label VARCHAR2
4846 p_ledger_id VARCHAR2
4847 p_calendar_display_code VARCHAR2
4848 p_member_name VARCHAR2
4849 p_member_display_code VARCHAR2
4850 p_member_description VARCHAR2
4851 p_dimension_group_display_code VARCHAR2
4852 P_ATTRIBUTE1..50 VARCHAR2
4853 OUT : None
4854
4855 Description : This program creates members in member interface table
4856 and attribute information in dimension member attribute
4857 interface table.
4858 Modification History :
4859 Date Name Desc
4860 ---------- --------- -------------------------------------------------------
4861 09/23/2005 SHTRIPAT Created.
4862 ---------- --------- -------------------------------------------------------
4863 +===========================================================================*/
4864 PROCEDURE Upload_Member_Interface
4865 ( p_interface_dimension_name IN VARCHAR2
4866 , p_dimension_varchar_label IN VARCHAR2
4867 , p_ledger_id IN NUMBER
4868 , p_calendar_display_code IN VARCHAR2
4869 , p_member_name IN VARCHAR2
4870 , p_member_display_code IN VARCHAR2
4871 , p_member_description IN VARCHAR2
4872 , p_dimension_group_display_code IN VARCHAR2
4873 , P_ATTRIBUTE1 IN VARCHAR2
4874 , P_ATTRIBUTE2 IN VARCHAR2
4875 , P_ATTRIBUTE3 IN VARCHAR2
4876 , P_ATTRIBUTE4 IN VARCHAR2
4877 , P_ATTRIBUTE5 IN VARCHAR2
4878 , P_ATTRIBUTE6 IN VARCHAR2
4879 , P_ATTRIBUTE7 IN VARCHAR2
4880 , P_ATTRIBUTE8 IN VARCHAR2
4881 , P_ATTRIBUTE9 IN VARCHAR2
4882 , P_ATTRIBUTE10 IN VARCHAR2
4883 , P_ATTRIBUTE11 IN VARCHAR2
4884 , P_ATTRIBUTE12 IN VARCHAR2
4885 , P_ATTRIBUTE13 IN VARCHAR2
4886 , P_ATTRIBUTE14 IN VARCHAR2
4887 , P_ATTRIBUTE15 IN VARCHAR2
4888 , P_ATTRIBUTE16 IN VARCHAR2
4889 , P_ATTRIBUTE17 IN VARCHAR2
4890 , P_ATTRIBUTE18 IN VARCHAR2
4891 , P_ATTRIBUTE19 IN VARCHAR2
4892 , P_ATTRIBUTE20 IN VARCHAR2
4893 , P_ATTRIBUTE21 IN VARCHAR2
4894 , P_ATTRIBUTE22 IN VARCHAR2
4895 , P_ATTRIBUTE23 IN VARCHAR2
4896 , P_ATTRIBUTE24 IN VARCHAR2
4897 , P_ATTRIBUTE25 IN VARCHAR2
4898 , P_ATTRIBUTE26 IN VARCHAR2
4899 , P_ATTRIBUTE27 IN VARCHAR2
4900 , P_ATTRIBUTE28 IN VARCHAR2
4901 , P_ATTRIBUTE29 IN VARCHAR2
4902 , P_ATTRIBUTE30 IN VARCHAR2
4903 , P_ATTRIBUTE31 IN VARCHAR2
4904 , P_ATTRIBUTE32 IN VARCHAR2
4905 , P_ATTRIBUTE33 IN VARCHAR2
4906 , P_ATTRIBUTE34 IN VARCHAR2
4907 , P_ATTRIBUTE35 IN VARCHAR2
4908 , P_ATTRIBUTE36 IN VARCHAR2
4909 , P_ATTRIBUTE37 IN VARCHAR2
4910 , P_ATTRIBUTE38 IN VARCHAR2
4911 , P_ATTRIBUTE39 IN VARCHAR2
4912 , P_ATTRIBUTE40 IN VARCHAR2
4913 , P_ATTRIBUTE41 IN VARCHAR2
4914 , P_ATTRIBUTE42 IN VARCHAR2
4915 , P_ATTRIBUTE43 IN VARCHAR2
4916 , P_ATTRIBUTE44 IN VARCHAR2
4917 , P_ATTRIBUTE45 IN VARCHAR2
4918 , P_ATTRIBUTE46 IN VARCHAR2
4919 , P_ATTRIBUTE47 IN VARCHAR2
4920 , P_ATTRIBUTE48 IN VARCHAR2
4921 , P_ATTRIBUTE49 IN VARCHAR2
4922 , P_ATTRIBUTE50 IN VARCHAR2
4923 )
4924 IS
4925 --
4926 l_api_name CONSTANT VARCHAR2(30) := 'Upload_Member_Interface';
4927 l_param_info VARCHAR2(4000) ;
4928 l_curr_activity VARCHAR2(4000) ;
4929 --
4930 l_return_status VARCHAR2(1) ;
4931 l_msg_count NUMBER ;
4932 l_msg_data VARCHAR2(2000) ;
4933 --
4934 l_attr_t_str VARCHAR2(4000) := NULL ;
4935 l_update_str VARCHAR2(4000) := NULL ;
4936 --
4937 l_curr_attr_label VARCHAR2(30) := NULL ;
4938 l_curr_attr_value VARCHAR2(4000) := NULL ;
4939 --
4940 l_cal_pr_end_date_col_name VARCHAR2(30) ;
4941 l_cal_pr_num_col_name VARCHAR2(30) ;
4942 l_period_end_date_found BOOLEAN := FALSE ;
4943 l_cal_period_num_found BOOLEAN := FALSE ;
4944 l_not_null_attr_count NUMBER := -1 ;
4945 --
4946 l_populate_attribute_table VARCHAR2(1) := 'Y' ;
4947 --
4948 l_attribute_id NUMBER ;
4949 l_attribute_required_flag VARCHAR2(1) := 'Y' ;
4950 l_cal_period_end_date DATE;
4951 l_adi_format_mask VARCHAR2(20) := FND_PROFILE.VALUE('FEM_INTF_ATTR_DATE_FORMAT_MASK');
4952 --
4953 -- Retrieve the value_set_display_code for given ledger_id
4954 -- and dimension_id.
4955 CURSOR l_VS_Disp_Code_csr
4956 ( dim_id NUMBER
4957 , ledger NUMBER
4958 )
4959 IS
4960 SELECT
4961 VS.value_set_display_code
4962 FROM
4963 fem_Value_Sets_vl VS
4964 WHERE
4965 VS.value_set_id = ( FEM_DIMENSION_UTIL_PKG.Dimension_Value_Set_Id
4966 ( dim_id -- p_dimension_id
4967 , ledger -- p_ledger_id
4968 )
4969 ) ;
4970 --
4971 -- Retrieve attribute details.
4972 CURSOR l_retrieve_attr_details_csr
4973 ( attr_label VARCHAR2
4974 , dim_id NUMBER
4975 )
4976 IS
4977 SELECT
4978 dimattr.attribute_id
4979 ,dimattr.attribute_required_flag, dimattr.attribute_data_type_code
4980 FROM
4981 fem_dim_attributes_b dimattr
4982 WHERE
4983 dimattr.attribute_varchar_label = attr_label
4984 AND dimattr.dimension_id = dim_id ;
4985 --
4986 -- Check whether the attribute is associated
4987 -- with specified level.
4988 CURSOR l_chk_level_attr_existnce_csr
4989 ( attr_id NUMBER
4990 , dim_grp_disp_code VARCHAR2
4991 )
4992 IS
4993 SELECT
4994 1
4995 FROM
4996 fem_dim_attr_grps attrgrp
4997 , fem_dimension_grps_b dimgrp
4998 WHERE
4999 dimgrp.dimension_group_display_code = dim_grp_disp_code
5000 AND attrgrp.dimension_group_id = dimgrp.dimension_group_id
5001 AND attrgrp.attribute_id = attr_id ;
5002 -- **
5003 -- Just check whether inclusion of fem_dimension_grps_b.dimension_id
5004 -- in above cursor will quickly filter the records.
5005 -- **
5006 --
5007 BEGIN
5008 --
5009 SAVEPOINT Upload_Member_Interface ;
5010 --
5011 l_param_info := NULL ;
5012 l_curr_activity := NULL ;
5013 --
5014 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
5015 THEN
5016 --
5017 l_param_info := ' p_dimension_varchar_label='||p_dimension_varchar_label||
5018 ',p_ledger_id = ' || p_ledger_id ||
5019 ',p_calendar_display_code = ' ||p_calendar_display_code||
5020 ',p_member_name = ' ||p_member_name ||
5021 ',p_member_display_code = '|| p_member_display_code ||
5022 ',p_dimension_group_display_code='||
5023 p_dimension_group_display_code ;
5024 l_curr_activity := 'Starting Upload_Member_Interface API ' ;
5025 --
5026 -- Put parameter information.
5027 FND_LOG.String
5028 ( log_level => FND_LOG.LEVEL_STATEMENT
5029 , module => l_api_name
5030 , message => 'Parametr Info: ' || l_param_info
5031 ) ;
5032 --
5033 -- Put the current activity into log.
5034 FND_LOG.String
5035 ( log_level => FND_LOG.LEVEL_STATEMENT
5036 , module => l_api_name
5037 , message => 'Activity: ' || l_curr_activity
5038 ) ;
5039 --
5040 END IF ;
5041 --
5042 -- Exit if P_DIMENSION_VARCHAR_LABEL parameter
5043 -- contains NULL value. This will happen in case
5044 -- of multiple dimension interfaces. This is Web ADI
5045 -- limitation.
5046 --
5047 IF ( p_dimension_varchar_label IS NULL )
5048 THEN
5049 --
5050 RETURN ;
5051 --
5052 END IF ;
5053 --
5054 -- Retrieve user's langauge value.
5055 --
5056 g_session_language := USERENV('LANG') ;
5057 --
5058 g_not_null_attr_name_tbl := FND_TABLE_OF_VARCHAR2_30() ;
5059 g_not_null_attr_val_tbl.DELETE ;
5060
5061 -- Bug#6446663 - Begin
5062 g_attribute_vs_display_code := FND_TABLE_OF_VARCHAR2_255() ;
5063 g_version_display_code := FND_TABLE_OF_VARCHAR2_255() ;
5064 -- Bug#6446663 - Begin
5065
5066 --
5067 -- Check whether API has been run for p_dimension_varchar_label before.
5068 -- If not then populate dimension metadata information once.
5069 IF ( ( g_global_val_tbl.EXISTS(1)
5070 AND
5071 g_global_val_tbl(1).dimension_varchar_label <>
5072 p_dimension_varchar_label
5073 )
5074 OR
5075 g_global_val_tbl.COUNT = 0
5076 )
5077 THEN
5078 --
5079 -- Populate global variables with metadata information of
5080 -- the supplied p_dimension_varchar_label.
5081 -- Other APIs can reuse the populated global variables.
5082 -- This will be done only once.
5083 --
5084 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
5085 THEN
5086 --
5087 l_curr_activity := 'Calling Populate_Dim_Metadata_Info API ' ||
5088 'for ' || p_dimension_varchar_label ;
5089 --
5090 -- Put the current activity into log.
5091 FND_LOG.String
5092 ( log_level => FND_LOG.LEVEL_STATEMENT
5093 , module => l_api_name
5094 , message => 'Activity: ' || l_curr_activity
5095 ) ;
5096 --
5097 END IF ;
5098 --
5099 Populate_Dim_Metadata_Info
5100 ( x_return_status => l_return_status
5101 , p_dimension_varchar_label => p_dimension_varchar_label
5102 ) ;
5103 --
5104 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
5105 THEN
5106 RAISE FND_API.G_EXC_ERROR ;
5107 END IF ;
5108 --
5109 END IF ;
5110 --
5111 -- Put the Dimension parameter information into global table.
5112 g_global_val_tbl(1).ledger_id := p_ledger_id ;
5113 g_global_val_tbl(1).member_display_code := p_member_display_code ;
5114 g_global_val_tbl(1).calendar_display_code := p_calendar_display_code ;
5115 g_global_val_tbl(1).member_name := p_member_name ;
5116 g_global_val_tbl(1).member_description := p_member_description ;
5117 --
5118 -- Get the Value_Set_Display_code if
5119 -- g_global_val_tbl(1).value_set_required_flag is Y.
5120 g_global_val_tbl(1).value_set_display_code := NULL ;
5121 IF ( g_global_val_tbl(1).value_set_required_flag = 'Y' )
5122 THEN
5123 --
5124 FOR l_VS_Disp_Code_csr_rec IN l_VS_Disp_Code_csr
5125 ( g_global_val_tbl(1).dimension_id
5126 , g_global_val_tbl(1).ledger_id
5127 )
5128 LOOP
5129 g_global_val_tbl(1).value_set_display_code :=
5130 l_VS_Disp_Code_csr_rec.value_set_display_code ;
5131 END LOOP ;
5132 --
5133 END IF ;
5134 --
5135 g_global_val_tbl(1).dim_grp_disp_code := NULL ;
5136 -- If group_use_code is <> NOT_SUPPORTED, then
5137 -- assign p_dimension_group_display_code.
5138 IF ( g_global_val_tbl(1).group_use_code <> 'NOT_SUPPORTED' )
5139 THEN
5140 g_global_val_tbl(1).dim_grp_disp_code :=
5141 p_dimension_group_display_code ;
5142 END IF ;
5143 --
5144 -- Populate Members Process Starts.
5145 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
5146 THEN
5147 --
5148 l_curr_activity := 'Calling Pop_Other_Dim_Mem_Intf_table API ' ||
5149 ' for '||l_cal_pr_end_date_col_name ||' and ' ||
5150 l_cal_pr_num_col_name ;
5151 --
5152 -- Put the current activity into log.
5153 FND_LOG.String
5154 ( log_level => FND_LOG.LEVEL_STATEMENT
5155 , module => l_api_name
5156 , message => 'Activity: ' || l_curr_activity
5157 ) ;
5158 --
5159 END IF ;
5160 --
5161 -- If not a TIME dimension, then proceed.
5162 IF ( g_global_val_tbl(1).dimension_type_code <> 'TIME' )
5163 THEN
5164 --
5165 Pop_Other_Dim_Mem_Intf_table ;
5166 --
5167 END IF ;
5168 --
5169 --
5170 -- Populate Members Process Ends, though the insert
5171 -- statements will be placed alongwith attribute
5172 -- population.
5173 --
5174 --
5175 -- Populate Attribute Process Starts.
5176 --
5177 -- l_period_end_date_found and l_cal_period_num_found
5178 -- IN OUT parameters are being used for performance
5179 -- purposes. Once CAL_PERIOD dim related attribute
5180 -- values are found, no need to check them again.
5181 --
5182 -- Process P_ATTRIBUTE1
5183 IF ( P_ATTRIBUTE1 IS NOT NULL )
5184 THEN
5185 Process_Attribute
5186 ( x_period_end_date_found => l_period_end_date_found
5187 , x_GL_period_num_found => l_cal_period_num_found
5188 , p_attribute_value => P_ATTRIBUTE1
5189 , p_attribute_index => 1
5190 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5191 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5192 ) ;
5193 END IF ;
5194
5195 -- Process P_ATTRIBUTE2
5196 IF ( P_ATTRIBUTE2 IS NOT NULL )
5197 THEN
5198 Process_Attribute
5199 ( x_period_end_date_found => l_period_end_date_found
5200 , x_GL_period_num_found => l_cal_period_num_found
5201 , p_attribute_value => P_ATTRIBUTE2
5202 , p_attribute_index => 2
5203 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5204 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5205 ) ;
5206 END IF ;
5207
5208 -- Process P_ATTRIBUTE3
5209 IF ( P_ATTRIBUTE3 IS NOT NULL )
5210 THEN
5211 Process_Attribute
5212 ( x_period_end_date_found => l_period_end_date_found
5213 , x_GL_period_num_found => l_cal_period_num_found
5214 , p_attribute_value => P_ATTRIBUTE3
5215 , p_attribute_index => 3
5216 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5217 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5218 ) ;
5219 END IF ;
5220
5221 -- Process P_ATTRIBUTE4
5222 IF ( P_ATTRIBUTE4 IS NOT NULL )
5223 THEN
5224 Process_Attribute
5225 ( x_period_end_date_found => l_period_end_date_found
5226 , x_GL_period_num_found => l_cal_period_num_found
5227 , p_attribute_value => P_ATTRIBUTE4
5228 , p_attribute_index => 4
5229 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5230 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5231 ) ;
5232 END IF ;
5233
5234 -- Process P_ATTRIBUTE5
5235 IF ( P_ATTRIBUTE5 IS NOT NULL )
5236 THEN
5237 Process_Attribute
5238 ( x_period_end_date_found => l_period_end_date_found
5239 , x_GL_period_num_found => l_cal_period_num_found
5240 , p_attribute_value => P_ATTRIBUTE5
5241 , p_attribute_index => 5
5242 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5243 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5244 ) ;
5245 END IF ;
5246
5247 -- Process P_ATTRIBUTE6
5248 IF ( P_ATTRIBUTE6 IS NOT NULL )
5249 THEN
5250 Process_Attribute
5251 ( x_period_end_date_found => l_period_end_date_found
5252 , x_GL_period_num_found => l_cal_period_num_found
5253 , p_attribute_value => P_ATTRIBUTE6
5254 , p_attribute_index => 6
5255 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5256 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5257 ) ;
5258 END IF ;
5259
5260 -- Process P_ATTRIBUTE7
5261 IF ( P_ATTRIBUTE7 IS NOT NULL )
5262 THEN
5263 Process_Attribute
5264 ( x_period_end_date_found => l_period_end_date_found
5265 , x_GL_period_num_found => l_cal_period_num_found
5266 , p_attribute_value => P_ATTRIBUTE7
5267 , p_attribute_index => 7
5268 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5269 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5270 ) ;
5271 END IF ;
5272
5273 -- Process P_ATTRIBUTE8
5274 IF ( P_ATTRIBUTE8 IS NOT NULL )
5275 THEN
5276 Process_Attribute
5277 ( x_period_end_date_found => l_period_end_date_found
5278 , x_GL_period_num_found => l_cal_period_num_found
5279 , p_attribute_value => P_ATTRIBUTE8
5280 , p_attribute_index => 8
5281 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5282 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5283 ) ;
5284 END IF ;
5285
5286 -- Process P_ATTRIBUTE9
5287 IF ( P_ATTRIBUTE9 IS NOT NULL )
5288 THEN
5289 Process_Attribute
5290 ( x_period_end_date_found => l_period_end_date_found
5291 , x_GL_period_num_found => l_cal_period_num_found
5292 , p_attribute_value => P_ATTRIBUTE9
5293 , p_attribute_index => 9
5294 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5295 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5296 ) ;
5297 END IF ;
5298
5299 -- Process P_ATTRIBUTE10
5300 IF ( P_ATTRIBUTE10 IS NOT NULL )
5301 THEN
5302 Process_Attribute
5303 ( x_period_end_date_found => l_period_end_date_found
5304 , x_GL_period_num_found => l_cal_period_num_found
5305 , p_attribute_value => P_ATTRIBUTE10
5306 , p_attribute_index => 10
5307 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5308 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5309 ) ;
5310 END IF ;
5311
5312 -- Process P_ATTRIBUTE11
5313 IF ( P_ATTRIBUTE11 IS NOT NULL )
5314 THEN
5315 Process_Attribute
5316 ( x_period_end_date_found => l_period_end_date_found
5317 , x_GL_period_num_found => l_cal_period_num_found
5318 , p_attribute_value => P_ATTRIBUTE11
5319 , p_attribute_index => 11
5320 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5321 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5322 ) ;
5323 END IF ;
5324
5325 -- Process P_ATTRIBUTE12
5326 IF ( P_ATTRIBUTE12 IS NOT NULL )
5327 THEN
5328 Process_Attribute
5329 ( x_period_end_date_found => l_period_end_date_found
5330 , x_GL_period_num_found => l_cal_period_num_found
5331 , p_attribute_value => P_ATTRIBUTE12
5332 , p_attribute_index => 12
5333 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5334 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5335 ) ;
5336 END IF ;
5337
5338 -- Process P_ATTRIBUTE13
5339 IF ( P_ATTRIBUTE13 IS NOT NULL )
5340 THEN
5341 Process_Attribute
5342 ( x_period_end_date_found => l_period_end_date_found
5343 , x_GL_period_num_found => l_cal_period_num_found
5344 , p_attribute_value => P_ATTRIBUTE13
5345 , p_attribute_index => 13
5346 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5347 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5348 ) ;
5349 END IF ;
5350
5351 -- Process P_ATTRIBUTE14
5352 IF ( P_ATTRIBUTE14 IS NOT NULL )
5353 THEN
5354 Process_Attribute
5355 ( x_period_end_date_found => l_period_end_date_found
5356 , x_GL_period_num_found => l_cal_period_num_found
5357 , p_attribute_value => P_ATTRIBUTE14
5358 , p_attribute_index => 14
5359 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5360 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5361 ) ;
5362 END IF ;
5363
5364 -- Process P_ATTRIBUTE15
5365 IF ( P_ATTRIBUTE15 IS NOT NULL )
5366 THEN
5367 Process_Attribute
5368 ( x_period_end_date_found => l_period_end_date_found
5369 , x_GL_period_num_found => l_cal_period_num_found
5370 , p_attribute_value => P_ATTRIBUTE15
5371 , p_attribute_index => 15
5372 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5373 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5374 ) ;
5375 END IF ;
5376
5377 -- Process P_ATTRIBUTE16
5378 IF ( P_ATTRIBUTE16 IS NOT NULL )
5379 THEN
5380 Process_Attribute
5381 ( x_period_end_date_found => l_period_end_date_found
5382 , x_GL_period_num_found => l_cal_period_num_found
5383 , p_attribute_value => P_ATTRIBUTE16
5384 , p_attribute_index => 16
5385 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5386 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5387 ) ;
5388 END IF ;
5389
5390 -- Process P_ATTRIBUTE17
5391 IF ( P_ATTRIBUTE17 IS NOT NULL )
5392 THEN
5393 Process_Attribute
5394 ( x_period_end_date_found => l_period_end_date_found
5395 , x_GL_period_num_found => l_cal_period_num_found
5396 , p_attribute_value => P_ATTRIBUTE17
5397 , p_attribute_index => 17
5398 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5399 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5400 ) ;
5401 END IF ;
5402
5403 -- Process P_ATTRIBUTE18
5404 IF ( P_ATTRIBUTE18 IS NOT NULL )
5405 THEN
5406 Process_Attribute
5407 ( x_period_end_date_found => l_period_end_date_found
5408 , x_GL_period_num_found => l_cal_period_num_found
5409 , p_attribute_value => P_ATTRIBUTE18
5410 , p_attribute_index => 18
5411 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5412 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5413 ) ;
5414 END IF ;
5415
5416 -- Process P_ATTRIBUTE19
5417 IF ( P_ATTRIBUTE19 IS NOT NULL )
5418 THEN
5419 Process_Attribute
5420 ( x_period_end_date_found => l_period_end_date_found
5421 , x_GL_period_num_found => l_cal_period_num_found
5422 , p_attribute_value => P_ATTRIBUTE19
5423 , p_attribute_index => 19
5424 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5425 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5426 ) ;
5427 END IF ;
5428
5429 -- Process P_ATTRIBUTE20
5430 IF ( P_ATTRIBUTE20 IS NOT NULL )
5431 THEN
5432 Process_Attribute
5433 ( x_period_end_date_found => l_period_end_date_found
5434 , x_GL_period_num_found => l_cal_period_num_found
5435 , p_attribute_value => P_ATTRIBUTE20
5436 , p_attribute_index => 20
5437 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5438 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5439 ) ;
5440 END IF ;
5441
5442 -- Process P_ATTRIBUTE21
5443 IF ( P_ATTRIBUTE21 IS NOT NULL )
5444 THEN
5445 Process_Attribute
5446 ( x_period_end_date_found => l_period_end_date_found
5447 , x_GL_period_num_found => l_cal_period_num_found
5448 , p_attribute_value => P_ATTRIBUTE21
5449 , p_attribute_index => 21
5450 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5451 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5452 ) ;
5453 END IF ;
5454
5455 -- Process P_ATTRIBUTE22
5456 IF ( P_ATTRIBUTE22 IS NOT NULL )
5457 THEN
5458 Process_Attribute
5459 ( x_period_end_date_found => l_period_end_date_found
5460 , x_GL_period_num_found => l_cal_period_num_found
5461 , p_attribute_value => P_ATTRIBUTE22
5462 , p_attribute_index => 22
5463 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5464 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5465 ) ;
5466 END IF ;
5467
5468 -- Process P_ATTRIBUTE23
5469 IF ( P_ATTRIBUTE23 IS NOT NULL )
5470 THEN
5471 Process_Attribute
5472 ( x_period_end_date_found => l_period_end_date_found
5473 , x_GL_period_num_found => l_cal_period_num_found
5474 , p_attribute_value => P_ATTRIBUTE23
5475 , p_attribute_index => 23
5476 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5477 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5478 ) ;
5479 END IF ;
5480
5481 -- Process P_ATTRIBUTE24
5482 IF ( P_ATTRIBUTE24 IS NOT NULL )
5483 THEN
5484 Process_Attribute
5485 ( x_period_end_date_found => l_period_end_date_found
5486 , x_GL_period_num_found => l_cal_period_num_found
5487 , p_attribute_value => P_ATTRIBUTE24
5488 , p_attribute_index => 24
5489 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5490 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5491 ) ;
5492 END IF ;
5493
5494 -- Process P_ATTRIBUTE25
5495 IF ( P_ATTRIBUTE25 IS NOT NULL )
5496 THEN
5497 Process_Attribute
5498 ( x_period_end_date_found => l_period_end_date_found
5499 , x_GL_period_num_found => l_cal_period_num_found
5500 , p_attribute_value => P_ATTRIBUTE25
5501 , p_attribute_index => 25
5502 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5503 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5504 ) ;
5505 END IF ;
5506
5507 -- Process P_ATTRIBUTE26
5508 IF ( P_ATTRIBUTE26 IS NOT NULL )
5509 THEN
5510 Process_Attribute
5511 ( x_period_end_date_found => l_period_end_date_found
5512 , x_GL_period_num_found => l_cal_period_num_found
5513 , p_attribute_value => P_ATTRIBUTE26
5514 , p_attribute_index => 26
5515 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5516 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5517 ) ;
5518 END IF ;
5519
5520 -- Process P_ATTRIBUTE27
5521 IF ( P_ATTRIBUTE27 IS NOT NULL )
5522 THEN
5523 Process_Attribute
5524 ( x_period_end_date_found => l_period_end_date_found
5525 , x_GL_period_num_found => l_cal_period_num_found
5526 , p_attribute_value => P_ATTRIBUTE27
5527 , p_attribute_index => 27
5528 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5529 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5530 ) ;
5531 END IF ;
5532
5533 -- Process P_ATTRIBUTE28
5534 IF ( P_ATTRIBUTE28 IS NOT NULL )
5535 THEN
5536 Process_Attribute
5537 ( x_period_end_date_found => l_period_end_date_found
5538 , x_GL_period_num_found => l_cal_period_num_found
5539 , p_attribute_value => P_ATTRIBUTE28
5540 , p_attribute_index => 28
5541 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5542 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5543 ) ;
5544 END IF ;
5545
5546 -- Process P_ATTRIBUTE29
5547 IF ( P_ATTRIBUTE29 IS NOT NULL )
5548 THEN
5549 Process_Attribute
5550 ( x_period_end_date_found => l_period_end_date_found
5551 , x_GL_period_num_found => l_cal_period_num_found
5552 , p_attribute_value => P_ATTRIBUTE29
5553 , p_attribute_index => 29
5554 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5555 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5556 ) ;
5557 END IF ;
5558
5559 -- Process P_ATTRIBUTE30
5560 IF ( P_ATTRIBUTE30 IS NOT NULL )
5561 THEN
5562 Process_Attribute
5563 ( x_period_end_date_found => l_period_end_date_found
5564 , x_GL_period_num_found => l_cal_period_num_found
5565 , p_attribute_value => P_ATTRIBUTE30
5566 , p_attribute_index => 30
5567 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5568 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5569 ) ;
5570 END IF ;
5571
5572 -- Process P_ATTRIBUTE31
5573 IF ( P_ATTRIBUTE31 IS NOT NULL )
5574 THEN
5575 Process_Attribute
5576 ( x_period_end_date_found => l_period_end_date_found
5577 , x_GL_period_num_found => l_cal_period_num_found
5578 , p_attribute_value => P_ATTRIBUTE31
5579 , p_attribute_index => 31
5580 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5581 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5582 ) ;
5583 END IF ;
5584
5585 -- Process P_ATTRIBUTE32
5586 IF ( P_ATTRIBUTE32 IS NOT NULL )
5587 THEN
5588 Process_Attribute
5589 ( x_period_end_date_found => l_period_end_date_found
5590 , x_GL_period_num_found => l_cal_period_num_found
5591 , p_attribute_value => P_ATTRIBUTE32
5592 , p_attribute_index => 32
5593 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5594 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5595 ) ;
5596 END IF ;
5597
5598 -- Process P_ATTRIBUTE33
5599 IF ( P_ATTRIBUTE33 IS NOT NULL )
5600 THEN
5601 Process_Attribute
5602 ( x_period_end_date_found => l_period_end_date_found
5603 , x_GL_period_num_found => l_cal_period_num_found
5604 , p_attribute_value => P_ATTRIBUTE33
5605 , p_attribute_index => 33
5606 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5607 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5608 ) ;
5609 END IF ;
5610
5611 -- Process P_ATTRIBUTE34
5612 IF ( P_ATTRIBUTE34 IS NOT NULL )
5613 THEN
5614 Process_Attribute
5615 ( x_period_end_date_found => l_period_end_date_found
5616 , x_GL_period_num_found => l_cal_period_num_found
5617 , p_attribute_value => P_ATTRIBUTE34
5618 , p_attribute_index => 34
5619 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5620 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5621 ) ;
5622 END IF ;
5623
5624 -- Process P_ATTRIBUTE35
5625 IF ( P_ATTRIBUTE35 IS NOT NULL )
5626 THEN
5627 Process_Attribute
5628 ( x_period_end_date_found => l_period_end_date_found
5629 , x_GL_period_num_found => l_cal_period_num_found
5630 , p_attribute_value => P_ATTRIBUTE35
5631 , p_attribute_index => 35
5632 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5633 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5634 ) ;
5635 END IF ;
5636
5637 -- Process P_ATTRIBUTE36
5638 IF ( P_ATTRIBUTE36 IS NOT NULL )
5639 THEN
5640 Process_Attribute
5641 ( x_period_end_date_found => l_period_end_date_found
5642 , x_GL_period_num_found => l_cal_period_num_found
5643 , p_attribute_value => P_ATTRIBUTE36
5644 , p_attribute_index => 36
5645 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5646 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5647 ) ;
5648 END IF ;
5649
5650 -- Process P_ATTRIBUTE37
5651 IF ( P_ATTRIBUTE37 IS NOT NULL )
5652 THEN
5653 Process_Attribute
5654 ( x_period_end_date_found => l_period_end_date_found
5655 , x_GL_period_num_found => l_cal_period_num_found
5656 , p_attribute_value => P_ATTRIBUTE37
5657 , p_attribute_index => 37
5658 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5659 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5660 ) ;
5661 END IF ;
5662
5663 -- Process P_ATTRIBUTE38
5664 IF ( P_ATTRIBUTE38 IS NOT NULL )
5665 THEN
5666 Process_Attribute
5667 ( x_period_end_date_found => l_period_end_date_found
5668 , x_GL_period_num_found => l_cal_period_num_found
5669 , p_attribute_value => P_ATTRIBUTE38
5670 , p_attribute_index => 38
5671 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5672 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5673 ) ;
5674 END IF ;
5675
5676 -- Process P_ATTRIBUTE39
5677 IF ( P_ATTRIBUTE39 IS NOT NULL )
5678 THEN
5679 Process_Attribute
5680 ( x_period_end_date_found => l_period_end_date_found
5681 , x_GL_period_num_found => l_cal_period_num_found
5682 , p_attribute_value => P_ATTRIBUTE39
5683 , p_attribute_index => 39
5684 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5685 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5686 ) ;
5687 END IF ;
5688
5689 -- Process P_ATTRIBUTE40
5690 IF ( P_ATTRIBUTE40 IS NOT NULL )
5691 THEN
5692 Process_Attribute
5693 ( x_period_end_date_found => l_period_end_date_found
5694 , x_GL_period_num_found => l_cal_period_num_found
5695 , p_attribute_value => P_ATTRIBUTE40
5696 , p_attribute_index => 40
5697 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5698 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5699 ) ;
5700 END IF ;
5701
5702 -- Process P_ATTRIBUTE41
5703 IF ( P_ATTRIBUTE41 IS NOT NULL )
5704 THEN
5705 Process_Attribute
5706 ( x_period_end_date_found => l_period_end_date_found
5707 , x_GL_period_num_found => l_cal_period_num_found
5708 , p_attribute_value => P_ATTRIBUTE41
5709 , p_attribute_index => 41
5710 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5711 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5712 ) ;
5713 END IF ;
5714
5715 -- Process P_ATTRIBUTE42
5716 IF ( P_ATTRIBUTE42 IS NOT NULL )
5717 THEN
5718 Process_Attribute
5719 ( x_period_end_date_found => l_period_end_date_found
5720 , x_GL_period_num_found => l_cal_period_num_found
5721 , p_attribute_value => P_ATTRIBUTE42
5722 , p_attribute_index => 42
5723 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5724 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5725 ) ;
5726 END IF ;
5727
5728 -- Process P_ATTRIBUTE43
5729 IF ( P_ATTRIBUTE43 IS NOT NULL )
5730 THEN
5731 Process_Attribute
5732 ( x_period_end_date_found => l_period_end_date_found
5733 , x_GL_period_num_found => l_cal_period_num_found
5734 , p_attribute_value => P_ATTRIBUTE43
5735 , p_attribute_index => 43
5736 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5737 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5738 ) ;
5739 END IF ;
5740
5741 -- Process P_ATTRIBUTE44
5742 IF ( P_ATTRIBUTE44 IS NOT NULL )
5743 THEN
5744 Process_Attribute
5745 ( x_period_end_date_found => l_period_end_date_found
5746 , x_GL_period_num_found => l_cal_period_num_found
5747 , p_attribute_value => P_ATTRIBUTE44
5748 , p_attribute_index => 44
5749 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5750 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5751 ) ;
5752 END IF ;
5753
5754 -- Process P_ATTRIBUTE45
5755 IF ( P_ATTRIBUTE45 IS NOT NULL )
5756 THEN
5757 Process_Attribute
5758 ( x_period_end_date_found => l_period_end_date_found
5759 , x_GL_period_num_found => l_cal_period_num_found
5760 , p_attribute_value => P_ATTRIBUTE45
5761 , p_attribute_index => 45
5762 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5763 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5764 ) ;
5765 END IF ;
5766
5767 -- Process P_ATTRIBUTE46
5768 IF ( P_ATTRIBUTE46 IS NOT NULL )
5769 THEN
5770 Process_Attribute
5771 ( x_period_end_date_found => l_period_end_date_found
5772 , x_GL_period_num_found => l_cal_period_num_found
5773 , p_attribute_value => P_ATTRIBUTE46
5774 , p_attribute_index => 46
5775 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5776 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5777 ) ;
5778 END IF ;
5779
5780 -- Process P_ATTRIBUTE47
5781 IF ( P_ATTRIBUTE47 IS NOT NULL )
5782 THEN
5783 Process_Attribute
5784 ( x_period_end_date_found => l_period_end_date_found
5785 , x_GL_period_num_found => l_cal_period_num_found
5786 , p_attribute_value => P_ATTRIBUTE47
5787 , p_attribute_index => 47
5788 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5789 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5790 ) ;
5791 END IF ;
5792
5793 -- Process P_ATTRIBUTE48
5794 IF ( P_ATTRIBUTE48 IS NOT NULL )
5795 THEN
5796 Process_Attribute
5797 ( x_period_end_date_found => l_period_end_date_found
5798 , x_GL_period_num_found => l_cal_period_num_found
5799 , p_attribute_value => P_ATTRIBUTE48
5800 , p_attribute_index => 48
5801 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5802 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5803 ) ;
5804 END IF ;
5805
5806 -- Process P_ATTRIBUTE49
5807 IF ( P_ATTRIBUTE49 IS NOT NULL )
5808 THEN
5809 Process_Attribute
5810 ( x_period_end_date_found => l_period_end_date_found
5811 , x_GL_period_num_found => l_cal_period_num_found
5812 , p_attribute_value => P_ATTRIBUTE49
5813 , p_attribute_index => 49
5814 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5815 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5816 ) ;
5817 END IF ;
5818
5819 -- Process P_ATTRIBUTE50
5820 IF ( P_ATTRIBUTE50 IS NOT NULL )
5821 THEN
5822 Process_Attribute
5823 ( x_period_end_date_found => l_period_end_date_found
5824 , x_GL_period_num_found => l_cal_period_num_found
5825 , p_attribute_value => P_ATTRIBUTE50
5826 , p_attribute_index => 50
5827 , p_cal_pr_end_date_col_name => l_cal_pr_end_date_col_name
5828 , p_gl_pr_num_col_name => l_cal_pr_num_col_name
5829 ) ;
5830 END IF ;
5831 --
5832 -- Populate Attribute Process Ends.
5833 --
5834 -- Now frame the insert statement for member attribute
5835 --interface table for CAL_PERIOD.
5836 --
5837 IF ( g_global_val_tbl(1).dimension_type_code = 'TIME' )
5838 THEN
5839 --
5840 l_not_null_attr_count := g_not_null_attr_name_tbl.COUNT ;
5841 l_cal_period_end_date := to_date(g_cal_pr_end_date_col_value,l_adi_format_mask);
5842 --
5843 FOR l_att_indx IN 1..l_not_null_attr_count
5844 LOOP
5845 --
5846 IF ( g_not_null_attr_name_tbl(l_att_indx) = 'CAL_PERIOD_END_DATE' )
5847 THEN
5848 --
5849 l_curr_attr_label := 'CAL_PERIOD_END_DATE' ;
5850 l_curr_attr_value := g_cal_pr_end_date_col_value ;
5851 --
5852 ELSIF ( g_not_null_attr_name_tbl(l_att_indx) = 'GL_PERIOD_NUM' )
5853 THEN
5854 --
5855 l_curr_attr_label := 'GL_PERIOD_NUM' ;
5856 l_curr_attr_value := g_cal_pr_num_col_name_value ;
5857 ELSE
5858 --
5859 l_curr_attr_label := g_not_null_attr_name_tbl(l_att_indx) ;
5860 l_curr_attr_value := g_not_null_attr_val_tbl(l_att_indx) ;
5861 --
5862 END IF ;
5863 --
5864 -- Check whether the attribute is required or not.
5865 -- Check it's association with level only when its optional.
5866 FOR l_attr_rec IN l_retrieve_attr_details_csr
5867 ( l_curr_attr_label
5868 , g_global_val_tbl(1).dimension_id
5869 )
5870 LOOP
5871 --
5872 l_attribute_id := l_attr_rec.attribute_id ;
5873 l_attribute_required_flag := l_attr_rec.attribute_required_flag ;
5874 --
5875 END LOOP ;
5876 --
5877 -- Optional Attribute, go ahead.
5878 IF ( l_attribute_required_flag = 'N' )
5879 THEN
5880 --
5881 -- Though not required to check.
5882 IF ( g_global_val_tbl(1).dim_grp_disp_code IS NOT NULL )
5883 THEN
5884 --
5885 l_populate_attribute_table := 'N' ;
5886 --
5887 FOR l_rec IN l_chk_level_attr_existnce_csr
5888 ( l_attribute_id
5889 , g_global_val_tbl(1).dim_grp_disp_code
5890 )
5891 LOOP
5892 --
5893 l_populate_attribute_table := 'Y' ;
5894 --
5895 END LOOP ;
5896 --
5897 END IF ;
5898 --
5899 END IF ;
5900 --
5901 IF ( l_populate_attribute_table = 'Y' )
5902 THEN
5903 --
5904 l_update_str := 'UPDATE ' ||
5905 g_global_val_tbl(1).intf_attribute_table_name ||
5906 ' SET ' ||
5907 ' status = :b_status ' ||
5908 ' , attribute_assign_value ' ||
5909 ' = :b_attrib_asgn_value ' ||
5910 'WHERE ' ||
5911 'cal_period_end_date ' ||
5912 ' = :b_cal_period_end_date AND ' ||
5913 'cal_period_number = :b_cal_period_number AND ' ||
5914 'calendar_display_code = ' ||
5915 ' :b_calendar_display_code '||
5916 'AND dimension_group_display_code = ' ||
5917 ' :b_dimension_group_display_code AND ' ||
5918 'attribute_varchar_label = ' ||
5919 ' :b_attribute_varchar_label ' ||
5920 'AND version_display_code = ' ||
5921 ' :b_version_display_code AND ' ||
5922 'NVL(attr_assign_vs_display_code, ''XYZ'') = ' ||
5923 ' NVL(:b_attr_assign_vs_display_code, ''XYZ'') ' ;
5924 --
5925 EXECUTE IMMEDIATE
5926 l_update_str
5927 USING
5928 'LOAD'
5929 , l_curr_attr_value
5930 , l_cal_period_end_date
5931 , g_cal_pr_num_col_name_value
5932 , g_global_val_tbl(1).calendar_display_code
5933 , g_global_val_tbl(1).dim_grp_disp_code
5934 , l_curr_attr_label
5935 , g_version_display_code(l_att_indx)
5936 , g_attribute_vs_display_code(l_att_indx) ;
5937 --
5938 -- No record exists. Insert record.
5939 IF ( SQL%ROWCOUNT = 0 )
5940 THEN
5941 --
5942 l_attr_t_str := 'INSERT INTO ' ||
5943 g_global_val_tbl(1).intf_attribute_table_name ||
5944 '( cal_period_end_date ' ||
5945 ', cal_period_number' ||
5946 ', attribute_varchar_label' ||
5947 ', attribute_assign_value' ||
5948 ', attr_assign_vs_display_code' ||
5949 ', status' ||
5950 ', calendar_display_code' ||
5951 ', dimension_group_display_code' ||
5952 ', version_display_code' ||
5953 ')' ||
5954 'VALUES' ||
5955 '( :b_cal_period_end_date' ||
5956 ', :b_cal_period_number' ||
5957 ', :b_attribute_varchar_label' ||
5958 ', :b_attribute_assign_value' ||
5959 ', :b_attr_assign_vs_display_code' ||
5960 ', :b_status' ||
5961 ', :b_calendar_display_code' ||
5962 ', :b_dimension_group_display_code' ||
5963 ', :b_version_display_code' ||
5964 ')' ;
5965 --
5966 EXECUTE IMMEDIATE
5967 l_attr_t_str
5968 USING
5969 l_cal_period_end_date
5970 , g_cal_pr_num_col_name_value
5971 , l_curr_attr_label
5972 , l_curr_attr_value
5973 , g_attribute_vs_display_code(l_att_indx)
5974 , 'LOAD'
5975 , g_global_val_tbl(1).calendar_display_code
5976 , g_global_val_tbl(1).dim_grp_disp_code
5977 , g_version_display_code(l_att_indx) ;
5978 --
5979 END IF ;
5980 --
5981 END IF ;
5982 --
5983 END LOOP ;
5984 --
5985 END IF ;
5986 --
5987 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
5988 THEN
5989 --
5990 l_curr_activity := 'Upload_Member_Interface API comnpleted successfully.' ;
5991 --
5992 -- Put the current activity into log.
5993 FND_LOG.String
5994 ( log_level => FND_LOG.LEVEL_STATEMENT
5995 , module => l_api_name
5996 , message => 'Activity: ' || l_curr_activity
5997 ) ;
5998 --
5999 END IF ;
6000 --
6001 /*EXCEPTION
6002 --
6003 WHEN OTHERS THEN
6004 ROLLBACK TO Upload_Member_Interface ;
6005 --
6006 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
6007 THEN
6008 -- Put the current activity into log.
6009 FND_LOG.String
6010 ( log_level => FND_LOG.LEVEL_STATEMENT
6011 , module => l_api_name
6012 , message => 'SQL Error ' || sqlerrm
6013 ) ;
6014 --
6015 END IF ;
6016 --
6017 -- *********************
6018 -- ***** IMPORTANT *****
6019 -- *********************
6020
6021 -- For the time being, using Raise_Exception
6022 -- to raise the exception to Excel. Need to
6023 -- decide the text of error message.
6024 APP_EXCEPTION.Raise_Exception() ;
6025 --*/
6026 END Upload_Member_Interface ;
6027
6028
6029 PROCEDURE Upload_Member_Header_Interface
6030 (
6031 p_dimension_varchar_label IN VARCHAR2
6032 )
6033 IS
6034 BEGIN
6035
6036 NULL;
6037
6038 END Upload_Member_Header_Interface;
6039
6040 /*===========================================================================+
6041 Procedure Name : Populate_Mem_ADI_Metadata_CP
6042 Parameters :
6043 IN : p_dimension_varchar_label VARCHAR2
6044 OUT : errbuf VARCHAR2
6045 retcode VARCHAR2
6046
6047 Description : This program calls Populate_Mem_WebADI_Metadata to
6048 populate dimension Metadata
6049
6050 Modification History :
6051 Date Name Desc
6052 ---------- --------- -------------------------------------------------------
6053 12/01/2005 SHTRIPAT Created.
6054 ---------- --------- -------------------------------------------------------
6055 +===========================================================================*/
6056 PROCEDURE Populate_Mem_ADI_Metadata_CP
6057 ( errbuf OUT NOCOPY VARCHAR2
6058 , retcode OUT NOCOPY VARCHAR2
6059 , p_dimension_varchar_label IN VARCHAR2
6060 )
6061 IS
6062 --
6063 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Mem_ADI_Metadata_CP' ;
6064 l_api_version CONSTANT NUMBER := 1.0 ;
6065 --
6066 l_return_status VARCHAR2(1) ;
6067 l_msg_count NUMBER ;
6068 l_msg_data VARCHAR2(4000) ;
6069 --
6070 x_exception_msg VARCHAR2(4000) ;
6071 BEGIN
6072 --
6073 Populate_Mem_WebADI_Metadata
6074 ( x_return_status => l_return_status
6075 , x_msg_count => l_msg_count
6076 , x_msg_data => l_msg_data
6077 , p_api_version => l_api_version
6078 , p_init_msg_list => FND_API.G_FALSE
6079 , p_commit => FND_API.G_TRUE
6080 , p_dimension_varchar_label => p_dimension_varchar_label
6081 ) ;
6082 --
6083 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
6084 THEN
6085 RAISE FND_API.G_EXC_ERROR ;
6086 END IF ;
6087 --
6088 retcode := 0;
6089 --
6090 EXCEPTION
6091 --
6092 WHEN FND_API.G_EXC_ERROR THEN
6093 --
6094 retcode := 2 ;
6095 errbuf := l_msg_data ;
6096 --
6097 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6098 --
6099 retcode := 2 ;
6100 errbuf := l_msg_data ;
6101 --
6102 WHEN OTHERS THEN
6103 --
6104 retcode := 2 ;
6105 errbuf := l_msg_data ;
6106 --
6107 END Populate_Mem_ADI_Metadata_CP ;
6108 --
6109
6110 --Bug#5186753: Proc to delete all the dynamically
6111 --created seed data.
6112
6113 PROCEDURE Delete_Fem_Webadi_Seed (
6114 p_api_version IN NUMBER ,
6115 p_init_msg_list IN VARCHAR2,
6116 p_commit IN VARCHAR2,
6117 x_return_status OUT NOCOPY VARCHAR2,
6118 x_msg_count OUT NOCOPY NUMBER ,
6119 x_msg_data OUT NOCOPY VARCHAR2
6120 ) IS
6121
6122 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Fem_Webadi_Seed';
6123 l_api_version CONSTANT NUMBER := 1.0;
6124
6125 CURSOR dimintg_lyts_csr IS
6126 SELECT layout_code FROM bne_layouts_b WHERE integrator_code = 'FEM_DIM_MEMBER_INTG';
6127
6128 CURSOR dimintg_intfs_csr IS
6129 SELECT interface_code FROM bne_interfaces_b WHERE integrator_code = 'FEM_DIM_MEMBER_INTG'
6130 AND interface_code <> 'FEM_DIM_MEMBER_HEADER_INTF';
6131
6132 BEGIN
6133
6134 SAVEPOINT Delete_Fem_Webadi_Seed_Pvt;
6135
6136 IF NOT FND_API.Compatible_API_Call ( l_api_version,
6137 p_api_version,
6138 l_api_name,
6139 G_PKG_NAME )
6140 THEN
6141 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
6142 END IF;
6143 --
6144
6145 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
6146 FND_MSG_PUB.initialize ;
6147 END IF;
6148 --
6149 x_return_status := FND_API.G_RET_STS_SUCCESS ;
6150 --
6151
6152 FEM_ENGINES_PKG.User_Message (
6153 p_app_name => 'FEM'
6154 ,p_msg_text => 'Deleting layout blocks and layout cols...');
6155
6156 FOR dimintg_lyts_csr_rec IN dimintg_lyts_csr
6157 LOOP
6158 DELETE FROM bne_layout_cols WHERE layout_code = dimintg_lyts_csr_rec.layout_code
6159 AND interface_code <> 'FEM_DIM_MEMBER_HEADER_INTF';
6160 DELETE FROM bne_layout_blocks_b WHERE layout_code = dimintg_lyts_csr_rec.layout_code;
6161 DELETE FROM bne_layout_blocks_tl WHERE layout_code = dimintg_lyts_csr_rec.layout_code;
6162 END LOOP;
6163
6164 FEM_ENGINES_PKG.User_Message (
6165 p_app_name => 'FEM'
6166 ,p_msg_text => 'Deleted...');
6167
6168 FEM_ENGINES_PKG.User_Message (
6169 p_app_name => 'FEM'
6170 ,p_msg_text => 'Deleting layouts...');
6171
6172
6173 FOR dimintg_lyts_csr_rec IN dimintg_lyts_csr
6174 LOOP
6175 DELETE FROM bne_layouts_tl WHERE layout_code = dimintg_lyts_csr_rec.layout_code;
6176 END LOOP;
6177
6178 DELETE FROM bne_layouts_b WHERE integrator_code = 'FEM_DIM_MEMBER_INTG';
6179
6180 FEM_ENGINES_PKG.User_Message (
6181 p_app_name => 'FEM'
6182 ,p_msg_text => 'Deleted...');
6183
6184 FEM_ENGINES_PKG.User_Message (
6185 p_app_name => 'FEM'
6186 ,p_msg_text => 'Deleting interface cols');
6187
6188 FOR dimintg_intfs_csr_rec IN dimintg_intfs_csr
6189 LOOP
6190 DELETE FROM bne_interface_cols_b WHERE interface_code = dimintg_intfs_csr_rec.interface_code;
6191 DELETE FROM bne_interface_cols_tl WHERE interface_code = dimintg_intfs_csr_rec.interface_code;
6192 END LOOP;
6193
6194 FEM_ENGINES_PKG.User_Message (
6195 p_app_name => 'FEM'
6196 ,p_msg_text => 'Deleted...');
6197
6198 FEM_ENGINES_PKG.User_Message (
6199 p_app_name => 'FEM'
6200 ,p_msg_text => 'Deleting interfaces...');
6201
6202 FOR dimintg_intfs_csr_rec IN dimintg_intfs_csr
6203 LOOP
6204 DELETE FROM bne_interfaces_tl WHERE interface_code = dimintg_intfs_csr_rec.interface_code;
6205 END LOOP;
6206
6207 DELETE FROM bne_interfaces_b WHERE integrator_code = 'FEM_DIM_MEMBER_INTG'
6208 AND interface_code <> 'FEM_DIM_MEMBER_HEADER_INTF';
6209
6210 FEM_ENGINES_PKG.User_Message (
6211 p_app_name => 'FEM'
6212 ,p_msg_text => 'Deleted...');
6213
6214 FEM_ENGINES_PKG.User_Message (
6215 p_app_name => 'FEM'
6216 ,p_msg_text => 'Deleting attr maps');
6217
6218 DELETE FROM fem_webadi_dim_attr_maps;
6219
6220 FEM_ENGINES_PKG.User_Message (
6221 p_app_name => 'FEM'
6222 ,p_msg_text => 'Deleted...');
6223
6224 IF ( FND_API.To_Boolean( p_char => p_commit) ) THEN
6225 COMMIT;
6226 END IF;
6227
6228 EXCEPTION
6229
6230
6231 WHEN FND_API.G_EXC_ERROR THEN
6232 --
6233 ROLLBACK TO Delete_Fem_Webadi_Seed_pvt ;
6234 x_return_status := FND_API.G_RET_STS_ERROR;
6235 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
6236 p_data => x_msg_data );
6237 --
6238 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6239 --
6240 ROLLBACK TO Delete_Fem_Webadi_Seed_Pvt ;
6241 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6242 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
6243 p_data => x_msg_data );
6244
6245 WHEN OTHERS THEN
6246 --
6247 ROLLBACK TO Delete_Fem_Webadi_Seed_Pvt;
6248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6249 --
6250 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
6251 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
6252 l_api_name);
6253 END IF;
6254 --
6255 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
6256 p_data => x_msg_data );
6257 --
6258 END Delete_Fem_Webadi_Seed;
6259
6260
6261
6262
6263 END FEM_WEBADI_MEMBER_UTILS_PVT;