[Home] [Help]
134: v_period_dr Number ;
135: vl_retcode Number ;
136: v_begin_amount number ;
137: v_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE ;
138: v_record_category fv_facts_temp.fct_int_record_category%TYPE ;
139: v_fiscal_yr Varchar2(25);
140: v_segment varchar2(30);
141: v_year_gtn2001 BOOLEAN ;
142: v_time_frame fv_treasury_symbols.time_frame%TYPE ;
142: v_time_frame fv_treasury_symbols.time_frame%TYPE ;
143: v_financing_acct fv_facts_federal_accounts.financing_account%TYPE ;
144: v_year_budget_auth VARCHAR2(3);
145: v_tbal_run_flag Varchar2(1) ;
146: v_tbal_indicator FV_FACTS_TEMP.TBAL_INDICATOR%TYPE ;
147: v_tbal_fund_value FV_FUND_PARAMETERS.FUND_VALUE%TYPE ;
148: v_tbal_acct_num varchar2(25);
149: v_edit_check_code Number ;
150: -- v_debug varchar2(1) := NVL(FND_PROFILE.VALUE('FV_DEBUG_FLAG'),'N');
391: 'Running Trial balance by fund fund range ' ||
392: vp_fund_low || ' ' || vp_fund_high) ;
393: End If ;
394:
395: DELETE FROM fv_facts_temp
396: WHERE fct_int_record_type = 'TB';
397: COMMIT;
398:
399: PROCESS_BY_FUND_RANGE ;
631: -- analyzed for reporting in the FACTS output file. After getting the
632: -- list of trasnactions that needs to be reported, it applies all the
633: -- FACTS attributes for the account number and perform further
634: -- processing for Legislative Indicator and Apportionment Category.
635: -- It populates the table FV_FACTS_TEMP for edit check process to
636: -- perform edit checks.
637: -- ------------------------------------------------------------------
638: PROCEDURE PROCESS_FACTS_TRANSACTIONS
639: IS
2350: END CALC_BALANCE ;
2351: -- -------------------------------------------------------------------
2352: -- PROCEDURE CREATE_FACTS_RECORD
2353: -- -------------------------------------------------------------------
2354: -- Inserts a new record into FV_FACTS_TEMP table with the current
2355: -- values from the global variables.
2356: -- ------------------------------------------------------------------
2357: PROCEDURE CREATE_FACTS_RECORD
2358: IS
2361: vl_disbursements_flag VARCHAR2(1);
2362: vl_exists Varchar2(1) ;
2363: vl_fyr_segment_value fv_pya_fiscalyear_map.fyr_segment_value%type;
2364: v_ussgl_acct fv_facts_ussgl_accounts.ussgl_account%TYPE;
2365: v_excptn_cat fv_facts_temp.fct_int_record_category%TYPE;
2366: vl_parent_sgl_acct_num fv_facts_temp.parent_sgl_acct_number%TYPE;
2367: vl_enabled_flag fv_facts_ussgl_accounts.ussgl_enabled_flag%TYPE;
2368: vl_reporting_type fv_facts_ussgl_accounts.reporting_type%TYPE;
2369:
2362: vl_exists Varchar2(1) ;
2363: vl_fyr_segment_value fv_pya_fiscalyear_map.fyr_segment_value%type;
2364: v_ussgl_acct fv_facts_ussgl_accounts.ussgl_account%TYPE;
2365: v_excptn_cat fv_facts_temp.fct_int_record_category%TYPE;
2366: vl_parent_sgl_acct_num fv_facts_temp.parent_sgl_acct_number%TYPE;
2367: vl_enabled_flag fv_facts_ussgl_accounts.ussgl_enabled_flag%TYPE;
2368: vl_reporting_type fv_facts_ussgl_accounts.reporting_type%TYPE;
2369:
2370: BEGIN
2398: END IF;
2399: END IF;
2400: END IF;
2401:
2402: INSERT INTO FV_FACTS_TEMP
2403: (code_combination_id,
2404: SGL_ACCT_NUMBER ,
2405: COHORT ,
2406: BEGIN_END ,
2996:
2997: vl_rollup_cursor := DBMS_SQL.OPEN_CURSOR;
2998:
2999: vl_rollup := '
3000: INSERT INTO FV_FACTS_TEMP
3001: (TREASURY_SYMBOL_ID ,
3002: SGL_ACCT_NUMBER ,
3003: COHORT ,
3004: INDEF_DEF_FLAG ,
3064: SUM(AMOUNT1),
3065: SUM(AMOUNT2),
3066: SUM(decode(begin_end , ''P'' , 0 , period_activity )) '
3067: || vl_group_by ||
3068: ' From FV_FACTS_TEMP fvt, gl_code_combinations glcc
3069: WHERE fct_int_record_category = ''REPORTED''
3070: AND fct_int_record_type = ''TB''
3071: AND tbal_fund_value = ' || '''' || v_fund_value || ''''
3072: || ' and glcc.code_combination_id = fvt.code_combination_id
3108: END IF;
3109:
3110: -- Delete the Detail Records that are used in rollup process
3111: /*
3112: DELETE FROM FV_FACTS_TEMP
3113: WHERE (fct_int_record_category = 'REPORTED'
3114: -- OR fct_int_record_category = 'REPORTED_NEW' )
3115: AND AMOUNT = 0 AND NVL(PERIOD_ACTIVITY,0) = 0
3116: AND treasury_symbol_id = v_treasury_symbol_id ) ;
3562: l_module_name VARCHAR2(200) := g_module_name || 'process_cat_b_seq';
3563:
3564: CURSOR cat_b_cur(reported_type VARCHAR2) IS
3565: SELECT rowid, tbal_fund_value, sgl_acct_number, appor_cat_b_txt
3566: FROM fv_facts_temp
3567: WHERE fct_int_record_category = reported_type
3568: AND appor_cat_code = 'B'
3569: AND TRIM(appor_cat_b_txt) IS NOT NULL
3570: ORDER BY tbal_fund_value, sgl_acct_number, appor_cat_b_txt ;
3569: AND TRIM(appor_cat_b_txt) IS NOT NULL
3570: ORDER BY tbal_fund_value, sgl_acct_number, appor_cat_b_txt ;
3571:
3572: l_seq NUMBER;
3573: l_old_fund fv_facts_temp.tbal_fund_value%TYPE := '***';
3574: l_old_account fv_facts_temp.sgl_acct_number%TYPE := -99;
3575: l_old_cat_b_txt fv_facts_temp.appor_cat_b_txt%TYPE := '~~~';
3576: l_count NUMBER;
3577:
3570: ORDER BY tbal_fund_value, sgl_acct_number, appor_cat_b_txt ;
3571:
3572: l_seq NUMBER;
3573: l_old_fund fv_facts_temp.tbal_fund_value%TYPE := '***';
3574: l_old_account fv_facts_temp.sgl_acct_number%TYPE := -99;
3575: l_old_cat_b_txt fv_facts_temp.appor_cat_b_txt%TYPE := '~~~';
3576: l_count NUMBER;
3577:
3578: BEGIN
3571:
3572: l_seq NUMBER;
3573: l_old_fund fv_facts_temp.tbal_fund_value%TYPE := '***';
3574: l_old_account fv_facts_temp.sgl_acct_number%TYPE := -99;
3575: l_old_cat_b_txt fv_facts_temp.appor_cat_b_txt%TYPE := '~~~';
3576: l_count NUMBER;
3577:
3578: BEGIN
3579:
3596: ELSE
3597: l_seq := 1;
3598: END IF;
3599:
3600: UPDATE fv_facts_temp
3601: SET appor_cat_b_dtl = LPAD(to_char(l_seq), 3, '0')
3602: WHERE rowid = cat_b_rec.rowid;
3603:
3604: l_old_fund := cat_b_rec.tbal_fund_value;