[Home] [Help]
PACKAGE BODY: APPS.AR_CMGT_AGING
Source
1 PACKAGE BODY ar_cmgt_aging AS
2 /* $Header: ARCMAGEB.pls 120.5 2006/06/01 05:56:26 kjoshi noship $ */
3
4 --
5 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
6
7 pg_source_name VARCHAR2(30) := nvl(AR_CMGT_DATA_POINTS_PKG.g_source_name,'OCM');
8 pg_source_id VARCHAR2(45) := nvl(AR_CMGT_DATA_POINTS_PKG.g_source_id,-99);
9
10 PROCEDURE calc_aging_buckets (
11 p_party_id IN NUMBER,
12 p_customer_id IN NUMBER,
13 p_site_use_id IN NUMBER,
14 p_currency_code IN VARCHAR2,
15 p_credit_option IN VARCHAR2,
16 p_bucket_name IN VARCHAR2,
17 p_org_id IN NUMBER,
18 p_exchange_rate_type IN VARCHAR2,
19 p_source IN VARCHAR2 default NULL,
20 p_outstanding_balance IN OUT NOCOPY NUMBER,
21 p_bucket_titletop_0 OUT NOCOPY VARCHAR2,
22 p_bucket_titlebottom_0 OUT NOCOPY VARCHAR2,
23 p_bucket_amount_0 IN OUT NOCOPY NUMBER,
24 p_bucket_titletop_1 OUT NOCOPY VARCHAR2,
25 p_bucket_titlebottom_1 OUT NOCOPY VARCHAR2,
26 p_bucket_amount_1 IN OUT NOCOPY NUMBER,
27 p_bucket_titletop_2 OUT NOCOPY VARCHAR2,
28 p_bucket_titlebottom_2 OUT NOCOPY VARCHAR2,
29 p_bucket_amount_2 IN OUT NOCOPY NUMBER,
30 p_bucket_titletop_3 OUT NOCOPY VARCHAR2,
31 p_bucket_titlebottom_3 OUT NOCOPY VARCHAR2,
32 p_bucket_amount_3 IN OUT NOCOPY NUMBER,
33 p_bucket_titletop_4 OUT NOCOPY VARCHAR2,
34 p_bucket_titlebottom_4 OUT NOCOPY VARCHAR2,
35 p_bucket_amount_4 IN OUT NOCOPY NUMBER,
36 p_bucket_titletop_5 OUT NOCOPY VARCHAR2,
37 p_bucket_titlebottom_5 OUT NOCOPY VARCHAR2,
38 p_bucket_amount_5 IN OUT NOCOPY NUMBER,
39 p_bucket_titletop_6 OUT NOCOPY VARCHAR2,
40 p_bucket_titlebottom_6 OUT NOCOPY VARCHAR2,
41 p_bucket_amount_6 IN OUT NOCOPY NUMBER
42 ) IS
43 v_amount_due_remaining NUMBER;
44 v_bucket_0 NUMBER;
45 v_bucket_1 NUMBER;
46 v_bucket_2 NUMBER;
47 v_bucket_3 NUMBER;
48 v_bucket_4 NUMBER;
49 v_bucket_5 NUMBER;
50 v_bucket_6 NUMBER;
51 v_bucket_category ar_aging_bucket_lines.type%TYPE;
52 --
53 v_bucket_line_type_0 ar_aging_bucket_lines.type%TYPE;
54 v_bucket_days_from_0 NUMBER;
55 v_bucket_days_to_0 NUMBER;
56 v_bucket_line_type_1 ar_aging_bucket_lines.type%TYPE;
57 v_bucket_days_from_1 NUMBER;
58 v_bucket_days_to_1 NUMBER;
59 v_bucket_line_type_2 ar_aging_bucket_lines.type%TYPE;
60 v_bucket_days_from_2 NUMBER;
61 v_bucket_days_to_2 NUMBER;
62 v_bucket_line_type_3 ar_aging_bucket_lines.type%TYPE;
63 v_bucket_days_from_3 NUMBER;
64 v_bucket_days_to_3 NUMBER;
65 v_bucket_line_type_4 ar_aging_bucket_lines.type%TYPE;
66 v_bucket_days_from_4 NUMBER;
67 v_bucket_days_to_4 NUMBER;
68 v_bucket_line_type_5 ar_aging_bucket_lines.type%TYPE;
69 v_bucket_days_from_5 NUMBER;
70 v_bucket_days_to_5 NUMBER;
71 v_bucket_line_type_6 ar_aging_bucket_lines.type%TYPE;
72 v_bucket_days_from_6 NUMBER;
73 v_bucket_days_to_6 NUMBER;
74 v_outstanding_balance_1 NUMBER :=0;
75 v_outstanding_balance_2 NUMBER :=0;
76 v_outstanding_balance_3 NUMBER :=0;
77 v_outstanding_balance_4 NUMBER :=0;
78 v_bucket_amount_1_0 NUMBER :=0;
79 v_bucket_amount_1_1 NUMBER :=0;
80 v_bucket_amount_1_2 NUMBER :=0;
81 v_bucket_amount_1_3 NUMBER :=0;
82 v_bucket_amount_1_4 NUMBER :=0;
83 v_bucket_amount_1_5 NUMBER :=0;
84 v_bucket_amount_1_6 NUMBER :=0;
85 v_bucket_amount_2_0 NUMBER :=0;
86 v_bucket_amount_2_1 NUMBER :=0;
87 v_bucket_amount_2_2 NUMBER :=0;
88 v_bucket_amount_2_3 NUMBER :=0;
89 v_bucket_amount_2_4 NUMBER :=0;
90 v_bucket_amount_2_5 NUMBER :=0;
91 v_bucket_amount_2_6 NUMBER :=0;
92 v_bucket_amount_3_0 NUMBER :=0;
93 v_bucket_amount_3_1 NUMBER :=0;
94 v_bucket_amount_3_2 NUMBER :=0;
95 v_bucket_amount_3_3 NUMBER :=0;
96 v_bucket_amount_3_4 NUMBER :=0;
97 v_bucket_amount_3_5 NUMBER :=0;
98 v_bucket_amount_3_6 NUMBER :=0;
99 v_bucket_amount_4_0 NUMBER :=0;
100 v_bucket_amount_4_1 NUMBER :=0;
101 v_bucket_amount_4_2 NUMBER :=0;
102 v_bucket_amount_4_3 NUMBER :=0;
103 v_bucket_amount_4_4 NUMBER :=0;
104 v_bucket_amount_4_5 NUMBER :=0;
105 v_bucket_amount_4_6 NUMBER :=0;
106 --
107 l_as_of_date DATE := trunc(sysdate);
108
112 l_overall_limit NUMBER;
109 -- Variables for ar_cmgt_util.get_limit_currency procedure
110 l_limit_currency VARCHAR2(30);
111 l_trx_limit NUMBER;
113 l_cust_acct_profile_amt_id NUMBER;
114 l_global_exposure_flag hz_credit_usage_rule_sets_b.global_exposure_flag%type;
115 l_include_all_flag VARCHAR2(1);
116 l_curr_tbl HZ_CREDIT_USAGES_PKG.curr_tbl_type;
117 l_excl_curr_list VARCHAR2(2000);
118
119 CURSOR c_sel_bucket_data is
120 select lines.days_start,
121 lines.days_to,
122 lines.report_heading1,
123 lines.report_heading2,
124 lines.type
125 from ar_aging_bucket_lines lines,
126 ar_aging_buckets buckets
127 where lines.aging_bucket_id = buckets.aging_bucket_id
128 and upper(buckets.bucket_name) = upper(p_bucket_name)
129 and nvl(buckets.status,'A') = 'A'
130 order by lines.bucket_sequence_num
131 ;
132 --
133 /* bug4887799 : Cursor c_buckets is divided into multiple cursors
134 to improve performance */
135 CURSOR c_buckets1 IS
136 select sum(adr), sum(bucket0), sum(bucket1), sum(bucket2),
137 sum(bucket3), sum(bucket4), sum(bucket5),sum(bucket6)
138 from(
139 SELECT sum( gl_currency_api.convert_amount(
140 ps.invoice_currency_code,
141 p_currency_code,
142 sysdate,
143 p_exchange_rate_type,
144 ps.amount_due_remaining)) adr,
145 sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_0,
146 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_0,
147 v_bucket_days_to_0,ps.due_date,v_bucket_category,l_as_of_date)
148 * gl_currency_api.convert_amount(
149 ps.invoice_currency_code,
150 p_currency_code,
151 sysdate,
152 p_exchange_rate_type,
153 ps.amount_due_remaining)) bucket0 ,
154 sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_1,
155 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_1,
156 v_bucket_days_to_1,ps.due_date,v_bucket_category,l_as_of_date)
157 * gl_currency_api.convert_amount(
158 ps.invoice_currency_code,
159 p_currency_code,
160 sysdate,
161 p_exchange_rate_type,
162 ps.amount_due_remaining)) bucket1 ,
163 sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_2,
164 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_2,
165 v_bucket_days_to_2,ps.due_date,v_bucket_category,l_as_of_date)
166 * gl_currency_api.convert_amount(
167 ps.invoice_currency_code,
168 p_currency_code,
169 sysdate,
170 p_exchange_rate_type,
171 ps.amount_due_remaining)) bucket2 ,
172 sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_3,
173 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_3,
174 v_bucket_days_to_3,ps.due_date,v_bucket_category,l_as_of_date)
175 * gl_currency_api.convert_amount(
176 ps.invoice_currency_code,
177 p_currency_code,
178 sysdate,
179 p_exchange_rate_type,
180 ps.amount_due_remaining)) bucket3 ,
181 sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_4,
182 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_4,
183 v_bucket_days_to_4,ps.due_date,v_bucket_category,l_as_of_date)
184 * gl_currency_api.convert_amount(
185 ps.invoice_currency_code,
186 p_currency_code,
187 sysdate,
188 p_exchange_rate_type,
189 ps.amount_due_remaining)) bucket4 ,
190 sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_5,
191 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_5,
192 v_bucket_days_to_5,ps.due_date,v_bucket_category,l_as_of_date)
193 * gl_currency_api.convert_amount(
194 ps.invoice_currency_code,
195 p_currency_code,
196 sysdate,
197 p_exchange_rate_type,
198 ps.amount_due_remaining)) bucket5 ,
199 sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_6,
200 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_6,
201 v_bucket_days_to_6,ps.due_date,v_bucket_category,l_as_of_date)
202 * gl_currency_api.convert_amount(
203 ps.invoice_currency_code,
204 p_currency_code,
205 sysdate,
206 p_exchange_rate_type,
207 ps.amount_due_remaining)) bucket6
208 FROM ar_payment_schedules_all ps
209 WHERE payment_schedule_id +0 > 0
210 AND ps.class NOT IN ('GUAR', 'PMT')
211 --kjoshi bug#5169416
212 AND nvl(sign(ps.cons_inv_id),0) = decode(p_source,'CONS_BILL',1,0) --apandit BFB changes
213 AND trx_date <= l_as_of_date
214 AND actual_date_closed > l_as_of_date
218 ( SELECT child_id
215 and ps.customer_id in (select cust_account_id
216 FROM hz_cust_accounts
217 WHERE party_id in
219 from hz_hierarchy_nodes
220 where parent_object_type = 'ORGANIZATION'
221 and parent_table_name = 'HZ_PARTIES'
222 and child_object_type = 'ORGANIZATION'
223 and parent_id = p_party_id
224 and effective_start_date <= l_as_of_date
225 and effective_end_date >= l_as_of_date
226 and hierarchy_type =
227 FND_PROFILE.VALUE('AR_CMGT_HIERARCHY_TYPE')
228 and pg_source_name <> 'LNS'
229 union
230 select p_party_id from dual
231 UNION
232 select hz_party_id
233 from LNS_LOAN_PARTICIPANTS_V
234 where loan_id = pg_source_id
235 and participant_type_code = 'COBORROWER'
236 and pg_source_name = 'LNS'
237 and (end_date_active is null OR
238 (sysdate between start_date_active and end_date_active)
239 )
240 )
241 union
242 select p_customer_id from dual
243 )
244 and decode(p_site_use_id,
245 NULL, ps.customer_site_use_id,
246 p_site_use_id) = ps.customer_site_use_id
247 and ((ps.invoice_currency_code = p_currency_code
248 and p_source = 'CONS_BILL')
249 or (nvl(p_source,'x') <> 'CONS_BILL' and
250 ps.invoice_currency_code in
251 (select currency
252 from ar_cmgt_curr_usage_gt)))
253 ) ;
254 CURSOR c_buckets2 IS
255 select sum(adr), sum(bucket0), sum(bucket1), sum(bucket2),
256 sum(bucket3), sum(bucket4), sum(bucket5),sum(bucket6)
257 from(
258 -----All the receipt and CM applications after the as of date ---------
259 SELECT
260 sum( gl_currency_api.convert_amount(
261 ps.invoice_currency_code,
262 p_currency_code,
263 sysdate,
264 p_exchange_rate_type,
265 (ra.amount_applied +
266 NVL(ra.earned_discount_taken,0)
267 + NVL(ra.unearned_discount_taken,0) ))) adr,
268 sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_0,
269 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_0,
270 v_bucket_days_to_0,ps.due_date,v_bucket_category,l_as_of_date)
271 * gl_currency_api.convert_amount(
272 ps.invoice_currency_code,
273 p_currency_code,
274 sysdate,
275 p_exchange_rate_type,
276 (ra.amount_applied +
277 NVL(ra.earned_discount_taken,0)
278 + NVL(ra.unearned_discount_taken,0) ))) bucket0 ,
279 sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_1,
280 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_1,
281 v_bucket_days_to_1,ps.due_date,v_bucket_category,l_as_of_date)
282 * gl_currency_api.convert_amount(
283 ps.invoice_currency_code,
284 p_currency_code,
285 sysdate,
286 p_exchange_rate_type,
287 (ra.amount_applied +
288 NVL(ra.earned_discount_taken,0)
289 + NVL(ra.unearned_discount_taken,0) ))) bucket1 ,
290 sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_2,
291 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_2,
292 v_bucket_days_to_2,ps.due_date,v_bucket_category,l_as_of_date)
293 * gl_currency_api.convert_amount(
294 ps.invoice_currency_code,
295 p_currency_code,
296 sysdate,
297 p_exchange_rate_type,
298 (ra.amount_applied +
299 NVL(ra.earned_discount_taken,0)
300 + NVL(ra.unearned_discount_taken,0) ))) bucket2 ,
301 sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_3,
302 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_3,
303 v_bucket_days_to_3,ps.due_date,v_bucket_category,l_as_of_date)
304 * gl_currency_api.convert_amount(
305 ps.invoice_currency_code,
306 p_currency_code,
307 sysdate,
308 p_exchange_rate_type,
309 (ra.amount_applied +
310 NVL(ra.earned_discount_taken,0)
311 + NVL(ra.unearned_discount_taken,0) ))) bucket3 ,
312 sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_4,
313 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_4,
314 v_bucket_days_to_4,ps.due_date,v_bucket_category,l_as_of_date)
315 * gl_currency_api.convert_amount(
316 ps.invoice_currency_code,
317 p_currency_code,
318 sysdate,
322 + NVL(ra.unearned_discount_taken,0) ))) bucket4 ,
319 p_exchange_rate_type,
320 (ra.amount_applied +
321 NVL(ra.earned_discount_taken,0)
323 sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_5,
324 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_5,
325 v_bucket_days_to_5,ps.due_date,v_bucket_category,l_as_of_date)
326 * gl_currency_api.convert_amount(
327 ps.invoice_currency_code,
328 p_currency_code,
329 sysdate,
330 p_exchange_rate_type,
331 (ra.amount_applied +
332 NVL(ra.earned_discount_taken,0)
333 + NVL(ra.unearned_discount_taken,0) ))) bucket5 ,
334 sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_6,
335 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_6,
336 v_bucket_days_to_6,ps.due_date,v_bucket_category,l_as_of_date)
337 * gl_currency_api.convert_amount(
338 ps.invoice_currency_code,
339 p_currency_code,
340 sysdate,
341 p_exchange_rate_type,
342 (ra.amount_applied +
343 NVL(ra.earned_discount_taken,0)
344 + NVL(ra.unearned_discount_taken,0) ))) bucket6
345 FROM
346 ar_payment_schedules_all ps,
347 ar_receivable_applications_all ra
348 WHERE ra.applied_payment_schedule_id = ps.payment_schedule_id
349 --kjoshi bug#5169416
350 AND nvl(sign(ps.cons_inv_id),0) = decode(p_source,'CONS_BILL',1,0)
351 AND ps.payment_schedule_id +0 > 0
352 AND ra.apply_date > l_as_of_date
353 AND ra.status = 'APP'
354 AND ps.class NOT IN ('GUAR', 'PMT')
355 AND ps.trx_date <= l_as_of_date
356 AND ps.actual_date_closed > l_as_of_date
357 AND NVL(ra.confirmed_flag,'Y') = 'Y'
358 and ps.customer_id in (select cust_account_id
359 FROM hz_cust_accounts
360 WHERE party_id in
361 ( SELECT child_id
362 from hz_hierarchy_nodes
363 where parent_object_type = 'ORGANIZATION'
364 and parent_table_name = 'HZ_PARTIES'
365 and child_object_type = 'ORGANIZATION'
366 and parent_id = p_party_id
367 and effective_start_date <= l_as_of_date
368 and effective_end_date >= l_as_of_date
369 and hierarchy_type =
370 FND_PROFILE.VALUE('AR_CMGT_HIERARCHY_TYPE')
371 and pg_source_name <> 'LNS'
372 union
373 select p_party_id from dual
374 UNION
375 select hz_party_id
376 from LNS_LOAN_PARTICIPANTS_V
377 where loan_id = pg_source_id
378 and participant_type_code = 'COBORROWER'
379 and pg_source_name = 'LNS'
380 and (end_date_active is null OR
381 (sysdate between start_date_active and end_date_active)
382 )
383 )
384 union
385 select p_customer_id from dual
386 )
387 and decode(p_site_use_id,
388 NULL, ps.customer_site_use_id,
389 p_site_use_id) = ps.customer_site_use_id
390 and ((ps.invoice_currency_code = p_currency_code
391 and p_source = 'CONS_BILL')
392 or (nvl(p_source,'x') <> 'CONS_BILL' and
393 ps.invoice_currency_code in
394 (select currency
395 from ar_cmgt_curr_usage_gt)))
396 ) ;
397 CURSOR c_buckets3 IS
398 select sum(adr), sum(bucket0), sum(bucket1), sum(bucket2),
399 sum(bucket3), sum(bucket4), sum(bucket5),sum(bucket6)
400 from(
401 ------------All the adjustments after the as of date---------------
402 SELECT
403 -sum(gl_currency_api.convert_amount(
404 ps.invoice_currency_code,
405 p_currency_code,
406 sysdate,
407 p_exchange_rate_type,
408 adj.amount)) adr,
409 -sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_0,
410 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_0,
411 v_bucket_days_to_0,ps.due_date,v_bucket_category,l_as_of_date)
412 * gl_currency_api.convert_amount(
413 ps.invoice_currency_code,
414 p_currency_code,
415 sysdate,
416 p_exchange_rate_type,
417 adj.amount)) bucket0 ,
418 -sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_1,
419 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_1,
420 v_bucket_days_to_1,ps.due_date,v_bucket_category,l_as_of_date)
421 * gl_currency_api.convert_amount(
422 ps.invoice_currency_code,
423 p_currency_code,
424 sysdate,
425 p_exchange_rate_type,
426 adj.amount)) bucket1 ,
427 -sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_2,
431 ps.invoice_currency_code,
428 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_2,
429 v_bucket_days_to_2,ps.due_date,v_bucket_category,l_as_of_date)
430 * gl_currency_api.convert_amount(
432 p_currency_code,
433 sysdate,
434 p_exchange_rate_type,
435 adj.amount)) bucket2 ,
436 -sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_3,
437 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_3,
438 v_bucket_days_to_3,ps.due_date,v_bucket_category,l_as_of_date)
439 * gl_currency_api.convert_amount(
440 ps.invoice_currency_code,
441 p_currency_code,
442 sysdate,
443 p_exchange_rate_type,
444 adj.amount)) bucket3 ,
445 -sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_4,
446 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_4,
447 v_bucket_days_to_4,ps.due_date,v_bucket_category,l_as_of_date)
448 * gl_currency_api.convert_amount(
449 ps.invoice_currency_code,
450 p_currency_code,
451 sysdate,
452 p_exchange_rate_type,
453 adj.amount)) bucket4 ,
454 -sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_5,
455 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_5,
456 v_bucket_days_to_5,ps.due_date,v_bucket_category,l_as_of_date)
457 * gl_currency_api.convert_amount(
458 ps.invoice_currency_code,
459 p_currency_code,
460 sysdate,
461 p_exchange_rate_type,
462 adj.amount)) bucket5 ,
463 -sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_6,
464 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_6,
465 v_bucket_days_to_6,ps.due_date,v_bucket_category,l_as_of_date)
466 * gl_currency_api.convert_amount(
467 ps.invoice_currency_code,
468 p_currency_code,
469 sysdate,
470 p_exchange_rate_type,
471 adj.acctd_amount)) bucket6
472 FROM ar_adjustments_all adj,
473 ar_payment_schedules_all ps
474 WHERE adj.payment_schedule_id = ps.payment_schedule_id
475 --kjoshi bug#5169416
476 AND nvl(sign(ps.cons_inv_id),0) = decode(p_source,'CONS_BILL',1,0) --apandit BFB changes
477 AND adj.apply_date > l_as_of_date
478 AND ps.class NOT IN ('GUAR', 'PMT')
479 AND ps.trx_date <= l_as_of_date
480 AND ps.actual_date_closed > l_as_of_date
481 AND adj.status = 'A'
482 and ps.customer_id in (select cust_account_id
483 FROM hz_cust_accounts
484 WHERE party_id in
485 ( SELECT child_id
486 from hz_hierarchy_nodes
487 where parent_object_type = 'ORGANIZATION'
488 and parent_table_name = 'HZ_PARTIES'
489 and child_object_type = 'ORGANIZATION'
490 and parent_id = p_party_id
491 and effective_start_date <= l_as_of_date
492 and effective_end_date >= l_as_of_date
493 and hierarchy_type =
494 FND_PROFILE.VALUE('AR_CMGT_HIERARCHY_TYPE')
495 and pg_source_name <> 'LNS'
496 union
497 select p_party_id from dual
498 UNION
499 select hz_party_id
500 from LNS_LOAN_PARTICIPANTS_V
501 where loan_id = pg_source_id
502 and participant_type_code = 'COBORROWER'
503 and pg_source_name = 'LNS'
504 and (end_date_active is null OR
505 (sysdate between start_date_active and end_date_active)
506 )
507 )
508 union
509 select p_customer_id from dual
510 )
511 and decode(p_site_use_id,
512 NULL, ps.customer_site_use_id,
513 p_site_use_id) = ps.customer_site_use_id
514 and ((ps.invoice_currency_code = p_currency_code
515 and p_source = 'CONS_BILL')
516 or (nvl(p_source,'x') <> 'CONS_BILL' and
517 ps.invoice_currency_code in
518 (select currency
519 from ar_cmgt_curr_usage_gt)))
520 ) ;
521 CURSOR c_buckets4 IS
522 select sum(adr), sum(bucket0), sum(bucket1), sum(bucket2),
523 sum(bucket3), sum(bucket4), sum(bucket5),sum(bucket6)
524 from(
525 ---------all the CM applications after the as of date -----------
526 SELECT sum(gl_currency_api.convert_amount(
527 ps.invoice_currency_code,
528 p_currency_code,
529 sysdate,
530 p_exchange_rate_type,
531 (ra.amount_applied_from +
532 NVL(ra.earned_discount_taken,0)
533 + NVL(ra.unearned_discount_taken,0) ))) adr,
537 * gl_currency_api.convert_amount(
534 -sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_0,
535 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_0,
536 v_bucket_days_to_0,ps.due_date,v_bucket_category,l_as_of_date)
538 ps.invoice_currency_code,
539 p_currency_code,
540 sysdate,
541 p_exchange_rate_type,
542 (ra.amount_applied_from +
543 NVL(ra.earned_discount_taken,0)
544 + NVL(ra.unearned_discount_taken,0) ))) bucket0 ,
545 -sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_1,
546 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_1,
547 v_bucket_days_to_1,ps.due_date,v_bucket_category,l_as_of_date)
548 * gl_currency_api.convert_amount(
549 ps.invoice_currency_code,
550 p_currency_code,
551 sysdate,
552 p_exchange_rate_type,
553 (ra.amount_applied_from +
554 NVL(ra.earned_discount_taken,0)
555 + NVL(ra.unearned_discount_taken,0) ))) bucket1 ,
556 -sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_2,
557 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_2,
558 v_bucket_days_to_2,ps.due_date,v_bucket_category,l_as_of_date)
559 * gl_currency_api.convert_amount(
560 ps.invoice_currency_code,
561 p_currency_code,
562 sysdate,
563 p_exchange_rate_type,
564 (ra.amount_applied_from +
565 NVL(ra.earned_discount_taken,0)
566 + NVL(ra.unearned_discount_taken,0) ))) bucket2 ,
567 -sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_3,
568 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_3,
569 v_bucket_days_to_3,ps.due_date,v_bucket_category,l_as_of_date)
570 * gl_currency_api.convert_amount(
571 ps.invoice_currency_code,
572 p_currency_code,
573 sysdate,
574 p_exchange_rate_type,
575 (ra.amount_applied_from +
576 NVL(ra.earned_discount_taken,0)
577 + NVL(ra.unearned_discount_taken,0) ))) bucket3 ,
578 -sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_4,
579 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_4,
580 v_bucket_days_to_4,ps.due_date,v_bucket_category,l_as_of_date)
581 * gl_currency_api.convert_amount(
582 ps.invoice_currency_code,
583 p_currency_code,
584 sysdate,
585 p_exchange_rate_type,
586 (ra.amount_applied_from +
587 NVL(ra.earned_discount_taken,0)
588 + NVL(ra.unearned_discount_taken,0) ))) bucket4 ,
589 -sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_5,
590 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_5,
591 v_bucket_days_to_5,ps.due_date,v_bucket_category,l_as_of_date)
592 * gl_currency_api.convert_amount(
593 ps.invoice_currency_code,
594 p_currency_code,
595 sysdate,
596 p_exchange_rate_type,
597 (ra.amount_applied_from +
598 NVL(ra.earned_discount_taken,0)
599 + NVL(ra.unearned_discount_taken,0) ))) bucket5 ,
600 -sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_6,
601 ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_6,
602 v_bucket_days_to_6,ps.due_date,v_bucket_category,l_as_of_date)
603 * gl_currency_api.convert_amount(
604 ps.invoice_currency_code,
605 p_currency_code,
606 sysdate,
607 p_exchange_rate_type,
608 (ra.amount_applied_from +
609 NVL(ra.earned_discount_taken,0)
610 + NVL(ra.unearned_discount_taken,0) ))) bucket6
611 FROM ar_payment_schedules_all ps,
612 ar_receivable_applications_all ra
613 WHERE
614 ps.payment_schedule_id +0 > 0
615 AND ra.payment_schedule_id = ps.payment_schedule_id
616 --kjoshi bug#5169416
617 AND nvl(sign(ps.cons_inv_id),0) = decode(p_source,'CONS_BILL',1,0) --apandit BFB changes
618 AND ra.apply_date > l_as_of_date
619 AND ps.class NOT IN ('GUAR', 'PMT')
620 AND ra.status = 'APP'
621 and ra.application_type = 'CM'
622 AND ps.trx_date <= l_as_of_date
623 AND ps.actual_date_closed > l_as_of_date
624 AND NVL(ra.confirmed_flag,'Y') = 'Y'
625 and ps.customer_id in (select cust_account_id
626 FROM hz_cust_accounts
627 WHERE party_id in
628 ( SELECT child_id
629 from hz_hierarchy_nodes
630 where parent_object_type = 'ORGANIZATION'
631 and parent_table_name = 'HZ_PARTIES'
632 and child_object_type = 'ORGANIZATION'
633 and parent_id = p_party_id
637 FND_PROFILE.VALUE('AR_CMGT_HIERARCHY_TYPE')
634 and effective_start_date <= l_as_of_date
635 and effective_end_date >= l_as_of_date
636 and hierarchy_type =
638 and pg_source_name <> 'LNS'
639 union
640 select p_party_id from dual
641 UNION
642 select hz_party_id
643 from LNS_LOAN_PARTICIPANTS_V
644 where loan_id = pg_source_id
645 and participant_type_code = 'COBORROWER'
646 and pg_source_name = 'LNS'
647 and (end_date_active is null OR
648 (sysdate between start_date_active and end_date_active)
649 )
650 )
651 union
652 select p_customer_id from dual
653 )
654 and decode(p_site_use_id,
655 NULL, ps.customer_site_use_id,
656 p_site_use_id) = ps.customer_site_use_id
657 and ((ps.invoice_currency_code = p_currency_code
658 and p_source = 'CONS_BILL')
659 or (nvl(p_source,'x') <> 'CONS_BILL' and
660 ps.invoice_currency_code in
661 (select currency
662 from ar_cmgt_curr_usage_gt)))
663 ) ;
664 BEGIN
665
666 -- Put in the currencies in the global temporary table
667 -- commenting out the following code because, if aging is called
668 -- from top ten exposure report then user need to see the aging
669 -- for all currencies, whether the customer has case folder or not.
670 IF (p_source = 'PERF_REPORT') THEN
671 /* AR_CMGT_UTIL.get_limit_currency(
672 p_party_id => p_party_id,
673 p_cust_account_id => p_customer_id,
674 p_cust_acct_site_id => p_site_use_id,
675 p_trx_currency_code => p_currency_code,
676 p_limit_curr_code => l_limit_currency,
677 p_trx_limit => l_trx_limit,
678 p_overall_limit => l_overall_limit,
679 p_cust_acct_profile_amt_id => l_cust_acct_profile_amt_id,
680 p_global_exposure_flag => l_global_exposure_flag,
681 p_include_all_flag => l_include_all_flag,
682 p_usage_curr_tbl => l_curr_tbl,
683 p_excl_curr_list => l_excl_curr_list);
684
685 IF ( (nvl(l_include_all_flag,'N') = 'N') and l_limit_currency IS NOT NULL )
686 THEN
687 for i in 1..l_curr_tbl.COUNT
688 LOOP
689 INSERT INTO ar_cmgt_curr_usage_gt ( credit_request_id, currency) values
690 ( NULL, l_curr_tbl(i).usage_curr_code);
691 END LOOP;
692 ELSE
693 -- populate temp table with all currency. may not be a good soulution
694 -- to take this approach. Would be better to have another cursor.
695 INSERT INTO ar_cmgt_curr_usage_gt(currency)
696 ( select distinct currency from ar_trx_bal_summary);
697 END IF; */
698 INSERT INTO ar_cmgt_curr_usage_gt(currency)
699 ( select distinct currency from ar_trx_bal_summary);
700 END IF;
701
702 --
703 -- Get the aging buckets definition.
704 --
705 OPEN c_sel_bucket_data;
706 FETCH c_sel_bucket_data INTO v_bucket_days_from_0, v_bucket_days_to_0,
707 p_bucket_titletop_0, p_bucket_titlebottom_0,
708 v_bucket_line_type_0;
709 IF c_sel_bucket_data%FOUND THEN
710 p_bucket_amount_0 := 0;
711 IF (v_bucket_line_type_0 = 'DISPUTE_ONLY') OR
712 (v_bucket_line_type_0 = 'PENDADJ_ONLY') OR
713 (v_bucket_line_type_0 = 'DISPUTE_PENDADJ') THEN
714 v_bucket_category := v_bucket_line_type_0;
715 END IF;
716 FETCH c_sel_bucket_data INTO v_bucket_days_from_1, v_bucket_days_to_1,
717 p_bucket_titletop_1, p_bucket_titlebottom_1,
718 v_bucket_line_type_1;
719 ELSE
720 p_bucket_titletop_0 := NULL;
721 p_bucket_titlebottom_0 := NULL;
722 p_bucket_amount_0 := NULL;
723 END IF;
724 IF c_sel_bucket_data%FOUND THEN
725 p_bucket_amount_1 := 0;
726 IF (v_bucket_line_type_1 = 'DISPUTE_ONLY') OR
727 (v_bucket_line_type_1 = 'PENDADJ_ONLY') OR
728 (v_bucket_line_type_1 = 'DISPUTE_PENDADJ') THEN
729 v_bucket_category := v_bucket_line_type_1;
730 END IF;
731 FETCH c_sel_bucket_data INTO v_bucket_days_from_2, v_bucket_days_to_2,
732 p_bucket_titletop_2, p_bucket_titlebottom_2,
733 v_bucket_line_type_2;
734 ELSE
735 p_bucket_titletop_1 := NULL;
736 p_bucket_titlebottom_1 := NULL;
737 p_bucket_amount_1 := NULL;
738 END IF;
739 IF c_sel_bucket_data%FOUND THEN
740 p_bucket_amount_2 := 0;
741 IF (v_bucket_line_type_2 = 'DISPUTE_ONLY') OR
742 (v_bucket_line_type_2 = 'PENDADJ_ONLY') OR
743 (v_bucket_line_type_2 = 'DISPUTE_PENDADJ') THEN
744 v_bucket_category := v_bucket_line_type_2;
745 END IF;
746 FETCH c_sel_bucket_data INTO v_bucket_days_from_3, v_bucket_days_to_3,
747 p_bucket_titletop_3, p_bucket_titlebottom_3,
748 v_bucket_line_type_3;
749 ELSE
750 p_bucket_titletop_2 := NULL;
751 p_bucket_titlebottom_2 := NULL;
752 p_bucket_amount_2 := NULL;
753 END IF;
757 (v_bucket_line_type_3 = 'PENDADJ_ONLY') OR
754 IF c_sel_bucket_data%FOUND THEN
755 p_bucket_amount_3 := 0;
756 IF (v_bucket_line_type_3 = 'DISPUTE_ONLY') OR
758 (v_bucket_line_type_3 = 'DISPUTE_PENDADJ') THEN
759 v_bucket_category := v_bucket_line_type_3;
760 END IF;
761 FETCH c_sel_bucket_data INTO v_bucket_days_from_4, v_bucket_days_to_4,
762 p_bucket_titletop_4, p_bucket_titlebottom_4,
763 v_bucket_line_type_4;
764 ELSE
765 p_bucket_titletop_3 := NULL;
766 p_bucket_titlebottom_3 := NULL;
767 p_bucket_amount_3 := NULL;
768 END IF;
769 IF c_sel_bucket_data%FOUND THEN
770 p_bucket_amount_4 := 0;
771 IF (v_bucket_line_type_4 = 'DISPUTE_ONLY') OR
772 (v_bucket_line_type_4 = 'PENDADJ_ONLY') OR
773 (v_bucket_line_type_4 = 'DISPUTE_PENDADJ') THEN
774 v_bucket_category := v_bucket_line_type_4;
775 END IF;
776 FETCH c_sel_bucket_data INTO v_bucket_days_from_5, v_bucket_days_to_5,
777 p_bucket_titletop_5, p_bucket_titlebottom_5,
778 v_bucket_line_type_5;
779 ELSE
780 p_bucket_titletop_4 := NULL;
781 p_bucket_titlebottom_4 := NULL;
782 p_bucket_amount_4 := NULL;
783 END IF;
784 IF c_sel_bucket_data%FOUND THEN
785 p_bucket_amount_5 := 0;
786 IF (v_bucket_line_type_5 = 'DISPUTE_ONLY') OR
787 (v_bucket_line_type_5 = 'PENDADJ_ONLY') OR
788 (v_bucket_line_type_5 = 'DISPUTE_PENDADJ') THEN
789 v_bucket_category := v_bucket_line_type_5;
790 END IF;
791 FETCH c_sel_bucket_data INTO v_bucket_days_from_6, v_bucket_days_to_6,
792 p_bucket_titletop_6, p_bucket_titlebottom_6,
793 v_bucket_line_type_6;
794 ELSE
795 p_bucket_titletop_5 := NULL;
796 p_bucket_titlebottom_5 := NULL;
797 p_bucket_amount_5 := NULL;
798 END IF;
799 IF c_sel_bucket_data%FOUND THEN
800 p_bucket_amount_6 := 0;
801 IF (v_bucket_line_type_6 = 'DISPUTE_ONLY') OR
802 (v_bucket_line_type_6 = 'PENDADJ_ONLY') OR
803 (v_bucket_line_type_6 = 'DISPUTE_PENDADJ') THEN
804 v_bucket_category := v_bucket_line_type_6;
805 END IF;
806 ELSE
807 p_bucket_titletop_6 := NULL;
808 p_bucket_titlebottom_6 := NULL;
809 p_bucket_amount_6 := NULL;
810 END IF;
811 CLOSE c_sel_bucket_data;
812 --
813 -- get the aging bucket balance. The v_bucket_ is either 1 or 0.
814 --
815 p_outstanding_balance := 0;
816 OPEN c_buckets1;
817 FETCH c_buckets1 INTO v_outstanding_balance_1,
818 v_bucket_amount_1_0, v_bucket_amount_1_1, v_bucket_amount_1_2,
819 v_bucket_amount_1_3, v_bucket_amount_1_4, v_bucket_amount_1_5,
820 v_bucket_amount_1_6;
821 CLOSE c_buckets1;
822 OPEN c_buckets2;
823 FETCH c_buckets2 INTO v_outstanding_balance_2,
824 v_bucket_amount_2_0, v_bucket_amount_2_1, v_bucket_amount_2_2,
825 v_bucket_amount_2_3, v_bucket_amount_2_4, v_bucket_amount_2_5,
826 v_bucket_amount_2_6;
827 CLOSE c_buckets2;
828 OPEN c_buckets3;
829 FETCH c_buckets3 INTO v_outstanding_balance_3,
830 v_bucket_amount_3_0, v_bucket_amount_3_1, v_bucket_amount_3_2,
831 v_bucket_amount_3_3, v_bucket_amount_3_4, v_bucket_amount_3_5,
832 v_bucket_amount_3_6;
833 CLOSE c_buckets3;
834 OPEN c_buckets4;
835 FETCH c_buckets4 INTO v_outstanding_balance_4,
836 v_bucket_amount_4_0, v_bucket_amount_4_1, v_bucket_amount_4_2,
837 v_bucket_amount_4_3, v_bucket_amount_4_4, v_bucket_amount_4_5,
838 v_bucket_amount_4_6;
839 CLOSE c_buckets4;
840 p_outstanding_balance := nvl(v_outstanding_balance_1,0)
841 + nvl(v_outstanding_balance_2,0)
842 + nvl(v_outstanding_balance_3,0)
843 + nvl(v_outstanding_balance_4,0);
844 p_bucket_amount_0 := nvl(v_bucket_amount_1_0,0)
845 + nvl(v_bucket_amount_2_0,0)
846 + nvl(v_bucket_amount_3_0,0)
847 + nvl(v_bucket_amount_4_0,0);
848 p_bucket_amount_1 := nvl(v_bucket_amount_1_1,0)
849 + nvl(v_bucket_amount_2_1,0)
850 + nvl(v_bucket_amount_3_1,0)
851 + nvl(v_bucket_amount_4_1,0);
852 p_bucket_amount_2 := nvl(v_bucket_amount_1_2,0)
853 + nvl(v_bucket_amount_2_2,0)
854 + nvl(v_bucket_amount_3_2,0)
855 + nvl(v_bucket_amount_4_2,0);
856 p_bucket_amount_3 := nvl(v_bucket_amount_1_3,0)
857 + nvl(v_bucket_amount_2_3,0)
858 + nvl(v_bucket_amount_3_3,0)
859 + nvl(v_bucket_amount_4_3,0);
860 p_bucket_amount_4 := nvl(v_bucket_amount_1_4,0)
861 + nvl(v_bucket_amount_2_4,0)
862 + nvl(v_bucket_amount_3_4,0)
863 + nvl(v_bucket_amount_4_4,0);
864 p_bucket_amount_5 := nvl(v_bucket_amount_1_5,0)
865 + nvl(v_bucket_amount_2_5,0)
866 + nvl(v_bucket_amount_3_5,0)
867 + nvl(v_bucket_amount_4_5,0);
868 p_bucket_amount_6 := nvl(v_bucket_amount_1_6,0)
869 + nvl(v_bucket_amount_2_6,0)
870 + nvl(v_bucket_amount_3_6,0)
871 + nvl(v_bucket_amount_4_6,0);
875 IF PG_DEBUG in ('Y', 'C') THEN
872 --
873 EXCEPTION
874 WHEN OTHERS THEN
876 arp_standard.debug('EXCEPTION: arp_customer_aging.calc_aging_buckets');
877 END IF;
878 END calc_aging_buckets;
879
880 END AR_CMGT_AGING;