APPS.FEM_WEBADI_FEM_BAL_UTILS_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 70

select date_assign_value into l_cal_period_end_date from fem_cal_periods_attr
where cal_period_id = P_CAL_PERIOD_ID
and attribute_id = (
select a.attribute_id from fem_dim_attributes_b a,fem_dim_attr_versions_b v
where  a.attribute_id = v.attribute_id
and a.attribute_varchar_label = 'CAL_PERIOD_END_DATE'
and v.default_version_flag = 'Y' );

Line: 78

select number_assign_value into l_cal_period_number from FEM_CAL_PERIODS_ATTR
where cal_period_id = P_CAL_PERIOD_ID
and attribute_id = (
select a.attribute_id from fem_dim_attributes_b a,fem_dim_attr_versions_b v
where  a.attribute_id = v.attribute_id
and a.attribute_varchar_label = 'GL_PERIOD_NUM'
and v.default_version_flag = 'Y' );

Line: 86

select dimension_group_display_code into l_cal_per_dim_grp_display_code	from FEM_DIMENSION_GRPS_B
where dimension_group_id = (select dimension_group_id from fem_cal_periods_b where
cal_period_id = P_CAL_PERIOD_ID);

Line: 90

select dim_attribute_varchar_member into l_ds_balance_type_code from FEM_DATASETS_ATTR
where dataset_code = P_DATASET_CODE
and attribute_id = (
select a.attribute_id from fem_dim_attributes_b a, fem_dim_attr_versions_b v
where  a.attribute_id = v.attribute_id
and attribute_varchar_label = 'DATASET_BALANCE_TYPE_CODE'
and v.default_version_flag = 'Y');

Line: 116

select ledger_id into l_ledger_id from fem_ledgers_b
where ledger_display_code = p_ledger_display_code;

Line: 135

insert into fem_bal_interface_t
(LOAD_SET_ID,
LOAD_METHOD_CODE,
BAL_POST_TYPE_CODE,
DS_BALANCE_TYPE_CODE,
CAL_PER_DIM_GRP_DISPLAY_CODE,
CAL_PERIOD_NUMBER,
CAL_PERIOD_END_DATE,
SOURCE_SYSTEM_DISPLAY_CODE,
LEDGER_DISPLAY_CODE,
CURRENCY_CODE,
CURRENCY_TYPE_CODE,
BUDGET_DISPLAY_CODE,
ENCUMBRANCE_TYPE_CODE,
CCTR_ORG_DISPLAY_CODE,
FINANCIAL_ELEM_DISPLAY_CODE,
PRODUCT_DISPLAY_CODE,
NATURAL_ACCOUNT_DISPLAY_CODE,
CHANNEL_DISPLAY_CODE,
LINE_ITEM_DISPLAY_CODE,
PROJECT_DISPLAY_CODE,
CUSTOMER_DISPLAY_CODE,
ENTITY_DISPLAY_CODE,
INTERCOMPANY_DISPLAY_CODE,
TASK_DISPLAY_CODE,
USER_DIM1_DISPLAY_CODE,
USER_DIM2_DISPLAY_CODE,
USER_DIM3_DISPLAY_CODE,
USER_DIM4_DISPLAY_CODE,
USER_DIM5_DISPLAY_CODE,
USER_DIM6_DISPLAY_CODE,
USER_DIM7_DISPLAY_CODE,
USER_DIM8_DISPLAY_CODE,
USER_DIM9_DISPLAY_CODE,
USER_DIM10_DISPLAY_CODE,
XTD_BALANCE_E,
XTD_BALANCE_F,
YTD_BALANCE_E,
YTD_BALANCE_F,
QTD_BALANCE_E,
QTD_BALANCE_F,
PTD_DEBIT_BALANCE_E,
PTD_CREDIT_BALANCE_E,
YTD_DEBIT_BALANCE_E,
YTD_CREDIT_BALANCE_E,
POSTING_REQUEST_ID,
POSTING_ERROR_CODE,
PREVIOUS_ERROR_FLAG)
VALUES(
l_load_set_id,
x_exec_mode,
P_BAL_POST_TYPE_CODE,
l_ds_balance_type_code,
l_cal_per_dim_grp_display_code,
l_cal_period_number,
l_cal_period_end_date,
P_SOURCE_SYSTEM_DISPLAY_CODE,
P_LEDGER_DISPLAY_CODE,
P_CURRENCY_CODE,
P_CURRENCY_TYPE_CODE,
P_BUDGET_DISPLAY_CODE,
P_ENCUMBRANCE_TYPE_CODE,
P_CCTR_ORG_DISPLAY_CODE,
P_FINANCIAL_ELEM_DISPLAY_CODE,
P_PRODUCT_DISPLAY_CODE,
P_NATURAL_ACCOUNT_DISPLAY_CODE,
P_CHANNEL_DISPLAY_CODE,
P_LINE_ITEM_DISPLAY_CODE,
P_PROJECT_DISPLAY_CODE,
P_CUSTOMER_DISPLAY_CODE,
P_ENTITY_DISPLAY_CODE,
P_INTERCOMPANY_DISPLAY_CODE,
P_TASK_DISPLAY_CODE,
P_USER_DIM1_DISPLAY_CODE,
P_USER_DIM2_DISPLAY_CODE,
P_USER_DIM3_DISPLAY_CODE,
P_USER_DIM4_DISPLAY_CODE,
P_USER_DIM5_DISPLAY_CODE,
P_USER_DIM6_DISPLAY_CODE,
P_USER_DIM7_DISPLAY_CODE,
P_USER_DIM8_DISPLAY_CODE,
P_USER_DIM9_DISPLAY_CODE,
P_USER_DIM10_DISPLAY_CODE,
P_XTD_BALANCE_E,
P_XTD_BALANCE_F,
P_YTD_BALANCE_E,
P_YTD_BALANCE_F,
P_QTD_BALANCE_E,
P_QTD_BALANCE_F,
P_PTD_DEBIT_BALANCE_E,
P_PTD_CREDIT_BALANCE_E,
P_YTD_DEBIT_BALANCE_E,
P_YTD_CREDIT_BALANCE_E,
l_posting_req_id,
l_posting_error_code,
l_previous_error_flag);

