1 PACKAGE BODY RRS_PARTY_MERGE_PKG as
2 /* $Header: RRSPMRGB.pls 120.3 2005/12/15 06:39 pfarkade noship $ */
3 --========================================================================
4 -- PROCEDURE :merge_site_party
5 -- PARAMETERS:
6 -- p_entity_name Name of Entity Being Merged
7 -- p_from_id Primary Key Id of the entity that is being merged
8 -- p_to_id The record under the 'To Parent' that is being merged
9 -- p_from_fk_id Foreign Key id of the Old Parent Record
10 -- p_to_fk_id Foreign Key id of the New Parent Record
11 -- p_parent_entity_name Name of Parent Entity
12 -- p_batch_id Id of the Batch
13 -- p_batch_party_id Id uniquely identifies the batch and party record that is being merged
14 -- x_return_status Returns the staus of call
15 --
16 -- COMMENT : Merge of Real Estate party with another Real Estate or Non-Real Estate party is not allowed.
17 -- When an External Party is getting merged update the records in RRS_SITES_B.
18 --========================================================================
19 PROCEDURE MERGE_SITE_PARTY(
20 p_entity_name IN VARCHAR2,
21 p_from_id IN NUMBER,
22 p_to_id IN NUMBER,
23 p_from_fk_id IN NUMBER,
24 p_to_fk_id IN NUMBER,
25 p_parent_entity_name IN VARCHAR2,
26 p_batch_id IN NUMBER,
27 p_batch_party_id IN NUMBER,
28 x_return_status OUT NOCOPY VARCHAR2) IS
29
30
31 Cursor c_sites(c_party_id NUMBER,c_site_type VARCHAR2) IS
32 SELECT 1 FROM dual
33 WHERE EXISTS(
34 SELECT site_party_id
35 FROM rrs_sites_b
36 WHERE site_type_code = c_site_type
37 AND site_party_id = c_party_id);
38 l_from_sites NUMBER;
39 l_to_sites NUMBER;
40 l_sites_external NUMBER;
41 BEGIN
42
43 x_return_status := FND_API.G_RET_STS_SUCCESS;
44 IF (p_from_fk_id <> p_to_fk_id) THEN
45 IF (p_parent_entity_name = 'HZ_PARTIES') THEN
46
47 OPEN c_sites(p_from_fk_id,'I');
48 FETCH c_sites into l_from_sites;
49 IF ( c_sites%FOUND ) THEN
50 x_return_status := FND_API.G_RET_STS_ERROR;
51 FND_MESSAGE.SET_NAME('RRS','RRS_SITE_NO_FROM_MERGE');
52 FND_MSG_PUB.ADD;
53 END IF;
54 CLOSE c_sites;
55
56 /*Commented the code as our merge API will be called only when p_from_fk_id is SITE_PARTY_ID.
57 In this case p_from_fk_id is created from any other system and this check will be effective only when
58 it is done in the merge API registered by that particular product.*/
59 /*OPEN c_sites(p_to_fk_id,'I');
60 FETCH c_sites into l_to_sites;
61 IF ( c_sites%FOUND) THEN
62 x_return_status := FND_API.G_RET_STS_ERROR;
63 FND_MESSAGE.SET_NAME('RRS','RRS_SITE_NO_TO_MERGE');
64 FND_MSG_PUB.ADD;
65 END IF;
66 CLOSE c_sites;*/
67
68 OPEN c_sites(p_from_fk_id,'E');
69 FETCH c_sites into l_sites_external;
70 IF (c_sites%FOUND) THEN
71 UPDATE rrs_sites_b
72 SET site_party_id = p_to_fk_id,
73 last_update_date = SYSDATE,
74 last_updated_by = FND_GLOBAL.user_id,
75 last_update_login = FND_GLOBAL.user_id
76 WHERE site_party_id = p_from_fk_id;
77 END IF;
78 CLOSE c_sites;
79
80 END IF;
81 END IF;
82
83 EXCEPTION WHEN OTHERS THEN
84 FND_MESSAGE.SET_NAME('AR','HZ_API_OTHERS_EXCEP');
85 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
86 FND_MSG_PUB.ADD;
87 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
88
89 END MERGE_SITE_PARTY;
90 --========================================================================
91 -- PROCEDURE :merge_le_party
92 -- PARAMETERS:
93 -- p_entity_name Name of Entity Being Merged
94 -- p_from_id Primary Key Id of the entity that is being merged
95 -- p_to_id The record under the 'To Parent' that is being merged
96 -- p_from_fk_id Foreign Key id of the Old Parent Record
97 -- p_to_fk_id Foreign Key id of the New Parent Record
98 -- p_parent_entity_name Name of Parent Entity
99 -- p_batch_id Id of the Batch
100 -- p_batch_party_id Id uniquely identifies the batch and party record that is being merged
101 -- x_return_status Returns the staus of call
102 --
103 -- COMMENT : When an Legal Entity Party is getting merged update the records in RRS_SITES_B.
104 --========================================================================
105
106 PROCEDURE MERGE_LE_PARTY(
107 p_entity_name IN VARCHAR2,
108 p_from_id IN NUMBER,
109 p_to_id IN NUMBER,
110 p_from_fk_id IN NUMBER,
111 p_to_fk_id IN NUMBER,
112 p_parent_entity_name IN VARCHAR2,
113 p_batch_id IN NUMBER,
114 p_batch_party_id IN NUMBER,
115 x_return_status OUT NOCOPY VARCHAR2) IS
116
117 Cursor get_sites_for_update(c_party_id IN NUMBER) IS
118 SELECT 1 FROM DUAL
119 WHERE EXISTS(
120 SELECT le_party_id
121 FROM rrs_sites_b
122 WHERE le_party_id = c_party_id);
123
124 l_from_site_num NUMBER;
125
126 BEGIN
127
128 x_return_status := FND_API.G_RET_STS_SUCCESS;
129
130 IF (p_from_fk_id <> p_to_fk_id) THEN
131 IF (p_parent_entity_name = 'HZ_PARTIES') THEN
132
133 OPEN get_sites_for_update(p_from_fk_id);
134 FETCH get_sites_for_update INTO l_from_site_num;
135 IF ( get_sites_for_update%FOUND ) THEN
136 UPDATE rrs_sites_b
137 SET le_party_id = p_to_fk_id,
138 last_update_date = SYSDATE,
139 last_updated_by = FND_GLOBAL.user_id,
140 last_update_login = FND_GLOBAL.user_id
141 WHERE le_party_id = p_from_fk_id;
142 END IF;
143 CLOSE get_sites_for_update;
144 END IF;
145 END IF;
146 EXCEPTION
147 WHEN others THEN
148 FND_MESSAGE.SET_NAME('AR','HZ_API_OTHERS_EXCEP');
149 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
150 FND_MSG_PUB.ADD;
151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
152 END MERGE_LE_PARTY;
153
154 END RRS_PARTY_MERGE_PKG;