[Home] [Help]
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
400 ------------------------------------------------------------------------------------------------
401 PROCEDURE process_record_type_01 IS
402 l_module_name VARCHAR2(200) ;
403 BEGIN
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
412
409 FETCH cur_receipts INTO v_treasury_symbol, v_receipt_amt,
410 v_orig_suppl_ind;
411 EXIT when cur_receipts%NOTFOUND;
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);
525 END IF;
526
527 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
528 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RECORD 02 IS '|| V_RECORD_02);
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;
545 ------------------------------------------------------------------------------------------------
542 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
543 RAISE;
544 END process_record_type_02;
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
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);
654 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THE PAYMENT TRANSACTION PRIOR AMOUNT IS ' || V_PMT_TRAN_PRIOR_AMT_GOALS);
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);
670 -- v_record_type := '0'||v_record_type;
667 v_record_type := v_record_type + 1;
668
669 -- Fix for bug 1483366
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'
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
776 v_deposit_current_amt := '00000000000000';
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);
805 END IF;
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);
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
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);
911
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;