[Home] [Help]
16:
17: -- New Variable declared by Surya on 04/30/98 to receive the value of
18: -- the passed quarter number
19: parm_gl_period_name gl_period_statuses.period_name%TYPE;
20: parm_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE;
21:
22: -- ------------------------------------
23: -- All Pre-build Query Variables
24: -- ------------------------------------
27: --
28: -- ------------------------------------
29: -- Stored Global Variables
30: -- ------------------------------------
31: g_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE;
32: g_federal_acct_symbol_id number(15);
33: g_insert_count NUMBER;
34: g_error_code NUMBER;
35: g_error_message VARCHAR2(400);
51: c_end_period VARCHAR2(40);
52:
53: -- New variables declared by Narsimha to get the resource type from fv_treasury_sybols.
54:
55: c_resource_type fv_treasury_symbols.resource_type%TYPE;
56: c_rescission_flag varchar2(10);
57:
58: -- New variables declared by Narsimha Balakkari on 04/07/99 to capture
59: -- Established Year and Cancellation Year for given treasury symbol
76: c_sf133_natural_bal_type fv_sf133_definitions_lines.sf133_natural_balance_type%TYPE;
77: c_sf133_line_category fv_sf133_definitions_lines.sf133_fund_category%TYPE;
78:
79: -- New variable declared by pkpatel to fix Bug 1575992
80: c_sf133_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE;
81:
82: -- new variabla declared by Narsimha.
83:
84: c_sf133_report_line_number fv_sf133_definitions_lines.sf133_report_line_number%TYPE;
103: o_sf133_column_amount fv_sf133_definitions_cols_temp.sf133_column_amount%TYPE;
104: o_sf133_amt_not_shown fv_sf133_definitions_cols_temp.sf133_amount_not_shown%TYPE;
105:
106: -- New variable declared by pkpatel to fix Bug 1575992
107: o_sf133_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE;
108: -- New Variables for using dynamic SQL
109: v_select VARCHAR2(30000);
110: v_cursor_id INTEGER;
111:
113: --Added ts_range_cursor as part of Enh #2129123
114: /* Cursor to select treasury symbols which fall in specified range */
115: CURSOR ts_range_cursor(tsymbol_r1 VARCHAR2,tsymbol_r2 VARCHAR2) IS
116: SELECT treasury_symbol,treasury_symbol_id
117: FROM fv_treasury_symbols
118: WHERE treasury_symbol BETWEEN tsymbol_r1 AND tsymbol_r2
119: AND time_frame ='SINGLE'
120: AND (fund_group_code NOT BETWEEN '3800' AND '3899')
121: AND (fund_group_code NOT BETWEEN '6001' AND '6999')
134: ORDER BY glflex.application_column_name;
135: --
136: -- ---------- Define Report Treasury Symbol Line Cursor -------------
137: -- MODIFIED BY SURYA ON 5/6/98 TO REPLACE FV_FUND_PARAMETERS WITH
138: -- FV_TREASURY_SYMBOLS
139:
140: -- Modified by Surya on 1/20/99 to add another join for SOB to fix
141: -- data duplication
142: --pkpatel :Changed to fix Bug 1575992
152: line.sf133_fund_category sf133_line_category,
153: line.sf133_report_line_number sf133_report_line_number
154:
155: FROM fv_sf133_definitions_lines line,
156: FV_TREASURY_SYMBOLS FTS
157: WHERE FTS.Treasury_symbol = parm_treasury_value_r1
158: AND FTS.set_of_books_id = parm_set_of_books_id
159: AND (line.sf133_line_type_code) IN ('T', 'D', 'S', 'N')
160: AND line.set_of_books_id = FTS.set_of_books_id
300: populate_gtt_with_ccid (parm_treasury_symbol_id);
301:
302: SELECT established_fiscal_yr, substr(cancellation_date,8,4)
303: INTO g_established_year, g_cancellation_year
304: FROM fv_treasury_symbols
305: WHERE treasury_symbol = parm_treasury_value_r1
306: AND set_of_books_id = parm_set_of_books_id ;
307: -- dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',parm_treasury_symbol_id);
308: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
312: --Fetch the Federal Acct Symbol Id for the TS
313:
314: SELECT federal_acct_symbol_id
315: INTO g_federal_acct_symbol_id
316: FROM fv_treasury_symbols
317: WHERE set_of_books_id = parm_set_of_books_id
318: AND treasury_symbol_id = parm_treasury_symbol_id;
319:
320: build_report_lines;
753: -- added time frame condition - 1633861
754: begin
755: select treasury_symbol,treasury_symbol_id
756: into c_sf133_ts_value,g_treasury_symbol_id
757: from fv_treasury_symbols
758: WHERE set_of_books_id = parm_set_of_books_id
759: and federal_acct_symbol_id = g_federal_acct_symbol_id
760: and established_fiscal_yr = l_loop_year
761: and time_frame = 'SINGLE';
943: c_rescission_flag := 'FALSE';
944: IF upper(c_sf133_additional_info) = 'RESCISSION' THEN
945:
946: select upper(resource_type) into c_resource_type
947: from fv_treasury_symbols
948: where treasury_symbol = parm_treasury_value_r1
949: and set_of_books_id = parm_set_of_books_id;
950:
951: IF c_resource_type like '%APPROPRIATION%' THEN