[Home] [Help]
PACKAGE BODY: APPS.ARRX_BSS
Source
1 PACKAGE BODY arrx_bss AS
2 /* $Header: ARRXBSB.pls 120.3 2006/04/24 12:00:11 ggadhams ship $ */
3
4 PROCEDURE arrxbss_report(p_request_id IN NUMBER
5 ,p_user_id IN NUMBER
6 ,p_reporting_level IN VARCHAR2
7 ,p_reporting_entity_id IN NUMBER
8 ,p_as_of_date IN DATE
9 ,retcode OUT NOCOPY NUMBER
10 ,errbuf OUT NOCOPY VARCHAR2) AS
11
12 -- Declare local variables
13 l_login_id NUMBER;
14 l_status VARCHAR2(4000);
15 l_amount VARCHAR2(4000);
16 l_applied VARCHAR2(4000);
17 l_org_where_trx VARCHAR2(4000);
18 l_org_where_ctt VARCHAR2(4000);
19 l_org_where_rah VARCHAR2(4000);
20 l_org_where_rah1 VARCHAR2(4000);
21 l_org_where_ps VARCHAR2(4000);
22 l_org_where_app VARCHAR2(4000);
23 l_books_id GL_SETS_OF_BOOKS.set_of_books_id%TYPE;
24 l_currency_code GL_SETS_OF_BOOKS.currency_code%TYPE;
25 l_sob_name GL_SETS_OF_BOOKS.name%TYPE;
26 l_count NUMBER;
27 l_balance_due NUMBER;
28 l_functional_balance_due NUMBER;
29 l_applied_amount NUMBER;
30 l_functional_applied_amount NUMBER;
31 l_new_ADR AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE;
32 l_new_acctd_ADR AR_PAYMENT_SCHEDULES.acctd_amount_due_remaining%TYPE;
33
34 -- Declare variables for Dynamic Cursors
35 v_CursorID_main INTEGER;
36 v_Dummy_main INTEGER;
37 v_CursorID_amt INTEGER;
38 v_Dummy_amt INTEGER;
39 v_CursorID_app INTEGER;
40 v_Dummy_app INTEGER;
41
42 -- Declare the variables which will hold the results of the SELECT statements
43 v_transaction_type RA_CUST_TRX_TYPES.name%TYPE;
44 v_transaction_type_id RA_CUST_TRX_TYPES.cust_trx_type_id%TYPE;
45 v_status AR_LOOKUPS.meaning%TYPE;
46 v_status_code AR_TRANSACTION_HISTORY.status%TYPE;
47 v_currency_code RA_CUSTOMER_TRX.invoice_currency_code%TYPE;
48 v_balance_due NUMBER;
49 v_functional_balance_due NUMBER;
50 v_customer_trx_id RA_CUSTOMER_TRX.customer_trx_id%TYPE;
51 v_ps_exchange_rate AR_PAYMENT_SCHEDULES.exchange_rate%TYPE;
52
53 BEGIN
54
55 -- Initialise status parameters
56 retcode := 2;
57 errbuf := 'Inner Package Failure';
58
59 -- Initialize MO Reporting
60 XLA_MO_REPORTING_API.Initialize(p_reporting_level, p_reporting_entity_id, 'AUTO');
61
62 -- Initialize the org parameters for the ALL tables
63 l_org_where_trx := XLA_MO_REPORTING_API.Get_Predicate('trx', null);
64 l_org_where_ctt := XLA_MO_REPORTING_API.Get_Predicate('ctt', null);
65 l_org_where_rah := XLA_MO_REPORTING_API.Get_Predicate('rah', null);
66 l_org_where_rah1 := XLA_MO_REPORTING_API.Get_Predicate('rah1', null);
67 l_org_where_ps := XLA_MO_REPORTING_API.Get_Predicate('ps', null);
68 l_org_where_app := XLA_MO_REPORTING_API.Get_Predicate('app', null);
69
70 -- Get the Login info
71 fnd_profile.get('LOGIN_ID', l_login_id);
72
73 -- Get functional currency
74
75 /* bug 2018415 replace fnd_profile call
76 fnd_profile.get(name => 'GL_SET_OF_BKS_ID',
77 val => l_books_id);
78 */
79
80 -- l_books_id := arp_global.sysparam.set_of_books_id;
81 --Bug 5041260 Seetting the Set Of Books Id Based on the Reporting Level
82 if p_reporting_level = 3000 then
83 select set_of_books_id
84 into l_books_id
85 from ar_system_parameters_all
86 where org_id = p_reporting_entity_id;
87 elsif p_reporting_level = 1000 then
88 l_books_id := p_reporting_entity_id;
89 end if ;
90
91
92
93 SELECT currency_code,
94 name
95 INTO l_currency_code,
96 l_sob_name
97 FROM gl_sets_of_books
98 WHERE set_of_books_id = l_books_id;
99
100 /*------------------------------------------------------------------+
101 | Create Cursors |
102 +------------------------------------------------------------------*/
103
104 -- This is the cursor at the highest level of summarising.
105 l_status := 'SELECT trx.invoice_currency_code
106 ,ctt.name transaction_type
107 ,ctt.cust_trx_type_id
108 ,rah.status
109 ,arl.meaning
110 FROM ra_cust_trx_types_all ctt
111 ,ra_customer_trx_all trx
112 ,ar_transaction_history_all rah
113 ,ar_lookups arl
114 WHERE trx.cust_trx_type_id = ctt.cust_trx_type_id
115 AND rah.customer_trx_id = trx.customer_trx_id
116 AND arl.lookup_code = rah.status
117 AND arl.lookup_type = ''TRANSACTION_HISTORY_STATUS'''||
118 'AND rah.transaction_history_id = (SELECT MAX(rah1.transaction_history_id)
119 FROM ar_transaction_history_all rah1
120 WHERE rah1.trx_date <= to_char(:b_status_date)' ||
121 l_org_where_rah1 ||
122 'AND rah1.customer_trx_id = trx.customer_trx_id)' ||
123 l_org_where_trx ||
124 l_org_where_ctt ||
125 l_org_where_rah ||
126 'AND rah.status <> ''INCOMPLETE'''||
127 'GROUP BY trx.invoice_currency_code
128 ,ctt.name
129 ,ctt.cust_trx_type_id
130 ,rah.status
131 ,arl.meaning';
132
133 l_amount := 'SELECT nvl(ps.amount_due_remaining,0)
134 ,nvl(ps.acctd_amount_due_remaining,0)
135 ,trx.customer_trx_id
136 ,ps.exchange_rate
137 FROM ra_cust_trx_types_all ctt
138 ,ra_customer_trx_all trx
139 ,ar_transaction_history_all rah
140 ,ar_payment_schedules_all ps
141 WHERE trx.cust_trx_type_id = ctt.cust_trx_type_id
142 AND rah.customer_trx_id = trx.customer_trx_id
143 AND rah.transaction_history_id = (SELECT MAX(rah1.transaction_history_id)
144 FROM ar_transaction_history_all rah1
145 WHERE rah1.trx_date <= to_char(:b_status_date)' ||
146 l_org_where_rah1 ||
147 'AND rah1.customer_trx_id = trx.customer_trx_id)' ||
148 l_org_where_trx ||
149 l_org_where_ctt ||
150 l_org_where_rah ||
151 l_org_where_ps ||
152 'AND trx.customer_trx_id = ps.customer_trx_id(+)
153 AND trx.invoice_currency_code = :b_currency_code '||
154 'AND rah.status = :b_status_code '||
155 'AND trx.cust_trx_type_id = :b_transaction_type_id ';
156
157 l_applied := 'SELECT nvl(app.amount_applied,0)
158 FROM ra_customer_trx_all trx,
159 ar_payment_schedules_all ps,
160 ar_receivable_applications_all app
161 WHERE trx.customer_trx_id = ps.customer_trx_id
162 AND trx.customer_trx_id = app.applied_customer_trx_id
163 AND app.applied_customer_trx_id = :b_trx_id '||
164 l_org_where_trx ||
165 l_org_where_ps ||
166 l_org_where_app ||
167 'AND app.status = ''APP'''||
168 'AND trunc(app.apply_date) > :b_as_of_date ';
169
170 /*------------------------------------------------------------------+
171 | Parse the main cursor |
172 +------------------------------------------------------------------*/
173
174 -- Open the cursor for dynamic processing.
175 v_CursorID_main := DBMS_SQL.OPEN_CURSOR;
176
177 -- Parse the main query.
178 DBMS_SQL.PARSE(v_CursorID_main, l_status, DBMS_SQL.native);
179
180
181 -- Bind variables for main cursor
182 DBMS_SQL.BIND_VARIABLE(v_CursorID_main, ':b_status_date', p_as_of_date);
183 -- If the MO Reporting Get Predicate function returns a bind variable then
184 -- we need to bind it.
185 IF l_org_where_trx like '%:p_reporting_entity_id%' THEN
186 DBMS_SQL.BIND_VARIABLE(v_CursorID_main, ':p_reporting_entity_id', p_reporting_entity_id);
187 END IF;
188
189
190 -- Define the output variables
191 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 1, v_currency_code, 15);
192 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 2, v_transaction_type, 20);
193 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 3, v_transaction_type_id);
194 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 4, v_status_code, 30);
195 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 5, v_status, 80);
196
197 -- Execute the statement. We're not concerned about the return
198 -- value, but we do need to declare a variable for it.
199 v_Dummy_main := DBMS_SQL.EXECUTE(v_CursorID_main);
200
201 -- This is the fetch loop.
202 LOOP
203
204 -- Fetch the rows into the buffer, and also check for the exit
205 -- condition from the loop.
206 IF DBMS_SQL.FETCH_ROWS(v_CursorID_main) = 0 THEN
207 EXIT;
208 END IF;
209
210 -- Retrieve the rows from the buffer into PL/SQL variables.
211 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 1, v_currency_code);
212 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 2, v_transaction_type);
213 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 3, v_transaction_type_id);
214 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 4, v_status_code);
215 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 5, v_status);
216
217 /*------------------------------------------------------------------+
218 | Amount cursor |
219 +------------------------------------------------------------------*/
220
221 -- Open the cursor for dynamic processing.
222 v_CursorID_amt := DBMS_SQL.OPEN_CURSOR;
223
224 -- Parse the amt query.
225 DBMS_SQL.PARSE(v_CursorID_amt, l_amount, DBMS_SQL.native);
226
227 -- Bind variables for amt cursor
228 DBMS_SQL.BIND_VARIABLE(v_CursorID_amt, ':b_status_date', p_as_of_date);
229 DBMS_SQL.BIND_VARIABLE(v_CursorID_amt, ':b_currency_code', v_currency_code);
230 DBMS_SQL.BIND_VARIABLE(v_CursorID_amt, ':b_status_code', v_status_code);
231 DBMS_SQL.BIND_VARIABLE(v_CursorID_amt, ':b_transaction_type_id', v_transaction_type_id);
232
233 -- If the MO Reporting Get Predicate function returns a bind variable then
234 -- we need to bind it.
235 IF l_org_where_trx like '%:p_reporting_entity_id%' THEN
236 DBMS_SQL.BIND_VARIABLE(v_CursorID_amt, ':p_reporting_entity_id', p_reporting_entity_id);
237 END IF;
238
239 -- Define the output variables
240 DBMS_SQL.DEFINE_COLUMN(v_CursorID_amt, 1, v_balance_due);
241 DBMS_SQL.DEFINE_COLUMN(v_CursorID_amt, 2, v_functional_balance_due);
242 DBMS_SQL.DEFINE_COLUMN(v_CursorID_amt, 3, v_customer_trx_id);
243 DBMS_SQL.DEFINE_COLUMN(v_CursorID_amt, 4, v_ps_exchange_rate);
244
245 -- Execute the statement.
246 v_Dummy_amt := DBMS_SQL.EXECUTE(v_CursorID_amt);
247
248 -- Initalise variables
249 l_balance_due := 0;
250 l_functional_balance_due := 0;
251 l_count := 0;
252
253 -- This is the fetch loop.
254 LOOP
255
256 -- Fetch the rows into the buffer, and also check for the exit
257 -- condition from the loop.
258 IF DBMS_SQL.FETCH_ROWS(v_CursorID_amt) = 0 THEN
259 EXIT;
260 END IF;
261
262 -- Retrieve the rows from the buffer into PL/SQL variables.
263 DBMS_SQL.COLUMN_VALUE(v_CursorID_amt, 1, v_balance_due);
264 DBMS_SQL.COLUMN_VALUE(v_CursorID_amt, 2, v_functional_balance_due);
265 DBMS_SQL.COLUMN_VALUE(v_CursorID_amt, 3, v_customer_trx_id);
266 DBMS_SQL.COLUMN_VALUE(v_CursorID_amt, 4, v_ps_exchange_rate);
267
268 l_balance_due := l_balance_due + v_balance_due;
269 l_functional_balance_due := l_functional_balance_due + v_functional_balance_due;
270 l_count := l_count +1;
271
272 /*------------------------------------------------------------------+
273 | Applied Amounts |
274 | |
275 | We currently have the open amounts for each record. We need to |
276 | add in any amounts that have been applied if they have been |
277 | applied since the as_of_date so to store the actual open amount |
278 | at the as_of_date. |
279 +------------------------------------------------------------------*/
280
281 l_applied_amount := null;
282 l_functional_applied_amount := null;
283
284 -- Open the cursor for dynamic processing.
285 v_CursorID_app := DBMS_SQL.OPEN_CURSOR;
286
287 -- Parse the amt query.
288 DBMS_SQL.PARSE(v_CursorID_app, l_applied, DBMS_SQL.native);
289
290 -- Bind variables for applied cursor
291 DBMS_SQL.BIND_VARIABLE(v_CursorID_app, ':b_as_of_date', p_as_of_date);
292 DBMS_SQL.BIND_VARIABLE(v_CursorID_app, ':b_trx_id', v_customer_trx_id);
293
294 -- If the MO Reporting Get Predicate function returns a bind variable then
295 -- we need to bind it.
296 IF l_org_where_trx like '%:p_reporting_entity_id%' THEN
297 DBMS_SQL.BIND_VARIABLE(v_CursorID_app, ':p_reporting_entity_id', p_reporting_entity_id);
298 END IF;
299
300 -- Define the output variables
301 DBMS_SQL.DEFINE_COLUMN(v_CursorID_app, 1, l_applied_amount);
302
303 -- Execute the statement.
304 v_Dummy_app := DBMS_SQL.EXECUTE(v_CursorID_app);
305
306 -- This is the fetch loop.
307 LOOP
308
309 -- Fetch the rows into the buffer, and also check for the exit
310 -- condition from the loop.
311 IF DBMS_SQL.FETCH_ROWS(v_CursorID_app) = 0 THEN
312 EXIT;
313 END IF;
314
315 -- Retrieve the rows from the buffer into PL/SQL variables.
316 DBMS_SQL.COLUMN_VALUE(v_CursorID_app, 1, l_applied_amount);
317
318 -- Add to open amounts
319 IF l_applied_amount IS NOT NULL THEN
320 l_balance_due := l_balance_due + l_applied_amount;
321
322 -- Ensure we calculate Functional Acctd Amount correctly
323 --Changed the call to arp_util.calc_accounted_amount intead of
324 -- arp_util.calc_acctd_amount for Bug 5041260
325 arp_util.calc_accounted_amount(
326 l_currency_code,
327 --NULL,
328 NULL,
329 NULL,
330 v_ps_exchange_rate,
331 '-',
332 v_balance_due,
333 v_functional_balance_due,
334 l_applied_amount,
335 l_new_ADR,
336 l_new_acctd_ADR,
337 l_functional_applied_amount);
338
339 l_functional_balance_due := l_functional_balance_due + l_functional_applied_amount;
340 END IF;
341
342 -- End loop for Applied Amounts
343 END LOOP;
344
345 IF DBMS_SQL.IS_OPEN(v_CursorID_app) THEN
346 DBMS_SQL.CLOSE_CURSOR(v_CursorID_app);
347 END IF;
348
349 -- End loop for Amounts
350 END LOOP;
351
352 IF DBMS_SQL.IS_OPEN(v_CursorID_amt) THEN
353 DBMS_SQL.CLOSE_CURSOR(v_CursorID_amt);
354 END IF;
355
356 /*------------------------------------------------------------------+
357 | Insert Data into Interface Table |
358 +------------------------------------------------------------------*/
359
360 -- Check if any records exist for the status being inserted
361 IF l_count > 0 THEN
362
363 -- Insert the fetched data into the Interface Table
364 INSERT INTO ar_br_status_sum_itf
365 (creation_date
366 ,created_by
367 ,last_update_login
368 ,last_update_date
369 ,last_updated_by
370 ,request_id
371 ,status
372 ,currency
373 ,balance_due
374 ,functional_balance_due
375 ,transaction_type
376 ,count
377 ,functional_currency_code
378 ,organization_name
379 )
380 VALUES
381 (sysdate
382 ,p_user_id
383 ,l_login_id
384 ,sysdate
385 ,p_user_id
386 ,p_request_id
387 ,v_status
388 ,v_currency_code
389 ,l_balance_due
390 ,l_functional_balance_due
391 ,v_transaction_type
392 ,l_count
393 ,l_currency_code
394 ,l_sob_name
395 );
396
397 END IF;
398
399 END LOOP;
400
401 -- Close the cursor.
402 DBMS_SQL.CLOSE_CURSOR(v_CursorID_main);
403
404 -- Update status variables to successful completion
405 retcode := 0;
406 errbuf := '';
407
408 -- Commit our work.
409 COMMIT;
410
411 EXCEPTION
412 WHEN OTHERS THEN
413 -- Close the cursors, then raise the error again.
414 IF DBMS_SQL.IS_OPEN (v_CursorID_amt) THEN
415 DBMS_SQL.CLOSE_CURSOR(v_CursorID_amt);
416 END IF;
417
418 IF DBMS_SQL.IS_OPEN (v_CursorID_app) THEN
419 DBMS_SQL.CLOSE_CURSOR(v_CursorID_app);
420 END IF;
421
422 DBMS_SQL.CLOSE_CURSOR(v_CursorID_main);
423 RAISE;
424 END arrxbss_report;
425
426 END arrx_bss;