1 package body XLE_PARTY_MERGE_PUB AS
2 /* $Header: xleptymergeb.pls 120.4 2006/03/31 06:01:02 cjain ship $ */
3
4 -- Should not be called by any other application
5 --
6 -- COMMENT : This procedure is used to perform for following actions
7 -- When the relationship party merges
8 -- the corresponding recipient_id need to be merged
9 --========================================================================
10 PROCEDURE merge_registrations(
11 p_Entity_name IN VARCHAR2, -- Name of the Entity being merged
12 p_from_id IN NUMBER, -- PK of the Party ID being merged
13 p_to_id IN OUT NOCOPY NUMBER, -- PK of the target Party ID; returned if duplicate
14 p_From_FK_id IN NUMBER, -- same as p_from_id
15 p_To_FK_id IN NUMBER, -- same as p_to_id
16 p_Parent_Entity_name IN VARCHAR2, -- should always be 'HZ_PARTIES' or 'HZ_PARTY_STIES'
17 p_batch_id IN NUMBER, -- Batch ID running the merge
18 p_Batch_Party_id IN NUMBER, -- same as the From Party ID
19 x_return_status OUT NOCOPY VARCHAR2)
20
21 IS
22
23 l_merge_reason_code VARCHAR2(30);
24 RESOURCE_BUSY EXCEPTION;
25 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
26
27 Cursor C1 is
28 Select 'X' from
29 XLE_REGISTRATIONS
30 Where issuing_authority_id = p_from_fk_id
31 for update nowait;
32
33 Cursor C2 is
34 Select 'X' from
35 XLE_REGISTRATIONS
36 Where issuing_authority_site_id = p_from_fk_id
37 for update nowait;
38
39 BEGIN
40 x_return_status := FND_API.G_RET_STS_SUCCESS;
41
42 --check the merge reason, if merge reason is 'Duplicate Record' then no validation is performed.
43 --otherwise check if the resource is being used somewhere
44 SELECT merge_reason_code
45 INTO l_merge_reason_code
46 FROM hz_merge_batch
47 WHERE batch_id = p_batch_id;
48
49 IF l_merge_reason_code = 'DUPLICATE' THEN
50 -- if reason code is duplicate then allow the party merge to happen without
51 -- any validations.
52 null;
53 ELSE
54 -- if there are any validations to be done, include it in this section
55 null;
56 END IF;
57
58 /* Perform the Merge */
59
60 /* If Parent (i.e., Party ID) has NOT changed, then nothing needs to be done. Set
61 Merged To ID is the same as Merged From ID and Return */
62
63 IF p_from_FK_id = p_to_FK_id THEN
64 p_to_id := p_from_id;
65
66 RETURN;
67 END IF;
68
69 /********************************************************************************
70 If the Party_ID (Parent) has changed, then transfer the dependent record to the
71 new parent.
72 *******************************************************************************/
73
74 IF p_from_FK_id <> p_to_FK_id THEN
75 IF p_parent_entity_name = 'HZ_PARTIES' THEN
76
77 fnd_message.set_name('XLE', 'XLE_LOCKING_TABLE');
78 fnd_message.set_token('TABLE_NAME', 'XLE_REGISTRATIONS', FALSE);
79
80 Open C1;
81 Close C1;
82
83 fnd_message.set_name('XLE', 'XLE_UPDATING_TABLE');
84 fnd_message.set_token('TABLE_NAME', 'XLE_REGISTRATIONS', FALSE);
85
86 /* Update the "From" Party_ID to be equal to the new target */
87 UPDATE XLE_REGISTRATIONS
88 SET issuing_authority_id = p_To_FK_id,
89 LAST_UPDATED_BY = hz_utility_pub.user_id,
90 LAST_UPDATE_DATE = hz_utility_pub.last_update_date,
91 LAST_UPDATE_LOGIN = hz_utility_pub.last_update_login
92 WHERE issuing_authority_id = p_From_FK_id;
93
94 RETURN;
95
96 ELSIF p_Parent_Entity_name = 'HZ_PARTY_SITES' THEN
97
98 fnd_message.set_name('XLE', 'XLE_LOCKING_TABLE');
99 fnd_message.set_token('TABLE_NAME', 'XLE_REGISTRATIONS', FALSE);
100
101 Open C2;
102 Close C2;
103
104 fnd_message.set_name('XLE', 'XLE_UPDATING_TABLE');
105 fnd_message.set_token('TABLE_NAME', 'XLE_REGISTRATIONS', FALSE);
106
107 UPDATE XLE_REGISTRATIONS
108 SET issuing_authority_site_id = p_To_FK_id,
109 LAST_UPDATED_BY = hz_utility_pub.user_id,
110 LAST_UPDATE_DATE = hz_utility_pub.last_update_date,
111 LAST_UPDATE_LOGIN = hz_utility_pub.last_update_login
112 WHERE issuing_authority_site_id = p_From_FK_id;
113 RETURN;
114 END IF;
115 END IF;
116
117 EXCEPTION
118 WHEN RESOURCE_BUSY THEN
119 FND_MESSAGE.SET_NAME('XLE','XLE_REGISTRATIONS_LOCK');
120 FND_MSG_PUB.ADD;
121 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
122 WHEN OTHERS THEN
123 FND_MESSAGE.SET_NAME('XLE','HZ_API_OTHERS_EXCEP');
124 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
125 FND_MSG_PUB.ADD;
126 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
127
128 END merge_registrations;
129
130
131 --========================================================================
132 -- PROCEDURE : merge_reg_functions Called by HZ Party merge routine
133 -- Should not be called by any other application
134 --
135 -- COMMENT : This procedure is used to perform for following actions
136 -- When the relationship party merges
137 -- the corresponding party_id needs to be merged
138 --========================================================================
139 PROCEDURE merge_reg_functions(
140 p_Entity_name IN VARCHAR2, -- Name of the Entity being merged
141 p_from_id IN NUMBER, -- PK of the Party ID being merged
142 p_to_id IN OUT NOCOPY NUMBER, -- PK of the target Party ID; returned if duplicate
143 p_From_FK_id IN NUMBER, -- same as p_from_id
144 p_To_FK_id IN NUMBER, -- same as p_to_id
145 p_Parent_Entity_name IN VARCHAR2, -- should always be 'HZ_PARTIES' or 'HZ_PARTY_SITES'
146 p_batch_id IN NUMBER, -- Batch ID running the merge
147 p_Batch_Party_id IN NUMBER, -- same as the From Party ID
148 x_return_status OUT NOCOPY VARCHAR2)
149
150 IS
151
152 l_merge_reason_code VARCHAR2(30);
153 RESOURCE_BUSY EXCEPTION;
154 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
155
156 Cursor C1 is
157 Select 'X' from
158 XLE_REG_FUNCTIONS
159 Where authority_id = p_from_fk_id
160 for update nowait;
161
162 Cursor C2 is
163 Select 'X' from
164 XLE_REG_FUNCTIONS
165 Where authority_site_id = p_from_fk_id
166 for update nowait;
167
168 BEGIN
169
170 x_return_status := FND_API.G_RET_STS_SUCCESS;
171
172 --check the merge reason, if merge reason is 'Duplicate Record' then no validation is performed.
173 --otherwise check if the resource is being used somewhere
174 SELECT merge_reason_code
175 INTO l_merge_reason_code
176 FROM hz_merge_batch
177 WHERE batch_id = p_batch_id;
178
179 IF l_merge_reason_code = 'DUPLICATE' THEN
180 -- if reason code is duplicate then allow the party merge to happen without
181 -- any validations.
182 null;
183 ELSE
184 -- if there are any validations to be done, include it in this section
185 null;
186 END IF;
187
188 /* Perform the Merge */
189
190 /* If Parent (i.e., Party ID) has NOT changed, then nothing needs to be done. Set
191 Merged To ID is the same as Merged From ID and Return */
192
193 IF p_from_FK_id = p_to_FK_id THEN
194 p_to_id := p_from_id;
195
196 RETURN;
197 END IF;
198
199 /********************************************************************************
200 If the Party_ID (Parent) has changed, then transfer the dependent record to the
201 new parent.
202 *******************************************************************************/
203
204 IF p_from_FK_id <> p_to_FK_id THEN
205
206 IF p_parent_entity_name = 'HZ_PARTIES' THEN
207
208 fnd_message.set_name('XLE', 'XLE_LOCKING_TABLE');
209 fnd_message.set_token('TABLE_NAME', 'XLE_REG_FUNCTIONS', FALSE);
210
211 Open C1;
212 Close C1;
213
214 fnd_message.set_name('XLE', 'XLE_UPDATING_TABLE');
215 fnd_message.set_token('TABLE_NAME', 'XLE_REG_FUNCTIONS', FALSE);
216
217 /* Update the "From" Party_ID to be equal to the new target */
218 UPDATE XLE_REG_FUNCTIONS
219 SET authority_id = p_To_FK_id,
220 LAST_UPDATED_BY = hz_utility_pub.user_id,
221 LAST_UPDATE_DATE = hz_utility_pub.last_update_date,
222 LAST_UPDATE_LOGIN = hz_utility_pub.last_update_login
223 WHERE authority_id = p_From_FK_id;
224 RETURN;
225
226 ELSIF p_Parent_Entity_name = 'HZ_PARTY_SITES' THEN
227
228 fnd_message.set_name('XLE', 'XLE_LOCKING_TABLE');
229 fnd_message.set_token('TABLE_NAME', 'XLE_REG_FUNCTIONS', FALSE);
230
231 Open C2;
232 Close C2;
233
234 fnd_message.set_name('XLE', 'XLE_UPDATING_TABLE');
235 fnd_message.set_token('TABLE_NAME', 'XLE_REG_FUNCTIONS', FALSE);
236
237 UPDATE XLE_REG_FUNCTIONS
238 SET authority_site_id = p_To_FK_id,
239 LAST_UPDATED_BY = hz_utility_pub.user_id,
240 LAST_UPDATE_DATE = hz_utility_pub.last_update_date,
241 LAST_UPDATE_LOGIN = hz_utility_pub.last_update_login
242 WHERE authority_site_id = p_From_FK_id;
243 RETURN;
244 END IF;
245 END IF;
246 EXCEPTION
247 WHEN RESOURCE_BUSY THEN
248 FND_MESSAGE.SET_NAME('XLE','XLE_REG_FUNCTIONS');
249 FND_MSG_PUB.ADD;
250 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
251 WHEN OTHERS THEN
252 FND_MESSAGE.SET_NAME('XLE','HZ_API_OTHERS_EXCEP');
253 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
254 FND_MSG_PUB.ADD;
255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
256
257
258 END merge_reg_functions;
259
260 --========================================================================
261 -- PROCEDURE : merge_legal_entities Called by HZ Party merge routine
262 -- Should not be called by any other application
263 --
264 -- COMMENT : This procedure is used to perform for following actions
265 -- When the relationship party merges
266 -- It should veto the merging of legal entities.
267 --========================================================================
268
269 PROCEDURE merge_legal_entities(
270 p_entity_name IN VARCHAR2,
271 p_from_id IN NUMBER,
272 p_to_id IN OUT NOCOPY NUMBER,
273 p_from_fk_id IN NUMBER,
274 p_to_fk_id IN NUMBER,
275 p_parent_entity_name IN VARCHAR2,
276 p_batch_id IN NUMBER,
277 p_batch_party_id IN NUMBER,
278 x_return_status OUT NOCOPY VARCHAR2)
279
280 IS
281
282 BEGIN
283
284 -- always veto legal entity merge
285
286 fnd_message.set_name('XLE','XLE_LEGAL_ENTITY_VETO_MERGE');
287 fnd_msg_pub.ADD;
288 x_return_status := fnd_api.g_ret_sts_error ;
289
290 EXCEPTION
291 WHEN OTHERS THEN
292 FND_MESSAGE.SET_NAME('XLE','HZ_API_OTHERS_EXCEP');
293 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
294 FND_MSG_PUB.ADD;
295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
296 END merge_legal_entities;
297
298 --========================================================================
299 -- PROCEDURE : merge_establishments Called by HZ Party merge routine
300 -- Should not be called by any other application
301 --
302 -- COMMENT : This procedure is used to perform for following actions
303 -- When the relationship party merges
304 -- It should veto the merging of establishments.
305 --========================================================================
306
307 PROCEDURE merge_establishments(
308 p_entity_name IN VARCHAR2,
309 p_from_id IN NUMBER,
310 p_to_id IN OUT NOCOPY NUMBER,
311 p_from_fk_id IN NUMBER,
312 p_to_fk_id IN NUMBER,
313 p_parent_entity_name IN VARCHAR2,
314 p_batch_id IN NUMBER,
315 p_batch_party_id IN NUMBER,
316 x_return_status OUT NOCOPY VARCHAR2)
317
318 IS
319 BEGIN
320
321 -- always veto establishments merge
322
323 fnd_message.set_name('XLE','XLE_ESTABLISHMENT_VETO_MERGE');
324 fnd_msg_pub.ADD;
325 x_return_status := fnd_api.g_ret_sts_error ;
326
327 EXCEPTION
328 WHEN OTHERS THEN
329 FND_MESSAGE.SET_NAME('XLE','HZ_API_OTHERS_EXCEP');
330 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
331 FND_MSG_PUB.ADD;
332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
333 END merge_establishments;
334
335
336
337 END XLE_PARTY_MERGE_PUB;
338
[Home] [Help]