[Home] [Help]
PACKAGE BODY: APPS.OKS_IMPORT_REPORT
Source
1 PACKAGE BODY OKS_IMPORT_REPORT AS
2 -- $Header: OKSPKIMPRPTB.pls 120.1 2007/09/06 11:19:50 vmutyala noship $
3 --+=======================================================================+
4 --| Copyright (c) 2003 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| OKSPKIMPRPTB.pls Created By Vamshi Mutyala |
10 --| |
11 --| DESCRIPTION |
12 --| Service Contracts Import Statistics and Error Report Package |
13 --| |
14 --+========================================================================
15
16 --===================
17 -- GLOBALS
18 --===================
19
20 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKS_IMPORT_REPORT';
21
22 --========================================================================
23 -- PRIVATE CONSTANTS AND VARIABLES
24 --========================================================================
25 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'oks.plsql.import.' || G_PKG_NAME;
26 G_WORKER_REQ_ID CONSTANT NUMBER := FND_GLOBAL.conc_request_id;
27 G_MODULE_HEAD CONSTANT VARCHAR2(200) := G_MODULE_NAME || '(Req Id = '||G_WORKER_REQ_ID||').';
28 G_LOG_LEVEL CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
29 G_UNEXPECTED_LOG CONSTANT BOOLEAN := fnd_log.level_unexpected >= G_LOG_LEVEL AND
30 fnd_log.TEST(fnd_log.level_unexpected, G_MODULE_HEAD);
31 G_ERROR_LOG CONSTANT BOOLEAN := G_UNEXPECTED_LOG AND fnd_log.level_error >= G_LOG_LEVEL;
32 G_EXCEPTION_LOG CONSTANT BOOLEAN := G_ERROR_LOG AND fnd_log.level_exception >= G_LOG_LEVEL;
33 G_EVENT_LOG CONSTANT BOOLEAN := G_EXCEPTION_LOG AND fnd_log.level_event >= G_LOG_LEVEL;
34 G_PROCEDURE_LOG CONSTANT BOOLEAN := G_EVENT_LOG AND fnd_log.level_procedure >= G_LOG_LEVEL;
35 G_STMT_LOG CONSTANT BOOLEAN := G_PROCEDURE_LOG AND fnd_log.level_statement >= G_LOG_LEVEL;
36
37 --=========================
38 -- PROCEDURES AND FUNCTIONS
39 --=========================
40 --========================================================================
41 -- PROCEDURE : Print_Statistics PUBLIC
42 -- PARAMETERS: P_batch_id IN Batch Id
43 -- P_parent_request_id IN Import Process Request Id
44 -- COMMENT : This procedure will print statistics part of the report.
45 --=========================================================================
46 PROCEDURE Print_Statistics(P_batch_id IN VARCHAR2,
47 P_parent_request_id IN NUMBER)
48 IS
49
50 CURSOR statistics_cur (c_parent_request_id IN NUMBER,
51 c_batch_id IN NUMBER)
52 IS
53 SELECT FL.Meaning Statistic_Name,
54 INNER_Q.Headers_Stat Headers_Stat,
55 INNER_Q.Lines_Stat Lines_Stat,
56 INNER_Q.Covered_Levels_Stat Covered_Levels_Stat,
57 INNER_Q.Usage_Counters_Stat Usage_Counters_Stat,
58 INNER_Q.Sales_Credits_Stat Sales_Credits_Stat,
59 INNER_Q.Notes_Stat Notes_Stat
60 FROM FND_LOOKUPS FL,
61 (SELECT OIS.STATISTIC_TYPE_ID Statistic_type,
62 SUM(OIS.HEADERS_STAT) Headers_Stat,
63 SUM(OIS.LINES_STAT) Lines_Stat,
64 SUM(OIS.COVERED_LEVELS_STAT) Covered_Levels_Stat,
65 SUM(OIS.USAGE_COUNTERS_STAT) Usage_Counters_Stat,
66 SUM(OIS.SALES_CREDITS_STAT) Sales_Credits_Stat,
67 SUM(OIS.NOTES_STAT) Notes_Stat
68 FROM OKS_IMPORT_STATISTICS OIS
69 WHERE OIS.PARENT_REQUEST_ID = c_parent_request_id
70 AND OIS.BATCH_ID = c_batch_id
71 GROUP BY OIS.STATISTIC_TYPE_ID) INNER_Q
72 WHERE FL.LOOKUP_TYPE = 'OKS_IMPORT_STAT_TYPE'
73 AND FL.LOOKUP_CODE = INNER_Q.Statistic_type
74 ORDER BY INNER_Q.Statistic_type;
75
76 TYPE stats_table IS TABLE OF statistics_cur%ROWTYPE INDEX BY BINARY_INTEGER;
77
78 l_statistics_tab stats_table;
79 l_stmt_num NUMBER := 0;
80 l_routine CONSTANT VARCHAR2(30) := 'Print_Statistics';
81 l_stats_message VARCHAR2(200);
82 l_parent_request_id NUMBER;
83 l_loop_count NUMBER := 0;
84 BEGIN
85
86 IF G_PROCEDURE_LOG THEN
87 fnd_log.string(fnd_log.level_procedure,
88 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
89 'Entering with ' ||
90 'P_batch_id = ' || P_batch_id ||','||
91 'P_parent_request_id = ' || P_parent_request_id);
92 END IF;
93
94 l_stmt_num := 10;
95 OPEN statistics_cur(P_parent_request_id, P_batch_id);
96
97 l_stmt_num := 20;
98 FETCH statistics_cur BULK COLLECT INTO l_statistics_tab;
99
100 l_loop_count := l_statistics_tab.count;
101
102 IF l_loop_count = 0 THEN
103 RAISE G_NO_DATA_FOUND_EXC;
104 END IF;
105 FND_FILE.put_line(FND_FILE.OUTPUT, 'Interface Records Processed:');
106
107 FND_FILE.put_line(FND_FILE.OUTPUT, '___________________________________________________________________________________________________________________________');
108 FND_FILE.put_line(FND_FILE.OUTPUT, rpad('Interface Tables->', 36) ||
109 'Headers' ||
110 lpad('Lines', 10) ||
111 lpad('Covered Levels', 20) ||
112 lpad('Usage Counters', 20) ||
113 lpad('Sales Credits', 20) ||
114 lpad('Notes', 10));
115 FND_FILE.put_line(FND_FILE.OUTPUT, '___________________________________________________________________________________________________________________________');
116 FOR i IN 1..l_loop_count
117 LOOP
118 FND_FILE.put_line(FND_FILE.OUTPUT, rpad(l_statistics_tab(i).Statistic_Name, 36) ||
119 lpad(l_statistics_tab(i).Headers_Stat, 7) ||
120 lpad(l_statistics_tab(i).Lines_Stat, 10) ||
121 lpad(l_statistics_tab(i).Covered_Levels_Stat, 20) ||
122 lpad(l_statistics_tab(i).Usage_Counters_Stat, 20) ||
123 lpad(l_statistics_tab(i).Sales_Credits_Stat, 20) ||
124 lpad(l_statistics_tab(i).Notes_Stat, 10));
125 END LOOP;
126
127 FND_FILE.put_line(FND_FILE.OUTPUT, '___________________________________________________________________________________________________________________________');
128 FND_FILE.put_line(FND_FILE.OUTPUT, fnd_global.local_chr(10) || fnd_global.local_chr(10) || fnd_global.local_chr(10));
129 IF G_PROCEDURE_LOG THEN
130 fnd_log.string(fnd_log.level_procedure,
131 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
132 'Exit.');
133 END IF;
134 EXCEPTION
135 WHEN G_NO_DATA_FOUND_EXC THEN
136 RAISE G_NO_DATA_FOUND_EXC;
137 WHEN FND_API.G_EXC_ERROR THEN
138 RAISE FND_API.G_EXC_ERROR;
139 WHEN OTHERS THEN
140 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
141 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
142 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
143 FND_MSG_PUB.Add;
144 RAISE FND_API.G_EXC_ERROR;
145 END Print_Statistics;
146
147 --========================================================================
148 -- PROCEDURE : Print_Error_Messages PUBLIC
149 -- PARAMETERS: P_batch_id IN Batch Id
150 -- P_parent_request_id IN Import Process Request Id
151 -- P_mode IN Validate Only, Import flag
152 -- COMMENT : This procedure will print error messages in the report output.
153 --=========================================================================
154
155 PROCEDURE Print_Error_Messages(P_batch_id IN VARCHAR2,
156 P_parent_request_id IN NUMBER,
157 P_mode IN VARCHAR2)
158 IS
159
160 CURSOR error_messages_cur (c_parent_request_id IN NUMBER)
161 IS
162 SELECT OIE.INTERFACE_TABLE, OIE.INTERFACE_ID, nvl(FNM.MESSAGE_TEXT, OIE.ERROR_MESSAGE) ERROR_MESSAGE
163 FROM OKS_IMP_ERRORS OIE,
164 (SELECT FM.* FROM FND_NEW_MESSAGES FM, FND_APPLICATION FA
165 WHERE FM.LANGUAGE_CODE = USERENV('LANG')
166 AND FA.APPLICATION_SHORT_NAME = 'OKS' AND FM.APPLICATION_ID = FA.APPLICATION_ID) FNM
167 WHERE OIE.ERROR_MESSAGE = FNM.MESSAGE_NAME (+)
168 AND PARENT_REQUEST_ID = c_parent_request_id;
169
170 TYPE error_messages_tab IS TABLE OF error_messages_cur%rowtype INDEX BY BINARY_INTEGER;
171 l_err_msg_tab error_messages_tab;
172
173 l_stmt_num NUMBER := 0;
174 l_routine CONSTANT VARCHAR2(30) := 'Print_Error_Messages';
175 l_threshold_count NUMBER := 201;
176 l_error_count NUMBER := 0;
177 l_loop_count NUMBER := 0;
178 BEGIN
179 IF G_PROCEDURE_LOG THEN
180 fnd_log.string(fnd_log.level_procedure,
181 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
182 'Entering with ' ||
183 'P_mode = ' || P_mode ||','||
184 'P_batch_id = ' || P_batch_id ||','||
185 'P_parent_request_id = ' || P_parent_request_id);
186 END IF;
187 FND_FILE.put_line(FND_FILE.OUTPUT, 'Error Messages:');
188 FND_FILE.put_line(FND_FILE.OUTPUT, '___________________________________________________________________________________________________________________________');
189 FND_FILE.put_line(FND_FILE.OUTPUT, lpad('Interface Table', 16) ||
190 lpad('Interface Id', 28) ||
191 lpad('Error Message', 25));
192 FND_FILE.put_line(FND_FILE.OUTPUT, '___________________________________________________________________________________________________________________________');
193
194 l_stmt_num := 10;
195 OPEN error_messages_cur (P_parent_request_id);
196 l_stmt_num := 20;
197 FETCH error_messages_cur BULK COLLECT INTO l_err_msg_tab LIMIT l_threshold_count;
198
199 l_error_count := l_err_msg_tab.count;
200
201 IF l_error_count = l_threshold_count THEN
202 l_loop_count := l_error_count - 1;
203 ELSE
204 l_loop_count := l_error_count;
205 END IF;
206
207 FOR i IN 1..l_loop_count
208 LOOP
209 FND_FILE.put_line(FND_FILE.OUTPUT, rpad(l_err_msg_tab(i).INTERFACE_TABLE, 35) ||
210 rpad(l_err_msg_tab(i).INTERFACE_ID, 15) ||
211 l_err_msg_tab(i).ERROR_MESSAGE);
212 END LOOP;
213 FND_FILE.put_line(FND_FILE.OUTPUT, '___________________________________________________________________________________________________________________________');
214
215 CLOSE error_messages_cur;
216
217 IF l_error_count = l_threshold_count THEN
218 FND_MESSAGE.Set_Name('OKS', 'OKS_IMP_RPT_TOO_MANY_ERRORS');
219 FND_FILE.put_line(FND_FILE.OUTPUT, FND_MESSAGE.GET);
220 END IF;
221
222 IF G_PROCEDURE_LOG THEN
223 fnd_log.string(fnd_log.level_procedure,
224 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
225 'Exit.');
226 END IF;
227 EXCEPTION
228 WHEN FND_API.G_EXC_ERROR THEN
229 RAISE FND_API.G_EXC_ERROR;
230 WHEN OTHERS THEN
231 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
232 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
233 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
234 FND_MSG_PUB.Add;
235 RAISE FND_API.G_EXC_ERROR;
236 END Print_Error_Messages;
237
238 --========================================================================
239 -- PROCEDURE : Process_Error_Reporting PUBLIC
240 -- PARAMETERS: X_errbuf out Error message buffer
241 -- X_retcode out Return status code
242 -- P_batch_id IN Batch Id
243 -- P_parent_request_id IN Import Process Request Id
244 -- P_mode IN Validate Only, Import flag
245 -- P_Num_Workers IN Number of workers
246 -- P_commit_size IN Work unit size
247 -- COMMENT : This procedure will report statistics and errors if any in
248 -- output for a batch processed by a parent Import request.
249 --=========================================================================
250
251 PROCEDURE Process_Error_Reporting(X_errbuf OUT NOCOPY VARCHAR2,
252 X_retcode OUT NOCOPY VARCHAR2,
253 P_batch_id IN VARCHAR2,
254 P_parent_request_id IN NUMBER,
255 P_mode IN VARCHAR2,
256 P_Num_Workers IN NUMBER,
257 P_commit_size IN NUMBER)
258 IS
259 l_stmt_num NUMBER := 0;
260 l_routine CONSTANT VARCHAR2(30) := 'Process_Error_Reporting';
261 l_msg_data VARCHAR2(2000);
262 l_msg_count NUMBER;
263 l_start_date VARCHAR2(30);
264 l_end_date VARCHAR2(30);
265 l_report_date VARCHAR2(30);
266 l_mode VARCHAR2(30);
267 BEGIN
268 IF G_PROCEDURE_LOG THEN
269 fnd_log.string(fnd_log.level_procedure,
270 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
271 'Entering with ' ||
272 'P_mode = ' || P_mode ||','||
273 'P_batch_id = ' || P_batch_id ||','||
274 'P_parent_request_id = ' || P_parent_request_id ||','||
275 'P_Num_Workers = ' || P_Num_Workers ||','||
276 'P_commit_size = ' || P_commit_size );
277 END IF;
278
279 l_stmt_num := 10;
280
281 select to_char(actual_start_date, 'DD-MON-YYYY HH24:MI:SS'),
282 to_char(actual_completion_date, 'DD-MON-YYYY HH24:MI:SS'),
283 to_char(sysdate, 'DD-MON-YYYY HH24:MI')
284 into l_start_date, l_end_date, l_report_date
285 from fnd_concurrent_requests
286 where request_id = P_parent_request_id;
287
288 l_stmt_num := 20;
289
290 select meaning into l_mode from fnd_lookups where lookup_type = 'OKS_IMPORT_MODE' and lookup_code = P_mode;
291
292 FND_FILE.put_line(FND_FILE.OUTPUT, fnd_global.local_chr(10) || fnd_global.local_chr(10) || fnd_global.local_chr(10));
293 FND_FILE.put_line(FND_FILE.OUTPUT, lpad('Import Execution Report', 70) || lpad('Date: ', 32) || l_report_date);
294 FND_FILE.put_line(FND_FILE.OUTPUT, fnd_global.local_chr(10));
295 FND_FILE.put_line(FND_FILE.OUTPUT, lpad('Import Process Request Id: ', 35) || P_parent_request_id);
296 FND_FILE.put_line(FND_FILE.OUTPUT, lpad('Request Start Date: ', 35) || l_start_date);
297 FND_FILE.put_line(FND_FILE.OUTPUT, lpad('Request End Date: ', 35) || l_end_date);
298 FND_FILE.put_line(FND_FILE.OUTPUT, lpad('Batch Number: ', 35) || P_batch_id);
299 FND_FILE.put_line(FND_FILE.OUTPUT, lpad('Mode: ', 35) || l_mode);
300 FND_FILE.put_line(FND_FILE.OUTPUT, lpad('Number of Workers: ', 35) || P_Num_Workers);
301 FND_FILE.put_line(FND_FILE.OUTPUT, lpad('Commit Size : ', 35) || P_commit_size);
302 FND_FILE.put_line(FND_FILE.OUTPUT, fnd_global.local_chr(10));
303
304
305 l_stmt_num := 30;
306 Print_Statistics(P_batch_id,
307 P_parent_request_id);
308
309 l_stmt_num := 40;
310 Print_Error_Messages(P_batch_id,
311 P_parent_request_id,
312 P_mode);
313
314 IF G_PROCEDURE_LOG THEN
315 fnd_log.string(fnd_log.level_procedure,
316 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
317 'Exit with ' ||
318 'X_errbuf = ' || X_errbuf ||','||
319 'X_retcode = ' || X_retcode);
320 END IF;
321 EXCEPTION
322 WHEN G_NO_DATA_FOUND_EXC THEN
323 FND_MESSAGE.Set_Name('OKS', 'OKS_IMP_RPT_NO_DATA');
324 FND_MESSAGE.set_token('REQUEST', P_parent_request_id);
325 l_msg_data := FND_MESSAGE.GET;
326 FND_FILE.put_line(FND_FILE.OUTPUT, l_msg_data);
327 X_errbuf := l_msg_data;
328 WHEN no_data_found THEN
329 FND_MESSAGE.Set_Name('OKS', 'OKS_IMP_RPT_NO_REQUEST');
330 FND_MESSAGE.set_token('REQUEST', P_parent_request_id);
331 FND_MESSAGE.set_token('STMT', l_stmt_num);
332 l_msg_data := FND_MESSAGE.GET;
333 FND_FILE.put_line(FND_FILE.OUTPUT, l_msg_data);
334 X_errbuf := l_msg_data;
335 WHEN FND_API.G_EXC_ERROR THEN
336 FND_MSG_PUB.Count_And_Get
337 (p_encoded => FND_API.G_FALSE
338 ,p_count => l_msg_count
339 ,p_data => l_msg_data
340 );
341 X_retcode := '2';
342 X_errbuf := l_msg_data;
343
344 IF G_EXCEPTION_LOG THEN
345 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, G_MODULE_HEAD || l_routine , l_msg_data);
346 END IF;
347
348 WHEN OTHERS THEN
349 X_errbuf := 'Stmt no '||l_stmt_num||' '||SQLCODE || substr(SQLERRM, 1, 200);
350 X_retcode := '2';
351 FND_MSG_PUB.Count_And_Get
352 (p_encoded => FND_API.G_FALSE
353 ,p_count => l_msg_count
354 ,p_data => l_msg_data
355 );
356
357 IF G_EXCEPTION_LOG THEN
358 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
359 , G_MODULE_HEAD || l_routine ||'.others_exc'
360 , 'others: ' || X_errbuf || ' ' || substr(l_msg_data, 1,250)
361 );
362 END IF;
363
364 END Process_Error_Reporting;
365
366 END OKS_IMPORT_REPORT;