[Home] [Help]
PACKAGE BODY: APPS.IEM_UTILITY_PVT
Source
1 package body IEM_UTILITY_PVT as
2 /* $Header: iemutilb.pls 120.2.12010000.2 2009/08/07 09:27:55 lkullamb ship $*/
3 G_PKG_NAME varchar2(100):='IEM_UTILITY_PVT';
4 PROCEDURE GetEmailAccountList(p_api_version_number IN NUMBER,
5 p_init_msg_list IN VARCHAR2 ,
6 p_commit IN VARCHAR2 ,
7 x_account_tbl out nocopy email_account_tbl,
8 x_return_status OUT NOCOPY VARCHAR2,
9 x_msg_count OUT NOCOPY NUMBER,
10 x_msg_data OUT NOCOPY VARCHAR2) IS
11 l_api_version_number number:=1.0;
12 l_api_name varchar2(30):='GetEmailAccountList';
13 cursor c1 is select email_account_id,from_name from iem_mstemail_accounts where deleted_flag<>'Y'
14 order by from_name;
15 i number:=0;
16 BEGIN
17 -- Standard call to check for call compatibility.
18 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
19 p_api_version_number,
20 l_api_name,
21 G_PKG_NAME)
22 THEN
23 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
24 END IF;
25 SAVEPOINT select_mail_count_pvt;
26 x_return_status := FND_API.G_RET_STS_SUCCESS;
27 i:=0;
28 for v1 in c1 loop
29 i:=i+1;
30 x_account_tbl(i).email_account_id:=v1.email_account_id;
31 x_account_tbl(i).account_name:=v1.from_name;
32 end loop;
33 -- Standard Check Of p_commit.
34 IF p_commit='T' THEN
35 COMMIT WORK;
36 END IF;
37 -- Standard callto get message count and if count is 1, get message info.
38 FND_MSG_PUB.Count_And_Get
39 ( p_count => x_msg_count,
40 p_data => x_msg_data
41 );
42 EXCEPTION
43
44 WHEN FND_API.G_EXC_ERROR THEN
45 ROLLBACK TO select_mail_count_PVT;
46 x_return_status := FND_API.G_RET_STS_ERROR ;
47 FND_MSG_PUB.Count_And_Get
48 ( p_count => x_msg_count,
49 p_data => x_msg_data
50 );
51 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
52 ROLLBACK TO select_mail_count_PVT;
53 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
54 FND_MSG_PUB.Count_And_Get
55 ( p_count => x_msg_count,
56 p_data => x_msg_data
57 );
58 WHEN OTHERS THEN
59 ROLLBACK TO select_mail_count_PVT;
60 x_return_status := FND_API.G_RET_STS_ERROR;
61 IF FND_MSG_PUB.Check_Msg_Level
62 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
63 THEN
64 FND_MSG_PUB.Add_Exc_Msg
65 ( G_PKG_NAME ,
66 l_api_name
67 );
68 END IF;
69 FND_MSG_PUB.Count_And_Get
70 ( p_count => x_msg_count ,
71 p_data => x_msg_data
72 );
73
74 END GetEmailAccountList;
75
76 PROCEDURE GetClassLists(p_api_version_number IN NUMBER,
77 p_init_msg_list IN VARCHAR2 ,
78 p_commit IN VARCHAR2 ,
79 p_email_account_id in number,
80 x_class_tbl out nocopy email_class_tbl,
81 x_return_status OUT NOCOPY VARCHAR2,
82 x_msg_count OUT NOCOPY NUMBER,
83 x_msg_data OUT NOCOPY VARCHAR2) IS
84 l_api_version_number number:=1.0;
85 l_api_name varchar2(30):='GetEmailAccountList';
86 cursor c1 is
87 select a.route_classification_id,b.name from iem_account_route_class a,iem_route_classifications b
88 where a.route_classification_id=b.route_classification_id and a.enabled_flag='Y'
89 and a.email_account_id=p_email_account_id
90 order by 2;
91
92 i number:=0;
93 BEGIN
94 -- Standard call to check for call compatibility.
95 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
96 p_api_version_number,
97 l_api_name,
98 G_PKG_NAME)
99 THEN
100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
101 END IF;
102 SAVEPOINT select_mail_count_pvt;
103 x_return_status := FND_API.G_RET_STS_SUCCESS;
104 i:=0;
105 for v1 in c1 loop
106 i:=i+1;
107 x_class_tbl(i).rt_classification_id:=v1.route_classification_id;
108 x_class_tbl(i).name:=v1.name;
109 end loop;
110 -- Standard Check Of p_commit.
111 IF p_commit='T' THEN
112 COMMIT WORK;
113 END IF;
114 -- Standard callto get message count and if count is 1, get message info.
115 FND_MSG_PUB.Count_And_Get
116 ( p_count => x_msg_count,
117 p_data => x_msg_data
118 );
119 EXCEPTION
120
121 WHEN FND_API.G_EXC_ERROR THEN
122 ROLLBACK TO select_mail_count_PVT;
123 x_return_status := FND_API.G_RET_STS_ERROR ;
124 FND_MSG_PUB.Count_And_Get
125 ( p_count => x_msg_count,
126 p_data => x_msg_data
127 );
128 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
129 ROLLBACK TO select_mail_count_PVT;
130 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
131 FND_MSG_PUB.Count_And_Get
132 ( p_count => x_msg_count,
133 p_data => x_msg_data
134 );
135 WHEN OTHERS THEN
136 ROLLBACK TO select_mail_count_PVT;
137 x_return_status := FND_API.G_RET_STS_ERROR;
138 IF FND_MSG_PUB.Check_Msg_Level
139 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
140 THEN
141 FND_MSG_PUB.Add_Exc_Msg
142 ( G_PKG_NAME ,
143 l_api_name
144 );
145 END IF;
146 FND_MSG_PUB.Count_And_Get
147 ( p_count => x_msg_count ,
148 p_data => x_msg_data
149 );
150 end GetClassLists;
151 FUNCTION gettimezone(p_date in date,
152 p_resource_id in number) return varchar2 IS
153 l_client_tz_id number;
154 l_status varchar2(10);
155 l_msg_count number;
156 l_msg_data varchar2(200);
157 l_date date;
158 x_date varchar2(200);
159 l_user_id number;
160 l_time_format varchar2(100);
161 l_format varchar2(100);
162 begin
163 --add if condition for changing CherrPick view in UWQ,12.1.2 project
164 if p_resource_id>0 then
165 begin
166 select user_id into l_user_id from JTF_RS_RESOURCE_EXTNS
167 where resource_id=p_resource_id;
168 l_client_tz_id :=to_number(fnd_profile.value_specific('CLIENT_TIMEZONE_ID',l_user_id));
169 l_time_format :=fnd_profile.value_specific('ICX_DATE_FORMAT_MASK',l_user_id);
170 l_format:=l_time_format||' '||'HH24:MI:SS';
171 HZ_TIMEZONE_PUB.Get_Time(1.0,'F', 0, l_client_tz_id, p_date,
172 l_date, l_status, l_msg_count, l_msg_data);
173 x_date:=to_char(l_date,l_format);
174 return(x_date);
175 exception when others then
176 x_date:=to_char(p_date,'DD-MON-YYYY HH24:MI:SS');
177 end;
178 else
179 l_client_tz_id :=to_number(fnd_profile.value_specific('CLIENT_TIMEZONE_ID'));
180 l_time_format :=fnd_profile.value_specific('ICX_DATE_FORMAT_MASK');
181 l_format:=l_time_format||' '||'HH24:MI:SS';
182 HZ_TIMEZONE_PUB.Get_Time(1.0,'F', 0, l_client_tz_id, p_date,
183 l_date, l_status, l_msg_count, l_msg_data);
184 x_date:=to_char(l_date,l_format);
185 end if;
186 return(x_date);
187 end gettimezone;
188 end IEM_UTILITY_PVT ;