[Home] [Help]
PACKAGE BODY: APPS.XLA_XLAIQACL_UTILS_PKG
Source
1 PACKAGE BODY xla_xlaiqacl_utils_pkg AS
2 /* $Header: xlafuacl.pkb 120.2 2006/06/28 11:17:48 kprattip noship $ */
3
4 -- ************************************************************************
5 -- PUBLIC PROCEDURES
6 -- ************************************************************************
7
8 /*===========================================================================+
9 | PROCEDURE |
10 | calc_sums |
11 | |
12 | DESCRIPTION |
13 | Calculates dr and cr totals either for given transaction or for the |
14 | passed where clause for given application. The function totals the lines|
15 | from the view name passed in. |
16 | |
17 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
18 | none |
19 | |
20 | ARGUMENTS : IN: p_application_id -- E.g 222 for Receivables |
21 | p_trx_hdr_table -- Transaction header table |
22 | p_trx_hdr_id -- Transaction header id |
23 | p_cost_type_id -- Cost Type Id (Mfg PAC trx) |
24 | p_ovr_where_clause -- Overriding where clause |
25 | p_view_name -- View Name |
26 | p_add_col_name_1 -- Additional Column Name 1 |
27 | p_add_col_value_1 -- Additional Column Value 1 |
28 | p_add_col_name_2 -- Additional Column Name 2 |
29 | p_add_col_value_2 -- Additional Column Value 2 |
30 | OUT: x_total_entered_dr |
31 | x_total_entered_cr |
32 | x_total_accounted_dr |
33 | x_total_accounted_cr |
34 | NOTES |
35 | |
39 | support Mfg. PAC transactions. |
36 | MODIFICATION HISTORY |
37 | 06-Nov-98 Heli Lankinen Created |
38 | 04-Aug-99 Mahesh Sabapthy Added parameter cost_type_id to |
40 | 15-Sep-99 Dimple Shah Added parameters - |
41 | add_col_name_1, add_col_value_1, |
42 | add_col_name_2, add_col_value_2 |
43 | |
44 +===========================================================================*/
45 PROCEDURE CALC_SUMS (
46 p_application_id IN NUMBER,
47 p_set_of_books_id IN NUMBER,
48 p_trx_hdr_table IN VARCHAR2,
49 p_trx_hdr_id IN NUMBER,
50 p_cost_type_id IN NUMBER,
51 p_ovr_where_clause IN VARCHAR2,
52 p_view_name IN VARCHAR2,
53 p_add_col_name_1 IN VARCHAR2,
54 p_add_col_value_1 IN VARCHAR2,
55 p_add_col_name_2 IN VARCHAR2,
56 p_add_col_value_2 IN VARCHAR2,
57 x_total_entered_dr OUT NOCOPY NUMBER,
58 x_total_entered_cr OUT NOCOPY NUMBER,
59 x_total_accounted_dr OUT NOCOPY NUMBER,
60 x_total_accounted_cr OUT NOCOPY NUMBER ) IS
61
62 l_total_entered_dr NUMBER;
63 l_total_entered_cr NUMBER;
64 l_total_accounted_dr NUMBER;
65 l_total_accounted_cr NUMBER;
66
67 l_trx_hdr_table VARCHAR2(50);
68 l_trx_hdr_id NUMBER;
69
70 c INTEGER;
71 l_count_cur NUMBER;
72 select_statement VARCHAR2(3000);
73 select_clause VARCHAR2(3000);
74 where_clause VARCHAR2(3000);
75 rows NUMBER;
76
77 BEGIN
78
79 select_clause :=
80 ' SELECT SUM(entered_dr),
81 SUM(entered_cr),
82 SUM(accounted_dr),
83 SUM(accounted_cr),
84 COUNT(DISTINCT currency_code) '||
85 ' FROM '||p_view_name;
86
87 IF p_ovr_where_clause IS NOT NULL THEN
88 where_clause := p_ovr_where_clause ||
89 ' AND application_id = :l_appl_id '||
90 ' AND set_of_books_id = :l_sob_id ';
91 ELSE
92 /* Changed by Dimple. */
93 -- Standard where clause
94 where_clause := ' WHERE application_id = :l_appl_id '||
95 ' AND set_of_books_id = :l_sob_id ';
96
97 IF p_add_col_name_1 IS NOT NULL THEN
98 where_clause := where_clause||
99 ' AND '||p_add_col_name_1||' = :l_add_col_value_1 ';
100 END IF;
101
102 IF p_add_col_name_2 IS NOT NULL THEN
103 where_clause := where_clause||' '||
104 ' AND '||p_add_col_name_2||' = :l_add_col_value_2 ';
105 END IF;
106
107 IF p_trx_hdr_table IS NOT NULL THEN
108 where_clause := where_clause||
109 ' AND trx_hdr_table = :l_trx_hdr_table ';
110 END IF;
111
112 IF p_trx_hdr_id IS NOT NULL THEN
113 where_clause := where_clause||
114 ' AND trx_hdr_id = :l_trx_hdr_id ';
115 END IF;
116
117 -- Mfg PAC support: Filter based on cost_type_id for PAC transactions
118 IF ( p_cost_type_id IS NOT NULL ) THEN
119 where_clause := where_clause||
120 ' AND cost_type_id = :l_cost_type_id ';
121 END IF;
122 END IF;
123
124
125 -- Final Select Statement
126
127 select_statement := select_clause||' '||where_clause;
128
129 -- open cursor
130 c := dbms_sql.open_cursor;
131
132 -- parse cursor
133 dbms_sql.parse(c,select_statement,dbms_sql.v7);
134
135 -- bind variables
136 IF p_ovr_where_clause IS NULL THEN
137
138 IF p_add_col_name_1 IS NOT NULL THEN
139 dbms_sql.bind_variable(c,'l_add_col_value_1', p_add_col_value_1);
140 END IF;
141 IF p_add_col_name_2 IS NOT NULL THEN
142 dbms_sql.bind_variable(c,'l_add_col_value_2', p_add_col_value_2);
143 END IF;
144 IF p_trx_hdr_table IS NOT NULL THEN
145 dbms_sql.bind_variable(c,'l_trx_hdr_table', p_trx_hdr_table);
146 END IF;
147 IF p_trx_hdr_id IS NOT NULL THEN
148 dbms_sql.bind_variable(c,'l_trx_hdr_id', p_trx_hdr_id);
149 END IF;
150 IF p_cost_type_id IS NOT NULL THEN
151 dbms_sql.bind_variable(c,'l_cost_type_id', p_cost_type_id);
152 END IF;
153 END IF;
154
155 IF p_application_id IS NOT NULL THEN
156 dbms_sql.bind_variable(c,'l_appl_id', p_application_id);
157 END IF;
158 IF p_set_of_books_id IS NOT NULL THEN
159 dbms_sql.bind_variable(c,'l_sob_id', p_set_of_books_id);
160 END IF;
161
162 --define columns in select
163 dbms_sql.define_column(c,1,l_total_entered_dr);
164 dbms_sql.define_column(c,2,l_total_entered_cr);
165 dbms_sql.define_column(c,3,l_total_accounted_dr);
166 dbms_sql.define_column(c,4,l_total_accounted_cr);
167 dbms_sql.define_column(c,5,l_count_cur);
168
169 rows := dbms_sql.execute(c);
170 IF dbms_sql.fetch_rows(c) = 0 THEN
171
172 -- No rows retrieved
173 x_total_entered_dr := 0;
174 x_total_entered_cr := 0;
175 x_total_accounted_dr := 0;
176 x_total_accounted_cr := 0;
177
178 ELSE
179
180 dbms_sql.column_value(c,1,l_total_entered_dr);
181 dbms_sql.column_value(c,2,l_total_entered_cr);
182 dbms_sql.column_value(c,3,l_total_accounted_dr);
183 dbms_sql.column_value(c,4,l_total_accounted_cr);
184 dbms_sql.column_value(c,5,l_count_cur);
185
186 x_total_accounted_dr := l_total_accounted_dr;
190 x_total_entered_dr := l_total_entered_dr;
187 x_total_accounted_cr := l_total_accounted_cr;
188
189 IF l_count_cur = 1 THEN --single currency, show totals
191 x_total_entered_cr := l_total_entered_cr;
192 ELSE -- this is cross currency, no entered totals
193 x_total_entered_dr := NULL;
194 x_total_entered_cr := NULL;
195 END IF;
196
197 END IF;
198
199 --close cursor
200 dbms_sql.close_cursor(c);
201
202 EXCEPTION
203 WHEN OTHERS THEN
204 if ( dbms_sql.is_open(c) ) then
205 dbms_sql.close_cursor(c);
206 end if;
207 fnd_message.set_name('FND', 'FORM_UNHANDLED_EXCEPTION');
208 fnd_message.set_token('PROCEDURE', 'xla_xlaiqacl_total_pkg.calc_sums');
209 RAISE;
210
211 END CALC_SUMS;
212
213 /*===========================================================================+
214 | PROCEDURE |
215 | get_acct_method_info |
216 | |
217 | DESCRIPTION |
218 | Get Accounting methods and associated SOB info by product. |
219 +===========================================================================*/
220 PROCEDURE get_acct_method_info (
221 p_application_id IN NUMBER,
222 x_acct_method_info OUT NOCOPY acct_method_info_tbl ) IS
223
224 /* CURSOR ap_acct_method_sob_c IS
225 SELECT v.set_of_books_id, v.sob_type, v.accounting_method,
226 v.base_currency_code, v.name, sob.short_name,
227 ap.displayed_field
228 FROM ap_sob_info_v v, gl_sets_of_books sob, ap_lookup_codes ap
229 WHERE v.set_of_books_id <> -1
230 AND sob.set_of_books_id = v.set_of_books_id
231 AND v.accounting_method = ap.lookup_code
232 AND ap.lookup_type = 'ACCOUNTING BASIS METHOD';
233 */
234 -- Changed the select from ap_sob_info_v view to actual select from tables
235 -- since that view was owned by AP and changed to select reporting sobs too.
236
237 CURSOR ap_acct_method_sob_c IS
238 SELECT sob1.set_of_books_id set_of_books_id,
239 'Primary' sob_type,
240 sp1.accounting_method_option accounting_method,
241 sp1.base_currency_code base_currency_code,
242 sob1.name name ,
243 sob1.short_name short_name,
244 ap1.displayed_field displayed_field
245 FROM gl_sets_of_books sob1, ap_system_parameters sp1,
246 ap_lookup_codes ap1
247 WHERE sob1.set_of_books_id <> -1
248 AND ap1.lookup_code = sp1.accounting_method_option
249 AND ap1.lookup_type = 'ACCOUNTING BASIS METHOD'
250 AND sob1.set_of_books_id = sp1.set_of_books_id
251 UNION
252 SELECT sob2.set_of_books_id set_of_books_id,
253 'Secondary' sob_type,
254 sp2.secondary_accounting_method accounting_method,
255 sp2.base_currency_code base_currency_code,
256 sob2.name name ,
257 sob2.short_name short_name,
258 ap2.displayed_field displayed_field
259 FROM gl_sets_of_books sob2, ap_system_parameters sp2,
260 ap_lookup_codes ap2
261 WHERE sob2.set_of_books_id <> -1
262 AND ap2.lookup_code = sp2.secondary_accounting_method
263 AND ap2.lookup_type = 'ACCOUNTING BASIS METHOD'
264 AND sob2.set_of_books_id = sp2.secondary_set_of_books_id;
265
266
267 i BINARY_INTEGER := 1; -- table subscript
268 l_acct_method_info acct_method_info_tbl;
269 BEGIN
270 xla_util.debug('xla_xlaiqacl_utils_pkg.get_acct_method_info()+');
271
272 IF ( p_application_id = 200 ) THEN
273
274 FOR l_ap_acct_method_info_rec in ap_acct_method_sob_c LOOP
275
276 l_acct_method_info(i).accounting_method :=
277 l_ap_acct_method_info_rec.accounting_method;
278 l_acct_method_info(i).sob_id :=
279 l_ap_acct_method_info_rec.set_of_books_id;
280 l_acct_method_info(i).sob_curr :=
281 l_ap_acct_method_info_rec.base_currency_code;
282 l_acct_method_info(i).sob_type :=
283 substr(l_ap_acct_method_info_rec.sob_type,1,1);
284 l_acct_method_info(i).sob_name :=
285 l_ap_acct_method_info_rec.name;
286 l_acct_method_info(i).sob_short_name :=
287 l_ap_acct_method_info_rec.short_name;
288 l_acct_method_info(i).accounting_method_name :=
289 l_ap_acct_method_info_rec.displayed_field;
290
291 i := i + 1;
292
293 END LOOP;
294
295 END IF; -- AP?
296
297 -- Copy to Output
298 x_acct_method_info := l_acct_method_info;
299
300 xla_util.debug('xla_xlaiqacl_utils_pkg.get_acct_method_info()-');
301
302 EXCEPTION
303 WHEN OTHERS THEN
304 fnd_message.set_name('FND', 'FORM_UNHANDLED_EXCEPTION');
305 fnd_message.set_token('PROCEDURE', 'xla_xlaiqacl_total_pkg.get_acct_method_info');
306 RAISE;
307
308 END get_acct_method_info;
309
310 /*===========================================================================+
311 | PROCEDURE |
312 | get_acct_method_info_scalar |
313 | |
314 | DESCRIPTION |
315 | Gets the accounting methods and set of books info associated with the |
319 | none |
316 | accounting method for a given application. |
317 | |
318 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
320 | |
321 | ARGUMENTS : IN: p_application_id -- E.g 200 for Payables |
322 | OUT: |
323 | acct_method_n |
324 | sob_id_n |
325 | sob_curr_n |
326 | sob_type_n |
327 | sob_name_n |
328 | sob_short_name_n |
329 acct_method_name_n
330 | NOTES |
331 | |
332 | MODIFICATION HISTORY |
333 | 28-Apr-99 Mahesh Sabapathy Created |
334 +===========================================================================*/
335 PROCEDURE get_acct_method_info_scalar (
336 p_application_id IN NUMBER,
337 x_acct_method_1 OUT NOCOPY VARCHAR2,
338 x_sob_id_1 OUT NOCOPY NUMBER,
339 x_sob_curr_1 OUT NOCOPY VARCHAR2,
340 x_sob_type_1 OUT NOCOPY VARCHAR2,
341 x_sob_name_1 OUT NOCOPY VARCHAR2,
342 x_sob_short_name_1 OUT NOCOPY VARCHAR2,
343 x_acct_method_name_1 OUT NOCOPY VARCHAR2,
344 x_acct_method_2 OUT NOCOPY VARCHAR2,
345 x_sob_id_2 OUT NOCOPY NUMBER,
346 x_sob_curr_2 OUT NOCOPY VARCHAR2,
347 x_sob_type_2 OUT NOCOPY VARCHAR2,
348 x_sob_name_2 OUT NOCOPY VARCHAR2,
349 x_sob_short_name_2 OUT NOCOPY VARCHAR2,
350 x_acct_method_name_2 OUT NOCOPY VARCHAR2 ) IS
351
352 l_acct_method_info acct_method_info_tbl;
353 i BINARY_INTEGER;
354 BEGIN
355 -- Get Acct Method info
356 get_acct_method_info( p_application_id,
357 l_acct_method_info );
358
359 IF l_acct_method_info.COUNT > 0 THEN
360
361 -- Translate table to scalar parameters
362 FOR i in l_acct_method_info.FIRST .. l_acct_method_info.LAST LOOP
363
364 IF l_acct_method_info.EXISTS(i) THEN
365
366 -- First row
367 IF i = 1 AND l_acct_method_info(i).sob_id <> -1 THEN
368 x_acct_method_1 := l_acct_method_info(i).accounting_method;
369 x_sob_id_1 := l_acct_method_info(i).sob_id;
370 x_sob_curr_1 := l_acct_method_info(i).sob_curr;
371 x_sob_type_1 := l_acct_method_info(i).sob_type;
372 x_sob_name_1 := l_acct_method_info(i).sob_name;
373 x_sob_short_name_1 := l_acct_method_info(i).sob_short_name;
374 x_acct_method_name_1 := l_acct_method_info(i).accounting_method_name;
375 END IF;
376
377 -- Second row
378 IF i = 2 AND l_acct_method_info(i).sob_id <> -1 THEN
379 x_acct_method_2 := l_acct_method_info(i).accounting_method;
380 x_sob_id_2 := l_acct_method_info(i).sob_id;
381 x_sob_curr_2 := l_acct_method_info(i).sob_curr;
382 x_sob_type_2 := l_acct_method_info(i).sob_type;
383 x_sob_name_2 := l_acct_method_info(i).sob_name;
384 x_sob_short_name_2 := l_acct_method_info(i).sob_short_name;
385 x_acct_method_name_2 := l_acct_method_info(i).accounting_method_name;
386 END IF;
387
388 END IF;
389
390 END LOOP;
391
392 END IF;
393
394 EXCEPTION
395 WHEN OTHERS THEN
396 fnd_message.set_name('FND', 'FORM_UNHANDLED_EXCEPTION');
397 fnd_message.set_token('PROCEDURE', 'xla_xlaiqacl_total_pkg.get_acct_method_info_scalar');
398 RAISE;
399
400 END get_acct_method_info_scalar;
401
402 END xla_xlaiqacl_utils_pkg;