[Home] [Help]
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'
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'
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'
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'
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
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'
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'
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'
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'
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'
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
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
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
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'
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'
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'
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'