1 PACKAGE BODY PV_PARTNER_MIGRATIONS_PUB
2 /* $Header: pvxpmigb.pls 120.9 2006/09/01 12:40:38 rdsharma noship $ */
3 as
4
5
6 /*============================================================================
7 -- Start of comments
8 -- API name : Update_Primary_Partner
9 -- Type : Private.
10 -- Function : This API is a private api used to update the partner types of
11 -- of a partner. This api would make one of the partner type as primary
12 --
13 -- Pre-reqs : None.
14 -- Parameters :
15 -- IN : p_entity_id IN integer,
16 -- p_primary_partner_type IN VARCHAR2,
17 --
18 -- OUT : x_return_status OUT VARCHAR2(1)
19 -- x_msg_count OUT NUMBER
20 -- x_msg_data OUT VARCHAR2(2000)
21 --
22 -- Version : Current version 1.0
23 -- Initial version 1.0
24 --
25 -- Notes : Note text
26 --
27 -- History :
28 -- Created 2005/12/26 pinagara
29 -- Modified 2006/08/30 rdsharma Fix the bug# 5486739.
30 -- End of comments
31 ============================================================================*/
32
33 PROCEDURE Update_Primary_Partner(
34 p_entity_id IN integer,
35 p_primary_partner_type IN VARCHAR2,
36 x_return_status OUT NOCOPY VARCHAR2,
37 x_msg_count OUT NOCOPY NUMBER,
38 x_msg_data OUT NOCOPY VARCHAR
39 )
40 IS
41
42 l_entity_id integer;
43 l_party_id integer;
44 l_version integer;
45 l_attr_value varchar2(2000);
46 l_attr_value_extn varchar2(4000);
47 l_count integer;
48 l_attr_val_tbl PV_ENTY_ATTR_VALUE_PUB.attr_value_tbl_type;
49 l_partner_name varchar2(2000);
50 l_primary_partner_type varchar2(2000);
51 l_addtl_partner_type varchar2(2000);
52
53 -- Local variable declaration for Standard Out variables.
54 l_return_status VARCHAR2(1);
55 l_msg_count NUMBER;
56 l_msg_data VARCHAR2(2000);
57
58
59 CURSOR GET_PARTNER_TYPES IS
60 SELECT
61 entity_id,
62 version,
63 attr_value,
64 attr_value_extn
65 from
66 pv_enty_attr_values
67 where
68 entity_id = p_entity_id and
69 latest_flag = 'Y' AND
70 attribute_id = 3;
71
72
73 CURSOR GET_PARTY_NAME(CV_PARTNER_ID INTEGER) IS
74 SELECT
75 PARTY_NAME, PARTY_ID
76 FROM
77 PV_PARTNER_PROFILES,
78 HZ_PARTIES
79 WHERE
80 HZ_PARTIES.PARTY_ID = PV_PARTNER_PROFILES.PARTNER_PARTY_ID AND
81 PV_PARTNER_PROFILES.PARTNER_ID = CV_PARTNER_ID;
82
83
84 CURSOR GET_PARTNER_TYPE_DTLS(CV_PARTNER_ID INTEGER) is
85 select ATTR_VALUE from pv_enty_attr_values tabl where entity_id = CV_PARTNER_ID and ATTRIBUTE_id= 3
86 and version = (SELECT
87 MAX(case (version -1)
88 when 0 then
89 1
90 else
91 (version -1)
92 end ) fROM pv_enty_attr_values
93 WHERE
94 entity_id = CV_PARTNER_ID AND
95 ATTRIBUTE_ID = 3 AND
96 LAST_UPDATE_DATE = (SELECT MIN(LAST_UPDATE_DATE) FROM pv_enty_attr_values WHERE ATTR_VALUE_EXTN = 'Y' AND
97 ENTITY_ID = tabl.entity_id and attribute_id = 3)
98 )
99 union
100
101 SELECT ATTR_VALUE FROM pv_enty_attr_values
102 WHERE
103 entity_id = CV_PARTNER_ID and
104 ATTRIBUTE_ID = 3 AND
105 ENTITY_ID not IN (SELECT ENTITY_ID FROM pv_enty_attr_values WHERE entity_id = CV_PARTNER_ID and ATTRIBUTE_ID = 3 and ATTR_VALUE_eXTN is not NULL) AND
106 LATEST_FLAG = 'Y';
107
108 begin
109
110 l_addtl_partner_type := null;
111 l_count := 1;
112
113 open GET_PARTNER_TYPE_DTLS(CV_PARTNER_ID => p_entity_id);
114 fetch GET_PARTNER_TYPE_DTLS into l_attr_value;
115 while GET_PARTNER_TYPE_DTLS%found
116 loop
117 -- if l_attr_value <> p_primary_partner_type then
118 if length(l_addtl_partner_type) > 0 then
119 l_addtl_partner_type := l_addtl_partner_type || ',' || l_attr_value;
120 else
121 l_addtl_partner_type := l_attr_value;
122 end if;
123 -- end if;
124 fetch GET_PARTNER_TYPE_DTLS into l_attr_value;
125 end loop;
126 close GET_PARTNER_TYPE_DTLS;
127
128
129 OPEN GET_PARTNER_TYPES;
130 Fetch GET_PARTNER_TYPES INTO
131 l_entity_id,
132 l_version,
133 l_attr_value,
134 l_attr_value_extn;
135
136 if GET_PARTNER_TYPES%found then
137 l_attr_val_tbl(l_count).attr_value := p_primary_partner_type;
138 l_attr_val_tbl(l_count).attr_value_extn := 'Y';
139 end if;
140 close GET_PARTNER_TYPES;
141
142 -- dbms_output.put_line ('entity:' || l_entity_id || ', Type :' || p_primary_partner_type);
143
144 -- Get the Partner name for the supplied PARTNER_ID.
145 for x in GET_PARTY_NAME(CV_PARTNER_ID => l_entity_id)
146 loop
147 l_partner_name := x.party_name;
148 l_party_id := x.party_id;
149 end loop;
150
151 IF l_partner_name IS NULL then
152 l_partner_name := 'N/A';
153 end if;
154
155 PV_ENTY_ATTR_VALUE_PUB.Upsert_Attr_Value (
156 p_api_version_number=> 1.0
157 ,p_init_msg_list => FND_API.g_false
158 ,p_commit => FND_API.g_false
159 ,p_validation_level => FND_API.g_valid_level_full
160 ,x_return_status => l_return_status
161 ,x_msg_count => l_msg_count
162 ,x_msg_data => l_msg_data
163 ,p_attribute_id => 3
164 ,p_entity => 'PARTNER'
165 ,p_entity_id => l_entity_id
166 ,p_version => l_version
167 ,p_attr_val_tbl => l_attr_val_tbl
168 );
169
170 FND_FILE.PUT_LINE(FND_FILE.LOG, '' );
171 FND_FILE.PUT_LINE(FND_FILE.LOG,' ' || RPAD(l_partner_name||'('||l_entity_id||')',45) || RPAD(p_primary_partner_type,42) || l_addtl_partner_type );
172
173 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
174 IF ( l_msg_count > 1 ) THEN
175 FOR l_msg_index IN 1..l_msg_count LOOP
176 apps.fnd_message.set_encoded(apps.fnd_msg_pub.get(l_msg_index));
177 FND_FILE.PUT_LINE(FND_FILE.LOG,' ' || substr(apps.fnd_message.get,1,254) );
178 END LOOP;
179 ELSE
180 -- Standard call to get message count and if count=1, get the message
181 FND_MSG_PUB.Count_And_Get (
182 p_encoded => FND_API.G_FALSE
183 ,p_count => l_msg_count
184 ,p_data => l_msg_data
185 );
186 FND_FILE.PUT_LINE(FND_FILE.LOG,' ' || l_msg_data );
187 END IF;
188 END IF;
189
190 END Update_Primary_Partner;
191
192 /*============================================================================
193 -- Start of comments
194 -- API name : Convert_Partner_Type
195 -- Type : Public.
196 -- Function : This api is used to migrate the partners from multiple partner
197 -- types to single partners with primary partner type
198 --
199 -- Pre-reqs : None.
200 -- Parameters :
201 -- IN : p_running_mode IN integer,
202 -- p_overwrite IN VARCHAR2,
203 --
204 -- OUT : Errbuf OUT VARCHAR2
205 -- retcode OUT VARCHAR2
206 --
207 -- Version : Current version 1.0
208 -- Initial version 1.0
209 --
210 -- Notes : Note text
211 --
212 -- End of comments
213 ============================================================================*/
214 PROCEDURE Convert_Partner_Type
215 (
216 Errbuf OUT NOCOPY VARCHAR2,
217 Retcode OUT NOCOPY VARCHAR2,
218 p_Running_Mode IN varchar2 DEFAULT 'EVALUATION',
219 p_OverWrite IN varchar2 DEFAULT 'N'
220 ) is
221
222 l_count integer;
223 l_distinct_count integer;
224 l_attr_value varchar2(2000);
225 l_processed_count integer;
226 l_unprocessed_count integer;
227 l_entity_id integer;
228
229 -- Local variable declaration for Standard Out variables.
230 l_return_status VARCHAR2(1);
231 l_msg_count NUMBER;
232 l_msg_data VARCHAR2(2000);
233
234 -- Cursor to get all the configured partner types under
235 -- PV_PARTNER_TYPE_RANKING lookup type.
236 CURSOR Get_Ranked_Partner_types is
237 select
238 lookup_code,
239 tag
240 from
241 fnd_lookup_values
242 where
243 lookup_type = 'PV_PARTNER_TYPE_RANKING' and
244 enabled_flag = 'Y' AND
245 language = userenv('LANG') and
246 TAG IS NOT NULL
247 order by tag asc ;
248
249 -- Cursor to get the count for all unprocessed partners.
250 CURSOR Get_Unprocessed_Record_Count IS
251 select
252 count(distinct entity_id) as partner_count
253 from
254 pv_enty_attr_values
255 where
256 attribute_id = 3 and
257 attr_value_extn = 'Y';
258
259 -- Cursor to get the count of all partners with each Partner type.
260 CURSOR Get_Primary_Partner_Type_count is
261 select
262 attr_value , count(attr_value) as partner_count
263 from
264 pv_enty_attr_values
265 where
266 attribute_id = 3 and latest_flag = 'Y' and attr_value_extn = 'Y'
267 group by
268 attr_value
269 having
270 count(attr_value) > 0
271 order by
272 count(attr_value) desc;
273
274 -- Cursor to get the count for all 'Active' and 'Inactive' Partners.
275 CURSOR Get_Partner_Status_Count is
276 SELECT
277 (select count(status) from pv_partner_profiles where status = 'A') Active,
278 (select count(status) from pv_partner_profiles where status = 'I') Inactive
279
280 FROM DUAL;
281
282 -- Cursor to get all partners with VAD partner types.
283 CURSOR Get_VAD_Prtnr_Typ_Migrn_OvWrt is
284 SELECT
285 ATTR_VALUE,
286 entity_id
287 FROM (
288 SELECT
289 ATTR_VALUE,
290 entity_id
291 FROM pv_enty_attr_values tabl
292 WHERE
293 ATTRIBUTE_ID = 3
294 AND ATTR_VALUE = 'VAD'
295 AND VERSION = (SELECT
296 MAX(case (version -1)
297 when 0 then
298 1
299 else
300 (version -1)
301 end )
302 FROM pv_enty_attr_values
303 WHERE
304 entity_id = tabl.entity_id AND
305 LAST_UPDATE_DATE = ( SELECT MIN(LAST_UPDATE_DATE)
306 FROM pv_enty_attr_values
307 WHERE ATTR_VALUE_EXTN = 'Y'
308 AND ENTITY_ID = tabl.entity_id
309 AND attribute_id = 3
310 )
311 )
312 UNION
313 SELECT
314 ATTR_VALUE,
315 entity_id
316 FROM pv_enty_attr_values
317 WHERE
318 ATTRIBUTE_ID = 3
319 AND LATEST_FLAG = 'Y'
320 AND ATTR_VALUE = 'VAD'
321 AND entity_id in ( SELECT entity_id
322 FROM pv_enty_attr_values
323 WHERE ATTRIBUTE_ID = 3
324 AND attr_value = 'VAD'
325 AND latest_flag = 'Y'
326 )
327 AND ENTITY_ID NOT IN (SELECT ENTITY_ID
328 FROM pv_enty_attr_values
329 WHERE ATTRIBUTE_ID = 3
330 AND attr_value_extn is not null
331 )
332 )
333 partners,
334 pv_partner_profiles profiles
335 WHERE partners.entity_id = profiles.partner_id
336 AND profiles.status in ('A', 'I');
337
338 -- Cursor to get all partners with other partner types.
339 CURSOR Get_Prtnr_Typ_Migrn_Overwrite IS
340 select distinct
341 details.attr_value,
342 details.entity_id
343 from
344 (
345 SELECT
346 min(tag) as tag,
347 entity_id
348 FROM
349 (
350 SELECT ATTR_VALUE,entity_id
351 FROM pv_enty_attr_values tabl
352 WHERE
353 ENTITY_ID IN (SELECT ENTITY_ID FROM pv_enty_attr_values WHERE ATTRIBUTE_ID = 3 AND ATTR_VALUE_EXTN = 'Y') AND
354 ATTRIBUTE_ID = 3 AND
355 VERSION = (SELECT
356 MAX(case (version -1)
357 when 0 then
358 1
359 else
360 (version -1)
361 end )
362 FROM pv_enty_attr_values
363 WHERE
364 entity_id = tabl.entity_id and
365 LAST_UPDATE_DATE = (SELECT MIN(LAST_UPDATE_DATE)
366 FROM pv_enty_attr_values
367 WHERE ATTR_VALUE_EXTN = 'Y'
368 AND ENTITY_ID = tabl.entity_id
369 AND attribute_id = 3)
370 )
371
372 UNION
373
374 SELECT ATTR_VALUE,entity_id
375 FROM pv_enty_attr_values
376 WHERE
377 ATTRIBUTE_ID = 3 AND
378 ENTITY_ID NOT IN (SELECT ENTITY_ID
379 FROM pv_enty_attr_values
380 WHERE ATTRIBUTE_ID = 3
381 AND attr_value_extn is not null ) AND
382 LATEST_FLAG = 'Y'
383
384
385 ) attr,
386 fnd_lookup_values lkp where
387 ENTITY_ID NOT IN (SELECT ENTITY_ID FROM pv_enty_attr_values WHERE ENTITY_ID = attr.entity_id and ATTR_VALUE = 'VAD') and
388 attr.attr_value = lkp.lookup_code and
389 lkp.language = userenv('LANG') and
390 lkp.lookup_type = 'PV_PARTNER_TYPE_RANKING'
391 group by entity_id
392 ) sorted,
393 pv_enty_attr_values details,
394 fnd_lookup_values match,
395 pv_partner_profiles ppp
396 where
397 details.attr_value = lookup_code and
398 match.lookup_type = 'PV_PARTNER_TYPE_RANKING' and
399 match.language = userenv('LANG') and
400 sorted. entity_id = details.entity_id and
401 details.entity_id = ppp.partner_id and
402 ppp.status IN ('A' , 'I') and
403 to_number(sorted.tag) = to_number(match.tag)
404
405 order by details.entity_id;
406
407
408 CURSOR GET_VAD_PRTNR_TYP_MIGRN IS
409 SELECT distinct
410 attr_value,
411 entity_id
412 FROM
413 pv_enty_attr_values partners,
414 pv_partner_profiles profiles
415 WHERE
416 partners.attribute_id = 3
417 AND partners.latest_flag = 'Y'
418 AND partners.attr_value = 'VAD'
419 AND partners.entity_id not in (
420 SELECT
421 distinct entity_id
422 FROM
423 pv_enty_attr_values
424 WHERE
425 attribute_id = 3
426 AND attr_value_extn is not null
427 AND latest_flag = 'Y'
428 )
429 AND partners.entity_id = profiles.partner_id
430 AND profiles.status in ('A', 'I');
431
432 CURSOR GET_PRTNR_TYP_MIGRN IS
433 SELECT distinct
434 attr_value,
435 entity_id
436 FROM pv_enty_attr_values attr,
437 fnd_lookup_values lkp,
438 pv_partner_profiles ppp
439 WHERE
440 attribute_id = 3
441 AND latest_flag = 'Y'
442 AND entity_id not in (
443 select
444 distinct entity_id
445 from
449 latest_flag = 'Y' AND
446 pv_enty_attr_values
447 where
448 attribute_id = 3 and
450 (attr_value = 'VAD' OR attr_value_extn is not null )
451 )
452 AND attr.attr_value = lkp.lookup_code
453 AND lkp.lookup_type = 'PV_PARTNER_TYPE_RANKING'
454 AND lkp.language = userenv('LANG')
455 AND attr.entity_id = ppp.partner_id
456 AND ppp.status IN ('A' , 'I')
457 AND to_number(tag) = (select min(to_number(tag))
458 from fnd_lookup_values
459 where
460 lookup_code in (select attr_value
461 from pv_enty_attr_values
462 where attribute_id=3
463 and entity_id = attr.entity_id
464 and latest_flag = 'Y')
465 and lookup_type = 'PV_PARTNER_TYPE_RANKING'
466 and tag is not null
467 and language = userenv('LANG')
468 and lookup_code <> 'VAD'
469 );
470
471
472
473 BEGIN
474
475 fnd_message.set_name('PV','PV_MIGR_HEADER');
476 FND_MESSAGE.SET_TOKEN('STARTDATE',TO_char(SYSDATE,'MM/DD/YYYY hh:mm:ss'));
477 FND_MESSAGE.SET_TOKEN('USER',fnd_global.user_name);
478
479 FND_MESSAGE.SET_TOKEN('PARAM1','Running_Mode');
480 FND_MESSAGE.SET_TOKEN('PARAM2','Overwrite');
481 FND_MESSAGE.SET_TOKEN('PARAMVALUE1',p_running_mode);
482 FND_MESSAGE.SET_TOKEN('PARAMVALUE2',p_Overwrite);
483
484 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
485
486 SAVEPOINT migrate_partner_type;
487
488 --Validating the PV_PARTNER_TYPE_RNKING lookup setup.
489 -- If setup is not available then Exit the program.
490 select count(*)
491 into l_count
492 from fnd_lookup_values
493 where lookup_type = 'PV_PARTNER_TYPE_RANKING'
494 and language = userenv('LANG')
495 and enabled_flag = 'Y';
496
497 if not l_count > 0 then
498 FND_MESSAGE.set_name('PV','PV_MIGR_LKUP_SETUP');
499 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
500
501 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
502 end if;
503
504 -- Validate that all different partner types code are included
505 -- in PV_PARTNER_TYPE_RNKING lookup setup.
506 select
507 COUNT(*) into l_count
508 from
509 pv_attribute_codes_b
510 where
511 attribute_id = 3 and
512 attr_code <> 'VAD' AND
513 ATTR_CODE NOT IN
514 (
515 SELECT
516 LOOKUP_CODE
517 FROM
518 FND_LOOKUP_VALUES
519 WHERE
520 LOOKUP_CODE<> 'VAD' AND
521 language = userenv('LANG') and
522 lookup_type = 'PV_PARTNER_TYPE_RANKING'and
523 enabled_flag = 'Y'
524 );
525 IF l_count > 0 THEN
526 FND_MESSAGE.set_name('PV','PV_MIGR_LKUP_INCOMPL');
527 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
528 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
529 end if;
530
531
532 --Validate that all partner types have been ranked, if yes check if they are numbers
533 select
534 count(*) into l_count
535 from
536 FND_LOOKUP_VALUES
537 where
538 LOOKUP_CODE <> 'VAD' AND
539 lookup_type = 'PV_PARTNER_TYPE_RANKING' AND
540 language = userenv('LANG') and
541 enabled_flag = 'Y' and
542 TAG IS NULL;
543
544 if l_count > 0 then
545 FND_MESSAGE.set_name('PV','PV_MIGR_RANK_INCOMPL');
546 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
547
548 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
549 end if;
550
551 --Check for the integer
552 begin
553 select
554 count(to_number(TAG)) into l_count
555 from
556 FND_LOOKUP_VALUES
557 where
558 LOOKUP_CODE <> 'VAD' AND
559 lookup_type = 'PV_PARTNER_TYPE_RANKING' AND
560 enabled_flag = 'Y' and
561 language = userenv('LANG') and
562 TAG IS NOT NULL;
563 exception
564 when INVALID_NUMBER then
565 FND_MESSAGE.set_name('PV','PV_MIGR_RANK_NUMBER');
566 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
567
568 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
569 end;
570
571
572 --Make sure that the same ranking is not used by more than one partner_type
573
574 select
575 count(distinct tag) into l_distinct_count
576 from
577 FND_LOOKUP_VALUES
578 where
579 LOOKUP_CODE <> 'VAD' AND
580 lookup_type = 'PV_PARTNER_TYPE_RANKING' AND
581 language = userenv('LANG') and
582 TAG IS NOT NULL;
583
584 select
585 count(tag) into l_count
586 from
587 FND_LOOKUP_VALUES
588 where
589 LOOKUP_CODE <> 'VAD' AND
590 lookup_type = 'PV_PARTNER_TYPE_RANKING' AND
591 language = userenv('LANG') and
592 TAG IS NOT NULL;
593
594
595 if l_count <> l_distinct_count then
596 FND_MESSAGE.set_name('PV','PV_MIGR_RANK_UNIQUE');
597 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
598
599 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
600 end if;
601
602
603 -- Writing the header information of the Log report.
604 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
605 FND_MESSAGE.set_name('PV','PV_MIGR_RANKING');
606 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
610 loop
607 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
608
609 for x in Get_Ranked_Partner_types
611 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ' || RPAD(x.lookup_code,50) || ' ' || x.tag);
612 end loop;
613
614 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
615
616
617 -- Writing the 'Active' and 'Inactive' partner type count in the Log report.
618 for x in Get_Partner_Status_Count
619 loop
620 FND_MESSAGE.set_name('PV','PV_MIGR_DETAILS');
621 FND_MESSAGE.SET_TOKEN('ACTIVEPARTNERS',x.Active);
622 FND_MESSAGE.SET_TOKEN('INACTIVEPARTNERS',x.InActive);
623 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
624 end loop;
625
626 if p_OverWrite = 'N' then
627 for x in Get_Unprocessed_Record_Count
628 loop
629 l_unprocessed_count := x.partner_count;
630 end loop;
631 else
632 l_unprocessed_count := 0;
633 end if;
634
635 -- Start processing all VAD partner types.
636 IF p_OverWrite = 'Y' then
637
638 l_processed_count := 0;
639 -- OPEN the Cursor for all VAD partners for processing with Overwrite(Y)
640 OPEN Get_VAD_Prtnr_Typ_Migrn_OvWrt;
641
642 -- FETCH first VAD partner for processing.
643 FETCH Get_VAD_Prtnr_Typ_Migrn_OvWrt
644 INTO
645 l_attr_value,
646 l_entity_id;
647
648 WHILE Get_VAD_Prtnr_Typ_Migrn_OvWrt%FOUND
649 LOOP
650 BEGIN
651
652 -- Standard Start of savepoint
653 SAVEPOINT VAD_Partner_OWRTY;
654
655 -- Initialize return variables before the procedure call.
656 l_return_status := FND_API.G_RET_STS_SUCCESS;
657 l_msg_count := 0;
658 l_msg_data := NULL;
659
660 Update_Primary_Partner
661 (
662 p_entity_id => l_entity_id,
663 p_primary_partner_type => l_attr_value,
664 x_return_status => l_return_status,
665 x_msg_count => l_msg_count,
666 x_msg_data => l_msg_data
667 );
668
669 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
670 RAISE FND_API.G_EXC_ERROR;
671 END IF;
672
673 EXCEPTION
674 WHEN FND_API.G_EXC_ERROR THEN
675 ROLLBACK TO VAD_Partner_OWRTY;
676 WHEN OTHERS THEN
677 ROLLBACK TO VAD_Partner_OWRTY;
678 END;
679
680
681 l_processed_count := l_processed_count + 1;
682 if mod(l_processed_count,50) = 0 then
683 if p_running_mode = 'EXECUTION' then
684 commit;
685 end if;
686 end if;
687
688 FETCH Get_VAD_Prtnr_Typ_Migrn_OvWrt
689 INTO
690 l_attr_value,
691 l_entity_id;
692
693 -- Commented by Rahul Dev Sharma
694 -- CLOSE Get_VAD_Prtnr_Typ_Migrn_OvWrt;
695
696 END LOOP; -- Finish the LOOP for all the VAD partners.
697
698 -- CLOSE the cursor Get_VAD_Prtnr_Typ_Migrn_OvWrt after processing all VAD partners.
699 CLOSE Get_VAD_Prtnr_Typ_Migrn_OvWrt;
700
701 ---- ********************************************************* ----
702 ---- Process all Other Partners second, when p_OverWrite = 'Y' ----
703 ---- ********************************************************* ----
704
705 -- OPEN the Cursor for all other types partners for processing with Overwrite(Y).
706 OPEN Get_Prtnr_Typ_Migrn_Overwrite;
707
708 -- BEGIN
709 l_processed_count := l_processed_count + Get_Prtnr_Typ_Migrn_Overwrite%RowCount;
710 FETCH
711 Get_Prtnr_Typ_Migrn_Overwrite
712 INTO
713 l_attr_value,
714 l_entity_id;
715
716 WHILE Get_Prtnr_Typ_Migrn_Overwrite%FOUND
717 LOOP
718 BEGIN
719
720 -- Standard Start of savepoint
721 SAVEPOINT OTHER_Partner_OWRTY;
722
723 -- Initialize return variables before the procedure call.
724 l_return_status := FND_API.G_RET_STS_SUCCESS;
725 l_msg_count := 0;
726 l_msg_data := NULL;
727
728 Update_Primary_Partner
729 (
730 p_entity_id => l_entity_id,
731 p_primary_partner_type => l_attr_value ,
732 x_return_status => l_return_status,
733 x_msg_count => l_msg_count,
734 x_msg_data => l_msg_data
735 );
736
737 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
738 RAISE FND_API.G_EXC_ERROR;
739 END IF;
740
741 EXCEPTION
742 WHEN FND_API.G_EXC_ERROR THEN
743 ROLLBACK TO OTHER_Partner_OWRTY;
744 WHEN OTHERS THEN
745 ROLLBACK TO OTHER_Partner_OWRTY;
746 END;
747
748 l_processed_count := l_processed_count + 1;
749 if mod(l_processed_count,50) = 0 then
750 if p_running_mode = 'EXECUTION' then
751 commit;
752 end if;
753 end if;
754
755 FETCH Get_Prtnr_Typ_Migrn_Overwrite
756 INTO
757 l_attr_value,
758 l_entity_id;
759
760 END LOOP;
761
762 -- CLOSE the cursor Get_Prtnr_Typ_Migrn_OvWrt after processing all other types partners.
763 CLOSE Get_Prtnr_Typ_Migrn_Overwrite;
764 else
765 ---- ***************************************************** ----
766 ---- Process all VAD Partners first when p_OverWrite = 'N' ----
767 ---- ***************************************************** ----
768 l_processed_count := 0;
769 OPEN Get_VAD_Prtnr_Typ_Migrn;
770 l_processed_count := Get_VAD_Prtnr_Typ_Migrn%RowCount;
771
772 FETCH
776 l_entity_id;
773 Get_VAD_Prtnr_Typ_Migrn
774 INTO
775 l_attr_value,
777
778 WHILE Get_VAD_Prtnr_Typ_Migrn%FOUND
779 LOOP
780 BEGIN
781
782
783 -- Standard Start of savepoint
784 SAVEPOINT VAD_Partner_OWRTN;
785
786 -- Initialize return variables before the procedure call.
787 l_return_status := FND_API.G_RET_STS_SUCCESS;
788 l_msg_count := 0;
789 l_msg_data := NULL;
790
791 Update_Primary_Partner(
792 p_entity_id => l_entity_id,
793 p_primary_partner_type => l_attr_value ,
794 x_return_status => l_return_status,
795 x_msg_count => l_msg_count,
796 x_msg_data => l_msg_data
797 );
798
799 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
800 RAISE FND_API.G_EXC_ERROR;
801 END IF;
802
803 EXCEPTION
804 WHEN FND_API.G_EXC_ERROR THEN
805 ROLLBACK TO VAD_Partner_OWRTN;
806 WHEN OTHERS THEN
807 ROLLBACK TO VAD_Partner_OWRTN;
808 END;
809
810 l_processed_count := l_processed_count + 1;
811 if mod(l_processed_count,50) = 0 then
812 if p_running_mode = 'EXECUTION' then
813 commit;
814 end if;
815 end if;
816
817 FETCH
818 Get_VAD_Prtnr_Typ_Migrn
819 INTO
820 l_attr_value,
821 l_entity_id;
822 END LOOP;
823
824 CLOSE Get_VAD_Prtnr_Typ_Migrn;
825
826 ---- ********************************************************* ----
827 ---- Process all Other Partners second, when p_OverWrite = 'N' ----
828 ---- ********************************************************* ----
829 OPEN Get_Prtnr_Typ_Migrn;
830 l_processed_count := l_processed_count + Get_Prtnr_Typ_Migrn%RowCount;
831 FETCH Get_Prtnr_Typ_Migrn
832 INTO
833 l_attr_value,
834 l_entity_id;
835
836 WHILE Get_Prtnr_Typ_Migrn%FOUND
837 LOOP
838 BEGIN
839 -- Standard Start of savepoint
840 SAVEPOINT OTHER_Partner_OWRTN;
841
842 -- Initialize return variables before the procedure call.
843 l_return_status := FND_API.G_RET_STS_SUCCESS;
844 l_msg_count := 0;
845 l_msg_data := NULL;
846
847 Update_Primary_Partner(
848 p_entity_id => l_entity_id,
849 p_primary_partner_type => l_attr_value ,
850 x_return_status => l_return_status,
851 x_msg_count => l_msg_count,
852 x_msg_data => l_msg_data
853 );
854
855 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
856 RAISE FND_API.G_EXC_ERROR;
857 END IF;
858
859 EXCEPTION
860 WHEN FND_API.G_EXC_ERROR THEN
861 ROLLBACK TO OTHER_Partner_OWRTN;
862 WHEN OTHERS THEN
863 ROLLBACK TO OTHER_Partner_OWRTN;
864 END;
865
866 l_processed_count := l_processed_count + 1;
867 if mod(l_processed_count,50) = 0 then
868 if p_running_mode = 'EXECUTION' then
869 commit;
870 end if;
871 end if;
872
873 FETCH Get_Prtnr_Typ_Migrn
874 INTO
875 l_attr_value,
876 l_entity_id;
877
878 END LOOP;
879
880 CLOSE Get_Prtnr_Typ_Migrn;
881
882 END IF;
883 -- FND_FILE.PUT_LINE(FND_FILE.LOG, ' Partner Type Number of Primary Partners');
884
885 FND_MESSAGE.set_name('PV','PV_MIGR_PTNRTYP_COUNT');
886 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
887
888 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
889
890 for x in Get_Primary_Partner_Type_count
891 loop
892 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ' || rpad(x.attr_value,62) || ' ' || x.partner_count );
893 end loop;
894
895 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
896
897 FND_MESSAGE.set_name('PV','PV_MIGR_FOOTER');
898 FND_MESSAGE.SET_TOKEN('PROCESSED',lpad(l_processed_count,5));
899 FND_MESSAGE.SET_TOKEN('UNPROCESSED',lpad(l_unprocessed_count,5));
900 FND_MESSAGE.SET_TOKEN('ENDDATE',TO_char(SYSDATE,'MM/DD/YYYY hh:mm:ss'));
901
902 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
903
904 if p_Running_Mode = 'EVALUATION' then
905 ROLLBACK TO migrate_partner_type;
906 else
907 commit;
908 end if;
909
910 EXCEPTION
911 WHEN OTHERS THEN
912 -- ROLLBACK;
913 FND_MESSAGE.SET_NAME('PV', 'PV_MIGR_ERROR');
914 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
915 errbuf := FND_MESSAGE.get;
916 retcode := 2;
917 FND_FILE.PUT_LINE(FND_FILE.LOG, 'SQLERRM: ' || SQLERRM);
918 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
919 FND_FILE.PUT_LINE(FND_FILE.LOG, 'message[' ||I||']=');
920 FND_FILE.PUT_LINE(FND_FILE.LOG, Substr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1,255));
921 END LOOP;
922 END;
923
924
925 END;
[Home] [Help]