1 PACKAGE BODY PON_VENDORMERGE_GRP as
2 -- $Header: PONVDMGB.pls 120.19 2006/10/04 10:03:20 ppaulsam noship $
3
4 -- Read the profile option that enables/disables the debug log
5 -- store the profile value for logging in a global constant variable
6
7 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
8 g_pkg_name CONSTANT VARCHAR2(30) := 'PON_VENDORMERGE_GRP';
9 g_api_name CONSTANT VARCHAR2(30) := 'MERGE_VENDOR';
10
11 /* FORWARD Declaration Start */
12
13 FUNCTION GET_ACTIVE_SITE_COUNT(p_dup_vendor_id IN NUMBER,
14 p_dup_vendor_site_id IN NUMBER) RETURN NUMBER;
15
16
17 PROCEDURE GET_MERGE_TO_VENDOR_INFO(p_vendor_id IN NUMBER,
18 p_vendor_site_id IN NUMBER,
19 x_return_status IN OUT NOCOPY VARCHAR2 ,
20 x_msg_count IN OUT NOCOPY NUMBER,
21 x_msg_data IN OUT NOCOPY VARCHAR2,
22 x_trading_partner_name OUT NOCOPY VARCHAR2,
23 x_vendor_site_code OUT NOCOPY VARCHAR2);
24
25 -- This will be called when two sites are being merged for same vendors
26 PROCEDURE MERGE_SAME_VENDOR_DIFF_SITES(p_vendor_id IN NUMBER,
27 p_vendor_site_id IN NUMBER,
28 p_vendor_site_code IN VARCHAR2,
29 p_dup_vendor_id IN NUMBER,
30 p_dup_vendor_site_id IN NUMBER,
31 x_return_status IN OUT NOCOPY VARCHAR2,
32 x_msg_count IN OUT NOCOPY NUMBER,
33 x_msg_data IN OUT NOCOPY VARCHAR2);
34
35 -- This will be called when two sites are being merged for different vendors
36 -- Here merge to supplier site can be null, in that case copy operation will be
37 -- perfomed and site will be created with same name as of merge from supplier site.
38
39 PROCEDURE MERGE_DIFF_VENDOR_AND_SITE( p_trading_partner_id IN NUMBER,
40 p_dup_trading_partner_id IN NUMBER,
41 p_vendor_id IN NUMBER,
42 p_vendor_site_id IN NUMBER,
43 p_vendor_site_code IN VARCHAR2,
44 p_dup_vendor_id IN NUMBER,
45 p_dup_vendor_site_id IN NUMBER,
46 p_trading_partner_name IN VARCHAR2,
47 x_return_status IN OUT NOCOPY VARCHAR2,
48 x_msg_count IN OUT NOCOPY NUMBER,
49 x_msg_data IN OUT NOCOPY VARCHAR2);
50
51 -- This will be called when two sites are being merged for different vendors
52 -- and the site being mearged is the last site.
53 -- Here the supplier being mearged will be marked as inactive.
54
55 PROCEDURE MERGE_DIFF_VENDOR_LAST_SITE(p_trading_partner_id IN NUMBER,
56 p_dup_trading_partner_id IN NUMBER,
57 p_vendor_id IN NUMBER,
58 p_vendor_site_id IN NUMBER,
59 p_vendor_site_code IN VARCHAR2,
60 p_dup_vendor_id IN NUMBER,
61 p_dup_vendor_site_id IN NUMBER,
62 p_trading_partner_name IN VARCHAR2,
63 x_return_status IN OUT NOCOPY VARCHAR2,
64 x_msg_count IN OUT NOCOPY NUMBER,
65 x_msg_data IN OUT NOCOPY VARCHAR2);
66
67 -- This is called to insert a record in pon_supplier_activities
68 -- when two different vendor / vendor sites are merged.
69
70 PROCEDURE UPDATE_SUPPLIER_ACTIVITY(p_dup_trading_partner_id IN NUMBER,
71 x_return_status IN OUT NOCOPY VARCHAR2,
72 x_msg_count IN OUT NOCOPY NUMBER,
73 x_msg_data IN OUT NOCOPY VARCHAR2);
74
75 /* FORWARD Declaration End */
76
77
78 PROCEDURE MERGE_VENDOR (p_api_version IN NUMBER,
79 p_vendor_id IN NUMBER,
80 p_dup_vendor_id IN NUMBER,
81 p_vendor_site_id IN NUMBER,
82 p_dup_vendor_site_id IN NUMBER,
83 p_party_id IN NUMBER,
84 P_dup_party_id IN NUMBER,
85 p_party_site_id IN NUMBER,
86 p_dup_party_site_id IN NUMBER,
87 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
88 p_commit IN VARCHAR2 default FND_API.G_FALSE,
89 p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL,
90 p_return_status OUT NOCOPY VARCHAR2,
91 p_msg_count OUT NOCOPY NUMBER,
92 p_msg_data OUT NOCOPY VARCHAR2)
93 IS
94
95 l_api_version CONSTANT NUMBER := 1.0;
96 l_procedure_name VARCHAR2(20) := 'MERGE_VENDOR';
97
98 l_progress NUMBER;
99 x_trading_partner_id pon_bid_headers.trading_partner_id%type;
100 x_trading_partner_name pon_bid_headers.trading_partner_name%type;
101 x_vendor_site_code po_vendor_sites_all.vendor_site_code%type;
102 l_num_active_sites NUMBER;
103
104 BEGIN --{
105
106 l_progress := 100;
107
108 IF (g_fnd_debug = 'Y') THEN --{
109 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
110 FND_LOG.string (log_level => FND_LOG.level_procedure,
111 module => g_pkg_name || l_procedure_name,
112 message => ' l_progress ' || l_progress
113 || ' p_api_version ' || p_api_version
114 || ' p_init_msg_list ' || p_init_msg_list
115 || ' p_commit ' || p_commit
116 || ' p_validation_level ' || p_validation_level
117 || ' p_return_status ' || p_return_status
118 || ' p_msg_count ' || p_msg_count
119 || ' p_msg_data ' || p_msg_data
120 || ' p_vendor_id ' || p_vendor_id
121 || ' p_dup_vendor_id ' || p_dup_vendor_id
122 || ' p_vendor_site_id ' || p_vendor_site_id
123 || ' p_dup_vendor_site_id ' || p_dup_vendor_site_id
124 || ' p_party_id ' || p_party_id
125 || ' P_dup_party_id ' || P_dup_party_id
126 || ' p_party_site_id ' || p_party_site_id
127 || ' p_dup_party_site_id ' || p_dup_party_site_id );
128 END IF;
129 END IF; --}
130
131 fnd_file.put_line (fnd_file.log, l_progress|| 'Start : PON_VENDORMERGE_GRP.MERGE_VENDOR ');
132 fnd_file.put_line (fnd_file.log, l_progress|| ' p_vendor_id ' || p_vendor_id);
133 fnd_file.put_line (fnd_file.log, l_progress|| ' p_dup_vendor_id ' || p_dup_vendor_id);
134 fnd_file.put_line (fnd_file.log, l_progress|| ' p_vendor_site_id ' || p_vendor_site_id);
135 fnd_file.put_line (fnd_file.log, l_progress|| ' p_dup_vendor_site_id ' || p_dup_vendor_site_id);
136 fnd_file.put_line (fnd_file.log, l_progress|| ' p_party_id ' || p_party_id);
137 fnd_file.put_line (fnd_file.log, l_progress|| ' P_dup_party_id ' || P_dup_party_id);
138 fnd_file.put_line (fnd_file.log, l_progress|| ' p_party_site_id ' || p_party_site_id);
139 fnd_file.put_line (fnd_file.log, l_progress|| ' p_dup_party_site_id ' || p_dup_party_site_id );
140
141 -- Standard call to check for call compatibility
142
143 IF (NOT FND_API.Compatible_API_Call(l_api_version,p_api_version,g_api_name,g_pkg_name)) THEN
144 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
145 END IF;
146
147 l_progress := 101;
148 fnd_file.put_line (fnd_file.log, l_progress|| 'Done compatibility Check ....');
149
150 -- Check p_init_msg_list
151 IF FND_API.to_Boolean(p_init_msg_list) THEN
152 FND_MSG_PUB.initialize;
153 END IF;
154
155 l_progress := 102;
156 fnd_file.put_line (fnd_file.log, l_progress|| 'Done FND_MSG_PUB.initialize ');
157
158 -- Initialize API return status to success
159 p_return_status := FND_API.G_RET_STS_SUCCESS;
160
161
162 -- Raise an Exception if l_party_id = null or l_party_id = -1
163
164 IF ( p_party_id = null OR p_party_id = -1) THEN --{
165 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
167 END IF; --}
168
169
170 l_progress := 103;
171 fnd_file.put_line (fnd_file.log, l_progress|| 'Done party_id check....');
172
173 -- Raise an Exception if l_dup_party_id = null or l_dup_party_id = -1
174
175 IF ( p_dup_party_id = null OR p_dup_party_id = null -1) THEN --{
176 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
178 END IF; --}
179
180 l_progress := 104;
181 fnd_file.put_line (fnd_file.log, l_progress|| 'Done dup_party_id check....');
182
183 /*
184 We have Following scenario to deal with Merge routine.
185
186 |===============================================================================================|
187 | |From supplier | From suppl site| Copy | To supplier | To supplier site | |
188 | |==============|================|======|=============|==================| |
189 | |p_dup_vendor | p_dup_site_id | | p_vendor_id | p_vendor_site_id | |
190 |========|==============|================|======|=============|==================|==============|
191 |========|==============|================|======|=============|==================|==============|
192 | Case 1 |Supp A | Site 1 | | Supp A | Site 2 | Allowed |
193 |========|==============|================|======|=============|==================|==============|
194 | Case 2 |Supp A | Site 1 | | Supp B | Site 2 | Allowed |
195 |========|==============|================|======|=============|==================|==============|
196 | |Supp A | Null | | Supp B | Null | Not Allowed |
197 | |==============|================|======|=============|==================|==============|
198 | |Supp A | Null | | Supp B | Site 2 | Not Allowed |
199 | |==============|================|======|=============|==================|==============|
200 | |Supp A | Site 1 | | Supp B | Null | Not Allowed |
201 |========|==============|================|======|=============|==================|==============|
202 | Case 3 |Supp A | Site 1 | Y | Supp B | Null | Allowed(**) |
203 |========|==============|================|======|=============|==================|==============|
204
205
206 (**) Case 3: For this case, a new supplier site is created with the site id "Site 1" under Supp B,
207 So this essentially means that it is the same as Case 2 in the table but
208 with the same site code.
209
210 Case 1 : From supplier(p_dup_vendor_id) and To supplier(p_vendor_id) are same
211 and From suppl site(p_dup_vendor_site_id) and To supplier site(p_vendor_site_id)
212 are different that is we are mergeing to sites which belongs to same supllier.
213
214 Case 2 : From supplier(p_dup_vendor_id) and To supplier(p_vendor_id) are different
215 and From suppl site(p_dup_vendor_site_id) and To supplier site(p_vendor_site_id)
216 are different that is we are mergeing to sites which belongs to different supllier,
217 which means we are transferring a site from Supplier1 to Supplier2.
218
219 We will have two scenario in this case as below,
220 a) Site being merged is not the last active site for supplier being merged.
221 In this case Supp A will remain Active and only users that are associated with
222 the Site 1 will be moved over to SuppB Site 2.
223 Also, there should not be an update for records that don't refer the 'from'
224 site being merged as the supplier is still active.
225
226 b) Site being merged is the last active site for supplier being merged.
227 In this case Supplier A will become inactive and all users associated with supplier A
228 will move to Supplier B.
229
230 Case 3 : From supplier(p_dup_vendor_id) and To supplier(p_vendor_id) are different
231 To supplier site(p_vendor_site_id) is null and copy flag is enabled, i.e. copy
232 From Supplier Site(p_dup_vendor_site_id) under To Supplier(p_vendor_id).
233
234 Implementation Approch :
235 =======================
236
237 Case 1: We will update vendor_site_id, vendor_site code for all tables that have reference
238 to vendor_site_id.
239
240 Case 2: The implementation will be as below for scenario a and b described above,
241
242 a) Update vendor_id, trading_partner_id, vendor_site_id
243 on all tables with reference to these cols
244 where vendor_id = p_dup_vendor_id
245 and trading_partner_id = p_dup_trading_partner_id
246 and (vendor_site_id = p_dup_vendor_site_id ).
247
248 The record should not be updated, if the vendor_site_id
249 is NOT populated.
250
251 b) Update vendor_id, trading_partner_id, vendor_site_id,
252 on all tables with reference to these cols
253 where vendor_id = p_dup_vendor_id
254 and trading_partner_id = p_dup_trading_partner_id
255 and vendor_site_id = p_dup_vendor_site_id
256
257 The record should be updated even if vendor_site_id is
258 not populated.
259
260 Case 3: Here new supplier site will be created with the site code same as of
261 From Supplier Site(p_dup_vendor_site_id) under To Supplier(p_vendor_id).
262 This is the same as Case 2 but with the same site code.
263 */
264
265 -- Get the vendor information for the site we're merging to
266 GET_MERGE_TO_VENDOR_INFO(p_vendor_id => p_vendor_id,
267 p_vendor_site_id => p_vendor_site_id,
268 x_return_status => p_return_status,
269 x_msg_count => p_msg_count,
270 x_msg_data => p_msg_data,
271 x_trading_partner_name => x_trading_partner_name,
272 x_vendor_site_code => x_vendor_site_code);
273
274 l_progress := 105;
275 fnd_file.put_line (fnd_file.log, l_progress|| ' Done GET_MERGE_TO_VENDOR_INFO ');
276
277 --{
278 IF (p_vendor_id = p_dup_vendor_id and p_vendor_site_id <> p_dup_vendor_site_id) THEN
279 BEGIN --{
280 l_progress := 106;
281 fnd_file.put_line (fnd_file.log, l_progress|| ' Merge Site From Same Suppliers ');
282 /* Case 1*/
283
284 MERGE_SAME_VENDOR_DIFF_SITES(p_vendor_id => p_vendor_id,
285 p_vendor_site_id => p_vendor_site_id,
286 p_vendor_site_code => x_vendor_site_code,
287 p_dup_vendor_id => p_dup_vendor_id,
288 p_dup_vendor_site_id => p_dup_vendor_site_id,
289 x_return_status => p_return_status,
290 x_msg_count => p_msg_count,
291 x_msg_data => p_msg_data);
292 END; --}
293 ELSE IF (p_vendor_id <> p_dup_vendor_id) THEN
294 BEGIN --{
295 /* Case 2 and Case3 */
296 l_progress := 107;
297
298 l_num_active_sites := GET_ACTIVE_SITE_COUNT(p_dup_vendor_id, p_dup_vendor_site_id);
299
300 l_progress := 108;
301 fnd_file.put_line (fnd_file.log, l_progress|| 'AFter getting active site count '||l_num_active_sites);
302
303 IF (l_num_active_sites > 0) THEN
304 /* Case 2b*/
305 fnd_file.put_line (fnd_file.log, l_progress|| ' Merge diff Suppliers - Not last Site');
306
307 MERGE_DIFF_VENDOR_AND_SITE(p_trading_partner_id => p_party_id,
308 p_dup_trading_partner_id => p_dup_party_id,
309 p_vendor_id => p_vendor_id,
310 p_vendor_site_id => p_vendor_site_id,
311 p_vendor_site_code => x_vendor_site_code,
312 p_dup_vendor_id => p_dup_vendor_id,
313 p_dup_vendor_site_id => p_dup_vendor_site_id,
314 p_trading_partner_name => x_trading_partner_name,
315 x_return_status => p_return_status,
316 x_msg_count => p_msg_count,
317 x_msg_data => p_msg_data);
318 l_progress := 109;
319 fnd_file.put_line (fnd_file.log, l_progress|| 'After MERGE_DIFF_VENDOR_AND_SITE');
320 ELSE
321 /* Case 2a*/
322
323 fnd_file.put_line (fnd_file.log, l_progress|| ' Merge diff Suppliers - Merge last Site');
324
325 MERGE_DIFF_VENDOR_LAST_SITE(p_trading_partner_id => p_party_id,
326 p_dup_trading_partner_id => p_dup_party_id,
327 p_vendor_id => p_vendor_id,
328 p_vendor_site_id => p_vendor_site_id,
329 p_vendor_site_code => x_vendor_site_code,
330 p_dup_vendor_id => p_dup_vendor_id,
331 p_dup_vendor_site_id => p_dup_vendor_site_id,
332 p_trading_partner_name => x_trading_partner_name,
333 x_return_status => p_return_status,
334 x_msg_count => p_msg_count,
335 x_msg_data => p_msg_data);
336 l_progress := 110;
337 fnd_file.put_line (fnd_file.log, l_progress|| 'After Merge diff Suppliers - Merge last Site');
338 END IF;
339
340 /*
341 After the merge there can be cases where this table
342 does not have records for the bids that got updated
343 due to the merge and hence we might need to insert
344 extra rows here.
345 Same logic as in the upgrade
346 */
347
348 UPDATE_SUPPLIER_ACTIVITY(p_dup_trading_partner_id => p_dup_party_id,
349 x_return_status => p_return_status,
350 x_msg_count => p_msg_count,
351 x_msg_data => p_msg_data);
352
353 l_progress := 111;
354 fnd_file.put_line (fnd_file.log, l_progress|| 'After UPDATE_SUPPLIER_ACTIVITY ...');
355
356 EXCEPTION WHEN OTHERS THEN
357 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
358 l_progress := 000;
359
360 fnd_file.put_line (fnd_file.log, l_progress|| 'In Exception ...'||' SQLERRM = ' || SQLERRM);
361 fnd_file.put_line (fnd_file.log, l_progress|| 'In Exception ...'||' SQLCODE = '|| SQLCODE);
362
363 IF (G_FND_DEBUG = 'Y') THEN
364 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
365 FND_LOG.STRING(log_level => FND_LOG.LEVEL_EXCEPTION,
366 module => g_pkg_name || l_procedure_name,
367 message => 'Exception '
368 ||' l_progress = '||l_progress
369 ||' SQLERRM = ' || SQLERRM
370 ||' SQLCODE = '|| SQLCODE);
371 END IF;
372 END IF;
373
374 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
375 THEN
376 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, g_api_name);
377 END IF;
378
379 FND_MSG_PUB.Count_And_Get( p_count => p_msg_count,
380 p_data => p_msg_data);
381 END; --}
382 END IF;
383 END IF;
384 --}
385
386 l_progress := 112;
387 fnd_file.put_line (fnd_file.log, l_progress|| ' Before PON_CONTERMS_UTL_PVT.updateDelivOnVendorMerge call.... ');
388
389 -- if there are any conterms and if
390 -- contracts is installed, then merge the
391 -- deliverables for these 2 vendors
392
393 PON_CONTERMS_UTL_PVT.updateDelivOnVendorMerge(p_dup_vendor_id,
394 p_dup_vendor_site_id,
395 p_vendor_id,
396 p_vendor_site_id,
397 p_msg_data,
398 p_msg_count,
399 p_return_status);
400
401 l_progress := 112;
402
403 fnd_file.put_line (fnd_file.log, l_progress|| ' After PON_CONTERMS_UTL_PVT.updateDelivOnVendorMerge call.... ');
404 fnd_file.put_line (fnd_file.log, l_progress|| 'End of PON_VENDORMERGE_GRP.MERGE_VENDOR ');
405
406 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
407 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
408 l_progress := 1000;
409 fnd_file.put_line (fnd_file.log, l_progress|| 'In Exception ...'||' SQLERRM = ' || SQLERRM);
410 fnd_file.put_line (fnd_file.log, l_progress|| 'In Exception ...'||' SQLCODE = '|| SQLCODE);
411
412 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
413 p_data => p_msg_data);
414 WHEN OTHERS THEN
415 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
416 IF (G_FND_DEBUG = 'Y') THEN
417 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
418 FND_LOG.STRING(log_level => FND_LOG.LEVEL_EXCEPTION,
419 module => g_pkg_name || l_procedure_name,
420 message => 'Exception '
421 ||' l_progress = '||l_progress
422 ||' SQLERRM = ' || SQLERRM
423 ||' SQLCODE = '|| SQLCODE);
424 END IF;
425 END IF;
426
427 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
428 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, g_api_name);
429 END IF;
430
431 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
432 p_data => p_msg_data);
433 END; --}
434
435 /* END OF MAIN ROUTINE - MERGE_VENDOR */
436
437 /* PROCEDURE AND FUNCTION CALL STARTS */
438
439 FUNCTION GET_ACTIVE_SITE_COUNT(p_dup_vendor_id IN NUMBER,
440 p_dup_vendor_site_id IN NUMBER)
441 RETURN NUMBER
442 AS
443 l_num_active_sites NUMBER;
444 BEGIN
445 -- select count of active sites (besides the site being merged)
446
447 -- Note: Verify if we need to consider any ap table instead of
448 -- po_vendor_sites_all to check active sites.
449
450 SELECT count(*)
451 INTO l_num_active_sites
452 FROM po_vendor_sites_all
453 WHERE vendor_id = p_dup_vendor_id
454 AND vendor_site_id <> p_dup_vendor_site_id
455 AND nvl(inactive_date, sysdate+1) > sysdate;
456 return l_num_active_sites;
457 END;
458
459 PROCEDURE GET_MERGE_TO_VENDOR_INFO(p_vendor_id IN NUMBER,
460 p_vendor_site_id IN NUMBER,
461 x_return_status IN OUT NOCOPY VARCHAR2 ,
462 x_msg_count IN OUT NOCOPY NUMBER,
463 x_msg_data IN OUT NOCOPY VARCHAR2,
464 x_trading_partner_name OUT NOCOPY VARCHAR2,
465 x_vendor_site_code OUT NOCOPY VARCHAR2)
466 IS
467 l_procedure_name VARCHAR2(30) := 'GET_MERGE_TO_VENDOR_INFO';
468
469 BEGIN --{
470 -- select the vendor information for the site we're merging to
471 SELECT pv.vendor_name, pvs.vendor_site_code
472 INTO x_trading_partner_name, x_vendor_site_code
473 FROM po_vendors pv, po_vendor_sites_all pvs
474 WHERE pv.vendor_id = p_vendor_id
475 AND pv.vendor_id = pvs.vendor_id
476 AND pvs.vendor_site_id = p_vendor_site_id;
477
478 EXCEPTION WHEN NO_DATA_FOUND THEN
479 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
480
481 fnd_file.put_line (fnd_file.log, 'In Exception - GET_MERGE_TO_VENDOR_INFO ...'||' SQLERRM = ' || SQLERRM);
482 fnd_file.put_line (fnd_file.log, 'In Exception - GET_MERGE_TO_VENDOR_INFO ...'||' SQLCODE = '|| SQLCODE);
483
484 IF (G_FND_DEBUG = 'Y') THEN
485 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
486 FND_LOG.STRING(log_level => FND_LOG.LEVEL_EXCEPTION,
487 module => g_pkg_name || l_procedure_name,
488 message => 'Exception '
489 || ' p_vendor_site_id ' || p_vendor_site_id
490 || ' p_vendor_id ' || p_vendor_id
491 || ' SQLCODE = '|| SQLCODE
492 || ' SQLERRM = ' || SQLERRM);
493 END IF;
494 END IF;
495
496 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
497 THEN
498 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, g_api_name);
499 END IF;
500
501 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
502 p_data => x_msg_data);
503 END; --}
504
505
506 PROCEDURE MERGE_SAME_VENDOR_DIFF_SITES(p_vendor_id IN NUMBER,
507 p_vendor_site_id IN NUMBER,
508 p_vendor_site_code IN VARCHAR2,
509 p_dup_vendor_id IN NUMBER,
510 p_dup_vendor_site_id IN NUMBER,
511 x_return_status IN OUT NOCOPY VARCHAR2,
512 x_msg_count IN OUT NOCOPY NUMBER,
513 x_msg_data IN OUT NOCOPY VARCHAR2)
514 IS
515 l_procedure_name VARCHAR2(30) := 'MERGE_SAME_VENDOR_DIFF_SITES';
516 l_progress NUMBER;
517 l_trading_partner_id NUMBER;
518 l_dup_vendor_site_code po_vendor_sites_all.vendor_site_code%type;
519
520 BEGIN --{
521
522 /*
523 Followling table will be updated while merging at SITE Level.
524 1. pon_bid_headers
525 2. pon_bidding_parties
526 3. pon_party_line_exclusions
527 4. pon_pf_supplier_formula
528 */
529
530 l_progress := 301;
531 fnd_file.put_line (fnd_file.log, l_progress|| 'Start : MERGE_SAME_VENDOR_DIFF_SITES');
532
533 /* Bug 4948321 : FTS for pon_bid_headers due to vendorId comparision.
534 - Retrieve trading_partner_id from ap_suppliers and use that
535 in update query for pon_bid_headers.
536 */
537
538 SELECT party_id
539 INTO l_trading_partner_id
540 FROM ap_suppliers
541 WHERE vendor_id=p_vendor_id;
542
543 UPDATE pon_bid_headers pbh1
544 SET pbh1.vendor_site_id = p_vendor_site_id,
545 pbh1.vendor_site_code = decode(pbh1.vendor_site_code,null,null,'-1','-1',p_vendor_site_code),
546 pbh1.last_updated_by = -1,
547 pbh1.last_update_date = sysdate
548 WHERE pbh1.trading_partner_id = l_trading_partner_id
549 AND pbh1.vendor_site_id = p_dup_vendor_site_id
550 AND NOT EXISTS (SELECT 'DUPLICATE'
551 FROM pon_bid_headers pbh2
552 WHERE pbh2.auction_header_id = pbh1.auction_header_id
553 AND pbh2.vendor_id = pbh1.vendor_id
554 AND pbh2.trading_partner_contact_id = pbh1.trading_partner_contact_id
555 AND pbh2.trading_partner_id = pbh1.trading_partner_id
556 AND pbh2.vendor_site_id = p_vendor_site_id);
557
558 l_progress := 302;
559 fnd_file.put_line (fnd_file.log, l_progress|| 'Done : pon_bid_headers');
560
561 -- Bug 5100555
562 -- This is done to update site code for reusable invitation list
563 -- putting separate query to take care of duplication of supplier
564 -- in list.
565
566 SELECT pvs.vendor_site_code
567 INTO l_dup_vendor_site_code
568 FROM po_vendors pv, po_vendor_sites_all pvs
569 WHERE pv.vendor_id = p_dup_vendor_id
570 AND pv.vendor_id = pvs.vendor_id
571 AND pvs.vendor_site_id = p_dup_vendor_site_id;
572
573 UPDATE pon_bidding_parties pbp1
574 set pbp1.vendor_site_id = decode(pbp1.vendor_site_id , -1,-1, p_vendor_site_id),
575 pbp1.vendor_site_code = decode(pbp1.vendor_site_code,'-1','-1',p_vendor_site_code)
576 WHERE pbp1.trading_partner_id = l_trading_partner_id
577 AND ((pbp1.vendor_site_id = p_dup_vendor_site_id and pbp1.list_id = -1)
578 OR
579 (pbp1.vendor_site_code = l_dup_vendor_site_code and pbp1.list_id <> -1)
580 OR
581 ( pbp1.vendor_site_code = l_dup_vendor_site_code
582 and pbp1.auction_header_id is not null
583 and exists( select 1 from pon_auction_headers_all pah
584 where pah.auction_header_id = pbp1.auction_header_id
585 and pah.global_template_flag='Y' )
586 )
587 )
588 AND NOT EXISTS (SELECT 'DUPLICATE'
589 FROM pon_bidding_parties pbp2
590 WHERE pbp2.auction_header_id = pbp1.auction_header_id
591 AND pbp2.list_id = pbp1.list_id
592 AND pbp2.trading_partner_id = pbp1.trading_partner_id
593 AND decode(pbp2.vendor_site_code,'-1',p_vendor_site_code,pbp2.vendor_site_code) = p_vendor_site_code
594 AND decode(pbp2.vendor_site_id,-1, p_vendor_site_id,pbp2.vendor_site_id) = p_vendor_site_id);
595
596 l_progress := 303;
597 fnd_file.put_line (fnd_file.log, l_progress|| 'Done : pon_bidding_parties');
598
599 UPDATE pon_party_line_exclusions pple1
600 SET pple1.vendor_site_id = p_vendor_site_id,
601 pple1.last_updated_by = -1,
602 pple1.last_update_date = sysdate
603 WHERE pple1.vendor_site_id = p_dup_vendor_site_id
604 AND NOT EXISTS (SELECT 'DUPLICATE'
605 FROM pon_party_line_exclusions pple2
606 WHERE pple2.auction_header_id = pple1.auction_header_id
607 AND pple2.line_number = pple1.line_number
608 AND pple2.trading_partner_id = pple1.trading_partner_id
609 AND pple2.vendor_site_id = p_vendor_site_id);
610
611 l_progress := 304;
612 fnd_file.put_line (fnd_file.log, l_progress|| 'Done : pon_party_line_exclusions');
613
614 UPDATE pon_pf_supplier_formula ppsf1
615 SET ppsf1.vendor_site_id = p_vendor_site_id
616 WHERE ppsf1.vendor_site_id = p_dup_vendor_site_id
617 AND NOT EXISTS (SELECT 'DUPLICATE'
618 FROM pon_pf_supplier_formula ppsf2
619 WHERE ppsf2.auction_header_id = ppsf1.auction_header_id
620 AND ppsf2.trading_partner_id = ppsf1.trading_partner_id
621 AND ppsf2.vendor_site_id = p_vendor_site_id);
622
623 l_progress := 305;
624 fnd_file.put_line (fnd_file.log, l_progress|| 'Done : pon_pf_supplier_formula');
625
626 EXCEPTION WHEN NO_DATA_FOUND THEN
627 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
628
629 fnd_file.put_line (fnd_file.log, l_progress || 'In Exception - MERGE_SAME_VENDOR_DIFF_SITES...'||' SQLERRM = ' || SQLERRM);
630 fnd_file.put_line (fnd_file.log, l_progress || 'In Exception - MERGE_SAME_VENDOR_DIFF_SITES...'||' SQLCODE = '|| SQLCODE);
631
632 IF (G_FND_DEBUG = 'Y') THEN
633 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
634 FND_LOG.STRING(log_level => FND_LOG.LEVEL_EXCEPTION,
635 module => g_pkg_name || l_procedure_name,
636 message => 'Exception SQLERRM = ' || SQLERRM
637 ||' SQLCODE = '|| SQLCODE);
638 END IF;
639 END IF;
640
641 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
642 THEN
643 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, g_api_name);
644 END IF;
645
646 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
647 p_data => x_msg_data);
648
649 END; --}
650
651
652 PROCEDURE MERGE_DIFF_VENDOR_AND_SITE(p_trading_partner_id IN NUMBER,
653 p_dup_trading_partner_id IN NUMBER,
654 p_vendor_id IN NUMBER,
655 p_vendor_site_id IN NUMBER,
656 p_vendor_site_code IN VARCHAR2,
657 p_dup_vendor_id IN NUMBER,
658 p_dup_vendor_site_id IN NUMBER,
659 p_trading_partner_name IN VARCHAR2,
660 x_return_status IN OUT NOCOPY VARCHAR2,
661 x_msg_count IN OUT NOCOPY NUMBER,
662 x_msg_data IN OUT NOCOPY VARCHAR2)
663 IS
664 l_procedure_name VARCHAR2(30) := 'MERGE_DIFF_VENDOR_AND_SITE';
665 l_progress NUMBER;
666 l_dup_vendor_site_code po_vendor_sites_all.vendor_site_code%type;
667
668 BEGIN --{
669
670 /* CASE 2b - site being merged is not the last site
671
672 Followling table will be updated while merging at Vendor level.
673 1. pon_bid_item_prices
674 2. pon_bid_headers
675 3. pon_bidding_parties
676 4. pon_party_line_exclusions
677 5. pon_pf_supplier_formula
678 */
679
680 l_progress := 500;
681 fnd_file.put_line (fnd_file.log, l_progress|| ' Start : MERGE_DIFF_VENDOR_AND_SITE');
682
683 UPDATE pon_bid_item_prices pbip1
684 SET pbip1.bid_trading_partner_id = p_trading_partner_id,
685 pbip1.last_updated_by = -1,
686 pbip1.last_update_date = sysdate
687 WHERE pbip1.bid_trading_partner_id = p_dup_trading_partner_id
688 AND pbip1.bid_number IN (SELECT bid_number
689 FROM pon_bid_headers pbh1
690 WHERE pbh1.trading_partner_id = p_dup_trading_partner_id
691 AND pbh1.vendor_id = p_dup_vendor_id
692 AND pbh1.vendor_site_id= p_dup_vendor_site_id
693 AND NOT EXISTS (SELECT 'DUPLICATE'
694 FROM pon_bid_headers pbh2
695 WHERE pbh2.auction_header_id = pbh1.auction_header_id
696 AND pbh2.vendor_id = p_vendor_id
697 AND pbh2.vendor_site_id = p_vendor_site_id
698 AND pbh2.trading_partner_id = p_trading_partner_id
699 AND pbh2.trading_partner_contact_id = pbh1.trading_partner_contact_id)
700 );
701
702 l_progress := 501;
703 fnd_file.put_line (fnd_file.log, l_progress|| ' Done: pon_bid_item_prices ');
704
705 UPDATE pon_bid_headers pbh1
706 SET pbh1.trading_partner_id = p_trading_partner_id,
707 pbh1.trading_partner_name = p_trading_partner_name,
708 pbh1.vendor_id = p_vendor_id,
709 pbh1.vendor_site_id = p_vendor_site_id,
710 pbh1.vendor_site_code = p_vendor_site_code,
711 pbh1.last_updated_by = -1,
712 pbh1.last_update_date = sysdate
713 WHERE pbh1.trading_partner_id = p_dup_trading_partner_id
714 AND pbh1.vendor_id = p_dup_vendor_id
715 AND pbh1.vendor_site_id = p_dup_vendor_site_id
716 AND NOT EXISTS (SELECT 'DUPLICATE'
717 FROM pon_bid_headers pbh2
718 WHERE pbh2.auction_header_id = pbh1.auction_header_id
719 AND pbh2.vendor_id = p_vendor_id
720 AND pbh2.vendor_site_id = p_vendor_site_id
721 AND pbh2.trading_partner_id = p_trading_partner_id
722 AND pbh2.trading_partner_contact_id = pbh1.trading_partner_contact_id);
723
724 l_progress := 502;
725 fnd_file.put_line (fnd_file.log, l_progress|| ' Done: pon_bid_headers');
726
727 -- Bug 5100555
728 -- This is done to update site code for reusable invitation list
729 -- putting separate query to take care of duplication of supplier
730 -- in list.
731
732 SELECT pvs.vendor_site_code
733 INTO l_dup_vendor_site_code
734 FROM po_vendors pv, po_vendor_sites_all pvs
735 WHERE pv.vendor_id = p_dup_vendor_id
736 AND pv.vendor_id = pvs.vendor_id
737 AND pvs.vendor_site_id = p_dup_vendor_site_id;
738
739 UPDATE pon_bidding_parties pbp1
740 SET pbp1.trading_partner_id = p_trading_partner_id,
741 pbp1.trading_partner_name = p_trading_partner_name,
742 pbp1.vendor_site_code = decode(pbp1.vendor_site_code,'-1','-1',p_vendor_site_code),
743 pbp1.vendor_site_id = decode(vendor_site_id , -1,-1, p_vendor_site_id),
744 pbp1.last_updated_by = -1,
745 pbp1.last_update_date = sysdate
746 WHERE pbp1.trading_partner_id = p_dup_trading_partner_id
747 AND ((pbp1.vendor_site_id = p_dup_vendor_site_id and pbp1.list_id = -1)
748 OR
749 (pbp1.vendor_site_code = l_dup_vendor_site_code and pbp1.list_id <> -1)
750 OR
751 ( pbp1.vendor_site_code = l_dup_vendor_site_code
752 and pbp1.auction_header_id is not null
753 and exists( select 1 from pon_auction_headers_all pah
754 where pah.auction_header_id = pbp1.auction_header_id
755 and pah.global_template_flag='Y' )
756 )
757 )
758 AND NOT EXISTS (SELECT 'DUPLICATE'
759 FROM pon_bidding_parties pbp2
760 WHERE pbp2.auction_header_id = pbp1.auction_header_id
761 AND pbp2.list_id = pbp1.list_id
762 AND decode(pbp2.vendor_site_code,'-1',p_vendor_site_code,pbp2.vendor_site_code) = p_vendor_site_code
763 AND pbp2.trading_partner_id = p_trading_partner_id
764 AND decode(pbp2.vendor_site_id,-1, p_vendor_site_id,pbp2.vendor_site_id) = p_vendor_site_id);
765
766 l_progress := 503;
767 fnd_file.put_line (fnd_file.log, l_progress|| ' Done: pon_bidding_parties');
768
769 UPDATE pon_party_line_exclusions pple1
770 SET pple1.trading_partner_id = p_trading_partner_id,
771 pple1.vendor_site_id = p_vendor_site_id,
772 pple1.last_updated_by = -1,
773 pple1.last_update_date = sysdate
774 WHERE pple1.trading_partner_id = p_dup_trading_partner_id
775 AND pple1.vendor_site_id = p_dup_vendor_site_id
776 AND NOT EXISTS (SELECT 'DUPLICATE'
777 FROM pon_party_line_exclusions pple2
778 WHERE pple2.auction_header_id = pple1.auction_header_id
779 AND pple2.trading_partner_id = p_trading_partner_id
780 AND pple2.vendor_site_id =p_vendor_site_id);
781
782 l_progress := 505;
783 fnd_file.put_line (fnd_file.log, l_progress|| ' Done: pon_party_line_exclusions');
784
785 UPDATE pon_pf_supplier_formula ppsf1
786 SET ppsf1.trading_partner_id = p_trading_partner_id,
787 ppsf1.vendor_site_id = p_vendor_site_id,
788 ppsf1.last_updated_by = -1,
789 ppsf1.last_update_date = sysdate
790 WHERE ppsf1.trading_partner_id = p_dup_trading_partner_id
791 AND ppsf1.vendor_site_id = p_dup_vendor_site_id
792 AND NOT EXISTS (SELECT 'DUPLICATE'
793 FROM pon_pf_supplier_formula ppsf2
794 WHERE ppsf2.auction_header_id = ppsf1.auction_header_id
795 AND ppsf2.trading_partner_id = p_trading_partner_id
796 AND ppsf2.vendor_site_id = p_vendor_site_id);
797 l_progress := 506;
798 fnd_file.put_line (fnd_file.log, l_progress|| ' Done: pon_pf_supplier_formula');
799
800 EXCEPTION WHEN NO_DATA_FOUND THEN
801 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
802
803 fnd_file.put_line (fnd_file.log, l_progress || 'In Exception - MERGE_DIFF_VENDOR_AND_SITE...'||' SQLERRM = ' || SQLERRM);
804 fnd_file.put_line (fnd_file.log, l_progress || 'In Exception - MERGE_DIFF_VENDOR_AND_SITE...'||' SQLCODE = '|| SQLCODE);
805
806 IF (G_FND_DEBUG = 'Y') THEN
807 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
808 FND_LOG.STRING(log_level => FND_LOG.LEVEL_EXCEPTION,
809 module => g_pkg_name || l_procedure_name,
810 message => 'Exception SQLERRM = ' || SQLERRM
811 ||' SQLCODE = '|| SQLCODE);
812 END IF;
813 END IF;
814
815 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
816 THEN
817 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, g_api_name);
818 END IF;
819
820 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
821 p_data => x_msg_data);
822
823
824 END; --}
825
826 PROCEDURE MERGE_DIFF_VENDOR_LAST_SITE(p_trading_partner_id IN NUMBER,
827 p_dup_trading_partner_id IN NUMBER,
828 p_vendor_id IN NUMBER,
829 p_vendor_site_id IN NUMBER,
830 p_vendor_site_code IN VARCHAR2,
831 p_dup_vendor_id IN NUMBER,
832 p_dup_vendor_site_id IN NUMBER,
833 p_trading_partner_name IN VARCHAR2,
834 x_return_status IN OUT NOCOPY VARCHAR2,
835 x_msg_count IN OUT NOCOPY NUMBER,
836 x_msg_data IN OUT NOCOPY VARCHAR2)
837 IS
838 l_procedure_name VARCHAR2(35) := 'MERGE_DIFF_VENDOR_LAST_SITE';
839 l_progress NUMBER;
840 l_dup_vendor_site_code po_vendor_sites_all.vendor_site_code%type;
841 BEGIN --{
842
843 /* CASE 2a - site being merged is the last site
844
845 Followling table will be updated while merging last site
846
847 1. pon_bid_item_prices
848 2. pon_bid_headers
849 3. pon_bidding_parties
850 4. pon_party_line_exclusions
851 5. pon_pf_supplier_formula
852 6. pon_supplier_access
853 7. pon_threads
854 8. pon_thread_entries
855 9. pon_te_recipients
856 10. pon_supplier_activities
857 11. pon_acknowledgements
858 */
859
860 l_progress := 701;
861 fnd_file.put_line (fnd_file.log, l_progress|| 'Start : MERGE_DIFF_VENDOR_LAST_SITE ');
862
863 UPDATE pon_bid_item_prices pbip1
864 SET pbip1.bid_trading_partner_id = p_trading_partner_id,
865 pbip1.last_updated_by = -1,
866 pbip1.last_update_date = sysdate
867 WHERE pbip1.bid_trading_partner_id = p_dup_trading_partner_id
868 AND pbip1.bid_number IN (SELECT bid_number
869 FROM pon_bid_headers pbh1
870 WHERE pbh1.trading_partner_id = p_dup_trading_partner_id
871 AND pbh1.vendor_id = p_dup_vendor_id
872 AND pbh1.vendor_site_id = decode(pbh1.vendor_site_id,-1,-1,p_dup_vendor_site_id)
873 AND NOT EXISTS (SELECT 'DUPLICATE'
874 FROM pon_bid_headers pbh2
875 WHERE pbh2.auction_header_id = pbh1.auction_header_id
876 AND pbh2.vendor_id = p_vendor_id
877 AND pbh2.vendor_site_id= decode(pbh2.vendor_site_id,-1,-1,p_vendor_site_id)
878 AND pbh2.trading_partner_id = p_trading_partner_id
879 AND pbh2.trading_partner_contact_id = pbh1.trading_partner_contact_id)
880 );
881
882
883 l_progress := 702;
884 fnd_file.put_line (fnd_file.log, l_progress|| 'Done pon_bid_item_prices ');
885
886 UPDATE pon_acknowledgements
887 SET trading_partner_id = p_trading_partner_id,
888 last_updated_by = -1,
889 last_update_date = sysdate
890 WHERE trading_partner_id = p_dup_trading_partner_id ;
891
892
893 l_progress := 703;
894 fnd_file.put_line (fnd_file.log, l_progress|| 'Done pon_acknowledgements');
895
896 UPDATE pon_bid_headers pbh1
897 SET pbh1.trading_partner_id = p_trading_partner_id,
898 pbh1.trading_partner_name = p_trading_partner_name,
899 pbh1.vendor_id = p_vendor_id,
900 pbh1.vendor_site_id = decode(pbh1.vendor_site_id,-1,-1,p_vendor_site_id),
901 pbh1.vendor_site_code = decode(pbh1.vendor_site_code,null,null,'-1','-1',p_vendor_site_code),
902 pbh1.last_updated_by = -1,
903 pbh1.last_update_date = sysdate
904 WHERE pbh1.trading_partner_id = p_dup_trading_partner_id
905 AND pbh1.vendor_id = p_dup_vendor_id
906 AND pbh1.vendor_site_id = decode(pbh1.vendor_site_id,-1,-1,p_dup_vendor_site_id)
907 AND NOT EXISTS (SELECT 'DUPLICATE'
908 FROM pon_bid_headers pbh2
909 WHERE pbh2.auction_header_id = pbh1.auction_header_id
910 AND pbh2.vendor_id = p_vendor_id
911 AND decode(pbh2.vendor_site_id,-1,p_vendor_site_id,pbh2.vendor_site_id) = p_vendor_site_id
912 AND decode(pbh2.vendor_site_code,'-1',p_vendor_site_code,null,p_vendor_site_code,pbh2.vendor_site_code) = p_vendor_site_code
913 AND pbh2.trading_partner_id = p_trading_partner_id
914 AND pbh2.trading_partner_contact_id = pbh1.trading_partner_contact_id);
915
916 l_progress := 704;
917 fnd_file.put_line (fnd_file.log, l_progress|| 'Done pon_bid_headers');
918
919 SELECT pvs.vendor_site_code
920 INTO l_dup_vendor_site_code
921 FROM po_vendors pv, po_vendor_sites_all pvs
922 WHERE pv.vendor_id = p_dup_vendor_id
923 AND pv.vendor_id = pvs.vendor_id
924 AND pvs.vendor_site_id = p_dup_vendor_site_id;
925
926 UPDATE pon_bidding_parties pbp1
927 SET pbp1.trading_partner_id = p_trading_partner_id,
928 pbp1.trading_partner_name = p_trading_partner_name,
929 pbp1.vendor_site_id = decode(pbp1.vendor_site_id,-1,-1,p_vendor_site_id),
930 pbp1.vendor_site_code = decode(pbp1.vendor_site_code,'-1','-1',p_vendor_site_code),
931 pbp1.last_updated_by = -1,
932 pbp1.last_update_date = sysdate
933 WHERE pbp1.trading_partner_id = p_dup_trading_partner_id
934 AND ((pbp1.vendor_site_id=decode(pbp1.vendor_site_id,-1,-1,p_dup_vendor_site_id) and pbp1.list_id = -1)
935 OR
936 (pbp1.vendor_site_code = decode(pbp1.vendor_site_code,'-1','-1',l_dup_vendor_site_code) and pbp1.list_id <> -1)
937 OR
938 ( pbp1.vendor_site_code = decode(pbp1.vendor_site_code,'-1','-1',l_dup_vendor_site_code)
939 and pbp1.auction_header_id is not null
940 and exists( select 1 from pon_auction_headers_all pah
941 where pah.auction_header_id = pbp1.auction_header_id
942 and pah.global_template_flag='Y' )
943 )
944 )
945 AND NOT EXISTS (SELECT 'DUPLICATE'
946 FROM pon_bidding_parties pbp2
947 WHERE pbp2.auction_header_id = pbp1.auction_header_id
948 AND pbp2.list_id = pbp1.list_id
949 AND pbp2.trading_partner_id = p_trading_partner_id
950 AND decode(pbp2.vendor_site_code,'-1',p_vendor_site_code,pbp2.vendor_site_code) = p_vendor_site_code
951 AND pbp2.vendor_site_id = decode(pbp1.vendor_site_id,-1,-1,p_vendor_site_id));
952
953 l_progress := 705;
954 fnd_file.put_line (fnd_file.log, l_progress|| 'Done pon_bidding_parties');
955
956 UPDATE pon_party_line_exclusions pple1
957 SET pple1.trading_partner_id = p_trading_partner_id,
958 pple1.vendor_site_id = decode(pple1.vendor_site_id,-1,-1,p_vendor_site_id)
959 WHERE pple1.trading_partner_id = p_dup_trading_partner_id
960 AND pple1.vendor_site_id = decode(pple1.vendor_site_id,-1,-1,p_dup_vendor_site_id)
961 AND NOT EXISTS (SELECT 'DUPLICATE'
962 FROM pon_party_line_exclusions pple2
963 WHERE pple2.auction_header_id = pple1.auction_header_id
964 AND pple2.trading_partner_id = p_trading_partner_id
965 AND pple2.vendor_site_id =decode(pple2.vendor_site_id,-1,-1,p_vendor_site_id));
966
967 l_progress := 707;
968 fnd_file.put_line (fnd_file.log, l_progress|| 'Done pon_party_line_exclusions');
969
970 UPDATE pon_pf_supplier_formula ppsf1
971 SET ppsf1.trading_partner_id = p_trading_partner_id,
972 ppsf1.vendor_site_id = decode(ppsf1.vendor_site_id,-1,-1,p_vendor_site_id),
973 ppsf1.last_updated_by = -1,
974 ppsf1.last_update_date = sysdate
975 WHERE ppsf1.trading_partner_id = p_dup_trading_partner_id
976 AND ppsf1.vendor_site_id = decode(ppsf1.vendor_site_id,-1,-1,p_dup_vendor_site_id)
977 AND NOT EXISTS (SELECT 'DUPLICATE'
978 FROM pon_pf_supplier_formula ppsf2
979 WHERE ppsf2.auction_header_id = ppsf1.auction_header_id
980 AND ppsf2.trading_partner_id = p_trading_partner_id
981 AND ppsf2.vendor_site_id = decode(ppsf1.vendor_site_id,-1,-1,p_vendor_site_id));
982
983 l_progress := 708;
984 fnd_file.put_line (fnd_file.log, l_progress|| 'Done pon_pf_supplier_formula');
985
986 /*
987 We are comparing w.r.t AUCTION_HEADER_ID_ORIG_AMEND becuase
988 the UK is definied on this field.
989 */
990
991 UPDATE pon_supplier_access psa1
992 SET psa1.supplier_trading_partner_id = p_trading_partner_id,
993 psa1.last_updated_by = -1,
994 psa1.last_update_date = sysdate
995 WHERE psa1.supplier_trading_partner_id = p_dup_trading_partner_id
996 AND NOT EXISTS (SELECT 'DUPLICATE'
997 FROM pon_supplier_access psa2
998 WHERE psa2.auction_header_id_orig_amend = psa1.auction_header_id_orig_amend
999 AND psa2.supplier_trading_partner_id = p_trading_partner_id);
1000
1001 l_progress := 709;
1002 fnd_file.put_line (fnd_file.log, l_progress|| 'Done pon_supplier_access');
1003
1004 /*
1005 We are comparing w.r.t AUCTION_HEADER_ID_ORIG_AMEND becuase
1006 the UK is definied on this field.
1007 */
1008
1009 UPDATE pon_supplier_activities psa1
1010 SET psa1.trading_partner_id = p_trading_partner_id,
1011 psa1.last_updated_by = -1,
1012 psa1.last_update_date = sysdate
1013 WHERE trading_partner_id =p_dup_trading_partner_id
1014 AND NOT EXISTS (SELECT 'DUPLICATE'
1015 FROM pon_supplier_activities psa2
1016 WHERE psa2.auction_header_id_orig_amend = psa1.auction_header_id_orig_amend
1017 AND psa2.trading_partner_contact_id = psa1.trading_partner_contact_id
1018 AND psa2.last_activity_time = psa1.last_activity_time
1019 AND psa2.trading_partner_id = p_trading_partner_id);
1020
1021 l_progress := 709;
1022 fnd_file.put_line (fnd_file.log, l_progress|| 'Done pon_supplier_activities');
1023
1024 /* Who columns are not in update as we don't have it on table */
1025
1026 UPDATE pon_threads pt
1027 SET pt.owner_party_id = p_trading_partner_id
1028 WHERE pt.owner_party_id = p_dup_trading_partner_id;
1029
1030 l_progress := 710;
1031 fnd_file.put_line (fnd_file.log, l_progress|| 'Done pon_threads');
1032
1033 /* Who columns are not in update as we don't have it on table */
1034
1035 UPDATE pon_thread_entries pte
1036 SET pte.from_company_id = p_trading_partner_id,
1037 pte.from_company_name = p_trading_partner_name,
1038 pte.vendor_id= p_vendor_id
1039 WHERE pte.vendor_id = p_dup_vendor_id
1040 AND pte.from_company_id = p_dup_trading_partner_id;
1041
1042 l_progress := 711;
1043 fnd_file.put_line (fnd_file.log, l_progress|| 'Done pon_thread_entries');
1044
1045 /* Who columns are not in update as we don't have it on table */
1046
1047 UPDATE pon_te_recipients ptr
1048 SET ptr.to_company_id = p_trading_partner_id,
1049 ptr.to_company_name = p_trading_partner_name
1050 WHERE ptr.to_company_id = p_dup_trading_partner_id;
1051
1052 l_progress := 712;
1053 fnd_file.put_line (fnd_file.log, l_progress|| 'Done pon_te_recipients');
1054
1055
1056 EXCEPTION WHEN NO_DATA_FOUND THEN
1057 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1058
1059 fnd_file.put_line (fnd_file.log, l_progress|| 'In exception - MERGE_DIFF_VENDOR_LAST_SITE SQLERRM '||SQLERRM);
1060 fnd_file.put_line (fnd_file.log, l_progress|| 'In exception - MERGE_DIFF_VENDOR_LAST_SITE SQLCODE'||SQLCODE);
1061
1062 IF (G_FND_DEBUG = 'Y') THEN
1063 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1064 FND_LOG.STRING(log_level => FND_LOG.LEVEL_EXCEPTION,
1065 module => g_pkg_name || l_procedure_name,
1066 message => 'Exception SQLERRM = ' || SQLERRM
1067 ||' SQLCODE = '|| SQLCODE);
1068 END IF;
1069 END IF;
1070
1071 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1072 THEN
1073 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, g_api_name);
1074 END IF;
1075
1076 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1077 p_data => x_msg_data);
1078
1079 END; --}
1080
1081 -- After the merge there can be cases where this table
1082 -- does not have records for the bids that got updated
1083 -- due to the merge and hence we might need to insert
1084 -- extra rows here.
1085 -- Same logic as in the upgrade
1086
1087 -- Refer to the Bugs 3940301/4145154 for more information.
1088
1089 PROCEDURE UPDATE_SUPPLIER_ACTIVITY(p_dup_trading_partner_id IN NUMBER,
1090 x_return_status IN OUT NOCOPY VARCHAR2,
1091 x_msg_count IN OUT NOCOPY NUMBER,
1092 x_msg_data IN OUT NOCOPY VARCHAR2)
1093 IS
1094 l_procedure_name VARCHAR2(30) := 'UPDATE_SUPPLIER_ACTIVITY';
1095 BEGIN --{
1096
1097 INSERT INTO PON_SUPPLIER_ACTIVITIES
1098 (
1099 auction_header_id_orig_amend,
1100 trading_partner_contact_id,
1101 last_activity_time,
1102 auction_header_id,
1103 trading_partner_id,
1104 session_id,
1105 last_activity_code,
1106 last_action_flag,
1107 creation_date,
1108 created_by,
1109 last_update_date,
1110 last_updated_by,
1111 last_update_login
1112 )
1113 SELECT
1114 ah.auction_header_id_orig_amend,
1115 bh.trading_partner_contact_id,
1116 bh.creation_date,
1117 bh.auction_header_id,
1118 bh.trading_partner_id,
1119 -1, -- session id
1120 DECODE(bh.bid_status, 'DRAFT', 'CRT_RESP', 'SUBMIT_BID'), -- activity code
1121 'Y', -- last action flag
1122 SYSDATE,
1123 bh.created_by,
1124 SYSDATE,
1125 bh.last_updated_by,
1126 0
1127 FROM pon_auction_headers_all ah,
1128 pon_bid_headers bh
1129 WHERE bh.trading_partner_id = p_dup_trading_partner_id
1130 AND ah.auction_header_id = bh.auction_header_id
1131 AND NOT EXISTS (
1132 SELECT NULL
1133 FROM pon_supplier_activities psa
1134 WHERE psa.auction_header_id_orig_amend = ah.auction_header_id_orig_amend
1135 AND psa.trading_partner_id = bh.trading_partner_id
1136 AND psa.trading_partner_contact_id = bh.trading_partner_contact_id
1137 )
1138 AND NOT EXISTS
1139 (
1140 SELECT NULL
1141 FROM pon_bid_headers bh2,
1142 pon_auction_headers_all ah2
1143 WHERE ah2.auction_header_id = bh2.auction_header_id
1144 AND ah.auction_header_id_orig_amend = ah2.auction_header_id_orig_amend
1145 AND bh2.bid_number > bh.bid_number
1146 AND bh2.trading_partner_contact_id = bh.trading_partner_contact_id
1147 AND bh2.trading_partner_id = bh.trading_partner_id
1148 );
1149 --
1150 -- end supplier activities update.
1151 --
1152
1153 EXCEPTION WHEN NO_DATA_FOUND THEN
1154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1155
1156 fnd_file.put_line (fnd_file.log, 'In exception - UPDATE_SUPPLIER_ACTIVITY SQLERRM '||SQLERRM);
1157 fnd_file.put_line (fnd_file.log, 'In exception - UPDATE_SUPPLIER_ACTIVITY SQLCODE'||SQLCODE);
1158
1159 IF (G_FND_DEBUG = 'Y') THEN
1160 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1161 FND_LOG.STRING(log_level => FND_LOG.LEVEL_EXCEPTION,
1162 module => g_pkg_name || l_procedure_name,
1163 message => 'Exception SQLERRM = ' || SQLERRM
1164 ||' SQLCODE = '|| SQLCODE);
1165 END IF;
1166 END IF;
1167
1168 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1169 THEN
1170 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, g_api_name);
1171 END IF;
1172
1173 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1174 p_data => x_msg_data);
1175
1176 END; --}
1177
1178 END PON_VENDORMERGE_GRP; --}
[Home] [Help]