[Home] [Help]
PACKAGE BODY: APPS.FV_BE_UTIL_PKG
Source
4 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
1 package body fv_be_util_pkg AS
2 -- $Header: FVBEUTLB.pls 120.7.12010000.2 2009/06/17 16:37:59 sharoy ship $ |
3 g_module_name VARCHAR2(100) := 'fv.plsql.fv_be_util_pkg.';
5 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
6
7 g_errbuf varchar2(1000);
8 g_retcode number ;
9 g_sob_id gl_sets_of_books.set_of_books_id%TYPE;
10 g_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE;
11 g_gl_seg_num NUMBER(4);
12 g_n_segments NUMBER(4);
13 g_gl_seg_name fnd_id_flex_segments.application_column_name%TYPE;
14 g_gl_bal_seg_name fnd_id_flex_segments.application_column_name%TYPE;
15 g_gl_sec_initialized BOOLEAN := FALSE;
16
17
18 FUNCTION has_segments_access( p_bud_segments IN varchar2
19 ,p_ccid IN NUMBER
20 ,p_coa_id IN NUMBER
21 ,p_sob_id IN NUMBER) RETURN varchar2 IS
22 l_ccid NUMBER;
23 l_valid_flag BOOLEAN;
24 l_delim VARCHAR2(10);
25 l_num NUMBER;
26 l_module_name VARCHAR2(1000);
27 l_segarray fnd_flex_ext.segmentarray;
28 i NUMBER;
29 BEGIN
30 l_module_name := 'FV_BE_UTIL_PKG.has_segments_access';
31 l_valid_flag := FALSE;
35 IF p_ccid IS NULL THEN
32
33 -- if p_ccid is null then fetch the ccid using
34 -- fnd_flex_ext.get_combination_id()
36 FV_UTILITY.DEBUG_MESG(G_LEVEL_STATEMENT,l_module_name, 'ccid is NULL');
37 l_delim := fnd_flex_ext.get_delimiter(
38 'SQLGL'
39 ,'GL#'
40 ,p_coa_id);
41 FV_UTILITY.DEBUG_MESG(G_LEVEL_STATEMENT,l_module_name, 'Delimiter ' || l_delim);
42
43 l_num := fnd_flex_ext.breakup_segments(
44 p_bud_segments
45 ,l_delim
46 ,l_segarray);
47 IF l_num IS NULL THEN
48 FV_UTILITY.DEBUG_MESG(G_LEVEL_STATEMENT,l_module_name, 'fnd_flex_ext.breakup_segments() returned null');
49 END IF;
50
51 FOR i IN l_segarray.FIRST .. l_segarray.LAST
52 LOOP
53 FV_UTILITY.DEBUG_MESG(G_LEVEL_STATEMENT,l_module_name,'l_segarray('||i||') - '||l_segarray(i));
54 END LOOP;
55
56 FV_UTILITY.DEBUG_MESG(G_LEVEL_STATEMENT,l_module_name, 'p_coa_id is ' || p_coa_id);
57 l_valid_flag := fnd_flex_ext.get_combination_id
58 (application_short_name => 'SQLGL'
59 ,key_flex_code => 'GL#'
60 ,structure_number => p_coa_id
61 ,validation_date => SYSDATE
62 ,n_segments => l_num
63 ,segments => l_segarray
64 ,combination_id => l_ccid
65 ,data_set => -1
66 );
67 IF l_valid_flag = FALSE THEN
68 FV_UTILITY.DEBUG_MESG(G_LEVEL_STATEMENT, l_module_name,
69 'fnd_flex_ext.get_combination_id() ended with an error');
70 RETURN 'FALSE';
71 ELSE
72 FV_UTILITY.DEBUG_MESG(G_LEVEL_STATEMENT,l_module_name,
73 'The API call fnd_flex_ext.get_combination_id() completed');
74 FV_UTILITY.DEBUG_MESG(G_LEVEL_STATEMENT,l_module_name, 'ccid is ' || l_ccid);
75 END IF;
76
77 -- else we can directly call the API fnd_flex_ext.get_combination_id()
78 ELSE
79 FV_UTILITY.DEBUG_MESG(G_LEVEL_STATEMENT,l_module_name, 'ccid is not NULL');
80 l_ccid := p_ccid;
81 FV_UTILITY.DEBUG_MESG(G_LEVEL_STATEMENT,l_module_name, 'ccid: '||l_ccid);
82 END IF;
83
84 IF NOT g_gl_sec_initialized THEN
85 fv_utility.log_mesg('Initializing gl security package');
86 gl_security_pkg.init();
87 g_gl_sec_initialized := TRUE;
88 END IF;
89
90 --IF l_valid_flag THEN
91 -- gl_security_pkg.init();
92 IF gl_security_pkg.validate_access(p_sob_id, l_ccid)='TRUE' THEN
93 RETURN 'TRUE';
94 ELSE
95 RETURN 'FALSE';
96 END IF;
97 --ELSE
98 --fnd_message.debug('fnd_flex_ext.get_combination_id() ended with an error');
99 --FV_UTILITY.DEBUG_MESG(G_LEVEL_STATEMENT, l_module_name,'fnd_flex_ext.get_combination_id() ended with an error');
100 --RETURN 'FALSE';
101 --END IF;
102
103 EXCEPTION when others then
104 RAISE;
105 END has_segments_access;
106
107 -- BCPSA-BE Enhancements
108 -- Removed p_transaction_code parameter
109 -- Added p_transaction_type_id and p_sub_type parameters
110
111 procedure check_cross_validation ( errbuf OUT NOCOPY varchar2,
112 retcode OUT NOCOPY number,
113 p_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE,
114 p_header_segments fnd_flex_ext.SegmentArray,
115 p_detail_segments fnd_flex_ext.SegmentArray,
116 p_budget_level_id fv_be_trx_hdrs.budget_level_id%TYPE,
117 p_transaction_type_id fv_be_trx_dtls.transaction_type_id%TYPE,
118 p_sub_type fv_be_trx_dtls.sub_type%TYPE,
119 p_source fv_be_trx_hdrs.source%TYPE,
120 p_increase_decrease_flag fv_be_trx_dtls.increase_decrease_flag%TYPE)
121 is
122
123 l_module_name VARCHAR2(200) := g_module_name || 'check_cross_validation';
124 l_user_id NUMBER(15);
125 l_resp_id NUMBER(15);
126 l_errcode BOOLEAN;
127 l_valid_flag BOOLEAN;
128 l_dr_ccid NUMBER(15);
129 l_cr_ccid NUMBER(15);
130
131 l_gl_dr_segments fnd_flex_ext.SegmentArray;
132 l_gl_cr_segments fnd_flex_ext.SegmentArray;
133
134 l_dr_account gl_ussgl_account_pairs.dr_account_segment_value%TYPE;
135 l_cr_account gl_ussgl_account_pairs.cr_account_segment_value%TYPE;
136
137
138 -- BCPSA-BE Enhancements
139 -- Removed transcation_code_c cursor
140 -- Added accounts_cur cursor
141
142 CURSOR accounts_cur IS
143 SELECT cr_account_segment_value,
144 dr_account_segment_value
145 FROM fv_be_account_pairs
146 WHERE be_tt_id = p_transaction_type_id
147 AND nvl(sub_type, 'X') = nvl(p_sub_type, 'X')
148 AND chart_of_accounts_id = g_chart_of_accounts_id;
149
150 begin
151
152 g_chart_of_accounts_id := p_chart_of_accounts_id;
153
154 retcode := 0;
155 g_retcode := 0;
156
157 l_user_id := fnd_global.user_id;
158 l_resp_id := fnd_global.resp_id;
159
160 /*
161 fv_utility.Get_Context(l_user_id, l_resp_id, 'ACCT_SEGMENT',
162 g_gl_seg_name, l_errcode, errbuf);
163 */
164 fv_utility.Get_Segment_Col_Names(g_chart_of_accounts_id, g_gl_seg_name,
165 g_gl_bal_seg_name, l_errcode, errbuf);
166 if (l_errcode) then
167 retcode := 2;
168 return;
169 end if;
170
171 if (p_budget_level_id = 1) then
172
173 --Initialize both dr and cr arrays with values from document header
174
175 initialize_gl_segments(p_header_segments, l_gl_dr_segments);
176 initialize_gl_segments(p_header_segments, l_gl_cr_segments);
177
178 else
179 if (p_source = 'RPR') then
180
181 --Initialize both dr and cr arrays with values from document details
182 initialize_gl_segments(p_detail_segments, l_gl_dr_segments);
183 initialize_gl_segments(p_detail_segments, l_gl_cr_segments);
184
185 else
186 if (p_increase_decrease_flag = 'I') then
187
188 --Initialize dr arrays with values from document header
189 --Initialize cr arrays with values from document detail
190 initialize_gl_segments(p_header_segments, l_gl_dr_segments);
191 initialize_gl_segments(p_detail_segments, l_gl_cr_segments);
192
193 else
194
195 --Initialize cr arrays with values from document header
196 --Initialize dr arrays with values from document detail
197 initialize_gl_segments(p_header_segments, l_gl_cr_segments);
198 initialize_gl_segments(p_detail_segments, l_gl_dr_segments);
199
200 end if;--increase_decrease_flag
201
202 end if; --source 'RPR'
203
204 end if; --budget_level_id 1
205
206 if (g_retcode = 0) then
207
208 -- BCPSA-BE Enhancements
209 -- Removed transcation_code_c cursor
210 -- Added accounts_cur cursor
211
212 open accounts_cur;
213 loop
214 fetch accounts_cur
215 into l_cr_account, l_dr_account;
216
217 exit when accounts_cur%NOTFOUND or accounts_cur%NOTFOUND is NULL;
218
219 if (p_increase_decrease_flag = 'I') then
220 l_gl_cr_segments(g_gl_seg_num) := l_cr_account;
221 l_gl_dr_segments(g_gl_seg_num) := l_dr_account;
222 else
223 l_gl_cr_segments(g_gl_seg_num) := l_dr_account;
224 l_gl_dr_segments(g_gl_seg_num) := l_cr_account;
225 end if;
226
227 l_valid_flag := fnd_flex_ext.get_combination_id('SQLGL', 'GL#',
228 g_chart_of_accounts_id, SYSDATE, g_n_segments, l_gl_cr_segments,l_cr_ccid);
229 if (not l_valid_flag) then
230 retcode := 2;
231 errbuf := fnd_flex_ext.get_message;
232 return;
233 end if;
234 l_valid_flag := fnd_flex_ext.get_combination_id('SQLGL', 'GL#',
235 g_chart_of_accounts_id, SYSDATE, g_n_segments, l_gl_dr_segments,l_dr_ccid);
236 if (not l_valid_flag) then
237 retcode := 2;
238 errbuf := fnd_flex_ext.get_message;
239 return;
240 end if;
241 end loop;
242 close accounts_cur;
243
244 end if; --g_retcode = 0
245
246 retcode := g_retcode;
247 errbuf := g_errbuf;
248
249 exception when others then
250 retcode := 2;
251 errbuf:= 'Error in check_cross_validation procedure. SQL Error is '||sqlerrm;
252 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
253
254 end; --check_cross_validation
255
256
257 procedure initialize_gl_segments(p_from_segments IN fnd_flex_ext.SegmentArray,
258 p_to_segments OUT NOCOPY fnd_flex_ext.SegmentArray) is
259
260 l_module_name VARCHAR2(200) := g_module_name || 'initialize_gl_segments';
261
262 cursor flex_fields is
263 select application_column_name
264 from fnd_id_flex_segments
265 where id_flex_code = 'GL#'
266 and id_flex_num = g_chart_of_accounts_id
267 order by segment_num;
268
269 l_n_segments NUMBER(4);
270 l_column_name fnd_id_flex_segments.application_column_name%TYPE;
271 l_from_seg_num NUMBER(4);
272
273 begin
274
275 l_n_segments := 0;
276 for flex_fields_rec in flex_fields
277 loop
278 l_n_segments := l_n_segments + 1;
279 l_column_name := flex_fields_rec.application_column_name;
280 l_from_seg_num := substr(rtrim(l_column_name),8);
281
282 --Get the natural account segment column position in array
283
284 if (l_column_name = g_gl_seg_name) then
285 g_gl_seg_num := l_n_segments;
286 end if;
287
288 p_to_segments(l_n_segments) := p_from_segments(l_from_seg_num);
289 end loop;
290 g_n_segments := l_n_segments;
291
292 exception when others then
293 g_retcode := 2;
294 g_errbuf:= 'Error in initialize_gl_segments procedure. SQL Error is '||sqlerrm;
295 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_errbuf);
296
297 end; --initialize_gl_segments
298
299 end fv_be_util_pkg; -- Package body