[Home] [Help]
PACKAGE BODY: APPS.ZX_PTP_CUST_MIG_PKG
Source
1 PACKAGE BODY ZX_PTP_CUST_MIG_PKG AS
2 /* $Header: zxptpcustmigb.pls 120.22 2005/07/25 07:45:36 asengupt ship $ */
3
4
5 l_Created_By zx_party_tax_profile.created_by%type := fnd_global.user_id;
6 l_Creation_Date zx_party_tax_profile.creation_date%type := SYSDATE;
7 l_Last_Updated_By zx_party_tax_profile.last_updated_by%type := fnd_global.user_id;
8 l_Last_Update_Date zx_party_tax_profile.last_update_date%type := SYSDATE;
9 l_Last_Update_Login zx_party_tax_profile.last_update_login%type := FND_GLOBAL.CONC_LOGIN_ID;
10 l_Request_Id zx_party_tax_profile.request_id%type := FND_GLOBAL.CONC_REQUEST_ID;
11 l_Program_Application_Id zx_party_tax_profile.program_application_id%type := 235;
12 l_Program_Id zx_party_tax_profile.program_id%type := FND_GLOBAL.CONC_PROGRAM_ID;
13 l_Program_Login_Id zx_party_tax_profile.program_login_id%type := FND_GLOBAL.CONC_LOGIN_ID;
14
15 ----The variables added below enable the ad_parallel_update feature to be used to imporve performance
16 l_worker_id NUMBER;
17 l_num_workers NUMBER;
18 l_table_owner VARCHAR2(30);
19 l_batch_size VARCHAR2(30);
20 l_any_rows_to_process BOOLEAN;
21 l_table_name VARCHAR2(30);
22 l_script_name VARCHAR2(30);
23 l_start_rowid ROWID;
24 l_end_rowid ROWID;
25 l_rows_processed NUMBER;
26
27
28
29
30 /*=========================================================================+
31 | PROCEDURE |
32 | CUSTOMER_MIGRATE |
33 | |
34 | DESCRIPTION |
35 | This procedure is used to create party tax profiles and |
36 | registrations for customers based on their accounts. |
37 | SCOPE - PUBLIC |
38 | |
39 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
40 | |
41 | CALLED FROM |
42 | ZX_PTP_CUST_MIG |
43 | |
44 | NOTES |
45 | |
46 | MODIFICATION HISTORY |
47 | 20-Jul-04 Ranjith Palani,Arnab Sengupta Created. |
48 | |
49 |=========================================================================*/
50
51 PROCEDURE CUSTOMER_MIGRATE IS
52 BEGIN
53
54 arp_util_tax.debug(' CUSTOMER_MIGRATE (+) ' );
55
56 arp_util_tax.debug(' Creating Customer PTP ' );
57
58
59 INSERT ALL
60 INTO
61 ZX_PARTY_TAX_PROFILE (
62 Party_Tax_Profile_Id
63 ,Party_Id
64 ,Rep_Registration_Number --BugFix 4054814
65 ,Registration_Type_Code
66 ,Country_Code
67 ,Party_Type_Code
68 ,Customer_Flag
69 ,First_Party_Le_Flag
70 ,Supplier_Flag
71 ,Site_Flag
72 ,Legal_Establishment_Flag
73 ,Rounding_Level_code --rp
74 ,Process_For_Applicability_Flag
75 ,Rounding_Rule_Code --rp
76 ,Inclusive_Tax_Flag
77 ,Use_Le_As_Subscriber_Flag
78 ,Effective_From_Use_Le
79 ,Reporting_Authority_Flag
80 ,Collecting_Authority_Flag
81 ,Provider_Type_Code
82 ,RECORD_TYPE_CODE
83 ,Tax_Classification_Code
84 ,Self_Assess_Flag
85 ,Allow_Offset_Tax_Flag
86 ,Allow_Awt_Flag
87 ,Created_By
88 ,Creation_Date
89 ,Last_Updated_By
90 ,Last_Update_Date
91 ,Last_Update_Login
92 ,REQUEST_ID
93 ,PROGRAM_APPLICATION_ID
94 ,PROGRAM_ID
95 ,PROGRAM_LOGIN_ID
96 ,OBJECT_VERSION_NUMBER
97 )
98 VALUES
99 (
100 ZX_PARTY_TAX_PROFILE_S.NEXTVAL
101 ,PARTY_ID -- Party ID
102 ,TAX_REFERENCE -- Rep Registration Number
103 ,NULL -- Registration Type
104 ,COUNTRY_CODE -- Country Code
105 ,'THIRD_PARTY' -- Party Type Bug 4381583
106 ,'Y' -- Customer_Flag
107 ,'N' -- First Party
108 ,'N' -- Suppliers
109 ,'N' -- Site
110 ,'N' -- Establishment
111 ,'LINE' -- Rounding Level
112 ,'Y' -- Process_For_Applicability_Flag
113 ,'NEAREST'
114 ,'N'
115 ,'N' -- Use_Le_As_Subscriber_Flag
116 ,NULL -- Effective_From_Use_Le
117 ,'N' -- Reporting Authority Flag
118 ,'N' -- Collecting Authority Flag
119 ,NULL -- Provider Type
120 ,'MIGRATED' -- Record Type
121 ,NULL -- Tax Classification
122 ,'N' -- Self_Assess_Flag
123 ,'N' -- Allow_Offset_Tax_Flag
124 ,'N' -- Allow_AWT_Flag
125 ,l_Created_By
126 ,l_Creation_Date
127 ,l_Last_Updated_By
128 ,l_Last_Update_Date
129 ,l_Last_Update_Login
130 ,l_Request_Id
131 ,l_Program_Application_Id
132 ,l_Program_Id
133 ,l_Program_Login_Id
134 ,1
135 )
136
137 SELECT
138 HZP.PARTY_ID PARTY_ID,
139 HZP.Tax_Reference TAX_REFERENCE,
140 HZP.COUNTRY COUNTRY_CODE
141
142 FROM
143 HZ_PARTIES HZP
144 WHERE
145 (HZP.PARTY_TYPE = 'ORGANIZATION'
146 OR HZP.PARTY_TYPE = 'PERSON')
147 AND not exists (select 1 from zx_party_tax_profile
148 where party_id = HZP.PARTY_ID and Party_Type_Code = 'THIRD_PARTY'
149 );
150
151
152 arp_util_tax.debug(' CUSTOMER_MIGRATE (-) ' );
153
154
155 EXCEPTION
156 WHEN OTHERS THEN
157 arp_util_tax.debug('Exception: Error Occurred during PTP Customer '|| SQLERRM );
158
159 END CUSTOMER_MIGRATE;
160
161
162 /*=========================================================================+
163 | PROCEDURE |
164 | CUSTOMER_SITE_MIGRATE |
165 | |
166 | DESCRIPTION |
167 | This procedure is used to create party tax profiles and |
168 | registrations for customers based on their account sites. |
169 | SCOPE - PUBLIC |
170 | |
171 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
172 | |
173 | CALLED FROM |
174 | ZX_PTP_CUST_MIG |
175 | |
176 | NOTES |
177 | |
178 | MODIFICATION HISTORY |
179 | 20-Jul-04 Ranjith Palani,Arnab Sengupta Created. |
180 | |
181 |=========================================================================*/
182
183
184 PROCEDURE CUSTOMER_SITE_MIGRATE IS
185
186 l_status fnd_module_installations.status%TYPE;
187 l_db_status fnd_module_installations.DB_STATUS%TYPE;
188
189 BEGIN
190 arp_util_tax.debug(' CUSTOMER_SITE_MIGRATE(+) ' );
191 arp_util_tax.debug(' Creating customer site PTP ' );
192
193
194 INSERT
195 INTO
196 ZX_PARTY_TAX_PROFILE(
197 Party_Tax_Profile_Id
198 ,Party_Id
199 ,Rep_Registration_Number --BugFix 4054875
200 ,Registration_Type_Code
201 ,Country_Code
202 ,Party_Type_code
203 ,Customer_Flag
204 ,First_Party_Le_Flag
205 ,Supplier_Flag
206 ,Site_Flag
207 ,Legal_Establishment_Flag
208 ,Rounding_Level_code
209 ,Process_For_Applicability_Flag
210 ,Rounding_Rule_Code
211 ,Inclusive_Tax_Flag
212 ,Use_Le_As_Subscriber_Flag
213 ,Effective_From_Use_Le
214 ,Reporting_Authority_Flag
215 ,Collecting_Authority_Flag
216 ,PROVIDER_TYPE_CODE
217 ,RECORD_TYPE_CODE
218 ,TAX_CLASSIFICATION_CODE
219 ,Self_Assess_Flag
220 ,ALLOW_AWT_FLAG
221 ,Allow_Offset_Tax_Flag
222 ,Created_By
223 ,Creation_Date
224 ,Last_Updated_By
225 ,Last_Update_Date
226 ,Last_Update_Login
227 ,REQUEST_ID
228 ,PROGRAM_APPLICATION_ID
229 ,PROGRAM_ID
230 ,PROGRAM_LOGIN_ID
231 ,OBJECT_VERSION_NUMBER
232 )
233 SELECT
234 ZX_PARTY_TAX_PROFILE_S.NEXTVAL
235 ,HZPS.PARTY_SITE_ID -- Party ID
236 ,PTP.Rep_Registration_Number --- Rep Registration
237 ,NULL
238 ,PTP.COUNTRY_CODE
239 ,'THIRD_PARTY_SITE' -- Party Type Bug 4381583
240 ,'Y' -- Customer_Flag
241 ,'N' -- First Party
242 ,'N' -- Suppliers
243 ,'Y' -- Site
244 ,'N' -- Establishment
245 ,PTP.Rounding_Level_code
246 ,'Y' -- Process_For_Applicability_Flag
247 ,PTP.Rounding_Rule_Code
248 ,'N'
249 ,'N' -- Use_Le_As_Subscriber_Flag
250 ,NULL -- Effective_From_Use_Le
251 ,'N' -- Reporting Authority Flag
252 ,'N' -- Collecting Authority Flag
253 , NULL -- Provider Type
254 ,'MIGRATED' -- Record Type
255 , NULL
256 ,'N' -- Self_Assess_Flag
257 ,'N' -- Allow_Offset_Tax_Flag
258 ,'N' --Allow_AWT_Flag
259 ,l_Created_By
260 ,l_Creation_Date
261 ,l_Last_Updated_By
262 ,l_Last_Update_Date
263 ,l_Last_Update_Login
264 ,l_Request_Id
265 ,l_Program_Application_Id
266 ,l_Program_Id
267 ,l_Program_Login_Id
268 ,1
269 FROM
270
271 ZX_PARTY_TAX_PROFILE PTP,
272 HZ_PARTY_SITES HZPS
273
274 WHERE
275 PTP.party_id = HZPS.party_id
276 AND PTP.Party_Type_Code = 'CUSTOMER'
277 AND not exists ( select 1 from zx_party_tax_profile
278 where party_id = HZPS.Party_Site_Id
279 and Party_Type_Code = 'THIRD_PARTY_SITE');
280
281
282
283 EXCEPTION
284 When Others then
285 arp_util_tax.debug('Exception: Error in Cust Site Migration' || SQLERRM );
286 arp_util_tax.debug(' CUSTOMER_SITE_USES Registration creation (-) ' );
287
288
289 END CUSTOMER_SITE_MIGRATE ;
290
291
292 /*=========================================================================+
293 | PROCEDURE |
294 | Create_Lookups |
295 | |
296 | DESCRIPTION |
297 | This procedure is used to create lookups for zx_ptptr_geo_type_class|
298 | SCOPE - PUBLIC |
299 | |
300 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
301 | |
302 | CALLED FROM |
303 | ZX_PTP_CUST_MIG |
304 | |
305 | NOTES |
306 | |
307 | MODIFICATION HISTORY |
308 | 20-Jul-04 Ranjith Palani,Arnab Sengupta Created. |
309 | |
310 |=========================================================================*/
311
312
316 arp_util_tax.debug('Create_Lookup(+)');
313 PROCEDURE Create_Lookups IS
314 BEGIN
315 IF PG_DEBUG = 'Y' THEN
317 END IF;
318
319 INSERT ALL
320 WHEN (NOT EXISTS
321 (SELECT 1 FROM FND_LOOKUP_TYPES
322 WHERE LOOKUP_TYPE = 'ZX_PTPTR_GEO_TYPE_CLASS')
323 ) THEN
324 INTO FND_LOOKUP_TYPES
325 (
326 APPLICATION_ID ,
327 LOOKUP_TYPE ,
328 CUSTOMIZATION_LEVEL ,
329 SECURITY_GROUP_ID ,
330 VIEW_APPLICATION_ID ,
331 CREATION_DATE ,
332 CREATED_BY ,
333 LAST_UPDATE_DATE ,
334 LAST_UPDATED_BY ,
335 LAST_UPDATE_LOGIN
336 )
337 VALUES
338 (
339 235 ,
340 'ZX_PTPTR_GEO_TYPE_CLASS' ,
341 'E' ,
342 0 ,
343 0 ,
344 SYSDATE ,
345 fnd_global.user_id ,
346 SYSDATE ,
347 fnd_global.user_id ,
348 fnd_global.conc_login_id
349 )
350 SELECT 1 FROM DUAL;
351
352 INSERT INTO FND_LOOKUP_TYPES_TL
353 (
354 LOOKUP_TYPE,
355 SECURITY_GROUP_ID,
356 VIEW_APPLICATION_ID,
357 LANGUAGE,
358 SOURCE_LANG,
359 MEANING,
360 DESCRIPTION,
361 CREATED_BY,
362 CREATION_DATE,
363 LAST_UPDATED_BY,
364 LAST_UPDATE_DATE,
365 LAST_UPDATE_LOGIN
366 )
367 SELECT
368 types.lookup_type,
369 0 ,--SECURITY_GROUP_ID
370 0 ,--VIEW_APPLICATION_ID
371 L.LANGUAGE_CODE ,
372 userenv('LANG') ,
373 'Tax Registrations Geo Type Classification',--MEANING
374 'This lookup type has been created to migrate HZ_CUST_SITE_USES.TAX_CLASSIFICATION to tax registrations.' ,--DESCRIPTION
375 fnd_global.user_id ,
376 SYSDATE ,
377 fnd_global.user_id ,
378 SYSDATE ,
379 fnd_global.conc_login_id
380 FROM FND_LOOKUP_TYPES types, FND_LANGUAGES L
381 WHERE L.INSTALLED_FLAG in ('I', 'B')
382 AND types.lookup_type = 'ZX_PTPTR_GEO_TYPE_CLASS'
383 AND not exists
384 (select '1'
385 from fnd_lookup_types_tl sub
386 where sub.lookup_type = 'ZX_PTPTR_GEO_TYPE_CLASS'
387 and sub.security_group_id = 0
388 and sub.view_application_id = 0
389 and sub.language = l.language_code);
390
391 INSERT INTO FND_LOOKUP_VALUES
392 (
393 LOOKUP_TYPE ,
394 LANGUAGE ,
395 LOOKUP_CODE ,
396 MEANING ,
397 DESCRIPTION ,
398 ENABLED_FLAG ,
399 START_DATE_ACTIVE ,
400 END_DATE_ACTIVE ,
401 SOURCE_LANG ,
402 SECURITY_GROUP_ID ,
403 VIEW_APPLICATION_ID ,
404 TERRITORY_CODE ,
405 ATTRIBUTE_CATEGORY ,
406 ATTRIBUTE1 ,
407 ATTRIBUTE2 ,
408 ATTRIBUTE3 ,
409 ATTRIBUTE4 ,
410 ATTRIBUTE5 ,
411 ATTRIBUTE6 ,
412 ATTRIBUTE7 ,
413 ATTRIBUTE8 ,
414 ATTRIBUTE9 ,
415 ATTRIBUTE10 ,
416 ATTRIBUTE11 ,
417 ATTRIBUTE12 ,
418 ATTRIBUTE13 ,
419 ATTRIBUTE14 ,
420 ATTRIBUTE15 ,
421 TAG ,
422 CREATION_DATE ,
423 CREATED_BY ,
424 LAST_UPDATE_DATE ,
425 LAST_UPDATED_BY ,
426 LAST_UPDATE_LOGIN
427 )
428 SELECT
429 'ZX_PTPTR_GEO_TYPE_CLASS',
430 l.language_code , -- LANGUAGE
431 lk.LOOKUP_CODE ,
432 lk.MEANING ,
433 lk.DESCRIPTION ,
434 'Y' ,--ENABLED_FLAG
435 lk.START_DATE_ACTIVE ,
436 NULL ,--END_DATE_ACTIVE
437 userenv('LANG') ,--SOURCE_LANG
438 0 ,--SECURITY_GROUP_ID
439 0 ,--VIEW_APPLICATION_ID
440 NULL ,--TERRITORY_CODE
441 NULL ,--ATTRIBUTE_CATEGORY
442 NULL ,--ATTRIBUTE1
443 NULL ,--ATTRIBUTE2
444 NULL ,--ATTRIBUTE3
445 NULL ,--ATTRIBUTE4
446 NULL ,--ATTRIBUTE5
447 NULL ,--ATTRIBUTE6
448 NULL ,--ATTRIBUTE7
449 NULL ,--ATTRIBUTE8
450 NULL ,--ATTRIBUTE9
451 NULL ,--ATTRIBUTE10
452 NULL ,--ATTRIBUTE11
453 NULL ,--ATTRIBUTE12
454 NULL ,--ATTRIBUTE13
455 NULL ,--ATTRIBUTE14
456 NULL ,--ATTRIBUTE15
457 NULL ,--TAG
458 SYSDATE ,
459 fnd_global.user_id ,
460 SYSDATE ,
461 fnd_global.user_id ,
462 fnd_global.conc_login_id
463 FROM FND_LOOKUP_VALUES lk, FND_LANGUAGES l
464 WHERE lk.lookup_type = 'AR_TAX_CLASSIFICATION'
465 and lk.language = 'US'
466 and l.installed_flag in ('I', 'B')
467 AND not exists
468 (select '1'
469 from FND_LOOKUP_VALUES
470 where lookup_code = lk.lookup_code
471 and lookup_type = 'ZX_PTPTR_GEO_TYPE_CLASS'
472 and language = l.LANGUAGE_CODE);
473
474 IF PG_DEBUG = 'Y' THEN
475 arp_util_tax.debug('Create_Lookup(-)');
476 END IF;
477
478 END Create_Lookups;
479
480
481
482 /*=========================================================================+
483 | PROCEDURE |
484 | ZX_PTP_CUST_MIG |
485 | |
486 | DESCRIPTION |
487 | This procedure is used as the driver procedure to call all the other|
488 | procdures |
489 | SCOPE - PUBLIC |
490 | |
491 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
492 | |
493 | CALLED FROM |
494 | |
495 | |
496 | NOTES |
497 | |
498 | MODIFICATION HISTORY |
499 | 20-Jul-04 Ranjith Palani,Arnab Sengupta Created. |
500 | |
501 |=========================================================================*/
502
503
504
505 PROCEDURE ZX_PTP_CUST_MIG IS
506 BEGIN
507
508 EXECUTE IMMEDIATE 'ALTER SEQUENCE ZX.ZX_PARTY_TAX_PROFILE_S CACHE 20000';
509
510 arp_util_tax.debug(' ZX_PTP_CUST_MIG... (+) ' );
511
512 arp_util_tax.debug(' Calling CREATE_LOOKUPS.. ' );
513
514 --ZX_PTP_CUST_MIG_PKG.CREATE_LOOKUPS;
515
516 arp_util_tax.debug(' Calling CUSTOMER_MIGRATE.. ' );
517
518 --ZX_PTP_CUST_MIG_PKG.CUSTOMER_MIGRATE;
519
520 arp_util_tax.debug(' Calling CUSTOMER_SITE_MIGRATE.. ' );
521
522 --ZX_PTP_CUST_MIG_PKG.CUSTOMER_SITE_MIGRATE;
523
524 EXECUTE IMMEDIATE 'ALTER SEQUENCE ZX.ZX_PARTY_TAX_PROFILE_S CACHE 20';
525
526 END ZX_PTP_CUST_MIG;
527
528
529 END ZX_PTP_CUST_MIG_PKG;