eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.FEM_INTG_BAL_ENG_LOAD_PKG SQL Statements

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

Line: 47


    SELECT	fvs.flex_value_set_id,
		fvs.validation_type
    INTO	v_value_set_id,
		v_value_set_type
    FROM	gl_ledgers lgr,
		fnd_segment_attribute_values sav,
		fnd_id_flex_segments ifs,
		fnd_flex_value_sets fvs
    WHERE	lgr.ledger_id = FEM_GL_POST_PROCESS_PKG.pv_ledger_id
    AND		sav.application_id = 101
    AND		sav.id_flex_code = 'GL#'
    AND		sav.id_flex_num = lgr.chart_of_accounts_id
    AND		sav.segment_attribute_type = 'GL_BALANCING'
    AND		sav.attribute_value = 'Y'
    AND		ifs.application_id = 101
    AND		ifs.id_flex_code = 'GL#'
    AND		ifs.id_flex_num = lgr.chart_of_accounts_id
    AND		ifs.application_column_name = sav.application_column_name
    AND		fvs.flex_value_set_id = ifs.flex_value_set_id;

Line: 70


'SELECT flex_value' || pv_nl ||
'FROM   fnd_flex_values' || pv_nl ||
'WHERE  flex_value_set_id = ' || v_value_set_id || pv_nl ||
'AND    summary_flag <> ''Y''';

Line: 75


      SELECT	application_table_name,
		value_column_name,
		additional_where_clause,
		summary_column_name
      INTO	v_table_name,
		v_value_col_name,
		v_where_clause,
		v_summary_col_name
      FROM	fnd_flex_validation_tables
      WHERE	flex_value_set_id = v_value_set_id;

Line: 87


'SELECT flex.flex_value' || pv_nl ||
'FROM   (SELECT ' || v_value_col_name || ' flex_value,' || pv_nl ||
'               ' || v_summary_col_name || ' summary_flag' || pv_nl ||
'        FROM   ' || v_table_name || pv_nl ||
'        ' || v_where_clause || ') flex' || pv_nl ||
'WHERE  flex.summary_flag <> ''Y''';

Line: 106


				x_num_rows_inserted	OUT NOCOPY NUMBER,
				p_bsv_range_low		VARCHAR2,
				p_bsv_range_high	VARCHAR2,
				p_maintain_qtd		VARCHAR2) IS
    v_sql	VARCHAR2(32767);

Line: 114


    v_insert		VARCHAR2(2000);

Line: 115


    v_ccmap_selection	VARCHAR2(1000);

Line: 119


    v_intermediate_rows_inserted	NUMBER;

Line: 132


    SELECT 1
    FROM fem_bal_post_interim_gt
    WHERE posting_error_flag = 'Y'
    AND   (nvl(xtd_balance_e,0) <> 0 OR
           nvl(xtd_balance_f,0) <> 0 OR
           nvl(ytd_balance_e,0) <> 0 OR
           nvl(ytd_balance_f,0) <> 0 OR
           nvl(qtd_balance_e,0) <> 0 OR
           nvl(qtd_balance_f,0) <> 0 OR
           nvl(ptd_debit_balance_e,0) <> 0 OR
           nvl(ptd_credit_balance_e,0) <> 0 OR
           nvl(ytd_debit_balance_e,0) <> 0 OR
           nvl(ytd_credit_balance_e,0) <> 0);

Line: 164


    x_num_rows_inserted := 0;

Line: 181


    v_insert :=
'INSERT INTO FEM_BAL_POST_INTERIM_GT(INTERFACE_ROWID, DELTA_RUN_ID, ' ||
'BAL_POST_TYPE_CODE, DATASET_CODE, CAL_PERIOD_ID,  LEDGER_ID, ' ||
'SOURCE_SYSTEM_CODE, COMPANY_COST_CENTER_ORG_ID, FINANCIAL_ELEM_ID, ' ||
'PRODUCT_ID, NATURAL_ACCOUNT_ID, CHANNEL_ID, LINE_ITEM_ID, PROJECT_ID, ' ||
'CUSTOMER_ID, ENTITY_ID, INTERCOMPANY_ID, TASK_ID, USER_DIM1_ID, ' ||
'USER_DIM2_ID, USER_DIM3_ID, USER_DIM4_ID, USER_DIM5_ID, USER_DIM6_ID, ' ||
'USER_DIM7_ID, USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID, CURRENCY_CODE, ' ||
'CURRENCY_TYPE_CODE, POSTING_ERROR_FLAG, CODE_COMBINATION_ID, ' ||
'XTD_BALANCE_E,  QTD_BALANCE_E, YTD_BALANCE_E, XTD_BALANCE_F, ' ||
'QTD_BALANCE_F,  YTD_BALANCE_F, PTD_DEBIT_BALANCE_E, PTD_CREDIT_BALANCE_E, ' ||
'YTD_DEBIT_BALANCE_E, YTD_CREDIT_BALANCE_E)' || pv_nl;

Line: 194


    v_ccmap_selection :=
