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