eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

APPS.FII_BUDGET_FORECAST_C dependencies on FII_BUDGET_INTERFACE

Line 2048: -- This is for cases when user upload data to fii_budget_interface directly

2044: -- Procedure
2045: -- Id_Convert()
2046: -- Purpose
2047: -- Perform value to ID conversion on dimensional columns
2048: -- This is for cases when user upload data to fii_budget_interface directly
2049: -- and they insert flex values for the dimensional columns.
2050: -- Arguments
2051: -- None
2052: -- Example

Line 2063: FROM FII_BUDGET_INTERFACE i,

2059:
2060: CURSOR sob_cursor IS
2061: SELECT DISTINCT nvl(f.ledger_id, i.ledger_id),
2062: nvl(i.ledger, '-1')
2063: FROM FII_BUDGET_INTERFACE i,
2064: (SELECT DISTINCT sob.ledger_id, int.ledger
2065: FROM FII_BUDGET_INTERFACE int,
2066: GL_LEDGERS_PUBLIC_V sob
2067: WHERE (sob.ledger_id = int.ledger_id OR

Line 2065: FROM FII_BUDGET_INTERFACE int,

2061: SELECT DISTINCT nvl(f.ledger_id, i.ledger_id),
2062: nvl(i.ledger, '-1')
2063: FROM FII_BUDGET_INTERFACE i,
2064: (SELECT DISTINCT sob.ledger_id, int.ledger
2065: FROM FII_BUDGET_INTERFACE int,
2066: GL_LEDGERS_PUBLIC_V sob
2067: WHERE (sob.ledger_id = int.ledger_id OR
2068: sob.name = int.ledger)) f
2069: WHERE i.ledger = f.ledger(+);

Line 2074: FROM FII_BUDGET_INTERFACE i,

2070:
2071: CURSOR co_cursor IS
2072: SELECT DISTINCT nvl(f.flex_value_id, i.company_id),
2073: nvl(i.company, '-1'), f.ledger_id
2074: FROM FII_BUDGET_INTERFACE i,
2075: (SELECT DISTINCT fv.flex_value_id, int.company, int.ledger_id
2076: FROM FII_BUDGET_INTERFACE int,
2077: FII_DIM_MAPPING_RULES r,
2078: FND_FLEX_VALUES fv,

Line 2076: FROM FII_BUDGET_INTERFACE int,

2072: SELECT DISTINCT nvl(f.flex_value_id, i.company_id),
2073: nvl(i.company, '-1'), f.ledger_id
2074: FROM FII_BUDGET_INTERFACE i,
2075: (SELECT DISTINCT fv.flex_value_id, int.company, int.ledger_id
2076: FROM FII_BUDGET_INTERFACE int,
2077: FII_DIM_MAPPING_RULES r,
2078: FND_FLEX_VALUES fv,
2079: GL_LEDGERS_PUBLIC_V sob
2080: WHERE r.dimension_short_name = 'FII_COMPANIES'

Line 2091: FROM FII_BUDGET_INTERFACE i,

2087:
2088: CURSOR cc_cursor IS
2089: SELECT DISTINCT nvl(f.flex_value_id, i.cost_center_id),
2090: nvl(i.cost_center, '-1'), f.ledger_id
2091: FROM FII_BUDGET_INTERFACE i,
2092: (SELECT DISTINCT fv.flex_value_id, int.cost_center, int.ledger_id
2093: FROM FII_BUDGET_INTERFACE int,
2094: FII_DIM_MAPPING_RULES r,
2095: FND_FLEX_VALUES fv,

Line 2093: FROM FII_BUDGET_INTERFACE int,

2089: SELECT DISTINCT nvl(f.flex_value_id, i.cost_center_id),
2090: nvl(i.cost_center, '-1'), f.ledger_id
2091: FROM FII_BUDGET_INTERFACE i,
2092: (SELECT DISTINCT fv.flex_value_id, int.cost_center, int.ledger_id
2093: FROM FII_BUDGET_INTERFACE int,
2094: FII_DIM_MAPPING_RULES r,
2095: FND_FLEX_VALUES fv,
2096: GL_LEDGERS_PUBLIC_V sob
2097: WHERE r.dimension_short_name = 'HRI_CL_ORGCC'

Line 2109: FII_BUDGET_INTERFACE int

2105: CURSOR ccc_org_cursor IS
2106: SELECT DISTINCT NVL(cccorg.ccc_org_id, -1),
2107: int.company_id, int.cost_center_id
2108: FROM FII_CCC_MGR_GT cccorg,
2109: FII_BUDGET_INTERFACE int
2110: WHERE cccorg.company_id (+) = int.company_id
2111: AND cccorg.cost_center_id (+) = int.cost_center_id
2112: AND int.company_cost_center_org_id IS NULL;
2113:

Line 2117: FROM FII_BUDGET_INTERFACE i,

2113:
2114: CURSOR fc_cursor IS
2115: SELECT DISTINCT nvl(f.flex_value_id, i.fin_category_id),
2116: nvl(i.fin_item, '-1'), f.ledger_id
2117: FROM FII_BUDGET_INTERFACE i,
2118: (SELECT DISTINCT fv.flex_value_id, int.fin_item, int.ledger_id
2119: FROM FII_DIM_MAPPING_RULES r,
2120: FII_BUDGET_INTERFACE int,
2121: FND_FLEX_VALUES fv,

Line 2120: FII_BUDGET_INTERFACE int,

2116: nvl(i.fin_item, '-1'), f.ledger_id
2117: FROM FII_BUDGET_INTERFACE i,
2118: (SELECT DISTINCT fv.flex_value_id, int.fin_item, int.ledger_id
2119: FROM FII_DIM_MAPPING_RULES r,
2120: FII_BUDGET_INTERFACE int,
2121: FND_FLEX_VALUES fv,
2122: GL_LEDGERS_PUBLIC_V sob
2123: WHERE r.dimension_short_name = 'GL_FII_FIN_ITEM'
2124: AND r.chart_of_accounts_id = sob.chart_of_accounts_id

Line 2135: FROM FII_BUDGET_INTERFACE i,

2131: -- Cursor used for User-Defined Dimension dimension
2132: CURSOR udd1_cursor IS
2133: SELECT DISTINCT nvl(f.flex_value_id, FIIBUUP_UNASSIGNED_UDD_ID),
2134: nvl(i.user_dim1, 'UNASSIGNED'), f.ledger_id
2135: FROM FII_BUDGET_INTERFACE i,
2136: (SELECT DISTINCT fv.flex_value_id, int.user_dim1 , int.ledger_id
2137: FROM FII_DIM_MAPPING_RULES r,
2138: FII_BUDGET_INTERFACE int,
2139: FND_FLEX_VALUES fv,

Line 2138: FII_BUDGET_INTERFACE int,

2134: nvl(i.user_dim1, 'UNASSIGNED'), f.ledger_id
2135: FROM FII_BUDGET_INTERFACE i,
2136: (SELECT DISTINCT fv.flex_value_id, int.user_dim1 , int.ledger_id
2137: FROM FII_DIM_MAPPING_RULES r,
2138: FII_BUDGET_INTERFACE int,
2139: FND_FLEX_VALUES fv,
2140: GL_LEDGERS_PUBLIC_V sob
2141: WHERE r.dimension_short_name = 'FII_USER_DEFINED_1'
2142: AND r.chart_of_accounts_id = sob.chart_of_accounts_id

Line 2152: FROM mtl_categories_tl m, fii_budget_interface int

2148:
2149: -- Cursor used for Product Category dimension
2150: CURSOR prod_cat_cursor IS
2151: SELECT DISTINCT m.category_id, int.product_code
2152: FROM mtl_categories_tl m, fii_budget_interface int
2153: WHERE m.description = int.product_code
2154: AND m.language = userenv('LANG');
2155:
2156: TYPE num_type IS TABLE OF NUMBER;

Line 2201: UPDATE fii_budget_interface int

2197: OPEN sob_cursor;
2198: FETCH sob_cursor BULK COLLECT INTO l_id_val_rec.l_id, l_id_val_rec.l_value;
2199:
2200: FORALL i in l_id_val_rec.l_id.FIRST .. l_id_val_rec.l_id.LAST
2201: UPDATE fii_budget_interface int
2202: SET int.ledger_id = l_id_val_rec.l_id(i)
2203: WHERE int.ledger = l_id_val_rec.l_value(i);
2204:
2205: CLOSE sob_cursor;

Line 2214: UPDATE fii_budget_interface int

2210: l_id_val_rec.l_value,
2211: l_id_val_rec.l_ledger_id;
2212:
2213: FORALL i in l_id_val_rec.l_id.FIRST .. l_id_val_rec.l_id.LAST
2214: UPDATE fii_budget_interface int
2215: SET int.company_id = l_id_val_rec.l_id(i)
2216: WHERE int.company = l_id_val_rec.l_value(i)
2217: AND int.ledger_id = l_id_val_rec.l_ledger_id(i);
2218:

Line 2228: UPDATE fii_budget_interface int

2224: l_id_val_rec.l_value,
2225: l_id_val_rec.l_ledger_id;
2226:
2227: FORALL i in l_id_val_rec.l_id.FIRST .. l_id_val_rec.l_id.LAST
2228: UPDATE fii_budget_interface int
2229: SET int.cost_center_id = l_id_val_rec.l_id(i)
2230: WHERE int.cost_center = l_id_val_rec.l_value(i)
2231: AND int.ledger_id = l_id_val_rec.l_ledger_id(i);
2232:

Line 2244: UPDATE fii_budget_interface int

2240: l_ccc_org_rec.l_cc_id;
2241:
2242: IF (l_ccc_org_rec.l_cc_id.FIRST IS NOT NULL) THEN
2243: FORALL i in l_ccc_org_rec.l_cc_id.FIRST .. l_ccc_org_rec.l_cc_id.LAST
2244: UPDATE fii_budget_interface int
2245: SET int.company_cost_center_org_id = l_ccc_org_rec.l_ccc_org_id(i)
2246: WHERE int.company_id = l_ccc_org_rec.l_com_id(i)
2247: AND int.cost_center_id = l_ccc_org_rec.l_cc_id(i);
2248: END IF;

Line 2259: UPDATE fii_budget_interface int

2255: l_id_val_rec.l_value,
2256: l_id_val_rec.l_ledger_id;
2257:
2258: FORALL i in l_id_val_rec.l_id.FIRST .. l_id_val_rec.l_id.LAST
2259: UPDATE fii_budget_interface int
2260: SET int.fin_category_id = l_id_val_rec.l_id(i)
2261: WHERE int.fin_item = l_id_val_rec.l_value(i)
2262: AND int.ledger_id = l_id_val_rec.l_ledger_id(i);
2263: CLOSE fc_cursor;

Line 2281: UPDATE fii_budget_interface int

2277: l_id_val_rec.l_value,
2278: l_id_val_rec.l_ledger_id;
2279:
2280: FORALL i in l_id_val_rec.l_id.FIRST .. l_id_val_rec.l_id.LAST
2281: UPDATE fii_budget_interface int
2282: SET (int.user_dim1_id, int.user_dim1,
2283: int.user_dim2_id, int.user_dim2) =
2284: (SELECT l_id_val_rec.l_id(i), l_id_val_rec.l_value(i),
2285: FIIBUUP_UNASSIGNED_UDD_ID, 'UNASSIGNED' from dual)

Line 2294: UPDATE fii_budget_interface int

2290:
2291: ELSE
2292: -- UDD1 is disabled. Update user_dim1 to UNASSIGNED and user_dim1_id to
2293: -- the pre-seeded unassigned ID.
2294: UPDATE fii_budget_interface int
2295: SET (int.user_dim1_id, int.user_dim1, int.user_dim2_id, int.user_dim2) =
2296: (SELECT FIIBUUP_UNASSIGNED_UDD_ID, 'UNASSIGNED',
2297: FIIBUUP_UNASSIGNED_UDD_ID, 'UNASSIGNED' from dual);
2298:

Line 2308: UPDATE fii_budget_interface int

2304: l_id_val_rec.l_value;
2305:
2306: IF (l_id_val_rec.l_id.FIRST IS NOT NULL) THEN
2307: FORALL i in l_id_val_rec.l_id.FIRST .. l_id_val_rec.l_id.LAST
2308: UPDATE fii_budget_interface int
2309: SET int.prod_category_id = l_id_val_rec.l_id(i)
2310: WHERE int.product_code = l_id_val_rec.l_value(i);
2311: END IF;
2312:

Line 2377: -- Validate all data in FII_BUDGET_INTERFACE and reports any

2373: --
2374: -- Procedure
2375: -- Validate
2376: -- Purpose
2377: -- Validate all data in FII_BUDGET_INTERFACE and reports any
2378: -- violations found:
2379: --
2380: -- 1. Validate records are either for budgets or forecast
2381: -- 2. Check if any record is missing any time period information

Line 2438: FROM FII_BUDGET_INTERFACE

2434:
2435: -- This is the cursor to print out invalid plan_type_code
2436: CURSOR planCursor IS
2437: SELECT plan_type_code, prim_amount_g
2438: FROM FII_BUDGET_INTERFACE
2439: WHERE plan_type_code NOT IN ('B', 'F')
2440: FOR UPDATE OF STATUS_CODE;
2441:
2442: -- This is the cursor to print out records with null time

Line 2445: FROM FII_BUDGET_INTERFACE

2441:
2442: -- This is the cursor to print out records with null time
2443: CURSOR csr_null_time_1 IS
2444: SELECT plan_type_code, prim_amount_g
2445: FROM FII_BUDGET_INTERFACE
2446: WHERE report_time_period IS NULL
2447: FOR UPDATE OF STATUS_CODE;
2448:
2449: CURSOR csr_null_time_2 IS

Line 2451: FROM FII_BUDGET_INTERFACE

2447: FOR UPDATE OF STATUS_CODE;
2448:
2449: CURSOR csr_null_time_2 IS
2450: SELECT plan_type_code, prim_amount_g
2451: FROM FII_BUDGET_INTERFACE
2452: WHERE plan_type_code = 'B'
2453: AND report_time_period IS NULL
2454: FOR UPDATE OF STATUS_CODE;
2455:

Line 2458: FROM FII_BUDGET_INTERFACE

2454: FOR UPDATE OF STATUS_CODE;
2455:
2456: CURSOR csr_null_time_3 IS
2457: SELECT plan_type_code, prim_amount_g
2458: FROM FII_BUDGET_INTERFACE
2459: WHERE plan_type_code = 'F'
2460: AND report_time_period IS NULL
2461: FOR UPDATE OF STATUS_CODE;
2462:

Line 2466: FROM FII_BUDGET_INTERFACE

2462:
2463: -- This is the cursor that print out all null ledger
2464: CURSOR csr_null_ledger_id IS
2465: SELECT plan_type_code, prim_amount_g
2466: FROM FII_BUDGET_INTERFACE
2467: WHERE ledger IS NULL
2468: AND ledger_id IS NULL
2469: FOR UPDATE OF STATUS_CODE;
2470:

Line 2475: FROM FII_BUDGET_INTERFACE b

2471: -- Validate that the ledger_id provided are valid and
2472: -- set up in FDS
2473: CURSOR ledgerCursor IS
2474: SELECT b.ledger, b.ledger_id
2475: FROM FII_BUDGET_INTERFACE b
2476: WHERE (b.ledger IS NOT NULL AND b.ledger_id IS NULL) OR
2477: (nvl(b.ledger_id, -1) NOT IN (
2478: SELECT ledger_id
2479: FROM FII_SLG_ASSIGNMENTS))

Line 2485: FROM FII_BUDGET_INTERFACE

2481:
2482: -- This is the cursor that print out all null company id
2483: CURSOR csr_null_com_id IS
2484: SELECT plan_type_code, prim_amount_g
2485: FROM FII_BUDGET_INTERFACE
2486: WHERE company IS NULL
2487: AND company_id IS NULL
2488: FOR UPDATE OF STATUS_CODE;
2489:

Line 2494: FROM FII_BUDGET_INTERFACE b

2490: -- This is the cursor that validates all company_id provided are
2491: -- from the value sets of the BSVs of the ledgers setup in FDS
2492: CURSOR comCursor IS
2493: SELECT b.company, b.company_id
2494: FROM FII_BUDGET_INTERFACE b
2495: WHERE (b.company IS NOT NULL AND b.company_id IS NULL) OR
2496: NOT EXISTS (
2497: SELECT '1'
2498: FROM FND_FLEX_VALUES fv,

Line 2521: FROM FII_BUDGET_INTERFACE

2517:
2518: -- This is the cursor that print out all null cost_center_id
2519: CURSOR csr_null_cc_id IS
2520: SELECT plan_type_code, prim_amount_g
2521: FROM FII_BUDGET_INTERFACE
2522: WHERE cost_center IS NULL
2523: AND cost_center_id IS NULL
2524: FOR UPDATE OF STATUS_CODE;
2525:

Line 2530: FROM FII_BUDGET_INTERFACE b

2526: -- This is the cursor that validates all cost_center_id provided are
2527: -- from the value sets of the cost center segment of ledgers setup in FDS
2528: CURSOR ccCursor IS
2529: SELECT b.cost_center, b.cost_center_id
2530: FROM FII_BUDGET_INTERFACE b
2531: WHERE (b.cost_center IS NOT NULL and b.cost_center_id IS NULL) OR
2532: NOT EXISTS (
2533: SELECT '1'
2534: FROM FND_FLEX_VALUES fv,

Line 2557: FROM FII_BUDGET_INTERFACE

2553:
2554: -- This is the cursor that print out all null fin_category_id
2555: CURSOR csr_null_fin_cat_id IS
2556: SELECT plan_type_code, prim_amount_g
2557: FROM FII_BUDGET_INTERFACE
2558: WHERE fin_item IS NULL
2559: AND fin_category_id IS NULL
2560: FOR UPDATE OF STATUS_CODE;
2561:

Line 2566: FROM FII_BUDGET_INTERFACE b

2562: -- This is the cursor that validates all fin_category_id
2563: -- provided are indeed defined in the system.
2564: CURSOR fincatCursor IS
2565: SELECT b.fin_item, b.fin_category_id
2566: FROM FII_BUDGET_INTERFACE b
2567: WHERE (b.fin_item IS NOT NULL AND b.fin_category_id IS NULL) OR
2568: NOT EXISTS (
2569: SELECT '1'
2570: FROM fnd_flex_values fv,

Line 2593: FROM FII_BUDGET_INTERFACE b

2589: -- This is the cursor that validates all product categories
2590: -- provided are indeed defined in the system.
2591: CURSOR prodCursor IS
2592: SELECT b.product_code, b.prod_category_id
2593: FROM FII_BUDGET_INTERFACE b
2594: WHERE (b.prod_category_id IS NULL AND b.product_code IS NOT NULL) OR
2595: (b.prod_category_id NOT IN
2596: (SELECT mck.category_id
2597: FROM MTL_CATEGORIES_KFV mck

Line 2605: FROM FII_BUDGET_INTERFACE b

2601: -- This is the cursor that validates all user_dim1_id
2602: -- provided are indeed defined in the system.
2603: CURSOR udd1Cursor IS
2604: SELECT b.user_dim1, b.user_dim1_id
2605: FROM FII_BUDGET_INTERFACE b
2606: WHERE (b.user_dim1 IS NOT NULL AND b.user_dim1_id IS NULL) OR
2607: (nvl(b.user_dim1_id, -1) NOT IN (
2608: SELECT fv.flex_value_id
2609: FROM fnd_flex_values fv,

Line 2629: FROM FII_BUDGET_INTERFACE

2625: -- currency. It also validates that the conversion rate is positive
2626: -- and non-zero.
2627: CURSOR rateCursor IS
2628: SELECT plan_type_code, prim_amount_g
2629: FROM FII_BUDGET_INTERFACE
2630: WHERE FIIBUUP_SEC_CURR_CODE is NOT NULL
2631: AND sec_amount_g is NULL
2632: AND (conversion_rate is NULL OR conversion_rate <= 0)
2633: FOR UPDATE OF STATUS_CODE;

Line 2643: FROM FII_BUDGET_INTERFACE

2639: SELECT plan_type_code, trunc(version_date),
2640: report_time_period,
2641: ledger_id, company_id, cost_center_id, fin_category_id,
2642: prod_category_id, user_dim1_id
2643: FROM FII_BUDGET_INTERFACE
2644: GROUP BY plan_type_code, trunc(version_date),
2645: report_time_period,
2646: ledger_id, company_id, cost_center_id, fin_category_id,
2647: prod_category_id, user_dim1_id

Line 2660: FROM FII_BUDGET_INTERFACE i

2656: SELECT i.plan_type_code, trunc(i.version_date),
2657: i.report_time_period, i.ledger_id,
2658: i.company_id, i.cost_center_id, i.fin_category_id,
2659: i.prod_category_id, i.user_dim1_id
2660: FROM FII_BUDGET_INTERFACE i
2661: WHERE ((trunc(i.version_date) < FIIBUUP_GLOBAL_START_DATE) OR
2662: (trunc(i.version_date) <
2663: (SELECT MAX(b.version_date)
2664: FROM FII_BUDGET_BASE b, FII_TIME_ENT_PERIOD p

Line 2683: FROM FII_BUDGET_INTERFACE i

2679: SELECT i.plan_type_code, trunc(i.version_date),
2680: i.report_time_period, i.ledger_id,
2681: i.company_id, i.cost_center_id, i.fin_category_id,
2682: i.prod_category_id, i.user_dim1_id
2683: FROM FII_BUDGET_INTERFACE i
2684: WHERE ((trunc(i.version_date) < FIIBUUP_GLOBAL_START_DATE) OR
2685: (trunc(i.version_date) <
2686: (SELECT MAX(b.version_date)
2687: FROM FII_BUDGET_BASE b, FII_TIME_ENT_QTR q

Line 2706: FROM FII_BUDGET_INTERFACE i

2702: SELECT i.plan_type_code, trunc(i.version_date),
2703: i.report_time_period, i.ledger_id,
2704: i.company_id, i.cost_center_id, i.fin_category_id,
2705: i.prod_category_id, i.user_dim1_id
2706: FROM FII_BUDGET_INTERFACE i
2707: WHERE ((trunc(i.version_date) < FIIBUUP_GLOBAL_START_DATE) OR
2708: (trunc(i.version_date) <
2709: (SELECT MAX(b.version_date)
2710: FROM FII_BUDGET_BASE b, FII_TIME_ENT_YEAR y

Line 2739: g_phase := 'Delete from FII_BUDGET_INTERFACE for not-null status_code';

2735: IF (FIIBUUP_DEBUG) THEN
2736: FII_UTIL.Write_Log ('Removing last processed records from the interface table');
2737: END IF;
2738:
2739: g_phase := 'Delete from FII_BUDGET_INTERFACE for not-null status_code';
2740: delete from FII_BUDGET_INTERFACE
2741: where status_code is not NULL;
2742:
2743: -- Update records as 'VALIDATED' and set the upload date to sysdate

Line 2740: delete from FII_BUDGET_INTERFACE

2736: FII_UTIL.Write_Log ('Removing last processed records from the interface table');
2737: END IF;
2738:
2739: g_phase := 'Delete from FII_BUDGET_INTERFACE for not-null status_code';
2740: delete from FII_BUDGET_INTERFACE
2741: where status_code is not NULL;
2742:
2743: -- Update records as 'VALIDATED' and set the upload date to sysdate
2744: IF (FIIBUUP_DEBUG) THEN

Line 2749: update FII_BUDGET_INTERFACE

2745: FII_UTIL.Write_Log ('Updating new records in the interface table');
2746: END IF;
2747:
2748: g_phase := 'Update all other records to status_code = VALIDATED and upload_date = sysdate';
2749: update FII_BUDGET_INTERFACE
2750: set (status_code, upload_date) = (SELECT 'VALIDATED', sysdate from dual);
2751:
2752: -- Bug fix 4943332: We don't need to count the rows of FII_BUDGET_INTERFACE
2753: -- as we can get the row count updated by previuos SQL

Line 2752: -- Bug fix 4943332: We don't need to count the rows of FII_BUDGET_INTERFACE

2748: g_phase := 'Update all other records to status_code = VALIDATED and upload_date = sysdate';
2749: update FII_BUDGET_INTERFACE
2750: set (status_code, upload_date) = (SELECT 'VALIDATED', sysdate from dual);
2751:
2752: -- Bug fix 4943332: We don't need to count the rows of FII_BUDGET_INTERFACE
2753: -- as we can get the row count updated by previuos SQL
2754: IF (SQL%ROWCOUNT = 0) THEN
2755: FII_MESSAGE.Write_Log (msg_name => 'FII_BUD_NO_RECS', token_num => 0);
2756: retcode := 'W';

Line 2786: UPDATE FII_BUDGET_INTERFACE

2782: END IF;
2783:
2784: FII_UTIL.Write_Output (l_plan_code || ' ' || l_prim_amount_g);
2785:
2786: UPDATE FII_BUDGET_INTERFACE
2787: SET status_code = 'ERROR'
2788: WHERE CURRENT OF planCursor;
2789: End Loop;
2790:

Line 2828: UPDATE FII_BUDGET_INTERFACE

2824: END IF;
2825:
2826: FII_UTIL.Write_Output (l_plan_code || ' ' || l_prim_amount_g);
2827:
2828: UPDATE FII_BUDGET_INTERFACE
2829: SET Status_Code = 'ERROR'
2830: WHERE CURRENT OF csr_null_time_1;
2831: End Loop;
2832:

Line 2855: UPDATE FII_BUDGET_INTERFACE

2851: END IF;
2852:
2853: FII_UTIL.Write_Output (l_plan_code || ' ' || l_prim_amount_g);
2854:
2855: UPDATE FII_BUDGET_INTERFACE
2856: SET Status_Code = 'ERROR'
2857: WHERE CURRENT OF csr_null_time_2;
2858: End Loop;
2859:

Line 2883: UPDATE FII_BUDGET_INTERFACE

2879: END IF;
2880:
2881: FII_UTIL.Write_Output (l_plan_code || ' ' || l_prim_amount_g);
2882:
2883: UPDATE FII_BUDGET_INTERFACE
2884: SET Status_Code = 'ERROR'
2885: WHERE CURRENT OF csr_null_time_3;
2886: End Loop;
2887: END IF;

Line 2908: ' FROM FII_BUDGET_INTERFACE' ||

2904: END IF;
2905:
2906: l_sqlstmt := 'SELECT p1.report_time_period TIME ' ||
2907: 'FROM (SELECT distinct report_time_period ' ||
2908: ' FROM FII_BUDGET_INTERFACE' ||
2909: ' WHERE report_time_period is not NULL';
2910:
2911: IF (FIIBUUP_BUDGET_TIME_UNIT = FIIBUUP_FORECAST_TIME_UNIT) THEN
2912: l_sqlstmt := l_sqlstmt || ' ) p1, ';

Line 2986: Update FII_BUDGET_INTERFACE

2982: v1 => l_time_unit);
2983:
2984: --Update the Status_Code to 'ERROR'
2985:
2986: Update FII_BUDGET_INTERFACE
2987: Set Status_Code = 'ERROR'
2988: Where report_time_period = l_time_unit;
2989:
2990: END LOOP;

Line 3003: ' FROM FII_BUDGET_INTERFACE ' ||

2999: IF (FIIBUUP_BUDGET_TIME_UNIT <> FIIBUUP_FORECAST_TIME_UNIT) THEN
3000:
3001: l_sqlstmt := 'SELECT p2.report_time_period TIME ' ||
3002: 'FROM (SELECT distinct report_time_period ' ||
3003: ' FROM FII_BUDGET_INTERFACE ' ||
3004: ' WHERE report_time_period is not NULL ' ||
3005: ' AND plan_type_code = ''F'') p2, ';
3006:
3007: IF (FIIBUUP_FORECAST_TIME_UNIT = 'P') THEN

Line 3075: Update FII_BUDGET_INTERFACE

3071: t1 => 'TIME_PERIOD',
3072: v1 => l_time_unit);
3073:
3074: --Update the Status_Code to 'ERROR'
3075: Update FII_BUDGET_INTERFACE
3076: Set Status_Code = 'ERROR'
3077: Where report_time_period = l_time_unit;
3078:
3079: END LOOP;

Line 3124: UPDATE FII_BUDGET_INTERFACE

3120: END IF;
3121:
3122: FII_UTIL.Write_Output (l_plan_code || ' ' || l_prim_amount_g);
3123:
3124: UPDATE FII_BUDGET_INTERFACE
3125: SET status_code = 'ERROR'
3126: WHERE CURRENT OF csr_null_ledger_id;
3127: END LOOP;
3128: l_print_hdr1 := FALSE;

Line 3166: UPDATE FII_BUDGET_INTERFACE

3162: END IF;
3163:
3164: FII_UTIL.Write_Output (l_plan_code || ' ' || l_prim_amount_g);
3165:
3166: UPDATE FII_BUDGET_INTERFACE
3167: SET status_code = 'ERROR'
3168: WHERE CURRENT OF csr_null_com_id;
3169: END LOOP;
3170: l_print_hdr1 := FALSE;

Line 3208: UPDATE FII_BUDGET_INTERFACE

3204: END IF;
3205:
3206: FII_UTIL.Write_Output (l_plan_code || ' ' || l_prim_amount_g);
3207:
3208: UPDATE FII_BUDGET_INTERFACE
3209: SET status_code = 'ERROR'
3210: WHERE CURRENT OF csr_null_cc_id;
3211: END LOOP;
3212:

Line 3252: UPDATE FII_BUDGET_INTERFACE

3248: END IF;
3249:
3250: FII_UTIL.Write_Output (l_plan_code || ' ' || l_prim_amount_g);
3251:
3252: UPDATE FII_BUDGET_INTERFACE
3253: SET Status_Code = 'ERROR'
3254: WHERE CURRENT OF csr_null_fin_cat_id;
3255: End Loop;
3256: l_print_hdr1 := FALSE;

Line 3269: FROM FII_BUDGET_INTERFACE

3265: -- Bug fix 4943332: Changed to return 1 if any error row exists
3266: BEGIN
3267: SELECT 1
3268: INTO l_err_count
3269: FROM FII_BUDGET_INTERFACE
3270: WHERE status_code = 'ERROR'
3271: AND rownum = 1;
3272: EXCEPTION
3273: WHEN NO_DATA_FOUND THEN

Line 3324: UPDATE FII_BUDGET_INTERFACE

3320:
3321: -- print out individual invalid ledger id
3322: FII_UTIL.Write_Output (l_ledger_id || ' ' || l_ledger);
3323:
3324: UPDATE FII_BUDGET_INTERFACE
3325: SET status_code = 'ERROR'
3326: WHERE CURRENT OF ledgerCursor;
3327:
3328: END LOOP;

Line 3368: UPDATE FII_BUDGET_INTERFACE

3364:
3365: -- print out individual invalid company id
3366: FII_UTIL.Write_Output (l_com_id || ' ' || l_com);
3367:
3368: UPDATE FII_BUDGET_INTERFACE
3369: SET status_code = 'ERROR'
3370: WHERE CURRENT OF comCursor;
3371:
3372: END LOOP;

Line 3412: UPDATE FII_BUDGET_INTERFACE

3408:
3409: -- print out individual invalid cost center id
3410: FII_UTIL.Write_Output (l_cc_id || ' ' || l_cc);
3411:
3412: UPDATE FII_BUDGET_INTERFACE
3413: SET status_code = 'ERROR'
3414: WHERE CURRENT OF ccCursor;
3415:
3416: END LOOP;

Line 3455: UPDATE FII_BUDGET_INTERFACE

3451:
3452: -- print out individual invalid financial category id
3453: FII_UTIL.Write_Output (l_fin_cat_id || ' ' || l_fin_item);
3454:
3455: UPDATE FII_BUDGET_INTERFACE
3456: SET status_code = 'ERROR'
3457: WHERE CURRENT OF fincatCursor;
3458:
3459: END LOOP;

Line 3502: UPDATE FII_BUDGET_INTERFACE

3498:
3499: -- print out individual invalid product category id
3500: FII_UTIL.Write_Output (l_prod_cat_id || ' ' || l_prod_code);
3501:
3502: UPDATE FII_BUDGET_INTERFACE
3503: SET Status_Code = 'ERROR'
3504: WHERE CURRENT OF prodCursor;
3505:
3506: END LOOP;

Line 3549: UPDATE FII_BUDGET_INTERFACE

3545:
3546: -- print out individual invalid user defined dimension 1 ID
3547: FII_UTIL.Write_Output (l_udd1_id || ' ' || l_udd1);
3548:
3549: UPDATE FII_BUDGET_INTERFACE
3550: SET status_code = 'ERROR'
3551: WHERE CURRENT OF udd1Cursor;
3552:
3553: END LOOP;

Line 3596: UPDATE FII_BUDGET_INTERFACE

3592: END IF;
3593:
3594: FII_UTIL.Write_Output (l_plan_code || ' ' || l_prim_amount_g);
3595:
3596: UPDATE FII_BUDGET_INTERFACE
3597: SET Status_Code = 'ERROR'
3598: WHERE CURRENT OF rateCursor;
3599: End Loop;
3600: l_print_hdr1 := FALSE;

Line 3663: UPDATE FII_BUDGET_INTERFACE

3659: l_prod_cat_id || ' ' ||
3660: l_udd1_id);
3661:
3662: --Update the Status_Code
3663: UPDATE FII_BUDGET_INTERFACE
3664: SET status_code = 'ERROR'
3665: WHERE plan_type_code = l_plan_code
3666: AND ledger_id = l_ledger_id
3667: AND company_id = l_com_id

Line 3737: UPDATE FII_BUDGET_INTERFACE

3733: l_prod_cat_id || ' ' ||
3734: l_udd1_id);
3735:
3736: --Update the Status_Code
3737: UPDATE FII_BUDGET_INTERFACE
3738: SET status_code = 'ERROR'
3739: WHERE plan_type_code = l_plan_code
3740: AND trunc(version_date) = trunc(l_ver_date)
3741: AND report_time_period = l_time_unit

Line 3796: UPDATE FII_BUDGET_INTERFACE

3792: l_prod_cat_id || ' ' ||
3793: l_udd1_id);
3794:
3795: --Update the Status_Code
3796: UPDATE FII_BUDGET_INTERFACE
3797: SET status_code = 'ERROR'
3798: WHERE plan_type_code = l_plan_code
3799: AND trunc(version_date) = trunc(l_ver_date)
3800: AND report_time_period = l_time_unit

Line 3855: UPDATE FII_BUDGET_INTERFACE

3851: l_prod_cat_id || ' ' ||
3852: l_udd1_id);
3853:
3854: --Update the Status_Code
3855: UPDATE FII_BUDGET_INTERFACE
3856: SET status_code = 'ERROR'
3857: WHERE plan_type_code = l_plan_code
3858: AND trunc(version_date) = trunc(l_ver_date)
3859: AND report_time_period = l_time_unit

Line 3878: FROM FII_BUDGET_INTERFACE

3874: -- Bug fix 4943332: Changed to return 1 if any error row exists
3875: BEGIN
3876: SELECT 1
3877: INTO l_count
3878: FROM FII_BUDGET_INTERFACE
3879: WHERE status_code = 'ERROR'
3880: AND rownum = 1;
3881: EXCEPTION
3882: WHEN NO_DATA_FOUND THEN

Line 4051: 'FROM FII_BUDGET_INTERFACE bi, ';

4047: ' bi.fin_category_id, bi.prod_category_id, '||
4048: ' bi.user_dim1_id, -1, NULL, '||
4049: ' NULL, NULL, NULL, NULL, '||
4050: ' SYSDATE, :user_id, SYSDATE, :user_id, :login_id '||
4051: 'FROM FII_BUDGET_INTERFACE bi, ';
4052:
4053: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt || l_bud_time_tab_name;
4054: l_fc_sqlstmt := l_fc_sqlstmt || l_tmpstmt || l_fc_time_tab_name;
4055:

Line 4103: ' FROM FII_BUDGET_INTERFACE bi, '||

4099: ' bi.company_id, bi.cost_center_id, bi.fin_category_id, '||
4100: ' bi.prod_category_id, bi.user_dim1_id, '||
4101: ' -2, max(bb.version_date), NULL, NULL, NULL, NULL, '||
4102: ' SYSDATE, :user_id, SYSDATE, :user_id, :login_id '||
4103: ' FROM FII_BUDGET_INTERFACE bi, '||
4104: ' FII_BUDGET_BASE bb, ';
4105:
4106: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt || l_bud_time_tab_name;
4107: l_fc_sqlstmt := l_fc_sqlstmt || l_tmpstmt || l_fc_time_tab_name;

Line 4189: ' FROM FII_BUDGET_INTERFACE bi, FII_BUDGET_BASE bb, ';

4185: ' bi.prod_category_id, bi.user_dim1_id, '||
4186: ' -3, NULL, bb.prim_amount_total, bb.prim_amount_g, '||
4187: ' bb.sec_amount_total, bb.sec_amount_g, '||
4188: ' SYSDATE, :user_id, SYSDATE, :user_id, :login_id '||
4189: ' FROM FII_BUDGET_INTERFACE bi, FII_BUDGET_BASE bb, ';
4190:
4191: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt || l_bud_time_tab_name;
4192: l_fc_sqlstmt := l_fc_sqlstmt || l_tmpstmt || l_fc_time_tab_name;
4193:

Line 4262: ' FROM FII_BUDGET_INTERFACE bi, FII_BUDGET_BASE bb2, ';

4258: ' -4, bb2.version_date, bb2.prim_amount_total, '||
4259: ' bb2.prim_amount_g, '||
4260: ' bb2.sec_amount_total, bb2.sec_amount_g, '||
4261: ' SYSDATE, :user_id, SYSDATE, :user_id, :login_id '||
4262: ' FROM FII_BUDGET_INTERFACE bi, FII_BUDGET_BASE bb2, ';
4263:
4264: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt || l_bud_time_tab_name;
4265: l_fc_sqlstmt := l_fc_sqlstmt || l_tmpstmt || l_fc_time_tab_name;
4266:

Line 4285: l_tmpstmt := ', fii_budget_interface bi '||

4281:
4282: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt || l_bud_time_tab_name;
4283: l_fc_sqlstmt := l_fc_sqlstmt || l_tmpstmt || l_fc_time_tab_name;
4284:
4285: l_tmpstmt := ', fii_budget_interface bi '||
4286: ' WHERE bi.report_time_period = t.name '||
4287: ' AND bb.no_version_flag = ''Y'' ';
4288:
4289: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt ||

Line 4548: ' FROM FII_BUDGET_INTERFACE bi, ';

4544: ' bi.prod_category_id, '||
4545: ' bi.user_dim1_id, '||
4546: ' -5, NULL, NULL, NULL, NULL, NULL, '||
4547: ' SYSDATE, :user_id, SYSDATE, :user_id, :login_id '||
4548: ' FROM FII_BUDGET_INTERFACE bi, ';
4549:
4550: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt || l_bud_time_tab_name;
4551: l_fc_sqlstmt := l_fc_sqlstmt || l_tmpstmt || l_fc_time_tab_name;
4552:

Line 4607: ' FROM FII_BUDGET_INTERFACE bi, ';

4603: ' -6, bb2.version_date, bb2.prim_amount_total, '||
4604: ' bb2.prim_amount_g, bb2.sec_amount_total, '||
4605: ' bb2.sec_amount_g, '||
4606: ' SYSDATE, :user_id, SYSDATE, :user_id, :login_id '||
4607: ' FROM FII_BUDGET_INTERFACE bi, ';
4608:
4609: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt || l_bud_time_tab_name || ', ';
4610: l_fc_sqlstmt := l_fc_sqlstmt || l_tmpstmt || l_fc_time_tab_name || ', ';
4611:

Line 4631: l_tmpstmt := ', fii_budget_interface bi '||

4627:
4628: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt || l_bud_time_tab_name;
4629: l_fc_sqlstmt := l_fc_sqlstmt || l_tmpstmt || l_fc_time_tab_name;
4630:
4631: l_tmpstmt := ', fii_budget_interface bi '||
4632: ' WHERE bi.report_time_period = t.name ';
4633:
4634: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt ||
4635: ' AND t.' || l_bud_join_col_name || '= bb.time_id ';

Line 4944: 'FROM FII_BUDGET_INTERFACE i, FII_BUDGET_DELTAS d, FII_BUDGET_BASE b, ';

4940: ' - d.orig_sec_amount_total + d.orig_sec_amount_g '||
4941: '), '||
4942: ' DECODE(d.data_type, -4, trunc(i.version_date), NULL), '||
4943: ' d.data_type, ''N'' '||
4944: 'FROM FII_BUDGET_INTERFACE i, FII_BUDGET_DELTAS d, FII_BUDGET_BASE b, ';
4945:
4946: ELSE
4947: l_tmpstmt := ' DECODE (d.data_type, '||
4948: ' -5, DECODE(i.conversion_rate, '||

Line 4961: 'FROM FII_BUDGET_INTERFACE i, FII_BUDGET_DELTAS d, FII_BUDGET_BASE b,';

4957: ' ),'||
4958: ' DECODE(d.data_type, -6, trunc(i.version_date), NULL), '||
4959: ' d.data_type,'||
4960: ' ''Y'' '||
4961: 'FROM FII_BUDGET_INTERFACE i, FII_BUDGET_DELTAS d, FII_BUDGET_BASE b,';
4962: END IF;
4963:
4964: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt || l_bud_time_tab_name ||
4965: ' WHERE d.time_id = t.' || l_bud_join_col_name;

Line 6571: FROM fii_budget_interface

6567: l_ret_num NUMBER;
6568:
6569: CURSOR ver_date_cur IS
6570: SELECT DISTINCT trunc(version_date)
6571: FROM fii_budget_interface
6572: WHERE trunc(version_date) IS NOT NULL
6573: ORDER BY trunc(version_date);
6574:
6575: CURSOR null_ver_date IS

Line 6577: FROM fii_budget_interface

6573: ORDER BY trunc(version_date);
6574:
6575: CURSOR null_ver_date IS
6576: SELECT 'Y'
6577: FROM fii_budget_interface
6578: WHERE version_date IS NULL;
6579:
6580: BEGIN
6581: l_drop_bud_data := 'N';

Line 6766: /* fix by ilavenil, gather statistics on fii_budget_interface*/

6762: FIIBUUP_PURGE_EFF_DATE :=
6763: TO_DATE(X_Purge_Eff_Date, 'YYYY/MM/DD HH24:MI:SS');
6764: END IF;
6765:
6766: /* fix by ilavenil, gather statistics on fii_budget_interface*/
6767: IF (FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry,
6768: l_fii_schema)) THEN
6769: FND_STATS.GATHER_TABLE_STATS(
6770: OWNNAME => l_fii_schema,

Line 6771: TABNAME => 'FII_BUDGET_INTERFACE');

6767: IF (FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry,
6768: l_fii_schema)) THEN
6769: FND_STATS.GATHER_TABLE_STATS(
6770: OWNNAME => l_fii_schema,
6771: TABNAME => 'FII_BUDGET_INTERFACE');
6772:
6773: -- Bug 4674640: Should enable parallel dml after calling
6774: -- gather stats
6775: execute immediate 'alter session enable parallel dml';

Line 7329: FROM FII_BUDGET_INTERFACE

7325: -- Bug fix 4943332: Changed to return 1 if any row exists
7326: BEGIN
7327: SELECT 1
7328: INTO l_int_count
7329: FROM FII_BUDGET_INTERFACE
7330: WHERE rownum = 1;
7331: EXCEPTION
7332: WHEN NO_DATA_FOUND THEN
7333: l_int_count := 0;

Line 7347: -- Lock down FII_BUDGET_INTERFACE before processing.

7343:
7344: RETURN;
7345: END IF;
7346:
7347: -- Lock down FII_BUDGET_INTERFACE before processing.
7348: -- This is to prevent users from uploading new records into the
7349: -- interface while program is running.
7350: IF (FIIBUUP_DEBUG) THEN
7351: FII_MESSAGE.Write_Log

Line 7357: v2 => 'Locking down FII_BUDGET_INTERFACE...');

7353: token_num => 2,
7354: t1 => 'ROUTINE',
7355: v1 => 'Main()',
7356: t2 => 'ACTION',
7357: v2 => 'Locking down FII_BUDGET_INTERFACE...');
7358: END IF;
7359:
7360: g_phase := 'Locking down FII_BUDGET_INTERFACE';
7361: EXECUTE IMMEDIATE

Line 7360: g_phase := 'Locking down FII_BUDGET_INTERFACE';

7356: t2 => 'ACTION',
7357: v2 => 'Locking down FII_BUDGET_INTERFACE...');
7358: END IF;
7359:
7360: g_phase := 'Locking down FII_BUDGET_INTERFACE';
7361: EXECUTE IMMEDIATE
7362: 'Lock Table FII_BUDGET_INTERFACE in exclusive mode nowait';
7363:
7364: -- Start upload process

Line 7362: 'Lock Table FII_BUDGET_INTERFACE in exclusive mode nowait';

7358: END IF;
7359:
7360: g_phase := 'Locking down FII_BUDGET_INTERFACE';
7361: EXECUTE IMMEDIATE
7362: 'Lock Table FII_BUDGET_INTERFACE in exclusive mode nowait';
7363:
7364: -- Start upload process
7365: FII_BUDGET_FORECAST_C.Validate(l_ret_code);
7366: