1 PACKAGE BODY CSD_PARTIES_UTIL AS
2 /* $Header: csdvptub.pls 115.3 2002/12/03 20:34:51 sangigup noship $ */
3 --
4 -- Package name : CSD_PARTIES_UTIL
5 -- Purpose : This package contains the utilities for managing
6 -- TCA parties in Depot Repair.
7 -- History :
8 -- Version Date Name Description
9 -- 115.9 10/20/02 swai Created.
10
11
12 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_PARTIES_UTIL';
13 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdvptub.pls';
14
15
16
17 /*----------------------------------------------------------------*/
18 /* procedure name: Get_EmailDetails */
19 /* description : Gets email contact point for a given party */
20 /* This procedure accepts party id and returns */
21 /* the email contact point id and last updated */
22 /* date for that party. */
23 /* */
24 /* p_api_version Standard IN param */
25 /* p_commit Standard IN param */
26 /* p_init_msg_list Standard IN param */
27 /* p_validation_level Standard IN param */
28 /* p_party_id Party ID to get email details for */
29 /* x_email_cnt_point_id Email Contact Point ID */
30 /* x_email_last_update_date Last Updated Date for Contact Pt */
31 /* x_return_status Standard OUT param */
32 /* x_msg_count Standard OUT param */
33 /* x_msg_data Standard OUT param */
34 /* */
35 /*----------------------------------------------------------------*/
36 PROCEDURE Get_EmailDetails
37 (
38 p_api_version IN NUMBER,
39 p_commit IN VARCHAR2 := fnd_api.g_false,
40 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
41 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
42 p_party_id IN NUMBER,
43 x_email_cnt_point_id OUT NOCOPY NUMBER,
44 x_email_last_update_date OUT NOCOPY DATE,
45 x_return_status OUT NOCOPY VARCHAR2,
46 x_msg_count OUT NOCOPY NUMBER,
47 x_msg_data OUT NOCOPY VARCHAR2 )
48 IS
49 l_api_name CONSTANT VARCHAR2(30) := 'Get_EmailDetails';
50 l_api_version CONSTANT NUMBER := 1.0;
51 l_party_id NUMBER := p_party_id;
52
53 CURSOR get_email_details IS
54 SELECT contact_point_id,
55 last_update_date
56 FROM hz_contact_points
57 WHERE owner_table_id = l_party_id
58 AND owner_table_name = 'HZ_PARTIES'
59 AND contact_point_type='EMAIL'
60 AND primary_flag='Y' ;
61 BEGIN
62 -- Standard Start of API savepoint
63 SAVEPOINT Get_EmailDetails_Utl;
64
65 -- Standard call to check for call compatibility.
66 IF NOT FND_API.Compatible_API_Call (l_api_version,
67 p_api_version,
68 l_api_name,
69 G_PKG_NAME)
70 THEN
71 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
72 END IF;
73
74 -- Initialize message list if p_init_msg_list is set to TRUE.
75 IF FND_API.to_Boolean(p_init_msg_list) THEN
76 FND_MSG_PUB.initialize;
77 END IF;
78
79 -- Initialize API return status to success
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81
82 --
83 -- Begin API Body
84 --
85 FOR get_email_details_rec IN get_email_details LOOP
86 x_email_cnt_point_id:=get_email_details_rec.contact_point_id;
87 x_email_last_update_date:=get_email_details_rec.last_update_date;
88 END LOOP;
89
90 --
91 -- End API Body
92 --
93
94 -- Standard check of p_commit.
95 IF FND_API.To_Boolean( p_commit ) THEN
96 COMMIT WORK;
97 END IF;
98
99 -- Standard call to get message count and IF count is get message info.
100 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
101 p_data => x_msg_data );
102
103 EXCEPTION
104 WHEN FND_API.G_EXC_ERROR THEN
105 ROLLBACK TO Get_EmailDetails_Utl;
106 x_return_status := FND_API.G_RET_STS_ERROR ;
107 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
108 p_data => x_msg_data);
109 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
110 ROLLBACK TO Get_EmailDetails_Utl;
111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
112 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
113 p_data => x_msg_data );
114 WHEN OTHERS THEN
115 ROLLBACK TO Get_EmailDetails_Utl;
116 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
117 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
118 THEN
119 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,
120 l_api_name );
121 END IF;
122 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
123 p_data => x_msg_data );
124 END Get_EmailDetails;
125
126
127 /*----------------------------------------------------------------*/
128 /* procedure name: Get_AddrDetails */
129 /* description : Gets address location and site for a party */
130 /* This procedure accepts party id and returns */
131 /* the address location and site ids and last */
132 /* updated date of both items for that party. */
133 /* */
134 /* p_api_version Standard IN param */
135 /* p_commit Standard IN param */
136 /* p_init_msg_list Standard IN param */
137 /* p_validation_level Standard IN param */
138 /* p_party_id Party ID to get addr details for */
139 /* x_addr_location_id Location ID for party */
140 /* x_party_site_id Site ID for location */
141 /* x_addr_last_update_date Last updated date for location */
142 /* x_party_site_last_update_date Last updated date for site */
143 /* x_return_status Standard OUT param */
144 /* x_msg_count Standard OUT param */
145 /* x_msg_data Standard OUT param */
146 /* */
147 /*----------------------------------------------------------------*/
148 PROCEDURE Get_AddrDetails
149 (
150 p_api_version IN NUMBER,
151 p_commit IN VARCHAR2 := fnd_api.g_false,
152 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
153 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
154 p_party_id IN NUMBER,
155 x_addr_location_id OUT NOCOPY NUMBER,
156 x_party_site_id OUT NOCOPY NUMBER,
157 x_addr_last_update_date OUT NOCOPY DATE,
158 x_party_site_last_update_date OUT NOCOPY DATE,
159 x_return_status OUT NOCOPY VARCHAR2,
160 x_msg_count OUT NOCOPY NUMBER,
161 x_msg_data OUT NOCOPY VARCHAR2 )
162 IS
163 l_api_name CONSTANT VARCHAR2(30) := 'Get_AddrDetails';
164 l_api_version CONSTANT NUMBER := 1.0;
165 l_party_id NUMBER := p_party_id;
166
167 CURSOR get_address_details IS
168 SELECT loc.location_id,
169 loc.last_update_date,
170 sites.party_site_id,
171 sites.last_update_date sites_last_update_date
172 FROM hz_party_sites sites, hz_locations loc
173 WHERE sites.party_id = l_party_id
174 AND loc.location_id = sites.location_id
175 AND sites.identifying_address_flag='Y' ;
176
177 BEGIN
178 -- Standard Start of API savepoint
179 SAVEPOINT Get_AddrDetails_Utl;
180
181 -- Standard call to check for call compatibility.
182 IF NOT FND_API.Compatible_API_Call (l_api_version,
183 p_api_version,
184 l_api_name,
185 G_PKG_NAME)
186 THEN
187 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
188 END IF;
189
190 -- Initialize message list if p_init_msg_list is set to TRUE.
191 IF FND_API.to_Boolean(p_init_msg_list) THEN
192 FND_MSG_PUB.initialize;
193 END IF;
194
195 -- Initialize API return status to success
196 x_return_status := FND_API.G_RET_STS_SUCCESS;
197
198 --
199 -- Begin API Body
200 --
201 FOR get_address_details_rec IN get_address_details LOOP
202 x_party_site_id:=get_address_details_rec.party_site_id;
203 x_addr_location_id:=get_address_details_rec.location_id;
204 x_party_site_last_update_date:=get_address_details_rec.sites_last_update_date;
205 x_addr_last_update_date:=get_address_details_rec.last_update_date;
206 END LOOP;
207
208 --
209 -- End API Body
210 --
211
212 -- Standard check of p_commit.
213 IF FND_API.To_Boolean( p_commit ) THEN
214 COMMIT WORK;
215 END IF;
216
217 -- Standard call to get message count and IF count is get message info.
218 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
219 p_data => x_msg_data );
220
221 EXCEPTION
222 WHEN FND_API.G_EXC_ERROR THEN
223 ROLLBACK TO Get_AddrDetails_Utl;
224 x_return_status := FND_API.G_RET_STS_ERROR ;
225 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
226 p_data => x_msg_data);
227 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
228 ROLLBACK TO Get_AddrDetails_Utl;
229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
230 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
231 p_data => x_msg_data );
232 WHEN OTHERS THEN
233 ROLLBACK TO Get_AddrDetails_Utl;
234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
235 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
236 THEN
237 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,
238 l_api_name );
239 END IF;
240 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
241 p_data => x_msg_data );
242 END Get_AddrDetails;
243
244
245 /*----------------------------------------------------------------*/
246 /* procedure name: Get_PhoneDetails */
247 /* description : This procedure accepts phone contact point id */
248 /* and returns the last updated for that contact */
249 /* point. */
250 /* */
251 /* p_api_version Standard IN param */
252 /* p_commit Standard IN param */
253 /* p_init_msg_list Standard IN param */
254 /* p_validation_level Standard IN param */
255 /* p_phone_cnt_point_id Phone Contact Point ID */
256 /* x_phone_last_update_date Last Updated Date for Contact Pt */
257 /* x_return_status Standard OUT param */
258 /* x_msg_count Standard OUT param */
259 /* x_msg_data Standard OUT param */
260 /* */
261 /*----------------------------------------------------------------*/
262 PROCEDURE Get_PhoneDetails
263 (
264 p_api_version IN NUMBER,
265 p_commit IN VARCHAR2 := fnd_api.g_false,
266 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
267 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
268 p_phone_cnt_point_id IN NUMBER,
269 x_phone_last_update_date OUT NOCOPY DATE,
270 x_return_status OUT NOCOPY VARCHAR2,
271 x_msg_count OUT NOCOPY NUMBER,
272 x_msg_data OUT NOCOPY VARCHAR2 )
273 IS
274 l_api_name CONSTANT VARCHAR2(30) := 'Get_PhoneDetails';
275 l_api_version CONSTANT NUMBER := 1.0;
276 l_phone_cnt_point_id NUMBER := p_phone_cnt_point_id;
277
278 CURSOR get_phone_details IS
279 SELECT LAST_UPDATE_DATE
280 FROM HZ_CONTACT_POINTS
281 WHERE CONTACT_POINT_ID = l_phone_cnt_point_id;
282
283 BEGIN
284 -- Standard Start of API savepoint
285 SAVEPOINT Get_PhoneDetails_Utl;
286
287 -- Standard call to check for call compatibility.
288 IF NOT FND_API.Compatible_API_Call (l_api_version,
289 p_api_version,
290 l_api_name,
291 G_PKG_NAME)
292 THEN
293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
294 END IF;
295
296 -- Initialize message list if p_init_msg_list is set to TRUE.
297 IF FND_API.to_Boolean(p_init_msg_list) THEN
298 FND_MSG_PUB.initialize;
299 END IF;
300
301 -- Initialize API return status to success
302 x_return_status := FND_API.G_RET_STS_SUCCESS;
303
304 --
305 -- Begin API Body
306 --
307 FOR get_phone_details_rec IN get_phone_details LOOP
308 x_phone_last_update_date:=get_phone_details_rec.last_update_date;
309 END LOOP;
310
311 --
312 -- End API Body
313 --
314
315 -- Standard check of p_commit.
316 IF FND_API.To_Boolean( p_commit ) THEN
317 COMMIT WORK;
318 END IF;
319
320 -- Standard call to get message count and IF count is get message info.
321 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
322 p_data => x_msg_data );
323
324 EXCEPTION
325 WHEN FND_API.G_EXC_ERROR THEN
326 ROLLBACK TO Get_PhoneDetails_Utl;
327 x_return_status := FND_API.G_RET_STS_ERROR ;
328 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
329 p_data => x_msg_data);
330 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
331 ROLLBACK TO Get_PhoneDetails_Utl;
332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
333 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
334 p_data => x_msg_data );
335 WHEN OTHERS THEN
336 ROLLBACK TO Get_PhoneDetails_Utl;
337 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
338 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
339 THEN
340 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,
341 l_api_name );
342 END IF;
343 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
344 p_data => x_msg_data );
345 END Get_PhoneDetails;
346
347
348 /*----------------------------------------------------------------*/
349 /* procedure name: Get_PartyDetails */
350 /* description : This procedure accepts TCA party id and */
351 /* returns the last updated for that party. */
352 /* */
353 /* p_api_version Standard IN param */
354 /* p_commit Standard IN param */
355 /* p_init_msg_list Standard IN param */
356 /* p_validation_level Standard IN param */
357 /* p_party_id Party ID to get details for */
358 /* x_last_update_date Last Updated Date for Party */
359 /* x_return_status Standard OUT param */
360 /* x_msg_count Standard OUT param */
361 /* x_msg_data Standard OUT param */
362 /* */
363 /*----------------------------------------------------------------*/
364 PROCEDURE Get_PartyDetails
365 (
366 p_api_version IN NUMBER,
367 p_commit IN VARCHAR2 := fnd_api.g_false,
368 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
369 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
370 p_party_id IN NUMBER,
371 x_last_update_date OUT NOCOPY DATE,
375 IS
372 x_return_status OUT NOCOPY VARCHAR2,
373 x_msg_count OUT NOCOPY NUMBER,
374 x_msg_data OUT NOCOPY VARCHAR2 )
376 l_api_name CONSTANT VARCHAR2(30) := 'Get_PartyDetails';
377 l_api_version CONSTANT NUMBER := 1.0;
378 l_curr_party_id NUMBER := p_party_id;
379 CURSOR get_party_details IS
380 SELECT LAST_UPDATE_DATE
381 FROM HZ_PARTIES
382 WHERE PARTY_ID=l_curr_party_id;
383
384 BEGIN
385 -- Standard Start of API savepoint
386 SAVEPOINT Get_PartyDetails_Utl;
387
388 -- Standard call to check for call compatibility.
389 IF NOT FND_API.Compatible_API_Call (l_api_version,
390 p_api_version,
391 l_api_name,
392 G_PKG_NAME)
393 THEN
394 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
395 END IF;
396
397 -- Initialize message list if p_init_msg_list is set to TRUE.
398 IF FND_API.to_Boolean(p_init_msg_list) THEN
399 FND_MSG_PUB.initialize;
400 END IF;
401
402 -- Initialize API return status to success
403 x_return_status := FND_API.G_RET_STS_SUCCESS;
404
405 --
406 -- Begin API Body
407 --
408 FOR get_party_details_rec IN get_party_details LOOP
409 x_last_update_date:=get_party_details_rec.last_update_date;
410 END LOOP;
411
412
413 --
414 -- End API Body
415 --
416
417 -- Standard check of p_commit.
418 IF FND_API.To_Boolean( p_commit ) THEN
419 COMMIT WORK;
420 END IF;
421
422 -- Standard call to get message count and IF count is get message info.
423 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
424 p_data => x_msg_data );
425
426 EXCEPTION
427 WHEN FND_API.G_EXC_ERROR THEN
428 ROLLBACK TO Get_PartyDetails_Utl;
429 x_return_status := FND_API.G_RET_STS_ERROR ;
430 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
431 p_data => x_msg_data);
432 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
433 ROLLBACK TO Get_PartyDetails_Utl;
434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
435 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
436 p_data => x_msg_data );
437 WHEN OTHERS THEN
438 ROLLBACK TO Get_PartyDetails_Utl;
439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
440 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
441 THEN
442 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,
443 l_api_name );
444 END IF;
445 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
446 p_data => x_msg_data );
447 END Get_PartyDetails;
448
449
450
451 END CSD_PARTIES_UTIL ;
[Home] [Help]