1 PACKAGE BODY CLN_RN_UTILS AS
2 /* $Header: CLNRNUTB.pls 120.8 2006/04/06 01:41:36 amchaudh noship $ */
3 -- Name
4 -- CONVERT_TO_RN_TIMEZONE (Internal Function)
5 -- Purpose
6 -- Converts a date value from server time zone into RosettaNet time zone
7 -- Arguments
8 -- Date
9 -- Notes
10 --
11 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
12 PROCEDURE CONVERT_TO_RN_TIMEZONE(
13 p_input_date IN DATE,
14 x_utc_date OUT NOCOPY DATE )
15 IS
16 l_error_code NUMBER;
17 l_db_timezone VARCHAR2(30);
18 l_rn_timezone VARCHAR2(30);
19 l_error_msg VARCHAR2(255);
20 l_msg_data VARCHAR2(255);
21 BEGIN
22 IF (l_Debug_Level <= 2) THEN
23 cln_debug_pub.Add('----- Entering CONVERT_TO_RN_TIMEZONE API ------- ',2);
24 END IF;
25 IF (l_Debug_Level <= 1) THEN
26 cln_debug_pub.Add('Date Entered by the user -->'||p_input_date,1);
27 END IF;
28 -- get the timezone of the db server
29 l_db_timezone := FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE;
30 IF (l_Debug_Level <= 1) THEN
31 cln_debug_pub.Add('TimeZone of the DB server -->'||l_db_timezone,1);
32 END IF;
33 l_rn_timezone := fnd_profile.value('CLN_RN_TIMEZONE');
34 IF (l_Debug_Level <= 1) THEN
35 cln_debug_pub.Add('TimeZone of the UTC -->'||l_rn_timezone,1);
36 END IF;
37 IF (l_Debug_Level <= 1) THEN
38 cln_debug_pub.Add('Calling function to convert the datetime to UTC',1);
39 END IF;
40 -- this function converts the datetime from the user entered/db timezone to UTC
41 x_utc_date := FND_TIMEZONES_PVT.adjust_datetime(p_input_date,l_db_timezone,l_rn_timezone);
42 IF (l_Debug_Level <= 1) THEN
43 cln_debug_pub.Add('Date in UTC format -->'||x_utc_date,1);
44 END IF;
45 IF (l_Debug_Level <= 2) THEN
46 cln_debug_pub.Add('----- Exiting CONVERT_TO_RN_TIMEZONE API ------- ',2);
47 END IF;
48 -- Exception Handling
49 EXCEPTION
50 WHEN OTHERS THEN
51 l_error_code := SQLCODE;
52 l_error_msg := SQLERRM;
53 l_msg_data := 'Unexpected Error -'||l_error_code||' : '||l_error_msg;
54 IF (l_Debug_Level <= 5) THEN
55 cln_debug_pub.Add(l_msg_data,6);
56 cln_debug_pub.Add('------- ERROR: Exiting CONVERT_TO_RN_TIMEZONE API --------- ',5);
57 END IF;
58 END CONVERT_TO_RN_TIMEZONE;
59 -- Name
60 -- CONVERT_TO_RN_DATETIME
61 -- Purpose
62 -- Converts a date value into RosettaNet datetime format
63 -- RosettaNet Datetime Format: YYYYMMDDThhmmss.SSSZ
64 -- Arguments
65 -- Date
66 -- Notes
67 -- If the date passed is NULL, then sysdate is considered.
68 PROCEDURE CONVERT_TO_RN_DATETIME(
69 p_server_date IN DATE,
70 x_rn_datetime OUT NOCOPY VARCHAR2)
71 IS
72 l_error_code NUMBER;
73 l_utc_date DATE;
74 l_milliseconds VARCHAR2(5);
75 l_server_timezone VARCHAR2(30);
76 l_error_msg VARCHAR2(255);
77 l_msg_data VARCHAR2(255);
78 BEGIN
79 IF (l_Debug_Level <= 2) THEN
80 cln_debug_pub.Add('----- Entering CONVERT_TO_RN_DATETIME API ------- ',2);
81 END IF;
82 IF (l_Debug_Level <= 1) THEN
83 cln_debug_pub.Add('User Entered Date --> '||p_server_date,1);
84 END IF;
85 IF(p_server_date is null) THEN
86 x_rn_datetime := null;
87 IF (l_Debug_Level <= 1) THEN
88 cln_debug_pub.Add('Null is passed. So exiting the procedure with null as return',1);
89 END IF;
90 RETURN;
91 END IF;
92 IF (l_Debug_Level <= 1) THEN
93 cln_debug_pub.Add('Call CONVERT_TO_RN_TIMEZONE API....... ',1);
94 END IF;
95 CONVERT_TO_RN_TIMEZONE(
96 p_input_date => p_server_date,
97 x_utc_date => l_utc_date );
98 IF (l_Debug_Level <= 1) THEN
99 cln_debug_pub.Add('TimeStamp as per UTC '||l_utc_date,1);
100 END IF;
101 l_milliseconds := '000'; --We wont get milliseconds
102 IF (l_Debug_Level <= 1) THEN
103 cln_debug_pub.Add('Truncated Millisecond '||l_milliseconds,1);
104 END IF;
105 x_rn_datetime := TO_CHAR(l_utc_date,'YYYYMMDD')||'T'||TO_CHAR(l_utc_date,'hh24miss')||'.'||l_milliseconds||'Z';
106 IF (l_Debug_Level <= 1) THEN
107 cln_debug_pub.Add('Date in Rosettanet Format '||x_rn_datetime,1);
108 END IF;
109 IF (l_Debug_Level <= 2) THEN
110 cln_debug_pub.Add('----- Exiting CONVERT_TO_RN_DATETIME API ------- ',2);
111 END IF;
112 -- Exception Handling
113 EXCEPTION
114 WHEN OTHERS THEN
115 l_error_code := SQLCODE;
116 l_error_msg := SQLERRM;
117 l_msg_data := 'Unexpected Error -'||l_error_code||' : '||l_error_msg;
118 IF (l_Debug_Level <= 5) THEN
119 cln_debug_pub.Add(l_msg_data,6);
120 cln_debug_pub.Add('------- ERROR: Exiting CONVERT_TO_RN_DATETIME API --------- ',5);
121 END IF;
122 END CONVERT_TO_RN_DATETIME;
123 -- Name
124 -- CONVERT_TO_RN_DATE
125 -- Purpose
126 -- Converts a date value into RosettaNet date format
127 -- RosettaNet Date Format: YYYYMMDDZ
128 -- Arguments
129 -- Date
130 -- Notes
131 -- If the date passed is NULL, then sysdate is considered.
132 PROCEDURE CONVERT_TO_RN_DATE(
133 p_server_date IN DATE,
134 x_rn_date OUT NOCOPY VARCHAR2)
135 IS
136 l_utc_date DATE;
137 l_milliseconds VARCHAR2(5);
138 l_server_timezone VARCHAR2(50);
139 l_error_code NUMBER;
140 l_error_msg VARCHAR2(255);
141 l_msg_data VARCHAR2(255);
142 BEGIN
143 IF (l_Debug_Level <= 2) THEN
144 cln_debug_pub.Add('----- Entering CONVERT_TO_RN_DATETIME API ------- ',2);
145 END IF;
146 IF (l_Debug_Level <= 1) THEN
147 cln_debug_pub.Add('User Entered Date '||p_server_date,1);
148 END IF;
149 IF(p_server_date is null) THEN
150 x_rn_date := null;
151 IF (l_Debug_Level <= 1) THEN
152 cln_debug_pub.Add('Null is passed. So exiting the procedure with NULL as return',1);
153 END IF;
154 RETURN;
155 END IF;
156 x_rn_date := TO_CHAR(p_server_date,'YYYYMMDD')||'Z';
157 IF (l_Debug_Level <= 1) THEN
158 cln_debug_pub.Add('Date in Rosettanet Format '||x_rn_date,1);
159 END IF;
160 IF (l_Debug_Level <= 2) THEN
161 cln_debug_pub.Add('----- Exiting CONVERT_TO_RN_DATE API ------- ',2);
162 END IF;
163 -- Exception Handling
164 EXCEPTION
165 WHEN OTHERS THEN
166 l_error_code := SQLCODE;
167 l_error_msg := SQLERRM;
168 l_msg_data := 'Unexpected Error -'||l_error_code||' : '||l_error_msg;
169 IF (l_Debug_Level <= 5) THEN
170 cln_debug_pub.Add(l_msg_data,6);
171 cln_debug_pub.Add('------- ERROR: Exiting CONVERT_TO_RN_DATE API --------- ',5);
172 END IF;
173 END CONVERT_TO_RN_DATE;
174 -- Name
175 -- CONVERT_TO_DB_DATE
176 -- Purpose
177 -- Converts a date value from RosettaNet date/datetime format to db format
178 -- RosettaNet Datetime Format: YYYYMMDDThhmmss.SSSZ
179 -- RosettaNet Date Format : YYYYMMDDZ
180 -- Arguments
181 -- Date
182 -- Notes
183 -- If the date passed is NULL, then sysdate is considered.
184 PROCEDURE CONVERT_TO_DB_DATE(
185 p_rn_date IN VARCHAR2,
186 x_db_date OUT NOCOPY DATE)
187 IS
188 l_server_date DATE;
189 l_utc_datetime DATE;
190 l_count_t_appearanace NUMBER;
191 l_error_code NUMBER;
192 l_rn_frmt_date VARCHAR2(30);
193 l_rn_timezone VARCHAR2(30);
194 l_db_timezone VARCHAR2(30);
195 l_error_msg VARCHAR2(255);
196 l_msg_data VARCHAR2(255);
197 BEGIN
198 IF (l_Debug_Level <= 2) THEN
199 cln_debug_pub.Add('----- Entering CONVERT_TO_DB_DATE API ------- ',2);
200 END IF;
201 IF (l_Debug_Level <= 1) THEN
202 cln_debug_pub.Add('Rosettanet Date '||p_rn_date,1);
203 END IF;
204 IF(p_rn_date is null) THEN
205 x_db_date := null;
206 IF (l_Debug_Level <= 1) THEN
207 cln_debug_pub.Add('Null is passed. So exiting the procedure with NULL as return',1);
208 END IF;
209 RETURN;
210 END IF;
211 l_count_t_appearanace := instr(p_rn_date,'T');
212 IF (l_count_t_appearanace > 0) THEN
213 --Datetime Format: YYYYMMDDThhmmss.SSSZ
214 l_rn_timezone := fnd_profile.value('CLN_RN_TIMEZONE');
215 IF (l_Debug_Level <= 1) THEN
216 cln_debug_pub.Add('TimeZone of the UTC '||l_rn_timezone,1);
217 END IF;
218 -- get the timezone of the db server
219 l_db_timezone := FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE;
220 IF (l_Debug_Level <= 1) THEN
221 cln_debug_pub.Add('TimeZone of the DB server '||l_db_timezone,1);
222 END IF;
223 IF (l_Debug_Level <= 1) THEN
224 cln_debug_pub.Add('Datetime Format: YYYYMMDDThhmmss.SSSZ',1);
225 END IF;
226 l_rn_frmt_date := substr(p_rn_date,1,8)||substr(p_rn_date,10,6);
227 IF (l_Debug_Level <= 1) THEN
228 cln_debug_pub.Add('Date After Formatting (String)'||l_rn_frmt_date,1);
229 END IF;
230 l_utc_datetime := TO_DATE(l_rn_frmt_date,'YYYYMMDDHH24MISS');
231 IF (l_Debug_Level <= 1) THEN
232 cln_debug_pub.Add('Date After Formatting (Date)'||l_utc_datetime,1);
233 END IF;
234 -- this function converts the datetime from the user entered/db timezone to UTC
235 x_db_date := FND_TIMEZONES_PVT.adjust_datetime(l_utc_datetime,l_rn_timezone,l_db_timezone);
236 IF (l_Debug_Level <= 1) THEN
237 cln_debug_pub.Add('Date after conversion '||x_db_date,1);
238 END IF;
239 ELSE
240 --Date Format : YYYYMMDDZ
241 IF (l_Debug_Level <= 1) THEN
242 cln_debug_pub.Add('Date Format : YYYYMMDDZ',1);
243 END IF;
244 l_rn_frmt_date := substr(p_rn_date,1,8);
245 IF (l_Debug_Level <= 1) THEN
246 cln_debug_pub.Add('Date After Formatting (String) '||l_rn_frmt_date,1);
247 END IF;
248 x_db_date := TO_DATE(l_rn_frmt_date,'YYYYMMDD');
249 IF (l_Debug_Level <= 1) THEN
250 cln_debug_pub.Add('Date After Formatting (Date)'||l_utc_datetime,1);
251 END IF;
252 END IF;
253 IF (l_Debug_Level <= 2) THEN
254 cln_debug_pub.Add('----- Exiting CONVERT_TO_DB_DATE API ------- ',2);
255 END IF;
256 -- Exception Handling
257 EXCEPTION
258 WHEN OTHERS THEN
259 l_error_code := SQLCODE;
260 l_error_msg := SQLERRM;
261 l_msg_data := 'Unexpected Error -'||l_error_code||' : '||l_error_msg;
262 IF (l_Debug_Level <= 5) THEN
263 cln_debug_pub.Add(l_msg_data,6);
264 cln_debug_pub.Add('------- ERROR: Exiting CONVERT_TO_DB_DATE API --------- ',5);
265 END IF;
266 END CONVERT_TO_DB_DATE;
267 -- Name
268 -- CONVERT_Number_To_Char
269 -- Purpose
270 -- Converts a Number value into a character with the given format
271 -- Arguments
272 -- Number
273 -- Format
274 -- Notes
275 -- If the date passed is NULL, then sysdate is considered.
276 PROCEDURE CONVERT_NUMBER_TO_CHAR(
277 p_number IN NUMBER,
278 p_format IN VARCHAR2,
279 x_char OUT NOCOPY VARCHAR2)
280 IS
281 l_error_code NUMBER;
282 l_error_msg VARCHAR2(255);
283 l_msg_data VARCHAR2(255);
284 BEGIN
285 IF (l_Debug_Level <= 2) THEN
286 cln_debug_pub.Add('----- Entering CONVERT_NUMBER_TO_CHAR API ------- ',2);
287 END IF;
288 IF (l_Debug_Level <= 1) THEN
289 cln_debug_pub.Add('Passed Number '||p_number,1);
290 cln_debug_pub.Add('Passed Format '||p_format,1);
291 END IF;
292 x_char := TO_CHAR(p_number,p_format);
293 IF (l_Debug_Level <= 1) THEN
294 cln_debug_pub.Add('Number After Formatting'||x_char,1);
295 END IF;
296 IF (l_Debug_Level <= 2) THEN
297 cln_debug_pub.Add('----- Exiting CONVERT_NUMBER_TO_CHAR API ------- ',2);
298 END IF;
299 -- Exception Handling
300 EXCEPTION
301 WHEN OTHERS THEN
302 l_error_code := SQLCODE;
303 l_error_msg := SQLERRM;
304 l_msg_data := 'Unexpected Error in CONVERT_NUMBER_TO_CHAR -'||l_error_code||' : '||l_error_msg;
305 IF (l_Debug_Level <= 5) THEN
306 cln_debug_pub.Add(l_msg_data,6);
307 cln_debug_pub.Add('------- ERROR: Exiting CONVERT_NUMBER_TO_CHAR API --------- ',5);
311 -- GET_FROM_ROLE
308 END IF;
309 END CONVERT_NUMBER_TO_CHAR;
310 -- Name
312 -- Purpose
313 -- Gets the fromRole details
314 -- based on the Organization ID
315 -- Arguments
316 -- Date
317 -- Notes
318 -- Organization ID
319 PROCEDURE GET_FROM_ROLE(
320 p_org_id IN VARCHAR2,
321 x_name OUT NOCOPY VARCHAR2,
322 x_email OUT NOCOPY VARCHAR2,
323 x_telephone OUT NOCOPY VARCHAR2,
324 x_fax OUT NOCOPY VARCHAR2,
325 x_ece_location_code OUT NOCOPY VARCHAR2 )
326 IS
327 l_error_code NUMBER;
328 l_error_msg VARCHAR2(255);
329 l_msg_data VARCHAR2(255);
330 BEGIN
331 IF (l_Debug_Level <= 5) THEN
332 cln_debug_pub.Add('----- Entering GET_FROM_ROLE -----',2);
333 END IF;
334 IF (l_Debug_Level <= 1) THEN
335 cln_debug_pub.Add('Organization ID ' || p_org_id, 1);
336 END IF;
337 IF (l_Debug_Level <= 1) THEN
338 cln_debug_pub.Add('Executing the Query........', 1);
339 END IF;
340 SELECT org.name
341 , null
342 , loc.telephone_number_1
343 , loc.telephone_number_2
344 , loc.ece_tp_location_code
345 INTO x_name
346 , x_email
347 , x_telephone
348 , x_fax
349 , x_ece_location_code
350 FROM hr_locations_all loc, hr_all_organization_units_vl org
351 WHERE org.location_id = loc.location_id
352 AND org.organization_id = p_org_id;
353 IF (l_Debug_Level <= 1) THEN
354 cln_debug_pub.Add('Result From the Query........', 1);
355 END IF;
356 IF (l_Debug_Level <= 1) THEN
357 cln_debug_pub.Add('Name (From) :' || x_name, 1);
358 cln_debug_pub.Add('Email :' || x_email, 1);
359 cln_debug_pub.Add('Telephone :' || x_telephone, 1);
360 cln_debug_pub.Add('Fax :' || x_fax, 1);
361 cln_debug_pub.Add('ECE Location Code :' || x_ece_location_code, 1);
362 END IF;
363 IF (l_Debug_Level <= 2) THEN
364 cln_debug_pub.Add('----- Exiting GET_FROM_ROLE -----',2);
365 END IF;
366 -- Exception Handling
367 EXCEPTION
368 WHEN OTHERS THEN
369 l_error_code := SQLCODE;
370 l_error_msg := SQLERRM;
371 l_msg_data := 'Unexpected Error -'||l_error_code||' : '||l_error_msg;
372 IF (l_Debug_Level <= 5) THEN
373 cln_debug_pub.Add(l_msg_data,6);
374 cln_debug_pub.Add('------- ERROR: Exiting GET_FROM_ROLE API --------- ',5);
375 END IF;
376 END GET_FROM_ROLE;
377 -- Name
378 -- GET_TO_ROLE
379 -- Purpose
380 -- Gets the toRole details
381 -- based on the TP Header ID
382 -- Arguments
383 -- TP Header ID
384 -- Notes
385 -- No special notes
386 PROCEDURE GET_TO_ROLE(
387 p_tp_header_id IN VARCHAR2,
388 x_name OUT NOCOPY VARCHAR2,
389 x_email OUT NOCOPY VARCHAR2,
390 x_telephone OUT NOCOPY VARCHAR2,
391 x_fax OUT NOCOPY VARCHAR2,
392 x_ece_location_code OUT NOCOPY VARCHAR2 )
393 IS
394 l_party_type VARCHAR2(30);
395 l_party_id NUMBER;
396 l_party_site_id NUMBER;
397 l_error_code NUMBER;
398 l_error_msg VARCHAR2(255);
399 l_msg_data VARCHAR2(255);
400 BEGIN
401 IF (l_Debug_Level <= 2) THEN
402 cln_debug_pub.Add('---- Entering GET_TO_ROLE -------',2);
403 END IF;
404 IF (l_Debug_Level <= 1) THEN
405 cln_debug_pub.Add('Trading Partner header_id:' || p_tp_header_id, 1);
406 END IF;
407 SELECT party_type, party_id, party_site_id
408 INTO l_party_type, l_party_id, l_party_site_id
409 FROM ECX_TP_HEADERS
410 WHERE tp_header_id = p_tp_header_id;
411 IF (l_Debug_Level <= 1) THEN
412 cln_debug_pub.Add('Party Type :' || l_party_type, 1);
413 cln_debug_pub.Add('Party ID :' || l_party_id, 1);
414 cln_debug_pub.Add('Party Site ID :' || l_party_site_id, 1);
415 END IF;
416 IF (l_party_type = 'S') THEN
417 SELECT pv.vendor_name, pvsa.area_code || ' ' || pvsa.phone, pvsa.fax_area_code || ' ' || pvsa.fax,
418 pvsa.ece_tp_location_code, pvsa.email_address
419 INTO x_name, x_telephone, x_fax, x_ece_location_code, x_email
420 FROM po_vendors pv, po_vendor_sites_all pvsa
421 WHERE pv.vendor_id = pvsa.vendor_id
422 AND pv.vendor_id = l_party_id
423 AND pvsa.vendor_site_id = l_party_site_id;
424 ELSIF (l_party_type = 'B') THEN
425 SELECT BANK_BRANCH_NAME,hcp.RAW_PHONE_NUMBER,hcp.EDI_ECE_TP_LOCATION_CODE
426 INTO x_name, x_telephone, x_ece_location_code
427 FROM CE_BANK_BRANCHES_V bb, HZ_PARTIES hp, HZ_contact_points hcp
428 WHERE bb.BRANCH_PARTY_ID = l_party_id
429 AND bb.BRANCH_PARTY_ID = hp.party_id
430 AND hp.PRIMARY_PHONE_CONTACT_PT_ID = hcp.CONTACT_POINT_ID;
434 FROM HR_LOCATIONS
431 ELSIF (l_party_type = 'I') THEN
432 SELECT location_code, telephone_number_1, ece_tp_location_code
433 INTO x_name, x_telephone, x_ece_location_code
435 WHERE location_id = l_party_id;
436 ELSIF (l_party_type = 'C') or (l_party_type = 'CARRIER') THEN
437 SELECT hz.party_name
438 INTO x_name
439 FROM hz_parties hz
440 WHERE hz.party_id = l_party_id;
441 BEGIN
442 Select phone_number, email_address
443 Into x_telephone, x_email
444 From hz_contact_points
445 Where owner_table_name = 'HZ_PARTIES'
446 and owner_table_id = l_party_id
447 and rownum < 1;
448 EXCEPTION
449 WHEN NO_DATA_FOUND THEN
450 --Contact information not available
451 IF (l_Debug_Level <= 1) THEN
452 cln_debug_pub.Add('Contact information not available', 1);
453 END IF;
454 END;
455 END IF;
456 IF (l_Debug_Level <= 1) THEN
457 cln_debug_pub.Add('Party Name :' || x_name, 1);
458 cln_debug_pub.Add('Phone :' || x_telephone, 1);
459 cln_debug_pub.Add('Fax :' || x_fax, 1);
460 cln_debug_pub.Add('Email ID :' || x_email, 1);
461 cln_debug_pub.Add('ECE TP Location ID:' || x_ece_location_code, 1);
462 END IF;
463 IF (l_Debug_Level <= 2) THEN
464 cln_debug_pub.Add('----- Exiting GET_TO_ROLE -----',2);
465 END IF;
466 -- Exception Handling
467 EXCEPTION
468 WHEN OTHERS THEN
469 l_error_code := SQLCODE;
470 l_error_msg := SQLERRM;
471 l_msg_data := 'Unexpected Error -'||l_error_code||' : '||l_error_msg;
472 IF (l_Debug_Level <= 5) THEN
473 cln_debug_pub.Add(l_msg_data,6);
474 cln_debug_pub.Add('------- ERROR: Exiting GET_TO_ROLE API --------- ',5);
475 END IF;
476 END GET_TO_ROLE;
477 -- Name
478 -- FROM_RN_TO_ORCL_FORMAT (Internal Function)
479 -- Purpose
480 -- Convers the Number format from RN to Oracle Undersandable Way
481 -- Arguments
482 -- Format As per RN Spec
483 -- Return
484 -- Format in Oracle Syntax
485 -- Notes
486 -- No special notes
487 FUNCTION FROM_RN_TO_ORCL_FORMAT(
488 p_format IN VARCHAR2) RETURN VARCHAR2
489 IS
490 l_pos NUMBER;
491 l_temp_format VARCHAR2(30);
492 l_number_of_nines NUMBER;
493 l_orcl_format VARCHAR2(30);
494 BEGIN
495 IF (l_Debug_Level <= 2) THEN
496 cln_debug_pub.Add('-----------Entering FROM_RN_TO_ORCL_FORMAT-----------', 2);
497 cln_debug_pub.Add('Format :' || p_format,2);
498 END IF;
499 l_temp_format := p_format;
500 l_orcl_format := '';--Initialize
501 l_pos := INSTR(p_format,'V');
502 IF (l_Debug_Level <= 1) THEN
503 cln_debug_pub.Add('l_pos :' || l_pos,1);
504 END IF;
505 IF (l_pos > 0) THEN
506 l_temp_format := substr(p_format,1, l_pos-1);
507 IF (l_Debug_Level <= 1) THEN
508 cln_debug_pub.Add('l_temp_format :' || l_temp_format,1);
509 END IF;
510 IF instr(l_temp_format,'(') > 0 THEN -- In the form of 9(n)
511 l_number_of_nines := to_number(SUBSTR( SUBSTR(l_temp_format,3), 1, length(l_temp_format) - 3));
512 IF (l_Debug_Level <= 1) THEN
513 cln_debug_pub.Add('l_number_of_nines :' || l_number_of_nines,1);
514 END IF;
515 l_orcl_format:= lpad('.',l_number_of_nines+1,'9');
516 ELSE -- In the form of 9999
517 l_orcl_format:= l_temp_format || '.';
518 END IF;
519 l_temp_format:= substr(p_format,l_pos+1);
520 END IF;
521 IF (l_Debug_Level <= 1) THEN
522 cln_debug_pub.Add('l_temp_format after making for first part :' || l_temp_format,1);
523 END IF;
524 IF instr(l_temp_format,'(') > 0 THEN -- In the form of 9(n)
525 l_number_of_nines := to_number(SUBSTR( SUBSTR(l_temp_format,3), 1, length(l_temp_format) - 3));
529 l_orcl_format := l_orcl_format || ltrim(rpad(' ',l_number_of_nines+1,'9')) ;
526 IF (l_Debug_Level <= 1) THEN
527 cln_debug_pub.Add('l_number_of_nines :' || l_number_of_nines,1);
528 END IF;
530 ELSE -- In the form of 9999
531 l_orcl_format := l_orcl_format || l_temp_format;
532 END IF;
533 IF (l_Debug_Level <= 1) THEN
534 cln_debug_pub.Add('l_orcl_format before return :' || l_orcl_format,1);
535 END IF;
536 IF (l_Debug_Level <= 2) THEN
537 cln_debug_pub.Add('-----------Exiting FROM_RN_TO_ORCL_FORMAT-----------', 2);
538 END IF;
539 RETURN l_orcl_format;
540 EXCEPTION
541 WHEN OTHERS THEN
542 IF (l_Debug_Level <= 5) THEN
543 cln_debug_pub.Add('-----------When others in FROM_RN_TO_ORCL_FORMAT-----------', 6);
544 END IF;
545 --Failed Format Conversion Validation
546 Return NULL;
547 END;
548 FUNCTION IS_VALID_DATE_FORMAT(
549 p_value IN VARCHAR2,
550 p_format IN VARCHAR2) RETURN BOOLEAN
551 IS
552 l_date DATE;
553 BEGIN
554 IF (l_Debug_Level <= 2) THEN
555 cln_debug_pub.Add('-----------Entering IS_VALID_DATE_FORMAT-----------', 2);
556 cln_debug_pub.Add('Value :' || p_value,2);
557 cln_debug_pub.Add('Format :' || p_format,2);
558 END IF;
559 l_date := to_date(p_value,p_format);
560 IF (l_Debug_Level <= 2) THEN
561 cln_debug_pub.Add('-----------Exiting IS_VALID_DATE_FORMAT successfully-----------', 2);
562 END IF;
563 RETURN TRUE;
564 EXCEPTION
565 WHEN OTHERS THEN
566 IF (l_Debug_Level <= 2) THEN
567 cln_debug_pub.Add('-----------Exiting IS_VALID_DATE_FORMAT errornously-----------', 2);
568 END IF;
569 --Failed Date Validation
570 Return FALSE;
571 END;
572 FUNCTION IS_VALID_NUMBER_FORMAT(
573 p_value IN VARCHAR2,
574 p_format IN VARCHAR2 DEFAULT NULL) RETURN BOOLEAN
575 IS
576 l_temp NUMBER;
577 BEGIN
578 IF (l_Debug_Level <= 2) THEN
579 cln_debug_pub.Add('-----------Entering IS_VALID_NUMBER_FORMAT-----------', 2);
580 cln_debug_pub.Add('Value :' || p_value,2);
581 cln_debug_pub.Add('Format :' || p_format,2);
582 END IF;
583 IF p_format is NULL THEN
584 l_temp := to_number(p_value);
585 ELSE
586 l_temp := to_number(p_value,p_format);
587 END IF;
588 IF (l_Debug_Level <= 2) THEN
589 cln_debug_pub.Add('-----------Exiting IS_VALID_NUMBER_FORMAT successfully-----------', 2);
590 END IF;
591 RETURN TRUE;
592 EXCEPTION
593 WHEN OTHERS THEN
594 IF (l_Debug_Level <= 2) THEN
595 cln_debug_pub.Add('-----------Exiting IS_VALID_NUMBER_FORMAT errornously-----------', 2);
596 END IF;
597 --Failed Number Validation
601 p_value IN VARCHAR2,
598 Return FALSE;
599 END;
600 FUNCTION CONVERT_TO_NUMBER(
602 p_return IN OUT NOCOPY NUMBER) RETURN BOOLEAN
603 IS
604 BEGIN
605 IF (l_Debug_Level <= 2) THEN
606 cln_debug_pub.Add('-----------Entering CONVERT_TO_NUMBER-----------', 2);
607 cln_debug_pub.Add('Value :' || p_value,2);
608 END IF;
609 p_return := to_number(p_value);
610 IF (l_Debug_Level <= 2) THEN
611 cln_debug_pub.Add('-----------Exiting CONVERT_TO_NUMBER-----------', 2);
612 cln_debug_pub.Add('Return :' || p_return,2);
613 END IF;
614 RETURN TRUE;
615 EXCEPTION
616 WHEN OTHERS THEN
617 IF (l_Debug_Level <= 2) THEN
618 cln_debug_pub.Add('-----------Exiting CONVERT_TO_NUMBER errornously-----------', 2);
619 END IF;
620 --Failed Number Validation
621 Return FALSE;
622 END;
623 FUNCTION VALIDATE_ELEMENT(
624 p_name IN VARCHAR2,
625 p_value IN VARCHAR2,
626 p_min_length IN NUMBER,
627 p_max_length IN NUMBER,
628 p_type IN VARCHAR2,
629 p_format IN VARCHAR2,
630 x_error_message IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN
631 IS
632 l_field_length NUMBER;
633 l_value VARCHAR2(1000);
634 l_part_of_value VARCHAR2(100);
635 l_num_value NUMBER;
636 l_orcl_format VARCHAR2(100);
637 VALIDATION_FAILED EXCEPTION;
638 l_validation_info VARCHAR2(1000);
639 BEGIN
640 IF (l_Debug_Level <= 2) THEN
641 cln_debug_pub.Add('-----------ENTERING VALIDATE_ELEMENT-----------', 2);
642 cln_debug_pub.Add('Node Name :' || p_name,2);
643 cln_debug_pub.Add('Node Value:' || p_value,2);
644 cln_debug_pub.Add('Validation Information. Min Len : ' || p_min_length ||
645 ',Max Len : ' ||p_max_length ||
646 ',Type : ' ||p_type ||
647 ',Format : ' ||p_Format ,2);
648 END IF;
649 -- In case of error following error message is thrown
650 l_field_length := nvl(length(p_value),0);
651 IF (l_Debug_Level <= 1) THEN
652 cln_debug_pub.Add('About to do minimum lenght validation, l_field_lenght : ' || l_field_length,1);
653 END IF;
654 IF (p_min_length is not null) and (l_field_length < p_min_length) THEN
655 x_error_message := x_error_message || 'Minimum Length Validation Failed';
656 IF (l_Debug_Level <= 1) THEN
657 cln_debug_pub.Add(x_error_message,1);
658 END IF;
659 l_validation_info := 'Minimum Length Validation';
660 RAISE VALIDATION_FAILED;
661 END IF;
662 IF (l_Debug_Level <= 1) THEN
663 cln_debug_pub.Add('About to do maximum lenght validation',1);
664 END IF;
665 IF (p_max_length is not null) and (l_field_length > p_max_length) THEN
666 l_validation_info := 'Maximum Length Validation';
667 RAISE VALIDATION_FAILED;
668 END IF;
669 IF (l_Debug_Level <= 1) THEN
670 cln_debug_pub.Add('About to do type validation : '|| p_type,1);
671 END IF;
672 IF p_type is not null THEN
673 l_validation_info := 'Data Type Validation';
674 IF p_type = 'Date' THEN
675 l_value := rtrim(ltrim(p_value));
676 IF(length(l_value) <> 9) THEN
677 RAISE VALIDATION_FAILED;
678 END IF;
679 l_part_of_value := substr(l_value,1,8);
680 IF (l_Debug_Level <= 1) THEN
681 cln_debug_pub.Add('Part of Value : '|| l_part_of_value,1);
682 END IF;
683 IF NOT is_valid_date_format(l_part_of_value,'YYYYMMDD') THEN
684 RAISE VALIDATION_FAILED;
685 END IF;
686 ELSIF p_type = 'DateTime' THEN
687 l_value := rtrim(ltrim(p_value));
688 IF(length(l_value) <> 20) THEN
689 RAISE VALIDATION_FAILED;
690 END IF;
691 l_part_of_value := substr(l_value,1,8);
692 IF (l_Debug_Level <= 1) THEN
693 cln_debug_pub.Add('Part of Value : '|| l_part_of_value,1);
694 END IF;
695 IF NOT is_valid_date_format(l_part_of_value,'YYYYMMDD') THEN
696 RAISE VALIDATION_FAILED;
697 END IF;
698 l_part_of_value := substr(l_value,10,6);
699 IF (l_Debug_Level <= 1) THEN
700 cln_debug_pub.Add('Part of Value : '|| l_part_of_value,1);
701 END IF;
702 IF NOT is_valid_date_format(l_part_of_value,'HH24MISS') THEN
703 RAISE VALIDATION_FAILED;
704 END IF;
705 BEGIN
706 l_part_of_value := substr(l_value,17,3);
707 IF (l_Debug_Level <= 1) THEN
708 cln_debug_pub.Add('Part of Value : '|| l_part_of_value,1);
709 END IF;
710 IF NOT is_valid_number_format(l_part_of_value) THEN
711 RAISE VALIDATION_FAILED;
712 END IF;
713 EXCEPTION
717 ELSIF p_type = 'Time' THEN
714 WHEN OTHERS THEN
715 NULL;-- ignore the exception
716 END;
718 l_value := rtrim(ltrim(p_value));
719 IF(length(l_value) <> 11) THEN
720 RAISE VALIDATION_FAILED;
721 END IF;
722 l_part_of_value := substr(l_value,1,6);
723 IF NOT is_valid_date_format(l_part_of_value,'HH24MISS') THEN
724 RAISE VALIDATION_FAILED;
725 END IF;
726 BEGIN
727 l_part_of_value := substr(rtrim(ltrim(l_value)),8,3);
728 IF NOT is_valid_number_format(l_part_of_value) THEN
729 RAISE VALIDATION_FAILED;
730 END IF;
731 EXCEPTION
732 WHEN OTHERS THEN
733 NULL;
734 END;
735 ELSIF p_type = 'Integer' or p_type = 'NaturalNumber'
736 OR p_type = 'PositiveInteger' or p_type = 'Real'
737 THEN
738 IF (l_Debug_Level <= 1) THEN
739 cln_debug_pub.Add('About to do format validation : '|| l_orcl_format,1);
740 END IF;
741 IF p_format is NOT NULL THEN
742 IF( NOT is_valid_number_format(l_value,p_format) ) THEN
743 RAISE VALIDATION_FAILED;
744 END IF;
745 ELSE --Format not specified, so do datatype validation
746 IF not convert_to_number(p_value,l_num_value) THEN
747 RAISE VALIDATION_FAILED;
748 END IF;
749 IF p_type = 'Integer' THEN
750 IF( l_num_value - round(l_num_value) <> 0 ) THEN
751 RAISE VALIDATION_FAILED;
752 END IF;
753 ELSIF p_type = 'PositiveInteger' THEN
754 IF( l_num_value - round(l_num_value) <> 0 or l_num_value < 0 ) THEN
755 RAISE VALIDATION_FAILED;
756 END IF;
757 ELSIF p_type = 'NaturalNumber' THEN
758 IF( l_num_value - round(l_num_value) <> 0 or l_num_value <= 0 ) THEN
759 RAISE VALIDATION_FAILED;
760 END IF;
761 END IF;
762 END IF;
763 END IF;
764 END IF;
765 IF (l_Debug_Level <= 2) THEN
766 cln_debug_pub.Add('-----------EXITING VALIDATE_ELEMENT-----------', 2);
767 END IF;
768 RETURN TRUE;
769 EXCEPTION
770 WHEN VALIDATION_FAILED THEN
771 x_error_message := 'Validation Failed For the element : '|| p_name || ' Value : '|| p_value || ' Validation : ' || l_validation_info;
772 IF (l_Debug_Level <= 5) THEN
773 cln_debug_pub.Add(x_error_message, 6);
774 END IF;
775 RETURN FALSE;
776 WHEN OTHERS THEN
777 x_error_message := 'Unknown exception while doing the validations for element : '|| p_name || ' Value : '|| p_value || ' Validation : ' || l_validation_info;
778 IF (l_Debug_Level <= 5) THEN
779 cln_debug_pub.Add(x_error_message, 6);
780 END IF;
781 RETURN FALSE;
782 END VALIDATE_ELEMENT;
783 PROCEDURE VALIDATE_XML(
784 p_itemtype IN VARCHAR2,
785 p_itemkey IN VARCHAR2,
786 p_actid IN NUMBER,
787 p_funcmode IN VARCHAR2,
788 x_resultout IN OUT NOCOPY VARCHAR2
789 )
790 IS
791 l_xmlDoc CLOB;
792 l_payload CLOB;
793 l_parser xmlparser.parser;
794 l_domDoc xmldom.DOMDocument;
795 l_node xmldom.domNode;
796 l_nodelist xmldom.DOMNodeList;
797 l_nodelistlen NUMBER;
798 l_error_code VARCHAR2(255);
799 l_error_msg VARCHAR2(1000);
800 l_msg_data VARCHAR2(1000);
801 l_ini_pos NUMBER;
802 l_fin_pos NUMBER;
803 l_amount INTEGER;
804 l_eventmsg WF_EVENT_T;
805 l_name VARCHAR2(1000);
806 l_value VARCHAR2(1000);
807 l_error_message VARCHAR2(1000);
808 l_start_timestamp date;
809 l_root_element VARCHAR2(100);
810 VALIDATION_FAILED EXCEPTION;
811 l_fnd_error_msg VARCHAR2(1000);
812 TYPE t_min_len_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
813 TYPE t_max_len_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
814 TYPE t_data_type_list is TABLE of VARCHAR2(100) INDEX BY BINARY_INTEGER;
815 TYPE t_format_list is TABLE of VARCHAR2(100) INDEX BY BINARY_INTEGER;
816 TYPE t_xml_element is TABLE of VARCHAR2(255) INDEX BY BINARY_INTEGER;
817 l_min_lengths t_min_len_list;
818 l_max_lengths t_min_len_list;
819 l_types t_data_type_list;
820 l_formats t_format_list;
821 l_xml_elements t_xml_element;
822 TYPE t_validations_tables is TABLE of CLN_RN_VALIDATIONS%ROWTYPE INDEX BY BINARY_INTEGER;
823 l_tab_validations t_validations_tables;
824 CURSOR c_validations(p_document_type in varchar2) IS SELECT XML_ELEMENT, MINIMUM_LENGTH, MAXIMUM_LENGTH, DATA_TYPE, oracle_format_mask
825 FROM CLN_RN_VALIDATIONS
826 WHERE document_type = p_document_type;
827 BEGIN
828 x_resultout := 'SUCCESS';
829 l_parser := xmlparser.newParser;
830 l_start_timestamp := sysdate;
831 IF (l_Debug_Level <= 2) THEN
832 cln_debug_pub.Add('-----------ENTERING VALIDATE_XML-----------', 2);
833 END IF;
834 IF (l_Debug_Level <= 1) THEN
835 cln_debug_pub.Add('WITH PARAMETERS', 1);
836 cln_debug_pub.Add('p_itemtype:' || p_itemtype, 1);
837 cln_debug_pub.Add('p_itemkey:' || p_itemkey, 1);
838 cln_debug_pub.Add('p_actid:' || p_actid, 1);
839 cln_debug_pub.Add('p_funcmode:' || p_funcmode, 1);
840 END IF;
841 l_eventmsg := wf_engine.getActivityAttrEvent(p_itemtype, p_itemkey, p_actid, 'CLN_EVENT_MESSAGE');
842 l_xmlDoc := l_eventmsg.getEventData;
843 l_ini_pos := -1;
844 l_ini_pos := dbms_lob.instr(l_xmlDoc, '!DOCTYPE ');
845 IF (l_Debug_Level <= 1) THEN
846 cln_debug_pub.Add('Init Position:' || l_ini_pos, 1);
847 END IF;
848 IF (l_ini_pos > 0) THEN
849 l_fin_pos := dbms_lob.instr(l_xmlDoc, '>', l_ini_pos);
850 l_fin_pos := l_fin_pos + 1;
851 l_amount := dbms_lob.getlength(l_xmlDoc);
852 IF (l_Debug_Level <= 1) THEN
853 cln_debug_pub.Add('Final Position:' || l_fin_pos, 1);
854 cln_debug_pub.Add('Length:' || l_amount, 1);
855 END IF;
856 DBMS_LOB.CREATETEMPORARY(l_payload, TRUE, DBMS_LOB.SESSION);
857 dbms_lob.copy(l_payload, l_xmlDoc, l_amount - l_fin_pos + 10, 1, l_fin_pos);
858 END IF;
859 l_parser := xmlparser.newparser;
860 xmlparser.setValidationMode(l_parser,FALSE);
861 xmlparser.showWarnings(l_parser,FALSE);
862 BEGIN
863 IF (l_ini_pos > 0) THEN
864 xmlparser.parseClob(l_parser,l_payload);
865 ELSE
866 xmlparser.parseClob(l_parser,l_xmlDoc);
867 END IF;
868 l_domDoc := xmlparser.getDocument(l_parser);
869 IF (l_Debug_Level <= 1) THEN
870 cln_debug_pub.Add('About to get root element', 1);
871 END IF;
872 l_root_element:= xmldom.getNodeName( xmldom.makeNode(xmldom.getDocumentElement(l_domDoc))); -- Getting the root element of the document
873 IF (l_Debug_Level <= 1) THEN
874 cln_debug_pub.Add('Root element :' || l_root_element, 1);
875 END IF;
876 OPEN c_validations(l_root_element);
877 FETCH c_validations BULK COLLECT INTO l_xml_elements, l_min_lengths,l_max_lengths,l_types, l_formats;
878 IF c_validations%NOTFOUND THEN
879 Null; --Error
880 END IF;
881 CLOSE c_validations;
882 FOR i in 1..l_xml_elements.count LOOP
883 IF (l_Debug_Level <= 1) THEN
884 cln_debug_pub.Add('In the loop for iteration:' || i, 1);
885 cln_debug_pub.Add('l_xml_elements:' || l_xml_elements(i), 1);
886 cln_debug_pub.Add('l_min_lengths:' || l_min_lengths(i), 1);
887 cln_debug_pub.Add('l_max_lengths:' || l_max_lengths(i), 1);
888 cln_debug_pub.Add('l_types:' || l_types(i), 1);
889 cln_debug_pub.Add('l_formats:' || l_formats(i), 1);
890 END IF;
891 l_nodelist := xmldom.getElementsByTagName(l_domDoc, l_xml_elements(i));
892 l_nodelistlen := xmldom.getLength(l_nodelist);
893 IF (l_Debug_Level <= 1) THEN
894 cln_debug_pub.Add('Number of element found :' || l_nodelistlen, 1);
895 END IF;
896 FOR l_counter IN 0..l_nodelistlen-1 LOOP
897 IF (l_Debug_Level <= 1) THEN
898 cln_debug_pub.Add('Trying to do validation - loop counter:' || l_counter, 1);
899 END IF;
900 l_node := xmldom.item(l_nodelist, l_counter);
901 --l_name := xmldom.getNodeName(l_node);
902 l_node := xmldom.getFirstChild(l_node);
903 IF xmldom.isNull(l_node) THEN
904 l_value := null;
905 IF (l_Debug_Level <= 1) THEN
906 cln_debug_pub.Add('Value is null. So no need to do validation', 1);
907 END IF;
908 -- Need not do validation for null nodes
909 /*IF NOT VALIDATE_ELEMENT(l_xml_elements(i),l_value, l_min_lengths(i),l_max_lengths(i),l_types(i),l_formats(i), l_error_message) THEN
910 --Validation failed
911 RAISE Validation_Failed;
912 END IF;*/
913 ELSIF xmldom.getNodeType(l_node) = xmldom.TEXT_NODE THEN -- get the text node associated with the element node
914 l_value := xmldom.getNodeValue(l_node);
915 IF (l_Debug_Level <= 1) THEN
916 cln_debug_pub.Add('Value of the tag : ' || l_value , 1);
917 END IF;
918 IF( (l_value is not null)) THEN -- Need not do validation for nodes that doesnt have values
919 IF (l_Debug_Level <= 1) THEN
920 cln_debug_pub.Add('About to call validate element', 1);
921 END IF;
922 IF NOT VALIDATE_ELEMENT(l_xml_elements(i),l_value, l_min_lengths(i),l_max_lengths(i),l_types(i),l_formats(i), l_error_message) THEN
923 --Validation failed
924 RAISE Validation_Failed;
925 END IF;
926 END IF;
927 END IF;
928 END LOOP;
929 END LOOP;
930 EXCEPTION
931 WHEN Validation_FAILED THEN
932 x_resultout := 'FAIL:'||l_error_message;
933 IF (l_Debug_Level <= 5) THEN
934 cln_debug_pub.Add('Validation Failed With messge :'||l_error_message ,6);
935 END IF;
936 -- Added for 3C4 messages
937 FND_MESSAGE.SET_NAME('CLN','M4R_3C4_XML_VALIDATION_FAIL');
938 FND_MESSAGE.SET_TOKEN('ERRMSG',l_error_message);
939 l_fnd_error_msg:= FND_MESSAGE.GET;
940 wf_engine.SetItemAttrText(p_itemtype,p_itemkey,'CH_MESSAGE_VALIDATION_FAILED',l_fnd_error_msg);
941 CLN_NP_PROCESSOR_PKG.Notify_administrator('RosettaNet Validations Failed for the XML message in the Workflow ' || ' ' ||
942 'Item Type: ' || p_itemtype || ', ' ||
943 'Item Key: ' || p_itemkey || ', ' ||
944 'Error: ' || l_error_message);
945 WHEN OTHERS THEN
946 l_error_code := SQLCODE;
947 l_error_msg := SQLERRM;
948 l_msg_data := l_error_code||' : '||l_error_msg;
949 x_resultout := 'ERROR:'||l_msg_data;
950 IF (l_Debug_Level <= 5) THEN
951 cln_debug_pub.Add(l_msg_data,6);
952 END IF;
953 l_error_message := l_msg_data;
954 END;
955 IF (l_ini_pos > 0) THEN
956 DBMS_LOB.FREETEMPORARY(l_payload);
957 END IF;
958 xmlparser.freeparser(l_parser);
959 IF (l_Debug_Level <= 2) THEN
960 cln_debug_pub.Add('EXITING VALIDATE_XML normally', 2);
961 cln_debug_pub.Add('Time Taken in seconds : ' || to_char(24.0*60.0*60.0*(sysdate - l_start_timestamp),'99999999999.9999999'),1);
962 END IF;
963 EXCEPTION
964 WHEN OTHERS THEN
965 l_error_code := SQLCODE;
966 l_error_msg := SQLERRM;
967 l_msg_data := l_error_code||' : '||l_error_msg;
968 x_resultout := 'ERROR:'||l_msg_data;
969 IF (l_Debug_Level <= 5) THEN
970 cln_debug_pub.Add(l_msg_data,6);
971 cln_debug_pub.Add('EXITING VALIDATE_XML with others error', 1);
972 END IF;
973 END VALIDATE_XML;
974 PROCEDURE GET_ITEM_CONFIG_PARAMS(
975 p_item_config_dtl_tag IN VARCHAR2,
976 x_top_model_line_id OUT NOCOPY VARCHAR2,
977 x_link_to_line_id OUT NOCOPY VARCHAR2
978 )
979 IS
980 delim_pos NUMBER;
981 BEGIN
982 IF (l_debug_level <= 2) THEN
983 cln_debug_pub.Add('----- Entering cln_rn_utl.get_item_config_params API ------- ',2);
984 END IF;
985 IF (l_debug_level <= 1) THEN
986 cln_debug_pub.Add('----- received parameters ------- ',1);
987 cln_debug_pub.Add('p_item_config_dtl_tag - ' || p_item_config_dtl_tag,1);
988 END IF;
989 x_top_model_line_id := NULL;
990 x_link_to_line_id := NULL;
991 delim_pos := instr(p_item_config_dtl_tag,':',1,1);
992 IF delim_pos > 0 THEN
993 x_top_model_line_id := substr(p_item_config_dtl_tag,1,delim_pos-1);
994 x_link_to_line_id := substr(p_item_config_dtl_tag,delim_pos+1);
995 END IF;
996 IF (l_debug_level <= 1) THEN
997 cln_debug_pub.Add('----- returning values ------- ',1);
998 cln_debug_pub.Add('x_top_model_line_id - ' || x_top_model_line_id, 1);
999 cln_debug_pub.Add('x_link_to_line_id - ' || x_link_to_line_id , 1);
1000 END IF;
1001 IF (l_debug_level <= 2) THEN
1002 cln_debug_pub.Add('----- Exiting cln_rn_utl.get_item_config_params API ------- ',2);
1003 END IF;
1004 END;
1005 PROCEDURE CREATE_ITEM_CONFIG_TAG(
1006 p_top_model_line_id IN VARCHAR2,
1007 p_link_to_line_id IN VARCHAR2,
1008 x_item_config_dtl_tag OUT NOCOPY VARCHAR2
1009 )
1010 IS
1011 BEGIN
1012 IF (l_debug_level <= 2) THEN
1013 cln_debug_pub.Add('----- Entering cln_rn_utl.create_item_config_tag API ------- ',2);
1014 END IF;
1015 IF (l_debug_level <= 1) THEN
1016 cln_debug_pub.Add('----- received parameters ------- ',1);
1017 cln_debug_pub.Add('p_top_model_line_id - ' || p_top_model_line_id, 1);
1018 cln_debug_pub.Add('p_link_to_line_id - ' || p_link_to_line_id , 1);
1019 END IF;
1020 IF (p_top_model_line_id IS NULL) AND (p_link_to_line_id IS NULL) THEN
1021 x_item_config_dtl_tag := NULL;
1022 ELSE
1023 x_item_config_dtl_tag := p_top_model_line_id || ':' || p_link_to_line_id;
1024 END IF;
1025 IF (l_debug_level <= 1) THEN
1026 cln_debug_pub.Add('----- returning values ------- ',1);
1027 cln_debug_pub.Add('x_item_config_dtl_tag - ' || x_item_config_dtl_tag, 1);
1028 END IF;
1029 IF (l_debug_level <= 2) THEN
1030 cln_debug_pub.Add('----- Exiting cln_rn_utl.create_item_config_tag API ------- ',2);
1031 END IF;
1032 END;
1033
1034
1035 Procedure get_user_id
1036 (p_user_name IN VARCHAR,
1037 x_user_id OUT NOCOPY NUMBER,
1038 x_error_code OUT NOCOPY NUMBER,
1039 x_error_message OUT NOCOPY VARCHAR) is
1040 l_count NUMBER;
1041 BEGIN
1042 x_error_code := 0;
1043 select count(*)
1044 into l_count
1045 from fnd_user
1046 where user_name = p_user_name;
1047 if (l_count = 0) then
1048 x_error_code := 1;
1049 x_user_id := 0;
1050 x_error_message := 'Invalid User Name ' || p_user_name;
1051 else
1052 select user_id
1053 into x_user_id
1054 from fnd_user
1055 where user_name = p_user_name;
1056 end if;
1057 END get_user_id;
1058
1059
1060 PROCEDURE getPurchaseOrderNum(p_PoAndRel IN VARCHAR2,
1061 x_PoNum OUT NOCOPY VARCHAR2) IS
1062 l_RelExists VARCHAR2(100);
1063 l_error_code NUMBER;
1064 l_error_msg VARCHAR2(1000);
1065 BEGIN
1066 IF (l_Debug_Level <= 2) THEN
1067 cln_debug_pub.Add('----- Entering cln_rn_util.getPurchaseOrderNum API ------- ',2);
1068 END IF;
1069 IF (l_Debug_Level <= 1) THEN
1070 cln_debug_pub.Add('p_PoAndRel:' || p_PoAndRel,1);
1071 END IF;
1072 l_RelExists := INSTR(p_PoAndRel, '-', 1, 1);
1073 if(l_RelExists = 0) then
1074 x_PoNum := p_PoAndRel;
1075 else
1076 x_PoNum := RTRIM(RTRIM(p_PoAndRel, '0123456789'), '-');
1077 end if;
1078 IF (l_Debug_Level <= 2) THEN
1079 cln_debug_pub.Add('----- Exiting cln_rn_util.getPurchaseOrderNum API ------- ',2);
1080 END IF;
1081 EXCEPTION
1082 WHEN OTHERS THEN
1083 l_error_code := SQLCODE;
1084 l_error_msg := SQLERRM;
1085 IF (l_Debug_Level <= 1) THEN
1086 cln_debug_pub.Add('Exception ' || ':' || l_error_code || ':' || l_error_msg,1);
1087 END IF;
1088 END getPurchaseOrderNum;
1089
1090 PROCEDURE getRelNum(p_PoAndRel IN VARCHAR2,
1091 x_RelNum OUT NOCOPY VARCHAR2) IS
1092 l_modifiedString VARCHAR2(100) := '000';
1093 l_error_code NUMBER;
1094 l_error_msg VARCHAR2(1000);
1095 BEGIN
1096 x_RelNum := LTRIM(LTRIM(p_PoAndRel, '0123456789'), '-');
1097 EXCEPTION
1098 WHEN OTHERS THEN
1099 l_error_code := SQLCODE;
1100 l_error_msg := SQLERRM;
1101 IF (l_Debug_Level <= 1) THEN
1102 cln_debug_pub.Add('Exception ' || ':' || l_error_code || ':' || l_error_msg,1);
1103 END IF;
1104 END getRelNum;
1105
1106 PROCEDURE getRelNum(p_PoAndRel IN VARCHAR2,
1107 x_RelNum OUT NOCOPY NUMBER) IS
1108 l_modifiedString VARCHAR2(100) := '000';
1109 l_error_code NUMBER;
1110 l_error_msg VARCHAR2(1000);
1111 BEGIN
1112 x_RelNum :=TO_NUMBER( LTRIM(LTRIM(p_PoAndRel, '0123456789'), '-'));
1113 EXCEPTION
1114 WHEN OTHERS THEN
1115 l_error_code := SQLCODE;
1116 l_error_msg := SQLERRM;
1117 IF (l_Debug_Level <= 1) THEN
1118 cln_debug_pub.Add('Exception ' || ':' || l_error_code || ':' || l_error_msg,1);
1119 END IF;
1120 END getRelNum;
1121
1122 PROCEDURE getRevNum
1123 (p_PORELANDREV IN varchar2,
1124 x_porel OUT NOCOPY varchar2,
1125 x_revnum OUT NOCOPY VARCHAR2) IS
1126 l_modifiedString VARCHAR2(100) := '000';
1127 l_error_code NUMBER;
1128 l_error_msg VARCHAR2(1000);
1129 BEGIN
1130 IF (l_Debug_Level <= 5) THEN
1131 cln_debug_pub.Add('Entered get RevNum(p_PORELANDREV,x_porel,x_revnum)');
1132 cln_debug_pub.Add('p_PORELANDREV :' || p_PORELANDREV,1);
1133 END IF;
1134
1135 x_RevNum :=LTRIM(LTRIM(p_PORELANDREV, '0123456789-'), ':');
1136 x_porel :=RTRIM(RTRIM(p_PORELANDREV, '0123456789-'), ':');
1137
1138 IF (l_Debug_Level <= 5) THEN
1139 cln_debug_pub.Add('x_porel :' || x_porel);
1140 cln_debug_pub.Add('x_revnum :' || x_revnum ,1);
1141 END IF;
1142
1143
1144 EXCEPTION
1145 WHEN OTHERS THEN
1146 l_error_code := SQLCODE;
1147 l_error_msg := SQLERRM;
1148 IF (l_Debug_Level <= 1) THEN
1149 cln_debug_pub.Add('Exception ' || ':' || l_error_code || ':' || l_error_msg,1);
1150 END IF;
1151 END getRevNum;
1152
1153
1154 PROCEDURE CONCAT_PO_RELNUM
1155 (p_ponum IN VARCHAR2,
1156 p_porelnum IN VARCHAR2,
1157 x_poandrelnum OUT NOCOPY VARCHAR2) IS
1158 BEGIN
1159 if (p_porelnum is NULL) or (p_porelnum = 0) then
1160 x_poandrelnum := p_ponum;
1161 else
1162 x_poandrelnum := p_ponum || '-' || p_porelnum;
1163 end if;
1164 END CONCAT_PO_RELNUM;
1165
1166
1167 PROCEDURE CONCAT_PORELNUM_REVNUM
1168 (p_porelnum IN VARCHAR2,
1169 p_porevnum IN VARCHAR2,
1170 x_porelrevnum OUT NOCOPY VARCHAR2) IS
1171 BEGIN
1172 if (p_porevnum is NULL) or (p_porevnum = 0) then
1173 x_porelrevnum := p_porelnum;
1174 else
1175 x_porelrevnum := p_porelnum || ':' || p_porevnum;
1176 end if;
1177 END CONCAT_PORELNUM_REVNUM;
1178
1179
1180
1181 PROCEDURE getTagParamValue(
1182 p_xml_tag IN VARCHAR2,
1183 p_param IN vARCHAR2,
1184 x_value OUT NOCOPY VARCHAR2) IS
1185 l_PipeExists NUMBER;
1186 l_EqualExists NUMBER;
1187 l_error_code NUMBER;
1188 l_error_msg VARCHAR2(1000);
1189 l_remaining_part VARCHAR2(1000);
1190 l_part VARCHAR2(1000);
1191 l_name VARCHAR2(1000);
1192
1193 BEGIN
1194 IF (l_Debug_Level <= 2) THEN
1195 cln_debug_pub.Add('----- Entering cln_rn_util.getTagParamValue API ------- ',2);
1196 END IF;
1197 IF (l_Debug_Level <= 1) THEN
1198 cln_debug_pub.Add('p_xml_tag:' || p_xml_tag,1);
1199 cln_debug_pub.Add('p_param:' || p_param,1);
1200 END IF;
1201 x_value := null;
1202 l_remaining_part := p_xml_tag;
1203 while length(l_remaining_part) > 0 loop
1204 l_PipeExists := INSTR(l_remaining_part, '|', 1, 1);
1205 if(l_PipeExists > 0) then
1206 l_part := substr(l_remaining_part,1,l_PipeExists-1);
1207 l_remaining_part := substr(l_remaining_part,l_PipeExists+1);
1208 else
1209 l_part := l_remaining_part;
1210 l_remaining_part := '';
1211 end if;
1212 l_EqualExists := INSTR(l_part, '=', 1, 1);
1213 if(l_EqualExists > 0) then
1214 l_name := substr(l_part,1,l_EqualExists-1);
1215 if (l_name = p_param) then
1216 x_value := substr(l_part,l_EqualExists+1);
1217 IF (l_Debug_Level <= 2) THEN
1218 cln_debug_pub.Add('----- Exiting cln_rn_util.getTagParamValue API - param found ' || substr(l_part,l_EqualExists+1) ,2);
1219 END IF;
1220 return;
1221 end if;
1222 end if;
1223 IF (l_Debug_Level <= 1) THEN
1224 cln_debug_pub.Add('l_remaining_part:' || l_remaining_part,1);
1225 END IF;
1226 end loop;
1227 IF (l_Debug_Level <= 2) THEN
1228 cln_debug_pub.Add('----- Exiting cln_rn_util.getTagParamValue API - Param not found ------- ',2);
1229 END IF;
1230 EXCEPTION
1231 when others then
1232 l_error_code := SQLCODE;
1233 l_error_msg := SQLERRM;
1234 IF (l_Debug_Level <= 5) THEN
1235 cln_debug_pub.Add('Exception ' || ':' || l_error_code || ':' || l_error_msg,1);
1236 END IF;
1237 x_value := null;
1238 END getTagParamValue;
1239
1240 PROCEDURE Get_tag_value_from_xml(
1241 p_internal_control_num IN NUMBER,
1242 p_tag_path IN VARCHAR2,
1243 x_tag_value IN OUT NOCOPY VARCHAR2
1244 )
1245 IS
1246 l_xmlDoc CLOB;
1247 l_parser xmlparser.parser := xmlparser.newParser;
1248 l_domDoc xmldom.DOMDocument;
1249 l_node xmldom.domNode;
1250 l_element xmldom.domElement;
1251 l_nodeList xmldom.domNodeList;
1252 l_size number;
1253 l_Nname varchar2(255);
1254 l_Nvalue varchar2(255);
1255 l_error_code VARCHAR2(255);
1256 l_error_msg VARCHAR2(1000);
1257 l_msg_data VARCHAR2(1000);
1258 l_payload CLOB;
1259 l_ini_pos NUMBER(38);
1260 l_fin_pos NUMBER(38);
1261 l_amount INTEGER;
1262 l_PipeExists NUMBER;
1263 l_remaining_part VARCHAR2(1000);
1264 l_part VARCHAR2(1000);
1265 BEGIN
1266 IF (l_Debug_Level <= 2) THEN
1267 cln_debug_pub.Add('-----------ENTERING Get_tag_value_from_xml-----------', 2);
1268 END IF;
1269 IF (l_Debug_Level <= 2) THEN
1270 cln_debug_pub.Add('WITH PARAMETERS', 1);
1271 cln_debug_pub.Add('p_internal_control_num:' || p_internal_control_num, 1);
1272 cln_debug_pub.Add('p_tag_path:' || p_tag_path, 1);
1273 END IF;
1274
1275 x_tag_value := NULL;
1276
1277 SELECT payload into l_xmlDoc FROM ecx_doclogs WHERE internal_control_number = p_internal_control_num;
1278
1279 l_ini_pos := -1;
1280 l_ini_pos := dbms_lob.instr(l_xmlDoc, '!DOCTYPE ');
1281 IF (l_Debug_Level <= 2) THEN
1282 cln_debug_pub.Add('Init Position:' || l_ini_pos, 1);
1283 END IF;
1284
1285 IF (l_ini_pos > 0) THEN
1286 l_fin_pos := dbms_lob.instr(l_xmlDoc, '>', l_ini_pos);
1287 l_fin_pos := l_fin_pos + 1;
1288 l_amount := dbms_lob.getlength(l_xmlDoc);
1289
1290 IF (l_Debug_Level <= 2) THEN
1291 cln_debug_pub.Add('Final Position:' || l_fin_pos, 1);
1295 DBMS_LOB.CREATETEMPORARY(l_payload, TRUE, DBMS_LOB.SESSION);
1292 cln_debug_pub.Add('Length:' || l_amount, 1);
1293 END IF;
1294
1296
1297 dbms_lob.copy(l_payload, l_xmlDoc, l_amount - l_fin_pos + 10, 1, l_fin_pos);
1298
1299 END IF;
1300
1301 l_parser := xmlparser.newparser;
1302 xmlparser.setValidationMode(l_parser,FALSE);
1303 xmlparser.showWarnings(l_parser,FALSE);
1304
1305 BEGIN
1306
1307 IF (l_ini_pos > 0) THEN
1308 xmlparser.parseClob(l_parser,l_payload);
1309 ELSE
1310 xmlparser.parseClob(l_parser,l_xmlDoc);
1311 END IF;
1312
1313 l_domDoc := xmlparser.getDocument(l_parser);
1314 l_element := xmldom.getDocumentElement(l_domDoc);
1315
1316 --l_nodeList := Xmldom.getElementsByTagName(l_domDoc, 'CNTROLAREA');
1317 --l_element := xmldom.makeElement(xmldom.item( l_nodeList, 0 ));
1318 --l_nodeList := Xmldom.getElementsByTagName(l_element, 'REFERENCEID');
1319
1320
1321 l_remaining_part := p_tag_path;
1322 while length(l_remaining_part) > 0 loop
1323 l_PipeExists := INSTR(l_remaining_part, '/', 1, 1);
1324 if(l_PipeExists > 0) then
1325 l_part := substr(l_remaining_part,1,l_PipeExists-1);
1326 l_remaining_part := substr(l_remaining_part,l_PipeExists+1);
1327
1328 l_nodeList := Xmldom.getElementsByTagName(l_element, l_part);
1329 l_element := xmldom.makeElement(xmldom.item( l_nodeList, 0 ));
1330 else
1331 l_part := l_remaining_part;
1332 l_remaining_part := '';
1333
1334 l_nodeList := Xmldom.getElementsByTagName(l_element, l_part);
1335 end if;
1336
1337 IF (l_Debug_Level <= 1) THEN
1338 cln_debug_pub.Add('l_remaining_part:' || l_remaining_part,1);
1339 END IF;
1340 end loop;
1341
1342 --l_nodeList := Xmldom.getElementsByTagName(l_domDoc, l_part);
1343 l_node := xmldom.item( l_nodeList, 0 );
1344 l_Nvalue := xmldom.getNodeName(l_node);
1345 l_node := xmldom.getFirstChild(l_node);
1346
1347 IF NOT xmldom.IsNull(l_node) THEN
1348 x_tag_value := xmldom.getNodeValue(l_node);
1349 END IF;
1350
1351 EXCEPTION
1352 WHEN OTHERS THEN
1353 x_tag_value := NULL;
1354 l_error_code := SQLCODE;
1355 l_error_msg := SQLERRM;
1356 l_msg_data := l_error_code||' : '||l_error_msg;
1357 IF (l_Debug_Level <= 5) THEN
1358 cln_debug_pub.Add(l_msg_data,6);
1359 END IF;
1360 END;
1361
1362 IF (l_ini_pos > 0) THEN
1363 DBMS_LOB.FREETEMPORARY(l_payload);
1364 END IF;
1365 xmlparser.freeparser(l_parser);
1366
1367 IF (l_Debug_Level <= 5) THEN
1368 cln_debug_pub.Add('Application Reference ID:' || x_tag_value,1);
1369 END IF;
1370 IF (l_Debug_Level <= 2) THEN
1371 cln_debug_pub.Add('EXITING Get_tag_value_from_xml', 2);
1372 END IF;
1373 EXCEPTION
1374 WHEN OTHERS THEN
1375 l_error_code := SQLCODE;
1376 l_error_msg := SQLERRM;
1377 l_msg_data := l_error_code||' : '||l_error_msg;
1378 IF (l_Debug_Level <= 5) THEN
1379 cln_debug_pub.Add(l_msg_data,6);
1380 cln_debug_pub.Add('EXITING Get_tag_value_from_xml', 1);
1381 END IF;
1382 x_tag_value := NULL;
1383 END Get_tag_value_from_xml;
1384
1385
1386
1387 -- Name
1388 -- CONVERT_TO_RN_DATE_EVENT
1389 -- Purpose
1390 -- Converts a date value into RosettaNet date format and time format
1391 -- RosettaNet Date Format: YYYYMMDDZ Time Format : hhmmss.SSSZ
1392 -- Arguments
1393 -- Date
1394 -- Notes
1395 -- If the date passed is NULL, then sysdate is considered.
1396 PROCEDURE CONVERT_TO_RN_DATE_EVENT(
1397 p_server_date IN DATE,
1398 x_rn_date OUT NOCOPY VARCHAR2,
1399 x_rn_time OUT NOCOPY VARCHAR2)
1400 IS
1401 l_error_code NUMBER;
1402 l_utc_date DATE;
1403 l_milliseconds VARCHAR2(5);
1404 l_server_timezone VARCHAR2(30);
1405 l_error_msg VARCHAR2(255);
1406 l_msg_data VARCHAR2(255);
1407 BEGIN
1408 IF (l_Debug_Level <= 2) THEN
1409 cln_debug_pub.Add('----- Entering CONVERT_TO_RN_DATE_EVENT API ------- ',2);
1410 END IF;
1411 IF (l_Debug_Level <= 1) THEN
1412 cln_debug_pub.Add('User Entered Date --> '||p_server_date,1);
1413 END IF;
1414 IF(p_server_date is null) THEN
1415 x_rn_date := null;
1416 x_rn_time := null;
1417 IF (l_Debug_Level <= 1) THEN
1418 cln_debug_pub.Add('Null is passed. So exiting the procedure with null as return',1);
1419 END IF;
1420 RETURN;
1421 END IF;
1422 IF (l_Debug_Level <= 1) THEN
1423 cln_debug_pub.Add('Call CONVERT_TO_RN_TIMEZONE API....... ',1);
1424 END IF;
1425 CONVERT_TO_RN_TIMEZONE(
1426 p_input_date => p_server_date,
1427 x_utc_date => l_utc_date );
1428 IF (l_Debug_Level <= 1) THEN
1429 cln_debug_pub.Add('TimeStamp as per UTC '||l_utc_date,1);
1430 END IF;
1431 l_milliseconds := '000'; --We wont get milliseconds
1432 IF (l_Debug_Level <= 1) THEN
1433 cln_debug_pub.Add('Truncated Millisecond '||l_milliseconds,1);
1434 END IF;
1435 x_rn_date := TO_CHAR(l_utc_date,'YYYYMMDD')||'Z';
1436 x_rn_time := TO_CHAR(l_utc_date,'hh24miss')||'.'||l_milliseconds||'Z';
1440 END IF;
1437 IF (l_Debug_Level <= 1) THEN
1438 cln_debug_pub.Add('Date in Rosettanet Format '||x_rn_date,1);
1439 cln_debug_pub.Add('Time in Rosettanet Format '||x_rn_time,1);
1441 IF (l_Debug_Level <= 2) THEN
1442 cln_debug_pub.Add('----- Exiting CONVERT_TO_RN_DATE_EVENT API ------- ',2);
1443 END IF;
1444 -- Exception Handling
1445 EXCEPTION
1446 WHEN OTHERS THEN
1447 l_error_code := SQLCODE;
1448 l_error_msg := SQLERRM;
1449 l_msg_data := 'Unexpected Error -'||l_error_code||' : '||l_error_msg;
1450 IF (l_Debug_Level <= 5) THEN
1451 cln_debug_pub.Add(l_msg_data,6);
1452 cln_debug_pub.Add('------- ERROR: Exiting CONVERT_TO_RN_DATE_EVENT API --------- ',5);
1453 END IF;
1454 x_rn_date := null;
1455 x_rn_time := null;
1456 END CONVERT_TO_RN_DATE_EVENT;
1457
1458 -- Name
1459 -- CONVERT_TO_DB_DATE
1460 -- Purpose
1461 -- Converts a date value from RosettaNet date/datetime format to db format
1462 -- RosettaNet Date Format : YYYYMMDDZ
1463 -- RosettaNet Time Format : hhmmss.SSSZ
1464 -- Arguments
1465 -- Date
1466 -- Notes
1467 -- If the date passed is NULL, then sysdate is considered.
1468 PROCEDURE CONVERT_TO_DB_DATE(
1469 p_rn_date IN VARCHAR2,
1470 p_rn_time IN VARCHAR2,
1471 x_db_date OUT NOCOPY DATE)
1472 IS
1473 l_server_date DATE;
1474 l_utc_datetime DATE;
1475 l_count_t_appearanace NUMBER;
1476 l_error_code NUMBER;
1477 l_rn_frmt_date VARCHAR2(30);
1478 l_rn_timezone VARCHAR2(30);
1479 l_db_timezone VARCHAR2(30);
1480 l_error_msg VARCHAR2(255);
1481 l_msg_data VARCHAR2(255);
1482 BEGIN
1483 IF (l_Debug_Level <= 2) THEN
1484 cln_debug_pub.Add('----- Entering CONVERT_TO_DB_DATE API ------- ',2);
1485 END IF;
1486 IF (l_Debug_Level <= 1) THEN
1487 cln_debug_pub.Add('Rosettanet Date '||p_rn_date,1);
1488 cln_debug_pub.Add('Rosettanet Time '||p_rn_time,1);
1489 END IF;
1490 IF(p_rn_date is null) THEN
1491 x_db_date := null;
1492 IF (l_Debug_Level <= 1) THEN
1493 cln_debug_pub.Add('Null is passed. So exiting the procedure with NULL as return',1);
1494 END IF;
1495 RETURN;
1496 END IF;
1497 IF (p_rn_time is not null) THEN
1498 --Datetime Format: YYYYMMDDThhmmss.SSSZ
1499 l_rn_timezone := fnd_profile.value('CLN_RN_TIMEZONE');
1500 IF (l_Debug_Level <= 1) THEN
1501 cln_debug_pub.Add('TimeZone of the UTC '||l_rn_timezone,1);
1502 END IF;
1503 -- get the timezone of the db server
1504 l_db_timezone := FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE;
1505 IF (l_Debug_Level <= 1) THEN
1506 cln_debug_pub.Add('TimeZone of the DB server '||l_db_timezone,1);
1507 END IF;
1508 IF (l_Debug_Level <= 1) THEN
1509 cln_debug_pub.Add('Datetime Format: YYYYMMDDThhmmss.SSSZ',1);
1510 END IF;
1511 l_rn_frmt_date := substr(p_rn_date,1,8)||substr(p_rn_time,1,6);
1512 IF (l_Debug_Level <= 1) THEN
1513 cln_debug_pub.Add('Date After Formatting (String)'||l_rn_frmt_date,1);
1514 END IF;
1515 l_utc_datetime := TO_DATE(l_rn_frmt_date,'YYYYMMDDHH24MISS');
1516 IF (l_Debug_Level <= 1) THEN
1517 cln_debug_pub.Add('Date After Formatting (Date)'||l_utc_datetime,1);
1518 END IF;
1519 -- this function converts the datetime from the user entered/db timezone to UTC
1520 x_db_date := FND_TIMEZONES_PVT.adjust_datetime(l_utc_datetime,l_rn_timezone,l_db_timezone);
1521 IF (l_Debug_Level <= 1) THEN
1522 cln_debug_pub.Add('Date after conversion '||x_db_date,1);
1523 END IF;
1524 ELSE
1525 --Date Format : YYYYMMDDZ
1526 IF (l_Debug_Level <= 1) THEN
1527 cln_debug_pub.Add('Date Format : YYYYMMDDZ',1);
1528 END IF;
1529 l_rn_frmt_date := substr(p_rn_date,1,8);
1530 IF (l_Debug_Level <= 1) THEN
1531 cln_debug_pub.Add('Date After Formatting (String) '||l_rn_frmt_date,1);
1532 END IF;
1533 x_db_date := TO_DATE(l_rn_frmt_date,'YYYYMMDD');
1534 IF (l_Debug_Level <= 1) THEN
1535 cln_debug_pub.Add('Date After Formatting (Date)'||l_utc_datetime,1);
1536 END IF;
1537 END IF;
1538 IF (l_Debug_Level <= 2) THEN
1539 cln_debug_pub.Add('----- Exiting CONVERT_TO_DB_DATE API ------- ',2);
1540 END IF;
1541 -- Exception Handling
1542 EXCEPTION
1543 WHEN OTHERS THEN
1544 x_db_date := null;
1545 l_error_code := SQLCODE;
1546 l_error_msg := SQLERRM;
1547 l_msg_data := 'Unexpected Error -'||l_error_code||' : '||l_error_msg;
1548 IF (l_Debug_Level <= 5) THEN
1549 cln_debug_pub.Add(l_msg_data,6);
1550 cln_debug_pub.Add('------- ERROR: Exiting CONVERT_TO_DB_DATE API --------- ',5);
1551 END IF;
1552 END CONVERT_TO_DB_DATE;
1553
1554
1555 PROCEDURE TRUNCATE_STRING(
1556 p_instring in varchar2,
1557 p_numofchar in number,
1558 x_outstring out nocopy varchar2 )
1559 IS
1560 l_error_code NUMBER;
1561 l_error_msg VARCHAR2(255);
1562 l_msg_data VARCHAR2(255);
1563 BEGIN
1564 IF (l_Debug_Level <=2 ) THEN
1565 cln_debug_pub.Add('-------- ENTERING TRUNCATE_STRING ----------');
1566 END IF;
1567
1568 IF (l_Debug_Level <= 1) THEN
1569 cln_debug_pub.Add('input_string :'||p_instring ,1);
1570 cln_debug_pub.Add('num_char :'||p_numofchar,1);
1571 END IF;
1572
1573 select substr (p_instring,1,p_numofchar) into x_outstring from dual;
1574
1575 IF (l_Debug_Level <= 1) THEN
1576 cln_debug_pub.Add('output string :'||x_outstring ,1);
1577 END IF;
1578
1579 IF (l_Debug_Level <=2) THEN
1580 cln_debug_pub.Add('-------- EXITING TRUNCATE_STRING ----------');
1581 END IF;
1582
1583
1584 EXCEPTION
1585 WHEN OTHERS THEN
1586 l_error_code := SQLCODE;
1587 l_error_msg := SQLERRM;
1588 l_msg_data := 'Unexpected Error -'||l_error_code||' : '||l_error_msg;
1589 IF (l_Debug_Level <= 5) THEN
1590 cln_debug_pub.Add(l_msg_data,6);
1591 cln_debug_pub.Add('------- EXCEPTION IN TRUNCATE STRING--------- ',5);
1592 END IF;
1593 END TRUNCATE_STRING;
1594
1595 END CLN_RN_UTILS;
[Home] [Help]