[Home] [Help]
PACKAGE BODY: APPS.ECX_ENG_UTILS
Source
1 package body ecx_eng_utils as
2 -- $Header: ECXENUTB.pls 120.3 2006/05/24 16:24:01 susaha ship $
3
4 l_procedure PLS_INTEGER := ecx_debug.g_procedure;
5 l_statement PLS_INTEGER := ecx_debug.g_statement;
6 l_unexpected PLS_INTEGER := ecx_debug.g_unexpected;
7 l_procedureEnabled boolean := ecx_debug.g_procedureEnabled;
8 l_statementEnabled boolean := ecx_debug.g_statementEnabled;
9 l_unexpectedEnabled boolean := ecx_debug.g_unexpectedEnabled;
10
11 -- generates a cXML standard conforming payloadID
12 procedure generate_cXML_payloadID (p_document_number in varchar2,
13 x_payload_id out NOCOPY varchar2)
14 is
15
16
17 i_method_name varchar2(2000) := 'ecx_eng_utils.generate_cXML_payloadID';
18
19 random_number NUMBER := null;
20 logical_id VARCHAR2(2000) := null;
21 date_time VARCHAR2(100) := null;
22 invalid_input EXCEPTION;
23
24 begin
25
26 if (l_procedureEnabled) then
27 ecx_debug.push(i_method_name);
28 end if;
29
30 if (p_document_number is null) then
31 raise invalid_input;
32 end if;
33
34 random_number := wf_core.random;
35 logical_id := ecx_trading_partner_pvt.getOAGLOGICALID();
36 date_time := to_char(sysdate, 'YYYY-MM-DD-HH24-MI-SS');
37
38 x_payload_id := (date_time || '.' || p_document_number || '.' || random_number || '@' || logical_id);
39
40 if (l_procedureEnabled) then
41 ecx_debug.pop(i_method_name);
42 end if;
43
44 exception
45
46 when invalid_input then
47 ecx_debug.setErrorInfo(2, 30, SQLERRM || ' - ECX_ENG_UTILS.GENERATE_CXML_PAYLOADID INVALID INPUT DOCUMENT NUMBER');
48 if(l_unexpectedEnabled) then
49 ecx_debug.log(l_unexpected,'ECX', SQLERRM || ' - ECX_ENG_UTILS.GENERATE_CXML_PAYLOADID',
50 i_method_name);
51 end if;
52 if (l_procedureEnabled) then
53 ecx_debug.pop(i_method_name);
54 end if;
55 raise ecx_utils.program_exit;
56
57 when others then
58 ecx_debug.setErrorInfo(2, 30, SQLERRM || ' - ECX_ENG_UTILS.GENERATE_CXML_PAYLOADID');
59 if(l_unexpectedEnabled) then
60 ecx_debug.log(l_unexpected,'ECX', SQLERRM || ' - ECX_ENG_UTILS.GENERATE_CXML_PAYLOADID',
61 i_method_name);
62 end if;
63 if (l_procedureEnabled) then
64 ecx_debug.pop(i_method_name);
65 end if;
66 raise ecx_utils.program_exit;
67
68
69 end generate_cXML_payloadID;
70
71
72 -- validates the date against various cXML formats and returns the
73 -- converted datetime as per the specified timezone
74 Function getDate (cXMLdate varchar2, dbTimezone varchar2)
75 return varchar2
76 is language java
77 name 'oracle.apps.ecx.util.cXMLDateTimeFormat.getDate(java.lang.String, java.lang.String)
78 returns java.lang.String';
79
80
81 -- converts oracle date into cXML date format
82 procedure convert_to_cxml_date (p_ora_date in date,
83 x_cxml_date out NOCOPY varchar2
84 )
85 is
86
87 i_method_name varchar2(2000) := 'ecx_eng_utils.convert_to_cxml_date';
88 l_year varchar2(200);
89 l_month varchar2(200);
90 l_day varchar2(200);
91 begin
92 if (l_procedureEnabled) then
93 ecx_debug.push(i_method_name);
94 end if;
95 if (p_ora_date is not null)
96 then
97 l_year := to_char(p_ora_date, 'YYYY');
98 l_month := to_char(p_ora_date, 'MM');
99 l_day := to_char(p_ora_date, 'DD');
100
101 x_cxml_date := l_year || '-' || l_month || '-' || l_day;
102 else
103 x_cxml_date := null;
104 end if;
105
106 if (l_procedureEnabled) then
107 ecx_debug.pop(i_method_name);
108 end if;
109
110 exception
111 when others then
112 ecx_debug.setErrorInfo(2, 30, SQLERRM || ' - ECX_ENG_UTILS.CONVERT_TO_CXML_DATE');
113 if(l_unexpectedEnabled) then
114 ecx_debug.log(l_unexpected,'ECX', SQLERRM || ' - ECX_ENG_UTILS.CONVERT_TO_CXML_DATE',
115 i_method_name);
116 end if;
117 if (l_procedureEnabled) then
118 ecx_debug.pop(i_method_name);
119 end if;
120 raise ecx_utils.program_exit;
121 end convert_to_cxml_date;
122
123
124 -- converts oracle date into cXML datetime format
125 procedure convert_to_cXML_datetime (p_ora_date in date,
126 x_cxml_date out NOCOPY varchar2
127 )
128 is
129
130 i_method_name varchar2(2000) := 'ecx_eng_utils.convert_to_cXML_datetime';
131 l_ora_date varchar2(200);
132 l_install_mode varchar2(200);
133 l_year varchar2(200);
134 l_month varchar2(200);
135 l_day varchar2(200);
136 l_hour varchar2(200);
137 l_min varchar2(200);
138 l_sec varchar2(200);
139 l_string varchar2(2000);
140 l_offset number;
141 l_offset_hours number;
142 l_offset_mins number;
143 l_timezone varchar2(200);
144 l_timezone_sign varchar2(200);
145
146 begin
147 if (l_procedureEnabled) then
148 ecx_debug.push(i_method_name);
149 end if;
150
151 l_ora_date := to_char(p_ora_date, 'YYYYMMDD HH24MISS');
152
153 if (l_ora_date is not null)
154 then
155
156 if (ecx_eng_utils.g_server_tz is null) then
157 -- get the timezone string
158 -- Check for the Installation Type ( Standalone or Embedded );
159 l_install_mode := wf_core.translate('WF_INSTALL');
160 if l_install_mode = 'EMBEDDED'
161 then
162 l_string := 'begin
163 fnd_profile.get(' || '''ECX_SERVER_TIMEZONE''' || ', ecx_eng_utils.g_server_tz);
164 end;';
165 execute immediate l_string ;
166 else
167 ecx_eng_utils.g_server_tz:= wf_core.translate('ECX_SERVER_TIMEZONE');
168 end if;
169 end if;
170
171 if (ecx_eng_utils.g_server_tz is null)
172 then
173 ecx_eng_utils.g_server_tz := 'GMT';
174 end if;
175
176 l_year := to_char(p_ora_date, 'YYYY');
177 l_month := to_char(p_ora_date, 'MM');
178 l_day := to_char(p_ora_date, 'DD');
179 l_hour := to_char(p_ora_date, 'HH');
180 l_min := to_char(p_ora_date, 'MI');
181 l_sec := to_char(p_ora_date, 'SS');
182
183 -- get the deviation
184 l_offset := ecx_actions.getTimezoneOffset( to_number(l_year), to_number(l_month),
185 to_number(l_day), to_number(l_hour),
186 to_number(l_min), to_number(l_sec),
187 ecx_eng_utils.g_server_tz);
188
189 if l_offset >= 0 then
190 l_timezone_sign := '+';
191 end if;
192
193 -- calculate the timezone in the required format
194 l_offset_hours := floor(l_offset);
195
196 l_offset_mins := (l_offset * 60) mod 60;
197
198 l_timezone := rtrim(ltrim(to_char(l_offset_hours, '09'))) ||':'||
199 rtrim(ltrim(to_char(l_offset_mins, '09')));
200
201 if l_timezone_sign is NOT NULL then
202 l_timezone := l_timezone_sign || l_timezone;
203 end if;
204
205 x_cxml_date := l_year || '-' || l_month || '-' || l_day || 'T' || l_hour || ':' ||
206 l_min || ':' || l_sec || l_timezone;
207 else
208 x_cxml_date := null;
209 end if;
210 if (l_procedureEnabled) then
211 ecx_debug.pop(i_method_name);
212 end if;
213 exception
214 when ecx_utils.program_exit then
215 if (l_procedureEnabled) then
216 ecx_debug.pop(i_method_name);
217 end if;
218 raise ecx_utils.program_exit;
219 when others then
220 ecx_debug.setErrorInfo(2, 30, SQLERRM || ' - ECX_ENG_UTILS.CONVERT_TO_CXML_DATETIME');
221 if(l_unexpectedEnabled) then
222 ecx_debug.log(l_unexpected,'ECX', SQLERRM || ' - ECX_ENG_UTILS.CONVERT_TO_CXML_DATETIME',
223 i_method_name);
224 end if;
225 if (l_procedureEnabled) then
226 ecx_debug.pop(i_method_name);
227 end if;
228 raise ecx_utils.program_exit;
229 end convert_to_cXML_datetime;
230
231
232 -- converts the cxml datetime to oracle date
233 procedure convert_from_cXML_datetime (p_cxml_date in varchar2,
234 x_ora_date out NOCOPY date
235 )
236 is
237
238 i_method_name varchar2(2000) := 'ecx_eng_utils.convert_from_cXML_datetime';
239 l_format_date varchar2(200);
240 l_string varchar2(2000);
241 l_install_mode varchar2(200);
242 begin
243 if (l_procedureEnabled) then
244 ecx_debug.push(i_method_name);
245 end if;
246 if (p_cxml_date is not null)
247 then
248
249 if (ecx_eng_utils.g_server_tz is null) then
250 -- get the DB timezone ID
251 -- Check for the Installation Type ( Standalone or Embedded );
252 l_install_mode := wf_core.translate('WF_INSTALL');
253
254 if l_install_mode = 'EMBEDDED'
255 then
256 l_string := 'begin
257 fnd_profile.get(' || '''ECX_SERVER_TIMEZONE''' || ', ecx_eng_utils.g_server_tz);
258 end;';
259 execute immediate l_string ;
260 else
261 ecx_eng_utils.g_server_tz:= wf_core.translate('ECX_SERVER_TIMEZONE');
262 end if;
263 end if;
264
265 if (ecx_eng_utils.g_server_tz is null)
266 then
267 ecx_eng_utils.g_server_tz := 'GMT';
268 end if;
269
270 l_format_date := getDate(p_cxml_date, ecx_eng_utils.g_server_tz);
271
272 if (l_format_date is null)
273 then
274 ecx_debug.setErrorInfo(1, 30, 'ECX_INVALID_CXML_DATE_FORMAT', 'DATE', p_cxml_date);
275
276 if(l_unexpectedEnabled) then
277 ecx_debug.log(l_unexpected, 'ECX', 'ECX_INVALID_CXML_DATE_FORMAT', i_method_name, 'DATE', p_cxml_date);
278 end if;
279
280 raise ecx_utils.program_exit;
281 else
282 x_ora_date := to_date(l_format_date, 'YYYYMMDD HH24MISS');
283 end if;
284 else
285 x_ora_date := null;
286 end if;
287 if (l_procedureEnabled) then
288 ecx_debug.pop(i_method_name);
289 end if;
290 exception
291 when ecx_utils.program_exit then
292 if (l_procedureEnabled) then
293 ecx_debug.pop(i_method_name);
294 end if;
295 raise ecx_utils.program_exit;
296
297 when others then
298 ecx_debug.setErrorInfo(2, 30, SQLERRM || ' - ECX_ENG_UTILS.CONVERT_FROM_CXML_DATETIME');
299 if(l_unexpectedEnabled) then
300 ecx_debug.log(l_unexpected,'ECX', SQLERRM || ' - ECX_ENG_UTILS.CONVERT_FROM_CXML_DATETIME',i_method_name);
301 end if;
302 if (l_procedureEnabled) then
303 ecx_debug.pop(i_method_name);
304 end if;
305 raise ecx_utils.program_exit;
306 end convert_from_cXML_datetime;
307
308
309 /*
310 Return the password from ecx_tp_details based on the tp_detail_id
311 stored in ecx_utils.g_tp_dtl_id
312 */
313 procedure get_tp_pwd (
314 x_password OUT NOCOPY Varchar2
315 )
316 is
317
318 i_method_name varchar2(2000) := 'ecx_eng_utils.get_tp_pwd';
319 begin
320 if (l_procedureEnabled) then
321 ecx_debug.push(i_method_name);
322 end if;
323
324 if(l_statementEnabled) then
325 ecx_debug.log(l_statement, 'tp_detail_id', ecx_utils.g_tp_dtl_id,i_method_name);
326 end if;
327 select password
328 into x_password
329 from ecx_tp_details
330 where tp_detail_id = ecx_utils.g_tp_dtl_id;
331
332 if (x_password is not null)
333 then
334 x_password := ecx_eng_utils.PWD_SPEC_CODE || x_password ||
335 ecx_eng_utils.PWD_SPEC_CODE;
336 end if;
337 if(l_statementEnabled) then
338 ecx_debug.log(l_statement,'password', x_password,i_method_name);
339 end if;
340 if (l_procedureEnabled) then
341 ecx_debug.pop(i_method_name);
342 end if;
343 exception
344 when no_data_found then
345 ecx_debug.setErrorInfo(1, 30, 'ECX_TP_DTL_ID_NOT_FOUND', 'TP_DTL_ID', ecx_utils.g_tp_dtl_id);
346 if(l_unexpectedEnabled) then
347 ecx_debug.log(l_unexpected,'ECX', 'ECX_TP_DTL_ID_NOT_FOUND', 'TP_DTL_ID', ecx_utils.g_tp_dtl_id,i_method_name);
348 end if;
349 if (l_procedureEnabled) then
350 ecx_debug.pop(i_method_name);
351 end if;
352 raise ecx_utils.program_exit;
353
354 when ecx_utils.program_exit then
355 if (l_procedureEnabled) then
356 ecx_debug.pop(i_method_name);
357 end if;
358 raise ecx_utils.program_exit;
359
360 when others then
361 ecx_debug.setErrorInfo(2, 30, SQLERRM || ' - ECX_ENG_UTILS.GET_TP_PASSWORD');
362 if(l_unexpectedEnabled) then
363 ecx_debug.log(l_unexpected,'ECX', SQLERRM || ' - ECX_ENG_UTILS.GET_TP_PASSWORD',i_method_name);
364 end if;
365 if (l_procedureEnabled) then
366 ecx_debug.pop(i_method_name);
367 end if;
368 raise ecx_utils.program_exit;
369
370 end get_tp_pwd;
371
372
373 procedure convertEncryCodeClob(
374 p_clob IN CLOB,
375 x_clob OUT NOCOPY CLOB
376 ) is
377
378 i_method_name varchar2(2000) := 'ecx_eng_utils.convertEncryCodeClob';
379 l_in_clob_len number := 0;
380 l_start_code varchar2(50);
381 l_start_code_len pls_integer;
382 l_start_code_pos number := 0;
383 l_end_code varchar2(50);
384 l_end_code_len pls_integer;
385 l_end_code_pos number := 0;
386 l_offset number := 1;
387 l_spec_str varchar2(2000);
388 l_spec_str_len number := 0;
389 l_out_string Varchar2(2000);
390 l_amount number := 0;
391 l_errmsg Varchar2(2000);
392 l_errcode pls_integer;
393
394 begin
395
396 if (l_procedureEnabled) then
397 ecx_debug.push(i_method_name);
398 end if;
399
400 if (p_clob is null) then
401 return;
402 end if;
403
404 l_in_clob_len := dbms_lob.getlength(p_clob);
405 if (l_in_clob_len = 0) then
406 return;
407 end if;
408
409 l_start_code := ecx_eng_utils.PWD_SPEC_CODE;
410 l_start_code_len := length(l_start_code);
411 l_end_code := ecx_eng_utils.PWD_SPEC_CODE;
412 l_end_code_len := length(l_end_code);
413
414 if (x_clob is null) then
415 dbms_lob.createtemporary(x_clob, true, dbms_lob.session);
416 end if;
417
418 loop
419 if (l_offset >= l_in_clob_len) then
420 exit;
421 end if;
422
423 -- Get the start offset of special character field.
424 l_start_code_pos := dbms_lob.instr(p_clob, l_start_code, l_offset);
425
426 if(l_statementEnabled) then
427 ecx_debug.log(l_statement,'l_start_code_pos', l_start_code_pos,i_method_name);
428 end if;
429
430 -- if cannot find the special char anymore, then write the rest of the clob.
431 if (l_start_code_pos = 0 or l_start_code_pos is null) then
432 dbms_lob.copy(x_clob, p_clob, l_in_clob_len - l_offset + 1,
433 dbms_lob.getlength(x_clob)+1, l_offset);
434 exit;
435 else
436 -- write all the data that is before the special code.
437 l_amount := l_start_code_pos - l_offset;
438 if (l_amount > 0) then
439 dbms_lob.copy(x_clob, p_clob, l_amount,
440 dbms_lob.getlength(x_clob)+1, l_offset);
441 end if;
442
443 -- find the end of the special code and figure out the special string.
444 l_end_code_pos := dbms_lob.instr(p_clob, l_end_code,
445 l_start_code_pos + l_start_code_len);
446
447 if(l_statementEnabled) then
448 ecx_debug.log(l_statement,'l_end_code_pos', l_end_code_pos,i_method_name);
449 end if;
450 l_spec_str_len := l_end_code_pos - (l_start_code_pos + l_start_code_len);
451
452 if (l_spec_str_len > 0) then
453 -- we assume that between two special set of character is the
454 -- password and this need to be call the ecx_data_encrypt.
455 -- get the string between two set of special characters.
456 l_spec_str := dbms_lob.substr(p_clob, l_spec_str_len,
457 l_start_code_pos + l_start_code_len);
458
459 l_out_string := l_spec_str;
460 ecx_print_local.replace_spec_char(p_value => l_out_string,
461 x_value => l_spec_str);
462
463 if(l_statementEnabled) then
464 ecx_debug.log(l_statement,'String before decryption', l_spec_str,i_method_name);
465 end if;
466 ecx_obfuscate.ecx_data_encrypt(l_input_string => l_spec_str,
467 l_qual_code => 'D',
468 l_output_string => l_out_string,
469 errmsg => l_errmsg,
470 retcode => l_errcode);
471
472 if (l_errcode <> 0) then
473 if(l_unexpectedEnabled) then
474 ecx_debug.log(l_unexpected,'Decryption API Error', l_errmsg,i_method_name);
475 end if;
476 ecx_debug.setErrorInfo(l_errcode, ecx_utils.error_type, l_errmsg);
477 raise ecx_utils.program_exit;
478 end if;
479
480 -- if it is not a valid encrypted password, ecx_data_encrypt returns null.
481 -- we want to keep the same value if this is not able to decrypt.
482 if (l_out_string is null) then
483 l_out_string := l_spec_str;
484 end if;
485
486 ecx_print_local.escape_spec_char(p_value => l_out_string,
487 x_value => l_spec_str);
488
489 dbms_lob.writeappend(x_clob, length(l_spec_str), l_spec_str);
490 end if;
491
492 l_offset := l_end_code_pos + l_end_code_len;
493 end if;
494 end loop;
495 if (l_procedureEnabled) then
496 ecx_debug.pop(i_method_name);
497 end if;
498
499 exception
500 when others then
501 ecx_debug.setErrorInfo(2, 30, SQLERRM || ' - ECX_ENG_UTILS.convertEncryCodeClob');
502 if(l_unexpectedEnabled) then
503 ecx_debug.log(l_unexpected,'ECX', SQLERRM || ' - ECX_ENG_UTILS.convertEncryCodeClob',
504 i_method_name);
505 end if;
506 if (l_procedureEnabled) then
507 ecx_debug.pop(i_method_name);
508 end if;
509 raise ecx_utils.program_exit;
510 end convertEncryCodeClob;
511
512
513 end ecx_eng_utils;