1 PACKAGE BODY PON_OEX_TIMEZONE_PKG as
2 /* $Header: PONOEXTB.pls 120.5 2010/12/09 07:04:01 sgulkota ship $ */
3
4 g_module constant varchar2(200) := 'PON.PLSQL.PON_OEX_TIMEZONE_PKG';
5
6 /**
7 * This function returns number because it used to be a java stored procedure
8 * It now takes in HZ_TIMEZONES.TIMEZONE_ID as the timezone name
9 * as per bug 3664385 - hz_timezones replaced by fnd_timezones
10 */
11 FUNCTION valid_zone (p_timeZone VARCHAR2) RETURN NUMBER IS
12 tz_id NUMBER;
13 valid NUMBER;
14 BEGIN
15 tz_id := TO_NUMBER(p_timeZone);
16
17 SELECT COUNT(upgrade_tz_id) INTO valid
18 FROM fnd_timezones_b
19 WHERE upgrade_tz_id = tz_id
20 and enabled_flag= 'Y';
21
22 RETURN valid;
23 END valid_zone;
24
25 FUNCTION convert_time (p_fromDate DATE,
26 p_fromZone VARCHAR2,
27 p_toZone VARCHAR2) RETURN DATE IS
28 toDate DATE;
29 status VARCHAR2(100);
30 msg_count NUMBER;
31 msg_data VARCHAR2(2000); --Fix for bug 10384460 - modified the msg_data from 100 to 2000
32 BEGIN
33 /* check if the timezones are the same */
34 IF p_fromZone = p_toZone THEN
35 RETURN p_fromDate;
36 END IF;
37
38 IF p_fromDate IS NULL THEN
39 RETURN NULL;
40 END IF;
41
42 /* use AR's api to do the conversion */
43 HZ_TIMEZONE_PUB.Get_Time(
44 p_api_version => 1.0,
45 p_init_msg_list => FND_API.G_FALSE,
46 p_source_tz_id => TO_NUMBER(p_fromZone),
47 p_dest_tz_id => TO_NUMBER(p_toZone),
48 p_source_day_time => p_fromDate,
49 x_dest_day_time => toDate,
50 x_return_status => status,
51 x_msg_count => msg_count,
52 x_msg_data => msg_data);
53
54 RETURN toDate;
55
56 END convert_time;
57
58 /*=========================================================================+
59 --
60 -- CONVERT_DATE_TO_USER_TZ will convert given set of dates to user timezone.
61 -- This procedure will be used mainly to covnert Negotiation Preview date,
62 -- Negotiation open date, and Negotiation Close Date to user timezone.
63 --
64 -- It will return user timezone based on userId passed. If value for
65 -- user timezone is null procedure will return timezone of the
66 -- Negotiation Creator.
67 --
68 -- This logic will be applicable to the date conversion as well. That is
69 -- it will convert the dates to Negotiation Creator's timezone if
70 -- value of user timezone is null.
71 --
72 -- This API can be used to retrieve formatted date for other dates also.
73 -- In that case user can pass
74 -- Parameters :
75 -- p_person_party_id IN - trading partner contact id of the user
76 -- p_auctioneer_user_name IN - trading partner contact name of
77 -- the Negotiation Creator
78 -- x_date_value1 IN OUT NOCOPY - Negotication Preview Date and
79 -- New Date value in user timezone
80 -- x_date_value2 IN OUT NOCOPY - Negotication Open Date and
81 -- New Date value in user timezone
82 -- x_date_value3 IN OUT NOCOPY - Negotication Close Date and
83 -- New Date value in user timezone
84 -- x_date_value4 IN OUT NOCOPY - Other misc dates
85 -- New Date value in user timezone
86 -- x_date_value5 IN OUT NOCOPY - Other misc dates
87 -- New Date value in user timezone
88 -- x_timezone_disp OUT NOCOPY - Time zone value for display.
89 --
90 +=========================================================================*/
91
92 PROCEDURE CONVERT_DATE_TO_USER_TZ (
93 p_person_party_id IN NUMBER,
94 p_auctioneer_user_name IN VARCHAR2,
95 x_date_value1 IN OUT NOCOPY DATE,
96 x_date_value2 IN OUT NOCOPY DATE,
97 x_date_value3 IN OUT NOCOPY DATE,
98 x_date_value4 IN OUT NOCOPY DATE,
99 x_date_value5 IN OUT NOCOPY DATE,
100 x_timezone_disp OUT NOCOPY VARCHAR2)
101 IS
102 l_module_name CONSTANT VARCHAR2(40) := 'CONVERT_DATE_TO_USER_TZ';
103 l_oex_timezone VARCHAR2(80);
104 l_timezone VARCHAR2(80);
105 l_language_code VARCHAR2(30);
106 l_user_name VARCHAR2(100);
107 l_progress VARCHAR2(3) := '000';
108 BEGIN
109 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
110 FND_LOG.string (log_level => FND_LOG.level_procedure,
111 module => g_module || l_module_name,
112 message => 'Start of Procedure ' || g_module || l_module_name
113 || ', l_progress = ' || l_progress
114 || ', x_date_value1 = ' || x_date_value1
115 || ', x_date_value2 = ' || x_date_value2
116 || ', x_date_value3 = ' || x_date_value3
117 || ', x_date_value4 = ' || x_date_value4
118 || ', x_date_value5 = ' || x_date_value5
119 || ', p_person_party_id = ' || p_person_party_id
120 || ', p_auctioneer_user_name = ' || p_auctioneer_user_name);
121 END IF;
122
123 l_progress := '010';
124
125 BEGIN -- Fetch user name
126 select user_name
127 into l_user_name
128 from fnd_user
129 where person_party_id = p_person_party_id
130 and nvl(end_date,sysdate) >= sysdate;
131 EXCEPTION
132 WHEN TOO_MANY_ROWS THEN
133 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
134 FND_LOG.string (log_level => FND_LOG.level_procedure,
135 module => g_module || l_module_name,
136 message => 'In EXCEPTION BLOCK ' || g_module || l_module_name
137 || ', l_progress = ' || l_progress
138 || ', p_person_party_id = ' || p_person_party_id
139 || ', l_user_name = ' || l_user_name);
140 END IF;
141
142 select user_name
143 into l_user_name
144 from fnd_user
145 where person_party_id = p_person_party_id
146 and nvl(end_date,sysdate) >= sysdate
147 and rownum=1;
148
149 WHEN NO_DATA_FOUND THEN
150 l_progress := '020';
151 l_user_name := p_auctioneer_user_name;
152 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
153 FND_LOG.string (log_level => FND_LOG.level_procedure,
154 module => g_module || l_module_name,
155 message => 'In EXCEPTION BLOCK ' || g_module || l_module_name
156 || ', l_progress = ' || l_progress
157 || ', p_person_party_id = ' || p_person_party_id
158 || ', l_user_name = ' || l_user_name);
159 END IF;
160 END; -- End of Fetch User Name Block.
161
162 l_progress := '030';
163
164 -- Get oex timezone
165 l_oex_timezone := PON_AUCTION_PKG.Get_Oex_Time_Zone;
166
167
168 -- Get the user's time zone
169 l_timezone := PON_AUCTION_PKG.Get_Time_Zone(p_person_party_id);
170
171 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
172 FND_LOG.string (log_level => FND_LOG.level_procedure,
173 module => g_module || l_module_name,
174 message => 'After getting oex and user timezone ' || g_module || l_module_name
175 || ', l_progress = ' || l_progress
176 || ', l_oex_timezone = ' || l_oex_timezone
177 || ', l_timezone = ' || l_timezone);
178 END IF;
179
180 l_progress := '040';
181
182 IF (l_timezone is null or l_timezone = '') THEN
183 l_timezone := l_oex_timezone;
184 END IF;
185
186 IF (PON_OEX_TIMEZONE_PKG.VALID_ZONE(l_timezone) = 1) THEN
187 l_progress := '050';
188 IF (x_date_value1 is not null) THEN
189 x_date_value1 := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(x_date_value1,l_oex_timezone,l_timezone);
190 END IF;
191
192 IF (x_date_value2 is not null) THEN
193 x_date_value2 := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(x_date_value2,l_oex_timezone,l_timezone);
194 END IF;
195
196 IF (x_date_value3 is not null) THEN
197 x_date_value3 := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(x_date_value3,l_oex_timezone,l_timezone);
198 END IF;
199
200 IF (x_date_value4 is not null) THEN
201 x_date_value4 := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(x_date_value4,l_oex_timezone,l_timezone);
202 END IF;
203
204 IF (x_date_value5 is not null) THEN
205 x_date_value5 := PON_OEX_TIMEZONE_PKG.CONVERT_TIME(x_date_value5,l_oex_timezone,l_timezone);
206 END IF;
207 ELSE
208 l_progress := '060';
209 l_timezone := l_oex_timezone;
210 END IF;
211
212 PON_PROFILE_UTIL_PKG.GET_WF_LANGUAGE(l_user_name,l_language_code);
213
214 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
215 FND_LOG.string (log_level => FND_LOG.level_procedure,
216 module => g_module || l_module_name,
217 message => 'After Dates and User Languare ' || g_module || l_module_name
218 || ', l_progress = ' || l_progress
219 || ', x_date_value1 = ' || x_date_value1
220 || ', x_date_value2 = ' || x_date_value2
221 || ', x_date_value3 = ' || x_date_value3
222 || ', x_date_value4 = ' || x_date_value4
223 || ', x_date_value5 = ' || x_date_value5
224 || ', l_language_code = ' || l_language_code
225 || ', l_timezone = ' || l_timezone);
226 END IF;
227
228 l_progress := '070';
229
230 x_timezone_disp := PON_AUCTION_PKG.Get_TimeZone_Description(l_timezone, l_language_code);
231
232 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
233 FND_LOG.string (log_level => FND_LOG.level_procedure,
234 module => g_module || l_module_name,
235 message => 'End of Procedure ' || g_module || l_module_name
236 || ', l_progress = ' || l_progress
237 || ', x_date_value1 = ' || x_date_value1
238 || ', x_date_value2 = ' || x_date_value2
239 || ', x_date_value3 = ' || x_date_value3
240 || ', x_date_value4 = ' || x_date_value4
241 || ', x_date_value5 = ' || x_date_value5
242 || ', x_timezone_disp = ' || x_timezone_disp);
243 END IF;
244 END CONVERT_DATE_TO_USER_TZ;
245
246
247 END PON_OEX_TIMEZONE_PKG;
[Home] [Help]