[Home] [Help]
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')
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
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
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;
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'
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;
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;
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:
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:
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'
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;
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:
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:
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: