[Home] [Help]
PACKAGE BODY: APPS.INL_TCAMERGE_GRP
Source
1 PACKAGE BODY INL_TCAMERGE_GRP AS
2 /* $Header: INLGMRGB.pls 120.1 2011/01/18 16:40:18 acferrei noship $ */
3 --
4 --========================================================================
5 -- PROCEDURE :Merge_Vendors
6 -- PARAMETERS:
7 -- p_from_id Merge from vendor ID
8 -- p_to_id Merge to vendor ID
9 -- p_from_party_id Merge from party ID
10 -- p_to_party_id Merge to party ID
11 -- p_from_site_id Merge from vendor site ID
12 -- p_to_site_id Merge to vendor site ID
13 -- p_from_party_site_id Merge from party site ID
14 -- p_to_party_site_id Merge to party site ID
15 -- p_calling_mode Either 'INVOICE' or 'PO'
16 -- x_return_status Return status
17 --
18 -- COMMENT :
19 -- This is the core INL Vendor merge routine that is called from
20 -- Merge_VendorParties() API.
21 --
22 -- Parameter p_calling_mode indicates what updates to perform.
23 -- 'INVOICE' ==> Update only non-PO entities
24 -- 'PO' ==> Update PO related entities
25 --========================================================================
26 PROCEDURE Merge_Vendors (
27 p_from_id IN NUMBER,
28 p_to_id IN NUMBER,
29 p_from_party_id IN NUMBER,
30 p_to_party_id IN NUMBER,
31 p_from_site_id IN NUMBER,
32 p_to_site_id IN NUMBER,
33 p_from_party_site_id IN NUMBER,
34 p_to_party_site_id IN NUMBER,
35 p_calling_mode IN VARCHAR2,
36 x_return_status OUT NOCOPY VARCHAR2 ) IS
37 --
38 CURSOR c_simulations IS
39 SELECT simulation_id
40 FROM inl_simulations
41 WHERE vendor_id = p_from_id
42 AND vendor_site_id = p_from_site_id;
43
44 CURSOR c_line_groups IS
45 SELECT ship_line_group_id
46 FROM inl_ship_line_groups
47 WHERE party_id = p_from_party_id
48 AND party_site_id = p_from_party_site_id
49 AND src_type_code = 'PO';
50
51 CURSOR c_lines IS
52 SELECT ship_line_id
53 FROM inl_ship_lines_all
54 WHERE ((poa_party_id = p_from_id AND poa_party_site_id = p_from_site_id)
55 OR (ship_from_party_id = p_from_id AND ship_from_party_site_id = p_from_site_id)
56 OR (bill_from_party_id = p_from_id AND bill_from_party_site_id = p_from_site_id))
57 AND ship_line_src_type_code = 'PO';
58
59 CURSOR c_charge_lines IS
60 SELECT charge_line_id
61 FROM inl_charge_lines cl
62 WHERE (cl.party_id = p_from_party_id or cl.party_site_id = p_from_party_site_id)
63 AND ((cl.ship_from_party_id = p_from_id AND cl.ship_from_party_site_id = p_from_site_id)
64 OR (cl.bill_from_party_id = p_from_id AND cl.bill_from_party_site_id = p_from_site_id)
65 OR (cl.poa_party_id = p_from_id AND cl.poa_party_site_id = p_from_site_id));
66
67 CURSOR c_matches IS
68 SELECT match_id
69 FROM INL_MATCHES
70 WHERE party_id = p_from_party_id
71 AND party_site_id = p_from_party_site_id;
72 --
73 TYPE id_tbl_type is TABLE of NUMBER INDEX BY BINARY_INTEGER;
74 l_simulation_list id_tbl_type;
75 l_line_group_list id_tbl_type;
76 l_line_list id_tbl_type;
77 l_charge_line_list id_tbl_type;
78 l_match_list id_tbl_type;
79 l_proc_name CONSTANT VARCHAR2 (30) := 'Merge_Vendors';
80 l_debug_info VARCHAR2(200);
81 j NUMBER := 0;
82 --
83 BEGIN
84 --{
85 -- Standard Beginning of Procedure/Function Logging
86 INL_LOGGING_PVT.Log_BeginProc ( p_module_name => g_module_name,
87 p_procedure_name => l_proc_name) ;
88
89 -- Standard Start of API savepoint
90 SAVEPOINT Merge_Vendors_GRP;
91 --
92
93 -- Initialize API return status to success
94 x_return_status := FND_API.G_RET_STS_SUCCESS;
95 --
96
97 -- Logging variables
98 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
99 p_procedure_name => l_proc_name,
100 p_var_name => 'p_from_id',
101 p_var_value => p_from_id);
102 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
103 p_procedure_name => l_proc_name,
104 p_var_name => 'p_to_id',
105 p_var_value => p_to_id) ;
106 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
107 p_procedure_name => l_proc_name,
108 p_var_name => 'p_from_party_id',
109 p_var_value => p_from_party_id) ;
110 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
111 p_procedure_name => l_proc_name,
112 p_var_name => 'p_to_party_id',
113 p_var_value => p_to_party_id) ;
114 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
115 p_procedure_name => l_proc_name,
116 p_var_name => 'p_from_site_id',
117 p_var_value => p_from_site_id) ;
118 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
119 p_procedure_name => l_proc_name,
120 p_var_name => 'p_to_site_id',
121 p_var_value => p_to_site_id) ;
122 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
123 p_procedure_name => l_proc_name,
124 p_var_name => 'p_from_party_site_id',
125 p_var_value => p_from_party_site_id) ;
126 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
127 p_procedure_name => l_proc_name,
128 p_var_name => 'p_to_party_site_id',
129 p_var_value => p_to_party_site_id) ;
130 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
131 p_procedure_name => l_proc_name,
132 p_var_name => 'p_calling_mode',
133 p_var_value => p_calling_mode) ;
134
135 --
136 -- Update PO related entities only when the mode is PO
137 --
138 IF (p_calling_mode = 'PO') THEN
139 --{
140 --{
141 OPEN c_simulations;
142 FETCH c_simulations BULK COLLECT INTO l_simulation_list;
143 CLOSE c_simulations;
144
145 l_debug_info := 'Count of Rows fetched from Cursor C_SIMULATIONS = '||l_simulation_list.count;
146 INL_LOGGING_PVT.Log_Statement ( p_module_name => g_module_name,
147 p_procedure_name => l_proc_name,
148 p_debug_info => l_debug_info) ;
149
150 IF l_simulation_list.COUNT > 0 THEN
151 --
152 FORALL j IN l_simulation_list.FIRST..l_simulation_list.LAST
153 UPDATE inl_simulations
154 SET vendor_id = p_to_id,
155 vendor_site_id = p_to_site_id,
156 request_id = fnd_global.conc_request_id,
157 program_id = fnd_global.conc_program_id,
158 program_application_id = fnd_global.prog_appl_id,
159 last_update_date = sysdate,
160 last_updated_by = fnd_global.user_id,
161 last_update_login = fnd_global.login_id
162 WHERE simulation_id = l_simulation_list(j);
163
164 l_debug_info := 'Updated inl_simulations. Number of Rows updated is ' || sql%rowcount;
165 INL_LOGGING_PVT.Log_Statement ( p_module_name => g_module_name,
166 p_procedure_name => l_proc_name,
167 p_debug_info => l_debug_info) ;
168 END IF;
169 --}
170
171 --{
172 OPEN c_line_groups;
173 FETCH c_line_groups BULK COLLECT INTO l_line_group_list;
174 CLOSE c_line_groups;
175
176 l_debug_info := 'Count of Rows fetched from Cursor C_LINE_GROUPS = '||l_line_group_list.count;
177 INL_LOGGING_PVT.Log_Statement ( p_module_name => g_module_name,
178 p_procedure_name => l_proc_name,
179 p_debug_info => l_debug_info) ;
180
181 IF l_line_group_list.COUNT > 0 THEN
182 --
183 FORALL j IN l_line_group_list.FIRST..l_line_group_list.LAST
184 UPDATE inl_ship_line_groups
185 SET party_id = p_to_party_id,
186 party_site_id = p_to_party_site_id,
187 request_id = fnd_global.conc_request_id,
188 program_id = fnd_global.conc_program_id,
189 program_application_id = fnd_global.prog_appl_id,
190 last_update_date = sysdate,
191 last_updated_by = fnd_global.user_id,
192 last_update_login = fnd_global.login_id
193 WHERE ship_line_group_id = l_line_group_list(j);
194
195 l_debug_info := 'Updated inl_ship_line_groups. Number of Rows updated is ' || sql%rowcount;
196 INL_LOGGING_PVT.Log_Statement ( p_module_name => g_module_name,
197 p_procedure_name => l_proc_name,
198 p_debug_info => l_debug_info) ;
199 END IF;
200 --}
201
202 --{
203 OPEN c_lines;
204 FETCH c_lines BULK COLLECT INTO l_line_list;
205 CLOSE c_lines;
206
207 l_debug_info := 'Count of Rows fetched from Cursor C_LINES = ' || l_line_list.count;
208 INL_LOGGING_PVT.Log_Statement ( p_module_name => g_module_name,
209 p_procedure_name => l_proc_name,
210 p_debug_info => l_debug_info) ;
211
212 IF l_line_list.COUNT > 0 THEN
213 --
214 FORALL j IN l_line_list.FIRST..l_line_list.LAST
215 UPDATE inl_ship_lines_all
216 SET poa_party_id = decode(poa_party_id,p_from_id,p_to_id,poa_party_id),
217 poa_party_site_id = decode(poa_party_site_id,p_from_site_id,p_to_site_id,poa_party_site_id),
218 ship_from_party_id = decode(ship_from_party_id,p_from_id,p_to_id,ship_from_party_id),
219 ship_from_party_site_id = decode(ship_from_party_site_id,p_from_site_id,p_to_site_id,ship_from_party_site_id),
220 bill_from_party_id = decode(bill_from_party_id,p_from_id,p_to_id,bill_from_party_id),
221 bill_from_party_site_id = decode(bill_from_party_site_id,p_from_site_id,p_to_site_id,bill_from_party_site_id),
222 request_id = fnd_global.conc_request_id,
223 program_id = fnd_global.conc_program_id,
224 program_application_id = fnd_global.prog_appl_id,
225 last_update_date = sysdate,
226 last_updated_by = fnd_global.user_id,
227 last_update_login = fnd_global.login_id
228 WHERE ship_line_id = l_line_list(j);
229
230 l_debug_info := 'Updated inl_ship_lines_all. Number of Rows updated is ' || sql%rowcount;
231 INL_LOGGING_PVT.Log_Statement ( p_module_name => g_module_name,
232 p_procedure_name => l_proc_name,
233 p_debug_info => l_debug_info) ;
234 END IF;
235 --}
236
237 --{
238 OPEN c_charge_lines;
239 FETCH c_charge_lines BULK COLLECT INTO l_charge_line_list;
240 CLOSE c_charge_lines;
241
242 l_debug_info := 'Count of Rows fetched from Cursor C_CHARGE_LINES = ' || l_charge_line_list.count;
243 INL_LOGGING_PVT.Log_Statement ( p_module_name => g_module_name,
244 p_procedure_name => l_proc_name,
245 p_debug_info => l_debug_info) ;
246
247 IF l_charge_line_list.COUNT > 0 THEN
248 --
249 FORALL j IN l_charge_line_list.FIRST..l_charge_line_list.LAST
250 UPDATE inl_charge_lines
251 SET ship_from_party_id = decode(ship_from_party_id,p_from_id,p_to_id,ship_from_party_id),
252 ship_from_party_site_id = decode(nvl(ship_from_party_site_id,-1),-1,null,p_from_site_id,p_to_site_id,ship_from_party_site_id),
253 bill_from_party_id = decode(bill_from_party_id,p_from_id,p_to_id,bill_from_party_id),
254 bill_from_party_site_id = decode(nvl(bill_from_party_site_id,-1),-1,null,p_from_site_id,p_to_site_id,bill_from_party_site_id),
255 poa_party_id = decode(poa_party_id,p_from_id,p_to_id,poa_party_id),
256 poa_party_site_id = decode(nvl(poa_party_site_id,-1),-1,null,p_from_site_id,p_to_site_id,poa_party_site_id),
257 party_id = p_to_party_id,
258 party_site_id = decode(nvl(party_site_id,-1),-1,null,p_from_party_site_id,p_to_party_site_id,party_site_id),
259 last_update_date = sysdate,
260 last_updated_by = fnd_global.user_id,
261 last_update_login = fnd_global.login_id
262 WHERE charge_line_id = l_charge_line_list(j);
263
264 l_debug_info := 'Updated inl_charges. Number of Rows updated is ' || sql%rowcount;
265 INL_LOGGING_PVT.Log_Statement ( p_module_name => g_module_name,
266 p_procedure_name => l_proc_name,
267 p_debug_info => l_debug_info) ;
268 END IF;
269 --}
270
271 -- {
272 OPEN c_matches;
273 FETCH c_matches BULK COLLECT INTO l_match_list;
274 CLOSE c_matches;
275
276 l_debug_info := 'Count of Rows fetched from Cursor C_MATCHES = '||l_match_list.count;
277 INL_LOGGING_PVT.Log_Statement ( p_module_name => g_module_name,
278 p_procedure_name => l_proc_name,
279 p_debug_info => l_debug_info) ;
280
281 IF l_match_list.COUNT > 0 THEN
282 --
283 FORALL j IN l_match_list.FIRST..l_match_list.LAST
284 UPDATE inl_matches
285 SET party_id = p_to_party_id,
286 party_site_id = p_to_party_site_id,
287 request_id = fnd_global.conc_request_id,
288 program_id = fnd_global.conc_program_id,
289 program_application_id = fnd_global.prog_appl_id,
290 last_update_date = sysdate,
291 last_updated_by = fnd_global.user_id,
292 last_update_login = fnd_global.login_id
293 WHERE match_id = l_match_list(j);
294
295 l_debug_info := 'Updated inl_matches. Number of Rows updated is ' || sql%rowcount;
296 INL_LOGGING_PVT.Log_Statement ( p_module_name => g_module_name,
297 p_procedure_name => l_proc_name,
298 p_debug_info => l_debug_info) ;
299 END IF;
300 --}
301 --}
302 END IF;
303 --
304 l_debug_info := 'x_return_status '||x_return_status;
305 INL_LOGGING_PVT.Log_Statement ( p_module_name => g_module_name,
306 p_procedure_name => l_proc_name,
307 p_debug_info => l_debug_info) ;
308
309 -- Standard End of Procedure/Function Logging
310 INL_LOGGING_PVT.Log_EndProc ( p_module_name => g_module_name,
311 p_procedure_name => l_proc_name) ;
312
313 EXCEPTION
314 WHEN FND_API.G_EXC_ERROR THEN
315 -- Standard Expected Error Logging
316 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
317 p_procedure_name => l_proc_name);
318 ROLLBACK TO Merge_Vendors_GRP;
319 x_return_status := FND_API.G_RET_STS_ERROR;
320
321 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
322 -- Standard Unexpected Error Logging
323 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
324 p_procedure_name => l_proc_name);
325 ROLLBACK TO Merge_Vendors_GRP;
326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
327
328 WHEN OTHERS THEN
329 -- Standard Unexpected Error Logging
330 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
331 p_procedure_name => l_proc_name);
332 ROLLBACK TO Merge_Vendors_GRP;
333 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
334 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
335 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,l_proc_name);
336 END IF;
337 --}
338 END Merge_Vendors;
339
340 --========================================================================
341 -- PROCEDURE :Merge_VendorParties
342 -- PARAMETERS:
343 -- p_from_vendor_id Merge from vendor ID
344 -- p_to_vendor_id Merge to vendor ID
345 -- p_from_party_id Merge from party ID
346 -- p_to_party_id Merge to party ID
347 -- p_from_vendor_site_id Merge from vendor site ID
348 -- p_to_vendor_site_id Merge to vendor site ID
349 -- p_from_party_site_id Merge from party site ID
350 -- p_to_party_site_id Merge to party site ID
351 -- p_calling_mode Mode in which AP calls us
352 -- 'INVOICE' or 'PO'
353 -- x_return_status Return status
354 -- x_msg_count Return count message
355 -- x_msg_data Return message data
356 --
357 -- COMMENTS
358 -- This is the API that is called by APXINUPD.rdf. This in turn
359 -- will call the core Merge_Vendors() procedure to
360 -- perform all the necessary updates to LCM data.
361 --
362 --========================================================================
363
364 PROCEDURE Merge_VendorParties
365 ( p_from_vendor_id IN NUMBER,
366 p_to_vendor_id IN NUMBER,
367 p_from_party_id IN NUMBER,
368 p_to_party_id IN NUMBER,
369 p_from_vendor_site_id IN NUMBER,
370 p_to_vendor_site_id IN NUMBER,
371 p_from_party_site_id IN NUMBER,
372 p_to_party_site_id IN NUMBER,
373 p_calling_mode IN VARCHAR2,
374 x_return_status OUT NOCOPY VARCHAR2,
375 x_msg_count OUT NOCOPY NUMBER,
376 x_msg_data OUT NOCOPY VARCHAR2
377 )
378 IS
379 --
380 CURSOR c_getParty(p_vendorId IN NUMBER) IS
381 SELECT party_id
382 FROM po_vendors
383 WHERE vendor_id = p_vendorId;
384
385 CURSOR c_getPartySite(p_vendorSiteId IN NUMBER) IS
386 SELECT party_site_id
387 FROM po_vendor_sites_all
388 WHERE vendor_site_id = p_vendorSiteId;
389 --
390 l_proc_name CONSTANT VARCHAR2 (30) := 'Merge_VendorParties';
391 l_debug_info VARCHAR2(200);
392 l_return_status VARCHAR2(1);
393 l_fromPartyId NUMBER;
394 l_toPartyId NUMBER;
395 l_fromPartySiteId NUMBER;
396 l_toPartySiteId NUMBER;
397 --
398 BEGIN
399 --{
400 -- Standard Beginning of Procedure/Function Logging
401 INL_LOGGING_PVT.Log_BeginProc ( p_module_name => g_module_name,
402 p_procedure_name => l_proc_name);
403
404 -- Standard Start of API savepoint
405 SAVEPOINT Merge_VendorParties_GRP;
406
407 -- Initialize API return status to success
408 x_return_status := FND_API.G_RET_STS_SUCCESS;
409 --
410
411 -- Logging variables
412 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
413 p_procedure_name => l_proc_name,
414 p_var_name => 'p_from_vendor_id',
415 p_var_value => p_from_vendor_id);
416 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
417 p_procedure_name => l_proc_name,
418 p_var_name => 'p_to_vendor_id',
419 p_var_value => p_to_vendor_id) ;
420 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
421 p_procedure_name => l_proc_name,
422 p_var_name => 'p_from_party_id',
423 p_var_value => p_from_party_id) ;
424 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
425 p_procedure_name => l_proc_name,
426 p_var_name => 'p_to_party_id',
427 p_var_value => p_to_party_id) ;
428 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
429 p_procedure_name => l_proc_name,
430 p_var_name => 'p_from_vendor_site_id',
431 p_var_value => p_from_vendor_site_id) ;
432 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
433 p_procedure_name => l_proc_name,
434 p_var_name => 'p_to_vendor_site_id',
435 p_var_value => p_to_vendor_site_id) ;
436 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
437 p_procedure_name => l_proc_name,
438 p_var_name => 'p_from_party_site_id',
439 p_var_value => p_from_party_site_id) ;
440 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
441 p_procedure_name => l_proc_name,
442 p_var_name => 'p_to_party_site_id',
443 p_var_value => p_to_party_site_id) ;
444 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
445 p_procedure_name => l_proc_name,
446 p_var_name => 'p_calling_mode',
447 p_var_value => p_calling_mode) ;
448
449
450 --
451 IF p_from_party_id IS NULL THEN
452 --
453 OPEN c_getParty(p_from_vendor_id);
454 FETCH c_getParty INTO l_fromPartyId;
455 IF (c_getParty%NOTFOUND) THEN
456 Null;
457 END IF;
458 CLOSE c_getParty;
459 --
460 END IF;
461
462 IF p_from_party_site_id IS NULL THEN
463 --
464 OPEN c_getPartySite(p_from_vendor_site_id);
465 FETCH c_getPartySite INTO l_fromPartySiteId;
466 IF (c_getPartySite%NOTFOUND) THEN
467 Null;
468 END IF;
469 CLOSE c_getPartySite;
470 --
471 END IF;
472
473 IF p_to_party_id IS NULL THEN
474 --
475 OPEN c_getParty(p_to_vendor_id);
476 FETCH c_getParty INTO l_toPartyId;
477 IF (c_getParty%NOTFOUND) THEN
478 Null;
479 END IF;
480 CLOSE c_getParty;
481 --
482 END IF;
483
484 IF p_to_party_site_id IS NULL THEN
485 --
486 OPEN c_getPartySite(p_to_vendor_site_id);
487 FETCH c_getPartySite INTO l_toPartySiteId;
488 IF (c_getPartySite%NOTFOUND) THEN
489 Null;
490 END IF;
491 CLOSE c_getPartySite;
492 --
493 END IF;
494 --
495 l_debug_info := 'l_fromPartyId '||l_fromPartyId||' l_toPartyId '||l_toPartyId||' l_fromPartySiteId '||l_fromPartySiteId||' l_toPartySiteId '||l_toPartySiteId;
496 INL_LOGGING_PVT.Log_Statement ( p_module_name => g_module_name,
497 p_procedure_name => l_proc_name,
498 p_debug_info => l_debug_info) ;
499
500 --
501 -- Now call the core Vendor Merge routine to update INL data
502 --
503 INL_TCAMERGE_GRP.Merge_Vendors
504 (
505 p_from_id => p_from_vendor_id,
506 p_to_id => P_to_vendor_id,
507 p_from_party_id => NVL(p_from_party_id, l_fromPartyId),
508 p_to_party_id => NVL(p_to_party_id, l_toPartyId),
509 p_from_site_id => p_from_vendor_site_id,
510 p_to_site_id => p_to_vendor_site_id,
511 p_from_party_site_id => NVL(p_from_party_site_id, l_fromPartySiteId),
512 p_to_party_site_id => NVL(p_to_party_site_id, l_toPartySiteId),
513 p_calling_mode => p_calling_mode,
514 x_return_status => l_return_status
515 );
516 --
517 l_debug_info := 'After calling core Merge_Vendors API l_return_status '||l_return_status;
518 INL_LOGGING_PVT.Log_Statement ( p_module_name => g_module_name,
519 p_procedure_name => l_proc_name,
520 p_debug_info => l_debug_info);
521
522 -- If any errors happen abort API.
523 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
524 RAISE FND_API.G_EXC_ERROR;
525 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
526 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
527 END IF;
528 --
529 l_debug_info := 'x_return_status '||x_return_status;
530 INL_LOGGING_PVT.Log_Statement ( p_module_name => g_module_name,
531 p_procedure_name => l_proc_name,
532 p_debug_info => l_debug_info) ;
533
534 -- Standard call to get message count and if count is 1, get message info.
535 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
536 p_count => x_msg_count,
537 p_data => x_msg_data);
538
539 -- Standard End of Procedure/Function Logging
540 INL_LOGGING_PVT.Log_EndProc ( p_module_name => g_module_name,
541 p_procedure_name => l_proc_name) ;
542
543 --}
544 EXCEPTION
545 WHEN FND_API.G_EXC_ERROR THEN
546 -- Standard Expected Error Logging
547 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
548 p_procedure_name => l_proc_name);
549 ROLLBACK TO Merge_VendorParties_GRP;
550 x_return_status := FND_API.G_RET_STS_ERROR;
551 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
552 -- Standard Unexpected Error Logging
553 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
554 p_procedure_name => l_proc_name);
555 ROLLBACK TO Merge_VendorParties_GRP;
556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
557 WHEN OTHERS THEN
558 -- Standard Unexpected Error Logging
559 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
560 p_procedure_name => l_proc_name);
561 ROLLBACK TO Merge_VendorParties_GRP;
562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
563 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
564 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,l_proc_name);
565 END IF;
566 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
567 p_count => x_msg_count,
568 p_data => x_msg_data);
569 END Merge_VendorParties;
570
571 --========================================================================
572 -- PROCEDURE :Merge_Parties
573 -- PARAMETERS:
574 -- p_entity_name Name of Entity Being Merged
575 -- p_from_id Primary Key Id of the entity that is being merged
576 -- p_to_id The record under the 'To Parent' that is being merged
577 -- p_from_fk_id Foreign Key id of the Old Parent Record
578 -- p_to_fk_id Foreign Key id of the New Parent Record
579 -- p_parent_entity_name Name of Parent Entity
580 -- p_batch_id Id of the Batch
581 -- p_batch_party_id Id uniquely identifies the batch and party record that is being merged
582 -- x_return_status Returns the status of call
583 --
584 -- COMMENT :
585 --
586 --========================================================================
587
588 PROCEDURE Merge_Parties
589 ( p_entity_name IN VARCHAR2,
590 p_from_id IN NUMBER,
591 p_to_id IN OUT NOCOPY NUMBER,
592 p_from_fk_id IN NUMBER,
593 p_to_fk_id IN NUMBER,
594 p_parent_entity_name IN VARCHAR2,
595 p_batch_id IN NUMBER,
596 p_batch_party_id IN NUMBER,
597 x_return_status IN OUT NOCOPY VARCHAR2
598 ) IS
599
600 CURSOR c_charge_lines IS
601 SELECT charge_line_id
602 FROM inl_charge_lines cl
603 WHERE cl.party_id = p_from_fk_id
604 AND cl.ship_from_party_id is null
605 AND cl.bill_from_party_id is null
606 AND cl.poa_party_id is null;
607
608 TYPE id_tbl_type is TABLE of NUMBER INDEX BY BINARY_INTEGER;
609 l_charge_line_list id_tbl_type;
610 l_proc_name CONSTANT VARCHAR2 (30) := 'Merge_Parties';
611 l_debug_info VARCHAR2(200);
612 l_return_status VARCHAR2(1);
613
614 BEGIN
615 --{
616 -- Standard Beginning of Procedure/Function Logging
617 INL_LOGGING_PVT.Log_BeginProc ( p_module_name => g_module_name,
618 p_procedure_name => l_proc_name);
619
620 -- Standard Start of API savepoint
621 SAVEPOINT Merge_Parties_GRP;
622
623 -- Initialize API return status to success
624 x_return_status := FND_API.G_RET_STS_SUCCESS;
625 --
626
627 -- Logging variables
628 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
629 p_procedure_name => l_proc_name,
630 p_var_name => 'p_entity_name',
631 p_var_value => p_entity_name);
632 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
633 p_procedure_name => l_proc_name,
634 p_var_name => 'p_from_id',
635 p_var_value => p_from_id);
636 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
637 p_procedure_name => l_proc_name,
638 p_var_name => 'p_to_id',
639 p_var_value => p_to_id);
640 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
641 p_procedure_name => l_proc_name,
642 p_var_name => 'p_from_fk_id',
643 p_var_value => p_from_fk_id);
644 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
645 p_procedure_name => l_proc_name,
646 p_var_name => 'p_to_fk_id',
647 p_var_value => p_to_fk_id);
648 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
649 p_procedure_name => l_proc_name,
650 p_var_name => 'p_parent_entity_name',
651 p_var_value => p_parent_entity_name);
652 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
653 p_procedure_name => l_proc_name,
654 p_var_name => 'p_batch_id',
655 p_var_value => p_batch_id);
656 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
657 p_procedure_name => l_proc_name,
658 p_var_name => 'p_batch_party_id',
659 p_var_value => p_batch_party_id);
660
661 --{
662 OPEN c_charge_lines;
663 FETCH c_charge_lines BULK COLLECT INTO l_charge_line_list;
664 CLOSE c_charge_lines;
665
666 l_debug_info := 'Count of Rows fetched from Cursor C_CHARGE_LINES = ' || l_charge_line_list.count;
667 INL_LOGGING_PVT.Log_Statement ( p_module_name => g_module_name,
668 p_procedure_name => l_proc_name,
669 p_debug_info => l_debug_info) ;
670
671 IF l_charge_line_list.COUNT > 0 THEN
672 --
673 FORALL j IN l_charge_line_list.FIRST..l_charge_line_list.LAST
674 UPDATE inl_charge_lines
675 SET party_id = p_to_fk_id,
676 last_update_date = sysdate,
677 last_updated_by = fnd_global.user_id,
678 last_update_login = fnd_global.login_id
679 WHERE charge_line_id = l_charge_line_list(j);
680
681 l_debug_info := 'Updated inl_charges. Number of Rows updated is ' || sql%rowcount;
682 INL_LOGGING_PVT.Log_Statement ( p_module_name => g_module_name,
683 p_procedure_name => l_proc_name,
684 p_debug_info => l_debug_info) ;
685 END IF;
686 --}
687
688 l_debug_info := 'x_return_status '||x_return_status;
689 INL_LOGGING_PVT.Log_Statement ( p_module_name => g_module_name,
690 p_procedure_name => l_proc_name,
691 p_debug_info => l_debug_info) ;
692
693 -- Standard End of Procedure/Function Logging
694 INL_LOGGING_PVT.Log_EndProc ( p_module_name => g_module_name,
695 p_procedure_name => l_proc_name) ;
696
697 --}
698 EXCEPTION
699 WHEN FND_API.G_EXC_ERROR THEN
700 -- Standard Expected Error Logging
701 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
702 p_procedure_name => l_proc_name);
703 ROLLBACK TO Merge_Parties_GRP;
704 x_return_status := FND_API.G_RET_STS_ERROR;
705 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
706 -- Standard Unexpected Error Logging
707 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
708 p_procedure_name => l_proc_name);
709 ROLLBACK TO Merge_Parties_GRP;
710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
711 WHEN OTHERS THEN
712 -- Standard Unexpected Error Logging
713 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
714 p_procedure_name => l_proc_name);
715 ROLLBACK TO Merge_Parties_GRP;
716 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
717 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
718 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,l_proc_name);
719 END IF;
720 END Merge_Parties;
721
722 --========================================================================
723 -- PROCEDURE :Merge_PartySites
724 -- PARAMETERS:
725 -- p_entity_name Name of Entity Being Merged
726 -- p_from_id Primary Key Id of the entity that is being merged
727 -- p_to_id The record under the 'To Parent' that is being merged
728 -- p_from_fk_id Foreign Key id of the Old Parent Record
729 -- p_to_fk_id Foreign Key id of the New Parent Record
730 -- p_parent_entity_name Name of Parent Entity
731 -- p_batch_id Id of the Batch
732 -- p_batch_party_id Id uniquely identifies the batch and party record that is being merged
733 -- x_return_status Returns the status of call
734 --
735 -- COMMENT :
736 --
737 --
738 --========================================================================
739
740 PROCEDURE Merge_PartySites
741 ( p_entity_name IN VARCHAR2,
742 p_from_id IN NUMBER,
743 p_to_id IN OUT NOCOPY NUMBER,
744 p_from_fk_id IN NUMBER,
745 p_to_fk_id IN NUMBER,
746 p_parent_entity_name IN VARCHAR2,
747 p_batch_id IN NUMBER,
748 p_batch_party_id IN NUMBER,
749 x_return_status IN OUT NOCOPY VARCHAR2
750 ) IS
751
752 CURSOR c_charge_lines IS
753 SELECT charge_line_id
754 FROM inl_charge_lines cl
755 WHERE cl.party_site_id = p_from_fk_id
756 AND cl.ship_from_party_site_id is null
757 AND cl.bill_from_party_site_id is null
758 AND cl.poa_party_site_id is null;
759
760 TYPE id_tbl_type is TABLE of NUMBER INDEX BY BINARY_INTEGER;
761 l_charge_line_list id_tbl_type;
762 l_proc_name CONSTANT VARCHAR2 (30) := 'Merge_PartySites';
763 l_debug_info VARCHAR2(200);
764 l_return_status VARCHAR2(1);
765
766 BEGIN
767 --{
768
769 -- Standard Beginning of Procedure/Function Logging
770 INL_LOGGING_PVT.Log_BeginProc ( p_module_name => g_module_name,
771 p_procedure_name => l_proc_name);
772
773 -- Standard Start of API savepoint
774 SAVEPOINT Merge_PartySites_GRP;
775
776 -- Initialize API return status to success
777 x_return_status := FND_API.G_RET_STS_SUCCESS;
778
779 -- Logging variables
780 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
781 p_procedure_name => l_proc_name,
782 p_var_name => 'p_entity_name',
783 p_var_value => p_entity_name);
784 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
785 p_procedure_name => l_proc_name,
786 p_var_name => 'p_from_id',
787 p_var_value => p_from_id);
788 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
789 p_procedure_name => l_proc_name,
790 p_var_name => 'p_to_id',
791 p_var_value => p_to_id);
792 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
793 p_procedure_name => l_proc_name,
794 p_var_name => 'p_from_fk_id',
795 p_var_value => p_from_fk_id);
796 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
797 p_procedure_name => l_proc_name,
798 p_var_name => 'p_to_fk_id',
799 p_var_value => p_to_fk_id);
800 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
801 p_procedure_name => l_proc_name,
802 p_var_name => 'p_parent_entity_name',
803 p_var_value => p_parent_entity_name);
804 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
805 p_procedure_name => l_proc_name,
806 p_var_name => 'p_batch_id',
807 p_var_value => p_batch_id);
808 INL_LOGGING_PVT.Log_Variable ( p_module_name => g_module_name,
809 p_procedure_name => l_proc_name,
810 p_var_name => 'p_batch_party_id',
811 p_var_value => p_batch_party_id);
812
813 --{
814 OPEN c_charge_lines;
815 FETCH c_charge_lines BULK COLLECT INTO l_charge_line_list;
816 CLOSE c_charge_lines;
817
818 l_debug_info := 'Count of Rows fetched from Cursor C_CHARGE_LINES = ' || l_charge_line_list.count;
819 INL_LOGGING_PVT.Log_Statement ( p_module_name => g_module_name,
820 p_procedure_name => l_proc_name,
821 p_debug_info => l_debug_info) ;
822
823 IF l_charge_line_list.COUNT > 0 THEN
824 --
825 FORALL j IN l_charge_line_list.FIRST..l_charge_line_list.LAST
826 UPDATE inl_charge_lines
827 SET party_site_id = p_to_fk_id,
828 last_update_date = sysdate,
829 last_updated_by = fnd_global.user_id,
830 last_update_login = fnd_global.login_id
831 WHERE charge_line_id = l_charge_line_list(j);
832
833 l_debug_info := 'Updated inl_charges. Number of Rows updated is ' || sql%rowcount;
834 INL_LOGGING_PVT.Log_Statement ( p_module_name => g_module_name,
835 p_procedure_name => l_proc_name,
836 p_debug_info => l_debug_info) ;
837 END IF;
838 --}
839
840 l_debug_info := 'x_return_status '||x_return_status;
841 INL_LOGGING_PVT.Log_Statement ( p_module_name => g_module_name,
842 p_procedure_name => l_proc_name,
843 p_debug_info => l_debug_info) ;
844
845 -- Standard End of Procedure/Function Logging
846 INL_LOGGING_PVT.Log_EndProc ( p_module_name => g_module_name,
847 p_procedure_name => l_proc_name) ;
848
849 --}
850 EXCEPTION
851 WHEN FND_API.G_EXC_ERROR THEN
852 -- Standard Expected Error Logging
853 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
854 p_procedure_name => l_proc_name);
855 ROLLBACK TO Merge_PartySites_GRP;
856 x_return_status := FND_API.G_RET_STS_ERROR;
857 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
858 -- Standard Unexpected Error Logging
859 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
860 p_procedure_name => l_proc_name);
861 ROLLBACK TO Merge_PartySites_GRP;
862 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
863 WHEN OTHERS THEN
864 -- Standard Unexpected Error Logging
865 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
866 p_procedure_name => l_proc_name);
867 ROLLBACK TO Merge_PartySites_GRP;
868 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
869 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
870 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,l_proc_name);
871 END IF;
872
873 END Merge_PartySites;
874
875 END INL_TCAMERGE_GRP;