[Home] [Help]
PACKAGE BODY: APPS.ARP_CUSTOMER_AGING
Source
1 PACKAGE BODY arp_customer_aging AS
2 /* $Header: ARCWAGEB.pls 120.8 2006/08/17 14:14:56 naneja ship $ */
3
4 --
5 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
6
7 PROCEDURE calc_aging_buckets (
8 p_customer_id IN NUMBER,
9 p_customer_site_use_id IN NUMBER,
10 p_as_of_date IN DATE,
11 p_currency_code IN VARCHAR2,
12 p_credit_option IN VARCHAR2,
13 p_invoice_type_low IN VARCHAR2,
14 p_invoice_type_high IN VARCHAR2,
15 p_ps_max_id IN NUMBER DEFAULT 0,
16 p_app_max_id IN NUMBER DEFAULT 0,
17 p_bucket_name IN VARCHAR2,
18 p_outstanding_balance IN OUT NOCOPY NUMBER,
19 p_bucket_titletop_0 OUT NOCOPY VARCHAR2,
20 p_bucket_titlebottom_0 OUT NOCOPY VARCHAR2,
21 p_bucket_amount_0 IN OUT NOCOPY NUMBER,
22 p_bucket_titletop_1 OUT NOCOPY VARCHAR2,
23 p_bucket_titlebottom_1 OUT NOCOPY VARCHAR2,
24 p_bucket_amount_1 IN OUT NOCOPY NUMBER,
25 p_bucket_titletop_2 OUT NOCOPY VARCHAR2,
26 p_bucket_titlebottom_2 OUT NOCOPY VARCHAR2,
27 p_bucket_amount_2 IN OUT NOCOPY NUMBER,
28 p_bucket_titletop_3 OUT NOCOPY VARCHAR2,
29 p_bucket_titlebottom_3 OUT NOCOPY VARCHAR2,
30 p_bucket_amount_3 IN OUT NOCOPY NUMBER,
31 p_bucket_titletop_4 OUT NOCOPY VARCHAR2,
32 p_bucket_titlebottom_4 OUT NOCOPY VARCHAR2,
33 p_bucket_amount_4 IN OUT NOCOPY NUMBER,
34 p_bucket_titletop_5 OUT NOCOPY VARCHAR2,
35 p_bucket_titlebottom_5 OUT NOCOPY VARCHAR2,
36 p_bucket_amount_5 IN OUT NOCOPY NUMBER,
37 p_bucket_titletop_6 OUT NOCOPY VARCHAR2,
38 p_bucket_titlebottom_6 OUT NOCOPY VARCHAR2,
39 p_bucket_amount_6 IN OUT NOCOPY NUMBER
40 ) IS
41 v_amount_due_remaining NUMBER;
42 v_bucket_0 NUMBER;
43 v_bucket_1 NUMBER;
44 v_bucket_2 NUMBER;
45 v_bucket_3 NUMBER;
46 v_bucket_4 NUMBER;
47 v_bucket_5 NUMBER;
48 v_bucket_6 NUMBER;
49 v_bucket_category ar_aging_bucket_lines.type%TYPE;
50 --
51 v_bucket_line_type_0 ar_aging_bucket_lines.type%TYPE;
52 v_bucket_days_from_0 NUMBER;
53 v_bucket_days_to_0 NUMBER;
54 v_bucket_line_type_1 ar_aging_bucket_lines.type%TYPE;
55 v_bucket_days_from_1 NUMBER;
56 v_bucket_days_to_1 NUMBER;
57 v_bucket_line_type_2 ar_aging_bucket_lines.type%TYPE;
58 v_bucket_days_from_2 NUMBER;
59 v_bucket_days_to_2 NUMBER;
60 v_bucket_line_type_3 ar_aging_bucket_lines.type%TYPE;
61 v_bucket_days_from_3 NUMBER;
62 v_bucket_days_to_3 NUMBER;
63 v_bucket_line_type_4 ar_aging_bucket_lines.type%TYPE;
64 v_bucket_days_from_4 NUMBER;
65 v_bucket_days_to_4 NUMBER;
66 v_bucket_line_type_5 ar_aging_bucket_lines.type%TYPE;
67 v_bucket_days_from_5 NUMBER;
68 v_bucket_days_to_5 NUMBER;
69 v_bucket_line_type_6 ar_aging_bucket_lines.type%TYPE;
70 v_bucket_days_from_6 NUMBER;
71 v_bucket_days_to_6 NUMBER;
72 --
73 CURSOR c_sel_bucket_data is
74 select lines.days_start,
75 lines.days_to,
76 lines.report_heading1,
77 lines.report_heading2,
78 lines.type
79 from ar_aging_bucket_lines lines,
80 ar_aging_buckets buckets
81 where lines.aging_bucket_id = buckets.aging_bucket_id
82 and upper(buckets.bucket_name) = upper(p_bucket_name)
83 and nvl(buckets.status,'A') = 'A'
84 order by lines.bucket_sequence_num
85 ;
86 --
87 /* bug4047166 : The cursosr c_buckets is now 5 different cursors and
88 one of them will get executed depending upon the site and trx type
89 parameter values. The cursor c_buckets_1 will be used as default one
90 This is done to improve performance */
91
92 CURSOR c_buckets_1 IS
93 select decode(p_currency_code, NULL, ps.acctd_amount_due_remaining,
94 ps.amount_due_remaining),
95 decode(v_bucket_line_type_0,
96 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
97 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
98 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
99 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
100 1),
101 decode( greatest(v_bucket_days_from_0,
102 ceil(p_as_of_date-ps.due_date)),
103 least(v_bucket_days_to_0,
104 ceil(p_as_of_date-ps.due_date)),1,
105 0)
106 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
107 decode(v_bucket_category,
108 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
109 1))
110 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
111 decode(v_bucket_category,
112 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
113 1))) b0,
114 decode(v_bucket_line_type_1,
115 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
116 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
117 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
118 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
119 1),
120 decode( greatest(v_bucket_days_from_1,
121 ceil(p_as_of_date-ps.due_date)),
122 least(v_bucket_days_to_1,
123 ceil(p_as_of_date-ps.due_date)),1,
124 0)
125 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
126 decode(v_bucket_category,
127 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
128 1))
129 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
130 decode(v_bucket_category,
131 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
132 1))) b1,
133 decode(v_bucket_line_type_2,
134 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
135 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
136 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
137 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
138 1),
139 decode( greatest(v_bucket_days_from_2,
140 ceil(p_as_of_date-ps.due_date)),
141 least(v_bucket_days_to_2,
142 ceil(p_as_of_date-ps.due_date)),1,
143 0)
144 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
145 decode(v_bucket_category,
146 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
147 1))
148 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
149 decode(v_bucket_category,
150 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
151 1))) b2,
152 decode(v_bucket_line_type_3,
153 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
154 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
155 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
156 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
157 1),
158 decode( greatest(v_bucket_days_from_3,
159 ceil(p_as_of_date-ps.due_date)),
160 least(v_bucket_days_to_3,
161 ceil(p_as_of_date-ps.due_date)),1,
162 0)
163 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
164 decode(v_bucket_category,
165 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
166 1))
167 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
168 decode(v_bucket_category,
169 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
170 1))) b3,
171 decode(v_bucket_line_type_4,
172 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
173 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
174 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
175 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
176 1),
177 decode( greatest(v_bucket_days_from_4,
178 ceil(p_as_of_date-ps.due_date)),
179 least(v_bucket_days_to_4,
180 ceil(p_as_of_date-ps.due_date)),1,
181 0)
182 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
183 decode(v_bucket_category,
184 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
185 1))
186 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
187 decode(v_bucket_category,
188 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
189 1))) b4,
190 decode(v_bucket_line_type_5,
191 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
192 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
193 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
194 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
195 1),
196 decode( greatest(v_bucket_days_from_5,
197 ceil(p_as_of_date-ps.due_date)),
198 least(v_bucket_days_to_5,
199 ceil(p_as_of_date-ps.due_date)),1,
200 0)
201 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
202 decode(v_bucket_category,
203 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
204 1))
205 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
206 decode(v_bucket_category,
207 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
208 1))) b5,
209 decode(v_bucket_line_type_6,
210 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
211 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
212 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
213 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
214 1),
215 decode( greatest(v_bucket_days_from_6,
216 ceil(p_as_of_date-ps.due_date)),
217 least(v_bucket_days_to_6,
218 ceil(p_as_of_date-ps.due_date)),1,
219 0)
220 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
221 decode(v_bucket_category,
222 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
223 1))
224 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
225 decode(v_bucket_category,
226 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
227 1))) b6
228 from ra_cust_trx_types,
229 ar_payment_schedules ps
230 where ps.gl_date <= p_as_of_date
231 and ps.cust_trx_type_id = ra_cust_trx_types.cust_trx_type_id
232 and ps.gl_date_closed > p_as_of_date
233 and ps.customer_id = p_customer_id
234 and decode(p_customer_site_use_id,
235 NULL, ps.customer_site_use_id,
236 p_customer_site_use_id) = ps.customer_site_use_id
237 and decode(upper(p_currency_code),
238 NULL, ps.invoice_currency_code,
239 upper(p_currency_code)) = ps.invoice_currency_code
240 and decode(upper(p_credit_option),
241 'AGE', 'dummy','SUMMARY','dummy',
242 'CM') <> ps.class
243 and decode(upper(p_credit_option),
244 'AGE', 'dummy','SUMMARY','dummy',
245 'PMT') <> ps.class
246 and decode(p_invoice_type_low,
247 NULL, ra_cust_trx_types.name,
248 p_invoice_type_low) <= ra_cust_trx_types.name
249 and decode(p_invoice_type_high,
250 NULL, ra_cust_trx_types.name,
251 p_invoice_type_high) >= ra_cust_trx_types.name
252 UNION ALL
253 select -sum(decode(p_currency_code, NULL, app.acctd_amount_applied_from,
254 app.amount_applied)),
255 decode(v_bucket_line_type_0,
256 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
257 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
258 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
259 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
260 1),
261 decode( greatest(v_bucket_days_from_0,
262 ceil(p_as_of_date-ps.due_date)),
263 least(v_bucket_days_to_0,
264 ceil(p_as_of_date-ps.due_date)),1,
265 0)
266 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
267 decode(v_bucket_category,
268 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
269 1))
270 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
271 decode(v_bucket_category,
272 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
273 1))) b0,
274 decode(v_bucket_line_type_1,
275 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
276 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
277 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
278 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
279 1),
280 decode( greatest(v_bucket_days_from_1,
281 ceil(p_as_of_date-ps.due_date)),
282 least(v_bucket_days_to_1,
283 ceil(p_as_of_date-ps.due_date)),1,
284 0)
285 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
286 decode(v_bucket_category,
287 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
288 1))
289 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
290 decode(v_bucket_category,
291 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
292 1)))b1,
293 decode(v_bucket_line_type_2,
294 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
295 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
296 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
297 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
298 1),
299 decode( greatest(v_bucket_days_from_2,
300 ceil(p_as_of_date-ps.due_date)),
301 least(v_bucket_days_to_2,
302 ceil(p_as_of_date-ps.due_date)),1,
303 0)
304 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
305 decode(v_bucket_category,
306 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
307 1))
308 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
309 decode(v_bucket_category,
310 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
311 1))) b2,
312 decode(v_bucket_line_type_3,
313 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
314 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
315 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
316 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
317 1),
318 decode( greatest(v_bucket_days_from_3,
319 ceil(p_as_of_date-ps.due_date)),
320 least(v_bucket_days_to_3,
321 ceil(p_as_of_date-ps.due_date)),1,
322 0)
323 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
324 decode(v_bucket_category,
325 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
326 1))
327 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
328 decode(v_bucket_category,
329 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
330 1))) b3,
331 decode(v_bucket_line_type_4,
332 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
333 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
334 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
335 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
336 1),
337 decode( greatest(v_bucket_days_from_4,
338 ceil(p_as_of_date-ps.due_date)),
339 least(v_bucket_days_to_4,
340 ceil(p_as_of_date-ps.due_date)),1,
341 0)
342 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
343 decode(v_bucket_category,
344 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
345 1))
346 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
347 decode(v_bucket_category,
348 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
349 1))) b4,
350 decode(v_bucket_line_type_5,
351 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
352 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
353 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
354 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
355 1),
356 decode( greatest(v_bucket_days_from_5,
357 ceil(p_as_of_date-ps.due_date)),
358 least(v_bucket_days_to_5,
359 ceil(p_as_of_date-ps.due_date)),1,
360 0)
361 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
362 decode(v_bucket_category,
363 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
364 1))
365 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
366 decode(v_bucket_category,
367 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
368 1))) b5,
369 decode(v_bucket_line_type_6,
370 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
371 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
372 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
373 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
374 1),
375 decode( greatest(v_bucket_days_from_6,
376 ceil(p_as_of_date-ps.due_date)),
377 least(v_bucket_days_to_6,
378 ceil(p_as_of_date-ps.due_date)),1,
379 0)
380 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
381 decode(v_bucket_category,
382 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
383 1))
387 1))) b6
384 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
385 decode(v_bucket_category,
386 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
388 from ar_payment_schedules ps,
389 ar_receivable_applications app
390 where app.gl_date+0 <= p_as_of_date
391 and ps.cash_receipt_id+0 = app.cash_receipt_id
392 and app.status in ( 'ACC', 'UNAPP', 'UNID','OTHER ACC')
393 and nvl(app.confirmed_flag, 'Y') = 'Y'
394 and ps.gl_date_closed > p_as_of_date
395 and (app.reversal_gl_date > p_as_of_date OR
396 app.reversal_gl_date is null )
397 and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
398 and ps.customer_id = p_customer_id
399 and decode(p_customer_site_use_id,
400 NULL, nvl(ps.customer_site_use_id,-10),
401 p_customer_site_use_id) = nvl(ps.customer_site_use_id,-10)
402 and decode(upper(p_currency_code),
403 NULL, ps.invoice_currency_code,
404 upper(p_currency_code)) = ps.invoice_currency_code
405 and decode(p_credit_option,'AGE','AGE','dummy') = 'AGE' /*4436914*/
406 and decode(upper(p_credit_option),
407 'AGE', 'dummy','SUMMARY','dummy',
408 'CM') <> ps.class
409 and decode(upper(p_credit_option),
410 'AGE', 'dummy','SUMMARY','dummy',
411 'PMT') <> ps.class
412 group by ps.due_date,
413 ps.amount_due_original,
414 ps.amount_adjusted,
415 ps.amount_applied,
416 ps.amount_credited,
417 ps.gl_date,
418 ps.amount_in_dispute,
419 ps.amount_adjusted_pending,
420 ps.invoice_currency_code,
421 ps.exchange_rate,
422 ps.class,
423 decode( app.status, 'UNID', 'UNID', 'UNAPP')
424 UNION ALL /*Bug 4436914 excluded APP and adjustments after as of date*/
425 select nvl(sum(decode(p_currency_code, NULL, app.amount_applied,
426 app.acctd_amount_applied_from)),0) amount_due_remaining,
427 decode(v_bucket_line_type_0,
428 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
429 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
430 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
431 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
432 1),
433 decode( greatest(v_bucket_days_from_0,
434 ceil(p_as_of_date-ps.due_date)),
435 least(v_bucket_days_to_0,
436 ceil(p_as_of_date-ps.due_date)),1,
437 0)
438 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
439 decode(v_bucket_category,
440 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
441 1))
442 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
443 decode(v_bucket_category,
444 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
445 1))) b0,
446 decode(v_bucket_line_type_1,
447 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
448 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
449 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
450 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
451 1),
452 decode( greatest(v_bucket_days_from_1,
453 ceil(p_as_of_date-ps.due_date)),
454 least(v_bucket_days_to_1,
455 ceil(p_as_of_date-ps.due_date)),1,
456 0)
457 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
458 decode(v_bucket_category,
459 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
460 1))
461 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
462 decode(v_bucket_category,
463 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
464 1)))b1,
465 decode(v_bucket_line_type_2,
466 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
467 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
468 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
469 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
470 1),
471 decode( greatest(v_bucket_days_from_2,
472 ceil(p_as_of_date-ps.due_date)),
473 least(v_bucket_days_to_2,
474 ceil(p_as_of_date-ps.due_date)),1,
475 0)
476 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
477 decode(v_bucket_category,
478 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
479 1))
480 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
481 decode(v_bucket_category,
482 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
486 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
483 1))) b2,
484 decode(v_bucket_line_type_3,
485 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
487 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
488 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
489 1),
490 decode( greatest(v_bucket_days_from_3,
491 ceil(p_as_of_date-ps.due_date)),
492 least(v_bucket_days_to_3,
493 ceil(p_as_of_date-ps.due_date)),1,
494 0)
495 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
496 decode(v_bucket_category,
497 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
498 1))
499 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
500 decode(v_bucket_category,
501 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
502 1))) b3,
503 decode(v_bucket_line_type_4,
504 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
505 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
506 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
507 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
508 1),
509 decode( greatest(v_bucket_days_from_4,
510 ceil(p_as_of_date-ps.due_date)),
511 least(v_bucket_days_to_4,
512 ceil(p_as_of_date-ps.due_date)),1,
513 0)
514 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
515 decode(v_bucket_category,
516 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
517 1))
518 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
519 decode(v_bucket_category,
520 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
521 1))) b4,
522 decode(v_bucket_line_type_5,
523 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
524 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
525 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
526 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
527 1),
528 decode( greatest(v_bucket_days_from_5,
529 ceil(p_as_of_date-ps.due_date)),
530 least(v_bucket_days_to_5,
531 ceil(p_as_of_date-ps.due_date)),1,
532 0)
533 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
534 decode(v_bucket_category,
535 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
536 1))
537 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
538 decode(v_bucket_category,
539 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
540 1))) b5,
541 decode(v_bucket_line_type_6,
542 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
543 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
544 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
545 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
546 1),
547 decode( greatest(v_bucket_days_from_6,
548 ceil(p_as_of_date-ps.due_date)),
549 least(v_bucket_days_to_6,
550 ceil(p_as_of_date-ps.due_date)),1,
551 0)
552 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
553 decode(v_bucket_category,
554 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
555 1))
556 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
557 decode(v_bucket_category,
558 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
559 1))) b6
560 from ar_payment_schedules ps,
561 ar_receivable_applications app
562 where app.gl_date+0 > p_as_of_date
563 and ps.cash_receipt_id = app.cash_receipt_id /*4436914*/
564 and (ps.payment_schedule_id = app.applied_payment_schedule_id
565 OR
566 ps.payment_schedule_id = app.payment_schedule_id)
567 and app.status in ( 'APP')
568 and nvl(app.confirmed_flag, 'Y') = 'Y'
569 and ps.gl_date_closed > p_as_of_date
570 and (app.reversal_gl_date > p_as_of_date OR
571 app.reversal_gl_date is null )
572 and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
573 and ps.customer_id = p_customer_id
574 and decode(p_customer_site_use_id,
575 NULL, nvl(ps.customer_site_use_id,-10),
576 p_customer_site_use_id) = nvl(ps.customer_site_use_id,-10)
580 and decode(upper(p_credit_option),
577 and decode(upper(p_currency_code),
578 NULL, ps.invoice_currency_code,
579 upper(p_currency_code)) = ps.invoice_currency_code
581 'AGE', 'dummy','SUMMARY','dummy',
582 'CM') <> ps.class
583 and decode(upper(p_credit_option),
584 'AGE', 'dummy','SUMMARY','dummy',
585 'PMT') <> ps.class
586 group by ps.due_date,
587 ps.amount_due_original,
588 ps.amount_adjusted,
589 ps.amount_applied,
590 ps.amount_credited,
591 ps.gl_date,
592 ps.amount_in_dispute,
593 ps.amount_adjusted_pending,
594 ps.invoice_currency_code,
595 ps.exchange_rate,
596 ps.class,
597 ps.payment_schedule_id
598 UNION ALL
599 SELECT -sum(nvl(adj.amount,0)) amount_due_remaining,1,0,0,0,0,0,0
600 FROM ar_adjustments adj,
601 ar_payment_schedules_all ps
602 WHERE adj.GL_date > p_as_of_date
603 AND ps.payment_schedule_id = adj.payment_schedule_id
604 AND adj.status = 'A'
605 AND ps.gl_date_closed > p_as_of_date
606 AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
607 AND ps.customer_id = p_customer_id
608 AND decode(p_customer_site_use_id,
609 NULL, nvl(ps.customer_site_use_id,-10),
610 p_customer_site_use_id) = nvl(ps.customer_site_use_id,-10)
611 AND decode(upper(p_currency_code),NULL, ps.invoice_currency_code,upper(p_currency_code))= ps.invoice_currency_code
612 AND decode(upper(p_credit_option),'AGE', 'dummy','SUMMARY','dummy','CM') <> ps.class
613 AND decode(upper(p_credit_option),'AGE', 'dummy','SUMMARY','dummy','PMT') <> ps.class
614 group by ps.due_date,
615 ps.amount_due_original,
616 ps.amount_adjusted,
617 ps.amount_applied,
618 ps.amount_credited,
619 ps.gl_date,
620 ps.amount_in_dispute,
621 ps.amount_adjusted_pending,
622 ps.invoice_currency_code,
623 ps.exchange_rate,
624 ps.class;
625
626
627 /* bug4047166 : the cursor c_buckets_2 will be used as if site use id is not null
628 and trx types are null */
629
630 CURSOR c_buckets_2 IS
631 select decode(p_currency_code, NULL, ps.acctd_amount_due_remaining,
632 ps.amount_due_remaining),
633 decode(v_bucket_line_type_0,
634 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
635 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
636 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
637 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
638 1),
639 decode( greatest(v_bucket_days_from_0,
640 ceil(p_as_of_date-ps.due_date)),
641 least(v_bucket_days_to_0,
642 ceil(p_as_of_date-ps.due_date)),1,
643 0)
644 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
645 decode(v_bucket_category,
646 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
647 1))
648 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
649 decode(v_bucket_category,
650 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
651 1))) b0,
652 decode(v_bucket_line_type_1,
653 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
654 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
655 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
656 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
657 1),
658 decode( greatest(v_bucket_days_from_1,
659 ceil(p_as_of_date-ps.due_date)),
660 least(v_bucket_days_to_1,
661 ceil(p_as_of_date-ps.due_date)),1,
662 0)
663 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
664 decode(v_bucket_category,
665 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
666 1))
667 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
668 decode(v_bucket_category,
669 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
670 1))) b1,
671 decode(v_bucket_line_type_2,
672 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
673 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
674 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
675 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
676 1),
677 decode( greatest(v_bucket_days_from_2,
678 ceil(p_as_of_date-ps.due_date)),
679 least(v_bucket_days_to_2,
680 ceil(p_as_of_date-ps.due_date)),1,
681 0)
682 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
683 decode(v_bucket_category,
684 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
685 1))
686 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
687 decode(v_bucket_category,
688 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
689 1))) b2,
690 decode(v_bucket_line_type_3,
691 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
692 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
693 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
694 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
695 1),
696 decode( greatest(v_bucket_days_from_3,
697 ceil(p_as_of_date-ps.due_date)),
698 least(v_bucket_days_to_3,
702 decode(v_bucket_category,
699 ceil(p_as_of_date-ps.due_date)),1,
700 0)
701 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
703 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
704 1))
705 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
706 decode(v_bucket_category,
707 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
708 1))) b3,
709 decode(v_bucket_line_type_4,
710 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
711 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
712 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
713 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
714 1),
715 decode( greatest(v_bucket_days_from_4,
716 ceil(p_as_of_date-ps.due_date)),
717 least(v_bucket_days_to_4,
718 ceil(p_as_of_date-ps.due_date)),1,
719 0)
720 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
721 decode(v_bucket_category,
722 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
723 1))
724 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
725 decode(v_bucket_category,
726 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
727 1))) b4,
728 decode(v_bucket_line_type_5,
729 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
730 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
731 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
732 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
733 1),
734 decode( greatest(v_bucket_days_from_5,
735 ceil(p_as_of_date-ps.due_date)),
736 least(v_bucket_days_to_5,
737 ceil(p_as_of_date-ps.due_date)),1,
738 0)
739 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
740 decode(v_bucket_category,
741 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
742 1))
743 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
744 decode(v_bucket_category,
745 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
746 1))) b5,
747 decode(v_bucket_line_type_6,
748 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
749 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
750 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
751 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
752 1),
753 decode( greatest(v_bucket_days_from_6,
754 ceil(p_as_of_date-ps.due_date)),
755 least(v_bucket_days_to_6,
756 ceil(p_as_of_date-ps.due_date)),1,
757 0)
758 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
759 decode(v_bucket_category,
760 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
761 1))
762 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
763 decode(v_bucket_category,
764 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
765 1))) b6
766 from ar_payment_schedules ps,
767 ra_cust_trx_types rctt
768 where ps.gl_date <= p_as_of_date
769 and ps.gl_date_closed > p_as_of_date
770 and ps.cust_trx_type_id = rctt.cust_trx_type_id
771 and ps.customer_id = p_customer_id
772 and ps.customer_site_use_id = p_customer_site_use_id
773 and decode(upper(p_currency_code),
774 NULL, ps.invoice_currency_code,
775 upper(p_currency_code)) = ps.invoice_currency_code
776 and decode(upper(p_credit_option),
777 'AGE', 'dummy','SUMMARY','dummy',
778 'CM') <> ps.class
779 and decode(upper(p_credit_option),
780 'AGE', 'dummy', 'SUMMARY','dummy',
781 'PMT') <> ps.class
782 UNION ALL
783 select -sum(decode(p_currency_code, NULL, app.acctd_amount_applied_from,
784 app.amount_applied)),
785 decode(v_bucket_line_type_0,
786 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
787 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
788 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
789 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
790 1),
791 decode( greatest(v_bucket_days_from_0,
792 ceil(p_as_of_date-ps.due_date)),
793 least(v_bucket_days_to_0,
794 ceil(p_as_of_date-ps.due_date)),1,
795 0)
796 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
797 decode(v_bucket_category,
798 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
799 1))
800 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
801 decode(v_bucket_category,
802 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
803 1))) b0,
804 decode(v_bucket_line_type_1,
805 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
806 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
807 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
808 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
809 1),
810 decode( greatest(v_bucket_days_from_1,
811 ceil(p_as_of_date-ps.due_date)),
812 least(v_bucket_days_to_1,
813 ceil(p_as_of_date-ps.due_date)),1,
814 0)
815 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
816 decode(v_bucket_category,
817 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
818 1))
819 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
820 decode(v_bucket_category,
821 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
822 1)))b1,
823 decode(v_bucket_line_type_2,
824 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
825 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
826 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
827 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
828 1),
829 decode( greatest(v_bucket_days_from_2,
830 ceil(p_as_of_date-ps.due_date)),
831 least(v_bucket_days_to_2,
832 ceil(p_as_of_date-ps.due_date)),1,
833 0)
834 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
835 decode(v_bucket_category,
836 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
837 1))
838 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
839 decode(v_bucket_category,
840 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
841 1))) b2,
842 decode(v_bucket_line_type_3,
843 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
844 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
845 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
846 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
847 1),
848 decode( greatest(v_bucket_days_from_3,
849 ceil(p_as_of_date-ps.due_date)),
850 least(v_bucket_days_to_3,
851 ceil(p_as_of_date-ps.due_date)),1,
852 0)
853 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
854 decode(v_bucket_category,
855 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
856 1))
857 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
858 decode(v_bucket_category,
859 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
860 1))) b3,
861 decode(v_bucket_line_type_4,
862 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
863 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
864 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
865 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
866 1),
867 decode( greatest(v_bucket_days_from_4,
868 ceil(p_as_of_date-ps.due_date)),
872 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
869 least(v_bucket_days_to_4,
870 ceil(p_as_of_date-ps.due_date)),1,
871 0)
873 decode(v_bucket_category,
874 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
875 1))
876 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
877 decode(v_bucket_category,
878 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
879 1))) b4,
880 decode(v_bucket_line_type_5,
881 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
882 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
883 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
884 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
885 1),
886 decode( greatest(v_bucket_days_from_5,
887 ceil(p_as_of_date-ps.due_date)),
888 least(v_bucket_days_to_5,
889 ceil(p_as_of_date-ps.due_date)),1,
890 0)
891 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
892 decode(v_bucket_category,
893 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
894 1))
895 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
896 decode(v_bucket_category,
897 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
898 1))) b5,
899 decode(v_bucket_line_type_6,
900 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
901 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
902 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
903 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
904 1),
905 decode( greatest(v_bucket_days_from_6,
906 ceil(p_as_of_date-ps.due_date)),
907 least(v_bucket_days_to_6,
908 ceil(p_as_of_date-ps.due_date)),1,
909 0)
910 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
911 decode(v_bucket_category,
912 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
913 1))
914 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
915 decode(v_bucket_category,
916 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
917 1))) b6
918 from ar_payment_schedules ps,
919 ar_receivable_applications app
920 where app.gl_date+0 <= p_as_of_date
921 and ps.cash_receipt_id+0 = app.cash_receipt_id
922 and app.status in ( 'ACC', 'UNAPP', 'UNID','OTHER ACC')
923 and nvl(app.confirmed_flag, 'Y') = 'Y'
924 and ps.gl_date_closed > p_as_of_date
925 and (app.reversal_gl_date > p_as_of_date OR
926 app.reversal_gl_date is null )
927 and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
928 and ps.customer_id = p_customer_id
929 and ps.customer_site_use_id = p_customer_site_use_id
930 and decode(upper(p_currency_code),
931 NULL, ps.invoice_currency_code,
932 upper(p_currency_code)) = ps.invoice_currency_code
933 and decode(p_credit_option,'AGE','AGE','dummy') = 'AGE' /*4436914*/
934 and decode(upper(p_credit_option),
935 'AGE', 'dummy','SUMMARY','dummy',
936 'CM') <> ps.class
937 and decode(upper(p_credit_option),
938 'AGE', 'dummy','SUMMARY','dummy',
939 'PMT') <> ps.class
940 group by ps.due_date,
941 ps.amount_due_original,
942 ps.amount_adjusted,
943 ps.amount_applied,
944 ps.amount_credited,
945 ps.gl_date,
946 ps.amount_in_dispute,
947 ps.amount_adjusted_pending,
948 ps.invoice_currency_code,
949 ps.exchange_rate,
950 ps.class,
951 decode( app.status, 'UNID', 'UNID', 'UNAPP')
955 decode(v_bucket_line_type_0,
952 UNION ALL /*Bug 4436914 excluded APP and adjustments after as of date*/
953 select nvl(sum(decode(p_currency_code, NULL, app.amount_applied,
954 app.acctd_amount_applied_from)),0) amount_due_remaining,
956 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
957 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
958 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
959 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
960 1),
961 decode( greatest(v_bucket_days_from_0,
962 ceil(p_as_of_date-ps.due_date)),
963 least(v_bucket_days_to_0,
964 ceil(p_as_of_date-ps.due_date)),1,
968 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
965 0)
966 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
967 decode(v_bucket_category,
969 1))
970 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
971 decode(v_bucket_category,
972 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
973 1))) b0,
974 decode(v_bucket_line_type_1,
975 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
976 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
977 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
978 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
979 1),
980 decode( greatest(v_bucket_days_from_1,
981 ceil(p_as_of_date-ps.due_date)),
982 least(v_bucket_days_to_1,
983 ceil(p_as_of_date-ps.due_date)),1,
984 0)
985 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
986 decode(v_bucket_category,
987 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
988 1))
989 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
990 decode(v_bucket_category,
991 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
992 1)))b1,
993 decode(v_bucket_line_type_2,
994 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
995 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
996 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
997 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
998 1),
999 decode( greatest(v_bucket_days_from_2,
1000 ceil(p_as_of_date-ps.due_date)),
1001 least(v_bucket_days_to_2,
1002 ceil(p_as_of_date-ps.due_date)),1,
1003 0)
1007 1))
1004 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1005 decode(v_bucket_category,
1006 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1008 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1009 decode(v_bucket_category,
1010 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1011 1))) b2,
1012 decode(v_bucket_line_type_3,
1013 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1014 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1015 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1016 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1017 1),
1018 decode( greatest(v_bucket_days_from_3,
1019 ceil(p_as_of_date-ps.due_date)),
1020 least(v_bucket_days_to_3,
1021 ceil(p_as_of_date-ps.due_date)),1,
1022 0)
1023 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1027 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1024 decode(v_bucket_category,
1025 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1026 1))
1031 decode(v_bucket_line_type_4,
1028 decode(v_bucket_category,
1029 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1030 1))) b3,
1032 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1033 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1034 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1035 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1039 least(v_bucket_days_to_4,
1036 1),
1037 decode( greatest(v_bucket_days_from_4,
1038 ceil(p_as_of_date-ps.due_date)),
1040 ceil(p_as_of_date-ps.due_date)),1,
1041 0)
1042 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1043 decode(v_bucket_category,
1044 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1045 1))
1046 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1047 decode(v_bucket_category,
1048 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1049 1))) b4,
1053 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1050 decode(v_bucket_line_type_5,
1051 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1052 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1054 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1055 1),
1056 decode( greatest(v_bucket_days_from_5,
1057 ceil(p_as_of_date-ps.due_date)),
1058 least(v_bucket_days_to_5,
1059 ceil(p_as_of_date-ps.due_date)),1,
1060 0)
1061 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1062 decode(v_bucket_category,
1063 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1064 1))
1065 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1066 decode(v_bucket_category,
1067 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1068 1))) b5,
1069 decode(v_bucket_line_type_6,
1070 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1071 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1072 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1073 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1074 1),
1075 decode( greatest(v_bucket_days_from_6,
1076 ceil(p_as_of_date-ps.due_date)),
1077 least(v_bucket_days_to_6,
1078 ceil(p_as_of_date-ps.due_date)),1,
1079 0)
1080 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1081 decode(v_bucket_category,
1082 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1083 1))
1084 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1085 decode(v_bucket_category,
1086 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1087 1))) b6
1088 from ar_payment_schedules ps,
1089 ar_receivable_applications app
1090 where app.gl_date+0 > p_as_of_date
1091 and ps.cash_receipt_id = app.cash_receipt_id /*4436914*/
1092 and (ps.payment_schedule_id = app.applied_payment_schedule_id
1093 OR
1094 ps.payment_schedule_id = app.payment_schedule_id)
1095 and app.status in ( 'APP')
1096 and nvl(app.confirmed_flag, 'Y') = 'Y'
1097 and ps.gl_date_closed > p_as_of_date
1098 and (app.reversal_gl_date > p_as_of_date OR
1099 app.reversal_gl_date is null )
1100 and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
1101 and ps.customer_id = p_customer_id
1102 and ps.customer_site_use_id = p_customer_site_use_id
1103 and decode(upper(p_currency_code),
1104 NULL, ps.invoice_currency_code,
1105 upper(p_currency_code)) = ps.invoice_currency_code
1106 and decode(upper(p_credit_option),
1110 'AGE', 'dummy','SUMMARY','dummy',
1107 'AGE', 'dummy','SUMMARY','dummy',
1108 'CM') <> ps.class
1109 and decode(upper(p_credit_option),
1111 'PMT') <> ps.class
1112 group by ps.due_date,
1113 ps.amount_due_original,
1114 ps.amount_adjusted,
1115 ps.amount_applied,
1116 ps.amount_credited,
1117 ps.gl_date,
1118 ps.amount_in_dispute,
1119 ps.amount_adjusted_pending,
1120 ps.invoice_currency_code,
1121 ps.exchange_rate,
1122 ps.class,
1123 ps.payment_schedule_id
1124 UNION ALL
1125 SELECT -sum(nvl(adj.amount,0)) amount_due_remaining,1,0,0,0,0,0,0
1126 FROM ar_adjustments adj,
1127 ar_payment_schedules_all ps
1128 WHERE adj.GL_date > p_as_of_date
1129 AND ps.payment_schedule_id = adj.payment_schedule_id
1130 AND adj.status = 'A'
1131 AND ps.gl_date_closed > p_as_of_date
1132 AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
1133 AND ps.customer_id = p_customer_id
1134 AND ps.customer_site_use_id = p_customer_site_use_id
1135 AND decode(upper(p_currency_code),NULL, ps.invoice_currency_code,upper(p_currency_code))= ps.invoice_currency_code
1136 AND decode(upper(p_credit_option),'AGE', 'dummy','SUMMARY','dummy','CM') <> ps.class
1137 AND decode(upper(p_credit_option),'AGE', 'dummy','SUMMARY','dummy','PMT') <> ps.class
1138 group by ps.due_date,
1139 ps.amount_due_original,
1140 ps.amount_adjusted,
1141 ps.amount_applied,
1142 ps.amount_credited,
1143 ps.gl_date,
1144 ps.amount_in_dispute,
1148 ps.class;
1145 ps.amount_adjusted_pending,
1146 ps.invoice_currency_code,
1147 ps.exchange_rate,
1149
1150 /* bug4047166 : the cursor c_buckets_3 will be used as if site use id is not null
1151 and trx types are not null */
1152
1153 CURSOR c_buckets_3 IS
1154 select decode(p_currency_code, NULL, ps.acctd_amount_due_remaining,
1155 ps.amount_due_remaining),
1156 decode(v_bucket_line_type_0,
1157 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1158 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1159 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1160 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1161 1),
1162 decode( greatest(v_bucket_days_from_0,
1163 ceil(p_as_of_date-ps.due_date)),
1164 least(v_bucket_days_to_0,
1165 ceil(p_as_of_date-ps.due_date)),1,
1166 0)
1167 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1168 decode(v_bucket_category,
1169 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1170 1))
1171 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1172 decode(v_bucket_category,
1173 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1174 1))) b0,
1175 decode(v_bucket_line_type_1,
1176 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1177 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1178 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1179 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1180 1),
1181 decode( greatest(v_bucket_days_from_1,
1182 ceil(p_as_of_date-ps.due_date)),
1183 least(v_bucket_days_to_1,
1184 ceil(p_as_of_date-ps.due_date)),1,
1185 0)
1186 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1187 decode(v_bucket_category,
1188 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1189 1))
1190 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1191 decode(v_bucket_category,
1192 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1193 1))) b1,
1194 decode(v_bucket_line_type_2,
1195 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1196 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1197 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1198 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1199 1),
1200 decode( greatest(v_bucket_days_from_2,
1201 ceil(p_as_of_date-ps.due_date)),
1202 least(v_bucket_days_to_2,
1203 ceil(p_as_of_date-ps.due_date)),1,
1204 0)
1205 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1206 decode(v_bucket_category,
1207 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1208 1))
1209 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1210 decode(v_bucket_category,
1211 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1212 1))) b2,
1213 decode(v_bucket_line_type_3,
1214 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1215 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1216 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1217 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1218 1),
1219 decode( greatest(v_bucket_days_from_3,
1220 ceil(p_as_of_date-ps.due_date)),
1221 least(v_bucket_days_to_3,
1222 ceil(p_as_of_date-ps.due_date)),1,
1223 0)
1224 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1225 decode(v_bucket_category,
1226 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1227 1))
1228 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1229 decode(v_bucket_category,
1230 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1231 1))) b3,
1232 decode(v_bucket_line_type_4,
1233 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1234 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1235 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1236 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1237 1),
1238 decode( greatest(v_bucket_days_from_4,
1239 ceil(p_as_of_date-ps.due_date)),
1240 least(v_bucket_days_to_4,
1241 ceil(p_as_of_date-ps.due_date)),1,
1242 0)
1243 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1244 decode(v_bucket_category,
1245 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1246 1))
1247 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1248 decode(v_bucket_category,
1249 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1250 1))) b4,
1251 decode(v_bucket_line_type_5,
1252 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1253 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1254 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1255 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1256 1),
1257 decode( greatest(v_bucket_days_from_5,
1258 ceil(p_as_of_date-ps.due_date)),
1259 least(v_bucket_days_to_5,
1260 ceil(p_as_of_date-ps.due_date)),1,
1261 0)
1262 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1263 decode(v_bucket_category,
1264 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1265 1))
1266 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1267 decode(v_bucket_category,
1268 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1269 1))) b5,
1270 decode(v_bucket_line_type_6,
1271 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1272 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1273 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1274 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1275 1),
1276 decode( greatest(v_bucket_days_from_6,
1280 0)
1277 ceil(p_as_of_date-ps.due_date)),
1278 least(v_bucket_days_to_6,
1279 ceil(p_as_of_date-ps.due_date)),1,
1284 1))
1281 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1282 decode(v_bucket_category,
1283 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1285 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1286 decode(v_bucket_category,
1287 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1288 1))) b6
1289 from ra_cust_trx_types,
1290 ar_payment_schedules ps
1291 where ps.gl_date <= p_as_of_date
1292 and ps.cust_trx_type_id = ra_cust_trx_types.cust_trx_type_id
1293 and ps.gl_date_closed > p_as_of_date
1294 and ps.customer_id = p_customer_id
1295 and ps.customer_site_use_id = p_customer_site_use_id
1296 and decode(upper(p_currency_code),
1297 NULL, ps.invoice_currency_code,
1298 upper(p_currency_code)) = ps.invoice_currency_code
1299 and decode(upper(p_credit_option),
1300 'AGE', 'dummy', 'SUMMARY','dummy',
1301 'CM') <> ps.class
1302 and decode(upper(p_credit_option),
1303 'AGE', 'dummy', 'SUMMARY','dummy',
1304 'PMT') <> ps.class
1305 and decode(p_invoice_type_low,
1306 NULL, ra_cust_trx_types.name,
1307 p_invoice_type_low) <= ra_cust_trx_types.name
1308 and decode(p_invoice_type_high,
1309 NULL, ra_cust_trx_types.name,
1310 p_invoice_type_high) >= ra_cust_trx_types.name
1311 UNION ALL
1312 select -sum(decode(p_currency_code, NULL, app.acctd_amount_applied_from,
1313 app.amount_applied)),
1314 decode(v_bucket_line_type_0,
1315 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1316 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1317 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1318 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1319 1),
1320 decode( greatest(v_bucket_days_from_0,
1321 ceil(p_as_of_date-ps.due_date)),
1322 least(v_bucket_days_to_0,
1323 ceil(p_as_of_date-ps.due_date)),1,
1324 0)
1325 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1326 decode(v_bucket_category,
1327 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1328 1))
1329 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1330 decode(v_bucket_category,
1331 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1332 1))) b0,
1333 decode(v_bucket_line_type_1,
1334 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1335 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1336 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1337 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1338 1),
1339 decode( greatest(v_bucket_days_from_1,
1340 ceil(p_as_of_date-ps.due_date)),
1341 least(v_bucket_days_to_1,
1342 ceil(p_as_of_date-ps.due_date)),1,
1343 0)
1344 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1345 decode(v_bucket_category,
1346 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1347 1))
1348 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1349 decode(v_bucket_category,
1350 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1351 1)))b1,
1352 decode(v_bucket_line_type_2,
1353 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1354 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1355 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1356 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1357 1),
1358 decode( greatest(v_bucket_days_from_2,
1359 ceil(p_as_of_date-ps.due_date)),
1360 least(v_bucket_days_to_2,
1361 ceil(p_as_of_date-ps.due_date)),1,
1362 0)
1363 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1364 decode(v_bucket_category,
1365 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1366 1))
1367 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1368 decode(v_bucket_category,
1369 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1370 1))) b2,
1371 decode(v_bucket_line_type_3,
1372 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1373 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1374 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1375 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1376 1),
1377 decode( greatest(v_bucket_days_from_3,
1378 ceil(p_as_of_date-ps.due_date)),
1379 least(v_bucket_days_to_3,
1380 ceil(p_as_of_date-ps.due_date)),1,
1381 0)
1382 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1383 decode(v_bucket_category,
1384 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1385 1))
1386 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1387 decode(v_bucket_category,
1388 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1389 1))) b3,
1390 decode(v_bucket_line_type_4,
1391 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1392 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1393 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1394 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1395 1),
1396 decode( greatest(v_bucket_days_from_4,
1397 ceil(p_as_of_date-ps.due_date)),
1398 least(v_bucket_days_to_4,
1399 ceil(p_as_of_date-ps.due_date)),1,
1400 0)
1401 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1402 decode(v_bucket_category,
1403 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1404 1))
1405 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1406 decode(v_bucket_category,
1407 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1408 1))) b4,
1409 decode(v_bucket_line_type_5,
1413 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1410 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1411 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1412 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1414 1),
1415 decode( greatest(v_bucket_days_from_5,
1416 ceil(p_as_of_date-ps.due_date)),
1417 least(v_bucket_days_to_5,
1418 ceil(p_as_of_date-ps.due_date)),1,
1419 0)
1420 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1421 decode(v_bucket_category,
1422 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1423 1))
1424 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1425 decode(v_bucket_category,
1426 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1427 1))) b5,
1428 decode(v_bucket_line_type_6,
1429 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1430 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1431 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1432 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1433 1),
1434 decode( greatest(v_bucket_days_from_6,
1435 ceil(p_as_of_date-ps.due_date)),
1436 least(v_bucket_days_to_6,
1437 ceil(p_as_of_date-ps.due_date)),1,
1438 0)
1439 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1440 decode(v_bucket_category,
1441 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1442 1))
1443 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1444 decode(v_bucket_category,
1445 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1446 1))) b6
1447 from ar_payment_schedules ps,
1448 ar_receivable_applications app
1449 where app.gl_date+0 <= p_as_of_date
1450 and ps.cash_receipt_id+0 = app.cash_receipt_id
1451 and app.status in ( 'ACC', 'UNAPP', 'UNID','OTHER ACC')
1452 and nvl(app.confirmed_flag, 'Y') = 'Y'
1453 and ps.gl_date_closed > p_as_of_date
1454 and (app.reversal_gl_date > p_as_of_date OR
1455 app.reversal_gl_date is null )
1456 and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
1457 and ps.customer_id = p_customer_id
1458 and ps.customer_site_use_id = p_customer_site_use_id
1459 and decode(upper(p_currency_code),
1460 NULL, ps.invoice_currency_code,
1461 upper(p_currency_code)) = ps.invoice_currency_code
1462 and decode(p_credit_option,'AGE','AGE','dummy') = 'AGE' /*4436914*/
1463 and decode(upper(p_credit_option),
1464 'AGE', 'dummy', 'SUMMARY','dummy',
1465 'CM') <> ps.class
1466 and decode(upper(p_credit_option),
1467 'AGE', 'dummy', 'SUMMARY','dummy',
1468 'PMT') <> ps.class
1469 group by ps.due_date,
1470 ps.amount_due_original,
1471 ps.amount_adjusted,
1472 ps.amount_applied,
1473 ps.amount_credited,
1474 ps.gl_date,
1475 ps.amount_in_dispute,
1476 ps.amount_adjusted_pending,
1477 ps.invoice_currency_code,
1478 ps.exchange_rate,
1479 ps.class,
1480 decode( app.status, 'UNID', 'UNID', 'UNAPP')
1481 UNION ALL /*Bug 4436914 excluded APP and adjustments after as of date*/
1482 select nvl(sum(decode(p_currency_code, NULL, app.amount_applied,
1483 app.acctd_amount_applied_from)),0) amount_due_remaining,
1484 decode(v_bucket_line_type_0,
1485 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1486 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1487 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1488 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1489 1),
1490 decode( greatest(v_bucket_days_from_0,
1491 ceil(p_as_of_date-ps.due_date)),
1492 least(v_bucket_days_to_0,
1493 ceil(p_as_of_date-ps.due_date)),1,
1494 0)
1495 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1496 decode(v_bucket_category,
1497 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1498 1))
1499 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1500 decode(v_bucket_category,
1501 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1502 1))) b0,
1503 decode(v_bucket_line_type_1,
1504 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1505 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1506 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1507 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1508 1),
1509 decode( greatest(v_bucket_days_from_1,
1510 ceil(p_as_of_date-ps.due_date)),
1511 least(v_bucket_days_to_1,
1512 ceil(p_as_of_date-ps.due_date)),1,
1513 0)
1514 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1515 decode(v_bucket_category,
1516 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1517 1))
1518 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1519 decode(v_bucket_category,
1520 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1524 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1521 1)))b1,
1522 decode(v_bucket_line_type_2,
1523 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1525 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1526 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1527 1),
1528 decode( greatest(v_bucket_days_from_2,
1529 ceil(p_as_of_date-ps.due_date)),
1530 least(v_bucket_days_to_2,
1531 ceil(p_as_of_date-ps.due_date)),1,
1532 0)
1533 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1534 decode(v_bucket_category,
1535 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1536 1))
1537 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1538 decode(v_bucket_category,
1539 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1540 1))) b2,
1541 decode(v_bucket_line_type_3,
1542 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1543 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1544 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1545 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1546 1),
1550 ceil(p_as_of_date-ps.due_date)),1,
1547 decode( greatest(v_bucket_days_from_3,
1548 ceil(p_as_of_date-ps.due_date)),
1549 least(v_bucket_days_to_3,
1551 0)
1552 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1553 decode(v_bucket_category,
1554 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1555 1))
1556 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1557 decode(v_bucket_category,
1558 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1559 1))) b3,
1560 decode(v_bucket_line_type_4,
1561 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1562 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1563 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1564 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1565 1),
1566 decode( greatest(v_bucket_days_from_4,
1567 ceil(p_as_of_date-ps.due_date)),
1568 least(v_bucket_days_to_4,
1569 ceil(p_as_of_date-ps.due_date)),1,
1570 0)
1571 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1572 decode(v_bucket_category,
1573 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1574 1))
1575 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1576 decode(v_bucket_category,
1577 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1578 1))) b4,
1579 decode(v_bucket_line_type_5,
1580 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1581 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1582 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1583 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1584 1),
1585 decode( greatest(v_bucket_days_from_5,
1586 ceil(p_as_of_date-ps.due_date)),
1587 least(v_bucket_days_to_5,
1588 ceil(p_as_of_date-ps.due_date)),1,
1589 0)
1590 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1591 decode(v_bucket_category,
1592 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1593 1))
1594 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1595 decode(v_bucket_category,
1596 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1600 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1597 1))) b5,
1598 decode(v_bucket_line_type_6,
1599 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1601 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1602 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1603 1),
1604 decode( greatest(v_bucket_days_from_6,
1605 ceil(p_as_of_date-ps.due_date)),
1606 least(v_bucket_days_to_6,
1607 ceil(p_as_of_date-ps.due_date)),1,
1608 0)
1609 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1610 decode(v_bucket_category,
1611 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1612 1))
1613 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1614 decode(v_bucket_category,
1615 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1616 1))) b6
1617 from ar_payment_schedules ps,
1618 ar_receivable_applications app
1619 where app.gl_date+0 > p_as_of_date
1620 and ps.cash_receipt_id = app.cash_receipt_id /*4436914*/
1621 and (ps.payment_schedule_id = app.applied_payment_schedule_id
1622 OR
1623 ps.payment_schedule_id = app.payment_schedule_id)
1624 and app.status in ( 'APP')
1625 and nvl(app.confirmed_flag, 'Y') = 'Y'
1626 and ps.gl_date_closed > p_as_of_date
1627 and (app.reversal_gl_date > p_as_of_date OR
1628 app.reversal_gl_date is null )
1629 and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
1630 and ps.customer_id = p_customer_id
1631 and ps.customer_site_use_id = p_customer_site_use_id
1632 and decode(upper(p_currency_code),
1633 NULL, ps.invoice_currency_code,
1634 upper(p_currency_code)) = ps.invoice_currency_code
1635 and decode(upper(p_credit_option),
1636 'AGE', 'dummy','SUMMARY','dummy',
1637 'CM') <> ps.class
1638 and decode(upper(p_credit_option),
1639 'AGE', 'dummy','SUMMARY','dummy',
1640 'PMT') <> ps.class
1641 group by ps.due_date,
1642 ps.amount_due_original,
1643 ps.amount_adjusted,
1644 ps.amount_applied,
1645 ps.amount_credited,
1646 ps.gl_date,
1647 ps.amount_in_dispute,
1648 ps.amount_adjusted_pending,
1649 ps.invoice_currency_code,
1650 ps.exchange_rate,
1651 ps.class,
1652 ps.payment_schedule_id
1653 UNION ALL
1654 SELECT -sum(nvl(adj.amount,0)) amount_due_remaining,1,0,0,0,0,0,0
1655 FROM ar_adjustments adj,
1656 ar_payment_schedules_all ps
1657 WHERE adj.GL_date > p_as_of_date
1658 AND ps.payment_schedule_id = adj.payment_schedule_id
1659 AND adj.status = 'A'
1660 AND ps.gl_date_closed > p_as_of_date
1661 AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
1662 AND ps.customer_id = p_customer_id
1663 AND ps.customer_site_use_id = p_customer_site_use_id
1664 AND decode(upper(p_currency_code),NULL, ps.invoice_currency_code,upper(p_currency_code))= ps.invoice_currency_code
1665 AND decode(upper(p_credit_option),'AGE', 'dummy','SUMMARY','dummy','CM') <> ps.class
1666 AND decode(upper(p_credit_option),'AGE', 'dummy','SUMMARY','dummy','PMT') <> ps.class
1667 group by ps.due_date,
1668 ps.amount_due_original,
1669 ps.amount_adjusted,
1670 ps.amount_applied,
1671 ps.amount_credited,
1672 ps.gl_date,
1673 ps.amount_in_dispute,
1674 ps.amount_adjusted_pending,
1675 ps.invoice_currency_code,
1676 ps.exchange_rate,
1677 ps.class;
1678
1679 /* bug4047166 : the cursor c_buckets_4 will be used as if site use id is null
1680 and trx types are null */
1681
1682 CURSOR c_buckets_4 IS
1683 select decode(p_currency_code, NULL, ps.acctd_amount_due_remaining,
1684 ps.amount_due_remaining),
1685 decode(v_bucket_line_type_0,
1686 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1687 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1688 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1689 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1690 1),
1691 decode( greatest(v_bucket_days_from_0,
1692 ceil(p_as_of_date-ps.due_date)),
1693 least(v_bucket_days_to_0,
1694 ceil(p_as_of_date-ps.due_date)),1,
1695 0)
1696 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1697 decode(v_bucket_category,
1698 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1699 1))
1700 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1701 decode(v_bucket_category,
1702 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1703 1))) b0,
1704 decode(v_bucket_line_type_1,
1705 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1706 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1707 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1708 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1712 least(v_bucket_days_to_1,
1709 1),
1710 decode( greatest(v_bucket_days_from_1,
1711 ceil(p_as_of_date-ps.due_date)),
1713 ceil(p_as_of_date-ps.due_date)),1,
1714 0)
1715 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1716 decode(v_bucket_category,
1717 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1718 1))
1719 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1720 decode(v_bucket_category,
1721 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1722 1))) b1,
1723 decode(v_bucket_line_type_2,
1724 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1725 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1726 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1727 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1728 1),
1729 decode( greatest(v_bucket_days_from_2,
1730 ceil(p_as_of_date-ps.due_date)),
1731 least(v_bucket_days_to_2,
1732 ceil(p_as_of_date-ps.due_date)),1,
1733 0)
1734 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1735 decode(v_bucket_category,
1736 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1737 1))
1738 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1739 decode(v_bucket_category,
1740 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1741 1))) b2,
1742 decode(v_bucket_line_type_3,
1743 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1744 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1745 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1746 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1747 1),
1748 decode( greatest(v_bucket_days_from_3,
1749 ceil(p_as_of_date-ps.due_date)),
1750 least(v_bucket_days_to_3,
1751 ceil(p_as_of_date-ps.due_date)),1,
1752 0)
1753 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1754 decode(v_bucket_category,
1755 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1756 1))
1757 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1758 decode(v_bucket_category,
1759 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1760 1))) b3,
1761 decode(v_bucket_line_type_4,
1762 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1763 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1764 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1765 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1766 1),
1767 decode( greatest(v_bucket_days_from_4,
1768 ceil(p_as_of_date-ps.due_date)),
1769 least(v_bucket_days_to_4,
1770 ceil(p_as_of_date-ps.due_date)),1,
1771 0)
1772 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1773 decode(v_bucket_category,
1774 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1775 1))
1776 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1777 decode(v_bucket_category,
1778 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1779 1))) b4,
1780 decode(v_bucket_line_type_5,
1781 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1782 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1783 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1784 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1785 1),
1786 decode( greatest(v_bucket_days_from_5,
1787 ceil(p_as_of_date-ps.due_date)),
1788 least(v_bucket_days_to_5,
1789 ceil(p_as_of_date-ps.due_date)),1,
1790 0)
1791 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1792 decode(v_bucket_category,
1793 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1794 1))
1795 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1796 decode(v_bucket_category,
1797 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1798 1))) b5,
1799 decode(v_bucket_line_type_6,
1800 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1801 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1802 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1803 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1804 1),
1805 decode( greatest(v_bucket_days_from_6,
1806 ceil(p_as_of_date-ps.due_date)),
1807 least(v_bucket_days_to_6,
1808 ceil(p_as_of_date-ps.due_date)),1,
1809 0)
1810 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1811 decode(v_bucket_category,
1812 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1813 1))
1817 1))) b6
1814 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1815 decode(v_bucket_category,
1816 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1818 from ar_payment_schedules ps,
1819 ra_cust_trx_types rctt
1820 where ps.gl_date <= p_as_of_date
1821 and ps.gl_date_closed > p_as_of_date
1822 and ps.cust_trx_type_id = rctt.cust_trx_type_id
1823 and ps.customer_id = p_customer_id
1824 and decode(upper(p_currency_code),
1825 NULL, ps.invoice_currency_code,
1826 upper(p_currency_code)) = ps.invoice_currency_code
1827 and decode(upper(p_credit_option),
1828 'AGE', 'dummy', 'SUMMARY','dummy',
1829 'CM') <> ps.class
1830 and decode(upper(p_credit_option),
1831 'AGE', 'dummy', 'SUMMARY','dummy',
1832 'PMT') <> ps.class
1833 UNION ALL
1834 select -sum(decode(p_currency_code, NULL, app.acctd_amount_applied_from,
1835 app.amount_applied)),
1836 decode(v_bucket_line_type_0,
1837 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1838 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1839 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1840 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1841 1),
1842 decode( greatest(v_bucket_days_from_0,
1843 ceil(p_as_of_date-ps.due_date)),
1844 least(v_bucket_days_to_0,
1845 ceil(p_as_of_date-ps.due_date)),1,
1846 0)
1847 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1848 decode(v_bucket_category,
1849 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1850 1))
1851 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1852 decode(v_bucket_category,
1853 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1854 1))) b0,
1855 decode(v_bucket_line_type_1,
1856 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1857 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1858 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1859 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1860 1),
1861 decode( greatest(v_bucket_days_from_1,
1862 ceil(p_as_of_date-ps.due_date)),
1863 least(v_bucket_days_to_1,
1864 ceil(p_as_of_date-ps.due_date)),1,
1865 0)
1866 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1867 decode(v_bucket_category,
1868 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1869 1))
1870 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1871 decode(v_bucket_category,
1872 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1873 1)))b1,
1874 decode(v_bucket_line_type_2,
1875 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1876 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1877 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1878 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1879 1),
1880 decode( greatest(v_bucket_days_from_2,
1881 ceil(p_as_of_date-ps.due_date)),
1882 least(v_bucket_days_to_2,
1883 ceil(p_as_of_date-ps.due_date)),1,
1884 0)
1885 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1886 decode(v_bucket_category,
1887 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1888 1))
1889 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1890 decode(v_bucket_category,
1891 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1892 1))) b2,
1893 decode(v_bucket_line_type_3,
1894 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1895 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1896 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1897 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1898 1),
1899 decode( greatest(v_bucket_days_from_3,
1900 ceil(p_as_of_date-ps.due_date)),
1901 least(v_bucket_days_to_3,
1902 ceil(p_as_of_date-ps.due_date)),1,
1903 0)
1904 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1905 decode(v_bucket_category,
1906 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1907 1))
1908 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1909 decode(v_bucket_category,
1910 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1911 1))) b3,
1912 decode(v_bucket_line_type_4,
1913 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1914 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1915 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1916 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1917 1),
1918 decode( greatest(v_bucket_days_from_4,
1919 ceil(p_as_of_date-ps.due_date)),
1920 least(v_bucket_days_to_4,
1921 ceil(p_as_of_date-ps.due_date)),1,
1922 0)
1923 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1924 decode(v_bucket_category,
1925 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1926 1))
1927 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1928 decode(v_bucket_category,
1929 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1930 1))) b4,
1931 decode(v_bucket_line_type_5,
1932 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1933 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1934 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1935 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1936 1),
1937 decode( greatest(v_bucket_days_from_5,
1938 ceil(p_as_of_date-ps.due_date)),
1939 least(v_bucket_days_to_5,
1940 ceil(p_as_of_date-ps.due_date)),1,
1941 0)
1942 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1943 decode(v_bucket_category,
1944 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1945 1))
1946 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1947 decode(v_bucket_category,
1948 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1949 1))) b5,
1953 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
1950 decode(v_bucket_line_type_6,
1951 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
1952 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1954 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1955 1),
1956 decode( greatest(v_bucket_days_from_6,
1957 ceil(p_as_of_date-ps.due_date)),
1958 least(v_bucket_days_to_6,
1959 ceil(p_as_of_date-ps.due_date)),1,
1960 0)
1961 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
1962 decode(v_bucket_category,
1963 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1964 1))
1965 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
1966 decode(v_bucket_category,
1967 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1968 1))) b6
1969 from ar_payment_schedules ps,
1970 ar_receivable_applications app
1971 where app.gl_date+0 <= p_as_of_date
1972 and ps.cash_receipt_id+0 = app.cash_receipt_id
1973 and app.status in ( 'ACC', 'UNAPP', 'UNID','OTHER ACC')
1974 and nvl(app.confirmed_flag, 'Y') = 'Y'
1975 and ps.gl_date_closed > p_as_of_date
1976 and (app.reversal_gl_date > p_as_of_date OR
1977 app.reversal_gl_date is null )
1978 and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
1979 and ps.customer_id = p_customer_id
1980 and decode(upper(p_currency_code),
1981 NULL, ps.invoice_currency_code,
1982 upper(p_currency_code)) = ps.invoice_currency_code
1983 and decode(p_credit_option,'AGE','AGE','dummy') = 'AGE' /*4436914*/
1984 and decode(upper(p_credit_option),
1985 'AGE', 'dummy', 'SUMMARY','dummy',
1986 'CM') <> ps.class
1987 and decode(upper(p_credit_option),
1988 'AGE', 'dummy', 'SUMMARY','dummy',
1989 'PMT') <> ps.class
1990 group by ps.due_date,
1991 ps.amount_due_original,
1992 ps.amount_adjusted,
1993 ps.amount_applied,
1994 ps.amount_credited,
1995 ps.gl_date,
1996 ps.amount_in_dispute,
1997 ps.amount_adjusted_pending,
1998 ps.invoice_currency_code,
1999 ps.exchange_rate,
2000 ps.class,
2001 decode( app.status, 'UNID', 'UNID', 'UNAPP')
2002 UNION ALL /*Bug 4436914 excluded APP and adjustments after as of date*/
2003 select nvl(sum(decode(p_currency_code, NULL, app.amount_applied,
2004 app.acctd_amount_applied_from)),0) amount_due_remaining,
2005 decode(v_bucket_line_type_0,
2006 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2007 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2008 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2009 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2010 1),
2011 decode( greatest(v_bucket_days_from_0,
2012 ceil(p_as_of_date-ps.due_date)),
2013 least(v_bucket_days_to_0,
2014 ceil(p_as_of_date-ps.due_date)),1,
2015 0)
2016 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2017 decode(v_bucket_category,
2018 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2019 1))
2020 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2021 decode(v_bucket_category,
2022 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2023 1))) b0,
2024 decode(v_bucket_line_type_1,
2025 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2026 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2027 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2028 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2029 1),
2030 decode( greatest(v_bucket_days_from_1,
2031 ceil(p_as_of_date-ps.due_date)),
2032 least(v_bucket_days_to_1,
2033 ceil(p_as_of_date-ps.due_date)),1,
2034 0)
2035 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2036 decode(v_bucket_category,
2037 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2038 1))
2039 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2040 decode(v_bucket_category,
2041 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2042 1)))b1,
2043 decode(v_bucket_line_type_2,
2044 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2045 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2046 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2047 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2048 1),
2049 decode( greatest(v_bucket_days_from_2,
2050 ceil(p_as_of_date-ps.due_date)),
2051 least(v_bucket_days_to_2,
2052 ceil(p_as_of_date-ps.due_date)),1,
2053 0)
2054 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2055 decode(v_bucket_category,
2056 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2057 1))
2061 1))) b2,
2058 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2059 decode(v_bucket_category,
2060 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2062 decode(v_bucket_line_type_3,
2063 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2064 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2068 decode( greatest(v_bucket_days_from_3,
2065 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2066 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2067 1),
2069 ceil(p_as_of_date-ps.due_date)),
2070 least(v_bucket_days_to_3,
2071 ceil(p_as_of_date-ps.due_date)),1,
2072 0)
2073 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2074 decode(v_bucket_category,
2075 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2076 1))
2077 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2078 decode(v_bucket_category,
2079 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2080 1))) b3,
2081 decode(v_bucket_line_type_4,
2082 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2083 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2084 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2085 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2086 1),
2087 decode( greatest(v_bucket_days_from_4,
2088 ceil(p_as_of_date-ps.due_date)),
2089 least(v_bucket_days_to_4,
2090 ceil(p_as_of_date-ps.due_date)),1,
2091 0)
2092 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2093 decode(v_bucket_category,
2094 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2095 1))
2096 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2097 decode(v_bucket_category,
2098 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2099 1))) b4,
2100 decode(v_bucket_line_type_5,
2101 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2102 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2103 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2104 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2108 least(v_bucket_days_to_5,
2105 1),
2106 decode( greatest(v_bucket_days_from_5,
2107 ceil(p_as_of_date-ps.due_date)),
2109 ceil(p_as_of_date-ps.due_date)),1,
2110 0)
2111 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2112 decode(v_bucket_category,
2113 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2114 1))
2115 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2116 decode(v_bucket_category,
2117 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2118 1))) b5,
2119 decode(v_bucket_line_type_6,
2120 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2121 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2122 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2123 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2124 1),
2125 decode( greatest(v_bucket_days_from_6,
2126 ceil(p_as_of_date-ps.due_date)),
2127 least(v_bucket_days_to_6,
2128 ceil(p_as_of_date-ps.due_date)),1,
2129 0)
2130 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2131 decode(v_bucket_category,
2132 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2133 1))
2134 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2135 decode(v_bucket_category,
2136 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2137 1))) b6
2138 from ar_payment_schedules ps,
2139 ar_receivable_applications app
2140 where app.gl_date+0 > p_as_of_date
2141 and ps.cash_receipt_id = app.cash_receipt_id /*4436914*/
2142 and (ps.payment_schedule_id = app.applied_payment_schedule_id
2143 OR
2144 ps.payment_schedule_id = app.payment_schedule_id)
2145 and app.status in ( 'APP')
2146 and nvl(app.confirmed_flag, 'Y') = 'Y'
2147 and ps.gl_date_closed > p_as_of_date
2148 and (app.reversal_gl_date > p_as_of_date OR
2149 app.reversal_gl_date is null )
2150 and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
2151 and ps.customer_id = p_customer_id
2152 and decode(upper(p_currency_code),
2153 NULL, ps.invoice_currency_code,
2154 upper(p_currency_code)) = ps.invoice_currency_code
2155 and decode(upper(p_credit_option),
2156 'AGE', 'dummy','SUMMARY','dummy',
2157 'CM') <> ps.class
2158 and decode(upper(p_credit_option),
2159 'AGE', 'dummy','SUMMARY','dummy',
2160 'PMT') <> ps.class
2161 group by ps.due_date,
2162 ps.amount_due_original,
2163 ps.amount_adjusted,
2164 ps.amount_applied,
2165 ps.amount_credited,
2166 ps.gl_date,
2167 ps.amount_in_dispute,
2168 ps.amount_adjusted_pending,
2169 ps.invoice_currency_code,
2170 ps.exchange_rate,
2171 ps.class,
2172 ps.payment_schedule_id
2173 UNION ALL
2174 SELECT -sum(nvl(adj.amount,0)) amount_due_remaining,1,0,0,0,0,0,0
2175 FROM ar_adjustments adj,
2176 ar_payment_schedules_all ps
2177 WHERE adj.GL_date > p_as_of_date
2178 AND ps.payment_schedule_id = adj.payment_schedule_id
2179 AND adj.status = 'A'
2180 AND ps.gl_date_closed > p_as_of_date
2181 AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
2182 AND ps.customer_id = p_customer_id
2183 AND decode(upper(p_currency_code),NULL, ps.invoice_currency_code,upper(p_currency_code))= ps.invoice_currency_code
2184 AND decode(upper(p_credit_option),'AGE', 'dummy','SUMMARY','dummy','CM') <> ps.class
2185 AND decode(upper(p_credit_option),'AGE', 'dummy','SUMMARY','dummy','PMT') <> ps.class
2186 group by ps.due_date,
2187 ps.amount_due_original,
2188 ps.amount_adjusted,
2189 ps.amount_applied,
2190 ps.amount_credited,
2191 ps.gl_date,
2192 ps.amount_in_dispute,
2193 ps.amount_adjusted_pending,
2194 ps.invoice_currency_code,
2195 ps.exchange_rate,
2196 ps.class;
2197
2198 /* bug4047166 : the cursor c_buckets_5 will be used as if site use id is null
2199 and trx types are not null */
2200
2201 CURSOR c_buckets_5 IS
2202 select decode(p_currency_code, NULL, ps.acctd_amount_due_remaining,
2203 ps.amount_due_remaining) amount_due_remaining,
2204 decode(v_bucket_line_type_0,
2205 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2206 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2207 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2208 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2209 1),
2210 decode( greatest(v_bucket_days_from_0,
2211 ceil(p_as_of_date-ps.due_date)),
2212 least(v_bucket_days_to_0,
2213 ceil(p_as_of_date-ps.due_date)),1,
2214 0)
2215 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2216 decode(v_bucket_category,
2217 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2218 1))
2219 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2223 decode(v_bucket_line_type_1,
2220 decode(v_bucket_category,
2221 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2222 1))) b0,
2224 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2225 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2226 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2227 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2228 1),
2229 decode( greatest(v_bucket_days_from_1,
2230 ceil(p_as_of_date-ps.due_date)),
2231 least(v_bucket_days_to_1,
2232 ceil(p_as_of_date-ps.due_date)),1,
2233 0)
2234 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2235 decode(v_bucket_category,
2236 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2237 1))
2238 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2239 decode(v_bucket_category,
2240 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2241 1))) b1,
2242 decode(v_bucket_line_type_2,
2243 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2247 1),
2244 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2245 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2246 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2248 decode( greatest(v_bucket_days_from_2,
2249 ceil(p_as_of_date-ps.due_date)),
2250 least(v_bucket_days_to_2,
2251 ceil(p_as_of_date-ps.due_date)),1,
2252 0)
2253 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2254 decode(v_bucket_category,
2255 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2256 1))
2257 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2258 decode(v_bucket_category,
2259 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2260 1))) b2,
2261 decode(v_bucket_line_type_3,
2262 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2263 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2264 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2265 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2266 1),
2267 decode( greatest(v_bucket_days_from_3,
2268 ceil(p_as_of_date-ps.due_date)),
2269 least(v_bucket_days_to_3,
2270 ceil(p_as_of_date-ps.due_date)),1,
2271 0)
2272 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2273 decode(v_bucket_category,
2274 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2275 1))
2276 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2277 decode(v_bucket_category,
2278 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2279 1))) b3,
2280 decode(v_bucket_line_type_4,
2281 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2282 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2283 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2284 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2285 1),
2286 decode( greatest(v_bucket_days_from_4,
2287 ceil(p_as_of_date-ps.due_date)),
2288 least(v_bucket_days_to_4,
2289 ceil(p_as_of_date-ps.due_date)),1,
2290 0)
2291 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2292 decode(v_bucket_category,
2296 decode(v_bucket_category,
2293 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2294 1))
2295 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2297 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2298 1))) b4,
2299 decode(v_bucket_line_type_5,
2300 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2301 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2302 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2303 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2304 1),
2305 decode( greatest(v_bucket_days_from_5,
2306 ceil(p_as_of_date-ps.due_date)),
2307 least(v_bucket_days_to_5,
2308 ceil(p_as_of_date-ps.due_date)),1,
2309 0)
2310 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2311 decode(v_bucket_category,
2312 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2313 1))
2314 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2318 decode(v_bucket_line_type_6,
2315 decode(v_bucket_category,
2316 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2317 1))) b5,
2319 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2320 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2321 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2322 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2323 1),
2327 ceil(p_as_of_date-ps.due_date)),1,
2324 decode( greatest(v_bucket_days_from_6,
2325 ceil(p_as_of_date-ps.due_date)),
2326 least(v_bucket_days_to_6,
2328 0)
2329 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2330 decode(v_bucket_category,
2331 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2332 1))
2333 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2334 decode(v_bucket_category,
2335 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2336 1))) b6
2337 from ra_cust_trx_types,
2338 ar_payment_schedules ps
2339 where ps.gl_date <= p_as_of_date
2340 and ps.cust_trx_type_id = ra_cust_trx_types.cust_trx_type_id
2341 and ps.gl_date_closed > p_as_of_date
2342 and ps.customer_id = p_customer_id
2343 and decode(upper(p_currency_code),
2344 NULL, ps.invoice_currency_code,
2345 upper(p_currency_code)) = ps.invoice_currency_code
2346 and decode(upper(p_credit_option),
2347 'AGE', 'dummy', 'SUMMARY','dummy',
2348 'CM') <> ps.class
2349 and decode(upper(p_credit_option),
2350 'AGE', 'dummy', 'SUMMARY','dummy',
2351 'PMT') <> ps.class
2352 and decode(p_invoice_type_low,
2353 NULL, ra_cust_trx_types.name,
2354 p_invoice_type_low) <= ra_cust_trx_types.name
2355 and decode(p_invoice_type_high,
2356 NULL, ra_cust_trx_types.name,
2357 p_invoice_type_high) >= ra_cust_trx_types.name
2358 UNION ALL
2359 select nvl(-sum(decode(p_currency_code, NULL, app.amount_applied,
2360 app.acctd_amount_applied_from)),0) amount_due_remaining,
2361 decode(v_bucket_line_type_0,
2362 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2363 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2364 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2365 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2366 1),
2367 decode( greatest(v_bucket_days_from_0,
2368 ceil(p_as_of_date-ps.due_date)),
2369 least(v_bucket_days_to_0,
2370 ceil(p_as_of_date-ps.due_date)),1,
2371 0)
2372 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2373 decode(v_bucket_category,
2374 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2375 1))
2376 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2377 decode(v_bucket_category,
2378 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2379 1))) b0,
2380 decode(v_bucket_line_type_1,
2381 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2382 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2383 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2384 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2385 1),
2386 decode( greatest(v_bucket_days_from_1,
2387 ceil(p_as_of_date-ps.due_date)),
2388 least(v_bucket_days_to_1,
2389 ceil(p_as_of_date-ps.due_date)),1,
2390 0)
2391 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2392 decode(v_bucket_category,
2393 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2394 1))
2395 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2396 decode(v_bucket_category,
2397 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2398 1)))b1,
2399 decode(v_bucket_line_type_2,
2400 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2401 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2402 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2403 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2404 1),
2405 decode( greatest(v_bucket_days_from_2,
2406 ceil(p_as_of_date-ps.due_date)),
2407 least(v_bucket_days_to_2,
2411 decode(v_bucket_category,
2408 ceil(p_as_of_date-ps.due_date)),1,
2409 0)
2410 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2415 decode(v_bucket_category,
2412 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2413 1))
2414 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2416 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2417 1))) b2,
2418 decode(v_bucket_line_type_3,
2419 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2420 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2421 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2422 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2423 1),
2424 decode( greatest(v_bucket_days_from_3,
2425 ceil(p_as_of_date-ps.due_date)),
2426 least(v_bucket_days_to_3,
2427 ceil(p_as_of_date-ps.due_date)),1,
2428 0)
2429 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2430 decode(v_bucket_category,
2431 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2432 1))
2433 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2434 decode(v_bucket_category,
2435 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2436 1))) b3,
2437 decode(v_bucket_line_type_4,
2438 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2439 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2440 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2441 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2442 1),
2443 decode( greatest(v_bucket_days_from_4,
2444 ceil(p_as_of_date-ps.due_date)),
2445 least(v_bucket_days_to_4,
2446 ceil(p_as_of_date-ps.due_date)),1,
2447 0)
2448 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2449 decode(v_bucket_category,
2450 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2451 1))
2452 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2453 decode(v_bucket_category,
2454 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2455 1))) b4,
2456 decode(v_bucket_line_type_5,
2457 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2458 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2459 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2460 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2461 1),
2462 decode( greatest(v_bucket_days_from_5,
2463 ceil(p_as_of_date-ps.due_date)),
2464 least(v_bucket_days_to_5,
2465 ceil(p_as_of_date-ps.due_date)),1,
2466 0)
2467 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2468 decode(v_bucket_category,
2469 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2470 1))
2471 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2472 decode(v_bucket_category,
2473 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2474 1))) b5,
2475 decode(v_bucket_line_type_6,
2476 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2477 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2478 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2479 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2480 1),
2481 decode( greatest(v_bucket_days_from_6,
2482 ceil(p_as_of_date-ps.due_date)),
2483 least(v_bucket_days_to_6,
2484 ceil(p_as_of_date-ps.due_date)),1,
2485 0)
2486 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2487 decode(v_bucket_category,
2488 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2489 1))
2490 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2491 decode(v_bucket_category,
2492 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2493 1))) b6
2494 from ar_payment_schedules ps,
2495 ar_receivable_applications app
2496 where app.gl_date+0 <= p_as_of_date
2497 and ps.cash_receipt_id+0 = app.cash_receipt_id
2498 and app.status in ( 'ACC', 'UNAPP', 'UNID','OTHER ACC')
2499 and nvl(app.confirmed_flag, 'Y') = 'Y'
2500 and ps.gl_date_closed > p_as_of_date
2501 and (app.reversal_gl_date > p_as_of_date OR
2502 app.reversal_gl_date is null )
2503 and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
2504 and ps.customer_id = p_customer_id
2505 and decode(upper(p_currency_code),
2506 NULL, ps.invoice_currency_code,
2507 upper(p_currency_code)) = ps.invoice_currency_code
2511 'CM') <> ps.class
2508 and decode(p_credit_option,'AGE','AGE','dummy') = 'AGE' /*4436914*/
2509 and decode(upper(p_credit_option),
2510 'AGE', 'dummy', 'SUMMARY','dummy',
2512 and decode(upper(p_credit_option),
2513 'AGE', 'dummy', 'SUMMARY','dummy',
2514 'PMT') <> ps.class
2515 group by ps.due_date,
2516 ps.amount_due_original,
2517 ps.amount_adjusted,
2518 ps.amount_applied,
2519 ps.amount_credited,
2520 ps.gl_date,
2521 ps.amount_in_dispute,
2522 ps.amount_adjusted_pending,
2523 ps.invoice_currency_code,
2524 ps.exchange_rate,
2525 ps.class,
2526 decode( app.status, 'UNID', 'UNID', 'UNAPP')
2527 UNION ALL /*Bug 4436914 included APP*/
2528 select nvl(sum(decode(p_currency_code, NULL, app.amount_applied,
2529 app.acctd_amount_applied_from)),0) amount_due_remaining,
2530 decode(v_bucket_line_type_0,
2531 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2532 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2533 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2534 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2535 1),
2536 decode( greatest(v_bucket_days_from_0,
2537 ceil(p_as_of_date-ps.due_date)),
2538 least(v_bucket_days_to_0,
2539 ceil(p_as_of_date-ps.due_date)),1,
2540 0)
2541 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2542 decode(v_bucket_category,
2543 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2544 1))
2545 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2546 decode(v_bucket_category,
2547 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2548 1))) b0,
2549 decode(v_bucket_line_type_1,
2550 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2551 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2552 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2553 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2554 1),
2555 decode( greatest(v_bucket_days_from_1,
2556 ceil(p_as_of_date-ps.due_date)),
2557 least(v_bucket_days_to_1,
2558 ceil(p_as_of_date-ps.due_date)),1,
2559 0)
2560 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2561 decode(v_bucket_category,
2562 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2563 1))
2564 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2565 decode(v_bucket_category,
2566 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2567 1)))b1,
2568 decode(v_bucket_line_type_2,
2569 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2570 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2571 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2572 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2573 1),
2574 decode( greatest(v_bucket_days_from_2,
2575 ceil(p_as_of_date-ps.due_date)),
2576 least(v_bucket_days_to_2,
2577 ceil(p_as_of_date-ps.due_date)),1,
2578 0)
2579 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2580 decode(v_bucket_category,
2581 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2582 1))
2583 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2584 decode(v_bucket_category,
2585 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2586 1))) b2,
2587 decode(v_bucket_line_type_3,
2588 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2589 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2590 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2591 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2592 1),
2593 decode( greatest(v_bucket_days_from_3,
2594 ceil(p_as_of_date-ps.due_date)),
2595 least(v_bucket_days_to_3,
2596 ceil(p_as_of_date-ps.due_date)),1,
2597 0)
2598 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2599 decode(v_bucket_category,
2600 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2601 1))
2602 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2603 decode(v_bucket_category,
2604 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2605 1))) b3,
2606 decode(v_bucket_line_type_4,
2607 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2611 1),
2608 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2609 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2610 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2612 decode( greatest(v_bucket_days_from_4,
2613 ceil(p_as_of_date-ps.due_date)),
2614 least(v_bucket_days_to_4,
2615 ceil(p_as_of_date-ps.due_date)),1,
2616 0)
2617 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2618 decode(v_bucket_category,
2619 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2620 1))
2621 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2622 decode(v_bucket_category,
2623 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2624 1))) b4,
2625 decode(v_bucket_line_type_5,
2626 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2627 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2628 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2629 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2630 1),
2631 decode( greatest(v_bucket_days_from_5,
2632 ceil(p_as_of_date-ps.due_date)),
2633 least(v_bucket_days_to_5,
2634 ceil(p_as_of_date-ps.due_date)),1,
2635 0)
2636 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2637 decode(v_bucket_category,
2638 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2639 1))
2640 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2641 decode(v_bucket_category,
2642 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2643 1))) b5,
2644 decode(v_bucket_line_type_6,
2645 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
2646 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2647 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
2648 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
2649 1),
2650 decode( greatest(v_bucket_days_from_6,
2651 ceil(p_as_of_date-ps.due_date)),
2652 least(v_bucket_days_to_6,
2653 ceil(p_as_of_date-ps.due_date)),1,
2654 0)
2655 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
2656 decode(v_bucket_category,
2657 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2658 1))
2659 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
2660 decode(v_bucket_category,
2661 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2662 1))) b6
2663 from ar_payment_schedules ps,
2664 ar_receivable_applications app
2665 where app.gl_date+0 > p_as_of_date
2666 and ps.cash_receipt_id = app.cash_receipt_id /*4436914*/
2667 and (ps.payment_schedule_id = app.applied_payment_schedule_id
2668 OR
2669 ps.payment_schedule_id = app.payment_schedule_id)
2670 and app.status in ( 'APP')
2671 and nvl(app.confirmed_flag, 'Y') = 'Y'
2672 and ps.gl_date_closed > p_as_of_date
2673 and (app.reversal_gl_date > p_as_of_date OR
2674 app.reversal_gl_date is null )
2675 and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
2676 and ps.customer_id = p_customer_id
2677 and decode(upper(p_currency_code),
2678 NULL, ps.invoice_currency_code,
2679 upper(p_currency_code)) = ps.invoice_currency_code
2680 and decode(upper(p_credit_option),
2681 'AGE', 'dummy','SUMMARY','dummy',
2682 'CM') <> ps.class
2683 and decode(upper(p_credit_option),
2684 'AGE', 'dummy','SUMMARY','dummy',
2685 'PMT') <> ps.class
2686 group by ps.due_date,
2687 ps.amount_due_original,
2688 ps.amount_adjusted,
2689 ps.amount_applied,
2690 ps.amount_credited,
2691 ps.gl_date,
2692 ps.amount_in_dispute,
2693 ps.amount_adjusted_pending,
2694 ps.invoice_currency_code,
2695 ps.exchange_rate,
2696 ps.class,
2697 ps.payment_schedule_id
2698 UNION ALL
2699 SELECT -sum(nvl(adj.amount,0)) amount_due_remaining,1,0,0,0,0,0,0
2700 FROM ar_adjustments adj,
2701 ar_payment_schedules_all ps
2702 WHERE adj.GL_date > p_as_of_date
2703 AND ps.payment_schedule_id = adj.payment_schedule_id
2704 AND adj.status = 'A'
2705 AND ps.gl_date_closed > p_as_of_date
2706 AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
2707 AND ps.customer_id = p_customer_id
2708 AND decode(upper(p_currency_code),NULL, ps.invoice_currency_code,upper(p_currency_code))= ps.invoice_currency_code
2709 AND decode(upper(p_credit_option),'AGE', 'dummy','SUMMARY','dummy','CM') <> ps.class
2713 ps.amount_adjusted,
2710 AND decode(upper(p_credit_option),'AGE', 'dummy','SUMMARY','dummy','PMT') <> ps.class
2711 group by ps.due_date,
2712 ps.amount_due_original,
2714 ps.amount_applied,
2715 ps.amount_credited,
2716 ps.gl_date,
2717 ps.amount_in_dispute,
2718 ps.amount_adjusted_pending,
2719 ps.invoice_currency_code,
2720 ps.exchange_rate,
2721 ps.class;
2722 BEGIN
2723 --
2724 -- Get the aging buckets definition.
2725 --
2726 OPEN c_sel_bucket_data;
2727 FETCH c_sel_bucket_data INTO v_bucket_days_from_0, v_bucket_days_to_0,
2728 p_bucket_titletop_0, p_bucket_titlebottom_0,
2729 v_bucket_line_type_0;
2730 IF c_sel_bucket_data%FOUND THEN
2731 p_bucket_amount_0 := 0;
2732 IF (v_bucket_line_type_0 = 'DISPUTE_ONLY') OR
2733 (v_bucket_line_type_0 = 'PENDADJ_ONLY') OR
2734 (v_bucket_line_type_0 = 'DISPUTE_PENDADJ') THEN
2735 v_bucket_category := v_bucket_line_type_0;
2736 END IF;
2737 FETCH c_sel_bucket_data INTO v_bucket_days_from_1, v_bucket_days_to_1,
2738 p_bucket_titletop_1, p_bucket_titlebottom_1,
2739 v_bucket_line_type_1;
2740 ELSE
2741 p_bucket_titletop_0 := NULL;
2742 p_bucket_titlebottom_0 := NULL;
2743 p_bucket_amount_0 := NULL;
2744 END IF;
2745 IF c_sel_bucket_data%FOUND THEN
2746 p_bucket_amount_1 := 0;
2747 IF (v_bucket_line_type_1 = 'DISPUTE_ONLY') OR
2748 (v_bucket_line_type_1 = 'PENDADJ_ONLY') OR
2749 (v_bucket_line_type_1 = 'DISPUTE_PENDADJ') THEN
2750 v_bucket_category := v_bucket_line_type_1;
2751 END IF;
2752 FETCH c_sel_bucket_data INTO v_bucket_days_from_2, v_bucket_days_to_2,
2753 p_bucket_titletop_2, p_bucket_titlebottom_2,
2754 v_bucket_line_type_2;
2755 ELSE
2756 p_bucket_titletop_1 := NULL;
2757 p_bucket_titlebottom_1 := NULL;
2758 p_bucket_amount_1 := NULL;
2759 END IF;
2760 IF c_sel_bucket_data%FOUND THEN
2761 p_bucket_amount_2 := 0;
2762 IF (v_bucket_line_type_2 = 'DISPUTE_ONLY') OR
2763 (v_bucket_line_type_2 = 'PENDADJ_ONLY') OR
2764 (v_bucket_line_type_2 = 'DISPUTE_PENDADJ') THEN
2765 v_bucket_category := v_bucket_line_type_2;
2766 END IF;
2767 FETCH c_sel_bucket_data INTO v_bucket_days_from_3, v_bucket_days_to_3,
2768 p_bucket_titletop_3, p_bucket_titlebottom_3,
2769 v_bucket_line_type_3;
2770 ELSE
2771 p_bucket_titletop_2 := NULL;
2772 p_bucket_titlebottom_2 := NULL;
2773 p_bucket_amount_2 := NULL;
2774 END IF;
2775 IF c_sel_bucket_data%FOUND THEN
2776 p_bucket_amount_3 := 0;
2777 IF (v_bucket_line_type_3 = 'DISPUTE_ONLY') OR
2778 (v_bucket_line_type_3 = 'PENDADJ_ONLY') OR
2779 (v_bucket_line_type_3 = 'DISPUTE_PENDADJ') THEN
2780 v_bucket_category := v_bucket_line_type_3;
2781 END IF;
2782 FETCH c_sel_bucket_data INTO v_bucket_days_from_4, v_bucket_days_to_4,
2783 p_bucket_titletop_4, p_bucket_titlebottom_4,
2784 v_bucket_line_type_4;
2785 ELSE
2786 p_bucket_titletop_3 := NULL;
2787 p_bucket_titlebottom_3 := NULL;
2788 p_bucket_amount_3 := NULL;
2789 END IF;
2790 IF c_sel_bucket_data%FOUND THEN
2791 p_bucket_amount_4 := 0;
2792 IF (v_bucket_line_type_4 = 'DISPUTE_ONLY') OR
2793 (v_bucket_line_type_4 = 'PENDADJ_ONLY') OR
2794 (v_bucket_line_type_4 = 'DISPUTE_PENDADJ') THEN
2795 v_bucket_category := v_bucket_line_type_4;
2796 END IF;
2797 FETCH c_sel_bucket_data INTO v_bucket_days_from_5, v_bucket_days_to_5,
2798 p_bucket_titletop_5, p_bucket_titlebottom_5,
2799 v_bucket_line_type_5;
2800 ELSE
2801 p_bucket_titletop_4 := NULL;
2802 p_bucket_titlebottom_4 := NULL;
2803 p_bucket_amount_4 := NULL;
2804 END IF;
2805 IF c_sel_bucket_data%FOUND THEN
2806 p_bucket_amount_5 := 0;
2807 IF (v_bucket_line_type_5 = 'DISPUTE_ONLY') OR
2808 (v_bucket_line_type_5 = 'PENDADJ_ONLY') OR
2809 (v_bucket_line_type_5 = 'DISPUTE_PENDADJ') THEN
2810 v_bucket_category := v_bucket_line_type_5;
2811 END IF;
2812 FETCH c_sel_bucket_data INTO v_bucket_days_from_6, v_bucket_days_to_6,
2813 p_bucket_titletop_6, p_bucket_titlebottom_6,
2814 v_bucket_line_type_6;
2815 ELSE
2816 p_bucket_titletop_5 := NULL;
2817 p_bucket_titlebottom_5 := NULL;
2818 p_bucket_amount_5 := NULL;
2819 END IF;
2820 IF c_sel_bucket_data%FOUND THEN
2821 p_bucket_amount_6 := 0;
2822 IF (v_bucket_line_type_6 = 'DISPUTE_ONLY') OR
2823 (v_bucket_line_type_6 = 'PENDADJ_ONLY') OR
2824 (v_bucket_line_type_6 = 'DISPUTE_PENDADJ') THEN
2825 v_bucket_category := v_bucket_line_type_6;
2826 END IF;
2827 ELSE
2828 p_bucket_titletop_6 := NULL;
2829 p_bucket_titlebottom_6 := NULL;
2830 p_bucket_amount_6 := NULL;
2831 END IF;
2832 CLOSE c_sel_bucket_data;
2833 --
2834 -- get the aging bucket balance. The v_bucket_ is either 1 or 0.
2835 --
2836 p_outstanding_balance := 0;
2840 LOOP
2837 /* bug4047166: Added code to handle different cursosrs */
2838 IF p_customer_site_use_id IS NOT NULL AND (p_invoice_type_low IS NULL AND p_invoice_type_high IS NULL) THEN
2839 OPEN c_buckets_2;
2841 FETCH c_buckets_2 INTO v_amount_due_remaining,
2842 v_bucket_0, v_bucket_1, v_bucket_2,
2843 v_bucket_3, v_bucket_4, v_bucket_5, v_bucket_6;
2844 EXIT WHEN c_buckets_2%NOTFOUND;
2845 p_outstanding_balance := p_outstanding_balance + v_amount_due_remaining;
2846 IF p_bucket_amount_0 IS NOT NULL THEN
2847 p_bucket_amount_0 := p_bucket_amount_0 +
2848 (v_bucket_0 * v_amount_due_remaining);
2849 END IF;
2850 IF p_bucket_amount_1 IS NOT NULL THEN
2851 p_bucket_amount_1 := p_bucket_amount_1 +
2852 (v_bucket_1 * v_amount_due_remaining);
2853 END IF;
2854 IF p_bucket_amount_2 IS NOT NULL THEN
2855 p_bucket_amount_2 := p_bucket_amount_2 +
2856 (v_bucket_2 * v_amount_due_remaining);
2857 END IF;
2858 IF p_bucket_amount_3 IS NOT NULL THEN
2859 p_bucket_amount_3 := p_bucket_amount_3 +
2860 (v_bucket_3 * v_amount_due_remaining);
2861 END IF;
2862 IF p_bucket_amount_4 IS NOT NULL THEN
2863 p_bucket_amount_4 := p_bucket_amount_4 +
2864 (v_bucket_4 * v_amount_due_remaining);
2865 END IF;
2866 IF p_bucket_amount_5 IS NOT NULL THEN
2867 p_bucket_amount_5 := p_bucket_amount_5 +
2868 (v_bucket_5 * v_amount_due_remaining);
2869 END IF;
2870 IF p_bucket_amount_6 IS NOT NULL THEN
2871 p_bucket_amount_6 := p_bucket_amount_6 +
2872 (v_bucket_6 * v_amount_due_remaining);
2873 END IF;
2874 END LOOP;
2875 CLOSE c_buckets_2;
2876 ELSIF p_customer_site_use_id IS NOT NULL AND (p_invoice_type_low IS NOT NULL OR p_invoice_type_high IS NOT NULL) THEN
2877 OPEN c_buckets_3;
2878 LOOP
2879 FETCH c_buckets_3 INTO v_amount_due_remaining,
2880 v_bucket_0, v_bucket_1, v_bucket_2,
2881 v_bucket_3, v_bucket_4, v_bucket_5, v_bucket_6;
2882 EXIT WHEN c_buckets_3%NOTFOUND;
2883 p_outstanding_balance := p_outstanding_balance + v_amount_due_remaining;
2884 IF p_bucket_amount_0 IS NOT NULL THEN
2885 p_bucket_amount_0 := p_bucket_amount_0 +
2886 (v_bucket_0 * v_amount_due_remaining);
2887 END IF;
2888 IF p_bucket_amount_1 IS NOT NULL THEN
2889 p_bucket_amount_1 := p_bucket_amount_1 +
2890 (v_bucket_1 * v_amount_due_remaining);
2891 END IF;
2892 IF p_bucket_amount_2 IS NOT NULL THEN
2893 p_bucket_amount_2 := p_bucket_amount_2 +
2894 (v_bucket_2 * v_amount_due_remaining);
2895 END IF;
2896 IF p_bucket_amount_3 IS NOT NULL THEN
2897 p_bucket_amount_3 := p_bucket_amount_3 +
2898 (v_bucket_3 * v_amount_due_remaining);
2899 END IF;
2900 IF p_bucket_amount_4 IS NOT NULL THEN
2901 p_bucket_amount_4 := p_bucket_amount_4 +
2902 (v_bucket_4 * v_amount_due_remaining);
2903 END IF;
2904 IF p_bucket_amount_5 IS NOT NULL THEN
2905 p_bucket_amount_5 := p_bucket_amount_5 +
2906 (v_bucket_5 * v_amount_due_remaining);
2907 END IF;
2908 IF p_bucket_amount_6 IS NOT NULL THEN
2909 p_bucket_amount_6 := p_bucket_amount_6 +
2910 (v_bucket_6 * v_amount_due_remaining);
2911 END IF;
2912 END LOOP;
2913 CLOSE c_buckets_3;
2914 ELSIF p_customer_site_use_id IS NULL AND (p_invoice_type_low IS NULL AND p_invoice_type_high IS NULL) THEN
2915 OPEN c_buckets_4;
2916 LOOP
2917 FETCH c_buckets_4 INTO v_amount_due_remaining,
2918 v_bucket_0, v_bucket_1, v_bucket_2,
2919 v_bucket_3, v_bucket_4, v_bucket_5, v_bucket_6;
2920 EXIT WHEN c_buckets_4%NOTFOUND;
2921 p_outstanding_balance := p_outstanding_balance + v_amount_due_remaining;
2922 IF p_bucket_amount_0 IS NOT NULL THEN
2923 p_bucket_amount_0 := p_bucket_amount_0 +
2924 (v_bucket_0 * v_amount_due_remaining);
2925 END IF;
2926 IF p_bucket_amount_1 IS NOT NULL THEN
2927 p_bucket_amount_1 := p_bucket_amount_1 +
2928 (v_bucket_1 * v_amount_due_remaining);
2929 END IF;
2930 IF p_bucket_amount_2 IS NOT NULL THEN
2931 p_bucket_amount_2 := p_bucket_amount_2 +
2932 (v_bucket_2 * v_amount_due_remaining);
2933 END IF;
2934 IF p_bucket_amount_3 IS NOT NULL THEN
2935 p_bucket_amount_3 := p_bucket_amount_3 +
2936 (v_bucket_3 * v_amount_due_remaining);
2937 END IF;
2938 IF p_bucket_amount_4 IS NOT NULL THEN
2939 p_bucket_amount_4 := p_bucket_amount_4 +
2940 (v_bucket_4 * v_amount_due_remaining);
2941 END IF;
2942 IF p_bucket_amount_5 IS NOT NULL THEN
2943 p_bucket_amount_5 := p_bucket_amount_5 +
2944 (v_bucket_5 * v_amount_due_remaining);
2945 END IF;
2946 IF p_bucket_amount_6 IS NOT NULL THEN
2947 p_bucket_amount_6 := p_bucket_amount_6 +
2948 (v_bucket_6 * v_amount_due_remaining);
2949 END IF;
2950 END LOOP;
2951 CLOSE c_buckets_4;
2952 ELSIF p_customer_site_use_id IS NULL AND (p_invoice_type_low IS NOT NULL OR p_invoice_type_high IS NOT NULL) THEN
2953 OPEN c_buckets_5;
2954 LOOP
2955 FETCH c_buckets_5 INTO v_amount_due_remaining,
2956 v_bucket_0, v_bucket_1, v_bucket_2,
2960 IF p_bucket_amount_0 IS NOT NULL THEN
2957 v_bucket_3, v_bucket_4, v_bucket_5, v_bucket_6;
2958 EXIT WHEN c_buckets_5%NOTFOUND;
2959 p_outstanding_balance := p_outstanding_balance + nvl(v_amount_due_remaining,0);
2961 p_bucket_amount_0 := p_bucket_amount_0 +
2962 (v_bucket_0 * v_amount_due_remaining);
2963 END IF;
2964 IF p_bucket_amount_1 IS NOT NULL THEN
2965 p_bucket_amount_1 := p_bucket_amount_1 +
2966 (v_bucket_1 * v_amount_due_remaining);
2967 END IF;
2968 IF p_bucket_amount_2 IS NOT NULL THEN
2969 p_bucket_amount_2 := p_bucket_amount_2 +
2970 (v_bucket_2 * v_amount_due_remaining);
2971 END IF;
2972 IF p_bucket_amount_3 IS NOT NULL THEN
2973 p_bucket_amount_3 := p_bucket_amount_3 +
2974 (v_bucket_3 * v_amount_due_remaining);
2975 END IF;
2976 IF p_bucket_amount_4 IS NOT NULL THEN
2977 p_bucket_amount_4 := p_bucket_amount_4 +
2978 (v_bucket_4 * v_amount_due_remaining);
2979 END IF;
2980 IF p_bucket_amount_5 IS NOT NULL THEN
2981 p_bucket_amount_5 := p_bucket_amount_5 +
2982 (v_bucket_5 * v_amount_due_remaining);
2983 END IF;
2984 IF p_bucket_amount_6 IS NOT NULL THEN
2985 p_bucket_amount_6 := p_bucket_amount_6 +
2986 (v_bucket_6 * v_amount_due_remaining);
2987 END IF;
2988 END LOOP;
2989 CLOSE c_buckets_5;
2990 ELSE
2991 OPEN c_buckets_1;
2992 LOOP
2993 FETCH c_buckets_1 INTO v_amount_due_remaining,
2994 v_bucket_0, v_bucket_1, v_bucket_2,
2995 v_bucket_3, v_bucket_4, v_bucket_5, v_bucket_6;
2996 EXIT WHEN c_buckets_1%NOTFOUND;
2997 p_outstanding_balance := p_outstanding_balance + v_amount_due_remaining;
2998 IF p_bucket_amount_0 IS NOT NULL THEN
2999 p_bucket_amount_0 := p_bucket_amount_0 +
3000 (v_bucket_0 * v_amount_due_remaining);
3001 END IF;
3002 IF p_bucket_amount_1 IS NOT NULL THEN
3003 p_bucket_amount_1 := p_bucket_amount_1 +
3004 (v_bucket_1 * v_amount_due_remaining);
3005 END IF;
3006 IF p_bucket_amount_2 IS NOT NULL THEN
3007 p_bucket_amount_2 := p_bucket_amount_2 +
3008 (v_bucket_2 * v_amount_due_remaining);
3009 END IF;
3010 IF p_bucket_amount_3 IS NOT NULL THEN
3011 p_bucket_amount_3 := p_bucket_amount_3 +
3012 (v_bucket_3 * v_amount_due_remaining);
3013 END IF;
3014 IF p_bucket_amount_4 IS NOT NULL THEN
3015 p_bucket_amount_4 := p_bucket_amount_4 +
3016 (v_bucket_4 * v_amount_due_remaining);
3017 END IF;
3018 IF p_bucket_amount_5 IS NOT NULL THEN
3019 p_bucket_amount_5 := p_bucket_amount_5 +
3020 (v_bucket_5 * v_amount_due_remaining);
3021 END IF;
3022 IF p_bucket_amount_6 IS NOT NULL THEN
3023 p_bucket_amount_6 := p_bucket_amount_6 +
3024 (v_bucket_6 * v_amount_due_remaining);
3025 END IF;
3026 END LOOP;
3027 CLOSE c_buckets_1;
3028 END IF;
3029
3030 EXCEPTION
3031 WHEN OTHERS THEN
3032 IF PG_DEBUG in ('Y', 'C') THEN
3033 arp_standard.debug('EXCEPTION: arp_customer_aging.calc_aging_buckets');
3034 END IF;
3035 END calc_aging_buckets;
3036 --
3037 --
3038 --
3039 PROCEDURE calc_credits (
3040 p_customer_id IN NUMBER,
3041 p_customer_site_use_id IN NUMBER,
3042 p_as_of_date IN DATE,
3043 p_currency_code IN VARCHAR2,
3044 p_ps_max_id IN NUMBER DEFAULT 0,
3045 p_credits OUT NOCOPY NUMBER
3046 ) IS
3047 CURSOR c_credits IS
3048 SELECT NVL( SUM( DECODE(p_currency_code, NULL,
3049 ps.acctd_amount_due_remaining,
3050 ps.amount_due_remaining)), 0)
3051 FROM ar_payment_schedules ps,
3052 ra_cust_trx_line_gl_dist gld
3053 WHERE ps.customer_id = p_customer_id
3054 AND decode(p_customer_site_use_id,
3055 NULL, ps.customer_site_use_id,
3056 p_customer_site_use_id) = ps.customer_site_use_id
3057 AND decode(upper(p_currency_code),
3058 NULL, ps.invoice_currency_code,
3059 upper(p_currency_code)) = ps.invoice_currency_code
3060 AND ps.customer_trx_id = gld.customer_trx_id
3061 AND gld.account_class = 'REC'
3062 AND gld.latest_rec_flag = 'Y'
3063 AND ps.class||'' = 'CM'
3064 AND ps.gl_date <= p_as_of_date;
3065 BEGIN
3066 p_credits := 0;
3067 OPEN c_credits;
3068 FETCH c_credits INTO p_credits;
3069 CLOSE c_credits;
3070 --
3071 EXCEPTION
3072 WHEN OTHERS THEN
3073 IF PG_DEBUG in ('Y', 'C') THEN
3074 arp_standard.debug('EXCEPTION: arp_customer_aging.calc_credits');
3075 END IF;
3076 END calc_credits;
3077 --
3078 --
3079 --
3080 PROCEDURE calc_receipts (
3081 p_customer_id IN NUMBER,
3082 p_customer_site_use_id IN NUMBER,
3083 p_as_of_date IN DATE,
3084 p_currency_code IN VARCHAR2,
3085 p_app_max_id IN NUMBER DEFAULT 0,
3086 p_unapplied_cash OUT NOCOPY NUMBER,
3090 ) IS
3087 p_onacct_cash OUT NOCOPY NUMBER,
3088 p_cash_claims OUT NOCOPY NUMBER,
3089 p_prepayments OUT NOCOPY NUMBER
3091 /* bug4047166: The cursor c_unapplied_cash is now 4 different cursors.
3092 Any one of them will get executed depening upon the site and currency
3093 value provided. This is done to improve performance */
3094
3095 CURSOR c_unapplied_cash_1 IS
3096 SELECT NVL(SUM( DECODE(app.status,'UNAPP',
3097 DECODE(p_currency_code, NULL,
3098 -app.acctd_amount_applied_from,
3099 -app.amount_applied), 0) ), 0),
3100 NVL(SUM( DECODE(app.status,'ACC',
3101 DECODE(p_currency_code, NULL,
3102 -app.acctd_amount_applied_from,
3103 -app.amount_applied), 0) ), 0),
3104 NVL(SUM( DECODE(app.status,'OTHER ACC',
3105 DECODE(app.applied_payment_schedule_id, -4,
3106 DECODE(p_currency_code, NULL,
3107 -app.acctd_amount_applied_from,
3108 -app.amount_applied),0), 0) ), 0),
3109 NVL(SUM( DECODE(app.status,'OTHER ACC',
3110 DECODE(app.applied_payment_schedule_id, -7,
3111 DECODE(p_currency_code, NULL,
3112 -app.acctd_amount_applied_from,
3113 -app.amount_applied),0), 0) ), 0)
3114 FROM ar_receivable_applications app,
3115 ar_payment_schedules ps
3116 WHERE ps.customer_id = p_customer_id
3117 AND ps.cash_receipt_id = app.cash_receipt_id
3118 AND nvl( app.confirmed_flag, 'Y' ) = 'Y'
3119 AND app.status in ( 'UNAPP', 'ACC' ,'OTHER ACC')
3120 AND app.gl_date <= p_as_of_date;
3121
3122 CURSOR c_unapplied_cash_2 IS
3123 SELECT NVL(SUM( DECODE(app.status,'UNAPP',
3124 DECODE(p_currency_code, NULL,
3125 -app.acctd_amount_applied_from,
3126 -app.amount_applied), 0) ), 0),
3127 NVL(SUM( DECODE(app.status,'ACC',
3128 DECODE(p_currency_code, NULL,
3129 -app.acctd_amount_applied_from,
3130 -app.amount_applied), 0) ), 0),
3131 NVL(SUM( DECODE(app.status,'OTHER ACC',
3132 DECODE(app.applied_payment_schedule_id, -4,
3133 DECODE(p_currency_code, NULL,
3134 -app.acctd_amount_applied_from,
3135 -app.amount_applied),0), 0) ), 0),
3136 NVL(SUM( DECODE(app.status,'OTHER ACC',
3137 DECODE(app.applied_payment_schedule_id, -7,
3138 DECODE(p_currency_code, NULL,
3139 -app.acctd_amount_applied_from,
3140 -app.amount_applied),0), 0) ), 0)
3141 FROM ar_receivable_applications app,
3142 ar_payment_schedules ps
3143 WHERE ps.customer_id = p_customer_id
3144 AND ps.customer_site_use_id = p_customer_site_use_id
3145 AND ps.cash_receipt_id = app.cash_receipt_id
3146 AND nvl( app.confirmed_flag, 'Y' ) = 'Y'
3147 AND app.status in ( 'UNAPP', 'ACC' ,'OTHER ACC')
3148 AND app.gl_date <= p_as_of_date;
3149
3150 CURSOR c_unapplied_cash_3 IS
3151 SELECT NVL(SUM( DECODE(app.status,'UNAPP',
3152 DECODE(p_currency_code, NULL,
3153 -app.acctd_amount_applied_from,
3154 -app.amount_applied), 0) ), 0),
3155 NVL(SUM( DECODE(app.status,'ACC',
3156 DECODE(p_currency_code, NULL,
3157 -app.acctd_amount_applied_from,
3158 -app.amount_applied), 0) ), 0),
3159 NVL(SUM( DECODE(app.status,'OTHER ACC',
3160 DECODE(app.applied_payment_schedule_id, -4,
3161 DECODE(p_currency_code, NULL,
3162 -app.acctd_amount_applied_from,
3163 -app.amount_applied),0), 0) ), 0),
3164 NVL(SUM( DECODE(app.status,'OTHER ACC',
3165 DECODE(app.applied_payment_schedule_id, -7,
3166 DECODE(p_currency_code, NULL,
3167 -app.acctd_amount_applied_from,
3168 -app.amount_applied),0), 0) ), 0)
3169 FROM ar_receivable_applications app,
3170 ar_payment_schedules ps
3171 WHERE ps.customer_id = p_customer_id
3172 AND ps.cash_receipt_id = app.cash_receipt_id
3173 AND nvl( app.confirmed_flag, 'Y' ) = 'Y'
3174 AND app.status in ( 'UNAPP', 'ACC' ,'OTHER ACC')
3175 AND ps.invoice_currency_code = p_currency_code
3176 AND app.gl_date <= p_as_of_date;
3177
3178 CURSOR c_unapplied_cash_4 IS
3179 SELECT NVL(SUM( DECODE(app.status,'UNAPP',
3180 DECODE(p_currency_code, NULL,
3181 -app.acctd_amount_applied_from,
3182 -app.amount_applied), 0) ), 0),
3183 NVL(SUM( DECODE(app.status,'ACC',
3184 DECODE(p_currency_code, NULL,
3188 DECODE(app.applied_payment_schedule_id, -4,
3185 -app.acctd_amount_applied_from,
3186 -app.amount_applied), 0) ), 0),
3187 NVL(SUM( DECODE(app.status,'OTHER ACC',
3189 DECODE(p_currency_code, NULL,
3190 -app.acctd_amount_applied_from,
3191 -app.amount_applied),0), 0) ), 0),
3192 NVL(SUM( DECODE(app.status,'OTHER ACC',
3193 DECODE(app.applied_payment_schedule_id, -7,
3194 DECODE(p_currency_code, NULL,
3195 -app.acctd_amount_applied_from,
3196 -app.amount_applied),0), 0) ), 0)
3197 FROM ar_receivable_applications app,
3198 ar_payment_schedules ps
3199 WHERE ps.customer_id = p_customer_id
3200 AND ps.customer_site_use_id = p_customer_site_use_id
3201 AND ps.cash_receipt_id = app.cash_receipt_id
3202 AND nvl( app.confirmed_flag, 'Y' ) = 'Y'
3203 AND app.status in ( 'UNAPP', 'ACC' ,'OTHER ACC')
3204 AND ps.invoice_currency_code = p_currency_code
3205 AND app.gl_date <= p_as_of_date;
3206 BEGIN
3207 p_unapplied_cash := 0;
3208 p_onacct_cash := 0;
3209 /* bug4047166: Added following code to handle different cursosrs */
3210
3211 IF p_customer_site_use_id IS NOT NULL AND p_currency_code IS NOT NULL THEN
3212 OPEN c_unapplied_cash_4;
3213 FETCH c_unapplied_cash_4 INTO p_unapplied_cash, p_onacct_cash,p_cash_claims,p_prepayments;
3214 CLOSE c_unapplied_cash_4;
3215 ELSIF p_customer_site_use_id IS NOT NULL AND p_currency_code IS NULL THEN
3216 OPEN c_unapplied_cash_2;
3217 FETCH c_unapplied_cash_2 INTO p_unapplied_cash, p_onacct_cash,p_cash_claims,p_prepayments;
3218 CLOSE c_unapplied_cash_2;
3219 ELSIF p_customer_site_use_id IS NULL AND p_currency_code IS NOT NULL THEN
3220 OPEN c_unapplied_cash_3;
3221 FETCH c_unapplied_cash_3 INTO p_unapplied_cash, p_onacct_cash,p_cash_claims,p_prepayments;
3222 CLOSE c_unapplied_cash_3;
3223 ELSE
3224 OPEN c_unapplied_cash_1;
3225 FETCH c_unapplied_cash_1 INTO p_unapplied_cash, p_onacct_cash,p_cash_claims,p_prepayments;
3226 CLOSE c_unapplied_cash_1;
3227 END IF;
3228 EXCEPTION
3229 WHEN OTHERS THEN
3230 IF PG_DEBUG in ('Y', 'C') THEN
3231 arp_standard.debug('EXCEPTION: arp_customer_aging.calc_receipts');
3232 END IF;
3233 END calc_receipts;
3234 --
3235 --
3236 --
3237 PROCEDURE calc_risk_receipts (
3238 p_customer_id IN NUMBER,
3239 p_customer_site_use_id IN NUMBER,
3240 p_as_of_date IN DATE,
3241 p_currency_code IN VARCHAR2,
3242 p_ps_max_id IN NUMBER DEFAULT 0,
3243 p_risk_receipts OUT NOCOPY NUMBER
3244 ) IS
3245 CURSOR c_risk IS
3246 -- bug 1865105 starts
3247 SELECT NVL( SUM( DECODE(p_currency_code, NULL, crh.acctd_amount,
3248 crh.amount)), 0)
3249 FROM ar_cash_receipts cr,
3250 ar_cash_receipt_history crh
3251 WHERE cr.pay_from_customer = p_customer_id
3252 /* bug no : 1274152. Aging form did not consider the receipts done without custo
3253 mer location for the calculation of receipt at risk. NVL is added for ps.custome
3254 r_site_use_id in the procedure calc_risk_receipts to avoid null = null compariso
3255 n
3256 fixed by rajsrini */
3257 AND decode(p_customer_site_use_id,
3258 NULL, nvl(cr.customer_site_use_id,0),
3259 p_customer_site_use_id) = nvl(cr.customer_site_use_id,0)
3260 AND cr.currency_code= nvl(p_currency_code,cr.currency_code)
3261 AND cr.reversal_date is null
3262 AND nvl(cr.confirmed_flag,'Y') = 'Y'
3263 AND cr.cash_receipt_id = crh.cash_receipt_id
3264 AND crh.current_record_flag||'' = 'Y'
3265 AND crh.gl_date <= p_as_of_date
3266 AND crh.status NOT IN ( DECODE ( crh.factor_flag,'Y',
3267 'RISK_ELIMINATED','N', 'CLEARED'), 'REVERSED' )
3268 /* 06-AUG-2000 J Rautiainen BR Implementation
3269 * Short term debt applications are not considered as receipts at risk */
3270 and not exists (select 'X'
3271 from ar_receivable_applications rap
3272 where rap.cash_receipt_id = cr.cash_receipt_id
3273 and rap.applied_payment_schedule_id = -2
3274 and rap.display = 'Y');
3275 -- bug 1865105 ends
3276 BEGIN
3277 OPEN c_risk;
3278 FETCH c_risk INTO p_risk_receipts;
3279 CLOSE c_risk;
3280 --
3281 EXCEPTION
3282 WHEN OTHERS THEN
3283 IF PG_DEBUG in ('Y', 'C') THEN
3284 arp_standard.debug('EXCEPTION: arp_customer_aging.calc_risk_receipts');
3285 END IF;
3286 END calc_risk_receipts;
3287 --
3288 --
3289 --
3290 PROCEDURE calc_dispute (
3291 p_customer_id IN NUMBER,
3292 p_customer_site_use_id IN NUMBER,
3293 p_as_of_date IN DATE,
3294 p_currency_code IN VARCHAR2,
3295 p_ps_max_id IN NUMBER DEFAULT 0,
3296 p_dispute OUT NOCOPY NUMBER
3297 ) IS
3298 CURSOR c_dispute IS
3299 SELECT NVL(SUM(decode(p_currency_code,NULL,ps.amount_in_dispute * nvl(ps.exchange_rate,1), ps.amount_in_dispute)),0)
3300 FROM ar_payment_schedules ps
3301 WHERE ps.customer_id = p_customer_id
3302 AND decode(p_customer_site_use_id,
3303 NULL, ps.customer_site_use_id,
3304 p_customer_site_use_id) = ps.customer_site_use_id
3305 AND decode(upper(p_currency_code),
3306 NULL, ps.invoice_currency_code,
3307 upper(p_currency_code)) = ps.invoice_currency_code
3308 --Bug-1304510:Changed ps.due_date to ps.dispute_date.
3309 AND ps.dispute_date <= p_as_of_date
3310 AND nvl( ps.amount_in_dispute, 0 ) <> 0
3311 AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y';
3312 BEGIN
3313 OPEN c_dispute;
3314 FETCH c_dispute INTO p_dispute;
3315 CLOSE c_dispute;
3316 --
3317 EXCEPTION
3318 WHEN OTHERS THEN
3319 IF PG_DEBUG in ('Y', 'C') THEN
3320 arp_standard.debug('EXCEPTION: arp_customer_aging.calc_dispute');
3321 END IF;
3322 END calc_dispute;
3323 --
3324 --
3325 --
3326 PROCEDURE calc_pending_adj (
3327 p_customer_id IN NUMBER,
3328 p_customer_site_use_id IN NUMBER,
3329 p_as_of_date IN DATE,
3330 p_currency_code IN VARCHAR2,
3331 p_ps_max_id IN NUMBER DEFAULT 0,
3332 p_pending_adj OUT NOCOPY NUMBER
3333 ) IS
3334 CURSOR c_pending_adj IS
3335 SELECT NVL( SUM( ps.amount_adjusted_pending ), 0)
3336 FROM ar_payment_schedules ps
3337 WHERE ps.customer_id = p_customer_id
3338 AND decode(p_customer_site_use_id,
3339 NULL, ps.customer_site_use_id,
3340 p_customer_site_use_id) = ps.customer_site_use_id
3341 AND decode(upper(p_currency_code),
3342 NULL, ps.invoice_currency_code,
3343 upper(p_currency_code)) = ps.invoice_currency_code
3344 AND ps.due_date <= p_as_of_date
3345 AND nvl( ps.amount_adjusted_pending, 0 ) <> 0
3346 AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y';
3347 BEGIN
3348 OPEN c_pending_adj;
3349 FETCH c_pending_adj INTO p_pending_adj;
3350 CLOSE c_pending_adj;
3351 --
3352 EXCEPTION
3353 WHEN OTHERS THEN
3354 IF PG_DEBUG in ('Y', 'C') THEN
3355 arp_standard.debug('EXCEPTION: arp_customer_aging.calc_pending_adj');
3356 END IF;
3357 END calc_pending_adj;
3358 --
3359 END arp_customer_aging;