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