Line: 248

 select encumbrance_type_id into l_enc_type_id from FEM_ENCUMBRANCE_TYPES_B where encumbrance_type_code = P_ENC_TYPE_CODE;

Line: 262

 select budget_id into l_budget_id from FEM_BUDGETS_B where budget_display_code = P_BUDGET_DISPLAY_CODE;

Line: 285

 select 'Y' into x_error_flag from dual where exists(
 select fcp.column_name column_name from fem_tab_column_prop fcp
 where fcp.table_name = 'FEM_BALANCES'
 and fcp.column_property_code = 'PROCESSING_KEY'
 and fcp.column_name not in
 ('CREATED_BY_REQUEST_ID',
 'CREATED_BY_OBJECT_ID',
 'LAST_UPDATED_BY_REQUEST_ID',
 'LAST_UPDATED_BY_OBJECT_ID',
 'CREATION_ROW_SEQUENCE',
 'DATASET_CODE',
 'CAL_PERIOD_ID')
 and (not exists (select 1 from fem_tab_columns_b ftc where table_name = 'FEM_BALANCES' and
 ftc.column_name = fcp.column_name) or
 fcp.column_name in(select column_name from fem_tab_columns_b ftc where table_name = 'FEM_BALANCES' and
 ftc.column_name = fcp.column_name and interface_column_name is null))
 UNION
 select column_name from dba_tab_columns M
 where owner = 'FEM' and table_name = 'FEM_BAL_INTERFACE_T'
 and nullable = 'N'  and column_name in (select substr(interface_col_name,3) from
 bne_interface_cols_b where interface_code = 'FEM_BALANCES_INTF')
 and column_name not in ('LOAD_SET_ID','LOAD_METHOD_CODE','BAL_POST_TYPE_CODE',
'DS_BALANCE_TYPE_CODE','CAL_PER_DIM_GRP_DISPLAY_CODE','CAL_PERIOD_NUMBER','CAL_PERIOD_END_DATE',
'XTD_BALANCE_E','XTD_BALANCE_F','YTD_BALANCE_E','YTD_BALANCE_F','QTD_BALANCE_E','QTD_BALANCE_F',
'PTD_DEBIT_BALANCE_E','PTD_CREDIT_BALANCE_E','YTD_DEBIT_BALANCE_E','YTD_CREDIT_BALANCE_E')
 and not exists (SELECT 1 FROM FEM_TAB_COLUMNS_B T WHERE TABLE_NAME = 'FEM_BALANCES' AND T.INTERFACE_COLUMN_NAME = M.COLUMN_NAME));

Line: 341

(select table_name,column_name,data_type,nullable from dba_tab_columns
where owner = 'FEM'
and table_name = 'FEM_BAL_INTERFACE_T'
and column_name in (select substr(interface_col_name,3) from
bne_interface_cols_b where interface_code = 'FEM_BALANCES_INTF'))

loop

 begin
  select fem_data_type_code into l_fem_type from fem_tab_columns_b
  where table_name = 'FEM_BALANCES'
  and interface_column_name = interface_cols.column_name;

