[Home] [Help]
PACKAGE BODY: APPS.ZX_PTP_MIGRATE_PKG
Source
1 PACKAGE BODY ZX_PTP_MIGRATE_PKG AS
2 /* $Header: zxptpmigb.pls 120.60 2010/10/27 14:38:50 ssanka ship $ */
3
4
5 l_multi_org_flag fnd_product_groups.multi_org_flag%type;
6 l_org_id NUMBER(15);
7
8
9 ------------The procedures declared below were created as part of bug fix 3722296------
10
11 PROCEDURE ZX_CREATE_REG(
12 p_reg_info varchar2,
13 p_ptp_id zx_party_tax_profile.party_tax_profile_id%type
14 );
15
16 PROCEDURE ZX_CREATE_REGISTRATIONS(
17 p_hr_org_reg_info varchar2,
18 p_hr_loc_reg_info varchar2,
19 p_ar_tax_reg_info varchar2,
20 p_fin_vat_reg_info varchar2,
21 p_ptp_id zx_party_tax_profile.party_tax_profile_id%type,
22 p_level number
23 );
24
25
26 -------------THIS PROCEDURE MUST NOT BE INVOKED FROM ANY WRAPPER ROUTINE MUST ONLY BE
27 -------------CALLED FROM REG_REP_DRIVER_PROC
28
29 PROCEDURE ZX_CREATE_REP_TYPE_ASSOC
30 (
31 p_reg_rec register_num_tab,
32 p_level NUMBER,
33 p_ptp_id zx_party_tax_profile.party_tax_profile_id%type,
34 p_hr_rep_type_info_lat varchar2,
35 p_hr_rep_type_info_kor varchar2,
36 p_hr_rep_type_info_eur_grc varchar2,
37 p_ar_tax_reg_info varchar2,
38 p_fin_vat_reg_info varchar2
39 );
40
41 --PROCEDURE REG_REP_DRIVER_PROC
42 --(p_party_type_code zx_party_tax_profile.party_type_code%type) ;
43
44 --PROCEDURE REG_REP_DRIVER_PROC_OU
45 --(p_party_type_code zx_party_tax_profile.party_type_code%type) ;
46
47 -----------------------------------------------------------------------------------------
48
49
50
51 /*=========================================================================+
52 | PROCEDURE |
53 | ZX_CREATE_REG |
54 | |
55 | DESCRIPTION |
56 | |
57 | Used to create the registrations |
58 | |
59 | |
60 | |
61 | |
62 | |
63 | SCOPE - PUBLIC |
64 | |
65 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
66 | |
67 | CALLED FROM |
68 | REG_REP_DRIVER_PROC |
69 | NOTES |
70 | |
71 | MODIFICATION HISTORY |
72 | 23-Sep-04 Arnab Sengupta Created. |
73 | Bugfix: 3722296 |
74 |=========================================================================*/
75
76 PROCEDURE ZX_CREATE_REG(
77 p_reg_info varchar2,
78 p_ptp_id zx_party_tax_profile.party_tax_profile_id%type
79 )
80 IS
81 BEGIN
82
83
84 arp_util_tax.debug('ZX_CREATE_REG(+)');
85
86
87 INSERT
88 INTO ZX_REGISTRATIONS (
89 Registration_Id,
90 Registration_Type_Code,
91 Registration_Number,
92 Registration_Status_Code,
93 Registration_Source_Code,
94 Registration_Reason_Code,
95 Party_Tax_Profile_Id,
96 Tax_Classification_Code,
97 Tax_Authority_Id,
98 Coll_Tax_Authority_Id,
99 Rep_Tax_Authority_Id,
100 Tax,
101 Tax_Regime_Code,
102 Rounding_Rule_Code,
103 Tax_Jurisdiction_Code,
104 Self_Assess_Flag,
105 Inclusive_Tax_Flag,
106 Effective_From,
107 Effective_To,
108 Rep_Party_Tax_Name,
109 Legal_Registration_Id,
110 Default_Registration_Flag,
111 Account_Id,
112 RECORD_TYPE_CODE,
113 Created_By,
114 Creation_Date,
115 Last_Updated_By,
116 Last_Update_Date,
117 Last_Update_Login,
118 PROGRAM_APPLICATION_ID,
119 REQUEST_ID,
120 PROGRAM_ID,
121 PROGRAM_LOGIN_ID,
122 OBJECT_VERSION_NUMBER)
123 SELECT
124
125 ZX_REGISTRATIONS_S.NEXTVAL, --Registration Id
126 NULL, --Registration_Type_Code
127 p_reg_info , --Registration Number
128 'REGISTERED', --Registration Status Code
129 'EXPLICIT', --Registration Source Code
130 NULL, --Registration Reason Code
131 p_ptp_id, --Party Tax Profile Id
132 NULL, --Tax_Classification_Code
133 NULL, --Tax_Authority_Id,
134 NULL, --Coll_Tax_Authority_Id
135 NULL, --Rep_Tax_Authority_Id
136 NULL, --Tax
137 NULL, --Tax Regime Code
138 ROUNDING_RULE_CODE,--Rounding Rule Code
139 NULL, --Tax Jurisdiction Code
140 SELF_ASSESS_FLAG, --Self Assess Flag
141 INCLUSIVE_TAX_FLAG, --Inclusive Tax Flag
142 SYSDATE, --Effective From
143 NULL, --Effective To
144 NULL, --Rep_Party_Tax_Name
145 NULL, --Legal Registration Id
146 'Y', --Default Registration Flag
147 NULL, --Account_Id
148 'MIGRATED', --Record Type Code
149 fnd_global.user_id, --Created By
150 SYSDATE, --Creation Date
151 fnd_global.user_id, --Last Updated By
152 SYSDATE, --Last Update Date
153 FND_GLOBAL.CONC_LOGIN_ID, --Last_Update_Login
154 NULL, --Program Application Id
155 FND_GLOBAL.CONC_REQUEST_ID, --REQUEST_ID
156 FND_GLOBAL.CONC_PROGRAM_ID, --PROGRAM_ID
157 FND_GLOBAL.CONC_LOGIN_ID , --PROGRAM_LOGIN_ID
158 1
159
160 FROM ZX_PARTY_TAX_PROFILE
161
162 WHERE PARTY_TAX_PROFILE_ID=p_ptp_id
163 AND NOT EXISTS
164 (SELECT 1 FROM ZX_REGISTRATIONS WHERE REGISTRATION_NUMBER=p_reg_info
165 AND party_tax_profile_id = p_ptp_id);
166
167 arp_util_tax.debug('ZX_CREATE_REG(-)');
168
169
170 END ZX_CREATE_REG;
171
172
173 /*=========================================================================+
174 | PROCEDURE |
175 | ZX_CREATE_REGISTRATIONS |
176 | |
177 | DESCRIPTION |
178 | |
179 | Used to create the registrations for various entities like |
180 | 1.HR Organization Information |
181 | 2.Hr Locations |
182 | 3.AR System Parameters All |
183 | 4.Financial Systems Parameters All |
184 | |
185 | SCOPE - PUBLIC |
186 | |
187 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
188 | |
189 | CALLED FROM |
190 | REG_REP_DRIVER_PROC |
191 | NOTES |
192 | |
193 | MODIFICATION HISTORY |
194 | 23-Sep-04 Arnab Sengupta Created. |
195 | Bugfix: 3722296 |
196 |=========================================================================*/
197
198
199
200 PROCEDURE ZX_CREATE_REGISTRATIONS(
201 p_hr_org_reg_info varchar2,
202 p_hr_loc_reg_info varchar2,
203 p_ar_tax_reg_info varchar2,
204 p_fin_vat_reg_info varchar2,
205 p_ptp_id zx_party_tax_profile.party_tax_profile_id%type,
206 p_level number
207 )
208 IS
209 BEGIN
210
211
212 arp_util_tax.debug('ZX_CREATE_REGISTRATIONS(+)');
213
214 IF p_ptp_id is not null THEN
215
216 IF p_level = 1 THEN
217
218 ZX_CREATE_REG(p_hr_org_reg_info,p_ptp_id);
219
220 ELSIF p_level = 2 THEN
221
222 ZX_CREATE_REG(p_hr_loc_reg_info,p_ptp_id);
223
224 ELSIF p_level = 3 THEN
225
226 ZX_CREATE_REG( p_ar_tax_reg_info,p_ptp_id);
227
228
229 ELSIF p_level = 4 THEN
230
231 ZX_CREATE_REG( p_fin_vat_reg_info,p_ptp_id);
232
233 END IF;
234
235
236 arp_util_tax.debug('ZX_CREATE_REGISTRATIONS(-)');
237
238 END IF;
239 END ZX_CREATE_REGISTRATIONS;
240
241 /*=========================================================================+
242 | PROCEDURE |
243 | ZX_CREATE_REP_TYPE_ASSOC |
244 | |
245 | DESCRIPTION |
246 | |
247 | Used to create the reporting type associations |
248 | for various entities like |
249 | 1.HR Organization Information |
250 | 2.Hr Locations |
251 | 3.AR System Parameters All |
252 | 4.Financial Systems Parameters All |
253 | |
254 | SCOPE - PUBLIC |
255 | |
256 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
257 | |
258 | CALLED FROM |
259 | REG_REP_DRIVER_PROC |
260 | NOTES |
261 | |
262 | MODIFICATION HISTORY |
263 | 23-Sep-04 Arnab Sengupta Created. |
264 | Bugfix: 3722296 |
265 |=========================================================================*/
266
267
268 PROCEDURE ZX_CREATE_REP_TYPE_ASSOC
269 (
270 p_reg_rec register_num_tab,
271 p_level NUMBER,
272 p_ptp_id zx_party_tax_profile.party_tax_profile_id%type,
273 p_hr_rep_type_info_lat varchar2,
274 p_hr_rep_type_info_kor varchar2,
275 p_hr_rep_type_info_eur_grc varchar2,
276 p_ar_tax_reg_info varchar2,
277 p_fin_vat_reg_info varchar2)
278
279 IS
280
281 I NUMBER;
282 BEGIN
283
284 arp_util_tax.debug('ZX_CREATE_REP_TYPE_ASSOC(+)');
285
286 IF p_ptp_id IS NOT NULL THEN
287
288 IF p_level =2 THEN
289
290 ZX_MIGRATE_REP_ENTITIES_PKG.ZX_CREATE_REP_ASSOCIATION_PTP
291 (p_hr_rep_type_info_lat,p_ptp_id,'JL - REG NUMBER');
292 ZX_MIGRATE_REP_ENTITIES_PKG.ZX_CREATE_REP_ASSOCIATION_PTP
293 (p_hr_rep_type_info_kor,p_ptp_id,'JA - REG NUMBER');
294 ZX_MIGRATE_REP_ENTITIES_PKG.ZX_CREATE_REP_ASSOCIATION_PTP
295 (p_hr_rep_type_info_eur_grc,p_ptp_id,'JE - REG NUMBER');
296
297 ELSIF p_level = 3 THEN
298 ZX_MIGRATE_REP_ENTITIES_PKG.ZX_CREATE_REP_ASSOCIATION_PTP
299 (p_ar_tax_reg_info,p_ptp_id,'AR-SYSTEM-PARAM-REG-NUM');
300
301 ELSIF p_level = 4 THEN
302 ZX_MIGRATE_REP_ENTITIES_PKG.ZX_CREATE_REP_ASSOCIATION_PTP
303 (p_fin_vat_reg_info,p_ptp_id,'FSO-REG-NUM');
304
305 END IF;
306
307 END IF;
308
309 arp_util_tax.debug('ZX_CREATE_REP_TYPE_ASSOC(-)');
310
311
312 END ZX_CREATE_REP_TYPE_ASSOC;
313
314
315 /*=========================================================================+
316 | PROCEDURE |
317 | REG_REP_DRIVER_PROC |
318 | |
319 | DESCRIPTION |
320 | This procedure contains the acutal logic for deciding |
321 | when to create a registrations and when to create |
322 | a reporting type association . |
323 | |
324 | |
325 | SCOPE - PUBLIC |
326 | |
327 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
328 | ZX_CREATE_REGISTRATIONS |
329 | ZX_CREATE_REP_TYPE_ASSOC |
330 | CALLED FROM |
331 | |
332 | NOTES |
333 | |
334 | MODIFICATION HISTORY |
335 | 23-Sep-04 Arnab Sengupta Created. |
336 | Bugfix: 3722296 |
337 |=========================================================================*/
338
339
340 PROCEDURE REG_REP_DRIVER_PROC(p_party_type_code zx_party_tax_profile.party_type_code%type)
341 IS
342
343 ---------------------Local variable declarations-----------------
344 l_first_reg_val varchar2(160);
345 l_ins_flag_one BOOLEAN; ----This flag is used to ensure that only one registration is created
346 l_ins_flag_two BOOLEAN;-----This flag is used to ensure that duplicate reporting code associations are not created
347 l_position_first_reg NUMBER;
348 C_LINES_PER_COMMIT NUMBER := 1000;
349
350 --------TABLE DECLARTIONS-----------------------------------
351 pg_org_reg_num_tab org_reg_num_tab;
352 pg_loc_reg_num_tab loc_reg_num_tab;
353 pg_ar_sys_reg_num_tab ar_sys_reg_num_tab;
354 pg_fin_sys_reg_num_tab fin_sys_reg_num_tab;
355 reg_num_tab register_num_tab;
356 ptp_tab party_tax_profile_tab;
357 hr_rep_info_lat_tab hr_org_rep_info_tab;
358 hr_rep_info_kor_tab hr_org_rep_info_tab;
359 hr_rep_info_eur_grc_tab hr_org_rep_info_tab;
360 ---------------Cursor Declarations-----------------------------
361
362 CURSOR C_GET_REG_NUMBERS IS
363 SELECT (SELECT HrOrgInfo.ORG_INFORMATION2
364 FROM Hr_Organization_Information HrOrgInfo
365 WHERE HrOrgInfo.Org_Information_Context = 'Legal Entity Accounting'
366 AND HrOrgInfo.organization_id = xep.legal_entity_id) ORG_REG_NUM,
367 decode(HrLoc.GLOBAL_ATTRIBUTE_CATEGORY,
368 'JL.AR.PERWSLOC.LOC',HrLoc.Global_Attribute11||hrloc.Global_Attribute12,
369 'JL.CL.PERWSLOC.LOC',HrLoc.Global_Attribute1) LOC_REG_NUM,
370 ARP.Tax_Registration_Number AR_SYS_REG_NUM,
371 Fso.Vat_Registration_Num FIN_SYS_REG_NUM,
372 PTP.Party_Tax_Profile_Id PTP_ID,
373 HrLoc.Global_Attribute11||HrLoc.Global_Attribute12 HR_REP_TYPE_INFO_LAT,
374 HrLoc.Global_Attribute1 HR_REP_TYPE_INFO_KOR,
375 HrLoc.Global_Attribute2 HR_REP_TYPE_INFO_EUR_GRC
376 FROM Hr_Locations_All HrLoc
377 ,xle_etb_profiles XEP
378 ,xle_associations xa
379 ,xle_association_types atype
380 ,xle_assoc_object_types ctype_obj
381 ,xle_assoc_object_types ctype_sub
382 ,Hr_All_Organization_Units HrOU
383 ,Hr_Organization_Information HrOuInfo
384 ,Financials_System_Params_All Fso
385 ,Ap_System_Parameters_All ASP
386 ,Ar_System_Parameters_All ARP
387 ,Zx_party_tax_profile ptp
388 WHERE XEP.legal_entity_id = HrOuInfo.ORG_INFORMATION2
389 AND XEP.establishment_id = xa.subject_id
390 and atype.association_type_id = xa.association_type_id
391 and atype.object_type_id = ctype_obj.object_type_id
392 and atype.subject_type_id = ctype_sub.object_type_id
393 and ctype_obj.name = 'OPERATING_UNIT'
394 AND xa.OBJECT_ID = HrOuInfo.organization_id
395 AND PTP.party_Id = XEP.party_id
396 AND PTP.party_type_code = 'LEGAL_ESTABLISHMENT'
397 AND decode(l_multi_org_flag,'N',l_org_id,HrOu.organization_id)
398 = decode(l_multi_org_flag,'N',l_org_id,HrOuInfo.organization_id(+))
399 AND HrOuInfo.Org_Information_Context = 'Operating Unit Information'
400 AND decode(l_multi_org_flag,'N',l_org_id,HrOu.organization_id)
401 = decode(l_multi_org_flag,'N',l_org_id,Fso.org_id(+))
402 AND decode(l_multi_org_flag,'N',l_org_id,HrOu.organization_id)
403 = decode(l_multi_org_flag,'N',l_org_id,ASP.org_id(+))
404 AND HrOU.location_id = HrLoc.location_id (+)
405 AND decode(l_multi_org_flag,'N',l_org_id,ASP.org_id)
406 = decode(l_multi_org_flag,'N',l_org_id,ARP.org_id(+));
407
408 BEGIN
409
410 arp_util_tax.debug('REG_REP_DRIVER_PROC(+)');
411
412 ------------INITIALIZING THE FLAGS--------------------------------
413 l_ins_flag_one:=FALSE;
414 --------------Fetch all records into PL sql tables-----------------
415 OPEN C_GET_REG_NUMBERS;
416
417 LOOP
418
419 FETCH C_GET_REG_NUMBERS BULK COLLECT INTO
420
421 pg_org_reg_num_tab,
422 pg_loc_reg_num_tab,
423 pg_ar_sys_reg_num_tab,
424 pg_fin_sys_reg_num_tab,
425 ptp_tab,
426 hr_rep_info_lat_tab,
427 hr_rep_info_kor_tab,
428 hr_rep_info_eur_grc_tab
429
430 LIMIT C_LINES_PER_COMMIT;
431
432 EXIT WHEN C_GET_REG_NUMBERS%NOTFOUND;
433
434 END LOOP;
435
436 CLOSE C_GET_REG_NUMBERS;
437
438 FOR i IN 1..nvl(pg_org_reg_num_tab.last, 0)
439 LOOP
440
441 reg_num_tab(1) :=pg_org_reg_num_tab(i);
442 reg_num_tab(2) :=pg_loc_reg_num_tab(i);
443 reg_num_tab(3) :=pg_ar_sys_reg_num_tab(i);
444 reg_num_tab(4) :=pg_fin_sys_reg_num_tab(i);
445
446 l_ins_flag_one:=FALSE;
447
448 FOR j in 1..4
449 LOOP
450 IF reg_num_tab(j) IS NOT NULL THEN
451
452 ZX_CREATE_REP_TYPE_ASSOC
453 (reg_num_tab
454 ,j
455 ,ptp_tab(i)
456 ,hr_rep_info_lat_tab(i)
457 ,hr_rep_info_kor_tab(i)
458 ,hr_rep_info_eur_grc_tab(i)
459 ,pg_ar_sys_reg_num_tab(i)
460 ,pg_fin_sys_reg_num_tab(i));
461 END IF;
462 END LOOP;
463
464 END LOOP;
465
466 arp_util_tax.debug('REG_REP_DRIVER_PROC(-)');
467 EXCEPTION
468 WHEN NO_DATA_FOUND THEN
469 NULL;
470 WHEN OTHERS THEN
471 arp_util_tax.debug('ERROR IN REG_REP_DRIVER_PROC');
472 END REG_REP_DRIVER_PROC;
473
474 /*=========================================================================+
475 | PROCEDURE |
476 | REG_REP_DRIVER_PROC_OU |
477 | |
478 | DESCRIPTION |
479 | This procedure contains the acutal logic for deciding |
480 | when to create a reporting type association . |
481 | |
482 | |
483 | SCOPE - PUBLIC |
484 | |
485 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
486 | ZX_CREATE_REP_TYPE_ASSOC |
487 | |
488 |=========================================================================*/
489
490
491 PROCEDURE REG_REP_DRIVER_PROC_OU(p_party_type_code zx_party_tax_profile.party_type_code%type)
492 IS
493 ---------------------Local variable declarations-----------------
494 l_first_reg_val varchar2(160);
495 l_position_first_reg NUMBER;
496 C_LINES_PER_COMMIT NUMBER := 1000;
497
498 --------TABLE DECLARTIONS-----------------------------------
499 pg_org_reg_num_tab org_reg_num_tab;
500 pg_loc_reg_num_tab loc_reg_num_tab;
501 pg_ar_sys_reg_num_tab ar_sys_reg_num_tab;
502 pg_fin_sys_reg_num_tab fin_sys_reg_num_tab;
503 reg_num_tab register_num_tab;
504 ptp_tab party_tax_profile_tab;
505 hr_rep_info_lat_tab hr_org_rep_info_tab;
506 hr_rep_info_kor_tab hr_org_rep_info_tab;
507 hr_rep_info_eur_grc_tab hr_org_rep_info_tab;
508
509 ---------------Cursor Declarations-----------------------------
510 CURSOR C_GET_REG_NUMBERS IS
511 SELECT
512 HrOrgInfo.ORG_INFORMATION2 ORG_REG_NUM,
513 decode(HrLoc.GLOBAL_ATTRIBUTE_CATEGORY, 'JL.AR.PERWSLOC.LOC',
514 HrLoc.Global_Attribute11,'JL.CL.PERWSLOC.LOC',
515 HrLoc.Global_Attribute1) LOC_REG_NUM,
516 ArSysParam.Tax_Registration_Number AR_SYS_REG_NUM,
517 FinSysParam.Vat_Registration_Num FIN_SYS_REG_NUM,
518 PTP.Party_Tax_Profile_Id PTP_ID,
519 HrLoc.Global_Attribute11||HrLoc.Global_Attribute12 HR_REP_TYPE_INFO_LAT,
520 HrLoc.Global_Attribute1 HR_REP_TYPE_INFO_KOR,
521 HrLoc.Global_Attribute2 HR_REP_TYPE_INFO_EUR_GRC
522 FROM
523 HR_ORGANIZATION_INFORMATION HrOrgInfo,
524 HR_LOCATIONS_ALL HrLoc,
525 FINANCIALS_SYSTEM_PARAMS_ALL FinSysParam,
526 AR_SYSTEM_PARAMETERS_ALL ArSysParam,
527 HR_ALL_ORGANIZATION_UNITS HrOrgUnits,
528 ZX_PARTY_TAX_PROFILE PTP
529 WHERE
530 nvl(ptp.Party_Type_code,p_party_type_code) = p_party_type_code
531 and ptp.party_id (+) = HrOrgInfo.organization_id
532 and decode(l_multi_org_flag,'N',l_org_id,HrOrgUnits.organization_id) =
533 decode(l_multi_org_flag,'N',l_org_id,HrOrgInfo.organization_id(+))
534 and nvl(HrOrgInfo.ORG_INFORMATION_CONTEXT,'Legal Entity Accounting') =
535 'Legal Entity Accounting'
536 and HrOrgUnits.location_id = HrLoc.location_id (+)
537 and decode(l_multi_org_flag,'N',l_org_id,HrOrgUnits.organization_id) =
538 decode(l_multi_org_flag,'N',l_org_id,ArSysParam.org_id(+))
539 and decode(l_multi_org_flag,'N',l_org_id,HrOrgUnits.organization_id) =
540 decode(l_multi_org_flag,'N',l_org_id,FinSysParam.org_id(+));
541
542 BEGIN
543 arp_util_tax.debug('REG_REP_DRIVER_PROC_OU(+)');
544
545 OPEN C_GET_REG_NUMBERS;
546
547 LOOP
548
549 FETCH C_GET_REG_NUMBERS BULK COLLECT INTO
550
551 pg_org_reg_num_tab,
552 pg_loc_reg_num_tab,
553 pg_ar_sys_reg_num_tab,
554 pg_fin_sys_reg_num_tab,
555 ptp_tab,
556 hr_rep_info_lat_tab,
557 hr_rep_info_kor_tab,
558 hr_rep_info_eur_grc_tab
559
560 LIMIT C_LINES_PER_COMMIT;
561
562 EXIT WHEN C_GET_REG_NUMBERS%NOTFOUND;
563
564 END LOOP;
565
566 CLOSE C_GET_REG_NUMBERS;
567
568 FOR i IN 1..nvl(pg_org_reg_num_tab.last, 0) LOOP
569
570 reg_num_tab(1) :=pg_org_reg_num_tab(i);
571 reg_num_tab(2) :=pg_loc_reg_num_tab(i);
572 reg_num_tab(3) :=pg_ar_sys_reg_num_tab(i);
573 reg_num_tab(4) :=pg_fin_sys_reg_num_tab(i);
574
575 FOR j in 1..4 LOOP
576
577 IF reg_num_tab(j) IS NOT NULL THEN
578
579 ZX_CREATE_REP_TYPE_ASSOC
580 (reg_num_tab
581 ,j
582 ,ptp_tab(i)
583 ,hr_rep_info_lat_tab(i)
584 ,hr_rep_info_kor_tab(i)
585 ,hr_rep_info_eur_grc_tab(i)
586 ,pg_ar_sys_reg_num_tab(i)
587 ,pg_fin_sys_reg_num_tab(i));
588 END IF;
589 END LOOP;
590
591 END LOOP;
592 arp_util_tax.debug('REG_REP_DRIVER_PROC_OU(-)');
593 EXCEPTION
594 WHEN NO_DATA_FOUND THEN
595 NULL;
596 WHEN OTHERS THEN
597 arp_util_tax.debug('ERROR REG_REP_DRIVER_PROC_OU');
598 END REG_REP_DRIVER_PROC_OU;
599
600
601
602 /*===========================================================================+
603 | Procedure : FIRST_PARTY_EXTRACT |
604 | |
605 | |
606 | Description: This procedure is a part of party tax |
607 | profile migration which does the data |
608 | migration for First party legal entitiy details. |
609 | |
610 | |
611 | ARGUMENTS : |
612 | |
613 | |
614 | NOTES |
615 | |
616 | |
617 | |
618 | History |
619 | zmohiudd Tuesday, November 04,2003 |
620 | |
621 | |
622 +===========================================================================*/
623
624 PROCEDURE FIRST_PARTY_EXTRACT(p_org_id in Number)
625 IS
626
627 BEGIN
628
629 arp_util_tax.debug(' FIRST_PARTY_EXTRACT (+) ' );
630
631 INSERT into
632 ZX_PARTY_TAX_PROFILE(
633 Party_Tax_Profile_Id,
634 Party_Id,
635 Party_Type_Code,
636 Customer_Flag,
637 First_Party_Le_Flag,
638 Supplier_Flag,
639 Site_Flag,
640 Legal_Establishment_Flag,
641 Rounding_Level_code,
642 Process_For_Applicability_Flag ,
643 ROUNDING_RULE_CODE,
644 Inclusive_Tax_Flag,
645 Use_Le_As_Subscriber_Flag,
646 Reporting_Authority_Flag,
647 Collecting_Authority_Flag,
648 PROVIDER_TYPE_CODE,
649 RECORD_TYPE_CODE,
650 TAX_CLASSIFICATION_CODE,
651 Self_Assess_Flag,
652 Allow_Offset_Tax_Flag,
653 Created_By,
654 Creation_Date,
655 Last_Updated_By,
656 Last_Update_Date,
657 Last_Update_Login,
658 OBJECT_VERSION_NUMBER)
659 (SELECT
660 ZX_PARTY_TAX_PROFILE_S.NEXTVAL
661 ,XEP.Party_ID -- Party_Id
662 ,'FIRST_PARTY' -- Party Type
663 ,'N' -- Customer_Flag
664 ,'Y' -- First Party LE Flag
665 ,'N' -- Supplier Flag
666 ,'N' -- Site_Flag
667 ,'N' -- Legal_Establishment_Flag
668 ,'HEADER' -- Rounding Level
669 ,'Y' -- Process_for_Applicability (Only for 3 Party)
670 , Decode (FSO.TAX_ROUNDING_RULE, 'N', 'NEAREST','D','DOWN','UP') --ROUNDING_RULE_CODE
671 ,'N' -- Inclusive_Tax_Flag
672 ,'N' -- Use_Le_As_Subscriber_Flag
673 ,'N' --Reporting_Authority_Flag
674 ,'N' -- Collecting_Authority_Flag
675 ,'N' -- PROVIDER_TYPE_CODE
676 , 'MIGRATED' -- RECORD_TYPE_CODE
677 , nvl(HRloc.Tax_Name, Fso.Vat_Code) -- TAX_CLASSIFICATION_CODE
678 ,'N' -- Self_Assess_Flag
679 ,'N' -- Allow_Offset_Tax_Flag
680 ,Fnd_Global.User_Id
681 ,Sysdate
682 ,Fnd_Global.User_Id
683 ,Sysdate
684 ,Fnd_Global.Conc_Login_Id
685 ,1
686 FROM
687 xle_entity_profiles XEP,
688 Hr_Locations_All HrLoc
689 ,Hr_All_Organization_Units HrOU
690 ,Financials_System_Params_All Fso
691 WHERE
692 HrOU.location_id = HrLoc.location_id (+)
693 AND decode(l_multi_org_flag,'N',l_org_id,HrOU.organization_id) = decode(l_multi_org_flag,'N',l_org_id,Fso.org_id(+))
694 AND decode(l_multi_org_flag,'N',l_org_id,HrOU.organization_id(+)) = XEP.legal_entity_id
695 AND not exists ( select 1 from zx_party_tax_profile
696 WHERE party_id = XEP.Party_ID and Party_Type_Code = 'FIRST_PARTY'));
697
698 arp_util_tax.debug(' FIRST_PARTY_EXTRACT (-) ' );
699
700 EXCEPTION
701 WHEN OTHERS THEN
702 arp_util_tax.debug('Exception: Error Occurred during First party Extract in PTP/REGISTRATIONS Migration '||SQLERRM );
703
704 END;
705
706
707 /*===========================================================================+
708 | Procedure : LEGAL_ESTABLISHMENT |
709 | |
710 | |
711 | Description: This procedure is a part of party tax |
712 | profile migration which does the data |
713 | migration for Legal Establishment details. |
714 | |
715 | |
716 | ARGUMENTS : |
717 | |
718 | |
719 | NOTES |
720 | |
721 | |
722 | |
723 | History |
724 | zmohiudd Tuesday, November 04,2003 |
725 | Oct 24 Main Establishment Condition not needed |
726 | |
727 +===========================================================================*/
728
729
730 PROCEDURE LEGAL_ESTABLISHMENT(p_org_id in NUMBER)
731 IS
732
733 l_status fnd_module_installations.status%TYPE;
734 l_db_status fnd_module_installations.DB_STATUS%TYPE;
735
736 BEGIN
737
738 arp_util_tax.debug(' LEGAL_ESTABLISHMENT (+) ' );
739
740 -- Following insert creates the associated establishments to the Operating units.
741
742 INSERT INTO ZX_PARTY_TAX_PROFILE
743 (
744 Party_Tax_Profile_Id,
745 Party_Id,
746 Rep_Registration_Number,
747 Party_Type_code,
748 Customer_Flag,
749 First_Party_Le_Flag,
750 Supplier_Flag,
751 Site_Flag,
752 Legal_Establishment_Flag,
753 Rounding_Level_code,
754 Process_For_Applicability_Flag ,
755 ROUNDING_RULE_CODE,
756 Inclusive_Tax_Flag,
757 Use_Le_As_Subscriber_Flag,
758 Reporting_Authority_Flag,
759 Collecting_Authority_Flag,
760 PROVIDER_TYPE_CODE,
761 RECORD_TYPE_CODE,
762 TAX_CLASSIFICATION_CODE,
763 Self_Assess_Flag,
764 Allow_Offset_Tax_Flag,
765 Created_By,
766 Creation_Date,
767 Last_Updated_By,
768 Last_Update_Date,
769 Last_Update_Login,
770 OBJECT_VERSION_NUMBER
771 )
772 SELECT
773 ZX_PARTY_TAX_PROFILE_S.NEXTVAL
774 ,XEP.Party_Id --Party_Id
775 --Bug 4361933
776 ,nvl((SELECT HrOrgInfo.ORG_INFORMATION2 FROM Hr_Organization_Information HrOrgInfo
777 WHERE HrOrgInfo.Org_Information_Context = 'Legal Entity Accounting'
778 AND HrOrgInfo.organization_id = xep.legal_entity_id)
779 ,nvl(decode(hrloc.GLOBAL_ATTRIBUTE_CATEGORY,
780 'JL.AR.PERWSLOC.LOC',hrloc.Global_Attribute11||hrloc.Global_Attribute12,
781 'JL.CL.PERWSLOC.LOC',hrloc.Global_Attribute11||hrloc.Global_Attribute12,
782 'JL.CO.PERWSLOC.LOC',hrloc.Global_Attribute11||hrloc.Global_Attribute12,
783 'JA.KR.PERWSLOC.WITHHOLDING',hrloc.Global_Attribute1,
784 'JA.TW.PERWSLOC.LOC',hrloc.Global_Attribute1,
785 'JE.GR.PERWSLOC.LOC',hrloc.Global_Attribute2),
786 nvl(ARP.tax_registration_number, Fso.Vat_Registration_Num))
787 )--REP_REGISTRATION_NUMBER
788 ,'LEGAL_ESTABLISHMENT' -- Party Type
789 ,'N' -- Customer_Flag
790 ,'N' -- First Party LE Flag
791 ,'N' -- Supplier Flag
792 ,'N' -- Site_Flag
793 ,'Y' -- Legal_Establishment_Flag
794 ,'HEADER' -- Rounding Level
795 ,'Y' -- Process_for_Applicability (Only for 3rd Party)
796 ,Decode (FSO.TAX_ROUNDING_RULE, 'N', 'NEAREST','D','DOWN','UP') --ROUNDING_RULE_CODE
797 ,ASP.Amount_Includes_Tax_Flag --INCLUSIVE_TAX_FLAG
798 ,'N' -- Use_Le_As_Subscriber_Flag
799 ,'N' --Reporting_Authority_Flag
800 ,'N' -- Collecting_Authority_Flag
801 ,'N' -- PROVIDER_TYPE_CODE
802 ,'MIGRATED' -- RECORD_TYPE_CODE
803 ,nvl(HRloc.Tax_Name, Fso.Vat_Code) --TAX_CLASSIFICATION_CODE
804 ,'N' -- Self_Assess_Flag
805 ,'N' -- Allow_Offset_Tax_Flag
806 ,Fnd_Global.User_Id -- Who Column
807 ,Sysdate -- Who Column
808 ,Fnd_Global.User_Id -- Who Column
809 ,Sysdate -- Who Column
810 ,Fnd_Global.Conc_Login_Id -- Who Column
811 ,1
812 FROM Hr_Locations_All HrLoc
813 ,xle_etb_profiles XEP
814 ,xle_associations xa
815 ,xle_association_types atype
816 ,xle_assoc_object_types ctype_obj
817 ,xle_assoc_object_types ctype_sub
818 ,Hr_All_Organization_Units HrOU
819 ,Hr_Organization_Information HrOuInfo
820 ,Financials_System_Params_All Fso
821 ,Ap_System_Parameters_All ASP
822 ,Ar_System_Parameters_All ARP
823 ,Zx_party_tax_profile ptp
824 WHERE XEP.legal_entity_id = HrOuInfo.ORG_INFORMATION2
825 AND XEP.establishment_id = xa.subject_id
826 and atype.association_type_id = xa.association_type_id
827 and atype.object_type_id = ctype_obj.object_type_id
828 and atype.subject_type_id = ctype_sub.object_type_id
829 and ctype_obj.name = 'OPERATING_UNIT'
830 AND xa.OBJECT_ID = HrOuInfo.organization_id
831 AND PTP.party_Id = XEP.party_id
832 AND PTP.party_type_code = 'LEGAL_ESTABLISHMENT'
833 AND decode(l_multi_org_flag,'N',l_org_id,HrOu.organization_id)
834 = decode(l_multi_org_flag,'N',l_org_id,HrOuInfo.organization_id(+))
835 AND HrOuInfo.Org_Information_Context = 'Operating Unit Information'
836 AND decode(l_multi_org_flag,'N',l_org_id,HrOu.organization_id)
837 = decode(l_multi_org_flag,'N',l_org_id,Fso.org_id(+))
838 AND decode(l_multi_org_flag,'N',l_org_id,HrOu.organization_id)
839 = decode(l_multi_org_flag,'N',l_org_id,ASP.org_id(+))
840 AND HrOU.location_id = HrLoc.location_id (+)
841 AND decode(l_multi_org_flag,'N',l_org_id,ASP.org_id)
842 = decode(l_multi_org_flag,'N',l_org_id,ARP.org_id(+))
843 AND NOT EXISTS (select 1 from zx_party_tax_profile where party_id = xep.party_id
844 and party_type_code = 'LEGAL_ESTABLISHMENT');
845
846
847 -- Following insert creates the remaining establishments.
848
849 INSERT INTO ZX_PARTY_TAX_PROFILE
850 (
851 Party_Tax_Profile_Id,
852 Party_Id,
853 Rep_Registration_Number,
854 Party_Type_code,
855 Customer_Flag,
856 First_Party_Le_Flag,
857 Supplier_Flag,
858 Site_Flag,
859 Legal_Establishment_Flag,
860 Rounding_Level_code,
861 Process_For_Applicability_Flag ,
862 ROUNDING_RULE_CODE,
863 Inclusive_Tax_Flag,
864 Use_Le_As_Subscriber_Flag,
865 Reporting_Authority_Flag,
866 Collecting_Authority_Flag,
867 PROVIDER_TYPE_CODE,
868 RECORD_TYPE_CODE,
869 TAX_CLASSIFICATION_CODE,
870 Self_Assess_Flag,
871 Allow_Offset_Tax_Flag,
872 Created_By,
873 Creation_Date,
874 Last_Updated_By,
875 Last_Update_Date,
876 Last_Update_Login,
877 OBJECT_VERSION_NUMBER
878 )
879 SELECT
880 ZX_PARTY_TAX_PROFILE_S.NEXTVAL
881 ,XEP.Party_Id --Party_Id
882 --Bug 4361933
883 ,nvl(hrorginfo.org_information2,
884 nvl(decode(hrloc.GLOBAL_ATTRIBUTE_CATEGORY,
885 'JL.AR.PERWSLOC.LOC'
886 ,hrloc.Global_Attribute11||hrloc.Global_Attribute12,
887 'JL.CL.PERWSLOC.LOC'
888 ,hrloc.Global_Attribute11||hrloc.Global_Attribute12,
889 'JL.CO.PERWSLOC.LOC'
890 ,hrloc.Global_Attribute11||hrloc.Global_Attribute12,
891 'JA.KR.PERWSLOC.WITHHOLDING',hrloc.Global_Attribute1,
892 'JA.TW.PERWSLOC.LOC',hrloc.Global_Attribute1,
893 'JE.GR.PERWSLOC.LOC',hrloc.Global_Attribute2),
894 nvl(ARP.tax_registration_number, Fso.Vat_Registration_Num)))--REP_REGISTRATION_NUMBER
895 ,'LEGAL_ESTABLISHMENT' -- Party Type
896 ,'N' -- Customer_Flag
897 ,'N' -- First Party LE Flag
898 ,'N' -- Supplier Flag
899 ,'N' -- Site_Flag
900 ,'Y' -- Legal_Establishment_Flag
901 ,'HEADER' -- Rounding Level
902 ,'Y' -- Process_for_Applicability (Only for 3rd Party)
903 ,Decode (FSO.TAX_ROUNDING_RULE, 'N', 'NEAREST','D','DOWN','UP') --ROUNDING_RULE_CODE
904 ,ASP.Amount_Includes_Tax_Flag --INCLUSIVE_TAX_FLAG
905 ,'N' -- Use_Le_As_Subscriber_Flag
906 ,'N' --Reporting_Authority_Flag
907 ,'N' -- Collecting_Authority_Flag
908 ,'N' -- PROVIDER_TYPE_CODE
909 ,'MIGRATED' -- RECORD_TYPE_CODE
910 ,nvl(HRloc.Tax_Name, Fso.Vat_Code) --TAX_CLASSIFICATION_CODE
911 ,'N' -- Self_Assess_Flag
912 ,'N' -- Allow_Offset_Tax_Flag
913 ,Fnd_Global.User_Id -- Who Column
914 ,Sysdate -- Who Column
915 ,Fnd_Global.User_Id -- Who Column
916 ,Sysdate -- Who Column
917 ,Fnd_Global.Conc_Login_Id -- Who Column
918 ,1
919 FROM
920 Hr_Locations_All HrLoc
921 ,xle_etb_profiles XEP
922 ,Hr_All_Organization_Units HrOU
923 ,Hr_Organization_Information HrOrgInfo
924 ,Financials_System_Params_All Fso
925 ,Ap_System_Parameters_All ASP
926 ,Ar_System_Parameters_All ARP
927 WHERE
928 XEP.legal_entity_id = HrOU.organization_id (+) --bug 4519314
929 AND decode(l_multi_org_flag,'N',l_org_id,HrOu.organization_id) =
930 decode(l_multi_org_flag,'N',l_org_id,HrOrgInfo.organization_id(+))
931 AND HrOrgInfo.Org_Information_Context = 'Legal Entity Accounting'
932 AND decode(l_multi_org_flag,'N',l_org_id,HrOu.organization_id) =
933 decode(l_multi_org_flag,'N',l_org_id,Fso.org_id(+))
934 AND decode(l_multi_org_flag,'N',l_org_id,HrOu.organization_id)=
935 decode(l_multi_org_flag,'N',l_org_id,ASP.org_id(+))
936 AND HrOU.location_id = HrLoc.location_id (+)
937 AND decode(l_multi_org_flag,'N',l_org_id,ASP.org_id) =
938 decode(l_multi_org_flag,'N',l_org_id,ARP.org_id(+))
939 AND NOT EXISTS
940 (select 1 from zx_party_tax_profile where party_id = xep.party_id
941 and party_type_code = 'LEGAL_ESTABLISHMENT');
942
943
944 -- Remove logic to create Associated Establishments' Business Organizations and locations
945 -- this logic has been transfered to Legal Entity Team
946
947 --Bug 5228787
948 /*
949 INSERT INTO ZX_REGISTRATIONS
950 (
951 Registration_Id,
952 Registration_Type_Code,
953 Registration_Number,
954 Registration_Status_Code,
955 Registration_Source_Code,
956 Registration_Reason_Code,
957 Party_Tax_Profile_Id,
958 Tax_Authority_Id,
959 Coll_Tax_Authority_Id,
960 Rep_Tax_Authority_Id,
961 Tax,
962 Tax_Regime_Code,
963 ROUNDING_RULE_CODE,
964 Tax_Jurisdiction_Code,
965 Self_Assess_Flag,
966 Inclusive_Tax_Flag,
967 Effective_From,
968 Effective_To,
969 Rep_Party_Tax_Name,
970 Legal_Registration_Id,
971 Default_Registration_Flag,
972 BANK_ID,
973 BANK_BRANCH_ID,
974 BANK_ACCOUNT_NUM ,
975 RECORD_TYPE_CODE,
976 Created_By,
977 Creation_Date,
978 Last_Updated_By,
979 Last_Update_Date,
980 Last_Update_Login,
981 OBJECT_VERSION_NUMBER)
982 SELECT
983 ZX_REGISTRATIONS_S.NEXTVAL
984 ,Null -- Type
985 ,ptp.rep_registration_number --Registration_Number
986 ,'REGISTERED' -- Registration_Status_code
987 ,'EXPLICIT'
988 ,NULL -- Registration_Reason_Code
989 ,PTP.Party_Tax_Profile_ID
990 ,NULL -- Tax Authority ID
991 ,NULL -- Collecting Tax Authority ID
992 ,NULL -- Reporting Tax Authority ID
993 ,NULL -- Tax
994 ,NULL -- TAX Regime Code
995 ,PTP.ROUNDING_RULE_CODE
996 ,NULL -- Tax Jurisdiction Code
997 ,PTP.Self_Assess_Flag -- Self Assess
998 ,PTP.Inclusive_Tax_Flag
999 ,sysdate -- Effective from
1000 ,Null -- Effective to
1001 ,NULL -- Rep_Party_Tax_Name
1002 ,NULL -- Legal Registration_ID
1003 ,'Y' -- Default Registration Flag
1004 ,hrloc.global_Attribute5
1005 ,hrloc.global_Attribute6
1006 ,hrloc.global_Attribute7
1007 ,'MIGRATED' -- Record Type
1008 ,fnd_global.user_id
1009 ,SYSDATE
1010 ,fnd_global.user_id
1011 ,SYSDATE
1012 ,FND_GLOBAL.CONC_LOGIN_ID
1013 ,1
1014 FROM
1015 zx_party_tax_profile PTP,
1016 Hr_Locations_All HrLoc,
1017 Hr_All_Organization_Units hrou,
1018 Hr_Organization_Information hroi
1019 WHERE
1020 PTP.Party_Type_code = 'LEGAL_ESTABLISHMENT'
1021 and hrou.location_id= HrLoc.location_id (+)
1022 and decode(l_multi_org_flag,'N',l_org_id,HrOU.organization_id) =
1023 decode(l_multi_org_flag,'N',l_org_id,hroi.organization_id(+))
1024 AND HrOi.party_id = ptp.Party_id
1025 AND hrloc.global_attribute_category = 'JA.SG.PERWSLOC.LOC'
1026 and not exists (select 1 from zx_registrations
1027 where party_tax_profile_id = ptp.party_tax_profile_id
1028 and registration_number = ptp.rep_registration_number);
1029 */
1030
1031 ------Bugfix 3722296----------
1032 REG_REP_DRIVER_PROC('LEGAL_ESTABLISHMENT');
1033 ------------------------------
1034
1035
1036 -- Brazilian Tax Registation Number Upgrade
1037
1038 BEGIN
1039 SELECT STATUS, DB_STATUS
1040 INTO l_status, l_db_status
1041 FROM fnd_module_installations
1042 WHERE APPLICATION_ID = '7004'
1043 and MODULE_SHORT_NAME = 'jlbrloc';
1044 EXCEPTION
1045 WHEN NO_DATA_FOUND THEN
1046 NULL;
1047 WHEN OTHERS THEN
1048 arp_util_tax.debug('Exception: Error Occurred in Supplier sites Extract in PTP/REGISTRATIONS Migration '||SQLERRM );
1049 END;
1050
1051 IF (nvl(l_status,'N') in ('I','S') or nvl(l_db_status,'N') in ('I','S')) THEN
1052
1053 -- Inserts Records for CNPJ
1054 INSERT INTO ZX_REGISTRATIONS
1055 (
1056 Registration_Id,
1057 Registration_Type_Code,
1058 Registration_Number,
1059 Registration_Status_Code,
1060 Registration_Source_Code,
1061 Registration_Reason_Code,
1062 Party_Tax_Profile_Id,
1063 Tax_Authority_Id,
1064 Coll_Tax_Authority_Id,
1065 Rep_Tax_Authority_Id,
1066 Tax,
1067 Tax_Regime_Code,
1068 ROUNDING_RULE_CODE,
1069 Tax_Jurisdiction_Code,
1070 Self_Assess_Flag,
1071 Inclusive_Tax_Flag,
1072 Effective_From,
1073 Effective_To,
1074 Rep_Party_Tax_Name,
1075 Legal_Registration_Id,
1076 Default_Registration_Flag,
1077 RECORD_TYPE_CODE,
1078 Created_By,
1079 Creation_Date,
1080 Last_Updated_By,
1081 Last_Update_Date,
1082 Last_Update_Login,
1083 Object_Version_Number)
1084 SELECT
1085 ZX_REGISTRATIONS_S.NEXTVAL
1086 ,'CNPJ' -- Type
1087 ,jl.REGISTER_NUMBER||'/'||jl.REGISTER_SUBSIDIARY||'/'||jl.REGISTER_DIGIT Registration_Number
1088 ,'REGISTERED' -- Registration_Status_code
1089 ,'EXPLICIT'
1090 ,NULL -- Registration_Reason_Code
1091 ,PTP.Party_Tax_Profile_ID
1092 ,NULL -- Tax Authority ID
1093 ,NULL -- Collecting Tax Authority ID
1094 ,NULL -- Reporting Tax Authority ID
1095 ,NULL -- Tax
1096 ,'BR-IPI' -- Tax Regime Code
1097 ,PTP.ROUNDING_RULE_CODE
1098 , NULL -- Tax Jurisdiction Code
1099 ,PTP.Self_Assess_Flag -- Self Assess
1100 ,PTP.Inclusive_Tax_Flag
1101 ,nvl(jl.CREATION_DATE, Sysdate) -- Effective from
1102 ,jl.INACTIVE_DATE -- Effective to
1103 ,NULL -- Rep_Party_Tax_Name
1104 ,NULL -- Legal Registration_ID
1105 ,'Y' -- Default Registration Flag
1106 ,'MIGRATED' -- Record Type
1107 ,fnd_global.user_id
1108 ,SYSDATE
1109 ,fnd_global.user_id
1110 ,SYSDATE
1111 ,FND_GLOBAL.CONC_LOGIN_ID
1112 ,1
1113 FROM
1114 jl_br_company_infos jl
1115 ,gl_ledger_le_v gl
1116 ,xle_etb_profiles etb
1117 ,zx_party_tax_profile ptp
1118 WHERE
1119 jl.INACTIVE_DATE is null
1120 and jl.set_of_books_id = gl.ledger_id
1121 and etb.legal_entity_id = gl.legal_entity_id
1122 and etb.party_id = ptp.party_id
1123 and ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
1124 AND NOT EXISTS (SELECT 1 FROM zx_registrations
1125 WHERE party_tax_profile_id = ptp.party_tax_profile_id
1126 AND Registration_Type_Code = 'CNPJ'
1127 AND tax_regime_code = 'BR-IPI' );
1128
1129 -- update rep_registation_number
1130 Update zx_party_tax_profile ptp
1131 Set rep_registration_number =
1132 (Select registration_number
1133 from zx_registrations reg
1134 where reg.party_tax_profile_id = ptp.party_tax_profile_id
1135 and Registration_Type_Code = 'CNPJ'
1136 and tax_regime_code = 'BR-IPI')
1137 Where ptp.party_tax_profile_id =
1138 (Select reg.party_tax_profile_ID
1139 from zx_registrations reg
1140 where reg.party_tax_profile_id = ptp.party_tax_profile_id
1141 and Registration_Type_Code = 'CNPJ'
1142 and tax_regime_code = 'BR-IPI');
1143
1144 -- Inserts Records for CNPJ
1145 INSERT INTO ZX_REGISTRATIONS
1146 (
1147 Registration_Id,
1148 Registration_Type_Code,
1149 Registration_Number,
1150 Registration_Status_Code,
1151 Registration_Source_Code,
1152 Registration_Reason_Code,
1153 Party_Tax_Profile_Id,
1154 Tax_Authority_Id,
1155 Coll_Tax_Authority_Id,
1156 Rep_Tax_Authority_Id,
1157 Tax,
1158 Tax_Regime_Code,
1159 ROUNDING_RULE_CODE,
1160 Tax_Jurisdiction_Code,
1161 Self_Assess_Flag,
1162 Inclusive_Tax_Flag,
1163 Effective_From,
1164 Effective_To,
1165 Rep_Party_Tax_Name,
1166 Legal_Registration_Id,
1167 Default_Registration_Flag,
1168 RECORD_TYPE_CODE,
1169 Created_By,
1170 Creation_Date,
1171 Last_Updated_By,
1172 Last_Update_Date,
1173 Last_Update_Login,
1174 Object_Version_Number)
1175 SELECT
1176 ZX_REGISTRATIONS_S.NEXTVAL
1177 ,'STATE INSCRIPTION' -- Type
1178 ,jl.STATE_INSCRIPTION -- Registration_Number
1179 ,'REGISTERED' -- Registration_Status_code
1180 ,'EXPLICIT'
1181 ,NULL -- Registration_Reason_Code
1182 ,PTP.Party_Tax_Profile_ID
1183 ,NULL -- Tax Authority ID
1184 ,NULL -- Collecting Tax Authority ID
1185 ,NULL -- Reporting Tax Authority ID
1186 ,NULL -- Tax
1187 ,'BR-ICMS' -- Tax Regime Code
1188 ,PTP.ROUNDING_RULE_CODE
1189 , NULL -- Tax Jurisdiction Code
1190 , PTP.Self_Assess_Flag -- Self Assess
1191 ,PTP.Inclusive_Tax_Flag
1192 ,nvl(jl.CREATION_DATE, Sysdate) -- Effective from
1193 ,jl.INACTIVE_DATE -- Effective to
1194 ,NULL -- Rep_Party_Tax_Name
1195 ,NULL -- Legal Registration_ID
1196 ,'Y' -- Default Registration Flag
1197 ,'MIGRATED' -- Record Type
1198 ,fnd_global.user_id
1199 ,SYSDATE
1200 ,fnd_global.user_id
1201 ,SYSDATE
1202 ,FND_GLOBAL.CONC_LOGIN_ID
1203 ,1
1204 FROM
1205 jl_br_company_infos jl
1206 ,gl_ledger_le_v gl
1207 ,xle_etb_profiles etb
1208 ,zx_party_tax_profile ptp
1209 WHERE
1210 jl.INACTIVE_DATE is null
1211 and jl.set_of_books_id = gl.ledger_id
1212 and etb.legal_entity_id = gl.legal_entity_id
1213 and etb.party_id = ptp.party_id
1214 and ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
1215 AND NOT EXISTS (SELECT 1 FROM zx_registrations
1216 WHERE party_tax_profile_id = ptp.party_tax_profile_id
1217 AND Registration_Type_Code = 'STATE INSCRIPTION'
1218 AND tax_regime_code = 'BR-ICMS');
1219
1220 INSERT INTO ZX_REGISTRATIONS
1221 (
1222 Registration_Id,
1223 Registration_Type_Code,
1224 Registration_Number,
1225 Registration_Status_Code,
1226 Registration_Source_Code,
1227 Registration_Reason_Code,
1228 Party_Tax_Profile_Id,
1229 Tax_Authority_Id,
1230 Coll_Tax_Authority_Id,
1231 Rep_Tax_Authority_Id,
1232 Tax,
1233 Tax_Regime_Code,
1234 ROUNDING_RULE_CODE,
1235 Tax_Jurisdiction_Code,
1236 Self_Assess_Flag,
1237 Inclusive_Tax_Flag,
1238 Effective_From,
1239 Effective_To,
1240 Rep_Party_Tax_Name,
1241 Legal_Registration_Id,
1242 Default_Registration_Flag,
1243 RECORD_TYPE_CODE,
1244 Created_By,
1245 Creation_Date,
1246 Last_Updated_By,
1247 Last_Update_Date,
1248 Last_Update_Login,
1249 OBJECT_VERSION_NUMBER)
1250 SELECT
1251 ZX_REGISTRATIONS_S.NEXTVAL
1252 ,'CITY INSCRIPTION' -- Type
1253 ,jl.MUNICIPAL_INSCRIPTION -- Registration_Number
1254 ,'REGISTERED' -- Registration_Status_code
1255 ,'EXPLICIT'
1256 ,NULL -- Registration_Reason_Code
1257 ,PTP.Party_Tax_Profile_ID
1258 ,NULL -- Tax Authority ID
1259 ,NULL -- Collecting Tax Authority ID
1260 ,NULL -- Reporting Tax Authority ID
1261 ,NULL -- Tax
1262 ,'BR-ISS' -- Tax Regime Code
1263 ,PTP.ROUNDING_RULE_CODE
1264 ,NULL -- Tax Jurisdiction Code
1265 ,PTP.Self_Assess_Flag -- Self Assess
1266 ,PTP.Inclusive_Tax_Flag
1267 ,nvl(jl.CREATION_DATE, Sysdate) -- Effective from
1268 ,jl.INACTIVE_DATE -- Effective to
1269 ,NULL -- Rep_Party_Tax_Name
1270 ,NULL -- Legal Registration_ID
1271 ,'Y' -- Default Registration Flag
1272 ,'MIGRATED' -- Record Type
1273 ,fnd_global.user_id
1274 ,SYSDATE
1275 ,fnd_global.user_id
1276 ,SYSDATE
1277 ,FND_GLOBAL.CONC_LOGIN_ID
1278 ,1
1279 FROM
1280 jl_br_company_infos jl
1281 ,gl_ledger_le_v gl
1282 ,xle_etb_profiles etb
1283 ,zx_party_tax_profile ptp
1284 WHERE
1285 jl.INACTIVE_DATE is null
1286 and jl.set_of_books_id = gl.ledger_id
1287 and etb.legal_entity_id = gl.legal_entity_id
1288 and etb.party_id = ptp.party_id
1289 and ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
1290 AND NOT EXISTS (SELECT 1 FROM zx_registrations
1291 WHERE party_tax_profile_id = ptp.party_tax_profile_id
1292 AND registration_type_code = 'CITY INSCRIPTION'
1293 AND tax_regime_code = 'BR-ISS');
1294
1295
1296 END IF; -- (nvl(l_status,'N') = 'Y' or nvl(l_db_status,'N') = 'Y') Brazil Localizations
1297 -- Brazilian Tax Registation Number Upgrade
1298
1299 arp_util_tax.debug(' LEGAL_ESTABLISHMENT (-) ' );
1300
1301 EXCEPTION
1302 WHEN NO_DATA_FOUND THEN
1303 NULL;
1304 WHEN OTHERS THEN
1305 arp_util_tax.debug('Exception: Error Occurred during Legal Establishment Extract in PTP/REGISTRATIONS Migration '||SQLERRM );
1306 END LEGAL_ESTABLISHMENT;
1307
1308 /*===========================================================================+
1309 | Procedure : SUPPLIER_EXTRACT |
1310 | |
1311 | |
1312 | Description: This procedure is a part of party tax |
1313 | profile migration which does the data |
1314 | migration for Supplier details. |
1315 | |
1316 | |
1317 | ARGUMENTS : |
1318 | |
1319 | |
1320 | NOTES |
1321 | |
1322 | |
1323 | |
1324 | History |
1325 | zmohiudd Tuesday, November 04,2003 |
1326 | Venkat 4th May 04 Bug # 3594759 |
1327 | |
1328 +===========================================================================*/
1329
1330 PROCEDURE SUPPLIER_EXTRACT(p_party_id in NUMBER, p_org_id in NUMBER)
1331 IS
1332
1333
1334 ---Commenting out this cursor declaration as it is not being used anywhere in the procedure
1335 ---Bug 4054883
1336
1337 /*CURSOR C_SUPPLIER_TYPE IS
1338 SELECT POV.VENDOR_ID
1339 FROM ap_suppliers POV , ZX_PARTY_TAX_PROFILE PTP
1340 WHERE POV.VENDOR_ID = PTP.PARTY_ID
1341 AND PTP.PARTY_TYPE_CODE = 'SUPPLIER'
1342 AND VENDOR_TYPE_LOOKUP_CODE is not null
1343 AND VENDOR_TYPE_LOOKUP_CODE <> 'TAX AUTHORITY';*/
1344
1345 l_status fnd_module_installations.status%TYPE;
1346 l_db_status fnd_module_installations.DB_STATUS%TYPE;
1347
1348
1349 BEGIN
1350
1351 arp_util_tax.debug(' SUPPLIER_EXTRACT(+) ' );
1352
1353 /*
1354 The logic to create PTPs for suppliers is to loop through po_vendors based on
1355 po_vendors.party_id. TRN will be stored in zx_party_tax_profile it self. In
1356 this case no records will be created in zx_registrations.
1357 */
1358 /*
1359 Bug 4317072 as per this bug we would no longer be requiring the ad_parallel_update feature in the pls file
1360 Separate scripts have been written to deal with this feature
1361 */
1362 INSERT INTO
1363 ZX_PARTY_TAX_PROFILE(
1364 Party_Tax_Profile_Id
1365 ,Party_Id
1366 ,Rep_Registration_Number
1367 ,Party_Type_code
1368 ,Customer_Flag
1369 ,First_Party_Le_Flag
1370 ,Supplier_Flag
1371 ,Site_Flag
1372 ,Legal_Establishment_Flag
1373 ,Rounding_Level_code
1374 ,Process_For_Applicability_Flag
1375 ,ROUNDING_RULE_CODE
1376 ,Inclusive_Tax_Flag
1377 ,Use_Le_As_Subscriber_Flag
1378 ,Effective_From_Use_Le
1379 ,Reporting_Authority_Flag
1380 ,Collecting_Authority_Flag
1381 ,PROVIDER_TYPE_CODE
1382 ,RECORD_TYPE_CODE
1383 ,TAX_CLASSIFICATION_CODE
1384 ,Self_Assess_Flag
1385 ,Allow_Offset_Tax_Flag
1386 ,Created_By
1387 ,Creation_Date
1388 ,Last_Updated_By
1389 ,Last_Update_Date
1390 ,Last_Update_Login
1391 ,OBJECT_VERSION_NUMBER)
1392 (SELECT
1393 ZX_PARTY_TAX_PROFILE_S.NEXTVAL
1394 ,PARTY_ID -- Party ID
1395 ,decode(pv.GLOBAL_ATTRIBUTE_CATEGORY,
1396 'JL.AR.APXVDMVD.SUPPLIERS',pv.Global_Attribute12||pv.num_1099,
1397 'JL.CL.APXVDMVD.SUPPLIERS',pv.Global_Attribute12||pv.num_1099,
1398 'JL.CO.APXVDMVD.SUPPLIERS',pv.Global_Attribute12||pv.num_1099,
1399 pv.VAT_Registration_Num) -- Reg Num
1400 ,'THIRD_PARTY' -- Party Type
1401 ,'N' -- Customer_Flag
1402 ,'N' -- First Party Flag
1403 ,'Y' -- Supplier Flag
1404 ,'N' -- Site Flag
1405 ,'N' -- Establishment Flag
1406 ,decode(nvl(auto_tax_calc_flag,'L'),'L','LINE','H','HEADER','T','HEADER','LINE')
1407 ,decode(nvl(auto_tax_calc_flag, 'N'), 'N', 'N', 'Y')
1408 ,DECODE (AP_TAX_ROUNDING_RULE,'N','NEAREST','D','DOWN','UP')
1409 ,nvl(amount_includes_tax_flag,'N')
1410 ,'N' -- Use_Le_As_Subscriber_Flag
1411 ,NULL -- Effective_From_Use_Le
1412 ,'N' -- Reporting Authority Flag
1413 ,'N' -- Collecting Authority Flag
1414 ,NULL -- Provider Type
1415 ,'MIGRATED' -- Record Type
1416 ,vat_code -- Tax Classification
1417 ,'N' -- Self_Assess_Flag
1418 ,nvl(offset_tax_flag,'N') -- Allow_Offset_Tax_Flag
1419 ,fnd_global.user_id -- Who Columns
1420 ,SYSDATE -- Who Columns
1421 ,fnd_global.user_id -- Who Columns
1422 ,SYSDATE -- Who Columns
1423 ,FND_GLOBAL.CONC_LOGIN_ID -- Who Columns
1424 , 1
1425 FROM ap_suppliers PV
1426 WHERE VENDOR_ID = nvl(p_party_ID,VENDOR_ID)
1427 AND not exists (select 1 from zx_party_tax_profile
1428 where party_id = PV.party_id and Party_Type_Code = 'THIRD_PARTY'));
1429
1430
1431 /*
1432 INSERT INTO
1433 ZX_REGISTRATIONS(
1434 Registration_Id,
1435 Registration_Type_Code,
1436 Registration_Number,
1437 Registration_Status_Code,
1438 Registration_Source_Code,
1439 Registration_Reason_Code,
1440 Party_Tax_Profile_Id,
1441 Tax_Authority_Id,
1442 Coll_Tax_Authority_Id,
1443 Rep_Tax_Authority_Id,
1444 Tax,
1445 Tax_Regime_Code,
1446 ROUNDING_RULE_CODE,
1447 Tax_Jurisdiction_Code,
1448 Self_Assess_Flag,
1449 Inclusive_Tax_Flag,
1450 Effective_From,
1451 Effective_To,
1452 Rep_Party_Tax_Name,
1453 Legal_Registration_Id,
1454 Default_Registration_Flag,
1455 RECORD_TYPE_CODE,
1456 Created_By,
1457 Creation_Date,
1458 Last_Updated_By,
1459 Last_Update_Date,
1460 Last_Update_Login,
1461 OBJECT_VERSION_NUMBER)
1462 (SELECT
1463 ZX_REGISTRATIONS_S.NEXTVAL
1464 ,Null -- Type
1465 ,decode(pv.GLOBAL_ATTRIBUTE_CATEGORY,
1466 'JL.AR.APXVDMVD.SUPPLIERS',pv.Global_Attribute12||pv.num_1099,
1467 'JL.CL.APXVDMVD.SUPPLIERS',pv.Global_Attribute12||pv.num_1099,
1468 'JL.CO.APXVDMVD.SUPPLIERS',pv.Global_Attribute12||pv.num_1099,
1469 pv.VAT_Registration_Num) -- Reg Num
1470 --Bug # 3594759
1471 ,decode(pv.GLOBAL_ATTRIBUTE_CATEGORY,
1472 'JL.AR.APXVDMVD.SUPPLIERS',pv.Global_Attribute1,
1473 'REGISTERED') -- Registration_Status_code
1474 ,'EXPLICIT' -- Registration_Source_Code
1475 ,NULL -- Registration_Reason_Code
1476 ,PTP.Party_Tax_Profile_ID
1477 ,NULL -- Tax Authority ID
1478 ,NULL -- Collecting Tax Authority ID
1479 ,NULL -- Reporting Tax Authority ID
1480 ,NULL -- Tax
1481 ,NULL -- TAX_Regime_Code
1482 ,PTP.ROUNDING_RULE_CODE
1483 , NULL -- Tax Jurisdiction Code
1484 , PTP.Self_Assess_Flag -- Self Assess
1485 ,PTP.Inclusive_Tax_Flag
1486 ,nvl(PV.Start_Date_Active, Sysdate) -- Effective from
1487 ,PV.End_Date_Active -- Effective to
1488 ,NULL -- Rep_Party_Tax_Name
1489 ,NULL -- Legal Registration_ID
1490 ,'Y' -- Default Registration Flag
1491 ,'MIGRATED' -- Record Type
1492 ,fnd_global.user_id
1493 ,SYSDATE
1494 ,fnd_global.user_id
1495 ,SYSDATE
1496 ,FND_GLOBAL.CONC_LOGIN_ID
1497 ,1
1498 FROM ap_suppliers PV,
1499 zx_party_tax_profile PTP
1500 WHERE
1501 PV.Party_id = PTP.Party_ID
1502 AND PTP.Party_Type_code = 'SUPPLIER'
1503 and not exists (select 1 from zx_registrations
1504 WHERE party_tax_profile_id = ptp.party_tax_profile_id));
1505 */
1506
1507 -- Bug # 3594759
1508 -- Verify Argentina Installation
1509 SELECT STATUS, DB_STATUS
1510 INTO l_status, l_db_status
1511 FROM fnd_module_installations
1512 WHERE APPLICATION_ID = '7004'
1513 And MODULE_SHORT_NAME = 'jlarloc';
1514
1515 IF (nvl(l_status,'N') in ('I','S') or
1516 nvl(l_db_status,'N') in ('I','S')) THEN
1517
1518 -- Code to migrate the lookup code for the Lookup Type
1519 -- JLZZ_AP_VAT_REG_STAT_CODE into
1520 -- ZX_REGISTRATIONS_STATUS lookup type
1521 insert into fnd_lookup_values(
1522 LOOKUP_TYPE,
1523 LANGUAGE,
1524 LOOKUP_CODE,
1525 MEANING,
1526 DESCRIPTION,
1527 ENABLED_FLAG,
1528 START_DATE_ACTIVE,
1529 END_DATE_ACTIVE,
1530 CREATED_BY,
1531 CREATION_DATE,
1532 LAST_UPDATED_BY,
1533 LAST_UPDATE_LOGIN,
1534 LAST_UPDATE_DATE,
1535 SOURCE_LANG,
1536 SECURITY_GROUP_ID,
1537 VIEW_APPLICATION_ID,
1538 TERRITORY_CODE,
1539 ATTRIBUTE_CATEGORY,
1540 ATTRIBUTE1,
1541 ATTRIBUTE2,
1542 ATTRIBUTE3,
1543 ATTRIBUTE4,
1544 ATTRIBUTE5,
1545 ATTRIBUTE6,
1546 ATTRIBUTE7,
1547 ATTRIBUTE8,
1548 ATTRIBUTE9,
1549 ATTRIBUTE10,
1550 ATTRIBUTE11,
1551 ATTRIBUTE12,
1552 ATTRIBUTE13,
1553 ATTRIBUTE14,
1554 ATTRIBUTE15)
1555 (select
1556 'ZX_REGISTRATIONS_STATUS',
1557 LANGUAGE,
1558 LOOKUP_CODE,
1559 MEANING,
1560 DESCRIPTION,
1561 ENABLED_FLAG,
1562 START_DATE_ACTIVE,
1563 END_DATE_ACTIVE,
1564 fnd_global.user_id,
1565 SYSDATE,
1566 fnd_global.user_id,
1567 fnd_global.conc_login_id,
1568 SYSDATE,
1569 SOURCE_LANG,
1570 SECURITY_GROUP_ID,
1571 VIEW_APPLICATION_ID,
1572 TERRITORY_CODE,
1573 ATTRIBUTE_CATEGORY,
1574 ATTRIBUTE1,
1575 ATTRIBUTE2,
1576 ATTRIBUTE3,
1577 ATTRIBUTE4,
1578 ATTRIBUTE5,
1579 ATTRIBUTE6,
1580 ATTRIBUTE7,
1581 ATTRIBUTE8,
1582 ATTRIBUTE9,
1583 ATTRIBUTE10,
1584 ATTRIBUTE11,
1585 ATTRIBUTE12,
1586 ATTRIBUTE13,
1587 ATTRIBUTE14,
1588 ATTRIBUTE15
1589 FROM
1590 FND_LOOKUP_VALUES fnd
1591 WHERE
1592 fnd.LOOKUP_TYPE = 'JLZZ_AP_VAT_REG_STAT_CODE'
1593 AND NOT EXISTS
1594 ( select 1 from FND_LOOKUP_VALUES
1595 where lookup_type = 'JLZZ_AP_VAT_REG_STAT_CODE' and
1596 lookup_code = fnd.lookup_code) );
1597
1598 END IF;
1599
1600 arp_util_tax.debug(' Now calling SUPPLIER_TYPE_EXTRACT ' );
1601
1602 ZX_PTP_MIGRATE_PKG.SUPPLIER_TYPE_EXTRACT;
1603
1604
1605 arp_util_tax.debug(' Now calling SUPPLIER_ASSOC_EXTRACT ' );
1606
1607 ZX_PTP_MIGRATE_PKG.SUPPLIER_ASSOC_EXTRACT;
1608
1609 arp_util_tax.debug(' SUPPLIER_EXTRACT(-) ' );
1610
1611 EXCEPTION
1612 WHEN OTHERS THEN
1613 arp_util_tax.debug('Exception: Error Occurred during Supplier extract in PTP/REGISTRATIONS Migration '||SQLERRM );
1614
1615 END; -- End Suppliers Migration
1616
1617 -- Supplier Sites Migration
1618
1619 /*===========================================================================+
1620 | Procedure : SUPPLIER_SITE_EXTRACT |
1621 | |
1622 | |
1623 | Description: This procedure is a part of party tax |
1624 | profile migration which does the data |
1625 | migration for Supplier site details. |
1626 | |
1627 | |
1628 | ARGUMENTS : |
1629 | |
1630 | |
1631 | NOTES |
1632 | |
1633 | |
1634 | |
1635 | History |
1636 | zmohiudd Tuesday, November 04,2003 |
1637 | Venkat 4th May 04 Added code for Reporting Code Association |
1638 | Bug # 3594759 |
1639 +===========================================================================*/
1640
1641
1642 PROCEDURE SUPPLIER_SITE_EXTRACT(p_party_id in NUMBER, p_org_id in Number) IS
1643
1644 l_status fnd_module_installations.status%TYPE;
1645 l_db_status fnd_module_installations.DB_STATUS%TYPE;
1646
1647
1648 BEGIN
1649
1650 arp_util_tax.debug(' SUPPLIER_SITE_EXTRACT(+) ' );
1651
1652 /*
1653 Bug 4317072 as per this bug we would no longer be requiring the ad_parallel_update feature in the pls file
1654 Separate scripts have been written to deal with this feature
1655 */
1656
1657 /*
1658 In case one party_site_id has multiple records in po_vendor_sites, we will
1659 group the tax attributes to determine if all of the are the same, if all
1660 attributes are the same we will create only one PTP for that party_site_id,
1661 with all tax attributes coming from po_vendor_sites. If the attributes are
1662 different we will create one PTP row for the first record coming in the query
1663 and TR records for all other po_vendor_sites records.
1664 */
1665
1666 INSERT ALL
1667 WHEN COUNT = 1
1668 THEN
1669 INTO
1670 ZX_PARTY_TAX_PROFILE(
1671 Party_Tax_Profile_Id
1672 ,Party_Id
1673 ,Rep_Registration_Number
1674 ,Party_Type_code
1675 ,Customer_Flag
1676 ,First_Party_Le_Flag
1677 ,Supplier_Flag
1678 ,Site_Flag
1679 ,Legal_Establishment_Flag
1680 ,Rounding_Level_code
1681 ,Process_For_Applicability_Flag
1682 ,ROUNDING_RULE_CODE
1683 ,Inclusive_Tax_Flag
1684 ,Use_Le_As_Subscriber_Flag
1685 ,Effective_From_Use_Le
1686 ,Reporting_Authority_Flag
1687 ,Collecting_Authority_Flag
1688 ,PROVIDER_TYPE_CODE
1689 ,RECORD_TYPE_CODE
1690 ,TAX_CLASSIFICATION_CODE
1691 ,Self_Assess_Flag
1692 ,Allow_Offset_Tax_Flag
1693 ,Created_By
1694 ,Creation_Date
1695 ,Last_Updated_By
1696 ,Last_Update_Date
1697 ,Last_Update_Login
1698 ,Object_Version_Number)
1699
1700 VALUES(
1701 ZX_PARTY_TAX_PROFILE_S.NEXTVAL,
1702 PARTY_SITE_ID,
1703 VAT_REGISTRATION_NUM
1704 ,'THIRD_PARTY_SITE' -- Party Type
1705 ,'N' -- Customer_Flag
1706 ,'N' -- First Party Flag
1707 ,'N' -- Supplier Flag
1708 ,'Y' -- Site Flag
1709 ,'N' -- Establishment Flag
1710 ,ROUNDING_LEVEL_CODE
1711 ,PROCESS_FOR_APPLICABILITY_FLAG
1712 ,ROUNDING_RULE_CODE
1713 ,INCLUSIVE_TAX_FLAG
1714 ,'N' -- Use_Le_As_Subscriber_Flag
1715 ,NULL -- Effective_From_Use_Le
1716 ,'N' -- Reporting Authority Flag
1717 ,'N' -- Collecting Authority Flag
1718 ,NULL -- Provider Type
1719 ,'MIGRATED' -- Record Type
1720 ,VAT_CODE
1721 ,'N'
1722 ,ALLOW_OFFSET_TAX_FLAG
1723 ,fnd_global.user_id -- Who Columns
1724 ,SYSDATE -- Who Columns
1725 ,fnd_global.user_id -- Who Columns
1726 ,SYSDATE -- Who Columns
1727 ,FND_GLOBAL.CONC_LOGIN_ID -- Who Columns
1728 ,1)
1729 (SELECT
1730 PVS.PARTY_SITE_ID PARTY_SITE_ID-- Party ID
1731 ,PVS.Vat_Registration_Num VAT_REGISTRATION_NUM
1732 ,decode(nvl(PVS.auto_tax_calc_flag,'L'),'L','LINE','H','HEADER','T','HEADER','LINE') ROUNDING_LEVEL_CODE
1733 ,decode(nvl(PVS.auto_tax_calc_flag, 'N'), 'N', 'N', 'Y') PROCESS_FOR_APPLICABILITY_FLAG
1734 ,DECODE (PVS.AP_TAX_ROUNDING_RULE,'N','NEAREST','D','DOWN','UP') ROUNDING_RULE_CODE
1735 ,nvl(PVS.amount_includes_tax_flag,'N') INCLUSIVE_TAX_FLAG
1736 ,PVS.vat_code -- Tax Classification
1737 ,nvl(PVS.offset_tax_flag,'N') ALLOW_OFFSET_TAX_FLAG -- Allow_Offset_Tax_Flag
1738 , rank() OVER(PARTITION BY PVS.PARTY_SITE_ID ORDER BY PVS.VENDOR_SITE_ID ) COUNT
1739
1740 FROM
1741 ap_suppliers PV,
1742 ap_supplier_sites_all PVS
1743 WHERE
1744 PVS.VENDOR_SITE_ID = nvl(p_party_id,PVS.VENDOR_SITE_ID) --this condition is for the sync process
1745 AND PV.VENDOR_ID = PVS.VENDOR_ID
1746 AND NOT EXISTS
1747 ( select 1 from zx_party_tax_profile
1748 WHERE party_id = pvs.PARTY_SITE_ID and Party_Type_Code = 'THIRD_PARTY_SITE'));
1749
1750
1751 /*
1752 Bug 4317072 as per this bug we would no longer be requiring the ad_parallel_update feature in the pls file
1753 Separate scripts have been written to deal with this feature
1754 */
1755 /*Commenting out the following code on account of bug 4378828*/
1756 /*
1757
1758 INSERT ALL
1759 WHEN (COUNTER > 1) then
1760 INTO
1761 ZX_REGISTRATIONS(
1762 Registration_Id,
1763 Registration_Type_Code,
1764 Registration_Number,
1765 Registration_Status_Code,
1766 Registration_Source_Code,
1767 Registration_Reason_Code,
1768 Party_Tax_Profile_Id,
1769 Account_Id,
1770 Account_Site_Id,
1771 Tax_Authority_Id,
1772 Coll_Tax_Authority_Id,
1773 Rep_Tax_Authority_Id,
1774 Tax,
1775 Tax_Regime_Code,
1776 ROUNDING_RULE_CODE,
1777 Tax_Jurisdiction_Code,
1778 Self_Assess_Flag,
1779 Inclusive_Tax_Flag,
1780 Effective_From,
1781 Effective_To,
1782 Rep_Party_Tax_Name,
1783 Legal_Registration_Id,
1784 Default_Registration_Flag,
1785 RECORD_TYPE_CODE,
1786 Created_By,
1787 Creation_Date,
1788 Last_Updated_By,
1789 Last_Update_Date,
1790 Last_Update_Login,
1791 Object_Version_Number)
1792 values
1793 (
1794 ZX_REGISTRATIONS_S.NEXTVAL,
1795 NULL, --Registration_Type_Code
1796 Registration_Number ,
1797 Registration_Status_Code,
1798 'EXPLICIT' , -- Registration_Source_Code
1799 NULL , -- Registration_Reason_Code
1800 PARTY_TAX_PROFILE_ID,
1801 ACCOUNT_ID,
1802 ACCOUNT_SITE_ID,
1803 NULL -- Tax Authority ID
1804 ,NULL -- Collecting Tax Authority ID
1805 ,NULL -- Reporting Tax Authority ID
1806 ,NULL -- Tax
1807 ,NULL -- TAX_Regime_Code
1808 ,ROUNDING_RULE_CODE
1809 , NULL -- Tax Jurisdiction Code
1810 ,SELF_ASSESS_FLAG
1811 ,INCLUSIVE_TAX_FLAG
1812 ,EFFECTIVE_FROM
1813 ,EFFECTIVE_TO
1814 ,NULL -- Rep_Party_Tax_Name
1815 ,NULL -- Legal Registration_ID
1816 ,'Y' -- Default Registration Flag
1817 ,'MIGRATED' -- Record Type
1818 ,fnd_global.user_id
1819 ,SYSDATE
1820 ,fnd_global.user_id
1821 ,SYSDATE
1822 ,FND_GLOBAL.CONC_LOGIN_ID
1823 ,1
1824 )
1825
1826 (SELECT
1827 decode(pv.GLOBAL_ATTRIBUTE_CATEGORY,
1828 'JL.AR.APXVDMVD.SUPPLIERS',pv.Global_Attribute12,
1829 'JL.CL.APXVDMVD.SUPPLIERS',pv.Global_Attribute12,
1830 'JL.CO.APXVDMVD.SUPPLIERS',pv.Global_Attribute12,
1831 pv.VAT_Registration_Num) Registration_Number-- Reg Num
1832 --Bug # 3594759
1833 ,decode(pv.GLOBAL_ATTRIBUTE_CATEGORY,
1834 'JL.AR.APXVDMVD.SUPPLIERS',pv.Global_Attribute1,
1835 'REGISTERED') Registration_Status_Code-- Registration_Status_code
1836 ,PTP.Party_Tax_Profile_ID PARTY_TAX_PROFILE_ID
1837 ,pv.vendor_id ACCOUNT_ID
1838 ,pvs.vendor_site_id ACCOUNT_SITE_ID
1839 ,PTP.ROUNDING_RULE_CODE ROUNDING_RULE_CODE
1840 , PTP.Self_Assess_Flag SELF_ASSESS_FLAG -- Self Assess
1841 ,PTP.Inclusive_Tax_Flag INCLUSIVE_TAX_FLAG
1842 ,nvl(PV.Start_Date_Active, Sysdate) EFFECTIVE_FROM-- Effective from
1843 ,PV.End_Date_Active EFFECTIVE_TO-- Effective to
1844 ,counter
1845
1846 FROM
1847 ap_suppliers pv,
1848 ap_supplier_sites_all pvs,
1849 zx_party_tax_profile PTP,
1850 (select party_site_id,
1851 COUNT
1852 (DISTINCT(PARTY_SITE_ID||AMOUNT_INCLUDES_TAX_FLAG
1853 ||AP_TAX_ROUNDING_RULE||AUTO_TAX_CALC_FLAG||OFFSET_TAX_FLAG||VAT_CODE||VAT_REGISTRATION_NUM) ) Counter
1854 FROM
1855 ap_supplier_sites_all
1856 group by party_site_id
1857 ) tax_attr_tab
1858 WHERE
1859 pv.vendor_id = pvs.vendor_id
1860 AND PTP.party_id = pvs.party_site_id
1861 AND tax_attr_tab.party_site_id = pvs.party_site_id
1862 AND PTP.Party_Type_code = 'SUPPLIER_SITE'
1863 and not exists (select 1 from zx_registrations
1864 WHERE party_tax_profile_id = ptp.party_tax_profile_id));
1865 */
1866
1867
1868 /* INSERT INTO
1869 ZX_PARTY_TAX_PROFILE(
1870 Party_Tax_Profile_Id
1871 ,Party_Id
1872 ,Party_Type_code
1873 ,Customer_Flag
1874 ,First_Party_Le_Flag
1875 ,Supplier_Flag
1876 ,Site_Flag
1877 ,Legal_Establishment_Flag
1878 ,Rounding_Level_code
1879 ,Process_For_Applicability_Flag
1880 ,ROUNDING_RULE_CODE
1881 ,Inclusive_Tax_Flag
1882 ,Use_Le_As_Subscriber_Flag
1883 ,Effective_From_Use_Le
1884 ,Reporting_Authority_Flag
1885 ,Collecting_Authority_Flag
1886 ,PROVIDER_TYPE_CODE
1887 ,RECORD_TYPE_CODE
1888 ,TAX_CLASSIFICATION_CODE
1889 ,Self_Assess_Flag
1890 ,Allow_Offset_Tax_Flag
1891 ,Created_By
1892 ,Creation_Date
1893 ,Last_Updated_By
1894 ,Last_Update_Date
1895 ,Last_Update_Login
1896 ,OBJECT_VERSION_NUMBER)
1897 (SELECT
1898 ZX_PARTY_TAX_PROFILE_S.NEXTVAL
1899 ,pvs.VENDOR_SITE_ID -- Party ID
1900 ,'SUPPLIER_SITE' -- Party Type
1901 ,'N' -- Customer_Flag
1902 ,'N' -- First Party Flag
1903 ,'Y' -- Supplier Flag
1904 ,'Y' -- Site Flag
1905 ,'N' -- Establishment Flag
1906 ,decode(nvl(pvs.auto_tax_calc_flag,'L'),'L','LINE','H','HEADER','T','HEADER','LINE')
1907 ,decode(nvl(pvs.auto_tax_calc_flag, 'N'), 'N', 'N', 'Y')
1908 , DECODE (pvs.AP_TAX_ROUNDING_RULE,'N','NEAREST',
1909 'D','DOWN', 'UP')
1910 , nvl(pvs.amount_includes_tax_flag, 'N')
1911 ,'N' -- Use_Le_As_Subscriber_Flag
1912 , NULL -- Effective_From_Use_Le
1913 ,'N' -- Reporting Authority Flag
1914 ,'N' -- Collecting Authority Flag
1915 ,NULL -- Provider Type
1916 ,'MIGRATED' -- Record Type
1917 ,pvs.vat_code -- Tax Classification
1918 ,'N' -- Self_Assess_Flag
1919 , nvl(pv.offset_tax_flag,'N') -- Allow_Offset_Tax_Flag
1920 , fnd_global.user_id -- Who Columns
1921 ,SYSDATE -- Who Columns
1922 ,fnd_global.user_id -- Who Columns
1923 ,SYSDATE -- Who Columns
1924 ,FND_GLOBAL.CONC_LOGIN_ID -- Who Columns
1925 ,1
1926 FROM ap_supplier_sites_all Pvs,
1927 ap_suppliers Pv
1928 WHERE pvs.Vendor_Site_Id = nvl(P_Party_Id,pvs.Vendor_site_Id)
1929 AND pvs.vendor_id = pv.vendor_id
1930 AND not exists ( select 1 from zx_party_tax_profile
1931 WHERE party_id = pvs.VENDOR_SITE_ID and Party_Type_Code = 'SUPPLIER_SITE'));
1932
1933
1934 INSERT INTO
1935 ZX_REGISTRATIONS(
1936 Registration_Id,
1937 Registration_Type_Code,
1938 Registration_Number,
1939 Registration_Status_Code,
1940 Registration_Source_Code,
1941 Registration_Reason_Code,
1942 Party_Tax_Profile_Id,
1943 Tax_Authority_Id,
1944 Coll_Tax_Authority_Id,
1945 Rep_Tax_Authority_Id,
1946 Tax,
1947 Tax_Regime_Code,
1948 ROUNDING_RULE_CODE,
1949 Tax_Jurisdiction_Code,
1950 Self_Assess_Flag,
1951 Inclusive_Tax_Flag,
1952 Effective_From,
1953 Effective_To,
1954 Rep_Party_Tax_Name,
1955 Legal_Registration_Id,
1956 Default_Registration_Flag,
1957 RECORD_TYPE_CODE,
1958 Created_By,
1959 Creation_Date,
1960 Last_Updated_By,
1961 Last_Update_Date,
1962 Last_Update_Login,
1963 OBJECT_VERSION_NUMBER)
1964 (SELECT
1965 ZX_REGISTRATIONS_S.NEXTVAL
1966 ,NULL -- Type
1967 ,PVS.VAT_Registration_Num --Reg Number
1968 ,'REGISTERED' -- Registration_Status_code
1969 ,'EXPLICIT' -- Registration_Source_Code
1970 ,NULL -- Registration_Reason_Code
1971 ,PTP.Party_Tax_Profile_ID
1972 ,NULL -- Tax Authority ID
1973 ,NULL -- Collecting Tax Authority ID
1974 ,NULL -- Reporting Tax Authority ID
1975 ,NULL -- Tax
1976 ,NULL -- TAX_Regime_Code
1977 ,PTP.ROUNDING_RULE_CODE
1978 , NULL -- Tax Jurisdiction Code
1979 , PTP.Self_Assess_Flag -- Self Assess
1980 ,PTP.Inclusive_Tax_Flag
1981 ,nvl(PV.Start_Date_Active, Sysdate) -- Effective from
1982 ,PV.End_Date_Active -- Effective to
1983 ,NULL -- Rep_Party_Tax_Name
1984 ,NULL -- Legal Registration_ID
1985 ,'Y' -- Default Registration Flag
1986 ,'MIGRATED' -- Record Type
1987 ,fnd_global.user_id
1988 ,SYSDATE
1989 ,fnd_global.user_id
1990 ,SYSDATE
1991 ,FND_GLOBAL.CONC_LOGIN_ID
1992 ,1
1993 FROM ap_supplier_sites_all PVS,
1994 ap_suppliers PV,
1995 zx_party_tax_profile PTP
1996 WHERE
1997 PVS.vendor_site_id = PTP.Party_ID
1998 AND PTP.Party_Type_code = 'SUPPLIER_SITE'
1999 AND PVS.Vendor_ID = PV.Vendor_ID
2000 AND NOT EXISTS (SELECT 1 FROM zx_registrations
2001 WHERE party_tax_profile_id = ptp.party_tax_profile_id
2002 AND Registration_Type_Code is null));*/
2003
2004 --Commenting out this code since Brazilian GDFs are not being migrated as per bug 4054883*/
2005 /*
2006 -- Verify Brazil Installation
2007
2008 BEGIN
2009
2010 SELECT STATUS, DB_STATUS
2011 INTO l_status, l_db_status
2012 FROM fnd_module_installations
2013 WHERE APPLICATION_ID = '7004'
2014 and MODULE_SHORT_NAME = 'jlbrloc';
2015
2016 EXCEPTION
2017
2018 WHEN OTHERS THEN
2019 arp_util_tax.debug('Exception: Error Occurred in Supplier sites Extract in PTP/REGISTRATIONS Migration '||SQLERRM );
2020
2021 END;
2022
2023
2024 IF (nvl(l_status,'N') in ('I','S') or nvl(l_db_status,'N') in ('I','S')) THEN
2025
2026
2027 -- Inserts Records for CNPJ
2028 INSERT INTO
2029 ZX_REGISTRATIONS(
2030 Registration_Id,
2031 Registration_Type_Code,
2032 Registration_Number,
2033 Registration_Status_Code,
2034 Registration_Source_Code,
2035 Registration_Reason_Code,
2036 Party_Tax_Profile_Id,
2037 Tax_Authority_Id,
2038 Coll_Tax_Authority_Id,
2039 Rep_Tax_Authority_Id,
2040 Tax,
2041 Tax_Regime_Code,
2042 ROUNDING_RULE_CODE,
2043 Tax_Jurisdiction_Code,
2044 Self_Assess_Flag,
2045 Inclusive_Tax_Flag,
2046 Effective_From,
2047 Effective_To,
2048 Rep_Party_Tax_Name,
2049 Legal_Registration_Id,
2050 Default_Registration_Flag,
2051 RECORD_TYPE_CODE,
2052 Created_By,
2053 Creation_Date,
2054 Last_Updated_By,
2055 Last_Update_Date,
2056 Last_Update_Login,
2057 Object_Version_Number)
2058 (SELECT
2059 ZX_REGISTRATIONS_S.NEXTVAL
2060 ,'CNPJ' -- Type
2061 ,PVS.Global_Attribute10||' / '||PVS.Global_Attribute11||' / '||PVS.Global_Attribute12 --Reg Number
2062 ,'REGISTERED' -- Registration_Status_code
2063 ,'EXPLICIT'
2064 ,NULL -- Registration_Reason_Code
2065 ,PTP.Party_Tax_Profile_ID
2066 ,NULL -- Tax Authority ID
2067 ,NULL -- Collecting Tax Authority ID
2068 ,NULL -- Reporting Tax Authority ID
2069 ,NULL -- Tax
2070 ,'BR-IPI' -- Tax Regime Code
2071 ,PTP.ROUNDING_RULE_CODE
2072 , NULL -- Tax Jurisdiction Code
2073 , PTP.Self_Assess_Flag -- Self Assess
2074 ,PTP.Inclusive_Tax_Flag
2075 ,nvl(PV.Start_Date_Active, Sysdate) -- Effective from
2076 ,PV.End_Date_Active -- Effective to
2077 ,NULL -- Rep_Party_Tax_Name
2078 ,NULL -- Legal Registration_ID
2079 ,'Y' -- Default Registration Flag
2080 ,'MIGRATED' -- Record Type
2081 ,fnd_global.user_id
2082 ,SYSDATE
2083 ,fnd_global.user_id
2084 ,SYSDATE
2085 ,FND_GLOBAL.CONC_LOGIN_ID
2086 ,1
2087 FROM ap_supplier_sites_all PVS,
2088 ap_suppliers PV,
2089 zx_party_tax_profile PTP
2090 WHERE
2091 PVS.vendor_site_id = PTP.Party_ID
2092 AND PTP.Party_Type_code = 'SUPPLIER_SITE'
2093 AND PVS.Vendor_ID = PV.Vendor_ID
2094 AND pvs.GLOBAL_ATTRIBUTE_CATEGORY = 'JL.BR.APXVDMVD.SITES'
2095 AND NOT EXISTS (SELECT 1 FROM zx_registrations
2096 WHERE party_tax_profile_id = ptp.party_tax_profile_id
2097 AND Registration_Type_Code = 'CNPJ'
2098 AND tax_regime_code = 'BR-IPI' ));
2099
2100 -- Inserts Records for State Inscription
2101
2102 INSERT INTO
2103 ZX_REGISTRATIONS(
2104 Registration_Id,
2105 Registration_Type_Code,
2106 Registration_Number,
2107 Registration_Status_Code,
2108 Registration_Source_Code,
2109 Registration_Reason_Code,
2110 Party_Tax_Profile_Id,
2111 Tax_Authority_Id,
2112 Coll_Tax_Authority_Id,
2113 Rep_Tax_Authority_Id,
2114 Tax,
2115 Tax_Regime_Code,
2116 ROUNDING_RULE_CODE,
2117 Tax_Jurisdiction_Code,
2118 Self_Assess_Flag,
2119 Inclusive_Tax_Flag,
2120 Effective_From,
2121 Effective_To,
2122 Rep_Party_Tax_Name,
2123 Legal_Registration_Id,
2124 Default_Registration_Flag,
2125 RECORD_TYPE_CODE,
2126 Created_By,
2127 Creation_Date,
2128 Last_Updated_By,
2129 Last_Update_Date,
2130 Last_Update_Login,
2131 OBJECT_VERSION_NUMBER)
2132 (SELECT
2133 ZX_REGISTRATIONS_S.NEXTVAL
2134 ,'STATE INSCRIPTION' -- Type
2135 ,PVS.Global_Attribute13 -- State Registration Num
2136 ,'REGISTERED' -- Registration_Status_Code
2137 ,'EXPLICIT'
2138 ,NULL -- Registration_Reason_Code
2139 ,PTP.Party_Tax_Profile_ID
2140 ,NULL -- Tax Authority ID
2141 ,NULL -- Collecting Tax Authority ID
2142 ,NULL -- Reporting Tax Authority ID
2143 ,NULL -- Tax
2144 ,'BR-ICMS' -- Tax_Regime_Code
2145 ,PTP.ROUNDING_RULE_CODE
2146 , NULL -- Tax Jurisdiction Code
2147 , PTP.Self_Assess_Flag -- Self Asses
2148 ,PTP.Inclusive_Tax_Flag
2149 ,nvl(PV.Start_Date_Active, Sysdate) -- Effective from
2150 ,PV.End_Date_Active -- Effective To
2151 ,NULL -- Rep_Party_Tax_Name
2152 ,NULL -- Legal Registration_ID
2153 ,'N' -- Default Registration Flag
2154 ,'MIGRATED' -- Record Type
2155 ,fnd_global.user_id
2156 ,SYSDATE
2157 ,fnd_global.user_id
2158 ,SYSDATE
2159 ,FND_GLOBAL.CONC_LOGIN_ID
2160 ,1
2161 FROM ap_supplier_sites_all PVS,
2162 ap_suppliers PV,
2163 zx_party_tax_profile PTP
2164 WHERE
2165 PVS.vendor_site_id = PTP.Party_ID
2166 AND PTP.Party_Type_code = 'SUPPLIER_SITE'
2167 AND PVS.Vendor_ID = PV.Vendor_ID
2168 AND pvs.GLOBAL_ATTRIBUTE_CATEGORY = 'JL.BR.APXVDMVD.SITES'
2169 AND NOT EXISTS (SELECT 1 FROM zx_registrations
2170 WHERE party_tax_profile_id = ptp.party_tax_profile_id
2171 AND Registration_Type_Code = 'STATE INSCRIPTION'
2172 AND tax_regime_code= 'BR-ICMS' ));
2173
2174 -- Inserts Records for Municipal Inscription
2175
2176 INSERT INTO
2177 ZX_REGISTRATIONS(
2178 Registration_Id,
2179 Registration_Type_Code,
2180 Registration_Number,
2181 Registration_Status_Code,
2182 Registration_Source_Code,
2183 Registration_Reason_Code,
2184 Party_Tax_Profile_Id,
2185 Tax_Authority_Id,
2186 Coll_Tax_Authority_Id,
2187 Rep_Tax_Authority_Id,
2188 Tax,
2189 Tax_Regime_Code,
2190 ROUNDING_RULE_CODE,
2191 Tax_Jurisdiction_Code,
2192 Self_Assess_Flag,
2193 Inclusive_Tax_Flag,
2194 Effective_From,
2195 Effective_To,
2196 Rep_Party_Tax_Name,
2197 Legal_Registration_Id,
2198 Default_Registration_Flag,
2199 RECORD_TYPE_CODE,
2200 Created_By,
2201 Creation_Date,
2202 Last_Updated_By,
2203 Last_Update_Date,
2204 Last_Update_Login,
2205 OBJECT_VERSION_NUMBER)
2206 (SELECT
2207 ZX_REGISTRATIONS_S.NEXTVAL
2208 ,'CITY INSCRIPTION' -- Type
2209 ,PVS.Global_Attribute14 -- City Registration Num
2210 ,'REGISTERED' -- Registration_Status_Code
2211 ,'EXPLICIT'
2212 ,NULL -- Registration_Reason_Code
2213 ,PTP.Party_Tax_Profile_ID
2214 ,NULL -- Tax Authority ID
2215 ,NULL -- Collecting Tax Authority ID
2216 ,NULL -- Reporting Tax Authority ID
2217 ,NULL -- Tax
2218 ,'BR-ISS' -- Tax_Regime_Code
2219 ,PTP.ROUNDING_RULE_CODE
2220 , NULL -- Tax Jurisdiction Code
2221 ,PTP.Self_Assess_Flag -- Self Asses
2222 ,PTP.Inclusive_Tax_Flag
2223 ,nvl(PV.Start_Date_Active, Sysdate) -- Effective from
2224 ,PV.End_Date_Active -- Effective To
2225 ,NULL -- Rep_Party_Tax_Name
2226 ,NULL -- Legal Registration_ID
2227 ,'N' -- Default Registration Flag
2228 ,'MIGRATED' -- Record Type
2229 ,fnd_global.user_id
2230 ,SYSDATE
2231 ,fnd_global.user_id
2232 ,SYSDATE
2233 ,FND_GLOBAL.CONC_LOGIN_ID
2234 ,1
2235 FROM ap_supplier_sites_all PVS,
2236 ap_suppliers PV,
2237 zx_party_tax_profile PTP
2238 WHERE
2239 PVS.vendor_site_id = PTP.Party_ID
2240 AND PTP.Party_Type_code = 'SUPPLIER_SITE'
2241 AND PVS.Vendor_ID = PV.Vendor_ID
2242 AND pvs.GLOBAL_ATTRIBUTE_CATEGORY = 'JL.BR.APXVDMVD.SITES'
2243 AND NOT EXISTS (SELECT 1 FROM zx_registrations
2244 WHERE party_tax_profile_id = ptp.party_tax_profile_id
2245 AND registration_type_code = 'CITY INSCRIPTION'
2246 AND tax_regime_code = 'BR-ISS' ));
2247
2248
2249 END IF; -- (nvl(l_status,'N') = 'Y' or nvl(l_db_status,'N') = 'Y') Brazil Localizations
2250
2251 */
2252 arp_util_tax.debug(' SUPPLIER_SITE_EXTRACT(-) ' );
2253
2254 EXCEPTION
2255 WHEN OTHERS THEN
2256 arp_util_tax.debug('Exception: Error Occurred during Supplier sites Extract in PTP/REGISTRATIONS Migration '||SQLERRM );
2257
2258 END;
2259 /*===========================================================================+
2260 | Procedure : OU_EXTRACT |
2261 | |
2262 | |
2263 | Description: This procedure is a part of party tax |
2264 | profile migration which does the data |
2265 | migration for Operating Unit details. |
2266 | |
2267 | |
2268 | ARGUMENTS : |
2269 | |
2270 | |
2271 | NOTES : Handle case for Non-Multi Org Environments |
2272 | |
2273 | |
2274 | History |
2275 | zmohiudd Tuesday, November 04,2003 |
2276 | |
2277 | |
2278 +===========================================================================*/
2279
2280 PROCEDURE OU_EXTRACT(p_party_id in NUMBER) IS
2281
2282 BEGIN
2283
2284 arp_util_tax.debug(' OU_EXTRACT(+) ' );
2285 IF L_MULTI_ORG_FLAG = 'N'
2286 --Bug Fix 4460944
2287 THEN
2288 INSERT INTO
2289 ZX_PARTY_TAX_PROFILE(
2290 Party_Tax_Profile_Id,
2291 Party_Id,
2292 Party_Type_code,
2293 Customer_Flag,
2294 First_Party_Le_Flag,
2295 Supplier_Flag,
2296 Site_Flag,
2297 Legal_Establishment_Flag,
2298 Rounding_Level_code,
2299 Process_For_Applicability_Flag ,
2300 ROUNDING_RULE_CODE,
2301 Inclusive_Tax_Flag,
2302 Use_Le_As_Subscriber_Flag,
2303 Effective_From_Use_Le,
2304 Reporting_Authority_Flag,
2305 Collecting_Authority_Flag,
2306 PROVIDER_TYPE_CODE,
2307 RECORD_TYPE_CODE,
2308 TAX_CLASSIFICATION_CODE,
2309 Self_Assess_Flag,
2310 Allow_Offset_Tax_Flag,
2311 Created_By,
2312 Creation_Date,
2313 Last_Updated_By,
2314 Last_Update_Date,
2315 Last_Update_Login,
2316 OBJECT_VERSION_NUMBER)
2317 (SELECT
2318 ZX_PARTY_TAX_PROFILE_S.NEXTVAL -- Party_Tax_Profile_Id
2319 ,L_ORG_ID -- Party_Id
2320 ,'OU' -- Party_Type_code
2321 ,'N' -- Customer Flag
2322 ,'N' -- First_Party_Le_Flag
2323 ,'N' -- Supllier_Flag
2324 ,'N' -- Site_Flag
2325 ,'N' -- Legal_Establishment_Flag
2326 ,NULL -- Rounding_Level_Code
2327 ,'Y' -- Process_For_Applicability_Flag
2328 ,NULL, -- Rounding_Rule_Code
2329 'N', -- Inclusive_Tax_Flag
2330 'N', -- Use_Le_As_Subscriber_Flag
2331 NULL, -- Effective_From_Use_Le
2332 'N', -- Reporting_Authority_Flag
2333 'N', -- Collecting_Authority_Flag
2334 Null, -- Provider_Type_Code
2335 'MIGRATED', -- Record_Type_Code
2336 Null, -- Tax_Classification_Code
2337 'N', -- Self_Assess_Flag
2338 'N', -- Allow_Offset_Tax_Flag
2339 fnd_global.user_id, -- Created_By
2340 SYSDATE, -- Creation_Date
2341 fnd_global.user_id, -- Last_Updated_By
2342 SYSDATE, -- Last_Update_Date
2343 FND_GLOBAL.CONC_LOGIN_ID, -- Last_Update_Login
2344 1
2345 FROM DUAL
2346 WHERE not exists ( select 1 from zx_party_tax_profile
2347 WHERE party_id = l_org_id and Party_Type_Code = 'OU'));
2348
2349 ELSE
2350
2351 INSERT INTO
2352 ZX_PARTY_TAX_PROFILE(
2353 Party_Tax_Profile_Id,
2354 Party_Id,
2355 Party_Type_code,
2356 Customer_Flag,
2357 First_Party_Le_Flag,
2358 Supplier_Flag,
2359 Site_Flag,
2360 Legal_Establishment_Flag,
2361 Rounding_Level_code,
2362 Process_For_Applicability_Flag ,
2363 ROUNDING_RULE_CODE,
2364 Inclusive_Tax_Flag,
2365 Use_Le_As_Subscriber_Flag,
2366 Effective_From_Use_Le,
2367 Reporting_Authority_Flag,
2368 Collecting_Authority_Flag,
2369 PROVIDER_TYPE_CODE,
2370 RECORD_TYPE_CODE,
2371 TAX_CLASSIFICATION_CODE,
2372 Self_Assess_Flag,
2373 Allow_Offset_Tax_Flag,
2374 Created_By,
2375 Creation_Date,
2376 Last_Updated_By,
2377 Last_Update_Date,
2378 Last_Update_Login,
2379 OBJECT_VERSION_NUMBER)
2380 (SELECT
2381 ZX_PARTY_TAX_PROFILE_S.NEXTVAL -- Party_Tax_Profile_Id
2382 ,Organization_id -- Party_Id
2383 ,'OU' -- Party_Type_code
2384 ,'N' -- Customer Flag
2385 ,'N' -- First_Party_Le_Flag
2386 ,'N' -- Supllier_Flag
2387 ,'N' -- Site_Flag
2388 ,'N' -- Legal_Establishment_Flag
2389 ,NULL -- Rounding_Level_Code
2390 ,'Y' -- Process_For_Applicability_Flag
2391 ,NULL, -- Rounding_Rule_Code
2392 'N', -- Inclusive_Tax_Flag
2393 'N', -- Use_Le_As_Subscriber_Flag
2394 NULL, -- Effective_From_Use_Le
2395 'N', -- Reporting_Authority_Flag
2396 'N', -- Collecting_Authority_Flag
2397 Null, -- Provider_Type_Code
2398 'MIGRATED', -- Record_Type_Code
2399 Null, -- Tax_Classification_Code
2400 'N', -- Self_Assess_Flag
2401 'N', -- Allow_Offset_Tax_Flag
2402 fnd_global.user_id, -- Created_By
2403 SYSDATE, -- Creation_Date
2404 fnd_global.user_id, -- Last_Updated_By
2405 SYSDATE, -- Last_Update_Date
2406 FND_GLOBAL.CONC_LOGIN_ID, -- Last_Update_Login
2407 1
2408 FROM HR_OPERATING_UNITS
2409 WHERE HR_OPERATING_UNITS.ORGANIZATION_ID =
2410 nvl(p_party_ID,HR_OPERATING_UNITS.ORGANIZATION_ID) and
2411 not exists ( select 1 from zx_party_tax_profile
2412 WHERE party_id = organization_id and Party_Type_Code = 'OU'));
2413
2414 ------Bugfix 4308003----------
2415 REG_REP_DRIVER_PROC_OU('OU');
2416 ------------------------------
2417 END IF;
2418
2419
2420
2421 arp_util_tax.debug(' OU_EXTRACT(-) ' );
2422
2423 EXCEPTION
2424 WHEN OTHERS THEN
2425 arp_util_tax.debug('Exception: Error Occurred during Operating Units Extract in PTP/REGISTRATIONS Migration '||SQLERRM );
2426
2427 END;
2428
2429
2430 /*===========================================================================+
2431 | Procedure: SUPPLIER_TYPE_EXTRACT |
2432 | |
2433 | |
2434 | Description: This procedure does the data extraction for |
2435 | Fiscal Classifications for Party and populates |
2436 | |
2437 | |
2438 | |
2439 | |
2440 | ARGUMENTS : |
2441 | |
2442 | |
2443 | |
2444 | |
2445 | |
2446 | NOTES |
2447 | |
2448 | |
2449 | |
2450 | |
2451 | |
2452 | History |
2453 | |
2454 | zmohiudd Wednesday, March 10, 2004 Created |
2455 | |
2456 | |
2457 | |
2458 | |
2459 +===========================================================================*/
2460
2461
2462 PROCEDURE SUPPLIER_TYPE_EXTRACT IS
2463 BEGIN
2464
2465 arp_util_tax.debug(' SUPPLIER_TYPE_EXTRACT .. (+) ' );
2466
2467 -- Migrate fnd lookups source of Supplier Type
2468
2469 arp_util_tax.debug(' Creating Fiscal classification types .. (+) ' );
2470
2471 INSERT ALL
2472 INTO ZX_FC_TYPES_B(
2473 CLASSIFICATION_TYPE_ID,
2474 OWNER_TABLE_CODE,
2475 OWNER_ID_CHAR,
2476 CLASSIFICATION_TYPE_CODE,
2477 CLASSIFICATION_TYPE_CATEG_CODE,
2478 CLASSIFICATION_TYPE_GROUP_CODE,
2479 DELIMITER,
2480 START_POSITION,
2481 NUM_CHARACTERS,
2482 CLASSIFICATION_TYPE_LEVEL_CODE,
2483 EFFECTIVE_FROM ,
2484 EFFECTIVE_TO ,
2485 RECORD_TYPE_CODE,
2486 CREATED_BY,
2487 CREATION_DATE,
2488 LAST_UPDATED_BY,
2489 LAST_UPDATE_DATE,
2490 LAST_UPDATE_LOGIN,
2491 OBJECT_VERSION_NUMBER)
2492 VALUES (
2493 ZX_FC_TYPES_B_S.NEXTVAL,
2494 'HZ_CLASS_CATEGORY',
2495 'VENDOR TYPE',
2496 'SUPPLIER_TYPE',
2497 'PARTY_FISCAL_CLASS',
2498 NULL,
2499 NULL,
2500 NULL,
2501 NULL,
2502 1,
2503 SYSDATE,
2504 NULL,
2505 'MIGRATED',
2506 fnd_global.user_id,
2507 SYSDATE,
2508 fnd_global.user_id,
2509 SYSDATE,
2510 fnd_global.conc_login_id,
2511 1)
2512 SELECT '1' from dual
2513 WHERE NOT EXISTS
2514 (SELECT NULL
2515 FROM ZX_FC_TYPES_B TYPE
2516 WHERE TYPE.CLASSIFICATION_TYPE_CODE =
2517 'SUPPLIER_TYPE' AND
2518 TYPE.CLASSIFICATION_TYPE_CATEG_CODE =
2519 'PARTY_FISCAL_CLASS');
2520
2521 INSERT ALL
2522 INTO ZX_FC_TYPES_TL(
2523 CLASSIFICATION_TYPE_ID,
2524 CLASSIFICATION_TYPE_NAME,
2525 LANGUAGE,
2526 SOURCE_LANG,
2527 CREATED_BY,
2528 CREATION_DATE,
2529 LAST_UPDATED_BY,
2530 LAST_UPDATE_DATE,
2531 LAST_UPDATE_LOGIN
2532 )
2533 VALUES (CLASSIFICATION_TYPE_ID,
2534 'Supplier Type',
2535 LANGUAGE_CODE,
2536 userenv('LANG') ,
2537 fnd_global.user_id ,
2538 SYSDATE ,
2539 fnd_global.user_id ,
2540 SYSDATE ,
2541 fnd_global.conc_login_id)
2542 SELECT
2543 fc_types.CLASSIFICATION_TYPE_ID ,
2544 L.LANGUAGE_CODE
2545 FROM
2546 FND_LANGUAGES L,
2547 ZX_FC_TYPES_B fc_types
2548 WHERE
2549 L.INSTALLED_FLAG in ('I', 'B')
2550 AND fc_types.RECORD_TYPE_CODE = 'MIGRATED'
2551 AND fc_types.CLASSIFICATION_TYPE_CODE
2552 ='SUPPLIER_TYPE'
2553 AND fc_types.CLASSIFICATION_TYPE_CATEG_CODE
2554 ='PARTY_FISCAL_CLASS'
2555 AND not exists
2556 (select NULL
2557 from ZX_FC_TYPES_TL T
2558 where T.CLASSIFICATION_TYPE_ID =
2559 fc_types.CLASSIFICATION_TYPE_ID
2560 and T.LANGUAGE = L.LANGUAGE_CODE);
2561
2562
2563 /* arp_util_tax.debug(' Creating Fiscal classification codes .. (+) ' );
2564
2565 INSERT ALL
2566 INTO ZX_FC_CODES_B (
2567 classification_type_code,
2568 classification_id,
2569 classification_code,
2570 effective_from,
2571 effective_to,
2572 parent_classification_code,
2573 Country_code,
2574 record_type_code ,
2575 created_by,
2576 creation_date,
2577 last_updated_by,
2578 last_update_date,
2579 last_update_login,
2580 OBJECT_VERSION_NUMBER)
2581 VALUES ('SUPPLIER_TYPE',
2582 zx_fc_codes_b_s.nextval,
2583 'SUPPLIER TYPE',
2584 sysdate,
2585 null,
2586 null,
2587 null,
2588 'MIGRATED',
2589 fnd_global.user_id,
2590 SYSDATE,
2591 fnd_global.user_id,
2592 SYSDATE,
2593 FND_GLOBAL.CONC_LOGIN_ID,
2594 1)
2595 SELECT '1' FROM DUAL
2596 WHERE NOT EXISTS
2597 (SELECT NULL
2598 FROM ZX_FC_CODES_B CODES
2599 WHERE CODES.CLASSIFICATION_TYPE_CODE =
2600 'SUPPLIER_TYPE' AND
2601 CODES.CLASSIFICATION_CODE =
2602 'SUPPLIER TYPE');
2603
2604 INSERT ALL
2605 INTO ZX_FC_CODES_TL(
2606 CLASSIFICATION_ID,
2607 CLASSIFICATION_NAME,
2608 CREATED_BY,
2609 CREATION_DATE,
2610 LAST_UPDATED_BY,
2611 LAST_UPDATE_DATE,
2612 LAST_UPDATE_LOGIN,
2613 LANGUAGE,
2614 SOURCE_LANG)
2615 VALUES (CLASSIFICATION_ID,
2616 'Supplier Type',
2617 fnd_global.user_id,
2618 SYSDATE,
2619 fnd_global.user_id,
2620 SYSDATE,
2621 FND_GLOBAL.CONC_LOGIN_ID,
2622 LANGUAGE_CODE,
2623 userenv('LANG'))
2624 INTO ZX_FC_CODES_DENORM_B(
2625 CLASSIFICATION_TYPE_ID,
2626 CLASSIFICATION_TYPE_CODE,
2627 CLASSIFICATION_TYPE_NAME,
2628 CLASSIFICATION_TYPE_CATEG_CODE,
2629 CLASSIFICATION_ID,
2630 CLASSIFICATION_CODE,
2631 CLASSIFICATION_NAME,
2632 LANGUAGE,
2633 EFFECTIVE_FROM,
2634 EFFECTIVE_TO,
2635 ENABLED_FLAG,
2636 ANCESTOR_ID,
2637 ANCESTOR_CODE,
2638 ANCESTOR_NAME,
2639 CONCAT_CLASSIF_CODE,
2640 CONCAT_CLASSIF_NAME,
2641 CLASSIFICATION_CODE_LEVEL,
2642 COUNTRY_CODE,
2643 SEGMENT1,
2644 SEGMENT2,
2645 SEGMENT3,
2646 SEGMENT4,
2647 SEGMENT5,
2648 SEGMENT6,
2649 SEGMENT7,
2650 SEGMENT8,
2651 SEGMENT9,
2652 SEGMENT10,
2653 SEGMENT1_NAME,
2654 SEGMENT2_NAME,
2655 SEGMENT3_NAME,
2656 SEGMENT4_NAME,
2657 SEGMENT5_NAME,
2658 SEGMENT6_NAME,
2659 SEGMENT7_NAME,
2660 SEGMENT8_NAME,
2661 SEGMENT9_NAME,
2662 SEGMENT10_NAME,
2663 CREATED_BY,
2664 CREATION_DATE,
2665 LAST_UPDATED_BY,
2666 LAST_UPDATE_LOGIN,
2667 LAST_UPDATE_DATE,
2668 REQUEST_ID,
2669 PROGRAM_ID,
2670 PROGRAM_APPLICATION_ID,
2671 PROGRAM_LOGIN_ID,
2672 RECORD_TYPE_CODE)
2673 VALUES (CLASSIFICATION_TYPE_ID,
2674 CLASSIFICATION_TYPE_CODE,
2675 CLASSIFICATION_TYPE_NAME,
2676 CLASSIFICATION_TYPE_CATEG_CODE,
2677 CLASSIFICATION_ID,
2678 'SUPPLIER TYPE',
2679 'Supplier Type',
2680 LANGUAGE_CODE,
2681 sysdate,
2682 null,
2683 null,
2684 null,
2685 null,
2686 null,
2687 'SUPPLIER TYPE',
2688 'Supplier Type',
2689 1,
2690 Null,
2691 'SUPPLIER TYPE',
2692 Null,
2693 Null,
2694 Null,
2695 Null,
2696 Null,
2697 Null,
2698 Null,
2699 Null,
2700 Null,
2701 'Supplier Type',
2702 Null,
2703 Null,
2704 Null,
2705 Null,
2706 Null,
2707 Null,
2708 Null,
2709 Null,
2710 Null,
2711 fnd_global.user_id,
2712 SYSDATE,
2713 fnd_global.user_id,
2714 FND_GLOBAL.CONC_LOGIN_ID,
2715 sysdate,
2716 FND_GLOBAL.CONC_REQUEST_ID,
2717 fnd_global.CONC_PROGRAM_ID,
2718 235,
2719 FND_GLOBAL.CONC_LOGIN_ID,
2720 'MIGRATED')
2721 SELECT
2722 TYPE.CLASSIFICATION_TYPE_ID,
2723 TYPE.CLASSIFICATION_TYPE_CODE,
2724 TYPE.CLASSIFICATION_TYPE_NAME,
2725 TYPE.Classification_Type_Categ_Code,
2726 TYPE.DELIMITER,
2727 CODE.CLASSIFICATION_ID,
2728 L.LANGUAGE_CODE
2729 FROM ZX_FC_TYPES_VL TYPE,
2730 ZX_FC_CODES_B CODE,
2731 FND_LANGUAGES L
2732 WHERE TYPE.CLASSIFICATION_TYPE_CODE = 'SUPPLIER_TYPE'
2733 AND TYPE.CLASSIFICATION_TYPE_CATEG_CODE
2734 ='PARTY_FISCAL_CLASS'
2735 AND TYPE.CLASSIFICATION_TYPE_CODE =
2736 CODE.CLASSIFICATION_TYPE_CODE
2737 AND TYPE.RECORD_TYPE_CODE = 'MIGRATED'
2738 AND L.INSTALLED_FLAG in ('I', 'B')
2739 AND not exists
2740 (select NULL
2741 from ZX_FC_CODES_TL T
2742 where T.CLASSIFICATION_ID =
2743 CODE.CLASSIFICATION_ID
2744 and T.LANGUAGE = L.LANGUAGE_CODE);*/
2745
2746
2747 arp_util_tax.debug(' SUPPLIER_TYPE_EXTRACT .. (-) ' );
2748
2749 END;
2750
2751 /*===========================================================================+
2752 | Procedure: SUPPLIER_ASSOC_EXTRACT |
2753 | |
2754 | |
2755 | Description: This procedure creates associations for |
2756 | a party types and Fiscal classification |
2757 | |
2758 | |
2759 | |
2760 | ARGUMENTS : |
2761 | |
2762 | |
2763 | |
2764 | |
2765 | |
2766 | NOTES |
2767 | |
2768 | |
2769 | |
2770 | |
2771 | |
2772 | History |
2773 | |
2774 | zmohiudd Wednesday, March 10, 2004 Created |
2775 | |
2776 | |
2777 | |
2778 | |
2779 +===========================================================================*/
2780
2781
2782 PROCEDURE SUPPLIER_ASSOC_EXTRACT IS
2783
2784 BEGIN
2785
2786 arp_util_tax.debug(' SUPPLIER_ASSOC_EXTRACT .. (+) ' );
2787 /*
2788 ---Migrate the association of Supplier Type to a Supplier.
2789 ---In PTP Extract
2790 INSERT INTO
2791 HZ_CODE_ASSIGNMENTS
2792 (CODE_ASSIGNMENT_ID,
2793 OWNER_TABLE_NAME,
2794 OWNER_TABLE_ID,
2795 CLASS_CATEGORY,
2796 CLASS_CODE,
2797 PRIMARY_FLAG,
2798 CONTENT_SOURCE_TYPE,
2799 ACTUAL_CONTENT_SOURCE,
2800 IMPORTANCE_RANKING,
2801 START_DATE_ACTIVE,
2802 END_DATE_ACTIVE,
2803 CREATED_BY,
2804 CREATION_DATE,
2805 LAST_UPDATE_LOGIN,
2806 LAST_UPDATE_DATE,
2807 LAST_UPDATED_BY,
2808 STATUS,
2809 OBJECT_VERSION_NUMBER,
2810 CREATED_BY_MODULE,
2811 APPLICATION_ID,
2812 RANK,
2813 OWNER_TABLE_KEY_1,
2814 OWNER_TABLE_KEY_2,
2815 OWNER_TABLE_KEY_3,
2816 OWNER_TABLE_KEY_4,
2817 OWNER_TABLE_KEY_5,
2818 PROGRAM_APPLICATION_ID,
2819 PROGRAM_ID)
2820
2821 Select
2822 HZ_CODE_ASSIGNMENTS_S.nextval,
2823 'ZX_PARTY_TAX_PROFILE',
2824 PTP.PARTY_TAX_PROFILE_ID party_tax_profile_id,
2825 'VENDOR TYPE',
2826 POV.VENDOR_TYPE_LOOKUP_CODE fiscal_classification_code,
2827 'N',
2828 'USER_ENTERED',
2829 'USER_ENTERED',
2830 Null,
2831 Sysdate,
2832 Null,
2833 fnd_global.user_id,
2834 Sysdate,
2835 FND_GLOBAL.CONC_LOGIN_ID,
2836 Sysdate,
2837 fnd_global.user_id,
2838 Null,
2839 1,
2840 'EBTAX MIGRATION',
2841 235,
2842 Null,
2843 Null,
2844 Null,
2845 Null,
2846 Null,
2847 NULL,
2848 fnd_global.PROG_APPL_ID,
2849 fnd_global.CONC_PROGRAM_ID
2850 FROM ap_suppliers POV ,
2851 ZX_PARTY_TAX_PROFILE PTP
2852 WHERE POV.PARTY_ID = PTP.PARTY_ID
2853 AND PTP.PARTY_TYPE_CODE = 'SUPPLIER'
2854 AND POV.VENDOR_TYPE_LOOKUP_CODE is not null;
2855 */
2856 arp_util_tax.debug(' SUPPLIER_ASSOC_EXTRACT .. (-) ' );
2857
2858 END ;
2859
2860 /*===========================================================================+
2861 | Procedure: Party_Assoc_Extract |
2862 | |
2863 | |
2864 | Description: This procedure creates associations for |
2865 | a party types and Fiscal classification |
2866 | |
2867 | |
2868 | |
2869 | |
2870 | ARGUMENTS : |
2871 | |
2872 | |
2873 | |
2874 | |
2875 | |
2876 | NOTES |
2877 | |
2878 | |
2879 | |
2880 | |
2881 | |
2882 | History |
2883 | |
2884 | zmohiudd Wednesday, March 10, 2004 Created |
2885 | |
2886 | |
2887 | |
2888 | |
2889 +===========================================================================*/
2890
2891 PROCEDURE Party_Assoc_Extract
2892 (p_party_source IN VARCHAR2,
2893 p_party_tax_profile_id IN NUMBER,
2894 p_fiscal_class_type_code IN VARCHAR2,
2895 p_fiscal_classification_code IN VARCHAR2 ,
2896 p_dml_type IN VARCHAR2)
2897 IS
2898 l_table_owner HZ_CODE_ASSIGNMENTS.OWNER_TABLE_NAME%TYPE := 'HZ_PARTIES';
2899
2900 BEGIN
2901
2902
2903 arp_util_tax.debug(' Party_Assoc_Extract .. (+) ' );
2904
2905 IF p_party_source in ('ZX_PARTY_TAX_PROFILE' , 'PO_VENDOR' ,'PO_VENDOR_SITES') THEN
2906 l_table_owner := 'ZX_PARTY_TAX_PROFILE';
2907 ELSIF p_party_source = 'HR_ORGANIZATIONS' or p_party_source = 'AP_REPORTING_ENTITIES' THEN
2908 l_table_owner := 'HZ_PARTIES';
2909 END IF;
2910
2911 IF p_dml_type= 'I' THEN
2912 BEGIN
2913
2914 INSERT INTO
2915 HZ_CODE_ASSIGNMENTS
2916 (CODE_ASSIGNMENT_ID,
2917 OWNER_TABLE_NAME,
2918 OWNER_TABLE_ID,
2919 CLASS_CATEGORY,
2920 CLASS_CODE,
2921 PRIMARY_FLAG,
2922 CONTENT_SOURCE_TYPE,
2923 ACTUAL_CONTENT_SOURCE,
2924 IMPORTANCE_RANKING,
2925 START_DATE_ACTIVE,
2926 END_DATE_ACTIVE,
2927 CREATED_BY,
2928 CREATION_DATE,
2929 LAST_UPDATE_LOGIN,
2930 LAST_UPDATE_DATE,
2931 LAST_UPDATED_BY,
2932 STATUS,
2933 OBJECT_VERSION_NUMBER,
2934 CREATED_BY_MODULE,
2935 APPLICATION_ID,
2936 RANK,
2937 OWNER_TABLE_KEY_1,
2938 OWNER_TABLE_KEY_2,
2939 OWNER_TABLE_KEY_3,
2940 OWNER_TABLE_KEY_4,
2941 OWNER_TABLE_KEY_5)
2942 values
2943 (HZ_CODE_ASSIGNMENTS_S.nextval,
2944 l_table_owner,
2945 p_party_tax_profile_id,
2946 p_fiscal_class_type_code,
2947 p_fiscal_classification_code,
2948 'N',
2949 'USER_ENTERED',
2950 'USER_ENTERED',
2951 Null,
2952 Sysdate,
2953 Null,
2954 fnd_global.user_id,
2955 Sysdate,
2956 FND_GLOBAL.CONC_LOGIN_ID,
2957 Sysdate,
2958 fnd_global.user_id,
2959 Null,
2960 Null,
2961 Null,
2962 235,
2963 Null,
2964 Null,
2965 Null,
2966 Null,
2967 Null,
2968 Null);
2969
2970
2971 EXCEPTION
2972 WHEN OTHERS THEN
2973 arp_util_tax.debug('Error while Inserting data into
2974 HZ_CODE_ASSIGNMENTS for Party ID ' ||
2975 p_party_tax_profile_id );
2976 arp_util_tax.debug('Error Code: '||SQLCODE|| '. Error Message '|| SQLERRM);
2977
2978 END ;
2979
2980 ELSIF p_dml_type= 'U' THEN
2981
2982 IF p_fiscal_classification_code is Not NULL THEN
2983
2984 -- If Update fails it means that for this record
2985 -- VENDOR_TYPE_LOOKUP_CODE was updated from Null to NOT Null
2986 -- In this case we create a new row.
2987
2988 arp_util_tax.debug('Updating the data , since the fiscal classification code is not null now ');
2989
2990
2991
2992
2993 BEGIN
2994
2995
2996
2997 MERGE INTO HZ_CODE_ASSIGNMENTS pfa
2998 USING (SELECT
2999 p_party_tax_profile_id party_Tax_profile_id ,
3000 p_fiscal_class_type_code fiscal_class_type_code,
3001 p_fiscal_classification_code fiscal_classification_code
3002 FROM DUAL) fc
3003 ON (pfa.OWNER_TABLE_ID = fc.PARTY_TAX_PROFILE_ID and
3004 pfa.CLASS_CATEGORY = fc.fiscal_class_type_code and
3005 pfa.class_code = fc.fiscal_classification_code)
3006 WHEN MATCHED THEN UPDATE SET
3007 OWNER_TABLE_NAME = l_table_owner ,
3008 OWNER_TABLE_ID = fc.PARTY_TAX_PROFILE_ID,
3009 CLASS_CATEGORY = fc.fiscal_class_type_code,
3010 CLASS_CODE = fc.fiscal_classification_code,
3011 PRIMARY_FLAG = 'N',
3012 CONTENT_SOURCE_TYPE = 'USER_ENTERED',
3013 ACTUAL_CONTENT_SOURCE = 'USER_ENTERED',
3014 IMPORTANCE_RANKING = Null,
3015 START_DATE_ACTIVE = SYSDATE,
3016 END_DATE_ACTIVE = Null,
3017 CREATED_BY = fnd_global.user_id,
3018 CREATION_DATE = SYSDATE,
3019 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID,
3020 LAST_UPDATE_DATE = SYSDATE,
3021 LAST_UPDATED_BY = fnd_global.user_id,
3022 APPLICATION_ID = 235
3023 WHEN NOT MATCHED THEN INSERT
3024 (
3025 CODE_ASSIGNMENT_ID,
3026 OWNER_TABLE_NAME,
3027 OWNER_TABLE_ID,
3028 CLASS_CATEGORY,
3029 CLASS_CODE,
3030 PRIMARY_FLAG,
3031 CONTENT_SOURCE_TYPE,
3032 ACTUAL_CONTENT_SOURCE,
3033 IMPORTANCE_RANKING,
3034 START_DATE_ACTIVE,
3035 END_DATE_ACTIVE,
3036 CREATED_BY,
3037 CREATION_DATE,
3038 LAST_UPDATE_LOGIN,
3039 LAST_UPDATE_DATE,
3040 LAST_UPDATED_BY,
3041 STATUS,
3042 OBJECT_VERSION_NUMBER,
3043 CREATED_BY_MODULE,
3044 APPLICATION_ID,
3045 RANK,
3046 OWNER_TABLE_KEY_1,
3047 OWNER_TABLE_KEY_2,
3048 OWNER_TABLE_KEY_3,
3049 OWNER_TABLE_KEY_4,
3050 OWNER_TABLE_KEY_5
3051 )
3052 values
3053 (
3054 HZ_CODE_ASSIGNMENTS_S.nextval,
3055 l_table_owner,
3056 p_party_tax_profile_id,
3057 p_fiscal_class_type_code,
3058 p_fiscal_classification_code,
3059 'N',
3060 'USER_ENTERED',
3061 'USER_ENTERED',
3062 Null,
3063 Sysdate,
3064 Null,
3065 fnd_global.user_id,
3066 Sysdate,
3067 FND_GLOBAL.CONC_LOGIN_ID,
3068 Sysdate,
3069 fnd_global.user_id,
3070 Null,
3071 Null,
3072 Null,
3073 235,
3074 Null,
3075 Null,
3076 Null,
3077 Null,
3078 Null,
3079 Null);
3080
3081
3082 EXCEPTION
3083 WHEN OTHERS THEN
3084 arp_util_tax.debug('Error while merging data into
3085 HZ_CODE_ASSIGNMENTS for Party ID ' || p_party_tax_profile_id );
3086 arp_util_tax.debug('Error Code: '||SQLCODE||'. Error Message '|| SQLERRM);
3087 END;
3088
3089
3090 ELSE
3091
3092
3093 arp_util_tax.debug('Updating the data in HZ_CODE_ASSIGNMENTS ');
3094
3095
3096 UPDATE HZ_CODE_ASSIGNMENTS
3097 SET
3098 OWNER_TABLE_NAME = l_table_owner,
3099 OWNER_TABLE_ID = P_PARTY_TAX_PROFILE_ID,
3100 CLASS_CATEGORY = P_fiscal_class_type_code,
3101 CLASS_CODE = P_fiscal_classification_code,
3102 PRIMARY_FLAG = 'N',
3103 CONTENT_SOURCE_TYPE = 'USER_ENTERED',
3104 ACTUAL_CONTENT_SOURCE = 'USER_ENTERED',
3105 IMPORTANCE_RANKING = Null,
3106 START_DATE_ACTIVE = SYSDATE,
3107 END_DATE_ACTIVE = Null,
3108 CREATED_BY = fnd_global.user_id,
3109 CREATION_DATE = SYSDATE,
3110 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID,
3111 LAST_UPDATE_DATE = SYSDATE,
3112 LAST_UPDATED_BY = fnd_global.user_id,
3113 APPLICATION_ID = 235
3114 WHERE
3115 OWNER_TABLE_ID = p_party_tax_profile_id and
3116 CLASS_CATEGORY = p_fiscal_class_type_code and
3117 CLASS_CODE = p_fiscal_classification_code ;
3118
3119 END IF; -- Classification Code not null
3120
3121 END IF;
3122
3123
3124 arp_util_tax.debug(' Party_Assoc_Extract .. (-) ' );
3125
3126 END;
3127
3128
3129 /*===========================================================================+
3130 | Procedure: SUPPLIER_TYPE_MIGRATION |
3131 | |
3132 | |
3133 | Description: This procedure is used to carry out Supplier Type |
3134 | Extract after the Supplier Migration has occurred |
3135 | |
3136 | |
3137 | |
3138 | |
3139 | ARGUMENTS : |
3140 | |
3141 | |
3142 | |
3143 | |
3144 | |
3145 | NOTES |
3146 | |
3147 | |
3148 | |
3149 | |
3150 | |
3151 | History |
3152 | |
3153 | Arnab Sengupta Wednesday, May 4th , 2005 Created |
3154 | |
3155 | |
3156 | |
3157 | |
3158 +===========================================================================*/
3159
3160
3161 PROCEDURE SUPPLIER_TYPE_MIGRATION IS
3162 l_status fnd_module_installations.status%TYPE;
3163 l_db_status fnd_module_installations.DB_STATUS%TYPE;
3164
3165 BEGIN
3166 -- Bug # 3594759
3167 -- Verify Argentina Installation
3168 SELECT STATUS, DB_STATUS
3169 INTO l_status, l_db_status
3170 FROM fnd_module_installations
3171 WHERE APPLICATION_ID = '7004'
3172 And MODULE_SHORT_NAME = 'jlarloc';
3173
3174 IF (nvl(l_status,'N') in ('I','S') or
3175 nvl(l_db_status,'N') in ('I','S')) THEN
3176
3177 -- Code to migrate the lookup code for the Lookup Type
3178 -- JLZZ_AP_VAT_REG_STAT_CODE into
3179 -- ZX_REGISTRATIONS_STATUS lookup type
3180 insert into fnd_lookup_values(
3181 LOOKUP_TYPE,
3182 LANGUAGE,
3183 LOOKUP_CODE,
3184 MEANING,
3185 DESCRIPTION,
3186 ENABLED_FLAG,
3187 START_DATE_ACTIVE,
3188 END_DATE_ACTIVE,
3189 CREATED_BY,
3190 CREATION_DATE,
3191 LAST_UPDATED_BY,
3192 LAST_UPDATE_LOGIN,
3193 LAST_UPDATE_DATE,
3194 SOURCE_LANG,
3195 SECURITY_GROUP_ID,
3196 VIEW_APPLICATION_ID,
3197 TERRITORY_CODE,
3198 ATTRIBUTE_CATEGORY,
3199 ATTRIBUTE1,
3200 ATTRIBUTE2,
3201 ATTRIBUTE3,
3202 ATTRIBUTE4,
3203 ATTRIBUTE5,
3204 ATTRIBUTE6,
3205 ATTRIBUTE7,
3206 ATTRIBUTE8,
3207 ATTRIBUTE9,
3208 ATTRIBUTE10,
3209 ATTRIBUTE11,
3210 ATTRIBUTE12,
3211 ATTRIBUTE13,
3212 ATTRIBUTE14,
3213 ATTRIBUTE15)
3214 (select
3215 'ZX_REGISTRATIONS_STATUS',
3216 LANGUAGE,
3217 LOOKUP_CODE,
3218 MEANING,
3219 DESCRIPTION,
3220 ENABLED_FLAG,
3221 START_DATE_ACTIVE,
3222 END_DATE_ACTIVE,
3223 fnd_global.user_id,
3224 SYSDATE,
3225 fnd_global.user_id,
3226 fnd_global.conc_login_id,
3227 SYSDATE,
3228 SOURCE_LANG,
3229 SECURITY_GROUP_ID,
3230 VIEW_APPLICATION_ID,
3231 TERRITORY_CODE,
3232 ATTRIBUTE_CATEGORY,
3233 ATTRIBUTE1,
3234 ATTRIBUTE2,
3235 ATTRIBUTE3,
3236 ATTRIBUTE4,
3237 ATTRIBUTE5,
3238 ATTRIBUTE6,
3239 ATTRIBUTE7,
3240 ATTRIBUTE8,
3241 ATTRIBUTE9,
3242 ATTRIBUTE10,
3243 ATTRIBUTE11,
3244 ATTRIBUTE12,
3245 ATTRIBUTE13,
3246 ATTRIBUTE14,
3247 ATTRIBUTE15
3248 FROM
3249 FND_LOOKUP_VALUES fnd
3250 WHERE
3251 fnd.LOOKUP_TYPE = 'JLZZ_AP_VAT_REG_STAT_CODE'
3252 AND NOT EXISTS
3253 ( select 1 from FND_LOOKUP_VALUES
3254 where lookup_type = 'JLZZ_AP_VAT_REG_STAT_CODE' and
3255 lookup_code = fnd.lookup_code) );
3256
3257 END IF;
3258 arp_util_tax.debug(' Now calling SUPPLIER_TYPE_EXTRACT ' );
3259
3260 ZX_PTP_MIGRATE_PKG.SUPPLIER_TYPE_EXTRACT;
3261
3262
3263 arp_util_tax.debug(' Now calling SUPPLIER_ASSOC_EXTRACT ' );
3264
3265 ZX_PTP_MIGRATE_PKG.SUPPLIER_ASSOC_EXTRACT;
3266
3267
3268 END SUPPLIER_TYPE_MIGRATION;
3269
3270
3271 /*===========================================================================+
3272 | Procedure: ZX_PTP_MAIN |
3273 | |
3274 | |
3275 | Description: This procedure is the main wrapper procedure |
3276 | for party tax profile migration |
3277 | |
3278 | |
3279 | |
3280 | |
3281 | ARGUMENTS : |
3282 | |
3283 | |
3284 | |
3285 | |
3286 | |
3287 | NOTES |
3288 | |
3289 | |
3290 | |
3291 | |
3292 | |
3293 | History |
3294 | |
3295 | zmohiudd Wednesday, March 10, 2004 Created |
3296 | |
3297 | |
3298 | |
3299 | |
3300 +===========================================================================*/
3301
3302 PROCEDURE ZX_PTP_MAIN IS
3303
3304 BEGIN
3305
3306 arp_util_tax.debug(' ZX_PTP_MAIN .. (+) ' );
3307
3308 arp_util_tax.debug(' Now calling FIRST_PARTY_EXTRACT.. ' );
3309
3310 ZX_PTP_MIGRATE_PKG.FIRST_PARTY_EXTRACT(null);
3311
3312 arp_util_tax.debug(' calling LEGAL_ESTABLISHMENT... ' );
3313
3314 ZX_PTP_MIGRATE_PKG.LEGAL_ESTABLISHMENT(null);
3315
3316 /* The following calls have been commented out on account of bug 4317072
3317 SQL scripts have been written to execute the code in these procedures .These scripts will get
3318 called in the appropriate phase .From now on these calls will be made explicitly for synchronization
3319 purpose only */
3320
3321
3322 /*
3323 arp_util_tax.debug(' calling SUPPLIER_EXTRACT... ' );
3324
3325 ZX_PTP_MIGRATE_PKG.SUPPLIER_EXTRACT(null, null);
3326
3327 arp_util_tax.debug(' calling SUPPLIER_SITE_EXTRACT... ' );
3328
3329 ZX_PTP_MIGRATE_PKG.SUPPLIER_SITE_EXTRACT(null, null) ;
3330 */
3331 END;
3332
3333 BEGIN
3334
3335 SELECT NVL(MULTI_ORG_FLAG,'N') INTO L_MULTI_ORG_FLAG FROM
3336 FND_PRODUCT_GROUPS;
3337
3338 IF L_MULTI_ORG_FLAG = 'N' THEN
3339
3340 FND_PROFILE.GET('ORG_ID',L_ORG_ID);
3341
3342 IF L_ORG_ID IS NULL THEN
3343 arp_util_tax.debug('MO: Operating Units site level profile option value not set,
3344 resulted in Null Org Id');
3345 END IF;
3346 ELSE
3347 L_ORG_ID := NULL;
3348 END IF;
3349
3350 EXCEPTION
3351 WHEN OTHERS THEN
3352 arp_util_tax.debug('Exception in constructor of P2P PTP '||sqlerrm);
3353
3354 END ZX_PTP_MIGRATE_PKG;