[Home] [Help]
PACKAGE BODY: APPS.IEX_AGING_BUCKETS_PKG
Source
1 PACKAGE BODY iex_aging_buckets_pkg AS
2 /* $Header: iexpagbb.pls 120.19.12020000.2 2012/10/09 20:53:41 ehuh ship $ */
3 --
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IEX_AGING_BUCKETS_PKG';
6 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexpagbb.pls';
7 l_line varchar2(100);
8 l_date date ;
9
10 -- Decimal Rounding in case of currency calculations.
11 l_round_decimal Number := 8 ;
12
13
14 -- Cursor to select all cust_account_ids in case user passes party_id
15 CURSOR AR_AGING_PARTY_CUR(p_party Number)
16 IS
17 SELECT cust_account_id
18 FROM HZ_CUST_ACCOUNTS
19 WHERE party_id = p_party ;
20
21 -- Cursor to select all cust_account_ids in case user passes party_id for paying relationship
22 CURSOR AR_AGING_PAYING_PARTY_CUR(p_party Number)
23 IS
24 SELECT cust_account_id
25 FROM HZ_CUST_ACCOUNTS
26 WHERE party_id IN
27 (SELECT p_party FROM dual
28 UNION
29 SELECT ar.related_party_id
30 FROM ar_paying_relationships_v ar
31 WHERE ar.party_id = p_party
32 AND TRUNC(sysdate) BETWEEN
33 TRUNC(NVL(ar.effective_start_date,sysdate)) AND
34 TRUNC(NVL(ar.effective_end_date,sysdate)));
35
36
37 PG_DEBUG NUMBER;
38
39 PROCEDURE calc_aging_buckets (
40 p_customer_id IN NUMBER,
41 p_customer_site_use_id IN NUMBER,
42 p_as_of_date IN DATE,
43 p_currency_code IN VARCHAR2,
44 p_credit_option IN VARCHAR2,
45 p_invoice_type_low IN VARCHAR2,
46 p_invoice_type_high IN VARCHAR2,
47 p_ps_max_id IN NUMBER ,
48 p_app_max_id IN NUMBER ,
49 p_bucket_id IN Number,
50 p_outstanding_balance IN OUT NOCOPY NUMBER,
51 p_bucket_line_id_0 OUT NOCOPY NUMBER,
52 p_bucket_seq_num_0 OUT NOCOPY NUMBER,
53 p_bucket_titletop_0 OUT NOCOPY VARCHAR2,
54 p_bucket_titlebottom_0 OUT NOCOPY VARCHAR2,
55 p_bucket_amount_0 IN OUT NOCOPY NUMBER,
56 p_bucket_line_id_1 OUT NOCOPY NUMBER,
57 p_bucket_seq_num_1 OUT NOCOPY NUMBER,
58 p_bucket_titletop_1 OUT NOCOPY VARCHAR2,
59 p_bucket_titlebottom_1 OUT NOCOPY VARCHAR2,
60 p_bucket_amount_1 IN OUT NOCOPY NUMBER,
61 p_bucket_line_id_2 OUT NOCOPY NUMBER,
62 p_bucket_seq_num_2 OUT NOCOPY NUMBER,
63 p_bucket_titletop_2 OUT NOCOPY VARCHAR2,
64 p_bucket_titlebottom_2 OUT NOCOPY VARCHAR2,
65 p_bucket_amount_2 IN OUT NOCOPY NUMBER,
66 p_bucket_line_id_3 OUT NOCOPY NUMBER,
67 p_bucket_seq_num_3 OUT NOCOPY NUMBER,
68 p_bucket_titletop_3 OUT NOCOPY VARCHAR2,
69 p_bucket_titlebottom_3 OUT NOCOPY VARCHAR2,
70 p_bucket_amount_3 IN OUT NOCOPY NUMBER,
71 p_bucket_line_id_4 OUT NOCOPY NUMBER,
72 p_bucket_seq_num_4 OUT NOCOPY NUMBER,
73 p_bucket_titletop_4 OUT NOCOPY VARCHAR2,
74 p_bucket_titlebottom_4 OUT NOCOPY VARCHAR2,
75 p_bucket_amount_4 IN OUT NOCOPY NUMBER,
76 p_bucket_line_id_5 OUT NOCOPY NUMBER,
77 p_bucket_seq_num_5 OUT NOCOPY NUMBER,
78 p_bucket_titletop_5 OUT NOCOPY VARCHAR2,
79 p_bucket_titlebottom_5 OUT NOCOPY VARCHAR2,
80 p_bucket_amount_5 IN OUT NOCOPY NUMBER,
81 p_bucket_line_id_6 OUT NOCOPY NUMBER,
82 p_bucket_seq_num_6 OUT NOCOPY NUMBER,
83 p_bucket_titletop_6 OUT NOCOPY VARCHAR2,
84 p_bucket_titlebottom_6 OUT NOCOPY VARCHAR2,
85 p_bucket_amount_6 IN OUT NOCOPY NUMBER
86 ) IS
87 v_amount_due_remaining NUMBER;
88 v_bucket_0 NUMBER;
89 v_bucket_1 NUMBER;
90 v_bucket_2 NUMBER;
91 v_bucket_3 NUMBER;
92 v_bucket_4 NUMBER;
93 v_bucket_5 NUMBER;
94 v_bucket_6 NUMBER;
95 v_bucket_category ar_aging_bucket_lines.type%TYPE;
96 --
97 v_bucket_line_type_0 ar_aging_bucket_lines.type%TYPE;
98 v_bucket_days_from_0 NUMBER;
99 v_bucket_days_to_0 NUMBER;
100 v_bucket_line_id_0 NUMBER;
101 v_bucket_seq_num_0 NUMBER ;
102
103 v_bucket_line_type_1 ar_aging_bucket_lines.type%TYPE;
104 v_bucket_days_from_1 NUMBER;
105 v_bucket_days_to_1 NUMBER;
106 v_bucket_line_id_1 NUMBER;
107 v_bucket_seq_num_1 NUMBER ;
108
109 v_bucket_line_type_2 ar_aging_bucket_lines.type%TYPE;
110 v_bucket_days_from_2 NUMBER;
111 v_bucket_days_to_2 NUMBER;
112 v_bucket_line_id_2 NUMBER;
113 v_bucket_seq_num_2 NUMBER ;
114
115 v_bucket_line_type_3 ar_aging_bucket_lines.type%TYPE;
116 v_bucket_days_from_3 NUMBER;
117 v_bucket_days_to_3 NUMBER;
118 v_bucket_line_id_3 NUMBER;
119 v_bucket_seq_num_3 NUMBER ;
120
121 v_bucket_line_type_4 ar_aging_bucket_lines.type%TYPE;
122 v_bucket_days_from_4 NUMBER;
123 v_bucket_days_to_4 NUMBER;
124 v_bucket_line_id_4 NUMBER;
125 v_bucket_seq_num_4 NUMBER ;
126
127 v_bucket_line_type_5 ar_aging_bucket_lines.type%TYPE;
128 v_bucket_days_from_5 NUMBER;
129 v_bucket_days_to_5 NUMBER;
130 v_bucket_line_id_5 NUMBER;
131 v_bucket_seq_num_5 NUMBER ;
132
133 v_bucket_line_type_6 ar_aging_bucket_lines.type%TYPE;
134 v_bucket_days_from_6 NUMBER;
135 v_bucket_days_to_6 NUMBER;
136 v_bucket_line_id_6 NUMBER;
137 v_bucket_seq_num_6 NUMBER ;
138
139 --
140 CURSOR c_sel_bucket_data is
141 select lines.days_start,
142 lines.days_to,
143 lines.report_heading1,
144 lines.report_heading2,
145 lines.type,
146 lines.aging_bucket_line_id,
147 lines.bucket_sequence_num
148 from ar_aging_bucket_lines lines,
149 ar_aging_buckets buckets
150 where lines.aging_bucket_id = buckets.aging_bucket_id
151 and buckets.aging_bucket_id = p_bucket_id
152 and buckets.status = 'A'
153 order by lines.bucket_sequence_num ;
154 --
155 CURSOR c_buckets IS
156 select decode(p_currency_code, NULL, ps.acctd_amount_due_remaining,
157 ps.amount_due_remaining),
158 decode(v_bucket_line_type_0,
159 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
160 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
161 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
162 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
163 1),
164 decode( greatest(v_bucket_days_from_0,
165 ceil(p_as_of_date-ps.due_date)),
166 least(v_bucket_days_to_0,
167 ceil(p_as_of_date-ps.due_date)),1,
168 0)
169 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
170 decode(v_bucket_category,
171 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
172 1))
173 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
174 decode(v_bucket_category,
175 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
176 1))) b0,
177 decode(v_bucket_line_type_1,
178 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
179 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
180 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
181 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
182 1),
183 decode( greatest(v_bucket_days_from_1,
184 ceil(p_as_of_date-ps.due_date)),
185 least(v_bucket_days_to_1,
186 ceil(p_as_of_date-ps.due_date)),1,
187 0)
188 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
189 decode(v_bucket_category,
190 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
191 1))
192 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
193 decode(v_bucket_category,
194 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
195 1))) b1,
196 decode(v_bucket_line_type_2,
197 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
198 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
199 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
200 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
201 1),
202 decode( greatest(v_bucket_days_from_2,
203 ceil(p_as_of_date-ps.due_date)),
204 least(v_bucket_days_to_2,
205 ceil(p_as_of_date-ps.due_date)),1,
206 0)
207 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
208 decode(v_bucket_category,
209 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
210 1))
211 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
212 decode(v_bucket_category,
213 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
214 1))) b2,
215 decode(v_bucket_line_type_3,
216 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
217 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
218 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
219 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
220 1),
221 decode( greatest(v_bucket_days_from_3,
222 ceil(p_as_of_date-ps.due_date)),
223 least(v_bucket_days_to_3,
224 ceil(p_as_of_date-ps.due_date)),1,
225 0)
226 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
227 decode(v_bucket_category,
228 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
229 1))
230 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
231 decode(v_bucket_category,
232 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
233 1))) b3,
234 decode(v_bucket_line_type_4,
235 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
236 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
237 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
238 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
239 1),
240 decode( greatest(v_bucket_days_from_4,
241 ceil(p_as_of_date-ps.due_date)),
242 least(v_bucket_days_to_4,
243 ceil(p_as_of_date-ps.due_date)),1,
244 0)
245 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
246 decode(v_bucket_category,
247 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
248 1))
249 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
250 decode(v_bucket_category,
251 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
252 1))) b4,
253 decode(v_bucket_line_type_5,
254 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
255 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
256 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
257 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
258 1),
259 decode( greatest(v_bucket_days_from_5,
260 ceil(p_as_of_date-ps.due_date)),
261 least(v_bucket_days_to_5,
262 ceil(p_as_of_date-ps.due_date)),1,
263 0)
264 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
265 decode(v_bucket_category,
266 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
267 1))
268 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
269 decode(v_bucket_category,
270 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
271 1))) b5,
272 decode(v_bucket_line_type_6,
273 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
274 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
275 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
276 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
277 1),
278 decode( greatest(v_bucket_days_from_6,
279 ceil(p_as_of_date-ps.due_date)),
280 least(v_bucket_days_to_6,
281 ceil(p_as_of_date-ps.due_date)),1,
282 0)
283 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
284 decode(v_bucket_category,
285 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
286 1))
287 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
288 decode(v_bucket_category,
289 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
290 1))) b6
291 from ra_cust_trx_types,
292 ar_payment_schedules ps
293 where ps.gl_date <= p_as_of_date
294 and ps.cust_trx_type_id = ra_cust_trx_types.cust_trx_type_id
295 and ps.gl_date_closed > p_as_of_date
296 and ps.customer_id = p_customer_id
297 and ra_cust_trx_types.org_id= ps.org_id --Added for Bug 9902108 03-Aug-2010 barathsr
298 and decode(p_customer_site_use_id,
299 NULL, ps.customer_site_use_id,
300 p_customer_site_use_id) = ps.customer_site_use_id
301 and decode(upper(p_currency_code),
302 NULL, ps.invoice_currency_code,
303 upper(p_currency_code)) = ps.invoice_currency_code
304 and decode(upper(p_credit_option),
305 'AGE', 'dummy',
306 'CM') <> ps.class
307 and decode(upper(p_credit_option),
308 'AGE', 'dummy',
309 'PMT') <> ps.class
310 and decode(p_invoice_type_low,
311 NULL, ra_cust_trx_types.name,
312 p_invoice_type_low) <= ra_cust_trx_types.name
313 and decode(p_invoice_type_high,
314 NULL, ra_cust_trx_types.name,
315 p_invoice_type_high) >= ra_cust_trx_types.name
316 AND PS.STATUS = 'OP' -- bug 14085877
317 UNION ALL
318 select -sum(decode(p_currency_code, NULL, app.acctd_amount_applied_from,
319 app.amount_applied)),
320 decode(v_bucket_line_type_0,
321 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
322 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
323 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
324 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
325 1),
326 decode( greatest(v_bucket_days_from_0,
327 ceil(p_as_of_date-ps.due_date)),
328 least(v_bucket_days_to_0,
329 ceil(p_as_of_date-ps.due_date)),1,
330 0)
331 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
332 decode(v_bucket_category,
333 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
334 1))
335 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
336 decode(v_bucket_category,
337 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
338 1))) b0,
339 decode(v_bucket_line_type_1,
340 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
341 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
342 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
343 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
344 1),
345 decode( greatest(v_bucket_days_from_1,
346 ceil(p_as_of_date-ps.due_date)),
347 least(v_bucket_days_to_1,
348 ceil(p_as_of_date-ps.due_date)),1,
349 0)
350 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
351 decode(v_bucket_category,
352 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
353 1))
354 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
355 decode(v_bucket_category,
356 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
357 1)))b1,
358 decode(v_bucket_line_type_2,
359 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
360 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
361 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
362 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
363 1),
364 decode( greatest(v_bucket_days_from_2,
365 ceil(p_as_of_date-ps.due_date)),
366 least(v_bucket_days_to_2,
367 ceil(p_as_of_date-ps.due_date)),1,
368 0)
369 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
370 decode(v_bucket_category,
371 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
372 1))
373 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
374 decode(v_bucket_category,
375 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
376 1))) b2,
377 decode(v_bucket_line_type_3,
378 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
379 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
380 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
381 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
382 1),
383 decode( greatest(v_bucket_days_from_3,
384 ceil(p_as_of_date-ps.due_date)),
385 least(v_bucket_days_to_3,
386 ceil(p_as_of_date-ps.due_date)),1,
387 0)
388 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
389 decode(v_bucket_category,
390 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
391 1))
392 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
393 decode(v_bucket_category,
394 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
395 1))) b3,
396 decode(v_bucket_line_type_4,
397 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
398 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
399 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
400 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
401 1),
402 decode( greatest(v_bucket_days_from_4,
403 ceil(p_as_of_date-ps.due_date)),
404 least(v_bucket_days_to_4,
405 ceil(p_as_of_date-ps.due_date)),1,
406 0)
407 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
408 decode(v_bucket_category,
409 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
410 1))
411 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
412 decode(v_bucket_category,
413 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
414 1))) b4,
415 decode(v_bucket_line_type_5,
416 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
417 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
418 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
419 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
420 1),
421 decode( greatest(v_bucket_days_from_5,
422 ceil(p_as_of_date-ps.due_date)),
423 least(v_bucket_days_to_5,
424 ceil(p_as_of_date-ps.due_date)),1,
425 0)
426 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
427 decode(v_bucket_category,
428 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
429 1))
430 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
431 decode(v_bucket_category,
432 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
433 1))) b5,
434 decode(v_bucket_line_type_6,
435 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
436 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
437 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
438 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
439 1),
440 decode( greatest(v_bucket_days_from_6,
441 ceil(p_as_of_date-ps.due_date)),
442 least(v_bucket_days_to_6,
443 ceil(p_as_of_date-ps.due_date)),1,
444 0)
445 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
446 decode(v_bucket_category,
447 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
448 1))
449 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
450 decode(v_bucket_category,
451 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
452 1))) b6
453 from ar_payment_schedules ps,
454 ar_receivable_applications app
455 where app.gl_date+0 <= p_as_of_date
456 and ps.cash_receipt_id+0 = app.cash_receipt_id
457 and app.status in ( 'ACC', 'UNAPP', 'UNID','OTHER ACC')
458 and nvl(app.confirmed_flag, 'Y') = 'Y'
459 and ps.gl_date_closed > p_as_of_date
460 and (app.reversal_gl_date > p_as_of_date OR
461 app.reversal_gl_date is null )
462 and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
463 and ps.customer_id = p_customer_id
464 and ps.org_id=app.org_id--Added for Bug 9902108 03-Aug-2010 barathsr
465 and decode(p_customer_site_use_id,
466 NULL, nvl(ps.customer_site_use_id,-10),
467 p_customer_site_use_id) = nvl(ps.customer_site_use_id,-10)
468 and decode(upper(p_currency_code),
469 NULL, ps.invoice_currency_code,
470 upper(p_currency_code)) = ps.invoice_currency_code
471 and decode(upper(p_credit_option),
472 'AGE', 'dummy',
473 'CM') <> ps.class
474 and decode(upper(p_credit_option),
475 'AGE', 'dummy',
476 'PMT') <> ps.class
477 AND PS.STATUS = 'OP' -- bug 14085877
478 group by ps.due_date,
479 ps.amount_due_original,
480 ps.amount_adjusted,
481 ps.amount_applied,
482 ps.amount_credited,
483 ps.gl_date,
484 ps.amount_in_dispute,
485 ps.amount_adjusted_pending,
486 ps.invoice_currency_code,
487 ps.exchange_rate,
488 ps.class,
489 decode( app.status, 'UNID', 'UNID', 'UNAPP');
490
491
492 v_line varchar2(100);
493 l_org_id number;
494 BEGIN
495 v_line := '-----------------------------------------' ;
496 --
497 -- Get the aging buckets definition.
498 --
499 -- IF PG_DEBUG < 10 THEN
500 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
501 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || v_line);
502 END IF;
503 -- IF PG_DEBUG < 10 THEN
504 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
505 IEX_DEBUG_PUB.LogMessage
506 ('IEX_AGING_BUCKETS_PKG. CALC_AGING_BUCKETS --->> Start <<--- ');
507 END IF;
508 -- IF PG_DEBUG < 10 THEN
509 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
510 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || v_line);
511 END IF;
512
513 OPEN c_sel_bucket_data;
514 FETCH c_sel_bucket_data
515 INTO v_bucket_days_from_0,
516 v_bucket_days_to_0,
517 p_bucket_titletop_0,
518 p_bucket_titlebottom_0,
519 v_bucket_line_type_0,
520 p_bucket_line_id_0,
521 p_bucket_seq_num_0 ;
522 -- IF PG_DEBUG < 10 THEN
523 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
524 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || ' Bucket Line Id 0 [' || to_char(p_bucket_line_id_0)
525 || '] Bucket Seq Num [' || to_char(p_bucket_seq_num_0));
526 END IF;
527
528
529 IF c_sel_bucket_data%FOUND THEN
530 p_bucket_amount_0 := 0;
531 IF (v_bucket_line_type_0 = 'DISPUTE_ONLY') OR
532 (v_bucket_line_type_0 = 'PENDADJ_ONLY') OR
533 (v_bucket_line_type_0 = 'DISPUTE_PENDADJ') THEN
534 v_bucket_category := v_bucket_line_type_0;
535 END IF;
536 FETCH c_sel_bucket_data INTO v_bucket_days_from_1, v_bucket_days_to_1,
537 p_bucket_titletop_1, p_bucket_titlebottom_1,
538 v_bucket_line_type_1,
539 p_bucket_line_id_1,
540 p_bucket_seq_num_1 ;
541
542 -- IF PG_DEBUG < 10 THEN
543 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
544 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || ' Bucket Line Id 1 [' || to_char(p_bucket_line_id_1)
545 || '] Bucket Seq Num [' || to_char(p_bucket_seq_num_1));
546 END IF;
547
548 ELSE
549 p_bucket_titletop_0 := NULL;
550 p_bucket_titlebottom_0 := NULL;
551 p_bucket_amount_0 := NULL;
552 END IF;
553 IF c_sel_bucket_data%FOUND THEN
554 p_bucket_amount_1 := 0;
555 IF (v_bucket_line_type_1 = 'DISPUTE_ONLY') OR
556 (v_bucket_line_type_1 = 'PENDADJ_ONLY') OR
557 (v_bucket_line_type_1 = 'DISPUTE_PENDADJ') THEN
558 v_bucket_category := v_bucket_line_type_1;
559 END IF;
560 FETCH c_sel_bucket_data INTO v_bucket_days_from_2, v_bucket_days_to_2,
561 p_bucket_titletop_2, p_bucket_titlebottom_2,
562 v_bucket_line_type_2,
563 p_bucket_line_id_2,
564 p_bucket_seq_num_2 ;
565 -- IF PG_DEBUG < 10 THEN
566 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
567 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || ' Bucket Line Id 2 [' || to_char(p_bucket_line_id_2)
568 || '] Bucket Seq Num [' || to_char(p_bucket_seq_num_2));
569 END IF;
570
571 ELSE
572 p_bucket_titletop_1 := NULL;
573 p_bucket_titlebottom_1 := NULL;
574 p_bucket_amount_1 := NULL;
575 END IF;
576 IF c_sel_bucket_data%FOUND THEN
577 p_bucket_amount_2 := 0;
578 IF (v_bucket_line_type_2 = 'DISPUTE_ONLY') OR
579 (v_bucket_line_type_2 = 'PENDADJ_ONLY') OR
580 (v_bucket_line_type_2 = 'DISPUTE_PENDADJ') THEN
581 v_bucket_category := v_bucket_line_type_2;
582 END IF;
583 FETCH c_sel_bucket_data INTO v_bucket_days_from_3, v_bucket_days_to_3,
584 p_bucket_titletop_3, p_bucket_titlebottom_3,
585 v_bucket_line_type_3,
586 p_bucket_line_id_3,
587 p_bucket_seq_num_3 ;
588 -- IF PG_DEBUG < 10 THEN
589 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
590 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || ' Bucket Line Id 3 [' || to_char(p_bucket_line_id_3)
591 || '] Bucket Seq Num [' || to_char(p_bucket_seq_num_3));
592 END IF;
593
594 ELSE
595 p_bucket_titletop_2 := NULL;
596 p_bucket_titlebottom_2 := NULL;
597 p_bucket_amount_2 := NULL;
598 END IF;
599 IF c_sel_bucket_data%FOUND THEN
600 p_bucket_amount_3 := 0;
601 IF (v_bucket_line_type_3 = 'DISPUTE_ONLY') OR
602 (v_bucket_line_type_3 = 'PENDADJ_ONLY') OR
603 (v_bucket_line_type_3 = 'DISPUTE_PENDADJ') THEN
604 v_bucket_category := v_bucket_line_type_3;
605 END IF;
606 FETCH c_sel_bucket_data INTO v_bucket_days_from_4, v_bucket_days_to_4,
607 p_bucket_titletop_4, p_bucket_titlebottom_4,
608 v_bucket_line_type_4,
609 p_bucket_line_id_4,
610 p_bucket_seq_num_4 ;
611 -- IF PG_DEBUG < 10 THEN
612 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
613 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || ' Bucket Line Id 4 [' || to_char(p_bucket_line_id_4)
614 || '] Bucket Seq Num [' || to_char(p_bucket_seq_num_4));
615 END IF;
616
617 ELSE
618 p_bucket_titletop_3 := NULL;
619 p_bucket_titlebottom_3 := NULL;
620 p_bucket_amount_3 := NULL;
621 END IF;
622 IF c_sel_bucket_data%FOUND THEN
623 p_bucket_amount_4 := 0;
624 IF (v_bucket_line_type_4 = 'DISPUTE_ONLY') OR
625 (v_bucket_line_type_4 = 'PENDADJ_ONLY') OR
626 (v_bucket_line_type_4 = 'DISPUTE_PENDADJ') THEN
627 v_bucket_category := v_bucket_line_type_4;
628 END IF;
629 FETCH c_sel_bucket_data INTO v_bucket_days_from_5, v_bucket_days_to_5,
630 p_bucket_titletop_5, p_bucket_titlebottom_5,
631 v_bucket_line_type_5,
632 p_bucket_line_id_5,
633 p_bucket_seq_num_5 ;
634 -- IF PG_DEBUG < 10 THEN
635 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
636 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || ' Bucket Line Id 5 [' || to_char(p_bucket_line_id_5)
637 || '] Bucket Seq Num [' || to_char(p_bucket_seq_num_5));
638 END IF;
639
640 ELSE
641 p_bucket_titletop_4 := NULL;
642 p_bucket_titlebottom_4 := NULL;
643 p_bucket_amount_4 := NULL;
644 END IF;
645 IF c_sel_bucket_data%FOUND THEN
646 p_bucket_amount_5 := 0;
647 IF (v_bucket_line_type_5 = 'DISPUTE_ONLY') OR
648 (v_bucket_line_type_5 = 'PENDADJ_ONLY') OR
649 (v_bucket_line_type_5 = 'DISPUTE_PENDADJ') THEN
650 v_bucket_category := v_bucket_line_type_5;
651 END IF;
652 FETCH c_sel_bucket_data INTO v_bucket_days_from_6, v_bucket_days_to_6,
653 p_bucket_titletop_6, p_bucket_titlebottom_6,
654 v_bucket_line_type_6,
655 p_bucket_line_id_6,
656 p_bucket_seq_num_6 ;
657 -- IF PG_DEBUG < 10 THEN
658 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
659 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || ' Bucket Line Id 6 [' || to_char(p_bucket_line_id_6)
660 || '] Bucket Seq Num [' || to_char(p_bucket_seq_num_6));
661 END IF;
662
663 ELSE
664 p_bucket_titletop_5 := NULL;
665 p_bucket_titlebottom_5 := NULL;
666 p_bucket_amount_5 := NULL;
667 END IF;
668 IF c_sel_bucket_data%FOUND THEN
669 p_bucket_amount_6 := 0;
670 IF (v_bucket_line_type_6 = 'DISPUTE_ONLY') OR
671 (v_bucket_line_type_6 = 'PENDADJ_ONLY') OR
672 (v_bucket_line_type_6 = 'DISPUTE_PENDADJ') THEN
673 v_bucket_category := v_bucket_line_type_6;
674 END IF;
675 ELSE
676 p_bucket_titletop_6 := NULL;
677 p_bucket_titlebottom_6 := NULL;
678 p_bucket_amount_6 := NULL;
679 END IF;
680 CLOSE c_sel_bucket_data;
681
682
683 l_org_id:=mo_global.get_current_org_id;--Added for Bug 9902108 03-Aug-2010 barathsr
684 IEX_DEBUG_PUB.LogMessage('current_org_id-->'||l_org_id);--Added for Bug 9902108 03-Aug-2010 barathsr
685
686 --
687 -- get the aging bucket balance. The v_bucket_ is either 1 or 0.
688 --
689 p_outstanding_balance := 0;
690 OPEN c_buckets;
691 LOOP
692 FETCH c_buckets INTO v_amount_due_remaining,
693 v_bucket_0, v_bucket_1, v_bucket_2,
694 v_bucket_3, v_bucket_4, v_bucket_5, v_bucket_6;
695 EXIT WHEN c_buckets%NOTFOUND;
696 IEX_DEBUG_PUB.LogMessage('v_amt_dure_remain-->'||v_amount_due_remaining||' '||'bucket 0-->'||v_bucket_0||' '||'bucket 1-->'||v_bucket_1);
697 IEX_DEBUG_PUB.LogMessage('bucket 2-->'||v_bucket_2||' '||'bucket 3-->'||v_bucket_3||' '||'bucket 4-->'||v_bucket_4||' '||'bucket 5-->'||v_bucket_5||' '||'bucket 6-->'||v_bucket_6);
698 p_outstanding_balance := p_outstanding_balance + v_amount_due_remaining;
699 IEX_DEBUG_PUB.LogMessage('p_out_bal-->'||p_outstanding_balance);
700 IF p_bucket_amount_0 IS NOT NULL THEN
701 p_bucket_amount_0 := p_bucket_amount_0 +
702 (v_bucket_0 * v_amount_due_remaining);
703 IEX_DEBUG_PUB.LogMessage('bucket_0_amt-->'||p_bucket_amount_0);
704
705 END IF;
706 IF p_bucket_amount_1 IS NOT NULL THEN
707 p_bucket_amount_1 := p_bucket_amount_1 +
708 (v_bucket_1 * v_amount_due_remaining);
709 IEX_DEBUG_PUB.LogMessage('bucket_1_amt-->'||p_bucket_amount_1);
710 END IF;
711 IF p_bucket_amount_2 IS NOT NULL THEN
712 p_bucket_amount_2 := p_bucket_amount_2 +
713 (v_bucket_2 * v_amount_due_remaining);
714 IEX_DEBUG_PUB.LogMessage('bucket_2_amt-->'||p_bucket_amount_2);
715 END IF;
716 IF p_bucket_amount_3 IS NOT NULL THEN
717 p_bucket_amount_3 := p_bucket_amount_3 +
718 (v_bucket_3 * v_amount_due_remaining);
719 IEX_DEBUG_PUB.LogMessage('bucket_3_amt-->'||p_bucket_amount_3);
720 END IF;
721 IF p_bucket_amount_4 IS NOT NULL THEN
722 p_bucket_amount_4 := p_bucket_amount_4 +
723 (v_bucket_4 * v_amount_due_remaining);
724 IEX_DEBUG_PUB.LogMessage('bucket_4_amt-->'||p_bucket_amount_4);
725 END IF;
726 IF p_bucket_amount_5 IS NOT NULL THEN
727 p_bucket_amount_5 := p_bucket_amount_5 +
728 (v_bucket_5 * v_amount_due_remaining);
729 IEX_DEBUG_PUB.LogMessage('bucket_5_amt-->'||p_bucket_amount_5);
730 END IF;
731 IF p_bucket_amount_6 IS NOT NULL THEN
732 p_bucket_amount_6 := p_bucket_amount_6 +
733 (v_bucket_6 * v_amount_due_remaining);
734 IEX_DEBUG_PUB.LogMessage('bucket_6_amt-->'||p_bucket_amount_6);
735 END IF;
736 END LOOP;
737 CLOSE c_buckets;
738
739 -- IF PG_DEBUG < 10 THEN
740 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
741 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || v_line);
742 END IF;
743 -- IF PG_DEBUG < 10 THEN
744 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
745 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || ' 0 - id [' || to_char(p_bucket_line_id_0)
746 || '] Seq [' || to_char(p_bucket_seq_num_0) || ' Desc [' ||
747 p_bucket_titletop_0 || ' ' || p_bucket_titlebottom_0 || '] Amt [' ||
748 to_char(p_bucket_amount_0));
749 END IF;
750 -- IF PG_DEBUG < 10 THEN
751 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
752 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || v_line);
753 END IF;
754
755 -- IF PG_DEBUG < 10 THEN
756 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
757 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || ' 1 - id [' || to_char(p_bucket_line_id_1)
758 || '] Seq [' || to_char(p_bucket_seq_num_1) || ' Desc [' ||
759 p_bucket_titletop_1 || ' ' || p_bucket_titlebottom_1 || '] Amt [' ||
760 to_char(p_bucket_amount_1));
761 END IF;
762
763
764 -- IF PG_DEBUG < 10 THEN
765 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
766 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || v_line);
767 END IF;
768 -- IF PG_DEBUG < 10 THEN
769 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
770 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || ' 2 - id [' || to_char(p_bucket_line_id_2)
771 || '] Seq [' || to_char(p_bucket_seq_num_2) || ' Desc [' ||
772 p_bucket_titletop_2 || ' ' || p_bucket_titlebottom_2 || '] Amt [' ||
773 to_char(p_bucket_amount_2));
774 END IF;
775 -- IF PG_DEBUG < 10 THEN
776 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
777 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || v_line);
778 END IF;
779
780 -- IF PG_DEBUG < 10 THEN
781 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
782 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || ' 3 - id [' || to_char(p_bucket_line_id_3)
783 || '] Seq [' || to_char(p_bucket_seq_num_3) || ' Desc [' ||
784 p_bucket_titletop_3 || ' ' || p_bucket_titlebottom_3 || '] Amt [' ||
785 to_char(p_bucket_amount_3));
786 END IF;
787 -- IF PG_DEBUG < 10 THEN
788 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
789 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || v_line);
790 END IF;
791
792 -- IF PG_DEBUG < 10 THEN
793 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
794 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || ' 4 - id [' || to_char(p_bucket_line_id_4)
795 || '] Seq [' || to_char(p_bucket_seq_num_4) || ' Desc [' ||
796 p_bucket_titletop_4 || ' ' || p_bucket_titlebottom_4 || '] Amt [' ||
797 to_char(p_bucket_amount_4));
798 END IF;
799 -- IF PG_DEBUG < 10 THEN
800 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
801 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || v_line);
802 END IF;
803
804 -- IF PG_DEBUG < 10 THEN
805 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
806 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || ' 5 - id [' || to_char(p_bucket_line_id_5)
807 || '] Seq [' || to_char(p_bucket_seq_num_5) || ' Desc [' ||
808 p_bucket_titletop_5 || ' ' || p_bucket_titlebottom_5 || '] Amt [' ||
809 to_char(p_bucket_amount_5));
810 END IF;
811 -- IF PG_DEBUG < 10 THEN
812 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
813 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || v_line);
814 END IF;
815
816 -- IF PG_DEBUG < 10 THEN
817 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
818 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || ' 6 - id [' || to_char(p_bucket_line_id_6)
819 || '] Seq [' || to_char(p_bucket_seq_num_6) || ' Desc [' ||
820 p_bucket_titletop_6 || ' ' || p_bucket_titlebottom_6 || '] Amt [' ||
821 to_char(p_bucket_amount_6));
822 END IF;
823 -- IF PG_DEBUG < 10 THEN
824 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
825 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || v_line);
826 END IF;
827
828
829 -- IF PG_DEBUG < 10 THEN
830 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
831 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || v_line);
832 END IF;
833 -- IF PG_DEBUG < 10 THEN
834 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
835 IEX_DEBUG_PUB.LogMessage
836 ('IEX_AGING_BUCKETS_PKG. CALC_AGING_BUCKETS --->> End <<--- ');
837 END IF;
838 -- IF PG_DEBUG < 10 THEN
839 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
840 IEX_DEBUG_PUB.LogMessage('calc_aging_buckets: ' || v_line);
841 END IF;
842
843 --
844 EXCEPTION
845 WHEN OTHERS THEN
846 -- IF PG_DEBUG < 10 THEN
847 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
848 iex_debug_pub.logmessage('EXCEPTION: IEX_PROFILE_AGING_PKG.calc_aging_buckets');
849 END IF;
850 END calc_aging_buckets;
851 --
852 --
853 --
854
855 /*---------------------------------------------------------------------------
856 Procedure Calc_Credits
857 ---------------------------------------------------------------------------*/
858 PROCEDURE calc_credits (
859 p_filter_mode IN VARCHAR2,
860 p_filter_id IN NUMBER,
861 p_customer_site_use_id IN NUMBER,
862 p_as_of_date IN DATE,
863 p_currency_code IN VARCHAR2,
864 p_ps_max_id IN NUMBER ,
865 p_using_paying_rel IN VARCHAR2,
866 p_credits OUT NOCOPY NUMBER
867 ) IS
868
869
870 CURSOR cust_credits IS
871 SELECT NVL( SUM( DECODE(p_currency_code, NULL,
872 ps.acctd_amount_due_remaining,
873 ps.amount_due_remaining)), 0)
874 FROM ar_payment_schedules ps,
875 ra_cust_trx_line_gl_dist gld
876 WHERE ps.customer_id = p_filter_id
877 AND decode(p_customer_site_use_id,
878 NULL, ps.customer_site_use_id,
879 p_customer_site_use_id) = ps.customer_site_use_id
880 AND decode(upper(p_currency_code),
881 NULL, ps.invoice_currency_code,
882 upper(p_currency_code)) = ps.invoice_currency_code
883 AND ps.customer_trx_id = gld.customer_trx_id
884 AND gld.account_class = 'REC'
885 AND gld.latest_rec_flag = 'Y'
886 AND ps.class||'' = 'CM'
887 and ps.status = 'OP' -- fixed a bug 5569664
888 AND ps.gl_date <= p_as_of_date;
889
890 CURSOR cust_site_credits IS
891 SELECT NVL( SUM( DECODE(p_currency_code, NULL,
892 ps.acctd_amount_due_remaining,
893 ps.amount_due_remaining)), 0)
894 FROM ar_payment_schedules ps,
895 ra_cust_trx_line_gl_dist gld
896 WHERE ps.customer_site_use_id = p_filter_id
897 AND decode(upper(p_currency_code),
898 NULL, ps.invoice_currency_code,
899 upper(p_currency_code)) = ps.invoice_currency_code
900 AND ps.customer_trx_id = gld.customer_trx_id
901 AND gld.account_class = 'REC'
902 AND gld.latest_rec_flag = 'Y'
903 AND ps.class||'' = 'CM'
904 and ps.status = 'OP' -- fixed a bug 5569664
905 AND ps.gl_date <= p_as_of_date;
906
907
908 CURSOR party_credits IS
909 SELECT NVL( SUM( DECODE(p_currency_code, NULL,
910 ps.acctd_amount_due_remaining,
911 ps.amount_due_remaining)), 0)
912 FROM ar_payment_schedules ps,
913 ra_cust_trx_line_gl_dist gld,
914 hz_cust_accounts hzca
915 WHERE ps.customer_id = hzca.cust_account_id
916 AND hzca.party_id = p_filter_id
917 AND decode(p_customer_site_use_id,
918 NULL, ps.customer_site_use_id,
919 p_customer_site_use_id) = ps.customer_site_use_id
920 AND decode(upper(p_currency_code),
921 NULL, ps.invoice_currency_code,
922 upper(p_currency_code)) = ps.invoice_currency_code
923 AND ps.customer_trx_id = gld.customer_trx_id
924 AND gld.account_class = 'REC'
925 AND gld.latest_rec_flag = 'Y'
926 AND ps.class||'' = 'CM'
927 and ps.status = 'OP' -- fixed a bug 5569664
928 AND ps.gl_date <= p_as_of_date;
929
930 CURSOR party_paying_credits IS
931 SELECT NVL( SUM( DECODE(p_currency_code, NULL,
932 ps.acctd_amount_due_remaining,
933 ps.amount_due_remaining)), 0)
934 FROM ar_payment_schedules ps,
935 ra_cust_trx_line_gl_dist gld,
936 hz_cust_accounts hzca
937 WHERE ps.customer_id = hzca.cust_account_id
938 AND hzca.party_id IN
939 (SELECT p_filter_id FROM dual
940 UNION
941 SELECT ar.related_party_id
942 FROM ar_paying_relationships_v ar
943 WHERE ar.party_id = p_filter_id
944 AND TRUNC(sysdate) BETWEEN
945 TRUNC(NVL(ar.effective_start_date,sysdate)) AND
946 TRUNC(NVL(ar.effective_end_date,sysdate)) )
947 AND decode(p_customer_site_use_id,
948 NULL, ps.customer_site_use_id,
949 p_customer_site_use_id) = ps.customer_site_use_id
950 AND decode(upper(p_currency_code),
951 NULL, ps.invoice_currency_code,
952 upper(p_currency_code)) = ps.invoice_currency_code
953 AND ps.customer_trx_id = gld.customer_trx_id
954 AND gld.account_class = 'REC'
955 AND gld.latest_rec_flag = 'Y'
956 AND ps.class||'' = 'CM'
957 and ps.status = 'OP' -- fixed a bug 5569664
958 AND ps.gl_date <= p_as_of_date;
959
960
961
962 BEGIN
963 -- IF PG_DEBUG < 10 THEN
964 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
965 IEX_DEBUG_PUB.LogMessage('calc_credits: ' || l_line);
966 END IF;
967 -- IF PG_DEBUG < 10 THEN
968 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
969 IEX_DEBUG_PUB.LogMessage
970 ('IEX_AGING_BUCKETS_PKG.CALC_CREDITS --->> Start <<--- ');
971 END IF;
972 -- IF PG_DEBUG < 10 THEN
973 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
974 IEX_DEBUG_PUB.LogMessage('calc_credits: ' || l_line);
975 END IF;
976
977 p_credits := 0;
978
979 If p_filter_mode = 'PARTY' then
980 IF NVL(p_using_paying_rel, 'N') = 'Y' THEN
981 OPEN party_paying_credits;
982 FETCH party_paying_credits INTO p_credits;
983 Close party_paying_credits ;
984 ELSE
985 OPEN party_credits;
986 FETCH party_credits INTO p_credits;
987 Close party_credits ;
988 END IF;
989 elsif p_filter_mode = 'CUST' then
990 OPEN cust_credits;
991 FETCH cust_credits INTO p_credits;
992 Close cust_credits ;
993 elsif p_filter_mode = 'BILLTO' then
994 OPEN cust_site_credits;
995 FETCH cust_site_credits INTO p_credits;
996 Close cust_site_credits ;
997 End If ;
998 --
999 -- IF PG_DEBUG < 10 THEN
1000 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1001 IEX_DEBUG_PUB.LogMessage('calc_credits: ' || l_line);
1002 END IF;
1003 -- IF PG_DEBUG < 10 THEN
1004 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1005 IEX_DEBUG_PUB.LogMessage
1006 ('IEX_AGING_BUCKETS_PKG.CALC_CREDITS --->> End <<--- ');
1007 END IF;
1008 -- IF PG_DEBUG < 10 THEN
1009 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1010 IEX_DEBUG_PUB.LogMessage('calc_credits: ' || l_line);
1011 END IF;
1012
1013 EXCEPTION
1014 WHEN OTHERS THEN
1015 -- IF PG_DEBUG < 10 THEN
1016 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1017 iex_debug_pub.logmessage('EXCEPTION: Iex_Aging_Buckets_Pkg.calc_credits');
1018 END IF;
1019 -- IF PG_DEBUG < 10 THEN
1020 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1021 iex_debug_pub.logmessage('calc_credits: ' || SQLCODE || ' ---> ' || SQLERRM);
1022 END IF;
1023 if cust_credits%ISOPEN then
1024 CLOSE cust_credits ;
1025 End If ;
1026 if party_credits%ISOPEN then
1027 CLOSE party_credits ;
1028 End If ;
1029
1030 -- IF PG_DEBUG < 10 THEN
1031 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1032 IEX_DEBUG_PUB.LogMessage('calc_credits: ' || l_line);
1033 END IF;
1034 -- IF PG_DEBUG < 10 THEN
1035 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1036 IEX_DEBUG_PUB.LogMessage
1037 ('IEX_AGING_BUCKETS_PKG.CALC_CREDITS --->> End with Exception <<--- ');
1038 END IF;
1039 -- IF PG_DEBUG < 10 THEN
1040 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1041 IEX_DEBUG_PUB.LogMessage('calc_credits: ' || l_line);
1042 END IF;
1043
1044 END calc_credits;
1045 --
1046 --
1047 --
1048 /*---------------------------------------------------------------------------
1049 Procedure Calc_receipts
1050 ---------------------------------------------------------------------------*/
1051 PROCEDURE calc_receipts (
1052 p_filter_mode IN VARCHAR2,
1053 p_filter_id IN NUMBER,
1054 p_customer_site_use_id IN NUMBER,
1055 p_as_of_date IN DATE,
1056 p_currency_code IN VARCHAR2,
1057 p_app_max_id IN NUMBER ,
1058 p_using_paying_rel IN VARCHAR2,
1059 p_unapplied_cash OUT NOCOPY NUMBER,
1060 p_onacct_cash OUT NOCOPY NUMBER,
1061 p_cash_claims OUT NOCOPY NUMBER,
1062 p_prepayments OUT NOCOPY NUMBER)
1063 IS
1064
1065 CURSOR party_unapplied_cash
1066 IS
1067 SELECT NVL(SUM( DECODE(app.status,'UNAPP',
1068 DECODE(p_currency_code, NULL,
1069 -app.acctd_amount_applied_from,
1070 -app.amount_applied), 0) ), 0),
1071 NVL(SUM( DECODE(app.status,'ACC',
1072 DECODE(p_currency_code, NULL,
1073 -app.acctd_amount_applied_from,
1074 -app.amount_applied), 0) ), 0),
1075 NVL(SUM( DECODE(app.status,'OTHER ACC',
1076 DECODE(app.applied_payment_schedule_id, -4,
1077 DECODE(p_currency_code, NULL,
1078 -app.acctd_amount_applied_from,
1079 -app.amount_applied),0), 0) ), 0),
1080 NVL(SUM( DECODE(app.status,'OTHER ACC',
1081 DECODE(app.applied_payment_schedule_id, -7,
1082 DECODE(p_currency_code, NULL,
1083 -app.acctd_amount_applied_from,
1084 -app.amount_applied),0), 0) ), 0)
1085 FROM ar_receivable_applications app,
1086 ar_payment_schedules ps,
1087 hz_cust_accounts hzca
1088 WHERE ps.customer_id = hzca.cust_account_id
1089 AND hzca.party_id = p_filter_id
1090 AND decode(p_customer_site_use_id,
1091 NULL, nvl(ps.customer_site_use_id,-10),
1092 p_customer_site_use_id) = nvl(ps.customer_site_use_id,-10)
1093 AND ps.cash_receipt_id = app.cash_receipt_id
1094 AND nvl( app.confirmed_flag, 'Y' ) = 'Y'
1095 AND app.status in ( 'UNAPP', 'ACC' ,'OTHER ACC')
1096 AND decode(upper(p_currency_code),
1097 NULL, ps.invoice_currency_code,
1098 upper(p_currency_code)) = ps.invoice_currency_code
1099 AND app.gl_date <= p_as_of_date;
1100
1101 CURSOR party_paying_unapplied_cash
1102 IS
1103 SELECT NVL(SUM( DECODE(app.status,'UNAPP',
1104 DECODE(p_currency_code, NULL,
1105 -app.acctd_amount_applied_from,
1106 -app.amount_applied), 0) ), 0),
1107 NVL(SUM( DECODE(app.status,'ACC',
1108 DECODE(p_currency_code, NULL,
1109 -app.acctd_amount_applied_from,
1110 -app.amount_applied), 0) ), 0),
1111 NVL(SUM( DECODE(app.status,'OTHER ACC',
1112 DECODE(app.applied_payment_schedule_id, -4,
1113 DECODE(p_currency_code, NULL,
1114 -app.acctd_amount_applied_from,
1115 -app.amount_applied),0), 0) ), 0),
1116 NVL(SUM( DECODE(app.status,'OTHER ACC',
1117 DECODE(app.applied_payment_schedule_id, -7,
1118 DECODE(p_currency_code, NULL,
1119 -app.acctd_amount_applied_from,
1120 -app.amount_applied),0), 0) ), 0)
1121 FROM ar_receivable_applications app,
1122 ar_payment_schedules ps,
1123 hz_cust_accounts hzca
1124 WHERE ps.customer_id = hzca.cust_account_id
1125 AND hzca.party_id IN
1126 (SELECT p_filter_id FROM dual
1127 UNION
1128 SELECT ar.related_party_id
1129 FROM ar_paying_relationships_v ar
1130 WHERE ar.party_id = p_filter_id
1131 AND TRUNC(sysdate) BETWEEN
1132 TRUNC(NVL(ar.effective_start_date,sysdate)) AND
1133 TRUNC(NVL(ar.effective_end_date,sysdate)) )
1134 AND decode(p_customer_site_use_id,
1135 NULL, nvl(ps.customer_site_use_id,-10),
1136 p_customer_site_use_id) = nvl(ps.customer_site_use_id,-10)
1137 AND ps.cash_receipt_id = app.cash_receipt_id
1138 AND nvl( app.confirmed_flag, 'Y' ) = 'Y'
1139 AND app.status in ( 'UNAPP', 'ACC' ,'OTHER ACC')
1140 AND decode(upper(p_currency_code),
1141 NULL, ps.invoice_currency_code,
1142 upper(p_currency_code)) = ps.invoice_currency_code
1143 AND app.gl_date <= p_as_of_date;
1144
1145
1146
1147
1148
1149
1150 CURSOR cust_unapplied_cash
1151 IS
1152 SELECT NVL(SUM( DECODE(app.status,'UNAPP',
1153 DECODE(p_currency_code, NULL,
1154 -app.acctd_amount_applied_from,
1155 -app.amount_applied), 0) ), 0),
1156 NVL(SUM( DECODE(app.status,'ACC',
1157 DECODE(p_currency_code, NULL,
1158 -app.acctd_amount_applied_from,
1159 -app.amount_applied), 0) ), 0),
1160 NVL(SUM( DECODE(app.status,'OTHER ACC',
1161 DECODE(app.applied_payment_schedule_id, -4,
1162 DECODE(p_currency_code, NULL,
1163 -app.acctd_amount_applied_from,
1164 -app.amount_applied),0), 0) ), 0),
1165 NVL(SUM( DECODE(app.status,'OTHER ACC',
1166 DECODE(app.applied_payment_schedule_id, -7,
1167 DECODE(p_currency_code, NULL,
1168 -app.acctd_amount_applied_from,
1169 -app.amount_applied),0), 0) ), 0)
1170 FROM ar_receivable_applications app,
1171 ar_payment_schedules ps
1172 WHERE ps.customer_id = p_filter_id
1173 AND decode(p_customer_site_use_id,
1174 NULL, nvl(ps.customer_site_use_id,-10),
1175 p_customer_site_use_id) = nvl(ps.customer_site_use_id,-10)
1176 AND ps.cash_receipt_id = app.cash_receipt_id
1177 AND nvl( app.confirmed_flag, 'Y' ) = 'Y'
1178 AND app.status in ( 'UNAPP', 'ACC' ,'OTHER ACC')
1179 AND decode(upper(p_currency_code),
1180 NULL, ps.invoice_currency_code,
1181 upper(p_currency_code)) = ps.invoice_currency_code
1182 AND app.gl_date <= p_as_of_date;
1183
1184
1185
1186 CURSOR cust_site_unapplied_cash
1187 IS
1188 SELECT NVL(SUM( DECODE(app.status,'UNAPP',
1189 DECODE(p_currency_code, NULL,
1190 -app.acctd_amount_applied_from,
1191 -app.amount_applied), 0) ), 0),
1192 NVL(SUM( DECODE(app.status,'ACC',
1193 DECODE(p_currency_code, NULL,
1194 -app.acctd_amount_applied_from,
1195 -app.amount_applied), 0) ), 0),
1196 NVL(SUM( DECODE(app.status,'OTHER ACC',
1197 DECODE(app.applied_payment_schedule_id, -4,
1198 DECODE(p_currency_code, NULL,
1199 -app.acctd_amount_applied_from,
1200 -app.amount_applied),0), 0) ), 0),
1201 NVL(SUM( DECODE(app.status,'OTHER ACC',
1202 DECODE(app.applied_payment_schedule_id, -7,
1203 DECODE(p_currency_code, NULL,
1204 -app.acctd_amount_applied_from,
1205 -app.amount_applied),0), 0) ), 0)
1206 FROM ar_receivable_applications app,
1207 ar_payment_schedules ps
1208 WHERE ps.customer_site_use_id = p_filter_id
1209 AND ps.cash_receipt_id = app.cash_receipt_id
1210 AND nvl( app.confirmed_flag, 'Y' ) = 'Y'
1211 AND app.status in ( 'UNAPP', 'ACC' ,'OTHER ACC')
1212 AND decode(upper(p_currency_code),
1213 NULL, ps.invoice_currency_code,
1214 upper(p_currency_code)) = ps.invoice_currency_code
1215 AND app.gl_date <= p_as_of_date;
1216
1217 --Bug4388111. Fixed By LKKUMAR. Need to Pouplate values for BILLTO. Start.
1218 CURSOR billto_unapplied_cash
1219 IS
1220 SELECT NVL(SUM( DECODE(app.status,'UNAPP',
1221 DECODE(p_currency_code, NULL,
1222 -app.acctd_amount_applied_from,
1223 -app.amount_applied), 0) ), 0),
1224 NVL(SUM( DECODE(app.status,'ACC',
1225 DECODE(p_currency_code, NULL,
1226 -app.acctd_amount_applied_from,
1227 -app.amount_applied), 0) ), 0),
1228 NVL(SUM( DECODE(app.status,'OTHER ACC',
1229 DECODE(app.applied_payment_schedule_id, -4,
1230 DECODE(p_currency_code, NULL,
1231 -app.acctd_amount_applied_from,
1232 -app.amount_applied),0), 0) ), 0),
1233 NVL(SUM( DECODE(app.status,'OTHER ACC',
1234 DECODE(app.applied_payment_schedule_id, -7,
1235 DECODE(p_currency_code, NULL,
1236 -app.acctd_amount_applied_from,
1237 -app.amount_applied),0), 0) ), 0)
1238 FROM ar_receivable_applications app,
1239 ar_payment_schedules ps,
1240 hz_cust_site_uses hzsu,
1241 hz_cust_acct_sites hzas
1242 WHERE ps.customer_id = hzas.cust_account_id
1243 and hzsu.cust_acct_site_id = hzas.cust_acct_site_id
1244 AND hzsu.site_use_id = ps.customer_site_use_id
1245 AND ps.cash_receipt_id = app.cash_receipt_id
1246 AND hzsu.site_use_id = p_filter_id
1247 AND decode(p_customer_site_use_id,
1248 NULL, nvl(ps.customer_site_use_id,-10),
1249 p_customer_site_use_id) = nvl(ps.customer_site_use_id,-10)
1250 AND ps.cash_receipt_id = app.cash_receipt_id
1251 AND nvl( app.confirmed_flag, 'Y' ) = 'Y'
1252 AND app.status in ( 'UNAPP', 'ACC' ,'OTHER ACC')
1253 AND decode(upper(p_currency_code),
1254 NULL, ps.invoice_currency_code,
1255 upper(p_currency_code)) = ps.invoice_currency_code
1256 AND app.gl_date <= p_as_of_date;
1257 --Bug4388111. Fixed By LKKUMAR. Need to Pouplate values for BILLTO. End.
1258 BEGIN
1259 p_unapplied_cash := 0;
1260 p_onacct_cash := 0;
1261
1262 If p_filter_mode = 'PARTY' then
1263 IF NVL(p_using_paying_rel, 'N') = 'Y' THEN
1264 OPEN party_paying_unapplied_cash;
1265 FETCH party_paying_unapplied_cash
1266 INTO p_unapplied_cash, p_onacct_cash,p_cash_claims,p_prepayments;
1267 Close party_paying_unapplied_cash ;
1268 ELSE
1269 OPEN party_unapplied_cash;
1270 FETCH party_unapplied_cash
1271 INTO p_unapplied_cash, p_onacct_cash,p_cash_claims,p_prepayments;
1272 Close party_unapplied_cash ;
1273 END IF;
1274 elsif p_filter_mode = 'CUST' then
1275 OPEN cust_unapplied_cash;
1276 FETCH cust_unapplied_cash
1277 INTO p_unapplied_cash, p_onacct_cash,p_cash_claims,p_prepayments;
1278 Close cust_unapplied_cash ;
1279 --Bug4388111. Fixed By LKKUMAR. Need to Pouplate values for BILLTO. Start.
1280 elsif p_filter_mode = 'BILLTO' then
1281 OPEN billto_unapplied_cash;
1282 FETCH billto_unapplied_cash
1283 INTO p_unapplied_cash, p_onacct_cash,p_cash_claims,p_prepayments;
1284 Close billto_unapplied_cash ;
1285 --Bug4388111. Fixed By LKKUMAR. Need to Pouplate values for BILLTO. Start.
1286 End If ;
1287
1288 EXCEPTION
1289 WHEN OTHERS THEN
1290 -- IF PG_DEBUG < 10 THEN
1291 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1292 iex_debug_pub.logmessage
1293 ('EXCEPTION: Iex_Aging_Buckets_Pkg.calc_receipts');
1294 END IF;
1295 -- IF PG_DEBUG < 10 THEN
1296 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1297 iex_debug_pub.logmessage('calc_receipts: ' || SQLCODE || ' ---> ' || SQLERRM);
1298 END IF;
1299 if cust_unapplied_cash%ISOPEN then
1300 CLOSE cust_unapplied_cash ;
1301 End If ;
1302 if party_unapplied_cash%ISOPEN then
1303 CLOSE party_unapplied_cash ;
1304 End If ;
1305
1306 -- IF PG_DEBUG < 10 THEN
1307 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1308 IEX_DEBUG_PUB.LogMessage('calc_receipts: ' || l_line);
1309 END IF;
1310 -- IF PG_DEBUG < 10 THEN
1311 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1312 IEX_DEBUG_PUB.LogMessage
1313 ('IEX_AGING_BUCKETS_PKG.CALC_RECEIPTS --->> End with Exception <<---');
1314 END IF;
1315 -- IF PG_DEBUG < 10 THEN
1316 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1317 IEX_DEBUG_PUB.LogMessage('calc_receipts: ' || l_line);
1318 END IF;
1319 END calc_receipts;
1320 --
1321 --
1322 --
1323 PROCEDURE calc_risk_receipts (
1324 p_filter_mode IN Varchar2,
1325 p_filter_id IN NUMBER,
1326 p_customer_site_use_id IN NUMBER,
1327 p_as_of_date IN DATE,
1328 p_currency_code IN VARCHAR2,
1329 p_ps_max_id IN NUMBER,
1330 p_using_paying_rel IN VARCHAR2,
1331 p_risk_receipts OUT NOCOPY NUMBER
1332 ) IS
1333 CURSOR cust_risk_receipts IS
1334 SELECT NVL( SUM( DECODE(p_currency_code, NULL, crh.acctd_amount,
1335 crh.amount)), 0)
1336 FROM ar_cash_receipts cr,
1337 ar_cash_receipt_history crh
1338 WHERE cr.pay_from_customer = p_filter_id
1339 /* bug no : 1274152. Aging form did not consider the receipts done
1340 without customer location for the calculation of receipt at risk.
1341 NVL is added for ps.customer_site_use_id in the procedure
1342 calc_risk_receipts to avoid null = null comparison fixed by rajsrini */
1343 AND decode(p_customer_site_use_id,
1344 NULL, nvl(cr.customer_site_use_id,0),
1345 p_customer_site_use_id) = nvl(cr.customer_site_use_id,0)
1346 AND cr.currency_code= nvl(p_currency_code,cr.currency_code)
1347 AND cr.reversal_date is null
1348 AND nvl(cr.confirmed_flag,'Y') = 'Y'
1349 AND cr.cash_receipt_id = crh.cash_receipt_id
1350 AND crh.current_record_flag||'' = 'Y'
1351 AND crh.gl_date <= p_as_of_date
1352 AND crh.status NOT IN ( DECODE ( crh.factor_flag,'Y',
1353 'RISK_ELIMINATED','N', 'CLEARED'), 'REVERSED' )
1354 /* 06-AUG-2000 J Rautiainen BR Implementation
1355 * Short term debt applications are not considered as receipts at risk */
1356 and not exists (select 'X'
1357 from ar_receivable_applications rap
1358 where rap.cash_receipt_id = cr.cash_receipt_id
1359 and rap.applied_payment_schedule_id = -2
1360 and rap.display = 'Y');
1361
1362 CURSOR party_risk_receipts IS
1363 SELECT NVL( SUM( DECODE(p_currency_code, NULL, crh.acctd_amount,
1364 crh.amount)), 0)
1365 FROM ar_cash_receipts cr,
1366 ar_cash_receipt_history crh,
1367 hz_cust_accounts hzca
1368 WHERE cr.pay_from_customer = hzca.cust_account_id
1369 AND hzca.party_id = p_filter_id
1370 /* bug no : 1274152. Aging form did not consider the receipts done
1371 without customer location for the calculation of receipt at risk.
1372 NVL is added for ps.customer_site_use_id in the procedure
1373 calc_risk_receipts to avoid null = null comparison fixed by rajsrini */
1374 AND decode(p_customer_site_use_id,
1375 NULL, nvl(cr.customer_site_use_id,0),
1376 p_customer_site_use_id) = nvl(cr.customer_site_use_id,0)
1377 AND cr.currency_code= nvl(p_currency_code,cr.currency_code)
1378 AND cr.reversal_date is null
1379 AND nvl(cr.confirmed_flag,'Y') = 'Y'
1380 AND cr.cash_receipt_id = crh.cash_receipt_id
1381 AND crh.current_record_flag||'' = 'Y'
1382 AND crh.gl_date <= p_as_of_date
1383 AND crh.status NOT IN ( DECODE ( crh.factor_flag,'Y',
1384 'RISK_ELIMINATED','N', 'CLEARED'), 'REVERSED' )
1385 /* 06-AUG-2000 J Rautiainen BR Implementation
1386 * Short term debt applications are not considered as receipts at risk */
1387 and not exists (select 'X'
1388 from ar_receivable_applications rap
1389 where rap.cash_receipt_id = cr.cash_receipt_id
1390 and rap.applied_payment_schedule_id = -2
1391 and rap.display = 'Y');
1392
1393 CURSOR party_paying_risk_receipts IS
1394 SELECT NVL( SUM( DECODE(p_currency_code, NULL, crh.acctd_amount,
1395 crh.amount)), 0)
1396 FROM ar_cash_receipts cr,
1397 ar_cash_receipt_history crh,
1398 hz_cust_accounts hzca
1399 WHERE cr.pay_from_customer = hzca.cust_account_id
1400 AND hzca.party_id IN
1401 (SELECT p_filter_id FROM dual
1402 UNION
1403 SELECT ar.related_party_id
1404 FROM ar_paying_relationships_v ar
1405 WHERE ar.party_id = p_filter_id
1406 AND TRUNC(sysdate) BETWEEN
1407 TRUNC(NVL(ar.effective_start_date,sysdate)) AND
1408 TRUNC(NVL(ar.effective_end_date,sysdate)) )
1409 /* bug no : 1274152. Aging form did not consider the receipts done
1410 without customer location for the calculation of receipt at risk.
1411 NVL is added for ps.customer_site_use_id in the procedure
1412 calc_risk_receipts to avoid null = null comparison fixed by rajsrini */
1413 AND decode(p_customer_site_use_id,
1414 NULL, nvl(cr.customer_site_use_id,0),
1415 p_customer_site_use_id) = nvl(cr.customer_site_use_id,0)
1416 AND cr.currency_code= nvl(p_currency_code,cr.currency_code)
1417 AND cr.reversal_date is null
1418 AND nvl(cr.confirmed_flag,'Y') = 'Y'
1419 AND cr.cash_receipt_id = crh.cash_receipt_id
1420 AND crh.current_record_flag||'' = 'Y'
1421 AND crh.gl_date <= p_as_of_date
1422 AND crh.status NOT IN ( DECODE ( crh.factor_flag,'Y',
1423 'RISK_ELIMINATED','N', 'CLEARED'), 'REVERSED' )
1424 /* 06-AUG-2000 J Rautiainen BR Implementation
1425 * Short term debt applications are not considered as receipts at risk */
1426 and not exists (select 'X'
1427 from ar_receivable_applications rap
1428 where rap.cash_receipt_id = cr.cash_receipt_id
1429 and rap.applied_payment_schedule_id = -2
1430 and rap.display = 'Y');
1431
1432 CURSOR cust_site_risk_receipts IS
1433 SELECT NVL( SUM( DECODE(p_currency_code, NULL, crh.acctd_amount,
1434 crh.amount)), 0)
1435 FROM ar_cash_receipts cr,
1436 ar_cash_receipt_history crh,
1437 /* begin add for bug 4930373 to use AR_CASH_RECEIPTS_N2 */
1438 hz_cust_acct_sites_all acct_site,
1439 hz_cust_site_uses_all site_uses,
1440 hz_cust_accounts_all cust_acct
1441 /* end add for bug 4930373 to use AR_CASH_RECEIPTS_N2 */
1442 WHERE cr.customer_site_use_id = p_filter_id
1443 /* bug no : 1274152. Aging form did not consider the receipts done
1444 without customer location for the calculation of receipt at risk.
1445 NVL is added for ps.customer_site_use_id in the procedure
1446 calc_risk_receipts to avoid null = null comparison fixed by rajsrini */
1447 AND cr.currency_code= nvl(p_currency_code,cr.currency_code)
1448 AND cr.reversal_date is null
1449 AND nvl(cr.confirmed_flag,'Y') = 'Y'
1450 AND cr.cash_receipt_id = crh.cash_receipt_id
1451 AND crh.current_record_flag||'' = 'Y'
1452 AND crh.gl_date <= p_as_of_date
1453 AND crh.status NOT IN ( DECODE ( crh.factor_flag,'Y',
1454 'RISK_ELIMINATED','N', 'CLEARED'), 'REVERSED' )
1455 /* 06-AUG-2000 J Rautiainen BR Implementation
1456 * Short term debt applications are not considered as receipts at risk */
1457 and not exists (select 'X'
1458 from ar_receivable_applications rap
1459 where rap.cash_receipt_id = cr.cash_receipt_id
1460 and rap.applied_payment_schedule_id = -2
1461 and rap.display = 'Y')
1462 /* begin add for bug 4930373 to use AR_CASH_RECEIPTS_N2 */
1463 and site_uses.site_use_id = p_filter_id
1464 AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
1465 and cust_acct.cust_account_id = acct_site.cust_account_id
1466 and cust_acct.party_id = cr.pay_from_customer;
1467 /* end add for bug 4930373 to use AR_CASH_RECEIPTS_N2 */
1468
1469
1470 BEGIN
1471
1472 -- IF PG_DEBUG < 10 THEN
1473 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1474 IEX_DEBUG_PUB.LogMessage('calc_risk_receipts: ' || l_line);
1475 END IF;
1476 -- IF PG_DEBUG < 10 THEN
1477 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1478 IEX_DEBUG_PUB.LogMessage
1479 ('IEX_AGING_BUCKETS_PKG.CALC_risk_receipts --->> Start <<--- ');
1480 END IF;
1481 -- IF PG_DEBUG < 10 THEN
1482 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1483 IEX_DEBUG_PUB.LogMessage('calc_risk_receipts: ' || l_line);
1484 END IF;
1485
1486 p_risk_receipts := 0;
1487
1488 If p_filter_mode = 'PARTY' then
1489 IF NVL(p_using_paying_rel, 'N') = 'Y' THEN
1490 OPEN party_paying_risk_receipts;
1491 FETCH party_paying_risk_receipts INTO p_risk_receipts;
1492 Close party_paying_risk_receipts ;
1493 ELSE
1494 OPEN party_risk_receipts;
1495 FETCH party_risk_receipts INTO p_risk_receipts;
1496 Close party_risk_receipts ;
1497 END IF;
1498 elsif p_filter_mode = 'CUST' then
1499 OPEN cust_risk_receipts;
1500 FETCH cust_risk_receipts INTO p_risk_receipts;
1501 Close cust_risk_receipts ;
1502 elsif p_filter_mode = 'BILLTO' then
1503 OPEN cust_site_risk_receipts;
1504 FETCH cust_site_risk_receipts INTO p_risk_receipts;
1505 Close cust_site_risk_receipts ;
1506 End If ;
1507 --
1508 -- IF PG_DEBUG < 10 THEN
1509 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1510 IEX_DEBUG_PUB.LogMessage('calc_risk_receipts: ' || l_line);
1511 END IF;
1512 -- IF PG_DEBUG < 10 THEN
1513 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1514 IEX_DEBUG_PUB.LogMessage
1515 ('IEX_AGING_BUCKETS_PKG.CALC_risk_receipts --->> End <<--- ');
1516 END IF;
1517 -- IF PG_DEBUG < 10 THEN
1518 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1519 IEX_DEBUG_PUB.LogMessage('calc_risk_receipts: ' || l_line);
1520 END IF;
1521
1522 EXCEPTION
1523 WHEN OTHERS THEN
1524 -- IF PG_DEBUG < 10 THEN
1525 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1526 iex_debug_pub.logmessage('EXCEPTION: Iex_Aging_Buckets_Pkg.calc_risk_receipts');
1527 END IF;
1528 -- IF PG_DEBUG < 10 THEN
1529 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1530 iex_debug_pub.logmessage('calc_risk_receipts: ' || SQLCODE || ' ---> ' || SQLERRM);
1531 END IF;
1532 if cust_risk_receipts%ISOPEN then
1533 CLOSE cust_risk_receipts ;
1534 End If ;
1535 if party_risk_receipts%ISOPEN then
1536 CLOSE party_risk_receipts ;
1537 End If ;
1538
1539 -- IF PG_DEBUG < 10 THEN
1540 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1541 IEX_DEBUG_PUB.LogMessage('calc_risk_receipts: ' || l_line);
1542 END IF;
1543 -- IF PG_DEBUG < 10 THEN
1544 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1545 IEX_DEBUG_PUB.LogMessage
1546 ('IEX_AGING_BUCKETS_PKG.CALC_risk_receipts --->> End with Exception <<--- ');
1547 END IF;
1548 -- IF PG_DEBUG < 10 THEN
1549 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1550 IEX_DEBUG_PUB.LogMessage('calc_risk_receipts: ' || l_line);
1551 END IF;
1552
1553 END calc_risk_receipts;
1554 --
1555 --
1556 --
1557 /*---------------------------------------------------------------------------
1558 Procedure Calc_Dispute
1559 ---------------------------------------------------------------------------*/
1560 PROCEDURE calc_dispute
1561 (p_filter_mode IN VARCHAR2,
1562 p_filter_id IN NUMBER,
1563 p_customer_site_use_id IN NUMBER,
1564 p_as_of_date IN DATE,
1565 p_currency_code IN VARCHAR2,
1566 p_ps_max_id IN NUMBER,
1567 p_using_paying_rel IN VARCHAR2,
1568 p_dispute OUT NOCOPY NUMBER)
1569 IS
1570
1571 CURSOR cust_dispute IS
1572 SELECT NVL(SUM(decode(p_currency_code, NULL,
1573 ROUND(ps.amount_in_dispute * nvl(ps.exchange_rate,1),l_round_decimal),
1574 ps.amount_in_dispute)),0)
1575 FROM ar_payment_schedules ps
1576 WHERE ps.customer_id = p_filter_id
1577 AND decode(p_customer_site_use_id,
1578 NULL, ps.customer_site_use_id,
1579 p_customer_site_use_id) = ps.customer_site_use_id
1580 AND decode(upper(p_currency_code),
1581 NULL, ps.invoice_currency_code,
1582 upper(p_currency_code)) = ps.invoice_currency_code
1583 --AND ps.due_date <= p_as_of_date --Commented for bug#7044352 on 23-May-2008 by SCHEKURI
1584 AND nvl( ps.amount_in_dispute, 0 ) <> 0
1585 -- bug 13092458 and ps.amount_due_remaining > 0 -- fixed a bug 5473635,
1586 and ps.status = 'OP' -- fixed a bug 5569664
1587 AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y' ;
1588
1589
1590 CURSOR cust_site_dispute IS
1591 SELECT NVL(SUM(decode(p_currency_code, NULL,
1592 ROUND(ps.amount_in_dispute * nvl(ps.exchange_rate,1),l_round_decimal),
1593 ps.amount_in_dispute)),0)
1594 FROM ar_payment_schedules ps
1595 WHERE ps.customer_site_use_id = p_filter_id
1596 AND decode(upper(p_currency_code),
1597 NULL, ps.invoice_currency_code,
1598 upper(p_currency_code)) = ps.invoice_currency_code
1599 --AND ps.due_date <= p_as_of_date --Commented for bug#7044352 on 23-May-2008 by SCHEKURI
1600 AND nvl( ps.amount_in_dispute, 0 ) <> 0
1601 --- Begin - Andre Araujo - 11/09/2004 - Performance fix we will select the aging separately
1602 AND ps.status = 'OP'
1603 --- End - Andre Araujo - 11/09/2004 - Performance fix we will select the aging separately
1604 -- bug 13092458 and ps.amount_due_remaining > 0 -- fixed a bug 5473635
1605 AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y' ;
1606
1607
1608 CURSOR party_dispute IS
1609 SELECT NVL(SUM(decode(p_currency_code, NULL,
1610 ROUND(ps.amount_in_dispute * nvl(ps.exchange_rate,1),l_round_decimal),
1611 ps.amount_in_dispute)),0)
1612 FROM ar_payment_schedules ps,
1613 hz_cust_accounts hzca
1614 WHERE ps.customer_id = hzca.cust_Account_id
1615 AND hzca.party_id = p_filter_id
1616 AND decode(p_customer_site_use_id,
1617 NULL, ps.customer_site_use_id,
1618 p_customer_site_use_id) = ps.customer_site_use_id
1619 AND decode(upper(p_currency_code),
1620 NULL, ps.invoice_currency_code,
1621 upper(p_currency_code)) = ps.invoice_currency_code
1622 --AND ps.due_date <= p_as_of_date --Commented for bug#7044352 on 23-May-2008 by SCHEKURI
1623 AND nvl( ps.amount_in_dispute, 0 ) <> 0
1624 -- bug 13092458 and ps.amount_due_remaining > 0 -- fixed a bug 5473635
1625 and ps.status = 'OP' -- fixed a bug 5569664
1626 AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y' ;
1627
1628 CURSOR party_paying_dispute IS
1629 SELECT NVL(SUM(decode(p_currency_code, NULL,
1630 ROUND(ps.amount_in_dispute * nvl(ps.exchange_rate,1),l_round_decimal),
1631 ps.amount_in_dispute)),0)
1632 FROM ar_payment_schedules ps,
1633 hz_cust_accounts hzca
1634 WHERE ps.customer_id = hzca.cust_Account_id
1635 AND hzca.party_id IN
1636 (SELECT p_filter_id FROM dual
1637 UNION
1638 SELECT ar.related_party_id
1639 FROM ar_paying_relationships_v ar
1640 WHERE ar.party_id = p_filter_id
1641 AND TRUNC(sysdate) BETWEEN
1642 TRUNC(NVL(ar.effective_start_date,sysdate)) AND
1643 TRUNC(NVL(ar.effective_end_date,sysdate)) )
1644 AND decode(p_customer_site_use_id,
1645 NULL, ps.customer_site_use_id,
1646 p_customer_site_use_id) = ps.customer_site_use_id
1647 AND decode(upper(p_currency_code),
1648 NULL, ps.invoice_currency_code,
1649 upper(p_currency_code)) = ps.invoice_currency_code
1650 --AND ps.due_date <= p_as_of_date --Commented for bug#7044352 on 23-May-2008 by SCHEKURI
1651 AND nvl( ps.amount_in_dispute, 0 ) <> 0
1652 -- bug 13092458 and ps.amount_due_remaining > 0 -- fixed a bug 5473635
1653 and ps.status = 'OP' -- fixed a bug 5569664
1654 AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y' ;
1655
1656
1657
1658
1659 BEGIN
1660 -- IF PG_DEBUG < 10 THEN
1661 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1662 IEX_DEBUG_PUB.LogMessage('calc_dispute: ' || l_line);
1663 END IF;
1664 -- IF PG_DEBUG < 10 THEN
1665 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1666 IEX_DEBUG_PUB.LogMessage
1667 ('IEX_AGING_BUCKETS_PKG.CALC_DISPUTE --->> Start <<--- ');
1668 END IF;
1669 -- IF PG_DEBUG < 10 THEN
1670 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1671 IEX_DEBUG_PUB.LogMessage('calc_dispute: ' || l_line);
1672 END IF;
1673
1674 p_dispute := 0;
1675
1676 If p_filter_mode = 'PARTY' then
1677 IF NVL(p_using_paying_rel, 'N') = 'Y' THEN
1678 OPEN party_paying_dispute;
1679 FETCH party_paying_dispute INTO p_dispute;
1680 Close party_paying_dispute ;
1681 ELSE
1682 OPEN party_dispute;
1683 FETCH party_dispute INTO p_dispute;
1684 Close party_dispute ;
1685 END IF;
1686 elsif p_filter_mode = 'CUST' then
1687 OPEN cust_dispute;
1688 FETCH cust_dispute INTO p_dispute;
1689 Close cust_dispute ;
1690 elsif p_filter_mode = 'BILLTO' then
1691 OPEN cust_site_dispute;
1692 FETCH cust_site_dispute INTO p_dispute;
1693 Close cust_site_dispute ;
1694 End If ;
1695 --
1696 -- IF PG_DEBUG < 10 THEN
1697 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1698 IEX_DEBUG_PUB.LogMessage('calc_dispute: ' || l_line);
1699 END IF;
1700 -- IF PG_DEBUG < 10 THEN
1701 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1702 IEX_DEBUG_PUB.LogMessage
1703 ('IEX_AGING_BUCKETS_PKG.CALC_dispute --->> End <<--- ');
1704 END IF;
1705 -- IF PG_DEBUG < 10 THEN
1706 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1707 IEX_DEBUG_PUB.LogMessage('calc_dispute: ' || l_line);
1708 END IF;
1709
1710 EXCEPTION
1711 WHEN OTHERS THEN
1712 -- IF PG_DEBUG < 10 THEN
1713 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1714 iex_debug_pub.logmessage('EXCEPTION: Iex_Aging_Buckets_Pkg.calc_dispute');
1715 END IF;
1716 -- IF PG_DEBUG < 10 THEN
1717 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1718 iex_debug_pub.logmessage('calc_dispute: ' || SQLCODE || ' ---> ' || SQLERRM);
1719 END IF;
1720 if cust_dispute%ISOPEN then
1721 CLOSE cust_dispute ;
1722 End If ;
1723 if party_dispute%ISOPEN then
1724 CLOSE party_dispute ;
1725 End If ;
1726
1727 -- IF PG_DEBUG < 10 THEN
1728 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1729 IEX_DEBUG_PUB.LogMessage('calc_dispute: ' || l_line);
1730 END IF;
1731 -- IF PG_DEBUG < 10 THEN
1732 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1733 IEX_DEBUG_PUB.LogMessage
1734 ('IEX_AGING_BUCKETS_PKG.CALC_dispute --->> End with Exception <<--- ');
1735 END IF;
1736 -- IF PG_DEBUG < 10 THEN
1737 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1738 IEX_DEBUG_PUB.LogMessage('calc_dispute: ' || l_line);
1739 END IF;
1740 END calc_dispute;
1741 --
1742 --
1743 --
1744
1745 PROCEDURE calc_adj_fin_charges(
1746 p_filter_mode IN Varchar2,
1747 p_filter_id IN NUMBER,
1748 p_customer_site_use_id IN NUMBER,
1749 p_as_of_date IN DATE,
1750 p_currency_code IN VARCHAR2,
1751 p_ps_max_id IN NUMBER,
1752 p_using_paying_rel IN VARCHAR2,
1753 p_adj OUT NOCOPY NUMBER,
1754 p_pending_adj OUT NOCOPY NUMBER,
1755 p_fin_charges OUT NOCOPY NUMBER
1756 )
1757 IS
1758
1759 CURSOR cust_adj IS
1760 SELECT
1761 ROUND(NVL(SUM(ps.amount_adjusted *
1762 NVL(ps.exchange_rate, 1)),0), l_round_decimal),
1763 ROUND(NVL(SUM(ps.amount_adjusted_pending *
1764 NVL(ps.exchange_rate, 1)),0), l_round_decimal)
1765 FROM ar_payment_schedules ps
1766 WHERE ps.customer_id = p_filter_id
1767 AND decode(p_customer_site_use_id,
1768 NULL, ps.customer_site_use_id,
1769 p_customer_site_use_id) = ps.customer_site_use_id
1770 AND decode(upper(p_currency_code),
1771 NULL, ps.invoice_currency_code,
1772 upper(p_currency_code)) = ps.invoice_currency_code
1773 -- AND ps.due_date <= p_as_of_date commented for bug#7418862 by PNAVEENK on 21-OCT-2008
1774 AND (nvl( ps.amount_adjusted_pending, 0 ) <> 0
1775 OR
1776 nvl( ps.amount_adjusted, 0 ) <> 0)
1777 and ps.status = 'OP' -- fixed a bug 5569664
1778 AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y';
1779
1780 CURSOR party_adj IS
1781 SELECT
1782 ROUND(NVL(SUM(ps.amount_adjusted *
1783 NVL(ps.exchange_rate, 1)),0), l_round_decimal),
1784 ROUND(NVL(SUM(ps.amount_adjusted_pending *
1785 NVL(ps.exchange_rate, 1)),0), l_round_decimal)
1786 FROM ar_payment_schedules ps,
1787 hz_cust_accounts hzca
1788 WHERE ps.customer_id = hzca.cust_account_id
1789 AND hzca.party_id = p_filter_id
1790 AND decode(p_customer_site_use_id,
1791 NULL, ps.customer_site_use_id,
1792 p_customer_site_use_id) = ps.customer_site_use_id
1793 AND decode(upper(p_currency_code),
1794 NULL, ps.invoice_currency_code,
1795 upper(p_currency_code)) = ps.invoice_currency_code
1796 -- AND ps.due_date <= p_as_of_date commented for bug#7418862 by PNAVEENK on 21-OCT-2008
1797 AND (nvl( ps.amount_adjusted_pending, 0 ) <> 0
1798 OR
1799 nvl( ps.amount_adjusted, 0 ) <> 0)
1800 and ps.status = 'OP' -- fixed a bug 5569664
1801 AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y';
1802
1803 CURSOR party_paying_adj IS
1804 SELECT
1805 ROUND(NVL(SUM(ps.amount_adjusted *
1806 NVL(ps.exchange_rate, 1)),0), l_round_decimal),
1807 ROUND(NVL(SUM(ps.amount_adjusted_pending *
1808 NVL(ps.exchange_rate, 1)),0), l_round_decimal)
1809 FROM ar_payment_schedules ps,
1810 hz_cust_accounts hzca
1811 WHERE ps.customer_id = hzca.cust_account_id
1812 AND hzca.party_id IN
1813 (SELECT p_filter_id FROM dual
1814 UNION
1815 SELECT ar.related_party_id
1816 FROM ar_paying_relationships_v ar
1817 WHERE ar.party_id = p_filter_id
1818 AND TRUNC(sysdate) BETWEEN
1819 TRUNC(NVL(ar.effective_start_date,sysdate)) AND
1820 TRUNC(NVL(ar.effective_end_date,sysdate)) )
1821 AND decode(p_customer_site_use_id,
1822 NULL, ps.customer_site_use_id,
1823 p_customer_site_use_id) = ps.customer_site_use_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 ps.due_date <= p_as_of_date commented for bug#7418862 by PNAVEENK on 21-OCT-2008
1828 AND (nvl( ps.amount_adjusted_pending, 0 ) <> 0
1829 OR
1830 nvl( ps.amount_adjusted, 0 ) <> 0)
1831 and ps.status = 'OP' -- fixed a bug 5569664
1832 AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y';
1833
1834
1835 CURSOR cust_site_adj IS
1836 SELECT
1837 ROUND(NVL(SUM(ps.amount_adjusted *
1838 NVL(ps.exchange_rate, 1)),0), l_round_decimal),
1839 ROUND(NVL(SUM(ps.amount_adjusted_pending *
1840 NVL(ps.exchange_rate, 1)),0), l_round_decimal)
1841 FROM ar_payment_schedules ps
1842 WHERE ps.customer_site_use_id = p_filter_id
1843 --- Begin - Andre Araujo - 11/09/2004 - Performance fix
1844 AND ps.status = 'OP'
1845 --- End - Andre Araujo - 11/09/2004 - Performance fix
1846 AND decode(upper(p_currency_code),
1847 NULL, ps.invoice_currency_code,
1848 upper(p_currency_code)) = ps.invoice_currency_code
1849 -- AND ps.due_date <= p_as_of_date commented for bug#7418862 by PNAVEENK on 21-OCT-2008
1850 AND (nvl( ps.amount_adjusted_pending, 0 ) <> 0
1851 OR
1852 nvl( ps.amount_adjusted, 0 ) <> 0)
1853 and ps.status = 'OP' -- fixed a bug 5569664
1854 AND nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y';
1855
1856
1857 CURSOR cust_fin_charges IS
1858 SELECT
1859 ROUND(NVL(SUM( ps.receivables_charges_charged
1860 * NVL(ps.exchange_rate,1)),0),l_round_decimal)
1861 FROM ar_payment_schedules ps
1862 WHERE ps.customer_id = p_filter_id
1863 AND decode(p_customer_site_use_id,
1864 NULL, ps.customer_site_use_id,
1865 p_customer_site_use_id) = ps.customer_site_use_id
1866 AND decode(upper(p_currency_code),
1867 NULL, ps.invoice_currency_code,
1868 upper(p_currency_code)) = ps.invoice_currency_code
1869 -- AND ps.due_date <= p_as_of_date commented for bug#7418916 by PNAVEENK on 20-OCT-2008
1870 --- Begin - Andre Araujo - 11/09/2004 - Performance fix
1871 AND ps.status = 'OP'
1872 --- End - Andre Araujo - 11/09/2004 - Performance fix
1873 AND nvl(ps.receivables_charges_charged, 0 ) <> 0 ;
1874
1875 CURSOR party_fin_charges IS
1876 SELECT
1877 ROUND(NVL(SUM( ps.receivables_charges_charged
1878 * NVL(ps.exchange_rate,1)),0),l_round_decimal)
1879 FROM ar_payment_schedules ps,
1880 hz_cust_accounts hzca
1881 WHERE ps.customer_id = hzca.cust_account_id
1882 --- Begin - Andre Araujo - 11/09/2004 - Performance fix
1883 AND ps.status = 'OP'
1884 --- End - Andre Araujo - 11/09/2004 - Performance fix
1885 AND hzca.party_id = p_filter_id
1886 AND decode(p_customer_site_use_id,
1887 NULL, ps.customer_site_use_id,
1888 p_customer_site_use_id) = ps.customer_site_use_id
1889 AND decode(upper(p_currency_code),
1890 NULL, ps.invoice_currency_code,
1891 upper(p_currency_code)) = ps.invoice_currency_code
1892 -- AND ps.due_date <= p_as_of_date commented for bug#7418916 by PNAVEENK on 20-OCT-2008
1893 AND nvl(ps.receivables_charges_charged, 0 ) <> 0 ;
1894
1895 CURSOR party_paying_fin_charges IS
1896 SELECT
1897 ROUND(NVL(SUM( ps.receivables_charges_charged
1898 * NVL(ps.exchange_rate,1)),0),l_round_decimal)
1899 FROM ar_payment_schedules ps,
1900 hz_cust_accounts hzca
1901 WHERE ps.customer_id = hzca.cust_account_id
1902 AND hzca.party_id IN
1903 (SELECT p_filter_id FROM dual
1904 UNION
1905 SELECT ar.related_party_id
1906 FROM ar_paying_relationships_v ar
1907 WHERE ar.party_id = p_filter_id
1908 AND TRUNC(sysdate) BETWEEN
1909 TRUNC(NVL(ar.effective_start_date,sysdate)) AND
1910 TRUNC(NVL(ar.effective_end_date,sysdate)) )
1911 AND decode(p_customer_site_use_id,
1912 NULL, ps.customer_site_use_id,
1913 p_customer_site_use_id) = ps.customer_site_use_id
1914 AND decode(upper(p_currency_code),
1915 NULL, ps.invoice_currency_code,
1916 upper(p_currency_code)) = ps.invoice_currency_code
1917 --- Begin - Andre Araujo - 11/09/2004 - Performance fix
1918 AND ps.status = 'OP'
1919 --- End - Andre Araujo - 11/09/2004 - Performance fix
1920 -- AND ps.due_date <= p_as_of_date commented for bug#7418916 by PNAVEENK on 20-OCT-2008
1921 AND nvl(ps.receivables_charges_charged, 0 ) <> 0 ;
1922
1923 CURSOR cust_site_fin_charges IS
1924 SELECT
1925 ROUND(NVL(SUM( ps.receivables_charges_charged
1926 * NVL(ps.exchange_rate,1)),0),l_round_decimal)
1927 FROM ar_payment_schedules ps
1928 WHERE ps.customer_site_use_id = p_filter_id
1929 AND decode(upper(p_currency_code),
1930 NULL, ps.invoice_currency_code,
1931 upper(p_currency_code)) = ps.invoice_currency_code
1932 --- Begin - Andre Araujo - 11/09/2004 - Performance fix
1933 AND ps.status = 'OP'
1934 --- End - Andre Araujo - 11/09/2004 - Performance fix
1935 -- AND ps.due_date <= p_as_of_date commented for bug#7418916 by PNAVEENK on 20-OCT-2008
1936 AND nvl(ps.receivables_charges_charged, 0 ) <> 0 ;
1937
1938 BEGIN
1939
1940 -- IF PG_DEBUG < 10 THEN
1941 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1942 IEX_DEBUG_PUB.LogMessage('calc_adj_fin_charges: ' || l_line);
1943 END IF;
1944 -- IF PG_DEBUG < 10 THEN
1945 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1946 IEX_DEBUG_PUB.LogMessage
1947 ('calc_adj_fin_charges: ' || 'IEX_AGING_BUCKETS_PKG.CALC_PENDING_ADJ --->> Start <<--- ');
1948 END IF;
1949 -- IF PG_DEBUG < 10 THEN
1950 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1951 IEX_DEBUG_PUB.LogMessage('calc_adj_fin_charges: ' || l_line);
1952 END IF;
1953
1954 p_pending_adj := 0;
1955
1956 If p_filter_mode = 'PARTY' then
1957 IF NVL(p_using_paying_rel, 'N') = 'Y' THEN
1958 OPEN party_paying_adj;
1959 FETCH party_paying_adj
1960 INTO p_adj,
1961 p_pending_adj ;
1962 Close party_paying_adj ;
1963
1964 OPEN party_paying_fin_charges;
1965 FETCH party_paying_fin_charges
1966 INTO p_fin_charges ;
1967 Close party_paying_fin_charges ;
1968 ELSE
1969 OPEN party_adj;
1970 FETCH party_adj
1971 INTO p_adj,
1972 p_pending_adj ;
1973 Close party_adj ;
1974
1975 OPEN party_fin_charges;
1976 FETCH party_fin_charges
1977 INTO p_fin_charges ;
1978 Close party_fin_charges ;
1979 END IF;
1980
1981 elsif p_filter_mode = 'CUST' then
1982 OPEN cust_adj;
1983 FETCH cust_adj
1984 INTO p_adj,
1985 p_pending_adj ;
1986 Close cust_adj ;
1987
1988 OPEN cust_fin_charges;
1989 FETCH cust_fin_charges
1990 INTO p_fin_charges ;
1991 Close cust_fin_charges ;
1992 elsif p_filter_mode = 'BILLTO' then
1993 OPEN cust_site_adj;
1994 FETCH cust_site_adj
1995 INTO p_adj,
1996 p_pending_adj ;
1997 Close cust_site_adj ;
1998
1999 OPEN cust_site_fin_charges;
2000 FETCH cust_site_fin_charges
2001 INTO p_fin_charges ;
2002 Close cust_site_fin_charges ;
2003
2004 End If ;
2005 --
2006 -- IF PG_DEBUG < 10 THEN
2007 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2008 IEX_DEBUG_PUB.LogMessage('calc_adj_fin_charges: ' || l_line);
2009 END IF;
2010 -- IF PG_DEBUG < 10 THEN
2011 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2012 IEX_DEBUG_PUB.LogMessage
2013 ('calc_adj_fin_charges: ' || 'IEX_AGING_BUCKETS_PKG.CALC_pending_adj --->> End <<--- ');
2014 END IF;
2015 -- IF PG_DEBUG < 10 THEN
2016 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2017 IEX_DEBUG_PUB.LogMessage('calc_adj_fin_charges: ' || l_line);
2018 END IF;
2019
2020 EXCEPTION
2021 WHEN OTHERS THEN
2022 -- IF PG_DEBUG < 10 THEN
2023 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2024 iex_debug_pub.logmessage('calc_adj_fin_charges: ' || 'EXCEPTION: Iex_Aging_Buckets_Pkg.calc_pending_adj');
2025 END IF;
2026 -- IF PG_DEBUG < 10 THEN
2027 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2028 iex_debug_pub.logmessage('calc_adj_fin_charges: ' || SQLCODE || ' ---> ' || SQLERRM);
2029 END IF;
2030
2031 if cust_adj%ISOPEN then
2032 CLOSE cust_adj ;
2033 End If ;
2034 if party_adj%ISOPEN then
2035 CLOSE party_adj ;
2036 End If ;
2037
2038 if cust_fin_charges%ISOPEN then
2039 CLOSE cust_fin_charges ;
2040 End If ;
2041 if party_fin_charges%ISOPEN then
2042 CLOSE party_fin_charges ;
2043 End If ;
2044
2045
2046
2047 -- IF PG_DEBUG < 10 THEN
2048 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2049 IEX_DEBUG_PUB.LogMessage('calc_adj_fin_charges: ' || l_line);
2050 END IF;
2051 -- IF PG_DEBUG < 10 THEN
2052 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2053 IEX_DEBUG_PUB.LogMessage
2054 ('calc_adj_fin_charges: ' || 'IEX_AGING_BUCKETS_PKG.CALC_pending_adj --->> End with Exception <<--- ');
2055 END IF;
2056 -- IF PG_DEBUG < 10 THEN
2057 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2058 IEX_DEBUG_PUB.LogMessage('calc_adj_fin_charges: ' || l_line);
2059 END IF;
2060
2061 END calc_adj_fin_charges;
2062
2063
2064
2065 /*------------------------------------------------------------------------
2066 PROCEDURE QUERY_AGING_LINES
2067 Main Procedure to list Aging Bucket Lines. This Calls the Procedure
2068 CALC_AGING_BUCKETS to get the bucket level amounts. This Procedure
2069 is Called from the Aging Tab for IEXAGTAB to populate the aging grid
2070 ------------------------------------------------------------------------*/
2071 PROCEDURE QUERY_AGING_LINES
2072 (p_api_version IN NUMBER := 1.0,
2073 p_init_msg_list IN VARCHAR2,
2074 p_commit IN VARCHAR2,
2075 p_validation_level IN NUMBER,
2076 x_return_status IN OUT NOCOPY VARCHAR2,
2077 x_msg_count IN OUT NOCOPY NUMBER,
2078 x_msg_data IN OUT NOCOPY VARCHAR2,
2079 p_filter_mode IN Varchar2,
2080 p_filter_id IN Number,
2081 p_customer_site_use_id IN Number,
2082 p_bucket_id IN Number ,
2083 p_credit_option IN Varchar2,
2084 p_using_paying_rel IN VARCHAR2,
2085 x_bucket_lines_tbl IN OUT NOCOPY bucket_lines_tbl )
2086 IS
2087 l_api_version CONSTANT NUMBER := 1.0;
2088 l_api_name CONSTANT VARCHAR2(30) := 'Query_Aging_Lines';
2089 l_return_status VARCHAR2(1);
2090 l_msg_count NUMBER;
2091 l_msg_data VARCHAR2(32767);
2092 l_cnt Number := 1 ;
2093 v_aging_summary_cur Profile_cur ;
2094 v_aging_summary_sql Varchar2(5000) ;
2095 v_aging_summary_select_rec Aging_Summary_Select_Rec ;
2096 v_aging_bucket_line_id Number ;
2097 v_dispute_count Number ;
2098
2099 v_currency Varchar2(10) ;
2100 v_msg_data Varchar2(100) ;
2101 v_return_status Varchar2(100) ;
2102 v_msg_count Number ;
2103
2104 -- Added by Surya on 7/30/03
2105 -- This cust account is derived only in case of filter mode bill to
2106 v_billto_cust_account_id Number ;
2107
2108 -- These contains either above found cust_account_id or the bill to id
2109 -- based on the filter_mode
2110 v_filter_id1 Number ;
2111 v_filter_id2 Number ;
2112
2113 ------------------------------------------------------------------
2114 -- API Main Parameter Variables
2115 ------------------------------------------------------------------
2116 l_outstanding_balance NUMBER := 0 ;
2117
2118 l_bucket_line_id_0 NUMBER ;
2119 l_bucket_seq_num_0 NUMBER ;
2120 l_bucket_titletop_0 Varchar2(15) ;
2121 l_bucket_titlebottom_0 Varchar2(15) ;
2122 l_bucket_amount_0 NUMBER := 0 ;
2123
2124 l_bucket_line_id_1 NUMBER ;
2125 l_bucket_seq_num_1 NUMBER ;
2126 l_bucket_titletop_1 Varchar2(15) ;
2127 l_bucket_titlebottom_1 Varchar2(15) ;
2128 l_bucket_amount_1 NUMBER := 0 ;
2129
2130 l_bucket_line_id_2 NUMBER ;
2131 l_bucket_seq_num_2 NUMBER ;
2132 l_bucket_titletop_2 Varchar2(15) ;
2133 l_bucket_titlebottom_2 Varchar2(15) ;
2134 l_bucket_amount_2 NUMBER := 0 ;
2135
2136 l_bucket_line_id_3 NUMBER ;
2137 l_bucket_seq_num_3 NUMBER ;
2138 l_bucket_titletop_3 Varchar2(15) ;
2139 l_bucket_titlebottom_3 Varchar2(15) ;
2140 l_bucket_amount_3 NUMBER := 0 ;
2141
2142 l_bucket_line_id_4 NUMBER ;
2143 l_bucket_seq_num_4 NUMBER ;
2144 l_bucket_titletop_4 Varchar2(15) ;
2145 l_bucket_titlebottom_4 Varchar2(15) ;
2146 l_bucket_amount_4 NUMBER := 0 ;
2147
2148 l_bucket_line_id_5 NUMBER ;
2149 l_bucket_seq_num_5 NUMBER ;
2150 l_bucket_titletop_5 Varchar2(15) ;
2151 l_bucket_titlebottom_5 Varchar2(15) ;
2152 l_bucket_amount_5 NUMBER := 0 ;
2153
2154 l_bucket_line_id_6 NUMBER ;
2155 l_bucket_seq_num_6 NUMBER ;
2156 l_bucket_titletop_6 Varchar2(15) ;
2157 l_bucket_titlebottom_6 Varchar2(15) ;
2158 l_bucket_amount_6 NUMBER := 0 ;
2159
2160
2161
2162 -- Collectible Amount Variables
2163 TYPE collectible_bkt_id_tbl IS TABLE OF Number Index By Binary_Integer;
2164 TYPE collectible_bkt_amt_tbl IS TABLE OF Number Index By Binary_Integer;
2165 Bkt_cnt Number := 0 ;
2166
2167 l_collectible_bkt_id_tbl collectible_bkt_id_tbl ;
2168 l_collectible_bkt_amt_tbl collectible_bkt_amt_tbl ;
2169
2170 l_collect_dispute_amt number; --Added for bug 6701396 gnramasa 4th Mar 08
2171
2172
2173 -- Grid Total Variables
2174 -- ====================
2175 l_total_rec Number := 0 ;
2176 l_total_amount Number := 0 ;
2177 l_total_invoices Number := 0 ;
2178 l_total_inv_amount Number := 0 ;
2179 l_total_coll_amount Number := 0 ;
2180 l_total_chargebacks Number := 0 ;
2181 l_total_cb_amount Number := 0 ;
2182 l_total_debit_memos Number := 0 ;
2183 l_total_dm_amount Number := 0 ;
2184 l_total_disputes Number := 0 ;
2185 l_total_cnsld_invoices Number := 0 ;
2186 l_total_disp_amount Number := 0 ;
2187
2188 ------------------------------------------------------------------
2189 -- API Temporary Parameter Variables(Used only in party mode)
2190 ------------------------------------------------------------------
2191 lt_outstanding_balance NUMBER ;
2192
2193 lt_bucket_line_id_0 NUMBER ;
2194 lt_bucket_seq_num_0 NUMBER ;
2195 lt_bucket_titletop_0 Varchar2(15) ;
2196 lt_bucket_titlebottom_0 Varchar2(15) ;
2197 lt_bucket_amount_0 NUMBER ;
2198
2199 lt_bucket_line_id_1 NUMBER ;
2200 lt_bucket_seq_num_1 NUMBER ;
2201 lt_bucket_titletop_1 Varchar2(15) ;
2202 lt_bucket_titlebottom_1 Varchar2(15) ;
2203 lt_bucket_amount_1 NUMBER ;
2204
2205 lt_bucket_line_id_2 NUMBER ;
2206 lt_bucket_seq_num_2 NUMBER ;
2207 lt_bucket_titletop_2 Varchar2(15) ;
2208 lt_bucket_titlebottom_2 Varchar2(15) ;
2209 lt_bucket_amount_2 NUMBER ;
2210
2211 lt_bucket_line_id_3 NUMBER ;
2212 lt_bucket_seq_num_3 NUMBER ;
2213 lt_bucket_titletop_3 Varchar2(15) ;
2214 lt_bucket_titlebottom_3 Varchar2(15) ;
2215 lt_bucket_amount_3 NUMBER ;
2216
2217 lt_bucket_line_id_4 NUMBER ;
2218 lt_bucket_seq_num_4 NUMBER ;
2219 lt_bucket_titletop_4 Varchar2(15) ;
2220 lt_bucket_titlebottom_4 Varchar2(15) ;
2221 lt_bucket_amount_4 NUMBER ;
2222
2223 lt_bucket_line_id_5 NUMBER ;
2224 lt_bucket_seq_num_5 NUMBER ;
2225 lt_bucket_titletop_5 Varchar2(15) ;
2226 lt_bucket_titlebottom_5 Varchar2(15) ;
2227 lt_bucket_amount_5 NUMBER ;
2228
2229 lt_bucket_line_id_6 NUMBER ;
2230 lt_bucket_seq_num_6 NUMBER ;
2231 lt_bucket_titletop_6 Varchar2(15) ;
2232 lt_bucket_titlebottom_6 Varchar2(15) ;
2233 lt_bucket_amount_6 NUMBER ;
2234
2235
2236 l_count Number := 1 ;
2237
2238 -- Used to store fetched cust_account_ids for the passed party_id
2239 l_customer_id Number ;
2240
2241 -- Dummy declaration to get consolidated invoice amount. Amount not
2242 -- derived at this time. Will be implemented later, if required
2243 l_cons_amount Number := 0 ;
2244 Begin
2245 -- IF PG_DEBUG < 10 THEN
2246 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2247 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || l_line);
2248 END IF;
2249 -- IF PG_DEBUG < 10 THEN
2250 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2251 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES --->> Start <<--- ');
2252 END IF;
2253 -- IF PG_DEBUG < 10 THEN
2254 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2255 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || l_line);
2256 END IF;
2257
2258
2259 SAVEPOINT Query_Aging_lines;
2260
2261 -- Standard call to check for call compatibility.
2262 IF NOT FND_API.Compatible_API_Call (l_api_version,
2263 p_api_version,
2264 l_api_name,
2265 G_PKG_NAME) THEN
2266 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2267 END IF;
2268
2269 -- Check p_init_msg_list
2270 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2271 FND_MSG_PUB.initialize;
2272 END IF;
2273
2274
2275 -- Sysdate-1 is passed to follow AR API aging calculation.
2276 -- Modified by Surya on 02/03/03. Bug # 2754557
2277 Select TRUNC(sysdate)
2278 into l_date
2279 from dual ;
2280
2281
2282 -- IF PG_DEBUG < 10 THEN
2283 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2284 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || 'Bucket Id >> ' || p_bucket_id );
2285 END IF;
2286
2287 x_return_status := FND_API.G_RET_STS_SUCCESS;
2288
2289 IEX_CURRENCY_PVT.GET_FUNCT_CURR(
2290 P_API_VERSION =>1.0,
2291 p_init_msg_list => 'T',
2292 p_commit => 'F',
2293 p_validation_level => 100,
2294 X_Functional_currency => v_currency,
2295 X_return_status => v_return_status,
2296 X_MSG_COUNT => v_msg_count,
2297 X_MSG_DATA => v_msg_data );
2298
2299 -- IF PG_DEBUG < 10 THEN
2300 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2301 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || 'Functional Currency >> '|| v_currency ||
2302 ' Filter Mode ' || p_filter_mode);
2303 END IF;
2304
2305
2306
2307 if p_filter_mode IN ('CUST', 'BILLTO') then
2308
2309 -- IF PG_DEBUG < 10 THEN
2310 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2311 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || 'Filter Mode >> '||
2312 p_filter_mode || ' Start');
2313 END IF;
2314
2315 if p_filter_mode = 'BILLTO' then
2316 Begin
2317 select DISTINCT aps.customer_id
2318 into v_billto_cust_account_id
2319 from ar_payment_schedules aps
2320 where aps.customer_site_use_id = p_filter_id ;
2321
2322 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2323 iex_debug_pub.logmessage('Cust Account id for bill to >> '
2324 || v_billto_cust_account_id);
2325 END IF;
2326
2327 Exception
2328 WHEN NO_DATA_FOUND THEN
2329 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2330 iex_debug_pub.logmessage('ERROR >> No Customer Account
2331 for the Passed Bill to id >>' || p_filter_id);
2332 END IF;
2333 WHEN OTHERS then
2334 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2335 iex_debug_pub.logmessage('ERROR >> When deriving customer
2336 account for the Passed Bill to id >>' || p_filter_id ||
2337 SQLCODE || SQLERRM);
2338 END IF;
2339 End ;
2340 v_filter_id1 := v_billto_cust_account_id ;
2341 v_filter_id2 := p_filter_id ;
2342 else
2343 v_filter_id1 := p_filter_id ;
2344 v_filter_id2 := NULL ;
2345 End If ;
2346
2347
2348 IEX_AGING_BUCKETS_PKG.calc_aging_buckets (
2349 p_customer_id => v_filter_id1 ,
2350 p_customer_site_use_id => v_filter_id2, -- NULL, changed by ehuh for 11591
2351 p_as_of_date => l_date ,
2352 p_currency_code => NULL,
2353 p_credit_option => p_credit_option ,
2354 p_invoice_type_low => NULL,
2355 p_invoice_type_high => NULL,
2356 p_ps_max_id => NULL,
2357 p_app_max_id => NULL,
2358 p_bucket_id => p_bucket_id ,
2359 p_outstanding_balance => l_outstanding_balance,
2360 p_bucket_line_id_0 => l_bucket_line_id_0,
2361 p_bucket_seq_num_0 => l_bucket_seq_num_0,
2362 p_bucket_titletop_0 => l_bucket_titletop_0 ,
2363 p_bucket_titlebottom_0 => l_bucket_titlebottom_0,
2364 p_bucket_amount_0 => l_bucket_amount_0 ,
2365 p_bucket_line_id_1 => l_bucket_line_id_1,
2366 p_bucket_seq_num_1 => l_bucket_seq_num_1,
2367 p_bucket_titletop_1 => l_bucket_titletop_1 ,
2368 p_bucket_titlebottom_1 => l_bucket_titlebottom_1,
2369 p_bucket_amount_1 => l_bucket_amount_1 ,
2370 p_bucket_line_id_2 => l_bucket_line_id_2,
2371 p_bucket_seq_num_2 => l_bucket_seq_num_2,
2372 p_bucket_titletop_2 => l_bucket_titletop_2 ,
2373 p_bucket_titlebottom_2 => l_bucket_titlebottom_2,
2374 p_bucket_amount_2 => l_bucket_amount_2 ,
2375 p_bucket_line_id_3 => l_bucket_line_id_3,
2376 p_bucket_seq_num_3 => l_bucket_seq_num_3,
2377 p_bucket_titletop_3 => l_bucket_titletop_3 ,
2378 p_bucket_titlebottom_3 => l_bucket_titlebottom_3,
2379 p_bucket_amount_3 => l_bucket_amount_3 ,
2380 p_bucket_line_id_4 => l_bucket_line_id_4,
2381 p_bucket_seq_num_4 => l_bucket_seq_num_4,
2382 p_bucket_titletop_4 => l_bucket_titletop_4 ,
2383 p_bucket_titlebottom_4 => l_bucket_titlebottom_4,
2384 p_bucket_amount_4 => l_bucket_amount_4 ,
2385 p_bucket_line_id_5 => l_bucket_line_id_5,
2386 p_bucket_seq_num_5 => l_bucket_seq_num_5,
2387 p_bucket_titletop_5 => l_bucket_titletop_5 ,
2388 p_bucket_titlebottom_5 => l_bucket_titlebottom_5,
2389 p_bucket_amount_5 => l_bucket_amount_5 ,
2390 p_bucket_line_id_6 => l_bucket_line_id_6,
2391 p_bucket_seq_num_6 => l_bucket_seq_num_6,
2392 p_bucket_titletop_6 => l_bucket_titletop_6 ,
2393 p_bucket_titlebottom_6 => l_bucket_titlebottom_6,
2394 p_bucket_amount_6 => l_bucket_amount_6 ) ;
2395
2396
2397 elsif p_filter_mode = 'PARTY' then
2398 Begin
2399 IF NVL(p_using_paying_rel, 'N') = 'Y' THEN
2400 If AR_AGING_PAYING_PARTY_CUR%ISOPEN = false then
2401 OPEN AR_AGING_PAYING_PARTY_CUR(p_filter_id) ;
2402 End If ;
2403 ELSE
2404 If AR_AGING_PARTY_CUR%ISOPEN = false then
2405 OPEN AR_AGING_PARTY_CUR(p_filter_id) ;
2406 End If ;
2407 END IF;
2408
2409 LOOP
2410 IF NVL(p_using_paying_rel, 'N') = 'Y' THEN
2411 FETCH AR_AGING_PAYING_PARTY_CUR
2412 INTO l_customer_id ;
2413 ELSE
2414 FETCH AR_AGING_PARTY_CUR
2415 INTO l_customer_id ;
2416 END IF;
2417 -- IF PG_DEBUG < 10 THEN
2418 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2419 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || 'Party Mode Fetch Iteration >> '
2420 || to_char(l_count) || ' Filter Id '||
2421 to_char(p_filter_id));
2422 END IF;
2423 l_count := l_count + 1 ;
2424
2425 IF NVL(p_using_paying_rel, 'N') = 'Y' THEN
2426 EXIT WHEN AR_AGING_PAYING_PARTY_CUR%NOTFOUND ;
2427 ELSE
2428 EXIT WHEN AR_AGING_PARTY_CUR%NOTFOUND ;
2429 END IF;
2430
2431 -- IF PG_DEBUG < 10 THEN
2432 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2433 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || 'Mode Party >> Customer >> '
2434 || l_customer_id );
2435 END IF;
2436
2437 lt_outstanding_balance := 0 ;
2438 -- Call the Aging Procedure for each fetched customer_id
2439 -- IF PG_DEBUG < 10 THEN
2440 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2441 IEX_DEBUG_PUB.LogMessage
2442 ('QUERY_AGING_LINES: ' || 'Before Calling calc aging buckets >> Parameters >>');
2443 END IF;
2444 -- IF PG_DEBUG < 10 THEN
2445 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2446 IEX_DEBUG_PUB.LogMessage
2447 ('QUERY_AGING_LINES: ' || ' Bucket Id > '|| p_bucket_id
2448 || 'Customer Id > ' || to_char(l_customer_id));
2449 END IF;
2450
2451 IEX_AGING_BUCKETS_PKG.calc_aging_buckets (
2452 p_customer_id => l_customer_id ,
2453 p_customer_site_use_id => p_customer_site_use_id, -- NULL, changed by ehuh for 11591
2454 p_as_of_date => l_date ,
2455 p_currency_code => NULL,
2456 p_credit_option => p_credit_option ,
2457 p_invoice_type_low => NULL,
2458 p_invoice_type_high => NULL,
2459 p_ps_max_id => NULL,
2460 p_app_max_id => NULL,
2461 p_bucket_id => p_bucket_id ,
2462 p_outstanding_balance => lt_outstanding_balance,
2463 p_bucket_line_id_0 => lt_bucket_line_id_0,
2464 p_bucket_seq_num_0 => lt_bucket_seq_num_0,
2465 p_bucket_titletop_0 => lt_bucket_titletop_0 ,
2466 p_bucket_titlebottom_0 => lt_bucket_titlebottom_0,
2467 p_bucket_amount_0 => lt_bucket_amount_0 ,
2468 p_bucket_line_id_1 => lt_bucket_line_id_1,
2469 p_bucket_seq_num_1 => lt_bucket_seq_num_1,
2470 p_bucket_titletop_1 => lt_bucket_titletop_1 ,
2471 p_bucket_titlebottom_1 => lt_bucket_titlebottom_1,
2472 p_bucket_amount_1 => lt_bucket_amount_1 ,
2473 p_bucket_line_id_2 => lt_bucket_line_id_2,
2474 p_bucket_seq_num_2 => lt_bucket_seq_num_2,
2475 p_bucket_titletop_2 => lt_bucket_titletop_2 ,
2476 p_bucket_titlebottom_2 => lt_bucket_titlebottom_2,
2477 p_bucket_amount_2 => lt_bucket_amount_2 ,
2478 p_bucket_line_id_3 => lt_bucket_line_id_3,
2479 p_bucket_seq_num_3 => lt_bucket_seq_num_3,
2480 p_bucket_titletop_3 => lt_bucket_titletop_3 ,
2481 p_bucket_titlebottom_3 => lt_bucket_titlebottom_3,
2482 p_bucket_amount_3 => lt_bucket_amount_3 ,
2483 p_bucket_line_id_4 => lt_bucket_line_id_4,
2484 p_bucket_seq_num_4 => lt_bucket_seq_num_4,
2485 p_bucket_titletop_4 => lt_bucket_titletop_4 ,
2486 p_bucket_titlebottom_4 => lt_bucket_titlebottom_4,
2487 p_bucket_amount_4 => lt_bucket_amount_4 ,
2488 p_bucket_line_id_5 => lt_bucket_line_id_5,
2489 p_bucket_seq_num_5 => lt_bucket_seq_num_5,
2490 p_bucket_titletop_5 => lt_bucket_titletop_5 ,
2491 p_bucket_titlebottom_5 => lt_bucket_titlebottom_5,
2492 p_bucket_amount_5 => lt_bucket_amount_5 ,
2493 p_bucket_line_id_6 => lt_bucket_line_id_6,
2494 p_bucket_seq_num_6 => lt_bucket_seq_num_6,
2495 p_bucket_titletop_6 => lt_bucket_titletop_6 ,
2496 p_bucket_titlebottom_6 => lt_bucket_titlebottom_6,
2497 p_bucket_amount_6 => lt_bucket_amount_6 ) ;
2498
2499 -- Roll up all the derived Bucket Information for the
2500 -- Customer into main parameter variables. These
2501 -- act as common output for both party and cust modes
2502 -- when loading the final output table.
2503
2504 -- IF PG_DEBUG < 10 THEN
2505 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2506 IEX_DEBUG_PUB.LogMessage
2507 ('QUERY_AGING_LINES: ' || 'Acct Balance >> '|| to_char(lt_outstanding_balance));
2508 END IF;
2509
2510 l_outstanding_balance :=
2511 l_outstanding_balance + lt_outstanding_balance ;
2512
2513 -- IF PG_DEBUG < 10 THEN
2514 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2515 IEX_DEBUG_PUB.LogMessage
2516 ('QUERY_AGING_LINES: ' || 'Total Balance >> '|| to_char(l_outstanding_balance));
2517 END IF;
2518
2519 -- Rolling Bucket 0
2520 l_bucket_amount_0 :=
2521 l_bucket_amount_0 + lt_bucket_amount_0 ;
2522 if (l_bucket_titletop_0) is NULL then
2523 l_bucket_titletop_0 := lt_bucket_titletop_0 ;
2524 End IF ;
2525 if (l_bucket_titlebottom_0) is NULL then
2526 l_bucket_titlebottom_0 := lt_bucket_titlebottom_0 ;
2527 End IF ;
2528
2529 if (l_bucket_line_id_0) is NULL then
2530 l_bucket_line_id_0 := lt_bucket_line_id_0 ;
2531 End If ;
2532
2533 if (l_bucket_seq_num_0) is NULL then
2534 l_bucket_seq_num_0 := lt_bucket_seq_num_0 ;
2535 End If ;
2536
2537 -- IF PG_DEBUG < 10 THEN
2538 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2539 IEX_DEBUG_PUB.LogMessage
2540 ('QUERY_AGING_LINES: ' || 'Bucket 0 >> '||lt_bucket_titletop_0 || ' '
2541 || lt_bucket_titlebottom_0);
2542 END IF;
2543
2544 /*------------- Rolling Bucket 1 ------------*/
2545 l_bucket_amount_1 :=
2546 l_bucket_amount_1 + lt_bucket_amount_1 ;
2547 if (l_bucket_titletop_1) is NULL then
2548 l_bucket_titletop_1 := lt_bucket_titletop_1 ;
2549 End IF ;
2550 if (l_bucket_titlebottom_1) is NULL then
2551 l_bucket_titlebottom_1 := lt_bucket_titlebottom_1 ;
2552 End IF ;
2553 if (l_bucket_line_id_1) is NULL then
2554 l_bucket_line_id_1 := lt_bucket_line_id_1 ;
2555 End If ;
2556
2557 if (l_bucket_seq_num_1) is NULL then
2558 l_bucket_seq_num_1 := lt_bucket_seq_num_1 ;
2559 End If ;
2560 -- IF PG_DEBUG < 10 THEN
2561 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2562 IEX_DEBUG_PUB.LogMessage
2563 ('QUERY_AGING_LINES: ' || 'Bucket 1 >> '||lt_bucket_titletop_1 || ' '
2564 || lt_bucket_titlebottom_1);
2565 END IF;
2566
2567 /*------------- Rolling Bucket 2 ------------*/
2568 l_bucket_amount_2 :=
2569 l_bucket_amount_2 + lt_bucket_amount_2 ;
2570 if (l_bucket_titletop_2) is NULL then
2571 l_bucket_titletop_2 := lt_bucket_titletop_2 ;
2572 End IF ;
2573 if (l_bucket_titlebottom_2) is NULL then
2574 l_bucket_titlebottom_2 := lt_bucket_titlebottom_2 ;
2575 End IF ;
2576
2577 if (l_bucket_line_id_2) is NULL then
2578 l_bucket_line_id_2 := lt_bucket_line_id_2 ;
2579 End If ;
2580
2581 if (l_bucket_seq_num_2) is NULL then
2582 l_bucket_seq_num_2 := lt_bucket_seq_num_2 ;
2583 End If ;
2584
2585 /*------------- Rolling Bucket 3 ------------*/
2586 l_bucket_amount_3 :=
2587 l_bucket_amount_3 + lt_bucket_amount_3 ;
2588 if (l_bucket_titletop_3) is NULL then
2589 l_bucket_titletop_3 := lt_bucket_titletop_3 ;
2590 End IF ;
2591 if (l_bucket_titlebottom_3) is NULL then
2592 l_bucket_titlebottom_3 := lt_bucket_titlebottom_3 ;
2593 End IF ;
2594
2595 if (l_bucket_line_id_3) is NULL then
2596 l_bucket_line_id_3 := lt_bucket_line_id_3 ;
2597 End If ;
2598
2599 if (l_bucket_seq_num_3) is NULL then
2600 l_bucket_seq_num_3 := lt_bucket_seq_num_3 ;
2601 End If ;
2602 -- IF PG_DEBUG < 10 THEN
2603 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2604 IEX_DEBUG_PUB.LogMessage
2605 ('QUERY_AGING_LINES: ' || 'Bucket 3 >> '||lt_bucket_titletop_3 || ' '
2606 || lt_bucket_titlebottom_3);
2607 END IF;
2608
2609 /*------------- Rolling Bucket 4 ------------*/
2610 l_bucket_amount_4 :=
2611 l_bucket_amount_4 + lt_bucket_amount_4 ;
2612 if (l_bucket_titletop_4) is NULL then
2613 l_bucket_titletop_4 := lt_bucket_titletop_4 ;
2614 End IF ;
2615 if (l_bucket_titlebottom_4) is NULL then
2616 l_bucket_titlebottom_4 := lt_bucket_titlebottom_4 ;
2617 End IF ;
2618
2619 if (l_bucket_line_id_4) is NULL then
2620 l_bucket_line_id_4 := lt_bucket_line_id_4 ;
2621 End If ;
2622
2623 if (l_bucket_seq_num_4) is NULL then
2624 l_bucket_seq_num_4 := lt_bucket_seq_num_4 ;
2625 End If ;
2626
2627 /*------------- Rolling Bucket 5 ------------*/
2628 l_bucket_amount_5 :=
2629 l_bucket_amount_5 + lt_bucket_amount_5 ;
2630 if (l_bucket_titletop_5) is NULL then
2631 l_bucket_titletop_5 := lt_bucket_titletop_5 ;
2632 End IF ;
2633 if (l_bucket_titlebottom_5) is NULL then
2634 l_bucket_titlebottom_5 := lt_bucket_titlebottom_5 ;
2635 End IF ;
2636 if (l_bucket_line_id_5) is NULL then
2637 l_bucket_line_id_5 := lt_bucket_line_id_5 ;
2638 End If ;
2639
2640 if (l_bucket_seq_num_5) is NULL then
2641 l_bucket_seq_num_5 := lt_bucket_seq_num_5 ;
2642 End If ;
2643
2644 /*------------- Rolling Bucket 6 ------------*/
2645 l_bucket_amount_6 :=
2646 l_bucket_amount_6 + lt_bucket_amount_6 ;
2647 if (l_bucket_titletop_6) is NULL then
2648 l_bucket_titletop_6 := lt_bucket_titletop_6 ;
2649 End IF ;
2650 if (l_bucket_titlebottom_6) is NULL then
2651 l_bucket_titlebottom_6 := lt_bucket_titlebottom_6 ;
2652 End IF ;
2653
2654 if (l_bucket_line_id_6) is NULL then
2655 l_bucket_line_id_6 := lt_bucket_line_id_6 ;
2656 End If ;
2657
2658 if (l_bucket_seq_num_6) is NULL then
2659 l_bucket_seq_num_6 := lt_bucket_seq_num_6 ;
2660 End If ;
2661
2662 END LOOP ;
2663 IF NVL(p_using_paying_rel, 'N') = 'Y' THEN
2664 If AR_AGING_PAYING_PARTY_CUR%ISOPEN then
2665 CLOSE AR_AGING_PAYING_PARTY_CUR ;
2666 End IF ;
2667 ELSE
2668 If AR_AGING_PARTY_CUR%ISOPEN then
2669 CLOSE AR_AGING_PARTY_CUR ;
2670 End IF ;
2671 END IF;
2672 Exception
2673 WHEN OTHERS Then
2674 -- IF PG_DEBUG < 10 THEN
2675 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2676 IEX_DEBUG_PUB.LogMessage
2677 ('QUERY_AGING_LINES: ' || 'When Others Party Mode >> '||SQLCODE
2678 || ' ==> ' ||SQLERRM);
2679 END IF;
2680 IF NVL(p_using_paying_rel, 'N') = 'Y' THEN
2681 If AR_AGING_PAYING_PARTY_CUR%ISOPEN then
2682 CLOSE AR_AGING_PAYING_PARTY_CUR ;
2683 End IF ;
2684 ELSE
2685 If AR_AGING_PARTY_CUR%ISOPEN then
2686 CLOSE AR_AGING_PARTY_CUR ;
2687 End IF ;
2688 END IF;
2689 End ;
2690 End If ;
2691
2692 -- IF PG_DEBUG < 10 THEN
2693 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2694 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || ' Loading Buckets into Table of Records');
2695 END IF;
2696
2697 -- Load the Derived values into PL/SQL table
2698 /* ---------------------------------------------------------
2699 Bucket 0
2700 ---------------------------------------------------------*/
2701 if l_bucket_amount_0 IS NOT NULL then
2702 --if l_bucket_amount_0 <> 0 then
2703 x_bucket_lines_tbl(l_cnt).bucket_line
2704 := l_bucket_titletop_0 || l_bucket_titlebottom_0 ;
2705 x_bucket_lines_tbl(l_cnt).amount := l_bucket_amount_0 ;
2706
2707 x_bucket_lines_tbl(l_cnt).bucket_line_id
2708 := l_bucket_line_id_0 ;
2709 x_bucket_lines_tbl(l_cnt).bucket_seq_num
2710 := l_bucket_seq_num_0 ;
2711
2712 -- IF PG_DEBUG < 10 THEN
2713 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2714 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || l_line);
2715 END IF;
2716 -- IF PG_DEBUG < 10 THEN
2717 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2718 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || ' Row 1 Bucket Name ' ||
2719 x_bucket_lines_tbl(l_cnt).bucket_line ||
2720 ' Amount = ' || to_char(l_bucket_amount_0) ||
2721 '] Line Id = ['|| to_char(l_bucket_line_id_0) ||
2722 '] Seq Num = ['|| to_char(l_bucket_seq_num_0));
2723 END IF;
2724
2725 l_cnt := l_cnt + 1 ;
2726 -- End If ;
2727 End If ;
2728
2729 /*---------------------------------------------------------
2730 Bucket 1
2731 ---------------------------------------------------------*/
2732 if l_bucket_amount_1 IS NOT NULL then
2733 -- if l_bucket_amount_1 <> 0 then
2734 x_bucket_lines_tbl(l_cnt).bucket_line
2735 := l_bucket_titletop_1 || l_bucket_titlebottom_1 ;
2736 x_bucket_lines_tbl(l_cnt).amount := l_bucket_amount_1 ;
2737
2738 x_bucket_lines_tbl(l_cnt).bucket_line_id
2739 := l_bucket_line_id_1 ;
2740 x_bucket_lines_tbl(l_cnt).bucket_seq_num
2741 := l_bucket_seq_num_1 ;
2742
2743 -- IF PG_DEBUG < 10 THEN
2744 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2745 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || ' Row 2 Bucket Name [' ||
2746 x_bucket_lines_tbl(l_cnt).bucket_line ||
2747 '] Amount = [' || to_char(l_bucket_amount_1) ||
2748 '] Line Id = ['|| to_char(l_bucket_line_id_1) ||
2749 '] Seq Num = ['|| to_char(l_bucket_seq_num_1));
2750 END IF;
2751 l_cnt := l_cnt + 1 ;
2752 -- End If ;
2753 End If ;
2754
2755 /*---------------------------------------------------------
2756 Bucket 2
2757 ---------------------------------------------------------*/
2758 if l_bucket_amount_2 IS NOT NULL then
2759 -- if l_bucket_amount_2 <> 0 then
2760 x_bucket_lines_tbl(l_cnt).bucket_line
2761 := l_bucket_titletop_2 || l_bucket_titlebottom_2 ;
2762 x_bucket_lines_tbl(l_cnt).amount := l_bucket_amount_2 ;
2763
2764 x_bucket_lines_tbl(l_cnt).bucket_line_id
2765 := l_bucket_line_id_2 ;
2766 x_bucket_lines_tbl(l_cnt).bucket_seq_num
2767 := l_bucket_seq_num_2 ;
2768
2769 -- IF PG_DEBUG < 10 THEN
2770 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2771 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || ' Row 3 Bucket Name ' ||
2772 x_bucket_lines_tbl(l_cnt).bucket_line ||
2773 ' Amount = ' || to_char(l_bucket_amount_2) ||
2774 '] Line Id = ['|| to_char(l_bucket_line_id_2) ||
2775 '] Seq Num = ['|| to_char(l_bucket_seq_num_2));
2776 END IF;
2777 l_cnt := l_cnt + 1 ;
2778 -- End If ;
2779 End If ;
2780
2781 /*---------------------------------------------------------
2782 Bucket 3
2783 ---------------------------------------------------------*/
2784 if l_bucket_amount_3 IS NOT NULL then
2785 -- If l_bucket_amount_3 <> 0 then
2786 x_bucket_lines_tbl(l_cnt).bucket_line
2787 := l_bucket_titletop_3 || l_bucket_titlebottom_3 ;
2788 x_bucket_lines_tbl(l_cnt).amount := l_bucket_amount_3 ;
2789
2790 x_bucket_lines_tbl(l_cnt).bucket_line_id
2791 := l_bucket_line_id_3 ;
2792 x_bucket_lines_tbl(l_cnt).bucket_seq_num
2793 := l_bucket_seq_num_3 ;
2794
2795 -- IF PG_DEBUG < 10 THEN
2796 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2797 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || ' Row 4 Bucket Name ' ||
2798 x_bucket_lines_tbl(l_cnt).bucket_line ||
2799 ' Amount = ' || to_char(l_bucket_amount_3) ||
2800 '] Line Id = ['|| to_char(l_bucket_line_id_3) ||
2801 '] Seq Num = ['|| to_char(l_bucket_seq_num_3));
2802 END IF;
2803
2804 l_cnt := l_cnt + 1 ;
2805 -- End If ;
2806 End If ;
2807
2808 /*---------------------------------------------------------
2809 Bucket 4
2810 ---------------------------------------------------------*/
2811 if l_bucket_amount_4 IS NOT NULL then
2812 -- If l_bucket_amount_4 <> 0 then
2813 x_bucket_lines_tbl(l_cnt).bucket_line
2814 := l_bucket_titletop_4 || l_bucket_titlebottom_4 ;
2815 x_bucket_lines_tbl(l_cnt).amount := l_bucket_amount_4 ;
2816
2817 x_bucket_lines_tbl(l_cnt).bucket_line_id
2818 := l_bucket_line_id_4 ;
2819 x_bucket_lines_tbl(l_cnt).bucket_seq_num
2820 := l_bucket_seq_num_4 ;
2821
2822 -- IF PG_DEBUG < 10 THEN
2823 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2824 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || ' Row 5 Bucket Name ' ||
2825 x_bucket_lines_tbl(l_cnt).bucket_line ||
2826 ' Amount = ' || to_char(l_bucket_amount_4) ||
2827 '] Line Id = ['|| to_char(l_bucket_line_id_4) ||
2828 '] Seq Num = ['|| to_char(l_bucket_seq_num_4));
2829 END IF;
2830 l_cnt := l_cnt + 1 ;
2831 -- End If ;
2832 End If ;
2833
2834 /*---------------------------------------------------------
2835 Bucket 5
2836 ---------------------------------------------------------*/
2837 if l_bucket_amount_5 IS NOT NULL then
2838 -- If l_bucket_amount_5 <> 0 then
2839 x_bucket_lines_tbl(l_cnt).bucket_line
2840 := l_bucket_titletop_5 || l_bucket_titlebottom_5 ;
2841 x_bucket_lines_tbl(l_cnt).amount := l_bucket_amount_5 ;
2842
2843 x_bucket_lines_tbl(l_cnt).bucket_line_id
2844 := l_bucket_line_id_5 ;
2845 x_bucket_lines_tbl(l_cnt).bucket_seq_num
2846 := l_bucket_seq_num_5 ;
2847
2848 -- IF PG_DEBUG < 10 THEN
2849 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2850 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || ' Row 6 Bucket Name ' ||
2851 x_bucket_lines_tbl(l_cnt).bucket_line ||
2852 ' Amount = ' || to_char(l_bucket_amount_5) ||
2853 '] Line Id = ['|| to_char(l_bucket_line_id_5) ||
2854 '] Seq Num = ['|| to_char(l_bucket_seq_num_5));
2855 END IF;
2856 l_cnt := l_cnt + 1 ;
2857 -- End If ;
2858 End If ;
2859
2860 /*---------------------------------------------------------
2861 Bucket 6
2862 ---------------------------------------------------------*/
2863 if l_bucket_amount_6 IS NOT NULL then
2864 -- If l_bucket_amount_6 <> 0 then
2865 x_bucket_lines_tbl(l_cnt).bucket_line
2866 := l_bucket_titletop_6 || l_bucket_titlebottom_6 ;
2867 x_bucket_lines_tbl(l_cnt).amount := l_bucket_amount_6 ;
2868
2869 x_bucket_lines_tbl(l_cnt).bucket_line_id
2870 := l_bucket_line_id_6 ;
2871 x_bucket_lines_tbl(l_cnt).bucket_seq_num
2872 := l_bucket_seq_num_6 ;
2873
2874 -- IF PG_DEBUG < 10 THEN
2875 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2876 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || ' Row 7 Bucket Name ' ||
2877 x_bucket_lines_tbl(l_cnt).bucket_line ||
2878 ' Amount = ' || to_char(l_bucket_amount_6) ||
2879 '] Line Id = ['|| to_char(l_bucket_line_id_6) ||
2880 '] Seq Num = ['|| to_char(l_bucket_seq_num_6));
2881 END IF;
2882 l_cnt := l_cnt + 1 ;
2883 -- End If ;
2884 End If ;
2885 -- IF PG_DEBUG < 10 THEN
2886 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2887 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || l_line);
2888 END IF;
2889
2890
2891
2892 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2893 iex_debug_pub.logmessage('Filter Mode Before Collectible Amt ' || p_filter_mode || ' [ '||p_filter_id || ' ]') ;
2894 END IF;
2895
2896 -- Derive Collectible Amounts
2897 if p_filter_mode = 'CUST' then
2898 select aabl.aging_bucket_line_id, sum(acctd_amount_due_remaining)
2899 BULK COLLECT INTO
2900 l_collectible_bkt_id_tbl,
2901 l_collectible_bkt_amt_tbl
2902 from ar_aging_bucket_lines aabl,
2903 ar_payment_schedules aps,
2904 iex_delinquencies id
2905 where id.payment_schedule_id = aps.payment_schedule_id
2906 --BEGIN-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
2907 -- and id.status IN ('DELINQUENT', 'PRE-DELINQUENT')
2908 and id.status IN ('DELINQUENT', 'PREDELINQUENT')
2909 --END-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
2910 and trunc(sysdate)-aps.due_date >= aabl.days_start
2911 and trunc(sysdate)-aps.due_date <= aabl.days_to
2912 and id.cust_account_id = p_filter_id
2913 and aabl.aging_bucket_id = p_bucket_id
2914 --- Begin - Andre Araujo - 11/09/2004 - Performance fix
2915 AND aps.status = 'OP'
2916 --- End - Andre Araujo - 11/09/2004 - Performance fix
2917 group by aabl.aging_bucket_line_id ;
2918
2919 --Start bug 6701396 gnramasa 4th Mar 08
2920 if (NVL(FND_PROFILE.VALUE('IEX_EXCLUDE_DISPUTE_AMT_FROM_REMAINING_AMT'), 'N') = 'Y') then
2921 if l_collectible_bkt_id_tbl.COUNT > 0 then
2922 For coll_bkt_cnt IN 1..l_collectible_bkt_id_tbl.COUNT
2923 LOOP
2924 SELECT nvl(SUM(total_amount), 0)
2925 INTO l_collect_dispute_amt
2926 FROM ra_cm_requests
2927 WHERE customer_trx_id IN
2928 (SELECT DISTINCT id.transaction_id
2929 FROM ar_aging_bucket_lines aabl,
2930 ar_payment_schedules aps,
2931 iex_delinquencies id
2932 WHERE id.payment_schedule_id = aps.payment_schedule_id
2933 AND id.status IN('DELINQUENT', 'PREDELINQUENT')
2934 AND TRUNC(sysdate) -aps.due_date >= aabl.days_start
2935 AND TRUNC(sysdate) -aps.due_date <= aabl.days_to
2936 AND id.cust_account_id = p_filter_id
2937 AND aabl.aging_bucket_id = p_bucket_id
2938 AND aps.status = 'OP'
2939 AND aabl.aging_bucket_line_id = l_collectible_bkt_id_tbl(coll_bkt_cnt))
2940 AND status = 'PENDING_APPROVAL';
2941
2942 l_collectible_bkt_amt_tbl(coll_bkt_cnt) := l_collectible_bkt_amt_tbl(coll_bkt_cnt) + l_collect_dispute_amt;
2943 End Loop ;
2944 End If ;
2945 end if; --profile value 'IEX_EXCLUDE_DISPUTE_AMT_FROM_REMAINING_AMT'
2946
2947 ElsIf p_filter_mode = 'PARTY' then
2948 IF NVL(p_using_paying_rel, 'N') = 'Y' THEN
2949 select aabl.aging_bucket_line_id, sum(acctd_amount_due_remaining)
2950 BULK COLLECT INTO
2951 l_collectible_bkt_id_tbl,
2952 l_collectible_bkt_amt_tbl
2953 from ar_aging_bucket_lines aabl,
2954 ar_payment_schedules aps,
2955 iex_delinquencies id,
2956 hz_cust_accounts hzca
2957 where id.payment_schedule_id = aps.payment_schedule_id
2958 --BEGIN-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
2959 -- and id.status IN ('DELINQUENT', 'PRE-DELINQUENT')
2960 and id.status IN ('DELINQUENT', 'PREDELINQUENT')
2961 --END-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
2962 and trunc(sysdate)-aps.due_date >= aabl.days_start
2963 and trunc(sysdate)-aps.due_date <= aabl.days_to
2964 and id.cust_account_id = hzca.cust_account_id
2965 and hzca.party_id IN
2966 (SELECT p_filter_id FROM dual
2967 UNION
2968 SELECT ar.related_party_id
2969 FROM ar_paying_relationships_v ar
2970 WHERE ar.party_id = p_filter_id
2971 AND TRUNC(sysdate) BETWEEN
2972 TRUNC(NVL(ar.effective_start_date,sysdate)) AND
2973 TRUNC(NVL(ar.effective_end_date,sysdate)) )
2974 and aabl.aging_bucket_id = p_bucket_id
2975 --- Begin - Andre Araujo - 11/09/2004 - Performance fix
2976 AND aps.status = 'OP'
2977 --- End - Andre Araujo - 11/09/2004 - Performance fix
2978 group by aabl.aging_bucket_line_id ;
2979
2980 if (NVL(FND_PROFILE.VALUE('IEX_EXCLUDE_DISPUTE_AMT_FROM_REMAINING_AMT'), 'N') = 'Y') then
2981 if l_collectible_bkt_id_tbl.COUNT > 0 then
2982 For coll_bkt_cnt IN 1..l_collectible_bkt_id_tbl.COUNT
2983 LOOP
2984 SELECT nvl(SUM(total_amount), 0)
2985 INTO l_collect_dispute_amt
2986 FROM ra_cm_requests
2987 WHERE customer_trx_id IN
2988 (SELECT DISTINCT id.transaction_id
2989 FROM ar_aging_bucket_lines aabl,
2990 ar_payment_schedules aps,
2991 iex_delinquencies id,
2992 hz_cust_accounts hzca
2993 WHERE id.payment_schedule_id = aps.payment_schedule_id
2994 AND id.status IN('DELINQUENT', 'PREDELINQUENT')
2995 AND TRUNC(sysdate) -aps.due_date >= aabl.days_start
2996 AND TRUNC(sysdate) -aps.due_date <= aabl.days_to
2997 AND id.cust_account_id = hzca.cust_account_id
2998 AND hzca.party_id IN
2999 (SELECT p_filter_id
3000 FROM dual
3001 UNION
3002 SELECT ar.related_party_id
3003 FROM ar_paying_relationships_v ar
3004 WHERE ar.party_id = p_filter_id
3005 AND TRUNC(sysdate) BETWEEN TRUNC(nvl(ar.effective_start_date, sysdate))
3006 AND TRUNC(nvl(ar.effective_end_date, sysdate)))
3007 AND aabl.aging_bucket_id = p_bucket_id
3008 AND aps.status = 'OP'
3009 AND aabl.aging_bucket_line_id = l_collectible_bkt_id_tbl(coll_bkt_cnt))
3010 AND status = 'PENDING_APPROVAL';
3011
3012 l_collectible_bkt_amt_tbl(coll_bkt_cnt) := l_collectible_bkt_amt_tbl(coll_bkt_cnt) + l_collect_dispute_amt;
3013 End Loop ;
3014 End If ;
3015 end if; --profile value 'IEX_EXCLUDE_DISPUTE_AMT_FROM_REMAINING_AMT'
3016
3017 ELSE
3018 select aabl.aging_bucket_line_id, sum(acctd_amount_due_remaining)
3019 BULK COLLECT INTO
3020 l_collectible_bkt_id_tbl,
3021 l_collectible_bkt_amt_tbl
3022 from ar_aging_bucket_lines aabl,
3023 ar_payment_schedules aps,
3024 iex_delinquencies id,
3025 hz_cust_accounts hzca
3026 where id.payment_schedule_id = aps.payment_schedule_id
3027 --BEGIN-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
3028 -- and id.status IN ('DELINQUENT', 'PRE-DELINQUENT')
3029 and id.status IN ('DELINQUENT', 'PREDELINQUENT')
3030 --END-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
3031 and trunc(sysdate)-aps.due_date >= aabl.days_start
3032 and trunc(sysdate)-aps.due_date <= aabl.days_to
3033 and id.cust_account_id = hzca.cust_account_id
3034 and hzca.party_id = p_filter_id
3035 and aabl.aging_bucket_id = p_bucket_id
3036 --- Begin - Andre Araujo - 11/09/2004 - Performance fix
3037 AND aps.status = 'OP'
3038 --- End - Andre Araujo - 11/09/2004 - Performance fix
3039 group by aabl.aging_bucket_line_id ;
3040
3041 if (NVL(FND_PROFILE.VALUE('IEX_EXCLUDE_DISPUTE_AMT_FROM_REMAINING_AMT'), 'N') = 'Y') then
3042 if l_collectible_bkt_id_tbl.COUNT > 0 then
3043 For coll_bkt_cnt IN 1..l_collectible_bkt_id_tbl.COUNT
3044 LOOP
3045 SELECT nvl(SUM(total_amount), 0)
3046 INTO l_collect_dispute_amt
3047 FROM ra_cm_requests
3048 WHERE customer_trx_id IN
3049 (SELECT DISTINCT id.transaction_id
3050 FROM ar_aging_bucket_lines aabl,
3051 ar_payment_schedules aps,
3052 iex_delinquencies id,
3053 hz_cust_accounts hzca
3054 WHERE id.payment_schedule_id = aps.payment_schedule_id
3055 AND id.status IN('DELINQUENT', 'PREDELINQUENT')
3056 AND TRUNC(sysdate) -aps.due_date >= aabl.days_start
3057 AND TRUNC(sysdate) -aps.due_date <= aabl.days_to
3058 AND id.cust_account_id = hzca.cust_account_id
3059 AND hzca.party_id = p_filter_id
3060 AND aabl.aging_bucket_id = p_bucket_id
3061 AND aps.status = 'OP'
3062 AND aabl.aging_bucket_line_id = l_collectible_bkt_id_tbl(coll_bkt_cnt))
3063 AND status = 'PENDING_APPROVAL';
3064
3065 l_collectible_bkt_amt_tbl(coll_bkt_cnt) := l_collectible_bkt_amt_tbl(coll_bkt_cnt) + l_collect_dispute_amt;
3066 End Loop ;
3067 End If ;
3068 end if; --profile value 'IEX_EXCLUDE_DISPUTE_AMT_FROM_REMAINING_AMT'
3069
3070 END IF;
3071 Else
3072 select aabl.aging_bucket_line_id, sum(acctd_amount_due_remaining) -- added by ehuh for bill-to
3073 BULK COLLECT INTO -- added by ehuh for bill-to
3074 l_collectible_bkt_id_tbl, -- added by ehuh for bill-to
3075 l_collectible_bkt_amt_tbl -- added by ehuh for bill-to
3076 from ar_aging_bucket_lines aabl, -- added by ehuh for bill-to
3077 ar_payment_schedules aps, -- added by ehuh for bill-to
3078 iex_delinquencies id
3079 where id.payment_schedule_id = aps.payment_schedule_id -- added by ehuh for bill-to
3080 --BEGIN-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
3081 -- and id.status IN ('DELINQUENT', 'PRE-DELINQUENT')
3082 and id.status IN ('DELINQUENT', 'PREDELINQUENT')
3083 --END-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
3084 and trunc(sysdate)-aps.due_date >= aabl.days_start -- added by ehuh for bill-to
3085 and trunc(sysdate)-aps.due_date <= aabl.days_to -- added by ehuh for bill-to
3086 and aabl.aging_bucket_id = p_bucket_id -- added by ehuh for bill-to
3087 and aps.customer_site_use_id = p_filter_id -- added by ehuh for bill-to
3088 --- Begin - Andre Araujo - 11/09/2004 - Performance fix
3089 AND aps.status = 'OP'
3090 --- End - Andre Araujo - 11/09/2004 - Performance fix
3091 group by aabl.aging_bucket_line_id ; -- added by ehuh for bill-to
3092
3093 if (NVL(FND_PROFILE.VALUE('IEX_EXCLUDE_DISPUTE_AMT_FROM_REMAINING_AMT'), 'N') = 'Y') then
3094 if l_collectible_bkt_id_tbl.COUNT > 0 then
3095 For coll_bkt_cnt IN 1..l_collectible_bkt_id_tbl.COUNT
3096 LOOP
3097 SELECT nvl(SUM(total_amount), 0)
3098 INTO l_collect_dispute_amt
3099 FROM ra_cm_requests
3100 WHERE customer_trx_id IN
3101 (SELECT DISTINCT id.transaction_id
3102 FROM ar_aging_bucket_lines aabl,
3103 ar_payment_schedules aps,
3104 iex_delinquencies id
3105 WHERE id.payment_schedule_id = aps.payment_schedule_id
3106 AND id.status IN('DELINQUENT', 'PREDELINQUENT')
3107 AND TRUNC(sysdate) -aps.due_date >= aabl.days_start
3108 AND TRUNC(sysdate) -aps.due_date <= aabl.days_to
3109 AND aabl.aging_bucket_id = p_bucket_id
3110 AND aps.customer_site_use_id = p_filter_id
3111 AND aps.status = 'OP'
3112 AND aabl.aging_bucket_line_id = l_collectible_bkt_id_tbl(coll_bkt_cnt))
3113 AND status = 'PENDING_APPROVAL';
3114
3115 l_collectible_bkt_amt_tbl(coll_bkt_cnt) := l_collectible_bkt_amt_tbl(coll_bkt_cnt) + l_collect_dispute_amt;
3116 End Loop ;
3117 End If ;
3118 end if; --profile value 'IEX_EXCLUDE_DISPUTE_AMT_FROM_REMAINING_AMT'
3119 --End bug 6701396 gnramasa 4th Mar 08
3120 End If ;
3121
3122 /*---------------------------------------------------------
3123 Loading all the one time results
3124 ---------------------------------------------------------*/
3125 -- IF PG_DEBUG < 10 THEN
3126 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3127 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || ' Loading one time results ' );
3128 END IF;
3129 FOR cnt in 1..x_bucket_lines_tbl.COUNT
3130 LOOP
3131 x_bucket_lines_tbl(cnt).outstanding_balance
3132 := l_outstanding_balance ;
3133 x_bucket_lines_tbl(cnt).currency := v_currency ;
3134 x_bucket_lines_tbl(cnt).collectible_amount := 0 ;
3135
3136 -- Merge the Collectible Amount with the AR Amount
3137 if l_collectible_bkt_id_tbl.COUNT > 0 then
3138 For bkt_cnt IN 1..l_collectible_bkt_id_tbl.COUNT
3139 LOOP
3140
3141 if l_collectible_bkt_id_tbl(bkt_cnt) =
3142 x_bucket_lines_tbl(cnt).bucket_line_id then
3143 x_bucket_lines_tbl(cnt).collectible_amount :=
3144 l_collectible_bkt_amt_tbl(bkt_cnt) ;
3145 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: collectible_amount--> ' ||x_bucket_lines_tbl(cnt).collectible_amount);
3146 End If ;
3147 End Loop ;
3148 End If ;
3149
3150
3151 -- IF PG_DEBUG < 10 THEN
3152 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3153 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || ' Row >> ' || to_char(cnt) || ' Bal [' ||
3154 to_char(x_bucket_lines_tbl(cnt).outstanding_balance) ||
3155 'Currency [' || x_bucket_lines_tbl(cnt).currency);
3156 END IF;
3157
3158
3159 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: x_bucket_lines_tbl_amt-->'|| x_bucket_lines_tbl(cnt).amount);
3160 l_total_amount := l_total_amount + x_bucket_lines_tbl(cnt).amount;
3161
3162 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: l_total_amt-->'||l_total_amount);
3163 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: x_bucket_lines_tbl_coll_amt-->'|| x_bucket_lines_tbl(cnt).collectible_amount);
3164 l_total_coll_amount := l_total_coll_amount +
3165 x_bucket_lines_tbl(cnt).collectible_amount;
3166 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: l_total_coll_amount-->'|| l_total_coll_amount);
3167
3168
3169 GET_CNSLD_INVOICE_COUNT(
3170 p_api_version => p_api_version,
3171 p_init_msg_list => p_init_msg_list,
3172 p_commit => p_commit,
3173 p_validation_level => p_validation_level,
3174 x_return_status => x_return_status,
3175 x_msg_count => x_msg_count,
3176 x_msg_data => x_msg_data,
3177 p_filter_mode => p_filter_mode,
3178 p_bucket_line_id => x_bucket_lines_tbl(cnt).bucket_line_id,
3179 p_filter_id => p_filter_id,
3180 p_customer_site_use_id => p_customer_site_use_id,
3181 p_using_paying_rel => p_using_paying_rel,
3182 x_count => x_bucket_lines_tbl(cnt).consolidated_invoices,
3183 x_amount => l_cons_amount) ;
3184
3185 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3186 IEX_DEBUG_PUB.LogMessage('After Call CONS : ' || x_bucket_lines_tbl(cnt).consolidated_invoices) ;
3187 END IF;
3188
3189 l_total_cnsld_invoices := l_total_cnsld_invoices +
3190 x_bucket_lines_tbl(cnt).consolidated_invoices ;
3191 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: '||'l_total_cnsld_invoices-->'||l_total_cnsld_invoices);
3192
3193
3194 -- Load all the Values from the Collections
3195 -- Load Invoice Count/Amount
3196 GET_BKT_INVOICE_CLASS_INFO
3197 (p_api_version => p_api_version,
3198 p_init_msg_list => p_init_msg_list,
3199 p_commit => p_commit,
3200 p_validation_level => p_validation_level,
3201 x_return_status => x_return_status,
3202 x_msg_count => x_msg_count,
3203 x_msg_data => x_msg_data,
3204 p_filter_mode => p_filter_mode,
3205 p_bucket_line_id => x_bucket_lines_tbl(cnt).bucket_line_id,
3206 p_filter_id => p_filter_id,
3207 p_customer_site_use_id => p_customer_site_use_id, -- added by ehuh for bill-to
3208 p_using_paying_rel => p_using_paying_rel,
3209 p_class => 'INV',
3210 x_class_count => x_bucket_lines_tbl(cnt).Invoice_count,
3211 x_class_amount => x_bucket_lines_tbl(cnt).Invoice_amount);
3212
3213
3214 l_total_invoices := l_total_invoices +
3215 x_bucket_lines_tbl(cnt).Invoice_count ;
3216 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: '||'l_total_inv_cnt-->'||l_total_invoices);
3217 l_total_inv_amount := l_total_inv_amount +
3218 x_bucket_lines_tbl(cnt).Invoice_amount ;
3219 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: '||'l_total_inv_amt-->'||l_total_inv_amount);
3220
3221
3222 -- Load DM Count/Amount
3223 GET_BKT_INVOICE_CLASS_INFO
3224 (p_api_version => p_api_version,
3225 p_init_msg_list => p_init_msg_list,
3226 p_commit => p_commit,
3227 p_validation_level => p_validation_level,
3228 x_return_status => x_return_status,
3229 x_msg_count => x_msg_count,
3230 x_msg_data => x_msg_data,
3231 p_filter_mode => p_filter_mode,
3232 p_bucket_line_id => x_bucket_lines_tbl(cnt).bucket_line_id,
3233 p_filter_id => p_filter_id,
3234 p_customer_site_use_id => p_customer_site_use_id, -- added by ehuh for bill-to
3235 p_using_paying_rel => p_using_paying_rel,
3236 p_class => 'DM',
3237 x_class_count => x_bucket_lines_tbl(cnt).Dm_count,
3238 x_class_amount => x_bucket_lines_tbl(cnt).Dm_amount);
3239 l_total_debit_memos := l_total_debit_memos +
3240 x_bucket_lines_tbl(cnt).dm_count ;
3241 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: '||'l_total_dms-->'||l_total_debit_memos);
3242 l_total_dm_amount := l_total_dm_amount +
3243 x_bucket_lines_tbl(cnt).dm_amount ;
3244 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: '||'l_total_dm_amt-->'||l_total_dm_amount);
3245
3246 -- Load Chargeback Count/Amount
3247 GET_BKT_INVOICE_CLASS_INFO
3248 (p_api_version => p_api_version,
3249 p_init_msg_list => p_init_msg_list,
3250 p_commit => p_commit,
3251 p_validation_level => p_validation_level,
3252 x_return_status => x_return_status,
3253 x_msg_count => x_msg_count,
3254 x_msg_data => x_msg_data,
3255 p_filter_mode => p_filter_mode,
3256 p_bucket_line_id => x_bucket_lines_tbl(cnt).bucket_line_id,
3257 p_filter_id => p_filter_id,
3258 p_customer_site_use_id => p_customer_site_use_id, -- added by ehuh for bill-to
3259 p_class => 'CB',
3260 p_using_paying_rel => p_using_paying_rel,
3261 x_class_count => x_bucket_lines_tbl(cnt).cb_count,
3262 x_class_amount => x_bucket_lines_tbl(cnt).cb_amount);
3263 l_total_chargebacks := l_total_chargebacks +
3264 x_bucket_lines_tbl(cnt).cb_count ;
3265 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: '||'l_total_cbs-->'||l_total_chargebacks);
3266 l_total_cb_amount := l_total_cb_amount +
3267 x_bucket_lines_tbl(cnt).cb_amount ;
3268 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: '||'l_total_cb_amt-->'||l_total_cb_amount);
3269 END LOOP ;
3270
3271 -- Adding Total Row to the Table
3272 /* TRANSLATE THIS */
3273 l_total_rec := x_bucket_lines_tbl.COUNT + 1 ;
3274 x_bucket_lines_tbl(l_total_rec).Bucket_line:= 'Totals';
3275 x_bucket_lines_tbl(l_total_rec).Amount := l_total_amount;
3276 x_bucket_lines_tbl(l_total_rec).Currency :=
3277 x_bucket_lines_tbl(l_total_rec-1).Currency;
3278 x_bucket_lines_tbl(l_total_rec).collectible_amount :=
3279 l_total_coll_amount ;
3280 x_bucket_lines_tbl(l_total_rec).invoice_count := l_total_invoices ;
3281 x_bucket_lines_tbl(l_total_rec).invoice_amount := l_total_inv_amount ;
3282
3283 x_bucket_lines_tbl(l_total_rec).consolidated_invoices :=
3284 l_total_cnsld_invoices ;
3285
3286 x_bucket_lines_tbl(l_total_rec).dm_count := l_total_debit_memos ;
3287 x_bucket_lines_tbl(l_total_rec).dm_amount := l_total_dm_amount ;
3288
3289 x_bucket_lines_tbl(l_total_rec).cb_count := l_total_chargebacks ;
3290 x_bucket_lines_tbl(l_total_rec).cb_amount := l_total_cb_amount ;
3291
3292 x_bucket_lines_tbl(l_total_rec).disputed_tran_count:= l_total_disputes ;
3293 x_bucket_lines_tbl(l_total_rec).disputed_tran_amount :=
3294 l_total_disp_amount ;
3295
3296 -- Standard check of p_commit
3297 IF FND_API.To_Boolean(p_commit) THEN
3298 COMMIT WORK;
3299 END IF;
3300
3301 -- Standard call to get message count and if count is 1, get message info
3302 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3303
3304 -- IF PG_DEBUG < 10 THEN
3305 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3306 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || '-----------------------------------------');
3307 END IF;
3308 -- IF PG_DEBUG < 10 THEN
3309 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3310 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES --->> End <<--- ');
3311 END IF;
3312 -- IF PG_DEBUG < 10 THEN
3313 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3314 IEX_DEBUG_PUB.LogMessage('QUERY_AGING_LINES: ' || '-----------------------------------------');
3315 END IF;
3316
3317 EXCEPTION
3318 WHEN FND_API.G_EXC_ERROR THEN
3319 ROLLBACK TO Query_Aging_lines;
3320 x_return_status := FND_API.G_RET_STS_ERROR;
3321 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3322
3323 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3324 ROLLBACK TO Query_Aging_lines;
3325 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3326 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3327
3328 WHEN OTHERS THEN
3329 ROLLBACK TO Query_Aging_lines;
3330 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3331 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3332 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3333 END IF;
3334 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3335
3336 End QUERY_AGING_LINES ;
3337
3338
3339
3340 PROCEDURE GET_BKT_INVOICE_CLASS_INFO
3341 (p_api_version IN NUMBER := 1.0,
3342 p_init_msg_list IN VARCHAR2,
3343 p_commit IN VARCHAR2,
3344 p_validation_level IN NUMBER,
3345 x_return_status OUT NOCOPY VARCHAR2,
3346 x_msg_count OUT NOCOPY NUMBER,
3347 x_msg_data OUT NOCOPY VARCHAR2,
3348 p_filter_mode IN Varchar2,
3349 p_bucket_line_id IN AR_AGING_BUCKET_LINES_B.Aging_Bucket_Line_Id%TYPE,
3350 p_filter_id IN Number,
3351 p_customer_site_use_id IN Number, -- added by ehuh for bill-to
3352 p_class IN varchar2,
3353 p_using_paying_rel IN VARCHAR2,
3354 x_class_count OUT NOCOPY Number,
3355 x_class_amount OUT NOCOPY NUMBER )
3356 IS
3357 v_tran_sql varchar2(1000) ;
3358 v_party_tran_sql varchar2(1000) ;
3359 v_paying_party_tran_sql varchar2(1000) ;
3360 v_party_billto_tran_sql
3361 varchar2(2000) ; -- added by ehuh for bill-to
3362 v_sql varchar2(1000) ;
3363 --- Begin - Andre Araujo - 11/09/2004 - Performance fix we will select the aging separately
3364 v_days_start NUMBER;
3365 v_days_to NUMBER;
3366 --- End - Andre Araujo - 11/09/2004 - Performance fix we will select the aging separately
3367
3368 BEGIN
3369
3370 x_class_count := 0; -- added by ehuh for bill-to
3371 x_class_amount := 0; -- added by ehuh for bill-to
3372
3373 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3374 IEX_DEBUG_PUB.LogMessage(l_line) ;
3375 IEX_DEBUG_PUB.LogMessage('GET_BKT_INVOICE_CLASS_INFO --->> Start <<--- ') ;
3376 IEX_DEBUG_PUB.LogMessage(l_line) ;
3377 END IF;
3378
3379
3380 --- Begin - Andre Araujo - 11/09/2004 - Performance fix we will select the aging separately
3381 select days_start, days_to
3382 into v_days_start, v_days_to
3383 from ar_aging_bucket_lines
3384 where aging_bucket_line_id = p_bucket_line_id;
3385
3386 v_tran_sql :=
3387 -- 'SELECT NVL(count(1), 0), NVL(SUM(ACCTD_AMOUNT_DUE_REMAINING), 0)
3388 -- FROM ar_payment_schedules arp,
3389 -- ar_aging_bucket_lines aabl
3390 -- WHERE sysdate-arp.due_date >= aabl.days_start
3391 -- and sysdate-arp.due_date <= aabl.days_to
3392 -- and arp.class = :class
3393 -- and arp.status = ''OP''
3394 -- and aabl.aging_bucket_line_id = :bucket_line_id
3395 -- AND arp.customer_id = :cust_account_id ' ;
3396 --Bug5170294. Fix by LKKUMAR. Use Trunc on Dates. Start.
3397 'SELECT NVL(count(1), 0), NVL(SUM(ACCTD_AMOUNT_DUE_REMAINING), 0)
3398 FROM ar_payment_schedules arp
3399 WHERE (arp.customer_id = :cust_account_id
3400 and arp.status = ''OP'')
3401 and
3402 (
3403 trunc(sysdate)-trunc(arp.due_date) >= :days_start
3404 and trunc(sysdate)-trunc(arp.due_date) <= :days_to
3405 and arp.class = :class
3406 )' ;
3407
3408
3409 IF NVL(p_using_paying_rel, 'N') = 'Y' THEN
3410 v_paying_party_tran_sql :=
3411 'SELECT NVL(count(1), 0), NVL(SUM(ACCTD_AMOUNT_DUE_REMAINING), 0)
3412 FROM ar_payment_schedules arp,
3413 ar_aging_bucket_lines aabl,
3414 hz_cust_accounts hzca
3415 WHERE trunc(sysdate)-trunc(arp.due_date) >= aabl.days_start
3416 and trunc(sysdate)-trunc(arp.due_date) <= aabl.days_to
3417 and arp.class = :class
3418 and arp.status = ''OP''
3419 and arp.customer_id = hzca.cust_account_id
3420 and aabl.aging_bucket_line_id = :bucket_line_id
3421 and hzca.party_id IN
3422 (SELECT :party_id FROM dual
3423 UNION
3424 SELECT ar.related_party_id
3425 FROM ar_paying_relationships_v ar
3426 WHERE ar.party_id = :party_id
3427 AND TRUNC(sysdate) BETWEEN
3428 TRUNC(NVL(ar.effective_start_date,sysdate)) AND
3429 TRUNC(NVL(ar.effective_end_date,sysdate)) ) ' ;
3430 --Bug5170294. Fix by LKKUMAR. Use Trunc on Dates. End.
3431 ELSE
3432 v_party_tran_sql :=
3433 -- 'SELECT NVL(count(1), 0), NVL(SUM(ACCTD_AMOUNT_DUE_REMAINING), 0)
3434 -- FROM ar_payment_schedules arp,
3435 -- ar_aging_bucket_lines aabl,
3436 -- hz_cust_accounts hzca
3437 -- WHERE sysdate-arp.due_date >= aabl.days_start
3438 -- and sysdate-arp.due_date <= aabl.days_to
3439 -- and arp.class = :class
3440 -- and arp.status = ''OP''
3441 -- and arp.customer_id = hzca.cust_account_id
3442 -- and aabl.aging_bucket_line_id = :bucket_line_id
3443 -- and hzca.party_id = :party_id ' ;
3444 --Bug5170294. Fix by LKKUMAR. Use Trunc on Dates. Start.
3445 'SELECT NVL(count(1), 0), NVL(SUM(ACCTD_AMOUNT_DUE_REMAINING), 0)
3446 FROM ar_payment_schedules arp
3447 WHERE (arp.customer_id in (select cust_account_id from hz_cust_accounts where party_id = :party_id)
3448 and arp.status = ''OP'')
3449 and
3450 (
3451 trunc(sysdate)-trunc(arp.due_date) >= :days_start
3452 and trunc(sysdate)-trunc(arp.due_date) <= :days_to
3453 and arp.class = :class
3454 )' ;
3455 --Bug5170294. Fix by LKKUMAR. Use Trunc on Dates. End.
3456
3457 END IF;
3458
3459 /* Start added by ehuh for bill_to */
3460 v_party_billto_tran_sql :=
3461 -- 'SELECT NVL(count(1), 0), NVL(SUM(ACCTD_AMOUNT_DUE_REMAINING), 0)
3462 -- FROM ar_payment_schedules arp,
3463 -- ar_aging_bucket_lines aabl,
3464 -- hz_cust_accounts hzca
3465 -- WHERE sysdate-arp.due_date >= aabl.days_start
3466 -- and sysdate-arp.due_date <= aabl.days_to
3467 -- and arp.class = :class
3468 -- and arp.status = ''OP''
3469 -- and arp.customer_id = hzca.cust_account_id
3470 -- and aabl.aging_bucket_line_id = :bucket_line_id
3471 -- and arp.customer_site_use_id = :customer_site_use_id ' ;
3472 --Bug5170294. Fix by LKKUMAR. Use Trunc on Dates. Start.
3473 'SELECT NVL(count(1), 0), NVL(SUM(ACCTD_AMOUNT_DUE_REMAINING), 0)
3474 FROM ar_payment_schedules arp
3475 WHERE (arp.customer_site_use_id = :customer_site_use_id
3476 and arp.status = ''OP'')
3477 and
3478 (
3479 trunc(sysdate)-trunc(arp.due_date) >= :days_start
3480 and trunc(sysdate)-trunc(arp.due_date) <= :days_to
3481 and arp.class = :class
3482 )' ;
3483 --Bug5170294. Fix by LKKUMAR. Use Trunc on Dates. End.
3484
3485 /* End added by ehuh for bill-to */
3486
3487 if p_filter_mode = 'PARTY' then
3488 v_sql := v_party_tran_sql ;
3489 elsif p_filter_mode = 'BILLTO' then
3490 v_sql := v_party_billto_tran_sql ;
3491 else
3492 v_sql := v_tran_sql ;
3493 End If ;
3494
3495 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3496 IEX_DEBUG_PUB.LogMessage('SQL Executed --> ' || v_sql) ;
3497 END IF;
3498
3499 Begin
3500
3501 if p_filter_mode = 'PARTY' and NVL(p_using_paying_rel, 'N') = 'Y' then
3502 EXECUTE IMMEDIATE v_sql
3503 INTO x_class_count,
3504 x_class_amount
3505 USING p_class,
3506 p_bucket_line_id,
3507 p_filter_id, p_filter_id ;
3508 else
3509 EXECUTE IMMEDIATE v_sql
3510 INTO x_class_count,
3511 x_class_amount
3512 USING p_filter_id,
3513 v_days_start,
3514 v_days_to,
3515 p_class;
3516 -- fix bug #4110299 p_class,
3517 -- fix bug #4110299 p_bucket_line_id,
3518 -- fix bug #4110299 p_filter_id ;
3519 end if;
3520 Exception
3521 WHEN NO_DATA_FOUND THEN
3522 x_class_count := 0; x_class_amount := 0;
3523 WHEN OTHERS THEN
3524 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3525 IEX_DEBUG_PUB.LogMessage(SQLCODE || ' ' || SQLERRM) ;
3526 END IF;
3527 End ;
3528
3529 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3530 IEX_DEBUG_PUB.LogMessage(l_line) ;
3531 IEX_DEBUG_PUB.LogMessage('GET_BKT_INVOICE_CLASS_INFO --->> End <<--- ') ;
3532 IEX_DEBUG_PUB.LogMessage(l_line) ;
3533 END IF;
3534
3535 END ;
3536
3537 -- Added as a part of OKL changes
3538 PROCEDURE GET_CNSLD_INVOICE_COUNT
3539 (p_api_version IN NUMBER := 1.0,
3540 p_init_msg_list IN VARCHAR2,
3541 p_commit IN VARCHAR2,
3542 p_validation_level IN NUMBER,
3543 x_return_status OUT NOCOPY VARCHAR2,
3544 x_msg_count OUT NOCOPY NUMBER,
3545 x_msg_data OUT NOCOPY VARCHAR2,
3546 p_filter_mode IN Varchar2,
3547 p_bucket_line_id IN AR_AGING_BUCKET_LINES_B.Aging_Bucket_Line_Id%TYPE,
3548 p_filter_id IN Number,
3549 p_customer_site_use_id IN Number,
3550 p_using_paying_rel IN VARCHAR2,
3551 x_count OUT NOCOPY Number,
3552 x_amount OUT NOCOPY NUMBER)
3553 IS
3554 v_cust_cnsld_sql varchar2(2000) ;
3555 v_party_cnsld_sql varchar2(2000) ;
3556 v_party_billto_cnsld_sql
3557 varchar2(2000) ;
3558 v_sql varchar2(3000) ;
3559
3560
3561 -- Bind Variables
3562 b_class AR_PAYMENT_SCHEDULES.CLASS%TYPE;
3563 b_status AR_PAYMENT_SCHEDULES.STATUS%TYPE;
3564 b_interface_attr RA_CUSTOMER_TRX.INTERFACE_HEADER_ATTRIBUTE9%TYPE;
3565 b_interface_context RA_CUSTOMER_TRX.INTERFACE_HEADER_CONTEXT%TYPE;
3566
3567 BEGIN
3568
3569 b_interface_attr := 'CURE' ;
3570 b_interface_context := 'OKL_CONTRACTS' ;
3571
3572 x_count := 0;
3573 x_amount := 0;
3574
3575 b_class := 'INV' ;
3576 b_status := 'OP' ;
3577
3578 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3579 IEX_DEBUG_PUB.LogMessage(l_line) ;
3580 IEX_DEBUG_PUB.LogMessage('GET_CNSLD_INVOICE_COUNT --->> Start <<--- ') ;
3581 IEX_DEBUG_PUB.LogMessage(l_line) ;
3582 IEX_DEBUG_PUB.LogMessage('Hello') ;
3583 END IF;
3584
3585 v_cust_cnsld_sql :=
3586 'SELECT count(distinct ocahb.id)
3587 FROM ar_payment_schedules arp,
3588 ar_aging_bucket_lines aabl,
3589 ra_customer_trx rct,
3590 okl_cnsld_ar_strms_b ocasb,
3591 OKL_CNSLD_AR_LINES_B ocalb,
3592 OKL_CNSLD_AR_HDRS_B ocahb
3593 WHERE (:l_date - arp.due_date) >= aabl.days_start
3594 and (:l_date - arp.due_date) <= aabl.days_to
3595 and arp.class = :l_class
3596 and rct.customer_trx_id = arp.customer_trx_id
3597 and ocasb.receivables_invoice_id = rct.customer_trx_id
3598 and ocalb.id = ocasb.lln_id
3599 and ocahb.id = ocalb.cnr_id
3600 and rct.interface_header_attribute9 <> :l_interface_attr
3601 and rct.interface_header_context = :l_interface_context
3602 and arp.status = :l_status
3603 and aabl.aging_bucket_line_id = :bucket_line_id
3604 AND arp.customer_id = :cust_account_id' ;
3605
3606
3607 IF NVL(p_using_paying_rel, 'N') = 'Y' THEN
3608 v_party_cnsld_sql :=
3609 'SELECT count(distinct ocahb.id)
3610 FROM ar_payment_schedules arp,
3611 ar_aging_bucket_lines aabl,
3612 ra_customer_trx rct,
3613 okl_cnsld_ar_strms_b ocasb,
3614 OKL_CNSLD_AR_LINES_B ocalb,
3615 OKL_CNSLD_AR_HDRS_B ocahb,
3616 HZ_CUST_ACCOUNTS hzca
3617 WHERE (:l_date - arp.due_date) >= aabl.days_start
3618 and (:l_date - arp.due_date) <= aabl.days_to
3619 and arp.class = :l_class
3620 and rct.customer_trx_id = arp.customer_trx_id
3621 and ocasb.receivables_invoice_id = rct.customer_trx_id
3622 and ocalb.id = ocasb.lln_id
3623 and ocahb.id = ocalb.cnr_id
3624 and rct.interface_header_attribute9 <> :l_interface_attr
3625 and rct.interface_header_context = :l_interface_context
3626 and arp.status = :l_status
3627 and aabl.aging_bucket_line_id = :bucket_line_id
3628 AND arp.customer_id = hzca.cust_account_id
3629 AND hzca.party_id IN
3630 (SELECT :party_id FROM dual
3631 UNION
3632 SELECT ar.related_party_id
3633 FROM ar_paying_relationships_v ar
3634 WHERE ar.party_id = :party_id
3635 AND TRUNC(sysdate) BETWEEN
3636 TRUNC(NVL(ar.effective_start_date,sysdate)) AND
3637 TRUNC(NVL(ar.effective_end_date,sysdate)) ) ' ;
3638
3639 ELSE
3640 v_party_cnsld_sql :=
3641 'SELECT count(distinct ocahb.id)
3642 FROM ar_payment_schedules arp,
3643 ar_aging_bucket_lines aabl,
3644 ra_customer_trx rct,
3645 okl_cnsld_ar_strms_b ocasb,
3646 OKL_CNSLD_AR_LINES_B ocalb,
3647 OKL_CNSLD_AR_HDRS_B ocahb,
3648 HZ_CUST_ACCOUNTS hzca
3649 WHERE (:l_date - arp.due_date) >= aabl.days_start
3650 and (:l_date - arp.due_date) <= aabl.days_to
3651 and arp.class = :l_class
3652 and rct.customer_trx_id = arp.customer_trx_id
3653 and ocasb.receivables_invoice_id = rct.customer_trx_id
3654 and ocalb.id = ocasb.lln_id
3655 and ocahb.id = ocalb.cnr_id
3656 and rct.interface_header_attribute9 <> :l_interface_attr
3657 and rct.interface_header_context = :l_interface_context
3658 and arp.status = :l_status
3659 and aabl.aging_bucket_line_id = :bucket_line_id
3660 AND arp.customer_id = hzca.cust_account_id
3661 AND hzca.party_id = :party_id ' ;
3662
3663 END IF;
3664
3665 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3666 IEX_DEBUG_PUB.LogMessage('') ;
3667 IEX_DEBUG_PUB.LogMessage(v_party_cnsld_sql) ;
3668 END IF;
3669
3670
3671
3672 v_party_billto_cnsld_sql :=
3673 'SELECT count(distinct ocahb.id)
3674 FROM ar_payment_schedules arp,
3675 ar_aging_bucket_lines aabl,
3676 ra_customer_trx rct,
3677 okl_cnsld_ar_strms_b ocasb,
3678 OKL_CNSLD_AR_LINES_B ocalb,
3679 OKL_CNSLD_AR_HDRS_B ocahb,
3680 HZ_CUST_ACCOUNTS hzca
3681 WHERE (:l_date - arp.due_date) >= aabl.days_start
3682 and (:l_date - arp.due_date) <= aabl.days_to
3683 and arp.class = :l_class
3684 and rct.customer_trx_id = arp.customer_trx_id
3685 and ocasb.receivables_invoice_id = rct.customer_trx_id
3686 and ocalb.id = ocasb.lln_id
3687 and ocahb.id = ocalb.cnr_id
3688 and rct.interface_header_attribute9 <> :l_interface_attr
3689 and rct.interface_header_context = :l_interface_context
3690 and arp.status = :l_status
3691 and aabl.aging_bucket_line_id = :bucket_line_id
3692 AND arp.customer_id = hzca.cust_account_id
3693 AND arp.customer_site_use_id = :customer_site_use_id' ;
3694
3695
3696 if p_filter_mode = 'PARTY' then
3697 v_sql := v_party_cnsld_sql ;
3698 elsif p_filter_mode = 'CUST' then
3699 v_sql := v_cust_cnsld_sql ;
3700 else
3701 v_sql := v_party_billto_cnsld_sql ;
3702 End If ;
3703
3704 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3705 IEX_DEBUG_PUB.LogMessage('SQL Executed --> ' || v_sql) ;
3706 END IF;
3707
3708 Begin
3709
3710 IF NVL(p_using_paying_rel, 'N') = 'Y' THEN
3711 EXECUTE IMMEDIATE v_sql
3712 INTO x_count
3713 USING l_date,
3714 l_date,
3715 b_class,
3716 b_interface_attr,
3717 b_interface_context,
3718 b_status,
3719 p_bucket_line_id,
3720 p_filter_id, p_filter_id;
3721
3722 ELSE
3723 EXECUTE IMMEDIATE v_sql
3724 INTO x_count
3725 USING l_date,
3726 l_date,
3727 b_class,
3728 b_interface_attr,
3729 b_interface_context,
3730 b_status,
3731 p_bucket_line_id,
3732 p_filter_id ;
3733
3734 END IF;
3735 Exception
3736 WHEN NO_DATA_FOUND THEN
3737 x_count := 0; x_amount := 0;
3738 WHEN OTHERS THEN
3739 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3740 IEX_DEBUG_PUB.LogMessage(SQLCODE || ' ' || SQLERRM) ;
3741 END IF;
3742 End ;
3743
3744 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3745 IEX_DEBUG_PUB.LogMessage(l_line) ;
3746 IEX_DEBUG_PUB.LogMessage('GET_CNSLD_INVOICE_COUNT --->> End <<--- ') ;
3747 IEX_DEBUG_PUB.LogMessage(l_line) ;
3748 END IF;
3749 EXCEPTION
3750 WHEN OTHERS then
3751 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3752 IEX_DEBUG_PUB.LogMessage(' GET_CNSLD_INVOICE_COUNT - MAIN ' ||
3753 SQLCODE || ' ' || SQLERRM) ;
3754 END IF;
3755
3756 END GET_CNSLD_INVOICE_COUNT ;
3757
3758 BEGIN
3759 l_line := '-----------------------------------------' ;
3760 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3761 END iex_aging_buckets_pkg ;
3762 --
3763 --
3764 --
3765 --