[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.3 2011/03/03 08:19:03 dnema ship $ */
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
55 BEGIN
52 FROM qp_list_headers_tl
53 WHERE name = p_name;
54
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 (
205 AUTOMATIC_FLAG, MODIFIER_LEVEL_CODE, ARITHMETIC_OPERATOR, OPERAND,PRICING_PHASE_ID,
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,
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 --bug 11826293
302
303 /*SELECT QP.QP_QUALIFIER_GROUP_NO_S.nextval
304 INTO x_qualifier_grouping_no
305 FROM DUAL;*/
306
307 SELECT QP_QUALIFIER_GROUP_NO_S.nextval
308 INTO x_qualifier_grouping_no
309 FROM DUAL;
310 ELSE
311 x_qualifier_grouping_no := p_qualifier_grouping_no;
312 END IF;
313
314 -- mkarya for bug 1955867, the order of p_context, p_start_date_active,p_end_date_active in the VALUES
315 -- clause have been arranged to populate these columns properly
316 -- Create a record in qp_qualifiers
317 INSERT INTO QP_QUALIFIERS (
318 QUALIFIER_ID,CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
319 LAST_UPDATE_LOGIN, LIST_HEADER_ID, LIST_LINE_ID,COMPARISON_OPERATOR_CODE, QUALIFIER_CONTEXT,
320 QUALIFIER_ATTRIBUTE, QUALIFIER_ATTR_VALUE, QUALIFIER_GROUPING_NO,EXCLUDER_FLAG,
321 QUALIFIER_PRECEDENCE,QUALIFIER_DATATYPE,START_DATE_ACTIVE,END_DATE_ACTIVE,CONTEXT,ATTRIBUTE1,ATTRIBUTE2,
322 ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,
323 ATTRIBUTE11,ATTRIBUTE12, ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15
324 --ENH Upgrade BOAPI for orig_sys...ref RAVI
328 VALUES (
325 ,ORIG_SYS_QUALIFIER_REF
326 ,ORIG_SYS_LINE_REF
327 ,ORIG_SYS_HEADER_REF)
329 QP_QUALIFIERS_S.nextval,p_creation_date,p_created_by,p_last_update_date,p_last_updated_by,
330 p_last_update_login,p_list_header_id,p_list_line_id,p_comparision_operator_code,
331 p_qualifier_context, p_qualifier_attribute,p_qualifier_attr_value,x_qualifier_grouping_no,
332 p_excluder_flag,p_qualifier_precedence,p_qualifier_datatype, p_start_date_active,p_end_date_active, p_context,
333 p_attribute1,p_attribute2,p_attribute3,p_attribute4, p_attribute5,p_attribute6,p_attribute7,
334 p_attribute8,p_attribute9,p_attribute10, p_attribute11,p_attribute12,p_attribute13,
335 p_attribute14,p_attribute15
336 --ENH Upgrade BOAPI for orig_sys...ref RAVI
337 ,to_char(QP_QUALIFIERS_S.currval)
338 ,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=p_list_line_id)
339 ,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=p_list_header_id)
340 );
341 EXCEPTION
342 WHEN OTHERS THEN
343 err_msg := SQLERRM;
344 rollback;
345 QP_Util.Log_Error(p_id1 => p_id1,
346 p_error_type => p_type,
347 p_error_desc => err_msg,
348 p_error_module => 'Create_Qualifier');
349 raise;
350
351 END Create_Qualifier;
352
353 PROCEDURE Create_Pricing_Attribute(
354
355 p_creation_date DATE,
356 p_created_by NUMBER,
357 p_last_update_date DATE,
358 p_last_updated_by NUMBER,
359 p_last_update_login NUMBER,
360 p_program_application_id NUMBER,
361 p_program_id NUMBER,
362 p_program_update_date DATE,
363 p_request_id NUMBER,
364 p_list_line_id NUMBER,
365 p_excluder_flag VARCHAR2,
366 p_accumulate_flag VARCHAR2,
367 p_product_attribute_context VARCHAR2,
368 p_product_attribute VARCHAR2,
369 p_product_attr_value VARCHAR2,
370 p_product_uom_code VARCHAR2,
371 p_pricing_attribute_context VARCHAR2,
372 p_pricing_attribute VARCHAR2,
373 p_pricing_attr_value_from VARCHAR2,
374 p_pricing_attr_value_to VARCHAR2,
375 p_comparision_operator_code VARCHAR2,
376 p_pricing_attr_datatype VARCHAR2,
377 p_product_attr_datatype VARCHAR2,
378 p_id1 VARCHAR2,
379 p_id2 VARCHAR2 := null,
380 p_type VARCHAR2,
381 x_pricing_attribute_id OUT NOCOPY /* file.sql.39 change */ NUMBER) AS
382 err_num NUMBER;
383 err_msg VARCHAR2(100);
384
385 BEGIN
386
387 INSERT INTO QP_PRICING_ATTRIBUTES
388 (PRICING_ATTRIBUTE_ID, CREATION_DATE, CREATED_BY,
389 LAST_UPDATE_DATE, LAST_UPDATED_BY,LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID,
390 PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID, LIST_LINE_ID, EXCLUDER_FLAG,
391 ACCUMULATE_FLAG, PRODUCT_ATTRIBUTE_CONTEXT,PRODUCT_ATTRIBUTE,
392 PRODUCT_ATTR_VALUE, PRODUCT_UOM_CODE, PRICING_ATTRIBUTE_CONTEXT,
393 PRICING_ATTRIBUTE, PRICING_ATTR_VALUE_FROM, PRICING_ATTR_VALUE_TO,
394 ATTRIBUTE_GROUPING_NO,COMPARISON_OPERATOR_CODE,PRICING_ATTRIBUTE_DATATYPE,
395 PRODUCT_ATTRIBUTE_DATATYPE
396 --ENH Upgrade BOAPI for orig_sys...ref RAVI
397 ,ORIG_SYS_PRICING_ATTR_REF
398 ,ORIG_SYS_LINE_REF
399 ,ORIG_SYS_HEADER_REF)
400 VALUES (
401 QP_PRICING_ATTRIBUTES_S.nextval,p_creation_date, p_created_by,
402 p_last_update_date, p_last_updated_by, p_last_update_login, p_program_application_id,
403 p_program_id, p_program_update_date, p_request_id, p_list_line_id, p_excluder_flag,
404 p_accumulate_flag, p_product_attribute_context, p_product_attribute,
405 p_product_attr_value, p_product_uom_code, p_pricing_attribute_context,
406 p_pricing_attribute, p_pricing_attr_value_from, p_pricing_attr_value_to,
407 1 , p_comparision_operator_code,
408 p_pricing_attr_datatype, p_product_attr_datatype
409 --ENH Upgrade BOAPI for orig_sys...ref RAVI
410 ,to_char(QP_PRICING_ATTRIBUTES_S.currval)
411 ,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=p_list_line_id)
412 ,(select l.ORIG_SYS_HEADER_REF from qp_list_lines l where l.list_line_id=p_list_line_id)
413 );
414
415
416 /* SELECT QP_PRICING_ATTRIBUTES_S.currval
417 INTO x_pricing_attribute_id
418 FROM DUAL; */
419 x_pricing_attribute_id :=1;
420
421
422 EXCEPTION
423 WHEN OTHERS THEN
424 err_msg := SQLERRM;
425 rollback;
426 QP_Util.Log_Error(p_id1 => p_id1,
427 p_id2 => p_id2,
428 p_error_type => p_type,
429 p_error_desc => err_msg,
430 p_error_module => 'Create_Pricing_Attribute');
431 raise;
432
433 END Create_Pricing_Attribute;
434
435 PROCEDURE Create_Pricing_Attribute_Break(
436
437 p_creation_date DATE,
438 p_created_by NUMBER,
439 p_last_update_date DATE,
440 p_last_updated_by NUMBER,
441 p_last_update_login NUMBER,
442 p_program_application_id NUMBER,
443 p_program_id NUMBER,
444 p_program_update_date DATE,
445 p_request_id NUMBER,
446 p_list_line_id NUMBER,
447 p_excluder_flag VARCHAR2,
448 p_accumulate_flag VARCHAR2,
449 p_product_attribute_context VARCHAR2,
450 p_product_attribute VARCHAR2,
451 p_product_attr_value VARCHAR2,
452 p_product_uom_code VARCHAR2,
453 p_pricing_attribute_context VARCHAR2,
454 p_pricing_attribute VARCHAR2,
455 p_pricing_attr_value_from NUMBER,
456 p_pricing_attr_value_to NUMBER,
457 p_comparision_operator_code VARCHAR2,
458 p_pricing_attr_datatype VARCHAR2,
459 p_product_attr_datatype VARCHAR2,
460 p_id1 VARCHAR2,
461 p_id2 VARCHAR2 := null,
462 p_type VARCHAR2,
463 x_pricing_attribute_id OUT NOCOPY /* file.sql.39 change */ NUMBER) AS
464 err_num NUMBER;
465 err_msg VARCHAR2(100);
466
467 BEGIN
468
469 INSERT INTO QP_PRICING_ATTRIBUTES
470 (PRICING_ATTRIBUTE_ID, CREATION_DATE, CREATED_BY,
471 LAST_UPDATE_DATE, LAST_UPDATED_BY,LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID,
472 PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID, LIST_LINE_ID, EXCLUDER_FLAG,
473 ACCUMULATE_FLAG, PRODUCT_ATTRIBUTE_CONTEXT,PRODUCT_ATTRIBUTE,
474 PRODUCT_ATTR_VALUE, PRODUCT_UOM_CODE, PRICING_ATTRIBUTE_CONTEXT,
475 PRICING_ATTRIBUTE, PRICING_ATTR_VALUE_FROM, PRICING_ATTR_VALUE_TO,
476 ATTRIBUTE_GROUPING_NO,COMPARISON_OPERATOR_CODE,PRICING_ATTRIBUTE_DATATYPE,
477 PRODUCT_ATTRIBUTE_DATATYPE
478 --ENH Upgrade BOAPI for orig_sys...ref RAVI
479 ,ORIG_SYS_PRICING_ATTR_REF
480 ,ORIG_SYS_LINE_REF
481 ,ORIG_SYS_HEADER_REF)
482 VALUES (
483 QP_PRICING_ATTRIBUTES_S.nextval,p_creation_date, p_created_by,
484 p_last_update_date, p_last_updated_by, p_last_update_login, p_program_application_id,
485 p_program_id, p_program_update_date, p_request_id, p_list_line_id, p_excluder_flag,
486 p_accumulate_flag, p_product_attribute_context, p_product_attribute,
487 p_product_attr_value, p_product_uom_code, p_pricing_attribute_context,
488 p_pricing_attribute, qp_number.number_to_canonical(p_pricing_attr_value_from),
489 qp_number.number_to_canonical(p_pricing_attr_value_to), 1 ,
490 p_comparision_operator_code,p_pricing_attr_datatype, p_product_attr_datatype
491 --ENH Upgrade BOAPI for orig_sys...ref RAVI
492 ,to_char(QP_PRICING_ATTRIBUTES_S.currval)
493 ,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=p_list_line_id)
494 ,(select l.ORIG_SYS_HEADER_REF from qp_list_lines l where l.list_line_id=p_list_line_id)
495 );
496
497 SELECT QP_PRICING_ATTRIBUTES_S.currval
498 INTO x_pricing_attribute_id
499 FROM DUAL;
500
501
502 EXCEPTION
503 WHEN OTHERS THEN
504 err_msg := SQLERRM;
505 rollback;
506 QP_Util.Log_Error(p_id1 => p_id1,
507 p_id2 => p_id2,
508 p_error_type => p_type,
509 p_error_desc => err_msg,
510 p_error_module => 'Create_Pricing_Attribute_Break');
511 raise;
512
513 END Create_Pricing_Attribute_Break;
514
515 PROCEDURE Create_Related_Modifier (
516
517 p_creation_date DATE,
518 p_created_by NUMBER,
519 p_last_update_date DATE,
520 p_last_updated_by NUMBER,
521 p_last_update_login NUMBER,
522 p_from_rltd_modifier_id NUMBER,
523 p_to_rltd_modifier_id NUMBER,
524 p_rltd_modifier_grp_type VARCHAR2,
525 p_id1 VARCHAR2,
526 p_id2 VARCHAR2 := null,
527 p_type VARCHAR2,
528 x_rltd_modifier_id OUT NOCOPY /* file.sql.39 change */ NUMBER) AS
529
530 err_num NUMBER;
531 err_msg VARCHAR2(100);
532
533 BEGIN
534
535 INSERT INTO QP_RLTD_MODIFIERS
536 ( RLTD_MODIFIER_ID,CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
537 LAST_UPDATED_BY, LAST_UPDATE_LOGIN,RLTD_MODIFIER_GRP_NO,
538 FROM_RLTD_MODIFIER_ID, TO_RLTD_MODIFIER_ID,RLTD_MODIFIER_GRP_TYPE)
539 VALUES(
540 QP_RLTD_MODIFIERS_S.nextval,p_creation_date,p_created_by,p_last_update_date,
541 p_last_updated_by, p_last_update_login,QP_RLTD_MODIFIER_GRP_NO_S.nextval,
542 p_from_rltd_modifier_id,p_to_rltd_modifier_id,p_rltd_modifier_grp_type);
543
544 SELECT QP_RLTD_MODIFIERS_S.currval
545 INTO x_rltd_modifier_id
546 FROM DUAL;
547
548
549 EXCEPTION
550 WHEN OTHERS THEN
551 err_msg := SQLERRM;
552 rollback;
553 QP_Util.Log_Error(p_id1 => p_id1,
554 p_id2 => p_id2,
555 p_error_type => p_type,
556 p_error_desc => err_msg,
557 p_error_module => 'Create_Related_Modifier');
558 raise;
559
560 END Create_Related_Modifier;
561
562 procedure insert_line_distribution
563 (
564 l_worker in number,
565 l_start_line IN Number,
566 l_end_line IN Number,
567 l_type_var IN Varchar2
568 )
569 is
570 Begin
571
572 insert into qp_upg_lines_distribution
573 (
574 worker,
575 start_line_id,
576 end_line_id,
577 alloted_flag,
578 line_type,
579 creation_date
580 )
581 values
582 (
583 l_worker,
584 l_start_line,
585 l_end_line,
586 'N',
587 l_type_var,
588 sysdate
589 );
590
591 end insert_line_distribution;
592
593 END QP_Modifier_Upgrade_Util_PVT;