[Home] [Help]
PACKAGE BODY: APPS.FV_RECEIVABLES_ACTIVITY_PKG
Source
1 PACKAGE BODY fv_receivables_activity_pkg AS
2 /* $Header: FVXDCDFB.pls 120.7 2006/07/31 13:16:56 kbhatt noship $ */
3 -- ======================================================================
4 -- Variable Naming Conventions
5 -- ======================================================================
6 -- 1. Input/Output Parameter ,global variables: "vp_<Variable Name>"
7 -- 2. Other Global Variables : "vg_<Variable_Name>"
8 -- 3. Procedure Level local variables : "amt / num _<Variable_Name>"
9 -- 4. PL/SQL Table variables : "vt_<Variable_Name>"
10 -- 5. User Defined Excpetions : "e_<Exception_Name>"
11 -- ======================================================================
12 -- Parameter Global Variable Declarations
13 -- ======================================================================
14 vp_errbuf VARCHAR2(5000) ;
15 vp_retcode NUMBER ;
16 vp_sob_id Gl_Sets_Of_Books.set_of_books_id%TYPE ;
17 vp_nonfed_customer_class ar_lookups.lookup_code%TYPE;
18 vp_type_of_receivable FV_RECEIVABLE_TYPES_ALL.receivable_type%TYPE ;
19 vp_write_off_activity_1 AR_ADJUSTMENTS_ALL.Receivables_trx_id%type;
20 vp_write_off_activity_2 AR_ADJUSTMENTS_ALL.Receivables_trx_id%type;
21 vp_write_off_activity_3 AR_ADJUSTMENTS_ALL.Receivables_trx_id%type;
22
23 --Bug 5414783
24 --vp_org_id NUMBER; -- Bug 4655467
25
26 -- ======================================================================
27 -- Other Global Variable Declarations
28 -- ======================================================================
29 vg_end_date DATE;
30 g_module_name VARCHAR2(100);
31 g_as_of_date DATE;
32 vl_fy_begin_date DATE;
33 vl_fy_end_date DATE;
34
35 TYPE g_rec_desc IS RECORD (cash_receipt_id NUMBER
36 ,amount NUMBER
37 ,desc_type VARCHAR2(20)) ;
38
39 TYPE g_rec_desc_type IS TABLE OF g_rec_desc
40 INDEX BY BINARY_INTEGER;
41
42 -- ------------------------------------------------------------------
43 -- Procedure Main
44 -- ------------------------------------------------------------------
45 --Main procedure is called from concurrent program.
46 --This procedure calls all the subsequent procedures
47 --in the receivables activity process
48 -- ------------------------------------------------------------------
49 PROCEDURE Main(
50 errbuf OUT NOCOPY VARCHAR2,
51 retcode OUT NOCOPY NUMBER,
52 p_set_of_books_id NUMBER,
53 p_reporting_entity_code VARCHAR2,
54 p_fiscal_year NUMBER,
55 p_quarter NUMBER,
56 p_reported_by VARCHAR2,
57 p_type_of_receivable VARCHAR2,
58 p_write_off_activity_1 VARCHAR2,
59 p_write_off_activity_2 VARCHAR2,
60 p_write_off_activity_3 VARCHAR2,
61 p_nonfed_customer_class VARCHAR2,
62 p_footnotes VARCHAR2,
63 p_preparer_name VARCHAR2,
64 p_preparer_phone VARCHAR2,
65 p_preparer_fax_number VARCHAR2,
66 p_preparer_email VARCHAR2,
67 p_supervisor_name VARCHAR2,
68 p_supervisor_phone VARCHAR2,
69 p_supervisor_email VARCHAR2,
70 p_address_line_1 VARCHAR2,
71 p_address_line_2 VARCHAR2,
72 p_address_line_3 VARCHAR2,
73 p_city VARCHAR2,
74 p_state VARCHAR2,
75 p_postal_code VARCHAR2)
76 IS
77
78 l_module_name VARCHAR2(200);
79 BEGIN
80 vp_retcode := 0;
81 g_module_name := 'fv.plsql.fv_receivables_activity_pkg.';
82
83 l_module_name := g_module_name || 'Main';
84
85 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
86 'START OF RECEIVABLES ACTIVITY MAIN PROCESS ......');
87
88 -- Load the parameter global variables
89 vp_sob_id := p_set_of_books_id ;
90 vp_nonfed_customer_class := p_nonfed_customer_class;
91 vp_type_of_receivable := p_type_of_receivable ;
92 vp_write_off_activity_1 := p_write_off_activity_1 ;
93 vp_write_off_activity_2 := p_write_off_activity_2 ;
94 vp_write_off_activity_3 := p_write_off_activity_3 ;
95
96 --Bug 5414783
97 /*
98 vp_org_id:=mo_global.get_current_org_id; -- Bug 4655467
99 fnd_request.set_org_id(vp_org_id); -- Bug 4655467
100 */
101
102 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
103 l_module_name,' SET OF BOOKS ID IS:'||TO_CHAR(vp_sob_id));
104
105 -- Deriving the End Date for the period for which Report is being run
106 BEGIN
107 SELECT MIN(start_date) , MAX(end_date)
108 INTO vl_fy_begin_date , vl_fy_end_date
109 FROM gl_period_statuses
110 WHERE period_year = p_fiscal_year
111 AND set_of_books_id = P_SET_OF_BOOKS_ID
112 AND application_id = '101';
113 EXCEPTION
114 WHEN NO_DATA_FOUND THEN
115 vp_retcode := SQLCODE ;
116 vp_errbuf := SQLERRM ||'Parameter Fiscal Year is not defined'
117 || 'for the Set of Books' ;
118 errbuf := vp_errbuf;
119 retcode := vp_retcode;
120 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name
121 ||'Undefined Fiscal Year',VP_ERRBUF) ;
122 RETURN ;
123 END;
124
125 BEGIN
126 IF P_QUARTER IS NOT NULL THEN
127 SELECT MAX (end_date)
128 INTO vg_end_date
129 FROM gl_period_statuses
130 WHERE period_year = P_FISCAL_YEAR
131 AND set_of_books_id = vp_sob_id
132 AND application_id = '101'
133 AND quarter_num = P_QUARTER;
134 ELSE
135 vg_end_date := vl_fy_end_date;
136 END IF;
137 EXCEPTION
138 WHEN NO_DATA_FOUND THEN
139 vp_retcode := SQLCODE ;
140 vp_errbuf := SQLERRM ||'Parameter Quarter is not defined.' ;
141 errbuf := vp_errbuf;
142 retcode := vp_retcode;
143 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name
144 ||'Undefined Quarter',VP_ERRBUF) ;
145 RETURN ;
146 END;
147 -- If the report_by is System Date then the SYSDATE is considered as As_of_date
148 -- If the report_by is Querter End Date then Querter/Year End Date is considered
149 -- as As_of_Date.
150 -- This g_as_of_date is used to calculate the age of the purchase
151 -- invoice.
152 IF p_reported_by = 'SYSDATE' THEN
153 g_as_of_date := TRUNC(sysdate);
154 ELSE
155 g_as_of_date := TRUNC(vg_end_date);
156 END IF;
157 -- purge the temp table
158 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
159 'Purging Temporary Table...') ;
160 DELETE FROM fv_receivables_activity_temp;
161
162 -- populate temp table for Part I section B and PartII SectionA and SectionB
163 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
164 'Populating Temp Table with' ||
165 'Part I Section B, Part II Section A, Section B Values...') ;
166 Populate_IB_IIAB ();
167
168 IF vp_retcode = 0 THEN
169 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
170 'Populating Temp Table with'||
171 'Part I Section A and Part II Section C Values.....') ;
172 Populate_IA_IIC;
173 END IF;
174
175
176 IF vp_retcode = 0 THEN
177 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
178 'SUBMITTING FUNDS AVAILABILITY REPORTS .....');
179 Submit_Report (p_set_of_books_id,
180 p_reporting_entity_code ,
181 p_fiscal_year ,
182 p_quarter ,
183 p_reported_by,
184 p_type_of_receivable ,
185 p_footnotes ,
186 p_preparer_name ,
187 p_preparer_phone ,
188 p_preparer_fax_number ,
189 p_preparer_email ,
190 p_supervisor_name ,
191 p_supervisor_phone ,
192 p_supervisor_email ,
193 p_address_line_1 ,
194 p_address_line_2 ,
195 p_address_line_3 ,
196 p_city ,
197 p_state ,
198 p_postal_code ) ;
199 END IF;
200
201 -- Checking for errors
202 IF vp_retcode <> 0 THEN
203 errbuf := vp_errbuf;
204 retcode := vp_retcode;
205 ROLLBACK;
206 ELSE
207 COMMIT;
208 END IF;
209
210
211 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
212 'END THE RECEIVABLES ACTIVITY MAIN PROCESS ......');
213
214
215 EXCEPTION
216 WHEN OTHERS THEN
217 vp_retcode := SQLCODE ;
218 vp_errbuf := SQLERRM ||' -- Error in Main procedure' ;
219 errbuf := vp_errbuf;
220 retcode := vp_retcode;
221 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
222 l_module_name||'.final_exception',VP_ERRBUF) ;
223 RETURN ;
224 END Main;
225
226 -- ------------------------------------------------------------------
227 -- Procedure Populate_IB_IIAB
228 -- Procedure populate_PartISecB_PartIISecASecB is called from the
229 -- Main procedure.
230 -- This procedure populated the temp table for Report Sections
231 -- PartI SecB , and PartII SecA and SecB
232 -- ------------------------------------------------------------------
233
234 PROCEDURE Populate_IB_IIAB IS
235
236 l_module_name VARCHAR2(200);
237
238 CURSOR CUR_IB_IIAB IS
239 SELECT rct.customer_trx_id,
240 rct.related_customer_trx_id,
241 rct.trx_date,
242 aps.amount_due_original,
243 aps.amount_due_remaining,
244 aps.actual_date_closed,
245 aps.due_date,
246 aps.class,
247 aps.payment_schedule_id,
248 interface_header_attribute3 created_from,
249 hzp1.category_code customer_category_code
250 --fvis.status
251 FROM RA_CUSTOMER_TRX_ALL rct,
252 RA_CUST_TRX_LINE_GL_DIST_ALL rctlgd,
253 AR_PAYMENT_SCHEDULES_ALL aps,
254 --RA_CUSTOMERS rc, Bug#4476059 Quick Change
255 hz_cust_accounts hzca1,
256 hz_parties hzp1,
257 FV_RECEIVABLE_TYPES_ALL frt,
258 FV_REC_CUST_TRX_TYPES_ALL fctt,
259 GL_CODE_COMBINATIONS glc
260 --FV_INVOICE_STATUSES_ALL fvis
261 WHERE rct.customer_trx_id = rctlgd.customer_trx_id
262 AND rct.trx_date <= vg_end_date
263 AND rctlgd.account_class = 'REC'
264 AND rctlgd.set_of_books_id = vp_sob_id
265 AND aps.customer_trx_id = rct.customer_trx_id
266 AND rct.bill_to_customer_id = hzca1.cust_account_id
267 AND hzca1.party_id = hzp1.party_id
268 AND rctlgd.code_combination_id = glc.code_combination_id
269 AND hzca1.customer_class_code = vp_nonfed_customer_class
270 AND aps.class = 'INV'
271 AND frt.receivable_type_id = fctt.receivable_type_id
272 AND frt.receivable_type = vp_type_of_receivable
273 AND fctt.cust_trx_type_id = rct.cust_trx_type_id
274 AND frt.SET_OF_BOOKS_ID = vp_sob_id -- Bug 4655467
275 --AND frt.org_id = vp_org_id -- Bug 4655467
276 AND rct.SET_OF_BOOKS_ID = vp_sob_id -- Bug 4655467
277 --AND rct.org_id = vp_org_id -- Bug 4655467
278 --added to main query for delinquent debt 180 days or less
279 --AND aps.customer_trx_id = fvis.customer_trx_id (+)
280 ---AND vg_end_date BETWEEN NVL(fvis.start_date, vg_end_date)
281 --added to main query for delinquent debt 180 daysorless
282 --AND NVL(fvis.end_date, vg_end_date)
283 UNION
284 SELECT rct2.customer_trx_id,
285 rct2.related_customer_trx_id,
286 rct2.trx_date,
287 aps2.amount_due_original,
288 aps2.amount_due_remaining,
289 aps2.actual_date_closed,
290 aps2.due_date,
291 aps2.class,
292 aps2.payment_schedule_id,
293 rct2.interface_header_attribute3 created_from,
294 hzp2.category_code customer_category_code
295 -- fvis.status
296 FROM RA_CUSTOMER_TRX_ALL rct2,
297 AR_PAYMENT_SCHEDULES_ALL aps2,
298 --RA_CUSTOMERS rc2 Bug#4476059 Quick Change
299 hz_cust_accounts hzca2,
300 hz_parties hzp2
301 -- FV_INVOICE_STATUSES_ALL fvis
302 where aps2.class in ('DM','CM')
303 and hzca2.cust_account_id = rct2.bill_to_customer_id
304 and hzca2.party_id = hzp2.party_id
305 and rct2.customer_trx_id = aps2.customer_trx_id
306 and rct2.SET_OF_BOOKS_ID = vp_sob_id -- Bug 4655467
307 --and rct2.org_id = vp_org_id -- Bug 4655467
308 --added to main query for delinquent debt 180 days or less
309 --and aps2.customer_trx_id = fvis.customer_trx_id (+)
310 and rct2.related_customer_trx_id in
311 (Select rct3.customer_trx_id
312 from RA_CUSTOMER_TRX_ALL rct3,
313 RA_CUST_TRX_LINE_GL_DIST_ALL rctlgd3,
314 --RA_CUSTOMERS rc3,-- Bug#4476059 Quick Change
315 hz_cust_accounts hzca3,
316 hz_parties hzp3,
317 AR_PAYMENT_SCHEDULES_ALL aps3,
318 FV_RECEIVABLE_TYPES_ALL frt3,
319 FV_REC_CUST_TRX_TYPES_ALL fctt3,
320 -- FV_FUND_PARAMETERS fp3, -- Bug 4655467
321 -- FV_TREASURY_SYMBOLS fts3, -- Bug 4655467
322 GL_CODE_COMBINATIONS glc3
323 where rct3.customer_trx_id = rctlgd3.customer_trx_id
324 and rct3.trx_date <= vg_end_date
325 and rctlgd3.account_class ='REC'
326 and rctlgd3.set_of_books_id = vp_sob_id
327 and frt3.SET_OF_BOOKS_ID = vp_sob_id -- Bug 4655467
328 -- and frt3.org_id = vp_org_id -- Bug 4655467
329 and rct3.SET_OF_BOOKS_ID = vp_sob_id -- Bug 4655467
330 -- and rct3.org_id = vp_org_id -- Bug 4655467
331 and aps3.customer_trx_id = rct3.customer_trx_id
332 and rct3.bill_to_customer_id = hzca3.cust_account_id
333 and hzca3.party_id = hzp3.party_id
334 and rctlgd3.code_combination_id = glc3.code_combination_id
335 and hzca3.customer_class_code = vp_nonfed_customer_class
336 and aps3.class = 'INV'
337 and frt3.receivable_type = vp_type_of_receivable
338 and frt3.receivable_type_id = fctt3.receivable_type_id
339 and fctt3.cust_trx_type_id = rct3.cust_trx_type_id);
340 -- AND vg_end_date BETWEEN NVL(fvis.start_date, vg_end_date)
341 --added to main query for delinquent debt 180 days or less;
342 ---AND NVL(fvis.end_date, vg_end_date) ;
343
344 CURSOR CUR_INV_STATUS( p_customer_trx_id NUMBER ) IS
345 SELECT status
346 FROM ra_customer_trx_all rct , fv_invoice_statuses_all fvis
347 WHERE rct.customer_trx_id = P_customer_trx_id
348 AND fvis.customer_trx_id = rct.customer_trx_id
349 AND vg_end_date BETWEEN NVL(fvis.start_date,vg_end_date)
350 AND NVL(fvis.end_date,vg_end_date);
351
352 amt_tot_delinq_bankruptcy NUMBER ;
353 amt_tot_delinq_foreclosure NUMBER ;
354 amt_tot_delinq_forbearance NUMBER ;
355 amt_tot_delinq_collection NUMBER ;
356 amt_tot_delinq_litigation NUMBER ;
357 amt_tot_delinq_internal_offset NUMBER ;
358 amt_tot_delinq_garnishment NUMBER ;
359 amt_tot_delinq_cross NUMBER ;
360 amt_tot_delinq_treasury_offset NUMBER ;
361 amt_tot_delinq_agency NUMBER ;
362 amt_tot_delinq_other NUMBER ;
363
364 num_tot_delinq_bankruptcy NUMBER ;
365 num_tot_delinq_foreclosure NUMBER ;
366 num_tot_delinq_forbearance NUMBER ;
367 num_tot_delinq_collection NUMBER ;
368 num_tot_delinq_litigation NUMBER ;
369 num_tot_delinq_internal_offset NUMBER ;
370 num_tot_delinq_garnishment NUMBER ;
371 num_tot_delinq_cross NUMBER ;
372 num_tot_delinq_treasury_offset NUMBER ;
373 num_tot_delinq_agency NUMBER ;
374 num_tot_delinq_other NUMBER ;
375
376 -- Part II Section A
377 amt_delinq_2A_tot NUMBER;
378 num_delinq_2A_tot NUMBER;
379 amt_delinq_1A NUMBER ;
380 amt_delinq_1B NUMBER ;
381 amt_delinq_1C NUMBER ;
382 amt_delinq_1D NUMBER ;
383 amt_delinq_1E NUMBER ;
384 amt_delinq_1F NUMBER ;
385 amt_delinq_1G NUMBER ;
386 amt_delinq_commercial NUMBER ;
387 amt_delinq_consumer NUMBER ;
388 amt_delinq_forgn_sovrn NUMBER ;
389
390 num_delinq_1A NUMBER ;
391 num_delinq_1B NUMBER ;
392 num_delinq_1C NUMBER ;
393 num_delinq_1D NUMBER ;
394 num_delinq_1E NUMBER ;
395 num_delinq_1F NUMBER ;
396 num_delinq_1G NUMBER ;
397 num_delinq_commercial NUMBER ;
398 num_delinq_consumer NUMBER ;
399 num_delinq_forgn_sovrn NUMBER ;
400
401 --Part II , Section B
402 amt_debt_eligible_180_10 NUMBER ;
403 amt_debt_eligible_bankruptcy NUMBER ;
404 amt_debt_eligible_foreign NUMBER ;
405 amt_debt_eligible_forbearance NUMBER ;
406 amt_debt_eligible_foreclosure NUMBER ;
407 amt_debt_eligible_other NUMBER ;
408 amt_debt_eligible_collection NUMBER ;
409 amt_debt_eligible_litigation NUMBER ;
410 amt_debt_eligible_int_offset NUMBER ;
411 amt_debt_eligible_offset NUMBER ;
412 amt_debt_eligible_X_servicing NUMBER ;
413
414 num_debt_eligible_180_10 NUMBER ;
415 num_debt_eligible_bankruptcy NUMBER ;
416 num_debt_eligible_foreign NUMBER ;
417 num_debt_eligible_forbearance NUMBER ;
418 num_debt_eligible_foreclosure NUMBER ;
419 num_debt_eligible_other NUMBER ;
420 num_debt_eligible_collection NUMBER ;
421 num_debt_eligible_litigation NUMBER ;
422 num_debt_eligible_int_offset NUMBER ;
423 num_debt_eligible_offset NUMBER ;
424 num_debt_eligible_X_servicing NUMBER ;
425
426 l_dm_status fv_invoice_statuses_all.status%TYPE ;
427 l_pay_schedule_id NUMBER;
428 l_dm_due_date DATE;
429 l_count NUMBER ;
430 l_customer_trx_id NUMBER;
431
432 IA_increment NUMBER;
433 IIA_Increment NUMBER ;
434 IIB1_Increment NUMBER ;
435 IIB2_Increment NUMBER;
436
437 BEGIN
438
439 --initialize
440 l_dm_status := '';
441 l_module_name := g_module_name || 'Populate_IB_IIAB' ;
442 amt_tot_delinq_bankruptcy := 0 ;
443 amt_tot_delinq_foreclosure := 0 ;
444 amt_tot_delinq_forbearance := 0 ;
445 amt_tot_delinq_collection := 0 ;
446 amt_tot_delinq_litigation := 0 ;
447 amt_tot_delinq_internal_offset := 0 ;
448 amt_tot_delinq_garnishment := 0 ;
449 amt_tot_delinq_cross := 0 ;
450 amt_tot_delinq_treasury_offset := 0 ;
451 amt_tot_delinq_agency := 0 ;
452 amt_tot_delinq_other := 0 ;
453
454 num_tot_delinq_bankruptcy := 0 ;
455 num_tot_delinq_foreclosure := 0 ;
456 num_tot_delinq_forbearance := 0 ;
457 num_tot_delinq_collection := 0 ;
458 num_tot_delinq_litigation := 0 ;
459 num_tot_delinq_internal_offset := 0 ;
460 num_tot_delinq_garnishment := 0 ;
461 num_tot_delinq_cross := 0 ;
462 num_tot_delinq_treasury_offset := 0 ;
463 num_tot_delinq_agency := 0 ;
464 num_tot_delinq_other := 0 ;
465
466 -- Part II Section A
467 amt_delinq_2A_tot := 0;
468 amt_delinq_1A := 0 ;
469 amt_delinq_1B := 0 ;
470 amt_delinq_1C := 0 ;
471 amt_delinq_1D := 0 ;
472 amt_delinq_1E := 0 ;
473 amt_delinq_1F := 0 ;
474 amt_delinq_1G := 0 ;
475 amt_delinq_commercial := 0 ;
476 amt_delinq_consumer := 0 ;
477 amt_delinq_forgn_sovrn := 0 ;
478
479 num_delinq_2A_tot := 0;
480 num_delinq_1A := 0 ;
481 num_delinq_1B := 0 ;
482 num_delinq_1C := 0 ;
483 num_delinq_1D := 0 ;
484 num_delinq_1E := 0 ;
485 num_delinq_1F := 0 ;
486 num_delinq_1G := 0 ;
487 num_delinq_commercial := 0 ;
488 num_delinq_consumer := 0 ;
489 num_delinq_forgn_sovrn := 0 ;
490
491 --Part II , Section B
492 amt_debt_eligible_180_10 := 0 ;
493 amt_debt_eligible_bankruptcy := 0 ;
494 amt_debt_eligible_foreign := 0 ;
495 amt_debt_eligible_forbearance := 0 ;
496 amt_debt_eligible_foreclosure := 0 ;
497 amt_debt_eligible_other := 0 ;
498 amt_debt_eligible_collection := 0 ;
499 amt_debt_eligible_litigation := 0 ;
500 amt_debt_eligible_int_offset := 0 ;
501 amt_debt_eligible_offset := 0 ;
502 amt_debt_eligible_X_servicing := 0 ;
503
504 num_debt_eligible_180_10 := 0 ;
505 num_debt_eligible_bankruptcy := 0 ;
506 num_debt_eligible_foreign := 0 ;
507 num_debt_eligible_forbearance := 0 ;
508 num_debt_eligible_foreclosure := 0 ;
509 num_debt_eligible_other := 0 ;
510 num_debt_eligible_collection := 0 ;
511 num_debt_eligible_litigation := 0 ;
512 num_debt_eligible_int_offset := 0 ;
513 num_debt_eligible_offset := 0 ;
514 num_debt_eligible_X_servicing := 0 ;
515
516 l_dm_status := '' ;
517 l_pay_schedule_id := 0;
518 l_count := 0 ;
519
520 IA_increment := 0;
521 IIA_Increment := 0 ;
522 IIB1_Increment := 0 ;
523 IIB2_Increment := 0;
524
525
526
527 FOR recs IN CUR_IB_IIAB
528
529 LOOP
530
531
532 -- id will never be -99 so comparisions down
533 -- the line will definitely fail if class is not INV
534 l_pay_schedule_id := -99 ;
535
536 -- Part I Section B, 'Delinquent Debt by Age'
537 -- Initialize the Due Date for 'INV'
538 l_dm_due_date := recs.due_date;
539 IF recs.class = 'DM' and recs.related_customer_trx_id IS NOT NULL then
540 SELECT nvl(min(due_date),recs.due_date)
541 INTO l_dm_due_date
542 FROM ar_payment_schedules_all a
543 WHERE a.customer_trx_id = recs.related_customer_trx_id
544 AND amount_due_remaining > 0;
545 ELSIF recs.class = 'INV' AND recs.amount_due_remaining > 0 THEN
546 SELECT MIN(payment_schedule_id)
547 INTO l_pay_schedule_id
548 FROM ar_payment_schedules_all a
549 WHERE a.customer_trx_id = recs.customer_trx_id;
550 END IF;
551
552 IF recs.payment_schedule_id = l_pay_schedule_id THEN
553 IA_increment := 1 ;
554 ELSE
555 IA_increment := 0 ;
556 END IF;
557
558 --amt* variables are the corresponding Dolalr amount
559 --num* variables are the corresponding Number/ Count
560
561 IF
562 trunc(g_as_of_date) between trunc(l_dm_due_date)+1 and trunc(l_dm_due_date)+90 then
563 amt_delinq_1A := amt_delinq_1A + recs.amount_due_remaining;
564 num_delinq_1A := num_delinq_1A + IA_increment ;
565 ELSIF
566 trunc(g_as_of_date) between trunc(l_dm_due_date)+91 and trunc(l_dm_due_date)+180
567 then
568 amt_delinq_1B := amt_delinq_1B + recs.amount_due_remaining;
569 num_delinq_1B := num_delinq_1B + IA_increment ;
570 ELSIF
571 trunc(g_as_of_date) between trunc(l_dm_due_date)+181 and trunc(l_dm_due_date)+365
572 then
573 amt_delinq_1C := amt_delinq_1C + recs.amount_due_remaining;
574 num_delinq_1C := num_delinq_1C + IA_increment ;
575 ELSIF
576 trunc(g_as_of_date) between trunc(l_dm_due_date)+366 and trunc(l_dm_due_date)+730
577 then
578 amt_delinq_1D := amt_delinq_1D + recs.amount_due_remaining;
579 num_delinq_1D := num_delinq_1D + IA_increment ;
580 ELSIF
581 trunc(g_as_of_date) between trunc(l_dm_due_date)+731 and trunc(l_dm_due_date)+2190
582 then
583 amt_delinq_1E := amt_delinq_1E + recs.amount_due_remaining;
584 num_delinq_1E := num_delinq_1E + IA_increment ;
585 ELSIF
586 trunc(g_as_of_date) between trunc(l_dm_due_date)+2191 and trunc(l_dm_due_date)+3650
587 THEN
588 amt_delinq_1F := amt_delinq_1F + recs.amount_due_remaining;
589 num_delinq_1F := num_delinq_1F + IA_increment ;
590 ELSIF
591 trunc(g_as_of_date) > trunc(l_dm_due_date)+3650 THEN
592 amt_delinq_1G := amt_delinq_1G + recs.amount_due_remaining;
593 num_delinq_1G := num_delinq_1G + IA_increment ;
594 END IF;
595
596 IF trunc(g_as_of_date) > trunc(l_dm_due_date) THEN
597 IF upper(recs.customer_category_code) = 'COMMERCIAL' THEN
598 amt_delinq_commercial := amt_delinq_commercial + recs.amount_due_remaining;
599 num_delinq_commercial := num_delinq_commercial + IA_increment ;
600 ELSIF upper(recs.customer_category_code) = 'CONSUMER' THEN
601 amt_delinq_consumer := amt_delinq_consumer + recs.amount_due_remaining;
602 num_delinq_consumer := num_delinq_consumer + IA_increment ;
603 ELSIF upper(recs.customer_category_code) = 'FORGN_SOVRN' THEN
604 amt_delinq_forgn_sovrn := amt_delinq_forgn_sovrn +
605 recs.amount_due_remaining;
606 num_delinq_forgn_sovrn := num_delinq_forgn_sovrn + IA_increment ;
607 END IF;
608 END IF;
609
610 --Part II Section A, Delinquent Debt 180 Days or Less
611 --l_dm_status := recs.status ;
612 l_count := 0 ;
613 l_pay_schedule_id := 0;
614
615 IF (recs.class <> 'INV' ) then
616
617 -- Getting the STATUS of the Parent Invoice if the
618 --current record is not class 'INV'
619 BEGIN
620 -- this is to pick on the status that falls within the
621 -- period for which the report is running for.
622 SELECT related_customer_trx_id
623 INTO l_customer_trx_id
624 FROM ra_customer_trx_all rct
625 WHERE rct.customer_trx_id = recs.customer_trx_id
626 AND rct.SET_OF_BOOKS_ID = vp_sob_id; -- Bug 4655467
627 -- AND rct.org_id = vp_org_id ; -- Bug 4655467
628 EXCEPTION
629 -- when no data found - no action needed because l_dm_status
630 -- will be NULL and it will fall thru the logic below
631 WHEN NO_DATA_FOUND THEN
632 NULL;
633 END;
634 ELSE
635 l_customer_trx_id := recs.customer_trx_id;
636 END IF;
637
638
639 -- Checking if the current invoice has not been crossed
640 IF (recs.class = 'INV' ) then
641 BEGIN
642 SELECT count(*)
643 INTO l_count
644 FROM fv_invoice_statuses_all fvis
645 WHERE fvis.customer_trx_id = recs.customer_trx_id
646 AND fvis.status = 'CROSS';
647 --AND l_end_date BETWEEN fvis.start_date AND fvis.end_date;
648 -- may need to add above check in case a bug is logged .
649
650 EXCEPTION
651 WHEN NO_DATA_FOUND THEN
652 null;
653 END;
654 End IF;
655
656
657
658 SELECT min(payment_schedule_id)
659 INTO l_pay_schedule_id
660 FROM ar_payment_schedules_all
661 WHERE customer_trx_id = recs.customer_trx_id
662 AND amount_due_remaining > 0;
663
664 IF recs.payment_schedule_id = l_pay_schedule_id THEN
665 IIA_Increment := 1 ;
666 ELSE
667 IIA_Increment := 0 ;
668 END IF;
669
670 IF trunc(g_as_of_date) between trunc(recs.due_date)+1 and
671 trunc(recs.due_date)+180 THEN
672 amt_delinq_2A_tot := amt_delinq_2A_tot + recs.amount_due_remaining;
673 num_delinq_2A_tot := num_delinq_2A_tot + IIA_Increment ;
674 END IF;
675
676
677
678 -- Begin Calculation Part II Section A
679 IF trunc(g_as_of_date) between trunc(recs.due_date)+1 and trunc(recs.due_date)+180
680 and l_count = 0 THEN ---- and l_dm_status IS NOT NULL then
681 -- Part II Sec A 1A
682
683 FOR inv_stat_rec IN CUR_INV_STATUS( l_customer_trx_id )
684
685 LOOP
686 l_dm_status := inv_stat_rec.status ;
687 IF l_dm_status = 'BANKRUPTCY' THEN
688 amt_tot_delinq_bankruptcy :=
689 amt_tot_delinq_bankruptcy + recs.amount_due_remaining;
690 num_tot_delinq_bankruptcy := num_tot_delinq_bankruptcy + IIA_Increment ;
691 -- Part II Sec A 1B
692 ELSIF l_dm_status = 'FORBEARANCE' THEN
693 amt_tot_delinq_forbearance :=
694 amt_tot_delinq_forbearance + recs.amount_due_remaining;
695 num_tot_delinq_forbearance := num_tot_delinq_forbearance + IIA_Increment ;
696 -- Part II Sec A 1C
697 ELSIF l_dm_status = 'FORECLOSURE' THEN
698 amt_tot_delinq_foreclosure :=
699 amt_tot_delinq_foreclosure + recs.amount_due_remaining;
700 num_tot_delinq_foreclosure := num_tot_delinq_foreclosure + IIA_Increment ;
701 -- Part II Sec A 1D
702 ELSIF l_dm_status = 'COLLECTION' THEN
703 amt_tot_delinq_collection :=
704 amt_tot_delinq_collection + recs.amount_due_remaining;
705 num_tot_delinq_collection := num_tot_delinq_collection + IIA_Increment ;
706 -- Part II Sec A 1E
707 ELSIF l_dm_status = 'LITIGATION' THEN
708 amt_tot_delinq_litigation :=
709 amt_tot_delinq_litigation + recs.amount_due_remaining;
710 num_tot_delinq_litigation := num_tot_delinq_litigation + IIA_Increment ;
711 -- Part II Sec A 1F
712 ELSIF l_dm_status = 'INTERNAL_OFFSET'THEN
713 amt_tot_delinq_internal_offset :=
714 amt_tot_delinq_internal_offset + recs.amount_due_remaining;
715 num_tot_delinq_internal_offset := num_tot_delinq_internal_offset
716 + IIA_Increment ;
717 -- Part II Sec A 1G
718 ELSIF l_dm_status = 'GARNISHMENT' THEN
719 amt_tot_delinq_garnishment :=
720 amt_tot_delinq_garnishment + recs.amount_due_remaining;
721 num_tot_delinq_garnishment := num_tot_delinq_garnishment + IIA_Increment ;
722 -- Part II Sec A 1H
723 ELSIF l_dm_status = 'CROSS' THEN
724 amt_tot_delinq_cross :=
725 amt_tot_delinq_cross + recs.amount_due_remaining;
726 num_tot_delinq_cross := num_tot_delinq_cross + IIA_Increment ;
727 -- above condition is not generally possible because
728 --we never enter the If End if Construct when
729 -- the status is 'cross' (v_count = 0)
730
731 -- Part II Sec A 1I
732 ELSIF l_dm_status = 'TREASURY_OFFSET' THEN
733 amt_tot_delinq_treasury_offset := amt_tot_delinq_treasury_offset +
734 recs.amount_due_remaining;
735 num_tot_delinq_treasury_offset := num_tot_delinq_treasury_offset +
736 IIA_Increment ;
737 -- Part II Sec A 1J
738 ELSIF l_dm_status = 'AGENCY' THEN
739 amt_tot_delinq_agency := amt_tot_delinq_agency +
740 recs.amount_due_remaining;
741 num_tot_delinq_agency := num_tot_delinq_agency +
742 IIA_Increment ;
743 -- Part II Sec A 1K
744 ELSIF l_dm_status = 'OTHER' THEN
745 amt_tot_delinq_other := amt_tot_delinq_other +
746 recs.amount_due_remaining;
747 num_tot_delinq_other := num_tot_delinq_other +
748 IIA_Increment ;
749 END IF;
750 END LOOP;
751 END IF; -- End Calculation Part II Section A
752
753 -- Begin Calculation Part II Section B
754 IF trunc(g_as_of_date) between trunc(recs.due_date)+181
755 and trunc(recs.due_date)+3650 THEN
756 --and l_dm_status IS NOT NULL then
757
758 IF recs.payment_schedule_id = l_pay_schedule_id and recs.class = 'INV' THEN
759 IIB1_Increment := -1 ;
760 ELSE
761 IIB1_Increment := 0 ;
762 END IF ;
763
764 IF recs.payment_schedule_id = l_pay_schedule_id THEN
765 IIB2_Increment := -1 ;
766 ELSE
767 IIB2_Increment := 0 ;
768 END IF ;
769
770 -- Part II Sec B 1A
771 amt_debt_eligible_180_10 := amt_debt_eligible_180_10 -
772 recs.amount_due_remaining ;
773 num_debt_eligible_180_10 := num_debt_eligible_180_10 + IIB1_Increment ;
774
775
776 FOR inv_stat_rec IN CUR_INV_STATUS( l_customer_trx_id )
777
778 LOOP
779
780 l_dm_status := inv_stat_rec.status;
781 -- Part II Sec B 1B
782 IF l_dm_status = 'BANKRUPTCY' THEN
783 amt_debt_eligible_bankruptcy := amt_debt_eligible_bankruptcy -
784 recs.amount_due_remaining ;
785 num_debt_eligible_bankruptcy := num_debt_eligible_bankruptcy
786 + IIB1_Increment ;
787 -- Part II Sec B 1C
788 ELSIF l_dm_status = 'FOREIGN' THEN
789 amt_debt_eligible_foreign := amt_debt_eligible_foreign -
790 recs.amount_due_remaining ;
791 num_debt_eligible_foreign := num_debt_eligible_foreign
792 + IIB1_Increment ;
793 -- Part II Sec B 1D
794 ELSIF l_dm_status = 'FORBEARANCE' THEN
795 amt_debt_eligible_forbearance := amt_debt_eligible_forbearance -
796 recs.amount_due_remaining ;
797 num_debt_eligible_forbearance := num_debt_eligible_forbearance
798 + IIB1_Increment ;
799 -- Part II Sec B 1E
800 ELSIF l_dm_status = 'FORECLOSURE' THEN
801 amt_debt_eligible_foreclosure := amt_debt_eligible_foreclosure -
802 recs.amount_due_remaining ;
803 num_debt_eligible_foreclosure := num_debt_eligible_foreclosure
804 + IIB1_Increment ;
805 -- Part II Sec B 1F AND Part II Sec B 2E
806 -- Part II sec B 1F and 2E are the same values
807 ELSIF l_dm_status = 'OTHER' THEN
808 amt_debt_eligible_other := amt_debt_eligible_other -
809 recs.amount_due_remaining ;
810 num_debt_eligible_other := num_debt_eligible_other
811 + IIB1_Increment ;
812 -- Part II Sec B 2B
813 ELSIF l_dm_status = 'COLLECTION' THEN
814 amt_debt_eligible_collection := amt_debt_eligible_collection -
815 recs.amount_due_remaining ;
816 num_debt_eligible_collection := num_debt_eligible_collection
817 + IIB2_Increment ;
818 -- Part II Sec B 2C
819 ELSIF l_dm_status = 'LITIGATION' THEN
820 amt_debt_eligible_litigation := amt_debt_eligible_litigation -
821 recs.amount_due_remaining ;
822 num_debt_eligible_litigation := num_debt_eligible_litigation
823 + IIB2_Increment ;
824 -- Part II Sec B 2D
825 ELSIF l_dm_status = 'INTERNAL_OFFSET' THEN
826 amt_debt_eligible_int_offset := amt_debt_eligible_int_offset -
827 recs.amount_due_remaining ;
828 num_debt_eligible_int_offset := num_debt_eligible_int_offset
829 + IIB2_Increment ;
830 END IF;
831 END LOOP;
832 END IF; -- End Calculation Part II Section B
833 END LOOP; -- recs.traversal
834 --Part II Sec B 1G = Part II Sec B 2A in both Dollar Amt and Number
835 -- Part II Sec B 1G
836 -- Part II Sec B 2A
837 amt_debt_eligible_offset := amt_debt_eligible_180_10 +
838 amt_debt_eligible_bankruptcy +
839 amt_debt_eligible_foreign +
840 amt_debt_eligible_forbearance +
841 amt_debt_eligible_foreclosure +
842 amt_debt_eligible_other ;
843
844 num_debt_eligible_offset := num_debt_eligible_180_10 +
845 num_debt_eligible_bankruptcy +
846 num_debt_eligible_foreign +
847 num_debt_eligible_forbearance +
848 num_debt_eligible_foreclosure +
849 num_debt_eligible_other ;
850 -- Part II Sec B 2F
851 amt_debt_eligible_X_servicing := amt_debt_eligible_offset +
852 amt_debt_eligible_collection +
853 amt_debt_eligible_litigation +
854 amt_debt_eligible_int_offset +
855 amt_debt_eligible_other ;
856
857 num_debt_eligible_X_servicing := num_debt_eligible_offset +
858 num_debt_eligible_collection +
859 num_debt_eligible_litigation +
860 num_debt_eligible_int_offset +
861 num_debt_eligible_other ;
862
863
864 --==========================================================================
865 -- Populating Lines for Part I Section B
866 --==========================================================================
867 insert_row('1B01', 'Section B' ,NULL ,NULL) ;
868 insert_row('1B02', 'Delinquent Debt by Age' ,NULL ,NULL) ;
869 insert_row('1B03','LINE' ,NULL ,NULL) ;
870 insert_row('1B1', '(1) Total Delinquencies',NULL ,NULL);
871 insert_row('1B1A', ' (A) 1-90 Days', num_delinq_1A , amt_delinq_1A);
872 insert_row('1B1B', ' (B) 91-180 Days', num_delinq_1B , amt_delinq_1B);
873 insert_row('1B1C', ' (C) 181-365 Days', num_delinq_1C , amt_delinq_1C);
874 insert_row('1B1D', ' (D) 1-2 Years', num_delinq_1D , amt_delinq_1D);
875 insert_row('1B1E', ' (E) 2-6 Years', num_delinq_1E , amt_delinq_1E);
876 insert_row('1B1F', ' (F) 6-10 Years', num_delinq_1F , amt_delinq_1F);
877 insert_row('1B1G', ' (G) Over 10 Years',num_delinq_1G , amt_delinq_1G);
878 insert_row('1B2', '(2) Commercial', num_delinq_commercial,
879 amt_delinq_commercial);
880 insert_row('1B3', '(3) Consumer', num_delinq_consumer,
881 amt_delinq_consumer);
882 insert_row('1B4', '(4) Foreign/Sovereign Debt' , num_delinq_forgn_sovrn ,
883 amt_delinq_forgn_sovrn);
884 -- Dummy Lines for Page Break on the Report.
885 insert_row('1B411', '' ,NULL ,NULL);
886 insert_row('1B412', '' ,NULL ,NULL);
887 insert_row('1B413', '' ,NULL ,NULL);
888 insert_row('1B414', '' ,NULL ,NULL);
889 insert_row('1B415', '' ,NULL ,NULL);
890 insert_row('1B416', '' ,NULL ,NULL);
891 insert_row('1B417', '' ,NULL ,NULL);
892 insert_row('1B418', '' ,NULL ,NULL);
893 insert_row('1B419', '' ,NULL ,NULL);
894 insert_row('1B420', '' ,NULL ,NULL);
895 insert_row('1B421', '' ,NULL ,NULL);
896 --==========================================================================
897 -- Populating Lines for Part II Section A
898 --==========================================================================
899 insert_row('2011','LINE' ,NULL ,NULL) ;
900 insert_row('2012','Part II - Debt Management Tool and Technique',NULL,NULL);
901 insert_row('2013',' Performance Data ' ,NULL ,NULL) ;
902 insert_row('2014','LINE' ,NULL ,NULL) ;
903 insert_row('2A01','Section A' ,NULL ,NULL) ;
904 insert_row('2A02','Delinquent Debt 180 Days or Less',NULL ,NULL);
905
906 insert_row('2A03','LINE' ,NULL ,NULL) ;
907 insert_row('2A1', '(1) Total Delinquencies 1 - 180 Days',num_delinq_2A_tot ,
908 amt_delinq_2A_tot ) ;
909 insert_row('2A1A',' (A) In Bankruptcy', num_tot_delinq_bankruptcy ,
910 amt_tot_delinq_bankruptcy);
911 insert_row('2A1B',' (B) In Forbearance or In Formal Appeals Process',
912 num_tot_delinq_forbearance ,amt_tot_delinq_forbearance);
913 insert_row('2A1C',' (C) In Foreclosure',
914 num_tot_delinq_foreclosure ,amt_tot_delinq_foreclosure);
915 insert_row('2A1D',' (D) At Private Collection Agencies',
916 num_tot_delinq_collection ,amt_tot_delinq_collection);
917 insert_row('2A1E',' (E) In Litigation',
918 num_tot_delinq_litigation ,amt_tot_delinq_litigation);
919 insert_row('2A1F',' (F) Eligible for Internal Offset',
920 num_tot_delinq_internal_offset ,amt_tot_delinq_internal_offset);
921 insert_row('2A1G',' (G) In Wage Garnishment',
922 num_tot_delinq_garnishment ,amt_tot_delinq_garnishment);
923 insert_row('2A1H',' (H) At Treasury for Cross Servicing',
924 num_tot_delinq_cross ,amt_tot_delinq_cross);
925 insert_row('2A1I',' (I) At Treasury for Offset',
926 num_tot_delinq_treasury_offset ,amt_tot_delinq_treasury_offset);
927 insert_row('2A1J',' (J) At Agency',
928 num_tot_delinq_agency ,amt_tot_delinq_agency);
929 insert_row('2A1K',' (K) Other - must footnote',
930 num_tot_delinq_other ,amt_tot_delinq_other);
931
932 --==========================================================================
933 -- Populating Lines for Part II Section B
934 --==========================================================================
935 insert_row('2B01','LINE' ,NULL ,NULL) ;
936 insert_row('2B02','Section B' ,NULL ,NULL) ;
937 insert_row('2B03','Debt Eligible for Referral to Treasury for' ,NULL ,NULL) ;
938 insert_row('2B04','Offset and Cross-Servicing' ,NULL ,NULL) ;
939 insert_row('2B05','LINE' ,NULL ,NULL) ;
940 insert_row('2B11', '(1) Debt Eligible for Referral to Treasury' ,NULL ,NULL) ;
941 insert_row('2B12', ' for Offset' ,NULL ,NULL) ;
942 insert_row('2B1A', ' (A) Delinquent Debt Over 180 Days to 10 Years' ,
943 num_debt_eligible_180_10 ,amt_debt_eligible_180_10) ;
944 insert_row('2B1B', ' (B) In Bankruptcy (-)' ,
945 num_debt_eligible_bankruptcy ,amt_debt_eligible_bankruptcy) ;
946 insert_row('2B1C', ' (C) Foreign/Sovereign Debt (-)' ,
947 num_debt_eligible_foreign ,amt_debt_eligible_foreign) ;
948 insert_row('2B1D', ' (D) In Forbearance or Formal Appeals Process(-)' ,
949 num_debt_eligible_forbearance ,amt_debt_eligible_forbearance) ;
950 insert_row('2B1E', ' (E) In Foreclosure (-)' ,
951 num_debt_eligible_foreclosure ,amt_debt_eligible_foreclosure) ;
952 insert_row('2B1F', ' (F) Other - must footnote (+ or -)' ,
953 num_debt_eligible_other ,amt_debt_eligible_other) ;
954 insert_row('2B1G1',' (G) Debt Eligible for Referral to Treasury' ,
955 num_debt_eligible_offset ,amt_debt_eligible_offset) ;
956 insert_row('2B1G2',' for Offset' ,NULL, NULL) ;
957 insert_row('2B21', '(2) Debt Eligible for Referral to Treasury or a ' ,
958 NULL ,NULL) ;
959 insert_row('2B22', ' Designated Debt Collection Center for' ,NULL ,NULL) ;
960 insert_row('2B23', ' Cross-Servicing' ,NULL ,NULL) ;
961 insert_row('2B2A1',' (A) Debt Eligible for Referral to Treasury' ,
962 num_debt_eligible_offset ,amt_debt_eligible_offset) ;
963 insert_row('2B2A2',' for Offset' ,NULL,NULL) ;
964 insert_row('2B2B', ' (B) At PCAs (-)' ,num_debt_eligible_collection ,
965 amt_debt_eligible_collection) ;
966 insert_row('2B2C', ' (C) In Litigation(-)' ,num_debt_eligible_litigation ,
967 amt_debt_eligible_litigation) ;
968 insert_row('2B2D', ' (D) Eligible for Internal Offset (-)' ,
969 num_debt_eligible_int_offset ,amt_debt_eligible_int_offset) ;
970 insert_row('2B2E', ' (E) Other - must footnote (+ or -)' ,
971 num_debt_eligible_other ,amt_debt_eligible_other) ;
972 insert_row('2B2F1',' (F) Debt Eligible for Referral to Treasury or a ' ,
973 num_debt_eligible_X_servicing ,amt_debt_eligible_X_servicing) ;
974 insert_row('2B2F2',' Designated Debt Collection Center for' ,NULL ,NULL) ;
975 insert_row('2B2F3',' Cross-Servicing' ,NULL ,NULL) ;
976
977
978 EXCEPTION
979 WHEN OTHERS THEN
980
981 vp_retcode := SQLCODE;
982 vp_errbuf := SQLERRM;
983 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
984 '.final_exception',vp_errbuf) ;
985 RAISE;
986
987 END Populate_IB_IIAB ;
988
989
990 -- ------------------------------------------------------------------
991 -- Procedure Populate_IA_IIC
992 -- Procedure populate_IA_IIC is called from the
993 -- Main procedure. This procedure populates the temp table
994 -- for Report Sections PartI SecA and PartII Sec B
995 -- ------------------------------------------------------------------
996
997 PROCEDURE Populate_IA_IIC IS
998
999 l_module_name VARCHAR2(200);
1000
1001 CURSOR CUR_IA IS
1002 SELECT rct.customer_trx_id,
1003 rct.related_customer_trx_id,
1004 rct.trx_date,
1005 aps.amount_due_original,
1006 aps.amount_due_remaining,
1007 aps.actual_date_closed,
1008 aps.due_date,
1009 aps.class,
1010 aps.payment_schedule_id,
1011 rct.interface_header_attribute3 created_from,
1012 hzp1.category_code customer_category_code
1013 FROM RA_CUSTOMER_TRX_ALL rct,
1014 RA_CUST_TRX_LINE_GL_DIST_ALL rctlgd,
1015 AR_PAYMENT_SCHEDULES_ALL aps,
1016 --RA_CUSTOMERS rc, --Bug#4476059 Quick Change
1017 hz_cust_accounts hzca1,
1018 hz_parties hzp1,
1019 FV_RECEIVABLE_TYPES_ALL frt,
1020 FV_REC_CUST_TRX_TYPES_ALL fctt,
1021 -- FV_FUND_PARAMETERS fp, -- Bug 4655467
1022 GL_CODE_COMBINATIONS glc
1023 -- FV_TREASURY_SYMBOLS fts -- Bug 4655467
1024 WHERE rct.customer_trx_id = rctlgd.customer_trx_id
1025 AND rct.trx_date <= vg_end_date
1026 AND rctlgd.account_class ='REC'
1027 AND rctlgd.set_of_books_id = vp_sob_id
1028 AND aps.customer_trx_id = rct.customer_trx_id
1029 AND rct.bill_to_customer_id = hzca1.cust_account_id
1030 AND hzca1.party_id = hzp1.party_id
1031 AND rctlgd.code_combination_id = glc.code_combination_id
1032 AND hzca1.customer_class_code = vp_nonfed_customer_class
1033 AND aps.class = 'INV'
1034 AND frt.receivable_type_id = fctt.receivable_type_id
1035 AND frt.receivable_type = vp_type_of_receivable
1036 AND fctt.cust_trx_type_id = rct.cust_trx_type_id
1037 AND frt.SET_OF_BOOKS_ID = vp_sob_id -- Bug 4655467
1038 --AND frt.org_id = vp_org_id -- Bug 4655467
1039 AND rct.SET_OF_BOOKS_ID = vp_sob_id -- Bug 4655467
1040 --AND rct.org_id = vp_org_id -- Bug 4655467
1041 UNION
1042 SELECT rct2.customer_trx_id,
1043 rct2.related_customer_trx_id,
1044 rct2.trx_date,
1045 aps2.amount_due_original,
1046 aps2.amount_due_remaining,
1047 aps2.actual_date_closed,
1048 aps2.due_date,
1049 aps2.class,
1050 aps2.payment_schedule_id,
1051 rct2.interface_header_attribute3 created_from,
1052 hzp2.category_code customer_category_code
1053 from RA_CUSTOMER_TRX_ALL rct2,
1054 ar_payment_schedules_all aps2,
1055 --ra_customers rc2 --Bug#4476059 Quick Change
1056 hz_cust_accounts hzca2,
1057 hz_parties hzp2
1058 where aps2.class in ('DM','CM')
1059 and hzca2.cust_account_id = rct2.bill_to_customer_id
1060 AND hzca2.party_id = hzp2.party_id
1061 and rct2.customer_trx_id = aps2.customer_trx_id
1062 and rct2.SET_OF_BOOKS_ID = vp_sob_id -- Bug 4655467
1063 -- and rct2.org_id = vp_org_id -- Bug 4655467
1064 and rct2.related_customer_trx_id
1065
1066 IN
1067 (Select rct3.customer_trx_id
1068 from RA_CUSTOMER_TRX_ALL rct3,
1069 RA_CUST_TRX_LINE_GL_DIST_ALL rctlgd3,
1070 --RA_CUSTOMERS rc3, --Bug#4476059 Quick Change
1071 hz_cust_accounts hzca3,
1072 hz_parties hzp3,
1073 AR_PAYMENT_SCHEDULES_ALL aps3,
1074 FV_RECEIVABLE_TYPES_ALL frt3,
1075 FV_REC_CUST_TRX_TYPES_ALL fctt3,
1076 -- FV_FUND_PARAMETERS fp3, -- Bug 4655467
1077 -- FV_TREASURY_SYMBOLS fts3, -- Bug 4655467
1078 GL_CODE_COMBINATIONS glc3
1079 where rct3.customer_trx_id = rctlgd3.customer_trx_id
1080 and rct3.trx_date <= vg_end_date
1081 and rctlgd3.account_class ='REC'
1082 and rctlgd3.set_of_books_id = vp_sob_id
1083 and frt3.SET_OF_BOOKS_ID = vp_sob_id -- Bug 4655467
1084 -- and frt3.org_id = vp_org_id -- Bug 4655467
1085 and rct3.SET_OF_BOOKS_ID = vp_sob_id -- Bug 4655467
1086 -- and rct3.org_id = vp_org_id -- Bug 4655467
1087 and aps3.customer_trx_id = rct3.customer_trx_id
1088 and rct3.bill_to_customer_id = hzca3.cust_account_id
1089 and hzca3.party_id = hzp3.party_id
1090 and rctlgd3.code_combination_id = glc3.code_combination_id
1091 and hzca3.customer_class_code = vp_nonfed_customer_class
1092 and aps3.class = 'INV'
1093 and frt3.receivable_type = vp_type_of_receivable
1094 and frt3.receivable_type_id = fctt3.receivable_type_id
1095 and fctt3.cust_trx_type_id = rct3.cust_trx_type_id);
1096
1097 CURSOR CUR_IA_ADJUSTMENTS(p_payment_schedule_id NUMBER ,
1098 p_customer_trx_id NUMBER) IS
1099 SELECT decode(fvs.factsi_customer_attribute, 'ATTRIBUTE1', ara.attribute1,
1100 'ATTRIBUTE2', ara.attribute2, 'ATTRIBUTE3', ara.attribute3,
1101 'ATTRIBUTE4', ara.attribute4, 'ATTRIBUTE5', ara.attribute5,
1102 'ATTRIBUTE6', ara.attribute6, 'ATTRIBUTE7', ara.attribute7,
1103 'ATTRIBUTE8', ara.attribute8, 'ATTRIBUTE8', ara.attribute8,
1104 'ATTRIBUTE9', ara.attribute9, 'ATTRIBUTE10', ara.attribute10,
1105 'ATTRIBUTE11', ara.attribute11, 'ATTRIBUTE12', ara.attribute12,
1106 'ATTRIBUTE13', ara.attribute13, 'ATTRIBUTE14', ara.attribute14,
1107 'ATTRIBUTE15', ara.attribute15) attribute15,
1108 ara.amount,
1109 ara.receivables_trx_id,
1110 ara.payment_schedule_id,
1111 rct.interface_header_attribute3 created_from,
1112 ara.customer_trx_id,
1113 hzp1.category_code customer_category_code,
1114 ara.type,
1115 rctt.type trx_type
1116 FROM ar_adjustments_all ara,
1117 ra_customer_trx_all rct,
1118 --ra_customers rc, --Bug#4476059 Quick Change
1119 hz_cust_accounts hzca1,
1120 hz_parties hzp1,
1121 ra_cust_trx_types_all rctt,
1122 fv_system_parameters fvs
1123 WHERE rct.customer_trx_id = ara.customer_trx_id
1124 AND hzca1.cust_account_id = rct.bill_to_customer_id
1125 AND hzca1.party_id = hzp1.party_id
1126 AND rct.cust_trx_type_id = rctt.cust_trx_type_id
1127 AND ara.apply_date >= vl_fy_begin_date
1128 AND ara.apply_date < vg_end_date+1
1129 AND ara.gl_date >= vl_fy_begin_date
1130 AND ara.gl_date < vg_end_date+1
1131 AND ara.payment_schedule_id = p_payment_schedule_id
1132 AND ara.customer_trx_id = p_customer_trx_id
1133 AND rct.SET_OF_BOOKS_ID = vp_sob_id; -- Bug 4655467
1134 --AND rct.org_id = vp_org_id; -- Bug 4655467
1135
1136
1137 CURSOR CUR_1A_COLLECTIONS_1 (p_payment_schedule_id NUMBER ,
1138 p_customer_trx_id NUMBER ) IS
1139 SELECT ara.amount_applied,
1140 ara.APPLIED_CUSTOMER_TRX_ID ,
1141 ara.receivables_trx_id,
1142 hzp1.category_code customer_category_code,
1143 ara.applied_payment_schedule_id,
1144 acr.receipt_number,
1145 acr.cash_receipt_id,
1146 acr.amount
1147 from ar_receivable_applications_all ara,
1148 ar_cash_receipts_all acr,
1149 --ra_customers rc, ----Bug#4476059 Quick Change
1150 hz_cust_accounts hzca1,
1151 hz_parties hzp1,
1152 ra_customer_trx_all rct
1153 where ara.cash_receipt_id = acr.cash_receipt_id
1154 and ara.status = 'APP'
1155 and ara.apply_date between vl_fy_begin_date and vg_end_date+1
1156 and rct.customer_trx_id = p_customer_trx_id
1157 and hzca1.cust_account_id = rct.bill_to_customer_id
1158 AND hzca1.party_id = hzp1.party_id
1159 AND ara.APPLIED_CUSTOMER_TRX_ID = p_customer_trx_id
1160 AND ara.applied_payment_schedule_id = p_payment_schedule_id ;
1161
1162 CURSOR CUR_1A_COL_ON_REC (p_receipt_number VARCHAR2 ,
1163 p_customer_trx_id NUMBER ,
1164 p_cash_receipt_id NUMBER )IS
1165 SELECT receipt_desc_type ,NVL(ficr.amount,0) amount
1166 FROM fv_interim_cash_receipts_all ficr,
1167 ar_cash_receipts_all acr,
1168 ar_cash_receipt_history_all acrh
1169 WHERE ficr.receipt_number = p_receipt_number
1170 AND ficr.customer_trx_id = p_customer_trx_id
1171 AND ficr.set_of_books_id = vp_sob_id
1172 AND ficr.batch_id = acrh.batch_id
1173 AND acrh.cash_receipt_id = acr.cash_receipt_id
1174 AND acrh.current_record_flag = 'Y'
1175 AND acr.cash_receipt_id = p_cash_receipt_id ;
1176
1177
1178 CURSOR CUR_IIC_Collections( p_payment_schedule_id NUMBER,
1179 p_customer_trx_id NUMBER ) IS
1180 select ara.amount_applied,
1181 ara.applied_customer_trx_id,
1182 ara.applied_payment_schedule_id,
1183 acr.receipt_number,
1184 ara.cash_receipt_id,
1185 aps.class
1186 from ar_receivable_applications_all ara,
1187 ar_cash_receipts_all acr,
1188 ar_payment_schedules_all aps
1189 where ara.cash_receipt_id = acr.cash_receipt_id
1190 and acr.set_of_books_id = vp_sob_id
1191 and nvl(ara.days_late,0) >= 0
1192 and trunc(ara.apply_date) > trunc(aps.due_date)
1193 and aps.customer_trx_id = ara.applied_customer_trx_id
1194 and aps.payment_schedule_id = ara.applied_payment_schedule_id
1195 and ara.applied_customer_trx_id = p_customer_trx_id
1196 and ara.applied_payment_schedule_id = p_payment_schedule_id ;
1197
1198 amt_accruals NUMBER ;
1199 amt_fy_begin_bal NUMBER ;
1200 amt_fy_new_rec NUMBER ;
1201 amt_fin_accruals_int NUMBER ;
1202 amt_adj_reclassified NUMBER ;
1203 amt_adj_sales_assets NUMBER ;
1204 amt_adj_consolidation NUMBER ;
1205 amt_adj_accrual NUMBER ;
1206 amt_write_off_A NUMBER ;
1207 amt_write_off_B NUMBER ;
1208 amt_fin_adj_accruals_int NUMBER ;
1209 amt_col_third_party NUMBER ;
1210 amt_col_asset_sales NUMBER ;
1211 amt_col_others NUMBER ;
1212 amt_col_at_agency NUMBER ;
1213
1214 amt_SECC_1A NUMBER ;
1215 amt_SECC_1B NUMBER ;
1216 amt_SECC_1C NUMBER ;
1217 amt_SECC_1D NUMBER ;
1218 amt_SECC_1E NUMBER ;
1219 amt_SECC_1F NUMBER ;
1220 amt_SECC_1G NUMBER ;
1221 amt_SECC_1H NUMBER ;
1222 amt_SECC_1I NUMBER ;
1223 amt_SECC_1J NUMBER ;
1224
1225 num_fy_begin_bal NUMBER ;
1226 num_fy_new_rec NUMBER ;
1227 num_adj NUMBER ;
1228 num_write_off_A NUMBER ;
1229 num_write_off_B NUMBER ;
1230 num_SECC_1A NUMBER ;
1231 num_SECC_1B NUMBER ;
1232 num_SECC_1C NUMBER ;
1233 num_SECC_1D NUMBER ;
1234 num_SECC_1E NUMBER ;
1235 num_SECC_1F NUMBER ;
1236 num_SECC_1G NUMBER ;
1237 num_SECC_1H NUMBER ;
1238 num_SECC_1I NUMBER ;
1239 num_SECC_1J NUMBER ;
1240
1241 --l_dm_status fv_invoice_statuses_all.status%TYPE := '' ;
1242 --l_pay_schedule_id NUMBER;
1243 --l_dm_due_date DATE;
1244 --l_count NUMBER ;
1245
1246 l_exists NUMBER ;
1247 l_adj_amount Number;
1248 l_appld_amount number ;
1249 l_count NUMBER;
1250
1251 --l_total_amt NUMBER;
1252 --l_amt_appl_on_fin NUMBER;
1253
1254 l_act_amt NUMBER;
1255 l_cust_trx_id NUMBER;
1256 l_rec_desc_tbl g_rec_desc_type ;
1257
1258 l_pay_schedule_id ar_payment_schedules_all.payment_schedule_id%type;
1259 schedule_id ar_receivable_applications_all.applied_payment_schedule_id%type;
1260
1261 dummy varchar2(2);
1262 fc_flag varchar2(2);
1263 l_status ra_customer_trx_all.status_trx%type ;
1264 IIC_Col_recs_Increment NUMBER;
1265
1266 BEGIN
1267
1268 IIC_Col_recs_Increment := 0;
1269 l_module_name := g_module_name || 'Populate_IB_IIAB';
1270 amt_accruals := 0;
1271 amt_fy_begin_bal := 0;
1272 amt_fy_new_rec := 0;
1273 amt_fin_accruals_int := 0;
1274 amt_adj_reclassified := 0;
1275 amt_adj_sales_assets := 0;
1276 amt_adj_consolidation := 0;
1277 amt_adj_accrual := 0;
1278 amt_write_off_A := 0;
1279 amt_write_off_B := 0;
1280 amt_fin_adj_accruals_int := 0;
1281 amt_col_third_party := 0;
1282 amt_col_asset_sales := 0;
1283 amt_col_others := 0;
1284 amt_col_at_agency := 0;
1285
1286 amt_SECC_1A := 0;
1287 amt_SECC_1B := 0;
1288 amt_SECC_1C := 0;
1289 amt_SECC_1D := 0;
1290 amt_SECC_1E := 0;
1291 amt_SECC_1F := 0;
1292 amt_SECC_1G := 0;
1293 amt_SECC_1H := 0;
1294 amt_SECC_1I := 0;
1295 amt_SECC_1J := 0;
1296
1297 num_fy_begin_bal := 0;
1298 num_fy_new_rec := 0;
1299 num_adj := 0;
1300 num_write_off_A := 0;
1301 num_write_off_B := 0;
1302 num_SECC_1A := 0;
1303 num_SECC_1B := 0;
1304 num_SECC_1C := 0;
1305 num_SECC_1D := 0;
1306 num_SECC_1E := 0;
1307 num_SECC_1F := 0;
1308 num_SECC_1G := 0;
1309 num_SECC_1H := 0;
1310 num_SECC_1I := 0;
1311 num_SECC_1J := 0;
1312
1313 l_adj_amount := 0;
1314 l_appld_amount:= 0;
1315
1316 --l_total_amt := 0;
1317 --l_amt_appl_on_fin := 0;
1318 l_act_amt := 0;
1319 l_count := 1;
1320
1321
1322 FOR recs IN CUR_IA
1323
1324 LOOP
1325 l_adj_amount:= 0;
1326 l_appld_amount := 0;
1327
1328 l_pay_schedule_id := 0 ; -- id will never be 0 so comparisions down
1329 -- the line will definitely fail if class is not INV
1330
1331 -- 1A1 Begining FY Balance Amount
1332 IF (recs.trx_date < vl_fy_begin_date ) and
1333 recs.actual_date_closed > vl_fy_begin_date THEN
1334 select nvl(sum(amount),0)
1335 into l_adj_amount
1336 from ar_adjustments_all
1337 where customer_trx_id = recs.customer_trx_id
1338 and apply_date < vl_fy_begin_date + 1;
1339
1340 select nvl(sum(amount_applied),0) * -1
1341 into l_appld_amount
1342 from ar_receivable_applications_all
1343 where applied_customer_trx_id = recs.customer_trx_id
1344 and apply_date < vl_fy_begin_date + 1;
1345
1346 amt_fy_begin_bal := amt_fy_begin_bal +
1347 recs.amount_due_original +
1348 l_adj_amount + l_appld_amount ;
1349 END IF;
1350
1351 -- 1A1 Begining FY Balance Number
1352 SELECT MIN(payment_schedule_id)
1353 INTO l_pay_schedule_id
1354 FROM ar_payment_schedules_all a
1355 WHERE a.customer_trx_id = recs.customer_trx_id
1356 AND actual_date_closed > vl_fy_begin_date;
1357
1358 IF (recs.trx_date < vl_fy_begin_date ) AND
1359 recs.payment_schedule_id = l_pay_schedule_id THEN
1360 num_fy_begin_bal := num_fy_begin_bal + 1 ;
1361 END IF;
1362
1363
1364 -- 1A2 New Receivables Number
1365 SELECT min(payment_schedule_id)
1366 INTO l_pay_schedule_id
1367 FROM ar_payment_schedules_all a
1368 WHERE a.customer_trx_id = recs.customer_trx_id;
1369
1370 --1A2 New Receivables Amount
1371 IF (recs.trx_date >= vl_fy_begin_date and recs.class = 'INV') THEN
1372 amt_fy_new_rec := amt_fy_new_rec + recs.amount_due_original;
1373 --1A2 New Receivables Number
1374 IF recs.payment_schedule_id = l_pay_schedule_id THEN
1375 num_fy_new_rec := num_fy_new_rec + 1;
1376 END IF;
1377 END IF ;
1378
1379
1380 --1A3 Accruals
1381 BEGIN
1382 SELECT DISTINCT 'x' INTO dummy
1383 FROM fv_finance_charge_controls_all
1384 WHERE set_of_books_id = vp_sob_id
1385 AND charge_type = recs.created_from ;
1386
1387 amt_accruals := amt_accruals + recs.amount_due_original;
1388
1389 EXCEPTION
1390 WHEN NO_DATA_FOUND THEN
1391 Null;
1392 END ;
1393
1394
1395 -- 1A9 Interest and Late Charges
1396 BEGIN
1397 SELECT DISTINCT 'x' INTO dummy
1398 FROM fv_finance_charge_controls_all
1399 WHERE set_of_books_id = vp_sob_id
1400 AND charge_type = recs.created_from
1401 AND category<> 'A';
1402
1403 amt_fin_accruals_int := amt_fin_accruals_int + recs.amount_due_remaining;
1404
1405 EXCEPTION
1406 WHEN NO_DATA_FOUND THEN
1407 Null;
1408 END ;
1409
1410
1411 FOR adj_recs IN CUR_IA_ADJUSTMENTS (recs.payment_schedule_id ,
1412 recs.customer_trx_id)
1413 LOOP
1414
1415 --1A5 Adjustments
1416
1417 BEGIN
1418 SELECT distinct 'Y' into fc_flag
1419 from fv_finance_charge_controls_all
1420 where charge_type = adj_recs.created_from;
1421
1422 EXCEPTION
1423 when no_data_found then
1424 fc_flag := 'N';
1425 END;
1426
1427 IF fc_flag = 'N' then
1428 IF (adj_recs.attribute15 = 'RECLASSIFIED') THEN
1429 amt_adj_reclassified := amt_adj_reclassified + adj_recs.amount ;
1430 num_adj := num_adj + 1;
1431 ELSIF (adj_recs.attribute15 = 'ASSET') THEN
1432 amt_adj_sales_assets := amt_adj_sales_assets + adj_recs.amount ;
1433 num_adj := num_adj + 1;
1434 ELSIF (adj_recs.attribute15 = 'CONSOLIDATION') THEN
1435 amt_adj_consolidation := amt_adj_consolidation + adj_recs.amount ;
1436 num_adj := num_adj + 1;
1437 END IF;
1438 ELSIF fc_flag = 'Y' THEN
1439 -- 1A3 Accruals
1440 IF (adj_recs.attribute15 IS NULL ) THEN
1441 amt_adj_accrual := amt_adj_accrual + adj_recs.amount ;
1442 END IF;
1443 END IF; -- fc_flag = N / Y
1444
1445 --END IF;
1446
1447 --1A6 Amounts Written-Off
1448 IF adj_recs.receivables_trx_id IN
1449 (vp_WRITE_OFF_ACTIVITY_1,vP_WRITE_OFF_ACTIVITY_2,vp_WRITE_OFF_ACTIVITY_3)
1450 THEN
1451 SELECT status_trx
1452 INTO l_status
1453 FROM ra_customer_trx_all
1454 WHERE customer_trx_id = adj_recs.customer_trx_id;
1455
1456 IF l_status IN ('OP', 'PEN') THEN
1457 amt_write_off_A := amt_write_off_A + adj_recs.amount;
1458 num_write_off_A := num_write_off_A + 1;
1459 ELSIF l_status IN ('CL', 'VD')THEN
1460 amt_write_off_B := amt_write_off_B + adj_recs.amount;
1461 num_write_off_B := num_write_off_B + 1;
1462 END IF;
1463 END IF;
1464
1465 --1A9 Interest and Late Charges
1466
1467 BEGIN
1468 select distinct 'Y' into fc_flag
1469 from fv_finance_charge_controls_all
1470 where set_of_books_id = vp_sob_id
1471 AND charge_type = recs.created_from
1472 AND category <> 'A';
1473 EXCEPTIOn
1474 when no_data_found then
1475 fc_flag := 'N';
1476 End;
1477 if adj_recs.attribute15 is null and nvl(adj_recs.receivables_trx_id, '-999')
1478 not in (nvl(vP_WRITE_OFF_ACTIVITY_1, '-99'),
1479 nvl(vP_WRITE_OFF_ACTIVITY_2, '-99'),
1480 nvl(vP_WRITE_OFF_ACTIVITY_3, '-99')) and fc_flag = 'Y' then
1481 amt_fin_adj_accruals_int := amt_fin_adj_accruals_int + adj_recs.amount;
1482 end if;
1483
1484 END LOOP; -- CUR_1A_ADJUSTMENTS
1485
1486 -- 1A4 Collections
1487 FOR col_recs IN CUR_1A_COLLECTIONS_1(recs.payment_schedule_id ,
1488 recs.customer_trx_id)
1489 LOOP
1490
1491
1492 -- get the parent trx id for the DM/CM
1493
1494 IF recs.related_customer_trx_id IS NOT NULL and recs.class <> 'INV' THEN
1495 l_cust_trx_id := recs.related_customer_trx_id ;
1496 ELSE
1497 l_cust_trx_id := recs.customer_trx_id ;
1498 END IF;
1499
1500 l_exists := 0;
1501
1502
1503 FOR receipt_desc_recs IN CUR_1A_COL_ON_REC (col_recs.receipt_number,
1504 l_cust_trx_id ,
1505 col_recs.cash_receipt_id)
1506 LOOP
1507
1508 IF l_count > 1 THEN
1509 FOR i IN 1..l_rec_desc_tbl.count
1510 LOOP
1511 IF l_rec_desc_tbl(i).cash_receipt_id = col_recs.cash_receipt_id THEN
1512 l_rec_desc_tbl(i).amount := l_rec_desc_tbl(i).amount -
1513 col_recs.amount_applied;
1514 l_exists := 1;
1515 END IF;
1516 END LOOP;
1517 END IF;
1518
1519 IF l_exists = 0 THEN
1520 l_rec_desc_tbl(l_count).amount :=
1521 receipt_desc_recs.amount-col_recs.amount_applied;
1522 l_rec_desc_tbl(l_count).cash_receipt_id :=
1523 col_recs.cash_receipt_id;
1524 l_rec_desc_tbl(l_count).desc_type :=
1525 receipt_desc_recs.receipt_desc_type;
1526 l_count := l_count+1;
1527 END IF;
1528
1529 l_act_amt := col_recs.amount_applied;
1530
1531
1532 IF nvl(col_recs.receivables_trx_id, '-999') not in
1533 (nvl(vp_WRITE_OFF_ACTIVITY_1, '-99'),
1534 nvl(vp_WRITE_OFF_ACTIVITY_2, '-99'),
1535 nvl(vp_WRITE_OFF_ACTIVITY_3, '-99')) THEN
1536 IF (receipt_desc_recs.receipt_desc_type = 'TP') THEN
1537 amt_col_third_party := amt_col_third_party - l_act_amt;
1538 ELSIF (receipt_desc_recs.receipt_desc_type) = 'AS' THEN
1539 amt_col_asset_sales := amt_col_asset_sales - l_act_amt;
1540 ELSIF (receipt_desc_recs.receipt_desc_type) = 'OT' OR
1541 (receipt_desc_recs.receipt_desc_type) NOT IN ( 'AG','AS', 'TP')THEN
1542 amt_col_others := amt_col_others - l_act_amt;
1543 ELSE
1544 amt_col_at_agency := amt_col_at_agency - l_act_amt;
1545 END IF;
1546 END IF;
1547 END LOOP ; -- CUR_1A_COL_ON_REC
1548 END LOOP; -- CUR_1A_COLLECTIONS_1
1549
1550 -- 2C Collections
1551
1552
1553 FOR IIC_col_recs IN CUR_IIC_COLLECTIONS(recs.payment_schedule_id ,
1554 recs.customer_trx_id)
1555 LOOP
1556 select min(applied_payment_schedule_id)
1557 into schedule_id
1558 FROM ar_receivable_applications_all
1559 where applied_customer_trx_id =IIC_col_recs.applied_customer_trx_id
1560 and cash_receipt_id = IIC_col_recs.cash_receipt_id;
1561
1562 IF(IIC_Col_recs.applied_payment_schedule_id = schedule_id
1563 and recs.class = 'INV') THEN
1564 IIC_Col_recs_Increment := 1 ;
1565 ELSE
1566 IIC_Col_recs_Increment := 0 ;
1567 END IF;
1568
1569 IF recs.related_customer_trx_id IS NOT NULL and recs.class <> 'INV' THEN
1570 l_cust_trx_id := recs.related_customer_trx_id ;
1571 ELSE
1572 l_cust_trx_id := recs.customer_trx_id ;
1573 END IF;
1574
1575 FOR receipt_desc_recs IN CUR_1A_COL_ON_REC (IIC_col_recs.receipt_number,
1576 -- IIC_Col_recs.applied_customer_trx_id ,
1577 l_cust_trx_id,
1578 IIC_col_recs.cash_receipt_id)
1579 LOOP
1580
1581 l_act_amt := IIC_Col_recs.amount_applied;
1582
1583 IF (receipt_desc_recs.receipt_desc_type) = 'PC' THEN
1584 amt_SECC_1A := amt_SECC_1A + l_act_amt;
1585 num_SECC_1A := num_SECC_1A + IIC_Col_recs_Increment ;
1586 ELSIF (receipt_desc_recs.receipt_desc_type) = 'LI' THEN
1587 amt_SECC_1B := amt_SECC_1B + l_act_amt;
1588 num_SECC_1B := num_SECC_1B + IIC_Col_recs_Increment ;
1589 ELSIF (receipt_desc_recs.receipt_desc_type) = 'IO' THEN
1590 amt_SECC_1C := amt_SECC_1C + l_act_amt;
1591 num_SECC_1C := num_SECC_1C + IIC_Col_recs_Increment ;
1592 ELSIF (receipt_desc_recs.receipt_desc_type) = 'TP' THEN
1593 amt_SECC_1D := amt_SECC_1D + l_act_amt;
1594 num_SECC_1D := num_SECC_1D + IIC_Col_recs_Increment ;
1595 ELSIF (receipt_desc_recs.receipt_desc_type) = 'AS' THEN
1596 amt_SECC_1E := amt_SECC_1E + l_act_amt;
1597 num_SECC_1E := num_SECC_1E + IIC_Col_recs_Increment ;
1598 ELSIF (receipt_desc_recs.receipt_desc_type) = 'WG' THEN
1599 amt_SECC_1F := amt_SECC_1F + l_act_amt;
1600 num_SECC_1F := num_SECC_1F + IIC_Col_recs_Increment ;
1601 ELSIF (receipt_desc_recs.receipt_desc_type) = 'TD' THEN
1602 amt_SECC_1G := amt_SECC_1G + l_act_amt;
1603 num_SECC_1G := num_SECC_1G + IIC_Col_recs_Increment ;
1604 ELSIF (receipt_desc_recs.receipt_desc_type) = 'TO' THEN
1605 amt_SECC_1H := amt_SECC_1H + l_act_amt;
1606 num_SECC_1H := num_SECC_1H + IIC_Col_recs_Increment ;
1607 ELSIF (receipt_desc_recs.receipt_desc_type) = 'AG' THEN
1608 amt_SECC_1I := amt_SECC_1I + l_act_amt;
1609 num_SECC_1I := num_SECC_1I + IIC_Col_recs_Increment ;
1610 ELSE
1611 amt_SECC_1J := amt_SECC_1J + l_act_amt;
1612 num_SECC_1J := num_SECC_1J + IIC_Col_recs_Increment ;
1613 END IF;
1614 END LOOP ; -- CUR_1A_COL_ON_REC
1615 END LOOP; -- CUR_IIC_COLLECTIONS
1616
1617
1618 END LOOP;-- CUR_1A
1619
1620 -- On account
1621 FOR i IN 1..l_rec_desc_tbl.count
1622 LOOP
1623
1624 IF (l_rec_desc_tbl(i).desc_type) = 'TP' THEN
1625 amt_col_third_party := amt_col_third_party + l_rec_desc_tbl(i).amount;
1626 ELSIF (l_rec_desc_tbl(i).desc_type) = 'AS' THEN
1627 amt_col_asset_sales := amt_col_asset_sales + l_rec_desc_tbl(i).amount;
1628 ELSIF (l_rec_desc_tbl(i).desc_type) = 'OT' OR
1629 (l_rec_desc_tbl(i).desc_type) NOT IN ( 'AG','AS', 'TP')THEN
1630 amt_col_others := amt_col_others + l_rec_desc_tbl(i).amount;
1631 ELSE
1632 amt_col_at_agency := amt_col_at_agency + l_rec_desc_tbl(i).amount;
1633 END IF;
1634 END LOOP;
1635
1636 insert_row('101', 'LINE' ,NULL ,NULL) ;
1637 insert_row('102', 'Part I - Status of Receivables' ,NULL ,NULL) ;
1638 insert_row('103', 'LINE' ,NULL ,NULL) ;
1639 insert_row('1A01','Section A' ,NULL ,NULL) ;
1640 insert_row('1A02','Receivables and Collections' ,NULL ,NULL) ;
1641 insert_row('1A03','LINE' ,NULL ,NULL) ;
1642 insert_row('1A1', '(1) Beginning FY Balance',
1643 num_fy_begin_bal ,amt_fy_begin_bal ) ;
1644 insert_row('1A2', '(2) New Receivables (+)',
1645 num_fy_new_rec ,amt_fy_new_rec ) ;
1646 insert_row('1A3', '(3) Accruals (+)', '', amt_adj_accrual + amt_accruals) ;
1647 insert_row('1A4', '(4) Collections on Receivables (-)', '' ,
1648 amt_col_at_agency +
1649 amt_col_third_party +
1650 amt_col_asset_sales +
1651 amt_col_others ) ;
1652 insert_row('1A4A',' (A) At Agency', '' ,amt_col_at_agency ) ;
1653 insert_row('1A4B',' (B) At Third Party', '' ,amt_col_third_party ) ;
1654 insert_row('1A4C',' (C) Asset Sales', '' ,amt_col_asset_sales ) ;
1655 insert_row('1A4D',' (D) Other - must footnote', '' ,amt_col_others ) ;
1656 insert_row('1A5', '(5) Adjustments',
1657 num_adj ,amt_adj_reclassified + amt_adj_sales_assets + amt_adj_consolidation) ;
1658 insert_row('1A5A',' (A) Reclassified/Adjusted Amounts (+ or -)',
1659 '', amt_adj_reclassified) ;
1660 insert_row('1A5B',' (B) Adjustments Due to Sale of Assets (+ or -)',
1661 '', amt_adj_sales_assets) ;
1662 insert_row('1A5C',' (C) Consolidations (+ or -)',
1663 '', amt_adj_consolidation) ;
1664 insert_row('1A6', '(6) Amounts Written-Off (-)',
1665 num_write_off_A + num_write_off_B , amt_write_off_A + amt_write_off_B ) ;
1666 insert_row('1A6A',' (A) Currently Not Collectible',
1667 num_write_off_A , amt_write_off_A ) ;
1668 insert_row('1A6B',' (B) Written-Off and Closed Out',
1669 num_write_off_B , amt_write_off_B ) ;
1670 insert_row('1A70', '(7) Ending Balance',
1671 num_fy_begin_bal + num_fy_new_rec - num_write_off_A - num_write_off_B,
1672 amt_fy_begin_bal + amt_fy_new_rec + amt_accruals
1673 +amt_col_at_agency + amt_col_third_party + amt_col_asset_sales + amt_col_others
1674 + amt_adj_reclassified + amt_adj_sales_assets + amt_adj_consolidation
1675 + amt_write_off_A + amt_write_off_A ) ;
1676 insert_row('1A71','LINE' ,NULL ,NULL) ;
1677 insert_row('1A7A',' (A) Foreign/Sovereign', '' , '' ) ;
1678 insert_row('1A7B',' (B) State and Local Government', '' , '' ) ;
1679 insert_row('1A7C','LINE' ,NULL ,NULL) ;
1680 insert_row('1A8', '(8) Rescheduled Debt','' ,'' ) ;
1681 insert_row('1A8A',' (A) Delinquent','' ,'') ;
1682 insert_row('1A8B',' (B) Non-Delinquent','' ,'') ;
1683 insert_row('1A91', '(9) Interest' || ' & ' || 'Late Charges',
1684 '', amt_fin_accruals_int + amt_fin_adj_accruals_int ) ;
1685 insert_row('1A92','LINE' ,NULL ,NULL) ;
1686
1687
1688 -- Part 2 Section C
1689 insert_row('2C01', 'LINE' ,NULL ,NULL) ;
1690 insert_row('2C02', 'Section C' ,NULL ,NULL) ;
1691 insert_row('2C03', 'Collections' ,NULL ,NULL) ;
1692 insert_row('2C04', 'LINE' ,NULL ,NULL) ;
1693 insert_row('2C1', '(1) Collections on Delinquent Debt',
1694 num_SECC_1A + num_SECC_1B + num_SECC_1C + num_SECC_1D +
1695 num_SECC_1E + num_SECC_1F + num_SECC_1G + num_SECC_1H +
1696 num_SECC_1I + num_SECC_1J ,
1697 amt_SECC_1A + amt_SECC_1B + amt_SECC_1C + amt_SECC_1D +
1698 amt_SECC_1E + amt_SECC_1F + amt_SECC_1G + amt_SECC_1H +
1699 amt_SECC_1I + amt_SECC_1J ) ;
1700 insert_row('2C1A', ' (A) By Private Collection Agencies',
1701 num_SECC_1A ,amt_SECC_1A ) ;
1702 insert_row('2C1B', ' (B) By Litigation',
1703 num_SECC_1B ,amt_SECC_1B ) ;
1704 insert_row('2C1C', ' (C) By Internal Offset',
1705 num_SECC_1C , amt_SECC_1C) ;
1706 insert_row('2C1D', ' (D) By Third Party',
1707 num_SECC_1D ,amt_SECC_1D ) ;
1708 insert_row('2C1E', ' (E) By Asset Sales',
1709 num_SECC_1E ,amt_SECC_1E ) ;
1710 insert_row('2C1F', ' (F) By Wage Garnishment',
1711 num_SECC_1F ,amt_SECC_1F);
1712 insert_row('2C1G1',' (G) By Treasury or a Designated Debt Collection',
1713 num_SECC_1G ,amt_SECC_1G ) ;
1714 insert_row('2C1G2',' Center Cross Servicing', NULL ,NULL ) ;
1715 insert_row('2C1H', ' (H) By Treasury Offset',
1716 num_SECC_1H , amt_SECC_1H);
1717 insert_row('2C1I', ' (I) By Agency',
1718 num_SECC_1I , amt_SECC_1I);
1719 insert_row('2C1J', ' (J) Other - must footnote',
1720 num_SECC_1J ,amt_SECC_1J);
1721
1722 -- Part 2 Section D - NO calculations for this section
1723 --exist in the existing report.
1724 insert_row('2D01','LINE' ,NULL ,NULL) ;
1725 insert_row('2D02','Section D' ,NULL ,NULL) ;
1726 insert_row('2D03','Debt Disposition' ,NULL ,NULL) ;
1727 insert_row('2D04','LINE' ,NULL ,NULL) ;
1728 insert_row('2D1', '(1) Written Off and Not Closed Out' ,NULL ,NULL) ;
1729 insert_row('2D1A', ' (A) At Private Collection Agencies' ,NULL ,NULL) ;
1730 insert_row('2D1B1',' (B) At Treasury or a Designated Debt Collection' ,
1731 NULL ,NULL) ;
1732 insert_row('2D1B2',' Center for Cross Servicing' ,NULL ,NULL) ;
1733 insert_row('2D1C', ' (C) At Treasury for Offset' ,NULL ,NULL) ;
1734 insert_row('2D1D4',' (D) Other - must footnote' ,NULL ,NULL) ;
1735 insert_row('2D2', '(2) Reported to IRS on Form 1099-C' ,NULL ,NULL) ;
1736 insert_row('2D21','LINE' ,NULL ,NULL) ;
1737
1738 EXCEPTION
1739 WHEN OTHERS THEN
1740 vp_retcode := SQLCODE;
1741 vp_errbuf := SQLERRM;
1742 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
1743 '.final_exception',vp_errbuf) ;
1744 RAISE;
1745
1746 END Populate_IA_IIC ;
1747
1748 -- ------------------------------------------------------------------
1749 -- Procedure Insert Row
1750 -- ------------------------------------------------------------------
1751 -- Insert_Row procedure is called from the <> <> procedure.
1752 -- This procedure insert data in the fv_receivables_activity_temp
1753 -- Table.
1754 -- ------------------------------------------------------------------
1755 PROCEDURE insert_row
1756 ( p_line_num VARCHAR2,
1757 p_descpription VARCHAR2,
1758 p_count NUMBER,
1759 p_amount NUMBER
1760 ) IS
1761 l_module_name VARCHAR2(200);
1762
1763 BEGIN
1764 l_module_name := g_module_name || 'Insert_Row.';
1765
1766 INSERT INTO fv_receivables_activity_temp (
1767 LINE_NUM,
1768 DESCRIPTION,
1769 COUNT ,
1770 AMOUNT)
1771 VALUES (
1772 p_line_num,
1773 p_descpription,
1774 p_count,
1775 p_amount );
1776
1777 EXCEPTION
1778 WHEN OTHERS THEN
1779 vp_retcode := SQLCODE ;
1780 vp_errbuf := SQLERRM ||
1781 'Error in Insert_Row procedure while inserting value for line:' ||
1782 p_line_num ;
1783 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
1784 'Final Exception',vp_errbuf) ;
1785 RETURN ;
1786 END insert_row;
1787 -- ------------------------------------------------------------------
1788 -- Procedure Submit_Reports
1789 -- ------------------------------------------------------------------
1790 -- Submit_Reports procedure is called from the Main Procedure.
1791 -- This procedure submits the Receivables Activity Worksheet Report
1792 -- ------------------------------------------------------------------
1793
1794
1795 PROCEDURE Submit_Report (p_set_of_books_id NUMBER,
1796 p_reporting_entity_code VARCHAR2,
1797 p_fiscal_year NUMBER,
1798 p_quarter NUMBER,
1799 p_reported_by VARCHAR2,
1800 p_type_of_receivable VARCHAR2,
1801 p_footnotes VARCHAR2,
1802 p_preparer_name VARCHAR2,
1803 p_preparer_phone VARCHAR2,
1804 p_preparer_fax_number VARCHAR2,
1805 p_preparer_email VARCHAR2,
1806 p_supervisor_name VARCHAR2,
1807 p_supervisor_phone VARCHAR2,
1808 p_supervisor_email VARCHAR2,
1809 p_address_line_1 VARCHAR2,
1810 p_address_line_2 VARCHAR2,
1811 p_address_line_3 VARCHAR2,
1812 p_city VARCHAR2,
1813 p_state VARCHAR2,
1814 p_postal_code VARCHAR2 ) IS
1815
1816 l_module_name VARCHAR2(200);
1817 vl_req_id NUMBER;
1818 vl_count NUMBER ;
1819 vl_org_id NUMBER ; -- MOAC Changes
1820
1821 BEGIN
1822
1823 l_module_name := g_module_name || 'Submit_Report.';
1824 SELECT COUNT(*)
1825 INTO vl_count
1826 FROM fv_receivables_activity_temp ;
1827
1828 IF vl_count = 0 THEN
1829 vp_retcode := 1 ;
1830 vp_errbuf := 'No Data Found ' ;
1831 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,vp_errbuf) ;
1832 RETURN ;
1833 END IF;
1834
1835 -- MOAC Change
1836 vl_org_id:=mo_global.get_current_org_id;
1837 fnd_request.set_org_id(vl_org_id);
1838
1839 vl_req_id:= Fnd_Request.Submit_Request (
1840 'FV','FVXDCDFR','','',FALSE,
1841 p_set_of_books_id,
1842 p_reporting_entity_code,
1843 p_fiscal_year,
1844 p_quarter,
1845 p_reported_by,
1846 p_type_of_receivable,
1847 p_footnotes,
1848 p_preparer_name,
1849 p_preparer_phone,
1850 p_preparer_fax_number,
1851 p_preparer_email ,
1852 p_supervisor_name ,
1853 p_supervisor_phone ,
1854 p_supervisor_email ,
1855 p_address_line_1 ,
1856 p_address_line_2 ,
1857 p_address_line_3 ,
1858 p_city ,
1859 p_state ,
1860 p_postal_code
1861 ) ;
1862
1863 IF (vl_req_id = 0) THEN
1864 vp_retcode := 2 ;
1865 vp_errbuf := 'Error in Submit_Report procedure,' ||
1866 ' while submitting Receivables Activity Report .' ;
1867 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,vp_errbuf) ;
1868 RETURN ;
1869 END IF;
1870
1871 EXCEPTION
1872 WHEN OTHERS THEN
1873 vp_retcode := SQLCODE ;
1874 vp_errbuf := SQLERRM ||' -- Error in Submit_Report procedure.' ;
1875 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
1876 '.final_exception',vp_errbuf) ;
1877 RETURN ;
1878 END Submit_Report;
1879 ---------------------------------------------------------------------
1880 -- END OF PACKAGE BODY
1881 ---------------------------------------------------------------------
1882 END fv_receivables_activity_pkg ;