Line: 360

    update bne_interface_cols_tl
    set user_hint = DECODE(interface_cols.DATA_TYPE, 'VARCHAR2',
                  DECODE(interface_cols.nullable, 'N',l_varchar_req_field, l_varchar_field),
                  DECODE(interface_cols.DATA_TYPE, 'NUMBER',
                  DECODE(interface_cols.nullable, 'N',l_number_req_field, l_number_field),
                  DECODE(interface_cols.DATA_TYPE, 'DATE',
                  DECODE(interface_cols.nullable, 'N',l_date_req_field, l_date_field), NULL)))
    where sequence_num = (select sequence_num from bne_interface_cols_b where interface_col_name = 'P_' || interface_cols.column_name
                          and interface_code = 'FEM_BALANCES_INTF')
    and interface_code = 'FEM_BALANCES_INTF'
    and language = userenv('LANG');

Line: 373

    update bne_interface_cols_tl
    set user_hint = DECODE(interface_cols.nullable, 'N',l_lov_req_field, l_lov_field)
    where sequence_num = (select sequence_num from bne_interface_cols_b where interface_col_name = 'P_' || interface_cols.column_name
                          and interface_code = 'FEM_BALANCES_INTF')
    and interface_code = 'FEM_BALANCES_INTF'
    and language = userenv('LANG');

Line: 384

   update bne_interface_cols_b
   set not_null_flag = 'N',
   required_flag = 'N'
   where interface_code = 'FEM_BALANCES_INTF'
   and interface_col_name = 'P_' || interface_cols.column_name;

Line: 390

   update bne_interface_cols_b
   set not_null_flag = 'Y',
   required_flag = 'Y'
   where interface_code = 'FEM_BALANCES_INTF'
   and interface_col_name = 'P_' || interface_cols.column_name;

Line: 405

(select table_name,column_name,data_type,nullable from dba_tab_columns
where owner = 'FEM'
and table_name = 'FEM_BAL_INTERFACE_T'
and column_name in (select ftc.interface_column_name interface_column_name from fem_tab_columns_b ftc,fem_tab_column_prop fcp
where ftc.table_name = fcp.table_name
and ftc.column_name = fcp.column_name
and ftc.table_name = 'FEM_BALANCES'
and fcp.column_property_code = 'PROCESSING_KEY'
and ftc.column_name not in
('CREATED_BY_REQUEST_ID',
'CREATED_BY_OBJECT_ID',
'LAST_UPDATED_BY_REQUEST_ID',
'LAST_UPDATED_BY_OBJECT_ID',
'CREATION_ROW_SEQUENCE')))

loop

 begin
 select fem_data_type_code into l_fem_type from fem_tab_columns_b
 where table_name = 'FEM_BALANCES'
 and interface_column_name = key_recs.column_name;

Line: 434

    update bne_interface_cols_tl
    set user_hint = DECODE(key_recs.DATA_TYPE, 'VARCHAR2',l_varchar_req_field,'NUMBER',l_number_req_field,
                   'DATE',l_date_req_field, NULL)
    where sequence_num = (select sequence_num from bne_interface_cols_b where interface_col_name = 'P_' || key_recs.column_name
                          and interface_code = 'FEM_BALANCES_INTF')
    and interface_code = 'FEM_BALANCES_INTF'
    and language = userenv('LANG');

Line: 443

    update bne_interface_cols_tl
    set user_hint = l_lov_req_field
    where sequence_num = (select sequence_num from bne_interface_cols_b where interface_col_name = 'P_' || key_recs.column_name
                          and interface_code = 'FEM_BALANCES_INTF')
    and interface_code = 'FEM_BALANCES_INTF'
    and language = userenv('LANG');

Line: 454

  update bne_interface_cols_b
  set not_null_flag = 'Y',
  required_flag = 'Y'
  where interface_code = 'FEM_BALANCES_INTF'
  and interface_col_name = 'P_'|| key_recs.column_name;

Line: 467

   update bne_interface_cols_b
   set display_order = null
   where interface_code = 'FEM_BALANCES_INTF' and
   sequence_num not in (1,2,3,4,5,9,10,11,12);

Line: 474

   select interface_col_name from bne_interface_cols_vl
   where interface_code = 'FEM_BALANCES_INTF'
   and display_flag = 'Y' and enabled_flag = 'Y'
   and sequence_num not in (1,2,3,4,5,9,10,11,12)
   order by not_null_flag desc,upper(prompt_above)
   )

  loop  -- Starting the loop to update entries one by one

   update bne_interface_cols_b
   set display_order = l_display_order * 10
   where interface_code = 'FEM_BALANCES_INTF'
   and interface_col_name = bne_cols.interface_col_name;

Line: 492

  update bne_layout_cols b
  set interface_seq_num = (select sequence_num from bne_interface_cols_b where interface_code = 'FEM_BALANCES_INTF' and display_order = b.sequence_num
  and sequence_num not in (1,2,3,4,5,9,10,11,12))  where layout_code = 'FEM_BALANCES_LAYOUT'
  and block_id =2;