eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PO_R12_CAT_UPG_UTL

Source


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;