[Home] [Help]
PACKAGE BODY: APPS.AP_VENDOR_PARTY_MERGE_PKG
Source
1 PACKAGE BODY AP_VENDOR_PARTY_MERGE_PKG AS
2 /* $Header: apvdmrgb.pls 120.22.12020000.2 2012/07/13 19:42:51 mkmeda ship $ */
3
4 --Global constants for logging
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_VENDOR_PARTY_MERGE_PKG';
6 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
7 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
8 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
9 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
10 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
11 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
12 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
13
14 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
15 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
16 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
17 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
18 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
19 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
20 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
21 G_MODULE_NAME CONSTANT VARCHAR2(100)
22 := 'AP.PLSQL.AP_VENDOR_PARTY_MERGE_PKG';
23
24 -- Bug 5641382. Added the parameter v_dup_vendor_id to the procedure
25 PROCEDURE Other_Products_VendorMerge(v_dup_vendor_id IN NUMBER DEFAULT NULL,
26 v_dup_vendor_site_id IN NUMBER DEFAULT NULL)
27 /* Added extra parameter for bug 9501188*/
28 IS
29
30 --
31 l_return_status VARCHAR2(50);
32 l_msg_data VARCHAR2(1000);
33 l_msg_count NUMBER;
34 l_msg_index_out NUMBER;
35
36 /* Changes introduced for bug 9501188 - Start */
37 l_xla_errbuf VARCHAR2 (2000);
38 l_xla_retcode VARCHAR2 (100);
39 l_xla_event_ids xla_third_party_merge_pub.t_event_ids;
40 l_xla_request_id NUMBER;
41 l_xla_ledger_id NUMBER;
42 l_source_id_int_1 NUMBER;
43
44 /* Changes introduced for bug 9501188 - End */
45
46 CURSOR Invoice_Cursor IS
47 SELECT dv.vendor_id C_VENDOR_ID,
48 dv.vendor_site_id C_VENDOR_SITE_ID,
49 dv.duplicate_vendor_id C_DUP_VENDOR_ID,
50 dv.duplicate_vendor_site_id C_DUP_VENDOR_SITE_ID,
51 dv.entry_id C_ENTRY_ID,
52 dv.org_id C_ORG_ID,
53 a.vendor_name C_VENDOR_NAME,
54 b.vendor_name C_DUP_VENDOR_NAME,
55 a.party_id C_PARTY_ID,
56 b.party_id C_DUP_PARTY_ID,
57 c.vendor_site_code C_VENDOR_SITE_CODE,
58 d.vendor_site_code C_DUP_VENDOR_SITE_CODE,
59 c.party_site_id C_PARTY_SITE_ID,
60 d.party_site_id C_DUP_PARTY_SITE_ID,
61 dv.keep_site_flag C_KEEP_SITE_FLAG,
62 dv.paid_invoices_flag C_PAID_INVOICES_FLAG,
63 a.segment1 C_NEW_VENDOR_NUMBER,
64 b.segment1 C_OLD_VENDOR_NUMBER
65 FROM ap_duplicate_vendors_all dv,
66 ap_suppliers a,
67 ap_suppliers b,
68 ap_supplier_sites_all c,
69 ap_supplier_sites_all d
70 WHERE dv.process_flag='S'
71 AND a.vendor_id=dv.vendor_id
72 AND c.vendor_site_id=nvl(dv.vendor_site_id,duplicate_vendor_site_id)
73 AND b.vendor_id=dv.duplicate_vendor_id
74 AND d.vendor_site_id=dv.duplicate_vendor_site_id
75 AND d.org_id = dv.org_id
76 AND dv.process<>'P'
77 /* Added for Bug 5641382 */
78 AND dv.duplicate_vendor_id = NVL(v_dup_vendor_id, dv.duplicate_vendor_id)
79 AND dv.duplicate_vendor_site_id = NVL(v_dup_vendor_site_id, dv.duplicate_vendor_site_id); /* Added for bug 9501188 */
80
81 CURSOR PO_Cursor IS
82 SELECT dv.vendor_id C_VENDOR_ID,
83 dv.vendor_site_id C_VENDOR_SITE_ID,
84 dv.duplicate_vendor_id C_DUP_VENDOR_ID,
85 dv.duplicate_vendor_site_id C_DUP_VENDOR_SITE_ID,
86 dv.entry_id C_ENTRY_ID,
87 dv.org_id C_ORG_ID,
88 a.vendor_name C_VENDOR_NAME,
89 b.vendor_name C_DUP_VENDOR_NAME,
90 a.party_id C_PARTY_ID,
91 b.party_id C_DUP_PARTY_ID,
92 c.vendor_site_code C_VENDOR_SITE_CODE,
93 d.vendor_site_code C_DUP_VENDOR_SITE_CODE,
94 c.party_site_id C_PARTY_SITE_ID,
95 d.party_site_id C_DUP_PARTY_SITE_ID,
96 dv.keep_site_flag C_KEEP_SITE_FLAG,
97 dv.paid_invoices_flag C_PAID_INVOICES_FLAG,
98 a.segment1 C_NEW_VENDOR_NUMBER,
99 b.segment1 C_OLD_VENDOR_NUMBER
100 FROM ap_duplicate_vendors_all dv,
101 ap_suppliers a,
102 ap_suppliers b,
103 ap_supplier_sites_all c,
104 ap_supplier_sites_all d
105 WHERE dv.process_flag in ('S','D')
106 AND a.vendor_id=dv.vendor_id
107 AND c.vendor_site_id=nvl(dv.vendor_site_id,duplicate_vendor_site_id)
108 AND b.vendor_id=dv.duplicate_vendor_id
109 AND d.vendor_site_id=dv.duplicate_vendor_site_id
110 AND d.org_id = dv.org_id
111 AND dv.process<>'I'
112 /* Added for Bug 5641382 */
113 AND dv.duplicate_vendor_id = NVL(v_dup_vendor_id, dv.duplicate_vendor_id)
114 AND dv.duplicate_vendor_site_id = NVL(v_dup_vendor_site_id, dv.duplicate_vendor_site_id); /* Added for bug 9501188 */
115
116
117 l_Invoice_Row Invoice_Cursor%ROWTYPE;
118 l_PO_Row PO_Cursor%ROWTYPE;
119
120 l_count_xla_gt number;
121
122 /* Bug 9551257 Start */
123 l_active_site_count number;
124 p_last_site_flag varchar(3);
125 /* Bug 9551257 End */
126
127 l_org_id NUMBER;
128
129 BEGIN
130 --
131 --
132 -- Process Invoice Related Impact Product Calls.
133
134 /* Changes for bug 9501188 Start */
135
136 l_xla_errbuf := null;
137 l_xla_retcode := null;
138 l_xla_request_id := 0;
139 l_xla_ledger_id := 0;
140 l_source_id_int_1 := 0;
141
142 /* Changes for bug 9501188 End */
143
144 /* Bug 9551257 Start */
145 l_active_site_count := 0;
146 p_last_site_flag := 'N';
147 /* Bug 9551257 End */
148 /* Bug 12555081 - pos not merged after vendor merge completes successfully-start */
149 select org_id
150 into l_org_id
151 from ap_supplier_sites_all
152 where vendor_site_id = v_dup_vendor_site_id;
153
154 mo_global.set_policy_context('S', l_org_id);
155
156 /* Bug 12555081 - pos not merged after vendor merge completes successfully-end */
157
158 OPEN Invoice_Cursor;
159 LOOP
160 FETCH Invoice_Cursor INTO l_Invoice_Row;
161 EXIT WHEN Invoice_Cursor%NOTFOUND;
162
163 IF l_invoice_row.C_KEEP_SITE_FLAG = 'Y' AND
164 l_invoice_row.C_VENDOR_SITE_ID IS NULL THEN
165
166 SELECT vendor_site_id
167 INTO l_invoice_row.C_VENDOR_SITE_ID
168 FROM ap_supplier_sites_all
169 WHERE vendor_id = l_invoice_row.C_VENDOR_ID
170 AND vendor_site_code = l_invoice_row.C_VENDOR_SITE_CODE
171 AND org_id = l_invoice_row.C_ORG_ID; --Bug#7307532
172
173 END IF;
174
175 /*Changes for bug 9551257 Start */
176
177 FND_FILE.Put_Line(FND_FILE.Log,'Calling IBY API - Start');
178
179 SELECT count(apps.vendor_site_id)
180 INTO l_active_site_count
181 FROM ap_suppliers aps, ap_supplier_sites_all apps
182 WHERE aps.vendor_id = l_invoice_row.C_DUP_VENDOR_ID
183 AND aps.vendor_id = apps.vendor_id
184 AND apps.vendor_site_id <> l_invoice_row.C_DUP_VENDOR_SITE_ID
185 AND apps.pay_site_flag = 'Y'
186 AND apps.inactive_date is not null;
187
188 IF l_active_site_count > 0 THEN
189 p_last_site_flag := 'N';
190 ELSE
191 p_last_site_flag := 'Y';
192 END IF;
193
194 FND_FILE.Put_Line(FND_FILE.Log,'In IBY merge call, p_last_site_flag:'||p_last_site_flag);
195
196 IBY_SUPP_BANK_MERGE_PUB.BANK_ACCOUNTS_MERGE(
197 P_from_vendor_id => l_invoice_row.C_DUP_VENDOR_ID,
198 P_to_vendor_id => l_invoice_row.C_VENDOR_ID,
199 P_from_party_id => l_invoice_row.C_DUP_PARTY_ID,
200 P_to_party_id => l_invoice_row.C_PARTY_ID,
201 P_from_vendor_site_id => l_invoice_row.C_DUP_VENDOR_SITE_ID,
202 P_to_vendor_site_id => l_invoice_row.C_VENDOR_SITE_ID,
203 P_from_party_site_id => l_invoice_row.C_DUP_PARTY_SITE_ID,
204 P_to_partysite_id => l_invoice_row.C_PARTY_SITE_ID,
205 P_from_org_id => l_invoice_row.C_ORG_ID,
206 P_to_org_id => l_invoice_row.C_ORG_ID,
207 P_from_org_type => 'OPERATING_UNIT',
208 P_to_org_type => 'OPERATING_UNIT',
209 p_keep_site_flag => l_invoice_row.C_KEEP_SITE_FLAG,
210 p_last_site_flag => p_last_site_flag,
211 X_return_status => l_return_status,
212 X_msg_count => l_msg_count,
213 X_msg_data => l_msg_data
214 );
215
216 FND_FILE.Put_Line(FND_FILE.Log,'In IBY merge call, l_return_status:'||l_return_status);
217
218 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
219 --
220 --
221 IF l_msg_count > 0 THEN
222 --
223 --
224 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
225 FND_MSG_PUB.Get( p_msg_index => i,
226 p_encoded => 'F',
227 p_data => l_msg_data,
228 p_msg_index_out => l_msg_index_out
229 );
230 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
231 End LOOP;
232 END IF;
233 RAISE FND_API.G_EXC_ERROR;
234 END IF;
235
236 /*Changes for bug 9551257 End */
237
238 /* Changes introduced for bug 9501188 - Start */
239
240 FND_FILE.Put_Line(FND_FILE.Log,'Calling XLA API - Start');
241
242 BEGIN
243 SELECT set_of_books_id into l_xla_ledger_id
244 FROM ap_system_parameters_all
245 WHERE org_id = l_invoice_row.C_ORG_ID
246 and rownum = 1;
247
248 FND_FILE.Put_Line(FND_FILE.Log,'Fetching the ledge_id: '||l_xla_ledger_id||' and org_id:'||l_invoice_row.C_ORG_ID);
249
250 EXCEPTION
251 WHEN OTHERS THEN
252 FND_FILE.Put_Line(FND_FILE.Log,' Error fetching set of book id');
253 RAISE FND_API.G_EXC_ERROR;
254 END;
255
256 BEGIN
257 select count(1) into l_count_xla_gt from xla_events_gt;
258
259 EXCEPTION
260 WHEN OTHERS THEN
261 l_count_xla_gt := 0;
262 END;
263
264 FND_FILE.Put_Line(FND_FILE.Log,'third_party_merge l_count_xla_gt:'||l_count_xla_gt);
265 FND_FILE.Put_Line(FND_FILE.Log,'third_party_merge l_invoice_row.C_DUP_VENDOR_ID:'||l_invoice_row.C_DUP_VENDOR_ID||':');
266 FND_FILE.Put_Line(FND_FILE.Log,'third_party_merge l_invoice_row.C_DUP_VENDOR_SITE_ID:'||l_invoice_row.C_DUP_VENDOR_SITE_ID||':');
267 FND_FILE.Put_Line(FND_FILE.Log,'third_party_merge l_invoice_row.C_VENDOR_ID:'||l_invoice_row.C_VENDOR_ID);
268 FND_FILE.Put_Line(FND_FILE.Log,'third_party_merge l_invoice_row.C_VENDOR_SITE_ID:'||l_invoice_row.C_VENDOR_SITE_ID);
269
270 FND_FILE.Put_Line(FND_FILE.Log,'Before call to xla_third_party_merge_pub.third_party_merge...');
271
272 If (nvl(l_count_xla_gt,0) <> 0) then
273 xla_third_party_merge_pub.third_party_merge (x_errbuf => l_xla_errbuf,
274 x_retcode => l_xla_retcode,
275 x_event_ids => l_xla_event_ids,
276 x_request_id => l_xla_request_id,
277 p_application_id => 200,
278 p_ledger_id => l_xla_ledger_id,
279 p_third_party_merge_date => sysdate,
280 p_third_party_type => 'S',
281 p_original_third_party_id => l_invoice_row.C_DUP_VENDOR_ID,
282 p_original_site_id => l_invoice_row.C_DUP_VENDOR_SITE_ID,
283 p_new_third_party_id => l_invoice_row.C_VENDOR_ID,
284 p_new_site_id => l_invoice_row.C_VENDOR_SITE_ID,
285 p_type_of_third_party_merge => 'PARTIAL',
286 p_mapping_flag => 'N',
287 p_execution_mode => 'SYNC',
288 p_accounting_mode => 'F',
289 p_transfer_to_gl_flag => 'Y',
290 p_post_in_gl_flag => 'N'
291 );
292
293 FND_FILE.Put_Line(FND_FILE.Log, 'Call after procedure xla_third_party_merge_pub.third_party_merge:'||l_xla_retcode);
294 IF l_xla_retcode <> 'S' then
295
296 FND_FILE.Put_Line(FND_FILE.Log, 'Error on procedure call xla_third_party_merge_pub.third_party_merge call:'||l_xla_retcode);
297 IF l_xla_errbuf IS NOT NULL THEN
298 FND_FILE.PUT_LINE(FND_FILE.Log,'Error buffer l_xla_errbuf:'||l_xla_errbuf);
299 END IF;
300 RAISE FND_API.G_EXC_ERROR;
301 END IF;
302
303 FOR i IN l_xla_event_ids.FIRST .. l_xla_event_ids.LAST
304 LOOP
305
306 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
307 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||' AP_VENDOR_PARTY_MERGE_PKG.other_products_vendormerge: ',
308 'Merged event_id: '||l_xla_event_ids (i) );
309 END IF;
310
311 END LOOP;
312
313 DELETE FROM xla_events_gt;
314
315 FND_FILE.Put_Line(FND_FILE.Log, 'Deleting the record of xla_events_gt');
316
317 FND_FILE.Put_Line(FND_FILE.Log,'Calling XLA API - End');
318
319 End if;
320 /* Changes introduced for bug 9501188 - End */
321
322
323 FND_FILE.Put_Line(FND_FILE.Log,'Calling WSH API');
324
325 -- Call WSH API
326 WSH_VENDOR_PARTY_MERGE_PKG.Vendor_Party_Merge(
327 p_from_vendor_id => l_invoice_row.C_Dup_Vendor_Id,
328 p_to_vendor_id => l_invoice_row.C_Vendor_Id,
329 p_from_party_id => l_invoice_row.C_Dup_Party_Id,
330 p_to_party_id => l_invoice_row.C_Party_Id,
331 p_from_vendor_site_id => l_invoice_row.C_Dup_Vendor_Site_Id,
332 p_to_vendor_site_id => l_invoice_row.C_Vendor_Site_Id,
333 p_from_party_site_id => l_invoice_row.C_Dup_Party_Site_Id,
334 p_to_partysite_id => l_invoice_row.C_Party_Site_Id,
335 p_calling_mode => 'INVOICE',
336 x_return_status => l_return_status,
337 x_msg_count => l_msg_count,
338 x_msg_data => l_msg_data);
339
340
341 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
342 --
343 --
344 IF l_msg_count > 0 THEN
345 --
346 --
347 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
348 FND_MSG_PUB.Get( p_msg_index => i,
349 p_encoded => 'F',
350 p_data => l_msg_data,
351 p_msg_index_out => l_msg_index_out
352 );
353 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
354 End LOOP;
355 END IF;
356 RAISE FND_API.G_EXC_ERROR;
357 END IF;
358
359 FND_FILE.Put_Line(FND_FILE.Log,'Calling Fixed Assets API');
360
361 -- Fixed Assets
362 FA_VendorMerge_GRP.Merge_Vendor(
363 p_api_version => 1.0
364 , p_init_msg_list => FND_API.G_FALSE
365 , p_commit => FND_API.G_FALSE
366 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
367 , x_return_status => l_return_status
368 , x_msg_count => l_msg_count
369 , x_msg_data => l_msg_data
370 , p_vendor_id => l_invoice_row.C_Vendor_Id
371 , p_dup_vendor_id => l_invoice_row.C_Dup_Vendor_Id
372 , p_vendor_site_id => l_invoice_row.C_Vendor_Site_Id
373 , p_dup_vendor_site_id => l_invoice_row.C_Dup_Vendor_Site_Id
374 , p_party_id => l_invoice_row.C_Party_Id
375 , p_dup_party_id => l_invoice_row.C_Dup_Party_Id
376 , p_party_site_id => l_invoice_row.C_Party_Site_Id
377 , p_dup_party_site_id => l_invoice_row.C_Dup_Party_Site_Id
378 , p_segment1 => l_invoice_row.C_New_Vendor_Number
379 , p_dup_segment1 => l_invoice_row.C_Old_Vendor_Number
380 , p_vendor_name => l_invoice_row.C_Vendor_Name);
381
382 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
383 --
384 --
385 IF l_msg_count > 0 THEN
386 --
387 --
388 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
389 FND_MSG_PUB.Get( p_msg_index => i,
390 p_encoded => 'F',
391 p_data => l_msg_data,
392 p_msg_index_out => l_msg_index_out
393 );
394 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
395 End LOOP;
396 END IF;
397 RAISE FND_API.G_EXC_ERROR;
398 END IF;
399
400 FND_FILE.Put_Line(FND_FILE.Log,'Calling Trade Management API');
401
402 -- Trade Management
403 OZF_VENDORMERGE_GRP.Merge_Vendor(
404 p_api_version => 1.0
405 , p_init_msg_list => FND_API.G_FALSE
406 , p_commit => FND_API.G_FALSE
407 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
408 , p_return_status => l_return_status
409 , p_msg_count => l_msg_count
410 , p_msg_data => l_msg_data
411 , p_vendor_id => l_invoice_row.C_Vendor_Id
412 , p_dup_vendor_id => l_invoice_row.C_Dup_Vendor_Id
413 , p_vendor_site_id => l_invoice_row.C_Vendor_Site_Id
414 , p_dup_vendor_site_id => l_invoice_row.C_Dup_Vendor_Site_Id
415 , p_party_id => l_invoice_row.C_Party_Id
416 , p_dup_party_id => l_invoice_row.C_Dup_Party_Id
417 , p_party_site_id => l_invoice_row.C_Party_Site_Id
418 , p_dup_party_site_id => l_invoice_row.C_Dup_Party_Site_Id);
419
420 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
421 --
422 --
423 IF l_msg_count > 0 THEN
424 --
425 --
426 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
427 FND_MSG_PUB.Get( p_msg_index => i,
428 p_encoded => 'F',
429 p_data => l_msg_data,
430 p_msg_index_out => l_msg_index_out
431 );
432 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
433 End LOOP;
434 END IF;
435 RAISE FND_API.G_EXC_ERROR;
436 END IF;
437
438 FND_FILE.Put_Line(FND_FILE.Log,'Calling CMRO API');
439
440 -- Complex Maintenance Repair and Overhaul
441 AHL_VENDORMERGE_GRP.Merge_Vendor(
442 p_api_version => 1.0
443 , p_init_msg_list => FND_API.G_FALSE
444 , p_commit => FND_API.G_FALSE
445 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
446 , x_return_status => l_return_status
447 , x_msg_count => l_msg_count
448 , x_msg_data => l_msg_data
449 , p_vendor_id => l_invoice_row.C_Vendor_Id
450 , p_dup_vendor_id => l_invoice_row.C_Dup_Vendor_Id
451 , p_vendor_site_id => l_invoice_row.C_Vendor_Site_Id
452 , p_dup_vendor_site_id => l_invoice_row.C_Dup_Vendor_Site_Id
453 , p_party_id => l_invoice_row.C_Party_Id
454 , p_dup_party_id => l_invoice_row.C_Dup_Party_Id
455 , p_party_site_id => l_invoice_row.C_Party_Site_Id
456 , p_dup_party_site_id => l_invoice_row.C_Dup_Party_Site_Id);
457
458 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
459 --
460 --
461 IF l_msg_count > 0 THEN
462 --
463 --
464 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
465 FND_MSG_PUB.Get( p_msg_index => i,
466 p_encoded => 'F',
467 p_data => l_msg_data,
468 p_msg_index_out => l_msg_index_out
469 );
470 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
471 End LOOP;
472 END IF;
473 RAISE FND_API.G_EXC_ERROR;
474 END IF;
475
476 FND_FILE.Put_Line(FND_FILE.Log,'Calling Netting API');
477
478 -- Netting
479 FUN_VENDORMERGE_GRP.Merge_Vendor(
480 p_api_version => 1.0
481 , p_init_msg_list => FND_API.G_FALSE
482 , p_commit => FND_API.G_FALSE
483 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
484 , p_return_status => l_return_status
485 , p_msg_count => l_msg_count
486 , p_msg_data => l_msg_data
487 , p_vendor_id => l_invoice_row.C_Vendor_Id
488 , p_dup_vendor_id => l_invoice_row.C_Dup_Vendor_Id
489 , p_vendor_site_id => l_invoice_row.C_Vendor_Site_Id
490 , p_dup_vendor_site_id => l_invoice_row.C_Dup_Vendor_Site_Id);
491
492 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
493 --
494 --
495 IF l_msg_count > 0 THEN
496 --
497 --
498 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
499 FND_MSG_PUB.Get( p_msg_index => i,
500 p_encoded => 'F',
501 p_data => l_msg_data,
502 p_msg_index_out => l_msg_index_out
503 );
504 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
505 End LOOP;
506 END IF;
507 RAISE FND_API.G_EXC_ERROR;
508 END IF;
509
510 FND_FILE.Put_Line(FND_FILE.Log,'Calling iRecruitment API');
511
512 -- iRecruitment
513 IRC_VENDORMERGE_GRP.Merge_Vendor(
514 p_api_version => 1.0
515 , p_init_msg_list => FND_API.G_FALSE
516 , p_commit => FND_API.G_FALSE
517 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
518 , p_return_status => l_return_status
519 , p_msg_count => l_msg_count
520 , p_msg_data => l_msg_data
521 , p_vendor_id => l_invoice_row.C_Vendor_Id
522 , p_dup_vendor_id => l_invoice_row.C_Dup_Vendor_Id
523 , p_vendor_site_id => l_invoice_row.C_Vendor_Site_Id
524 , p_dup_vendor_site_id => l_invoice_row.C_Dup_Vendor_Site_Id
525 , p_party_id => l_invoice_row.C_Party_Id
526 , p_dup_party_id => l_invoice_row.C_Dup_Party_Id
527 , p_party_site_id => l_invoice_row.C_Party_Site_Id
528 , p_dup_party_site_id => l_invoice_row.C_Dup_Party_Site_Id);
529
530 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
531 --
532 --
533 IF l_msg_count > 0 THEN
534 --
535 --
536 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
537 FND_MSG_PUB.Get( p_msg_index => i,
538 p_encoded => 'F',
539 p_data => l_msg_data,
540 p_msg_index_out => l_msg_index_out
541 );
542 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
543 End LOOP;
544 END IF;
545 RAISE FND_API.G_EXC_ERROR;
546 END IF;
547
548 -- Bug 6018743. Added the call to Oracle Loans API.
549 FND_FILE.Put_Line(FND_FILE.Log,'Calling OKL API');
550
551 --Oracle Loans
552 OKL_VENDORMERGE_GRP.Merge_Vendor(
553 p_api_version => 1.0
554 , p_init_msg_list => FND_API.G_FALSE
555 , p_commit => FND_API.G_FALSE
556 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
557 , p_return_status => l_return_status
558 , p_msg_count => l_msg_count
559 , p_msg_data => l_msg_data
560 , p_vendor_id => l_invoice_row.C_Vendor_Id
561 , p_dup_vendor_id => l_invoice_row.C_Dup_Vendor_Id
562 , p_vendor_site_id => l_invoice_row.C_Vendor_Site_Id
563 , p_dup_vendor_site_id => l_invoice_row.C_Dup_Vendor_Site_Id
564 , p_party_id => l_invoice_row.C_Party_Id
565 , p_dup_party_id => l_invoice_row.C_Dup_Party_Id
566 , p_party_site_id => l_invoice_row.C_Party_Site_Id
567 , p_dup_party_site_id => l_invoice_row.C_Dup_Party_Site_Id);
568
569 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
570 --
571 --
572 IF l_msg_count > 0 THEN
573 --
574 --
575 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
576 FND_MSG_PUB.Get( p_msg_index => i,
577 p_encoded => 'F',
578 p_data => l_msg_data,
579 p_msg_index_out => l_msg_index_out
580 );
581 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
582 End LOOP;
583 END IF;
584 RAISE FND_API.G_EXC_ERROR;
585 END IF;
586 -- Bug 6018743 ends.
587
588 /* Bug 9677677 Start - Localization */
589
590 FND_FILE.Put_Line(FND_FILE.Log,'Calling JL API');
591
592 JL_VENDORMERGE_GRP.Merge_Vendor(
593 p_api_version => 1.0
594 , p_init_msg_list => FND_API.G_FALSE
595 , p_commit => FND_API.G_FALSE
596 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
597 , p_return_status => l_return_status
598 , p_msg_count => l_msg_count
599 , p_msg_data => l_msg_data
600 , p_vendor_id => l_invoice_row.C_Vendor_Id
601 , p_dup_vendor_id => l_invoice_row.C_Dup_Vendor_Id
602 , p_vendor_site_id => l_invoice_row.C_Vendor_Site_Id
603 , p_dup_vendor_site_id => l_invoice_row.C_Dup_Vendor_Site_Id
604 , p_party_id => l_invoice_row.C_Party_Id
605 , p_dup_party_id => l_invoice_row.C_Dup_Party_Id
606 , p_party_site_id => l_invoice_row.C_Party_Site_Id
607 , p_dup_party_site_id => l_invoice_row.C_Dup_Party_Site_Id
608 );
609
610 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
611 --
612 --
613 IF l_msg_count > 0 THEN
614 --
615 --
616 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
617 FND_MSG_PUB.Get( p_msg_index => i,
618 p_encoded => 'F',
619 p_data => l_msg_data,
620 p_msg_index_out => l_msg_index_out
621 );
622 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
623 End LOOP;
624 END IF;
625 RAISE FND_API.G_EXC_ERROR;
626 END IF;
627 /* Bug 9677677 End - Localization */
628
629 FND_FILE.Put_Line(FND_FILE.Log,'Invoices: Complete');
630
631 END LOOP;
632 CLOSE Invoice_Cursor;
633 --
634 --
635
636 --
637 --
638 -- Process PO Related Impact Product Calls.
639 OPEN PO_Cursor;
640 LOOP
641 FETCH PO_Cursor INTO l_PO_Row;
642 EXIT WHEN PO_Cursor%NOTFOUND;
643
644 IF l_po_row.C_KEEP_SITE_FLAG = 'Y' AND
645 l_po_row.C_VENDOR_SITE_ID IS NULL THEN
646
647 SELECT vendor_site_id
648 INTO l_po_row.C_VENDOR_SITE_ID
649 FROM ap_supplier_sites_all
650 WHERE vendor_id = l_po_row.C_VENDOR_ID
651 AND vendor_site_code = l_po_row.C_VENDOR_SITE_CODE
652 AND org_id = l_po_row.C_ORG_ID; --Bug#7307532
653
654 END IF;
655
656 FND_FILE.Put_Line(FND_FILE.Log,'PO: Calling WSH API');
657
658 -- Call WSH API
659 WSH_VENDOR_PARTY_MERGE_PKG.Vendor_Party_Merge(
660 P_From_Vendor_Id => l_PO_Row.C_Dup_Vendor_ID,
661 P_To_Vendor_Id => l_PO_Row.C_Vendor_ID,
662 P_From_Party_Id => l_PO_Row.C_Dup_Party_ID,
663 P_To_Party_Id => l_PO_Row.C_Party_ID,
664 P_From_Vendor_Site_Id => l_PO_Row.C_Dup_Vendor_Site_ID,
665 P_To_Vendor_Site_Id => l_Po_Row.C_Vendor_Site_Id,
666 P_From_Party_Site_Id => l_PO_Row.C_Dup_Party_Site_ID,
667 P_To_Partysite_id => l_PO_Row.C_Party_Site_ID,
668 P_calling_mode => 'PO',
669 x_return_status => l_return_status,
670 x_msg_count => l_msg_count,
671 x_msg_data => l_msg_data);
672
673 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
674 --
675 --
676 IF l_msg_count > 0 THEN
677 --
678 --
679 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
680 FND_MSG_PUB.Get( p_msg_index => i,
681 p_encoded => 'F',
682 p_data => l_msg_data,
683 p_msg_index_out => l_msg_index_out
684 );
685 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
686 End LOOP;
687 END IF;
688 RAISE FND_API.G_EXC_ERROR;
689 END IF;
690
691 FND_FILE.Put_Line(FND_FILE.Log,'PO: Calling MRP API');
692
693 -- MRP
694 MRP_VendorMerge_GRP.Merge_Vendor(
695 p_api_version => 1.0
696 , p_init_msg_list => FND_API.G_FALSE
697 , p_commit => FND_API.G_FALSE
698 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
699 , x_return_status => l_return_status
700 , x_msg_count => l_msg_count
701 , x_msg_data => l_msg_data
702 , p_vendor_id => l_po_row.C_Vendor_Id
703 , p_vendor_site_id => l_po_row.C_Vendor_Site_Id
704 , p_dup_vendor_id => l_po_row.C_Dup_Vendor_Id
705 , p_dup_vendor_site_id => l_po_row.C_Dup_Vendor_Site_Id);
706
707 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
708 --
709 --
710 IF l_msg_count > 0 THEN
711 --
712 --
713 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
714 FND_MSG_PUB.Get( p_msg_index => i,
715 p_encoded => 'F',
716 p_data => l_msg_data,
717 p_msg_index_out => l_msg_index_out
718 );
719 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
720 END LOOP;
721 END IF;
722 RAISE FND_API.G_EXC_ERROR;
723 END IF;
724
725 FND_FILE.Put_Line(FND_FILE.Log,'PO: Calling Sourcing API');
726
727 -- Sourcing
728 PON_VENDORMERGE_GRP.Merge_Vendor(
729 p_api_version => 1.0
730 , p_vendor_id => l_po_row.C_Vendor_Id
731 , p_dup_vendor_id => l_po_row.C_Dup_Vendor_Id
732 , p_vendor_site_id => l_po_row.C_Vendor_Site_Id
733 , p_dup_vendor_site_id => l_po_row.C_Dup_Vendor_Site_Id
734 , p_party_id => l_po_row.C_Party_Id
735 , p_dup_party_id => l_po_row.C_Dup_Party_Id
736 , p_party_site_id => l_po_row.C_Party_Site_Id
737 , p_dup_party_site_id => l_po_row.C_Dup_Party_Site_Id
738 , p_init_msg_list => FND_API.G_FALSE
739 , p_commit => FND_API.G_FALSE
740 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
741 , p_return_status => l_return_status
742 , p_msg_count => l_msg_count
743 , p_msg_data => l_msg_data);
744
745 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
746 --
747 --
748 IF l_msg_count > 0 THEN
749 --
750 --
751 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
752 FND_MSG_PUB.Get( p_msg_index => i,
753 p_encoded => 'F',
754 p_data => l_msg_data,
755 p_msg_index_out => l_msg_index_out
756 );
757 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
758 END LOOP;
759 END IF;
760 RAISE FND_API.G_EXC_ERROR;
761 END IF;
762
763 FND_FILE.Put_Line(FND_FILE.Log,'PO: Calling iSP API');
764
765 -- iSupplier Portal
766 POS_VENDORMERGE_GRP.merge_vendor (
767 p_api_version => 1.0,
768 p_init_msg_list => fnd_api.g_false,
769 p_commit => fnd_api.g_false,
770 p_validation_level => fnd_api.g_valid_level_full,
771 p_return_status => l_return_status,
772 p_msg_count => l_msg_count,
773 p_msg_data => l_msg_data,
774 p_vendor_id => l_po_row.C_Vendor_Id,
775 p_dup_vendor_id => l_po_row.C_Dup_Vendor_Id,
776 p_vendor_site_id => l_po_row.C_Vendor_Site_Id,
777 p_dup_vendor_site_id => l_po_row.C_Dup_Vendor_Site_Id,
778 p_party_id => l_po_row.C_Party_Id,
779 p_dup_party_id => l_po_row.C_Dup_Party_Id,
780 p_party_site_id => l_po_row.C_Party_Site_Id,
781 p_dup_party_site_id => l_po_row.C_Dup_Party_Site_Id);
782
783 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
784 --
785 --
786 IF l_msg_count > 0 THEN
787 --
788 --
789 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
790 FND_MSG_PUB.Get( p_msg_index => i,
791 p_encoded => 'F',
792 p_data => l_msg_data,
793 p_msg_index_out => l_msg_index_out
794 );
795 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
796 END LOOP;
797 END IF;
798 RAISE FND_API.G_EXC_ERROR;
799 END IF;
800
801 FND_FILE.Put_Line(FND_FILE.Log,'PO: Calling Procurement API');
802
803 -- Procurement
804 PO_VENDORMERGE_GRP.merge_vendor (
805 p_api_version => 1.0,
806 p_init_msg_list => fnd_api.g_false,
807 p_commit => fnd_api.g_false,
808 p_validation_level => fnd_api.g_valid_level_full,
809 x_return_status => l_return_status,
810 x_msg_count => l_msg_count,
811 x_msg_data => l_msg_data,
812 p_vendor_id => l_po_row.C_Vendor_Id,
813 p_dup_vendor_id => l_po_row.C_Dup_Vendor_Id,
814 p_vendor_site_id => l_po_row.C_Vendor_Site_Id,
815 p_dup_vendor_site_id => l_po_row.C_Dup_Vendor_Site_Id,
816 p_party_id => l_po_row.C_Party_Id,
817 p_dup_party_id => l_po_row.C_Dup_Party_Id,
818 p_party_site_id => l_po_row.C_Party_Site_Id,
819 p_dup_party_site_id => l_po_row.C_Dup_Party_Site_Id);
820
821 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
822 --
823 --
824 IF l_msg_count > 0 THEN
825 --
826 --
827 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
828 FND_MSG_PUB.Get( p_msg_index => i,
829 p_encoded => 'F',
830 p_data => l_msg_data,
831 p_msg_index_out => l_msg_index_out
832 );
833 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
834 END LOOP;
835 END IF;
836 RAISE FND_API.G_EXC_ERROR;
837 END IF;
838
839 FND_FILE.Put_Line(FND_FILE.Log,'PO: Complete');
840 -- Bug 7592393 Start. Call to update receiving tables
841 FND_FILE.Put_Line(FND_FILE.Log,'PO: Calling Receiving API');
842
843 -- Receiving
844 RCV_UTILITIES.Merge_Vendor ( p_commit => fnd_api.g_false,
845 x_return_status => l_return_status,
846 x_msg_count => l_msg_count,
847 x_msg_data => l_msg_data,
848 p_vendor_id => l_po_row.C_Vendor_Id,
849 p_vendor_site_id => l_po_row.C_Vendor_Site_Id,
850 p_dup_vendor_id => l_po_row.C_Dup_Vendor_Id,
851 p_dup_vendor_site_id => l_po_row.C_Dup_Vendor_Site_Id);
852
853
854 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
855 IF l_msg_count > 0 THEN
856 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
857 FND_MSG_PUB.Get( p_msg_index => i,
858 p_encoded => 'F',
859 p_data => l_msg_data,
860 p_msg_index_out => l_msg_index_out
861 );
862 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
863 END LOOP;
864 END IF;
865 RAISE FND_API.G_EXC_ERROR;
866 END IF;
867 -- Bug 7592393 End.
868
869
870 END LOOP;
871 CLOSE PO_Cursor;
872 --
873
874 EXCEPTION
875 WHEN FND_API.G_EXC_ERROR THEN
876 FND_MSG_PUB.Count_And_Get
877 ( p_count => l_msg_count,
878 p_data => l_msg_data
879 );
880 IF Invoice_Cursor%ISOPEN THEN
881 CLOSE Invoice_Cursor;
882 END IF;
883
884 IF PO_Cursor%ISOPEN THEN
885 CLOSE PO_Cursor;
886 END IF;
887
888 ROLLBACK;
889 APP_EXCEPTION.RAISE_EXCEPTION;
890
891 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
892 FND_MSG_PUB.Count_And_Get
893 ( p_count => l_msg_count,
894 p_data => l_msg_data
895 );
896 IF Invoice_Cursor%ISOPEN THEN
897 CLOSE Invoice_Cursor;
898 END IF;
899
900 IF PO_Cursor%ISOPEN THEN
901 CLOSE PO_Cursor;
902 END IF;
903
904 ROLLBACK;
905 APP_EXCEPTION.RAISE_EXCEPTION;
906
907 WHEN OTHERS THEN
908 IF FND_MSG_PUB.Check_Msg_Level
909 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
910 FND_MSG_PUB.Add_Exc_Msg
911 ( G_PKG_NAME ,
912 'AP_VENDOR_PARTY_MERGE_PKG.Other_Products_VendorMerge'
913 );
914 END IF;
915 FND_MSG_PUB.Count_And_Get
916 ( p_count => l_msg_count,
917 p_data => l_msg_data
918 );
919 IF (SQLCODE <> -20001) THEN
920 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
921 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
922 FND_FILE.Put_Line(FND_FILE.Log,sqlerrm);
923 END IF;
924
925 IF Invoice_Cursor%ISOPEN THEN
926 CLOSE Invoice_Cursor;
927 END IF;
928
929 IF PO_Cursor%ISOPEN THEN
930 CLOSE PO_Cursor;
931 END IF;
932
933 ROLLBACK;
934 APP_EXCEPTION.RAISE_EXCEPTION;
935
936 END Other_Products_VendorMerge;
937
938 PROCEDURE AP_TCA_Contact_Merge (
939 p_from_party_site_id IN NUMBER,
940 p_to_party_site_id IN NUMBER,
941 p_from_per_party_id IN NUMBER,
942 p_to_org_party_id IN NUMBER,
943 x_return_status OUT NOCOPY VARCHAR2,
944 x_msg_count OUT NOCOPY NUMBER,
945 x_msg_data OUT NOCOPY VARCHAR2,
946 p_create_partysite_cont_pts IN VARCHAR2 DEFAULT 'N' --bug12571995
947 )
948 IS
949
950 l_relationship_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
951 l_relationship_id NUMBER;
952 l_party_id NUMBER;
953 l_party_number NUMBER;
954 l_contact_point_id NUMBER;
955 l_old_relationship_id NUMBER;
956 l_contact_point_rec HZ_CONTACT_POINT_v2pub.CONTACT_POINT_REC_TYPE;
957 l_edi_rec HZ_CONTACT_POINT_v2pub.EDI_REC_TYPE;
958 l_email_rec HZ_CONTACT_POINT_v2pub.EMAIL_REC_TYPE;
959 l_phone_rec HZ_CONTACT_POINT_v2pub.PHONE_REC_TYPE;
960 l_telex_rec HZ_CONTACT_POINT_v2pub.TELEX_REC_TYPE;
961 l_web_rec HZ_CONTACT_POINT_v2pub.WEB_REC_TYPE;
962 l_object_version_number NUMBER;
963 l_party_object_version_number NUMBER;
964 l_org_contact_rec HZ_PARTY_CONTACT_V2PUB.org_contact_rec_type;
965 l_org_contact_id NUMBER;
966 l_party_site_rec HZ_PARTY_SITE_V2PUB.party_site_rec_type;
967 l_party_site_num VARCHAR2(1);
968 l_party_site_id NUMBER;
969 l_party_site_number VARCHAR2(30);
970 l_location_id NUMBER;
971 l_msg_index_out NUMBER;
972 l_new_vendor_contact_id NUMBER;
973 /* Bug 9639308 Procedure rewritten to check for existing org contacts and relationships,
974 Improve error handling,
975 Remove the commit
976 Streamline c_contact_point cursor
977 Add last update values to the ap_supplier_contacts update */
978 -- Bug 7297864- End
979 CURSOR c_contact_point (l_dup_from_party_id NUMBER, l_per_party_id NUMBER) /* bug 9604355 */
980 IS
981 select hcpp.contact_point_id contact_point_id,
982 hcpp.owner_table_id contact_point_owner_id,
983 hcpp.owner_table_name contact_point_owner_name
984 from hz_parties hp,
985 hz_relationships hzr,
986 hz_party_usg_assignments hpua,
987 HZ_CONTACT_POINTS hcpp
988 where hp.party_id = hzr.subject_id
989 and hzr.subject_id = l_per_party_id /* bug 9604355 */
990 and hzr.relationship_type = 'CONTACT'
991 and hzr.relationship_code = 'CONTACT_OF'
992 and hzr.subject_type ='PERSON'
993 and hzr.object_type = 'ORGANIZATION'
994 and hzr.status = 'A'
995 and hp.party_id not in (select contact_party_id
996 from pos_contact_requests pcr,
997 pos_supplier_mappings psm
998 where pcr.request_status='PENDING'
999 and psm.mapping_id = pcr.mapping_id
1000 and psm.PARTY_ID = l_dup_from_party_id /* :2 bug 9604355 */
1001 and contact_party_id is not null
1002 )
1003 and hpua.party_id = hp.party_id
1004 and hpua.status_flag = 'A'
1005 and hpua.party_usage_code = 'SUPPLIER_CONTACT'
1006 and hcpp.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
1007 and hcpp.OWNER_TABLE_ID(+) = hzr.PARTY_ID
1008 and hcpp.status = 'A';
1009
1010
1011 BEGIN -- TCA_Contact_Merge Procedure begin
1012
1013 FND_FILE.Put_Line(FND_FILE.Log,'Start AP_VENDOR_PARTY_MERGE_PKG.AP_TCA_Contact_Merge API call');
1014
1015 -- For loop to fetch all contacts one by one
1016 FOR contact_loop_c IN ( SELECT vendor_contact_id,
1017 per_party_id,
1018 org_party_site_id,
1019 org_contact_id,
1020 party_site_id
1021 FROM ap_supplier_contacts
1022 WHERE org_party_site_id = p_from_party_site_id
1023 )
1024 LOOP
1025
1026 FND_FILE.Put_Line(FND_FILE.Log,'Inside the loop of merge vendor_contact :'|| contact_loop_c.vendor_contact_id);
1027
1028 --Initialization of new relationship rec - relationship between 'To Party' and 'Contact'
1029 l_relationship_rec.subject_id := contact_loop_c.per_party_id;
1030 l_relationship_rec.subject_type := 'PERSON';
1031 l_relationship_rec.subject_table_name := 'HZ_PARTIES';
1032 l_relationship_rec.object_id := p_to_org_party_id;
1033 l_relationship_rec.object_type := 'ORGANIZATION';
1034 l_relationship_rec.object_table_name := 'HZ_PARTIES';
1035 l_relationship_rec.relationship_code := 'CONTACT_OF';
1036 l_relationship_rec.relationship_type := 'CONTACT';
1037 l_relationship_rec.status := 'A';
1038 l_relationship_rec.created_by_module:='AP_SUPPLIERS_API';
1039
1040 -- Added the following code for bug 9639308
1041 -- First check if relation already exists for the contact and supplier that we are trying to link. If so, no need to create new relationship.
1042 BEGIN
1043 SELECT vendor_contact_id
1044 INTO l_new_vendor_contact_id
1045 FROM ap_supplier_contacts apc
1046 WHERE per_party_id = l_relationship_rec.subject_id
1047 --AND NVL(org_party_site_id, -1) = contact_loop_c.to_party_site_id;
1048 AND NVL(org_party_site_id, -1) = p_to_party_site_id;
1049 EXCEPTION
1050 WHEN no_data_found THEN
1051 l_new_vendor_contact_id := NULL;
1052 END;
1053
1054 IF l_new_vendor_contact_id IS NULL THEN
1055
1056 BEGIN
1057 SELECT relationship_id,
1058 party_id
1059 INTO l_relationship_id,l_party_id
1060 FROM hz_relationships
1061 WHERE subject_id = l_relationship_rec.subject_id
1062 AND subject_type = 'PERSON'
1063 AND object_id = l_relationship_rec.object_id
1064 AND object_type = 'ORGANIZATION'
1065 AND status = 'A'
1066 AND directional_flag = 'F';
1067
1068 EXCEPTION
1069 WHEN no_data_found THEN
1070 l_relationship_id := NULL;
1071 l_party_id := NULL; --Bug9899876
1072 END;
1073
1074 BEGIN
1075 IF l_relationship_id IS NOT NULL THEN
1076 SELECT org_contact_id
1077 INTO l_org_contact_id
1078 FROM hz_org_contacts
1079 WHERE party_relationship_id = l_relationship_id;
1080 END IF;
1081
1082 EXCEPTION
1083 WHEN no_data_found THEN
1084 l_org_contact_id := NULL;
1085 END;
1086
1087 IF l_relationship_id IS NULL
1088 AND l_org_contact_id IS NULL
1089 AND contact_loop_c.org_contact_id IS NOT NULL THEN
1090
1091 FND_FILE.Put_Line(FND_FILE.Log,'Inside if clause contact_loop_c.org_contact_id is not null');
1092
1093 SELECT comments,
1094 contact_number,
1095 department_code,
1096 department,
1097 title,
1098 job_title,
1099 decision_maker_flag,
1100 job_title_code,
1101 reference_use_flag,
1102 rank
1103 INTO l_org_contact_rec.comments,
1104 l_org_contact_rec.contact_number,
1105 l_org_contact_rec.department_code,
1106 l_org_contact_rec.department,
1107 l_org_contact_rec.title,
1108 l_org_contact_rec.job_title,
1109 l_org_contact_rec.decision_maker_flag,
1110 l_org_contact_rec.job_title_code,
1111 l_org_contact_rec.reference_use_flag,
1112 l_org_contact_rec.rank
1113 FROM hz_org_contacts
1114 WHERE org_contact_id = contact_loop_c.org_contact_id;
1115
1116 l_org_contact_rec.created_by_module := 'AP_SUPPLIERS_API';
1117 l_org_contact_rec.party_rel_rec := l_relationship_rec;
1118
1119 FND_FILE.Put_Line(FND_FILE.Log,'Inside if clause before calling hz_party_contact_v2pub.create_org_contact');
1120
1121 hz_party_contact_v2pub.Create_org_contact('T',l_org_contact_rec,l_org_contact_id,l_relationship_id,
1122 l_party_id,l_party_number,x_return_status,
1123 x_msg_count,x_msg_data);
1124
1125 FND_FILE.Put_Line(FND_FILE.Log,'Inside if clause after calling hz_party_contact_v2pub.create_org_contact');
1126
1127 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1128 IF x_msg_count > 0 THEN
1129 FOR i IN 1.. fnd_msg_pub.count_msg LOOP
1130 fnd_msg_pub.Get(p_msg_index => i,p_encoded => 'F',p_data => x_msg_data,
1131 p_msg_index_out => l_msg_index_out);
1132
1133 FND_FILE.Put_Line(FND_FILE.Log,x_msg_data||' for Vendor contact : '||contact_loop_c.vendor_contact_id);
1134 END LOOP;
1135 END IF;
1136 RAISE fnd_api.g_exc_error;
1137 END IF;
1138
1139 ELSIF l_relationship_id IS NULL
1140 AND l_org_contact_id IS NULL
1141 AND contact_loop_c.org_contact_id IS NULL THEN
1142
1143 FND_FILE.Put_Line(FND_FILE.Log,'Inside else clause before calling hz_relationship_v2pub.create_relationship');
1144
1145 hz_relationship_v2pub.Create_relationship('T',l_relationship_rec,l_relationship_id,
1146 l_party_id,l_party_number,x_return_status,
1147 x_msg_count,x_msg_data);
1148
1149 FND_FILE.Put_Line(FND_FILE.Log,'Inside else clause after calling hz_relationship_v2pub.create_relationship');
1150
1151 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1152 IF x_msg_count > 0 THEN
1153 FOR i IN 1.. fnd_msg_pub.count_msg LOOP
1154 fnd_msg_pub.Get(p_msg_index => i,p_encoded => 'F',p_data => x_msg_data,
1155 p_msg_index_out => l_msg_index_out);
1156
1157 FND_FILE.Put_Line(FND_FILE.Log,'Error : '||x_msg_data);
1158 END LOOP;
1159 END IF;
1160 RAISE fnd_api.g_exc_error;
1161 END IF;
1162 END IF;
1163
1164 SELECT location_id
1165 INTO l_location_id
1166 FROM hz_party_sites
1167 WHERE party_site_id = contact_loop_c.party_site_id;
1168
1169 l_party_site_rec.location_id := l_location_id;
1170 l_party_site_rec.created_by_module := 'AP_SUPPLIERS_API';
1171 l_party_site_rec.application_id := 200;
1172 l_party_site_rec.party_id := l_party_id;
1173
1174 BEGIN
1175 SELECT party_site_id
1176 INTO l_party_site_id
1177 FROM hz_party_sites
1178 WHERE location_id = l_location_id
1179 AND party_id = l_party_id;
1180
1181 EXCEPTION
1182 WHEN no_data_found THEN
1183 l_party_site_id := NULL;
1184 END;
1185
1186 IF l_party_site_id IS NULL THEN
1187 fnd_profile.Get('HZ_GENERATE_PARTY_SITE_NUMBER',l_party_site_num);
1188
1189 IF Nvl(l_party_site_num,'Y') = 'N' THEN
1190 SELECT hz_party_site_number_s.nextval
1191 INTO l_party_site_rec.party_site_number
1192 FROM dual;
1193 END IF;
1194
1195 FND_FILE.Put_Line(FND_FILE.Log,'Inside loop before calling hz_party_site_v2pub.create_party_site');
1196
1197 -- Create party_site between l_location_id and new party_id - l_party_id
1198 hz_party_site_v2pub.Create_party_site('T',p_party_site_rec => l_party_site_rec,
1199 x_return_status => x_return_status,x_msg_count => x_msg_count,
1200 x_msg_data => x_msg_data,x_party_site_id => l_party_site_id,
1201 x_party_site_number => l_party_site_number);
1202
1203 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1204 IF x_msg_count > 0 THEN
1205 FOR i IN 1.. fnd_msg_pub.count_msg LOOP
1206 fnd_msg_pub.Get(p_msg_index => i,p_encoded => 'F',p_data => x_msg_data,
1207 p_msg_index_out => l_msg_index_out);
1208
1209 FND_FILE.Put_Line(FND_FILE.Log,'Error : '||x_msg_data);
1210 END LOOP;
1211 END IF;
1212
1213 RAISE fnd_api.g_exc_error;
1214 END IF;
1215
1216 FND_FILE.Put_Line(FND_FILE.Log,'Inside loop after calling hz_party_site_v2pub.create_party_site');
1217
1218 END IF;
1219
1220 BEGIN
1221 SELECT owner_table_id
1222 INTO l_contact_point_rec.owner_table_id
1223 FROM hz_contact_points
1224 WHERE owner_table_id = l_party_id
1225 AND owner_table_name = 'HZ_PARTIES'
1226 AND status = 'A'
1227 AND ROWNUM < 2;
1228 EXCEPTION
1229 WHEN no_data_found THEN
1230 -- For loop to fetch all contact points belong to above contacts (Email-Ids and Phone numbers)
1231 FOR contact_point_loop_c IN c_contact_point(p_from_per_party_id, contact_loop_c.per_party_id) LOOP /* Bug 9604355 */
1232 FND_FILE.Put_Line(FND_FILE.Log,'inside second loop');
1233 BEGIN
1234 -- Fetching contact point details
1235 SELECT contact_point_type,
1236 status,
1237 owner_table_name,
1238 primary_flag,
1239 orig_system_reference,
1240 content_source_type,
1241 contact_point_purpose,
1242 primary_by_purpose,
1243 edi_transaction_handling,
1244 edi_id_number,
1245 edi_payment_method,
1246 edi_payment_format,
1247 edi_remittance_method,
1248 edi_remittance_instruction,
1249 edi_tp_header_id,
1250 edi_ece_tp_location_code,
1251 email_format,
1252 email_address,
1253 phone_calling_calendar,
1254 last_contact_dt_time,
1255 timezone_id,
1256 phone_area_code,
1257 phone_country_code,
1258 phone_number,
1259 phone_extension,
1260 phone_line_type,
1261 telex_number,
1262 web_type,
1263 url,
1264 application_id
1265 INTO l_contact_point_rec.contact_point_type,
1266 l_contact_point_rec.status,
1267 l_contact_point_rec.owner_table_name,
1268 l_contact_point_rec.primary_flag,
1269 l_contact_point_rec.orig_system_reference,
1270 l_contact_point_rec.content_source_type,
1271 l_contact_point_rec.contact_point_purpose,
1272 l_contact_point_rec.primary_by_purpose,
1273 l_edi_rec.edi_transaction_handling,
1274 l_edi_rec.edi_id_number,
1275 l_edi_rec.edi_payment_method,
1276 l_edi_rec.edi_payment_format,
1277 l_edi_rec.edi_remittance_method,
1278 l_edi_rec.edi_remittance_instruction,
1279 l_edi_rec.edi_tp_header_id,
1280 l_edi_rec.edi_ece_tp_location_code,
1281 l_email_rec.email_format,
1282 l_email_rec.email_address,
1283 l_phone_rec.phone_calling_calendar,
1284 l_phone_rec.last_contact_dt_time,
1285 l_phone_rec.timezone_id,
1286 l_phone_rec.phone_area_code,
1287 l_phone_rec.phone_country_code,
1288 l_phone_rec.phone_number,
1289 l_phone_rec.phone_extension,
1290 l_phone_rec.phone_line_type,
1291 l_telex_rec.telex_number,
1292 l_web_rec.web_type,
1293 l_web_rec.url,
1294 l_contact_point_rec.application_id
1295 FROM hz_contact_points
1296 WHERE contact_point_id = contact_point_loop_c.contact_point_id;
1297
1298 l_contact_point_rec.owner_table_id := l_party_id;
1299 l_contact_point_rec.created_by_module := 'AP_SUPPLIERS_API';
1300
1301 -- Creation of new contact points with new party_id
1302 hz_contact_point_v2pub.Create_contact_point('T',l_contact_point_rec,l_edi_rec,l_email_rec,
1303 l_phone_rec,l_telex_rec,l_web_rec,l_contact_point_id,
1304 x_return_status,x_msg_count,x_msg_data);
1305 FND_FILE.Put_Line(FND_FILE.Log,'x_return_status: '||x_return_status);
1306
1307 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1308 IF x_msg_count > 0 THEN
1309 FOR i IN 1.. fnd_msg_pub.count_msg LOOP
1310 fnd_msg_pub.Get(p_msg_index => i,p_encoded => 'F',p_data => x_msg_data,
1311 p_msg_index_out => l_msg_index_out);
1312 FND_FILE.Put_Line(FND_FILE.Log,'Error : '||x_msg_data);
1313 END LOOP;
1314 END IF;
1315
1316 RAISE fnd_api.g_exc_error;
1317 END IF;
1318 FND_FILE.Put_Line(FND_FILE.Log,'end of second loop');
1319 EXCEPTION
1320 WHEN no_data_found THEN
1321 NULL;
1322 END;
1323 END LOOP;
1324
1325 END;
1326 FND_FILE.Put_Line(FND_FILE.Log,'before creating ap_supplier_contacts');
1327 FND_FILE.Put_Line(FND_FILE.Log,'contact_loop_c.vendor_contact_id '
1328 ||contact_loop_c.vendor_contact_id);
1329
1330 BEGIN
1331 /* Bug 9559145 -- commenting below and writing new insert statement*/
1332
1333 --UPDATE ap_supplier_contacts
1334 --SET org_party_site_id = p_to_party_site_id,
1335 -- rel_party_id = l_party_id,
1336 -- relationship_id = l_relationship_id,
1337 -- party_site_id = l_party_site_id,
1338 -- org_contact_id = l_org_contact_id,
1339 -- last_update_date = sysdate,
1340 -- last_updated_by = FND_GLOBAL.USER_ID,
1341 -- last_update_login = FND_GLOBAL.LOGIN_ID,
1342 -- request_id = FND_GLOBAL.conc_request_id,
1343 -- program_application_id = FND_GLOBAL.prog_appl_id,
1344 -- program_id = FND_GLOBAL.conc_program_id
1345 --WHERE vendor_contact_id = contact_loop_c.vendor_contact_id;
1346
1347 FND_FILE.Put_Line(FND_FILE.Log,'creating new contact for to_party');
1348
1349 INSERT INTO AP_SUPPLIER_CONTACTS
1350 ( VENDOR_CONTACT_ID,
1351 LAST_UPDATE_DATE,
1352 LAST_UPDATED_BY,
1353 VENDOR_SITE_ID,
1354 LAST_UPDATE_LOGIN,
1355 CREATION_DATE,
1356 CREATED_BY,
1357 INACTIVE_DATE,
1358 FIRST_NAME,
1359 MIDDLE_NAME,
1360 LAST_NAME,
1361 PREFIX,
1362 TITLE,
1363 MAIL_STOP,
1364 AREA_CODE,
1365 PHONE,
1366 ATTRIBUTE_CATEGORY,
1367 ATTRIBUTE1,
1368 ATTRIBUTE2,
1369 ATTRIBUTE3,
1370 ATTRIBUTE4,
1371 ATTRIBUTE5,
1372 ATTRIBUTE6,
1373 ATTRIBUTE7,
1374 ATTRIBUTE8,
1375 ATTRIBUTE9,
1376 ATTRIBUTE10,
1377 ATTRIBUTE11,
1378 ATTRIBUTE12,
1379 ATTRIBUTE13,
1380 ATTRIBUTE14,
1381 ATTRIBUTE15,
1382 REQUEST_ID,
1383 PROGRAM_APPLICATION_ID,
1384 PROGRAM_ID,
1385 PROGRAM_UPDATE_DATE,
1386 CONTACT_NAME_ALT,
1387 FIRST_NAME_ALT,
1388 LAST_NAME_ALT,
1389 DEPARTMENT,
1390 EMAIL_ADDRESS,
1391 URL,
1392 ALT_AREA_CODE,
1393 ALT_PHONE,
1394 FAX_AREA_CODE,
1395 FAX,
1396 PER_PARTY_ID,
1397 RELATIONSHIP_ID,
1398 REL_PARTY_ID,
1399 PARTY_SITE_ID,
1400 ORG_CONTACT_ID,
1401 ORG_PARTY_SITE_ID)
1402 SELECT
1403 PO_VENDOR_CONTACTS_S.NEXTVAL, --bug13743686
1404 sysdate,
1405 FND_GLOBAL.USER_ID,
1406 VENDOR_SITE_ID,
1407 FND_GLOBAL.LOGIN_ID,
1408 CREATION_DATE,
1409 CREATED_BY,
1410 INACTIVE_DATE,
1411 FIRST_NAME,
1412 MIDDLE_NAME,
1413 LAST_NAME,
1414 PREFIX,
1415 TITLE,
1416 MAIL_STOP,
1417 AREA_CODE,
1418 PHONE,
1419 ATTRIBUTE_CATEGORY,
1420 ATTRIBUTE1,
1421 ATTRIBUTE2,
1422 ATTRIBUTE3,
1423 ATTRIBUTE4,
1424 ATTRIBUTE5,
1425 ATTRIBUTE6,
1426 ATTRIBUTE7,
1427 ATTRIBUTE8,
1428 ATTRIBUTE9,
1429 ATTRIBUTE10,
1430 ATTRIBUTE11,
1431 ATTRIBUTE12,
1432 ATTRIBUTE13,
1433 ATTRIBUTE14,
1434 ATTRIBUTE15,
1435 FND_GLOBAL.conc_request_id,
1436 FND_GLOBAL.prog_appl_id,
1437 FND_GLOBAL.conc_program_id,
1438 PROGRAM_UPDATE_DATE,
1439 CONTACT_NAME_ALT,
1440 FIRST_NAME_ALT,
1441 LAST_NAME_ALT,
1442 DEPARTMENT,
1443 EMAIL_ADDRESS,
1444 URL,
1445 ALT_AREA_CODE,
1446 ALT_PHONE,
1447 FAX_AREA_CODE,
1448 FAX,
1449 PER_PARTY_ID,
1450 l_relationship_id,
1451 l_party_id,
1452 l_party_site_id,
1453 l_org_contact_id,
1454 p_to_party_site_id
1455 FROM AP_SUPPLIER_CONTACTS
1456 WHERE vendor_contact_id = contact_loop_c.vendor_contact_id;
1457
1458 FND_FILE.Put_Line(FND_FILE.Log,'No.of rows inserted '||SQL%ROWCOUNT);
1459
1460 l_org_contact_id := NULL; --Bug9899876
1461
1462 EXCEPTION
1463 WHEN OTHERS THEN
1464 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1465 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1466 FND_FILE.Put_Line(FND_FILE.Log,sqlerrm);
1467 RAISE fnd_api.g_exc_error;
1468 END;
1469 ELSE
1470 FND_FILE.Put_Line(FND_FILE.Log,'Vendor contact already exists with same info ID : '
1471 || l_new_vendor_contact_id);
1472 END IF;
1473 END LOOP;
1474
1475 -- bug12571995 start
1476 IF p_create_partysite_cont_pts = 'Y'
1477 THEN
1478 FOR contact_point_loop_c IN (
1479 SELECT contact_point_id
1480 FROM hz_contact_points
1481 WHERE owner_table_name = 'HZ_PARTY_SITES'
1482 AND owner_table_id = p_from_party_site_id
1483 ) LOOP
1484 FND_FILE.Put_Line(FND_FILE.Log,'inside party site contact points oop');
1485 BEGIN
1486 -- Fetching contact point details
1487 SELECT contact_point_type,
1488 status,
1489 owner_table_name,
1490 primary_flag,
1491 orig_system_reference,
1492 content_source_type,
1493 contact_point_purpose,
1494 primary_by_purpose,
1495 edi_transaction_handling,
1496 edi_id_number,
1497 edi_payment_method,
1498 edi_payment_format,
1499 edi_remittance_method,
1500 edi_remittance_instruction,
1501 edi_tp_header_id,
1502 edi_ece_tp_location_code,
1503 email_format,
1504 email_address,
1505 phone_calling_calendar,
1506 last_contact_dt_time,
1507 timezone_id,
1508 phone_area_code,
1509 phone_country_code,
1510 phone_number,
1511 phone_extension,
1512 phone_line_type,
1513 telex_number,
1514 web_type,
1515 url,
1516 200
1517 INTO l_contact_point_rec.contact_point_type,
1518 l_contact_point_rec.status,
1519 l_contact_point_rec.owner_table_name,
1520 l_contact_point_rec.primary_flag,
1521 l_contact_point_rec.orig_system_reference,
1522 l_contact_point_rec.content_source_type,
1523 l_contact_point_rec.contact_point_purpose,
1524 l_contact_point_rec.primary_by_purpose,
1525 l_edi_rec.edi_transaction_handling,
1526 l_edi_rec.edi_id_number,
1527 l_edi_rec.edi_payment_method,
1528 l_edi_rec.edi_payment_format,
1529 l_edi_rec.edi_remittance_method,
1530 l_edi_rec.edi_remittance_instruction,
1531 l_edi_rec.edi_tp_header_id,
1532 l_edi_rec.edi_ece_tp_location_code,
1533 l_email_rec.email_format,
1534 l_email_rec.email_address,
1535 l_phone_rec.phone_calling_calendar,
1536 l_phone_rec.last_contact_dt_time,
1537 l_phone_rec.timezone_id,
1538 l_phone_rec.phone_area_code,
1539 l_phone_rec.phone_country_code,
1540 l_phone_rec.phone_number,
1541 l_phone_rec.phone_extension,
1542 l_phone_rec.phone_line_type,
1543 l_telex_rec.telex_number,
1544 l_web_rec.web_type,
1545 l_web_rec.url,
1546 l_contact_point_rec.application_id
1547 FROM hz_contact_points
1548 WHERE contact_point_id = contact_point_loop_c.contact_point_id;
1549
1550 l_contact_point_rec.owner_table_id := p_to_party_site_id;
1551 l_contact_point_rec.created_by_module := 'AP_SUPPLIERS_API';
1552
1553 -- Creation of new contact points with new party_site_id
1554 hz_contact_point_v2pub.Create_contact_point('T',l_contact_point_rec,l_edi_rec,l_email_rec,
1555 l_phone_rec,l_telex_rec,l_web_rec,l_contact_point_id,
1556 x_return_status,x_msg_count,x_msg_data);
1557 FND_FILE.Put_Line(FND_FILE.Log,'x_return_status: '||x_return_status);
1558
1559 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1560 IF x_msg_count > 0 THEN
1561 FOR i IN 1.. fnd_msg_pub.count_msg LOOP
1562 fnd_msg_pub.Get(p_msg_index => i,p_encoded => 'F',p_data => x_msg_data,
1563 p_msg_index_out => l_msg_index_out);
1564 FND_FILE.Put_Line(FND_FILE.Log,'Error : '||x_msg_data);
1565 END LOOP;
1566 END IF;
1567
1568 RAISE fnd_api.g_exc_error;
1569 END IF;
1570 FND_FILE.Put_Line(FND_FILE.Log,'end of party site contact points loop');
1571 EXCEPTION
1572 WHEN no_data_found THEN
1573 NULL;
1574 END;
1575 END LOOP;
1576
1577 END IF;
1578 --bug12571995 end
1579
1580 FND_FILE.Put_Line(FND_FILE.Log,'End AP_VENDOR_PARTY_MERGE_PKG.AP_TCA_Contact_Merge API call');
1581
1582 EXCEPTION
1583 WHEN FND_API.G_EXC_ERROR THEN
1584 FND_MSG_PUB.Count_And_Get
1585 ( p_count => x_msg_count,
1586 p_data => x_msg_data
1587 );
1588
1589 IF c_contact_point%ISOPEN THEN
1590 CLOSE c_contact_point;
1591 END IF;
1592
1593 ROLLBACK;
1594 x_return_status := FND_API.G_RET_STS_ERROR;
1595
1596 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1597 FND_MSG_PUB.Count_And_Get
1598 ( p_count => x_msg_count,
1599 p_data => x_msg_data
1600 );
1601 IF c_contact_point%ISOPEN THEN
1602 CLOSE c_contact_point;
1603 END IF;
1604
1605 ROLLBACK;
1606 APP_EXCEPTION.RAISE_EXCEPTION;
1607
1608 WHEN OTHERS THEN
1609 IF FND_MSG_PUB.Check_Msg_Level
1610 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1611 FND_MSG_PUB.Add_Exc_Msg
1612 ( G_PKG_NAME ,
1613 'AP_VENDOR_PARTY_MERGE_PKG.AP_TCA_Contact_Merge'
1614 );
1615 END IF;
1616 FND_MSG_PUB.Count_And_Get
1617 ( p_count => x_msg_count,
1618 p_data => x_msg_data
1619 );
1620 IF (SQLCODE <> -20001) THEN
1621 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1622 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1623 FND_FILE.Put_Line(FND_FILE.Log,sqlerrm);
1624 END IF;
1625
1626 IF c_contact_point%ISOPEN THEN
1627 CLOSE c_contact_point;
1628 END IF;
1629
1630 ROLLBACK;
1631 APP_EXCEPTION.RAISE_EXCEPTION;
1632
1633 /* Bug 7297864- End Bug 9639308 End */
1634 END AP_TCA_Contact_Merge;
1635
1636 END AP_VENDOR_PARTY_MERGE_PKG;