1 PACKAGE BODY PON_TCA_MERGE_PVT as
2 /* $Header: PONTCMGB.pls 120.5 2006/03/28 03:53:11 sapandey noship $ */
3
4 --
5 -- This is the basic signature required by the TCA Party Merge routine
6 --
7 -- PROCEDURE PARTY_MERGE(
8 -- p_Entity_name IN VARCHAR2,
9 -- p_from_id IN NUMBER,
10 -- x_to_id IN OUT NOCOPY NUMBER ,
11 -- p_From_FK_id IN NUMBER,
12 -- p_To_FK_id IN NUMBER,
13 -- p_Parent_Entity_name IN VARCHAR2,
14 -- p_batch_id IN NUMBER,
15 -- p_Batch_Party_id IN NUMBER,
16 -- x_return_status IN OUT NOCOPY VARCHAR2 )
17 --
18
19 -- Start of comments
20 -- API name : VETO_ENTERPRISE_PARTY_MERGE
21 --
22 -- Type : Group
23 --
24 -- Pre-reqs : Negotiation with the given trading_partmer_id
25 -- (p_From_FK_id) must exists in the database
26 --
27 -- Function : This procedure will be attached to the Trading Partner Id
28 -- merge for Sourcing Buyer entities thus it will simply veto the
29 -- accidental merge without checking any thing.
30 --
31 -- So, DO NOT attach this procedure to any other Party Merge
32 -- scenario apart from the Enterprise Buyer Party merge case
33 -- for which it is designed
34 --
35 -- Parameters:
36 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
37 -- TCA Merge Dictionary that is going to merge
38 --
39 -- IN : p_from_id NUMBER Required, the value of PK of the record
40 -- which is going to be merged
41 --
42 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
43 -- to which the p_from_id party is going to be merged
44 --
45 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
46 -- Party Site, etc.) when merge is executed
47 --
48 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
49 -- Party Site, etc.) when merge is executed
50 --
51 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
52 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
53 --
54 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
55 -- Party Merge is executed
56 --
57 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
58 -- for which the Party Merge is executed
59 --
60 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
61 -- was successful or not; It can have the following values -
62 --
63 -- FND_API.G_RET_STS_SUCCESS (Success)
64 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
65 -- failed due to Unexpected error)
66 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
67 -- vetod the Party Merge)
68 --
69 -- Version : Current version 1.0
70 -- Previous version 1.0
71 -- Initial version 1.0
72 --
73 -- End of comments
74 PROCEDURE VETO_ENTERPRISE_PARTY_MERGE (
75 p_Entity_name IN VARCHAR2,
76 p_from_id IN NUMBER,
77 x_to_id IN OUT NOCOPY NUMBER ,
78 p_From_FK_id IN NUMBER,
79 p_To_FK_id IN NUMBER,
80 p_Parent_Entity_name IN VARCHAR2,
81 p_batch_id IN NUMBER,
82 p_Batch_Party_id IN NUMBER,
83 x_return_status IN OUT NOCOPY VARCHAR2 )
84 IS
85 BEGIN
86 --{start of procedure
87
88 --
89 -- It does not check any thing and simply forwards
90 -- a veto against the current Party Merge
91 --
92 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
93 FND_MSG_PUB.ADD;
94 x_return_status := FND_API.G_RET_STS_ERROR;
95
96 --} end of procedure
97 END VETO_ENTERPRISE_PARTY_MERGE;
98
99
100 -- Start of comments
101 -- API name : CHECK_PERSON_PARTY_MERGE_TYPE
102 --
103 -- Type : Private
104 --
105 -- Pre-reqs : Given Party Id(p_from_id, to_from_id)
106 -- must exists in the HZ_PARTIES table
107 --
108 -- Function : This function will expect and accept to person party id
109 -- (merge from party id and merge to party id) and it will
110 -- return following values depending on the the situation
111 -- given -
112 --
113 -- Scenario Return Value
114 -- -------------------------- -------------------------
115 -- From and To parties are
116 -- Buer Users - BUYER
117 --
118 -- From and To parties are
119 -- Supplier Users - SELLER
120 --
121 -- From party is Buyer
122 -- and To party is Seller
123 -- user - INCOMPATIBLE
124 --
125 -- From party is Seller
126 -- and To party is Buyer
127 -- user - INCOMPATIBLE
128 --
129 -- From party is not
130 -- a Seller user and
131 -- From party is not
132 -- a Buyer user
133 -- But
134 -- To party is a Seller
135 -- user OR To party is
136 -- a Buyer user - INCOMPATIBLE
137 --
138 -- From party is not
139 -- a Seller or Buyer
140 -- user
141 -- AND
142 -- To party is not a
143 -- Buyer
144 -- OR Seller user - IRRELEVANT
145 --
146 --
147 -- Parameters:
148 -- IN : p_from_id NUMBER Required, the Merge From Party Id
149 --
150 -- IN : p_to_id NUMBER Required, the Merge To Party Id
151 --
152 --
153 -- Version : Current version 1.0
154 -- Previous version 1.0
155 -- Initial version 1.0
156 --
157 -- End of comments
158 FUNCTION CHECK_PERSON_PARTY_MERGE_TYPE (
159 p_from_id IN NUMBER,
160 p_to_id IN NUMBER
161 )
162 RETURN VARCHAR2
163 IS
164 PRAGMA AUTONOMOUS_TRANSACTION;
165 l_merge_type VARCHAR2(20);
166 l_from_user_type VARCHAR2(20);
167 l_to_user_type VARCHAR2(20);
168 l_counter NUMBER;
169
170 CURSOR CHECK_USER_TYPE (p_user_party_id NUMBER)
171 IS
172 SELECT 'BUYER' AS USER_TYPE
173 FROM
174 HZ_PARTIES USER_PARTIES,
175 HZ_RELATIONSHIPS,
176 HZ_CODE_ASSIGNMENTS
177 WHERE USER_PARTIES.PARTY_ID = p_user_party_id
178 AND HZ_RELATIONSHIPS.SUBJECT_ID = USER_PARTIES.PARTY_ID
179 AND HZ_RELATIONSHIPS.RELATIONSHIP_TYPE = 'POS_EMPLOYMENT'
180 AND HZ_RELATIONSHIPS.RELATIONSHIP_CODE = 'EMPLOYEE_OF'
181 AND HZ_RELATIONSHIPS.START_DATE <= SYSDATE
182 AND HZ_RELATIONSHIPS.END_DATE >= SYSDATE
183 AND HZ_CODE_ASSIGNMENTS.OWNER_TABLE_ID = HZ_RELATIONSHIPS.OBJECT_ID
184 AND HZ_CODE_ASSIGNMENTS.OWNER_TABLE_NAME = 'HZ_PARTIES'
185 AND HZ_CODE_ASSIGNMENTS.CLASS_CATEGORY = 'POS_PARTICIPANT_TYPE'
186 AND HZ_CODE_ASSIGNMENTS.CLASS_CODE = 'ENTERPRISE'
187 UNION
188 SELECT 'SELLER' AS USER_TYPE
189 FROM
190 POS_SUPPLIER_USERS_V
191 WHERE PERSON_PARTY_ID = p_user_party_id;
192
193 BEGIN
194
195 l_from_user_type := 'NONE';
196 l_to_user_type := 'NONE';
197 l_counter := 0;
198
199 -- fnd_file.put_line (fnd_file.log, '10 : Starting Party Comparion:');
200 --
201 -- open the user check cursor to determine user type
202 --
203 FOR user IN CHECK_USER_TYPE(p_from_id) LOOP
204 l_from_user_type := user.USER_TYPE;
205 l_counter := l_counter + 1;
206
207 EXIT WHEN (l_counter = 1); -- May be I can do a better construct here
208 END LOOP;
209
210 -- fnd_file.put_line (fnd_file.log, '20 : Merge From Party Type:'||l_from_user_type);
211
212 l_counter := 0;
213 FOR user IN CHECK_USER_TYPE(p_to_id) LOOP
214 l_to_user_type := user.USER_TYPE;
215 l_counter := l_counter + 1;
216
217 EXIT WHEN (l_counter = 1); -- May be I can do a better construct here
218 END LOOP;
219
220 -- fnd_file.put_line (fnd_file.log, '20 : Merge To Party Type:'||l_to_user_type);
221
222 IF (l_from_user_type = 'BUYER') THEN
223
224 IF (l_to_user_type = 'BUYER') THEN
225 l_merge_type := G_BUYER;
226 ELSE
227 l_merge_type := G_INCOMPATIBLE;
228 END IF;
229
230 ELSIF (l_from_user_type = 'SELLER') THEN
231
232 IF (l_to_user_type = 'SELLER') THEN
233 l_merge_type := G_SELLER;
234 ELSE
235 l_merge_type := G_INCOMPATIBLE;
236 END IF;
237
238 ELSE
239
240 IF (l_to_user_type = 'SELLER' OR l_to_user_type = 'BUYER') THEN
241 l_merge_type := G_INCOMPATIBLE;
242 ELSE
243 l_merge_type := G_IRRELEVANT;
244 END IF;
245
246 END IF;
247
248 RETURN l_merge_type;
249 END CHECK_PERSON_PARTY_MERGE_TYPE;
250
251 -- Start of comments
252 -- API name : CHECK_COMPANY_PARTY_MERGE_TYPE
253 --
254 -- Type : Private
255 --
256 -- Pre-reqs : Given Company Party Id(p_from_id, to_from_id)
257 -- must exists in the HZ_PARTIES table
258 --
259 -- Function : This function will expect and accept to company party id
260 -- (merge from party id and merge to party id) and it will
261 -- return following values depending on the the situation
262 -- given -
263 --
264 -- Scenario Return Value
265 -- -------------------------- -------------------------
266 -- From and To parties are
267 -- Buer Companies - INCOMPATIBLE
268 --
269 -- From and To parties are
270 -- Supplier Companies - SELLER
271 --
272 -- From party is Buyer
273 -- and To party is Seller
274 -- Company - INCOMPATIBLE
275 --
276 -- From party is Seller
277 -- and To party is Buyer
278 -- Companies - INCOMPATIBLE
279 --
280 -- From party is not
281 -- a Seller and
282 -- From party is not
283 -- a Buyer Company
284 -- But
285 -- To party is a Seller
286 -- OR To party is
287 -- a Buyer Company - INCOMPATIBLE
288 --
289 -- From party is not
290 -- a Seller or Buyer
291 -- user
292 -- AND
293 -- To party is not a
294 -- Buyer
295 -- OR Seller
296 -- Companies - IRRELEVANT
297 --
298 --
299 -- Parameters:
300 -- IN : p_from_id NUMBER Required, the Merge From Party Id
301 --
302 -- IN : p_to_id NUMBER Required, the Merge To Party Id
303 --
304 --
305 -- Version : Current version 1.0
306 -- Previous version 1.0
307 -- Initial version 1.0
308 --
309 -- End of comments
310 FUNCTION CHECK_COMPANY_PARTY_MERGE_TYPE (
311 p_from_id IN NUMBER,
312 p_to_id IN NUMBER
313 )
314 RETURN VARCHAR2
315 IS
316 l_merge_type VARCHAR2(10);
317 l_from_comp_type VARCHAR2(10);
318 l_to_comp_type VARCHAR2(10);
319 l_counter NUMBER;
320
321 CURSOR CHECK_COMPANY_TYPE (p_party_id NUMBER)
322 IS
323 SELECT 'BUYER' AS COMPANY_TYPE
324 FROM
325 HZ_PARTIES USER_PARTIES,
326 HZ_CODE_ASSIGNMENTS
327 WHERE USER_PARTIES.PARTY_ID = p_party_id
328 AND USER_PARTIES.PARTY_TYPE = 'ORGANIZATION'
329 AND HZ_CODE_ASSIGNMENTS.OWNER_TABLE_ID = USER_PARTIES.PARTY_ID
330 AND HZ_CODE_ASSIGNMENTS.OWNER_TABLE_NAME = 'HZ_PARTIES'
331 AND HZ_CODE_ASSIGNMENTS.CLASS_CATEGORY = 'POS_PARTICIPANT_TYPE'
332 AND HZ_CODE_ASSIGNMENTS.CLASS_CODE = 'ENTERPRISE'
333 UNION
334 SELECT 'SELLER' AS COMPANY_TYPE
335 FROM
336 PO_VENDORS
337 WHERE PARTY_ID = p_party_id;
338
339 BEGIN
340
341 l_from_comp_type := 'NONE';
342 l_to_comp_type := 'NONE';
343 l_counter := 0;
344
345 --
346 -- open the user check cursor to determine user type
347 --
348 FOR comp IN CHECK_COMPANY_TYPE(p_from_id) LOOP
349 l_from_comp_type := comp.COMPANY_TYPE;
350 l_counter := l_counter + 1;
351
352 EXIT WHEN (l_counter = 1); -- May be I can do a better construct here
353 END LOOP;
354
355 l_counter := 0;
356 FOR comp IN CHECK_COMPANY_TYPE(p_to_id) LOOP
357 l_to_comp_type := comp.COMPANY_TYPE;
358 l_counter := l_counter + 1;
359
360 EXIT WHEN (l_counter = 1); -- May be I can do a better construct here
361 END LOOP;
362
363 IF (l_from_comp_type = 'BUYER') THEN
364
365 l_merge_type := G_INCOMPATIBLE;
366
367 ELSIF (l_from_comp_type = 'SELLER') THEN
368
369 IF (l_to_comp_type = 'SELLER') THEN
370 l_merge_type := G_SELLER;
371 ELSE
372 l_merge_type := G_INCOMPATIBLE;
373 END IF;
374
375 ELSE
376
377 IF (l_to_comp_type = 'BUYER') THEN
378 l_merge_type := G_INCOMPATIBLE;
379 --
380 -- If it is not a Buyer/Seller to Seller merge then
381 -- we have nothing to say as we will not have any transaction
382 -- But, I dont know why this API will be called then?
383 -- May be some data corruption hence raise veto
384 --
385 ELSIF (l_to_comp_type = 'SELLER' ) THEN
386 l_merge_type := G_INCOMPATIBLE;
387 ELSE
388 l_merge_type := G_IRRELEVANT;
389 END IF;
390
391 END IF;
392
393 RETURN l_merge_type;
394 END CHECK_COMPANY_PARTY_MERGE_TYPE;
395
396 -- Start of comments
397 -- API name : GET_USER_NAME
398 --
399 -- Type : Private
400 --
401 -- Pre-reqs : Given Company Party Id(p_user_party_id)
402 -- must exists in the HZ_PARTIES table
403 --
404 -- Function : This function will expect and accept to company party id
405 -- (given user party id as present in HZ before merge) and it will
406 -- return FND_USER.USER_NAME as it was BEFORE the merge
407 -- operation started.
408 -- It will reutn the FIRST user_name in case the given user party_id
409 -- is mapped to more than one user in FND_USER table.
410 -- It returns NULL if there are no user attached
411 --
412 -- Parameters:
413 -- IN : p_user_party_id NUMBER Required, the User Party Id
414 --
415 --
416 -- Version : Current version 1.0
417 -- Previous version 1.0
418 -- Initial version 1.0
419 --
420 -- End of comments
421 FUNCTION GET_USER_NAME (
422 p_user_party_id IN NUMBER
423 )
424 RETURN VARCHAR2
425 IS
426 PRAGMA AUTONOMOUS_TRANSACTION;
427 l_user_name FND_USER.USER_NAME%TYPE;
428 BEGIN
429
430 BEGIN
431 SELECT
432 fu.USER_NAME
433 INTO
434 l_user_name
435 FROM FND_USER fu,
436 HZ_PARTIES hz
437 WHERE fu.PERSON_PARTY_ID=hz.PARTY_ID
438 AND hz.PARTY_ID= p_user_party_id
439 AND ROWNUM = 1;
440 EXCEPTION
441 WHEN OTHERS THEN
442 l_user_name := NULL;
443 END;
444
445 RETURN l_user_name;
446 END GET_USER_NAME;
447
448 -- Start of comments
449 -- API name : GET_USER_ID
450 --
451 -- Type : Private
452 --
453 -- Pre-reqs : Given Company Party Id(p_user_party_id)
454 -- must exists in the HZ_PARTIES table
455 --
456 -- Function : This function will expect and accept to company party id
457 -- (given user party id as present in HZ before merge) and it will
461 -- is mapped to more than one user in FND_USER table.
458 -- return FND_USER.USER_ID as it was BEFORE the merge
459 -- operation started.
460 -- It will reutn the FIRST user_id in case the given user party_id
462 -- It returns NULL if there are no user attached
463 --
464 -- Parameters:
465 -- IN : p_user_party_id NUMBER Required, the User Party Id
466 --
467 --
468 -- Version : Current version 1.0
469 -- Previous version 1.0
470 -- Initial version 1.0
471 --
472 -- End of comments
473 FUNCTION GET_USER_ID (
474 p_user_party_id IN NUMBER
475 )
476 RETURN NUMBER
477 IS
478 PRAGMA AUTONOMOUS_TRANSACTION;
479 l_user_id FND_USER.USER_ID%TYPE;
480 BEGIN
481
482 BEGIN
483 SELECT
484 fu.USER_ID
485 INTO
486 l_user_id
487 FROM FND_USER fu,
488 HZ_PARTIES hz
489 WHERE fu.PERSON_PARTY_ID=hz.PARTY_ID
490 AND hz.PARTY_ID= p_user_party_id
491 AND ROWNUM = 1;
492 EXCEPTION
493 WHEN OTHERS THEN
494 l_user_id := NULL;
495 END;
496
497 RETURN l_user_id;
498 END GET_USER_ID;
499
500 -- Start of comments
501 -- API name : HAS_MULTIPLE_FND_USERS
502 --
503 -- Type : Private
504 --
505 -- Pre-reqs : Given Company Party Id(p_user_party_id)
506 -- must exists in the HZ_PARTIES table
507 --
508 -- Function : This function will accept a user party id
509 -- (given user party id as present in HZ before merge) and it will
510 -- return true if there are multiple FND_USERs associated with
511 -- given user party id (p_user_party_id)
512 -- It will reutn false otherwise even if there are no FND_USER
513 -- associated
514 --
515 -- Parameters:
516 -- IN : p_user_party_id NUMBER Required, the User Party Id
517 --
518 --
519 -- Version : Current version 1.0
520 -- Previous version 1.0
521 -- Initial version 1.0
522 --
523 -- End of comments
524 FUNCTION HAS_MULTIPLE_FND_USERS (
525 p_user_party_id IN NUMBER
526 )
527 RETURN BOOLEAN
528 IS
529 PRAGMA AUTONOMOUS_TRANSACTION;
530 l_user_name_count NUMBER;
531 BEGIN
532
533 BEGIN
534 SELECT
535 COUNT(fu.USER_NAME)
536 INTO
537 l_user_name_count
538 FROM FND_USER fu,
539 HZ_PARTIES hz
540 WHERE fu.PERSON_PARTY_ID=hz.PARTY_ID
541 AND hz.PARTY_ID= p_user_party_id;
542 EXCEPTION
543 WHEN OTHERS THEN
544 l_user_name_count := 0;
545 END;
546
547 IF (l_user_name_count > 1) THEN
548 RETURN TRUE;
549 ELSE
550 RETURN FALSE;
551 END IF;
552 END HAS_MULTIPLE_FND_USERS;
553
554
555 -- Start of comments
556 -- API name : MERGE_NEG_TEAM_FND_USER
557 --
558 -- Type : Group
559 --
560 -- Pre-reqs : Negotiation with the given trading_partner_contact_id
561 -- (p_From_FK_id) must exists in the database
562 --
563 -- Function : This procedure will be attached to the USER_ID
564 -- column of PON_NEG_TEAM_MEMBERS table.
565 --
566 -- It will NOT veto Party Merge in any case
567 -- This will merge the all the PON_NEG_TEAM_MEMBERS records
568 -- having USER_ID equals to the only FND_USER mapped to p_From_FK_id
569 -- to only FND_USER mapped to party id having value (p_To_FK_id).
570 -- It will NOT merge the USER_ID information if any of the merging parties
571 -- have more than one FND_USER record mapped to one person in HZ_PARTIES.
572 --
573 -- It will not trown any error/exception in such scenario and will silently ignore
574 -- merge.
575 --
576 -- The procedure will not update PON_NEG_TEAM_MEMBERS.USER_ID
577 -- if there is a functional unique/primary key violation while updating the records.
578 --
579 -- Parameters:
580 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
581 -- Party Site, etc.) when merge is executed
582 --
583 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
584 -- Party Site, etc.) when merge is executed
585 --
586 -- Version : Current version 1.0
587 -- Previous version 1.0
588 -- Initial version 1.0
589 --
590 -- End of comments
591 PROCEDURE MERGE_NEG_TEAM_FND_USER (
592 p_From_FK_id IN NUMBER,
593 p_To_FK_id IN NUMBER)
594 IS
595 l_to_user_id FND_USER.USER_ID%TYPE;
596 l_from_user_id FND_USER.USER_ID%TYPE;
597
598 BEGIN
599 --{start of procedure
600
604
601 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.MERGE_NEG_TEAM_FND_USER ');
602 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
603 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
605 --
606 -- We will be updating the user_id column of pon_neg_team_members table
607 -- if there are only one FND_USER associated with the merge from ot merge to party id.
608 -- We will not touch the pon_neg_team_members otherwise.
609 --
610 fnd_file.put_line (fnd_file.log, '10 : Buyer Merge - Calling HAS_MULTIPLE_FND_USERS function');
611
612 IF (HAS_MULTIPLE_FND_USERS(p_To_FK_id) = FALSE AND
613 HAS_MULTIPLE_FND_USERS(p_From_FK_id) = FALSE) THEN
614 fnd_file.put_line (fnd_file.log, '20 : Buyer Merge - Merge From and Merge To Party has one FND_USER');
615 fnd_file.put_line (fnd_file.log, '30 : Buyer Merge - Merging PON_NEG_TEAM_MEMBERS table');
616
617 l_from_user_id := GET_USER_ID(p_From_FK_id);
618 l_to_user_id := GET_USER_ID(p_To_FK_id);
619
620 IF (l_from_user_id IS NOT NULL AND
621 l_to_user_id IS NOT NULL ) THEN
622
623 --
624 -- That means we have exactly only one fnd user for each of the merging parties
625 --
626 fnd_file.put_line (fnd_file.log, '40 : Buyer Merge - Both the parties have only one fnd user each');
627
628 UPDATE PON_NEG_TEAM_MEMBERS pntm
629 SET pntm.USER_ID = l_to_user_id
630 WHERE pntm.USER_ID = l_from_user_id
631 AND NOT EXISTS (SELECT 'DUPLICATE'
632 FROM PON_NEG_TEAM_MEMBERS pntm1
633 WHERE pntm.auction_header_id = pntm1.auction_header_id
634 AND pntm1.USER_ID = l_to_user_id);
635
636 fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated USER IDs in PON_NEG_TEAM_MEMBERS: '||sql%rowcount||' rows');
637
638 END If;
639 END IF;
640
641 EXCEPTION
642 WHEN OTHERS THEN
643
644 --
645 -- We need add some Sourcing specific message for this
646 --
647 fnd_file.put_line (fnd_file.log, '60 : Error in PON_TCA_PARTY_MERGE.MERGE_NEG_TEAM_FND_USER SQLERRM:'||SQLERRM);
648 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
649 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
650 FND_MSG_PUB.ADD;
651
652 --} end of procedure
653 END MERGE_NEG_TEAM_FND_USER;
654
655
656 -- Start of comments
657 -- API name : NEG_TPC_MERGE
658 --
659 -- Type : Group
660 --
661 -- Pre-reqs : Negotiation with the given trading_partner_contact_id
662 -- (p_From_FK_id) must exists in the database
663 --
664 -- Function : This procedure will be attached to the TRADING_PARTNER_CONTACT_ID
665 -- merge for Sourcing PON_AUCTION_HEADERS_ALL entity.
666 --
667 -- It will veto Party Merge if -
668 -- p_From_FK_id and p_To_FK_id both are not Buyer user party
669 --
670 -- This will merge the all the PON_AUCTION_HEADERS_ALL records
671 -- having TRADING_PARTNER_CONTACT_ID equals to p_From_FK_id
672 -- to party id having value (p_To_FK_id)
673 --
674 -- Parameters:
675 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
676 -- TCA Merge Dictionary that is going to merge
677 --
678 -- IN : p_from_id NUMBER Required, the value of PK of the record
679 -- which is going to be merged
680 --
681 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
682 -- to which the p_from_id party is going to be merged
683 --
684 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
685 -- Party Site, etc.) when merge is executed
686 --
687 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
688 -- Party Site, etc.) when merge is executed
689 --
690 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
691 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
692 --
693 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
694 -- Party Merge is executed
695 --
696 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
697 -- for which the Party Merge is executed
698 --
699 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
700 -- was successful or not; It can have the following values -
701 --
702 -- FND_API.G_RET_STS_SUCCESS (Success)
703 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
704 -- failed due to Unexpected error)
705 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
709 -- Previous version 1.0
706 -- vetod the Party Merge)
707 --
708 -- Version : Current version 1.0
710 -- Initial version 1.0
711 --
712 -- End of comments
713 PROCEDURE NEG_TPC_MERGE (
714 p_Entity_name IN VARCHAR2,
715 p_from_id IN NUMBER,
716 x_to_id IN OUT NOCOPY NUMBER ,
717 p_From_FK_id IN NUMBER,
718 p_To_FK_id IN NUMBER,
719 p_Parent_Entity_name IN VARCHAR2,
720 p_batch_id IN NUMBER,
721 p_Batch_Party_id IN NUMBER,
722 x_return_status IN OUT NOCOPY VARCHAR2 )
723 IS
724 l_merge_scenario VARCHAR2(20);
725 l_user_name FND_USER.USER_NAME%TYPE;
726 l_to_user_id FND_USER.USER_ID%TYPE;
727 l_from_user_id FND_USER.USER_ID%TYPE;
728
729 BEGIN
730 --{start of procedure
731
732 x_return_status := FND_API.G_RET_STS_SUCCESS;
733
734 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.NEG_TPC_MERGE ');
735 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
736 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
737 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
738 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
739 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
740 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
741 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
742 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
743
744 --
745 -- check the merge scenario
746 --
747 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
748 p_from_id => p_From_FK_id,
749 p_to_id => p_To_FK_id);
750
751 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
752
753 IF (l_merge_scenario = G_SELLER OR l_merge_scenario = G_INCOMPATIBLE) THEN
754
755 fnd_file.put_line (fnd_file.log, '20 : Seller Merge - Error');
756 --
757 -- This can not happen in any normal scenario as
758 -- PON_AUCTION_HEADERS_ALL.TRADING_PARTNER_CONTACT_ID
759 -- can never contain a seller user id. Thus, raising a veto though
760 -- it will possibly never be called
761 --
762 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
763 FND_MSG_PUB.ADD;
764 x_return_status := FND_API.G_RET_STS_ERROR;
765
766 ELSIF (l_merge_scenario = G_BUYER) THEN
767 --
768 -- Now transact the data we have
769 --
770 fnd_file.put_line (fnd_file.log, '30 : Buyer Merge - Getting User Name from FND');
771
772 l_user_name := GET_USER_NAME(p_To_FK_id);
773
774 fnd_file.put_line (fnd_file.log, '40 : Buyer Merge - User Name from FND:'||l_user_name);
775
776
777 --
778 -- If we do not get the l_user_name name then the
779 -- following SQLs will not be executed. Moreover,
780 -- there is no chance of unique key constraint violation
781 -- due to the following updates
782 --
783 UPDATE PON_AUCTION_HEADERS_ALL
784 SET TRADING_PARTNER_CONTACT_ID = p_To_FK_id,
785 LAST_UPDATE_DATE = SYSDATE,
786 LAST_UPDATED_BY = -1,
787 TRADING_PARTNER_CONTACT_NAME = l_user_name
788 WHERE TRADING_PARTNER_CONTACT_ID = p_From_FK_id;
789
790 fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated TPC IDs in PON_AUCTION_HEADERS_ALL: '||sql%rowcount||' rows');
791
792 UPDATE PON_DISCUSSIONS
793 SET OWNER_PARTY_ID = p_To_FK_id,
794 LAST_UPDATE_DATE = SYSDATE
795 WHERE OWNER_PARTY_ID = p_From_FK_id;
796
797 fnd_file.put_line (fnd_file.log, '60 : Buyer Merge - Updated TPC IDs in PON_DISCUSSIONS');
798
799 --
800 -- We will be updating the user_id column of pon_neg_team_members table
801 -- if there are only one FND_USER associated with the merge from ot merge to party id.
802 -- We will not touch the pon_neg_team_members otherwise.
803 --
804 fnd_file.put_line (fnd_file.log, '70 : Buyer Merge - Calling MERGE_NEG_TEAM_FND_USER procedure');
805
806 MERGE_NEG_TEAM_FND_USER (p_From_FK_id => p_From_FK_id,
807 p_To_FK_id => p_To_FK_id);
808
809 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
810 --
811 -- Nothing to do
812 --
813 fnd_file.put_line (fnd_file.log, '70 : Irrelevent Merge');
814 NULL;
815 END IF;
816
817 EXCEPTION
818 WHEN OTHERS THEN
819
820 --
821 -- We need add some Sourcing specific message for this
822 --
823 fnd_file.put_line (fnd_file.log, '80 : Error in PON_TCA_PARTY_MERGE.NEG_TPC_MERGE SQLERRM:'||SQLERRM);
824 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
828 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
825 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
826 FND_MSG_PUB.ADD;
827
829
830 --} end of procedure
831 END NEG_TPC_MERGE;
832
833 -- Start of comments
834 -- API name : NEG_DRFT_LCK_MERGE
835 --
836 -- Type : Group
837 --
838 -- Pre-reqs : Negotiation with the given draft_locked_by_contact_id
839 -- (p_From_FK_id) must exists in the database
840 --
841 -- Function : This procedure will be attached to the DRAFT_LOCKED_BY_CONTACT_ID
842 -- merge for Sourcing PON_AUCTION_HEADERS_ALL entity.
843 --
844 -- It will veto Party Merge if -
845 -- p_From_FK_id and p_To_FK_id both are not Buyer user party
846 --
847 -- This will merge the all the PON_AUCTION_HEADERS_ALL records
848 -- having DRAFT_LOCKED_BY_CONTACT_ID equals to p_From_FK_id
849 -- to party id having value (p_To_FK_id)
850 --
851 -- Parameters:
852 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
853 -- TCA Merge Dictionary that is going to merge
854 --
855 -- IN : p_from_id NUMBER Required, the value of PK of the record
856 -- which is going to be merged
857 --
858 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
859 -- to which the p_from_id party is going to be merged
860 --
861 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
862 -- Party Site, etc.) when merge is executed
863 --
864 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
865 -- Party Site, etc.) when merge is executed
866 --
867 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
868 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
869 --
870 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
871 -- Party Merge is executed
872 --
873 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
874 -- for which the Party Merge is executed
875 --
876 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
877 -- was successful or not; It can have the following values -
878 --
879 -- FND_API.G_RET_STS_SUCCESS (Success)
880 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
881 -- failed due to Unexpected error)
882 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
883 -- vetod the Party Merge)
884 --
885 -- Version : Current version 1.0
886 -- Previous version 1.0
887 -- Initial version 1.0
888 --
889 -- End of comments
890 PROCEDURE NEG_DRFT_LCK_MERGE (
891 p_Entity_name IN VARCHAR2,
892 p_from_id IN NUMBER,
893 x_to_id IN OUT NOCOPY NUMBER ,
894 p_From_FK_id IN NUMBER,
895 p_To_FK_id IN NUMBER,
896 p_Parent_Entity_name IN VARCHAR2,
897 p_batch_id IN NUMBER,
898 p_Batch_Party_id IN NUMBER,
899 x_return_status IN OUT NOCOPY VARCHAR2 )
900 IS
901 l_merge_scenario VARCHAR2(20);
902
903 BEGIN
904 --{start of procedure
905
906 x_return_status := FND_API.G_RET_STS_SUCCESS;
907
908 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.NEG_DRFT_LCK_MERGE ');
909 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
910 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
911 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
912 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
913 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
914 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
915 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
916 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
917
918 --
919 -- check the merge scenario
920 --
921 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
922 p_from_id => p_From_FK_id,
923 p_to_id => p_To_FK_id);
924
925 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
926
927 IF (l_merge_scenario = G_SELLER OR l_merge_scenario = G_INCOMPATIBLE) THEN
928
929 fnd_file.put_line (fnd_file.log, '20 : Seller or Incompatible Merge - Error');
930 --
931 -- This can not happen in any normal scenario as
932 -- PON_AUCTION_HEADERS_ALL.DRAFT_LOCKED_BY_CONTACT_ID
933 -- can never contain a seller user id. Thus, raising a veto though
934 -- it will possibly never be called
938 x_return_status := FND_API.G_RET_STS_ERROR;
935 --
936 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
937 FND_MSG_PUB.ADD;
939
940 ELSIF (l_merge_scenario = G_BUYER) THEN
941 --
942 -- Now transact the data we have
943 --
944 fnd_file.put_line (fnd_file.log, '30 : Buyer Merge ');
945
946 --
947 -- there is no chance of unique key constraint violation
948 -- due to the following updates
949 --
950 UPDATE PON_AUCTION_HEADERS_ALL
951 SET LAST_UPDATE_DATE = SYSDATE,
952 LAST_UPDATED_BY = -1,
953 DRAFT_LOCKED_BY_CONTACT_ID = p_To_FK_id
954 WHERE DRAFT_LOCKED_BY_CONTACT_ID = p_From_FK_id;
955
956 fnd_file.put_line (fnd_file.log, '40 : Buyer Merge - Updated DRAFT_LOCKED_BY_CONTACT_IDs in PON_AUCTION_HEADERS_ALL: '||sql%rowcount||' rows');
957
958 --
959 -- We will be updating the user_id column of pon_neg_team_members table
960 -- if there are only one FND_USER associated with the merge from ot merge to party id.
961 -- We will not touch the pon_neg_team_members otherwise.
962 --
963 fnd_file.put_line (fnd_file.log, '45 : Buyer Merge - Calling MERGE_NEG_TEAM_FND_USER procedure');
964
965 MERGE_NEG_TEAM_FND_USER (p_From_FK_id => p_From_FK_id,
966 p_To_FK_id => p_To_FK_id);
967
968 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
969 --
970 -- Nothing to do
971 --
972 fnd_file.put_line (fnd_file.log, '50 : Irrelevent Merge');
973 NULL;
974 END IF;
975
976 EXCEPTION
977 WHEN OTHERS THEN
978
979 --
980 -- We need add some Sourcing specific message for this
981 --
982 fnd_file.put_line (fnd_file.log, '60 : Error in PON_TCA_PARTY_MERGE.NEG_DRFT_LCK_MERGE SQLERRM:'||SQLERRM);
983 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
984 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
985 FND_MSG_PUB.ADD;
986
987 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
988
989 --} end of procedure
990 END NEG_DRFT_LCK_MERGE;
991
992 -- Start of comments
993 -- API name : NEG_DRFT_UNLCK_MERGE
994 --
995 -- Type : Group
996 --
997 -- Pre-reqs : Negotiation with the given draft_unlocked_by_contact_id
998 -- (p_From_FK_id) must exists in the database
999 --
1000 -- Function : This procedure will be attached to the DRAFT_UNLOCKED_BY_CONTACT_ID
1001 -- merge for Sourcing PON_AUCTION_HEADERS_ALL entity.
1002 --
1003 -- It will veto Party Merge if -
1004 -- p_From_FK_id and p_To_FK_id both are not Buyer user party
1005 --
1006 -- This will merge the all the PON_AUCTION_HEADERS_ALL records
1007 -- having DRAFT_UNLOCKED_BY_CONTACT_ID equals to p_From_FK_id
1008 -- to party id having value (p_To_FK_id)
1009 --
1010 -- Parameters:
1011 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
1012 -- TCA Merge Dictionary that is going to merge
1013 --
1014 -- IN : p_from_id NUMBER Required, the value of PK of the record
1015 -- which is going to be merged
1016 --
1017 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
1018 -- to which the p_from_id party is going to be merged
1019 --
1020 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
1021 -- Party Site, etc.) when merge is executed
1022 --
1023 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
1024 -- Party Site, etc.) when merge is executed
1025 --
1026 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
1027 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
1028 --
1029 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
1030 -- Party Merge is executed
1031 --
1032 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
1033 -- for which the Party Merge is executed
1034 --
1035 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
1036 -- was successful or not; It can have the following values -
1037 --
1038 -- FND_API.G_RET_STS_SUCCESS (Success)
1039 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
1040 -- failed due to Unexpected error)
1041 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
1042 -- vetod the Party Merge)
1043 --
1044 -- Version : Current version 1.0
1045 -- Previous version 1.0
1046 -- Initial version 1.0
1050 p_Entity_name IN VARCHAR2,
1047 --
1048 -- End of comments
1049 PROCEDURE NEG_DRFT_UNLCK_MERGE (
1051 p_from_id IN NUMBER,
1052 x_to_id IN OUT NOCOPY NUMBER ,
1053 p_From_FK_id IN NUMBER,
1054 p_To_FK_id IN NUMBER,
1055 p_Parent_Entity_name IN VARCHAR2,
1056 p_batch_id IN NUMBER,
1057 p_Batch_Party_id IN NUMBER,
1058 x_return_status IN OUT NOCOPY VARCHAR2 )
1059 IS
1060 l_merge_scenario VARCHAR2(20);
1061
1062 BEGIN
1063 --{start of procedure
1064
1065 x_return_status := FND_API.G_RET_STS_SUCCESS;
1066
1067 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.NEG_DRFT_UNLCK_MERGE ');
1068 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
1069 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
1070 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
1071 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
1072 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
1073 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
1074 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
1075 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
1076
1077 --
1078 -- check the merge scenario
1079 --
1080 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
1081 p_from_id => p_From_FK_id,
1082 p_to_id => p_To_FK_id);
1083
1084 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
1085
1086 IF (l_merge_scenario = G_SELLER OR l_merge_scenario = G_INCOMPATIBLE) THEN
1087
1088 fnd_file.put_line (fnd_file.log, '20 : Seller or Incompatible Merge - Error');
1089
1090 --
1091 -- This can not happen in any normal scenario as
1092 -- PON_AUCTION_HEADERS_ALL.DRAFT_UNLOCKED_BY_CONTACT_ID
1093 -- can never contain a seller user id. Thus, raising a veto though
1094 -- it will possibly never be called
1095 --
1096 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
1097 FND_MSG_PUB.ADD;
1098 x_return_status := FND_API.G_RET_STS_ERROR;
1099
1100 ELSIF (l_merge_scenario = G_BUYER) THEN
1101
1102 fnd_file.put_line (fnd_file.log, '30 : Buyer Merge ');
1103
1104 --
1105 -- Now transact the data we have
1106 --
1107
1108 --
1109 -- there is no chance of unique key constraint violation
1110 -- due to the following updates
1111 --
1112 UPDATE PON_AUCTION_HEADERS_ALL
1113 SET LAST_UPDATE_DATE = SYSDATE,
1114 LAST_UPDATED_BY = -1,
1115 DRAFT_UNLOCKED_BY_CONTACT_ID = p_To_FK_id
1116 WHERE DRAFT_UNLOCKED_BY_CONTACT_ID = p_From_FK_id;
1117
1118 fnd_file.put_line (fnd_file.log, '40 : Buyer Merge - Updated DRAFT_UNLOCKED_BY_CONTACT_IDs in PON_AUCTION_HEADERS_ALL: '||sql%rowcount||' rows');
1119
1120 --
1121 -- We will be updating the user_id column of pon_neg_team_members table
1122 -- if there are only one FND_USER associated with the merge from ot merge to party id.
1123 -- We will not touch the pon_neg_team_members otherwise.
1124 --
1125 fnd_file.put_line (fnd_file.log, '45 : Buyer Merge - Calling MERGE_NEG_TEAM_FND_USER procedure');
1126
1127 MERGE_NEG_TEAM_FND_USER (p_From_FK_id => p_From_FK_id,
1128 p_To_FK_id => p_To_FK_id);
1129
1130 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
1131
1132 fnd_file.put_line (fnd_file.log, '50 : Irrelevent Merge');
1133 --
1134 -- Nothing to do
1135 --
1136 NULL;
1137 END IF;
1138
1139 EXCEPTION
1140 WHEN OTHERS THEN
1141
1142 fnd_file.put_line (fnd_file.log, '60 : Error in PON_TCA_PARTY_MERGE.NEG_DRFT_UNLCK_MERGE SQLERRM:'||SQLERRM);
1143 --
1144 -- We need add some Sourcing specific message for this
1145 --
1146 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1147 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1148 FND_MSG_PUB.ADD;
1149
1150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1151
1152 --} end of procedure
1153 END NEG_DRFT_UNLCK_MERGE;
1154
1155
1156 -- Start of comments
1157 -- API name : NEG_SCORE_LCK_MERGE
1158 --
1159 -- Type : Group
1160 --
1161 -- Pre-reqs : Negotiation with the given scoring_lock_tp_contact_id
1162 -- (p_From_FK_id) must exists in the database
1163 --
1164 -- Function : This procedure will be attached to the SCORING_LOCK_TP_CONTACT_ID
1165 -- merge for Sourcing PON_AUCTION_HEADERS_ALL entity.
1166 --
1167 -- It will veto Party Merge if -
1168 -- p_From_FK_id and p_To_FK_id both are not Buyer user party
1169 --
1170 -- This will merge the all the PON_AUCTION_HEADERS_ALL records
1171 -- having SCORING_LOCK_TP_CONTACT_ID equals to p_From_FK_id
1175 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
1172 -- to party id having value (p_To_FK_id)
1173 --
1174 -- Parameters:
1176 -- TCA Merge Dictionary that is going to merge
1177 --
1178 -- IN : p_from_id NUMBER Required, the value of PK of the record
1179 -- which is going to be merged
1180 --
1181 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
1182 -- to which the p_from_id party is going to be merged
1183 --
1184 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
1185 -- Party Site, etc.) when merge is executed
1186 --
1187 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
1188 -- Party Site, etc.) when merge is executed
1189 --
1190 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
1191 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
1192 --
1193 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
1194 -- Party Merge is executed
1195 --
1196 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
1197 -- for which the Party Merge is executed
1198 --
1199 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
1200 -- was successful or not; It can have the following values -
1201 --
1202 -- FND_API.G_RET_STS_SUCCESS (Success)
1203 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
1204 -- failed due to Unexpected error)
1205 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
1206 -- vetod the Party Merge)
1207 --
1208 -- Version : Current version 1.0
1209 -- Previous version 1.0
1210 -- Initial version 1.0
1211 --
1212 -- End of comments
1213 PROCEDURE NEG_SCORE_LCK_MERGE (
1214 p_Entity_name IN VARCHAR2,
1215 p_from_id IN NUMBER,
1216 x_to_id IN OUT NOCOPY NUMBER ,
1217 p_From_FK_id IN NUMBER,
1218 p_To_FK_id IN NUMBER,
1219 p_Parent_Entity_name IN VARCHAR2,
1220 p_batch_id IN NUMBER,
1221 p_Batch_Party_id IN NUMBER,
1222 x_return_status IN OUT NOCOPY VARCHAR2 )
1223 IS
1224 l_merge_scenario VARCHAR2(20);
1225
1226 BEGIN
1227 --{start of procedure
1228
1229 x_return_status := FND_API.G_RET_STS_SUCCESS;
1230
1231 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.NEG_SCORE_LCK_MERGE ');
1232 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
1233 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
1234 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
1235 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
1236 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
1237 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
1238 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
1239 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
1240
1241 --
1242 -- check the merge scenario
1243 --
1244 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
1245 p_from_id => p_From_FK_id,
1246 p_to_id => p_To_FK_id);
1247
1248 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
1249
1250 IF (l_merge_scenario = G_SELLER OR l_merge_scenario = G_INCOMPATIBLE) THEN
1251
1252 fnd_file.put_line (fnd_file.log, '20 : Seller Merge - Error');
1253
1254 --
1255 -- This can not happen in any normal scenario as
1256 -- PON_AUCTION_HEADERS_ALL.SCORING_LOCK_TP_CONTACT_ID
1257 -- can never contain a seller user id. Thus, raising a veto though
1258 -- it will possibly never be called
1259 --
1260 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
1261 FND_MSG_PUB.ADD;
1262 x_return_status := FND_API.G_RET_STS_ERROR;
1263
1264 ELSIF (l_merge_scenario = G_BUYER) THEN
1265 --
1266 -- Now transact the data we have
1267 --
1268 fnd_file.put_line (fnd_file.log, '30 : Buyer Merge ');
1269
1270 --
1271 -- there is no chance of unique key constraint violation
1272 -- due to the following updates
1273 --
1274 UPDATE PON_AUCTION_HEADERS_ALL
1275 SET LAST_UPDATE_DATE = SYSDATE,
1276 LAST_UPDATED_BY = -1,
1277 SCORING_LOCK_TP_CONTACT_ID = p_To_FK_id
1278 WHERE SCORING_LOCK_TP_CONTACT_ID = p_From_FK_id;
1279
1283 -- We will be updating the user_id column of pon_neg_team_members table
1280 fnd_file.put_line (fnd_file.log, '40 : Buyer Merge - Updated SCORING_LOCK_TP_CONTACT_IDs in PON_AUCTION_HEADERS_ALL: '||sql%rowcount||' rows');
1281
1282 --
1284 -- if there are only one FND_USER associated with the merge from ot merge to party id.
1285 -- We will not touch the pon_neg_team_members otherwise.
1286 --
1287 fnd_file.put_line (fnd_file.log, '45 : Buyer Merge - Calling MERGE_NEG_TEAM_FND_USER procedure');
1288
1289 MERGE_NEG_TEAM_FND_USER (p_From_FK_id => p_From_FK_id,
1290 p_To_FK_id => p_To_FK_id);
1291
1292
1293 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
1294 --
1295 -- Nothing to do
1296 --
1297 fnd_file.put_line (fnd_file.log, '50 : Irrelevent Merge');
1298 NULL;
1299 END IF;
1300
1301 EXCEPTION
1302 WHEN OTHERS THEN
1303
1304 fnd_file.put_line (fnd_file.log, '60 : Error in PON_TCA_PARTY_MERGE.NEG_SCORE_LCK_MERGE SQLERRM:'||SQLERRM);
1305 --
1306 -- We need add some Sourcing specific message for this
1307 --
1308 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1309 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1310 FND_MSG_PUB.ADD;
1311
1312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1313
1314 --} end of procedure
1315 END NEG_SCORE_LCK_MERGE;
1316
1317
1318 -- Start of comments
1319 -- API name : NEG_EVENT_MERGE
1320 --
1321 -- Type : Group
1322 --
1323 -- Pre-reqs : Negotiation Event with the given trading_partner_contact_id
1324 -- (p_From_FK_id) must exists in the database
1325 --
1326 -- Function : This procedure will be attached to the TRADING_PARTNER_CONTACT_ID
1327 -- merge for Sourcing PON_AUCTION_EVENTS entity.
1328 --
1329 -- It will veto Party Merge if -
1330 -- p_From_FK_id and p_To_FK_id both are not Buyer user party
1331 --
1332 -- This will merge the all the PON_AUCTION_EVENTS records
1333 -- having TRADING_PARTNER_CONTACT_ID equals to p_From_FK_id
1334 -- to party id having value (p_To_FK_id)
1335 --
1336 -- Parameters:
1337 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
1338 -- TCA Merge Dictionary that is going to merge
1339 --
1340 -- IN : p_from_id NUMBER Required, the value of PK of the record
1341 -- which is going to be merged
1342 --
1343 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
1344 -- to which the p_from_id party is going to be merged
1345 --
1346 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
1347 -- Party Site, etc.) when merge is executed
1348 --
1349 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
1350 -- Party Site, etc.) when merge is executed
1351 --
1352 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
1353 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
1354 --
1355 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
1356 -- Party Merge is executed
1357 --
1358 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
1359 -- for which the Party Merge is executed
1360 --
1361 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
1362 -- was successful or not; It can have the following values -
1363 --
1364 -- FND_API.G_RET_STS_SUCCESS (Success)
1365 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
1366 -- failed due to Unexpected error)
1367 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
1368 -- vetod the Party Merge)
1369 --
1370 -- Version : Current version 1.0
1371 -- Previous version 1.0
1372 -- Initial version 1.0
1373 --
1374 -- End of comments
1375 PROCEDURE NEG_EVENT_MERGE (
1376 p_Entity_name IN VARCHAR2,
1377 p_from_id IN NUMBER,
1378 x_to_id IN OUT NOCOPY NUMBER ,
1379 p_From_FK_id IN NUMBER,
1380 p_To_FK_id IN NUMBER,
1381 p_Parent_Entity_name IN VARCHAR2,
1382 p_batch_id IN NUMBER,
1383 p_Batch_Party_id IN NUMBER,
1384 x_return_status IN OUT NOCOPY VARCHAR2 )
1385 IS
1386 l_merge_scenario VARCHAR2(20);
1387 l_person_name VARCHAR2(300);
1388
1389 BEGIN
1390 --{start of procedure
1391
1392 x_return_status := FND_API.G_RET_STS_SUCCESS;
1393
1394 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.NEG_EVENT_MERGE ');
1395 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
1399 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
1396 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
1397 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
1398 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
1400 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
1401 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
1402 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
1403
1404 --
1405 -- check the merge scenario
1406 --
1407 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
1408 p_from_id => p_From_FK_id,
1409 p_to_id => p_To_FK_id);
1410
1411 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
1412
1413 IF (l_merge_scenario = G_SELLER OR l_merge_scenario = G_INCOMPATIBLE) THEN
1414
1415 fnd_file.put_line (fnd_file.log, '20 : Seller Merge - Error');
1416 --
1417 -- This can not happen in any normal scenario as
1418 -- PON_AUCTION_EVENTS.TRADING_PARTNER_CONTACT_ID
1419 -- can never contain a seller user id. Thus, raising a veto though
1420 -- it will possibly never be called
1421 --
1422 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
1423 FND_MSG_PUB.ADD;
1424 x_return_status := FND_API.G_RET_STS_ERROR;
1425
1426 ELSIF (l_merge_scenario = G_BUYER) THEN
1427
1428 fnd_file.put_line (fnd_file.log, '30 : Buyer Merge - Getting Person Name from HZ_PARTIES');
1429 --
1430 -- Now transact the data we have
1431 --
1432 SELECT
1433 hz.person_first_name ||' '|| hz.person_last_name as person_name
1434 INTO
1435 l_person_name
1436 FROM HZ_PARTIES hz
1437 WHERE hz.PARTY_ID= p_To_FK_id;
1438
1439 fnd_file.put_line (fnd_file.log, '40 : Buyer Merge - Person Name from HZ:'|| l_person_name);
1440 --
1441 -- there is no chance of unique key constraint violation
1442 -- due to the following updates
1443 --
1444 UPDATE PON_AUCTION_EVENTS
1445 SET TRADING_PARTNER_CONTACT_ID = p_To_FK_id,
1446 LAST_UPDATE_DATE = SYSDATE,
1447 LAST_UPDATED_BY = -1,
1448 TRADING_PARTNER_CONTACT_NAME = l_person_name
1449 WHERE TRADING_PARTNER_CONTACT_ID = p_From_FK_id;
1450
1451 fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated TRADING_PARTNER_CONTACT_IDs in PON_AUCTION_EVENTS: '||sql%rowcount||' rows');
1452
1453 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
1454 --
1455 -- Nothing to do
1456 --
1457 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
1458 NULL;
1459 END IF;
1460
1461 EXCEPTION
1462 WHEN OTHERS THEN
1463
1464 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.NEG_EVENT_MERGE SQLERRM:'||SQLERRM);
1465 --
1466 -- We need add some Sourcing specific message for this
1467 --
1468 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1469 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1470 FND_MSG_PUB.ADD;
1471
1472 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1473
1474 --} end of procedure
1475 END NEG_EVENT_MERGE;
1476
1477 -- Start of comments
1478 -- API name : BIDDER_LST_MERGE
1479 --
1480 -- Type : Group
1481 --
1482 -- Pre-reqs : Bidders List with the given trading_partner_contact_id
1483 -- (p_From_FK_id) must exists in the database
1484 --
1485 -- Function : This procedure will be attached to the TRADING_PARTNER_CONTACT_ID
1486 -- merge for Sourcing PON_BIDDERS_LISTS entity.
1487 --
1488 -- It will veto Party Merge if -
1489 -- p_From_FK_id and p_To_FK_id both are not Buyer user party
1490 --
1491 -- This will merge the all the PON_BIDDERS_LISTS records
1492 -- having TRADING_PARTNER_CONTACT_ID equals to p_From_FK_id
1493 -- to party id having value (p_To_FK_id)
1494 --
1495 -- Parameters:
1496 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
1497 -- TCA Merge Dictionary that is going to merge
1498 --
1499 -- IN : p_from_id NUMBER Required, the value of PK of the record
1500 -- which is going to be merged
1501 --
1502 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
1503 -- to which the p_from_id party is going to be merged
1504 --
1505 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
1506 -- Party Site, etc.) when merge is executed
1507 --
1508 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
1509 -- Party Site, etc.) when merge is executed
1510 --
1511 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
1512 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
1513 --
1514 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
1515 -- Party Merge is executed
1516 --
1517 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
1518 -- for which the Party Merge is executed
1519 --
1520 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
1521 -- was successful or not; It can have the following values -
1522 --
1523 -- FND_API.G_RET_STS_SUCCESS (Success)
1524 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
1525 -- failed due to Unexpected error)
1526 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
1527 -- vetod the Party Merge)
1528 --
1529 -- Version : Current version 1.0
1530 -- Previous version 1.0
1531 -- Initial version 1.0
1532 --
1533 -- End of comments
1534 PROCEDURE BIDDER_LST_MERGE (
1535 p_Entity_name IN VARCHAR2,
1536 p_from_id IN NUMBER,
1537 x_to_id IN OUT NOCOPY NUMBER ,
1538 p_From_FK_id IN NUMBER,
1539 p_To_FK_id IN NUMBER,
1540 p_Parent_Entity_name IN VARCHAR2,
1541 p_batch_id IN NUMBER,
1542 p_Batch_Party_id IN NUMBER,
1543 x_return_status IN OUT NOCOPY VARCHAR2 )
1544 IS
1545 l_merge_scenario VARCHAR2(20);
1546
1547 BEGIN
1548 --{start of procedure
1549
1550 x_return_status := FND_API.G_RET_STS_SUCCESS;
1551
1552 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.BIDDER_LST_MERGE ');
1553 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
1554 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
1555 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
1556 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
1557 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
1558 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
1559 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
1560 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
1561
1562 --
1563 -- check the merge scenario
1564 --
1565 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
1566 p_from_id => p_From_FK_id,
1567 p_to_id => p_To_FK_id);
1568
1569 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
1570
1571 IF (l_merge_scenario = G_SELLER OR l_merge_scenario = G_INCOMPATIBLE) THEN
1572
1573 fnd_file.put_line (fnd_file.log, '20 : Seller Merge - Error');
1574 --
1575 -- This can not happen in any normal scenario as
1576 -- PON_BIDDERS_LISTS.TRADING_PARTNER_CONTACT_ID
1577 -- can never contain a seller user id. Thus, raising a veto though
1578 -- it will possibly never be called
1579 --
1580 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
1581 FND_MSG_PUB.ADD;
1582 x_return_status := FND_API.G_RET_STS_ERROR;
1583
1584 ELSIF (l_merge_scenario = G_BUYER) THEN
1585 --
1586 -- Now transact the data we have
1587 --
1588 fnd_file.put_line (fnd_file.log, '30 : Buyer Merge ');
1589 --
1590 -- there is no chance of unique key constraint violation
1591 -- due to the following updates
1592 --
1593 -- TRADING_PARTNER_CONTACT_NAME field is always null
1594 -- in the table hence it is not updated here to maintain the
1595 -- consistency
1596 --
1597 UPDATE PON_BIDDERS_LISTS
1601 WHERE TRADING_PARTNER_CONTACT_ID = p_From_FK_id;
1598 SET LAST_UPDATE_DATE = SYSDATE,
1599 LAST_UPDATED_BY = -1,
1600 TRADING_PARTNER_CONTACT_ID = p_To_FK_id
1602
1603 fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated TPC IDs in PON_BIDDERS_LISTS: '||sql%rowcount||' rows');
1604
1605 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
1606 --
1607 -- Nothing to do
1608 --
1609 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
1610 NULL;
1611 END IF;
1612
1613 EXCEPTION
1614 WHEN OTHERS THEN
1615
1616 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.BIDDER_LST_MERGE SQLERRM:'||SQLERRM);
1617 --
1618 -- We need add some Sourcing specific message for this
1619 --
1620 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1621 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1622 FND_MSG_PUB.ADD;
1623
1624 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1625
1626 --} end of procedure
1627 END BIDDER_LST_MERGE;
1628
1629
1630 -- Start of comments
1631 -- API name : NEG_ATTR_LST_MERGE
1632 --
1633 -- Type : Group
1634 --
1635 -- Pre-reqs : Attributes List with the given trading_partner_contact_id
1636 -- (p_From_FK_id) must exists in the database
1637 --
1638 -- Function : This procedure will be attached to the TRADING_PARTNER_CONTACT_ID
1639 -- merge for Sourcing PON_ATTRIBUTE_LISTS entity.
1640 --
1641 -- It will veto Party Merge if -
1642 -- p_From_FK_id and p_To_FK_id both are not Buyer user party
1643 --
1644 -- This will merge the all the PON_ATTRIBUTE_LISTS records
1645 -- having TRADING_PARTNER_CONTACT_ID equals to p_From_FK_id
1646 -- to party id having value (p_To_FK_id)
1647 --
1648 -- Parameters:
1649 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
1650 -- TCA Merge Dictionary that is going to merge
1651 --
1652 -- IN : p_from_id NUMBER Required, the value of PK of the record
1653 -- which is going to be merged
1654 --
1655 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
1656 -- to which the p_from_id party is going to be merged
1657 --
1658 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
1659 -- Party Site, etc.) when merge is executed
1660 --
1661 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
1662 -- Party Site, etc.) when merge is executed
1663 --
1664 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
1665 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
1666 --
1667 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
1668 -- Party Merge is executed
1669 --
1670 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
1671 -- for which the Party Merge is executed
1672 --
1673 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
1674 -- was successful or not; It can have the following values -
1675 --
1676 -- FND_API.G_RET_STS_SUCCESS (Success)
1677 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
1678 -- failed due to Unexpected error)
1679 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
1680 -- vetod the Party Merge)
1681 --
1682 -- Version : Current version 1.0
1683 -- Previous version 1.0
1684 -- Initial version 1.0
1685 --
1686 -- End of comments
1687 PROCEDURE NEG_ATTR_LST_MERGE (
1688 p_Entity_name IN VARCHAR2,
1689 p_from_id IN NUMBER,
1690 x_to_id IN OUT NOCOPY NUMBER ,
1691 p_From_FK_id IN NUMBER,
1692 p_To_FK_id IN NUMBER,
1693 p_Parent_Entity_name IN VARCHAR2,
1694 p_batch_id IN NUMBER,
1695 p_Batch_Party_id IN NUMBER,
1696 x_return_status IN OUT NOCOPY VARCHAR2 )
1697 IS
1698 l_merge_scenario VARCHAR2(20);
1699
1700 BEGIN
1701 --{start of procedure
1702
1703 x_return_status := FND_API.G_RET_STS_SUCCESS;
1704
1705 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.NEG_ATTR_LST_MERGE ');
1706 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
1707 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
1708 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
1709 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
1713 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
1710 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
1711 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
1712 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
1714
1715 --
1716 -- check the merge scenario
1717 --
1718 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
1719 p_from_id => p_From_FK_id,
1720 p_to_id => p_To_FK_id);
1721
1722 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
1723
1724 IF (l_merge_scenario = G_SELLER OR l_merge_scenario = G_INCOMPATIBLE) THEN
1725
1726 fnd_file.put_line (fnd_file.log, '20 : Seller Merge - Error');
1727 --
1728 -- This can not happen in any normal scenario as
1729 -- PON_ATTRIBUTE_LISTS.TRADING_PARTNER_CONTACT_ID
1730 -- can never contain a seller user id. Thus, raising a veto though
1731 -- it will possibly never be called
1732 --
1733 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
1734 FND_MSG_PUB.ADD;
1735 x_return_status := FND_API.G_RET_STS_ERROR;
1736
1737 ELSIF (l_merge_scenario = G_BUYER) THEN
1738
1739 fnd_file.put_line (fnd_file.log, '30 : Buyer Merge');
1740
1741 --
1742 -- Now transact the data we have
1743 --
1744
1745 --
1746 -- there is no chance of unique key constraint violation
1747 -- due to the following updates
1748 --
1749 UPDATE PON_ATTRIBUTE_LISTS
1750 SET LAST_UPDATE_DATE = SYSDATE,
1751 LAST_UPDATED_BY = -1,
1752 TRADING_PARTNER_CONTACT_ID = p_To_FK_id
1753 WHERE TRADING_PARTNER_CONTACT_ID = p_From_FK_id;
1754
1755 fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated TPC IDs in PON_ATTRIBUTE_LISTS: '||sql%rowcount||' rows');
1756
1757
1758 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
1759 --
1760 -- Nothing to do
1761 --
1762 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
1763 NULL;
1764 END IF;
1765
1766 EXCEPTION
1767 WHEN OTHERS THEN
1768
1769 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.NEG_ATTR_LST_MERGE SQLERRM:'||SQLERRM);
1770 --
1771 -- We need add some Sourcing specific message for this
1772 --
1773 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1774 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1775 FND_MSG_PUB.ADD;
1776
1777 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1778
1779 --} end of procedure
1780 END NEG_ATTR_LST_MERGE;
1781
1782 -- Start of comments
1783 -- API name : RES_SURROG_MERGE
1784 --
1785 -- Type : Group
1786 --
1787 -- Pre-reqs : Responses with the given surrog_bid_created_contact_id
1788 -- (p_From_FK_id) must exists in the database
1789 --
1790 -- Function : This procedure will be attached to the SURROG_BID_CREATED_CONTACT_ID
1791 -- merge for Sourcing PON_BID_HEADERS entity.
1792 --
1793 -- It will veto Party Merge if -
1794 -- p_From_FK_id and p_To_FK_id both are not Buyer user party
1795 --
1796 -- This will merge the all the PON_BID_HEADERS records
1797 -- having SURROG_BID_CREATED_CONTACT_ID equals to p_From_FK_id
1798 -- to party id having value (p_To_FK_id)
1799 --
1800 -- Parameters:
1801 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
1802 -- TCA Merge Dictionary that is going to merge
1803 --
1804 -- IN : p_from_id NUMBER Required, the value of PK of the record
1805 -- which is going to be merged
1806 --
1807 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
1808 -- to which the p_from_id party is going to be merged
1809 --
1810 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
1811 -- Party Site, etc.) when merge is executed
1812 --
1813 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
1814 -- Party Site, etc.) when merge is executed
1815 --
1816 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
1817 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
1818 --
1819 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
1820 -- Party Merge is executed
1821 --
1822 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
1823 -- for which the Party Merge is executed
1824 --
1825 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
1826 -- was successful or not; It can have the following values -
1827 --
1828 -- FND_API.G_RET_STS_SUCCESS (Success)
1829 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
1830 -- failed due to Unexpected error)
1831 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
1832 -- vetod the Party Merge)
1833 --
1834 -- Version : Current version 1.0
1835 -- Previous version 1.0
1836 -- Initial version 1.0
1837 --
1838 -- End of comments
1839 PROCEDURE RES_SURROG_MERGE (
1840 p_Entity_name IN VARCHAR2,
1841 p_from_id IN NUMBER,
1842 x_to_id IN OUT NOCOPY NUMBER ,
1843 p_From_FK_id IN NUMBER,
1844 p_To_FK_id IN NUMBER,
1845 p_Parent_Entity_name IN VARCHAR2,
1846 p_batch_id IN NUMBER,
1847 p_Batch_Party_id IN NUMBER,
1848 x_return_status IN OUT NOCOPY VARCHAR2 )
1849 IS
1850 l_merge_scenario VARCHAR2(20);
1851
1852 BEGIN
1853 --{start of procedure
1854
1855 x_return_status := FND_API.G_RET_STS_SUCCESS;
1856
1857 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.RES_SURROG_MERGE ');
1858 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
1859 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
1860 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
1861 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
1862 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
1863 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
1864 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
1865 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
1866
1867 --
1868 -- check the merge scenario
1869 --
1870 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
1871 p_from_id => p_From_FK_id,
1872 p_to_id => p_To_FK_id);
1873
1874 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
1875
1876 IF (l_merge_scenario = G_SELLER OR l_merge_scenario = G_INCOMPATIBLE) THEN
1877
1878 fnd_file.put_line (fnd_file.log, '20 : Seller Merge - Error');
1879 --
1880 -- This can not happen in any normal scenario as
1881 -- PON_BID_HEADERS.SURROG_BID_CREATED_CONTACT_ID
1882 -- can never contain a seller user id. Thus, raising a veto though
1883 -- it will possibly never be called
1884 --
1885 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
1886 FND_MSG_PUB.ADD;
1887 x_return_status := FND_API.G_RET_STS_ERROR;
1888
1889 ELSIF (l_merge_scenario = G_BUYER) THEN
1890 fnd_file.put_line (fnd_file.log, '30 : Buyer Merge ');
1891 --
1892 -- Now transact the data we have
1893 --
1894
1895 --
1896 -- there is no chance of unique key constraint violation
1897 -- due to the following updates
1898 --
1899 UPDATE PON_BID_HEADERS
1900 SET LAST_UPDATE_DATE = SYSDATE,
1901 LAST_UPDATED_BY = -1,
1902 SURROG_BID_CREATED_CONTACT_ID = p_To_FK_id
1903 WHERE SURROG_BID_CREATED_CONTACT_ID = p_From_FK_id;
1904
1905 fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated SURROG_BID_CREATED_CONTACT_IDs in PON_BID_HEADERS: '||sql%rowcount||' rows');
1906
1907 --
1908 -- We will be updating the user_id column of pon_neg_team_members table
1909 -- if there are only one FND_USER associated with the merge from ot merge to party id.
1910 -- We will not touch the pon_neg_team_members otherwise.
1911 --
1912 fnd_file.put_line (fnd_file.log, '55 : Buyer Merge - Calling MERGE_NEG_TEAM_FND_USER procedure');
1913
1914 MERGE_NEG_TEAM_FND_USER (p_From_FK_id => p_From_FK_id,
1915 p_To_FK_id => p_To_FK_id);
1916
1917
1918 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
1919 --
1920 -- Nothing to do
1921 --
1922 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
1923 NULL;
1924 END IF;
1925
1926 EXCEPTION
1927 WHEN OTHERS THEN
1928
1929
1930 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.RES_SURROG_MERGE SQLERRM:'||SQLERRM);
1931 --
1932 -- We need add some Sourcing specific message for this
1933 --
1934 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1935 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1936 FND_MSG_PUB.ADD;
1937
1938 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1939
1940 --} end of procedure
1941 END RES_SURROG_MERGE;
1942
1943 -- Start of comments
1944 -- API name : RES_SCORE_MERGE
1945 --
1946 -- Type : Group
1947 --
1948 -- Pre-reqs : Responses with the given score_override_tp_contact_id
1949 -- (p_From_FK_id) must exists in the database
1950 --
1951 -- Function : This procedure will be attached to the SCORE_OVERRIDE_TP_CONTACT_ID
1952 -- merge for Sourcing PON_BID_HEADERS entity.
1953 --
1954 -- It will veto Party Merge if -
1955 -- p_From_FK_id and p_To_FK_id both are not Buyer user party
1956 --
1957 -- This will merge the all the PON_BID_HEADERS records
1958 -- having SCORE_OVERRIDE_TP_CONTACT_ID equals to p_From_FK_id
1959 -- to party id having value (p_To_FK_id)
1960 --
1961 -- Parameters:
1962 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
1963 -- TCA Merge Dictionary that is going to merge
1964 --
1965 -- IN : p_from_id NUMBER Required, the value of PK of the record
1966 -- which is going to be merged
1967 --
1968 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
1969 -- to which the p_from_id party is going to be merged
1970 --
1971 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
1972 -- Party Site, etc.) when merge is executed
1973 --
1974 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
1975 -- Party Site, etc.) when merge is executed
1976 --
1977 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
1978 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
1979 --
1980 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
1981 -- Party Merge is executed
1982 --
1983 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
1984 -- for which the Party Merge is executed
1985 --
1986 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
1987 -- was successful or not; It can have the following values -
1988 --
1989 -- FND_API.G_RET_STS_SUCCESS (Success)
1990 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
1991 -- failed due to Unexpected error)
1992 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
1993 -- vetod the Party Merge)
1994 --
1995 -- Version : Current version 1.0
1996 -- Previous version 1.0
1997 -- Initial version 1.0
1998 --
1999 -- End of comments
2000 PROCEDURE RES_SCORE_MERGE (
2001 p_Entity_name IN VARCHAR2,
2002 p_from_id IN NUMBER,
2003 x_to_id IN OUT NOCOPY NUMBER ,
2004 p_From_FK_id IN NUMBER,
2005 p_To_FK_id IN NUMBER,
2006 p_Parent_Entity_name IN VARCHAR2,
2007 p_batch_id IN NUMBER,
2008 p_Batch_Party_id IN NUMBER,
2009 x_return_status IN OUT NOCOPY VARCHAR2 )
2010 IS
2011 l_merge_scenario VARCHAR2(20);
2012
2013 BEGIN
2014 --{start of procedure
2015
2016 x_return_status := FND_API.G_RET_STS_SUCCESS;
2017
2018 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.RES_SCORE_MERGE ');
2022 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
2019 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
2020 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
2021 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
2023 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
2024 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
2025 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
2026 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
2027
2028 --
2029 -- check the merge scenario
2030 --
2031 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
2032 p_from_id => p_From_FK_id,
2033 p_to_id => p_To_FK_id);
2034
2035 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
2036
2037 IF (l_merge_scenario = G_SELLER OR l_merge_scenario = G_INCOMPATIBLE) THEN
2038
2039 fnd_file.put_line (fnd_file.log, '20 : Seller Merge - Error');
2040
2041 --
2042 -- This can not happen in any normal scenario as
2043 -- PON_BID_HEADERS.SCORE_OVERRIDE_TP_CONTACT_ID
2044 -- can never contain a seller user id. Thus, raising a veto though
2045 -- it will possibly never be called
2046 --
2047 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
2048 FND_MSG_PUB.ADD;
2049 x_return_status := FND_API.G_RET_STS_ERROR;
2050
2051 ELSIF (l_merge_scenario = G_BUYER) THEN
2052
2053 fnd_file.put_line (fnd_file.log, '30 : Buyer Merge ');
2054
2055 --
2056 -- Now transact the data we have
2057 --
2058
2059 --
2060 -- there is no chance of unique key constraint violation
2061 -- due to the following updates
2062 --
2063 UPDATE PON_BID_HEADERS
2064 SET LAST_UPDATE_DATE = SYSDATE,
2065 LAST_UPDATED_BY = -1,
2066 SCORE_OVERRIDE_TP_CONTACT_ID = p_To_FK_id
2067 WHERE SCORE_OVERRIDE_TP_CONTACT_ID = p_From_FK_id;
2068
2069 fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated SCORE_OVERRIDE_TP_CONTACT_IDs in PON_BID_HEADERS: '||sql%rowcount||' rows');
2070
2071 --
2072 -- We will be updating the user_id column of pon_neg_team_members table
2073 -- if there are only one FND_USER associated with the merge from ot merge to party id.
2074 -- We will not touch the pon_neg_team_members otherwise.
2075 --
2076 fnd_file.put_line (fnd_file.log, '55 : Buyer Merge - Calling MERGE_NEG_TEAM_FND_USER procedure');
2077
2078 MERGE_NEG_TEAM_FND_USER (p_From_FK_id => p_From_FK_id,
2079 p_To_FK_id => p_To_FK_id);
2080
2081 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
2082 --
2083 -- Nothing to do
2084 --
2085 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
2086 NULL;
2087 END IF;
2088
2089 EXCEPTION
2090 WHEN OTHERS THEN
2091
2092 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.RES_SCORE_MERGE SQLERRM:'||SQLERRM);
2093 --
2094 -- We need add some Sourcing specific message for this
2095 --
2096 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2097 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2098 FND_MSG_PUB.ADD;
2099
2100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2101
2102 --} end of procedure
2103 END RES_SCORE_MERGE;
2104
2105
2106 -- Start of comments
2107 -- API name : RES_SHRT_LIST_MERGE
2108 --
2109 -- Type : Group
2110 --
2111 -- Pre-reqs : Responses with the given shortlist_tpc_id
2112 -- (p_From_FK_id) must exists in the database
2113 --
2114 -- Function : This procedure will be attached to the SHORTLIST_TPC_ID
2115 -- merge for Sourcing PON_BID_HEADERS entity.
2116 --
2117 -- It will veto Party Merge if -
2118 -- p_From_FK_id and p_To_FK_id both are not Buyer user party
2119 --
2120 -- This will merge the all the PON_BID_HEADERS records
2121 -- having SHORTLIST_TPC_ID equals to p_From_FK_id
2122 -- to party id having value (p_To_FK_id)
2123 --
2124 -- Parameters:
2125 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
2126 -- TCA Merge Dictionary that is going to merge
2127 --
2128 -- IN : p_from_id NUMBER Required, the value of PK of the record
2129 -- which is going to be merged
2130 --
2131 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
2132 -- to which the p_from_id party is going to be merged
2133 --
2134 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
2138 -- Party Site, etc.) when merge is executed
2135 -- Party Site, etc.) when merge is executed
2136 --
2137 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
2139 --
2140 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
2141 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
2142 --
2143 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
2144 -- Party Merge is executed
2145 --
2146 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
2147 -- for which the Party Merge is executed
2148 --
2149 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
2150 -- was successful or not; It can have the following values -
2151 --
2152 -- FND_API.G_RET_STS_SUCCESS (Success)
2153 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
2154 -- failed due to Unexpected error)
2155 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
2156 -- vetod the Party Merge)
2157 --
2158 -- Version : Current version 1.0
2159 -- Previous version 1.0
2160 -- Initial version 1.0
2161 --
2162 -- End of comments
2163 PROCEDURE RES_SHRT_LIST_MERGE (
2164 p_Entity_name IN VARCHAR2,
2165 p_from_id IN NUMBER,
2166 x_to_id IN OUT NOCOPY NUMBER ,
2167 p_From_FK_id IN NUMBER,
2168 p_To_FK_id IN NUMBER,
2169 p_Parent_Entity_name IN VARCHAR2,
2170 p_batch_id IN NUMBER,
2171 p_Batch_Party_id IN NUMBER,
2172 x_return_status IN OUT NOCOPY VARCHAR2 )
2173 IS
2174 l_merge_scenario VARCHAR2(20);
2175
2176 BEGIN
2177 --{start of procedure
2178
2179 x_return_status := FND_API.G_RET_STS_SUCCESS;
2180
2181 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.RES_SHRT_LIST_MERGE ');
2182 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
2183 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
2184 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
2185 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
2186 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
2187 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
2188 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
2189 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
2190
2191 --
2192 -- check the merge scenario
2193 --
2194 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
2195 p_from_id => p_From_FK_id,
2196 p_to_id => p_To_FK_id);
2197
2198 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
2199
2200 IF (l_merge_scenario = G_SELLER OR l_merge_scenario = G_INCOMPATIBLE) THEN
2201
2202 fnd_file.put_line (fnd_file.log, '20 : Seller Merge - Error');
2203 --
2204 -- This can not happen in any normal scenario as
2205 -- PON_BID_HEADERS.SHORTLIST_TPC_ID
2206 -- can never contain a seller user id. Thus, raising a veto though
2207 -- it will possibly never be called
2208 --
2209 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
2210 FND_MSG_PUB.ADD;
2211 x_return_status := FND_API.G_RET_STS_ERROR;
2212
2213 ELSIF (l_merge_scenario = G_BUYER) THEN
2214 fnd_file.put_line (fnd_file.log, '30 : Buyer Merge ');
2215 --
2216 -- Now transact the data we have
2217 --
2218
2219 --
2220 -- there is no chance of unique key constraint violation
2221 -- due to the following updates
2222 --
2223 UPDATE PON_BID_HEADERS
2224 SET LAST_UPDATE_DATE = SYSDATE,
2225 LAST_UPDATED_BY = -1,
2226 SHORTLIST_TPC_ID = p_To_FK_id
2227 WHERE SHORTLIST_TPC_ID = p_From_FK_id;
2228
2229 fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated SHORTLIST_TPC_IDs in PON_BID_HEADERS: '||sql%rowcount||' rows');
2230
2231 --
2232 -- We will be updating the user_id column of pon_neg_team_members table
2233 -- if there are only one FND_USER associated with the merge from ot merge to party id.
2234 -- We will not touch the pon_neg_team_members otherwise.
2235 --
2236 fnd_file.put_line (fnd_file.log, '55 : Buyer Merge - Calling MERGE_NEG_TEAM_FND_USER procedure');
2237
2238 MERGE_NEG_TEAM_FND_USER (p_From_FK_id => p_From_FK_id,
2239 p_To_FK_id => p_To_FK_id);
2240
2241 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
2242 --
2243 -- Nothing to do
2244 --
2245 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
2246 NULL;
2247 END IF;
2248
2249 EXCEPTION
2250 WHEN OTHERS THEN
2251
2252 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.RES_SHRT_LIST_MERGE SQLERRM:'||SQLERRM);
2253 --
2254 -- We need add some Sourcing specific message for this
2255 --
2256 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2257 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2258 FND_MSG_PUB.ADD;
2259
2260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2261
2262 --} end of procedure
2263 END RES_SHRT_LIST_MERGE;
2264
2265 -- Start of comments
2266 -- API name : NEG_CONTRCT_MERGE
2267 --
2268 -- Type : Group
2269 --
2270 -- Pre-reqs : Negotitions with the given authoring_party_contact_id
2271 -- (p_From_FK_id) must exists in the database
2272 --
2273 -- Function : This procedure will be attached to the AUTHORING_PARTY_CONTACT_ID
2274 -- merge for Sourcing PON_CONTRACTS entity.
2275 --
2276 -- It will veto Party Merge if -
2277 -- p_From_FK_id and p_To_FK_id both are not Buyer user party
2278 --
2279 -- This will merge the all the PON_CONTRACTS records
2280 -- having AUTHORING_PARTY_CONTACT_ID equals to p_From_FK_id
2281 -- to party id having value (p_To_FK_id)
2282 --
2283 -- Parameters:
2284 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
2285 -- TCA Merge Dictionary that is going to merge
2286 --
2287 -- IN : p_from_id NUMBER Required, the value of PK of the record
2288 -- which is going to be merged
2289 --
2290 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
2291 -- to which the p_from_id party is going to be merged
2292 --
2293 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
2294 -- Party Site, etc.) when merge is executed
2295 --
2296 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
2297 -- Party Site, etc.) when merge is executed
2298 --
2299 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
2300 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
2301 --
2302 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
2303 -- Party Merge is executed
2304 --
2305 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
2306 -- for which the Party Merge is executed
2307 --
2308 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
2309 -- was successful or not; It can have the following values -
2310 --
2311 -- FND_API.G_RET_STS_SUCCESS (Success)
2312 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
2313 -- failed due to Unexpected error)
2314 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
2315 -- vetod the Party Merge)
2316 --
2317 -- Version : Current version 1.0
2318 -- Previous version 1.0
2319 -- Initial version 1.0
2320 --
2321 -- End of comments
2322 PROCEDURE NEG_CONTRCT_MERGE (
2323 p_Entity_name IN VARCHAR2,
2324 p_from_id IN NUMBER,
2325 x_to_id IN OUT NOCOPY NUMBER ,
2326 p_From_FK_id IN NUMBER,
2327 p_To_FK_id IN NUMBER,
2328 p_Parent_Entity_name IN VARCHAR2,
2329 p_batch_id IN NUMBER,
2330 p_Batch_Party_id IN NUMBER,
2331 x_return_status IN OUT NOCOPY VARCHAR2 )
2332 IS
2333 l_merge_scenario VARCHAR2(20);
2334
2335 BEGIN
2336 --{start of procedure
2337
2338 x_return_status := FND_API.G_RET_STS_SUCCESS;
2339
2340 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.NEG_CONTRCT_MERGE ');
2341 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
2342 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
2343 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
2344 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
2345 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
2346 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
2347 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
2348 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
2349
2350 --
2351 -- check the merge scenario
2352 --
2353 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
2354 p_from_id => p_From_FK_id,
2355 p_to_id => p_To_FK_id);
2356
2357 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
2358
2359 IF (l_merge_scenario = G_SELLER OR l_merge_scenario = G_INCOMPATIBLE) THEN
2360
2361 fnd_file.put_line (fnd_file.log, '20 : Seller Merge - Error');
2362 --
2363 -- This can not happen in any normal scenario as
2364 -- PON_CONTRACTS.AUTHORING_PARTY_CONTACT_ID
2365 -- can never contain a seller user id. Thus, raising a veto though
2366 -- it will possibly never be called
2367 --
2368 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
2369 FND_MSG_PUB.ADD;
2370 x_return_status := FND_API.G_RET_STS_ERROR;
2371
2372 ELSIF (l_merge_scenario = G_BUYER) THEN
2373
2374 fnd_file.put_line (fnd_file.log, '30 : Buyer Merge ');
2375 --
2376 -- Now transact the data we have
2377 --
2378
2379 --
2380 -- there is no chance of unique key constraint violation
2381 -- due to the following updates
2382 --
2383 UPDATE PON_CONTRACTS
2384 SET LAST_UPDATE_DATE = SYSDATE,
2385 LAST_UPDATED_BY = -1,
2386 AUTHORING_PARTY_CONTACT_ID = p_To_FK_id
2387 WHERE AUTHORING_PARTY_CONTACT_ID = p_From_FK_id;
2388
2389 fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated AUTHORING_PARTY_CONTACT_IDs in PON_CONTRACTS: '||sql%rowcount||' rows');
2390
2391 --
2392 -- We will be updating the user_id column of pon_neg_team_members table
2393 -- if there are only one FND_USER associated with the merge from ot merge to party id.
2394 -- We will not touch the pon_neg_team_members otherwise.
2395 --
2396 fnd_file.put_line (fnd_file.log, '55 : Buyer Merge - Calling MERGE_NEG_TEAM_FND_USER procedure');
2397
2398 MERGE_NEG_TEAM_FND_USER (p_From_FK_id => p_From_FK_id,
2399 p_To_FK_id => p_To_FK_id);
2400
2401 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
2402 --
2403 -- Nothing to do
2404 --
2405 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
2406 NULL;
2407 END IF;
2408
2409 EXCEPTION
2410 WHEN OTHERS THEN
2411
2412 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.NEG_CONTRCT_MERGE SQLERRM:'||SQLERRM);
2413 --
2414 -- We need add some Sourcing specific message for this
2415 --
2416 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2417 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2418 FND_MSG_PUB.ADD;
2419
2420 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2421
2422 --} end of procedure
2423 END NEG_CONTRCT_MERGE;
2424
2425
2426 -- Start of comments
2427 -- API name : NEG_DISC_THR_MERGE
2428 --
2429 -- Type : Group
2430 --
2431 -- Pre-reqs : Discussion Messages with the given owner_party_id
2432 -- (p_From_FK_id) must exists in the database
2433 --
2434 -- Function : This procedure will be attached to the OWNER_PARTY_ID
2435 -- merge for Sourcing PON_THREADS entity.
2436 --
2437 -- It will veto Party Merge if -
2438 -- p_From_FK_id and p_To_FK_id both are not Buyer user party
2439 -- OR
2440 -- p_From_FK_id and p_To_FK_id both are not Seller user party
2441 --
2442 -- This will merge the all the PON_THREADS records
2443 -- having OWNER_PARTY_ID equals to p_From_FK_id
2444 -- to party id having value (p_To_FK_id)
2445 --
2446 -- Parameters:
2447 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
2448 -- TCA Merge Dictionary that is going to merge
2449 --
2450 -- IN : p_from_id NUMBER Required, the value of PK of the record
2451 -- which is going to be merged
2452 --
2453 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
2454 -- to which the p_from_id party is going to be merged
2455 --
2456 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
2457 -- Party Site, etc.) when merge is executed
2458 --
2459 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
2460 -- Party Site, etc.) when merge is executed
2461 --
2462 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
2463 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
2464 --
2465 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
2466 -- Party Merge is executed
2467 --
2468 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
2469 -- for which the Party Merge is executed
2470 --
2471 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
2472 -- was successful or not; It can have the following values -
2473 --
2474 -- FND_API.G_RET_STS_SUCCESS (Success)
2475 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
2476 -- failed due to Unexpected error)
2477 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
2478 -- vetod the Party Merge)
2479 --
2480 -- Version : Current version 1.0
2481 -- Previous version 1.0
2482 -- Initial version 1.0
2483 --
2484 -- End of comments
2485 PROCEDURE NEG_DISC_THR_MERGE (
2486 p_Entity_name IN VARCHAR2,
2487 p_from_id IN NUMBER,
2488 x_to_id IN OUT NOCOPY NUMBER ,
2489 p_From_FK_id IN NUMBER,
2490 p_To_FK_id IN NUMBER,
2491 p_Parent_Entity_name IN VARCHAR2,
2492 p_batch_id IN NUMBER,
2493 p_Batch_Party_id IN NUMBER,
2494 x_return_status IN OUT NOCOPY VARCHAR2 )
2495 IS
2496 l_merge_scenario VARCHAR2(20);
2497
2498 BEGIN
2499 --{start of procedure
2500
2501 x_return_status := FND_API.G_RET_STS_SUCCESS;
2502
2503 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.NEG_DISC_THR_MERGE ');
2504 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
2505 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
2506 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
2507 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
2508 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
2509 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
2510 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
2511 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
2512
2513 --
2514 -- check the merge scenario
2515 --
2516 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
2517 p_from_id => p_From_FK_id,
2518 p_to_id => p_To_FK_id);
2519
2523
2520 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
2521
2522 IF (l_merge_scenario = G_INCOMPATIBLE) THEN
2524 fnd_file.put_line (fnd_file.log, '20 : Incompatible Merge - Error');
2525 --
2526 -- This can not be passed. Thus, raising a veto.
2527 --
2528 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
2529 FND_MSG_PUB.ADD;
2530 x_return_status := FND_API.G_RET_STS_ERROR;
2531
2532 ELSIF (l_merge_scenario = G_SELLER OR l_merge_scenario = G_BUYER) THEN
2533
2534 fnd_file.put_line (fnd_file.log, '30 : Buyer or Seller Merge ');
2535 --
2536 -- there is no chance of unique key constraint violation
2537 -- due to the following updates
2538 --
2539
2540 UPDATE PON_THREADS
2541 SET LAST_UPDATE_DATE = SYSDATE,
2542 OWNER_PARTY_ID = p_To_FK_id
2543 WHERE OWNER_PARTY_ID = p_From_FK_id;
2544
2545 fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated OWNER_PARTY_IDs in PON_THREADS: '||sql%rowcount||' rows');
2546
2547 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
2548 --
2549 -- Nothing to do
2550 --
2551 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
2552 NULL;
2553 END IF;
2554
2555 EXCEPTION
2556 WHEN OTHERS THEN
2557
2558 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.NEG_DISC_THR_MERGE SQLERRM:'||SQLERRM);
2559 --
2560 -- We need add some Sourcing specific message for this
2561 --
2562 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2563 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2564 FND_MSG_PUB.ADD;
2565
2566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2567
2568 --} end of procedure
2569 END NEG_DISC_THR_MERGE;
2570
2571 -- Start of comments
2572 -- API name : NEG_DISC_THR_ENTRY_MERGE
2573 --
2574 -- Type : Group
2575 --
2576 -- Pre-reqs : Discussion Message Entries with the given from_id
2577 -- (p_From_FK_id) must exists in the database
2578 --
2579 -- Function : This procedure will be attached to the FROM_ID
2580 -- merge for Sourcing PON_THREAD_ENTRIES entity.
2581 --
2582 -- It will veto Party Merge if -
2583 -- p_From_FK_id and p_To_FK_id both are not Buyer user party
2584 -- OR
2585 -- p_From_FK_id and p_To_FK_id both are not Seller user party
2586 --
2587 -- This will merge the all the PON_THREAD_ENTRIES records
2588 -- having FROM_ID equals to p_From_FK_id
2589 -- to party id having value (p_To_FK_id)
2590 --
2591 -- Parameters:
2592 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
2593 -- TCA Merge Dictionary that is going to merge
2594 --
2595 -- IN : p_from_id NUMBER Required, the value of PK of the record
2596 -- which is going to be merged
2597 --
2598 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
2599 -- to which the p_from_id party is going to be merged
2600 --
2601 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
2602 -- Party Site, etc.) when merge is executed
2603 --
2604 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
2605 -- Party Site, etc.) when merge is executed
2606 --
2607 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
2608 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
2609 --
2610 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
2611 -- Party Merge is executed
2612 --
2613 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
2614 -- for which the Party Merge is executed
2615 --
2616 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
2617 -- was successful or not; It can have the following values -
2618 --
2619 -- FND_API.G_RET_STS_SUCCESS (Success)
2620 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
2621 -- failed due to Unexpected error)
2622 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
2623 -- vetod the Party Merge)
2624 --
2625 -- Version : Current version 1.0
2626 -- Previous version 1.0
2627 -- Initial version 1.0
2628 --
2629 -- End of comments
2630 PROCEDURE NEG_DISC_THR_ENTRY_MERGE (
2631 p_Entity_name IN VARCHAR2,
2632 p_from_id IN NUMBER,
2633 x_to_id IN OUT NOCOPY NUMBER ,
2634 p_From_FK_id IN NUMBER,
2635 p_To_FK_id IN NUMBER,
2636 p_Parent_Entity_name IN VARCHAR2,
2637 p_batch_id IN NUMBER,
2638 p_Batch_Party_id IN NUMBER,
2639 x_return_status IN OUT NOCOPY VARCHAR2 )
2640 IS
2641 l_merge_scenario VARCHAR2(20);
2642 l_first_name HZ_PARTIES.PERSON_FIRST_NAME%TYPE;
2643 l_last_name HZ_PARTIES.PERSON_LAST_NAME%TYPE;
2644
2645 BEGIN
2646 --{start of procedure
2647
2648 x_return_status := FND_API.G_RET_STS_SUCCESS;
2649
2650 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.NEG_DISC_THR_ENTRY_MERGE ');
2651 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
2652 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
2653 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
2654 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
2655 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
2656 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
2657 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
2658 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
2659
2660 --
2661 -- check the merge scenario
2662 --
2663 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
2664 p_from_id => p_From_FK_id,
2665 p_to_id => p_To_FK_id);
2666
2667 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
2668
2669 IF (l_merge_scenario = G_INCOMPATIBLE) THEN
2670
2671 fnd_file.put_line (fnd_file.log, '20 : Incompatible Merge - Error');
2672
2673 --
2674 -- This can not be passed. Thus, raising a veto.
2675 --
2676 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
2677 FND_MSG_PUB.ADD;
2678 x_return_status := FND_API.G_RET_STS_ERROR;
2679
2680 ELSIF (l_merge_scenario = G_SELLER OR l_merge_scenario = G_BUYER) THEN
2681
2682 fnd_file.put_line (fnd_file.log, '30 : Buyer or Seller Merge - Getting Person Name from HZ');
2683
2684 SELECT
2685 hz.PERSON_FIRST_NAME,
2686 hz.PERSON_LAST_NAME
2687 INTO
2688 l_first_name,
2689 l_last_name
2690 FROM HZ_PARTIES hz
2691 WHERE hz.PARTY_ID= p_To_FK_id;
2692
2693 fnd_file.put_line (fnd_file.log, '40 : Buyer or Seller Merge - Person Name from HZ:'|| l_first_name||','||l_last_name);
2694
2695 --
2696 -- there is no chance of unique key constraint violation
2697 -- due to the following updates
2698 --
2699
2700 --
2701 -- NOTE: There is no WHO columns in this table
2702 --
2703 UPDATE PON_THREAD_ENTRIES
2704 SET FROM_ID = p_To_FK_id,
2705 FROM_FIRST_NAME= l_first_name,
2706 FROM_LAST_NAME=l_last_name
2707 WHERE FROM_ID = p_From_FK_id;
2708
2709 fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated FROM_IDs in PON_THREAD_ENTRIES: '||sql%rowcount||' rows');
2710
2711 --
2712 -- We will be updating the user_id column of pon_neg_team_members table
2713 -- if there are only one FND_USER associated with the merge from ot merge to party id.
2714 -- We will not touch the pon_neg_team_members otherwise.
2715 --
2716 fnd_file.put_line (fnd_file.log, '55 : Buyer Merge - Calling MERGE_NEG_TEAM_FND_USER procedure');
2717
2718 IF ( l_merge_scenario = G_BUYER) THEN
2719 MERGE_NEG_TEAM_FND_USER (p_From_FK_id => p_From_FK_id,
2720 p_To_FK_id => p_To_FK_id);
2721 END IF;
2722
2723
2724 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
2725 --
2726 -- Nothing to do
2727 --
2728 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
2729 NULL;
2730 END IF;
2731
2732 EXCEPTION
2733 WHEN OTHERS THEN
2734
2735 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.NEG_DISC_THR_ENTRY_MERGE SQLERRM:'||SQLERRM);
2736 --
2737 -- We need add some Sourcing specific message for this
2738 --
2739 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2740 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2741 FND_MSG_PUB.ADD;
2742
2743 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2744
2745 --} end of procedure
2746 END NEG_DISC_THR_ENTRY_MERGE;
2747
2748
2749 -- Start of comments
2750 -- API name : NEG_COMP_MERGE
2751 --
2752 -- Type : Group
2753 --
2754 -- Pre-reqs : Trading Partner ID entries with the given from_company_id
2755 -- (p_From_FK_id) must exists in the database
2756 --
2757 -- Function : This procedure will be attached to the TRADING_PARTNER_ID (p_From_FK_id)
2758 -- merge for different Sourcing entities (like PON_THREAD_ENTRIES etc.).
2759 --
2760 -- It will veto Party Merge if -
2761 -- p_From_FK_id or p_To_FK_id is/are Buyer company party
2762 -- OR
2763 -- p_From_FK_id is Seller but p_To_FK_id is not Buyer or Seller party
2764 -- OR
2765 -- p_To_FK_id is Seller but p_From_FK_id is not Buyer or Seller party
2766 --
2767 -- This will check the merge possibility for the entities
2768 -- having TRADING_PARTNER_ID equals to p_From_FK_id
2769 -- to TRADING_PARTNER_ID having value (p_To_FK_id). This will raise veto
2770 -- if the merge is not possible.
2771 --
2772 -- Parameters:
2773 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
2774 -- TCA Merge Dictionary that is going to merge
2775 --
2776 -- IN : p_from_id NUMBER Required, the value of PK of the record
2777 -- which is going to be merged
2778 --
2779 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
2780 -- to which the p_from_id party is going to be merged
2781 --
2782 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
2783 -- Party Site, etc.) when merge is executed
2784 --
2785 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
2786 -- Party Site, etc.) when merge is executed
2787 --
2788 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
2789 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
2790 --
2791 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
2792 -- Party Merge is executed
2793 --
2794 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
2795 -- for which the Party Merge is executed
2796 --
2797 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
2798 -- was successful or not; It can have the following values -
2799 --
2800 -- FND_API.G_RET_STS_SUCCESS (Success)
2801 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
2802 -- failed due to Unexpected error)
2803 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
2804 -- vetod the Party Merge)
2805 --
2806 -- Version : Current version 1.0
2807 -- Previous version 1.0
2808 -- Initial version 1.0
2809 --
2810 -- End of comments
2811 PROCEDURE NEG_COMP_MERGE (
2812 p_Entity_name IN VARCHAR2,
2813 p_from_id IN NUMBER,
2814 x_to_id IN OUT NOCOPY NUMBER ,
2815 p_From_FK_id IN NUMBER,
2816 p_To_FK_id IN NUMBER,
2817 p_Parent_Entity_name IN VARCHAR2,
2818 p_batch_id IN NUMBER,
2819 p_Batch_Party_id IN NUMBER,
2820 x_return_status IN OUT NOCOPY VARCHAR2 )
2821 IS
2822 l_merge_scenario VARCHAR2(20);
2823 l_first_name HZ_PARTIES.PERSON_FIRST_NAME%TYPE;
2824 l_last_name HZ_PARTIES.PERSON_LAST_NAME%TYPE;
2825
2826 BEGIN
2827 --{start of procedure
2828
2829 x_return_status := FND_API.G_RET_STS_SUCCESS;
2830
2831 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.NEG_COMP_MERGE ');
2832 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
2833 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
2834 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
2835 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
2836 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
2837 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
2838 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
2839 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
2840
2841 --
2842 -- check the merge scenario
2843 --
2844 l_merge_scenario := CHECK_COMPANY_PARTY_MERGE_TYPE (
2845 p_from_id => p_From_FK_id,
2846 p_to_id => p_To_FK_id);
2847
2848 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
2849
2850 IF (l_merge_scenario = G_INCOMPATIBLE) THEN
2851
2852 fnd_file.put_line (fnd_file.log, '20 : Incompatible Merge - Error');
2853 --
2854 -- This can not be passed. Thus, raising a veto.
2855 --
2856 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
2857 FND_MSG_PUB.ADD;
2858 x_return_status := FND_API.G_RET_STS_ERROR;
2859
2860 ELSIF (l_merge_scenario = G_SELLER OR l_merge_scenario = G_IRRELEVANT) THEN
2861 --
2862 -- Case 1. Both are Seller Companies - In that case the vendor merge routine
2863 -- must have been executed (ASSUMPTION) and AP will veto merge
2867 -- we dont care about the merge
2864 -- otherwise. Hence, we have nothing to do here.
2865 --
2866 -- Case 2. Both are Non Buyer/Seller Companies - In that case
2868 --
2869 fnd_file.put_line (fnd_file.log, '30 : Buyer or Seller Merge - Ignore');
2870 NULL;
2871 END IF;
2872
2873 EXCEPTION
2874 WHEN OTHERS THEN
2875
2876 fnd_file.put_line (fnd_file.log, '40 : Error in PON_TCA_PARTY_MERGE.NEG_COMP_MERGE SQLERRM:'||SQLERRM);
2877 --
2878 -- We need add some Sourcing specific message for this
2879 --
2880 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2881 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2882 FND_MSG_PUB.ADD;
2883
2884 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2885
2886 --} end of procedure
2887 END NEG_COMP_MERGE;
2888
2889 -- Start of comments
2890 -- API name : NEG_DISC_TE_RCP_MERGE
2891 --
2892 -- Type : Group
2893 --
2894 -- Pre-reqs : Discussion Message Thread Entries with the given to_id
2895 -- (p_From_FK_id) must exists in the database
2896 --
2897 -- Function : This procedure will be attached to the TO_ID
2898 -- merge for Sourcing PON_TE_RECIPIENTS entity.
2899 --
2900 -- It will veto Party Merge if -
2901 -- p_From_FK_id and p_To_FK_id both are not Buyer user party
2902 -- OR
2903 -- p_From_FK_id and p_To_FK_id both are not Seller user party
2904 --
2905 -- This will merge the all the PON_TE_RECIPIENTS records
2906 -- having TO_ID equals to p_From_FK_id
2907 -- to party id having value (p_To_FK_id)
2908 --
2909 -- Parameters:
2910 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
2911 -- TCA Merge Dictionary that is going to merge
2912 --
2913 -- IN : p_from_id NUMBER Required, the value of PK of the record
2914 -- which is going to be merged
2915 --
2916 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
2917 -- to which the p_from_id party is going to be merged
2918 --
2919 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
2920 -- Party Site, etc.) when merge is executed
2921 --
2922 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
2923 -- Party Site, etc.) when merge is executed
2924 --
2925 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
2926 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
2927 --
2928 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
2929 -- Party Merge is executed
2930 --
2931 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
2932 -- for which the Party Merge is executed
2933 --
2934 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
2935 -- was successful or not; It can have the following values -
2936 --
2937 -- FND_API.G_RET_STS_SUCCESS (Success)
2938 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
2939 -- failed due to Unexpected error)
2940 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
2941 -- vetod the Party Merge)
2942 --
2943 -- Version : Current version 1.0
2944 -- Previous version 1.0
2945 -- Initial version 1.0
2946 --
2947 -- End of comments
2948 PROCEDURE NEG_DISC_TE_RCP_MERGE (
2949 p_Entity_name IN VARCHAR2,
2950 p_from_id IN NUMBER,
2951 x_to_id IN OUT NOCOPY NUMBER ,
2952 p_From_FK_id IN NUMBER,
2953 p_To_FK_id IN NUMBER,
2954 p_Parent_Entity_name IN VARCHAR2,
2955 p_batch_id IN NUMBER,
2956 p_Batch_Party_id IN NUMBER,
2957 x_return_status IN OUT NOCOPY VARCHAR2 )
2958 IS
2959 l_merge_scenario VARCHAR2(20);
2960 l_first_name HZ_PARTIES.PERSON_FIRST_NAME%TYPE;
2961 l_last_name HZ_PARTIES.PERSON_LAST_NAME%TYPE;
2962
2963 BEGIN
2964 --{start of procedure
2965
2966 x_return_status := FND_API.G_RET_STS_SUCCESS;
2967
2968 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.NEG_DISC_TE_RCP_MERGE ');
2969 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
2970 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
2971 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
2972 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
2973 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
2974 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
2978 --
2975 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
2976 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
2977
2979 -- check the merge scenario
2980 --
2981 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
2982 p_from_id => p_From_FK_id,
2983 p_to_id => p_To_FK_id);
2984
2985 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
2986
2987 IF (l_merge_scenario = G_INCOMPATIBLE) THEN
2988
2989 fnd_file.put_line (fnd_file.log, '20 : Incompatible Merge - Error');
2990 --
2991 -- This can not be passed. Thus, raising a veto.
2992 --
2993 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
2994 FND_MSG_PUB.ADD;
2995 x_return_status := FND_API.G_RET_STS_ERROR;
2996
2997 ELSIF (l_merge_scenario = G_SELLER OR l_merge_scenario = G_BUYER) THEN
2998
2999 fnd_file.put_line (fnd_file.log, '30 : Buyer or Seller Merge - Getting Person Name from HZ');
3000
3001 SELECT
3002 hz.PERSON_FIRST_NAME,
3003 hz.PERSON_LAST_NAME
3004 INTO
3005 l_first_name,
3006 l_last_name
3007 FROM HZ_PARTIES hz
3008 WHERE hz.PARTY_ID= p_To_FK_id;
3009
3010 fnd_file.put_line (fnd_file.log, '40 : Buyer or Seller Merge - Person Name from HZ:'|| l_first_name||','||l_last_name);
3011
3012 --
3013 -- there is no chance of unique key constraint violation
3014 -- due to the following constraints. We will not update
3015 -- the to_id where a {entry_id, to_id} violation is possible
3016 --
3017
3018 --
3019 -- NOTE: There is no WHO columns in this table
3020 --
3021 UPDATE PON_TE_RECIPIENTS te
3022 SET te.TO_ID = p_To_FK_id,
3023 te.TO_FIRST_NAME = l_first_name,
3024 te.TO_LAST_NAME = l_last_name
3025 WHERE te.TO_ID = p_From_FK_id
3026 AND NOT EXISTS (SELECT 'duplicate'
3027 FROM PON_TE_RECIPIENTS te1
3028 WHERE te1.ENTRY_ID = te.ENTRY_ID
3029 AND te1.TO_ID = p_To_FK_id);
3030
3031 fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated TO_IDs in PON_TE_RECIPIENTS: '||sql%rowcount||' rows');
3032
3033 --
3034 -- We will be updating the user_id column of pon_neg_team_members table
3035 -- if there are only one FND_USER associated with the merge from ot merge to party id.
3036 -- We will not touch the pon_neg_team_members otherwise.
3037 --
3038 fnd_file.put_line (fnd_file.log, '55 : Buyer Merge - Calling MERGE_NEG_TEAM_FND_USER procedure');
3039
3040 MERGE_NEG_TEAM_FND_USER (p_From_FK_id => p_From_FK_id,
3044 --
3041 p_To_FK_id => p_To_FK_id);
3042
3043 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
3045 -- Nothing to do
3046 --
3047 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
3048 NULL;
3049 END IF;
3050
3051 EXCEPTION
3052 WHEN OTHERS THEN
3053
3054 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.NEG_DISC_TE_RCP_MERGE SQLERRM:'||SQLERRM);
3055 --
3056 -- We need add some Sourcing specific message for this
3057 --
3058 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3059 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3060 FND_MSG_PUB.ADD;
3061
3062 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3063
3064 --} end of procedure
3065 END NEG_DISC_TE_RCP_MERGE;
3066
3067
3068 -- API name : RES_SURR_ACK_MERGE
3069 --
3070 -- Type : Group
3071 --
3072 -- Pre-reqs : Responses with the given surrog_bid_ack_contact_id
3073 -- (p_From_FK_id) must exists in the database
3074 --
3075 -- Function : This procedure will be attached to the SURROG_BID_ACK_CONTACT_ID
3076 -- merge for Sourcing PON_ACKNOWLEDGEMENTS entity.
3077 --
3078 -- It will veto Party Merge if -
3079 -- p_From_FK_id and p_To_FK_id both are not Buyer user party
3080 --
3081 -- This will merge the all the PON_ACKNOWLEDGEMENTS records
3082 -- having SURROG_BID_ACK_CONTACT_ID equals to p_From_FK_id
3083 -- to party id having value (p_To_FK_id)
3084 --
3085 -- Parameters:
3086 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
3087 -- TCA Merge Dictionary that is going to merge
3088 --
3089 -- IN : p_from_id NUMBER Required, the value of PK of the record
3090 -- which is going to be merged
3091 --
3092 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
3093 -- to which the p_from_id party is going to be merged
3094 --
3095 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
3096 -- Party Site, etc.) when merge is executed
3097 --
3098 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
3099 -- Party Site, etc.) when merge is executed
3100 --
3101 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
3102 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
3103 --
3104 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
3105 -- Party Merge is executed
3106 --
3107 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
3108 -- for which the Party Merge is executed
3109 --
3110 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
3111 -- was successful or not; It can have the following values -
3112 --
3113 -- FND_API.G_RET_STS_SUCCESS (Success)
3114 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
3115 -- failed due to Unexpected error)
3116 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
3117 -- vetod the Party Merge)
3118 --
3119 -- Version : Current version 1.0
3120 -- Previous version 1.0
3121 -- Initial version 1.0
3122 --
3123 -- End of comments
3124 PROCEDURE RES_SURR_ACK_MERGE (
3125 p_Entity_name IN VARCHAR2,
3126 p_from_id IN NUMBER,
3127 x_to_id IN OUT NOCOPY NUMBER ,
3128 p_From_FK_id IN NUMBER,
3129 p_To_FK_id IN NUMBER,
3130 p_Parent_Entity_name IN VARCHAR2,
3131 p_batch_id IN NUMBER,
3132 p_Batch_Party_id IN NUMBER,
3133 x_return_status IN OUT NOCOPY VARCHAR2 )
3134 IS
3135 l_merge_scenario VARCHAR2(20);
3136
3137 BEGIN
3138 --{start of procedure
3139
3140 x_return_status := FND_API.G_RET_STS_SUCCESS;
3141
3142 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.RES_SURR_ACK_MERGE ');
3143 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
3144 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
3145 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
3146 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
3147 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
3148 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
3149 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
3150 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
3151
3152 --
3153 -- check the merge scenario
3154 --
3155 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
3159 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
3156 p_from_id => p_From_FK_id,
3157 p_to_id => p_To_FK_id);
3158
3160
3161 IF (l_merge_scenario = G_SELLER OR l_merge_scenario = G_INCOMPATIBLE) THEN
3162
3163 fnd_file.put_line (fnd_file.log, '20 : Incompatible or Seller Merge - Error');
3164
3165 --
3166 -- This can not happen in any normal scenario as
3167 -- PON_ACKNOWLEDGEMENTS.SURROG_BID_ACK_CONTACT_ID
3168 -- can never contain a seller user id. Thus, raising a veto though
3169 -- it will possibly never be called
3170 --
3171 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
3172 FND_MSG_PUB.ADD;
3173 x_return_status := FND_API.G_RET_STS_ERROR;
3174
3175 ELSIF (l_merge_scenario = G_BUYER) THEN
3176
3177 fnd_file.put_line (fnd_file.log, '30 : Buyer Merge ');
3178 --
3179 -- Now transact the data we have
3180 --
3181
3182 --
3183 -- there is no chance of unique key constraint violation
3184 -- due to the following updates
3185 --
3186 UPDATE PON_ACKNOWLEDGEMENTS
3187 SET LAST_UPDATE_DATE = SYSDATE,
3188 LAST_UPDATED_BY = -1,
3189 SURROG_BID_ACK_CONTACT_ID = p_To_FK_id
3190 WHERE SURROG_BID_ACK_CONTACT_ID = p_From_FK_id;
3191
3192 fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated SURROG_BID_ACK_CONTACT_IDs in PON_ACKNOWLEDGEMENTS: '||sql%rowcount||' rows');
3193
3194 --
3195 -- We will be updating the user_id column of pon_neg_team_members table
3196 -- if there are only one FND_USER associated with the merge from ot merge to party id.
3197 -- We will not touch the pon_neg_team_members otherwise.
3198 --
3199 fnd_file.put_line (fnd_file.log, '55 : Buyer Merge - Calling MERGE_NEG_TEAM_FND_USER procedure');
3200
3201 MERGE_NEG_TEAM_FND_USER (p_From_FK_id => p_From_FK_id,
3202 p_To_FK_id => p_To_FK_id);
3203
3204 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
3205 --
3206 -- Nothing to do
3207 --
3208 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
3209 NULL;
3210 END IF;
3211
3212 EXCEPTION
3213 WHEN OTHERS THEN
3214
3215 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.RES_SURR_ACK_MERGE SQLERRM:'||SQLERRM);
3216 --
3217 -- We need add some Sourcing specific message for this
3218 --
3219 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3220 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3221 FND_MSG_PUB.ADD;
3222
3223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3224
3225 --} end of procedure
3226 END RES_SURR_ACK_MERGE;
3227
3228 -- Start of comments
3229 -- API name : NEG_SUPP_ACC_MERGE
3230 --
3231 -- Type : Group
3232 --
3233 -- Pre-reqs : Supplier Access Lock entries with the given buyer_tp_contact_id
3234 -- (p_From_FK_id) must exists in the database
3235 --
3236 -- Function : This procedure will be attached to the BUYER_TP_CONTACT_ID
3237 -- merge for Sourcing PON_SUPPLIER_ACCESS entity.
3238 --
3239 -- It will veto Party Merge if -
3240 -- p_From_FK_id and p_To_FK_id both are not Buyer user party
3241 --
3242 -- This will merge the all the PON_SUPPLIER_ACCESS records
3243 -- having BUYER_TP_CONTACT_ID equals to p_From_FK_id
3244 -- to party id having value (p_To_FK_id)
3245 --
3246 -- Parameters:
3247 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
3248 -- TCA Merge Dictionary that is going to merge
3249 --
3250 -- IN : p_from_id NUMBER Required, the value of PK of the record
3251 -- which is going to be merged
3252 --
3253 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
3254 -- to which the p_from_id party is going to be merged
3255 --
3256 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
3257 -- Party Site, etc.) when merge is executed
3258 --
3259 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
3260 -- Party Site, etc.) when merge is executed
3261 --
3262 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
3263 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
3264 --
3265 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
3266 -- Party Merge is executed
3267 --
3268 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
3269 -- for which the Party Merge is executed
3270 --
3274 -- FND_API.G_RET_STS_SUCCESS (Success)
3271 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
3272 -- was successful or not; It can have the following values -
3273 --
3275 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
3276 -- failed due to Unexpected error)
3277 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
3278 -- vetod the Party Merge)
3279 --
3280 -- Version : Current version 1.0
3281 -- Previous version 1.0
3282 -- Initial version 1.0
3283 --
3284 -- End of comments
3285 PROCEDURE NEG_SUPP_ACC_MERGE (
3286 p_Entity_name IN VARCHAR2,
3287 p_from_id IN NUMBER,
3288 x_to_id IN OUT NOCOPY NUMBER ,
3289 p_From_FK_id IN NUMBER,
3290 p_To_FK_id IN NUMBER,
3291 p_Parent_Entity_name IN VARCHAR2,
3292 p_batch_id IN NUMBER,
3293 p_Batch_Party_id IN NUMBER,
3294 x_return_status IN OUT NOCOPY VARCHAR2 )
3295 IS
3296 l_merge_scenario VARCHAR2(20);
3297
3298 BEGIN
3299 --{start of procedure
3300
3301 x_return_status := FND_API.G_RET_STS_SUCCESS;
3302
3303 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.NEG_SUPP_ACC_MERGE ');
3304 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
3305 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
3306 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
3307 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
3308 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
3309 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
3310 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
3311 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
3312
3313 --
3314 -- check the merge scenario
3315 --
3316 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
3317 p_from_id => p_From_FK_id,
3318 p_to_id => p_To_FK_id);
3319
3320 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
3321
3322 IF (l_merge_scenario = G_SELLER OR l_merge_scenario = G_INCOMPATIBLE) THEN
3323
3324 fnd_file.put_line (fnd_file.log, '20 : Incompatible or Seller Merge - Error');
3325 --
3326 -- This can not happen in any normal scenario as
3327 -- PON_SUPPLIER_ACCESS.BUYER_TP_CONTACT_ID
3328 -- can never contain a seller user id. Thus, raising a veto though
3329 -- it will possibly never be called
3330 --
3331 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
3332 FND_MSG_PUB.ADD;
3333 x_return_status := FND_API.G_RET_STS_ERROR;
3334
3335 ELSIF (l_merge_scenario = G_BUYER) THEN
3336 fnd_file.put_line (fnd_file.log, '30 : Buyer Merge ');
3337 --
3338 -- Now transact the data we have
3339 --
3340
3341 --
3342 -- there is no chance of unique key constraint violation
3343 -- due to the following updates
3344 --
3345 UPDATE PON_SUPPLIER_ACCESS
3346 SET LAST_UPDATE_DATE = SYSDATE,
3347 LAST_UPDATED_BY = -1,
3348 BUYER_TP_CONTACT_ID = p_To_FK_id
3349 WHERE BUYER_TP_CONTACT_ID = p_From_FK_id;
3350
3351 fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated BUYER_TP_CONTACT_IDs in PON_SUPPLIER_ACCESS: '||sql%rowcount||' rows');
3352
3353 --
3354 -- We will be updating the user_id column of pon_neg_team_members table
3355 -- if there are only one FND_USER associated with the merge from ot merge to party id.
3356 -- We will not touch the pon_neg_team_members otherwise.
3357 --
3358 fnd_file.put_line (fnd_file.log, '55 : Buyer Merge - Calling MERGE_NEG_TEAM_FND_USER procedure');
3359
3360 MERGE_NEG_TEAM_FND_USER (p_From_FK_id => p_From_FK_id,
3361 p_To_FK_id => p_To_FK_id);
3362
3363 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
3364 --
3365 -- Nothing to do
3366 --
3367 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
3368 NULL;
3369 END IF;
3370
3371 EXCEPTION
3372 WHEN OTHERS THEN
3373
3374 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.NEG_SUPP_ACC_MERGE SQLERRM:'||SQLERRM);
3375 --
3376 -- We need add some Sourcing specific message for this
3377 --
3378 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3379 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3380 FND_MSG_PUB.ADD;
3381
3382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3383
3384 --} end of procedure
3385 END NEG_SUPP_ACC_MERGE;
3386
3387 -- Start of comments
3388 -- API name : BID_PARTY_TPC_MERGE
3389 --
3393 -- (p_From_FK_id) must exists in the database
3390 -- Type : Group
3391 --
3392 -- Pre-reqs : Invitation List entries with the given trading_partner_contact_id
3394 --
3395 -- Function : This procedure will be attached to the TRADING_PARTNER_CONTACT_ID
3396 -- merge for Sourcing PON_BIDDING_PARTIES entity.
3397 --
3398 -- It will veto Party Merge if -
3399 -- p_From_FK_id and p_To_FK_id both are not Seller user party
3400 --
3401 -- This will merge the all the PON_BIDDING_PARTIES records
3402 -- having TRADING_PARTNER_CONTACT_ID equals to p_From_FK_id
3403 -- to party id having value (p_To_FK_id)
3404 --
3405 -- Parameters:
3406 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
3407 -- TCA Merge Dictionary that is going to merge
3408 --
3409 -- IN : p_from_id NUMBER Required, the value of PK of the record
3410 -- which is going to be merged
3411 --
3412 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
3413 -- to which the p_from_id party is going to be merged
3414 --
3415 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
3416 -- Party Site, etc.) when merge is executed
3417 --
3418 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
3419 -- Party Site, etc.) when merge is executed
3420 --
3421 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
3422 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
3423 --
3424 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
3425 -- Party Merge is executed
3426 --
3427 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
3428 -- for which the Party Merge is executed
3429 --
3430 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
3431 -- was successful or not; It can have the following values -
3432 --
3433 -- FND_API.G_RET_STS_SUCCESS (Success)
3434 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
3435 -- failed due to Unexpected error)
3436 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
3437 -- vetod the Party Merge)
3438 --
3439 -- Version : Current version 1.0
3440 -- Previous version 1.0
3441 -- Initial version 1.0
3442 --
3443 -- End of comments
3444 PROCEDURE BID_PARTY_TPC_MERGE (
3445 p_Entity_name IN VARCHAR2,
3446 p_from_id IN NUMBER,
3447 x_to_id IN OUT NOCOPY NUMBER ,
3448 p_From_FK_id IN NUMBER,
3449 p_To_FK_id IN NUMBER,
3450 p_Parent_Entity_name IN VARCHAR2,
3451 p_batch_id IN NUMBER,
3452 p_Batch_Party_id IN NUMBER,
3453 x_return_status IN OUT NOCOPY VARCHAR2 )
3454 IS
3455 l_merge_scenario VARCHAR2(20);
3456 l_person_name VARCHAR2(300);
3457
3458 BEGIN
3459 --{start of procedure
3460
3461 x_return_status := FND_API.G_RET_STS_SUCCESS;
3462
3463 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.BID_PARTY_TPC_MERGE ');
3464 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
3465 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
3466 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
3467 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
3468 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
3469 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
3470 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
3471 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
3472
3473 fnd_file.put_line (fnd_file.log, '10.1 : Going to check merge scenario:');
3474 --
3475 -- check the merge scenario
3476 --
3477 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
3478 p_from_id => p_From_FK_id,
3479 p_to_id => p_To_FK_id);
3480
3481 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
3482
3483 IF (l_merge_scenario = G_BUYER OR l_merge_scenario = G_INCOMPATIBLE) THEN
3484
3485 fnd_file.put_line (fnd_file.log, '20 : Buyer Merge - Error');
3486 --
3487 -- This can not happen in any normal scenario as
3488 -- PON_BIDDING_PARTIES.TRADING_PARTNER_CONTACT_ID
3489 -- can never contain a buyer user id. Thus, raising a veto though
3490 -- it will possibly never be called
3491 --
3492 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
3493 FND_MSG_PUB.ADD;
3494 x_return_status := FND_API.G_RET_STS_ERROR;
3498 fnd_file.put_line (fnd_file.log, '30 : Seller Merge - Getting Person Name from HZ');
3495
3496 ELSIF (l_merge_scenario = G_SELLER) THEN
3497
3499
3500 SELECT
3501 hz.person_last_name ||', '|| hz.person_first_name as person_name
3502 INTO
3503 l_person_name
3504 FROM HZ_PARTIES hz
3505 WHERE hz.PARTY_ID= p_To_FK_id;
3506
3507 fnd_file.put_line (fnd_file.log, '40 : Seller Merge - Person Name from HZ:'|| l_person_name);
3508 --
3509 -- Now transact the data we have
3510 --
3511
3512 --
3513 -- there is no chance of unique key constraint violation
3514 -- due to the following updates
3515 --
3516 UPDATE PON_BIDDING_PARTIES
3517 SET LAST_UPDATE_DATE = SYSDATE,
3518 LAST_UPDATED_BY = -1,
3519 TRADING_PARTNER_CONTACT_ID = p_To_FK_id,
3520 TRADING_PARTNER_CONTACT_NAME = l_person_name
3521 WHERE TRADING_PARTNER_CONTACT_ID = p_From_FK_id;
3522
3523 fnd_file.put_line (fnd_file.log, '50 : Seller Merge - Updated TRADING_PARTNER_CONTACT_IDs in PON_BIDDING_PARTIES: '||sql%rowcount||' rows');
3524
3525 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
3526 --
3527 -- Nothing to do
3528 --
3529 NULL;
3530 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
3531 END IF;
3532
3533 EXCEPTION
3534 WHEN OTHERS THEN
3535
3536 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.BID_PARTY_TPC_MERGE SQLERRM:'||SQLERRM);
3537 --
3538 -- We need add some Sourcing specific message for this
3539 --
3540 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3541 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3542 FND_MSG_PUB.ADD;
3543
3544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3545
3546 --} end of procedure
3547 END BID_PARTY_TPC_MERGE;
3548
3549 -- Start of comments
3550 -- API name : BID_PARTY_ACK_TPC_MERGE
3551 --
3552 -- Type : Group
3553 --
3554 -- Pre-reqs : Invitation List entries with the given ack_partner_contact_id
3555 -- (p_From_FK_id) must exists in the database
3556 --
3557 -- Function : This procedure will be attached to the ACK_PARTNER_CONTACT_ID
3558 -- merge for Sourcing PON_BIDDING_PARTIES entity.
3559 --
3560 -- It will veto Party Merge if -
3561 -- p_From_FK_id and p_To_FK_id both are not Seller user party
3562 --
3563 -- This will merge the all the PON_BIDDING_PARTIES records
3564 -- having ACK_PARTNER_CONTACT_ID equals to p_From_FK_id
3565 -- to party id having value (p_To_FK_id)
3566 --
3567 -- Parameters:
3568 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
3569 -- TCA Merge Dictionary that is going to merge
3570 --
3571 -- IN : p_from_id NUMBER Required, the value of PK of the record
3572 -- which is going to be merged
3573 --
3574 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
3575 -- to which the p_from_id party is going to be merged
3576 --
3577 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
3578 -- Party Site, etc.) when merge is executed
3579 --
3580 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
3581 -- Party Site, etc.) when merge is executed
3582 --
3583 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
3584 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
3585 --
3586 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
3587 -- Party Merge is executed
3588 --
3589 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
3590 -- for which the Party Merge is executed
3591 --
3592 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
3593 -- was successful or not; It can have the following values -
3594 --
3595 -- FND_API.G_RET_STS_SUCCESS (Success)
3596 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
3597 -- failed due to Unexpected error)
3598 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
3599 -- vetod the Party Merge)
3600 --
3601 -- Version : Current version 1.0
3602 -- Previous version 1.0
3603 -- Initial version 1.0
3604 --
3605 -- End of comments
3606 PROCEDURE BID_PARTY_ACK_TPC_MERGE (
3607 p_Entity_name IN VARCHAR2,
3608 p_from_id IN NUMBER,
3609 x_to_id IN OUT NOCOPY NUMBER ,
3610 p_From_FK_id IN NUMBER,
3611 p_To_FK_id IN NUMBER,
3615 x_return_status IN OUT NOCOPY VARCHAR2 )
3612 p_Parent_Entity_name IN VARCHAR2,
3613 p_batch_id IN NUMBER,
3614 p_Batch_Party_id IN NUMBER,
3616 IS
3617 l_merge_scenario VARCHAR2(20);
3618 l_person_name VARCHAR2(300);
3619
3620 BEGIN
3621 --{start of procedure
3622
3623 x_return_status := FND_API.G_RET_STS_SUCCESS;
3624
3625 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.BID_PARTY_ACK_TPC_MERGE ');
3626 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
3627 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
3628 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
3629 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
3630 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
3631 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
3632 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
3633 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
3634
3635 --
3636 -- check the merge scenario
3637 --
3638 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
3639 p_from_id => p_From_FK_id,
3640 p_to_id => p_To_FK_id);
3641
3642 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
3643
3644 IF (l_merge_scenario = G_BUYER OR l_merge_scenario = G_INCOMPATIBLE) THEN
3645
3646 fnd_file.put_line (fnd_file.log, '20 : Buyer or Incompatible Merge - Error');
3647 --
3648 -- This can not happen in any normal scenario as
3649 -- PON_BIDDING_PARTIES.ACK_PARTNER_CONTACT_ID
3650 -- can never contain a buyer user id. Thus, raising a veto though
3651 -- it will possibly never be called
3652 --
3653 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
3654 FND_MSG_PUB.ADD;
3655 x_return_status := FND_API.G_RET_STS_ERROR;
3656
3657 ELSIF (l_merge_scenario = G_SELLER) THEN
3658
3659 fnd_file.put_line (fnd_file.log, '30 : Seller Merge - Getting Person Name from HZ');
3660
3661 SELECT
3662 hz.person_last_name ||', '|| hz.person_first_name as person_name
3663 INTO
3664 l_person_name
3665 FROM HZ_PARTIES hz
3666 WHERE hz.PARTY_ID= p_To_FK_id;
3667
3668 fnd_file.put_line (fnd_file.log, '40 : Seller Merge - Person Name from HZ:'|| l_person_name);
3669 --
3670 -- Now transact the data we have
3671 --
3672
3673 --
3674 -- there is no chance of unique key constraint violation
3675 -- due to the following updates
3676 --
3677 UPDATE PON_BIDDING_PARTIES
3678 SET LAST_UPDATE_DATE = SYSDATE,
3679 LAST_UPDATED_BY = -1,
3680 ACK_PARTNER_CONTACT_ID = p_To_FK_id,
3681 ACK_PARTNER_CONTACT_NAME = l_person_name
3682 WHERE ACK_PARTNER_CONTACT_ID = p_From_FK_id;
3683
3684 fnd_file.put_line (fnd_file.log, '50 : Seller Merge - Updated ACK_PARTNER_CONTACT_IDs in PON_BIDDING_PARTIES: '||sql%rowcount||' rows');
3685
3686 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
3687 --
3688 -- Nothing to do
3689 --
3690 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
3691 NULL;
3692 END IF;
3693
3694 EXCEPTION
3695 WHEN OTHERS THEN
3696
3697 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.BID_PARTY_ACK_TPC_MERGE SQLERRM:'||SQLERRM);
3698 --
3699 -- We need add some Sourcing specific message for this
3700 --
3701 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3702 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3703 FND_MSG_PUB.ADD;
3704
3705 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3706
3707 --} end of procedure
3708 END BID_PARTY_ACK_TPC_MERGE;
3709
3710 -- Start of comments
3711 -- API name : RES_UNLCK_TPC_MERGE
3712 --
3713 -- Type : Group
3714 --
3715 -- Pre-reqs : Responses with the given draft_unlocked_by_contact_id
3716 -- (p_From_FK_id) must exists in the database
3717 --
3718 -- Function : This procedure will be attached to the DRAFT_UNLOCKED_BY_CONTACT_ID
3719 -- merge for Sourcing PON_BID_HEADERS entity.
3720 --
3721 -- It will veto Party Merge if -
3722 -- p_From_FK_id and p_To_FK_id both are not Seller or Buyer user party
3723 --
3724 -- This will merge the all the PON_BID_HEADERS records
3725 -- having DRAFT_UNLOCKED_BY_CONTACT_ID equals to p_From_FK_id
3726 -- to party id having value (p_To_FK_id)
3727 --
3728 -- Parameters:
3729 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
3733 -- which is going to be merged
3730 -- TCA Merge Dictionary that is going to merge
3731 --
3732 -- IN : p_from_id NUMBER Required, the value of PK of the record
3734 --
3735 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
3736 -- to which the p_from_id party is going to be merged
3737 --
3738 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
3739 -- Party Site, etc.) when merge is executed
3740 --
3741 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
3742 -- Party Site, etc.) when merge is executed
3743 --
3744 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
3745 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
3746 --
3747 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
3748 -- Party Merge is executed
3749 --
3750 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
3751 -- for which the Party Merge is executed
3752 --
3753 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
3754 -- was successful or not; It can have the following values -
3755 --
3756 -- FND_API.G_RET_STS_SUCCESS (Success)
3757 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
3758 -- failed due to Unexpected error)
3759 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
3760 -- vetod the Party Merge)
3761 --
3762 -- Version : Current version 1.0
3763 -- Previous version 1.0
3764 -- Initial version 1.0
3765 --
3766 -- End of comments
3767 PROCEDURE RES_UNLCK_TPC_MERGE (
3768 p_Entity_name IN VARCHAR2,
3769 p_from_id IN NUMBER,
3770 x_to_id IN OUT NOCOPY NUMBER ,
3771 p_From_FK_id IN NUMBER,
3772 p_To_FK_id IN NUMBER,
3773 p_Parent_Entity_name IN VARCHAR2,
3774 p_batch_id IN NUMBER,
3775 p_Batch_Party_id IN NUMBER,
3776 x_return_status IN OUT NOCOPY VARCHAR2 )
3777 IS
3778 l_merge_scenario VARCHAR2(20);
3779
3780 BEGIN
3781 --{start of procedure
3782
3783 x_return_status := FND_API.G_RET_STS_SUCCESS;
3784
3785 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.RES_UNLCK_TPC_MERGE ');
3786 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
3787 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
3788 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
3789 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
3790 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
3791 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
3792 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
3793 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
3794
3795 --
3796 -- check the merge scenario
3797 --
3798 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
3799 p_from_id => p_From_FK_id,
3800 p_to_id => p_To_FK_id);
3801
3802 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
3803
3804 IF (l_merge_scenario = G_INCOMPATIBLE) THEN
3805
3806 fnd_file.put_line (fnd_file.log, '20 : Incompatible Merge - Error');
3807 --
3808 -- This can not happen in any normal scenario as
3809 -- PON_BID_HEADERS.DRAFT_UNLOCKED_BY_CONTACT_ID
3810 -- can never contain a buyer user id which is merged to a seller user.
3811 -- Thus, raising a veto though
3812 -- it will possibly never be called
3813 --
3814 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
3815 FND_MSG_PUB.ADD;
3816 x_return_status := FND_API.G_RET_STS_ERROR;
3817
3818 ELSIF (l_merge_scenario = G_SELLER OR l_merge_scenario = G_BUYER ) THEN
3819
3820 fnd_file.put_line (fnd_file.log, '30 : Seller Merge ');
3821 --
3822 -- there is no chance of unique key constraint violation
3823 -- due to the following updates
3824 --
3825 UPDATE PON_BID_HEADERS
3826 SET LAST_UPDATE_DATE = SYSDATE,
3827 LAST_UPDATED_BY = -1,
3828 DRAFT_UNLOCKED_BY_CONTACT_ID = p_To_FK_id
3829 WHERE DRAFT_UNLOCKED_BY_CONTACT_ID = p_From_FK_id;
3830
3834 --
3831 fnd_file.put_line (fnd_file.log, '50 : Seller Merge - Updated DRAFT_UNLOCKED_BY_CONTACT_IDs in PON_BID_HEADERS: '||sql%rowcount||' rows');
3832
3833 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
3835 -- Nothing to do
3836 --
3837 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
3838 NULL;
3839 END IF;
3840
3841 EXCEPTION
3842 WHEN OTHERS THEN
3843
3844 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.RES_UNLCK_TPC_MERGE SQLERRM:'||SQLERRM);
3845 --
3846 -- We need add some Sourcing specific message for this
3847 --
3848 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3849 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3850 FND_MSG_PUB.ADD;
3851
3852 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3853
3854 --} end of procedure
3855 END RES_UNLCK_TPC_MERGE;
3856
3857
3858 -- Start of comments
3859 -- API name : RES_LCK_TPC_MERGE
3860 --
3861 -- Type : Group
3862 --
3863 -- Pre-reqs : Responses with the given draft_locked_by_contact_id
3864 -- (p_From_FK_id) must exists in the database
3865 --
3866 -- Function : This procedure will be attached to the DRAFT_LOCKED_BY_CONTACT_ID
3867 -- merge for Sourcing PON_BID_HEADERS entity.
3868 --
3869 -- It will veto Party Merge if -
3870 -- p_From_FK_id and p_To_FK_id both are not Seller or Buyer user party
3871 --
3872 -- This will merge the all the PON_BID_HEADERS records
3873 -- having DRAFT_LOCKED_BY_CONTACT_ID equals to p_From_FK_id
3874 -- to party id having value (p_To_FK_id)
3875 --
3876 -- Parameters:
3877 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
3878 -- TCA Merge Dictionary that is going to merge
3879 --
3880 -- IN : p_from_id NUMBER Required, the value of PK of the record
3881 -- which is going to be merged
3882 --
3883 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
3884 -- to which the p_from_id party is going to be merged
3885 --
3886 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
3887 -- Party Site, etc.) when merge is executed
3888 --
3889 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
3890 -- Party Site, etc.) when merge is executed
3891 --
3892 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
3893 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
3894 --
3895 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
3896 -- Party Merge is executed
3897 --
3898 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
3899 -- for which the Party Merge is executed
3900 --
3901 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
3902 -- was successful or not; It can have the following values -
3903 --
3904 -- FND_API.G_RET_STS_SUCCESS (Success)
3905 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
3906 -- failed due to Unexpected error)
3907 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
3908 -- vetod the Party Merge)
3909 --
3910 -- Version : Current version 1.0
3911 -- Previous version 1.0
3912 -- Initial version 1.0
3913 --
3914 -- End of comments
3915 PROCEDURE RES_LCK_TPC_MERGE (
3916 p_Entity_name IN VARCHAR2,
3917 p_from_id IN NUMBER,
3918 x_to_id IN OUT NOCOPY NUMBER ,
3919 p_From_FK_id IN NUMBER,
3920 p_To_FK_id IN NUMBER,
3921 p_Parent_Entity_name IN VARCHAR2,
3922 p_batch_id IN NUMBER,
3923 p_Batch_Party_id IN NUMBER,
3924 x_return_status IN OUT NOCOPY VARCHAR2 )
3925 IS
3926 l_merge_scenario VARCHAR2(20);
3927
3928 BEGIN
3929 --{start of procedure
3930
3931 x_return_status := FND_API.G_RET_STS_SUCCESS;
3932
3933 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.RES_LCK_TPC_MERGE ');
3934 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
3935 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
3936 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
3937 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
3938 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
3939 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
3940 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
3941 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
3945 --
3942
3943 --
3944 -- check the merge scenario
3946 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
3947 p_from_id => p_From_FK_id,
3948 p_to_id => p_To_FK_id);
3949
3950 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
3951
3952 IF (l_merge_scenario = G_INCOMPATIBLE) THEN
3953
3954 fnd_file.put_line (fnd_file.log, '20 : Incompatible Merge - Error');
3955 --
3956 -- This can not happen in any normal scenario as
3957 -- PON_BID_HEADERS.DRAFT_LOCKED_BY_CONTACT_ID
3958 -- can never contain a buyer user id which is merged to a seller user id.
3959 -- Thus, raising a veto though
3960 -- it will possibly never be called
3961 --
3962 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
3963 FND_MSG_PUB.ADD;
3964 x_return_status := FND_API.G_RET_STS_ERROR;
3965
3966 ELSIF (l_merge_scenario = G_SELLER OR l_merge_scenario = G_BUYER) THEN
3967
3968 fnd_file.put_line (fnd_file.log, '30 : Seller Merge ');
3969 --
3970 -- there is no chance of unique key constraint violation
3971 -- due to the following updates
3972 --
3973 UPDATE PON_BID_HEADERS
3974 SET LAST_UPDATE_DATE = SYSDATE,
3975 LAST_UPDATED_BY = -1,
3976 DRAFT_LOCKED_BY_CONTACT_ID = p_To_FK_id
3977 WHERE DRAFT_LOCKED_BY_CONTACT_ID = p_From_FK_id;
3978
3979 fnd_file.put_line (fnd_file.log, '50 : Seller Merge - Updated DRAFT_LOCKED_BY_CONTACT_IDs in PON_BID_HEADERS: '||sql%rowcount||' rows');
3980
3981 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
3982 --
3983 -- Nothing to do
3984 --
3985 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
3986 NULL;
3987 END IF;
3988
3989 EXCEPTION
3990 WHEN OTHERS THEN
3991
3992 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.RES_LCK_TPC_MERGE SQLERRM:'||SQLERRM);
3993 --
3994 -- We need add some Sourcing specific message for this
3995 --
3996 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3997 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3998 FND_MSG_PUB.ADD;
3999
4000 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4001
4002 --} end of procedure
4003 END RES_LCK_TPC_MERGE;
4004
4005 -- Start of comments
4006 -- API name : RES_TPC_MERGE
4007 --
4008 -- Type : Group
4009 --
4010 -- Pre-reqs : Responses with the given trading_partner_contact_id
4011 -- (p_From_FK_id) must exists in the database
4012 --
4013 -- Function : This procedure will be attached to the TRADING_PARTNER_CONTACT_ID
4014 -- merge for Sourcing PON_BID_HEADERS entity.
4015 --
4016 -- It will veto Party Merge if -
4017 -- p_From_FK_id and p_To_FK_id both are not Seller user party
4018 --
4019 -- This will merge the all the PON_BID_HEADERS records
4020 -- having TRADING_PARTNER_CONTACT_ID equals to p_From_FK_id
4021 -- to party id having value (p_To_FK_id)
4022 --
4023 -- Parameters:
4024 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
4025 -- TCA Merge Dictionary that is going to merge
4026 --
4027 -- IN : p_from_id NUMBER Required, the value of PK of the record
4028 -- which is going to be merged
4029 --
4030 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
4031 -- to which the p_from_id party is going to be merged
4032 --
4033 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
4034 -- Party Site, etc.) when merge is executed
4035 --
4036 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
4037 -- Party Site, etc.) when merge is executed
4038 --
4039 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
4040 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
4041 --
4042 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
4043 -- Party Merge is executed
4044 --
4045 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
4046 -- for which the Party Merge is executed
4047 --
4048 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
4049 -- was successful or not; It can have the following values -
4050 --
4051 -- FND_API.G_RET_STS_SUCCESS (Success)
4052 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
4053 -- failed due to Unexpected error)
4054 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
4055 -- vetod the Party Merge)
4056 --
4057 -- Version : Current version 1.0
4058 -- Previous version 1.0
4059 -- Initial version 1.0
4060 --
4061 -- End of comments
4062 PROCEDURE RES_TPC_MERGE (
4063 p_Entity_name IN VARCHAR2,
4064 p_from_id IN NUMBER,
4065 x_to_id IN OUT NOCOPY NUMBER ,
4066 p_From_FK_id IN NUMBER,
4067 p_To_FK_id IN NUMBER,
4068 p_Parent_Entity_name IN VARCHAR2,
4069 p_batch_id IN NUMBER,
4070 p_Batch_Party_id IN NUMBER,
4071 x_return_status IN OUT NOCOPY VARCHAR2 )
4072 IS
4073 l_merge_scenario VARCHAR2(20);
4074 l_user_name FND_USER.USER_NAME%TYPE;
4075
4076 BEGIN
4077 --{start of procedure
4078
4079 x_return_status := FND_API.G_RET_STS_SUCCESS;
4080
4081 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.RES_TPC_MERGE ');
4082 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
4083 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
4084 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
4085 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
4086 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
4087 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
4088 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
4089 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
4090
4091 --
4092 -- check the merge scenario
4093 --
4094 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
4095 p_from_id => p_From_FK_id,
4096 p_to_id => p_To_FK_id);
4097
4098 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
4099
4100 IF (l_merge_scenario = G_BUYER OR l_merge_scenario = G_INCOMPATIBLE) THEN
4101
4102 fnd_file.put_line (fnd_file.log, '20 : Buyer or Incompatible Merge - Error');
4103 --
4104 -- This can not happen in any normal scenario as
4105 -- PON_BID_HEADERS.TRADING_PARTNER_CONTACT_ID
4106 -- can never contain a buyer user id. Thus, raising a veto though
4107 -- it will possibly never be called
4108 --
4109 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
4110 FND_MSG_PUB.ADD;
4111 x_return_status := FND_API.G_RET_STS_ERROR;
4112
4113 ELSIF (l_merge_scenario = G_SELLER) THEN
4114
4115 fnd_file.put_line (fnd_file.log, '30 : Seller Merge - Getting User Name from FND');
4116
4117 l_user_name := GET_USER_NAME(p_To_FK_id);
4118
4119 fnd_file.put_line (fnd_file.log, '40 : Seller Merge - User Name from FND:'||l_user_name);
4120
4121 --
4122 -- Now transact the data we have
4123 --
4124
4125 --
4126 -- there is no chance of unique key constraint violation
4127 -- due to the following constraints. We should not produce
4128 -- two active bids from the same supplier but different trading
4129 -- partner contact id
4130 --
4131
4132 UPDATE PON_BID_HEADERS pb
4133 SET pb.LAST_UPDATE_DATE = SYSDATE,
4134 pb.LAST_UPDATED_BY = -1,
4135 pb.TRADING_PARTNER_CONTACT_ID = p_To_FK_id,
4136 pb.TRADING_PARTNER_CONTACT_NAME = l_user_name
4137 WHERE pb.TRADING_PARTNER_CONTACT_ID = p_From_FK_id
4138 AND NOT EXISTS (SELECT 'DUPLICATE'
4139 FROM PON_BID_HEADERS pb1
4140 WHERE pb1.AUCTION_HEADER_ID = pb.AUCTION_HEADER_ID
4141 AND pb1.TRADING_PARTNER_ID <> pb.TRADING_PARTNER_ID
4142 AND pb1.TRADING_PARTNER_CONTACT_ID = p_To_FK_id
4143 AND pb1.BID_STATUS = 'ACTIVE');
4144
4145
4149 --
4146 fnd_file.put_line (fnd_file.log, '50 : Seller Merge - Updated TRADING_PARTNER_CONTACT_IDs in PON_BID_HEADERS: '||sql%rowcount||' rows');
4147
4148 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
4150 -- Nothing to do
4151 --
4152 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
4153 NULL;
4154 END IF;
4155
4156 EXCEPTION
4157 WHEN OTHERS THEN
4158
4159 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.RES_TPC_MERGE SQLERRM:'||SQLERRM);
4160 --
4161 -- We need add some Sourcing specific message for this
4162 --
4163 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4164 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4165 FND_MSG_PUB.ADD;
4166
4167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4168
4169 --} end of procedure
4170 END RES_TPC_MERGE;
4171
4172 -- Start of comments
4173 -- API name : OPTMZ_TPC_MERGE
4174 --
4175 -- Type : Group
4176 --
4177 -- Pre-reqs : Optimization Scenario with the given trading_partner_contact_id
4178 -- (p_From_FK_id) must exists in the database
4179 --
4180 -- Function : This procedure will be attached to the TRADING_PARTNER_CONTACT_ID
4181 -- merge for Sourcing PON_OPTIMIZE_CONSTRAINTS entity.
4182 --
4183 -- It will veto Party Merge if -
4184 -- p_From_FK_id and p_To_FK_id both are not Seller user party
4185 --
4186 -- This will merge the all the PON_OPTIMIZE_CONSTRAINTS records
4187 -- having TRADING_PARTNER_CONTACT_ID equals to p_From_FK_id
4188 -- to party id having value (p_To_FK_id)
4189 --
4190 -- Parameters:
4191 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
4192 -- TCA Merge Dictionary that is going to merge
4193 --
4194 -- IN : p_from_id NUMBER Required, the value of PK of the record
4195 -- which is going to be merged
4196 --
4197 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
4198 -- to which the p_from_id party is going to be merged
4199 --
4200 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
4201 -- Party Site, etc.) when merge is executed
4202 --
4203 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
4204 -- Party Site, etc.) when merge is executed
4205 --
4206 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
4207 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
4208 --
4209 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
4210 -- Party Merge is executed
4211 --
4212 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
4213 -- for which the Party Merge is executed
4214 --
4215 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
4216 -- was successful or not; It can have the following values -
4217 --
4218 -- FND_API.G_RET_STS_SUCCESS (Success)
4219 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
4220 -- failed due to Unexpected error)
4221 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
4222 -- vetod the Party Merge)
4223 --
4224 -- Version : Current version 1.0
4225 -- Previous version 1.0
4226 -- Initial version 1.0
4227 --
4228 -- End of comments
4229 PROCEDURE OPTMZ_TPC_MERGE (
4230 p_Entity_name IN VARCHAR2,
4231 p_from_id IN NUMBER,
4232 x_to_id IN OUT NOCOPY NUMBER ,
4233 p_From_FK_id IN NUMBER,
4234 p_To_FK_id IN NUMBER,
4235 p_Parent_Entity_name IN VARCHAR2,
4236 p_batch_id IN NUMBER,
4237 p_Batch_Party_id IN NUMBER,
4238 x_return_status IN OUT NOCOPY VARCHAR2 )
4239 IS
4240 l_merge_scenario VARCHAR2(20);
4241
4242 BEGIN
4243 --{start of procedure
4244
4245 x_return_status := FND_API.G_RET_STS_SUCCESS;
4246
4247 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.OPTMZ_TPC_MERGE ');
4248 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
4249 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
4250 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
4251 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
4252 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
4253 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
4254 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
4255 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
4259 --
4256
4257 --
4258 -- check the merge scenario
4260 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
4261 p_from_id => p_From_FK_id,
4262 p_to_id => p_To_FK_id);
4263
4264 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
4265
4266 IF (l_merge_scenario = G_BUYER OR l_merge_scenario = G_INCOMPATIBLE) THEN
4267
4268 fnd_file.put_line (fnd_file.log, '20 : Buyer or Incompatible Merge - Error');
4269 --
4270 -- This can not happen in any normal scenario as
4271 -- PON_OPTIMIZE_CONSTRAINTS.TRADING_PARTNER_CONTACT_ID
4272 -- can never contain a buyer user id. Thus, raising a veto though
4273 -- it will possibly never be called
4274 --
4275 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
4276 FND_MSG_PUB.ADD;
4277 x_return_status := FND_API.G_RET_STS_ERROR;
4278
4279 ELSIF (l_merge_scenario = G_SELLER) THEN
4280
4281 fnd_file.put_line (fnd_file.log, '30 : Seller Merge ');
4282
4283 --
4284 -- there is no chance of unique key constraint violation
4285 -- due to the following updates
4286 --
4287 UPDATE PON_OPTIMIZE_CONSTRAINTS
4288 SET LAST_UPDATE_DATE = SYSDATE,
4289 LAST_UPDATED_BY = -1,
4290 TRADING_PARTNER_CONTACT_ID = p_To_FK_id
4291 WHERE TRADING_PARTNER_CONTACT_ID = p_From_FK_id;
4292
4293 fnd_file.put_line (fnd_file.log, '50 : Seller Merge - Updated TRADING_PARTNER_CONTACT_IDs in PON_OPTIMIZE_CONSTRAINTS: '||sql%rowcount||' rows');
4294
4295 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
4296 --
4297 -- Nothing to do
4298 --
4299 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
4300 NULL;
4301 END IF;
4302
4303 EXCEPTION
4304 WHEN OTHERS THEN
4305
4306 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.OPTMZ_TPC_MERGE SQLERRM:'||SQLERRM);
4307 --
4308 -- We need add some Sourcing specific message for this
4309 --
4310 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4311 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4312 FND_MSG_PUB.ADD;
4313
4314 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4315
4316 --} end of procedure
4317 END OPTMZ_TPC_MERGE;
4318
4319 -- Start of comments
4320 -- API name : RES_ACK_MERGE
4321 --
4322 -- Type : Group
4323 --
4324 -- Pre-reqs : Invitation Response with the given trading_partner_contact_id
4325 -- (p_From_FK_id) must exists in the database
4326 --
4327 -- Function : This procedure will be attached to the TRADING_PARTNER_CONTACT_ID
4328 -- merge for Sourcing PON_ACKNOWLEDGEMENTS entity.
4329 --
4330 -- It will veto Party Merge if -
4331 -- p_From_FK_id and p_To_FK_id both are not Seller user party
4332 --
4333 -- This will merge the all the PON_ACKNOWLEDGEMENTS records
4334 -- having TRADING_PARTNER_CONTACT_ID equals to p_From_FK_id
4335 -- to party id having value (p_To_FK_id)
4336 --
4337 -- Parameters:
4338 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
4339 -- TCA Merge Dictionary that is going to merge
4340 --
4341 -- IN : p_from_id NUMBER Required, the value of PK of the record
4342 -- which is going to be merged
4343 --
4344 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
4345 -- to which the p_from_id party is going to be merged
4346 --
4347 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
4348 -- Party Site, etc.) when merge is executed
4349 --
4350 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
4351 -- Party Site, etc.) when merge is executed
4352 --
4353 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
4354 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
4355 --
4356 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
4357 -- Party Merge is executed
4358 --
4359 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
4360 -- for which the Party Merge is executed
4361 --
4362 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
4363 -- was successful or not; It can have the following values -
4364 --
4365 -- FND_API.G_RET_STS_SUCCESS (Success)
4366 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
4370 --
4367 -- failed due to Unexpected error)
4368 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
4369 -- vetod the Party Merge)
4371 -- Version : Current version 1.0
4372 -- Previous version 1.0
4373 -- Initial version 1.0
4374 --
4375 -- End of comments
4376 PROCEDURE RES_ACK_MERGE (
4377 p_Entity_name IN VARCHAR2,
4378 p_from_id IN NUMBER,
4379 x_to_id IN OUT NOCOPY NUMBER ,
4380 p_From_FK_id IN NUMBER,
4381 p_To_FK_id IN NUMBER,
4382 p_Parent_Entity_name IN VARCHAR2,
4383 p_batch_id IN NUMBER,
4384 p_Batch_Party_id IN NUMBER,
4385 x_return_status IN OUT NOCOPY VARCHAR2 )
4386 IS
4387 l_merge_scenario VARCHAR2(20);
4388
4389 BEGIN
4390 --{start of procedure
4391
4392 x_return_status := FND_API.G_RET_STS_SUCCESS;
4393
4394 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.RES_ACK_MERGE ');
4395 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
4396 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
4397 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
4398 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
4399 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
4400 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
4401 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
4402 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
4403
4404 --
4405 -- check the merge scenario
4406 --
4407 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
4408 p_from_id => p_From_FK_id,
4409 p_to_id => p_To_FK_id);
4410
4411 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
4412
4413 IF (l_merge_scenario = G_BUYER OR l_merge_scenario = G_INCOMPATIBLE) THEN
4414
4415 fnd_file.put_line (fnd_file.log, '20 : Buyer or Incompatible Merge - Error');
4416 --
4417 -- This can not happen in any normal scenario as
4418 -- PON_ACKNOWLEDGEMENTS.TRADING_PARTNER_CONTACT_ID
4419 -- can never contain a buyer user id. Thus, raising a veto though
4420 -- it will possibly never be called
4421 --
4422 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
4423 FND_MSG_PUB.ADD;
4424 x_return_status := FND_API.G_RET_STS_ERROR;
4425
4426 ELSIF (l_merge_scenario = G_SELLER) THEN
4427
4428 fnd_file.put_line (fnd_file.log, '30 : Seller Merge ');
4429 --
4430 -- there is no chance of unique key constraint violation
4431 -- due to the following logic - trading_partner_contact_id should not
4432 -- clash in an auction_header_id (PON_ACKNOWLEDGEMENTS_U1 violation)
4433 --
4434 UPDATE PON_ACKNOWLEDGEMENTS pa
4435 SET pa.LAST_UPDATE_DATE = SYSDATE,
4436 pa.LAST_UPDATED_BY = -1,
4437 pa.TRADING_PARTNER_CONTACT_ID = p_To_FK_id
4438 WHERE pa.TRADING_PARTNER_CONTACT_ID = p_From_FK_id
4439 AND NOT EXISTS ( SELECT 'DUPLICATE'
4440 FROM PON_ACKNOWLEDGEMENTS pa1
4441 WHERE pa1.AUCTION_HEADER_ID = pa.AUCTION_HEADER_ID
4442 AND pa1.TRADING_PARTNER_CONTACT_ID = p_To_FK_id );
4443
4444
4445 fnd_file.put_line (fnd_file.log, '50 : Seller Merge - Updated TRADING_PARTNER_CONTACT_IDs in PON_ACKNOWLEDGEMENTS: '||sql%rowcount||' rows');
4446
4447 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
4448 --
4449 -- Nothing to do
4450 --
4451 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
4452 NULL;
4453 END IF;
4454
4455 EXCEPTION
4456 WHEN OTHERS THEN
4457
4458 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.RES_ACK_MERGE SQLERRM:'||SQLERRM);
4459 --
4460 -- We need add some Sourcing specific message for this
4461 --
4462 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4463 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4464 FND_MSG_PUB.ADD;
4465
4466 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4467
4468 --} end of procedure
4469 END RES_ACK_MERGE;
4470
4471 -- Start of comments
4472 -- API name : AUC_SUMM_TPC_MERGE
4473 --
4474 -- Type : Group
4475 --
4476 -- Pre-reqs : Award Summary records with the given trading_partner_contact_id
4477 -- (p_From_FK_id) must exists in the database
4478 --
4479 -- Function : This procedure will be attached to the TRADING_PARTNER_CONTACT_ID
4480 -- merge for Sourcing PON_AUCTION_SUMMARY entity.
4481 --
4482 -- It will veto Party Merge if -
4483 -- p_From_FK_id and p_To_FK_id both are not Seller user party
4484 --
4485 -- This will merge the all the PON_AUCTION_SUMMARY records
4486 -- having TRADING_PARTNER_CONTACT_ID equals to p_From_FK_id
4487 -- to party id having value (p_To_FK_id)
4488 --
4489 -- Parameters:
4490 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
4491 -- TCA Merge Dictionary that is going to merge
4492 --
4493 -- IN : p_from_id NUMBER Required, the value of PK of the record
4494 -- which is going to be merged
4495 --
4496 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
4497 -- to which the p_from_id party is going to be merged
4498 --
4499 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
4500 -- Party Site, etc.) when merge is executed
4501 --
4502 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
4503 -- Party Site, etc.) when merge is executed
4504 --
4505 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
4506 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
4507 --
4508 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
4509 -- Party Merge is executed
4510 --
4511 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
4512 -- for which the Party Merge is executed
4513 --
4514 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
4515 -- was successful or not; It can have the following values -
4516 --
4517 -- FND_API.G_RET_STS_SUCCESS (Success)
4518 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
4519 -- failed due to Unexpected error)
4520 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
4521 -- vetod the Party Merge)
4522 --
4523 -- Version : Current version 1.0
4524 -- Previous version 1.0
4525 -- Initial version 1.0
4526 --
4527 -- End of comments
4528 PROCEDURE AUC_SUMM_TPC_MERGE (
4529 p_Entity_name IN VARCHAR2,
4530 p_from_id IN NUMBER,
4531 x_to_id IN OUT NOCOPY NUMBER ,
4532 p_From_FK_id IN NUMBER,
4533 p_To_FK_id IN NUMBER,
4534 p_Parent_Entity_name IN VARCHAR2,
4535 p_batch_id IN NUMBER,
4536 p_Batch_Party_id IN NUMBER,
4537 x_return_status IN OUT NOCOPY VARCHAR2 )
4538 IS
4539 l_merge_scenario VARCHAR2(20);
4540
4541 BEGIN
4542 --{start of procedure
4543
4544 x_return_status := FND_API.G_RET_STS_SUCCESS;
4545
4546 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.AUC_SUMM_TPC_MERGE ');
4547 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
4548 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
4549 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
4550 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
4551 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
4552 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
4553 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
4554 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
4555
4556 --
4557 -- check the merge scenario
4558 --
4559 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
4560 p_from_id => p_From_FK_id,
4561 p_to_id => p_To_FK_id);
4562
4563 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
4564
4565 IF (l_merge_scenario = G_BUYER OR l_merge_scenario = G_INCOMPATIBLE) THEN
4566 fnd_file.put_line (fnd_file.log, '20 : Buyer or Incompatible Merge - Error');
4567 --
4568 -- This can not happen in any normal scenario as
4569 -- PON_AUCTION_SUMMARY.TRADING_PARTNER_CONTACT_ID
4570 -- can never contain a buyer user id. Thus, raising a veto though
4571 -- it will possibly never be called
4572 --
4573 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
4574 FND_MSG_PUB.ADD;
4575 x_return_status := FND_API.G_RET_STS_ERROR;
4576
4577 ELSIF (l_merge_scenario = G_SELLER) THEN
4578
4579 fnd_file.put_line (fnd_file.log, '30 : Seller Merge ');
4580 --
4581 -- there is no chance of unique key constraint violation
4582 -- due to the following updates
4583 --
4584
4585 --
4586 -- NOTE: The table does not have WHO columns
4587 --
4588 UPDATE PON_AUCTION_SUMMARY
4589 SET TRADING_PARTNER_CONTACT_ID = p_To_FK_id
4590 WHERE TRADING_PARTNER_CONTACT_ID = p_From_FK_id;
4591
4592 fnd_file.put_line (fnd_file.log, '50 : Seller Merge - Updated TRADING_PARTNER_CONTACT_IDs in PON_AUCTION_SUMMARY: '||sql%rowcount||' rows');
4593
4594 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
4595 --
4596 -- Nothing to do
4597 --
4598 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
4599 NULL;
4600 END IF;
4601
4602 EXCEPTION
4603 WHEN OTHERS THEN
4604
4605 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.AUC_SUMM_TPC_MERGE SQLERRM:'||SQLERRM);
4606 --
4607 -- We need add some Sourcing specific message for this
4608 --
4609 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4610 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4611 FND_MSG_PUB.ADD;
4612
4613 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4614
4615 --} end of procedure
4616 END AUC_SUMM_TPC_MERGE;
4617
4618
4619 -- Start of comments
4620 -- API name : SUP_ACT_MERGE
4621 --
4622 -- Type : Group
4623 --
4624 -- Pre-reqs : Supplier Activity records with the given trading_partner_contact_id
4625 -- (p_From_FK_id) must exists in the database
4626 --
4627 -- Function : This procedure will be attached to the TRADING_PARTNER_CONTACT_ID
4628 -- merge for Sourcing PON_SUPPLIER_ACTIVITIES entity.
4629 --
4630 -- It will veto Party Merge if -
4631 -- p_From_FK_id and p_To_FK_id both are not Seller user party
4632 --
4633 -- This will merge the all the PON_SUPPLIER_ACTIVITIES records
4634 -- having TRADING_PARTNER_CONTACT_ID equals to p_From_FK_id
4635 -- to party id having value (p_To_FK_id)
4636 --
4637 -- Parameters:
4638 -- IN : p_Entity_name VARCHAR2 Required, the Entity name from
4639 -- TCA Merge Dictionary that is going to merge
4640 --
4641 -- IN : p_from_id NUMBER Required, the value of PK of the record
4642 -- which is going to be merged
4643 --
4644 -- IN OUT : x_to_id NUMBER Required, the value of PK of the record
4645 -- to which the p_from_id party is going to be merged
4646 --
4647 -- IN : p_From_FK_id NUMBER Required, value of the from ID (e.g. Party,
4648 -- Party Site, etc.) when merge is executed
4649 --
4650 -- IN : p_To_FK_id NUMBER Required, Value of the to ID (e.g. Party,
4651 -- Party Site, etc.) when merge is executed
4652 --
4653 -- IN : p_Entity_name VARCHAR2 Required, Name of parent HZ
4654 -- table (e.g. HZ_PARTIES, HZ_PARTY_SITES)
4655 --
4656 -- IN : p_batch_id NUMBER Required, ID of the batch in which the
4657 -- Party Merge is executed
4658 --
4659 -- IN : p_Batch_Party_id NUMBER Required, ID of the batch and Party record
4660 -- for which the Party Merge is executed
4661 --
4662 -- IN OUT : x_return_status VARCHAR2, flag to indicate if the Party Merge procedure
4663 -- was successful or not; It can have the following values -
4664 --
4665 -- FND_API.G_RET_STS_SUCCESS (Success)
4666 -- FND_API.G_RET_STS_UNEXP_ERROR (Not vetoed but
4667 -- failed due to Unexpected error)
4668 -- FND_API.G_RET_STS_ERROR (Failed as the procedure
4669 -- vetod the Party Merge)
4670 --
4671 -- Version : Current version 1.0
4672 -- Previous version 1.0
4673 -- Initial version 1.0
4674 --
4675 -- End of comments
4676 PROCEDURE SUP_ACT_MERGE (
4677 p_Entity_name IN VARCHAR2,
4678 p_from_id IN NUMBER,
4679 x_to_id IN OUT NOCOPY NUMBER ,
4680 p_From_FK_id IN NUMBER,
4681 p_To_FK_id IN NUMBER,
4682 p_Parent_Entity_name IN VARCHAR2,
4683 p_batch_id IN NUMBER,
4684 p_Batch_Party_id IN NUMBER,
4685 x_return_status IN OUT NOCOPY VARCHAR2 )
4686 IS
4687 l_merge_scenario VARCHAR2(20);
4688
4689 BEGIN
4690 --{start of procedure
4691
4692 x_return_status := FND_API.G_RET_STS_SUCCESS;
4693
4694 fnd_file.put_line (fnd_file.log, 'Start : PON_TCA_MERGE_PVT.SUP_ACT_MERGE ');
4695 fnd_file.put_line (fnd_file.log, ' p_Entity_name ' || p_Entity_name);
4696 fnd_file.put_line (fnd_file.log, ' p_from_id ' || p_from_id);
4697 fnd_file.put_line (fnd_file.log, ' p_From_FK_id ' || p_From_FK_id);
4698 fnd_file.put_line (fnd_file.log, ' p_To_FK_id ' || p_To_FK_id);
4699 fnd_file.put_line (fnd_file.log, ' p_Parent_Entity_name ' || p_Parent_Entity_name);
4700 fnd_file.put_line (fnd_file.log, ' p_batch_id ' || p_batch_id);
4701 fnd_file.put_line (fnd_file.log, ' p_Batch_Party_id ' || p_Batch_Party_id);
4702 fnd_file.put_line (fnd_file.log, ' x_return_status ' || x_return_status );
4703
4704 --
4705 -- check the merge scenario
4706 --
4707 l_merge_scenario := CHECK_PERSON_PARTY_MERGE_TYPE (
4708 p_from_id => p_From_FK_id,
4709 p_to_id => p_To_FK_id);
4710
4711 fnd_file.put_line (fnd_file.log, '10 : l_merge_scenario:'||l_merge_scenario);
4712
4713 IF (l_merge_scenario = G_BUYER OR l_merge_scenario = G_INCOMPATIBLE) THEN
4714
4715 fnd_file.put_line (fnd_file.log, '20 : Buyer or Incompatible Merge - Error');
4716 --
4717 -- This can not happen in any normal scenario as
4718 -- PON_SUPPLIER_ACTIVITIES.TRADING_PARTNER_CONTACT_ID
4719 -- can never contain a buyer user id. Thus, raising a veto though
4720 -- it will possibly never be called
4721 --
4722 FND_MESSAGE.SET_NAME('AR', 'PON_TCA_MRG_ERR'); -- Need to put some Sourcing Specific Error Message
4723 FND_MSG_PUB.ADD;
4724 x_return_status := FND_API.G_RET_STS_ERROR;
4725
4726 ELSIF (l_merge_scenario = G_SELLER) THEN
4727
4728 fnd_file.put_line (fnd_file.log, '30 : Seller Merge ');
4729 --
4730 -- there is no chance of unique key constraint violation
4731 -- due to the following logic
4732 --
4733 UPDATE PON_SUPPLIER_ACTIVITIES psa1
4734 SET psa1.TRADING_PARTNER_CONTACT_ID = p_To_FK_id,
4735 psa1.LAST_UPDATED_BY = -1,
4736 psa1.LAST_UPDATE_DATE = sysdate
4737 WHERE psa1.TRADING_PARTNER_CONTACT_ID = p_From_FK_id
4738 AND NOT EXISTS (SELECT 'DUPLICATE'
4739 FROM PON_SUPPLIER_ACTIVITIES psa2
4740 WHERE psa2.AUCTION_HEADER_ID_ORIG_AMEND = psa1.AUCTION_HEADER_ID_ORIG_AMEND
4741 AND psa2.LAST_ACTIVITY_TIME = psa1.LAST_ACTIVITY_TIME
4742 AND psa2.TRADING_PARTNER_ID = psa1.TRADING_PARTNER_ID
4743 AND psa2.TRADING_PARTNER_CONTACT_ID = p_To_FK_id );
4744
4745
4746 fnd_file.put_line (fnd_file.log, '50 : Seller Merge - Updated TRADING_PARTNER_CONTACT_IDs in PON_SUPPLIER_ACTIVITIES: '||sql%rowcount||' rows');
4747
4748 ELSIF (l_merge_scenario = G_IRRELEVANT) THEN
4749 --
4750 -- Nothing to do
4751 --
4752 fnd_file.put_line (fnd_file.log, '60 : Irrelevent Merge');
4753 NULL;
4754 END IF;
4755
4756 EXCEPTION
4757 WHEN OTHERS THEN
4758
4759 fnd_file.put_line (fnd_file.log, '70 : Error in PON_TCA_PARTY_MERGE.SUP_ACT_MERGE SQLERRM:'||SQLERRM);
4760 --
4761 -- We need add some Sourcing specific message for this
4762 --
4763 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4764 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4765 FND_MSG_PUB.ADD;
4766
4767 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4768
4769 --} end of procedure
4770 END SUP_ACT_MERGE;
4771
4772
4773
4774 END PON_TCA_MERGE_PVT; --}
[Home] [Help]