'  ccmap.PRODUCT_ID,' || pv_nl ||
'  ccmap.NATURAL_ACCOUNT_ID,' || pv_nl ||
'  ccmap.CHANNEL_ID,' || pv_nl ||
'  ccmap.LINE_ITEM_ID,' || pv_nl ||
'  ccmap.PROJECT_ID,' || pv_nl ||
'  ccmap.CUSTOMER_ID,' || pv_nl ||
'  ccmap.ENTITY_ID,' || pv_nl ||
'  ccmap.INTERCOMPANY_ID,' || pv_nl ||
'  ccmap.TASK_ID,' || pv_nl ||
'  ccmap.USER_DIM1_ID,' || pv_nl ||
'  ccmap.USER_DIM2_ID,' || pv_nl ||
'  ccmap.USER_DIM3_ID,' || pv_nl ||
'  ccmap.USER_DIM4_ID,' || pv_nl ||
'  ccmap.USER_DIM5_ID,' || pv_nl ||
'  ccmap.USER_DIM6_ID,' || pv_nl ||
'  ccmap.USER_DIM7_ID,' || pv_nl ||
'  ccmap.USER_DIM8_ID,' || pv_nl ||
'  ccmap.USER_DIM9_ID,' || pv_nl ||
'  ccmap.USER_DIM10_ID,' || pv_nl;

Line: 247


'        (SELECT 1' || pv_nl ||
'         FROM FEM_INTG_BAL_DEF_CURRS ccy' || pv_nl ||
'         WHERE ccy.bal_rule_obj_def_id = ' || FEM_GL_POST_PROCESS_PKG.pv_rule_obj_def_id || pv_nl ||
'         AND   ccy.xlated_currency_code = glb.currency_code)))' || pv_nl;

Line: 262


'        (SELECT 1' || pv_nl ||
'         FROM FEM_INTG_BAL_DEF_CURRS ccy' || pv_nl ||
'         WHERE ccy.bal_rule_obj_def_id = ' || FEM_GL_POST_PROCESS_PKG.pv_rule_obj_def_id || pv_nl ||
'         AND   ccy.xlated_currency_code = glb.currency_code)))' || pv_nl;

Line: 279


'      (SELECT 1' || pv_nl ||
'       FROM FEM_INTG_BAL_DEF_CURRS ccy' || pv_nl ||
'       WHERE ccy.bal_rule_obj_def_id = ' || FEM_GL_POST_PROCESS_PKG.pv_rule_obj_def_id || pv_nl ||
'       AND   ccy.xlated_currency_code = glb.currency_code)' || pv_nl;

Line: 293


v_insert ||
'SELECT' || pv_nl ||
'  glb.ROWID,' || pv_nl ||
'  null,' || pv_nl ||
'  ''R'',' || pv_nl ||
'  param.OUTPUT_DATASET_CODE,' || pv_nl ||
'  param.CAL_PERIOD_ID,' || pv_nl ||
'  ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || ',' || pv_nl ||
'  ' || FEM_GL_POST_PROCESS_PKG.pv_gl_source_system_code || ',' || pv_nl ||
'  ccmap.COMPANY_COST_CENTER_ORG_ID,' || pv_nl ||
'  decode(glb.currency_code,' || pv_nl ||
'         ''STAT'', 10000,' || pv_nl ||
'          decode(xat_acct.dim_attribute_varchar_member,' || pv_nl ||
'                 ''REVENUE'', 455,' || pv_nl ||
'                 ''EXPENSE'', 457,' || pv_nl ||
'                 100)),' || pv_nl ||
v_ccmap_selection ||
'  glb.CURRENCY_CODE,' || pv_nl ||
'  decode(glb.translated_flag,' || pv_nl ||
'         ''Y'', ''TRANSLATED'',' || pv_nl ||
'         ''N'', ''TRANSLATED'',' || pv_nl ||
'         ''ENTERED''),' || pv_nl ||
'  decode(ccmap.code_combination_id, null, ''Y'', ''N''),' || pv_nl ||
'  glb.code_combination_id,' || pv_nl ||
'  xat_sign.number_assign_value *' || pv_nl ||
'  decode(xat_acct.dim_attribute_varchar_member,' || pv_nl ||
'         ''REVENUE'', nvl(glb.period_net_dr,0) -' || pv_nl ||
'                    nvl(glb.period_net_cr,0),' || pv_nl ||
'         ''EXPENSE'', nvl(glb.period_net_dr,0) -' || pv_nl ||
'                    nvl(glb.period_net_cr,0),' || pv_nl ||
'         nvl(glb.begin_balance_dr,0)+nvl(glb.period_net_dr,0) -' || pv_nl ||
'         nvl(glb.begin_balance_cr,0)-nvl(glb.period_net_cr,0)),' || pv_nl;

Line: 458


'       (SELECT 1' || pv_nl ||
'        FROM fem_balances fb_curr' || pv_nl ||
'        WHERE fb_curr.ledger_id = ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || pv_nl ||
'        AND fb_curr.dataset_code = param.output_dataset_code' || pv_nl ||
'        AND fb_curr.cal_period_id = param.cal_period_id)' || pv_nl ||
'       OR cc.' || FEM_GL_POST_PROCESS_PKG.pv_bsv_app_col_name || ' BETWEEN ' || p_bsv_range_low || ' AND ' || p_bsv_range_high || ')' || pv_nl;

Line: 485


      v_intermediate_rows_inserted := SQL%ROWCOUNT;

Line: 491


        p_msg_text => 'Inserted ' || TO_CHAR(v_intermediate_rows_inserted) ||
                      ' rows into FEM_BAL_POST_INTERIM_GT');

Line: 494


      x_num_rows_inserted := v_intermediate_rows_inserted;

Line: 506


