[Home] [Help]
PACKAGE BODY: APPS.GL_AS_POST_UPG_CHK_PKG
Source
1 PACKAGE BODY GL_AS_POST_UPG_CHK_PKG AS
2 /* $Header: gluasucb.pls 120.3 2006/07/28 21:56:44 mgowda noship $ */
3
4 -- -------------------------
5 -- Private Package Variables
6 -- -------------------------
7 pc_log_level_statement CONSTANT NUMBER := FND_LOG.level_statement;
8 pc_log_level_procedure CONSTANT NUMBER := FND_LOG.level_procedure;
9 pc_log_level_event CONSTANT NUMBER := FND_LOG.level_event;
10 pc_log_level_exception CONSTANT NUMBER := FND_LOG.level_exception;
11 pc_log_level_error CONSTANT NUMBER := FND_LOG.level_error;
12 pc_log_level_unexpected CONSTANT NUMBER := FND_LOG.level_unexpected;
13
14 pv_nl VARCHAR2(1);
15
16 PRINT_ERROR EXCEPTION;
17
18 -- -------------------------------
19 -- Private Procedures Declarations
20 -- -------------------------------
21 PROCEDURE Print_Table_Header( p_info_msg_name IN VARCHAR2
22 , p_column_text IN VARCHAR2);
23
24 -- -----------------
25 -- Public Procedures
26 -- -----------------
27
28 -- PROCEDURE
29 -- Verify_Setup()
30 --
31 -- DESCRIPTION:
32 -- This is the main function of this ASM Post-upgrade Check package.
33 PROCEDURE Verify_Setup( x_errbuf IN OUT NOCOPY VARCHAR2
34 , x_retcode IN OUT NOCOPY VARCHAR2) IS
35 v_status_code VARCHAR2(30);
36 v_return_status BOOLEAN;
37 v_column_text VARCHAR2(500);
38
39 CURSOR c_unassigned_alc IS
40 SELECT '<tr align="left" valign="top" class="OraTableCellText">'
41 || '<td>' || NVL(lg.name, '&'||'nbsp;') || '</td>'
42 || '<td>' || NVL(curr.name, '&'||'nbsp;') || '</td>'
43 || '<td>' || NVL(lg.description,
44 '&'||'nbsp;') || '</td></tr>' row_text
45 FROM GL_LEDGERS lg,
46 FND_CURRENCIES_TL curr
47 WHERE lg.ledger_category_code = 'ALC'
48 AND NOT EXISTS(SELECT 'Assigned'
49 FROM GL_LEDGER_RELATIONSHIPS rs
50 WHERE rs.target_ledger_id = lg.ledger_id
51 AND rs.target_ledger_category_code = 'ALC'
52 AND rs.relationship_type_code IN ('JOURNAL', 'SUBLEDGER'))
53 AND curr.currency_code = lg.currency_code
54 AND curr.language = USERENV('LANG')
55 ORDER BY lg.name;
56
57 CURSOR c_multi_src_alc IS
58 SELECT distinct '<tr align="left" valign="top" class="OraTableCellText">'
59 || '<td>' || NVL(alc.name, '&'||'nbsp;') || '</td>'
60 || '<td>' || NVL(rcurr.name, '&'||'nbsp;') || '</td>'
61 || '<td>' || NVL(lkr.meaning, '&'||'nbsp;') || '</td>'
62 || '<td>' || NVL(src.name, '&'||'nbsp;') || '</td>'
63 || '<td>' || NVL(pcurr.name, '&'||'nbsp;') || '</td></tr>' row_text
64 FROM GL_LEDGERS alc,
65 GL_LEDGERS src,
66 FND_CURRENCIES_TL pcurr,
67 FND_CURRENCIES_TL rcurr,
68 GL_LEDGER_RELATIONSHIPS rs,
69 GL_LOOKUPS lkr
70 WHERE src.ledger_id = rs.source_ledger_id
71 AND alc.ledger_id = rs.target_ledger_id
72 AND rcurr.currency_code = alc.currency_code
73 AND rcurr.language = USERENV('LANG')
74 AND pcurr.currency_code = src.currency_code
75 AND pcurr.language = USERENV('LANG')
76 AND rs.target_ledger_category_code = 'ALC'
77 AND rs.relationship_type_code IN ('JOURNAL', 'SUBLEDGER')
78 AND lkr.lookup_code = rs.relationship_type_code
79 AND lkr.lookup_type = 'GL_ASF_ALC_RELATIONSHIP_LEVEL'
80 AND rs.relationship_enabled_flag = 'Y'
81 AND rs.target_ledger_id IN
82 (SELECT rs2.target_ledger_id
83 FROM GL_LEDGER_RELATIONSHIPS rs2
84 WHERE rs2.relationship_enabled_flag = 'Y'
85 AND rs2.target_ledger_category_code = 'ALC'
86 AND rs2.relationship_type_code IN ('JOURNAL', 'SUBLEDGER')
87 HAVING COUNT(distinct rs2.source_ledger_id) > 1
88 GROUP BY rs2.target_ledger_id)
89 ORDER BY row_text;
90
91 CURSOR c_alc_tcurr IS
92 SELECT '<tr align="left" valign="top" class="OraTableCellText">'
93 || '<td>' || NVL(balrs.target_ledger_name, '&'||'nbsp;') || '</td>'
94 || '<td>' || NVL(tcurr.name, '&'||'nbsp;') || '</td>'
95 || '<td>' || NVL(src.name, '&'||'nbsp;') || '</td>'
96 || '<td>' || NVL(scurr.name, '&'||'nbsp;') || '</td></tr>' row_text
97 FROM GL_LEDGERS src,
98 GL_LEDGER_RELATIONSHIPS srcrs,
99 GL_LEDGER_RELATIONSHIPS balrs,
100 FND_CURRENCIES_TL scurr,
101 FND_CURRENCIES_TL tcurr
102 WHERE balrs.relationship_type_code = 'BALANCE'
103 AND balrs.target_ledger_category_code = 'ALC'
104 AND balrs.relationship_enabled_flag = 'Y'
105 AND src.ledger_id = balrs.source_ledger_id
106 AND src.ledger_category_code = 'ALC'
107 AND srcrs.target_ledger_id (+) = balrs.source_ledger_id
108 AND srcrs.target_ledger_category_code (+) = 'ALC'
109 AND srcrs.relationship_type_code (+) <> 'NONE'
110 AND srcrs.relationship_type_code (+) <> 'BALANCE'
111 AND srcrs.application_id (+) = 101
112 AND scurr.currency_code = src.currency_code
113 AND scurr.language = USERENV('LANG')
114 AND tcurr.currency_code = balrs.target_currency_code
115 AND tcurr.language = USERENV('LANG')
116 ORDER BY balrs.target_ledger_name, src.name;
117
118 CURSOR c_journal_alc IS
119 SELECT '<tr align="left" valign="top" class="OraTableCellText">'
120 || '<td>' || NVL(alc.name, '&'||'nbsp;') || '</td>'
121 || '<td>' || NVL(rcurr.name, '&'||'nbsp;') || '</td>'
122 || '<td>' || NVL(alc.description, '&'||'nbsp;') || '</td>'
123 || '<td>' || NVL(src.name, '&'||'nbsp;') || '</td>'
124 || '<td>' || NVL(pcurr.name, '&'||'nbsp;') || '</td></tr>' row_text
125 FROM GL_LEDGER_RELATIONSHIPS rs,
126 GL_LEDGERS alc,
127 GL_LEDGERS src,
128 FND_CURRENCIES_TL pcurr,
129 FND_CURRENCIES_TL rcurr
130 WHERE rs.relationship_type_code = 'JOURNAL'
131 AND rs.target_ledger_category_code = 'ALC'
132 AND alc.ledger_id = rs.target_ledger_id
133 AND src.ledger_id = rs.source_ledger_id
134 AND pcurr.currency_code = src.currency_code
135 AND pcurr.language = USERENV('LANG')
136 AND rcurr.currency_code = alc.currency_code
137 AND rcurr.language = USERENV('LANG')
138 AND EXISTS (SELECT 'Upgraded from MRC RSOB'
139 FROM GL_MC_BOOK_ASSIGNMENTS_11i
140 WHERE primary_set_of_books_id = rs.source_ledger_id
141 AND reporting_set_of_books_id = rs.target_ledger_id)
142 ORDER BY alc.name, src.name;
143
144 CURSOR c_crt_gl_rs IS
145 SELECT '<tr align="left" valign="top" class="OraTableCellText">'
146 || '<td>' || NVL(rs.target_ledger_name, '&'||'nbsp;') || '</td>'
147 || '<td>' || NVL(src.name, '&'||'nbsp;') || '</td>'
148 || '<td>' || NVL(NVL(lkre.meaning, rs.relationship_enabled_flag),
149 '&'||'nbsp;') || '</td>'
150 || '<td>' || NVL(NVL(dtype.user_conversion_type,
151 rs.alc_default_conv_rate_type),
152 '&'||'nbsp;') || '</td>'
153 || '<td>' || NVL(NVL(lknra.meaning, rs.alc_no_rate_action_code),
154 '&'||'nbsp;') || '</td>'
155 || '<td>' || NVL(NVL(lkict.meaning, rs.alc_inherit_conversion_type),
156 '&'||'nbsp;') || '</td>'
157 || '<td>' || NVL(NVL(lkico.meaning, rs.alc_init_conv_option_code),
158 '&'||'nbsp;') || '</td>'
159 || '<td>' || NVL(rs.alc_init_period, '&'||'nbsp;') || '</td>'
160 || '<td>' || DECODE(rs.alc_initializing_rate_date
161 , null, '&'||'nbsp;'
162 , TO_DATE(rs.alc_initializing_rate_date,
163 'DD-MON-YYYY')) || '</td>'
164 || '<td>' || NVL(NVL(itype.user_conversion_type,
165 rs.alc_initializing_rate_type),
166 '&'||'nbsp;') || '</td></tr>' row_text
167 FROM GL_LEDGERS src,
168 GL_LEDGER_RELATIONSHIPS rs,
169 GL_DAILY_CONVERSION_TYPES dtype,
170 GL_DAILY_CONVERSION_TYPES itype,
171 GL_LOOKUPS lkre,
172 GL_LOOKUPS lknra,
173 GL_LOOKUPS lkict,
174 GL_LOOKUPS lkico
175 WHERE rs.target_ledger_category_code = 'ALC'
176 AND rs.relationship_type_code = 'SUBLEDGER'
177 AND rs.application_id = 101
178 AND rs.created_by = 1
179 AND src.ledger_id = rs.source_ledger_id
180 AND dtype.conversion_type (+) = rs.alc_default_conv_rate_type
181 AND itype.conversion_type (+) = rs.alc_initializing_rate_type
182 AND lkre.lookup_code (+) = rs.relationship_enabled_flag
183 AND lkre.lookup_type (+) = 'YES/NO'
184 AND lkre.enabled_flag (+) = 'Y'
185 AND lknra.lookup_code (+) = rs.alc_no_rate_action_code
186 AND lknra.lookup_type (+) = 'GL_ASF_ALC_RATE_ACTION'
187 AND lknra.enabled_flag (+) = 'Y'
188 AND lkict.lookup_code (+) = rs.alc_inherit_conversion_type
189 AND lkict.lookup_type (+) = 'YES/NO'
190 AND lkict.enabled_flag (+) = 'Y'
191 AND lkico.lookup_code (+) = rs.alc_init_conv_option_code
192 AND lkico.lookup_type (+) = 'GL_ASF_ALC_CONVERSION_OPTION'
193 AND lkico.enabled_flag (+) = 'Y'
194 AND NOT EXISTS (SELECT 'Exists in GL_MC_REPORTING_OPTIONS'
195 FROM GL_MC_REPORTING_OPTIONS_11i
196 WHERE primary_set_of_books_id = rs.source_ledger_id
197 AND reporting_set_of_books_id = rs.target_ledger_id
198 AND application_id = 101)
199 ORDER BY rs.target_ledger_name, src.name;
200
201 CURSOR c_invalid_jrule IS
202 SELECT '<tr align="left" valign="top" class="OraTableCellText">'
203 || '<td>' || NVL(alc.name, '&'||'nbsp;') || '</td>'
204 || '<td>' || NVL(src.name, '&'||'nbsp;') || '</td>'
205 || '<td>' || NVL(appl.application_name, '&'||'nbsp;') || '</td>'
206 || '<td>' || NVL(jes.user_je_source_name, '&'||'nbsp;') || '</td>'
207 || '<td>' || NVL(jec.user_je_category_name,
208 '&'||'nbsp;') || '</td></tr>' row_text
209 FROM GL_LEDGERS alc,
210 GL_LEDGERS src,
211 GL_LEDGER_RELATIONSHIPS glrs,
212 GL_LEDGER_RELATIONSHIPS sublgrs,
213 GL_JE_INCLUSION_RULES jrule,
214 GL_JE_SOURCES_VL jes,
215 GL_JE_CATEGORIES_VL jec,
216 FND_APPLICATION_TL appl
217 WHERE alc.ledger_id = glrs.target_ledger_id
218 AND src.ledger_id = glrs.source_ledger_id
219 AND glrs.application_id = 101
220 AND glrs.target_ledger_category_code = 'ALC'
221 AND glrs.relationship_type_code = 'SUBLEDGER'
222 AND glrs.relationship_enabled_flag = 'Y'
223 AND glrs.gl_je_conversion_set_id = jrule.je_rule_set_id
224 AND sublgrs.target_ledger_id = glrs.target_ledger_id
225 AND sublgrs.source_ledger_id = glrs.source_ledger_id
226 AND sublgrs.primary_ledger_id = glrs.primary_ledger_id
227 AND sublgrs.target_ledger_category_code = 'ALC'
228 AND sublgrs.relationship_type_code = 'SUBLEDGER'
229 AND sublgrs.relationship_enabled_flag = 'Y'
230 AND sublgrs.application_id = DECODE(jrule.je_source_name
231 , 'Assets' , 140
232 , 'AR Translator' , 222
233 , 'Receivables' , 222
234 , 'Project Accounting', 275
235 , 'Purchasing' , 201
236 , 'Payables' , 200
237 , 'AP Translator' , 200
238 , -1)
239 AND jes.je_source_name = jrule.je_source_name
240 AND jec.je_category_name = jrule.je_category_name
241 AND appl.application_id = sublgrs.application_id
242 AND appl.language = USERENV('LANG')
243 AND jrule.include_flag = 'Y'
244 ORDER BY alc.name, src.name, appl.application_name,
245 jes.user_je_source_name, jec.user_je_category_name;
246
247 CURSOR c_diff_setup_alc IS
248 SELECT '<tr align="left" valign="top" class="OraTableCellText">'
249 || '<td>' || NVL(alc.name, '&'||'nbsp;') || '</td>'
250 || '<td>' || NVL(src.name, '&'||'nbsp;') || '</td>'
251 || '<td>' || NVL(appl.application_name, '&'||'nbsp;') || '</td>'
252 || '<td>' || NVL(ou.name, '&'||'nbsp;') || '</td>'
253 || '<td>' || NVL(NVL(ctype.user_conversion_type,
254 rs.alc_default_conv_rate_type),
255 '&'||'nbsp;') || '</td>'
256 || '<td>' || NVL(NVL(lk1.meaning,
257 rs.alc_no_rate_action_code),
258 '&'||'nbsp;') || '</td>'
259 || '<td>' || NVL(NVL(lk2.meaning,
260 alc_inherit_conversion_type),
261 '&'||'nbsp;') || '</td></tr>' row_text
262 FROM GL_LEDGERS alc,
263 GL_LEDGERS src,
264 GL_LEDGER_RELATIONSHIPS rs,
265 FND_APPLICATION_TL appl,
266 HR_OPERATING_UNITS ou,
267 GL_DAILY_CONVERSION_TYPES ctype,
268 GL_LOOKUPS lk1,
269 GL_LOOKUPS lk2
270 WHERE alc.ledger_id = rs.target_ledger_id
271 AND src.ledger_id = rs.source_ledger_id
272 AND appl.application_id = rs.application_id
273 AND appl.language = USERENV('LANG')
274 AND ou.set_of_books_id (+) = rs.primary_ledger_id
275 AND ou.organization_id (+) = rs.org_id
276 AND rs.target_ledger_category_code = 'ALC'
277 AND rs.relationship_type_code = 'SUBLEDGER'
278 AND ctype.conversion_type (+) = rs.alc_default_conv_rate_type
279 AND lk1.lookup_code (+) = rs.alc_no_rate_action_code
280 AND lk1.lookup_type (+) = 'GL_ASF_ALC_RATE_ACTION'
281 AND lk1.enabled_flag (+) = 'Y'
282 AND lk2.lookup_code (+) = rs.alc_inherit_conversion_type
283 AND lk2.lookup_type (+) = 'YES/NO'
284 AND lk2.enabled_flag (+) = 'Y'
285 AND EXISTS (
286 SELECT 'Different setup'
287 FROM GL_LEDGER_RELATIONSHIPS rs2
288 WHERE rs2.target_ledger_id = rs.target_ledger_id
289 AND rs2.source_ledger_id = rs.source_ledger_id
290 AND rs2.primary_ledger_id = rs.primary_ledger_id
291 AND rs2.target_ledger_category_code = 'ALC'
292 AND rs2.relationship_type_code = 'SUBLEDGER'
293 AND rs2.relationship_id <> rs.relationship_id
294 AND ( rs2.alc_default_conv_rate_type
295 <> rs.alc_default_conv_rate_type
296 OR rs2.alc_no_rate_action_code
297 <> rs.alc_no_rate_action_code
298 OR rs2.alc_inherit_conversion_type
299 <> rs.alc_inherit_conversion_type))
300 ORDER BY alc.name, src.name, appl.application_name, ou.name;
301
302 CURSOR c_partial_setup_alc IS
303 SELECT '<tr align="left" valign="top" class="OraTableCellText">'
304 || '<td>' || NVL(alc.name, '&'||'nbsp;') || '</td>'
305 || '<td>' || NVL(src.name, '&'||'nbsp;') || '</td>'
306 || '<td>' || NVL(appl.application_name, '&'||'nbsp;') || '</td>'
307 || '<td>' || NVL(ou.name, '&'||'nbsp;') || '</td></tr>' row_text
308 FROM GL_LEDGERS alc,
309 GL_LEDGERS src,
310 FND_APPLICATION_TL appl,
311 HR_OPERATING_UNITS ou,
312 (SELECT DISTINCT source_ledger_id, target_ledger_id, primary_ledger_id
313 FROM GL_LEDGER_RELATIONSHIPS
314 WHERE target_ledger_category_code = 'ALC'
315 AND relationship_type_code = 'SUBLEDGER'
316 AND application_id <> 101
317 AND relationship_enabled_flag = 'Y') qrs
318 WHERE alc.ledger_id = qrs.target_ledger_id
319 AND src.ledger_id = qrs.source_ledger_id
320 AND ou.set_of_books_id (+) = qrs.primary_ledger_id
321 AND appl.application_id IN (101, 140, 200, 201, 222, 275)
322 AND appl.language = USERENV('LANG')
323 AND NOT EXISTS
324 (SELECT 'Enabled relatinoship defined'
325 FROM GL_LEDGER_RELATIONSHIPS rs
326 WHERE rs.target_ledger_category_code = 'ALC'
327 AND rs.relationship_type_code = 'SUBLEDGER'
328 AND rs.relationship_enabled_flag = 'Y'
329 AND rs.application_id = appl.application_id
330 AND rs.target_ledger_id = alc.ledger_id
331 AND rs.source_ledger_id = src.ledger_id
332 AND ( NVL(rs.org_id, -99) = -99
333 OR rs.org_id = ou.organization_id))
334 ORDER BY alc.name, src.name, appl.application_name, ou.name;
335
336 v_func_name VARCHAR2(100);
337 v_module VARCHAR2(100);
338
339 BEGIN
340 -- Initialize the variables
341 v_status_code := 'INIT_VAR';
342 v_module := 'gl.plsql.gl_as_post_upg_chk_pkg.verify_setup';
343 v_func_name := 'GL_AS_POST_UPG_CHK_PKG.Verify_Setup';
344 pv_nl := '
345 ';
346
347 -- Log the procedure entry
348 GL_MESSAGE.Func_Ent(func_name => v_func_name,
349 log_level => pc_log_level_procedure,
350 module => v_module);
351
352 -- Write the HTML header section
353 v_status_code := 'WRITE_HTML_HDR';
354
355 FND_FILE.put_line
356 (FND_FILE.output,
357 '<html>'
358 ||pv_nl||'<head>'
359 ||pv_nl||'<title>'
360 ||FND_MESSAGE.Get_String('SQLGL', 'GL_ASU_POST_RPT_TITLE')
361 ||'</title>'
362 ||pv_nl||'<meta http-equiv="Content-Type" content="text/html; '
363 ||'charset=iso-8859-1">');
364
365 FND_FILE.put_line
366 (FND_FILE.output,
367 pv_nl||'<STYLE TYPE="text/css">'
368 ||pv_nl||' .OraTableColumnHeader {font-family:Arial, Helvetica, Geneva, '
369 ||'sans-serif; font-size:10pt; font-weight:bold; '
370 ||'background-color:#cccc99; color:#336699; text-indent:1}'
371 ||pv_nl||' .OraTableCellText {font-family:Arial, Helvetica, Geneva, '
372 ||'sans-serif; font-size:10pt; background-color:#f7f7e7; '
373 ||'color:#000000; text-indent:1}'
374 ||pv_nl||' .ASMHeaderText {font-family:Arial, Helvetica, Geneva, '
375 ||'sans-serif; color:#000000}'
376 ||pv_nl||' .ASMErrorText {font-family:Arial, Helvetica, Geneva, '
377 ||'sans-serif; font-size:10pt; font-weight:bold; color:#FF0000}'
378 ||pv_nl||' .ASMInfoText {font-family:Arial, Helvetica, Geneva, '
379 ||'sans-serif; font-size:10pt; color:#000000}'
380 ||pv_nl||'</STYLE>');
381
382 FND_FILE.put_line
383 (FND_FILE.output,
384 pv_nl||'</head>'
385 ||pv_nl||'<body bgcolor="#FFFFFF">');
386
387 -- Write the report title
388 v_status_code := 'WRITE_RPT_TITLE';
389
390 FND_FILE.put_line
391 (FND_FILE.output,
392 pv_nl||'<h2><font class="ASMHeaderText">'
393 ||FND_MESSAGE.Get_String('SQLGL', 'GL_ASU_POST_RPT_TITLE')
394 ||'</font></h2>'
395 ||pv_nl||'<h4><font class="ASMInfoText">As of '
396 ||TO_CHAR(SYSDATE, 'DD-MON-YY HH:MM:SS')||'</font></h4>');
397
398 -- Print the message GL_ASU_ADDITIONAL_INFO
399 FND_FILE.put_line
400 (FND_FILE.output,
401 pv_nl||'<br><font class="ASMInfoText">'
402 ||FND_MESSAGE.Get_String('SQLGL', 'GL_ASU_ADDITIONAL_INFO')
403 ||'</font><br><br>');
404
405 v_status_code := 'CHECK_UNASSIGNED_ALC';
406 -- Check if there are any unassigned ALCs
407
408 -- Print section title
409 FND_FILE.put_line
410 (FND_FILE.output,
411 pv_nl||'<br><br><B><font class="ASMHeaderText">'
412 ||FND_MESSAGE.Get_String('SQLGL', 'GL_ASU_POST_UNASSIGN_RC')
413 ||'</front></B><br><br>');
414
415 FOR v_unassigned_alc IN c_unassigned_alc
416 LOOP
417 IF (v_status_code = 'CHECK_UNASSIGNED_ALC')
418 THEN
419 -- Print the talbe header
420 v_status_code := 'PRINT_UNASSIGNED_ALC_HDR';
421
422 -- Build the column text
423 v_column_text :=
424 '<td>'||FND_MESSAGE.Get_String('SQLGL',
425 'GL_ASU_PRIMARY_LEDGER')
426 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
427 'MRC_CURRSETUP_CURR')
428 ||'</td><td>'||FND_MESSAGE.Get_String('FND',
429 'FND_MENU_DESC_LABEL')
430 ||'</td>';
431
432 -- Print the info text and table column
433 Print_Table_Header(p_info_msg_name => 'GL_ASU_POST_UNASSIGN_RC_INFO',
434 p_column_text => v_column_text);
435
436 -- Set the status so we won't print the header again
437 v_status_code := 'PRINT_UNASSIGNED_ALC_ROW';
438
439 END IF;
440
441 -- Print the row text
442 FND_FILE.put_line(FND_FILE.output, pv_nl||v_unassigned_alc.row_text);
443
444 END LOOP;
445
446 -- Check if we have print Unassigned ALC table
447 IF (v_status_code = 'PRINT_UNASSIGNED_ALC_ROW')
448 THEN
449 -- Some rows are printed
450 FND_FILE.put_line(FND_FILE.output, pv_nl||'</table><br><br>');
451 ELSE
452 -- No setup issues are found
453 FND_FILE.put_line(FND_FILE.output,
454 pv_nl||'<font class="ASMInfoText">'
455 ||FND_MESSAGE.Get_String('SQLGL',
456 'GL_ASU_NO_ISSUES')
457 ||'</font><br><br>');
458 END IF;
459
460 v_status_code := 'CHECK_MULTI_SRC_ALC';
461 -- Check if there are any ALCs assigned to multiple Sources
462
463 -- Print section title
464 FND_FILE.put_line
465 (FND_FILE.output,
466 pv_nl||'<br><br><B><font class="ASMHeaderText">'
467 ||FND_MESSAGE.Get_String('SQLGL', 'GL_ASU_POST_MULTI_SRC_RC')
468 ||'</front></B><br><br>');
469
470 FOR v_multi_src_alc IN c_multi_src_alc
471 LOOP
472 IF (v_status_code = 'CHECK_MULTI_SRC_ALC')
473 THEN
474 -- Print the table header and info text
475 v_status_code := 'PRINT_MUTLI_SRC_ALC_HDR';
476
477 -- Build the column text
478 v_column_text :=
479 '<td>'||FND_MESSAGE.Get_String('SQLGL',
480 'GL_ASU_RCURR')
481 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
482 'MRC_CURRSETUP_CURR')
483 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
484 'GL_ASU_POST_RC_LEVEL')
485 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
486 'GL_ASU_POST_SRC_LG')
487 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
488 'GL_ASU_POST_SRC_CURR')
489 ||'</td>';
490
491 -- Print the info text and table column
492 Print_Table_Header(p_info_msg_name => 'GL_ASU_POST_MULTI_SRC_RC_INFO',
493 p_column_text => v_column_text);
494
495 -- Set the status so we won't print the header again
496 v_status_code := 'PRINT_MULTI_SRC_ALC_ROW';
497
498 END IF;
499
500 -- Print the row text
501 FND_FILE.put_line(FND_FILE.output, pv_nl||v_multi_src_alc.row_text);
502
503 END LOOP;
504
505 -- Check if we have print Multi-Source ALC table
506 IF (v_status_code = 'PRINT_MULTI_SRC_ALC_ROW')
507 THEN
508 -- Some rows are printed
509 FND_FILE.put_line(FND_FILE.output, pv_nl||'</table><br><br>');
510 ELSE
511 -- No setup issues are found
512 FND_FILE.put_line(FND_FILE.output,
513 pv_nl||'<font class="ASMInfoText">'
514 ||FND_MESSAGE.Get_String('SQLGL',
515 'GL_ASU_NO_ISSUES')
516 ||'</font><br><br>');
517 END IF;
518
519 v_status_code := 'CHECK_ALC_TCURR';
520 -- Check if any ALC Ledgers have Balance Level ALC
521
522 -- Print section title
523 FND_FILE.put_line
524 (FND_FILE.output,
525 pv_nl||'<br><br><B><font class="ASMHeaderText">'
526 ||FND_MESSAGE.Get_String('SQLGL', 'GL_ASU_POST_BLRC_SRC')
527 ||'</front></B><br><br>');
528
529 FOR v_alc_tcurr IN c_alc_tcurr
530 LOOP
531 IF (v_status_code = 'CHECK_ALC_TCURR')
532 THEN
533 -- Print the table header and info text
534 v_status_code := 'PRINT_ALC_TCURR_HDR';
535
536 -- Build the column text
537 v_column_text :=
538 '<td>'||FND_MESSAGE.Get_String('SQLGL',
539 'GL_ASU_RCURR')||' '
540 ||FND_MESSAGE.Get_String('SQLGL', 'GL_ASU_POST_BALANCE_LEVEL')
541 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
542 'MRC_CURRSETUP_CURR')
543 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
544 'GL_ASU_POST_SRC_RC_JL_SL')
545 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
546 'GL_ASU_POST_SRC_CURR')
547 ||'</td>';
548
549 -- Print the info text and table column
550 Print_Table_Header(p_info_msg_name => 'GL_ASU_POST_BLRC_SRC_INFO',
551 p_column_text => v_column_text);
552
553 -- Set the status so we won't print the header again
554 v_status_code := 'PRINT_ALC_TCURR_ROW';
555
556 END IF;
557
558 -- Print the row text
559 FND_FILE.put_line(FND_FILE.output, pv_nl||v_alc_tcurr.row_text);
560
561 END LOOP;
562
563 -- Check if we have print RSOB Translated Currencies table
564 IF (v_status_code = 'PRINT_ALC_TCURR_ROW')
565 THEN
566 -- Some rows are printed
567 FND_FILE.put_line(FND_FILE.output, pv_nl||'</table><br><br>');
568 ELSE
569 -- No setup issues are found
570 FND_FILE.put_line(FND_FILE.output,
571 pv_nl||'<font class="ASMInfoText">'
572 ||FND_MESSAGE.Get_String('SQLGL',
573 'GL_ASU_NO_ISSUES')
574 ||'</font><br><br>');
575 END IF;
576
577 v_status_code := 'CHECK_JOURNAL_ALC';
578 -- Check if there are any journal level ALC Ledgers
579
580 -- Print section title
581 FND_FILE.put_line
582 (FND_FILE.output,
583 pv_nl||'<br><br><B><font class="ASMHeaderText">'
584 ||FND_MESSAGE.Get_String('SQLGL', 'GL_ASU_POST_JLRC_REVIEW')
585 ||'</front></B><br><br>');
586
587 FOR v_journal_alc IN c_journal_alc
588 LOOP
589 IF (v_status_code = 'CHECK_JOURNAL_ALC')
590 THEN
591 -- Print the table header and info text
592 v_status_code := 'PRINT_JOURNAL_ALC_HDR';
593
594 -- Build the column text
595 v_column_text :=
596 '<td>'||FND_MESSAGE.Get_String('SQLGL',
597 'GL_ASU_RCURR')
598 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
599 'MRC_CURRSETUP_CURR')
600 ||'</td><td>'||FND_MESSAGE.Get_String('FND',
601 'FND_MENU_DESC_LABEL')
602 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
603 'GL_ASU_POST_SRC_LG')
604 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
605 'GL_ASU_POST_SRC_CURR')
606 ||'</td>';
607
608 -- Print the info text and table column
609 Print_Table_Header(p_info_msg_name => 'GL_ASU_POST_JLRC_REVIEW_INFO',
610 p_column_text => v_column_text);
611
612 -- Set the status so we won't print the header again
613 v_status_code := 'PRINT_JOURNAL_ALC_ROW';
614
615 END IF;
616
617 -- Print the row text
618 FND_FILE.put_line(FND_FILE.output, pv_nl||v_journal_alc.row_text);
619
620 END LOOP;
621
622 -- Check if we have print Journal Level ALC Ledgers table
623 IF (v_status_code = 'PRINT_JOURNAL_ALC_ROW')
624 THEN
625 -- Some rows are printed
626 FND_FILE.put_line(FND_FILE.output, pv_nl||'</table><br><br>');
627 ELSE
628 -- No setup issues are found
629 FND_FILE.put_line(FND_FILE.output,
630 pv_nl||'<font class="ASMInfoText">'
631 ||FND_MESSAGE.Get_String('SQLGL',
632 'GL_ASU_NO_ISSUES')
633 ||'</font><br><br>');
634 END IF;
635
636 v_status_code := 'CHECK_CRT_GL_RS';
637 -- Check if there are any Subledger Level ALC GL relationships created by
638 -- the Upgrade
639
640 -- Print section title
641 FND_FILE.put_line
642 (FND_FILE.output,
643 pv_nl||'<br><br><B><font class="ASMHeaderText">'
644 ||FND_MESSAGE.Get_String('SQLGL', 'GL_ASU_POST_SLRC_GL_CRT')
645 ||'</front></B><br><br>');
646
647 FOR v_crt_gl_rs IN c_crt_gl_rs
648 LOOP
649 IF (v_status_code = 'CHECK_CRT_GL_RS')
650 THEN
651 -- Print the table header and info text
652 v_status_code := 'PRINT_CRT_GL_RS_HDR';
653
654 -- Build the column text
655 v_column_text :=
656 '<td>'||FND_MESSAGE.Get_String('SQLGL',
657 'GL_ASU_RCURR')
658 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
659 'GL_ASU_POST_SRC_LG')
660 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
661 'GL_ASU_POST_ENABLED')
662 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
663 'GL_ASU_POST_DEFAULT_RTYPE')
664 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
665 'GL_ASU_POST_MISSING_RATE')
666 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
667 'GL_ASU_POST_INHERIT_RTYPE')
668 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
669 'GL_ASU_POST_INIT_CONV_OPT')
670 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
671 'GL_ASU_POST_INIT_PERIOD')
672 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
673 'GL_ASU_POST_INIT_RDATE')
674 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
675 'GL_ASU_POST_INIT_RTYPE')
676 ||'</td>';
677
678 -- Print the info text and table column
679 Print_Table_Header(p_info_msg_name => 'GL_ASU_POST_SLRC_GL_CRT_INFO',
680 p_column_text => v_column_text);
681
682 -- Set the status so we won't print the header again
683 v_status_code := 'PRINT_CRT_GL_RS_ROW';
684
685 END IF;
686
687 FND_FILE.put_line(FND_FILE.output, pv_nl||v_crt_gl_rs.row_text);
688
689 END LOOP;
690
691 -- Check if we have print GL Setup Created by Upgrade table
692 IF (v_status_code = 'PRINT_CRT_GL_RS_ROW')
693 THEN
694 -- Some rows are printed
695 FND_FILE.put_line(FND_FILE.output, pv_nl||'</table><br><br>');
696 ELSE
697 -- No setup issues are found
698 FND_FILE.put_line(FND_FILE.output,
699 pv_nl||'<font class="ASMInfoText">'
700 ||FND_MESSAGE.Get_String('SQLGL',
701 'GL_ASU_NO_ISSUES')
702 ||'</font><br><br>');
703 END IF;
704
705 v_status_code := 'CHECK_INVALID_JRULE';
706 -- Check if there are any invalid journal inclusion rules setup
707
708 -- Print section title
709 FND_FILE.put_line
710 (FND_FILE.output,
711 pv_nl||'<br><br><B><font class="ASMHeaderText">'
712 ||FND_MESSAGE.Get_String('SQLGL', 'GL_ASU_POST_SLRC_JRULE')
713 ||'</front></B><br><br>');
714
715 FOR v_invalid_jrule IN c_invalid_jrule
716 LOOP
717 IF (v_status_code = 'CHECK_INVALID_JRULE')
718 THEN
719 -- Print the table header and info text
720 v_status_code := 'PRINT_INVALID_JRULE_HDR';
721
722 -- Build the column text
723 v_column_text :=
724 '<td>'||FND_MESSAGE.Get_String('SQLGL',
725 'GL_ASU_RCURR')
726 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
727 'GL_ASU_POST_SRC_LG')
728 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
729 'GL_ASU_PRODUCT')
730 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
731 'GL_ASU_JE_SOURCE')
732 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
733 'GL_ASU_JE_CATEGORY')
734 ||'</td>';
735
736 -- Print the info text and table column
737 Print_Table_Header(p_info_msg_name => 'GL_ASU_POST_SLRC_JRULE_INFO',
738 p_column_text => v_column_text);
739
740 -- Set the status so we won't print the header again
741 v_status_code := 'PRINT_INVALID_JRULE_ROW';
742
743 END IF;
744
745 -- Print the row text
746 FND_FILE.put_line(FND_FILE.output, pv_nl||v_invalid_jrule.row_text);
747
748 END LOOP;
749
750 -- Check if we have print Invalid Journal Inclusion Rules table
751 IF (v_status_code = 'PRINT_INVALID_JRULE_ROW')
752 THEN
753 -- Some rows are printed
754 FND_FILE.put_line(FND_FILE.output, pv_nl||'</table><br><br>');
755 ELSE
756 -- No setup issues are found
757 FND_FILE.put_line(FND_FILE.output,
758 pv_nl||'<font class="ASMInfoText">'
759 ||FND_MESSAGE.Get_String('SQLGL',
760 'GL_ASU_NO_ISSUES')
761 ||'</font><br><br>');
762 END IF;
763
764 v_status_code := 'CHECK_DIFF_SETUP_ALC';
765 -- Check if there are any ALC with inconsistent setup
766
767 -- Print section title
768 FND_FILE.put_line
769 (FND_FILE.output,
770 pv_nl||'<br><br><B><font class="ASMHeaderText">'
771 ||FND_MESSAGE.Get_String('SQLGL', 'GL_ASU_POST_SLRC_DIFF')
772 ||'</front></B><br><br>');
773
774 FOR v_diff_setup_alc IN c_diff_setup_alc
775 LOOP
776 IF (v_status_code = 'CHECK_DIFF_SETUP_ALC')
777 THEN
778 -- Print the table header and info text
779 v_status_code := 'PRINT_DIFF_SETUP_ALC_HDR';
780
781 -- Build the column text
782 v_column_text :=
783 '<td>'||FND_MESSAGE.Get_String('SQLGL',
784 'GL_ASU_RCURR')
785 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
786 'GL_ASU_POST_SRC_LG')
787 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
788 'GL_ASU_PRODUCT')
789 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
790 'GL_ASU_OU')
791 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
792 'GL_ASU_POST_DEFAULT_RTYPE')
793 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
794 'GL_ASU_POST_MISSING_RATE')
795 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
796 'GL_ASU_POST_INHERIT_RTYPE')
797 ||'</td>';
798
799 -- Print the info text and table column
800 Print_Table_Header(p_info_msg_name => 'GL_ASU_POST_SLRC_DIFF_INFO',
801 p_column_text => v_column_text);
802
803 -- Set the status so we won't print the header again
804 v_status_code := 'PRINT_DIFF_SETUP_ALC_ROW';
805
806 END IF;
807
808 -- Print the row text
809 FND_FILE.put_line(FND_FILE.output, pv_nl||v_diff_setup_alc.row_text);
810
811 END LOOP;
812
813 -- Check if we have print Inconsisent ALC Setup table
814 IF (v_status_code = 'PRINT_DIFF_SETUP_ALC_ROW')
815 THEN
816 -- Some rows are printed
817 FND_FILE.put_line(FND_FILE.output, pv_nl||'</table><br><br>');
818 ELSE
819 -- No setup issues are found
820 FND_FILE.put_line(FND_FILE.output,
821 pv_nl||'<font class="ASMInfoText">'
822 ||FND_MESSAGE.Get_String('SQLGL',
823 'GL_ASU_NO_ISSUES')
824 ||'</font><br><br>');
825 END IF;
826
827 v_status_code := 'CHECK_PARTIAL_SETUP_ALC';
828 -- Check if there are any ALC Ledgers with partial setup only
829
830 -- Print section title
831 FND_FILE.put_line
832 (FND_FILE.output,
833 pv_nl||'<br><br><B><font class="ASMHeaderText">'
834 ||FND_MESSAGE.Get_String('SQLGL', 'GL_ASU_POST_SLRC_PARTIAL')
835 ||'</front></B><br><br>');
836
837 FOR v_partial_setup_alc IN c_partial_setup_alc
838 LOOP
839 IF (v_status_code = 'CHECK_PARTIAL_SETUP_ALC')
840 THEN
841 -- Print the table header and info text
842 v_status_code := 'PRINT_PARTIAL_SETUP_ALC_HDR';
843
844 -- Build the column text
845 v_column_text :=
846 '<td>'||FND_MESSAGE.Get_String('SQLGL',
847 'GL_ASU_RCURR')
848 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
849 'GL_ASU_POST_SRC_LG')
850 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
851 'GL_ASU_PRODUCT')
852 ||'</td><td>'||FND_MESSAGE.Get_String('SQLGL',
853 'GL_ASU_OU')
854 ||'</td>';
855
856 -- Print the info text and table column
857 Print_Table_Header(p_info_msg_name => 'GL_ASU_POST_SLRC_PARTIAL_INFO',
858 p_column_text => v_column_text);
859
860 -- Set the status so we won't print the header again
861 v_status_code := 'PRINT_PARTIAL_SETUP_ALC_ROW';
862
863 END IF;
864
865 -- Print the row text
866 FND_FILE.put_line(FND_FILE.output, pv_nl||v_partial_setup_alc.row_text);
867
868 END LOOP;
869
870 -- Check if we have print Incomplete ALC Setup table
871 IF (v_status_code = 'PRINT_PARTIAL_SETUP_ALC_ROW')
872 THEN
873 -- Some rows are printed
874 FND_FILE.put_line(FND_FILE.output, pv_nl||'</table><br><br>');
875 ELSE
876 -- No setup issues are found
877 FND_FILE.put_line(FND_FILE.output,
878 pv_nl||'<font class="ASMInfoText">'
879 ||FND_MESSAGE.Get_String('SQLGL',
880 'GL_ASU_NO_ISSUES')
881 ||'</font><br><br>');
882 END IF;
883
884 -- Complete the HTML report
885 FND_FILE.put_line(FND_FILE.output, pv_nl||'</body></html>');
886
887 -- Log the success exit
888 GL_MESSAGE.Func_Succ(func_name => v_func_name,
889 log_level => pc_log_level_procedure,
890 module => v_module);
891
892 -- Set the concurrent program completion status before exit
893 v_return_status := FND_CONCURRENT.Set_Completion_Status
894 (status => 'SUCCESS', message => NULL);
895
896 -- Commit the changes and exit
897 Commit;
898
899 EXCEPTION
900 WHEN PRINT_ERROR THEN
901 -- <<< Error rased when printing table header >>>
902
903 -- Set the output parameters for the concurrent program
904 x_errbuf := SQLERRM;
905 x_retcode := SQLCODE;
906
907 -- Print the fatal error message
908 FND_FILE.put_line
909 (FND_FILE.output,
910 pv_nl||'<br><br><font class="ASMErrorText">'
911 ||FND_MESSAGE.Get_String('SQLGL', 'GL_ASU_FATAL_ERROR')
912 ||'</font><br><br>');
913 -- Complete the HTML report
914 FND_FILE.put_line(FND_FILE.output, pv_nl||'</body></html>');
915
916 -- Log the error exit
917 GL_MESSAGE.Func_Fail(func_name => v_func_name,
918 log_level => pc_log_level_procedure,
919 module => v_module);
920 -- Set the concurrent program completion status to ERROR
921 v_return_status := FND_CONCURRENT.Set_Completion_Status
922 (status => 'ERROR', message => NULL);
923 -- Commit the changes
924 Commit;
925
926 WHEN Others THEN
927 -- <<< Unexpected database exceptions >>>
928
929 -- Set the output parameters for the concurrent program
930 x_errbuf := SQLERRM;
931 x_retcode := SQLCODE;
932
933 -- Print the error message to program log file
934 FND_FILE.put_line(FND_FILE.log, x_errbuf);
935 -- Print the error message to FND Log
936 GL_MESSAGE.Write_Fndlog_String(log_level => pc_log_leveL_unexpected,
937 module => v_module,
938 message => x_errbuf);
939
940 -- Print the fatal error message
941 FND_FILE.put_line
942 (FND_FILE.output,
943 pv_nl||'<br><br><font class="ASMErrorText">'
944 ||FND_MESSAGE.Get_String('SQLGL', 'GL_ASU_FATAL_ERROR')
945 ||'</font><br><br>');
946 -- Complete the HTML report
947 FND_FILE.put_line(FND_FILE.output, pv_nl||'</body></html>');
948
949 -- Log the error exit
950 GL_MESSAGE.Func_Fail(func_name => v_func_name,
951 log_level => pc_log_level_procedure,
952 module => v_module);
953 -- Set the concurrent program completion status to ERROR
954 v_return_status := FND_CONCURRENT.Set_Completion_Status
955 (status => 'ERROR', message => NULL);
956 -- Commit the changes
957 Commit;
958
959 END Verify_Setup;
960
961 -- PROCEDURE
962 -- Print_Table_Header()
963 --
964 -- DESCRIPTION:
965 -- This will print the passed info message and the table column text.
966 PROCEDURE Print_Table_Header( p_info_msg_name IN VARCHAR2
967 , p_column_text IN VARCHAR2) IS
968 v_func_name VARCHAR2(100);
969 v_module VARCHAR2(100);
970 BEGIN
971 -- Initialize the variables
972 v_module := 'gl.plsql.gl_as_post_upg_chk_pkg.print_table_header';
973 v_func_name := 'GL_AS_POST_UPG_CHK_PKG.Print_Table_Header';
974
975 -- Log the procedure entry
976 GL_MESSAGE.Func_Ent(func_name => v_func_name,
977 log_level => pc_log_level_procedure,
978 module => v_module);
979
980 IF (p_info_msg_name IS NOT NULL)
981 THEN
982 FND_FILE.put_line
983 (FND_FILE.output,
984 pv_nl||'<font class="ASMInfoText">'
985 ||FND_MESSAGE.Get_String('SQLGL', p_info_msg_name)
986 ||'</font><br><br>');
987 END IF;
988
989 IF (p_column_text IS NOT NULL)
990 THEN
991 FND_FILE.put_line
992 (FND_FILE.output,
993 pv_nl||'<table align="center" width="95%" border="1">');
994 FND_FILE.put_line
995 (FND_FILE.output,
996 pv_nl||'<tr align="left" valign="bottom" class="OraTableColumnHeader">');
997 FND_FILE.put_line
998 (FND_FILE.output,
999 pv_nl||p_column_text||'</tr>');
1000 END IF;
1001
1002 -- Log the success exit
1003 GL_MESSAGE.Func_Succ(func_name => v_func_name,
1004 log_level => pc_log_level_procedure,
1005 module => v_module);
1006 EXCEPTION
1007 WHEN Others THEN
1008 -- <<< Unexpected database exceptions >>>
1009
1010 -- Print the error message to program log file
1011 FND_FILE.put_line(FND_FILE.log, SQLERRM);
1012 -- Print the error message to FND Log
1013 GL_MESSAGE.Write_Fndlog_String(log_level => pc_log_leveL_unexpected,
1014 module => v_module,
1015 message => SQLERRM);
1016 -- Log the error exit
1017 GL_MESSAGE.Func_Fail(func_name => v_func_name,
1018 log_level => pc_log_level_procedure,
1019 module => v_module);
1020 RAISE PRINT_ERROR;
1021
1022 END Print_Table_Header;
1023
1024 END GL_AS_POST_UPG_CHK_PKG;