eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.FV_GOALS_224

Source


1 PACKAGE BODY FV_GOALS_224 as
2 --$Header: FVTI224B.pls 120.12.12010000.1 2008/07/28 06:32:07 appldev ship $
3 --	l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('FV_DEBUG_FLAG'),'N');
4   g_module_name VARCHAR2(100) ;
5 
6 -- ============================================================================
7 
8 	retcode				varchar2(255);
9 	errbuf 				varchar2(255);
10 --	req_id				number;
11 	var_count			number;
12 --	var_message			varchar2(200);
13 
14 
15 --01
16 	v_receipt_amt_goals		varchar2(14);
17 	v_receipt_amt			number;
18 --02
19 	v_disbursement_amt_goals	varchar2(14);
20 	v_disbursement_amt		number;
21 --03
22 	v_pmt_tran_current_amt_goals	varchar2(14);
23 	v_pmt_tran_current_amt		number;
24 --04-13
25 	v_pmt_tran_prior_amt_goals	varchar2(14);
26 	v_pmt_tran_prior_amt		number;
27 --14
28 	v_collection_rcvd_amt_goals	varchar2(14);
29 	v_collection_rcvd_amt		number;
30 --15
31 	v_deposit_current_amt_goals	varchar2(14);
32 	v_deposit_current_amt		number;
33 --16-25
34 	v_deposit_prior_amt_goals	varchar2(14);
35 	v_deposit_prior_amt		number;
36 	v_deposit_prior_amt_record_26       number;
37 --26
38 	v_net_total_amt_goals		varchar2(14);
39 	v_net_total_amt			number;
40 
41 	v_set_of_books_id		gl_ledgers_public_v.ledger_id%TYPE;
42 
43 --	v_98				varchar2(14);
44 --	v_99				varchar2(14);
45 
46 	v_count				varchar2(14);
47 	v_count_per_alc			varchar2(14);
48 --	V_length			number(2);
49 --	v_org_id			number(15);
50 --	v_supplemental_ind		varchar2(1);
51 
52 
53 --	v_goals_224_record_type		varchar2(2);
54 
55 	v_record_type			varchar2(2);
56 	v_orig_suppl_ind		varchar2(1);
57 	v_alc_code			varchar2(8);
58 	v_treasury_symbol		varchar2(20);
59 	v_entry_number			varchar2(2);
60 	v_transaction_date		varchar2(4);
61 	v_reporting_date		varchar2(6);
62         v_end_date			date;
63 
64 	v_record			varchar2(57);
65 	v_record_01			varchar2(57);
66 	v_record_02			varchar2(57);
67 	v_record_03			varchar2(57);
68 
69 	v_record_14			varchar2(57);
70 	v_record_15			varchar2(57);
71 
72 	v_record_26			varchar2(57);
73 	v_record_98			varchar2(57);
74 	v_record_99			varchar2(57);
75 
76 	gl_period_name			varchar2(30);
77 	flex_num             		number;
78 	statuses_period_type 		varchar2(25);
79 	vp_alc  Ap_Bank_Accounts_All.agency_location_code%TYPE;
80 
81 -- 01, receipts
82 	CURSOR		cur_receipts IS
83 	SELECT	rpad(nvl(substr(replace(fst.treasury_symbol,'-',''),1,20),'                    '),20, ' '), nvl(sum(amount), 0), nvl(supplemental_flag,0)
84 	FROM       	fv_sf224_temp fst
85 	WHERE     	column_group in (20,21)
86       	AND        	reported_month in ('CURRENT', 'CURRENT/PRIOR')
87 	AND		alc_code = v_alc_code
88         AND fst.sf224_processed_flag = 'Y'
89         AND fst.end_period_date < v_end_date
90         AND fst.record_category = 'GLRECORD'
91 	GROUP BY 	fst.treasury_symbol,supplemental_flag;
92 
93 -- 02, disbursements
94 	CURSOR		cur_disbursements IS
95 	SELECT		rpad(nvl(substr(replace(fst.treasury_symbol,'-',''),1,20),'                    '),20, ' '),
96 			nvl(sum(amount), 0),nvl(supplemental_flag,0)
97 	FROM       	fv_sf224_temp fst
98 	WHERE     	column_group in (30,31)
99       	AND        	reported_month in ('CURRENT', 'CURRENT/PRIOR')
100 	AND		alc_code = v_alc_code
101         AND fst.sf224_processed_flag = 'Y'
102         AND fst.end_period_date < v_end_date
103         AND fst.record_category = 'GLRECORD'
104 	GROUP BY 	fst.treasury_symbol,supplemental_flag;
105 
106 -- 04 - 13
107 	CURSOR		cur_pmt_tran_prior_amt IS
108 	SELECT     	distinct to_char(accomplish_date, 'MMYY'),
109 			nvl(sum(amount * decode(column_group, 21,-1,1)),0),
110 			nvl(supplemental_flag,0)
111 	FROM        	fv_sf224_temp fst
112 	WHERE      	column_group in (21, 30)
113 	AND         	reported_month = 'CURRENT/PRIOR'
114 	AND		alc_code = v_alc_code
115         AND fst.sf224_processed_flag = 'Y'
116         AND fst.end_period_date < v_end_date
117         AND fst.record_category = 'GLRECORD'
118 	GROUP BY 	to_char(accomplish_date,'MMYY'),supplemental_flag
119   HAVING nvl(sum(amount * decode(column_group, 21,-1,1)),0) <> 0
120 	ORDER BY 	to_char(accomplish_date,'MMYY') desc;
121 
122 -- 16 - 25
123 	CURSOR		cur_deposit_prior_amt IS
124 	SELECT      	to_char(accomplish_date,'MMYY'),
125 			nvl(sum(amount * decode(column_group, 31,-1,1)),0),
126 			nvl(supplemental_flag,0)
127 	FROM        	fv_sf224_temp fst
128 	WHERE      	column_group in (20,31)
129 	AND         	reported_month in ('CURRENT/PRIOR')
130 	AND		alc_code = v_alc_code
131         AND fst.sf224_processed_flag = 'Y'
132         AND fst.end_period_date < v_end_date
133         AND fst.record_category = 'GLRECORD'
134 	GROUP BY 	to_char(accomplish_date,'MMYY'),supplemental_flag
135   HAVING nvl(sum(amount * decode(column_group, 31,-1,1)),0) <> 0
136 	ORDER BY 	to_char(accomplish_date,'MMYY') desc;
137 
138 	CURSOR 	get_alc_cur IS
139 	SELECT DISTINCT alc_code
140 	FROM Fv_Sf224_Temp fst
141 	WHERE set_of_books_id = v_set_of_books_id
142 	AND fst.sf224_processed_flag = 'Y'
143 	AND fst.record_category = 'GLRECORD'
144 	AND fst.end_period_date < v_end_date
145 	AND fst.alc_code = DECODE (vp_alc, 'ALL', fst.alc_code, vp_alc);
146 
147 
148   CURSOR get_zeroalc_cur
149   (
150     c_set_of_books_id       NUMBER,
151     c_gl_period             VARCHAR2,
152     c_alc                   VARCHAR2,
153     c_partial_or_full       VARCHAR2,
154     c_business_activity     VARCHAR2,
155     c_gwa_reporter_category VARCHAR2
156   )IS
157   select fv.agency_location_code
158    from fv_alc_business_activity_v fv
159   where set_of_books_id = c_set_of_books_id
160     and period_name = c_gl_period
161     AND c_partial_or_full = 'Partial'
162     and c_alc ='ALL'
163     and c_business_activity <>'ALL'
164     and business_activity_code = c_business_activity
165     and c_gwa_reporter_category <> 'ALL'
166     and GWA_REPORTER_CATEGORY_CODE =c_gwa_reporter_category
167     AND NOT EXISTS(SELECT DISTINCT c.alc_code
168                      FROM fv_sf224_temp c
169                     WHERE c.set_of_books_id = c_set_of_books_id
170                       AND c.alc_code = fv.agency_location_code
171 	              AND record_category = 'GLRECORD'
172                       AND sf224_processed_flag = 'Y'
173 		      AND alc_code = fv.agency_location_code
174                       AND end_period_date < v_end_date)
175   UNION
176   select agency_location_code
177     from fv_alc_business_activity_v fv
178    where set_of_books_id=c_set_of_books_id
179      and period_name =  c_gl_period
180      AND c_partial_or_full = 'Partial'
181      and c_alc  ='ALL'
182      and c_business_activity='ALL'
183      and business_activity_code in (select lookup_code
184                                       from fv_lookup_codes
185                                      where LOOKUP_TYPE = 'FV_SF224_BUSINESS_ACTIVITY')
186      and c_gwa_reporter_category = 'ALL'
187      and GWA_REPORTER_CATEGORY_CODE in (select lookup_code
188                                           from fv_lookup_codes
189                                          where LOOKUP_TYPE = 'FV_SF224_GWA_REPORTER_CATEGORY' )
190      AND NOT EXISTS(SELECT DISTINCT c.alc_code
191                       FROM fv_sf224_temp c
192                      WHERE c.set_of_books_id = c_set_of_books_id
193                        AND c.alc_code = fv.agency_location_code
194                        AND record_category = 'GLRECORD'
195                        AND sf224_processed_flag = 'Y'
196 		       AND alc_code = fv.agency_location_code
197                        AND end_period_date < v_end_date)
198   UNION
199   select agency_location_code
200     from fv_alc_business_activity_v fv
201    where set_of_books_id=c_set_of_books_id
202      and period_name = c_gl_period
203      AND c_partial_or_full = 'Partial'
204      and c_alc  ='ALL'
205      and c_business_activity ='ALL'
206      and business_activity_code in ( select lookup_code
207                                        from fv_lookup_codes
208                                       where LOOKUP_TYPE = 'FV_SF224_BUSINESS_ACTIVITY')
209      and c_gwa_reporter_category  <> 'ALL'
210      and GWA_REPORTER_CATEGORY_CODE = c_gwa_reporter_category
211      AND NOT EXISTS(SELECT DISTINCT c.alc_code
212                       FROM fv_sf224_temp c
213                      WHERE c.set_of_books_id = c_set_of_books_id
214                        AND c.alc_code = fv.agency_location_code
215                        AND record_category = 'GLRECORD'
216                        AND sf224_processed_flag = 'Y'
217 		       AND alc_code = fv.agency_location_code
218                        AND end_period_date < v_end_date)
219   UNION
220   select agency_location_code
221     from fv_alc_business_activity_v fv
222     where set_of_books_id=c_set_of_books_id
223       and period_name =  c_gl_period
224       AND c_partial_or_full = 'Partial'
225       and c_alc  ='ALL'
226       and c_business_activity <>'ALL'
227       and business_activity_code =c_business_activity
228       and c_gwa_reporter_category  = 'ALL'
229       and GWA_REPORTER_CATEGORY_CODE in (select fmap.gwa_reporter_category_code
230                                            from fv_sf224_map fmap
231                                           where fmap.business_activity_code= c_business_activity )
232       AND NOT EXISTS(SELECT DISTINCT c.alc_code
233                        FROM fv_sf224_temp c
234                       WHERE c.set_of_books_id = c_set_of_books_id
235                         AND c.alc_code = fv.agency_location_code
236                         AND record_category = 'GLRECORD'
237                         AND sf224_processed_flag = 'Y'
238 			AND alc_code = fv.agency_location_code
239                         AND end_period_date < v_end_date)
240   UNION
241   select distinct fab.agency_location_code
242     from fv_alc_business_activity_v fab
243     where fab.set_of_books_id=c_set_of_books_id
244       AND c_partial_or_full = 'Full'
245       and c_alc  ='ALL'
246       and fab.agency_location_code not in (select agency_location_code
247                                              from fv_alc_business_activity_v fab1
248                                              where fab1.period_name =c_gl_period)
249       AND NOT EXISTS(SELECT DISTINCT c.alc_code
250                        FROM fv_sf224_temp c
251                       WHERE c.set_of_books_id = c_set_of_books_id
252                         AND c.alc_code = fab.agency_location_code
253                         AND record_category = 'GLRECORD'
254                         AND sf224_processed_flag = 'Y'
255 			AND alc_code = fab.agency_location_code
256                         AND end_period_date < v_end_date);
257 
258 
259 -----------------------------------------------------------------------------------------------------------------------
260 
261 PROCEDURE main(	errbuf	 OUT NOCOPY VARCHAR2,
262 			retcode	 OUT NOCOPY VARCHAR2,
263                         p_ledger_id     IN      NUMBER,
264 			p_gl_period   	IN	VARCHAR2,
265 			p_alc   	IN	VARCHAR2,
266 			p_partial_or_full IN VARCHAR2,
267 			p_business_activity IN VARCHAR2,
268 			p_gwa_reporter_category IN VARCHAR2) IS
269   l_module_name VARCHAR2(200) ;
270 
271 BEGIN
272  l_module_name := g_module_name || 'MAIN';
273         v_set_of_books_id := p_ledger_id;
274 
275         gl_period_name := p_gl_period;
276 	vp_alc := p_alc;
277 
278         SELECT  chart_of_accounts_id
279         INTO    flex_num
280         FROM    gl_ledgers_public_v
281         WHERE   ledger_id = v_set_of_books_id;
282 
283         SELECT  distinct period_type
284         INTO    statuses_period_type
285         FROM    gl_period_statuses
286         WHERE   application_id  = '101'
287         AND     ledger_id = v_set_of_books_id;
288 
289         SELECT  to_char(end_date,'YYMMDD'),
290                 TRUNC(end_date)+1
291         INTO    v_reporting_date,
292                 v_end_date
293         FROM    gl_periods glp,
294                 gl_ledgers_public_v gsob
295         WHERE   glp.period_name                 = gl_period_name
296         AND     glp.period_type                 = statuses_period_type
297         AND     gsob.ledger_id            = v_set_of_books_id
298         AND     gsob.chart_of_accounts_id       = flex_num
299         AND     glp.period_set_name             = gsob.period_set_name;
300 
301 
302         IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
303  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THE REPORTING DATE IS ' || V_REPORTING_DATE);
304         END IF;
305 
306 	DELETE FROM FV_GOALS_224_TEMP;
307 
308 	COMMIT;
309 
310 	OPEN get_alc_cur;
311 	LOOP
312 	  FETCH get_alc_cur INTO v_alc_code;
313  	  EXIT WHEN get_alc_cur%NOTFOUND;
314 	  IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
315  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESSING GOALS FOR ALC = ' ||V_ALC_CODE ||
316 				' from the Fv_Sf224_Temp table');
317 	  END IF;
318 
319 	  SELECT count(*)
320       	  INTO	var_count
321 	  FROM 	fv_sf224_temp fst
322 	  where	set_of_books_id = v_set_of_books_id
323 	  and  alc_code = v_alc_code
324           AND fst.sf224_processed_flag = 'Y'
325           AND fst.end_period_date < v_end_date
326           AND fst.record_category = 'GLRECORD';
327 
328  	  IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
329  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'VAR_COUNT IS ' || VAR_COUNT);
330  	  END IF;
331 
332 	  if var_count > 0 THEN
333 		process_record_type_01;
334 		process_record_type_02;
335 		process_record_type_03;
336 		process_record_type_04_13;
337 		process_record_type_14;
338 		process_record_type_15;
339 		process_record_type_16_25;
340 		process_record_type_26;
341 		process_record_type_98;
342 	  else
343 	        process_record_type_03;
344 	        process_record_type_26;
345 	        process_record_type_98;
346 	  end if;
347         END LOOP;
348 
349 	OPEN get_zeroalc_cur(v_set_of_books_id,p_gl_period, p_alc, p_partial_or_full, p_business_activity, p_gwa_reporter_category);
350 	LOOP
351 	  FETCH get_zeroalc_cur INTO v_alc_code;
352  	  EXIT WHEN get_zeroalc_cur%NOTFOUND;
353 
354 	  IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
355  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESSING GOALS FOR ALC = ' ||V_ALC_CODE ||
356 		' for the zero activity, for record types 3, 26 and 98.');
357 	  END IF;
358 
359 	  process_record_type_03;
360 	  process_record_type_26;
361 	  process_record_type_98;
362        END LOOP;
363 
364        process_record_type_99;
365 
366 EXCEPTION
367    When TOO_MANY_ROWS  then
368 	if cur_receipts%ISOPEN then
369 	  close cur_receipts;
370 	end if;
371 	if cur_disbursements%ISOPEN then
372 	  close cur_disbursements;
373 	end if;
374 	if cur_pmt_tran_prior_amt%ISOPEN then
375 	  close cur_pmt_tran_prior_amt;
376 	end if;
377 	if cur_deposit_prior_amt%ISOPEN then
378 	  close cur_deposit_prior_amt;
379 	end if;
380   When OTHERS then
381 	errbuf:=sqlerrm;
382 	retcode:= -1;
383   FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
384 	if cur_receipts%ISOPEN then
385 	  close cur_receipts;
386 	end if;
387 	if cur_disbursements%ISOPEN then
388 	  close cur_disbursements;
389 	end if;
390 	if cur_pmt_tran_prior_amt%ISOPEN then
391 	  close cur_pmt_tran_prior_amt;
392 	end if;
393 	if cur_deposit_prior_amt%ISOPEN then
394 	  close cur_deposit_prior_amt;
395 	end if;
396 
397 	return;
398 END MAIN;
399 
403 BEGIN
400 ------------------------------------------------------------------------------------------------
401 PROCEDURE process_record_type_01 IS
402   l_module_name VARCHAR2(200) ;
404  l_module_name := g_module_name || 'process_record_type_01';
405 --- Getting amount for record type = 01, receipts
406 
407 	OPEN 		cur_receipts;
408 	LOOP
409 	FETCH		cur_receipts INTO v_treasury_symbol, v_receipt_amt,
410 				v_orig_suppl_ind;
411 			EXIT when cur_receipts%NOTFOUND;
412 
413 	IF		v_receipt_amt >= 0 THEN
414 			v_receipt_amt_goals := replace(replace(to_char(v_receipt_amt,'000000000000.00'),'.',''), ' ', '');
415 	ELSE
416 			v_receipt_amt_goals := replace(replace(to_char(v_receipt_amt,'00000000000.00'),'.',''), ' ', '');
417 	END IF;
418 
419 
420 	v_record_type	:= '01';
421 	v_entry_number 	:= '01';
422 
423 	v_transaction_date := '    ';
424 
425 	v_record_01	:= 	v_record_type		||
426 				v_orig_suppl_ind		||
427 				v_alc_code 			||
428 				v_treasury_symbol		||
429 				v_entry_number		||
430 				v_receipt_amt_goals	||
431 				v_transaction_date	||
432 				v_reporting_date;
433 
434 	IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
435  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V RECORD TYPE IS ' || V_RECORD_TYPE);
436  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V ORIG_SUPPL_IND IS ' || V_ORIG_SUPPL_IND);
437  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THE TREASURY SYMBOL IS ' || V_TREASURY_SYMBOL);
438  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THE RECEIPT AMOUNT IS ' || V_RECEIPT_AMT_GOALS);
439  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THE ENTRY NUMBER FOR RECEIPT 01 IS '|| V_ENTRY_NUMBER);
440 	END IF;
441 
442 	IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
443  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RECORD 01 IS '|| V_RECORD_01);
444 	END IF;
445 
446 	IF (v_alc_code IS NOT NULL) AND (v_receipt_amt <> 0)THEN
447 	  insert into fv_goals_224_temp(goals_224_temp_id, goals_224_record_type, alc_code, goals_224_record, supplemental_flag)
448 	  values(fv_goals_224_temp_id_s.nextval, v_record_type, v_alc_code, v_record_01, v_orig_suppl_ind);
449       END IF;
450 
451 	v_treasury_symbol	:= 	NULL;
452 
453 	END LOOP;
454 	close cur_receipts;
455 EXCEPTION
456   WHEN OTHERS THEN
457     errbuf := SQLERRM;
458     retcode := -1;
459     FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
460     RAISE;
461 END process_record_type_01;
462 ------------------------------------------------------------------------------------------------
463 PROCEDURE process_record_type_02 IS
464   l_module_name VARCHAR2(200) ;
465 BEGIN
466  l_module_name := g_module_name || 'process_record_type_02';
467 -- Getting amount for record type = 02, disbursements
468 
469 	OPEN 			cur_disbursements;
470 	LOOP
471 	FETCH			cur_disbursements INTO v_treasury_symbol,
472 			v_disbursement_amt, v_orig_suppl_ind;
473 				EXIT when cur_disbursements%NOTFOUND;
474 
475 	IF		v_disbursement_amt >= 0 THEN
476 			v_disbursement_amt_goals := replace(replace(to_char(v_disbursement_amt,'000000000000.00'),'.',''), ' ', '');
477 	ELSE
478 			v_disbursement_amt_goals := replace(replace(to_char(v_disbursement_amt,'00000000000.00'),'.',''), ' ', '');
479 	END IF;
480 
481 	v_record_type	:= '02';
482 	v_entry_number 	:= '01';
483 
484 	v_transaction_date := '    ';
485 
486 	BEGIN
487 	SELECT	distinct '02'
488 	INTO		v_entry_number
489 	FROM		fv_sf224_temp fst
490 	WHERE		rpad(substr(replace(treasury_symbol,'-',''),1,20),20,' ') IN
491                   (SELECT substr(goals_224_record, 12,20)
492                    FROM fv_goals_224_temp where alc_code = v_alc_code)
493 	AND		rpad(substr(replace(treasury_symbol,'-',''),1,20),20,' ') = v_treasury_symbol
494 	and		alc_code IN
495                   (SELECT substr(goals_224_record, 4,8)
496                    FROM fv_goals_224_temp where alc_code = v_alc_code)
497 	AND		alc_code = v_alc_code
498         AND fst.sf224_processed_flag = 'Y'
499         AND fst.end_period_date < v_end_date
500         AND fst.record_category = 'GLRECORD';
501 	EXCEPTION	WHEN NO_DATA_FOUND THEN
502 			null;
503 			WHEN OTHERS THEN
504 			errbuf := SQLERRM;
505 			retcode := -1;
506       FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.message1',errbuf);
507 			return;
508 	END;
509 
510 	v_record_02	:= 	v_record_type			||
511 				v_orig_suppl_ind			||
512 				v_alc_code 				||
513 				v_treasury_symbol			||
514 				v_entry_number			||
515 				v_disbursement_amt_goals	||
516 				v_transaction_date		||
517 				v_reporting_date;
518 
519 	IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
520  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V RECORD TYPE IS ' || V_RECORD_TYPE);
521  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V ORIG_SUPPL_IND IS ' || V_ORIG_SUPPL_IND);
522  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THE TREASURY SYMBOL IS ' ||V_TREASURY_SYMBOL);
523  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THE DISBURSEMENT AMOUNT IS ' || V_DISBURSEMENT_AMT_GOALS);
524  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THE ENTRY NUMBER FOR DISBURSEMENT 02 IS '|| V_ENTRY_NUMBER);
528  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RECORD 02 IS '|| V_RECORD_02);
525 	END IF;
526 
527 	IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
529 	END IF;
530 
531 	IF v_alc_code IS NOT NULL THEN
532 	  insert into fv_goals_224_temp(goals_224_temp_id, goals_224_record_type, alc_code, goals_224_record, supplemental_flag)
533 	  values(fv_goals_224_temp_id_s.nextval, v_record_type, v_alc_code, v_record_02, v_orig_suppl_ind);
534 	END IF;
535 
536 	END LOOP;
537 	close cur_disbursements;
538 EXCEPTION
539   WHEN OTHERS THEN
540     errbuf := SQLERRM;
541     retcode := -1;
542     FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
543     RAISE;
544 END process_record_type_02;
545 ------------------------------------------------------------------------------------------------
546 PROCEDURE process_record_type_03 IS
547   l_module_name VARCHAR2(200) ;
548 BEGIN
549  l_module_name := g_module_name || 'process_record_type_03';
550 --- Getting amount for record type = 03, payment transaction current
551 
552 		BEGIN
553       		SELECT 	nvl(sum(amount * decode(column_group, 21,-1,1)),0),
554 			nvl(supplemental_flag,0)
555 		INTO    v_pmt_tran_current_amt,v_orig_suppl_ind
556 		FROM    fv_sf224_temp fst
557 		WHERE   column_group in (30,21)
558 		AND     reported_month = 'CURRENT'
559 	        AND	alc_code = v_alc_code
560                 AND fst.sf224_processed_flag = 'Y'
561                 AND fst.end_period_date < v_end_date
562                 AND fst.record_category = 'GLRECORD'
563 		GROUP BY supplemental_flag
564     HAVING nvl(sum(amount * decode(column_group, 21,-1,1)),0) <> 0;
565 		EXCEPTION	WHEN NO_DATA_FOUND THEN
566 				v_pmt_tran_current_amt	:= '00000000000000';
567 				v_orig_suppl_ind := 0;
568 				WHEN OTHERS THEN
569 				errbuf := SQLERRM;
570 				retcode := -1;
571         FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.message1',errbuf);
572 				return;
573 		END;
574 
575 		IF 	v_pmt_tran_current_amt >= 0 THEN
576 			v_pmt_tran_current_amt_goals := replace(replace(to_char(v_pmt_tran_current_amt,'000000000000.00'),'.',''), ' ', '');
577 		ELSE
578 			v_pmt_tran_current_amt_goals := replace(replace(to_char(v_pmt_tran_current_amt,'00000000000.00'),'.',''), ' ', '');
579 		END IF;
580 
581 	v_record_type	:= '03';
582 
583 	v_treasury_symbol 	:= '                    ';
584 	v_entry_number 		:= '  ';
585 	v_transaction_date	:= '    ';
586 
587 	v_record_03	:= 	v_record_type			||
588 				v_orig_suppl_ind			||
589 				v_alc_code 				||
590 				v_treasury_symbol			||
591 				v_entry_number			||
592 				v_pmt_tran_current_amt_goals	||
593 				v_transaction_date		||
594 				v_reporting_date;
595 
596 	IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
597  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V RECORD TYPE IS ' || V_RECORD_TYPE);
598  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V ORIG_SUPPL_IND IS ' || V_ORIG_SUPPL_IND);
599  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THE PMT TRAN CURRENT AMOUNT IS ' || V_PMT_TRAN_CURRENT_AMT_GOALS);
600 	END IF;
601 
602 	IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
603  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RECORD 03 IS '|| V_RECORD_03);
604 	END IF;
605 
606 	IF ((v_alc_code IS NOT NULL) AND (TO_NUMBER(v_pmt_tran_current_amt) <> 0)) THEN
607 	  insert into fv_goals_224_temp(goals_224_temp_id, goals_224_record_type, alc_code, goals_224_record, supplemental_flag)
608 	  values(fv_goals_224_temp_id_s.nextval, v_record_type, v_alc_code, v_record_03, v_orig_suppl_ind);
609 	END IF;
610 EXCEPTION
611   WHEN OTHERS THEN
612     errbuf := SQLERRM;
613     retcode := -1;
614     FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
615     RAISE;
616 END process_record_type_03;
617 -----------------------------------------------------------------------------------------------
618 PROCEDURE process_record_type_04_13 IS
619   l_module_name VARCHAR2(200) ;
620 BEGIN
621  l_module_name := g_module_name || 'process_record_type_04-13';
622 --- Getting amount for record type = 04 - 13, payment transaction prior
623 
624 	OPEN		cur_pmt_tran_prior_amt;
625 	v_record_type := '04';
626 	LOOP
627 
628 	FETCH 	cur_pmt_tran_prior_amt into v_transaction_date, v_pmt_tran_prior_amt,
629 				v_orig_suppl_ind;
630 			EXIT WHEN cur_pmt_tran_prior_amt%notfound;
631 
632 	IF 	v_pmt_tran_prior_amt >= 0 THEN
633 		v_pmt_tran_prior_amt_goals := replace(replace(to_char(v_pmt_tran_prior_amt,'000000000000.00'),'.',''), ' ', '');
634 	ELSE
635 		v_pmt_tran_prior_amt_goals := replace(replace(to_char(v_pmt_tran_prior_amt,'00000000000.00'),'.',''), ' ', '');
636 	END IF;
637 
638 	v_treasury_symbol 	:= '                    ';
639 	v_entry_number 		:= '  ';
640 
641 	v_record	:= 	v_record_type			||
642 				v_orig_suppl_ind			||
643 				v_alc_code 				||
644 				v_treasury_symbol			||
645 				v_entry_number			||
646 				v_pmt_tran_prior_amt_goals	||
647 				v_transaction_date		||
648 				v_reporting_date;
649 
650 	IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
654  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THE PAYMENT TRANSACTION PRIOR AMOUNT IS ' || V_PMT_TRAN_PRIOR_AMT_GOALS);
651  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V RECORD TYPE IS ' || V_RECORD_TYPE);
652  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V ORIG_SUPPL_IND IS ' || V_ORIG_SUPPL_IND);
653  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THE TRANSACTION DATE IS ' || V_TRANSACTION_DATE);
655 	END IF;
656 
657 	IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
658  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RECORD XX IS '|| V_RECORD);
659 	END IF;
660 
661 	IF (v_alc_code IS NOT NULL) AND (v_pmt_tran_prior_amt <> 0)THEN
662 	  insert into fv_goals_224_temp(goals_224_temp_id, goals_224_record_type, alc_code, goals_224_record, supplemental_flag)
663 	  values(fv_goals_224_temp_id_s.nextval, v_record_type, v_alc_code, v_record, v_orig_suppl_ind);
664 	END IF;
665 
666 	v_record_type := to_number(v_record_type);
667 	v_record_type := v_record_type + 1;
668 
669 -- Fix for bug 1483366
670 	-- v_record_type := '0'||v_record_type;
671 
672 	IF length(v_record_type) <= 1 THEN
673 	  v_record_type := '0' || v_record_type;
674 	END IF;
675 
676 	-- End fix for bug 1483366
677 
678 
679 	END LOOP;
680       CLOSE cur_pmt_tran_prior_amt;
681 EXCEPTION
682   WHEN OTHERS THEN
683     errbuf := SQLERRM;
684     retcode := -1;
685     FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
686     RAISE;
687 END process_record_type_04_13;
688 ------------------------------------------------------------------------------------------------
689 PROCEDURE process_record_type_14 IS
690   l_module_name VARCHAR2(200) ;
691 BEGIN
692  l_module_name :=  g_module_name || 'process_record_type_14';
693 --- Getting amount for record type = 14, collections received
694 
695 		BEGIN
696 		SELECT	nvl(sum(amount*decode(column_group, 31,-1,1)),0),
697 			nvl(supplemental_flag,0)
698 		INTO        v_collection_rcvd_amt,v_orig_suppl_ind
699 		FROM        fv_sf224_temp fst
700 		WHERE     	column_group in (20,31)
701 		AND         reported_month in ('CURRENT','CURRENT/PRIOR')
702 	        AND	alc_code = v_alc_code
703                 AND fst.sf224_processed_flag = 'Y'
704                 AND fst.end_period_date < v_end_date
705                 AND fst.record_category = 'GLRECORD'
706 		GROUP BY supplemental_flag
707     HAVING nvl(sum(amount*decode(column_group, 31,-1,1)),0) <> 0;
708 		EXCEPTION	WHEN NO_DATA_FOUND THEN
709 				v_collection_rcvd_amt	:= '00000000000000';
710 				WHEN OTHERS THEN
711 				errbuf := SQLERRM;
712 				retcode := -1;
713         FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.message1',errbuf);
714 				return;
715 		END;
716 
717 	IF 	v_collection_rcvd_amt >= 0 THEN
718 		v_collection_rcvd_amt_goals := replace(replace(to_char(v_collection_rcvd_amt,'000000000000.00'),'.',''), ' ', '');
719 	ELSE
720 		v_collection_rcvd_amt_goals := replace(replace(to_char(v_collection_rcvd_amt,'00000000000.00'),'.',''), ' ', '');
721 	END IF;
722 
723 	v_record_type	:= '14';
724 	v_treasury_symbol 	:= '                    ';
725 	v_entry_number 		:= '  ';
726 	v_transaction_date	:= '    ';
727 
728 	v_record_14	:= 	v_record_type			||
729 				v_orig_suppl_ind			||
730 				v_alc_code 				||
731 				v_treasury_symbol			||
732 				v_entry_number			||
733 				v_collection_rcvd_amt_goals	||
734 				v_transaction_date		||
735 				v_reporting_date;
736 
737 	IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
738  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V RECORD TYPE IS ' || V_RECORD_TYPE);
739  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V ORIG_SUPPL_IND IS ' || V_ORIG_SUPPL_IND);
740  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THE COLLECTIONS RECEIVED AMOUNT IS ' || V_COLLECTION_RCVD_AMT_GOALS);
741  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RECORD 14 IS '|| V_RECORD_14);
742 	END IF;
743 
744         IF v_alc_code IS NOT NULL AND v_collection_rcvd_amt_goals <> 0 THEN
745 	  insert into fv_goals_224_temp(goals_224_temp_id, goals_224_record_type, alc_code, goals_224_record, supplemental_flag)
746 	  values(fv_goals_224_temp_id_s.nextval, v_record_type, v_alc_code, v_record_14, v_orig_suppl_ind);
747 	END IF;
748 EXCEPTION
749   WHEN OTHERS THEN
750     errbuf := SQLERRM;
751     retcode := -1;
752     FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
753     RAISE;
754 END process_record_type_14;
755 ------------------------------------------------------------------------------------------------
756 PROCEDURE process_record_type_15 IS
757   l_module_name VARCHAR2(200) ;
758 BEGIN
759  l_module_name := g_module_name || 'process_record_type_15';
760 --- Getting amount for record type = 15, deposit current amount
761 
762 	BEGIN
763 	SELECT	nvl(sum(amount*decode(column_group, 31,-1,1)),0),
764 		nvl(supplemental_flag,0)
765 	INTO        v_deposit_current_amt,v_orig_suppl_ind
766 	FROM        fv_sf224_temp fst
767 	WHERE      	column_group in (20,31)
768 	AND         reported_month = ('CURRENT')
769 	AND	alc_code = v_alc_code
770         AND fst.sf224_processed_flag = 'Y'
771         AND fst.end_period_date < v_end_date
772         AND fst.record_category = 'GLRECORD'
776 			v_deposit_current_amt	:= '00000000000000';
773 	GROUP BY 	to_char(accomplish_date,'MM-YYYY'),supplemental_flag
774   HAVING nvl(sum(amount*decode(column_group, 31,-1,1)),0) <> 0;
775 	EXCEPTION	WHEN NO_DATA_FOUND THEN
777 	END;
778 
779 	IF 	v_deposit_current_amt >= 0 THEN
780 		v_deposit_current_amt_goals := replace(replace(to_char(v_deposit_current_amt,'000000000000.00'),'.',''), ' ', '');
781 	ELSE
782 		v_deposit_current_amt_goals := replace(replace(to_char(v_deposit_current_amt,'00000000000.00'),'.',''), ' ', '');
783 	END IF;
784 
785 
786 	v_record_type		:= '15';
787 	v_treasury_symbol 	:= '                    ';
788 	v_entry_number 		:= '  ';
789 	v_transaction_date	:= '    ';
790 
791 	v_record_15	:= 	v_record_type			||
792 				v_orig_suppl_ind			||
793 				v_alc_code 				||
794 				v_treasury_symbol			||
795 				v_entry_number			||
796 				v_deposit_current_amt_goals	||
797 				v_transaction_date		||
798 				v_reporting_date;
799 
800 	IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
801  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V RECORD TYPE IS ' || V_RECORD_TYPE);
802  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V ORIG_SUPPL_IND IS ' || V_ORIG_SUPPL_IND);
803  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THE DEPOSIT CURRENT AMOUNT IS ' || V_DEPOSIT_CURRENT_AMT_GOALS);
804  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RECORD 15 IS '|| V_RECORD_15);
805 	END IF;
806 
807 	IF v_alc_code IS NOT NULL AND v_deposit_current_amt <> 0 THEN
808 	  insert into fv_goals_224_temp(goals_224_temp_id, goals_224_record_type, alc_code, goals_224_record, supplemental_flag)
809 	  values(fv_goals_224_temp_id_s.nextval, v_record_type, v_alc_code, v_record_15, v_orig_suppl_ind);
810 	END IF;
811 EXCEPTION
812   WHEN OTHERS THEN
813     errbuf := SQLERRM;
814     retcode := -1;
815     FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
816     RAISE;
817 END process_record_type_15;
818 ------------------------------------------------------------------------------------------------
819 PROCEDURE process_record_type_16_25 IS
820   l_module_name VARCHAR2(200) ;
821 BEGIN
822  l_module_name := g_module_name || 'process_record_type_16-25';
823 --- Getting amount for record type = 16 - 25, deposits presented or mailed to bank
824 
825 	v_deposit_prior_amt_record_26 := 0;
826 
827 	OPEN		cur_deposit_prior_amt;
828 	v_record_type := '16';
829 	LOOP
830 	FETCH 	cur_deposit_prior_amt into v_transaction_date,
831 		v_deposit_prior_amt,v_orig_suppl_ind;
832    	  EXIT WHEN cur_deposit_prior_amt%notfound;
833 
834 	  --  Keep a running total of the deposit prior amount to be used in
835 	  --  record 26
836 	   v_deposit_prior_amt_record_26 :=
837 			v_deposit_prior_amt_record_26 + v_deposit_prior_amt;
838 	IF  v_deposit_prior_amt >= 0 THEN
839 
840 	  --  Keep a running total of the deposit prior amount to be used in
841 	  --  record 26
842 	  --   v_deposit_prior_amt_record_26 :=
843           --		v_deposit_prior_amt_record_26 + v_deposit_prior_amt;
844 
845 	   v_deposit_prior_amt_goals := replace(replace(to_char(v_deposit_prior_amt,'000000000000.00'),'.',''), ' ', '');
846 	ELSE
847 	   v_deposit_prior_amt_goals := replace(replace(to_char(v_deposit_prior_amt,'00000000000.00'),'.',''), ' ', '');
848 	END IF;
849 
850 
851 	v_treasury_symbol 	:= '                    ';
852 	v_entry_number 		:= '  ';
853 
854 	v_record	:= 	v_record_type			||
855 				v_orig_suppl_ind			||
856 				v_alc_code 				||
857 				v_treasury_symbol			||
858 				v_entry_number			||
859 				v_deposit_prior_amt_goals	||
860 				v_transaction_date		||
861 				v_reporting_date;
862 
863 	IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
864  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V RECORD TYPE IS ' || V_RECORD_TYPE);
865  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V ORIG_SUPPL_IND IS ' || V_ORIG_SUPPL_IND);
866  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THE TRANSACTION DATE IS ' || V_TRANSACTION_DATE);
867  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THE DEPOSIT PRIOR AMOUNT IS ' || V_DEPOSIT_PRIOR_AMT_GOALS);
868  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RECORD XX IS '|| V_RECORD);
869 	END IF;
870 
871 	IF v_alc_code IS NOT NULL AND v_deposit_prior_amt <> 0 THEN
872 	  insert into fv_goals_224_temp(goals_224_temp_id, goals_224_record_type, alc_code, goals_224_record, supplemental_flag)
873 	  values(fv_goals_224_temp_id_s.nextval, v_record_type, v_alc_code, v_record, v_orig_suppl_ind);
874 	END IF;
875 
876 	v_record_type := to_number(v_record_type);
877 	v_record_type := v_record_type + 1;
878 
879 	END LOOP;
880       CLOSE cur_deposit_prior_amt;
881 EXCEPTION
882   WHEN OTHERS THEN
883     errbuf := SQLERRM;
884     retcode := -1;
885     FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
886     RAISE;
887 END process_record_type_16_25;
888 ------------------------------------------------------------------------------------------------
889 PROCEDURE process_record_type_26 IS
890   l_module_name VARCHAR2(200) ;
891   l_count_non_reclass NUMBER;
892 BEGIN
893  l_module_name := g_module_name || 'process_record_type_26';
894 --- Getting amount for record type = 26, net total
895 
896   l_count_non_reclass := 0;
897   SELECT COUNT(*)
898     INTO l_count_non_reclass
899     FROM fv_sf224_temp fst
900    WHERE column_group in (20,21, 30, 31)
901      AND reported_month in ('CURRENT', 'CURRENT/PRIOR')
902 	   AND alc_code = v_alc_code
903      AND fst.sf224_processed_flag = 'Y'
904      AND fst.end_period_date < v_end_date
905      AND fst.record_category = 'GLRECORD'
906      AND NVL(reclass, 'N') = 'N';
907 
911 
908 	v_net_total_amt := (NVL(v_deposit_prior_amt_record_26,0) +
909 				NVL(v_deposit_current_amt,0)) -
910 				NVL(v_collection_rcvd_amt,0);
912 	IF 	v_net_total_amt >= 0 THEN
913 		v_net_total_amt_goals := replace(replace(to_char(v_net_total_amt,'000000000000.00'),'.',''), ' ', '');
914 	ELSE
915 		v_net_total_amt_goals := replace(replace(to_char(v_net_total_amt,'00000000000.00'),'.',''), ' ', '');
916 	END IF;
917 
918 	v_record_type	:= '26';
919 	v_treasury_symbol 	:= '                    ';
920 	v_entry_number 		:= '  ';
921 	v_transaction_date	:= '    ';
922 
923 	v_record_26	:= 	v_record_type		||
924 				v_orig_suppl_ind		||
925 				v_alc_code 			||
926 				v_treasury_symbol		||
927 				v_entry_number		||
928 				v_net_total_amt_goals		||
929 				v_transaction_date	||
930 				v_reporting_date;
931 
932         IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
933  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RECORD 26 ************ ');
934  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V_DEPOSIT_PRIOR_AMT IS ' || V_DEPOSIT_PRIOR_AMT);
935  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V_DEPOSIT_CURRENT_AMT IS ' || V_DEPOSIT_CURRENT_AMT);
936  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V_COLLECTION_RCVD_AMT IS ' || V_COLLECTION_RCVD_AMT);
937         END IF;
938 
939 	IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
940  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V RECORD TYPE IS ' || V_RECORD_TYPE);
941  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V ORIG_SUPPL_IND IS ' || V_ORIG_SUPPL_IND);
942  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'NET TOTAL IS ' || V_NET_TOTAL_AMT);
943  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RECORD 26 IS '|| V_RECORD_26);
944 	END IF;
945 
946 	IF ((v_alc_code IS NOT NULL) AND ((v_net_total_amt <> 0) OR ((v_net_total_amt = 0) AND (l_count_non_reclass > 0)))) THEN
947 	  insert into fv_goals_224_temp(goals_224_temp_id, goals_224_record_type, alc_code, goals_224_record, supplemental_flag)
948 	  values(fv_goals_224_temp_id_s.nextval, v_record_type, v_alc_code, v_record_26, v_orig_suppl_ind);
949 	END IF;
950 EXCEPTION
951   WHEN OTHERS THEN
952     errbuf := SQLERRM;
953     retcode := -1;
954     FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
955     RAISE;
956 
957 END process_record_type_26;
958 ------------------------------------------------------------------------------------------------
959 PROCEDURE process_record_type_98 IS
960   l_module_name VARCHAR2(200) ;
961 BEGIN
962  l_module_name  := g_module_name || 'process_record_type_98';
963 --- Getting amount for record type = 98, subtotal for the number of records for each alc of the bulk file
964 
965 	SELECT	lpad(count(*), 14, '0'), max(supplemental_flag)
966 	INTO		v_count_per_alc, v_orig_suppl_ind
967 	FROM		fv_goals_224_temp
968 	WHERE alc_code = v_alc_code;
969 
970 	v_record_type	:= '98';
971 
972 	IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
973  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V RECORD TYPE IS ' || V_RECORD_TYPE);
974  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V COUNT PER ALC IS ' || V_COUNT_PER_ALC);
975 	END IF;
976 
977 	v_treasury_symbol 	:= '                    ';
978 	v_entry_number 		:= '  ';
979 	v_transaction_date	:= '    ';
980 
981 	v_record_98	:= 	v_record_type		||
982 				v_orig_suppl_ind		||
983 				v_alc_code 			||
984 				v_treasury_symbol		||
985 				v_entry_number		||
986 				v_count_per_alc		||
987 				v_transaction_date	||
988 				v_reporting_date;
989 
990 	IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
991  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RECORD 98 IS '|| V_RECORD_98);
992 	END IF;
993 
994 	IF ((v_alc_code IS NOT NULL) AND (v_count_per_alc <> 0)) THEN
995 	  insert into fv_goals_224_temp(goals_224_temp_id, goals_224_record_type, alc_code, goals_224_record, supplemental_flag)
996 	  values(fv_goals_224_temp_id_s.nextval, v_record_type, v_alc_code, v_record_98, v_orig_suppl_ind);
997 	END IF;
998 
999 	IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1000  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V ORIG_SUPPL_IND IS ' || V_ORIG_SUPPL_IND);
1001 	END IF;
1002 EXCEPTION
1003   WHEN OTHERS THEN
1004     errbuf := SQLERRM;
1005     retcode := -1;
1006     FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
1007     RAISE;
1008 
1009 END process_record_type_98;
1010 ------------------------------------------------------------------------------------------------
1011 PROCEDURE process_record_type_99 IS
1012   l_module_name VARCHAR2(200) ;
1013 BEGIN
1014   l_module_name := g_module_name || 'process_record_type_99';
1015 --- Getting count for all records, record type = 99, last record
1016 
1017 	SELECT	lpad(count(*), 14, '0')
1018 	INTO		v_count
1019 	FROM		fv_goals_224_temp
1020 	WHERE 	goals_224_record_type not in ('98','99');
1021 
1022 	v_record_type	:= '99';
1023 	IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1024  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V RECORD TYPE IS ' || V_RECORD_TYPE);
1025 	END IF;
1026 	v_orig_suppl_ind 		:= ' ';
1027 	v_alc_code			:= '        ';
1028 	v_treasury_symbol 	:= '                    ';
1029 	v_entry_number 		:= '  ';
1030 	v_transaction_date	:= '    ';
1031 
1032 	v_record_99	:= 	v_record_type  		||
1033 				v_orig_suppl_ind		||
1034 				v_alc_code 			||
1035 				v_treasury_symbol		||
1036 				v_entry_number		||
1037 				v_count			||
1038 				v_transaction_date	||
1039 				v_reporting_date;
1040 
1041 	IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1042  FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RECORD 99 IS '|| V_RECORD_99);
1043 	END IF;
1044 
1045 --if the only value that gets inserted into the temp table with amount of 0, it is a
1046 -- zero activity 224
1047   if (v_count <> 0) THEN
1048   	insert into fv_goals_224_temp(goals_224_temp_id, goals_224_record_type, goals_224_record, supplemental_flag)
1049   	values(fv_goals_224_temp_id_s.nextval, v_record_type, v_record_99, v_orig_suppl_ind);
1050   end if;
1051 EXCEPTION
1052   WHEN OTHERS THEN
1053     errbuf := SQLERRM;
1054     retcode := -1;
1055     FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
1056     RAISE;
1057 
1058 END process_record_type_99;
1059 ------------------------------------------------------------------------------------------------
1060 BEGIN
1061  g_module_name := 'fv.plsql.FV_GOALS_224.';
1062 
1063 END FV_GOALS_224;