'INSERT INTO FEM_INTG_DELTA_LOADS dl' || pv_nl ||
'( LEDGER_ID' || pv_nl ||
'   ,DATASET_CODE' || pv_nl ||
'   ,CAL_PERIOD_ID' || pv_nl ||
'   ,DELTA_RUN_ID' || pv_nl ||
'   ,LOADED_FLAG' || pv_nl ||
'   ,CREATION_DATE' || pv_nl ||
'   ,CREATED_BY' || pv_nl ||
'   ,LAST_UPDATE_DATE' || pv_nl ||
'   ,LAST_UPDATED_BY' || pv_nl ||
'   ,LAST_UPDATE_LOGIN' || pv_nl ||
'   ,BALANCE_SEG_VALUE)' || pv_nl ||
' SELECT '|| pv_nl ||
        FEM_GL_POST_PROCESS_PKG.pv_ledger_id || pv_nl ||
'       ,param_in.output_dataset_code' || pv_nl ||
'       ,param_in.cal_period_id' || pv_nl ||
'       ,-1' || pv_nl ||
'       ,''Y''' || pv_nl ||
'       ,sysdate' || pv_nl ||
'       ,'|| FEM_GL_POST_PROCESS_PKG.pv_user_id|| pv_nl ||
'       ,sysdate' || pv_nl ||
'       ,'|| FEM_GL_POST_PROCESS_PKG.pv_user_id || pv_nl ||
'       ,'||FEM_GL_POST_PROCESS_PKG.pv_login_id || pv_nl ||
'       ,flex.flex_value' || pv_nl ||
'FROM   FEM_INTG_EXEC_PARAMS_GT param_in,' || pv_nl ||
'        (    '||v_flex_query_stmt||' ) flex' || pv_nl ||
'WHERE  param_in.error_code IS NULL' || pv_nl ||
'AND    param_in.request_id IS NOT NULL' || pv_nl ||
'AND    param_in.load_method_code = ''I''' || pv_nl ||
'AND    NOT EXISTS ( SELECT 1' || pv_nl ||
'                    FROM FEM_INTG_DELTA_LOADS' || pv_nl ||
'                    WHERE LEDGER_ID= '||FEM_GL_POST_PROCESS_PKG.pv_ledger_id || pv_nl ||
'                    AND DATASET_CODE = param_in.output_dataset_code' || pv_nl ||
'                    AND CAL_PERIOD_ID = param_in.cal_period_id' || pv_nl ||
'                    AND BALANCE_SEG_VALUE = flex.flex_value)';

Line: 556


v_insert ||
'SELECT' || pv_nl ||
'  glb.ROWID,' || pv_nl ||
'  glb.delta_run_id,' || pv_nl ||
'  ''A'',' || pv_nl ||
'  param.OUTPUT_DATASET_CODE,' || pv_nl ||
'  param.CAL_PERIOD_ID,' || pv_nl ||
'  ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || ',' || pv_nl ||
'  ' || FEM_GL_POST_PROCESS_PKG.pv_gl_source_system_code || ',' || pv_nl ||
'  ccmap.COMPANY_COST_CENTER_ORG_ID,' || pv_nl ||
'  decode(glb.currency_code,' || pv_nl ||
'         ''STAT'', 10000,' || pv_nl ||
'          decode(xat_acct.dim_attribute_varchar_member,' || pv_nl ||
'                 ''REVENUE'', 455,' || pv_nl ||
'                 ''EXPENSE'', 457,' || pv_nl ||
'                 100)),' || pv_nl ||
v_ccmap_selection ||
'  glb.CURRENCY_CODE,' || pv_nl ||
'  decode(glb.translated_flag,' || pv_nl ||
'         ''Y'', ''TRANSLATED'',' || pv_nl ||
'         ''N'', ''TRANSLATED'',' || pv_nl ||
'         ''ENTERED''),' || pv_nl ||
'  decode(ccmap.code_combination_id, null, ''Y'', ''N''), ' || pv_nl ||
'  glb.code_combination_id,' || pv_nl ||
'  xat_sign.number_assign_value *' || pv_nl ||
'  decode(xat_acct.dim_attribute_varchar_member,' || pv_nl ||
'         ''REVENUE'',nvl(glb.period_net_dr,0)-nvl(glb.period_net_cr,0),' || pv_nl ||
'         ''EXPENSE'',nvl(glb.period_net_dr,0)-nvl(glb.period_net_cr,0),' || pv_nl ||
'         nvl(glb.begin_balance_dr,0)+nvl(glb.period_net_dr,0)-' || pv_nl ||
'         nvl(glb.begin_balance_cr,0)-nvl(glb.period_net_cr,0)), ' || pv_nl;

Line: 744


      v_intermediate_rows_inserted := SQL%ROWCOUNT;

Line: 750


        p_msg_text => 'Inserted ' || TO_CHAR(v_intermediate_rows_inserted) ||
                      ' rows into FEM_BAL_POST_INTERIM_GT');

Line: 753


      x_num_rows_inserted := x_num_rows_inserted + v_intermediate_rows_inserted;

Line: 775


      v_intermediate_rows_inserted := SQL%ROWCOUNT;

Line: 781


        p_msg_text => 'Inserted ' || TO_CHAR(v_intermediate_rows_inserted) ||
                      ' rows into FEM_BAL_POST_INTERIM_GT');

Line: 784


      x_num_rows_inserted := x_num_rows_inserted + v_intermediate_rows_inserted;

Line: 827


				x_num_rows_inserted	OUT NOCOPY NUMBER,
				p_effective_date	DATE,
				p_bsv_range_low		VARCHAR2,
				p_bsv_range_high	VARCHAR2) IS

    -- Since the statements are only going to vary by a few parts of the
    -- string, it makes more sense to create a template, and then replace the
    -- necessary parts of the statement with the information for each period
    v_sql_template	VARCHAR2(32767);

