[Home] [Help]
PACKAGE BODY: APPS.FV_SF224_TRANSACTIONS
Source
1 PACKAGE BODY FV_SF224_TRANSACTIONS AS
2 --$Header: FVSF224B.pls 120.56.12010000.1 2008/07/28 06:31:59 appldev ship $
3
4 -- Forward Declarations --
5 PROCEDURE check_alc_address(p_alc VARCHAR2);
6 FUNCTION get_void_check_obligation_date( p_invoice_id NUMBER,
7 p_check_id NUMBER,
8 p_inv_dist_num NUMBER) RETURN DATE;
9 PROCEDURE Check_partial_reporting (
10 p_business_activity_code IN fv_alc_addresses_all.business_activity_code%TYPE,
11 p_gwa_reporter_category_code IN fv_alc_gwa_categories.gwa_reporter_category_code%TYPE,
12 p_error_code OUT NOCOPY NUMBER,
13 p_error_desc OUT NOCOPY VARCHAR2
14 );
15
16 --=======================================================================
17
18 g_module_name VARCHAR2(100);
19 g_lo_date DATE;
20 g_FAILURE NUMBER;
21 g_SUCCESS NUMBER;
22 g_WARNING NUMBER;
23 g_request_id NUMBER;
24 g_user_id NUMBER;
25 g_login_id NUMBER;
26 g_org_id NUMBER;
27 g_enter VARCHAR2(10);
28 g_exit VARCHAR2(10);
29 g_sysdate DATE;
30
31 g_payment_type_flag ap_checks_all.PAYMENT_TYPE_FLAG%TYPE;
32 g_check_void_date DATE;
33 g_invoice_type_lookup_code ap_invoices_all.invoice_type_lookup_code%TYPE;
34
35 sob NUMBER;
36 error_code NUMBER;
37 error_buf VARCHAR2(2000);
38 update_flag VARCHAR2(10);
39
40 g_partial_or_full VARCHAR2(11);
41 g_business_activity VARCHAR2(30);
42 g_gwa_reporter_category VARCHAR2(30);
43 l_reportable VARCHAR2(1);
44
45
46 L_SF224_TYPE_CODE VARCHAR2(30);
47 L_NAME FV_SF224_TEMP.NAME%TYPE;
48 L_AMOUNT FV_SF224_TEMP.AMOUNT%TYPE;
49 L_D_R_FLAG FV_SF224_TEMP.D_R_FLAG%TYPE;
50 L_ACCOMPLISH_DATE DATE;
51
52 -- References Teasury confirmation Id
53 L_REFERENCE_1 FV_SF224_TEMP.REFERENCE_1%TYPE;
54 L_REFERENCE_2 FV_SF224_TEMP.REFERENCE_2%TYPE;
55
56 -- References Check_id
57 L_REFERENCE_3 FV_SF224_TEMP.REFERENCE_3%TYPE;
58 L_REFERENCE_8 GL_JE_LINES.REFERENCE_8%TYPE; -- Invoice Dist Line Num
59
60 -- New Variable added as a part of bug fix #983941
61 L_REFERENCE_9 FV_SF224_TEMP.REFERENCE_9%TYPE ;
62
63 -- A new vaiable added l_accomplish_attribute for Bug 1801069
64 l_accomplish_attribute VARCHAR2( 15);
65 L_JE_LINE_NUM FV_SF224_TEMP.JE_LINE_NUM%TYPE ;
66 L_REPORTED_FLAG FV_SF224_TEMP.REPORTED_FLAG%TYPE ;
67 L_EXCEPTION_EXISTS FV_SF224_TEMP.EXCEPTION_EXISTS%TYPE ;
68 L_RECORD_CATEGORY FV_SF224_TEMP.RECORD_CATEGORY%TYPE ;
69 L_JE_HEADER_ID FV_SF224_TEMP.JE_HEADER_ID%TYPE ;
70 L_EXCEPTION_SECTION FV_SF224_TEMP.EXCEPTION_SECTION%TYPE;
71
72 L_GL_PERIOD GL_PERIODS.PERIOD_NAME%TYPE;
73 L_REPORTED_MONTH FV_SF224_TEMP.REPORTED_MONTH%TYPE;
74 L_EXCEPTION_CATEGORY FV_SF224_TEMP.EXCEPTION_CATEGORY%TYPE;
75 L_COLUMN_GROUP NUMBER;
76 L_TXN_CATEGORY FV_SF224_TEMP.TXN_CATEGORY%TYPE;
77 L_BILLING_AGENCY_FUND FV_INTERAGENCY_FUNDS.BILLING_AGENCY_FUND%TYPE;
78
79 L_BATCH_ID FV_SF224_TEMP.JE_BATCH_ID%TYPE;
80 L_RECORD_TYPE VARCHAR2(30);
81 L_IA_FLAG VARCHAR2(2);
82 L_OBLIGATION_DATE DATE;
83
84 p_def_org_id NUMBER(15) ;
85 p_def_alc_code VARCHAR2(12) ;
86 l_treasury_symbol VARCHAR2(35);
87 l_treasury_symbol_id NUMBER(15);
88 l_sign_number NUMBER;
89 l_type VARCHAR2(25);
90 l_alc_code VARCHAR2(12);
91 flex_num NUMBER ;
92 x_name VARCHAR2(100);
93
94 l_je_source GL_JE_HEADERS.JE_SOURCE%TYPE;
95 l_je_category GL_JE_HEADERS.JE_CATEGORY%TYPE;
96 l_je_from_sla_flag FV_SF224_TEMP.JE_FROM_SLA_FLAG%TYPE;
97 l_document_number VARCHAR2(100);
98
99 --Define variable to store the end date for the transaction period
100 l_txn_end_date GL_PERIODS.END_DATE%TYPE;
101 l_txn_start_date GL_PERIODS.START_DATE%TYPE;
102
103
104 PROCEDURE process_sf224_transactions;
105 PROCEDURE Set_Exception(exp_type VARCHAR2) ;
106
107 PROCEDURE get_alc ( p_bank_acct_id IN NUMBER,
108 p_alc_code OUT NOCOPY VARCHAR2,
109 p_error_code OUT NOCOPY NUMBER,
110 p_error_desc OUT NOCOPY VARCHAR2 );
111
112 PROCEDURE set_transaction_type;
113 PROCEDURE Insert_new_transaction(x_amount Number, x_sign_number number);
114
115 ---------------------------------------------------------------------------------
116 ----- INITIALIZE_GLOBAL_VARIABLES
117 --------------------------------------------------------------------------------
118 PROCEDURE initialize_global_variables
119 IS
120 BEGIN
121 g_module_name := 'fv.plsql.fv_sf224_transactions.';
122 g_lo_date := TO_DATE('01/01/0001', 'DD/MM/YYYY');
123 g_FAILURE := -1;
124 g_SUCCESS := 0;
125 g_WARNING := -2;
126 g_request_id := fnd_global.conc_request_id;
127 g_user_id := fnd_global.user_id;
128 g_login_id := fnd_global.login_id;
129 g_org_id := mo_global.get_current_org_id;
130 g_enter := 'ENTER';
131 g_exit := 'EXIT';
132 g_sysdate := SYSDATE;
133 END;
134
135 ---------------------------------------------------------------------------------
136 ----- POST_PROCESS_FOR_MAIN
137 ---------------------------------------------------------------------------------
138 PROCEDURE post_process_for_main
139 (
140 p_set_of_books_id IN NUMBER,
141 p_gl_period IN VARCHAR2,
142 p_alc IN VARCHAR2,
143 p_run_mode IN VARCHAR2,
144 p_partial_or_full IN VARCHAR2,
145 p_business_activity IN VARCHAR2,
146 p_gwa_reporter_category IN VARCHAR2,
147 p_end_period_date OUT NOCOPY DATE,
148 p_error_code OUT NOCOPY NUMBER,
149 p_error_desc OUT NOCOPY VARCHAR2
150 )
151 IS
152 l_module_name VARCHAR2(200);
153 l_location VARCHAR2(200);
154 l_exists VARCHAR2(2);
155 l_last_fetch BOOLEAN;
156 l_yr_start_date DATE;
157 l_start_date gl_periods.start_date%TYPE;
158 l_end_date gl_periods.end_date%TYPE;
159 l_start_date_2 gl_periods.start_date%TYPE;
160 l_end_date_2 gl_periods.end_date%TYPE;
161 l_cash_receipt_id NUMBER;
162 l_hi_date DATE := TO_DATE('12/31/9999', 'MM/DD/YYYY');
163 l_business_activity_code fv_alc_addresses_all.business_activity_code%TYPE;
164 l_gwa_reporter_category_code fv_alc_gwa_categories.gwa_reporter_category_code%TYPE;
165 l_include_in_report fv_sf224_map.trx_category_coll%TYPE;
166
167
168
169 CURSOR current_224_cur
170 (
171 c_set_of_books_id NUMBER,
172 c_alc_code VARCHAR2,
173 c_end_date DATE
174 ) IS
175 SELECT fst.rowid,
176 fst.gl_period,
177 fst.accomplish_date,
178 UPPER(fst.sf224_type_code),
179 fst.record_type,
180 fst.inter_agency_flag,
181 fst.obligation_date,
182 fst.d_r_flag,
183 fst.column_group,
184 fst.reported_month,
185 fst.exception_category,
186 fst.exception_section,
187 fst.reported_gl_period,
188 fst.supplemental_flag,
189 fst.alc_code,
190 fst.reference_2,
191 fst.reference_3,
192 fst.processed_flag,
193 fst.update_type,
194 fst.je_source,
195 fst.je_category,
196 fst.txn_category,
197 fst.sign_number,
198 fst.amount,
199 fst.actual_amount,
200 fst.reclass,
201 fst.reported_flag,
202 fst.je_from_sla_flag
203 FROM fv_sf224_temp fst
204 WHERE fst.set_of_books_id = c_set_of_books_id
205 AND fst.sf224_processed_flag = 'Y'
206 AND fst.alc_code = DECODE (c_alc_code, 'ALL', fst.alc_code, c_alc_code)
207 AND fst.end_period_date < c_end_date;
208
209 TYPE alc_supplemental_r IS RECORD
210 (
211 alc_code VARCHAR2(30),
212 supplemental_flag VARCHAR2(1)
213 );
214 TYPE alc_supplemental_t IS TABLE OF alc_supplemental_r INDEX BY BINARY_INTEGER;
215 l_alc_supplemental alc_supplemental_t;
216 l_tot_alc_supplemental NUMBER := 0;
217 l_tmp_supplemental_flag NUMBER;
218
219 TYPE row_id_t IS TABLE OF rowid;
220 TYPE gl_period_t IS TABLE OF fv_sf224_temp.gl_period%TYPE;
221 TYPE accomplish_date_t IS TABLE OF fv_sf224_temp.accomplish_date%TYPE;
222 TYPE reported_month_t IS TABLE OF fv_sf224_temp.reported_month%TYPE;
223 TYPE exception_category_t IS TABLE OF fv_sf224_temp.exception_category%TYPE;
224 TYPE sf224_type_code_t IS TABLE OF fv_sf224_temp.sf224_type_code%TYPE;
225 TYPE record_type_t IS TABLE OF fv_sf224_temp.record_type%TYPE;
226 TYPE column_group_t IS TABLE OF fv_sf224_temp.column_group%TYPE;
227 TYPE inter_agency_flag_t IS TABLE OF fv_sf224_temp.inter_agency_flag%TYPE;
228 TYPE obligation_date_t IS TABLE OF fv_sf224_temp.obligation_date%TYPE;
229 TYPE d_r_flag_t IS TABLE OF fv_sf224_temp.d_r_flag%TYPE;
230 TYPE exception_section_t IS TABLE OF fv_sf224_temp.exception_section%TYPE;
231 TYPE reported_gl_period_t IS TABLE OF fv_sf224_temp.reported_gl_period%TYPE;
232 TYPE supplemental_flag_t IS TABLE OF fv_sf224_temp.supplemental_flag%TYPE;
233 TYPE alc_code_t IS TABLE OF fv_sf224_temp.alc_code%TYPE;
234 TYPE reference_2_t IS TABLE OF fv_sf224_temp.reference_2%TYPE;
235 TYPE reference_3_t IS TABLE OF fv_sf224_temp.reference_3%TYPE;
236 TYPE processed_flag_t IS TABLE OF fv_sf224_temp.processed_flag%TYPE;
237 TYPE update_type_t IS TABLE OF fv_sf224_temp.update_type%TYPE;
238 TYPE je_source_t IS TABLE OF fv_sf224_temp.je_source%TYPE;
239 TYPE je_category_t IS TABLE OF fv_sf224_temp.je_category%TYPE;
240 TYPE txn_category_t IS TABLE OF fv_sf224_temp.txn_category%TYPE;
241 TYPE sign_number_t IS TABLE OF fv_sf224_temp.sign_number%TYPE;
242 TYPE amount_t IS TABLE OF fv_sf224_temp.amount%TYPE;
243 TYPE actual_amount_t IS TABLE OF fv_sf224_temp.actual_amount%TYPE;
244 TYPE reclass_t IS TABLE OF fv_sf224_temp.reclass%TYPE;
245 TYPE reported_flag_t IS TABLE OF fv_sf224_temp.reported_flag%TYPE;
246 TYPE je_from_sla_flag_t IS TABLE OF fv_sf224_temp.je_from_sla_flag%TYPE;
247
248 l_rowid row_id_t;
249 l_gl_period gl_period_t;
250 l_accomplish_date accomplish_date_t;
251 l_reported_month reported_month_t;
252 l_exception_category exception_category_t;
253 l_sf224_type_code sf224_type_code_t;
254 l_record_type record_type_t;
255 l_column_group column_group_t;
256 l_inter_agency_flag inter_agency_flag_t;
257 l_obligation_date obligation_date_t;
258 l_d_r_flag d_r_flag_t;
259 l_exception_section exception_section_t;
260 l_reported_gl_period reported_gl_period_t;
261 l_supplemental_flag supplemental_flag_t;
262 l_alc_code alc_code_t;
263 l_reference_2 reference_2_t;
264 l_reference_3 reference_3_t;
265 l_processed_flag processed_flag_t;
266 l_update_type update_type_t;
267 l_je_source je_source_t;
268 l_je_category je_category_t;
269 l_txn_category txn_category_t;
270 l_sign_number sign_number_t;
271 l_amount amount_t;
272 l_actual_amount actual_amount_t;
273 l_reclass reclass_t;
274 l_reported_flag reported_flag_t;
275 l_je_from_sla_flag je_from_sla_flag_t;
276
277 BEGIN
278 l_module_name := g_module_name || '.extract';
279 p_error_code := g_SUCCESS;
280
281 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
282 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
283 END IF;
284
285 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
286 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_set_of_books_id = '||p_set_of_books_id);
287 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_gl_period = '||p_gl_period);
288 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_alc = '||p_alc);
289 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_run_mode = '||p_run_mode);
290 END IF;
291
292 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
293 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'SELECT FROM gl_period_statuses');
294 END IF;
295 BEGIN
296 SELECT start_date,
297 end_date,
298 year_start_date
299 INTO l_start_date,
300 l_end_date,
301 l_yr_start_date
302 FROM gl_period_statuses glp
303 WHERE glp.period_name = p_gl_period
304 AND glp.ledger_id = p_set_of_books_id
305 AND glp.application_id = 101;
306 p_end_period_date := l_end_date;
307 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
308 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_start_date ='||l_start_date);
309 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_end_date ='||l_end_date);
310 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_yr_start_date ='||l_yr_start_date);
311 END IF;
312 EXCEPTION
313 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
314 p_error_code := g_FAILURE;
315 p_error_desc := 'No Such Period (' || p_gl_period|| ') exists';
316 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
317 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
318 WHEN OTHERS THEN
319 p_error_code := g_FAILURE;
320 p_error_desc := SQLERRM;
321 l_location := l_module_name||'select_gl_period_statuses1';
322 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
323 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
324 END ;
325
326 IF (p_error_code = g_SUCCESS) THEN
327
328 l_last_fetch := FALSE;
329 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
330 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'OPEN current_224_cur');
331 END IF;
332 OPEN current_224_cur (p_set_of_books_id, p_alc, TRUNC(l_end_date)+1);
333 LOOP
334 FETCH current_224_cur
335 BULK COLLECT INTO
336 l_rowid,
337 l_gl_period,
338 l_accomplish_date,
339 l_sf224_type_code,
340 l_record_type,
341 l_inter_agency_flag,
342 l_obligation_date,
343 l_d_r_flag,
344 l_column_group,
345 l_reported_month,
346 l_exception_category,
347 l_exception_section,
348 l_reported_gl_period,
349 l_supplemental_flag,
350 l_alc_code,
351 l_reference_2,
352 l_reference_3,
353 l_processed_flag,
354 l_update_type,
355 l_je_source,
356 l_je_category,
357 l_txn_category,
358 l_sign_number,
359 l_amount,
360 l_actual_amount,
361 l_reclass,
362 l_reported_flag,
363 l_je_from_sla_flag;
364
365 IF current_224_cur%NOTFOUND THEN
366 l_last_fetch := TRUE;
367 END IF;
368
369 IF (l_gl_period.count = 0 AND l_last_fetch) THEN
370 EXIT;
371 END IF;
372
373 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
374 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'FETCH current_224_cur');
375 END IF;
376 FOR i IN l_rowid.first .. l_rowid.last LOOP
377 BEGIN
378 SELECT start_date,
379 end_date
380 INTO l_start_date_2,
381 l_end_date_2
382 FROM gl_period_statuses glp
383 WHERE glp.period_name = l_gl_period(i)
384 AND glp.ledger_id = p_set_of_books_id
385 AND glp.application_id = 101;
386 EXCEPTION
387 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
388 p_error_code := g_FAILURE;
389 p_error_desc := 'No Such Period (' || l_gl_period(i)|| ') exists';
390 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
391 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
392 WHEN OTHERS THEN
393 p_error_code := g_FAILURE;
394 p_error_desc := SQLERRM;
395 l_location := l_module_name||'select_gl_period_statuses2';
396 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
397 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
398 END;
399
400 IF (p_error_code = g_SUCCESS) THEN
401 IF (l_accomplish_date(i) BETWEEN l_start_date AND l_end_date) THEN
402 l_reported_month (i) := 'CURRENT';
403 l_reported_gl_period(i) := p_gl_period;
404 IF(l_end_date_2 = l_end_date) THEN
405 l_exception_category(i) := NULL;
406 l_exception_section(i) := -1;
407 ELSIF (l_end_date_2 < l_end_date) THEN
408 l_exception_category(i) := 'PRIOR PERIOD';
409 l_exception_section(i) := 1;
410 ELSIF (l_end_date_2 > l_end_date) THEN
411 l_exception_category(i) := 'FUTURE PERIOD';
412 l_exception_section(i) := 2;
413 END IF;
414 ELSIF(l_accomplish_date(i) < l_start_date) THEN
415 l_reported_month(i) := 'CURRENT/PRIOR';
416 l_reported_gl_period(i) := p_gl_period;
417 IF(l_end_date_2 = l_end_date) THEN
418 l_exception_category(i) := NULL;
419 l_exception_section(i) := -1;
420 ELSIF (l_end_date_2 < l_end_date) THEN
421 l_exception_category(i) := 'PRIOR PERIOD';
422 l_exception_section(i) := 1;
423 ELSIF (l_end_date_2 > l_end_date) THEN
424 l_exception_category(i) := 'FUTURE PERIOD';
425 l_exception_section(i) := 2;
426 END IF;
427 ELSE
428 l_reported_month(i) := 'FUTURE';
429 IF (l_end_date_2 = l_end_date) THEN
430 l_exception_category(i) := 'FUTURE_ACOMPLISH';
431 l_exception_section(i) := 3;
432 ELSIF (l_end_date_2 < l_end_date) THEN
433 l_exception_category(i) := 'FUTURE ACCOMPLISH';
434 l_exception_section(i) := 3;
435 ELSIF (l_end_date_2 > l_end_date) THEN
436 l_exception_category(i) := 'FUTURE PERIOD';
437 l_exception_section(i) := 3;
438 END IF;
439 END IF;
440
441 IF (l_record_type(i) like '%refund%' or l_record_type(i) = 'VOID') THEN
442 IF (l_obligation_date(i) < l_yr_start_date) THEN
443 l_column_group(i) := 20 ;
444 l_txn_category(i) := 'C';
445 IF ((l_record_type(i) = 'VOID') OR
446 (l_record_type(i) = 'Receipt_refund' AND l_inter_agency_flag(i) = 'Y')) THEN
447 l_column_group(i) := 21;
448 l_txn_category(i) := 'P';
449 END IF;
450 ELSE
451 l_column_group(i) := 30 ;
452 l_txn_category(i) := 'P';
453 l_sign_number(i) := -1;
454 IF(l_record_type(i) = 'Receipt_refund' AND l_inter_agency_flag(i) = 'N') THEN
455 l_column_group(i) := 31;
456 l_txn_category(i) := 'C';
457 END IF;
458 END IF;
459 ELSIF (l_record_type(i) = 'PAYABLE_REFUND') THEN
460 IF l_obligation_date(i) < l_yr_start_date THEN
461 l_column_group(i) := 20;
462 l_txn_category(i) := 'C';
463 l_sign_number(i) := 1;
464 ELSE
465 l_column_group(i) := 31;
466 l_txn_category(i) := 'C';
467 l_sign_number(i) := -1;
468 END IF;
469 END IF;
470 END IF;
471
472 IF (p_error_code = g_SUCCESS) THEN
473 IF (l_reported_month(i) LIKE 'CURRENT%') THEN
474 FOR j IN 1..l_tot_alc_supplemental LOOP
475 IF (l_alc_code(i) = l_alc_supplemental(j).alc_code) THEN
476 l_supplemental_flag(i) := l_alc_supplemental(j).supplemental_flag;
477 END IF;
478 END LOOP;
479
480 IF (l_supplemental_flag(i) IS NULL) THEN
481 BEGIN
482 SELECT MAX(supplemental_flag)
483 INTO l_tmp_supplemental_flag
484 FROM fv_sf224_audits
485 WHERE reported_gl_period = p_gl_period
486 AND set_of_books_id = p_set_of_books_id
487 AND alc_code = l_alc_code(i);
488
489 IF (l_tmp_supplemental_flag IS NULL) THEN
490 l_supplemental_flag(i) := '0';
491 l_tot_alc_supplemental := l_tot_alc_supplemental + 1;
492 l_alc_supplemental(l_tot_alc_supplemental).supplemental_flag := l_supplemental_flag(i);
493 l_alc_supplemental(l_tot_alc_supplemental).alc_code := l_alc_code(i);
494 ELSIF (l_tmp_supplemental_flag < 3) THEN
495 l_supplemental_flag(i) := TO_CHAR(l_tmp_supplemental_flag+1);
496 l_tot_alc_supplemental := l_tot_alc_supplemental + 1;
497 l_alc_supplemental(l_tot_alc_supplemental).supplemental_flag := l_supplemental_flag(i);
498 l_alc_supplemental(l_tot_alc_supplemental).alc_code := l_alc_code(i);
499 ELSE
500 l_supplemental_flag(i) := '3';
501 l_tot_alc_supplemental := l_tot_alc_supplemental + 1;
502 l_alc_supplemental(l_tot_alc_supplemental).supplemental_flag := l_supplemental_flag(i);
503 l_alc_supplemental(l_tot_alc_supplemental).alc_code := l_alc_code(i);
504 END IF;
505 EXCEPTION
506 WHEN NO_DATA_FOUND THEN
507 l_supplemental_flag(i) := '0';
508 l_tot_alc_supplemental := l_tot_alc_supplemental + 1;
509 l_alc_supplemental(l_tot_alc_supplemental).supplemental_flag := l_supplemental_flag(i);
510 l_alc_supplemental(l_tot_alc_supplemental).alc_code := l_alc_code(i);
511 WHEN OTHERS THEN
512 p_error_code := g_FAILURE;
513 p_error_desc := SQLERRM;
514 l_location := l_module_name||'select_fv_sf224_audits';
515 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
516 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
517 END;
518 END IF;
519 END IF;
520 END IF;
521
522 ---------------------------------- by KS 3-OCT-2006 ------------------------
523 -- CHECK whether any cash receipt been entered/deleted in fv_interangy_funds_all
524 -- table after pre process run.User could have added/deleted the records
525
526 IF (l_je_source(i) = 'Receivables') THEN
527 IF NVL(l_je_from_sla_flag(i),'N') IN ('N','U') THEN
528 IF (l_je_category(i) = 'Misc Receipts') THEN
529 l_cash_receipt_id := l_reference_2(i);
530 ELSE
531 l_cash_receipt_id := SUBSTR(l_reference_2(i),0,INSTR(l_reference_2(i),'C')-1);
532 END IF;
533 BEGIN
534 SELECT 'x'
535 INTO l_exists
536 FROM fv_interagency_funds_all
537 WHERE cash_receipt_id = l_cash_receipt_id;
538 l_inter_agency_flag(i) := 'Y';
539 l_update_type(i) := 'RECEIPT';
540 l_column_group(i) := 21;
541 EXCEPTION
542 WHEN no_data_found THEN
543 l_inter_agency_flag(i) := 'N';
544 l_update_type(i) := NULL;
545 l_column_group(i) := 20;
546 END;
547 ELSE --l_je_from_sla_flag is 'Y'
548 l_cash_receipt_id := l_reference_2(i);
549 BEGIN
550 SELECT 'x'
551 INTO l_exists
552 FROM fv_interagency_funds_all
553 WHERE cash_receipt_id = l_cash_receipt_id;
554 l_inter_agency_flag(i) := 'Y';
555 l_update_type(i) := 'RECEIPT';
556 l_column_group(i) := 21;
557 EXCEPTION
558 WHEN no_data_found THEN
559 l_inter_agency_flag(i) := 'N';
560 l_update_type(i) := NULL;
561 l_column_group(i) := 20;
562 END;
563 END IF;
564 END IF;
565
566 ---------------------------------------------------------------------------
567
568 IF (p_error_code = g_SUCCESS) THEN
569 IF (p_run_mode = 'F') THEN
570
571 IF (l_inter_agency_flag(i) = 'Y' and l_reported_month(i) like '%CURRENT%') THEN
572 BEGIN
573 UPDATE fv_interagency_funds_all
574 SET processed_flag = 'Y',
575 period_reported = p_gl_period,
576 last_updated_by = g_user_id,
577 last_update_date = g_sysdate,
578 last_update_login = g_login_id
579 WHERE DECODE(l_update_type(i),'RECEIPT', cash_receipt_id, invoice_id) =
580 DECODE(l_update_type(i),'RECEIPT', to_number(l_cash_receipt_id),to_number(l_reference_2(i)));
581 EXCEPTION
582 WHEN OTHERS THEN
583 p_error_code := g_FAILURE;
584 p_error_desc := SQLERRM;
585 l_location := l_module_name||'update_fv_interagency_funds_all';
586 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
587 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
588 END;
589 END IF;
590
591 IF (l_processed_flag(i) = 'Y' AND l_reported_month(i) like '%CURRENT%') then
592 BEGIN
593 UPDATE fv_refunds_voids_all
594 SET processed_flag = 'Y',
595 period_reported = p_gl_period,
596 last_updated_by = g_user_id,
597 last_update_date = g_sysdate,
598 last_update_login = g_login_id
599 WHERE DECODE(l_update_type(i),'RECEIPT', cash_receipt_id, invoice_id) =
600 DECODE(l_update_type(i),'RECEIPT', TO_NUMBER(l_cash_receipt_id),to_number(l_reference_2(i)))
601 AND type = l_type
602 AND NVL(check_id,0) = DECODE(l_update_type(i),'RECEIPT', NVL(check_id,0), to_number(l_reference_3(i)));
603 EXCEPTION
604 WHEN OTHERS THEN
605 p_error_code := g_FAILURE;
606 p_error_desc := SQLERRM;
607 l_location := l_module_name||'update_fv_refunds_voids_all';
608 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
609 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
610 END;
611 END IF;
612 END IF;
613 END IF;
614
615 /*
616 Adding logic here to restrict the parameters based on Partial reporting.
617 Or else there will be massive changes in the reports. So for the time being
618 we shall change the reported month to something other than CURRENT% to avoid
619 getting picked up by the report. This column always gets populated during the
620 pre process.
621 */
622
623 IF (p_partial_or_full='Partial') then
624 IF (p_error_code = g_SUCCESS) THEN
625 BEGIN
626 SELECT fa.business_activity_code,
627 fa.gwa_reporter_category_code
628 INTO l_business_activity_code,
629 l_gwa_reporter_category_code
630 FROM fv_alc_business_activity_v fa
631 WHERE fa.set_of_books_id = p_set_of_books_id
632 AND fa.agency_location_code = l_alc_code(i)
633 AND fa.period_name = l_gl_period(i);
634 EXCEPTION
635 WHEN NO_DATA_FOUND THEN
636 l_business_activity_code := NULL;
637 l_gwa_reporter_category_code := NULL;
638 WHEN OTHERS THEN
639 p_error_code := g_FAILURE;
640 p_error_desc := SQLERRM;
641 l_location := l_module_name||'select_fv_alc_business_activity_v';
642 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
643 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
644 END;
645 END IF;
646
647 IF (p_error_code = g_SUCCESS) THEN
648 BEGIN
649 SELECT DECODE(l_txn_category(i), 'C', trx_category_coll,
650 'P', trx_category_pay,
651 'I', trx_category_intra,
652 'I')
653 INTO l_include_in_report
654 FROM fv_sf224_map
655 WHERE NVL(business_activity_code, 'NULL') = NVL(l_business_activity_code, 'NULL')
656 AND NVL(gwa_reporter_category_code, 'NULL') = NVL(l_gwa_reporter_category_code, 'NULL');
657 EXCEPTION
658 WHEN NO_DATA_FOUND THEN
659 NULL;
660 WHEN OTHERS THEN
661 p_error_code := g_FAILURE;
662 p_error_desc := SQLERRM;
663 l_location := l_module_name||'select_fv_sf224_map';
664 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
665 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
666 END;
667 END IF;
668
669 IF ((p_business_activity <> 'ALL') AND (l_business_activity_code <> p_business_activity)) THEN
670 l_include_in_report := 'X';
671 END IF;
672
673 IF ((p_gwa_reporter_category <> 'ALL') AND (l_gwa_reporter_category_code <> p_gwa_reporter_category)) THEN
674 l_include_in_report := 'X';
675 END IF;
676
677
678 IF (l_include_in_report IN ('X', 'E')) THEN
679 l_reported_month(i) := 'NOT REPORTED';
680 END IF;
681
682 END IF;
683
684
685
686 IF (p_error_code <> g_SUCCESS) THEN
687 EXIT;
688 END IF;
689 END LOOP;
690
691 IF (p_error_code = g_SUCCESS) THEN
692 BEGIN
693 FORALL i IN l_rowid.first .. l_rowid.last
694 UPDATE fv_sf224_temp fst
695 SET fst.column_group = l_column_group(i),
696 fst.exception_category = l_exception_category(i),
697 fst.reported_month = l_reported_month(i),
698 fst.reported_flag = DECODE(l_reported_month(i), 'CURRENT', 'Y', 'CURRENT/PRIOR','Y','N'),
699 fst.exception_section = l_exception_section (i),
700 fst.reported_gl_period = l_reported_gl_period(i),
701 fst.supplemental_flag = l_supplemental_flag(i),
702 fst.txn_category = l_txn_category(i),
703 fst.sign_number = l_sign_number(i),
704 fst.amount = l_actual_amount(i) * l_sign_number(i),
705 fst.last_updated_by = g_user_id,
706 fst.last_update_date = g_sysdate,
707 fst.last_update_login = g_login_id,
708 fst.updated_request_id = g_request_id
709 WHERE ROWID = l_rowid(i);
710
711 EXCEPTION
712 WHEN OTHERS THEN
713 p_error_code := g_FAILURE;
714 p_error_desc := SQLERRM;
715 l_location := l_module_name||'update_fv_sf224_temp';
716 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
717 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
718 END;
719 END IF;
720 END LOOP;
721
722 IF (current_224_cur%ISOPEN) THEN
723 CLOSE current_224_cur;
724 END IF;
725 END IF;
726 EXCEPTION
727 WHEN OTHERS THEN
728 p_error_code := g_FAILURE;
729 p_error_desc := SQLERRM;
730 l_location := l_module_name||'.final_exception';
731 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
732 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
733 IF (current_224_cur%ISOPEN) THEN
734 CLOSE current_224_cur;
735 END IF;
736 END post_process_for_main;
737
738 ----------------------------------------------------------------------
739 -- UPDATE_AUDIT_INFO
740 ----------------------------------------------------------------------
741 PROCEDURE update_audit_info
742 (
743 p_set_of_books_id IN NUMBER,
744 p_alc IN VARCHAR2,
745 p_end_period_date IN DATE,
746 p_error_code OUT NOCOPY NUMBER,
747 p_error_desc OUT NOCOPY VARCHAR2
748 )
749 IS
750 l_module_name VARCHAR2(200) ;
751 l_location VARCHAR2(200);
752
753 BEGIN
754 l_module_name := g_module_name || 'Update_audit_info';
755 p_error_code := g_SUCCESS;
756 -- Updating Audit tables
757
758 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
759 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'INSERTING INTO THE AUDITS TABLE.');
760 END IF;
761
762 BEGIN
763 INSERT INTO fv_sf224_audits
764 (
765 batch_id ,
766 sf224_month_reported,
767 reported_month,
768 column_group,
769 treasury_symbol_id,
770 created_by,
771 creation_date,
772 last_update_date,
773 last_updated_by,
774 last_update_login,
775 supplemental_flag,
776 exception_category,
777 gl_period,
778 reported_gl_period,
779 set_of_books_id,
780 alc_code,
781 inter_agency_flag,
782 je_header_id,
783 je_line_num,
784 record_type,
785 je_source,
786 je_category,
787 document_number,
788 je_from_sla_flag
789 )
790 SELECT je_batch_id,
791 TO_CHAR(accomplish_date, 'MM-YYYY'),
792 reported_month,
793 column_group,
794 treasury_symbol_id,
795 g_user_id,
796 g_sysdate,
797 g_sysdate,
798 g_user_id,
799 g_login_id,
800 supplemental_flag,
801 exception_category,
802 gl_period,
803 reported_gl_period,
804 set_of_books_id,
805 alc_code,
806 inter_agency_flag,
807 je_header_id,
808 je_line_num,
809 record_type,
810 je_source,
811 je_category,
812 document_number,
813 je_from_sla_flag
814 FROM fv_sf224_temp fst
815 WHERE ((fst.reported_month in ('CURRENT/PRIOR','CURRENT')
816 AND fst.reported_flag = 'Y'
817 AND fst.record_category = 'GLRECORD')
818 OR (exception_category IN ('INVALID_BA_GWA_SEC_COMBO', 'GWA_REPORTABLE')))
819 AND fst.set_of_books_id = p_set_of_books_id
820 AND fst.alc_code = DECODE (p_alc, 'ALL', alc_code, p_alc)
821 AND fst.end_period_date < TRUNC(p_end_period_date)+1;
822 EXCEPTION
823 WHEN OTHERS THEN
824 p_error_code := g_FAILURE;
825 p_error_desc := SQLERRM;
826 l_location := l_module_name||'insert_fv_sf224_temp';
827 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
828 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
829 END;
830
831 IF (p_error_code = g_SUCCESS) THEN
832 BEGIN
833 DELETE fv_sf224_temp fst
834 WHERE fst.reported_month in ('CURRENT/PRIOR','CURRENT')
835 AND fst.reported_flag = 'Y'
836 AND fst.record_category = 'GLRECORD'
837 AND fst.set_of_books_id = p_set_of_books_id
838 AND fst.alc_code = DECODE (p_alc, 'ALL', alc_code, p_alc)
839 AND fst.end_period_date < TRUNC(p_end_period_date)+1;
840 EXCEPTION
841 WHEN OTHERS THEN
842 p_error_code := g_FAILURE;
843 p_error_desc := SQLERRM;
844 l_location := l_module_name||'delete_fv_sf224_temp';
845 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
846 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
847 END;
848 END IF;
849
850 EXCEPTION
851 WHEN OTHERS THEN
852 p_error_code := g_FAILURE;
853 p_error_desc := SQLERRM;
854 l_location := l_module_name||'.final_exception';
855 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
856 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
857
858 END update_audit_info;
859
860 --------------------------------------------------------------------
861 ----- SUBMIT_224_REPORT
862 --------------------------------------------------------------------
863 PROCEDURE submit_224_report
864 (
865 p_set_of_books_id IN NUMBER,
866 p_gl_period IN VARCHAR2,
867 p_alc IN VARCHAR2,
868 p_run_mode IN VARCHAR2,
869 p_partial_or_full IN VARCHAR2,
870 p_business_activity IN VARCHAR2,
871 p_gwa_reporter_category IN VARCHAR2,
872 p_error_code OUT NOCOPY NUMBER,
873 p_error_desc OUT NOCOPY VARCHAR2
874 )
875 IS
876 l_module_name VARCHAR2(200) ;
877 l_location VARCHAR2(200);
878
879 l_req_id1 NUMBER;
880 l_req_id2 NUMBER;
881 l_req_id3 NUMBER;
882 l_req_id4 NUMBER;
883 l_req_id5 NUMBER;
884 l_call_status BOOLEAN;
885 l_dev_phase VARCHAR2(80);
886 l_phase VARCHAR2(80);
887 l_status VARCHAR2(80);
888 l_dev_status VARCHAR2(80);
889 l_message VARCHAR2(80);
890 l_print_option BOOLEAN;
891 l_copies NUMBER(15);
892 l_printer_name VARCHAR2(240);
893 l_request_id NUMBER(15);
894
895
896 CURSOR get_print_options_c(c_request_id NUMBER) is
897 SELECT printer,
898 number_of_copies
899 FROM fnd_concurrent_requests
900 WHERE request_id = c_request_id ;
901
902 BEGIN
903 l_module_name := g_module_name || 'submit_224_report';
904 p_error_code := g_SUCCESS;
905 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
906 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'submitting 224 reports .....') ;
907 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_set_of_books_id='||p_set_of_books_id) ;
908 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_gl_period='||p_gl_period) ;
909 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_alc='||p_alc) ;
910 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_partial_or_full='||p_partial_or_full) ;
911 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_business_activity='||p_business_activity) ;
912 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_gwa_reporter_category='||p_gwa_reporter_category) ;
913 END IF;
914
915 l_request_id := fnd_global.conc_request_id;
916
917 BEGIN
918 OPEN get_print_options_c(l_request_id);
919 FETCH get_print_options_c
920 INTO l_printer_name,
921 l_copies;
922 CLOSE get_print_options_c;
923 EXCEPTION
924 WHEN OTHERS THEN
925 p_error_code := g_FAILURE;
926 p_error_desc := SQLERRM;
927 l_location := l_module_name||'cursor_get_print_options_c';
928 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
929 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
930 END;
931
932 IF (p_error_code = g_SUCCESS) THEN
933 l_print_option := fnd_request.set_print_options
934 (
935 printer => l_printer_name,
936 copies => l_copies
937 );
938
939 l_req_id1 := fnd_request.submit_request
940 (
941 application => 'FV',
942 program => 'FVSF224R',
943 description => NULL,
944 start_time => NULL,
945 sub_request => FALSE,
946 argument1 => p_gl_period,
947 argument2 => p_set_of_books_id,
948 argument3 => p_alc,
949 argument4 => p_partial_or_full,
950 argument5 => p_business_activity,
951 argument6 => p_gwa_reporter_category
952
953 );
954 IF (l_req_id1 = 0) THEN
955 p_error_code := g_FAILURE;
956 p_error_desc := 'ERROR SUBMITTING 224 REPORT';
957 l_location := 'submit_FVSF224R';
958 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
959 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
960 ELSE
961 COMMIT;
962 END IF;
963 END IF;
964
965
966 IF (p_error_code = g_SUCCESS) THEN
967 l_print_option := fnd_request.set_print_options
968 (
969 printer => l_printer_name,
970 copies => l_copies
971 );
972
973 l_req_id2 := fnd_request.submit_request
974 (
975 application => 'FV',
976 program => 'FVSF224L',
977 description => NULL,
978 start_time => NULL,
979 sub_request => FALSE,
980 argument1 => p_gl_period,
981 argument2 => p_set_of_books_id,
982 argument3 => p_run_mode,
983 argument4 => p_partial_or_full,
984 argument5 => p_alc
985 );
986 IF (l_req_id2 = 0) THEN
987 p_error_code := g_FAILURE;
988 p_error_desc := 'ERROR SUBMITTING 224 LISTING';
989 l_location := 'submit_FVSF224L';
990 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
991 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
992 ELSE
993 COMMIT;
994 END IF;
995 END IF;
996
997 IF (p_error_code = g_SUCCESS) THEN
998 l_print_option := fnd_request.set_print_options
999 (
1000 printer => l_printer_name,
1001 copies => l_copies
1002 );
1003
1004 l_req_id3 := fnd_request.submit_request
1005 (
1006 application => 'FV',
1007 program => 'FV224EXR',
1008 description => NULL,
1009 start_time => NULL,
1010 sub_request => FALSE,
1011 argument1 => p_gl_period,
1012 argument2 => p_set_of_books_id,
1013 argument3 => p_partial_or_full,
1014 argument4 => p_alc
1015 );
1016 IF (l_req_id3 = 0) THEN
1017 p_error_code := g_FAILURE;
1018 p_error_desc := 'ERROR SUBMITTING 224 EXCEPTION REPORT';
1019 l_location := 'submit_FV224EXR';
1020 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1021 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1022 ELSE
1023 COMMIT;
1024 END IF;
1025 END IF;
1026
1027 IF (p_error_code = g_SUCCESS) THEN
1028 l_print_option := fnd_request.set_print_options
1029 (
1030 printer => l_printer_name,
1031 copies => l_copies
1032 );
1033
1034 l_req_id4 := fnd_request.submit_request
1035 (
1036 application => 'FV',
1037 program => 'FVTI224P',
1038 description => NULL,
1039 start_time => NULL,
1040 sub_request => FALSE,
1041 argument1 => p_set_of_books_id,
1042 argument2 => p_gl_period,
1043 argument3 => p_alc,
1044 argument4 => p_partial_or_full,
1045 argument5 => p_business_activity,
1046 argument6 => p_gwa_reporter_category
1047 );
1048 IF (l_req_id4 = 0) THEN
1049 p_error_code := g_FAILURE;
1050 p_error_desc := 'ERROR SUBMITTING GOALS 224 PROCESS';
1051 l_location := 'submit_FVTI224P';
1052 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1053 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1054 ELSE
1055 COMMIT;
1056 END IF;
1057 END IF;
1058
1059 IF (p_error_code = g_SUCCESS) THEN
1060 l_call_status := fnd_concurrent.wait_for_request
1061 (
1062 request_id => l_req_id1,
1063 interval => 20,
1064 max_wait => 0,
1065 phase => l_phase,
1066 status => l_status,
1067 dev_phase => l_dev_phase,
1068 dev_status => l_dev_status,
1069 message => l_message
1070 );
1071 IF (l_call_status = FALSE) THEN
1072 p_error_code := g_FAILURE;
1073 p_error_desc := 'Cannot wait for the status of 224 Report';
1074 l_location := 'wait_req1';
1075 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1076 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1077 END IF;
1078 END IF;
1079
1080 IF (p_error_code = g_SUCCESS) THEN
1081 l_call_status := fnd_concurrent.wait_for_request
1082 (
1083 request_id => l_req_id2,
1084 interval => 20,
1085 max_wait => 0,
1086 phase => l_phase,
1087 status => l_status,
1088 dev_phase => l_dev_phase,
1089 dev_status => l_dev_status,
1090 message => l_message
1091 );
1092 IF (l_call_status = FALSE) THEN
1093 p_error_code := g_FAILURE;
1094 p_error_desc := 'Cannot wait for the status of 224 Detail Report';
1095 l_location := 'wait_req2';
1096 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1097 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1098 END IF;
1099 END IF;
1100
1101 IF (p_error_code = g_SUCCESS) THEN
1102 l_call_status := fnd_concurrent.wait_for_request
1103 (
1104 request_id => l_req_id3,
1105 interval => 20,
1106 max_wait => 0,
1107 phase => l_phase,
1108 status => l_status,
1109 dev_phase => l_dev_phase,
1110 dev_status => l_dev_status,
1111 message => l_message
1112 );
1113 IF (l_call_status = FALSE) THEN
1114 p_error_code := g_FAILURE;
1115 p_error_desc := 'Cannot wait for the status of 224 Exception Report';
1116 l_location := 'wait_req3';
1117 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1118 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1119 END IF;
1120 END IF;
1121
1122 IF (p_error_code = g_SUCCESS) THEN
1123 l_call_status := fnd_concurrent.wait_for_request
1124 (
1125 request_id => l_req_id4,
1126 interval => 20,
1127 max_wait => 0,
1128 phase => l_phase,
1129 status => l_status,
1130 dev_phase => l_dev_phase,
1131 dev_status => l_dev_status,
1132 message => l_message
1133 );
1134 IF (l_call_status = FALSE) THEN
1135 p_error_code := g_FAILURE;
1136 p_error_desc := 'Cannot wait for the status of GOALS 224 process';
1137 l_location := 'wait_req4';
1138 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1139 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1140 END IF;
1141 END IF;
1142
1143 IF (p_error_code = g_SUCCESS) THEN
1144 l_print_option := fnd_request.set_print_options
1145 (
1146 printer => l_printer_name,
1147 copies => l_copies
1148 );
1149
1150 l_req_id5 := fnd_request.submit_request
1151 (
1152 application => 'FV',
1153 program => 'FVTI224R',
1154 description => NULL,
1155 start_time => NULL,
1156 sub_request => FALSE,
1157 argument1 => 'FVTI224R'
1158 );
1159 IF (l_req_id5 = 0) THEN
1160 p_error_code := g_FAILURE;
1161 p_error_desc := 'ERROR SUBMITTING GOALS 224 REPORT';
1162 l_location := 'submit_FVTI224R';
1163 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1164 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1165 ELSE
1166 COMMIT;
1167 END IF;
1168 END IF;
1169
1170 IF (p_error_code = g_SUCCESS) THEN
1171 l_call_status := fnd_concurrent.wait_for_request
1172 (
1173 request_id => l_req_id5,
1174 interval => 20,
1175 max_wait => 0,
1176 phase => l_phase,
1177 status => l_status,
1178 dev_phase => l_dev_phase,
1179 dev_status => l_dev_status,
1180 message => l_message
1181 );
1182 IF (l_call_status = FALSE) THEN
1183 p_error_code := g_FAILURE;
1184 p_error_desc := 'Cannot wait for the status of GOALS 224 report';
1185 l_location := 'wait_req5';
1186 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1187 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1188 END IF;
1189 END IF;
1190 EXCEPTION
1191 WHEN OTHERS THEN
1192 p_error_code := g_FAILURE;
1193 p_error_desc := SQLERRM;
1194 l_location := l_module_name||'.final_exception';
1195 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1196 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1197
1198 END submit_224_report;
1199
1200 --------------------------------------------------------------------
1201 ----- CALL_EXTRACT_PROCESS
1202 --------------------------------------------------------------------
1203
1204 PROCEDURE call_extract_process
1205 (
1206 p_set_of_books_id IN NUMBER,
1207 p_error_code OUT NOCOPY NUMBER,
1208 p_error_desc OUT NOCOPY VARCHAR2
1209 )
1210 IS
1211 l_module_name VARCHAR2(200) ;
1212 l_location VARCHAR2(200);
1213
1214 l_req_id NUMBER;
1215 l_call_status boolean;
1216 l_dev_phase VARCHAR2(80);
1217 l_phase VARCHAR2(80);
1218 l_status VARCHAR2(80);
1219 l_dev_status VARCHAR2(80);
1220 l_message VARCHAR2(80);
1221
1222 BEGIN
1223 l_module_name := g_module_name || 'call_extract_process';
1224 p_error_code := g_SUCCESS;
1225 p_def_org_id := mo_global.get_current_org_id;
1226 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1227 fv_utility.debug_mesg(fnd_log.level_statement,l_module_name, 'In call_extact process');
1228 fv_utility.debug_mesg(fnd_log.level_statement,l_module_name, 'p_set_of_books_id='||p_set_of_books_id) ;
1229 fv_utility.debug_mesg(fnd_log.level_statement,l_module_name, 'org_id='||p_def_org_id) ;
1230 END IF;
1231
1232 l_req_id := fnd_request.submit_request
1233 (
1234 application => 'FV',
1235 program => 'FVSF224E',
1236 description => NULL,
1237 start_time => NULL,
1238 sub_request => FALSE,
1239 argument1 => p_set_of_books_id
1240 );
1241 IF (l_req_id = 0) THEN
1242 p_error_code := g_FAILURE;
1243 p_error_desc := 'ERROR SUBMITTING 224 EXTRACT PROCESS';
1244 l_location := 'submit_FVSF224E';
1245 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1246 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1247 ELSE
1248 COMMIT;
1249 END IF;
1250
1251 IF (p_error_code = g_SUCCESS) THEN
1252 l_call_status := fnd_concurrent.wait_for_request
1253 (
1254 request_id => l_req_id,
1255 interval => 20,
1256 max_wait => 0,
1257 phase => l_phase,
1258 status => l_status,
1259 dev_phase => l_dev_phase,
1260 dev_status => l_dev_status,
1261 message => l_message
1262 );
1263 IF (l_call_status = FALSE) THEN
1264 p_error_code := g_FAILURE;
1265 p_error_desc := 'Cannot wait for the status of 224 Extract Process';
1266 l_location := 'wait_req1';
1267 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1268 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1269 END IF;
1270 END IF;
1271
1272 EXCEPTION
1273 WHEN OTHERS THEN
1274 p_error_code := g_FAILURE;
1275 p_error_desc := SQLERRM;
1276 l_location := l_module_name||'.final_exception';
1277 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1278 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1279
1280 END call_extract_process;
1281
1282
1283 ----------------------------------------------------------------------
1284 -- MAIN
1285 ----------------------------------------------------------------------
1286 PROCEDURE main
1287 (
1288 p_errbuf OUT NOCOPY VARCHAR2,
1289 p_retcode OUT NOCOPY NUMBER,
1290 p_set_of_books_id IN NUMBER,
1291 p_gl_period IN VARCHAR2,
1292 p_run_mode IN VARCHAR2,
1293 p_partial_or_full IN VARCHAR2,
1294 p_business_activity IN VARCHAR2,
1295 p_gwa_reporter_category IN VARCHAR2,
1296 p_alc IN VARCHAR2
1297 )
1298 IS
1299 l_module_name VARCHAR2(200);
1300 l_location VARCHAR2(200);
1301 l_end_period_date DATE;
1302 BEGIN
1303
1304 l_module_name := g_module_name || 'main';
1305 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1306 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START OF THE 224 MAIN PROCESS.....');
1307 END IF;
1308
1309 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1310 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'INITIALIZING...');
1311 END IF;
1312 p_retcode := g_SUCCESS;
1313
1314 update_flag := p_run_mode;
1315 sob := p_set_of_books_id;
1316 p_def_org_id := mo_global.get_current_org_id;
1317 g_partial_or_full := p_partial_or_full;
1318 g_business_activity := p_business_activity;
1319 g_gwa_reporter_category := p_gwa_reporter_category;
1320
1321 IF (p_retcode = g_SUCCESS) THEN
1322 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level ) THEN
1323 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'calling call_extract_process procedure and run with org_id .' || p_def_org_id);
1324 END IF;
1325
1326 call_extract_process
1327 (
1328 p_set_of_books_id => p_set_of_books_id,
1329 p_error_code => p_retcode,
1330 p_error_desc => p_errbuf
1331 );
1332 END IF;
1333
1334 IF (p_retcode = g_SUCCESS) THEN
1335 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level ) THEN
1336 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'calling post_process_for_main procedure.....');
1337 END IF;
1338 post_process_for_main
1339 (
1340 p_set_of_books_id => p_set_of_books_id,
1341 p_gl_period => p_gl_period,
1342 p_alc => p_alc,
1343 p_run_mode => p_run_mode,
1344 p_partial_or_full => p_partial_or_full,
1345 p_business_activity => p_business_activity,
1346 p_gwa_reporter_category => p_gwa_reporter_category,
1347 p_end_period_date => l_end_period_date,
1348 p_error_code => p_retcode,
1349 p_error_desc => p_errbuf
1350 );
1351 END IF;
1352
1353 IF (p_retcode = g_SUCCESS) THEN
1354 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level ) THEN
1355 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'calling submit_224_report procedure.....');
1356 END IF;
1357 submit_224_report
1358 (
1359 p_set_of_books_id => p_set_of_books_id,
1360 p_gl_period => p_gl_period,
1361 p_alc => p_alc,
1362 p_run_mode => p_run_mode,
1363 p_partial_or_full => p_partial_or_full,
1364 p_business_activity => p_business_activity,
1365 p_gwa_reporter_category => p_gwa_reporter_category,
1366 p_error_code => p_retcode,
1367 p_error_desc => p_errbuf
1368 );
1369 END IF;
1370
1371 IF (p_retcode = g_SUCCESS) THEN
1372 IF (update_flag = 'F') THEN
1373 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level ) THEN
1374 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'calling update_audit_info procedure.....');
1375 END IF;
1376 update_audit_info
1377 (
1378 p_set_of_books_id => p_set_of_books_id,
1379 p_alc => p_alc,
1380 p_end_period_date => l_end_period_date,
1381 p_error_code => p_retcode,
1382 p_error_desc => p_errbuf
1383 );
1384 END IF;
1385 END IF;
1386
1387 IF (p_retcode = g_SUCCESS) THEN
1388 IF (p_partial_or_full='Full') THEN
1389 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1390 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CALLING CHENCK ALC ADDRESS ROCEDURE.....');
1391 END IF;
1392 check_alc_address (p_alc) ;
1393 END IF;
1394 END IF;
1395 EXCEPTION
1396 WHEN OTHERS THEN
1397 p_retcode := g_FAILURE;
1398 p_errbuf := SQLERRM;
1399 l_location := l_module_name||'.final_exception';
1400 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1401 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
1402
1403 END main;
1404
1405 ----------------------------------------------------------------------
1406 -- CHECK_REPORT_DEFINTIONS
1407 ----------------------------------------------------------------------
1408 PROCEDURE check_report_definitions
1409 (
1410 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE,
1411 p_error_code OUT NOCOPY NUMBER,
1412 p_error_desc OUT NOCOPY VARCHAR2
1413 )
1414 IS
1415 l_module_name VARCHAR2(200) ;
1416 l_cnt NUMBER;
1417 BEGIN
1418 l_module_name := g_module_name || 'check_report_definitions';
1419 p_error_code := g_SUCCESS;
1420 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1421 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'In ' || l_module_name);
1422 End if;
1423
1424 SELECT COUNT(*)
1425 INTO l_cnt
1426 FROM fv_report_definitions
1427 WHERE set_of_books_id = p_set_of_books_id
1428 AND agency_location_code IS NULL
1429 AND d_r_flag IN ('D','R');
1430
1431 IF l_cnt > 0 THEN
1432 p_error_code := g_FAILURE;
1433 p_error_desc := 'The agency location code needs to be updated '||
1434 'for the Disbursement and Receipt records '||
1435 'in the 224 and Fund Balance with Treasury Form, '||
1436 'before running the 224 Process';
1437 fv_utility.log_mesg(fnd_log.level_error, l_module_name||'.error1', p_error_desc) ;
1438 RETURN;
1439 END IF;
1440 EXCEPTION
1441 WHEN OTHERS THEN
1442 p_error_code := g_FAILURE;
1443 p_error_desc := SQLERRM || ' -- Error in Check_Report_Defintions procedure.';
1444 fv_utility.log_mesg(fnd_log.level_unexpected, l_module_name||'.final_exception', p_error_desc) ;
1445 RETURN;
1446 END Check_Report_Definitions;
1447
1448 ----------------------------------------------------------------------
1449 -- GET_RUN_DATE
1450 ----------------------------------------------------------------------
1451 PROCEDURE get_run_date
1452 (
1453 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE,
1454 p_previous_run_date OUT NOCOPY fv_sf224_run.last_run_date%TYPE,
1455 p_error_code OUT NOCOPY NUMBER,
1456 p_error_desc OUT NOCOPY VARCHAR2
1457 )
1458 IS
1459 l_module_name VARCHAR2(200);
1460 l_location VARCHAR2(400);
1461 BEGIN
1462 l_module_name := g_module_name || 'get_previous_run_date';
1463 p_error_code := g_SUCCESS;
1464
1465 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1466 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_set_of_books_id = '||p_set_of_books_id);
1467 END IF;
1468
1469 BEGIN
1470 SELECT last_run_date
1471 INTO p_previous_run_date
1472 FROM fv_sf224_run fsr
1473 WHERE fsr.set_of_books_id = p_set_of_books_id;
1474 EXCEPTION
1475 WHEN NO_DATA_FOUND THEN
1476 /* Commented out to get the old data
1477 SELECT min(start_date)
1478 INTO g_lo_date
1479 FROM gl_period_statuses
1480 WHERE ledger_id = sob
1481 AND application_id = 101
1482 AND period_year = to_char(sysdate, 'YYYY');
1483 */
1484
1485 p_previous_run_date := g_lo_date;
1486
1487 WHEN OTHERS THEN
1488 p_error_code := g_FAILURE;
1489 p_error_desc := SQLERRM;
1490 l_location := l_module_name||'select_fv_sf224_run';
1491 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1492 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1493 END;
1494
1495 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1496 fv_utility.debug_mesg(fnd_log.level_statement,l_module_name,'Last 224 run_date ='||p_previous_run_date);
1497 END IF;
1498
1499 EXCEPTION
1500 WHEN OTHERS THEN
1501 p_error_code := g_FAILURE;
1502 p_error_desc := SQLERRM;
1503 l_location := l_module_name||'.final_exception';
1504 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1505 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1506 END get_run_date;
1507
1508 ----------------------------------------------------------------------
1509 -- SET_RUN_DATE
1510 ----------------------------------------------------------------------
1511 PROCEDURE set_run_date
1512 (
1513 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE,
1514 p_error_code OUT NOCOPY NUMBER,
1515 p_error_desc OUT NOCOPY VARCHAR2
1516 )
1517 IS
1518 l_module_name VARCHAR2(200);
1519 l_location VARCHAR2(400);
1520 l_insert_required BOOLEAN;
1521 BEGIN
1522 l_module_name := g_module_name || 'set_run_date';
1523 p_error_code := g_SUCCESS;
1524
1525 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1526 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_set_of_books_id = '||p_set_of_books_id);
1527 END IF;
1528
1529 BEGIN
1530 UPDATE fv_sf224_run
1531 SET last_run_date = g_SYSDATE
1532 WHERE set_of_books_id = p_set_of_books_id;
1533 IF (SQL%ROWCOUNT = 0) THEN
1534 l_insert_required := TRUE;
1535 ELSE
1536 l_insert_required := FALSE;
1537 END IF;
1538 EXCEPTION
1539 WHEN OTHERS THEN
1540 p_error_code := g_FAILURE;
1541 p_error_desc := SQLERRM;
1542 l_location := l_module_name||'update_fv_sf224_run';
1543 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1544 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1545 END;
1546 IF (p_error_code = g_SUCCESS AND l_insert_required) THEN
1547 BEGIN
1548 INSERT INTO fv_sf224_run
1549 (
1550 set_of_books_id,
1551 last_run_date
1552 )
1553 VALUES
1554 (
1555 p_set_of_books_id,
1556 g_SYSDATE
1557 );
1558 EXCEPTION
1559 WHEN OTHERS THEN
1560 p_error_code := g_FAILURE;
1561 p_error_desc := SQLERRM;
1562 l_location := l_module_name||'insert_fv_sf224_run';
1563 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1564 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1565 END;
1566 END IF;
1567
1568 EXCEPTION
1569 WHEN OTHERS THEN
1570 p_error_code := g_FAILURE;
1571 p_error_desc := SQLERRM;
1572 l_location := l_module_name||'.final_exception';
1573 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1574 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1575 END set_run_date;
1576
1577
1578 ----------------------------------------------------------------------
1579 -- INITIALIZE_PROGRAM_VARIABLES
1580 ----------------------------------------------------------------------
1581 PROCEDURE initialize_program_variables
1582 (
1583 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE,
1584 p_currency_code OUT NOCOPY gl_sets_of_books.currency_code%TYPE,
1585 p_chart_of_accounts_id OUT NOCOPY gl_sets_of_books.chart_of_accounts_id%TYPE,
1586 p_acct_segment OUT NOCOPY fnd_id_flex_segments.application_column_name%TYPE,
1587 p_acct_value_set_id OUT NOCOPY fnd_id_flex_segments.flex_value_set_id%TYPE,
1588 p_bal_segment OUT NOCOPY fnd_id_flex_segments.application_column_name%TYPE,
1589 p_accomplish_attribute OUT NOCOPY fv_system_parameters.sf224_accomplish_date%TYPE,
1590 p_error_code OUT NOCOPY NUMBER,
1591 p_error_desc OUT NOCOPY VARCHAR2
1592 )
1593 IS
1594 l_module_name VARCHAR2(200);
1595 l_location VARCHAR2(200);
1596 l_application_id NUMBER := 101;
1597 l_id_flex_code VARCHAR2(25) := 'GL#';
1598 l_acct_seg_attr_type VARCHAR2(30) := 'GL_ACCOUNT';
1599 l_bal_seg_attr_type VARCHAR2(30) := 'GL_BALANCING';
1600 l_retval BOOLEAN;
1601 l_period_set_name gl_period_sets.period_set_name%TYPE;
1602
1603 BEGIN
1604 l_module_name := g_module_name || 'initialize_program_variables';
1605 p_error_code := g_SUCCESS;
1606
1607 p_chart_of_accounts_id := NULL;
1608 p_acct_segment := NULL;
1609 p_acct_value_set_id := NULL;
1610
1611 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1612 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_set_of_books_id = '||p_set_of_books_id);
1613 END IF;
1614
1615 IF (p_error_code = g_SUCCESS) THEN
1616 BEGIN
1617 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1618 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from gl_sets_of_books');
1619 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'g_set_of_books_id='||p_set_of_books_id);
1620 END IF;
1621 SELECT gsob.chart_of_accounts_id,
1622 gsob.currency_code,
1623 gsob.period_set_name
1624 INTO p_chart_of_accounts_id,
1625 p_currency_code,
1626 l_period_set_name
1627 FROM gl_sets_of_books gsob
1628 WHERE set_of_books_id = p_set_of_books_id;
1629 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1630 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id='||p_chart_of_accounts_id);
1631 END IF;
1632 EXCEPTION
1633 WHEN OTHERS THEN
1634 p_error_code := g_FAILURE;
1635 p_error_desc := SQLERRM;
1636 l_location := l_module_name||'select_gl_sets_of_books';
1637 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1638 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1639 END;
1640 END IF;
1641
1642 IF (p_error_code = g_SUCCESS) THEN
1643 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1644 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling fnd_flex_apis.get_segment_column');
1645 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_application_id ='||l_application_id);
1646 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_id_flex_code ='||l_id_flex_code);
1647 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id ='||p_chart_of_accounts_id);
1648 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_acct_seg_attr_type ='||l_acct_seg_attr_type);
1649 END IF;
1650 l_retval := fnd_flex_apis.get_segment_column
1651 (
1652 x_application_id => l_application_id,
1653 x_id_flex_code => l_id_flex_code,
1654 x_id_flex_num => p_chart_of_accounts_id,
1655 x_seg_attr_type => l_acct_seg_attr_type,
1656 x_app_column_name => p_acct_segment
1657 );
1658 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1659 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_acct_segment ='||p_acct_segment);
1660 END IF;
1661 IF (NOT l_retval) THEN
1662 p_error_code := g_FAILURE;
1663 p_error_desc := fnd_message.get;
1664 l_location := l_module_name||'call_fnd_flex_apis.get_segment_column';
1665 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1666 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1667 END IF;
1668 END IF;
1669
1670 IF (p_error_code = g_SUCCESS) THEN
1671 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1672 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling fnd_flex_apis.get_segment_column');
1673 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_application_id ='||l_application_id);
1674 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_id_flex_code ='||l_id_flex_code);
1675 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id ='||p_chart_of_accounts_id);
1676 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_acct_seg_attr_type ='||l_bal_seg_attr_type);
1677 END IF;
1678 l_retval := fnd_flex_apis.get_segment_column
1679 (
1680 x_application_id => l_application_id,
1681 x_id_flex_code => l_id_flex_code,
1682 x_id_flex_num => p_chart_of_accounts_id,
1683 x_seg_attr_type => l_bal_seg_attr_type,
1684 x_app_column_name => p_bal_segment
1685 );
1686 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1687 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_acct_segment ='||p_acct_segment);
1688 END IF;
1689 IF (NOT l_retval) THEN
1690 p_error_code := g_FAILURE;
1691 p_error_desc := fnd_message.get;
1692 l_location := l_module_name||'call_fnd_flex_apis.get_segment_column';
1693 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1694 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1695 END IF;
1696 END IF;
1697
1698 IF (p_error_code = g_SUCCESS) THEN
1699 BEGIN
1700 SELECT flex_value_set_id
1701 INTO p_acct_value_set_id
1702 FROM fnd_id_flex_segments
1703 WHERE application_column_name = p_acct_segment
1704 AND application_id = l_application_id
1705 AND id_flex_code = l_id_flex_code
1706 AND id_flex_num = p_chart_of_accounts_id
1707 AND enabled_flag = 'Y';
1708 EXCEPTION
1709 WHEN OTHERS THEN
1710 p_error_code := g_FAILURE;
1711 p_error_desc := SQLERRM;
1712 l_location := l_module_name||'select_fnd_id_flex_segments';
1713 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1714 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1715 END;
1716 END IF;
1717
1718 IF (p_error_code = g_SUCCESS) THEN
1719 BEGIN
1720 SELECT sf224_accomplish_date
1721 INTO p_accomplish_attribute
1722 FROM fv_system_parameters;
1723 EXCEPTION
1724 WHEN OTHERS THEN
1725 p_error_code := g_FAILURE;
1726 p_error_desc := SQLERRM;
1727 l_location := l_module_name||'select_fv_system_parameters';
1728 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1729 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1730 END;
1731 END IF;
1732
1733 EXCEPTION
1734 WHEN OTHERS THEN
1735 p_error_code := g_FAILURE;
1736 p_error_desc := SQLERRM;
1737 l_location := l_module_name||'.final_exception';
1738 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1739 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1740 END initialize_program_variables;
1741
1742 ----------------------------------------------------------------------
1743 -- INSERT_SF224_BATCHES
1744 ----------------------------------------------------------------------
1745 PROCEDURE insert_sf224_batches
1746 (
1747 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE,
1748 p_previous_run_date IN fv_sf224_run.last_run_date%TYPE,
1749 p_current_run_date IN fv_sf224_run.last_run_date%TYPE,
1750 p_currency_code IN gl_sets_of_books.currency_code%TYPE,
1751 p_acct_segment IN fnd_id_flex_segments.application_column_name%TYPE,
1752 p_bal_segment IN fnd_id_flex_segments.application_column_name%TYPE,
1753 p_error_code OUT NOCOPY NUMBER,
1754 p_error_desc OUT NOCOPY VARCHAR2
1755 )
1756 IS
1757 l_module_name VARCHAR2(200) ;
1758 l_string varchar2(10000);
1759 l_string1 varchar2(10000);
1760 l_string2 varchar2(10000);
1761 l_string3 varchar2(1000);
1762
1763 l_cur number;
1764 l_row number;
1765
1766 BEGIN
1767 p_error_code := g_SUCCESS;
1768 l_module_name := g_module_name || 'insert_sf224_batches';
1769
1770 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1771 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'in insert_sf224_batches procedure, before the insert.');
1772 END IF;
1773 l_string1 := 'INSERT INTO FV_SF224_TEMP
1774 (
1775 je_batch_id,
1776 fund_code,
1777 fund_description,
1778 sf224_type_code,
1779 name,
1780 set_of_books_id,
1781 posted_date,
1782 amount,
1783 actual_amount,
1784 d_r_flag,
1785 reference_1,
1786 reference_2,
1787 reference_3,
1788 reference_4,
1789 reference_5,
1790 reference_6,
1791 reference_8,
1792 reference_9,
1793 je_line_num,
1794 je_header_id,
1795 gl_period,
1796 default_period_name,
1797 external_reference,
1798 treasury_symbol,
1799 treasury_symbol_id,
1800 record_category,
1801 federal_rpt_id,
1802 sf224_processed_flag,
1803 account,
1804 exception_section,
1805 gl_date,
1806 created_by,
1807 creation_date,
1808 last_updated_by,
1809 last_update_date,
1810 last_update_login,
1811 created_request_id,
1812 JE_SOURCE,
1813 JE_CATEGORY,
1814 JE_FROM_SLA_FLAG
1815 )';
1816
1817 l_string3 := ' AND not exists (select batch_id
1818 from fv_sf224_audits fvs
1819 where fvs.batch_id = glb.je_batch_id
1820 and fvs.je_header_id = gll.je_header_id
1821 and fvs.je_line_num = gll.je_line_num)';
1822
1823 /* Start for non-sla, upgraded 11i data */
1824 l_string2 :=
1825 'SELECT glb.je_batch_id,
1826 ffp.fund_value,
1827 ffp.description,
1828 fts.sf224_type_code,
1829 glb.name,
1830 decode(NVL(glh.je_from_sla_flag, ''N''),''U'', glb.SET_OF_BOOKS_ID_11I, ''N'', gll.ledger_id),
1831 glb.posted_date,
1832 nvl(gll.Entered_dr,0) - nvl(gll.Entered_cr,0),
1833 nvl(gll.Entered_dr,0) - nvl(gll.Entered_cr,0),
1834 frd.d_r_flag,
1835 LTRIM(RTRIM(gll.reference_1)),
1836 LTRIM(RTRIM(gll.reference_2)),
1837 LTRIM(RTRIM(gll.reference_3)),
1838 LTRIM(RTRIM(gll.reference_4)),
1839 LTRIM(RTRIM(gll.reference_5)),
1840 LTRIM(RTRIM(gll.reference_6)),
1841 LTRIM(RTRIM(gll.reference_8)),
1842 LTRIM(RTRIM(gll.reference_9)),
1843 gll.je_line_num,
1844 gll.je_header_id,
1845 gll.period_name,
1846 glb.default_period_name,
1847 glh.external_reference,
1848 fts.treasury_symbol,
1849 ffp.treasury_symbol_id,
1850 ''GLRECORD'',
1851 frd.federal_rpt_id,
1852 ''N'',
1853 frd.account,
1854 NULL,
1855 gll.effective_date,
1856 :b_user_id,
1857 :b_sysdate,
1858 :b_user_id,
1859 :b_sysdate,
1860 :b_login_id,
1861 :b_request_id,
1862 glh.je_source,
1863 glh.je_category,
1864 glh.je_from_sla_flag
1865 FROM gl_je_batches glb,
1866 gl_je_headers glh,
1867 gl_je_lines gll,
1868 gl_code_combinations gcc,
1869 fv_report_definitions frd,
1870 fv_fund_parameters ffp,
1871 fv_treasury_symbols fts
1872 WHERE glb.status = ''P''
1873 AND glb.actual_flag = ''A''
1874 AND glb.je_batch_id = glh.je_batch_id
1875 AND glh.je_header_id = gll.je_header_id
1876 AND gll.code_combination_id = gcc.code_combination_id
1877 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
1878 AND gll.ledger_id = :b_sob
1879 AND frd.set_of_books_id = :b_sob
1880 AND ffp.set_of_books_id = :b_sob
1881 AND gcc.'||p_bal_segment||' = ffp.fund_value
1882 AND glh.currency_code = :b_g_currency_code
1883 AND glh.posted_date >= :posted_from_date
1884 AND glh.posted_date <= :posted_to_date
1885 AND gcc.'||p_acct_segment||' = frd.account
1886 AND frd.d_r_flag in (''D'',''R'')
1887 AND NVL(glh.je_from_sla_flag, ''N'') IN (''N'', ''U'')';
1888
1889 --IF (p_previous_run_date = g_lo_date) THEN
1890 l_string := l_string1 || l_string2 || l_string3 ;
1891 ---ELSE
1892 --l_string := l_string1 || l_string2;
1893 --END IF;
1894
1895 l_cur:= dbms_sql.open_cursor;
1896 dbms_sql.parse(l_cur, l_string, DBMS_SQL.V7);
1897 dbms_sql.bind_variable(l_cur,':b_sob',p_set_of_books_id);
1898 dbms_sql.bind_variable(l_cur,':b_g_currency_code',p_currency_code);
1899 dbms_sql.bind_variable(l_cur,':posted_from_date',p_previous_run_date);
1900 dbms_sql.bind_variable(l_cur,':posted_to_date',p_current_run_date);
1901 dbms_sql.bind_variable(l_cur,':b_user_id',g_user_id);
1902 dbms_sql.bind_variable(l_cur,':b_sysdate',g_sysdate);
1903 dbms_sql.bind_variable(l_cur,':b_login_id',g_login_id);
1904 dbms_sql.bind_variable(l_cur,':b_request_id',g_request_id);
1905 l_row := dbms_sql.EXECUTE(l_cur);
1906 dbms_sql.close_cursor(l_cur);
1907 /* END for non-sla, upgraded 11i data */
1908 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert for upgraded 11i data ');
1909 /* Start for je_source is payables and je_category is non treasury */
1910
1911 l_string2 := ' SELECT glb.je_batch_id,
1912 ffp.fund_value,
1913 ffp.description,
1914 fts.sf224_type_code,
1915 glb.name,
1916 gll.ledger_id,
1917 glb.posted_date,
1918 nvl(xal.accounted_dr,0) - nvl(xal.accounted_cr,0),
1919 nvl(xal.accounted_dr,0) - nvl(xal.accounted_cr,0),
1920 frd.d_r_flag,
1921 NULL, --LTRIM(RTRIM(gll.reference_1))
1922 LTRIM(RTRIM(aid.invoice_id)), --Invoice_id LTRIM(RTRIM(gll.reference_2))
1923 LTRIM(RTRIM(aip.check_id)), --Check_id LTRIM(RTRIM(gll.reference_3)),
1924 NULL, --LTRIM(RTRIM(gll.reference_4)),
1925 NULL, --LTRIM(RTRIM(gll.reference_5)),
1926 NULL, --LTRIM(RTRIM(gll.reference_6)),
1927 LTRIM(RTRIM(aid.distribution_line_number)), -- invoice_distributin_line_number LTRIM(RTRIM(gll.reference_8)),
1928 LTRIM(RTRIM(aip.invoice_payment_id)), -- invoice_payment_id LTRIM(RTRIM(gll.reference_9)),
1929 gll.je_line_num,
1930 gll.je_header_id,
1931 gll.period_name,
1932 glb.default_period_name,
1933 glh.external_reference,
1934 fts.treasury_symbol,
1935 ffp.treasury_symbol_id,
1936 ''GLRECORD'',
1937 frd.federal_rpt_id,
1938 ''N'',
1939 frd.account,
1940 NULL,
1941 gll.effective_date,
1942 :b_user_id,
1943 :b_sysdate,
1944 :b_user_id,
1945 :b_sysdate,
1946 :b_login_id,
1947 :b_request_id,
1948 glh.je_source,
1949 glh.je_category,
1950 glh.je_from_sla_flag
1951 FROM gl_je_batches glb,
1952 gl_je_headers glh,
1953 gl_je_lines gll,
1954 gl_code_combinations gcc,
1955 fv_report_definitions frd,
1956 fv_fund_parameters ffp,
1957 fv_treasury_symbols fts,
1958 ap_invoices_all ai,
1959 ap_invoice_distributions_all aid,
1960 ap_invoice_payments_all aip,
1961 ap_payment_hist_dists aphd,
1962 gl_import_references glir,
1963 xla_ae_headers xah,
1964 xla_ae_lines xal,
1965 xla_events xet,
1966 xla_distribution_links xdl
1967 WHERE glb.status = ''P''
1968 AND glb.actual_flag = ''A''
1969 AND glb.je_batch_id = glh.je_batch_id
1970 AND glh.je_header_id = gll.je_header_id
1971 AND gll.code_combination_id = gcc.code_combination_id
1972 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
1973 AND aip.invoice_payment_id = aphd.invoice_payment_id
1974 AND gll.ledger_id = :b_sob
1975 AND frd.set_of_books_id = :b_sob
1976 AND ffp.set_of_books_id = :b_sob
1977 AND gcc.'||p_bal_segment||' = ffp.fund_value
1978 AND glh.currency_code = :b_g_currency_code
1979 AND glh.posted_date >= :posted_from_date
1980 AND glh.posted_date <= :posted_to_date
1981 AND gcc.'||p_acct_segment||' = frd.account
1982 AND frd.d_r_flag in (''D'',''R'')
1983 AND glh.je_source=''Payables''
1984 AND glh.je_category <> ''Treasury Confirmation''
1985 AND glh.je_from_sla_flag = ''Y''
1986 AND ai.invoice_id = aid.invoice_id
1987 AND aip.invoice_id = ai.invoice_id
1988 AND glir.je_header_id = gll.je_header_id
1989 AND glir.je_line_num = gll.je_line_num
1990 AND xal.gl_sl_link_id = glir.gl_sl_link_id
1991 AND xal.gl_sl_link_table = glir.gl_sl_link_table
1992 AND xal.ae_header_id = xah.ae_header_id
1993 AND xet.event_id = xah.event_id
1994 AND xdl.event_id = xet.event_id
1995 AND xdl.ae_header_id = xah.ae_header_id
1996 AND xdl.ae_line_num = xal.ae_line_num
1997 AND xdl.source_distribution_type IN ( ''AP_PMT_DIST'')
1998 AND xdl.source_distribution_id_num_1 = aphd.payment_hist_dist_id
1999 AND aphd.invoice_distribution_id = aid.invoice_distribution_id
2000 AND xdl.application_id = 200 ';
2001
2002 --IF (p_previous_run_date = g_lo_date) THEN
2003 l_string := l_string1 || l_string2 || l_string3 ;
2004 --ELSE
2005 --l_string := l_string1 || l_string2;
2006 --END IF;
2007
2008 l_cur:= dbms_sql.open_cursor;
2009 dbms_sql.parse(l_cur, l_string, DBMS_SQL.V7);
2010 dbms_sql.bind_variable(l_cur,':b_sob',p_set_of_books_id);
2011 dbms_sql.bind_variable(l_cur,':b_g_currency_code',p_currency_code);
2012 dbms_sql.bind_variable(l_cur,':posted_from_date',p_previous_run_date);
2013 dbms_sql.bind_variable(l_cur,':posted_to_date',p_current_run_date);
2014 dbms_sql.bind_variable(l_cur,':b_user_id',g_user_id);
2015 dbms_sql.bind_variable(l_cur,':b_sysdate',g_sysdate);
2016 dbms_sql.bind_variable(l_cur,':b_login_id',g_login_id);
2017 dbms_sql.bind_variable(l_cur,':b_request_id',g_request_id);
2018 l_row := dbms_sql.EXECUTE(l_cur);
2019 dbms_sql.close_cursor(l_cur);
2020 /* END for je_source is payables and je_category is non treasury*/
2021
2022 /* Start for je_source is payables and je_category is non treasury */
2023
2024 l_string2 := ' SELECT glb.je_batch_id,
2025 ffp.fund_value,
2026 ffp.description,
2027 fts.sf224_type_code,
2028 glb.name,
2029 gll.ledger_id,
2030 glb.posted_date,
2031 nvl(xal.accounted_dr,0) - nvl(xal.accounted_cr,0),
2032 nvl(xal.accounted_dr,0) - nvl(xal.accounted_cr,0),
2033 frd.d_r_flag,
2034 NULL, --LTRIM(RTRIM(gll.reference_1))
2035 LTRIM(RTRIM(aid.invoice_id)), --Invoice_id LTRIM(RTRIM(gll.reference_2))
2036 LTRIM(RTRIM(aip.check_id)), --Check_id LTRIM(RTRIM(gll.reference_3)),
2037 NULL, --LTRIM(RTRIM(gll.reference_4)),
2038 NULL, --LTRIM(RTRIM(gll.reference_5)),
2039 NULL, --LTRIM(RTRIM(gll.reference_6)),
2040 LTRIM(RTRIM(aid.distribution_line_number)), -- invoice_distributin_line_number LTRIM(RTRIM(gll.reference_8)),
2041 LTRIM(RTRIM(aip.invoice_payment_id)), -- invoice_payment_id LTRIM(RTRIM(gll.reference_9)),
2042 gll.je_line_num,
2043 gll.je_header_id,
2044 gll.period_name,
2045 glb.default_period_name,
2046 glh.external_reference,
2047 fts.treasury_symbol,
2048 ffp.treasury_symbol_id,
2049 ''GLRECORD'',
2050 frd.federal_rpt_id,
2051 ''N'',
2052 frd.account,
2053 NULL,
2054 gll.effective_date,
2055 :b_user_id,
2056 :b_sysdate,
2057 :b_user_id,
2058 :b_sysdate,
2059 :b_login_id,
2060 :b_request_id,
2061 glh.je_source,
2062 glh.je_category,
2063 glh.je_from_sla_flag
2064 FROM gl_je_batches glb,
2065 gl_je_headers glh,
2066 gl_je_lines gll,
2067 gl_code_combinations gcc,
2068 fv_report_definitions frd,
2069 fv_fund_parameters ffp,
2070 fv_treasury_symbols fts,
2071 ap_invoices_all ai,
2072 ap_invoice_distributions_all aid,
2073 ap_invoice_payments_all aip,
2074 gl_import_references glir,
2075 xla_ae_headers xah,
2076 xla_ae_lines xal,
2077 xla_events xet,
2078 xla_distribution_links xdl
2079 WHERE glb.status = ''P''
2080 AND glb.actual_flag = ''A''
2081 AND glb.je_batch_id = glh.je_batch_id
2082 AND glh.je_header_id = gll.je_header_id
2083 AND gll.code_combination_id = gcc.code_combination_id
2084 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
2085 AND gll.ledger_id = :b_sob
2086 AND frd.set_of_books_id = :b_sob
2087 AND ffp.set_of_books_id = :b_sob
2088 AND gcc.'||p_bal_segment||' = ffp.fund_value
2089 AND glh.currency_code = :b_g_currency_code
2090 AND glh.posted_date >= :posted_from_date
2091 AND glh.posted_date <= :posted_to_date
2092 AND gcc.'||p_acct_segment||' = frd.account
2093 AND frd.d_r_flag in (''D'',''R'')
2094 AND glh.je_source=''Payables''
2095 AND glh.je_category <> ''Treasury Confirmation''
2096 AND glh.je_from_sla_flag = ''Y''
2097 AND ai.invoice_id = aid.invoice_id
2098 AND aip.invoice_id = ai.invoice_id
2099 AND glir.je_header_id = gll.je_header_id
2100 AND glir.je_line_num = gll.je_line_num
2101 AND xal.gl_sl_link_id = glir.gl_sl_link_id
2102 AND xal.gl_sl_link_table = glir.gl_sl_link_table
2103 AND xal.ae_header_id = xah.ae_header_id
2104 AND xet.event_id = xah.event_id
2105 AND xdl.event_id = xet.event_id
2106 AND xdl.ae_header_id = xah.ae_header_id
2107 AND xdl.ae_line_num = xal.ae_line_num
2108 AND xdl.source_distribution_type IN ( ''AP_INV_DIST'', ''AP_PREPAY'')
2109 AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
2110 AND xdl.application_id = 200 ';
2111
2112 --IF (p_previous_run_date = g_lo_date) THEN
2113 l_string := l_string1 || l_string2 || l_string3 ;
2114 --ELSE
2115 --l_string := l_string1 || l_string2;
2116 --END IF;
2117
2118 l_cur:= dbms_sql.open_cursor;
2119 dbms_sql.parse(l_cur, l_string, DBMS_SQL.V7);
2120 dbms_sql.bind_variable(l_cur,':b_sob',p_set_of_books_id);
2121 dbms_sql.bind_variable(l_cur,':b_g_currency_code',p_currency_code);
2122 dbms_sql.bind_variable(l_cur,':posted_from_date',p_previous_run_date);
2123 dbms_sql.bind_variable(l_cur,':posted_to_date',p_current_run_date);
2124 dbms_sql.bind_variable(l_cur,':b_user_id',g_user_id);
2125 dbms_sql.bind_variable(l_cur,':b_sysdate',g_sysdate);
2126 dbms_sql.bind_variable(l_cur,':b_login_id',g_login_id);
2127 dbms_sql.bind_variable(l_cur,':b_request_id',g_request_id);
2128 l_row := dbms_sql.EXECUTE(l_cur);
2129 dbms_sql.close_cursor(l_cur);
2130 /* END for je_source is payables and je_category is non treasury*/
2131 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert for je_source is payables and je_category is non treasury');
2132 /* Start for je_source is payables and je_category is treasury confirmation */
2133
2134 l_string2 := ' SELECT glb.je_batch_id,
2135 ffp.fund_value,
2136 ffp.description,
2137 fts.sf224_type_code,
2138 DECODE (xle.event_type_code, ''TREASURY_VOID'', REPLACE (glb.name, ''Budget Execution'', ''VOID''), glb.name),
2139 gll.ledger_id,
2140 glb.posted_date,
2141 nvl(xal.accounted_dr,0) - nvl(xal.accounted_cr,0),
2142 nvl(xal.accounted_dr,0) - nvl(xal.accounted_cr,0),
2143 frd.d_r_flag,
2144 LTRIM(RTRIM(xdl.APPLIED_TO_SOURCE_ID_NUM_1)), -- treasury confirmation id LTRIM(RTRIM(gll.reference_1)),
2145 NULL, --LTRIM(RTRIM(gll.reference_2)),
2146 LTRIM(RTRIM(AIP.check_id)), --Check_id LTRIM(RTRIM(gll.reference_3)),
2147 LTRIM(RTRIM(aid.invoice_id)), --invoice_id LTRIM(RTRIM(gll.reference_4)),
2148 NULL, --LTRIM(RTRIM(gll.reference_5)),
2149 LTRIM(RTRIM(ftc.treasury_doc_date)), --Accomplish date LTRIM(RTRIM(gll.reference_6)),
2150 NULL, --LTRIM(RTRIM(gll.reference_8)),
2151 NULL,--LTRIM(RTRIM(gll.reference_9)),
2152 gll.je_line_num,
2153 gll.je_header_id,
2154 gll.period_name,
2155 glb.default_period_name,
2156 glh.external_reference,
2157 fts.treasury_symbol,
2158 ffp.treasury_symbol_id,
2159 ''GLRECORD'',
2160 frd.federal_rpt_id,
2161 ''N'',
2162 frd.account,
2163 NULL,
2164 gll.effective_date,
2165 :b_user_id,
2166 :b_sysdate,
2167 :b_user_id,
2168 :b_sysdate,
2169 :b_login_id,
2170 :b_request_id,
2171 glh.je_source,
2172 glh.je_category,
2173 glh.je_from_sla_flag
2174 FROM gl_je_batches glb,
2175 gl_je_headers glh,
2176 gl_je_lines gll,
2177 gl_code_combinations gcc,
2178 fv_report_definitions frd,
2179 fv_fund_parameters ffp,
2180 fv_treasury_symbols fts,
2181 ap_invoice_distributions_all aid,
2182 ap_invoice_payments_all aip,
2183 ap_payment_hist_dists aphd,
2184 gl_import_references glir,
2185 xla_ae_headers xah,
2186 xla_ae_lines xal,
2187 xla_distribution_links xdl,
2188 fv_treasury_confirmations_all ftc,
2189 xla_events xle
2190 WHERE glb.status = ''P''
2191 AND glb.actual_flag = ''A''
2192 AND glb.je_batch_id = glh.je_batch_id
2193 AND glh.je_header_id = gll.je_header_id
2194 AND gll.code_combination_id = gcc.code_combination_id
2195 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
2196 AND gll.ledger_id = :b_sob
2197 AND frd.set_of_books_id = :b_sob
2198 AND ffp.set_of_books_id = :b_sob
2199 AND gcc.'||p_bal_segment||' = ffp.fund_value
2200 AND glh.currency_code = :b_g_currency_code
2201 AND glh.posted_date >= :posted_from_date
2202 AND glh.posted_date <= :posted_to_date
2203 AND gcc.'||p_acct_segment||' = frd.account
2204 AND frd.d_r_flag in (''D'',''R'')
2205 AND glh.je_category = ''Treasury Confirmation''
2206 AND glh.je_from_sla_flag = ''Y''
2207 AND aip.invoice_payment_id = aphd.invoice_payment_id
2208 AND glir.je_header_id = gll.je_header_id
2209 AND glir.je_line_num = gll.je_line_num
2210 AND xal.gl_sl_link_id = glir.gl_sl_link_id
2211 AND xal.gl_sl_link_table = glir.gl_sl_link_table
2212 AND xal.ae_header_id = xah.ae_header_id
2213 AND xdl.event_id = xah.event_id
2214 AND xle.event_id = xah.event_id
2215 AND ftc.treasury_confirmation_id = xdl.APPLIED_TO_SOURCE_ID_NUM_1
2216 AND xdl.ae_header_id = xah.ae_header_id
2217 AND xdl.ae_line_num = xal.ae_line_num
2218 AND xdl.source_distribution_type = ''FV_TREASURY_CONFIRMATIONS_ALL''
2219 AND xdl.source_distribution_id_num_1 = aphd.payment_hist_dist_id
2220 AND aid.invoice_distribution_id = aphd.invoice_distribution_id
2221 AND xdl.application_id = 8901 ' ;
2222 --IF (p_previous_run_date = g_lo_date) THEN
2223 l_string := l_string1 || l_string2 || l_string3 ;
2224 --ELSE
2225 --l_string := l_string1 || l_string2;
2226 --END IF;
2227
2228 l_cur:= dbms_sql.open_cursor;
2229 dbms_sql.parse(l_cur, l_string, DBMS_SQL.V7);
2230 dbms_sql.bind_variable(l_cur,':b_sob',p_set_of_books_id);
2231 dbms_sql.bind_variable(l_cur,':b_g_currency_code',p_currency_code);
2232 dbms_sql.bind_variable(l_cur,':posted_from_date',p_previous_run_date);
2233 dbms_sql.bind_variable(l_cur,':posted_to_date',p_current_run_date);
2234 dbms_sql.bind_variable(l_cur,':b_user_id',g_user_id);
2235 dbms_sql.bind_variable(l_cur,':b_sysdate',g_sysdate);
2236 dbms_sql.bind_variable(l_cur,':b_login_id',g_login_id);
2237 dbms_sql.bind_variable(l_cur,':b_request_id',g_request_id);
2238 l_row := dbms_sql.EXECUTE(l_cur);
2239 dbms_sql.close_cursor(l_cur);
2240 /* END for je_source is payables and je_category is treasury confirmation */
2241 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert for je_source is payables and je_category is treasury confirmation');
2242
2243 /* Start for je_source is project and je_category is labour_cost */
2244
2245 l_string2 := ' SELECT glb.je_batch_id,
2246 ffp.fund_value,
2247 ffp.description,
2248 fts.sf224_type_code,
2249 glb.name,
2250 gll.ledger_id,
2251 glb.posted_date,
2252 nvl(xal.accounted_dr,0) - nvl(xal.accounted_cr,0),
2253 nvl(xal.accounted_dr,0) - nvl(xal.accounted_cr,0),
2254 frd.d_r_flag,
2255 LTRIM(RTRIM(xte.SOURCE_ID_INT_1)), -- expenditure_item_id LTRIM(RTRIM(gll.reference_1)),
2256 NULL, --LTRIM(RTRIM(gll.reference_2)),
2257 NULL, --LTRIM(RTRIM(gll.reference_3)),
2258 NULL, --LTRIM(RTRIM(gll.reference_4)),
2259 NULL, --LTRIM(RTRIM(gll.reference_5)),
2260 NULL, --LTRIM(RTRIM(gll.reference_6)),
2261 NULL, --LTRIM(RTRIM(gll.reference_8)),
2262 NULL, --LTRIM(RTRIM(gll.reference_9)),
2263 gll.je_line_num,
2264 gll.je_header_id,
2265 gll.period_name,
2266 glb.default_period_name,
2267 glh.external_reference,
2268 fts.treasury_symbol,
2269 ffp.treasury_symbol_id,
2270 ''GLRECORD'',
2271 frd.federal_rpt_id,
2272 ''N'',
2273 frd.account,
2274 NULL,
2275 gll.effective_date,
2276 :b_user_id,
2277 :b_sysdate,
2278 :b_user_id,
2279 :b_sysdate,
2280 :b_login_id,
2281 :b_request_id,
2282 glh.je_source,
2283 glh.je_category,
2284 glh.je_from_sla_flag
2285
2286 FROM gl_je_batches glb,
2287 gl_je_headers glh,
2288 gl_je_lines gll,
2289 gl_code_combinations gcc,
2290 fv_report_definitions frd,
2291 fv_fund_parameters ffp,
2292 fv_treasury_symbols fts,
2293 gl_import_references glir,
2294 xla_ae_headers xah,
2295 xla_ae_lines xal,
2296 xla_events xet,
2297 xla_distribution_links xdl,
2298 xla_transaction_entities xte
2299 WHERE glb.status = ''P''
2300 AND glb.actual_flag = ''A''
2301 AND glb.je_batch_id = glh.je_batch_id
2302 AND glh.je_header_id = gll.je_header_id
2303 AND gll.code_combination_id = gcc.code_combination_id
2304 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
2305 AND gll.ledger_id = :b_sob
2306 AND frd.set_of_books_id = :b_sob
2307 AND ffp.set_of_books_id = :b_sob
2308 AND gcc.'||p_bal_segment||' = ffp.fund_value
2309 AND glh.currency_code = :b_g_currency_code
2310 AND glh.posted_date >= :posted_from_date
2311 AND glh.posted_date <= :posted_to_date
2312 AND gcc.'||p_acct_segment||' = frd.account
2313 AND frd.d_r_flag in (''D'',''R'')
2314 AND glh.je_source=''Project Accounting''
2315 AND glh.je_category = ''Labor Cost''
2316 AND glh.je_from_sla_flag = ''Y''
2317 AND glir.je_header_id = gll.je_header_id
2318 AND glir.je_line_num = gll.je_line_num
2319 AND xal.gl_sl_link_id = glir.gl_sl_link_id
2320 AND xal.gl_sl_link_table = glir.gl_sl_link_table
2321 AND xal.ae_header_id = xah.ae_header_id
2322 AND xet.event_id = xah.event_id
2323 -- AND xte.event_id = xet.event_id
2324 AND xdl.event_id = xet.event_id
2325 AND xdl.ae_header_id = xah.ae_header_id
2326 AND xdl.ae_line_num = xal.ae_line_num
2327 AND xte.entity_id = xet.entity_id
2328 AND xte.entity_code =''EXPENDITURES''
2329 AND xdl.APPLICATION_ID = 275 ';
2330
2331 --IF (p_previous_run_date = g_lo_date) THEN
2332 l_string := l_string1 || l_string2 || l_string3 ;
2333 --ELSE
2334 --l_string := l_string1 || l_string2;
2335 --END IF;
2336
2337 l_cur:= dbms_sql.open_cursor;
2338 dbms_sql.parse(l_cur, l_string, DBMS_SQL.V7);
2339 dbms_sql.bind_variable(l_cur,':b_sob',p_set_of_books_id);
2340 dbms_sql.bind_variable(l_cur,':b_g_currency_code',p_currency_code);
2341 dbms_sql.bind_variable(l_cur,':posted_from_date',p_previous_run_date);
2342 dbms_sql.bind_variable(l_cur,':posted_to_date',p_current_run_date);
2343 dbms_sql.bind_variable(l_cur,':b_user_id',g_user_id);
2344 dbms_sql.bind_variable(l_cur,':b_sysdate',g_sysdate);
2345 dbms_sql.bind_variable(l_cur,':b_login_id',g_login_id);
2346 dbms_sql.bind_variable(l_cur,':b_request_id',g_request_id);
2347 l_row := dbms_sql.EXECUTE(l_cur);
2348 dbms_sql.close_cursor(l_cur);
2349 /* END for je_source is project and je_category is labour_cost */
2350 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert for je_source is project and je_category is labour_cost');
2351 /* Start for je_source is Receivables, based on ar_cash_receipt_history_all */
2352
2353 l_string2 := ' SELECT glb.je_batch_id,
2354 ffp.fund_value,
2355 ffp.description,
2356 fts.sf224_type_code,
2357 glb.name,
2358 gll.ledger_id,
2359 glb.posted_date,
2360 nvl(xal.accounted_dr,0) - nvl(xal.accounted_cr,0),
2361 nvl(xal.accounted_dr,0) - nvl(xal.accounted_cr,0),
2362 frd.d_r_flag,
2363 NULL, --LTRIM(RTRIM(gll.reference_1)),
2364 LTRIM(RTRIM(arch.cash_receipt_id)), --LTRIM(RTRIM(gll.reference_2)),
2365 NULL, --LTRIM(RTRIM(gll.reference_3)),
2366 NULL, --LTRIM(RTRIM(gll.reference_4)),
2367 LTRIM(RTRIM(arch.CASH_RECEIPT_HISTORY_ID)), ---cash_receipt_hist_id --LTRIM(RTRIM(gll.reference_5)),
2368 NULL, --LTRIM(RTRIM(gll.reference_6)),
2369 NULL, --LTRIM(RTRIM(gll.reference_8)),
2370 NULL, --LTRIM(RTRIM(gll.reference_9)),
2371 gll.je_line_num,
2372 gll.je_header_id,
2373 gll.period_name,
2374 glb.default_period_name,
2375 glh.external_reference,
2376 fts.treasury_symbol,
2377 ffp.treasury_symbol_id,
2378 ''GLRECORD'',
2379 frd.federal_rpt_id,
2380 ''N'',
2381 frd.account,
2382 NULL,
2383 gll.effective_date,
2384 :b_user_id,
2385 :b_sysdate,
2386 :b_user_id,
2387 :b_sysdate,
2388 :b_login_id,
2389 :b_request_id,
2390 glh.je_source,
2391 glh.je_category,
2392 glh.je_from_sla_flag
2393 FROM gl_je_batches glb,
2394 gl_je_headers glh,
2395 gl_je_lines gll,
2396 gl_code_combinations gcc,
2397 fv_report_definitions frd,
2398 fv_fund_parameters ffp,
2399 fv_treasury_symbols fts,
2400 ar_distributions_all ard,
2401 ar_cash_receipt_history_all arch,
2402 gl_import_references glir,
2403 xla_ae_headers xah,
2404 xla_ae_lines xal,
2405 xla_events xet,
2406 xla_distribution_links xdl,
2407 xla_transaction_entities xte
2408 WHERE glb.status = ''P''
2409 AND glb.actual_flag = ''A''
2410 AND glb.je_batch_id = glh.je_batch_id
2411 AND glh.je_header_id = gll.je_header_id
2412 AND gll.code_combination_id = gcc.code_combination_id
2413 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
2414 AND gll.ledger_id = :b_sob
2415 AND frd.set_of_books_id = :b_sob
2416 AND ffp.set_of_books_id = :b_sob
2417 AND gcc.'||p_bal_segment||' = ffp.fund_value
2418 AND glh.currency_code = :b_g_currency_code
2419 AND glh.posted_date >= :posted_from_date
2420 AND glh.posted_date <= :posted_to_date
2421 AND gcc.'||p_acct_segment||' = frd.account
2422 AND frd.d_r_flag in (''D'',''R'')
2423 AND glh.je_source=''Receivables''
2424 --AND glh.je_category = ''Misc Receipts''
2425 AND glh.je_from_sla_flag = ''Y''
2426 AND glir.je_header_id = gll.je_header_id
2427 AND glir.je_line_num = gll.je_line_num
2428 AND xal.gl_sl_link_id = glir.gl_sl_link_id
2429 AND xal.gl_sl_link_table = glir.gl_sl_link_table
2430 AND xal.ae_header_id = xah.ae_header_id
2431 AND xet.event_id = xah.event_id
2432 -- AND xte.event_id = xet.event_id
2433 AND xte.entity_id = xet.entity_id
2434 AND xdl.event_id = xet.event_id
2435 AND xdl.ae_header_id = xah.ae_header_id
2436 AND xdl.ae_line_num = xal.ae_line_num
2437 AND xdl.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
2438 AND xdl.source_distribution_id_num_1 = ard.line_id
2439 AND ard.source_table=''CRH''
2440 AND ard.source_id = arch.CASH_RECEIPT_HISTORY_ID
2441 AND xdl.APPLICATION_ID = 222 ';
2442 --IF (p_previous_run_date = g_lo_date) THEN
2443 l_string := l_string1 || l_string2 || l_string3 ;
2444 --ELSE
2445 --l_string := l_string1 || l_string2;
2446 --END IF;
2447
2448
2449 l_cur:= dbms_sql.open_cursor;
2450 dbms_sql.parse(l_cur, l_string, DBMS_SQL.V7);
2451 dbms_sql.bind_variable(l_cur,':b_sob',p_set_of_books_id);
2452 dbms_sql.bind_variable(l_cur,':b_g_currency_code',p_currency_code);
2453 dbms_sql.bind_variable(l_cur,':posted_from_date',p_previous_run_date);
2454 dbms_sql.bind_variable(l_cur,':posted_to_date',p_current_run_date);
2455 dbms_sql.bind_variable(l_cur,':b_user_id',g_user_id);
2456 dbms_sql.bind_variable(l_cur,':b_sysdate',g_sysdate);
2457 dbms_sql.bind_variable(l_cur,':b_login_id',g_login_id);
2458 dbms_sql.bind_variable(l_cur,':b_request_id',g_request_id);
2459 l_row := dbms_sql.EXECUTE(l_cur);
2460 dbms_sql.close_cursor(l_cur);
2461 /* END for je_source is Receivables, based on ar_cash_receipt_history_all*/
2462 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert for je_source is Receivables, based on ar_cash_receipt_history_all');
2463 /* Start for je_source is Receivables , based on AR_RECEIVABLE_APPLICATIONS_ALL */
2464
2465 l_string2 := ' SELECT glb.je_batch_id,
2466 ffp.fund_value,
2467 ffp.description,
2468 fts.sf224_type_code,
2469 glb.name,
2470 gll.ledger_id,
2471 glb.posted_date,
2472 nvl(xal.accounted_dr,0) - nvl(xal.accounted_cr,0),
2473 nvl(xal.accounted_dr,0) - nvl(xal.accounted_cr,0),
2474 frd.d_r_flag,
2475 NULL, --LTRIM(RTRIM(gll.reference_1)),
2476 LTRIM(RTRIM(arr.cash_receipt_id)), --LTRIM(RTRIM(gll.reference_2)),
2477 NULL, --LTRIM(RTRIM(gll.reference_3)),
2478 NULL, --LTRIM(RTRIM(gll.reference_4)),
2479 LTRIM(RTRIM(arr.receivable_application_id)), ---cash_receipt_hist_id --LTRIM(RTRIM(gll.reference_5)),
2480 NULL, --LTRIM(RTRIM(gll.reference_6)),
2481 NULL, --LTRIM(RTRIM(gll.reference_8)),
2482 NULL, --LTRIM(RTRIM(gll.reference_9)),
2483 gll.je_line_num,
2484 gll.je_header_id,
2485 gll.period_name,
2486 glb.default_period_name,
2487 glh.external_reference,
2488 fts.treasury_symbol,
2489 ffp.treasury_symbol_id,
2490 ''GLRECORD'',
2491 frd.federal_rpt_id,
2492 ''N'',
2493 frd.account,
2494 NULL,
2495 gll.effective_date,
2496 :b_user_id,
2497 :b_sysdate,
2498 :b_user_id,
2499 :b_sysdate,
2500 :b_login_id,
2501 :b_request_id,
2502 glh.je_source,
2503 glh.je_category,
2504 glh.je_from_sla_flag
2505 FROM gl_je_batches glb,
2506 gl_je_headers glh,
2507 gl_je_lines gll,
2508 gl_code_combinations gcc,
2509 fv_report_definitions frd,
2510 fv_fund_parameters ffp,
2511 fv_treasury_symbols fts,
2512 ar_distributions_all ard,
2513 AR_RECEIVABLE_APPLICATIONS_ALL arr,
2514 gl_import_references glir,
2515 xla_ae_headers xah,
2516 xla_ae_lines xal,
2517 xla_events xet,
2518 xla_distribution_links xdl,
2519 xla_transaction_entities xte
2520 WHERE glb.status = ''P''
2521 AND glb.actual_flag = ''A''
2522 AND glb.je_batch_id = glh.je_batch_id
2523 AND glh.je_header_id = gll.je_header_id
2524 AND gll.code_combination_id = gcc.code_combination_id
2525 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
2526 AND gll.ledger_id = :b_sob
2527 AND frd.set_of_books_id = :b_sob
2528 AND ffp.set_of_books_id = :b_sob
2529 AND gcc.'||p_bal_segment||' = ffp.fund_value
2530 AND glh.currency_code = :b_g_currency_code
2531 AND glh.posted_date >= :posted_from_date
2532 AND glh.posted_date <= :posted_to_date
2533 AND gcc.'||p_acct_segment||' = frd.account
2534 AND frd.d_r_flag in (''D'',''R'')
2535 AND glh.je_source=''Receivables''
2536 --AND glh.je_category = ''Misc Receipts''
2537 AND glh.je_from_sla_flag = ''Y''
2538 AND glir.je_header_id = gll.je_header_id
2539 AND glir.je_line_num = gll.je_line_num
2540 AND xal.gl_sl_link_id = glir.gl_sl_link_id
2541 AND xal.gl_sl_link_table = glir.gl_sl_link_table
2542 AND xal.ae_header_id = xah.ae_header_id
2543 AND xet.event_id = xah.event_id
2544 --AND xte.event_id = xet.event_id
2545 AND xte.entity_id = xet.entity_id
2546 AND xdl.event_id = xet.event_id
2547 AND xdl.ae_header_id = xah.ae_header_id
2548 AND xdl.ae_line_num = xal.ae_line_num
2549 AND xdl.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
2550 AND xdl.source_distribution_id_num_1 = ard.line_id
2551 AND ard.source_table=''RA''
2552 AND ard.source_id = arr.receivable_application_id
2553 AND xdl.APPLICATION_ID = 222 ';
2554
2555 --IF (p_previous_run_date = g_lo_date) THEN
2556 l_string := l_string1 || l_string2 || l_string3 ;
2557 --ELSE
2558 --l_string := l_string1 || l_string2;
2559 --END IF;
2560
2561 l_cur:= dbms_sql.open_cursor;
2562 dbms_sql.parse(l_cur, l_string, DBMS_SQL.V7);
2563 dbms_sql.bind_variable(l_cur,':b_sob',p_set_of_books_id);
2564 dbms_sql.bind_variable(l_cur,':b_g_currency_code',p_currency_code);
2565 dbms_sql.bind_variable(l_cur,':posted_from_date',p_previous_run_date);
2566 dbms_sql.bind_variable(l_cur,':posted_to_date',p_current_run_date);
2567 dbms_sql.bind_variable(l_cur,':b_user_id',g_user_id);
2568 dbms_sql.bind_variable(l_cur,':b_sysdate',g_sysdate);
2569 dbms_sql.bind_variable(l_cur,':b_login_id',g_login_id);
2570 dbms_sql.bind_variable(l_cur,':b_request_id',g_request_id);
2571 l_row := dbms_sql.EXECUTE(l_cur);
2572 dbms_sql.close_cursor(l_cur);
2573
2574 /* END for je_source is Receivables , based on AR_RECEIVABLE_APPLICATIONS_ALL */
2575 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert for je_source is Receivables , based on AR_RECEIVABLE_APPLICATIONS_ALL ');
2576 /* Start for for je_source is Receivables , based on AR_MISC_CASH_DISTRIBUTIONS_ALL */
2577
2578 l_string2 := ' SELECT glb.je_batch_id,
2579 ffp.fund_value,
2580 ffp.description,
2581 fts.sf224_type_code,
2582 glb.name,
2583 gll.ledger_id,
2584 glb.posted_date,
2585 nvl(xal.accounted_dr,0) - nvl(xal.accounted_cr,0),
2586 nvl(xal.accounted_dr,0) - nvl(xal.accounted_cr,0),
2587 frd.d_r_flag,
2588 NULL, --LTRIM(RTRIM(gll.reference_1)),
2589 LTRIM(RTRIM(arm.cash_receipt_id)),
2590 NULL, --LTRIM(RTRIM(gll.reference_3)),
2591 NULL, --LTRIM(RTRIM(gll.reference_4)),
2592 LTRIM(RTRIM(arm.MISC_CASH_DISTRIBUTION_ID)), --cash_receipt_hist_id --LTRIM(RTRIM(gll.reference_5)),
2593 NULL, --LTRIM(RTRIM(gll.reference_6)),
2594 NULL, --LTRIM(RTRIM(gll.reference_8)),
2595 NULL, --LTRIM(RTRIM(gll.reference_9)),
2596 gll.je_line_num,
2597 gll.je_header_id,
2598 gll.period_name,
2599 glb.default_period_name,
2600 glh.external_reference,
2601 fts.treasury_symbol,
2602 ffp.treasury_symbol_id,
2603 ''GLRECORD'',
2604 frd.federal_rpt_id,
2605 ''N'',
2606 frd.account,
2607 NULL,
2608 gll.effective_date,
2609 :b_user_id,
2610 :b_sysdate,
2611 :b_user_id,
2612 :b_sysdate,
2613 :b_login_id,
2614 :b_request_id,
2615 glh.je_source,
2616 glh.je_category,
2617 glh.je_from_sla_flag
2618 FROM gl_je_batches glb,
2619 gl_je_headers glh,
2620 gl_je_lines gll,
2621 gl_code_combinations gcc,
2622 fv_report_definitions frd,
2623 fv_fund_parameters ffp,
2624 fv_treasury_symbols fts,
2625 ar_distributions_all ard,
2626 AR_MISC_CASH_DISTRIBUTIONS_ALL arm,
2627 gl_import_references glir,
2628 xla_ae_headers xah,
2629 xla_ae_lines xal,
2630 xla_events xet,
2631 xla_distribution_links xdl,
2632 xla_transaction_entities xte
2633 WHERE glb.status = ''P''
2634 AND glb.actual_flag = ''A''
2635 AND glb.je_batch_id = glh.je_batch_id
2636 AND glh.je_header_id = gll.je_header_id
2637 AND gll.code_combination_id = gcc.code_combination_id
2638 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
2639 AND gll.ledger_id = :b_sob
2640 AND frd.set_of_books_id = :b_sob
2641 AND ffp.set_of_books_id = :b_sob
2642 AND gcc.'||p_bal_segment||' = ffp.fund_value
2643 AND glh.currency_code = :b_g_currency_code
2644 AND glh.posted_date >= :posted_from_date
2645 AND glh.posted_date <= :posted_to_date
2646 AND gcc.'||p_acct_segment||' = frd.account
2647 AND frd.d_r_flag in (''D'',''R'')
2648 AND glh.je_source=''Receivables''
2649 --AND glh.je_category = ''Misc Receipts''
2650 AND glh.je_from_sla_flag = ''Y''
2651 AND glir.je_header_id = gll.je_header_id
2652 AND glir.je_line_num = gll.je_line_num
2653 AND xal.gl_sl_link_id = glir.gl_sl_link_id
2654 AND xal.gl_sl_link_table = glir.gl_sl_link_table
2655 AND xal.ae_header_id = xah.ae_header_id
2656 AND xet.event_id = xah.event_id
2657 --AND xte.event_id = xet.event_id
2658 AND xte.entity_id = xet.entity_id
2659 AND xdl.event_id = xet.event_id
2660 AND xdl.ae_header_id = xah.ae_header_id
2661 AND xdl.ae_line_num = xal.ae_line_num
2662 AND xdl.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
2663 AND xdl.source_distribution_id_num_1 = ard.line_id
2664 AND ard.source_id = arm.MISC_CASH_DISTRIBUTION_ID
2665 AND ard.source_table=''MCD''
2666 AND xdl.APPLICATION_ID = 222 ';
2667 --IF (p_previous_run_date = g_lo_date) THEN
2668 l_string := l_string1 || l_string2 || l_string3 ;
2669 --ELSE
2670 --l_string := l_string1 || l_string2;
2671 --END IF;
2672
2673
2674 l_cur:= dbms_sql.open_cursor;
2675 dbms_sql.parse(l_cur, l_string, DBMS_SQL.V7);
2676 dbms_sql.bind_variable(l_cur,':b_sob',p_set_of_books_id);
2677 dbms_sql.bind_variable(l_cur,':b_g_currency_code',p_currency_code);
2678 dbms_sql.bind_variable(l_cur,':posted_from_date',p_previous_run_date);
2679 dbms_sql.bind_variable(l_cur,':posted_to_date',p_current_run_date);
2680 dbms_sql.bind_variable(l_cur,':b_user_id',g_user_id);
2681 dbms_sql.bind_variable(l_cur,':b_sysdate',g_sysdate);
2682 dbms_sql.bind_variable(l_cur,':b_login_id',g_login_id);
2683 dbms_sql.bind_variable(l_cur,':b_request_id',g_request_id);
2684 l_row := dbms_sql.EXECUTE(l_cur);
2685 dbms_sql.close_cursor(l_cur);
2686 /* END for je_source is Receivables , based on AR_MISC_CASH_DISTRIBUTIONS_ALL */
2687 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert for je_source is Receivables , based on AR_MISC_CASH_DISTRIBUTIONS_ALL ');
2688
2689 EXCEPTION
2690 WHEN OTHERS THEN
2691 p_error_code := sqlcode;
2692 p_error_desc := sqlerrm;
2693 fv_utility.log_mesg(fnd_log.level_unexpected, l_module_name||'.final_exception', p_error_desc) ;
2694 END insert_sf224_batches ;
2695
2696
2697 ----------------------------------------------------------------------
2698 -- PROCESS_SF224_TRANSACTIONS
2699 ----------------------------------------------------------------------
2700 PROCEDURE Process_sf224_transactions is
2701
2702 l_module_name VARCHAR2(200);
2703 l_org_amount NUMBER;
2704 l_reference NUMBER;
2705 l_exists VARCHAR2(1);
2706 p_def_p_ia_paygroup VARCHAR2(30);
2707 l_inv_pay_id NUMBER(15):= 0;
2708
2709 --Variables defined for dbms_sql
2710 l_cursor_id INTEGER;
2711 l_ignore INTEGER;
2712 l_select VARCHAR2(2000);
2713
2714 vl_checkrun_name Ap_Inv_Selection_Criteria_All.checkrun_name%TYPE;
2715 vl_treasury_symbol Fv_Treasury_Symbols.treasury_symbol%TYPE;
2716 vl_misc_cd_flag VARCHAR2(1);
2717 vl_refund_cnt NUMBER:= 0;
2718
2719 vg_bank_acct_id ce_bank_accounts.bank_account_id%TYPE;
2720 l_temp_cr_hist_id NUMBER;
2721 l_cash_receipt_hist_id NUMBER;
2722 l_federal_rpt_id Fv_Report_Definitions.federal_rpt_id%TYPE;
2723 l_rowid VARCHAR2(25);
2724 l_vendor_id AP_INVOICES.VENDOR_ID%TYPE;
2725 l_INVOICE_ID AP_INVOICES.invoice_ID%TYPE;
2726 l_inv_amount NUMBER;
2727 l_check_date DATE;
2728 l_payables_ia_paygroup FV_system_parameters_v.payables_ia_paygroup%type;
2729
2730 -- References invoice_id populated by treasury confirmation procedure
2731 L_REFERENCE_4 FV_SF224_TEMP.REFERENCE_4%TYPE ;
2732 L_REFERENCE_5 FV_SF224_TEMP.REFERENCE_5%TYPE ;
2733 L_REFERENCE_6 FV_SF224_TEMP.REFERENCE_6%TYPE ;
2734
2735 l_fund_code VARCHAR2(25);
2736 l_error_stage NUMBER;
2737 l_processed_flag VARCHAR2(1);
2738 l_cb_flag VARCHAR2(1);
2739 l_cash_receipt_id NUMBER;
2740 l_dummy VARCHAR2(1);
2741 l_invoice_date DATE;
2742 l_update_type VARCHAR2(25);
2743 l_org_id NUMBER(15);
2744 x_amount NUMBER;
2745 l_void_date DATE;
2746
2747 l_business_activity_code fv_alc_addresses_all.business_activity_code%TYPE;
2748 l_gwa_reporter_category_code fv_alc_gwa_categories.gwa_reporter_category_code%TYPE;
2749 l_alc_addresses_id fv_alc_addresses_all.fv_alc_addresses_id%TYPE;
2750
2751 --Added for reclassification changes
2752 l_void_check_number ap_checks_all.void_check_number%TYPE;
2753 l_voided_reclassified_payment VARCHAR2(1);
2754 l_reclass_receipt_number ar_cash_receipts_all.receipt_number%TYPE;
2755 l_receipt_reclassified VARCHAR2(1);
2756 l_ar_reclass_attribute VARCHAR2(15);
2757 sql_stmt VARCHAR2(500);
2758 sql_stmt1 VARCHAR2(500);
2759 l_reclass VARCHAR2(1) :='N';
2760
2761 l_invoice_type_lookup_code VARCHAR2(25);
2762 l_payment_type_flag VARCHAR2(25);
2763 l_check_void_date DATE;
2764
2765 CURSOR C1
2766 (
2767 c_set_of_books_id NUMBER
2768 )IS
2769 SELECT rowid,
2770 je_batch_id,
2771 fund_code,
2772 sf224_type_code,
2773 name,
2774 amount,
2775 actual_amount,
2776 d_r_flag,
2777 accomplish_date,
2778 reference_1,
2779 reference_2,
2780 reference_3,
2781 reference_4,
2782 reference_5,
2783 reference_6,
2784 reference_8,
2785 reference_9,
2786 je_line_num,
2787 je_header_id,
2788 reported_flag,
2789 exception_exists,
2790 record_category,
2791 gl_period,
2792 exception_category,
2793 exception_section,
2794 reported_month,
2795 column_group,
2796 record_type,
2797 inter_agency_flag,
2798 obligation_date,
2799 treasury_symbol,
2800 treasury_symbol_id,
2801 federal_rpt_id,
2802 txn_category,
2803 je_source,
2804 je_category,
2805 je_from_sla_flag
2806 FROM fv_sf224_temp
2807 WHERE set_of_books_id = c_set_of_books_id
2808 AND sf224_processed_flag = 'N';
2809
2810 CURSOR C2
2811 (
2812 c_cash_receipt_id NUMBER,
2813 c_fund_code VARCHAR2
2814 ) IS
2815 SELECT obligation_date,
2816 refund_amount
2817 FROM fv_refunds_voids_all
2818 WHERE cash_receipt_id = c_cash_receipt_id
2819 AND type = 'AP_REFUND'
2820 AND fund_value = c_fund_code;
2821
2822 CURSOR get_count_csr
2823 (
2824 c_batch_id NUMBER,
2825 c_je_header_id NUMBER,
2826 c_je_category VARCHAR2,
2827 c_cash_receipt_id NUMBER,
2828 c_fund_code VARCHAR2,
2829 c_je_from_sla_flag VARCHAR2
2830 )
2831 IS
2832 SELECT COUNT(*)
2833 FROM fv_sf224_temp
2834 WHERE je_batch_id = c_batch_id
2835 AND je_header_id = c_je_header_id
2836 AND DECODE(c_je_from_sla_flag, 'Y', reference_2, DECODE(c_je_category,'Misc Receipts',reference_2, SUBSTR(reference_2,0,INSTR(reference_2,'C')-1)))
2837 = TO_CHAR(c_cash_receipt_id)
2838 AND fund_code = c_fund_code
2839 AND name = 'Refunds_and_Voids'
2840 AND record_category = 'CREATED'
2841 AND record_type = 'Receipt_refund';
2842
2843 BEGIN
2844 l_module_name := g_module_name || 'Process_sf224_transactions';
2845 l_exists := 'N' ;
2846 vl_misc_cd_flag := 'N';
2847 error_code := g_SUCCESS;
2848 OPEN C1 (sob);
2849 IF(sqlcode < 0) THEN
2850 error_code := sqlcode;
2851 error_buf := sqlerrm;
2852 RETURN;
2853 END IF;
2854
2855 p_def_org_id := mo_global.get_current_org_id;
2856 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2857 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Deriving PAYGROUP for org id '|| p_def_org_id);
2858 END IF;
2859
2860 -- Determine the default paygroup based on the org_id
2861 BEGIN
2862 SELECT payables_ia_paygroup
2863 INTO p_def_p_ia_paygroup
2864 FROM FV_Operating_units_all
2865 WHERE org_id = p_def_org_id;
2866 EXCEPTION
2867 WHEN No_Data_Found THEN
2868 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2869 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PAYABLES IA PAYGROUP NOT FOUND,BASED ON THE ORG_ID '
2870 ||TO_CHAR(p_def_org_id));
2871 END IF;
2872 NULL;
2873 WHEN OTHERS THEN
2874 error_code := g_FAILURE;
2875 error_buf :=SQLERRM || '-- Error in Process_Sf224_Transactions procedure '||
2876 'while determining the payables ia paygroup.';
2877 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error2', error_buf) ;
2878 END;
2879
2880 --Determine the attribute column used in AR Reclass Receipt DFF
2881 BEGIN
2882 SELECT AR_RECLASS_ATTRIBUTE
2883 INTO l_ar_reclass_attribute
2884 FROM fv_system_parameters;
2885 EXCEPTION
2886 WHEN NO_DATA_FOUND THEN
2887 l_ar_reclass_attribute:=NULL;
2888 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2889 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Receivables Reclassification Field' ||
2890 ' has not set in Define Federal System Parameters Form');
2891 END IF;
2892 END;
2893
2894 /* ----- Populating the C1 Cursor ------------*/
2895 LOOP
2896 FETCH C1 INTO
2897 l_rowid,
2898 l_batch_id,
2899 l_fund_code,
2900 l_sf224_type_code,
2901 l_name,
2902 l_amount,
2903 l_org_amount,
2904 l_d_r_flag,
2905 l_accomplish_date,
2906 l_reference_1,
2907 l_reference_2,
2908 l_reference_3,
2909 l_reference_4,
2910 l_reference_5,
2911 l_reference_6,
2912 l_reference_8,
2913 l_reference_9,
2914 l_je_line_num,
2915 l_je_header_id,
2916 l_reported_flag,
2917 l_exception_exists,
2918 l_record_category,
2919 l_gl_period,
2920 l_exception_category,
2921 l_exception_section,
2922 l_reported_month,
2923 l_column_group,
2924 l_record_type,
2925 l_ia_flag,
2926 l_obligation_date,
2927 l_treasury_symbol,
2928 l_treasury_symbol_id,
2929 l_federal_rpt_id,
2930 l_txn_category,
2931 l_je_source,
2932 l_je_category,
2933 l_je_from_sla_flag;
2934
2935 IF (C1%NOTFOUND) THEN
2936 EXIT;
2937 END IF;
2938
2939 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2940 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'---------------------------------');
2941 END IF;
2942 -- Determine the default alc
2943 BEGIN
2944 SELECT agency_location_code
2945 INTO p_def_alc_code
2946 FROM Fv_Report_Definitions
2947 WHERE federal_rpt_id = l_federal_rpt_id;
2948 EXCEPTION
2949 WHEN NO_DATA_FOUND THEN
2950 error_buf := 'The default agency_location_code could not be determined
2951 -- Error in Process_Sf224_Transactions procedure.';
2952 error_code := g_FAILURE;
2953 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error3', error_buf) ;
2954 RETURN;
2955 WHEN OTHERS THEN
2956 error_buf := SQLERRM||'-- Error in Process_Sf224_Transactions procedure.';
2957 error_code := g_FAILURE;
2958 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error4', error_buf) ;
2959 RETURN;
2960 END;
2961 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2962 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'DEFAULT ALC IS '||P_DEF_ALC_CODE);
2963 END IF;
2964 --Fetch the end date for the period in which txn was entered
2965 BEGIN
2966 SELECT start_date,
2967 end_date
2968 INTO l_txn_start_date,
2969 l_txn_end_date
2970 FROM gl_period_statuses glp
2971 WHERE glp.period_name = l_gl_period
2972 AND glp.ledger_id = sob
2973 AND glp.application_id = 101;
2974 EXCEPTION WHEN OTHERS THEN
2975 error_code := 2;
2976 error_buf := substr(sqlerrm,1,50) || ' while fetching txn end date';
2977 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error5', error_buf) ;
2978 END;
2979
2980 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2981 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'END DATE OF THE TRANSACTION PERIOD '||
2982 to_char(l_txn_end_date, 'MM/DD/YYYY'));
2983 END IF;
2984
2985
2986 -----------------------------------------------------------------------
2987 -- Initializing variables
2988 -----------------------------------------------------------------------
2989
2990 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2991 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESSING TXN WITH SOURCE ='||
2992 L_JE_SOURCE||',Category='||l_je_category||',Batch='||l_name||
2993 ',Header='||l_je_header_id||',Line='||l_je_line_num );
2994 END IF;
2995 l_processed_flag := 'N';
2996 l_exception_category := NULL;
2997 l_exception_section := -1;
2998 l_billing_agency_fund := NULL;
2999 l_ia_flag := 'N';
3000 l_txn_category := NULL;
3001 l_sign_number := 1;
3002 l_accomplish_date := NULL;
3003 l_alc_code := p_def_alc_code;
3004 l_type := NULL;
3005 l_update_type := NULL;
3006 x_amount := ABS(l_amount);
3007 x_name := 'MANUAL';
3008
3009 l_void_check_number :=NULL;
3010 l_voided_reclassified_payment:='N';
3011 l_reclass_receipt_number :=NULL;
3012 l_receipt_reclassified :='N';
3013 sql_stmt:=NULL;
3014 sql_stmt1:=NULL;
3015 -----------------------------------------------------------
3016 -- Assign the transaction type according to Batch Name
3017 ------------------------------------------------------------
3018 l_document_number := NULL;
3019
3020 IF (l_je_category = 'Treasury Confirmation' AND
3021 ((NVL(l_je_from_sla_flag,'N') IN ('N', 'U') AND upper(l_name) like '%TREASURY%') OR
3022 (NVL(l_je_from_sla_flag,'N')='Y' AND upper(l_name) like '%BUDGET EXECUTION%'))) then -- source1
3023 -- get the document number
3024
3025 BEGIN
3026 SELECT checkrun_name
3027 INTO l_document_number
3028 FROM ap_checks_all
3029 WHERE check_id = TO_NUMBER(l_reference_3);
3030 EXCEPTION WHEN NO_DATA_FOUND THEN
3031 SELECT SUBSTR(name,1,50)
3032 INTO l_document_number
3033 FROM gl_je_headers
3034 WHERE je_header_id = l_je_header_id;
3035 END;
3036 IF l_reference_1 IS NULL AND l_reference_6 IS NULL Then -- ref1
3037 x_name := 'Blank Treasury confirmation Id' ;
3038 Set_Exception('R');
3039 ELSIF l_reference_1 IS NOT NULL THEN -- ref1
3040 BEGIN
3041 select 'Y'
3042 into l_exists
3043 from Fv_treasury_confirmations_all
3044 Where treasury_confirmation_id = to_number(l_REFERENCE_1) ;
3045 EXCEPTION
3046 When no_data_found then
3047 x_name := 'Invalid Treasury Confirmation Id - '
3048 || l_reference_1 ;
3049 Set_Exception('R');
3050 When INVALID_NUMBER OR VALUE_ERROR Then
3051 x_name := 'Error while converting to number - '
3052 || l_reference_1 ;
3053 Set_Exception('R');
3054 END ;
3055 END IF; -- ref1
3056 ELSIF (l_je_source = 'Project Accounting') AND (l_je_category ='Labor Cost') THEN -- source1
3057 IF l_accomplish_attribute IS NOT NULL THEN
3058 IF NVL(l_je_from_sla_flag, 'N') IN ('N','U') THEN
3059 IF l_reference_1 IS NULL THEN
3060 -- PA batch name is null then process as Manual.
3061 x_name :='Blank Project Accting Batch Name';
3062 Set_Exception('R');
3063 ELSE
3064 BEGIN
3065 --go find accomplish date in PA
3066 l_select := 'select pei.'||l_accomplish_attribute||
3067 ',pei.org_id from pa_cost_distribution_lines_all pcdl,
3068 pa_expenditure_items_all pei
3069 where pcdl.batch_name =:b_reference_1
3070 and pcdl.expenditure_item_id = pei.expenditure_item_id';
3071 COMMIT;
3072 -- Get document number
3073 SELECT SUBSTR(name,1,50)
3074 INTO l_document_number
3075 FROM gl_je_headers
3076 WHERE je_header_id = l_je_header_id;
3077 l_cursor_id := dbms_sql.open_cursor;
3078 dbms_sql.parse(l_cursor_id, l_select, dbms_sql.v7);
3079 dbms_sql.bind_variable(l_cursor_id,':b_reference_1',l_reference_1);
3080 dbms_sql.define_column(l_cursor_id, 1, l_accomplish_date);
3081 dbms_sql.define_column(l_cursor_id, 2, l_org_id);
3082 l_ignore := dbms_sql.execute(l_cursor_id);
3083 l_ignore := dbms_sql.fetch_rows(l_cursor_id);
3084 IF (l_ignore > 0) THEN
3085 dbms_sql.column_value(l_cursor_id, 1, l_accomplish_date);
3086 dbms_sql.column_value(l_cursor_id, 2, l_org_id);
3087 IF l_accomplish_date IS NULL THEN
3088 -- create an exception
3089 l_reported_flag :='Y';
3090 l_exception_exists :='Y' ;
3091 l_record_category :='EXCEPTION';
3092 l_exception_category :='NO_PA_ACCOMPLISH_DATE';
3093 l_exception_section := 7;
3094 l_alc_code := p_def_alc_code;
3095 l_accomplish_date := l_txn_end_date ;
3096 Insert_new_transaction(l_amount, 1);
3097 END IF;
3098 ELSE
3099 x_name := 'No Project Accting Batch Name Found';
3100 Set_Exception('R');
3101 END IF;
3102 EXCEPTION
3103 WHEN others THEN
3104 error_buf :='PA Accomplish Date Error'||sqlerrm;
3105 error_code := g_FAILURE;
3106 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error7', error_buf) ;
3107 RETURN;
3108 END;
3109 END IF;
3110 ELSE --l_je_from_sla_flag
3111 IF l_reference_1 IS NOT NULL THEN
3112 -- PA Expenditure Type Id is null.
3113 x_name :='Blank Project Expenditure Type Id';
3114 Set_Exception('R');
3115 ELSE
3116 BEGIN
3117 --go find accomplish date in PA
3118 l_select := 'select pei.'||l_accomplish_attribute||
3119 ',pei.org_id from pa_cost_distribution_lines_all pcdl,
3120 pa_expenditure_items_all pei
3121 where pcdl.expenditure_item_id =:b_reference_1
3122 and pcdl.expenditure_item_id = pei.expenditure_item_id';
3123 COMMIT;
3124 -- Get document number
3125 SELECT SUBSTR(name,1,50)
3126 INTO l_document_number
3127 FROM gl_je_headers
3128 WHERE je_header_id = l_je_header_id;
3129 l_cursor_id := dbms_sql.open_cursor;
3130 dbms_sql.parse(l_cursor_id, l_select, dbms_sql.v7);
3131 dbms_sql.bind_variable(l_cursor_id,':b_reference_1',l_reference_1);
3132 dbms_sql.define_column(l_cursor_id, 1, l_accomplish_date);
3133 dbms_sql.define_column(l_cursor_id, 2, l_org_id);
3134 l_ignore := dbms_sql.execute(l_cursor_id);
3135 l_ignore := dbms_sql.fetch_rows(l_cursor_id);
3136 IF (l_ignore > 0) THEN
3137 dbms_sql.column_value(l_cursor_id, 1, l_accomplish_date);
3138 dbms_sql.column_value(l_cursor_id, 2, l_org_id);
3139 IF l_accomplish_date IS NULL THEN
3140 -- create an exception
3141 l_reported_flag :='Y';
3142 l_exception_exists :='Y' ;
3143 l_record_category :='EXCEPTION';
3144 l_exception_category :='NO_PA_ACCOMPLISH_DATE';
3145 l_exception_section := 7;
3146 l_alc_code := p_def_alc_code;
3147 l_accomplish_date := l_txn_end_date ;
3148 Insert_new_transaction(l_amount, 1);
3149 END IF;
3150 ELSE
3151 x_name := 'No Project Accting Batch Name Found';
3152 Set_Exception('R');
3153 END IF;
3154 EXCEPTION
3155 WHEN others THEN
3156 error_buf :='PA Accomplish Date Error'||sqlerrm;
3157 error_code := g_FAILURE;
3158 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error7', error_buf) ;
3159 RETURN;
3160 END;
3161 END IF;
3162 END IF;
3163 ELSE -- accomplish date attribute in fv_system_parameters is null.
3164 l_accomplish_date := l_txn_end_date;
3165 END IF;
3166 l_record_type := 'Disbursement';
3167 ELSIF (l_je_source = 'Payables' AND l_je_category <> 'Treasury Confirmation') Then -- source1
3168 -- get the document number
3169 BEGIN
3170 SELECT invoice_num
3171 INTO l_document_number
3172 FROM ap_invoices_all
3173 WHERE invoice_id = TO_NUMBER(l_reference_2);
3174 EXCEPTION WHEN NO_DATA_FOUND THEN
3175 SELECT SUBSTR(name,1,50)
3176 INTO l_document_number
3177 FROM gl_je_headers
3178 WHERE je_header_id = l_je_header_id;
3179 END;
3180 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3181 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Document_Num => ' || l_document_number);
3182 END IF;
3183 IF l_reference_2 IS NULL Then
3184 x_name := 'Blank Invoice Id' ;
3185 Set_Exception('R');
3186 ELSE
3187 BEGIN
3188 SELECT 'Y', invoice_type_lookup_code
3189 INTO l_exists, g_invoice_type_lookup_code
3190 FROM ap_invoices_all
3191 WHERE invoice_id = to_number(l_REFERENCE_2) ;
3192 EXCEPTION
3193 when no_data_found then
3194 x_name := 'Invalid Invoice Id - ' || l_reference_2 ;
3195 Set_Exception('R');
3196 When INVALID_NUMBER OR VALUE_ERROR Then
3197 x_name := 'Error while converting to number - '
3198 || l_reference_2 ;
3199 Set_Exception('R');
3200 END;
3201 END IF;
3202 IF l_reference_3 IS NULL Then
3203 x_name := 'Blank Check Id' ;
3204 Set_Exception('R');
3205 ELSE
3206 BEGIN
3207 select 'Y', payment_type_flag, void_date
3208 into l_exists, g_payment_type_flag, g_check_void_date
3209 from ap_checks_all
3210 Where check_id = to_number(l_REFERENCE_3) ;
3211 EXCEPTION
3212 when no_data_found then
3213 x_name := 'Invalid Check Id - ' || l_reference_3 ;
3214 Set_Exception('R');
3215 When INVALID_NUMBER OR VALUE_ERROR Then
3216 x_name := 'Error while converting to number - '
3217 || l_reference_3 ;
3218 Set_Exception('R');
3219 END;
3220 END IF;
3221 IF l_reference_9 IS NULL Then
3222 x_name := 'Blank Invoice Payment Id' ;
3223 Set_Exception('R');
3224 ELSE
3225 BEGIN
3226 select 'Y'
3227 into l_exists
3228 from ap_invoice_payments_all
3229 Where invoice_payment_id = to_number(l_REFERENCE_9) ;
3230 EXCEPTION
3231 when no_data_found then
3232 x_name := 'Invalid Invoice Payment Id - '|| l_reference_9 ;
3233 Set_Exception('R');
3234 When INVALID_NUMBER OR VALUE_ERROR Then
3235 x_name := 'Error while converting to number - '|| l_reference_9 ;
3236 Set_Exception('R');
3237 END;
3238 END IF;
3239
3240 ELSIF (l_je_source = 'Receivables') THEN -- source1
3241 vl_misc_cd_flag := 'N';
3242 IF (NVL(l_je_from_sla_flag,'N') IN ('N', 'U')) THEN
3243 IF (l_je_category = 'Misc Receipts') THEN -- rec category
3244 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3245 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESSING A MISC RECEIPT');
3246 END IF;
3247 l_cash_receipt_id := l_reference_2;
3248 l_cash_receipt_hist_id := l_reference_5;
3249 ELSE -- rec category
3250 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3251 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESSING A TRADE RECEIPT OR OTHER');
3252 END IF;
3253 -- get the cash receipt id, and the cash receipt history id
3254 l_cash_receipt_id := SUBSTR(l_reference_2,0,INSTR(l_reference_2,'C')-1);
3255 l_cash_receipt_hist_id := SUBSTR(l_reference_2,INSTR(l_reference_2,'C')+1,LENGTH(l_reference_2));
3256 END IF;
3257 ELSE --l_je_from_sla_flag is 'Y'
3258 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3259 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESSING RECEIPT WHEN SOURCE IS SLA');
3260 END IF;
3261 l_cash_receipt_id := l_reference_2;
3262 l_cash_receipt_hist_id := l_reference_5;
3263 END IF;
3264 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3265 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASH RECEIPT ID = '
3266 ||TO_NUMBER(l_cash_receipt_id));
3267 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASH RECEIPT HISTORY ID = '
3268 ||TO_NUMBER(l_cash_receipt_hist_id));
3269 END IF;
3270 IF ((l_cash_receipt_id IS NULL) OR (l_cash_receipt_hist_id IS NULL)) THEN
3271 x_name := 'Blank Cash Receipt Id ' ;
3272 Set_Exception('R');
3273 ELSIF (l_cash_receipt_id IS NOT NULL) THEN
3274 BEGIN
3275 SELECT 'Y'
3276 INTO l_exists
3277 FROM Ar_Cash_Receipts_All
3278 WHERE cash_receipt_id = to_number(l_cash_receipt_id);
3279 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3280 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASH RECEIPT ID EXISTS');
3281 END IF;
3282 EXCEPTION
3283 WHEN no_data_found THEN
3284 x_name := 'Invalid Cash Receipt Id - '||TO_NUMBER(l_cash_receipt_id) ;
3285 Set_Exception('R');
3286 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
3287 x_name := 'Error while converting to number - '|| TO_NUMBER(l_cash_receipt_id) ;
3288 Set_Exception('R');
3289 END;
3290 BEGIN
3291 SELECT 'Y'
3292 INTO l_exists
3293 FROM Ar_Cash_Receipt_History_All
3294 WHERE cash_receipt_history_id = to_number(l_cash_receipt_hist_id)
3295 AND cash_receipt_id = TO_NUMBER(l_cash_receipt_id);
3296 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3297 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASH RECEIPT HIST ID EXISTS IN '||
3298 'Ar_Cash_Receipt_History_All.');
3299 END IF;
3300 EXCEPTION
3301 WHEN no_data_found THEN
3302 IF(l_je_category = 'Misc Receipts') THEN
3303 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3304 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOR MISC RECEIPT: '||
3305 'Cash Receipt Hist Id does not '||
3306 'exist in Ar_Cash_Receipt_History_All table.'||
3307 'Checking in Ar_Misc_Cash_Distributions_All table.');
3308 END IF;
3309 l_exists := 'M';
3310 ELSE
3311 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3312 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOR TRADE RECEIPT: '||
3313 'Cash Receipt Hist Id does not '||
3314 'exist in Ar_Cash_Receipt_History_All table.'||
3315 'Checking in Ar_Receivable_Applications_All table.');
3316 END IF;
3317 l_exists := 'C';
3318 END IF;
3319 WHEN INVALID_NUMBER OR VALUE_ERROR Then
3320 x_name := 'Error while converting to number - '
3321 || TO_NUMBER(l_cash_receipt_hist_id) ;
3322 Set_Exception('R');
3323 END;
3324 IF((l_je_category <> 'Misc Receipts') AND (l_exists = 'C')) THEN
3325 BEGIN
3326 SELECT cash_receipt_history_id
3327 INTO l_temp_cr_hist_id
3328 FROM Ar_Receivable_Applications_All
3329 WHERE receivable_application_id = TO_NUMBER(l_cash_receipt_hist_id);
3330 l_cash_receipt_hist_id := l_temp_cr_hist_id;
3331 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3332 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASH RECEIPT HIST ID EXISTS IN '||
3333 'Ar_Receivable_Applications_All table and is '||
3334 TO_NUMBER(l_cash_receipt_hist_id)||'.Checking in '||
3335 'Ar_Cash_Receipt_History_All to see if it is a valid '||
3336 'cash receipt hist id.');
3337 END IF;
3338 BEGIN
3339 SELECT 'Y'
3340 INTO l_exists
3341 FROM Ar_Cash_Receipt_History_All
3342 WHERE cash_receipt_history_id = TO_NUMBER(l_cash_receipt_hist_id)
3343 AND cash_receipt_id = TO_NUMBER(l_cash_receipt_id);
3344 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3345 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASH RECEIPT HIST ID EXISTS IN '||
3346 'the Ar_Cash_Receipt_History_All table.');
3347 END IF;
3348 EXCEPTION
3349 WHEN NO_DATA_FOUND THEN
3350 x_name := 'Invalid Cash Receipt History Id - '
3351 || TO_NUMBER(l_cash_receipt_hist_id) ;
3352 Set_Exception('R');
3353 WHEN INVALID_NUMBER OR VALUE_ERROR Then
3354 x_name := 'Error while converting to number - '
3355 || TO_NUMBER(l_cash_receipt_hist_id) ;
3356 Set_Exception('R');
3357 END;
3358 EXCEPTION
3359 WHEN NO_DATA_FOUND THEN
3360 x_name := 'Invalid Cash Receipt History Id - '
3361 || TO_NUMBER(l_cash_receipt_hist_id) ;
3362 Set_Exception('R');
3363 WHEN INVALID_NUMBER OR VALUE_ERROR Then
3364 x_name := 'Error while converting to number - '
3365 || TO_NUMBER(l_cash_receipt_hist_id) ;
3366 Set_Exception('R');
3367 END;
3368 ELSIF ((l_je_category = 'Misc Receipts') AND (l_exists = 'M')) THEN
3369 BEGIN
3370 SELECT 'Y'
3371 INTO l_exists
3372 FROM Ar_Misc_Cash_Distributions_All
3373 WHERE misc_cash_distribution_id = TO_NUMBER(l_cash_receipt_hist_id)
3374 AND cash_receipt_id = TO_NUMBER(l_cash_receipt_id);
3375 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3376 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'MISC CASH DISTRIBUTION ID EXISTS IN '||
3377 'Ar_Misc_Cash_Distributions_All table and is '|| TO_NUMBER(l_cash_receipt_hist_id));
3378 END IF;
3379 vl_misc_cd_flag := 'Y';
3380 EXCEPTION
3381 WHEN NO_DATA_FOUND THEN
3382 x_name := 'Invalid Misc Cash Distribution Id - '|| TO_NUMBER(l_cash_receipt_hist_id) ;
3383 Set_Exception('R');
3384 WHEN INVALID_NUMBER OR VALUE_ERROR Then
3385 x_name := 'Error while converting to number - '|| TO_NUMBER(l_cash_receipt_hist_id) ;
3386 Set_Exception('R');
3387 END;
3388 END IF; --l_je_category and l_exists
3389 END IF ; --l_cash_receipt_id is not null
3390 -- get the document number
3391 BEGIN
3392 SELECT receipt_number
3393 INTO l_document_number
3394 FROM ar_cash_receipts_all
3395 WHERE cash_receipt_id = TO_NUMBER(l_cash_receipt_id);
3396 EXCEPTION WHEN NO_DATA_FOUND THEN
3397 SELECT SUBSTR(name,1,50)
3398 INTO l_document_number
3399 FROM gl_je_headers
3400 WHERE je_header_id = l_je_header_id;
3401 END;
3402
3403 ELSIF(l_je_category = 'Treasury Confirmation' AND
3404 l_name LIKE '%VOID%') THEN -- source1
3405 -- get the document number
3406 BEGIN
3407 SELECT checkrun_name
3408 INTO l_document_number
3409 FROM ap_checks_all
3410 WHERE check_id = to_number(l_reference_3);
3411 EXCEPTION
3412 WHEN NO_DATA_FOUND THEN
3413 SELECT SUBSTR(name,1,50)
3414 INTO l_document_number
3415 FROM gl_je_headers
3416 WHERE je_header_id = l_je_header_id;
3417 END;
3418 IF l_reference_3 IS NULL then
3419 x_name := 'Blank Check Id ' ;
3420 Set_Exception('R');
3421 ELSE
3422 BEGIN
3423 select 'Y'
3424 into l_exists
3425 from AP_CHECKS_ALL
3426 Where check_id = to_number(l_reference_3);
3427 Exception
3428 when no_data_found then
3429 x_name := 'Invalid Check Id - ' || l_reference_3 ;
3430 Set_Exception('R');
3431 When INVALID_NUMBER OR VALUE_ERROR Then
3432 x_name := 'Error while converting to number - '|| l_reference_3 ;
3433 Set_Exception('R');
3434 End ;
3435 End If ;
3436 If l_reference_4 IS NULL then
3437 x_name := 'Blank Invoice Id' ;
3438 Set_Exception('R');
3439 Else
3440 Begin
3441 select 'Y'
3442 into l_exists
3443 from AP_INVOICES_ALL
3444 Where invoice_id = to_number(l_reference_4);
3445 Exception
3446 when no_data_found then
3447 x_name := 'Invalid Invoice Id - ' || l_reference_4 ;
3448 Set_Exception('R');
3449 When INVALID_NUMBER OR VALUE_ERROR Then
3450 x_name := 'Error while converting to number - '|| l_reference_4 ;
3451 Set_Exception('R');
3452 End ;
3453 End If ;
3454 ELSE -- source1
3455 -- In case of Manual Transaction, the alc_code will be the default alc.
3456 x_name := l_name;
3457 l_name := 'MANUAL';
3458 SELECT SUBSTR(name,1,50)
3459 INTO l_document_number
3460 FROM gl_je_headers
3461 WHERE je_header_id = l_je_header_id;
3462 END IF; -- source1
3463
3464 ---------------------------------------------------------------------------
3465 -- Re-assign the batch name. All batches start as MANUAL
3466 -- THEN get overwritten.
3467 ---------------------------------------------------------------------------
3468 x_name := l_name;
3469 -- Initialising the bank_acct_id
3470 vg_bank_acct_id := NULL;
3471 IF (x_name <> 'MANUAL') THEN -- manual
3472 IF (l_je_source = 'Receivables' ) THEN -- source2
3473 l_exists := 'N';
3474 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3475 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3476 'GETTING THE ACCOMPLISH DATE FOR RECEIPTS');
3477 END IF;
3478 -- Check IF the cash receipt is a reversal
3479 IF ((l_je_category = 'Misc Receipts') AND (vl_misc_cd_flag = 'Y')) THEN -- rev check
3480 BEGIN
3481 SELECT 'Y'
3482 INTO l_exists
3483 FROM Ar_Misc_Cash_Distributions_All
3484 WHERE misc_cash_distribution_id = l_cash_receipt_hist_id
3485 AND cash_receipt_id = l_cash_receipt_id
3486 AND created_from = 'ARP_REVERSE_RECEIPT.REVERSE';
3487 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3488 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3489 'MISC CASH DIST ID HAS REVERSE VALUE IN '||'created from column....it is a reversal');
3490 END IF;
3491 EXCEPTION
3492 WHEN NO_DATA_FOUND THEN
3493 l_exists := 'N';
3494 WHEN OTHERS THEN
3495 error_buf := SQLERRM||'- Error while deriving the reversal status'
3496 ||' for the misc cash dist id '||l_cash_receipt_hist_id;
3497 error_code := 2;
3498 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error10', error_buf) ;
3499 RETURN;
3500 END;
3501 ELSE -- rev check
3502 BEGIN
3503 SELECT 'Y'
3504 INTO l_exists
3505 FROM Ar_Cash_Receipt_History_All
3506 WHERE cash_receipt_history_id = l_cash_receipt_hist_id
3507 AND cash_receipt_id = l_cash_receipt_id
3508 AND status='REVERSED';
3509 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3510 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3511 'REVERSAL CASH RECEIPT HIST ID EXISTS..it is a reversal');
3512 END IF;
3513 EXCEPTION
3514 WHEN NO_DATA_FOUND THEN
3515 l_exists := 'N';
3516 WHEN OTHERS THEN
3517 error_buf := SQLERRM||'- Error while deriving the status'
3518 ||' for the cash receipt hist id '||l_cash_receipt_hist_id;
3519 error_code := 2;
3520 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error11', error_buf) ;
3521 RETURN;
3522 END;
3523 END IF; -- rev check
3524 BEGIN
3525 SELECT DECODE(l_exists,'Y',reversal_date,receipt_date)
3526 INTO l_accomplish_date
3527 FROM ar_cash_receipts_all
3528 WHERE cash_receipt_id = l_cash_receipt_id;
3529 EXCEPTION
3530 WHEN OTHERS THEN
3531 error_buf := SQLERRM|| '- Error while deriving the accomplish date'
3532 ||' for the cash receipt id '||l_cash_receipt_id;
3533 error_code := 1 ;
3534 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error12', error_buf) ;
3535 RETURN;
3536 END;
3537 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3538 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3539 'ACCOMPLISH DATE IS '||TO_CHAR(L_ACCOMPLISH_DATE, 'MM/DD/YYYY'));
3540 END IF;
3541 BEGIN
3542 SELECT remit_bank_acct_use_id
3543 INTO vg_bank_acct_id
3544 FROM Ar_Cash_Receipts_All
3545 WHERE cash_receipt_id = TO_NUMBER(l_cash_receipt_id);
3546 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3547 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOUND THE BANK ACCOUNT ID');
3548 END IF;
3549 -- Get the agency location code
3550 Get_Alc
3551 (
3552 p_bank_acct_id => vg_bank_acct_id,
3553 p_alc_code => l_alc_code,
3554 p_error_code => error_code,
3555 p_error_desc => error_buf
3556 );
3557 EXCEPTION
3558 WHEN NO_DATA_FOUND THEN
3559 -- Unable to find the remittance_bank_account_id
3560 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3561 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3562 'UNABLE TO FIND THE BANK ACCT ID');
3563 END IF;
3564 Set_Exception('D');
3565 WHEN OTHERS THEN
3566 error_buf := SQLERRM||'- Error while deriving the agency_location_code'
3567 ||' for the cash receipt id '||l_cash_receipt_id;
3568 error_code := 2;
3569 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error13', error_buf) ;
3570 RETURN;
3571 END;
3572 --- Check for Interagency funds
3573 l_ia_flag := 'N';
3574 l_record_type := 'Receipt';
3575 BEGIN /* B1 */
3576 SELECT 'x'
3577 INTO l_dummy
3578 FROM fv_interagency_funds_all
3579 WHERE cash_receipt_id = l_cash_receipt_id;
3580 l_ia_flag := 'Y';
3581 l_update_type := 'RECEIPT';
3582 EXCEPTION
3583 WHEN no_data_found THEN
3584 l_ia_flag := 'N';
3585 WHEN too_many_rows THEN
3586 error_buf := 'Too many rows in interagency select';
3587 error_code := g_FAILURE;
3588 RETURN;
3589 END ; /* B1 */
3590 IF (l_ia_flag = 'N' AND l_ar_reclass_attribute IS NOT NULL
3591 AND l_cash_receipt_id IS NOT NULL) THEN
3592 BEGIN
3593 sql_stmt1:= 'SELECT ACR.' || l_ar_reclass_attribute ||
3594 ' FROM AR_CASH_RECEIPTS_ALL ACR WHERE ACR.cash_receipt_id = '
3595 || l_cash_receipt_id;
3596 l_reclass_receipt_number := NULL;
3597 EXECUTE IMMEDIATE sql_stmt1 INTO l_reclass_receipt_number ;
3598 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3599 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
3600 'Reclass Receipt Number: '|| l_reclass_receipt_number );
3601 END IF;
3602 EXCEPTION
3603 WHEN NO_DATA_FOUND THEN
3604 l_reclass_receipt_number:=NULL;
3605 END;
3606 IF (l_reclass_receipt_number IS NOT NULL) THEN
3607 BEGIN /* B1 */
3608 SELECT 'x'
3609 INTO l_dummy
3610 FROM fv_interagency_funds_all a
3611 WHERE cash_receipt_id = (SELECT cash_receipt_id
3612 FROM ar_cash_receipts b
3613 WHERE b.receipt_number = l_reclass_receipt_number);
3614 l_ia_flag := 'Y';
3615 l_txn_category := 'I';
3616 l_update_type := 'RECEIPT';
3617 EXCEPTION
3618 WHEN NO_DATA_FOUND THEN
3619 l_ia_flag := 'N';
3620 WHEN TOO_MANY_ROWS THEN
3621 error_buf := 'Too many rows in interagency select';
3622 error_code := -1;
3623 RETURN;
3624 END ; /* B1 */
3625 END IF;
3626 END IF;
3627 -- Check for Refunded invoice
3628 --begin /* Receivable Refund */
3629 OPEN C2 (l_cash_receipt_id, l_fund_code);
3630 IF (sqlcode < 0) THEN
3631 error_code := sqlcode ;
3632 error_buf := sqlerrm ;
3633 RETURN ;
3634 END IF;
3635 LOOP
3636 FETCH C2
3637 INTO l_obligation_date,
3638 l_inv_amount ;
3639 EXIT WHEN C2%NOTFOUND;
3640 vl_refund_cnt := 0;
3641 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3642 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3643 'BATCH_ID,HEADER_ID,FUND,REF2 ='||L_BATCH_ID||','||
3644 l_je_header_id||','||l_fund_code||','||l_reference_2);
3645 END IF;
3646 OPEN Get_Count_Csr
3647 (
3648 l_batch_id,
3649 l_je_header_id,
3650 l_je_category,
3651 l_cash_receipt_id,
3652 l_fund_code,
3653 l_je_from_sla_flag
3654 );
3655 FETCH Get_Count_Csr INTO vl_refund_cnt;
3656 CLOSE Get_Count_Csr;
3657 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3658 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3659 'THE # OF RECORDS IN FV_SF224_TEMP '||VL_REFUND_CNT);
3660 END IF;
3661 IF (vl_refund_cnt = 0) THEN
3662 l_record_type := 'Receipt_refund';
3663 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3664 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3665 'RECORD_TYPE AFTER REFUNDS CHECK IS '||L_RECORD_TYPE);
3666 END IF;
3667 l_type := 'AP_REFUND';
3668 l_update_type := 'RECEIPT';
3669 l_processed_flag := 'Y';
3670 --l_record_type := 'Receipt_refund';
3671 x_name := 'Refunds_and_Voids';
3672 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3673 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3674 'BEFORE SET_TRANSACTION_TYPE');
3675 END IF;
3676 set_transaction_type;
3677 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3678 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3679 'AFTER SET_TRANSACTION_TYPE');
3680 END IF;
3681 -- Added for WAPA bug fix.
3682 -- Bug 1013752
3683 l_reported_flag := 'N';
3684 IF(l_reported_month like 'CURRENT%') THEN
3685 l_reported_flag := 'Y' ;
3686 END IF;
3687 l_exception_exists := NULL ;
3688 l_record_category := 'CREATED' ;
3689 l_billing_agency_fund := l_fund_code;
3690 -- Insert the exception transaction
3691 insert_new_transaction (l_inv_amount, l_sign_number);
3692 l_org_amount := l_org_amount - l_inv_amount;
3693 ELSE
3694 l_record_type := 'Receipt';
3695 END IF;
3696 END LOOP;
3697 CLOSE C2;
3698 IF l_processed_flag = 'Y' THEN
3699 l_amount := l_org_amount;
3700 END IF;
3701 l_record_type := 'Receipt';
3702 ELSIF (l_je_category = 'Treasury Confirmation' AND
3703 ((NVL(l_je_from_sla_flag,'N') IN ('N', 'U') AND upper(l_name) like '%TREASURY%') OR
3704 (NVL(l_je_from_sla_flag,'N')='Y' AND upper(l_name) like '%BUDGET EXECUTION%'))) then -- source2
3705
3706 IF l_reference_1 IS NULL THEN -- ref1
3707 -- IF the code is executing this IF stmnt, it means that ref6
3708 -- is not null. IF ref1 is null and ref6 is null, it would have
3709 -- been a Manual entry which is taken care of in the first IF stmnt.
3710 -- Hence accomplish date would be ref6.
3711 l_accomplish_date := l_reference_6;
3712 IF l_reference_3 IS NULL THEN -- ref3-0
3713 -- Case when ref1 is null, ref3 is null and ref6 is not null
3714 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3715 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
3716 'CASE:REF1,REF3 ARE NULL; REF6 IS NOT NULL');
3717 END IF;
3718 Set_Exception('D');
3719 GOTO end_label;
3720 ELSE -- ref3-0
3721 -- Case when ref1 is null,ref3 and ref6 are not null
3722 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3723 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3724 'CASE:REF1 IS NULL, REF3 AND REF6 ARE NOT NULL');
3725 END IF;
3726 BEGIN
3727 SELECT ce_bank_acct_use_id
3728 INTO vg_bank_acct_id
3729 FROM Ap_Checks_All
3730 WHERE check_id = TO_NUMBER(l_reference_3);
3731 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3732 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOUND THE BANK ACCOUNT ID');
3733 END IF;
3734 EXCEPTION
3735 WHEN NO_DATA_FOUND THEN
3736 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3737 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3738 'UNABLE TO FIND THE BANK ACCOUNT ID');
3739 END IF;
3740 -- Unable to find the bank_account_id
3741 Set_Exception('D');
3742 GOTO end_label;
3743 WHEN OTHERS THEN
3744 error_buf := SQLERRM||'- Error while deriving the '||
3745 'bank_account_id from Ap_Checks_All table.';
3746 error_code := 2;
3747 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error14', error_buf) ;
3748 RETURN;
3749 END;
3750 END IF; -- ref3-0
3751 ELSE -- ref1
3752 -- Case when ref1 is not null
3753 IF l_reference_3 IS NULL THEN -- ref3
3754 IF l_reference_6 IS NULL THEN -- ref6-1
3755 -- Case when ref1 is not null,ref3 and ref6 are null
3756 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3757 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3758 'CASE:REF1 IS NOT NULL;REF3 AND REF6 ARE NULL');
3759 END IF;
3760 BEGIN
3761 SELECT checkrun_name,treasury_doc_date,org_id
3762 INTO vl_checkrun_name, l_accomplish_date, l_org_id
3763 FROM Fv_Treasury_Confirmations_All
3764 WHERE treasury_confirmation_id = TO_NUMBER(l_reference_1);
3765 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3766 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3767 'FOUND THE CHECKRUN_NAME,DOC_DATE,ORG_ID');
3768 END IF;
3769 EXCEPTION
3770 WHEN OTHERS THEN
3771 error_buf := SQLERRM||'- Error while deriving the '||
3772 'checkrun_name,treasury_doc_date from '||
3773 'Fv_Treasury_Confirmations_All table.';
3774 error_code := 2;
3775 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error15', error_buf) ;
3776 RETURN;
3777 END;
3778 ELSE -- ref6-1
3779 -- Case when ref1 is not null,ref3 is null, and ref6 is not null
3780 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3781 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3782 'CASE:REF1 AND REF6 ARE NOT NULL;REF3 IS NULL');
3783 END IF;
3784 l_accomplish_date := l_reference_6;
3785 BEGIN
3786 SELECT checkrun_name,org_id
3787 INTO vl_checkrun_name,l_org_id
3788 FROM Fv_Treasury_Confirmations_All
3789 WHERE treasury_confirmation_id = TO_NUMBER(l_reference_1);
3790 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3791 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3792 'FOUND THE CHECKRUN_NAME,ORG_ID');
3793 END IF;
3794 EXCEPTION
3795 WHEN OTHERS THEN
3796 error_buf := SQLERRM||'- Error while deriving the '||
3797 'checkrun_name from Fv_Treasury_Confirmations_All table.';
3798 error_code := 2;
3799 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error16', error_buf);
3800 RETURN;
3801 END;
3802 END IF; -- ref6-1
3803 IF (vl_checkrun_name IS NOT NULL) THEN -- vl_checkrun
3804 BEGIN
3805 SELECT ce_bank_acct_use_id
3806 INTO vg_bank_acct_id
3807 FROM Ap_Inv_Selection_Criteria_All
3808 WHERE checkrun_name = vl_checkrun_name
3809 AND org_id = l_org_id;
3810 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3811 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3812 'FOUND THE BANK ACCOUNT ID.');
3813 END IF;
3814 EXCEPTION
3815 WHEN NO_DATA_FOUND THEN
3816 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3817 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3818 'UNABLE TO FIND BANK ACCOUNT ID');
3819 END IF;
3820 -- Unable to find the bank_account_id
3821 Set_Exception('D');
3822 GOTO end_label;
3823 WHEN OTHERS THEN
3824 error_buf := SQLERRM||'- Error while deriving the '||
3825 'bank_account_id from Ap_Inv_Selection_Criteria_All table.';
3826 error_code := 2;
3827 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error17', error_buf) ;
3828 RETURN;
3829 END;
3830 END IF; -- vl_checkrun
3831 ELSIF l_reference_3 IS NOT NULL THEN -- ref3
3832 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3833 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASE WHEN REF3 IS NOT NULL');
3834 END IF;
3835 IF l_reference_6 IS NULL THEN -- ref6-2
3836 -- Case when ref1 and ref3 is not null, and ref6 is null
3837 -- In this scenario, we determine the accomplish date,
3838 -- based on the reference_1, and alc based on reference_3.
3839 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3840 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3841 'CASE:REF1 AND REF3 ARE NOT NULL;REF6 IS NULL');
3842 END IF;
3843 BEGIN
3844 SELECT treasury_doc_date
3845 INTO l_accomplish_date
3846 FROM Fv_Treasury_Confirmations_All
3847 WHERE treasury_confirmation_id = TO_NUMBER(l_reference_1);
3848 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3849 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOUND TREASURY_DOC_DATE');
3850 END IF;
3851 EXCEPTION
3852 WHEN OTHERS THEN
3853 error_buf := SQLERRM||'- Error while deriving the '||
3854 'treasury_doc_date from Fv_Treasury_Confirmations_All table.';
3855 error_code := 2;
3856 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error18', error_buf) ;
3857 RETURN;
3858 END;
3859 ELSE -- ref6-2
3860 -- Case when ref3 is not null, and ref6 is not null
3861 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3862 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3863 'CASE:REF1,REF3 AND REF6 ARE NOT NULL');
3864 END IF;
3865 l_accomplish_date := l_reference_6;
3866 END IF; -- ref6-2
3867 BEGIN
3868 SELECT ce_bank_acct_use_id
3869 INTO vg_bank_acct_id
3870 FROM Ap_Checks_All
3871 WHERE check_id = TO_NUMBER(l_reference_3);
3872 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3873 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOUND THE BANK ACCOUNT ID');
3874 END IF;
3875 EXCEPTION
3876 WHEN NO_DATA_FOUND THEN
3877 -- Unable to find the bank_account_id
3878 Set_Exception('D');
3879 GOTO end_label;
3880 WHEN OTHERS THEN
3881 error_buf := SQLERRM||'- Error while deriving the '||
3882 'bank_account_id from Ap_Checks_All table.';
3883 error_code := 2;
3884 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error19', error_buf) ;
3885 RETURN;
3886 END;
3887 END IF; -- ref3
3888 END IF; -- ref1
3889 -- Determine the Alc for the bank_account_id found above.
3890 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3891 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'GETTING THE ALC..');
3892 END IF;
3893 Get_Alc
3894 (
3895 p_bank_acct_id => vg_bank_acct_id,
3896 p_alc_code => l_alc_code,
3897 p_error_code => error_code,
3898 p_error_desc => error_buf
3899 );
3900 l_record_type := 'Disbursement';
3901 <<end_label>>
3902 l_record_type := 'Disbursement';
3903 ELSIF (l_je_source = 'Payables' AND l_je_category <> 'Treasury Confirmation') THEN -- source2
3904 BEGIN
3905 SELECT Distinct org_id
3906 INTO l_org_id
3907 FROM ap_invoice_payments_all
3908 WHERE invoice_id = to_number(l_reference_2) ;
3909 EXCEPTION
3910 WHEN OTHERS THEN
3911 error_code := 2;
3912 error_buf := SQLERRM||'--Error while deriving the org_id, in the '||
3913 'procedure Process_Sf224_Transactions.';
3914 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error20', error_buf) ;
3915 END;
3916 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3917 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
3918 'ORG ID OF THE TXN IS '||TO_CHAR(L_ORG_ID));
3919 END IF;
3920 IF l_org_id IS NULL THEN
3921 l_payables_ia_paygroup := p_def_p_ia_paygroup ;
3922 ELSE
3923 BEGIN
3924 SELECT payables_ia_paygroup
3925 INTO l_payables_ia_paygroup
3926 FROM fv_operating_units_all
3927 WHERE org_id = l_org_id;
3928 EXCEPTION
3929 WHEN OTHERS THEN
3930 error_code := 2;
3931 error_buf := SQLERRM ||'--Error while deriving the '||
3932 'payables_ia_paygroup in the procedure Process_Sf224_Transactions';
3933 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error21', error_buf) ;
3934 END;
3935 END IF ;
3936 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3937 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3938 'PAYBLES PAY GROUP IS '||L_PAYABLES_IA_PAYGROUP);
3939 END IF;
3940 l_record_type := 'Disbursement';
3941 BEGIN /* to process DIT payments */
3942 l_Error_stage := 0;
3943 l_inv_amount := 0;
3944 l_reference := To_Number(l_reference_2) ;
3945 BEGIN
3946 SELECT api.invoice_id,
3947 api.vendor_id,
3948 api.invoice_amount,
3949 nvl(apc.treasury_pay_date,apc.check_date)
3950 INTO l_invoice_id ,
3951 l_vendor_id,
3952 l_inv_amount,
3953 l_check_date
3954 FROM ap_checks_all apc,
3955 ap_invoices_all api
3956 WHERE api.invoice_id = NVL(l_reference, 0)
3957 AND apc.check_id = to_number(l_reference_3)
3958 AND l_payables_ia_paygroup = api.pay_group_lookup_code
3959 AND apc.payment_method_lookup_code = 'CLEARING' ;
3960 l_ia_flag := 'Y';
3961 EXCEPTION
3962 WHEN too_many_rows THEN
3963 error_buf := 'Too many rows in invoice info,dit select';
3964 error_code := g_FAILURE;
3965 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error22', error_buf) ;
3966 RETURN;
3967 WHEN No_Data_Found THEN
3968 l_ia_flag := 'N' ;
3969 END;
3970 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3971 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3972 'VENDOR ID,INVOICE AMT,CHECK DATE ARE: '||
3973 TO_CHAR(l_vendor_id)||' '||TO_CHAR(l_inv_amount)||' '||
3974 TO_CHAR(l_check_date, 'MM/DD/YYYY'));
3975 END IF;
3976 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3977 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'INTERAGENCY FLAG IS '||L_IA_FLAG);
3978 END IF;
3979 l_error_stage := 1;
3980 BEGIN
3981 SELECT ce_bank_acct_use_id
3982 INTO vg_bank_acct_id
3983 FROM Ap_Checks_All
3984 WHERE check_id = TO_NUMBER(l_reference_3);
3985 EXCEPTION
3986 WHEN NO_DATA_FOUND THEN
3987 -- Unable to find the bank_account_id
3988 Set_Exception('D');
3989 WHEN OTHERS THEN
3990 error_buf := SQLERRM||'- Error while deriving the '||
3991 'bank_account_id from Ap_Checks_All table when '||
3992 'category<>Trea Conf.';
3993 error_code := 2;
3994 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error24', error_buf) ;
3995 RETURN;
3996 END;
3997 -- Determine the Alc for the bank_account_id found above.
3998 Get_Alc
3999 (
4000 p_bank_acct_id => vg_bank_acct_id,
4001 p_alc_code => l_alc_code,
4002 p_error_code => error_code,
4003 p_error_desc => error_buf
4004 );
4005 IF l_ia_flag = 'Y' THEN
4006 BEGIN
4007 SELECT chargeback_flag, iaf.billing_agency_fund
4008 INTO l_cb_flag, l_billing_agency_fund
4009 FROM fv_interagency_funds_all iaf
4010 WHERE iaf.vendor_id = l_vendor_id
4011 AND iaf.invoice_id = l_invoice_id ;
4012 EXCEPTION
4013 WHEN too_many_rows THEN
4014 error_buf := 'Too many rows in chargeback flag Prelim select';
4015 error_code := g_FAILURE;
4016 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error23', error_buf) ;
4017 RETURN;
4018 END;
4019 END IF ;
4020 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4021 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4022 'CHARGEBACK FLAG AND BILLING AGENCY FUND ARE :'
4023 || L_CB_FLAG ||' '|| L_BILLING_AGENCY_FUND);
4024 END IF;
4025 BEGIN /* Void Date */
4026 SELECT nvl(apc.treasury_pay_date,apc.check_date),
4027 apc.void_date
4028 INTO l_accomplish_date,
4029 l_void_date
4030 FROM ap_checks_all apc,
4031 ap_invoices_all api
4032 WHERE api.invoice_id = Nvl(l_reference, 0)
4033 AND apc.check_id = nvl(l_reference_3,0);
4034 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4035 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4036 'CHECK DATE/ACCOM DATE AND VOID DATE ARE '||
4037 TO_CHAR(l_accomplish_date, 'MM/DD/YYYY')||' '||
4038 TO_CHAR(l_void_date, 'MM/DD/YYYY'));
4039 END IF;
4040 BEGIN /* VOID */
4041 l_inv_pay_id := 0;
4042 IF (l_void_date IS NOT NULL AND NVL(g_payment_type_flag,'Q') <> 'R') THEN
4043 SELECT NVL(MAX(invoice_payment_id),0)
4044 INTO l_inv_pay_id
4045 FROM ap_invoice_payments
4046 WHERE invoice_id = NVL(l_reference, 0)
4047 AND check_id = NVL(l_reference_3,0)
4048 AND invoice_payment_id >l_reference_9;
4049 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4050 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4051 'VOID DATE IS NOT NULL AND INVOICE '||
4052 'payment id is '||TO_CHAR(l_inv_pay_id));
4053 END IF;
4054 IF (l_inv_pay_id = 0) THEN
4055 l_accomplish_date := l_void_date ;
4056 l_record_type := 'VOID';
4057 BEGIN /* V1 */
4058 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4059 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4060 'Before Getting the Obligation Date');
4061 END IF;
4062 l_obligation_date := get_void_check_obligation_date
4063 ( TO_NUMBER(l_reference_2),
4064 TO_NUMBER(l_reference_3),
4065 TO_NUMBER(l_reference_8)
4066 );
4067 l_record_type := 'VOID';
4068 l_processed_flag := 'Y';
4069 l_update_type := 'VOID_PAYABLE';
4070 l_type := 'VOID';
4071 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4072 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4073 'OBLIGATION DATE IS '||TO_CHAR(l_obligation_date, 'MM/DD/YYYY'));
4074 END IF;
4075 EXCEPTION
4076 WHEN No_Data_Found THEN
4077 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4078 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4079 'NO DATE FUND Exception while getting the Obligation Date');
4080 END IF;
4081 error_buf := 'No Data Fund Exception while getting the Obligation Date for VOID';
4082 error_code := g_FAILURE;
4083 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error25', error_buf);
4084 RETURN;
4085 WHEN too_many_rows THEN
4086 error_buf := 'Too many rows in obligation_date select';
4087 error_code := g_FAILURE;
4088 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error25', error_buf);
4089 RETURN;
4090 END ; /* V1 */
4091 END IF;
4092 END IF;
4093 END ; /* VOID */
4094 EXCEPTION
4095 WHEN too_many_rows THEN
4096 error_buf := 'Too many rows in void_date disbursement select';
4097 error_code := g_FAILURE;
4098 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error26', error_buf) ;
4099 RETURN;
4100 WHEN no_data_found THEN
4101 NULL;
4102 WHEN OTHERS THEN
4103 error_Buf := sqlerrm ;
4104 error_Code := g_FAILURE ;
4105 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error27', error_buf) ;
4106 ROLLBACK ;
4107 RETURN;
4108 END ; /* Void Date */
4109 IF (l_ia_flag = 'Y' AND l_error_stage <> g_FAILURE) THEN
4110 IF(l_cb_flag = 'Y') THEN
4111 /* charge back flag */
4112 l_record_type := 'Receipt';
4113 ELSE
4114 IF(l_billing_agency_fund IS NOT NULL) THEN
4115 vl_treasury_symbol := l_treasury_symbol;
4116 l_treasury_symbol := l_billing_agency_fund;
4117 x_name := 'Inter agency funds';
4118 set_transaction_type;
4119 l_column_group := 30;
4120 l_txn_category := 'I';
4121 l_reported_flag:= 'N';
4122 IF(l_reported_month like 'CURRENT%') THEN
4123 l_reported_flag := 'Y' ;
4124 END IF;
4125 l_exception_exists := NULL ;
4126 l_record_category := 'CREATED' ;
4127 -- Insert the exception transaction
4128 insert_new_transaction(l_org_amount, 1);
4129 END IF;
4130 IF(l_billing_agency_fund = vl_treasury_symbol) THEN
4131 l_billing_agency_fund := 'DUPLICATE';
4132 l_exception_category := 'PAYABLES_DUPLICATE_FUND';
4133 l_exception_section := 5;
4134 l_column_group := 0;
4135 l_reported_month := NULL ;
4136 l_obligation_date := NULL ;
4137 l_accomplish_date := NULL ;
4138 l_reported_flag := 'Y' ;
4139 l_exception_exists := 'Y' ;
4140 l_record_category := 'EXCEPTION' ;
4141 -- Insert the exception transaction
4142 insert_new_transaction(l_amount, 1);
4143 l_accomplish_date := l_check_date ;
4144 END IF;
4145 END IF; /* charge back flag = 'Y' */
4146 END IF;
4147 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4148 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4149 'RECORD TYPE IS '||L_RECORD_TYPE);
4150 END IF;
4151 EXCEPTION
4152 WHEN no_data_found THEN
4153 IF (l_error_stage = 1) THEN
4154 l_billing_agency_fund := 'UNDEFINED';
4155 l_exception_category := 'PAYABLES_MISSING_IAF';
4156 l_exception_section := 4;
4157 l_treasury_symbol := 'UNDEFINED';
4158 l_column_group := 0;
4159 l_reported_flag := 'N' ;
4160 l_exception_exists := 'Y' ;
4161 l_record_category := 'EXCEPTION' ;
4162 -- Insert the exception transaction
4163 insert_new_transaction(l_amount, 1);
4164 END IF;
4165 WHEN others THEN
4166 error_buf := sqlerrm;
4167 error_code := g_FAILURE;
4168 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error28', error_buf) ;
4169 ROLLBACK;
4170 RETURN;
4171 END ; /* End proces DIT */
4172 ELSIF (l_je_category = 'Treasury Confirmation' AND
4173 upper(x_name) LIKE '%VOID%') THEN -- source2
4174 l_record_type := 'VOID';
4175 BEGIN
4176 -- check_id
4177 SELECT void_date
4178 INTO l_accomplish_date
4179 FROM ap_checks_all
4180 WHERE check_id = To_number (nvl(l_reference_3,'0')) ;
4181 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4182 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4183 'VOID DATE/ACCOMPLISH DATE IS '||
4184 TO_CHAR(l_accomplish_date, 'MM/DD/YYYY'));
4185 END IF;
4186 -- invoice_id
4187 SELECT invoice_date
4188 INTO l_invoice_date
4189 FROM ap_invoices_all
4190 WHERE Invoice_id = to_number (nvl(l_reference_4,'0')) ;
4191 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4192 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'INVOICE DATE IS '||
4193 TO_CHAR(l_invoice_date, 'MM/DD/YYYY'));
4194 END IF;
4195 EXCEPTION
4196 WHEN no_data_found THEN
4197 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4198 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4199 'UNABLE TO FIND EITHER THE VOID '||
4200 'date or invoice date');
4201 END IF;
4202 l_billing_agency_fund := 'UNDEFINED';
4203 l_exception_category := 'VOID_INCOMPLETE';
4204 l_treasury_symbol := 'UNDEFINED';
4205 l_column_group := 0;
4206 l_reported_flag := 'N' ;
4207 l_exception_exists := 'Y' ;
4208 l_record_category := 'EXCEPTION' ;
4209 Insert_New_Transaction(l_org_amount, 1);
4210 END ;
4211 BEGIN
4212 SELECT ce_bank_acct_use_id
4213 INTO vg_bank_acct_id
4214 FROM Ap_Checks_All
4215 WHERE check_id = TO_NUMBER(l_reference_3);
4216 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4217 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOUND THE BANK ACCT ID');
4218 END IF;
4219 EXCEPTION
4220 WHEN NO_DATA_FOUND THEN
4221 -- Unable to find the bank_account_id
4222 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4223 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4224 'UNABLE TO FIND THE BANK ACCT ID');
4225 END IF;
4226 Set_Exception('D');
4227 WHEN OTHERS THEN
4228 error_buf := SQLERRM||'- Error while deriving the '||
4229 'bank_account_id from Ap_Checks_All table when name like VOID.';
4230 error_code := 2;
4231 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error29', error_buf) ;
4232 RETURN;
4233 END;
4234 -- Determine the Alc for the bank_account_id found above.
4235 Get_Alc
4236 (
4237 p_bank_acct_id => vg_bank_acct_id,
4238 p_alc_code => l_alc_code,
4239 p_error_code => error_code,
4240 p_error_desc => error_buf
4241 );
4242 /* Reassigning l_reference_4 to l_reference_2. This is because
4243 the process is saving invoice_id in reference_2 column. */
4244 l_reference_2 := l_reference_4;
4245 BEGIN /* V1 */
4246 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4247 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4248 'Before Calling the get_void_check_obligation_date Procedure ... ');
4249 END IF;
4250 l_obligation_date :=get_void_check_obligation_date
4251 ( TO_NUMBER(l_reference_2),
4252 TO_NUMBER(l_reference_3),
4253 TO_NUMBER(l_reference_8)
4254 );
4255 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4256 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4257 'After Calling the get_void_check_obligation_date Procedure l_obligation_date ');
4258 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'OBLIGATION DATE IS '||
4259 TO_CHAR(l_obligation_date, 'MM/DD/YYYY'));
4260 END IF;
4261 l_record_type := 'VOID';
4262 l_processed_flag := 'Y';
4263 l_update_type := 'VOID_PAYABLE';
4264 l_type := 'VOID';
4265 EXCEPTION
4266 WHEN No_Data_Found THEN
4267 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4268 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4269 'UNABLE TO FIND OBLIGATION DATE');
4270 END IF;
4271 error_buf := 'No Data Fund Exception while getting the Obligation Date for VOID';
4272 error_code := g_FAILURE;
4273 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error25', error_buf) ;
4274 RETURN;
4275 WHEN too_many_rows THEN
4276 error_buf := 'Too many rows in obligation_date select';
4277 error_code := g_FAILURE;
4278 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error30', error_buf) ;
4279 RETURN;
4280 END ; /* V1 */
4281 ELSIF (l_je_source = 'Project Accounting') AND(l_je_category ='Labor Cost') THEN -- source2
4282 IF (l_exception_category = 'NO_PA_ACCOMPLISH_DATE') THEN
4283 l_accomplish_date := l_txn_end_date ;
4284 END IF;
4285 Set_Exception('D');
4286 END IF; -- source2
4287 ELSE -- Manual
4288 /* x_name = 'MANUAL' default end_date for accomplish_date
4289 and alc code would be default alc. */
4290 --l_accomplish_date := l_txn_end_date ;
4291 --l_alc_code := p_def_alc_code;
4292 l_record_type := 'MANUAL';
4293 IF (x_name = 'MANUAL') THEN
4294 Set_Exception('R');
4295 END IF;
4296 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4297 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'ACCOMPLISH DATE IS '
4298 ||TO_CHAR(l_accomplish_date, 'MM/DD/YYYY'));
4299 END IF;
4300 END IF; -- Manual
4301 ------------------------------------------------------------
4302 -- End of all accomplish date and exception assignments
4303 ------------------------------------------------------------
4304 IF l_je_source = 'Payables' AND l_je_category = 'Payments'
4305 AND g_payment_type_flag = 'R'
4306 AND g_invoice_type_lookup_code IN ( 'CREDIT','DEBIT')
4307 AND l_reference_2 IS NOT NULL AND l_reference_3 IS NOT NULL
4308 AND l_reference_8 IS NOT NULL THEN /*g_payment_type_flag = 'R' */
4309 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4310 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'g_payment_type_flag = R');
4311 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'g_invoice_type_lookup_code ' ||
4312 g_invoice_type_lookup_code);
4313 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Check Void Date : '||
4314 g_check_void_date );
4315 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_1 : ' || l_reference_1 );
4316 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_2 (INV ID) : ' ||
4317 l_reference_2 );
4318 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_3 (CHECK ID) : ' ||
4319 l_reference_3 );
4320 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_4 : ' || l_reference_4 );
4321 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_5 : ' || l_reference_5 );
4322 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_6 : ' || l_reference_6 );
4323 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_8 (INV DIST NO) : ' ||
4324 l_reference_8 );
4325 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_9 : ' || l_reference_9 );
4326 END IF;
4327 BEGIN /* Refund Checks */
4328 SELECT obligation_date
4329 INTO l_obligation_date
4330 FROM fv_refunds_voids_all
4331 WHERE set_of_books_id = sob
4332 AND org_id = g_org_id
4333 AND TYPE = 'PAYABLE_REFUND'
4334 AND invoice_id = l_reference_2
4335 AND check_id = l_reference_3
4336 -- AND invoice_payment_id = l_reference_9
4337 AND distribution_line_number = l_reference_8;
4338 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4339 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Obligation Date : ' ||
4340 l_obligation_date );
4341 END IF;
4342 l_processed_flag := 'Y';
4343 l_update_type := 'PAYABLE_REFUND';
4344 l_type := 'PAYABLE_REFUND';
4345 l_record_type := 'PAYABLE_REFUND';
4346 IF l_obligation_date IS NULL THEN
4347 l_exception_category := 'PAYABLE_REFUND_NULL_EXPN';
4348 l_exception_section := 9;
4349 l_treasury_symbol := 'UNDEFINED';
4350 l_column_group := 0;
4351 l_accomplish_date := NULL;
4352 l_reported_flag := 'N' ;
4353 l_exception_exists := 'Y' ;
4354 l_record_category := 'EXCEPTION' ;
4355 Insert_New_Transaction(l_org_amount,1);
4356 END IF;
4357 EXCEPTION
4358 WHEN NO_DATA_FOUND THEN
4359 l_exception_category := 'PAYABLE_REFUND_NDF_EXPN';
4360 l_exception_section := 8;
4361 l_treasury_symbol := 'UNDEFINED';
4362 l_column_group := 0;
4363 l_accomplish_date := NULL;
4364 l_reported_flag := 'N' ;
4365 l_exception_exists := 'Y' ;
4366 l_record_category := 'EXCEPTION' ;
4367 Insert_New_Transaction(l_org_amount,1);
4368 END; /* Refund Checks */
4369 END IF; /* g_payment_type_flag = 'R' */
4370 IF (l_accomplish_date is not null) then -- if accomplish_date
4371 IF ((l_alc_code IS NOT NULL) OR (l_exception_category IS NULL)) THEN
4372 set_transaction_type;
4373 END IF;
4374
4375 /* Reclassification start :-
4376 Payables: The Payment Form has been updated to include a Void Payment field.
4377 If the Void Payment field is populated for a payment, then that payment and the cancelled payment it references
4378 will be considered as reclassified transactions and will be included in Section I of the partial 224.
4379 Receivables: A new DFF has been introduced in AR Receipt form. This DFF allows to replace the receipt for any existing
4380 receipts. Both receipts will be treated as reclassified and inludeded in Partial 224. */
4381
4382 IF l_je_source = 'Payables' AND l_je_category = 'Payments' AND g_payment_type_flag = 'M'
4383 AND l_reference_2 IS NOT NULL AND l_reference_3 IS NOT NULL AND l_reference_9 IS NOT NULL THEN
4384 BEGIN
4385 SELECT apc.void_check_number /* replace attribute1 with new field name */
4386 INTO l_void_check_number
4387 FROM ap_checks_all apc
4388 WHERE apc.check_id = to_number(l_reference_3);
4389 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4390 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Void Check Number : '|| l_void_check_number );
4391 END IF;
4392 EXCEPTION
4393 WHEN NO_DATA_FOUND THEN
4394 l_void_check_number:=NULL;
4395 END;
4396 BEGIN
4397 SELECT 'Y'
4398 INTO l_voided_reclassified_payment
4399 FROM AP_INVOICE_PAYMENTS_ALL APP
4400 WHERE APP.CHECK_ID = TO_NUMBER(L_REFERENCE_3)
4401 AND APP.invoice_payment_id = TO_NUMBER(L_REFERENCE_9)
4402 AND APP.reversal_inv_pmt_id IS NOT NULL
4403 AND APP.reversal_flag ='Y';
4404 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4405 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'Voided Reclassified Payment: '||l_voided_reclassified_payment);
4406 END IF;
4407 EXCEPTION
4408 WHEN NO_DATA_FOUND THEN
4409 l_voided_reclassified_payment:='N';
4410 END;
4411 ELSIF l_je_source = 'Receivables' AND l_cash_receipt_id IS NOT NULL AND l_ar_reclass_attribute IS NOT NULL THEN
4412 BEGIN
4413 sql_stmt1:= 'SELECT ACR.' || l_ar_reclass_attribute ||
4414 ' FROM AR_CASH_RECEIPTS_ALL ACR WHERE ACR.cash_receipt_id = '||l_cash_receipt_id;
4415 EXECUTE IMMEDIATE sql_stmt1 INTO l_reclass_receipt_number ;
4416 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4417 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Reclass Receipt Number: '|| l_reclass_receipt_number );
4418 END IF;
4419 EXCEPTION
4420 WHEN NO_DATA_FOUND THEN
4421 l_reclass_receipt_number:=NULL;
4422 END;
4423 BEGIN
4424 sql_stmt:='SELECT ''Y'' FROM AR_CASH_RECEIPTS_ALL ac, Ar_Cash_Receipt_History_All ah
4425 where ac.cash_receipt_id = ah.cash_receipt_id
4426 and ah.cash_receipt_history_id = '|| l_cash_receipt_hist_id ||
4427 ' and ah.STATUS= ''REVERSED'' '||
4428 ' AND ah.CURRENT_RECORD_FLAG = ''Y'' '||
4429 ' and exists (SELECT ''Y'' FROM AR_CASH_RECEIPTS_ALL AC2 '||
4430 ' WHERE AC2.' || l_ar_reclass_attribute ||
4431 ' = ' || '''' || l_document_number || '''' || ' )' ;
4432 EXECUTE IMMEDIATE sql_stmt INTO l_receipt_reclassified ;
4433 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4434 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'Receipt Reclassified: '|| l_receipt_reclassified );
4435 END IF;
4436 EXCEPTION
4437 WHEN NO_DATA_FOUND THEN
4438 l_receipt_reclassified:='N';
4439 l_exists := 'M';
4440 END;
4441 IF(l_exists = 'M') AND (l_je_category = 'Misc Receipts') THEN
4442 BEGIN
4443 sql_stmt:='SELECT ''Y'' FROM AR_CASH_RECEIPTS_ALL ac, Ar_Misc_Cash_Distributions_All ah
4444 where ac.cash_receipt_id = ah.cash_receipt_id
4445 and ah.misc_cash_distribution_id = '|| l_cash_receipt_hist_id ||
4446 ' and ah.cash_receipt_id = '||l_cash_receipt_id||
4447 ' and ah.created_from= ''ARP_REVERSE_RECEIPT.REVERSE'' '||
4448 ' and exists (SELECT ''Y'' FROM AR_CASH_RECEIPTS_ALL AC2 '||
4449 ' WHERE AC2.' || l_ar_reclass_attribute ||
4450 ' = ' || '''' || l_document_number || '''' || ' )' ;
4451 EXECUTE IMMEDIATE sql_stmt INTO l_receipt_reclassified ;
4452 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4453 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'Receipt Reclassified: '|| l_receipt_reclassified );
4454 END IF;
4455 EXCEPTION
4456 WHEN NO_DATA_FOUND THEN
4457 l_receipt_reclassified:='N';
4458 END;
4459 END IF;
4460 END IF; /*Reclassification end */
4461
4462 l_reclass := 'N';
4463 IF (l_je_category=fnd_profile.value('FV_RECLASSIFICATION_JOURNAL_CATEGORY') OR -- Journal reclassified
4464 (l_void_check_number IS NOT NULL OR l_voided_reclassified_payment = 'Y')OR -- payable reclassified
4465 (l_reclass_receipt_number IS NOT NULL OR l_receipt_reclassified = 'Y')) THEN -- Receivables reclassified
4466
4467 l_reclass := 'Y';
4468 l_reportable := 'I';
4469
4470 ELSIF g_partial_or_full ='Partial' THEN
4471 BEGIN
4472 SELECT fv.fv_alc_addresses_id ,fv.business_activity_code, fv.gwa_reporter_category_code
4473 INTO l_alc_addresses_id, l_business_activity_code, l_gwa_reporter_category_code
4474 FROM fv_alc_business_activity_v fv
4475 WHERE fv.agency_location_code = l_alc_code
4476 AND fv.PERIOD_NAME = l_gl_period
4477 AND fv.SET_OF_BOOKS_ID = sob;
4478 EXCEPTION
4479 WHEN NO_DATA_FOUND THEN
4480 l_gwa_reporter_category_code:= NULL;
4481 l_alc_addresses_id:= NULL;
4482 l_business_activity_code:= NULL;
4483 END;
4484 IF l_gwa_reporter_category_code IS NOT NULL THEN
4485 check_partial_reporting
4486 (
4487 p_business_activity_code => l_business_activity_code,
4488 p_gwa_reporter_category_code => l_gwa_reporter_category_code,
4489 p_error_code => error_code,
4490 p_error_desc => error_buf
4491 );
4492 IF error_code <> g_SUCCESS THEN
4493 RETURN;
4494 END IF;
4495 ELSE
4496 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' No GWA Reporter Category found for the ALC: ' || l_alc_code ||
4497 ' for the selected period: '|| l_gl_period);
4498 l_reportable:='E';
4499 END IF;
4500 ELSE
4501 l_reportable :='I';
4502 END IF;
4503
4504 IF l_reportable='X' or l_reportable='E' THEN
4505 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4506 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'deleting the section VI, VII exception ');
4507 END IF;
4508 DELETE fv_sf224_temp fv
4509 WHERE fv.je_line_num = L_JE_LINE_NUM
4510 AND fv.je_header_id = L_JE_HEADER_ID
4511 AND fv.exception_category IN ('DEFAULT_ALC','REPORTED_AS_MANUAL','NO_PA_ACCOMPLISH_DATE');
4512
4513 IF l_reportable='X' THEN
4514 l_exception_category := 'INVALID_BA_GWA_SEC_COMBO';
4515 l_exception_section := 10;
4516 ELSIF l_reportable='E' THEN
4517 l_exception_category := 'GWA_REPORTABLE';
4518 l_exception_section := 0;
4519 END IF;
4520 l_column_group := 0;
4521 l_reported_flag := 'N';
4522 l_exception_exists := 'Y';
4523 l_record_category := 'EXCEPTION' ;
4524 Insert_New_Transaction(l_org_amount, 1);
4525
4526 END IF;
4527 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4528 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'UPDATING FV_SF224_TEMP WITH THE ALC_CODE');
4529 END IF;
4530
4531
4532 Update fv_sf224_temp
4533 set reported_month = l_reported_month,
4534 accomplish_date = l_accomplish_date,
4535 exception_category = l_exception_category,
4536 exception_section = l_exception_section,
4537 column_group = l_column_group,
4538 inter_agency_flag = l_ia_flag,
4539 obligation_date = l_obligation_date,
4540 record_type = l_record_type,
4541 reference_2 = l_reference_2,
4542 reference_3 = l_reference_3,
4543 amount = l_org_amount * l_sign_number,
4544 actual_amount = l_org_amount,
4545 sign_number = l_sign_number,
4546 alc_code = l_alc_code,
4547 reported_flag = DECODE ( l_reportable, 'I', DECODE(l_reported_month, 'CURRENT', 'Y',
4548 'CURRENT/PRIOR','Y','N'),'N'),
4549 EXCEPTION_EXISTS = L_EXCEPTION_EXISTS,
4550 SF224_PROCESSED_FLAG= 'Y',
4551 je_source = l_je_source,
4552 je_category = l_je_category,
4553 document_number = l_document_number,
4554 txn_category = l_txn_category,
4555 reclass = NVL(l_reclass, 'N'),
4556 start_period_date = l_txn_start_date,
4557 end_period_date = l_txn_end_date,
4558 update_type = l_update_type,
4559 last_updated_by = g_user_id,
4560 last_update_date = g_sysdate,
4561 last_update_login = g_login_id,
4562 updated_request_id = g_request_id
4563 where rowid = l_rowid;
4564
4565 ELSE -- if accomplish_date
4566 update fv_sf224_temp
4567 set reported_flag = 'N',
4568 sf224_Processed_flag = 'Y',
4569 exception_exists = l_exception_exists,
4570 last_updated_by = g_user_id,
4571 last_update_date = g_sysdate,
4572 last_update_login = g_login_id,
4573 updated_request_id = g_request_id
4574 where rowid = l_rowid ;
4575 END IF; -- if accomplish_date
4576
4577 IF (sqlcode < 0) THEN
4578 error_buf := 'fv_sf224_temp table Update failed ';
4579 error_code := g_FAILURE;
4580 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error31', error_buf) ;
4581 RETURN;
4582 END IF;
4583 END LOOP; -- end c1
4584 CLOSE c1;
4585 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4586 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'-----------------------------------------------');
4587 END IF;
4588 COMMIT;
4589 EXCEPTION
4590 WHEN OTHERS THEN
4591 error_code := sqlcode ;
4592 error_buf := sqlerrm ;
4593 IF C1%ISOPEN THEN
4594 CLOSE C1;
4595 END IF ;
4596 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', error_buf) ;
4597 ROLLBACK ;
4598 RETURN ;
4599 END Process_Sf224_Transactions; /* of procedure */
4600
4601 ---------------------------------------------------------------------------------
4602 ----- CHECK_PARTIAL_REPORTING
4603 ---------------------------------------------------------------------------------
4604
4605
4606 PROCEDURE Check_partial_reporting
4607 (
4608 p_business_activity_code IN fv_alc_addresses_all.business_activity_code%TYPE,
4609 p_gwa_reporter_category_code IN fv_alc_gwa_categories.gwa_reporter_category_code%TYPE,
4610 p_error_code OUT NOCOPY NUMBER,
4611 p_error_desc OUT NOCOPY VARCHAR2
4612 )
4613 IS
4614 l_module_name VARCHAR2(200) ;
4615 BEGIN
4616 p_error_code := g_SUCCESS;
4617 l_module_name := g_module_name || 'Check_partial_reporting';
4618 IF l_ia_flag = 'Y' THEN /*CHECK FOR INTER GOVERENMENTAL TRANSACTION */
4619 BEGIN
4620 SELECT mp.trx_category_intra
4621 INTO l_reportable
4622 FROM fv_sf224_map mp
4623 WHERE mp.business_activity_code = p_business_activity_code
4624 AND mp.GWA_REPORTER_CATEGORY_CODE = p_gwa_reporter_category_code;
4625 EXCEPTION
4626 WHEN no_data_found THEN
4627 l_reportable:='E';
4628 END;
4629 ELSE
4630 /*CHECK FOR PAYMENTS AND COLLECTION BASED ON COLUMN_GROUP */
4631 IF (l_column_group = 20 OR l_column_group = 31 ) THEN
4632 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4633 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'column group is 20 or 31 ');
4634 END IF;
4635 BEGIN
4636 SELECT mp.trx_category_coll
4637 INTO l_reportable
4638 FROM fv_sf224_map mp
4639 WHERE mp.business_activity_code = p_business_activity_code
4640 AND mp.GWA_REPORTER_CATEGORY_CODE = p_gwa_reporter_category_code;
4641 EXCEPTION
4642 WHEN no_data_found THEN
4643 l_reportable:='E';
4644 END;
4645 ELSIF (l_column_group = 21 OR l_column_group = 30 ) THEN
4646 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4647 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'column group is 21 or 30 ');
4648 END IF;
4649 BEGIN
4650 SELECT mp.trx_category_pay
4651 INTO l_reportable
4652 FROM fv_sf224_map mp
4653 WHERE mp.business_activity_code = p_business_activity_code
4654 AND mp.GWA_REPORTER_CATEGORY_CODE = p_gwa_reporter_category_code;
4655 EXCEPTION
4656 WHEN no_data_found THEN
4657 l_reportable:='E';
4658 END;
4659 END IF;
4660 END IF;
4661 EXCEPTION
4662 WHEN OTHERS THEN
4663 p_error_code := g_FAILURE;
4664 p_error_desc := SQLERRM || '-- Error in procedure Check_partial_reporting.';
4665 fv_utility.log_mesg(fnd_log.level_unexpected, l_module_name||'.final_exception', p_error_desc) ;
4666 RETURN;
4667 END Check_partial_reporting;
4668
4669
4670
4671 ----------------------------------------------------------------------
4672 -- SET_EXCEPTION
4673 ----------------------------------------------------------------------
4674 PROCEDURE Set_Exception(exp_type VARCHAR2) IS
4675 l_module_name VARCHAR2(200) ;
4676 BEGIN
4677 l_module_name := g_module_name || 'Set_Exception';
4678 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4679 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'IN SET_EXCEPTION PROC WITH EXP_TYPE = '||EXP_TYPE);
4680 END IF;
4681 IF (exp_type = 'R') THEN
4682 -- Reported as Manual exception
4683 l_name := 'MANUAL';
4684 l_exception_category := 'REPORTED_AS_MANUAL';
4685 l_exception_section := 6;
4686 l_accomplish_date := l_txn_end_date ;
4687 ELSE
4688 -- Default ALC exception
4689 l_exception_category := 'DEFAULT_ALC';
4690 l_exception_section := 6;
4691 --l_alc_code := p_def_alc_code;
4692 END IF;
4693 l_alc_code := p_def_alc_code;
4694 l_reported_flag := 'Y';
4695 l_exception_exists := 'Y';
4696 l_record_category := 'EXCEPTION';
4697 Insert_New_Transaction(l_amount, 1);
4698 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4699 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TXN INSERTED');
4700 END IF;
4701 EXCEPTION
4702 WHEN OTHERS THEN
4703 error_code := 2;
4704 error_buf := SQLERRM || '-- Error in procedure Set_Exception.';
4705 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', error_buf) ;
4706 RETURN;
4707 END Set_Exception;
4708
4709
4710 ----------------------------------------------------------------------
4711 -- GET_ALC
4712 ----------------------------------------------------------------------
4713
4714 PROCEDURE get_alc
4715 (
4716 p_bank_acct_id IN NUMBER,
4717 p_alc_code OUT NOCOPY VARCHAR2,
4718 p_error_code OUT NOCOPY NUMBER,
4719 p_error_desc OUT NOCOPY VARCHAR2
4720 )
4721 IS
4722 l_module_name VARCHAR2(200) ;
4723 l_location VARCHAR2(200);
4724
4725 CURSOR get_alc_cur
4726 (
4727 c_bank_acct_id NUMBER
4728 ) IS
4729 SELECT ceba.agency_location_code
4730 FROM ce_Bank_Accounts ceba,
4731 ce_bank_acct_uses_all cebu
4732 WHERE cebu.org_id = g_org_id
4733 AND cebu.org_id = ceba.account_owner_org_id
4734 AND cebu.bank_acct_use_id = c_bank_acct_id
4735 AND ceba.bank_account_id = cebu.bank_account_id;
4736
4737 BEGIN
4738 l_module_name := g_module_name || 'get_alc';
4739 p_error_code := g_SUCCESS;
4740
4741 OPEN get_alc_cur(p_bank_acct_id);
4742 FETCH get_alc_cur INTO p_alc_code;
4743 CLOSE get_alc_cur;
4744
4745 IF (p_alc_code IS NULL) THEN
4746 set_exception('D');
4747 END IF;
4748 EXCEPTION
4749 WHEN OTHERS THEN
4750 p_error_code := g_FAILURE;
4751 p_error_desc := SQLERRM;
4752 l_location := l_module_name||'.final_exception';
4753 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
4754 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
4755 END Get_Alc;
4756
4757
4758
4759 ----------------------------------------------------------------------
4760 -- INSERT_NEW_TRANSACTION
4761 ----------------------------------------------------------------------
4762 PROCEDURE insert_new_transaction(x_amount number, x_sign_number number) is
4763 l_module_name VARCHAR2(200) ;
4764
4765 BEGIN
4766 l_module_name := g_module_name || 'Insert_new_transaction';
4767 INSERT INTO fv_sf224_temp
4768 (
4769 je_batch_id,
4770 fund_code,
4771 name,
4772 amount,
4773 actual_amount,
4774 sign_number,
4775 reported_month,
4776 column_group,
4777 record_type,
4778 inter_agency_flag,
4779 obligation_date,
4780 exception_category,
4781 accomplish_date,
4782 treasury_symbol,
4783 treasury_symbol_id,
4784 je_line_num,
4785 reported_flag,
4786 exception_exists,
4787 record_category,
4788 reference_1,
4789 reference_2,
4790 reference_3,
4791 reference_9,
4792 je_header_id,
4793 alc_code,
4794 gl_period,
4795 set_of_books_id,
4796 je_source,
4797 je_category,
4798 document_number,
4799 txn_category,
4800 start_period_date,
4801 end_period_date,
4802 exception_section,
4803 created_by,
4804 creation_date,
4805 last_updated_by,
4806 last_update_date,
4807 last_update_login,
4808 created_request_id
4809 )
4810 VALUES
4811 (
4812 l_batch_id,
4813 l_billing_agency_fund,
4814 x_name,
4815 x_amount*x_sign_number,
4816 x_amount,
4817 x_sign_number,
4818 l_reported_month,
4819 l_column_group,
4820 l_record_type,
4821 l_ia_flag,
4822 l_obligation_date,
4823 l_exception_category,
4824 l_accomplish_date,
4825 l_treasury_symbol,
4826 l_treasury_symbol_id,
4827 l_je_line_num,
4828 DECODE(l_record_category, 'EXCEPTION', NULL, l_reported_flag),
4829 DECODE(l_record_category, 'EXCEPTION', NULL, l_exception_exists),
4830 l_record_category,
4831 l_reference_1,
4832 l_reference_2,
4833 l_reference_3,
4834 l_reference_9,
4835 l_je_header_id,
4836 l_alc_code,
4837 l_gl_period,
4838 sob,
4839 l_je_source,
4840 l_je_category,
4841 l_document_number,
4842 l_txn_category,
4843 l_txn_start_date,
4844 l_txn_end_date,
4845 l_exception_section,
4846 g_user_id,
4847 g_sysdate,
4848 g_user_id,
4849 g_sysdate,
4850 g_login_id,
4851 g_request_id
4852 );
4853 EXCEPTION
4854 WHEN OTHERS THEN
4855 error_code := sqlcode ;
4856 error_buf := sqlerrm ;
4857 RollBack ;
4858 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', error_buf) ;
4859 Return ;
4860 END Insert_new_transaction;
4861
4862 ----------------------------------------------------------------------
4863 -- SET_TRANSACTION_TYPE
4864 ----------------------------------------------------------------------
4865 Procedure set_transaction_type is
4866 l_module_name VARCHAR2(200) ;
4867 BEGIN
4868 l_module_name := g_module_name || 'set_transaction_type';
4869 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4870 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'IN SET_TRANSACTION_TYPE PROC');
4871 END IF;
4872 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4873 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'EXCEPTION CATEGORY IS '||L_EXCEPTION_CATEGORY);
4874 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RECORD TYPE IS '||L_RECORD_TYPE);
4875 END IF;
4876 l_sf224_type_code := upper(l_sf224_type_code);
4877 l_sign_number := 1;
4878 IF(l_record_type = 'Receipt') THEN
4879 l_column_group := 20;
4880 l_txn_category := 'C';
4881 IF(l_ia_flag = 'Y') THEN
4882 l_column_group := 21;
4883 l_txn_category := 'I';
4884 END IF;
4885 ELSIF (l_record_type = 'Disbursement') THEN
4886 l_column_group := 30;
4887 l_txn_category := 'P';
4888 l_sign_number := -1;
4889 ELSIF(l_record_type LIKE '%refund%' OR l_record_type = 'VOID') THEN
4890 NULL;
4891 ELSIF(l_record_type = 'MANUAL') THEN
4892 IF (l_sf224_type_code = 'RECEIPT') THEN
4893 l_column_group := 20;
4894 l_txn_category := 'C';
4895 ELSIF (l_sf224_type_code = 'DISBURSEMENT') THEN
4896 l_column_group := 30;
4897 l_txn_category := 'P';
4898 l_sign_number := -1;
4899 ELSIF(l_sf224_type_code = 'REVOLVING') THEN
4900 IF(l_d_r_flag = 'R') THEN
4901 l_column_group := 20;
4902 l_txn_category := 'C';
4903 ELSE
4904 l_column_group := 30;
4905 l_txn_category := 'P';
4906 l_sign_number := -1;
4907 END IF;
4908 END IF; /* fund_type */
4909 END IF; /* Record type */
4910 EXCEPTION
4911 WHEN OTHERS THEN
4912 error_code := sqlcode ;
4913 error_buf := sqlerrm ;
4914 ROLLBACK ;
4915 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', error_buf) ;
4916 RETURN;
4917 END set_transaction_type;
4918
4919
4920 ----------------------------------------------------------------------
4921 -- PRE_PRECESS
4922 ----------------------------------------------------------------------
4923
4924 PROCEDURE pre_process
4925 (
4926 p_set_of_books_id IN NUMBER,
4927 p_error_code OUT NOCOPY NUMBER,
4928 p_error_desc OUT NOCOPY VARCHAR2
4929 )
4930 IS
4931 l_module_name VARCHAR2(200);
4932 l_location VARCHAR2(200);
4933 BEGIN
4934 l_module_name := g_module_name || '.extract';
4935 p_error_code := g_SUCCESS;
4936
4937 BEGIN
4938 /* Delete all exceptions */
4939 DELETE fv_sf224_temp fst
4940 WHERE fst.set_of_books_id = p_set_of_books_id
4941 AND fst.exception_exists IS NULL
4942 AND fst.record_category = 'EXCEPTION';
4943 EXCEPTION
4944 WHEN OTHERS THEN
4945 p_error_code := g_FAILURE;
4946 p_error_desc := SQLERRM;
4947 l_location := l_module_name||'delete_fv_sf224_temp';
4948 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
4949 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
4950 END;
4951
4952 IF (p_error_code = g_SUCCESS) THEN
4953 BEGIN
4954 /* Reprocess all exceptions */
4955 UPDATE fv_sf224_temp fst
4956 SET fst.sf224_processed_flag = 'N',
4957 last_updated_by = g_user_id,
4958 last_update_date = g_sysdate,
4959 last_update_login = g_login_id,
4960 updated_request_id = g_request_id
4961 WHERE fst.set_of_books_id = p_set_of_books_id
4962 AND fst.exception_exists = 'Y';
4963 EXCEPTION
4964 WHEN OTHERS THEN
4965 p_error_code := g_FAILURE;
4966 p_error_desc := SQLERRM;
4967 l_location := l_module_name||'update_fv_sf224_temp';
4968 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
4969 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
4970 END;
4971 END IF;
4972
4973 EXCEPTION
4974 WHEN OTHERS THEN
4975 p_error_code := g_FAILURE;
4976 p_error_desc := SQLERRM;
4977 l_location := l_module_name||'.final_exception';
4978 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
4979 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
4980 END pre_process;
4981
4982
4983
4984
4985 -- End addition
4986
4987 -- ==============================================================================================
4988 -- This procedure check is the ALC address for each ALC code in the FV_ALC_ADDRESSES_ALL Table
4989 -- If atleast address for one ALC code does not exist the status of the Concurrent program is set
4990 -- as Warning and all the ALCs with out address are displayed in the Con Log
4991 -- ==============================================================================================
4992 PROCEDURE check_alc_address
4993 (
4994 p_alc IN VARCHAR2
4995 )
4996 IS
4997 l_module_name VARCHAR2(100);
4998 l_count NUMBER;
4999 l_alc_add_missing_count NUMBER := 0;
5000 CURSOR alc_code_csr IS
5001 SELECT DISTINCT tmp.alc_code
5002 FROM fv_sf224_temp tmp
5003 WHERE tmp.set_of_books_id = sob
5004 AND tmp.alc_code IS NOT NULL;
5005 BEGIN
5006 l_module_name := g_module_name || 'check_alc_address';
5007 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5008 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Start of check_alc_address p_alc_code => ' || p_alc) ;
5009 END IF;
5010 IF p_alc = 'ALL' THEN /* p_alc = 'ALL' */
5011 FOR alc_code_rec IN alc_code_csr LOOP
5012 BEGIN
5013 SELECT 1
5014 INTO l_count
5015 FROM fv_alc_addresses_all
5016 WHERE AGENCY_LOCATION_CODE = alc_code_rec.alc_code
5017 AND set_of_books_id = sob;
5018 EXCEPTION
5019 WHEN NO_DATA_FOUND THEN
5020 IF l_alc_add_missing_count = 0 THEN
5021 fv_utility.log_mesg(fnd_log.LEVEL_ERROR, l_module_name,'ALC address not found for the following ALC(s)');
5022 error_buf := SUBSTR(error_buf,1,40) || ' WITH WARNING **' ||
5023 fnd_global.newline() || 'ALC Address Not Found For The ALC(s)' ;
5024 END IF;
5025 l_alc_add_missing_count := l_alc_add_missing_count+1;
5026 fv_utility.log_mesg(fnd_log.LEVEL_ERROR, l_module_name,l_alc_add_missing_count || ' : ' || alc_code_rec.alc_code);
5027 error_buf := error_buf || fnd_global.newline() || l_alc_add_missing_count ||' : ' || alc_code_rec.alc_code ;
5028 END;
5029 END LOOP;
5030 ELSE /* when p_alc is not 'ALL' */
5031 BEGIN
5032 SELECT 1
5033 INTO l_count
5034 FROM fv_alc_addresses_all
5035 WHERE AGENCY_LOCATION_CODE = p_alc
5036 AND set_of_books_id = sob;
5037 EXCEPTION
5038 WHEN NO_DATA_FOUND THEN
5039 l_alc_add_missing_count := l_alc_add_missing_count+1;
5040 fv_utility.LOG_MESG(Fnd_Log.LEVEL_ERROR, l_module_name,'ALC address not found for the following ALC ' || p_alc);
5041 error_buf := SUBSTR(error_buf,1,40) || ' WITH WARNING **' || fnd_global.newline() ||
5042 'ALC Address Not Found For The ALC ' || p_alc ;
5043 END;
5044 END IF; /* p_alc = 'ALL' */
5045 IF l_alc_add_missing_count > 0 THEN /* l_alc_add_missing_count > 0 */
5046 IF FND_CONCURRENT.SET_COMPLETION_STATUS ('WARNING','ALC address not found for the ALC(s)') THEN
5047 -- error_buf := SUBSTR(error_buf,1,40) || ' WITH WARNING **' || fnd_global.newline() ||
5048 -- 'ALC address not found for the ALC(s)' ;
5049 NULL;
5050 ELSE
5051 fv_utility.LOG_MESG(Fnd_Log.LEVEL_ERROR, l_module_name,'Error in seting the Concurent Program Status as Waring');
5052 error_code := SQLCODE ;
5053 error_buf := SQLERRM;
5054 END IF;
5055 END IF; /* l_alc_add_missing_count > 0 */
5056 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5057 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'End of check_alc_address ') ;
5058 END IF;
5059 EXCEPTION
5060 WHEN OTHERS THEN
5061 error_code := SQLCODE;
5062 error_buf := SQLERRM;
5063 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', error_buf) ;
5064 RAISE;
5065 END check_alc_address;
5066
5067 ----------------------------------------------------------------------
5068 -- EXTRACT
5069 ----------------------------------------------------------------------
5070
5071 PROCEDURE extract
5072 (
5073 p_errbuf OUT NOCOPY VARCHAR2,
5074 p_retcode OUT NOCOPY NUMBER,
5075 p_set_of_books_id IN NUMBER
5076 )
5077 IS
5078 l_module_name VARCHAR2(200);
5079 l_location VARCHAR2(200);
5080 l_currency_code gl_sets_of_books.currency_code%TYPE;
5081 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE;
5082 l_acct_segment fnd_id_flex_segments.application_column_name%TYPE;
5083 l_acct_value_set_id fnd_id_flex_segments.flex_value_set_id%TYPE;
5084 l_bal_segment fnd_id_flex_segments.application_column_name%TYPE;
5085 l_previous_run_date DATE;
5086 BEGIN
5087 l_module_name := g_module_name || '.extract';
5088 p_retcode := g_SUCCESS;
5089 sob := p_set_of_books_id;
5090
5091 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5092 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'In extrac procdure ');
5093 End if;
5094
5095
5096 check_report_definitions
5097 (
5098 p_set_of_books_id => p_set_of_books_id,
5099 p_error_code => p_retcode,
5100 p_error_desc => p_errbuf
5101 );
5102
5103 IF (p_retcode = g_SUCCESS) THEN
5104 initialize_program_variables
5105 (
5106 p_set_of_books_id => p_set_of_books_id,
5107 p_currency_code => l_currency_code,
5108 p_chart_of_accounts_id => l_chart_of_accounts_id,
5109 p_acct_segment => l_acct_segment,
5110 p_acct_value_set_id => l_acct_value_set_id,
5111 p_bal_segment => l_bal_segment,
5112 p_accomplish_attribute => l_accomplish_attribute,
5113 p_error_code => p_retcode,
5114 p_error_desc => p_errbuf
5115 );
5116 END IF;
5117
5118 flex_num := l_chart_of_accounts_id;
5119
5120 IF (p_retcode = g_SUCCESS) THEN
5121 get_run_date
5122 (
5123 p_set_of_books_id => p_set_of_books_id,
5124 p_previous_run_date => l_previous_run_date,
5125 p_error_code => p_retcode,
5126 p_error_desc => p_errbuf
5127 );
5128 END IF;
5129 IF (p_retcode = g_SUCCESS) THEN
5130 insert_sf224_batches
5131 (
5132 p_set_of_books_id => p_set_of_books_id,
5133 p_previous_run_date => l_previous_run_date,
5134 p_current_run_date => g_SYSDATE,
5135 p_currency_code => l_currency_code,
5136 p_acct_segment => l_acct_segment,
5137 p_bal_segment => l_bal_segment,
5138 p_error_code => p_retcode,
5139 p_error_desc => p_errbuf
5140 );
5141 END IF;
5142 IF (p_retcode = g_SUCCESS) THEN
5143 set_run_date
5144 (
5145 p_set_of_books_id => p_set_of_books_id,
5146 p_error_code => p_retcode,
5147 p_error_desc => p_errbuf
5148 );
5149 END IF;
5150 IF (p_retcode = g_SUCCESS) THEN
5151 pre_process
5152 (
5153 p_set_of_books_id => p_set_of_books_id,
5154 p_error_code => p_retcode,
5155 p_error_desc => p_errbuf
5156 );
5157 END IF;
5158
5159 IF (p_retcode = g_SUCCESS) THEN
5160 process_sf224_transactions;
5161 p_retcode := error_code;
5162 p_errbuf := error_buf;
5163 END IF;
5164
5165
5166 IF (p_retcode <> g_SUCCESS) THEN
5167 ROLLBACK;
5168 ELSE
5169 COMMIT;
5170 END IF;
5171 EXCEPTION
5172 WHEN OTHERS THEN
5173 p_retcode := g_FAILURE;
5174 p_errbuf := SQLERRM;
5175 l_location := l_module_name||'.final_exception';
5176 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
5177 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
5178 END extract;
5179
5180
5181
5182 -- ==============================================================================================
5183 -- This procedure get the obligation Date of the Void Payment.
5184 -- If the Distibution is derived from a PO, the PO date is considered as obligation Date
5185 -- Else If the Distibution is derived from an Invoice, the Invocie date is considered as obligation Date
5186 -- Else that is the distribution is manual entry then the Invoice Date is taken as the Obligation date
5187 -- If a NO_DATA_FOUND Exception Happen during the calculation the NO_DATA_FOUND raised
5188 -- back to the calling Procedure
5189 -- ==============================================================================================
5190
5191 FUNCTION get_void_check_obligation_date( p_invoice_id NUMBER,
5192 p_check_id NUMBER,
5193 P_inv_dist_num NUMBER)
5194
5195 RETURN DATE IS
5196 l_obligation_date DATE;
5197 l_module_name VARCHAR2(100);
5198 l_po_dist_id ap_invoice_distributions_all.po_distribution_id%TYPE;
5199 l_parent_invoice_id ap_invoices_all.invoice_id%TYPE;
5200 BEGIN
5201 l_module_name := g_module_name || 'get_void_check_obligation_date';
5202 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5203 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Start of get_void_check_obligation_date') ;
5204 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, ' p_invoice_id => ' || p_invoice_id ||
5205 ' p_check_id => ' || p_check_id ||
5206 ' P_inv_dist_num => ' || P_inv_dist_num) ;
5207 END IF;
5208
5209 ------------- modified for bug 5454445
5210 SELECT NVL(max(po_distribution_id),0),
5211 NVL(max(parent_invoice_id),0)
5212 INTO l_po_dist_id, l_parent_invoice_id
5213 FROM ap_invoice_distributions_all
5214 WHERE invoice_id = p_invoice_id ;
5215 --AND distribution_line_number = p_inv_dist_num;
5216 --------------
5217 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5218 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5219 ' l_po_dist_id => ' || l_po_dist_id || ' l_parent_invoice_id => ' || l_parent_invoice_id) ;
5220 END IF;
5221 IF l_po_dist_id <> 0 THEN
5222 SELECT NVL(gl_encumbered_date,creation_date)
5223 INTO l_obligation_date
5224 FROM po_distributions
5225 WHERE po_distribution_id =l_po_dist_id;
5226 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5227 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5228 'Obligation Date is PO Date => ' || l_obligation_date);
5229 END IF;
5230 ELSIF l_parent_invoice_id <> 0 THEN
5231 SELECT INVOICE_DATE
5232 INTO l_obligation_date
5233 FROM ap_invoices_all
5234 WHERE invoice_id = l_parent_invoice_id;
5235 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5236 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5237 'Obligation Date is Parent Invoice Date => ' || l_obligation_date);
5238 END IF;
5239 ELSE
5240 SELECT INVOICE_DATE
5241 INTO l_obligation_date
5242 FROM ap_invoices_all
5243 WHERE invoice_id = p_invoice_id;
5244 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5245 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5246 'Obligation Date is Invoice Date => ' || l_obligation_date);
5247 END IF;
5248 END IF;
5249
5250
5251 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5252 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5253 'End of get_void_check_obligation_date l_obligation_date NEW CODE => ' || l_obligation_date) ;
5254 END IF;
5255 RETURN l_obligation_date;
5256 EXCEPTION
5257 WHEN NO_DATA_FOUND THEN
5258 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5259 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'NO DATA FOUND EXCEPTION');
5260 END IF;
5261 RAISE;
5262 WHEN OTHERS THEN
5263 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5264 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'WHEN OTHERS EXCEPTION');
5265 END IF;
5266 error_code := SQLCODE;
5267 error_buf := SQLERRM;
5268 RAISE ;
5269 END get_void_check_obligation_date;
5270
5271 -- ==============================================================================================
5272 -- This procedure populate all the Refunds information from AP to FV table.
5273 -- This procedure is called from FMS224 Populate Payables Refunds Process.
5274 -- And this process takes mainly two parameters GL Period Low and GL period high
5275 -- And this process submit the Report which lists all the imported data from AP to FV table (FV_REFUNDS_VOIDS_all)
5276 -- with con request Id as the parameter
5277 -- Logic :
5278 -- This process takes all the posted Refunds (ie, Payment Type Flag = 'R' (Refund) and
5279 -- Posted Flag = Y (Accounted) and Entry exist in GL_JE_LINES (Posted in GL),
5280 -- which are not in the FV table (ie INVOICE PAYMNET ID does not exist in FV table )
5281 -- and GL date with in the GL Period.
5282 -- If the Distibution is derived from a PO, the PO date is populated as obligation Date
5283 -- and PO Number as Obligation Number
5284 -- Else If the Distibution is derived from an Invoice, the Invocie date is populated as
5285 -- obligation Date and parent Invoice Number as Obligation Nuber
5286 -- Else that is the distribution is manual entry then NULL is populated for
5287 -- Obligation date and Obligation Number
5288 -- The dist_code_combination_id is joined with gl_je_lines code_combination_id is to
5289 -- imporve the performance
5290 -- ==============================================================================================
5291 PROCEDURE fv_ap_refund_populate( errbuf OUT NOCOPY VARCHAR2,
5292 retcode OUT NOCOPY NUMBER,
5293 p_set_of_books_id IN NUMBER,
5294 p_org_id IN NUMBER,
5295 P_gl_period_low IN VARCHAR2,
5296 p_gl_period_high IN VARCHAR2)
5297 IS
5298 l_module_name VARCHAR2(100);
5299 l_gl_start_date DATE;
5300 l_gl_end_date DATE;
5301 l_user_id NUMBER(15);
5302 l_conc_request_id NUMBER(15);
5303 l_set_of_books_id NUMBER(15);
5304 l_org_id NUMBER(15);
5305 l_no_copies NUMBER(15);
5306 l_printer_name VARCHAR2(240);
5307 l_print_option BOOLEAN;
5308 l_report_conc_request_id NUMBER(15);
5309 l_call_status BOOLEAN;
5310 l_dphase VARCHAR2(80);
5311 l_rphase VARCHAR2(80);
5312 l_dstatus VARCHAR2(80);
5313 l_rstatus VARCHAR2(80);
5314 l_message VARCHAR2(80);
5315
5316 l_error_code NUMBER;
5317 l_error_buf VARCHAR2(1000);
5318 BEGIN
5319 l_module_name := g_module_name || '.fv_ap_refund_populate';
5320 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5321 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Strat of the fv_ap_refund_populate Process');
5322 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_set_of_books_id : ' || p_set_of_books_id) ;
5323 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_org_id : ' || p_org_id ) ;
5324 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_gl_period_low : ' || p_gl_period_low) ;
5325 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_gl_period_high : ' || p_gl_period_high) ;
5326 END IF;
5327 l_user_id := fnd_global.user_id;
5328 l_conc_request_id := fnd_global.conc_request_id;
5329 l_set_of_books_id := p_set_of_books_id;
5330 l_org_id := p_org_id;
5331 -- Get the start Date GL_PERIOD_LOW and End Date of GL_PERIOD_HIGH
5332 BEGIN
5333 SELECT start_date
5334 INTO l_gl_start_date
5335 FROM gl_period_statuses
5336 WHERE ledger_id = l_set_of_books_id
5337 AND application_id = 101
5338 AND period_name = p_gl_period_low;
5339 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5340 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Start Date for the period low is : ' ||
5341 l_gl_start_date ) ;
5342 END IF;
5343 EXCEPTION
5344 WHEN NO_DATA_FOUND THEN
5345 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5346 'Error in getting Start Date for the period Low ' ) ;
5347 RAISE;
5348 END;
5349 BEGIN
5350 SELECT end_date
5351 INTO l_gl_end_date
5352 FROM gl_period_statuses
5353 WHERE ledger_id = l_set_of_books_id
5354 AND application_id = 101
5355 AND period_name = p_gl_period_high;
5356 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5357 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'End Date for the period low is : ' ||
5358 l_gl_end_date ) ;
5359 END IF;
5360 EXCEPTION
5361 WHEN NO_DATA_FOUND THEN
5362 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5363 'Error in getting End Date for the Period High ' ) ;
5364 RAISE;
5365 END;
5366
5367 -- Populate the the data from AP tables to FV table
5368 INSERT INTO FV_REFUNDS_VOIDS_ALL
5369 ( refunds_voids_id,
5370 TYPE ,
5371 invoice_id,
5372 processed_flag,
5373 set_of_books_id,
5374 org_id,
5375 conc_request_id,
5376 last_update_date,
5377 last_updated_by,
5378 created_by,
5379 creation_date,
5380 last_update_login,
5381 vendor_id,
5382 vendor_site_id,
5383 invoice_distribution_id,
5384 distribution_line_number,
5385 dist_code_combination_id ,
5386 dist_amount,
5387 check_id,
5388 invoice_payment_id,
5389 refund_amount,
5390 refund_gl_date ,
5391 invoice_gl_date,
5392 invoice_num,
5393 vendor_name ,
5394 vendor_site_code,
5395 check_number,
5396 refund_gl_period,
5397 invoice_amount,
5398 vendor_number,
5399 obligation_date,
5400 obligation_number
5401 )
5402 (
5403 SELECT fv_refunds_voids_s.NEXTVAL,
5404 'PAYABLE_REFUND',
5405 api.invoice_id,
5406 'N',
5407 l_set_of_books_id,
5408 l_org_id,
5409 l_conc_request_id,
5410 SYSDATE,
5411 l_user_id,
5412 l_user_id,
5413 SYSDATE,
5414 l_user_id,
5415 api.vendor_id,
5416 api.vendor_site_id,
5417 apid.invoice_distribution_id,
5418 apid.distribution_line_number ,
5419 apid.dist_code_combination_id,
5420 apid.amount,
5421 apc.check_id,
5422 apip.invoice_payment_id,
5423 apip.amount refund_amount,
5424 apc.check_date ,
5425 api.gl_date,
5426 api.invoice_num,
5427 apc.vendor_name ,
5428 apc.vendor_site_code,
5429 apc.check_number,
5430 apip.period_name,
5431 api.invoice_amount,
5432 (SELECT segment1 FROM po_vendors WHERE vendor_id = apc.vendor_id),
5433 DECODE(apid.po_distribution_id,
5434 NULL,
5435 DECODE(apid.parent_invoice_id,
5436 NULL,
5437 NULL,
5438 (SELECT invoice_date
5439 FROM ap_invoices_all
5440 WHERE invoice_id = apid.parent_invoice_id)),
5441 (SELECT NVL(gl_encumbered_date,creation_date)
5442 FROM po_distributions
5443 WHERE po_distribution_id = apid.po_distribution_id))
5444 obligation_date,
5445 DECODE(apid.po_distribution_id,
5446 NULL,
5447 DECODE(apid.parent_invoice_id,
5448 NULL,
5449 NULL,
5450 (SELECT invoice_num
5451 FROM ap_invoices_all
5452 WHERE invoice_id = apid.parent_invoice_id)),
5453 (SELECT segment1
5454 FROM po_headers
5455 WHERE po_header_id = (SELECT po_header_id
5456 FROM po_distributions
5457 WHERE po_distribution_id = apid.po_distribution_id)))
5458 obligation_number
5459 FROM
5460 ap_checks_all apc,
5461 ap_invoice_payments_all apip,
5462 ap_invoices_all api,
5463 ap_invoice_distributions_all apid
5464 WHERE apip.set_of_books_id = l_set_of_books_id
5465 AND apip.set_of_books_id = api.set_of_books_id
5466 AND api.set_of_books_id = apid.set_of_books_id
5467 AND apc.org_id = l_org_id
5468 AND apc.org_id = apip.org_id
5469 AND apip.org_id = api.org_id
5470 AND api.org_id = apid.org_id
5471 AND apc.check_id = apip.check_id
5472 AND apip.invoice_id = api.invoice_id
5473 AND api.invoice_id = apid.invoice_id
5474 AND api.invoice_type_lookup_code IN ( 'CREDIT','DEBIT')
5475 AND apc.payment_type_flag = 'R'
5476 AND apip.posted_flag = 'Y'
5477 AND reversal_inv_pmt_id IS NULL
5478 AND ap_checks_pkg.get_posting_status(apc.check_id) IN ('Y','P')
5479 AND TRUNC(apc.check_date) BETWEEN TRUNC(l_gl_start_date) AND TRUNC(l_gl_end_date)
5480 AND NOT EXISTS
5481 ( SELECT 1 FROM fv_refunds_voids_all
5482 WHERE invoice_payment_id = apip.invoice_payment_id)
5483 AND EXISTS (SELECT 1
5484 FROM gl_je_lines gljl,
5485 gl_je_headers gljh,
5486 ap_invoice_distributions_all apid
5487 WHERE gljl.ledger_id = apid.set_of_books_id
5488 AND gljl.status = 'P'
5489 AND gljh.je_header_id = gljl.je_header_id
5490 AND gljh.je_from_sla_flag = 'N'
5491 AND gljl.code_combination_id = apid.DIST_CODE_COMBINATION_ID
5492 AND apid.invoice_id = api.invoice_id
5493 AND gljl.reference_2 = TO_CHAR(api.invoice_id)
5494 UNION
5495 SELECT 1
5496 FROM gl_je_lines gljl,
5497 gl_je_headers gljh,
5498 gl_import_references glir,
5499 xla_ae_lines xal,
5500 xla_distribution_links xdl,
5501 ap_invoice_distributions_all apid
5502 WHERE gljl.ledger_id = apid.set_of_books_id
5503 AND glir.je_batch_id = gljh.je_batch_id
5504 AND glir.je_header_id = gljh.je_header_id
5505 AND glir.je_line_num = gljl.je_line_num
5506 AND gljl.status = 'P'
5507 AND gljh.je_header_id = gljl.je_header_id
5508 AND gljh.je_from_sla_flag = 'Y'
5509 AND gljh.je_source = 'Payables'
5510 AND xal.gl_sl_link_id = glir.gl_sl_link_id
5511 AND xal.gl_sl_link_table = glir.gl_sl_link_table
5512 AND xdl.ae_header_id = xal.ae_header_id
5513 AND xdl.ae_line_num = xal.ae_line_num
5514 AND xdl.source_distribution_id_num_1 = apid.invoice_distribution_id
5515 AND gljl.code_combination_id = apid.DIST_CODE_COMBINATION_ID
5516 AND apid.invoice_id = api.invoice_id
5517 )
5518
5519 );
5520 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5521 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'No of Rows inserted into the FV table : '
5522 || SQL%ROWCOUNT ) ;
5523 END IF;
5524 COMMIT;
5525 -- Call the report to display the data populated by the process
5526 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5527 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5528 'SUBMITTING FMS 224 Populate Payables Refunds Process Report .....') ;
5529 END IF;
5530 SELECT printer,
5531 number_of_copies
5532 INTO l_printer_name,
5533 l_no_copies
5534 FROM fnd_concurrent_requests
5535 WHERE request_id = l_conc_request_id ;
5536
5537 l_print_option := FND_REQUEST.SET_PRINT_OPTIONS(printer => l_printer_name, copies => l_no_copies) ;
5538 -- Submit the Report
5539 l_report_conc_request_id := FND_REQUEST.SUBMIT_REQUEST ('FV','FVAPRFPR','','',FALSE,
5540 l_conc_request_id,l_set_of_books_id,
5541 l_org_id,p_gl_period_low,p_gl_period_high);
5542 IF l_report_conc_request_id = 0 THEN
5543 retcode := -1 ;
5544 errbuf := 'ERROR SUBMITTING FMS 224 Populate Payables Refunds Process Report. ';
5545 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
5546 'ERROR SUBMITTING FMS 224 Populate Payables Refunds Process Report. ') ;
5547 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5548 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5549 'Deleting all the Data from FV table inserted by the current process') ;
5550 END IF;
5551 DELETE FROM fv_refunds_voids_all
5552 WHERE TYPE = 'PAYABLE_REFUND' AND conc_request_id = l_conc_request_id;
5553 COMMIT;
5554 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5555 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5556 'No of Rows Deleted from FV table : ' || SQL%ROWCOUNT ) ;
5557 END IF;
5558 END IF;
5559 EXCEPTION
5560 WHEN OTHERS THEN
5561 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception','When Others Exception') ;
5562 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception','Error Code : ' ||
5563 SQLCODE) ;
5564 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception','Error Mesg : ' ||
5565 SQLERRM) ;
5566 errbuf := SQLERRM;
5567 retcode := SQLCODE;
5568 END fv_ap_refund_populate;
5569
5570 ----------------------------------------------------------------------
5571 -- END OF PACKAGE BODY
5572 ----------------------------------------------------------------------
5573 BEGIN
5574 initialize_global_variables;
5575 END FV_SF224_TRANSACTIONS;