[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.12.12010000.4 2009/02/05 05:08:16 manjayar 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 IS
27
28 --
29 l_return_status VARCHAR2(50);
30 l_msg_data VARCHAR2(1000);
31 l_msg_count NUMBER;
32 l_msg_index_out NUMBER;
33
34 CURSOR Invoice_Cursor IS
35 SELECT dv.vendor_id C_VENDOR_ID,
36 dv.vendor_site_id C_VENDOR_SITE_ID,
37 dv.duplicate_vendor_id C_DUP_VENDOR_ID,
38 dv.duplicate_vendor_site_id C_DUP_VENDOR_SITE_ID,
39 dv.entry_id C_ENTRY_ID,
40 dv.org_id C_ORG_ID,
41 a.vendor_name C_VENDOR_NAME,
42 b.vendor_name C_DUP_VENDOR_NAME,
43 a.party_id C_PARTY_ID,
44 b.party_id C_DUP_PARTY_ID,
45 c.vendor_site_code C_VENDOR_SITE_CODE,
46 d.vendor_site_code C_DUP_VENDOR_SITE_CODE,
47 c.party_site_id C_PARTY_SITE_ID,
48 d.party_site_id C_DUP_PARTY_SITE_ID,
49 dv.keep_site_flag C_KEEP_SITE_FLAG,
50 dv.paid_invoices_flag C_PAID_INVOICES_FLAG,
51 a.segment1 C_NEW_VENDOR_NUMBER,
52 b.segment1 C_OLD_VENDOR_NUMBER
53 FROM ap_duplicate_vendors_all dv,
54 ap_suppliers a,
55 ap_suppliers b,
56 ap_supplier_sites_all c,
57 ap_supplier_sites_all d
58 WHERE dv.process_flag='S'
59 AND a.vendor_id=dv.vendor_id
60 AND c.vendor_site_id=nvl(dv.vendor_site_id,duplicate_vendor_site_id)
61 AND b.vendor_id=dv.duplicate_vendor_id
62 AND d.vendor_site_id=dv.duplicate_vendor_site_id
63 AND d.org_id = dv.org_id
64 AND dv.process<>'P'
65 -- Added for Bug 5641382
66 AND dv.duplicate_vendor_id = NVL(v_dup_vendor_id, dv.duplicate_vendor_id);
67
68 CURSOR PO_Cursor IS
69 SELECT dv.vendor_id C_VENDOR_ID,
70 dv.vendor_site_id C_VENDOR_SITE_ID,
71 dv.duplicate_vendor_id C_DUP_VENDOR_ID,
72 dv.duplicate_vendor_site_id C_DUP_VENDOR_SITE_ID,
73 dv.entry_id C_ENTRY_ID,
74 dv.org_id C_ORG_ID,
75 a.vendor_name C_VENDOR_NAME,
76 b.vendor_name C_DUP_VENDOR_NAME,
77 a.party_id C_PARTY_ID,
78 b.party_id C_DUP_PARTY_ID,
79 c.vendor_site_code C_VENDOR_SITE_CODE,
80 d.vendor_site_code C_DUP_VENDOR_SITE_CODE,
81 c.party_site_id C_PARTY_SITE_ID,
82 d.party_site_id C_DUP_PARTY_SITE_ID,
83 dv.keep_site_flag C_KEEP_SITE_FLAG,
84 dv.paid_invoices_flag C_PAID_INVOICES_FLAG,
85 a.segment1 C_NEW_VENDOR_NUMBER,
86 b.segment1 C_OLD_VENDOR_NUMBER
87 FROM ap_duplicate_vendors_all dv,
88 ap_suppliers a,
89 ap_suppliers b,
90 ap_supplier_sites_all c,
91 ap_supplier_sites_all d
92 WHERE dv.process_flag in ('S','D')
93 AND a.vendor_id=dv.vendor_id
94 AND c.vendor_site_id=nvl(dv.vendor_site_id,duplicate_vendor_site_id)
95 AND b.vendor_id=dv.duplicate_vendor_id
96 AND d.vendor_site_id=dv.duplicate_vendor_site_id
97 AND d.org_id = dv.org_id
98 AND dv.process<>'I'
99 -- Added for Bug 5641382
100 AND dv.duplicate_vendor_id = NVL(v_dup_vendor_id, dv.duplicate_vendor_id);
101
102 l_Invoice_Row Invoice_Cursor%ROWTYPE;
103 l_PO_Row PO_Cursor%ROWTYPE;
104
105 BEGIN
106 --
107 --
108 -- Process Invoice Related Impact Product Calls.
109 OPEN Invoice_Cursor;
110 LOOP
111 FETCH Invoice_Cursor INTO l_Invoice_Row;
112 EXIT WHEN Invoice_Cursor%NOTFOUND;
113
114 IF l_invoice_row.C_KEEP_SITE_FLAG = 'Y' AND
115 l_invoice_row.C_VENDOR_SITE_ID IS NULL THEN
116
117 SELECT vendor_site_id
118 INTO l_invoice_row.C_VENDOR_SITE_ID
119 FROM ap_supplier_sites_all
120 WHERE vendor_id = l_invoice_row.C_VENDOR_ID
121 AND vendor_site_code = l_invoice_row.C_VENDOR_SITE_CODE
122 AND org_id = l_invoice_row.C_ORG_ID; --Bug#7307532
123
124 END IF;
125
126 FND_FILE.Put_Line(FND_FILE.Log,'Calling WSH API');
127
128 -- Call WSH API
129 WSH_VENDOR_PARTY_MERGE_PKG.Vendor_Party_Merge(
130 p_from_vendor_id => l_invoice_row.C_Dup_Vendor_Id,
131 p_to_vendor_id => l_invoice_row.C_Vendor_Id,
132 p_from_party_id => l_invoice_row.C_Dup_Party_Id,
133 p_to_party_id => l_invoice_row.C_Party_Id,
134 p_from_vendor_site_id => l_invoice_row.C_Dup_Vendor_Site_Id,
135 p_to_vendor_site_id => l_invoice_row.C_Vendor_Site_Id,
136 p_from_party_site_id => l_invoice_row.C_Dup_Party_Site_Id,
137 p_to_partysite_id => l_invoice_row.C_Party_Site_Id,
138 p_calling_mode => 'INVOICE',
139 x_return_status => l_return_status,
140 x_msg_count => l_msg_count,
141 x_msg_data => l_msg_data);
142
143
144 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
145 --
146 --
147 IF l_msg_count > 0 THEN
148 --
149 --
150 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
151 FND_MSG_PUB.Get( p_msg_index => i,
152 p_encoded => 'F',
153 p_data => l_msg_data,
154 p_msg_index_out => l_msg_index_out
155 );
156 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
157 End LOOP;
158 END IF;
159 RAISE FND_API.G_EXC_ERROR;
160 END IF;
161
162 FND_FILE.Put_Line(FND_FILE.Log,'Calling Fixed Assets API');
163
164 -- Fixed Assets
165 FA_VendorMerge_GRP.Merge_Vendor(
166 p_api_version => 1.0
167 , p_init_msg_list => FND_API.G_FALSE
168 , p_commit => FND_API.G_FALSE
169 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
170 , x_return_status => l_return_status
171 , x_msg_count => l_msg_count
172 , x_msg_data => l_msg_data
173 , p_vendor_id => l_invoice_row.C_Vendor_Id
174 , p_dup_vendor_id => l_invoice_row.C_Dup_Vendor_Id
175 , p_vendor_site_id => l_invoice_row.C_Vendor_Site_Id
176 , p_dup_vendor_site_id => l_invoice_row.C_Dup_Vendor_Site_Id
177 , p_party_id => l_invoice_row.C_Party_Id
178 , p_dup_party_id => l_invoice_row.C_Dup_Party_Id
179 , p_party_site_id => l_invoice_row.C_Party_Site_Id
180 , p_dup_party_site_id => l_invoice_row.C_Dup_Party_Site_Id
181 , p_segment1 => l_invoice_row.C_New_Vendor_Number
182 , p_dup_segment1 => l_invoice_row.C_Old_Vendor_Number
183 , p_vendor_name => l_invoice_row.C_Vendor_Name);
184
185 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
186 --
187 --
188 IF l_msg_count > 0 THEN
189 --
190 --
191 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
192 FND_MSG_PUB.Get( p_msg_index => i,
193 p_encoded => 'F',
194 p_data => l_msg_data,
195 p_msg_index_out => l_msg_index_out
196 );
197 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
198 End LOOP;
199 END IF;
200 RAISE FND_API.G_EXC_ERROR;
201 END IF;
202
203 FND_FILE.Put_Line(FND_FILE.Log,'Calling Trade Management API');
204
205 -- Trade Management
206 OZF_VENDORMERGE_GRP.Merge_Vendor(
207 p_api_version => 1.0
208 , p_init_msg_list => FND_API.G_FALSE
209 , p_commit => FND_API.G_FALSE
210 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
211 , p_return_status => l_return_status
212 , p_msg_count => l_msg_count
213 , p_msg_data => l_msg_data
214 , p_vendor_id => l_invoice_row.C_Vendor_Id
215 , p_dup_vendor_id => l_invoice_row.C_Dup_Vendor_Id
216 , p_vendor_site_id => l_invoice_row.C_Vendor_Site_Id
217 , p_dup_vendor_site_id => l_invoice_row.C_Dup_Vendor_Site_Id
218 , p_party_id => l_invoice_row.C_Party_Id
219 , p_dup_party_id => l_invoice_row.C_Dup_Party_Id
220 , p_party_site_id => l_invoice_row.C_Party_Site_Id
221 , p_dup_party_site_id => l_invoice_row.C_Dup_Party_Site_Id);
222
223 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
224 --
225 --
226 IF l_msg_count > 0 THEN
227 --
228 --
229 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
230 FND_MSG_PUB.Get( p_msg_index => i,
231 p_encoded => 'F',
232 p_data => l_msg_data,
233 p_msg_index_out => l_msg_index_out
234 );
235 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
236 End LOOP;
237 END IF;
238 RAISE FND_API.G_EXC_ERROR;
239 END IF;
240
241 FND_FILE.Put_Line(FND_FILE.Log,'Calling CMRO API');
242
243 -- Complex Maintenance Repair and Overhaul
244 AHL_VENDORMERGE_GRP.Merge_Vendor(
245 p_api_version => 1.0
246 , p_init_msg_list => FND_API.G_FALSE
247 , p_commit => FND_API.G_FALSE
248 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
249 , x_return_status => l_return_status
250 , x_msg_count => l_msg_count
251 , x_msg_data => l_msg_data
252 , p_vendor_id => l_invoice_row.C_Vendor_Id
253 , p_dup_vendor_id => l_invoice_row.C_Dup_Vendor_Id
254 , p_vendor_site_id => l_invoice_row.C_Vendor_Site_Id
255 , p_dup_vendor_site_id => l_invoice_row.C_Dup_Vendor_Site_Id
256 , p_party_id => l_invoice_row.C_Party_Id
257 , p_dup_party_id => l_invoice_row.C_Dup_Party_Id
258 , p_party_site_id => l_invoice_row.C_Party_Site_Id
259 , p_dup_party_site_id => l_invoice_row.C_Dup_Party_Site_Id);
260
261 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
262 --
263 --
264 IF l_msg_count > 0 THEN
265 --
266 --
267 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
268 FND_MSG_PUB.Get( p_msg_index => i,
269 p_encoded => 'F',
270 p_data => l_msg_data,
271 p_msg_index_out => l_msg_index_out
272 );
273 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
274 End LOOP;
275 END IF;
276 RAISE FND_API.G_EXC_ERROR;
277 END IF;
278
279 FND_FILE.Put_Line(FND_FILE.Log,'Calling Netting API');
280
281 -- Netting
282 FUN_VENDORMERGE_GRP.Merge_Vendor(
283 p_api_version => 1.0
284 , p_init_msg_list => FND_API.G_FALSE
285 , p_commit => FND_API.G_FALSE
286 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
287 , p_return_status => l_return_status
288 , p_msg_count => l_msg_count
289 , p_msg_data => l_msg_data
290 , p_vendor_id => l_invoice_row.C_Vendor_Id
291 , p_dup_vendor_id => l_invoice_row.C_Dup_Vendor_Id
292 , p_vendor_site_id => l_invoice_row.C_Vendor_Site_Id
293 , p_dup_vendor_site_id => l_invoice_row.C_Dup_Vendor_Site_Id);
294
295 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
296 --
297 --
298 IF l_msg_count > 0 THEN
299 --
300 --
301 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
302 FND_MSG_PUB.Get( p_msg_index => i,
303 p_encoded => 'F',
304 p_data => l_msg_data,
305 p_msg_index_out => l_msg_index_out
306 );
307 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
308 End LOOP;
309 END IF;
310 RAISE FND_API.G_EXC_ERROR;
311 END IF;
312
313 FND_FILE.Put_Line(FND_FILE.Log,'Calling iRecruitment API');
314
315 -- iRecruitment
316 IRC_VENDORMERGE_GRP.Merge_Vendor(
317 p_api_version => 1.0
318 , p_init_msg_list => FND_API.G_FALSE
319 , p_commit => FND_API.G_FALSE
320 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
321 , p_return_status => l_return_status
322 , p_msg_count => l_msg_count
323 , p_msg_data => l_msg_data
324 , p_vendor_id => l_invoice_row.C_Vendor_Id
328 , p_party_id => l_invoice_row.C_Party_Id
325 , p_dup_vendor_id => l_invoice_row.C_Dup_Vendor_Id
326 , p_vendor_site_id => l_invoice_row.C_Vendor_Site_Id
327 , p_dup_vendor_site_id => l_invoice_row.C_Dup_Vendor_Site_Id
329 , p_dup_party_id => l_invoice_row.C_Dup_Party_Id
330 , p_party_site_id => l_invoice_row.C_Party_Site_Id
331 , p_dup_party_site_id => l_invoice_row.C_Dup_Party_Site_Id);
332
333 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
334 --
335 --
336 IF l_msg_count > 0 THEN
337 --
338 --
339 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
340 FND_MSG_PUB.Get( p_msg_index => i,
341 p_encoded => 'F',
342 p_data => l_msg_data,
343 p_msg_index_out => l_msg_index_out
344 );
345 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
346 End LOOP;
347 END IF;
348 RAISE FND_API.G_EXC_ERROR;
349 END IF;
350
351 -- Bug 6018743. Added the call to Oracle Loans API.
352 FND_FILE.Put_Line(FND_FILE.Log,'Calling OKL API');
353
354 --Oracle Loans
355 OKL_VENDORMERGE_GRP.Merge_Vendor(
356 p_api_version => 1.0
357 , p_init_msg_list => FND_API.G_FALSE
358 , p_commit => FND_API.G_FALSE
359 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
360 , p_return_status => l_return_status
361 , p_msg_count => l_msg_count
362 , p_msg_data => l_msg_data
363 , p_vendor_id => l_invoice_row.C_Vendor_Id
364 , p_dup_vendor_id => l_invoice_row.C_Dup_Vendor_Id
365 , p_vendor_site_id => l_invoice_row.C_Vendor_Site_Id
366 , p_dup_vendor_site_id => l_invoice_row.C_Dup_Vendor_Site_Id
367 , p_party_id => l_invoice_row.C_Party_Id
368 , p_dup_party_id => l_invoice_row.C_Dup_Party_Id
369 , p_party_site_id => l_invoice_row.C_Party_Site_Id
370 , p_dup_party_site_id => l_invoice_row.C_Dup_Party_Site_Id);
371
372 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
373 --
374 --
375 IF l_msg_count > 0 THEN
376 --
377 --
378 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
379 FND_MSG_PUB.Get( p_msg_index => i,
380 p_encoded => 'F',
381 p_data => l_msg_data,
382 p_msg_index_out => l_msg_index_out
383 );
384 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
385 End LOOP;
386 END IF;
387 RAISE FND_API.G_EXC_ERROR;
388 END IF;
389 -- Bug 6018743 ends.
390
391 -- Bug6679421 . Added the call to Oracle Price Protection (DPP) API.
392
393 FND_FILE.Put_Line(FND_FILE.Log,'Calling DPP API');
394
395 DPP_VENDORMERGE_GRP.Merge_Vendor(
396 p_api_version => 1.0
397 , p_init_msg_list => FND_API.G_FALSE
398 , p_commit => FND_API.G_FALSE
399 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
400 , p_return_status => l_return_status
401 , p_msg_count => l_msg_count
402 , p_msg_data => l_msg_data
403 , p_vendor_id => l_invoice_row.C_Vendor_Id
404 , p_dup_vendor_id => l_invoice_row.C_Dup_Vendor_Id
405 , p_vendor_site_id => l_invoice_row.C_Vendor_Site_Id
406 , p_dup_vendor_site_id => l_invoice_row.C_Dup_Vendor_Site_Id
407 , p_party_id => l_invoice_row.C_Party_Id
408 , p_dup_party_id => l_invoice_row.C_Dup_Party_Id
409 , p_party_site_id => l_invoice_row.C_Party_Site_Id
410 , p_dup_party_site_id => l_invoice_row.C_Dup_Party_Site_Id);
411
412 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
413 --
414 --
415 IF l_msg_count > 0 THEN
416 --
417 --
418 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
419 FND_MSG_PUB.Get( p_msg_index => i,
420 p_encoded => 'F',
421 p_data => l_msg_data,
422 p_msg_index_out => l_msg_index_out
423 );
424 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
425 End LOOP;
426 END IF;
427 RAISE FND_API.G_EXC_ERROR;
428 END IF;
429 --Bug6679421 ends.
430
431 FND_FILE.Put_Line(FND_FILE.Log,'Invoices: Complete');
432
433 END LOOP;
434 CLOSE Invoice_Cursor;
435 --
436 --
437
438 --
439 --
440 -- Process PO Related Impact Product Calls.
441 OPEN PO_Cursor;
442 LOOP
443 FETCH PO_Cursor INTO l_PO_Row;
444 EXIT WHEN PO_Cursor%NOTFOUND;
445
446 IF l_po_row.C_KEEP_SITE_FLAG = 'Y' AND
447 l_po_row.C_VENDOR_SITE_ID IS NULL THEN
448
452 WHERE vendor_id = l_po_row.C_VENDOR_ID
449 SELECT vendor_site_id
450 INTO l_po_row.C_VENDOR_SITE_ID
451 FROM ap_supplier_sites_all
453 AND vendor_site_code = l_po_row.C_VENDOR_SITE_CODE
454 AND org_id = l_po_row.C_ORG_ID; --Bug#7307532
455
456 END IF;
457
458 FND_FILE.Put_Line(FND_FILE.Log,'PO: Calling WSH API');
459
460 -- Call WSH API
461 WSH_VENDOR_PARTY_MERGE_PKG.Vendor_Party_Merge(
462 P_From_Vendor_Id => l_PO_Row.C_Dup_Vendor_ID,
463 P_To_Vendor_Id => l_PO_Row.C_Vendor_ID,
464 P_From_Party_Id => l_PO_Row.C_Dup_Party_ID,
465 P_To_Party_Id => l_PO_Row.C_Party_ID,
466 P_From_Vendor_Site_Id => l_PO_Row.C_Dup_Vendor_Site_ID,
467 P_To_Vendor_Site_Id => l_Po_Row.C_Vendor_Site_Id,
468 P_From_Party_Site_Id => l_PO_Row.C_Dup_Party_Site_ID,
469 P_To_Partysite_id => l_PO_Row.C_Party_Site_ID,
470 P_calling_mode => 'PO',
471 x_return_status => l_return_status,
472 x_msg_count => l_msg_count,
473 x_msg_data => l_msg_data);
474
475 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
476 --
477 --
478 IF l_msg_count > 0 THEN
479 --
480 --
481 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
482 FND_MSG_PUB.Get( p_msg_index => i,
483 p_encoded => 'F',
484 p_data => l_msg_data,
485 p_msg_index_out => l_msg_index_out
486 );
487 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
488 End LOOP;
489 END IF;
490 RAISE FND_API.G_EXC_ERROR;
491 END IF;
492
493 FND_FILE.Put_Line(FND_FILE.Log,'PO: Calling MRP API');
494
495 -- MRP
496 MRP_VendorMerge_GRP.Merge_Vendor(
497 p_api_version => 1.0
498 , p_init_msg_list => FND_API.G_FALSE
499 , p_commit => FND_API.G_FALSE
500 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
501 , x_return_status => l_return_status
502 , x_msg_count => l_msg_count
503 , x_msg_data => l_msg_data
504 , p_vendor_id => l_po_row.C_Vendor_Id
505 , p_vendor_site_id => l_po_row.C_Vendor_Site_Id
506 , p_dup_vendor_id => l_po_row.C_Dup_Vendor_Id
507 , p_dup_vendor_site_id => l_po_row.C_Dup_Vendor_Site_Id);
508
509 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
510 --
511 --
512 IF l_msg_count > 0 THEN
513 --
514 --
515 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
516 FND_MSG_PUB.Get( p_msg_index => i,
517 p_encoded => 'F',
518 p_data => l_msg_data,
519 p_msg_index_out => l_msg_index_out
520 );
521 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
522 END LOOP;
523 END IF;
524 RAISE FND_API.G_EXC_ERROR;
525 END IF;
526
527 FND_FILE.Put_Line(FND_FILE.Log,'PO: Calling Sourcing API');
528
529 -- Sourcing
530 PON_VENDORMERGE_GRP.Merge_Vendor(
531 p_api_version => 1.0
532 , p_vendor_id => l_po_row.C_Vendor_Id
533 , p_dup_vendor_id => l_po_row.C_Dup_Vendor_Id
534 , p_vendor_site_id => l_po_row.C_Vendor_Site_Id
535 , p_dup_vendor_site_id => l_po_row.C_Dup_Vendor_Site_Id
536 , p_party_id => l_po_row.C_Party_Id
537 , p_dup_party_id => l_po_row.C_Dup_Party_Id
538 , p_party_site_id => l_po_row.C_Party_Site_Id
539 , p_dup_party_site_id => l_po_row.C_Dup_Party_Site_Id
540 , p_init_msg_list => FND_API.G_FALSE
541 , p_commit => FND_API.G_FALSE
542 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
543 , p_return_status => l_return_status
544 , p_msg_count => l_msg_count
545 , p_msg_data => l_msg_data);
546
547 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
548 --
549 --
550 IF l_msg_count > 0 THEN
551 --
552 --
553 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
554 FND_MSG_PUB.Get( p_msg_index => i,
555 p_encoded => 'F',
556 p_data => l_msg_data,
557 p_msg_index_out => l_msg_index_out
558 );
559 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
560 END LOOP;
561 END IF;
562 RAISE FND_API.G_EXC_ERROR;
563 END IF;
564
565 FND_FILE.Put_Line(FND_FILE.Log,'PO: Calling iSP API');
566
567 -- iSupplier Portal
568 POS_VENDORMERGE_GRP.merge_vendor (
569 p_api_version => 1.0,
573 p_return_status => l_return_status,
570 p_init_msg_list => fnd_api.g_false,
571 p_commit => fnd_api.g_false,
572 p_validation_level => fnd_api.g_valid_level_full,
574 p_msg_count => l_msg_count,
575 p_msg_data => l_msg_data,
576 p_vendor_id => l_po_row.C_Vendor_Id,
577 p_dup_vendor_id => l_po_row.C_Dup_Vendor_Id,
578 p_vendor_site_id => l_po_row.C_Vendor_Site_Id,
579 p_dup_vendor_site_id => l_po_row.C_Dup_Vendor_Site_Id,
580 p_party_id => l_po_row.C_Party_Id,
581 p_dup_party_id => l_po_row.C_Dup_Party_Id,
582 p_party_site_id => l_po_row.C_Party_Site_Id,
583 p_dup_party_site_id => l_po_row.C_Dup_Party_Site_Id);
584
585 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
586 --
587 --
588 IF l_msg_count > 0 THEN
589 --
590 --
591 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
592 FND_MSG_PUB.Get( p_msg_index => i,
593 p_encoded => 'F',
594 p_data => l_msg_data,
595 p_msg_index_out => l_msg_index_out
596 );
597 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
598 END LOOP;
599 END IF;
600 RAISE FND_API.G_EXC_ERROR;
601 END IF;
602
603 FND_FILE.Put_Line(FND_FILE.Log,'PO: Calling Procurement API');
604
605 -- Procurement
606 PO_VENDORMERGE_GRP.merge_vendor (
607 p_api_version => 1.0,
608 p_init_msg_list => fnd_api.g_false,
609 p_commit => fnd_api.g_false,
610 p_validation_level => fnd_api.g_valid_level_full,
611 x_return_status => l_return_status,
612 x_msg_count => l_msg_count,
613 x_msg_data => l_msg_data,
614 p_vendor_id => l_po_row.C_Vendor_Id,
615 p_dup_vendor_id => l_po_row.C_Dup_Vendor_Id,
616 p_vendor_site_id => l_po_row.C_Vendor_Site_Id,
617 p_dup_vendor_site_id => l_po_row.C_Dup_Vendor_Site_Id,
618 p_party_id => l_po_row.C_Party_Id,
619 p_dup_party_id => l_po_row.C_Dup_Party_Id,
620 p_party_site_id => l_po_row.C_Party_Site_Id,
621 p_dup_party_site_id => l_po_row.C_Dup_Party_Site_Id);
622
623 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
624 --
625 --
626 IF l_msg_count > 0 THEN
627 --
628 --
629 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
630 FND_MSG_PUB.Get( p_msg_index => i,
631 p_encoded => 'F',
632 p_data => l_msg_data,
633 p_msg_index_out => l_msg_index_out
634 );
635 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
636 END LOOP;
637 END IF;
638 RAISE FND_API.G_EXC_ERROR;
639 END IF;
640
641 FND_FILE.Put_Line(FND_FILE.Log,'PO: Complete');
642 -- Bug 7592393 Start. Call to update receiving tables
643 FND_FILE.Put_Line(FND_FILE.Log,'PO: Calling Receiving API');
644
645 -- Receiving
646 RCV_UTILITIES.Merge_Vendor ( p_commit => fnd_api.g_false,
647 x_return_status => l_return_status,
648 x_msg_count => l_msg_count,
649 x_msg_data => l_msg_data,
650 p_vendor_id => l_po_row.C_Vendor_Id,
651 p_vendor_site_id => l_po_row.C_Vendor_Site_Id,
652 p_dup_vendor_id => l_po_row.C_Dup_Vendor_Id,
653 p_dup_vendor_site_id => l_po_row.C_Dup_Vendor_Site_Id);
654
655
656 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
657 IF l_msg_count > 0 THEN
658 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
659 FND_MSG_PUB.Get( p_msg_index => i,
660 p_encoded => 'F',
661 p_data => l_msg_data,
662 p_msg_index_out => l_msg_index_out
663 );
664 FND_FILE.Put_Line(FND_FILE.Log,l_msg_data);
665 END LOOP;
666 END IF;
667 RAISE FND_API.G_EXC_ERROR;
668 END IF;
669 -- Bug 7592393 End.
670
671
672 END LOOP;
673 CLOSE PO_Cursor;
674 --
675
676 EXCEPTION
677 WHEN FND_API.G_EXC_ERROR THEN
678 FND_MSG_PUB.Count_And_Get
679 ( p_count => l_msg_count,
680 p_data => l_msg_data
681 );
682 IF Invoice_Cursor%ISOPEN THEN
683 CLOSE Invoice_Cursor;
684 END IF;
685
686 IF PO_Cursor%ISOPEN THEN
687 CLOSE PO_Cursor;
691 APP_EXCEPTION.RAISE_EXCEPTION;
688 END IF;
689
690 ROLLBACK;
692
693 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
694 FND_MSG_PUB.Count_And_Get
695 ( p_count => l_msg_count,
696 p_data => l_msg_data
697 );
698 IF Invoice_Cursor%ISOPEN THEN
699 CLOSE Invoice_Cursor;
700 END IF;
701
702 IF PO_Cursor%ISOPEN THEN
703 CLOSE PO_Cursor;
704 END IF;
705
706 ROLLBACK;
707 APP_EXCEPTION.RAISE_EXCEPTION;
708
709 WHEN OTHERS THEN
710 IF FND_MSG_PUB.Check_Msg_Level
711 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
712 FND_MSG_PUB.Add_Exc_Msg
713 ( G_PKG_NAME ,
714 'AP_VENDOR_PARTY_MERGE_PKG.Other_Products_VendorMerge'
715 );
716 END IF;
717 FND_MSG_PUB.Count_And_Get
718 ( p_count => l_msg_count,
719 p_data => l_msg_data
720 );
721 IF (SQLCODE <> -20001) THEN
722 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
723 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
724 FND_FILE.Put_Line(FND_FILE.Log,sqlerrm);
725 END IF;
726
727 IF Invoice_Cursor%ISOPEN THEN
728 CLOSE Invoice_Cursor;
729 END IF;
730
731 IF PO_Cursor%ISOPEN THEN
732 CLOSE PO_Cursor;
733 END IF;
734
735 ROLLBACK;
736 APP_EXCEPTION.RAISE_EXCEPTION;
737
738 END Other_Products_VendorMerge;
739
740 PROCEDURE AP_TCA_Contact_Merge (
741 p_from_party_site_id IN NUMBER,
742 p_to_party_site_id IN NUMBER,
743 p_from_per_party_id IN NUMBER,
744 p_to_org_party_id IN NUMBER,
745 x_return_status OUT NOCOPY VARCHAR2,
746 x_msg_count OUT NOCOPY NUMBER,
747 x_msg_data OUT NOCOPY VARCHAR2
748 )
749 IS
750
751 l_relationship_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
752 l_relationship_id NUMBER;
753 l_party_id NUMBER;
754 l_party_number NUMBER;
755 l_contact_point_id NUMBER;
756 l_old_relationship_id NUMBER;
757 l_contact_point_rec HZ_CONTACT_POINT_v2pub.CONTACT_POINT_REC_TYPE;
758 l_edi_rec HZ_CONTACT_POINT_v2pub.EDI_REC_TYPE;
759 l_email_rec HZ_CONTACT_POINT_v2pub.EMAIL_REC_TYPE;
760 l_phone_rec HZ_CONTACT_POINT_v2pub.PHONE_REC_TYPE;
761 l_telex_rec HZ_CONTACT_POINT_v2pub.TELEX_REC_TYPE;
762 l_web_rec HZ_CONTACT_POINT_v2pub.WEB_REC_TYPE;
763 l_object_version_number NUMBER;
764 l_party_object_version_number NUMBER;
765 l_org_contact_rec HZ_PARTY_CONTACT_V2PUB.org_contact_rec_type;
766 l_org_contact_id NUMBER;
767 l_party_site_rec HZ_PARTY_SITE_V2PUB.party_site_rec_type;
768 l_party_site_num VARCHAR2(1);
769 l_party_site_id NUMBER;
770 l_party_site_number VARCHAR2(30);
771 l_location_id NUMBER;
772 l_msg_index_out NUMBER;
773
774 -- Bug 7297864- End
775 CURSOR c_contact_point (l_from_party_id NUMBER)
776 IS
777 select
778 hcpp.contact_point_id contact_point_id,
779 hcpp.owner_table_id contact_point_owner_id,
780 hcpp.owner_table_name contact_point_owner_name
781 from
782 hz_parties hp, fnd_user fu , hz_relationships hzr,
783 hz_party_usg_assignments hpua , HZ_CONTACT_POINTS hcpp
784 where hp.party_id = hzr.subject_id
785 and hzr.object_id = l_from_party_id ---:1 -- party of supplier
786 and hzr.relationship_type = 'CONTACT'
787 and hzr.relationship_code = 'CONTACT_OF'
788 and hzr.subject_type ='PERSON'
789 and hzr.object_type = 'ORGANIZATION'
790 and hzr.status = 'A'
791 and fu.person_party_id (+) = hp.party_id
792 and hp.party_id not in
793 (
794 select contact_party_id from pos_contact_requests pcr,
795 pos_supplier_mappings psm
796 where pcr.request_status='PENDING'
797 and psm.mapping_id = pcr.mapping_id
798 and psm.PARTY_ID = l_from_party_id --:2
799 and contact_party_id is not null
800 )
801 and hpua.party_id = hp.party_id
802 and hpua.status_flag = 'A'
803 and hpua.party_usage_code = 'SUPPLIER_CONTACT'
804 And hcpp.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
805 And hcpp.OWNER_TABLE_ID(+) = hzr.PARTY_ID
806 And hcpp.status (+)='A'
807 ;
808
809
810 BEGIN -- TCA_Contact_Merge Procedure begin
811
812 FND_FILE.Put_Line(FND_FILE.Log,'Start AP_VENDOR_PARTY_MERGE_PKG.AP_TCA_Contact_Merge API call');
813
814 -- For loop to fetch all contacts one by one
815 FOR contact_loop_c IN
816 ( SELECT vendor_contact_id, per_party_id, org_party_site_id, org_contact_id, party_site_id
817 FROM ap_supplier_contacts
818 WHERE org_party_site_id = p_from_party_site_id
819 )
820 LOOP
821
822 --Initialization of new relationship rec - relationship between 'To Party' and 'Contact'
823 l_relationship_rec.subject_id := contact_loop_c.per_party_id;
824 l_relationship_rec.subject_type := 'PERSON';
828 l_relationship_rec.object_table_name := 'HZ_PARTIES';
825 l_relationship_rec.subject_table_name := 'HZ_PARTIES';
826 l_relationship_rec.object_id := p_to_org_party_id;
827 l_relationship_rec.object_type := 'ORGANIZATION';
829 l_relationship_rec.relationship_code := 'CONTACT_OF';
830 l_relationship_rec.relationship_type := 'CONTACT';
831 l_relationship_rec.status := 'A';
832 l_relationship_rec.created_by_module:='AP_SUPPLIERS_API';
833
834 IF contact_loop_c.org_contact_id is not null THEN
835
836 select comments, contact_number, department_code,
837 department, title, job_title,
838 decision_maker_flag, job_title_code,
839 reference_use_flag, rank
840 into l_org_contact_rec.comments, l_org_contact_rec.contact_number,
841 l_org_contact_rec.department_code, l_org_contact_rec.department,
842 l_org_contact_rec.title, l_org_contact_rec.job_title,
843 l_org_contact_rec.decision_maker_flag, l_org_contact_rec.job_title_code,
844 l_org_contact_rec.reference_use_flag, l_org_contact_rec.rank
845 from hz_org_contacts
846 where org_contact_id = contact_loop_c.org_contact_id;
847
848 l_org_contact_rec.created_by_module := 'AP_SUPPLIERS_API';
849 l_org_contact_rec.party_rel_rec := l_relationship_rec;
850
851 hz_party_contact_v2pub.create_org_contact('T', l_org_contact_rec, l_org_contact_id,l_relationship_id,l_party_id,l_party_number,x_return_status,x_msg_count,x_msg_data);
852
853 FND_FILE.Put_Line(FND_FILE.Log,'call hz_party_contact_v2pub.create_org_contact');
854
855 ELSE
856 hz_relationship_v2pub.create_relationship('T',l_relationship_rec,l_relationship_id,l_party_id,l_party_number,x_return_status,x_msg_count,x_msg_data);
857
858 FND_FILE.Put_Line(FND_FILE.Log,'call hz_party_contact_v2pub.create_org_contact');
859 END IF;
860
861 select location_id
862 into l_location_id
863 from hz_party_sites
864 where party_site_id = contact_loop_c.party_site_id;
865
866 l_party_site_rec.location_id := l_location_id;
867 l_party_site_rec.created_by_module := 'AP_SUPPLIERS_API';
868 l_party_site_rec.application_id := 200;
869 l_party_site_rec.party_id := l_party_id;
870
871 fnd_profile.get('HZ_GENERATE_PARTY_SITE_NUMBER', l_party_site_num);
872 IF nvl(l_party_site_num, 'Y') = 'N' THEN
873 SELECT HZ_PARTY_SITE_NUMBER_S.Nextval
874 INTO l_party_site_rec.party_site_number
875 FROM DUAL;
876 END IF;
877
878 -- Create party_site between l_location_id and new party_id - l_party_id
879 hz_party_site_v2pub.create_party_site(
880 'T',
881 p_party_site_rec => l_party_site_rec,
882 x_return_status => x_return_status,
883 x_msg_count => x_msg_count,
884 x_msg_data => x_msg_data,
885 x_party_site_id => l_party_site_id,
886 x_party_site_number => l_party_site_number);
887
888 FND_FILE.Put_Line(FND_FILE.Log,'call hz_party_contact_v2pub.create_org_contact');
889
890 -- For loop to fetch all contact points belong to above contacts (Email-Ids and Phone numbers)
891 FOR contact_point_loop_c IN c_contact_point(p_from_per_party_id)
892 LOOP
893
894 -- Fetching contact point details
895 select
896 contact_point_type,
897 status,
898 owner_table_name,
899 primary_flag,
900 orig_system_reference,
901 content_source_type,
902 contact_point_purpose,
903 primary_by_purpose,
904 edi_transaction_handling,
905 edi_id_number,
906 edi_payment_method,
907 edi_payment_format,
908 edi_remittance_method,
909 edi_remittance_instruction,
910 edi_tp_header_id,
911 edi_ece_tp_location_code,
912 email_format,
913 email_address,
914 phone_calling_calendar,
915 last_contact_dt_time,
916 timezone_id,
917 phone_area_code,
918 phone_country_code,
919 phone_number,
920 phone_extension,
921 phone_line_type,
922 telex_number,
923 web_type,
924 url,
925 application_id
926 into
927 l_contact_point_rec.contact_point_type,
928 l_contact_point_rec.status,
929 l_contact_point_rec.owner_table_name,
930 l_contact_point_rec.primary_flag,
931 l_contact_point_rec.orig_system_reference,
932 l_contact_point_rec.content_source_type,
933 l_contact_point_rec.contact_point_purpose,
934 l_contact_point_rec.primary_by_purpose,
935 l_edi_rec.edi_transaction_handling,
936 l_edi_rec.edi_id_number,
937 l_edi_rec.edi_payment_method,
938 l_edi_rec.edi_payment_format,
939 l_edi_rec.edi_remittance_method,
940 l_edi_rec.edi_remittance_instruction,
941 l_edi_rec.edi_tp_header_id,
942 l_edi_rec.edi_ece_tp_location_code,
943 l_email_rec.email_format,
944 l_email_rec.email_address,
945 l_phone_rec.phone_calling_calendar,
946 l_phone_rec.last_contact_dt_time,
947 l_phone_rec.timezone_id,
948 l_phone_rec.phone_area_code,
949 l_phone_rec.phone_country_code,
950 l_phone_rec.phone_number,
951 l_phone_rec.phone_extension,
952 l_phone_rec.phone_line_type,
953 l_telex_rec.telex_number,
954 l_web_rec.web_type,
955 l_web_rec.url,
956 l_contact_point_rec.application_id
957 from hz_contact_points
958 where contact_point_id = contact_point_loop_c.contact_point_id;
959
960 l_contact_point_rec.owner_table_id := l_party_id;
961 l_contact_point_rec.created_by_module := 'AP_SUPPLIERS_API';
962
963 -- Creation of new contact points with new party_id
964 hz_contact_point_v2pub.create_contact_point('T',l_contact_point_rec,l_edi_rec,l_email_rec,l_phone_rec,l_telex_rec,l_web_rec,l_contact_point_id,x_return_status,x_msg_count,x_msg_data);
965
966 FND_FILE.Put_Line(FND_FILE.Log,'call hz_contact_point_v2pub.create_contact_point');
967
968 END LOOP;
969
970
971 UPDATE ap_supplier_contacts
972 SET org_party_site_id = p_to_party_site_id,
973 rel_party_id = l_party_id,
974 relationship_id = l_relationship_id,
975 party_site_id = l_party_site_id
976 WHERE vendor_contact_id = contact_loop_c.vendor_contact_id;
977
978 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
979 IF x_msg_count > 0 THEN
980 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
981 FND_MSG_PUB.Get( p_msg_index => i,
982 p_encoded => 'F',
983 p_data => x_msg_data,
984 p_msg_index_out => l_msg_index_out
985 );
986 FND_FILE.Put_Line(FND_FILE.Log,x_msg_data);
987 END LOOP;
988 END IF;
989 RAISE FND_API.G_EXC_ERROR;
990 END IF;
991
992 FND_FILE.Put_Line(FND_FILE.Log,'End AP_VENDOR_PARTY_MERGE_PKG.AP_TCA_Contact_Merge API call');
993
994 END LOOP;
995
996 COMMIT;
997
998 EXCEPTION
999 WHEN OTHERS THEN
1000 IF (SQLCODE <> -20001) THEN
1001 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1002 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1003 FND_FILE.Put_Line(FND_FILE.Log,sqlerrm);
1004 END IF;
1005
1006 -- Bug 7297864- End
1007
1008 END AP_TCA_Contact_Merge;
1009
1010 END AP_VENDOR_PARTY_MERGE_PKG;