[Home] [Help]
PACKAGE BODY: APPS.HZ_MERGE_PARTIES_PUB
Source
1 PACKAGE BODY HZ_MERGE_PARTIES_PUB AS
2 /*$Header: ARHMRGPB.pls 120.9 2005/12/06 06:25:41 ansingha noship $ */
3
4 ----------------------------------
5 -- declaration of global variables
6 ----------------------------------
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'HZ_MERGE_PARTIES_PUB';
9
10 ------------------------------------
11 -- declaration of private procedures
12 ------------------------------------
13
14 PROCEDURE do_create_merge_party(
15 p_batch_id IN NUMBER,
16 p_merge_type IN VARCHAR2,
17 p_from_party_id IN NUMBER,
18 p_to_party_id IN NUMBER,
19 p_merge_reason_code IN VARCHAR2,
20 x_batch_party_id OUT NOCOPY NUMBER,
21 x_return_status IN OUT NOCOPY VARCHAR2
22 );
23
24 ---for validating lookup code merge_reason_code
25 PROCEDURE validate_merge_party (
26 p_create_update_flag IN VARCHAR2,
27 p_batch_id IN NUMBER,
28 p_merge_type IN VARCHAR2,
29 p_from_party_id IN NUMBER,
30 p_to_party_id IN NUMBER,
31 p_merge_reason_code IN VARCHAR2,
32 x_return_status IN OUT NOCOPY VARCHAR2);
33
34
35 PROCEDURE check_party_hq(cp_batch_id IN NUMBER,
36 cp_from_party_id IN NUMBER,
37 cp_to_party_id IN NUMBER);
38
39 PROCEDURE insert_party_details(cp_batch_party_id IN NUMBER,
40 cp_from_party_id IN NUMBER,
41 cp_to_party_id IN NUMBER);
42
43
44 PROCEDURE insert_reln_parties(cp_batch_party_id IN NUMBER,
45 cp_batch_id IN NUMBER);
46
47
48 -----------------------------
49 -- body of private procedures
50 -----------------------------
51
52 /**==========================================================================+
53 | PROCEDURE
54 | do_create_party
55 |
56 | DESCRIPTION
57 | Creates party.
58 |
59 | SCOPE - PRIVATE
60 |
61 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
62 |
63 | ARGUMENTS : IN:
64 | OUT:
65 | x_batch_party_id
66 | IN/ OUT:
67 | p_batch_id,
68 | p_merge_type
69 | p_from_party_id
70 | p_to_party_id
71 | p_merge_reason_code
72 | x_return_status
73 |
74 | RETURNS : NONE
75 |
76 | NOTES
77 |
78 | MODIFICATION HISTORY
79 |
80 +===========================================================================**/
81 PROCEDURE do_create_merge_party(
82 p_batch_id IN NUMBER,
83 p_merge_type IN VARCHAR2,
84 p_from_party_id IN NUMBER,
85 p_to_party_id IN NUMBER,
86 p_merge_reason_code IN VARCHAR2,
87 x_batch_party_id OUT NOCOPY NUMBER,
88 x_return_status IN OUT NOCOPY VARCHAR2
89 ) IS
90 BEGIN
91
92 x_batch_party_id := NULL;
93
94 -- validate the merge_batch _rec record
95 HZ_MERGE_PARTIES_PUB.validate_merge_party(
96 'C' ,
97 p_batch_id,
98 p_merge_type,
99 p_from_party_id,
100 p_to_party_id,
101 p_merge_reason_code,
102 x_return_status);
103
104 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
105 RAISE FND_API.G_EXC_ERROR;
106 END IF;
107
108 --Check the From Party and the HQ Branch
109 check_party_hq(p_batch_id ,
110 p_from_party_id ,
111 p_to_party_id );
112
113
114
115 -- call table-handler to insert the record
116 HZ_MERGE_PARTIES_PKG.INSERT_ROW(
117 px_BATCH_PARTY_ID => x_batch_party_id,
118 p_batch_id => p_batch_id,
119 p_merge_type => p_merge_type,
120 p_from_party_id => p_from_party_id,
121 p_to_party_id => p_to_party_id,
122 p_merge_reason_code => p_MERGE_REASON_CODE,
123 p_merge_status => 'PENDING',
124 p_CREATED_BY => HZ_UTILITY_PUB.CREATED_BY,
125 p_CREATION_DATE => HZ_UTILITY_PUB.CREATION_DATE,
126 p_LAST_UPDATE_LOGIN => HZ_UTILITY_PUB.LAST_UPDATE_LOGIN,
127 p_LAST_UPDATE_DATE => HZ_UTILITY_PUB.LAST_UPDATE_DATE,
128 p_LAST_UPDATED_BY => HZ_UTILITY_PUB.LAST_UPDATED_BY);
129
130 END do_create_merge_party;
131
132
133 PROCEDURE insert_party_details( cp_batch_party_id IN NUMBER,
134 cp_from_party_id IN NUMBER,
135 cp_to_party_id IN NUMBER) IS
136 BEGIN
137 -----Insert Party Site details
138 hz_merge_util.insert_party_site_details(
139 cp_from_party_id,
140 cp_to_party_id,
141 cp_batch_party_id,
142 HZ_UTILITY_PUB.CREATED_BY,
143 HZ_UTILITY_PUB.CREATION_DATE,
144 HZ_UTILITY_PUB.LAST_UPDATE_LOGIN,
145 HZ_UTILITY_PUB.LAST_UPDATE_DATE,
146 HZ_UTILITY_PUB.LAST_UPDATED_BY);
147
148 -----Insert Party Relations details
149 hz_merge_util.insert_party_reln_details(
150 cp_from_party_id,
151 cp_to_party_id,
152 cp_batch_party_id,
153 HZ_UTILITY_PUB.CREATED_BY,
154 HZ_UTILITY_PUB.CREATION_DATE,
155 HZ_UTILITY_PUB.LAST_UPDATE_LOGIN,
156 HZ_UTILITY_PUB.LAST_UPDATE_DATE,
157 HZ_UTILITY_PUB.LAST_UPDATED_BY);
158
159 END insert_party_details;
160
161
162 PROCEDURE insert_reln_parties( cp_batch_party_id IN NUMBER,
163 cp_batch_id IN NUMBER)IS
164
165 CURSOR merged_relns(cp_batch_party_id NUMBER) IS
166 SELECT merge_from_entity_id, merge_to_entity_id,
167 HZ_MERGE_UTIL.get_reln_party_id(merge_from_entity_id) from_reln_party_id,
168 HZ_MERGE_UTIL.get_reln_party_id(merge_to_entity_id) to_reln_party_id
169 FROM hz_merge_party_details
170 WHERE batch_party_id = cp_batch_party_id
171 AND entity_name = 'HZ_PARTY_RELATIONSHIPS'
172 AND merge_to_entity_id IS NOT NULL
173 AND merge_from_entity_id IS NOT NULL
174 AND merge_from_entity_id <> merge_to_entity_id;
175
176 l_from_rel_id NUMBER := NULL;
177 l_to_rel_id NUMBER := NULL;
178 l_from_reln_party_id NUMBER := NULL;
179 l_to_reln_party_id NUMBER := NULL;
180
181 l_batch_party_id NUMBER := NULL;
182
183 num NUMBER := 0;
184 BEGIN
185
186
187 OPEN merged_relns(cp_batch_party_id);
188 LOOP
189 FETCH merged_relns INTO l_from_rel_id, l_to_rel_id,
190 l_from_reln_party_id, l_to_reln_party_id;
191 EXIT WHEN merged_relns%NOTFOUND;
192
193 /* IF :BATCH.BATCH_COMMIT = 'R' THEN
194 CLOSE merged_relns;
195 FND_MESSAGE.set_name('AR', 'HZ_RECORD_COMMIT_NOT_ALLOWED');
196 FND_MESSAGE.error;
197 RAISE FORM_TRIGGER_FAILURE;
198 END IF;*/
199
200 IF l_to_reln_party_id IS NOT NULL AND
201 l_from_reln_party_id IS NOT NULL THEN
202
203 l_batch_party_id := null;
204
205 HZ_MERGE_PARTIES_PKG.Insert_Row(
206 l_batch_party_id,
207 cp_BATCH_ID,
208 'PARTY_MERGE',
209 l_from_reln_party_id,
210 l_to_reln_party_id,
211 'DUPLICATE_RELN_PARTY',
212 'PENDING',
213 HZ_UTILITY_PUB.CREATED_BY,
214 HZ_UTILITY_PUB.CREATION_DATE,
215 HZ_UTILITY_PUB.LAST_UPDATE_LOGIN,
216 HZ_UTILITY_PUB.LAST_UPDATE_DATE,
217 HZ_UTILITY_PUB.LAST_UPDATED_BY);
218
219 num := num+1;
220 END IF;
221 END LOOP;
222 CLOSE merged_relns;
223
224 /* BugNo 3024162 Commented the code If num>0 ... END IF;*/
225 /* IF num>0 THEN
226 fnd_message.set_name('AR','HZ_NUM_RELN_PARTY_REQD');
227 fnd_message.set_token('NUM_PARTIES',TO_CHAR(num));
228 RAISE FND_API.G_EXC_ERROR;
229 END IF;
230 */
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 FND_MESSAGE.SET_NAME('AR','HZ_FORM_DUP_PROC_ERROR');
235 APP_EXCEPTION.RAISE_EXCEPTION;
236 END insert_reln_parties;
237
238 PROCEDURE check_party_hq( cp_batch_id IN NUMBER,
239 cp_from_party_id IN NUMBER,
240 cp_to_party_id IN NUMBER) IS
241
242 l_batch_party_id HZ_MERGE_PARTIES.BATCH_PARTY_ID%TYPE := NULL;
243 l_dummy_count NUMBER := 0;
244
245 CURSOR c_check_from_party( cp_batch_id IN NUMBER ,cp_from_party_id IN NUMBER, cp_to_party_id IN NUMBER ) IS
246 SELECT batch_party_id
247 FROM HZ_MERGE_PARTIES
248 WHERE (from_party_id = cp_from_party_id OR
249 to_party_id = cp_to_party_id OR
250 from_party_id = cp_to_party_id OR
251 to_party_id = cp_from_party_id)
252 AND batch_id = cp_batch_id;
253
254 CURSOR c_check_hq_branch( cp_from_party_id IN NUMBER, cp_to_party_id IN NUMBER ) IS
255 SELECT 1
256 FROM HZ_RELATIONSHIPS --4500011
257 WHERE content_source_type = 'DNB'
258 AND subject_id = cp_from_party_id
259 AND object_id = cp_to_party_id
260 AND RELATIONSHIP_CODE = 'HEADQUARTERS_OF'
261 AND subject_table_name = 'HZ_PARTIES'
262 AND object_table_name = 'HZ_PARTIES'
263 AND directional_flag = 'F';
264
265 BEGIN
266 OPEN c_check_from_party(cp_batch_id ,cp_from_party_id , cp_to_party_id);
267 FETCH c_check_from_party INTO l_batch_party_id;
268 IF c_check_from_party%FOUND THEN
269 CLOSE c_check_from_party;
270 FND_MESSAGE.SET_NAME('AR','HZ_PARTY_ALREADY_IN_BATCH');
271 -----------Srini y'r msg involves party number etc whose value we don't know here
272 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
273 RAISE FND_API.G_EXC_ERROR;
274 END IF;
275 CLOSE c_check_from_party;
276
277 /*OPEN c_check_hq_branch(cp_from_party_id ,cp_to_party_id);
278 FETCH c_check_hq_branch INTO l_dummy_count;
279 IF c_check_hq_branch%FOUND THEN
280 CLOSE c_check_hq_branch;
281 FND_MESSAGE.SET_NAME('AR','HZ_DNB_BRANCH');
282 -----------y'r msg involves party name etc whose value we don't have
283 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
284 RAISE FND_API.G_EXC_ERROR;
285 END IF;
286 CLOSE c_check_hq_branch; */
287
288 END CHECK_PARTY_HQ;
289
290
291 PROCEDURE validate_merge_party (
292 p_create_update_flag IN VARCHAR2,
293 p_batch_id IN NUMBER,
294 p_merge_type IN VARCHAR2,
295 p_from_party_id IN NUMBER,
296 p_to_party_id IN NUMBER,
297 p_merge_reason_code IN VARCHAR2,
298 x_return_status IN OUT NOCOPY VARCHAR2
299 ) IS
300
301 l_dummy VARCHAR2(1);
302
303 BEGIN
304
305 ---------- Validations for Lookup MERGE_REASON_CODE
306 IF p_create_update_flag = 'C' AND p_merge_reason_code is NOT NULL
307 THEN
308 HZ_UTILITY_V2PUB.validate_lookup (
309 p_column => 'merge_reason_code',
310 p_lookup_type => 'MERGE_REASON_CODE',
311 p_column_value => p_merge_reason_code,
312 x_return_status => x_return_status );
313 END IF;
314
315
316 END validate_merge_party;
317
318
319 -----------------------------
320 -- body of public procedures
321 -----------------------------
322
323
324
325 /**==========================================================================+
326 | PROCEDURE
327 | create_merge_batch
328 |
329 | DESCRIPTION
330 | Creates merge_batch.
331 |
332 | SCOPE - PUBLIC
333 |
334 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
335 |
336 | ARGUMENTS : IN:
337 | p_api_version
338 | p_init_msg_list
339 | p_commit
340 | p_merge_batch
341 | p_validation_level
342 | OUT:
343 | x_return_status
344 | x_msg_count
345 | x_msg_data
346 | x_location_id
347 | IN/ OUT:
348 |
349 | RETURNS : NONE
350 |
351 | NOTES
352 |
353 | MODIFICATION HISTORY
354 |
355 +===========================================================================**/
356
357 procedure create_merge_party (
358 p_api_version IN NUMBER,
359 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
360 p_commit IN VARCHAR2:= FND_API.G_FALSE,
361 p_batch_id IN NUMBER,
362 p_merge_type IN VARCHAR2,
363 p_from_party_id IN NUMBER,
364 p_to_party_id IN NUMBER,
365 p_merge_reason_code IN VARCHAR2,
366 x_return_status OUT NOCOPY VARCHAR2,
367 x_msg_count OUT NOCOPY NUMBER,
368 x_msg_data OUT NOCOPY VARCHAR2,
369 x_batch_party_id OUT NOCOPY NUMBER,
370 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL
371 ) IS
372
373 l_api_name CONSTANT VARCHAR2(30) := 'create_merge_batch';
374 l_api_version CONSTANT NUMBER := 1.0;
375 l_batch_id NUMBER := p_batch_id;
376 l_merge_type VARCHAR2(30) := p_merge_type;
377 l_from_party_id NUMBER := p_from_party_id;
378 l_to_party_id NUMBER := p_to_party_id;
379 l_merge_reason_code VARCHAR2(30) := p_merge_reason_code;
380
381 BEGIN
382
383 -- standard start of API savepoint
384 SAVEPOINT create_merge_party;
385
386 -- standard call to check for call compatibility.
387 IF NOT FND_API.Compatible_API_Call(
388 l_api_version,
389 p_api_version,
390 l_api_name,
391 G_PKG_NAME)
392 THEN
393 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
394 END IF;
395
396 -- initialize message list if p_init_msg_list is set to TRUE.
397 IF FND_API.to_Boolean(p_init_msg_list) THEN
398 FND_MSG_PUB.initialize;
399 END IF;
400
401 -- initialize API return status to success.
402 x_return_status := FND_API.G_RET_STS_SUCCESS;
403
404
405 -- call to business logic.
406 do_create_merge_party(
407 l_batch_id,
408 l_merge_type,
409 l_from_party_id,
410 l_to_party_id,
411 l_merge_reason_code,
412 x_batch_party_id,
413 x_return_status);
414
415
416
417 --Call to insert details
418 insert_party_details(x_batch_party_id,
419 l_from_party_id ,
420 l_to_party_id );
421
422 ---Call to insert relationships
423 insert_reln_parties(x_batch_party_id ,
424 l_batch_id );
425
426 -- standard check of p_commit.
427 IF FND_API.to_Boolean(p_commit) THEN
428 COMMIT;
429 END IF;
430
431 -- standard call to get message count and if count is 1, get message info.
432 FND_MSG_PUB.Count_And_Get(
433 p_encoded => FND_API.G_FALSE,
434 p_count => x_msg_count,
435 p_data => x_msg_data);
436
437 EXCEPTION
438 WHEN FND_API.G_EXC_ERROR THEN
439 ROLLBACK TO create_merge_party;
440 x_return_status := FND_API.G_RET_STS_ERROR;
441 FND_MSG_PUB.Count_And_Get(
442 p_encoded => FND_API.G_FALSE,
443 p_count => x_msg_count,
444 p_data => x_msg_data);
445
446 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
447 ROLLBACK TO create_merge_party;
448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
449 FND_MSG_PUB.Count_And_Get(
450 p_encoded => FND_API.G_FALSE,
451 p_count => x_msg_count,
452 p_data => x_msg_data);
453
454 WHEN OTHERS THEN
455 ROLLBACK TO create_merge_party;
456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
457
458 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
459 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
460 FND_MSG_PUB.ADD;
461
462 FND_MSG_PUB.Count_And_Get(
463 p_encoded => FND_API.G_FALSE,
464 p_count => x_msg_count,
465 p_data => x_msg_data);
466
467 END create_merge_party;
468 END HZ_MERGE_PARTIES_PUB;