[Home] [Help]
PACKAGE BODY: APPS.ZX_PARTY_MERGE_PKG
Source
1 PACKAGE BODY ZX_PARTY_MERGE_PKG AS
2 /* $Header: zxcptpmb.pls 120.5 2006/02/24 22:08:58 dbetanco ship $ */
3
4
5 g_api_name constant varchar2(30) := 'ZX_PARTY_MERGE_PKG';
6 --g_user_id constant number(15) := arp_standard.profile.user_id;
7 --g_login_id constant number(15) := arp_standard.profile.last_update_login;
8
9 g_user_id constant number(15) := 101;
10 g_login_id constant number(15) := 101;
11
12 -- Logging Infra
13 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ZX_PARTY_MERGE_PKG';
14 G_CURRENT_RUNTIME_LEVEL NUMBER;
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(30) := 'ZX.PLSQL.ZX_PARTY_MERGE_PKG';
22
23
24 PROCEDURE ZX_CUST_REG_MERGE (
25 p_entity_name in hz_merge_dictionary.entity_name%type,
26 p_from_id in oks_billing_profiles_b.id%type,
27 x_to_id in out nocopy oks_billing_profiles_b.id%type,
28 p_from_fk_id in hz_merge_parties.from_party_id%type,
29 p_to_fk_id in hz_merge_parties.to_party_id%type,
30 p_parent_entity_name in hz_merge_dictionary.parent_entity_name%type,
31 p_batch_id in hz_merge_batch.batch_id%type,
32 p_batch_party_id in hz_merge_party_details.batch_party_id%type,
33 x_return_status out nocopy varchar2)
34 IS
35 -- Enter the procedure variables here. As shown below
36 l_proc_name varchar2(30) := 'ZX_CUST_REG_MERGE';
37 l_count number(10) := 0;
38 l_from_start_date date;
39 l_from_end_date date;
40 l_to_start_date date;
41 l_to_end_date date;
42 l_registration_from VARCHAR2(50);
43 l_registration_to VARCHAR2(50);
44 l_registration_id_from NUMBER;
45 l_registration_id_to NUMBER;
46 l_update_reg_from_date DATE;
47 l_update_reg_to_date DATE;
48 l_location_id_from NUMBER;
49 l_location_id_to NUMBER;
50 l_reg_src_code_from VARCHAR2(30);
51 l_reg_src_code_to VARCHAR2(30);
52 l_reg_reason_code_from VARCHAR2(30);
53 l_reg_reason_code_to VARCHAR2(30);
54 l_rep_tax_auth_id_from NUMBER;
55 l_rep_tax_auth_id_to NUMBER;
56 l_coll_tax_auth_id_from NUMBER;
57 l_coll_tax_auth_id_to NUMBER;
58
59
60 cursor registration_number(p_fk_id hz_merge_parties.from_party_id%type) IS
61 select registration_id, registration_number, effective_from, effective_to,
62 LEGAL_LOCATION_ID, REGISTRATION_SOURCE_CODE, REGISTRATION_REASON_CODE,
63 REP_TAX_AUTHORITY_ID, COLL_TAX_AUTHORITY_ID
64 from zx_registrations reg, zx_party_tax_profile prof
65 where reg.PARTY_TAX_PROFILE_ID = prof.PARTY_TAX_PROFILE_ID
66 and prof.party_id = p_fk_id;
67 from_registration_rec registration_number%ROWTYPE;
68 to_registration_rec registration_number%ROWTYPE;
69
70 -- Logging Infra
71 l_procedure_name CONSTANT VARCHAR2(30) := '.ZX_CUST_REG_MERGE ';
72 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
73
74 BEGIN
75 -- arp_message.set_line(g_api_name||'.'||l_proc_name);
76 --------------------------------------------------------------------------------
77 -- Logging Infra: Setting up runtime level
78 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
79
80 -- Logging Infra: Procedure level
81 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
82 l_log_msg := l_procedure_name||' (+) ';
83 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||' .begin ', l_log_msg);
84 END IF;
85 --------------------------------------------------------------------------------
86
87 x_return_status := FND_API.G_RET_STS_SUCCESS;
88
89 --If it is a Site Merge, nothing to be done. Return the x_to_id.
90
91 if p_from_fk_id = p_to_fk_id then
92 x_to_id := p_from_id;
93 return;
94 end if;
95
96 if p_from_fk_id <> p_to_fk_id then
97 BEGIN
98
99 arp_message.set_line('Updating zx_party_tax_profile...');
100
101 OPEN registration_number(p_from_fk_id);
102 FETCH registration_number INTO from_registration_rec;
103 IF registration_number%FOUND THEN
104 l_registration_id_from :=from_registration_rec.registration_id;
105 l_registration_from :=from_registration_rec.registration_number;
106 l_from_start_date :=from_registration_rec.effective_from;
107 l_from_end_date :=from_registration_rec.effective_to;
108 l_location_id_from :=from_registration_rec.LEGAL_LOCATION_ID;
109 l_reg_src_code_from :=from_registration_rec.REGISTRATION_SOURCE_CODE;
110 l_reg_reason_code_from :=from_registration_rec.REGISTRATION_REASON_CODE;
111 l_rep_tax_auth_id_from :=from_registration_rec.REP_TAX_AUTHORITY_ID;
112 l_coll_tax_auth_id_from :=from_registration_rec.COLL_TAX_AUTHORITY_ID;
113 END IF;
114 CLOSE registration_number;
115
116 OPEN registration_number(p_to_fk_id);
117 FETCH registration_number INTO to_registration_rec;
118
119 IF registration_number%FOUND THEN
120 l_registration_id_to :=to_registration_rec.registration_id;
121 l_registration_to :=to_registration_rec.registration_number;
122 l_to_start_date :=to_registration_rec.effective_from;
123 l_to_end_date :=to_registration_rec.effective_to;
124 l_location_id_to :=to_registration_rec.LEGAL_LOCATION_ID;
125 l_reg_src_code_to :=to_registration_rec.REGISTRATION_SOURCE_CODE;
126 l_reg_reason_code_to :=to_registration_rec.REGISTRATION_REASON_CODE;
127 l_rep_tax_auth_id_to :=to_registration_rec.REP_TAX_AUTHORITY_ID;
128 l_coll_tax_auth_id_to :=to_registration_rec.COLL_TAX_AUTHORITY_ID;
129 END IF;
130 CLOSE registration_number;
131
132 if (l_registration_from = l_registration_to) then
133
134 if(l_from_start_date > l_to_start_date) THEN
135 l_update_reg_from_date := l_to_start_date;
136 else
137 l_update_reg_from_date := l_from_start_date;
138 end if;
139
140 if(l_from_end_date is null or l_to_end_date is null) THEN
141 l_update_reg_to_date := null;
142 elsif(l_from_end_date > l_to_end_date) THEN
143 l_update_reg_to_date := l_from_end_date;
144 else
145 l_update_reg_to_date := l_to_end_date;
146 end if;
147
148 if(l_location_id_to is null and l_location_id_from is not null) THEN
149 l_location_id_to := l_location_id_from;
150 end if;
151
152 if(l_reg_src_code_to is null and l_reg_src_code_from is not null) THEN
153 l_reg_src_code_to := l_reg_src_code_from;
154 end if;
155
156 if(l_reg_reason_code_to is null and l_reg_reason_code_from is not null) THEN
157 l_reg_reason_code_to := l_reg_reason_code_from;
158 end if;
159
160 if(l_rep_tax_auth_id_to is null and l_rep_tax_auth_id_from is not null) THEN
161 l_rep_tax_auth_id_to := l_rep_tax_auth_id_from;
162 end if;
163
164 if(l_coll_tax_auth_id_to is null and l_coll_tax_auth_id_from is not null) THEN
165 l_coll_tax_auth_id_to := l_coll_tax_auth_id_from;
166 end if;
167
168 update zx_registrations
169 set merged_to_registration_id = l_registration_id_to,
170 effective_to = sysdate,
171 last_update_date = sysdate,
172 last_updated_by = g_user_id,
173 last_update_login = g_login_id,
174 object_version_number = object_version_number+1
175 where registration_id = l_registration_id_from;
176
177 update zx_registrations
178 set effective_from = l_update_reg_from_date,
179 effective_to = l_update_reg_to_date,
180 LEGAL_LOCATION_ID = l_location_id_to,
181 REGISTRATION_SOURCE_CODE = l_reg_src_code_to,
182 REGISTRATION_REASON_CODE = l_reg_reason_code_to,
183 REP_TAX_AUTHORITY_ID = l_rep_tax_auth_id_to,
184 COLL_TAX_AUTHORITY_ID = l_coll_tax_auth_id_to,
185 last_update_date = sysdate,
186 last_updated_by = g_user_id,
187 last_update_login = g_login_id,
188 object_version_number = object_version_number+1
189 where registration_id = l_registration_id_to;
190
191 l_count := sql%rowcount;
192 arp_message.set_name('AR','AR_ROWS_UPDATED');
193 arp_message.set_token('NUM_ROWS',to_char(l_count));
194
195 end if; -- (l_registration_from = l_registration_to)
196
197 EXCEPTION
198 WHEN NO_DATA_FOUND THEN
199 NULL;
200 WHEN OTHERS THEN
201 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
202 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
203 FND_MSG_PUB.ADD;
204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
205 END;
206 end if; -- p_from_fk_id <> p_to_fk_id
207 ---------------------------------------------------------------------------------
208 -- Logging Infra: Procedure level
209 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
210 l_log_msg := l_procedure_name||' (-) ';
211 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.END ', l_log_msg);
212 END IF;
213 ---------------------------------------------------------------------------------
214 end ZX_CUST_REG_MERGE;
215
216 PROCEDURE ZX_PTP_MERGE (
217 p_entity_name in hz_merge_dictionary.entity_name%type,
218 p_from_id in oks_billing_profiles_b.id%type,
219 x_to_id in out nocopy oks_billing_profiles_b.id%type,
220 p_from_fk_id in hz_merge_parties.from_party_id%type,
221 p_to_fk_id in hz_merge_parties.to_party_id%type,
222 p_parent_entity_name in hz_merge_dictionary.parent_entity_name%type,
223 p_batch_id in hz_merge_batch.batch_id%type,
224 p_batch_party_id in hz_merge_party_details.batch_party_id%type,
225 x_return_status out nocopy varchar2)
226 IS
227 -- Enter the procedure variables here. As shown below
228 l_proc_name varchar2(30) := 'ZX_PARTY_TAX_PROFILE';
229 l_count number(10) := 0;
230 l_ptp_id_from NUMBER;
231 l_ptp_id_to NUMBER;
232 l_code_assignment_id hz_code_assignments.owner_table_name%TYPE;
233
234 cursor Party_Tax_Profile(p_fk_id hz_merge_parties.from_party_id%type) IS
235 select Party_Tax_Profile_id
236 from zx_party_tax_profile prof
237 where prof.party_id = p_fk_id
238 and prof.party_type_code = 'THIRD_PARTY';
239
240 from_ptp_rec Party_Tax_Profile%ROWTYPE;
241 to_ptp_rec Party_Tax_Profile%ROWTYPE;
242
243 cursor Class_Categories_From(p_ptp_id number) IS
244 select code_assignment_id, class_category, class_code, END_DATE_ACTIVE
245 from hz_code_assignments
246 where owner_table_name = 'ZX_PARTY_TAX_PROFILE'
247 and owner_table_id = p_ptp_id
248 and NVL(END_DATE_ACTIVE,SYSDATE) >= sysdate;
249
250 cursor Class_Codes_To(p_ptp_id number,
251 p_class_category hz_code_assignments.class_category%type,
252 p_end_date hz_code_assignments.end_date_active%type,
253 p_class_code hz_code_assignments.class_code%type) IS
254 select distinct class_category, class_code, END_DATE_ACTIVE
255 from hz_code_assignments
256 where owner_table_name = 'ZX_PARTY_TAX_PROFILE'
257 and owner_table_id = p_ptp_id
258 and NVL(END_DATE_ACTIVE,SYSDATE) >= NVL(p_end_date,SYSDATE)
259 and class_category = p_class_category
260 and class_code = p_class_code
261 group by class_category, class_code, END_DATE_ACTIVE;
262
263 Class_Codes_To_Rec Class_Codes_To%ROWTYPE;
264
265 -- Logging Infra
266 l_procedure_name CONSTANT VARCHAR2(30) := '.ZX_PTP_MERGE ';
267 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
268
269 BEGIN
270 -- arp_message.set_line(g_api_name||'.'||l_proc_name);
271 --------------------------------------------------------------------------------------
272 -- Logging Infra: Setting up runtime level
273 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
274
275 -- Logging Infra: Procedure level
276 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
277 l_log_msg := l_procedure_name||' (+) ';
278 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||' .begin ', l_log_msg);
279 END IF;
280 ---------------------------------------------------------------------------------
281
282 x_return_status := FND_API.G_RET_STS_SUCCESS;
283
284 --If it is a Site Merge, nothing to be done. Return the x_to_id.
285
286 if p_from_fk_id = p_to_fk_id then
287 x_to_id := p_from_id;
288 return;
289 end if;
290
291 if p_from_fk_id <> p_to_fk_id then
292
293 BEGIN
294 arp_message.set_line('Updating zx_party_tax_profile...');
295
296 OPEN Party_Tax_Profile(p_from_fk_id);
297 FETCH Party_Tax_Profile INTO from_ptp_rec;
298 IF Party_Tax_Profile%FOUND THEN
299 l_ptp_id_from := from_ptp_rec.Party_Tax_Profile_id;
300 END IF;
301 CLOSE Party_Tax_Profile;
302
303 OPEN Party_Tax_Profile(p_to_fk_id);
304 FETCH Party_Tax_Profile INTO to_ptp_rec;
305 IF Party_Tax_Profile%FOUND THEN
306 l_ptp_id_to := to_ptp_rec.Party_Tax_Profile_id;
307 END IF;
308 CLOSE Party_Tax_Profile;
309
310 update zx_party_tax_profile
311 set merged_to_ptp_id = l_ptp_id_to,
312 MERGED_STATUS_CODE = 'MERGED',
313 last_update_date = sysdate,
314 last_updated_by = g_user_id,
315 last_update_login = g_login_id,
316 object_version_number = object_version_number+1
317 where Party_Tax_Profile_id = l_ptp_id_from;
318
319 l_count := sql%rowcount;
320 arp_message.set_name('AR','AR_ROWS_UPDATED');
321 arp_message.set_token('NUM_ROWS',to_char(l_count));
322
323 For code_assig IN Class_Categories_From (l_ptp_id_from) LOOP
324 OPEN Class_Codes_To(l_ptp_id_to
325 ,code_assig.class_category
326 ,code_assig.END_DATE_ACTIVE
327 ,code_assig.class_code);
328 FETCH Class_Codes_To INTO Class_Codes_To_Rec.Class_Category,
329 Class_Codes_To_Rec.class_code,
330 Class_Codes_To_Rec.END_DATE_ACTIVE;
331 IF Class_Codes_To%NOTFOUND THEN
332 update hz_code_assignments
333 set owner_table_id = l_ptp_id_to,
334 last_update_date = sysdate,
335 last_updated_by = g_user_id,
336 last_update_login = g_login_id,
337 object_version_number = object_version_number+1
338 where code_assignment_id = code_assig.code_assignment_id;
339 END IF;
340 CLOSE Class_Codes_To;
341 END Loop;
342
343 EXCEPTION
344 WHEN NO_DATA_FOUND THEN
345 NULL;
346 WHEN OTHERS THEN
347 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
348 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
349 FND_MSG_PUB.ADD;
350 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
351 END;
352 end if;
353 ---------------------------------------------------------------------------------
354 -- Logging Infra: Procedure level
355 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
356 l_log_msg := l_procedure_name||' (-) ';
357 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.END ', l_log_msg);
358 END IF;
359 ---------------------------------------------------------------------------------
360 end ZX_PTP_MERGE;
361
362 PROCEDURE ZX_TAX_AUTH_MERGE (
363 p_entity_name in hz_merge_dictionary.entity_name%type,
364 p_ptp_id_from in NUMBER,
365 p_ptp_id_to in NUMBER,
366 x_to_id in out nocopy oks_billing_profiles_b.id%type,
367 p_from_fk_id in hz_merge_parties.from_party_id%type,
368 p_to_fk_id in hz_merge_parties.to_party_id%type,
369 x_return_status out nocopy varchar2)
370 IS
371 -- Enter the procedure variables here. As shown below
372 l_proc_name varchar2(30) := 'ZX_TAX_AUTH_MERGE';
373 l_count number(10) := 0;
374
375 -- Logging Infra
376 l_procedure_name CONSTANT VARCHAR2(30) := '.ZX_TAX_AUTH_MERGE ';
377 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
378
379 BEGIN
380 --arp_message.set_line(g_api_name||'.'||l_proc_name);
381 --------------------------------------------------------------------------------------
382 -- Logging Infra: Setting up runtime level
383 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
384
385 -- Logging Infra: Procedure level
386 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
387 l_log_msg := l_procedure_name||' (+) ';
388 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||' .begin ', l_log_msg);
389 END IF;
390 ---------------------------------------------------------------------------------
391
392
393 x_return_status := FND_API.G_RET_STS_SUCCESS;
394
395 --If it is a Site Merge, nothing to be done. Return the x_to_id.
396
397 if p_from_fk_id = p_to_fk_id then
398 return;
399 end if;
400
401 if p_from_fk_id <> p_to_fk_id then
402 BEGIN
403
404 arp_message.set_line('Updating zx_registrations for tax Authorities...');
405
406 update zx_registrations
407 set TAX_AUTHORITY_ID = p_ptp_id_to,
408 last_update_date = sysdate,
409 last_updated_by = g_user_id,
410 last_update_login = g_login_id,
411 object_version_number = object_version_number+1
412 where TAX_AUTHORITY_ID = p_ptp_id_from;
413
414 update zx_registrations
415 set REP_TAX_AUTHORITY_ID = p_ptp_id_to,
419 object_version_number = object_version_number+1
416 last_update_date = sysdate,
417 last_updated_by = g_user_id,
418 last_update_login = g_login_id,
420 where REP_TAX_AUTHORITY_ID = p_ptp_id_from;
421
422 update zx_registrations
423 set COLL_TAX_AUTHORITY_ID = p_ptp_id_to,
424 last_update_date = sysdate,
425 last_updated_by = g_user_id,
426 last_update_login = g_login_id,
427 object_version_number = object_version_number+1
428 where COLL_TAX_AUTHORITY_ID = p_ptp_id_from;
429
430
431 arp_message.set_line('Updating zx_taxes_b for tax Authorities...');
432
433 update zx_taxes_b
434 set REP_TAX_AUTHORITY_ID = p_ptp_id_to,
435 last_update_date = sysdate,
436 last_updated_by = g_user_id,
437 last_update_login = g_login_id,
438 object_version_number = object_version_number+1
439 where REP_TAX_AUTHORITY_ID = p_ptp_id_from;
440
441 update zx_taxes_b
442 set COLL_TAX_AUTHORITY_ID = p_ptp_id_to,
443 last_update_date = sysdate,
444 last_updated_by = g_user_id,
445 last_update_login = g_login_id,
446 object_version_number = object_version_number+1
447 where COLL_TAX_AUTHORITY_ID = p_ptp_id_from;
448
449
450 arp_message.set_line('Updating zx_regimes_b for tax Authorities...');
451
452 update zx_regimes_b
453 set REP_TAX_AUTHORITY_ID = p_ptp_id_to,
454 last_update_date = sysdate,
455 last_updated_by = g_user_id,
456 last_update_login = g_login_id,
457 object_version_number = object_version_number+1
458 where REP_TAX_AUTHORITY_ID = p_ptp_id_from;
459
460 update zx_regimes_b
461 set COLL_TAX_AUTHORITY_ID = p_ptp_id_to,
462 last_update_date = sysdate,
463 last_updated_by = g_user_id,
464 last_update_login = g_login_id,
465 object_version_number = object_version_number+1
466 where COLL_TAX_AUTHORITY_ID = p_ptp_id_from;
467
468 arp_message.set_line('Updating zx_jurisdictions_b for tax Authorities...');
469
470 update zx_jurisdictions_b
471 set REP_TAX_AUTHORITY_ID = p_ptp_id_to,
472 last_update_date = sysdate,
473 last_updated_by = g_user_id,
474 last_update_login = g_login_id,
475 object_version_number = object_version_number+1
476 where REP_TAX_AUTHORITY_ID = p_ptp_id_from;
477
478 update zx_jurisdictions_b
479 set COLL_TAX_AUTHORITY_ID = p_ptp_id_to,
480 last_update_date = sysdate,
481 last_updated_by = g_user_id,
482 last_update_login = g_login_id,
483 object_version_number = object_version_number+1
484 where COLL_TAX_AUTHORITY_ID = p_ptp_id_from;
485
486 arp_message.set_line('Updating zx_jurisdictions_b for tax Authorities...');
487
488 update zx_exemptions
489 set ISSUING_TAX_AUTHORITY_ID = p_ptp_id_to,
490 last_update_date = sysdate,
491 last_updated_by = g_user_id,
492 last_update_login = g_login_id,
493 object_version_number = object_version_number+1
494 where ISSUING_TAX_AUTHORITY_ID = p_ptp_id_from;
495
496 l_count := sql%rowcount;
497 arp_message.set_name('AR','AR_ROWS_UPDATED');
498 arp_message.set_token('NUM_ROWS',to_char(l_count));
499
500 EXCEPTION
501 WHEN NO_DATA_FOUND THEN
502 NULL;
503 WHEN OTHERS THEN
504 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
505 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
506 FND_MSG_PUB.ADD;
507 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
508 END;
509 end if;
510 ---------------------------------------------------------------------------------
511 -- Logging Infra: Procedure level
512 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
513 l_log_msg := l_procedure_name||' (-) ';
514 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.END ', l_log_msg);
515 END IF;
516 ---------------------------------------------------------------------------------
517 end ZX_TAX_AUTH_MERGE;
518
519 PROCEDURE ZX_EXEMPTIONS_P (
520 p_entity_name in hz_merge_dictionary.entity_name%type,
521 p_from_id in oks_billing_profiles_b.id%type,
522 x_to_id in out nocopy oks_billing_profiles_b.id%type,
523 p_from_fk_id in hz_merge_parties.from_party_id%type,
524 p_to_fk_id in hz_merge_parties.to_party_id%type,
525 p_parent_entity_name in hz_merge_dictionary.parent_entity_name%type,
526 p_batch_id in hz_merge_batch.batch_id%type,
527 p_batch_party_id in hz_merge_party_details.batch_party_id%type,
528 x_return_status out nocopy varchar2)
529
530 IS
531
532 cursor exe_number(p_fk_id hz_merge_parties.from_party_id%type) IS
533 select TAX_EXEMPTION_ID
534 , EXEMPT_CERTIFICATE_NUMBER
535 , effective_from
536 , effective_to
537 , EXEMPTION_TYPE_CODE
538 , EXEMPTION_STATUS_CODE
539 ,TAX_REGIME_CODE
540 from zx_exemptions exemp
541 where party_tax_profile_id = p_fk_id;
542
543 CURSOR to_exemption (l_certificate_number varchar2,
544 l_effective_from zx_exemptions.effective_from%type,
545 l_effective_to zx_exemptions.effective_to%type,
546 l_type_code zx_exemptions.exemption_type_code%type,
547 l_status_code zx_exemptions.exemption_status_code%type,
548 l_tax_regime_code zx_exemptions.tax_regime_code%type)
549 IS
550 select TAX_EXEMPTION_ID
551 from zx_exemptions exemp
552 where party_tax_profile_id = p_to_fk_id
553 and exempt_certificate_number = l_certificate_number
554 and effective_from = l_effective_from
555 and nvl(effective_to,l_effective_to) = l_effective_to
556 and exemption_type_code = l_type_code
557 and exemption_status_code = l_status_code
558 and tax_regime_code = l_tax_regime_code;
559 to_exemption_rec to_exemption%ROWTYPE;
560
561 -- Enter the procedure variables here.
562 l_proc_name varchar2(30) := 'ZX_EXEMPTIONS';
563 l_count number(10) := 0;
564
565 -- Logging Infra
566 l_procedure_name CONSTANT VARCHAR2(30) := '.ZX_EXEMPTIONS_P ';
567 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
568
569 BEGIN
570 -- arp_message.set_line(g_api_name||'.'||l_proc_name);
571 --------------------------------------------------------------------------------------
572 -- Logging Infra: Setting up runtime level
573 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
574
575 -- Logging Infra: Procedure level
576 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
577 l_log_msg := l_procedure_name||' (+) ';
578 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||' .begin ', l_log_msg);
579 END IF;
580 ---------------------------------------------------------------------------------
581
582 x_return_status := FND_API.G_RET_STS_SUCCESS;
583
584 --If it is a Site Merge, nothing to be done. Return the x_to_id.
585
586 if p_from_fk_id = p_to_fk_id then
587 x_to_id := p_from_id;
588 return;
589 end if;
590
591 if p_from_fk_id <> p_to_fk_id then
592 BEGIN
593
594 arp_message.set_line('Updating exemptions...');
595 For rec_exe IN exe_number(p_from_fk_id) Loop
596 OPEN to_exemption(rec_exe.exempt_certificate_number
597 ,rec_exe.effective_from
598 ,rec_exe.effective_to
599 ,rec_exe.exemption_type_code
600 ,rec_exe.exemption_status_code
601 ,rec_exe.tax_regime_code);
602 FETCH to_exemption INTO to_exemption_rec;
606 party_tax_profile_id = p_to_fk_id,
603 IF to_exemption%NOTFOUND THEN
604 update zx_exemptions
605 set --merged_to_exemption_id = l_exemption_id_to,
607 last_update_date = sysdate,
608 last_updated_by = g_user_id,
609 last_update_login = g_login_id,
610 object_version_number = object_version_number+1
611 where TAX_EXEMPTION_ID = rec_exe.tax_exemption_id;
612 End IF;
613 CLOSE to_exemption;
614 END Loop;
615 l_count := sql%rowcount;
616 arp_message.set_name('AR','AR_ROWS_UPDATED');
617 arp_message.set_token('NUM_ROWS',to_char(l_count));
618
619 EXCEPTION
620 WHEN NO_DATA_FOUND THEN
621 NULL;
622 WHEN OTHERS THEN
623 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
624 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
625 FND_MSG_PUB.ADD;
626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
627 END;
628 end if;
629 ---------------------------------------------------------------------------------
630 -- Logging Infra: Procedure level
631 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
632 l_log_msg := l_procedure_name||' (-) ';
633 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.END ', l_log_msg);
634 END IF;
635 ---------------------------------------------------------------------------------
636 end ZX_EXEMPTIONS_P;
637
638 PROCEDURE ZX_CUSTOMER_VETO (
639 p_ptp_id_from in number,
640 p_ptp_id_to in number,
641 x_merge_yn out nocopy VARCHAR2,
642 p_from_fk_id in hz_merge_parties.from_party_id%type,
643 p_to_fk_id in hz_merge_parties.to_party_id%type,
644 x_return_status out nocopy varchar2)
645 IS
646 -- Enter the procedure variables here. As shown below
647 l_proc_name varchar2(30) := 'ZX_CUSTOMER_VETO';
648 l_count number(10) := 0;
649 l_ptp_id_from NUMBER;
650 l_ptp_id_to NUMBER;
651 l_calculate_tax_from VARCHAR2(1);
652 l_calculate_tax_to VARCHAR2(1);
653 l_code_assignment_id hz_code_assignments.owner_table_name%TYPE;
654 l_hash_key BINARY_INTEGER;
655
656 l_reg_attr_tbl_from reg_attr_tbl_type;
657 l_reg_attr_tbl_to reg_attr_tbl_type;
658 TABLE_SIZE BINARY_INTEGER := 2048;
659 -- class_category_rec class_category_rec_type;
660 -- class_category_tbl class_category_tbl_type;
661
662 cursor Calculate_Tax_Flag(p_ptp_id NUMBER) IS
663 select PROCESS_FOR_APPLICABILITY_FLAG
664 from zx_party_tax_profile ptp
665 where ptp.party_tax_profile_id = p_ptp_id;
666
667 from_calc_tax_rec Calculate_Tax_Flag%ROWTYPE;
668 to_calc_tax_rec Calculate_Tax_Flag%ROWTYPE;
669
670 cursor Registration_Attributes(p_ptp_id number) IS
671 select a.REGISTRATION_TYPE_CODE, a.REGISTRATION_NUMBER, a.ROUNDING_RULE_CODE,
672 a.SELF_ASSESS_FLAG, a.INCLUSIVE_TAX_FLAG
673 from zx_registrations a, zx_party_tax_profile b
674 where b.party_tax_profile_id = p_ptp_id
675 and a.party_tax_profile_id = b.party_tax_profile_id;
676
677 Reg_Attr_From_Rec Registration_Attributes%ROWTYPE;
681 l_procedure_name CONSTANT VARCHAR2(30) := '.ZX_CUSTOMER_VETO ';
678 Reg_Attr_To_Rec Registration_Attributes%ROWTYPE;
679
680 -- Logging Infra
682 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
683
684
685 BEGIN
686 --arp_message.set_line(g_api_name||'.'||l_proc_name);
687 --------------------------------------------------------------------------------------
688 -- Logging Infra: Setting up runtime level
689 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
690
691 -- Logging Infra: Procedure level
692 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
693 l_log_msg := l_procedure_name||' (+) ';
694 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||' .begin ', l_log_msg);
695 END IF;
696 ---------------------------------------------------------------------------------
697
698 x_return_status := FND_API.G_RET_STS_SUCCESS;
699
700 /* Tax Applicability it is just for Suppliers
701 Commenting as per Nigel Updates 3511846
702 OPEN Calculate_Tax_Flag(p_ptp_id_from);
703 FETCH Calculate_Tax_Flag INTO from_calc_tax_rec;
704 IF Calculate_Tax_Flag%FOUND THEN
705 l_calculate_tax_from :=from_calc_tax_rec.PROCESS_FOR_APPLICABILITY_FLAG;
706 END IF;
707 CLOSE Calculate_Tax_Flag;
708
709 OPEN Calculate_Tax_Flag(p_ptp_id_to);
710 FETCH Calculate_Tax_Flag INTO to_calc_tax_rec;
711 IF Calculate_Tax_Flag%FOUND THEN
712 l_calculate_tax_to :=to_calc_tax_rec.PROCESS_FOR_APPLICABILITY_FLAG;
713 END IF;
714 CLOSE Calculate_Tax_Flag;
715
716
717 if(l_calculate_tax_from <> l_calculate_tax_to) THEN
718 arp_message.set_line('Parties '||p_from_fk_id||' and '||p_to_fk_id||' cannot be merged
719 as Calculate Tax Flag has different Values');
720 x_merge_yn := 'N';
721 end if;
722 */
723
724 FOR REC in Registration_Attributes(p_ptp_id_to)
725 LOOP
726
727 l_hash_key := DBMS_UTILITY.get_hash_value(REC.REGISTRATION_TYPE_CODE||REC.REGISTRATION_NUMBER||REC.ROUNDING_RULE_CODE||REC.SELF_ASSESS_FLAG||REC.INCLUSIVE_TAX_FLAG,1,TABLE_SIZE);
728
729 l_reg_attr_tbl_to(l_hash_key) := REC;
730 END LOOP;
731
732 FOR REC in Registration_Attributes(p_ptp_id_from)
733 LOOP
734
735 l_hash_key := DBMS_UTILITY.get_hash_value(REC.REGISTRATION_TYPE_CODE||REC.REGISTRATION_NUMBER||REC.ROUNDING_RULE_CODE||REC.SELF_ASSESS_FLAG||REC.INCLUSIVE_TAX_FLAG,1,TABLE_SIZE);
736
737 if(l_reg_attr_tbl_to.exists(l_hash_key)) THEN
738 x_merge_yn := 'Y';
739 ELSE
740 arp_message.set_line('Parties '||p_from_fk_id||' and '||p_to_fk_id||' cannot
741 be merged as Registration Attributes have different Values');
742 x_merge_yn := 'N';
743 END IF;
744
745 -- l_reg_attr_tbl_from(l_hash_key) := REC;
746 END LOOP;
747
748 ---------------------------------------------------------------------------------
749 -- Logging Infra: Procedure level
750 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
751 l_log_msg := l_procedure_name||' (-) ';
752 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.END ', l_log_msg);
753 END IF;
754 ---------------------------------------------------------------------------------
755
756 EXCEPTION
757 WHEN NO_DATA_FOUND THEN
758 NULL;
759 WHEN OTHERS THEN
760 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
761 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
762 FND_MSG_PUB.ADD;
763 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
764 end ZX_CUSTOMER_VETO;
765
766 PROCEDURE ZX_MERGE (
767 p_entity_name in hz_merge_dictionary.entity_name%type,
768 p_from_id in oks_billing_profiles_b.id%type,
769 x_to_id in out nocopy oks_billing_profiles_b.id%type,
770 p_from_fk_id in hz_merge_parties.from_party_id%type,
771 p_to_fk_id in hz_merge_parties.to_party_id%type,
772 p_parent_entity_name in hz_merge_dictionary.parent_entity_name%type,
773 p_batch_id in hz_merge_batch.batch_id%type,
774 p_batch_party_id in hz_merge_party_details.batch_party_id%type,
775 x_return_status out nocopy varchar2)
776 IS
777 -- Enter the procedure variables here. As shown below
778 l_proc_name varchar2(30) := 'ZX_MERGE';
779 l_count number(10) := 0;
780 l_ptp_id_from NUMBER;
781 l_ptp_id_to NUMBER;
782 l_code_assignment_id hz_code_assignments.owner_table_name%TYPE;
783 l_party_type_from VARCHAR2(30);
784 l_party_type_to VARCHAR2(30);
785 l_merge_yn VARCHAR2(1);
786
787
788 cursor Party_Tax_Profile(p_fk_id hz_merge_parties.from_party_id%type) IS
789 select Party_Tax_Profile_id, party_type_code
790 from zx_party_tax_profile prof
791 where prof.party_id = p_fk_id
792 and prof.party_type_code = 'THIRD_PARTY';
793
794 from_ptp_rec Party_Tax_Profile%ROWTYPE;
795 to_ptp_rec Party_Tax_Profile%ROWTYPE;
796
797 -- Logging Infra
798 l_procedure_name CONSTANT VARCHAR2(30) := '.ZX_MERGE ';
799 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
800
801 BEGIN
802 -- arp_message.set_line(g_api_name||'.'||l_proc_name);
803 --------------------------------------------------------------------------------------
804 -- Logging Infra: Setting up runtime level
805 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
806
807 -- Logging Infra: Procedure level
808 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
809 l_log_msg := l_procedure_name||' (+) ';
810 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||' .begin ', l_log_msg);
814 x_return_status := FND_API.G_RET_STS_SUCCESS;
811 END IF;
812 ---------------------------------------------------------------------------------
813
815
816 --If it is a Site Merge, nothing to be done. Return the x_to_id.
817
818 if p_from_fk_id = p_to_fk_id then
819 x_to_id := p_from_id;
820 x_return_status := 'E';
821 return;
822 end if;
823
824 if p_from_fk_id <> p_to_fk_id then
825 BEGIN
826
827 arp_message.set_line('Identifying Party Type...');
828
829 OPEN Party_Tax_Profile(p_from_fk_id);
830 FETCH Party_Tax_Profile INTO from_ptp_rec;
831 IF Party_Tax_Profile%FOUND THEN
832 l_ptp_id_from :=from_ptp_rec.Party_Tax_Profile_id;
833 l_party_type_from := from_ptp_rec.Party_type_code;
834 END IF;
835 CLOSE Party_Tax_Profile;
836
837 OPEN Party_Tax_Profile(p_to_fk_id);
838 FETCH Party_Tax_Profile INTO from_ptp_rec;
839 IF Party_Tax_Profile%FOUND THEN
840 l_ptp_id_to :=from_ptp_rec.Party_Tax_Profile_id;
841 l_party_type_to := from_ptp_rec.Party_type_code;
842 END IF;
843 CLOSE Party_Tax_Profile;
844
845 if(l_party_type_from <> l_party_type_to) THEN
846 arp_message.set_line('Cannot Merge Parties, Party Types are different...');
847 x_return_status := 'E';
848 return;
849 else
850 if(l_party_type_from = 'THIRD_PARTY') THEN
851 ZX_CUSTOMER_VETO(
852 p_ptp_id_from => l_ptp_id_from,
853 p_ptp_id_to => l_ptp_id_to,
854 x_merge_yn => l_merge_yn,
855 p_from_fk_id => p_from_fk_id,
856 p_to_fk_id => p_to_fk_id,
857 x_return_status => x_return_status);
858
859 /* As per Nigel comments bug 3511846
860 ZX_FISCAL_CLASS_VETO(
861 p_ptp_id_from => l_ptp_id_from,
862 p_ptp_id_to => l_ptp_id_to,
863 x_merge_yn => l_merge_yn,
864 p_from_fk_id => p_from_fk_id,
865 p_to_fk_id => p_to_fk_id,
866 x_return_status => x_return_status);
867
868 */
869
870 if(l_merge_yn = 'N') THEN
871 x_return_status := 'E';
872 else
873 ZX_PTP_MERGE(
874 p_entity_name => p_entity_name,
875 p_from_id => p_from_id,
876 x_to_id => x_to_id,
877 p_from_fk_id => p_from_fk_id,
878 p_to_fk_id => p_to_fk_id,
879 p_parent_entity_name=> p_parent_entity_name,
880 p_batch_id => p_batch_id,
881 p_batch_party_id => p_batch_party_id,
882 x_return_status => x_return_status);
883
884 ZX_CUST_REG_MERGE(
885 p_entity_name => p_entity_name,
886 p_from_id => p_from_id,
887 x_to_id => x_to_id,
888 p_from_fk_id => p_from_fk_id,
889 p_to_fk_id => p_to_fk_id,
890 p_parent_entity_name=> p_parent_entity_name,
891 p_batch_id => p_batch_id,
892 p_batch_party_id => p_batch_party_id,
893 x_return_status => x_return_status);
894
895 ZX_EXEMPTIONS_P(
896 p_entity_name => p_entity_name,
897 p_from_id => p_from_id,
898 x_to_id => x_to_id,
899 p_from_fk_id => l_ptp_id_from,
900 p_to_fk_id => l_ptp_id_from,
901 p_parent_entity_name=> p_parent_entity_name,
902 p_batch_id => p_batch_id,
903 p_batch_party_id => p_batch_party_id,
904 x_return_status => x_return_status);
905 end if;
906 elsif(l_party_type_from = 'TAX_AUTHORITY') THEN
907 ZX_TAX_AUTH_MERGE (
908 p_entity_name => p_entity_name,
909 p_ptp_id_from => l_ptp_id_from,
910 p_ptp_id_to => l_ptp_id_to,
911 x_to_id => x_to_id,
912 p_from_fk_id => p_from_fk_id,
913 p_to_fk_id => p_to_fk_id,
914 x_return_status => x_return_status);
915 end if;
916 end if;
917
918 EXCEPTION
919 WHEN NO_DATA_FOUND THEN
920 NULL;
921 WHEN OTHERS THEN
922 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
923 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
924 FND_MSG_PUB.ADD;
925 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
926 END;
927 end if;
928 ---------------------------------------------------------------------------------
929 -- Logging Infra: Procedure level
930 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
931 l_log_msg := l_procedure_name||' (-) ';
932 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.END ', l_log_msg);
933 END IF;
934 ---------------------------------------------------------------------------------
935 end ZX_MERGE;
936
937 END ZX_PARTY_MERGE_PKG;