[Home] [Help]
PACKAGE BODY: APPS.ICX_CAT_UTIL_PVT
Source
1 PACKAGE BODY ICX_CAT_UTIL_PVT AS
2 /* $Header: ICXVUTLB.pls 120.18 2006/07/01 00:10:52 kaholee noship $*/
3
4 -- Constants
5 G_PKG_NAME CONSTANT VARCHAR2(30) :='ICX_CAT_UTIL_PVT';
6
7 g_log_module_prefix VARCHAR2(10) := 'icx.plsql.';
8 g_log_module_seperator VARCHAR2(1) := '.';
9 g_log_module_begin VARCHAR2(5) := 'begin';
10 g_log_module_end VARCHAR2(3) := 'end';
11
12 -- function to get the apps schema name
13 FUNCTION getAppsSchemaName
14 RETURN VARCHAR2
15 IS
16 l_err_loc PLS_INTEGER;
17 BEGIN
18 l_err_loc := 100;
19 IF (g_apps_schema_name IS NULL)
20 THEN
21 l_err_loc := 200;
22 SELECT oracle_username
23 INTO g_apps_schema_name
24 FROM fnd_oracle_userid
25 WHERE read_only_flag = 'U';
26 END IF;
27 l_err_loc := 300;
28 RETURN g_apps_schema_name;
29 EXCEPTION
30 WHEN OTHERS THEN
31 l_err_loc := 400;
32 RETURN 'APPS';
33 END getAppsSchemaName;
34
35 -- function to get the icx schema name
36 FUNCTION getIcxSchemaName
37 RETURN VARCHAR2
38 IS
39 l_status VARCHAR2(20);
40 l_industry VARCHAR2(20);
41 l_icx_schema_name VARCHAR2(20) := 'ICX';
42 l_err_loc PLS_INTEGER;
43 BEGIN
44 l_err_loc := 100;
45 IF (g_icx_schema_name IS NOT NULL OR
46 FND_INSTALLATION.GET_APP_INFO('ICX', l_status,
47 l_industry, g_icx_schema_name))
48 THEN
49 l_err_loc := 200;
50 l_icx_schema_name := g_icx_schema_name;
51 END IF;
52 l_err_loc := 300;
53 RETURN l_icx_schema_name;
54 END getIcxSchemaName;
55
56 FUNCTION getModuleNameForDebug
57 ( p_pkg_name IN VARCHAR2 ,
58 p_proc_name IN VARCHAR2
59 )
60 RETURN VARCHAR2
61 IS
62 l_err_loc PLS_INTEGER;
63 BEGIN
64 l_err_loc := 100;
65 RETURN g_log_module_prefix || UPPER(p_pkg_name) || g_log_module_seperator || p_proc_name;
66 END getModuleNameForDebug;
67
68 PROCEDURE logProcBegin
69 ( p_pkg_name IN VARCHAR2 ,
70 p_proc_name IN VARCHAR2 ,
71 p_log_string IN VARCHAR2
72 )
73 IS
74 l_proc_begin_module VARCHAR2(80);
75 l_err_loc PLS_INTEGER;
76 BEGIN
77 l_err_loc := 100;
78 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
79 l_err_loc := 200;
80 l_proc_begin_module := getModuleNameForDebug(p_pkg_name, p_proc_name) || g_log_module_seperator || g_log_module_begin;
81 l_err_loc := 300;
82 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_proc_begin_module, p_log_string);
83 l_err_loc := 400;
84 END IF;
85 EXCEPTION
86 WHEN OTHERS THEN
87 l_err_loc := 500;
88 NULL;
89 END logProcBegin;
90
91 PROCEDURE logProcEnd
92 ( p_pkg_name IN VARCHAR2 ,
93 p_proc_name IN VARCHAR2 ,
94 p_log_string IN VARCHAR2
95 )
96 IS
97 l_proc_end_module VARCHAR2(80);
98 l_err_loc PLS_INTEGER;
99 BEGIN
100 l_err_loc := 100;
101 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
102 l_err_loc := 200;
103 l_proc_end_module := getModuleNameForDebug(p_pkg_name, p_proc_name) || g_log_module_seperator || g_log_module_end;
104 l_err_loc := 300;
105 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_proc_end_module, p_log_string);
106 l_err_loc := 400;
107 END IF;
108 EXCEPTION
109 WHEN OTHERS THEN
110 l_err_loc := 500;
111 NULL;
112 END logProcEnd;
113
114 PROCEDURE logUnexpectedException
115 ( p_pkg_name IN VARCHAR2 ,
116 p_proc_name IN VARCHAR2 ,
117 p_log_string IN VARCHAR2
118 )
119 IS
120 l_err_loc PLS_INTEGER;
121 BEGIN
122 l_err_loc := 100;
123 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
124 l_err_loc := 200;
125 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, getModuleNameForDebug(p_pkg_name, p_proc_name), p_log_string);
126 l_err_loc := 300;
127 END IF;
128 EXCEPTION
129 WHEN OTHERS THEN
130 l_err_loc := 400;
131 NULL;
132 END logUnexpectedException;
133
134 PROCEDURE logPOSessionGTData
135 ( p_key IN NUMBER
136 )
137 IS
138 CURSOR poSessionGTCsr (p_key NUMBER) IS
139 SELECT key, index_num1, index_num2,
140 index_char1, index_char2,
141 char1, char2, char3
142 FROM po_session_gt
143 WHERE key = p_key;
144
145 l_api_name CONSTANT VARCHAR2(30) := 'logPOSessionGTData';
146 l_err_loc PLS_INTEGER;
147
148 ----- Start of declaring columns selected in the cursor -----
149
150 l_key_tbl DBMS_SQL.NUMBER_TABLE;
151 l_index_num1_tbl DBMS_SQL.NUMBER_TABLE;
152 l_index_num2_tbl DBMS_SQL.NUMBER_TABLE;
153 l_index_char1_tbl DBMS_SQL.VARCHAR2_TABLE;
154 l_index_char2_tbl DBMS_SQL.VARCHAR2_TABLE;
155 l_char1_tbl DBMS_SQL.NUMBER_TABLE;
156 l_char2_tbl DBMS_SQL.VARCHAR2_TABLE;
157 l_char3_tbl DBMS_SQL.VARCHAR2_TABLE;
158
159 ------ End of declaring columns selected in the cursor ------
160 BEGIN
161 l_err_loc := 100;
162 OPEN poSessionGTCsr(p_key);
163
164 l_err_loc := 200;
165 IF (ICX_CAT_UTIL_PVT.g_batch_size IS NULL) THEN
166 setBatchSize;
167 END IF;
168
169 LOOP
170 l_err_loc := 300;
171 l_key_tbl.DELETE;
172 l_index_num1_tbl.DELETE;
173 l_index_num2_tbl.DELETE;
174 l_index_char1_tbl.DELETE;
175 l_index_char2_tbl.DELETE;
176 l_char1_tbl.DELETE;
177 l_char2_tbl.DELETE;
178 l_char3_tbl.DELETE;
179
180 l_err_loc := 400;
181 FETCH poSessionGTCsr BULK COLLECT INTO
182 l_key_tbl, l_index_num1_tbl, l_index_num2_tbl,
183 l_index_char1_tbl, l_index_char2_tbl,
184 l_char1_tbl, l_char2_tbl, l_char3_tbl
185 LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
186
187 EXIT WHEN l_key_tbl.COUNT = 0;
188
189 l_err_loc := 500;
190 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
191 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
192 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
193 'For p_key:' || p_key || ', PO_SESSION_GT rowcount:' || l_key_tbl.COUNT );
194 END IF;
195
196 l_err_loc := 600;
197 FOR i IN 1..l_key_tbl.COUNT LOOP
198 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
199 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
200 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
201 'PO_SESSION_GT Row # ' || i ||
202 '; l_key_tbl: ' || l_key_tbl(i) ||
203 ', l_index_num1_tbl: ' || l_index_num1_tbl(i) ||
204 ', l_index_num2_tbl: ' || l_index_num2_tbl(i) ||
205 ', l_index_char1_tbl: ' || l_index_char1_tbl(i) ||
206 ', l_index_char2_tbl: ' || l_index_char2_tbl(i) ||
207 ', l_char1_tbl: ' || l_char1_tbl(i) ||
208 ', l_char2_tbl: ' || l_char2_tbl(i) ||
209 ', l_char3_tbl: ' || l_char3_tbl(i) );
210 END IF;
211 END LOOP;
212
213 EXIT WHEN l_key_tbl.COUNT < ICX_CAT_UTIL_PVT.g_batch_size;
214 END LOOP;
215 l_err_loc := 700;
216 EXCEPTION
217 WHEN OTHERS THEN
218 ICX_CAT_UTIL_PVT.logUnexpectedException(
219 G_PKG_NAME, l_api_name,
220 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
221 END logPOSessionGTData;
222
223 PROCEDURE logMtlItemBulkloadRecsData
224 ( p_request_id IN NUMBER
225 )
226 IS
227 CURSOR mtlItemBulkloadRecsCsr (p_request_id NUMBER) IS
228 SELECT inventory_item_id, organization_id, revision_id,
229 category_id, category_set_id
230 FROM mtl_item_bulkload_recs
231 WHERE request_id = p_request_id;
232
233 l_api_name CONSTANT VARCHAR2(30) := 'logMtlItemBulkloadRecsData';
234 l_err_loc PLS_INTEGER;
235
236 ----- Start of declaring columns selected in the cursor -----
237
238 l_inventory_item_id_tbl DBMS_SQL.NUMBER_TABLE;
239 l_organization_id_tbl DBMS_SQL.NUMBER_TABLE;
240 l_revision_id_tbl DBMS_SQL.NUMBER_TABLE;
241 l_category_id_tbl DBMS_SQL.NUMBER_TABLE;
242 l_category_set_id_tbl DBMS_SQL.NUMBER_TABLE;
243
244 ------ End of declaring columns selected in the cursor ------
245 BEGIN
246 l_err_loc := 100;
247 OPEN mtlItemBulkloadRecsCsr(p_request_id);
248
249 l_err_loc := 200;
250 IF (ICX_CAT_UTIL_PVT.g_batch_size IS NULL) THEN
251 setBatchSize;
252 END IF;
253
254 LOOP
255 l_err_loc := 300;
256 l_inventory_item_id_tbl.DELETE;
257 l_organization_id_tbl.DELETE;
258 l_revision_id_tbl.DELETE;
259 l_category_id_tbl.DELETE;
260 l_category_set_id_tbl.DELETE;
261
262 l_err_loc := 400;
263 FETCH mtlItemBulkloadRecsCsr BULK COLLECT INTO
264 l_inventory_item_id_tbl, l_organization_id_tbl,
265 l_revision_id_tbl, l_category_id_tbl, l_category_set_id_tbl
266 LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
267
268 EXIT WHEN l_inventory_item_id_tbl.COUNT = 0;
269
270 l_err_loc := 500;
271 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
272 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
273 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
274 'For p_request_id:' || p_request_id ||
275 ', MTL_ITEM_BULKLOAD_RECS rowcount:' || l_inventory_item_id_tbl.COUNT);
276 END IF;
277
278 l_err_loc := 600;
279 FOR i IN 1..l_inventory_item_id_tbl.COUNT LOOP
280 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
281 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
282 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
283 'MTL_ITEM_BULKLOAD_RECS Row # ' || i ||
284 '; l_inventory_item_id_tbl: ' || l_inventory_item_id_tbl(i) ||
285 ', l_organization_id_tbl: ' || l_organization_id_tbl(i) ||
286 ', l_revision_id_tbl: ' || l_revision_id_tbl(i) ||
287 ', l_category_id_tbl: ' || l_category_id_tbl(i) ||
288 ', l_category_set_id_tbl: ' || l_category_set_id_tbl(i) );
289 END IF;
290 END LOOP;
291
292 EXIT WHEN l_inventory_item_id_tbl.COUNT < ICX_CAT_UTIL_PVT.g_batch_size;
293 END LOOP;
294 l_err_loc := 700;
295 EXCEPTION
296 WHEN OTHERS THEN
297 ICX_CAT_UTIL_PVT.logUnexpectedException(
298 G_PKG_NAME, l_api_name,
299 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
300 END logMtlItemBulkloadRecsData;
301
302 -- IF commit depends on the p_commit passed to the API, then the
303 -- Calling procedure should make sure to call logAndCommitSnapShotTooOld only if p_commit is true.
304 PROCEDURE logAndCommitSnapShotTooOld
305 ( p_pkg_name IN VARCHAR2 ,
306 p_api_name IN VARCHAR2 ,
307 p_err_string IN VARCHAR2
308 )
309 IS
310 l_err_loc PLS_INTEGER;
311 BEGIN
312 l_err_loc := 100;
313 ICX_CAT_UTIL_PVT.logUnexpectedException(
314 p_pkg_name, p_api_name,
315 p_api_name || ' --> snap shot too old error caught at '|| p_err_string ||
316 'Commit will be done in logAndCommitSnapShotTooOld');
317 l_err_loc := 200;
318 COMMIT;
319 END logAndCommitSnapShotTooOld;
320
321 FUNCTION getTimeDiff
322 ( p_start IN DATE ,
323 p_end IN DATE
324 )
325 RETURN NUMBER
326 IS
327 l_time_mult NUMBER := 60*24;
328 BEGIN
329 RETURN ROUND((p_end - p_start) * l_time_mult,3);
330 END getTimeDiff;
331
332 FUNCTION getTimeStats
333 ( p_start IN DATE ,
334 p_end IN DATE
335 )
336 RETURN VARCHAR2
337 IS
338 l_space VARCHAR2(1) := ' ';
339 BEGIN
340 RETURN l_space || 'time(m): ' || getTimeDiff(p_start,p_end)
341 || l_space || 'start: ' || TO_CHAR(p_start,'HH24:MI:SS')
342 || l_space || 'end: ' || TO_CHAR(p_end,'HH24:MI:SS');
343 END getTimeStats;
344
345 --------------------------------------------------------------
346 -- Get PL/SQL Table element Start --
347 --------------------------------------------------------------
348 FUNCTION getTableElement
349 ( p_table IN DBMS_SQL.NUMBER_TABLE ,
350 p_index IN BINARY_INTEGER
351 )
352 RETURN VARCHAR2
353 IS
354 l_string VARCHAR2(2000) := '';
355 l_err_loc PLS_INTEGER;
356 BEGIN
357 l_err_loc := 100;
358 IF p_table.EXISTS(p_index) THEN
359 l_err_loc := 200;
360 l_string := l_string || p_table(p_index);
361 ELSE
362 l_err_loc := 300;
363 l_string := l_string || '<Not Exists>';
364 END IF;
365 l_err_loc := 400;
366 RETURN l_string;
367 END getTableElement;
368
369 FUNCTION getTableElement
370 ( p_table IN DBMS_SQL.VARCHAR2_TABLE ,
371 p_index IN BINARY_INTEGER
372 )
373 RETURN VARCHAR2
374 IS
375 l_string VARCHAR2(2000) := '';
376 l_err_loc PLS_INTEGER;
377 BEGIN
378 l_err_loc := 100;
379 IF p_table.EXISTS(p_index) THEN
380 l_err_loc := 200;
381 l_string := l_string || p_table(p_index);
382 ELSE
383 l_err_loc := 300;
384 l_string := l_string || '<Not Exists>';
385 END IF;
386 RETURN l_string;
387 END getTableElement;
388
389 FUNCTION getTableElement
390 ( p_table IN DBMS_SQL.UROWID_TABLE ,
391 p_index IN BINARY_INTEGER
392 )
393 RETURN VARCHAR2
394 IS
395 l_string VARCHAR2(2000) := '';
396 l_err_loc PLS_INTEGER;
397 BEGIN
398 l_err_loc := 100;
399 IF p_table.EXISTS(p_index) THEN
400 l_err_loc := 200;
401 l_string := l_string || p_table(p_index);
402 ELSE
403 l_err_loc := 300;
404 l_string := l_string || '<Not Exists>';
405 END IF;
406 l_err_loc := 400;
407 RETURN l_string;
408 END getTableElement;
409
410 FUNCTION getTableElement
411 ( p_table IN DBMS_SQL.DATE_TABLE ,
412 p_index IN BINARY_INTEGER
413 )
414 RETURN VARCHAR2
415 IS
416 l_string VARCHAR2(2000) := '';
417 l_err_loc PLS_INTEGER;
418 BEGIN
419 l_err_loc := 100;
420 IF p_table.EXISTS(p_index) THEN
421 l_err_loc := 200;
422 l_string := l_string || TO_CHAR(p_table(p_index), 'MM/DD/YY HH24:MI:SS');
423 ELSE
424 l_err_loc := 300;
425 l_string := l_string || '<Not Exists>';
426 END IF;
427 l_err_loc := 400;
428 RETURN l_string;
429 END getTableElement;
430
431 FUNCTION checkValueExistsInTable
432 ( p_table IN DBMS_SQL.NUMBER_TABLE ,
433 p_value IN NUMBER
434 )
435 RETURN VARCHAR2
436 IS
437 l_ret_value VARCHAR2(1) := 'N';
438 l_api_name CONSTANT VARCHAR2(30) := 'checkValueExistsInTable';
439 l_err_loc PLS_INTEGER;
440 BEGIN
441 FOR j IN 1..p_table.COUNT LOOP
442 l_ret_value := 'N';
443 IF (p_value = p_table(j)) THEN
444 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
445 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
446 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
447 ' p_value:' || p_value ||
448 ', already exists in the p_table at index:' || j ||
449 '; about to exit from table check');
450 END IF;
451 l_ret_value := 'Y';
452 EXIT;
453 END IF;
454 END LOOP;
455
456 RETURN l_ret_value;
457 EXCEPTION
458 WHEN OTHERS THEN
459 ICX_CAT_UTIL_PVT.logUnexpectedException(
460 G_PKG_NAME, l_api_name,
461 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
462 RAISE;
463 END checkValueExistsInTable;
464
465 --------------------------------------------------------------
466 -- Get PL/SQL Table element End --
467 --------------------------------------------------------------
468
469 FUNCTION getPOCategoryIdFromIp(p_category_id IN NUMBER)
470 RETURN NUMBER
471 IS
472 l_po_category_id NUMBER;
473 l_err_loc PLS_INTEGER;
474 BEGIN
475 l_err_loc := 100;
476 SELECT to_number(external_source_key)
477 INTO l_po_category_id
478 FROM icx_por_category_order_map
479 WHERE rt_category_id = p_category_id;
480
481 l_err_loc := 200;
482
483 RETURN l_po_category_id;
484 EXCEPTION
485 WHEN OTHERS THEN
486 l_err_loc := 300;
487 RETURN TO_NUMBER(NULL);
488 END getPOCategoryIdFromIp;
489
490 FUNCTION getNextSequenceForWhoColumns
491 RETURN NUMBER
492 IS
493 l_api_name CONSTANT VARCHAR2(30) := 'getNextSequenceForWhoColumns';
494 l_internal_request_id NUMBER;
495 l_err_loc PLS_INTEGER;
496 BEGIN
497 l_err_loc := 100;
498 SELECT icx_cat_items_ctx_requestid_s.nextval
499 INTO l_internal_request_id
500 FROM dual;
501
502 l_err_loc := 200;
503
504 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
505 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
506 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
507 'Next Seq from icx_cat_items_ctx_requestid_s:' || l_internal_request_id);
508 END IF;
509
510 RETURN l_internal_request_id;
511 EXCEPTION
512 WHEN OTHERS THEN
513 l_err_loc := 300;
514 RETURN TO_NUMBER(NULL);
515 END getNextSequenceForWhoColumns;
516
517 PROCEDURE setBatchSize
518 ( p_batch_size IN NUMBER DEFAULT NULL
519 )
520 IS
521 l_api_name CONSTANT VARCHAR2(30) := 'setBatchSize';
522 l_err_loc PLS_INTEGER;
523 BEGIN
524 l_err_loc := 100;
525 IF (p_batch_size IS NOT NULL) THEN
526 l_err_loc := 200;
527 g_batch_size := p_batch_size;
528 ELSE
529 l_err_loc := 300;
530 fnd_profile.get('POR_LOAD_PURGE_COMMIT_SIZE', ICX_CAT_UTIL_PVT.g_batch_size);
531 IF (g_batch_size IS NULL) THEN
532 l_err_loc := 400;
533 g_batch_size := 2500;
534 END IF;
535 END IF;
536
537 l_err_loc := 500;
538
539 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
540 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
541 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
542 'Batch Size set to:' || ICX_CAT_UTIL_PVT.g_batch_size);
543 END IF;
544 EXCEPTION
545 WHEN OTHERS THEN
546 l_err_loc := 600;
547 g_batch_size := 2500;
548 END setBatchSize;
549
550 PROCEDURE setCommitParameter
551 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
552 )
553 IS
554 l_api_name CONSTANT VARCHAR2(30) := 'setCommitParameter';
555 l_err_loc PLS_INTEGER;
556 BEGIN
557 l_err_loc := 100;
558 g_COMMIT := p_commit;
559
560 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
561 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
562 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
563 'g_COMMIT set to:' || g_COMMIT);
564 END IF;
565 END setCommitParameter;
566
567 PROCEDURE setWhoColumns
568 ( p_request_id IN NUMBER
569 )
570 IS
571 l_api_name CONSTANT VARCHAR2(30) := 'setWhoColumns';
572 l_err_loc PLS_INTEGER;
573 l_internal_request_id NUMBER;
574 BEGIN
575 l_err_loc := 100;
576 l_internal_request_id := ICX_CAT_UTIL_PVT.getNextSequenceForWhoColumns;
577 g_who_columns_rec.user_id := fnd_global.user_id;
578 g_who_columns_rec.login_id := fnd_global.login_id;
579 g_who_columns_rec.internal_request_id := l_internal_request_id;
580
581 IF (p_request_id IS NULL) THEN
582 l_err_loc := 200;
583 g_who_columns_rec.request_id := null;
584 g_who_columns_rec.program_application_id := null;
585 g_who_columns_rec.program_id := null;
586 g_who_columns_rec.program_login_id := null;
587 ELSE
588 l_err_loc := 300;
589 g_who_columns_rec.request_id := p_request_id;
590 g_who_columns_rec.program_application_id := fnd_global.prog_appl_id;
591 g_who_columns_rec.program_id := fnd_global.conc_program_id;
592 g_who_columns_rec.program_login_id := fnd_global.conc_login_id;
593 END IF;
594 l_err_loc := 400;
595 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
596 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
597 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
598 'Who columns; internal_request_id:' || l_internal_request_id ||
599 ', request_id:' || p_request_id ||
600 ', user_id:' || g_who_columns_rec.user_id ||
601 ', login_id:' || g_who_columns_rec.login_id);
602 END IF;
603 END setWhoColumns;
604
605 PROCEDURE setBaseLanguage
606 IS
607 l_api_name CONSTANT VARCHAR2(30) := 'setBaseLanguage';
608 l_err_loc PLS_INTEGER;
609 BEGIN
610 l_err_loc := 100;
611 SELECT language_code
612 INTO g_base_language
613 FROM fnd_languages
614 WHERE installed_flag='B';
615
616 l_err_loc := 200;
617 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
618 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
619 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
620 'Base language:' || g_base_language);
621 END IF;
622 EXCEPTION
623 WHEN OTHERS THEN
624 ICX_CAT_UTIL_PVT.logUnexpectedException(
625 G_PKG_NAME, l_api_name,
626 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
627 g_base_language := 'US';
628 END setBaseLanguage;
629
630 PROCEDURE getPurchasingCategorySetInfo
631 IS
632 l_api_name CONSTANT VARCHAR2(30) := 'getPurchasingCategorySetInfo';
633 l_err_loc PLS_INTEGER;
634 BEGIN
635 l_err_loc := 100;
636 SELECT category_set_id,
637 validate_flag,
638 structure_id
639 INTO g_category_set_id,
640 g_validate_flag,
641 g_structure_id
642 FROM mtl_default_sets_view
643 WHERE functional_area_id = 2;
644
645 l_err_loc := 200;
646 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
647 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
648 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
649 'Purchasing category set info: g_category_set_id:' || g_category_set_id ||
650 ', g_validate_flag:' || g_validate_flag ||
651 ', g_structure_id:' || g_structure_id);
652 END IF;
653 EXCEPTION
654 WHEN OTHERS THEN
655 ICX_CAT_UTIL_PVT.logUnexpectedException(
656 G_PKG_NAME, l_api_name,
657 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
658 RAISE;
659 END getPurchasingCategorySetInfo;
660
661 PROCEDURE getMIConcatSegmentClause
662 IS
663 l_api_name CONSTANT VARCHAR2(30) := 'getMIConcatSegmentClause';
664 l_err_loc PLS_INTEGER;
665 l_appl_column_name_tbl DBMS_SQL.VARCHAR2_TABLE;
666 l_delimiter VARCHAR2(1);
667 BEGIN
668 l_err_loc := 100;
669 SELECT application_column_name
670 BULK COLLECT INTO l_appl_column_name_tbl
671 FROM fnd_id_flex_segments
672 WHERE application_id = 401
673 AND id_flex_code = 'MSTK'
674 AND id_flex_num = 101
675 AND enabled_flag = 'Y'
676 ORDER BY segment_num;
677
678 l_err_loc := 200;
679 SELECT concatenated_segment_delimiter
680 INTO l_delimiter
681 FROM fnd_id_flex_structures
682 WHERE application_id = 401
683 AND id_flex_code = 'MSTK'
684 AND id_flex_num = 101
685 AND enabled_flag = 'Y';
686
687 l_err_loc := 300;
688 FOR i IN 1..l_appl_column_name_tbl.COUNT LOOP
689 IF ( g_mi_concat_seg_clause IS NOT NULL ) THEN
690 l_err_loc := 400;
691 g_mi_concat_seg_clause := g_mi_concat_seg_clause || ' || ''' || l_delimiter || ''' || ' || l_appl_column_name_tbl(i);
692 ELSE
693 l_err_loc := 500;
694 g_mi_concat_seg_clause := l_appl_column_name_tbl(i);
695 END IF;
696 END LOOP;
697
698 l_err_loc := 600;
699 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
700 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
701 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
702 'Concatenated Segment Clause for master items: ' || g_mi_concat_seg_clause);
703 END IF;
704
705 l_err_loc := 700;
706 EXCEPTION
707 WHEN OTHERS THEN
708 ICX_CAT_UTIL_PVT.logUnexpectedException(
709 G_PKG_NAME, l_api_name,
710 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
711 RAISE;
712 END getMIConcatSegmentClause;
713
714 FUNCTION getR12UpgradeJobNumber
715 RETURN NUMBER
716 IS
717 l_api_name CONSTANT VARCHAR2(30) := 'getR12UpgradeJobNumber';
718 l_err_loc PLS_INTEGER;
719 l_upgrade_job_number PLS_INTEGER;
720 BEGIN
721 l_err_loc := 100;
722 SELECT NVL(MIN(job_number), 1)
723 INTO l_upgrade_job_number
724 FROM icx_cat_r12_upgrade_jobs;
725
726 l_err_loc := 200;
727 IF (l_upgrade_job_number > 0) THEN
728 l_upgrade_job_number := ICX_CAT_UTIL_PVT.g_upgrade_user;
729 ELSE
730 l_upgrade_job_number := l_upgrade_job_number - 1;
731 END IF;
732
733 RETURN l_upgrade_job_number;
734 EXCEPTION
735 WHEN OTHERS THEN
736 ICX_CAT_UTIL_PVT.logUnexpectedException(
737 G_PKG_NAME, l_api_name,
738 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
739 RAISE;
740 END getR12UpgradeJobNumber;
741
742 --
743 -- Function
744 -- get_message
745 -- Purpose
746 -- Returns the corresponding value of the mesage name after --
747 -- substituting it with the token
748
749 FUNCTION get_message(p_message_name in VARCHAR2,
750 p_token_name in VARCHAR2,
751 p_token_value in VARCHAR2) return VARCHAR2 is
752 l_message fnd_new_messages.message_text%TYPE;
753 BEGIN
754 fnd_message.set_name('ICX',p_message_name);
755 fnd_message.set_token(p_token_name,p_token_value);
756 l_message := fnd_message.get;
757 return l_message;
758 END;
759
760 -- function to check if the item is valid to be shown in the search results page
761 FUNCTION is_item_valid_for_search
762 (
763 p_source_type IN VARCHAR2,
764 p_po_line_id IN NUMBER,
765 p_req_template_name IN VARCHAR2,
766 p_req_template_line_num IN NUMBER,
767 p_category_id IN NUMBER,
768 p_org_id IN NUMBER
769 )
770 RETURN NUMBER
771 IS
772 l_status NUMBER;
773 l_err_loc PLS_INTEGER;
774 BEGIN
775 l_err_loc := 100;
776
777 l_status := 1;
778 IF (p_source_type = 'MASTER_ITEM') THEN
779 l_err_loc := 150;
780 IF (is_category_valid(p_category_id) = 0) THEN
781 l_err_loc := 200;
782 l_status := 0;
783 END IF;
784 ELSIF (p_source_type in ('TEMPLATE', 'INTERNAL_TEMPLATE')) THEN
785 l_err_loc := 250;
786 IF (is_req_template_line_valid(p_org_id, p_req_template_name, p_req_template_line_num) = 0) THEN
787 l_err_loc := 350;
788 l_status := 0;
789 END IF;
790 ELSIF (p_source_type in ('BLANKET', 'GLOBAL_BLANKET')) THEN
791 l_err_loc := 400;
792 IF (is_blanket_valid(p_po_line_id) = 0) THEN
793 l_err_loc := 450;
794 l_status := 0;
795 END IF;
796 ELSIF (p_source_type = 'QUOTATION') THEN
797 l_err_loc := 500;
798 IF (is_quotation_valid(p_po_line_id) = 0) THEN
799 l_err_loc := 550;
800 l_status := 0;
801 END IF;
802 END IF;
803
804 l_err_loc := 600;
805
806 RETURN l_status;
807
808 EXCEPTION
809 WHEN OTHERS THEN
810 l_err_loc := 650;
811 l_status := 0;
812 RETURN l_status;
813
814 END is_item_valid_for_search;
815
816 -- function to check if the category is valid
817 FUNCTION is_category_valid
818 (
819 p_category_id IN NUMBER
820 )
821 RETURN NUMBER
822 IS
823 l_start_date DATE;
824 l_end_date DATE;
825 l_disable_date DATE;
826 l_status NUMBER;
827 l_err_loc PLS_INTEGER;
828 BEGIN
829 l_err_loc := 100;
830
831 l_status := 1;
832
833 BEGIN
834 SELECT start_date_active, end_date_active, disable_date
835 INTO l_start_date, l_end_date, l_disable_date
836 FROM mtl_categories_kfv
837 WHERE category_id = p_category_id;
838 EXCEPTION
839 WHEN OTHERS THEN
840 l_err_loc := 200;
841 l_status := 0;
842 END;
843
844 l_err_loc := 300;
845
846 IF (l_status = 1) THEN
847 IF (NVL(l_start_date, SYSDATE) > SYSDATE OR
848 NVL(l_end_date, SYSDATE+1) <= SYSDATE OR
849 NVL(l_disable_date, SYSDATE+1) <= SYSDATE) THEN
850 l_err_loc := 400;
851 l_status := 0;
852 END IF;
853 END IF;
854
855 l_err_loc := 500;
856
857 RETURN l_status;
858
859 EXCEPTION
860 WHEN OTHERS THEN
861 l_err_loc := 600;
862 l_status := 0;
863 RETURN l_status;
864
865 END is_category_valid;
866
867 -- function to check if the req template line is valid
868 FUNCTION is_req_template_line_valid
869 (
870 p_org_id IN NUMBER,
871 p_req_template_name IN VARCHAR2,
872 p_req_template_line_num IN NUMBER
873 )
874 RETURN NUMBER
875 IS
876 l_status NUMBER;
877 l_inactive_date DATE;
878 l_po_line_id NUMBER;
879 l_err_loc PLS_INTEGER;
880 BEGIN
881 l_err_loc := 100;
882
883 l_status := 1;
884 SELECT prh.inactive_date, prl.po_line_id
885 INTO l_inactive_date, l_po_line_id
886 FROM po_reqexpress_headers_all prh, po_reqexpress_lines_all prl
887 WHERE prh.express_name = p_req_template_name
888 AND prh.org_id = p_org_id
889 AND prl.express_name = prh.express_name
890 AND prl.org_id = prh.org_id
891 AND prl.sequence_num = TO_NUMBER(p_req_template_line_num);
892
893 l_err_loc := 200;
894
895 IF (NVL(l_inactive_date, SYSDATE+1) <= SYSDATE) THEN
896 l_status := 0;
897 ELSIF (l_po_line_id IS NOT NULL AND l_po_line_id <> -2) THEN
898 l_status := is_blanket_valid(l_po_line_id);
899 END IF;
900
901 l_err_loc := 300;
902
903 RETURN l_status;
904
905 EXCEPTION
906 WHEN OTHERS THEN
907 l_err_loc := 400;
908 l_status := 0;
909 RETURN l_status;
910
911 END is_req_template_line_valid;
912
913 -- function to check if the blanket is valid
914 FUNCTION is_blanket_valid
915 (
916 p_po_line_id IN NUMBER
917 )
918 RETURN NUMBER
919 IS
920 l_status NUMBER;
921 l_err_loc PLS_INTEGER;
922 BEGIN
923 l_err_loc := 100;
924
925 l_status := 0;
926
927 SELECT 1
928 INTO l_status
929 FROM po_headers_all ph, po_lines_all pl
930 WHERE pl.po_line_id = p_po_line_id
931 AND ph.po_header_id = pl.po_header_id
932 AND ph.approved_date IS NOT NULL
933 AND ph.authorization_status NOT IN ('REJECTED', 'INCOMPLETE')
934 AND NVL(ph.user_hold_flag, 'N') <> 'Y'
935 AND NVL(ph.cancel_flag, 'N') <> 'Y'
936 AND NVL(ph.frozen_flag, 'N') <> 'Y'
937 AND NVL(ph.closed_code, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED')
938 AND NVL(pl.closed_code, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED')
939 AND NVL(pl.cancel_flag, 'N') <> 'Y'
940 AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(ph.start_date), TRUNC( SYSDATE - 1))
941 AND NVL(TRUNC(ph.end_date), TRUNC( SYSDATE + 1))
942 AND TRUNC(SYSDATE) <= NVL(TRUNC(pl.expiration_date), TRUNC( SYSDATE+1));
943
944 l_err_loc := 200;
945
946 RETURN l_status;
947
948 EXCEPTION
949 WHEN OTHERS THEN
950 l_err_loc := 300;
951 l_status := 0;
952 RETURN l_status;
953
954 END is_blanket_valid;
955
956 -- function to check if the quotation is valid
957 FUNCTION is_quotation_valid
958 (
959 p_po_line_id IN NUMBER
960 )
961 RETURN NUMBER
962 IS
963 l_status NUMBER;
964 l_err_loc PLS_INTEGER;
965 BEGIN
966 l_err_loc := 100;
967
968 l_status := 0;
969
970 SELECT 1
971 INTO l_status
972 FROM po_headers_all ph, po_lines_all pl
973 WHERE pl.po_line_id = p_po_line_id
974 AND ph.po_header_id = pl.po_header_id
975 AND ph.status_lookup_code = 'A'
976 AND ph.quotation_class_code = 'CATALOG'
977 AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(ph.start_date), TRUNC( SYSDATE - 1))
978 AND NVL(TRUNC(ph.end_date), TRUNC( SYSDATE + 1))
979 AND (NVL(ph.approval_required_flag, 'N') = 'N'
980 OR
981 (ph.approval_required_flag = 'Y' AND
982 EXISTS (SELECT 'current approved effective price break'
983 FROM po_line_locations_all pll, po_quotation_approvals_all pqa
984 WHERE pl.po_line_id = pll.po_line_id
985 AND SYSDATE BETWEEN NVL(pll.start_date, SYSDATE-1) AND
986 NVL(pll.end_date, SYSDATE+1)
987 AND pqa.line_location_id = pll.line_location_id
988 AND pqa.approval_type IS NOT NULL
989 AND SYSDATE BETWEEN NVL(pqa.start_date_active, SYSDATE-1)
990 AND NVL(pqa.end_date_active, SYSDATE+1))));
991
992 l_err_loc := 200;
993
994 RETURN l_status;
995
996 EXCEPTION
997 WHEN OTHERS THEN
998 l_err_loc := 300;
999 l_status := 0;
1000 RETURN l_status;
1001
1002 END is_quotation_valid;
1003
1004 -- function to get the conversion rate from the from_currency to the to_currency
1005 FUNCTION get_rate
1006 (
1007 p_from_currency VARCHAR2,
1008 p_to_currency VARCHAR2,
1009 p_rate_date DATE,
1010 p_rate_type VARCHAR2
1011 )
1012 RETURN NUMBER
1013 IS
1014 l_rate NUMBER;
1015 l_err_loc PLS_INTEGER;
1016 BEGIN
1017 l_err_loc := 100;
1018
1019 l_rate := GL_CURRENCY_API.get_rate(p_from_currency, p_to_currency, p_rate_date, p_rate_type);
1020
1021 l_err_loc := 300;
1022
1023 RETURN l_rate;
1024
1025 -- the GL_CURRENCY_API.get_rate API above will throw an exception if no rate
1026 -- is found. In this case, we will return null. We will also return null
1027 -- if there is any other errors from the API.
1028 EXCEPTION
1029 WHEN OTHERS THEN
1030 l_err_loc := 400;
1031 RETURN null;
1032
1033 END get_rate;
1034
1035 -- function to convert the amount from the from_currency to the to_currency
1036 FUNCTION convert_amount
1037 (
1038 p_from_currency VARCHAR2,
1039 p_to_currency VARCHAR2,
1040 p_conversion_date DATE,
1041 p_conversion_type VARCHAR2,
1042 p_conversion_rate NUMBER,
1043 p_amount NUMBER
1044 )
1045 RETURN NUMBER
1046 IS
1047 l_converted_amount NUMBER;
1048 l_rate NUMBER;
1049 l_err_loc PLS_INTEGER;
1050 BEGIN
1051 l_err_loc := 100;
1052
1053 -- return p_amount if p_from_currency is the same as p_to_currency
1054 IF (p_from_currency = p_to_currency OR p_amount IS null) THEN
1055 l_err_loc := 150;
1056 l_converted_amount := p_amount;
1057 ELSE
1058 l_err_loc := 200;
1059 -- use user rate if conversion type is USER
1060 IF (p_conversion_type = 'User') THEN
1061 l_err_loc := 250;
1062 l_rate := p_conversion_rate;
1063 ELSE
1064 l_err_loc := 300;
1065 l_rate := get_rate(p_from_currency, p_to_currency, p_conversion_date, p_conversion_type);
1066 END IF;
1067
1068 l_err_loc := 350;
1069
1070 IF (l_rate IS NOT null) THEN
1071 l_err_loc := 400;
1072 l_converted_amount := p_amount * l_rate;
1073 ELSE
1074 l_err_loc := 450;
1075 l_converted_amount := null;
1076 END IF;
1077 l_err_loc := 500;
1078 END IF;
1079
1080 l_err_loc := 550;
1081
1082 RETURN l_converted_amount;
1083
1084 END convert_amount;
1085
1086
1087 END ICX_CAT_UTIL_PVT;