[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.14.12010000.2 2008/11/27 12:25:12 nisinha 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
136 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
137 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'()-');
138 END IF;
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 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
144 FND_MSG_PUB.add;
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
151 PROCEDURE CREATE_VERTEX_TCA_ZX (x_return_status OUT NOCOPY VARCHAR2) IS
152
153 l_api_name CONSTANT VARCHAR2(50) := 'CREATE_VERTEX_TCA_ZX';
154 p_organization_rec HZ_PARTY_V2PUB.organization_rec_type;
155 p_party_rec HZ_PARTY_V2PUB.party_rec_type;
156 l_return_status VARCHAR2(2000);
157 l_msg_count NUMBER;
158 l_msg_data VARCHAR2(2000);
159 l_party_id NUMBER;
160 l_party_number VARCHAR2(2000);
161 l_profile_id NUMBER;
162 l_org_contact_id NUMBER;
163 dummy NUMBER;
164 l_exists NUMBER;
165
166 BEGIN
167 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
168 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
169 END IF;
170
171 x_return_status := FND_API.G_RET_STS_SUCCESS;
172
173 SELECT 1
174 INTO dummy
175 FROM zx_party_tax_profile
176 where party_tax_profile_id = 1;
177
178 EXCEPTION
179 WHEN NO_DATA_FOUND THEN
180 -- For organization record
181 p_organization_rec.organization_name := 'Vertex Inc';
182 p_organization_rec.duns_number_c := '09-685-3189';
183 p_organization_rec.created_by_module := 'EBTAX_SERVICE_PROVIDER';
184 p_organization_rec.application_id := 235;
185
186
187 BEGIN
188 SELECT 1
189 INTO dummy
190 FROM hz_parties
191 WHERE party_number = '09-685-3189';
192
193 EXCEPTION WHEN NO_DATA_FOUND THEN
194 --TCA accepts party number only if profile option is N
195 NULL;
196 END;
197
198 IF nvl(dummy,0)<>1 THEN
199 IF l_gen_party_number is null OR l_gen_party_number = 'Y' THEN
200 /*select hz_party_number_s.nextval
201 into p_organization_rec.party_rec.party_number
202 from dual;
203 */
204 NULL;
205 ELSE
206 p_organization_rec.party_rec.party_number := '09-685-3189';
207 END IF;
208
209
210 HZ_PARTY_V2PUB.create_organization(
211 p_init_msg_list => 'T',
212 p_organization_rec => p_organization_rec,
213 x_return_status => l_return_status,
214 x_msg_count => l_msg_count,
215 x_msg_data => l_msg_data,
216 x_party_id => l_party_id,
217 x_party_number => l_party_number,
218 x_profile_id => l_profile_id);
219
220 /*
221 IF l_msg_count > 1 THEN
222 FOR i IN 1..l_msg_count LOOP
223 dbms_output.put_line(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ));
224 END LOOP;
225 END IF;
226 */
227 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
228 /*Insert into zx_party_tax_profile*/
229 insert_ptp (p_party_id => l_party_id,
230 p_ptp_id => 1,
231 x_return_status => l_return_status
232 );
233 END IF;
234
235 END IF;
236 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
237 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'()-');
238 END IF;
239 END CREATE_VERTEX_TCA_ZX;
240
241 PROCEDURE CREATE_TAXWARE_TCA_ZX(x_return_status OUT NOCOPY VARCHAR2) IS
242
243 l_api_name CONSTANT VARCHAR2(50) := 'CREATE_TAXWARE_TCA_ZX';
244 p_organization_rec HZ_PARTY_V2PUB.organization_rec_type;
245 p_party_rec HZ_PARTY_V2PUB.party_rec_type;
246 l_return_status VARCHAR2(2000);
247 l_msg_count NUMBER;
248 l_msg_data VARCHAR2(2000);
249 l_party_id NUMBER;
250 l_party_number VARCHAR2(2000);
251 l_profile_id NUMBER;
252 l_org_contact_id NUMBER;
253 dummy NUMBER;
254 l_exists NUMBER;
255
256 BEGIN
257 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
258 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
259 END IF;
260
261 x_return_status := FND_API.G_RET_STS_SUCCESS;
262
263 SELECT 1
264 INTO dummy
265 FROM zx_party_tax_profile
266 where party_tax_profile_id = 2;
267
268 EXCEPTION
269 WHEN NO_DATA_FOUND THEN
270 -- For organization record
271 p_organization_rec.organization_name := 'ADP Inc.';
272 p_organization_rec.duns_number_c := '13-583-0177';
273 p_organization_rec.created_by_module := 'EBTAX_SERVICE_PROVIDER';
274 p_organization_rec.application_id := 235;
275
276 BEGIN
277 SELECT 1
278 INTO dummy
279 FROM hz_parties
280 WHERE party_number = '13-583-0177';
281
282 EXCEPTION WHEN NO_DATA_FOUND THEN
283 --TCA accepts party number only if profile option is N
284 NULL;
285 END;
286
287 IF nvl(dummy,0)<>1 THEN
288 IF l_gen_party_number is null OR l_gen_party_number = 'Y' THEN
289 NULL;
290 /*
291 select hz_party_number_s.nextval
292 into p_organization_rec.party_rec.party_number
293 from dual;
294 */
295 ELSE
296 p_organization_rec.party_rec.party_number := '13-583-0177';
297 END IF;
298
299 HZ_PARTY_V2PUB.create_organization(
300 p_init_msg_list => 'T',
301 p_organization_rec => p_organization_rec,
302 x_return_status => l_return_status,
303 x_msg_count => l_msg_count,
304 x_msg_data => l_msg_data,
305 x_party_id => l_party_id,
306 x_party_number => l_party_number,
307 x_profile_id => l_profile_id);
308
309 /*dbms_output.put_line(SubStr('x_return_status = '||l_return_status,1,255));
310 dbms_output.put_line(SubStr('x_msg_count = '||TO_CHAR(l_msg_count), 1, 255));
311 dbms_output.put_line(SubStr('x_msg_data = '||l_msg_data,1,255));
312 */
313 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
314 /*Insert into zx_party_tax_profile*/
315 insert_ptp (p_party_id => l_party_id,
316 p_ptp_id => 2,
317 x_return_status => l_return_status
318 );
319 END IF;
320
321 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
322 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'()-');
323 END IF;
324 END IF;
325 END CREATE_TAXWARE_TCA_ZX;
326
327
328 PROCEDURE MIGRATE_TAX_PARTNER (x_return_status OUT NOCOPY VARCHAR2) IS
329 l_api_name CONSTANT VARCHAR2(30) := 'MIGRATE_TAX_PARTNER';
330 l_return_status VARCHAR2(1);
331 l_dss_enabled VARCHAR2(5);
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 --Get the profile value to determine if need to pass the party number
341 fnd_profile.get('HZ_GENERATE_PARTY_NUMBER', l_gen_party_number);
342 l_dss_enabled := fnd_profile.value('HZ_DSS_ENABLED');
343 fnd_profile.put('HZ_DSS_ENABLED','N');
344
345 /*Create Vertex as TCA party*/
346 CREATE_VERTEX_TCA_ZX (x_return_status);
347 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
348 x_return_status := FND_API.G_RET_STS_ERROR ;
349 FND_MESSAGE.SET_NAME ('ZX','GENERIC_MESSAGE');
350 FND_MESSAGE.SET_TOKEN ('GENERIC_TEXT','Creating Vertex as Organization Party in TCA and PTP in eBTax : '||SQLERRM);
351 FND_MSG_PUB.Add;
352 END IF;
353
354 /*Create Taxware as TCA party*/
355 CREATE_TAXWARE_TCA_ZX (l_return_status);
356 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
357 x_return_status := FND_API.G_RET_STS_ERROR ;
358 FND_MESSAGE.SET_NAME ('ZX','GENERIC_MESSAGE');
359 FND_MESSAGE.SET_TOKEN ('GENERIC_TEXT','Creating Taxware as Organization Party in TCA and PTP in eBTax: '||SQLERRM);
360 FND_MSG_PUB.Add;
361 END IF;
362
363 fnd_profile.put('HZ_DSS_ENABLED',l_dss_enabled);
364 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
365 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'()-');
366 END IF;
367
368 END MIGRATE_TAX_PARTNER;
369 END ZX_TAX_PARTNER_MIGRATE_PKG;
370
371