[Home] [Help]
PACKAGE BODY: APPS.FEM_WEBADI_FEM_BAL_UTILS_PVT
Source
1 PACKAGE BODY FEM_WEBADI_FEM_BAL_UTILS_PVT AS
2 /* $Header: FEMVADIBALUTILB.pls 120.1 2008/02/20 06:46:56 jcliving noship $ */
3
4 X_EXEC_MODE VARCHAR2(10) := NULL;
5
6 PROCEDURE UPLOAD_FEM_BALANCES_INTERFACE(
7 P_DATA_TABLE_NAME VARCHAR2,
8 P_LOAD_METHOD_CODE VARCHAR2,
9 P_BAL_POST_TYPE_CODE VARCHAR2,
10 P_DATASET_CODE NUMBER,
11 P_CAL_PERIOD_ID VARCHAR2,
12 P_CCTR_ORG_DISPLAY_CODE VARCHAR2 DEFAULT NULL,
13 P_CURRENCY_CODE VARCHAR2,
14 P_CURRENCY_TYPE_CODE VARCHAR2,
15 P_SOURCE_SYSTEM_DISPLAY_CODE VARCHAR2,
16 P_LEDGER_DISPLAY_CODE VARCHAR2,
17 P_BUDGET_DISPLAY_CODE VARCHAR2 DEFAULT NULL,
18 P_ENCUMBRANCE_TYPE_CODE VARCHAR2 DEFAULT NULL,
19 P_FINANCIAL_ELEM_DISPLAY_CODE VARCHAR2 DEFAULT NULL,
20 P_PRODUCT_DISPLAY_CODE VARCHAR2 DEFAULT NULL,
21 P_NATURAL_ACCOUNT_DISPLAY_CODE VARCHAR2 DEFAULT NULL,
22 P_CHANNEL_DISPLAY_CODE VARCHAR2 DEFAULT NULL,
23 P_LINE_ITEM_DISPLAY_CODE VARCHAR2 DEFAULT NULL,
24 P_PROJECT_DISPLAY_CODE VARCHAR2 DEFAULT NULL,
25 P_CUSTOMER_DISPLAY_CODE VARCHAR2 DEFAULT NULL,
26 P_ENTITY_DISPLAY_CODE VARCHAR2 DEFAULT NULL,
27 P_INTERCOMPANY_DISPLAY_CODE VARCHAR2 DEFAULT NULL,
28 P_TASK_DISPLAY_CODE VARCHAR2 DEFAULT NULL,
29 P_USER_DIM1_DISPLAY_CODE IN VARCHAR2 DEFAULT NULL,
30
31 P_USER_DIM2_DISPLAY_CODE IN VARCHAR2 DEFAULT NULL,
32 P_USER_DIM3_DISPLAY_CODE IN VARCHAR2 DEFAULT NULL,
33 P_USER_DIM4_DISPLAY_CODE IN VARCHAR2 DEFAULT NULL,
34 P_USER_DIM5_DISPLAY_CODE IN VARCHAR2 DEFAULT NULL,
35 P_USER_DIM6_DISPLAY_CODE IN VARCHAR2 DEFAULT NULL,
36 P_USER_DIM7_DISPLAY_CODE IN VARCHAR2 DEFAULT NULL,
37 P_USER_DIM8_DISPLAY_CODE IN VARCHAR2 DEFAULT NULL,
38 P_USER_DIM9_DISPLAY_CODE IN VARCHAR2 DEFAULT NULL,
39 P_USER_DIM10_DISPLAY_CODE IN VARCHAR2 DEFAULT NULL,
40 P_XTD_BALANCE_E IN NUMBER DEFAULT NULL,
41 P_XTD_BALANCE_F IN NUMBER DEFAULT NULL,
42 P_YTD_BALANCE_E IN NUMBER DEFAULT NULL,
43 P_YTD_BALANCE_F IN NUMBER DEFAULT NULL,
44 P_QTD_BALANCE_E IN NUMBER DEFAULT NULL,
45 P_QTD_BALANCE_F IN NUMBER DEFAULT NULL,
46 P_PTD_DEBIT_BALANCE_E IN NUMBER DEFAULT NULL,
47 P_PTD_CREDIT_BALANCE_E IN NUMBER DEFAULT NULL,
48 P_YTD_DEBIT_BALANCE_E IN NUMBER DEFAULT NULL,
49 P_YTD_CREDIT_BALANCE_E IN NUMBER DEFAULT NULL,
50 P_ATTR1 IN VARCHAR2 DEFAULT NULL,
51 P_ATTR2 IN VARCHAR2 DEFAULT NULL) is
52
53 l_posting_req_id number := null;
54 l_posting_error_code varchar2(30) := null;
55 l_previous_error_flag varchar2(1) := null;
56 l_load_set_id number := 1;
57 l_cal_period_number number ;
58 l_cal_period_end_date date;
59 l_cal_per_dim_grp_display_code varchar2(500);
60 l_ds_balance_type_code varchar2(20);
61 l_col_display varchar2(100);
62 l_ledger_id number;
63 l_dataset_code number;
64 x_return_status varchar2(20);
65 x_msg_count number;
66 x_msg_data varchar2(100);
67
68 begin
69
70 select date_assign_value into l_cal_period_end_date from fem_cal_periods_attr
71 where cal_period_id = P_CAL_PERIOD_ID
72 and attribute_id = (
73 select a.attribute_id from fem_dim_attributes_b a,fem_dim_attr_versions_b v
74 where a.attribute_id = v.attribute_id
75 and a.attribute_varchar_label = 'CAL_PERIOD_END_DATE'
76 and v.default_version_flag = 'Y' );
77
78 select number_assign_value into l_cal_period_number from FEM_CAL_PERIODS_ATTR
79 where cal_period_id = P_CAL_PERIOD_ID
80 and attribute_id = (
81 select a.attribute_id from fem_dim_attributes_b a,fem_dim_attr_versions_b v
82 where a.attribute_id = v.attribute_id
83 and a.attribute_varchar_label = 'GL_PERIOD_NUM'
84 and v.default_version_flag = 'Y' );
85
86 select dimension_group_display_code into l_cal_per_dim_grp_display_code from FEM_DIMENSION_GRPS_B
87 where dimension_group_id = (select dimension_group_id from fem_cal_periods_b where
88 cal_period_id = P_CAL_PERIOD_ID);
89
90 select dim_attribute_varchar_member into l_ds_balance_type_code from FEM_DATASETS_ATTR
91 where dataset_code = P_DATASET_CODE
92 and attribute_id = (
93 select a.attribute_id from fem_dim_attributes_b a, fem_dim_attr_versions_b v
94 where a.attribute_id = v.attribute_id
95 and attribute_varchar_label = 'DATASET_BALANCE_TYPE_CODE'
96 and v.default_version_flag = 'Y');
97
98 -- CHECKING PROPER COMBO FOR DS BALANCE TYPE CODE, BUDGET CODE AND INCUMBRANCE TYPE-------------
99
100
101 if((l_ds_balance_type_code = 'ACTUAL') AND (P_BUDGET_DISPLAY_CODE is not null OR P_ENCUMBRANCE_TYPE_CODE is not null)) then
102 FND_MESSAGE.SET_NAME('FEM','FEM_ADI_DS_ERROR');
103 APP_EXCEPTION.Raise_Exception ;
104 end if;
105 if ((l_ds_balance_type_code = 'BUDGET') AND (P_BUDGET_DISPLAY_CODE is null OR P_ENCUMBRANCE_TYPE_CODE is not null)) then
106 FND_MESSAGE.SET_NAME('FEM','FEM_ADI_DS_ERROR');
107 APP_EXCEPTION.Raise_Exception ;
108 end if;
109 if ((l_ds_balance_type_code = 'ENCUMBRANCE') AND (P_BUDGET_DISPLAY_CODE is not null OR P_ENCUMBRANCE_TYPE_CODE is null)) then
110 FND_MESSAGE.SET_NAME('FEM','FEM_ADI_DS_ERROR');
111 APP_EXCEPTION.Raise_Exception ;
112 end if;
113
114 ----------------- Finding load method code --------------------------
115
116 select ledger_id into l_ledger_id from fem_ledgers_b
117 where ledger_display_code = p_ledger_display_code;
118
119 if(x_exec_mode is null) then
120
121 FEM_LOADER_ENG_UTIL_PKG.Get_XGL_Loader_Exec_Mode(1.0,
122 fnd_api.g_false,
123 fnd_api.g_false,
124 fnd_api.g_true,
125 x_return_status,
126 x_msg_count,
127 x_msg_data,
128 p_cal_period_id,
129 l_ledger_id,
130 p_dataset_code,
131 x_exec_mode);
132 end if;
133 --------------------------------------------------------------------
134
135 insert into fem_bal_interface_t
136 (LOAD_SET_ID,
137 LOAD_METHOD_CODE,
138 BAL_POST_TYPE_CODE,
139 DS_BALANCE_TYPE_CODE,
140 CAL_PER_DIM_GRP_DISPLAY_CODE,
141 CAL_PERIOD_NUMBER,
142 CAL_PERIOD_END_DATE,
143 SOURCE_SYSTEM_DISPLAY_CODE,
144 LEDGER_DISPLAY_CODE,
145 CURRENCY_CODE,
146 CURRENCY_TYPE_CODE,
147 BUDGET_DISPLAY_CODE,
148 ENCUMBRANCE_TYPE_CODE,
149 CCTR_ORG_DISPLAY_CODE,
150 FINANCIAL_ELEM_DISPLAY_CODE,
151 PRODUCT_DISPLAY_CODE,
152 NATURAL_ACCOUNT_DISPLAY_CODE,
153 CHANNEL_DISPLAY_CODE,
154 LINE_ITEM_DISPLAY_CODE,
155 PROJECT_DISPLAY_CODE,
156 CUSTOMER_DISPLAY_CODE,
157 ENTITY_DISPLAY_CODE,
158 INTERCOMPANY_DISPLAY_CODE,
159 TASK_DISPLAY_CODE,
160 USER_DIM1_DISPLAY_CODE,
161 USER_DIM2_DISPLAY_CODE,
162 USER_DIM3_DISPLAY_CODE,
163 USER_DIM4_DISPLAY_CODE,
164 USER_DIM5_DISPLAY_CODE,
165 USER_DIM6_DISPLAY_CODE,
166 USER_DIM7_DISPLAY_CODE,
167 USER_DIM8_DISPLAY_CODE,
168 USER_DIM9_DISPLAY_CODE,
169 USER_DIM10_DISPLAY_CODE,
170 XTD_BALANCE_E,
171 XTD_BALANCE_F,
172 YTD_BALANCE_E,
173 YTD_BALANCE_F,
174 QTD_BALANCE_E,
175 QTD_BALANCE_F,
176 PTD_DEBIT_BALANCE_E,
177 PTD_CREDIT_BALANCE_E,
178 YTD_DEBIT_BALANCE_E,
179 YTD_CREDIT_BALANCE_E,
180 POSTING_REQUEST_ID,
181 POSTING_ERROR_CODE,
182 PREVIOUS_ERROR_FLAG)
183 VALUES(
184 l_load_set_id,
185 x_exec_mode,
186 P_BAL_POST_TYPE_CODE,
187 l_ds_balance_type_code,
188 l_cal_per_dim_grp_display_code,
189 l_cal_period_number,
190 l_cal_period_end_date,
191 P_SOURCE_SYSTEM_DISPLAY_CODE,
192 P_LEDGER_DISPLAY_CODE,
193 P_CURRENCY_CODE,
194 P_CURRENCY_TYPE_CODE,
195 P_BUDGET_DISPLAY_CODE,
196 P_ENCUMBRANCE_TYPE_CODE,
197 P_CCTR_ORG_DISPLAY_CODE,
198 P_FINANCIAL_ELEM_DISPLAY_CODE,
199 P_PRODUCT_DISPLAY_CODE,
200 P_NATURAL_ACCOUNT_DISPLAY_CODE,
201 P_CHANNEL_DISPLAY_CODE,
202 P_LINE_ITEM_DISPLAY_CODE,
203 P_PROJECT_DISPLAY_CODE,
204 P_CUSTOMER_DISPLAY_CODE,
205 P_ENTITY_DISPLAY_CODE,
206 P_INTERCOMPANY_DISPLAY_CODE,
207 P_TASK_DISPLAY_CODE,
208 P_USER_DIM1_DISPLAY_CODE,
209 P_USER_DIM2_DISPLAY_CODE,
210 P_USER_DIM3_DISPLAY_CODE,
211 P_USER_DIM4_DISPLAY_CODE,
212 P_USER_DIM5_DISPLAY_CODE,
213 P_USER_DIM6_DISPLAY_CODE,
214 P_USER_DIM7_DISPLAY_CODE,
215 P_USER_DIM8_DISPLAY_CODE,
216 P_USER_DIM9_DISPLAY_CODE,
217 P_USER_DIM10_DISPLAY_CODE,
218 P_XTD_BALANCE_E,
219 P_XTD_BALANCE_F,
220 P_YTD_BALANCE_E,
221 P_YTD_BALANCE_F,
222 P_QTD_BALANCE_E,
223 P_QTD_BALANCE_F,
224 P_PTD_DEBIT_BALANCE_E,
225 P_PTD_CREDIT_BALANCE_E,
226 P_YTD_DEBIT_BALANCE_E,
227 P_YTD_CREDIT_BALANCE_E,
228 l_posting_req_id,
229 l_posting_error_code,
230 l_previous_error_flag);
231
232 EXCEPTION
233 --
234 WHEN DUP_VAL_ON_INDEX THEN
235 FND_MESSAGE.SET_NAME('FEM','FEM_ADI_DUPLICATE_ROWS');
236 APP_EXCEPTION.Raise_Exception ;
237
238 END UPLOAD_FEM_BALANCES_INTERFACE;
239
240 FUNCTION GET_ENC_TYPE_ID(P_ENC_TYPE_CODE VARCHAR2) RETURN NUMBER
241 IS
242 l_enc_type_id number;
243
244 BEGIN
245 if(P_ENC_TYPE_CODE is null) then
246 l_enc_type_id := null;
247 else
248 select encumbrance_type_id into l_enc_type_id from FEM_ENCUMBRANCE_TYPES_B where encumbrance_type_code = P_ENC_TYPE_CODE;
249 end if;
250 return l_enc_type_id;
251
252 END GET_ENC_TYPE_ID;
253
254 FUNCTION GET_BUDGET_ID(P_BUDGET_DISPLAY_CODE VARCHAR2) RETURN NUMBER
255 IS
256 l_budget_id number;
257
258 BEGIN
259 if(P_BUDGET_DISPLAY_CODE is null) then
260 l_budget_id := null;
261 else
262 select budget_id into l_budget_id from FEM_BUDGETS_B where budget_display_code = P_BUDGET_DISPLAY_CODE;
263 end if;
264 return l_budget_id;
265
266 END GET_BUDGET_ID;
267
268 PROCEDURE SET_ALL_REQD_COLS(X_ERROR_FLAG OUT NOCOPY VARCHAR2)
269 IS
270 l_fem_type varchar2(20);
271 l_varchar_field varchar2(20);
272 l_number_field varchar2(20);
273 l_date_field varchar2(20);
274 l_lov_field varchar2(20);
275 l_varchar_req_field varchar2(20);
276 l_number_req_field varchar2(20);
277 l_date_req_field varchar2(20);
278 l_lov_req_field varchar2(20);
279 l_display_order number := 1;
280
281 BEGIN
282
283 begin
284
285 select 'Y' into x_error_flag from dual where exists(
286 select fcp.column_name column_name from fem_tab_column_prop fcp
287 where fcp.table_name = 'FEM_BALANCES'
288 and fcp.column_property_code = 'PROCESSING_KEY'
289 and fcp.column_name not in
290 ('CREATED_BY_REQUEST_ID',
291 'CREATED_BY_OBJECT_ID',
292 'LAST_UPDATED_BY_REQUEST_ID',
293 'LAST_UPDATED_BY_OBJECT_ID',
294 'CREATION_ROW_SEQUENCE',
295 'DATASET_CODE',
296 'CAL_PERIOD_ID')
297 and (not exists (select 1 from fem_tab_columns_b ftc where table_name = 'FEM_BALANCES' and
298 ftc.column_name = fcp.column_name) or
299 fcp.column_name in(select column_name from fem_tab_columns_b ftc where table_name = 'FEM_BALANCES' and
300 ftc.column_name = fcp.column_name and interface_column_name is null))
301 UNION
302 select column_name from dba_tab_columns M
303 where owner = 'FEM' and table_name = 'FEM_BAL_INTERFACE_T'
304 and nullable = 'N' and column_name in (select substr(interface_col_name,3) from
305 bne_interface_cols_b where interface_code = 'FEM_BALANCES_INTF')
306 and column_name not in ('LOAD_SET_ID','LOAD_METHOD_CODE','BAL_POST_TYPE_CODE',
307 'DS_BALANCE_TYPE_CODE','CAL_PER_DIM_GRP_DISPLAY_CODE','CAL_PERIOD_NUMBER','CAL_PERIOD_END_DATE',
308 'XTD_BALANCE_E','XTD_BALANCE_F','YTD_BALANCE_E','YTD_BALANCE_F','QTD_BALANCE_E','QTD_BALANCE_F',
309 'PTD_DEBIT_BALANCE_E','PTD_CREDIT_BALANCE_E','YTD_DEBIT_BALANCE_E','YTD_CREDIT_BALANCE_E')
310 and not exists (SELECT 1 FROM FEM_TAB_COLUMNS_B T WHERE TABLE_NAME = 'FEM_BALANCES' AND T.INTERFACE_COLUMN_NAME = M.COLUMN_NAME));
311 exception
312 when NO_DATA_FOUND then
313 x_error_flag := 'N';
314 end;
315
316 if(x_error_flag is not null AND x_error_flag = 'Y') then
317 return;
318 else
319 x_error_flag := 'N';
320 end if;
321
322 FND_MESSAGE.SET_NAME ('FEM', 'FEM_ADI_USER_HINT_LOV_REQ');
323 l_lov_req_field := FND_MESSAGE.GET;
324 FND_MESSAGE.SET_NAME ('FEM', 'FEM_ADI_USER_HINT_LOV');
325 l_lov_field := FND_MESSAGE.GET;
326 FND_MESSAGE.SET_NAME ('FEM', 'FEM_ADI_USER_HINT_TEXT_REQ');
327 l_varchar_req_field := FND_MESSAGE.GET;
328 FND_MESSAGE.SET_NAME ('FEM', 'FEM_ADI_USER_HINT_TEXT');
329 l_varchar_field := FND_MESSAGE.GET;
330 FND_MESSAGE.SET_NAME ('FEM', 'FEM_ADI_USER_HINT_NUMBER_REQ');
331 l_number_req_field := FND_MESSAGE.GET;
332 FND_MESSAGE.SET_NAME ('FEM', 'FEM_ADI_USER_HINT_NUMBER');
333 l_number_field := FND_MESSAGE.GET;
334 FND_MESSAGE.SET_NAME ('FEM', 'FEM_ADI_USER_HINT_DATE_REQ');
335 l_date_req_field := FND_MESSAGE.GET;
336 FND_MESSAGE.SET_NAME ('FEM', 'FEM_ADI_USER_HINT_DATE');
337 l_date_field := FND_MESSAGE.GET;
338
339 --------------------------- Initially setting interface columns ----------------------------------
340 for interface_cols in
341 (select table_name,column_name,data_type,nullable from dba_tab_columns
342 where owner = 'FEM'
343 and table_name = 'FEM_BAL_INTERFACE_T'
344 and column_name in (select substr(interface_col_name,3) from
345 bne_interface_cols_b where interface_code = 'FEM_BALANCES_INTF'))
346
347 loop
348
349 begin
350 select fem_data_type_code into l_fem_type from fem_tab_columns_b
351 where table_name = 'FEM_BALANCES'
352 and interface_column_name = interface_cols.column_name;
353 exception
354 when NO_DATA_FOUND then
355 goto end_loop; ---- Simulating what 'CONTINUE' key word does in c/c++
356 end;
357 ------------------------------------ Updating BNE_INTERFACE_COLS_TL table -------------------------
358 if(l_fem_type <> 'DIMENSION') then
359
360 update bne_interface_cols_tl
361 set user_hint = DECODE(interface_cols.DATA_TYPE, 'VARCHAR2',
362 DECODE(interface_cols.nullable, 'N',l_varchar_req_field, l_varchar_field),
363 DECODE(interface_cols.DATA_TYPE, 'NUMBER',
364 DECODE(interface_cols.nullable, 'N',l_number_req_field, l_number_field),
365 DECODE(interface_cols.DATA_TYPE, 'DATE',
366 DECODE(interface_cols.nullable, 'N',l_date_req_field, l_date_field), NULL)))
367 where sequence_num = (select sequence_num from bne_interface_cols_b where interface_col_name = 'P_' || interface_cols.column_name
368 and interface_code = 'FEM_BALANCES_INTF')
369 and interface_code = 'FEM_BALANCES_INTF'
370 and language = userenv('LANG');
371 else
372
373 update bne_interface_cols_tl
374 set user_hint = DECODE(interface_cols.nullable, 'N',l_lov_req_field, l_lov_field)
375 where sequence_num = (select sequence_num from bne_interface_cols_b where interface_col_name = 'P_' || interface_cols.column_name
376 and interface_code = 'FEM_BALANCES_INTF')
377 and interface_code = 'FEM_BALANCES_INTF'
378 and language = userenv('LANG');
379 end if;
380 ---------------------------------------------------------------------------------------------------
381
382 ---------------------------------- Updating BNE_INTERFACE_COLS_B ----------------------------------
383 if(interface_cols.nullable <> 'N') then
384 update bne_interface_cols_b
385 set not_null_flag = 'N',
386 required_flag = 'N'
387 where interface_code = 'FEM_BALANCES_INTF'
388 and interface_col_name = 'P_' || interface_cols.column_name;
389 else
390 update bne_interface_cols_b
391 set not_null_flag = 'Y',
392 required_flag = 'Y'
393 where interface_code = 'FEM_BALANCES_INTF'
394 and interface_col_name = 'P_' || interface_cols.column_name;
395 end if;
396 ---------------------------------------------------------------------------------------------------
397 <<end_loop>>
398 NULL; ----- Just writing an executable code
399 end loop;
400 ------------------------------- Initial interface cols prop setting done --------------------------
401
402
403 ----------------------- Setting property for processing key cols ----------------------------------
404 for key_recs in
405 (select table_name,column_name,data_type,nullable from dba_tab_columns
406 where owner = 'FEM'
407 and table_name = 'FEM_BAL_INTERFACE_T'
408 and column_name in (select ftc.interface_column_name interface_column_name from fem_tab_columns_b ftc,fem_tab_column_prop fcp
409 where ftc.table_name = fcp.table_name
410 and ftc.column_name = fcp.column_name
411 and ftc.table_name = 'FEM_BALANCES'
412 and fcp.column_property_code = 'PROCESSING_KEY'
413 and ftc.column_name not in
414 ('CREATED_BY_REQUEST_ID',
415 'CREATED_BY_OBJECT_ID',
416 'LAST_UPDATED_BY_REQUEST_ID',
417 'LAST_UPDATED_BY_OBJECT_ID',
418 'CREATION_ROW_SEQUENCE')))
419
420 loop
421
422 begin
423 select fem_data_type_code into l_fem_type from fem_tab_columns_b
424 where table_name = 'FEM_BALANCES'
425 and interface_column_name = key_recs.column_name;
426 exception
427 when NO_DATA_FOUND then
428 goto end_last_loop; ---- Simulating what 'CONTINUE' key word does in c/c++
429 end;
430
431 ------------------------------------ Updating BNE_INTERFACE_COLS_TL table -------------------------
432 if(l_fem_type <> 'DIMENSION') then
433
434 update bne_interface_cols_tl
435 set user_hint = DECODE(key_recs.DATA_TYPE, 'VARCHAR2',l_varchar_req_field,'NUMBER',l_number_req_field,
436 'DATE',l_date_req_field, NULL)
437 where sequence_num = (select sequence_num from bne_interface_cols_b where interface_col_name = 'P_' || key_recs.column_name
438 and interface_code = 'FEM_BALANCES_INTF')
439 and interface_code = 'FEM_BALANCES_INTF'
440 and language = userenv('LANG');
441
442 else
443 update bne_interface_cols_tl
444 set user_hint = l_lov_req_field
445 where sequence_num = (select sequence_num from bne_interface_cols_b where interface_col_name = 'P_' || key_recs.column_name
446 and interface_code = 'FEM_BALANCES_INTF')
447 and interface_code = 'FEM_BALANCES_INTF'
448 and language = userenv('LANG');
449
450 end if;
451 ---------------------------------------------------------------------------------------------------
452
453 ---------------------------------- Updating BNE_INTERFACE_COLS_B ----------------------------------
454 update bne_interface_cols_b
455 set not_null_flag = 'Y',
456 required_flag = 'Y'
457 where interface_code = 'FEM_BALANCES_INTF'
458 and interface_col_name = 'P_'|| key_recs.column_name;
459 ---------------------------------------------------------------------------------------------------
460 <<end_last_loop>>
461 NULL; -------- Just writing an executable statement
462 end loop;
463
464 ---------------------------------------------------------------------------------------------------
465 ------------------------ Setting up the display order again ---------------------------------------
466
467 update bne_interface_cols_b
468 set display_order = null
469 where interface_code = 'FEM_BALANCES_INTF' and
470 sequence_num not in (1,2,3,4,5,9,10,11,12);
471
472 for bne_cols in
473 (
474 select interface_col_name from bne_interface_cols_vl
475 where interface_code = 'FEM_BALANCES_INTF'
476 and display_flag = 'Y' and enabled_flag = 'Y'
477 and sequence_num not in (1,2,3,4,5,9,10,11,12)
478 order by not_null_flag desc,upper(prompt_above)
479 )
480
481 loop -- Starting the loop to update entries one by one
482
483 update bne_interface_cols_b
484 set display_order = l_display_order * 10
485 where interface_code = 'FEM_BALANCES_INTF'
486 and interface_col_name = bne_cols.interface_col_name;
487
488 l_display_order := l_display_order + 1;
489
490 end loop;
491
492 update bne_layout_cols b
493 set interface_seq_num = (select sequence_num from bne_interface_cols_b where interface_code = 'FEM_BALANCES_INTF' and display_order = b.sequence_num
494 and sequence_num not in (1,2,3,4,5,9,10,11,12)) where layout_code = 'FEM_BALANCES_LAYOUT'
495 and block_id =2;
496 --------------------------------------------------------------------------------------------------
497
498 COMMIT;
499
500 END SET_ALL_REQD_COLS;
501
502 END FEM_WEBADI_FEM_BAL_UTILS_PVT;