eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.OZF_BASELINESALES_LIFT_PVT

Source


1 PACKAGE BODY OZF_BASELINESALES_LIFT_PVT AS
2 /*$Header: ozfvbslb.pls 120.1 2005/09/09 12:17 mkothari noship $*/
3 
4  OZF_DEBUG_HIGH_ON   CONSTANT BOOLEAN     := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
5  OZF_DEBUG_MEDIUM_ON CONSTANT BOOLEAN     := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
6  OZF_DEBUG_LOW_ON    CONSTANT BOOLEAN     := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
7 
8 -- ------------------------
9 -- Public Procedures
10 -- ------------------------
11 
12 -- ------------------------------------------------------------------
13 -- Name: START_PURGE
14 -- Desc: Program to Purge Baseline Sales and Promotional Lift Factor Data
15 -- -----------------------------------------------------------------
16 PROCEDURE START_PURGE
17               (
18                 ERRBUF            OUT  NOCOPY VARCHAR2,
19                 RETCODE           OUT  NOCOPY NUMBER,
20                 p_data_source     IN VARCHAR2,
21                 p_data_type       IN VARCHAR2,
22                 p_curr_or_hist    IN VARCHAR2,
23                 p_record_type     IN VARCHAR2,
24                 p_as_of_date      IN VARCHAR2
25 	       )
26 IS
27     l_api_name                CONSTANT VARCHAR2(30) := 'START_PURGE';
28     l_full_name               CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
29     x_msg_count               NUMBER;
30     x_msg_data                VARCHAR2(240);
31     x_return_status           VARCHAR2(1);
32     l_init_msg_list           VARCHAR2(10)  := FND_API.G_FALSE;
33     l_del_tbl_stmt1           VARCHAR2(100) := ' DELETE FROM ';
34     l_del_err_tbl_stmt1       VARCHAR2(250) :=
35             ' DELETE FROM OZF_INTERFACE_ERRORS ERR '
36          || ' WHERE ERR.ENTITY_NAME = :1 '
37          || ' AND ERR.ENTITY_ROW_ID IN (SELECT ';
38     l_from_clause          VARCHAR2(10) := ' FROM ';
39     l_del_err_tbl_stmt3   VARCHAR2(100) := ' WHERE DATA_SOURCE = :2 ';
40     l_del_err_tbl_stmt4   VARCHAR2(100) := ' AND STATUS_FLAG = :3) ';
41     l_del_int_tbl_stmt3   VARCHAR2(100) := ' WHERE DATA_SOURCE = :1 ';
42     l_del_int_tbl_stmt4   VARCHAR2(100) := ' AND STATUS_FLAG = :2 ';
43     l_del_his_tbl_stmt3   VARCHAR2(100) := ' WHERE DATA_SOURCE = :1 AND HISTORY_SNAPSHOT_DATE <= :2 ';
44     l_sql_stmt      VARCHAR2(1000);
45     l_entity_name   VARCHAR2(30);
46     l_column_name   VARCHAR2(30);
47     l_status_flag   VARCHAR2(1);
48     l_as_of_date    DATE;
49     l_header_rows   NUMBER;
50     l_error_rows   NUMBER;
51 BEGIN
52 
53 	ozf_utility_pvt.write_conc_log(' Start: Private API: ' || l_full_name || ' (-)');
54 	ozf_utility_pvt.write_conc_log(' -- Start Purging Program at : ' ||
55 	                                 to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
56 
57 	SAVEPOINT START_PURGE;
58 
59 	-- Initialize API return status to SUCCESS
60 	x_return_status := FND_API.G_RET_STS_SUCCESS;
61 
62 	ozf_utility_pvt.write_conc_log('-- p_data_source is      : ' || p_data_source ) ;
63 	ozf_utility_pvt.write_conc_log('-- p_data_type is        : ' || p_data_type ) ;
64 	ozf_utility_pvt.write_conc_log('-- p_curr_or_hist is     : ' || p_curr_or_hist ) ;
65 	ozf_utility_pvt.write_conc_log('-- p_record_type is      : ' || p_record_type ) ;
66 	ozf_utility_pvt.write_conc_log('-- p_as_of_date is       : ' || p_as_of_date ) ;
67 
68 	-- check if data_source is null
69 	IF p_data_source is NULL THEN
70 	   FND_MESSAGE.set_name('OZF', 'OZF_BASE_DATA_SRC_MISSING');
71 	   FND_MSG_PUB.add;
72 	   ozf_utility_pvt.write_conc_log(FND_MESSAGE.Get);
73 	   RAISE FND_API.g_exc_error;
74 	END IF;
75 
76 	-- check if data_type is null
77 	IF p_data_type is NULL THEN
78 	   FND_MESSAGE.set_name('OZF', 'OZF_BASE_DATA_TYPE_MISSING');
79 	   FND_MSG_PUB.add;
80 	   ozf_utility_pvt.write_conc_log(FND_MESSAGE.Get);
81 	   RAISE FND_API.g_exc_error;
82 	END IF;
83 
84 	-- check if p_curr_or_hist is null
85 	IF p_curr_or_hist is NULL THEN
86 	   FND_MESSAGE.set_name('OZF', 'OZF_BASE_CURR_OR_HIST_MISSING');
87 	   FND_MSG_PUB.add;
88 	   ozf_utility_pvt.write_conc_log(FND_MESSAGE.Get);
89 	   RAISE FND_API.g_exc_error;
90 	END IF;
91 
92 	-- check if purging current data and p_record_type is null
93 	IF p_curr_or_hist = 'CURR_DATA' AND p_record_type is NULL THEN
94 	   FND_MESSAGE.set_name('OZF', 'OZF_BASE_RECORD_TYPE_MISSING');
95 	   FND_MSG_PUB.add;
96 	   ozf_utility_pvt.write_conc_log(FND_MESSAGE.Get);
97 	   RAISE FND_API.g_exc_error;
98 	END IF;
99 
100 	-- check if purging historical data and p_as_of_date is null
101 	IF p_curr_or_hist = 'HIST_DATA' AND p_as_of_date is NULL THEN
102 	   FND_MESSAGE.set_name('OZF', 'OZF_BASE_AS_OF_DATE_MISSING');
103 	   FND_MSG_PUB.add;
104 	   ozf_utility_pvt.write_conc_log(FND_MESSAGE.Get);
105 	   RAISE FND_API.g_exc_error;
106 	END IF;
107 
108 
109 	----------------------------------------------------------
110 	-- Process Current Data
111 	----------------------------------------------------------
112 	IF p_curr_or_hist = 'CURR_DATA' THEN
113 
114 	  IF p_data_type = 'BASELINE_SALES' THEN
115 	     l_entity_name := 'OZF_BASELINE_SALES_INTERFACE';
116 	     l_column_name := 'BASELINE_SALES_INTERFACE_ID';
117 	  ELSIF p_data_type = 'LIFT_FACTORS' THEN
118 	     l_entity_name := 'OZF_LIFT_FACTORS_INTERFACE';
119 	     l_column_name := 'LIFT_FACTORS_INTERFACE_ID';
120 	  END IF;
121 
122 	  IF p_record_type = 'ERR_REC' THEN
123 	     l_status_flag := 'E';
124 	     l_sql_stmt := l_del_err_tbl_stmt1 || l_column_name || l_from_clause || l_entity_name
125 	                || l_del_err_tbl_stmt3 || l_del_err_tbl_stmt4;
126 	     IF OZF_DEBUG_HIGH_ON THEN
127 		ozf_utility_pvt.write_conc_log ('-- l_sql_stmt = '||l_sql_stmt);
128 	     END IF;
129 	     EXECUTE IMMEDIATE l_sql_stmt USING l_entity_name, p_data_source, l_status_flag;
130 	     l_error_rows := sql%rowcount;
131 	     ozf_utility_pvt.write_conc_log ('-- Purged '|| to_char(l_error_rows)||' error records in error table for entity = '
132 						  ||l_entity_name||' for Data Source ='
133 						  ||p_data_source ||';');
134 
135 	     l_sql_stmt := null;
136 	     l_sql_stmt := l_del_tbl_stmt1 || l_entity_name || l_del_int_tbl_stmt3 || l_del_int_tbl_stmt4;
137 	     IF OZF_DEBUG_HIGH_ON THEN
138 		ozf_utility_pvt.write_conc_log ('-- l_sql_stmt = '||l_sql_stmt);
139 	     END IF;
140 	     EXECUTE IMMEDIATE l_sql_stmt USING p_data_source, l_status_flag;
141 	     l_header_rows := sql%rowcount;
142 	     ozf_utility_pvt.write_conc_log ('-- Purged '|| to_char(l_header_rows)||' error records in interface table = '
143 						  ||l_entity_name||' for Data Source = '
144 						  ||p_data_source ||';');
145 
146 	  ELSIF p_record_type = 'S_P_REC' THEN
147 	     l_status_flag := 'P';
148 	     l_sql_stmt := l_del_err_tbl_stmt1 || l_column_name || l_from_clause || l_entity_name
149 	                || l_del_err_tbl_stmt3 || l_del_err_tbl_stmt4;
150 	     IF OZF_DEBUG_HIGH_ON THEN
151 		ozf_utility_pvt.write_conc_log ('-- l_sql_stmt = '||l_sql_stmt);
152 	     END IF;
153 	     EXECUTE IMMEDIATE l_sql_stmt USING l_entity_name, p_data_source, l_status_flag;
154 	     l_error_rows := sql%rowcount;
155 	     ozf_utility_pvt.write_conc_log ('-- Purged '|| to_char(l_error_rows)||' error records in error table for entity = '
156 						  ||l_entity_name||' for Data Source ='
157 						  ||p_data_source ||';');
158 	     l_sql_stmt := null;
159 	     l_sql_stmt := l_del_tbl_stmt1 || l_entity_name || l_del_int_tbl_stmt3 || l_del_int_tbl_stmt4;
160 	     IF OZF_DEBUG_HIGH_ON THEN
161 		ozf_utility_pvt.write_conc_log ('-- l_sql_stmt = '||l_sql_stmt);
162 	     END IF;
163 	     EXECUTE IMMEDIATE l_sql_stmt USING p_data_source, l_status_flag;
164 	     l_header_rows := sql%rowcount;
165 	     ozf_utility_pvt.write_conc_log ('-- Purged '|| to_char(l_header_rows)||' successfully processed records in interface table = '
166 						  ||l_entity_name||' for Data Source = '
167 						  ||p_data_source ||';');
168 
169 	  ELSIF p_record_type = 'ALL_REC' THEN
170 	     l_sql_stmt := l_del_err_tbl_stmt1 || l_column_name || l_from_clause || l_entity_name
171 	                || l_del_err_tbl_stmt3 || ') ';
172 	     IF OZF_DEBUG_HIGH_ON THEN
173 		ozf_utility_pvt.write_conc_log ('-- l_sql_stmt = '||l_sql_stmt);
174 	     END IF;
175 	     EXECUTE IMMEDIATE l_sql_stmt USING l_entity_name, p_data_source;
176 	     l_error_rows := sql%rowcount;
177 	     ozf_utility_pvt.write_conc_log ('-- Purged '|| to_char(l_error_rows)||' error records in error table for entity = '
178 						  ||l_entity_name||' for Data Source ='
179 						  ||p_data_source ||';');
180 
181 	     l_sql_stmt := null;
182 	     l_sql_stmt := l_del_tbl_stmt1 || l_entity_name || l_del_int_tbl_stmt3;
183 	     IF OZF_DEBUG_HIGH_ON THEN
184 		ozf_utility_pvt.write_conc_log ('-- l_sql_stmt = '||l_sql_stmt);
185 	     END IF;
186 	     EXECUTE IMMEDIATE l_sql_stmt USING p_data_source;
187 	     l_header_rows := sql%rowcount;
188 	     ozf_utility_pvt.write_conc_log ('-- Purged '|| to_char(l_header_rows)||' records in interface table = '
189 						  ||l_entity_name||' for Data Source = '
190 						  ||p_data_source ||';');
191 	  END IF;
192 
193 	----------------------------------------------------------
194 	-- Process Historical Data
195 	----------------------------------------------------------
196 	ELSIF p_curr_or_hist = 'HIST_DATA' THEN
197 
198 	  l_as_of_date := trunc(NVL(FND_DATE.CANONICAL_TO_DATE(p_as_of_date), sysdate));
199 
200 	  IF p_data_type = 'BASELINE_SALES' THEN
201 	     l_entity_name := 'OZF_BASELINE_SALES_HISTORY';
202 	  ELSIF p_data_type = 'LIFT_FACTORS' THEN
203 	     l_entity_name := 'OZF_LIFT_FACTORS_HISTORY';
204 	  END IF;
205           l_sql_stmt := null;
206           l_sql_stmt := l_del_tbl_stmt1 || l_entity_name || l_del_his_tbl_stmt3;
207 	  IF OZF_DEBUG_HIGH_ON THEN
208 	     ozf_utility_pvt.write_conc_log ('-- l_sql_stmt = '||l_sql_stmt);
209 	  END IF;
210 	  EXECUTE IMMEDIATE l_sql_stmt USING p_data_source, l_as_of_date;
211           l_header_rows := sql%rowcount;
212 	  ozf_utility_pvt.write_conc_log ('-- Purged '|| to_char(l_header_rows)||' records of historical data in '
213 	     				||l_entity_name||' for Data Source = '
214 	     			        ||p_data_source ||' up to snapshot date '
215 	     			        ||to_char(l_as_of_date,'DD-MON-YYYY')||' ;');
216 
217 	END IF;
218 
219 
220 	----------------------------------------------------------
221 	-- commit the deleled records
222 	----------------------------------------------------------
223 	ozf_utility_pvt.write_conc_log('-- Commiting Purged Records ');
224 
225 	COMMIT;
226 
227 	ozf_utility_pvt.write_conc_log('-- Committed ');
228 	----------------------------------------------------------
229 
230 	ozf_utility_pvt.write_conc_log(' -- End: Purging Program at : ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
231 	ozf_utility_pvt.write_conc_log('End: Private API: ' || l_full_name || ' (+)');
232 
233 EXCEPTION
234      WHEN FND_API.G_EXC_ERROR THEN
235           ROLLBACK TO START_PURGE;
236 	  x_return_status := FND_API.g_ret_sts_error ;
237           ERRBUF := x_msg_data;
238           RETCODE := 2;
239           ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' Expected Error ' );
240           ozf_utility_pvt.write_conc_log(sqlerrm(sqlcode) );
241 
242      WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
243           ROLLBACK TO START_PURGE;
244 	  x_return_status := FND_API.g_ret_sts_unexp_error ;
245           ERRBUF := sqlerrm(sqlcode);
246           RETCODE := 2;
247           ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' Unexpected Error ' );
248           ozf_utility_pvt.write_conc_log(sqlerrm(sqlcode) );
249 
250      WHEN OTHERS THEN
251           ROLLBACK TO START_PURGE;
252 	  x_return_status := FND_API.g_ret_sts_unexp_error ;
253           ERRBUF  := sqlerrm(sqlcode);
254           RETCODE := 2;
255           ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' Other Error ' );
256           ozf_utility_pvt.write_conc_log(sqlerrm(sqlcode) );
257 
258 END START_PURGE;
259 
260 
261 END OZF_BASELINESALES_LIFT_PVT;