eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
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  |                                                                           |
36  | MODIFICATION HISTORY                                                      |
37  |     06-Nov-98  Heli Lankinen       Created                                |
38  |     04-Aug-99  Mahesh Sabapthy       Added parameter cost_type_id to      |
39  |                                      support Mfg. PAC transactions.       |
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;
187 	x_total_accounted_cr := l_total_accounted_cr;
188 
189 	IF l_count_cur = 1 THEN --single currency, show totals
190    	       x_total_entered_dr := l_total_entered_dr;
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   |
316  |   accounting method for a given application.                              |
317  |                                                                           |
318  | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED                                    |
319  |    none                                                                   |
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;