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