[Home] [Help]
PACKAGE BODY: APPS.ZX_TAX_PARTNER_MIGRATE_PKG
Source
1 package body ZX_TAX_PARTNER_MIGRATE_PKG AS
2 /* $Header: zxptnrmigpkgb.pls 120.16.12020000.2 2012/11/06 10:13:21 ssohal ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ZX_TAX_PARTNER_MIGRATE_PKG';
5 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12 G_MODULE_NAME CONSTANT VARCHAR2(250):= 'ZX.PLSQL.ZX_TAX_PARTNER_MIGRATE_PKG.';
13
14 l_gen_party_number VARCHAR2(1);
15
16 PROCEDURE insert_ptp (
17 p_party_id IN NUMBER,
18 p_ptp_id IN NUMBER,
19 x_return_status OUT NOCOPY VARCHAR2
20 )IS
21
22 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_PTP';
23 l_return_status VARCHAR2(1);
24
25 BEGIN
26 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
27 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
28 END IF;
29
30 x_return_status := FND_API.G_RET_STS_SUCCESS;
31
32 INSERT INTO zx_party_tax_profile (
33 collecting_authority_flag,
34 provider_type_code,
35 create_awt_dists_type_code,
36 create_awt_invoices_type_code,
37 tax_classification_code,
38 self_assess_flag,
39 allow_offset_tax_flag,
40 rep_registration_number,
41 effective_from_use_le,
42 record_type_code,
43 request_id,
44 party_tax_profile_id,
45 attribute1,
46 attribute2,
47 attribute3,
48 attribute4,
49 attribute5,
50 attribute6,
51 attribute7,
52 attribute8,
53 attribute9,
54 attribute10,
55 attribute11,
56 attribute12,
57 attribute13,
58 attribute14,
59 attribute15,
60 attribute_category,
61 party_id,
62 program_login_id,
63 party_type_code,
64 supplier_flag,
65 customer_flag,
66 site_flag,
67 process_for_applicability_flag,
68 rounding_level_code,
69 rounding_rule_code,
70 withholding_start_date,
71 inclusive_tax_flag,
72 allow_awt_flag,
73 use_le_as_subscriber_flag,
74 legal_establishment_flag,
75 first_party_le_flag,
76 reporting_authority_flag,
77 creation_date,
78 created_by,
79 last_update_date,
80 last_updated_by,
81 last_update_login,
82 object_version_number)
83 VALUES (
84 NULL,
85 'BOTH',
86 NULL,
87 NULL,
88 NULL,
89 NULL,
90 NULL,
91 NULL,
92 NULL,
93 'MIGRATED',
94 NULL,
95 p_ptp_id,
96 NULL,
97 NULL,
98 NULL,
99 NULL,
100 NULL,
101 NULL,
102 NULL,
103 NULL,
104 NULL,
105 NULL,
106 NULL,
107 NULL,
108 NULL,
109 NULL,
110 NULL,
111 NULL,
112 p_party_id,
113 NULL,
114 'TAX_PARTNER',
115 NULL,
116 NULL,
117 NULL,
118 NULL,
119 NULL,
120 NULL,
121 NULL,
122 NULL,
123 NULL,
124 NULL,
125 NULL,
126 NULL,
127 NULL,
128 SYSDATE,
129 FND_GLOBAL.USER_ID,
130 SYSDATE,
131 FND_GLOBAL.USER_ID,
132 FND_GLOBAL.LOGIN_ID,
133 1);
134
135 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
136 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'()-');
137 END IF;
138
139 EXCEPTION
140 WHEN OTHERS THEN
141 FND_MESSAGE.SET_NAME ('ZX','GENERIC_MESSAGE');
142 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','INSERT_PTP : '||SQLERRM);
143 FND_MSG_PUB.add;
144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
145 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
146 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
147 END IF;
148 END insert_ptp;
149
150 -- Procedure to create TCA Party and ZX Party Tax Profile record for Vertex --
151 PROCEDURE CREATE_VERTEX_TCA_ZX (x_return_status OUT NOCOPY VARCHAR2) IS
152 l_api_name CONSTANT VARCHAR2(50) := 'CREATE_VERTEX_TCA_ZX';
153 p_organization_rec HZ_PARTY_V2PUB.organization_rec_type;
154 l_return_status VARCHAR2(2000);
155 l_msg_count NUMBER;
156 l_msg_data VARCHAR2(2000);
157 l_party_id NUMBER;
158 l_party_number VARCHAR2(2000);
159 l_profile_id NUMBER;
160 dummy NUMBER;
161 l_exists NUMBER;
162
163 BEGIN
164 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
165 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
166 END IF;
167
168 x_return_status := FND_API.G_RET_STS_SUCCESS;
169
170 BEGIN
171 SELECT 1
172 INTO l_exists
173 FROM zx_party_tax_profile
174 WHERE party_tax_profile_id = 1;
175
176 EXCEPTION
177 WHEN NO_DATA_FOUND THEN
178 -- For organization record
179 p_organization_rec.organization_name := 'Vertex Inc';
180 p_organization_rec.duns_number_c := '09-685-3189';
181 p_organization_rec.created_by_module := 'EBTAX_SERVICE_PROVIDER';
182 p_organization_rec.application_id := 235;
183
184 BEGIN
185 SELECT 1
186 INTO dummy
187 FROM hz_parties
188 WHERE party_number = '09-685-3189';
189
190 EXCEPTION WHEN NO_DATA_FOUND THEN
191 --TCA accepts party number only if profile option is N
192 NULL;
193 END;
194
195 IF NVL(dummy,0) <> 1 THEN
196 IF l_gen_party_number IS NULL OR l_gen_party_number = 'Y' THEN
197 --SELECT hz_party_number_s.nextval
198 -- INTO p_organization_rec.party_rec.party_number
199 -- FROM dual;
200 NULL;
201 ELSE
202 p_organization_rec.party_rec.party_number := '09-685-3189';
203 END IF;
204
205 HZ_PARTY_V2PUB.create_organization(
206 p_init_msg_list => 'T',
207 p_organization_rec => p_organization_rec,
208 x_return_status => l_return_status,
209 x_msg_count => l_msg_count,
210 x_msg_data => l_msg_data,
211 x_party_id => l_party_id,
212 x_party_number => l_party_number,
213 x_profile_id => l_profile_id);
214
215 --IF l_msg_count > 1 THEN
216 -- FOR i IN 1..l_msg_count LOOP
217 -- DBMS_OUTPUT.PUT_LINE(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ));
218 -- END LOOP;
219 --END IF;
220
221 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
222 /*Insert into zx_party_tax_profile*/
223 insert_ptp (p_party_id => l_party_id,
224 p_ptp_id => 1,
225 x_return_status => l_return_status
226 );
227 END IF;
228 END IF;
229 END;
230
231 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
232 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'()-');
233 END IF;
234 END CREATE_VERTEX_TCA_ZX;
235
236 -- Procedure to create TCA Party and ZX Party Tax Profile record for Taxware --
237 PROCEDURE CREATE_TAXWARE_TCA_ZX(x_return_status OUT NOCOPY VARCHAR2) IS
238 l_api_name CONSTANT VARCHAR2(50) := 'CREATE_TAXWARE_TCA_ZX';
239 p_organization_rec HZ_PARTY_V2PUB.organization_rec_type;
240 l_return_status VARCHAR2(2000);
241 l_msg_count NUMBER;
242 l_msg_data VARCHAR2(2000);
243 l_party_id NUMBER;
244 l_party_number VARCHAR2(2000);
245 l_profile_id NUMBER;
246 dummy NUMBER;
247 l_exists NUMBER;
248
249 BEGIN
250 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
251 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
252 END IF;
253
254 x_return_status := FND_API.G_RET_STS_SUCCESS;
255
256 BEGIN
257 SELECT 1
258 INTO l_exists
259 FROM zx_party_tax_profile
260 WHERE party_tax_profile_id = 2;
261
262 EXCEPTION
263 WHEN NO_DATA_FOUND THEN
264 -- For organization record
265 p_organization_rec.organization_name := 'ADP Inc';
266 p_organization_rec.duns_number_c := '13-583-0177';
267 p_organization_rec.created_by_module := 'EBTAX_SERVICE_PROVIDER';
268 p_organization_rec.application_id := 235;
269
270 BEGIN
271 SELECT 1
272 INTO dummy
273 FROM hz_parties
274 WHERE party_number = '13-583-0177';
275
276 EXCEPTION WHEN NO_DATA_FOUND THEN
277 --TCA accepts party number only if profile option is N
278 NULL;
279 END;
280
281 IF NVL(dummy,0) <> 1 THEN
282 IF l_gen_party_number IS NULL OR l_gen_party_number = 'Y' THEN
283 --SELECT hz_party_number_s.nextval
284 -- INTO p_organization_rec.party_rec.party_number
285 -- FROM dual;
286 NULL;
287 ELSE
288 p_organization_rec.party_rec.party_number := '13-583-0177';
289 END IF;
290
291 HZ_PARTY_V2PUB.create_organization(
292 p_init_msg_list => 'T',
293 p_organization_rec => p_organization_rec,
294 x_return_status => l_return_status,
295 x_msg_count => l_msg_count,
296 x_msg_data => l_msg_data,
297 x_party_id => l_party_id,
298 x_party_number => l_party_number,
299 x_profile_id => l_profile_id);
300
301 -- DBMS_OUTPUT.PUT_LINE(SubStr('x_return_status = '||l_return_status,1,255));
302 -- DBMS_OUTPUT.PUT_LINE(SubStr('x_msg_count = '||TO_CHAR(l_msg_count), 1, 255));
303 -- DBMS_OUTPUT.PUT_LINE(SubStr('x_msg_data = '||l_msg_data,1,255));
304
305 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
306 /*Insert into zx_party_tax_profile*/
307 insert_ptp (p_party_id => l_party_id,
308 p_ptp_id => 2,
309 x_return_status => l_return_status
310 );
311 END IF;
312 END IF;
313 END;
314
315 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
316 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'()-');
317 END IF;
318 END CREATE_TAXWARE_TCA_ZX;
319
320 -- Procedure to create TCA Party and ZX Party Tax Profile record for Other Tax Partners --
321 PROCEDURE CREATE_OTHERS_TCA_ZX(x_return_status OUT NOCOPY VARCHAR2) IS
322 l_api_name CONSTANT VARCHAR2(50) := 'CREATE_OTHERS_TCA_ZX';
323 p_organization_rec HZ_PARTY_V2PUB.organization_rec_type;
324 l_return_status VARCHAR2(2000);
325 l_msg_count NUMBER;
326 l_msg_data VARCHAR2(2000);
327 l_party_id NUMBER;
328 l_party_number VARCHAR2(2000);
329 l_profile_id NUMBER;
330 dummy NUMBER;
331 l_exists NUMBER;
332
333 BEGIN
334 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
335 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
336 END IF;
337
338 x_return_status := FND_API.G_RET_STS_SUCCESS;
339
340 BEGIN
341 SELECT 1
342 INTO l_exists
343 FROM zx_party_tax_profile
344 WHERE party_tax_profile_id = 3;
345
346 EXCEPTION
347 WHEN NO_DATA_FOUND THEN
348 -- For organization record
349 p_organization_rec.organization_name := 'Other Tax Partner';
350 p_organization_rec.duns_number_c := '';
351 p_organization_rec.created_by_module := 'EBTAX_SERVICE_PROVIDER';
352 p_organization_rec.application_id := 235;
353
354 BEGIN
355 SELECT 1
356 INTO dummy
357 FROM hz_parties
358 WHERE party_number = '';
359 EXCEPTION WHEN NO_DATA_FOUND THEN
360 NULL;
361 END;
362
363 IF NVL(dummy,0) <> 1 THEN
364 IF l_gen_party_number IS NULL OR l_gen_party_number = 'Y' THEN
365 NULL;
366 ELSE
367 p_organization_rec.party_rec.party_number := '';
368 END IF;
369
370 HZ_PARTY_V2PUB.create_organization(
371 p_init_msg_list => 'T',
372 p_organization_rec => p_organization_rec,
373 x_return_status => l_return_status,
374 x_msg_count => l_msg_count,
375 x_msg_data => l_msg_data,
376 x_party_id => l_party_id,
377 x_party_number => l_party_number,
378 x_profile_id => l_profile_id);
379
380 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
381 /*Insert into zx_party_tax_profile*/
382 insert_ptp (p_party_id => l_party_id,
383 p_ptp_id => 3,
384 x_return_status => l_return_status
385 );
386 END IF;
387 END IF;
388 END;
389
390 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
391 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'()-');
392 END IF;
393 END CREATE_OTHERS_TCA_ZX;
394
395 -- Procedure to create TCA Party and ZX Party Tax Profile record for CCH --
396 PROCEDURE CREATE_CCH_TCA_ZX(x_return_status OUT NOCOPY VARCHAR2) IS
397 l_api_name CONSTANT VARCHAR2(50) := 'CREATE_CCH_TCA_ZX';
398 p_organization_rec HZ_PARTY_V2PUB.organization_rec_type;
399 l_return_status VARCHAR2(2000);
400 l_msg_count NUMBER;
401 l_msg_data VARCHAR2(2000);
402 l_party_id NUMBER;
403 l_party_number VARCHAR2(2000);
404 l_profile_id NUMBER;
405 dummy NUMBER;
406 l_exists NUMBER;
407
408 BEGIN
409 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
410 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
411 END IF;
412
413 x_return_status := FND_API.G_RET_STS_SUCCESS;
414
415 BEGIN
416 SELECT 1
417 INTO l_exists
418 FROM zx_party_tax_profile
419 WHERE party_tax_profile_id = 4;
420
421 EXCEPTION
422 WHEN NO_DATA_FOUND THEN
423 -- For organization record
427 p_organization_rec.application_id := 235;
424 p_organization_rec.organization_name := 'CCH Inc';
425 p_organization_rec.duns_number_c := '';
426 p_organization_rec.created_by_module := 'EBTAX_SERVICE_PROVIDER';
428
429 BEGIN
430 SELECT 1
431 INTO dummy
432 FROM hz_parties
433 WHERE party_number = '';
434 EXCEPTION WHEN NO_DATA_FOUND THEN
435 NULL;
436 END;
437
438 IF NVL(dummy,0) <> 1 THEN
439 IF l_gen_party_number IS NULL OR l_gen_party_number = 'Y' THEN
440 NULL;
441 ELSE
442 p_organization_rec.party_rec.party_number := '';
443 END IF;
444
445 HZ_PARTY_V2PUB.create_organization(
446 p_init_msg_list => 'T',
447 p_organization_rec => p_organization_rec,
448 x_return_status => l_return_status,
449 x_msg_count => l_msg_count,
450 x_msg_data => l_msg_data,
451 x_party_id => l_party_id,
452 x_party_number => l_party_number,
453 x_profile_id => l_profile_id);
454
455 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
456 /*Insert into zx_party_tax_profile*/
457 insert_ptp (p_party_id => l_party_id,
458 p_ptp_id => 4,
459 x_return_status => l_return_status
460 );
461 END IF;
462 END IF;
463 END;
464
465 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
466 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'()-');
467 END IF;
468 END CREATE_CCH_TCA_ZX;
469
470 -- Public procedure to create TCA and ZX parties for Tax Partners --
471 PROCEDURE MIGRATE_TAX_PARTNER (x_return_status OUT NOCOPY VARCHAR2) IS
472 l_api_name CONSTANT VARCHAR2(30) := 'MIGRATE_TAX_PARTNER';
473 l_return_status VARCHAR2(1);
474 l_dss_enabled VARCHAR2(5);
475
476 BEGIN
477 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
478 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
479 END IF;
480
481 x_return_status := FND_API.G_RET_STS_SUCCESS;
482
483 --Get the profile value to determine if need to pass the party number
484 fnd_profile.get('HZ_GENERATE_PARTY_NUMBER', l_gen_party_number);
485 l_dss_enabled := fnd_profile.value('HZ_DSS_ENABLED');
486 fnd_profile.put('HZ_DSS_ENABLED','N');
487
488 /*Create Vertex as TCA party*/
489 CREATE_VERTEX_TCA_ZX (x_return_status);
490 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
491 x_return_status := FND_API.G_RET_STS_ERROR ;
492 FND_MESSAGE.SET_NAME ('ZX','GENERIC_MESSAGE');
493 FND_MESSAGE.SET_TOKEN ('GENERIC_TEXT','Creating Vertex as Organization Party in TCA and PTP in eBTax : '||SQLERRM);
494 FND_MSG_PUB.Add;
495 END IF;
496
497 /*Create Taxware as TCA party*/
498 CREATE_TAXWARE_TCA_ZX (l_return_status);
499 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
500 x_return_status := FND_API.G_RET_STS_ERROR ;
501 FND_MESSAGE.SET_NAME ('ZX','GENERIC_MESSAGE');
502 FND_MESSAGE.SET_TOKEN ('GENERIC_TEXT','Creating Taxware as Organization Party in TCA and PTP in eBTax: '||SQLERRM);
503 FND_MSG_PUB.Add;
504 END IF;
505
506 /*Create Others as TCA party*/
507 CREATE_OTHERS_TCA_ZX (l_return_status);
508 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
509 x_return_status := FND_API.G_RET_STS_ERROR ;
510 FND_MESSAGE.SET_NAME ('ZX','GENERIC_MESSAGE');
511 FND_MESSAGE.SET_TOKEN ('GENERIC_TEXT','Creating Other Tax Partner as Organization Party in TCA and PTP in eBTax: '||SQLERRM);
512 FND_MSG_PUB.Add;
513 END IF;
514
515 /*Create CCH as TCA party*/
516 CREATE_CCH_TCA_ZX (l_return_status);
517 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
518 x_return_status := FND_API.G_RET_STS_ERROR ;
519 FND_MESSAGE.SET_NAME ('ZX','GENERIC_MESSAGE');
520 FND_MESSAGE.SET_TOKEN ('GENERIC_TEXT','Creating CCH as Organization Party in TCA and PTP in eBTax: '||SQLERRM);
521 FND_MSG_PUB.Add;
522 END IF;
523
524 fnd_profile.put('HZ_DSS_ENABLED',l_dss_enabled);
525 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
526 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'()-');
527 END IF;
528
529 END MIGRATE_TAX_PARTNER;
530 END ZX_TAX_PARTNER_MIGRATE_PKG;