[Home] [Help]
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;
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,
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
984: and codes.ancestor_id = nvl(l_fc_id,codes.ancestor_id)
985: and codes.language = l.language_code);
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,
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);
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'
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,
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
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);
1633: FND_GLOBAL.CONC_LOGIN_ID,
1634: language,
1635: source_lang)
1636:
1637: INTO ZX_FC_CODES_DENORM_B
1638: (CLASSIFICATION_TYPE_ID,
1639: CLASSIFICATION_TYPE_CODE,
1640: CLASSIFICATION_TYPE_NAME,
1641: CLASSIFICATION_TYPE_CATEG_CODE,
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);
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,
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);
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:
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
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:
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,
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)
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'
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
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,
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
2773: and FL.language=L.language_code(+)
2774: and L.INSTALLED_FLAG in ('I', 'B')
2775: and NOT EXISTS -- this condition makes sure we dont duplicate data for U1
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
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'))
2919: and language = l.language_code
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,
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)
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,
3882: CLASSIFICATION_TYPE_CODE,
3883: CLASSIFICATION_TYPE_NAME,
3884: CLASSIFICATION_TYPE_CATEG_CODE,
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)
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,
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)
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,
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
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,
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);