[Home] [Help]
PACKAGE BODY: APPS.JTF_QUALIFIER_PVT
Source
1 Package Body JTF_QUALIFIER_PVT AS
2 /* $Header: jtfvtrqb.pls 120.0 2005/06/02 18:22:56 appldev ship $ */
3
4 -- ---------------------------------------------------
5 -- Start of Comments
6 -- ---------------------------------------------------
7 -- PACKAGE NAME: JTF_QUALIFIER_PVT
8 -- ---------------------------------------------------
9 -- PURPOSE
10 -- Joint task force core territory manager private api's.
11 -- This package is a private API for inserting, updating and deleting
12 -- qualifier related information into JTF tables.
13 -- It contains specification for pl/sql records and tables
14 -- and the Private territory related API's.
15 --
16 -- Procedures:
17 --
18 --
19 -- NOTES
20 -- This package is available for private use only.
21 --
22 -- HISTORY
23 -- 07/15/99 JDOCHERT Created
24 -- 03/28/00 VNEDUNGA Adding new columns for eliminating
25 -- dependency to AS_INTERESTS in
26 -- JTF_QUAL_USGS table
27 --
28 -- End of Comments
29 --
30
31
32 -- ***************************************************
33 -- GLOBAL VARIABLES
34 -- ***************************************************
35
36 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_QUALIFIER_PVT';
37 G_FILE_NAME CONSTANT VARCHAR2(12):='jtfvtrqb.pls';
38
39 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
40 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
41 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
42 G_APP_SHORT_NAME VARCHAR2(50) := FND_GLOBAL.Application_Short_Name;
43
44
45 -- ******************************************************
46 -- PRIVATE ROUTINES
47 -- ******************************************************
48
49 /* Returns TRUE if mandatory information is missing from record,
50 ** otherwise returns FALSE if information is complete
51 */
52 FUNCTION is_seed_qual_rec_missing
53 ( p_seed_qual_rec IN JTF_QUALIFIER_PUB.Seed_Qual_Rec_Type)
54 RETURN VARCHAR2
55 IS
56 BEGIN
57
58 IF (p_seed_qual_rec.name IS NULL OR
59 p_seed_qual_rec.name = FND_API.G_MISS_CHAR OR
60 p_seed_qual_rec.description IS NULL OR
61 p_seed_qual_rec.description = FND_API.G_MISS_CHAR)
62 THEN
63 RETURN FND_API.G_TRUE;
64 ELSE
65 RETURN FND_API.G_FALSE;
66 END IF;
67
68 END is_seed_qual_rec_missing;
69
70
71 /* Validate the record information
72 ** All mandatory items are present
73 ** Convert missing values to defaults
74 */
75 PROCEDURE validate_seed_qual_rec
76 ( p_seed_qual_rec IN JTF_QUALIFIER_PUB.Seed_Qual_Rec_Type,
77 p_validation_mode IN VARCHAR2,
78 p_validation_level IN NUMBER,
79 x_return_status OUT NOCOPY VARCHAR2)
80 IS
81 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
82
83 l_seed_qual_rec JTF_QUALIFIER_PUB.Seed_Qual_Rec_Type := p_seed_qual_rec;
84
85 BEGIN
86
87
88
89 -- IF ( p_validation_level >= JTF_CTM_UTILITY_PVT.G_VALID_LEVEL_ITEM ) THEN
90
91 /* If record is being updated, check that primary key is not null */
92 IF ( (p_validation_mode = JTF_CTM_UTILITY_PVT.G_UPDATE) AND
93 (l_seed_qual_rec.seeded_qual_id IS NULL OR
94 l_seed_qual_rec.seeded_qual_id = FND_API.G_MISS_NUM) )THEN
95
96
97 l_return_status := FND_API.G_RET_STS_ERROR;
98 END IF;
99
100 -- IF ( p_validation_level >= JTF_CTM_UTILITY_PVT.G_VALID_LEVEL_RECORD ) THEN
101
102 /* Check that all mandatory items exist in record */
103 IF (is_seed_qual_rec_missing (p_seed_qual_rec) = FND_API.G_TRUE) THEN
104
105 l_return_status := FND_API.G_RET_STS_ERROR;
106
107 /* Debug message */
108 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
109 FND_MESSAGE.Set_Name ('JTF', 'PVTQUAL API:Miss req itms');
110 FND_MESSAGE.Set_Token ('COLUMN', 'NAME, DESCRIPTION');
111 FND_MSG_PUB.ADD;
112 END IF;
113
114 END IF;
115
116 x_return_status := l_return_status;
117
118
119
120 END validate_seed_qual_rec;
121
122
123 /* Insert seeded qualifier record into database */
124 PROCEDURE Create_Seed_Qual_Record
125 ( p_seed_qual_rec IN JTF_QUALIFIER_PUB.Seed_Qual_Rec_Type,
126 x_seed_qual_out_rec OUT NOCOPY JTF_QUALIFIER_PUB.Seed_Qual_Out_Rec_Type )
127 IS
128
129 CURSOR c_chk_qual_name (l_qual_name VARCHAR2) IS
130 SELECT seeded_qual_id
131 FROM JTF_SEEDED_QUAL
132 WHERE UPPER(name) = UPPER(l_qual_name);
133
134 l_seeded_qual_id_csr NUMBER;
135
136 l_rowid ROWID;
137 l_seed_qual_rec JTF_QUALIFIER_PUB.Seed_Qual_Rec_Type := JTF_QUALIFIER_PUB.G_MISS_SEED_QUAL_REC;
138 l_seed_qual_out_rec JTF_QUALIFIER_PUB.Seed_Qual_Out_Rec_Type := JTF_QUALIFIER_PUB.G_MISS_SEED_QUAL_OUT_REC;
139
140 BEGIN
141
142 -- Initialise API return status to success
143 l_seed_qual_out_rec.return_status := FND_API.G_RET_STS_SUCCESS;
144
145 -- initialise local seeded qualifier record
146 l_seed_qual_rec := p_seed_qual_rec;
147
148 -- IF (l_seed_qual_rec.seeded_qual_id = FND_API.G_MISS_NUM) THEN
149 -- END IF;
150 -- check if qualifier with this name already exists
151 OPEN c_chk_qual_name (l_seed_qual_rec.name);
152 FETCH c_chk_qual_name INTO l_seeded_qual_id_csr;
153
154 IF c_chk_qual_name%NOTFOUND THEN
155
156
157
158 -- convert id to null, so that next value of sequence will
159 -- be selected in default table handler
160 IF (l_seed_qual_rec.seeded_qual_id = FND_API.G_MISS_NUM) THEN
161
162 l_seed_qual_rec.seeded_qual_id := NULL;
163 END IF;
164
165 -- Call INSERT table handler
166 JTF_SEEDED_QUAL_PKG.INSERT_ROW(
167 X_Rowid => l_rowid,
168 X_SEEDED_QUAL_ID => l_seed_qual_rec.seeded_qual_id,
169 X_LAST_UPDATE_DATE => l_seed_qual_rec.LAST_UPDATE_DATE,
170 X_LAST_UPDATED_BY => l_seed_qual_rec.LAST_UPDATED_BY,
171 X_CREATION_DATE => l_seed_qual_rec.CREATION_DATE,
172 X_CREATED_BY => l_seed_qual_rec.CREATED_BY,
173 X_LAST_UPDATE_LOGIN => l_seed_qual_rec.LAST_UPDATE_LOGIN,
174 X_NAME => l_seed_qual_rec.NAME,
175 X_DESCRIPTION => l_seed_qual_rec.DESCRIPTION,
176 X_ORG_ID => l_seed_qual_rec.ORG_ID
177 );
178
179 l_seed_qual_out_rec.seeded_qual_id := l_seed_qual_rec.seeded_qual_id;
180
181 ELSE
182
183 l_seed_qual_out_rec.seeded_qual_id := l_seeded_qual_id_csr;
184
185 -- Debug message
186 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
187 FND_MESSAGE.Set_Name ('JTF', 'PVTQUAL API:name exists');
188 FND_MESSAGE.Set_Token ('ROW', 'JTF_SEEDED_QUAL_B');
189 FND_MSG_PUB.ADD;
190 END IF;
191
192 END IF;
193
194 CLOSE c_chk_qual_name;
195
196
197 l_seed_qual_out_rec.return_status := FND_API.G_RET_STS_SUCCESS;
198
199 -- save id and status
200 x_seed_qual_out_rec := l_seed_qual_out_rec;
201
202
203 END Create_Seed_Qual_Record;
204
205
206 -- Update seeded qualifier record in database
207 PROCEDURE Update_Seed_Qual_Record
208 ( p_seed_qual_rec IN JTF_QUALIFIER_PUB.Seed_Qual_Rec_Type,
209 x_seed_qual_out_rec OUT NOCOPY JTF_QUALIFIER_PUB.Seed_Qual_Out_Rec_Type )
210 IS
211
212 l_rowid ROWID;
213 l_seed_qual_rec JTF_QUALIFIER_PUB.Seed_Qual_Rec_Type := JTF_QUALIFIER_PUB.G_MISS_SEED_QUAL_REC;
214 l_seed_qual_out_rec JTF_QUALIFIER_PUB.Seed_Qual_Out_Rec_Type := JTF_QUALIFIER_PUB.G_MISS_SEED_QUAL_OUT_REC;
215
216 BEGIN
217 -- Initialise API return status to success
218 l_seed_qual_out_rec.return_status := FND_API.G_RET_STS_SUCCESS;
219
220 -- initialise local seeded qualifier record
221 l_seed_qual_rec := p_seed_qual_rec;
222
223 -- Call UPDATE table handler
224 JTF_SEEDED_QUAL_PKG.UPDATE_ROW(
225 X_Rowid => l_rowid,
226 X_SEEDED_QUAL_ID => l_seed_qual_rec.seeded_qual_id,
227 X_LAST_UPDATE_DATE => l_seed_qual_rec.LAST_UPDATE_DATE,
228 X_LAST_UPDATED_BY => l_seed_qual_rec.LAST_UPDATED_BY,
229 X_CREATION_DATE => l_seed_qual_rec.CREATION_DATE,
230 X_CREATED_BY => l_seed_qual_rec.CREATED_BY,
231 X_LAST_UPDATE_LOGIN => l_seed_qual_rec.LAST_UPDATE_LOGIN,
232 X_NAME => l_seed_qual_rec.NAME,
233 X_DESCRIPTION => l_seed_qual_rec.DESCRIPTION,
234 X_ORG_ID => l_seed_qual_rec.ORG_ID
235 );
236
237
238 l_seed_qual_out_rec.seeded_qual_id := l_seed_qual_rec.seeded_qual_id;
239 l_seed_qual_out_rec.return_status := FND_API.G_RET_STS_SUCCESS;
240
241 -- save id and status
242 x_seed_qual_out_rec := l_seed_qual_out_rec;
243
244
245
246 END Update_Seed_Qual_Record;
247
248
249 -- Delete seeded qualifier record from database
250 PROCEDURE Delete_Seed_Qual_Record
251 ( p_seeded_qual_id IN NUMBER,
252 x_return_status OUT NOCOPY VARCHAR2 )
253 IS
254
255 l_rowid ROWID;
256 l_return_status VARCHAR2(1);
257
258 BEGIN
259
260 -- Initialise API return status to success
261 l_return_status := FND_API.G_RET_STS_SUCCESS;
262
263 -- Call DELETE table handler
264 JTF_SEEDED_QUAL_PKG.DELETE_ROW ( X_SEEDED_QUAL_ID => p_seeded_qual_id);
265
266 -- save status
267 x_return_status := l_return_status;
268
269
270 END Delete_Seed_Qual_Record;
271
272
273 -- *************************************************************************************
274 -- this function returns the datatype of a column
275 FUNCTION get_column_datatype(p_column_name VARCHAR2, p_table_name VARCHAR2)
276 RETURN VARCHAR2
277 IS
278 -- cursor to check that column exists in an Application schema
279 CURSOR c_column_datatype (l_column_name VARCHAR2, l_table_name VARCHAR2, l_apps_schema VARCHAR2) IS
280 SELECT data_type
281 FROM ALL_TAB_COLUMNS
282 WHERE column_name = UPPER(l_column_name)
283 AND table_name = UPPER(l_table_name)
284 AND owner =
285 (select table_owner
286 from all_synonyms
287 where synonym_name = UPPER(l_table_name)
288 and owner = l_apps_schema);
289
290 -- column datatype
291 l_column_datatype_csr ALL_TAB_COLUMNS.DATA_TYPE%TYPE;
292
293 l_apps_schema_name VARCHAR2(30);
294 BEGIN
295
296 /* ACHANDA : Bug # 3511203 : get apps schema and use it to get the data type from all_tab_columns */
297 SELECT oracle_username
298 INTO l_apps_schema_name
299 FROM fnd_oracle_userid
300 WHERE read_only_flag = 'U';
301
302 -- get the column datatype
303 OPEN c_column_datatype (p_column_name, p_table_name, l_apps_schema_name);
304 FETCH c_column_datatype INTO l_column_datatype_csr;
305
306 IF c_column_datatype%NOTFOUND THEN
307
308 -- Debug message
309 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
310 FND_MESSAGE.Set_Name ('JTF', 'PVTQUAL API:Col dtype noexist');
311 FND_MESSAGE.Set_Token ( 'COLUMN', 'QUAL_TABLE1');
312 FND_MSG_PUB.ADD;
313 END IF;
314
315 l_column_datatype_csr := NULL;
316
317 END IF;
318
319 CLOSE c_column_datatype;
320
321 RETURN l_column_datatype_csr;
322
323 END get_column_datatype;
324
325 -- *************************************************************************************
326 -- Checks if there are territories using this qualifier
327 -- Determines if qualifier disable should be allowed
328 PROCEDURE check_qualifier_usage
329 (l_qual_usg_id IN NUMBER,
330 l_qualifier_used OUT NOCOPY VARCHAR2 )
331 IS
332 l_count NUMBER;
333
334 BEGIN
335
336 select 1
337 into l_count
338 from jtf_terr jta,
339 jtf_terr_qual jtq
340 where jta.terr_id = jtq.terr_id
341 and jtq.qual_usg_id = l_qual_usg_id
342 and rownum < 2;
343
344 If l_count > 0 then
345 l_qualifier_used := 'TRUE';
346 end if;
347
348 EXCEPTION
349 WHEN NO_DATA_FOUND THEN
350 l_qualifier_used := 'FALSE';
351 WHEN OTHERS THEN
352 l_qualifier_used := 'NEITHER';
353
354 END check_qualifier_usage;
355
356 ----------------------------------------------------------------------
357
358 -- Converts missing items' values to default values
359 PROCEDURE convert_miss_qual_usgs_rec
360 ( p_qual_usgs_rec IN JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type,
361 x_qual_usgs_rec OUT NOCOPY JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type)
362 IS
363
364 l_qual_usgs_rec JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type := p_qual_usgs_rec;
365
366 BEGIN
367
368 IF (l_qual_usgs_rec.enabled_flag = FND_API.G_MISS_CHAR) THEN
369 l_qual_usgs_rec.enabled_flag := 'N';
370 END IF;
371
372
373
374
375 IF (l_qual_usgs_rec.qual_col1_datatype = FND_API.G_MISS_CHAR) THEN
376 l_qual_usgs_rec.qual_col1_datatype := get_column_datatype( l_qual_usgs_rec.qual_col1,
377 l_qual_usgs_rec.qual_col1_table);
378 END IF;
379
380 IF (l_qual_usgs_rec.prim_int_cde_col_datatype = FND_API.G_MISS_CHAR) THEN
381
382 IF (l_qual_usgs_rec.prim_int_cde_col <> FND_API.G_MISS_CHAR
383 AND l_qual_usgs_rec.int_cde_col_table <> FND_API.G_MISS_CHAR) THEN
384
385 l_qual_usgs_rec.prim_int_cde_col_datatype := get_column_datatype( l_qual_usgs_rec.prim_int_cde_col,
386 l_qual_usgs_rec.int_cde_col_table);
387 END IF;
388
389 END IF;
390
391 IF (l_qual_usgs_rec.sec_int_cde_col_datatype = FND_API.G_MISS_CHAR) THEN
392
393 IF (l_qual_usgs_rec.sec_int_cde_col <> FND_API.G_MISS_CHAR
394 AND l_qual_usgs_rec.int_cde_col_table <> FND_API.G_MISS_CHAR) THEN
395
396 l_qual_usgs_rec.sec_int_cde_col_datatype := get_column_datatype( l_qual_usgs_rec.sec_int_cde_col,
397 l_qual_usgs_rec.int_cde_col_table);
398 END IF;
399
400 END IF;
401
402
403
404
405 x_qual_usgs_rec := l_qual_usgs_rec;
406
407 END convert_miss_qual_usgs_rec;
408
409
410 -- Returns TRUE if mandatory information is missing from record,
411 -- otherwise returns FALSE if information is complete
412 FUNCTION is_qual_usgs_rec_missing
413 ( p_qual_usgs_rec IN JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type)
414 RETURN VARCHAR2
415 IS
416 BEGIN
417
418 IF ( p_qual_usgs_rec.qual_type_usg_id IS NULL OR
419 p_qual_usgs_rec.qual_type_usg_id = FND_API.G_MISS_NUM OR
420 p_qual_usgs_rec.qual_col1 IS NULL OR
421 p_qual_usgs_rec.qual_col1 = FND_API.G_MISS_CHAR OR
422 p_qual_usgs_rec.qual_col1_alias IS NULL OR
423 p_qual_usgs_rec.qual_col1_alias = FND_API.G_MISS_CHAR OR
424 p_qual_usgs_rec.qual_col1_datatype IS NULL OR
425 p_qual_usgs_rec.qual_col1_datatype = FND_API.G_MISS_CHAR OR
426 p_qual_usgs_rec.qual_col1_table IS NULL OR
427 p_qual_usgs_rec.qual_col1_table = FND_API.G_MISS_CHAR OR
428 p_qual_usgs_rec.qual_col1_table_alias IS NULL OR
429 p_qual_usgs_rec.qual_col1_table_alias = FND_API.G_MISS_CHAR
430 )
431 THEN
432 -- Debug message
433 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
434 FND_MESSAGE.Set_Name ('JTF', 'PVTQUAL API:Miss mandtry itms');
435 FND_MESSAGE.Set_Token ( 'COLUMN',
436 'QUAL_TYPE_USG_ID, ' ||
437 'QUAL_COL1, QUAL_COL1_ALIAS, QUAL_COL1_DATATYPE, ' ||
438 'QUAL_COL1_TABLE, QUAL_COL1_TABLE_ALIAS');
439 FND_MSG_PUB.ADD;
440 END IF;
441
442 RETURN FND_API.G_TRUE;
443 ELSE
444 RETURN FND_API.G_FALSE;
445 END IF;
446
447 END is_qual_usgs_rec_missing;
448
449
450 /* function returns TRUE if table and column that define the Qualifier
451 ** are valid, otherwise returns FALSE
452 */
453 FUNCTION table_col_is_valid (p_table_name VARCHAR2, p_col_name VARCHAR2)
454 RETURN VARCHAR2
455 IS
456
457 -- cursor to check that table exists in an Application schema
458 CURSOR c_chk_table_exists (l_table_name VARCHAR2, l_apps_schema VARCHAR2) IS
459 SELECT 'X'
460 FROM ALL_TAB_COLUMNS
461 WHERE table_name = UPPER(l_table_name)
462 AND owner =
463 (select table_owner
464 from all_synonyms
465 where synonym_name = UPPER(l_table_name)
466 and owner = l_apps_schema);
467
468 -- cursor to check that column exists in an Application schema
469 CURSOR c_chk_col_exists (l_table_name VARCHAR2, l_col_name VARCHAR2, l_apps_schema VARCHAR2) IS
470 SELECT 'X'
471 FROM ALL_TAB_COLUMNS
472 WHERE column_name = UPPER(l_col_name)
473 AND table_name = UPPER(l_table_name)
474 AND owner =
475 (select table_owner
476 from all_synonyms
477 where synonym_name = UPPER(l_table_name)
478 and owner = l_apps_schema);
479
480 l_return_csr VARCHAR2(1);
481 l_return_variable VARCHAR2(1) := FND_API.G_TRUE;
482 l_apps_schema_name VARCHAR2(30);
483
484 BEGIN
485
486 /* ACHANDA : Bug # 3511203 : get apps schema and use it to get the data type from all_tab_columns */
487 SELECT oracle_username
488 INTO l_apps_schema_name
489 FROM fnd_oracle_userid
490 WHERE read_only_flag = 'U';
491
492 /* check if table exists */
493 OPEN c_chk_table_exists (p_table_name, l_apps_schema_name);
494 FETCH c_chk_table_exists INTO l_return_csr;
495
496 IF c_chk_table_exists%NOTFOUND THEN
497
498 l_return_variable := FND_API.G_FALSE;
499
500 /* Debug message */
501 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
502 FND_MESSAGE.Set_Name ('JTF', 'PVTQUAL API:Table dnot exist');
503 FND_MESSAGE.Set_Token ('TABLE', p_table_name);
504 FND_MSG_PUB.ADD;
505 END IF;
506
507 END IF;
508
509 CLOSE c_chk_table_exists;
510
511
512
513
514
515 /* check if column exists */
516 OPEN c_chk_col_exists (p_table_name, p_col_name, l_apps_schema_name);
517 FETCH c_chk_col_exists INTO l_return_csr;
518
519 IF c_chk_col_exists%NOTFOUND THEN
520
521 l_return_variable := FND_API.G_FALSE;
522
523 /* Debug message */
524 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
525 FND_MESSAGE.Set_Name ('JTF', 'PVTQUAL API:Column dnot exist');
526 FND_MESSAGE.Set_Token ('COLUMN', p_col_name);
527 FND_MSG_PUB.ADD;
528 END IF;
529
530 END IF;
531
532 CLOSE c_chk_col_exists;
533
534 RETURN l_return_variable;
535
536 END table_col_is_valid;
537
538
539 /* Returns TRUE if the optional information for the record is valid,
540 ** returns otherwise FALSE
541 ** As none of these items are required, they are set to their default
542 ** value, so that the record can still be inserted into the database
543 ** Checks items that use lookup values
544 */
545 FUNCTION qual_usgs_info_is_valid
546 ( p_qual_usgs_rec IN OUT NOCOPY JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type)
547 RETURN VARCHAR2
548 IS
549
550 /* return varaible */
551 l_return_variable VARCHAR2(1) := FND_API.G_TRUE;
552
553 /* local scratch record */
554 l_qual_usgs_rec JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type;
555
556 BEGIN
557
558
559 /* initialise local record */
560 l_qual_usgs_rec := p_qual_usgs_rec;
561
562 IF ( JTF_CTM_UTILITY_PVT.lookup_code_is_valid ( l_qual_usgs_rec.seeded_flag
563 , 'FLAG'
564 , 'FND_LOOKUPS') = FND_API.G_FALSE) THEN
565 l_return_variable := FND_API.G_FALSE;
566 l_qual_usgs_rec.seeded_flag := 'N';
567 END IF;
568
569 IF ( JTF_CTM_UTILITY_PVT.lookup_code_is_valid ( l_qual_usgs_rec.display_type
570 , 'DISPLAY_TYPE'
571 , 'FND_LOOKUPS') = FND_API.G_FALSE) THEN
572 l_return_variable := FND_API.G_FALSE;
573 l_qual_usgs_rec.display_type := 'STANDARD';
574 END IF;
575
576 RETURN l_return_variable;
577
578 END qual_usgs_info_is_valid;
579
580
581 /* Validate the record information
582 ** All mandatory items are present
583 ** Convert missing values to defaults
584 */
585 PROCEDURE validate_qual_usgs_rec
586 ( p_qual_usgs_rec IN JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type,
587 p_validation_mode IN VARCHAR2,
588 p_validation_level IN NUMBER,
589 x_return_status OUT NOCOPY VARCHAR2)
590 IS
591
592 -- cursor to check that Unique Key constraint not violated
593 CURSOR c_chk_uk_violation (p_seeded_qual_id NUMBER, p_qual_type_usg_id NUMBER) IS
594 SELECT 'X'
595 FROM JTF_QUAL_USGS
596 WHERE seeded_qual_id = p_seeded_qual_id
597 AND qual_type_usg_id = p_qual_type_usg_id;
598
599 l_return_csr VARCHAR2(1);
600
601 -- Initialise return status
602 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
603
604 -- Initialise local Qualifer Usages record
605 l_qual_usgs_rec JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type := p_qual_usgs_rec;
606
607 BEGIN
608
609
610 -- IF ( p_validation_level >= JTF_CTM_UTILITY_PVT.G_VALID_LEVEL_ITEM ) THEN
611
612 /* If record is being updated, check that primary key is not null */
613 IF ( (p_validation_mode = JTF_CTM_UTILITY_PVT.G_UPDATE) AND
614 ( l_qual_usgs_rec.qual_usg_id IS NULL OR
615 l_qual_usgs_rec.qual_usg_id = FND_API.G_MISS_NUM) ) THEN
616
617 l_return_status := FND_API.G_RET_STS_ERROR;
618
619 END IF;
620
621
622 -- IF ( p_validation_level >= JTF_CTM_UTILITY_PVT.G_VALID_LEVEL_RECORD ) THEN
623
624 /* Check that all mandatory items exist in record */
625 IF (is_qual_usgs_rec_missing (l_qual_usgs_rec) = FND_API.G_TRUE) THEN
626 l_return_status := FND_API.G_RET_STS_ERROR;
627 END IF;
628
629
630
631
632 -- IF ( p_validation_level >= JTF_CTM_UTILITY_PVT.G_VALID_LEVEL_INTER_ENTITY ) THEN
633
634 /* check FK reference to JTF_SEEDED_QUAL */
635 IF ( l_qual_usgs_rec.seeded_qual_id IS NOT NULL AND
636 l_qual_usgs_rec.seeded_qual_id <> FND_API.G_MISS_NUM ) THEN
637
638 IF ( JTF_CTM_UTILITY_PVT.fk_id_is_valid (
639 l_qual_usgs_rec.seeded_qual_id,
640 'SEEDED_QUAL_ID',
641 'JTF_SEEDED_QUAL') = FND_API.G_FALSE)
642 THEN
643 l_return_status := FND_API.G_RET_STS_ERROR;
644 END IF;
645 END IF;
646
647
648
649
650 -- IF ( p_validation_level >= JTF_CTM_UTILITY_PVT.G_VALID_LEVEL_INTER_ENTITY ) THEN
651
652 /* check FK reference to JTF_QUAL_TYPE_USGS */
653 IF ( l_qual_usgs_rec.qual_type_usg_id IS NOT NULL AND
654 l_qual_usgs_rec.qual_type_usg_id <> FND_API.G_MISS_NUM ) THEN
655
656 IF ( JTF_CTM_UTILITY_PVT.fk_id_is_valid (
657 l_qual_usgs_rec.qual_type_usg_id,
658 'QUAL_TYPE_USG_ID',
659 'JTF_QUAL_TYPE_USGS') = FND_API.G_FALSE)
660 THEN
661 l_return_status := FND_API.G_RET_STS_ERROR;
662 END IF;
663 END IF;
664
665
666
667 /* If record is being updated, check that primary key is not null */
668 IF (p_validation_mode = JTF_CTM_UTILITY_PVT.G_CREATE) THEN
669
670 /* check that Unique Key constraint not violated */
671 IF ( l_qual_usgs_rec.seeded_qual_id IS NOT NULL AND
672 l_qual_usgs_rec.seeded_qual_id <> FND_API.G_MISS_NUM AND
673 l_qual_usgs_rec.qual_type_usg_id IS NOT NULL AND
674 l_qual_usgs_rec.qual_type_usg_id <> FND_API.G_MISS_NUM )THEN
675
676 /* check if rec already exists */
677 OPEN c_chk_uk_violation ( l_qual_usgs_rec.seeded_qual_id
678 , l_qual_usgs_rec.qual_type_usg_id);
679 FETCH c_chk_uk_violation INTO l_return_csr;
680
681 IF c_chk_uk_violation%FOUND THEN
682
683 l_return_status := FND_API.G_RET_STS_ERROR;
684
685 /* Debug message */
686 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
687 FND_MESSAGE.Set_Name ('JTF', 'PVTQUAL API:UK Violation');
688 FND_MESSAGE.Set_Token ('TABLE', 'JTF_QUAL_USGS');
689 FND_MSG_PUB.ADD;
690 END IF;
691
692 END IF; /* c_chk_uk_violation%FOUND */
693
694 CLOSE c_chk_uk_violation;
695
696 END IF;
697 END IF;
698
699
700
701
702 -- IF ( p_validation_level >= JTF_CTM_UTILITY_PVT.G_VALID_LEVEL_RECORD ) THEN
703
704 /* check qualifier column and table exists */
705 IF ( l_qual_usgs_rec.qual_col1_table IS NOT NULL AND
706 l_qual_usgs_rec.qual_col1_table <> FND_API.G_MISS_CHAR AND
707 l_qual_usgs_rec.qual_col1 IS NOT NULL AND
708 l_qual_usgs_rec.qual_col1 <> FND_API.G_MISS_CHAR ) THEN
709
710 /* if qualifier has been defined as a special function, do
711 ** not check if table and column definitions exist
712 */
713 IF (l_qual_usgs_rec.qual_col1_datatype <> 'SPECIAL_FUNCTION') THEN
714
715 IF (table_col_is_valid ( l_qual_usgs_rec.qual_col1_table,
716 l_qual_usgs_rec.qual_col1) = FND_API.G_FALSE)
717 THEN
718 l_return_status := FND_API.G_RET_STS_ERROR;
719 END IF;
720
721 END IF;
722 END IF;
723
724
725
726
727 -- IF ( p_validation_level >= JTF_CTM_UTILITY_PVT.G_VALID_LEVEL_RECORD ) THEN
728
729 /* check qualifier column and table exists */
730 IF ( l_qual_usgs_rec.int_cde_col_table IS NOT NULL AND
731 l_qual_usgs_rec.int_cde_col_table <> FND_API.G_MISS_CHAR AND
732 l_qual_usgs_rec.prim_int_cde_col IS NOT NULL AND
733 l_qual_usgs_rec.prim_int_cde_col <> FND_API.G_MISS_CHAR ) THEN
734
735 IF (table_col_is_valid ( l_qual_usgs_rec.int_cde_col_table,
736 l_qual_usgs_rec.prim_int_cde_col) = FND_API.G_FALSE)
737 THEN
738
739 l_return_status := FND_API.G_RET_STS_ERROR;
740 END IF;
741
742 END IF;
743
744
745
746
747 -- IF ( p_validation_level >= JTF_CTM_UTILITY_PVT.G_VALID_LEVEL_RECORD ) THEN
748
749 /* check qualifier column and table exists */
750 IF ( l_qual_usgs_rec.int_cde_col_table IS NOT NULL AND
751 l_qual_usgs_rec.int_cde_col_table <> FND_API.G_MISS_CHAR AND
752 l_qual_usgs_rec.sec_int_cde_col IS NOT NULL AND
753 l_qual_usgs_rec.sec_int_cde_col <> FND_API.G_MISS_CHAR ) THEN
754
755 IF (table_col_is_valid ( l_qual_usgs_rec.int_cde_col_table,
756 l_qual_usgs_rec.sec_int_cde_col) = FND_API.G_FALSE)
757 THEN
758
759 l_return_status := FND_API.G_RET_STS_ERROR;
760 END IF;
761
762 END IF;
763
764
765
766
767 /* save return status */
768 x_return_status := l_return_status;
769
770 END validate_qual_usgs_rec;
771
772
773
774
775 -- Insert qualifier usage record into database
776 PROCEDURE Create_Qual_Usgs_Record
777 ( p_seed_qual_id IN NUMBER,
778 p_qual_usgs_rec IN JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type,
779 x_qual_usgs_out_rec OUT NOCOPY JTF_QUALIFIER_PUB.Qual_Usgs_All_Out_Rec_Type)
780 IS
781
782 l_return_csr VARCHAR2(1);
783
784 l_rowid ROWID;
785 l_qual_usgs_rec JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type := JTF_QUALIFIER_PUB.G_MISS_QUAL_USGS_ALL_REC;
786 l_qual_usgs_out_rec JTF_QUALIFIER_PUB.Qual_Usgs_All_Out_Rec_Type := JTF_QUALIFIER_PUB.G_MISS_QUAL_USGS_ALL_OUT_REC;
787
788 BEGIN
789
790 l_qual_usgs_rec := p_qual_usgs_rec;
791
792 -- Initialise API return status to success
793 l_qual_usgs_out_rec.return_status := FND_API.G_RET_STS_SUCCESS;
794
795 -- convert id to null, so that next value of sequence will
796 -- be selected
797 IF (l_qual_usgs_rec.qual_usg_id = FND_API.G_MISS_NUM) THEN
798
799
800 l_qual_usgs_rec.qual_usg_id := NULL;
801 END IF;
802 -- Call INSERT table handler
803 JTF_QUAL_USGS_PKG.INSERT_ROW(
804 X_Rowid => l_rowid,
805 X_QUAL_USG_ID => l_qual_usgs_rec.QUAL_USG_ID,
806 X_LAST_UPDATE_DATE => l_qual_usgs_rec.LAST_UPDATE_DATE,
807 X_LAST_UPDATED_BY => l_qual_usgs_rec.LAST_UPDATED_BY,
808 X_CREATION_DATE => l_qual_usgs_rec.CREATION_DATE,
809 X_CREATED_BY => l_qual_usgs_rec.CREATED_BY,
810 X_LAST_UPDATE_LOGIN => l_qual_usgs_rec.LAST_UPDATE_LOGIN,
811 X_APPLICATION_SHORT_NAME => l_qual_usgs_rec.APPLICATION_SHORT_NAME,
812 X_SEEDED_QUAL_ID => p_seed_qual_id,
813 X_QUAL_TYPE_USG_ID => l_qual_usgs_rec.QUAL_TYPE_USG_ID,
814 X_ENABLED_FLAG => l_qual_usgs_rec.ENABLED_FLAG,
815 X_QUAL_COL1 => l_qual_usgs_rec.QUAL_COL1,
816 X_QUAL_COL1_ALIAS => l_qual_usgs_rec.QUAL_COL1_ALIAS,
817 X_QUAL_COL1_DATATYPE => l_qual_usgs_rec.QUAL_COL1_DATATYPE,
818 X_QUAL_COL1_TABLE => l_qual_usgs_rec.QUAL_COL1_TABLE,
819 X_QUAL_COL1_TABLE_ALIAS => l_qual_usgs_rec.QUAL_COL1_TABLE_ALIAS,
820 X_PRIM_INT_CDE_COL => l_qual_usgs_rec.PRIM_INT_CDE_COL,
821 X_PRIM_INT_CDE_COL_DATATYPE => l_qual_usgs_rec.PRIM_INT_CDE_COL_DATATYPE,
822 X_PRIM_INT_CDE_COL_ALIAS => l_qual_usgs_rec.PRIM_INT_CDE_COL_ALIAS,
823 X_SEC_INT_CDE_COL => l_qual_usgs_rec.SEC_INT_CDE_COL,
824 X_SEC_INT_CDE_COL_ALIAS => l_qual_usgs_rec.SEC_INT_CDE_COL_ALIAS,
825 X_SEC_INT_CDE_COL_DATATYPE => l_qual_usgs_rec.SEC_INT_CDE_COL_DATATYPE,
826 X_INT_CDE_COL_TABLE => l_qual_usgs_rec.INT_CDE_COL_TABLE,
827 X_INT_CDE_COL_TABLE_ALIAS => l_qual_usgs_rec.INT_CDE_COL_TABLE_ALIAS,
828 X_SEEDED_FLAG => l_qual_usgs_rec.SEEDED_FLAG,
829 X_DISPLAY_TYPE => l_qual_usgs_rec.DISPLAY_TYPE,
830 X_LOV_SQL => l_qual_usgs_rec.LOV_SQL,
831 x_CONVERT_TO_ID_FLAG => l_qual_usgs_rec.CONVERT_TO_ID_FLAG,
832 x_COLUMN_COUNT => l_qual_usgs_rec.COLUMN_COUNT,
833 x_FORMATTING_FUNCTION_FLAG => l_qual_usgs_rec.FORMATTING_FUNCTION_FLAG,
834 x_FORMATTING_FUNCTION_NAME => l_qual_usgs_rec.FORMATTING_FUNCTION_NAME,
835 x_SPECIAL_FUNCTION_FLAG => l_qual_usgs_rec.SPECIAL_FUNCTION_FLAG,
836 x_SPECIAL_FUNCTION_NAME => l_qual_usgs_rec.SPECIAL_FUNCTION_NAME,
837 x_ENABLE_LOV_VALIDATION => l_qual_usgs_rec.ENABLE_LOV_VALIDATION,
838 x_DISPLAY_SQL1 => l_qual_usgs_rec.DISPLAY_SQL1,
839 x_LOV_SQL2 => l_qual_usgs_rec.LOV_SQL2,
840 x_DISPLAY_SQL2 => l_qual_usgs_rec.DISPLAY_SQL2,
841 x_LOV_SQL3 => l_qual_usgs_rec.LOV_SQL3,
842 x_DISPLAY_SQL3 => l_qual_usgs_rec.DISPLAY_SQL3,
843 X_ORG_ID => l_qual_usgs_rec.ORG_ID,
844 X_RULE1 => l_qual_usgs_rec.RULE1,
845 X_RULE2 => l_qual_usgs_rec.RULE2,
846 X_DISPLAY_SEQUENCE => l_qual_usgs_rec.DISPLAY_SEQUENCE,
847 X_DISPLAY_LENGTH => l_qual_usgs_rec.DISPLAY_LENGTH,
848 X_JSP_LOV_SQL => l_qual_usgs_rec.JSP_LOV_SQL,
849 x_use_in_lookup_flag => l_qual_usgs_rec.use_in_lookup_flag);
850
851
852
853 l_qual_usgs_out_rec.qual_usg_id := l_qual_usgs_rec.qual_usg_id;
854
855
856
857 l_qual_usgs_out_rec.return_status := FND_API.G_RET_STS_SUCCESS;
858
859 -- save id and status
860 x_qual_usgs_out_rec := l_qual_usgs_out_rec;
861
862 --exception
863 --when others then
864
865
866
867 END Create_Qual_Usgs_Record;
868
869
870 -- Update qualifier usage record in database
871 PROCEDURE Update_Qual_Usgs_Record
872 ( p_qual_usgs_rec IN JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type,
873 x_qual_usgs_out_rec OUT NOCOPY JTF_QUALIFIER_PUB.Qual_Usgs_All_Out_Rec_Type)
874 IS
875
876 l_rowid ROWID;
877 l_qual_usgs_rec JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type := JTF_QUALIFIER_PUB.G_MISS_QUAL_USGS_ALL_REC;
878 l_qual_usgs_out_rec JTF_QUALIFIER_PUB.Qual_Usgs_All_Out_Rec_Type := JTF_QUALIFIER_PUB.G_MISS_QUAL_USGS_ALL_OUT_REC;
879
880 BEGIN
881
882
883 -- initialize locak seeded qualifier record
884 l_qual_usgs_rec := p_qual_usgs_rec;
885
886 -- Initialise API return status to success
887 l_qual_usgs_out_rec.return_status := FND_API.G_RET_STS_SUCCESS;
888
889
890 -- by eihsu, using similar method of problem resolution as in procedure Update_Seed_Qual_Record
891 -- (vinoo and jdochert)
892
893 -- Call UPDATE table handler
894
895 JTF_QUAL_USGS_PKG.UPDATE_ROW(
896 X_Rowid => l_rowid,
897 X_QUAL_USG_ID => l_qual_usgs_rec.QUAL_USG_ID,
898 X_LAST_UPDATE_DATE => l_qual_usgs_rec.LAST_UPDATE_DATE,
899 X_LAST_UPDATED_BY => l_qual_usgs_rec.LAST_UPDATED_BY,
900 X_CREATION_DATE => l_qual_usgs_rec.CREATION_DATE,
901 X_CREATED_BY => l_qual_usgs_rec.CREATED_BY,
902 X_LAST_UPDATE_LOGIN => l_qual_usgs_rec.LAST_UPDATE_LOGIN,
903 X_APPLICATION_SHORT_NAME => l_qual_usgs_rec.APPLICATION_SHORT_NAME,
904 X_SEEDED_QUAL_ID => l_qual_usgs_rec.SEEDED_QUAL_ID,
905 X_QUAL_TYPE_USG_ID => l_qual_usgs_rec.QUAL_TYPE_USG_ID,
906 X_ENABLED_FLAG => l_qual_usgs_rec.ENABLED_FLAG,
907 X_QUAL_COL1 => l_qual_usgs_rec.QUAL_COL1,
908 X_QUAL_COL1_ALIAS => l_qual_usgs_rec.QUAL_COL1_ALIAS,
909 X_QUAL_COL1_DATATYPE => l_qual_usgs_rec.QUAL_COL1_DATATYPE,
910 X_QUAL_COL1_TABLE => l_qual_usgs_rec.QUAL_COL1_TABLE,
911 X_QUAL_COL1_TABLE_ALIAS => l_qual_usgs_rec.QUAL_COL1_TABLE_ALIAS,
912 X_PRIM_INT_CDE_COL => l_qual_usgs_rec.PRIM_INT_CDE_COL,
913 X_PRIM_INT_CDE_COL_DATATYPE => l_qual_usgs_rec.PRIM_INT_CDE_COL_DATATYPE,
914 X_PRIM_INT_CDE_COL_ALIAS => l_qual_usgs_rec.PRIM_INT_CDE_COL_ALIAS,
915 X_SEC_INT_CDE_COL => l_qual_usgs_rec.SEC_INT_CDE_COL,
916 X_SEC_INT_CDE_COL_ALIAS => l_qual_usgs_rec.SEC_INT_CDE_COL_ALIAS,
917 X_SEC_INT_CDE_COL_DATATYPE => l_qual_usgs_rec.SEC_INT_CDE_COL_DATATYPE,
918 X_INT_CDE_COL_TABLE => l_qual_usgs_rec.INT_CDE_COL_TABLE,
919 X_INT_CDE_COL_TABLE_ALIAS => l_qual_usgs_rec.INT_CDE_COL_TABLE_ALIAS,
920 X_SEEDED_FLAG => l_qual_usgs_rec.SEEDED_FLAG,
921 X_DISPLAY_TYPE => l_qual_usgs_rec.DISPLAY_TYPE,
922 X_LOV_SQL => l_qual_usgs_rec.LOV_SQL,
923 x_CONVERT_TO_ID_FLAG => l_qual_usgs_rec.CONVERT_TO_ID_FLAG,
924 x_COLUMN_COUNT => l_qual_usgs_rec.COLUMN_COUNT,
925 x_FORMATTING_FUNCTION_FLAG => l_qual_usgs_rec.FORMATTING_FUNCTION_FLAG,
926 x_FORMATTING_FUNCTION_NAME => l_qual_usgs_rec.FORMATTING_FUNCTION_NAME,
927 x_SPECIAL_FUNCTION_FLAG => l_qual_usgs_rec.SPECIAL_FUNCTION_FLAG,
928 x_SPECIAL_FUNCTION_NAME => l_qual_usgs_rec.SPECIAL_FUNCTION_NAME,
929 x_ENABLE_LOV_VALIDATION => l_qual_usgs_rec.ENABLE_LOV_VALIDATION,
930 x_DISPLAY_SQL1 => l_qual_usgs_rec.DISPLAY_SQL1,
931 x_LOV_SQL2 => l_qual_usgs_rec.LOV_SQL2,
932 x_DISPLAY_SQL2 => l_qual_usgs_rec.DISPLAY_SQL2,
933 x_LOV_SQL3 => l_qual_usgs_rec.LOV_SQL3,
934 x_DISPLAY_SQL3 => l_qual_usgs_rec.DISPLAY_SQL3,
935 X_ORG_ID => l_qual_usgs_rec.ORG_ID,
936 X_RULE1 => l_qual_usgs_rec.RULE1,
937 X_RULE2 => l_qual_usgs_rec.RULE2,
938 X_DISPLAY_SEQUENCE => l_qual_usgs_rec.DISPLAY_SEQUENCE,
939 X_DISPLAY_LENGTH => l_qual_usgs_rec.DISPLAY_LENGTH,
940 X_JSP_LOV_SQL => l_qual_usgs_rec.JSP_LOV_SQL,
941 X_use_in_lookup_flag => l_qual_usgs_rec.use_in_lookup_flag
942 );
943
944 l_qual_usgs_out_rec.qual_usg_id := l_qual_usgs_rec.qual_usg_id;
945 l_qual_usgs_out_rec.return_status := FND_API.G_RET_STS_SUCCESS;
946
947 -- save id and status
948 x_qual_usgs_out_rec := l_qual_usgs_out_rec;
949
950
951
952 END Update_Qual_Usgs_Record;
953
954
955 /* Check if records should be deleted
956 ** seeded flag <> Y
957 ** cannot delete seeded_qual if more that one qual_usg exists for that qualifier
958 ** cannot delete qualifier if it is being used in a territory or territory type definition
959 ** check if record should be deleted
960 */
961 PROCEDURE is_qualifier_delete_allowed ( p_seeded_qual_id IN NUMBER
962 , p_qual_usg_id IN NUMBER
963 , x_return_status OUT NOCOPY VARCHAR2 )
964 IS
965
966 /* seeded qualifier record does not have more than one child */
967 CURSOR c_chk_sq_child ( p_seeded_qual_id NUMBER, p_qual_usg_id NUMBER ) IS
968 SELECT qual_usg_id
969 FROM JTF_QUAL_USGS
970 WHERE seeded_qual_id = p_seeded_qual_id
971 AND qual_usg_id <> p_qual_usg_id;
972
973 /* cursor to check if qualifier usage record is seeded */
974 CURSOR c_chk_seeded_flag ( p_qual_usg_id NUMBER ) IS
975 SELECT qual_usg_id
976 FROM JTF_QUAL_USGS
977 WHERE seeded_flag = 'Y'
978 AND qual_usg_id = p_qual_usg_id;
979
980 /* cursor to check if qualifier usage is used in a territory definition */
981 CURSOR c_chk_terr ( p_qual_usg_id NUMBER ) IS
982 SELECT terr_qual_id
983 FROM JTF_TERR_QUAL
984 WHERE qual_usg_id = p_qual_usg_id;
985
986 /* cursor to check if qualifier usage is used in a territory type definition */
987 CURSOR c_chk_terr_type ( p_qual_usg_id NUMBER ) IS
988 SELECT terr_type_qual_id
989 FROM JTF_TERR_TYPE_QUAL
990 WHERE qual_usg_id = p_qual_usg_id;
991
992 /* cursor return variable */
993 dummy_csr NUMBER;
994
995 /* local return variable */
996 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
997
998 /* local scratch variables */
999 l_seeded_qual_id NUMBER := p_seeded_qual_id;
1000 l_qual_usg_id NUMBER := p_qual_usg_id;
1001
1002 BEGIN
1003
1004 /* check for existence of other child records */
1005 OPEN c_chk_sq_child ( l_seeded_qual_id, l_qual_usg_id );
1006 FETCH c_chk_sq_child INTO dummy_csr;
1007 IF c_chk_sq_child%FOUND THEN
1008
1009 /* Debug Message */
1010 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1011 THEN
1012 FND_MESSAGE.Set_Name('JTF', 'DELETE QUAL PVT: CHECK_DEL1');
1013 FND_MSG_PUB.Add;
1014 END IF;
1015
1016 l_return_status := FND_API.G_RET_STS_ERROR;
1017 END IF;
1018
1019
1020
1021
1022 /* check if qualifier is seeded */
1023 OPEN c_chk_seeded_flag ( l_qual_usg_id );
1024 FETCH c_chk_seeded_flag INTO dummy_csr;
1025 IF c_chk_seeded_flag%FOUND THEN
1026
1027 /* Debug Message */
1028 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1029 THEN
1030 FND_MESSAGE.Set_Name('JTF', 'DELETE QUAL PVT: CHECK_DEL2');
1031 FND_MSG_PUB.Add;
1032 END IF;
1033
1034 l_return_status := FND_API.G_RET_STS_ERROR;
1035 END IF;
1036
1037 OPEN c_chk_terr ( l_qual_usg_id );
1038
1039
1040 FETCH c_chk_terr INTO dummy_csr;
1041
1042
1043 IF c_chk_terr%FOUND THEN
1044 /* Debug Message */
1045 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1046 THEN
1047 FND_MESSAGE.Set_Name('JTF', 'DELETE QUAL PVT: CHECK_DEL3');
1048 FND_MSG_PUB.Add;
1049 END IF;
1050
1051 l_return_status := FND_API.G_RET_STS_ERROR;
1052 END IF;
1053
1054
1055
1056
1057
1058 /* check if qualifier is being used by a territory type */
1059 OPEN c_chk_terr_type ( l_qual_usg_id );
1060 FETCH c_chk_terr_type INTO dummy_csr;
1061 IF c_chk_terr_type%FOUND THEN
1062
1063 /* Debug Message */
1064 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1065 THEN
1066 FND_MESSAGE.Set_Name('JTF', 'DELETE QUAL PVT: CHECK_DEL4');
1067 FND_MSG_PUB.Add;
1068 END IF;
1069
1070 l_return_status := FND_API.G_RET_STS_ERROR;
1071 END IF;
1072
1073
1074
1075 /* save status */
1076 x_return_status := l_return_status;
1077
1078 END;
1079
1080
1081
1082 -- Delete qualifier usage record from database
1083 PROCEDURE Delete_Qual_Usgs_Record
1084 ( p_qual_usg_id IN NUMBER,
1085 x_return_status OUT NOCOPY VARCHAR2 )
1086 IS
1087
1088 l_rowid ROWID;
1089 l_return_status VARCHAR2(1);
1090
1091 BEGIN
1092
1093
1094 -- Initialise API return status to success
1095 l_return_status := FND_API.G_RET_STS_SUCCESS;
1096
1097 -- Call UPDATE table handler
1098 JTF_QUAL_USGS_PKG.DELETE_ROW ( X_QUAL_USG_ID => p_qual_usg_id);
1099
1100 -- save status
1101 x_return_status := l_return_status;
1102
1103 END Delete_Qual_Usgs_Record;
1104
1105
1106 -- ******************************************************
1107 -- PUBLIC ROUTINES
1108 -- ******************************************************
1109
1110 -- ***************************************************
1111 -- start of comments
1112 -- ***************************************************
1113 --
1114 -- API name : Create_Qualifier
1115 -- Type : PRIVATE
1116 -- Function : To create qualifiers
1117 --
1118 -- Pre-reqs :
1119 -- Parameters:
1120 -- IN :
1121 -- Required
1122 -- Parameter Name Data Type Default
1123 -- p_api_version NUMBER
1124 -- p_Seed_Qual_Rec JTF_QUALIFIER_PUB.Seed_Qual_Rec_Type JTF_QUALIFIER_PUB.G_MISS_SEED_QUAL_REC
1125 -- p_Qual_Usgs_Rec JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type JTF_QUALIFIER_PUB.G_MISS_QUAL_USGS_ALL_REC
1126 --
1127 -- Optional
1128 -- Parameter Name Data Type Default
1129 -- P_Init_Msg_List VARCHAR2 FND_API.G_FALSE
1130 -- P_Commit VARCHAR2 FND_API.G_FALSE
1131 -- p_validation_level VARCHAR2 FND_API.G_VALID_LEVEL_FULL
1132 --
1133 -- OUT :
1134 -- Parameter Name Data Type Default
1135 -- x_Return_Status VARCHAR2(1)
1136 -- x_Msg_Count NUMBER
1137 -- x_Msg_Data VARCHAR2(2000)
1138 -- x_Seeded_Qual_Id NUMBER
1139 -- x_Qual_Usgs_Id NUMBER
1140 --
1141 --
1142 -- Notes:
1143 --
1144 --
1145 -- End of Comments
1146 --
1147 PROCEDURE Create_Qualifier
1148 (p_api_version IN NUMBER,
1149 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1150 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1151 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1152 x_Return_Status OUT NOCOPY VARCHAR2,
1153 x_Msg_Count OUT NOCOPY NUMBER,
1154 x_Msg_Data OUT NOCOPY VARCHAR2,
1155 -- commented eihsu 11/04
1156 p_Seed_Qual_Rec IN JTF_QUALIFIER_PUB.Seed_Qual_Rec_Type ,--:= JTF_QUALIFIER_PUB.G_MISS_SEED_QUAL_REC,
1157 p_Qual_Usgs_Rec IN JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type ,--:= JTF_QUALIFIER_PUB.G_MISS_QUAL_USGS_ALL_REC,
1158 x_Seed_Qual_Rec OUT NOCOPY JTF_QUALIFIER_PUB.Seed_Qual_Out_Rec_Type,
1159 x_Qual_Usgs_Rec OUT NOCOPY JTF_QUALIFIER_PUB.Qual_Usgs_All_Out_Rec_Type
1160 )
1161 IS
1162 l_api_name CONSTANT VARCHAR2(30) := 'Create_Qualifier';
1163 l_api_version CONSTANT NUMBER := 1.0;
1164 l_return_status VARCHAR2(1);
1165
1166 l_seed_qual_rec JTF_QUALIFIER_PUB.Seed_Qual_Rec_Type;
1167 l_seed_qual_out_rec JTF_QUALIFIER_PUB.Seed_Qual_Out_Rec_Type;
1168
1169 l_qual_usgs_rec JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type;
1170 l_qual_usgs_out_rec JTF_QUALIFIER_PUB.Qual_Usgs_All_Out_Rec_Type;
1171
1172 /* variable for qualifier disable eligibility test */
1173 l_qualifier_used VARCHAR2(30);
1174
1175 BEGIN
1176
1177 -- Standard Start of API savepoint
1178 SAVEPOINT CREATE_QUALIFIER_PVT;
1179
1180 -- Standard call to check for call compatability
1181 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1182 p_api_version,
1183 l_api_name,
1184 G_PKG_NAME)
1185 THEN
1186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1187 END IF;
1188
1189 -- Initialize message list if p_init_msg_list is set to TRUE.
1190 IF FND_API.to_Boolean( p_init_msg_list )
1191 THEN
1192 FND_MSG_PUB.initialize;
1193 END IF;
1194
1195 /* Debug Message */
1196 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1197 THEN
1198 FND_MESSAGE.Set_Name('JTF', 'PVT Create Qual: Start');
1199 FND_MSG_PUB.Add;
1200 END IF;
1201
1202
1203 -- ******************************************************************
1204 -- API BODY START
1205 -- ******************************************************************
1206
1207 -- Initialize API return status to success
1208 l_return_status := FND_API.G_RET_STS_SUCCESS;
1209
1210 -- Seeded Qualifier record doesn't have any default values, so
1211 -- just initialise local variable to value of variable passed in as
1212 -- a parameter to this procedure
1213 l_seed_qual_rec := p_seed_qual_rec;
1214
1215 -- Converts missing items' values to default values in Qualifier Usages record
1216 convert_miss_qual_usgs_rec (p_qual_usgs_rec, l_qual_usgs_rec);
1217
1218 -- Check if any territories are using this qualifier before disabling it.
1219 IF l_qual_usgs_rec.enabled_flag = 'N' THEN
1220 check_qualifier_usage (l_qual_usgs_rec.qual_usg_id, l_qualifier_used);
1221 END IF;
1222 IF l_qualifier_used = 'TRUE' THEN
1223 -- qualifier being used and cannot be diabled
1224 RAISE FND_API.G_EXC_ERROR;
1225
1226 END IF;
1227
1228 IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
1229
1230 -- Debug message
1231 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1232 FND_MESSAGE.Set_Name('JTF', 'PVTQUAL API:Validate Rec');
1233 FND_MSG_PUB.ADD;
1234 END IF;
1235
1236
1237 -- validate the seeded qualifier record
1238 validate_seed_qual_rec ( l_seed_qual_rec,
1239 JTF_CTM_UTILITY_PVT.G_CREATE,
1240 p_validation_level,
1241 l_return_status);
1242
1243
1244 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1245 RAISE FND_API.G_EXC_ERROR;
1246 END IF;
1247
1248 -- validate the qualifier usage record
1249 validate_qual_usgs_rec ( l_qual_usgs_rec,
1250 JTF_CTM_UTILITY_PVT.G_CREATE,
1251 p_validation_level,
1252 l_return_status);
1253
1254 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1255 RAISE FND_API.G_EXC_ERROR;
1256 END IF;
1257
1258 END IF; -- End of Record Validation
1259
1260
1261 -- Process Seeded Qualifier Record
1262 ----------------------------------
1263
1264 -- Debug message
1265 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1266 FND_MESSAGE.Set_Name('JTF', 'PVT QUALIFIER API:');
1267 FND_MSG_PUB.ADD;
1268 END IF;
1269
1270 -- Insert seeded qualifier record into database
1271 create_seed_qual_record ( l_seed_qual_rec,
1272 l_seed_qual_out_rec);
1273
1274 l_return_status := l_seed_qual_out_rec.return_status;
1275
1276
1277 -- ' l_seeded_qual_id = '|| TO_CHAR(l_seed_qual_out_rec.seeded_qual_id));
1278
1279 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1280 RAISE FND_API.G_EXC_ERROR;
1281 END IF;
1282
1283
1284 -- Process Qualifier Usages Record
1285 ----------------------------------
1286
1287 -- Debug message
1288 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1289 FND_MESSAGE.Set_Name('JTF', 'PVT API: Ins');
1290 FND_MSG_PUB.ADD;
1291 END IF;
1292
1293 create_qual_usgs_record ( l_seed_qual_out_rec.seeded_qual_id,
1294 l_qual_usgs_rec,
1295 l_qual_usgs_out_rec);
1296
1297 l_return_status := l_qual_usgs_out_rec.return_status;
1298
1299
1300
1301 -- ' l_qual_usg_id = '|| TO_CHAR(l_qual_usgs_out_rec.qual_usg_id));
1302
1303
1304 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1305 RAISE FND_API.G_EXC_ERROR;
1306 END IF;
1307
1308 -- save the returned seeded qualifer id, qualifier usage id, and status
1309 x_seed_qual_rec.seeded_qual_id := l_seed_qual_out_rec.seeded_qual_id;
1310 x_qual_usgs_rec.qual_usg_id := l_qual_usgs_out_rec.qual_usg_id;
1311 x_return_status := l_return_status;
1312
1313 -- *************************************************************************************
1314 -- API BODY END
1315 -- *************************************************************************************
1316
1317
1318 -- Success Message
1319 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) AND
1320 l_return_status = FND_API.G_RET_STS_SUCCESS
1321 THEN
1322 FND_MESSAGE.Set_Name('JTF', 'API_SUCCESS');
1323 FND_MESSAGE.Set_Token('ROW', 'JTF_QUALIFIER', TRUE);
1324 FND_MSG_PUB.Add;
1325 END IF;
1326
1327 -- Standard check of p_commit.
1328 IF FND_API.To_Boolean ( p_commit )
1329 THEN
1330 COMMIT WORK;
1331 END IF;
1332
1333 -- Debug Message
1334 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1335 THEN
1336 FND_MESSAGE.Set_Name('JTF', 'PVT Create Qual API: End');
1337 FND_MSG_PUB.Add;
1338 END IF;
1339
1340 -- Standard call to get message count and if count is 1, get message info.
1341 FND_MSG_PUB.Count_And_Get
1342 ( p_count => x_msg_count,
1343 p_data => x_msg_data
1344 );
1345
1346 EXCEPTION
1347
1348 WHEN FND_API.G_EXC_ERROR THEN
1349 ROLLBACK TO CREATE_QUALIFIER_PVT;
1350 x_return_status := FND_API.G_RET_STS_ERROR ;
1351
1352 FND_MSG_PUB.Count_And_Get
1353 ( p_count => x_msg_count,
1354 p_data => x_msg_data
1355 );
1356
1357
1358 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1359 ROLLBACK TO CREATE_QUALIFIER_PVT;
1360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1361
1362 FND_MSG_PUB.Count_And_Get
1363 ( p_count => x_msg_count,
1364 p_data => x_msg_data
1365 );
1366
1367
1368 WHEN OTHERS THEN
1369 ROLLBACK TO CREATE_QUALIFIER_PVT;
1370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1371
1372 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1373 THEN
1374 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
1375 END IF;
1376
1377 FND_MSG_PUB.Count_And_Get
1378 ( p_count => x_msg_count,
1379 p_data => x_msg_data
1380 );
1381
1382 END;
1383
1384
1385 -- ***************************************************
1386 -- start of comments
1387 -- ***************************************************
1388 --
1389 -- API name : Update_Qualifier
1390 -- Type : PRIVATE
1391 -- Function : To update existing qualifiers
1392 --
1393 -- Pre-reqs :
1394 -- Parameters:
1395 -- IN :
1396 -- Required
1397 -- Parameter Name Data Type Default
1398 -- p_api_version NUMBER
1399 -- p_Seed_Qual_Rec JTF_QUALIFIER_PUB.Seed_Qual_Rec_Type JTF_QUALIFIER_PUB.G_MISS_SEED_QUAL_REC
1400 -- p_Qual_Usgs_Rec JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type JTF_QUALIFIER_PUB.G_MISS_QUAL_USGS_ALL_REC
1401 --
1402 -- Optional
1403 -- Parameter Name Data Type Default
1404 -- P_Init_Msg_List VARCHAR2 FND_API.G_FALSE
1405 -- P_Commit VARCHAR2 FND_API.G_FALSE
1406 -- p_validation_level VARCHAR2 FND_API.G_VALID_LEVEL_FULL
1407 --
1408 -- OUT :
1409 -- Parameter Name Data Type Default
1410 -- x_Return_Status VARCHAR2(1)
1411 -- x_Msg_Count NUMBER
1412 -- x_Msg_Data VARCHAR2(2000)
1413 -- x_Seed_Qual_Rec Seed_Qual_Out_Rec_Type,
1414 -- x_Qual_Usgs_Rec Qual_Usgs_All_Out_Rec_Type);
1415 --
1416 --
1417 -- Notes:
1418 --
1419 --
1420 -- End of Comments
1421 --
1422 PROCEDURE Update_Qualifier
1423 (p_api_version IN NUMBER,
1424 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1425 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1426 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1427 x_Return_Status OUT NOCOPY VARCHAR2,
1428 x_Msg_Count OUT NOCOPY NUMBER,
1429 x_Msg_Data OUT NOCOPY VARCHAR2,
1430 p_Seed_Qual_Rec IN JTF_QUALIFIER_PUB.Seed_Qual_Rec_Type := JTF_QUALIFIER_PUB.G_MISS_SEED_QUAL_REC,
1431 p_Qual_Usgs_Rec IN JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type := JTF_QUALIFIER_PUB.G_MISS_QUAL_USGS_ALL_REC,
1432 x_Seed_Qual_Rec OUT NOCOPY JTF_QUALIFIER_PUB.Seed_Qual_Out_Rec_Type,
1433 x_Qual_Usgs_Rec OUT NOCOPY JTF_QUALIFIER_PUB.Qual_Usgs_All_Out_Rec_Type
1434 )
1435 IS
1436 l_api_name CONSTANT VARCHAR2(30) := 'Update_Qualifier';
1437 l_api_version CONSTANT NUMBER := 1.0;
1438 l_return_status VARCHAR2(1);
1439
1440 /* local scratch records */
1441 l_seed_qual_rec JTF_QUALIFIER_PUB.Seed_Qual_Rec_Type;
1442 l_seed_qual_out_rec JTF_QUALIFIER_PUB.Seed_Qual_Out_Rec_Type;
1443
1444 l_qual_usgs_rec JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type;
1445 l_qual_usgs_out_rec JTF_QUALIFIER_PUB.Qual_Usgs_All_Out_Rec_Type;
1446
1447 BEGIN
1448
1449
1450 -- Standard Start of API savepoint
1451 SAVEPOINT UPDATE_QUALIFIER_PVT;
1452
1453 -- Standard call to check for call compatability
1454 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1455 p_api_version,
1456 l_api_name,
1457 G_PKG_NAME)
1458 THEN
1459 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1460 END IF;
1461
1462 -- Initialize message list if p_init_msg_list is set to TRUE.
1463 IF FND_API.to_Boolean( p_init_msg_list )
1464 THEN
1465 FND_MSG_PUB.initialize;
1466 END IF;
1467
1468 -- Debug Message
1469 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1470 THEN
1471 FND_MESSAGE.Set_Name('JTF', 'PVT UpdateQual API: Start');
1472 FND_MSG_PUB.Add;
1473 END IF;
1474
1475
1476 -- ******************************************************************
1477 -- API BODY START
1478 -- ******************************************************************
1479
1480 -- Initialize API return status to success
1481 l_return_status := FND_API.G_RET_STS_SUCCESS;
1482
1483 -- Seeded Qualifier record doesn't have any default values, so
1484 -- just initialise local variable to value of variable passed in as
1485 -- a parameter to this procedure
1486 l_seed_qual_rec := p_seed_qual_rec;
1487
1488
1489 -- Converts missing items' values to default values in Qualifier Usages record
1490 convert_miss_qual_usgs_rec (p_qual_usgs_rec, l_qual_usgs_rec);
1491
1492
1493 IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
1494
1495 -- Debug message
1496 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1497 FND_MESSAGE.Set_Name('JTF', 'PVT QUAL API: Valdte Record');
1498 FND_MSG_PUB.ADD;
1499 END IF;
1500
1501 -- validate the seeded qualifier record
1502 validate_seed_qual_rec ( l_seed_qual_rec,
1503 JTF_CTM_UTILITY_PVT.G_UPDATE,
1504 p_validation_level,
1505 l_return_status);
1506
1507
1508 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1509 RAISE FND_API.G_EXC_ERROR;
1510 END IF;
1511
1512
1513
1514
1515 -- validate the qualifier usage record
1516 validate_qual_usgs_rec ( l_qual_usgs_rec,
1517 JTF_CTM_UTILITY_PVT.G_UPDATE,
1518 p_validation_level,
1519 l_return_status);
1520
1521
1522
1523 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1524 RAISE FND_API.G_EXC_ERROR;
1525 END IF;
1526
1527
1528
1529 END IF; -- End of Record Validation
1530
1531
1532 -- Process Seeded Qualifier Record
1533 ----------------------------------
1534
1535 -- Debug message
1536 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1537 FND_MESSAGE.Set_Name('JTF', 'PVTQUAL API:Updte SeedQualRec');
1538 FND_MSG_PUB.ADD;
1539 END IF;
1540
1541 -- Update seeded qualifier record into database
1542 update_seed_qual_record ( l_seed_qual_rec,
1543 l_seed_qual_out_rec);
1544 l_return_status := l_seed_qual_out_rec.return_status;
1545
1546
1547 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1548 RAISE FND_API.G_EXC_ERROR;
1549 END IF;
1550
1551
1552 -- Process Qualifier Usages Record
1553 ----------------------------------
1554
1555 -- Debug message
1556 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1557 FND_MESSAGE.Set_Name('JTF', 'PVTQUAL API:Update QualUsgRec');
1558 FND_MSG_PUB.ADD;
1559 END IF;
1560
1561 update_qual_usgs_record ( l_qual_usgs_rec,
1562 l_qual_usgs_out_rec);
1563
1564 l_return_status := l_qual_usgs_out_rec.return_status;
1565
1566 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1567 RAISE FND_API.G_EXC_ERROR;
1568 END IF;
1569
1570 -- save the returned seeded qualifer id, qualifier usage id, and status
1571 x_seed_qual_rec.seeded_qual_id := l_seed_qual_out_rec.seeded_qual_id;
1572 x_qual_usgs_rec.qual_usg_id := l_qual_usgs_out_rec.qual_usg_id;
1573 x_return_status := l_return_status;
1574
1575 -- *************************************************************************************
1576 -- API BODY END
1577 -- *************************************************************************************
1578
1579
1580 -- Success Message
1581 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) AND
1582 l_return_status = FND_API.G_RET_STS_SUCCESS
1583 THEN
1584 FND_MESSAGE.Set_Name('JTF', 'API_SUCCESS');
1585 FND_MESSAGE.Set_Token('ROW', 'JTF_QUALIFIER', TRUE);
1586 FND_MSG_PUB.Add;
1587 END IF;
1588
1589
1590
1591 -- Standard check of p_commit.
1592 IF FND_API.To_Boolean ( p_commit )
1593 THEN
1594 COMMIT WORK;
1595 END IF;
1596
1597 -- Debug Message
1598 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1599 THEN
1600 FND_MESSAGE.Set_Name('JTF', 'PVT Update Qual API: End');
1601 FND_MSG_PUB.Add;
1602 END IF;
1603
1604 -- Standard call to get message count and if count is 1, get message info.
1605 FND_MSG_PUB.Count_And_Get
1606 ( p_count => x_msg_count,
1607 p_data => x_msg_data
1608 );
1609
1610 EXCEPTION
1611
1612 WHEN FND_API.G_EXC_ERROR THEN
1613 ROLLBACK TO UPDATE_QUALIFIER_PVT;
1614 x_return_status := FND_API.G_RET_STS_ERROR ;
1615
1616 FND_MSG_PUB.Count_And_Get
1617 ( p_count => x_msg_count,
1618 p_data => x_msg_data
1619 );
1620
1621
1622 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1623 ROLLBACK TO UPDATE_QUALIFIER_PVT;
1624 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1625
1626 FND_MSG_PUB.Count_And_Get
1627 ( p_count => x_msg_count,
1628 p_data => x_msg_data
1629 );
1630
1631
1632 WHEN OTHERS THEN
1633 ROLLBACK TO UPDATE_QUALIFIER_PVT;
1634 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1635
1636 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1637 THEN
1638 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
1639 END IF;
1640
1641 FND_MSG_PUB.Count_And_Get
1642 ( p_count => x_msg_count,
1643 p_data => x_msg_data
1644 );
1645
1646 END Update_Qualifier;
1647
1648
1649 -- ***************************************************
1650 -- start of comments
1651 -- ***************************************************
1652 --
1653 -- API name : Delete_Qualifier
1654 -- Type : PRIVATE
1655 -- Function : To delete an existing qualifiers
1656 --
1657 -- Pre-reqs :
1658 -- Parameters:
1659 -- IN :
1660 -- Required
1661 -- Parameter Name Data Type Default
1662 -- p_api_version NUMBER
1663 -- p_Seeded_Qual_Id NUMBER FND_API.G_MISS_NUM
1664 -- p_Qual_Usgs_Id NUMBER FND_API.G_MISS_NUM
1665 --
1666 -- Optional
1667 -- Parameter Name Data Type Default
1668 -- P_Init_Msg_List VARCHAR2 FND_API.G_FALSE
1669 -- P_Commit VARCHAR2 FND_API.G_FALSE
1670 -- p_validation_level VARCHAR2 FND_API.G_VALID_LEVEL_FULL
1671 --
1672 -- OUT :
1673 -- Parameter Name Data Type Default
1674 -- x_Return_Status VARCHAR2(1)
1675 -- x_Msg_Count NUMBER
1676 -- x_Msg_Data VARCHAR2(2000)
1677 --
1678 --
1679 -- Notes:
1680 --
1681 --
1682 -- End of Comments
1683 --
1684 PROCEDURE Delete_Qualifier
1685 (p_api_version IN NUMBER,
1686 p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1687 p_Commit IN VARCHAR2 := FND_API.G_FALSE,
1688 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1689 x_Return_Status OUT NOCOPY VARCHAR2,
1690 x_Msg_Count OUT NOCOPY NUMBER,
1691 x_Msg_Data OUT NOCOPY VARCHAR2,
1692 p_Seeded_Qual_Id IN NUMBER := FND_API.G_MISS_NUM,
1693 p_Qual_Usg_Id IN NUMBER := FND_API.G_MISS_NUM
1694 )
1695 IS
1696 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Qualifier';
1697 l_api_version CONSTANT NUMBER := 1.0;
1698 l_return_status VARCHAR2(1);
1699
1700 l_seeded_qual_id NUMBER;
1701 l_qual_usg_id NUMBER;
1702
1703 BEGIN
1704
1705
1706
1707
1708 -- Standard Start of API savepoint
1709 SAVEPOINT DELETE_QUALIFIER_PVT;
1710
1711 -- Standard call to check for call compatability
1712 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1713 p_api_version,
1714 l_api_name,
1715 G_PKG_NAME)
1716 THEN
1717 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1718 END IF;
1719
1720
1721 -- Initialize message list if p_init_msg_list is set to TRUE.
1722 IF FND_API.to_Boolean( p_init_msg_list )
1723 THEN
1724 FND_MSG_PUB.initialize;
1725 END IF;
1726
1727 -- Debug Message
1728 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1729 THEN
1730 FND_MESSAGE.Set_Name('JTF', 'Delete Qualifier PVT: Start');
1731 FND_MSG_PUB.Add;
1732 END IF;
1733
1734 -- ******************************************************************
1735 -- API BODY START
1736 -- ******************************************************************
1737
1738 -- Initialize API return status to success
1739 l_return_status := FND_API.G_RET_STS_SUCCESS;
1740
1741 /* initialise local variable to value of variable passed in as
1742 ** a parameter to this procedure
1743 */
1744 l_seeded_qual_id := p_seeded_qual_id;
1745 l_qual_usg_id := p_qual_usg_id;
1746
1747
1748
1749 -- CHECK IF RECORDS SHOULD BE DELETED
1750 --
1751 -- seeded flag <> Y
1752 -- cannot delete seeded_qual if more that one qual_usg exists for that qualifier
1753 -- cannot delete qualifier if it is being used in a territory or territory type definition
1754 /* check if record should be deleted */
1755 is_qualifier_delete_allowed ( l_seeded_qual_id, l_qual_usg_id , l_return_status);
1756
1757
1758
1759 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1760
1761
1762 RAISE FND_API.G_EXC_ERROR;
1763 END IF;
1764
1765
1766 -- Process Seeded Qualifier Record
1767 ----------------------------------
1768
1769 -- Debug message
1770 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1771 FND_MESSAGE.Set_Name('JTF', 'Delete Qualifier PVT: Debug1');
1772 FND_MSG_PUB.ADD;
1773 END IF;
1774
1775
1776
1777 -- Update seeded qualifier record into database
1778 delete_seed_qual_record ( l_seeded_qual_id,
1779 l_return_status);
1780
1781
1782
1783
1784 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1785 RAISE FND_API.G_EXC_ERROR;
1786 END IF;
1787
1788
1789 -- Process Qualifier Usages Record
1790 ----------------------------------
1791
1792 -- Debug message
1793 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1794 FND_MESSAGE.Set_Name('JTF', 'Delete Qualifier PVT: Debug2');
1795 FND_MSG_PUB.ADD;
1796 END IF;
1797
1798
1799
1800 delete_qual_usgs_record ( l_qual_usg_id,
1801 l_return_status);
1802
1803
1804 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1805 RAISE FND_API.G_EXC_ERROR;
1806 END IF;
1807
1808 /* save return status */
1809 x_return_status := l_return_status;
1810
1811 -- *************************************************************************************
1812 -- API BODY END
1813 -- *************************************************************************************
1814
1815
1816 -- Success Message
1817 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) AND
1818 l_return_status = FND_API.G_RET_STS_SUCCESS
1819 THEN
1820 FND_MESSAGE.Set_Name('JTF', 'API_SUCCESS');
1821 FND_MESSAGE.Set_Token('ROW', 'JTF_QUALIFIER', TRUE);
1822 FND_MSG_PUB.Add;
1823 END IF;
1824
1825 -- Standard check of p_commit.
1826 IF FND_API.To_Boolean ( p_commit )
1827 THEN
1828 COMMIT WORK;
1829 END IF;
1830
1831 -- Debug Message
1832 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1833 THEN
1834 FND_MESSAGE.Set_Name('JTF', 'PVT Delete Qual API: End');
1835 FND_MSG_PUB.Add;
1836 END IF;
1837
1838 -- Standard call to get message count and if count is 1, get message info.
1839 FND_MSG_PUB.Count_And_Get
1840 ( p_count => x_msg_count,
1841 p_data => x_msg_data
1842 );
1843
1844 EXCEPTION
1845
1846 WHEN FND_API.G_EXC_ERROR THEN
1847 ROLLBACK TO DELETE_QUALIFIER_PVT;
1848 x_return_status := FND_API.G_RET_STS_ERROR ;
1849
1850 FND_MSG_PUB.Count_And_Get
1851 ( p_count => x_msg_count,
1852 p_data => x_msg_data
1853 );
1854
1855
1856 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1857 ROLLBACK TO DELETE_QUALIFIER_PVT;
1858 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1859
1860 FND_MSG_PUB.Count_And_Get
1861 ( p_count => x_msg_count,
1862 p_data => x_msg_data
1863 );
1864
1865
1866 WHEN OTHERS THEN
1867 ROLLBACK TO DELETE_QUALIFIER_PVT;
1868 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1869
1870 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1871 THEN
1872 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
1873 END IF;
1874
1875 FND_MSG_PUB.Count_And_Get
1876 ( p_count => x_msg_count,
1877 p_data => x_msg_data
1878 );
1879
1880 END;
1881
1882 END JTF_QUALIFIER_PVT; -- Package Body JTF_QUALIFIER_PVT