Line: 838


    v_intermediate_rows_inserted	NUMBER;

Line: 843


    SELECT DISTINCT
	param.period_name,
	ps.end_date period_end_date,
	ps.start_date period_start_date,
	ps.quarter_start_date,
	ps.year_start_date
    FROM FEM_INTG_EXEC_PARAMS_GT param,
         GL_PERIOD_STATUSES ps
    WHERE param.error_code IS NULL
    AND   param.request_id IS NOT NULL
    AND   ps.application_id = 101
    AND   ps.ledger_id = FEM_GL_POST_PROCESS_PKG.pv_ledger_id
    AND   ps.period_name = param.period_name;

Line: 878


    x_num_rows_inserted := 0;

Line: 910


'INSERT INTO FEM_BAL_POST_INTERIM_GT(INTERFACE_ROWID, BAL_POST_TYPE_CODE, ' ||
'DATASET_CODE, CAL_PERIOD_ID,  LEDGER_ID, SOURCE_SYSTEM_CODE, ' ||
'COMPANY_COST_CENTER_ORG_ID, FINANCIAL_ELEM_ID, PRODUCT_ID, ' ||
'NATURAL_ACCOUNT_ID, CHANNEL_ID, LINE_ITEM_ID, PROJECT_ID, CUSTOMER_ID, ' ||
'ENTITY_ID, INTERCOMPANY_ID, TASK_ID, USER_DIM1_ID, USER_DIM2_ID, ' ||
'USER_DIM3_ID, USER_DIM4_ID, USER_DIM5_ID, USER_DIM6_ID, USER_DIM7_ID, ' ||
'USER_DIM8_ID, USER_DIM9_ID, USER_DIM10_ID, CURRENCY_CODE, ' ||
'CURRENCY_TYPE_CODE, POSTING_ERROR_FLAG, CODE_COMBINATION_ID, ' ||
'XTD_BALANCE_E, QTD_BALANCE_E, YTD_BALANCE_E, XTD_BALANCE_F, ' ||
'QTD_BALANCE_F, YTD_BALANCE_F, PTD_DEBIT_BALANCE_E, PTD_CREDIT_BALANCE_E, ' ||
'YTD_DEBIT_BALANCE_E, YTD_CREDIT_BALANCE_E)' || pv_nl ||
'SELECT' || pv_nl ||
'  glb.ROWID,' || pv_nl ||
'  ''R'',' || pv_nl ||
'  param.OUTPUT_DATASET_CODE,' || pv_nl ||
'  param.CAL_PERIOD_ID,' || pv_nl ||
'  ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || ',' || pv_nl ||
'  ' || FEM_GL_POST_PROCESS_PKG.pv_gl_source_system_code || ',' || pv_nl ||
'  ccmap.COMPANY_COST_CENTER_ORG_ID,' || pv_nl ||
'  140,' || pv_nl ||
'  ccmap.PRODUCT_ID,' || pv_nl ||
'  ccmap.NATURAL_ACCOUNT_ID,' || pv_nl ||
'  ccmap.CHANNEL_ID,' || pv_nl ||
'  ccmap.LINE_ITEM_ID,' || pv_nl ||
'  ccmap.PROJECT_ID,' || pv_nl ||
'  ccmap.CUSTOMER_ID,' || pv_nl ||
'  ccmap.ENTITY_ID,' || pv_nl ||
'  ccmap.INTERCOMPANY_ID,' || pv_nl ||
'  ccmap.TASK_ID,' || pv_nl ||
'  ccmap.USER_DIM1_ID,' || pv_nl ||
'  ccmap.USER_DIM2_ID,' || pv_nl ||
'  ccmap.USER_DIM3_ID,' || pv_nl ||
'  ccmap.USER_DIM4_ID,' || pv_nl ||
'  ccmap.USER_DIM5_ID,' || pv_nl ||
'  ccmap.USER_DIM6_ID,' || pv_nl ||
'  ccmap.USER_DIM7_ID,' || pv_nl ||
'  ccmap.USER_DIM8_ID,' || pv_nl ||
'  ccmap.USER_DIM9_ID,' || pv_nl ||
'  ccmap.USER_DIM10_ID,' || pv_nl ||
'  glb.CURRENCY_CODE,' || pv_nl ||
'  decode(glb.currency_type,' || pv_nl ||
'         ''T'', ''TRANSLATED'',' || pv_nl ||
'         ''O'', ''TRANSLATED'',' || pv_nl ||
'         ''ENTERED''),' || pv_nl ||
'  decode(ccmap.code_combination_id, null, ''Y'', ''N''),' || pv_nl ||
'  glb.code_combination_id,' || pv_nl ||
'  round(xat_sign.number_assign_value *' || pv_nl ||
'        nvl(glb.period_aggregate<<>>,0) / <<>> / ccy_mau.mau) * ccy_mau.mau,' || pv_nl ||
'  round(xat_sign.number_assign_value *' || pv_nl ||
'        decode(glb.currency_type,' || pv_nl ||
'               ''T'', nvl(glb.quarter_aggregate<<>>,0),' || pv_nl ||
'               ''O'', nvl(glb.quarter_aggregate<<>>,0),' || pv_nl ||
'               nvl(glb.opening_quarter_aggregate,0) +' || pv_nl ||
'               nvl(glb.period_aggregate<<>>,0)) /' || pv_nl ||
'        <<>> / ccy_mau.mau) * ccy_mau.mau,' || pv_nl ||
'  round(xat_sign.number_assign_value *' || pv_nl ||
'        decode(glb.currency_type,' || pv_nl ||
'               ''T'', nvl(glb.year_aggregate<<>>,0),' || pv_nl ||
'               ''O'', nvl(glb.year_aggregate<<>>,0),' || pv_nl ||
'               nvl(glb.opening_year_aggregate,0) +' || pv_nl ||
'               nvl(glb.period_aggregate<<>>,0)) /' || pv_nl ||
'        <<>> / ccy_mau.mau) * ccy_mau.mau,' || pv_nl ||
'  round(xat_sign.number_assign_value *' || pv_nl ||
'        decode(glb.currency_type,' || pv_nl ||
'               ''T'', null,' || pv_nl ||
'               ''O'', null,' || pv_nl;

