[Home] [Help]
PACKAGE BODY: APPS.JTF_CAL_ADDR_PVT
Source
1 PACKAGE BODY JTF_CAL_Addr_PVT AS
2 /* $Header: jtfvcab.pls 115.7 2002/04/09 10:56:34 pkm ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_CALAddress_PVT';
5
6 PROCEDURE Lock_Row
7 /*****************************************************************************
8 ** INTERNAL ONLY: This procedure is used in Update_Row and Delete_Row to make
9 ** sure the record isn't being used by a different user.
10 *****************************************************************************/
11 ( p_address_id IN NUMBER
12 , p_object_version_number IN NUMBER
13 )AS
14
15 CURSOR c_lock
16 /***************************************************************************
17 ** Cursor to lock the record
18 ***************************************************************************/
19 ( b_address_id IN NUMBER
20 )IS SELECT object_version_number
21 FROM jtf_cal_addresses
22 WHERE address_id = b_address_id
23 FOR UPDATE OF object_version_number NOWAIT;
24
25 l_object_version_number NUMBER;
26
27 BEGIN
28 /***************************************************************************
29 ** Making sure the cursor is closed
30 ***************************************************************************/
31 IF (c_lock%ISOPEN)
32 THEN
33 CLOSE c_lock;
34 END IF;
35
36 /***************************************************************************
37 ** Try to lock the record
38 ***************************************************************************/
39 OPEN c_lock(p_Address_ID);
40
41 /***************************************************************************
42 ** Get the object version number of the record that got locked
43 ***************************************************************************/
44 FETCH c_lock INTO l_object_version_number;
45
46 /***************************************************************************
47 ** The record no longer exists in the database: raise exception
48 ***************************************************************************/
49 IF (c_lock%NOTFOUND)
50 THEN
51 CLOSE c_lock;
52 fnd_message.set_name ('JTF', 'JTF_CAL_RECORD_DELETED');
53 fnd_msg_pub.add;
54 RAISE fnd_api.g_exc_unexpected_error;
55 END IF;
56
57 /***************************************************************************
58 ** If the object version number has changed, the record has changed:
59 ** raise exception
60 ***************************************************************************/
61 CLOSE c_lock;
62
63 IF (l_object_version_number <> p_object_version_number)
64 THEN
65 fnd_message.set_name ('JTF', 'JTF_CAL_RECORD_CHANGED');
66 fnd_msg_pub.add;
67 RAISE fnd_api.g_exc_unexpected_error;
68 END IF;
69 END Lock_Row;
70
71 --------------------------------------------------------------------------
72 -- Start of comments
73 -- API name : Insert_Row
74 -- Type : Private
75 -- Function : Create record in JTF_CAL_ADDRESSES table.
76 -- Pre-reqs : None.
77 -- Parameters :
78 -- name direction type required?
79 -- ---- --------- ---- ---------
80 -- p_api_version IN NUMBER required
81 -- p_init_msg_list IN VARCHAR2 optional
82 -- p_commit IN VARCHAR2 optional
83 -- p_validation_level IN NUMBER optional
84 -- x_return_status OUT VARCHAR2 required
85 -- x_msg_count OUT NUMBER required
86 -- x_msg_data OUT VARCHAR2 required
87 -- p_bel_rec IN cal_address_rec_type required
88 -- x_address_id OUT NUMBER required
89 --
90 -- Version : Current version 1.0
91 -- Previous version 1.0
92 -- Initial version 1.0
93 --
94 -- Notes: The object_version_number of a new entry is always 1.
95 --
96 -- End of comments
97 --------------------------------------------------------------------------
98 PROCEDURE Insert_Row
99 ( p_api_version IN NUMBER
100 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
101 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
102 , p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full
103 , x_return_status OUT VARCHAR2
104 , x_msg_count OUT NUMBER
105 , x_msg_data OUT VARCHAR2
106 , p_adr_rec IN AddrRec
107 , x_address_id OUT NUMBER
108 )
109 IS
110 l_api_name CONSTANT VARCHAR2(30) := 'Create_Row';
111 l_api_version CONSTANT NUMBER := 1.0;
112 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
113 l_object_version_number NUMBER := 1;
114 l_return_status VARCHAR2(1);
115 l_rowid ROWID;
116 l_address_id NUMBER;
117
118 CURSOR c_record_exists
119 (b_address_id NUMBER
120 )IS SELECT ROWID
121 FROM JTF_CAL_ADDRESSES
122 WHERE address_id = b_address_id;
123
124 BEGIN
125 --
126 -- Standard start of API savepoint
127 --
128 SAVEPOINT Create_Address_PVT;
129
130 --
131 -- Standard call to check for call compatibility
132 --
133 IF NOT FND_API.Compatible_API_Call( l_api_version
134 , p_api_version
135 , l_api_name
136 , G_PKG_NAME
137 )
138 THEN
139 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
140 END IF;
141
142 --
143 -- Initialize message list if p_init_msg_list is set to TRUE
144 --
145 IF FND_API.To_Boolean(p_init_msg_list)
146 THEN
147 FND_MSG_PUB.Initialize;
148 END IF;
149
150 --
151 -- Initialize API return status to success
152 --
153 x_return_status := FND_API.G_RET_STS_SUCCESS;
154
155 ------------------------------------------------------------------------
156 -- Insert into table. Generate the ID from the
157 -- sequence and return it
158 ------------------------------------------------------------------------
159 INSERT INTO JTF_CAL_ADDRESSES
160 ( ADDRESS_ID
161 , RESOURCE_ID
162 , CREATED_BY
163 , CREATION_DATE
164 , LAST_UPDATED_BY
165 , LAST_UPDATE_DATE
166 , LAST_UPDATE_LOGIN
167 , LAST_NAME
168 , FIRST_NAME
169 , JOB_TITLE
170 , COMPANY
171 , PRIMARY_CONTACT
172 , CONTACT1_TYPE
173 , CONTACT1
174 , CONTACT2_TYPE
175 , CONTACT2
176 , CONTACT3_TYPE
177 , CONTACT3
178 , CONTACT4_TYPE
179 , CONTACT4
180 , CONTACT5_TYPE
181 , CONTACT5
182 , WWW_ADDRESS
183 , ASSISTANT_NAME
184 , ASSISTANT_PHONE
185 , CATEGORY
186 , ADDRESS1
187 , ADDRESS2
188 , ADDRESS3
189 , ADDRESS4
190 , CITY
191 , STATE
192 , COUNTY
193 , ZIP
194 , COUNTRY
195 , NOTE
196 , PRIVATE_FLAG
197 , DELETED_AS_OF
198 , APPLICATION_ID
199 , SECURITY_GROUP_ID
200 , OBJECT_VERSION_NUMBER
201 ) VALUES
202 ( JTF_CAL_ADDRESSES_S.NEXTVAL -- returning into l_adress_id
203 , p_adr_rec.RESOURCE_ID
204 , p_adr_rec.CREATED_BY
205 , p_adr_rec.CREATION_DATE
206 , p_adr_rec.LAST_UPDATED_BY
207 , p_adr_rec.LAST_UPDATE_DATE
208 , p_adr_rec.LAST_UPDATE_LOGIN
209 , p_adr_rec.LAST_NAME
210 , p_adr_rec.FIRST_NAME
211 , p_adr_rec.JOB_TITLE
212 , p_adr_rec.COMPANY
213 , p_adr_rec.PRIMARY_CONTACT
214 , p_adr_rec.CONTACT1_TYPE
215 , p_adr_rec.CONTACT1
216 , p_adr_rec.CONTACT2_TYPE
217 , p_adr_rec.CONTACT2
218 , p_adr_rec.CONTACT3_TYPE
219 , p_adr_rec.CONTACT3
220 , p_adr_rec.CONTACT4_TYPE
221 , p_adr_rec.CONTACT4
222 , p_adr_rec.CONTACT5_TYPE
223 , p_adr_rec.CONTACT5
224 , p_adr_rec.WWW_ADDRESS
225 , p_adr_rec.ASSISTANT_NAME
226 , p_adr_rec.ASSISTANT_PHONE
227 , p_adr_rec.CATEGORY
228 , p_adr_rec.ADDRESS1
229 , p_adr_rec.ADDRESS2
230 , p_adr_rec.ADDRESS3
231 , p_adr_rec.ADDRESS4
232 , p_adr_rec.CITY
233 , p_adr_rec.COUNTY
234 , p_adr_rec.STATE
235 , p_adr_rec.ZIP
236 , p_adr_rec.COUNTRY
237 , p_adr_rec.NOTE
238 , p_adr_rec.PRIVATE_FLAG
239 , p_adr_rec.DELETED_AS_OF
240 , p_adr_rec.APPLICATION_ID
241 , p_adr_rec.SECURITY_GROUP_ID
242 , l_object_version_number -- always 1 for a new object
243 )RETURNING ADDRESS_ID INTO l_address_id;
244
245 --
246 -- Check if the record was created
247 --
248 OPEN c_record_exists(l_address_id);
249 FETCH c_record_exists INTO l_ROWID;
250 IF (c_record_exists%NOTFOUND)
251 THEN
252 CLOSE c_record_exists;
253 RAISE no_data_found;
254 END IF;
255 CLOSE c_record_exists;
256
257 --
258 -- Return the key value to the caller
259 --
260 x_address_id := l_address_id;
261
262 --
263 -- Standard check of p_commit
264 --
265 IF FND_API.To_Boolean(p_commit)
266 THEN
267 COMMIT WORK;
268 END IF;
269
270 --
271 -- Standard call to get message count and if count is 1, get message info
272 --
273 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
274 , p_data => x_msg_data
275 );
276
277 EXCEPTION
278 WHEN FND_API.G_EXC_ERROR
279 THEN
280 ROLLBACK TO Create_Address_PVT;
281 x_return_status := FND_API.G_RET_STS_ERROR;
282 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
283 , p_data => x_msg_data
284 );
285
286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
287 THEN
288 ROLLBACK TO Create_Address_PVT;
289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
290 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
291 , p_data => x_msg_data
292 );
293 WHEN OTHERS
294 THEN
295 ROLLBACK TO Create_Address_PVT;
296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
297 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
298 THEN
299 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
300 , l_api_name
301 );
302 END IF;
303 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
304 , p_data => x_msg_data
305 );
306
307 END Insert_Row;
308
309 --------------------------------------------------------------------------
310 -- Start of comments
311 -- API name : Update_Row
312 -- Type : Private
313 -- Function : Update record in JTF_CAL_ADDRESSES table.
314 -- Pre-reqs : None.
315 -- Parameters :
316 -- name direction type required?
317 -- ---- --------- -------- ---------
318 -- p_api_version IN NUMBER required
319 -- p_init_msg_list IN VARCHAR2 optional
320 -- p_commit IN VARCHAR2 optional
321 -- p_validation_level IN NUMBER optional
322 -- x_return_status OUT VARCHAR2 required
323 -- x_msg_count OUT NUMBER required
324 -- x_msg_data OUT VARCHAR2 required
325 -- p_adr_rec IN cal_address_rec_type required
326 -- x_object_version_number OUT NUMBER required
327 --
328 -- Version : Current version 1.0
329 -- Previous version 1.0
330 -- Initial version 1.0
331 --
332 -- Notes: An address can only be updated if the object_version_number
333 -- is an exact match.
334 --
335 -- End of comments
336 --------------------------------------------------------------------------
337 PROCEDURE Update_Row
338 ( p_api_version IN NUMBER
339 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
340 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
341 , p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full
342 , x_return_status OUT VARCHAR2
343 , x_msg_count OUT NUMBER
344 , x_msg_data OUT VARCHAR2
345 , p_adr_rec IN AddrRec
346 , x_object_version_number OUT NUMBER
347 )
348 IS
349 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
350 l_api_version CONSTANT NUMBER := 1.0;
351 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
352 l_object_version_number NUMBER;
353 l_return_status VARCHAR2(1);
354 l_msg_count NUMBER;
355 l_msg_data VARCHAR2(2000);
356
357 BEGIN
358 -- Standard start of API savepoint
359 SAVEPOINT Update_Address_PVT;
360
361 -- Standard call to check for call compatibility
362 IF NOT FND_API.Compatible_API_Call( l_api_version
363 , p_api_version
364 , l_api_name
365 , G_PKG_NAME
366 )
367 THEN
368 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
369 END IF;
370
371 -- Initialize message list if p_init_msg_list is set to TRUE
372 IF FND_API.To_Boolean(p_init_msg_list)
373 THEN
374 FND_MSG_PUB.Initialize;
375 END IF;
376
377 -- Initialize API return status to success
378 x_return_status := FND_API.G_RET_STS_SUCCESS;
379
380 -- Try to lock the row before updating it
381 Lock_Row( p_adr_rec.address_id
382 , p_adr_rec.object_version_number
383 );
384
385
386 UPDATE JTF_CAL_ADDRESSES
387 SET RESOURCE_ID = p_adr_rec.RESOURCE_ID
388 , LAST_UPDATED_BY = p_adr_rec.LAST_UPDATED_BY -- Needs to be set by caller
389 , LAST_UPDATE_DATE = p_adr_rec.LAST_UPDATE_DATE -- Needs to be set by caller
390 , LAST_UPDATE_LOGIN = p_adr_rec.LAST_UPDATE_LOGIN -- Needs to be set by caller
391 , LAST_NAME = p_adr_rec.LAST_NAME
392 , FIRST_NAME = p_adr_rec.FIRST_NAME
393 , JOB_TITLE = p_adr_rec.JOB_TITLE
394 , COMPANY = p_adr_rec.COMPANY
395 , PRIMARY_CONTACT = p_adr_rec.PRIMARY_CONTACT
396 , CONTACT1_TYPE = p_adr_rec.CONTACT1_TYPE
397 , CONTACT1 = p_adr_rec.CONTACT1
398 , CONTACT2_TYPE = p_adr_rec.CONTACT2_TYPE
399 , CONTACT2 = p_adr_rec.CONTACT2
400 , CONTACT3_TYPE = p_adr_rec.CONTACT3_TYPE
401 , CONTACT3 = p_adr_rec.CONTACT3
402 , CONTACT4_TYPE = p_adr_rec.CONTACT4_TYPE
403 , CONTACT4 = p_adr_rec.CONTACT4
404 , CONTACT5_TYPE = p_adr_rec.CONTACT5_TYPE
405 , CONTACT5 = p_adr_rec.CONTACT5
406 , WWW_ADDRESS = p_adr_rec.WWW_ADDRESS
407 , ASSISTANT_NAME = p_adr_rec.ASSISTANT_NAME
408 , ASSISTANT_PHONE = p_adr_rec.ASSISTANT_PHONE
409 , CATEGORY = p_adr_rec.CATEGORY
410 , ADDRESS1 = p_adr_rec.ADDRESS1
411 , ADDRESS2 = p_adr_rec.ADDRESS2
412 , ADDRESS3 = p_adr_rec.ADDRESS3
413 , ADDRESS4 = p_adr_rec.ADDRESS4
414 , CITY = p_adr_rec.CITY
415 , COUNTY = p_adr_rec.COUNTY
416 , STATE = p_adr_rec.STATE
417 , ZIP = p_adr_rec.ZIP
418 , COUNTRY = p_adr_rec.COUNTRY
419 , NOTE = p_adr_rec.NOTE
420 , PRIVATE_FLAG = p_adr_rec.PRIVATE_FLAG
421 , DELETED_AS_OF = p_adr_rec.DELETED_AS_OF
422 , APPLICATION_ID = p_adr_rec.APPLICATION_ID
423 , SECURITY_GROUP_ID = p_adr_rec.SECURITY_GROUP_ID
424 , OBJECT_VERSION_NUMBER = jtf_cal_object_version_s.NEXTVAL
425 WHERE ADDRESS_ID = p_adr_rec.ADDRESS_ID
426 RETURNING OBJECT_VERSION_NUMBER INTO l_object_version_number; -- return new object version number
427
428 --
429 -- Check if the update was succesfull
430 --
431 IF (SQL%NOTFOUND)
432 THEN
433 RAISE no_data_found;
434 END IF;
435
436 --
437 -- Standard check of p_commit
438 --
439 IF FND_API.To_Boolean(p_commit)
440 THEN
441 COMMIT WORK;
442 END IF;
443
444 --
445 -- Return the new object_version_number
446 --
447 x_object_version_number := l_object_version_number;
448
449 --
450 -- Standard call to get message count and if count is 1, get message info
451 --
452 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
453 , p_data => x_msg_data
454 );
455
456 EXCEPTION
457 WHEN FND_API.G_EXC_ERROR
458 THEN
459 ROLLBACK TO Update_Address_PVT;
460 x_return_status := FND_API.G_RET_STS_ERROR;
461 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
462 , p_data => x_msg_data
463 );
464
465 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
466 THEN
467 ROLLBACK TO Update_Address_PVT;
468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
469 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
470 , p_data => x_msg_data
471 );
472
473 WHEN OTHERS
474 THEN
475 ROLLBACK TO Update_Address_PVT;
476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
477 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
478 THEN
479 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
480 , l_api_name
481 );
482 END IF;
483 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
484 , p_data => x_msg_data
485 );
486
487 END Update_Row;
488
489
490 --------------------------------------------------------------------------
491 -- Start of comments
492 -- API Name : Delete_Row
493 -- Type : Private
494 -- Description : Soft delete record in JTF_CAL_ADDRESSES table.
495 -- Pre-reqs : None
496 -- Parameters :
497 -- name direction type required?
498 -- ---- --------- ---- ---------
499 -- p_api_version IN NUMBER required
500 -- p_init_msg_list IN VARCHAR2 optional
501 -- p_commit IN VARCHAR2 optional
502 -- p_validation_level IN NUMBER optional
503 -- x_return_status OUT VARCHAR2 required
504 -- x_msg_count OUT NUMBER required
505 -- x_msg_data OUT VARCHAR2 required
506 -- p_address_id IN NUMBER required
507 -- p_object_version_number IN NUMBER required
508 --
509 -- Version : Current version 1.0
510 -- Previous version 1.0
511 -- Initial version 1.0
512 --
513 -- Notes: An address can only be deleted if the object_version_number
514 -- is an exact match.
515 --
516 -- End of comments
517 --------------------------------------------------------------------------
518 PROCEDURE Delete_Row
519 ( p_api_version IN NUMBER
520 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
521 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
522 , p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full
523 , x_return_status OUT VARCHAR2
524 , x_msg_count OUT NUMBER
525 , x_msg_data OUT VARCHAR2
526 , p_address_id IN NUMBER
527 , p_object_version_number IN NUMBER
528 )
529 IS
530 l_api_name CONSTANT VARCHAR2(30) := 'Delete_ExpressionLine';
531 l_api_version CONSTANT NUMBER := 1.1;
532 l_api_name_full CONSTANT VARCHAR2(62) := G_PKG_NAME||'.'||l_api_name;
533 l_return_status VARCHAR2(1);
534 l_msg_count NUMBER;
535 l_msg_data VARCHAR2(2000);
536 BEGIN
537 --
538 -- Establish save point
539 --
540 SAVEPOINT Delete_ExpressionLine_PVT;
541
542 --
543 -- Check version number
544 --
545 IF NOT FND_API.Compatible_API_Call( l_api_version
546 , p_api_version
547 , l_api_name
548 , G_PKG_NAME
549 )
550 THEN
551 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
552 END IF;
553
554 --
555 -- Initialize message list if requested
556 --
557 IF FND_API.to_Boolean( p_init_msg_list )
558 THEN
559 FND_MSG_PUB.initialize;
560 END IF;
561
562 -- Try to lock the row before updating it
563 Lock_Row( p_address_id
564 , p_object_version_number
565 );
566
567
568 UPDATE JTF_CAL_ADDRESSES
569 SET deleted_as_of = SYSDATE
570 WHERE address_id = p_address_id;
571
572 --
573 -- Check if the delete was succesfull
574 --
575 IF (SQL%NOTFOUND)
576 THEN
577 RAISE NO_DATA_FOUND;
578 END IF;
579
580 IF FND_API.To_Boolean( p_commit )
581 THEN
582 COMMIT WORK;
583 END IF;
584
585 --
586 -- Initialize return status to SUCCESS
587 --
588 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
589
590
591 FND_MSG_PUB.Count_And_Get( p_count => X_MSG_COUNT
592 , p_data => X_MSG_DATA
593 );
594
595 EXCEPTION
596 WHEN FND_API.G_EXC_ERROR
597 THEN
598 ROLLBACK TO Delete_ExpressionLine_PVT;
599 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
600 FND_MSG_PUB.Count_And_Get( p_count => X_MSG_COUNT
601 , p_data => X_MSG_DATA
602 );
603
604 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
605 THEN
606 ROLLBACK TO Delete_ExpressionLine_PVT;
607 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
608 FND_MSG_PUB.Count_And_Get( p_count => X_MSG_COUNT
609 , p_data => X_MSG_DATA
610 );
611
612 WHEN OTHERS
613 THEN
614 ROLLBACK TO Delete_ExpressionLine_PVT;
615 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
616 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
617 THEN
618 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
619 , l_api_name
620 );
621 END IF;
622 FND_MSG_PUB.Count_And_Get( p_count => X_MSG_COUNT
623 , p_data => X_MSG_DATA
624 );
625
626 END Delete_Row;
627
628 END JTF_CAL_Addr_PVT;