[Home] [Help]
1843:
1844: l_source_table_query_stmt :=
1845: ' select act.activity_id'||
1846: ' from fem_activities act'||
1847: ' ,pft_ar_driver_values_t drv'||
1848: ' where drv.CREATED_BY_REQUEST_ID = :b_request_id'||
1849: ' and drv.CREATED_BY_OBJECT_ID = :b_act_rate_obj_id'||
1850: ' and drv.activity_id = act.activity_id';
1851:
1997: ' ,b.entity_id'||
1998: ' ,b.intercompany_id'||
1999: ' ,sum(b.xtd_balance_f) / max(abs(drv.driver_value)) as act_rate_value'||
2000: ' from fem_balances b'||
2001: ' ,pft_ar_driver_values_t {{table_partition}} drv'||
2002: ' where b.ledger_id = '||p_request_rec.ledger_id||
2003: ' and b.financial_elem_id not in ('||G_FIN_ELEM_ID_STATISTIC||','||G_FIN_ELEM_ID_ACTIVITY_RATE||')'||
2004: ' and b.currency_type_code = ''ENTERED'''||
2005: ' and '||p_input_ds_b_where_clause||
2142: ' ,b.entity_id'||
2143: ' ,b.intercompany_id'||
2144: ' ,sum(b.xtd_balance_f) / max(abs(drv.driver_value)) as act_rate_value'||
2145: ' from fem_balances b'||
2146: ' ,pft_ar_driver_values_t {{table_partition}} drv'||
2147: ' where b.ledger_id = '||p_request_rec.ledger_id||
2148: ' and b.financial_elem_id not in ('||G_FIN_ELEM_ID_STATISTIC||','||G_FIN_ELEM_ID_ACTIVITY_RATE||')'||
2149: ' and b.currency_type_code = ''ENTERED'''||
2150: ' and '||p_input_ds_b_where_clause||
2206: x_prg_stat => l_mp_prog_status
2207: ,x_exception_code => l_mp_exception_code
2208: ,p_rule_id => p_rule_rec.act_rate_obj_id
2209: ,p_eng_step => L_CALC_ACT_RATE_VALUES
2210: ,p_data_table => 'PFT_AR_DRIVER_VALUES_T'
2211: ,p_eng_sql => l_act_rate_stmt
2212: ,p_table_alias => 'drv'
2213: ,p_run_name => L_CALC_ACT_RATE_VALUES
2214: ,p_eng_prg => null
2294: ' ,''N/A'''||
2295: ' ,b.last_updated_by_object_id'||
2296: ' ,b.last_updated_by_request_id'||
2297: ' from fem_balances b'||
2298: ' ,pft_ar_driver_values_t {{table_partition}} drv'||
2299: ' where b.ledger_id = '||p_request_rec.ledger_id||
2300: ' and b.dataset_code = '||p_request_rec.output_dataset_code||
2301: ' and b.cal_period_id = '||p_request_rec.output_cal_period_id||
2302: ' and b.created_by_request_id = drv.created_by_request_id'||
2324: x_prg_stat => l_mp_prog_status
2325: ,x_exception_code => l_mp_exception_code
2326: ,p_rule_id => p_rule_rec.act_rate_obj_id
2327: ,p_eng_step => L_CALC_ACT_RATE_FACTORS
2328: ,p_data_table => 'PFT_AR_DRIVER_VALUES_T'
2329: ,p_eng_sql => l_calc_fctrs_stmt
2330: ,p_table_alias => 'drv'
2331: ,p_run_name => L_CALC_ACT_RATE_FACTORS
2332: ,p_eng_prg => null
2901: ,p_table_name => 'PFT_AR_DRIVERS_T'
2902: ,p_statement_type => 'INSERT'
2903: );
2904:
2905: -- Register the PFT_AR_DRIVER_VALUES_T processing table as INSERT. This is needed
2906: -- in the event of a engine failure where the only way to purge these records
2907: -- is through the Undo Process
2908: Register_Table (
2909: p_request_rec => p_request_rec
2907: -- is through the Undo Process
2908: Register_Table (
2909: p_request_rec => p_request_rec
2910: ,p_rule_rec => p_rule_rec
2911: ,p_table_name => 'PFT_AR_DRIVER_VALUES_T'
2912: ,p_statement_type => 'INSERT'
2913: );
2914:
2915: commit;
4410: l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
4411: l_rows_loaded := l_rows_loaded + l_rowid_tbl.count;
4412:
4413: ----------------------------------------------------------------------------
4414: -- STEP 5: Build MP SQL Statement for bulk insert into PFT_AR_DRIVER_VALUES_T
4415: ----------------------------------------------------------------------------
4416:
4417: FEM_ENGINES_PKG.Tech_Message (
4418: p_severity => G_LOG_LEVEL_1
4416:
4417: FEM_ENGINES_PKG.Tech_Message (
4418: p_severity => G_LOG_LEVEL_1
4419: ,p_module => G_BLOCK||'.'||L_API_NAME
4420: ,p_msg_text => 'Step 5: Build MP SQL Statement for bulk insert into PFT_AR_DRIVER_VALUES_T'
4421: );
4422:
4423: -- ammittal 11/23/04 - Need to look at where clauses for hierarchy and
4424: -- conditions. Turn Value and Driver table population around?
4424: -- conditions. Turn Value and Driver table population around?
4425:
4426: -- Build tokenized SQL statement
4427: l_ar_driver_values_stmt :=
4428: ' insert into pft_ar_driver_values_t ('||
4429: ' created_by_request_id'||
4430: ' ,created_by_object_id'||
4431: ' ,activity_id'||
4432: ' ,driver_value'||
4475: ,'{{table_partition}}',' PARTITION('||l_part_name||') ');
4476: end if;
4477:
4478: ------------------------------------------------------------------------------
4479: -- STEP 6: Execute MP SQL Statement for bulk insert into PFT_AR_DRIVER_VALUES_T
4480: ------------------------------------------------------------------------------
4481:
4482: FEM_ENGINES_PKG.Tech_Message (
4483: p_severity => G_LOG_LEVEL_1
4481:
4482: FEM_ENGINES_PKG.Tech_Message (
4483: p_severity => G_LOG_LEVEL_1
4484: ,p_module => G_BLOCK||'.'||L_API_NAME
4485: ,p_msg_text => 'Step 6: Execute MP SQL Statement for bulk insert into PFT_AR_DRIVER_VALUES_T'
4486: );
4487:
4488: -- Execute the built SQL statement for bulk insert into PFT_AR_DRIVER_VALUES_T
4489: if (l_num_vals = 4) then
4484: ,p_module => G_BLOCK||'.'||L_API_NAME
4485: ,p_msg_text => 'Step 6: Execute MP SQL Statement for bulk insert into PFT_AR_DRIVER_VALUES_T'
4486: );
4487:
4488: -- Execute the built SQL statement for bulk insert into PFT_AR_DRIVER_VALUES_T
4489: if (l_num_vals = 4) then
4490: execute immediate l_ar_driver_values_stmt
4491: using p_hier_obj_def_id
4492: ,l_slc_val1, l_slc_val2, l_slc_val3, l_slc_val4;
4846: ' and b.created_by_object_id = :b_act_rate_obj_id'||
4847: ' )'||
4848: ' and exists ('||
4849: ' select 1'||
4850: ' from pft_ar_driver_values_t drv'||
4851: ' where drv.created_by_request_id = :b_request_id'||
4852: ' and drv.created_by_object_id = :b_act_rate_obj_id'||
4853: ' and drv.activity_id = b.activity_id'||
4854: ' )'||
5042: );
5043:
5044: ------------------------------------------------------------------------------
5045: -- STEP 2: If a successful object execution, update number of input rows in
5046: -- FEM_BALANCES before purging PFT_AR_DRIVER_VALUES_T.
5047: ------------------------------------------------------------------------------
5048: if (p_exec_status_code = G_EXEC_STATUS_SUCCESS) then
5049:
5050: FEM_ENGINES_PKG.Tech_Message (
5065: ' and b.created_by_object_id = :b_act_rate_obj_id'||
5066: ' )'||
5067: ' and exists ('||
5068: ' select 1'||
5069: ' from pft_ar_driver_values_t drv'||
5070: ' where drv.created_by_request_id = :b_request_id'||
5071: ' and drv.created_by_object_id = :b_act_rate_obj_id'||
5072: ' and drv.activity_id = b.activity_id'||
5073: ' )';
5108: end if;
5109:
5110: if (p_exec_status_code = G_EXEC_STATUS_SUCCESS) then
5111: ----------------------------------------------------------------------------
5112: -- STEP 2.1: Delete all records in the PFT_AR_DRIVER_VALUES_T table
5113: ----------------------------------------------------------------------------
5114: FEM_ENGINES_PKG.Tech_Message (
5115: p_severity => G_LOG_LEVEL_1
5116: ,p_module => G_BLOCK||'.'||L_API_NAME
5113: ----------------------------------------------------------------------------
5114: FEM_ENGINES_PKG.Tech_Message (
5115: p_severity => G_LOG_LEVEL_1
5116: ,p_module => G_BLOCK||'.'||L_API_NAME
5117: ,p_msg_text => 'Step 2.1: Purging Records in PFT_AR_DRIVER_VALUES_T'
5118: );
5119:
5120: delete from pft_ar_driver_values_t
5121: where created_by_request_id = p_request_rec.request_id
5116: ,p_module => G_BLOCK||'.'||L_API_NAME
5117: ,p_msg_text => 'Step 2.1: Purging Records in PFT_AR_DRIVER_VALUES_T'
5118: );
5119:
5120: delete from pft_ar_driver_values_t
5121: where created_by_request_id = p_request_rec.request_id
5122: and created_by_object_id = p_rule_rec.act_rate_obj_id;
5123:
5124: commit;