Line: 1063


'  (SELECT currency_code,' || pv_nl ||
'          nvl(minimum_accountable_unit, power(10,-precision)) mau' || pv_nl ||
'   FROM   FND_CURRENCIES fccy) ccy_mau,' || pv_nl ||
'  (SELECT currency_code,' || pv_nl ||
'          nvl(minimum_accountable_unit, power(10,-precision)) mau' || pv_nl ||
'   FROM   FND_CURRENCIES fccy) ccy_mau_c' || pv_nl ||
'WHERE param.error_code IS NULL' || pv_nl ||
'AND   param.request_id IS NOT NULL' || pv_nl ||
'AND   param.period_name = ''<<>>''' || pv_nl ||
'AND   ccy_mau.currency_code = glb.currency_code' || pv_nl;

Line: 1139


'       (SELECT 1' || pv_nl ||
'        FROM fem_balances fb_curr' || pv_nl ||
'        WHERE fb_curr.ledger_id = ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || pv_nl ||
'        AND fb_curr.dataset_code = param.output_dataset_code' || pv_nl ||
'        AND fb_curr.cal_period_id = param.cal_period_id)' || pv_nl ||
'       OR cc.' || FEM_GL_POST_PROCESS_PKG.pv_bsv_app_col_name || ' BETWEEN ' || p_bsv_range_low || ' AND ' || p_bsv_range_high || ')' || pv_nl;

Line: 1153


'        (SELECT 1' || pv_nl ||
'         FROM FEM_INTG_BAL_DEF_CURRS ccy' || pv_nl ||
'         WHERE ccy.bal_rule_obj_def_id = ' || FEM_GL_POST_PROCESS_PKG.pv_rule_obj_def_id || pv_nl ||
'         AND   ccy.xlated_currency_code = glb.currency_code)))' || pv_nl;

Line: 1170


'        (SELECT 1' || pv_nl ||
'         FROM FEM_INTG_BAL_DEF_CURRS ccy' || pv_nl ||
'         WHERE ccy.bal_rule_obj_def_id = ' || FEM_GL_POST_PROCESS_PKG.pv_rule_obj_def_id || pv_nl ||
'         AND   ccy.xlated_currency_code = glb.currency_code)))' || pv_nl;

Line: 1186


'      (SELECT 1' || pv_nl ||
'       FROM FEM_INTG_BAL_DEF_CURRS ccy' || pv_nl ||
'       WHERE ccy.bal_rule_obj_def_id = ' || FEM_GL_POST_PROCESS_PKG.pv_rule_obj_def_id || pv_nl ||
'       AND   ccy.xlated_currency_code = glb.currency_code)' || pv_nl;

Line: 1256


      v_intermediate_rows_inserted := SQL%ROWCOUNT;

Line: 1257


      x_num_rows_inserted := x_num_rows_inserted+v_intermediate_rows_inserted;

Line: 1263


        p_msg_text => 'Inserted ' || TO_CHAR(v_intermediate_rows_inserted) ||
                      ' rows into FEM_BAL_POST_INTERIM_GT');

Line: 1325


    INSERT INTO fem_intg_bpi_curr_gt(
 dataset_code, cal_period_id, code_combination_id, financial_elem_id,
 delta_run_id, xtd_balance_f_sum, qtd_balance_f_sum, ytd_balance_f_sum)
    SELECT /*+ full(fem_bal_post_interim_gt) */
           dataset_code, cal_period_id, code_combination_id,
           financial_elem_id, delta_run_id,
           SUM(nvl(xtd_balance_f,0)),
           SUM(nvl(qtd_balance_f,0)),
           SUM(nvl(ytd_balance_f,0))
    FROM fem_bal_post_interim_gt
    WHERE currency_code <> FEM_GL_POST_PROCESS_PKG.pv_func_ccy_code
    AND   currency_code <> 'STAT'
    AND   currency_type_code = 'ENTERED'
    AND   posting_error_flag = 'N'
    GROUP BY dataset_code, cal_period_id, code_combination_id,
             financial_elem_id, delta_run_id;

