[Home] [Help]
PACKAGE BODY: APPS.ARI_DB_UTILITIES
Source
4 /*=======================================================================+
1 PACKAGE BODY ARI_DB_UTILITIES AS
2 /* $Header: ARIDBUTLB.pls 120.32.12020000.3 2012/12/25 18:30:15 shvimal ship $ */
3
5 | Package Global Constants
6 +=======================================================================*/
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ARI_DB_UTILITIES';
8 /*========================================================================
9 | Prototype Declarations Procedures
10 *=======================================================================*/
11 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
12
16 p_currency_code IN VARCHAR2,
13 PROCEDURE calc_aging_buckets (
14 p_customer_id IN NUMBER,
15 p_as_of_date IN DATE,
17 p_credit_option IN VARCHAR2,
18 p_invoice_type_low IN VARCHAR2,
19 p_invoice_type_high IN VARCHAR2,
20 p_ps_max_id IN NUMBER DEFAULT 0,
21 p_app_max_id IN NUMBER DEFAULT 0,
22 p_bucket_name IN VARCHAR2,
23 p_outstanding_balance IN OUT NOCOPY NUMBER,
24 p_bucket_titletop_0 OUT NOCOPY VARCHAR2,
25 p_bucket_titlebottom_0 OUT NOCOPY VARCHAR2,
26 p_bucket_amount_0 IN OUT NOCOPY NUMBER,
27 p_bucket_titletop_1 OUT NOCOPY VARCHAR2,
28 p_bucket_titlebottom_1 OUT NOCOPY VARCHAR2,
29 p_bucket_amount_1 IN OUT NOCOPY NUMBER,
30 p_bucket_titletop_2 OUT NOCOPY VARCHAR2,
31 p_bucket_titlebottom_2 OUT NOCOPY VARCHAR2,
32 p_bucket_amount_2 IN OUT NOCOPY NUMBER,
33 p_bucket_titletop_3 OUT NOCOPY VARCHAR2,
34 p_bucket_titlebottom_3 OUT NOCOPY VARCHAR2,
35 p_bucket_amount_3 IN OUT NOCOPY NUMBER,
36 p_bucket_titletop_4 OUT NOCOPY VARCHAR2,
37 p_bucket_titlebottom_4 OUT NOCOPY VARCHAR2,
38 p_bucket_amount_4 IN OUT NOCOPY NUMBER,
39 p_bucket_titletop_5 OUT NOCOPY VARCHAR2,
40 p_bucket_titlebottom_5 OUT NOCOPY VARCHAR2,
41 p_bucket_amount_5 IN OUT NOCOPY NUMBER,
42 p_bucket_titletop_6 OUT NOCOPY VARCHAR2,
43 p_bucket_titlebottom_6 OUT NOCOPY VARCHAR2,
44 p_bucket_amount_6 IN OUT NOCOPY NUMBER,
45 p_session_id IN NUMBER
46 );
47
48
49 /*========================================================================
50 | Prototype Declarations Functions
51 *=======================================================================*/
52
53
54
55 /*========================================================================
56 | PUBLIC procedure oir_calc_aging_buckets
57 |
58 | DESCRIPTION
59 | This procedure performs aging calculations within the context
60 | of a customer, site, currency and an aging bucket style.
61 | ----------------------------------------
62 |
63 | PSEUDO CODE/LOGIC
64 |
65 | PARAMETERS
66 | p_customer_id Customer ID
67 | p_as_of_date As of when the calculations are performed.
68 | p_currency_code Currency Code
69 | p_credit_option Age/Not Age Credits
70 | p_invoice_type_low
71 | p_invoice_type_high
72 | p_ps_max_id
73 | p_app_max_id
74 | p_bucket_name Aging Bucket Defination to use.
75 | p_session_id Added for ALL LOCATION Enhancement
76 |
77 | RETURNS
78 | p_outstanding_balance Account Balance
79 | p_bucket_titletop_0 Bucket i's Title
80 | p_bucket_titlebottom_0
81 | p_bucket_amount_0 Bucket i's Amount
82 | p_bucket_titletop_1
83 | p_bucket_titlebottom_1
84 | p_bucket_amount_1
85 | p_bucket_titletop_2
86 | p_bucket_titlebottom_2
87 | p_bucket_amount_2
88 | p_bucket_titletop_3
89 | p_bucket_titlebottom_3
90 | p_bucket_amount_3
91 | p_bucket_titletop_4
92 | p_bucket_titlebottom_4
93 | p_bucket_amount_4
94 | p_bucket_titletop_5
95 | p_bucket_titlebottom_5
96 | p_bucket_amount_5
97 | p_bucket_titletop_6
98 | p_bucket_titlebottom_6
99 | p_bucket_amount_6
100 | p_bucket_status_code0 Status Codes used in Acct. Details
101 | p_bucket_status_code1 Status Poplist
102 | p_bucket_status_code2
103 | p_bucket_status_code3
104 | p_bucket_status_code4
105 | p_bucket_status_code5
106 | p_bucket_status_code6
107 |
108 | KNOWN ISSUES
109 |
110 |
111 |
112 | NOTES
113 |
114 |
115 |
116 | MODIFICATION HISTORY
117 | Date Author Description of Changes
118 | 26-Oct-2002 J. Albowicz Created
122 *=======================================================================*/
119 | 19-Oct-2005 vgundlap p_bucket_name is the id of
120 | the aging bucket.Modified the
121 | where clause accordingly.
123
124 procedure oir_calc_aging_buckets (
125 p_customer_id IN NUMBER,
126 p_as_of_date IN DATE,
127 p_currency_code IN VARCHAR2,
128 p_credit_option IN VARCHAR2,
129 p_invoice_type_low IN VARCHAR2,
130 p_invoice_type_high IN VARCHAR2,
131 p_ps_max_id IN NUMBER,
132 p_app_max_id IN NUMBER,
133 p_bucket_name IN VARCHAR2,
134 p_outstanding_balance IN OUT NOCOPY VARCHAR2,
135 p_bucket_titletop_0 OUT NOCOPY VARCHAR2,
136 p_bucket_titlebottom_0 OUT NOCOPY VARCHAR2,
137 p_bucket_amount_0 IN OUT NOCOPY VARCHAR2,
138 p_bucket_titletop_1 OUT NOCOPY VARCHAR2,
139 p_bucket_titlebottom_1 OUT NOCOPY VARCHAR2,
140 p_bucket_amount_1 IN OUT NOCOPY VARCHAR2,
141 p_bucket_titletop_2 OUT NOCOPY VARCHAR2,
142 p_bucket_titlebottom_2 OUT NOCOPY VARCHAR2,
143 p_bucket_amount_2 IN OUT NOCOPY VARCHAR2,
144 p_bucket_titletop_3 OUT NOCOPY VARCHAR2,
145 p_bucket_titlebottom_3 OUT NOCOPY VARCHAR2,
146 p_bucket_amount_3 IN OUT NOCOPY VARCHAR2,
147 p_bucket_titletop_4 OUT NOCOPY VARCHAR2,
148 p_bucket_titlebottom_4 OUT NOCOPY VARCHAR2,
149 p_bucket_amount_4 IN OUT NOCOPY VARCHAR2,
150 p_bucket_titletop_5 OUT NOCOPY VARCHAR2,
151 p_bucket_titlebottom_5 OUT NOCOPY VARCHAR2,
152 p_bucket_amount_5 IN OUT NOCOPY VARCHAR2,
153 p_bucket_titletop_6 OUT NOCOPY VARCHAR2,
154 p_bucket_titlebottom_6 OUT NOCOPY VARCHAR2,
155 p_bucket_amount_6 IN OUT NOCOPY VARCHAR2,
156 p_bucket_status_code0 OUT NOCOPY VARCHAR2,
157 p_bucket_status_code1 OUT NOCOPY VARCHAR2,
158 p_bucket_status_code2 OUT NOCOPY VARCHAR2,
159 p_bucket_status_code3 OUT NOCOPY VARCHAR2,
160 p_bucket_status_code4 OUT NOCOPY VARCHAR2,
161 p_bucket_status_code5 OUT NOCOPY VARCHAR2,
162 p_bucket_status_code6 OUT NOCOPY VARCHAR2,
163 p_session_id IN NUMBER
164 ) IS
165 l_outstanding_balance NUMBER := 0;
166 l_bucket_amount_0 NUMBER := 0;
167 l_bucket_amount_1 NUMBER := 0;
168 l_bucket_amount_2 NUMBER := 0;
169 l_bucket_amount_3 NUMBER := 0;
170 l_bucket_amount_4 NUMBER := 0;
171 l_bucket_amount_5 NUMBER := 0;
172 l_bucket_amount_6 NUMBER := 0;
173 l_bucket_line_type ar_aging_bucket_lines.type%TYPE;
174 l_bucket_days_from NUMBER;
175 l_bucket_days_to NUMBER;
176
177 CURSOR c_sel_bucket_data is
178 select lines.days_start,
179 lines.days_to,
180 lines.type
181 from ar_aging_bucket_lines lines,
182 ar_aging_buckets buckets
183 where lines.aging_bucket_id = buckets.aging_bucket_id
184 and buckets.aging_bucket_id = to_number(p_bucket_name)
185 and nvl(buckets.status,'A') = 'A'
189 begin
186 order by lines.bucket_sequence_num
187 ;
188
190
191 calc_aging_buckets (
192 p_customer_id, p_as_of_date, p_currency_code,
193 p_credit_option, p_invoice_type_low, p_invoice_type_high,
194 p_ps_max_id, p_app_max_id, p_bucket_name, l_outstanding_balance,
195 p_bucket_titletop_0, p_bucket_titlebottom_0, l_bucket_amount_0,
196 p_bucket_titletop_1, p_bucket_titlebottom_1, l_bucket_amount_1,
197 p_bucket_titletop_2, p_bucket_titlebottom_2, l_bucket_amount_2,
198 p_bucket_titletop_3, p_bucket_titlebottom_3, l_bucket_amount_3,
199 p_bucket_titletop_4, p_bucket_titlebottom_4, l_bucket_amount_4,
200 p_bucket_titletop_5, p_bucket_titlebottom_5, l_bucket_amount_5,
201 p_bucket_titletop_6, p_bucket_titlebottom_6, l_bucket_amount_6,
202 p_session_id);
203
204 p_outstanding_balance := to_char(l_outstanding_balance,
205 fnd_currency_cache.get_format_mask(p_currency_code, 30));
206 p_bucket_amount_0 := to_char(l_bucket_amount_0,
207 fnd_currency_cache.get_format_mask(p_currency_code, 30));
208 p_bucket_amount_1 := to_char(l_bucket_amount_1,
209 fnd_currency_cache.get_format_mask(p_currency_code, 30));
210 p_bucket_amount_2 := to_char(l_bucket_amount_2,
211 fnd_currency_cache.get_format_mask(p_currency_code, 30));
212 p_bucket_amount_3 := to_char(l_bucket_amount_3,
213 fnd_currency_cache.get_format_mask(p_currency_code, 30));
214 p_bucket_amount_4 := to_char(l_bucket_amount_4,
215 fnd_currency_cache.get_format_mask(p_currency_code, 30));
216 p_bucket_amount_5 := to_char(l_bucket_amount_5,
217 fnd_currency_cache.get_format_mask(p_currency_code, 30));
218 p_bucket_amount_6 := to_char(l_bucket_amount_6,
219 fnd_currency_cache.get_format_mask(p_currency_code, 30));
220
221 p_bucket_status_code0 := '';
222 p_bucket_status_code1 := '';
223 p_bucket_status_code2 := '';
224 p_bucket_status_code3 := '';
225 p_bucket_status_code4 := '';
226 p_bucket_status_code5 := '';
227 p_bucket_status_code6 := '';
228
229 /* Need to construct the Aging Status Codes for use in the poplist,
230 which has format of OIR_AGING_<integer days from>_<integer days to>.
231 Encoding the days to/from was done to avoid having to re-write the
232 queries used in the iRec advanced search. */
233
234 OPEN c_sel_bucket_data;
235 FETCH c_sel_bucket_data INTO l_bucket_days_from, l_bucket_days_to , l_bucket_line_type;
236 /* Construct the status code for Bucket 1 */
237 IF c_sel_bucket_data%FOUND THEN
238 select decode(l_bucket_line_type ,
239 'DISPUTE_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
240 'PENDADJ_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
241 'DISPUTE_PENDADJ', 'OIR_AGING_' || l_bucket_line_type ,
242 'OIR_AGING_' || to_char(l_bucket_days_from) || '_' || to_char(l_bucket_days_to)
243 ) into p_bucket_status_code0
244 from dual ;
245
246 FETCH c_sel_bucket_data INTO l_bucket_days_from, l_bucket_days_to , l_bucket_line_type;
247 END IF;
248
249 /* Construct the status code for Bucket 2 */
250 IF c_sel_bucket_data%FOUND THEN
251 select decode(l_bucket_line_type ,
252 'DISPUTE_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
253 'PENDADJ_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
254 'DISPUTE_PENDADJ', 'OIR_AGING_' || l_bucket_line_type ,
255 'OIR_AGING_' || to_char(l_bucket_days_from) || '_' || to_char(l_bucket_days_to)
256 ) into p_bucket_status_code1
257 from dual ;
258
259 FETCH c_sel_bucket_data INTO l_bucket_days_from, l_bucket_days_to , l_bucket_line_type;
260 END IF;
261
262 /* Construct the status code for Bucket 3 */
263 IF c_sel_bucket_data%FOUND THEN
264 select decode(l_bucket_line_type ,
265 'DISPUTE_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
266 'PENDADJ_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
267 'DISPUTE_PENDADJ', 'OIR_AGING_' || l_bucket_line_type ,
271
268 'OIR_AGING_' || to_char(l_bucket_days_from) || '_' || to_char(l_bucket_days_to)
269 ) into p_bucket_status_code2
270 from dual ;
272 FETCH c_sel_bucket_data INTO l_bucket_days_from, l_bucket_days_to , l_bucket_line_type;
273 END IF;
274
275 /* Construct the status code for Bucket 4 */
276 IF c_sel_bucket_data%FOUND THEN
277 select decode(l_bucket_line_type ,
278 'DISPUTE_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
279 'PENDADJ_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
280 'DISPUTE_PENDADJ', 'OIR_AGING_' || l_bucket_line_type ,
281 'OIR_AGING_' || to_char(l_bucket_days_from) || '_' || to_char(l_bucket_days_to)
282 ) into p_bucket_status_code3
283 from dual ;
284
285 FETCH c_sel_bucket_data INTO l_bucket_days_from, l_bucket_days_to , l_bucket_line_type;
286 END IF;
287
288 /* Construct the status code for Bucket 5 */
289 IF c_sel_bucket_data%FOUND THEN
290 select decode(l_bucket_line_type ,
291 'DISPUTE_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
292 'PENDADJ_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
293 'DISPUTE_PENDADJ', 'OIR_AGING_' || l_bucket_line_type ,
294 'OIR_AGING_' || to_char(l_bucket_days_from) || '_' || to_char(l_bucket_days_to)
295 ) into p_bucket_status_code4
296 from dual ;
297
298 FETCH c_sel_bucket_data INTO l_bucket_days_from, l_bucket_days_to , l_bucket_line_type;
299 END IF;
300
301 /* Construct the status code for Bucket 6 */
302 IF c_sel_bucket_data%FOUND THEN
303 select decode(l_bucket_line_type ,
304 'DISPUTE_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
305 'PENDADJ_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
306 'DISPUTE_PENDADJ', 'OIR_AGING_' || l_bucket_line_type ,
307 'OIR_AGING_' || to_char(l_bucket_days_from) || '_' || to_char(l_bucket_days_to)
308 ) into p_bucket_status_code5
309 from dual ;
310
311 FETCH c_sel_bucket_data INTO l_bucket_days_from, l_bucket_days_to , l_bucket_line_type;
312 END IF;
313
314 /* Construct the status code for Bucket 7 */
315 IF c_sel_bucket_data%FOUND THEN
316 select decode(l_bucket_line_type ,
317 'DISPUTE_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
318 'PENDADJ_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
319 'DISPUTE_PENDADJ', 'OIR_AGING_' || l_bucket_line_type ,
320 'OIR_AGING_' || to_char(l_bucket_days_from) || '_' || to_char(l_bucket_days_to)
321 ) into p_bucket_status_code6
322 from dual ;
323
324 END IF;
325
326 CLOSE c_sel_bucket_data;
327
328 END oir_calc_aging_buckets;
329
330
331
332
333
334 /* This code is replicated here from ARP_CUSTOMER_AGING. */
335
336 --
337 PROCEDURE calc_aging_buckets (
338 p_customer_id IN NUMBER,
339 p_as_of_date IN DATE,
340 p_currency_code IN VARCHAR2,
341 p_credit_option IN VARCHAR2,
342 p_invoice_type_low IN VARCHAR2,
343 p_invoice_type_high IN VARCHAR2,
344 p_ps_max_id IN NUMBER DEFAULT 0,
345 p_app_max_id IN NUMBER DEFAULT 0,
346 p_bucket_name IN VARCHAR2,
347 p_outstanding_balance IN OUT NOCOPY NUMBER,
348 p_bucket_titletop_0 OUT NOCOPY VARCHAR2,
349 p_bucket_titlebottom_0 OUT NOCOPY VARCHAR2,
350 p_bucket_amount_0 IN OUT NOCOPY NUMBER,
351 p_bucket_titletop_1 OUT NOCOPY VARCHAR2,
352 p_bucket_titlebottom_1 OUT NOCOPY VARCHAR2,
353 p_bucket_amount_1 IN OUT NOCOPY NUMBER,
354 p_bucket_titletop_2 OUT NOCOPY VARCHAR2,
355 p_bucket_titlebottom_2 OUT NOCOPY VARCHAR2,
356 p_bucket_amount_2 IN OUT NOCOPY NUMBER,
357 p_bucket_titletop_3 OUT NOCOPY VARCHAR2,
358 p_bucket_titlebottom_3 OUT NOCOPY VARCHAR2,
359 p_bucket_amount_3 IN OUT NOCOPY NUMBER,
360 p_bucket_titletop_4 OUT NOCOPY VARCHAR2,
361 p_bucket_titlebottom_4 OUT NOCOPY VARCHAR2,
362 p_bucket_amount_4 IN OUT NOCOPY NUMBER,
363 p_bucket_titletop_5 OUT NOCOPY VARCHAR2,
364 p_bucket_titlebottom_5 OUT NOCOPY VARCHAR2,
365 p_bucket_amount_5 IN OUT NOCOPY NUMBER,
366 p_bucket_titletop_6 OUT NOCOPY VARCHAR2,
367 p_bucket_titlebottom_6 OUT NOCOPY VARCHAR2,
368 p_bucket_amount_6 IN OUT NOCOPY NUMBER,
369 p_session_id IN NUMBER
370 ) IS
371 v_amount_due_remaining NUMBER;
372 v_bucket_0 NUMBER;
373 v_bucket_1 NUMBER;
374 v_bucket_2 NUMBER;
375 v_bucket_3 NUMBER;
376 v_bucket_4 NUMBER;
377 v_bucket_5 NUMBER;
378 v_bucket_6 NUMBER;
379 v_bucket_category ar_aging_bucket_lines.type%TYPE;
380 --
381 v_bucket_line_type_0 ar_aging_bucket_lines.type%TYPE;
382 v_bucket_days_from_0 NUMBER;
383 v_bucket_days_to_0 NUMBER;
384 v_bucket_line_type_1 ar_aging_bucket_lines.type%TYPE;
385 v_bucket_days_from_1 NUMBER;
386 v_bucket_days_to_1 NUMBER;
387 v_bucket_line_type_2 ar_aging_bucket_lines.type%TYPE;
388 v_bucket_days_from_2 NUMBER;
389 v_bucket_days_to_2 NUMBER;
390 v_bucket_line_type_3 ar_aging_bucket_lines.type%TYPE;
391 v_bucket_days_from_3 NUMBER;
392 v_bucket_days_to_3 NUMBER;
393 v_bucket_line_type_4 ar_aging_bucket_lines.type%TYPE;
394 v_bucket_days_from_4 NUMBER;
395 v_bucket_days_to_4 NUMBER;
396 v_bucket_line_type_5 ar_aging_bucket_lines.type%TYPE;
397 v_bucket_days_from_5 NUMBER;
401 v_bucket_days_to_6 NUMBER;
398 v_bucket_days_to_5 NUMBER;
399 v_bucket_line_type_6 ar_aging_bucket_lines.type%TYPE;
400 v_bucket_days_from_6 NUMBER;
402 --
403 CURSOR c_sel_bucket_data is
404 select lines.days_start,
405 lines.days_to,
406 lines.report_heading1,
407 lines.report_heading2,
408 lines.type
409 from ar_aging_bucket_lines lines,
410 ar_aging_buckets buckets
411 where lines.aging_bucket_id = buckets.aging_bucket_id
412 and buckets.aging_bucket_id = to_number(p_bucket_name)
413 and nvl(buckets.status,'A') = 'A'
414 order by lines.bucket_sequence_num
415 ;
416 --
417 CURSOR c_buckets_cust IS
418 SELECT sum(amt), sum(b0*amt), sum(b1*amt), sum(b2*amt), sum(b3*amt), sum(b4*amt), sum(b5*amt), sum(b6*amt)
419 FROM (select decode(p_currency_code, NULL, ps.acctd_amount_due_remaining,
420 ps.amount_due_remaining) amt,
421 decode(v_bucket_line_type_0,
422 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
423 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
424 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
425 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
426 1),
427 decode( greatest(v_bucket_days_from_0,
428 ceil(p_as_of_date-ps.due_date)),
429 least(v_bucket_days_to_0,
430 ceil(p_as_of_date-ps.due_date)),1,
431 0)
432 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
433 decode(v_bucket_category,
434 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
435 1))
436 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
437 decode(v_bucket_category,
438 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
439 1))) b0,
440 decode(v_bucket_line_type_1,
441 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
442 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
443 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
444 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
445 1),
446 decode( greatest(v_bucket_days_from_1,
447 ceil(p_as_of_date-ps.due_date)),
448 least(v_bucket_days_to_1,
449 ceil(p_as_of_date-ps.due_date)),1,
450 0)
451 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
452 decode(v_bucket_category,
453 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
454 1))
455 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
456 decode(v_bucket_category,
457 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
458 1))) b1,
459 decode(v_bucket_line_type_2,
460 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
461 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
462 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
463 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
464 1),
465 decode( greatest(v_bucket_days_from_2,
466 ceil(p_as_of_date-ps.due_date)),
467 least(v_bucket_days_to_2,
468 ceil(p_as_of_date-ps.due_date)),1,
469 0)
470 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
471 decode(v_bucket_category,
472 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
473 1))
474 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
475 decode(v_bucket_category,
476 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
477 1))) b2,
478 decode(v_bucket_line_type_3,
479 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
480 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
481 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
482 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
483 1),
484 decode( greatest(v_bucket_days_from_3,
485 ceil(p_as_of_date-ps.due_date)),
486 least(v_bucket_days_to_3,
487 ceil(p_as_of_date-ps.due_date)),1,
488 0)
489 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
490 decode(v_bucket_category,
491 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
492 1))
493 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
494 decode(v_bucket_category,
495 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
496 1))) b3,
497 decode(v_bucket_line_type_4,
498 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
499 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
500 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
501 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
502 1),
503 decode( greatest(v_bucket_days_from_4,
504 ceil(p_as_of_date-ps.due_date)),
505 least(v_bucket_days_to_4,
506 ceil(p_as_of_date-ps.due_date)),1,
507 0)
508 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
509 decode(v_bucket_category,
510 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
511 1))
512 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
513 decode(v_bucket_category,
514 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
515 1))) b4,
516 decode(v_bucket_line_type_5,
517 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
518 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
519 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
520 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
521 1),
522 decode( greatest(v_bucket_days_from_5,
523 ceil(p_as_of_date-ps.due_date)),
524 least(v_bucket_days_to_5,
525 ceil(p_as_of_date-ps.due_date)),1,
526 0)
527 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
528 decode(v_bucket_category,
529 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
530 1))
531 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
532 decode(v_bucket_category,
533 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
534 1))) b5,
535 decode(v_bucket_line_type_6,
539 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
536 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
537 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
538 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
540 1),
541 decode( greatest(v_bucket_days_from_6,
542 ceil(p_as_of_date-ps.due_date)),
543 least(v_bucket_days_to_6,
544 ceil(p_as_of_date-ps.due_date)),1,
545 0)
546 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
547 decode(v_bucket_category,
548 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
549 1))
550 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
551 decode(v_bucket_category,
552 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
553 1))) b6
554 from ar_payment_schedules ps,
555 ar_irec_user_acct_sites_all AcctSites,
556 ra_customer_trx ct
557 where ps.status = 'OP'
558 and AcctSites.user_id=FND_GLOBAL.USER_ID()
559 and AcctSites.customer_id=ps.customer_id
560 and AcctSites.customer_site_use_id=ps.customer_site_use_id
561 and AcctSites.session_id=p_session_id
562 and AcctSites.customer_id= p_customer_id
563 and AcctSites.org_id = ps.org_id
564 and upper(p_currency_code) = ps.invoice_currency_code
565 --and 'CM' <> ps.class
566 and 'PMT' <> ps.class
567 and 'GUAR' <> ps.class
568 AND ps.customer_trx_id = ct.customer_trx_id
569 AND(TRUNC(ps.trx_date)) >= trunc(decode( nvl(FND_PROFILE.VALUE('ARI_FILTER_TRXDATE_OLDER'), 0), 0, ps.trx_date, (sysdate-FND_PROFILE.VALUE('ARI_FILTER_TRXDATE_OLDER'))))
570 AND ct.printing_option = decode(nvl(FND_PROFILE.VALUE('ARI_FILTER_DONOTPRINT_TRX'), 'NOT'), 'Y', 'PRI', ct.printing_option)
571 ) ;
572
573 CURSOR c_buckets_all IS
574 SELECT SUM(amt), SUM(amt*b0),SUM(amt*b1),SUM(amt*b2),SUM(amt*b3),SUM(amt*b4),SUM(amt*b5),SUM(amt*b6)
575 FROM (
576 select decode(p_currency_code, NULL, ps.acctd_amount_due_remaining,
577 ps.amount_due_remaining) amt,
578 decode(v_bucket_line_type_0,
579 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
580 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
581 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
582 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
583 1),
584 decode( greatest(v_bucket_days_from_0,
585 ceil(p_as_of_date-ps.due_date)),
586 least(v_bucket_days_to_0,
587 ceil(p_as_of_date-ps.due_date)),1,
588 0)
589 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
590 decode(v_bucket_category,
591 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
592 1))
593 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
594 decode(v_bucket_category,
595 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
596 1))) b0,
597 decode(v_bucket_line_type_1,
598 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
599 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
600 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
601 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
602 1),
603 decode( greatest(v_bucket_days_from_1,
604 ceil(p_as_of_date-ps.due_date)),
605 least(v_bucket_days_to_1,
606 ceil(p_as_of_date-ps.due_date)),1,
607 0)
608 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
609 decode(v_bucket_category,
610 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
611 1))
612 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
613 decode(v_bucket_category,
614 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
615 1))) b1,
616 decode(v_bucket_line_type_2,
617 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
618 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
619 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
620 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
621 1),
622 decode( greatest(v_bucket_days_from_2,
623 ceil(p_as_of_date-ps.due_date)),
624 least(v_bucket_days_to_2,
625 ceil(p_as_of_date-ps.due_date)),1,
626 0)
627 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
628 decode(v_bucket_category,
629 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
630 1))
631 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
632 decode(v_bucket_category,
633 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
634 1))) b2,
635 decode(v_bucket_line_type_3,
636 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
637 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
638 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
639 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
640 1),
641 decode( greatest(v_bucket_days_from_3,
642 ceil(p_as_of_date-ps.due_date)),
643 least(v_bucket_days_to_3,
644 ceil(p_as_of_date-ps.due_date)),1,
645 0)
646 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
647 decode(v_bucket_category,
648 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
649 1))
650 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
651 decode(v_bucket_category,
652 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
653 1))) b3,
654 decode(v_bucket_line_type_4,
655 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
656 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
657 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
658 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
659 1),
660 decode( greatest(v_bucket_days_from_4,
661 ceil(p_as_of_date-ps.due_date)),
662 least(v_bucket_days_to_4,
663 ceil(p_as_of_date-ps.due_date)),1,
664 0)
665 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
666 decode(v_bucket_category,
667 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
668 1))
669 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
670 decode(v_bucket_category,
674 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
671 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
672 1))) b4,
673 decode(v_bucket_line_type_5,
675 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
676 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
677 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
678 1),
679 decode( greatest(v_bucket_days_from_5,
680 ceil(p_as_of_date-ps.due_date)),
681 least(v_bucket_days_to_5,
682 ceil(p_as_of_date-ps.due_date)),1,
683 0)
684 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
685 decode(v_bucket_category,
686 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
687 1))
688 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
689 decode(v_bucket_category,
690 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
691 1))) b5,
692 decode(v_bucket_line_type_6,
693 'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
694 'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
695 'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
696 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
697 1),
698 decode( greatest(v_bucket_days_from_6,
699 ceil(p_as_of_date-ps.due_date)),
700 least(v_bucket_days_to_6,
701 ceil(p_as_of_date-ps.due_date)),1,
702 0)
703 * decode(nvl(ps.amount_in_dispute,0), 0, 1,
704 decode(v_bucket_category,
705 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
706 1))
707 * decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
708 decode(v_bucket_category,
709 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
710 1))) b6
711 from ar_payment_schedules ps,
712 ar_irec_user_acct_sites_all AcctSites,
713 ra_customer_trx ct
714 where ps.status = 'OP'
715 and AcctSites.user_id=FND_GLOBAL.USER_ID()
716 and AcctSites.customer_id=ps.customer_id
717 and AcctSites.customer_site_use_id=ps.customer_site_use_id
718 and AcctSites.session_id=p_session_id
719 and AcctSites.org_id = ps.org_id
720 and p_currency_code = ps.invoice_currency_code
721 and 'CM' <> ps.class
722 and 'PMT' <> ps.class
723 and 'GUAR' <> ps.class
724 AND ps.customer_trx_id = ct.customer_trx_id
725 AND(TRUNC(ps.trx_date)) >= trunc(decode( nvl(FND_PROFILE.VALUE('ARI_FILTER_TRXDATE_OLDER'), 0), 0, ps.trx_date, (sysdate-FND_PROFILE.VALUE('ARI_FILTER_TRXDATE_OLDER'))))
726 AND ct.printing_option = decode(nvl(FND_PROFILE.VALUE('ARI_FILTER_DONOTPRINT_TRX'), 'NOT'), 'Y', 'PRI', ct.printing_option)
727 );
728 BEGIN
729 --
730 -- Get the aging buckets definition.
731 --
732 OPEN c_sel_bucket_data;
733 FETCH c_sel_bucket_data INTO v_bucket_days_from_0, v_bucket_days_to_0,
734 p_bucket_titletop_0, p_bucket_titlebottom_0,
735 v_bucket_line_type_0;
736 IF c_sel_bucket_data%FOUND THEN
737 p_bucket_amount_0 := 0;
738 IF (v_bucket_line_type_0 = 'DISPUTE_ONLY') OR
739 (v_bucket_line_type_0 = 'PENDADJ_ONLY') OR
740 (v_bucket_line_type_0 = 'DISPUTE_PENDADJ') THEN
741 v_bucket_category := v_bucket_line_type_0;
742 END IF;
743 FETCH c_sel_bucket_data INTO v_bucket_days_from_1, v_bucket_days_to_1,
744 p_bucket_titletop_1, p_bucket_titlebottom_1,
745 v_bucket_line_type_1;
746 ELSE
747 p_bucket_titletop_0 := NULL;
748 p_bucket_titlebottom_0 := NULL;
749 p_bucket_amount_0 := NULL;
750 END IF;
751 IF c_sel_bucket_data%FOUND THEN
752 p_bucket_amount_1 := 0;
753 IF (v_bucket_line_type_1 = 'DISPUTE_ONLY') OR
754 (v_bucket_line_type_1 = 'PENDADJ_ONLY') OR
755 (v_bucket_line_type_1 = 'DISPUTE_PENDADJ') THEN
756 v_bucket_category := v_bucket_line_type_1;
757 END IF;
758 FETCH c_sel_bucket_data INTO v_bucket_days_from_2, v_bucket_days_to_2,
759 p_bucket_titletop_2, p_bucket_titlebottom_2,
760 v_bucket_line_type_2;
761 ELSE
762 p_bucket_titletop_1 := NULL;
763 p_bucket_titlebottom_1 := NULL;
764 p_bucket_amount_1 := NULL;
765 END IF;
766 IF c_sel_bucket_data%FOUND THEN
767 p_bucket_amount_2 := 0;
768 IF (v_bucket_line_type_2 = 'DISPUTE_ONLY') OR
769 (v_bucket_line_type_2 = 'PENDADJ_ONLY') OR
770 (v_bucket_line_type_2 = 'DISPUTE_PENDADJ') THEN
771 v_bucket_category := v_bucket_line_type_2;
772 END IF;
773 FETCH c_sel_bucket_data INTO v_bucket_days_from_3, v_bucket_days_to_3,
774 p_bucket_titletop_3, p_bucket_titlebottom_3,
775 v_bucket_line_type_3;
776 ELSE
777 p_bucket_titletop_2 := NULL;
778 p_bucket_titlebottom_2 := NULL;
779 p_bucket_amount_2 := NULL;
780 END IF;
781 IF c_sel_bucket_data%FOUND THEN
782 p_bucket_amount_3 := 0;
783 IF (v_bucket_line_type_3 = 'DISPUTE_ONLY') OR
784 (v_bucket_line_type_3 = 'PENDADJ_ONLY') OR
785 (v_bucket_line_type_3 = 'DISPUTE_PENDADJ') THEN
786 v_bucket_category := v_bucket_line_type_3;
787 END IF;
788 FETCH c_sel_bucket_data INTO v_bucket_days_from_4, v_bucket_days_to_4,
789 p_bucket_titletop_4, p_bucket_titlebottom_4,
790 v_bucket_line_type_4;
791 ELSE
792 p_bucket_titletop_3 := NULL;
793 p_bucket_titlebottom_3 := NULL;
794 p_bucket_amount_3 := NULL;
795 END IF;
796 IF c_sel_bucket_data%FOUND THEN
797 p_bucket_amount_4 := 0;
798 IF (v_bucket_line_type_4 = 'DISPUTE_ONLY') OR
802 END IF;
799 (v_bucket_line_type_4 = 'PENDADJ_ONLY') OR
800 (v_bucket_line_type_4 = 'DISPUTE_PENDADJ') THEN
801 v_bucket_category := v_bucket_line_type_4;
803 FETCH c_sel_bucket_data INTO v_bucket_days_from_5, v_bucket_days_to_5,
804 p_bucket_titletop_5, p_bucket_titlebottom_5,
805 v_bucket_line_type_5;
806 ELSE
807 p_bucket_titletop_4 := NULL;
808 p_bucket_titlebottom_4 := NULL;
809 p_bucket_amount_4 := NULL;
810 END IF;
811 IF c_sel_bucket_data%FOUND THEN
812 p_bucket_amount_5 := 0;
813 IF (v_bucket_line_type_5 = 'DISPUTE_ONLY') OR
814 (v_bucket_line_type_5 = 'PENDADJ_ONLY') OR
815 (v_bucket_line_type_5 = 'DISPUTE_PENDADJ') THEN
816 v_bucket_category := v_bucket_line_type_5;
817 END IF;
818 FETCH c_sel_bucket_data INTO v_bucket_days_from_6, v_bucket_days_to_6,
819 p_bucket_titletop_6, p_bucket_titlebottom_6,
820 v_bucket_line_type_6;
821 ELSE
822 p_bucket_titletop_5 := NULL;
823 p_bucket_titlebottom_5 := NULL;
824 p_bucket_amount_5 := NULL;
825 END IF;
826 IF c_sel_bucket_data%FOUND THEN
827 p_bucket_amount_6 := 0;
828 IF (v_bucket_line_type_6 = 'DISPUTE_ONLY') OR
829 (v_bucket_line_type_6 = 'PENDADJ_ONLY') OR
830 (v_bucket_line_type_6 = 'DISPUTE_PENDADJ') THEN
831 v_bucket_category := v_bucket_line_type_6;
832 END IF;
833 ELSE
834 p_bucket_titletop_6 := NULL;
835 p_bucket_titlebottom_6 := NULL;
836 p_bucket_amount_6 := NULL;
837 END IF;
838 CLOSE c_sel_bucket_data;
839 --
840 -- get the aging bucket balance. The v_bucket_ is either 1 or 0.
841 --
842 p_outstanding_balance := 0;
843 IF(p_customer_id IS NOT NULL) THEN
844 OPEN c_buckets_cust;
845 ELSE
846 OPEN c_buckets_all;
847 END IF;
848 -- LOOP
849 IF(p_customer_id IS NOT NULL) THEN
850 FETCH c_buckets_cust INTO v_amount_due_remaining,
851 v_bucket_0, v_bucket_1, v_bucket_2,
852 v_bucket_3, v_bucket_4, v_bucket_5, v_bucket_6;
853 ELSE
854 FETCH c_buckets_all INTO v_amount_due_remaining,
855 v_bucket_0, v_bucket_1, v_bucket_2,
856 v_bucket_3, v_bucket_4, v_bucket_5, v_bucket_6;
857 END IF;
858 -- EXIT WHEN c_buckets%NOTFOUND;
859 p_outstanding_balance := p_outstanding_balance + v_amount_due_remaining;
860 IF p_bucket_amount_0 IS NOT NULL THEN
861 p_bucket_amount_0 := p_bucket_amount_0 + v_bucket_0 ;
862 END IF;
863 IF p_bucket_amount_1 IS NOT NULL THEN
864 p_bucket_amount_1 := p_bucket_amount_1 + v_bucket_1 ;
865 END IF;
866 IF p_bucket_amount_2 IS NOT NULL THEN
867 p_bucket_amount_2 := p_bucket_amount_2 + v_bucket_2 ;
868 END IF;
869 IF p_bucket_amount_3 IS NOT NULL THEN
870 p_bucket_amount_3 := p_bucket_amount_3 + v_bucket_3 ;
871 END IF;
872 IF p_bucket_amount_4 IS NOT NULL THEN
873 p_bucket_amount_4 := p_bucket_amount_4 + v_bucket_4 ;
874 END IF;
875 IF p_bucket_amount_5 IS NOT NULL THEN
876 p_bucket_amount_5 := p_bucket_amount_5 + v_bucket_5 ;
877 END IF;
878 IF p_bucket_amount_6 IS NOT NULL THEN
879 p_bucket_amount_6 := p_bucket_amount_6 + v_bucket_6 ;
880 END IF;
881 -- END LOOP;
882 IF(p_customer_id IS NOT NULL) THEN
883 CLOSE c_buckets_cust;
884 ELSE
885 CLOSE c_buckets_all;
886 END IF;
887 --
888 EXCEPTION
889 WHEN OTHERS THEN
890 IF (PG_DEBUG = 'Y') THEN
891 arp_standard.debug('EXCEPTION: arp_customer_aging.calc_aging_buckets');
892 END IF;
893 END calc_aging_buckets;
894 --
895
896
897 -- Procedure is wrapper function to various ari_utilities calls.
898 -- The call is implemented as one call in order to limit the number
899 -- of times the DB tier is called.
900 PROCEDURE get_site_info(p_customer_id IN NUMBER,
901 p_addr_id IN NUMBER DEFAULT NULL,
902 p_site_use IN VARCHAR2 DEFAULT 'ALL',
903 p_contact_name OUT NOCOPY VARCHAR,
904 p_contact_phone OUT NOCOPY VARCHAR,
905 p_site_uses OUT NOCOPY VARCHAR,
906 p_bill_to_site_use_id OUT NOCOPY VARCHAR)
907 IS
908 BEGIN
909
910 p_contact_name := NULL;
911 -- Bug 15876881 -Removed call to ari_utilities.get_contact(p_customer_id, p_addr_id, p_site_use);
912 p_contact_phone := NULL;
913 -- Bug 15876881 -Removed call to ari_utilities.get_phone(p_customer_id, p_addr_id, 'ALL', p_site_use);
914
915 -- If p_addr is null then the function is being called for "All Locations" entry.
916 IF p_addr_id IS NULL
917 THEN
918 p_site_uses := NULL;
919 p_bill_to_site_use_id := NULL;
920 ELSE
921 p_site_uses := ari_utilities.get_site_uses( p_addr_id );
922 p_bill_to_site_use_id := ari_utilities.get_bill_to_site_use_id( p_addr_id );
923 END IF ;
924
925 EXCEPTION
926 WHEN OTHERS THEN
927 RAISE;
928 END;
929
930
931 /*========================================================================
932 | PUBLIC procedure get_print_request_url
933 |
934 | DESCRIPTION
935 | This procedure is used to get the status of the print request and
939 |
936 | and also its URL.
937 |
938 | PSEUDO CODE/LOGIC
940 | PARAMETERS
941 | p_request_id The print request ID
942 | p_gwyuid The gateway user ID
943 | p_two_task The value of TWO_TASK
944 | p_user_id The user ID
945 |
946 | RETURNS
947 | p_output_url The output URL for the request
948 | p_status The status of the print request
949 |
950 | KNOWN ISSUES
951 |
952 |
953 |
954 | NOTES
955 |
956 |
957 |
958 | MODIFICATION HISTORY
959 | Date Author Description of Changes
960 | 08-Aug-2003 yashaskar Created
961 |
962 *=======================================================================*/
963
964 PROCEDURE get_print_request_url(
965 p_request_id IN NUMBER,
966 p_gwyuid IN VARCHAR2,
967 p_two_task IN VARCHAR2,
968 p_user_id IN NUMBER,
969 p_output_url OUT NOCOPY VARCHAR2,
970 p_status OUT NOCOPY VARCHAR2
971 ) IS
972
973 -- l_output_url varchar2(2000);
974 l_valid_user varchar2(1):='N';
975 l_status varchar2(10);
976
977 BEGIN
978
979 /* Verify that the request belongs to this user */
980
981 select 'Y' into l_valid_user
982 from fnd_concurrent_requests fcr,
983 fnd_concurrent_programs fcp
984 where fcr.request_id = p_request_id
985 and fcr.requested_by = p_user_id
986 and fcp.concurrent_program_id = fcr.concurrent_program_id
987 and fcp.concurrent_program_name = 'RAXINV_SEL';
988
989 /* Get the request status */
990
991 if (l_valid_user = 'Y') then
992 select status_code into l_status
993 from fnd_concurrent_requests
994 where request_id = p_request_id;
995 end if;
996
997 p_status := l_status;
998 p_output_url := null;
999
1000 /* get the output url if the status is complete */
1001
1002 if ( l_status = 'C') then
1003 p_output_url := fnd_webfile.get_url( file_type => fnd_webfile.request_out,
1004 id => p_request_id,
1005 gwyuid => p_gwyuid,
1006 two_task => p_two_task,
1007 expire_time => 30 );
1008 -- dbms_output.put_line('URL for the output: '||p_output_url);
1009 elsif ( l_status = 'E') then
1010 p_status := 'E';
1011 else
1012 p_status := 'OTHER';
1013 end if;
1014
1015
1016 EXCEPTION
1017 when NO_DATA_FOUND then
1018 p_status := 'INVALID';
1019
1020 -- dbms_output.put_line('l_status : '||p_status);
1021
1022 END get_print_request_url;
1023
1024 /*========================================================================
1025 | PUBLIC procedure oir_bpa_print_invoices
1026 |
1027 | DESCRIPTION
1028 | This procedure is used to submit the print request to BPA and also
1029 | inserts the record in ar_irec_print_requests table.
1030 |
1031 | PSEUDO CODE/LOGIC
1032 |
1033 | PARAMETERS
1034 | p_id_list The ids to be submitted
1035 | p_list_type List type
1036 | p_description Description
1037 | p_template_id Template id
1038 | p_customer_id Customer id
1039 | p_site_id Customer Site Use Id
1040 |
1041 | RETURNS
1042 | x_req_id_list Request Id
1043 |
1044 | KNOWN ISSUES
1045 |
1046 |
1047 |
1048 | NOTES
1049 |
1050 |
1051 |
1052 | MODIFICATION HISTORY
1053 | Date Author Description of Changes
1054 | 01-Aug-2005 rsinthre Created
1055 | 13-May-2011 avepati Click on print button should print the invoices
1056 | Using cusotm BPA templates
1057 *=======================================================================*/
1058 PROCEDURE oir_bpa_print_invoices(
1059 p_id_list IN VARCHAR2,
1060 x_req_id_list OUT NOCOPY VARCHAR2,
1061 p_list_type IN VARCHAR2,
1062 p_description IN VARCHAR2 ,
1063 p_customer_id IN NUMBER,
1064 p_customer_site_id IN NUMBER DEFAULT NULL,
1065 p_user_name IN VARCHAR2
1066 ) IS
1067 l_start_location NUMBER default 0;
1068 l_request_id VARCHAR2(15);
1069 l_req_id_list VARCHAR2(4000);
1070 l_created_by NUMBER(15);
1071 l_creation_date DATE;
1072 l_last_update_login NUMBER(15);
1073 l_last_update_date DATE;
1074 l_last_updated_by NUMBER(15);
1075 l_template_id NUMBER(30);
1076
1077 BEGIN
1078
1079 l_template_id := FND_PROFILE.value('OIR_BPA_TEMPLATE_SELECTION');
1080 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1081 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME, 'oir_bpa_print_invoices, l_template_id = ' || l_template_id);
1082 end if;
1083
1084 AR_BPA_PRINT_CONC.process_print_request(p_id_list, x_req_id_list, p_list_type, p_description,l_template_id);
1085 l_req_id_list := x_req_id_list;
1086
1087 l_created_by := FND_GLOBAL.USER_ID;
1088 l_creation_date := sysdate;
1089 l_last_update_login := FND_GLOBAL.LOGIN_ID;
1090 l_last_update_date := sysdate;
1091 l_last_updated_by := FND_GLOBAL.USER_ID;
1095 loop
1092
1093 if(x_req_id_list <> '0') then
1094
1096 l_start_location := instr(l_req_id_list, ',', 1);
1097 if(l_start_location = 0) then
1098 INSERT INTO AR_IREC_PRINT_REQUESTS(REQUEST_ID, CUSTOMER_ID, CUSTOMER_SITE_USE_ID, REQUESTED_BY, PROGRAM_NAME, UPLOAD_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
1099 VALUES (to_number(l_req_id_list), p_customer_id, p_customer_site_id, l_created_by, p_description, sysdate, l_created_by, l_creation_date, l_last_update_login, l_last_update_date, l_last_updated_by);
1100 exit;
1101 else
1102 l_request_id := substr(l_req_id_list, 1, l_start_location-1);
1103 l_req_id_list := substr(l_req_id_list, l_start_location+1);
1104 INSERT INTO AR_IREC_PRINT_REQUESTS(REQUEST_ID, CUSTOMER_ID, CUSTOMER_SITE_USE_ID, REQUESTED_BY, PROGRAM_NAME, UPLOAD_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
1105 VALUES (to_number(l_request_id), p_customer_id, p_customer_site_id, l_created_by, p_description, sysdate, l_created_by, l_creation_date, l_last_update_login, l_last_update_date, l_last_updated_by);
1106 end if;
1107 end loop;
1108 commit;
1109 end if;
1110 END oir_bpa_print_invoices;
1111
1112 /*========================================================================
1113 | PUBLIC procedure oir_invoice_print_selected_invoices
1114 |
1115 | DESCRIPTION
1116 | This procedure submits cuncurrent request to print the
1117 | selected invoices .The notification is sent to the user
1118 | who has submited this request .
1119 | ----------------------------------------
1120 |
1121 | PSEUDO CODE/LOGIC
1122 |
1123 | PARAMETERS
1124 | p_resp_name Responsibility Name
1125 | p_user_name User Name
1126 | p_random_invoices_flag Randomly selected invoices or a range of invoices
1127 | p_invoice_list_string Customer_trx_ids of all selected invoices
1128 |
1129 | RETURNS
1130 | p_request_id Request ID
1131 |
1132 | KNOWN ISSUES
1133 |
1134 |
1135 |
1136 | NOTES
1137 |
1138 |
1139 |
1140 | MODIFICATION HISTORY
1141 | Date Author Description of Changes
1142 | 21-Jul-2009 avepati Created
1143 | 08-Apr-2011 rsinthre Bug 12329147 Removed the apps schema reference
1144 |
1145 *=======================================================================*/
1146 PROCEDURE oir_print_selected_invoices(
1147 p_resp_name IN VARCHAR2,
1148 p_user_name IN VARCHAR2,
1149 p_org_id IN NUMBER,
1150 p_random_invoices_flag IN VARCHAR2,
1151 p_invoice_list_string IN VARCHAR2,
1152 p_customer_id IN VARCHAR2,
1153 p_customer_site_id IN VARCHAR2,
1154 p_request_id OUT NOCOPY NUMBER
1155 ) IS
1156
1157 appl_id fnd_responsibility_vl.application_id%type;
1158 resp_id fnd_responsibility_vl.responsibility_id%type;
1159 resp_name fnd_responsibility_vl.responsibility_name%type;
1160 user_id fnd_user.user_id%type;
1161 user_name fnd_user.user_name%type;
1162 l_request_id NUMBER(30);
1163 l_message VARCHAR2(2000);
1164 l_program_name VARCHAR2(2000);
1165
1166 v_set_layout_option BOOLEAN;
1167 l_created_by NUMBER(15);
1168 l_creation_date DATE;
1169 l_last_update_login NUMBER(15);
1170 l_last_update_date DATE;
1171 l_last_updated_by NUMBER(15);
1172 l_server_id number(15);
1173 l_security_group_id NUMBER(15);
1174
1175 BEGIN
1176 -- Initialize FND Global
1177 FND_MSG_PUB.INITIALIZE;
1178
1179 l_created_by := FND_GLOBAL.USER_ID;
1180 l_creation_date := sysdate;
1181 l_last_update_login := FND_GLOBAL.LOGIN_ID;
1182 l_last_update_date := sysdate;
1183 l_last_updated_by := FND_GLOBAL.USER_ID;
1184
1185 /* Get the environment set up and profiles set for logging */
1186 select application_id, responsibility_id, responsibility_name
1187 into appl_id, resp_id, resp_name
1188 from fnd_responsibility_vl
1189 where responsibility_name = p_resp_name;
1190
1191 select user_id, user_name
1192 into user_id, user_name
1193 from fnd_user
1194 where user_name = p_user_name;
1195
1196 l_server_id := FND_GLOBAL.server_id;
1197 l_security_group_id := FND_GLOBAL.security_group_id;
1198
1199 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1200 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME, 'oir_print_selected_invoices, l_server_id = ' || l_server_id||' l_security_group_id = '||l_security_group_id);
1201 end if;
1202
1203 fnd_global.apps_initialize(user_id, resp_id, appl_id, l_security_group_id, l_server_id);
1204 fnd_log_repository.init;
1205 fnd_request.set_org_id(p_org_id);
1206
1207 -- arp_global.init_global;
1208
1209 -- Bug 3933606
1210 FND_MESSAGE.SET_NAME('AR','ARI_PRINT_PROGRAM_NAME');
1211 FND_MESSAGE.set_token('CUSTOMER_ID',p_customer_id);
1212 FND_MESSAGE.set_token('CUSTOMER_SITE_ID',p_customer_site_id);
1213 l_program_name := FND_MESSAGE.get;
1214
1215 -- Bug 3957478 - Single notification for multiple print requests
1216 -- Notification here removed
1217 -- Added for bug 9005896
1218 v_set_layout_option := fnd_request.add_layout(
1219 template_appl_name => 'AR' --application
1220 ,template_code => 'RAXINV_SEL'
1221 ,template_language => 'en'
1222 ,template_territory => 'US'
1223 ,output_format => 'PDF');
1224
1225 IF ( NOT v_set_layout_option ) THEN
1226 fnd_file.put_line( fnd_file.log,'Unable to apply template');
1227 END IF;
1228
1229 l_request_id := fnd_request.submit_request('AR', 'RAXINV_SEL', l_program_name,
1233 '','','','','','','','','','',
1230 null, false, 'TRX_NUMBER','','','','',
1231 '','','',p_customer_id,'','N','N','','SEL','1',
1232 'N','10',p_random_invoices_flag,p_invoice_list_string,'','','','','','',
1234 '','','','','','','','','','',
1235 '','','','','','','','','','',
1236 '','','','','','','','','','',
1237 '','','','','','','','','','',
1238 '','','','','','','','','','',
1239 '','','','','','','','','','',
1240 '','','','','');
1241 if ( l_request_id = 0 ) then
1242
1243 fnd_message.retrieve(l_message);
1244
1245 LOOP
1246 l_message:=FND_MSG_PUB.GET(p_encoded=>FND_API.G_FALSE);
1247 IF (l_message IS NULL)THEN
1248 EXIT;
1249 END IF;
1250 END LOOP;
1251 else
1252 p_request_id := l_request_id;
1253 commit;
1254 end if;
1255
1256 INSERT INTO AR_IREC_PRINT_REQUESTS(REQUEST_ID, CUSTOMER_ID, CUSTOMER_SITE_USE_ID, REQUESTED_BY, PROGRAM_NAME, UPLOAD_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
1257 VALUES (to_number(l_request_id), p_customer_id, p_customer_site_id, l_created_by, l_program_name, sysdate, l_created_by, l_creation_date, l_last_update_login, l_last_update_date, l_last_updated_by);
1258
1259 commit;
1260
1261 END oir_print_selected_invoices ;
1262
1263 /*========================================================================
1264 | PUBLIC procedure upload_ar_bank_branch_concur
1265 |
1266 | DESCRIPTION
1267 | This procedure submits cuncurrent request to upload AR_BANK_DIRECTORY
1268 | table data to HZ_PARTIES.
1269 | --------------------------------------------------------------------
1270 |
1271 | PSEUDO CODE/LOGIC
1272 |
1273 | PARAMETERS
1274 | p_import_new_banks_only - imports new banks
1275 |
1276 | RETURNS
1277 | ERRBUF Error Data
1278 | RETCODE Return Status
1279 |
1280 | KNOWN ISSUES
1281 |
1282 | NOTES
1283 |
1284 | MODIFICATION HISTORY
1285 | Date Author Description of Changes
1286 | 24-sep-2009 avepati Created
1287 |
1288 *=======================================================================*/
1289
1290 PROCEDURE upload_ar_bank_branch_concur( ERRBUF OUT NOCOPY VARCHAR2,
1291 RETCODE OUT NOCOPY VARCHAR2,
1292 p_import_new_banks_only IN VARCHAR2) IS
1293
1294 l_api_version NUMBER := 1.0;
1295 l_init_msg_list VARCHAR2(30) DEFAULT FND_API.G_TRUE;
1296 l_bank_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1297 l_branch_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1298 l_ext_bank_rec IBY_EXT_BANKACCT_PUB.extbank_rec_type;
1299 l_ext_branch_rec IBY_EXT_BANKACCT_PUB.ExtBankBranch_rec_type;
1300 l_bank_party_id ce_bank_branches_v.bank_party_id%TYPE;
1301 l_branch_party_id ce_bank_branches_v.branch_party_id%TYPE;
1302 l_import_new_banks_only varchar2(1);
1303
1304
1305 CURSOR ar_bank_branch_cur IS -- cursor to fetch the records from ar_Bank_directory table
1306 SELECT routing_number,
1307 new_routing_number,
1308 nvl(bank_name,routing_number) bank_name,
1309 nvl(bank_name,routing_number) AS branch_name,
1310 nvl(country,'US') AS country
1311 FROM ar_bank_directory;
1312
1313 CURSOR ce_bank_branch_cur(l_routing_number VARCHAR2) IS -- cursor to check whether the routing number exists in ce_bank_Branches_v or not
1314 SELECT bank_party_id,branch_party_id
1315 FROM ce_bank_branches_V
1316 WHERE branch_number = l_routing_number;
1317
1318 CURSOR ce_chk_bank_exists(l_bank_name VARCHAR2) IS -- cursor to check whether the bank exists in ce_bank_Branches_v or not
1319 SELECT bank_party_id,branch_party_id, branch_number
1320 FROM ce_bank_branches_V
1321 WHERE upper(bank_name) = upper(l_bank_name);
1322
1323 CURSOR ce_chk_branch_exists(l_bank_name VARCHAR2) IS -- cursor to check whether the branch exists in ce_bank_Branches_v or not
1324 SELECT bank_party_id,branch_party_id, branch_number
1325 FROM ce_bank_branches_V
1326 WHERE upper(bank_branch_name) = upper(l_bank_name);
1327
1328 CURSOR hz_obj_ver_num_cur(l_party_id VARCHAR2) IS -- cursor to get the object version number
1329 SELECT object_version_number from hz_parties where party_id = l_party_id;
1330
1331 ar_bank_branch_rec ar_bank_branch_cur%ROWTYPE;
1332 ce_bank_branch_rec ce_bank_branch_cur%ROWTYPE;
1333 ce_bank_exists_rec ce_chk_bank_exists%ROWTYPE;
1334 ce_branch_exists_rec ce_chk_branch_exists%ROWTYPE;
1335
1336 l_cr_return_status VARCHAR2(10) :='NORMAL';
1337 l_procedure_name VARCHAR2(50);
1338 l_total_recs NUMBER :=0;
1339 l_success_recs NUMBER :=0;
1340 l_failure_recs NUMBER :=0;
1341 l_obj_ver_num NUMBER :=1;
1342 l_msg_count NUMBER;
1346 BEGIN
1343 l_return BOOLEAN;
1344 l_is_new_bank_branch BOOLEAN := FALSE;
1345
1347
1348 l_procedure_name := '.import_ar_bank_brances_info';
1349 fnd_file.put_line( FND_FILE.LOG, 'Begin Procedure ' || 'upload_ar_bank_branch_concur' || l_procedure_name);
1350 fnd_file.put_line( FND_FILE.LOG, '------------------------------------------------------------------------------------------+');
1351 IF nvl(p_import_new_banks_only,'Y') = 'Y' THEN
1352 l_import_new_banks_only := 'Y';
1353 ELSE
1354 l_import_new_banks_only := 'N';
1355 END IF;
1356
1357 fnd_file.put_line( FND_FILE.LOG, 'Import New Banks Only :: ' || l_import_new_banks_only);
1358 fnd_file.put_line( FND_FILE.LOG, '------------------------------------------------------------------------------------------+');
1359
1360 FOR ar_bank_branch_rec IN ar_bank_branch_cur LOOP
1361 l_total_recs := l_total_recs+1;
1362
1363 IF (l_import_new_banks_only = 'N' ) THEN
1364
1365 OPEN ce_bank_branch_cur(ar_bank_branch_rec.routing_number);
1366 FETCH ce_bank_branch_cur INTO ce_bank_branch_rec;
1367
1368 -- check whether new routing number exixts
1369 IF(ar_bank_branch_rec.new_routing_number is not null) THEN -- If new routing number exists
1370
1371 -- check whether routing number exists in CE
1372 IF (ce_bank_branch_cur%FOUND) THEN -- If routing number exists in CE
1373 CLOSE ce_bank_branch_cur;
1374
1375 -- end date the existing routing number
1376 iby_ext_bankacct_pub.set_ext_bank_branch_end_date (
1377 -- IN parameters
1378 p_api_version => l_api_version,
1379 p_init_msg_list => l_init_msg_list,
1380 p_branch_id => ce_bank_branch_rec.branch_party_id,
1381 p_end_date => sysdate,
1382 -- OUT parameters
1383 x_return_status => RETCODE,
1384 x_msg_count => l_msg_count,
1385 x_msg_data => ERRBUF,
1386 x_response => l_branch_response );
1387
1388 IF(RETCODE = FND_API.G_RET_STS_ERROR ) THEN
1389
1390 fnd_file.put_line( FND_FILE.LOG, 'Error - Endating the Routing Number :: '||ar_bank_branch_rec.routing_number ||' , Bank Name :: '||ar_bank_branch_rec.bank_name);
1391 fnd_file.put_line( FND_FILE.LOG, 'ERRBUF :: '||ERRBUF);
1392 l_cr_return_status := 'WARNING';
1393 ELSE
1394 fnd_file.put_line( FND_FILE.LOG, 'Successful - Endated the Routing Number :: '||ar_bank_branch_rec.routing_number||' , Bank Name :: '||ar_bank_branch_rec.bank_name);
1395 l_success_recs := l_success_recs+1;
1396 END IF;
1397
1398 ELSE -- If routing number not exists in CE -- NO ACTIVITY REQUIRED
1399
1400 fnd_file.put_line( FND_FILE.LOG, 'Skipping - New Routing Number :: '||ar_bank_branch_rec.new_routing_number || ' exists for this Bank Name :: '||ar_bank_branch_rec.bank_name ||', Routing Number :: '||ar_bank_branch_rec.new_routing_number);
1401
1402 CLOSE ce_bank_branch_cur;
1403
1404 END IF; --ce_bank_branch_cur%FOUND
1405
1406 ELSE -- If new routing doesn't exists
1407
1408 -- check whether routing number exists in CE
1409 IF (ce_bank_branch_cur%FOUND) THEN -- If routing number exists in CE
1410 CLOSE ce_bank_branch_cur;
1411
1412 OPEN ce_chk_branch_exists(ar_bank_branch_rec.bank_name);
1413 FETCH ce_chk_branch_exists INTO ce_branch_exists_rec;
1414
1415
1416 -- check whether same branch name exists for this routing number in CE
1417 IF(ce_chk_branch_exists%NOTFOUND) THEN -- If branch associated with this routing number doesnot exists
1418 CLOSE ce_chk_branch_exists;
1419
1420 -- update CE Branch name with AR bank name
1421 OPEN hz_obj_ver_num_cur(ce_bank_branch_rec.branch_party_id); -- Fetches object version number for branch from hz_parites
1422 FETCH hz_obj_ver_num_cur INTO l_obj_ver_num;
1423 CLOSE hz_obj_ver_num_cur;
1424
1425 l_ext_branch_rec.branch_party_id := ce_bank_branch_rec.branch_party_id;
1426 l_ext_branch_rec.bank_party_id := ce_bank_branch_rec.bank_party_id;
1427 l_ext_branch_rec.branch_name := ar_bank_branch_rec.branch_name;
1428 l_ext_branch_rec.branch_number := ar_bank_branch_rec.routing_number;
1429 l_ext_branch_rec.branch_type := 'ABA';
1430 l_ext_branch_rec.bch_object_version_number :=l_obj_ver_num;
1431 l_ext_branch_rec.typ_object_version_number :=l_obj_ver_num;
1432
1433 iby_ext_bankacct_pub.update_ext_bank_branch (
1434 -- IN parameters
1435 p_api_version => l_api_version,
1436 p_init_msg_list => l_init_msg_list,
1440 x_msg_count => l_msg_count,
1437 p_ext_bank_branch_rec => l_ext_branch_rec,
1438 -- OUT parameters
1439 x_return_status => RETCODE,
1441 x_msg_data => ERRBUF,
1442 x_response => l_branch_response );
1443
1444 IF(RETCODE = FND_API.G_RET_STS_ERROR ) THEN
1445
1446 fnd_file.put_line( FND_FILE.LOG, 'Error - Updating Branch Info for Routing Number :: '||ar_bank_branch_rec.routing_number||' , with Branch Name :: '||ar_bank_branch_rec.branch_name);
1447 fnd_file.put_line( FND_FILE.LOG, 'ERRBUF :: '||ERRBUF);
1448 l_cr_return_status := 'WARNING';
1449
1450 ELSE
1451 fnd_file.put_line( FND_FILE.LOG, 'Successful - Updating Branch Info for Routing Number :: '||ar_bank_branch_rec.routing_number||' , with Branch Name :: '||ar_bank_branch_rec.bank_name);
1452 l_success_recs := l_success_recs+1;
1453 END IF;
1454
1455 ELSE -- If branch associated with this routing number exists -- NO ACTIVITY REQUIRED
1456
1457 fnd_file.put_line( FND_FILE.LOG, 'Skipping - This Routing Number :: '||ar_bank_branch_rec.routing_number||' , with Branch Name :: '||ar_bank_branch_rec.bank_name || ' already exists in ce_bank_branches_v');
1458 CLOSE ce_chk_branch_exists;
1459
1460 END IF; --ce_chk_branch_exists%NOTFOUND
1461
1462 ELSE -- If routing not found in ce
1463
1464 CLOSE ce_bank_branch_cur;
1465 l_import_new_banks_only := 'Y';
1466 l_is_new_bank_branch := TRUE;
1467
1468 END IF; -- ce_bank_branch_cur%FOUND
1469
1470 END IF; -- ar_bank_branch_rec.new_routing_number
1471
1472
1473 END IF; -- l_import_new_banks_only = 'N'
1474
1475
1476 IF (l_import_new_banks_only = 'Y' ) THEN
1477
1478 IF(l_is_new_bank_branch) THEN -- resetting the values
1479
1480 l_is_new_bank_branch := FALSE;
1481 l_import_new_banks_only := 'N';
1482
1483 END IF;
1484
1485 -- check whether new routing number exixts
1486 IF(ar_bank_branch_rec.new_routing_number is null) THEN
1487
1488 OPEN ce_bank_branch_cur(ar_bank_branch_rec.routing_number);
1489 FETCH ce_bank_branch_cur INTO ce_bank_branch_rec;
1490
1491 -- check whether routing number exists in ce
1492 IF (ce_bank_branch_cur%NOTFOUND) THEN -- If routing number doesn't exixts in CE
1493 CLOSE ce_bank_branch_cur;
1494
1495 OPEN ce_chk_bank_exists(ar_bank_branch_rec.bank_name);
1496 FETCH ce_chk_bank_exists INTO ce_bank_exists_rec;
1497
1498
1499 -- check whether bank associated to this routing number eixsts in ce
1500 IF(ce_chk_bank_exists%NOTFOUND) THEN
1501 CLOSE ce_chk_bank_exists;
1502
1503 --create bank and branch for this routing number
1504
1505 l_ext_bank_rec.bank_id := NULL;
1509 l_ext_bank_rec.country_code := ar_bank_branch_rec.country;
1506 l_ext_bank_rec.bank_name := ar_bank_branch_rec.bank_name;
1507 l_ext_bank_rec.bank_number := ar_bank_branch_rec.routing_number;
1508 l_ext_bank_rec.institution_type := 'BANK';
1510 l_ext_bank_rec.object_version_number := '1';
1511
1512 iby_ext_bankacct_pub.create_ext_bank(
1513 -- IN parameters
1514 p_api_version => l_api_version,
1515 p_init_msg_list => l_init_msg_list,
1516 p_ext_bank_rec => l_ext_bank_rec,
1517 -- OUT parameters
1518 x_bank_id => l_bank_party_id,
1519 x_return_status => RETCODE,
1520 x_msg_count => l_msg_count,
1521 x_msg_data => ERRBUF,
1522 x_response => l_bank_response );
1523
1524 IF(RETCODE = FND_API.G_RET_STS_ERROR ) THEN
1525
1526 fnd_file.put_line( FND_FILE.LOG, 'Error - Creating Bank Info for Routing Number :: '||ar_bank_branch_rec.routing_number||' , Bank Name :: '||ar_bank_branch_rec.bank_name);
1527 fnd_file.put_line( FND_FILE.LOG, 'ERRBUF :: '||ERRBUF);
1528 l_cr_return_status := 'WARNING';
1529
1530 ELSIF (RETCODE = FND_API.G_RET_STS_SUCCESS AND l_bank_party_id is not null) THEN
1531
1532 l_ext_branch_rec.branch_party_id := NULL;
1533 l_ext_branch_rec.bank_party_id := l_bank_party_id;
1534 l_ext_branch_rec.branch_name := ar_bank_branch_rec.branch_name;
1535 l_ext_branch_rec.branch_number := ar_bank_branch_rec.routing_number;
1536 l_ext_branch_rec.branch_type := 'ABA';
1537 l_ext_branch_rec.bch_object_version_number :='1';
1538 l_ext_branch_rec.typ_object_version_number :='1';
1539
1540 iby_ext_bankacct_pub.create_ext_bank_branch(
1541 -- IN parameters
1542 p_api_version => l_api_version,
1543 p_init_msg_list => l_init_msg_list,
1544 p_ext_bank_branch_rec => l_ext_branch_rec,
1545 -- OUT parameters
1546 x_branch_id => l_branch_party_id,
1547 x_return_status => RETCODE,
1548 x_msg_count => l_msg_count,
1549 x_msg_data => ERRBUF,
1550 x_response => l_branch_response);
1551
1552 IF(RETCODE = FND_API.G_RET_STS_ERROR ) THEN
1553
1554 fnd_file.put_line( FND_FILE.LOG, 'Error - Creating Branch Info for Routing Number :: '||ar_bank_branch_rec.routing_number||' , Branch Name :: '||ar_bank_branch_rec.branch_name);
1555 fnd_file.put_line( FND_FILE.LOG, 'ERRBUF :: '||ERRBUF);
1556 l_cr_return_status := 'WARNING';
1557
1558 ELSE
1559 fnd_file.put_line( FND_FILE.LOG, 'Successful - Creating Bank and Branch Info for Routing Number :: '||ar_bank_branch_rec.routing_number||' , Bank Name :: '||ar_bank_branch_rec.bank_name);
1560 l_success_recs := l_success_recs+1;
1561 END IF;
1562
1563 END IF; -- RETCODE = FND_API.G_RET_STS_ERROR
1564
1565
1566 ELSE -- if bank associated to this routing number eixsts in ce
1567
1568 CLOSE ce_chk_bank_exists;
1569
1570 -- create branch with this routing number for this bank
1571 l_ext_branch_rec.branch_party_id := NULL;
1572 l_ext_branch_rec.bank_party_id := ce_bank_exists_rec.bank_party_id;
1573 l_ext_branch_rec.branch_name := ar_bank_branch_rec.routing_number; -- passing routing number as branch name
1574 l_ext_branch_rec.branch_number := ar_bank_branch_rec.routing_number;
1575 l_ext_branch_rec.branch_type := 'ABA';
1576 l_ext_branch_rec.bch_object_version_number :='1';
1577 l_ext_branch_rec.typ_object_version_number :='1';
1578
1579 iby_ext_bankacct_pub.create_ext_bank_branch(
1580 -- IN parameters
1581 p_api_version => l_api_version,
1582 p_init_msg_list => l_init_msg_list,
1583 p_ext_bank_branch_rec => l_ext_branch_rec,
1584 -- OUT parameters
1585 x_branch_id => l_branch_party_id,
1586 x_return_status => RETCODE,
1587 x_msg_count => l_msg_count,
1588 x_msg_data => ERRBUF,
1589 x_response => l_branch_response);
1590
1591 IF(RETCODE = FND_API.G_RET_STS_ERROR ) THEN
1592
1593 fnd_file.put_line( FND_FILE.LOG, 'Error - Creating Branch Info for Routing Number :: '||ar_bank_branch_rec.routing_number||' , with Branch Name :: '||ar_bank_branch_rec.routing_number);
1594 fnd_file.put_line( FND_FILE.LOG, 'ERRBUF :: '||ERRBUF);
1595 l_cr_return_status := 'WARNING';
1596
1597 ELSE
1598 fnd_file.put_line( FND_FILE.LOG, 'Successful - Creating Branch Info for Routing Number :: '||ar_bank_branch_rec.routing_number||' , with Branch Name :: '||ar_bank_branch_rec.routing_number);
1599 l_success_recs := l_success_recs+1;
1600 END IF;
1601
1602 END IF; -- ce_chk_bank_exists%NOTFOUND
1603
1604 ELSE -- If routing number exixts in CE -- NO ACTIVITY REQUIRED
1605
1606 fnd_file.put_line( FND_FILE.LOG, 'Skipping - This Routing Number :: '||ar_bank_branch_rec.routing_number||' , associated with Bank Name :: '||ar_bank_branch_rec.bank_name || ' already exists in ce_bank_branches_v');
1610
1607 CLOSE ce_bank_branch_cur;
1608
1609 END IF; --ce_bank_branch_cur%NOTFOUND
1611 ELSE -- ar_bank_branch_rec.new_routing_number is not null
1612 fnd_file.put_line( FND_FILE.LOG, 'Skipping - New Routing Number Exits for this Routing Number :: '||ar_bank_branch_rec.routing_number||' , Bank Name :: '||ar_bank_branch_rec.bank_name || ' in AR_BANK_DIRECTORY');
1613 END IF; -- ar_bank_branch_rec.new_routing_number is null
1614
1615 END IF; -- l_import_new_banks_only = 'Y'
1616
1617 fnd_file.put_line( FND_FILE.LOG, '------------------------------------------------------------------------------------------+');
1618
1619
1620 END LOOP;
1621
1622 l_failure_recs :=l_total_recs - l_success_recs;
1623 IF (l_import_new_banks_only = 'Y' ) THEN
1624 fnd_file.put_line( FND_FILE.LOG, 'Total Records Processed :: '||l_total_recs);
1625 fnd_file.put_line( FND_FILE.LOG, 'Successfully Created Records :: '||l_success_recs);
1626 fnd_file.put_line( FND_FILE.LOG, 'Not Processed/Failed to Create Records :: '||l_failure_recs);
1627 ELSE
1628 fnd_file.put_line( FND_FILE.LOG, 'Total Records Processed :: '||l_total_recs);
1629 fnd_file.put_line( FND_FILE.LOG, 'Successfully Created/Updated Records :: '||l_success_recs);
1630 fnd_file.put_line( FND_FILE.LOG, 'Failed to Create/Update Records :: '||l_failure_recs);
1631 END IF;
1632
1633 fnd_file.put_line( FND_FILE.LOG, '------------------------------------------------------------------------------------------+');
1634
1635 IF ( l_cr_return_status = 'WARNING' AND l_import_new_banks_only = 'N') THEN
1636 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
1637 status => l_cr_return_status,
1638 message => 'Not all banks informartion were created/updated successfully. Please review the log file.');
1639 ELSE
1640 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
1641 status => l_cr_return_status,
1642 message => 'Not all banks informartion were created successfully. Please review the log file.');
1643
1644 END IF;
1645
1646 fnd_file.put_line( FND_FILE.LOG, 'End Procedure ' || 'upload_ar_bank_branch_concur' || l_procedure_name);
1647
1648 EXCEPTION WHEN OTHERS THEN
1649 l_cr_return_status := 'ERROR';
1650 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
1651 status => l_cr_return_status,
1652 message => 'Exporting Bank Information has failed. Please review the log file.');
1653
1654 fnd_file.put_line( FND_FILE.LOG,'Unexpected Exception in ' || 'upload_ar_bank_branch_concur' || l_procedure_name);
1655 fnd_file.put_line( FND_FILE.LOG,'ERROR =>'|| SQLERRM);
1656
1657 END upload_ar_bank_branch_concur;
1658
1659 /*========================================================================
1660 | PUBLIC procedure PURGE_IREC_PRINT_REQUESTS
1661 |
1662 | DESCRIPTION
1663 | This procedure submits cuncurrent request to purge AR_IREC_PRINT_REQUESTS
1664 | table data matching the purge process of FND_CONCURRENT_REQUESTS table.
1665 | --------------------------------------------------------------------
1666 |
1667 | PSEUDO CODE/LOGIC
1668 |
1669 | PARAMETERS
1670 | NONE
1671 |
1672 | RETURNS
1673 | ERRBUF Error Data
1674 | RETCODE Return Status
1675 | p_creation_Date Purge Date
1676 |
1677 | KNOWN ISSUES
1678 |
1679 | NOTES
1680 |
1681 | MODIFICATION HISTORY
1682 | Date Author Description of Changes
1683 | 02-Apr-2010 avepati Created
1684 |
1685 *=======================================================================*/
1686
1687 PROCEDURE PURGE_IREC_PRINT_REQUESTS( ERRBUF OUT NOCOPY VARCHAR2,
1688 RETCODE OUT NOCOPY VARCHAR2,
1689 p_creation_date in varchar2 ) IS
1690
1691 l_cp_return_status VARCHAR2(10) :='NORMAL';
1692 l_procedure_name VARCHAR2(50);
1693 l_fnd_request_date DATE;
1694 l_return BOOLEAN;
1695 msgbuf VARCHAR2(2000);
1696 numrows NUMBER;
1697
1698 BEGIN
1699
1700 l_procedure_name := '.PURGE_IREC_PRINT_REQUESTS';
1701 fnd_file.put_line( FND_FILE.LOG, 'Begin Procedure ' || l_procedure_name);
1702 fnd_file.put_line( FND_FILE.LOG, '+---------------------------------------------------------------------------+');
1703
1704 if(p_creation_date is NULL) then
1705 select min(request_date) into l_fnd_request_date from fnd_concurrent_requests;
1706 else
1707 l_fnd_request_date := FND_CONC_DATE.STRING_TO_DATE(p_creation_date); /* Convert character string to date */
1708 if(l_fnd_request_date is NULL) then
1709
1710 l_cp_return_status := 'ERROR';
1711 errbuf := 'Unexpected error converting character string to date'||l_fnd_request_date;
1712 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
1713 status => l_cp_return_status,
1714 message => errbuf);
1715
1716 retcode := '2';
1717 FND_FILE.put_line(FND_FILE.log,errbuf);
1718
1719 return;
1720 end if;
1721 end if;
1722
1723 fnd_message.set_name('FND', 'PURGING_UP_TO_DATE');
1724 fnd_message.set_token('ENTITY', 'AR_IREC_PRINT_REQUESTS');
1725 fnd_message.set_token('DATE', l_fnd_request_date);
1726 msgbuf := fnd_message.get;
1727 FND_FILE.put_line(FND_FILE.log, msgbuf);
1728
1729 select count(*) into numrows from ar_irec_print_requests where trunc(creation_date) < trunc(l_fnd_request_date);
1730 delete from ar_irec_print_requests where trunc(creation_date) < trunc(l_fnd_request_date);
1731 commit;
1732
1736 fnd_message.set_token('ROWS', numrows);
1733 fnd_file.put_line( FND_FILE.LOG, '+---------------------------------------------------------------------------+');
1734
1735 fnd_message.set_name('FND', 'GENERIC_ROWS_PROCESSED');
1737 msgbuf := fnd_message.get;
1738 FND_FILE.put_line(FND_FILE.log, msgbuf);
1739 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
1740 status => l_cp_return_status,
1741 message => 'Purging AR_IREC_PRINT_REQUESTS completed successfully');
1742 exception
1743 when others then
1744 errbuf := sqlerrm;
1745 retcode := '2';
1746 FND_FILE.put_line(FND_FILE.log,errbuf);
1747 l_cp_return_status := 'ERROR';
1748 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
1749 status => l_cp_return_status,
1750 message => 'Unexpected error during purge process');
1751 raise;
1752
1753 END PURGE_IREC_PRINT_REQUESTS;
1754
1755 /*========================================================================
1756 | PUBLIC procedure PURGE_IREC_USER_ACCT_SITES_ALL
1757 |
1758 | DESCRIPTION
1759 | This procedure submits cuncurrent request to purge PURGE_IREC_USER_ACCT_SITES_ALL
1760 | --------------------------------------------------------------------
1761 |
1762 | PSEUDO CODE/LOGIC
1763 |
1764 | PARAMETERS
1765 | NONE
1766 |
1767 | RETURNS
1768 | ERRBUF Error Data
1769 | RETCODE Return Status
1770 | p_creation_Date Purge Date
1771 |
1772 | KNOWN ISSUES
1773 |
1774 | NOTES
1775 |
1776 | MODIFICATION HISTORY
1777 | Date Author Description of Changes
1778 | 14-Apr-2010 avepati Created
1779 |
1780 *=======================================================================*/
1781
1782 PROCEDURE PURGE_IREC_USER_ACCT_SITES_ALL( ERRBUF OUT NOCOPY VARCHAR2,
1783 RETCODE OUT NOCOPY VARCHAR2,
1784 p_creation_date in varchar2 ) IS
1785
1786 l_cp_return_status VARCHAR2(10) :='NORMAL';
1787 l_procedure_name VARCHAR2(50);
1788 l_purge_date DATE;
1789 l_return BOOLEAN;
1790 msgbuf VARCHAR2(2000);
1791 numrows NUMBER;
1792
1793 BEGIN
1794
1795 l_procedure_name := '.PURGE_IREC_USER_ACCT_SITES_ALL';
1796 fnd_file.put_line( FND_FILE.LOG, 'Begin Procedure ' || l_procedure_name);
1797 fnd_file.put_line( FND_FILE.LOG, '+---------------------------------------------------------------------------+');
1798
1799 if(p_creation_date is NULL) then
1800 select trunc(sysdate)-1 into l_purge_date from dual;
1801 else
1802 l_purge_date := FND_CONC_DATE.STRING_TO_DATE(p_creation_date); /* Convert character string to date */
1803 if(l_purge_date is NULL) then
1804
1805 l_cp_return_status := 'ERROR';
1806 errbuf := 'Unexpected error converting character string to date'||l_purge_date;
1807 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
1808 status => l_cp_return_status,
1809 message => errbuf);
1810
1811 retcode := '2';
1812 FND_FILE.put_line(FND_FILE.log,errbuf);
1813
1814 return;
1815 end if;
1816 end if;
1817
1818 fnd_message.set_name('FND', 'PURGING_UP_TO_DATE');
1819 fnd_message.set_token('ENTITY', 'AR_IREC_USER_ACCT_SITES_ALL');
1820 fnd_message.set_token('DATE', l_purge_date);
1821 msgbuf := fnd_message.get;
1822 FND_FILE.put_line(FND_FILE.log, msgbuf);
1823
1824 select count(*) into numrows from ar_irec_user_acct_sites_all where trunc(creation_date) < trunc(l_purge_date);
1825 delete from ar_irec_user_acct_sites_all where trunc(creation_date) < trunc(l_purge_date);
1826 commit;
1827
1828 fnd_file.put_line( FND_FILE.LOG, '+---------------------------------------------------------------------------+');
1829
1830 fnd_message.set_name('FND', 'GENERIC_ROWS_PROCESSED');
1831 fnd_message.set_token('ROWS', numrows);
1832 msgbuf := fnd_message.get;
1833 FND_FILE.put_line(FND_FILE.log, msgbuf);
1834 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
1835 status => l_cp_return_status,
1836 message => 'Purging AR_IREC_USER_ACCT_SITES_ALL completed successfully');
1837 exception
1838 when others then
1839 errbuf := sqlerrm;
1840 retcode := '2';
1841 FND_FILE.put_line(FND_FILE.log,errbuf);
1842 l_cp_return_status := 'ERROR';
1843 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
1844 status => l_cp_return_status,
1845 message => 'Unexpected error during purge process');
1846 raise;
1847
1848 END PURGE_IREC_USER_ACCT_SITES_ALL;
1849
1850
1851 END ARI_DB_UTILITIES;