[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_CNR_GROUPS_PVT
Source
1 Package Body JTF_TERR_CNR_GROUPS_PVT AS
2 /* $Header: jtfvcngb.pls 120.0 2005/06/02 18:22:07 appldev ship $ */
3
4 -- ---------------------------------------------------
5 -- Start of Comments
6 -- ---------------------------------------------------
7 -- PACKAGE NAME: JTF_TERR_CNR_GROUPS_PVT
8 -- ---------------------------------------------------
9 -- PURPOSE
10 -- Joint task force core territory resource private api's.
11 -- This package is a private API for inserting CNR groups
12 -- into JTF tables. It contains specification
13 -- for pl/sql records and tables related to territory
14 -- resource.
15 --
16 -- Procedures:
17 -- (see below for specification)
18 --
19 -- NOTES
20 -- This package is for PRIVATE USE ONLY use
21 --
22 -- HISTORY
23 -- 01/29/01 ARPATEL Created
24 -- 05/18/01 ARPATEL Added table handlers for JTF_TERR_CNR_GROUP_VALUES.
25 -- 04/25/02 ARPATEL Removed references to security_group_id bug#2269867.
26
27 -- End of Comments
28
29
30
31
32 -- ***************************************************
33 -- GLOBAL VARIABLES
34 -- ***************************************************
35 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_TERR_CNR_GROUPS_PVT';
36 G_FILE_NAME CONSTANT VARCHAR2(12):='jtftrcng.pls';
37
38
39
40 -- ***************************************************
41 -- start of comments
42 -- ***************************************************
43 -- API name : Create_Terr_Cnr_Group
44 -- Type : PUBLIC
45 -- Function : To create territory CNR's.
46 --
47 -- Pre-reqs :
48 -- Notes:
49 --
50 --
51 -- End of Comments
52 --
53 PROCEDURE Create_Terr_Cnr_Group
54 (
55 p_Api_Version_Number IN NUMBER,
56 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
57 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
58 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
59 x_Return_Status OUT NOCOPY VARCHAR2,
60 x_Msg_Count OUT NOCOPY NUMBER,
61 x_Msg_Data OUT NOCOPY VARCHAR2,
62 p_Terr_cnr_group_rec IN Terr_cnr_group_rec_type := G_MISS_TERR_CNR_GROUP_REC,
63 x_Terr_cnr_group_out_rec OUT NOCOPY Terr_cnr_group_out_rec_type
64 )
65 IS
66 l_api_name CONSTANT VARCHAR2(30) := 'Create_Terr_Cnr_Group';
67 l_api_version_number CONSTANT NUMBER := 1.0;
68 l_DML_Operation CONSTANT VARCHAR2(30) := 'INSERT';
69 l_return_status VARCHAR2(1);
70 l_Res_Counter NUMBER;
71 l_Res_Access_Counter NUMBER;
72 l_Terr_cnr_group_rec Terr_cnr_group_rec_type;
73 l_Terr_cnr_group_out_rec Terr_cnr_group_out_rec_type;
74 l_rowid ROWID;
75 l_terr_cnr_group_Id NUMBER;
76
77 --
78 BEGIN
79 --dbms_output.put_line('Create_Terr_Cnr_Group PVT: Entering API');
80
81 -- Standard Start of API savepoint
82 SAVEPOINT CREATE_CNR_GROUP_PVT;
83
84 -- Standard call to check for call compatibility.
85 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
86 p_api_version_number,
87 l_api_name,
88 G_PKG_NAME)
89 THEN
90 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
91 END IF;
92
93 -- Initialize message list if p_init_msg_list is set to TRUE.
94 IF FND_API.to_Boolean( p_init_msg_list ) THEN
95 FND_MSG_PUB.initialize;
96 END IF;
97
98 -- Debug Message
99 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
100 THEN
101 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
102 fnd_message.set_name ('PROC_NAME', l_api_name);
103 FND_MSG_PUB.Add;
104 END IF;
105
106 -- Initialize API return status to success
107 x_return_status := FND_API.G_RET_STS_SUCCESS;
108 --
109 -- API body
110 --
111 -- ******************************************************************
112 -- Validate CNR group parameters
113 -- ******************************************************************
114
115 IF ( p_validation_level >= FND_API.G_VALID_LEVEL_FULL)
116 THEN
117 -- Debug message
118 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
119 THEN
120 FND_MESSAGE.Set_Name('JTF', 'JTF_CNR_GROUP_VALIDATE_MSG');
121 FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Cnr_Group_Value_Rec');
122 FND_MSG_PUB.Add;
123 END IF;
124
125 -- Invoke validation procedures
126 Validate_Cnr_Group_Value_Rec(p_init_msg_list => FND_API.G_FALSE,
127 x_return_status => x_return_status,
128 x_msg_count => x_msg_count,
129 x_msg_data => x_msg_data,
130 p_DML_Operation => l_DML_Operation,
131 p_Terr_cnr_group_rec => p_Terr_cnr_group_rec);
132
133 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
134 RAISE FND_API.G_EXC_ERROR;
135 END IF;
136 END IF;
137
138 JTF_TERR_CNR_GROUPS_PKG.Insert_Row(
139 x_rowid => l_rowid,
140 x_CNR_GROUP_ID => l_terr_cnr_group_Id,
141 x_LAST_UPDATED_BY => p_Terr_cnr_group_rec.LAST_UPDATED_BY,
142 x_LAST_UPDATE_DATE => p_Terr_cnr_group_rec.LAST_UPDATE_DATE,
143 x_CREATED_BY => p_Terr_cnr_group_rec.CREATED_BY,
144 x_CREATION_DATE => p_Terr_cnr_group_rec.CREATION_DATE,
145 x_LAST_UPDATE_LOGIN => p_Terr_cnr_group_rec.LAST_UPDATE_LOGIN,
146 x_NAME => p_Terr_cnr_group_rec.NAME,
147 x_DESCRIPTION => p_Terr_cnr_group_rec.DESCRIPTION,
148 x_ATTRIBUTE_CATEGORY => p_Terr_cnr_group_rec.ATTRIBUTE_CATEGORY,
149 x_ATTRIBUTE1 => p_Terr_cnr_group_rec.ATTRIBUTE1,
150 x_ATTRIBUTE2 => p_Terr_cnr_group_rec.ATTRIBUTE2,
151 x_ATTRIBUTE3 => p_Terr_cnr_group_rec.ATTRIBUTE3,
152 x_ATTRIBUTE4 => p_Terr_cnr_group_rec.ATTRIBUTE4,
153 x_ATTRIBUTE5 => p_Terr_cnr_group_rec.ATTRIBUTE5,
154 x_ATTRIBUTE6 => p_Terr_cnr_group_rec.ATTRIBUTE6,
155 x_ATTRIBUTE7 => p_Terr_cnr_group_rec.ATTRIBUTE7,
156 x_ATTRIBUTE8 => p_Terr_cnr_group_rec.ATTRIBUTE8,
157 x_ATTRIBUTE9 => p_Terr_cnr_group_rec.ATTRIBUTE9,
158 x_ATTRIBUTE10 => p_Terr_cnr_group_rec.ATTRIBUTE10,
159 x_ATTRIBUTE11 => p_Terr_cnr_group_rec.ATTRIBUTE11,
160 x_ATTRIBUTE12 => p_Terr_cnr_group_rec.ATTRIBUTE12,
161 x_ATTRIBUTE13 => p_Terr_cnr_group_rec.ATTRIBUTE13,
162 x_ATTRIBUTE14 => p_Terr_cnr_group_rec.ATTRIBUTE14,
163 x_ATTRIBUTE15 => p_Terr_cnr_group_rec.ATTRIBUTE15);
164
165 -- l_terr_cnr_group_Id := p_Terr_cnr_group_rec.CNR_GROUP_ID;
166
167 -- set cnr_group_id for later use
168 x_Terr_cnr_group_out_rec.cnr_group_id := l_terr_cnr_group_Id;
169 --l_Terr_cnr_group_out_rec.cnr_group_id := l_terr_cnr_group_Id;
170
171 -- Debug Message
172 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
173 THEN
174 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
175 fnd_message.set_name ('PROC_NAME', l_api_name);
176 FND_MSG_PUB.Add;
177 END IF;
178
179
180 FND_MSG_PUB.Count_And_Get
181 ( p_count => x_msg_count,
182 p_data => x_msg_data
183 );
184
185 -- Standard check for p_commit
186 IF FND_API.to_Boolean( p_commit )
187 THEN
188 COMMIT WORK;
189 END IF;
190
191 --dbms_output.put_line('Create_Terr_Cnr_Group PVT: Exiting API');
192 EXCEPTION
193 --
194 WHEN FND_API.G_EXC_ERROR THEN
195 --dbms_output.put_line('Create_Terr_Cnr_Group PVT: FND_API.G_EXC_ERROR');
196 ROLLBACK TO CREATE_CNR_GROUP_PVT;
197 x_return_status := FND_API.G_RET_STS_ERROR ;
198 FND_MSG_PUB.Count_And_Get
199 ( p_count => x_msg_count,
200 p_data => x_msg_data
201 );
202
203 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
204 --dbms_output.put_line('Create_Terr_Cnr_Group PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
205 ROLLBACK TO CREATE_CNR_GROUP_PVT;
206 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
207 FND_MSG_PUB.Count_And_Get
208 ( p_count => x_msg_count,
209 p_data => x_msg_data
210 );
211
212 WHEN OTHERS THEN
213 --dbms_output.put_line('Create_Terr_Cnr_Group PVT: OTHERS - ' || SQLERRM);
214 ROLLBACK TO CREATE_CNR_GROUP_PVT;
215 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
216 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
217 THEN
218 fnd_msg_pub.add_exc_msg (
219 g_pkg_name,
220 'Error inside Create_Terr_Cnr_Group ' || sqlerrm);
221 END IF;
222 --
223 END Create_Terr_Cnr_Group;
224
225
226
227
228 -- ***************************************************
229 -- start of comments
230 -- ***************************************************
231 --
232 -- API name : Delete_Terr_Cnr_Group
233 -- Type : PUBLIC
234 -- Function : To delete CNR groups
235 --
236 --
237 -- Pre-reqs :
238 -- Notes:
239 -- Rules for deletion have to be very strict
240 --
241 -- End of Comments
242 --
243
244 PROCEDURE Delete_Terr_Cnr_Group
245 (
246 p_Api_Version_Number IN NUMBER,
247 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
248 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
249 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
250 X_Return_Status OUT NOCOPY VARCHAR2,
251 X_Msg_Count OUT NOCOPY NUMBER,
252 X_Msg_Data OUT NOCOPY VARCHAR2,
253 p_Terr_cnr_group_rec IN Terr_cnr_group_rec_type := G_MISS_TERR_CNR_GROUP_REC
254 )
255 AS
256
257 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Terr_Cnr_Group';
258 l_api_version_number CONSTANT NUMBER := 1.0;
259 l_DML_Operation CONSTANT VARCHAR2(30) := 'DELETE';
260 l_return_status VARCHAR2(1);
261
262 BEGIN
263 --
264 -- Standard Start of API savepoint
265 SAVEPOINT DELETE_CNR_GROUP_PVT;
266
267 -- Standard call to check for call compatibility.
268 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
269 p_api_version_number,
270 l_api_name,
271 G_PKG_NAME)
272 THEN
273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
274 END IF;
275
276 -- Initialize message list if p_init_msg_list is set to TRUE.
277 IF FND_API.to_Boolean( p_init_msg_list ) THEN
278 FND_MSG_PUB.initialize;
279 END IF;
280
281 -- Debug Message
282 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
283 THEN
284 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
285 fnd_message.set_name ('PROC_NAME', l_api_name);
286 FND_MSG_PUB.Add;
287 END IF;
288
289 -- Initialize API return status to success
290 x_return_status := FND_API.G_RET_STS_SUCCESS;
291 --
292 -- API body
293 --
294 --
295 -- ******************************************************************
296 -- Validate CNR group parameters
297 -- ******************************************************************
298 IF ( p_validation_level >= FND_API.G_VALID_LEVEL_FULL)
299 THEN
300 -- Debug message
301 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
302 THEN
303 FND_MESSAGE.Set_Name('JTF', 'JTF_CNR_GROUP_VALIDATE_MSG');
304 FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Cnr_Group_Value_Rec');
305 FND_MSG_PUB.Add;
306 END IF;
307
308 -- Invoke validation procedures
309 Validate_Cnr_Group_Value_Rec(p_init_msg_list => FND_API.G_FALSE,
310 x_return_status => x_return_status,
311 x_msg_count => x_msg_count,
312 x_msg_data => x_msg_data,
313 p_DML_Operation => l_DML_Operation,
314 p_Terr_cnr_group_rec => p_Terr_cnr_group_rec);
315
316 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
317 RAISE FND_API.G_EXC_ERROR;
318 END IF;
319 END IF;
320
321 JTF_TERR_CNR_GROUPS_PKG.Delete_Row(
322 x_CNR_GROUP_ID => p_Terr_cnr_group_rec.cnr_group_id
323 );
324
325 delete
326 from JTF_TERR_CNR_GROUP_VALUES jt
327 where jt.cnr_group_id = p_Terr_cnr_group_rec.cnr_group_id;
328
329 -- Debug Message
330 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
331 THEN
332 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
333 fnd_message.set_name ('PROC_NAME', l_api_name);
334 FND_MSG_PUB.Add;
335 END IF;
336
337
338 FND_MSG_PUB.Count_And_Get
339 ( p_count => x_msg_count,
340 p_data => x_msg_data
341 );
342
343 -- Standard check for p_commit
344 IF FND_API.to_Boolean( p_commit )
345 THEN
346 COMMIT WORK;
347 END IF;
348
349 EXCEPTION
350 --
351 WHEN FND_API.G_EXC_ERROR THEN
352 ROLLBACK TO DELETE_CNR_GROUP_PVT;
353 x_return_status := FND_API.G_RET_STS_ERROR ;
354 FND_MSG_PUB.Count_And_Get
355 ( p_count => x_msg_count,
356 p_data => x_msg_data
357 );
358
359 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
360 ROLLBACK TO DELETE_CNR_GROUP_PVT;
361 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
362 FND_MSG_PUB.Count_And_Get
363 ( p_count => x_msg_count,
364 p_data => x_msg_data
365 );
366
367 WHEN OTHERS THEN
368 ROLLBACK TO DELETE_CNR_GROUP_PVT;
369 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
370 FND_MSG_PUB.Count_And_Get
371 ( p_count => x_msg_count,
372 p_data => x_msg_data
373 );
374 --
375 END Delete_Terr_Cnr_Group;
376
377
378
379
380 -- ***************************************************
381 -- start of comments
382 -- ***************************************************
383 -- API name : Update_Terr_Cnr_Group
384 -- Type : PUBLIC
385 -- Function : To Update CNR groups.
386 --
387 -- Pre-reqs :
388 -- Notes:
389 --
390 --
391 -- End of Comments
392 --
393
394 PROCEDURE Update_Terr_Cnr_Group
395 (
396 p_Api_Version_Number IN NUMBER,
397 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
398 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
399 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
400 x_Return_Status OUT NOCOPY VARCHAR2,
401 x_Msg_Count OUT NOCOPY NUMBER,
402 x_Msg_Data OUT NOCOPY VARCHAR2,
403 p_Terr_cnr_group_rec IN Terr_cnr_group_rec_type := G_MISS_TERR_CNR_GROUP_REC,
404 x_Terr_cnr_group_out_rec OUT NOCOPY Terr_cnr_group_out_rec_type
405 )
406 AS
407 l_api_name CONSTANT VARCHAR2(30) := 'Update_Terr_Cnr_Group';
408 l_api_version_number CONSTANT NUMBER := 1.0;
409 l_DML_Operation CONSTANT VARCHAR2(30) := 'UPDATE';
410 l_return_status VARCHAR2(1);
411 l_rowid ROWID;
412 l_terr_cnr_group_Id NUMBER := p_Terr_cnr_group_rec.CNR_GROUP_ID;
413
414 BEGIN
415 -- Standard Start of API savepoint
416 SAVEPOINT UPDATE_CNR_GROUP_PVT;
417
418 -- Standard call to check for call compatibility.
419 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
420 p_api_version_number,
421 l_api_name,
422 G_PKG_NAME)
423 THEN
424 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
425 END IF;
426
427 -- Initialize message list if p_init_msg_list is set to TRUE.
428 IF FND_API.to_Boolean( p_init_msg_list ) THEN
429 FND_MSG_PUB.initialize;
430 END IF;
431
432 -- Debug Message
433 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
434 THEN
435 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
436 fnd_message.set_name ('PROC_NAME', l_api_name);
437 FND_MSG_PUB.Add;
438 END IF;
439
440
441 -- Initialize API return status to success
442 x_return_status := FND_API.G_RET_STS_SUCCESS;
443 --
444 -- API body
445 --
446 -- ******************************************************************
447 -- Validate CNR group parameters
448 -- ******************************************************************
449 IF ( p_validation_level >= FND_API.G_VALID_LEVEL_FULL)
450 THEN
451 -- Debug message
452 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
453 THEN
454 FND_MESSAGE.Set_Name('JTF', 'JTF_CNR_GROUP_VALIDATE_MSG');
455 FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Cnr_Group_Value_Rec');
456 FND_MSG_PUB.Add;
457 END IF;
458
459 -- Invoke validation procedures
460 Validate_Cnr_Group_Value_Rec(p_init_msg_list => FND_API.G_FALSE,
461 x_return_status => x_return_status,
462 x_msg_count => x_msg_count,
463 x_msg_data => x_msg_data,
464 p_DML_Operation => l_DML_Operation,
465 p_Terr_cnr_group_rec => p_Terr_cnr_group_rec);
466
467 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
468 RAISE FND_API.G_EXC_ERROR;
469 END IF;
470 END IF;
471
472 --dbms_output.put_line('before update_row call' );
473
474 JTF_TERR_CNR_GROUPS_PKG.Update_Row(
475 x_Rowid => l_rowid,
476 x_CNR_GROUP_ID => l_terr_cnr_group_Id,
477 x_LAST_UPDATED_BY => p_Terr_cnr_group_rec.LAST_UPDATED_BY,
478 x_LAST_UPDATE_DATE => p_Terr_cnr_group_rec.LAST_UPDATE_DATE,
479 x_CREATED_BY => p_Terr_cnr_group_rec.CREATED_BY,
480 x_CREATION_DATE => p_Terr_cnr_group_rec.CREATION_DATE,
481 x_LAST_UPDATE_LOGIN => p_Terr_cnr_group_rec.LAST_UPDATE_LOGIN,
482 x_NAME => p_Terr_cnr_group_rec.NAME,
483 x_DESCRIPTION => p_Terr_cnr_group_rec.DESCRIPTION,
484 x_ATTRIBUTE_CATEGORY => p_Terr_cnr_group_rec.ATTRIBUTE_CATEGORY,
485 x_ATTRIBUTE1 => p_Terr_cnr_group_rec.ATTRIBUTE1,
486 x_ATTRIBUTE2 => p_Terr_cnr_group_rec.ATTRIBUTE2,
487 x_ATTRIBUTE3 => p_Terr_cnr_group_rec.ATTRIBUTE3,
488 x_ATTRIBUTE4 => p_Terr_cnr_group_rec.ATTRIBUTE4,
489 x_ATTRIBUTE5 => p_Terr_cnr_group_rec.ATTRIBUTE5,
490 x_ATTRIBUTE6 => p_Terr_cnr_group_rec.ATTRIBUTE6,
491 x_ATTRIBUTE7 => p_Terr_cnr_group_rec.ATTRIBUTE7,
492 x_ATTRIBUTE8 => p_Terr_cnr_group_rec.ATTRIBUTE8,
493 x_ATTRIBUTE9 => p_Terr_cnr_group_rec.ATTRIBUTE9,
494 x_ATTRIBUTE10 => p_Terr_cnr_group_rec.ATTRIBUTE10,
495 x_ATTRIBUTE11 => p_Terr_cnr_group_rec.ATTRIBUTE11,
496 x_ATTRIBUTE12 => p_Terr_cnr_group_rec.ATTRIBUTE12,
497 x_ATTRIBUTE13 => p_Terr_cnr_group_rec.ATTRIBUTE13,
498 x_ATTRIBUTE14 => p_Terr_cnr_group_rec.ATTRIBUTE14,
499 x_ATTRIBUTE15 => p_Terr_cnr_group_rec.ATTRIBUTE15
500 );
501 --dbms_output.put_line('after update_row call' );
502
503 -- set cnr_group_id for later use
504 x_Terr_cnr_group_out_rec.cnr_group_id := l_terr_cnr_group_Id;
505
506 -- Debug Message
507 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
508 THEN
509 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
510 fnd_message.set_name ('PROC_NAME', l_api_name);
511 FND_MSG_PUB.Add;
512 END IF;
513
514
515 FND_MSG_PUB.Count_And_Get
516 ( p_count => x_msg_count,
517 p_data => x_msg_data
518 );
519
520 -- Standard check for p_commit
521 IF FND_API.to_Boolean( p_commit )
522 THEN
523 COMMIT WORK;
524 END IF;
525 --
526 EXCEPTION
527 --
528 WHEN FND_API.G_EXC_ERROR THEN
529 ROLLBACK TO UPDATE_CNR_GROUP_PVT;
530 x_return_status := FND_API.G_RET_STS_ERROR ;
531 FND_MSG_PUB.Count_And_Get
532 ( p_count => x_msg_count,
533 p_data => x_msg_data
534 );
535
536 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
537 ROLLBACK TO UPDATE_CNR_GROUP_PVT;
538 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
539 FND_MSG_PUB.Count_And_Get
540 ( p_count => x_msg_count,
541 p_data => x_msg_data
542 );
543
544 WHEN OTHERS THEN
545 ROLLBACK TO UPDATE_CNR_GROUP_PVT;
546 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
547 FND_MSG_PUB.Count_And_Get
548 ( p_count => x_msg_count,
549 p_data => x_msg_data
550 );
551 --
552 END Update_Terr_Cnr_Group;
553
554 ---------------------------------------------------------------------
555 -- Validate the CNR group Values passed in
556 ---------------------------------------------------------------------
557 -- Columns Validated
558 -- Make sure the values are in the right columns as per the
559 -- qualifer setup
560 -- Eg:
561 -- If the qualifer, diplay_type = 'CHAR' and
562 -- col1_data_type = 'NUMBER'
563 -- then make sure the ID is passed in LOW_VALUE_CHAR_ID
564 --
565 --
566 ---------------------------------------------------------------------
567 PROCEDURE Validate_Cnr_Group_Value_Rec
568 (p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
569 x_Return_Status OUT NOCOPY VARCHAR2,
570 x_msg_count OUT NOCOPY NUMBER,
571 x_msg_data OUT NOCOPY VARCHAR2,
572 p_DML_Operation IN VARCHAR2,
573 p_Terr_cnr_group_rec IN Terr_cnr_group_rec_type := G_MISS_TERR_CNR_GROUP_REC)
574 AS
575 l_display_type VARCHAR2(30);
576 l_qual_col1_datatype VARCHAR2(30);
577 l_convert_to_id_flag VARCHAR2(01);
578 l_cnr_child_num NUMBER;
579 l_cnr_group_num NUMBER;
580 l_cnr_qual_defined NUMBER;
581
582 CURSOR c_cnrgroupname IS
583 select 1
584 from JTF_TERR_CNR_GROUPS jtcg
585 where jtcg.NAME = p_Terr_cnr_group_rec.NAME
586 and rownum < 2;
587
588 CURSOR c_cnrgroupid IS
589 select 1
590 from JTF_TERR_VALUES_ALL jtva
591 where jtva.CNR_GROUP_ID = p_Terr_cnr_group_rec.CNR_GROUP_ID
592 and rownum < 2;
593
594 CURSOR c_cnrgroupname_update IS
595 select 1
596 from JTF_TERR_CNR_GROUPS jtcg
597 where jtcg.NAME = p_Terr_cnr_group_rec.NAME
598 and jtcg.CNR_GROUP_ID <> p_Terr_cnr_group_rec.CNR_GROUP_ID
599 and rownum < 2;
600
601 CURSOR c_qualifier_defined IS
602 select 1
603 from jtf_terr_values_all jtv, jtf_terr_qual_all jtq
604 where jtq.qual_usg_id = -1102
605 and jtv.terr_qual_id = jtq.terr_qual_id
606 and jtv.low_value_char_id = p_Terr_cnr_group_rec.CNR_GROUP_ID
607 and rownum < 2;
608 BEGIN
609 --dbms_output.put_line('Validate_Cnr_Group_Value_Rec: - cnr_group_id');
610
611 -- Initialize the status to success
612 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
613
614 IF ( p_DML_Operation = 'DELETE' ) THEN
615 --dbms_output.put_line('DELETE');
616 OPEN c_qualifier_defined;
617 FETCH c_qualifier_defined INTO l_cnr_qual_defined;
618
619 OPEN c_cnrgroupid;
620 FETCH c_cnrgroupid INTO l_cnr_child_num;
621
622 IF (c_cnrgroupid%FOUND) OR (c_qualifier_defined%FOUND) THEN
623 --dbms_output.put_line('c_cnrgroupid%FOUND');
624 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
625 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_CNR_HAS_FKREL');
626 FND_MESSAGE.Set_Token('COL_NAME', 'CNR_GROUP_ID' );
627 FND_MSG_PUB.ADD;
628 END IF;
629 x_Return_Status := FND_API.G_RET_STS_ERROR ;
630 END IF;
631 CLOSE c_cnrgroupid;
632
633
634 ELSIF (p_DML_Operation IN ('INSERT','UPDATE')) THEN
635 --dbms_output.put_line('INSERT,UPDATE');
636 --Check created by
637 IF ( p_Terr_cnr_group_rec.CREATED_BY is NULL OR
638 p_Terr_cnr_group_rec.CREATED_BY = FND_API.G_MISS_NUM ) THEN
639 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
640 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
641 FND_MESSAGE.Set_Token('COL_NAME', 'CREATED_BY' );
642 FND_MSG_PUB.ADD;
643 END IF;
644 x_Return_Status := FND_API.G_RET_STS_ERROR ;
645 End If;
646
647 --Check creation date
648 If ( p_Terr_cnr_group_rec.CREATION_DATE is NULL OR
649 p_Terr_cnr_group_rec.CREATION_DATE = FND_API.G_MISS_DATE ) THEN
650 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
651 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
652 FND_MESSAGE.Set_Token('COL_NAME', 'CREATION_DATE' );
653 FND_MSG_PUB.ADD;
654 END IF;
655 x_Return_Status := FND_API.G_RET_STS_ERROR ;
656 End If;
657
658 -- Validate last updated by
659 IF ( p_Terr_cnr_group_rec.LAST_UPDATED_BY is NULL OR
660 p_Terr_cnr_group_rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM) THEN
661 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
662 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
663 FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATED_BY' );
664 FND_MSG_PUB.ADD;
665 END IF;
666 x_Return_Status := FND_API.G_RET_STS_ERROR ;
667 End If;
668
669 -- Check last update date
670 If ( p_Terr_cnr_group_rec.LAST_UPDATE_DATE IS NULL OR
671 p_Terr_cnr_group_rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE ) THEN
672 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
673 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
674 FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_DATE' );
675 FND_MSG_PUB.ADD;
676 END IF;
677 x_Return_Status := FND_API.G_RET_STS_ERROR ;
678 End If;
679
680 --Check last update login
681 If ( p_Terr_cnr_group_rec.LAST_UPDATE_LOGIN is NULL OR
682 p_Terr_cnr_group_rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM ) THEN
683 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
684 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
685 FND_MESSAGE.Set_Token('COL_NAME', 'LAST_UPDATE_LOGIN' );
686 FND_MSG_PUB.ADD;
687 END IF;
688 x_Return_Status := FND_API.G_RET_STS_ERROR ;
689 End If;
690
691 --Check CNR group name
692 If ( p_Terr_cnr_group_rec.NAME is NULL OR
693 p_Terr_cnr_group_rec.NAME = FND_API.G_MISS_CHAR ) THEN
694 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
695 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
696 FND_MESSAGE.Set_Token('COL_NAME', 'NAME' );
697 FND_MSG_PUB.ADD;
698 END IF;
699 x_Return_Status := FND_API.G_RET_STS_ERROR ;
700 End If;
701
702 IF ( p_DML_Operation = 'INSERT' ) THEN
703 --dbms_output.put_line('INSERT CHECK AT END');
704 --Check for duplicate CNR group name
705 OPEN c_cnrgroupname;
706 FETCH c_cnrgroupname INTO l_cnr_group_num;
707
708 IF c_cnrgroupname%FOUND THEN
709 --dbms_output.put_line('c_cnrgroupname%FOUND');
710 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
711 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_CNR_DUPLICATE_NAME');
712 FND_MESSAGE.Set_Token('COL_NAME', 'NAME' );
713 FND_MSG_PUB.ADD;
714 END IF;
715 x_Return_Status := FND_API.G_RET_STS_ERROR ;
716 END IF;
717 CLOSE c_cnrgroupname;
718
719 --dbms_output.put_line('c_cnrgroupname CURSOR CALL');
720
721
722 ELSIF ( p_DML_Operation = 'UPDATE' ) THEN
723 --dbms_output.put_line('first update check');
724 --Check CNR group Id
725 If ( p_Terr_cnr_group_rec.CNR_GROUP_ID is NULL OR
726 p_Terr_cnr_group_rec.CNR_GROUP_ID = FND_API.G_MISS_NUM ) THEN
727 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
728 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_COL_VALUE');
729 FND_MESSAGE.Set_Token('COL_NAME', 'CNR_GROUP_ID' );
730 FND_MSG_PUB.ADD;
731 END IF;
732 x_Return_Status := FND_API.G_RET_STS_ERROR ;
733 End If;
734 --dbms_output.put_line('UPDATE CHECK AT END');
735
736 OPEN c_cnrgroupname_update;
737 FETCH c_cnrgroupname_update INTO l_cnr_group_num;
738
739 IF c_cnrgroupname_update%FOUND THEN
740 --dbms_output.put_line('c_cnrgroupname_update%FOUND');
741 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
742 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_CNR_DUPLICATE_NAME');
743 FND_MESSAGE.Set_Token('COL_NAME', 'NAME' );
744 FND_MSG_PUB.ADD;
745 END IF;
746 x_Return_Status := FND_API.G_RET_STS_ERROR ;
747 END IF;
748 CLOSE c_cnrgroupname_update;
749
750 END IF;
751
752 END IF;
753 --
754 --dbms_output.put_line('BEFORE COUNT and GET');
755 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
756 p_data => x_msg_data);
757 --dbms_output.put_line('AFTER COUNT and GET');
758
759 EXCEPTION
760 --
761 WHEN FND_API.G_EXC_ERROR THEN
762 --dbms_output('Validate_Cnr_Group_Value_Rec: FND_API.G_EXC_ERROR');
763 x_return_status := FND_API.G_RET_STS_ERROR ;
764 FND_MSG_PUB.Count_And_Get
765 ( P_count => x_msg_count,
766 P_data => x_msg_data
767 );
768
769 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
770 --dbms_output('Validate_Cnr_Group_Value_Rec: FND_API.G_EXC_UNEXPECTED_ERROR');
771 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
772 FND_MSG_PUB.Count_And_Get
773 ( P_count => x_msg_count,
774 P_data => x_msg_data
775 );
776
777 WHEN OTHERS THEN
778 --dbms_output('Validate_Cnr_Group_Value_Rec: OTHERS - ' || SQLERRM);
779 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
780
781 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_UNEXPECTED_ERROR');
782 FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Cnr_Group_Value_Rec' );
783 FND_MESSAGE.Set_Token('ERROR', sqlerrm );
784 FND_MSG_PUB.ADD;
785
786 FND_MSG_PUB.Count_And_Get
787 ( P_count => x_msg_count,
788 P_data => x_msg_data
789 );
790
791 End Validate_Cnr_Group_Value_Rec;
792
793 PROCEDURE Create_Terr_Cnr_Value
794 (
795 p_Api_Version_Number IN NUMBER,
796 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
797 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
798 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
799 x_Return_Status OUT NOCOPY VARCHAR2,
800 x_Msg_Count OUT NOCOPY NUMBER,
801 x_Msg_Data OUT NOCOPY VARCHAR2,
802 p_Terr_cnr_values_rec IN Terr_cnr_values_rec_type := G_MISS_TERR_CNR_VALUES_REC,
803 x_Terr_cnr_values_out_rec OUT NOCOPY Terr_cnr_values_out_rec_type
804 ) AS
805 l_api_name CONSTANT VARCHAR2(30) := 'Create_Terr_Cnr_Values';
806 l_api_version_number CONSTANT NUMBER := 1.0;
807 l_DML_Operation CONSTANT VARCHAR2(30) := 'INSERT';
808 l_return_status VARCHAR2(1);
809 l_Terr_cnr_values_rec Terr_cnr_values_rec_type;
810 l_Terr_cnr_values_out_rec Terr_cnr_values_out_rec_type;
811 l_rowid ROWID;
812 l_terr_cnr_group_value_Id NUMBER;
813
814 --
815 BEGIN
816 --dbms_output.put_line('Create_Terr_Cnr_Values PVT: Entering API');
817
818 -- Standard Start of API savepoint
819 SAVEPOINT CREATE_CNR_VALUES_PVT;
820
821 -- Standard call to check for call compatibility.
822 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
823 p_api_version_number,
824 l_api_name,
825 G_PKG_NAME)
826 THEN
827 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
828 END IF;
829
830 -- Initialize message list if p_init_msg_list is set to TRUE.
831 IF FND_API.to_Boolean( p_init_msg_list ) THEN
832 FND_MSG_PUB.initialize;
833 END IF;
834
835 -- Debug Message
836 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
837 THEN
838 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
839 fnd_message.set_name ('PROC_NAME', l_api_name);
840 FND_MSG_PUB.Add;
841 END IF;
842
843 -- Initialize API return status to success
844 x_return_status := FND_API.G_RET_STS_SUCCESS;
845 --
846 -- API body
847
848 -- Debug message
849 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
850 THEN
851 FND_MESSAGE.Set_Name('JTF', 'JTF_CNR_VALUES_VALIDATE_MSG');
852 FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Cnr_Group_Value_Rec');
853 FND_MSG_PUB.Add;
854 END IF;
855
856 JTF_TERR_CNR_GROUP_VALUES_PKG.Insert_Row(
857 x_rowid => l_rowid,
858 x_CNR_GROUP_VALUE_ID => l_terr_cnr_group_value_Id,
859 x_LAST_UPDATED_BY => p_Terr_cnr_values_rec.LAST_UPDATED_BY,
860 x_LAST_UPDATE_DATE => p_Terr_cnr_values_rec.LAST_UPDATE_DATE,
861 x_CREATED_BY => p_Terr_cnr_values_rec.CREATED_BY,
862 x_CREATION_DATE => p_Terr_cnr_values_rec.CREATION_DATE,
863 x_LAST_UPDATE_LOGIN => p_Terr_cnr_values_rec.LAST_UPDATE_LOGIN,
864 x_CNR_GROUP_ID => p_Terr_cnr_values_rec.CNR_GROUP_ID,
865 x_COMPARISON_OPERATOR => p_Terr_cnr_values_rec.COMPARISON_OPERATOR,
866 x_LOW_VALUE_CHAR => p_Terr_cnr_values_rec.LOW_VALUE_CHAR,
867 x_HIGH_VALUE_CHAR => p_Terr_cnr_values_rec.HIGH_VALUE_CHAR,
868 x_START_DATE_ACTIVE => p_Terr_cnr_values_rec.START_DATE_ACTIVE,
869 x_END_DATE_ACTIVE => p_Terr_cnr_values_rec.END_DATE_ACTIVE,
870 x_ORG_ID => p_Terr_cnr_values_rec.ORG_ID
871 );
872
873 -- set cnr_group_value_id for later use
874 x_Terr_cnr_values_out_rec.cnr_group_value_id := l_terr_cnr_group_value_Id;
875
876
877 -- Debug Message
878 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
879 THEN
880 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
881 fnd_message.set_name ('PROC_NAME', l_api_name);
882 FND_MSG_PUB.Add;
883 END IF;
884
885
886 FND_MSG_PUB.Count_And_Get
887 ( p_count => x_msg_count,
888 p_data => x_msg_data
889 );
890
891 -- Standard check for p_commit
892 IF FND_API.to_Boolean( p_commit )
893 THEN
894 COMMIT WORK;
895 END IF;
896
897 --dbms_output.put_line('Create_Terr_Cnr_Values PVT: Exiting API');
898 EXCEPTION
899 --
900 WHEN FND_API.G_EXC_ERROR THEN
901 --dbms_output.put_line('Create_Terr_Cnr_Values PVT: FND_API.G_EXC_ERROR');
902 ROLLBACK TO CREATE_CNR_VALUES_PVT;
903 x_return_status := FND_API.G_RET_STS_ERROR ;
904 FND_MSG_PUB.Count_And_Get
905 ( p_count => x_msg_count,
906 p_data => x_msg_data
907 );
908
909 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
910 --dbms_output.put_line('Create_Terr_Cnr_Values PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
911 ROLLBACK TO CREATE_CNR_VALUES_PVT;
912 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
913 FND_MSG_PUB.Count_And_Get
914 ( p_count => x_msg_count,
915 p_data => x_msg_data
916 );
917
918 WHEN OTHERS THEN
919 --dbms_output.put_line('Create_Terr_Cnr_Values PVT: OTHERS - ' || SQLERRM);
920 ROLLBACK TO CREATE_CNR_VALUES_PVT;
921 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
922 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
923 THEN
924 fnd_msg_pub.add_exc_msg (
925 g_pkg_name,
926 'Error inside Create_Terr_Cnr_Group ' || sqlerrm);
927 END IF;
928 --
929
930 End Create_Terr_Cnr_Value;
931
932 PROCEDURE Delete_Terr_Cnr_Value
933 (
934 p_Api_Version_Number IN NUMBER,
935 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
936 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
937 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
938 X_Return_Status OUT NOCOPY VARCHAR2,
939 X_Msg_Count OUT NOCOPY NUMBER,
940 X_Msg_Data OUT NOCOPY VARCHAR2,
941 p_Terr_cnr_values_rec IN Terr_cnr_values_rec_type := G_MISS_TERR_CNR_VALUES_REC
942 ) AS
943
944 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Terr_Cnr_Value';
945 l_api_version_number CONSTANT NUMBER := 1.0;
946 l_DML_Operation CONSTANT VARCHAR2(30) := 'DELETE';
947 l_return_status VARCHAR2(1);
948
949 BEGIN
950 --
951 -- Standard Start of API savepoint
952 SAVEPOINT DELETE_CNR_VALUE_PVT;
953
954 -- Standard call to check for call compatibility.
955 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
956 p_api_version_number,
957 l_api_name,
958 G_PKG_NAME)
959 THEN
960 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
961 END IF;
962
963 -- Initialize message list if p_init_msg_list is set to TRUE.
964 IF FND_API.to_Boolean( p_init_msg_list ) THEN
965 FND_MSG_PUB.initialize;
966 END IF;
967
968 -- Debug Message
969 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
970 THEN
971 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
972 fnd_message.set_name ('PROC_NAME', l_api_name);
973 FND_MSG_PUB.Add;
974 END IF;
975
976 -- Initialize API return status to success
977 x_return_status := FND_API.G_RET_STS_SUCCESS;
978 --
979 -- API body
980 --
981 JTF_TERR_CNR_GROUP_VALUES_PKG.Delete_Row(
982 x_CNR_GROUP_VALUE_ID => p_Terr_cnr_values_rec.cnr_group_value_id
983 );
984
985 -- Debug Message
986 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
987 THEN
988 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
989 fnd_message.set_name ('PROC_NAME', l_api_name);
990 FND_MSG_PUB.Add;
991 END IF;
992
993
994 FND_MSG_PUB.Count_And_Get
995 ( p_count => x_msg_count,
996 p_data => x_msg_data
997 );
998
999 -- Standard check for p_commit
1000 IF FND_API.to_Boolean( p_commit )
1001 THEN
1002 COMMIT WORK;
1003 END IF;
1004
1005 EXCEPTION
1006 --
1007 WHEN FND_API.G_EXC_ERROR THEN
1008 ROLLBACK TO DELETE_CNR_VALUE_PVT;
1009 x_return_status := FND_API.G_RET_STS_ERROR ;
1010 FND_MSG_PUB.Count_And_Get
1011 ( p_count => x_msg_count,
1012 p_data => x_msg_data
1013 );
1014
1015 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1016 ROLLBACK TO DELETE_CNR_VALUE_PVT;
1017 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1018 FND_MSG_PUB.Count_And_Get
1019 ( p_count => x_msg_count,
1020 p_data => x_msg_data
1021 );
1022
1023 WHEN OTHERS THEN
1024 ROLLBACK TO DELETE_CNR_VALUE_PVT;
1025 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1026 FND_MSG_PUB.Count_And_Get
1027 ( p_count => x_msg_count,
1028 p_data => x_msg_data
1029 );
1030 --
1031
1032 End Delete_Terr_Cnr_Value;
1033
1034 PROCEDURE Update_Terr_Cnr_Value
1035 (
1036 p_Api_Version_Number IN NUMBER,
1037 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1038 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1039 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1040 x_Return_Status OUT NOCOPY VARCHAR2,
1041 x_Msg_Count OUT NOCOPY NUMBER,
1042 x_Msg_Data OUT NOCOPY VARCHAR2,
1043 p_Terr_cnr_values_rec IN Terr_cnr_values_rec_type := G_MISS_TERR_CNR_VALUES_REC,
1044 x_Terr_cnr_values_out_rec OUT NOCOPY Terr_cnr_values_out_rec_type
1045 ) AS
1046 l_api_name CONSTANT VARCHAR2(30) := 'Update_Terr_Cnr_Values';
1047 l_api_version_number CONSTANT NUMBER := 1.0;
1048 l_DML_Operation CONSTANT VARCHAR2(30) := 'UPDATE';
1049 l_return_status VARCHAR2(1);
1050 l_Terr_cnr_values_rec Terr_cnr_values_rec_type;
1051 l_Terr_cnr_values_out_rec Terr_cnr_values_out_rec_type;
1052 l_rowid ROWID;
1053 l_terr_cnr_group_value_Id NUMBER := p_Terr_cnr_values_rec.CNR_GROUP_VALUE_ID;
1054
1055 --
1056 BEGIN
1057 --dbms_output.put_line('Update_Terr_Cnr_Values PVT: Entering API');
1058
1059 -- Standard Start of API savepoint
1060 SAVEPOINT UPDATE_CNR_VALUES_PVT;
1061
1062 -- Standard call to check for call compatibility.
1063 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1064 p_api_version_number,
1065 l_api_name,
1066 G_PKG_NAME)
1067 THEN
1068 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1069 END IF;
1070
1071 -- Initialize message list if p_init_msg_list is set to TRUE.
1072 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1073 FND_MSG_PUB.initialize;
1074 END IF;
1075
1076 -- Debug Message
1077 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1078 THEN
1079 fnd_message.set_name ('JTF', 'JTF_TERR_START_MSG');
1080 fnd_message.set_name ('PROC_NAME', l_api_name);
1081 FND_MSG_PUB.Add;
1082 END IF;
1083
1084 -- Initialize API return status to success
1085 x_return_status := FND_API.G_RET_STS_SUCCESS;
1086 --
1087 -- API body
1088
1089 -- Debug message
1090 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1091 THEN
1092 FND_MESSAGE.Set_Name('JTF', 'JTF_CNR_VALUES_VALIDATE_MSG');
1093 FND_MESSAGE.Set_Token('PROC_NAME', 'Validate_Cnr_Group_Value_Rec');
1094 FND_MSG_PUB.Add;
1095 END IF;
1096
1097 JTF_TERR_CNR_GROUP_VALUES_PKG.Update_Row(
1098 x_rowid => l_rowid,
1099 x_CNR_GROUP_VALUE_ID => l_terr_cnr_group_value_Id,
1100 x_LAST_UPDATED_BY => p_Terr_cnr_values_rec.LAST_UPDATED_BY,
1101 x_LAST_UPDATE_DATE => p_Terr_cnr_values_rec.LAST_UPDATE_DATE,
1102 x_CREATED_BY => p_Terr_cnr_values_rec.CREATED_BY,
1103 x_CREATION_DATE => p_Terr_cnr_values_rec.CREATION_DATE,
1104 x_LAST_UPDATE_LOGIN => p_Terr_cnr_values_rec.LAST_UPDATE_LOGIN,
1105 x_CNR_GROUP_ID => p_Terr_cnr_values_rec.CNR_GROUP_ID,
1106 x_COMPARISON_OPERATOR => p_Terr_cnr_values_rec.COMPARISON_OPERATOR,
1107 x_LOW_VALUE_CHAR => p_Terr_cnr_values_rec.LOW_VALUE_CHAR,
1108 x_HIGH_VALUE_CHAR => p_Terr_cnr_values_rec.HIGH_VALUE_CHAR,
1109 x_START_DATE_ACTIVE => p_Terr_cnr_values_rec.START_DATE_ACTIVE,
1110 x_END_DATE_ACTIVE => p_Terr_cnr_values_rec.END_DATE_ACTIVE,
1111 x_ORG_ID => p_Terr_cnr_values_rec.ORG_ID
1112 );
1113
1114 -- set cnr_group_value_id for later use
1115 x_Terr_cnr_values_out_rec.cnr_group_value_id := l_terr_cnr_group_value_Id;
1116
1117
1118 -- Debug Message
1119 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1120 THEN
1121 fnd_message.set_name ('JTF', 'JTF_TERR_END_MSG');
1122 fnd_message.set_name ('PROC_NAME', l_api_name);
1123 FND_MSG_PUB.Add;
1124 END IF;
1125
1126
1127 FND_MSG_PUB.Count_And_Get
1128 ( p_count => x_msg_count,
1129 p_data => x_msg_data
1130 );
1131
1132 -- Standard check for p_commit
1133 IF FND_API.to_Boolean( p_commit )
1134 THEN
1135 COMMIT WORK;
1136 END IF;
1137
1138 --dbms_output.put_line('Update_Terr_Cnr_Values PVT: Exiting API');
1139 EXCEPTION
1140 --
1141 WHEN FND_API.G_EXC_ERROR THEN
1142 --dbms_output.put_line('Update_Terr_Cnr_Values PVT: FND_API.G_EXC_ERROR');
1143 ROLLBACK TO UPDATE_CNR_VALUES_PVT;
1144 x_return_status := FND_API.G_RET_STS_ERROR ;
1145 FND_MSG_PUB.Count_And_Get
1146 ( p_count => x_msg_count,
1147 p_data => x_msg_data
1148 );
1149
1150 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1151 --dbms_output.put_line('Update_Terr_Cnr_Values PVT: FND_API.G_EXC_UNEXPECTED_ERROR');
1152 ROLLBACK TO UPDATE_CNR_VALUES_PVT;
1153 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1154 FND_MSG_PUB.Count_And_Get
1155 ( p_count => x_msg_count,
1156 p_data => x_msg_data
1157 );
1158
1159 WHEN OTHERS THEN
1160 --dbms_output.put_line('Update_Terr_Cnr_Values PVT: OTHERS - ' || SQLERRM);
1161 ROLLBACK TO UPDATE_CNR_VALUES_PVT;
1162 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1163 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1164 THEN
1165 fnd_msg_pub.add_exc_msg (
1166 g_pkg_name,
1167 'Error inside Update_Terr_Cnr_Group ' || sqlerrm);
1168 END IF;
1169 --
1170 End Update_Terr_Cnr_Value;
1171
1172
1173 END JTF_TERR_CNR_GROUPS_PVT;