1 PACKAGE BODY FTE_PARTY_MERGE as
2 /* $Header: FTEPAMRB.pls 115.1 2004/05/12 23:56:38 arguha noship $ */
3
4 PROCEDURE merge_facility_contacts(
5 p_entity_name IN VARCHAR2,
6 p_from_id IN NUMBER,
7 p_to_id IN OUT NOCOPY NUMBER,
8 p_from_fk_id IN NUMBER,
9 p_to_fk_id IN NUMBER,
10 p_parent_entity_name IN VARCHAR2,
11 p_batch_id IN NUMBER,
12 p_batch_party_id IN NUMBER,
13 x_return_status IN OUT NOCOPY VARCHAR2)
14 IS
15
16 l_merge_reason_code VARCHAR2(30);
17 l_api_name VARCHAR2(30) := 'merge_facility_contacts';
18 l_count NUMBER(10) := 0;
19 l_facility_id NUMBER := 0;
20 RESOURCE_BUSY EXCEPTION;
21 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
22
23 BEGIN
24
25 x_return_status := FND_API.G_RET_STS_SUCCESS;
26
27 SELECT merge_reason_code
28 INTO l_merge_reason_code
29 FROM hz_merge_batch
30 WHERE batch_id = p_batch_id;
31
32 IF l_merge_reason_code = 'DUPLICATE' THEN
33 -- if reason code is duplicate then allow the party merge to happen without
34 -- any validations.
35 null;
36 ELSE
37 -- if there are any validations to be done, include it in this section
38 null;
39 END IF;
40
41 -- If the parent has NOT changed (ie.Parent getting transferred) then nothing
42 -- needs to be done. Set Merged To Id is same as Merged From Id and return
43
44 IF p_from_fk_id = p_to_fk_id THEN
45 p_to_id := p_from_id;
46 RETURN;
47 END IF;
48
49 -- If the parent has changed(ie. Parent is getting merged) then transfer the
50 -- dependent record to the new parent.
51 -- Before transferring check if a similar
52 -- dependent record exists on the new parent.
53
54 -- Similar facility is equivalent to same facility
55 -- No Duplicate check as one facility can never have more than one
56 -- facility contact id
57
58 IF p_from_fk_id <> p_to_fk_id THEN
59 -- obtain lock on records to be updated.
60 SELECT facility_id
61 INTO l_facility_id
62 FROM fte_location_parameters
63 WHERE facility_contact_id = p_from_fk_id
64 FOR UPDATE NOWAIT;
65 IF (p_parent_entity_name = 'HZ_PARTIES') THEN
66
67 UPDATE fte_location_parameters
68 SET facility_contact_id = p_to_fk_id,
69 last_update_date = hz_utility_v2pub.last_update_date,
70 last_updated_by = hz_utility_v2pub.user_id,
71 last_update_login = hz_utility_v2pub.last_update_login,
72 request_id = hz_utility_v2pub.request_id,
73 program_application_id = hz_utility_v2pub.program_application_id,
74 program_id = hz_utility_v2pub.program_id,
75 program_update_date = sysdate
76 WHERE facility_contact_id = p_from_fk_id;
77
78 END IF;
79
80 l_count := sql%rowcount;
81
82 END IF;
83
84 EXCEPTION
85 WHEN RESOURCE_BUSY THEN
86 FND_MESSAGE.SET_NAME('FTE','FTE_FACILITY_LOCK');
87 FND_MSG_PUB.ADD;
88 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
89 WHEN OTHERS THEN
90 FND_MESSAGE.SET_NAME('AR','HZ_API_OTHERS_EXCEP');
91 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
92 FND_MSG_PUB.ADD;
93 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
94
95 END merge_facility_contacts;
96
97 END FTE_PARTY_MERGE;