[Home] [Help]
PACKAGE BODY: APPS.OZF_CHECK_DUP_PROD_PVT
Source
1 PACKAGE BODY OZF_CHECK_DUP_PROD_PVT AS
2 /* $Header: ozfvcdpb.pls 120.5 2006/05/25 23:48:54 julou noship $ */
3
4
5 g_pkg_name CONSTANT VARCHAR2(30):='OZF_CHECK_DUP_PROD_PVT';
6 ---------------------------------------------------------------------
7 -- FUNCTION
8 -- get_sql
9 --
10 -- PURPOSE
11 -- Retrieves SQL statment for given context, attribute.
12 --
13 -- PARAMETERS
14 -- p_context: product or qualifier context.
15 -- p_attribute: context attribute.
16 -- p_attr_value: context attribute value.
17 --
18 -- NOTES
19 -- This functions returns SQL statement for the given context, attribute and attribute value.
20 ---------------------------------------------------------------------
21 FUNCTION get_sql(
22 p_context IN VARCHAR2,
23 p_attribute IN VARCHAR2,
24 p_attr_value IN VARCHAR2
25 )
26 RETURN VARCHAR2
27 IS
28 CURSOR c_get_sql IS
29 SELECT sql_validation_1,
30 sql_validation_2,
31 sql_validation_3,
32 sql_validation_4,
33 sql_validation_5,
34 sql_validation_6,
35 sql_validation_7,
36 sql_validation_8,
37 condition_id_column
38 FROM ozf_denorm_queries
39 WHERE context = p_context
40 AND attribute = p_attribute
41 AND active_flag = 'Y'
42 AND last_update_date =
43 (
44 SELECT MAX(last_update_date)
45 FROM ozf_denorm_queries
46 WHERE context = p_context
47 AND attribute = p_attribute
48 AND active_flag = 'Y'
49 );
50
51 l_stmt VARCHAR2(32000) := NULL;
52 l_stmt_1 VARCHAR2(4000) := NULL;
53 l_stmt_2 VARCHAR2(4000) := NULL;
54 l_stmt_3 VARCHAR2(4000) := NULL;
55 l_stmt_4 VARCHAR2(4000) := NULL;
56 l_stmt_5 VARCHAR2(4000) := NULL;
57 l_stmt_6 VARCHAR2(4000) := NULL;
58 l_stmt_7 VARCHAR2(4000) := NULL;
59 l_stmt_8 VARCHAR2(4000) := NULL;
60 l_cond_id VARCHAR2(40);
61 BEGIN
62
63 OPEN c_get_sql;
64 FETCH c_get_sql INTO l_stmt_1, l_stmt_2,l_stmt_3, l_stmt_4, l_stmt_5, l_stmt_6, l_stmt_7, l_stmt_8, l_cond_id;
65 CLOSE c_get_sql;
66 -- special case, for item only.
67 IF l_stmt_1 is not null then
68 IF INSTR(l_stmt_1, '*') > 0 OR INSTR(l_stmt_1, '?') > 0 THEN
69 FND_DSQL.add_text('SELECT TO_NUMBER(');
70 FND_DSQL.add_bind(p_attr_value);
71 FND_DSQL.add_text(') product_id,''PRICING_ATTRIBUTE1'' product_type FROM DUAL');
72 ELSE
73 FND_DSQL.add_text(' ' || l_stmt_1);
74 END IF;
75
76 FND_DSQL.add_text(' ' || l_stmt_2);
77 FND_DSQL.add_text(' ' || l_stmt_3);
78 FND_DSQL.add_text(' ' || l_stmt_4);
79 FND_DSQL.add_text(' ' || l_stmt_5);
80 FND_DSQL.add_text(' ' || l_stmt_6);
81 FND_DSQL.add_text(' ' || l_stmt_7);
82 FND_DSQL.add_text(' ' || l_stmt_8);
83
84 IF l_cond_id IS NOT NULL THEN
85 IF INSTR(UPPER(l_stmt_1),'WHERE') > 0 OR INSTR(UPPER(l_stmt_2),'WHERE') > 0
86 OR INSTR(UPPER(l_stmt_3),'WHERE') > 0 OR INSTR(UPPER(l_stmt_4),'WHERE') > 0
87 OR INSTR(UPPER(l_stmt_5),'WHERE') > 0 OR INSTR(UPPER(l_stmt_6),'WHERE') > 0
88 OR INSTR(UPPER(l_stmt_7),'WHERE') > 0 OR INSTR(UPPER(l_stmt_8),'WHERE') > 0 THEN
89 FND_DSQL.add_text(' AND ');
90 FND_DSQL.add_text(l_cond_id);
91 FND_DSQL.add_text(' = ');
92 FND_DSQL.add_bind(p_attr_value);
93 ELSE -- no WHERE clause, need to add WHERE
94 FND_DSQL.add_text(' WHERE ');
95 FND_DSQL.add_text(l_cond_id);
96 FND_DSQL.add_text(' = ');
97 FND_DSQL.add_bind(p_attr_value);
98 END IF;
99 END IF;
100 l_stmt := FND_DSQL.get_text(FALSE);
101 ELSE
102 l_stmt := NULL;
103 END IF;
104
105 RETURN l_stmt;
106 END get_sql;
107
108
109 --------------------------------------------------------------------
110 -- PROCEDURE
111 -- denorm_vo_products
112 --
113 -- PURPOSE
114 -- Refreshes volume offer product denorm table ozf_vo_products_temp.
115 --
116 -- PARAMETERS
117 -- p_offer_id: identifier of the offer.
118 --
119 -- DESCRIPTION
120 -- This procedure calls get_sql, builds SQL statment for product.
121 ----------------------------------------------------------------------
122 PROCEDURE denorm_vo_products(
123 p_api_version IN NUMBER,
124 p_init_msg_list IN VARCHAR2,
125 p_commit IN VARCHAR2,
126 p_offer_id IN NUMBER,
127 x_return_status OUT NOCOPY VARCHAR2,
128 x_msg_count OUT NOCOPY NUMBER,
129 x_msg_data OUT NOCOPY VARCHAR2,
130 x_product_stmt OUT NOCOPY VARCHAR2
131 )
132 IS
133
134 CURSOR c_pbh_lines IS
135 SELECT offer_discount_line_id
136 FROM ozf_offer_discount_lines
137 WHERE offer_id = p_offer_id
138 AND tier_type = 'PBH'
139 AND tier_level = 'HEADER';
140
141 CURSOR c_pbh_lines_count IS
142 SELECT COUNT(*)
143 FROM ozf_offer_discount_lines
144 WHERE offer_id = p_offer_id
145 AND tier_type = 'PBH'
146 AND tier_level = 'HEADER';
147
148 CURSOR c_products(p_pbh_line_id NUMBER, p_excluder_flag VARCHAR2) IS
149 SELECT product_context,
150 product_attribute,
151 product_attr_value
152 FROM ozf_offer_discount_products
153 WHERE offer_id = p_offer_id
154 AND offer_discount_line_id = p_pbh_line_id
155 AND excluder_flag = p_excluder_flag;
156
157
158 CURSOR c_products_count(p_pbh_line_id NUMBER, p_excluder_flag VARCHAR2) IS
159 SELECT COUNT(*)
160 FROM ozf_offer_discount_products
161 WHERE offer_id = p_offer_id
162 AND offer_discount_line_id = p_pbh_line_id
163 AND excluder_flag = p_excluder_flag;
164
165 l_api_version CONSTANT NUMBER := 1.0;
166 l_api_name CONSTANT VARCHAR2(30) := 'denorm_vo_products';
167 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
168
169 l_stmt_temp VARCHAR2(32000) := NULL;
170 /*
171 l_pbh_lines_count NUMBER;
172 l_products_count NUMBER;
173 l_pbh_index NUMBER;
174 l_prod_index NUMBER;
175 l_excl_index NUMBER;
176 l_no_query_flag VARCHAR2(1) := 'N';*/
177 l_denorm_csr NUMBER;
178 l_ignore NUMBER;
179 l_stmt_denorm VARCHAR2(32000) := NULL;
180
181 BEGIN
182 SAVEPOINT denorm_vo_products;
183 ozf_utility_pvt.debug_message(l_full_name || ': start denorm_vo_products');
184
185 IF FND_API.to_boolean(p_init_msg_list) THEN
186 FND_MSG_PUB.initialize;
187 END IF;
188
189 IF NOT FND_API.compatible_api_call(l_api_version,
190 p_api_version,
191 l_api_name,
192 g_pkg_name)
193 THEN
194 RAISE FND_API.g_exc_unexpected_error;
195 END IF;
196
197 x_return_status := FND_API.g_ret_sts_success;
198
199 FOR l_pbh_line IN c_pbh_lines LOOP
200 FOR l_product IN c_products(l_pbh_line.offer_discount_line_id, 'N') LOOP
201 l_stmt_temp := NULL;
202 FND_DSQL.init;
203 FND_DSQL.add_text('INSERT INTO ozf_vo_products_temp(');
204 FND_DSQL.add_text('vo_products_temp_id,creation_date,created_by,last_update_date,');
205 FND_DSQL.add_text('last_updated_by,last_update_login,');
206 FND_DSQL.add_text('product_id,product_type) ');
207 FND_DSQL.add_text('SELECT ozf_vo_products_temp_s.NEXTVAL,SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
208 FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,');
209 FND_DSQL.add_text('product_id,product_type');
210 FND_DSQL.add_text(' FROM (');
211
212 l_stmt_temp := get_sql(p_context => l_product.product_context
213 ,p_attribute => l_product.product_attribute
214 ,p_attr_value => l_product.product_attr_value
215 );
216
217 FND_DSQL.add_text(')');
218
219 IF l_stmt_temp IS NULL THEN
220 EXIT;
221 END IF;
222
223 l_denorm_csr := DBMS_SQL.open_cursor;
224 FND_DSQL.set_cursor(l_denorm_csr);
225 l_stmt_denorm := FND_DSQL.get_text(FALSE);
226
227 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
228 FND_DSQL.do_binds;
229 l_ignore := DBMS_SQL.execute(l_denorm_csr);
230 dbms_sql.close_cursor(l_denorm_csr);
231 END LOOP;
232 /*
233 OPEN c_products_count(l_pbh_line.offer_discount_line_id, 'Y');
234 FETCH c_products_count INTO l_products_count;
235 CLOSE c_products_count;
236
237 l_excl_index := 1;
238
239 IF l_products_count > 0 THEN -- start: sql for exclusions
240 FND_DSQL.add_text(' MINUS (');
241
242 FOR l_product IN c_products(l_pbh_line.offer_discount_line_id, 'Y') LOOP
243 l_stmt_temp := NULL;
244
245 l_stmt_temp := get_sql(p_context => l_product.product_context
246 ,p_attribute => l_product.product_attribute
247 ,p_attr_value => l_product.product_attr_value
248 );
249
250 IF l_stmt_temp IS NULL THEN
251 l_no_query_flag := 'Y';
252 ELSE
253 IF l_excl_index < l_products_count THEN
254 FND_DSQL.add_text(' UNION ');
255 l_excl_index := l_excl_index + 1;
256 END IF;
257 END IF;
258 END LOOP;
259
260 FND_DSQL.add_text(')');
261 END IF; -- end: sql for exclusions
262
263 FND_DSQL.add_text(')');
264
265 IF l_pbh_index < l_pbh_lines_count THEN
266 FND_DSQL.add_text(' UNION ');
267 l_pbh_index := l_pbh_index + 1;
268 END IF;*/
269 END LOOP;
270
271 EXCEPTION
272 WHEN OTHERS THEN
273 ROLLBACK TO denorm_vo_products;
274 x_return_status := FND_API.g_ret_sts_unexp_error;
275
276 FND_MESSAGE.set_name('OZF', 'OZF_VO_PROD_STMT_FAILED');
277 FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
278 FND_MSG_PUB.add;
279
280 l_stmt_denorm := FND_DSQL.get_text(TRUE);
281 ozf_utility_pvt.debug_message (SUBSTR(l_stmt_denorm, 1, 250));
282 ozf_utility_pvt.debug_message (SUBSTR(l_stmt_denorm, 251, 250));
283 ozf_utility_pvt.debug_message (SUBSTR(l_stmt_denorm, 501, 250));
284 ozf_utility_pvt.debug_message (SUBSTR(l_stmt_denorm, 751, 250));
285
286 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
287 THEN
288 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
289 END IF;
290
291 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
292 p_count => x_msg_count,
293 p_data => x_msg_data);
294 END denorm_vo_products;
295
296 PROCEDURE check_dup_prod(
297 x_return_status OUT NOCOPY VARCHAR2,
298 x_msg_count OUT NOCOPY NUMBER,
299 x_msg_data OUT NOCOPY VARCHAR2,
300 p_offer_id IN NUMBER
301 )
302 IS
303 l_api_version CONSTANT NUMBER := 1.0;
304 l_api_name CONSTANT VARCHAR2(30) := 'check_dup_prod';
305 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
306 l_denorm_csr NUMBER;
307 l_ignore NUMBER;
308 l_stmt_denorm VARCHAR2(32000) := NULL;
309 l_stmt_product VARCHAR2(32000) := NULL;
310 l_duplicate_prod_exists VARCHAR2(1) := 'N';
311
312 CURSOR c_duplicate_prod_exists IS
313 SELECT 'Y' FROM DUAL
314 WHERE EXISTS(
315 SELECT 1
316 FROM ozf_vo_products_temp
317 HAVING COUNT(product_id) > 1
318 GROUP BY product_id
319 );
320 BEGIN
321 DELETE FROM ozf_vo_products_temp;
322
323 SAVEPOINT check_dup_prod;
324
325 x_return_status := Fnd_Api.g_ret_sts_success;
326
327 ozf_utility_pvt.debug_message(l_full_name || ': Start check_dup_prod' || p_offer_id);
328
329 -- ERRBUF := NULL;
330 -- RETCODE := '0';
331
332 IF NOT FND_API.compatible_api_call(l_api_version,
333 l_api_version,
334 l_api_name,
335 g_pkg_name)
336 THEN
337 RAISE FND_API.g_exc_unexpected_error;
338 END IF;
339 /*
340 FND_DSQL.init;
341 FND_DSQL.add_text('INSERT INTO ozf_vo_products_temp(');
342 FND_DSQL.add_text('vo_products_temp_id,creation_date,created_by,last_update_date,');
343 FND_DSQL.add_text('last_updated_by,last_update_login,');
344 FND_DSQL.add_text('product_id,product_type) ');
345 FND_DSQL.add_text('SELECT ozf_vo_products_temp_s.NEXTVAL,SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
346 FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,');
347 FND_DSQL.add_text('product_id,product_type');
348 FND_DSQL.add_text(' FROM (');
349 */
350 denorm_vo_products(p_api_version => l_api_version,
351 p_init_msg_list => FND_API.g_true,
352 p_commit => FND_API.g_false,
353 p_offer_id => p_offer_id,
354 x_return_status => x_return_status,
355 x_msg_count => x_msg_count,
356 x_msg_data => x_msg_data,
357 x_product_stmt => l_stmt_product);
358 /*
359 FND_DSQL.add_text(')');
360
361 ozf_utility_pvt.debug_message ('Denorm STMT status: ' || x_return_status);
362
363 l_denorm_csr := DBMS_SQL.open_cursor;
364 FND_DSQL.set_cursor(l_denorm_csr);
365 l_stmt_denorm := FND_DSQL.get_text(FALSE);
366 ozf_utility_pvt.debug_message ('start STMT--------------------------------------------');
367 ozf_utility_pvt.debug_message (SUBSTR(l_stmt_denorm, 1, 250));
368 ozf_utility_pvt.debug_message (SUBSTR(l_stmt_denorm, 251, 250));
369 ozf_utility_pvt.debug_message (SUBSTR(l_stmt_denorm, 501, 250));
370 ozf_utility_pvt.debug_message (SUBSTR(l_stmt_denorm, 751, 250));
371 ozf_utility_pvt.debug_message ('end STMT--------------------------------------------');
372 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
373 RAISE FND_API.g_exc_unexpected_error;
374 END IF;
375
376 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
377 FND_DSQL.do_binds;
378 l_ignore := DBMS_SQL.execute(l_denorm_csr);
379 dbms_sql.close_cursor(l_denorm_csr);
380 */
381 OPEN c_duplicate_prod_exists;
382 FETCH c_duplicate_prod_exists INTO l_duplicate_prod_exists;
383 CLOSE c_duplicate_prod_exists;
384
385 IF l_duplicate_prod_exists = 'Y' THEN
386 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
387 FND_MESSAGE.set_name('OZF', 'OZF_DUP_PROD_FOUND');
388 FND_MSG_PUB.add;
389 END IF;
390 RAISE FND_API.g_exc_error;
391 END IF;
392
393 EXCEPTION
394 /* WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
395 ROLLBACK TO check_dup_prod;
396 ozf_utility_pvt.debug_message('Unexpected Error: ' || SQLERRM);
397 ozf_utility_pvt.debug_message('Denorm STMT: ' || l_stmt_denorm);
398 x_return_status := FND_API.g_ret_sts_unexp_error ;
399 ERRBUF := l_msg_data;
400 RETCODE := 2;
401
402 WHEN OTHERS THEN
403 ROLLBACK TO check_dup_prod;
404 ozf_utility_pvt.debug_message('Other Error: ' || SQLERRM);
405 ozf_utility_pvt.debug_message('Denorm STMT: ' || l_stmt_denorm);
406 x_return_status := FND_API.g_ret_sts_unexp_error ;
407 ERRBUF := SQLERRM || ' - ' || l_stmt_denorm;
408 RETCODE := sqlcode;
409 */
410 WHEN FND_API.G_EXC_ERROR THEN
411 ROLLBACK TO check_dup_prod;
412 x_return_status := FND_API.G_RET_STS_ERROR;
413 FND_MSG_PUB.count_and_get(
414 p_encoded => FND_API.g_false
415 ,p_count => x_msg_count
416 ,p_data => x_msg_data);
417
418 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
419 ROLLBACK TO check_dup_prod;
420 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
421 FND_MSG_PUB.count_and_get(
422 p_encoded => FND_API.g_false
423 ,p_count => x_msg_count
424 ,p_data => x_msg_data);
425
426 WHEN OTHERS THEN
427 ROLLBACK TO check_dup_prod;
428 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
429 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
430 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME,l_api_name);
431 END IF;
432 FND_MSG_PUB.count_and_get(
433 p_encoded => FND_API.g_false
434 ,p_count => x_msg_count
435 ,p_data => x_msg_data);
436 END check_dup_prod;
437
438 END OZF_CHECK_DUP_PROD_PVT;