1 PACKAGE BODY IEM_PARAMETERS_PVT as
2 /* $Header: iemvparb.pls 115.8 2002/12/04 01:23:07 chtang noship $ */
3 G_PKG_NAME CONSTANT varchar2(30) :='IEM_PARAMETERS_PVT ';
4
5 PROCEDURE select_profile (p_api_version_number IN NUMBER,
6 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
7 p_commit IN VARCHAR2 := FND_API.G_FALSE,
8 p_profile_name IN VARCHAR2,
9 x_profile_value OUT NOCOPY VARCHAR2,
10 x_return_status OUT NOCOPY VARCHAR2,
11 x_msg_count OUT NOCOPY NUMBER,
12 x_msg_data OUT NOCOPY VARCHAR2
13 ) is
14 l_api_name VARCHAR2(255):='select_profile';
15 l_api_version_number NUMBER:=1.0;
16
17 BEGIN
18 -- Standard Start of API savepoint
19 SAVEPOINT select_profile_PVT;
20 -- Standard call to check for call compatibility.
21 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
22 p_api_version_number,
23 l_api_name,
24 G_PKG_NAME)
25 THEN
26 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
27 END IF;
28 -- Initialize message list if p_init_msg_list is set to TRUE.
29 IF FND_API.to_Boolean( p_init_msg_list )
30 THEN
31 FND_MSG_PUB.initialize;
32 END IF;
33 -- Initialize API return status to SUCCESS
34 x_return_status := FND_API.G_RET_STS_SUCCESS;
35
36 x_profile_value := FND_PROFILE.VALUE_SPECIFIC(p_profile_name);
37
38 -- Standard Check Of p_commit.
39 IF FND_API.To_Boolean(p_commit) THEN
40 COMMIT WORK;
41 END IF;
42 -- Standard callto get message count and if count is 1, get message info.
43 FND_MSG_PUB.Count_And_Get
44 ( p_count => x_msg_count,
45 p_data => x_msg_data
46 );
47
48 EXCEPTION
49
50
51 WHEN FND_API.G_EXC_ERROR THEN
52 ROLLBACK TO select_profile_PVT;
53 x_return_status := FND_API.G_RET_STS_ERROR ;
54 FND_MSG_PUB.Count_And_Get
55 ( p_count => x_msg_count,
56 p_data => x_msg_data
57 );
58
59 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
60 ROLLBACK TO select_profile_PVT;
61 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
62 FND_MSG_PUB.Count_And_Get
63 ( p_count => x_msg_count,
64 p_data => x_msg_data
65 );
66
67 WHEN OTHERS THEN
68 ROLLBACK TO select_profile_PVT;
69 x_return_status := FND_API.G_RET_STS_ERROR;
70 IF FND_MSG_PUB.Check_Msg_Level
71 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
72 THEN
73 FND_MSG_PUB.Add_Exc_Msg
74 ( G_PKG_NAME ,
75 l_api_name
76 );
77 END IF;
78 FND_MSG_PUB.Count_And_Get
79 ( p_count => x_msg_count ,
80 p_data => x_msg_data
81 );
82
83 END select_profile;
84
85
86 PROCEDURE update_profile (p_api_version_number IN NUMBER,
87 p_init_msg_list IN VARCHAR2,
88 p_commit IN VARCHAR2,
89 p_profile_name IN VARCHAR2,
90 p_profile_value IN VARCHAR2,
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_msg_count OUT NOCOPY NUMBER,
93 x_msg_data OUT NOCOPY VARCHAR2
94 ) is
95 l_api_name VARCHAR2(255):='update_profile';
96 l_api_version_number NUMBER:=1.0;
97 l_count NUMBER;
98 l_party_relate_count NUMBER;
99 l_party_type VARCHAR2(30);
100 l_party_id number;
101 l_resource_id number;
102 INVALID_DEFAULT_CUSTOMER_NUM EXCEPTION;
103 INVALID_DEFAULT_RESOURCE_NUM EXCEPTION;
104 PROFILE_NOT_UPDATED EXCEPTION;
105
106 BEGIN
107 -- Standard Start of API savepoint
108 SAVEPOINT update_profile_PVT;
109 -- Standard call to check for call compatibility.
110 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
111 p_api_version_number,
112 l_api_name,
113 G_PKG_NAME)
114 THEN
115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
116 END IF;
117 -- Initialize message list if p_init_msg_list is set to TRUE.
118 IF FND_API.to_Boolean( p_init_msg_list )
119 THEN
120 FND_MSG_PUB.initialize;
121 END IF;
122 -- Initialize API return status to SUCCESS
123 x_return_status := FND_API.G_RET_STS_SUCCESS;
124
125 -- Validate Default Customer number if the profile name is IEM_DEFAULT_CUSTOMER_NUMBER
126 if UPPER(p_profile_name) = 'IEM_DEFAULT_CUSTOMER_NUMBER' then
127
128 select count(*) into l_count from hz_parties where party_number=p_profile_value;
129 if l_count = 0 then
130 raise INVALID_DEFAULT_CUSTOMER_NUM;
131 end if;
132
133 -- Convert customer number into customer id
134 -- party_id and party_number is a 1 to 1 relationship
135 select party_id into l_party_id from hz_parties where party_number=p_profile_value;
136
137 if SQL%ROWCOUNT <> 1 then
138 raise PROFILE_NOT_UPDATED;
139 end if;
140
141 /* select party_type into l_party_type from hz_parties where party_number=p_profile_value;
142 if l_party_type <> 'PERSON' and l_party_type <> 'PARTY_RELATIONSHIP' then
143 raise INVALID_DEFAULT_CUSTOMER_NUM;
144 end if;
145 */
146
147 -- Save default customer id profile
148 if not FND_PROFILE.SAVE('IEM_DEFAULT_CUSTOMER_ID', l_party_id, 'SITE') then
149 raise PROFILE_NOT_UPDATED;
150 end if;
151 -- Validate Default Resource number if the profile name is IEM_DEFAULT_RESOURCE_NUMBER
152 elsif UPPER(p_profile_name) = 'IEM_DEFAULT_RESOURCE_NUMBER' then
153
154 if (p_profile_value is null) then
155 l_resource_id := '';
156 else
157 select count(*) into l_count from jtf_rs_resource_extns where resource_number=p_profile_value;
158 if l_count = 0 then
159 raise INVALID_DEFAULT_RESOURCE_NUM;
160 end if;
161
162 -- Convert resource number into resource id
163 -- resource_id and resource_number is a 1 to 1 relationship
164 select resource_id into l_resource_id from jtf_rs_resource_extns where resource_number=p_profile_value;
165
166 if SQL%ROWCOUNT <> 1 then
167 raise PROFILE_NOT_UPDATED;
168 end if;
169 end if; -- p_profile_value is null
170
171 -- Save default resource id profile
172 if not FND_PROFILE.SAVE('IEM_SRVR_ARES', l_resource_id, 'SITE') then
173 raise PROFILE_NOT_UPDATED;
174 end if;
175
176
177 end if;
178
179 if not FND_PROFILE.SAVE(p_profile_name, p_profile_value, 'SITE') then
180 raise PROFILE_NOT_UPDATED;
181 end if;
182
183 -- Standard Check Of p_commit.
184 IF FND_API.To_Boolean(p_commit) THEN
185 COMMIT WORK;
186 END IF;
187 -- Standard callto get message count and if count is 1, get message info.
188 FND_MSG_PUB.Count_And_Get
189 ( p_count => x_msg_count,
190 p_data => x_msg_data
191 );
192
193 EXCEPTION
194 WHEN INVALID_DEFAULT_CUSTOMER_NUM THEN
195 ROLLBACK TO update_profile_PVT;
196 x_return_status := FND_API.G_RET_STS_ERROR;
197 FND_MESSAGE.SET_NAME('IEM', 'IEM_SSS_INVALID_DEF_CUST_NUM');
198 FND_MSG_PUB.ADD;
199 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
200
201 WHEN INVALID_DEFAULT_RESOURCE_NUM THEN
202 ROLLBACK TO update_profile_PVT;
203 x_return_status := FND_API.G_RET_STS_ERROR;
204 FND_MESSAGE.SET_NAME('IEM', 'IEM_SSS_INVALID_DEF_REST_NUM');
205 FND_MSG_PUB.ADD;
206 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
207
208 WHEN PROFILE_NOT_UPDATED THEN
209 ROLLBACK TO update_profile_PVT;
210 x_return_status := FND_API.G_RET_STS_ERROR;
211 FND_MESSAGE.SET_NAME('IEM', 'IEM_ADMIN_INTENT_N_NOT_UPDATED');
212 FND_MSG_PUB.ADD;
213 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
214
215
216 WHEN FND_API.G_EXC_ERROR THEN
217 ROLLBACK TO update_profile_PVT;
218 x_return_status := FND_API.G_RET_STS_ERROR ;
219 FND_MSG_PUB.Count_And_Get
220 ( p_count => x_msg_count,
221 p_data => x_msg_data
222 );
223
224 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
225 ROLLBACK TO update_profile_PVT;
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
227 FND_MSG_PUB.Count_And_Get
228 ( p_count => x_msg_count,
229 p_data => x_msg_data
230 );
231
232 WHEN OTHERS THEN
233 ROLLBACK TO update_profile_PVT;
234 x_return_status := FND_API.G_RET_STS_ERROR;
235 IF FND_MSG_PUB.Check_Msg_Level
236 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
237 THEN
238 FND_MSG_PUB.Add_Exc_Msg
239 ( G_PKG_NAME ,
240 l_api_name
241 );
242 END IF;
243 FND_MSG_PUB.Count_And_Get
244 ( p_count => x_msg_count ,
245 p_data => x_msg_data
246 );
247
248 END update_profile;
249
250 PROCEDURE update_profile_wrap (p_api_version_number IN NUMBER,
251 p_init_msg_list IN VARCHAR2,
252 p_commit IN VARCHAR2,
253 p_profile_name_tbl IN jtf_varchar2_Table_100,
254 p_profile_value_tbl IN jtf_varchar2_Table_100,
255 x_return_status OUT NOCOPY VARCHAR2,
256 x_msg_count OUT NOCOPY NUMBER,
257 x_msg_data OUT NOCOPY VARCHAR2
258 ) is
259 l_api_name VARCHAR2(255):='update_profile_wrap';
260 l_api_version_number NUMBER:=1.0;
261 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
262 l_msg_count NUMBER := 0;
263 l_msg_data VARCHAR2(2000);
264 PROFILE_NOT_UPDATED EXCEPTION;
265
266 BEGIN
267 -- Standard Start of API savepoint
268 SAVEPOINT update_profile_PVT;
269 -- Standard call to check for call compatibility.
270 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
271 p_api_version_number,
272 l_api_name,
273 G_PKG_NAME)
274 THEN
275 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
276 END IF;
277 -- Initialize message list if p_init_msg_list is set to TRUE.
278 IF FND_API.to_Boolean( p_init_msg_list )
279 THEN
280 FND_MSG_PUB.initialize;
281 END IF;
282 -- Initialize API return status to SUCCESS
283 x_return_status := FND_API.G_RET_STS_SUCCESS;
284
285 FOR i IN p_profile_name_tbl.FIRST..p_profile_name_tbl.LAST LOOP
286
287 iem_parameters_pvt.update_profile (p_api_version_number => l_api_version_number,
288 p_init_msg_list => FND_API.G_FALSE,
289 p_commit => FND_API.G_FALSE,
290 p_profile_name => p_profile_name_tbl(i),
291 p_profile_value => p_profile_value_tbl(i),
292 x_return_status=> l_return_status,
293 x_msg_count => l_msg_count,
294 x_msg_data => l_msg_data);
295
296 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
297 raise PROFILE_NOT_UPDATED;
298 end if;
299
300 END LOOP;
301
302 -- Standard Check Of p_commit.
303 IF FND_API.To_Boolean(p_commit) THEN
304 COMMIT WORK;
305 END IF;
306 -- Standard callto get message count and if count is 1, get message info.
307 FND_MSG_PUB.Count_And_Get
308 ( p_count => x_msg_count,
309 p_data => x_msg_data
310 );
311
312 EXCEPTION
313
314 WHEN PROFILE_NOT_UPDATED THEN
315 ROLLBACK TO update_profile_PVT;
316 x_return_status := FND_API.G_RET_STS_ERROR;
317 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
318
319
320 WHEN FND_API.G_EXC_ERROR THEN
321 ROLLBACK TO update_profile_PVT;
322 x_return_status := FND_API.G_RET_STS_ERROR ;
323 FND_MSG_PUB.Count_And_Get
324 ( p_count => x_msg_count,
325 p_data => x_msg_data
326 );
327
328 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
329 ROLLBACK TO update_profile_PVT;
330 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
331 FND_MSG_PUB.Count_And_Get
332 ( p_count => x_msg_count,
333 p_data => x_msg_data
334 );
335
336 WHEN OTHERS THEN
337 ROLLBACK TO update_profile_PVT;
338 x_return_status := FND_API.G_RET_STS_ERROR;
339 IF FND_MSG_PUB.Check_Msg_Level
340 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
341 THEN
342 FND_MSG_PUB.Add_Exc_Msg
343 ( G_PKG_NAME ,
344 l_api_name
345 );
346 END IF;
347 FND_MSG_PUB.Count_And_Get
348 ( p_count => x_msg_count ,
349 p_data => x_msg_data
350 );
351
352 END update_profile_wrap;
353
354 END IEM_PARAMETERS_PVT ;