[Home] [Help]
PACKAGE BODY: APPS.QP_ATM_UPGRADE
Source
1 PACKAGE BODY QP_ATM_UPGRADE AS
2 /* $Header: QPATMUPB.pls 120.2 2006/05/23 20:03:32 gtippire noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'QP_ATM_UPGRADE';
5 --
6 -- moved to spec
7 /*CURSOR attribute_sourcing_cur (p_prc_context_code in varchar2,
8 p_segment_mapping_column in varchar2,
9 p_application_short_name in varchar2) is
10 SELECT d.*
11 FROM oe_def_attr_condns b,
12 ak_object_attributes a,
13 oe_def_condn_elems c,
14 oe_def_condn_elems c1,
15 oe_def_attr_def_rules d
16 WHERE substr(c.value_string,1,30) = p_prc_context_code and
17 b.attribute_code = p_segment_mapping_column and
18 substr(c1.value_string,1,30) = p_application_short_name and
19 b.attr_def_condition_id = d.attr_def_condition_id and
20 b.database_object_name = a.database_object_name and
21 b.database_object_name = d.database_object_name and
22 b.attribute_code = a.attribute_code and
23 b.attribute_code = d.attribute_code and
24 c.condition_id = b.condition_id and
25 c1.condition_id = b.condition_id and
26 c.attribute_code like '%CONTEXT%' and
27 c1.attribute_code = 'SRC_SYSTEM_CODE' and
28 a.attribute_application_id = 661 and
29 nvl(b.enabled_flag,'Y') = 'Y';
30 */
31 --
32 PROCEDURE p_upd_bad_upg_seed_data IS
33 --Private function to update bad seed data created from 10.7 to 11i upgrade.
34 BEGIN
35
36 UPDATE oe_def_attr_condns
37 SET enabled_flag = 'N'
38 WHERE
39 Condition_id IN (
40 SELECT C.condition_id
41 FROM
42 oe_def_attr_condns C,
43 oe_def_conditions_vl CT
44 WHERE
45 C.condition_id = CT.condition_id AND
46 C. database_object_name IN (
47 'QP_HDR_QUALIF_ATTRIBS_V',
48 'QP_LINE_PRICING_ATTRIBS_V',
49 'QP_LINE_QUALIF_ATTRIBS_V' ) AND
50 CT.display_name like 'ONT%' AND
51 C.condition_id > 1000 AND
52 C.system_flag= 'Y' AND
53 1< (SELECT count(*) FROM oe_def_conditions_vl CTS,
54 oe_def_attr_condns CS
55 WHERE
56 CTS.display_name = CT.display_name
57 AND CTS.condition_id = CS.condition_id
58 AND CS.attribute_code = C.attribute_code )
59 );
60
61 EXCEPTION
62
63 WHEN OTHERS THEN
64 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
65 THEN
66 OE_MSG_PUB.Add_Exc_Msg
67 ( G_PKG_NAME,
68 'p_upd_bad_upg_seed_data');
69 END IF;
70 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
71
72 END p_upd_bad_upg_seed_data;
73
74
75 --
76 FUNCTION p_get_pte_for_rqt(p_request_type_code in varchar2) return varchar2 is
77 --Private function to get pte_code for a Request type.
78 l_pte_code varchar2(30);
79 begin
80 select pte_code
81 into l_pte_code
82 from qp_pte_request_types_b
83 where request_type_code = p_request_type_code and
84 rownum = 1;
85 return (l_pte_code);
86 exception
87 when no_data_found then
88 return('-1');
89 when others then
90 return('-2');
91 end;
92 --
93
94 FUNCTION p_ssc_exists(p_application_short_name in varchar2,
95 p_pte_code in varchar2) return number is
96 -- Private function to check if Source System Exists in QP_PTE_SOURCE_SYSTEMS table
97 -- for a given PTE.
98 dummy varchar2(1);
99 begin
100 select 'x'
101 into dummy
102 from qp_pte_source_systems
103 where application_short_name = p_application_short_name and
104 pte_code = p_pte_code and
105 rownum = 1;
106 return(0);
107 exception
108 when no_data_found then
109 return(-1);
110 when others then
111 return(-2);
112 end;
113 --
114 FUNCTION p_req_exists(p_request_type_code in varchar2) return number is
115 --Private function to check if given Request Type Exists in QP_PTE_REQUEST_TYPES_B table.
116 dummy varchar2(1);
117 begin
118 select 'x'
119 into dummy
120 from qp_pte_request_types_b
121 where request_type_code = p_request_type_code and
122 rownum = 1;
123 return(0);
124 exception
125 when no_data_found then
126 return(-1);
127 when others then
128 return(-2);
129 end;
130 --
131 FUNCTION p_psg_exists(p_segment_id in number,p_pte_code in varchar2)
132 return number is
133 --Private function to check if given Segment and PTE exist in QP_PTE_SEGMENTS table.
134 dummy varchar2(1);
135 begin
136 select 'x'
137 into dummy
138 from qp_pte_segments
139 where pte_code = p_pte_code and
140 segment_id = p_segment_id and
141 rownum = 1;
142 return(0);
143 exception
144 when no_data_found then
145 return(-1);
146 when others then
147 return(-2);
148 end;
149 --
150 FUNCTION p_sourcing_exists(p_segment_id in number,
151 p_request_type_code in varchar2,
152 p_attribute_sourcing_level in varchar2)
153 return number is
154 --Private function to check if given Segment,Request Type exist in QP_ATTRIBUTE_SOURCING table.
155 dummy varchar2(1);
156 begin
157 select 'x'
158 into dummy
159 from qp_attribute_sourcing
160 where segment_id = p_segment_id and
161 request_type_code = p_request_type_code and
162 attribute_sourcing_level = p_attribute_sourcing_level and
163 rownum = 1;
164 return(0);
165 exception
166 when no_data_found then
167 return(-1);
168 when others then
169 return(-2);
170 end;
171 --
172 FUNCTION p_con_exists (p_prc_context_code in varchar2,
173 p_flexfield_name in varchar2,
174 x_prc_context_id out NOCOPY /* file.sql.39 change */ number)
175 --Private function to check if Context code exists in QP_PRC_CONTEXTS_B table.
176 return number is
177 l_prc_context_id number(15);
178 begin
179 select prc_context_id
180 into l_prc_context_id
181 from qp_prc_contexts_b
182 where prc_context_code = p_prc_context_code and
183 ((p_flexfield_name = 'QP_ATTR_DEFNS_QUALIFIER' and prc_context_type = 'QUALIFIER') or
184 (p_flexfield_name <> 'QP_ATTR_DEFNS_QUALIFIER' and prc_context_type <> 'QUALIFIER')) and
185 rownum = 1;
186 x_prc_context_id := l_prc_context_id;
187 return(0);
188 exception
189 when no_data_found then
190 return(-1);
191 when others then
192 return(-2);
193 end;
194 --
195 FUNCTION p_seg_exists (p_prc_context_code in varchar2,
196 p_flexfield_name in varchar2,
197 p_segment_code in varchar2,
198 p_segment_mapping_column in varchar2)
199 --Private function to check if Segment code exists in QP_SEGMENTS_B table.
200 return number is
201 dummy varchar2(1);
202 begin
203 select 'x'
204 into dummy
205 from qp_segments_b a,qp_prc_contexts_b b
206 where a.prc_context_id = b.prc_context_id and
207 b.prc_context_code = p_prc_context_code and
208 (a.segment_code = p_segment_code or a.segment_mapping_column = p_segment_mapping_column) and
209 ((p_flexfield_name = 'QP_ATTR_DEFNS_QUALIFIER' and b.prc_context_type = 'QUALIFIER') or
210 (p_flexfield_name <> 'QP_ATTR_DEFNS_QUALIFIER' and b.prc_context_type <> 'QUALIFIER')) and
211 rownum = 1;
212 return(0);
213 exception
214 when no_data_found then
215 return(-1);
216 when others then
217 return(-2);
218 end;
219 --
220 FUNCTION p_seg_src_def_exists (p_prc_context_code in varchar2,
221 p_segment_mapping_column in varchar2)
222 --Private function to check if an attribute was meant to be sourced in old system.
223 return number is
224 dummy varchar2(1);
225 begin
226 SELECT 'x'
227 INTO dummy
228 FROM oe_def_attr_condns b,
229 ak_object_attributes oa,
230 oe_def_condn_elems c1,
231 oe_def_condn_elems c2
232 WHERE c1.value_string = p_prc_context_code and
233 b.attribute_code = p_segment_mapping_column and
234 b.database_object_name = oa.database_object_name and
235 b.attribute_code = oa.attribute_code and
236 c1.condition_id = b.condition_id and
237 c1.attribute_code like '%CONTEXT%' and
238 c2.condition_id = b.condition_id and
239 c2.attribute_code = 'SRC_SYSTEM_CODE' and
240 --c2.value_string = 'QP' and
241 oa.attribute_application_id = 661 and
242 /* commented out as enabled_flag is also upgraded now */
243 --nvl(b.enabled_flag,'Y') = 'Y' and,
244 rownum = 1;
245 return(0);
246 exception
247 when no_data_found then
248 return(-1);
249 when others then
250 return(-2);
251 end;
252 --
253 FUNCTION p_get_level (p_prc_context_code in varchar2,
254 p_segment_mapping_column in varchar2)
255 -- Private function to get segment level.
256 return varchar2 is
257 l_segment_level varchar2(10);
258 l_count number;
259 dummy varchar2(1);
260 begin
261 l_count := 0;
262 begin
263 SELECT 'x'
264 INTO dummy
265 FROM oe_def_attr_condns b,
266 ak_object_attributes oa,
267 oe_def_condn_elems c1
268 WHERE c1.value_string = p_prc_context_code and
269 b.attribute_code = p_segment_mapping_column and
270 b.database_object_name = oa.database_object_name and
271 b.attribute_code = oa.attribute_code and
272 c1.condition_id = b.condition_id and
273 c1.attribute_code like '%CONTEXT%' and
274 oa.attribute_application_id = 661 and
275 (b.database_object_name like '%HEADER%' or
276 b.database_object_name like '%HDR%') and
277 /* commented out as enabled_flag is also upgraded now */
278 -- nvl(b.enabled_flag,'Y') = 'Y' and
279 rownum = 1;
280 l_count := l_count + 1;
281 l_segment_level := 'ORDER';
282 exception
283 when no_data_found then
284 null;
285 end;
286 --
287 begin
288 SELECT 'x'
289 INTO dummy
290 FROM oe_def_attr_condns b,
291 ak_object_attributes oa,
292 oe_def_condn_elems c1
293 WHERE c1.value_string = p_prc_context_code and
294 b.attribute_code = p_segment_mapping_column and
295 b.database_object_name = oa.database_object_name and
296 b.attribute_code = oa.attribute_code and
297 c1.condition_id = b.condition_id and
298 c1.attribute_code like '%CONTEXT%' and
299 oa.attribute_application_id = 661 and
300 b.database_object_name like '%LINE%' and
301 /* commented out as enabled_flag is also upgraded now */
302 -- nvl(b.enabled_flag,'Y') = 'Y' and
303 rownum = 1;
304 l_segment_level := 'LINE';
305 l_count := l_count + 1;
306 --
307 if l_count = 2 then
308 l_segment_level := 'BOTH';
309 end if;
310 --
311 exception
312 when no_data_found then
313 l_segment_level := 'UNEXPECTED';
314 end;
315 return(l_segment_level);
316 exception
317 when no_data_found then
318 return(-1);
319 end;
320 --
321 FUNCTION p_pte_code_exists (p_segment_id in number)
322 --Private function to check, if pte_code 'ONT' exists, for a given segment_id.
323 return number is
324 dummy varchar2(1);
325 begin
326 select 'x'
327 into dummy
328 from qp_pte_segments
329 where segment_id = p_segment_id and
330 pte_code = 'ONT' and
331 rownum = 1;
332 return(0);
333 exception
334 when no_data_found then
335 return(-1);
336 end;
337 --
338 FUNCTION p_get_format_type (p_value_set_id in number)
339 --Private function to get Format type for a given value set.
340 return varchar2 is
341 l_format_type varchar2(1);
342 begin
343 select format_type
344 into l_format_type
345 from fnd_flex_value_sets
346 where flex_value_set_id = p_value_set_id and
347 rownum = 1;
348 return(l_format_type);
349 exception
350 when no_data_found then
351 return(null);
352 end;
353 --
354 PROCEDURE p_insert_lookup_code (p_lookup_type in varchar2,
355 p_lookup_code in varchar2) is
356 -- Private procedure to insert new PTEs and Request Types.
357 l_row_id varchar2(25);
358 l_meaning varchar2(80);
359 begin
360 if p_lookup_type = 'REQUEST_TYPE' then
361 l_meaning := p_lookup_code|| '(New)';
362 elsif p_lookup_type = 'QP_PTE_TYPE' then
363 l_meaning := 'Unassigned-'|| g_pte_num;
364 end if;
365 --
366 FND_LOOKUP_VALUES_PKG.INSERT_ROW(
367 X_ROWID => l_row_id,
368 X_LOOKUP_TYPE => p_lookup_type,
369 X_SECURITY_GROUP_ID => 0,
370 X_VIEW_APPLICATION_ID => 661,
371 X_LOOKUP_CODE => p_lookup_code,
372 X_TAG => null,
373 X_ATTRIBUTE_CATEGORY => null,
374 X_ATTRIBUTE1 => null,
375 X_ATTRIBUTE2 => null,
376 X_ATTRIBUTE3 => null,
377 X_ATTRIBUTE4 => null,
378 X_ENABLED_FLAG => 'Y',
379 X_START_DATE_ACTIVE => sysdate,
380 X_END_DATE_ACTIVE => null,
381 X_TERRITORY_CODE => null,
382 X_ATTRIBUTE5 => null,
383 X_ATTRIBUTE6 => null,
384 X_ATTRIBUTE7 => null,
385 X_ATTRIBUTE8 => null,
386 X_ATTRIBUTE9 => null,
387 X_ATTRIBUTE10 => null,
388 X_ATTRIBUTE11 => null,
389 X_ATTRIBUTE12 => null,
390 X_ATTRIBUTE13 => null,
391 X_ATTRIBUTE14 => null,
392 X_ATTRIBUTE15 => null,
393 X_MEANING => l_meaning,
394 X_DESCRIPTION => l_meaning,
395 X_CREATION_DATE => sysdate,
396 X_CREATED_BY => 1,
397 X_LAST_UPDATE_DATE => sysdate,
398 X_LAST_UPDATED_BY => 1,
399 X_LAST_UPDATE_LOGIN => null);
400 end;
401 --
402 PROCEDURE p_insert_context_b (p_flexfield_name in varchar2) is
403 -- Private procedure to insert Contexts in QP_PRC_CONTEXTS_B.
404 begin
405 g_context_seqno := g_context_seqno + 1;
406 INSERT into QP_PRC_CONTEXTS_B
407 (PRC_CONTEXT_ID,
408 PRC_CONTEXT_CODE,
409 PRC_CONTEXT_TYPE,
410 SEEDED_FLAG,
411 ENABLED_FLAG,
412 CONTEXT,
413 ATTRIBUTE1,
414 ATTRIBUTE2,
415 ATTRIBUTE3,
416 ATTRIBUTE4,
417 ATTRIBUTE5,
418 ATTRIBUTE6,
419 ATTRIBUTE7,
420 ATTRIBUTE8,
421 ATTRIBUTE9,
422 ATTRIBUTE10,
423 ATTRIBUTE11,
424 ATTRIBUTE12,
425 ATTRIBUTE13,
426 ATTRIBUTE14,
427 ATTRIBUTE15,
428 CREATED_BY,
429 CREATION_DATE,
430 LAST_UPDATED_BY,
431 LAST_UPDATE_DATE,
432 LAST_UPDATE_LOGIN,
433 PROGRAM_APPLICATION_ID,
434 PROGRAM_ID,
435 PROGRAM_UPDATE_DATE) values
436 (g_context_seqno,
437 g_context_b_rec.descriptive_flex_context_code,
438 decode(p_flexfield_name,'QP_ATTR_DEFNS_QUALIFIER','QUALIFIER',
439 decode(g_context_b_rec.descriptive_flex_context_code,'ITEM','PRODUCT','PRICING_ATTRIBUTE')),
440 decode(g_context_b_rec.created_by,1,'Y','N'),
441 g_context_b_rec.enabled_flag,
442 null,null,null,null,null,null,null,null,
443 null,null,null,null,null,null,null,null,
444 g_context_b_rec.created_by,
445 g_context_b_rec.creation_date,
446 g_context_b_rec.last_updated_by,
447 g_context_b_rec.last_update_date,
448 g_context_b_rec.last_update_login,
449 null,null,null);
450 end;
451 --
452 PROCEDURE p_insert_context_tl is
453 -- Private procedure to insert Contexts in QP_PRC_CONTEXTS_TL.
454 begin
455 INSERT into QP_PRC_CONTEXTS_TL
456 (PRC_CONTEXT_ID,
457 LANGUAGE,
458 SOURCE_LANG,
459 SEEDED_PRC_CONTEXT_NAME,
460 USER_PRC_CONTEXT_NAME,
461 SEEDED_DESCRIPTION,
462 USER_DESCRIPTION,
463 CREATED_BY,
464 CREATION_DATE,
465 LAST_UPDATED_BY,
466 LAST_UPDATE_DATE,
467 LAST_UPDATE_LOGIN ) values
468 (g_context_seqno,
469 g_context_tl_rec.language,
470 g_context_tl_rec.source_lang,
471 g_context_tl_rec.descriptive_flex_context_name,
472 g_context_tl_rec.descriptive_flex_context_name,
473 g_context_tl_rec.description,
474 nvl(g_context_tl_rec.description,'** No description found **'),
475 g_context_tl_rec.created_by,
476 g_context_tl_rec.creation_date,
477 g_context_tl_rec.last_updated_by,
478 g_context_tl_rec.last_update_date,
479 g_context_tl_rec.last_update_login);
480 end;
481 --
482 PROCEDURE p_insert_segment_b (p_prc_context_id in number,
483 p_valueset_id in number,
484 p_format_type in varchar2) is
485 -- Private procedure to insert Segments in QP_SEGMENTS_B.
486 begin
487 g_segment_seqno := g_segment_seqno + 1;
488 INSERT into QP_SEGMENTS_B
489 (SEGMENT_ID,
490 SEGMENT_CODE,
491 PRC_CONTEXT_ID,
492 AVAILABILITY_IN_BASIC,
493 APPLICATION_ID,
494 SEGMENT_MAPPING_COLUMN,
495 SEEDED_FLAG,
496 REQUIRED_FLAG,
497 SEEDED_PRECEDENCE,
498 USER_PRECEDENCE,
499 SEEDED_VALUESET_ID,
500 USER_VALUESET_ID,
501 SEEDED_FORMAT_TYPE,
502 USER_FORMAT_TYPE,
503 CONTEXT,
504 ATTRIBUTE1,
505 ATTRIBUTE2,
506 ATTRIBUTE3,
507 ATTRIBUTE4,
508 ATTRIBUTE5,
509 ATTRIBUTE6,
510 ATTRIBUTE7,
511 ATTRIBUTE8,
512 ATTRIBUTE9,
513 ATTRIBUTE10,
514 ATTRIBUTE11,
515 ATTRIBUTE12,
516 ATTRIBUTE13,
517 ATTRIBUTE14,
518 ATTRIBUTE15,
519 CREATED_BY,
520 CREATION_DATE,
521 LAST_UPDATED_BY,
522 LAST_UPDATE_DATE,
523 LAST_UPDATE_LOGIN,
524 PROGRAM_APPLICATION_ID,
525 PROGRAM_ID,
526 PROGRAM_UPDATE_DATE) values
527 (g_segment_seqno,
528 g_segment_b_rec.end_user_column_name,
529 p_prc_context_id,
530 decode(qp_util.get_qp_status,'S','Y','N'),
531 661,
532 g_segment_b_rec.application_column_name,
533 decode(g_segment_b_rec.created_by,1,'Y','N'),
534 g_segment_b_rec.required_flag,
535 g_segment_b_rec.column_seq_num,
536 g_segment_b_rec.column_seq_num,
537 p_valueset_id,
538 p_valueset_id,
539 p_format_type,
540 p_format_type,
541 null,null,null,null,null,null,null,null,
542 null,null,null,null,null,null,null,null,
543 g_segment_b_rec.created_by,
544 g_segment_b_rec.creation_date,
545 g_segment_b_rec.last_updated_by,
546 g_segment_b_rec.last_update_date,
547 g_segment_b_rec.last_update_login,
548 null,null,null);
549 end;
550 --
551 PROCEDURE p_insert_segment_tl is
552 -- Private procedure to insert Segments in QP_SEGMENTS_TL.
553 begin
554 INSERT into QP_SEGMENTS_TL
555 (SEGMENT_ID,
556 LANGUAGE,
557 SOURCE_LANG,
558 SEEDED_SEGMENT_NAME,
559 USER_SEGMENT_NAME,
560 CREATED_BY,
561 CREATION_DATE,
562 LAST_UPDATED_BY,
563 LAST_UPDATE_DATE,
564 LAST_UPDATE_LOGIN ) values
565 (g_segment_seqno,
566 g_segment_tl_rec.language,
567 g_segment_tl_rec.source_lang,
568 g_segment_tl_rec.form_left_prompt,
569 g_segment_tl_rec.form_left_prompt,
570 g_segment_tl_rec.created_by,
571 g_segment_tl_rec.creation_date,
572 g_segment_tl_rec.last_updated_by,
573 g_segment_tl_rec.last_update_date,
574 g_segment_tl_rec.last_update_login);
575 end;
576 --
577 PROCEDURE p_insert_ssc (p_source_system_code in varchar2,
578 p_pte_code in varchar2) is
579 -- Private procedure to insert Source Systems.
580 begin
581 g_ssc_seqno := g_ssc_seqno + 1;
582 INSERT into QP_PTE_SOURCE_SYSTEMS
583 (PTE_SOURCE_SYSTEM_ID,
584 PTE_CODE,
585 APPLICATION_SHORT_NAME,
586 ENABLED_FLAG,
587 CONTEXT,
588 ATTRIBUTE1,
589 ATTRIBUTE2,
590 ATTRIBUTE3,
591 ATTRIBUTE4,
592 ATTRIBUTE5,
593 ATTRIBUTE6,
594 ATTRIBUTE7,
595 ATTRIBUTE8,
596 ATTRIBUTE9,
597 ATTRIBUTE10,
598 ATTRIBUTE11,
599 ATTRIBUTE12,
600 ATTRIBUTE13,
601 ATTRIBUTE14,
602 ATTRIBUTE15,
603 CREATED_BY,
604 CREATION_DATE,
605 LAST_UPDATED_BY,
606 LAST_UPDATE_DATE,
607 LAST_UPDATE_LOGIN,
608 PROGRAM_APPLICATION_ID,
609 PROGRAM_ID,
610 PROGRAM_UPDATE_DATE) values
611 (g_ssc_seqno,
612 p_pte_code,
613 p_source_system_code,
614 'Y',
615 null,null,null,null,
616 null,null,null,null,
617 null,null,null,null,
618 null,null,null,null,
619 1,
620 sysdate,
621 1,
622 sysdate,
623 null,
624 null,null,null);
625 end;
626 --
627 PROCEDURE p_insert_rqt (p_request_type_code in varchar2,
628 p_pte_code in varchar2) is
629 -- Private procedure to insert Request Types in QP_PTE_REQUEST_TYPES_B/TL tables.
630 l_order_level_global_struct varchar2(80);
631 l_line_level_global_struct varchar2(80);
632 begin
633 if p_request_type_code = 'ASO' then
634 l_order_level_global_struct := 'ASO_PRICING_INT.G_HEADER_REC';
635 l_line_level_global_struct := 'ASO_PRICING_INT.G_LINE_REC';
636 elsif p_request_type_code = 'OKC' then
637 l_order_level_global_struct := 'OKC_PRICE_PUB.G_CONTRACT_INFO';
638 l_line_level_global_struct := 'OKC_PRICE_PUB.G_CONTRACT_INFO';
639 elsif p_request_type_code = 'INV' then
640 l_order_level_global_struct := 'INV_IC_ORDER_PUB.G_HDR';
641 l_line_level_global_struct := 'INV_IC_ORDER_PUB.G_LINE';
642 elsif p_request_type_code = 'ONT' then
643 l_order_level_global_struct := 'OE_ORDER_PUB.G_HDR';
644 l_line_level_global_struct := 'OE_ORDER_PUB.G_LINE';
645 else
646 l_order_level_global_struct := null;
647 l_line_level_global_struct := null;
648 end if;
649 --
650 INSERT into QP_PTE_REQUEST_TYPES_B
651 (REQUEST_TYPE_CODE,
652 PTE_CODE,
653 ORDER_LEVEL_GLOBAL_STRUCT,
654 LINE_LEVEL_GLOBAL_STRUCT,
655 ORDER_LEVEL_VIEW_NAME,
656 LINE_LEVEL_VIEW_NAME,
657 ENABLED_FLAG,
658 CONTEXT,
659 ATTRIBUTE1,
660 ATTRIBUTE2,
661 ATTRIBUTE3,
662 ATTRIBUTE4,
663 ATTRIBUTE5,
664 ATTRIBUTE6,
665 ATTRIBUTE7,
666 ATTRIBUTE8,
667 ATTRIBUTE9,
668 ATTRIBUTE10,
669 ATTRIBUTE11,
670 ATTRIBUTE12,
671 ATTRIBUTE13,
672 ATTRIBUTE14,
673 ATTRIBUTE15,
674 CREATED_BY,
675 CREATION_DATE,
676 LAST_UPDATED_BY,
677 LAST_UPDATE_DATE,
678 LAST_UPDATE_LOGIN,
679 PROGRAM_APPLICATION_ID,
680 PROGRAM_ID,
681 PROGRAM_UPDATE_DATE) values
682 (p_request_type_code,
683 p_pte_code,
684 l_order_level_global_struct,
685 l_line_level_global_struct,
686 null,
687 null,
688 'Y',
689 null,null,null,null,
690 null,null,null,null,
691 null,null,null,null,
692 null,null,null,null,
693 1,
694 sysdate,
695 1,
696 sysdate,
697 null,
698 null,null,null);
699 --
700 INSERT into QP_PTE_REQUEST_TYPES_TL
701 (REQUEST_TYPE_CODE,
702 LANGUAGE,
703 SOURCE_LANG,
704 REQUEST_TYPE_DESC,
705 CREATED_BY,
706 CREATION_DATE,
707 LAST_UPDATED_BY,
708 LAST_UPDATE_DATE,
709 LAST_UPDATE_LOGIN)
710 select p_request_type_code,
711 language,
712 source_lang,
713 nvl(description,meaning),
714 created_by,
715 creation_date,
716 last_updated_by,
717 last_update_date,
718 last_update_login
719 from fnd_lookup_values
720 where lookup_type = 'REQUEST_TYPE' and
721 lookup_code = p_request_type_code;
722 --
723 end;
724 --
725 PROCEDURE p_insert_pte_segments (p_segment_id in number,
726 p_pte_code in varchar2,
727 p_segment_level in varchar2,
728 p_sourcing_method in varchar2,
729 p_sourcing_enabled in varchar2) is
730 -- Private procedure to insert PTE-Segments in qp_pte_segments.
731 begin
732 g_psg_seqno := g_psg_seqno + 1;
733 INSERT into QP_PTE_SEGMENTS
734 (SEGMENT_PTE_ID,
735 SEGMENT_ID,
736 PTE_CODE,
737 SEGMENT_LEVEL,
738 SOURCING_ENABLED,
739 SEEDED_SOURCING_METHOD,
740 USER_SOURCING_METHOD,
741 SOURCING_STATUS,
742 LOV_ENABLED,
743 LIMITS_ENABLED,
744 CONTEXT,
745 ATTRIBUTE1,
746 ATTRIBUTE2,
747 ATTRIBUTE3,
748 ATTRIBUTE4,
749 ATTRIBUTE5,
750 ATTRIBUTE6,
751 ATTRIBUTE7,
752 ATTRIBUTE8,
753 ATTRIBUTE9,
754 ATTRIBUTE10,
755 ATTRIBUTE11,
756 ATTRIBUTE12,
757 ATTRIBUTE13,
758 ATTRIBUTE14,
759 ATTRIBUTE15,
760 CREATED_BY,
761 CREATION_DATE,
762 LAST_UPDATED_BY,
763 LAST_UPDATE_DATE,
764 LAST_UPDATE_LOGIN,
765 PROGRAM_APPLICATION_ID,
766 PROGRAM_ID,
767 PROGRAM_UPDATE_DATE) values
768 (g_psg_seqno,
769 p_segment_id,
770 p_pte_code,
771 p_segment_level,
772 p_sourcing_enabled,
773 p_sourcing_method,
774 p_sourcing_method,
775 'N',
776 'Y',
777 'Y',
778 null,null,null,null,
779 null,null,null,null,
780 null,null,null,null,
781 null,null,null,null,
782 1, -- fnd_profile.value('USER_ID')
783 sysdate,
784 1,
785 sysdate,
786 null,
787 null,null,null);
788 end;
789 --
790 PROCEDURE p_insert_sourcing( p_request_type_code in varchar2,
791 p_attribute_sourcing_level in varchar2,
792 p_value_string in varchar2,
793 p_enabled_flag in varchar2) is
794 -- Private procedure to insert Sourcing rules in QP_ATTRIBUTE_SOURCING.
795 begin
796 g_source_seqno := g_source_seqno + 1;
797 INSERT into QP_ATTRIBUTE_SOURCING
798 (ATTRIBUTE_SOURCING_ID,
799 SEGMENT_ID,
800 REQUEST_TYPE_CODE,
801 ATTRIBUTE_SOURCING_LEVEL,
802 APPLICATION_ID,
803 SEEDED_SOURCING_TYPE,
804 USER_SOURCING_TYPE,
805 SEEDED_VALUE_STRING,
806 USER_VALUE_STRING,
807 SEEDED_FLAG,
808 ENABLED_FLAG,
809 CONTEXT,
810 ATTRIBUTE1,
811 ATTRIBUTE2,
812 ATTRIBUTE3,
813 ATTRIBUTE4,
814 ATTRIBUTE5,
815 ATTRIBUTE6,
816 ATTRIBUTE7,
817 ATTRIBUTE8,
818 ATTRIBUTE9,
819 ATTRIBUTE10,
820 ATTRIBUTE11,
821 ATTRIBUTE12,
822 ATTRIBUTE13,
823 ATTRIBUTE14,
824 ATTRIBUTE15,
825 CREATED_BY,
826 CREATION_DATE,
827 LAST_UPDATED_BY,
828 LAST_UPDATE_DATE,
829 LAST_UPDATE_LOGIN,
830 PROGRAM_APPLICATION_ID,
831 PROGRAM_ID,
832 PROGRAM_UPDATE_DATE) values
833 (g_source_seqno,
834 g_all_seg_rec.segment_id,
835 p_request_type_code,
836 p_attribute_sourcing_level,
837 661,
838 g_sourcing_rec.src_type,
839 g_sourcing_rec.src_type,
840 p_value_string,
841 p_value_string,
842 decode(g_sourcing_rec.created_by,1,'Y','N'),
843 p_enabled_flag, --'Y', upgrade enabled_flag also
844 null,null,null,null,null,null,null,null,
845 null,null,null,null,null,null,null,null,
846 g_sourcing_rec.created_by,
847 g_sourcing_rec.creation_date,
848 g_sourcing_rec.last_updated_by,
849 g_sourcing_rec.last_update_date,
850 g_sourcing_rec.last_update_login,
851 null,null,null);
852 end;
853 --
854 /*
855 FUNCTION p_mapping_rule_exists (p_prc_context_code in varchar2,
856 p_segment_mapping_column in varchar2,
857 p_application_short_name in varchar2)
858 -- Private procedure to find out if Attribute Mapping exists.
859 return number is
860 l_sourcing_rec g_sourcing_rec%rowtype;
861 l_i number := 0;
862 begin
863 open attribute_sourcing_cur(p_prc_context_code,
864 p_segment_mapping_column,
865 p_application_short_name);
866 fetch attribute_sourcing_cur into l_sourcing_rec;
867 if attribute_sourcing_cur%notfound then
868 l_i := -1;
869 end if;
870 close attribute_sourcing_cur;
871 return(l_i);
872 end;
873 */
874 --
875 PROCEDURE p_delete_PTE_attribute_links is
876 -- Private procedure to delete certain unwanted PTE_Attribute Links.
877 -- Private procedure to find out if Attribute Mapping exists.
878 begin
879 /*
880 --
881 -- 1. For LOGISTICS context and PTEs other than 'Logistics',
882 --
883 delete from qp_pte_segments a
884 where a.pte_code <> 'LOGSTX' and
885 exists ( select 'x'
886 from qp_segments_b b,
887 qp_prc_contexts_b c
888 where b.prc_context_id = c.prc_context_id and
889 b.segment_id = a.segment_id and
890 c.prc_context_code = 'LOGISTICS') and
891 a.created_by = 1;
892 --
893 -- 2. COUPON_NO attribute and 'Logistics' PTE
894 --
895 delete from qp_pte_segments a
896 where a.pte_code = 'LOGSTX' and
897 exists ( select 'x'
898 from qp_segments_b b
899 where b.segment_id = a.segment_id and
900 b.segment_code = 'COUPON_NO') and
901 a.created_by = 1;
902 --
903 -- 3. 'Number of Students' attribute and PTEs other than 'Order Fulfillment'.
904 --
905 delete from qp_pte_segments a
906 where a.pte_code <> 'ORDFUL' and
907 exists ( select 'x'
908 from qp_segments_b b
909 where b.segment_id = a.segment_id and
910 b.segment_code = 'Number of students') and
911 a.created_by = 1;
912 */
913 --
914 -- 4. Links having Attribute Mapping Method as ATTRIBUTE MAPPING
915 -- and PTE as INTCOM without any Attribute Mapping rules.
916 --
917 delete from qp_pte_segments a
918 where a.pte_code = 'INTCOM' and
919 a.seeded_sourcing_method = 'ATTRIBUTE MAPPING' and
920 not exists ( select 'x'
921 from qp_attribute_sourcing b,
922 qp_pte_request_types_b c
923 where b.segment_id = a.segment_id and
924 b.request_type_code = c.request_type_code and
925 a.pte_code = c.pte_code) and
926 a.created_by = 1;
927 --
928 -- 5. Links having Attribute Mapping Method as ATTRIBUTE MAPPING
929 -- and PTE as DEMAND without any Attribute Mapping rules.
930 --
931 delete from qp_pte_segments a
932 where a.pte_code = 'DEMAND' and
933 a.seeded_sourcing_method = 'ATTRIBUTE MAPPING' and
934 not exists ( select 'x'
935 from qp_attribute_sourcing b,
936 qp_pte_request_types_b c
937 where b.segment_id = a.segment_id and
938 b.request_type_code = c.request_type_code and
939 a.pte_code = c.pte_code) and
940 a.created_by = 1;
941 end;
942 --
943 PROCEDURE p_detect_dup_mapping_cols(p_prc_context_id in number,
944 p_segment_mapping_column in varchar2,
945 p_segment_code in varchar2) is
946 l_segment_id number;
947 l_segment_code varchar2(30);
948 begin
949 select a.segment_id,
950 a.segment_code
951 into l_segment_id,
952 l_segment_code
953 from qp_segments_b a, qp_prc_contexts_b b
954 where a.prc_context_id = b.prc_context_id and
955 a.prc_context_id = p_prc_context_id and
956 segment_mapping_column = p_segment_mapping_column and
957 segment_code <> p_segment_code and
958 rownum = 1;
959 --
960 insert into qp_upgrade_errors
961 (creation_date,
962 created_by,
963 last_update_date,
964 last_updated_by,
965 error_type,
966 error_desc,
967 error_module) values
968 (sysdate,
969 fnd_global.user_id,
970 sysdate,
971 fnd_global.user_id,
972 'ATTRIBUTE_MANAGER_DATA_UPGRADE',
973 substr('A new attribute '|| l_segment_code ||
974 ' mapped to '|| g_segment_b_rec.application_column_name ||
975 ' already exists. Add this Attribute and it''s PTE-Links and AM Rules manually.'||
976 ' Refer to the Pricing Implementation Guide for details.',1,200),
977 'Attribute Manager');
978 --
979 delete from qp_segments_tl where segment_id = l_segment_id;
980 delete from qp_segments_b where segment_id = l_segment_id;
981 exception
982 when no_data_found then
983 null;
984 end;
985 --
986 PROCEDURE p_initialize_sequences is
987 begin
988 select nvl(max(prc_context_id),100)
989 into g_context_seqno
990 from qp_prc_contexts_b
991 where prc_context_id < 100000;
992 --
993 select nvl(max(segment_id),100)
994 into g_segment_seqno
995 from qp_segments_b
996 where segment_id < 100000;
997 --
998 select nvl(max(pte_source_system_id),100)
999 into g_ssc_seqno
1000 from qp_pte_source_systems
1001 where pte_source_system_id < 100000;
1002 --
1003 select nvl(max(segment_pte_id),100)
1004 into g_psg_seqno
1005 from qp_pte_segments
1006 where segment_pte_id < 100000;
1007 --
1008 select nvl(max(attribute_sourcing_id),100)
1009 into g_source_seqno
1010 from qp_attribute_sourcing
1011 where attribute_sourcing_id < 100000;
1012 end;
1013 --
1014 PROCEDURE upgrade_atm is
1015 CURSOR context_b_cur (p_flexfield_name in varchar2) is
1016 SELECT *
1017 FROM fnd_descr_flex_contexts
1018 WHERE application_id = 661
1019 AND descriptive_flexfield_name = p_flexfield_name
1020 ORDER BY descriptive_flex_context_code;
1021 --
1022 CURSOR context_tl_cur (p_flexfield_name in varchar2,
1023 p_context_code in varchar2) is
1024 SELECT *
1025 FROM fnd_descr_flex_contexts_tl
1026 WHERE application_id = 661
1027 AND descriptive_flexfield_name = p_flexfield_name
1028 AND descriptive_flex_context_code = p_context_code;
1029 --
1030 CURSOR segment_b_cur (p_flexfield_name in varchar2,
1031 p_context_code in varchar2) is
1032 SELECT *
1033 FROM fnd_descr_flex_column_usages
1034 WHERE application_id = 661
1035 AND descriptive_flexfield_name = p_flexfield_name
1036 AND descriptive_flex_context_code = p_context_code
1037 ORDER BY end_user_column_name;
1038 --
1039 CURSOR segment_tl_cur (p_flexfield_name in varchar2,
1040 p_context_code in varchar2,
1041 p_segment_code in varchar2) is
1042 SELECT *
1043 FROM fnd_descr_flex_col_usage_tl
1044 WHERE application_id = 661
1045 AND descriptive_flexfield_name = p_flexfield_name
1046 AND descriptive_flex_context_code = p_context_code
1047 AND application_column_name = p_segment_code;
1048 --
1049 CURSOR req_ssc_cur is
1050 SELECT request_type_code,
1051 source_system_code
1052 FROM qp_price_req_sources
1053 UNION
1054 SELECT 'ONT','AMS'
1055 FROM dual
1056 UNION
1057 SELECT 'ONT','QP'
1058 FROM dual
1059 UNION
1060 SELECT 'MSD','QP'
1061 FROM dual
1062 /*
1063 UNION
1064 SELECT 'KEN','KQM'
1065 FROM dual
1066 */
1067 UNION
1068 SELECT 'FTE','FTE'
1069 FROM dual;
1070 --
1071 CURSOR pte_cur is
1072 SELECT *
1073 FROM qp_lookups
1074 WHERE lookup_type = 'QP_PTE_TYPE';
1075 --
1076 CURSOR get_pte_cur (p_application_short_name in varchar2)is
1077 SELECT pte_code
1078 FROM qp_pte_source_systems
1079 WHERE application_short_name = p_application_short_name;
1080 --
1081 CURSOR all_segments_cur is
1082 SELECT b.prc_context_code,
1083 a.*
1084 FROM qp_segments_b a,
1085 qp_prc_contexts_b b
1086 WHERE b.prc_context_id = a.prc_context_id;
1087 --
1088 CURSOR pte_seg_cur (p_prc_context_code in varchar2,
1089 p_segment_mapping_column in varchar2) is
1090 SELECT substr(c1.value_string,1,30) application_short_name
1091 FROM oe_def_attr_condns b,
1092 ak_object_attributes a,
1093 oe_def_condn_elems c,
1094 oe_def_condn_elems c1
1095 WHERE substr(c.value_string,1,30) = p_prc_context_code and
1096 b.attribute_code = p_segment_mapping_column and
1097 b.database_object_name = a.database_object_name and
1098 b.attribute_code = a.attribute_code and
1099 c.condition_id = b.condition_id and
1100 c.attribute_code like '%CONTEXT%' and
1101 c1.condition_id = b.condition_id and
1102 c1.attribute_code = 'SRC_SYSTEM_CODE' and
1103 a.attribute_application_id = 661
1104 /* commented out as enabled_flag is also upgraded now */
1105 -- nvl(b.enabled_flag,'Y') = 'Y'
1106 GROUP BY substr(c1.value_string,1,30),
1107 substr(c.value_string,1,30),
1108 b.attribute_code;
1109 --
1110 CURSOR assign_pte_cur is
1111 select a.list_header_id,b.pte_code
1112 from qp_list_headers_b a,qp_pte_source_systems b
1113 where nvl(a.source_system_code,'x') = b.application_short_name
1114 order by decode(b.pte_code,'ORDFUL',2,1);
1115 --
1116 l_segment_level varchar2(10);
1117 l_request_type_code varchar2(30);
1118 l_flexfield_name varchar2(30);
1119 l_format_type varchar2(1);
1120 l_pte_code varchar2(30);
1121 l_application_short_name varchar2(30);
1122 l_value_string varchar2(2000);
1123 l_attribute_sourcing_level varchar2(30);
1124 l_list_header_id number;
1125 l_prc_context_id number;
1126 dummy varchar2(1);
1127 x_prc_context_id number;
1128 --
1129 retval number;
1130 segval number;
1131 atm_serious_problem EXCEPTION;
1132 --
1133 BEGIN
1134 --dbms_output.enable(1000000);
1135 -- Initialize sequences.
1136 p_initialize_sequences;
1137 --
1138 FOR flexno in 1..2 loop
1139 if flexno = 1 then
1140 l_flexfield_name := 'QP_ATTR_DEFNS_QUALIFIER';
1141 elsif flexno = 2 then
1142 l_flexfield_name := 'QP_ATTR_DEFNS_PRICING';
1143 end if;
1144 --
1145 open context_b_cur(l_flexfield_name);
1146 -- Copy all Contexts from 'Qualifier Contexts' and Pricing Contexts'.
1147 loop
1148 fetch context_b_cur INTO g_context_b_rec;
1149 exit when context_b_cur%notfound;
1150 --
1151 retval := p_con_exists(g_context_b_rec.descriptive_flex_context_code,
1152 l_flexfield_name,
1153 x_prc_context_id);
1154 if retval in (0,-1) then
1155 if retval = -1 then
1156 p_insert_context_b (l_flexfield_name);
1157 --
1158 open context_tl_cur(l_flexfield_name,g_context_b_rec.descriptive_flex_context_code);
1159 loop
1160 fetch context_tl_cur INTO g_context_tl_rec;
1161 exit when context_tl_cur%notfound;
1162 --
1163 p_insert_context_tl;
1164 --
1165 end loop;
1166 close context_tl_cur;
1167 end if;
1168 --
1169 -- Opening Segments Cursor
1170 --
1171 open segment_b_cur(l_flexfield_name,g_context_b_rec.descriptive_flex_context_code);
1172 loop
1173 fetch segment_b_cur INTO g_segment_b_rec;
1174 exit when segment_b_cur%notfound;
1175 --
1176 --l_valueset_id := nvl(g_segment_b_rec.flex_value_set_id,102189);
1177 l_format_type := p_get_format_type (g_segment_b_rec.flex_value_set_id);
1178 --
1179 segval := p_seg_exists(g_context_b_rec.descriptive_flex_context_code,
1180 l_flexfield_name,
1181 g_segment_b_rec.end_user_column_name,
1182 g_segment_b_rec.application_column_name);
1183 if segval = -1 then
1184 if retval = 0 then
1185 p_insert_segment_b (x_prc_context_id,g_segment_b_rec.flex_value_set_id, l_format_type);
1186 l_prc_context_id := x_prc_context_id;
1187 elsif retval = -1 then
1188 p_insert_segment_b (g_context_seqno,g_segment_b_rec.flex_value_set_id, l_format_type);
1189 l_prc_context_id := g_context_seqno;
1190 end if;
1191 --
1192 open segment_tl_cur( l_flexfield_name,
1193 g_context_b_rec.descriptive_flex_context_code,
1194 g_segment_b_rec.application_column_name);
1195 loop
1196 fetch segment_tl_cur INTO g_segment_tl_rec;
1197 exit when segment_tl_cur%notfound;
1198 p_insert_segment_tl;
1199 end loop;
1200 close segment_tl_cur;
1201 --
1202 p_detect_dup_mapping_cols(l_prc_context_id,
1203 g_segment_b_rec.application_column_name,
1204 g_segment_b_rec.end_user_column_name);
1205 --
1206 elsif segval = -2 then
1207 raise atm_serious_problem;
1208 end if;
1209 --
1210 end loop;
1211 close segment_b_cur;
1212 --
1213 elsif retval = -2 then
1214 raise atm_serious_problem;
1215 end if;
1216 ---
1217 end loop;
1218 close context_b_cur;
1219 END loop;
1220 --
1221 -- Creating PTEs and copying Request Types and Source System Codes
1222 -- Creating Sourcing Rules
1223 --
1224 open req_ssc_cur;
1225 --
1226 -- This Cusror is the new seeded data of Source Systems-Request Types for the new system.
1227 --
1228 loop
1229 fetch req_ssc_cur INTO g_req_ssc_rec;
1230 exit when req_ssc_cur%notfound;
1231 --
1232 retval := p_req_exists(g_req_ssc_rec.request_type_code);
1233 if retval = 0 then
1234 --
1235 -- If Request Type exists in New System, get the PTE to which it is attached.
1236 -- If the PTE does not have the Source System, add the source system
1237 --
1238 l_pte_code := p_get_pte_for_rqt(g_req_ssc_rec.request_type_code);
1239 --
1240 if p_ssc_exists(g_req_ssc_rec.source_system_code, l_pte_code) = -1 then
1241 p_insert_ssc(g_req_ssc_rec.source_system_code,l_pte_code);
1242 end if;
1243 elsif retval = -1 then
1244 --
1245 -- If Request Type does not exist in New System,
1246 -- Follow a pre-determined way of associating the Request Type to PTEs as shown below.
1247 --
1248 if g_req_ssc_rec.request_type_code in ('ONT','ASO','OKC') then
1249 l_pte_code := 'ORDFUL';
1250 elsif g_req_ssc_rec.request_type_code = 'IC' then
1251 l_pte_code := 'INTCOM';
1252 elsif g_req_ssc_rec.request_type_code = 'MSD' then
1253 l_pte_code := 'DEMAND';
1254 elsif g_req_ssc_rec.request_type_code = 'FTE' then
1255 l_pte_code := 'LOGSTX';
1256 else
1257 g_pte_num := g_pte_num + 1;
1258 --
1259 -- If Request Type is not a seeded Request type, create a brand new PTE for it.
1260 --
1261 l_pte_code := g_req_ssc_rec.request_type_code||'_PTE';
1262 p_insert_lookup_code('QP_PTE_TYPE',l_pte_code);
1263 end if;
1264 --
1265 -- Insert the new and Unseeded Request Types into lookups and Attribute Mapping tables,
1266 -- with the PTEs as determined above.
1267 --
1268 p_insert_lookup_code('REQUEST_TYPE',g_req_ssc_rec.request_type_code);
1269 p_insert_rqt (g_req_ssc_rec.request_type_code,l_pte_code);
1270 --
1271 -- Insert into qp_pte_source_systems, source systems and their corresponding PTEs
1272 --
1273 if p_ssc_exists(g_req_ssc_rec.source_system_code,l_pte_code) = -1 then
1274 p_insert_ssc(g_req_ssc_rec.source_system_code,l_pte_code);
1275 end if;
1276 --
1277 elsif retval = -2 then
1278 raise atm_serious_problem;
1279 end if;
1280 end loop;
1281 close req_ssc_cur;
1282 --
1283 -- Attribute Mapping and sourcing
1284 --
1285 --bug 2559788 start
1286
1287 p_upd_bad_upg_seed_data;
1288
1289 -- bug 2559788 end
1290
1291 open all_segments_cur;
1292 loop
1293 fetch all_segments_cur INTO g_all_seg_rec;
1294 exit when all_segments_cur%notfound;
1295 --
1296 retval := p_seg_src_def_exists(g_all_seg_rec.prc_context_code,
1297 g_all_seg_rec.segment_mapping_column);
1298 --
1299 if retval = 0 then
1300 -- If a defaulting rule exists for the chosen segment, in the old system,
1301 -- find all Source Systems for that Segment and
1302 -- link the segment to the all PTE, based on PTE-Source System association.
1303 open pte_seg_cur(g_all_seg_rec.prc_context_code,
1304 g_all_seg_rec.segment_mapping_column);
1305 loop
1306 fetch pte_seg_cur INTO l_application_short_name;
1307 exit when pte_seg_cur%notfound;
1308 --
1309 open get_pte_cur(l_application_short_name);
1310 loop
1311 fetch get_pte_cur INTO l_pte_code;
1312 exit when get_pte_cur%notfound;
1313 -- Look for level , irrespective of Source System Code.
1314 -- In case there is BOTH for any SSC, that must override LINE or ORDER for any other SSC
1315 l_segment_level := p_get_level (g_all_seg_rec.prc_context_code,
1316 g_all_seg_rec.segment_mapping_column);
1317 if p_psg_exists(g_all_seg_rec.segment_id,l_pte_code) = -1 then
1318 p_insert_pte_segments (g_all_seg_rec.segment_id,
1319 l_pte_code,
1320 l_segment_level,
1321 'ATTRIBUTE MAPPING',
1322 'Y');
1323 end if;
1324 open attribute_sourcing_cur(g_all_seg_rec.prc_context_code,
1325 g_all_seg_rec.segment_mapping_column,
1326 l_application_short_name);
1327 -- For Segments with defaulting rules, they must be sourced in the
1328 -- new system. The Source System code of the segments will determine, as to
1329 -- which Request types, they will be sourced for.
1330 loop
1331 fetch attribute_sourcing_cur INTO g_sourcing_rec;
1332 exit when attribute_sourcing_cur%notfound;
1333 --
1334 if g_sourcing_rec.src_type in ('API','API_MULTIREC') then
1335 l_value_string := g_sourcing_rec.src_api_pkg||'.'||g_sourcing_rec.src_api_fn;
1336 elsif g_sourcing_rec.src_type = 'PROFILE_OPTION' then
1337 l_value_string := g_sourcing_rec.src_profile_option;
1338 elsif g_sourcing_rec.src_type = 'RELATED_RECORD' then
1339 l_value_string := g_sourcing_rec.src_database_object_name||'.'||
1340 g_sourcing_rec.src_attribute_code;
1341 elsif g_sourcing_rec.src_type = 'SAME_RECORD' then
1342 l_value_string := g_sourcing_rec.src_attribute_code;
1343 elsif g_sourcing_rec.src_type = 'SYSTEM' then
1344 l_value_string := g_sourcing_rec.src_system_variable_expr;
1345 elsif g_sourcing_rec.src_type in ('SALES_CHANNEL_CODE','SOURCE_TYPE_CODE',
1346 'TAX_EXEMPT_CODE','CONSTANT') then
1347 l_value_string := g_sourcing_rec.src_constant_value;
1348 end if;
1349 --
1350 if instr(upper(g_sourcing_rec.database_object_name),'LINE') = 0 then
1351 l_attribute_sourcing_level := 'ORDER';
1352 else
1353 l_attribute_sourcing_level := 'LINE';
1354 end if;
1355 --
1356 -- Rules of sourcing :
1357 -- If the Source System is QP,sourcing will be done for ONT Request Types only.
1358 -- else if it is OKC,sourcing will be done for OKC Request Types only.
1359 -- else if it is ASO,sourcing will be done for ASO Request Types only.
1360 -- else if it is AMS,sourcing will be done for ONT and ASO Request Types respectively.
1361 --
1362 if l_application_short_name = 'QP' then
1363 l_request_type_code := 'ONT';
1364 elsif l_application_short_name = 'OKC' then
1365 l_request_type_code := 'OKC';
1366 elsif l_application_short_name = 'ASO' then
1367 l_request_type_code := 'ASO';
1368 elsif l_application_short_name = 'AMS' then
1369 l_request_type_code := 'ONT';
1370 end if;
1371 --
1372 if p_sourcing_exists(g_all_seg_rec.segment_id,l_request_type_code,l_attribute_sourcing_level) = -1 then
1373 p_insert_sourcing(l_request_type_code,l_attribute_sourcing_level,l_value_string, g_sourcing_rec.enabled_flag);
1374 end if;
1375 --
1376 if l_application_short_name = 'AMS' then
1377 if p_sourcing_exists(g_all_seg_rec.segment_id,'ASO',l_attribute_sourcing_level) = -1 then
1378 p_insert_sourcing('ASO',l_attribute_sourcing_level,l_value_string, g_sourcing_rec.enabled_flag);
1379 end if;
1380 end if;
1381 --
1382 end loop;
1383 close attribute_sourcing_cur;
1384 --
1385 end loop;
1386 close get_pte_cur;
1387 --
1388 end loop;
1389 close pte_seg_cur;
1390 --
1391 elsif retval = -1 then
1392 -- If a defaulting rule does not exist for the chosen segment, in the old system,
1393 -- link the segment to the all PTEs.
1394 open pte_cur;
1395 loop
1396 fetch pte_cur INTO g_pte_rec;
1397 exit when pte_cur%notfound;
1398 --
1399 if p_psg_exists(g_all_seg_rec.segment_id,g_pte_rec.lookup_code) = -1 then
1400 -- Do not create pte_segment links
1401 -- for LOGISTICS context and PTEs other than 'Logistics',
1402 -- COUPON_NO attribute and 'Logistics' PTE,
1403 -- 'Number of Students' attribute and PTEs other than 'Order Fulfillment'.
1404 if (g_all_seg_rec.prc_context_code = 'LOGISTICS' and
1405 g_pte_rec.lookup_code <> 'LOGSTX') or
1406 (g_all_seg_rec.segment_code = 'COUPON_NO' and
1407 g_pte_rec.lookup_code = 'LOGSTX') or
1408 (g_all_seg_rec.segment_code = 'TOTAL_ITEM_QUANTITY' and
1409 g_pte_rec.lookup_code = 'ORDFUL') or
1410 (g_all_seg_rec.segment_code = 'Number of students' and
1411 g_pte_rec.lookup_code <> 'ORDFUL') then
1412 null;
1413 else
1414 p_insert_pte_segments (g_all_seg_rec.segment_id,
1415 g_pte_rec.lookup_code,
1416 'LINE',
1417 'USER ENTERED',
1418 'N');
1419 end if;
1420 end if;
1421 end loop;
1422 close pte_cur;
1423 --
1424 elsif retval = -2 then
1425 raise atm_serious_problem;
1426 end if;
1427 --
1428 end loop;
1429 close all_segments_cur;
1430 --
1431 -- Delete PTE-Attributes Links that are not required
1432 --
1433 p_delete_PTE_attribute_links;
1434 --
1435 commit;
1436 --
1437 -- Derving PTE code and assigning PTE code to new pte_code column
1438 -- in qp_list_headers_b table
1439 --
1440 open assign_pte_cur;
1441 loop
1442 fetch assign_pte_cur INTO l_list_header_id,l_pte_code;
1443 exit when assign_pte_cur%notfound;
1444 --
1445 update qp_list_headers_b
1446 set pte_code = l_pte_code
1447 where list_header_id = l_list_header_id;
1448 --
1449 end loop;
1450 close assign_pte_cur;
1451 --
1452 commit;
1453 --
1454 EXCEPTION
1455 WHEN atm_serious_problem THEN
1456 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1457 THEN
1458 FND_MESSAGE.SET_NAME('QP','QP_UPGRADE_SERIOUS_ERROR');
1459 FND_MESSAGE.SET_TOKEN('ERROR_DESCRIPTION',sqlerrm);
1460 OE_MSG_PUB.Add;
1461 END IF;
1462 WHEN OTHERS THEN
1463 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1464 THEN
1465 OE_MSG_PUB.Add_Exc_Msg
1466 ( G_PKG_NAME,
1467 'Upgrade_ATM');
1468 END IF;
1469 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1470 END upgrade_atm;
1471 --
1472 END QP_ATM_UPGRADE;