[Home] [Help]
382:
383: ---------------------------------------------
384: -- Inserting records into GL CCID dimension
385: -- Two new dimensions UD1 and UD2 have been added for Expense Analysis.
386: -- Hence two new columns have been introduced in FII_GL_CCID_DIMENSIONS.
387: ---------------------------------------------
388: l_stmt := 'INSERT INTO FII_GL_CCID_DIMENSIONS (
389: code_combination_id,
390: chart_of_accounts_id,
384: -- Inserting records into GL CCID dimension
385: -- Two new dimensions UD1 and UD2 have been added for Expense Analysis.
386: -- Hence two new columns have been introduced in FII_GL_CCID_DIMENSIONS.
387: ---------------------------------------------
388: l_stmt := 'INSERT INTO FII_GL_CCID_DIMENSIONS (
389: code_combination_id,
390: chart_of_accounts_id,
391: company_id,
392: cost_center_id,
506:
507: execute immediate l_stmt;
508:
509: If g_debug_flag = 'Y' then
510: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' records into FII_GL_CCID_DIMENSIONS');
511: FII_UTIL.stop_timer;
512: FII_UTIL.print_timer('Duration');
513: FII_UTIL.Write_Log('');
514: End if;
522: -------------------------------------------------------
523: -- Do not need handle the case when company cost org ID
524: -- is NULL. For CCID which company cost org ID is NULL
525: -- we will insert -1 into the company cost org ID field
526: -- in FII_GL_CCID_DIMENSIONS table
527: -------------------------------------------------------
528:
529: WHEN OTHERS THEN
530: rollback;
563:
564: ---------------------------------------------
565: -- Inserting records into GL CCID dimension
566: -- Two new dimensions UD1 and UD2 have been added for Expense Analysis.
567: -- Hence two new columns have been introduced in FII_GL_CCID_DIMENSIONS.
568: ---------------------------------------------
569: l_stmt := 'INSERT /*+ append parallel(fii) */ INTO
570: FII_GL_CCID_DIMENSIONS fii (
571: code_combination_id,
566: -- Two new dimensions UD1 and UD2 have been added for Expense Analysis.
567: -- Hence two new columns have been introduced in FII_GL_CCID_DIMENSIONS.
568: ---------------------------------------------
569: l_stmt := 'INSERT /*+ append parallel(fii) */ INTO
570: FII_GL_CCID_DIMENSIONS fii (
571: code_combination_id,
572: chart_of_accounts_id,
573: company_id,
574: cost_center_id,
684:
685: EXECUTE IMMEDIATE l_stmt;
686:
687: If g_debug_flag = 'Y' then
688: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' records into FII_GL_CCID_DIMENSIONS');
689: FII_UTIL.stop_timer;
690: FII_UTIL.print_timer('Duration');
691: FII_UTIL.Write_Log('');
692: End if;
700:
701: EXCEPTION
702:
703: WHEN OTHERS THEN
704: FII_UTIL.TRUNCATE_TABLE('FII_GL_CCID_DIMENSIONS', g_fii_schema, g_retcode);
705: g_retcode := -1;
706: FII_UTIL.Write_Log('
707: -----------------------------
708: Error occured in Procedure: INSERT_INTO_CCID_DIM_INIT
735: -- Get the real max ccid that was inserted into CCID dimension
736: -- the g_new_max_ccid recorded at the beginning of the program
737: -- may not necessary be the largest CCID that was inserted.
738: -- New ccids could have been created while the program is
739: -- running. So record this max ccid from fii_gl_ccid_dimensions
740: --
741: -- Note that origianl g_new_max_ccid is from GL_CODE_COMBINATIONS,
742: --------------------------------------------------------------
743:
740: --
741: -- Note that origianl g_new_max_ccid is from GL_CODE_COMBINATIONS,
742: --------------------------------------------------------------
743:
744: g_phase := 'SELECT FROM fii_gl_ccid_dimensions';
745:
746: SELECT MAX(code_combination_id) INTO l_tmp_max_ccid
747: FROM fii_gl_ccid_dimensions;
748:
743:
744: g_phase := 'SELECT FROM fii_gl_ccid_dimensions';
745:
746: SELECT MAX(code_combination_id) INTO l_tmp_max_ccid
747: FROM fii_gl_ccid_dimensions;
748:
749: -- we should pick the larger one for g_new_max_ccid
750: -- between l_tmp_max_ccid and the original g_new_max_ccid
751: if g_new_max_ccid < l_tmp_max_ccid then
866: FII_UTIL.Write_Log(g_phase);
867: FII_UTIL.start_timer;
868: End if;
869:
870: UPDATE fii_gl_ccid_dimensions dim
871: SET dim.company_cost_center_org_id =
872: (SELECT NVL(gcc.company_cost_center_org_id, -1)
873: FROM gl_code_combinations gcc
874: WHERE gcc.code_combination_id = dim.code_combination_id)
875: WHERE dim.company_cost_center_org_id = -1;
876:
877: IF g_debug_flag = 'Y' then
878: FII_UTIL.Write_Log('Assigned CCC ORG ID to ' || SQL%ROWCOUNT
879: || ' CCIDs in FII_GL_CCID_DIMENSIONS');
880: FII_UTIL.stop_timer;
881: FII_UTIL.print_timer('Duration');
882: END IF;
883:
942: IF (g_mode = 'L') then
943:
944: --Clean up the CCID dimension table
945:
946: g_phase := 'TRUNCATE FII_GL_CCID_DIMENSIONS';
947:
948: FII_UTIL.TRUNCATE_TABLE('FII_GL_CCID_DIMENSIONS',g_fii_schema,g_retcode);
949:
950: --------------------------------------------------------------------------
944: --Clean up the CCID dimension table
945:
946: g_phase := 'TRUNCATE FII_GL_CCID_DIMENSIONS';
947:
948: FII_UTIL.TRUNCATE_TABLE('FII_GL_CCID_DIMENSIONS',g_fii_schema,g_retcode);
949:
950: --------------------------------------------------------------------------
951: --Bug 3205051: we should not force re-summarization for CCID initial load
952: -- This is wrong: Update FII_DIM_MAPPING_RULES to force
986: FROM gl_code_combinations;
987:
988: IF (g_new_max_ccid > g_max_ccid) THEN
989:
990: g_phase := 'Insert new CCIDs into FII_GL_CCID_DIMENSIONS table';
991: If g_debug_flag = 'Y' then
992: FII_UTIL.Write_Log(g_phase);
993: FII_UTIL.Write_Log('');
994: End if;
1136: --------------------------------------------------------
1137: --Will seed this in RSG
1138: -- FND_STATS.gather_table_stats
1139: -- (ownname => g_fii_schema,
1140: -- tabname => 'FII_GL_CCID_DIMENSIONS');
1141:
1142: If g_debug_flag = 'Y' then
1143: FII_MESSAGE.Func_Succ('FII_GL_CCID_C.INSERT_NEW_CCID');
1144: End if;
1150: if g_mode = 'L' then
1151:
1152: --program is run in Initial Load mode, truncate the table and reset LOG
1153:
1154: FII_UTIL.TRUNCATE_TABLE('FII_GL_CCID_DIMENSIONS',g_fii_schema,g_retcode);
1155:
1156: -- Bug 4152799.
1157: g_log_item := 'MAX_CCID';
1158:
1198: -----------------------------------------------------
1199: -- Product segment of the CCID records the product
1200: -- reporting classification
1201: -----------------------------------------------------
1202: l_stmt := 'UPDATE fii_gl_ccid_dimensions glcc
1203: SET (glcc.product_id, glcc.PROD_CATEGORY_ID) =
1204: (SELECT flx1.flex_value_id, mtc.category_id
1205: FROM gl_code_combinations glccd,
1206: mtl_categories mtc,
1226: EXECUTE IMMEDIATE l_stmt;
1227:
1228: If g_debug_flag = 'Y' then
1229: FII_UTIL.Write_Log('Updated Product Assignments for ' || SQL%ROWCOUNT
1230: || ' records in FII_GL_CCID_DIMENSIONS');
1231: FII_UTIL.stop_timer;
1232: FII_UTIL.print_timer('Duration');
1233: End if;
1234:
1447: End If;
1448:
1449:
1450: -------------------------------------------------------------
1451: -- Updating FII_GL_CCID_DIMENSIONS with product assignments information
1452: -------------------------------------------------------------
1453: g_phase := 'Updating FII_GL_CCID_DIMENSIONS to fix product assignments';
1454: If g_debug_flag = 'Y' then
1455: FII_UTIL.Write_Log(g_phase);
1449:
1450: -------------------------------------------------------------
1451: -- Updating FII_GL_CCID_DIMENSIONS with product assignments information
1452: -------------------------------------------------------------
1453: g_phase := 'Updating FII_GL_CCID_DIMENSIONS to fix product assignments';
1454: If g_debug_flag = 'Y' then
1455: FII_UTIL.Write_Log(g_phase);
1456: FII_UTIL.start_timer;
1457: End if;
1455: FII_UTIL.Write_Log(g_phase);
1456: FII_UTIL.start_timer;
1457: End if;
1458:
1459: UPDATE fii_gl_ccid_dimensions glcc
1460: SET glcc.PROD_CATEGORY_ID =
1461: (SELECT NVL(int.prod_category_id, glcc.prod_category_id)
1462: FROM fii_gl_ccid_prod_int int
1463: WHERE int.code_combination_id = glcc.code_combination_id
1465: WHERE glcc.chart_of_accounts_id = p_coa_id;
1466:
1467: If g_debug_flag = 'Y' then
1468: FII_UTIL.Write_Log('Updated Product Assignments for ' || SQL%ROWCOUNT
1469: || ' records in FII_GL_CCID_DIMENSIONS');
1470: FII_UTIL.stop_timer;
1471: FII_UTIL.print_timer('Duration');
1472: End if;
1473:
1508: If g_debug_flag = 'Y' then
1509: FII_MESSAGE.Func_Ent('FII_GL_CCID_C.INVALID_PROD_CODE_EXIST');
1510: End if;
1511:
1512: g_phase := 'Check for invalid product code in FII_GL_CCID_DIMENSIONS';
1513:
1514: ---------------------------------------------------------------------
1515: -- This function is called after the product id, product category
1516: -- mapping information has been entered into FII_GL_CCID_DIMENSIONS
1512: g_phase := 'Check for invalid product code in FII_GL_CCID_DIMENSIONS';
1513:
1514: ---------------------------------------------------------------------
1515: -- This function is called after the product id, product category
1516: -- mapping information has been entered into FII_GL_CCID_DIMENSIONS
1517: -- table. At this point, if PRODUCT_ID is populated but the
1518: -- the product category is not populated, then that would mean the
1519: -- the product code is invalid. Every product code should be
1520: -- mapped to a product category
1523: -- product_id is alway NULL
1524: ---------------------------------------------------------------------
1525: begin
1526: SELECT 1 INTO l_count
1527: FROM fii_gl_ccid_dimensions
1528: WHERE PRODUCT_ID IS NOT NULL
1529: AND PROD_CATEGORY_ID IS NULL
1530: AND rownum = 1;
1531: exception
1577: map.chart_of_accounts_id coa_id,
1578: NVL(map.application_column_name1,'NO_PROD_COLUMN') prod_seg,
1579: map.mapping_type_code assignment_type_code,
1580: 'FALSE' fact_resummarization_needed
1581: FROM fii_gl_ccid_dimensions gcc,
1582: fii_dim_mapping_rules map
1583: WHERE gcc.chart_of_accounts_id = map.chart_of_accounts_id
1584: AND map.dimension_short_name = g_dimension_name
1585: AND gcc.code_combination_id > g_max_ccid
1700: END IF;
1701:
1702: ----------------------------------------------------------------
1703: -- After product code and product category information
1704: -- have been inserted into FII_GL_CCID_DIMENSIONS table
1705: -- we will verify if there's any case of 'Invalid Product ID'.
1706: -- This situtation is when a CCID has 'product ID' populated,
1707: -- but there's no corresponding category ID. All valid
1708: -- product ID should be mapped to a category
2173: If g_debug_flag = 'Y' then
2174: FII_UTIL.Write_Log(g_phase);
2175: End if;
2176:
2177: FII_UTIL.TRUNCATE_TABLE ('FII_GL_CCID_DIMENSIONS', g_fii_schema, g_retcode);
2178:
2179: -------------------------------------------------------------------
2180: --Bug 3401590: should not update FII_DIM_MAPPING_RULES here
2181: --
2325: END IF;
2326:
2327: ----------------------------------------------------
2328: -- Find out what are the new CCIDs to process and
2329: -- insert these new CCIDs into FII_GL_CCID_DIMENSIONS
2330: -- table
2331: -----------------------------------------------------
2332: g_phase := 'Call INSERT_NEW_CCID';
2333:
2333:
2334: INSERT_NEW_CCID;
2335:
2336: ----------------------------------------------------
2337: -- Update FII_GL_CCID_DIMENSIONS table with Product
2338: -- assignment information for each CCID
2339: ----------------------------------------------------
2340: g_phase := 'Call MAINTAIN_PROD_ASSGN';
2341: