[Home] [Help]
153: v_period_dr Number ;
154: vl_retcode Number ;
155: v_begin_amount number ;
156: v_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE ;
157: v_record_category fv_facts_temp.fct_int_record_category%TYPE ;
158: v_fiscal_yr Varchar2(25);
159: v_segment varchar2(30);
160: v_year_gtn2001 BOOLEAN ;
161: v_time_frame fv_treasury_symbols.time_frame%TYPE ;
164:
165: /*
166: Commented as not used
167: v_tbal_run_flag Varchar2(1) ;
168: v_tbal_indicator FV_FACTS_TEMP.TBAL_INDICATOR%TYPE ;
169: v_edit_check_code Number ;
170: v_debug varchar2(1) := NVL(FND_PROFILE.VALUE('FV_DEBUG_FLAG'),'N');
171: v_vl_main_cursor_found varchar2(1) := 'N' ;
172: v_code_combination_id gl_code_combinations.code_combination_id%TYPE;
441: 'Running Trial balance by fund fund range ' ||
442: vp_fund_low || ' ' || vp_fund_high) ;
443: End If ;
444:
445: fv_utility.log_mesg('Before deleting from FV_FACTS_TEMP ');
446: DELETE FROM fv_facts_temp
447: WHERE fct_int_record_type = 'TB';
448: COMMIT;
449: fv_utility.log_mesg('After deleting from FV_FACTS_TEMP AND BEFORE PROCESS_BY_FUND_RANGE ');
442: vp_fund_low || ' ' || vp_fund_high) ;
443: End If ;
444:
445: fv_utility.log_mesg('Before deleting from FV_FACTS_TEMP ');
446: DELETE FROM fv_facts_temp
447: WHERE fct_int_record_type = 'TB';
448: COMMIT;
449: fv_utility.log_mesg('After deleting from FV_FACTS_TEMP AND BEFORE PROCESS_BY_FUND_RANGE ');
450: PROCESS_BY_FUND_RANGE ;
445: fv_utility.log_mesg('Before deleting from FV_FACTS_TEMP ');
446: DELETE FROM fv_facts_temp
447: WHERE fct_int_record_type = 'TB';
448: COMMIT;
449: fv_utility.log_mesg('After deleting from FV_FACTS_TEMP AND BEFORE PROCESS_BY_FUND_RANGE ');
450: PROCESS_BY_FUND_RANGE ;
451: fv_utility.log_mesg('After PROCESS_BY_FUND_RANGE ');
452: fv_utility.log_mesg('vp_retcode :: '||vp_retcode);
453:
696: -- analyzed for reporting in the FACTS output file. After getting the
697: -- list of trasnactions that needs to be reported, it applies all the
698: -- FACTS attributes for the account number and perform further
699: -- processing for Legislative Indicator and Apportionment Category.
700: -- It populates the table FV_FACTS_TEMP for edit check process to
701: -- perform edit checks.
702: -- ------------------------------------------------------------------
703: PROCEDURE PROCESS_FACTS_TRANSACTIONS
704: IS
2485: END CALC_BALANCE ;
2486: -- -------------------------------------------------------------------
2487: -- PROCEDURE CREATE_FACTS_RECORD
2488: -- -------------------------------------------------------------------
2489: -- Inserts a new record into FV_FACTS_TEMP table with the current
2490: -- values from the global variables.
2491: -- ------------------------------------------------------------------
2492: PROCEDURE CREATE_FACTS_RECORD
2493: IS
2497: /*
2498: * Commented bY 7324248
2499: vl_exists Varchar2(1) ;
2500: v_ussgl_acct fv_facts_ussgl_accounts.ussgl_account%TYPE;
2501: v_excptn_cat fv_facts_temp.fct_int_record_category%TYPE;
2502: vl_enabled_flag fv_facts_ussgl_accounts.ussgl_enabled_flag%TYPE;
2503: vl_reporting_type fv_facts_ussgl_accounts.reporting_type%TYPE;
2504: */
2505: vl_fyr_segment_value fv_pya_fiscalyear_map.fyr_segment_value%type;
2502: vl_enabled_flag fv_facts_ussgl_accounts.ussgl_enabled_flag%TYPE;
2503: vl_reporting_type fv_facts_ussgl_accounts.reporting_type%TYPE;
2504: */
2505: vl_fyr_segment_value fv_pya_fiscalyear_map.fyr_segment_value%type;
2506: vl_parent_sgl_acct_num fv_facts_temp.parent_sgl_acct_number%TYPE;
2507: vl_reimb_agree_sel VARCHAR2(250);
2508: vl_reimb_agree_seg_val VARCHAR2(30);
2509:
2510: --Modifed for FV ER bug 8760767
2605: END IF;
2606: END IF;
2607: */
2608:
2609: INSERT INTO FV_FACTS_TEMP
2610: (code_combination_id,
2611: SGL_ACCT_NUMBER ,
2612: COHORT ,
2613: BEGIN_END ,
3227:
3228: vl_rollup_cursor := DBMS_SQL.OPEN_CURSOR;
3229:
3230: vl_rollup := '
3231: INSERT INTO FV_FACTS_TEMP
3232: (TREASURY_SYMBOL_ID ,
3233: SGL_ACCT_NUMBER ,
3234: COHORT ,
3235: INDEF_DEF_FLAG ,
3297: SUM(AMOUNT1),
3298: SUM(AMOUNT2),
3299: SUM(decode(begin_end , ''P'' , 0 , period_activity )) '
3300: || vl_group_by ||
3301: ' From FV_FACTS_TEMP fvt, gl_code_combinations glcc
3302: WHERE fct_int_record_category = ''REPORTED''
3303: AND fct_int_record_type = ''TB''
3304: AND tbal_fund_value = ' || '''' || v_fund_value || ''''
3305: || ' and glcc.code_combination_id = fvt.code_combination_id
3342: END IF;
3343:
3344: -- Delete the Detail Records that are used in rollup process
3345: /*
3346: DELETE FROM FV_FACTS_TEMP
3347: WHERE (fct_int_record_category = 'REPORTED'
3348: -- OR fct_int_record_category = 'REPORTED_NEW' )
3349: AND AMOUNT = 0 AND NVL(PERIOD_ACTIVITY,0) = 0
3350: AND treasury_symbol_id = v_treasury_symbol_id ) ;
3351: */
3352:
3353: --Bug 7324248
3354: --Delete rows which contain no amounts or 0 amounts
3355: DELETE FROM FV_FACTS_TEMP
3356: WHERE fct_int_record_category = 'REPORTED_NEW'
3357: AND (NVL(amount,0) = 0 AND
3358: NVL(period_activity,0) = 0 AND
3359: NVL(amount1,0) = 0 AND
3359: NVL(amount1,0) = 0 AND
3360: NVL(amount2,0) = 0)
3361: AND treasury_symbol_id = v_treasury_symbol_id;
3362:
3363: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'NO OF ROWS DELETED FROM FV_FACTS_TEMP '||SQL%ROWCOUNT) ;
3364:
3365: -- Set up Debit/Credit Indicator
3366: EXCEPTION
3367: WHEN OTHERS THEN
3882: l_module_name VARCHAR2(200) := g_module_name || 'process_cat_b_seq';
3883:
3884: CURSOR cat_b_cur(reported_type VARCHAR2) IS
3885: SELECT rowid, tbal_fund_value, sgl_acct_number, appor_cat_b_txt
3886: FROM fv_facts_temp
3887: WHERE fct_int_record_category = reported_type
3888: AND appor_cat_code = 'B'
3889: AND TRIM(appor_cat_b_txt) IS NOT NULL
3890: ORDER BY tbal_fund_value, sgl_acct_number, appor_cat_b_txt ;
3889: AND TRIM(appor_cat_b_txt) IS NOT NULL
3890: ORDER BY tbal_fund_value, sgl_acct_number, appor_cat_b_txt ;
3891:
3892: l_seq NUMBER;
3893: l_old_fund fv_facts_temp.tbal_fund_value%TYPE := '***';
3894: l_old_account fv_facts_temp.sgl_acct_number%TYPE := -99;
3895: l_old_cat_b_txt fv_facts_temp.appor_cat_b_txt%TYPE := '~~~';
3896: l_count NUMBER;
3897:
3890: ORDER BY tbal_fund_value, sgl_acct_number, appor_cat_b_txt ;
3891:
3892: l_seq NUMBER;
3893: l_old_fund fv_facts_temp.tbal_fund_value%TYPE := '***';
3894: l_old_account fv_facts_temp.sgl_acct_number%TYPE := -99;
3895: l_old_cat_b_txt fv_facts_temp.appor_cat_b_txt%TYPE := '~~~';
3896: l_count NUMBER;
3897:
3898: BEGIN
3891:
3892: l_seq NUMBER;
3893: l_old_fund fv_facts_temp.tbal_fund_value%TYPE := '***';
3894: l_old_account fv_facts_temp.sgl_acct_number%TYPE := -99;
3895: l_old_cat_b_txt fv_facts_temp.appor_cat_b_txt%TYPE := '~~~';
3896: l_count NUMBER;
3897:
3898: BEGIN
3899:
3916: ELSE
3917: l_seq := 1;
3918: END IF;
3919:
3920: UPDATE fv_facts_temp
3921: SET appor_cat_b_dtl = LPAD(to_char(l_seq), 3, '0')
3922: WHERE rowid = cat_b_rec.rowid;
3923:
3924: l_old_fund := cat_b_rec.tbal_fund_value;