[Home] [Help]
PACKAGE BODY: APPS.IGC_UPGRADE_DATA_R12
Source
1 PACKAGE BODY IGC_UPGRADE_DATA_R12 AS
2 /*$Header: IGCUPGDB.pls 120.13 2008/02/26 13:50:13 mbremkum noship $ */
3
4 /*Global Variables - Start*/
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGC_UPGRADE_DATA_R12';
7 g_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
8 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
9 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
10 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
11 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
12 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
13 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
14 g_path VARCHAR2(255) := 'IGC.PLSQL.IGCUPGDB.IGC_UPGRADE_DATA_R12.';
15 g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
16 g_sob_id NUMBER := 0;
17 g_sob_name VARCHAR2(50) := NULL;
18 g_cbc_ledger_id gl_ledgers.ledger_id%TYPE := 0;
19 g_cbc_ledger_name VARCHAR2(50) := NULL;
20 g_conc_pr_user FND_CONCURRENT_PROGRAMS_VL.user_concurrent_program_name%TYPE;
21
22 TYPE t_typ_errors IS RECORD
23 (error_mesg VARCHAR2(4000)
24 );
25 TYPE t_tbl_errors IS TABLE of t_typ_errors INDEX BY BINARY_INTEGER;
26
27 TYPE t_typ_bud_map IS RECORD
28 (old_budget_name GL_BUDGETS.BUDGET_NAME%Type,
29 new_budget_name GL_BUDGETS.BUDGET_NAME%Type
30 );
31 TYPE t_tbl_bud_map IS TABLE of t_typ_bud_map INDEX BY BINARY_INTEGER;
32
33 TYPE t_typ_bud_entity_map IS RECORD
34 (old_bud_entity gl_budget_entities.NAME%Type,
35 new_bud_entity gl_budget_entities.NAME%Type
36 );
37 TYPE t_tbl_bud_entity_map IS TABLE of t_typ_bud_entity_map INDEX BY BINARY_INTEGER;
38
39 g_tbl_basic_errors t_tbl_errors;
40 g_tbl_warnings t_tbl_errors;
41 g_tbl_bud_map t_tbl_bud_map ;
42 g_tbl_bud_entity_map t_tbl_bud_entity_map;
43
44 g_final_out VARCHAR2(20) := 'SUCCESS';
45
46 /*Global Variables - End*/
47
48 /*Private Procedures or Functions - Start*/
49
50
51 PROCEDURE add_error (
52 p_error_mesg IN VARCHAR2
53 );
54
55 PROCEDURE add_warning (
56 p_error_mesg IN VARCHAR2
57 );
58
59 PROCEDURE set_final_out (
60 p_final_out IN VARCHAR2
61 );
62
63 PROCEDURE add_bud_map (
64 p_old_budget_name GL_BUDGETS.BUDGET_NAME%Type,
65 p_new_budget_name GL_BUDGETS.BUDGET_NAME%Type
66 );
67
68 PROCEDURE add_bud_entity_map (
69 p_old_bud_entity gl_budget_entities.NAME%Type,
70 p_new_bud_entity gl_budget_entities.NAME%Type
71 );
72
73 PROCEDURE Put_Debug_Msg (
74 p_path IN VARCHAR2,
75 p_debug_msg IN VARCHAR2
76 );
77
78 PROCEDURE Populate_Interface (p_fiscal_year IN NUMBER,
79 p_org_id IN NUMBER,
80 p_cc_header_id IN NUMBER,
81 p_category_name IN VARCHAR2,
82 p_transaction_date IN DATE,
83 p_cbc_je_batch_id IN NUMBER,
84 x_doc_type OUT NOCOPY VARCHAR2);
85
86 PROCEDURE Migrate_cbc_lines ( errbuf OUT NOCOPY VARCHAR2,
87 retcode OUT NOCOPY VARCHAR2,
88 p_fiscal_year IN NUMBER,
89 p_org_id IN NUMBER);
90
91 PROCEDURE GET_NEW_NAME ( P_OLD_NAME IN VARCHAR2,
92 P_LEN IN NUMBER,
93 P_FLAG IN VARCHAR2,
94 P_NEW_NAME IN OUT NOCOPY VARCHAR2);
95
96 PROCEDURE MIGRATE_GL_BUDGETS ( P_OLD_BUD_NAME IN VARCHAR2,
97 P_MASTER_BUD_VER_ID IN NUMBER,
98 P_BUDGET_VERSION_ID OUT NOCOPY GL_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
99 P_LATEST_OPENED_YEAR OUT NOCOPY Gl_BUDGETS_V.LATEST_OPENED_YEAR%TYPE);
100
101
102 PROCEDURE GL_BUDGET_ORG ( P_BUDGET_NAME IN GL_BUDGETS.BUDGET_NAME%TYPE,
103 P_BUDGET_VERSION_ID IN NUMBER,
104 P_BUDGET_ENTITY_ID OUT NOCOPY NUMBER);
105
106 PROCEDURE submit_open_year (P_sec_ledger_id IN Number,
107 Bud_Version_Id IN NUMBER,
108 p_latest_open_year IN GL_BUDGETS_V.Latest_opened_year%type,
109 p_fiscal_year IN NUMBER);
110
111 PROCEDURE migrate_cbc_bud_jounals(p_fiscal_year IN NUMBER);
112
113 PROCEDURE submit_assign_ranges(P_sec_ledger_id IN Number,
114 p_bud_entity_id IN Number);
115
116 PROCEDURE migrate_sum_templates;
117
118 FUNCTION get_cbc_budget_name(p_pri_budget_name IN VARCHAR2) RETURN VARCHAR2;
119 /*Private Procedures or Functions - End*/
120
121 /*Moved this procedure definition from the package spec to the package body - Bug 6847410*/
122
123 PROCEDURE Migrate_Budgets(p_fiscal_year IN Number,
124 p_org_id IN Number,
125 p_period_set_name IN gl_sets_of_books.period_set_name%type,
126 p_accounted_period_type IN gl_sets_of_books.accounted_period_type%type);
127
128 PROCEDURE validate_basic(p_org_id IN Number,
129 p_fin_year IN NUMBER,
130 p_balance_type IN VARCHAR2,
131 x_return_code OUT NOCOPY NUMBER,
132 x_msg_buf OUT NOCOPY VARCHAR2
133 );
134
135 FUNCTION check_request(p_request_id IN NUMBER) RETURN NUMBER;
136
137 PROCEDURE print_header(p_balance_type IN VARCHAR2,
138 p_mode IN VARCHAR2,
139 p_fiscal_year IN NUMBER);
140
141 PROCEDURE print_errors;
142
143 PROCEDURE print_enc_stat(p_fiscal_year IN NUMBER);
144
145 PROCEDURE print_enc_exceptions(p_fiscal_year IN NUMBER);
146
147 PROCEDURE print_budget_stats;
148
149 PROCEDURE print_end_report;
150
151 PROCEDURE Validate_Setup_and_Migrate ( errbuf OUT NOCOPY VARCHAR2,
152 retcode OUT NOCOPY VARCHAR2,
153 p_balance_type IN VARCHAR2,
154 p_mode IN VARCHAR2,
155 p_fiscal_year IN NUMBER) IS
156
157 l_period_set_name gl_sets_of_books.period_set_name%TYPE;
158 l_accounted_period_type gl_sets_of_books.accounted_period_type%TYPE;
159 l_number_per_fiscal_year gl_period_types.number_per_fiscal_year%TYPE;
160 l_open_periods NUMBER;
161
162 l_full_path VARCHAR2(255);
163 l_org_id NUMBER;
164 l_return_num NUMBER := 1;
165
166
167 BEGIN
168 retcode := 0;
169 l_full_path := g_path || 'Validate_Setup_and_Migrate';
170
171 l_org_id := MO_GLOBAL.get_current_org_id;
172
173 IF (g_debug_mode = 'Y') THEN
174 Put_Debug_Msg(l_full_path, 'ORG ID: ' || l_org_id);
175 END IF;
176
177 /*Obtain Set of Books Name and ID*/
178
179 MO_UTILS.get_ledger_info(l_org_id, g_sob_id, g_sob_name);
180
181 IF (g_debug_mode = 'Y') THEN
182 Put_Debug_Msg(l_full_path, 'SOB ID: ' || g_sob_id);
183 Put_Debug_Msg(l_full_path, 'Balance Type: ' || p_balance_type);
184 END IF;
185
186 print_header(p_balance_type,p_mode,p_fiscal_year);
187
188 validate_basic(l_org_id,p_fiscal_year,p_balance_type,retcode,errbuf);
189 IF retcode = 2 THEN
190 print_errors;
191 return;
192 --APP_EXCEPTION.Raise_Exception;
193 END IF;
194
195 SELECT period_set_name, accounted_period_type
196 INTO l_period_set_name, l_accounted_period_type
197 FROM gl_sets_of_books
198 WHERE set_of_books_id = g_sob_id;
199
200 IF (g_debug_mode = 'Y') THEN
201 Put_Debug_Msg(l_full_path, 'Accounting Period Type: ' || l_accounted_period_type);
202 END IF;
203
204 IF (p_mode = 'P') THEN
205
206 IF (g_debug_mode = 'Y') THEN
207 Put_Debug_Msg(l_full_path, 'Preliminary Mode - Return after Validation');
208 END IF;
209 print_errors;
210 RETURN;
211 ELSIF (p_mode = 'F') THEN
212 /* Call Migrate Budgets procedure to migrate budgets and budget org information */
213 IF p_balance_type = 'B' THEN
214 Migrate_Budgets(p_fiscal_year, l_org_id,l_period_set_name,
215 l_accounted_period_type);
216 migrate_cbc_bud_jounals(p_fiscal_year);
217 print_errors;
218 print_budget_stats;
219 print_end_report;
220
221 ELSIF p_balance_type = 'E' THEN
222 /*Reset all failure result codes in IGC_CBC_JE_LINES*/
223 UPDATE igc_cbc_je_lines
224 SET mig_result_code = NULL,
225 mig_request_id = NULL
226 WHERE set_of_books_id = g_sob_id
227 AND substr(mig_result_code, 0, 1) = 'F'
228 AND mig_result_code IS NOT NULL
229 AND period_year = p_fiscal_year
230 AND actual_flag = 'E';
231
232 IF (g_debug_mode = 'Y') THEN
233 Put_Debug_Msg(l_full_path, 'Reset Failure Result Codes: ' || SQL%ROWCOUNT);
234 END IF;
235
236 COMMIT;
237
238 /*Call Migrate only if mode is FINAL*/
239 IF (g_debug_mode = 'Y') THEN
240 Put_Debug_Msg(l_full_path, 'Calling Migrate CBC lines - Final Mode');
241 END IF;
242
243 Migrate_cbc_lines(errbuf, retcode, p_fiscal_year, l_org_id);
244 commit;
245 print_errors;
246 print_enc_stat(p_fiscal_year);
247 print_enc_exceptions(p_fiscal_year);
248 print_end_report;
249 END IF;
250 END IF;
251
252 EXCEPTION
253 WHEN OTHERS THEN
254 errbuf := SQLERRM;
255 retcode := 2;
256 IF (g_debug_mode = 'Y') THEN
257 Put_Debug_Msg(l_full_path, 'CBC Migration Failed with: ' || errbuf);
258 END IF;
259
260 END Validate_Setup_and_Migrate;
261
262
263 PROCEDURE Migrate_cbc_lines ( errbuf OUT NOCOPY VARCHAR2,
264 retcode OUT NOCOPY VARCHAR2,
265 p_fiscal_year IN NUMBER,
266 p_org_id IN NUMBER) IS
267
268 CURSOR c_cbc_line IS
269 SELECT DISTINCT reference_1, effective_date, je_category, cbc_je_batch_id,
270 decode(je_category, 'Provisional', '1', 'Confirmed', '2', 'Budget', '3', 'Requisitions', '1', 'Purchases', '2', '99') seq
271 FROM igc_cbc_je_lines
272 WHERE mig_result_code IS NULL
273 AND mig_request_id IS NULL
274 AND period_year = p_fiscal_year
275 AND set_of_books_id = g_sob_id
276 ORDER BY reference_1, seq;
277
278 l_full_path VARCHAR2(255);
279 l_cbc_line c_cbc_line%ROWTYPE;
280 x_ret_status VARCHAR2(5);
281 x_batch_result_code VARCHAR2(5);
282 l_doc_type VARCHAR2(3);
283 l_return_status BOOLEAN;
284
285 BEGIN
286
287 l_full_path := g_path || 'Migrate_cbc_lines';
288
289 retcode := 0;
290 errbuf:= '';
291
292 OPEN c_cbc_line;
293
294 LOOP
295 FETCH c_cbc_line INTO l_cbc_line;
296 EXIT WHEN c_cbc_line%NOTFOUND;
297
298 l_doc_type := NULL;
299
300 IF (g_debug_mode = 'Y') THEN
301 Put_Debug_Msg(l_full_path, 'CC Header ID: ' || l_cbc_line.reference_1 || ' Category: ' || l_cbc_line.je_category || ' Effective Date: ' || l_cbc_line.effective_date || ' CBC JE Batch ID: ' || l_cbc_line.cbc_je_batch_id);
302 END IF;
303
304 Populate_Interface (p_fiscal_year,
305 p_org_id,
306 l_cbc_line.reference_1,
307 l_cbc_line.je_category,
308 l_cbc_line.effective_date,
309 l_cbc_line.cbc_je_batch_id,
310 l_doc_type);
311
312 IF (g_debug_mode = 'Y') THEN
313 Put_Debug_Msg(l_full_path, 'Document Type: ' || l_doc_type);
314 END IF;
315
316 -- commit after populating interface table
317 COMMIT;
318
319 l_return_status := IGC_CBC_FUNDS_CHECKER.IGCFCK (g_sob_id, /*SOB ID*/
320 l_cbc_line.reference_1, /*CC Header ID*/
321 'R', /*Call Funds Check in Reserve Mode*/
322 'E', /*Actual Flag = 'E' i.e. Encumbrance*/
323 l_doc_type,
324 x_ret_status,
325 x_batch_result_code,
326 FND_API.G_FALSE,
327 FND_API.G_FALSE);
328
329 IF x_batch_result_code IN ('H00','H04','H12') THEN
330
331 FND_MESSAGE.set_name('IGC','IGC_CC_CBC_RESULT_CODE_'||x_batch_result_code);
332 errbuf := FND_MESSAGE.get;
333 retcode := 1;
334
335 END IF;
336
337 IF l_return_status = FALSE and x_batch_result_code IS NULL THEN
338 errbuf := 'Unexpected Error occured during Migration';
339 retcode := 2;
340 return;
341 END IF;
342
343 IF (g_debug_mode = 'Y') THEN
344 Put_Debug_Msg(l_full_path, 'Funds Check Result Code for CC Header ID ' || l_cbc_line.reference_1 || ': ' || x_batch_result_code);
345 END IF;
346
347 /*Update Result Codes in igc_cbc_je_lines for Exception reporting and to ensure CP rerun does not cause issues*/
348
349 /*Update Request ID of the CBC Upgrade Concurrent program*/
350
351 UPDATE igc_cbc_je_lines
352 SET mig_request_id = FND_GLOBAL.CONC_REQUEST_ID
353 WHERE reference_1 = l_cbc_line.reference_1
354 AND je_category = l_cbc_line.je_category
355 AND effective_date = l_cbc_line.effective_date
356 AND cbc_je_batch_id = l_cbc_line.cbc_je_batch_id;
357
358 /*Update CBC result Codes into IGC_CBC_JE_LINES from IGC_CC_INTERFACE to print Exception Report*/
359
360 UPDATE igc_cbc_je_lines ijl
361 SET (mig_result_code) = (SELECT cbc_result_code
362 FROM igc_cc_interface ict
363 WHERE ijl.reference_1 = ict.cc_header_id
364 AND ijl.effective_date = ict.cc_transaction_date
365 AND NVL(ijl.je_category, '') = NVL(ict.je_category_name, '')
366 AND ijl.code_combination_id = ict.code_combination_id)
367 WHERE ijl.reference_1 = l_cbc_line.reference_1
368 AND je_category = l_cbc_line.je_category
369 AND effective_date = l_cbc_line.effective_date
370 AND cbc_je_batch_id = l_cbc_line.cbc_je_batch_id;
371
372 IF (g_debug_mode = 'Y') THEN
373 Put_Debug_Msg(l_full_path, 'Updating Result Code. Number of rows updated: ' || SQL%ROWCOUNT);
374 END IF;
375
376 /*Entries in igc_cc_interface needs to be flushed as rerun of the CP may cause issues */
377 DELETE FROM igc_cc_interface
378 WHERE reference_8 = 'MIG';
379
380 commit;
381 END LOOP;
382
383 EXCEPTION
384
385 WHEN OTHERS THEN
386 IF (g_debug_mode = 'Y') THEN
387 Put_Debug_Msg(l_full_path, SQLERRM);
388 END IF;
389 /*Added by mbremkum*/
390 APP_EXCEPTION.Raise_Exception;
391
392 END Migrate_cbc_lines;
393
394
395 PROCEDURE Populate_Interface ( p_fiscal_year IN NUMBER,
396 p_org_id IN NUMBER,
397 p_cc_header_id IN NUMBER,
398 p_category_name IN VARCHAR2,
399 p_transaction_date IN DATE,
400 p_cbc_je_batch_id IN NUMBER,
401 x_doc_type OUT NOCOPY VARCHAR2) IS
402
403 CURSOR c_cbc_je_lines IS
404 SELECT icj.reference_1,
405 icj.reference_3,
406 icj.reference_2,
407 icj.code_combination_id,
408 icj.cbc_je_line_num,
409 icj.effective_date,
410 icj.entered_dr,
411 icj.entered_cr,
412 icj.je_source,
413 icj.je_category,
414 icj.period_name,
415 icj.actual_flag,
416 'C',
417 icj.set_of_books_id,
418 icj.description,
419 icj.posted_date,
420 DECODE(icj.je_source,'Contract Commitment','CC','Project Accounting','PA','Requisitions','REQ','Purchasing','PO','INV'),
421 icj.currency_code
422 FROM igc_cbc_je_lines icj, gl_code_combinations gcc
423 WHERE icj.set_of_books_id = g_sob_id
424 AND icj.reference_1 = p_cc_header_id
425 AND icj.je_category = p_category_name
426 AND icj.effective_date = p_transaction_date
427 AND icj.cbc_je_batch_id = p_cbc_je_batch_id
428 AND icj.period_year = p_fiscal_year
429 AND icj.actual_flag = 'E'
430 AND icj.mig_result_code IS NULL /*Only Records NOT migrated are processed*/
431 AND icj.code_combination_id = gcc.code_combination_id
432 AND gcc.summary_flag = 'N'; /*Migrate only for Detailed Records*/
433
434 l_cbc_je_lines c_cbc_je_lines%ROWTYPE;
435 l_reference_4 VARCHAR2(240);
436 l_full_path VARCHAR2(255);
437
438 BEGIN
439
440 l_full_path := g_path || 'Populate_Interface';
441
442 IF (g_debug_mode = 'Y') THEN
443 Put_Debug_Msg(l_full_path, 'In Populate_Interface');
444 Put_Debug_Msg(l_full_path, 'Parameters Fiscal Year:' || p_fiscal_year ||' CC Header ID: ' || p_cc_header_id || ' OrgID: ' ||p_org_id || ' CBC JE Batch ID: ' || p_cbc_je_batch_id || ' Category: ' || p_category_name);
445 END IF;
446
447 OPEN c_cbc_je_lines;
448 LOOP
449
450 FETCH c_cbc_je_lines INTO l_cbc_je_lines;
451 EXIT WHEN c_cbc_je_lines%NOTFOUND;
452
453 SELECT decode(l_cbc_je_lines.JE_SOURCE, 'Contract Commitment', 'CC', 'Project Accounting', 'PA', 'Requisitions', 'REQ', 'Purchasing', 'PO', 'INV')
454 INTO x_doc_type
455 FROM DUAL;
456
457 INSERT INTO igc_cc_interface(
458 CC_HEADER_ID,
459 CC_VERSION_NUM,
460 CC_ACCT_LINE_ID,
461 CC_DET_PF_LINE_ID,
462 CODE_COMBINATION_ID,
463 BATCH_LINE_NUM,
464 CC_TRANSACTION_DATE,
465 CC_FUNC_DR_AMT,
466 CC_FUNC_CR_AMT,
467 JE_SOURCE_NAME,
468 JE_CATEGORY_NAME,
469 PERIOD_SET_NAME,
470 PERIOD_NAME,
471 ACTUAL_FLAG,
472 BUDGET_DEST_FLAG,
473 SET_OF_BOOKS_ID,
474 CBC_RESULT_CODE,
475 STATUS_CODE,
476 REFERENCE_1,
477 REFERENCE_2,
478 REFERENCE_3,
479 REFERENCE_8,
480 BATCH_ID,
481 BUDGET_VERSION_ID,
482 TRANSACTION_DESCRIPTION,
483 CC_ENCMBRNC_DATE,
484 DOCUMENT_TYPE,
485 CURRENCY_CODE,
486 LAST_UPDATE_DATE,
487 LAST_UPDATED_BY,
488 CREATION_DATE,
489 CREATED_BY
490 )
491
492 VALUES(
493 l_cbc_je_lines.REFERENCE_1,
494 l_cbc_je_lines.REFERENCE_3,
495 l_cbc_je_lines.REFERENCE_2,
496 NULL,
497 l_cbc_je_lines.CODE_COMBINATION_ID,
498 l_cbc_je_lines.CBC_JE_LINE_NUM,
499 l_cbc_je_lines.EFFECTIVE_DATE,
500 l_cbc_je_lines.ENTERED_DR,
501 l_cbc_je_lines.ENTERED_CR,
502 l_cbc_je_lines.JE_SOURCE,
503 l_cbc_je_lines.JE_CATEGORY,
504 NULL,
505 l_cbc_je_lines.PERIOD_NAME,
506 l_cbc_je_lines.ACTUAL_FLAG,
507 'C',
508 l_cbc_je_lines.SET_OF_BOOKS_ID,
509 NULL,
510 NULL,
511 l_cbc_je_lines.REFERENCE_1,
512 l_cbc_je_lines.REFERENCE_2,
513 l_cbc_je_lines.REFERENCE_3,
514 'MIG',
515 NULL,
516 NULL,
517 l_cbc_je_lines.DESCRIPTION,
518 l_cbc_je_lines.POSTED_DATE,
519 x_doc_type,
520 l_cbc_je_lines.CURRENCY_CODE ,
521 sysdate,
522 -1,
523 sysdate,
524 -1
525 );
526
527 IF (g_debug_mode = 'Y') THEN
528 Put_Debug_Msg(l_full_path, 'Number of rows Inserted: ' || SQL%ROWCOUNT);
529 END IF;
530
531 IF (l_cbc_je_lines.JE_SOURCE = 'Project Accounting') THEN
532 SELECT distinct segment1 INTO l_reference_4 FROM pa_projects_all p, pa_budget_versions bv
533 WHERE p.project_id = bv.project_id AND bv.budget_version_id = p_cc_header_id;
534 ELSIF (l_cbc_je_lines.JE_SOURCE = 'Purchasing') THEN
535 SELECT distinct segment1 INTO l_reference_4 FROM PO_HEADERS_ALL
536 WHERE po_header_id = p_cc_header_id;
537 ELSIF (l_cbc_je_lines.JE_SOURCE = 'Requisitions') THEN
538 SELECT distinct segment1 INTO l_reference_4 FROM PO_REQUISITION_HEADERS_ALL
539 WHERE requisition_header_id = p_cc_header_id;
540 ELSIF (l_cbc_je_lines.JE_SOURCE = 'Contract Commitment') THEN
541 BEGIN
542 SELECT distinct cc_num INTO l_reference_4 FROM igc_cc_headers_all
543 WHERE cc_header_id = p_cc_header_id;
544 EXCEPTION
545 WHEN NO_DATA_FOUND THEN
546 l_reference_4 := null;
547 END;
548 END IF;
549
550 IF l_reference_4 IS NULL THEN
551 l_reference_4 := SUBSTR(l_cbc_je_lines.DESCRIPTION,0,INSTR(l_cbc_je_lines.DESCRIPTION, ' ')-1);
552 END IF;
553
554 IF (g_debug_mode = 'Y') THEN
555 Put_Debug_Msg(l_full_path, 'Reference 4: ' || l_reference_4);
556 END IF;
557
558 UPDATE igc_cc_interface
559 SET reference_4 = l_reference_4
560 WHERE cc_header_id = p_cc_header_id;
561
562 END LOOP;
563
564 CLOSE c_cbc_je_lines;
565
566 EXCEPTION
567
568 WHEN OTHERS THEN
569 IF (g_debug_mode = 'Y') THEN
570 Put_Debug_Msg(l_full_path, SQLERRM);
571 END IF;
572 /*Added by mbremkum*/
573 APP_EXCEPTION.Raise_Exception;
574
575 END Populate_Interface;
576
577
578 /* Start of changes for Budgets Migration */
579
580 PROCEDURE Migrate_Budgets(p_fiscal_year IN Number,
581 p_org_id IN Number,
582 p_period_set_name IN gl_sets_of_books.period_set_name%type,
583 p_accounted_period_type IN gl_sets_of_books.accounted_period_type%type) IS
584
585 v_period_start_num gl_periods.period_num%type;
586 v_period_end_num gl_periods.period_num%type;
587 v_period_start_name gl_budgets.first_valid_period_name%type;
588 v_period_end_name gl_budgets.last_valid_period_name%type;
589 v_budget_version_id gl_budget_versions.budget_version_id%type;
590 v_latest_opened_year gl_budgets_v.LATEST_OPENED_YEAR%type;
591
592 v_bud_entity_id gl_budget_entities.budget_entity_id%type;
593 l_full_path VARCHAR2(255);
594
595 cursor c_budget_cur is Select budget_name
596 from gl_budgets_v
597 where ledger_id = g_sob_id
598 and first_valid_period_name = v_period_start_name
599 and last_valid_period_name = v_period_end_name
600 and master_budget_version_id is null;
601
602 cursor c_master_cur is Select budget_name,master_budget_version_id
603 from gl_budgets_v
604 where ledger_id = g_sob_id
605 and first_valid_period_name = v_period_start_name
606 and last_valid_period_name = v_period_end_name
607 and master_budget_version_id is not null;
608
609 Begin
610
611 l_full_path := g_path || 'Migrate_Budgets';
612 IF (g_debug_mode = 'Y') THEN
613 Put_Debug_Msg(l_full_path, 'Primary Ledger ID ' || g_sob_id );
614 END IF;
615 select min(period_num),max(period_num)
616 into v_period_start_num,v_period_end_num
617 from gl_periods
618 where period_set_name = p_period_set_name
619 and period_year = p_fiscal_year
620 and Period_type = p_accounted_period_type ;
621
622 select period_name into v_period_start_name from gl_periods where period_num = v_period_start_num
623 and period_set_name = p_period_set_name
624 and period_year = p_fiscal_year
625 and Period_type = p_accounted_period_type;
626
627 select period_name into v_period_end_name from gl_periods where period_num = v_period_end_num
628 and period_set_name = p_period_set_name
629 and period_year = p_fiscal_year
630 and Period_type = p_accounted_period_type;
631
632 For c1 in c_budget_cur
633 Loop
634
635 Migrate_GL_Budgets(c1.budget_name,NULL,v_budget_version_id,v_latest_opened_year);
636
637 IF (g_debug_mode = 'Y') THEN
638 Put_Debug_Msg(l_full_path, 'New Budget Version Id created for new budget ' || v_budget_version_id );
639 END IF;
640 submit_open_year(g_cbc_ledger_id,v_budget_version_id,v_latest_opened_year,p_fiscal_year);
641 IF (g_debug_mode = 'Y') THEN
642 Put_Debug_Msg(l_full_path, 'Completed GL Submit Open year');
643 END IF;
644 GL_Budget_Org(c1.budget_name,v_budget_version_id,v_bud_entity_id);
645 IF (g_debug_mode = 'Y') THEN
646 Put_Debug_Msg(l_full_path, 'Completed GL_Budget_Org');
647 END IF;
648 Commit; --Need to commit before submitting "assign Budget ranges " program.
649 submit_assign_ranges(g_cbc_ledger_id,v_bud_entity_id);
650 IF (g_debug_mode = 'Y') THEN
651 Put_Debug_Msg(l_full_path, 'Completed submit_assign_ranges');
652 END IF;
653 end loop;
654 Commit; --We need to commit all orphan budgets first .
655 FOR c2 in c_master_cur
656 LOOP
657 Migrate_GL_Budgets(c2.budget_name,c2.master_budget_version_id,
658 v_budget_version_id,v_latest_opened_year);
659
660 submit_open_year(g_cbc_ledger_id,v_budget_version_id,v_latest_opened_year,p_fiscal_year);
661 IF (g_debug_mode = 'Y') THEN
662 Put_Debug_Msg(l_full_path, 'Completed GL Submit Open year');
663 END IF;
664 GL_Budget_Org(c2.budget_name,v_budget_version_id,v_bud_entity_id);
665 IF (g_debug_mode = 'Y') THEN
666 Put_Debug_Msg(l_full_path, 'Completed GL_Budget_Org');
667 END IF;
668 Commit; --Need to commit before submitting "assign Budget ranges " program.
669 IF (g_debug_mode = 'Y') THEN
670 Put_Debug_Msg(l_full_path, 'Completed GL Budget Org');
671 END IF;
672 submit_assign_ranges(g_cbc_ledger_id,v_bud_entity_id);
673 IF (g_debug_mode = 'Y') THEN
674 Put_Debug_Msg(l_full_path, 'Completed submit_assign_ranges');
675 END IF;
676 END Loop;
677 Commit;
678 migrate_sum_templates;
679 EXCEPTION
680 WHEN OTHERS THEN
681 IF (g_debug_mode = 'Y') THEN
682 Put_Debug_Msg(l_full_path, SQLERRM);
683 END IF;
684 /*Added by mbremkum*/
685 APP_EXCEPTION.Raise_Exception;
686 END Migrate_Budgets;
687
688 PROCEDURE get_new_name(p_old_name in varchar2,
689 p_len in number,p_flag IN varchar2,
690 p_new_name in out NOCOPY varchar2) IS
691 cnt number;
692 l_full_path VARCHAR2(255);
693 BEGIN
694 l_full_path := g_path || 'get_new_name';
695
696 if lengthb(p_old_name) <= p_len then
697 select p_old_name||'_MIG' into p_new_name from dual;
698
699 else
700 select substrb(p_old_name,1,decode(instrb(p_old_name,'_',-1),p_len,p_len-1,p_len))||'_MIG' into p_new_name
701 from dual;
702
703 end if;
704 IF p_flag = 'BUD' THEN
705 select count(*) into cnt from gl_budgets
706 where budget_name = p_new_name
707 and description not like '%R12_MIG_'||p_old_name;
708 ELSIF p_flag = 'ORG' THEN
709 select count(*) into cnt from gl_budget_entities
710 Where name = p_new_name
711 and description not like '%R12_MIG_'||p_old_name;
712 ELSE
713 IF (g_debug_mode = 'Y') THEN
714 Put_Debug_Msg(l_full_path, 'Input passed to p_flag parameter should be either BUD or ORG ');
715 END IF;
716
717 cnt := 0;
718 END IF;
719
720 IF cnt <>0 and p_len >1 then
721 get_new_name(p_old_name,p_len-1,p_flag,p_new_name);
722 else
723 IF (g_debug_mode = 'Y') THEN
724 Put_Debug_Msg(l_full_path, 'If new name does not exist ,then exiting from recursive function');
725 END IF;
726
727 end if;
728 end get_new_name;
729
730
731 PROCEDURE Migrate_GL_Budgets(p_old_bud_name in varchar2, p_Master_Bud_Ver_Id in number,
732 p_budget_version_id Out NOCOPY gl_budget_versions.budget_version_id%type,
733 p_latest_opened_year OUT NOCOPY gl_budgets_v.LATEST_OPENED_YEAR%type) is
734
735 v_new_budget gl_budgets.budget_name%type;
736
737 --p_budget_version_id gl_budget_versions.budget_version_id%type;
738
739 V_Master_Budget_Version_Id gl_budget_versions.budget_version_id%type;
740 BUDGET gl_budgets%rowtype;
741
742 v_row_id varchar2(50) := NULL;
743 l_full_path VARCHAR2(255);
744 CURSOR bud_cur(c_new_budget IN varchar2) IS
745 select budget_version_id, latest_opened_year
746 FROM gl_budgets_v
747 WHERE budget_name = c_new_budget;
748 BEGIN
749 l_full_path := g_path || 'Migrate_GL_Budgets';
750 get_new_name(p_old_bud_name,11,'BUD',v_new_budget);
751
752 IF (g_debug_mode = 'Y') THEN
753 Put_Debug_Msg(l_full_path, 'New Budget Name created: ' || v_new_budget);
754 END IF;
755
756 OPEN bud_cur(v_new_budget);
757 LOOP
758 FETCH bud_cur
759 into p_budget_version_id, p_latest_opened_year;
760 IF bud_cur%rowcount = 0 THEN
761 BEGIN
762 select * into BUDGET
763 from gl_budgets
764 where budget_name = p_old_bud_name;
765 p_budget_version_id := gl_budgets_pkg.get_unique_id;
766
767 IF (g_debug_mode = 'Y') THEN
768 Put_Debug_Msg(l_full_path, 'New Budget Version ID created: ' || p_budget_version_id );
769 END IF;
770
771 if length(BUDGET.Description) > 215 then
772 BUDGET.Description := substrb(BUDGET.Description,1,215)||'R12_MIG_'||BUDGET.budget_name;
773 else
774 BUDGET.Description := BUDGET.Description||'R12_MIG_'||BUDGET.budget_name;
775 End if;
776
777 GL_BUDGETS_PKG.Insert_Row(
778 X_Rowid => v_row_id,
779 X_Budget_Type => BUDGET.Budget_Type,
780 X_Budget_Name => v_new_budget,
781 X_ledger_Id => g_cbc_ledger_id,
782 X_Status => 'O', --BUDGET.Status,
783 X_Date_Created => BUDGET.Date_Created,
784 X_Require_Budget_Journals_flag => BUDGET.Require_Budget_Journals_Flag,
785 X_Current_Version_Id => BUDGET.Current_Version_Id,
786 X_Latest_Opened_Year => NULL,
787 X_First_Valid_Period_Name => BUDGET.First_Valid_Period_Name,
788 X_Last_Valid_Period_Name => BUDGET.Last_Valid_Period_Name,
789 X_Description => BUDGET.Description,
790 X_Date_Closed => BUDGET.Date_Closed,
791 X_Attribute1 => BUDGET.Attribute1,
792 X_Attribute2 => BUDGET.Attribute2,
793 X_Attribute3 => BUDGET.Attribute3,
794 X_Attribute4 => BUDGET.Attribute4,
795 X_Attribute5 => BUDGET.Attribute5,
796 X_Attribute6 => BUDGET.Attribute6,
797 X_Attribute7 => BUDGET.Attribute7,
798 X_Attribute8 => BUDGET.Attribute8,
799 X_Context => BUDGET.Context,
800 X_User_Id => BUDGET.Created_By,
801 X_Login_Id => BUDGET.Last_Update_Login,
802 X_Date => BUDGET.Creation_Date,
803 X_Budget_Version_Id => p_budget_version_id,
804 X_Master_Budget_Version_Id => p_Master_Bud_Ver_Id);
805
806 add_bud_map(p_old_bud_name,v_new_budget);
807 END;
808 END IF;
809 EXIT when bud_cur%notfound;
810 END LOOP;
811 CLOSE bud_cur;
812 EXCEPTION
813 WHEN OTHERS THEN
814 IF bud_cur%ISOPEN THEN
815 CLOSE bud_cur;
816 END IF;
817 IF (g_debug_mode = 'Y') THEN
818 Put_Debug_Msg(l_full_path, 'Exception occured in Migrate_GL_Budgets' || SQLERRM);
819 END IF;
820 /*Added by mbremkum*/
821 APP_EXCEPTION.Raise_Exception;
822 END Migrate_GL_Budgets;
823
824
825 PROCEDURE GL_Budget_Org(P_BUDGET_NAME In gl_budgets.budget_name%type,
826 P_budget_version_id IN Number,
827 p_budget_entity_id out NOCOPY number) IS
828
829 v_budget_entity_id gl_budget_entities.budget_entity_id%type;
830 X_Name gl_budget_entities.name%type;
831 bud_org_rec gl_budget_entities%rowtype;
832 /* Cursor to loop through all account ranges defined for a budget organization */
833 Cursor range_cur is SELECT *
834 FROM GL_BUDGET_ASSIGNMENT_RANGES
835 WHERE budget_entity_id = v_budget_entity_id;
836
837 /* Cursor to loop through all funding budgets assigned to a specific account range */
838 CURSOR BC_CUR(p_range_id IN Number) IS
839 Select * from GL_BUDORG_BC_OPTIONS
840 Where RANGE_ID = p_range_id;
841 v_range_id varchar2(100) := NULL;
842 V_Org_Description gl_budget_entities.description%type;
843 v_cbc_override gl_budorg_bc_options.funds_check_level_code%type;
844 v_coa_id gl_ledgers.chart_of_accounts_id%type;
845 v_new_range_id GL_BUDGET_ASSIGNMENT_RANGES.range_id%type;
846 l_full_path VARCHAR2(255);
847 /* Cursor to check whether the new organization is already migrated */
848 CURSOR budorg_cur(c_new_org_name IN VARCHAR2) IS
849 SELECT budget_entity_id from gl_budget_entities where name = c_new_org_name;
850
851 BEGIN
852
853 l_full_path := g_path || 'GL_Budget_Org';
854
855 IF (g_debug_mode = 'Y') THEN
856 Put_Debug_Msg(l_full_path, 'Starting');
857 END IF;
858
859 BEGIN
860 SELECT distinct budget_entity_id into v_budget_entity_id
861 FROM GL_BUDGET_ASSIGNMENT_RANGES_V
862 WHERE range_id IN
863 ( Select range_id From GL_BUDORG_BC_OPTIONS_V
864 Where FUNDING_BUDGET_NAME= P_BUDGET_NAME);
865 EXCEPTION
866 WHEN NO_DATA_FOUND THEN
867 return;
868 END;
869
870 IF (g_debug_mode = 'Y') THEN
871 Put_Debug_Msg(l_full_path, 'Old Budget Entity ID: ' || v_budget_entity_id );
872 END IF;
873
874 select * into bud_org_rec from gl_budget_entities
875 where budget_entity_id = v_budget_entity_id;
876
877 get_new_name(bud_org_rec.name,21,'ORG',X_Name);
878 IF (g_debug_mode = 'Y') THEN
879 Put_Debug_Msg(l_full_path, 'New Budget Entity ID: ' || p_budget_entity_id ||' New Budget Org Name: '|| X_Name );
880 END IF;
881
882 OPEN budorg_cur(x_name);
883 LOOP
884 FETCH budorg_cur into p_budget_entity_id;
885 IF budorg_cur%rowcount =0 THEN
886 /* Insert only if new bud org is not migrated already */
887 BEGIN
888 If lengthb(bud_org_rec.Description) > 207 then
889 V_Org_Description := substrb(bud_org_rec.Description,1,207)||'R12_MIG_'||bud_org_rec.name;
890
891 Else
892 V_Org_Description := bud_org_rec.Description||'R12_MIG_'||bud_org_rec.name;
893 END IF;
894 /************ Insert the corresponding rows in gl_entity_budgets. */
895 p_budget_entity_id := gl_budget_entities_pkg.get_unique_id;
896
897 INSERT INTO GL_ENTITY_BUDGETS
898 (budget_entity_id, budget_version_id, frozen_flag,
899 created_by, creation_date,
900 last_updated_by, last_update_date, last_update_login)
901 SELECT p_budget_entity_id, bv.budget_version_id, 'N',
902 bud_org_rec.last_updated_by, sysdate,
903 bud_org_rec.last_updated_by, sysdate,
904 bud_org_rec.last_update_login
905 FROM gl_budgets b, gl_budget_versions bv
906 WHERE b.ledger_id = bud_org_rec.Ledger_Id
907 AND bv.budget_name = b.budget_name
908 AND bv.budget_type = b.budget_type;
909
910 /************ Insert Budget Organization */
911
912 INSERT INTO gl_budget_entities(
913 budget_entity_id,
914 name,
915 ledger_id,
916 last_update_date,
917 last_updated_by,
918 budget_password_required_flag,
919 status_code,
920 creation_date,
921 created_by,
922 last_update_login,
923 encrypted_budget_password,
924 description,
925 start_date,
926 end_date,
927 segment1_type,
928 segment2_type,
929 segment3_type,
930 segment4_type,
931 segment5_type,
932 segment6_type,
933 segment7_type,
934 segment8_type,
935 segment9_type,
936 segment10_type,
937 segment11_type,
938 segment12_type,
939 segment13_type,
940 segment14_type,
941 segment15_type,
942 segment16_type,
943 segment17_type,
944 segment18_type,
945 segment19_type,
946 segment20_type,
947 segment21_type,
948 segment22_type,
949 segment23_type,
950 segment24_type,
951 segment25_type,
952 segment26_type,
953 segment27_type,
954 segment28_type,
955 segment29_type,
956 segment30_type,
957 attribute1,
958 attribute2,
959 attribute3,
960 attribute4,
961 attribute5,
962 attribute6,
963 attribute7,
964 attribute8,
965 attribute9,
966 attribute10,
967 context,
968 security_flag)
969 VALUES (
970
971 p_budget_entity_id,
972 X_Name,
973 g_cbc_ledger_id,
974 bud_org_rec.Last_Update_Date,
975 bud_org_rec.Last_Updated_By,
976 bud_org_rec.Budget_Password_Required_Flag,
977 bud_org_rec.Status_Code,
978 bud_org_rec.Creation_Date,
979 bud_org_rec.Created_By,
980 bud_org_rec.Last_Update_Login,
981 bud_org_rec.Encrypted_Budget_Password,
982 V_Org_Description,
983 bud_org_rec.Start_Date,
984 bud_org_rec.End_Date,
985 bud_org_rec.Segment1_Type,
986 bud_org_rec.Segment2_Type,
987 bud_org_rec.Segment3_Type,
988 bud_org_rec.Segment4_Type,
989 bud_org_rec.Segment5_Type,
990 bud_org_rec.Segment6_Type,
991 bud_org_rec.Segment7_Type,
992 bud_org_rec.Segment8_Type,
993 bud_org_rec.Segment9_Type,
994 bud_org_rec.Segment10_Type,
995 bud_org_rec.Segment11_Type,
996 bud_org_rec.Segment12_Type,
997 bud_org_rec.Segment13_Type,
998 bud_org_rec.Segment14_Type,
999 bud_org_rec.Segment15_Type,
1000 bud_org_rec.Segment16_Type,
1001 bud_org_rec.Segment17_Type,
1002 bud_org_rec.Segment18_Type,
1003 bud_org_rec.Segment19_Type,
1004 bud_org_rec.Segment20_Type,
1005 bud_org_rec.Segment21_Type,
1006 bud_org_rec.Segment22_Type,
1007 bud_org_rec.Segment23_Type,
1008 bud_org_rec.Segment24_Type,
1009 bud_org_rec.Segment25_Type,
1010 bud_org_rec.Segment26_Type,
1011 bud_org_rec.Segment27_Type,
1012 bud_org_rec.Segment28_Type,
1013 bud_org_rec.Segment29_Type,
1014 bud_org_rec.Segment30_Type,
1015 bud_org_rec.Attribute1,
1016 bud_org_rec.Attribute2,
1017 bud_org_rec.Attribute3,
1018 bud_org_rec.Attribute4,
1019 bud_org_rec.Attribute5,
1020 bud_org_rec.Attribute6,
1021 bud_org_rec.Attribute7,
1022 bud_org_rec.Attribute8,
1023 bud_org_rec.Attribute9,
1024 bud_org_rec.Attribute10,
1025 bud_org_rec.Context,
1026 bud_org_rec.Security_Flag);
1027
1028
1029 /********** Open Ranges cursor to insert ranges */
1030
1031 FOR Ranges in range_cur
1032 LOOP
1033
1034 select chart_of_accounts_id into v_coa_id
1035 from gl_ledgers where ledger_id = g_cbc_ledger_id;
1036
1037 select gl_budget_assignment_ranges_s.NEXTVAL into v_new_range_id
1038 from dual;
1039 BEGIN
1040 GL_BUD_ASSIGN_RANGE_PKG.Insert_Row(
1041 X_Rowid => v_range_id,
1042 X_Budget_Entity_Id => p_budget_entity_id,
1043 X_Ledger_Id => g_cbc_ledger_id,
1044 X_Currency_Code => RANGES.Currency_Code,
1045 X_Entry_Code => RANGES.Entry_Code,
1046 X_Range_Id => v_new_range_id,
1047 X_Status => 'A', --RANGES.Status,
1048 X_Last_Update_Date => RANGES.Last_Update_Date,
1049 X_Created_By => RANGES.Created_By,
1050 X_Creation_Date => RANGES.Creation_Date,
1051 X_Last_Updated_By => RANGES.Last_Updated_By,
1052 X_Last_Update_Login => RANGES.Last_Update_Login,
1053 X_Sequence_Number => RANGES.Sequence_Number,
1054 X_Segment1_Low => RANGES.Segment1_Low,
1055 X_Segment1_High => RANGES.Segment1_High,
1056 X_Segment2_Low => RANGES.Segment2_Low,
1057 X_Segment2_High => RANGES.Segment2_High,
1058 X_Segment3_Low => RANGES.Segment3_Low,
1059 X_Segment3_High => RANGES.Segment3_High,
1060 X_Segment4_Low => RANGES.Segment4_Low,
1061 X_Segment4_High => RANGES.Segment4_High,
1062 X_Segment5_Low => RANGES.Segment5_Low,
1063 X_Segment5_High => RANGES.Segment5_High,
1064 X_Segment6_Low => RANGES.Segment6_Low,
1065 X_Segment6_High => RANGES.Segment6_High,
1066 X_Segment7_Low => RANGES.Segment7_Low,
1067 X_Segment7_High => RANGES.Segment7_High,
1068 X_Segment8_Low => RANGES.Segment8_Low,
1069 X_Segment8_High => RANGES.Segment8_High,
1070 X_Segment9_Low => RANGES.Segment9_Low,
1071 X_Segment9_High => RANGES.Segment9_High,
1072 X_Segment10_Low => RANGES.Segment10_Low,
1073 X_Segment10_High => RANGES.Segment10_High,
1074 X_Segment11_Low => RANGES.Segment11_Low,
1075 X_Segment11_High => RANGES.Segment11_High,
1076 X_Segment12_Low => RANGES.Segment12_Low,
1077 X_Segment12_High => RANGES.Segment12_High,
1078 X_Segment13_Low => RANGES.Segment13_Low,
1079 X_Segment13_High => RANGES.Segment13_High,
1080 X_Segment14_Low => RANGES.Segment14_Low,
1081 X_Segment14_High => RANGES.Segment14_High,
1082 X_Segment15_Low => RANGES.Segment15_Low,
1083 X_Segment15_High => RANGES.Segment15_High,
1084 X_Segment16_Low => RANGES.Segment16_Low,
1085 X_Segment16_High => RANGES.Segment16_High,
1086 X_Segment17_Low => RANGES.Segment17_Low,
1087 X_Segment17_High => RANGES.Segment17_High,
1088 X_Segment18_Low => RANGES.Segment18_Low,
1089 X_Segment18_High => RANGES.Segment18_High,
1090 X_Segment19_Low => RANGES.Segment19_Low,
1091 X_Segment19_High => RANGES.Segment19_High,
1092 X_Segment20_Low => RANGES.Segment20_Low,
1093 X_Segment20_High => RANGES.Segment20_High,
1094 X_Segment21_Low => RANGES.Segment21_Low,
1095 X_Segment21_High => RANGES.Segment21_High,
1096 X_Segment22_Low => RANGES.Segment22_Low,
1097 X_Segment22_High => RANGES.Segment22_High,
1098 X_Segment23_Low => RANGES.Segment23_Low,
1099 X_Segment23_High => RANGES.Segment23_High,
1100 X_Segment24_Low => RANGES.Segment24_Low,
1101 X_Segment24_High => RANGES.Segment24_High,
1102 X_Segment25_Low => RANGES.Segment25_Low,
1103 X_Segment25_High => RANGES.Segment25_High,
1104 X_Segment26_Low => RANGES.Segment26_Low,
1105 X_Segment26_High => RANGES.Segment26_High,
1106 X_Segment27_Low => RANGES.Segment27_Low,
1107 X_Segment27_High => RANGES.Segment27_High,
1108 X_Segment28_Low => RANGES.Segment28_Low,
1109 X_Segment28_High => RANGES.Segment28_High,
1110 X_Segment29_Low => RANGES.Segment29_Low,
1111 X_Segment29_High => RANGES.Segment29_High,
1112 X_Segment30_Low => RANGES.Segment30_Low,
1113 X_Segment30_High => RANGES.Segment30_High,
1114 X_Context => RANGES.Context,
1115 X_Attribute1 => RANGES.Attribute1,
1116 X_Attribute2 => RANGES.Attribute2,
1117 X_Attribute3 => RANGES.Attribute3,
1118 X_Attribute4 => RANGES.Attribute4,
1119 X_Attribute5 => RANGES.Attribute5,
1120 X_Attribute6 => RANGES.Attribute6,
1121 X_Attribute7 => RANGES.Attribute7,
1122 X_Attribute8 => RANGES.Attribute8,
1123 X_Attribute9 => RANGES.Attribute9,
1124 X_Attribute10 => RANGES.Attribute10,
1125 X_Attribute11 => RANGES.Attribute11,
1126 X_Attribute12 => RANGES.Attribute12,
1127 X_Attribute13 => RANGES.Attribute13,
1128 X_Attribute14 => RANGES.Attribute14,
1129 X_Attribute15 => RANGES.Attribute15,
1130 X_Chart_Of_Accounts_Id => v_coa_id);
1131
1132 /********** Insert in to GL_BUDORG_BC_OPTIONS */
1133 FOR budctrl_rec in BC_CUR(RANGES.range_id)
1134 LOOP
1135
1136 BEGIN
1137 SELECT CBC_OVERRIDE into v_cbc_override
1138 FROM IGC_CBC_BA_RANGES
1139 WHERE CBC_RANGE_ID= RANGES.range_id
1140 AND SET_OF_BOOKS_ID = g_sob_id
1141 AND BUDGET_ENTITY_ID = v_budget_entity_id;
1142 EXCEPTIOn
1143 when No_data_found then
1144 v_cbc_override := budctrl_rec.Funds_Check_Level_Code;
1145 END;
1146
1147 GL_BUDORG_BC_OPTIONS_PKG.Insert_Row(
1148 X_Rowid => v_range_id,
1149 X_Range_Id => v_new_range_id,
1150 X_Last_Update_Date => budctrl_rec.Last_Update_Date,
1151 X_Created_By => budctrl_rec.Created_By,
1152 X_Creation_Date => budctrl_rec.Creation_Date,
1153 X_Funds_Check_Level_Code=> v_cbc_override ,
1154 X_Last_Updated_By => budctrl_rec.Last_Updated_By,
1155 X_Last_Update_Login => budctrl_rec.Last_Update_Login,
1156 X_Amount_Type => budctrl_rec.Amount_Type,
1157 X_Boundary_Code => budctrl_rec.Boundary_Code,
1158 X_Funding_Budget_Version_Id=> p_budget_version_id);
1159 END LOOP;
1160 EXCEPTION
1161 WHEN OTHERS THEN
1162 NULL;
1163 END;
1164 END LOOP; --Ranges cursor
1165
1166 END ; /* End of rowcount =0 */
1167 add_bud_entity_map(bud_org_rec.name,X_name);
1168 END IF;
1169 EXIT WHEN budorg_cur%NOTFOUND;
1170 END LOOP; --BUDORG_CUR
1171 CLOSE budorg_cur;
1172 EXCEPTION
1173 WHEN OTHERS THEN
1174 IF budorg_cur%ISOPEN THEN
1175 CLOSE budorg_cur;
1176 END IF;
1177 IF (g_debug_mode = 'Y') THEN
1178 Put_Debug_Msg(l_full_path, 'while migrating budget org info' || SQLERRM);
1179 END IF;
1180 /*Added by mbremkum*/
1181 APP_EXCEPTION.Raise_Exception;
1182 End GL_Budget_Org;
1183
1184
1185 PROCEDURE submit_open_year(P_sec_ledger_id IN Number,
1186 Bud_Version_Id IN NUMBER,
1187 p_latest_open_year IN gl_budgets_v.latest_opened_year%type,
1188 p_fiscal_year IN NUMBER)
1189 IS
1190 req_id number;
1191 v_access_set_id number;
1192 User_Exception Exception;
1193 l_request_status NUMBER;
1194 BEGIN
1195 v_access_set_id := fnd_profile.value('GL_ACCESS_SET_ID');
1196 If nvl(p_latest_open_year,0) < p_fiscal_year then
1197 req_id
1198 := fnd_request.submit_request(
1199 'SQLGL',
1200 'GLBOYR',
1201 '',
1202 '',
1203 FALSE,
1204 v_access_set_id,
1205 to_char(p_sec_ledger_id),
1206 to_char(Bud_Version_Id),
1207 chr(0),
1208 '', '', '', '', '', '', '', '', '', '',
1209 '', '', '', '', '', '', '', '', '', '',
1210 '', '', '', '', '', '', '', '', '', '',
1211 '', '', '', '', '', '', '', '', '', '',
1212 '', '', '', '', '', '', '', '', '', '',
1213 '', '', '', '', '', '', '', '', '', '',
1214 '', '', '', '', '', '', '', '', '', '',
1215 '', '', '', '', '', '', '', '', '', '',
1216 '', '', '', '', '', '', '', '', '', '',
1217 '', '', '', '', '', '');
1218 commit;
1219 -- Verify that the concurrent request was launched
1220 -- successfully.
1221 IF (req_id = 0) THEN
1222 --fnd_message.retrieve;
1223 -- fnd_message.error;
1224 Raise User_Exception;
1225 ELSE
1226 COMMIT;
1227 l_request_status := check_request(req_id);
1228 END IF;
1229 END IF;
1230 EXCEPTION
1231 When User_exception then
1232 Raise_application_error(20010,'Open Budget Year concurrent request is failed');
1233 END submit_open_year;
1234
1235
1236 PROCEDURE submit_assign_ranges(P_sec_ledger_id IN Number,
1237 p_bud_entity_id IN Number)
1238 IS
1239 v_req_id number;
1240 User_Exception Exception;
1241 l_request_status NUMBER;
1242 BEGIN
1243 IF p_bud_entity_id IS NULL THEN
1244 return;
1245 END IF;
1246 v_req_id
1247 := fnd_request.submit_request(
1248 'SQLGL',
1249 'GLBAAR',
1250 '',
1251 '',
1252 FALSE,
1253 to_char(P_sec_ledger_id),
1254 to_char(p_bud_entity_id),
1255 chr(0),
1256 '', '', '', '', '', '', '', '', '', '',
1257 '', '', '', '', '', '', '', '', '', '',
1258 '', '', '', '', '', '', '', '', '', '',
1259 '', '', '', '', '', '', '', '', '', '',
1260 '', '', '', '', '', '', '', '', '', '',
1261 '', '', '', '', '', '', '', '', '', '',
1262 '', '', '', '', '', '', '', '', '', '',
1263 '', '', '', '', '', '', '', '', '', '',
1264 '', '', '', '', '', '', '', '', '', '',
1265 '', '', '', '', '', '', '');
1266 commit;
1267 -- Verify that the concurrent request was launched
1268 -- successfully.
1269 IF (v_req_id = 0) THEN
1270 --fnd_message.retrieve;
1271 --fnd_message.error;
1272 Raise User_Exception;
1273 ELSE
1274 COMMIT;
1275 l_request_status := check_request(v_req_id);
1276 END IF;
1277 EXCEPTION
1278 When User_exception then
1279 Raise_application_error(20020,'Assign Budget Account Ranges concurrent request has failed');
1280 END submit_assign_ranges;
1281
1282
1283 /* Procedure to migrate summary templates data for all templates in a primary ledger */
1284
1285 PROCEDURE migrate_sum_templates IS
1286
1287 /* Cursor to get all template ids, for which setup is done in CBC */
1288 Cursor template_cur is
1289 select template_id, CBC_OVERRIDE
1290 from IGC_CBC_SUMMARY_TEMPLATES
1291 where set_of_books_id = g_sob_id
1292 and nvl(MIG_RESULT_CODE,'F') <> 'T';
1293
1294 gl_temp_rec GL_SUMMARY_TEMPLATES%rowtype;
1295
1296 /* cursor to get all budget records for which budgetroy control is set up for each template */
1297 Cursor bcoptions_cur(c_template_id IN NUMBER) is
1298 Select * from GL_SUMMARY_BC_OPTIONS
1299 where template_id = c_template_id;
1300
1301 v_template_id number;
1302 v_chart_of_accounts_id gl_ledgers.chart_of_accounts_id%type;
1303 user_exception Exception;
1304 v_old_budget_name gl_budgets_v.budget_name%type;
1305 v_new_bud_ver_id gl_budgets_v.budget_version_id%type;
1306 req_id number;
1307 v_row_id varchar2(50) := NULL;
1308 l_full_path VARCHAR2(255);
1309 l_request_status NUMBER;
1310 BEGIN
1311 l_full_path := g_path || 'migrate_sum_templates';
1312
1313 select chart_of_accounts_id into v_chart_of_accounts_id from gl_ledgers where ledger_id = g_cbc_ledger_id;
1314
1315 /* Inserting template information in GL_SUMMARY_TEMPLATES for which setup is made in CBC */
1316
1317 For i in template_cur
1318 loop
1319 v_template_id := GL_SUMMARY_TEMPLATES_PKG.get_unique_id;
1320 select * into gl_temp_rec from GL_SUMMARY_TEMPLATES where template_id = i.template_id;
1321 INSERT INTO GL_SUMMARY_TEMPLATES(
1322 template_id,
1323 ledger_id,
1324 status,
1325 last_update_date,
1326 last_updated_by,
1327 template_name,
1328 concatenated_description,
1329 account_category_code,
1330 max_code_combination_id,
1331 start_actuals_period_name,
1332 created_by,
1333 creation_date,
1334 last_update_login,
1335 segment1_type,
1336 segment2_type,
1337 segment3_type,
1338 segment4_type,
1339 segment5_type,
1340 segment6_type,
1341 segment7_type,
1342 segment8_type,
1343 segment9_type,
1344 segment10_type,
1345 segment11_type,
1346 segment12_type,
1347 segment13_type,
1348 segment14_type,
1349 segment15_type,
1350 segment16_type,
1351 segment17_type,
1352 segment18_type,
1353 segment19_type,
1354 segment20_type,
1355 segment21_type,
1356 segment22_type,
1357 segment23_type,
1358 segment24_type,
1359 segment25_type,
1360 segment26_type,
1361 segment27_type,
1362 segment28_type,
1363 segment29_type,
1364 segment30_type,
1365 description,
1366 attribute1,
1367 attribute2,
1368 attribute3,
1369 attribute4,
1370 attribute5,
1371 attribute6,
1372 attribute7,
1373 attribute8,
1374 context)
1375 VALUES (
1376 V_Template_Id,
1377 g_cbc_Ledger_id,
1378 'A',
1379 gl_temp_rec.Last_Update_Date,
1380 gl_temp_rec.Last_Updated_By,
1381 gl_temp_rec.Template_Name,
1382 gl_temp_rec.Concatenated_Description,
1383 gl_temp_rec.Account_Category_Code,
1384 gl_temp_rec.MAX_Code_Combination_Id,
1385 gl_temp_rec.Start_Actuals_Period_Name,
1386 gl_temp_rec.Created_By,
1387 gl_temp_rec.Creation_Date,
1388 gl_temp_rec.Last_Update_Login,
1389 gl_temp_rec.Segment1_Type,
1390 gl_temp_rec.Segment2_Type,
1391 gl_temp_rec.Segment3_Type,
1392 gl_temp_rec.Segment4_Type,
1393 gl_temp_rec.Segment5_Type,
1394 gl_temp_rec.Segment6_Type,
1395 gl_temp_rec.Segment7_Type,
1396 gl_temp_rec.Segment8_Type,
1397 gl_temp_rec.Segment9_Type,
1398 gl_temp_rec.Segment10_Type,
1399 gl_temp_rec.Segment11_Type,
1400 gl_temp_rec.Segment12_Type,
1401 gl_temp_rec.Segment13_Type,
1402 gl_temp_rec.Segment14_Type,
1403 gl_temp_rec.Segment15_Type,
1404 gl_temp_rec.Segment16_Type,
1405 gl_temp_rec.Segment17_Type,
1406 gl_temp_rec.Segment18_Type,
1407 gl_temp_rec.Segment19_Type,
1408 gl_temp_rec.Segment20_Type,
1409 gl_temp_rec.Segment21_Type,
1410 gl_temp_rec.Segment22_Type,
1411 gl_temp_rec.Segment23_Type,
1412 gl_temp_rec.Segment24_Type,
1413 gl_temp_rec.Segment25_Type,
1414 gl_temp_rec.Segment26_Type,
1415 gl_temp_rec.Segment27_Type,
1416 gl_temp_rec.Segment28_Type,
1417 gl_temp_rec.Segment29_Type,
1418 gl_temp_rec.Segment30_Type,
1419 gl_temp_rec.Description,
1420 gl_temp_rec.Attribute1,
1421 gl_temp_rec.Attribute2,
1422 gl_temp_rec.Attribute3,
1423 gl_temp_rec.Attribute4,
1424 gl_temp_rec.Attribute5,
1425 gl_temp_rec.Attribute6,
1426 gl_temp_rec.Attribute7,
1427 gl_temp_rec.Attribute8,
1428 gl_temp_rec.Context);
1429
1430 /* Inserting budgetary control options into GL_SUMMARY_BC_OPTIONS */
1431 FOR BUDCTRL_OPTIONS IN bcoptions_cur(i.template_id)
1432 LOOP
1433
1434 select budget_name into v_old_budget_name
1435 from gl_budgets_v where budget_version_id = BUDCTRL_OPTIONS.funding_budget_version_id;
1436
1437 select budget_version_id into v_new_bud_ver_id
1438 from gl_budgets_v where description like '%R12_MIG_'||v_old_budget_name;
1439
1440 GL_SUMMARY_BC_OPTIONS_PKG.Insert_Row(
1441 X_Rowid => v_row_id,
1442 X_Funds_Check_Level_Code => i.CBC_OVERRIDE ,
1443 X_Dr_Cr_Code => BUDCTRL_OPTIONS.dr_cr_code,
1444 X_Amount_Type => BUDCTRL_OPTIONS.amount_type,
1445 X_Boundary_Code => BUDCTRL_OPTIONS.boundary_code,
1446 X_Template_Id => v_template_id ,
1447 X_Last_Update_Date => BUDCTRL_OPTIONS.last_update_date,
1448 X_Last_Updated_By => BUDCTRL_OPTIONS.last_updated_by,
1449 X_Created_By => BUDCTRL_OPTIONS.created_by,
1450 X_Creation_Date => BUDCTRL_OPTIONS.creation_date,
1451 X_Last_Update_Login => BUDCTRL_OPTIONS.last_update_login,
1452 X_Funding_Budget_Version_Id => v_new_bud_ver_id
1453 );
1454 END LOOP; --For all records of GL_SUMMARY_BC_OPTIONS for each template.
1455
1456 update igc_cbc_summary_templates set MIG_RESULT_CODE = 'T',
1457 MIG_REQUEST_ID = fnd_global.conc_request_id
1458 WHERE template_id = i.template_id;
1459
1460 /* Submitting "Add/Delete Summary Accounts" concurrent program */
1461
1462 BEGIN
1463 req_id := fnd_request.submit_request(
1464 'SQLGL',
1465 'GLSTPM',
1466 '',
1467 '',
1468 FALSE,
1469 'A',
1470 to_char(V_Template_Id),
1471 to_char(g_cbc_ledger_id),
1472 to_char(v_chart_of_accounts_id),
1473 chr(0),
1474 '', '', '', '', '', '', '', '', '', '',
1475 '', '', '', '', '', '', '', '', '', '',
1476 '', '', '', '', '', '', '', '', '', '',
1477 '', '', '', '', '', '', '', '', '', '',
1478 '', '', '', '', '', '', '', '', '', '',
1479 '', '', '', '', '', '', '', '', '', '',
1480 '', '', '', '', '', '', '', '', '', '',
1481 '', '', '', '', '', '', '', '', '', '',
1482 '', '', '', '', '', '', '', '', '', '',
1483 '', '', '', '', '');
1484
1485 -- Verify that the concurrent request was launched
1486 -- successfully.
1487 IF (req_id = 0) THEN
1488 --fnd_message.retrieve;
1489 --fnd_message.error;
1490 Raise user_exception;
1491 ELSE
1492 COMMIT;
1493 l_request_status := check_request(req_id);
1494 END IF;
1495 EXCEPTION WHEN user_exception THEN
1496 Raise_application_error(20030,'Add/Delete Summary Accounts concurrent request has failed');
1497 END;
1498
1499 END LOOP; --FOr all template_id's in IGC_CBC_TEMPLATES table.
1500 EXCEPTION
1501 WHEN OTHERS THEN
1502 IF (g_debug_mode = 'Y') THEN
1503 Put_Debug_Msg(l_full_path, SQLERRM);
1504 END IF;
1505 /*Added by mbremkum*/
1506 APP_EXCEPTION.Raise_Exception;
1507 END migrate_sum_templates;
1508
1509
1510 PROCEDURE migrate_cbc_bud_jounals(p_fiscal_year IN NUMBER)
1511 IS
1512 l_user_id NUMBER := FND_GLOBAL.user_id;
1513
1514 l_gl_data_access_set fnd_profile_option_values.profile_option_value%type;
1515
1516 l_request_id_current NUMBER := fnd_global.conc_request_id;
1517 l_request_id_bud NUMBER;
1518 l_request_id_comp NUMBER;
1519
1520 bln_request_status BOOLEAN;
1521
1522 l_req_comp_phase VARCHAR2(50);
1523 l_req_comp_status VARCHAR2(50);
1524 l_req_comp_dev_phase VARCHAR2(50);
1525 l_req_comp_dev_status VARCHAR2(50);
1526 l_req_comp_mesg VARCHAR2(2000);
1527
1528 bln_req_comp BOOLEAN := TRUE;
1529
1530 l_full_path VARCHAR2(255);
1531 bln_missing_bud BOOLEAN := false;
1532
1533 CURSOR c_cbc_source is
1534 SELECT Distinct
1535 je_source
1536 FROM IGC_CBC_JE_LINES cbl
1537 WHERE set_of_books_id = g_sob_id
1538 AND cbl.period_year = p_fiscal_year
1539 AND mig_request_id is null
1540 AND actual_flag = 'B'
1541 AND detail_summary_code = 'D';
1542
1543 Type t_tbl_cbc_source IS TABLE OF c_cbc_source%ROWTYPE INDEX BY BINARY_INTEGER;
1544
1545 l_tbl_cbc_source t_tbl_cbc_source;
1546
1547 l_request_status NUMBER;
1548
1549 CURSOR c_bud_journals IS
1550 SELECT
1551 'NEW' status
1552 ,g_cbc_ledger_id ledger_id
1553 ,igc.je_source user_je_source_name
1554 ,igc.je_category user_je_category_name
1555 ,igc.effective_date accounting_date
1556 ,igc.currency_code currency_code
1557 ,sysdate date_created
1558 ,l_user_id created_by
1559 ,'B' actual_flag
1560 ,igc.budget_version_id old_budget_version_id
1561 ,BV.BUDGET_VERSION_ID new_budget_version_id
1562 ,decode(igc.entered_dr,0,null,entered_dr) entered_dr
1563 ,decode(igc.entered_cr,0,null,entered_cr) entered_cr
1564 ,igc.period_name period_name
1565 ,igc.code_combination_id code_combination_id
1566 ,'MIG-'||cbc_je_batch_id reference1
1567 ,'R11i MIGRATION - '||cbc_je_batch_id||' - '||cbc_je_line_num reference5
1568 ,'R11i MIGRATION - '||cbc_je_batch_id||' - '||cbc_je_line_num reference6
1569 ,igc.cbc_je_batch_id reference21
1570 ,igc.cbc_je_line_num reference22
1571 FROM igc_cbc_je_lines igc,
1572 gl_budget_assignments asg,
1573 GL_BUDORG_BC_OPTIONS boc,
1574 GL_BUDGET_VERSIONS BV ,
1575 gl_budgets bud,
1576 gl_period_statuses per_f,
1577 gl_period_statuses per_s
1578 WHERE asg.range_id = boc.range_id
1579 AND BV.BUDGET_VERSION_ID = BOC.FUNDING_BUDGET_VERSION_ID
1580 AND bud.budget_name = BV.budget_name
1581 AND asg.code_combination_id = igc.code_combination_id
1582 AND per_s.ledger_id = bud.ledger_id
1583 AND per_f.ledger_id = bud.ledger_id
1584 AND per_s.application_id = 101
1585 AND per_f.application_id = 101
1586 AND per_s.period_name = bud.first_valid_period_name
1587 AND per_f.period_name = bud.last_valid_period_name
1588 AND igc.effective_date between per_s.start_date and per_f.end_date
1589 AND bud.ledger_id = g_cbc_ledger_id
1590 AND igc.set_of_books_id = g_sob_id
1591 AND igc.period_year = p_fiscal_year
1592 AND igc.mig_result_code IS NULL
1593 AND igc.actual_flag = 'B'
1594 AND igc.detail_summary_code = 'D'
1595 AND igc.mig_request_id is NULL
1596 AND igc.budget_version_id IS NOT NULL;
1597
1598 Type t_tbl_bud_journals IS TABLE OF c_bud_journals%ROWTYPE INDEX BY BINARY_INTEGER;
1599
1600 l_tbl_bud_journals t_tbl_bud_journals;
1601 l_error VARCHAR2(2000);
1602 l_dummy VARCHAR2(1);
1603
1604 BEGIN
1605
1606 l_full_path := g_path || 'migrate_cbc_bud_jounals';
1607
1608 l_gl_data_access_set := fnd_profile.value('GL_ACCESS_SET_ID');
1609
1610 -- First Check all budget version exists in Secondary ledger
1611
1612 BEGIN
1613 SELECT '1'
1614 INTO l_dummy
1615 FROM DUAL
1616 WHERE EXISTS
1617 (SELECT 1
1618 FROM igc_cbc_je_lines igc
1619 where budget_version_id is NOT null
1620 AND igc.actual_flag = 'B'
1621 AND igc.DETAIL_SUMMARY_CODE = 'D'
1622 AND igc.period_year = p_fiscal_year
1623 AND set_of_books_id = g_sob_id
1624 AND NOT EXISTS
1625 ( SELECT 1
1626 FROM gl_budget_assignments asg,
1627 GL_BUDORG_BC_OPTIONS boc,
1628 GL_BUDGET_VERSIONS BV ,
1629 gl_budgets bud,
1630 gl_period_statuses per_f,
1631 gl_period_statuses per_s
1632 WHERE asg.range_id = boc.range_id
1633 AND BV.BUDGET_VERSION_ID = BOC.FUNDING_BUDGET_VERSION_ID
1634 AND bud.budget_name = BV.budget_name
1635 AND asg.code_combination_id = igc.code_combination_id
1636 AND per_s.ledger_id = bud.ledger_id
1637 AND per_f.ledger_id = bud.ledger_id
1638 AND per_s.application_id = 101
1639 AND per_f.application_id = 101
1640 AND per_s.period_name = bud.first_valid_period_name
1641 AND per_f.period_name = bud.last_valid_period_name
1642 AND igc.effective_date between per_s.start_date and per_f.end_date
1643 AND bud.ledger_id = g_cbc_ledger_id
1644 )
1645 );
1646 IF l_dummy = '1' THEN
1647 FND_MESSAGE.set_name('IGC','IGC_CBC_BUD_DATA_MISS');
1648 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME',g_conc_pr_user);
1649 l_error := FND_MESSAGE.get;
1650 add_error (l_error);
1651 return;
1652 END IF;
1653 EXCEPTION
1654 WHEN NO_DATA_FOUND THEN
1655 NULL;
1656 END;
1657
1658 IF (g_debug_mode = 'Y') THEN
1659 Put_Debug_Msg(l_full_path, 'Checked budget versions exists in CBC ledger');
1660 END IF;
1661
1662 OPEN c_bud_journals;
1663 LOOP
1664 FETCH c_bud_journals BULK COLLECT INTO l_tbl_bud_journals;
1665 EXIT WHEN c_bud_journals%NOTFOUND;
1666 END LOOP;
1667
1668 IF (g_debug_mode = 'Y') THEN
1669 Put_Debug_Msg(l_full_path, 'Number of budgets to migrate '||l_tbl_bud_journals.count);
1670 END IF;
1671
1672 FOR i_ind in 1..l_tbl_bud_journals.COUNT
1673 LOOP
1674 INSERT INTO GL_INTERFACE
1675 (
1676 status
1677 ,ledger_id
1678 ,user_je_source_name
1679 ,user_je_category_name
1680 ,accounting_date
1681 ,currency_code
1682 ,date_created
1683 ,created_by
1684 ,actual_flag
1685 ,budget_version_id
1686 ,entered_dr
1687 ,entered_cr
1688 ,period_name
1689 ,code_combination_id
1690 ,reference1
1691 ,reference5
1692 ,reference6
1693 ,reference21
1694 ,reference22
1695 )
1696 VALUES
1697 (
1698 l_tbl_bud_journals(i_ind).status
1699 ,l_tbl_bud_journals(i_ind).ledger_id
1700 ,l_tbl_bud_journals(i_ind).user_je_source_name
1701 ,l_tbl_bud_journals(i_ind).user_je_category_name
1702 ,l_tbl_bud_journals(i_ind).accounting_date
1703 ,l_tbl_bud_journals(i_ind).currency_code
1704 ,l_tbl_bud_journals(i_ind).date_created
1705 ,l_tbl_bud_journals(i_ind).created_by
1706 ,l_tbl_bud_journals(i_ind).actual_flag
1707 ,l_tbl_bud_journals(i_ind).new_budget_version_id
1708 ,l_tbl_bud_journals(i_ind).entered_dr
1709 ,l_tbl_bud_journals(i_ind).entered_cr
1710 ,l_tbl_bud_journals(i_ind).period_name
1711 ,l_tbl_bud_journals(i_ind).code_combination_id
1712 ,l_tbl_bud_journals(i_ind).reference1
1713 ,l_tbl_bud_journals(i_ind).reference5
1714 ,l_tbl_bud_journals(i_ind).reference6
1715 ,l_tbl_bud_journals(i_ind).reference21
1716 ,l_tbl_bud_journals(i_ind).reference22
1717 );
1718 END LOOP;
1719
1720 IF (g_debug_mode = 'Y') THEN
1721 Put_Debug_Msg(l_full_path, 'Inserted records into GL_INTERFACE table');
1722 END IF;
1723
1724 /* Get all journal source */
1725 OPEN c_cbc_source;
1726 LOOP
1727 FETCH c_cbc_source BULK COLLECT INTO l_tbl_cbc_source;
1728 EXIT WHEN c_cbc_source%NOTFOUND;
1729 END LOOP;
1730 CLOSE c_cbc_source;
1731
1732 COMMIT;
1733
1734 FOR j in 1..l_tbl_cbc_source.COUNT
1735 LOOP
1736 IF (g_debug_mode = 'Y') THEN
1737 Put_Debug_Msg(l_full_path, 'Submitting Import Journal request for '||l_tbl_cbc_source(j).je_source);
1738 END IF;
1739 l_request_id_bud := fnd_request.submit_request
1740 (application => 'SQLGL',
1741 program => 'GLLEZLSRS',
1742 description => NULL,
1743 start_time => NULL,
1744 sub_request => FALSE,
1745 argument1 => l_gl_data_access_set,
1746 argument2 => l_tbl_cbc_source(j).je_source,
1747 argument3 => g_cbc_ledger_id,
1748 argument4 => NULL,
1749 argument5 => 'N',
1750 argument6 => 'N',
1751 argument7 => 'N',
1752 argument8 => '',argument9 => '',argument10 => '',
1753 argument11 => '',argument12 => '',argument13 => '',argument14 => '',argument15 => '',
1754 argument16 => '',argument17 => '',argument18 => '',argument19 => '',argument20 => '',
1755 argument21 => '',argument22 => '',argument23 => '',argument24 => '',argument25 => '',
1756 argument26 => '',argument27 => '',argument28 => '',argument29 => '',argument30 => '',
1757 argument31 => '',argument32 => '',argument33 => '',argument34 => '',argument35 => '',
1758 argument36 => '',argument37 => '',argument38 => '',argument39 => '',argument40 => '',
1759 argument41 => '',argument42 => '',argument43 => '',argument44 => '',argument45 => '',
1760 argument46 => '',argument47 => '',argument48 => '',argument49 => '',argument50 => '',
1761 argument51 => '',argument52 => '',argument53 => '',argument54 => '',argument55 => '',
1762 argument56 => '',argument57 => '',argument58 => '',argument59 => '',argument60 => '',
1763 argument61 => '',argument62 => '',argument63 => '',argument64 => '',argument65 => '',
1764 argument66 => '',argument67 => '',argument68 => '',argument69 => '',argument70 => '',
1765 argument71 => '',argument72 => '',argument73 => '',argument74 => '',argument75 => '',
1766 argument76 => '',argument77 => '',argument78 => '',argument79 => '',argument80 => '',
1767 argument81 => '',argument82 => '',argument83 => '',argument84 => '',argument85 => '',
1768 argument86 => '',argument87 => '',argument88 => '',argument89 => '',argument90 => '',
1769 argument91 => '',argument92 => '',argument93 => '',argument94 => '',argument95 => '',
1770 argument96 => '',argument97 => '',argument98 => '',argument99 => '',argument100 => ''
1771 );
1772 IF (g_debug_mode = 'Y') THEN
1773 Put_Debug_Msg(l_full_path, 'Submitted Import Journal request for '||l_tbl_cbc_source(j).je_source||' request Id '||l_request_id_bud);
1774 END IF;
1775
1776 IF l_request_id_bud <= 0 THEN
1777 bln_req_comp := FALSE;
1778 ELSE
1779 COMMIT;
1780 l_request_status := check_request(l_request_id_bud);
1781 IF (g_debug_mode = 'Y') THEN
1782 Put_Debug_Msg(l_full_path, 'Updating igc_cbc_je_lines.. start');
1783 END IF;
1784 UPDATE igc_cbc_je_lines cbc
1785 SET mig_result_code = 'P00'
1786 ,mig_request_id = l_request_id_current
1787 WHERE set_of_books_id = g_sob_id
1788 AND period_year = p_fiscal_year
1789 AND mig_result_code IS NULL
1790 AND actual_flag = 'B'
1791 AND detail_summary_code = 'D'
1792 AND mig_request_id is NULL
1793 AND budget_version_id IS NOT NULL
1794 AND je_source = l_tbl_cbc_source(j).je_source
1795 AND EXISTS
1796 (SELECT 1
1797 FROM gl_je_lines l, gl_je_headers h, gl_je_batches b
1798 WHERE h.je_batch_id = b.je_batch_id
1799 AND h.je_header_id = l.je_header_id
1800 AND h.je_source = l_tbl_cbc_source(j).je_source
1801 AND h.ledger_id = g_cbc_ledger_id
1802 AND l.reference_1 = to_char(cbc.cbc_je_batch_id)
1803 AND l.reference_2 = to_char(cbc.cbc_je_line_num)
1804 );
1805
1806 IF (g_debug_mode = 'Y') THEN
1807 Put_Debug_Msg(l_full_path, 'Updating igc_cbc_je_lines.. complete');
1808 END IF;
1809
1810 COMMIT;
1811 END IF;
1812 END LOOP;
1813
1814 END migrate_cbc_bud_jounals;
1815
1816
1817 FUNCTION get_cbc_budget_name(p_pri_budget_name IN VARCHAR2) RETURN VARCHAR2 IS
1818 l_cbc_budget_name GL_BUDGETS.BUDGET_NAME%TYPE;
1819 BEGIN
1820 SELECT budget_name
1821 INTO l_cbc_budget_name
1822 FROM gl_budgets
1823 WHERE description like '%R12_MIG_'||p_pri_budget_name;
1824 RETURN l_cbc_budget_name;
1825 END;
1826
1827 /* End of changes for Budgets Migration */
1828
1829 PROCEDURE add_error (
1830 p_error_mesg IN VARCHAR2
1831 )IS
1832 l_tbl_count NUMBER;
1833 BEGIN
1834 l_tbl_count := g_tbl_basic_errors.count;
1835 g_tbl_basic_errors(l_tbl_count+1).error_mesg := p_error_mesg;
1836 END add_error;
1837
1838 PROCEDURE add_warning (
1839 p_error_mesg IN VARCHAR2
1840 )IS
1841 l_tbl_count NUMBER;
1842 BEGIN
1843 l_tbl_count := g_tbl_warnings.count;
1844 g_tbl_warnings(l_tbl_count+1).error_mesg := p_error_mesg;
1845 END add_warning;
1846
1847 PROCEDURE set_final_out (
1848 p_final_out IN VARCHAR2
1849 )IS
1850 BEGIN
1851 IF g_final_out = 'ERROR' THEN
1852 return;
1853 END IF;
1854 IF p_final_out = 'ERROR' THEN
1855 g_final_out := p_final_out;
1856 ELSIF g_final_out = 'SUCCESS' THEN
1857 g_final_out := p_final_out;
1858 END IF;
1859 END set_final_out;
1860
1861 PROCEDURE add_bud_map (
1862 p_old_budget_name GL_BUDGETS.BUDGET_NAME%Type,
1863 p_new_budget_name GL_BUDGETS.BUDGET_NAME%Type
1864 ) IS
1865 l_tbl_count NUMBER;
1866 BEGIN
1867 l_tbl_count := g_tbl_bud_map.count;
1868 g_tbl_bud_map(l_tbl_count+1).old_budget_name := p_old_budget_name;
1869 g_tbl_bud_map(l_tbl_count+1).new_budget_name := p_new_budget_name;
1870 END add_bud_map;
1871
1872
1873 PROCEDURE add_bud_entity_map (
1874 p_old_bud_entity gl_budget_entities.NAME%Type,
1875 p_new_bud_entity gl_budget_entities.NAME%Type
1876 ) IS
1877 l_tbl_count NUMBER;
1878 BEGIN
1879 l_tbl_count := g_tbl_bud_entity_map.count;
1880 g_tbl_bud_entity_map(l_tbl_count+1).old_bud_entity := p_old_bud_entity;
1881 g_tbl_bud_entity_map(l_tbl_count+1).new_bud_entity := p_new_bud_entity;
1882 END add_bud_entity_map;
1883
1884 PROCEDURE validate_basic(p_org_id IN Number,
1885 p_fin_year IN NUMBER,
1886 p_balance_type IN VARCHAR2,
1887 x_return_code OUT NOCOPY NUMBER,
1888 x_msg_buf OUT NOCOPY VARCHAR2
1889 ) IS
1890 l_error VARCHAR2(2000);
1891 l_option_name VARCHAR2(80);
1892 l_dummy VARCHAR2(1);
1893 l_gl_data_access_set fnd_profile_option_values.profile_option_value%type;
1894 BEGIN
1895
1896 IF NOT igi_gen.is_req_installed('CBC') THEN
1897
1898 SELECT meaning
1899 INTO l_option_name
1900 FROM igi_lookups
1901 WHERE lookup_code = 'CBC'
1902 AND lookup_type = 'GCC_DESCRIPTION';
1903
1904 FND_MESSAGE.SET_NAME('IGI', 'IGI_GEN_PROD_NOT_INSTALLED');
1905 FND_MESSAGE.SET_TOKEN('OPTION_NAME', l_option_name);
1906 l_error := fnd_message.get;
1907 add_error (l_error);
1908 x_msg_buf := l_error;
1909 x_return_code := 2;
1910 return;
1911 END IF;
1912
1913 IGC_LEDGER_UTILS.get_cbc_ledger(p_primary_ledger_id => g_sob_id, p_cbc_ledger_id => g_cbc_ledger_id, p_cbc_ledger_Name => g_cbc_ledger_name);
1914 /*
1915 IF (g_debug_mode = 'Y') THEN
1916 Put_Debug_Msg(l_full_path, 'Secondary Ledger Name: ' || g_cbc_ledger_name);
1917 END IF;
1918 */
1919 IF (g_cbc_ledger_id <= 0) THEN
1920 FND_MESSAGE.set_name('IGC','IGC_NO_CBC_LEDGER');
1921 FND_MESSAGE.SET_TOKEN('LEDGER_NAME',g_sob_name);
1922 x_return_code := 2;
1923 l_error := FND_MESSAGE.get;
1924 add_error (l_error);
1925 x_msg_buf := l_error ;
1926 return;
1927 END IF;
1928 Declare
1929 l_tot_count NUMBER := 0;
1930 l_open_count NUMBER := 0;
1931 BEGIN
1932 SELECT sum(decode(closing_status,'O',1,0)),sum(1)
1933 INTO l_open_count,l_tot_count
1934 FROM gl_period_statuses
1935 WHERE application_id = 101
1936 AND period_year = p_fin_year
1937 AND ledger_id = g_cbc_ledger_id
1938 AND adjustment_period_flag = 'N';
1939 IF nvl(l_tot_count,0) = 0 OR (l_open_count <> l_tot_count) THEN
1940 FND_MESSAGE.set_name('IGC','IGC_CBC_MIG_CLOSE_PERIOD');
1941 FND_MESSAGE.SET_TOKEN('YEAR',p_fin_year);
1942 FND_MESSAGE.SET_TOKEN('LEDGER_NAME',g_cbc_ledger_name);
1943 l_error := FND_MESSAGE.get;
1944 add_error (l_error);
1945 x_msg_buf := l_error ;
1946 x_return_code := 2;
1947 return;
1948 END IF;
1949 EXCEPTION
1950 WHEN OTHERS THEN NULL;
1951 END;
1952
1953 l_gl_data_access_set := fnd_profile.value('GL_ACCESS_SET_ID');
1954
1955 BEGIN
1956 SELECT distinct '1'
1957 INTO l_dummy
1958 FROM gl_access_set_ledgers acc, gl_ledgers lgr
1959 WHERE acc.access_set_id = l_gl_data_access_set
1960 AND lgr.ledger_id = acc.ledger_id
1961 AND lgr.object_type_code = 'L'
1962 AND acc.access_privilege_code in ('B','F')
1963 AND lgr.ledger_id = g_cbc_ledger_id;
1964 EXCEPTION
1965 WHEN NO_DATA_FOUND THEN
1966 FND_MESSAGE.set_name('IGC','IGC_CBC_LEDG_ACCESS');
1967 FND_MESSAGE.SET_TOKEN('LEDGER_NAME',g_cbc_ledger_name);
1968 l_error := FND_MESSAGE.get;
1969 add_error (l_error);
1970 x_msg_buf := l_error ;
1971 x_return_code := 2;
1972 END;
1973
1974 -- Check If budget data is migrated
1975 IF p_balance_type = 'E' THEN
1976 BEGIN
1977 SELECT '1'
1978 INTO l_dummy
1979 FROM DUAL
1980 WHERE EXISTS
1981 (SELECT 1
1982 FROM igc_cbc_je_lines igc
1983 where budget_version_id is NOT null
1984 AND igc.actual_flag = 'E'
1985 AND igc.DETAIL_SUMMARY_CODE = 'D'
1986 AND igc.period_year = p_fin_year
1987 AND set_of_books_id = g_sob_id
1988 AND mig_request_id IS NULL
1989 AND NOT EXISTS
1990 ( SELECT 1
1991 FROM gl_budget_assignments asg,
1992 GL_BUDORG_BC_OPTIONS boc,
1993 GL_BUDGET_VERSIONS BV ,
1994 gl_budgets bud,
1995 gl_period_statuses per_f,
1996 gl_period_statuses per_s
1997 WHERE asg.range_id = boc.range_id
1998 AND BV.BUDGET_VERSION_ID = BOC.FUNDING_BUDGET_VERSION_ID
1999 AND bud.budget_name = BV.budget_name
2000 AND asg.code_combination_id = igc.code_combination_id
2001 AND per_s.ledger_id = bud.ledger_id
2002 AND per_f.ledger_id = bud.ledger_id
2003 AND per_s.application_id = 101
2004 AND per_f.application_id = 101
2005 AND per_s.period_name = bud.first_valid_period_name
2006 AND per_f.period_name = bud.last_valid_period_name
2007 AND igc.effective_date between per_s.start_date and per_f.end_date
2008 AND bud.ledger_id = g_cbc_ledger_id
2009 )
2010 );
2011 IF l_dummy = '1' THEN
2012 FND_MESSAGE.set_name('IGC','IGC_CBC_BUD_DATA_MISS');
2013 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME',g_conc_pr_user);
2014 l_error := FND_MESSAGE.get;
2015 add_error (l_error);
2016 END IF;
2017 EXCEPTION
2018 WHEN NO_DATA_FOUND THEN
2019 NULL;
2020 END;
2021 END IF;
2022
2023
2024
2025 END validate_basic;
2026
2027 FUNCTION check_request(p_request_id IN NUMBER) RETURN NUMBER IS
2028 CURSOR c_req(c_request_id IN NUMBER) IS
2029 SELECT parent_request_id,
2030 request_id,level
2031 FROM fnd_concurrent_requests
2032 CONNECT BY PRIOR request_id = parent_request_id
2033 START with request_id = c_request_id
2034 order by request_id;
2035 l_other_request NUMBER;
2036 bln_request_status BOOLEAN;
2037
2038 l_req_comp_phase VARCHAR2(50);
2039 l_req_comp_status VARCHAR2(50);
2040 l_req_comp_dev_phase VARCHAR2(50);
2041 l_req_comp_dev_status VARCHAR2(50);
2042 l_req_comp_mesg VARCHAR2(2000);
2043 l_error VARCHAR2(2000);
2044 l_return NUMBER := 0;
2045 BEGIN
2046 FOR l_req in c_req(p_request_id)
2047 LOOP
2048 l_other_request := l_req.request_id;
2049 bln_request_status := FND_CONCURRENT.WAIT_FOR_REQUEST
2050 ( request_id =>l_other_request,
2051 phase =>l_req_comp_phase,status => l_req_comp_status,
2052 dev_phase =>l_req_comp_dev_phase,dev_status => l_req_comp_dev_status,
2053 message=>l_req_comp_mesg);
2054 IF trim(l_req_comp_dev_phase) = 'COMPLETE' AND
2055 trim(l_req_comp_dev_status) = 'NORMAL' THEN
2056 IF l_return >= 0 THEN
2057 l_return := 1;
2058 END IF;
2059 ELSE
2060 FND_MESSAGE.set_name('IGC','IGC_CBC_CONC_FAIL');
2061 FND_MESSAGE.SET_TOKEN('REQUEST_ID',l_other_request);
2062 l_error := FND_MESSAGE.get;
2063 add_error (l_error);
2064 l_return := -1;
2065 END IF;
2066 END LOOP;
2067 return l_return;
2068 END;
2069
2070 PROCEDURE print_header(p_balance_type IN VARCHAR2,
2071 p_mode IN VARCHAR2,
2072 p_fiscal_year IN NUMBER) IS
2073 l_conc_id NUMBER := fnd_global.CONC_PROGRAM_ID;
2074 l_conc_pr_name FND_CONCURRENT_PROGRAMS_VL.concurrent_program_name%TYPE;
2075 l_param_out NUMBER;
2076 l_balance_type_param VARCHAR2(240);
2077 l_mode_param VARCHAR2(240);
2078 l_fiscal_year_param VARCHAR2(240);
2079 BEGIN
2080 SELECT concurrent_program_name,
2081 user_concurrent_program_name
2082 INTO l_conc_pr_name,
2083 g_conc_pr_user
2084 FROM fnd_concurrent_programs_vl
2085 WHERE concurrent_program_id = l_conc_id;
2086
2087 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_conc_pr_name ||(rpad(' ',40,' '))||g_conc_pr_user );
2088 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
2089 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
2090 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Process Run Id :'||fnd_global.CONC_REQUEST_ID);
2091
2092 --l_param_out := FND_REQUEST_INFO.get_param_info(1,l_balance_type_param);
2093 --l_param_out := FND_REQUEST_INFO.get_param_info(10,l_mode_param);
2094 --l_param_out := FND_REQUEST_INFO.get_param_info(20,l_fiscal_year_param);
2095 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Balance Type'||' : '||p_balance_type);
2096 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Mode'||' : '||p_mode);
2097 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Fiscal Year'||' : '||p_fiscal_year);
2098 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
2099 commit;
2100 EXCEPTION
2101 WHEN OTHERS THEN
2102 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
2103 END;
2104
2105 PROCEDURE print_enc_stat(p_fiscal_year IN NUMBER) IS
2106 CURSOR c_result IS
2107 SELECT period_name,
2108 SUM(1) total_count,
2109 SUM(decode(mig_request_id,fnd_global.CONC_REQUEST_ID,
2110 decode(substr(mig_result_code,1,1),'P',1,0)
2111 ,0)) migrated_count,
2112 SUM(decode(substr(mig_result_code,1,1),'P',0,1)) pending_count
2113 FROM igc_cbc_je_lines
2114 WHERE period_year = p_fiscal_year
2115 AND set_of_books_id = g_sob_id
2116 AND actual_flag = 'E'
2117 AND DETAIL_SUMMARY_CODE = 'D'
2118 group by period_name;
2119 BEGIN
2120 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
2121 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('=',40,'=')||'CBC Encumbrance Migration Result========================'||rpad('=',40,'='));
2122 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
2123 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('Period Name',30,' ')||' '||rpad('Migrated Lines',25,' ')||' '||rpad('Pending Lines',25,' ')||' '||rpad('Total Lines',25,' '));
2124 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(' ',30,' ')||' '||rpad('In Request Id '||fnd_global.CONC_REQUEST_ID,25,' '));
2125 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('-',30,'-')||' '||rpad('-',25,'-')||' '||rpad('-',25,'-')||' '||rpad('-',25,'-'));
2126 FOR l_result IN c_result
2127 LOOP
2128 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(l_result.period_name,30,' ')||' '||rpad(l_result.migrated_count,25,' ')||' '||rpad(l_result.pending_count,25,' ')||' '||rpad(l_result.total_count,25,' '));
2129 END LOOP;
2130
2131 END;
2132
2133 PROCEDURE print_enc_exceptions(p_fiscal_year IN NUMBER) IS
2134 CURSOR c_fail IS
2135 SELECT b.name,to_char(l.cbc_je_line_num) cbc_je_line_num,
2136 gl.meaning
2137 FROM igc_cbc_je_lines l,igc_cbc_je_batches b,
2138 gl_lookups gl
2139 WHERE b.cbc_je_batch_id = l.cbc_je_batch_id
2140 AND gl.lookup_type LIKE 'FUNDS_CHECK_RESULT_CODE'
2141 AND l.mig_result_code like 'F%'
2142 AND gl.lookup_code = l.mig_result_code
2143 AND l.period_year = p_fiscal_year
2144 AND l.actual_flag = 'E';
2145 bln_error_found BOOLEAN := FALSE;
2146 BEGIN
2147 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
2148 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('=',40,'=')||'CBC Encumbrance details - Failed to Migrate to R12========================'||rpad('=',40,'='));
2149 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
2150 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('Batch Name',50,' ')||' '||rpad('Line Num',10,' ')||' '||rpad('Failed Reason',60,' '));
2151 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('-',50,'-')||' '||rpad('-',10,'-')||' '||rpad('-',60,'-'));
2152 FOR l_fail in c_fail
2153 LOOP
2154 IF (NOT bln_error_found) THEN
2155 bln_error_found := TRUE;
2156 END IF;
2157 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(substr(l_fail.name,1,50),50,' ')||' '||rpad(l_fail.cbc_je_line_num,10,' ')||' '||rpad(substr(l_fail.meaning,1,60),60,' '));
2158 END LOOP;
2159 IF NOT bln_error_found THEN
2160 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'NO DATA FOUND');
2161 END IF;
2162 END;
2163
2164
2165 PROCEDURE print_errors IS
2166 BEGIN
2167 /* Print Header */
2168 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
2169 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('=',40,'=')||'Following Validation error(s) occured'||rpad('=',40,'='));
2170 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('SL NO.',10,' ')||' '||rpad('Error Description',60,' '));
2171 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('-',10,'-')||' '||rpad('-',100,'-'));
2172 IF g_tbl_basic_errors.COUNT <= 0 THEN
2173 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'NO DATA FOUND');
2174 return;
2175 END IF;
2176 FOR i in 1..g_tbl_basic_errors.COUNT
2177 LOOP
2178 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,lpad(to_char(i),10,' ')||' '||substr(g_tbl_basic_errors(i).error_mesg,1,100));
2179 END LOOP;
2180
2181
2182 END print_errors;
2183
2184 PROCEDURE print_end_report IS
2185 BEGIN
2186
2187 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('=',63,'=')||'End of Report'||rpad('=',62,'='));
2188
2189 END print_end_report;
2190
2191 PROCEDURE print_budget_stats IS
2192 BEGIN
2193 /* Print Header */
2194 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
2195 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'================Following Budget Data created in CBC ledger========================');
2196 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
2197 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('Primary Ledger Budget Name',30,' ')||' '||rpad('CBC Ledger Budget Name',30,' '));
2198 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('-',30,'-')||' '||rpad('-',30,'-'));
2199 IF g_tbl_bud_map.COUNT < 1 THEN
2200 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'NO DATA FOUND');
2201 END IF;
2202
2203 FOR i in 1..g_tbl_bud_map.COUNT
2204 LOOP
2205 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(g_tbl_bud_map(i).old_budget_name,30,' ')||' '||rpad(g_tbl_bud_map(i).new_budget_name,30,' '));
2206 END LOOP;
2207
2208 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
2209 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
2210 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('Primary Ledger Budget Organization',40,' ')||' '||rpad('CBC Ledger Budget Organization',40,' '));
2211 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad('-',40,'-')||' '||rpad('-',40,'-'));
2212 IF g_tbl_bud_entity_map.COUNT < 1 THEN
2213 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'NO DATA FOUND');
2214 END IF;
2215
2216 FOR i in 1..g_tbl_bud_entity_map.COUNT
2217 LOOP
2218 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(g_tbl_bud_entity_map(i).old_bud_entity,40,' ')||' '||rpad(g_tbl_bud_entity_map(i).new_bud_entity,40,' '));
2219 END LOOP;
2220
2221
2222 END print_budget_stats;
2223
2224 PROCEDURE Put_Debug_Msg (
2225 p_path IN VARCHAR2,
2226 p_debug_msg IN VARCHAR2
2227 ) IS
2228 BEGIN
2229 IF(g_state_level >= g_debug_level) THEN
2230 FND_LOG.STRING(g_state_level, p_path, p_debug_msg);
2231 END IF;
2232 RETURN;
2233 EXCEPTION
2234 WHEN OTHERS THEN
2235 NULL;
2236 RETURN;
2237 END Put_Debug_Msg;
2238
2239 END IGC_UPGRADE_DATA_R12;