[Home] [Help]
PACKAGE BODY: APPS.ZX_FC_MIGRATE_PKG
Source
1 PACKAGE BODY ZX_FC_MIGRATE_PKG AS
2 /* $Header: zxfcmigrateb.pls 120.71.12010000.1 2008/07/28 13:32:02 appldev ship $ */
3
4 l_multi_org_flag fnd_product_groups.multi_org_flag%type;
5 l_org_id NUMBER(15);
6
7 /*THIS IS THE COMMON PROCEDURE USED TO INSERT THE GLOBAL DESCRIPTIVE FLEXI FIELD PROMPT VALUE*/
8 PROCEDURE GDF_PROMPT_INSERT(
9 p_classification_code IN ZX_FC_CODES_B.CLASSIFICATION_CODE%TYPE,
10 p_classification_name IN ZX_FC_CODES_TL.CLASSIFICATION_NAME%TYPE,
11 p_country_code IN ZX_FC_CODES_B.COUNTRY_CODE%TYPE,
12 p_tax_event_class_code IN ZX_EVENT_CLASSES_VL.TAX_EVENT_CLASS_CODE%TYPE
13 );
14
15 /* COMMON PROCEDURE USED TO ASSOCIATE THE ITEMS */
16 PROCEDURE ASSOCIATE_ITEMS(
17 p_global_attribute_category IN varchar2);
18
19 Procedure Update_Category_Set (p_category_set_name IN VARCHAR2,
20 p_category_set_id IN NUMBER);
21
22 G_CLASSIFICATION_TYPE_ID zx_fc_types_b.classification_type_id%type;
23 G_CLASSIFICATION_TYPE_CODE zx_fc_types_b.classification_type_code%type;
24 G_CLASSIFICATION_TYPE_NAME zx_fc_types_tl.classification_type_name%type;
25 G_CLASSIFICATION_TYP_CATEG_COD zx_fc_codes_denorm_b.classification_type_categ_code%type;
26 G_DELIMITER zx_fc_types_b.delimiter%type;
27
28 TYPE NUM_TAB is table of number index by binary_integer;
29 TYPE VAR30_TAB is table of varchar2(30) index by binary_integer;
30
31 CURSOR G_C_GET_TYPES_INFO(X_CLASSIFICATION_TYPE_CODE zx_fc_types_b.CLASSIFICATION_TYPE_CODE%type) is
32 SELECT
33 TYPE.CLASSIFICATION_TYPE_ID,
34 TYPE.CLASSIFICATION_TYPE_CODE,
35 TYPE.CLASSIFICATION_TYPE_NAME,
36 TYPE.Classification_Type_Categ_Code,
37 TYPE.DELIMITER
38 FROM ZX_FC_TYPES_VL TYPE
39 WHERE TYPE.CLASSIFICATION_TYPE_CODE = X_CLASSIFICATION_TYPE_CODE;
40
41 /*===========================================================================+
42 | Procedure: CREATE_CATEGORY_SETS |
43 | Description: |
44 | |
45 | ARGUMENTS : |
46 | |
47 | |
48 | History |
49 | rguerrer Created |
50 | |
51 +===========================================================================*/
52
53
54 Procedure Create_Category_Sets IS
55
56 p_category_set mtl_category_sets_b.Category_set_ID%TYPE;
57 p_flexfield FND_FLEX_KEY_API.FLEXFIELD_TYPE;
58 p_structure_id FND_FLEX_KEY_API.STRUCTURE_TYPE;
59 v_structure_id FND_FLEX_KEY_API.STRUCTURE_TYPE;
60 l_segment FND_FLEX_KEY_API.segment_type;
61 p_StatCode_Segment FND_FLEX_KEY_API.segment_type;
62 l_flex_exists Boolean;
63 l_structure_exists Boolean;
64 l_segment_exists Boolean;
65 p_StatCode_struct FND_FLEX_KEY_API.STRUCTURE_TYPE;
66 msg VARCHAR2(1000);
67 l_category_set_id mtl_category_sets_b.Category_set_ID%TYPE;
68
69 l_structure_id NUMBER;
70 l_control_level NUMBER;
71 l_row_id VARCHAR2(100);
72 l_next_val NUMBER;
73
74 Begin
75
76 fnd_flex_key_api.set_session_mode('seed_data');
77 l_flex_exists:= FALSE;
78
79 l_flex_exists:= fnd_flex_key_api.flexfield_exists(appl_short_name => 'INV',flex_code => 'MCAT',flex_title => 'Item Categories');
80
81 If l_flex_exists Then
82 p_flexfield:= fnd_flex_key_api.find_flexfield(appl_short_name => 'INV',flex_code => 'MCAT');
83
84 BEGIN
85 p_StatCode_struct:= fnd_flex_key_api.find_structure(p_flexfield,'STATISTICAL_CODE');
86 l_structure_exists:=TRUE;
87
88 EXCEPTION
89 WHEN OTHERS THEN
90 msg := 'ERROR: struct not found' || fnd_flex_key_api.message;
91 l_structure_exists:=FALSE;
92 END;
93
94 IF NOT l_structure_exists THEN
95 BEGIN
96 p_StatCode_struct:=fnd_flex_key_api.new_structure(flexfield => p_flexfield,
97 structure_code => 'STATISTICAL_CODE',
98 structure_title => 'Statistical Code',
99 description => 'Statistical Code',
100 view_name => NULL,
101 freeze_flag => 'Y',
102 enabled_flag => 'Y',
103 segment_separator => '.',
104 cross_val_flag => 'N',
105 freeze_rollup_flag=> 'N',
106 dynamic_insert_flag => 'N',
107 shorthand_enabled_flag => 'N',
108 shorthand_prompt => NULL,
109 shorthand_length => NULL );
110
111 fnd_flex_key_api.add_structure(p_flexfield,p_StatCode_struct);
112
113
114 BEGIN
115 -- Coded needed to instantiate the Structure
116 p_statcode_struct:= fnd_flex_key_api.find_structure(p_flexfield,'STATISTICAL_CODE');
117 l_structure_exists:=TRUE;
118
119 EXCEPTION
120 WHEN OTHERS THEN
121 msg := SUBSTR('ERROR: struct not found' || fnd_flex_key_api.message,1,225);
122 END;
123
124 EXCEPTION
125 WHEN OTHERS THEN
126 msg := substr('ERROR: ' || fnd_flex_key_api.message,1,225);
127 END;
128
129 END IF;
130
131 IF l_structure_exists THEN
132
133 BEGIN
134 p_StatCode_Segment:= fnd_flex_key_api.find_segment(flexfield=> p_flexfield,structure=> p_StatCode_struct,segment_name=>'Code');
135 l_segment_exists:=TRUE;
136 EXCEPTION
137 WHEN OTHERS THEN
138 msg := substr('ERROR: ' || fnd_flex_key_api.message,1,225);
139 l_segment_exists:=FALSE;
140 END;
141
142 IF NOT l_segment_exists THEN
143 BEGIN
144 p_StatCode_Segment:= fnd_flex_key_api.new_segment(flexfield => p_flexfield,
145 structure => p_StatCode_struct,
146 segment_name => 'Code',
147 description => 'Code',
148 column_name => 'SEGMENT1',
149 segment_number => 10,
150 enabled_flag => 'Y',
151 displayed_flag => 'Y',
152 indexed_flag => 'Y',
153 value_set => '30 Characters',
154 default_type => NULL,
155 default_value => NULL,
156 required_flag =>'Y',
157 security_flag => 'N',
158 range_code => NULL,
159 display_size => 25,
160 description_size => 50,
161 concat_size => 25,
162 lov_prompt => 'Code',
163 window_prompt => 'Code',
164 runtime_property_function => NULL,
165 additional_where_clause => NULL);
166
167 EXCEPTION
168 WHEN OTHERS THEN
169 msg := SUBSTR('ERROR: ' || fnd_flex_key_api.message,1,225);
170 END;
171
172 BEGIN
173 fnd_flex_key_api.add_segment(p_flexfield,p_StatCode_struct,p_StatCode_Segment);
174 l_segment_exists:=TRUE;
175 EXCEPTION
176 WHEN OTHERS THEN
177 msg := SUBSTR('ERROR: ' || fnd_flex_key_api.message,1,225);
178 l_segment_exists:=FALSE;
179 END;
180 END IF;
181
182 END IF; -- Structure available for segment
183
184 End If; -- Flexfield Exists.
185
186 IF l_segment_exists THEN
187
188 SELECT FIFS.ID_FLEX_NUM
189 INTO l_structure_id
190 FROM FND_ID_FLEX_STRUCTURES FIFS
191 WHERE FIFS.ID_FLEX_CODE = 'MCAT'
192 AND FIFS.APPLICATION_ID = 401
193 AND FIFS.ID_FLEX_STRUCTURE_CODE = 'STATISTICAL_CODE';
194
195 BEGIN
196 SELECT category_set_ID, structure_id
197 INTO l_category_set_id, l_structure_id
198 FROM mtl_category_sets
199 WHERE Category_Set_Name ='STATISTICAL_CODE';
200 EXCEPTION
201 WHEN NO_DATA_FOUND THEN
202 l_category_set_id := NULL;
203 END;
204
205 IF l_category_set_id IS NULL THEN
206
207 SELECT MTL_CATEGORY_SETS_S.NEXTVAL into l_next_val from dual;
208 -- Assuming Master Organization Control level
209 -- Bug 6441455, X_CONTROL_LEVEL - Passing 2 for Org control
210
211 MTL_CATEGORY_SETS_PKG.INSERT_ROW (
212 X_ROWID => l_row_id,
213 X_CATEGORY_SET_ID => l_next_val,
214 X_CATEGORY_SET_NAME => 'STATISTICAL_CODE',
215 X_DESCRIPTION => 'Statistical Code',
216 X_STRUCTURE_ID => l_structure_id,
217 X_VALIDATE_FLAG => 'N',
218 X_MULT_ITEM_CAT_ASSIGN_FLAG => 'N',
219 X_CONTROL_LEVEL_UPDT_FLAG => 'Y',
220 X_MULT_ITEM_CAT_UPDT_FLAG => 'Y',
221 X_VALIDATE_FLAG_UPDT_FLAG => 'Y',
222 X_HIERARCHY_ENABLED => 'N',
223 X_CONTROL_LEVEL => 2,
224 X_DEFAULT_CATEGORY_ID => null,
225 X_LAST_UPDATE_DATE => SYSDATE,
226 X_LAST_UPDATED_BY => 0,
227 X_CREATION_DATE => SYSDATE,
228 X_CREATED_BY => 0,
229 X_LAST_UPDATE_LOGIN => 0 );
230 END IF;
231
232 -- Update the descriptions to set them in correct Language, Source Lang.
233 Update_Category_Set ('STATISTICAL_CODE',l_category_set_id);
234
235 END IF;
236
237 END;
238
239
240 Procedure Create_Category_Set (p_structure_code IN VARCHAR2,
241 p_structure_desc IN VARCHAR2,
242 p_category_set_name IN VARCHAR2,
243 p_category_set_desc IN VARCHAR2 )
244 IS
245
246 p_category_set mtl_category_sets_b.Category_set_ID%TYPE;
247 p_flexfield FND_FLEX_KEY_API.FLEXFIELD_TYPE;
248 p_structure_id FND_FLEX_KEY_API.STRUCTURE_TYPE;
249 v_structure_id FND_FLEX_KEY_API.STRUCTURE_TYPE;
250 l_segment FND_FLEX_KEY_API.segment_type;
251 p_StatCode_Segment FND_FLEX_KEY_API.segment_type;
252 l_flex_exists Boolean;
253 l_structure_exists Boolean;
254 l_segment_exists Boolean;
255 p_StatCode_struct FND_FLEX_KEY_API.STRUCTURE_TYPE;
256 msg VARCHAR2(1000);
257 l_category_set_id mtl_category_sets_b.Category_set_ID%TYPE;
258
259 l_structure_id NUMBER;
260 l_control_level NUMBER;
261 l_row_id VARCHAR2(100);
262 l_next_val NUMBER;
263
264 Begin
265
266 fnd_flex_key_api.set_session_mode('seed_data');
267 l_flex_exists:= FALSE;
268
269 l_flex_exists:= fnd_flex_key_api.flexfield_exists(appl_short_name => 'INV',flex_code => 'MCAT',flex_title => 'Item Categories');
270
271 If l_flex_exists Then
272 p_flexfield:= fnd_flex_key_api.find_flexfield(appl_short_name => 'INV',flex_code => 'MCAT');
273
274 BEGIN
275 p_StatCode_struct:= fnd_flex_key_api.find_structure(p_flexfield,p_structure_code);
276 l_structure_exists:=TRUE;
277
278 EXCEPTION
279 WHEN OTHERS THEN
280 msg := 'ERROR: struct not found' || fnd_flex_key_api.message;
281 l_structure_exists:=FALSE;
282 END;
283
284 IF NOT l_structure_exists THEN
285 BEGIN
286 p_StatCode_struct:=fnd_flex_key_api.new_structure(flexfield => p_flexfield,
287 structure_code => p_structure_code,
288 structure_title => p_structure_desc,
289 description => p_structure_desc,
290 view_name => NULL,
291 freeze_flag => 'Y',
292 enabled_flag => 'Y',
293 segment_separator => '.',
294 cross_val_flag => 'N',
295 freeze_rollup_flag=> 'N',
296 dynamic_insert_flag => 'N',
297 shorthand_enabled_flag => 'N',
298 shorthand_prompt => NULL,
299 shorthand_length => NULL );
300
301 fnd_flex_key_api.add_structure(p_flexfield,p_StatCode_struct);
302
303
304 BEGIN
305 -- Code needed to instantiate the Structure
306 p_statcode_struct:= fnd_flex_key_api.find_structure(p_flexfield,p_structure_code);
307 l_structure_exists:=TRUE;
308
309 EXCEPTION
310 WHEN OTHERS THEN
311 msg := SUBSTR('ERROR: struct not found' || fnd_flex_key_api.message,1,225);
312 END;
313
314 EXCEPTION
315 WHEN OTHERS THEN
316 msg := substr('ERROR: ' || fnd_flex_key_api.message,1,225);
317 END;
318
319 END IF;
320
321 IF l_structure_exists THEN
322
323 BEGIN
324 p_StatCode_Segment:= fnd_flex_key_api.find_segment(flexfield=> p_flexfield,structure=> p_StatCode_struct,segment_name=>'Code');
325 l_segment_exists:=TRUE;
326 EXCEPTION
327 WHEN OTHERS THEN
328 msg := substr('ERROR: ' || fnd_flex_key_api.message,1,225);
329 l_segment_exists:=FALSE;
330 END;
331
332 IF NOT l_segment_exists THEN
333 BEGIN
334 p_StatCode_Segment:= fnd_flex_key_api.new_segment(flexfield => p_flexfield,
335 structure => p_StatCode_struct,
336 segment_name => 'Code',
337 description => 'Code',
338 column_name => 'SEGMENT1',
339 segment_number => 10,
340 enabled_flag => 'Y',
341 displayed_flag => 'Y',
342 indexed_flag => 'Y',
343 value_set => '30 Characters',
344 default_type => NULL,
345 default_value => NULL,
346 required_flag =>'Y',
347 security_flag => 'N',
348 range_code => NULL,
349 display_size => 25,
350 description_size => 50,
351 concat_size => 25,
352 lov_prompt => 'Code',
353 window_prompt => 'Code',
354 runtime_property_function => NULL,
355 additional_where_clause => NULL);
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 msg := SUBSTR('ERROR: ' || fnd_flex_key_api.message,1,225);
360 END;
361
362 BEGIN
363 fnd_flex_key_api.add_segment(p_flexfield,p_StatCode_struct,p_StatCode_Segment);
364 l_segment_exists:=TRUE;
365 EXCEPTION
366 WHEN OTHERS THEN
367 msg := SUBSTR('ERROR: ' || fnd_flex_key_api.message,1,225);
368 l_segment_exists:=FALSE;
369 END;
370 END IF;
371
372 END IF; -- Structure available for segment
373
374 End If; -- Flexfield Exists.
375
376 IF l_segment_exists THEN
377
378 SELECT FIFS.ID_FLEX_NUM
379 INTO l_structure_id
380 FROM FND_ID_FLEX_STRUCTURES FIFS
381 WHERE FIFS.ID_FLEX_CODE = 'MCAT'
382 AND FIFS.APPLICATION_ID = 401
383 AND FIFS.ID_FLEX_STRUCTURE_CODE = p_structure_code;
384
385 BEGIN
386 SELECT category_set_ID, structure_id
387 INTO l_category_set_id, l_structure_id
388 FROM mtl_category_sets
389 WHERE Category_Set_Name = p_category_set_name;
390 EXCEPTION
391 WHEN NO_DATA_FOUND THEN
392 l_category_set_id := NULL;
393 END;
394
395 IF l_category_set_id IS NULL THEN
396 SELECT MTL_CATEGORY_SETS_S.NEXTVAL into l_next_val from dual;
397
398 -- Assuming Master Organization Control level
399 -- Bug 6441455, X_CONTROL_LEVEL - Passing 2 for Org control
400
401 MTL_CATEGORY_SETS_PKG.INSERT_ROW (
402 X_ROWID => l_row_id,
403 X_CATEGORY_SET_ID => l_next_val,
404 X_CATEGORY_SET_NAME => p_category_set_name,
405 X_DESCRIPTION => p_category_set_desc,
406 X_STRUCTURE_ID => l_structure_id,
407 X_VALIDATE_FLAG => 'N',
408 X_MULT_ITEM_CAT_ASSIGN_FLAG => 'N',
409 X_CONTROL_LEVEL_UPDT_FLAG => 'Y',
410 X_MULT_ITEM_CAT_UPDT_FLAG => 'Y',
414 X_DEFAULT_CATEGORY_ID => null,
411 X_VALIDATE_FLAG_UPDT_FLAG => 'Y',
412 X_HIERARCHY_ENABLED => 'N',
413 X_CONTROL_LEVEL => 2,
415 X_LAST_UPDATE_DATE => SYSDATE,
416 X_LAST_UPDATED_BY => 0,
417 X_CREATION_DATE => SYSDATE,
418 X_CREATED_BY => 0,
419 X_LAST_UPDATE_LOGIN => 0 );
420 END IF;
421
422 -- Update the descriptions to set them in correct Language, Source Lang.
423 Update_Category_Set (p_category_set_name,l_category_set_id);
424
425 END IF;
426
427 END;
428
429
430 Procedure Update_Category_Set (p_category_set_name IN VARCHAR2,
431 p_category_set_id IN NUMBER)
432 IS
433 msg VARCHAR2(1000);
434 l_structure_id NUMBER;
435 l_control_level NUMBER;
436 l_context VARCHAR2(500);
437 l_global_att VARCHAR2(500);
438 l_category_status VARCHAR2(500);
439 Begin
440
441 IF P_CATEGORY_SET_NAME = 'STATISTICAL_CODE' THEN
442 l_context :='JE.HU.APINXWKB.FINAL';
443 l_global_att := 'GLOBAL_ATTRIBUTE6';
444 ELSIF P_CATEGORY_SET_NAME = 'WINE_CIGARRETE_CATEGORY' THEN
445 l_context :='JA.TW.ARXRWMAI.CASH_RECEIPTS';
446 l_global_att := 'GLOBAL_ATTRIBUTE2';
447 ELSIF P_CATEGORY_SET_NAME = 'FISCAL_CLASSIFICATION' THEN
448 l_context :='JL.BR.ARXSDML.Additional';
449 l_global_att := 'GLOBAL_ATTRIBUTE1';
450 ELSIF P_CATEGORY_SET_NAME = 'INTENDED_USE' THEN
451 l_context :='JL.AR.APXINWKB.INVOICES';
452 l_global_att := 'GLOBAL_ATTRIBUTE10';
453 END IF;
454
455 update MTL_CATEGORY_SETS_TL MS set (
456 SOURCE_LANG ,
457 LANGUAGE ,
458 CATEGORY_SET_NAME ,
459 DESCRIPTION ,
460 LAST_UPDATE_DATE ,
461 LAST_UPDATED_BY ,
462 LAST_UPDATE_LOGIN ) =
463 (SELECT
464 SOURCE_LANG ,
465 LANGUAGE ,
466 P_CATEGORY_SET_NAME,
467 FORM_LEFT_PROMPT,
468 SYSDATE,
469 0,
470 0
471 FROM FND_DESCR_FLEX_COL_USAGE_TL FL
472 WHERE FL.LANGUAGE= MS.LANGUAGE
473 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = l_context
474 AND APPLICATION_COLUMN_NAME = l_global_att)
475 where CATEGORY_SET_ID = P_CATEGORY_SET_ID;
476
477 EXCEPTION WHEN OTHERS THEN
478 l_category_status :='NOTEXISTS';
479
480 end;
481
482
483 /*===========================================================================+
484 | Procedure: CREATE_MTL_CATEGORIES |
485 | Description: This Procedure describes data migration for |
486 | LOOKUPS To Item Categories |
487 | |
488 | |
489 | |
490 | |
491 | ARGUMENTS : |
492 | |
493 | |
494 | NOTES |
495 | |
496 | |
497 | |
498 | History |
499 | zmohiudd Created |
500 | |
501 | |
502 +===========================================================================*/
503
504 PROCEDURE CREATE_MTL_CATEGORIES (
505 l_lookup_type IN VARCHAR2,
506 l_category_name IN VARCHAR2,
507 l_category_status OUT NOCOPY VARCHAR2,
508 l_category_set OUT NOCOPY mtl_category_sets_b.Category_set_ID%TYPE,
509 l_structure_id OUT NOCOPY mtl_category_sets_b.structure_id%TYPE )
510 IS
511 p_category_set MTL_CATEGORY_SETS_B.CATEGORY_SET_ID%TYPE;
512 p_structure_id mtl_category_sets_b.structure_id%TYPE;
513 l_category_set_exists BOOLEAN;
514
515 BEGIN
516
517 /* Get the Seeded Item Category Set Value */
518 BEGIN
519 SELECT Category_set_ID, structure_id
520 INTO p_category_set, p_structure_id
521 FROM mtl_category_sets
522 WHERE Category_Set_Name =l_category_name;
523
524 l_category_set_exists :=TRUE;
525 l_category_status :='EXISTS';
526
527 EXCEPTION WHEN NO_DATA_FOUND THEN
528 l_category_set_exists :=FALSE;
529 l_category_status :='NOTEXISTS';
530 p_category_set:=NULL;
531 p_structure_id:=NULL;
532 END;
533 l_category_set:= p_category_set;
534 l_structure_id:= p_structure_id;
535
536 /* Create Categories */
537 IF l_category_set_exists=TRUE THEN
538
539 INSERT INTO MTL_CATEGORIES_B(
540 CATEGORY_ID,
541 STRUCTURE_ID,
542 DISABLE_DATE,
543 SUMMARY_FLAG,
544 ENABLED_FLAG,
545 WEB_STATUS,
546 SUPPLIER_ENABLED_FLAG,
547 START_DATE_ACTIVE,
548 END_DATE_ACTIVE,
549 SEGMENT1,
550 CREATED_BY,
551 LAST_UPDATE_DATE,
552 LAST_UPDATED_BY,
553 CREATION_DATE,
557 mtl_categories_s.nextval,
554 LAST_UPDATE_LOGIN
555 )
556 SELECT
558 p_structure_id,
559 fnd.End_date_active,
560 'N',
561 'Y',
562 'N',
563 'N',
564 start_date_active,
565 NULL,
566 fnd.LOOKUP_CODE,
567 fnd_global.user_id,
568 SYSDATE,
569 fnd_global.user_id,
570 SYSDATE,
571 FND_GLOBAL.CONC_LOGIN_ID
572 FROM fnd_lookup_values fnd
573 WHERE lookup_type = l_lookup_type
574 AND language=userenv('lang') -- Bug 6441455
575 AND enabled_flag = 'Y'
576 AND NOT EXISTS
577 (select 'Y' from mtl_categories_b m where m.structure_id = p_structure_id
578 and m.segment1=fnd.lookup_code);
579
580 /* Create same languages existing in the Lookup Type */
581 INSERT INTO MTL_CATEGORIES_TL(
582 CATEGORY_ID,
583 LANGUAGE,
584 SOURCE_LANG,
585 DESCRIPTION,
586 LAST_UPDATE_DATE,
587 LAST_UPDATED_BY,
588 CREATION_DATE,
589 CREATED_BY,
590 LAST_UPDATE_LOGIN)
591 SELECT mtl.category_id,
592 fnd.LANGUAGE,
593 fnd.source_lang,
594 fnd.Meaning,
595 SYSDATE,
596 fnd_global.user_id,
597 SYSDATE,
598 fnd_global.user_id,
599 FND_GLOBAL.CONC_LOGIN_ID
600 FROM fnd_lookup_values fnd, mtl_categories_b mtl
601 WHERE lookup_type = l_lookup_type
602 AND FND.lookup_code = mtl.segment1
603 AND NOT EXISTS
604 (select 'Y' FROM mtl_categories_tl m
605 where m.category_id= mtl.category_id
606 and m.language=fnd.language);
607
608 END IF;
609
610 EXCEPTION WHEN OTHERS THEN
611 l_category_status :='NOTEXISTS';
612
613 END CREATE_MTL_CATEGORIES;
614
615 /*===========================================================================+
616 | Procedure: MTL_SYSTEM_ITEMS |
617 | Description: This Procedure describes data migration for |
618 | Fiscal Classification migration for |
619 | MTL_SYSTEM_ITEMS. |
620 | Migration of Item Associations |
621 | |
622 | |
623 | ARGUMENTS : |
624 | |
625 | |
626 | NOTES |
627 | |
628 | |
629 | |
630 | History |
631 | zmohiudd Created |
632 | |
633 | |
634 +===========================================================================*/
635
636 PROCEDURE MTL_SYSTEM_ITEMS IS
637
638 p_flexfield FND_FLEX_KEY_API.FLEXFIELD_TYPE;
639
640 l_structure_id mtl_category_sets_b.structure_id%TYPE;
641 l_category_status VARCHAR2(200);
642 l_category_set mtl_category_sets_b.Category_set_ID%TYPE;
643
644 l_Inventory_Category_Set mtl_category_sets_vl.Category_set_ID%TYPE;
645 l_Item_id Number;
646 l_Item_organization_id Number;
647 l_record_type zx_Fc_types_b.record_type_code%type;
648
649 l_classification_name fnd_lookup_values.meaning%type;
650
651 l_lookup_code fnd_lookup_values.lookup_code%type;
652 l_meaning fnd_lookup_values.meaning%type;
653 l_language fnd_lookup_values.language%type;
654 l_start_date_active fnd_lookup_values.start_date_active%type;
655 l_end_date_active fnd_lookup_values.end_date_active%type;
656 l_source_lang fnd_lookup_values.source_lang%type;
657 i integer:=0;
658 l_fc_id zx_fc_codes_b.classification_id%type;
659 l_return_status varchar2(200);
660 l_errorcode number;
661 l_msg_count number;
662 l_MSG_DATA varchar2(200);
663
664
665 BEGIN
666
667 arp_util_tax.debug( ' MTL_SYSTEM_ITEMS.. (+) ' );
668
669 -- Bug # 5300607. We need to remove Inventory installed check.
670 -- IF Zx_Migrate_Util.IS_INSTALLED('INV') = 'Y' THEN
671
672 -- If Hungary is installed then
673 IF Is_Country_Installed(7002, 'jehuloc') THEN
674
675 arp_util_tax.debug( 'Initialized the category set value.. ');
676
677 -- Call Create Categories
678 Create_Category_Sets;
679 CREATE_MTL_CATEGORIES('JGZZ_STATISTICAL_CODE','STATISTICAL_CODE',l_category_status,l_category_set,l_structure_id);
680
681 -- Call a common procedure to create FC Types
682 FC_TYPE_INSERT('STATISTICAL_CODE','Statistical Code',l_category_set);
683
684 /* Create Association to items move to zxitemcatmig.sql */
685
686 /* Regime Association to Fiscal Type */
687
688 arp_util_tax.debug( 'Creating the regime association to fiscal type.. ');
689
690 INSERT ALL INTO ZX_FC_TYPES_REG_ASSOC
691 (Tax_regime_code,
692 classification_type_code,
693 effective_FROM,
694 effective_to,
695 record_type_code,
696 created_by,
700 last_update_login,
697 creation_date,
698 last_updated_by,
699 last_update_date,
701 classif_regime_id,
702 object_version_number)
703 VALUES
704 (tax_regime_code,
705 'STATISTICAL_CODE',
706 SYSDATE,
707 NULL,
708 'MIGRATED',
709 fnd_global.user_id,
710 SYSDATE,
711 fnd_global.user_id,
712 SYSDATE,
713 FND_GLOBAL.CONC_LOGIN_ID,
714 zx_fc_types_reg_assoc_s.nextval,
715 1)
716 SELECT unique tax_regime_code
717 FROM ZX_RATES_B rates,
718 AP_TAX_CODES_ALL codes
719 WHERE
720 codes.tax_id = nvl(rates.source_id, rates.tax_rate_id) and
721 codes.global_attribute_category = 'JE.HU.APXTADTC.TAX_ORIGIN' and
722 rates.record_type_code = 'MIGRATED' and
723 not exists
724 (select null from ZX_FC_TYPES_REG_ASSOC
725 where classification_type_code = 'STATISTICAL_CODE' and
726 tax_regime_code = rates.tax_regime_code);
727
728 END IF; -- End of Hungary installed Checking
729
730 -- If Argentina is installed then
731 IF Is_Country_Installed(7004, 'jlarloc') THEN
732
733 --Bug # 3587896
734 /* Create Codes under Intended Use Fiscal Classifications */
735 arp_util_tax.debug( 'Creating the Codes under Intended Use Fiscal Classifications ');
736
737 FC_CODE_FROM_FND_LOOKUP('INTENDED_USE','JLZZ_AP_DESTINATION_CODE','AR',NULL,NULL,NULL,1);
738
739 END IF;
740
741 -- If Hungary is installed then
742 IF Is_Country_Installed(7002, 'jehuloc') THEN
743
744 /* Create Codes under Product Category Statistical Code */
745 arp_util_tax.debug( 'Creating the Statistical Code.. ');
746
747 --select zx_fc_codes_b_s.nextval into l_fc_id from dual;
748
749 FIRST_LEVEL_FC_CODE_INSERT('PRODUCT_CATEGORY','STATISTICAL_CODE','Statistical Code',NULL,l_fc_id);
750
751 FC_CODE_FROM_FND_LOOKUP('PRODUCT_CATEGORY','JGZZ_STATISTICAL_CODE',NULL,l_fc_id,'STATISTICAL_CODE','Statistical Code',2);
752
753 arp_util_tax.debug( ' MTL_SYSTEM_ITEMS end of hungary ');
754
755 END IF;
756
757 -- If Brazil is installed then
758 IF ( Is_Country_Installed(7004, 'jlbrloc') or Is_Country_Installed(7004, 'jlarloc') or
759 Is_Country_Installed(7004, 'jlcoloc') )THEN
760
761 --Bug# 3588145
762 -- Insert the FISCAL CLASSIFICATION CODE for BRAZIL in level one
763 FIRST_LEVEL_FC_CODE_INSERT('PRODUCT_CATEGORY','FISCAL CLASSIFICATION CODE',
764 'Fiscal Classification Code',NULL,l_fc_id);
765
766 -- Insert into second level
767
768 OPEN G_C_GET_TYPES_INFO('PRODUCT_CATEGORY');
769
770 FETCH G_C_GET_TYPES_INFO INTO
771 G_CLASSIFICATION_TYPE_ID,
772 G_CLASSIFICATION_TYPE_CODE,
773 G_CLASSIFICATION_TYPE_NAME,
774 G_CLASSIFICATION_TYP_CATEG_COD,
775 G_DELIMITER;
776
777 CLOSE G_C_GET_TYPES_INFO;
778 /* Removed the reference to obsolete object JL_BR_PO_FISC_CLASSIF_ALL. Bug # 5150296 */
779 INSERT
780 INTO ZX_FC_CODES_B
781 (classification_type_code,
782 classification_id,
783 classification_code,
784 effective_from,
785 effective_to,
786 parent_classification_code,
787 parent_classification_id,
788 country_code,
789 record_type_code,
790 created_by,
791 creation_date,
792 last_updated_by,
793 last_update_date,
794 last_update_login,
795 object_version_number)
796 ( SELECT
797 'PRODUCT_CATEGORY',
798 zx_fc_codes_b_s.nextval,
799 lookups.LOOKUP_CODE fc_code,
800 nvl(START_DATE_ACTIVE, to_date('01/01/1951','DD/MM/YYYY')) effective_from,
801 END_DATE_ACTIVE effective_to,
802 'FISCAL CLASSIFICATION CODE',--parent_classification_code
803 l_fc_id, --parent_classification_id
804 'BR',
805 'MIGRATED',
806 fnd_global.user_id,
807 SYSDATE,
808 fnd_global.user_id,
809 SYSDATE,
810 FND_GLOBAL.CONC_LOGIN_ID,
811 1
812 FROM
813 FND_LOOKUPS lookups
814 WHERE
815 lookups.LOOKUP_TYPE = 'JLZZ_AR_TX_FISCAL_CLASS_CODE'
816 AND NOT EXISTS -- this condition makes sure we dont duplicate data
817 (select NULL from ZX_FC_CODES_B Codes where
818 codes.classification_type_code = 'PRODUCT_CATEGORY'
819 and codes.parent_classification_id = nvl(l_fc_id,codes.parent_classification_id)
820 and codes.classification_code = lookups.lookup_code )
821 );
822
823
824 INSERT ALL INTO ZX_FC_CODES_TL
825 (CLASSIFICATION_ID,
826 CLASSIFICATION_NAME,
827 CREATED_BY,
828 CREATION_DATE,
829 LAST_UPDATED_BY,
830 LAST_UPDATE_DATE,
831 LAST_UPDATE_LOGIN,
832 LANGUAGE,
833 SOURCE_LANG)
834 VALUES (classification_id,
835 CASE WHEN fc_name = UPPER(fc_name)
836 THEN Initcap(fc_name)
837 ELSE
838 fc_name
839 END,
840 fnd_global.user_id,
841 SYSDATE,
842 fnd_global.user_id,
843 SYSDATE,
844 FND_GLOBAL.CONC_LOGIN_ID,
845 LANGUAGE,
846 SOURCE_LANGUAGE)
850 CLASSIFICATION_TYPE_NAME,
847 INTO ZX_FC_CODES_DENORM_B(
848 CLASSIFICATION_TYPE_ID,
849 CLASSIFICATION_TYPE_CODE,
851 CLASSIFICATION_TYPE_CATEG_CODE,
852 CLASSIFICATION_ID,
853 CLASSIFICATION_CODE,
854 CLASSIFICATION_NAME,
855 LANGUAGE,
856 EFFECTIVE_FROM,
857 EFFECTIVE_TO,
858 ENABLED_FLAG,
859 ANCESTOR_ID,
860 ANCESTOR_CODE,
861 ANCESTOR_NAME,
862 CONCAT_CLASSIF_CODE,
863 CONCAT_CLASSIF_NAME,
864 CLASSIFICATION_CODE_LEVEL,
865 COUNTRY_CODE,
866 SEGMENT1,
867 SEGMENT2,
868 SEGMENT3,
869 SEGMENT4,
870 SEGMENT5,
871 SEGMENT6,
872 SEGMENT7,
873 SEGMENT8,
874 SEGMENT9,
875 SEGMENT10,
876 SEGMENT1_NAME,
877 SEGMENT2_NAME,
878 SEGMENT3_NAME,
879 SEGMENT4_NAME,
880 SEGMENT5_NAME,
881 SEGMENT6_NAME,
882 SEGMENT7_NAME,
883 SEGMENT8_NAME,
884 SEGMENT9_NAME,
885 SEGMENT10_NAME,
886 CREATED_BY,
887 CREATION_DATE,
888 LAST_UPDATED_BY,
889 LAST_UPDATE_LOGIN,
890 LAST_UPDATE_DATE,
891 REQUEST_ID,
892 PROGRAM_ID,
893 PROGRAM_APPLICATION_ID,
894 PROGRAM_LOGIN_ID,
895 RECORD_TYPE_CODE)
896 VALUES (
897 G_CLASSIFICATION_TYPE_ID,
898 G_CLASSIFICATION_TYPE_CODE,
899 G_CLASSIFICATION_TYPE_NAME,
900 G_CLASSIFICATION_TYP_CATEG_COD,
901 classification_id,
902 fc_code,
903 fc_name,
904 LANGUAGE,
905 effective_from,
906 effective_to,
907 'Y',
908 l_fc_id,
909 'FISCAL CLASSIFICATION CODE',
910 'Fiscal Classification Code',
911 'FISCAL CLASSIFICATION CODE'||G_DELIMITER||fc_code,
912 'Fiscal Classification Code'||G_DELIMITER||fc_name,
913 2,
914 'BR',
915 'FISCAL CLASSIFICATION CODE',
916 fc_code,
917 Null,
918 Null,
919 Null,
920 Null,
921 Null,
922 Null,
923 Null,
924 Null,
925 'Fiscal Classification Code',
926 fc_name,
927 Null,
928 Null,
929 Null,
930 Null,
931 Null,
932 Null,
933 Null,
934 Null,
935 fnd_global.user_id,
936 SYSDATE,
937 fnd_global.user_id,
938 FND_GLOBAL.CONC_LOGIN_ID,
939 sysdate,
940 FND_GLOBAL.CONC_REQUEST_ID,
941 fnd_global.CONC_PROGRAM_ID,
942 235,
943 FND_GLOBAL.CONC_LOGIN_ID,
944 'MIGRATED')
945
946 SELECT
947 FL.LOOKUP_CODE fc_code,
948 FL.MEANING fc_name,
949 nvl(START_DATE_ACTIVE,to_date('01/01/1951','DD/MM/YYYY')) effective_from,
950 END_DATE_ACTIVE effective_to,
951 FL.LANGUAGE LANGUAGE,
952 FL.SOURCE_LANG SOURCE_LANGUAGE,
953 codes.classification_id
954 FROM
955 ZX_FC_CODES_b Codes,
956 FND_LOOKUP_VALUES FL,
957 FND_LANGUAGES L
958 WHERE
959 Codes.classification_type_code = 'PRODUCT_CATEGORY'
960 AND Codes.parent_classification_id=l_fc_id
961 AND Codes.classification_code = FL.lookup_code
962 AND Codes.RECORD_TYPE_CODE = 'MIGRATED'
963 AND FL.LOOKUP_TYPE = 'JLZZ_AR_TX_FISCAL_CLASS_CODE'
964 AND FL.VIEW_APPLICATION_ID = 0
965 AND FL.SECURITY_GROUP_ID = 0
966 AND FL.language=L.language_code(+)
967 AND L.INSTALLED_FLAG in ('I', 'B')
968 AND NOT EXISTS -- this condition makes sure we dont duplicate data
969 (select NULL
970 from ZX_FC_CODES_DENORM_B codes
971 where codes.classification_type_code = G_CLASSIFICATION_TYPE_CODE
972 and codes.classification_code = fl.lookup_code
973 and codes.ancestor_id = nvl(l_fc_id,codes.ancestor_id)
974 and codes.language = l.language_code);
975
976 arp_util_tax.debug( ' Calling the bulk api to create categories..(+)');
977
978 Create_Category_Set ('FISCAL_CLASSIFICATION',
979 'Fiscal Classification',
980 'FISCAL_CLASSIFICATION',
981 'Fiscal Classification');
982
983 CREATE_MTL_CATEGORIES('JLZZ_AR_TX_FISCAL_CLASS_CODE', 'FISCAL_CLASSIFICATION',
984 l_category_status,l_category_set, l_structure_id);
985
986 -- Call a common procedure to create FC Types
987 FC_TYPE_INSERT('FISCAL_CLASSIFICATION','Fiscal Classification Code',l_category_set);
988
989 -- Create Regime Association for 'FISCAL CLASSIFICATION CODE'
990 arp_util_tax.debug( 'Creating the regime association to fiscal type.. ');
991
992 INSERT ALL INTO
993 ZX_FC_TYPES_REG_ASSOC
994 (Tax_regime_code,
995 classification_type_code,
996 effective_FROM,
997 effective_to,
998 record_type_code,
999 created_by,
1000 creation_date,
1001 last_updated_by,
1002 last_update_date,
1003 last_update_login,
1004 classif_regime_id,
1005 object_version_number)
1006
1007 VALUES
1008 (TAX_REGIME_CODE,
1009 'FISCAL_CLASSIFICATION',
1010 SYSDATE,
1011 NULL,
1012 'MIGRATED',
1013 fnd_global.user_id,
1014 SYSDATE,
1015 fnd_global.user_id,
1016 SYSDATE,
1017 FND_GLOBAL.CONC_LOGIN_ID,
1018 zx_fc_types_reg_assoc_s.nextval,
1022 FROM ZX_RATES_B rates,
1019 1)
1020 SELECT
1021 unique tax_regime_code
1023 AP_TAX_CODES_ALL codes
1024 WHERE
1025 codes.tax_id = nvl(rates.source_id, rates.tax_rate_id) and
1026 codes.global_attribute_category = 'JL.BR.INVIDITM.AR.Fiscal' and
1027 rates.record_type_code = 'MIGRATED' and
1028 not exists
1029 (select null from ZX_FC_TYPES_REG_ASSOC
1030 where classification_type_code = 'FISCAL_CLASSIFICATION' and
1031 tax_regime_code = rates.tax_regime_code);
1032
1033
1034 arp_util_tax.debug( ' Calling the bulk api to create categories..(-)');
1035
1036 END IF; -- End of Brazil checking
1037
1038 arp_util_tax.debug( 'MTL_SYSTEM_ITEMS (-) ');
1039
1040 END MTL_SYSTEM_ITEMS ;
1041
1042 /*===========================================================================+
1043 | Procedure: FC_ENTITIES |
1044 | Description: This Procedure describes data migration for |
1045 | Fiscal Classification migration for source of |
1046 | GDF's on AP and AR. |
1047 | |
1048 | |
1049 | |
1050 | ARGUMENTS : |
1051 | |
1052 | |
1053 | NOTES |
1054 | |
1055 | |
1056 | |
1057 | History |
1058 | zmohiudd Created |
1059 | Venkat 14th May 04 Creation of Reporting Type and Reporting |
1060 | Usage for FISCAL PRINTER and CAI NUMBER |
1061 | Bug # 3587896 |
1062 | Venkat 18th Aug 04 Added code for o2c setup migration |
1063 | Bug # 3811144 (handling of translation |
1064 | record issue in _TL table) |
1065 +===========================================================================*/
1066
1067 PROCEDURE FC_ENTITIES IS
1068
1069 CURSOR c_wine_category IS
1070 SELECT Category_set_ID
1071 FROM mtl_category_sets
1072 WHERE Category_Set_Name ='WINE_CIGARRETE_CATEGORY';
1073
1074 l_LANGUAGE zx_fc_types_tl.language%type;
1075 l_fc_id zx_fc_codes_b.classification_id%type;
1076 p_category_set mtl_category_sets_vl.Category_set_ID%TYPE;
1077 p_flexfield FND_FLEX_KEY_API.FLEXFIELD_TYPE;
1078 p_structure_id FND_FLEX_KEY_API.STRUCTURE_TYPE;
1079 v_structure_id FND_FLEX_KEY_API.STRUCTURE_TYPE;
1080 v_classification_code zx_fc_codes_b.classification_code%type;
1081 v_classification_name zx_fc_codes_tl.classification_name%type;
1082 v_effective_from date;
1083 v_effective_to date;
1084 v_language zx_fc_codes_tl.language%type;
1085 v_RECORD_TYPE zx_Fc_codes_b.RECORD_TYPE_CODE%type;
1086
1087 BEGIN
1088
1089 arp_util_tax.debug( ' FC_ENTITIES .. (+) ' );
1090
1091 -- If Brazil is installed then
1092 IF Is_Country_Installed(7004, 'jlbrloc') THEN
1093
1094 /* Create Codes under User Defined Fiscal Classifications */
1095
1096 --Bug # 3588145
1097 arp_util_tax.debug( 'Creating the Codes under User Defined Fiscal Classifications ');
1098
1099 OPEN G_C_GET_TYPES_INFO('USER_DEFINED');
1100
1101 FETCH G_C_GET_TYPES_INFO INTO
1102 G_CLASSIFICATION_TYPE_ID,
1103 G_CLASSIFICATION_TYPE_CODE,
1104 G_CLASSIFICATION_TYPE_NAME,
1105 G_CLASSIFICATION_TYP_CATEG_COD,
1106 G_DELIMITER;
1107
1108 CLOSE G_C_GET_TYPES_INFO;
1109
1110 INSERT
1111 INTO ZX_FC_CODES_B
1112 (classification_type_code,
1113 classification_id,
1114 classification_code,
1115 effective_from,
1116 effective_to,
1117 parent_classification_code,
1118 parent_classification_id,
1119 country_code,
1120 record_type_code,
1121 created_by,
1122 creation_date,
1123 last_updated_by,
1124 last_update_date,
1125 last_update_login,
1126 object_version_number)
1127 (SELECT 'USER_DEFINED',
1128 zx_fc_codes_b_s.nextval,
1129 cfo_code,
1130 creation_date,
1131 null,
1132 null,----parent_classification_code
1133 null,----parent_classification_id
1134 'BR',
1135 'MIGRATED',
1136 fnd_global.user_id,
1137 SYSDATE,
1138 fnd_global.user_id,
1139 SYSDATE,
1140 FND_GLOBAL.CONC_LOGIN_ID,
1141 1
1142 FROM JL_BR_AP_OPERATIONS JLBR
1143 WHERE NOT EXISTS
1144 -- this condition makes sure we dont duplicate data
1145 (select NULL from ZX_FC_CODES_B Codes where
1146 codes.classification_type_code = 'USER_DEFINED'
1147 and codes.parent_classification_id is null
1148 and codes.classification_code = jlbr.cfo_code)
1149 );
1150
1151
1152 INSERT ALL
1153 INTO ZX_FC_CODES_TL
1154 (CLASSIFICATION_ID,
1158 LAST_UPDATED_BY,
1155 CLASSIFICATION_NAME,
1156 CREATED_BY,
1157 CREATION_DATE,
1159 LAST_UPDATE_DATE,
1160 LAST_UPDATE_LOGIN,
1161 LANGUAGE,
1162 SOURCE_LANG)
1163 VALUES (classification_id,
1164 CASE WHEN Meaning = UPPER(Meaning)
1165 THEN Initcap(Meaning)
1166 ELSE
1167 Meaning
1168 END,
1169 fnd_global.user_id,
1170 SYSDATE,
1171 fnd_global.user_id,
1172 SYSDATE,
1173 FND_GLOBAL.CONC_LOGIN_ID,
1174 lang_code,
1175 userenv('LANG'))
1176 INTO ZX_FC_CODES_DENORM_B(
1177 CLASSIFICATION_TYPE_ID,
1178 CLASSIFICATION_TYPE_CODE,
1179 CLASSIFICATION_TYPE_NAME,
1180 CLASSIFICATION_TYPE_CATEG_CODE,
1181 CLASSIFICATION_ID,
1182 CLASSIFICATION_CODE,
1183 CLASSIFICATION_NAME,
1184 LANGUAGE,
1185 EFFECTIVE_FROM,
1186 EFFECTIVE_TO,
1187 ENABLED_FLAG,
1188 ANCESTOR_ID,
1189 ANCESTOR_CODE,
1190 ANCESTOR_NAME,
1191 CONCAT_CLASSIF_CODE,
1192 CONCAT_CLASSIF_NAME,
1193 CLASSIFICATION_CODE_LEVEL,
1194 COUNTRY_CODE,
1195 SEGMENT1,
1196 SEGMENT2,
1197 SEGMENT3,
1198 SEGMENT4,
1199 SEGMENT5,
1200 SEGMENT6,
1201 SEGMENT7,
1202 SEGMENT8,
1203 SEGMENT9,
1204 SEGMENT10,
1205 SEGMENT1_NAME,
1206 SEGMENT2_NAME,
1207 SEGMENT3_NAME,
1208 SEGMENT4_NAME,
1209 SEGMENT5_NAME,
1210 SEGMENT6_NAME,
1211 SEGMENT7_NAME,
1212 SEGMENT8_NAME,
1213 SEGMENT9_NAME,
1214 SEGMENT10_NAME,
1215 CREATED_BY,
1216 CREATION_DATE,
1217 LAST_UPDATED_BY,
1218 LAST_UPDATE_LOGIN,
1219 LAST_UPDATE_DATE,
1220 REQUEST_ID,
1221 PROGRAM_ID,
1222 PROGRAM_APPLICATION_ID,
1223 PROGRAM_LOGIN_ID,
1224 RECORD_TYPE_CODE)
1225 VALUES (
1226 G_CLASSIFICATION_TYPE_ID,
1227 G_CLASSIFICATION_TYPE_CODE,
1228 G_CLASSIFICATION_TYPE_NAME,
1229 G_CLASSIFICATION_TYP_CATEG_COD,
1230 classification_id,
1231 Code,
1232 Meaning,
1233 lang_code,
1234 creation_date,
1235 null,
1236 'Y',
1237 null,
1238 null,
1239 null,
1240 Code,
1241 Meaning,
1242 1,
1243 'BR',
1244 Code,
1245 Null,
1246 Null,
1247 Null,
1248 Null,
1249 Null,
1250 Null,
1251 Null,
1252 Null,
1253 Null,
1254 Meaning,
1255 Null,
1256 Null,
1257 Null,
1258 Null,
1259 Null,
1260 Null,
1261 Null,
1262 Null,
1263 Null,
1264 fnd_global.user_id,
1265 SYSDATE,
1266 fnd_global.user_id,
1267 FND_GLOBAL.CONC_LOGIN_ID,
1268 sysdate,
1269 FND_GLOBAL.CONC_REQUEST_ID,
1270 fnd_global.CONC_PROGRAM_ID,
1271 235,
1272 FND_GLOBAL.CONC_LOGIN_ID,
1273 'MIGRATED')
1274 SELECT cfo_code Code,
1275 cfo_description Meaning,
1276 JLBR.creation_date ,
1277 l.language_code lang_code,
1278 codes.classification_id
1279 FROM ZX_FC_CODES_B Codes,
1280 JL_BR_AP_OPERATIONS JLBR,
1281 FND_LANGUAGES L
1282 WHERE
1283 Codes.classification_type_code = G_CLASSIFICATION_TYPE_CODE
1284 and Codes.parent_classification_id is null
1285 AND Codes.classification_code=JLBR.cfo_code
1286 AND Codes.RECORD_TYPE_CODE = 'MIGRATED'
1287 AND L.INSTALLED_FLAG in ('I', 'B')
1288 AND NOT EXISTS -- this condition makes sure we dont duplicate data
1289 (select NULL from ZX_FC_CODES_DENORM_B codes
1290 where codes.classification_type_code = G_CLASSIFICATION_TYPE_CODE
1291 and codes.classification_code = JLBR.cfo_code
1292 and codes.ancestor_id is null
1293 and codes.language = l.language_code);
1294
1295 arp_util_tax.debug( 'Create OPERATION FISCAL CODE under the Transaction Business Category Type-Level 2: BR');
1296
1297 -- Insert the GDF under TBC FC TYPE - 2nd level
1298 GDF_PROMPT_INSERT('OPERATION FISCAL CODE', 'Operation Fiscal Code', 'BR', 'PURCHASE_TRANSACTION');
1299
1300 OPEN G_C_GET_TYPES_INFO('TRX_BUSINESS_CATEGORY');
1301
1302 FETCH G_C_GET_TYPES_INFO INTO
1303 G_CLASSIFICATION_TYPE_ID,
1304 G_CLASSIFICATION_TYPE_CODE,
1305 G_CLASSIFICATION_TYPE_NAME,
1306 G_CLASSIFICATION_TYP_CATEG_COD,
1307 G_DELIMITER;
1308
1309 CLOSE G_C_GET_TYPES_INFO;
1310
1311
1312 --Create a Code under the Transaction Business Category Type, 3rd Level
1313
1314 arp_util_tax.debug( 'Create Code under the Transaction Business Category Type-Level 3: BR');
1315
1316 INSERT
1317 INTO ZX_FC_CODES_B (
1318 classification_type_code,
1319 classification_id,
1320 classification_code,
1321 effective_from,
1322 effective_to,
1323 parent_classification_code,
1324 parent_classification_id,
1325 Country_code,
1326 record_type_code,
1327 created_by,
1328 creation_date,
1329 last_updated_by,
1330 last_update_date,
1331 last_update_login,
1332 object_version_number
1333 )
1334 SELECT
1338 ap_op.creation_date,
1335 'TRX_BUSINESS_CATEGORY',
1336 zx_fc_codes_b_s.nextval,
1337 cfo_code ,
1339 null,
1340 fc.classification_code,--parent_classification_code
1341 fc.classification_id, --parent_classification_id
1342 'BR',
1343 'MIGRATED',
1344 fnd_global.user_id,
1345 SYSDATE,
1346 fnd_global.user_id,
1347 SYSDATE,
1348 FND_GLOBAL.CONC_LOGIN_ID,
1349 1
1350 FROM
1351 JL_BR_AP_OPERATIONS ap_op,
1352 ZX_FC_CODES_DENORM_B fc,
1353 ZX_EVENT_CLASSES_VL event
1354 WHERE
1355 fc.classification_type_code = 'TRX_BUSINESS_CATEGORY'
1356 and fc.classification_code='OPERATION FISCAL CODE'
1357 and fc.ancestor_code = event.tax_event_class_code
1358 and fc.language = userenv('LANG')
1359 and fc.classification_code_level = 2
1360 and event.tax_event_class_code = 'PURCHASE_TRANSACTION'
1361 and NOT EXISTS -- this condition makes sure we dont duplicate data
1362 ( select NULL from ZX_FC_CODES_B Codes where
1363 codes.classification_type_code = 'TRX_BUSINESS_CATEGORY'
1364 and codes.parent_classification_id =
1365 nvl(fc.classification_id,codes.parent_classification_id)
1366 and codes.classification_code = ap_op.cfo_code );
1367
1368 INSERT ALL
1369 INTO ZX_FC_CODES_TL
1370 (CLASSIFICATION_ID,
1371 CLASSIFICATION_NAME,
1372 CREATED_BY,
1373 CREATION_DATE,
1374 LAST_UPDATED_BY,
1375 LAST_UPDATE_DATE,
1376 LAST_UPDATE_LOGIN,
1377 LANGUAGE,
1378 SOURCE_LANG)
1379 VALUES (classification_id,
1380 CASE WHEN Meaning = UPPER(Meaning)
1381 THEN Initcap(Meaning)
1382 ELSE
1383 Meaning
1384 END,
1385 fnd_global.user_id,
1386 SYSDATE,
1387 fnd_global.user_id,
1388 SYSDATE,
1389 FND_GLOBAL.CONC_LOGIN_ID,
1390 lang_code,
1391 userenv('LANG'))
1392 INTO ZX_FC_CODES_DENORM_B(
1393 CLASSIFICATION_TYPE_ID,
1394 CLASSIFICATION_TYPE_CODE,
1395 CLASSIFICATION_TYPE_NAME,
1396 CLASSIFICATION_TYPE_CATEG_CODE,
1397 CLASSIFICATION_ID,
1398 CLASSIFICATION_CODE,
1399 CLASSIFICATION_NAME,
1400 LANGUAGE,
1401 EFFECTIVE_FROM,
1402 EFFECTIVE_TO,
1403 ENABLED_FLAG,
1404 ANCESTOR_ID,
1405 ANCESTOR_CODE,
1406 ANCESTOR_NAME,
1407 CONCAT_CLASSIF_CODE,
1408 CONCAT_CLASSIF_NAME,
1409 CLASSIFICATION_CODE_LEVEL,
1410 COUNTRY_CODE,
1411 SEGMENT1,
1412 SEGMENT2,
1413 SEGMENT3,
1414 SEGMENT4,
1415 SEGMENT5,
1416 SEGMENT6,
1417 SEGMENT7,
1418 SEGMENT8,
1419 SEGMENT9,
1420 SEGMENT10,
1421 SEGMENT1_NAME,
1422 SEGMENT2_NAME,
1423 SEGMENT3_NAME,
1424 SEGMENT4_NAME,
1425 SEGMENT5_NAME,
1426 SEGMENT6_NAME,
1427 SEGMENT7_NAME,
1428 SEGMENT8_NAME,
1429 SEGMENT9_NAME,
1430 SEGMENT10_NAME,
1431 CREATED_BY,
1432 CREATION_DATE,
1433 LAST_UPDATED_BY,
1434 LAST_UPDATE_LOGIN,
1435 LAST_UPDATE_DATE,
1436 REQUEST_ID,
1437 PROGRAM_ID,
1438 PROGRAM_APPLICATION_ID,
1439 PROGRAM_LOGIN_ID,
1440 RECORD_TYPE_CODE)
1441 VALUES (
1442 G_CLASSIFICATION_TYPE_ID,
1443 G_CLASSIFICATION_TYPE_CODE,
1444 G_CLASSIFICATION_TYPE_NAME,
1445 G_CLASSIFICATION_TYP_CATEG_COD,
1446 classification_id,
1447 Code,
1448 Meaning,
1449 lang_code,
1450 creation_date,
1451 null,
1452 'Y',
1453 parent_fc_id,
1454 parent_fc_code,
1455 parent_fc_name,
1456 tax_event_class_code ||G_DELIMITER || parent_fc_code || G_DELIMITER || Code,
1457 Name || G_DELIMITER || parent_fc_name || G_DELIMITER || Meaning,
1458 3,
1459 'BR',
1460 tax_event_class_code,
1461 parent_fc_code,
1462 Code,
1463 Null,
1464 Null,
1465 Null,
1466 Null,
1467 Null,
1468 Null,
1469 Null,
1470 Name,
1471 parent_fc_name,
1472 Meaning,
1473 Null,
1474 Null,
1475 Null,
1476 Null,
1477 Null,
1478 Null,
1479 Null,
1480 fnd_global.user_id,
1481 SYSDATE,
1482 fnd_global.user_id,
1483 FND_GLOBAL.CONC_LOGIN_ID,
1484 sysdate,
1485 FND_GLOBAL.CONC_REQUEST_ID,
1486 fnd_global.CONC_PROGRAM_ID,
1487 235,
1488 FND_GLOBAL.CONC_LOGIN_ID,
1489 'MIGRATED')
1490
1491 SELECT cfo_code Code,
1492 cfo_description Meaning,
1493 tax_event_class_code,
1494 event.tax_event_class_name name,
1495 fc.classification_id as parent_fc_id,
1496 fc.classification_code as parent_fc_code,
1497 fc.classification_name as parent_fc_name,
1498 ap_op.creation_date,
1499 codes.classification_id,
1500 l.language_code lang_code
1501 FROM
1502 ZX_FC_CODES_DENORM_B fc,
1503 ZX_FC_CODES_B Codes,
1504 JL_BR_AP_OPERATIONS ap_op,
1505 FND_LANGUAGES L,
1506 ZX_EVENT_CLASSES_VL event
1507 WHERE
1508 fc.classification_type_code = 'TRX_BUSINESS_CATEGORY'
1509 and fc.classification_code='OPERATION FISCAL CODE'
1510 and fc.language = userenv('LANG')
1511 and fc.ancestor_code = event.tax_event_class_code
1512 and event.tax_event_class_code = 'PURCHASE_TRANSACTION'
1516 and Codes.parent_classification_id = fc.classification_id
1513 and fc.classification_code_level = 2
1514
1515 and Codes.classification_type_code = G_CLASSIFICATION_TYPE_CODE
1517 and Codes.classification_code = ap_op.cfo_code
1518 and Codes.RECORD_TYPE_CODE = 'MIGRATED'
1519 and L.INSTALLED_FLAG in ('I', 'B')
1520
1521 AND NOT EXISTS -- this condition makes sure we dont duplicate data
1522 (select NULL from ZX_FC_CODES_DENORM_B codes where
1523 codes.classification_type_code = G_CLASSIFICATION_TYPE_CODE
1524 and codes.classification_code = ap_op.cfo_code
1525 and codes.ancestor_id = nvl(fc.classification_id,codes.ancestor_id)
1526 and codes.language = l.language_code);
1527
1528
1529 END IF; -- End of Brazil checking
1530
1531 -- If Taiwan is installed then
1532 IF Is_Country_Installed(7000, 'jatwloc') THEN
1533
1534
1535 -- Deductible Type Extract - Under Level 2
1536
1537 arp_util_tax.debug( 'Create the Deductible type extract for country : TAIWAN ');
1538 FC_CODE_GDF_INSERT('DEDUCTIBLE TYPE','Deductible Type','TW','JATW_DEDUCTIBLE_TYPE','PURCHASE_TRANSACTION','MIGRATED');
1539
1540 -- Document Subtype GUI Extract
1541
1542 arp_util_tax.debug( 'Create the Document Subtype GUI Extract for country : TAIWAN ');
1543
1544
1545 /* Create rows for Parent Records */
1546
1547 OPEN G_C_GET_TYPES_INFO('DOCUMENT_SUBTYPE');
1548
1549 FETCH G_C_GET_TYPES_INFO INTO
1550 G_CLASSIFICATION_TYPE_ID,
1551 G_CLASSIFICATION_TYPE_CODE,
1552 G_CLASSIFICATION_TYPE_NAME,
1553 G_CLASSIFICATION_TYP_CATEG_COD,
1554 G_DELIMITER;
1555
1556 CLOSE G_C_GET_TYPES_INFO;
1557
1558 FIRST_LEVEL_FC_CODE_INSERT('DOCUMENT_SUBTYPE','GUI TYPE','Government Uniform Invoice Type','TW',l_fc_id);
1559
1560
1561 /* Create Codes on level 2*/
1562
1563 arp_util_tax.debug( 'Create Codes on level 2 for country : TAIWAN ');
1564
1565 INSERT
1566 INTO ZX_FC_CODES_B (
1567 classification_type_code,
1568 classification_id,
1569 classification_code,
1570 effective_from,
1571 effective_to,
1572 parent_classification_code,
1573 parent_classification_id,
1574 Country_code,
1575 record_type_code,
1576 created_by,
1577 creation_date,
1578 last_updated_by,
1579 last_update_date,
1580 last_update_login,
1581 object_version_number)
1582 SELECT
1583 'DOCUMENT_SUBTYPE',
1584 zx_fc_codes_b_s.nextval,
1585 lookup_code, --classification_code
1586 nvl(start_date_active,to_date('01/01/1951','DD/MM/YYYY')), --effective_from
1587 end_date_active, --effective_to
1588 'GUI TYPE', --parent_classification_code
1589 l_fc_id, --parent_classification_id
1590 'TW',
1591 'MIGRATED',
1592 fnd_global.user_id,
1593 SYSDATE,
1594 fnd_global.user_id,
1595 SYSDATE,
1596 FND_GLOBAL.CONC_LOGIN_ID,
1597 1
1598
1599 FROM FND_LOOKUP_VALUES lookups
1600 WHERE
1601 lookups.lookup_type='JATW_GUI_TYPE'
1602 AND LANGUAGE = userenv('LANG')
1603 AND NOT EXISTS -- this condition makes sure we dont duplicate data
1604 (select NULL from ZX_FC_CODES_B Codes where
1605 codes.classification_type_code = 'DOCUMENT_SUBTYPE'
1606 and codes.parent_classification_id =
1607 nvl(l_fc_id,codes.parent_classification_id)
1608 and codes.classification_code = lookups.lookup_code
1609 );
1610
1611 INSERT ALL INTO
1612 ZX_FC_CODES_TL(
1613 CLASSIFICATION_ID,
1614 CLASSIFICATION_NAME,
1615 CREATED_BY,
1616 CREATION_DATE,
1617 LAST_UPDATED_BY,
1618 LAST_UPDATE_DATE,
1619 LAST_UPDATE_LOGIN,
1620 LANGUAGE,
1621 SOURCE_LANG)
1622 VALUES
1623 (classification_id,
1624 CASE WHEN Meaning = UPPER(Meaning)
1625 THEN Initcap(Meaning)
1626 ELSE
1627 Meaning
1628 END,
1629 fnd_global.user_id,
1630 SYSDATE,
1631 fnd_global.user_id,
1632 SYSDATE,
1633 FND_GLOBAL.CONC_LOGIN_ID,
1634 language,
1635 source_lang)
1636
1637 INTO ZX_FC_CODES_DENORM_B
1638 (CLASSIFICATION_TYPE_ID,
1639 CLASSIFICATION_TYPE_CODE,
1640 CLASSIFICATION_TYPE_NAME,
1641 CLASSIFICATION_TYPE_CATEG_CODE,
1642 CLASSIFICATION_ID,
1643 CLASSIFICATION_CODE,
1644 CLASSIFICATION_NAME,
1645 LANGUAGE,
1646 EFFECTIVE_FROM,
1647 EFFECTIVE_TO,
1648 ENABLED_FLAG,
1649 ANCESTOR_ID,
1650 ANCESTOR_CODE,
1651 ANCESTOR_NAME,
1652 CONCAT_CLASSIF_CODE,
1653 CONCAT_CLASSIF_NAME,
1654 CLASSIFICATION_CODE_LEVEL,
1655 COUNTRY_CODE,
1656 SEGMENT1,
1657 SEGMENT2,
1658 SEGMENT3,
1659 SEGMENT4,
1660 SEGMENT5,
1661 SEGMENT6,
1662 SEGMENT7,
1663 SEGMENT8,
1664 SEGMENT9,
1665 SEGMENT10,
1666 SEGMENT1_NAME,
1667 SEGMENT2_NAME,
1668 SEGMENT3_NAME,
1669 SEGMENT4_NAME,
1670 SEGMENT5_NAME,
1671 SEGMENT6_NAME,
1672 SEGMENT7_NAME,
1673 SEGMENT8_NAME,
1674 SEGMENT9_NAME,
1675 SEGMENT10_NAME,
1676 CREATED_BY,
1677 CREATION_DATE,
1678 LAST_UPDATED_BY,
1679 LAST_UPDATE_LOGIN,
1683 PROGRAM_APPLICATION_ID,
1680 LAST_UPDATE_DATE,
1681 REQUEST_ID,
1682 PROGRAM_ID,
1684 PROGRAM_LOGIN_ID,
1685 RECORD_TYPE_CODE)
1686 VALUES (
1687 G_CLASSIFICATION_TYPE_ID,
1688 G_CLASSIFICATION_TYPE_CODE,
1689 G_CLASSIFICATION_TYPE_NAME,
1690 G_CLASSIFICATION_TYP_CATEG_COD,
1691 classification_id,
1692 lookup_code,
1693 Meaning,
1694 language,
1695 start_date_active,
1696 end_date_active,
1697 enabled_flag,
1698 l_fc_id,
1699 'GUI TYPE',
1700 'Government Uniform Invoice Type',
1701 'GUI TYPE' || G_DELIMITER || lookup_code,
1702 'Government Uniform Invoice Type' || G_DELIMITER || Meaning,
1703 2,
1704 'TW',
1705 'GUI TYPE',
1706 lookup_code,
1707 Null,
1708 Null,
1709 Null,
1710 Null,
1711 Null,
1712 Null,
1713 Null,
1714 Null,
1715 'Government Uniform Invoice Type',
1716 Meaning,
1717 Null,
1718 Null,
1719 Null,
1720 Null,
1721 Null,
1722 Null,
1723 Null,
1724 Null,
1725 fnd_global.user_id,
1726 SYSDATE,
1727 fnd_global.user_id,
1728 FND_GLOBAL.CONC_LOGIN_ID,
1729 sysdate,
1730 FND_GLOBAL.CONC_REQUEST_ID,
1731 fnd_global.CONC_PROGRAM_ID,
1732 235,
1733 FND_GLOBAL.CONC_LOGIN_ID,
1734 'MIGRATED')
1735 SELECT
1736 lookup_code,
1737 meaning,
1738 nvl(start_date_active,to_date('01/01/1951','DD/MM/YYYY')) start_date_active,
1739 end_date_active,
1740 source_lang,
1741 language,
1742 lv.enabled_flag,
1743 classification_id
1744 FROM
1745 ZX_FC_CODES_B Codes,
1746 FND_LOOKUP_VALUES LV,
1747 FND_LANGUAGES L
1748 WHERE
1749 Codes.classification_type_code = G_CLASSIFICATION_TYPE_CODE
1750 AND Codes.parent_classification_id = l_fc_id
1751 AND Codes.classification_code = LV.lookup_code
1752 AND Codes.RECORD_TYPE_CODE IN ('MIGRATED','SEEDED')
1753 AND LV.VIEW_APPLICATION_ID = 7000
1754 AND LV.SECURITY_GROUP_ID = 0
1755 AND LV.lookup_type='JATW_GUI_TYPE'
1756 AND LV.language=L.language_code(+)
1757 AND L.INSTALLED_FLAG in ('I', 'B')
1758 AND NOT EXISTS -- this condition makes sure we dont duplicate data
1759 (select NULL from ZX_FC_CODES_DENORM_B codes where
1760 codes.classification_type_code = G_CLASSIFICATION_TYPE_CODE
1761 and codes.classification_code = lv.lookup_code
1762 and codes.ancestor_id = nvl(l_fc_id,codes.ancestor_id)
1763 and codes.language = l.language_code);
1764
1765 /*
1766 Wine/ Cigarette Extract
1767 */
1768
1769 arp_util_tax.debug( 'Create Wine/ Cigarette Extract for country : TAIWAN ');
1770
1771 /* Get the Seeded Item Category Set Value */
1772 Create_Category_Set ('WINE_CIGARRETE_CATEGORY',
1773 'Wine Cigarrete',
1774 'WINE_CIGARRETE_CATEGORY',
1775 'Wine Cigarrete');
1776 p_category_set := Null;
1777
1778 OPEN C_WINE_CATEGORY;
1779
1780 fetch C_WINE_CATEGORY
1781 INTO p_category_set;
1782
1783 IF p_category_set is not null then
1784
1785 -- Call a common procedure to create FC Types
1786 FC_TYPE_INSERT('WINE CIGARETTE','Wine Cigarette',p_category_set);
1787
1788 END IF;
1789
1790 close C_WINE_CATEGORY;
1791
1792 SELECT
1793 'WINE CIGARETTE',
1794 'Wine Cigarette',
1795 sysdate,
1796 Null,
1797 'US',
1798 'MIGRATED'
1799 INTO
1800 V_classification_code,
1801 V_classification_name,
1802 V_effective_from,
1803 V_effective_to,
1804 V_language,
1805 V_RECORD_TYPE
1806 FROM DUAl;
1807
1808 FIRST_LEVEL_FC_CODE_INSERT('PRODUCT_CATEGORY','WINE CIGARETTE','Wine Cigarette','TW',l_fc_id);
1809
1810
1811 END IF; -- End of Taiwan checking
1812
1813 arp_util_tax.debug( ' FC_ENTITIES ...(-) ' );
1814
1815 END FC_ENTITIES;
1816
1817 /*===========================================================================+
1818 | Procedure: COUNTRY_DEFAULT |
1819 | Description: This Procedure describes data migration for |
1820 | Fiscal Classification migration for |
1821 | COUNTRY_DEFAULT |
1822 | |
1823 | |
1824 | |
1825 | ARGUMENTS : |
1826 | |
1827 | |
1828 | NOTES |
1829 | |
1830 | |
1831 | |
1832 | History |
1833 | zmohiudd Created |
1834 | |
1835 | |
1836 +===========================================================================*/
1837
1838
1839 PROCEDURE COUNTRY_DEFAULT IS
1840
1841 BEGIN
1842
1846
1843 arp_util_tax.debug( ' COUNTRY_DEFAULT .. (+) ' );
1844
1845 If Zx_Migrate_Util.IS_INSTALLED('INV') = 'Y' then
1847 -- If Hungary is installed then
1848 IF Is_Country_Installed(7002, 'jehuloc') THEN
1849
1850 arp_util_tax.debug( ' Creating data for Hungary..' );
1851
1852 INSERT INTO ZX_FC_COUNTRY_DEFAULTS (
1853 COUNTRY_CODE,
1854 PRIMARY_INVENTORY_CATEGORY_SET,
1855 INTENDED_USE_DEFAULT,
1856 PRODUCT_CATEG_DEFAULT,
1857 RECORD_TYPE_CODE,
1858 COUNTRY_DEFAULTS_ID,
1859 created_by,
1860 creation_date,
1861 last_updated_by,
1862 last_update_date,
1863 last_update_login,
1864 object_version_number)
1865 SELECT
1866 'HU',
1867 OWNER_ID_NUM,
1868 Null,
1869 Null,
1870 'MIGRATED',
1871 zx_fc_country_defaults_s.nextval,
1872 fnd_global.user_id,
1873 SYSDATE,
1874 fnd_global.user_id,
1875 SYSDATE,
1876 FND_GLOBAL.CONC_LOGIN_ID,
1877 1
1878 FROM ZX_FC_TYPES_B
1879 WHERE
1880 classification_type_code = 'STATISTICAL_CODE'
1881 and classification_type_categ_code ='PRODUCT_FISCAL_CLASS'
1882 AND NOT EXISTS
1883 (SELECT 1 FROM ZX_FC_COUNTRY_DEFAULTS WHERE COUNTRY_CODE = 'HU');
1884
1885 END IF;
1886
1887 -- If Poland is installed then
1888 IF Is_Country_Installed(7002, 'jeplloc') THEN
1889
1890 arp_util_tax.debug( ' Creating data for Poland..' );
1891
1892 INSERT INTO ZX_FC_COUNTRY_DEFAULTS (
1893 COUNTRY_CODE,
1894 PRIMARY_INVENTORY_CATEGORY_SET,
1895 INTENDED_USE_DEFAULT,
1896 PRODUCT_CATEG_DEFAULT,
1897 RECORD_TYPE_CODE,
1898 COUNTRY_DEFAULTS_ID,
1899 created_by,
1900 creation_date,
1901 last_updated_by,
1902 last_update_date,
1903 last_update_login,
1904 object_version_number)
1905 SELECT
1906 'PL',
1907 OWNER_ID_NUM,
1908 Null,
1909 Null,
1910 'MIGRATED',
1911 zx_fc_country_defaults_s.nextval,
1912 fnd_global.user_id,
1913 SYSDATE,
1914 fnd_global.user_id,
1915 SYSDATE,
1916 FND_GLOBAL.CONC_LOGIN_ID,
1917 1
1918 FROM ZX_FC_TYPES_B
1919 WHERE
1920 classification_type_code = 'STATISTICAL_CODE'
1921 and classification_type_categ_code ='PRODUCT_FISCAL_CLASS'
1922 AND NOT EXISTS
1923 (SELECT 1 FROM ZX_FC_COUNTRY_DEFAULTS WHERE COUNTRY_CODE = 'PL');
1924
1925
1926 END IF; -- End of Poland checking
1927
1928 IF Is_Country_Installed(7004, 'jlarloc') THEN
1929
1930 arp_util_tax.debug( ' Creating data for Argentina..' );
1931
1932 INSERT INTO ZX_FC_COUNTRY_DEFAULTS (
1933 COUNTRY_CODE,
1934 PRIMARY_INVENTORY_CATEGORY_SET,
1935 INTENDED_USE_DEFAULT,
1936 PRODUCT_CATEG_DEFAULT,
1937 RECORD_TYPE_CODE,
1938 COUNTRY_DEFAULTS_ID,
1939 created_by,
1940 creation_date,
1941 last_updated_by,
1942 last_update_date,
1943 last_update_login,
1944 object_version_number)
1945 SELECT
1946 'AR',
1947 OWNER_ID_NUM,
1948 Null,
1949 Null,
1950 'MIGRATED',
1951 zx_fc_country_defaults_s.nextval,
1952 fnd_global.user_id,
1953 SYSDATE,
1954 fnd_global.user_id,
1955 SYSDATE,
1956 FND_GLOBAL.CONC_LOGIN_ID,
1957 1
1958 FROM ZX_FC_TYPES_B
1959 WHERE classification_type_code = 'FISCAL_CLASSIFICATION'
1960 and classification_type_categ_code ='PRODUCT_FISCAL_CLASS'
1961 AND NOT EXISTS
1962 (SELECT 1 FROM ZX_FC_COUNTRY_DEFAULTS WHERE COUNTRY_CODE = 'AR');
1963 END IF;
1964
1965
1966 -- If Hungary is installed then
1967 IF Is_Country_Installed(7004, 'jlbrloc') THEN
1968
1969 arp_util_tax.debug( ' Creating data for Brazil..' );
1970
1971 INSERT INTO ZX_FC_COUNTRY_DEFAULTS (
1972 COUNTRY_CODE,
1973 PRIMARY_INVENTORY_CATEGORY_SET,
1974 INTENDED_USE_DEFAULT,
1975 PRODUCT_CATEG_DEFAULT,
1976 RECORD_TYPE_CODE,
1977 COUNTRY_DEFAULTS_ID,
1978 created_by,
1979 creation_date,
1980 last_updated_by,
1981 last_update_date,
1982 last_update_login,
1983 object_version_number)
1984 SELECT
1985 'BR',
1986 OWNER_ID_NUM,
1987 Null,
1988 Null,
1989 'MIGRATED',
1993 fnd_global.user_id,
1990 zx_fc_country_defaults_s.nextval,
1991 fnd_global.user_id,
1992 SYSDATE,
1994 SYSDATE,
1995 FND_GLOBAL.CONC_LOGIN_ID,
1996 1
1997 FROM ZX_FC_TYPES_B
1998 WHERE classification_type_code = 'FISCAL_CLASSIFICATION'
1999 and classification_type_categ_code ='PRODUCT_FISCAL_CLASS'
2000 AND NOT EXISTS
2001 (SELECT 1 FROM ZX_FC_COUNTRY_DEFAULTS WHERE COUNTRY_CODE = 'BR');
2002 END IF;
2003
2004 IF Is_Country_Installed(7004, 'jlcoloc') THEN
2005
2006 arp_util_tax.debug( ' Creating data for Colombia..' );
2007 INSERT INTO ZX_FC_COUNTRY_DEFAULTS (
2008 COUNTRY_CODE,
2009 PRIMARY_INVENTORY_CATEGORY_SET,
2010 INTENDED_USE_DEFAULT,
2011 PRODUCT_CATEG_DEFAULT,
2012 RECORD_TYPE_CODE,
2013 COUNTRY_DEFAULTS_ID,
2014 created_by,
2015 creation_date,
2016 last_updated_by,
2017 last_update_date,
2018 last_update_login,
2019 object_version_number)
2020 SELECT
2021 'CO',
2022 OWNER_ID_NUM,
2023 Null,
2024 Null,
2025 'MIGRATED',
2026 zx_fc_country_defaults_s.nextval,
2027 fnd_global.user_id,
2028 SYSDATE,
2029 fnd_global.user_id,
2030 SYSDATE,
2031 FND_GLOBAL.CONC_LOGIN_ID,
2032 1
2033 FROM ZX_FC_TYPES_B
2034 WHERE classification_type_code = 'FISCAL_CLASSIFICATION'
2035 and classification_type_categ_code ='PRODUCT_FISCAL_CLASS'
2036 AND NOT EXISTS
2037 (SELECT 1 FROM ZX_FC_COUNTRY_DEFAULTS WHERE COUNTRY_CODE = 'CO');
2038 END IF;
2039
2040 End if; -- End of Inventory checking
2041
2042 arp_util_tax.debug( ' COUNTRY_DEFAULT .. (-) ' );
2043
2044 END COUNTRY_DEFAULT ;
2045
2046 /*THIS IS THE COMMON PROCEDURE USED TO INSERT THE FIRST LEVEL FC CODES*/
2047
2048 PROCEDURE FIRST_LEVEL_FC_CODE_INSERT(
2049 p_classification_type_code IN ZX_FC_CODES_B.CLASSIFICATION_TYPE_CODE%TYPE,
2050 p_classification_code IN ZX_FC_CODES_B.CLASSIFICATION_CODE%TYPE,
2051 p_classification_name IN ZX_FC_CODES_TL.CLASSIFICATION_NAME%TYPE,
2052 p_country_code IN ZX_FC_CODES_B.COUNTRY_CODE%TYPE,
2053 x_fc_id OUT NOCOPY ZX_FC_CODES_B.CLASSIFICATION_ID%TYPE
2054 )
2055 IS
2056
2057 BEGIN
2058
2059 arp_util_tax.debug('FIRST_LEVEL_FC_CODE_INSERT(+)');
2060 arp_util_tax.debug('p_classification_type_code = ' || p_classification_type_code);
2061 arp_util_tax.debug('p_classification_code = ' || p_classification_code);
2062
2063 OPEN G_C_GET_TYPES_INFO(p_classification_type_code);
2064
2065 FETCH G_C_GET_TYPES_INFO INTO
2066 G_CLASSIFICATION_TYPE_ID,
2067 G_CLASSIFICATION_TYPE_CODE,
2068 G_CLASSIFICATION_TYPE_NAME,
2069 G_CLASSIFICATION_TYP_CATEG_COD,
2070 G_DELIMITER;
2071
2072 CLOSE G_C_GET_TYPES_INFO;
2073
2074 --select zx_fc_codes_b_s.nextval into x_fc_id from dual;
2075
2076 INSERT
2077 INTO ZX_FC_CODES_B (
2078 classification_type_code,
2079 classification_id,
2080 classification_code,
2081 effective_from,
2082 effective_to,
2083 parent_classification_code,
2084 Country_code,
2085 record_type_code,
2086 created_by,
2087 creation_date,
2088 last_updated_by,
2089 last_update_date,
2090 last_update_login,
2091 object_version_number)
2092 SELECT
2093 p_classification_type_code,
2094 zx_fc_codes_b_s.nextval,
2095 p_classification_code,
2096 Sysdate,
2097 Null,
2098 Null, ---parent_classification_code
2099 p_country_code,
2100 'SEEDED',
2101 120,
2102 SYSDATE,
2103 fnd_global.user_id,
2104 SYSDATE,
2105 FND_GLOBAL.CONC_LOGIN_ID,
2106 1
2107 FROM DUAL
2108 WHERE NOT EXISTS
2109 (select NULL from ZX_FC_CODES_B Codes where
2110 codes.classification_type_code = p_classification_type_code
2111 and codes.parent_classification_id is null
2112 and codes.classification_code = p_classification_code
2113 );
2114
2115 INSERT ALL
2116 INTO ZX_FC_CODES_TL(
2117 CLASSIFICATION_ID,
2118 CLASSIFICATION_NAME,
2119 CREATED_BY,
2120 CREATION_DATE,
2121 LAST_UPDATED_BY,
2122 LAST_UPDATE_DATE,
2123 LAST_UPDATE_LOGIN,
2124 LANGUAGE,
2125 SOURCE_LANG)
2126 VALUES
2127 (classification_id, --Gives the classification id information
2128 CASE WHEN p_classification_name = UPPER(p_classification_name)
2129 THEN Initcap(p_classification_name)
2133 fnd_global.user_id,
2130 ELSE
2131 p_classification_name
2132 END,
2134 SYSDATE,
2135 fnd_global.user_id,
2136 SYSDATE,
2137 FND_GLOBAL.CONC_LOGIN_ID,
2138 lang_code,
2139 userenv('LANG'))
2140 INTO ZX_FC_CODES_DENORM_B
2141 (CLASSIFICATION_TYPE_ID,
2142 CLASSIFICATION_TYPE_CODE,
2143 CLASSIFICATION_TYPE_NAME,
2144 CLASSIFICATION_TYPE_CATEG_CODE,
2145 CLASSIFICATION_ID,
2146 CLASSIFICATION_CODE,
2147 CLASSIFICATION_NAME,
2148 LANGUAGE,
2149 EFFECTIVE_FROM,
2150 EFFECTIVE_TO,
2151 ENABLED_FLAG,
2152 ANCESTOR_ID,
2153 ANCESTOR_CODE,
2154 ANCESTOR_NAME,
2155 CONCAT_CLASSIF_CODE,
2156 CONCAT_CLASSIF_NAME,
2157 CLASSIFICATION_CODE_LEVEL,
2158 COUNTRY_CODE,
2159 SEGMENT1,
2160 SEGMENT2,
2161 SEGMENT3,
2162 SEGMENT4,
2163 SEGMENT5,
2164 SEGMENT6,
2165 SEGMENT7,
2166 SEGMENT8,
2167 SEGMENT9,
2168 SEGMENT10,
2169 SEGMENT1_NAME,
2170 SEGMENT2_NAME,
2171 SEGMENT3_NAME,
2172 SEGMENT4_NAME,
2173 SEGMENT5_NAME,
2174 SEGMENT6_NAME,
2175 SEGMENT7_NAME,
2176 SEGMENT8_NAME,
2177 SEGMENT9_NAME,
2178 SEGMENT10_NAME,
2179 CREATED_BY,
2180 CREATION_DATE,
2181 LAST_UPDATED_BY,
2182 LAST_UPDATE_LOGIN,
2183 LAST_UPDATE_DATE,
2184 REQUEST_ID,
2185 PROGRAM_ID,
2186 PROGRAM_APPLICATION_ID,
2187 PROGRAM_LOGIN_ID,
2188 RECORD_TYPE_CODE)
2189 VALUES (
2190 G_CLASSIFICATION_TYPE_ID,
2191 G_CLASSIFICATION_TYPE_CODE,
2192 G_CLASSIFICATION_TYPE_NAME,
2193 G_CLASSIFICATION_TYP_CATEG_COD,
2194 classification_id,
2195 p_classification_code,
2196 p_classification_name,
2197 lang_code,
2198 sysdate,
2199 null,
2200 'Y',
2201 null,
2202 null,
2203 null,
2204 p_classification_code, --Concatenated classification code
2205 p_classification_name, --Concatenated classification name
2206 1,
2207 p_country_code,
2208 p_classification_code,
2209 Null,
2210 Null,
2211 Null,
2212 Null,
2213 Null,
2214 Null,
2215 Null,
2216 Null,
2217 Null,
2218 p_classification_name,
2219 Null,
2220 Null,
2221 Null,
2222 Null,
2223 Null,
2224 Null,
2225 Null,
2226 Null,
2227 Null,
2228 120,
2229 SYSDATE,
2230 fnd_global.user_id,
2231 FND_GLOBAL.CONC_LOGIN_ID,
2232 sysdate,
2233 FND_GLOBAL.CONC_REQUEST_ID,
2234 fnd_global.CONC_PROGRAM_ID,
2235 235,
2236 FND_GLOBAL.CONC_LOGIN_ID,
2237 'SEEDED')
2238 select
2239 language_code lang_code, fc_codes.classification_id
2240 from
2241 fnd_languages l,
2242 zx_fc_codes_b fc_codes
2246 and fc_codes.parent_classification_id is null
2243 where
2244 l.installed_flag in ('I', 'B')
2245 and fc_codes.classification_type_code = p_classification_type_code
2247 and fc_codes.classification_code = p_classification_code
2248
2249 AND NOT EXISTS -- this condition makes sure we dont duplicate data
2250 (select NULL from ZX_FC_CODES_DENORM_B CODES where
2251 codes.classification_type_code = p_classification_type_code
2252 and codes.classification_code = p_classification_code
2253 and codes.ancestor_id is null
2254 and codes.LANGUAGE = L.LANGUAGE_CODE);
2255
2256 -- Find and return the classification id
2257 SELECT
2258 classification_id into x_fc_id
2259 from
2260 ZX_FC_CODES_B Codes
2261 where
2262 codes.classification_type_code = p_classification_type_code
2263 and codes.parent_classification_id is null
2264 and codes.classification_code = p_classification_code;
2265
2266 arp_util_tax.debug('FIRST_LEVEL_FC_CODE_INSERT(-)');
2267
2268 END FIRST_LEVEL_FC_CODE_INSERT;
2269
2270
2271
2272 /*THIS IS THE COMMON PROCEDURE USED TO INSERT VALUES BASED UPON THE LOOKUP TYPE */
2273
2274 PROCEDURE FC_CODE_FROM_FND_LOOKUP(
2275 p_classification_type_code IN ZX_FC_CODES_B.CLASSIFICATION_TYPE_CODE%TYPE,
2276 p_lookup_type IN FND_LOOKUP_VALUES.LOOKUP_TYPE%TYPE,
2277 p_country_code IN ZX_FC_CODES_B.COUNTRY_CODE%TYPE,
2278 p_parent_fc_id IN ZX_FC_CODES_B.CLASSIFICATION_ID%TYPE,
2279 p_ancestor_code IN ZX_FC_CODES_DENORM_B.ANCESTOR_CODE%TYPE,
2280 p_ancestor_name IN ZX_FC_CODES_DENORM_B.ANCESTOR_NAME%TYPE,
2281 p_classification_code_level IN ZX_FC_CODES_DENORM_B.CLASSIFICATION_CODE_LEVEL%TYPE)
2282 IS
2283
2284 BEGIN
2285
2286 arp_util_tax.debug('FC_CODE_FROM_FND_LOOKUP(+)');
2287
2288 arp_util_tax.debug('p_classification_type_code = ' || p_classification_type_code);
2289 arp_util_tax.debug('p_lookup_type = ' || p_lookup_type);
2290
2291 OPEN G_C_GET_TYPES_INFO(p_classification_type_code);
2292
2293 FETCH G_C_GET_TYPES_INFO INTO
2294 G_CLASSIFICATION_TYPE_ID,
2295 G_CLASSIFICATION_TYPE_CODE,
2296 G_CLASSIFICATION_TYPE_NAME,
2297 G_CLASSIFICATION_TYP_CATEG_COD,
2298 G_DELIMITER;
2299
2300 CLOSE G_C_GET_TYPES_INFO;
2301
2302 INSERT
2303 INTO ZX_FC_CODES_B
2304 (classification_type_code,
2305 classification_id,
2306 classification_code,
2307 effective_from,
2308 effective_to,
2309 parent_classification_code,
2310 parent_classification_id,
2311 country_code,
2312 record_type_code,
2313 created_by,
2314 creation_date,
2315 last_updated_by,
2316 last_update_date,
2317 last_update_login,
2318 object_version_number)
2319 SELECT p_classification_type_code,
2320 zx_fc_codes_b_s.nextval,
2321 lookup_code,
2322 nvl(start_date_active,to_date('01/01/1951','DD/MM/YYYY')),
2323 end_date_active,
2324 p_ancestor_code,----parent_classification_code
2325 p_parent_fc_id,-----parent_classification_id
2326 p_country_code,
2327 'MIGRATED',
2328 fnd_global.user_id,
2329 SYSDATE,
2330 fnd_global.user_id,
2331 SYSDATE,
2332 FND_GLOBAL.CONC_LOGIN_ID,
2333 1
2334 FROM FND_LOOKUP_VALUES lookups
2335 WHERE
2336 lookups.lookup_type=p_lookup_type
2337 AND LANGUAGE = userenv('LANG')
2338 AND lookups.enabled_flag = 'Y'
2339 AND NOT EXISTS -- this condition makes sure we dont duplicate data
2340 (select NULL from ZX_FC_CODES_B Codes where
2341 codes.classification_type_code = p_classification_type_code
2342 and ( codes.parent_classification_id = p_parent_fc_id or
2343 p_parent_fc_id is null )
2344 and codes.classification_code = lookups.lookup_code
2345 );
2346
2347 INSERT ALL INTO
2348 ZX_FC_CODES_TL
2349 (CLASSIFICATION_ID,
2350 CLASSIFICATION_NAME,
2351 CREATED_BY,
2352 CREATION_DATE,
2353 LAST_UPDATED_BY,
2354 LAST_UPDATE_DATE,
2355 LAST_UPDATE_LOGIN,
2356 LANGUAGE,
2357 SOURCE_LANG)
2358 VALUES (classification_id,
2359 CASE WHEN Meaning = UPPER(Meaning)
2360 THEN Initcap(Meaning)
2361 ELSE
2362 Meaning
2363 END,
2364 fnd_global.user_id,
2365 SYSDATE,
2366 fnd_global.user_id,
2367 SYSDATE,
2368 FND_GLOBAL.CONC_LOGIN_ID,
2369 language,
2370 SOURCE_LANG)
2371 INTO ZX_FC_CODES_DENORM_B(
2372 CLASSIFICATION_TYPE_ID,
2373 CLASSIFICATION_TYPE_CODE,
2374 CLASSIFICATION_TYPE_NAME,
2375 CLASSIFICATION_TYPE_CATEG_CODE,
2376 CLASSIFICATION_ID,
2377 CLASSIFICATION_CODE,
2378 CLASSIFICATION_NAME,
2379 LANGUAGE,
2380 EFFECTIVE_FROM,
2381 EFFECTIVE_TO,
2382 ENABLED_FLAG,
2383 ANCESTOR_ID,
2384 ANCESTOR_CODE,
2385 ANCESTOR_NAME,
2386 CONCAT_CLASSIF_CODE,
2387 CONCAT_CLASSIF_NAME,
2388 CLASSIFICATION_CODE_LEVEL,
2389 COUNTRY_CODE,
2390 SEGMENT1,
2391 SEGMENT2,
2392 SEGMENT3,
2393 SEGMENT4,
2394 SEGMENT5,
2395 SEGMENT6,
2399 SEGMENT10,
2396 SEGMENT7,
2397 SEGMENT8,
2398 SEGMENT9,
2400 SEGMENT1_NAME,
2401 SEGMENT2_NAME,
2402 SEGMENT3_NAME,
2403 SEGMENT4_NAME,
2404 SEGMENT5_NAME,
2405 SEGMENT6_NAME,
2406 SEGMENT7_NAME,
2407 SEGMENT8_NAME,
2408 SEGMENT9_NAME,
2409 SEGMENT10_NAME,
2410 CREATED_BY,
2411 CREATION_DATE,
2412 LAST_UPDATED_BY,
2413 LAST_UPDATE_LOGIN,
2414 LAST_UPDATE_DATE,
2415 REQUEST_ID,
2416 PROGRAM_ID,
2417 PROGRAM_APPLICATION_ID,
2418 PROGRAM_LOGIN_ID,
2419 RECORD_TYPE_CODE)
2420 VALUES (
2421 G_CLASSIFICATION_TYPE_ID,
2422 G_CLASSIFICATION_TYPE_CODE,
2423 G_CLASSIFICATION_TYPE_NAME,
2424 G_CLASSIFICATION_TYP_CATEG_COD,
2425 classification_id,
2426 lookup_code,
2427 Meaning,
2428 Language,
2429 start_date_active,
2430 end_date_active,
2431 enabled_flag,
2432 p_parent_fc_id,
2433 p_ancestor_code,
2434 p_ancestor_name,
2435 nvl2(p_ancestor_code,
2436 p_ancestor_code||G_DELIMITER||lookup_code,
2437 lookup_code),
2438 nvl2(p_ancestor_name,
2439 p_ancestor_name||G_DELIMITER||Meaning,
2440 Meaning),
2441 p_classification_code_level,
2442 p_country_code,
2443 nvl(p_ancestor_code, lookup_code), -- Segment1
2444 nvl2(p_ancestor_code, lookup_code, null), -- Segment2
2445 Null,
2446 Null,
2447 Null,
2448 Null,
2449 Null,
2450 Null,
2451 Null,
2452 Null,
2453 nvl(p_ancestor_name, Meaning), -- Segment1 Name
2454 nvl2(p_ancestor_name, Meaning, null), -- Segment2 Name
2455 Null,
2456 Null,
2457 Null,
2458 Null,
2459 Null,
2460 Null,
2461 Null,
2462 Null,
2463 fnd_global.user_id,
2464 SYSDATE,
2465 fnd_global.user_id,
2466 FND_GLOBAL.CONC_LOGIN_ID,
2467 sysdate,
2468 FND_GLOBAL.CONC_REQUEST_ID,
2469 fnd_global.CONC_PROGRAM_ID,
2470 235,
2471 FND_GLOBAL.CONC_LOGIN_ID,
2472 'MIGRATED')
2473 SELECT lookup_code,
2474 Meaning,
2475 Language,
2476 source_lang,
2477 nvl(start_date_active,to_date('01/01/1951','DD/MM/YYYY')) start_date_active,
2478 end_date_active,
2479 fl.enabled_flag,
2480 Codes.classification_id
2481 FROM
2482 zx_fc_codes_b Codes,
2483 fnd_lookup_values fl,
2484 fnd_languages l
2485 WHERE
2486
2487 codes.classification_type_code = p_classification_type_code
2488 and (codes.parent_classification_id = p_parent_fc_id or
2489 p_parent_fc_id is null)
2490 and codes.classification_code = fl.lookup_code
2491 AND Codes.RECORD_TYPE_CODE = 'MIGRATED'
2492 and fl.lookup_type = p_lookup_type
2493 AND fl.enabled_flag = 'Y' -- need to check again
2494 AND fl.language=l.language_code(+)
2495 AND l.INSTALLED_FLAG in ('I', 'B')
2496
2497 AND NOT EXISTS -- this condition makes sure we dont duplicate data
2498 (select NULL from ZX_FC_CODES_DENORM_B codes where
2499 codes.classification_type_code = G_CLASSIFICATION_TYPE_CODE
2500 and codes.classification_code = fl.lookup_code
2501 and (codes.ancestor_id = p_parent_fc_id or
2502 p_parent_fc_id is null)
2503 and codes.language = l.language_code);
2504
2505 arp_util_tax.debug('FC_CODE_FROM_FND_LOOKUP(-)');
2506
2507 END FC_CODE_FROM_FND_LOOKUP;
2508
2509
2510 /*THIS IS THE COMMON PROCEDURE USED TO PERFORM INSERTS BASED ON THE GLOBAL DESCRIPTIVE FLEXI FIELDS
2511 This procedure is called from zxcfctbc.ldt file also */
2512
2513 PROCEDURE FC_CODE_GDF_INSERT(
2514 p_classification_code IN ZX_FC_CODES_B.CLASSIFICATION_CODE%TYPE,
2515 p_classification_name IN ZX_FC_CODES_TL.CLASSIFICATION_NAME%TYPE,
2516 p_country_code IN ZX_FC_CODES_B.COUNTRY_CODE%TYPE,
2517 p_lookup_type IN FND_LOOKUP_VALUES.LOOKUP_TYPE%TYPE,
2518 p_tax_event_class_code IN ZX_EVENT_CLASSES_VL.TAX_EVENT_CLASS_CODE%TYPE,
2519 p_record_type_code IN ZX_FC_CODES_B.RECORD_TYPE_CODE%TYPE
2520 )
2521
2522 IS
2523
2524 BEGIN
2525
2526 arp_util_tax.debug('FC_CODE_GDF_INSERT(+)');
2527
2528 arp_util_tax.debug('p_classification_code = ' || p_classification_code);
2529 arp_util_tax.debug('p_lookup_type = ' || p_lookup_type);
2530
2531
2532 OPEN G_C_GET_TYPES_INFO('TRX_BUSINESS_CATEGORY');
2533
2534 fetch G_C_GET_TYPES_INFO into
2535
2536 G_CLASSIFICATION_TYPE_ID,
2537 G_CLASSIFICATION_TYPE_CODE,
2538 G_CLASSIFICATION_TYPE_NAME,
2539 G_CLASSIFICATION_TYP_CATEG_COD,
2540 G_DELIMITER;
2541
2542 CLOSE G_C_GET_TYPES_INFO;
2543
2544 --Insert the GDF prompt in second level
2545 GDF_PROMPT_INSERT(p_classification_code, p_classification_name, p_country_code, p_tax_event_class_code);
2546
2547 /*
2548 Create a Code under the Transaction Business Category Type, 3rd Level
2549 */
2550
2551 arp_util_tax.debug( 'Create Code under the Transaction Business Category Type-Level 3: ' || p_country_code);
2552
2553 INSERT
2554 INTO ZX_FC_CODES_B (
2555 classification_type_code,
2556 classification_id,
2557 classification_code,
2558 effective_from,
2559 effective_to,
2560 parent_classification_code,
2564 created_by,
2561 parent_classification_id,
2562 Country_code,
2563 record_type_code,
2565 creation_date,
2566 last_updated_by,
2567 last_update_date,
2568 last_update_login,
2569 object_version_number)
2570 SELECT
2571 'TRX_BUSINESS_CATEGORY',
2572 zx_fc_codes_b_s.nextval,
2573 lookup_code,
2574 nvl(start_date_active,to_date('01/01/1951','DD/MM/YYYY')) start_date_active,
2575 end_date_active,
2576 fc.classification_code ,---parent_classification_code
2577 fc.classification_id, ---parent_classification_id
2578 p_country_code,
2579 p_record_type_code,
2580 decode(p_record_type_code, 'SEEDED', 120, fnd_global.user_id),
2581 SYSDATE,
2582 decode(p_record_type_code, 'SEEDED', 120, fnd_global.user_id),
2583 SYSDATE,
2584 decode(p_record_type_code, 'SEEDED', 0, fnd_global.conc_login_id),
2585 1
2586 FROM
2587 ZX_FC_CODES_DENORM_B fc,
2588 ZX_EVENT_CLASSES_VL event,
2589 FND_LOOKUP_VALUES FL
2590 WHERE
2591 fc.classification_type_code = 'TRX_BUSINESS_CATEGORY'
2592 and fc.classification_code=p_classification_code
2593 and fc.language = userenv('LANG')
2594 and fc.ancestor_code = event.tax_event_class_code
2595 and fc.classification_code_level = 2
2596 and fl.lookup_type = p_lookup_type
2597 and fl.enabled_flag = 'Y'
2598 and fl.language = userenv('LANG')
2599 and event.tax_event_class_code = p_tax_event_class_code
2600 and NOT EXISTS -- this condition makes sure we dont duplicate data
2601 (select NULL from ZX_FC_CODES_B Codes where
2602 codes.classification_type_code = 'TRX_BUSINESS_CATEGORY'
2603 and codes.parent_classification_id =
2604 nvl(fc.classification_id,codes.parent_classification_id)
2605 and codes.classification_code = fl.lookup_code
2606 );
2607
2608
2609 INSERT ALL
2610 INTO ZX_FC_CODES_TL
2611 (CLASSIFICATION_ID,
2612 CLASSIFICATION_NAME,
2613 CREATED_BY,
2614 CREATION_DATE,
2615 LAST_UPDATED_BY,
2616 LAST_UPDATE_DATE,
2617 LAST_UPDATE_LOGIN,
2618 LANGUAGE,
2619 SOURCE_LANG)
2620 VALUES (classification_id,
2621 CASE WHEN Meaning = UPPER(Meaning)
2622 THEN Initcap(Meaning)
2623 ELSE
2624 Meaning
2625 END,
2626 decode(p_record_type_code, 'SEEDED', 120, fnd_global.user_id),
2627 SYSDATE,
2628 decode(p_record_type_code, 'SEEDED', 120, fnd_global.user_id),
2629 SYSDATE,
2630 decode(p_record_type_code, 'SEEDED', 0, fnd_global.conc_login_id),
2631 language,
2632 SOURCE_LANG)
2633 INTO ZX_FC_CODES_DENORM_B(
2634 CLASSIFICATION_TYPE_ID,
2635 CLASSIFICATION_TYPE_CODE,
2636 CLASSIFICATION_TYPE_NAME,
2637 CLASSIFICATION_TYPE_CATEG_CODE,
2638 CLASSIFICATION_ID,
2639 CLASSIFICATION_CODE,
2640 CLASSIFICATION_NAME,
2641 LANGUAGE,
2642 EFFECTIVE_FROM,
2643 EFFECTIVE_TO,
2644 ENABLED_FLAG,
2645 ANCESTOR_ID,
2646 ANCESTOR_CODE,
2647 ANCESTOR_NAME,
2648 CONCAT_CLASSIF_CODE,
2649 CONCAT_CLASSIF_NAME,
2650 CLASSIFICATION_CODE_LEVEL,
2651 COUNTRY_CODE,
2652 SEGMENT1,
2653 SEGMENT2,
2654 SEGMENT3,
2655 SEGMENT4,
2656 SEGMENT5,
2657 SEGMENT6,
2658 SEGMENT7,
2659 SEGMENT8,
2660 SEGMENT9,
2661 SEGMENT10,
2662 SEGMENT1_NAME,
2663 SEGMENT2_NAME,
2664 SEGMENT3_NAME,
2665 SEGMENT4_NAME,
2666 SEGMENT5_NAME,
2667 SEGMENT6_NAME,
2668 SEGMENT7_NAME,
2669 SEGMENT8_NAME,
2670 SEGMENT9_NAME,
2671 SEGMENT10_NAME,
2672 CREATED_BY,
2673 CREATION_DATE,
2674 LAST_UPDATED_BY,
2675 LAST_UPDATE_LOGIN,
2676 LAST_UPDATE_DATE,
2677 REQUEST_ID,
2678 PROGRAM_ID,
2679 PROGRAM_APPLICATION_ID,
2680 PROGRAM_LOGIN_ID,
2681 RECORD_TYPE_CODE)
2682 VALUES (
2683 G_CLASSIFICATION_TYPE_ID,
2684 G_CLASSIFICATION_TYPE_CODE,
2685 G_CLASSIFICATION_TYPE_NAME,
2686 G_CLASSIFICATION_TYP_CATEG_COD,
2687 classification_id,
2688 lookup_code,
2689 meaning,
2690 Language,
2691 start_date_active,
2692 end_date_active,
2693 enabled_flag,
2694 parent_fc_id,
2695 parent_fc_code,
2696 parent_fc_name,
2697 tax_event_class_code ||G_DELIMITER || parent_fc_code || G_DELIMITER || lookup_code,
2698 Name || G_DELIMITER || parent_fc_name || G_DELIMITER || Meaning,
2699 3,
2700 p_country_code,
2701 tax_event_class_code,
2702 parent_fc_code,
2703 lookup_code,
2704 Null,
2705 Null,
2706 Null,
2707 Null,
2708 Null,
2709 Null,
2710 Null,
2711 Name,
2712 parent_fc_name,
2713 Meaning,
2714 Null,
2715 Null,
2716 Null,
2717 Null,
2718 Null,
2719 Null,
2720 Null,
2721 decode(p_record_type_code, 'SEEDED', 120, fnd_global.user_id),
2722 SYSDATE,
2723 decode(p_record_type_code, 'SEEDED', 120, fnd_global.user_id),
2724 decode(p_record_type_code, 'SEEDED', 0, fnd_global.conc_login_id),
2725 sysdate,
2726 FND_GLOBAL.CONC_REQUEST_ID,
2727 fnd_global.CONC_PROGRAM_ID,
2728 235,
2729 FND_GLOBAL.CONC_LOGIN_ID,
2733 meaning,
2730 p_record_type_code)
2731 SELECT
2732 lookup_code,
2734 nvl(start_date_active,to_date('01/01/1951','DD/MM/YYYY')) start_date_active,
2735 end_date_active,
2736 tax_event_class_code,
2737 event.tax_event_class_name name,
2738 fc.classification_id as parent_fc_id,
2739 fc.classification_code as parent_fc_code,
2740 fc.classification_name as parent_fc_name,
2741 fl.language,
2742 fl.source_lang,
2743 fl.enabled_flag,
2744 Codes.classification_id
2745
2746 FROM
2747 ZX_FC_CODES_DENORM_B fc,
2748 ZX_FC_CODES_b Codes,
2749 ZX_EVENT_CLASSES_VL event,
2750 FND_LOOKUP_VALUES FL,
2751 FND_LANGUAGES L
2752 WHERE fc.classification_type_code = 'TRX_BUSINESS_CATEGORY'
2753 and fc.classification_code=p_classification_code
2754 and fc.language = userenv('LANG')
2755 and fc.ancestor_code = event.tax_event_class_code
2756 and fc.classification_code_level = 2
2757 and Codes.classification_type_code = 'TRX_BUSINESS_CATEGORY'
2758 and Codes.parent_classification_id = fc.classification_id
2759 and Codes.classification_code = FL.lookup_code
2760 and Codes.record_type_code = p_record_type_code
2761 and event.tax_event_class_code = p_tax_event_class_code
2762 and lookup_type = p_lookup_type
2763 and FL.enabled_flag = 'Y'
2764 and FL.language=L.language_code(+)
2765 and L.INSTALLED_FLAG in ('I', 'B')
2766 and NOT EXISTS -- this condition makes sure we dont duplicate data
2767 ( select NULL from ZX_FC_CODES_DENORM_B codes
2768 where codes.classification_type_code = G_CLASSIFICATION_TYPE_CODE
2769 and codes.classification_code = fl.lookup_code
2770 and codes.ancestor_id = nvl(fc.classification_id,codes.ancestor_id)
2771 and codes.language = l.language_code);
2772
2773 arp_util_tax.debug('FC_CODE_GDF_INSERT(-)');
2774
2775 END FC_CODE_GDF_INSERT;
2776
2777
2778 /*THIS IS THE COMMON PROCEDURE USED TO INSERT THE GLOBAL DESCRIPTIVE FLEXI FIELD PROMPT VALUE*/
2779
2780 PROCEDURE GDF_PROMPT_INSERT(
2781 p_classification_code IN ZX_FC_CODES_B.CLASSIFICATION_CODE%TYPE,
2782 p_classification_name IN ZX_FC_CODES_TL.CLASSIFICATION_NAME%TYPE,
2783 p_country_code IN ZX_FC_CODES_B.COUNTRY_CODE%TYPE,
2784 p_tax_event_class_code IN ZX_EVENT_CLASSES_VL.TAX_EVENT_CLASS_CODE%TYPE
2785 )
2786
2787 IS
2788
2789 BEGIN
2790
2791 arp_util_tax.debug('GDF_PROMPT_INSERT(+)');
2792
2793 arp_util_tax.debug('p_classification_code = ' || p_classification_code);
2794
2795 arp_util_tax.debug( 'Create the Second Level of Classification Codes for :'||p_country_code);
2796
2797 OPEN G_C_GET_TYPES_INFO('TRX_BUSINESS_CATEGORY');
2798
2799 fetch G_C_GET_TYPES_INFO into
2800
2801 G_CLASSIFICATION_TYPE_ID,
2802 G_CLASSIFICATION_TYPE_CODE,
2803 G_CLASSIFICATION_TYPE_NAME,
2804 G_CLASSIFICATION_TYP_CATEG_COD,
2805 G_DELIMITER;
2806
2807 CLOSE G_C_GET_TYPES_INFO;
2808
2809 INSERT
2810 INTO ZX_FC_CODES_B
2811 (classification_type_code,
2812 classification_id,
2813 classification_code,
2814 effective_from,
2815 effective_to,
2816 parent_classification_code,
2817 parent_classification_id,
2818 country_code,
2819 record_type_code,
2820 created_by,
2821 creation_date,
2822 last_updated_by,
2823 last_update_date,
2824 last_update_login,
2825 object_version_number)
2826 SELECT
2827 'TRX_BUSINESS_CATEGORY', --classification_type_code
2828 zx_fc_codes_b_s.nextval, --classification_id
2829 p_classification_code, --classification_code
2830 sysdate, --effective_from
2831 null, --effective_to
2832 event.tax_event_class_code, --parent_classification_code
2833 fc.classification_id, --parent_classification_id
2834 p_country_code, --country_code
2835 'SEEDED', --record_type_code
2836 120,
2837 sysdate,
2838 fnd_global.user_id,
2839 sysdate,
2840 fnd_global.conc_login_id,
2841 1
2842 FROM
2843 ZX_FC_CODES_B fc,
2844 ZX_EVENT_CLASSES_B event
2845 WHERE
2846 fc.classification_type_code = 'TRX_BUSINESS_CATEGORY'
2847 and fc.parent_classification_id is null
2848 and fc.classification_code=event.tax_event_class_code
2849 and event.tax_event_class_code = p_tax_event_class_code
2850 and not exists -- this condition makes sure we dont duplicate data
2851 (select NULL from ZX_FC_CODES_B Codes where
2852 codes.classification_type_code = 'TRX_BUSINESS_CATEGORY'
2853 and codes.parent_classification_id =
2854 nvl(fc.classification_id,codes.parent_classification_id)
2855 and codes.classification_code = p_classification_code
2856 );
2857
2858 INSERT ALL
2859 INTO ZX_FC_CODES_TL
2860 (CLASSIFICATION_ID,
2861 CLASSIFICATION_NAME,
2862 CREATED_BY,
2863 CREATION_DATE,
2864 LAST_UPDATED_BY,
2865 LAST_UPDATE_DATE,
2866 LAST_UPDATE_LOGIN,
2867 LANGUAGE,
2868 SOURCE_LANG)
2869 VALUES(
2870 classification_id,
2871 CASE WHEN p_classification_name = UPPER(p_classification_name)
2872 THEN Initcap(p_classification_name)
2873 ELSE
2877 sysdate,
2874 p_classification_name
2875 END,
2876 fnd_global.user_id,
2878 fnd_global.user_id,
2879 sysdate,
2880 fnd_global.conc_login_id,
2881 lang_code,
2882 userenv('LANG'))
2883
2884 INTO ZX_FC_CODES_DENORM_B(
2885 CLASSIFICATION_TYPE_ID,
2886 CLASSIFICATION_TYPE_CODE,
2887 CLASSIFICATION_TYPE_NAME,
2888 CLASSIFICATION_TYPE_CATEG_CODE,
2889 CLASSIFICATION_ID,
2890 CLASSIFICATION_CODE,
2891 CLASSIFICATION_NAME,
2892 LANGUAGE,
2893 EFFECTIVE_FROM,
2894 EFFECTIVE_TO,
2895 ENABLED_FLAG,
2896 ANCESTOR_ID,
2897 ANCESTOR_CODE,
2898 ANCESTOR_NAME,
2899 CONCAT_CLASSIF_CODE,
2900 CONCAT_CLASSIF_NAME,
2901 CLASSIFICATION_CODE_LEVEL,
2902 COUNTRY_CODE,
2903 SEGMENT1,
2904 SEGMENT2,
2905 SEGMENT3,
2906 SEGMENT4,
2907 SEGMENT5,
2908 SEGMENT6,
2909 SEGMENT7,
2910 SEGMENT8,
2911 SEGMENT9,
2912 SEGMENT10,
2913 SEGMENT1_NAME,
2914 SEGMENT2_NAME,
2915 SEGMENT3_NAME,
2916 SEGMENT4_NAME,
2917 SEGMENT5_NAME,
2918 SEGMENT6_NAME,
2919 SEGMENT7_NAME,
2920 SEGMENT8_NAME,
2921 SEGMENT9_NAME,
2922 SEGMENT10_NAME,
2923 CREATED_BY,
2924 CREATION_DATE,
2925 LAST_UPDATED_BY,
2926 LAST_UPDATE_LOGIN,
2927 LAST_UPDATE_DATE,
2928 REQUEST_ID,
2929 PROGRAM_ID,
2930 PROGRAM_APPLICATION_ID,
2931 PROGRAM_LOGIN_ID,
2932 RECORD_TYPE_CODE)
2933 VALUES (
2934 G_CLASSIFICATION_TYPE_ID,
2935 G_CLASSIFICATION_TYPE_CODE,
2936 G_CLASSIFICATION_TYPE_NAME,
2937 G_CLASSIFICATION_TYP_CATEG_COD,
2938 classification_id, --CLASSIFICATION_ID
2939 p_classification_code, --CLASSIFICATION_CODE
2940 p_classification_name, --CLASSIFICATION_NAME
2941 lang_code, --LANGUAGE
2942 sysdate, --EFFECTIVE_FROM
2943 null, --EFFECTIVE_TO
2944 'Y', --ENABLED_FLAG
2945 parent_fc_id, --ANCESTOR_ID
2946 tax_event_class_code, --ANCESTOR_CODE
2947 Name, --ANCESTOR_NAME
2948 tax_event_class_code ||G_DELIMITER ||
2949 p_classification_code, --CONCAT_CLASSIF_CODE
2950 Name || G_DELIMITER ||
2951 p_classification_name , --CONCAT_CLASSIF_NAME
2952 2, --CLASSIFICATION_CODE_LEVEL
2953 p_country_code, --COUNTRY_CODE
2954 tax_event_class_code, --SEGMENT1
2955 p_classification_code, --SEGMENT2
2956 Null,
2957 Null,
2958 Null,
2959 Null,
2960 Null,
2961 Null,
2962 Null,
2963 Null,
2964 Name, --SEGMENT1_NAME
2965 p_classification_name , --SEGMENT2_NAME
2966 Null,
2967 Null,
2968 Null,
2969 Null,
2970 Null,
2971 Null,
2972 Null,
2973 Null,
2974 120,
2975 sysdate,
2976 fnd_global.user_id,
2977 fnd_global.conc_login_id,
2978 sysdate,
2979 fnd_global.conc_request_id,
2980 fnd_global.conc_program_id,
2981 235,
2982 fnd_global.conc_login_id,
2983 'SEEDED')
2984 SELECT
2985 event.tax_event_class_code,
2986 event.tax_event_class_name name,
2987 fc.classification_id as parent_fc_id,
2988 zx.classification_id as classification_id,
2989 L.language_code lang_code
2990 FROM
2991 ZX_FC_CODES_B fc,
2992 ZX_FC_CODES_B zx,
2993 ZX_EVENT_CLASSES_TL event,
2994 FND_LANGUAGES L
2995 WHERE
2996 fc.classification_type_code = 'TRX_BUSINESS_CATEGORY'
2997 and fc.parent_classification_id is null
2998 and fc.classification_code=event.tax_event_class_code
2999 and event.tax_event_class_code = p_tax_event_class_code
3000 and zx.classification_type_code = 'TRX_BUSINESS_CATEGORY'
3001 and zx.parent_classification_id = fc.classification_id
3002 and zx.classification_code = p_classification_code
3003 and (zx.RECORD_TYPE_CODE = 'MIGRATED' OR zx.RECORD_TYPE_CODE = 'SEEDED')
3004 and event.language = L.language_code(+)
3005 and L.INSTALLED_FLAG in ('I', 'B')
3006 and NOT EXISTS -- this condition makes sure we dont duplicate data
3007 ( select NULL from ZX_FC_CODES_DENORM_B codes
3008 where
3009 codes.classification_type_code = G_CLASSIFICATION_TYPE_CODE
3010 and codes.classification_code = p_classification_code
3011 and codes.ancestor_id = nvl(fc.classification_id,codes.ancestor_id)
3012 and codes.language = l.language_code
3013 );
3014
3015 arp_util_tax.debug('GDF_PROMPT_INSERT(-)');
3016
3017 END GDF_PROMPT_INSERT;
3018
3019
3020 /* COMMON PROCEDURE USED TO ASSOCIATE THE ITEMS */
3021 PROCEDURE ASSOCIATE_ITEMS(p_global_attribute_category
3022 in varchar2) IS
3023 BEGIN
3024
3025 arp_util_tax.debug('ASSOCIATE_ITEMS(+)');
3026
3027 arp_util_tax.debug('p_global_attribute_category = ' || p_global_attribute_category);
3028
3029 /* Regime Association to Fiscal Type */
3030 INSERT ALL INTO
3031 ZX_FC_TYPES_REG_ASSOC
3032 ( Tax_regime_code,
3033 Classification_type_code,
3034 effective_from,
3035 effective_to,
3036 Record_type_code,
3037 created_by,
3038 creation_date,
3039 last_updated_by,
3040 last_update_date,
3041 last_update_login,
3045 ( TAX_REGIME_CODE,
3042 classif_regime_id,
3043 object_version_number)
3044 VALUES
3046 'STATISTICAL CODE',
3047 SYSDATE,
3048 NULL,
3049 'MIGRATED',
3050 fnd_global.user_id,
3051 SYSDATE,
3052 fnd_global.user_id,
3053 SYSDATE,
3054 FND_GLOBAL.CONC_LOGIN_ID,
3055 zx_fc_types_reg_assoc_s.nextval,
3056 1)
3057 Select unique
3058 tax_regime_code
3059 FROM ZX_RATES_B rates,
3060 AP_TAX_CODES_ALL codes
3061 WHERE
3062 codes.tax_id = nvl(rates.source_id, rates.tax_rate_id) and
3063 codes.global_attribute_category = p_global_attribute_category and
3064 rates.record_type_code = 'MIGRATED' and
3065 not exists
3066 (select null from ZX_FC_TYPES_REG_ASSOC
3067 where classification_type_code = 'STATISTICAL CODE' and
3068 tax_regime_code = rates.tax_regime_code);
3069
3070 arp_util_tax.debug('ASSOCIATE_ITEMS(-)');
3071
3072 End Associate_items;
3073
3074
3075 /* COMMON PROCEDURE USED TO INSERT THE FC TYPES */
3076 PROCEDURE FC_TYPE_INSERT(
3077 p_classification_type_code IN ZX_FC_TYPES_B.CLASSIFICATION_TYPE_CODE%TYPE,
3078 p_classification_type_name IN ZX_FC_TYPES_TL.CLASSIFICATION_TYPE_NAME%TYPE,
3079 p_owner_id_num IN ZX_FC_TYPES_B.OWNER_ID_NUM%TYPE
3080 ) IS
3081 BEGIN
3082
3083 arp_util_tax.debug('Creating the fiscal classification types ');
3084
3085 arp_util_tax.debug('p_classification_type_code = ' || p_classification_type_code);
3086
3087 INSERT
3088 INTO ZX_FC_TYPES_B(
3089 classification_type_id,
3090 classification_type_code,
3091 classification_type_categ_code,
3092 effective_from,
3093 effective_to,
3094 classification_type_level_code,
3095 owner_table_code,
3096 owner_id_num,
3097 start_position,
3098 num_characters,
3099 record_type_code,
3100 created_by,
3101 creation_date,
3102 last_updated_by,
3103 last_update_date,
3104 last_update_login,
3105 object_version_number)
3106 (SELECT
3107 zx_fc_types_b_s.nextval,
3108 p_classification_type_code,
3109 'PRODUCT_FISCAL_CLASS',
3110 sysdate,
3111 null,
3112 1,
3113 'MTL_CATEGORY_SETS_B',
3114 p_owner_id_num,
3115 1,
3116 400,
3117 'MIGRATED',
3118 fnd_global.user_id,
3119 SYSDATE,
3120 fnd_global.user_id,
3121 SYSDATE,
3122 FND_GLOBAL.CONC_LOGIN_ID,
3123 1
3124 FROM dual
3125 WHERE NOT EXISTS
3126 (SELECT null from ZX_FC_TYPES_B
3127 WHERE classification_type_code = p_classification_type_code
3128 and classification_type_categ_code = 'PRODUCT_FISCAL_CLASS')
3129 );
3130
3131
3132 INSERT
3133 INTO ZX_FC_TYPES_TL(
3134 CLASSIFICATION_TYPE_ID,
3135 CLASSIFICATION_TYPE_NAME,
3136 CREATED_BY,
3137 CREATION_DATE,
3138 LAST_UPDATED_BY,
3139 LAST_UPDATE_DATE,
3140 LAST_UPDATE_LOGIN,
3141 LANGUAGE,
3142 SOURCE_LANG)
3143 (select
3144 classification_type_id,
3145 CASE WHEN p_classification_type_name = UPPER(p_classification_type_name)
3146 THEN Initcap(p_classification_type_name)
3147 ELSE
3148 p_classification_type_name
3149 END,
3150 fnd_global.user_id,
3151 SYSDATE,
3152 fnd_global.user_id,
3153 SYSDATE,
3154 FND_GLOBAL.CONC_LOGIN_ID,
3155 language_code,
3156 userenv('LANG')
3157
3158 FROM FND_LANGUAGES L,
3159 ZX_FC_TYPES_B Types
3160 where
3161 Types.classification_type_code = p_classification_type_code
3162 and Types.classification_type_categ_code = 'PRODUCT_FISCAL_CLASS'
3163 --and Types.record_type_code = 'MIGRATED'
3164 and l.installed_flag in ('I', 'B')
3165 and NOT EXISTS -- this condition makes sure we dont duplicate data
3166 (select NULL from ZX_FC_TYPES_TL T
3167 where T.CLASSIFICATION_TYPE_ID = Types.CLASSIFICATION_TYPE_ID
3168 and T.LANGUAGE = L.LANGUAGE_CODE)
3169 );
3170
3171 -- Insert records into Determining Factors table
3172
3173 INSERT INTO ZX_DETERMINING_FACTORS_B (
3174 DETERMINING_FACTOR_ID,
3175 DETERMINING_FACTOR_CODE,
3176 DETERMINING_FACTOR_CLASS_CODE,
3177 VALUE_SET,
3178 TAX_PARAMETER_CODE,
3179 DATA_TYPE_CODE,
3180 TAX_FUNCTION_CODE,
3181 RECORD_TYPE_CODE,
3182 TAX_REGIME_DET_FLAG,
3183 TAX_SUMMARIZATION_FLAG,
3184 TAX_RULES_FLAG,
3185 TAXABLE_BASIS_FLAG,
3186 TAX_CALCULATION_FLAG,
3187 INTERNAL_FLAG,
3188 RECORD_ONLY_FLAG,
3189 REQUEST_ID,
3190 CREATION_DATE,
3191 CREATED_BY,
3192 LAST_UPDATE_DATE,
3193 LAST_UPDATED_BY,
3194 LAST_UPDATE_LOGIN,
3195 OBJECT_VERSION_NUMBER)
3196
3197 (SELECT Zx_Determining_Factors_B_S.nextval,
3198 p_classification_type_code,
3199 'PRODUCT_FISCAL_CLASS',
3200 NULL,
3201 NULL,
3202 'ALPHANUMERIC',
3203 NULL,
3204 'MIGRATED',
3205 'N', --TAX_REGIME_DET_FLAG
3209 'N', --TAX_CALCULATION_FLAG
3206 'Y', --TAX_SUMMARIZATION_FLAG
3207 'Y', --TAX_RULES_FLAG
3208 'N', --TAXABLE_BASIS_FLAG
3210 'Y', --INTERNAL_FLAG
3211 'N', --RECORD_ONLY_FLAG
3212 NULL,
3213 SYSDATE,
3214 FND_GLOBAL.USER_ID,
3215 SYSDATE,
3216 FND_GLOBAL.USER_ID,
3217 FND_GLOBAL.CONC_LOGIN_ID,
3218 1
3219 FROM dual
3220 WHERE NOT EXISTS
3221 (SELECT null from ZX_DETERMINING_FACTORS_B
3222 WHERE DETERMINING_FACTOR_CODE = p_classification_type_code
3223 AND DETERMINING_FACTOR_CLASS_CODE = 'PRODUCT_FISCAL_CLASS')
3224 );
3225
3226 INSERT INTO ZX_DET_FACTORS_TL (
3227 DETERMINING_FACTOR_NAME,
3228 DETERMINING_FACTOR_DESC,
3229 CREATION_DATE,
3230 CREATED_BY,
3231 LAST_UPDATE_DATE,
3232 LAST_UPDATED_BY,
3233 LAST_UPDATE_LOGIN,
3234 DETERMINING_FACTOR_ID,
3235 LANGUAGE,
3236 SOURCE_LANG)
3237 (SELECT
3238 CASE WHEN p_classification_type_name = UPPER(p_classification_type_name)
3239 THEN Initcap(p_classification_type_name)
3240 ELSE
3241 p_classification_type_name
3242 END,
3243 NULL,
3244 SYSDATE,
3245 FND_GLOBAL.USER_ID,
3246 SYSDATE,
3247 FND_GLOBAL.USER_ID,
3248 FND_GLOBAL.CONC_LOGIN_ID,
3249 detFactors.DETERMINING_FACTOR_ID,
3250 L.LANGUAGE_CODE,
3251 userenv('LANG')
3252
3253 FROM FND_LANGUAGES L,
3254 ZX_DETERMINING_FACTORS_B detFactors
3255 WHERE
3256 detFactors.DETERMINING_FACTOR_CODE = p_classification_type_code
3257 and detFactors.DETERMINING_FACTOR_CLASS_CODE = 'PRODUCT_FISCAL_CLASS'
3258 --and detFactors.RECORD_TYPE_CODE = 'MIGRATED'
3259 and l.installed_flag in ('I', 'B')
3260 and NOT EXISTS -- this condition makes sure we dont duplicate data
3261 (select NULL from ZX_DET_FACTORS_TL T
3262 where T.DETERMINING_FACTOR_ID = detFactors.DETERMINING_FACTOR_ID
3263 and T.LANGUAGE = L.LANGUAGE_CODE)
3264 );
3265
3266
3267 END FC_TYPE_INSERT;
3268
3269 PROCEDURE FC_PARTY_TYPE_INSERT(
3270 p_classification_type_code IN ZX_FC_TYPES_B.CLASSIFICATION_TYPE_CODE%TYPE,
3271 p_classification_type_name IN ZX_FC_TYPES_TL.CLASSIFICATION_TYPE_NAME%TYPE,
3272 p_tca_class IN VARCHAR2) IS
3273
3274 BEGIN
3275 -- Get the Party Classification ID
3276
3277 arp_util_tax.debug('Creating the party fiscal classification types ');
3278
3279 arp_util_tax.debug('p_classification_type_code = ' || p_classification_type_code);
3280
3281 INSERT
3282 INTO ZX_FC_TYPES_B(
3283 classification_type_id,
3284 classification_type_code,
3285 classification_type_categ_code,
3286 effective_from,
3287 effective_to,
3288 classification_type_level_code,
3289 owner_table_code,
3290 owner_id_char,
3291 start_position,
3292 num_characters,
3293 record_type_code,
3294 created_by,
3295 creation_date,
3296 last_updated_by,
3297 last_update_date,
3298 last_update_login,
3299 object_version_number)
3300 (SELECT
3301 zx_fc_types_b_s.nextval,
3302 p_classification_type_code,
3303 'PARTY_FISCAL_CLASS',
3304 sysdate,
3305 null,
3306 1,
3307 'HZ_CLASS_CATEGORY',
3308 p_tca_class,
3309 NULL,
3310 NULL,
3311 'MIGRATED',
3312 fnd_global.user_id,
3313 SYSDATE,
3314 fnd_global.user_id,
3315 SYSDATE,
3316 FND_GLOBAL.CONC_LOGIN_ID,
3317 1
3318 FROM dual
3319 WHERE NOT EXISTS
3320 (SELECT null from ZX_FC_TYPES_B
3321 WHERE classification_type_code = p_classification_type_code
3322 and classification_type_categ_code = 'PARTY_FISCAL_CLASS')
3323 );
3324
3325
3326 INSERT
3327 INTO ZX_FC_TYPES_TL(
3328 CLASSIFICATION_TYPE_ID,
3329 CLASSIFICATION_TYPE_NAME,
3330 CREATED_BY,
3331 CREATION_DATE,
3332 LAST_UPDATED_BY,
3333 LAST_UPDATE_DATE,
3334 LAST_UPDATE_LOGIN,
3335 LANGUAGE,
3336 SOURCE_LANG)
3337 (select
3338 classification_type_id,
3339 CASE WHEN p_classification_type_name = UPPER(p_classification_type_name)
3340 THEN Initcap(p_classification_type_name)
3341 ELSE
3342 p_classification_type_name
3343 END,
3344 fnd_global.user_id,
3345 SYSDATE,
3346 fnd_global.user_id,
3347 SYSDATE,
3348 FND_GLOBAL.CONC_LOGIN_ID,
3349 language_code,
3350 userenv('LANG')
3351
3352 FROM FND_LANGUAGES L,
3353 ZX_FC_TYPES_B Types
3354 where
3355 Types.classification_type_code = p_classification_type_code
3356 and Types.classification_type_categ_code = 'PARTY_FISCAL_CLASS'
3357 --and Types.record_type_code = 'MIGRATED'
3358 and l.installed_flag in ('I', 'B')
3359 and NOT EXISTS -- this condition makes sure we dont duplicate data
3360 (select NULL from ZX_FC_TYPES_TL T
3364
3361 where T.CLASSIFICATION_TYPE_ID = Types.CLASSIFICATION_TYPE_ID
3362 and T.LANGUAGE = L.LANGUAGE_CODE)
3363 );
3365 -- Insert records into Determining Factors table
3366
3367 INSERT INTO ZX_DETERMINING_FACTORS_B (
3368 DETERMINING_FACTOR_ID,
3369 DETERMINING_FACTOR_CODE,
3370 DETERMINING_FACTOR_CLASS_CODE,
3371 VALUE_SET,
3372 TAX_PARAMETER_CODE,
3373 DATA_TYPE_CODE,
3374 TAX_FUNCTION_CODE,
3375 RECORD_TYPE_CODE,
3376 TAX_REGIME_DET_FLAG,
3377 TAX_SUMMARIZATION_FLAG,
3378 TAX_RULES_FLAG,
3379 TAXABLE_BASIS_FLAG,
3380 TAX_CALCULATION_FLAG,
3381 INTERNAL_FLAG,
3382 RECORD_ONLY_FLAG,
3383 REQUEST_ID,
3384 CREATION_DATE,
3385 CREATED_BY,
3386 LAST_UPDATE_DATE,
3387 LAST_UPDATED_BY,
3388 LAST_UPDATE_LOGIN,
3389 OBJECT_VERSION_NUMBER)
3390
3391 (SELECT Zx_Determining_Factors_B_S.nextval,
3392 p_classification_type_code,
3393 'PARTY_FISCAL_CLASS',
3394 NULL,
3395 NULL,
3396 'ALPHANUMERIC',
3397 NULL,
3398 'MIGRATED',
3399 'N', --TAX_REGIME_DET_FLAG
3400 'Y', --TAX_SUMMARIZATION_FLAG
3401 'Y', --TAX_RULES_FLAG
3402 'N', --TAXABLE_BASIS_FLAG
3403 'N', --TAX_CALCULATION_FLAG
3404 'Y', --INTERNAL_FLAG
3405 'N', --RECORD_ONLY_FLAG
3406 NULL,
3407 SYSDATE,
3408 FND_GLOBAL.USER_ID,
3409 SYSDATE,
3410 FND_GLOBAL.USER_ID,
3411 FND_GLOBAL.CONC_LOGIN_ID,
3412 1
3413 FROM dual
3414 WHERE NOT EXISTS
3415 (SELECT null from ZX_DETERMINING_FACTORS_B
3416 WHERE DETERMINING_FACTOR_CODE = p_classification_type_code
3417 AND DETERMINING_FACTOR_CLASS_CODE = 'PARTY_FISCAL_CLASS')
3418 );
3419
3420 INSERT INTO ZX_DET_FACTORS_TL (
3421 DETERMINING_FACTOR_NAME,
3422 DETERMINING_FACTOR_DESC,
3423 CREATION_DATE,
3424 CREATED_BY,
3425 LAST_UPDATE_DATE,
3426 LAST_UPDATED_BY,
3427 LAST_UPDATE_LOGIN,
3428 DETERMINING_FACTOR_ID,
3429 LANGUAGE,
3430 SOURCE_LANG)
3431 (SELECT
3432 CASE WHEN p_classification_type_name = UPPER(p_classification_type_name)
3433 THEN Initcap(p_classification_type_name)
3434 ELSE
3435 p_classification_type_name
3436 END,
3437 NULL,
3438 SYSDATE,
3439 FND_GLOBAL.USER_ID,
3440 SYSDATE,
3441 FND_GLOBAL.USER_ID,
3442 FND_GLOBAL.CONC_LOGIN_ID,
3443 detFactors.DETERMINING_FACTOR_ID,
3444 L.LANGUAGE_CODE,
3445 userenv('LANG')
3446 FROM FND_LANGUAGES L,
3447 ZX_DETERMINING_FACTORS_B detFactors
3448 WHERE detFactors.DETERMINING_FACTOR_CODE = p_classification_type_code
3449 and detFactors.DETERMINING_FACTOR_CLASS_CODE = 'PARTY_FISCAL_CLASS'
3450 --and detFactors.RECORD_TYPE_CODE = 'MIGRATED'
3451 and l.installed_flag in ('I', 'B')
3452 and NOT EXISTS -- this condition makes sure we dont duplicate data
3453 (select NULL from ZX_DET_FACTORS_TL T
3454 where T.DETERMINING_FACTOR_ID = detFactors.DETERMINING_FACTOR_ID
3455 and T.LANGUAGE = L.LANGUAGE_CODE)
3456 );
3457
3458
3459 END FC_PARTY_TYPE_INSERT;
3460
3461 /* PROCEDURE USED TO INSERT THE FC TYPES/CODES FOR AP ENTITIES */
3462
3463 PROCEDURE ZX_MIGRATE_AP IS
3464
3465 l_fc_id zx_fc_codes_b.classification_id%type;
3466
3467 l_structure_id mtl_category_sets_b.structure_id%TYPE;
3468 l_category_status VARCHAR2(200);
3469 l_category_set mtl_category_sets_b.Category_set_ID%TYPE;
3470
3471 BEGIN
3472
3473 arp_util_tax.debug('ZX_MIGRATE_AP(+)');
3474
3475 --Bug # 3587896
3476 IF Is_Country_Installed(7004, 'jlarloc') THEN
3477
3478 arp_util_tax.debug('Mapping the INTENDED_USE FC Type to category set value.. ');
3479
3480 Create_Category_Set ('INTENDED_USE',
3481 'Intended Use',
3482 'INTENDED_USE',
3483 'Intended Use');
3484
3485 CREATE_MTL_CATEGORIES('JLZZ_AP_DESTINATION_CODE', 'INTENDED_USE',
3486 l_category_status, l_category_set, l_structure_id);
3487
3488 BEGIN
3489
3490 If Zx_Migrate_Util.IS_INSTALLED('INV') = 'Y' THEN
3491
3492 SELECT category_set_id
3493 Into l_category_set
3494 FROM mtl_category_sets_vl
3495 WHERE category_set_name ='INTENDED_USE';
3496
3497 UPDATE ZX_FC_TYPES_B
3498 SET owner_table_code = 'MTL_CATEGORY_SETS_B',
3499 owner_id_num = l_category_set,
3500 start_position = 1,
3501 num_characters = 400,
3502 last_update_date = sysdate,
3503 last_updated_by = fnd_global.user_id,
3504 object_version_number = object_version_number + 1
3505 WHERE
3506 classification_type_code = 'INTENDED_USE' and
3507 classification_type_categ_code = 'INTENDED_USE_CLASSIFICATION';
3508
3512 WHEN OTHERS THEN
3509 END IF;
3510
3511 EXCEPTION
3513 arp_util_tax.debug('Error while getting category set id for INTENDED_USE ');
3514 END;
3515
3516 END IF;-- Argentina Installed
3517
3518 -- If Poland is installed then
3519 IF Is_Country_Installed(7002, 'jeplloc') THEN
3520
3521 /* Call Inventory Item Categories BULK API */
3522 arp_util_tax.debug( 'Calling the inventory item category BULK API ');
3523 Create_Category_Sets;
3524
3525 CREATE_MTL_CATEGORIES('JGZZ_STATISTICAL_CODE', 'STATISTICAL_CODE',l_category_status, l_category_set, l_structure_id);
3526
3527 /* Create Association to items Moved to zxitemcatmig.sql */
3528
3529 /* Regime Association to Fiscal Type */
3530
3531 INSERT ALL INTO ZX_FC_TYPES_REG_ASSOC
3532 (Tax_regime_code,
3533 Classification_type_code,
3534 effective_from,
3535 effective_to,
3536 Record_type_code,
3537 created_by,
3538 creation_date,
3539 last_updated_by,
3540 last_update_date,
3541 last_update_login,
3542 classif_regime_id,
3543 object_version_number)
3544 VALUES
3545 (TAX_REGIME_CODE,
3546 'STATISTICAL_CODE',
3547 SYSDATE,
3548 NULL,
3549 'MIGRATED',
3550 fnd_global.user_id,
3551 SYSDATE,
3552 fnd_global.user_id,
3553 SYSDATE,
3554 FND_GLOBAL.CONC_LOGIN_ID,
3555 zx_fc_types_reg_assoc_s.NEXTVAL,
3556 1)
3557 SELECT
3558 unique tax_regime_code
3559 FROM ZX_RATES_B rates,
3560 AP_TAX_CODES_ALL codes
3561 WHERE
3562 codes.tax_id = nvl(rates.source_id, rates.tax_rate_id) and
3563 codes.global_attribute_category = 'JE.PL.APXTADTC.TAX_ORIGIN' and
3564 rates.record_type_code = 'MIGRATED' and
3565 not exists
3566 (select null from ZX_FC_TYPES_REG_ASSOC
3567 where classification_type_code = 'STATISTICAL_CODE' and
3568 tax_regime_code = rates.tax_regime_code);
3569
3570 END IF; -- End of Poland checking
3571
3572
3573 -- If Spain is installed then
3574 IF Is_Country_Installed(7002, 'jeesloc') THEN
3575
3576 /* Create the Second Level of Classification Codes */
3577 arp_util_tax.debug( 'Create the Second Level of Classification Codes : SPAIN ');
3578 FC_CODE_GDF_INSERT('INVOICE TYPE','Invoice Type','ES','JEES_INVOICE_CATEGORY', 'PURCHASE_TRANSACTION','MIGRATED');
3579 -- Bug # 5219856
3580 FC_CODE_GDF_INSERT('INVOICE TYPE','Invoice Type','ES','JEES_INVOICE_CATEGORY', 'SALES_TRANSACTION','MIGRATED');
3581
3582 END IF;
3583
3584
3585 -- If France is installed then
3586 IF Is_Country_Installed(7002, 'jefrloc') THEN
3587
3588 arp_util_tax.debug( 'Create the First Level of Classification Codes : FRANCE ');
3589
3590 OPEN G_C_GET_TYPES_INFO('DOCUMENT_SUBTYPE');
3591
3592 FETCH G_C_GET_TYPES_INFO INTO
3593 G_CLASSIFICATION_TYPE_ID,
3594 G_CLASSIFICATION_TYPE_CODE,
3595 G_CLASSIFICATION_TYPE_NAME,
3596 G_CLASSIFICATION_TYP_CATEG_COD,
3597 G_DELIMITER;
3598
3599 CLOSE G_C_GET_TYPES_INFO;
3600
3601 FIRST_LEVEL_FC_CODE_INSERT('DOCUMENT_SUBTYPE','DEDUCTION TAX RULE','Deduction Tax Rule','FR',l_fc_id);
3602
3603 /*
3604 Create Codes (level 2) under the Document Subtype for France Fiscal Type.
3605 */
3606
3607 arp_util_tax.debug( 'Create the Document Subtype for Fiscal Type : FRANCE ');
3608
3609 INSERT
3610 INTO ZX_FC_CODES_B (
3611 classification_type_code,
3612 classification_id,
3613 classification_code,
3614 effective_from,
3615 effective_to,
3616 parent_classification_code,
3617 parent_classification_id,
3618 country_code,
3619 record_type_code,
3620 created_by,
3621 creation_date,
3622 last_updated_by,
3623 last_update_date,
3624 last_update_login,
3625 object_version_number)
3626 SELECT
3627 'DOCUMENT_SUBTYPE',
3628 zx_fc_codes_b_s.nextval,
3629 flex_value,
3630 nvl(start_date_active,to_date('01/01/1951','DD/MM/YYYY')),
3631 end_date_active,
3632 'DEDUCTION TAX RULE',--parent_classification_code
3633 l_fc_id,--parent_classification_id
3634 'FR',
3635 'MIGRATED',
3636 fnd_global.user_id,
3637 SYSDATE,
3638 fnd_global.user_id,
3639 SYSDATE,
3640 FND_GLOBAL.CONC_LOGIN_ID,
3641 1
3642 FROM
3643 FND_FLEX_VALUES V,
3644 fnd_flex_value_sets vs
3645 WHERE v.FLEX_VALUE_SET_ID = vs.FLEX_VALUE_SET_id
3646 AND vs.FLEX_VALUE_SET_NAME ='JE_FR_TAX_RULE'
3647 AND v.enabled_flag = 'Y'
3648 AND NOT EXISTS
3649 (select NULL from ZX_FC_CODES_B Codes where
3650 codes.classification_type_code = 'DOCUMENT_SUBTYPE'
3651 and codes.parent_classification_id =
3652 nvl(l_fc_id,codes.parent_classification_id)
3653 and codes.classification_code = v.flex_value
3654 );
3655
3656 INSERT ALL
3657 INTO ZX_FC_CODES_TL(
3658 CLASSIFICATION_ID,
3659 CLASSIFICATION_NAME,
3660 CREATED_BY,
3661 CREATION_DATE,
3662 LAST_UPDATED_BY,
3663 LAST_UPDATE_DATE,
3664 LAST_UPDATE_LOGIN,
3665 LANGUAGE,
3666 SOURCE_LANG)
3670 THEN Initcap(flex_value)
3667 VALUES
3668 (classification_id,
3669 CASE WHEN flex_value = UPPER(flex_value)
3671 ELSE
3672 flex_value
3673 END,
3674 fnd_global.user_id,
3675 SYSDATE,
3676 fnd_global.user_id,
3677 SYSDATE,
3678 FND_GLOBAL.CONC_LOGIN_ID,
3679 lang_code,
3680 userenv('LANG'))
3681 INTO ZX_FC_CODES_DENORM_B
3682 (CLASSIFICATION_TYPE_ID,
3683 CLASSIFICATION_TYPE_CODE,
3684 CLASSIFICATION_TYPE_NAME,
3685 CLASSIFICATION_TYPE_CATEG_CODE,
3686 CLASSIFICATION_ID,
3687 CLASSIFICATION_CODE,
3688 CLASSIFICATION_NAME,
3689 LANGUAGE,
3690 EFFECTIVE_FROM,
3691 EFFECTIVE_TO,
3692 ENABLED_FLAG,
3693 ANCESTOR_ID,
3694 ANCESTOR_CODE,
3695 ANCESTOR_NAME,
3696 CONCAT_CLASSIF_CODE,
3697 CONCAT_CLASSIF_NAME,
3698 CLASSIFICATION_CODE_LEVEL,
3699 COUNTRY_CODE,
3700 SEGMENT1,
3701 SEGMENT2,
3702 SEGMENT3,
3703 SEGMENT4,
3704 SEGMENT5,
3705 SEGMENT6,
3706 SEGMENT7,
3707 SEGMENT8,
3708 SEGMENT9,
3709 SEGMENT10,
3710 SEGMENT1_NAME,
3711 SEGMENT2_NAME,
3712 SEGMENT3_NAME,
3713 SEGMENT4_NAME,
3714 SEGMENT5_NAME,
3715 SEGMENT6_NAME,
3716 SEGMENT7_NAME,
3717 SEGMENT8_NAME,
3718 SEGMENT9_NAME,
3719 SEGMENT10_NAME,
3720 CREATED_BY,
3721 CREATION_DATE,
3722 LAST_UPDATED_BY,
3723 LAST_UPDATE_LOGIN,
3724 LAST_UPDATE_DATE,
3725 REQUEST_ID,
3726 PROGRAM_ID,
3727 PROGRAM_APPLICATION_ID,
3728 PROGRAM_LOGIN_ID,
3729 RECORD_TYPE_CODE)
3730 VALUES (
3731 G_CLASSIFICATION_TYPE_ID,
3732 G_CLASSIFICATION_TYPE_CODE,
3733 G_CLASSIFICATION_TYPE_NAME,
3734 G_CLASSIFICATION_TYP_CATEG_COD,
3735 classification_id,
3736 flex_value,
3737 flex_value,
3738 lang_code,
3739 start_date_active,
3740 end_date_active,
3741 enabled_flag,
3742 l_fc_id,
3743 'DEDUCTION TAX RULE',
3744 'Deduction Tax Rule',
3745 'DEDUCTION TAX RULE' || G_DELIMITER || flex_value,
3746 'Deduction Tax Rule' || G_DELIMITER || flex_value,
3747 2,
3748 'FR',
3749 'DEDUCTION TAX RULE',
3750 flex_value,
3751 Null,
3752 Null,
3753 Null,
3754 Null,
3755 Null,
3756 Null,
3757 Null,
3758 Null,
3759 'Deduction Tax Rule',
3760 flex_value,
3761 Null,
3762 Null,
3763 Null,
3764 Null,
3765 Null,
3766 Null,
3767 Null,
3768 Null,
3769 fnd_global.user_id,
3770 SYSDATE,
3771 fnd_global.user_id,
3772 FND_GLOBAL.CONC_LOGIN_ID,
3773 sysdate,
3774 FND_GLOBAL.CONC_REQUEST_ID,
3775 fnd_global.CONC_PROGRAM_ID,
3776 235,
3777 FND_GLOBAL.CONC_LOGIN_ID,
3778 'MIGRATED')
3779 SELECT
3780 flex_value,
3781 nvl(start_date_active,to_date('01/01/1951','DD/MM/YYYY')) start_date_active,
3782 end_date_active,
3783 Codes.classification_id,
3784 L.language_code lang_code,
3785 v.enabled_flag
3786 FROM
3787 ZX_FC_CODES_B Codes,
3788 FND_FLEX_VALUES V,
3789 fnd_flex_value_sets vs,
3790 FND_LANGUAGES L
3791
3792 WHERE
3793 codes.classification_type_code = G_CLASSIFICATION_TYPE_CODE
3794 AND codes.parent_classification_id = l_fc_id
3795 AND codes.classification_code = v.flex_value
3796 AND Codes.RECORD_TYPE_CODE IN('MIGRATED', 'SEEDED')
3797 and v.FLEX_VALUE_SET_ID = vs.FLEX_VALUE_SET_id
3798 and vs.FLEX_VALUE_SET_NAME ='JE_FR_TAX_RULE'
3799 AND v.enabled_flag = 'Y'
3800 AND L.INSTALLED_FLAG in ('I', 'B')
3801 AND NOT EXISTS -- this condition makes sure we dont duplicate data
3802 (select NULL from ZX_FC_CODES_DENORM_B codes
3803 where
3804 codes.classification_type_code = G_CLASSIFICATION_TYPE_CODE
3805 and codes.classification_code = v.flex_value
3806 and codes.ancestor_id = nvl(l_fc_id,codes.ancestor_id)
3807 and codes.language = l.language_code);
3808
3809 END IF; -- End of France Checking
3810
3811 -- If Chile is installed then
3812 IF Is_Country_Installed(7004, 'jlclloc') THEN
3813
3814 -- Begin for CHILE..
3815 /* Create rows for Parent Records */
3816
3817 arp_util_tax.debug( 'Create rows for Parent Records for country : CHILE ');
3818
3819 OPEN G_C_GET_TYPES_INFO('DOCUMENT_SUBTYPE');
3820
3821 FETCH G_C_GET_TYPES_INFO INTO
3822 G_CLASSIFICATION_TYPE_ID,
3823 G_CLASSIFICATION_TYPE_CODE,
3824 G_CLASSIFICATION_TYPE_NAME,
3825 G_CLASSIFICATION_TYP_CATEG_COD,
3826 G_DELIMITER;
3827
3828 CLOSE G_C_GET_TYPES_INFO;
3829 FIRST_LEVEL_FC_CODE_INSERT('DOCUMENT_SUBTYPE','DOCUMENT TYPE','Document Type','CL',l_fc_id);
3830
3831 /* Create a Code under the Document Subtype Fiscal Type. */
3832
3833 arp_util_tax.debug( 'Create a Code under the Document Subtype Fiscal Type for country : CHILE ');
3834
3835 INSERT
3836 INTO ZX_FC_CODES_B (
3837 classification_type_code,
3838 classification_id,
3839 classification_code,
3840 effective_from,
3841 effective_to,
3845 record_type_code,
3842 parent_classification_code,
3843 parent_classification_id,
3844 country_code,
3846 created_by,
3847 creation_date,
3848 last_updated_by,
3849 last_update_date,
3850 last_update_login,
3851 object_version_number)
3852 SELECT
3853 'DOCUMENT_SUBTYPE',
3854 zx_fc_codes_b_s.nextval,
3855 lookup_code,
3856 nvl(start_date_active,to_date('01/01/1951','DD/MM/YYYY')),
3857 end_date_active,
3858 'DOCUMENT TYPE',--parent_classification_code
3859 l_fc_id, --parent_classification_id
3860 'CL',
3861 'MIGRATED',
3862 fnd_global.user_id,
3863 SYSDATE,
3864 fnd_global.user_id,
3865 SYSDATE,
3866 FND_GLOBAL.CONC_LOGIN_ID,
3867 1
3868
3869 FROM FND_LOOKUPS lookups
3870 WHERE
3871
3872 lookups.lookup_type='JLCL_AP_DOCUMENT_TYPE'
3873 AND NOT EXISTS -- this condition makes sure we dont duplicate data
3874 (select NULL from ZX_FC_CODES_B Codes where
3875 codes.classification_type_code = 'DOCUMENT_SUBTYPE'
3876 and codes.parent_classification_id = nvl(l_fc_id,codes.parent_classification_id)
3877 and codes.classification_code = lookups.lookup_code
3878 );
3879
3880 INSERT ALL
3881 INTO ZX_FC_CODES_TL(
3882
3883 CLASSIFICATION_ID,
3884 CLASSIFICATION_NAME,
3885 CREATED_BY,
3886 CREATION_DATE,
3887 LAST_UPDATED_BY,
3888 LAST_UPDATE_DATE,
3889 LAST_UPDATE_LOGIN,
3890 LANGUAGE,
3891 SOURCE_LANG)
3892 VALUES
3893 (classification_id,
3894 meaning,
3895 fnd_global.user_id,
3896 SYSDATE,
3897 fnd_global.user_id,
3898 SYSDATE,
3899 FND_GLOBAL.CONC_LOGIN_ID,
3900 language,
3901 source_lang)
3902
3903 INTO ZX_FC_CODES_DENORM_B
3904
3905 (CLASSIFICATION_TYPE_ID,
3906 CLASSIFICATION_TYPE_CODE,
3907 CLASSIFICATION_TYPE_NAME,
3908 CLASSIFICATION_TYPE_CATEG_CODE,
3909 CLASSIFICATION_ID,
3910 CLASSIFICATION_CODE,
3911 CLASSIFICATION_NAME,
3912 LANGUAGE,
3913 EFFECTIVE_FROM,
3914 EFFECTIVE_TO,
3915 ENABLED_FLAG,
3916 ANCESTOR_ID,
3917 ANCESTOR_CODE,
3918 ANCESTOR_NAME,
3919 CONCAT_CLASSIF_CODE,
3920 CONCAT_CLASSIF_NAME,
3921 CLASSIFICATION_CODE_LEVEL,
3922 COUNTRY_CODE,
3923 SEGMENT1,
3924 SEGMENT2,
3925 SEGMENT3,
3926 SEGMENT4,
3927 SEGMENT5,
3928 SEGMENT6,
3929 SEGMENT7,
3930 SEGMENT8,
3931 SEGMENT9,
3932 SEGMENT10,
3933 SEGMENT1_NAME,
3934 SEGMENT2_NAME,
3935 SEGMENT3_NAME,
3936 SEGMENT4_NAME,
3937 SEGMENT5_NAME,
3938 SEGMENT6_NAME,
3939 SEGMENT7_NAME,
3940 SEGMENT8_NAME,
3941 SEGMENT9_NAME,
3942 SEGMENT10_NAME,
3943 CREATED_BY,
3944 CREATION_DATE,
3945 LAST_UPDATED_BY,
3946 LAST_UPDATE_LOGIN,
3947 LAST_UPDATE_DATE,
3948 REQUEST_ID,
3949 PROGRAM_ID,
3950 PROGRAM_APPLICATION_ID,
3951 PROGRAM_LOGIN_ID,
3952 RECORD_TYPE_CODE)
3953 VALUES (
3954 G_CLASSIFICATION_TYPE_ID,
3955 G_CLASSIFICATION_TYPE_CODE,
3956 G_CLASSIFICATION_TYPE_NAME,
3957 G_CLASSIFICATION_TYP_CATEG_COD,
3958 classification_id,
3959 lookup_code,
3960 Meaning,
3961 language,
3962 start_date_active,
3963 end_date_active,
3964 enabled_flag,
3965 l_fc_id,
3966 'DOCUMENT TYPE',
3967 'Document Type',
3968 'DOCUMENT TYPE' || G_DELIMITER || lookup_code,
3969 'Document Type' || G_DELIMITER || Meaning,
3970 2,
3971 'CL',
3972 'DOCUMENT TYPE',
3973 lookup_code,
3974 Null,
3975 Null,
3976 Null,
3977 Null,
3978 Null,
3979 Null,
3980 Null,
3981 Null,
3982 'Document Type',
3983 Meaning,
3984 Null,
3985 Null,
3986 Null,
3987 Null,
3988 Null,
3989 Null,
3990 Null,
3991 Null,
3992 fnd_global.user_id,
3993 SYSDATE,
3994 fnd_global.user_id,
3995 FND_GLOBAL.CONC_LOGIN_ID,
3996 sysdate,
3997 FND_GLOBAL.CONC_REQUEST_ID,
3998 fnd_global.CONC_PROGRAM_ID,
3999 235,
4000 FND_GLOBAL.CONC_LOGIN_ID,
4001 'MIGRATED')
4002 SELECT
4003 lookup_code,
4004 meaning,
4005 nvl(start_date_active,to_date('01/01/1951','DD/MM/YYYY')) start_date_active,
4006 end_date_active,
4007 source_lang,
4008 language,
4009 lv.enabled_flag,
4010 classification_id
4011 FROM
4012 ZX_FC_CODES_B codes,
4013 FND_LOOKUP_VALUES LV,
4014 FND_LANGUAGES L
4015 WHERE
4016 codes.classification_type_code = G_CLASSIFICATION_TYPE_CODE
4017 AND codes.parent_classification_id = nvl(l_fc_id,parent_classification_id)
4018 AND Codes.classification_code = LV.lookup_code
4019 AND (Codes.RECORD_TYPE_CODE = 'MIGRATED' OR Codes.RECORD_TYPE_CODE = 'SEEDED')
4020 AND VIEW_APPLICATION_ID = 0
4021 AND SECURITY_GROUP_ID = 0
4022 AND Lookup_type= 'JLCL_AP_DOCUMENT_TYPE'
4023 AND LV.LANGUAGE=L.LANGUAGE_CODE(+)
4024 AND L.INSTALLED_FLAG in ('I', 'B')
4028 where
4025 AND NOT EXISTS -- this condition makes sure we dont duplicate data
4026 (select NULL
4027 from ZX_FC_CODES_DENORM_B codes
4029 codes.classification_type_code = G_CLASSIFICATION_TYPE_CODE
4030 and codes.classification_code = lv.lookup_code
4031 and codes.ancestor_id = nvl(l_fc_id,codes.ancestor_id)
4032 and codes.language = l.language_code);
4033
4034 END IF; -- End for Chile checking
4035
4036 arp_util_tax.debug('ZX_MIGRATE_AP(-)');
4037
4038 END ZX_MIGRATE_AP;
4039
4040
4041 /* PROCEDURE USED TO INSERT THE FC TYPES/CODES FOR AR ENTITIES */
4042
4043 PROCEDURE ZX_MIGRATE_AR IS
4044
4045 BEGIN
4046 arp_util_tax.debug('ZX_MIGRATE_AR(+)');
4047
4048 -- If Hungary is installed then
4049 IF Is_Country_Installed(7002, 'jehuloc') THEN
4050
4051 /* Create Association to items for 'STATISTICAL_CODE',*/
4052 -- Call the common procedure to associate the items for 'JE.HU.INVIDITM.STAT_CODE'
4053 -- this one done for items and regimes
4054 Associate_items('JE.HU.INVIDITM.STAT_CODE');
4055
4056 -- Call the common procedure to associate the items for 'JE.HU.ARXSTDML.STAT_CODE.
4057 -- i.e For AR Credit Memo Lines
4058
4059 -- this one done for items and regimes ???
4060 Associate_items('JE.HU.ARXSTDML.STAT_CODE');
4061
4062 END IF;
4063
4064 -- If Poland is installed then
4065 IF Is_Country_Installed(7002, 'jeplloc') THEN
4066
4067 /*
4068 Statistical Code is a shared Classification, then when this Type Code is migrated for Hungary ,
4069 we are also migrating for Poland, however the association needs to be performed for PL as well.
4070 */
4071
4072 --Call the common procedure to associate the items for 'JE.PL.INVIDITM.STAT_CODE'
4073 -- this one done for items and regimes
4074 Associate_items('JE.PL.INVIDITM.STAT_CODE');
4075
4076 --Call the common procedure to associate the items for 'JE. PL.ARXSTDML.STAT_CODE.
4077 --i.e For AR Credit Memo Lines
4078
4079 -- this one done for items and regimes ??? ???
4080 Associate_items('JE.PL.ARXSTDML.STAT_CODE');
4081
4082 END IF;
4083
4084 -- Create Regime Association for 'FISCAL CLASSIFICATION CODE'
4085 INSERT ALL INTO
4086 ZX_FC_TYPES_REG_ASSOC
4087 (Tax_regime_code,
4088 classification_type_code,
4089 effective_FROM,
4090 effective_to,
4091 record_type_code,
4092 created_by,
4093 creation_date,
4094 last_updated_by,
4095 last_update_date,
4096 last_update_login,
4097 classif_regime_id,
4098 object_version_number)
4099 VALUES
4100 (
4101 tax_regime_code,
4102 'FISCAL_CLASSIFICATION',
4103 SYSDATE,
4104 NULL,
4105 'MIGRATED',
4106 fnd_global.user_id,
4107 SYSDATE,
4108 fnd_global.user_id,
4109 SYSDATE,
4110 FND_GLOBAL.CONC_LOGIN_ID,
4111 zx_fc_types_reg_assoc_s.NEXTVAL,
4112 1)
4113
4114 SELECT unique
4115 tax_regime_code
4116 FROM
4117 zx_rates_b rates,
4118 zx_party_tax_profile ptp
4119 WHERE
4120 rates.content_owner_id = ptp.party_tax_profile_id and
4121 ptp.party_type_code = 'OU' and
4122 ptp.party_id in
4123 (SELECT unique decode(l_multi_org_flag,'N',l_org_id,org_id)
4124 FROM ar_system_parameters_all
4125 WHERE global_attribute_category in ('JL.CO.ARXSYSPA.SYS_PARAMETERS',
4126 'JL.BR.ARXSYSPA.Additional Info','JL.AR.ARXSYSPA.SYS_PARAMETERS')) and
4127 not exists
4128 (select null from ZX_FC_TYPES_REG_ASSOC
4129 where classification_type_code = 'FISCAL_CLASSIFICATION' and
4130 tax_regime_code = rates.tax_regime_code);
4131
4132 -- Create the Second Level of Classification Codes for Transaction Condition Class
4133 arp_util_tax.debug( 'Create the Second Level of Classification Codes for Transaction Condition Class');
4134 INSERT
4135 INTO ZX_FC_CODES_B
4136 (classification_type_code,
4137 classification_id,
4138 classification_code,
4139 effective_from,
4140 effective_to,
4141 parent_classification_code,
4142 parent_classification_id,
4143 country_code,
4144 record_type_code,
4145 created_by,
4146 creation_date,
4147 last_updated_by,
4148 last_update_date,
4149 last_update_login,
4150 object_version_number)
4151 SELECT
4152 'TRX_BUSINESS_CATEGORY', --classification_type_code
4153 zx_fc_codes_b_s.nextval, --classification_id
4154 lookups.fc_code, --classification_code
4155 lookups.effective_from, --effective_from
4156 lookups.effective_to, --effective_to
4157 event.tax_event_class_code, --parent_classification_code
4158 fc.classification_id, --parent_classification_id
4159 NULL, --country_code is null to share AR,BR,CO country
4160 'MIGRATED', --record_type_code
4161 fnd_global.user_id,
4162 sysdate,
4163 fnd_global.user_id,
4164 sysdate,
4165 fnd_global.conc_login_id,
4166 1
4167
4168 FROM
4169
4170 ZX_FC_CODES_B fc,
4171 ZX_EVENT_CLASSES_VL event,
4172 (SELECT
4173 lookups.LOOKUP_CODE fc_code,
4177 WHERE
4174 nvl(START_DATE_ACTIVE,to_date('01/01/1951','DD/MM/YYYY')) effective_from,
4175 END_DATE_ACTIVE effective_to
4176 FROM FND_LOOKUPS lookups
4178 (lookups.LOOKUP_TYPE = 'TRANSACTION_CLASS' or
4179 lookups.LOOKUP_TYPE = 'TRANSACTION_REASON')
4180 ) lookups
4181
4182 WHERE
4183 fc.classification_type_code = 'TRX_BUSINESS_CATEGORY'
4184 and fc.parent_classification_id is null
4185 and fc.classification_code=event.tax_event_class_code
4186 and event.tax_event_class_code = 'SALES_TRANSACTION'
4187 AND NOT EXISTS -- this condition makes sure we dont duplicate data
4188 (select NULL from ZX_FC_CODES_B Codes where
4189 codes.classification_type_code = 'TRX_BUSINESS_CATEGORY'
4190 and codes.parent_classification_id = fc.classification_id
4191 and codes.classification_code = lookups.fc_code
4192 );
4193
4194
4195 INSERT ALL
4196 INTO ZX_FC_CODES_TL
4197 (CLASSIFICATION_ID,
4198 CLASSIFICATION_NAME,
4199 CREATED_BY,
4200 CREATION_DATE,
4201 LAST_UPDATED_BY,
4202 LAST_UPDATE_DATE,
4203 LAST_UPDATE_LOGIN,
4204 LANGUAGE,
4205 SOURCE_LANG)
4206 VALUES(
4207 classification_id,
4208 CASE WHEN fc_name = UPPER(fc_name)
4209 THEN Initcap(fc_name)
4210 ELSE
4211 fc_name
4212 END,
4213 fnd_global.user_id,
4214 sysdate,
4215 fnd_global.user_id,
4216 sysdate,
4217 fnd_global.conc_login_id,
4218 LANGUAGE,
4219 SOURCE_LANGUAGE)
4220
4221 INTO ZX_FC_CODES_DENORM_B(
4222 CLASSIFICATION_TYPE_ID,
4223 CLASSIFICATION_TYPE_CODE,
4224 CLASSIFICATION_TYPE_NAME,
4225 CLASSIFICATION_TYPE_CATEG_CODE,
4226 CLASSIFICATION_ID,
4227 CLASSIFICATION_CODE,
4228 CLASSIFICATION_NAME,
4229 LANGUAGE,
4230 EFFECTIVE_FROM,
4231 EFFECTIVE_TO,
4232 ENABLED_FLAG,
4233 ANCESTOR_ID,
4234 ANCESTOR_CODE,
4235 ANCESTOR_NAME,
4236 CONCAT_CLASSIF_CODE,
4237 CONCAT_CLASSIF_NAME,
4238 CLASSIFICATION_CODE_LEVEL,
4239 COUNTRY_CODE,
4240 SEGMENT1,
4241 SEGMENT2,
4242 SEGMENT3,
4243 SEGMENT4,
4244 SEGMENT5,
4245 SEGMENT6,
4246 SEGMENT7,
4247 SEGMENT8,
4248 SEGMENT9,
4249 SEGMENT10,
4250 SEGMENT1_NAME,
4251 SEGMENT2_NAME,
4252 SEGMENT3_NAME,
4253 SEGMENT4_NAME,
4254 SEGMENT5_NAME,
4255 SEGMENT6_NAME,
4256 SEGMENT7_NAME,
4257 SEGMENT8_NAME,
4258 SEGMENT9_NAME,
4259 SEGMENT10_NAME,
4260 CREATED_BY,
4261 CREATION_DATE,
4262 LAST_UPDATED_BY,
4263 LAST_UPDATE_LOGIN,
4264 LAST_UPDATE_DATE,
4265 REQUEST_ID,
4266 PROGRAM_ID,
4267 PROGRAM_APPLICATION_ID,
4268 PROGRAM_LOGIN_ID,
4269 RECORD_TYPE_CODE)
4270 VALUES (
4271 G_CLASSIFICATION_TYPE_ID,
4272 G_CLASSIFICATION_TYPE_CODE,
4273 G_CLASSIFICATION_TYPE_NAME,
4274 G_CLASSIFICATION_TYP_CATEG_COD,
4275 classification_id, --CLASSIFICATION_ID
4276 fc_code, --CLASSIFICATION_CODE
4277 fc_name, --CLASSIFICATION_NAME
4278 LANGUAGE, --LANGUAGE
4279 effective_from, --EFFECTIVE_FROM
4280 effective_to, --EFFECTIVE_TO
4281 enabled_flag, --ENABLED_FLAG
4282 parent_fc_id, --ANCESTOR_ID
4283 tax_event_class_code, --ANCESTOR_CODE
4284 Name, --ANCESTOR_NAME
4285 tax_event_class_code ||G_DELIMITER ||
4286 fc_code, --CONCAT_CLASSIF_CODE
4287 Name || G_DELIMITER || fc_name, --CONCAT_CLASSIF_NAME
4288 2, --CLASSIFICATION_CODE_LEVEL
4289 NULL, --country_code is null to share AR,BR,CO country
4290 tax_event_class_code, --SEGMENT1
4291 fc_code, --SEGMENT2
4292 Null,
4293 Null,
4294 Null,
4295 Null,
4296 Null,
4297 Null,
4298 Null,
4299 Null,
4300 Name, --SEGMENT1_NAME
4301 fc_name, --SEGMENT2_NAME
4302 Null,
4303 Null,
4304 Null,
4305 Null,
4306 Null,
4307 Null,
4308 Null,
4309 Null,
4310 fnd_global.user_id,
4311 sysdate,
4312 fnd_global.user_id,
4313 fnd_global.conc_login_id,
4314 sysdate,
4315 fnd_global.conc_request_id,
4316 fnd_global.conc_program_id,
4317 235,
4318 fnd_global.conc_login_id,
4319 'MIGRATED')
4320 SELECT
4321 codes.fc_code,
4322 codes.fc_name,
4323 codes.effective_from,
4324 codes.effective_to,
4325 codes.source_language ,
4326 codes.language ,
4327 event.tax_event_class_code,
4328 event.tax_event_class_name name,
4329 fc.classification_id as parent_fc_id,
4330 codes.classification_id,
4331 codes.enabled_flag
4332 FROM
4333
4334 ZX_FC_CODES_B fc,
4335 ZX_EVENT_CLASSES_VL event,
4336
4337 (SELECT
4338 FL.LOOKUP_CODE fc_code,
4339 FL.MEANING fc_name,
4340 nvl(start_date_active, to_date('01/01/1951','DD/MM/YYYY')) effective_from,
4341 END_DATE_ACTIVE effective_to,
4342 FL.SOURCE_LANG source_language,
4343 FL.LANGUAGE language,
4344 Codes.classification_id,
4345 Codes.parent_classification_id,
4349 FND_LOOKUP_VALUES FL,
4346 fl.enabled_flag
4347 FROM
4348 ZX_FC_CODES_b Codes,
4350 FND_LANGUAGES L
4351 WHERE
4352 Codes.classification_type_code = 'TRX_BUSINESS_CATEGORY'
4353 AND Codes.classification_code = FL.lookup_code
4354 AND Codes.RECORD_TYPE_CODE = 'MIGRATED'
4355 AND (FL.LOOKUP_TYPE = 'TRANSACTION_CLASS' OR
4356 FL.LOOKUP_TYPE = 'TRANSACTION REASON')
4357 AND (VIEW_APPLICATION_ID = 0 OR VIEW_APPLICATION_ID=201)
4358 AND SECURITY_GROUP_ID = 0
4359 AND FL.language=L.language_code(+)
4360 AND L.INSTALLED_FLAG in ('I', 'B')
4361 ) codes
4362
4363 WHERE
4364 fc.classification_type_code = 'TRX_BUSINESS_CATEGORY'
4365 and fc.parent_classification_id is null
4366 and fc.classification_code = event.tax_event_class_code
4367 and fc.classification_id = codes.parent_classification_id
4368 and event.tax_event_class_code = 'SALES_TRANSACTION'
4369
4370 AND NOT EXISTS -- this condition makes sure we dont duplicate data
4371 (select NULL from ZX_FC_CODES_DENORM_B denorm
4372 where
4373 denorm.classification_type_code = G_CLASSIFICATION_TYPE_CODE
4374 and denorm.classification_code = codes.fc_code
4375 and denorm.ancestor_id = fc.classification_id
4376 and denorm.language = codes.language);
4377
4378
4379 arp_util_tax.debug('ZX_MIGRATE_AR(-)');
4380
4381 END ZX_MIGRATE_AR;
4382
4383
4384 /*===========================================================================+
4385 | Function: Is_Country_Installed |
4386 | Description: This function returns true if the passed application id |
4387 | and country is installed. |
4388 | |
4389 | ARGUMENTS : Application id, Module Short Name |
4390 | |
4391 | |
4392 | History |
4393 | 28-Sep-04 Venkat Initial Version |
4394 | |
4395 +===========================================================================*/
4396
4397
4398 FUNCTION Is_Country_Installed(
4399 p_application_id IN fnd_module_installations.APPLICATION_ID%TYPE,
4400 p_module_short_name IN fnd_module_installations.MODULE_SHORT_NAME%TYPE
4401 )
4402 RETURN BOOLEAN IS
4403
4404 l_status FND_PRODUCT_INSTALLATIONS.STATUS%TYPE;
4405 l_db_status FND_PRODUCT_INSTALLATIONS.DB_STATUS%TYPE;
4406
4407 BEGIN
4408
4409 arp_util_tax.debug( ' Is_Country_Installed .. (+) ' );
4410
4411 BEGIN
4412 SELECT STATUS, DB_STATUS
4413 into l_status, l_db_status
4414 FROM
4415 FND_MODULE_INSTALLATIONS
4416 WHERE
4417 APPLICATION_ID = p_application_id AND
4418 MODULE_SHORT_NAME = p_module_short_name;
4419 EXCEPTION
4420 WHEN OTHERS THEN
4421 arp_util_tax.debug('Error while getting status and db status value from fnd_module_installations');
4422 END;
4423
4424 IF (nvl(l_status,'N') in ('I','S') or
4425 nvl(l_db_status,'N') in ('I','S')) THEN
4426 return TRUE;
4427 ELSE
4428 return FALSE;
4429 END IF;
4430
4431 arp_util_tax.debug( ' Is_Country_Installed .. (-) ' );
4432
4433 END Is_Country_Installed;
4434
4435
4436 /*===========================================================================+
4437 | Procedure: ZX_GDF_TO_ARMEMO |
4438 | Description: Existing GDFs on memo lines will be migrated to Product |
4439 | Category column of AR memo lines |
4440 | ARGUMENTS : |
4441 | |
4442 | NOTES |
4443 | |
4444 | History |
4445 | |
4446 | 28-Sep-04 Venkat Initial Version |
4447 | |
4448 +===========================================================================*/
4449
4450 PROCEDURE ZX_GDF_TO_ARMEMO_LINES IS
4451
4452 BEGIN
4453 arp_util_tax.debug( ' ZX_GDF_TO_ARMEMO_LINES .. (+) ' );
4454
4455 -- If Hungary is installed then migrate Statistical Code GDF
4456 IF Is_Country_Installed(7002, 'jehuloc') THEN
4457 UPDATE ar_memo_lines_all_b
4458 SET tax_product_category = global_attribute1
4459 WHERE global_attribute_category = 'JE.HU.ARXSTDML.STAT_CODE';
4460 END IF;
4461
4462 -- If Poland is installed then migrate Statistical Code GDF
4463 IF Is_Country_Installed(7002, 'jeplloc') THEN
4464 UPDATE ar_memo_lines_all_b
4468
4465 SET tax_product_category = substrb(global_attribute1,1,48)
4466 WHERE global_attribute_category = 'JE.PL.ARXSTDML.STAT_CODE';
4467 END IF;
4469 -- If Argentina is installed then migrate Fiscal Classification Code GDF
4470 IF Is_Country_Installed(7004, 'jlarloc') THEN
4471 UPDATE ar_memo_lines_all_b
4472 SET tax_product_category = global_attribute1
4473 WHERE global_attribute_category = 'JL.AR.ARXSTDML.AR_MEMO_LINES';
4474 END IF;
4475
4476 -- If Brazil is installed then migrate Fiscal Classification Code GDF
4477 IF Is_Country_Installed(7004, 'jlbrloc')THEN
4478 UPDATE ar_memo_lines_all_b
4479 SET tax_product_category = global_attribute1
4480 WHERE global_attribute_category = 'JL.BR.ARXSDML.Additional';
4481 END IF;
4482
4483 -- If Colombia is installed then migrate Fiscal Classification Code GDF
4484 IF Is_Country_Installed(7004, 'jlcoloc') THEN
4485 UPDATE ar_memo_lines_all_b
4486 SET tax_product_category = global_attribute1
4487 WHERE global_attribute_category = 'JL.CO.ARXSTDML.AR_MEMO_LINES';
4488 END IF;
4489
4490 arp_util_tax.debug( ' ZX_GDF_TO_ARMEMO_LINES .. (-) ' );
4491
4492
4493 END ZX_GDF_TO_ARMEMO_LINES;
4494
4495
4496 /*===========================================================================+
4497 | Procedure: CREATE_SEEDED_FC_TYPES |
4498 | Description: Used to Create the FC Types for fresh install |
4499 | Called from Country Defaults UI |
4500 | ARGUMENTS : Country Code |
4501 | |
4502 | History |
4503 | |
4504 | 23-May-05 Venkat Initial Version |
4505 | |
4506 +===========================================================================*/
4507
4508 PROCEDURE CREATE_SEEDED_FC_TYPES(p_country_code IN VARCHAR2,
4509 x_category_set OUT NOCOPY NUMBER,
4510 x_category_set_name OUT NOCOPY VARCHAR2,
4511 x_return_status OUT NOCOPY VARCHAR2
4512 )
4513 IS
4514
4515 l_structure_id mtl_category_sets_b.structure_id%TYPE;
4516 l_category_status varchar2(200);
4517
4518 BEGIN
4519
4520 x_return_status := 'S';
4521
4522 -- If Inventory is installed then
4523 IF Zx_Migrate_Util.IS_INSTALLED('INV') = 'Y' THEN
4524
4525 IF p_country_code = 'HU' OR p_country_code = 'PL' THEN
4526
4527 -- Call Create Categories
4528 Create_Category_Sets;
4529 CREATE_MTL_CATEGORIES('JGZZ_STATISTICAL_CODE','STATISTICAL_CODE',
4530 l_category_status,x_category_set,l_structure_id);
4531
4532 -- Call a common procedure to create FC Types
4533 FC_TYPE_INSERT('STATISTICAL_CODE','Statistical Code',x_category_set);
4534
4535 END IF;
4536
4537
4538 IF p_country_code = 'AR' OR p_country_code = 'BR' OR p_country_code = 'CO' THEN
4539
4540 Create_Category_Set ('FISCAL_CLASSIFICATION',
4541 'Fiscal Classification',
4542 'FISCAL_CLASSIFICATION',
4543 'Fiscal Classification');
4544 CREATE_MTL_CATEGORIES('JLZZ_AR_TX_FISCAL_CLASS_CODE', 'FISCAL_CLASSIFICATION',
4545 l_category_status,x_category_set, l_structure_id);
4546 -- Call a common procedure to create FC Types
4547 FC_TYPE_INSERT('FISCAL_CLASSIFICATION','Fiscal Classification Code',x_category_set);
4548
4549 END IF;
4550
4551
4552 IF p_country_code = 'TW' THEN
4553
4554 Create_Category_Set ('WINE_CIGARRETE_CATEGORY',
4555 'Wine Cigarrete',
4556 'WINE_CIGARRETE_CATEGORY',
4557 'Wine Cigarrete');
4558 BEGIN
4559 SELECT Category_set_ID INTO x_category_set
4560 FROM mtl_category_sets
4561 WHERE Category_Set_Name ='WINE_CIGARRETE_CATEGORY';
4562 EXCEPTION
4563 WHEN NO_DATA_FOUND THEN
4564 x_category_set := NULL;
4565 END;
4566
4567 IF x_category_set is not null then
4568 -- Call a common procedure to create FC Types
4569 FC_TYPE_INSERT('WINE CIGARETTE','Wine Cigarette',x_category_set);
4570 END IF;
4571
4572 END IF;
4573
4574 IF x_category_set is not null then
4575 SELECT category_set_name INTO x_category_set_name FROM MTL_CATEGORY_SETS_VL
4576 WHERE category_set_id = x_category_set and rownum = 1;
4577 END IF;
4578
4579 -- Update the record type, created by and last updated by values
4580 UPDATE ZX_FC_TYPES_B SET record_type_code = 'SEEDED', created_by = 120,
4581 last_updated_by = 120, last_update_login = 0
4582 WHERE classification_type_code in ('STATISTICAL_CODE', 'FISCAL_CLASSIFICATION', 'WINE CIGARETTE') and
4583 classification_type_categ_code = 'PRODUCT_FISCAL_CLASS';
4584
4585 UPDATE ZX_DETERMINING_FACTORS_B SET record_type_code = 'SEEDED', created_by = 120,
4586 last_updated_by = 120, last_update_login = 0
4587 WHERE determining_factor_code in ('STATISTICAL_CODE', 'FISCAL_CLASSIFICATION', 'WINE CIGARETTE') and
4588 determining_factor_class_code = 'PRODUCT_FISCAL_CLASS';
4589
4590 END IF;
4591
4595
4592 EXCEPTION
4593 WHEN OTHERS THEN
4594 x_return_status := 'E';
4596 END CREATE_SEEDED_FC_TYPES;
4597
4598
4599 PROCEDURE OKL_MIGRATION IS
4600 p_flexfield FND_FLEX_KEY_API.FLEXFIELD_TYPE;
4601 l_structure_id mtl_category_sets_b.structure_id%TYPE;
4602 l_category_status VARCHAR2(200);
4603 l_category_set mtl_category_sets_b.Category_set_ID%TYPE;
4604 l_Inventory_Category_Set mtl_category_sets_vl.Category_set_ID%TYPE;
4605 l_Item_id Number;
4606 l_Item_organization_id Number;
4607 l_record_type zx_Fc_types_b.record_type_code%type;
4608 l_classification_name fnd_lookup_values.meaning%type;
4609 l_lookup_code fnd_lookup_values.lookup_code%type;
4610 l_meaning fnd_lookup_values.meaning%type;
4611 l_language fnd_lookup_values.language%type;
4612 l_start_date_active fnd_lookup_values.start_date_active%type;
4613 l_end_date_active fnd_lookup_values.end_date_active%type;
4614 l_source_lang fnd_lookup_values.source_lang%type;
4615 l_fc_id zx_fc_codes_b.classification_id%type;
4616 l_return_status varchar2(200);
4617 -- l_errorcode number;
4618 -- l_msg_count number;
4619 -- l_MSG_DATA varchar2(200);
4620 p_category_set mtl_category_sets_b.Category_set_ID%TYPE;
4621 p_structure_id FND_FLEX_KEY_API.STRUCTURE_TYPE;
4622 l_segment FND_FLEX_KEY_API.segment_type;
4623 p_StatCode_Segment FND_FLEX_KEY_API.segment_type;
4624 p_StatCode_Segmentnew FND_FLEX_KEY_API.segment_type;
4625 l_flex_exists Boolean;
4626 l_structure_exists Boolean;
4627 l_segment_exists Boolean;
4628 p_StatCode_struct FND_FLEX_KEY_API.STRUCTURE_TYPE;
4629 msg VARCHAR2(1000);
4630 l_category_set_id mtl_category_sets_b.Category_set_ID%TYPE;
4631 -- l_control_level NUMBER;
4632 -- l_row_id VARCHAR2(100);
4633 -- l_next_val NUMBER;
4634
4635 BEGIN
4636
4637 arp_util_tax.debug( 'OKL Migration ... (+) ' );
4638 /* Check for Inventory Installed or not, if so, create under Inventory, if not Create Product Category */
4639
4640 IF Zx_Migrate_Util.IS_INSTALLED('INV') = 'Y' THEN
4641
4642 -- OKL MIGRATION
4643 -- Category Set should had been created as a pre requesite before migration
4644 -- Create Categories and Fiscal Type for OKL Category Set.
4645
4646 CREATE_MTL_CATEGORIES('AR_TAX_PRODUCT_FISCAL_CLASS','Product Fiscal Class - Leasing',l_category_status,l_category_set,l_structure_id);
4647
4648 -- Call a common procedure to create FC Types
4649 IF l_category_status = 'EXISTS' then
4650
4651 arp_util_tax.debug( 'Creating Lease Product Fiscal Type');
4652
4653 FC_TYPE_INSERT('LEASE_MGT_PROD_FISC_CLASS','Lease Management Product Fiscal Class',l_category_set);
4654
4655 -- Regime Association
4656
4657 INSERT ALL INTO ZX_FC_TYPES_REG_ASSOC
4658 (Tax_regime_code,
4659 classification_type_code,
4660 effective_FROM,
4661 effective_to,
4662 record_type_code,
4663 created_by,
4664 creation_date,
4665 last_updated_by,
4666 last_update_date,
4667 last_update_login,
4668 classif_regime_id,
4669 object_version_number)
4670 VALUES
4671 (tax_regime_code,
4672 'LEASE_MGT_PROD_FISC_CLASS',
4673 SYSDATE,
4674 NULL,
4675 'MIGRATED',
4676 fnd_global.user_id,
4677 SYSDATE,
4678 fnd_global.user_id,
4679 SYSDATE,
4680 FND_GLOBAL.CONC_LOGIN_ID,
4681 zx_fc_types_reg_assoc_s.nextval,
4682 1)
4683 SELECT unique rates.tax_regime_code
4684 FROM ZX_RATES_B rates,
4685 AR_VAT_TAX_ALL_B codes
4686 WHERE codes.vat_tax_id = nvl(rates.source_id, rates.tax_rate_id) and
4687 codes.leasing_flag = 'Y' and
4688 rates.record_type_code = 'MIGRATED' and
4689 not exists
4690 (select null from ZX_FC_TYPES_REG_ASSOC
4691 where classification_type_code = 'LEASE_MGT_PROD_FISC_CLASS'
4692 and tax_regime_code = rates.tax_regime_code);
4693
4694 -- Disable Lookup Type
4695 -- Change the Flexfield Structure Segment value Set.
4696 BEGIN
4697 fnd_flex_key_api.set_session_mode('seed_data');
4698 l_flex_exists:= FALSE;
4699
4700 l_flex_exists:= fnd_flex_key_api.flexfield_exists(appl_short_name => 'INV',flex_code => 'MCAT',flex_title => 'Item Categories');
4701
4702 If l_flex_exists Then
4703 p_flexfield:= fnd_flex_key_api.find_flexfield(appl_short_name => 'INV',flex_code => 'MCAT');
4704
4705 BEGIN
4706 p_StatCode_struct:= fnd_flex_key_api.find_structure(p_flexfield,'AR_TAX_PRODUCT_FISCAL_CLASS');
4707 l_structure_exists:=TRUE;
4708 EXCEPTION
4709 WHEN OTHERS THEN
4710 msg := 'ERROR: struct not found' || fnd_flex_key_api.message;
4711 l_structure_exists:=FALSE;
4712 END;
4713
4714 IF l_structure_exists THEN
4715 -- find current segment
4716 BEGIN
4717 p_StatCode_Segment:= fnd_flex_key_api.find_segment(flexfield=> p_flexfield,structure=> p_StatCode_struct,segment_name=>'Product Fiscal Classification');
4718 l_segment_exists:=TRUE;
4719 EXCEPTION
4720 WHEN OTHERS THEN
4721 msg := substr('ERROR: ' || fnd_flex_key_api.message,1,225);
4725 IF l_segment_exists THEN
4722 l_segment_exists:=FALSE;
4723 END;
4724
4726 -- Create new segment wich will replace the definition of current.
4727 BEGIN
4728 p_StatCode_Segment:= fnd_flex_key_api.new_segment(flexfield => p_flexfield,
4729 structure => p_StatCode_struct,
4730 segment_name => 'Product Fiscal Classification',
4731 description => 'Product Fiscal Classification',
4732 column_name => 'SEGMENT1',
4733 segment_number => 1,
4734 enabled_flag => 'Y',
4735 displayed_flag => 'Y',
4736 indexed_flag => 'Y',
4737 value_set => '30 Characters',
4738 default_type => NULL,
4739 default_value => NULL,
4740 required_flag =>'Y',
4741 security_flag => 'N',
4742 range_code => NULL,
4743 display_size => 30,
4744 description_size => 50,
4745 concat_size => 25,
4746 lov_prompt => 'Product Fiscal Classification',
4747 runtime_property_function => NULL,
4748 additional_where_clause => NULL);
4749
4750 EXCEPTION
4751 WHEN OTHERS THEN
4752 msg := SUBSTR('ERROR: ' || fnd_flex_key_api.message,1,225);
4753 END;
4754
4755 BEGIN
4756 -- fnd_flex_key_api.add_segment(p_flexfield,p_StatCode_struct,p_StatCode_Segment);
4757 fnd_flex_key_api.modify_segment(p_flexfield, p_StatCode_struct, p_StatCode_Segment, p_StatCode_Segmentnew);
4758 l_segment_exists:=TRUE;
4759 EXCEPTION
4760 WHEN OTHERS THEN
4761 msg := SUBSTR('ERROR: ' || fnd_flex_key_api.message,1,225);
4762 l_segment_exists:=FALSE;
4763 END;
4764
4765 END IF; -- Segment
4766 End if; -- Structure
4767 End If; -- Flex
4768
4769 END;
4770
4771 END IF;
4772 END IF; -- Check for Inventory installed
4773
4774 -- Create Party Fiscal Type
4775 FC_PARTY_TYPE_INSERT('LEASE_MGT_PTY_FISC_CLASS','Lease Management Party Fiscal Class','AR_TAX_PARTY_FISCAL_CLASS');
4776
4777 -- Create User Defined Codes
4778
4779 arp_util_tax.debug( 'Creating the Codes under User Defined Fiscal Classifications ');
4780
4781 OPEN G_C_GET_TYPES_INFO('USER_DEFINED');
4782
4783 FETCH G_C_GET_TYPES_INFO INTO
4784 G_CLASSIFICATION_TYPE_ID,
4785 G_CLASSIFICATION_TYPE_CODE,
4786 G_CLASSIFICATION_TYPE_NAME,
4787 G_CLASSIFICATION_TYP_CATEG_COD,
4788 G_DELIMITER;
4789
4790 CLOSE G_C_GET_TYPES_INFO;
4791
4792 INSERT
4793 INTO ZX_FC_CODES_B
4794 (classification_type_code,
4795 classification_id,
4796 classification_code,
4797 effective_from,
4798 effective_to,
4799 parent_classification_code,
4800 parent_classification_id,
4801 country_code,
4802 record_type_code,
4803 created_by,
4804 creation_date,
4805 last_updated_by,
4806 last_update_date,
4807 last_update_login,
4808 object_version_number)
4809 (SELECT 'USER_DEFINED',
4810 zx_fc_codes_b_s.nextval,
4811 lookup_code, --classification_code
4812 nvl(start_date_active,sysdate), --effective_from
4813 end_date_active, --effective_to
4814 null,----parent_classification_code
4815 null,----parent_classification_id
4816 null,
4817 'MIGRATED',
4818 fnd_global.user_id,
4819 SYSDATE,
4820 fnd_global.user_id,
4821 SYSDATE,
4822 FND_GLOBAL.CONC_LOGIN_ID,
4823 1
4824 FROM FND_LOOKUP_VALUES lookups
4825 WHERE lookups.lookup_type='AR_TAX_TRX_BUSINESS_CATEGORY'
4826 AND LANGUAGE = userenv('LANG')
4827 AND NOT EXISTS
4828 -- this condition makes sure we dont duplicate data
4829 (select NULL from ZX_FC_CODES_B Codes where
4830 codes.classification_type_code = 'USER_DEFINED'
4831 and codes.parent_classification_id is null
4832 and codes.classification_code = lookups.lookup_code)
4833 );
4834
4835 INSERT ALL
4836 INTO ZX_FC_CODES_TL
4837 (CLASSIFICATION_ID,
4838 CLASSIFICATION_NAME,
4839 CREATED_BY,
4840 CREATION_DATE,
4841 LAST_UPDATED_BY,
4842 LAST_UPDATE_DATE,
4843 LAST_UPDATE_LOGIN,
4844 LANGUAGE,
4845 SOURCE_LANG)
4846 VALUES (classification_id,
4847 CASE WHEN Meaning = UPPER(Meaning)
4848 THEN Initcap(Meaning)
4849 ELSE
4850 Meaning
4851 END,
4852 fnd_global.user_id,
4853 SYSDATE,
4854 fnd_global.user_id,
4855 SYSDATE,
4856 FND_GLOBAL.CONC_LOGIN_ID,
4857 language,
4858 source_lang)
4859 INTO ZX_FC_CODES_DENORM_B(
4860 CLASSIFICATION_TYPE_ID,
4861 CLASSIFICATION_TYPE_CODE,
4862 CLASSIFICATION_TYPE_NAME,
4863 CLASSIFICATION_TYPE_CATEG_CODE,
4864 CLASSIFICATION_ID,
4865 CLASSIFICATION_CODE,
4866 CLASSIFICATION_NAME,
4867 LANGUAGE,
4868 EFFECTIVE_FROM,
4869 EFFECTIVE_TO,
4870 ENABLED_FLAG,
4871 ANCESTOR_ID,
4872 ANCESTOR_CODE,
4873 ANCESTOR_NAME,
4874 CONCAT_CLASSIF_CODE,
4875 CONCAT_CLASSIF_NAME,
4876 CLASSIFICATION_CODE_LEVEL,
4877 COUNTRY_CODE,
4878 SEGMENT1,
4879 SEGMENT2,
4880 SEGMENT3,
4881 SEGMENT4,
4885 SEGMENT8,
4882 SEGMENT5,
4883 SEGMENT6,
4884 SEGMENT7,
4886 SEGMENT9,
4887 SEGMENT10,
4888 SEGMENT1_NAME,
4889 SEGMENT2_NAME,
4890 SEGMENT3_NAME,
4891 SEGMENT4_NAME,
4892 SEGMENT5_NAME,
4893 SEGMENT6_NAME,
4894 SEGMENT7_NAME,
4895 SEGMENT8_NAME,
4896 SEGMENT9_NAME,
4897 SEGMENT10_NAME,
4898 CREATED_BY,
4899 CREATION_DATE,
4900 LAST_UPDATED_BY,
4901 LAST_UPDATE_LOGIN,
4902 LAST_UPDATE_DATE,
4903 REQUEST_ID,
4904 PROGRAM_ID,
4905 PROGRAM_APPLICATION_ID,
4906 PROGRAM_LOGIN_ID,
4907 RECORD_TYPE_CODE)
4908 VALUES (
4909 G_CLASSIFICATION_TYPE_ID,
4910 G_CLASSIFICATION_TYPE_CODE,
4911 G_CLASSIFICATION_TYPE_NAME,
4912 G_CLASSIFICATION_TYP_CATEG_COD,
4913 classification_id,
4914 lookup_code,
4915 Meaning,
4916 language,
4917 nvl(start_date_active,sysdate), --effective_from
4918 end_date_active, --effective_to
4919 'Y',
4920 null,
4921 null,
4922 null,
4923 lookup_code,
4924 Meaning,
4925 1,
4926 NULL,
4927 lookup_code,
4928 Null,
4929 Null,
4930 Null,
4931 Null,
4932 Null,
4933 Null,
4934 Null,
4935 Null,
4936 Null,
4937 Meaning,
4938 Null,
4939 Null,
4940 Null,
4941 Null,
4942 Null,
4943 Null,
4944 Null,
4945 Null,
4946 Null,
4947 fnd_global.user_id,
4948 SYSDATE,
4949 fnd_global.user_id,
4950 FND_GLOBAL.CONC_LOGIN_ID,
4951 sysdate,
4952 FND_GLOBAL.CONC_REQUEST_ID,
4953 fnd_global.CONC_PROGRAM_ID,
4954 235,
4955 FND_GLOBAL.CONC_LOGIN_ID,
4956 'MIGRATED')
4957 SELECT
4958 lookup_code,
4959 meaning,
4960 nvl(start_date_active,sysdate) start_date_active,
4961 end_date_active,
4962 source_lang,
4963 language,
4964 lv.enabled_flag,
4965 classification_id
4966 FROM
4967 ZX_FC_CODES_B Codes,
4968 FND_LOOKUP_VALUES LV,
4969 FND_LANGUAGES L
4970 WHERE
4971 Codes.classification_type_code = G_CLASSIFICATION_TYPE_CODE
4972 AND Codes.parent_classification_id is null
4973 AND Codes.classification_code = lv.lookup_code
4974 AND Codes.RECORD_TYPE_CODE IN ('MIGRATED','SEEDED')
4975 AND LV.VIEW_APPLICATION_ID = 222
4976 AND LV.SECURITY_GROUP_ID = 0
4977 AND LV.lookup_type='AR_TAX_TRX_BUSINESS_CATEGORY'
4978 AND LV.language=L.language_code(+)
4979 AND L.INSTALLED_FLAG in ('I', 'B')
4980 AND NOT EXISTS -- this condition makes sure we dont duplicate data
4981 (select NULL from ZX_FC_CODES_DENORM_B codes where
4982 codes.classification_type_code = G_CLASSIFICATION_TYPE_CODE
4983 and codes.classification_code = lv.lookup_code
4984 and codes.ancestor_id is null
4985 and codes.language = l.language_code);
4986
4987 -- Disable Lookup Type
4988 -- Create Country defaults ?
4989 arp_util_tax.debug( 'OKL Migration ... (-) ' );
4990
4991 END OKL_MIGRATION;
4992
4993
4994
4995 /*===========================================================================+
4996 | Procedure: ZX_FC_MIGRATE |
4997 | Description: This is the main procedure of fiscal classification |
4998 | migration. |
4999 | ARGUMENTS : |
5000 | |
5001 | NOTES |
5002 | |
5003 | History |
5004 | zmohiudd Created |
5005 | |
5006 | |
5007 +===========================================================================*/
5008
5009
5010 PROCEDURE ZX_FC_MIGRATE IS
5011
5012 BEGIN
5013
5014 arp_util_tax.debug( ' ZX_FC_MIGRATE .. (+) ' );
5015 arp_util_tax.debug( ' Now calling MTL system items .. ' );
5016
5017 ZX_FC_MIGRATE_PKG.MTL_SYSTEM_ITEMS;
5018
5019 arp_util_tax.debug( ' Now calling FC Entities .. ' );
5020
5021 ZX_FC_MIGRATE_PKG.FC_ENTITIES;
5022
5023 arp_util_tax.debug( ' Now calling Migrate AP .. ' );
5024
5025 If Zx_Migrate_Util.IS_INSTALLED('AP') = 'Y' THEN
5026 ZX_MIGRATE_AP;
5027 End if;
5028
5029 arp_util_tax.debug( ' Now calling Migrate AR .. ' );
5030
5031 If Zx_Migrate_Util.IS_INSTALLED('AR') = 'Y' THEN
5032 ZX_MIGRATE_AR;
5033 End if;
5034
5035 arp_util_tax.debug( ' Now calling Migrate OKL .. ' );
5036 OKL_MIGRATION;
5037
5038 arp_util_tax.debug( ' Now calling country default.. ' );
5039
5040 ZX_FC_MIGRATE_PKG.COUNTRY_DEFAULT;
5041
5042 arp_util_tax.debug( ' Now calling ZX_GDF_TO_ARMEMO_LINES...' );
5043
5044 ZX_GDF_TO_ARMEMO_LINES;
5045
5046 arp_util_tax.debug('ZX_FC_MIGRATE_PKG...(-)');
5047
5048 END ZX_FC_MIGRATE;
5049
5050 BEGIN
5051
5052 SELECT NVL(MULTI_ORG_FLAG,'N') INTO L_MULTI_ORG_FLAG FROM
5053 FND_PRODUCT_GROUPS;
5054
5058
5055 IF L_MULTI_ORG_FLAG = 'N' THEN
5056
5057 FND_PROFILE.GET('ORG_ID',L_ORG_ID);
5059 IF L_ORG_ID IS NULL THEN
5060 arp_util_tax.debug('MO: Operating Units site level profile option value not set , resulted in Null Org Id');
5061 END IF;
5062 ELSE
5063 L_ORG_ID := NULL;
5064 END IF;
5065
5066
5067
5068 EXCEPTION
5069 WHEN OTHERS THEN
5070 arp_util_tax.debug('Exception in constructor of Fiscal Classification '||sqlerrm);
5071
5072 END ZX_FC_MIGRATE_PKG ;