[Home] [Help]
PACKAGE BODY: APPS.QP_MODIFIER_UPGRADE_UTIL_PVT
Source
1 PACKAGE BODY QP_Modifier_Upgrade_Util_PVT AS
2 /* $Header: QPXVUTLB.pls 120.2 2006/03/21 11:16:40 rnayani noship $ */
3
4 PROCEDURE Create_List_Header(
5 p_creation_date DATE,
6 p_created_by NUMBER,
7 p_last_update_date DATE,
8 p_last_updated_by NUMBER,
9 p_last_update_login NUMBER,
10 p_list_type_code VARCHAR2,
11 p_start_date_effective DATE,
12 p_end_date_effective DATE,
13 p_automatic_flag VARCHAR2,
14 p_discount_lines_flag VARCHAR2,
15 p_currency_code VARCHAR2,
16 p_name VARCHAR2,
17 p_description VARCHAR2,
18 p_version_no NUMBER,
19 p_ask_for_flag VARCHAR2,
20 p_source_system_code VARCHAR2,
21 p_active_flag VARCHAR2,
22 p_gsa_indicator VARCHAR2,
23 p_context VARCHAR2,
24 p_attribute1 VARCHAR2,
25 p_attribute2 VARCHAR2,
26 p_attribute3 VARCHAR2,
27 p_attribute4 VARCHAR2,
28 p_attribute5 VARCHAR2,
29 p_attribute6 VARCHAR2,
30 p_attribute7 VARCHAR2,
31 p_attribute8 VARCHAR2,
32 p_attribute9 VARCHAR2,
33 p_attribute10 VARCHAR2,
34 p_attribute11 VARCHAR2,
35 p_attribute12 VARCHAR2,
36 p_attribute13 VARCHAR2,
37 p_attribute14 VARCHAR2,
38 p_attribute15 VARCHAR2,
39 p_new_flag BOOLEAN,
40 p_seq_num NUMBER,
41 p_id1 VARCHAR2,
42 p_type VARCHAR2,
43 x_list_header_id OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
44
45 err_num NUMBER;
46 err_msg VARCHAR2(2000);
47 v_seq_num NUMBER;
48 v_name VARCHAR2(240);
49
50 CURSOR get_duplicate_name_cur IS
51 SELECT distinct name
52 FROM qp_list_headers_tl
53 WHERE name = p_name;
54
55 BEGIN
56
57 IF (p_new_flag = TRUE) THEN
58 SELECT SO_PRICE_LISTS_S.nextval
59 INTO v_seq_num
60 FROM DUAL;
61 v_seq_num := v_seq_num + 5000;
62 ELSE
63 v_seq_num := p_seq_num + 1;
64 END IF;
65
66 INSERT INTO QP_LIST_HEADERS_B (
67 LIST_HEADER_ID, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
68 LAST_UPDATE_LOGIN, LIST_TYPE_CODE, START_DATE_ACTIVE, END_DATE_ACTIVE,
69 AUTOMATIC_FLAG,DISCOUNT_LINES_FLAG, CURRENCY_CODE,ASK_FOR_FLAG,
70 SOURCE_SYSTEM_CODE, ACTIVE_FLAG,GSA_INDICATOR,CONTEXT,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,
71 ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,
72 ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15)
73
74 VALUES(
75 QP_LIST_HEADERS_B_S.nextval,p_creation_date, p_created_by,p_last_update_date,p_last_updated_by,
76 p_last_update_login,p_list_type_code,p_start_date_effective,p_end_date_effective,
77 p_automatic_flag,p_discount_lines_flag,p_currency_code,p_ask_for_flag,
78 p_source_system_code,p_active_flag,p_gsa_indicator,p_context,p_attribute1,p_attribute2,p_attribute3,p_attribute4,
79 p_attribute5,p_attribute6,p_attribute7,p_attribute8,p_attribute9,p_attribute10,
80 p_attribute11,p_attribute12,p_attribute13,p_attribute14,p_attribute15);
81
82 -- Store the current list header id
83 SELECT QP_LIST_HEADERS_B_S.currval
84 INTO x_list_header_id
85 FROM DUAL;
86
87 --x_list_header_id := v_seq_num;
88
89 v_name := p_name;
90
91 OPEN get_duplicate_name_cur;
92 FETCH get_duplicate_name_cur INTO v_name;
93 CLOSE get_duplicate_name_cur;
94
95 IF (v_name IS NOT NULL) THEN
96 v_name := v_name || ' ' || p_id1;
97 ELSE
98 v_name := p_name;
99 END IF;
100
101 INSERT INTO QP_LIST_HEADERS_TL (
102 LIST_HEADER_ID, LANGUAGE, SOURCE_LANG, NAME, DESCRIPTION,
103 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
104 VERSION_NO)
105
106 SELECT
107 x_list_header_id, l.LANGUAGE_CODE, userenv('LANG'), v_name, p_description,
108 p_creation_date, p_created_by, p_last_update_date, p_last_updated_by, p_last_update_login,
109 p_version_no
110 FROM FND_LANGUAGES l
111 WHERE l.INSTALLED_FLAG in ('I', 'B')
112 AND NOT EXISTS
113 (SELECT NULL
114 FROM QP_LIST_HEADERS_TL t
115 WHERE t.LIST_HEADER_ID = x_list_header_id
116 AND t.LANGUAGE = l.LANGUAGE_CODE);
117
118 EXCEPTION
119 WHEN OTHERS THEN
120 err_msg := SQLERRM;
121 rollback;
122 QP_Util.Log_Error(p_id1 => p_id1,
123 p_error_type =>p_type,
124 p_error_desc => err_msg,
125 p_error_module => 'Create_List_Header');
126 raise;
127
128 END Create_List_Header;
129
130 PROCEDURE Create_List_Line(
131 p_creation_date DATE,
132 p_created_by NUMBER,
133 p_last_update_date DATE,
134 p_last_updated_by NUMBER,
135 p_last_update_login NUMBER,
136 p_program_application_id NUMBER,
137 p_program_id NUMBER,
138 p_program_update_date DATE,
139 p_request_id NUMBER,
140 p_list_header_id NUMBER,
141 p_list_line_type_code VARCHAR2,
142 p_start_date_effective DATE,
143 p_end_date_effective DATE,
144 p_automatic_flag VARCHAR2,
145 p_modifier_level_code VARCHAR2,
146 p_arithmetic_operator VARCHAR2,
147 p_operand NUMBER,
148 p_pricing_phase_id NUMBER,
149 p_incomp_grp_code VARCHAR2,
150 p_pricing_grp_seq NUMBER,
151 p_accrual_flag VARCHAR2,
152 p_product_precedence NUMBER,
153 p_proration_type_code VARCHAR2,
154 p_print_on_invoice_flag VARCHAR2,
155 p_override_flag VARCHAR2,
156 p_price_break_type_code VARCHAR2,
157 p_context VARCHAR2,
158 p_attribute1 VARCHAR2,
159 p_attribute2 VARCHAR2,
160 p_attribute3 VARCHAR2,
161 p_attribute4 VARCHAR2,
162 p_attribute5 VARCHAR2,
163 p_attribute6 VARCHAR2,
164 p_attribute7 VARCHAR2,
165 p_attribute8 VARCHAR2,
166 p_attribute9 VARCHAR2,
167 p_attribute10 VARCHAR2,
168 p_attribute11 VARCHAR2,
169 p_attribute12 VARCHAR2,
170 p_attribute13 VARCHAR2,
171 p_attribute14 VARCHAR2,
172 p_attribute15 VARCHAR2,
173 p_qualification_ind NUMBER, --2422176
174 --p_qualification_ind NUMBER := null,
175 p_new_flag BOOLEAN,
176 p_seq_num NUMBER,
177 p_id1 VARCHAR2,
178 p_id2 VARCHAR2 := null,
179 p_type VARCHAR2,
180 x_list_line_id OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
181
182 err_num NUMBER;
183 err_msg VARCHAR(100);
184 v_seq_num NUMBER;
185 v_list_line_seq NUMBER;
186
187 BEGIN
188 IF (p_new_flag = TRUE) THEN
189 SELECT SO_PRICE_LIST_LINES_S.nextval
190 INTO v_seq_num
191 FROM DUAL;
192 v_seq_num := v_seq_num + 5000;
193 ELSE
194 v_seq_num := p_seq_num + 1;
195 END IF;
196
197 SELECT QP_LIST_LINES_S.nextval
198 INTO v_list_line_seq
199 FROM DUAL;
200
201 INSERT INTO QP_LIST_LINES (
202 LIST_LINE_ID,LIST_LINE_NO, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
203 LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID,
204 LIST_HEADER_ID, LIST_LINE_TYPE_CODE, START_DATE_ACTIVE, END_DATE_ACTIVE,
205 AUTOMATIC_FLAG, MODIFIER_LEVEL_CODE, ARITHMETIC_OPERATOR, OPERAND,PRICING_PHASE_ID,
206 INCOMPATIBILITY_GRP_CODE,PRICING_GROUP_SEQUENCE,ACCRUAL_FLAG,PRODUCT_PRECEDENCE,QUALIFICATION_IND,
207 PRORATION_TYPE_CODE,PRINT_ON_INVOICE_FLAG,OVERRIDE_FLAG , PRICE_BREAK_TYPE_CODE ,
208 CONTEXT,ATTRIBUTE1,ATTRIBUTE2, ATTRIBUTE3,ATTRIBUTE4, ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,
209 ATTRIBUTE9,ATTRIBUTE10, ATTRIBUTE11,ATTRIBUTE12, ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15
210 --ENH Upgrade BOAPI for orig_sys...ref RAVI
211 ,ORIG_SYS_LINE_REF
212 ,ORIG_SYS_HEADER_REF
213 )
214
215 VALUES (
216 v_list_line_seq, v_list_line_seq, p_creation_date,p_created_by,p_last_update_date,p_last_updated_by,
217 p_last_update_login, p_program_application_id,p_program_id,p_program_update_date,p_request_id,
218 p_list_header_id,p_list_line_type_code,p_start_date_effective,p_end_date_effective,
219 p_automatic_flag,p_modifier_level_code,p_arithmetic_operator,p_operand,p_pricing_phase_id,
220 p_incomp_grp_code,p_pricing_grp_seq,p_accrual_flag,p_product_precedence, p_qualification_ind,
221 p_proration_type_code, p_print_on_invoice_flag,p_override_flag , p_price_break_type_code ,
222 p_context, p_attribute1,p_attribute2, p_attribute3,p_attribute4, p_attribute5, p_attribute6,
223 p_attribute7, p_attribute8,p_attribute9,p_attribute10, p_attribute11,p_attribute12, p_attribute13,
224 p_attribute14, p_attribute15
225 --ENH Upgrade BOAPI for orig_sys...ref RAVI
226 ,to_char(v_list_line_seq)
227 ,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=p_list_header_id)
228 );
229
230 -- Store the new list line id
231
232 SELECT QP_LIST_LINES_S.currval
233 INTO x_list_line_id
234 FROM DUAL;
235
236 --x_list_line_id := v_seq_num;
237
238
239 EXCEPTION
240 WHEN OTHERS THEN
241 err_msg := SQLERRM;
242 rollback;
243 QP_Util.Log_Error(p_id1 => p_id1,
244 p_id2 => p_id2,
245 p_error_type => p_type,
246 p_error_desc => err_msg,
247 p_error_module => 'Create_List_Line');
248 raise;
249
250 END Create_List_Line;
251
252 PROCEDURE Create_Qualifier (
253 p_creation_date DATE,
254 p_created_by NUMBER,
255 p_last_update_date DATE,
256 p_last_updated_by NUMBER,
257 p_last_update_login NUMBER,
258 p_program_application_id NUMBER,
259 p_program_id NUMBER,
260 p_program_update_date DATE,
261 p_request_id NUMBER,
262 p_excluder_flag VARCHAR2,
263 p_comparision_operator_code VARCHAR2,
264 p_qualifier_context VARCHAR2,
265 p_qualifier_attribute VARCHAR2,
266 p_qualifier_attr_value VARCHAR2,
267 p_qualifier_grouping_no NUMBER,
268 p_list_header_id NUMBER,
269 p_list_line_id NUMBER,
270 p_qualifier_precedence NUMBER,
271 p_qualifier_datatype VARCHAR2,
272 p_start_date_active DATE,
273 p_end_date_active DATE,
274 p_context VARCHAR2,
275 p_attribute1 VARCHAR2,
276 p_attribute2 VARCHAR2,
277 p_attribute3 VARCHAR2,
278 p_attribute4 VARCHAR2,
279 p_attribute5 VARCHAR2,
280 p_attribute6 VARCHAR2,
281 p_attribute7 VARCHAR2,
282 p_attribute8 VARCHAR2,
283 p_attribute9 VARCHAR2,
284 p_attribute10 VARCHAR2,
285 p_attribute11 VARCHAR2,
286 p_attribute12 VARCHAR2,
287 p_attribute13 VARCHAR2,
288 p_attribute14 VARCHAR2,
289 p_attribute15 VARCHAR2,
290 p_id1 VARCHAR2,
291 p_type VARCHAR2,
292 x_qualifier_grouping_no OUT NOCOPY /* file.sql.39 change */ NUMBER) AS
293 err_num NUMBER;
294 err_msg VARCHAR2(100);
295
296 BEGIN
297
298
299 -- Create new grouping no
300 IF (p_qualifier_grouping_no = 0) THEN
301 SELECT QP.QP_QUALIFIER_GROUP_NO_S.nextval
302 INTO x_qualifier_grouping_no
303 FROM DUAL;
304 ELSE
305 x_qualifier_grouping_no := p_qualifier_grouping_no;
306 END IF;
307
308 -- mkarya for bug 1955867, the order of p_context, p_start_date_active,p_end_date_active in the VALUES
309 -- clause have been arranged to populate these columns properly
310 -- Create a record in qp_qualifiers
311 INSERT INTO QP_QUALIFIERS (
312 QUALIFIER_ID,CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
313 LAST_UPDATE_LOGIN, LIST_HEADER_ID, LIST_LINE_ID,COMPARISON_OPERATOR_CODE, QUALIFIER_CONTEXT,
314 QUALIFIER_ATTRIBUTE, QUALIFIER_ATTR_VALUE, QUALIFIER_GROUPING_NO,EXCLUDER_FLAG,
315 QUALIFIER_PRECEDENCE,QUALIFIER_DATATYPE,START_DATE_ACTIVE,END_DATE_ACTIVE,CONTEXT,ATTRIBUTE1,ATTRIBUTE2,
316 ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,
317 ATTRIBUTE11,ATTRIBUTE12, ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15
318 --ENH Upgrade BOAPI for orig_sys...ref RAVI
319 ,ORIG_SYS_QUALIFIER_REF
320 ,ORIG_SYS_LINE_REF
321 ,ORIG_SYS_HEADER_REF)
322 VALUES (
323 QP_QUALIFIERS_S.nextval,p_creation_date,p_created_by,p_last_update_date,p_last_updated_by,
324 p_last_update_login,p_list_header_id,p_list_line_id,p_comparision_operator_code,
325 p_qualifier_context, p_qualifier_attribute,p_qualifier_attr_value,x_qualifier_grouping_no,
326 p_excluder_flag,p_qualifier_precedence,p_qualifier_datatype, p_start_date_active,p_end_date_active, p_context,
327 p_attribute1,p_attribute2,p_attribute3,p_attribute4, p_attribute5,p_attribute6,p_attribute7,
328 p_attribute8,p_attribute9,p_attribute10, p_attribute11,p_attribute12,p_attribute13,
329 p_attribute14,p_attribute15
330 --ENH Upgrade BOAPI for orig_sys...ref RAVI
331 ,to_char(QP_QUALIFIERS_S.currval)
332 ,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=p_list_line_id)
333 ,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=p_list_header_id)
334 );
335 EXCEPTION
336 WHEN OTHERS THEN
337 err_msg := SQLERRM;
338 rollback;
339 QP_Util.Log_Error(p_id1 => p_id1,
340 p_error_type => p_type,
341 p_error_desc => err_msg,
342 p_error_module => 'Create_Qualifier');
343 raise;
344
345 END Create_Qualifier;
346
347 PROCEDURE Create_Pricing_Attribute(
348
349 p_creation_date DATE,
350 p_created_by NUMBER,
351 p_last_update_date DATE,
352 p_last_updated_by NUMBER,
353 p_last_update_login NUMBER,
354 p_program_application_id NUMBER,
355 p_program_id NUMBER,
356 p_program_update_date DATE,
357 p_request_id NUMBER,
358 p_list_line_id NUMBER,
359 p_excluder_flag VARCHAR2,
360 p_accumulate_flag VARCHAR2,
361 p_product_attribute_context VARCHAR2,
362 p_product_attribute VARCHAR2,
363 p_product_attr_value VARCHAR2,
364 p_product_uom_code VARCHAR2,
365 p_pricing_attribute_context VARCHAR2,
366 p_pricing_attribute VARCHAR2,
367 p_pricing_attr_value_from VARCHAR2,
368 p_pricing_attr_value_to VARCHAR2,
369 p_comparision_operator_code VARCHAR2,
370 p_pricing_attr_datatype VARCHAR2,
371 p_product_attr_datatype VARCHAR2,
372 p_id1 VARCHAR2,
373 p_id2 VARCHAR2 := null,
374 p_type VARCHAR2,
375 x_pricing_attribute_id OUT NOCOPY /* file.sql.39 change */ NUMBER) AS
376 err_num NUMBER;
377 err_msg VARCHAR2(100);
378
379 BEGIN
380
381 INSERT INTO QP_PRICING_ATTRIBUTES
382 (PRICING_ATTRIBUTE_ID, CREATION_DATE, CREATED_BY,
383 LAST_UPDATE_DATE, LAST_UPDATED_BY,LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID,
384 PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID, LIST_LINE_ID, EXCLUDER_FLAG,
385 ACCUMULATE_FLAG, PRODUCT_ATTRIBUTE_CONTEXT,PRODUCT_ATTRIBUTE,
386 PRODUCT_ATTR_VALUE, PRODUCT_UOM_CODE, PRICING_ATTRIBUTE_CONTEXT,
387 PRICING_ATTRIBUTE, PRICING_ATTR_VALUE_FROM, PRICING_ATTR_VALUE_TO,
388 ATTRIBUTE_GROUPING_NO,COMPARISON_OPERATOR_CODE,PRICING_ATTRIBUTE_DATATYPE,
389 PRODUCT_ATTRIBUTE_DATATYPE
390 --ENH Upgrade BOAPI for orig_sys...ref RAVI
391 ,ORIG_SYS_PRICING_ATTR_REF
395 QP_PRICING_ATTRIBUTES_S.nextval,p_creation_date, p_created_by,
392 ,ORIG_SYS_LINE_REF
393 ,ORIG_SYS_HEADER_REF)
394 VALUES (
396 p_last_update_date, p_last_updated_by, p_last_update_login, p_program_application_id,
397 p_program_id, p_program_update_date, p_request_id, p_list_line_id, p_excluder_flag,
398 p_accumulate_flag, p_product_attribute_context, p_product_attribute,
399 p_product_attr_value, p_product_uom_code, p_pricing_attribute_context,
400 p_pricing_attribute, p_pricing_attr_value_from, p_pricing_attr_value_to,
401 1 , p_comparision_operator_code,
402 p_pricing_attr_datatype, p_product_attr_datatype
403 --ENH Upgrade BOAPI for orig_sys...ref RAVI
404 ,to_char(QP_PRICING_ATTRIBUTES_S.currval)
405 ,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=p_list_line_id)
406 ,(select l.ORIG_SYS_HEADER_REF from qp_list_lines l where l.list_line_id=p_list_line_id)
407 );
408
409
410 /* SELECT QP_PRICING_ATTRIBUTES_S.currval
411 INTO x_pricing_attribute_id
412 FROM DUAL; */
413 x_pricing_attribute_id :=1;
414
415
416 EXCEPTION
417 WHEN OTHERS THEN
418 err_msg := SQLERRM;
419 rollback;
420 QP_Util.Log_Error(p_id1 => p_id1,
421 p_id2 => p_id2,
422 p_error_type => p_type,
423 p_error_desc => err_msg,
424 p_error_module => 'Create_Pricing_Attribute');
425 raise;
426
427 END Create_Pricing_Attribute;
428
429 PROCEDURE Create_Pricing_Attribute_Break(
430
431 p_creation_date DATE,
432 p_created_by NUMBER,
433 p_last_update_date DATE,
434 p_last_updated_by NUMBER,
435 p_last_update_login NUMBER,
436 p_program_application_id NUMBER,
437 p_program_id NUMBER,
438 p_program_update_date DATE,
439 p_request_id NUMBER,
440 p_list_line_id NUMBER,
441 p_excluder_flag VARCHAR2,
442 p_accumulate_flag VARCHAR2,
443 p_product_attribute_context VARCHAR2,
444 p_product_attribute VARCHAR2,
445 p_product_attr_value VARCHAR2,
446 p_product_uom_code VARCHAR2,
447 p_pricing_attribute_context VARCHAR2,
448 p_pricing_attribute VARCHAR2,
449 p_pricing_attr_value_from NUMBER,
450 p_pricing_attr_value_to NUMBER,
451 p_comparision_operator_code VARCHAR2,
452 p_pricing_attr_datatype VARCHAR2,
453 p_product_attr_datatype VARCHAR2,
454 p_id1 VARCHAR2,
455 p_id2 VARCHAR2 := null,
456 p_type VARCHAR2,
457 x_pricing_attribute_id OUT NOCOPY /* file.sql.39 change */ NUMBER) AS
458 err_num NUMBER;
459 err_msg VARCHAR2(100);
460
461 BEGIN
462
463 INSERT INTO QP_PRICING_ATTRIBUTES
464 (PRICING_ATTRIBUTE_ID, CREATION_DATE, CREATED_BY,
465 LAST_UPDATE_DATE, LAST_UPDATED_BY,LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID,
466 PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID, LIST_LINE_ID, EXCLUDER_FLAG,
467 ACCUMULATE_FLAG, PRODUCT_ATTRIBUTE_CONTEXT,PRODUCT_ATTRIBUTE,
468 PRODUCT_ATTR_VALUE, PRODUCT_UOM_CODE, PRICING_ATTRIBUTE_CONTEXT,
469 PRICING_ATTRIBUTE, PRICING_ATTR_VALUE_FROM, PRICING_ATTR_VALUE_TO,
470 ATTRIBUTE_GROUPING_NO,COMPARISON_OPERATOR_CODE,PRICING_ATTRIBUTE_DATATYPE,
471 PRODUCT_ATTRIBUTE_DATATYPE
472 --ENH Upgrade BOAPI for orig_sys...ref RAVI
473 ,ORIG_SYS_PRICING_ATTR_REF
474 ,ORIG_SYS_LINE_REF
475 ,ORIG_SYS_HEADER_REF)
476 VALUES (
477 QP_PRICING_ATTRIBUTES_S.nextval,p_creation_date, p_created_by,
478 p_last_update_date, p_last_updated_by, p_last_update_login, p_program_application_id,
479 p_program_id, p_program_update_date, p_request_id, p_list_line_id, p_excluder_flag,
480 p_accumulate_flag, p_product_attribute_context, p_product_attribute,
481 p_product_attr_value, p_product_uom_code, p_pricing_attribute_context,
482 p_pricing_attribute, qp_number.number_to_canonical(p_pricing_attr_value_from),
483 qp_number.number_to_canonical(p_pricing_attr_value_to), 1 ,
484 p_comparision_operator_code,p_pricing_attr_datatype, p_product_attr_datatype
485 --ENH Upgrade BOAPI for orig_sys...ref RAVI
486 ,to_char(QP_PRICING_ATTRIBUTES_S.currval)
487 ,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=p_list_line_id)
488 ,(select l.ORIG_SYS_HEADER_REF from qp_list_lines l where l.list_line_id=p_list_line_id)
489 );
490
491 SELECT QP_PRICING_ATTRIBUTES_S.currval
492 INTO x_pricing_attribute_id
493 FROM DUAL;
494
495
496 EXCEPTION
497 WHEN OTHERS THEN
498 err_msg := SQLERRM;
499 rollback;
500 QP_Util.Log_Error(p_id1 => p_id1,
501 p_id2 => p_id2,
502 p_error_type => p_type,
503 p_error_desc => err_msg,
504 p_error_module => 'Create_Pricing_Attribute_Break');
505 raise;
506
507 END Create_Pricing_Attribute_Break;
508
509 PROCEDURE Create_Related_Modifier (
510
511 p_creation_date DATE,
512 p_created_by NUMBER,
513 p_last_update_date DATE,
514 p_last_updated_by NUMBER,
515 p_last_update_login NUMBER,
516 p_from_rltd_modifier_id NUMBER,
517 p_to_rltd_modifier_id NUMBER,
518 p_rltd_modifier_grp_type VARCHAR2,
519 p_id1 VARCHAR2,
520 p_id2 VARCHAR2 := null,
521 p_type VARCHAR2,
522 x_rltd_modifier_id OUT NOCOPY /* file.sql.39 change */ NUMBER) AS
523
524 err_num NUMBER;
525 err_msg VARCHAR2(100);
526
527 BEGIN
528
529 INSERT INTO QP_RLTD_MODIFIERS
530 ( RLTD_MODIFIER_ID,CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
531 LAST_UPDATED_BY, LAST_UPDATE_LOGIN,RLTD_MODIFIER_GRP_NO,
532 FROM_RLTD_MODIFIER_ID, TO_RLTD_MODIFIER_ID,RLTD_MODIFIER_GRP_TYPE)
533 VALUES(
534 QP_RLTD_MODIFIERS_S.nextval,p_creation_date,p_created_by,p_last_update_date,
535 p_last_updated_by, p_last_update_login,QP_RLTD_MODIFIER_GRP_NO_S.nextval,
536 p_from_rltd_modifier_id,p_to_rltd_modifier_id,p_rltd_modifier_grp_type);
537
538 SELECT QP_RLTD_MODIFIERS_S.currval
539 INTO x_rltd_modifier_id
540 FROM DUAL;
541
542
543 EXCEPTION
544 WHEN OTHERS THEN
545 err_msg := SQLERRM;
546 rollback;
547 QP_Util.Log_Error(p_id1 => p_id1,
548 p_id2 => p_id2,
549 p_error_type => p_type,
550 p_error_desc => err_msg,
551 p_error_module => 'Create_Related_Modifier');
552 raise;
553
554 END Create_Related_Modifier;
555
556 procedure insert_line_distribution
557 (
558 l_worker in number,
559 l_start_line IN Number,
560 l_end_line IN Number,
561 l_type_var IN Varchar2
562 )
563 is
564 Begin
565
566 insert into qp_upg_lines_distribution
567 (
568 worker,
569 start_line_id,
570 end_line_id,
571 alloted_flag,
572 line_type,
573 creation_date
574 )
575 values
576 (
577 l_worker,
578 l_start_line,
579 l_end_line,
580 'N',
581 l_type_var,
582 sysdate
583 );
584
585 end insert_line_distribution;
586
587 END QP_Modifier_Upgrade_Util_PVT;