[Home] [Help]
PACKAGE BODY: APPS.ICX_CAT_UTIL_PVT
Source
1 PACKAGE BODY ICX_CAT_UTIL_PVT AS
2 /* $Header: ICXVUTLB.pls 120.29.12020000.3 2013/04/11 05:27:08 jiarsun ship $*/
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,p_org_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 l_item_id NUMBER; --bug 15978590
881 BEGIN
882 l_err_loc := 100;
883
884 l_status := 1;
885 SELECT prh.inactive_date, prl.po_line_id, prl.item_id
886 INTO l_inactive_date, l_po_line_id, l_item_id
887 FROM po_reqexpress_headers_all prh, po_reqexpress_lines_all prl
888 WHERE prh.express_name = p_req_template_name
889 AND prh.org_id = p_org_id
890 AND prl.express_name = prh.express_name
891 AND prl.org_id = prh.org_id
892 AND prl.sequence_num = TO_NUMBER(p_req_template_line_num);
893
894 l_err_loc := 200;
895
896 IF (NVL(l_inactive_date, SYSDATE+1) <= SYSDATE) THEN
897 l_status := 0;
898 ELSIF (l_po_line_id IS NOT NULL AND l_po_line_id <> -2) THEN
899 l_status := is_blanket_valid(l_po_line_id,p_org_id);
900 --bug 15978590: add to check whether the item can be purchased or not
901 ELSIF (l_item_id IS NOT NULL) THEN
902 SELECT COUNT(*)
903 INTO l_status
904 FROM mtl_system_items_b msi, financials_system_params_all fsp
905 WHERE msi.INVENTORY_ITEM_ID = l_item_id
906 AND msi.ORGANIZATION_ID = fsp.INVENTORY_ORGANIZATION_ID
907 AND fsp.ORG_ID = p_org_id
908 AND (msi.purchasing_enabled_flag = 'Y'
909 OR msi.internal_order_enabled_flag = 'Y' );
910 --end bug 15978590
911 END IF;
912
913 l_err_loc := 300;
914
915 RETURN l_status;
916
917 EXCEPTION
918 WHEN OTHERS THEN
919 l_err_loc := 400;
920 l_status := 0;
921 RETURN l_status;
922
923 END is_req_template_line_valid;
924
925 -- function to check if the blanket is valid
926 FUNCTION is_blanket_valid
927 (
928 p_po_line_id IN NUMBER, p_org_id IN NUMBER
929 )
930 RETURN NUMBER
931 IS
932 l_status NUMBER;
933 l_err_loc PLS_INTEGER;
934 l_auth_status po_headers_all.authorization_status%type;
935 l_revision po_lines_archive_all.REVISION_NUM%type;
936 BEGIN
937 l_err_loc := 100;
938
939 l_status := 0;
940
941 --- bug 12792244 start
942
943 SELECT ph.authorization_status
944 INTO l_auth_status
945 FROM PO_HEADERS_ALL_SEC ph,PO_LINES_ALL pl
946 WHERE pl.po_line_id=p_po_line_id
947 AND ph.po_header_id = pl.po_header_id;
948
949
950 If l_auth_status <> 'APPROVED' THEN --bug 16374319
951
952 l_err_loc := 200;
953
954 select max(REVISION_NUM)
955 into l_revision
956 from po_lines_archive_all
957 where po_line_id=p_po_line_id;
958
959 SELECT 1
960 INTO l_status
961 FROM po_headers_archive_all ph, po_lines_archive_all pl
962 WHERE pl.po_line_id = p_po_line_id
963 AND ph.LATEST_EXTERNAL_FLAG = 'Y'
964 AND pl.REVISION_NUM = l_revision
965 AND ph.po_header_id = pl.po_header_id
966 AND ph.approved_date IS NOT NULL
967 AND ph.authorization_status NOT IN ('REJECTED', 'INCOMPLETE')
968 AND NVL(ph.user_hold_flag, 'N') <> 'Y'
969 AND NVL(ph.cancel_flag, 'N') <> 'Y'
970 AND NVL(ph.frozen_flag, 'N') <> 'Y'
971 AND NVL(ph.closed_code, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED')
972 AND NVL(pl.closed_code, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED')
973 AND NVL(pl.cancel_flag, 'N') <> 'Y'
974 AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(ph.start_date), TRUNC( SYSDATE - 1))
975 AND NVL(TRUNC(ph.end_date), TRUNC( SYSDATE + 1))
976 AND TRUNC(SYSDATE) <= NVL(TRUNC(pl.expiration_date), TRUNC( SYSDATE+1))
977 --Bug:#14370992 begin
978 AND Decode (pl.item_id, NULL, 1,
979 (SELECT Count(*)
980 FROM mtl_system_items_b msi,
981 financials_system_params_all fsp
982 WHERE msi.INVENTORY_ITEM_ID = pl.ITEM_ID
983 AND msi.ORGANIZATION_ID = fsp.INVENTORY_ORGANIZATION_ID
984 AND p_org_id = fsp.ORG_ID
985 AND (msi.purchasing_enabled_flag = 'Y'
986 OR msi.internal_order_enabled_flag = 'Y'))) = 1;
987 --Bug:#14370992 end
988
989 ELSE
990
991 --- bug 12792244 end
992
993 l_err_loc := 300;
994
995 SELECT 1
996 INTO l_status
997 FROM PO_HEADERS_ALL_SEC ph, po_lines_all pl
998 WHERE pl.po_line_id = p_po_line_id
999 AND ph.po_header_id = pl.po_header_id
1000 AND ph.approved_date IS NOT NULL
1001 AND ph.authorization_status NOT IN ('REJECTED', 'INCOMPLETE')
1002 AND NVL(ph.user_hold_flag, 'N') <> 'Y'
1003 AND NVL(ph.cancel_flag, 'N') <> 'Y'
1004 AND NVL(ph.frozen_flag, 'N') <> 'Y'
1005 AND NVL(ph.closed_code, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED')
1006 AND NVL(pl.closed_code, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED')
1007 AND NVL(pl.cancel_flag, 'N') <> 'Y'
1008 AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(ph.start_date), TRUNC( SYSDATE - 1))
1009 AND NVL(TRUNC(ph.end_date), TRUNC( SYSDATE + 1))
1010 AND TRUNC(SYSDATE) <= NVL(TRUNC(pl.expiration_date), TRUNC( SYSDATE+1))
1011 AND Decode (pl.item_id, NULL, 1,
1012 (SELECT Count(*)
1013 FROM mtl_system_items_b msi,
1014 financials_system_params_all fsp
1015 WHERE msi.INVENTORY_ITEM_ID = pl.ITEM_ID
1016 AND msi.ORGANIZATION_ID = fsp.INVENTORY_ORGANIZATION_ID
1017 AND p_org_id = fsp.ORG_ID --Bug:#14370992
1018 AND (msi.purchasing_enabled_flag = 'Y'
1019 OR msi.internal_order_enabled_flag = 'Y'))) = 1;
1020
1021 END IF;
1022 l_err_loc := 400;
1023
1024 RETURN l_status;
1025
1026 EXCEPTION
1027 WHEN OTHERS THEN
1028 l_err_loc := 500;
1029 l_status := 0;
1030 RETURN l_status;
1031
1032 END is_blanket_valid;
1033
1034 -- function to check if the quotation is valid
1035 FUNCTION is_quotation_valid
1036 (
1037 p_po_line_id IN NUMBER
1038 )
1039 RETURN NUMBER
1040 IS
1041 l_status NUMBER;
1042 l_err_loc PLS_INTEGER;
1043 BEGIN
1044 l_err_loc := 100;
1045
1046 l_status := 0;
1047
1048 SELECT 1
1049 INTO l_status
1050 FROM PO_HEADERS_ALL_SEC ph, po_lines_all pl
1051 WHERE pl.po_line_id = p_po_line_id
1052 AND ph.po_header_id = pl.po_header_id
1053 AND ph.status_lookup_code = 'A'
1054 AND ph.quotation_class_code = 'CATALOG'
1055 AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(ph.start_date), TRUNC( SYSDATE - 1))
1056 AND NVL(TRUNC(ph.end_date), TRUNC( SYSDATE + 1))
1057 AND (NVL(ph.approval_required_flag, 'N') = 'N'
1058 OR
1059 (ph.approval_required_flag = 'Y' AND
1060 EXISTS (SELECT 'current approved effective price break'
1061 FROM po_line_locations_all pll, po_quotation_approvals_all pqa
1062 WHERE pl.po_line_id = pll.po_line_id
1063 AND SYSDATE BETWEEN NVL(pll.start_date, SYSDATE-1) AND
1064 NVL(pll.end_date, SYSDATE+1)
1065 AND pqa.line_location_id = pll.line_location_id
1066 AND pqa.approval_type IS NOT NULL
1067 AND SYSDATE BETWEEN NVL(pqa.start_date_active, SYSDATE-1)
1068 AND NVL(pqa.end_date_active, SYSDATE+1))))
1069 AND TRUNC(SYSDATE) < NVL(TRUNC(pl.expiration_date), TRUNC( SYSDATE+1))
1070 AND 1 = CASE WHEN pl.item_id IS NOT NULL THEN (SELECT Count(*)
1071 FROM mtl_system_items_b msi,
1072 financials_system_params_all fsp
1073 WHERE msi.INVENTORY_ITEM_ID = pl.ITEM_ID
1074 AND msi.ORGANIZATION_ID = fsp.INVENTORY_ORGANIZATION_ID
1075 AND pl.org_id = fsp.ORG_ID
1076 AND (msi.purchasing_enabled_flag = 'Y'
1077 OR msi.internal_order_enabled_flag = 'Y'))
1078 WHEN pl.item_id IS NULL THEN 1 END;
1079 l_err_loc := 200;
1080
1081 RETURN l_status;
1082
1083 EXCEPTION
1084 WHEN OTHERS THEN
1085 l_err_loc := 300;
1086 l_status := 0;
1087 RETURN l_status;
1088
1089 END is_quotation_valid;
1090
1091 -- function to get the conversion rate from the from_currency to the to_currency
1092 FUNCTION get_rate
1093 (
1094 p_from_currency VARCHAR2,
1095 p_to_currency VARCHAR2,
1096 p_rate_date DATE,
1097 p_rate_type VARCHAR2
1098 )
1099 RETURN NUMBER
1100 IS
1101 l_rate NUMBER;
1102 l_err_loc PLS_INTEGER;
1103 BEGIN
1104 l_err_loc := 100;
1105
1106 l_rate := GL_CURRENCY_API.get_rate(p_from_currency, p_to_currency, p_rate_date, p_rate_type);
1107
1108 l_err_loc := 300;
1109
1110 RETURN l_rate;
1111
1112 -- the GL_CURRENCY_API.get_rate API above will throw an exception if no rate
1113 -- is found. In this case, we will return null. We will also return null
1114 -- if there is any other errors from the API.
1115 EXCEPTION
1116 WHEN OTHERS THEN
1117 l_err_loc := 400;
1118 RETURN null;
1119
1120 END get_rate;
1121
1122 -- function to convert the amount from the from_currency to the to_currency
1123 FUNCTION convert_amount
1124 (
1125 p_from_currency VARCHAR2,
1126 p_to_currency VARCHAR2,
1127 p_conversion_date DATE,
1128 p_conversion_type VARCHAR2,
1129 p_conversion_rate NUMBER,
1130 p_amount NUMBER
1131 )
1132 RETURN NUMBER
1133 IS
1134 l_converted_amount NUMBER;
1135 l_rate NUMBER;
1136 l_err_loc PLS_INTEGER;
1137 BEGIN
1138 l_err_loc := 100;
1139
1140 -- return p_amount if p_from_currency is the same as p_to_currency
1141 IF (p_from_currency = p_to_currency OR p_amount IS null) THEN
1142 l_err_loc := 150;
1143 l_converted_amount := p_amount;
1144 ELSE
1145 l_err_loc := 200;
1146 -- use user rate if conversion type is USER
1147 IF (p_conversion_type = 'User') THEN
1148 l_err_loc := 250;
1149 l_rate := p_conversion_rate;
1150 ELSE
1151 l_err_loc := 300;
1152 l_rate := get_rate(p_from_currency, p_to_currency, p_conversion_date, p_conversion_type);
1153 END IF;
1154
1155 l_err_loc := 350;
1156
1157 IF (l_rate IS NOT null) THEN
1158 l_err_loc := 400;
1159 l_converted_amount := p_amount * l_rate;
1160 ELSE
1161 l_err_loc := 450;
1162 l_converted_amount := null;
1163 END IF;
1164 l_err_loc := 500;
1165 END IF;
1166
1167 l_err_loc := 550;
1168
1169 RETURN l_converted_amount;
1170
1171 END convert_amount;
1172
1173 -- function to validate if the sourced document is valid to be rendered
1174 -- based on clm context
1175 FUNCTION check_clm_context
1176 (
1177 p_header_id NUMBER,
1178 p_line_id NUMBER
1179 )
1180 RETURN NUMBER IS
1181 l_count number := 0;
1182 BEGIN
1183
1184 if(p_header_id is not null and p_header_id <> -2) then
1185 select count(*)
1186 into l_count
1187 from po_headers_all_sec
1188 where po_header_id = p_header_id;
1189 elsif(p_line_id is not null and p_line_id <> -2) then
1190 select count(*)
1191 into l_count
1192 from po_headers_all_sec poh,
1193 po_lines_all pol
1194 where poh.po_header_id = pol.po_header_id
1195 and pol.po_line_id = p_line_id;
1196 else
1197 return 1;
1198 end if;
1199
1200 if(l_count = 0) then
1201 return 0;
1202 else
1203 return 1;
1204 end if;
1205
1206 END check_clm_context;
1207
1208 END ICX_CAT_UTIL_PVT;