[Home] [Help]
1034: ,x_dimension_rec => x_request_rec.dimension_rec
1035: );
1036:
1037: ------------------------------------------------------------------------------
1038: -- Check that FEM_BALANCES has the ABM_LEDGER table classification.
1039: ------------------------------------------------------------------------------
1040: -- Check added with bug 4510785
1041: begin
1042: select 'Y'
1042: select 'Y'
1043: into l_dummy_varchar
1044: from fem_table_class_assignmt_v
1045: where table_classification_code = 'ABM_LEDGER'
1046: and table_name = 'FEM_BALANCES';
1047: exception
1048: when no_data_found then
1049: FEM_ENGINES_PKG.User_Message (
1050: p_app_name => G_FEM
1049: FEM_ENGINES_PKG.User_Message (
1050: p_app_name => G_FEM
1051: ,p_msg_name => G_NO_TABLE_CLASS_ERR
1052: ,p_token1 => 'TABLE_NAME'
1053: ,p_value1 => 'FEM_BALANCES'
1054: ,p_token2 => 'TABLE_CLASSIFICATION'
1055: ,p_value2 => Get_Lookup_Meaning('FEM_TABLE_CLASSIFICATION_DSC','ABM_LEDGER')
1056: );
1057: raise l_request_prep_error;
1057: raise l_request_prep_error;
1058: end;
1059:
1060: ------------------------------------------------------------------------------
1061: -- Validate the Processing Key on FEM_BALANCES to make sure that it can
1062: -- handle rollup processing on the appropriate composite dimension.
1063: ------------------------------------------------------------------------------
1064: -- Validation added with bug 4475839
1065: FEM_SETUP_PKG.Validate_Proc_Key (
1070: ,x_return_status => l_return_status
1071: ,x_msg_count => l_msg_count
1072: ,x_msg_data => l_msg_data
1073: ,p_dimension_varchar_label => l_dimension_varchar_label
1074: ,p_table_name => 'FEM_BALANCES'
1075: );
1076:
1077: if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1078: Get_Put_Messages (
1160: raise l_request_prep_error;
1161: end;
1162:
1163: ------------------------------------------------------------------------------
1164: -- Call the Where Clause Generator for source data in FEM_BALANCES
1165: ------------------------------------------------------------------------------
1166: FEM_DS_WHERE_CLAUSE_GENERATOR.FEM_Gen_DS_WClause_PVT (
1167: p_api_version => 1.0
1168: ,p_init_msg_list => FND_API.G_FALSE
1171: ,x_msg_count => l_msg_count
1172: ,x_msg_data => l_msg_data
1173: ,p_ds_io_def_id => x_request_rec.dataset_grp_obj_def_id
1174: ,p_output_period_id => x_request_rec.output_cal_period_id
1175: ,p_table_name => 'FEM_BALANCES'
1176: ,p_table_alias => 'B'
1177: ,p_ledger_id => x_request_rec.ledger_id
1178: ,p_where_clause => x_input_ds_b_where_clause
1179: );
1194: ,p_value1 => x_request_rec.dataset_grp_obj_def_id
1195: ,p_token2 => 'OUTPUT_CAL_PERIOD_ID'
1196: ,p_value2 => x_request_rec.output_cal_period_id
1197: ,p_token3 => 'TABLE_NAME'
1198: ,p_value3 => 'FEM_BALANCES'
1199: ,p_token4 => 'LEDGER_ID'
1200: ,p_value4 => x_request_rec.ledger_id
1201: );
1202: raise l_request_prep_error;
1836: raise l_sql_stmts_prep_error;
1837:
1838: end if;
1839:
1840: --todo: check if any user dims have been reassigned in FEM_BALANCES and that
1841: --are also a component dimension.
1842:
1843: -- First find all the component dimension columns
1844: l_comp_dim_cols_stmt :=
1847: ' ,fem_tab_columns_v cols'||
1848: ' where reqs.'||l_comp_dim_req_col||'_dim_requirement_code is not null'||
1849: ' and reqs.'||l_comp_dim_req_col||'_dim_component_flag = ''Y'''||
1850: ' and reqs.dimension_id is not null'||
1851: ' and cols.table_name = ''FEM_BALANCES'''||
1852: ' and cols.column_name = reqs.column_name'||
1853: ' and cols.dimension_id = reqs.dimension_id';
1854:
1855: open l_comp_dim_cols_csr
1879: close l_comp_dim_cols_csr;
1880:
1881: -- Then find all the dimension columns that are not part of the composite
1882: -- dimension definition, thus making them data dimension columns in
1883: -- FEM_BALANCES
1884: l_comp_dim_cols_stmt :=
1885: ' select reqs.column_name'||
1886: ' ,decode(cols.column_name,props.column_name,''Y'',''N'') as proc_key_flag'||
1887: ' from fem_column_requiremnt_b reqs'||
1888: ' ,fem_tab_columns_v cols'||
1889: ' ,fem_tab_column_prop props'||
1890: ' where reqs.'||l_comp_dim_req_col||'_dim_component_flag = ''N'''||
1891: ' and reqs.dimension_id is not null'||
1892: ' and cols.table_name = ''FEM_BALANCES'''||
1893: ' and cols.column_name = reqs.column_name'||
1894: ' and cols.dimension_id is not null'||
1895: ' and cols.fem_data_type_code = ''DIMENSION'''||
1896: ' and props.table_name (+) = cols.table_name'||
2777: end if;
2778:
2779: ------------------------------------------------------------------------------
2780: -- Set the Temporary Sequence Name for performing Rollup Processing in the
2781: -- FEM_BALANCES table.
2782: ------------------------------------------------------------------------------
2783: x_rule_rec.sequence_name :=
2784: 'fem_ru_'||
2785: to_char(p_request_rec.request_id)||
2986:
2987: -- Build SQL statement for performing the rollup
2988: --todo: precision 38?
2989: x_rollup_parent_stmt :=
2990: ' merge into fem_balances bp'||
2991: ' using ('||
2992: ' select :b_source_system_code as source_system_code'||--new
2993: ' ,:b_currency_code as currency_code'||--new
2994: ' ,b.currency_type_code'||
2995: ' ,parent.cost_object_id'||
2996: p_sql_rec.comp_dim_comp_cols_using||
2997: p_sql_rec.comp_dim_data_cols_using||
2998: ' ,sum(b.xtd_balance_f) xtd_balance_f'||
2999: ' from fem_balances b'||
3000: ' ,fem_cost_objects parent'||
3001: ' where b.cost_object_id = :b_child_id'||
3002: ' and parent.cost_object_id = :b_parent_id'||
3003: ' and b.currency_type_code = ''ENTERED'''||
3063:
3064: x_find_child_chains_stmt :=
3065: ' select distinct created_by_request_id'||
3066: ' ,created_by_object_id'||
3067: ' from fem_balances b'||
3068: ' where b.currency_type_code = ''ENTERED'''||
3069: ' and '||p_input_ds_b_where_clause||
3070: ' and b.cost_object_id = :b_child_id'||
3071: ' and not ('||
3082: ' )';
3083:
3084: x_num_of_input_rows_stmt :=
3085: ' select count(*)'||
3086: ' from fem_balances b'||
3087: ' where b.currency_type_code = ''ENTERED'''||
3088: ' and '||p_input_ds_b_where_clause||
3089: ' and not ('||
3090: ' created_by_request_id = :b_request_id'||
3136: end if;
3137:
3138: -- Build SQL statement for performing the rollup
3139: x_rollup_parent_stmt :=
3140: ' merge into fem_balances bp'||
3141: ' using ('||
3142: ' select :b_source_system_code as source_system_code'||--new
3143: ' ,:b_currency_code as currency_code'||--new
3144: ' ,b.currency_type_code'||
3146: ' ,parent.activity_id'||
3147: p_sql_rec.comp_dim_comp_cols_using||
3148: p_sql_rec.comp_dim_data_cols_using||
3149: ' ,sum(b.xtd_balance_f) xtd_balance_f'||
3150: ' from fem_balances b'||
3151: ' ,fem_activities parent'||
3152: ' where b.activity_id = :b_child_id'||
3153: ' and parent.activity_id = :b_parent_id'||
3154: ' and b.ledger_id = :b_ledger_id'||
3221:
3222: x_find_child_chains_stmt :=
3223: ' select distinct created_by_request_id'||
3224: ' ,created_by_object_id'||
3225: ' from fem_balances b'||
3226: ' where b.ledger_id = :b_ledger_id'||
3227: ' and b.currency_type_code = ''ENTERED'''||
3228: ' and '||l_financial_elem_id_clause||
3229: ' and '||l_line_item_id_clause||
3243: ' )';
3244:
3245: x_num_of_input_rows_stmt :=
3246: ' select count(*)'||
3247: ' from fem_balances b'||
3248: ' where b.ledger_id = :b_ledger_id'||
3249: ' and b.currency_type_code = ''ENTERED'''||
3250: ' and '||l_financial_elem_id_clause||
3251: ' and '||l_line_item_id_clause||
3409: );
3410: raise l_register_rule_error;
3411: end if;
3412:
3413: -- Register the data location for the FEM_BALANCES output table
3414: FEM_DIMENSION_UTIL_PKG.Register_Data_Location (
3415: p_request_id => p_request_rec.request_id
3416: ,p_object_id => p_rule_rec.rollup_obj_id
3417: ,p_table_name => 'FEM_BALANCES'
3413: -- Register the data location for the FEM_BALANCES output table
3414: FEM_DIMENSION_UTIL_PKG.Register_Data_Location (
3415: p_request_id => p_request_rec.request_id
3416: ,p_object_id => p_rule_rec.rollup_obj_id
3417: ,p_table_name => 'FEM_BALANCES'
3418: ,p_ledger_id => p_request_rec.ledger_id
3419: ,p_cal_per_id => p_request_rec.output_cal_period_id
3420: ,p_dataset_cd => p_request_rec.output_dataset_code
3421: ,p_source_cd => p_request_rec.source_system_code
3421: ,p_source_cd => p_request_rec.source_system_code
3422: ,p_load_status => null
3423: );
3424:
3425: -- Register the FEM_BALANCES output table as INSERT.
3426: --
3427: -- NOTE: Eventhough we create output data in FEM_BALANCES through a MERGE
3428: -- statement, we are not updating records from other CREATED_BY_REQUEST_ID
3429: -- and CREATED_BY_OBJECT_ID combinations. We are using the MERGE statement
3423: );
3424:
3425: -- Register the FEM_BALANCES output table as INSERT.
3426: --
3427: -- NOTE: Eventhough we create output data in FEM_BALANCES through a MERGE
3428: -- statement, we are not updating records from other CREATED_BY_REQUEST_ID
3429: -- and CREATED_BY_OBJECT_ID combinations. We are using the MERGE statement
3430: -- to insert or update rows such that:
3431: --
3431: --
3432: -- CREATED_BY_REQUEST_ID = p_request_rec.request_id
3433: -- and CREATED_BY_OBJECT_ID = p_rule_rec.rollup_obj_id
3434: --
3435: -- We must therefore register the FEM_BALANCES output table as INSERT so that
3436: -- Undo will simply delete all output records, rather than zero the balance
3437: -- columns. And since we are registering FEM_BALANCES as INSERT, we do not
3438: -- need to register the updated columns for the Undo functionality.
3439: Register_Table (
3433: -- and CREATED_BY_OBJECT_ID = p_rule_rec.rollup_obj_id
3434: --
3435: -- We must therefore register the FEM_BALANCES output table as INSERT so that
3436: -- Undo will simply delete all output records, rather than zero the balance
3437: -- columns. And since we are registering FEM_BALANCES as INSERT, we do not
3438: -- need to register the updated columns for the Undo functionality.
3439: Register_Table (
3440: p_request_rec => p_request_rec
3441: ,p_rule_rec => p_rule_rec
3438: -- need to register the updated columns for the Undo functionality.
3439: Register_Table (
3440: p_request_rec => p_request_rec
3441: ,p_rule_rec => p_rule_rec
3442: ,p_table_name => 'FEM_BALANCES'
3443: ,p_statement_type => 'INSERT'
3444: );
3445:
3446: -- Register the FEM_RU_NODES_T processing table as INSERT. This is needed
3712: ,p_msg_text => 'BEGIN'
3713: );
3714:
3715: ------------------------------------------------------------------------------
3716: -- Create Temporary Sequence for peforming Rollup Processing in FEM_BALANCES.
3717: ------------------------------------------------------------------------------
3718: begin
3719: -- Temporary sequence is in the default APPS schema as GSCC does not
3720: -- allow hardcoded schemas.
3811: ,p_msg_text => 'BEGIN'
3812: );
3813:
3814: ------------------------------------------------------------------------------
3815: -- Drop Temporary Sequence for peforming Rollup Processing on FEM_BALANCES.
3816: ------------------------------------------------------------------------------
3817: begin
3818: select 'Y'
3819: into l_object_exists_flag
4847:
4848: commit;
4849:
4850: ------------------------------------------------------------------------------
4851: -- STEP 3: Pre-Populate COST_OBJECT_ID or ACTIVITY_ID on FEM_BALANCES
4852: ------------------------------------------------------------------------------
4853: FEM_ENGINES_PKG.Tech_Message (
4854: p_severity => G_LOG_LEVEL_1
4855: ,p_module => G_BLOCK||'.'||l_api_name
4852: ------------------------------------------------------------------------------
4853: FEM_ENGINES_PKG.Tech_Message (
4854: p_severity => G_LOG_LEVEL_1
4855: ,p_module => G_BLOCK||'.'||l_api_name
4856: ,p_msg_text => 'Step 3: Pre-Populate COST_OBJECT_ID or ACTIVITY_ID on FEM_BALANCES'
4857: );
4858:
4859: if (l_dimension_rec.dimension_varchar_label = 'COST_OBJECT') then
4860:
4860:
4861: -- The source table query comes from the list of uncosted nodes in
4862: -- FEM_RU_NODES_T. A join to FEM_COST_OBJECTS is necessary for
4863: -- Populate_Cost_Object_Id to work properly as it needs all the
4864: -- component dimension columns for the join on FEM_BALANCES.
4865: l_source_table_query_stmt :=
4866: ' select co.cost_object_id'||
4867: ' from fem_cost_objects co'||
4868: ' ,fem_ru_nodes_t n'||
4886: ,p_source_table_query => l_source_table_query_stmt
4887: ,p_source_table_query_param1 => l_source_table_query_param1
4888: ,p_source_table_query_param2 => l_source_table_query_param2
4889: ,p_source_table_alias => 'co'
4890: ,p_target_table_name => 'FEM_BALANCES'
4891: ,p_target_table_alias => 'b'
4892: ,p_target_dsg_where_clause => p_input_ds_b_where_clause
4893: );
4894:
4898:
4899: -- If a condition exists, the source table query comes from the list of
4900: -- uncosted nodes in FEM_RU_NODES_T. A join to FEM_ACTIVITIES is
4901: -- necessary for Populate_Activity_Id to work properly as it needs all the
4902: -- component dimension columns for the join on FEM_BALANCES.
4903: l_source_table_query_stmt :=
4904: ' select act.activity_id'||
4905: ' from fem_activities act'||
4906: ' ,fem_ru_nodes_t n'||
4917: -- If no condition exists, the source table query comes from all the
4918: -- nodes that exist in the Activity hierarchy, including the root node.
4919: -- A join to FEM_ACTIVITIES is necessary for Populate_Activity_Id to work
4920: -- properly as it needs all the component dimension columns for the join
4921: -- on FEM_BALANCES.
4922: l_source_table_query_stmt :=
4923: ' select act.activity_id'||
4924: ' from fem_activities act'||
4925: ' ,'||p_rule_rec.hier_rollup_table||' h'||
4944: ,p_source_table_query => l_source_table_query_stmt
4945: ,p_source_table_query_param1 => l_source_table_query_param1
4946: ,p_source_table_query_param2 => l_source_table_query_param2
4947: ,p_source_table_alias => 'act'
4948: ,p_target_table_name => 'FEM_BALANCES'
4949: ,p_target_table_alias => 'b'
4950: ,p_target_dsg_where_clause => p_input_ds_b_where_clause
4951: ,p_ledger_id => p_request_rec.ledger_id
4952: ,p_statistic_basis_id => p_rule_rec.statistic_basis_id
5318: | of a rollup hierarchy.
5319: |
5320: | Rollup processing is done by querying for all the child nodes that exist
5321: | for a the specified parent node and parent depth. For each child node,
5322: | we rollup all the data records in FEM_BALANCES to the parent node.
5323: |
5324: | If the track events flag is set, then we must register the chain
5325: | dependency of all the child node data records in FEM_BALANCES with respect
5326: | to the rollup parent node data records in FEM_BALANCES.
5321: | for a the specified parent node and parent depth. For each child node,
5322: | we rollup all the data records in FEM_BALANCES to the parent node.
5323: |
5324: | If the track events flag is set, then we must register the chain
5325: | dependency of all the child node data records in FEM_BALANCES with respect
5326: | to the rollup parent node data records in FEM_BALANCES.
5327: |
5328: | For cost object hierarchies, special processing is necessary to handle
5329: | cross ledger child nodes. A cross ledger may have a functional currency
5322: | we rollup all the data records in FEM_BALANCES to the parent node.
5323: |
5324: | If the track events flag is set, then we must register the chain
5325: | dependency of all the child node data records in FEM_BALANCES with respect
5326: | to the rollup parent node data records in FEM_BALANCES.
5327: |
5328: | For cost object hierarchies, special processing is necessary to handle
5329: | cross ledger child nodes. A cross ledger may have a functional currency
5330: | code that differs from the request's ledger currency code. If that's the
5328: | For cost object hierarchies, special processing is necessary to handle
5329: | cross ledger child nodes. A cross ledger may have a functional currency
5330: | code that differs from the request's ledger currency code. If that's the
5331: | case, the appropriate exchange rate must be used for rolling up all the
5332: | child node's data records in FEM_BALANCES to the parent node.
5333: |
5334: | SCOPE - PRIVATE
5335: |
5336: +============================================================================*/
5611:
5612: end if;
5613:
5614: --------------------------------------------------------------------------
5615: -- STEP 5: Rollup to the specified parent all data records in FEM_BALANCES
5616: -- of the specified child
5617: --------------------------------------------------------------------------
5618: FEM_ENGINES_PKG.Tech_Message (
5619: p_severity => G_LOG_LEVEL_1
6091: if (p_exec_status_code = G_EXEC_STATUS_SUCCESS) then
6092:
6093: ----------------------------------------------------------------------------
6094: -- STEP 2: If a successful object execution, update number of input rows in
6095: -- FEM_BALANCES before purging FEM_RU_NODES_T.
6096: ----------------------------------------------------------------------------
6097: FEM_ENGINES_PKG.Tech_Message (
6098: p_severity => G_LOG_LEVEL_1
6099: ,p_module => G_BLOCK||'.'||l_api_name
6235: );
6236:
6237: select count(*)
6238: into l_num_of_output_rows
6239: from fem_balances
6240: where dataset_code = p_request_rec.output_dataset_code
6241: and cal_period_id = p_request_rec.output_cal_period_id
6242: and created_by_request_id = p_request_rec.request_id
6243: and created_by_object_id = p_rule_rec.rollup_obj_id
6242: and created_by_request_id = p_request_rec.request_id
6243: and created_by_object_id = p_rule_rec.rollup_obj_id
6244: and ledger_id = p_request_rec.ledger_id;
6245:
6246: -- Unregister the data location for the FEM_BALANCES output table if no
6247: -- output rows were created.
6248: if (l_num_of_output_rows = 0) then
6249:
6250: FEM_DIMENSION_UTIL_PKG.Unregister_Data_Location (
6253: );
6254:
6255: end if;
6256:
6257: -- Set the number of output rows for the FEM_BALANCES output table.
6258: FEM_PL_PKG.Update_Num_Of_Output_Rows (
6259: p_api_version => 1.0
6260: ,p_commit => FND_API.G_FALSE
6261: ,p_request_id => p_request_rec.request_id
6259: p_api_version => 1.0
6260: ,p_commit => FND_API.G_FALSE
6261: ,p_request_id => p_request_rec.request_id
6262: ,p_object_id => p_rule_rec.rollup_obj_id
6263: ,p_table_name => 'FEM_BALANCES'
6264: ,p_statement_type => 'INSERT'
6265: ,p_num_of_output_rows => l_num_of_output_rows
6266: ,p_user_id => p_request_rec.user_id
6267: ,p_last_update_login => p_request_rec.login_id