[Home] [Help]
PACKAGE BODY: APPS.ZX_PARTY_MERGE_PKG
Source
1 PACKAGE BODY ZX_PARTY_MERGE_PKG AS
2 /* $Header: zxcptpmb.pls 120.14.12020000.2 2012/08/14 12:05:01 srajapar ship $ */
3
4 G_USER_ID CONSTANT NUMBER(15) := FND_GLOBAL.user_id;
5 G_LOGIN_ID CONSTANT NUMBER(15) := FND_GLOBAL.login_id;
6
7 -- Logging Infra
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ZX_PARTY_MERGE_PKG';
9 G_CURRENT_RUNTIME_LEVEL NUMBER;
10 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
11 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
12 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
13 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
14 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
15 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
16 G_MODULE_NAME CONSTANT VARCHAR2(30) := 'ZX.PLSQL.ZX_PARTY_MERGE_PKG';
17
18
19 PROCEDURE ZX_CUST_REG_MERGE_PVT (
20 p_entity_name in hz_merge_dictionary.entity_name%type,
21 p_from_id in oks_billing_profiles_b.id%type,
22 x_to_id in out nocopy oks_billing_profiles_b.id%type,
23 p_from_fk_id in hz_merge_parties.from_party_id%type,
24 p_to_fk_id in hz_merge_parties.to_party_id%type,
25 p_parent_entity_name in hz_merge_dictionary.parent_entity_name%type,
26 p_batch_id in hz_merge_batch.batch_id%type,
27 p_batch_party_id in hz_merge_party_details.batch_party_id%type,
28 x_return_status out nocopy varchar2)
29 IS
30 -- Enter the procedure variables here. As shown below
31 l_count NUMBER(10) := 0;
32 --l_from_start_date DATE;
33 --l_from_end_date DATE;
34 --l_to_start_date DATE;
35 --l_to_end_date DATE;
36 --l_registration_from VARCHAR2(50);
37 --l_registration_to VARCHAR2(50);
38 --l_registration_id_from NUMBER;
39 --l_registration_id_to NUMBER;
40 --l_update_reg_from_date DATE;
41 --l_update_reg_to_date DATE;
42 --l_location_id_from NUMBER;
43 --l_location_id_to NUMBER;
44 --l_reg_src_code_from VARCHAR2(30);
45 --l_reg_src_code_to VARCHAR2(30);
46 --l_reg_reason_code_from VARCHAR2(30);
47 --l_reg_reason_code_to VARCHAR2(30);
48 --l_rep_tax_auth_id_from NUMBER;
49 --l_rep_tax_auth_id_to NUMBER;
50 --l_coll_tax_auth_id_from NUMBER;
51 --l_coll_tax_auth_id_to NUMBER;
52
53
54 --cursor registration_number(p_fk_id hz_merge_parties.from_party_id%type) IS
55 --select registration_id, registration_number, effective_from, effective_to,
56 --LEGAL_LOCATION_ID, REGISTRATION_SOURCE_CODE, REGISTRATION_REASON_CODE,
57 --REP_TAX_AUTHORITY_ID, COLL_TAX_AUTHORITY_ID
58 --from zx_registrations reg, zx_party_tax_profile prof
59 --where reg.PARTY_TAX_PROFILE_ID = prof.PARTY_TAX_PROFILE_ID
60 --and prof.party_id = p_fk_id;
61 --from_registration_rec registration_number%ROWTYPE;
62 --to_registration_rec registration_number%ROWTYPE;0
63
64 CURSOR registration_number
65 (p_from_party_id hz_merge_parties.from_party_id%type
66 ,p_to_party_id hz_merge_parties.to_party_id%type)
67 IS
68 SELECT from_reg.registration_id registration_id_from,
69 to_reg.registration_id registration_id_to,
70 CASE WHEN from_reg.effective_from > to_reg.effective_from
71 THEN to_reg.effective_from
72 ELSE from_reg.effective_from
73 END as update_reg_from_date,
74 CASE WHEN from_reg.effective_to IS NULL OR to_reg.effective_to IS NULL
75 THEN NULL
76 WHEN from_reg.effective_to > to_reg.effective_to
77 THEN from_reg.effective_to
78 ELSE to_reg.effective_to
79 END as update_reg_to_date,
80 CASE WHEN to_reg.LEGAL_LOCATION_ID IS NULL AND from_reg.LEGAL_LOCATION_ID IS NOT NULL
81 THEN from_reg.LEGAL_LOCATION_ID
82 END as location_id_to,
83 CASE WHEN to_reg.REGISTRATION_SOURCE_CODE IS NULL AND from_reg.REGISTRATION_SOURCE_CODE IS NOT NULL
84 THEN from_reg.REGISTRATION_SOURCE_CODE
85 END as reg_src_code_to,
86 CASE WHEN to_reg.REGISTRATION_REASON_CODE IS NULL AND from_reg.REGISTRATION_REASON_CODE IS NOT NULL
87 THEN from_reg.REGISTRATION_REASON_CODE
88 END as reg_reason_code_to,
89 CASE WHEN to_reg.REP_TAX_AUTHORITY_ID IS NULL AND from_reg.REP_TAX_AUTHORITY_ID IS NOT NULL
90 THEN from_reg.REP_TAX_AUTHORITY_ID
91 END as rep_tax_auth_id_to,
92 CASE WHEN to_reg.COLL_TAX_AUTHORITY_ID IS NULL AND from_reg.COLL_TAX_AUTHORITY_ID IS NOT NULL
93 THEN from_reg.COLL_TAX_AUTHORITY_ID
94 END as coll_tax_auth_id_to
95 FROM zx_registrations from_reg,
96 zx_registrations to_reg
97 WHERE from_reg.PARTY_TAX_PROFILE_ID IN
98 (SELECT party_tax_profile_id
99 FROM zx_party_tax_profile
100 WHERE party_id = p_from_party_id
101 AND party_type_code = 'THIRD_PARTY'
102 )
103 AND to_reg.PARTY_TAX_PROFILE_ID IN
104 (SELECT party_tax_profile_id
105 FROM zx_party_tax_profile
106 WHERE party_id = p_to_party_id
107 AND party_type_code = 'THIRD_PARTY'
108 )
109 AND from_reg.registration_number = to_reg.registration_number
110 AND from_reg.registration_id <> to_reg.registration_id;
111
112 registration_rec registration_number%ROWTYPE;
113
114 -- Logging Infra
115 l_procedure_name CONSTANT VARCHAR2(30) := '.ZX_CUST_REG_MERGE_PVT ';
116 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
117
118 BEGIN
119
120 -- Logging Infra: Setting up runtime level
121 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
122
123 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
124 l_log_msg := l_procedure_name||' (+) ';
125 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
126 END IF;
127
128 x_return_status := FND_API.G_RET_STS_SUCCESS;
129
130 --If it is a Site Merge, nothing to be done. Return the x_to_id.
131
132 IF p_from_fk_id = p_to_fk_id THEN
133 x_to_id := p_from_id;
134 RETURN;
135 END IF;
136
137 IF p_from_fk_id <> p_to_fk_id THEN
138 BEGIN
139
140 arp_message.set_line(G_MODULE_NAME||l_procedure_name||' Updating zx_registrations...');
141
142 FOR registration_rec IN registration_number(p_from_fk_id,p_to_fk_id)
143 LOOP
144 UPDATE zx_registrations
145 SET merged_to_registration_id = registration_rec.registration_id_to,
146 effective_to = SYSDATE,
147 last_update_date = SYSDATE,
148 last_updated_by = G_USER_ID,
149 last_update_login = G_LOGIN_ID,
150 object_version_number = object_version_number+1
151 WHERE registration_id = registration_rec.registration_id_from;
152
153 UPDATE zx_registrations
154 SET effective_from = registration_rec.update_reg_from_date,
155 effective_to = registration_rec.update_reg_to_date,
156 legal_location_id = registration_rec.location_id_to,
157 registration_source_code = registration_rec.reg_src_code_to,
158 registration_reason_code = registration_rec.reg_reason_code_to,
159 rep_tax_authority_id = registration_rec.rep_tax_auth_id_to,
160 coll_tax_authority_id = registration_rec.coll_tax_auth_id_to,
161 last_update_date = SYSDATE,
162 last_updated_by = G_USER_ID,
163 last_update_login = G_LOGIN_ID,
164 object_version_number = object_version_number+1
165 WHERE registration_id = registration_rec.registration_id_to;
166
167 l_count := l_count + sql%rowcount;
168
169 END LOOP;
170
171 arp_message.set_name('AR','AR_ROWS_UPDATED');
172 arp_message.set_token('NUM_ROWS',to_char(l_count));
173
174 EXCEPTION
175 WHEN NO_DATA_FOUND THEN
176 NULL;
177 WHEN OTHERS THEN
178 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
179 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
180 FND_MSG_PUB.ADD;
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
182 END;
183 END IF; -- p_from_fk_id <> p_to_fk_id
184
185 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
186 l_log_msg := l_procedure_name||' (-) ';
187 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
188 END IF;
189
190 END ZX_CUST_REG_MERGE_PVT;
191
192
193
194 PROCEDURE ZX_PTP_MERGE_PVT (
195 p_entity_name in hz_merge_dictionary.entity_name%type,
196 p_from_id in oks_billing_profiles_b.id%type,
197 x_to_id in out nocopy oks_billing_profiles_b.id%type,
198 p_from_fk_id in hz_merge_parties.from_party_id%type,
199 p_to_fk_id in hz_merge_parties.to_party_id%type,
200 p_parent_entity_name in hz_merge_dictionary.parent_entity_name%type,
201 p_batch_id in hz_merge_batch.batch_id%type,
202 p_batch_party_id in hz_merge_party_details.batch_party_id%type,
203 x_return_status out nocopy varchar2)
204 IS
205 -- Enter the procedure variables here. As shown below
206 l_count number(10) := 0;
207 l_ptp_id_from NUMBER;
208 l_ptp_id_to NUMBER;
209 l_code_assignment_id hz_code_assignments.owner_table_name%TYPE;
210
211 CURSOR Party_Tax_Profile(p_fk_id hz_merge_parties.from_party_id%type) IS
212 SELECT Party_Tax_Profile_id
213 FROM zx_party_tax_profile prof
214 WHERE prof.party_id = p_fk_id
215 AND prof.party_type_code = 'THIRD_PARTY';
216
217 from_ptp_rec Party_Tax_Profile%ROWTYPE;
218 to_ptp_rec Party_Tax_Profile%ROWTYPE;
219
220 CURSOR Class_Categories_From(p_ptp_id number) IS
221 SELECT code_assignment_id, class_category, class_code, END_DATE_ACTIVE
222 FROM hz_code_assignments
223 WHERE owner_table_name = 'ZX_PARTY_TAX_PROFILE'
224 AND owner_table_id = p_ptp_id
225 AND NVL(END_DATE_ACTIVE,SYSDATE) >= SYSDATE;
226
227 CURSOR Class_Codes_To (p_ptp_id number,
228 p_class_category hz_code_assignments.class_category%type,
229 p_end_date hz_code_assignments.end_date_active%type,
230 p_class_code hz_code_assignments.class_code%type) IS
231 SELECT DISTINCT class_category, class_code, END_DATE_ACTIVE
232 FROM hz_code_assignments
233 WHERE owner_table_name = 'ZX_PARTY_TAX_PROFILE'
234 AND owner_table_id = p_ptp_id
235 AND NVL(END_DATE_ACTIVE,SYSDATE) >= NVL(p_end_date,SYSDATE)
236 AND class_category = p_class_category
237 AND class_code = p_class_code
238 GROUP BY class_category, class_code, END_DATE_ACTIVE;
239
240 Class_Codes_To_Rec Class_Codes_To%ROWTYPE;
241
242 -- Logging Infra
243 l_procedure_name CONSTANT VARCHAR2(30) := '.ZX_PTP_MERGE_PVT ';
244 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
245
246 BEGIN
247
248 -- Logging Infra: Setting up runtime level
249 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
250
251 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
252 l_log_msg := l_procedure_name||' (+) ';
253 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
254 END IF;
255
256 x_return_status := FND_API.G_RET_STS_SUCCESS;
257
258 --If it is a Site Merge, nothing to be done. Return the x_to_id.
259
260 IF p_from_fk_id = p_to_fk_id THEN
261 x_to_id := p_from_id;
262 RETURN;
263 END IF;
264
265 IF p_from_fk_id <> p_to_fk_id THEN
266
267 BEGIN
268 arp_message.set_line(G_MODULE_NAME||l_procedure_name||' Updating zx_party_tax_profile...');
269
270 OPEN Party_Tax_Profile(p_from_fk_id);
271 FETCH Party_Tax_Profile INTO from_ptp_rec;
272 IF Party_Tax_Profile%FOUND THEN
273 l_ptp_id_from := from_ptp_rec.Party_Tax_Profile_id;
274 END IF;
275 CLOSE Party_Tax_Profile;
276
277 OPEN Party_Tax_Profile(p_to_fk_id);
278 FETCH Party_Tax_Profile INTO to_ptp_rec;
279 IF Party_Tax_Profile%FOUND THEN
280 l_ptp_id_to := to_ptp_rec.Party_Tax_Profile_id;
281 END IF;
282 CLOSE Party_Tax_Profile;
283
284 UPDATE zx_party_tax_profile
285 set merged_to_ptp_id = l_ptp_id_to,
286 merged_status_code = 'MERGED',
287 last_update_date = SYSDATE,
288 last_updated_by = G_USER_ID,
289 last_update_login = G_LOGIN_ID,
290 object_version_number = object_version_number+1
291 WHERE Party_Tax_Profile_id = l_ptp_id_from;
292
293 l_count := sql%rowcount;
294 arp_message.set_name('AR','AR_ROWS_UPDATED');
295 arp_message.set_token('NUM_ROWS',to_char(l_count));
296
297 FOR code_assig IN Class_Categories_From (l_ptp_id_from) LOOP
298 OPEN Class_Codes_To(l_ptp_id_to
299 ,code_assig.class_category
300 ,code_assig.END_DATE_ACTIVE
301 ,code_assig.class_code);
302 FETCH Class_Codes_To INTO Class_Codes_To_Rec.Class_Category,
303 Class_Codes_To_Rec.class_code,
304 Class_Codes_To_Rec.END_DATE_ACTIVE;
305 IF Class_Codes_To%NOTFOUND THEN
306 UPDATE hz_code_assignments
307 set owner_table_id = l_ptp_id_to,
308 last_update_date = SYSDATE,
309 last_updated_by = G_USER_ID,
310 last_update_login = G_LOGIN_ID,
311 object_version_number = object_version_number+1
312 WHERE code_assignment_id = code_assig.code_assignment_id;
313 END IF;
314 CLOSE Class_Codes_To;
315 END Loop;
316
317 EXCEPTION
318 WHEN NO_DATA_FOUND THEN
319 NULL;
320 WHEN OTHERS THEN
321 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
322 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
323 FND_MSG_PUB.ADD;
324 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
325 END;
326 END IF;
327
328 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
329 l_log_msg := l_procedure_name||' (-) ';
330 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
331 END IF;
332
333 end ZX_PTP_MERGE_PVT;
334
335
336
337 PROCEDURE ZX_TAX_AUTH_MERGE_PVT (
338 p_entity_name in hz_merge_dictionary.entity_name%type,
339 p_ptp_id_from in NUMBER,
340 p_ptp_id_to in NUMBER,
341 x_to_id in out nocopy oks_billing_profiles_b.id%type,
342 p_from_fk_id in hz_merge_parties.from_party_id%type,
343 p_to_fk_id in hz_merge_parties.to_party_id%type,
344 x_return_status out nocopy varchar2)
345 IS
346 -- Enter the procedure variables here. As shown below
347 l_count number(10) := 0;
348
349 -- Logging Infra
350 l_procedure_name CONSTANT VARCHAR2(30) := '.ZX_TAX_AUTH_MERGE_PVT ';
351 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
352
353 BEGIN
354
355 -- Logging Infra: Setting up runtime level
356 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
357
358 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
359 l_log_msg := l_procedure_name||' (+) ';
360 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
361 END IF;
362
363 x_return_status := FND_API.G_RET_STS_SUCCESS;
364
365 --If it is a Site Merge, nothing to be done. Return the x_to_id.
366
367 if p_from_fk_id = p_to_fk_id THEN
368 return;
369 END IF;
370
371 if p_from_fk_id <> p_to_fk_id THEN
372 BEGIN
373
374 arp_message.set_line(G_MODULE_NAME||l_procedure_name||' Updating zx_registrations for tax Authorities...');
375
376 UPDATE zx_registrations
377 set TAX_AUTHORITY_ID = p_ptp_id_to,
378 last_update_date = SYSDATE,
379 last_updated_by = G_USER_ID,
380 last_update_login = G_LOGIN_ID,
381 object_version_number = object_version_number+1
382 WHERE TAX_AUTHORITY_ID = p_ptp_id_from;
383
384 UPDATE zx_registrations
385 set REP_TAX_AUTHORITY_ID = p_ptp_id_to,
386 last_update_date = SYSDATE,
387 last_updated_by = G_USER_ID,
388 last_update_login = G_LOGIN_ID,
389 object_version_number = object_version_number+1
390 WHERE REP_TAX_AUTHORITY_ID = p_ptp_id_from;
391
392 UPDATE zx_registrations
393 set COLL_TAX_AUTHORITY_ID = p_ptp_id_to,
394 last_update_date = SYSDATE,
395 last_updated_by = G_USER_ID,
396 last_update_login = G_LOGIN_ID,
397 object_version_number = object_version_number+1
398 WHERE COLL_TAX_AUTHORITY_ID = p_ptp_id_from;
399
400 arp_message.set_line(G_MODULE_NAME||l_procedure_name||' Updating zx_taxes_b for tax Authorities...');
401
402 UPDATE zx_taxes_b
403 set REP_TAX_AUTHORITY_ID = p_ptp_id_to,
404 last_update_date = SYSDATE,
405 last_updated_by = G_USER_ID,
406 last_update_login = G_LOGIN_ID,
407 object_version_number = object_version_number+1
408 WHERE REP_TAX_AUTHORITY_ID = p_ptp_id_from;
409
410 UPDATE zx_taxes_b
411 set COLL_TAX_AUTHORITY_ID = p_ptp_id_to,
412 last_update_date = SYSDATE,
413 last_updated_by = G_USER_ID,
414 last_update_login = G_LOGIN_ID,
415 object_version_number = object_version_number+1
416 WHERE COLL_TAX_AUTHORITY_ID = p_ptp_id_from;
417
418 arp_message.set_line(G_MODULE_NAME||l_procedure_name||' Updating zx_regimes_b for tax Authorities...');
419
420 UPDATE zx_regimes_b
421 set REP_TAX_AUTHORITY_ID = p_ptp_id_to,
422 last_update_date = SYSDATE,
423 last_updated_by = G_USER_ID,
424 last_update_login = G_LOGIN_ID,
425 object_version_number = object_version_number+1
426 WHERE REP_TAX_AUTHORITY_ID = p_ptp_id_from;
427
428 UPDATE zx_regimes_b
429 set COLL_TAX_AUTHORITY_ID = p_ptp_id_to,
430 last_update_date = SYSDATE,
431 last_updated_by = G_USER_ID,
432 last_update_login = G_LOGIN_ID,
433 object_version_number = object_version_number+1
434 WHERE COLL_TAX_AUTHORITY_ID = p_ptp_id_from;
435
436 arp_message.set_line(G_MODULE_NAME||l_procedure_name||' Updating zx_jurisdictions_b for tax Authorities...');
437
438 UPDATE zx_jurisdictions_b
439 set REP_TAX_AUTHORITY_ID = p_ptp_id_to,
440 last_update_date = SYSDATE,
441 last_updated_by = G_USER_ID,
442 last_update_login = G_LOGIN_ID,
443 object_version_number = object_version_number+1
444 WHERE REP_TAX_AUTHORITY_ID = p_ptp_id_from;
445
446 UPDATE zx_jurisdictions_b
447 set COLL_TAX_AUTHORITY_ID = p_ptp_id_to,
448 last_update_date = SYSDATE,
449 last_updated_by = G_USER_ID,
450 last_update_login = G_LOGIN_ID,
451 object_version_number = object_version_number+1
452 WHERE COLL_TAX_AUTHORITY_ID = p_ptp_id_from;
453
454 arp_message.set_line(G_MODULE_NAME||l_procedure_name||' Updating zx_jurisdictions_b for tax Authorities...');
455
456 UPDATE zx_exemptions
457 set ISSUING_TAX_AUTHORITY_ID = p_ptp_id_to,
458 last_update_date = SYSDATE,
459 last_updated_by = G_USER_ID,
460 last_update_login = G_LOGIN_ID,
461 object_version_number = object_version_number+1
462 WHERE ISSUING_TAX_AUTHORITY_ID = p_ptp_id_from;
463
464 l_count := sql%rowcount;
465 arp_message.set_name('AR','AR_ROWS_UPDATED');
466 arp_message.set_token('NUM_ROWS',to_char(l_count));
467
468 EXCEPTION
469 WHEN NO_DATA_FOUND THEN
470 NULL;
471 WHEN OTHERS THEN
472 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
473 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
474 FND_MSG_PUB.ADD;
475 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
476 END;
477 END IF;
478
479 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
480 l_log_msg := l_procedure_name||' (-) ';
481 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
482 END IF;
486
483
484 end ZX_TAX_AUTH_MERGE_PVT;
485
487
488 PROCEDURE ZX_EXEMPTIONS_PVT (
489 p_entity_name in hz_merge_dictionary.entity_name%type,
490 p_from_id in oks_billing_profiles_b.id%type,
491 x_to_id in out nocopy oks_billing_profiles_b.id%type,
492 p_from_fk_id in hz_merge_parties.from_party_id%type,
493 p_to_fk_id in hz_merge_parties.to_party_id%type,
494 p_parent_entity_name in hz_merge_dictionary.parent_entity_name%type,
495 p_batch_id in hz_merge_batch.batch_id%type,
496 p_batch_party_id in hz_merge_party_details.batch_party_id%type,
497 x_return_status out nocopy varchar2)
498
499 IS
500
501 cursor exe_number(p_fk_id hz_merge_parties.from_party_id%type) IS
502 SELECT TAX_EXEMPTION_ID
503 , EXEMPT_CERTIFICATE_NUMBER
504 , effective_from
505 , effective_to
506 , EXEMPTION_TYPE_CODE
507 , EXEMPTION_STATUS_CODE
508 , TAX_REGIME_CODE
509 , TAX_RATE_CODE
510 , CUST_ACCOUNT_ID
511 , SITE_USE_ID
512 , EXEMPT_REASON_CODE
513 , CONTENT_OWNER_ID
514 , TAX
515 , TAX_JURISDICTION_ID
516 , PRODUCT_ID
517 , TAX_STATUS_CODE
518 FROM zx_exemptions exemp
519 WHERE party_tax_profile_id = p_fk_id;
520
521 CURSOR to_exemption (l_certificate_number varchar2,
522 l_effective_from zx_exemptions.effective_from%type,
523 l_effective_to zx_exemptions.effective_to%type,
524 l_type_code zx_exemptions.exemption_type_code%type,
525 l_status_code zx_exemptions.exemption_status_code%type,
526 l_tax_regime_code zx_exemptions.tax_regime_code%type,
527 l_tax_rate_code zx_exemptions.tax_rate_code%type,
528 l_cust_account_id zx_exemptions.cust_account_id%type,
529 l_site_use_id zx_exemptions.site_use_id%type,
530 l_exempt_reason_code zx_exemptions.exempt_reason_code%type,
531 l_content_owner_id zx_exemptions.content_owner_id%type,
532 l_tax zx_exemptions.tax%type,
533 l_tax_jurisdiction_id zx_exemptions.tax_jurisdiction_id%type,
534 l_product_id zx_exemptions.product_id%type,
535 l_tax_status_code zx_exemptions.tax_status_code%type)
536 IS
537 SELECT TAX_EXEMPTION_ID
538 FROM zx_exemptions exemp
539 WHERE party_tax_profile_id = p_to_fk_id
540 AND exempt_certificate_number = l_certificate_number
541 AND effective_from = l_effective_from
542 AND NVL(effective_to,l_effective_to) = l_effective_to
543 AND exemption_type_code = l_type_code
544 AND exemption_status_code = l_status_code
545 AND tax_regime_code = l_tax_regime_code
546 AND tax_rate_code = l_tax_rate_code
547 AND cust_account_id = l_cust_account_id
548 AND site_use_id = l_site_use_id
549 AND exempt_reason_code = l_exempt_reason_code
550 AND content_owner_id = l_content_owner_id
551 AND tax = l_tax
552 AND tax_jurisdiction_id = l_tax_jurisdiction_id
553 AND tax_status_code = l_tax_status_code
554 AND (product_id is null or product_id = l_product_id)
555 AND duplicate_exemption = 0;
556
557 to_exemption_rec to_exemption%ROWTYPE;
558
559 -- Enter the procedure variables here.
560 l_count number(10) := 0;
561
562 -- Logging Infra
563 l_procedure_name CONSTANT VARCHAR2(30) := '.ZX_EXEMPTIONS_PVT ';
564 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
565
566 BEGIN
567
568 -- Logging Infra: Setting up runtime level
569 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
570
571 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
572 l_log_msg := l_procedure_name||' (+) ';
573 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
574 END IF;
575
576 x_return_status := FND_API.G_RET_STS_SUCCESS;
577
578 --If it is a Site Merge, nothing to be done. Return the x_to_id.
579
580 if p_from_fk_id = p_to_fk_id THEN
581 x_to_id := p_from_id;
582 return;
583 END IF;
584
585 if p_from_fk_id <> p_to_fk_id THEN
586 BEGIN
587
588 arp_message.set_line(G_MODULE_NAME||l_procedure_name||' Updating exemptions...');
589 FOR rec_exe IN exe_number(p_from_fk_id)
590 Loop
591 OPEN to_exemption(rec_exe.exempt_certificate_number
592 ,rec_exe.effective_from
593 ,rec_exe.effective_to
594 ,rec_exe.exemption_type_code
595 ,rec_exe.exemption_status_code
596 ,rec_exe.tax_regime_code
597 ,rec_exe.tax_rate_code
598 ,rec_exe.cust_account_id
599 ,rec_exe.site_use_id
600 ,rec_exe.exempt_reason_code
601 ,rec_exe.content_owner_id
602 ,rec_exe.tax
603 ,rec_exe.tax_jurisdiction_id
604 ,rec_exe.tax_status_code
605 ,rec_exe.product_id);
606 LOOP
607 FETCH to_exemption INTO to_exemption_rec;
608 IF to_exemption%NOTFOUND THEN
609 UPDATE zx_exemptions
610 set --merged_to_exemption_id = l_exemption_id_to,
611 party_tax_profile_id = p_to_fk_id,
612 last_update_date = SYSDATE,
613 last_updated_by = G_USER_ID,
614 last_update_login = G_LOGIN_ID,
615 object_version_number = object_version_number+1
616 WHERE TAX_EXEMPTION_ID = rec_exe.tax_exemption_id;
617 End IF;
618
619 l_count := l_count+sql%rowcount;
620 End Loop;
621 CLOSE to_exemption;
622
623 End Loop;
624
625 arp_message.set_name('AR','AR_ROWS_UPDATED');
626 arp_message.set_token('NUM_ROWS',to_char(l_count));
627
628 EXCEPTION
629 WHEN NO_DATA_FOUND THEN
630 NULL;
631 WHEN OTHERS THEN
632 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
633 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
634 FND_MSG_PUB.ADD;
635 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
636 END;
637 END IF;
638
639 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
640 l_log_msg := l_procedure_name||' (-) ';
641 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
642 END IF;
643 end ZX_EXEMPTIONS_PVT;
644
645 PROCEDURE ZX_CUSTOMER_VETO_PVT (
646 p_ptp_id_from in number,
647 p_ptp_id_to in number,
648 x_merge_yn out nocopy VARCHAR2,
649 p_from_fk_id in hz_merge_parties.from_party_id%type,
650 p_to_fk_id in hz_merge_parties.to_party_id%type,
651 x_return_status out nocopy varchar2)
652 IS
653 -- Enter the procedure variables here. As shown below
654 l_ptp_id_from NUMBER;
655 l_ptp_id_to NUMBER;
656 l_calculate_tax_from VARCHAR2(1);
657 l_calculate_tax_to VARCHAR2(1);
658 l_code_assignment_id hz_code_assignments.owner_table_name%TYPE;
659 -- l_hash_key BINARY_INTEGER;
660
661 -- l_reg_attr_tbl_from reg_attr_tbl_type;
662 -- l_reg_attr_tbl_to reg_attr_tbl_type;
663 -- TABLE_SIZE BINARY_INTEGER := 2048;
664 -- class_category_rec class_category_rec_type;
665 -- class_category_tbl class_category_tbl_type;
666
667 cursor Calculate_Tax_Flag(p_ptp_id NUMBER) IS
668 SELECT PROCESS_FOR_APPLICABILITY_FLAG
669 FROM zx_party_tax_profile ptp
670 WHERE ptp.party_tax_profile_id = p_ptp_id;
671
672 from_calc_tax_rec Calculate_Tax_Flag%ROWTYPE;
673 to_calc_tax_rec Calculate_Tax_Flag%ROWTYPE;
674
675 -- cursor Registration_Attributes(p_ptp_id number) IS
676 -- select a.REGISTRATION_TYPE_CODE, a.REGISTRATION_NUMBER, a.ROUNDING_RULE_CODE,
677 -- a.SELF_ASSESS_FLAG, a.INCLUSIVE_TAX_FLAG
678 -- from zx_registrations a, zx_party_tax_profile b
679 -- where b.party_tax_profile_id = p_ptp_id
680 -- and a.party_tax_profile_id = b.party_tax_profile_id;
681
682 -- Reg_Attr_From_Rec Registration_Attributes%ROWTYPE;
683 -- Reg_Attr_To_Rec Registration_Attributes%ROWTYPE;
684
685 cursor Registration_Attributes(p_ptp_id_1 number,
686 p_ptp_id_2 number) IS
687 SELECT a.REGISTRATION_TYPE_CODE,
688 a.REGISTRATION_NUMBER,
689 a.ROUNDING_RULE_CODE,
690 NVL(a.SELF_ASSESS_FLAG, 'N') SELF_ASSESS_FLAG,
691 NVL(a.INCLUSIVE_TAX_FLAG, 'N') INCLUSIVE_TAX_FLAG,
692 a.TAX_REGIME_CODE,
693 a.TAX,
694 b.REP_REGISTRATION_NUMBER
695 FROM zx_registrations a, zx_party_tax_profile b
696 WHERE b.party_tax_profile_id = p_ptp_id_1
697 AND a.party_tax_profile_id = b.party_tax_profile_id
698 AND SYSDATE BETWEEN a.effective_from AND NVL(a.effective_to, SYSDATE)
699 MINUS
700 SELECT a.REGISTRATION_TYPE_CODE,
701 a.REGISTRATION_NUMBER,
702 a.ROUNDING_RULE_CODE,
703 NVL(a.SELF_ASSESS_FLAG, 'N') SELF_ASSESS_FLAG,
704 NVL(a.INCLUSIVE_TAX_FLAG, 'N') INCLUSIVE_TAX_FLAG,
705 a.TAX_REGIME_CODE,
706 a.TAX,
707 b.REP_REGISTRATION_NUMBER
708 FROM zx_registrations a, zx_party_tax_profile b
709 WHERE b.party_tax_profile_id = p_ptp_id_2
710 AND a.party_tax_profile_id = b.party_tax_profile_id
711 AND SYSDATE BETWEEN a.effective_from AND NVL(a.effective_to, SYSDATE);
712
713 cursor Registration_Attributes_Exist(p_ptp_id number) IS
714 SELECT 1
715 FROM zx_registrations a, zx_party_tax_profile b
716 WHERE b.party_tax_profile_id = p_ptp_id
717 AND a.party_tax_profile_id = b.party_tax_profile_id
718 AND SYSDATE BETWEEN a.effective_from AND NVL(a.effective_to, SYSDATE);
719
720 Reg_Attr_Rec Registration_Attributes%ROWTYPE;
721 Reg_Attr_Rec_From Registration_Attributes%ROWTYPE;
722
723 -- Logging Infra
724 l_procedure_name CONSTANT VARCHAR2(30) := '.ZX_CUSTOMER_VETO_PVT ';
725 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
726 l_dummy_number NUMBER;
727
728 BEGIN
729
730 -- Logging Infra: Procedure level
731 -- Logging Infra: Setting up runtime level
732 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
733
734 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
735 l_log_msg := l_procedure_name||' (+) ';
736 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
737 END IF;
738
739 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
740 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name,
741 'Parameters to the procedure are as follows :- '||
742 'p_ptp_id_from : '||p_ptp_id_from||' , p_ptp_id_to : '||p_ptp_id_to||
743 'p_from_fk_id : '||p_from_fk_id||' , p_to_fk_id : '||p_to_fk_id
744 );
745 END IF;
746
747 x_return_status := FND_API.G_RET_STS_SUCCESS;
748 --
749 -- Tax Applicability it is just for Suppliers
750 -- Commenting as per Nigel Updates 3511846
751 --
752 -- OPEN Calculate_Tax_Flag(p_ptp_id_from);
753 -- FETCH Calculate_Tax_Flag INTO from_calc_tax_rec;
754 -- IF Calculate_Tax_Flag%FOUND THEN
755 -- l_calculate_tax_from :=from_calc_tax_rec.PROCESS_FOR_APPLICABILITY_FLAG;
756 -- END IF;
757 -- CLOSE Calculate_Tax_Flag;
758 --
759 -- OPEN Calculate_Tax_Flag(p_ptp_id_to);
760 -- FETCH Calculate_Tax_Flag INTO to_calc_tax_rec;
761 -- IF Calculate_Tax_Flag%FOUND THEN
762 -- l_calculate_tax_to :=to_calc_tax_rec.PROCESS_FOR_APPLICABILITY_FLAG;
763 -- END IF;
764 -- CLOSE Calculate_Tax_Flag;
765 --
766 -- if(l_calculate_tax_from <> l_calculate_tax_to) THEN
767 -- arp_message.set_line('Parties '||p_from_fk_id||' and '||p_to_fk_id||' cannot be merged
768 -- as Calculate Tax Flag has different Values');
769 -- x_merge_yn := 'N';
770 -- end if;
771 --
772
773 -- FOR REC in Registration_Attributes(p_ptp_id_to) LOOP
774 -- 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);
775 -- l_reg_attr_tbl_to(l_hash_key) := REC;
776 -- END LOOP;
777
778 -- FOR REC in Registration_Attributes(p_ptp_id_from) LOOP
782 -- ELSE
779 -- 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);
780 -- if(l_reg_attr_tbl_to.exists(l_hash_key)) THEN
781 -- x_merge_yn := 'Y';
783 -- arp_message.set_line('Parties '||p_from_fk_id||' and '||p_to_fk_id||
784 -- ' cannot be merged as Registration Attributes have different Values');
785 -- x_merge_yn := 'N';
786 -- END IF;
787
788 -- l_reg_attr_tbl_from(l_hash_key) := REC;
789 -- END LOOP;
790
791 OPEN Registration_Attributes (p_ptp_id_from,p_ptp_id_to); -- (A-B)
792 FETCH Registration_Attributes into Reg_Attr_Rec;
793
794 IF Registration_Attributes%FOUND THEN
795 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
796 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name,
797 'Parties '||p_ptp_id_from||' and '||p_ptp_id_to||
798 ' cannot be merged as Registration Attributes have different Values (A-B)'
799 );
800 END IF;
801 arp_message.set_line('Parties '||p_from_fk_id||' and '||p_to_fk_id||
802 ' cannot be merged as Registration Attributes have different Values (A-B)');
803 x_merge_yn := 'N';
804 CLOSE Registration_Attributes;
805 return;
806 END IF;
807
808 CLOSE Registration_Attributes;
809
810 OPEN Registration_Attributes (p_ptp_id_to, p_ptp_id_from); -- (B-A)
811 FETCH Registration_Attributes into Reg_Attr_Rec;
812
813 IF Registration_Attributes%FOUND THEN
814 OPEN Registration_Attributes_Exist (p_ptp_id_from);
815 FETCH Registration_Attributes_Exist into l_dummy_number;
816 IF Registration_Attributes_Exist%FOUND THEN
817 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
818 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name,
819 'Parties '||p_ptp_id_to||' and '||p_ptp_id_from||
820 ' cannot be merged as Registration Attributes have different Values (B-A)'
821 );
822 END IF;
823 arp_message.set_line('Parties '||p_to_fk_id ||' and '||p_from_fk_id||
824 ' cannot be merged as Registration Attributes have different Values (B-A)');
825 x_merge_yn := 'N';
826 CLOSE Registration_Attributes_Exist;
827 return;
828 END IF;
829 CLOSE Registration_Attributes_Exist;
830 END IF;
831
832 CLOSE Registration_Attributes;
833
834 x_merge_yn := 'Y';
835
836 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
837 l_log_msg := l_procedure_name||' (-) ';
838 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
839 END IF;
840
841 EXCEPTION
842 WHEN NO_DATA_FOUND THEN
843 NULL;
844 WHEN OTHERS THEN
845 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
846 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
847 FND_MSG_PUB.ADD;
848 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
849 end ZX_CUSTOMER_VETO_PVT;
850
851
852 PROCEDURE ZX_MERGE (
853 p_entity_name in hz_merge_dictionary.entity_name%type,
854 p_from_id in oks_billing_profiles_b.id%type,
855 x_to_id in out nocopy oks_billing_profiles_b.id%type,
856 p_from_fk_id in hz_merge_parties.from_party_id%type,
857 p_to_fk_id in hz_merge_parties.to_party_id%type,
858 p_parent_entity_name in hz_merge_dictionary.parent_entity_name%type,
859 p_batch_id in hz_merge_batch.batch_id%type,
860 p_batch_party_id in hz_merge_party_details.batch_party_id%type,
861 x_return_status out nocopy varchar2)
862 IS
863 -- Enter the procedure variables here. As shown below
864 l_ptp_id_from NUMBER;
865 l_ptp_id_to NUMBER;
866 l_code_assignment_id hz_code_assignments.owner_table_name%TYPE;
867 l_party_type_from VARCHAR2(30);
868 l_party_type_to VARCHAR2(30);
869 l_merge_yn VARCHAR2(1);
870
871
872 cursor Party_Tax_Profile(p_fk_id hz_merge_parties.from_party_id%type) IS
873 SELECT Party_Tax_Profile_id, party_type_code
874 FROM zx_party_tax_profile prof
875 WHERE prof.party_id = p_fk_id
876 AND prof.party_type_code = 'THIRD_PARTY';
877
878 from_ptp_rec Party_Tax_Profile%ROWTYPE;
879 to_ptp_rec Party_Tax_Profile%ROWTYPE;
880
881 -- Logging Infra
882 l_procedure_name CONSTANT VARCHAR2(30) := '.ZX_MERGE ';
883 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
884
885 BEGIN
886
887 -- Logging Infra: Setting up runtime level
888 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
889
890 -- Logging Infra: Procedure level
891 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
892 l_log_msg := l_procedure_name||' (+) ';
893 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
894 END IF;
895
896 x_return_status := FND_API.G_RET_STS_SUCCESS;
897
898 --If it is a Site Merge, nothing to be done. Return the x_to_id.
899
900 if p_from_fk_id = p_to_fk_id THEN
901 x_to_id := p_from_id;
902 x_return_status := 'E';
903 return;
904 END IF;
905
906 if p_from_fk_id <> p_to_fk_id THEN
907 BEGIN
908
909 arp_message.set_line(G_MODULE_NAME||l_procedure_name||' Identifying Party Type...');
910
911 OPEN Party_Tax_Profile(p_from_fk_id);
912 FETCH Party_Tax_Profile INTO from_ptp_rec;
913 IF Party_Tax_Profile%FOUND THEN
914 l_ptp_id_from := from_ptp_rec.Party_Tax_Profile_id;
915 l_party_type_from := from_ptp_rec.Party_type_code;
916 END IF;
917 CLOSE Party_Tax_Profile;
918
919 OPEN Party_Tax_Profile(p_to_fk_id);
920 FETCH Party_Tax_Profile INTO from_ptp_rec;
921 IF Party_Tax_Profile%FOUND THEN
922 l_ptp_id_to := from_ptp_rec.Party_Tax_Profile_id;
923 l_party_type_to := from_ptp_rec.Party_type_code;
924 END IF;
925 CLOSE Party_Tax_Profile;
926
927 if(l_party_type_from <> l_party_type_to) THEN
928 arp_message.set_line(G_MODULE_NAME||l_procedure_name||' Cannot Merge Parties, Party Types are different...');
929 x_return_status := 'E';
930 return;
931 else
932 if(l_party_type_from = 'THIRD_PARTY') THEN
933 ZX_CUSTOMER_VETO_PVT(
934 p_ptp_id_from => l_ptp_id_from,
935 p_ptp_id_to => l_ptp_id_to,
936 x_merge_yn => l_merge_yn,
937 p_from_fk_id => p_from_fk_id,
938 p_to_fk_id => p_to_fk_id,
939 x_return_status => x_return_status);
940
941 -- As per Nigel comments bug 3511846
942 -- ZX_FISCAL_CLASS_VETO(
943 -- p_ptp_id_from => l_ptp_id_from,
944 -- p_ptp_id_to => l_ptp_id_to,
945 -- x_merge_yn => l_merge_yn,
946 -- p_from_fk_id => p_from_fk_id,
947 -- p_to_fk_id => p_to_fk_id,
948 -- x_return_status => x_return_status);
949 --
950
951 IF (l_merge_yn = 'N') THEN
952 x_return_status := 'E';
953 ELSE
954 ZX_PTP_MERGE_PVT(
955 p_entity_name => p_entity_name,
956 p_from_id => p_from_id,
957 x_to_id => x_to_id,
958 p_from_fk_id => p_from_fk_id,
959 p_to_fk_id => p_to_fk_id,
960 p_parent_entity_name=> p_parent_entity_name,
961 p_batch_id => p_batch_id,
962 p_batch_party_id => p_batch_party_id,
963 x_return_status => x_return_status);
964
965 ZX_CUST_REG_MERGE_PVT(
966 p_entity_name => p_entity_name,
967 p_from_id => p_from_id,
968 x_to_id => x_to_id,
969 p_from_fk_id => p_from_fk_id,
970 p_to_fk_id => p_to_fk_id,
971 p_parent_entity_name=> p_parent_entity_name,
972 p_batch_id => p_batch_id,
973 p_batch_party_id => p_batch_party_id,
974 x_return_status => x_return_status);
975
976 ZX_EXEMPTIONS_PVT(
977 p_entity_name => p_entity_name,
978 p_from_id => p_from_id,
979 x_to_id => x_to_id,
980 p_from_fk_id => l_ptp_id_from,
981 p_to_fk_id => l_ptp_id_from,
982 p_parent_entity_name=> p_parent_entity_name,
983 p_batch_id => p_batch_id,
984 p_batch_party_id => p_batch_party_id,
985 x_return_status => x_return_status);
986 END IF;
987 ELSIF(l_party_type_from = 'TAX_AUTHORITY') THEN
988 ZX_TAX_AUTH_MERGE_PVT (
989 p_entity_name => p_entity_name,
990 p_ptp_id_from => l_ptp_id_from,
991 p_ptp_id_to => l_ptp_id_to,
992 x_to_id => x_to_id,
993 p_from_fk_id => p_from_fk_id,
994 p_to_fk_id => p_to_fk_id,
995 x_return_status => x_return_status);
996 END IF;
997 END IF;
998
999 EXCEPTION
1000 WHEN NO_DATA_FOUND THEN
1001 NULL;
1002 WHEN OTHERS THEN
1003 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
1004 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1005 FND_MSG_PUB.ADD;
1006 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1007 END;
1008 END IF;
1009
1010 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1011 l_log_msg := l_procedure_name||' (-) ';
1012 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1013 END IF;
1014
1015 end ZX_MERGE;
1016
1017 ------------------------------
1018 -- Merge PTP (Bulk Call)
1019 ------------------------------
1020 PROCEDURE MERGE_PTP_BULK
1021 (request_id IN NUMBER,
1022 set_number IN NUMBER,
1023 process_mode IN VARCHAR2
1024 ) IS
1025 -- Logging Infra
1026 l_procedure_name CONSTANT VARCHAR2(30) := '.MERGE_PTP_BULK ';
1027 l_prog_appl_id NUMBER;
1028 l_conc_program_id NUMBER;
1029 l_request_id NUMBER;
1030
1031 TYPE bulk_number_type is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1032 l_tbl_from_party_site_id bulk_number_type;
1033 l_tbl_to_party_site_id bulk_number_type;
1034 l_tbl_from_ptp_id bulk_number_type;
1035 l_tbl_to_ptp_id bulk_number_type;
1036 l_tbl_to_acct_id bulk_number_type;
1037 l_tbl_to_acct_site_id bulk_number_type;
1038
1039
1040 BEGIN
1041 arp_message.set_line(G_MODULE_NAME||l_procedure_name||'Begin with param request id: '||request_id);
1042 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1043 -- Logging Infra: Procedure level
1044 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1045 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
1046 'Begin with param request id: '||request_id);
1047 END IF;
1048 l_request_id := request_id;
1049 l_prog_appl_id := FND_GLOBAL.PROG_APPL_ID;
1050 l_conc_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
1051 --
1052 -- Processing for PTP records start
1053 --
1054 SELECT from_party_site_id,
1055 to_party_site_id,
1056 party_tax_profile_id,
1057 zx_party_tax_profile_s.nextval,
1058 cust_account_id,
1059 cust_acct_site_id
1060 BULK COLLECT INTO l_tbl_from_party_site_id,
1061 l_tbl_to_party_site_id,
1062 l_tbl_from_ptp_id,
1063 l_tbl_to_ptp_id,
1064 l_tbl_to_acct_id,
1065 l_tbl_to_acct_site_id
1066 FROM (SELECT cas.party_site_id from_party_site_id,
1067 cas2.party_site_id to_party_site_id,
1068 ptp.party_tax_profile_id,
1069 cas2.cust_account_id,
1070 cas2.cust_acct_site_id,
1071 row_number() over (partition by cas.party_site_id,
1072 cas2.party_site_id,
1073 cas2.cust_account_id,
1074 cas2.cust_acct_site_id
1075 order by rm.customer_site_id
1076 ) as party_site_num
1077 FROM RA_CUSTOMER_MERGES rm,
1078 HZ_CUST_ACCT_SITES_ALL cas,
1079 HZ_CUST_ACCT_SITES_ALL cas2,
1080 ZX_PARTY_TAX_PROFILE ptp
1081 WHERE rm.request_id = l_request_id
1082 AND rm.duplicate_address_id = cas.cust_acct_site_id
1083 AND rm.customer_address_id = cas2.cust_acct_site_id
1084 AND ptp.party_id = cas.party_site_id
1085 AND ptp.party_type_code = 'THIRD_PARTY_SITE'
1086 )
1087 WHERE party_site_num = 1;
1088
1089 arp_message.set_line(G_MODULE_NAME||l_procedure_name||'from party id - to party id - ptp id');
1090 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1091 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name,'from party id - to party id - ptp id');
1092 END IF;
1093
1094 for i in 1..l_tbl_to_ptp_id.count LOOP
1095 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1096 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name,
1097 l_tbl_from_party_site_id(i)||' - '|| l_tbl_to_party_site_id(i)||' - '||l_tbl_to_ptp_id(i));
1098 END IF;
1099 arp_message.set_line(G_MODULE_NAME||l_procedure_name||l_tbl_from_party_site_id(i)||' - '|| l_tbl_to_party_site_id(i)||' - '||l_tbl_to_ptp_id(i));
1100 END LOOP;
1101
1102 IF l_tbl_from_ptp_id.count > 0 THEN
1103 --
1104 -- inserting PTP records for the new site
1105 --
1106 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1107 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name,
1108 'Inserting party tax profile records');
1109 END IF;
1110 arp_message.set_line(G_MODULE_NAME||l_procedure_name||'Inserting party tax profile records');
1111 FORALL i IN 1..l_tbl_to_ptp_id.count
1112 INSERT INTO ZX_PARTY_TAX_PROFILE
1113 (party_type_code
1114 ,supplier_flag
1115 ,customer_flag
1116 ,site_flag
1117 ,process_for_applicability_flag
1118 ,rounding_level_code
1119 ,rounding_rule_code
1120 ,withholding_start_date
1121 ,inclusive_tax_flag
1122 ,allow_awt_flag
1123 ,use_le_as_subscriber_flag
1124 ,legal_establishment_flag
1125 ,first_party_le_flag
1126 ,reporting_authority_flag
1127 ,collecting_authority_flag
1128 ,provider_type_code
1129 ,create_awt_dists_type_code
1130 ,create_awt_invoices_type_code
1131 ,tax_classification_code
1132 ,self_assess_flag
1133 ,allow_offset_tax_flag
1134 ,effective_from_use_le
1135 ,record_type_code
1136 ,created_by
1137 ,creation_date
1138 ,last_updated_by
1139 ,last_update_date
1140 ,last_update_login
1141 ,request_id
1142 ,program_application_id
1143 ,program_id
1144 ,attribute1
1145 ,attribute2
1146 ,attribute3
1147 ,attribute4
1148 ,attribute5
1149 ,attribute6
1150 ,attribute7
1151 ,attribute8
1152 ,attribute9
1153 ,attribute10
1154 ,attribute11
1155 ,attribute12
1156 ,attribute13
1157 ,attribute14
1158 ,attribute15
1159 ,attribute_category
1160 ,program_login_id
1161 ,party_tax_profile_id
1162 ,party_id
1163 ,rep_registration_number
1164 ,object_version_number
1165 ,registration_type_code
1166 ,country_code
1167 ,merged_to_ptp_id
1168 ,merged_status_code
1169 )
1170 SELECT
1171 a.party_type_code
1172 ,a.supplier_flag
1173 ,a.customer_flag
1174 ,a.site_flag
1175 ,a.process_for_applicability_flag
1176 ,a.rounding_level_code
1177 ,a.rounding_rule_code
1178 ,a.withholding_start_date
1179 ,a.inclusive_tax_flag
1180 ,a.allow_awt_flag
1181 ,a.use_le_as_subscriber_flag
1182 ,a.legal_establishment_flag
1183 ,a.first_party_le_flag
1184 ,a.reporting_authority_flag
1185 ,a.collecting_authority_flag
1186 ,a.provider_type_code
1187 ,a.create_awt_dists_type_code
1188 ,a.create_awt_invoices_type_code
1189 ,a.tax_classification_code
1190 ,a.self_assess_flag
1191 ,a.allow_offset_tax_flag
1192 ,a.effective_from_use_le
1193 ,a.record_type_code
1194 ,G_USER_ID
1195 ,SYSDATE
1196 ,G_LOGIN_ID
1197 ,SYSDATE
1198 ,G_LOGIN_ID
1199 ,l_request_id
1200 ,l_prog_appl_id
1201 ,l_conc_program_id
1202 ,a.attribute1
1203 ,a.attribute2
1204 ,a.attribute3
1205 ,a.attribute4
1206 ,a.attribute5
1207 ,a.attribute6
1208 ,a.attribute7
1209 ,a.attribute8
1210 ,a.attribute9
1211 ,a.attribute10
1212 ,a.attribute11
1213 ,a.attribute12
1214 ,a.attribute13
1215 ,a.attribute14
1216 ,a.attribute15
1217 ,a.attribute_category
1218 ,G_LOGIN_ID
1219 ,l_tbl_to_ptp_id(i)
1220 ,l_tbl_to_party_site_id(i)
1221 ,a.rep_registration_number
1222 ,1
1223 ,a.registration_type_code
1224 ,a.country_code
1225 ,a.merged_to_ptp_id
1226 ,a.merged_status_code
1227 FROM zx_party_tax_profile a
1228 WHERE a.party_tax_profile_id = l_tbl_from_ptp_id(i);
1229 --
1230 -- Processing for PTP records end
1231 -----------------------------------------------------------------
1232 -- Processing for Registration records start
1233 --
1234 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1235 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name,
1236 'Inserting registration records');
1237 END IF;
1238 arp_message.set_line(G_MODULE_NAME||l_procedure_name||'Inserting registration records');
1239 -- fix for bug 14477937 check to avoid if insertion is creating ZX_REGISTRATIONS_U2
1240 FORALL i in 1..l_tbl_from_ptp_id.count
1241 INSERT INTO ZX_REGISTRATIONS
1242 (registration_type_code
1243 ,registration_number
1244 ,validation_rule
1245 ,rounding_rule_code
1246 ,tax_jurisdiction_code
1247 ,self_assess_flag
1248 ,registration_status_code
1249 ,registration_source_code
1250 ,registration_reason_code
1251 ,tax
1252 ,tax_regime_code
1253 ,inclusive_tax_flag
1254 ,has_tax_exemptions_flag
1255 ,effective_from
1256 ,effective_to
1257 ,rep_party_tax_name
1258 ,default_registration_flag
1259 ,bank_account_num
1260 ,legal_location_id
1261 ,record_type_code
1262 ,created_by
1263 ,creation_date
1264 ,last_updated_by
1265 ,last_update_date
1266 ,last_update_login
1267 ,request_id
1268 ,program_application_id
1269 ,program_id
1270 ,attribute1
1271 ,attribute2
1272 ,attribute3
1273 ,attribute4
1274 ,attribute5
1275 ,attribute6
1276 ,attribute7
1277 ,attribute8
1278 ,attribute9
1279 ,attribute10
1280 ,attribute11
1281 ,attribute12
1282 ,attribute13
1283 ,attribute14
1284 ,attribute15
1285 ,attribute_category
1286 ,tax_classification_code
1287 ,program_login_id
1288 ,registration_id
1289 ,tax_authority_id
1290 ,rep_tax_authority_id
1291 ,coll_tax_authority_id
1292 ,party_tax_profile_id
1293 ,legal_registration_id
1294 ,bank_id
1295 ,bank_branch_id
1296 ,account_id
1297 ,account_site_id
1298 ,object_version_number
1299 ,rounding_level_code
1300 ,account_type_code
1301 ,merged_to_registration_id
1302 )
1303 SELECT
1304 registration_type_code
1305 ,registration_number
1306 ,validation_rule
1307 ,rounding_rule_code
1308 ,tax_jurisdiction_code
1309 ,self_assess_flag
1310 ,registration_status_code
1311 ,registration_source_code
1312 ,registration_reason_code
1313 ,tax
1314 ,tax_regime_code
1315 ,inclusive_tax_flag
1316 ,has_tax_exemptions_flag
1317 ,effective_from
1318 ,effective_to
1319 ,rep_party_tax_name
1320 ,default_registration_flag
1321 ,bank_account_num
1322 ,legal_location_id
1323 ,record_type_code
1324 ,G_USER_ID
1325 ,SYSDATE
1326 ,G_USER_ID
1327 ,SYSDATE
1328 ,G_LOGIN_ID
1329 ,l_request_id
1330 ,l_prog_appl_id
1331 ,l_conc_program_id
1332 ,attribute1
1333 ,attribute2
1334 ,attribute3
1335 ,attribute4
1336 ,attribute5
1337 ,attribute6
1338 ,attribute7
1339 ,attribute8
1340 ,attribute9
1341 ,attribute10
1342 ,attribute11
1343 ,attribute12
1344 ,attribute13
1345 ,attribute14
1346 ,attribute15
1347 ,attribute_category
1348 ,tax_classification_code
1349 ,G_LOGIN_ID
1350 ,zx_registrations_s.nextval
1351 ,tax_authority_id
1352 ,rep_tax_authority_id
1353 ,coll_tax_authority_id
1354 ,l_tbl_to_ptp_id(i)
1355 ,legal_registration_id
1356 ,bank_id
1357 ,bank_branch_id
1358 ,l_tbl_to_acct_id(i)
1359 ,l_tbl_to_acct_site_id(i)
1360 ,1
1361 ,rounding_level_code
1362 ,account_type_code
1363 ,merged_to_registration_id
1364 FROM zx_registrations main_tbl
1365 WHERE party_tax_profile_id = l_tbl_from_ptp_id(i)
1366 AND NOT EXISTS
1367 (SELECT 1
1368 FROM zx_registrations ref_data
1369 WHERE ref_data.party_tax_profile_id = l_tbl_from_ptp_id(i)
1370 AND NVL(ref_data.tax_regime_code,FND_API.G_MISS_CHAR) = NVL(main_tbl.tax_regime_code,FND_API.G_MISS_CHAR)
1371 AND NVL(ref_data.tax,FND_API.G_MISS_CHAR) = NVL(main_tbl.tax,FND_API.G_MISS_CHAR)
1372 AND NVL(ref_data.tax_jurisdiction_code,FND_API.G_MISS_CHAR) = NVL(main_tbl.tax_jurisdiction_code,FND_API.G_MISS_CHAR)
1373 AND NVL(ref_data.account_id,FND_API.G_MISS_NUM) = NVL(l_tbl_to_acct_id(i),FND_API.G_MISS_NUM)
1374 AND NVL(ref_data.account_site_id,FND_API.G_MISS_NUM) = NVL(l_tbl_to_acct_site_id(i),FND_API.G_MISS_NUM)
1375 AND ref_data.effective_from = main_tbl.effective_from
1376 );
1377 --
1378 -- Processing for Registration records end
1379 -----------------------------------------------------------------
1380 -- Processing for Exemption records start
1381 --
1382 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1383 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name,
1384 'Inserting exemption records');
1385 END IF;
1386 arp_message.set_line(G_MODULE_NAME||l_procedure_name||'Inserting exemption records');
1387 FORALL i in 1..l_tbl_from_ptp_id.count
1388 INSERT INTO zx_exemptions
1389 (tax_exemption_id
1390 ,exemption_type_code
1391 ,exemption_status_code
1392 ,tax_regime_code
1393 ,tax_status_code
1394 ,tax
1395 ,tax_rate_code
1396 ,exempt_certificate_number
1397 ,exempt_reason_code
1398 ,issuing_tax_authority_id
1399 ,effective_from
1400 ,effective_to
1401 ,content_owner_id
1402 ,product_id
1403 ,inventory_org_id
1404 ,rate_modifier
1405 ,tax_jurisdiction_id
1406 ,det_factor_templ_code
1407 ,record_type_code
1408 ,created_by
1409 ,creation_date
1410 ,last_updated_by
1411 ,last_update_date
1412 ,last_update_login
1413 ,request_id
1414 ,program_application_id
1415 ,program_id
1416 ,program_login_id
1417 ,attribute1
1418 ,attribute2
1419 ,attribute3
1420 ,attribute4
1421 ,attribute5
1422 ,attribute6
1423 ,attribute7
1424 ,attribute8
1425 ,attribute9
1426 ,attribute10
1427 ,attribute11
1428 ,attribute12
1429 ,attribute13
1430 ,attribute14
1431 ,attribute15
1432 ,attribute_category
1433 ,apply_to_lower_levels_flag
1434 ,object_version_number
1435 ,party_tax_profile_id
1436 ,cust_account_id
1437 ,site_use_id
1438 ,duplicate_exemption
1439 )
1440 SELECT
1441 zx_exemptions_s.nextval
1442 ,exemption_type_code
1443 ,exemption_status_code
1444 ,tax_regime_code
1445 ,tax_status_code
1446 ,tax
1447 ,tax_rate_code
1448 ,exempt_certificate_number
1449 ,exempt_reason_code
1450 ,issuing_tax_authority_id
1451 ,effective_from
1452 ,effective_to
1453 ,content_owner_id
1454 ,product_id
1455 ,inventory_org_id
1456 ,rate_modifier
1457 ,tax_jurisdiction_id
1458 ,det_factor_templ_code
1459 ,record_type_code
1460 ,G_USER_ID
1461 ,SYSDATE
1462 ,G_USER_ID
1463 ,SYSDATE
1464 ,G_LOGIN_ID
1465 ,l_request_id
1466 ,l_prog_appl_id
1467 ,l_conc_program_id
1468 ,G_LOGIN_ID
1469 ,attribute1
1470 ,attribute2
1471 ,attribute3
1472 ,attribute4
1473 ,attribute5
1474 ,attribute6
1475 ,attribute7
1476 ,attribute8
1477 ,attribute9
1478 ,attribute10
1479 ,attribute11
1480 ,attribute12
1481 ,attribute13
1482 ,attribute14
1483 ,attribute15
1484 ,attribute_category
1485 ,apply_to_lower_levels_flag
1486 ,1
1487 ,l_tbl_to_ptp_id(i)
1488 ,l_tbl_to_acct_id(i)
1489 ,site_use_id
1490 ,duplicate_exemption
1491 FROM zx_exemptions
1492 WHERE party_tax_profile_id = l_tbl_from_ptp_id(i);
1493 --
1494 -- Processing for Exemption records end
1495 -----------------------------------------------------------------
1496 END IF;
1497
1498 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1499 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,'end');
1500 END IF;
1501 arp_message.set_line(G_MODULE_NAME||l_procedure_name||'end');
1502
1503 EXCEPTION
1504 WHEN OTHERS THEN
1505 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1506 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_procedure_name,'Exception - '||SQLERRM);
1507 END IF;
1508 arp_message.set_line(G_MODULE_NAME||l_procedure_name||'Exception - '||SQLERRM);
1509 END MERGE_PTP_BULK;
1510
1511 ---------------------------------
1512 -- Merge Party Site Registrations
1513 ---------------------------------
1514 PROCEDURE MERGE_SITE_REGISTRATIONS_PVT
1515 (p_from_ptp_id IN zx_party_tax_profile.party_tax_profile_id%TYPE
1516 ,p_to_ptp_id IN zx_party_tax_profile.party_tax_profile_id%TYPE
1517 ,x_return_status OUT NOCOPY VARCHAR2
1518 ) IS
1519
1520 CURSOR get_from_ptp_registrations IS
1521 SELECT * FROM zx_registrations
1522 WHERE party_tax_profile_id = p_from_ptp_id;
1523
1524 l_registration_id NUMBER;
1525 l_reg_count NUMBER;
1526 l_procedure_name CONSTANT VARCHAR2(30) := '.MERGE_SITE_REGISTRATIONS_PVT';
1527
1528 BEGIN
1529 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1530 x_return_status := FND_API.G_RET_STS_SUCCESS;
1531
1532 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1533 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
1534 'Merge_Site_Registrations_Pvt(+)');
1535 END IF;
1536
1537 l_reg_count := 0;
1538
1539 FOR rec IN get_from_ptp_registrations LOOP
1540
1541 SELECT zx_registrations_s.nextval
1542 INTO l_registration_id
1543 FROM dual;
1544
1545 -- fix for bug 14477937 check to avoid if insertion is creating ZX_REGISTRATIONS_U2
1546 INSERT INTO ZX_REGISTRATIONS
1547 (registration_type_code
1548 ,registration_number
1549 ,validation_rule
1550 ,rounding_rule_code
1551 ,tax_jurisdiction_code
1552 ,self_assess_flag
1553 ,registration_status_code
1554 ,registration_source_code
1555 ,registration_reason_code
1556 ,tax
1557 ,tax_regime_code
1558 ,inclusive_tax_flag
1559 ,has_tax_exemptions_flag
1560 ,effective_from
1561 ,effective_to
1562 ,rep_party_tax_name
1563 ,default_registration_flag
1564 ,bank_account_num
1565 ,legal_location_id
1566 ,record_type_code
1567 ,created_by
1568 ,creation_date
1569 ,last_updated_by
1570 ,last_update_date
1571 ,last_update_login
1572 ,attribute1
1573 ,attribute2
1574 ,attribute3
1575 ,attribute4
1576 ,attribute5
1577 ,attribute6
1578 ,attribute7
1579 ,attribute8
1580 ,attribute9
1581 ,attribute10
1582 ,attribute11
1583 ,attribute12
1584 ,attribute13
1585 ,attribute14
1586 ,attribute15
1587 ,attribute_category
1588 ,tax_classification_code
1589 ,registration_id
1590 ,tax_authority_id
1591 ,rep_tax_authority_id
1592 ,coll_tax_authority_id
1593 ,party_tax_profile_id
1594 ,legal_registration_id
1595 ,account_id
1596 ,account_site_id
1597 ,bank_id
1598 ,bank_branch_id
1599 ,object_version_number
1600 ,rounding_level_code
1601 ,account_type_code
1602 ,merged_to_registration_id
1603 )
1604 SELECT
1605 rec.registration_type_code
1606 ,rec.registration_number
1607 ,rec.validation_rule
1608 ,rec.rounding_rule_code
1609 ,rec.tax_jurisdiction_code
1610 ,rec.self_assess_flag
1611 ,rec.registration_status_code
1612 ,rec.registration_source_code
1613 ,rec.registration_reason_code
1614 ,rec.tax
1615 ,rec.tax_regime_code
1616 ,rec.inclusive_tax_flag
1617 ,rec.has_tax_exemptions_flag
1618 ,rec.effective_from
1619 ,rec.effective_to
1620 ,rec.rep_party_tax_name
1621 ,'N'
1622 ,rec.bank_account_num
1623 ,rec.legal_location_id
1624 ,rec.record_type_code
1625 ,G_USER_ID
1626 ,SYSDATE
1627 ,G_USER_ID
1628 ,SYSDATE
1629 ,G_LOGIN_ID
1630 ,rec.attribute1
1631 ,rec.attribute2
1632 ,rec.attribute3
1633 ,rec.attribute4
1634 ,rec.attribute5
1635 ,rec.attribute6
1636 ,rec.attribute7
1637 ,rec.attribute8
1638 ,rec.attribute9
1639 ,rec.attribute10
1640 ,rec.attribute11
1641 ,rec.attribute12
1642 ,rec.attribute13
1643 ,rec.attribute14
1644 ,rec.attribute15
1645 ,rec.attribute_category
1646 ,rec.tax_classification_code
1647 ,l_registration_id
1648 ,rec.tax_authority_id
1649 ,rec.rep_tax_authority_id
1650 ,rec.coll_tax_authority_id
1651 ,p_to_ptp_id
1652 ,rec.legal_registration_id
1653 ,rec.account_id
1654 ,rec.account_site_id
1655 ,rec.bank_id
1656 ,rec.bank_branch_id
1657 ,1
1658 ,rec.rounding_level_code
1659 ,rec.account_type_code
1660 ,TO_NUMBER(NULL)
1661 FROM DUAL
1662 WHERE NOT EXISTS
1663 (SELECT 1
1664 FROM zx_registrations ref_data
1665 WHERE ref_data.party_tax_profile_id = p_to_ptp_id
1666 AND NVL(ref_data.tax_regime_code,FND_API.G_MISS_CHAR) = NVL(rec.tax_regime_code,FND_API.G_MISS_CHAR)
1667 AND NVL(ref_data.tax,FND_API.G_MISS_CHAR) = NVL(rec.tax,FND_API.G_MISS_CHAR)
1668 AND NVL(ref_data.tax_jurisdiction_code,FND_API.G_MISS_CHAR) = NVL(rec.tax_jurisdiction_code,FND_API.G_MISS_CHAR)
1669 AND NVL(ref_data.account_id,FND_API.G_MISS_NUM) = NVL(rec.account_id,FND_API.G_MISS_NUM)
1670 AND NVL(ref_data.account_site_id,FND_API.G_MISS_NUM) = NVL(rec.account_site_id,FND_API.G_MISS_NUM)
1671 AND ref_data.effective_from = rec.effective_from
1672 );
1673
1674 UPDATE zx_registrations
1675 SET merged_to_registration_id = l_registration_id,
1676 effective_to = SYSDATE,
1677 last_update_date = SYSDATE,
1678 last_updated_by = G_USER_ID,
1679 last_update_login = G_LOGIN_ID,
1680 object_version_number = object_version_number+1
1681 WHERE registration_id = rec.registration_id
1682 AND party_tax_profile_id = p_from_ptp_id;
1683
1684 l_reg_count := l_reg_count + 1;
1685
1686 END LOOP;
1687
1688 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1689 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
1690 l_reg_count||' record(s) created in ZX_Registrations');
1691 END IF;
1692
1693 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1694 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
1695 'Merge_Site_Registrations_Pvt(+)');
1696 END IF;
1697
1698 EXCEPTION
1699 WHEN OTHERS THEN
1700 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1701 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_procedure_name,
1702 'Merge_Site_Registrations_Pvt(Exception)');
1703 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_procedure_name,
1704 'Error : '||SQLERRM);
1705 END IF;
1706 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1707
1708 END MERGE_SITE_REGISTRATIONS_PVT;
1709
1710 ------------------------------
1711 -- Merge Party Site Exemptions
1712 ------------------------------
1713 PROCEDURE MERGE_SITE_EXEMPTIONS_PVT
1714 (p_from_ptp_id IN zx_party_tax_profile.party_tax_profile_id%TYPE
1715 ,p_to_ptp_id IN zx_party_tax_profile.party_tax_profile_id%TYPE
1716 ,x_return_status OUT NOCOPY VARCHAR2
1717 ) IS
1718
1719 l_procedure_name CONSTANT VARCHAR2(30) := '.MERGE_SITE_EXEMPTIONS_PVT';
1720
1721 BEGIN
1722 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1723 x_return_status := FND_API.G_RET_STS_SUCCESS;
1724
1725 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1726 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
1727 'Merge_Site_Exemptions_Pvt(+)');
1728 END IF;
1729
1730 INSERT INTO zx_exemptions
1731 (tax_exemption_id
1732 ,exemption_type_code
1733 ,exemption_status_code
1734 ,tax_regime_code
1735 ,tax_status_code
1736 ,tax
1737 ,tax_rate_code
1738 ,exempt_certificate_number
1739 ,exempt_reason_code
1740 ,issuing_tax_authority_id
1741 ,effective_from
1742 ,effective_to
1743 ,content_owner_id
1744 ,product_id
1745 ,inventory_org_id
1746 ,rate_modifier
1747 ,tax_jurisdiction_id
1748 ,det_factor_templ_code
1749 ,record_type_code
1750 ,created_by
1751 ,creation_date
1752 ,last_updated_by
1753 ,last_update_date
1754 ,last_update_login
1755 ,attribute1
1756 ,attribute2
1757 ,attribute3
1758 ,attribute4
1759 ,attribute5
1760 ,attribute6
1761 ,attribute7
1762 ,attribute8
1763 ,attribute9
1764 ,attribute10
1765 ,attribute11
1766 ,attribute12
1767 ,attribute13
1768 ,attribute14
1769 ,attribute15
1770 ,attribute_category
1771 ,apply_to_lower_levels_flag
1772 ,object_version_number
1773 ,party_tax_profile_id
1774 ,cust_account_id
1775 ,site_use_id
1776 ,duplicate_exemption
1777 )
1778 SELECT
1779 zx_exemptions_s.nextval
1780 ,exemption_type_code
1781 ,exemption_status_code
1782 ,tax_regime_code
1783 ,tax_status_code
1784 ,tax
1785 ,tax_rate_code
1786 ,exempt_certificate_number
1787 ,exempt_reason_code
1788 ,issuing_tax_authority_id
1789 ,effective_from
1790 ,effective_to
1791 ,content_owner_id
1792 ,product_id
1793 ,inventory_org_id
1794 ,rate_modifier
1795 ,tax_jurisdiction_id
1796 ,det_factor_templ_code
1797 ,record_type_code
1798 ,G_USER_ID
1799 ,SYSDATE
1800 ,G_USER_ID
1801 ,SYSDATE
1802 ,G_LOGIN_ID
1806 ,attribute4
1803 ,attribute1
1804 ,attribute2
1805 ,attribute3
1807 ,attribute5
1808 ,attribute6
1809 ,attribute7
1810 ,attribute8
1811 ,attribute9
1812 ,attribute10
1813 ,attribute11
1814 ,attribute12
1815 ,attribute13
1816 ,attribute14
1817 ,attribute15
1818 ,attribute_category
1819 ,apply_to_lower_levels_flag
1820 ,1
1821 ,p_to_ptp_id
1822 ,cust_account_id
1823 ,site_use_id
1824 ,duplicate_exemption
1825 FROM zx_exemptions
1826 WHERE party_tax_profile_id = p_from_ptp_id;
1827
1828 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1829 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
1830 SQL%ROWCOUNT||' record(s) created in ZX_Exemptions');
1831 END IF;
1832
1833 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1834 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
1835 'Merge_Site_Exemptions_Pvt(-)');
1836 END IF;
1837
1838 EXCEPTION
1839 WHEN OTHERS THEN
1840 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1841 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_procedure_name,
1842 'Merge_Site_Exemptions_Pvt(Exception)');
1843 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_procedure_name,
1844 'Error : '||SQLERRM);
1845 END IF;
1846 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1847
1848 END MERGE_SITE_EXEMPTIONS_PVT;
1849
1850 ------------------------------
1851 -- Merge Party Sites PTP
1852 ------------------------------
1853 PROCEDURE MERGE_PARTY_SITES_PTP_PVT
1854 (p_from_fk_id IN zx_party_tax_profile.party_id%TYPE
1855 ,p_from_party_type IN zx_party_tax_profile.party_type_code%TYPE
1856 ,p_to_fk_id IN zx_party_tax_profile.party_id%TYPE
1857 ,p_to_party_type IN zx_party_tax_profile.party_type_code%TYPE
1858 ,x_return_status OUT NOCOPY VARCHAR2
1859 ) IS
1860
1861 CURSOR c_party_type (c_party_type zx_party_tax_profile.party_type_code%TYPE) IS
1862 SELECT lookup_code
1863 FROM fnd_lookups
1864 WHERE lookup_type = 'ZX_PTP_PARTY_TYPE'
1865 AND lookup_code = c_party_type
1866 AND enabled_flag = 'Y'
1867 AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active,SYSDATE);
1868
1869 CURSOR get_ptp_info
1870 (c_party_id zx_party_tax_profile.party_id%TYPE,
1871 c_party_type zx_party_tax_profile.party_type_code%TYPE) IS
1872 SELECT * FROM zx_party_tax_profile
1873 WHERE party_id = c_party_id
1874 AND party_type_code = c_party_type;
1875
1876 CURSOR get_ptp_id
1877 (c_party_id zx_party_tax_profile.party_id%TYPE,
1878 c_party_type zx_party_tax_profile.party_type_code%TYPE) IS
1879 SELECT party_tax_profile_id
1880 FROM zx_party_tax_profile
1881 WHERE party_id = c_party_id
1882 AND party_type_code = c_party_type;
1883
1884 l_party_type_code zx_party_tax_profile.party_type_code%TYPE;
1885
1886 l_from_ptp_rec zx_Party_Tax_Profile%ROWTYPE;
1887 l_to_ptp_rec zx_Party_Tax_Profile%ROWTYPE;
1888
1889 l_insert_ptp BOOLEAN;
1890 l_procedure_name CONSTANT VARCHAR2(30) := '.MERGE_PARTY_SITES_PTP_PVT';
1891
1892 BEGIN
1893
1894 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1895 x_return_status := FND_API.G_RET_STS_SUCCESS;
1896
1897 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1898 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
1899 'Merge_Party_Sites_PTP_Pvt(+)');
1900 END IF;
1901
1902 ---------------------------
1903 -- Party Type Validation --
1904 ---------------------------
1905 OPEN c_party_type(p_from_party_type);
1906 FETCH c_party_type INTO l_party_type_code;
1907 IF c_party_type%NOTFOUND THEN
1908 x_return_status := FND_API.G_RET_STS_ERROR;
1909 CLOSE c_party_type;
1910 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1911 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_procedure_name,
1912 'Error : From Party Type '||p_from_party_type||' is not valid');
1913 END IF;
1914 RETURN;
1915 END IF;
1916 CLOSE c_party_type;
1917
1918 OPEN c_party_type(p_to_party_type);
1919 FETCH c_party_type INTO l_party_type_code;
1920 IF c_party_type%NOTFOUND THEN
1921 x_return_status := FND_API.G_RET_STS_ERROR;
1922 CLOSE c_party_type;
1923 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1924 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_procedure_name,
1925 'Error : To Party Type '||p_to_party_type||' is not valid');
1926 END IF;
1927 RETURN;
1928 END IF;
1929 CLOSE c_party_type;
1930 ---------------------------
1931 -- From Party Validation --
1932 ---------------------------
1933 OPEN get_ptp_info(p_from_fk_id, p_from_party_type);
1934 FETCH get_ptp_info INTO l_from_ptp_rec;
1935 IF get_ptp_info%NOTFOUND THEN
1936 CLOSE get_ptp_info;
1937 x_return_status := FND_API.G_RET_STS_ERROR;
1938 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1939 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_procedure_name,
1940 'Error : From Party Info not available for Party-Id '||
1941 p_from_fk_id||' and Party-Type '||p_from_party_type);
1942 END IF;
1943 RETURN;
1944 END IF;
1945 CLOSE get_ptp_info;
1946 -------------------------
1947 -- To Party Validation --
1948 -------------------------
1949 OPEN get_ptp_info(p_to_fk_id, p_to_party_type);
1950 FETCH get_ptp_info INTO l_to_ptp_rec;
1951 -- bug 10430488 handling the insert and update separately
1952 IF get_ptp_info%FOUND THEN
1953 l_insert_ptp := FALSE;
1954 -- x_return_status := FND_API.G_RET_STS_ERROR;
1955 -- IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1956 -- FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_procedure_name,
1957 -- 'Error : To Party Info already available for Party-Id '||
1958 -- p_to_fk_id||' and Party-Type '||p_to_party_type);
1959 -- END IF;
1960 -- RETURN;
1961 ELSIF get_ptp_info%NOTFOUND THEN
1962 l_insert_ptp := TRUE;
1963 END IF;
1964 CLOSE get_ptp_info;
1965
1966 IF l_insert_ptp THEN
1967 ---------------------------------------
1968 -- Create New PTP record for To Site --
1969 ---------------------------------------
1970 ZX_PARTY_TAX_PROFILE_PKG.INSERT_ROW
1971 (p_collecting_authority_flag => l_from_ptp_rec.collecting_authority_flag
1972 ,p_provider_type_code => l_from_ptp_rec.provider_type_code
1973 ,p_create_awt_dists_type_code => l_from_ptp_rec.create_awt_dists_type_code
1974 ,p_create_awt_invoices_type_cod => l_from_ptp_rec.create_awt_invoices_type_code
1975 ,p_tax_classification_code => l_from_ptp_rec.tax_classification_code
1976 ,p_self_assess_flag => l_from_ptp_rec.self_assess_flag
1977 ,p_allow_offset_tax_flag => l_from_ptp_rec.allow_offset_tax_flag
1978 ,p_rep_registration_number => l_from_ptp_rec.rep_registration_number
1979 ,p_effective_from_use_le => l_from_ptp_rec.effective_from_use_le
1980 ,p_record_type_code => l_from_ptp_rec.record_type_code
1981 ,p_request_id => fnd_global.conc_request_id
1982 ,p_attribute1 => l_from_ptp_rec.attribute1
1983 ,p_attribute2 => l_from_ptp_rec.attribute2
1984 ,p_attribute3 => l_from_ptp_rec.attribute3
1985 ,p_attribute4 => l_from_ptp_rec.attribute4
1986 ,p_attribute5 => l_from_ptp_rec.attribute5
1987 ,p_attribute6 => l_from_ptp_rec.attribute6
1988 ,p_attribute7 => l_from_ptp_rec.attribute7
1992 ,p_attribute11 => l_from_ptp_rec.attribute11
1989 ,p_attribute8 => l_from_ptp_rec.attribute8
1990 ,p_attribute9 => l_from_ptp_rec.attribute9
1991 ,p_attribute10 => l_from_ptp_rec.attribute10
1993 ,p_attribute12 => l_from_ptp_rec.attribute12
1994 ,p_attribute13 => l_from_ptp_rec.attribute13
1995 ,p_attribute14 => l_from_ptp_rec.attribute14
1996 ,p_attribute15 => l_from_ptp_rec.attribute15
1997 ,p_attribute_category => l_from_ptp_rec.attribute_category
1998 ,p_party_id => p_to_fk_id
1999 ,p_program_login_id => fnd_global.conc_login_id
2000 ,p_party_type_code => p_to_party_type
2001 ,p_supplier_flag => l_from_ptp_rec.supplier_flag
2002 ,p_customer_flag => l_from_ptp_rec.customer_flag
2003 ,p_site_flag => l_from_ptp_rec.site_flag
2004 ,p_process_for_applicability_fl => l_from_ptp_rec.process_for_applicability_flag
2005 ,p_rounding_level_code => l_from_ptp_rec.rounding_level_code
2006 ,p_rounding_rule_code => l_from_ptp_rec.rounding_rule_code
2007 ,p_withholding_start_date => l_from_ptp_rec.withholding_start_date
2008 ,p_inclusive_tax_flag => l_from_ptp_rec.inclusive_tax_flag
2009 ,p_allow_awt_flag => l_from_ptp_rec.allow_awt_flag
2010 ,p_use_le_as_subscriber_flag => l_from_ptp_rec.use_le_as_subscriber_flag
2011 ,p_legal_establishment_flag => l_from_ptp_rec.legal_establishment_flag
2012 ,p_first_party_le_flag => l_from_ptp_rec.first_party_le_flag
2013 ,p_reporting_authority_flag => l_from_ptp_rec.reporting_authority_flag
2014 ,x_return_status => x_return_status
2015 ,p_registration_type_code => l_from_ptp_rec.registration_type_code
2016 ,p_country_code => l_from_ptp_rec.country_code
2017 );
2018 ELSE
2019 ---------------------------------------
2020 -- Update Existing PTP record for To Site --
2021 ---------------------------------------
2022 ZX_PARTY_TAX_PROFILE_PKG.UPDATE_ROW (
2023 p_party_tax_profile_id => l_to_ptp_rec.party_tax_profile_id
2024 ,p_collecting_authority_flag => l_from_ptp_rec.collecting_authority_flag
2025 ,p_provider_type_code => l_from_ptp_rec.provider_type_code
2026 ,p_create_awt_dists_type_code => l_from_ptp_rec.create_awt_dists_type_code
2027 ,p_create_awt_invoices_type_cod => l_from_ptp_rec.create_awt_invoices_type_code
2028 ,p_tax_classification_code => l_from_ptp_rec.tax_classification_code
2029 ,p_self_assess_flag => l_from_ptp_rec.self_assess_flag
2030 ,p_allow_offset_tax_flag => l_from_ptp_rec.allow_offset_tax_flag
2031 ,p_rep_registration_number => l_from_ptp_rec.rep_registration_number
2032 ,p_effective_from_use_le => l_from_ptp_rec.effective_from_use_le
2033 ,p_record_type_code => l_from_ptp_rec.record_type_code
2034 ,p_request_id => fnd_global.conc_request_id
2035 ,p_attribute1 => l_from_ptp_rec.attribute1
2036 ,p_attribute2 => l_from_ptp_rec.attribute2
2037 ,p_attribute3 => l_from_ptp_rec.attribute3
2038 ,p_attribute4 => l_from_ptp_rec.attribute4
2039 ,p_attribute5 => l_from_ptp_rec.attribute5
2040 ,p_attribute6 => l_from_ptp_rec.attribute6
2041 ,p_attribute7 => l_from_ptp_rec.attribute7
2042 ,p_attribute8 => l_from_ptp_rec.attribute8
2043 ,p_attribute9 => l_from_ptp_rec.attribute9
2044 ,p_attribute10 => l_from_ptp_rec.attribute10
2045 ,p_attribute11 => l_from_ptp_rec.attribute11
2046 ,p_attribute12 => l_from_ptp_rec.attribute12
2047 ,p_attribute13 => l_from_ptp_rec.attribute13
2048 ,p_attribute14 => l_from_ptp_rec.attribute14
2049 ,p_attribute15 => l_from_ptp_rec.attribute15
2050 ,p_attribute_category => l_from_ptp_rec.attribute_category
2051 ,p_party_id => p_to_fk_id
2052 ,p_program_login_id => fnd_global.conc_login_id
2053 ,p_party_type_code => p_to_party_type
2054 ,p_supplier_flag => l_from_ptp_rec.supplier_flag
2055 ,p_customer_flag => l_from_ptp_rec.customer_flag
2056 ,p_site_flag => l_from_ptp_rec.site_flag
2057 ,p_process_for_applicability_fl => l_from_ptp_rec.process_for_applicability_flag
2058 ,p_rounding_level_code => l_from_ptp_rec.rounding_level_code
2059 ,p_rounding_rule_code => l_from_ptp_rec.rounding_rule_code
2060 ,p_withholding_start_date => l_from_ptp_rec.withholding_start_date
2061 ,p_inclusive_tax_flag => l_from_ptp_rec.inclusive_tax_flag
2062 ,p_allow_awt_flag => l_from_ptp_rec.allow_awt_flag
2063 ,p_use_le_as_subscriber_flag => l_from_ptp_rec.use_le_as_subscriber_flag
2064 ,p_legal_establishment_flag => l_from_ptp_rec.legal_establishment_flag
2065 ,p_first_party_le_flag => l_from_ptp_rec.first_party_le_flag
2066 ,p_reporting_authority_flag => l_from_ptp_rec.reporting_authority_flag
2067 ,x_return_status => x_return_status
2068 ,p_registration_type_code => l_from_ptp_rec.registration_type_code
2069 ,p_country_code => l_from_ptp_rec.country_code
2070 );
2071 END IF;
2072
2073 IF NVL(x_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
2074 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2078 'Return Status = '||x_return_status);
2075 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
2076 'Incorrect status retuned by ZX_Party_Tax_Profile_Pkg.Insert_Row()');
2077 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
2079 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
2080 'Merge_Party_Sites_PTP_Pvt(-)');
2081 END IF;
2082 RETURN;
2083 END IF;
2084
2085 OPEN get_ptp_id (p_to_fk_id, p_to_party_type);
2086 FETCH get_ptp_id INTO l_to_ptp_rec.party_tax_profile_id;
2087 CLOSE get_ptp_id;
2088
2089 IF l_to_ptp_rec.party_tax_profile_id IS NULL THEN
2090 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2091 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
2092 'Error : Party_Tax_Profile_Id of To-Site is NULL');
2093 END IF;
2094 x_return_status := FND_API.G_RET_STS_ERROR ;
2095 RETURN;
2096 ELSE
2097 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2098 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
2099 'Party_Tax_Profile_Id of To-Site : '||l_to_ptp_rec.party_tax_profile_id);
2100 END IF;
2101 END IF;
2102
2103 UPDATE zx_party_tax_profile
2104 SET merged_to_ptp_id = l_to_ptp_rec.party_tax_profile_id,
2105 merged_status_code = 'MERGED',
2106 last_update_date = SYSDATE,
2107 last_updated_by = G_USER_ID,
2108 last_update_login = G_LOGIN_ID,
2109 object_version_number = object_version_number+1
2110 WHERE Party_Tax_Profile_id = l_from_ptp_rec.party_tax_profile_id;
2111
2112 -----------------------
2113 -- Create registrations
2114 -----------------------
2115 MERGE_SITE_REGISTRATIONS_PVT
2116 (p_from_ptp_id => l_from_ptp_rec.party_tax_profile_id
2117 ,p_to_ptp_id => l_to_ptp_rec.party_tax_profile_id
2118 ,x_return_status => x_return_status);
2119
2120 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2121 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2122 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
2123 'Incorrect status retuned by Merge_Site_Registrations_Pvt()');
2124 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
2125 'Return Status = '||x_return_status);
2126 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
2127 'Merge_Party_Sites_PTP_Pvt(-)');
2128 END IF;
2129 RETURN;
2130 END IF;
2131
2132 --------------------
2133 -- Create Exemptions
2134 --------------------
2135 MERGE_SITE_EXEMPTIONS_PVT
2136 (p_from_ptp_id => l_from_ptp_rec.party_tax_profile_id
2137 ,p_to_ptp_id => l_to_ptp_rec.party_tax_profile_id
2138 ,x_return_status => x_return_status);
2139
2140 IF NVL(x_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
2141 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2142 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
2143 'Incorrect status retuned by Merge_Site_Exemptions_Pvt()');
2144 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
2145 'Return Status = '||x_return_status);
2146 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
2147 'Merge_Party_Sites_PTP_Pvt(-)');
2148 END IF;
2149 RETURN;
2150 END IF;
2151
2152 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2153 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
2154 'Merge_Party_Sites_PTP_Pvt(-)');
2155 END IF;
2156
2157 EXCEPTION
2158 WHEN OTHERS THEN
2159 IF c_party_type%ISOPEN THEN CLOSE c_party_type; END IF;
2160 IF get_ptp_info%ISOPEN THEN CLOSE get_ptp_info; END IF;
2161 IF get_ptp_id%ISOPEN THEN CLOSE get_ptp_id; END IF;
2162
2163 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2164 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_procedure_name,
2165 'Merge_Party_Sites_PTP_Pvt(Exception)');
2166 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_procedure_name,
2167 'Error : '||SQLERRM);
2168 END IF;
2169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2170
2171 END MERGE_PARTY_SITES_PTP_PVT;
2172
2173 ------------------------------
2174 -- Merge Party Sites
2175 ------------------------------
2176 PROCEDURE MERGE_PTP_SITES (
2177 p_entity_name IN VARCHAR2,
2178 p_from_id IN NUMBER,
2179 p_to_id IN OUT NOCOPY NUMBER,
2180 p_from_fk_id IN NUMBER,
2181 p_to_fk_id IN NUMBER,
2182 p_parent_entity_name IN VARCHAR2,
2183 p_batch_id IN VARCHAR2,
2184 p_batch_party_id IN VARCHAR2,
2185 x_return_status IN OUT NOCOPY VARCHAR2
2186 ) IS
2187
2188 l_procedure_name CONSTANT VARCHAR2(30) := '.MERGE_PTP_SITES ';
2189
2190 BEGIN
2191
2192 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2193 x_return_status := FND_API.G_RET_STS_SUCCESS;
2194
2195 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2196 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
2197 'Merge_PTP_Sites(+)');
2198 END IF;
2199
2200 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2201 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,'Input Parameters :-');
2202 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,'Entity_Name : ' ||p_entity_name);
2203 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,'From_Id : ' ||p_from_id);
2204 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,'From_Fk_Id : ' ||p_from_fk_id);
2205 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,'To_Id : '||p_to_id);
2206 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,'To_Fk_id : '||p_to_fk_id);
2207 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,'Parent_Entity_Name : '||p_parent_entity_name);
2208 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,'Batch_Id : ' ||p_batch_id);
2209 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,'Batch_Party_Id : '||p_batch_party_id);
2210 END IF;
2211
2212 IF p_from_fk_id IS NULL THEN
2213 x_return_status := FND_API.G_RET_STS_ERROR;
2214 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2215 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
2216 'Error : From-Party-Id is NULL');
2217 END IF;
2218 RETURN;
2219 END IF;
2220
2221 IF p_to_fk_id IS NULL THEN
2222 x_return_status := FND_API.G_RET_STS_ERROR;
2223 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2224 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
2225 'Error : To-Party-Id is NULL');
2226 END IF;
2227 RETURN;
2228 END IF;
2229
2230 IF p_from_fk_id = p_to_fk_id THEN
2231 p_to_id := p_from_id;
2232 x_return_status := FND_API.G_RET_STS_ERROR;
2233 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2234 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
2235 'Error : From-Party-Id and To-Party-Id are same');
2236 END IF;
2237 RETURN;
2238 END IF;
2239
2240 MERGE_PARTY_SITES_PTP_PVT
2241 (p_from_fk_id => p_from_fk_id
2242 ,p_from_party_type => 'THIRD_PARTY_SITE'
2243 ,p_to_fk_id => p_to_fk_id
2244 ,p_to_party_type => 'THIRD_PARTY_SITE'
2245 ,x_return_status => x_return_status
2246 );
2247
2248 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2249 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2250 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
2251 'Incorrect status retuned by Merge_Party_Sites_PTP_Pvt()');
2252 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
2253 'Return Status = '||x_return_status);
2254 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
2255 'Merge_PTP_Sites(-)');
2256 END IF;
2257 RETURN;
2258 END IF;
2259
2260 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2261 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,
2262 'Merge_PTP_Sites(-)');
2263 END IF;
2264
2265 EXCEPTION
2266 WHEN OTHERS THEN
2267 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2268 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_procedure_name,
2269 'Merge_PTP_Sites(Exception)');
2270 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_procedure_name,
2271 'Error : '||SQLERRM);
2272 END IF;
2273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2274 RAISE;
2275 END MERGE_PTP_SITES;
2276
2277 END ZX_PARTY_MERGE_PKG;