[Home] [Help]
PACKAGE BODY: APPS.OZF_REFRESH_VIEW_PVT
Source
1 PACKAGE BODY ozf_refresh_view_pvt AS
2 /*$Header: ozfvrfeb.pls 120.1 2005/07/08 03:37:56 appldev ship $*/
3
4 PROCEDURE load (ERRBUF OUT NOCOPY VARCHAR2,
5 RETCODE OUT NOCOPY NUMBER,
6 p_view_name IN VARCHAR2)
7 IS
8 l_api_name CONSTANT VARCHAR2(30) := 'load';
9 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
10 l_api_version CONSTANT NUMBER := 1.0;
11 v_error_code NUMBER;
12 v_error_text VARCHAR2(1500);
13 l_user_id NUMBER := FND_GLOBAL.USER_ID();
14 x_msg_count NUMBER;
15 x_msg_data VARCHAR2(240);
16 x_return_status VARCHAR2(1) ;
17 l_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;
18
19 l_view_name varchar2(30);
20 BEGIN
21 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ': Start');
22
23 -- Initialize API return status to SUCCESS
24 x_return_status := FND_API.G_RET_STS_SUCCESS;
25
26 IF p_view_name = 'EARNING' THEN
27 l_view_name := 'OZF_EARNING_SUMMARY_MV';
28 ELSIF p_view_name = 'INVENTORY' THEN
29 l_view_name := 'OZF_INVENTORY_SUMMARY_MV';
30 ELSIF p_view_name = 'ORDER' THEN
31 l_view_name := 'OZF_ORDER_SALES_SUMRY_MV';
32 ELSIF p_view_name = 'CUSTFUND' THEN
33 l_view_name := 'OZF_CUST_FUND_SUMMARY_MV';
34 END IF;
35
36 ozf_utility_pvt.write_conc_log(' -- Begin Materialized view refresh -- ');
37
38 DBMS_MVIEW.REFRESH(
39 list => l_view_name ,
40 method => '?'
41 );
42
43 ozf_utility_pvt.write_conc_log(' -- End Mataerialized view refresh -- ');
44
45 --------------------------------------------------------
46 -- Gather statistics for the use of cost-based optimizer
47 --------------------------------------------------------
48 ozf_utility_pvt.write_conc_log(' -- Begin FND_STATS API to gather table statstics -- ');
49 fnd_stats.gather_table_stats (ownname=>'APPS', tabname=>l_view_name);
50 ozf_utility_pvt.write_conc_log(' -- End FND_STATS API to gather table statstics -- ');
51
52 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ': End');
53 EXCEPTION
54 WHEN FND_API.G_EXC_ERROR THEN
55 x_return_status := FND_API.g_ret_sts_error ;
56 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
57 p_count => x_msg_count,
58 p_data => x_msg_data);
59 ERRBUF := x_msg_data;
60 RETCODE := 2;
61 ozf_utility_pvt.write_conc_log('Private API: ' || l_api_name || ' Expected Error');
62
63 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
64 x_return_status := FND_API.g_ret_sts_unexp_error ;
65 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
66 p_count => x_msg_count,
67 p_data => x_msg_data);
68 ERRBUF := x_msg_data;
69 RETCODE := 2;
70 ozf_utility_pvt.write_conc_log('Private API: ' || l_api_name || ' Error');
71
72 WHEN OTHERS THEN
73 x_return_status := FND_API.g_ret_sts_unexp_error ;
74 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
75 p_count => x_msg_count,
76 p_data => x_msg_data);
77
78 ERRBUF := sqlerrm(sqlcode);
79 RETCODE := sqlcode;
80 ozf_utility_pvt.write_conc_log('Private API: ' || l_api_name || ' Others');
81
82 END load;
83
84
85 END ozf_refresh_view_pvt;