[Home] [Help]
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;
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
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);
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;
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
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;
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
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
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;
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
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
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 );
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);
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);
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;
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
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
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;
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
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';
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;
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
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
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;
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
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
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;
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);
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);
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
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
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);
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);
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);
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
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
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);
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
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);
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;
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);
308: END IF;
309: END CONVERT_NUMBER_TO_CHAR;
310: -- Name
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);
308: END IF;
309: END CONVERT_NUMBER_TO_CHAR;
310: -- Name
311: -- GET_FROM_ROLE
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;
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;
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
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);
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);
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;
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
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);
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;
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
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
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;
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
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
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,
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
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
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);
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;
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
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);
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;
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
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)
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
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');
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
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
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));
526: IF (l_Debug_Level <= 1) THEN
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));
526: IF (l_Debug_Level <= 1) THEN
527: cln_debug_pub.Add('l_number_of_nines :' || l_number_of_nines,1);
528: END IF;
529: l_orcl_format := l_orcl_format || ltrim(rpad(' ',l_number_of_nines+1,'9')) ;
530: ELSE -- In the form of 9999
531: l_orcl_format := l_orcl_format || l_temp_format;
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;
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
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;
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);
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
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);
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
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;
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
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);
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
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
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
598: Return FALSE;
599: END;
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
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);
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
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
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;
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 ||
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 ||
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);
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;
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
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;
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;
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
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;
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;
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;
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;
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;
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
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;
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(
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);
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);
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;
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');
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;
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;
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;
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);
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;
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);
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
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);
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);
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;
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));
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);
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
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);
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);
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
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);
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;
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);
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
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(
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,
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);
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;
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);
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
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);
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;
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,
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);
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
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;
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
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);
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;
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:
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;
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;
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,
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,
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
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-'), ':');
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-'), ':');
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:
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:
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);
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;
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
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;
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);
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;
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:
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);
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:
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;
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:
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);
1292: cln_debug_pub.Add('Length:' || l_amount, 1);
1293: END IF;
1294:
1295: DBMS_LOB.CREATETEMPORARY(l_payload, TRUE, DBMS_LOB.SESSION);
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);
1292: cln_debug_pub.Add('Length:' || l_amount, 1);
1293: END IF;
1294:
1295: DBMS_LOB.CREATETEMPORARY(l_payload, TRUE, DBMS_LOB.SESSION);
1296:
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);
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
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;
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;
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;
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:
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;
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;
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
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 );
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);
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';
1437: IF (l_Debug_Level <= 1) THEN
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';
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);
1440: END IF;
1441: IF (l_Debug_Level <= 2) THEN
1442: cln_debug_pub.Add('----- Exiting CONVERT_TO_RN_DATE_EVENT API ------- ',2);
1435: x_rn_date := TO_CHAR(l_utc_date,'YYYYMMDD')||'Z';
1436: x_rn_time := TO_CHAR(l_utc_date,'hh24miss')||'.'||l_milliseconds||'Z';
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);
1440: END IF;
1441: IF (l_Debug_Level <= 2) THEN
1442: cln_debug_pub.Add('----- Exiting CONVERT_TO_RN_DATE_EVENT API ------- ',2);
1443: END IF;
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);
1440: END IF;
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;
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;
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);
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;
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
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
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
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;
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);
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);
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
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
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);
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);
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);
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
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:
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:
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);
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;
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:
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 ----------');
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
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:
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;