[Home] [Help]
PACKAGE BODY: APPS.QP_UPGRADE_UTIL
Source
1 PACKAGE BODY QP_UPGRADE_UTIL AS
2 /* $Header: QPXUPGRB.pls 120.0 2005/06/02 00:40:33 appldev noship $ */
3 --===========================================================================
4 -- This script defines a procedure qp_update_upgrade to update the default type
5 -- This script defines a procedure qp_update_upgrade to update the default type
6 -- default value, required flag, sequence number, enabled flag and displyed flag
7 -- columns in the flexfiled table. These columns were not upgraded in the express-- upgrade.
8 -- default value, required flag, sequence number, enabled flag and displyed flag
9 -- columns in the flexfiled table. These columns were not upgraded in the express-- upgrade.
10
11 --===========================================================================
12
13
14 FUNCTION check_context_existance( p_application_id IN fnd_application.application_id%TYPE,
15 p_descriptive_flexfield_name IN VARCHAR2,
16 p_descr_flex_context_code IN VARCHAR2)
17 RETURN BOOLEAN
18
19 IS
20
21 dummy NUMBER(1);
22 x_context_exists BOOLEAN := TRUE;
23
24 BEGIN
25 SELECT NULL INTO dummy
26 FROM fnd_descr_flex_contexts
27 WHERE application_id = p_application_id
28 AND descriptive_flexfield_name = p_descriptive_flexfield_name
29 AND descriptive_flex_context_code = p_descr_flex_context_code;
30
31 --dbms_output.put_line ('Context Check Successful');
32 return x_context_exists;
33
34 EXCEPTION
35 WHEN no_data_found THEN
36 x_context_exists := FALSE;
37 return x_context_exists;
38 WHEN OTHERS THEN
39 NULL;
40 --dbms_output.put_line ('Error in Context Check');
41 END;
42
43 FUNCTION check_segment_existance( p_application_id NUMBER,
44 p_context_code VARCHAR2,
45 p_flexfield_name VARCHAR2,
46 p_application_column_name VARCHAR2)
47 RETURN BOOLEAN
48 IS
49
50 dummy NUMBER(1);
51 x_seg_exists BOOLEAN := TRUE;
52
53 BEGIN
54 select NULL INTO dummy
55 from FND_DESCR_FLEX_COLUMN_USAGES
56 where APPLICATION_ID = p_application_id
57 and DESCRIPTIVE_FLEX_CONTEXT_CODE = p_context_code
58 and DESCRIPTIVE_FLEXFIELD_NAME = p_flexfield_name
59 and APPLICATION_COLUMN_NAME = p_application_column_name;
60
61 --dbms_output.put_line ('Segment Check Successful');
62 return x_seg_exists ;
63
64 EXCEPTION
65 WHEN no_data_found THEN
66 x_seg_exists := FALSE;
67 return x_seg_exists;
68 WHEN OTHERS THEN
69 NULL;
70 --dbms_output.put_line ('Error in Segment Check');
71 END;
72
73 FUNCTION check_segment_name_existance( p_application_id NUMBER,
74 p_context_code VARCHAR2,
75 p_flexfield_name VARCHAR2,
76 p_segment_name VARCHAR2)
77 RETURN BOOLEAN
78 IS
79
80 dummy NUMBER(1);
81 x_seg_exists BOOLEAN := TRUE;
82
83 BEGIN
84 select NULL INTO dummy
85 from FND_DESCR_FLEX_COLUMN_USAGES
86 where APPLICATION_ID = p_application_id
87 and DESCRIPTIVE_FLEX_CONTEXT_CODE = p_context_code
88 and DESCRIPTIVE_FLEXFIELD_NAME = p_flexfield_name
89 and END_USER_COLUMN_NAME = p_segment_name;
90
91 --dbms_output.put_line ('Segment Name Check Successful');
92 return x_seg_exists ;
93
94 EXCEPTION
95 WHEN no_data_found THEN
96 x_seg_exists := FALSE;
97 return x_seg_exists;
98 WHEN OTHERS THEN
99 NULL;
100 --dbms_output.put_line ('Error in Segment Name Check');
101 END;
102
103 PROCEDURE QP_UPDATE_UPGRADE( P_PRODUCT IN VARCHAR2
104 , P_NEW_PRODUCT IN VARCHAR2
105 , P_FLEXFIELD_NAME IN VARCHAR2
106 , P_NEW_FLEXFIELD_NAME IN VARCHAR2)
107 IS
108 P_FLEXFIELD FND_DFLEX.DFLEX_R;
109 P_FLEXINFO FND_DFLEX.DFLEX_DR;
110 L_CONTEXTS FND_DFLEX.CONTEXTS_DR;
111 GDE_CONTEXTS FND_DFLEX.CONTEXTS_DR;
112 L_SEGMENTS FND_DFLEX.SEGMENTS_DR;
113 GDE_SEGMENTS FND_DFLEX.SEGMENTS_DR;
114 NEW_GDE_SEGMENTS FND_DFLEX.SEGMENTS_DR;
115 L_REQUIRED VARCHAR2(5);
116 L_ENABLED VARCHAR2(5);
117 L_DISPLAYED VARCHAR2(5);
118
119 L_VALUE_SET_ID NUMBER := 0;
120 L_VALUE_SET VARCHAR2(100) := NULL;
121 L_SEGMENT_COUNT NUMBER;
122 p_segment_name VARCHAR2(240);
123 NEW_GDE_CONTEXT_CODE CONSTANT VARCHAR2(30) := 'Upgrade Context';
124 OLD_GDE_CONTEXT_CODE CONSTANT VARCHAR2(30) := 'Global Data Elements';
125 G_QP_ATTR_DEFNS_PRICING CONSTANT VARCHAR2(30) := 'QP_ATTR_DEFNS_PRICING';
126 QP_APPLICATION_ID CONSTANT fnd_application.application_id%TYPE := 661;
127 p_context_name VARCHAR2(240);
128 p_application_column_name VARCHAR2(240);
129 p_application_id VARCHAR2(30);
130 BEGIN
131
132 FND_FLEX_DSC_API.SET_SESSION_MODE('customer_data');
133
134 /* vivek
135
136 FND_PROFILE.PUT('RESP_APPL_ID','0');
137 FND_PROFILE.PUT('RESP_ID','20419');
138 FND_PROFILE.PUT('USER_ID','1001');
139 -- Delete all the segments under the New Global Data Elements Context(if any)
140
141 --dbms_output.put_line ('Before even starting the process');
142 IF ( FND_FLEX_DSC_API.FLEXFIELD_EXISTS( P_NEW_PRODUCT,
143 P_NEW_FLEXFIELD_NAME )) THEN
144 --dbms_output.put_line ('Entered the Processing');
145 IF (P_NEW_FLEXFIELD_NAME = G_QP_ATTR_DEFNS_PRICING) THEN
146 -- Get the New Global Data Elements Context and Its Segments
147 FND_DFLEX.GET_FLEXFIELD( P_NEW_PRODUCT
148 , P_NEW_FLEXFIELD_NAME
149 , P_FLEXFIELD
150 , P_FLEXINFO );
151
152 -- Get all contexts for the flexfield
153 FND_DFLEX.GET_CONTEXTS( P_FLEXFIELD, L_CONTEXTS );
154
155 -- Get the Context Code for New Global Data Elements Context (if any)
156 FOR I IN 1..L_CONTEXTS.NCONTEXTS LOOP
157 --dbms_output.put_line ('Found the Old GDE Context');
158 IF (L_CONTEXTS.CONTEXT_CODE(I) = OLD_GDE_CONTEXT_CODE) THEN
159 FND_DFLEX.GET_SEGMENTS ( FND_DFLEX.MAKE_CONTEXT( P_FLEXFIELD , OLD_GDE_CONTEXT_CODE)
160 ,NEW_GDE_SEGMENTS
161 , FALSE ) ;
162 END IF;
163 EXIT;
164 END LOOP;
165
166 IF (NEW_GDE_SEGMENTS.NSEGMENTS > 0) THEN
167 --dbms_output.put_line('New GDE has segments');
168 FOR I IN 1..NEW_GDE_SEGMENTS.NSEGMENTS LOOP
169 --dbms_output.put_line('Trying to delete segments under old context');
170 FND_FLEX_DSC_API.DELETE_SEGMENT( P_NEW_PRODUCT
171 ,P_NEW_FLEXFIELD_NAME
172 ,OLD_GDE_CONTEXT_CODE -- Global Data Elements
173 ,NEW_GDE_SEGMENTS.SEGMENT_NAME(I));
174 END LOOP;
175 ELSE
176 NULL;
177 --dbms_output.put_line('New GDE has no segments');
178 END IF; -- NEW_GDE_SEGMENTS.NSEGMENTS > 0
179 END IF;
180 END IF;
181 vivek */
182 --dbms_output.put_line('Starting the actual Migration');
183 -- Now start the migration of contexts and segments
184 FND_DFLEX.GET_FLEXFIELD(
185 P_PRODUCT
186 , P_FLEXFIELD_NAME
187 , P_FLEXFIELD
188 , P_FLEXINFO );
189
190 FND_DFLEX.GET_CONTEXTS( P_FLEXFIELD, L_CONTEXTS );
191
192 -- Store all the old contexts
193 GDE_CONTEXTS := L_CONTEXTS;
194
195 -- Check To See If New Flex Structure Exists
196 IF ( FND_FLEX_DSC_API.FLEXFIELD_EXISTS( P_NEW_PRODUCT,
197 P_NEW_FLEXFIELD_NAME )) THEN
198 FOR I IN 1..L_CONTEXTS.NCONTEXTS LOOP
199 --dbms_output.put_line ( ' Global Code : ' || L_CONTEXTS.CONTEXT_CODE(I));
200 IF (L_CONTEXTS.CONTEXT_CODE(I) = OLD_GDE_CONTEXT_CODE AND P_NEW_FLEXFIELD_NAME = G_QP_ATTR_DEFNS_PRICING) THEN
201 --dbms_output.put_line('There are contexts for migration');
202 /* vivek IF (check_context_existance(QP_APPLICATION_ID,P_NEW_FLEXFIELD_NAME,NEW_GDE_CONTEXT_CODE) = FALSE) THEN
203 --dbms_output.put_line ('Creating the Upgrade Context');
204 FND_FLEX_DSC_API.CREATE_CONTEXT ( P_NEW_PRODUCT
205 , P_NEW_FLEXFIELD_NAME
206 , NEW_GDE_CONTEXT_CODE
207 , NEW_GDE_CONTEXT_CODE
208 , NEW_GDE_CONTEXT_CODE
209 , 'Y'
210 , 'N') ;
211 --dbms_output.put_line ('Created the Upgrade Context');
212 ELSE
213 NULL;
214 --dbms_output.put_line ('Upgrade Context Already Exists');
215 END IF;
216 FND_FLEX_DSC_API.ENABLE_CONTEXT (P_NEW_PRODUCT
217 , P_NEW_FLEXFIELD_NAME
218 , NEW_GDE_CONTEXT_CODE
219 , TRUE );
220
221 FND_FLEX_DSC_API.ENABLE_COLUMNS( P_NEW_PRODUCT
222 , P_NEW_FLEXFIELD_NAME
223 , 'ATTRIBUTE[0-9]+');
224
225 vivek */
226 FND_DFLEX.GET_SEGMENTS ( FND_DFLEX.MAKE_CONTEXT( P_FLEXFIELD , L_CONTEXTS.CONTEXT_CODE(I))
227 ,L_SEGMENTS
228 , FALSE ) ;
229
230
231 -- Store all the old global data elements' segments
232 GDE_SEGMENTS := L_SEGMENTS;
233
234 --dbms_output.put_line ( 'Old GDE Segments Count##: ' || nvl(GDE_SEGMENTS.NSEGMENTS,0));
235
236 FOR J IN 1..L_SEGMENTS.NSEGMENTS LOOP
237 /* vivek L_VALUE_SET_ID := L_SEGMENTS.VALUE_SET(J);
238 BEGIN
239 IF L_VALUE_SET_ID <> 0 THEN
240 SELECT FLEX_VALUE_SET_NAME INTO
241 L_VALUE_SET
242 FROM FND_FLEX_VALUE_SETS
243 WHERE FLEX_VALUE_SET_ID = L_VALUE_SET_ID;
244 ELSE
245 L_VALUE_SET := NULL;
246 END IF;
247 EXCEPTION
248 WHEN NO_DATA_FOUND THEN
249 L_VALUE_SET := NULL;
250 WHEN TOO_MANY_ROWS THEN
251 NULL;
252 END;
253 vivek */
254 IF (L_SEGMENTS.IS_REQUIRED(J) ) THEN
255 L_REQUIRED := 'Y';
256 ELSE
257 L_REQUIRED := 'N';
258 END IF;
259
260 IF (L_SEGMENTS.IS_ENABLED(J) ) THEN
261 L_ENABLED := 'Y';
262 ELSE
263 L_ENABLED := 'N';
264 END IF;
265
266 IF (L_SEGMENTS.IS_DISPLAYED(J) ) THEN
267 L_DISPLAYED := 'Y';
268 ELSE
269 L_DISPLAYED := 'N';
270 END IF;
271
272 IF (check_segment_existance(QP_APPLICATION_ID,
273 NEW_GDE_CONTEXT_CODE,
274 P_NEW_FLEXFIELD_NAME,
275 L_SEGMENTS.APPLICATION_COLUMN_NAME(J)) = TRUE ) THEN
276 --dbms_output.put_line ('First if');
277 IF (check_segment_name_existance(QP_APPLICATION_ID,
278 NEW_GDE_CONTEXT_CODE,
279 P_NEW_FLEXFIELD_NAME,
280 L_SEGMENTS.SEGMENT_NAME(J)) = TRUE ) THEN
281 --dbms_output.put_line ('Second if');
282 p_segment_name := L_SEGMENTS.SEGMENT_NAME(J);
283 /* ELSE
284 --dbms_output.put_line ('Second else');
285 p_segment_name := 'QP: ' || L_SEGMENTS.SEGMENT_NAME(J); -- Create new name
286 END IF;
287 */
288 -- Storing the values for error handling
289 p_context_name := NEW_GDE_CONTEXT_CODE;
290 p_application_column_name := L_SEGMENTS.APPLICATION_COLUMN_NAME(J);
291 p_application_id := QP_APPLICATION_ID;
292
293 --dbms_output.put_line ('Creating the Upgrade Context Segments');
294 BEGIN
295 FND_FLEX_DSC_API.MODIFY_SEGMENT (
296 P_APPL_SHORT_NAME => P_NEW_PRODUCT
297 , P_FLEXFIELD_NAME => P_NEW_FLEXFIELD_NAME
298 , P_CONTEXT_CODE => NEW_GDE_CONTEXT_CODE
299 , P_SEGMENT_NAME => p_segment_name
300 -- , P_COLUMN_NAME => L_SEGMENTS.APPLICATION_COLUMN_NAME(J)
301 , P_DEFAULT_TYPE => nvl(L_SEGMENTS.DEFAULT_TYPE(J),FND_API.G_MISS_CHAR)
302 , P_DEFAULT_VALUE => L_SEGMENTS.DEFAULT_VALUE(J)
303 , P_REQUIRED => L_REQUIRED
304 , P_SEQUENCE_NUMBER => L_SEGMENTS.SEQUENCE(J)
305 , P_ENABLED => L_ENABLED
306 , P_DISPLAYED => L_DISPLAYED);
307 EXCEPTION
308 WHEN NO_DATA_FOUND THEN
309 rollback;
310 Log_Error(p_id1 => -9999,
311 p_error_type => 'ERROR IN UPDATING SEGMENT',
312 p_error_desc => ' Application Id : ' || p_application_id ||
313 ' Old Flexfield Name : ' || P_FLEXFIELD_NAME ||
314 ' New Flexfield Name : ' || P_NEW_FLEXFIELD_NAME ||
315 ' Context Name : ' || p_context_name ||
316 ' Application Column Name : ' || p_application_column_name ||
317 ' Application Segment Name : ' || p_segment_name ,
318 p_error_module => 'QP_Update_Upgrade');
319 raise;
320 END;
321 END IF;
322 END IF; -- vivek
323 END LOOP; -- L_SEGMENTS
324 --EXIT;
325 END IF; -- Global Data Elements
326 END LOOP; -- L_CONTEXTS
327
328 --dbms_output.put_line('Total Context Count: ' || L_CONTEXTS.NCONTEXTS);
329 -- Process other contexts(other than Global Data Elements)
330 FOR I IN 1..L_CONTEXTS.NCONTEXTS LOOP
331 IF ((L_CONTEXTS.CONTEXT_CODE(I) <> OLD_GDE_CONTEXT_CODE AND P_NEW_FLEXFIELD_NAME = G_QP_ATTR_DEFNS_PRICING)
332 OR (P_NEW_FLEXFIELD_NAME <> G_QP_ATTR_DEFNS_PRICING)) THEN
333 --dbms_output.put_line ('Before Other Context Existance Check');
334 --dbms_output.put_line ('Context Code : ' || L_CONTEXTS.CONTEXT_CODE(I));
335 /* vivek IF (check_context_existance(QP_APPLICATION_ID,P_NEW_FLEXFIELD_NAME,L_CONTEXTS.CONTEXT_CODE(I)) = FALSE) THEN
336 --dbms_output.put_line ('Creating Other Contexts');
337 FND_FLEX_DSC_API.CREATE_CONTEXT ( P_NEW_PRODUCT
338 , P_NEW_FLEXFIELD_NAME
339 , L_CONTEXTS.CONTEXT_CODE(I)
340 , L_CONTEXTS.CONTEXT_NAME(I)
341 , L_CONTEXTS.CONTEXT_DESCRIPTION(I)
342 , 'Y'
343 , 'N') ;
344
345 END IF;
346 FND_FLEX_DSC_API.ENABLE_CONTEXT ( P_NEW_PRODUCT
347 , P_NEW_FLEXFIELD_NAME
348 , L_CONTEXTS.CONTEXT_NAME(I)
349 , TRUE );
350
351 FND_FLEX_DSC_API.ENABLE_COLUMNS(P_NEW_PRODUCT
352 , P_NEW_FLEXFIELD_NAME
353 , 'ATTRIBUTE[0-9]+');
354 vivek */
355
356 FND_DFLEX.GET_SEGMENTS ( FND_DFLEX.MAKE_CONTEXT( P_FLEXFIELD , L_CONTEXTS.CONTEXT_CODE(I))
357 ,L_SEGMENTS
358 , FALSE ) ;
359
360 L_SEGMENT_COUNT := L_SEGMENTS.NSEGMENTS;
361 --dbms_output.put_line ('Other Context Segment Count : ' || L_SEGMENT_COUNT);
362
363 FOR J IN 1..L_SEGMENTS.NSEGMENTS LOOP
364 /* vivek L_VALUE_SET_ID := L_SEGMENTS.VALUE_SET(J);
365 BEGIN
366 IF L_VALUE_SET_ID <> 0 THEN
367 SELECT FLEX_VALUE_SET_NAME INTO
368 L_VALUE_SET
369 FROM FND_FLEX_VALUE_SETS
370 WHERE FLEX_VALUE_SET_ID = L_VALUE_SET_ID;
371 ELSE
372 L_VALUE_SET := NULL;
373 END IF;
374 EXCEPTION
375 WHEN NO_DATA_FOUND THEN
376 L_VALUE_SET := NULL;
377 WHEN TOO_MANY_ROWS THEN
378 NULL;
379 END;
380 vivek */
381
382 IF (L_SEGMENTS.IS_REQUIRED(J) ) THEN
383 L_REQUIRED := 'Y';
384 ELSE
385 L_REQUIRED := 'N';
386 END IF;
387
388 IF (L_SEGMENTS.IS_ENABLED(J) ) THEN
389 L_ENABLED := 'Y';
390 ELSE
391 L_ENABLED := 'N';
392 END IF;
393
394 IF (L_SEGMENTS.IS_DISPLAYED(J) ) THEN
395 L_DISPLAYED := 'Y';
396 ELSE
397 L_DISPLAYED := 'N';
398 END IF;
399
400
401 IF (check_segment_existance(QP_APPLICATION_ID,
402 L_CONTEXTS.CONTEXT_CODE(I),
403 P_NEW_FLEXFIELD_NAME,
404 L_SEGMENTS.APPLICATION_COLUMN_NAME(J)) = TRUE ) THEN
405 --dbms_output.put_line ('Segment check false');
406 IF (check_segment_name_existance(QP_APPLICATION_ID,
407 -- NEW_GDE_CONTEXT_CODE,
408 L_CONTEXTS.CONTEXT_CODE(I),
409 P_NEW_FLEXFIELD_NAME,
410 L_SEGMENTS.SEGMENT_NAME(J)) = TRUE ) THEN
411 --dbms_output.put_line ('Segment name check false');
412 p_segment_name := L_SEGMENTS.SEGMENT_NAME(J);
413 /* ELSE
414 p_segment_name := 'QP: ' || L_SEGMENTS.SEGMENT_NAME(J);
415 END IF;
416 */
417 -- Storing the values for error handling
418 p_context_name := L_CONTEXTS.CONTEXT_CODE(I);
419 p_application_column_name := L_SEGMENTS.APPLICATION_COLUMN_NAME(J);
420 p_application_id := QP_APPLICATION_ID;
421
422 --dbms_output.put_line ('prod = '||P_NEW_PRODUCT);
423 --dbms_output.put_line ('Flexfield Name : ' || P_NEW_FLEXFIELD_NAME);
424 --dbms_output.put_line ('Context Code : ' || L_CONTEXTS.CONTEXT_CODE(I));
425 --dbms_output.put_line ('p_seg_name = '||p_segment_name);
426 --dbms_output.put_line ('Default type : ' || L_SEGMENTS.DEFAULT_TYPE(J));
427
428 --dbms_output.put_line ('Creating Other Contexts Segments ');
429 BEGIN
430 FND_FLEX_DSC_API.MODIFY_SEGMENT (
431 P_APPL_SHORT_NAME => P_NEW_PRODUCT
432 , P_FLEXFIELD_NAME => P_NEW_FLEXFIELD_NAME
433 , P_CONTEXT_CODE => L_CONTEXTS.CONTEXT_CODE(I)
437 , P_DEFAULT_VALUE => L_SEGMENTS.DEFAULT_VALUE(J)
434 , P_SEGMENT_NAME => p_segment_name
435 -- , P_COLUMN_NAME => L_SEGMENTS.APPLICATION_COLUMN_NAME(J)
436 , P_DEFAULT_TYPE => nvl(L_SEGMENTS.DEFAULT_TYPE(J),FND_API.G_MISS_CHAR)
438 , P_REQUIRED => L_REQUIRED
439 , P_SEQUENCE_NUMBER => L_SEGMENTS.SEQUENCE(J)
440 , P_ENABLED => L_ENABLED
441 , P_DISPLAYED => L_DISPLAYED);
442 EXCEPTION
443 WHEN NO_DATA_FOUND THEN
444 rollback;
445 Log_Error(p_id1 => -9999,
446 p_error_type => 'ERROR IN UPDATE SEGMENT',
447 p_error_desc => ' Application Id : ' || p_application_id ||
448 ' Old Flexfield Name : ' || P_FLEXFIELD_NAME ||
449 ' New Flexfield Name : ' || P_NEW_FLEXFIELD_NAME ||
450 ' Context Name : ' || p_context_name ||
451 ' Application Column Name : ' || p_application_column_name ||
452 ' Application Segment Name : ' || p_segment_name ,
453 p_error_module => 'QP_Update_Upgrade');
454 raise;
455 END ;
456 END IF;
457 END IF; -- vivek
458 END LOOP; -- L_SEGMENTS
459
460 -- Append all the global data segments into other contexts
461 --dbms_output.put_line ('Old GDE SEGMENTS Count : ' || nvl(GDE_SEGMENTS.NSEGMENTS,0));
462 IF (nvl(GDE_SEGMENTS.NSEGMENTS,0) > 0) THEN
463 FOR K IN 1..GDE_SEGMENTS.NSEGMENTS LOOP
464 /* vivek L_VALUE_SET_ID := GDE_SEGMENTS.VALUE_SET(K);
465 BEGIN
466 IF L_VALUE_SET_ID <> 0 THEN
467 SELECT FLEX_VALUE_SET_NAME INTO
468 L_VALUE_SET
469 FROM FND_FLEX_VALUE_SETS
470 WHERE FLEX_VALUE_SET_ID = L_VALUE_SET_ID;
471 ELSE
472 L_VALUE_SET := NULL;
473 END IF;
474 EXCEPTION
475 WHEN NO_DATA_FOUND THEN
476 L_VALUE_SET := NULL;
477 WHEN TOO_MANY_ROWS THEN
478 NULL;
479 END;
480 vivek */
481
482 IF (GDE_SEGMENTS.IS_REQUIRED(K) ) THEN
483 L_REQUIRED := 'Y';
484 ELSE
485 L_REQUIRED := 'N';
486 END IF;
487
488 IF (GDE_SEGMENTS.IS_ENABLED(K) ) THEN
489 L_ENABLED := 'Y';
490 ELSE
491 L_ENABLED := 'N';
492 END IF;
493
494 IF (GDE_SEGMENTS.IS_DISPLAYED(K) ) THEN
495 L_DISPLAYED := 'Y';
496 ELSE
497 L_DISPLAYED := 'N';
498 END IF;
499
500 /*
501 --dbms_output.put_line ('Before Segment Existance Check for Old Gde Segments');
502 --dbms_output.put_line ('Flexfield Name : ' || P_NEW_FLEXFIELD_NAME);
503 --dbms_output.put_line ('Context Code : ' || L_CONTEXTS.CONTEXT_CODE(I));
504 --dbms_output.put_line ('Application Column Name : ' || GDE_SEGMENTS.APPLICATION_COLUMN_NAME(K)); */
505 IF (check_segment_existance(QP_APPLICATION_ID,
506 L_CONTEXTS.CONTEXT_CODE(I),
507 P_NEW_FLEXFIELD_NAME,
508 GDE_SEGMENTS.APPLICATION_COLUMN_NAME(K)) = TRUE ) THEN
509 --dbms_output.put_line ('Segment check false');
510 IF (check_segment_name_existance(QP_APPLICATION_ID,
511 L_CONTEXTS.CONTEXT_CODE(I),
512 P_NEW_FLEXFIELD_NAME,
513 GDE_SEGMENTS.SEGMENT_NAME(K)) = TRUE ) THEN
514 --dbms_output.put_line ('Segment name check false');
515 p_segment_name := GDE_SEGMENTS.SEGMENT_NAME(K);
516 /* ELSE
517 p_segment_name := 'QP: ' || GDE_SEGMENTS.SEGMENT_NAME(K);
518 END IF;
519 */
520 -- Storing the values for error handling
521 p_context_name := L_CONTEXTS.CONTEXT_CODE(I);
522 p_application_column_name := GDE_SEGMENTS.APPLICATION_COLUMN_NAME(K);
523 p_application_id := QP_APPLICATION_ID;
524
525 --dbms_output.put_line ('Creating the OLD Gde segments to all contexts');
526 BEGIN
527 FND_FLEX_DSC_API.MODIFY_SEGMENT (
528 P_APPL_SHORT_NAME => P_NEW_PRODUCT
529 , P_FLEXFIELD_NAME => P_NEW_FLEXFIELD_NAME
530 , P_CONTEXT_CODE => L_CONTEXTS.CONTEXT_CODE(I)
531 , P_SEGMENT_NAME => p_segment_name
532 -- , P_COLUMN_NAME => GDE_SEGMENTS.APPLICATION_COLUMN_NAME(K)
533 , P_DEFAULT_TYPE => nvl(GDE_SEGMENTS.DEFAULT_TYPE(K),FND_API.G_MISS_CHAR)
534 , P_DEFAULT_VALUE => GDE_SEGMENTS.DEFAULT_VALUE(K)
535 , P_REQUIRED => L_REQUIRED
536 , P_SEQUENCE_NUMBER => GDE_SEGMENTS.SEQUENCE(K)
537 , P_ENABLED => L_ENABLED
538 , P_DISPLAYED => L_DISPLAYED);
539 EXCEPTION
540 WHEN NO_DATA_FOUND THEN
541 rollback;
542 Log_Error(p_id1 => -9999,
543 p_error_type => 'ERROR IN UPDATE SEGMENT',
544 p_error_desc => ' Application Id : ' || p_application_id ||
545 ' Old Flexfield Name : ' || P_FLEXFIELD_NAME ||
546 ' New Flexfield Name : ' || P_NEW_FLEXFIELD_NAME ||
547 ' Context Name : ' || p_context_name ||
548 ' Application Column Name : ' || p_application_column_name ||
549 ' Application Segment Name : ' || p_segment_name ,
550 p_error_module => 'QP_Update_Upgrade');
551 raise;
552 END ;
553 END IF;
554 END IF; -- vivek
555 END LOOP; -- GDE_SEGMENTS
556 END IF; -- GDE_SEGMENTS.NSEGMENTS > 0
557 END IF; -- Global Data Elements
558 END LOOP; -- CONTEXTS
559 END IF; /* CHECK FOR NEW FLEX FIELD STRUCTURE EXISTS */
560 EXCEPTION
561 WHEN OTHERS THEN
562 --dbms_output.put_line(fnd_flex_dsc_api.message);
563 rollback;
564 Log_Error(p_id1 => -6501,
565 p_error_type => 'FLEXFIELD UPGRADE',
566 p_error_desc => fnd_flex_dsc_api.message,
567 p_error_module => 'QP_Update_Upgrade');
568 raise;
569 END QP_UPDATE_UPGRADE;
570
571 PROCEDURE LOG_ERROR( P_ID1 VARCHAR2,
572 P_ID2 VARCHAR2 :=NULL,
573 P_ID3 VARCHAR2 :=NULL,
574 P_ID4 VARCHAR2 :=NULL,
575 P_ID5 VARCHAR2 :=NULL,
576 P_ID6 VARCHAR2 :=NULL,
577 P_ID7 VARCHAR2 :=NULL,
578 P_ID8 VARCHAR2 :=NULL,
579 P_ERROR_TYPE VARCHAR2,
580 P_ERROR_DESC VARCHAR2,
581 P_ERROR_MODULE VARCHAR2) AS
582
583 PRAGMA AUTONOMOUS_TRANSACTION;
584
585 BEGIN
586
587 INSERT INTO QP_UPGRADE_ERRORS(ERROR_ID,UPG_SESSION_ID,ID1,ID2,ID3,ID4,ID5,ID6,ID7,ID8,ERROR_TYPE,
588 ERROR_DESC,ERROR_MODULE,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,
589 LAST_UPDATED_BY,LAST_UPDATE_LOGIN) VALUES
590 (QP_UPGRADE_ERRORS_S.NEXTVAL,USERENV('SESSIONID'),
591 P_ID1,P_ID2,P_ID3,P_ID4,P_ID5,P_ID6,P_ID7,P_ID8,
592 P_ERROR_TYPE, SUBSTR(P_ERROR_DESC,1,240),P_ERROR_MODULE,SYSDATE,
593 FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,FND_GLOBAL.LOGIN_ID);
594 COMMIT;
595
596 END;
597
598
599 END QP_UPGRADE_UTIL;