[Home] [Help]
PACKAGE BODY: APPS.ECX_UTIL_API
Source
1 PACKAGE BODY ECX_UTIL_API AS
2 -- $Header: ECXUTLAB.pls 120.3.12020000.4 2013/03/21 13:02:47 jmaddila ship $
3
4
5 l_procedure PLS_INTEGER := ecx_debug.g_procedure;
6 l_statement PLS_INTEGER := ecx_debug.g_statement;
7 l_unexpected PLS_INTEGER := ecx_debug.g_unexpected;
8 l_procedureEnabled boolean := ecx_debug.g_procedureEnabled;
9 l_statementEnabled boolean := ecx_debug.g_statementEnabled;
10 l_unexpectedEnabled boolean := ecx_debug.g_unexpectedEnabled;
11
12 function validate_direction
13 (
14 p_direction in varchar2
15 ) return boolean
16 is
17 begin
18 if (p_direction is null)
19 then
20 return false;
21 end if;
22
23 if (p_direction = 'IN' OR p_direction = 'OUT')
24 then
25 return true;
26 else
27 return false;
28 end if;
29
30 exception
31 when others then
32 return false;
33 end validate_direction;
34
35 Function validate_party_type
36 (
37 p_party_type In Varchar2
38 ) return boolean
39 Is
40
41 l_insmode VARCHAR2(15);
42 l_Select VARCHAR2(500);
43 l_CursorID INTEGER;
44 l_result INTEGER;
45 Begin
46
47 -- Identiy the installation mode and based on the build the query.
48 -- For Standalone, refer to WF_LOOKUPS otherwise refer to ECX_LOOKUP_VALUES.
49
50 l_insmode := wf_core.translate('WF_INSTALL');
51
52 IF l_insmode = 'EMBEDDED' THEN
53 l_Select := 'SELECT 1 ' ||
54 ' FROM ecx_lookup_values' ||
55 ' WHERE lookup_type = ' || '''' || 'PARTY_TYPE' || '''' ||
56 ' AND lookup_code = :party_type ' ||
57 ' AND enabled_flag = ' || '''' || 'Y' || '''' ||
58 ' AND to_date(sysdate, ' || '''' || 'DD-MON-RRRR' || '''' || ') between' ||
59 ' to_date(nvl(start_date_active, sysdate), ' || '''' || 'DD-MON-RRRR' || '''' || ') ' ||
60 ' AND to_date(nvl(end_date_active, sysdate), ' || '''' || 'DD-MON-RRRR' || '''' || ')';
61 ELSE
62 l_Select := 'SELECT 1 ' ||
63 ' FROM wf_lookups' ||
64 ' WHERE lookup_type = ' || '''' || 'PARTY_TYPE' || '''' ||
65 ' AND lookup_code = :party_type ';
66 END IF;
67
68 l_CursorID := DBMS_SQL.OPEN_CURSOR;
69 DBMS_SQL.PARSE(l_CursorID, l_Select, DBMS_SQL.V7);
70
71 if l_insmode <> 'EMBEDDED'
72 then
73 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':party_type', 'STANDALONE');
74 else
75 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':party_type', p_party_type);
76 end if;
77
78 l_result := DBMS_SQL.EXECUTE(l_CursorID);
79 IF DBMS_SQL.FETCH_ROWS(l_CursorID) = 0 THEN
80 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
81 return false;
82 ELSE
83 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
84 return true;
85 END IF;
86
87 exception
88 when others then
89 return false;
90 End Validate_party_type;
91
92 /* Bug 2122579 */
93 Function validate_party_id
94 (
95 p_party_type In Varchar2,
96 p_party_id In number
97 )
98 return boolean
99 Is
100
101 l_Select VARCHAR2(500);
102 l_CursorID INTEGER;
103 l_result INTEGER;
104 Begin
105
106 /* The query string is built based on the party type.
107 For STANDALONE, no query is executed the function returns TRUE. */
108
109 IF p_party_type = 'C' THEN
110 l_Select := 'SELECT 1 FROM hz_parties hz ' ||
111 'WHERE hz.party_id = :party_id ' ||
112 'AND hz.party_type = ' || '''' || 'ORGANIZATION' || '''' ||
113 ' AND hz.status = ' || '''' || 'A' || '''' ||
114 ' AND EXISTS ( SELECT hzc.party_id FROM hz_cust_accounts hzc' ||
115 ' WHERE hzc.party_id = hz.party_id)' ;
116 ELSIF p_party_type = 'E' THEN
117 l_Select := 'SELECT 1 FROM hz_parties hz' ||
118 ' WHERE hz.party_id = :party_id' ;
119 ELSIF p_party_type = 'S' THEN
120 l_Select := 'SELECT 1 FROM po_vendors' ||
121 ' WHERE vendor_id = :party_id' ;
122 ELSIF p_party_type = 'B' THEN
123 l_Select := 'SELECT 1 FROM ce_bank_branches_v' ||
124 ' WHERE branch_party_id = :party_id' ;
125 ELSIF p_party_type = 'I' THEN
126 l_Select := 'SELECT 1 FROM hr_locations' ||
127 ' WHERE location_id = :party_id' ;
128 ELSIF p_party_type = 'CARRIER' THEN
129
130 /* Bug 2242598
131 Validation of party id for party type of CARRIER. */
132
133 l_Select := ' SELECT 1 FROM hz_parties hp,' ||
134 ' hz_code_assignments hca' ||
135 ' WHERE hp.status = ' || '''' || 'A' || '''' ||
136 ' AND hp.party_id = hca.owner_table_id' ||
137 ' AND hca.owner_table_name = ' || '''' ||
138 'HZ_PARTIES' || '''' ||
139 ' AND hca.class_category = ' || '''' ||
140 'TRANSPORTATION_PROVIDERS' || '''' ||
141 ' AND hca.class_code = ' || '''' ||
142 'CARRIER' || '''' ||
143 ' AND nvl(hca.start_date_active, ' ||
144 'sysdate) <= sysdate' ||
145 ' AND nvl(hca.end_date_active, sysdate) >= sysdate' ||
146 ' AND hp.party_id = :party_id';
147 END IF;
148
149 IF p_party_type <> 'STANDALONE' THEN
150 l_CursorID := DBMS_SQL.OPEN_CURSOR;
151 DBMS_SQL.PARSE(l_CursorID, l_Select, DBMS_SQL.V7);
152 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':party_id', p_party_id);
153 l_result := DBMS_SQL.EXECUTE(l_CursorID);
154 IF DBMS_SQL.FETCH_ROWS(l_CursorID) = 0 THEN
155 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
156 return false;
157 ELSE
158 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
159 return true;
160 END IF;
161 ELSE
162 return true;
163 END IF;
164
165 exception
166 when others then
167 return false;
168 End validate_party_id;
169
170 /* BUg 2122579 */
171 Function validate_party_site_id
172 (
173 p_party_type In Varchar2,
174 p_party_id In number,
175 p_party_site_id In number
176 )
177 return boolean
178 Is
179
180 l_Select VARCHAR2(500);
181 l_CursorID INTEGER;
182 l_result INTEGER;
183 Begin
184
185 /* The query string is built based on the party type.
186 For STANDALONE, no query is executed the function returns TRUE. */
187
188 IF p_party_type = 'C' THEN
189 l_Select := 'SELECT 1' ||
190 ' FROM hz_party_sites hps,' ||
191 ' hz_locations hzl' ||
192 ' WHERE hps.party_id = :party_id' ||
193 ' AND hps.party_site_id = :party_site_id' ||
194 ' AND hzl.location_id = hps.location_id';
195 ELSIF p_party_type = 'B' THEN
196 l_Select := 'SELECT 1 FROM ce_bank_branches_v' ||
197 ' WHERE branch_party_id = :party_site_id' ;
198 ELSIF p_party_type = 'S' THEN
199 l_Select := 'SELECT 1' ||
200 ' FROM po_vendor_sites' ||
201 ' WHERE vendor_id = :party_id' ||
202 ' AND vendor_site_id = :party_site_id';
203 ELSIF p_party_type = 'I' THEN
204 l_Select := 'SELECT 1' ||
205 ' FROM hr_locations' ||
206 ' WHERE location_id = :party_id' ;
207 ELSIF p_party_type = 'CARRIER' THEN
208
209 /* Bug 2242598
210 Validation of party id for party type of CARRIER. */
211
212 l_Select := 'SELECT 1' ||
213 ' FROM hz_party_sites hps,' ||
214 ' hz_locations hzl' ||
215 ' WHERE hps.party_id = :party_id' ||
216 ' AND hps.party_site_id = :party_site_id' ||
217 ' AND hzl.location_id = hps.location_id';
218 END IF;
219
220 IF p_party_type <> 'STANDALONE' THEN
221 l_CursorID := DBMS_SQL.OPEN_CURSOR;
222 DBMS_SQL.PARSE(l_CursorID, l_Select, DBMS_SQL.V7);
223 IF (p_party_type <> 'B') THEN
224 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':party_id', p_party_id);
225 END IF;
226 IF (p_party_type <> 'I') THEN
227 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':party_site_id', p_party_site_id);
228 END IF;
229 l_result := DBMS_SQL.EXECUTE(l_CursorID);
230 IF DBMS_SQL.FETCH_ROWS(l_CursorID) = 0 THEN
231 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
232 return false;
233 ELSE
234 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
235 return true;
236 END IF;
237 ELSE
238 return true;
239 END IF;
240
241 exception
242 when others then
243 return false;
244 End validate_party_site_id;
245
246 Function validate_email_address
247 (
248 p_email_addr In Varchar2
249 ) return boolean
250 Is
251 Begin
252 if (instr(p_email_addr, '@') = 0)
253 then
254 return false;
255 else
256 return true;
257 end if;
258 exception
259 when others then
260 return false;
261 End Validate_email_address;
262
263 Function validate_password_length
264 (
265 p_password In varchar2
266 ) return boolean
267 Is
268 Begin
269 If (length(p_password) < 5) Then
270 return false;
271 else
272 return true;
273 end if;
274 exception
275 when others then
276 return false;
277 End validate_password_length;
278
279 /* New function added for bug #2410173 to verify special characters
280 and to trim spaces in the password */
281 Function validate_password
282 (
283 x_password In Out NOCOPY varchar2
284 ) return boolean
285 Is
286
287 l_passwdlen NUMBER;
288 counter NUMBER;
289 l_ascPasswd NUMBER;
290 l_char VARCHAR2(1);
291
292 begin
293
294 x_password := ltrim(rtrim(x_password));
295 l_passwdLen := length(x_password);
296
297
298 IF x_password IS NOT NULL THEN
299 for counter in 1 .. l_passwdLen
300 loop
301 l_char := substr(x_password,counter,1);
302 select ascii(l_char) into l_ascPasswd from dual;
303 if ((l_ascPasswd >= 0 and l_ascPasswd <= 47) OR
304 (l_ascPasswd >= 58 and l_ascPasswd <= 64) OR
305 (l_ascPasswd >= 91 and l_ascPasswd <= 96) OR
306 (l_ascPasswd >= 123 and l_ascPasswd <= 127)) then
307 return false;
308 end if;
309 end loop;
310 END IF;
311 return true;
312
313 Exception
314 when others then
315 return false;
316
317 End validate_password;
318
319 Function validate_confirmation_code
320 (
321 p_confirmation In Varchar2
322 ) return boolean
323 Is
324
325 l_insmode VARCHAR2(15);
326 l_Select VARCHAR2(500);
327 l_CursorID INTEGER;
328 l_result INTEGER;
329 Begin
330
331 -- Identiy the installation mode and based on the build the query.
332 -- For Standalone, refer to WF_LOOKUPS otherwise refer to ECX_LOOKUP_VALUES.
333
334 l_insmode := wf_core.translate('WF_INSTALL');
335
336 IF l_insmode = 'EMBEDDED' THEN
337 l_Select := 'SELECT 1 ' ||
338 ' FROM ecx_lookup_values' ||
339 ' WHERE lookup_type = ' || '''' || 'CONFIRMATION_CODE' || '''' ||
340 ' AND lookup_code = :confirmation ' ||
341 ' AND enabled_flag = ' || '''' || 'Y' || '''' ||
342 ' AND to_date(sysdate, ' || '''' || 'DD-MON-RRRR' || '''' || ') between' ||
343 ' to_date(nvl(start_date_active, sysdate), ' || '''' || 'DD-MON-RRRR' || '''' || ') ' ||
344 ' AND to_date(nvl(end_date_active, sysdate), ' || '''' || 'DD-MON-RRRR' || '''' || ')';
345 ELSE
346 l_Select := 'SELECT 1 ' ||
347 ' FROM wf_lookups' ||
348 ' WHERE lookup_type = ' || '''' || 'CONFIRMATION_CODE' || '''' ||
349 ' AND lookup_code = :confirmation ';
350 END IF;
351
352 l_CursorID := DBMS_SQL.OPEN_CURSOR;
353 DBMS_SQL.PARSE(l_CursorID, l_Select, DBMS_SQL.V7);
354 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':confirmation', p_confirmation);
355 l_result := DBMS_SQL.EXECUTE(l_CursorID);
356 IF DBMS_SQL.FETCH_ROWS(l_CursorID) = 0 THEN
357 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
358 return false;
359 ELSE
360 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
361 return true;
362 END IF;
363
364 exception
365 when others then
366 return false;
367 End Validate_confirmation_code;
368
369 Function validate_protocol_type
370 (
371 p_protocol_type In Varchar2
372 ) return boolean
373 Is
374
375 l_insmode VARCHAR2(15);
376 l_Select VARCHAR2(500);
377 l_CursorID INTEGER;
378 l_result INTEGER;
379 Begin
380
381 -- Identiy the installation mode and based on the build the query.
382 -- For Standalone, refer to WF_LOOKUPS otherwise refer to ECX_LOOKUP_VALUES.
383
384 l_insmode := wf_core.translate('WF_INSTALL');
385
386 IF l_insmode = 'EMBEDDED' THEN
387 l_Select := 'SELECT 1 ' ||
388 ' FROM ecx_lookup_values' ||
389 ' WHERE lookup_type = ' || '''' || 'COMM_METHOD' || '''' ||
390 ' AND lookup_code = :protocol_type ' ||
391 ' AND enabled_flag = ' || '''' || 'Y' || '''' ||
392 ' AND to_date(sysdate, ' || '''' || 'DD-MON-RRRR' || '''' || ') between' ||
393 ' to_date(nvl(start_date_active, sysdate), ' || '''' || 'DD-MON-RRRR' || '''' || ') ' ||
394 ' AND to_date(nvl(end_date_active, sysdate), ' || '''' || 'DD-MON-RRRR' || '''' || ')';
395 ELSE
396 l_Select := 'SELECT 1 ' ||
397 ' FROM wf_lookups' ||
398 ' WHERE lookup_type = ' || '''' || 'COMM_METHOD' || '''' ||
399 ' AND lookup_code = :protocol_type ';
400 END IF;
401
402 l_CursorID := DBMS_SQL.OPEN_CURSOR;
403 DBMS_SQL.PARSE(l_CursorID, l_Select, DBMS_SQL.V7);
404 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':protocol_type', p_protocol_type);
405 l_result := DBMS_SQL.EXECUTE(l_CursorID);
406 IF DBMS_SQL.FETCH_ROWS(l_CursorID) = 0 THEN
407 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
408 return false;
409 ELSE
410 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
411 return true;
412 END IF;
413
414 exception
415 when others then
416 return false;
417 End Validate_protocol_type;
418
419 Function validate_queue_name
420 (
421 p_queue_name In Varchar2
422 ) return boolean
423 is
424 num pls_integer;
425 cursor c is
426 select count(*)
427 from wf_agents
428 where queue_name = p_queue_name;
429
430 begin
431 num := 0;
432
433 open c;
434 fetch c into num;
435 close c;
436
437 if (num = 1) then
438 return true;
439 else
440 return false;
441 end if;
442
443 exception
444 when others then
445 return false;
446 end validate_queue_name;
447
448
449 Function validate_trading_partner
450 (
451 p_tp_header_id In Varchar2
452 ) return boolean
453 is
454 num pls_integer := 0;
455
456 cursor get_tp_hdr_cnt is
457 select count(*) from ecx_tp_headers
458 where tp_header_id = p_tp_header_id;
459
460 begin
461
462 open get_tp_hdr_cnt;
463 fetch get_tp_hdr_cnt into num;
464 close get_tp_hdr_cnt;
465
466 if (num >= 1)
467 then
468 return true;
469 else
470 return false;
471 end if;
472
473 exception
474 when others then
475 if get_tp_hdr_cnt%ISOPEN
476 then
477 close get_tp_hdr_cnt;
478 end if;
479
480 return false;
481 end validate_trading_partner;
482
483 Function validate_data_seeded_flag
484 (
485 p_data_seeded In Varchar2
486 ) return boolean
487 is
488 begin
489 if (p_data_seeded = 'N') or (p_data_seeded = 'Y')
490 then
491 return true;
492 else
493 return false;
494 end if;
495 end validate_data_seeded_flag;
496
497
498 PROCEDURE validate_user(
499 p_username IN VARCHAR2,
500 p_password IN VARCHAR2,
501 p_party_id IN VARCHAR2,
502 p_party_site_id IN VARCHAR2,
503 p_party_type IN VARCHAR2,
504 x_ret_code OUT NOCOPY PLS_INTEGER) IS
505
506 l_insmode VARCHAR2(15);
507 l_proc_call VARCHAR2(32000);
508 l_proc_cursor PLS_INTEGER;
509 l_numrows PLS_INTEGER;
510 l_result VARCHAR2(20);
511 l_person_party_id NUMBER;
512 l_party_site_id VARCHAR2(50);
513 l_status VARCHAR2(20) := 'N';
514 l_msg VARCHAR2(500);
515
516
517 BEGIN
518 -- validate_user set x_ret_code = 0 if success; otherwise, x_ret_code = 1.
519 -- Identiy the installation mode and based on the installation mode,
520 -- call different procedures dynamically.
521
522 x_ret_code := 1;
523 l_insmode := wf_core.translate('WF_INSTALL');
524
525 IF l_insmode = 'EMBEDDED' THEN
526 -- Call the Java Wrapper API that validates the password
527 -- fnd_web_sec.validate_login(p_username,p_password);
528 -- if this function returns 'Y' means valid; otherwise not valid.
529
530 l_proc_call := 'BEGIN :l_result:= fnd_web_sec.validate_login (p_user=> :p_username,' ||
531 ' p_pwd => :p_password); END; ';
532 l_proc_cursor := dbms_sql.open_cursor;
533
534 begin
535 dbms_sql.parse(l_proc_cursor, l_proc_call, dbms_sql.native);
536 exception
537 when others then
538 raise;
539 end;
540
541 dbms_sql.bind_variable(l_proc_cursor, ':l_result', l_result, 32000);
542 dbms_sql.bind_variable(l_proc_cursor, ':p_username', p_username, 32000);
543 dbms_sql.bind_variable(l_proc_cursor, ':p_password', p_password, 32000);
544
545 begin
546 l_numrows := dbms_sql.execute(l_proc_cursor);
547 exception
548 when others then
549 raise;
550 end;
551
552 if (l_numrows > 0) then
553 dbms_sql.variable_value(l_proc_cursor, ':l_result', l_result);
554 else
555 l_result := 'N';
556 end if;
557
558 ELSE
559 -- standalone always return valid.
560 l_result := 'Y';
561 END IF;
562
563 IF UPPER(l_result) = 'Y' THEN
564 if (p_party_type = 'E') then
565 retrieve_customer_id(p_username => p_username,
566 p_description => 'Oracle Exchange User',
567 x_person_party_id => l_person_party_id);
568
569 if (l_person_party_id <> -1) then
570 retrieve_site_party_id (p_person_party_id => l_person_party_id,
571 x_party_id => l_party_site_id,
572 x_status => l_status,
573 x_msg => l_msg);
574
575 if (l_status = 'Y' and (l_party_site_id = p_party_site_id)) then
576 x_ret_code := 0;
577 end if;
578 end if;
579 else
580 x_ret_code := 0;
581 end if;
582 END IF;
583
584 dbms_sql.close_cursor(l_proc_cursor);
585
586 EXCEPTION
587 WHEN OTHERS THEN
588 x_ret_code := 1;
589 dbms_sql.close_cursor(l_proc_cursor);
590 END validate_user;
591
592 procedure retrieve_customer_id(
593 p_username IN VARCHAR2,
594 p_description IN VARCHAR2,
595 x_person_party_id OUT NOCOPY NUMBER ) is
596
597 l_select VARCHAR2(400);
598 l_cursor PLS_INTEGER;
599 l_numrows PLS_INTEGER;
600 l_username VARCHAR2(250);
601
602 BEGIN
603 l_select := 'select customer_id' ||
604 ' from fnd_user where user_name = :user_name' ||
605 ' AND description = :description';
606
607 l_cursor := dbms_sql.open_cursor;
608 begin
609 dbms_sql.parse(l_cursor, l_select, dbms_sql.native);
610 exception
611 when others then
612 raise;
613 end;
614 l_username := upper(p_username);
615 dbms_sql.define_column(l_cursor, 1, x_person_party_id);
616 dbms_sql.bind_variable(l_cursor, ':user_name', l_username);
617 dbms_sql.bind_variable(l_cursor, ':description', p_description);
618 begin
619 l_numrows := dbms_sql.execute(l_cursor);
620 if dbms_sql.fetch_rows(l_cursor) = 0 then
621 x_person_party_id := -1;
622 else
623 dbms_sql.column_value(l_cursor, 1, x_person_party_id);
624 end if;
625 exception
626 when others then
627 raise;
628 end;
629
630 dbms_sql.close_cursor(l_cursor);
631
632 EXCEPTION
633 when others then
634 x_person_party_id := -1;
635 dbms_sql.close_cursor(l_cursor);
636 END;
637
638 procedure retrieve_site_party_id(
639 p_person_party_id IN NUMBER,
640 x_party_id OUT NOCOPY VARCHAR2,
641 x_status OUT NOCOPY VARCHAR2,
642 x_msg OUT NOCOPY VARCHAR2) IS
643
644 l_proc_call VARCHAR2(32000);
645 l_proc_cursor PLS_INTEGER;
646 l_numrows PLS_INTEGER;
647
648 BEGIN
649 l_proc_call := 'BEGIN pom_user_hz_wrapper_pkg.retrieve_site_party_id(' ||
650 'p_user_party_id => :p_person_party_id, ' ||
651 'x_site_party_id => :x_party_id, ' ||
652 'x_status => :x_status, x_exception_msg => :x_msg); END; ';
653 l_proc_cursor := dbms_sql.open_cursor;
654
655 begin
656 dbms_sql.parse(l_proc_cursor, l_proc_call, dbms_sql.native);
657 exception
658 when others then
659 raise;
660 end;
661
662 dbms_sql.bind_variable(l_proc_cursor, ':p_person_party_id', p_person_party_id);
663 dbms_sql.bind_variable(l_proc_cursor, ':x_party_id', x_party_id, 32000);
664 dbms_sql.bind_variable(l_proc_cursor, ':x_status', x_status, 32000);
665 dbms_sql.bind_variable(l_proc_cursor, ':x_msg', x_msg, 32000);
666
667 begin
668 l_numrows := dbms_sql.execute(l_proc_cursor);
669 dbms_sql.variable_value(l_proc_cursor, ':x_party_id', x_party_id);
670 dbms_sql.variable_value(l_proc_cursor, ':x_status', x_status);
671 dbms_sql.variable_value(l_proc_cursor, ':x_msg', x_msg);
672 exception
673 when others then
674 raise;
675 end;
676
677 if (x_status is null) then
678 x_status := 'Y';
679 end if;
680
681 dbms_sql.close_cursor(l_proc_cursor);
682
683 EXCEPTION
684 WHEN OTHERS THEN
685 x_status := 'N';
686 dbms_sql.close_cursor(l_proc_cursor);
687 END retrieve_site_party_id;
688
689 Function getIANACharset return varchar2 is
690 l_IANAcharset varchar2(2000);
691 l_DBcharset varchar2(2000);
692 l_xmldecl varchar2(2000);
693 l_proc_call VARCHAR2(32000);
694 l_proc_cursor PLS_INTEGER;
695 l_numrows PLS_INTEGER;
696 Begin
697 Begin
698 select v$nls_parameters.value into l_DBcharset
699 from v$nls_parameters
700 where v$nls_parameters.parameter='NLS_CHARACTERSET';
701 Exception
702 When others then
703 l_DBcharset := 'UTF8';
704 End;
705
706 /* Call the utl_gdk mapping api to do the Oracle-IANA conversion */
707
708 Begin
709 l_proc_call := 'BEGIN :l_IANACharset := utl_gdk.charset_map(:l_DBcharset);End;';
710 l_proc_cursor := dbms_sql.open_cursor;
711 dbms_sql.parse(l_proc_cursor, l_proc_call, dbms_sql.native);
712 dbms_sql.bind_variable(l_proc_cursor, ':l_IANACharset',
713 l_IANACharset, 32000);
714 dbms_sql.bind_variable(l_proc_cursor, ':l_DBcharset',
715 l_DBcharset, 32000);
716 l_numrows := dbms_sql.execute(l_proc_cursor);
717
718 if (l_numrows > 0) then
719 dbms_sql.variable_value(l_proc_cursor, ':l_IANACharset',
720 l_IANACharset);
721 else
722 l_IANACharset := 'UTF-8';
723 end if;
724
725 if (l_IANACharset is null) then
726 l_IANACharset := 'UTF-8';
727 end if;
728
729 Exception
730 When Others then
731 l_IANACharset := 'UTF-8';
732 End;
733 IF (dbms_sql.is_open(l_proc_cursor)) then
734 dbms_sql.close_cursor(l_proc_cursor);
735 END IF;
736 return l_IANACharset;
737 End;
738
739
740 Function getValidationFlag return boolean is
741
742 i_string varchar2(2000);
743 l_validate varchar2(1) := 'Y';
744
745 begin
746
747 if (ecx_utils.g_install_mode is null) then
748 ecx_utils.g_install_mode := wf_core.translate('WF_INSTALL');
749 end if;
750
751 if ecx_utils.g_install_mode = 'EMBEDDED'
752 then
753 i_string := 'begin
754 fnd_profile.get('||'''ECX_XML_VALIDATE_FLAG'''||',
755 :l_validate);
756 end;';
757 execute immediate i_string USING OUT l_validate;
758 else
759 l_validate := wf_core.translate('ECX_XML_VALIDATE_FLAG');
760 end if;
761
762 /* if profile option is not set assume that the validation should happen */
763
764 if (l_validate is null) then
765 return true;
766 end if;
767
768 return (l_validate = 'Y') OR (l_validate = 'y');
769 end;
770
771 Function getMaximumXMLSize return Number is
772
773 i_string varchar2(2000);
774 l_size Number;
775 begin
776
777 if (ecx_utils.g_install_mode is null) then
778 ecx_utils.g_install_mode := wf_core.translate('WF_INSTALL');
779 end if;
780
781 if ecx_utils.g_install_mode = 'EMBEDDED'
782 then
783 i_string := 'begin
784 fnd_profile.get('||'''ECX_XML_MAXIMUM_SIZE'''||',
785 :l_size);
786 end;';
787 execute immediate i_string USING OUT l_size;
788 else
789 l_size := wf_core.translate('ECX_XML_MAXIMUM_SIZE');
790 end if;
791
792 if(l_size is null) then
793 l_size := 2000000;
794 end if;
795
796 return l_size ;
797
798 Exception
799 When Others then
800 l_size := 2000000;
801 return l_size;
802 end;
803
804 procedure parseXML(
805 p_parser IN xmlparser.parser,
806 p_xmlclob IN clob,
807 x_validate OUT NOCOPY boolean,
808 x_xmldoc OUT NOCOPY xmlDOM.DOMNode) is
809
810 i_method_name varchar2(2000) := 'ecx_util_api.parsexml';
811 l_max_size number;
812 l_ndoc xmlDOM.DOMDOcument;
813 l_clobLen number;
814
815 begin
816
817 /* Parse the document when
818 1. the document is less than the maximum size regardless what
819 ECX_XML_VALIDATE_FLAG is.
820 2. The document is larger than ECX_XML_MAXIMUM_SIZE and
821 ECX_XML_VALIDATE_FLAG is true */
822
823 x_validate := getValidationFlag();
824
825 if not (x_validate) then
826 l_max_size := getMaximumXMLSize();
827 l_clobLen := dbms_lob.getLength(p_xmlclob);
828
829 if (l_clobLen is null) then
830 l_cloblen := 0;
831 end if;
832
833 if (l_max_size is not null) then
834 x_validate := l_cloblen < l_max_size;
835 end if;
836 end if;
837
838 if x_validate then
839 /**
840 Parse from the Clob
841 **/
842 xmlparser.parseCLOB(p_parser, p_xmlclob);
843 l_ndoc := xmlparser.getDocument(p_parser);
844
845 -- assign ndoc to g_xmldoc for XSLT transformation, if any
846 x_xmldoc := xmlDOM.makeNode(l_ndoc);
847 end If;
848
849 exception
850 when others then
851 ecx_debug.setErrorInfo(1, 30, SQLERRM);
852 if(l_statementEnabled) then
853 ecx_debug.log(l_statement,'ECX', SQLERRM,i_method_name);
854 end if;
855 if NOT xmlDOM.isNull(l_ndoc) then
856 xmlDOM.freeDocument(l_ndoc);
857 end if;
858 raise ecx_utils.program_exit;
859 end parseXML;
860 /*Returns true if value of profile ECX_IN_JAVA_PROCESSING is Y, else false*/
861 function isInJavaProcessEnabled return boolean is
862 i_value varchar2(100);
863 begin
864 fnd_profile.get('ECX_IN_JAVA_PROCESSING',i_value);
865 return upper(i_value)='Y';
866 end isInJavaProcessEnabled;
867
868 END ECX_UTIL_API;