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