[Home] [Help]
PACKAGE BODY: APPS.QP_MODIFIER_UPGRADE_PVT
Source
4 -- Qualifier Context and Attribute Constants
1 PACKAGE BODY QP_Modifier_Upgrade_PVT AS
2 /* $Header: QPXVDISB.pls 120.2 2006/02/16 15:08:53 rchellam noship $ */
3
5
6 G_QUALIFIER_ATTRIBUTE1 CONSTANT VARCHAR2(20) := 'PRICE_LIST_ID';
7 G_QUALIFIER_ATTRIBUTE2 CONSTANT VARCHAR2(20) := 'DISCOUNT_ID';
8 G_QUALIFIER_ATTRIBUTE3 CONSTANT VARCHAR2(25) := 'CUSTOMER_CLASS_CODE';
9 G_QUALIFIER_ATTRIBUTE4 CONSTANT VARCHAR2(20) := 'SITE_ORG_ID';
10 G_QUALIFIER_ATTRIBUTE5 CONSTANT VARCHAR2(20) := 'SOLD_TO_ORG_ID';
11 G_QUALIFIER_ATTRIBUTE6 CONSTANT NUMBER := 1004; /*'Customer PO Number';*/
12 G_NEW_QUALIFIER_ATTRIBUTE6 CONSTANT NUMBER := 1053; /*'Customer PO Number';*/
13 G_QUALIFIER_ATTRIBUTE7 CONSTANT NUMBER := 1007; /*'Order Type';*/
14 G_NEW_QUALIFIER_ATTRIBUTE7 CONSTANT NUMBER := 1325; /*'Order Type';*/
15 G_QUALIFIER_ATTRIBUTE8 CONSTANT NUMBER := 1005; /*'Agreement Type';*/
16 G_NEW_QUALIFIER_ATTRIBUTE8 CONSTANT NUMBER := 1468; /*'Agreement Type';*/
17 G_QUALIFIER_ATTRIBUTE9 CONSTANT NUMBER := 1006; /*'Agreement Name';*/
18 G_NEW_QUALIFIER_ATTRIBUTE9 CONSTANT NUMBER := 1467; /*'Agreement Name';*/
19 G_QUALIFIER_ATTRIBUTE10 CONSTANT VARCHAR2(30) := 'GSA_CUSTOMER'; /* GSA Customer*/
20 G_PRODUCT_ATTRIBUTE1 CONSTANT NUMBER := 1001; /*'Item Number';*/
21 G_NEW_PRODUCT_ATTRIBUTE1 CONSTANT NUMBER := 1208; /*'Item Number';*/
22 G_PRODUCT_ATTRIBUTE2 CONSTANT NUMBER := 1045; /*'Item Category';*/
23 G_PRICING_ATTRIBUTE_UNITS CONSTANT VARCHAR2(20) := 'UNITS';
24 G_PRICING_ATTRIBUTE_DOLLARS CONSTANT VARCHAR2(20) := 'DOLLARS';
25
26 G_PRICING_ATTRIBUTE1 CONSTANT NUMBER := 1010;
27 G_PRICING_ATTRIBUTE2 CONSTANT NUMBER := 1011;
28 G_PRICING_ATTRIBUTE3 CONSTANT NUMBER := 1012;
29 G_PRICING_ATTRIBUTE4 CONSTANT NUMBER := 1013;
30 G_PRICING_ATTRIBUTE5 CONSTANT NUMBER := 1014;
31 G_PRICING_ATTRIBUTE6 CONSTANT NUMBER := 1015;
32 G_PRICING_ATTRIBUTE7 CONSTANT NUMBER := 1016;
33 G_PRICING_ATTRIBUTE8 CONSTANT NUMBER := 1017;
34 G_PRICING_ATTRIBUTE9 CONSTANT NUMBER := 1018;
35 G_PRICING_ATTRIBUTE10 CONSTANT NUMBER := 1019;
36 G_PRICING_ATTRIBUTE11 CONSTANT NUMBER := 1040;
37 G_PRICING_ATTRIBUTE12 CONSTANT NUMBER := 1041;
38 G_PRICING_ATTRIBUTE13 CONSTANT NUMBER := 1042;
39 G_PRICING_ATTRIBUTE14 CONSTANT NUMBER := 1043;
40 G_PRICING_ATTRIBUTE15 CONSTANT NUMBER := 1044;
41
42 -- Private Procedure
43
44 PROCEDURE Get_Percent (p_percent NUMBER ,
45 p_amount NUMBER,
46 p_newprice NUMBER,
47 x_operand OUT NOCOPY /* file.sql.39 change */ NUMBER,
48 x_arithmetic_operator OUT NOCOPY /* file.sql.39 change */ VARCHAR2) AS
49 BEGIN
50
51 IF (p_percent IS NOT NULL) THEN
52 x_operand := p_percent;
53 x_arithmetic_operator := '%';
54 ELSIF (p_amount IS NOT NULL) THEN
55 x_operand := p_amount;
56 x_arithmetic_operator := 'AMT';
57 ELSIF (p_newprice IS NOT NULL) THEN
58 x_operand := p_newprice;
59 x_arithmetic_operator := 'NEWPRICE';
60 ELSE
61 x_operand := 0;
62 x_arithmetic_operator := '%';
63 END IF;
64
65 END Get_Percent;
66
67 PROCEDURE Create_Discount_Mapping_Record(p_old_discount_id NUMBER,
68 p_old_discount_line_id NUMBER,
69 p_new_list_header_id NUMBER,
70 p_new_list_line_id NUMBER,
71 p_pricing_context VARCHAR2,
72 p_new_type VARCHAR2,
73 p_old_pbl_low NUMBER,
74 p_old_pbl_high NUMBER,
75 p_old_method_type_code VARCHAR2,
76 p_old_pb_percent NUMBER,
77 p_old_pb_amount NUMBER,
78 p_old_pb_price NUMBER) AS
79
80 err_msg varchar2(2000);
81
82 BEGIN
83
84 INSERT INTO QP_DISCOUNT_MAPPING(OLD_DISCOUNT_ID,OLD_DISCOUNT_LINE_ID,
85 NEW_LIST_HEADER_ID,NEW_LIST_LINE_ID, OLD_PRICE_BREAK_LINES_LOW,
86 OLD_PRICE_BREAK_LINES_HIGH, OLD_METHOD_TYPE_CODE, OLD_PRICE_BREAK_PERCENT,
87 OLD_PRICE_BREAK_AMOUNT, OLD_PRICE_BREAK_PRICE, NEW_TYPE,PRICING_CONTEXT)
88 VALUES (p_old_discount_id,p_old_discount_line_id,p_new_list_header_id,p_new_list_line_id,
89 p_old_pbl_low,p_old_pbl_high,p_old_method_type_code,p_old_pb_percent,
90 p_old_pb_amount,p_old_pb_price,p_new_type,p_pricing_context);
91 EXCEPTION
92 WHEN OTHERS THEN
93 err_msg := SQLERRM;
94 rollback;
95 QP_Util.Log_Error(p_id1 => p_old_discount_id,
96 p_id2 => p_old_discount_line_id,
97 p_id3 => p_new_list_header_id,
98 p_id4 => p_new_list_line_id,
99 p_id5 => p_old_pbl_low,
100 p_id6 => p_old_pbl_high,
101 p_id7 => p_old_method_type_code,
102 p_id8 => p_new_type,
103 p_error_type => 'DISCOUNT_MAPPING',
104 p_error_desc => err_msg,
105 p_error_module => 'Create_Discount_Mapping_Record');
106 raise;
107 END ;
108
109 PROCEDURE QP_Util_Get_Context_Attribute(p_entity_id VARCHAR2,
110 x_context OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
111 x_attribute OUT NOCOPY /* file.sql.39 change */ VARCHAR2) AS
112 v_context VARCHAR2(30);
113 v_attribute VARCHAR2(30);
114 err_msg VARCHAR2(2000);
115
116 BEGIN
117 QP_Util.get_context_attribute(p_entity_id,v_context,v_attribute);
118 x_context := v_context;
119 x_attribute := v_attribute;
120 EXCEPTION
121 WHEN OTHERS THEN
122 err_msg := SQLERRM;
123 rollback;
124 QP_Util.Log_Error(p_id1 => p_entity_id,
125 p_error_type => 'GET_CONTEXT_ATTRIBUTE',
126 p_error_desc => err_msg,
127 p_error_module => 'QP_Util_Get_Context_Attribute');
128 raise;
129 END;
130
131 PROCEDURE Get_Context_Attributes(
132 p_entity_id NUMBER,
133 x_context OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
134 x_attribute OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
135 x_product_flag OUT NOCOPY /* file.sql.39 change */ BOOLEAN,
136 x_pricing_flag OUT NOCOPY /* file.sql.39 change */ BOOLEAN,
137 x_qualifier_flag OUT NOCOPY /* file.sql.39 change */ BOOLEAN) AS
138
139 err_msg varchar2(2000);
140
141 BEGIN
142
143 -- Init the variables to null
144
145 x_context := NULL;
146 x_attribute := NULL;
147
148 x_product_flag := FALSE;
149 x_qualifier_flag := FALSE;
150 x_pricing_flag := FALSE;
151
152 QP_Util_Get_Context_Attribute(p_entity_id,x_context,x_attribute);
153
154 IF (p_entity_id in (G_PRODUCT_ATTRIBUTE1,G_NEW_PRODUCT_ATTRIBUTE1,G_PRODUCT_ATTRIBUTE2)) THEN
155 -- Get the attribute and context for item or item category
156 x_product_flag := TRUE;
157 ELSIF (p_entity_id in (G_QUALIFIER_ATTRIBUTE6,G_NEW_QUALIFIER_ATTRIBUTE6,
158 G_QUALIFIER_ATTRIBUTE7,G_NEW_QUALIFIER_ATTRIBUTE7,
159 G_QUALIFIER_ATTRIBUTE8,G_NEW_QUALIFIER_ATTRIBUTE8,
160 G_QUALIFIER_ATTRIBUTE9,G_NEW_QUALIFIER_ATTRIBUTE9)) THEN
161 -- Get the attribute and context for customer po,order type,agreement type
162 x_qualifier_flag := TRUE;
163 ELSIF (p_entity_id in (G_PRICING_ATTRIBUTE1, G_PRICING_ATTRIBUTE2, G_PRICING_ATTRIBUTE3,
164 G_PRICING_ATTRIBUTE4, G_PRICING_ATTRIBUTE5, G_PRICING_ATTRIBUTE6,
165 G_PRICING_ATTRIBUTE7, G_PRICING_ATTRIBUTE8, G_PRICING_ATTRIBUTE9,
166 G_PRICING_ATTRIBUTE10, G_PRICING_ATTRIBUTE11, G_PRICING_ATTRIBUTE12,
167 G_PRICING_ATTRIBUTE13, G_PRICING_ATTRIBUTE14, G_PRICING_ATTRIBUTE15)) THEN
168 -- Get the attribute and context for all pricing attributes
169 x_pricing_flag := TRUE;
170 END IF;
171
172 EXCEPTION
173 WHEN OTHERS THEN
174 err_msg := SQLERRM;
175 rollback;
176 QP_Util.Log_Error(p_id1 => p_entity_id,
177 p_error_type => 'GET_CONTEXT_ATTR',
178 p_error_desc => err_msg,
179 p_error_module => 'Get_Context_Attributes');
180 raise;
181 END Get_Context_Attributes;
182
183
184 PROCEDURE Create_Parallel_Slabs
185 (l_workers IN NUMBER) --2422176
186 --(l_workers IN NUMBER := 5)
187 IS
188 v_type CONSTANT VARCHAR2(3) := 'DLT';
189 l_total_lines NUMBER;
190 l_min_line NUMBER;
191 l_max_line NUMBER;
192 l_counter NUMBER;
193 l_gap NUMBER;
194 l_worker_count NUMBER;
195 l_worker_start NUMBER;
196 l_worker_end NUMBER;
197 l_price_list_line_id NUMBER;
198 l_start_flag NUMBER;
199 l_total_workers NUMBER;
200
201 BEGIN
202
203 delete qp_upg_lines_distribution
204 where line_type = v_type;
205 commit;
206
207 BEGIN
208 SELECT
209 NVL(MIN(DISCOUNT_ID),0),
210 NVL(MAX(DISCOUNT_ID),0)
211 INTO
212 l_min_line,
213 l_max_line
214 FROM
215 SO_DISCOUNTS;
216
217 EXCEPTION
218 when others then
219 null;
220 END;
221
222
223 FOR i in 1..l_workers LOOP
224
225 l_worker_start := l_min_line + trunc( (i-1) * (l_max_line-l_min_line)/l_workers);
226
227 l_worker_end := l_min_line + trunc(i*(l_max_line - l_min_line)/l_workers);
228
229 IF (i <> l_workers) then
230 l_worker_end := l_worker_end - 1;
231 END IF;
232
233 QP_Modifier_Upgrade_Util_PVT.insert_line_distribution
234 ( l_worker => i,
235 l_start_line => l_worker_start,
236 l_end_line => l_worker_end,
237 l_type_var => v_type);
238
239 END LOOP;
240
241 commit;
242
243 END Create_Parallel_Slabs;
244
245
246 PROCEDURE Create_Discounts(l_worker IN NUMBER := 1) AS
247
248 CURSOR get_discounts(p_min_line NUMBER,
249 p_max_line NUMBER) IS
250
251 SELECT sod.DISCOUNT_ID, sod.CREATION_DATE, sod.CREATED_BY, sod.LAST_UPDATE_DATE,
252 sod.LAST_UPDATED_BY, sod.LAST_UPDATE_LOGIN, sod.PROGRAM_APPLICATION_ID, sod.PROGRAM_ID,
253 sod.PROGRAM_UPDATE_DATE,sod.REQUEST_ID, sod.NAME, sod.DISCOUNT_TYPE_CODE, sod.PRICE_LIST_ID,
254 nvl(sod.AUTOMATIC_DISCOUNT_FLAG,'N') AUTOMATIC_DISCOUNT_FLAG, sod.OVERRIDE_ALLOWED_FLAG,
255 sod.GSA_INDICATOR, sod.PRORATE_FLAG, sod.PERCENT, sod.AMOUNT, sod.START_DATE_ACTIVE, sod.END_DATE_ACTIVE,
256 sod.DESCRIPTION, sod.DISCOUNT_LINES_FLAG, sopl.CURRENCY_CODE,sod.CONTEXT,
257 sod.ATTRIBUTE1, sod.ATTRIBUTE2, sod.ATTRIBUTE3,sod.ATTRIBUTE4,sod.ATTRIBUTE5,sod.ATTRIBUTE6,sod.ATTRIBUTE7,
258 sod.ATTRIBUTE8, sod.ATTRIBUTE9,sod.ATTRIBUTE10,sod.ATTRIBUTE11,sod.ATTRIBUTE12,sod.ATTRIBUTE13,
259 sod.ATTRIBUTE14, sod.ATTRIBUTE15
260 FROM SO_DISCOUNTS sod,QP_LIST_HEADERS_B sopl
261 WHERE sod.price_list_id = sopl.list_header_id
262 AND NOT EXISTS (SELECT 'x'
263 FROM QP_DISCOUNT_MAPPING a
264 WHERE a.OLD_DISCOUNT_ID = sod.DISCOUNT_ID
265 AND a.NEW_TYPE in ('O','L','Q','X'))
266 AND sod.DISCOUNT_ID BETWEEN p_min_line and p_max_line;
267
268 CURSOR get_discount_customers(p_discount_id NUMBER) IS
269 SELECT CUSTOMER_CLASS_CODE ,CUSTOMER_ID , SITE_USE_ID,START_DATE_ACTIVE,END_DATE_ACTIVE,CONTEXT,ATTRIBUTE1,
270 ATTRIBUTE2,ATTRIBUTE3, ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,
271 ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15
272 FROM SO_DISCOUNT_CUSTOMERS
273 WHERE DISCOUNT_ID = p_discount_id;
274
275 CURSOR get_discount_lines(p_discount_id NUMBER) IS
276 SELECT DISCOUNT_LINE_ID, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
277 LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,
278 REQUEST_ID, DISCOUNT_ID, ENTITY_ID, ENTITY_VALUE, PERCENT,
279 AMOUNT, PRICE, START_DATE_ACTIVE, END_DATE_ACTIVE,CONTEXT,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,
280 ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,
281 ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15
282 FROM SO_DISCOUNT_LINES_115
283 WHERE DISCOUNT_ID = p_discount_id;
284
285
286 CURSOR get_price_break_lines(p_discount_line_id NUMBER) IS
287
288 SELECT PRICE_BREAK_LINES_LOW_RANGE, PRICE_BREAK_LINES_HIGH_RANGE, DISCOUNT_LINE_ID,
289 METHOD_TYPE_CODE, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
290 LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,
291 REQUEST_ID, PERCENT, AMOUNT, PRICE, UNIT_CODE, START_DATE_ACTIVE, END_DATE_ACTIVE,
292 CONTEXT,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3, ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,
293 ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15
294 FROM SO_PRICE_BREAK_LINES
295 WHERE DISCOUNT_LINE_ID = p_discount_line_id;
296
297 CURSOR get_discounts_not_migrated_cur IS
298 SELECT a.DISCOUNT_ID,a.PRICE_LIST_ID
299 FROM SO_DISCOUNTS a
300 WHERE NOT EXISTS ( SELECT 'x'
301 FROM QP_LIST_HEADERS_B b
302 WHERE b.LIST_HEADER_ID = a.PRICE_LIST_ID);
303
304 CURSOR get_contexts_for_pattrs_cur(p_entity_id NUMBER) IS
305 SELECT nvl(a.DESCRIPTIVE_FLEX_CONTEXT_CODE,'Global Data Elements') descriptive_flex_context_code ,
306 b.ENTITY_CODE, a.COLUMN_SEQ_NUM, nvl(c.FORMAT_TYPE,'C') FORMAT_TYPE
307 FROM FND_DESCR_FLEX_COLUMN_USAGES a , SO_ENTITIES b, fnd_flex_value_sets c
308 WHERE a.DESCRIPTIVE_FLEXFIELD_NAME = 'PRICING_ATTRIBUTES'
309 AND a.APPLICATION_COLUMN_NAME = b.ENTITY_CODE
310 AND b.ENTITY_ID = p_entity_id
311 AND a.FLEX_VALUE_SET_ID = c.FLEX_VALUE_SET_ID(+);
312
313 CURSOR get_uom_for_item_cur(p_price_list_id NUMBER,
314 p_entity_value VARCHAR2) IS
315
316 SELECT distinct a.UNIT_CODE
317 FROM SO_PRICE_LIST_LINES_115 a
318 WHERE a.PRICE_LIST_ID = p_price_list_id
319 AND a.INVENTORY_ITEM_ID = p_entity_value;
320
321
322 x_list_header_id QP_LIST_HEADERS_B.LIST_HEADER_ID%TYPE;
323 v_list_header_id QP_LIST_HEADERS_B.LIST_HEADER_ID%TYPE;
324 x_list_line_id QP_LIST_LINES.LIST_LINE_ID%TYPE;
325 v_list_line_id QP_LIST_LINES.LIST_LINE_ID%TYPE;
326 v_price_break_line_id QP_LIST_LINES.LIST_LINE_ID%TYPE;
327 x_pricing_attribute_id QP_PRICING_ATTRIBUTES.PRICING_ATTRIBUTE_ID%TYPE;
328 v_pricing_attribute_id QP_PRICING_ATTRIBUTES.PRICING_ATTRIBUTE_ID%TYPE;
329 x_operand QP_LIST_LINES.OPERAND%TYPE;
330 x_arithmetic_operator QP_LIST_LINES.ARITHMETIC_OPERATOR%TYPE;
331 x_qualifier_grouping_no QP_QUALIFIERS.QUALIFIER_GROUPING_NO%TYPE;
332 v_qualifier_grouping_no QP_QUALIFIERS.QUALIFIER_GROUPING_NO%TYPE := 0;
333 v_cust_qualifier_grp_no QP_QUALIFIERS.QUALIFIER_GROUPING_NO%TYPE := 0;
334 v_context VARCHAR2(30);
335 v_attribute_name VARCHAR2(30);
336 v_pricing_context VARCHAR2(30);
337 v_pricing_attribute VARCHAR2(30);
338 v_pricing_flag BOOLEAN := FALSE;
339 v_product_context VARCHAR2(30);
340 v_product_attribute VARCHAR2(30);
341 x_product_precedence NUMBER;
342 x_pricing_precedence NUMBER;
343 x_product_datatype VARCHAR2(30);
344 x_pricing_datatype VARCHAR2(30);
345 v_price_break_context VARCHAR2(30);
346 v_price_break_attribute VARCHAR2(30);
347 v_price_context VARCHAR2(30);
348 v_price_attribute VARCHAR2(30);
349 v_operator_code VARCHAR2(30);
350 v_qualifier_flag BOOLEAN := FALSE;
351 x_qualifier_precedence NUMBER;
352 x_qualifier_datatype VARCHAR2(30);
353 x_error_code NUMBER;
354 v_product_flag BOOLEAN := FALSE;
355 v_discount_level VARCHAR2(20);
356 v_line_type_code VARCHAR2(10);
357 v_pricing_phase_id NUMBER;
358 x_rltd_modifier_id QP_RLTD_MODIFIERS.RLTD_MODIFIER_ID%TYPE;
359 v_lines_flag BOOLEAN := FALSE;
360 v_new_flag BOOLEAN := TRUE;
361 v_seq_num NUMBER := 0;
362 v_new_line_flag BOOLEAN := TRUE;
363 v_line_seq_num NUMBER := 0;
364 v_mapping_line_type VARCHAR2(1);
365 v_dummy VARCHAR2(1);
366 l_precedence NUMBER;
367 err_msg VARCHAR2(2000);
368 v_old_discount_id NUMBER;
369 v_old_discount_line_id NUMBER;
370 v_item_uom VARCHAR2(30) := NULL;
371 v_price_datatype VARCHAR2(30);
372 v_break_count NUMBER := 0;
373 v_unit_code VARCHAR2(30);
374 v_price_break_type_code VARCHAR2(30);
375 v_incomp_grp_code VARCHAR2(30);
376 v_pricing_group_sequence NUMBER;
377 v_min_line NUMBER;
378 v_max_line NUMBER;
379 v_contexts_flag BOOLEAN := FALSE;
380 v_entity_id NUMBER;
381 v_hqual_exists BOOLEAN := FALSE;
382 v_qualification_ind NUMBER;
383 v_precedence NUMBER;
384 are_there_discount_lines VARCHAR2(1);
385 number_discount_lines NUMBER := 0;
386 l_pricing_attr_value_from VARCHAR2(240);
387 l_comparison_operator_code VARCHAR2(30);
388
389 -- Other Constants
390
391 G_COMPARATOR_CODE CONSTANT VARCHAR2(1) := '=';
392 G_ORDER CONSTANT VARCHAR2(15) := 'ORDER_TOTAL';
393 G_LINE CONSTANT VARCHAR2(15) := 'LINE_ITEM';
394
395 BEGIN
396
397 -- Processing Header Level Discounts
398
399 -- To bypass the flex validation set g_validate_flag to FALSE
400 -- qp_util.validate_qp_flexfield() uses this flag.
401
402 qp_util.g_validate_flag :=FALSE;
403
404 begin
405
406 select start_line_id,
407 end_line_id
408 into v_min_line,
409 v_max_line
410 from qp_upg_lines_distribution
411 where worker = l_worker
412 and line_type = G_LIST_TYPE_CODE;
413
414 exception
415
416 when no_data_found then
417
418 /* log the error */
419 v_min_line := 0;
420 v_max_line := 0;
421 commit;
422 return;
423 end;
424
425 --dbms_output.put_line('v_min_line : ' || v_min_line);
426 --dbms_output.put_line('v_max_line : ' || v_max_line);
427
428
429 FOR i IN get_discounts(v_min_line,v_max_line)
430 LOOP
431
432 v_hqual_exists := FALSE;
433
434 v_old_discount_id := i.discount_id;
435
436 -- If the Discount does not exist
437 QP_Modifier_Upgrade_Util_PVT.Create_List_Header(i.creation_date,
438 i.created_by,
439 i.last_update_date,
440 i.last_updated_by,
441 nvl(i.last_update_login,1),
442 G_LIST_TYPE_CODE,
443 i.start_date_active,
444 i.end_date_active,
445 nvl(i.automatic_discount_flag,'N'),
446 i.discount_lines_flag,
447 i.currency_code,
448 i.name,
449 i.description,
450 NULL,--version_no,
451 'N', -- ask_for_flag
452 'QP',--source_sys_code
453 'Y',--active_flag,
454 nvl(i.gsa_indicator,'N'),
455 i.context,
456 i.attribute1,
457 i.attribute2,
458 i.attribute3,
459 i.attribute4,
460 i.attribute5,
461 i.attribute6,
462 i.attribute7,
463 i.attribute8,
464 i.attribute9,
465 i.attribute10,
466 i.attribute11,
467 i.attribute12,
468 i.attribute13,
469 i.attribute14,
470 i.attribute15,
471 v_new_flag,
472 v_seq_num,
473 v_old_discount_id,
474 'DISCOUNTS',
475 x_list_header_id);
476
477 -- Store the list header id
478 v_list_header_id := x_list_header_id;
479 v_seq_num := x_list_header_id;
480 v_new_flag := FALSE;
481
482
483
484 -- Create Qualifiers(For Price list) and attach it to the discount
485 -- Get the context and attribute for the price list
486 -- Price List and GSA Qualifiers are created with null grouping number
487
488 IF (i.price_list_id IS NOT NULL) THEN
489
490 v_hqual_exists := TRUE;
491
492 QP_UTIL_Get_Context_Attribute(G_QUALIFIER_ATTRIBUTE1,v_context , v_attribute_name);
493
494 --dbms_output.put_line('Context for Price List Is ' || v_context);
495 /* flex
496 BEGIN
497
498 QP_UTIL.Get_Qual_Flex_Properties(v_context,
499 v_attribute_name,
500 i.price_list_id,
501 x_qualifier_datatype,
502 x_qualifier_precedence,
503 x_error_code);
504
505 IF (x_error_code <> 0 ) THEN
506 QP_Util.Log_Error(p_id1 => G_QUALIFIER_ATTRIBUTE1,
507 p_error_type => 'QUAL_FLEX_PROPERTIES',
508 p_error_desc =>
509 'Please Check The Context: ' || v_context || ' Attribute: ' || v_attribute_name,
510 p_error_module => 'QP_Util.Get_Qual_Flex_Properties');
511 END IF;
512
513 EXCEPTION
514 WHEN OTHERS THEN
515 err_msg := SQLERRM;
516 rollback;
517 QP_Util.Log_Error(p_id1 => G_QUALIFIER_ATTRIBUTE1,
518 p_error_type => 'QUAL_FLEX_PROPERTIES',
519 p_error_desc => err_msg,
520 p_error_module => 'QP_Util.Get_Qual_Flex_Properties');
521 raise;
522 END;
523 flex */
524
525 QP_Modifier_Upgrade_Util_PVT.Create_Qualifier(i.creation_date,
526 i.created_by,
527 i.last_update_date,
528 i.last_updated_by,
529 i.last_update_login,
530 i.program_application_id,
531 i.program_id,
532 i.program_update_date,
533 i.request_id,
534 'N',
535 G_COMPARATOR_CODE,
536 v_context,
537 v_attribute_name,
538 i.price_list_id,
539 null,
540 v_list_header_id,
541 NULL,
542 -- x_qualifier_precedence,
543 -- nvl(x_qualifier_datatype,'C'),
544 140,
545 'C',
546 null, -- start_date_active
547 null, -- end_date_active
548 null,
549 null,
550 null,
551 null,
552 null,
553 null,
554 null,
555 null,
556 null,
557 null,
558 null,
559 null,
560 null,
561 null,
562 null,
563 null,
564 v_old_discount_id,
565 'DISCOUNTS',
566 x_qualifier_grouping_no);
567 END IF; -- i.price_list_id IS NOT NULL
568
569 -- Create a GSA Qualifier
570 IF (i.GSA_INDICATOR = 'Y') THEN
571
572 v_hqual_exists := TRUE;
573
574 -- Get the context and attribute for the gsa qualifier
575 QP_UTIL_Get_Context_Attribute(G_QUALIFIER_ATTRIBUTE10,v_context , v_attribute_name);
576
577 /* flex
578
579 BEGIN
580
581 QP_UTIL.Get_Qual_Flex_Properties(v_context,
582 v_attribute_name,
583 i.gsa_indicator,
584 x_qualifier_datatype,
585 x_qualifier_precedence,
586 x_error_code);
587 IF (x_error_code <> 0 ) THEN
588 QP_Util.Log_Error(p_id1 => G_QUALIFIER_ATTRIBUTE10,
589 p_error_type => 'QUAL_FLEX_PROPERTIES',
590 p_error_desc =>
591 'Please Check The Context: ' || v_context || ' Attribute: ' || v_attribute_name,
592 p_error_module => 'QP_Util.Get_Qual_Flex_Properties');
593 END IF;
594
595 EXCEPTION
596 WHEN OTHERS THEN
597 err_msg := SQLERRM;
598 rollback;
599 QP_Util.Log_Error(p_id1 => G_QUALIFIER_ATTRIBUTE10,
600 p_error_type => 'QUAL_FLEX_PROPERTIES',
601 p_error_desc => err_msg,
602 p_error_module => 'QP_Util.Get_Qual_Flex_Properties');
603 raise;
604 END;
605
606 flex */
607
608 -- Create a GSA Qualifier
609 QP_Modifier_Upgrade_Util_PVT.Create_Qualifier(i.creation_date,
610 i.created_by,
611 i.last_update_date,
612 i.last_updated_by,
613 i.last_update_login,
614 i.program_application_id,
615 i.program_id,
616 i.program_update_date,
617 i.request_id,
618 'N',
619 G_COMPARATOR_CODE,
620 v_context,
621 v_attribute_name,
622 i.gsa_indicator,
623 -1,
624 v_list_header_id,
625 NULL,
626 -- x_qualifier_precedence,
627 -- nvl(x_qualifier_datatype,'C'),
628 100,
629 'C',
630 null, -- start_date_active
631 null, -- end_date_active
632 null,
633 null,
634 null,
635 null,
636 null,
637 null,
638 null,
639 null,
640 null,
641 null,
642 null,
643 null,
644 null,
645 null,
646 null,
647 null,
648 v_old_discount_id,
649 'DISCOUNTS',
650 x_qualifier_grouping_no);
651 END IF;
652
653 -- Check other qualifiers from so_discount_customers like customer_class_code,site_use_id,
654 -- customer_id create qualifiers
655 -- There needs to be different group no for each record in so_discount_customers
656
657 FOR l IN get_discount_customers(i.discount_id)
658 LOOP
659
660 v_hqual_exists := TRUE;
661
662 IF (l.customer_class_code IS NOT NULL) THEN
663
664 -- Get the context and attribute for the customer class code
665 QP_UTIL_Get_Context_Attribute(G_QUALIFIER_ATTRIBUTE3,v_context , v_attribute_name);
666 /* flex
667 BEGIN
668 QP_UTIL.Get_Qual_Flex_Properties(v_context,
669 v_attribute_name,
670 l.customer_class_code,
671 x_qualifier_datatype,
672 x_qualifier_precedence,
673 x_error_code);
674 IF (x_error_code <> 0 ) THEN
675 QP_Util.Log_Error(p_id1 => G_QUALIFIER_ATTRIBUTE3,
676 p_error_type => 'QUAL_FLEX_PROPERTIES',
677 p_error_desc =>
678 'Please Check The Context: ' || v_context || ' Attribute: ' || v_attribute_name,
679 p_error_module => 'QP_Util.Get_Qual_Flex_Properties');
680 END IF;
681 EXCEPTION
682 WHEN OTHERS THEN
683 err_msg := SQLERRM;
684 rollback;
685 QP_Util.Log_Error(p_id1 => G_QUALIFIER_ATTRIBUTE3,
686 p_error_type => 'QUAL_FLEX_PROPERTIES',
687 p_error_desc => err_msg,
688 p_error_module => 'QP_Util.Get_Qual_Flex_Properties');
689 raise;
690 END;
691 flex */
692 -- Create the Qualifier for customer class code
693 QP_Modifier_Upgrade_Util_PVT.Create_Qualifier(i.creation_date,
694 i.created_by,
695 i.last_update_date,
696 i.last_updated_by,
697 i.last_update_login,
698 i.program_application_id,
699 i.program_id,
700 i.program_update_date,
701 i.request_id,
702 'N',
703 G_COMPARATOR_CODE,
704 v_context,
705 v_attribute_name,
706 l.customer_class_code,
707 v_cust_qualifier_grp_no,
708 v_list_header_id,
709 NULL,
710 -- x_qualifier_precedence,
711 -- nvl(x_qualifier_datatype,'C'),
712 310,
713 'C',
714 l.start_date_active,
715 l.end_date_active,
716 l.context,
717 l.attribute1,
718 l.attribute2,
719 l.attribute3,
720 l.attribute4,
721 l.attribute5,
722 l.attribute6,
723 l.attribute7,
724 l.attribute8,
725 l.attribute9,
726 l.attribute10,
727 l.attribute11,
728 l.attribute12,
729 l.attribute13,
730 l.attribute14,
731 l.attribute15,
732 v_old_discount_id,
733 'DISCOUNTS',
734 x_qualifier_grouping_no);
735 v_cust_qualifier_grp_no := x_qualifier_grouping_no;
736 END IF; --l.customer_class_code IS NOT NULL
737
738 IF (l.site_use_id IS NOT NULL) THEN
739
740 -- Get the context and attribute for the site_use_id
741 QP_UTIL_Get_Context_Attribute(G_QUALIFIER_ATTRIBUTE4,v_context , v_attribute_name);
742 /* flex
743 BEGIN
744 QP_UTIL.Get_Qual_Flex_Properties(v_context,
745 v_attribute_name,
746 l.site_use_id,
747 x_qualifier_datatype,
748 x_qualifier_precedence,
749 x_error_code);
750 IF (x_error_code <> 0 ) THEN
751 QP_Util.Log_Error(p_id1 => G_QUALIFIER_ATTRIBUTE4,
752 p_error_type => 'QUAL_FLEX_PROPERTIES',
753 p_error_desc =>
754 'Please Check The Context: ' || v_context || ' Attribute: ' || v_attribute_name,
755 p_error_module => 'QP_Util.Get_Qual_Flex_Properties');
756 END IF;
757 EXCEPTION
758 WHEN OTHERS THEN
759 err_msg := SQLERRM;
760 rollback;
761 QP_Util.Log_Error(p_id1 => G_QUALIFIER_ATTRIBUTE4,
762 p_error_type => 'QUAL_FLEX_PROPERTIES',
763 p_error_desc => err_msg,
764 p_error_module => 'QP_Util.Get_Qual_Flex_Properties');
765 raise;
766 END;
767 flex */
768 -- Create the qualifier for site_use_id
769 QP_Modifier_Upgrade_Util_PVT.Create_Qualifier(i.creation_date,
770 i.created_by,
771 i.last_update_date,
772 i.last_updated_by,
773 i.last_update_login,
774 i.program_application_id,
775 i.program_id,
776 i.program_update_date,
777 i.request_id,
778 'N',
779 G_COMPARATOR_CODE,
780 v_context,
781 v_attribute_name,
782 l.site_use_id,
783 v_cust_qualifier_grp_no,
784 v_list_header_id,
785 NULL,
786 -- x_qualifier_precedence,
787 -- nvl(x_qualifier_datatype,'C'),
788 270,
789 'N',
790 l.start_date_active,
791 l.end_date_active,
792 l.context,
793 l.attribute1,
794 l.attribute2,
795 l.attribute3,
796 l.attribute4,
797 l.attribute5,
798 l.attribute6,
799 l.attribute7,
800 l.attribute8,
801 l.attribute9,
802 l.attribute10,
803 l.attribute11,
804 l.attribute12,
805 l.attribute13,
806 l.attribute14,
807 l.attribute15,
808 v_old_discount_id,
809 'DISCOUNTS',
810 x_qualifier_grouping_no);
811 IF ( v_cust_qualifier_grp_no = 0 ) THEN
812 v_cust_qualifier_grp_no := x_qualifier_grouping_no;
813 END IF;
814 END IF; --Site Org Id
815
816 IF (l.customer_id IS NOT NULL) THEN
817
818 -- Get the context and attribute for the customer_id
819 QP_UTIL_Get_Context_Attribute(G_QUALIFIER_ATTRIBUTE5,v_context , v_attribute_name);
820 /* flex
821 BEGIN
822 QP_UTIL.Get_Qual_Flex_Properties(v_context,
823 v_attribute_name,
824 l.customer_id,
825 x_qualifier_datatype,
826 x_qualifier_precedence,
827 x_error_code);
828 IF (x_error_code <> 0 ) THEN
829 QP_Util.Log_Error(p_id1 => G_QUALIFIER_ATTRIBUTE5,
830 p_error_type => 'QUAL_FLEX_PROPERTIES',
831 p_error_desc =>
832 'Please Check The Context: ' || v_context || ' Attribute: ' || v_attribute_name,
833 p_error_module => 'QP_Util.Get_Qual_Flex_Properties');
834 END IF;
835 EXCEPTION
836 WHEN OTHERS THEN
837 err_msg := SQLERRM;
838 rollback;
839 QP_Util.Log_Error(p_id1 => G_QUALIFIER_ATTRIBUTE5,
840 p_error_type => 'QUAL_FLEX_PROPERTIES',
841 p_error_desc => err_msg,
842 p_error_module => 'QP_Util.Get_Qual_Flex_Properties');
843 raise;
844 END;
845 flex */
846 -- Create the qualifier for customer_id
847 QP_Modifier_Upgrade_Util_PVT.Create_Qualifier(i.creation_date,
848 i.created_by,
849 i.last_update_date,
850 i.last_updated_by,
851 i.last_update_login,
852 i.program_application_id,
853 i.program_id,
854 i.program_update_date,
855 i.request_id,
856 'N',
857 G_COMPARATOR_CODE,
858 v_context,
859 v_attribute_name,
860 l.customer_id,
861 v_cust_qualifier_grp_no,
862 v_list_header_id,
863 NULL,
864 -- x_qualifier_precedence,
865 -- nvl(x_qualifier_datatype,'C'),
866 260,
867 'N',
868 l.start_date_active,
869 l.end_date_active,
870 l.context,
871 l.attribute1,
872 l.attribute2,
873 l.attribute3,
874 l.attribute4,
875 l.attribute5,
876 l.attribute6,
877 l.attribute7,
878 l.attribute8,
879 l.attribute9,
880 l.attribute10,
881 l.attribute11,
882 l.attribute12,
883 l.attribute13,
884 l.attribute14,
885 l.attribute15,
886 v_old_discount_id,
887 'DISCOUNTS',
888 x_qualifier_grouping_no);
889 IF ( v_cust_qualifier_grp_no = 0 ) THEN
890 v_cust_qualifier_grp_no := x_qualifier_grouping_no;
891 END IF;
892 END IF; --l.customer_id IS NOT NULL
893 v_cust_qualifier_grp_no := 0;
894 END LOOP; -- Discount Customers
895
896 -- Create List Line
897
898 -- If discount_lines_flag = 'N'
899 -- there are no discount lines in so_discount_lines
900 -- Determine the Discount Level
901
902 IF (i.discount_type_code = G_ORDER) THEN
903 v_discount_level := G_ORDER_LEVEL;
904 v_pricing_phase_id := 4;
905 ELSE
906 v_discount_level := G_LINE_LEVEL;
907 v_pricing_phase_id := 2;
908 END IF;
909
910 IF (i.automatic_discount_flag = 'Y') THEN
911 v_pricing_group_sequence := 1;
912 v_incomp_grp_code := 'LVL 1';
913 ELSE
914 v_pricing_group_sequence := NULL; -- Manual Discounts
915 v_incomp_grp_code := NULL;
916 END IF;
917
918 select count(*)
919 into number_discount_lines
920 from so_discount_lines_115
921 where discount_id = i.discount_id;
922
923 if (number_discount_lines = 0 )
924 then
925 are_there_discount_lines := 'N';
926 else
927 are_there_discount_lines := 'Y';
928 end if;
929
930
931 -- IF (i.discount_lines_flag = 'N' ) THEN /* Order Level Discount */
932
933 IF (are_there_discount_lines = 'N' ) THEN /* Order Level Discount */
934
935
936 -- Determine the Arthimetic Operator and Operand in qp_list_lines from Percent and Amount columns
937 -- in so_discounts table
938
939 Get_Percent(i.percent , i.amount , 0 , x_operand , x_arithmetic_operator);
940
941 IF (x_arithmetic_operator = 'AMT') THEN
942 IF (x_operand < 0) THEN
943 v_line_type_code := G_SURCHARGE_CODE;
944 x_operand := -(x_operand);
945 ELSE
946 v_line_type_code := G_LIST_LINE_TYPE_CODE;
947 END IF;
948 ELSE
949 v_line_type_code := G_LIST_LINE_TYPE_CODE;
950 END IF;
951
952 -- If Level = 'Order' , then there is no record in Pricing Attributes.
953 -- So v_qualification_ind = 5
954 -- If Level = 'Line' , then there is record in Pricing Attributes which has product.
955 -- So v_qualification_ind = 1
956
957 IF (v_discount_level = G_ORDER_LEVEL) THEN
958 v_qualification_ind := 5;
959 ELSE
960 v_qualification_ind := 1;
961 END IF;
962
963 -- mkarya for bug 1807828, product precedence must be populated for LINE LEVEL discounts as pricing attribute record is always created for LINE LEVEL discounts with ALL product and ALL pricing attribute
964 v_precedence := NULL;
965 IF (v_discount_level = G_LINE_LEVEL) THEN
966 select a.COLUMN_SEQ_NUM
967 INTO v_precedence
968 FROM FND_DESCR_FLEX_COLUMN_USAGES a
969 WHERE a.DESCRIPTIVE_FLEXFIELD_NAME = 'QP_ATTR_DEFNS_PRICING'
970 AND a.APPLICATION_ID = 661 --(QP). Added for bug 5030757
971 AND a.DESCRIPTIVE_FLEX_CONTEXT_CODE = 'ITEM'
972 AND a.APPLICATION_COLUMN_NAME = 'PRICING_ATTRIBUTE3';
973 END IF;
974 -- Create the list line
975 QP_Modifier_Upgrade_Util_PVT.Create_List_Line(i.creation_date,
976 i.created_by,
977 i.last_update_date,
978 i.last_updated_by,
979 i.last_update_login,
980 i.program_application_id,
981 i.program_id,
982 i.program_update_date,
983 i.request_id,
984 v_list_header_id,
985 v_line_type_code,
986 i.start_date_active,
987 i.end_date_active,
988 i.automatic_discount_flag,
989 v_discount_level,
990 x_arithmetic_operator,
991 x_operand,
992 v_pricing_phase_id,
993 v_incomp_grp_code, -- incomp_grp_code
994 v_pricing_group_sequence, -- pricing_group_seq
995 'N', -- accrual_flag
996 -- NULL, -- issue Can this be null
997 v_precedence, -- for bug 1807828
998 i.PRORATE_FLAG,
999 'N', -- print on invoice flag
1000 nvl(i.override_allowed_flag,'N'), -- override flag
1001 null,
1002 i.context,
1003 i.attribute1,
1004 i.attribute2,
1005 i.attribute3,
1006 i.attribute4,
1007 i.attribute5,
1008 i.attribute6,
1009 i.attribute7,
1010 i.attribute8,
1011 i.attribute9,
1012 i.attribute10,
1013 i.attribute11,
1014 i.attribute12,
1015 i.attribute13,
1016 i.attribute14,
1017 i.attribute15,
1018 v_qualification_ind,
1019 v_new_line_flag,
1020 v_line_seq_num,
1021 v_old_discount_id,
1022 v_old_discount_line_id,
1023 'DISCOUNTS',
1024 x_list_line_id);
1025 -- Store the List Line Id
1026 v_list_line_id := x_list_line_id;
1027 v_line_seq_num := x_list_line_id;
1028 v_new_line_flag := FALSE;
1029
1030 -- Insert a record into the mapping table
1031
1032 Create_Discount_Mapping_Record(i.discount_id,NULL,v_list_header_id,v_list_line_id,null,'O',
1033 null,null,null,null,null,null);
1034
1035 -- Create a record in qp_pricing_attributes for ALL products and ALL pricing attributes only if
1036 -- LINE LEVEL Discounts with no actual discount lines
1037 -- Added after discounts upgrade review with Jay and Alison
1038
1039 IF (v_discount_level = G_LINE_LEVEL) THEN
1040
1041
1042 -- Create the Product/Pricing Attribute
1043 QP_Modifier_Upgrade_Util_PVT.Create_Pricing_Attribute(i.creation_date,
1044 i.created_by,
1045 i.last_update_date,
1046 i.last_updated_by,
1047 i.last_update_login,
1048 i.program_application_id,
1049 i.program_id,
1050 i.program_update_date,
1051 i.request_id,
1052 v_list_line_id,
1053 'N',
1054 'N',
1055 'ITEM',
1056 'PRICING_ATTRIBUTE3',
1057 'ALL',
1058 NULL, --Product UOM Code
1059 NULL,
1060 NULL,
1061 NULL,--Pricing attr value from
1062 NULL, --Pricing_attr_value_to
1063 NULL, -- changed to NULL from BETWEEN for bug 1872995
1064 'N', -- pricing datatype
1065 'C',
1066 v_old_discount_id,
1067 v_old_discount_line_id,
1068 'DISCOUNTS',
1069 x_pricing_attribute_id);
1070 END IF;
1071
1072 ELSE /* Line Level Discount */
1073
1074 -- Init the flag
1075 v_lines_flag := FALSE;
1076
1077 IF (i.automatic_discount_flag = 'Y') THEN
1078 v_incomp_grp_code := 'LVL 1';
1079 ELSE
1080 v_incomp_grp_code := NULL;
1081 END IF;
1082 -- Need this init at header and line level.That is the reason this statement is there 2 times in this file
1083 v_entity_id := NULL;
1084
1085
1086
1087 FOR j IN get_discount_lines(i.discount_id)
1088 LOOP
1089
1090 -- Need this init at header and line level.That is the reason this statement is there 2 times in this file
1091 v_entity_id := NULL;
1092
1093 -- Check to see if there are any discount lines.
1094
1095 v_old_discount_line_id := j.discount_line_id;
1096
1097 IF (j.entity_id IN (G_PRICING_ATTRIBUTE1,G_PRICING_ATTRIBUTE2,G_PRICING_ATTRIBUTE3,
1098 G_PRICING_ATTRIBUTE4,G_PRICING_ATTRIBUTE5,G_PRICING_ATTRIBUTE6,
1099 G_PRICING_ATTRIBUTE7,G_PRICING_ATTRIBUTE8,G_PRICING_ATTRIBUTE9,
1100 G_PRICING_ATTRIBUTE10,G_PRICING_ATTRIBUTE11,G_PRICING_ATTRIBUTE12,
1101 G_PRICING_ATTRIBUTE13,G_PRICING_ATTRIBUTE14,G_PRICING_ATTRIBUTE15)) THEN
1102
1103 v_contexts_flag := FALSE;
1104 v_entity_id := j.entity_id;
1105
1106 -- Create discount lines for different contexts of an entity id
1107 FOR b in get_contexts_for_pattrs_cur(j.entity_id)
1108 LOOP
1109 /* flex
1110 BEGIN
1111
1112 v_contexts_flag := TRUE;
1113
1114 QP_UTIL.Get_Prod_Flex_Properties(b.descriptive_flex_context_code,
1115 b.entity_code,
1116 j.entity_value,
1117 x_pricing_datatype,
1118 x_pricing_precedence,
1119 x_error_code);
1120 IF (x_error_code <> 0 ) THEN
1121 QP_Util.Log_Error(p_id1 => j.entity_id,
1122 p_error_type => 'PROD_FLEX_PROPERTIES',
1123 p_error_desc =>
1124 'Please Check The Context: ' || b.descriptive_flex_context_code ||
1125 ' Attribute: ' || b.entity_code ,
1126 p_error_module => 'QP_Util.Get_Prod_Flex_Properties');
1127 END IF;
1128 l_precedence := x_pricing_precedence;
1129 EXCEPTION
1130 WHEN OTHERS THEN
1131 err_msg := SQLERRM;
1132 rollback;
1133 QP_Util.Log_Error(p_id1 => j.entity_id,
1134 p_error_type => 'PROD_FLEX_PROPERTIES',
1135 p_error_desc => 'Pricing Entity ' || err_msg,
1136 p_error_module => 'QP_Util.Get_Prod_Flex_Properties');
1137 raise;
1138 END;
1139
1140 flex */
1141
1142 -- Set the lines flag to TRUE indicating that there are discount lines
1143 v_lines_flag := TRUE;
1144
1145 -- If percent or amount or price is not null then it is a regular discount line
1146 -- If percent and amount and price is null , then it is a price break.
1147 -- Ex ecute this logic only if it is a product or qualifier. This is because we are not dealing
1148 -- with any entities other than those handled in Get_Context_Attributes procedure
1149
1150 -- Determine the Arthimetic Operator and Operand in qp_list_lines from Percent and Amount
1151 Get_Percent(j.percent , j.amount , j.price , x_operand , x_arithmetic_operator);
1152
1153 IF (j.percent IS NOT NULL OR j.amount IS NOT NULL OR j.price IS NOT NULL) THEN
1154 IF (x_arithmetic_operator = 'AMT') THEN
1155 IF (x_operand < 0) THEN
1156 v_line_type_code := G_SURCHARGE_CODE;
1157 x_operand := -(x_operand);
1158 ELSE
1159 v_line_type_code := G_LIST_LINE_TYPE_CODE;
1160 END IF;
1161 ELSE
1162 v_line_type_code := G_LIST_LINE_TYPE_CODE;
1163 END IF;
1164 v_mapping_line_type := 'L'; -- Regular discount line
1165 v_price_break_type_code := NULL;
1166 v_price_context := null;
1167 v_price_attribute := null;
1168 v_operator_code := null;
1169 ELSE
1170 v_line_type_code := G_PRICE_BREAK_LINE_TYPE_CODE;
1171 v_mapping_line_type := 'Q'; -- Indicates that this line has price breaks
1172 v_price_break_type_code := 'POINT';
1173 v_price_context := 'VOLUME';
1174 v_price_attribute := 'PRICING_ATTRIBUTE10';
1175 v_operator_code := 'BETWEEN';
1176 END IF;
1177
1178 -- For entity ids related to Pricing Attributes
1179 -- If Qualifier Exists then v_qualification_ind = 1 , there is ateast 1 qualifier
1180
1181 v_qualification_ind := 1;
1182
1183 /*IF (v_hqual_exists) THEN
1184 v_qualification_ind := 1;
1185 ELSE
1186 v_qualification_ind := 3;
1187 END IF;*/
1188
1189 /*-- If PBH , then qualification_ind is null because it will have everything
1190 IF (v_line_type_code = G_PRICE_BREAK_LINE_TYPE_CODE) THEN
1191 v_qualification_ind := null;
1192 END IF;*/
1193
1194 -- Create the list line
1195 QP_Modifier_Upgrade_Util_PVT.Create_List_Line(j.creation_date,
1196 j.created_by,
1197 j.last_update_date,
1198 j.last_updated_by,
1199 j.last_update_login,
1200 j.program_application_id,
1201 j.program_id,
1202 j.program_update_date,
1203 j.request_id,
1204 v_list_header_id,
1205 v_line_type_code,
1206 j.start_date_active,
1207 j.end_date_active,
1208 i.automatic_discount_flag,
1209 v_discount_level,
1210 x_arithmetic_operator,
1211 x_operand,
1212 v_pricing_phase_id,
1213 v_incomp_grp_code,
1214 v_pricing_group_sequence, -- pricing_group_seq
1215 'N', -- accrual_flag
1216 -- l_precedence,
1217 b.COLUMN_SEQ_NUM,
1218 i.PRORATE_FLAG,
1219 'N', -- print on invoice flag
1220 nvl(i.override_allowed_flag,'N'), -- override flag
1221 v_price_break_type_code,
1222 j.context,
1223 j.attribute1,
1224 j.attribute2,
1225 j.attribute3,
1226 j.attribute4,
1227 j.attribute5,
1228 j.attribute6,
1229 j.attribute7,
1230 j.attribute8,
1231 j.attribute9,
1232 j.attribute10,
1233 j.attribute11,
1234 j.attribute12,
1235 j.attribute13,
1236 j.attribute14,
1237 j.attribute15,
1238 v_qualification_ind,
1239 v_new_line_flag,
1240 v_line_seq_num,
1241 v_old_discount_id,
1242 v_old_discount_line_id,
1243 'DISCOUNTS',
1244 x_list_line_id);
1245
1246 -- Store the List Line Id
1247 v_list_line_id := x_list_line_id;
1248 v_line_seq_num := x_list_line_id;
1249 v_new_line_flag := FALSE;
1250
1251
1252 -- Create Product/Pricing Attributes
1253 -- Create the Product/Pricing Attribute
1254
1255 /* Changes for Bug# 1872995 - When the pricing_context and pricing_attribute are null, assign null to pricing_attr_value_from and comparison_operator_code */
1256
1257 l_pricing_attr_value_from := j.entity_value;
1258 l_comparison_operator_code := G_COMPARATOR_CODE;
1259
1260 If b.descriptive_flex_context_code is NULL and b.entity_code is null then
1261 l_pricing_attr_value_from := NULL;
1262 l_comparison_operator_code := NULL;
1263 end if;
1264
1265
1266 QP_Modifier_Upgrade_Util_PVT.Create_Pricing_Attribute(j.creation_date,
1267 j.created_by,
1268 j.last_update_date,
1269 j.last_updated_by,
1270 j.last_update_login,
1271 j.program_application_id,
1272 j.program_id,
1273 j.program_update_date,
1274 j.request_id,
1275 v_list_line_id,
1276 'N',
1277 'N',
1278 'ITEM',
1279 'PRICING_ATTRIBUTE3',
1280 'ALL',
1281 NULL, --Product UOM Code
1282 b.descriptive_flex_context_code,-- Pricing Context
1283 b.entity_code,--Pricing Attribute
1284 l_pricing_attr_value_from,
1285 NULL, --Pricing_attr_value_to
1286 l_comparison_operator_code,
1287 -- nvl(x_pricing_datatype,'C'),
1288 b.FORMAT_TYPE,
1289 'C', -- product datatype
1290 v_old_discount_id,
1291 v_old_discount_line_id,
1292 'DISCOUNTS',
1293 x_pricing_attribute_id);
1294
1295 -- After discussion with Sripriya
1296
1297 --IF (v_line_type_code = G_PRICE_BREAK_LINE_TYPE_CODE) THEN
1298
1299 QP_Modifier_Upgrade_Util_PVT.Create_Pricing_Attribute(j.creation_date,
1300 j.created_by,
1301 j.last_update_date,
1302 j.last_updated_by,
1303 j.last_update_login,
1304 j.program_application_id,
1305 j.program_id,
1306 j.program_update_date,
1307 j.request_id,
1308 v_list_line_id,
1309 'N',
1310 'N',
1311 'ITEM',
1312 'PRICING_ATTRIBUTE3',
1313 'ALL',
1314 NULL, --Product UOM Code
1315 v_price_context,-- Pricing Context
1316 v_price_attribute,--Pricing Attribute
1317 NULL , --Pricing attr value from
1318 NULL, --Pricing_attr_value_to
1319 v_operator_code,
1320 'N', -- pricing datatype
1321 'C', -- product_datatype
1322 v_old_discount_id,
1323 v_old_discount_line_id,
1324 'DISCOUNTS',
1325 x_pricing_attribute_id);
1326
1327 --END IF;
1328
1329 -- Insert a record into the mapping table
1330 Create_Discount_Mapping_Record(i.discount_id,j.discount_line_id,
1331 v_list_header_id,v_list_line_id,b.descriptive_flex_context_code,v_mapping_line_type,
1332 null,null,null,null,null,null);
1333 END LOOP; -- get_contexts_for_pattrs_cur
1334 END IF;
1335
1336 -- This code needs to be executed for products , qualifiers and pricing attributes
1337 IF (j.entity_id IS NOT NULL) THEN
1338
1339 -- Determine the product/pricing contexts and attributes/qualifier context and attributes
1340 Get_Context_Attributes(j.entity_id,v_product_context,v_product_attribute,
1341 v_product_flag,v_pricing_flag,v_qualifier_flag);
1342
1343 IF (v_product_flag = TRUE) THEN
1344
1345 BEGIN
1346
1347 SELECT a.COLUMN_SEQ_NUM, nvl(c.FORMAT_TYPE,'C')
1348 INTO x_qualifier_precedence, x_product_datatype
1349 --INTO l_precedence, x_product_datatype --modified by dhgupta for 2992566
1350 FROM FND_DESCR_FLEX_COLUMN_USAGES a , fnd_flex_value_sets c
1351 WHERE a.DESCRIPTIVE_FLEXFIELD_NAME = 'QP_ATTR_DEFNS_PRICING'
1352 AND a.APPLICATION_ID = 661 --(QP). Added for bug 5030757
1353 AND a.DESCRIPTIVE_FLEX_CONTEXT_CODE = v_product_context
1354 AND a.APPLICATION_COLUMN_NAME = v_product_attribute
1355 AND a.FLEX_VALUE_SET_ID = c.FLEX_VALUE_SET_ID(+);
1356
1357 /* flex
1358
1359 BEGIN
1360 QP_UTIL.Get_Prod_Flex_Properties(v_product_context,
1361 v_product_attribute,
1362 j.entity_value,
1363 x_product_datatype,
1364 x_product_precedence,
1365 x_error_code);
1366 IF (x_error_code <> 0 ) THEN
1367 QP_Util.Log_Error(p_id1 => j.entity_id,
1368 p_error_type => 'PROD_FLEX_PROPERTIES',
1369 p_error_desc =>
1370 'Please Check The Context: ' || v_product_context ||
1371 ' Attribute: ' || v_product_attribute,
1372 p_error_module => 'QP_Util.Get_Prod_Flex_Properties');
1373 END IF;
1374 l_precedence := x_product_precedence;
1375 EXCEPTION
1376 WHEN OTHERS THEN
1380 p_error_type => 'PROD_FLEX_PROPERTIES',
1377 err_msg := SQLERRM;
1378 rollback;
1379 QP_Util.Log_Error(p_id1 => j.entity_id,
1381 p_error_desc => 'Product Entity ' || err_msg,
1382 p_error_module => 'QP_Util.Get_Prod_Flex_Properties');
1383 raise;
1384 END;
1385
1386 flex */
1387
1388 EXCEPTION
1389 WHEN OTHERS THEN
1390 err_msg := SQLERRM;
1391 rollback;
1392 QP_Util.Log_Error(p_id1 => j.entity_id,
1393 p_error_type => 'PROD_FLEX_PROPERTIES',
1394 p_error_desc => 'Product Entity ' || err_msg,
1395 p_error_module => 'Prod_Flex_Properties');
1396 raise;
1397 END;
1398
1399 ELSIF (v_qualifier_flag = TRUE) THEN
1400
1401 BEGIN
1402
1403 SELECT a.COLUMN_SEQ_NUM, nvl(c.FORMAT_TYPE,'C')
1404 INTO x_qualifier_precedence, x_qualifier_datatype
1405 FROM FND_DESCR_FLEX_COLUMN_USAGES a , fnd_flex_value_sets c
1406 WHERE a.DESCRIPTIVE_FLEXFIELD_NAME = 'QP_ATTR_DEFNS_QUALIFIER'
1407 AND a.APPLICATION_ID = 661 --(QP). Added for bug 5030757
1408 AND a.DESCRIPTIVE_FLEX_CONTEXT_CODE = v_product_context
1409 AND a.APPLICATION_COLUMN_NAME = v_product_attribute
1410 AND a.FLEX_VALUE_SET_ID = c.FLEX_VALUE_SET_ID(+);
1411
1412 /* flex
1413
1414 BEGIN
1415 QP_UTIL.Get_Qual_Flex_Properties(v_product_context,
1416 v_product_attribute,
1417 j.entity_value,
1418 x_qualifier_datatype,
1419 x_qualifier_precedence,
1420 x_error_code);
1421 l_precedence := x_qualifier_precedence;
1422 EXCEPTION
1423 WHEN OTHERS THEN
1424 err_msg := SQLERRM;
1425 rollback;
1426 QP_Util.Log_Error(p_id1 => j.entity_id,
1427 p_error_type => 'QUAL_FLEX_PROPERTIES',
1428 p_error_desc => 'Qualifier Entity ' || err_msg,
1429 p_error_module => 'QP_Util.Get_Qual_Flex_Properties');
1430 raise;
1431 END;
1432
1433 flex */
1434 EXCEPTION
1435 WHEN OTHERS THEN
1436 err_msg := SQLERRM;
1437 rollback;
1438 QP_Util.Log_Error(p_id1 => j.entity_id,
1439 p_error_type => 'QUAL_FLEX_PROPERTIES',
1440 p_error_desc => 'Qualifier Entity ' || err_msg,
1441 p_error_module => 'Qual_Flex_Properties');
1442 raise;
1443 END;
1444
1445
1446 END IF;
1447
1448 -- Set the lines flag to TRUE indicating that there are discount lines
1449 v_lines_flag := TRUE;
1450
1451 -- Entities other than that we are handling
1452 IF (v_product_flag = FALSE AND v_qualifier_flag = FALSE AND v_pricing_flag = FALSE) THEN
1453 -- Insert a record into the mapping table
1454 Create_Discount_Mapping_Record(i.discount_id,NULL,v_list_header_id,NULL,null,'L',
1455 null,null,null,null,null,null);
1456
1457 -- Added after discounts upgrade review by Jay,Alison and Ravi
1458 QP_Util.Log_Error(p_id1 => j.entity_id,
1459 p_error_type => 'ENTITY_NOT_HANDLED',
1460 p_error_desc => 'This entity is not handled by the Upgrade:' || j.entity_id,
1461 p_error_module => 'Create_Discounts');
1462 END IF;
1463 END IF;
1464
1465 -- If percent or amount or price is not null then it is a regular discount line
1466 -- If percent and amount and price is null , then it is a price break.
1467 -- Execute this logic only if it is a product or qualifier. This is because we are not dealing
1468 -- with any entities other than those handled in Get_Context_Attributes procedure
1469
1470 -- Determine the Arthimetic Operator and Operand in qp_list_lines from Percent and Amount
1471 Get_Percent(j.percent , j.amount , j.price , x_operand , x_arithmetic_operator);
1472
1473 IF (j.entity_id IS NOT NULL AND (v_product_flag = TRUE OR v_qualifier_flag = TRUE OR v_pricing_flag = TRUE))
1474 THEN
1475 IF (j.percent IS NOT NULL OR j.amount IS NOT NULL OR j.price IS NOT NULL) THEN
1476 IF (x_arithmetic_operator = 'AMT') THEN
1477 IF (x_operand < 0) THEN
1478 v_line_type_code := G_SURCHARGE_CODE;
1479 x_operand := -(x_operand);
1480 ELSE
1481 v_line_type_code := G_LIST_LINE_TYPE_CODE;
1482 END IF;
1483 ELSE
1484 v_line_type_code := G_LIST_LINE_TYPE_CODE;
1485 END IF;
1486 v_price_context := NULL;
1487 v_price_attribute := NULL;
1488 v_price_datatype := NULL;
1489 v_operator_code := NULL;
1490 v_price_break_type_code := NULL;
1491 v_mapping_line_type := 'L'; -- Regular discount line
1492 ELSE
1493 v_line_type_code := G_PRICE_BREAK_LINE_TYPE_CODE;
1494 v_price_context := 'VOLUME';
1495 v_price_attribute := 'PRICING_ATTRIBUTE10';
1496 v_operator_code := 'BETWEEN';
1497 v_price_datatype := 'N';
1498 v_price_break_type_code := 'POINT';
1499 v_mapping_line_type := 'Q'; -- Indicates that this line has price breaks
1500 END IF;
1501
1502 -- Find the uom for 'AMT' and 'NEWPRICE' , for PERCENT it is null
1503 IF(x_arithmetic_operator in ('AMT','NEWPRICE') and v_product_flag = TRUE) THEN
1504 OPEN get_uom_for_item_cur(i.price_list_id,j.entity_value);
1505 FETCH get_uom_for_item_cur INTO v_item_uom;
1506 CLOSE get_uom_for_item_cur;
1507 END IF;
1508
1509 -- Create the list line if product or qualifier, because pricing is already taken care of
1510 -- in get_contexts_for_pattrs_cur
1511
1512 -- v_product_flag = TRUE
1513 -- For entity ids related to Products
1514 -- If Qualifier Exists then v_qualification_ind = 1, atleast there is 1 qualifier(Price list)
1515 -- For PBH parent line it is 1 , and not null because it is not there in the
1519 v_qualification_ind := 1;
1516 -- from_rltd_modifier_id of the qp_rltd_modifiers table
1517
1518 IF (v_product_flag = TRUE) THEN
1520 END IF;
1521
1522 /*IF (v_product_flag = TRUE) THEN
1523 IF (v_hqual_exists) THEN
1524 v_qualification_ind := 1;
1525 ELSE
1526 v_qualification_ind := 3;
1527 END IF;
1528 END IF;*/
1529
1530 -- v_qualifier_flag = TRUE
1531 -- For entity id's relating to qualifiers(line level)
1532
1533 IF (v_qualifier_flag = TRUE) THEN
1534 IF (v_line_type_code = G_PRICE_BREAK_LINE_TYPE_CODE) THEN
1535 v_qualification_ind := 1;
1536 ELSE
1537 v_qualification_ind := 5;
1538 END IF;
1539 END IF;
1540
1541 /*-- If PBH , then qualification_ind is null because it will have everything
1542 IF (v_line_type_code = G_PRICE_BREAK_LINE_TYPE_CODE) THEN
1543 v_qualification_ind := null;
1544 END IF;*/
1545
1546 IF (v_product_flag = TRUE OR v_qualifier_flag = TRUE) THEN
1547 QP_Modifier_Upgrade_Util_PVT.Create_List_Line(j.creation_date,
1548 j.created_by,
1549 j.last_update_date,
1550 j.last_updated_by,
1551 j.last_update_login,
1552 j.program_application_id,
1553 j.program_id,
1554 j.program_update_date,
1555 j.request_id,
1556 v_list_header_id,
1557 v_line_type_code,
1558 j.start_date_active,
1559 j.end_date_active,
1560 i.automatic_discount_flag,
1561 v_discount_level,
1562 x_arithmetic_operator,
1563 x_operand,
1564 v_pricing_phase_id,
1565 v_incomp_grp_code,
1566 v_pricing_group_sequence, -- pricing_group_seq
1567 'N', -- accrual_flag
1568 -- l_precedence,
1569 x_qualifier_precedence,
1570 i.PRORATE_FLAG,
1571 'N', -- print on invoice flag
1572 nvl(i.override_allowed_flag,'N'), -- override flag
1573 v_price_break_type_code,
1574 j.context,
1575 j.attribute1,
1576 j.attribute2,
1577 j.attribute3,
1578 j.attribute4,
1579 j.attribute5,
1580 j.attribute6,
1581 j.attribute7,
1582 j.attribute8,
1583 j.attribute9,
1584 j.attribute10,
1585 j.attribute11,
1586 j.attribute12,
1587 j.attribute13,
1588 j.attribute14,
1589 j.attribute15,
1590 v_qualification_ind,
1591 v_new_line_flag,
1592 v_line_seq_num,
1593 v_old_discount_id,
1594 v_old_discount_line_id,
1595 'DISCOUNTS',
1596 x_list_line_id);
1597
1598 -- Store the List Line Id
1599 v_list_line_id := x_list_line_id;
1600 v_line_seq_num := x_list_line_id;
1601 v_new_line_flag := FALSE;
1602
1603 -- Insert a record into the mapping table
1604 Create_Discount_Mapping_Record(i.discount_id,j.discount_line_id,
1605 v_list_header_id,v_list_line_id,null,v_mapping_line_type, null,null,null,null,null,null);
1606
1607 END IF; -- v_product_flag = TRUE or v_qualfier_flag = TRUE
1608
1609 -- Create Product/Pricing Attributes
1610
1611 -- If v_product_flag is TRUE it is a product attribute ex: Item , Item Category
1612
1613 IF (v_product_flag = TRUE) THEN
1614
1615 -- Create the Product/Pricing Attribute
1616 QP_Modifier_Upgrade_Util_PVT.Create_Pricing_Attribute(j.creation_date,
1617 j.created_by,
1618 j.last_update_date,
1619 j.last_updated_by,
1620 j.last_update_login,
1621 j.program_application_id,
1622 j.program_id,
1623 j.program_update_date,
1624 j.request_id,
1625 v_list_line_id,
1626 'N',
1627 'N',
1628 v_product_context,
1629 v_product_attribute,
1630 j.entity_value,
1631 v_item_uom, --Product UOM Code
1632 v_price_context ,
1633 v_price_attribute,
1634 NULL,--Pricing attr value from
1635 NULL, --Pricing_attr_value_to
1636 v_operator_code,
1637 v_price_datatype, -- pricing datatype
1638 nvl(x_product_datatype,'C'),
1639 v_old_discount_id,
1640 v_old_discount_line_id,
1641 'DISCOUNTS',
1642 x_pricing_attribute_id);
1643 v_item_uom := NULL; -- Re-init
1644 END IF; -- v_product_flag = TRUE
1645
1646 -- If v_qualifier_flag= TRUE then create a Qualifier
1647 -- Ex: Order Type , Customer PO , Agreement Type , Agreement Name
1648 -- v_qualifier_flag = TRUE indicates that it is a Qualifier with one pricing attribute Units or
1649 -- Dollars.
1650 -- In this case , if it is a discount line , there will not be any pricing attributes for this
1651 -- Qualifier. Ex: Give 2% discount where Order Type = 'Standard'. In this case Order Type will
1652 -- be a Qualifier and there will be a discount line 2% in qp_list_lines.
1653
1654 IF (v_qualifier_flag = TRUE) THEN
1655
1656
1657 -- Create the Qualifier
1658 QP_Modifier_Upgrade_Util_PVT.Create_Qualifier(i.creation_date,
1659 i.created_by,
1660 i.last_update_date,
1661 i.last_updated_by,
1662 i.last_update_login,
1663 i.program_application_id,
1664 i.program_id,
1665 i.program_update_date,
1666 i.request_id,
1667 'N',
1668 G_COMPARATOR_CODE,
1669 v_product_context,
1670 v_product_attribute,
1671 j.entity_value,
1675 x_qualifier_precedence,
1672 v_cust_qualifier_grp_no, --Initially 0 , so create new grp no
1673 v_list_header_id,
1674 v_list_line_id,
1676 nvl(x_qualifier_datatype,'C'),
1677 null,-- start_date_active
1678 null,-- end_date_active
1679 null,
1680 null,
1681 null,
1682 null,
1683 null,
1684 null,
1685 null,
1686 null,
1687 null,
1688 null,
1689 null,
1690 null,
1691 null,
1692 null,
1693 null,
1694 null,
1695 v_old_discount_id,
1696 'DISCOUNTS',
1697 x_qualifier_grouping_no);
1698 END IF; -- v_qualifier_flag = TRUE
1699
1700 -- Price Breaks for the Discount Line
1701
1702 IF (j.amount IS NULL AND j.percent IS NULL AND j.price IS NULL) THEN
1703
1704 FOR k IN get_price_break_lines(j.discount_line_id)
1705 LOOP
1706
1707 -- Determine the Arthimetic Operator and Operand in qp_list_lines from Percent and Amount
1708 Get_Percent(k.percent,k.amount,k.price,x_operand , x_arithmetic_operator);
1709
1710 IF (x_arithmetic_operator = 'AMT') THEN
1711 IF (x_operand < 0) THEN
1712 v_line_type_code := G_SURCHARGE_CODE;
1713 x_operand := -(x_operand);
1714 ELSE
1715 v_line_type_code := G_LIST_LINE_TYPE_CODE;
1716 END IF;
1717 ELSE
1718 v_line_type_code := G_LIST_LINE_TYPE_CODE;
1719 END IF;
1720
1721 -- Determine the pricing context and attribute
1722 IF (UPPER(k.method_type_code) = G_PRICING_ATTRIBUTE_UNITS) THEN
1723
1724 -- Get the context and attribute for Units
1725 QP_UTIL_Get_Context_Attribute(G_PRICING_ATTRIBUTE_UNITS,v_price_break_context,v_price_break_attribute);
1726
1727 ELSIF (UPPER(k.method_type_code) = G_PRICING_ATTRIBUTE_DOLLARS) THEN
1728
1729 -- Get the context and attribute for Dollars
1730 QP_UTIL_Get_Context_Attribute(G_PRICING_ATTRIBUTE_DOLLARS,v_price_break_context,v_price_break_attribute);
1731 END IF;
1732
1733 -- PBH Children
1734 v_qualification_ind := 2;
1735
1736 -- Create a list line for each price break line
1737 QP_Modifier_Upgrade_Util_PVT.Create_List_Line(k.creation_date,
1738 k.created_by,
1739 k.last_update_date,
1740 k.last_updated_by,
1741 k.last_update_login,
1742 k.program_application_id,
1743 k.program_id,
1744 k.program_update_date,
1745 k.request_id,
1746 v_list_header_id,
1747 v_line_type_code,
1748 k.start_date_active,
1749 k.end_date_active,
1750 i.automatic_discount_flag,
1751 v_discount_level,
1752 x_arithmetic_operator,
1753 x_operand,
1754 v_pricing_phase_id,
1755 v_incomp_grp_code,
1756 v_pricing_group_sequence, -- pricing_group_seq
1757 'N', -- accrual_flag
1758 -- l_precedence,
1759 x_qualifier_precedence,
1760 i.PRORATE_FLAG,
1761 'N', -- print on invoice flag
1762 nvl(i.override_allowed_flag,'N'), -- override flag
1763 'POINT',
1764 k.context,
1765 k.attribute1,
1766 k.attribute2,
1767 k.attribute3,
1768 k.attribute4,
1769 k.attribute5,
1770 k.attribute6,
1771 k.attribute7,
1772 k.attribute8,
1773 k.attribute9,
1774 k.attribute10,
1775 k.attribute11,
1776 k.attribute12,
1777 k.attribute13,
1778 k.attribute14,
1779 k.attribute15,
1780 v_qualification_ind,
1781 v_new_line_flag,
1782 v_line_seq_num,
1783 v_old_discount_id,
1784 v_old_discount_line_id,
1785 'DISCOUNTS',
1786 x_list_line_id);
1787
1788 -- Store the List Line Id
1789 v_price_break_line_id := x_list_line_id;
1790 v_line_seq_num := x_list_line_id;
1791 v_new_line_flag := FALSE;
1792
1793 -- Insert a record into related modifier table
1794 QP_Modifier_Upgrade_Util_PVT.Create_Related_Modifier(k.creation_date,
1795 k.created_by,
1796 k.last_update_date,
1797 k.last_updated_by,
1798 k.last_update_login,
1799 v_list_line_id,
1800 v_price_break_line_id,
1801 'PRICE BREAK',
1802 v_old_discount_id,
1803 v_old_discount_line_id,
1804 'DISCOUNTS',
1805 x_rltd_modifier_id);
1806
1807
1808 -- If is a product then there will be only 1 record in qp_pricing_attributes with both
1809 -- Product and Pricing info in 1 record
1810
1811 IF (v_product_flag = TRUE ) THEN
1812
1813 -- Create the Product/Pricing Attribute
1814
1815 QP_Modifier_Upgrade_Util_PVT.Create_Pricing_Attribute_Break(k.creation_date,
1816 k.created_by,
1817 k.last_update_date,
1818 k.last_updated_by,
1819 k.last_update_login,
1820 k.program_application_id,
1821 k.program_id,
1822 k.program_update_date,
1823 k.request_id,
1824 v_price_break_line_id,
1825 'N',
1826 'N',
1827 v_product_context,
1828 v_product_attribute,
1829 j.entity_value,
1830 k.unit_code, /* Product UOM Code */
1831 v_price_break_context,
1832 v_price_break_attribute,
1833 k.price_break_lines_low_range,
1834 k.price_break_lines_high_range,
1835 'BETWEEN',
1836 'N', -- pricing_datatype
1840 'DISCOUNTS',
1837 'C', -- product datatype
1838 v_old_discount_id,
1839 v_old_discount_line_id,
1841 x_pricing_attribute_id);
1842 END IF; /* v_product_flag = TRUE */
1843
1844 -- This record is needed , if this is a break on qualifier
1845 -- This record the product record in qp_pricing_attributes for PBH Line(Parent Line)
1846 -- Insert only 1 record
1847 IF (v_qualifier_flag = TRUE and v_break_count = 0 ) THEN
1848 -- Create the Product/Pricing Attribute
1849 QP_Modifier_Upgrade_Util_PVT.Create_Pricing_Attribute(j.creation_date,
1850 j.created_by,
1851 j.last_update_date,
1852 j.last_updated_by,
1853 j.last_update_login,
1854 j.program_application_id,
1855 j.program_id,
1856 j.program_update_date,
1857 j.request_id,
1858 v_list_line_id,
1859 'N',
1860 'N',
1861 'ITEM',
1862 'PRICING_ATTRIBUTE3',
1863 'ALL',
1864 NULL, --Product UOM Code
1865 v_price_break_context,
1866 v_price_break_attribute,
1867 NULL,--Pricing attr value from
1868 NULL, --Pricing_attr_value_to
1869 'BETWEEN',
1870 'N', -- pricing datatype
1871 'C',
1872 v_old_discount_id,
1873 v_old_discount_line_id,
1874 'DISCOUNTS',
1875 x_pricing_attribute_id);
1876 END IF;
1877
1878 IF (v_qualifier_flag = TRUE OR v_pricing_flag = TRUE) THEN
1879
1880
1881 -- Create the Pricing Attribute
1882 QP_Modifier_Upgrade_Util_PVT.Create_Pricing_Attribute_Break(k.creation_date,
1883 k.created_by,
1884 k.last_update_date,
1885 k.last_updated_by,
1886 k.last_update_login,
1887 k.program_application_id,
1888 k.program_id,
1889 k.program_update_date,
1890 k.request_id,
1891 v_price_break_line_id,
1892 'N',
1893 'N',
1894 'ITEM',
1895 'PRICING_ATTRIBUTE3',
1896 'ALL',
1897 k.unit_code, /* Product UOM Code */
1898 v_price_break_context,
1899 v_price_break_attribute,
1900 k.price_break_lines_low_range,
1901 k.price_break_lines_high_range,
1902 'BETWEEN',
1903 'N', -- pricing datatype
1904 'C', -- product datatype
1905 v_old_discount_id,
1906 v_old_discount_line_id,
1907 'DISCOUNTS',
1908 x_pricing_attribute_id);
1909 END IF; /* v_qualifier_flag = TRUE OR v_pricing_flag = TRUE */
1910
1911
1912 -- Insert a record into the mapping table
1913 -- Type = 'B' is the actual price break line
1914
1915 Create_Discount_Mapping_Record(i.discount_id,j.discount_line_id,v_list_header_id,
1916 v_price_break_line_id,null,'B',k.price_break_lines_low_range,
1917 k.price_break_lines_high_range,k.method_type_code,
1918 k.percent,k.amount , k.price);
1919
1920
1921 -- Store the attribute id's
1922 v_pricing_attribute_id := x_pricing_attribute_id;
1923 v_break_count := v_break_count + 1;
1924 v_unit_code := k.unit_code;
1925 END LOOP; -- Price Break Lines Cursor
1926
1927 -- Update the Unit Code on the PBH line(parent Line)
1928 -- makes PRICING_ATTRIBUTE10 to PRICING_ATTRIBUTE12(if needed)
1929 UPDATE QP_PRICING_ATTRIBUTES
1930 SET PRODUCT_UOM_CODE = v_unit_code,
1931 PRICING_ATTRIBUTE = v_price_break_attribute
1932 WHERE LIST_LINE_ID = v_list_line_id;
1933 v_break_count := 0; -- Re-init
1934 END IF; -- if (j.percent IS NULL AND j.amount is NULL AND j.price is NULL
1935 END IF; -- j.entity_id IS NOT NULL AND v_product_flag = TRUE or v_qualifier_flag = TRUE
1936
1937 -- Added after discounts upgrade review by Jay,Alison and Ravi
1938 IF (v_contexts_flag = FALSE and v_entity_id IS NOT NULL) THEN
1939 QP_Util.Log_Error(p_id1 => j.discount_line_id,
1940 p_error_type => 'NO_PRICING_CONTEXTS_EXIST',
1941 p_error_desc => 'There are no contexts for the entity id : ' || v_entity_id ||
1942 ' of discount id: ' || i.discount_id || ' and discount line id: '||
1943 j.discount_line_id,
1944 p_error_module => 'Create_Discounts');
1945 END IF;
1946
1947 END LOOP; -- Discount Lines Cursor
1948
1949 -- If there are no discount lines insert record into mapping table
1950 IF (v_lines_flag = FALSE ) THEN
1951 -- Insert a record into the mapping table
1952 Create_Discount_Mapping_Record(i.discount_id,NULL,v_list_header_id,NULL,null,'O',
1953 null,null,null,null,null,null);
1954
1955 -- Added after discounts upgrade review by Jay,Alison and Ravi
1956 QP_Util.Log_Error(p_id1 => i.discount_id,
1957 p_error_type => 'NO_DISCOUNT_LINES',
1958 p_error_desc => 'There are no discount lines for this discount id:' || i.discount_id,
1959 p_error_module => 'Create_Discounts');
1960 END IF;
1961
1962
1963 END IF; /* are_there_discount_lines = 'N' */
1964 -- Reinit the qualifier grouping no
1965 v_qualifier_grouping_no := 0;
1966 commit;
1967 END LOOP; /* Discounts cursor */
1968
1969 -- Discounts Not Migrated
1970 FOR i in get_discounts_not_migrated_cur
1971 LOOP
1972 QP_Util.Log_Error(p_id1 => i.discount_id,
1973 p_id2 => i.price_list_id,
1974 p_error_type => 'DISCOUNTS_NOT_MIGRATED',
1975 p_error_desc =>
1976 'Discount Id ' || i.discount_id || ' is not migrated as there is problem with
1977 Price List Id ' || i.price_list_id,
1978 p_error_module => 'Create_Discounts');
1979 END LOOP;
1980
1981
1982 EXCEPTION
1983 WHEN OTHERS THEN
1984 err_msg := SQLERRM;
1985 rollback;
1986 QP_Util.Log_Error(p_id1 => v_old_discount_id,
1987 p_id2 => v_old_discount_line_id,
1988 p_error_type => 'DISCOUNTS',
1989 p_error_desc => err_msg,
1990 p_error_module => 'Create_Discounts');
1991
1992 -- Discounts Not Migrated
1993 FOR i in get_discounts_not_migrated_cur
1994 LOOP
1995 QP_Util.Log_Error(p_id1 => i.discount_id,
1996 p_id2 => i.price_list_id,
1997 p_error_type => 'DISCOUNTS_NOT_MIGRATED',
1998 p_error_desc =>
1999 'Discount Id ' || i.discount_id || ' is not migrated as there is problem with
2000 Price List Id ' || i.price_list_id,
2001 p_error_module => 'Create_Discounts');
2002 END LOOP;
2003
2004 -- commented the following raise statement for bug 2491781 as the calling program does not have
2005 -- any exception handling block to handle this raise statement.
2006 --raise;
2007
2008 END Create_Discounts;
2009
2010 END QP_Modifier_Upgrade_PVT;