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