PACKAGE BODY: APPS.HRI_APL_MV_REFRESH

Source

1 PACKAGE BODY hri_apl_mv_refresh AS
2 /* $Header: hrirsgapi.pkb 120.0 2006/02/07 03:09:28 jtitmas noship $ */
3 
4 -- -----------------------------------------------------------------------------
5 -- The procedure in this package drop and recreate the indexes depending
6 -- upon the mode in which it is called
7 -------------------------------------------------------------------------------
8 PROCEDURE custom_api(p_param IN OUT NOCOPY BIS_BIA_RSG_PARAMETER_TBL) IS
9 
10  l_api_type             VARCHAR2(300);
11  l_object_name          VARCHAR2(300);
12  l_mode                 VARCHAR2(300);
13  l_schema_name          VARCHAR2(300);
14 
15 BEGIN
16 
17   -- Retrieve parameters
18   l_api_type := BIS_BIA_RSG_CUSTOM_API_MGMNT.GET_PARAM
19                  (p_parameter_tbl => p_param,
20                   p_param_name    => BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_API_TYPE);
21   l_object_name := BIS_BIA_RSG_CUSTOM_API_MGMNT.GET_PARAM
22                     (p_parameter_tbl => p_param,
23                      p_param_name    => BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_OBJECT_NAME);
24   l_mode := BIS_BIA_RSG_CUSTOM_API_MGMNT.GET_PARAM
25              (p_parameter_tbl => p_param,
26               p_param_name    => BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_MODE);
27 
28   -- Select the user to find out the schema where the  materialized view exists, since
29   -- the materialized views are placed in the same schema as this package
30   SELECT user
31   INTO l_schema_name
32   FROM sys.dual;
33 
34   -- Call APIs based on context
35   IF l_api_type = 'MV_INDEX_MGT' THEN
36 
37     -- Check call mode before/after MV refresh
38     IF l_mode = 'BEFORE' THEN
39 
40       -- Log and drop indexes before MV refresh
41       bis_bia_rsg_custom_api_mgmnt.log('Before dropping indexes');
42       hri_utl_ddl.log_and_drop_indexes
43        (p_application_short_name => l_schema_name,
44         p_table_name             => l_object_name,
45         p_table_owner            => l_schema_name,
46         p_index_excptn_lst       => null);
47       bis_bia_rsg_custom_api_mgmnt.log('Dropped indexes');
48 
49     ELSIF (l_mode = 'AFTER') THEN
50 
51       -- Recreate indexes after MV refresh
52       bis_bia_rsg_custom_api_mgmnt.log('Before recreating indexes');
53       hri_utl_ddl.recreate_indexes
54        (p_application_short_name => l_schema_name,
55         p_table_name             => l_object_name,
56         p_table_owner            => l_schema_name);
57       bis_bia_rsg_custom_api_mgmnt.log('Recreated indexes');
58 
59     END IF;
60 
61   END IF;
62 
63 EXCEPTION WHEN OTHERS THEN
64 
65   -- Write error message to log
66   IF l_mode = 'BEFORE' THEN
67     bis_bia_rsg_custom_api_mgmnt.log('Error in dropping indexes');
68     bis_bia_rsg_custom_api_mgmnt.log(SQLERRM);
69   ELSIF l_mode = 'AFTER' THEN
70     bis_bia_rsg_custom_api_mgmnt.log('Error in recreating indexes');
71     bis_bia_rsg_custom_api_mgmnt.log(SQLERRM);
72   END IF;
73 
74   -- Pass on exception
75   RAISE;
76 
77 END custom_api;
78 
79 END hri_apl_mv_refresh;