1 PACKAGE BODY QP_ADJUST_PRICELIST_PVT AS
2 /* $Header: QPXVAPLB.pls 120.3.12010000.2 2008/11/14 05:34:39 jputta ship $ */
3
4 PROCEDURE Adjust_Price_List
5 (
6 -- p_api_version_number IN NUMBER,
7 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8 -- p_commit IN VARCHAR2 := FND_API.G_FALSE,
9 -- x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
10 -- x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
11 -- x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
12 errbuf OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
13 retcode OUT NOCOPY /* file.sql.39 change */ NUMBER,
14 p_list_header_id IN NUMBER,
15 -- Changed datatype of p_percent and p_amount for Bug 2209587
16 p_percent IN VARCHAR2,
17 p_amount IN VARCHAR2,
18 p_segment1_lohi IN VARCHAR2,
19 p_segment2_lohi IN VARCHAR2,
20 p_segment3_lohi IN VARCHAR2,
21 p_segment4_lohi IN VARCHAR2,
22 p_segment5_lohi IN VARCHAR2,
23 p_segment6_lohi IN VARCHAR2,
24 p_segment7_lohi IN VARCHAR2,
25 p_segment8_lohi IN VARCHAR2,
26 p_segment9_lohi IN VARCHAR2,
27 p_segment10_lohi IN VARCHAR2,
28 p_segment11_lohi IN VARCHAR2,
29 p_segment12_lohi IN VARCHAR2,
30 p_segment13_lohi IN VARCHAR2,
31 p_segment14_lohi IN VARCHAR2,
32 p_segment15_lohi IN VARCHAR2,
33 p_segment16_lohi IN VARCHAR2,
34 p_segment17_lohi IN VARCHAR2,
35 p_segment18_lohi IN VARCHAR2,
36 p_segment19_lohi IN VARCHAR2,
37 p_segment20_lohi IN VARCHAR2,
38 p_org_id IN NUMBER, -- added for 2053405 by dhgupta
39 p_category_set_id IN NUMBER, -- added for 2053405 by dhgupta
40 p_category_id IN NUMBER,
41 p_status_code IN VARCHAR2,
42 p_create_date IN DATE,
43 p_rounding_factor IN NUMBER
44 )
45 IS
46
47 --l_api_version_number CONSTANT NUMBER := 1.0;
48 --l_api_name CONSTANT VARCHAR2(30) := 'Copy_Price_List';
49 --l_return_status VARCHAR2(1);
50 --l_msg_count NUMBER;
51 --l_msg_buf VARCHAR2(4000);
52 l_conc_request_id NUMBER := -1;
53 l_conc_program_application_id NUMBER := -1;
54 l_conc_program_id NUMBER := -1;
55 l_conc_login_id NUMBER := -1;
56 l_user_id NUMBER := -1;
57 l_test NUMBER := 0;
58 l_update_stmt VARCHAR2(9000) := '';
59 l_select_stmt VARCHAR2(9000) := '';
60 l_where_common VARCHAR2(9000) := '';
61 l_where_select VARCHAR2(9000) := '';
62 l_change NUMBER := 0;
63 l_category_set_id NUMBER := 0;
64 l_category_id NUMBER := p_category_id;
65 l_create_date DATE := p_create_date;
66 l_status_code VARCHAR2(30) := p_status_code;
67 l_date_mask VARCHAR2(14) := '''YYYYMMDD''';
68 l_sysdate DATE;
69 dummy VARCHAR2(1);
70 l_percent NUMBER;
71 l_amount NUMBER;
72 l_rounding_factor NUMBER;
73 l_price_rounding VARCHAR2(50) :='';
74 l_param_set VARCHAR2(1) := 'N';
75
76 BEGIN
77
78 l_conc_request_id := FND_GLOBAL.CONC_REQUEST_ID;
79 l_conc_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
80 l_user_id := FND_GLOBAL.USER_ID;
81 l_conc_login_id := FND_GLOBAL.CONC_LOGIN_ID;
82 l_conc_program_application_id := FND_GLOBAL.PROG_APPL_ID;
83
84 l_sysdate := sysdate;
85
86 /* Added canonical to number conversion by dhgupta for 1877622 */
87
88 l_percent := qp_number.canonical_to_number(p_percent);
89 l_amount := qp_number.canonical_to_number(p_amount);
90
91 /** Following code adjusts price list **/
92
93
94 l_update_stmt := 'UPDATE qp_list_lines q
95
96 SET q.operand = ';
97
98 l_select_stmt := 'SELECT NULL
99 FROM qp_list_lines q ';
100
101 l_where_select := 'AND q.operand + :chg < 0
102 AND rownum < 2 ';
103
104 /* Bug 1733332,1807570 : Since the Price List form allows user to store
105 prices upto any precision, to be consistent we need to remove the
106 rounding in the Adjustment module. User should be able to adjust the price
107 by any amount or percent. We will not round that amount. */
108
109
110 l_price_rounding := fnd_profile.value('QP_PRICE_ROUNDING');
111
112 IF l_price_rounding IS NOT NULL THEN --Added for Enhancement 1732601
113
114 BEGIN
115
116 select rounding_factor
117 into l_rounding_factor
118 from qp_list_headers_b
119 where list_header_id = p_list_header_id;
120
121 EXCEPTION
122 WHEN OTHERS THEN
123 l_rounding_factor := -2;
124 END;
125
126 IF l_percent IS NOT NULL THEN --Modified for 2340126
127 --IF nvl(l_percent,0) <> 0 THEN
128
129 l_update_stmt := l_update_stmt ||
130 'ROUND((q.operand *(:chg/100) + q.operand),-1*:rf), ';
131 l_change := l_percent;
132
133
134 ELSIF l_amount IS NOT NULL THEN --Modified for 2340126
135 --ELSIF nvl(l_amount,0) <> 0 THEN
136 l_update_stmt := l_update_stmt ||
137 'ROUND(((:chg) + q.operand),-1*:rf), ';
138 l_change := l_amount;
139
140 END IF;
141
142 ELSE
143 IF l_percent IS NOT NULL THEN --Modified for 2340126
144 --IF nvl(l_percent,0) <> 0 THEN
145
146 l_update_stmt := l_update_stmt ||
147 'q.operand *(:chg/100) + q.operand, ';
148 l_change := l_percent;
149
150 ELSIF l_amount IS NOT NULL THEN --Modified for 2340126
151 --ELSIF nvl(l_amount,0) <> 0 THEN
152 l_update_stmt := l_update_stmt ||
153 '(:chg) + q.operand, ';
154 l_change := l_amount;
155
156 END IF;
157 END IF;
158
159 l_update_stmt := l_update_stmt ||
160 'q.last_update_date = :dat1,
161 q.last_updated_by = :usr,
162 q.request_id = :req,
163 q.program_application_id = :app,
164 q.program_id = :pgm,
165 q.program_update_date = :dat2,
166 q.last_update_login = :lgn ';
167
168
169 l_where_common := 'WHERE q.list_header_id = :lh
170 AND q.generate_using_formula_id IS NULL';
171 --Commented out for 2615377
172 /*
173 AND q.list_line_id IN
174 (SELECT DISTINCT a.list_line_id
175 FROM qp_pricing_attributes a
176 WHERE a.list_line_id = q.list_line_id ';
177 */
178 --Added for 2615377
179 IF p_create_date IS NOT NULL THEN
180 l_where_common := l_where_common ||
181 ' AND TO_DATE(TO_CHAR(q.creation_date,''YYYY/MM/DD''),''YYYY/MM/DD'') = ' || 'TO_DATE(''' || TO_CHAR(p_create_date,'YYYY/MM/DD') || ''',''YYYY/MM/DD'')' || ' ';
182 END IF;
183 --Added for 2615377
184 l_where_common := l_where_common ||' AND q.list_line_id IN
185 (SELECT a.list_line_id -- 7540916
186 FROM qp_pricing_attributes a
187 WHERE 1 = 1 ';
188
189 IF p_category_set_id IS NOT NULL OR p_category_id IS NOT NULL --Modified by dhgupta for 2053405
190 OR (p_create_date IS NOT NULL) OR (p_status_code IS NOT NULL)
191 OR (p_segment1_lohi <> ''''' AND ''''') OR (p_segment2_lohi <> ''''' AND ''''')
192 OR (p_segment3_lohi <> ''''' AND ''''') OR (p_segment4_lohi <> ''''' AND ''''')
193 OR (p_segment5_lohi <> ''''' AND ''''') OR (p_segment6_lohi <> ''''' AND ''''')
194 OR (p_segment7_lohi <> ''''' AND ''''') OR (p_segment8_lohi <> ''''' AND ''''')
195 OR (p_segment9_lohi <> ''''' AND ''''') OR (p_segment10_lohi <> ''''' AND ''''')
196 OR (p_segment11_lohi <> ''''' AND ''''')
197 OR (p_segment12_lohi <> ''''' AND ''''')
198 OR (p_segment13_lohi <> ''''' AND ''''')
199 OR (p_segment14_lohi <> ''''' AND ''''')
200 OR (p_segment15_lohi <> ''''' AND ''''')
201 OR (p_segment16_lohi <> ''''' AND ''''')
202 OR (p_segment17_lohi <> ''''' AND ''''')
203 OR (p_segment18_lohi <> ''''' AND ''''')
204 OR (p_segment19_lohi <> ''''' AND ''''')
205 OR (p_segment20_lohi <> ''''' AND ''''') THEN
206
207 l_param_set := 'Y';
208
209 /* Commented the following statement and replaced it with a new statement to fix
210 the bug 1586265 */
211 /*
212 l_update_stmt := l_update_stmt ||
213 'AND TO_NUMBER(a.product_attr_value) IN
214 ( SELECT m.inventory_item_id
215 FROM mtl_system_items m
216 WHERE m.inventory_item_id = TO_NUMBER(a.product_attr_value) ';
217 */
218
219 -- changes for 7540916
220
221 l_where_common := l_where_common ||
222 'AND a.product_attribute_context = ''ITEM''
223 AND a.product_attribute = ''PRICING_ATTRIBUTE1''
224 AND a.product_attr_value IN
225 (SELECT TO_CHAR(m.inventory_item_id)
226 FROM mtl_system_items m
227 WHERE 1=1 ';
228
229 --Commented out for 2615377
230 /*
231 IF p_create_date IS NOT NULL THEN
232 l_where_common := l_where_common ||
233 'AND TO_DATE(TO_CHAR(m.creation_date,''YYYY/MM/DD''),''YYYY/MM/DD'') = ' || 'TO_DATE(''' || TO_CHAR(p_create_date,'YYYY/MM/DD') || ''',''YYYY/MM/DD'')' || ' ';
234 END IF;
235 */
236
237 IF p_status_code IS NOT NULL THEN
238 l_where_common := l_where_common ||
239 'AND m.inventory_item_status_code = ' || '''' || p_status_code || '''' || ' ';
240 END IF;
241
242 /* Added by dhgupta for 2053405 */
243
244 IF p_category_set_id IS NULL THEN
245 l_where_common := l_where_common ||
246 'AND Exists
247 ( SELECT ''x'' -- changes for 7540916
248 FROM mtl_item_categories ic
249 WHERE m.inventory_item_id = ic.inventory_item_id
250 AND m.organization_id = ic.organization_id ) ';
251 END IF;
252
253 /* Added by dhgupta for 2053405 */
254
255 IF p_category_set_id IS NOT NULL AND p_category_id IS NULL THEN
256 l_where_common := l_where_common ||
257 'AND Exists
258 ( SELECT ''x'' -- changes for 7540916
259 FROM mtl_item_categories ic
260 WHERE m.inventory_item_id = ic.inventory_item_id
261 AND m.organization_id = ic.organization_id
262 AND ic.category_set_id = :category_set_id ) ';
263 END IF;
264
265 /* Added by dhgupta for 2053405 */
266
267 IF p_category_set_id IS NOT NULL AND p_category_id IS NOT NULL THEN
268 l_where_common := l_where_common ||
269 'AND Exists
270 ( SELECT ''x'' -- changes for 7540916
271 FROM mtl_item_categories ic
272 WHERE m.inventory_item_id = ic.inventory_item_id
273 AND m.organization_id = ic.organization_id
274 AND ic.category_set_id = :category_set_id
275 AND ic.category_id = :category_id ) ';
276 END IF;
277
278 /*
279 IF nvl(p_category_id,0) <> 0 THEN
280 -- SELECT category_set_id
281 -- INTO l_category_set_id
282 -- FROM mtl_default_category_sets
283 -- WHERE functional_area_id = 7; --Order Entry Functional Area
284
285 l_where_common := l_where_common ||
286 'AND m.inventory_item_id IN
287 ( SELECT ic.inventory_item_id
288 FROM mtl_item_categories ic
289 WHERE ic.inventory_item_id = m.inventory_item_id
290 AND ic.organization_id = m.organization_id
291 AND ic.organization_id = :org_id
292 AND ic.category_set_id = :category_set_id
293 AND ic.category_id = :category_id ) ';
294 END IF;
295 */
296 IF (p_segment1_lohi <> ''''' AND ''''') THEN
297 l_where_common := l_where_common ||
298 'AND (m.segment1 BETWEEN ' || p_segment1_lohi || ') ';
299 END IF;
300
301 IF (p_segment2_lohi <> ''''' AND ''''') THEN
302 l_where_common := l_where_common ||
303 'AND (m.segment2 BETWEEN ' || p_segment2_lohi || ') ';
304 END IF;
305
306 IF (p_segment3_lohi <> ''''' AND ''''') THEN
307 l_where_common := l_where_common ||
308 'AND (m.segment3 BETWEEN ' || p_segment3_lohi || ') ';
309 END IF;
310
311 IF (p_segment4_lohi <> ''''' AND ''''') THEN
312 l_where_common := l_where_common ||
313 'AND (m.segment4 BETWEEN ' || p_segment4_lohi || ') ';
314 END IF;
315
316 IF (p_segment5_lohi <> ''''' AND ''''') THEN
317 l_where_common := l_where_common ||
318 'AND (m.segment5 BETWEEN ' || p_segment5_lohi || ') ';
319 END IF;
320
321 IF (p_segment6_lohi <> ''''' AND ''''') THEN
322 l_where_common := l_where_common ||
323 'AND (m.segment6 BETWEEN ' || p_segment6_lohi || ') ';
324 END IF;
325
326 IF (p_segment7_lohi <> ''''' AND ''''') THEN
327 l_where_common := l_where_common ||
328 'AND (m.segment7 BETWEEN ' || p_segment7_lohi || ') ';
329 END IF;
330
331 IF (p_segment8_lohi <> ''''' AND ''''') THEN
332 l_where_common := l_where_common ||
333 'AND (m.segment8 BETWEEN ' || p_segment8_lohi || ') ';
334 END IF;
335
336 IF (p_segment9_lohi <> ''''' AND ''''') THEN
337 l_where_common := l_where_common ||
338 'AND (m.segment9 BETWEEN ' || p_segment9_lohi || ') ';
339 END IF;
340
341 IF (p_segment10_lohi <> ''''' AND ''''') THEN
342 l_where_common := l_where_common ||
343 'AND (m.segment10 BETWEEN ' || p_segment10_lohi || ') ';
344 END IF;
345
346 IF (p_segment11_lohi <> ''''' AND ''''') THEN
347 l_where_common := l_where_common ||
348 'AND (m.segment10 BETWEEN ' || p_segment11_lohi || ') ';
349 END IF;
350 IF (p_segment12_lohi <> ''''' AND ''''') THEN
351 l_where_common := l_where_common ||
352 'AND (m.segment12 BETWEEN ' || p_segment12_lohi || ') ';
353 END IF;
354
355 IF (p_segment13_lohi <> ''''' AND ''''') THEN
356 l_where_common := l_where_common ||
357 'AND (m.segment13 BETWEEN ' || p_segment13_lohi || ') ';
358 END IF;
359
360 IF (p_segment14_lohi <> ''''' AND ''''') THEN
361 l_where_common := l_where_common ||
362 'AND (m.segment14 BETWEEN ' || p_segment14_lohi || ') ';
363 END IF;
364
365 IF (p_segment15_lohi <> ''''' AND ''''') THEN
366 l_where_common := l_where_common ||
367 'AND (m.segment15 BETWEEN ' || p_segment15_lohi || ') ';
368 END IF;
369
370 IF (p_segment16_lohi <> ''''' AND ''''') THEN
371 l_where_common := l_where_common ||
372 'AND (m.segment16 BETWEEN ' || p_segment16_lohi || ') ';
373 END IF;
374
375 IF (p_segment17_lohi <> ''''' AND ''''') THEN
376 l_where_common := l_where_common ||
377 'AND (m.segment17 BETWEEN ' || p_segment17_lohi || ') ';
378 END IF;
379
380 IF (p_segment18_lohi <> ''''' AND ''''') THEN
381 l_where_common := l_where_common ||
382 'AND (m.segment18 BETWEEN ' || p_segment18_lohi || ') ';
383 END IF;
384
385 IF (p_segment19_lohi <> ''''' AND ''''') THEN
386 l_where_common := l_where_common ||
387 'AND (m.segment19 BETWEEN ' || p_segment19_lohi || ') ';
388 END IF;
389
390 IF (p_segment20_lohi <> ''''' AND ''''') THEN
391 l_where_common := l_where_common ||
395 l_update_stmt := l_update_stmt || l_where_common || ' and m.organization_id = :org_id ) )';
392 'AND (m.segment20 BETWEEN ' || p_segment20_lohi || ') ';
393 END IF;
394 -- changes for 7540916
396 l_select_stmt := l_select_stmt || l_where_common || ' and m.organization_id = :org_id ) )'||l_where_select ;
397
398 ELSE
399 -- changes for 7540916
400 l_update_stmt := l_update_stmt || l_where_common || ' and m.organization_id = :org_id )';
401 l_select_stmt := l_select_stmt || l_where_common || ' and m.organization_id = :org_id )'||l_where_select ;
402
403 END IF; /* If any of the criteria about inventory_item_id is satisfied */
404
405 IF (NVL(l_amount,0) < 0 And (FND_PROFILE.VALUE('QP_NEGATIVE_PRICING')= 'N')) THEN
406 BEGIN
407 IF l_param_set = 'N' THEN
408 EXECUTE IMMEDIATE l_select_stmt INTO dummy USING
409 p_list_header_id, l_change;
410 ELSE
411 IF p_category_set_id IS NULL AND p_category_id IS NULL THEN
412 EXECUTE IMMEDIATE l_select_stmt INTO dummy USING
413 p_list_header_id, p_org_id, l_change;
414 END IF;
415 IF p_category_set_id IS NOT NULL AND p_category_id IS NULL THEN
416 EXECUTE IMMEDIATE l_select_stmt INTO dummy USING
417 p_list_header_id, p_org_id, p_category_set_id, l_change;
418 END IF;
419 IF p_category_set_id IS NOT NULL AND p_category_id IS NOT NULL THEN
420 EXECUTE IMMEDIATE l_select_stmt INTO dummy USING
421 p_list_header_id, p_org_id, p_category_set_id, p_category_id, l_change;
422 END IF;
423 END IF;
424 errbuf := FND_MESSAGE.GET_STRING('QP','QP_NEGATIVE_PRICE_AFTER_ADJUST');
425 retcode := 2;
426 RETURN;
427 EXCEPTION
428 WHEN NO_DATA_FOUND THEN
429 NULL;
430 END;
431 END IF;
432
433 IF l_price_rounding IS NOT NULL THEN --Added for Enhancement 1732601
434 IF l_param_set = 'N' THEN
435 EXECUTE IMMEDIATE l_update_stmt USING
436 l_change,l_rounding_factor,l_sysdate, l_user_id,
437 l_conc_request_id, l_conc_program_application_id,
438 l_conc_program_id, l_sysdate, l_conc_login_id, p_list_header_id;
439 ELSE
440 IF p_category_set_id IS NULL AND p_category_id IS NULL THEN
441 EXECUTE IMMEDIATE l_update_stmt USING
442 l_change,l_rounding_factor,l_sysdate, l_user_id,
443 l_conc_request_id, l_conc_program_application_id,
444 l_conc_program_id, l_sysdate, l_conc_login_id, p_list_header_id, p_org_id;
445 END IF;
446 IF p_category_set_id IS NOT NULL AND p_category_id IS NULL THEN
447 EXECUTE IMMEDIATE l_update_stmt USING
448 l_change,l_rounding_factor,l_sysdate, l_user_id,
449 l_conc_request_id, l_conc_program_application_id,
450 l_conc_program_id, l_sysdate, l_conc_login_id, p_list_header_id, p_org_id, p_category_set_id;
451 END IF;
452 IF p_category_set_id IS NOT NULL AND p_category_id IS NOT NULL THEN
453 EXECUTE IMMEDIATE l_update_stmt USING
454 l_change,l_rounding_factor,l_sysdate, l_user_id,
455 l_conc_request_id, l_conc_program_application_id,
456 l_conc_program_id, l_sysdate, l_conc_login_id, p_list_header_id, p_org_id, p_category_set_id, p_category_id;
457 END IF;
458 END IF;
459 ELSE
460 IF l_param_set = 'N' THEN
461 EXECUTE IMMEDIATE l_update_stmt USING
462 l_change,l_sysdate, l_user_id,
463 l_conc_request_id, l_conc_program_application_id,
464 l_conc_program_id, l_sysdate, l_conc_login_id, p_list_header_id;
465 ELSE
466 IF p_category_set_id IS NULL AND p_category_id IS NULL THEN
467 EXECUTE IMMEDIATE l_update_stmt USING
468 l_change,l_sysdate, l_user_id,
469 l_conc_request_id, l_conc_program_application_id,
470 l_conc_program_id, l_sysdate, l_conc_login_id, p_list_header_id, p_org_id;
471 END IF;
472 IF p_category_set_id IS NOT NULL AND p_category_id IS NULL THEN
473 EXECUTE IMMEDIATE l_update_stmt USING
474 l_change,l_sysdate, l_user_id,
475 l_conc_request_id, l_conc_program_application_id,
476 l_conc_program_id, l_sysdate, l_conc_login_id, p_list_header_id, p_org_id, p_category_set_id;
477 END IF;
478 IF p_category_set_id IS NOT NULL AND p_category_id IS NOT NULL THEN
479 EXECUTE IMMEDIATE l_update_stmt USING
480 l_change,l_sysdate, l_user_id,
481 l_conc_request_id, l_conc_program_application_id,
482 l_conc_program_id, l_sysdate, l_conc_login_id, p_list_header_id, p_org_id, p_category_set_id, p_category_id;
483 END IF;
484 END IF;
485 END IF;
486 retcode := 0;
487
488 COMMIT;
489
490 errbuf := '';
491
492 EXCEPTION
493 WHEN OTHERS THEN
494 errbuf := SQLCODE||' - '||SQLERRM;
495 retcode := 2;
496 END Adjust_Price_List;
497
498 END QP_ADJUST_PRICELIST_PVT;
[Home] [Help]