Line: 1342


    UPDATE /*+ FULL(bpi) */ FEM_BAL_POST_INTERIM_GT bpi
    SET (xtd_balance_e,
         qtd_balance_e,
         ytd_balance_e,
         xtd_balance_f,
         qtd_balance_f,
         ytd_balance_f) =
    (SELECT
       bpi.xtd_balance_e - nvl(bpi_beq.xtd_balance_f_sum,0),
       bpi.qtd_balance_e - nvl(bpi_beq.qtd_balance_f_sum,0),
       bpi.ytd_balance_e - nvl(bpi_beq.ytd_balance_f_sum,0),
       bpi.xtd_balance_e - nvl(bpi_beq.xtd_balance_f_sum,0),
       bpi.qtd_balance_e - nvl(bpi_beq.qtd_balance_f_sum,0),
       bpi.ytd_balance_e - nvl(bpi_beq.ytd_balance_f_sum,0)
     FROM FEM_INTG_BPI_CURR_GT bpi_beq
     WHERE bpi_beq.dataset_code = bpi.dataset_code
     AND   bpi_beq.cal_period_id = bpi.cal_period_id
     AND   bpi_beq.code_combination_id = bpi.code_combination_id
     AND   bpi_beq.financial_elem_id = bpi.financial_elem_id
     AND   ((bpi_beq.delta_run_id IS NULL AND bpi.delta_run_id IS NULL) OR
            bpi_beq.delta_run_id = bpi.delta_run_id)
    )
    WHERE bpi.currency_code = FEM_GL_POST_PROCESS_PKG.pv_func_ccy_code
    AND   bpi.posting_error_flag = 'N'
    AND   EXISTS
    (SELECT 1
     FROM fem_intg_bpi_curr_gt bpi_beq
     WHERE bpi_beq.dataset_code = bpi.dataset_code
     AND   bpi_beq.cal_period_id = bpi.cal_period_id
     AND   bpi_beq.code_combination_id = bpi.code_combination_id
     AND   bpi_beq.financial_elem_id = bpi.financial_elem_id
     AND   ((bpi_beq.delta_run_id IS NULL AND bpi.delta_run_id IS NULL) OR
            bpi_beq.delta_run_id = bpi.delta_run_id));

Line: 1380


      p_msg_text => 'Updated ' || TO_CHAR(SQL%ROWCOUNT) ||
                    ' rows in FEM_BAL_POST_INTERIM_GT');

Line: 1450


    UPDATE FEM_BAL_POST_INTERIM_GT bpi
    SET (FINANCIAL_ELEM_ID, LINE_ITEM_ID) =
    (SELECT nvl(naa_fe.dim_attribute_numeric_member, bpi.financial_elem_id),
            nvl(naa_li.dim_attribute_numeric_member, bpi.line_item_id)
     FROM   FEM_NAT_ACCTS_ATTR naa_fe,
            FEM_NAT_ACCTS_ATTR naa_li
     WHERE  naa_fe.attribute_id (+)= v_na_fe_attr_id
     AND    naa_fe.version_id (+)= v_na_fe_v_id
     AND    naa_fe.natural_account_id (+)= bpi.natural_account_id
     AND    naa_li.attribute_id (+)= v_na_li_attr_id
     AND    naa_li.version_id (+)= v_na_li_v_id
     AND    naa_li.natural_account_id (+)= bpi.natural_account_id)
    WHERE  bpi.posting_error_flag = 'N';

Line: 1470


      p_msg_text => 'Updated ' || TO_CHAR(SQL%ROWCOUNT) ||
                    ' rows in FEM_BAL_POST_INTERIM_GT');

Line: 1508


    v_delete_stmt	VARCHAR2(4000);

Line: 1509


    v_insert_stmt	VARCHAR2(4000);

Line: 1511


    v_update_stmt	VARCHAR2(4000);

Line: 1538


    INSERT INTO GL_TRACK_DELTA_BALANCES(
      ledger_id, program_code, period_name, actual_flag,
      extract_level_code, currency_type_code, enabled_flag, last_update_date,
      last_updated_by, creation_date, created_by, last_update_login)
    SELECT DISTINCT FEM_GL_POST_PROCESS_PKG.pv_ledger_id,
                    'FEM',
                    param.period_name,
                    v_actual_flag,
                    'DTL',
                    'B',
                    'Y',
                    sysdate,
                    FEM_GL_POST_PROCESS_PKG.pv_user_id,
                    sysdate,
                    FEM_GL_POST_PROCESS_PKG.pv_user_id,
                    FEM_GL_POST_PROCESS_PKG.pv_login_id
    FROM   FEM_INTG_EXEC_PARAMS_GT param
    WHERE  param.error_code IS NULL
    AND    param.request_id IS NOT NULL
    AND    NOT EXISTS
           (SELECT 1
            FROM   GL_TRACK_DELTA_BALANCES tdb
            WHERE  tdb.ledger_id = FEM_GL_POST_PROCESS_PKG.pv_ledger_id
            AND    tdb.program_code = 'FEM'
            AND    tdb.period_name = param.period_name
            AND    tdb.actual_flag = v_actual_flag
            AND    tdb.extract_level_code = 'DTL'
            AND    tdb.currency_type_code = 'B'
            AND    tdb.enabled_flag = 'Y');

Line: 1571


      p_msg_text => 'Inserted ' || TO_CHAR(SQL%ROWCOUNT) ||
                    ' rows into GL_TRACK_DELTA_BALANCES');

Line: 1577


    v_delete_stmt :=
'DELETE FROM FEM_INTG_DELTA_LOADS dl' || pv_nl ||
'WHERE  dl.ledger_id = ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || pv_nl ||
'AND    dl.loaded_flag = ''N''' || pv_nl;

Line: 1585


      v_delete_stmt := v_delete_stmt ||
'AND    dl.balance_seg_value BETWEEN ' || p_bsv_range_low || ' AND ' ||
p_bsv_range_high || pv_nl;

Line: 1590


    v_delete_stmt := v_delete_stmt ||
