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