[Home] [Help]
PACKAGE BODY: APPS.FV_BE_INT_PKG
Source
1 PACKAGE BODY fv_be_int_pkg AS
2 --$Header: FVBEINTB.pls 120.37.12020000.3 2013/02/12 21:02:17 snama ship $
3 g_module_name VARCHAR2(100);
4
5
6 parm_source VARCHAR2(25);
7 parm_group_id NUMBER;
8 parm_ledger_id NUMBER;
9 v_error_code NUMBER;
10 v_exists VARCHAR2(1);
11 v_coa_id NUMBER;
12 v_seg_count NUMBER;
13
14 l_seg_type VARCHAR2(1);
15 l_seg_value VARCHAR2(30);
16 l_error_flag BOOLEAN := FALSE;
17
18 l_stmt varchar2(2000);
19 retcode NUMBER;
20 errbuf VARCHAR2(250);
21
22
23 l_segment_name fnd_id_flex_segments.application_column_name%TYPE;
24
25 segs_array fnd_flex_ext.segmentarray;
26 dummy_array fnd_flex_ext.segmentarray;
27 hdr_segs_array fnd_flex_ext.segmentarray;
28 tmp_hdr_segs_array fnd_flex_ext.segmentarray;
29
30 val_set_id_array fnd_flex_ext.segmentarray;
31
32 g_pub_law_code_flag VARCHAR2(1);
33 g_advance_flag VARCHAR2(1);
34 g_transfer_flag VARCHAR2(1);
35
36 g_advance_type_code fv_lookup_codes.lookup_code%TYPE;
37
38 CURSOR app_col(v_sob_id IN NUMBER) IS
39 SELECT application_column_name,flex_value_set_id
40 FROM fnd_id_flex_segments ffs,
41 gl_ledgers_public_v gsb
42 WHERE ffs.application_id = 101
43 AND ffs.id_flex_code = 'GL#'
44 AND ffs.id_flex_num = gsb.chart_of_accounts_id
45 AND gsb.ledger_id = v_sob_id
46 ORDER BY ffs.segment_num;
47
48 PROCEDURE update_err_code(l_rowid VARCHAR2,
49 l_err_code VARCHAR2,
50 l_err_reason VARCHAR2);
51 PROCEDURE validate_sob(v_sob_id NUMBER);
52 PROCEDURE validate_gl_date(v_gl_date VARCHAR2,
53 v_set_of_books_id NUMBER,
54 v_quarter_num OUT NOCOPY NUMBER);
55 PROCEDURE validate_budget_level(v_set_of_books_id NUMBER,
56 v_budget_level_id NUMBER);
57 PROCEDURE validate_budget_user( p_sob_id NUMBER,
58 p_bu_user_id NUMBER);
59 PROCEDURE validate_bu_access_level( p_sob_id NUMBER,
60 p_bu_user_id NUMBER,
61 p_budget_level_id NUMBER);
62 PROCEDURE validate_fund_value(v_set_of_books_id NUMBER,
63 v_fund_value VARCHAR2,
64 v_budget_level_id NUMBER);
65 PROCEDURE validate_tsymbol_date(v_set_of_books_id NUMBER,
66 v_fund_value VARCHAR2,
67 v_gl_date VARCHAR2);
68 PROCEDURE validate_trx_type_attribs(v_set_of_books_id NUMBER,
69 v_budget_level_id NUMBER,
70 v_trx_type VARCHAR2,
71 v_sub_type VARCHAR2,
72 v_public_law_code VARCHAR2,
73 v_advance_type VARCHAR2,
74 v_dept_id NUMBER,
75 v_main_account NUMBER);
76 PROCEDURE VALIDATE_SUB_TYPE(v_set_of_books_id NUMBER,
77 v_trx_type VARCHAR2,
78 v_budget_level_id NUMBER,
79 v_sub_type VARCHAR2);
80 PROCEDURE validate_doc_number(v_doc_number VARCHAR2,
81 v_set_of_books_id NUMBER,
82 v_fund_value VARCHAR2,
83 v_budget_level_id NUMBER,
84 v_source VARCHAR2);
85 PROCEDURE copy_default_seg_vals(v_set_of_books_id NUMBER,
86 v_fund_value VARCHAR2,
87 v_budget_level_id NUMBER,
88 v_rowid VARCHAR2);
89 PROCEDURE concat_segs(l_array fnd_flex_ext.segmentarray,
90 l_sob_id NUMBER, l_bud_segs OUT NOCOPY VARCHAR2);
91 PROCEDURE update_cleanup(parm_source IN VARCHAR2,
92 parm_group_id IN NUMBER);
93 PROCEDURE update_err_rec(v_rec_number IN NUMBER);
94 PROCEDURE reset_control_status;
95 PROCEDURE validate_dff
96 (
97 v_attribute_category fv_be_interface.attribute_category%TYPE,
98 v_attribute1 fv_be_interface.attribute1%TYPE,
99 v_attribute2 fv_be_interface.attribute2%TYPE,
100 v_attribute3 fv_be_interface.attribute3%TYPE,
101 v_attribute4 fv_be_interface.attribute4%TYPE,
102 v_attribute5 fv_be_interface.attribute5%TYPE,
103 v_attribute6 fv_be_interface.attribute6%TYPE,
104 v_attribute7 fv_be_interface.attribute7%TYPE,
105 v_attribute8 fv_be_interface.attribute8%TYPE,
106 v_attribute9 fv_be_interface.attribute9%TYPE,
107 v_attribute10 fv_be_interface.attribute10%TYPE,
108 v_attribute11 fv_be_interface.attribute11%TYPE,
109 v_attribute12 fv_be_interface.attribute12%TYPE,
110 v_attribute13 fv_be_interface.attribute13%TYPE,
111 v_attribute14 fv_be_interface.attribute14%TYPE,
112 v_attribute15 fv_be_interface.attribute15%TYPE,
113 v_error_mesg OUT NOCOPY VARCHAR2
114 );
115
116 --------------------------------------------------------------------------------
117 PROCEDURE MAIN(p_errbuf OUT NOCOPY VARCHAR2,
118 p_retcode OUT NOCOPY NUMBER,
119 source IN VARCHAR2,
120 group_id IN NUMBER,
121 validation IN VARCHAR2,
122 ledger_id IN NUMBER)
123 IS
124
125 l_module_name VARCHAR2(200);
126 v_status VARCHAR2(25);
127 l_bu_group_id NUMBER(15);
128 l_application_table_name FND_FLEX_VALIDATION_TABLES.application_table_name%type;
129 l_value_column_name FND_FLEX_VALIDATION_TABLES.value_column_name%type;
130 l_table_stmt VARCHAR2(1000);
131 l_validation_type VARCHAR2(2);
132
133 -- Cursor for selecting records from
134 -- fv_be_interface
135 -- BCPSA-BE Enhancement - Modified the cursor to get the Sub_Type instead of Transaction_Code
136 CURSOR int IS
137 SELECT rowid, set_of_books_id, gl_date, record_number,
138 budget_level_id, budgeting_segments, fund_value, doc_number,
139 amount, increase_decrease_flag, transaction_type,
140 sub_type, segment1, segment2, segment3, segment4,
141 segment5, segment6, segment7, segment8, segment9, segment10,
142 segment11, segment12, segment13, segment14, segment15,
143 segment16, segment17, segment18, segment19, segment20,
144 segment21, segment22, segment23, segment24, segment25,
145 segment26, segment27, segment28, segment29, segment30,
146 attribute1, attribute2, attribute3, attribute4, attribute5,
147 attribute6, attribute7, attribute8, attribute9, attribute10,
148 attribute11, attribute12, attribute13, attribute14, attribute15,
149 source, group_id, corrected_flag, public_law_code, advance_type,
150 dept_id, main_account, transfer_description, attribute_category,
151 budget_user_id
152 FROM fv_be_interface
153 WHERE source = parm_source
154 AND group_id = parm_group_id
155 AND set_of_books_id = parm_ledger_id
156 AND status IN ('NEW','REJECTED','ACCEPTED')
157 ORDER BY budget_level_id ;
158
159 -- BCPSA-BE Enhancement - Modified the cursor below to pull the information from FV_BE_ACCOUNT_PAIRS
160 -- instead of GL_USSGL_ACCOUNT_PAIRS table
161
162 CURSOR accounts_cur(p_sub_type IN VARCHAR2,
163 p_ledger_id IN NUMBER) IS
164 SELECT cr_account_segment_value,
165 dr_account_segment_value
166 FROM fv_be_account_pairs acc,
167 fv_be_trx_sub_types tst
168 WHERE acc.be_tt_id = tst.be_tt_id
169 and tst.sub_type =p_sub_type
170 AND chart_of_accounts_id = v_coa_id
171 AND acc.ledger_id = tst.ledger_id
172 AND acc.ledger_id = p_ledger_id;
173
174 l_ret_val VARCHAR2(25);
175 l_val_retcode NUMBER;
176 l_val_errbuf VARCHAR2(250);
177 v_delimiter VARCHAR2(1);
178 dtl_index NUMBER;
179 v_rej_rec_count NUMBER;
180
181 v_amount fv_be_trx_dtls.amount%TYPE;
182 v_tt_id fv_be_transaction_types.be_tt_id%TYPE;
183 v_gl_date fv_be_trx_dtls.gl_date%TYPE;
184 v_quarter_num fv_be_trx_dtls.quarter_num%TYPE;
185 v_doc_id fv_be_trx_hdrs.doc_id%TYPE;
186 v_doc_status fv_be_trx_hdrs.doc_status%TYPE;
187 v_int_rev_num fv_be_trx_hdrs.internal_revision_num%TYPE;
188 v_revision_num fv_be_trx_hdrs.revision_num%TYPE;
189 v_bud_segs fv_be_trx_hdrs.budgeting_segments%TYPE;
190 new_doc_id fv_be_trx_hdrs.doc_id%TYPE;
191 v_ts_id fv_be_trx_hdrs.treasury_symbol_id%TYPE;
192 ins_hdr BOOLEAN := FALSE;
193 v_req_id NUMBER;
194
195 v_segment1 VARCHAR2(25);
196 v_segment2 VARCHAR2(25);
197 v_segment3 VARCHAR2(25);
198 v_segment4 VARCHAR2(25);
199 v_segment5 VARCHAR2(25);
200 v_segment6 VARCHAR2(25);
201 v_segment7 VARCHAR2(25);
202 v_segment8 VARCHAR2(25);
203 v_segment9 VARCHAR2(25);
204 v_segment10 VARCHAR2(25);
205 v_segment11 VARCHAR2(25);
206 v_segment12 VARCHAR2(25);
207 v_segment13 VARCHAR2(25);
208 v_segment14 VARCHAR2(25);
209 v_segment15 VARCHAR2(25);
210 v_segment16 VARCHAR2(25);
211 v_segment17 VARCHAR2(25);
212 v_segment18 VARCHAR2(25);
213 v_segment19 VARCHAR2(25);
214 v_segment20 VARCHAR2(25);
215 v_segment21 VARCHAR2(25);
216 v_segment22 VARCHAR2(25);
217 v_segment23 VARCHAR2(25);
218 v_segment24 VARCHAR2(25);
219 v_segment25 VARCHAR2(25);
220 v_segment26 VARCHAR2(25);
221 v_segment27 VARCHAR2(25);
222 v_segment28 VARCHAR2(25);
223 v_segment29 VARCHAR2(25);
224 v_segment30 VARCHAR2(25);
225
226 v_interface_count NUMBER;
227 v_user_id NUMBER;
228 v_resp_id NUMBER;
229 v_err_code BOOLEAN;
230 acc_seg_name VARCHAR2(25);
231 v_temp_seg_val VARCHAR2(25);
232 validation_failed BOOLEAN;
233 missing_bud_segs BOOLEAN;
234 v_index NUMBER;
235 v_num_segs NUMBER;
236 v_acc_seg_index NUMBER;
237 v_bal_seg_name varchar2(25);
238 l_dff_error_message VARCHAR2(1024);
239 v_source_exists VARCHAR2(10);
240 l_account_count NUMBER;
241 BEGIN
242
243 l_module_name := g_module_name || 'MAIN';
244 parm_source := source;
245 parm_group_id := group_id;
246 parm_ledger_id := ledger_id;
247
248 -- Update the control table with the
249 -- appropriate status
250 UPDATE fv_be_interface_control
251 SET status = 'IN PROCESS',
252 date_processed = SYSDATE
253 WHERE source = parm_source
254 AND group_id = parm_group_id
255 AND status IN ('NEW','REJECTED','ACCEPTED');
256
257 COMMIT;
258
259 -- Count number of records in the interface table
260 -- for the source and group_id parameter. If there are no records found
261 -- for this source and group_id then error and return
262 SELECT count(*)
263 INTO v_interface_count
264 FROM fv_be_interface
265 WHERE source = parm_source
266 AND group_id = parm_group_id
267 AND set_of_books_id = parm_ledger_id
268 AND status IN ('NEW', 'REJECTED','ACCEPTED');
269
270 IF v_interface_count > 0
271 THEN
272 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
273 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THERE ARE '||TO_CHAR(V_INTERFACE_COUNT)||
274 ' record(s) for Import process');
275 END IF;
276 END IF;
277
278 IF v_interface_count = 0
279 THEN
280 errbuf := 'No records found for source: '||parm_source||
281 ' and Group ID: '||parm_group_id||
282 ' in the interface table!.';
283 retcode := -1;
284 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'message1_1',errbuf);
285 reset_control_status;
286 p_retcode := retcode;
287 p_errbuf := errbuf;
288 RETURN;
289 END IF;
290
291
292 -- Reset the error_code
293 -- and error_reason to null
294 UPDATE fv_be_interface
295 SET error_code = NULL,
296 error_reason = NULL
297 WHERE source = parm_source
298 AND group_id = parm_group_id
299 AND set_of_books_id = parm_ledger_id;
300
301
302 FOR int_rec IN int
303
304 -- Two loops are used to skip a record in error
305 -- and continue processing the next record
306 -- If a value is invalid then
307 -- update the current record with appropriate error
308 -- code and reason, skip the record in error and
309 -- continue processing from the beginning for the
310 -- next record
311 LOOP -- First
312 LOOP -- Second
313
314 -- reset error codes
315 v_error_code := 0;
316 retcode := NULL;
317 errbuf := NULL;
318
319 -- reset arrays
320 segs_array.DELETE;
321 val_set_id_array.DELETE;
322
323 --------------------------------------------------------------------------------
324 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
325 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'------------------------------------------------');
326 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START VALIDATING REC#: '||INT_REC.RECORD_NUMBER);
327 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'VALIDATING SET OF BOOKS ID: '||
328 int_rec.set_of_books_id);
329 END IF;
330 validate_sob(int_rec.set_of_books_id);
331 IF retcode <> 0 THEN
332 p_retcode := retcode;
333 p_errbuf := errbuf;
334 ROLLBACK;
335 RETURN;
336 END IF;
337 IF v_error_code <> 0 THEN
338 update_err_code(int_rec.rowid,'EM03',
339 'Invalid Set of Books ID');
340 -- exit second loop to continue processing next
341 -- record
342 EXIT;
343 END IF;
344 --------------------------------------------------------------------------------
345 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
346 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'VALIDATING GL DATE: '||
347 int_rec.gl_date);
348 END IF;
349 validate_gl_date(int_rec.gl_date,
350 int_rec.set_of_books_id,
351 v_quarter_num);
352 IF retcode <> 0 THEN
353 p_retcode := retcode;
354 p_errbuf := errbuf;
355 ROLLBACK;
356 RETURN;
357 END IF;
358 IF v_error_code <> 0 THEN
359 update_err_code(int_rec.rowid,'EP06', 'Invalid GL Date');
360 EXIT;
361 END IF;
362 --------------------------------------------------------------------------------
363 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
364 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'VALIDATING BUDGET LEVEL ID: '||
365 int_rec.budget_level_id);
366 END IF;
367 validate_budget_level(int_rec.set_of_books_id,
368 int_rec.budget_level_id);
369 IF retcode <> 0 THEN
370 p_retcode := retcode;
371 p_errbuf := errbuf;
372 ROLLBACK;
373 RETURN;
374 END IF;
375 IF v_error_code <> 0 THEN
376 update_err_code(int_rec.rowid, 'EM29',
377 'Invalid Budget Level');
378 EXIT;
379 END IF;
380 -----------------------------------------------------------------------------------------------
381 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
382 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'VALIDATING BUDGET USER: '||
383 int_rec.budget_user_id);
384 END IF;
385 validate_budget_user( int_rec.set_of_books_id,
386 int_rec.budget_user_id);
387 IF retcode <> 0 THEN
388 p_retcode := retcode;
389 p_errbuf := errbuf;
390 ROLLBACK;
391 RETURN;
392 END IF;
393 IF v_error_code <> 0 THEN
394 update_err_code(int_rec.rowid, 'EU01',
395 'Invalid Budget User');
396 EXIT;
397 END IF;
398 -----------------------------------------------------------------------------------------------
399 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
400 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'VALIDATING BUDGET USER ACCESS : '||
401 int_rec.budget_user_id || ' Budget Level Id => ' ||int_rec.budget_level_id );
402 END IF;
403 validate_bu_access_level( int_rec.set_of_books_id,
404 int_rec.budget_user_id,
405 int_rec.budget_level_id);
406 IF retcode <> 0 THEN
407 p_retcode := retcode;
408 p_errbuf := errbuf;
409 ROLLBACK;
410 RETURN;
411 END IF;
412 IF v_error_code <> 0 THEN
413 update_err_code(int_rec.rowid, 'EU02',
414 'Insufficient Access for Budget User');
415 EXIT;
416 END IF;
417
418
419 --------------------------------------------------------------------------------
420 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
421 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'VALIDATING FUND VALUE: '||
422 int_rec.fund_value);
423 END IF;
424 validate_fund_value(int_rec.set_of_books_id,
425 int_rec.fund_value, int_rec.budget_level_id);
426 IF retcode <> 0 THEN
427 p_retcode := retcode;
428 p_errbuf := errbuf;
429 ROLLBACK;
430 RETURN;
431 END IF;
432 IF v_error_code <> 0 THEN
433 update_err_code(int_rec.rowid, 'EM33',
434 'Fund Value not defined in Budget Distributions');
435 EXIT;
436 END IF;
437 --------------------------------------------------------------------------------
438 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
439 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
440 'Validating Treasury Symbol date for fund value: '||
441 int_rec.fund_value);
442 END IF;
443 validate_tsymbol_date(int_rec.set_of_books_id,
444 int_rec.fund_value,
445 int_rec.gl_date);
446 IF retcode <> 0 THEN
447 p_retcode := retcode;
448 p_errbuf := errbuf;
449 ROLLBACK;
450 RETURN;
451 END IF;
452 IF v_error_code <> 0 THEN
453 update_err_code(int_rec.rowid, 'EM34',
454 'Cancelled or Expired Treasury Symbol');
455 EXIT;
456 END IF;
457 --------------------------------------------------------------------------------
458 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
459 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'VALIDATING INCREASE/DECREASE FLAG: '||
460 int_rec.increase_decrease_flag);
461 END IF;
462
463 IF nvl(int_rec.increase_decrease_flag,' ') NOT IN ('I','D')
464 THEN
465 update_err_code(int_rec.rowid, 'EM35',
466 'Invalid Increase / Decrease Flag');
467 EXIT;
468 END IF;
469 --------------------------------------------------------------------------------
470 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
471 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'VALIDATING AMOUNT: '||INT_REC.AMOUNT);
472 END IF;
473 IF int_rec.amount < 0 THEN
474 update_err_code(int_rec.rowid, 'EM36',
475 'Amount must be equal to or greater than zero');
476 EXIT;
477 END IF;
478 --------------------------------------------------------------------------------
479 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
480 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'VALIDATING TRANSACTION TYPE: '||
481 int_rec.transaction_type);
482 END IF;
483 validate_trx_type_attribs(int_rec.set_of_books_id,
484 int_rec.budget_level_id,
485 int_rec.transaction_type,
486 int_rec.sub_type,
487 int_rec.public_law_code,
488 int_rec.advance_type,
489 int_rec.dept_id,
490 int_rec.main_account);
491 IF retcode <> 0 THEN
492 p_retcode := retcode;
493 p_errbuf := errbuf;
494 ROLLBACK;
495 RETURN;
496 END IF;
497 IF v_error_code = -6 THEN
498 update_err_code(int_rec.rowid, 'EM45',
499 'Public Law Code should not be more than 7 characters');
500 EXIT;
501 ELSIF v_error_code = -7 THEN
502 update_err_code(int_rec.rowid, 'EM46',
503 'Invalid Advance Type');
504 EXIT;
505 ELSIF v_error_code = -8 THEN
506 update_err_code(int_rec.rowid, 'EM47',
507 'Invalid Transfer Dept ID and/or Transfer Main Account');
508 EXIT;
509 ELSIF v_error_code = -9 THEN
510 update_err_code(int_rec.rowid, 'EM28',
511 'Invalid Transaction Type');
512 EXIT;
513 ELSIF v_error_code = -10 THEN
514 update_err_code(int_rec.rowid, 'EM10',
515 'Invalid Sub Type');
516 EXIT;
517 END IF;
518 --------------------------------------------------------------------------------
519 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
520 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'VALIDATING SUB TYPE: '||
521 int_rec.sub_type);
522 END IF;
523 VALIDATE_SUB_TYPE(int_rec.set_of_books_id,
524 int_rec.transaction_type,
525 int_rec.budget_level_id,
526 int_rec.sub_type);
527
528 IF retcode <> 0 THEN
529 p_errbuf := errbuf;
530 p_retcode := retcode;
531 ROLLBACK;
532 RETURN;
533 END IF;
534 IF v_error_code <> 0 THEN
535 update_err_code(int_rec.rowid, 'EM10',
536 'Invalid Sub Type');
537 EXIT;
538 END IF;
539 --------------------------------------------------------------------------------
540 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
541 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'VALIDATING DOCUMENT NUMBER: '||
542 int_rec.doc_number);
543 END IF;
544 validate_doc_number(int_rec.doc_number,
545 int_rec.set_of_books_id,
546 int_rec.fund_value,
547 int_rec.budget_level_id,
548 int_rec.source);
549
550 IF retcode <> 0 THEN
551 p_retcode := retcode;
552 p_errbuf := errbuf;
553 ROLLBACK;
554 RETURN;
555 END IF;
556 IF v_error_code <> 0 THEN
557 IF v_error_code = -8 THEN
558 update_err_code(int_rec.rowid, 'EM39',
559 'Previously existing document with same document
560 number has not been approved');
561 ELSIF
562 v_error_code = -9 THEN
563 update_err_code(int_rec.rowid, 'EM40',
564 'Document Number must be numeric');
565 ELSIF
566 v_error_code = -7 THEN
567 update_err_code(int_rec.rowid, 'EM44',
568 'Fund Value is not the same for document number,
569 set of books, source and budget level');
570 END IF;
571 EXIT;
572 END IF;
573 --------------------------------------------------------------------------------
574 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
575 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'VALIDATING DFFs');
576 END IF;
577 IF (validation = 'W') THEN
578 validate_dff
579 (
580 int_rec.attribute_category,
581 int_rec.attribute1,
582 int_rec.attribute2,
583 int_rec.attribute3,
584 int_rec.attribute4,
585 int_rec.attribute5,
586 int_rec.attribute6,
587 int_rec.attribute7,
588 int_rec.attribute8,
589 int_rec.attribute9,
590 int_rec.attribute10,
591 int_rec.attribute11,
592 int_rec.attribute12,
593 int_rec.attribute13,
594 int_rec.attribute14,
595 int_rec.attribute15,
596 l_dff_error_message
597 );
598
599 IF retcode <> 0 THEN
600 p_retcode := retcode;
601 p_errbuf := errbuf;
602 ROLLBACK;
603 RETURN;
604 END IF;
605 IF v_error_code <> 0 THEN
606 update_err_code(int_rec.rowid,'ED01', SUBSTR(l_dff_error_message, 1, 255));
607 EXIT;
608 END IF;
609 END IF;
610
611 --------------------------------------------------------------------------------
612 -- load segment table with the segment values
613 segs_array(1) := int_rec.segment1;
614 segs_array(2) := int_rec.segment2;
615 segs_array(3) := int_rec.segment3;
616 segs_array(4) := int_rec.segment4;
617 segs_array(5) := int_rec.segment5;
618 segs_array(6) := int_rec.segment6;
619 segs_array(7) := int_rec.segment7;
620 segs_array(8) := int_rec.segment8;
621 segs_array(9) := int_rec.segment9;
622 segs_array(10) := int_rec.segment10;
623 segs_array(11) := int_rec.segment11;
624 segs_array(12) := int_rec.segment12;
625 segs_array(13) := int_rec.segment13;
626 segs_array(14) := int_rec.segment14;
627 segs_array(15) := int_rec.segment15;
628 segs_array(16) := int_rec.segment16;
629 segs_array(17) := int_rec.segment17;
630 segs_array(18) := int_rec.segment18;
631 segs_array(19) := int_rec.segment19;
632 segs_array(20) := int_rec.segment20;
633 segs_array(21) := int_rec.segment21;
634 segs_array(22) := int_rec.segment22;
635 segs_array(23) := int_rec.segment23;
636 segs_array(24) := int_rec.segment24;
637 segs_array(25) := int_rec.segment25;
638 segs_array(26) := int_rec.segment26;
639 segs_array(27) := int_rec.segment27;
640 segs_array(28) := int_rec.segment28;
641 segs_array(29) := int_rec.segment29;
642 segs_array(30) := int_rec.segment30;
643
644 -- Replace segment values having segment type 'N' with
645 -- default segment values for that budget level
646 BEGIN
647 FOR app_col_name IN app_col(int_rec.set_of_books_id)
648 LOOP
649 l_seg_type := NULL;
650 l_seg_value := NULL;
651
652 l_stmt:=
653 'SELECT '||app_col_name.application_column_name||'_TYPE,'||
654 app_col_name.application_column_name||
655 ' FROM fv_budget_distribution_dtl
656 WHERE set_of_books_id = :set_of_books_id
657 AND budget_level_id = :budget_level_id
658 AND fund_value = :fund_value ';
659
660 EXECUTE IMMEDIATE l_stmt INTO l_seg_type, l_seg_value
661 USING int_rec.set_of_books_id, int_rec.budget_level_id,
662 int_rec.fund_value ;
663
664 -- R12 - the Segment value 'N' has been replaced with 'D'
665 -- Check if the segment type is D, If the segment type is
666 -- D, then replace the segment value with the default
667 -- segment value
668 IF (l_seg_type = 'D')
669 THEN
670 segs_array(substr(app_col_name.application_column_name,8))
671 := l_seg_value;
672 END IF;
673 END LOOP;
674 EXCEPTION
675 WHEN NO_DATA_FOUND THEN
676 errbuf := 'No Data Found error occurred while copying
677 default segment values';
678 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'message1',errbuf);
679 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'NO DATA FOUND ERROR OCCURRED
680 while copying default segment values');
681 retcode := -1;
682 p_retcode := retcode;
683 p_errbuf := errbuf;
684 reset_control_status;
685 ROLLBACK;
686 RETURN;
687 WHEN OTHERS THEN
688 errbuf := substr(sqlerrm,1,100)||':When others error occurred
689 while copying default segment values';
690 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'message2',errbuf);
691 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,SUBSTR(SQLERRM,1,100)||':WHEN OTHERS ERROR
692 occurred while copying default segment values');
693 retcode := -1;
694 p_retcode := retcode;
695 p_errbuf := errbuf;
696 reset_control_status;
697 ROLLBACK;
698 RETURN;
699 END; -- Copy default values
700
701 -- get chart of accounts id
702 SELECT chart_of_accounts_id
703 INTO v_coa_id
704 FROM gl_ledgers_public_v
705 WHERE ledger_id = int_rec.set_of_books_id;
706
707 --------------------------------------------------------------------------------
708 -- Validate mandatory segments. Check if the segment values,
709 -- for segments which have segment type of Y, are provided
710 -- in the interface record.
711
712 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
713 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'VALIDATING MANDATORY SEGMENTS');
714 END IF;
715 BEGIN
716 FOR app_col_rec IN app_col(int_rec.set_of_books_id)
717 LOOP
718 -- Load the flex value set id for validating
719 -- the segment values
720 val_set_id_array(substr(app_col_rec.application_column_name,8))
721 := app_col_rec.flex_value_set_id;
722
723 l_stmt:=
724 'SELECT '||app_col_rec.application_column_name||'_TYPE
725 FROM fv_budget_distribution_dtl
726 WHERE set_of_books_id = :set_of_books_id
727 AND budget_level_id = :budget_level_id
728 AND fund_value = :fund_value ';
729
730 EXECUTE IMMEDIATE l_stmt INTO l_seg_type
731 USING int_rec.set_of_books_id,
732 int_rec.budget_level_id, int_rec.fund_value;
733
734 l_error_flag := FALSE;
735 l_seg_value :=
736 segs_array(substr(app_col_rec.application_column_name,8));
737
738 -- R12 - the Segment value 'Y' has been replaced with 'E'
739 -- Check if the segment type is E and the segment
740 -- has a value. If the segment type is E and segment
741 -- value is null then update the record as error and
742 -- exit (no need to validate the remaining segments)
743
744 IF (l_seg_type = 'E' AND l_seg_value IS NULL)
745 THEN
746 update_err_code(int_rec.rowid,'EM31',
747 'There are more segments required for this budget level');
748 -- if any of the segments are in error
749 -- then no need to check for other segments
750 l_error_flag := TRUE;
751 EXIT;
752 END IF;
753 END LOOP;
754 EXCEPTION WHEN OTHERS THEN
755 errbuf := 'When others error while validating mandatory segments.'||SQLERRM;
756 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'message3',errbuf);
757 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'WHEN OTHERS ERROR WHILE VALIDATING
758 mandatory segments');
759 retcode := -1;
760 p_retcode := retcode;
761 p_errbuf := errbuf;
762 ROLLBACK;
763 reset_control_status;
764 RETURN;
765 END;
766
767 -- if there is an error in any of the segments
768 -- then skip the current record and go to next record
769 IF l_error_flag
770 THEN EXIT;
771 END IF;
772 -------------------------------------------------------------------------------
773 -- Validate segment values where the segment type is 'Y' (need not validate
774 -- segment values where segment type is 'N' since default values are copied
775 -- which already exist in the fv tables and hence have been validated) i.e.,
776 -- check whether the values exist in fnd_flex_values before cross validation.
777 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
778 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'VALIDATING SEGMENT VALUES');
779 END IF;
780
781 BEGIN
782 FOR app_col_rec IN app_col(int_rec.set_of_books_id)
783 LOOP
784
785 l_seg_type := NULL;
786
787 l_stmt:=
788 'SELECT '||app_col_rec.application_column_name||'_TYPE
789 FROM fv_budget_distribution_dtl
790 WHERE set_of_books_id = :set_of_books_id
791 AND budget_level_id = :budget_level_id
792 AND fund_value = :fund_value ';
793
794 EXECUTE IMMEDIATE l_stmt INTO l_seg_type
795 USING int_rec.set_of_books_id,
796 int_rec.budget_level_id, int_rec.fund_value;
797
798 IF l_seg_type = 'E' THEN
799
800
801 SELECT validation_type
802 into l_validation_type
803 FROM FND_FLEX_VALUE_SETS
804 WHERE flex_value_set_id=val_set_id_array(substr(app_col_rec.application_column_name,8,2));
805
806
807 IF l_validation_type ='F' THEN
808 SELECT
809 application_table_name,
810 value_column_name
811 INTO l_application_table_name,l_value_column_name
812 FROM FND_FLEX_VALIDATION_TABLES
813 WHERE flex_value_set_id=val_set_id_array(substr(app_col_rec.application_column_name,8,2));
814
815 l_table_stmt := ' SELECT 1 FROM '||l_application_table_name ||
816 ' WHERE ' || l_value_column_name ||' = :b_seg_value' ;
817
818
819 EXECUTE IMMEDIATE l_table_stmt INTO v_exists
820 USING segs_array(substr(app_col_rec.application_column_name,8,2)) ;
821
822 ELSE
823
824 SELECT 'x'
825 INTO v_exists
826 FROM fnd_flex_values
827 WHERE flex_value_set_id = val_set_id_array(substr(app_col_rec.application_column_name,8,2))
828 AND flex_value = segs_array(substr(app_col_rec.application_column_name,8,2))
829 AND enabled_flag = 'Y';
830 END IF;
831
832
833 END IF;
834 END LOOP;
835 EXCEPTION
836 WHEN NO_DATA_FOUND THEN
837 update_err_code(int_rec.rowid, 'EM37',
838 'Invalid Segment values');
839 -- exit second loop to continue processing next
840 -- record
841 EXIT;
842 WHEN OTHERS THEN
843 errbuf := 'When others error while validating segment values.'||SQLERRM;
844 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'message3_1',errbuf);
845 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'WHEN OTHERS ERROR WHILE VALIDATING
846 segment values');
847 retcode := -1;
848 p_retcode := retcode;
849 p_errbuf := errbuf;
850 ROLLBACK;
851 reset_control_status;
852 RETURN;
853 END;
854 -------------------------------------------------------------------------------
855 -- If the budget level is 1 then substitute the accounting segment value with
856 -- the value derived from gl_ussgl_accounting_pairs, once for debit_segment
857 -- value and once for credit_segment_value and then submit each for cross
858 -- validation.
859 -- If the budget level is not 1 then break up budgeting_segments into an array,
860 -- validate the segment values if the segment type is 'E' and copy the default
861 -- segment values into the array if the segment type is 'D'. Then substitute
862 -- accounting segment with dr_account_segment_value derived from the
863 -- transaction code. Use this as the header array for checking cross validation.
864 -- This is being done because the interface record contains the transaction
865 -- code which will be used for cross validation.
866 --------------------------------------------------------------------------------
867 v_user_id := fnd_global.user_id;
868 v_resp_id := fnd_global.resp_id;
869
870 /* this is no longer being used in R12
871 fv_utility.get_context(v_user_id, v_resp_id, 'ACCT_SEGMENT',
872 acc_seg_name, v_err_code, errbuf);
873 implementing new r12 call below */
874 fv_utility.get_segment_col_names(v_coa_id, acc_seg_name, v_bal_seg_name,
875 v_err_code,errbuf);
876
877 IF v_err_code THEN
878 retcode := -1;
879 errbuf := 'Error when getting accounting segment';
880 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'message4',errbuf);
881 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'ERROR WHEN GETTING ACCOUNTING SEGMENT');
882 p_retcode := retcode;
883 p_errbuf := errbuf;
884 ROLLBACK;
885 reset_control_status;
886 RETURN;
887 END IF;
888
889 -- Initialize the flag being used in the loop below
890 validation_failed := FALSE;
891 l_account_count := 0;
892
893 FOR trans_code IN accounts_cur(int_rec.sub_type,
894 int_rec.set_of_books_id)
895 LOOP
896 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,
897 l_module_name,'dr account: '||trans_code.dr_account_segment_value);
898 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,
899 l_module_name,'cr account: '||trans_code.cr_account_segment_value);
900
901 l_account_count := l_account_count + 1;
902
903 v_temp_seg_val := NULL;
904
905 IF int_rec.budget_level_id = 1 THEN
906 FOR i IN 1..2
907 LOOP
908
909 IF i = 1 THEN
910 v_temp_seg_val := trans_code.dr_account_segment_value;
911 ELSE
912 v_temp_seg_val := trans_code.cr_account_segment_value;
913 END IF;
914
915 segs_array(substr(acc_seg_name,8,2)) := v_temp_seg_val;
916
917 -- Checking cross-validation
918 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
919 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CHECKING CROSS VALIDATION');
920 END IF;
921 FV_BE_UTIL_PKG.check_cross_validation(l_val_errbuf,
922 l_val_retcode,v_coa_id, segs_array, segs_array,
923 int_rec.budget_level_id, v_tt_id,int_rec.sub_type,
924 int_rec.source, int_rec.increase_decrease_flag);
925
926 -- If a value fails validation then set
927 -- validation_failed to true and exit the current
928 -- loop
929 IF (l_val_retcode = 2)
930 THEN
931 validation_failed := TRUE;
932 EXIT;
933 END IF;
934
935 END LOOP;
936
937 ELSE -- if budget_level_id is not 1
938
939 BEGIN
940 SELECT concatenated_segment_delimiter
941 INTO v_delimiter
942 FROM fnd_id_flex_structures ffs,
943 gl_ledgers_public_v gsb
944 WHERE application_id = 101
945 AND id_flex_code = 'GL#'
946 AND ffs.id_flex_num = gsb.chart_of_accounts_id
947 AND gsb.ledger_id = int_rec.set_of_books_id;
948
949 v_num_segs := fnd_flex_ext.breakup_segments(int_rec.budgeting_segments, v_delimiter,
950 tmp_hdr_segs_array);
951 FOR I IN 1..30 LOOP
952 hdr_segs_array(I) := NULL;
953 END LOOP;
954 v_index := 0;
955 FOR cols_rec IN (SELECT application_column_name
956 FROM fnd_id_flex_segments
957 WHERE id_flex_code = 'GL#'
958 AND id_flex_num = v_coa_id
959 ORDER BY segment_num)
960 LOOP
961 v_index := v_index + 1;
962 hdr_segs_array(substr(rtrim(cols_rec.application_column_name),8)) := tmp_hdr_segs_array(v_index);
963 END LOOP;
964
965 EXCEPTION
966 WHEN NO_DATA_FOUND THEN
967 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'WHEN NO DATA FOUND EXCEPTION WHILE GETTING
968 delimiter');
969 errbuf := 'When no data found while getting delimiter';
970 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'message5',errbuf);
971 retcode := -1;
972 p_retcode := retcode;
973 p_errbuf := errbuf;
974 reset_control_status;
975 ROLLBACK;
976 RETURN;
977 WHEN OTHERS THEN
978 errbuf := 'When others exception while getting delimiter'||SQLERRM;
979 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'WHEN OTHERS EXCEPTION WHILE GETTING
980 delimiter');
981 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'message6',errbuf);
982 retcode := -1;
983 p_retcode := retcode;
984 p_errbuf := errbuf;
985 reset_control_status;
986 ROLLBACK;
987 RETURN;
988 END;
989
990 v_acc_seg_index := 0;
991 v_index := 0;
992
993 FOR app_col_rec IN app_col(int_rec.set_of_books_id)
994 LOOP
995
996 l_seg_type := NULL;
997
998 l_stmt:=
999 'SELECT '||app_col_rec.application_column_name||'_TYPE
1000 FROM fv_budget_distribution_dtl
1001 WHERE set_of_books_id = :set_of_books_id
1002 AND fund_value = :fund_value
1003 AND budget_level_id =
1004 (SELECT MAX(budget_level_id)
1005 FROM fv_budget_distribution_dtl
1006 WHERE fund_value = :fund_value
1007 AND set_of_books_id = :set_of_books_id
1008 AND budget_level_id < :budget_level_id )';
1009
1010 EXECUTE IMMEDIATE l_stmt INTO l_seg_type
1011 USING int_rec.set_of_books_id, int_rec.fund_value,
1012 int_rec.fund_value, int_rec.set_of_books_id,
1013 int_rec.budget_level_id ;
1014
1015 v_index := v_index + 1;
1016
1017 IF acc_seg_name = app_col_rec.application_column_name
1018 THEN
1019 v_acc_seg_index := v_index;
1020 END IF;
1021
1022 IF l_seg_type = 'E' THEN
1023 IF hdr_segs_array(substr(rtrim(app_col_rec.application_column_name),8)) IS NULL
1024 THEN
1025 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1026 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'MISSING BUDGETARY SEGMENT');
1027 END IF;
1028 missing_bud_segs := TRUE;
1029 EXIT;
1030 END IF;
1031 END IF;
1032
1033 IF l_seg_type = 'D'
1034 THEN
1035
1036 l_stmt:=
1037 'SELECT '||app_col_rec.application_column_name||
1038 ' FROM fv_budget_distribution_dtl
1039 WHERE set_of_books_id = :set_of_books_id
1040 AND fund_value = :fund_value
1041 AND budget_level_id =
1042 (SELECT MAX(budget_level_id)
1043 FROM fv_budget_distribution_dtl
1044 WHERE fund_value = :fund_value
1045 AND set_of_books_id = :set_of_books_id
1046 AND budget_level_id < :budget_level_id )';
1047
1048 EXECUTE IMMEDIATE l_stmt INTO hdr_segs_array(substr(rtrim(app_col_rec.application_column_name),8))
1049 USING int_rec.set_of_books_id, int_rec.fund_value,
1050 int_rec.fund_value, int_rec.set_of_books_id,
1051 int_rec.budget_level_id ;
1052
1053 END IF;
1054 END LOOP; -- app_col_rec
1055
1056 hdr_segs_array(substr(acc_seg_name,8,2)) :=
1057 trans_code.dr_account_segment_value;
1058
1059 segs_array(substr(acc_seg_name,8,2)) :=
1060 trans_code.cr_account_segment_value;
1061
1062 -- Checking cross-validation for budget_level other than 1
1063 FV_BE_UTIL_PKG.check_cross_validation(l_val_errbuf,
1064 l_val_retcode,v_coa_id, segs_array, segs_array,
1065 int_rec.budget_level_id, v_tt_id,int_rec.sub_type,
1066 int_rec.source, int_rec.increase_decrease_flag);
1067
1068 -- If a
1069 -- If a value fails validation then set
1070 -- validation_failed to true and exit the current
1071 -- loop
1072 IF (l_val_retcode = 2)
1073 THEN
1074 validation_failed := TRUE;
1075 EXIT;
1076 END IF;
1077
1078
1079 END IF; -- if budget_level_id = 1
1080 END LOOP; -- trans_code
1081
1082 IF l_account_count = 0 THEN
1083 retcode := -1;
1084 errbuf :=
1085 'Budget transaction sub type must have dr and cr accounts!';
1086 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
1087 l_module_name||'message4',errbuf);
1088 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
1089 l_module_name,'ERROR GETTING Dr AND Cr ACCOUNTING SEGMENTS');
1090 p_retcode := retcode;
1091 p_errbuf := errbuf;
1092 ROLLBACK;
1093 reset_control_status;
1094 RETURN;
1095 END IF;
1096 -- If cross validation failed OR any values missing from budgeting
1097 -- segments then exit the current record and continue processing the
1098 -- next record
1099 IF validation_failed
1100 THEN
1101 update_err_code(int_rec.rowid,'EM43',
1102 'Segments failed cross validation');
1103 EXIT;
1104 END IF;
1105
1106 IF missing_bud_segs
1107 THEN
1108 update_err_code(int_rec.rowid,'EM41',
1109 'Missing segment value in budgeting segments');
1110 EXIT;
1111 END IF;
1112
1113 -------------------------------------------------------------------------------
1114 -- Since no more validations are needed, update
1115 -- the status of this record to accepted,
1116 -- exit the loop and go to next rec, if any
1117 UPDATE fv_be_interface
1118 SET status = 'ACCEPTED',
1119 processed_flag = 'Y'
1120 WHERE rowid = int_rec.rowid ;
1121
1122 EXIT;
1123
1124 END LOOP; -- Second
1125 END LOOP; -- First
1126
1127 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1128 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'****** VALIDATION COMPLETE ******');
1129 END IF;
1130 --------------------------------------------------------------------------------
1131 -- Validation of all records are complete
1132 -- If any records are rejected, update the control table
1133 -- and exit, else continue processing
1134
1135 v_rej_rec_count := 0;
1136
1137 SELECT count(*)
1138 INTO v_rej_rec_count
1139 FROM fv_be_interface
1140 WHERE group_id = parm_group_id
1141 AND source = parm_source
1142 AND set_of_books_id = parm_ledger_id
1143 AND status = 'REJECTED';
1144
1145 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1146 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'****** RECORDS REJECTED :'||
1147 v_rej_rec_count||' ******');
1148 END IF;
1149
1150 IF v_rej_rec_count > 0
1151 THEN
1152 reset_control_status;
1153
1154 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1155 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'SUBMITTING BE TRANSACTIONS IMPORT REPORT');
1156 END IF;
1157
1158 v_req_id := FND_REQUEST.SUBMIT_REQUEST
1159 ('FV','FVBEINTR','','',FALSE, parm_ledger_id, parm_source, parm_group_id);
1160
1161 -- If the request submission fails, then abort process
1162 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1163 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'REQEST ID FOR REPORT = '||
1164 to_char(v_req_id)) ;
1165 END IF;
1166
1167 IF (v_req_id = 0)
1168 THEN
1169 errbuf := 'Unable to submit BE Transactions Import Report';
1170 retcode := -1;
1171 p_retcode := retcode;
1172 p_errbuf := errbuf;
1173 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'message7',errbuf);
1174 ROLLBACK;
1175 reset_control_status;
1176 RETURN;
1177 END IF;
1178
1179
1180 END IF;
1181 -- If all records are accepted then process them
1182 IF v_rej_rec_count = 0
1183 THEN
1184
1185 FOR valid_rec IN int
1186 LOOP
1187
1188 l_stmt := NULL;
1189 l_seg_type := NULL;
1190 v_doc_status := NULL;
1191 v_doc_id := NULL;
1192 v_revision_num := NULL;
1193 v_int_rev_num := NULL;
1194 ins_hdr := FALSE;
1195
1196 -- copy interface segment values into the array
1197 -- for concatenation (to create budgeting segments)
1198 segs_array.DELETE;
1199
1200 segs_array(1) := valid_rec.segment1;
1201 segs_array(2) := valid_rec.segment2;
1202 segs_array(3) := valid_rec.segment3;
1203 segs_array(4) := valid_rec.segment4;
1204 segs_array(5) := valid_rec.segment5;
1205 segs_array(6) := valid_rec.segment6;
1206 segs_array(7) := valid_rec.segment7;
1207 segs_array(8) := valid_rec.segment8;
1208 segs_array(9) := valid_rec.segment9;
1209 segs_array(10) := valid_rec.segment10;
1210 segs_array(11) := valid_rec.segment11;
1211 segs_array(12) := valid_rec.segment12;
1212 segs_array(13) := valid_rec.segment13;
1213 segs_array(14) := valid_rec.segment14;
1214 segs_array(15) := valid_rec.segment15;
1215 segs_array(16) := valid_rec.segment16;
1216 segs_array(17) := valid_rec.segment17;
1217 segs_array(18) := valid_rec.segment18;
1218 segs_array(19) := valid_rec.segment19;
1219 segs_array(20) := valid_rec.segment20;
1220 segs_array(21) := valid_rec.segment21;
1221 segs_array(22) := valid_rec.segment22;
1222 segs_array(23) := valid_rec.segment23;
1223 segs_array(24) := valid_rec.segment24;
1224 segs_array(25) := valid_rec.segment25;
1225 segs_array(26) := valid_rec.segment26;
1226 segs_array(27) := valid_rec.segment27;
1227 segs_array(28) := valid_rec.segment28;
1228 segs_array(29) := valid_rec.segment29;
1229 segs_array(30) := valid_rec.segment30;
1230 begin
1231 select 'x' into v_source_exists
1232 from fv_lookup_codes
1233 where lookup_type='BE_SOURCE'
1234 AND lookup_code=valid_rec.source;
1235
1236 exception
1237 WHEN NO_DATA_FOUND THEN
1238 valid_rec.source:= 'OTHER' ;
1239 end;
1240
1241 -- Replace segment values having segment type 'N' with
1242 -- default segment values for that budget level
1243 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1244 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'REPLACING SEGMENT VALUES WITH DEFAULT
1245 segment values before inserting');
1246 END IF;
1247 BEGIN
1248 FOR app_col_name IN app_col(valid_rec.set_of_books_id)
1249 LOOP
1250 l_seg_type := NULL;
1251 l_seg_value := NULL;
1252
1253 l_stmt:=
1254 'SELECT '||app_col_name.application_column_name||'_TYPE,'||
1255 app_col_name.application_column_name||
1256 ' FROM fv_budget_distribution_dtl
1257 WHERE set_of_books_id = :set_of_books_id
1258 AND budget_level_id = :budget_level_id
1259 AND fund_value = :fund_value ';
1260
1261 EXECUTE IMMEDIATE l_stmt INTO l_seg_type, l_seg_value
1262 USING valid_rec.set_of_books_id,
1263 valid_rec.budget_level_id,
1264 valid_rec.fund_value ;
1265
1266 -- Check if the segment type is N. If the segment type is
1267 -- N, then replace the segment value with the default
1268 -- segment value
1269
1270 IF (l_seg_type = 'D')
1271 THEN
1272 segs_array(substr(app_col_name.application_column_name,8)):= l_seg_value;
1273 END IF;
1274 END LOOP;
1275 EXCEPTION
1276 WHEN NO_DATA_FOUND THEN
1277 errbuf := 'No Data Found error occurred while copying default segment values';
1278 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'message8',errbuf);
1279 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'NO DATA FOUND ERROR OCCURRED
1280 while copying default segment values');
1281 retcode := -1;
1282 p_retcode := retcode;
1283 p_errbuf := errbuf;
1284 reset_control_status;
1285 ROLLBACK;
1286 RETURN;
1287
1288 WHEN OTHERS THEN
1289 errbuf := substr(sqlerrm,1,100)||':When others error occurred while copying default segment values';
1290 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'message9',errbuf);
1291 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,SUBSTR(SQLERRM,1,100)||':WHEN OTHERS ERROR
1292 occurred while copying default segment values');
1293 retcode := -1;
1294 p_retcode := retcode;
1295 p_errbuf := errbuf;
1296 reset_control_status;
1297 ROLLBACK;
1298 RETURN;
1299 END; -- Copy default values
1300 -- concatenate segments for budgeting segments
1301 concat_segs(segs_array, valid_rec.set_of_books_id,
1302 v_bud_segs);
1303
1304 SELECT be_tt_id,
1305 public_law_code_flag,
1306 advance_flag,
1307 transfer_flag
1308 INTO v_tt_id,
1309 g_pub_law_code_flag,
1310 g_advance_flag,
1311 g_transfer_flag
1312 FROM fv_be_transaction_types
1313 WHERE set_of_books_id = valid_rec.set_of_books_id
1314 AND budget_level_id = valid_rec.budget_level_id
1315 AND apprn_transaction_type = valid_rec.transaction_type;
1316
1317 -- check if document number exists
1318 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1319 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CHECKING IF DOC :'||VALID_REC.DOC_NUMBER||
1320 ' exists for fund '||valid_rec.fund_value||
1321 ' and budget level id: '||valid_rec.budget_level_id);
1322 END IF;
1323 BEGIN
1324 SELECT internal_revision_num, doc_id, revision_num
1325 INTO v_int_rev_num, v_doc_id, v_revision_num
1326 FROM fv_be_trx_hdrs
1327 WHERE set_of_books_id = valid_rec.set_of_books_id
1328 AND budget_level_id = valid_rec.budget_level_id
1329 AND doc_number = valid_rec.doc_number
1330 AND source = valid_rec.source
1331 FOR UPDATE OF doc_total;
1332 EXCEPTION WHEN NO_DATA_FOUND THEN
1333 v_int_rev_num := -9999;
1334 ins_hdr := TRUE;
1335 END;
1336
1337 -- If doc does not exist, it is a new record
1338 -- Note: Temporarily set status to IMPORTING
1339 -- then update it to IN or RA
1340 -- set the internal rev num to -9999 to distinguish
1341 -- existing hdr from a new hdr and later update it
1342 -- to 0
1343 IF ins_hdr THEN
1344
1345 SELECT fv_be_trx_hdrs_s.nextval
1346 INTO new_doc_id
1347 FROM DUAL;
1348
1349 SELECT treasury_symbol_id
1350 INTO v_ts_id
1351 FROM fv_fund_parameters
1352 WHERE fund_value = valid_rec.fund_value
1353 AND set_of_books_id = valid_rec.set_of_books_id;
1354
1355 IF valid_rec.budget_level_id = 1 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,'INSERTING NEW HEADER RECORD');
1358 END IF;
1359 -- Select the budget group id for the User
1360 BEGIN
1361 SELECT bu_group_id
1362 INTO l_bu_group_id
1363 FROM fv_budget_user_dtl
1364 WHERE set_of_books_id = valid_rec.set_of_books_id
1365 AND bu_user_id = valid_rec.budget_user_id
1366 AND valid_rec.budget_level_id BETWEEN bu_access_level_from AND bu_access_level_to;
1367 EXCEPTION
1368 WHEN OTHERS THEN
1369 retcode := -1;
1370 errbuf := 'Invalid budget user or Access level';
1371 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,errbuf);
1372 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'Error in getting the Group ID'
1373 || ' for the user user id => '||valid_rec.budget_user_id
1374 || ' budget level id ' || valid_rec.budget_level_id );
1375 p_retcode := retcode;
1376 p_errbuf := errbuf;
1377 reset_control_status;
1378 ROLLBACK;
1379 RETURN;
1380 END;
1381
1382 INSERT INTO fv_be_trx_hdrs (
1383 budgeting_segments, budget_level_id,
1384 doc_id, doc_number, doc_status,
1385 doc_total, fund_value, internal_revision_num, revision_num,
1386 set_of_books_id,bu_group_id, source, transaction_date,
1387 treasury_symbol_id, created_by, creation_date,
1388 last_updated_by, last_update_date, last_update_login,
1389 segment1, segment2, segment3, segment4,
1390 segment5, segment6, segment7, segment8, segment9, segment10,
1391 segment11, segment12, segment13, segment14, segment15,
1392 segment16, segment17, segment18, segment19, segment20,
1393 segment21, segment22, segment23, segment24, segment25,
1394 segment26, segment27, segment28, segment29, segment30)
1395 VALUES
1396 (v_bud_segs, valid_rec.budget_level_id,
1397 new_doc_id, valid_rec.doc_number, 'IMPORTING',
1398 0, valid_rec.fund_value, -9999, 0,
1399 valid_rec.set_of_books_id,l_bu_group_id, valid_rec.source, TRUNC(SYSDATE),
1400 v_ts_id, fnd_global.user_id, SYSDATE,
1401 fnd_global.user_id, SYSDATE, fnd_global.login_id,
1402 segs_array(1), segs_array(2), segs_array(3),
1403 segs_array(4), segs_array(5), segs_array(6),
1404 segs_array(7), segs_array(8), segs_array(9),
1405 segs_array(10), segs_array(11), segs_array(12),
1406 segs_array(13), segs_array(14), segs_array(15),
1407 segs_array(16), segs_array(17), segs_array(18),
1408 segs_array(19), segs_array(20), segs_array(21),
1409 segs_array(22), segs_array(23), segs_array(24),
1410 segs_array(25), segs_array(26), segs_array(27),
1411 segs_array(28), segs_array(29), segs_array(30));
1412
1413 ELSE -- if budget level <> 1 copy segments for previous budget
1414 -- level from the details table. Error the process if
1415 -- segments not found.
1416
1417 -- Breakup the budgeting segments and copy the default segment values
1418 -- if segment type is 'N'. Then concatenate the broken up segments
1419 -- with the defalt segment values to form new budgeting_segments
1420 -- and insert into the record.
1421 v_num_segs := 0;
1422 v_index := 0;
1423 dummy_array.DELETE;
1424 v_delimiter := null;
1425
1426 SELECT concatenated_segment_delimiter
1427 INTO v_delimiter
1428 FROM fnd_id_flex_structures ffs,
1429 gl_ledgers_public_v gsb
1430 WHERE application_id = 101
1431 AND id_flex_code = 'GL#'
1432 AND ffs.id_flex_num = gsb.chart_of_accounts_id
1433 AND gsb.ledger_id = valid_rec.set_of_books_id;
1434
1435 v_num_segs :=
1436 fnd_flex_ext.breakup_segments(valid_rec.budgeting_segments,
1437 v_delimiter, dummy_array);
1438
1439 IF v_num_segs = 0
1440 THEN
1441 retcode := -1;
1442 errbuf := 'No segments found in budgeting_segments';
1443 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'message10',errbuf);
1444 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'NO SEGMENTS FOUND IN BUDGETING SEGMENTS');
1445 reset_control_status;
1446 p_retcode := retcode;
1447 p_errbuf := errbuf;
1448 ROLLBACK;
1449 RETURN;
1450 END IF;
1451
1452 FOR app_col_rec IN app_col(valid_rec.set_of_books_id)
1453 LOOP
1454
1455 l_seg_type := NULL;
1456
1457 l_stmt:=
1458 'SELECT '||app_col_rec.application_column_name||'_TYPE
1459 FROM fv_budget_distribution_dtl
1460 WHERE set_of_books_id = :set_of_books_id
1461 AND fund_value = :fund_value
1462 AND budget_level_id =
1463 (SELECT MAX(budget_level_id)
1464 FROM fv_budget_distribution_dtl
1465 WHERE fund_value = :fund_value
1466 AND set_of_books_id = :set_of_books_id
1467 AND budget_level_id < :budget_level_id )';
1468
1469 EXECUTE IMMEDIATE l_stmt INTO l_seg_type
1470 USING valid_rec.set_of_books_id,
1471 valid_rec.fund_value,
1472 valid_rec.fund_value,
1473 valid_rec.set_of_books_id,
1474 valid_rec.budget_level_id ;
1475
1476 v_index := v_index + 1;
1477
1478 IF l_seg_type = 'D'
1479 THEN
1480
1481 l_stmt:=
1482 'SELECT '||app_col_rec.application_column_name||
1483 ' FROM fv_budget_distribution_dtl
1484 WHERE set_of_books_id = :set_of_books_id
1485 AND fund_value = :fund_value
1486 AND budget_level_id =
1487 (SELECT MAX(budget_level_id)
1488 FROM fv_budget_distribution_dtl
1489 WHERE fund_value = :fund_value
1490 AND set_of_books_id = :set_of_books_id
1491 AND budget_level_id < :budget_level_id )';
1492
1493 EXECUTE IMMEDIATE l_stmt INTO dummy_array(v_index)
1494 USING valid_rec.set_of_books_id,
1495 valid_rec.fund_value,
1496 valid_rec.fund_value,
1497 valid_rec.set_of_books_id,
1498 valid_rec.budget_level_id ;
1499
1500 END IF;
1501 END LOOP; -- app_col_rec
1502
1503 -- Concatenate segments in dummy_array
1504 v_num_segs := 0;
1505
1506 v_num_segs := dummy_array.COUNT;
1507
1508 valid_rec.budgeting_segments :=
1509 fnd_flex_ext.concatenate_segments(v_num_segs,
1510 dummy_array, v_delimiter);
1511
1512 BEGIN
1513 SELECT fbd.segment1, fbd.segment2, fbd.segment3, fbd.segment4,
1514 fbd.segment5, fbd.segment6, fbd.segment7, fbd.segment8,
1515 fbd.segment9, fbd.segment10,fbd.segment11,fbd.segment12,
1516 fbd.segment13,fbd.segment14,fbd.segment15,fbd.segment16,
1517 fbd.segment17,fbd.segment18,fbd.segment19,fbd.segment20,
1518 fbd.segment21,fbd.segment22,fbd.segment23,fbd.segment24,
1519 fbd.segment25,fbd.segment26,fbd.segment27, fbd.segment28,
1520 fbd.segment29, fbd.segment30
1521 INTO v_segment1, v_segment2, v_segment3, v_segment4,
1522 v_segment5, v_segment6, v_segment7, v_segment8,
1523 v_segment9, v_segment10, v_segment11, v_segment12,
1524 v_segment13, v_segment14, v_segment15, v_segment16,
1525 v_segment17, v_segment18, v_segment19, v_segment20,
1526 v_segment21, v_segment22, v_segment23, v_segment24,
1527 v_segment25, v_segment26, v_segment27, v_segment28,
1528 v_segment29, v_segment30
1529 FROM fv_be_trx_hdrs fbh,
1530 fv_be_trx_dtls fbd
1531 WHERE fbh.fund_value = valid_rec.fund_value
1532 AND fbh.set_of_books_id = valid_rec.set_of_books_id
1533 AND fbh.doc_id = fbd.doc_id
1534 AND fbh.set_of_books_id = fbd.set_of_books_id
1535 AND fbd.budgeting_segments = valid_rec.budgeting_segments
1536 AND rownum < 2
1537 AND fbh.budget_level_id =
1538 (SELECT MAX(budget_level_id)
1539 -- FROM fv_budget_distribution_dtl
1540 FROM fv_be_trx_dtls
1541 WHERE fund_value = valid_rec.fund_value
1542 AND set_of_books_id = valid_rec.set_of_books_id
1543 AND budget_level_id < valid_rec.budget_level_id
1544 );
1545
1546 EXCEPTION
1547 WHEN NO_DATA_FOUND THEN
1548 retcode := -1;
1549 p_retcode := retcode;
1550 p_errbuf := errbuf;
1551 errbuf := 'No records found with the same segments for the previous budget level';
1552 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'message11',errbuf);
1553 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'NO RECORDS FOUND WITH THE SAME SEGMENTS
1554 for the previous budget level');
1555 -- reset_control_status;
1556 -- ROLLBACK;
1557 update_err_rec(valid_rec.record_number);
1558 RETURN;
1559 WHEN OTHERS THEN
1560 retcode := -1;
1561 errbuf := 'When others error while checking for segments in the previous budget level.'||SQLERRM;
1562 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'message12',errbuf);
1563 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'WHEN OTHERS ERROR WHILE CHECKING FOR
1564 segments in the previous budget level');
1565 p_retcode := retcode;
1566 p_errbuf := errbuf;
1567 reset_control_status;
1568 ROLLBACK;
1569 RETURN;
1570 END;
1571
1572 BEGIN
1573 SELECT bu_group_id
1574 INTO l_bu_group_id
1575 FROM fv_budget_user_dtl
1576 WHERE set_of_books_id = valid_rec.set_of_books_id
1577 AND bu_user_id = valid_rec.budget_user_id
1578 AND valid_rec.budget_level_id BETWEEN bu_access_level_from and bu_access_level_to;
1579 EXCEPTION
1580 WHEN OTHERS THEN
1581 retcode := -1;
1582 errbuf := 'Invalid budget user or Acecess level';
1583 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, errbuf);
1584 p_retcode := retcode;
1585 p_errbuf := errbuf;
1586 reset_control_status;
1587 RETURN;
1588 END;
1589
1590 INSERT INTO fv_be_trx_hdrs (
1591 budgeting_segments, budget_level_id,
1592 doc_id, doc_number, doc_status,
1593 doc_total, fund_value, internal_revision_num, revision_num,
1594 set_of_books_id, bu_group_id,source, transaction_date,
1595 treasury_symbol_id, created_by, creation_date,
1596 last_updated_by, last_update_date, last_update_login,
1597 segment1, segment2, segment3, segment4, segment5,
1598 segment6, segment7, segment8, segment9, segment10,
1599 segment11, segment12, segment13, segment14, segment15,
1600 segment16, segment17, segment18, segment19, segment20,
1601 segment21, segment22, segment23, segment24, segment25,
1602 segment26, segment27, segment28, segment29, segment30)
1603 VALUES
1604 (valid_rec.budgeting_segments, valid_rec.budget_level_id,
1605 new_doc_id, valid_rec.doc_number, 'IMPORTING',
1606 0, valid_rec.fund_value, -9999, 0,
1607 valid_rec.set_of_books_id, l_bu_group_id,valid_rec.source, TRUNC(SYSDATE),
1608 v_ts_id, fnd_global.user_id, SYSDATE,
1609 fnd_global.user_id, SYSDATE, fnd_global.login_id,
1610 v_segment1, v_segment2, v_segment3, v_segment4, v_segment5,
1611 v_segment6, v_segment7, v_segment8, v_segment9, v_segment10,
1612 v_segment11, v_segment12, v_segment13, v_segment14,
1613 v_segment15, v_segment16, v_segment17, v_segment18,
1614 v_segment19, v_segment20, v_segment21, v_segment22,
1615 v_segment23, v_segment24, v_segment25, v_segment26,
1616 v_segment27, v_segment28, v_segment29, v_segment30);
1617
1618 END IF; -- if budget level = 1
1619
1620 -- set values for detail record if the doc is new
1621 v_doc_id := new_doc_id;
1622 v_revision_num := 0;
1623
1624 END IF; -- ins_hdr
1625
1626 -- For a new document dtl revision num is 0,
1627 -- for an existing document it is hdr rev num+1
1628 IF v_int_rev_num = -9999
1629 THEN v_revision_num := 0;
1630 ELSE
1631 v_revision_num := v_revision_num + 1;
1632 END IF;
1633
1634 IF g_pub_law_code_flag <> 'Y' THEN
1635 valid_rec.public_law_code := NULL;
1636 END IF;
1637
1638 IF g_pub_law_code_flag <> 'Y' THEN
1639 valid_rec.public_law_code := NULL;
1640 END IF;
1641
1642 IF g_advance_flag <> 'Y' THEN
1643 valid_rec.advance_type := NULL;
1644 ELSE
1645 valid_rec.advance_type := g_advance_type_code;
1646 END IF;
1647
1648 IF g_transfer_flag <> 'Y' THEN
1649 valid_rec.dept_id := NULL;
1650 valid_rec.main_account := NULL;
1651 valid_rec.transfer_description := NULL;
1652 END IF;
1653 validate_gl_date(valid_rec.gl_date,
1654 valid_rec.set_of_books_id,
1655 v_quarter_num);
1656 INSERT INTO fv_be_trx_dtls
1657 (
1658 amount,
1659 budgeting_segments,
1660 doc_id,
1661 gl_date,
1662 quarter_num,
1663 gl_transfer_flag,
1664 increase_decrease_flag,
1665 revision_num,
1666 set_of_books_id,
1667 sub_type,
1668 transaction_id,
1669 transaction_status,
1670 transaction_type_id,
1671 source,
1672 group_id,
1673 corrected_flag,
1674 created_by,
1675 creation_date,
1676 last_updated_by,
1677 last_update_date,
1678 last_update_login,
1679 segment1,
1680 segment2,
1681 segment3,
1682 segment4,
1683 segment5,
1684 segment6,
1685 segment7,
1686 segment8,
1687 segment9,
1688 segment10,
1689 segment11,
1690 segment12,
1691 segment13,
1692 segment14,
1693 segment15,
1694 segment16,
1695 segment17,
1696 segment18,
1697 segment19,
1698 segment20,
1699 segment21,
1700 segment22,
1701 segment23,
1702 segment24,
1703 segment25,
1704 segment26,
1705 segment27,
1706 segment28,
1707 segment29,
1708 segment30,
1709 public_law_code,
1710 advance_type,
1711 dept_id,
1712 main_account,
1713 transfer_description,
1714 attribute_category,
1715 attribute1,
1716 attribute2,
1717 attribute3,
1718 attribute4,
1719 attribute5,
1720 attribute6,
1721 attribute7,
1722 attribute8,
1723 attribute9,
1724 attribute10,
1725 attribute11,
1726 attribute12,
1727 attribute13,
1728 attribute14,
1729 attribute15
1730 )
1731 VALUES
1732 (
1733 valid_rec.amount,
1734 v_bud_segs,
1735 v_doc_id,
1736 valid_rec.gl_date,
1737 v_quarter_num,
1738 'N',
1739 valid_rec.increase_decrease_flag,
1740 v_revision_num,
1741 valid_rec.set_of_books_id,
1742 valid_rec.sub_type,
1743 fv_be_trx_dtls_s.nextval,
1744 'IN',
1745 v_tt_id,
1746 valid_rec.source,
1747 valid_rec.group_id,
1748 valid_rec.corrected_flag,
1749 fnd_global.user_id,
1750 SYSDATE,
1751 fnd_global.user_id,
1752 SYSDATE,
1753 fnd_global.login_id,
1754 segs_array(1),
1755 segs_array(2),
1756 segs_array(3),
1757 segs_array(4),
1758 segs_array(5),
1759 segs_array(6),
1760 segs_array(7),
1761 segs_array(8),
1762 segs_array(9),
1763 segs_array(10),
1764 segs_array(11),
1765 segs_array(12),
1766 segs_array(13),
1767 segs_array(14),
1768 segs_array(15),
1769 segs_array(16),
1770 segs_array(17),
1771 segs_array(18),
1772 segs_array(19),
1773 segs_array(20),
1774 segs_array(21),
1775 segs_array(22),
1776 segs_array(23),
1777 segs_array(24),
1778 segs_array(25),
1779 segs_array(26),
1780 segs_array(27),
1781 segs_array(28),
1782 segs_array(29),
1783 segs_array(30),
1784 valid_rec.public_law_code,
1785 valid_rec.advance_type,
1786 valid_rec.dept_id,
1787 valid_rec.main_account,
1788 valid_rec.transfer_description,
1789 valid_rec.attribute_category,
1790 DECODE(validation, 'N', NULL, valid_rec.attribute1),
1791 DECODE(validation, 'N', NULL, valid_rec.attribute2),
1792 DECODE(validation, 'N', NULL, valid_rec.attribute3),
1793 DECODE(validation, 'N', NULL, valid_rec.attribute4),
1794 DECODE(validation, 'N', NULL, valid_rec.attribute5),
1795 DECODE(validation, 'N', NULL, valid_rec.attribute6),
1796 DECODE(validation, 'N', NULL, valid_rec.attribute7),
1797 DECODE(validation, 'N', NULL, valid_rec.attribute8),
1798 DECODE(validation, 'N', NULL, valid_rec.attribute9),
1799 DECODE(validation, 'N', NULL, valid_rec.attribute10),
1800 DECODE(validation, 'N', NULL, valid_rec.attribute11),
1801 DECODE(validation, 'N', NULL, valid_rec.attribute12),
1802 DECODE(validation, 'N', NULL, valid_rec.attribute13),
1803 DECODE(validation, 'N', NULL, valid_rec.attribute14),
1804 DECODE(validation, 'N', NULL, valid_rec.attribute15)
1805 );
1806
1807 SELECT DECODE(valid_rec.increase_decrease_flag,'I',
1808 valid_rec.amount, (-1 * valid_rec.amount))
1809 INTO v_amount FROM DUAL;
1810
1811 -- set the doc status to IMPORTING to identify which
1812 -- header records status should be changed to IN
1813 UPDATE fv_be_trx_hdrs
1814 SET doc_status = 'IMPORTING',
1815 doc_total = doc_total + v_amount
1816 WHERE doc_id = v_doc_id;
1817
1818 END LOOP;
1819
1820 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1821 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RUNNING UPDATE CLEANUP');
1822 END IF;
1823 update_cleanup(parm_source,parm_group_id);
1824
1825 IF retcode <> 0 THEN
1826 p_retcode := retcode;
1827 p_errbuf := errbuf;
1828 ROLLBACK;
1829 reset_control_status;
1830 RETURN;
1831 END IF;
1832
1833 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1834 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'SUBMITTING BE TRANSACTIONS IMPORT REPORT');
1835 END IF;
1836 v_req_id := 0;
1837
1838 v_req_id := FND_REQUEST.SUBMIT_REQUEST
1839 ('FV','FVBEINTR','','',FALSE, parm_ledger_id, parm_source, parm_group_id);
1840
1841 -- If the request submission fails, then abort process
1842 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1843 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'REQEST ID FOR REPORT = '||
1844 to_char(v_req_id)) ;
1845 END IF;
1846
1847 IF (v_req_id = 0)
1848 THEN
1849 errbuf := 'Unable to submit BE Transactions Import Report';
1850 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.message13',errbuf);
1851 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'UNABLE TO SUBMIT BE TRANSACTIONS IMPORT REPORT');
1852 retcode := -1;
1853 p_retcode := retcode;
1854 p_errbuf := errbuf;
1855 ROLLBACK;
1856 reset_control_status;
1857 RETURN;
1858 END IF;
1859
1860 END IF; -- v_rej_rec_count=0
1861
1862 COMMIT;
1863 EXCEPTION
1864 WHEN OTHERS THEN
1865 retcode := -1;
1866 errbuf := SQLERRM;
1867 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
1868 p_retcode := retcode;
1869 p_errbuf := errbuf;
1870
1871 END; -- Main
1872 --------------------------------------------------------------------------------
1873 -- Procedures being used by the above code
1874 --------------------------------------------------------------------------------
1875 -- Procedure to update error records in the
1876 -- fv_be_interface table
1877 PROCEDURE update_err_code(l_rowid VARCHAR2, l_err_code VARCHAR2,
1878 l_err_reason VARCHAR2) IS
1879 l_module_name VARCHAR2(200);
1880 BEGIN
1881 l_module_name := g_module_name || 'update_err_code';
1882 UPDATE fv_be_interface
1883 SET error_code = l_err_code,
1884 error_reason = l_err_reason,
1885 status = 'REJECTED',
1886 processed_flag = 'Y'
1887 WHERE rowid = l_rowid;
1888 EXCEPTION
1889 WHEN OTHERS THEN
1890 errbuf := SQLERRM;
1891 retcode := -1;
1892 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
1893
1894 END update_err_code;
1895 --------------------------------------------------------------------------------
1896 -- Procedure to validate set of books id
1897 PROCEDURE validate_sob(v_sob_id NUMBER) IS
1898 l_module_name VARCHAR2(200);
1899 BEGIN
1900 l_module_name := g_module_name || 'validate_sob';
1901 SELECT 'x'
1902 INTO v_exists
1903 FROM gl_ledgers_public_v
1904 WHERE ledger_id = v_sob_id;
1905 EXCEPTION
1906 WHEN NO_DATA_FOUND THEN
1907 v_error_code := -9;
1908 WHEN OTHERS THEN
1909 retcode := -1;
1910 errbuf := 'When others error while validating set of books id.'||SQLERRM;
1911 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
1912 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'WHEN OTHERS ERROR WHILE VALIDATING
1913 set of books id');
1914 reset_control_status;
1915 END validate_sob;
1916 --------------------------------------------------------------------------------
1917 -- Procedure to validate period name
1918 PROCEDURE validate_gl_date(v_gl_date VARCHAR2,
1919 v_set_of_books_id NUMBER,
1920 v_quarter_num OUT NOCOPY NUMBER) IS
1921 l_module_name VARCHAR2(200);
1922 BEGIN
1923 l_module_name := g_module_name || 'validate_gl_date';
1924 v_quarter_num := NULL;
1925 SELECT quarter_num
1926 INTO v_quarter_num
1927 FROM gl_period_statuses
1928 WHERE v_gl_date BETWEEN start_date AND end_date
1929 AND set_of_books_id = v_set_of_books_id
1930 AND closing_status IN ('O','F')
1931 AND adjustment_period_flag = 'N'
1932 AND application_id = 101;
1933 EXCEPTION
1934 WHEN NO_DATA_FOUND THEN
1935 v_error_code := -9;
1936 WHEN OTHERS THEN
1937 retcode := -1;
1938 errbuf := 'When others error while validating period name.'||SQLERRM;
1939 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
1940 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'WHEN OTHERS ERROR WHILE VALIDATING
1941 period name');
1942 reset_control_status;
1943 END validate_gl_date;
1944 --------------------------------------------------------------------------------
1945 -- Procedure to validate budget level
1946 PROCEDURE validate_budget_level(v_set_of_books_id NUMBER,
1947 v_budget_level_id NUMBER) IS
1948 l_module_name VARCHAR2(200);
1949 BEGIN
1950 l_module_name := g_module_name || 'validate_budget_level';
1951 SELECT 'x'
1952 INTO v_exists
1953 FROM fv_budget_levels
1954 WHERE budget_level_id = v_budget_level_id
1955 AND set_of_books_id = v_set_of_books_id;
1956 EXCEPTION
1957 WHEN NO_DATA_FOUND THEN
1958 v_error_code := -9;
1959 WHEN OTHERS THEN
1960 retcode := -1;
1961 errbuf := 'When others error while validating budget level.'||SQLERRM;
1962 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
1963 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'WHEN OTHERS ERROR WHILE VALIDATING
1964 budget level');
1965 reset_control_status;
1966 END validate_budget_level;
1967 ---------------------------------------------------------------------------------
1968 -- Validate budget User
1969 PROCEDURE validate_budget_user( p_sob_id NUMBER,
1970 p_bu_user_id NUMBER) IS
1971 l_count NUMBER;
1972 l_module_name VARCHAR2(200);
1973
1974 BEGIN
1975 l_module_name := g_module_name || 'validate_budget_user';
1976 SELECT COUNT(*)
1977 INTO l_count
1978 FROM fv_budget_user_dtl
1979 WHERE set_of_books_id = p_sob_id
1980 AND bu_user_id = p_bu_user_id;
1981 IF l_count = 0 THEN
1982 v_error_code := -10;
1983 END IF;
1984 EXCEPTION
1985 WHEN OTHERS THEN
1986 retcode := -1;
1987 errbuf := 'When others error while validating budget User.'||SQLERRM;
1988 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
1989 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'WHEN OTHERS ERROR WHILE VALIDATING
1990 budget User user ID =>'|| p_bu_user_id);
1991 reset_control_status;
1992
1993 END;---------------------------------------------------------------------------------
1994 -- Validate budget User
1995 PROCEDURE validate_bu_access_level( p_sob_id NUMBER,
1996 p_bu_user_id NUMBER,
1997 p_budget_level_id NUMBER) IS
1998 l_update_flag VARCHAR2(1);
1999 l_module_name VARCHAR2(200);
2000
2001 BEGIN
2002 l_module_name := g_module_name || 'validate_bu_access_level';
2003 SELECT NVL(bu_update_flag,'N')
2004 INTO l_update_flag
2005 FROM fv_budget_user_dtl
2006 WHERE set_of_books_id = p_sob_id
2007 AND bu_user_id = p_bu_user_id
2008 AND p_budget_level_id BETWEEN bu_access_level_from AND bu_access_level_to;
2009 IF l_update_flag ='N' THEN
2010 v_error_code := -11;
2011 END IF;
2012 EXCEPTION
2013 WHEN NO_DATA_FOUND THEN
2014 v_error_code := -11;
2015 WHEN OTHERS THEN
2016 retcode := -1;
2017 errbuf := 'When others error while validating budget User.'||SQLERRM;
2018 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
2019 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'WHEN OTHERS ERROR WHILE VALIDATING
2020 budget User Access Level bu_user_id =>'|| p_bu_user_id
2021 || ' Budget_level_id => ' || p_budget_level_id);
2022 reset_control_status;
2023
2024 END;
2025
2026 --------------------------------------------------------------------------------
2027 -- Procedure to validate fund value
2028 PROCEDURE validate_fund_value(v_set_of_books_id NUMBER,
2029 v_fund_value VARCHAR2,
2030 v_budget_level_id NUMBER) IS
2031 l_module_name VARCHAR2(200);
2032 BEGIN
2033 l_module_name := g_module_name || 'validate_fund_value';
2034 SELECT 'x'
2035 INTO v_exists
2036 FROM fv_budget_distribution_dtl fbd
2037 WHERE fbd.set_of_books_id = v_set_of_books_id
2038 AND fbd.fund_value = v_fund_value
2039 AND fbd.budget_level_id = v_budget_level_id;
2040 EXCEPTION
2041 WHEN NO_DATA_FOUND THEN
2042 v_error_code := -9;
2043 WHEN OTHERS THEN
2044 retcode := -1;
2045 errbuf := 'When others error while validating fund value.'||SQLERRM;
2046 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
2047 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'WHEN OTHERS ERROR WHILE VALIDATING
2048 fund value');
2049 reset_control_status;
2050 END validate_fund_value;
2051 --------------------------------------------------------------------------------
2052 -- Procedure to validate treasury symbol expiry/cancellation date
2053 PROCEDURE validate_tsymbol_date(v_set_of_books_id NUMBER,
2054 v_fund_value VARCHAR2,
2055 v_gl_date VARCHAR2) IS
2056 l_module_name VARCHAR2(200);
2057 l_expire_date DATE;
2058 l_cancel_date DATE;
2059 l_tsid NUMBER;
2060 l_gl_date DATE;
2061 wrong_date EXCEPTION;
2062 BEGIN
2063 l_module_name := g_module_name || 'validate_tsymbol_date';
2064
2065 SELECT fts.expiration_date, fts.cancellation_date,
2066 fts.treasury_symbol_id
2067 INTO l_expire_date, l_cancel_date, l_tsid
2068 FROM fv_treasury_symbols fts,
2069 fv_budget_distribution_hdr fbh
2070 WHERE fts.treasury_symbol_id = fbh.treasury_symbol_id
2071 AND fts.set_of_books_id = fbh.set_of_books_id
2072 AND fbh.fund_value = v_fund_value
2073 AND fbh.set_of_books_id = v_set_of_books_id;
2074
2075 IF (nvl(l_expire_date,v_gl_date)
2076 < v_gl_date OR
2077 nvl(l_cancel_date,v_gl_date)
2078 < v_gl_date)
2079 THEN RAISE wrong_date;
2080 END IF;
2081 EXCEPTION
2082 WHEN WRONG_DATE THEN
2083 v_error_code := -9;
2084 WHEN OTHERS THEN
2085 retcode := -1;
2086 errbuf := SUBSTR(SQLERRM,1,100)||' :When others error while validating expire/cancel date for treasury symbol';
2087 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
2088 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,SUBSTR(SQLERRM,1,100)||' :WHEN OTHERS ERROR
2089 while validating expire/cancel date for treasury symbol');
2090 reset_control_status;
2091 END validate_tsymbol_date;
2092 --------------------------------------------------------------------------------
2093 -- Procedure to validate transaction type and its attributes
2094 PROCEDURE validate_trx_type_attribs(v_set_of_books_id NUMBER,
2095 v_budget_level_id NUMBER,
2096 v_trx_type VARCHAR2,
2097 v_sub_type VARCHAR2,
2098 v_public_law_code VARCHAR2,
2099 v_advance_type VARCHAR2,
2100 v_dept_id NUMBER,
2101 v_main_account NUMBER) IS
2102 l_module_name VARCHAR2(200);
2103 g_sub_type_flag varchar2(1);
2104 BEGIN
2105 l_module_name := g_module_name || 'validate_trx_type_attribs';
2106
2107 g_pub_law_code_flag := NULL;
2108 g_advance_flag := NULL;
2109 g_transfer_flag := NULL;
2110
2111 g_advance_type_code := NULL;
2112
2113
2114 SELECT public_law_code_flag, advance_flag,
2115 transfer_flag, sub_type_flag
2116 INTO g_pub_law_code_flag, g_advance_flag,
2117 g_transfer_flag,g_sub_type_flag
2118 FROM fv_be_transaction_types
2119 WHERE set_of_books_id = v_set_of_books_id
2120 AND budget_level_id = v_budget_level_id
2121 AND apprn_transaction_type = v_trx_type;
2122
2123 IF (g_pub_law_code_flag = 'Y') AND (v_public_law_code IS NULL OR LENGTH(v_public_law_code) > 7)
2124 THEN v_error_code := -6;
2125 RETURN;
2126 END IF;
2127 IF g_sub_type_flag = 'Y' and v_sub_type is null THEN
2128 v_error_code := -10;
2129 RETURN;
2130 END IF;
2131 IF g_advance_flag = 'Y'
2132 THEN
2133 BEGIN
2134 SELECT lookup_code
2135 INTO g_advance_type_code
2136 FROM fv_lookup_codes
2137 WHERE lookup_type = 'ADVANCE_FLAG'
2138 AND description = v_advance_type;
2139 EXCEPTION WHEN NO_DATA_FOUND THEN
2140 v_error_code := -7;
2141 RETURN;
2142 END;
2143 END IF;
2144
2145 IF (g_transfer_flag = 'Y' AND (v_dept_id IS NULL OR
2146 v_main_account IS NULL))
2147 THEN v_error_code := -8;
2148 END IF;
2149
2150 EXCEPTION
2151 WHEN NO_DATA_FOUND THEN
2152 v_error_code := -9;
2153 WHEN OTHERS THEN
2154 retcode := -1;
2155 errbuf := 'When others error while validating Transaction Type.'||SQLERRM;
2156 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
2157 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'WHEN OTHERS ERROR WHILE VALIDATING
2158 Transaction Type');
2159 reset_control_status;
2160 END validate_trx_type_attribs;
2161 --------------------------------------------------------------------------------
2162 -- Check for default transaction code in fv_be_transaction_types
2163 -- If default transaction code is not equal to the trx code
2164 -- then check the transaction code for that transaction type
2165 -- in fv_be_trx_codes. If the transaction code does not exist
2166 -- in fv_be_trx_codes then the record is in error
2167 PROCEDURE VALIDATE_SUB_TYPE(v_set_of_books_id NUMBER,
2168 v_trx_type VARCHAR2,
2169 v_budget_level_id NUMBER,
2170 v_sub_type VARCHAR2) IS
2171 l_module_name VARCHAR2(200);
2172 l_be_tt_id NUMBER;
2173 l_update_flag VARCHAR2(1);
2174 l_subtype_flag VARCHAR2(1);
2175 BEGIN
2176 l_module_name := g_module_name || 'VALIDATE_SUB_TYPE';
2177
2178 SELECT sub_type_flag
2179 INTO l_subtype_flag
2180 FROM fv_be_transaction_types
2181 WHERE set_of_books_id = v_set_of_books_id
2182 AND budget_level_id = v_budget_level_id
2183 AND apprn_transaction_type = v_trx_type;
2184
2185
2186
2187 IF (l_subtype_flag ='Y' ) or (V_SUB_TYPE is not null) THEN
2188
2189 SELECT 'X'
2190 INTO v_exists
2191 FROM FV_BE_TRANSACTION_TYPES T, FV_BE_TRX_SUB_TYPES S
2192 WHERE T.BE_TT_ID = S.BE_TT_ID
2193 AND T.BUDGET_LEVEL_ID = V_BUDGET_LEVEL_ID
2194 AND T.APPRN_TRANSACTION_TYPE = V_TRX_TYPE
2195 AND S.SUB_TYPE = V_SUB_TYPE
2196 AND t.set_of_books_id = v_set_of_books_id
2197 AND s.ledger_id = v_set_of_books_id;
2198 ELSE
2199 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Sub Type flag is set to No');
2200 END IF;
2201 EXCEPTION
2202 -- If Sub-Type does not exist
2203 WHEN NO_DATA_FOUND THEN
2204 v_error_code := -9;
2205 WHEN OTHERS THEN
2206 retcode := -1;
2207 errbuf := 'When others error while validating Sub Type.'||SQLERRM;
2208 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.message1',errbuf);
2209 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'WHEN OTHERS ERROR WHILE
2210 validating Sub-Type');
2211 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, '!!!retcode; '||retcode);
2212 reset_control_status;
2213 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, '$$$retcode; '||retcode);
2214 END VALIDATE_SUB_TYPE;
2215 --------------------------------------------------------------------------------
2216 -- Procedure to validate document number
2217 PROCEDURE validate_doc_number(v_doc_number VARCHAR2,
2218 v_set_of_books_id NUMBER,
2219 v_fund_value VARCHAR2,
2220 v_budget_level_id NUMBER,
2221 v_source VARCHAR2) IS
2222
2223 l_module_name VARCHAR2(200);
2224 l_doc_status VARCHAR2(25);
2225 l_revision_num NUMBER;
2226 l_doc_id NUMBER;
2227 l_doc_entry VARCHAR2(1);
2228 l_doc_type VARCHAR2(1);
2229 l_doc_number NUMBER;
2230 l_fund_value fv_fund_parameters.fund_value%TYPE;
2231 BEGIN
2232 l_module_name := g_module_name || 'validate_doc_number';
2233 SELECT doc_status, revision_num, doc_id, fund_value
2234 INTO l_doc_status, l_revision_num, l_doc_id, l_fund_value
2235 FROM fv_be_trx_hdrs
2236 WHERE set_of_books_id = v_set_of_books_id
2237 AND budget_level_id = v_budget_level_id
2238 AND doc_number = v_doc_number
2239 AND source = v_source;
2240
2241 -- Check if the fund_value is the same for the above combination
2242 -- if not, reject the record
2243 IF v_fund_value = l_fund_value THEN
2244 -- Check if document has been approved
2245 IF l_doc_status NOT IN ('AR','IMPORTING') THEN
2246 v_error_code := -8;
2247 RETURN;
2248 END IF;
2249 ELSE
2250 v_error_code := -7;
2251 RETURN;
2252 END IF;
2253
2254 EXCEPTION
2255 -- If doc number is not found, then validate the
2256 -- new doc number
2257 WHEN NO_DATA_FOUND THEN
2258 BEGIN
2259 SELECT doc_num_entry, doc_num_type
2260 INTO l_doc_entry, l_doc_type
2261 FROM fv_budget_levels
2262 WHERE set_of_books_id = v_set_of_books_id
2263 AND budget_level_id = v_budget_level_id;
2264 -- Check if document entry is automatic
2265 -- or manual. If it is automatic or is (manual and
2266 -- numeric), then check
2267 -- whether interface doc number is numeric.
2268 -- If it is not numeric then raise error
2269 IF (l_doc_entry = 'A') OR
2270 (l_doc_entry = 'M' AND l_doc_type = 'N')
2271 THEN
2272 SELECT to_number(v_doc_number)
2273 INTO l_doc_number
2274 FROM DUAL;
2275 END IF;
2276 EXCEPTION
2277 WHEN INVALID_NUMBER THEN
2278 v_error_code := -9;
2279 WHEN OTHERS THEN
2280 retcode := -1;
2281 errbuf := 'When others error while validating Document Number.'||SQLERRM;
2282 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.message1',errbuf);
2283 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'WHEN OTHERS ERROR WHILE
2284 validating Document Number');
2285 reset_control_status;
2286 END;
2287 WHEN OTHERS THEN
2288 retcode := -1;
2289 errbuf := 'When others error while validating Document Number.'||SQLERRM;
2290 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
2291 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'WHEN OTHERS ERROR WHILE
2292 validating Document Number');
2293 reset_control_status;
2294 END validate_doc_number;
2295 --------------------------------------------------------------------------------
2296 -- Procedure to validate DFF
2297 PROCEDURE validate_dff
2298 (
2299 v_attribute_category fv_be_interface.attribute_category%TYPE,
2300 v_attribute1 fv_be_interface.attribute1%TYPE,
2301 v_attribute2 fv_be_interface.attribute2%TYPE,
2302 v_attribute3 fv_be_interface.attribute3%TYPE,
2303 v_attribute4 fv_be_interface.attribute4%TYPE,
2304 v_attribute5 fv_be_interface.attribute5%TYPE,
2305 v_attribute6 fv_be_interface.attribute6%TYPE,
2306 v_attribute7 fv_be_interface.attribute7%TYPE,
2307 v_attribute8 fv_be_interface.attribute8%TYPE,
2308 v_attribute9 fv_be_interface.attribute9%TYPE,
2309 v_attribute10 fv_be_interface.attribute10%TYPE,
2310 v_attribute11 fv_be_interface.attribute11%TYPE,
2311 v_attribute12 fv_be_interface.attribute12%TYPE,
2312 v_attribute13 fv_be_interface.attribute13%TYPE,
2313 v_attribute14 fv_be_interface.attribute14%TYPE,
2314 v_attribute15 fv_be_interface.attribute15%TYPE,
2315 v_error_mesg OUT NOCOPY VARCHAR2
2316 ) IS
2317
2318 l_module_name VARCHAR2(200);
2319 l_validation_result BOOLEAN;
2320 BEGIN
2321 l_module_name := g_module_name || 'validate_dff';
2322 v_error_code := 0;
2323
2324 fnd_flex_descval.clear_column_values;
2325 fnd_flex_descval.set_context_value (v_attribute_category);
2326 fnd_flex_descval.set_column_value ('ATTRIBUTE1', v_attribute1);
2327 fnd_flex_descval.set_column_value ('ATTRIBUTE2', v_attribute2);
2328 fnd_flex_descval.set_column_value ('ATTRIBUTE3', v_attribute3);
2329 fnd_flex_descval.set_column_value ('ATTRIBUTE4', v_attribute4);
2330 fnd_flex_descval.set_column_value ('ATTRIBUTE5', v_attribute5);
2331 fnd_flex_descval.set_column_value ('ATTRIBUTE6', v_attribute6);
2332 fnd_flex_descval.set_column_value ('ATTRIBUTE7', v_attribute7);
2333 fnd_flex_descval.set_column_value ('ATTRIBUTE8', v_attribute8);
2334 fnd_flex_descval.set_column_value ('ATTRIBUTE9', v_attribute9);
2335 fnd_flex_descval.set_column_value ('ATTRIBUTE10', v_attribute10);
2336 fnd_flex_descval.set_column_value ('ATTRIBUTE11', v_attribute11);
2337 fnd_flex_descval.set_column_value ('ATTRIBUTE12', v_attribute12);
2338 fnd_flex_descval.set_column_value ('ATTRIBUTE13', v_attribute13);
2339 fnd_flex_descval.set_column_value ('ATTRIBUTE14', v_attribute14);
2340 fnd_flex_descval.set_column_value ('ATTRIBUTE15', v_attribute15);
2341
2342
2343 l_validation_result := fnd_flex_descval.validate_desccols
2344 (
2345 appl_short_name => 'FV',
2346 desc_flex_name => 'FV_BE_TRX_DTLS_DESC'
2347 );
2348
2349 IF (NOT l_validation_result) THEN
2350 v_error_mesg := fnd_flex_descval.error_message;
2351 v_error_code := -9;
2352 END IF;
2353
2354 EXCEPTION
2355 WHEN OTHERS THEN
2356 retcode := -1;
2357 errbuf := 'When others error while validating DFF.'||SQLERRM;
2358 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
2359 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,errbuf);
2360 reset_control_status;
2361 END validate_dff;
2362 --------------------------------------------------------------------------------
2363 -- Procedure to copy default segment values
2364 PROCEDURE copy_default_seg_vals(v_set_of_books_id NUMBER,
2365 v_fund_value VARCHAR2,
2366 v_budget_level_id NUMBER,
2367 v_rowid VARCHAR2) IS
2368
2369 l_module_name VARCHAR2(200);
2370 lv_stmt VARCHAR2(1000);
2371 lv_seg_type VARCHAR2(1);
2372 lv_seg_value VARCHAR2(25);
2373
2374 BEGIN
2375 l_module_name := g_module_name || 'copy_default_seg_vals';
2376 FOR app_col_name IN app_col(v_set_of_books_id)
2377 LOOP
2378
2379 lv_stmt:=
2380 'SELECT '||app_col_name.application_column_name||'_TYPE,'||
2381 app_col_name.application_column_name||
2382 ' FROM fv_budget_distribution_dtl
2383 WHERE set_of_books_id = :set_of_books_id
2384 AND budget_level_id = :budget_level_id
2385 AND fund_value = :fund_value ';
2386
2387 EXECUTE IMMEDIATE lv_stmt INTO lv_seg_type, lv_seg_value
2388 USING v_set_of_books_id, v_budget_level_id,
2389 v_fund_value ;
2390
2391 -- Check if the segment type is D. If the segment type is
2392 -- D, then update the current row with the default segment value
2393
2394 lv_stmt := NULL;
2395
2396 IF lv_seg_type = 'D' THEN
2397 lv_stmt :=
2398 'UPDATE fv_be_interface
2399 SET '||app_col_name.application_column_name||
2400 ' = '||''''||lv_seg_value||''''||
2401 ' WHERE rowid = :rowid ';
2402
2403
2404 EXECUTE IMMEDIATE lv_stmt USING v_rowid ;
2405
2406 END IF;
2407 END LOOP;
2408 EXCEPTION
2409 WHEN NO_DATA_FOUND THEN
2410 errbuf := 'No Data Found error while copying default segment values';
2411 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.no_data',errbuf);
2412 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'NO DATA FOUND ERROR WHILE COPYING
2413 default segment values');
2414 retcode := -1;
2415 reset_control_status;
2416 WHEN OTHERS THEN
2417 errbuf := substr(sqlerrm,1,100)||':When others error while copying default segment values';
2418 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
2419 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,SUBSTR(SQLERRM,1,100)||':WHEN OTHERS ERROR
2420 while copying default segment values');
2421 retcode := -1;
2422 reset_control_status;
2423 END copy_default_seg_vals;
2424 --------------------------------------------------------------------------------
2425 -- Procedure to concatenate segments
2426 PROCEDURE concat_segs(l_array fnd_flex_ext.segmentarray, l_sob_id NUMBER,
2427 l_bud_segs OUT NOCOPY VARCHAR2) IS
2428 l_module_name VARCHAR2(200);
2429 l_temp_string VARCHAR2(2000);
2430 l_count NUMBER;
2431 l_delmtr VARCHAR2(1);
2432
2433 BEGIN
2434 l_module_name := g_module_name || 'concat_segs';
2435 SELECT concatenated_segment_delimiter
2436 INTO l_delmtr
2437 FROM fnd_id_flex_structures ffs,
2438 gl_ledgers_public_v gsb
2439 WHERE application_id = 101
2440 AND id_flex_code = 'GL#'
2441 AND ffs.id_flex_num = gsb.chart_of_accounts_id
2442 AND gsb.ledger_id = l_sob_id;
2443
2444 l_count := 0;
2445 l_bud_segs := NULL;
2446
2447 FOR app_col_rec IN app_col(l_sob_id)
2448 LOOP
2449 IF l_count = 0 THEN
2450 l_temp_string :=
2451 l_array(substr(app_col_rec.application_column_name,8));
2452 ELSE
2453 l_temp_string :=
2454 l_delmtr||l_array(substr(app_col_rec.application_column_name,8));
2455 END IF;
2456 l_bud_segs := l_bud_segs||l_temp_string;
2457 l_count := 2;
2458 END LOOP;
2459 EXCEPTION
2460 WHEN NO_DATA_FOUND THEN
2461 errbuf := 'When no data found error while concatenating segments';
2462 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.no_data',errbuf);
2463 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'WHEN NO DATA FOUND ERROR WHILE CONCATENATING
2464 segments');
2465 retcode := -1;
2466 reset_control_status;
2467 WHEN OTHERS THEN
2468 errbuf := SQLERRM;
2469 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
2470 retcode := -1;
2471 END concat_segs;
2472 --------------------------------------------------------------------------------
2473 -- Procedure to update revision_num, doc_status
2474 -- and move records to the interface history table once all
2475 -- the records have been successfully validated
2476 PROCEDURE update_cleanup(parm_source IN VARCHAR2,
2477 parm_group_id IN NUMBER) IS
2478
2479 l_module_name VARCHAR2(200);
2480 l_prof_val VARCHAR2(1);
2481
2482 BEGIN
2483 l_module_name := g_module_name || 'update_cleanup';
2484 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2485 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'UPDATING REV NUM IN HEADERS');
2486 END IF;
2487 UPDATE fv_be_trx_hdrs fbh
2488 SET revision_num =
2489 (SELECT MAX(revision_num)
2490 FROM fv_be_trx_dtls fbd
2491 WHERE fbh.doc_id = fbd.doc_id)
2492 WHERE fbh.doc_status = 'IMPORTING';
2493
2494 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2495 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'UPDATING DOC STATUS IN HEADERS
2496 for existing recs');
2497 END IF;
2498 UPDATE fv_be_trx_hdrs
2499 SET doc_status = 'RA'
2500 WHERE doc_status = 'IMPORTING'
2501 AND internal_revision_num <> -9999;
2502
2503 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2504 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'UPDATING DOC STATUS, REV NUM IN
2505 headers for new recs');
2506 END IF;
2507 UPDATE fv_be_trx_hdrs
2508 SET doc_status = 'IN',
2509 internal_revision_num = 0
2510 WHERE doc_status = 'IMPORTING'
2511 AND internal_revision_num = -9999;
2512
2513 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2514 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'UPDATING STATUS IN CONTROL TABLE');
2515 END IF;
2516 UPDATE fv_be_interface_control
2517 SET status = 'IMPORTED'
2518 WHERE source = parm_source
2519 AND group_id = parm_group_id;
2520
2521 l_prof_val := FND_PROFILE.VALUE('FV_ARCH_BE_INT_RECS');
2522
2523 IF l_prof_val = 'Y'
2524 THEN
2525 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2526 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'INSERTING INTO HISTORY TABLE');
2527 END IF;
2528
2529 INSERT INTO fv_be_interface_history
2530 (record_number, set_of_books_id, source, group_id, error_code,
2531 error_reason, budget_level_id,
2532 budgeting_segments, transaction_type, sub_type,
2533 fund_value, period_name, segment1, segment2, segment3,
2534 segment4, segment5, segment6, segment7, segment8, segment9,
2535 segment10, segment11, segment12, segment13, segment14,
2536 segment15, segment16, segment17, segment18, segment19,
2537 segment20, segment21, segment22, segment23, segment24,
2538 segment25, segment26, segment27, segment28, segment29,
2539 segment30, increase_decrease_flag, amount, doc_number,
2540 attribute1, attribute2, attribute3, attribute4, attribute5,
2541 attribute6, attribute7, attribute8, attribute9, attribute10,
2542 attribute11, attribute12, attribute13, attribute14,
2543 attribute15, attribute_category, processed_flag, status,
2544 date_created, created_by, corrected_flag, last_update_date,
2545 last_updated_by, public_law_code, advance_type, dept_id,
2546 main_account, transfer_description,budget_user_id,
2547 gl_date)
2548 SELECT
2549 record_number, set_of_books_id, source, group_id, error_code,
2550 error_reason, budget_level_id,
2551 budgeting_segments, transaction_type, sub_type,
2552 fund_value, period_name, segment1, segment2, segment3,
2553 segment4, segment5, segment6, segment7, segment8, segment9,
2554 segment10, segment11, segment12, segment13, segment14,
2555 segment15, segment16, segment17, segment18, segment19,
2556 segment20, segment21, segment22, segment23, segment24,
2557 segment25, segment26, segment27, segment28, segment29,
2558 segment30, increase_decrease_flag, amount, doc_number,
2559 attribute1, attribute2, attribute3, attribute4, attribute5,
2560 attribute6, attribute7, attribute8, attribute9, attribute10,
2561 attribute11, attribute12, attribute13, attribute14,
2562 attribute15, attribute_category, processed_flag, status,
2563 date_created, created_by, corrected_flag, sysdate,
2564 fnd_global.user_id, public_law_code, advance_type,
2565 dept_id, main_account, transfer_description,budget_user_id,
2566 gl_date
2567 FROM fv_be_interface
2568 WHERE source = parm_source
2569 AND group_id = parm_group_id
2570 AND set_of_books_id = parm_ledger_id
2571 AND status = 'ACCEPTED'
2572 AND processed_flag = 'Y';
2573 END IF;
2574
2575 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2576 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'DELETING FROM BE_INTERFACE');
2577 END IF;
2578 DELETE FROM fv_be_interface
2579 WHERE source = parm_source
2580 AND group_id = parm_group_id
2581 AND set_of_books_id = parm_ledger_id
2582 AND status = 'ACCEPTED'
2583 AND processed_flag = 'Y';
2584 EXCEPTION
2585 WHEN NO_DATA_FOUND THEN
2586 retcode := -1;
2587 errbuf := 'When no data found error in update_cleanup';
2588 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.no_data',errbuf);
2589 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'WHEN NO DATA FOUND ERROR IN UPDATE_CLEANUP');
2590 reset_control_status;
2591 WHEN OTHERS THEN
2592 retcode := -1;
2593 errbuf := 'When others error in update_cleanup.'||SQLERRM;
2594 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
2595 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'WHEN OTHERS ERROR IN UPDATE_CLEANUP');
2596 reset_control_status;
2597 END update_cleanup;
2598 --------------------------------------------------------------------------------
2599 PROCEDURE update_err_rec(v_rec_number IN NUMBER) IS
2600 l_module_name VARCHAR2(200);
2601 BEGIN
2602 l_module_name := g_module_name || 'update_err_rec';
2603
2604 UPDATE fv_be_interface
2605 -- SET status = 'REJECTED',
2606 SET status = 'ACCEPTED',
2607 error_code = 'EM42',
2608 error_reason = 'Budgeting Segments do not exist for
2609 previous budget level'
2610 WHERE record_number = v_rec_number;
2611
2612 COMMIT;
2613 EXCEPTION
2614 WHEN OTHERS THEN
2615 retcode := -1;
2616 errbuf := SQLERRM;
2617 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
2618 RAISE;
2619 END update_err_rec;
2620 --------------------------------------------------------------------------------
2621 -- This procedure resets the status in the control table
2622 -- whenever there is a when-others error and processing
2623 -- cannot continue
2624 PROCEDURE reset_control_status IS
2625 l_module_name VARCHAR2(200);
2626 PRAGMA AUTONOMOUS_TRANSACTION;
2627 BEGIN
2628 l_module_name := g_module_name || 'reset_control_status';
2629 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2630 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RESETTING STATUS IN THE CONTROL TABLE');
2631 END IF;
2632 UPDATE fv_be_interface_control
2633 SET status = 'REJECTED'
2634 WHERE source = parm_source
2635 AND group_id = parm_group_id;
2636
2637 COMMIT;
2638
2639 EXCEPTION
2640 WHEN OTHERS THEN
2641 retcode := -1;
2642 errbuf := SQLERRM;
2643 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
2644 RAISE;
2645 END reset_control_status;
2646 --------------------------------------------------------------------------------
2647 BEGIN
2648 --GSCC File.Sql.35 fix
2649 g_module_name := 'fv.plsql.FV_BE_INT_PKG.';
2650 END fv_be_int_pkg;