'AND    EXISTS' || pv_nl ||
'       (SELECT 1' || pv_nl ||
'        FROM   FEM_INTG_EXEC_PARAMS_GT param' || pv_nl ||
'        WHERE  param.output_dataset_code  = dl.dataset_code' || pv_nl ||
'        AND    param.cal_period_id = dl.cal_period_id' || pv_nl ||
'        AND    param.error_code IS NULL' || pv_nl ||
'        AND    param.request_id IS NOT NULL)' || pv_nl ||
'AND    NOT EXISTS' || pv_nl ||
'       (SELECT 1' || pv_nl ||
'        FROM   FEM_BAL_POST_INTERIM_GT bpi,' || pv_nl ||
'               GL_CODE_COMBINATIONS from_cc' || pv_nl ||
'        WHERE  bpi.delta_run_id = dl.delta_run_id' || pv_nl ||
'        AND    bpi.posting_error_flag = ''Y''' || pv_nl ||
'        AND    from_cc.code_combination_id = bpi.code_combination_id' || pv_nl ||
'        AND    from_cc.' || FEM_GL_POST_PROCESS_PKG.pv_bsv_app_col_name ||
' = dl.balance_seg_value)';

Line: 1609


    FOR iterator IN 1..trunc((length(v_delete_stmt)+1499)/1500) LOOP
      FEM_ENGINES_PKG.Tech_Message
      (p_severity => pc_log_level_statement,
       p_module   => v_module,
       p_app_name => 'FEM',
       p_msg_name => 'FEM_GL_POST_204',
       p_token1   => 'VAR_NAME',
       p_value1   => 'v_delete_stmt: ' || iterator,
       p_token2   => 'VAR_VAL',
       p_value2   => substr(v_delete_stmt, iterator*1500-1499, 1500));

Line: 1621


    EXECUTE IMMEDIATE v_delete_stmt;

Line: 1626


      p_msg_text => 'Deleted ' || TO_CHAR(SQL%ROWCOUNT) ||
                    ' rows from FEM_INTG_DELTA_LOADS');

Line: 1632


    v_insert_stmt :=
'INSERT INTO FEM_INTG_DELTA_LOADS(LEDGER_ID, DATASET_CODE, CAL_PERIOD_ID, ' ||
'DELTA_RUN_ID, BALANCE_SEG_VALUE, LOADED_FLAG, CREATION_DATE, CREATED_BY, ' ||
'LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN)' || pv_nl ||
'SELECT DISTINCT ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || ',' || pv_nl ||
'                param.output_dataset_code,' || pv_nl ||
'                param.cal_period_id,' || pv_nl ||
'                bpi.delta_run_id,' || pv_nl ||
'                dl.balance_seg_value,' || pv_nl ||
'                ''N'',' || pv_nl ||
'                sysdate,' || pv_nl ||
'                ' || FEM_GL_POST_PROCESS_PKG.pv_user_id || ',' || pv_nl ||
'                sysdate,' || pv_nl ||
'                ' || FEM_GL_POST_PROCESS_PKG.pv_user_id || ',' || pv_nl ||
'                ' || FEM_GL_POST_PROCESS_PKG.pv_login_id || pv_nl ||
'FROM   FEM_INTG_EXEC_PARAMS_GT param,' || pv_nl ||
'       FEM_BAL_POST_INTERIM_GT bpi,' || pv_nl ||
'       GL_CODE_COMBINATIONS from_cc,' || pv_nl ||
'       FEM_INTG_DELTA_LOADS dl' || pv_nl ||
'WHERE  param.load_method_code = ''I''' || pv_nl ||
'AND    param.error_code IS NULL' || pv_nl ||
'AND    param.request_id IS NOT NULL' || pv_nl ||
'AND    dl.ledger_id = ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || pv_nl ||
'AND    dl.dataset_code = param.output_dataset_code' || pv_nl ||
'AND    dl.cal_period_id = param.cal_period_id' || pv_nl ||
'AND    dl.loaded_flag = ''Y''' || pv_nl;

Line: 1660


      v_insert_stmt := v_insert_stmt ||
'AND    dl.balance_seg_value BETWEEN ' || p_bsv_range_low || ' AND ' ||
p_bsv_range_high || pv_nl;

Line: 1665


    v_insert_stmt := v_insert_stmt ||
'AND    from_cc.code_combination_id = bpi.code_combination_id' || pv_nl ||
'AND    from_cc.' || FEM_GL_POST_PROCESS_PKG.pv_bsv_app_col_name ||
' = dl.balance_seg_value' || pv_nl ||
'AND    bpi.dataset_code = param.output_dataset_code' || pv_nl ||
'AND    bpi.cal_period_id = param.cal_period_id' || pv_nl ||
'AND    bpi.bal_post_type_code = ''A''' || pv_nl ||
'AND    bpi.posting_error_flag = ''Y''' || pv_nl ||
'AND    bpi.delta_run_id BETWEEN dl.delta_run_id + 1 AND ' ||
FEM_GL_POST_PROCESS_PKG.pv_max_delta_run_id;

Line: 1678


    FOR iterator IN 1..trunc((length(v_insert_stmt)+1499)/1500) LOOP
      FEM_ENGINES_PKG.Tech_Message
      (p_severity => pc_log_level_statement,
       p_module   => v_module,
       p_app_name => 'FEM',
       p_msg_name => 'FEM_GL_POST_204',
       p_token1   => 'VAR_NAME',
       p_value1   => 'v_insert_stmt: ' || iterator,
       p_token2   => 'VAR_VAL',
       p_value2   => substr(v_insert_stmt, iterator*1500-1499, 1500));

