1 PACKAGE BODY PO_R12_CAT_UPG_UTL AS
2 /* $Header: PO_R12_CAT_UPG_UTL.plb 120.8 2006/08/02 20:22:57 pthapliy noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_R12_CAT_UPG_UTL';
5 g_module_prefix CONSTANT VARCHAR2(100) := 'po.plsql.' || g_pkg_name || '.';
6
7 g_debug BOOLEAN := PO_R12_CAT_UPG_DEBUG.is_logging_enabled;
8 g_err_num NUMBER := PO_R12_CAT_UPG_PVT.g_application_err_num;
9
10 g_base_language FND_LANGUAGES.language_code%TYPE := NULL;
11
12 --------------------------------------------------------------------------------
13 --Start of Comments
14 --Name: add_fatal_error
15 --Pre-reqs:
16 -- None
17 --Modifies:
18 -- a) PO_INTERFACE_ERRORS table
19 -- b) FND_MSG_PUB on unhandled exceptions.
20 --Locks:
21 -- None.
22 --Function:
23 -- Inserts error message into PO_INTERFACE_ERRORS in an autonomous
24 -- transaction. As per ECO bug 4927349, all messages for catalog migration will
25 -- be provided by iProcurement (product code ICX).
26 --
27 --Parameters:
28 -- Same as the PO_INTERFACE_ERRORS_SV1.handle_interface_errors() procedure.
29 --End of Comments
30 --------------------------------------------------------------------------------
31 PROCEDURE add_fatal_error
32 (
33 p_interface_header_id IN NUMBER default NULL,
34 p_interface_line_id IN NUMBER default NULL,
35 p_interface_line_location_id IN NUMBER default NULL,
36 p_interface_distribution_id IN NUMBER default NULL,
37 p_price_diff_interface_id IN NUMBER default NULL,
38 p_interface_attr_values_id IN NUMBER default NULL,
39 p_interface_attr_values_tlp_id IN NUMBER default NULL,
40 p_error_message_name IN VARCHAR2 default NULL,
41 p_table_name IN VARCHAR2 default NULL,
42 p_column_name IN VARCHAR2 default NULL,
43 p_column_value IN VARCHAR2 default NULL,
44 p_token1_name IN VARCHAR2 default NULL,
45 p_token1_value IN VARCHAR2 default NULL,
46 p_token2_name IN VARCHAR2 default NULL,
47 p_token2_value IN VARCHAR2 default NULL,
48 p_token3_name IN VARCHAR2 default NULL,
49 p_token3_value IN VARCHAR2 default NULL,
50 p_token4_name IN VARCHAR2 default NULL,
51 p_token4_value IN VARCHAR2 default NULL,
52 p_token5_name IN VARCHAR2 default NULL,
53 p_token5_value IN VARCHAR2 default NULL,
54 p_token6_name IN VARCHAR2 default NULL,
55 p_token6_value IN VARCHAR2 default NULL
56 )
57 IS
58 PRAGMA AUTONOMOUS_TRANSACTION;
59
60 l_api_name CONSTANT VARCHAR2(30) := 'add_fatal_error';
61 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
62 l_progress VARCHAR2(3) := '000';
63
64 l_hdr_proc_flag VARCHAR2(1) := 'N';
65 l_original_message VARCHAR2(2000);
66 l_error_message_text VARCHAR2(2000);
67
68 -- ECO bug 4927349: All messages for catalog migration will be
69 -- provided by iProcurement (product code ICX).
70 l_product_code CONSTANT VARCHAR2(10) := 'ICX';
71 BEGIN
72 l_progress := '010';
73 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
74
75 IF g_debug THEN
76 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'interface_type='||'PO_DOCS_OPEN_INTERFACE');
77 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'error_type='||'FATAL');
78 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'batch_id='|| PO_R12_CAT_UPG_PVT.g_job.batch_id);
79 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'interface_header_id='|| p_interface_header_id);
80 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'interface_line_id='|| p_interface_line_id);
81 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'error_message_name='|| p_error_message_name);
82 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'table_name='|| p_table_name);
83 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'column_name='|| p_column_name);
84 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'column_value='|| p_column_value);
85 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'tokenname1='|| p_token1_name);
86 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'tokenname2='|| p_token2_name);
87 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'tokenname3='|| p_token3_name);
88 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'tokenname4='|| p_token4_name);
89 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'tokenname5='|| p_token5_name);
90 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'tokenname6='|| p_token6_name);
91 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'tokenvalue1='|| p_token1_value);
92 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'tokenvalue2='|| p_token2_value);
93 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'tokenvalue3='|| p_token3_value);
94 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'tokenvalue4='|| p_token4_value);
95 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'tokenvalue5='|| p_token5_value);
96 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'tokenvalue6='|| p_token6_value);
97 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'header_processable_flag='|| l_hdr_proc_flag);
98 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'interface_dist_id='|| p_interface_distribution_id);
99 END IF;
100
101 --PO_INTERFACE_ERRORS_UTL.add_to_tbl
102 /*
103 PO_INTERFACE_ERRORS_SV1.handle_interface_errors
104 (
105 x_interface_type => 'PO_DOCS_OPEN_INTERFACE',
106 x_error_type => 'FATAL',
107 x_batch_id => PO_R12_CAT_UPG_PVT.g_job.batch_id,
108 x_interface_header_id => p_interface_header_id,
109 x_interface_line_id => p_interface_line_id,
110 x_error_message_name => p_error_message_name,
111 x_table_name => p_table_name,
112 x_column_name => p_column_name,
113 x_tokenname1 => p_token1_name,
114 x_tokenname2 => p_token2_name,
115 x_tokenname3 => p_token3_name,
116 x_tokenname4 => p_token4_name,
117 x_tokenname5 => p_token5_name,
118 x_tokenname6 => p_token6_name,
119 x_tokenvalue1 => p_token1_value,
120 x_tokenvalue2 => p_token2_value,
121 x_tokenvalue3 => p_token3_value,
122 x_tokenvalue4 => p_token4_value,
123 x_tokenvalue5 => p_token5_value,
124 x_tokenvalue6 => p_token6_value,
125 x_header_processable_flag => l_hdr_proc_flag,
126 x_interface_dist_id => p_interface_distribution_id
127 );
128 */
129
130 FND_MESSAGE.set_name(l_product_code, p_error_message_name);
131
132 IF (p_token1_name IS NOT NULL AND p_token1_value IS NOT NULL) THEN
133 FND_MESSAGE.set_token(p_token1_name, p_token1_value);
134 END IF;
135 IF (p_token2_name IS NOT NULL AND p_token2_value IS NOT NULL) THEN
136 FND_MESSAGE.set_token(p_token2_name, p_token2_value);
137 END IF;
138 IF (p_token3_name IS NOT NULL AND p_token3_value IS NOT NULL) THEN
139 FND_MESSAGE.set_token(p_token3_name, p_token3_value);
140 END IF;
141 IF (p_token4_name IS NOT NULL AND p_token4_value IS NOT NULL) THEN
142 FND_MESSAGE.set_token(p_token4_name, p_token4_value);
143 END IF;
144 IF (p_token5_name IS NOT NULL AND p_token5_value IS NOT NULL) THEN
145 FND_MESSAGE.set_token(p_token5_name, p_token5_value);
146 END IF;
147 IF (p_token6_name IS NOT NULL AND p_token6_value IS NOT NULL) THEN
148 FND_MESSAGE.set_token(p_token6_name, p_token6_value);
149 END IF;
150
151 l_original_message := FND_MESSAGE.get;
152 l_error_message_text := substrb(l_original_message, 1, 2000);
153
154 l_progress := '020';
155 INSERT INTO PO_INTERFACE_ERRORS
156 (
157 INTERFACE_TYPE,
158 INTERFACE_TRANSACTION_ID,
159 COLUMN_NAME,
160 COLUMN_VALUE,
161 TABLE_NAME,
162 ERROR_MESSAGE,
163 ERROR_MESSAGE_NAME,
164 PROCESSING_DATE,
165 CREATION_DATE,
166 CREATED_BY,
167 LAST_UPDATE_DATE,
168 LAST_UPDATED_BY,
169 LAST_UPDATE_LOGIN,
170 INTERFACE_HEADER_ID,
171 INTERFACE_LINE_ID,
172 INTERFACE_DISTRIBUTION_ID,
173 REQUEST_ID,
174 PROGRAM_APPLICATION_ID,
175 PROGRAM_ID,
176 PROGRAM_UPDATE_DATE,
177 BATCH_ID
178
179 ) VALUES (
180
181 'PO_DOCS_OPEN_INTERFACE',
182 PO_INTERFACE_ERRORS_S.nextval,
183 p_column_name,
184 substrb(p_column_value, 1, 4000),
185 p_table_name,
186 l_error_message_text,
187 p_error_message_name,
188 sysdate,
189 sysdate,
190 FND_GLOBAL.user_id,
191 sysdate,
192 FND_GLOBAL.user_id,
193 FND_GLOBAL.login_id,
194 p_interface_header_id,
195 p_interface_line_id,
196 p_interface_distribution_id,
197 FND_GLOBAL.conc_request_id,
198 FND_GLOBAL.prog_appl_id,
199 FND_GLOBAL.conc_program_id,
200 sysdate,
201 PO_R12_CAT_UPG_PVT.g_job.batch_id
202 );
203
204 l_progress := '030';
205 -- Have to commit at the end of a successful autonomous transaction
206 COMMIT;
207
208 l_progress := '040';
209 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
210 EXCEPTION
211 WHEN OTHERS THEN
212 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
213 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
214 END add_fatal_error;
215
216 --------------------------------------------------------------------------------
217 --Start of Comments
218 --Name: reject_headers_intf
219 --Pre-reqs:
220 -- None
221 --Modifies:
222 -- a) PO interface tables
223 -- b) FND_MSG_PUB on unhandled exceptions.
224 --Locks:
225 -- None.
226 --Function:
227 -- Sets the process_code as rejected and processing id as negative for the
228 -- given set of interface_header_id's
229 --
230 --Parameters:
231 --IN:
232 --p_id_param_type
233 -- Specifies if the id's are for the which level -- header/line, etc.
234 --p_id_tbl
235 -- The id's to the interface tables
236 --p_cascade IN VARCHAR2
237 -- Specifies if the errors should be cascaded down to the levels below.
238 --End of Comments
239 --------------------------------------------------------------------------------
240 PROCEDURE reject_headers_intf
241 (
242 p_id_param_type IN VARCHAR2
243 , p_id_tbl IN PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER
244 , p_cascade IN VARCHAR2
245 )
246 IS
247 l_api_name CONSTANT VARCHAR2(30) := 'reject_headers_intf';
248 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
249 l_progress VARCHAR2(3) := '000';
250 BEGIN
251 l_progress := '010';
252 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
253
254 IF (p_id_param_type = 'INTERFACE_HEADER_ID') THEN
255
256 l_progress := '020';
257 -- SQL What: Update the process_code and processing_id in the Headers
258 -- Interface Table to mark error rows.
259 -- SQL Why : So that they are not picked up from from processing in the
260 -- downstream flows.
261 -- SQL Join: interface_header_id
262 FORALL i IN 1..p_id_tbl.COUNT
263 UPDATE po_headers_interface
264 SET process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
265 --processing_id = -PO_R12_CAT_UPG_PVT.g_processing_id
266 WHERE interface_header_id = p_id_tbl(i);
267 ELSE
268 l_progress := '030';
269 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'param_type is not INTERFACE_HEADER_ID'); END IF;
270 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
271 END IF;
272
273 l_progress := '040';
274 IF (p_cascade = FND_API.G_TRUE) THEN
275 l_progress := '050';
276
277 reject_lines_intf
278 ( p_id_param_type => 'INTERFACE_HEADER_ID'
279 , p_id_tbl => p_id_tbl
280 , p_cascade => FND_API.G_TRUE
281 );
282 END IF;
283
284 l_progress := '060';
285 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
286 EXCEPTION
287 WHEN OTHERS THEN
288 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
289 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
290 END reject_headers_intf;
291
292 --------------------------------------------------------------------------------
293 --Start of Comments
294 --Name: reject_lines_intf
295 --Pre-reqs:
296 -- None
297 --Modifies:
298 -- a) PO interface tables
299 -- b) FND_MSG_PUB on unhandled exceptions.
300 --Locks:
301 -- None.
302 --Function:
303 -- Sets the process_code as rejected and processing id as negative for the
304 -- given set of interface_line_id's
305 --
306 --Parameters:
307 --IN:
308 --p_id_param_type
309 -- Specifies if the id's are for the which level -- header/line, etc.
310 --p_id_tbl
311 -- The id's to the interface tables
312 --p_cascade IN VARCHAR2
313 -- Specifies if the errors should be cascaded down to the levels below.
314 --End of Comments
315 --------------------------------------------------------------------------------
316 PROCEDURE reject_lines_intf
317 (
318 p_id_param_type IN VARCHAR2
319 , p_id_tbl IN PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER
320 , p_cascade IN VARCHAR2
321 )
322 IS
323 l_api_name CONSTANT VARCHAR2(30) := 'reject_lines_intf';
324 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
325 l_progress VARCHAR2(3) := '000';
326
327 l_intf_line_id_tbl PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
328 l_processed_intf_hdr_id_tbl PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
329 BEGIN
330 l_progress := '010';
331 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
332 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_id_param_type='||p_id_param_type); END IF;
333 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_cascade='||p_cascade); END IF;
334 IF (p_id_tbl IS NOT NULL) THEN
335 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_id_tbl.COUNT='||p_id_tbl.COUNT); END IF;
336 ELSE
337 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_id_tbl.COUNT is NULL'); END IF;
338 END IF;
339
340 IF (p_id_param_type = 'INTERFACE_HEADER_ID') THEN
341 l_progress := '020';
342 -- SQL What: Update the process_code and processing_id in the Lines
343 -- Interface Table to mark error rows.
344 -- SQL Why : So that they are not picked up from from processing in the
345 -- downstream flows.
346 -- SQL Join: interface_header_id
347 FORALL i IN 1..p_id_tbl.COUNT
348 UPDATE po_lines_interface
349 SET process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
350 --processing_id = -PO_R12_CAT_UPG_PVT.g_processing_id
351 WHERE interface_header_id = p_id_tbl(i)
352 RETURNING interface_header_id, interface_line_id
353 BULK COLLECT INTO l_processed_intf_hdr_id_tbl, l_intf_line_id_tbl;
354
355 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows REJECTED in PO_LINES_INTERFACE='||SQL%rowcount); END IF;
356 ELSIF (p_id_param_type = 'INTERFACE_LINE_ID') THEN
357 l_progress := '030';
358 -- SQL What: Update the process_code and processing_id in the Lines
359 -- Interface Table to mark error rows.
360 -- SQL Why : So that they are not picked up from from processing in the
361 -- downstream flows.
362 -- SQL Join: interface_line_id
363 FORALL i IN 1..p_id_tbl.COUNT
364 UPDATE po_lines_interface
365 SET process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
366 --processing_id = -PO_R12_CAT_UPG_PVT.g_processing_id
367 WHERE interface_line_id = p_id_tbl(i)
368 RETURNING interface_header_id, interface_line_id
369 BULK COLLECT INTO l_processed_intf_hdr_id_tbl, l_intf_line_id_tbl;
370
371 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows REJECTED in PO_LINES_INTERFACE='||SQL%rowcount); END IF;
372 ELSE
373 l_progress := '040';
374 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Invalid param_type'); END IF;
375 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
376 END IF;
377
378 l_progress := '050';
379 IF (p_cascade = FND_API.G_TRUE) THEN
380 l_progress := '060';
381 --reject_line_locations_intf
382 --( p_id_param_type => 'INTERFACE_LINE_ID'
383 --, p_id_tbl => l_intf_line_id_tbl
384 --, p_cascade => FND_API.G_TRUE);
385 --
386 --reject_price_diff_intf
387 --( p_id_param_type => 'INTERFACE_LINE_ID'
388 --, p_id_tbl => l_intf_line_id_tbl);
389
390 reject_attr_values_intf
391 ( p_id_param_type => 'INTERFACE_LINE_ID'
392 , p_id_tbl => l_intf_line_id_tbl);
393
394 l_progress := '070';
395 reject_attr_values_tlp_intf
396 ( p_id_param_type => 'INTERFACE_LINE_ID'
397 , p_id_tbl => l_intf_line_id_tbl);
398
399 l_progress := '080';
400 END IF;
401
402 l_progress := '090';
403 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
404 EXCEPTION
405 WHEN OTHERS THEN
406 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
407 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
408 END reject_lines_intf;
409
410 --------------------------------------------------------------------------------
411 --Start of Comments
412 --Name: reject_attr_values_intf
413 --Pre-reqs:
414 -- None
415 --Modifies:
416 -- a) PO interface tables
417 -- b) FND_MSG_PUB on unhandled exceptions.
418 --Locks:
419 -- None.
420 --Function:
421 -- Sets the process_code as rejected and processing id as negative for the
422 -- given set of interface_line_id's.
423 --
424 --Parameters:
425 --IN:
426 --p_id_param_type
427 -- Specifies if the id's are for the which level -- attr/tlp, etc.
428 --p_id_tbl
429 -- The id's to the interface tables
430 --End of Comments
431 --------------------------------------------------------------------------------
432 PROCEDURE reject_attr_values_intf
433 (
434 p_id_param_type IN VARCHAR2
435 , p_id_tbl IN PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER
436 )
437 IS
438 l_api_name CONSTANT VARCHAR2(30) := 'reject_attr_values_intf';
439 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
440 l_progress VARCHAR2(3) := '000';
441
442 BEGIN
443 l_progress := '010';
444 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
445 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_id_param_type='||p_id_param_type); END IF;
446 IF (p_id_tbl IS NOT NULL) THEN
447 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_id_tbl.COUNT='||p_id_tbl.COUNT); END IF;
448 ELSE
449 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_id_tbl.COUNT is NULL'); END IF;
450 END IF;
451
452 IF (p_id_param_type = 'INTERFACE_LINE_ID') THEN
453 l_progress := '020';
454
455 -- SQL What: Update the process_code and processing_id in the Attr
456 -- Interface Table to mark error rows.
457 -- SQL Why : So that they are not picked up from from processing in the
458 -- downstream flows.
459 -- SQL Join: interface_line_id
460 -- Bug 5417386: Added hint, removed join with hdr_id
461 FORALL i IN 1..p_id_tbl.COUNT
462 UPDATE /*+ INDEX(POATRI, PO_ATTR_VALUES_INT_N1) */
463 PO_ATTR_VALUES_INTERFACE POATRI
464 SET POATRI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED,
465 POATRI.processing_id = -PO_R12_CAT_UPG_PVT.g_processing_id
466 WHERE POATRI.interface_line_id = p_id_tbl(i);
467
468 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows REJECTED in PO_ATTR_VALUES_INTERFACE='||SQL%rowcount); END IF;
469 ELSIF (p_id_param_type = 'INTERFACE_ATTR_VALUES_ID') THEN
470 l_progress := '030';
471
472 -- SQL What: Update the process_code and processing_id in the Attr
473 -- Interface Table to mark error rows.
474 -- SQL Why : So that they are not picked up from from processing in the
475 -- downstream flows.
476 -- SQL Join: interface_attr_values_id
477 --
478 -- No need of hint as the join is using the primary key interface_attr_values_id.
479 FORALL i IN 1..p_id_tbl.COUNT
480 UPDATE PO_ATTR_VALUES_INTERFACE POATRI
481 SET POATRI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED,
482 POATRI.processing_id = -PO_R12_CAT_UPG_PVT.g_processing_id
483 WHERE POATRI.interface_attr_values_id = p_id_tbl(i);
484
485 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows REJECTED in PO_ATTR_VALUES_INTERFACE='||SQL%rowcount); END IF;
486 ELSE
487 l_progress := '040';
488 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Invalid param_type'); END IF;
489 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
490 END IF;
491
492 l_progress := '050';
493 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
494 EXCEPTION
495 WHEN OTHERS THEN
496 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
497 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
498 END reject_attr_values_intf;
499
500 --------------------------------------------------------------------------------
501 --Start of Comments
502 --Name: reject_attr_values_tlp_intf
503 --Pre-reqs:
504 -- None
505 --Modifies:
506 -- a) PO interface tables
507 -- b) FND_MSG_PUB on unhandled exceptions.
508 --Locks:
509 -- None.
510 --Function:
511 -- Sets the process_code as rejected and processing id as negative for the
512 -- given set of interface_line_id's
513 --
514 --Parameters:
515 --IN:
516 --p_id_param_type
517 -- Specifies if the id's are for the which level -- attr/tlp, etc.
518 --p_id_tbl
519 -- The id's to the interface tables
520 --End of Comments
521 --------------------------------------------------------------------------------
522 PROCEDURE reject_attr_values_tlp_intf
523 (
524 p_id_param_type IN VARCHAR2
525 , p_id_tbl IN PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER
526 )
527 IS
528 l_api_name CONSTANT VARCHAR2(30) := 'reject_attr_values_tlp_intf';
529 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
530 l_progress VARCHAR2(3) := '000';
531
532 BEGIN
533 l_progress := '010';
534 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
535 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_id_param_type='||p_id_param_type); END IF;
536 IF (p_id_tbl IS NOT NULL) THEN
537 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_id_tbl.COUNT='||p_id_tbl.COUNT); END IF;
538 ELSE
539 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_id_tbl.COUNT is NULL'); END IF;
540 END IF;
541
542 IF (p_id_param_type = 'INTERFACE_LINE_ID') THEN
543 l_progress := '020';
544
545 -- SQL What: Update the process_code and processing_id in the Attr TLP
546 -- Interface Table to mark error rows.
547 -- SQL Why : So that they are not picked up from from processing in the
548 -- downstream flows.
549 -- SQL Join: interface_line_id
550 -- Bug 5417386: Added hint, removed join with hdr_id
551 FORALL i IN 1..p_id_tbl.COUNT
552 UPDATE /*+ INDEX(POTLPI, PO_ATTR_VALUES_TLP_INT_N1) */
553 PO_ATTR_VALUES_TLP_INTERFACE POTLPI
554 SET POTLPI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED,
555 POTLPI.processing_id = -PO_R12_CAT_UPG_PVT.g_processing_id
556 WHERE POTLPI.interface_line_id = p_id_tbl(i);
557
558 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows REJECTED in PO_ATTR_VALUES_TLP_INTERFACE='||SQL%rowcount); END IF;
559 ELSIF (p_id_param_type = 'INTERFACE_ATTR_VALUES_TLP_ID') THEN
560 l_progress := '030';
561
562 -- SQL What: Update the process_code and processing_id in the Attr TLP
563 -- Interface Table to mark error rows.
564 -- SQL Why : So that they are not picked up from from processing in the
565 -- downstream flows.
566 -- SQL Join: interface_attr_values_tlp_id
567 --
568 -- No need of hint as the join is using the primary key interface_attr_values_tlp_id.
569 FORALL i IN 1..p_id_tbl.COUNT
570 UPDATE po_attr_values_tlp_interface
571 SET process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED,
572 processing_id = -PO_R12_CAT_UPG_PVT.g_processing_id
573 WHERE interface_attr_values_tlp_id = p_id_tbl(i);
574
575 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows REJECTED in PO_ATTR_VALUES_TLP_INTERFACE='||SQL%rowcount); END IF;
576 ELSE
577 l_progress := '040';
578 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Invalid param_type'); END IF;
579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
580 END IF;
581
582 l_progress := '050';
583 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
584 EXCEPTION
585 WHEN OTHERS THEN
586 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
587 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
588 END reject_attr_values_tlp_intf;
589
590 --------------------------------------------------------------------------------
591 --Start of Comments
592 --Name: construct_subscript_array
593 --Pre-reqs:
594 -- None
595 --Modifies:
596 -- a) FND_MSG_PUB on unhandled exceptions.
597 --Locks:
598 -- None.
599 --Function:
600 -- Constructs a plsql table of numbers of the given size. This table will be
601 -- used in ceratin queries where a subscript needed to be inserted in a
602 -- FORALL construct.
603 --
604 --Parameters:
605 --IN:
606 --p_size
607 -- The size desired for the subscript array.
608 --End of Comments
609 --------------------------------------------------------------------------------
610 FUNCTION construct_subscript_array
611 (
612 p_size IN NUMBER
613 )
614 RETURN PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER
615 IS
616 l_api_name CONSTANT VARCHAR2(30) := 'construct_subscript_array';
617 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
618 l_progress VARCHAR2(3) := '000';
619
620 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
621 i NUMBER;
622 BEGIN
623 l_progress := '010';
624 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
625
626 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_size='||p_size); END IF;
627
628 FOR i IN 1 .. p_size
629 LOOP
630 l_progress := '020';
631 l_subscript_array(i) := i;
632 END LOOP;
633
634 l_progress := '030';
635 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
636
637 RETURN l_subscript_array;
638 EXCEPTION
639 WHEN OTHERS THEN
640 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
641 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
642 END construct_subscript_array;
643
644 --------------------------------------------------------------------------------
645 --Start of Comments
646 --Name: assign_processing_id
647 --Pre-reqs:
648 -- The PO_R12_CAT_UPG_PVT.g_job structure is populated with valid
649 -- data.
650 --Modifies:
651 -- a) PO interface tables
652 -- b) FND_MSG_PUB on unhandled exceptions.
653 --Locks:
654 -- None.
655 --Function:
656 -- Populates the processing_id column on the interface table for a given
657 -- batch_id, org_id, process_code and document_type combination.
658 --
659 --Parameters:
660 --IN:
661 -- None
662 --End of Comments
663 --------------------------------------------------------------------------------
664 PROCEDURE assign_processing_id
665 IS
666 l_api_name CONSTANT VARCHAR2(30) := 'assign_processing_id';
667 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
668 l_progress VARCHAR2(3) := '000';
669
670 -- SQL What: Cursor to pick up rows from the headers interface table based
671 -- on batch_id, org_id, process_code and document_type combination.
672 -- Does not pick up those rows that have been processed already
673 -- (where processing_id is NOT NULL).
674 -- SQL Why : To populate the processing_id column so that these rows are
675 -- picked up for processing.
676 -- SQL Join: batch_id, org_id, process_code, document_type.
677 CURSOR interface_headers_csr IS
678 SELECT PHI.interface_header_id
679 FROM po_headers_interface PHI
680 WHERE PHI.batch_id = PO_R12_CAT_UPG_PVT.g_job.batch_id
681 AND (PHI.org_id = PO_R12_CAT_UPG_PVT.g_job.org_id OR
682 PO_R12_CAT_UPG_PVT.g_job.org_id IS NULL)
683 AND PHI.processing_id IS NULL
684 AND PHI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_NEW;
685
686 l_intf_header_id_tbl PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
687 l_intf_line_id_tbl PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
688
689 l_processed_intf_hdr_id_tbl PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
690 BEGIN
691 l_progress := '010';
692 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
693
694 OPEN interface_headers_csr;
695
696 l_progress := '020';
697 LOOP
698 BEGIN -- block to handle SNAPSHOT_TOO_OLD exception
699 l_progress := '030';
700
701 FETCH interface_headers_csr
702 BULK COLLECT INTO l_intf_header_id_tbl
703 LIMIT PO_R12_CAT_UPG_PVT.g_job.batch_size;
704
705 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'PO_R12_CAT_UPG_PVT.g_processing_id='||PO_R12_CAT_UPG_PVT.g_processing_id); END IF;
706 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_intf_header_id_tbl.COUNT='||l_intf_header_id_tbl.COUNT); END IF;
707
708 l_progress := '040';
709 EXIT WHEN l_intf_header_id_tbl.COUNT = 0;
710
711 l_progress := '050';
712 -- SQL What: Assign processing_id to Headers Interface table rows.
713 -- SQL Why : So that these rows are picked up for processing.
714 -- SQL Join: interface_header_id
715 FORALL i IN 1..l_intf_header_id_tbl.COUNT
716 UPDATE po_headers_interface
717 SET processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
718 WHERE interface_header_id = l_intf_header_id_tbl(i)
719 AND processing_id IS NULL;
720
721 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows updated in Headers Interface='||SQL%rowcount); END IF;
722
723 l_progress := '060';
724 -- SQL What: Assign processing_id to Lines Interface table rows.
725 -- SQL Why : So that these rows are picked up for processing.
726 -- SQL Join: interface_header_id
727 FORALL i IN 1..l_intf_header_id_tbl.COUNT
728 UPDATE po_lines_interface
729 SET processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
730 WHERE interface_header_id = l_intf_header_id_tbl(i)
731 AND processing_id IS NULL
732 RETURNING interface_header_id, interface_line_id
733 BULK COLLECT INTO l_processed_intf_hdr_id_tbl, l_intf_line_id_tbl;
734
735 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows updated in Lines Interface='||SQL%rowcount); END IF;
736
737 l_progress := '070';
738 --FORALL i IN 1..l_intf_line_id_tbl.COUNT
739 -- UPDATE po_line_locations_interface
740 -- SET processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
741 -- WHERE interface_line_id = l_intf_line_id_tbl(i);
742
743 --FORALL i IN 1..l_intf_line_id_tbl.COUNT
744 -- UPDATE po_price_diff_interface
745 -- SET processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
746 -- WHERE interface_line_id = l_intf_line_id_tbl(i);
747
748 --IF (PO_R12_CAT_UPG_PVT.g_job.document_type =
749 -- PO_R12_CAT_UPG_PVT.g_DOC_TYPE_STANDARD) THEN
750 -- FORALL i IN 1..l_intf_line_id_tbl.COUNT
751 -- UPDATE po_distributions_interface
752 -- SET processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
753 -- WHERE interface_line_id = l_intf_line_id_tbl(i);
754 --END IF;
755
756 l_progress := '080';
757 -- SQL What: Assign processing_id to Attr Interface table rows.
758 -- SQL Why : So that these rows are picked up for processing.
759 -- SQL Join: interface_line_id
760 FORALL i IN 1..l_intf_line_id_tbl.COUNT
761 UPDATE /*+ INDEX(POATRI, PO_ATTR_VALUES_INT_N1) */
762 PO_ATTR_VALUES_INTERFACE POATRI
763 SET POATRI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
764 WHERE POATRI.interface_line_id = l_intf_line_id_tbl(i)
765 -- Bug 5345544: Start
766 -- Bug 5417386: Not required after index column re-ordering
767 --AND POATRI.interface_header_id = l_processed_intf_hdr_id_tbl(i)
768 -- Bug 5345544: End
769 AND POATRI.processing_id IS NULL;
770
771 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows updated in Attr Interface='||SQL%rowcount); END IF;
772
773 l_progress := '090';
774 -- SQL What: Assign processing_id to Attr TLP Interface table rows.
775 -- SQL Why : So that these rows are picked up for processing.
776 -- SQL Join: interface_line_id
777 -- Bug 5417386: Added hint
778 FORALL i IN 1..l_intf_line_id_tbl.COUNT
779 UPDATE /*+ INDEX(POTLPI, PO_ATTR_VALUES_TLP_INT_N1) */
780 PO_ATTR_VALUES_TLP_INTERFACE POTLPI
781 SET POTLPI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
782 WHERE POTLPI.interface_line_id = l_intf_line_id_tbl(i)
783 -- Bug 5345544: Start
784 -- Bug 5417386: Not required after index column re-ordering
785 --AND POTLPI.interface_header_id = l_processed_intf_hdr_id_tbl(i)
786 -- Bug 5345544: End
787 AND POTLPI.processing_id IS NULL;
788
789 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows updated in TLP Interface='||SQL%rowcount); END IF;
790
791 COMMIT;
792 EXCEPTION
793 WHEN g_SNAPSHOT_TOO_OLD THEN
794 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'EXCEPTION: SNAPSHOT_TOO_OLD. Now commiting and re-opening the interface_headers_csr'); END IF;
795
796 -- Commit and re-open the cursor
797 l_progress := '080';
798 COMMIT;
799
800 l_progress := '090';
801 CLOSE interface_headers_csr;
802
803 l_progress := '100';
804 OPEN interface_headers_csr;
805 l_progress := '110';
806 END; -- block to handle SNAPSHOT_TOO_OLD exception
807 END LOOP;
808
809 l_progress := '100';
810 IF (interface_headers_csr%ISOPEN) THEN
811 CLOSE interface_headers_csr;
812 END IF;
813
814 l_progress := '110';
815 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
816 EXCEPTION
817 WHEN OTHERS THEN
818 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
819 IF (interface_headers_csr%ISOPEN) THEN
820 CLOSE interface_headers_csr;
821 END IF;
822 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
823 END assign_processing_id;
824
825 PROCEDURE init_sys_parameters;
826
827 --------------------------------------------------------------------------------
828 --Start of Comments
829 --Name: init_startup_values
830 --Pre-reqs:
831 -- None
832 --Modifies:
833 -- a) FND_MSG_PUB on unhandled exceptions.
834 --Locks:
835 -- None.
836 --Function:
837 -- Populates the PO_R12_CAT_UPG_PVT.g_job structure using the
838 -- input parameters. Also fetches the next processing_id from the sequence.
839 --
840 --Parameters:
841 --IN:
842 --
843 --End of Comments
844 --------------------------------------------------------------------------------
845 PROCEDURE init_startup_values
846 (
847 p_commit IN VARCHAR2,
848 p_selected_batch_id IN NUMBER,
849 p_batch_size IN NUMBER,
850 p_buyer_id IN NUMBER,
851 p_document_type IN VARCHAR2,
852 p_document_subtype IN VARCHAR2,
853 p_create_items IN VARCHAR2,
854 p_create_sourcing_rules_flag IN VARCHAR2,
855 p_rel_gen_method IN VARCHAR2,
856 p_approved_status IN VARCHAR2,
857 p_process_code IN VARCHAR2,
858 p_interface_header_id IN NUMBER,
859 p_org_id IN NUMBER,
860 p_ga_flag IN VARCHAR2,
861 p_role IN VARCHAR2,
862 p_error_threshold IN NUMBER,
863 p_validate_only_mode IN VARCHAR2
864 )
865 IS
866 l_api_name CONSTANT VARCHAR2(30) := 'init_startup_values';
867 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
868 l_progress VARCHAR2(3) := '000';
869 BEGIN
870 l_progress := '010';
871 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
872
873 l_progress := '020';
874 -- Setup g_job param
875 PO_R12_CAT_UPG_PVT.g_job.commit_work := p_commit;
876 PO_R12_CAT_UPG_PVT.g_job.batch_id := p_selected_batch_id;
877 PO_R12_CAT_UPG_PVT.g_job.batch_size := p_batch_size;
878 PO_R12_CAT_UPG_PVT.g_job.buyer_id := p_buyer_id;
879 PO_R12_CAT_UPG_PVT.g_job.document_type := p_document_type;
880 PO_R12_CAT_UPG_PVT.g_job.document_subtype := p_document_subtype;
881 PO_R12_CAT_UPG_PVT.g_job.error_threshold := p_error_threshold;
882 PO_R12_CAT_UPG_PVT.g_job.validate_only_mode := p_validate_only_mode;
883
884 IF g_debug THEN
885 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Job param commit_work='||PO_R12_CAT_UPG_PVT.g_job.commit_work);
886 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Job param batch_id='||PO_R12_CAT_UPG_PVT.g_job.batch_id);
887 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Job param batch_size='||PO_R12_CAT_UPG_PVT.g_job.batch_size);
888 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Job param buyer_id='||PO_R12_CAT_UPG_PVT.g_job.buyer_id);
889 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Job param document_type='||PO_R12_CAT_UPG_PVT.g_job.document_type);
890 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Job param document_subtype='||PO_R12_CAT_UPG_PVT.g_job.document_subtype);
891 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Job param error_threshold='||PO_R12_CAT_UPG_PVT.g_job.error_threshold);
892 END IF;
893
894 l_progress := '030';
895 -- Setup g_sys param
896 init_sys_parameters;
897
898 -- Setup profile param
899 --init_profile_parameters;
900
901 -- setup g_out param
902 --PO_R12_CAT_UPG_PVT.g_out.processed_rec_count := 0;
903 --PO_R12_CAT_UPG_PVT.g_out.rejected_rec_count := 0;
904 --PO_R12_CAT_UPG_PVT.g_out.error_tolerance_exceeded := FND_API.G_FALSE;
905
906 l_progress := '040';
907 -- SQL What: Default processing_id from sequence
908 -- SQL Why : To assign processing id's to the interface table rows.
909 -- SQL Join: none
910 SELECT PO_PDOI_PROCESSING_ID_S.nextval
911 INTO PO_R12_CAT_UPG_PVT.g_processing_id
912 FROM DUAL;
913
914 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'processing_id='||PO_R12_CAT_UPG_PVT.g_processing_id); END IF;
915
916 l_progress := '050';
917 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
918 EXCEPTION
919 WHEN OTHERS THEN
920 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
921 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
922 END init_startup_values;
923
924 --------------------------------------------------------------------------------
925 --Start of Comments
926 --Name: init_sys_parameters
927 --Pre-reqs:
928 -- None
929 --Modifies:
930 -- a) PO_R12_CAT_UPG_PVT.g_sys structure.
931 -- b) FND_MSG_PUB on unhandled exceptions.
932 --Locks:
933 -- None.
934 --Function:
935 -- Populates the PO_R12_CAT_UPG_PVT.g_sys structure by calling
936 -- PO_CORE_S.get_po_parameters() procedure.
937 --
938 --Parameters:
939 --IN:
940 --
941 --End of Comments
942 --------------------------------------------------------------------------------
943 PROCEDURE init_sys_parameters
944 IS
945 l_api_name CONSTANT VARCHAR2(30) := 'init_sys_parameters';
946 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
947 l_progress VARCHAR2(3) := '000';
948 BEGIN
949 l_progress := '010';
950 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
951
952 l_progress := '020';
953
954 -- SQL What: Get the OU specific PO and Financials setup parameters
955 -- SQL Why : These values will be used in defaulting/validations
956 -- SQL Join: org_id, set_of_books_id, inventory_organization_id
957
958 -- NOTE: We are not using PO_CORE_S.get_po_parameters() because it depends on
959 -- the org context.
960 SELECT
961 sob.currency_code,
962 sob.chart_of_accounts_id,
963 nvl(FSPA.purch_encumbrance_flag,'N'),
964 nvl(FSPA.req_encumbrance_flag,'N'),
965 sob.set_of_books_id,
966 FSPA.ship_to_location_id,
967 FSPA.bill_to_location_id,
968 FSPA.fob_lookup_code,
969 FSPA.freight_terms_lookup_code,
970 FSPA.terms_id,
971 PSPA.default_rate_type,
972 null,--PSPA.taxable_flag,
973 PSPA.receiving_flag,
974 nvl(PSPA.enforce_buyer_name_flag, 'N'),
975 nvl(PSPA.enforce_buyer_authority_flag,'N'),
976 PSPA.line_type_id,
977 PSPA.manual_po_num_type,
978 PSPA.user_defined_po_num_code,
979 PSPA.price_type_lookup_code,
980 PSPA.invoice_close_tolerance,
981 PSPA.receive_close_tolerance,
982 PSPA.security_position_structure_id,
983 PSPA.expense_accrual_code,
984 FSPA.inventory_organization_id,
985 FSPA.revision_sort_ordering,
986 PSPA.min_release_amount,
987 nvl(PSPA.notify_if_blanket_flag,'N'),
988 nvl(sob.enable_budgetary_control_flag,'N'),
989 PSPA.user_defined_req_num_code,
990 nvl(PSPA.rfq_required_flag,'N'),
991 PSPA.manual_req_num_type,
992 PSPA.enforce_full_lot_quantities,
993 PSPA.disposition_warning_flag,
994 nvl(FSPA.reserve_at_completion_flag,'N'),
995 PSPA.user_defined_receipt_num_code,
996 PSPA.manual_receipt_num_type,
997 FSPA.use_positions_flag,
998 PSPA.default_quote_warning_delay,
999 PSPA.inspection_required_flag,
1000 PSPA.user_defined_quote_num_code,
1001 PSPA.manual_quote_num_type,
1002 PSPA.user_defined_rfq_num_code,
1003 PSPA.manual_rfq_num_type,
1004 FSPA.ship_via_lookup_code,
1005 rcv.qty_rcv_tolerance
1006 INTO
1007 PO_R12_CAT_UPG_PVT.g_sys.currency_code,
1008 PO_R12_CAT_UPG_PVT.g_sys.coa_id,
1009 PO_R12_CAT_UPG_PVT.g_sys.po_encumberance_flag,
1010 PO_R12_CAT_UPG_PVT.g_sys.req_encumberance_flag,
1011 PO_R12_CAT_UPG_PVT.g_sys.sob_id,
1012 PO_R12_CAT_UPG_PVT.g_sys.ship_to_location_id,
1013 PO_R12_CAT_UPG_PVT.g_sys.bill_to_location_id,
1014 PO_R12_CAT_UPG_PVT.g_sys.fob_lookup_code,
1015 PO_R12_CAT_UPG_PVT.g_sys.freight_terms_lookup_code,
1016 PO_R12_CAT_UPG_PVT.g_sys.terms_id,
1017 PO_R12_CAT_UPG_PVT.g_sys.default_rate_type,
1018 PO_R12_CAT_UPG_PVT.g_sys.taxable_flag,
1019 PO_R12_CAT_UPG_PVT.g_sys.receiving_flag,
1020 PO_R12_CAT_UPG_PVT.g_sys.enforce_buyer_name_flag,
1021 PO_R12_CAT_UPG_PVT.g_sys.enforce_buyer_auth_flag,
1022 PO_R12_CAT_UPG_PVT.g_sys.line_type_id,
1023 PO_R12_CAT_UPG_PVT.g_sys.manual_po_num_type,
1024 PO_R12_CAT_UPG_PVT.g_sys.po_num_code,
1025 PO_R12_CAT_UPG_PVT.g_sys.price_lookup_code,
1026 PO_R12_CAT_UPG_PVT.g_sys.invoice_close_tolerance,
1027 PO_R12_CAT_UPG_PVT.g_sys.receive_close_tolerance,
1028 PO_R12_CAT_UPG_PVT.g_sys.security_structure_id,
1029 PO_R12_CAT_UPG_PVT.g_sys.expense_accrual_code,
1030 PO_R12_CAT_UPG_PVT.g_sys.inv_org_id,
1031 PO_R12_CAT_UPG_PVT.g_sys.rev_sort_ordering,
1032 PO_R12_CAT_UPG_PVT.g_sys.min_rel_amount,
1033 PO_R12_CAT_UPG_PVT.g_sys.notify_blanket_flag,
1034 PO_R12_CAT_UPG_PVT.g_sys.budgetary_control_flag,
1035 PO_R12_CAT_UPG_PVT.g_sys.user_defined_req_num_code,
1036 PO_R12_CAT_UPG_PVT.g_sys.rfq_required_flag,
1037 PO_R12_CAT_UPG_PVT.g_sys.manual_req_num_type,
1038 PO_R12_CAT_UPG_PVT.g_sys.enforce_full_lot_qty,
1039 PO_R12_CAT_UPG_PVT.g_sys.disposition_warning_flag,
1040 PO_R12_CAT_UPG_PVT.g_sys.reserve_at_completion_flag,
1041 PO_R12_CAT_UPG_PVT.g_sys.user_defined_rcpt_num_code,
1042 PO_R12_CAT_UPG_PVT.g_sys.manual_rcpt_num_type,
1043 PO_R12_CAT_UPG_PVT.g_sys.use_positions_flag,
1044 PO_R12_CAT_UPG_PVT.g_sys.default_quote_warning_delay,
1045 PO_R12_CAT_UPG_PVT.g_sys.inspection_required_flag,
1046 PO_R12_CAT_UPG_PVT.g_sys.user_defined_quote_num_code,
1047 PO_R12_CAT_UPG_PVT.g_sys.manual_quote_num_type,
1048 PO_R12_CAT_UPG_PVT.g_sys.user_defined_rfq_num_code,
1049 PO_R12_CAT_UPG_PVT.g_sys.manual_rfq_num_type,
1050 PO_R12_CAT_UPG_PVT.g_sys.ship_via_lookup_code,
1051 PO_R12_CAT_UPG_PVT.g_sys.qty_rcv_tolerance
1052 FROM FINANCIALS_SYSTEM_PARAMS_ALL FSPA,
1053 GL_SETS_OF_BOOKS SOB,
1054 PO_SYSTEM_PARAMETERS_ALL PSPA,
1055 RCV_PARAMETERS RCV
1056 WHERE FSPA.set_of_books_id = SOB.set_of_books_id
1057 AND RCV.organization_id (+) = FSPA.inventory_organization_id
1058 AND PSPA.org_id = PO_R12_CAT_UPG_PVT.g_job.org_id
1059 AND FSPA.org_id = PO_R12_CAT_UPG_PVT.g_job.org_id;
1060
1061 SELECT master_organization_id
1062 INTO PO_R12_CAT_UPG_PVT.g_sys.master_inv_org_id
1063 FROM MTL_PARAMETERS
1064 WHERE organization_id = PO_R12_CAT_UPG_PVT.g_sys.inv_org_id;
1065
1066 IF g_debug THEN
1067 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default currency_code='||PO_R12_CAT_UPG_PVT.g_sys.currency_code);
1068 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default coa_id='||PO_R12_CAT_UPG_PVT.g_sys.coa_id);
1069 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default po_encumberance_flag='||PO_R12_CAT_UPG_PVT.g_sys.po_encumberance_flag);
1070 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default req_encumberance_flag='||PO_R12_CAT_UPG_PVT.g_sys.req_encumberance_flag);
1071 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default sob_id='||PO_R12_CAT_UPG_PVT.g_sys.sob_id);
1072 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default ship_to_location_id='||PO_R12_CAT_UPG_PVT.g_sys.ship_to_location_id);
1073 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default bill_to_location_id='||PO_R12_CAT_UPG_PVT.g_sys.bill_to_location_id);
1074 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default fob_lookup_code='||PO_R12_CAT_UPG_PVT.g_sys.fob_lookup_code);
1075 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default freight_terms_lookup_code='||PO_R12_CAT_UPG_PVT.g_sys.freight_terms_lookup_code);
1076 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default terms_id='||PO_R12_CAT_UPG_PVT.g_sys.terms_id);
1077 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default default_rate_type='||PO_R12_CAT_UPG_PVT.g_sys.default_rate_type);
1078 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default taxable_flag='||PO_R12_CAT_UPG_PVT.g_sys.taxable_flag);
1079 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default receiving_flag='||PO_R12_CAT_UPG_PVT.g_sys.receiving_flag);
1080 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default enforce_buyer_name_flag='||PO_R12_CAT_UPG_PVT.g_sys.enforce_buyer_name_flag);
1081 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default enforce_buyer_auth_flag='||PO_R12_CAT_UPG_PVT.g_sys.enforce_buyer_auth_flag);
1082 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default line_type_id='||PO_R12_CAT_UPG_PVT.g_sys.line_type_id);
1083 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default manual_po_num_type='||PO_R12_CAT_UPG_PVT.g_sys.manual_po_num_type);
1084 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default po_num_code='||PO_R12_CAT_UPG_PVT.g_sys.po_num_code);
1085 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default price_lookup_code='||PO_R12_CAT_UPG_PVT.g_sys.price_lookup_code);
1086 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default invoice_close_tolerance='||PO_R12_CAT_UPG_PVT.g_sys.invoice_close_tolerance);
1087 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default receive_close_tolerance='||PO_R12_CAT_UPG_PVT.g_sys.receive_close_tolerance);
1088 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default security_structure_id='||PO_R12_CAT_UPG_PVT.g_sys.security_structure_id);
1089 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default expense_accrual_code='||PO_R12_CAT_UPG_PVT.g_sys.expense_accrual_code);
1090 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default inv_org_id='||PO_R12_CAT_UPG_PVT.g_sys.inv_org_id);
1091 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default rev_sort_ordering='||PO_R12_CAT_UPG_PVT.g_sys.rev_sort_ordering);
1092 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default min_rel_amount='||PO_R12_CAT_UPG_PVT.g_sys.min_rel_amount);
1093 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default notify_blanket_flag='||PO_R12_CAT_UPG_PVT.g_sys.notify_blanket_flag);
1094 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default budgetary_control_flag='||PO_R12_CAT_UPG_PVT.g_sys.budgetary_control_flag);
1095 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default user_defined_req_num_code='||PO_R12_CAT_UPG_PVT.g_sys.user_defined_req_num_code);
1096 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default rfq_required_flag='||PO_R12_CAT_UPG_PVT.g_sys.rfq_required_flag);
1097 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default manual_req_num_type='||PO_R12_CAT_UPG_PVT.g_sys.manual_req_num_type);
1098 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default enforce_full_lot_qty='||PO_R12_CAT_UPG_PVT.g_sys.enforce_full_lot_qty);
1099 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default disposition_warning_flag='||PO_R12_CAT_UPG_PVT.g_sys.disposition_warning_flag);
1100 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default reserve_at_completion_flag='||PO_R12_CAT_UPG_PVT.g_sys.reserve_at_completion_flag);
1101 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default user_defined_rcpt_num_code='||PO_R12_CAT_UPG_PVT.g_sys.user_defined_rcpt_num_code);
1102 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default manual_rcpt_num_type='||PO_R12_CAT_UPG_PVT.g_sys.manual_rcpt_num_type);
1103 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default use_positions_flag='||PO_R12_CAT_UPG_PVT.g_sys.use_positions_flag);
1104 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default default_quote_warning_delay='||PO_R12_CAT_UPG_PVT.g_sys.default_quote_warning_delay);
1105 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default inspection_required_flag='||PO_R12_CAT_UPG_PVT.g_sys.inspection_required_flag);
1106 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default user_defined_quote_num_code='||PO_R12_CAT_UPG_PVT.g_sys.user_defined_quote_num_code);
1107 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default manual_quote_num_type='||PO_R12_CAT_UPG_PVT.g_sys.manual_quote_num_type);
1108 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default user_defined_rfq_num_code='||PO_R12_CAT_UPG_PVT.g_sys.user_defined_rfq_num_code);
1109 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default manual_rfq_num_type='||PO_R12_CAT_UPG_PVT.g_sys.manual_rfq_num_type);
1110 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default ship_via_lookup_code='||PO_R12_CAT_UPG_PVT.g_sys.ship_via_lookup_code);
1111 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default qty_rcv_tolerance='||PO_R12_CAT_UPG_PVT.g_sys.qty_rcv_tolerance);
1112 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Default master_inv_org_id='||PO_R12_CAT_UPG_PVT.g_sys.master_inv_org_id);
1113 END IF;
1114
1115 -- We do not need the Receiving parameters in Catalog Migration.
1116 -- So commenting out the following code
1117
1118 --SELECT master_organization_id
1119 --INTO PO_R12_CAT_UPG_PVT.g_sys_param.master_inv_org_id
1120 --FROM mtl_system_parameters
1121 --WHERE organization_id = PO_R12_CAT_UPG_PVT.g_sys.def_inv_org_id;
1122
1123 -- receiving parameters for defaulting org
1124 --RCV_CORE_S.get_receiving_controls
1125 --( x_line_loc_id => NULL,
1126 -- x_item_id => NULL,
1127 -- x_vendor_id => NULL,
1128 -- x_org_id => PO_R12_CAT_UPG_PVT.g_sys.def_inv_org_id,
1129 -- x_enforce_ship_to_loc => PO_R12_CAT_UPG_PVT.g_sys.enforce_ship_to_loc,
1130 -- x_allow_substitutes => PO_R12_CAT_UPG_PVT.g_sys.allow_substitutes,
1131 -- x_routing_id => PO_R12_CAT_UPG_PVT.g_sys.routing_id,
1132 -- x_qty_rcv_tolerance => PO_R12_CAT_UPG_PVT.g_sys.qty_rcv_tolerance,
1133 -- x_qty_rcv_exception => PO_R12_CAT_UPG_PVT.g_sys.qty_rcv_exception,
1134 -- x_days_early_receipt => PO_R12_CAT_UPG_PVT.g_sys.days_early_receipt,
1135 -- x_days_late_receipt => PO_R12_CAT_UPG_PVT.g_sys.days_late_receipt,
1136 -- x_rcv_date_exception => PO_R12_CAT_UPG_PVT.g_sys.rcv_date_exception
1137 --);
1138
1139 l_progress := '030';
1140 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1141 EXCEPTION
1142 WHEN OTHERS THEN
1143 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1144 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1145 END init_sys_parameters;
1146
1147 --------------------------------------------------------------------------------
1148 --Start of Comments
1149 --Name: get_base_lang
1150 --Pre-reqs:
1151 -- None
1152 --Modifies:
1153 -- a) FND_MSG_PUB on unhandled exceptions.
1154 --Locks:
1155 -- None.
1156 --Function:
1157 -- Gets the Base Language of the installed system.
1158 --Parameters:
1159 --IN:
1160 -- None
1161 --RETURN:
1162 -- VARCHAR2 -- the base languages of the system.
1163 --End of Comments
1164 --------------------------------------------------------------------------------
1165 FUNCTION get_base_lang
1166 RETURN VARCHAR2
1167 IS
1168 l_api_name CONSTANT VARCHAR2(30) := 'get_base_lang';
1169 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
1170 l_progress VARCHAR2(3) := '000';
1171 BEGIN
1172 l_progress := '010';
1173 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1174
1175 l_progress := '020';
1176 IF (g_base_language IS NULL) THEN
1177 -- SQL What: Get the base language of the system installation.
1178 -- SQL Why : Will be used to populate the created_language column
1179 -- SQL Join: installed_flag
1180 SELECT language_code
1181 INTO g_base_language
1182 FROM FND_LANGUAGES
1183 WHERE installed_flag='B';
1184 END IF;
1185
1186 l_progress := '030';
1187 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'base_lang=<'||g_base_language||'>'); END IF;
1188
1189 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1190 RETURN g_base_language;
1191 EXCEPTION
1192 WHEN OTHERS THEN
1193 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1194 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1195 END get_base_lang;
1196
1197 --------------------------------------------------------------------------------
1198 --Start of Comments
1199 --Name: get_num_languages
1200 --Pre-reqs:
1201 -- None
1202 --Modifies:
1203 -- a) FND_MSG_PUB on unhandled exceptions.
1204 --Locks:
1205 -- None.
1206 --Function:
1207 -- Gets the number of installed languages(+base lang)
1208 --Parameters:
1209 --IN:
1210 -- None
1211 --RETURN:
1212 -- NUMBER -- the number of installed languages.
1213 --End of Comments
1214 --------------------------------------------------------------------------------
1215 FUNCTION get_num_languages
1216 RETURN NUMBER
1217 IS
1218 l_api_name CONSTANT VARCHAR2(30) := 'get_num_languages';
1219 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
1220 l_progress VARCHAR2(3) := '000';
1221
1222 l_num_languages NUMBER := 1;
1223 BEGIN
1224 l_progress := '010';
1225 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1226
1227 -- SQL What: Get the number of installed languages in the system.
1228 -- SQL Why : Will be used to populate TLP records.
1229 -- SQL Join: installed_flag
1230 SELECT count(*)
1231 INTO l_num_languages
1232 FROM FND_LANGUAGES
1233 WHERE installed_flag IN ('B', 'I');
1234
1235 l_progress := '020';
1236 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'num_languages='||l_num_languages); END IF;
1237
1238 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1239 RETURN l_num_languages;
1240 EXCEPTION
1241 WHEN OTHERS THEN
1242 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1243 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1244 END get_num_languages;
1245
1246 END PO_R12_CAT_UPG_UTL;