[Home] [Help]
13: g_exec_status_success CONSTANT VARCHAR2(30) := 'SUCCESS';
14:
15: --Constants for output table names being registered with fem_pl_pkg
16: -- API register_table method.
17: g_fem_customer_profit CONSTANT VARCHAR2(30) := 'FEM_CUSTOMER_PROFIT';
18:
19: --constant for sql_stmt_type
20: g_insert CONSTANT VARCHAR2(30) := 'INSERT';
21: g_update CONSTANT VARCHAR2(30) := 'UPDATE';
1575: e_process_undo_rule_error EXCEPTION;
1576: e_register_rule_error EXCEPTION;
1577:
1578: BEGIN
1579: l_process_table := 'FEM_CUSTOMER_PROFIT';
1580: -- Initialize the return status to SUCCESS
1581: p_param_rec.return_status := fnd_api.G_RET_STS_SUCCESS;
1582:
1583: fem_engines_pkg.tech_message ( p_severity => g_log_level_2
1717: ,p_module => G_BLOCK||'.'||l_api_name
1718: ,p_msg_text => 'Aggregate to customer: INSERT');
1719:
1720: Register_Table(p_param_rec => p_param_rec
1721: ,p_tbl_name => g_fem_customer_profit
1722: ,p_num_output_rows => 0
1723: ,p_stmt_type => g_insert);
1724:
1725: ELSIF (p_param_rec.aggregate_customer = 'N' ) THEN
1728: ,p_module => G_BLOCK||'.'||l_api_name
1729: ,p_msg_text => 'Hierarchy Rollup: UPDATE');
1730:
1731: Register_Table(p_param_rec => p_param_rec
1732: ,p_tbl_name => g_fem_customer_profit
1733: ,p_num_output_rows => 0
1734: ,p_stmt_type => g_insert);
1735: END IF;
1736:
1800: To Level');
1801:
1802: --EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
1803:
1804: --SELECT /*+ PARALLEL(fem_customer_profit 4) PARALLEL(hier_cust 2)*/ customer_id
1805:
1806: SELECT customer_id
1807: BULK COLLECT INTO v_rollup_cust
1808: FROM fem_customer_profit fcp,
1804: --SELECT /*+ PARALLEL(fem_customer_profit 4) PARALLEL(hier_cust 2)*/ customer_id
1805:
1806: SELECT customer_id
1807: BULK COLLECT INTO v_rollup_cust
1808: FROM fem_customer_profit fcp,
1809: ((SELECT child_id
1810: FROM fem_customers_hier a
1811: WHERE hierarchy_obj_def_id = l_hier_object_def_id
1812: AND parent_depth_num = l_rel_dim_group_seq_to
1833:
1834: l_update_flag := TRUE;
1835:
1836: FORALL i IN v_rollup_cust.FIRST..v_rollup_cust.LAST
1837: UPDATE fem_customer_profit
1838: SET table_id = -999999
1839: WHERE cal_period_id = p_param_rec.output_cal_period_id
1840: AND ledger_id = p_param_rec.ledger_id
1841: AND dataset_code = p_param_rec.output_dataset_code
1847: l_update_flag := FALSE;
1848:
1849: SELECT customer_id
1850: BULK COLLECT INTO v_rollup_cust
1851: FROM fem_customer_profit fcp,
1852: ((SELECT child_id
1853: FROM fem_customers_hier a
1854: WHERE hierarchy_obj_def_id = l_hier_object_def_id
1855: AND parent_depth_num = l_rel_dim_group_seq_to
2101: Process_Obj_Exec_Step(
2102: p_param_rec => p_param_rec
2103: ,p_exe_step => 'ALL'
2104: ,p_exe_status_code => g_exec_status_success
2105: ,p_tbl_name => g_fem_customer_profit
2106: ,p_num_rows => NVL(l_num_rows_loaded,0));
2107:
2108: IF (l_update_flag AND p_param_rec.aggregate_customer = 'N' ) THEN
2109: fem_engines_pkg.tech_message(
2113:
2114: BEGIN
2115:
2116: FORALL i IN v_rollup_cust.FIRST..v_rollup_cust.LAST
2117: UPDATE fem_customer_profit fcp
2118: SET (FCP.created_by_object_id
2119: ,FCP.created_by_request_id
2120: ,FCP.record_count) =
2121: (SELECT created_by_object_id,created_by_request_id,record_count
2118: SET (FCP.created_by_object_id
2119: ,FCP.created_by_request_id
2120: ,FCP.record_count) =
2121: (SELECT created_by_object_id,created_by_request_id,record_count
2122: FROM fem_customer_profit FCP
2123: WHERE FCP.ledger_id = p_param_rec.ledger_id
2124: AND FCP.source_system_code = p_param_rec.source_system_code
2125: AND FCP.cal_period_id = p_param_rec.output_cal_period_id
2126: AND FCP.dataset_code = p_param_rec.output_dataset_code
2152: p_severity => g_log_level_3
2153: ,p_module => G_BLOCK||'.'||l_api_name
2154: ,p_msg_text => 'Deleting the Duplicate Rows');
2155:
2156: DELETE FROM fem_customer_profit
2157: WHERE table_id = -999999;
2158:
2159: EXCEPTION
2160: WHEN OTHERS THEN
2176: Process_Obj_Exec_Step(
2177: p_param_rec => p_param_rec
2178: ,p_exe_step => 'ALL'
2179: ,p_exe_status_code => g_exec_status_success
2180: ,p_tbl_name => g_fem_customer_profit
2181: ,p_num_rows => 0);
2182:
2183: END IF; -- l_proceed_further
2184:
2195: Process_Obj_Exec_Step(
2196: p_param_rec => p_param_rec
2197: ,p_exe_step => 'ALL'
2198: ,p_exe_status_code => 'ERROR_UNDO'
2199: ,p_tbl_name => g_fem_customer_profit
2200: ,p_num_rows => NULL);
2201:
2202: fem_engines_pkg.tech_message (
2203: p_severity => g_log_level_5
2214: Process_Obj_Exec_Step(
2215: p_param_rec => p_param_rec
2216: ,p_exe_step => 'ALL'
2217: ,p_exe_status_code => g_exec_status_error_rerun
2218: ,p_tbl_name => g_fem_customer_profit
2219: ,p_num_rows => NULL);
2220:
2221: fem_engines_pkg.tech_message (
2222: p_severity => g_log_level_5
2233: Process_Obj_Exec_Step(
2234: p_param_rec => p_param_rec
2235: ,p_exe_step => 'ALL'
2236: ,p_exe_status_code => g_exec_status_error_rerun
2237: ,p_tbl_name => g_fem_customer_profit
2238: ,p_num_rows => NULL);
2239:
2240: fem_engines_pkg.user_message (p_app_name => G_PFT
2241: ,p_msg_name => G_ENG_SINGLE_RULE_ERR);
2342: fem_engines_pkg.tech_message ( p_severity => g_log_level_2
2343: ,p_module => G_BLOCK||'.'||l_api_name
2344: ,p_msg_text => 'Aggregate To Customer');
2345: -- To add the where clause to aggregate all the customer data in
2346: -- FEM_CUSTOMER_PROFIT Table
2347: account_aggregation ( p_src_alias => l_table_alias
2348: ,p_select_col => l_select_stmt
2349: ,p_from_clause => l_from_stmt
2350: ,p_where_clause => l_where_stmt