[Home] [Help]
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: );
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 */
654: l_start_date_active fnd_lookup_values.start_date_active%type;
655: l_end_date_active fnd_lookup_values.end_date_active%type;
656: l_source_lang fnd_lookup_values.source_lang%type;
657: i integer:=0;
658: l_fc_id zx_fc_codes_b.classification_id%type;
659: l_return_status varchar2(200);
660: l_errorcode number;
661: l_msg_count number;
662: l_MSG_DATA varchar2(200);
743:
744: /* Create Codes under Product Category Statistical Code */
745: arp_util_tax.debug( 'Creating the Statistical Code.. ');
746:
747: --select zx_fc_codes_b_s.nextval into l_fc_id from dual;
748:
749: FIRST_LEVEL_FC_CODE_INSERT('PRODUCT_CATEGORY','STATISTICAL_CODE','Statistical Code',NULL,l_fc_id);
750:
751: FC_CODE_FROM_FND_LOOKUP('PRODUCT_CATEGORY','JGZZ_STATISTICAL_CODE',NULL,l_fc_id,'STATISTICAL_CODE','Statistical Code',2);
776:
777: CLOSE G_C_GET_TYPES_INFO;
778: /* Removed the reference to obsolete object JL_BR_PO_FISC_CLASSIF_ALL. Bug # 5150296 */
779: INSERT
780: INTO ZX_FC_CODES_B
781: (classification_type_code,
782: classification_id,
783: classification_code,
784: effective_from,
794: last_update_login,
795: object_version_number)
796: ( SELECT
797: 'PRODUCT_CATEGORY',
798: zx_fc_codes_b_s.nextval,
799: lookups.LOOKUP_CODE fc_code,
800: nvl(START_DATE_ACTIVE, to_date('01/01/1951','DD/MM/YYYY')) effective_from,
801: END_DATE_ACTIVE effective_to,
802: 'FISCAL CLASSIFICATION CODE',--parent_classification_code
813: FND_LOOKUPS lookups
814: WHERE
815: lookups.LOOKUP_TYPE = 'JLZZ_AR_TX_FISCAL_CLASS_CODE'
816: AND NOT EXISTS -- this condition makes sure we dont duplicate data
817: (select NULL from ZX_FC_CODES_B Codes where
818: codes.classification_type_code = 'PRODUCT_CATEGORY'
819: and codes.parent_classification_id = nvl(l_fc_id,codes.parent_classification_id)
820: and codes.classification_code = lookups.lookup_code )
821: );
951: FL.LANGUAGE LANGUAGE,
952: FL.SOURCE_LANG SOURCE_LANGUAGE,
953: codes.classification_id
954: FROM
955: ZX_FC_CODES_b Codes,
956: FND_LOOKUP_VALUES FL,
957: FND_LANGUAGES L
958: WHERE
959: Codes.classification_type_code = 'PRODUCT_CATEGORY'
1071: FROM mtl_category_sets
1072: WHERE Category_Set_Name ='WINE_CIGARRETE_CATEGORY';
1073:
1074: l_LANGUAGE zx_fc_types_tl.language%type;
1075: l_fc_id zx_fc_codes_b.classification_id%type;
1076: p_category_set mtl_category_sets_vl.Category_set_ID%TYPE;
1077: p_flexfield FND_FLEX_KEY_API.FLEXFIELD_TYPE;
1078: p_structure_id FND_FLEX_KEY_API.STRUCTURE_TYPE;
1079: v_structure_id FND_FLEX_KEY_API.STRUCTURE_TYPE;
1076: p_category_set mtl_category_sets_vl.Category_set_ID%TYPE;
1077: p_flexfield FND_FLEX_KEY_API.FLEXFIELD_TYPE;
1078: p_structure_id FND_FLEX_KEY_API.STRUCTURE_TYPE;
1079: v_structure_id FND_FLEX_KEY_API.STRUCTURE_TYPE;
1080: v_classification_code zx_fc_codes_b.classification_code%type;
1081: v_classification_name zx_fc_codes_tl.classification_name%type;
1082: v_effective_from date;
1083: v_effective_to date;
1084: v_language zx_fc_codes_tl.language%type;
1081: v_classification_name zx_fc_codes_tl.classification_name%type;
1082: v_effective_from date;
1083: v_effective_to date;
1084: v_language zx_fc_codes_tl.language%type;
1085: v_RECORD_TYPE zx_Fc_codes_b.RECORD_TYPE_CODE%type;
1086:
1087: BEGIN
1088:
1089: arp_util_tax.debug( ' FC_ENTITIES .. (+) ' );
1107:
1108: CLOSE G_C_GET_TYPES_INFO;
1109:
1110: INSERT
1111: INTO ZX_FC_CODES_B
1112: (classification_type_code,
1113: classification_id,
1114: classification_code,
1115: effective_from,
1124: last_update_date,
1125: last_update_login,
1126: object_version_number)
1127: (SELECT 'USER_DEFINED',
1128: zx_fc_codes_b_s.nextval,
1129: cfo_code,
1130: creation_date,
1131: null,
1132: null,----parent_classification_code
1141: 1
1142: FROM JL_BR_AP_OPERATIONS JLBR
1143: WHERE NOT EXISTS
1144: -- this condition makes sure we dont duplicate data
1145: (select NULL from ZX_FC_CODES_B Codes where
1146: codes.classification_type_code = 'USER_DEFINED'
1147: and codes.parent_classification_id is null
1148: and codes.classification_code = jlbr.cfo_code)
1149: );
1275: cfo_description Meaning,
1276: JLBR.creation_date ,
1277: l.language_code lang_code,
1278: codes.classification_id
1279: FROM ZX_FC_CODES_B Codes,
1280: JL_BR_AP_OPERATIONS JLBR,
1281: FND_LANGUAGES L
1282: WHERE
1283: Codes.classification_type_code = G_CLASSIFICATION_TYPE_CODE
1313:
1314: arp_util_tax.debug( 'Create Code under the Transaction Business Category Type-Level 3: BR');
1315:
1316: INSERT
1317: INTO ZX_FC_CODES_B (
1318: classification_type_code,
1319: classification_id,
1320: classification_code,
1321: effective_from,
1332: object_version_number
1333: )
1334: SELECT
1335: 'TRX_BUSINESS_CATEGORY',
1336: zx_fc_codes_b_s.nextval,
1337: cfo_code ,
1338: ap_op.creation_date,
1339: null,
1340: fc.classification_code,--parent_classification_code
1358: and fc.language = userenv('LANG')
1359: and fc.classification_code_level = 2
1360: and event.tax_event_class_code = 'PURCHASE_TRANSACTION'
1361: and NOT EXISTS -- this condition makes sure we dont duplicate data
1362: ( select NULL from ZX_FC_CODES_B Codes where
1363: codes.classification_type_code = 'TRX_BUSINESS_CATEGORY'
1364: and codes.parent_classification_id =
1365: nvl(fc.classification_id,codes.parent_classification_id)
1366: and codes.classification_code = ap_op.cfo_code );
1499: codes.classification_id,
1500: l.language_code lang_code
1501: FROM
1502: ZX_FC_CODES_DENORM_B fc,
1503: ZX_FC_CODES_B Codes,
1504: JL_BR_AP_OPERATIONS ap_op,
1505: FND_LANGUAGES L,
1506: ZX_EVENT_CLASSES_VL event
1507: WHERE
1562:
1563: arp_util_tax.debug( 'Create Codes on level 2 for country : TAIWAN ');
1564:
1565: INSERT
1566: INTO ZX_FC_CODES_B (
1567: classification_type_code,
1568: classification_id,
1569: classification_code,
1570: effective_from,
1580: last_update_login,
1581: object_version_number)
1582: SELECT
1583: 'DOCUMENT_SUBTYPE',
1584: zx_fc_codes_b_s.nextval,
1585: lookup_code, --classification_code
1586: nvl(start_date_active,to_date('01/01/1951','DD/MM/YYYY')), --effective_from
1587: end_date_active, --effective_to
1588: 'GUI TYPE', --parent_classification_code
1600: WHERE
1601: lookups.lookup_type='JATW_GUI_TYPE'
1602: AND LANGUAGE = userenv('LANG')
1603: AND NOT EXISTS -- this condition makes sure we dont duplicate data
1604: (select NULL from ZX_FC_CODES_B Codes where
1605: codes.classification_type_code = 'DOCUMENT_SUBTYPE'
1606: and codes.parent_classification_id =
1607: nvl(l_fc_id,codes.parent_classification_id)
1608: and codes.classification_code = lookups.lookup_code
1741: language,
1742: lv.enabled_flag,
1743: classification_id
1744: FROM
1745: ZX_FC_CODES_B Codes,
1746: FND_LOOKUP_VALUES LV,
1747: FND_LANGUAGES L
1748: WHERE
1749: Codes.classification_type_code = G_CLASSIFICATION_TYPE_CODE
2045:
2046: /*THIS IS THE COMMON PROCEDURE USED TO INSERT THE FIRST LEVEL FC CODES*/
2047:
2048: PROCEDURE FIRST_LEVEL_FC_CODE_INSERT(
2049: p_classification_type_code IN ZX_FC_CODES_B.CLASSIFICATION_TYPE_CODE%TYPE,
2050: p_classification_code IN ZX_FC_CODES_B.CLASSIFICATION_CODE%TYPE,
2051: p_classification_name IN ZX_FC_CODES_TL.CLASSIFICATION_NAME%TYPE,
2052: p_country_code IN ZX_FC_CODES_B.COUNTRY_CODE%TYPE,
2053: x_fc_id OUT NOCOPY ZX_FC_CODES_B.CLASSIFICATION_ID%TYPE
2046: /*THIS IS THE COMMON PROCEDURE USED TO INSERT THE FIRST LEVEL FC CODES*/
2047:
2048: PROCEDURE FIRST_LEVEL_FC_CODE_INSERT(
2049: p_classification_type_code IN ZX_FC_CODES_B.CLASSIFICATION_TYPE_CODE%TYPE,
2050: p_classification_code IN ZX_FC_CODES_B.CLASSIFICATION_CODE%TYPE,
2051: p_classification_name IN ZX_FC_CODES_TL.CLASSIFICATION_NAME%TYPE,
2052: p_country_code IN ZX_FC_CODES_B.COUNTRY_CODE%TYPE,
2053: x_fc_id OUT NOCOPY ZX_FC_CODES_B.CLASSIFICATION_ID%TYPE
2054: )
2048: PROCEDURE FIRST_LEVEL_FC_CODE_INSERT(
2049: p_classification_type_code IN ZX_FC_CODES_B.CLASSIFICATION_TYPE_CODE%TYPE,
2050: p_classification_code IN ZX_FC_CODES_B.CLASSIFICATION_CODE%TYPE,
2051: p_classification_name IN ZX_FC_CODES_TL.CLASSIFICATION_NAME%TYPE,
2052: p_country_code IN ZX_FC_CODES_B.COUNTRY_CODE%TYPE,
2053: x_fc_id OUT NOCOPY ZX_FC_CODES_B.CLASSIFICATION_ID%TYPE
2054: )
2055: IS
2056:
2049: p_classification_type_code IN ZX_FC_CODES_B.CLASSIFICATION_TYPE_CODE%TYPE,
2050: p_classification_code IN ZX_FC_CODES_B.CLASSIFICATION_CODE%TYPE,
2051: p_classification_name IN ZX_FC_CODES_TL.CLASSIFICATION_NAME%TYPE,
2052: p_country_code IN ZX_FC_CODES_B.COUNTRY_CODE%TYPE,
2053: x_fc_id OUT NOCOPY ZX_FC_CODES_B.CLASSIFICATION_ID%TYPE
2054: )
2055: IS
2056:
2057: BEGIN
2070: G_DELIMITER;
2071:
2072: CLOSE G_C_GET_TYPES_INFO;
2073:
2074: --select zx_fc_codes_b_s.nextval into x_fc_id from dual;
2075:
2076: INSERT
2077: INTO ZX_FC_CODES_B (
2078: classification_type_code,
2073:
2074: --select zx_fc_codes_b_s.nextval into x_fc_id from dual;
2075:
2076: INSERT
2077: INTO ZX_FC_CODES_B (
2078: classification_type_code,
2079: classification_id,
2080: classification_code,
2081: effective_from,
2090: last_update_login,
2091: object_version_number)
2092: SELECT
2093: p_classification_type_code,
2094: zx_fc_codes_b_s.nextval,
2095: p_classification_code,
2096: Sysdate,
2097: Null,
2098: Null, ---parent_classification_code
2105: FND_GLOBAL.CONC_LOGIN_ID,
2106: 1
2107: FROM DUAL
2108: WHERE NOT EXISTS
2109: (select NULL from ZX_FC_CODES_B Codes where
2110: codes.classification_type_code = p_classification_type_code
2111: and codes.parent_classification_id is null
2112: and codes.classification_code = p_classification_code
2113: );
2238: select
2239: language_code lang_code, fc_codes.classification_id
2240: from
2241: fnd_languages l,
2242: zx_fc_codes_b fc_codes
2243: where
2244: l.installed_flag in ('I', 'B')
2245: and fc_codes.classification_type_code = p_classification_type_code
2246: and fc_codes.parent_classification_id is null
2256: -- Find and return the classification id
2257: SELECT
2258: classification_id into x_fc_id
2259: from
2260: ZX_FC_CODES_B Codes
2261: where
2262: codes.classification_type_code = p_classification_type_code
2263: and codes.parent_classification_id is null
2264: and codes.classification_code = p_classification_code;
2271:
2272: /*THIS IS THE COMMON PROCEDURE USED TO INSERT VALUES BASED UPON THE LOOKUP TYPE */
2273:
2274: PROCEDURE FC_CODE_FROM_FND_LOOKUP(
2275: p_classification_type_code IN ZX_FC_CODES_B.CLASSIFICATION_TYPE_CODE%TYPE,
2276: p_lookup_type IN FND_LOOKUP_VALUES.LOOKUP_TYPE%TYPE,
2277: p_country_code IN ZX_FC_CODES_B.COUNTRY_CODE%TYPE,
2278: p_parent_fc_id IN ZX_FC_CODES_B.CLASSIFICATION_ID%TYPE,
2279: p_ancestor_code IN ZX_FC_CODES_DENORM_B.ANCESTOR_CODE%TYPE,
2273:
2274: PROCEDURE FC_CODE_FROM_FND_LOOKUP(
2275: p_classification_type_code IN ZX_FC_CODES_B.CLASSIFICATION_TYPE_CODE%TYPE,
2276: p_lookup_type IN FND_LOOKUP_VALUES.LOOKUP_TYPE%TYPE,
2277: p_country_code IN ZX_FC_CODES_B.COUNTRY_CODE%TYPE,
2278: p_parent_fc_id IN ZX_FC_CODES_B.CLASSIFICATION_ID%TYPE,
2279: p_ancestor_code IN ZX_FC_CODES_DENORM_B.ANCESTOR_CODE%TYPE,
2280: p_ancestor_name IN ZX_FC_CODES_DENORM_B.ANCESTOR_NAME%TYPE,
2281: p_classification_code_level IN ZX_FC_CODES_DENORM_B.CLASSIFICATION_CODE_LEVEL%TYPE)
2274: PROCEDURE FC_CODE_FROM_FND_LOOKUP(
2275: p_classification_type_code IN ZX_FC_CODES_B.CLASSIFICATION_TYPE_CODE%TYPE,
2276: p_lookup_type IN FND_LOOKUP_VALUES.LOOKUP_TYPE%TYPE,
2277: p_country_code IN ZX_FC_CODES_B.COUNTRY_CODE%TYPE,
2278: p_parent_fc_id IN ZX_FC_CODES_B.CLASSIFICATION_ID%TYPE,
2279: p_ancestor_code IN ZX_FC_CODES_DENORM_B.ANCESTOR_CODE%TYPE,
2280: p_ancestor_name IN ZX_FC_CODES_DENORM_B.ANCESTOR_NAME%TYPE,
2281: p_classification_code_level IN ZX_FC_CODES_DENORM_B.CLASSIFICATION_CODE_LEVEL%TYPE)
2282: IS
2299:
2300: CLOSE G_C_GET_TYPES_INFO;
2301:
2302: INSERT
2303: INTO ZX_FC_CODES_B
2304: (classification_type_code,
2305: classification_id,
2306: classification_code,
2307: effective_from,
2316: last_update_date,
2317: last_update_login,
2318: object_version_number)
2319: SELECT p_classification_type_code,
2320: zx_fc_codes_b_s.nextval,
2321: lookup_code,
2322: nvl(start_date_active,to_date('01/01/1951','DD/MM/YYYY')),
2323: end_date_active,
2324: p_ancestor_code,----parent_classification_code
2336: lookups.lookup_type=p_lookup_type
2337: AND LANGUAGE = userenv('LANG')
2338: AND lookups.enabled_flag = 'Y'
2339: AND NOT EXISTS -- this condition makes sure we dont duplicate data
2340: (select NULL from ZX_FC_CODES_B Codes where
2341: codes.classification_type_code = p_classification_type_code
2342: and ( codes.parent_classification_id = p_parent_fc_id or
2343: p_parent_fc_id is null )
2344: and codes.classification_code = lookups.lookup_code
2478: end_date_active,
2479: fl.enabled_flag,
2480: Codes.classification_id
2481: FROM
2482: zx_fc_codes_b Codes,
2483: fnd_lookup_values fl,
2484: fnd_languages l
2485: WHERE
2486:
2510: /*THIS IS THE COMMON PROCEDURE USED TO PERFORM INSERTS BASED ON THE GLOBAL DESCRIPTIVE FLEXI FIELDS
2511: This procedure is called from zxcfctbc.ldt file also */
2512:
2513: PROCEDURE FC_CODE_GDF_INSERT(
2514: p_classification_code IN ZX_FC_CODES_B.CLASSIFICATION_CODE%TYPE,
2515: p_classification_name IN ZX_FC_CODES_TL.CLASSIFICATION_NAME%TYPE,
2516: p_country_code IN ZX_FC_CODES_B.COUNTRY_CODE%TYPE,
2517: p_lookup_type IN FND_LOOKUP_VALUES.LOOKUP_TYPE%TYPE,
2518: p_tax_event_class_code IN ZX_EVENT_CLASSES_VL.TAX_EVENT_CLASS_CODE%TYPE,
2512:
2513: PROCEDURE FC_CODE_GDF_INSERT(
2514: p_classification_code IN ZX_FC_CODES_B.CLASSIFICATION_CODE%TYPE,
2515: p_classification_name IN ZX_FC_CODES_TL.CLASSIFICATION_NAME%TYPE,
2516: p_country_code IN ZX_FC_CODES_B.COUNTRY_CODE%TYPE,
2517: p_lookup_type IN FND_LOOKUP_VALUES.LOOKUP_TYPE%TYPE,
2518: p_tax_event_class_code IN ZX_EVENT_CLASSES_VL.TAX_EVENT_CLASS_CODE%TYPE,
2519: p_record_type_code IN ZX_FC_CODES_B.RECORD_TYPE_CODE%TYPE
2520: )
2515: p_classification_name IN ZX_FC_CODES_TL.CLASSIFICATION_NAME%TYPE,
2516: p_country_code IN ZX_FC_CODES_B.COUNTRY_CODE%TYPE,
2517: p_lookup_type IN FND_LOOKUP_VALUES.LOOKUP_TYPE%TYPE,
2518: p_tax_event_class_code IN ZX_EVENT_CLASSES_VL.TAX_EVENT_CLASS_CODE%TYPE,
2519: p_record_type_code IN ZX_FC_CODES_B.RECORD_TYPE_CODE%TYPE
2520: )
2521:
2522: IS
2523:
2550:
2551: arp_util_tax.debug( 'Create Code under the Transaction Business Category Type-Level 3: ' || p_country_code);
2552:
2553: INSERT
2554: INTO ZX_FC_CODES_B (
2555: classification_type_code,
2556: classification_id,
2557: classification_code,
2558: effective_from,
2568: last_update_login,
2569: object_version_number)
2570: SELECT
2571: 'TRX_BUSINESS_CATEGORY',
2572: zx_fc_codes_b_s.nextval,
2573: lookup_code,
2574: nvl(start_date_active,to_date('01/01/1951','DD/MM/YYYY')) start_date_active,
2575: end_date_active,
2576: fc.classification_code ,---parent_classification_code
2597: and fl.enabled_flag = 'Y'
2598: and fl.language = userenv('LANG')
2599: and event.tax_event_class_code = p_tax_event_class_code
2600: and NOT EXISTS -- this condition makes sure we dont duplicate data
2601: (select NULL from ZX_FC_CODES_B Codes where
2602: codes.classification_type_code = 'TRX_BUSINESS_CATEGORY'
2603: and codes.parent_classification_id =
2604: nvl(fc.classification_id,codes.parent_classification_id)
2605: and codes.classification_code = fl.lookup_code
2744: Codes.classification_id
2745:
2746: FROM
2747: ZX_FC_CODES_DENORM_B fc,
2748: ZX_FC_CODES_b Codes,
2749: ZX_EVENT_CLASSES_VL event,
2750: FND_LOOKUP_VALUES FL,
2751: FND_LANGUAGES L
2752: WHERE fc.classification_type_code = 'TRX_BUSINESS_CATEGORY'
2777:
2778: /*THIS IS THE COMMON PROCEDURE USED TO INSERT THE GLOBAL DESCRIPTIVE FLEXI FIELD PROMPT VALUE*/
2779:
2780: PROCEDURE GDF_PROMPT_INSERT(
2781: p_classification_code IN ZX_FC_CODES_B.CLASSIFICATION_CODE%TYPE,
2782: p_classification_name IN ZX_FC_CODES_TL.CLASSIFICATION_NAME%TYPE,
2783: p_country_code IN ZX_FC_CODES_B.COUNTRY_CODE%TYPE,
2784: p_tax_event_class_code IN ZX_EVENT_CLASSES_VL.TAX_EVENT_CLASS_CODE%TYPE
2785: )
2779:
2780: PROCEDURE GDF_PROMPT_INSERT(
2781: p_classification_code IN ZX_FC_CODES_B.CLASSIFICATION_CODE%TYPE,
2782: p_classification_name IN ZX_FC_CODES_TL.CLASSIFICATION_NAME%TYPE,
2783: p_country_code IN ZX_FC_CODES_B.COUNTRY_CODE%TYPE,
2784: p_tax_event_class_code IN ZX_EVENT_CLASSES_VL.TAX_EVENT_CLASS_CODE%TYPE
2785: )
2786:
2787: IS
2806:
2807: CLOSE G_C_GET_TYPES_INFO;
2808:
2809: INSERT
2810: INTO ZX_FC_CODES_B
2811: (classification_type_code,
2812: classification_id,
2813: classification_code,
2814: effective_from,
2824: last_update_login,
2825: object_version_number)
2826: SELECT
2827: 'TRX_BUSINESS_CATEGORY', --classification_type_code
2828: zx_fc_codes_b_s.nextval, --classification_id
2829: p_classification_code, --classification_code
2830: sysdate, --effective_from
2831: null, --effective_to
2832: event.tax_event_class_code, --parent_classification_code
2839: sysdate,
2840: fnd_global.conc_login_id,
2841: 1
2842: FROM
2843: ZX_FC_CODES_B fc,
2844: ZX_EVENT_CLASSES_B event
2845: WHERE
2846: fc.classification_type_code = 'TRX_BUSINESS_CATEGORY'
2847: and fc.parent_classification_id is null
2847: and fc.parent_classification_id is null
2848: and fc.classification_code=event.tax_event_class_code
2849: and event.tax_event_class_code = p_tax_event_class_code
2850: and not exists -- this condition makes sure we dont duplicate data
2851: (select NULL from ZX_FC_CODES_B Codes where
2852: codes.classification_type_code = 'TRX_BUSINESS_CATEGORY'
2853: and codes.parent_classification_id =
2854: nvl(fc.classification_id,codes.parent_classification_id)
2855: and codes.classification_code = p_classification_code
2987: fc.classification_id as parent_fc_id,
2988: zx.classification_id as classification_id,
2989: L.language_code lang_code
2990: FROM
2991: ZX_FC_CODES_B fc,
2992: ZX_FC_CODES_B zx,
2993: ZX_EVENT_CLASSES_TL event,
2994: FND_LANGUAGES L
2995: WHERE
2988: zx.classification_id as classification_id,
2989: L.language_code lang_code
2990: FROM
2991: ZX_FC_CODES_B fc,
2992: ZX_FC_CODES_B zx,
2993: ZX_EVENT_CLASSES_TL event,
2994: FND_LANGUAGES L
2995: WHERE
2996: fc.classification_type_code = 'TRX_BUSINESS_CATEGORY'
3461: /* PROCEDURE USED TO INSERT THE FC TYPES/CODES FOR AP ENTITIES */
3462:
3463: PROCEDURE ZX_MIGRATE_AP IS
3464:
3465: l_fc_id zx_fc_codes_b.classification_id%type;
3466:
3467: l_structure_id mtl_category_sets_b.structure_id%TYPE;
3468: l_category_status VARCHAR2(200);
3469: l_category_set mtl_category_sets_b.Category_set_ID%TYPE;
3606:
3607: arp_util_tax.debug( 'Create the Document Subtype for Fiscal Type : FRANCE ');
3608:
3609: INSERT
3610: INTO ZX_FC_CODES_B (
3611: classification_type_code,
3612: classification_id,
3613: classification_code,
3614: effective_from,
3624: last_update_login,
3625: object_version_number)
3626: SELECT
3627: 'DOCUMENT_SUBTYPE',
3628: zx_fc_codes_b_s.nextval,
3629: flex_value,
3630: nvl(start_date_active,to_date('01/01/1951','DD/MM/YYYY')),
3631: end_date_active,
3632: 'DEDUCTION TAX RULE',--parent_classification_code
3645: WHERE v.FLEX_VALUE_SET_ID = vs.FLEX_VALUE_SET_id
3646: AND vs.FLEX_VALUE_SET_NAME ='JE_FR_TAX_RULE'
3647: AND v.enabled_flag = 'Y'
3648: AND NOT EXISTS
3649: (select NULL from ZX_FC_CODES_B Codes where
3650: codes.classification_type_code = 'DOCUMENT_SUBTYPE'
3651: and codes.parent_classification_id =
3652: nvl(l_fc_id,codes.parent_classification_id)
3653: and codes.classification_code = v.flex_value
3783: Codes.classification_id,
3784: L.language_code lang_code,
3785: v.enabled_flag
3786: FROM
3787: ZX_FC_CODES_B Codes,
3788: FND_FLEX_VALUES V,
3789: fnd_flex_value_sets vs,
3790: FND_LANGUAGES L
3791:
3832:
3833: arp_util_tax.debug( 'Create a Code under the Document Subtype Fiscal Type for country : CHILE ');
3834:
3835: INSERT
3836: INTO ZX_FC_CODES_B (
3837: classification_type_code,
3838: classification_id,
3839: classification_code,
3840: effective_from,
3850: last_update_login,
3851: object_version_number)
3852: SELECT
3853: 'DOCUMENT_SUBTYPE',
3854: zx_fc_codes_b_s.nextval,
3855: lookup_code,
3856: nvl(start_date_active,to_date('01/01/1951','DD/MM/YYYY')),
3857: end_date_active,
3858: 'DOCUMENT TYPE',--parent_classification_code
3870: WHERE
3871:
3872: lookups.lookup_type='JLCL_AP_DOCUMENT_TYPE'
3873: AND NOT EXISTS -- this condition makes sure we dont duplicate data
3874: (select NULL from ZX_FC_CODES_B Codes where
3875: codes.classification_type_code = 'DOCUMENT_SUBTYPE'
3876: and codes.parent_classification_id = nvl(l_fc_id,codes.parent_classification_id)
3877: and codes.classification_code = lookups.lookup_code
3878: );
4008: language,
4009: lv.enabled_flag,
4010: classification_id
4011: FROM
4012: ZX_FC_CODES_B codes,
4013: FND_LOOKUP_VALUES LV,
4014: FND_LANGUAGES L
4015: WHERE
4016: codes.classification_type_code = G_CLASSIFICATION_TYPE_CODE
4131:
4132: -- Create the Second Level of Classification Codes for Transaction Condition Class
4133: arp_util_tax.debug( 'Create the Second Level of Classification Codes for Transaction Condition Class');
4134: INSERT
4135: INTO ZX_FC_CODES_B
4136: (classification_type_code,
4137: classification_id,
4138: classification_code,
4139: effective_from,
4149: last_update_login,
4150: object_version_number)
4151: SELECT
4152: 'TRX_BUSINESS_CATEGORY', --classification_type_code
4153: zx_fc_codes_b_s.nextval, --classification_id
4154: lookups.fc_code, --classification_code
4155: lookups.effective_from, --effective_from
4156: lookups.effective_to, --effective_to
4157: event.tax_event_class_code, --parent_classification_code
4166: 1
4167:
4168: FROM
4169:
4170: ZX_FC_CODES_B fc,
4171: ZX_EVENT_CLASSES_VL event,
4172: (SELECT
4173: lookups.LOOKUP_CODE fc_code,
4174: nvl(START_DATE_ACTIVE,to_date('01/01/1951','DD/MM/YYYY')) effective_from,
4184: and fc.parent_classification_id is null
4185: and fc.classification_code=event.tax_event_class_code
4186: and event.tax_event_class_code = 'SALES_TRANSACTION'
4187: AND NOT EXISTS -- this condition makes sure we dont duplicate data
4188: (select NULL from ZX_FC_CODES_B Codes where
4189: codes.classification_type_code = 'TRX_BUSINESS_CATEGORY'
4190: and codes.parent_classification_id = fc.classification_id
4191: and codes.classification_code = lookups.fc_code
4192: );
4330: codes.classification_id,
4331: codes.enabled_flag
4332: FROM
4333:
4334: ZX_FC_CODES_B fc,
4335: ZX_EVENT_CLASSES_VL event,
4336:
4337: (SELECT
4338: FL.LOOKUP_CODE fc_code,
4344: Codes.classification_id,
4345: Codes.parent_classification_id,
4346: fl.enabled_flag
4347: FROM
4348: ZX_FC_CODES_b Codes,
4349: FND_LOOKUP_VALUES FL,
4350: FND_LANGUAGES L
4351: WHERE
4352: Codes.classification_type_code = 'TRX_BUSINESS_CATEGORY'
4611: l_language fnd_lookup_values.language%type;
4612: l_start_date_active fnd_lookup_values.start_date_active%type;
4613: l_end_date_active fnd_lookup_values.end_date_active%type;
4614: l_source_lang fnd_lookup_values.source_lang%type;
4615: l_fc_id zx_fc_codes_b.classification_id%type;
4616: l_return_status varchar2(200);
4617: -- l_errorcode number;
4618: -- l_msg_count number;
4619: -- l_MSG_DATA varchar2(200);
4789:
4790: CLOSE G_C_GET_TYPES_INFO;
4791:
4792: INSERT
4793: INTO ZX_FC_CODES_B
4794: (classification_type_code,
4795: classification_id,
4796: classification_code,
4797: effective_from,
4806: last_update_date,
4807: last_update_login,
4808: object_version_number)
4809: (SELECT 'USER_DEFINED',
4810: zx_fc_codes_b_s.nextval,
4811: lookup_code, --classification_code
4812: nvl(start_date_active,sysdate), --effective_from
4813: end_date_active, --effective_to
4814: null,----parent_classification_code
4825: WHERE lookups.lookup_type='AR_TAX_TRX_BUSINESS_CATEGORY'
4826: AND LANGUAGE = userenv('LANG')
4827: AND NOT EXISTS
4828: -- this condition makes sure we dont duplicate data
4829: (select NULL from ZX_FC_CODES_B Codes where
4830: codes.classification_type_code = 'USER_DEFINED'
4831: and codes.parent_classification_id is null
4832: and codes.classification_code = lookups.lookup_code)
4833: );
4963: language,
4964: lv.enabled_flag,
4965: classification_id
4966: FROM
4967: ZX_FC_CODES_B Codes,
4968: FND_LOOKUP_VALUES LV,
4969: FND_LANGUAGES L
4970: WHERE
4971: Codes.classification_type_code = G_CLASSIFICATION_TYPE_CODE