Line: 1690


    EXECUTE IMMEDIATE v_insert_stmt;

Line: 1695


      p_msg_text => 'Inserted ' || TO_CHAR(SQL%ROWCOUNT) ||
                    ' rows into FEM_INTG_DELTA_LOADS');

Line: 1702


'USING (SELECT param_in.output_dataset_code,' || pv_nl ||
'              param_in.cal_period_id,' || pv_nl ||
'              flex.flex_value' || pv_nl ||
'       FROM   FEM_INTG_EXEC_PARAMS_GT param_in,' || pv_nl ||
'              (' || v_flex_query_stmt || ') flex' || pv_nl ||
'       WHERE  param_in.error_code IS NULL' || pv_nl ||
'       AND    param_in.request_id IS NOT NULL' || pv_nl ||
'       AND    param_in.load_method_code = ''S'') param' || pv_nl ||
'ON (dl.ledger_id = ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || ' AND' || pv_nl ||
'    dl.dataset_code = param.output_dataset_code AND' || pv_nl ||
'    dl.cal_period_id = param.cal_period_id AND' || pv_nl ||
'    dl.balance_seg_value = param.flex_value AND' || pv_nl ||
'    dl.loaded_flag = ''Y'')' || pv_nl ||
'WHEN MATCHED THEN' || pv_nl ||
'  UPDATE SET dl.delta_run_id = ' || FEM_GL_POST_PROCESS_PKG.pv_max_delta_run_id || ',' || pv_nl ||
'             dl.last_update_date = sysdate,' || pv_nl ||
'             dl.last_updated_by = ' || FEM_GL_POST_PROCESS_PKG.pv_user_id || ',' || pv_nl ||
'             dl.last_update_login = ' || FEM_GL_POST_PROCESS_PKG.pv_login_id || pv_nl ||
'WHEN NOT MATCHED THEN' || pv_nl ||
'  INSERT(LEDGER_ID, DATASET_CODE, CAL_PERIOD_ID, DELTA_RUN_ID, ' ||
'BALANCE_SEG_VALUE, LOADED_FLAG, CREATION_DATE, CREATED_BY, ' ||
'LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN)' || pv_nl ||
'  VALUES' || pv_nl ||
'  (' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || ',' || pv_nl ||
'   param.output_dataset_code,' || pv_nl ||
'   param.cal_period_id,' || pv_nl ||
'   ' || FEM_GL_POST_PROCESS_PKG.pv_max_delta_run_id || ',' || pv_nl ||
'   param.flex_value,' || pv_nl ||
'   ''Y'',' || pv_nl ||
'   sysdate,' || pv_nl ||
'   ' || FEM_GL_POST_PROCESS_PKG.pv_user_id || ',' || pv_nl ||
'   sysdate,' || pv_nl ||
'   ' || FEM_GL_POST_PROCESS_PKG.pv_user_id || ',' || pv_nl ||
'   ' || FEM_GL_POST_PROCESS_PKG.pv_login_id || ')';

Line: 1758


    v_update_stmt :=
'UPDATE fem_intg_delta_loads dl' || pv_nl ||
'SET    delta_run_id = ' || FEM_GL_POST_PROCESS_PKG.pv_max_delta_run_id || ',' || pv_nl ||
'       last_update_date = sysdate,' || pv_nl ||
'       last_updated_by = ' || FEM_GL_POST_PROCESS_PKG.pv_user_id || ',' || pv_nl ||
'       last_update_login = ' || FEM_GL_POST_PROCESS_PKG.pv_login_id || pv_nl ||
'WHERE  dl.ledger_id = ' || FEM_GL_POST_PROCESS_PKG.pv_ledger_id || pv_nl ||
'AND    dl.loaded_flag = ''Y''' || pv_nl;

Line: 1768


      v_update_stmt := v_update_stmt ||
'AND    dl.balance_seg_value BETWEEN ' || p_bsv_range_low || ' AND ' ||
p_bsv_range_high || pv_nl;

Line: 1773


    v_update_stmt := v_update_stmt ||
'AND    (dataset_code, cal_period_id) IN' || pv_nl ||
'       (SELECT param.output_dataset_code,' || pv_nl ||
'               param.cal_period_id' || pv_nl ||
'        FROM   fem_intg_exec_params_gt param' || pv_nl ||
'        WHERE  param.error_code IS NULL' || pv_nl ||
'        AND    param.request_id IS NOT NULL' || pv_nl ||
'        AND    param.load_method_code = ''I'')';

Line: 1783


    FOR iterator IN 1..trunc((length(v_update_stmt)+1499)/1500) LOOP
      FEM_ENGINES_PKG.Tech_Message
      (p_severity => pc_log_level_statement,
       p_module   => v_module,
       p_app_name => 'FEM',
       p_msg_name => 'FEM_GL_POST_204',
       p_token1   => 'VAR_NAME',
       p_value1   => 'v_update_stmt: ' || iterator,
       p_token2   => 'VAR_VAL',
       p_value2   => substr(v_update_stmt, iterator*1500-1499, 1500));

Line: 1795


    EXECUTE IMMEDIATE v_update_stmt;

Line: 1800


      p_msg_text => 'Updated ' || TO_CHAR(SQL%ROWCOUNT) ||
                    ' rows into FEM_INTG_DELTA_LOADS');