[Home] [Help]
PACKAGE BODY: APPS.CS_TP_TEMPLATES_PVT
Source
1 package body CS_TP_TEMPLATES_PVT as
2 /* $Header: cstptmmb.pls 120.0 2005/06/01 10:55:21 appldev noship $ */
3
4 /*============================================================================+
5 | Copyright (c) 1999 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================+
9 | History |
10 | Created by nazhou. |
11 | 30-APR-2002 KLOU |
12 | 1. Fix problem in show_templates_with_link that when |
13 | only urgency profile is turn on, a sql "invalid |
14 | column" exception is thrown. |
15 | 23-Sep-2002 WMA |
16 | 2. Tune up the performance for querying the link |
17 | 115.41 03-DEC-2002 WZLI changed OUT and IN OUT calls to use NOCOPY hint |
18 | to enable pass by reference. |
19 | 115.42 05-FEB-2003 WMA |
20 | Add four more new APIS |
21 | Show_template_links_Two |
22 | Show_Non_Asso_Links_TWo |
23 | Delete_Template_Link |
24 | Add_Template_Link |
25 | 115.43 26-FEB-2003 WMA |
26 | In produce Show_Link_attribute_list |
27 | if the first character of the arrtibute name (l_name) is space |
28 | remove it( 2824600) |
29 | 115.44 01-FEB-2004 WMA |
30 | change the template ID and jtf_code to be dynamically bind |
31 | in procedure Show_template_attributes. |
32 | 115.45 13-OCT-2004 WMA |
33 | change procedure show_template_non_asso_links_two for performance|
34 | tuning. |
35 | 115.46 18-APR-2005 WMA |
36 | Handle the date format issue, change the literal queries. |
37 | it is copied from version: 115.44.11510.4. |
38 +============================================================================*/
39 -- ---------------------------------------------------------
40 -- Define global variables and types
41 -- ---------------------------------------------------------
42 l_default_last_up_date_format CONSTANT VARCHAR2(30) := 'MM/DD/YYYY/SSSSS';
43 G_PKG_NAME CONSTANT VARCHAR2(100) := 'CS_TP_TEMPLATE_PVT';
44
45 l_default_update_format2 VARCHAR2(100) := '';
46
47 FUNCTION get_user_id RETURN NUMBER AS
48 BEGIN
49 Return FND_GLOBAL.USER_ID;
50 END get_user_id;
51
52 FUNCTION get_date_format_from_user(p_user_id IN NUMBER)
53 RETURN VARCHAR2 AS
54 BEGIN
55 -- get the default date format for this user
56 Return FND_PROFILE.VALUE_SPECIFIC(
57 'ICX_DATE_FORMAT_MASK',
58 p_user_id,
59 null,
60 null);
61 EXCEPTION
62 WHEN OTHERS THEN
63 RETURN 'MON-DD-YYYY'; -- use this one as default
64 END get_date_format_from_user;
65
66 FUNCTION get_date_format_from_user_two
67 RETURN VARCHAR2 AS
68 p_user_id NUMBER;
69 p_format varchar2(100) := null;
70 BEGIN
71 p_user_id := get_user_id;
72 -- get the default date format for this user
73 p_format := FND_PROFILE.VALUE_SPECIFIC(
74 'ICX_DATE_FORMAT_MASK',
75 p_user_id,
76 null,
77 null);
78 if( p_format is null or p_format = '') then
79 p_format := 'MON-DD-YYYY';
80 end if;
81
82 return p_format;
83
84 EXCEPTION
85 WHEN OTHERS THEN
86 RETURN 'MON-DD-YYYY'; -- use this one as default
87 END get_date_format_from_user_two;
88
89
90 -- This function is temporaly fixed for the UAT env
91 -- This function is unified with Calender_date_format
92 -- later the date format should be read from the user-profile
93 FUNCTION get_calender_date_format RETURN VARCHAR2 AS
94 BEGIN
95 Return 'DD-MON-RRRR';
96 END get_calender_date_format;
97
98 FUNCTION get_date_format RETURN VARCHAR2 AS
99 BEGIN
100 RETURN get_calender_date_format;
101 END get_date_format;
102
103 PROCEDURE check_attribute_error (
104 p_template_attributes IN template_attribute_list,
105 x_return_status Out NOCOPY varchar2)
106 IS
107 sorted_list template_attribute_list;
108 temp_attribute template_attribute;
109 i number;
110 j number;
111 BEGIN
112 x_return_status := fnd_api.g_ret_sts_success;
113 sorted_list := p_template_attributes;
114
115 for i in sorted_list.first..(sorted_list.last-1) loop
116 for j in sorted_list.first..(sorted_list.last-1) loop
117 if (sorted_list(j+1).mendthreshold < sorted_list(j).mendthreshold) then
118 temp_attribute := sorted_list(j+1);
119 sorted_list(j+1):= sorted_list(j);
120 sorted_list(j):= temp_attribute;
121 end if;
122 end loop;
123 end loop;
124 for i in sorted_list.first..(sorted_list.last-1) loop
125 if (sorted_list(i).mstartthreshold > g_attr_max_threshold or
126 sorted_list(i).mstartthreshold < g_attr_min_threshold or
127 sorted_list(i).mendthreshold > g_attr_max_threshold or
128 sorted_list(i).mendthreshold < g_attr_min_threshold or
129 sorted_list(i).mstartthreshold > sorted_list(i).mendthreshold ) then
130 x_return_status := fnd_api.g_ret_sts_error;
131 fnd_message.set_name('cs','CS_TP_TEMPLATE_ATTR_THRESH');
132 fnd_msg_pub.add;
133 raise fnd_api.g_exc_error;
134 elsif (sorted_list(i).mstartthreshold=0 and sorted_list(i).mendthreshold=0) then
135 null;
136 elsif (sorted_list(i).mendthreshold >= sorted_list(i+1).mstartthreshold) then
137 x_return_status := fnd_api.g_ret_sts_error;
138 fnd_message.set_name('CS','CS_TP_TEMPLATE_ATTR_THRESH');
139 fnd_msg_pub.add;
140 raise fnd_api.g_exc_error;
141 end if;
142 end loop;
143 -- Check the last element
144 if (sorted_list(sorted_list.last).mstartthreshold > g_attr_max_threshold or
145 sorted_list(sorted_list.last).mstartthreshold < g_attr_min_threshold or
146 sorted_list(sorted_list.last).mendthreshold > g_attr_max_threshold or
147 sorted_list(sorted_list.last).mendthreshold < g_attr_min_threshold or
148 sorted_list(sorted_list.last).mstartthreshold > sorted_list(sorted_list.last).mendthreshold ) then
149 x_return_status := fnd_api.g_ret_sts_error;
150 fnd_message.set_name('CS','CS_TP_TEMPLATE_ATTR_THRESH');
151 fnd_msg_pub.add;
152 raise fnd_api.g_exc_error;
153 end if;
154 end Check_Attribute_Error;
155
156 -- ---------------------------------------------------------
157 -- Define public procedures
158 -- ---------------------------------------------------------
159 -- *****************************************************************************
160 -- Start of Comments
161 -- This procedure Add_Template Add an additional template to the CS_TP_Templates_B Table
162 -- The user needs to pass in a template record which holds the template attributes.
163 -- User can leave the template id and last_updated_date field in the template
164 -- record blank. However, user needs to pass in the rest of the fields in
165 -- the template record. In addition, the mEndDate must be later than mStartDate
166 --
167 -- @param p_one_template required
168 -- @param p_api_version_number required
169 -- @param p_commit
170 -- @param p_init_msg_list
171
172 -- @return x_template_id
173 -- x_msg_count
174 -- x_msg_data
175 -- x_return_status
176 -- Changed by KLOU, 05/01/2002
177 -- 1. Remove the Begin-End block when mEndDate < mStartDate. Instead, raise exception
178 -- directly.
179 -- 2. When mDefaultFlag is null, set it to 'F' instead of raising exception.
180 --
181 -- End of Comments
182 PROCEDURE Add_Template (
183 p_api_version_number IN NUMBER,
184 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
185 p_commit IN VARCHAR := FND_API.G_FALSE,
186 p_one_template IN TEMPLATE,
187 x_msg_count OUT NOCOPY NUMBER,
188 x_msg_data OUT NOCOPY VARCHAR2,
189 x_return_status OUT NOCOPY VARCHAR2,
190 x_template_id OUT NOCOPY NUMBER)
191
192 IS
193 l_api_name CONSTANT VARCHAR2(30) := 'Add_Template';
194 l_api_version CONSTANT NUMBER := 1.0;
195 l_template_id NUMBER := FND_API.G_MISS_NUM;
196 l_current_date DATE := FND_API.G_MISS_DATE;
197 l_created_by NUMBER := FND_API.G_MISS_NUM;
198 l_login NUMBER := FND_API.G_MISS_NUM;
199 l_rowid VARCHAR2(100);
200 l_date_format VARCHAR2(100);
201
202 BEGIN
203 -- Initialize message list if p_init_msg_list is set to TRUE.
204 if fnd_api.to_boolean( p_init_msg_list ) then
205 fnd_msg_pub.initialize;
206 end if;
207
208 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
209
210 -- Start API Body
211 -- Perform validation
212
213 --l_date_format := get_date_format;
214 l_date_format := get_date_format_from_user_two;
215
216 if p_one_template.mtemplatename is null or
217 p_one_template.mtemplatename = fnd_api.g_miss_char then
218 x_return_status := fnd_api.g_ret_sts_error;
219 fnd_message.set_name('CS','CS_TP_TEMPLATE_NAME_INVALID');
220 fnd_msg_pub.add;
221 raise fnd_api.g_exc_error;
222 end if;
223
224 if (to_date(p_one_template.mEndDate, l_date_format))
225 < (to_date(p_one_template.mStartDate, l_date_format)) then
226 --incorrect date format, raise generic exception
227 x_return_status := fnd_api.g_ret_sts_error;
228 fnd_message.set_name('CS','CS_TP_TEMPLATE_DATE_INVALID');
229 fnd_msg_pub.add;
230 raise fnd_api.g_exc_error;
231 end if;
232
233 --Get the template id from the next available sequence number
234 select cs_tp_templates_s.nextval into l_template_id from dual;
235
236 l_current_date := sysdate;
237 l_created_by := fnd_global.user_id;
238 l_login := fnd_global.login_id;
239
240 CS_TP_TEMPLATES_PKG.INSERT_ROW (
241 x_rowid => l_rowid,
242 x_template_id => l_template_id,
243 x_default_flag => nvl(p_one_template.mDefaultFlag, 'F'),
244 x_start_date_active => to_date (p_one_template.mStartDate, l_date_format),
245 x_end_date_active => to_date (p_one_template.mEndDate, l_date_format),
246 x_name => p_one_template.mTemplateName,
247 x_description => null,
248 x_creation_date => l_current_date,
249 x_created_by => l_created_by,
250 x_last_update_date => l_current_date,
251 x_last_updated_by => l_created_by,
252 x_last_update_login => l_login,
253 x_attribute1 => p_one_template.mShortCode,
254 x_uni_question_note_flag => p_one_template.mUniquestionNoteFlag,
255 x_uni_question_note_type => p_one_template.mUniquestionNoteType);
256
257 x_template_id := l_template_id;
258
259 if fnd_api.to_boolean( p_commit ) then
260 commit work;
261 end if;
262 Fnd_Msg_Pub.Count_And_Get(
263 p_count => x_msg_count ,
264 p_data => x_msg_data );
265 END Add_Template;
266
267 -- *****************************************************************************
268 -- Start of Comments
269 --
270 -- Delete Template will delete the template with the passed in template id in the CS_TP_Templates_B and CS_TP_Templates_TL table with the passed in P_Template_ID
271 --
272 -- An exception will be raised if the template with passed in templated id cannot be found
273 -- @param P_Template_ID required
274 -- @param p_api_version_number required
275 -- @param p_commit
276 -- @param p_init_msg_list
277 -- @return x_msg_count
278 -- x_msg_data
279 -- x_return_status
280 --
281 -- End of Comments
282 PROCEDURE Delete_Template (
283 p_api_version_number IN NUMBER,
284 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
285 p_commit IN VARCHAR := FND_API.G_FALSE,
286 p_template_id IN NUMBER,
287 x_msg_count OUT NOCOPY NUMBER,
288 x_msg_data OUT NOCOPY VARCHAR2,
289 x_return_status OUT NOCOPY VARCHAR2)
290 IS
291 l_api_name CONSTANT VARCHAR2(30):= 'Delete_Template';
292 l_api_version CONSTANT NUMBER := 1.0;
293
294 BEGIN
295 -- Initialize message list if p_init_msg_list is set to TRUE.
296 If Fnd_Api.To_Boolean( p_init_msg_list ) Then
297 Fnd_Msg_Pub.Initialize;
298 End If;
299
300 x_return_status := FND_API.G_RET_STS_SUCCESS;
301 CS_TP_TEMPLATES_PKG.DELETE_ROW (P_Template_ID);
302
303 -- Standard check of p_commit.
304 If Fnd_Api.To_Boolean( p_commit ) Then
305 Commit Work;
306 End If;
307
308 Fnd_Msg_Pub.Count_And_Get(p_count => x_msg_count ,
309 p_data => x_msg_data);
310
311 EXCEPTION
312 WHEN OTHERS THEN
313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
314 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
315 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
316 END IF;
317 FND_MSG_PUB.Count_And_Get
318 (p_count => x_msg_count ,
319 p_data => x_msg_data);
320 Raise;
321 END Delete_Template;
322
323 -- *****************************************************************************
324 -- Start of Comments
325 --
326 -- Update Template will update the template with a specific template id in the CS_TP_Templates_B and CS_TP_Templates_TL table with the new template attributes.
327 -- All fields inside the template are required
328 -- An exception is raised if template with template id cannot be found
329 -- @param P_One_Template required
330 -- @param p_api_version_number required
331 -- @param p_commit
332 -- @param p_init_msg_list
333
334 -- @return x_msg_count
335 -- x_msg_data
336 -- x_return_status
337 --
338 -- End of Comments
339 -- *****************************************************************************
340
341 PROCEDURE Update_Template (
342 p_api_version_number IN NUMBER,
343 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
344 p_commit IN VARCHAR := FND_API.G_FALSE,
345 p_one_template IN Template,
346 x_msg_count OUT NOCOPY NUMBER,
347 x_msg_data OUT NOCOPY VARCHAR2,
348 x_return_status OUT NOCOPY VARCHAR2)
349
350 IS
351 l_api_name CONSTANT VARCHAR2(30):= 'Update_Template';
352 l_api_version CONSTANT NUMBER := 1.0;
353 l_date_format VARCHAR2(60):= FND_API.G_MISS_CHAR;
354 l_last_updated_date DATE;
355 l_current_date DATE :=FND_API.G_MISS_DATE;
356 l_last_updated_by NUMBER :=FND_API.G_MISS_NUM;
357 l_login NUMBER :=FND_API.G_MISS_NUM;
358 CURSOR c IS
359 Select last_update_date From CS_TP_TEMPLATES_B
360 Where TEMPLATE_ID = p_one_template.mTemplateID;
361
362 BEGIN
363 IF FND_API.to_Boolean( p_init_msg_list ) THEN
364 FND_MSG_PUB.initialize;
365 END IF;
366
367 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
368
369 -- Perform validation
370 -- l_date_format := get_calender_date_format;
371
372 l_date_format := get_date_format_from_user_two;
373 IF(nvl(p_one_template.mTemplateName,fnd_api.g_miss_char)=fnd_api.g_miss_char) THEN
374 X_Return_Status := FND_API.G_RET_STS_ERROR;
375 FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_NAME_INVALID');
376 FND_MSG_PUB.Add;
377 RAISE FND_API.G_EXC_ERROR;
378 END IF;
379
380 IF (nvl(P_One_Template.mDefaultFlag,fnd_api.g_miss_char)=fnd_api.g_miss_char) THEN
381 X_Return_Status := FND_API.G_RET_STS_ERROR;
382 FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_ATTR_INVALID');
383 FND_MSG_PUB.Add;
384 RAISE FND_API.G_EXC_ERROR;
385 END IF;
386
387 IF TO_DATE(P_One_Template.mEndDate, l_date_format)
388 < TO_DATE (P_One_Template.mStartDate, l_date_format) THEN
389 X_Return_Status := FND_API.G_RET_STS_ERROR;
390 FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_DATE_INVALID');
391 FND_MSG_PUB.Add;
392 RAISE FND_API.G_EXC_ERROR;
393 END IF;
394 --check to see if the template is modified after the client's query
395 Open c;
396 Fetch c Into l_last_updated_date;
397 If (c%notfound) Then
398 Close c;
399 X_Return_Status := FND_API.G_RET_STS_ERROR;
400 FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_ID_INVALID');
401 FND_MSG_PUB.Add;
402 Raise no_data_found;
403 End If;
404 Close c;
405
406 If (P_One_Template.mLast_Updated_Date Is Null
407 OR length(P_One_Template.mLast_Updated_Date) <=0
408 OR P_One_Template.mLast_Updated_Date = FND_API.G_MISS_CHAR) then
409 X_Return_Status := FND_API.G_RET_STS_ERROR;
410 FND_MESSAGE.SET_NAME('CS','CS_TP_LASTUPDATE_DATE_NULL');
411 FND_MSG_PUB.Add;
412 RAISE FND_API.G_EXC_ERROR;
413 End If;
414
415 l_default_update_format2 :=
416 get_date_format_from_user_two||' HH24:MI:SS';
417 -- is the last updated date from db later than the date from client
418 If (l_last_updated_date >
419 to_date(P_One_Template.mLast_Updated_Date, l_default_update_format2)) Then
420 X_Return_Status := FND_API.G_RET_STS_ERROR;
421 FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_UPDATED');
422 FND_MSG_PUB.Add;
423 RAISE FND_API.G_EXC_ERROR;
424 End If;
425
426 l_current_date := sysdate;
427 l_last_updated_by := fnd_global.user_id;
428 l_login := fnd_global.login_id;
429
430 CS_TP_TEMPLATES_PKG.Update_Row (
431 x_template_id => P_One_Template.mTemplateID,
432 x_default_flag => P_One_Template.mDefaultFlag,
433 x_start_date_active => TO_DATE (P_One_Template.mStartDate, l_date_format),
434 x_end_date_active => TO_DATE (P_One_Template.mEndDate, l_date_format),
435 x_name => P_One_Template.mTemplateName,
436 x_description => NULL,
437 x_last_update_date => l_current_date,
438 x_last_updated_by => l_last_updated_by,
439 x_last_update_login => l_login,
440 x_attribute1 => P_One_Template.mShortCode,
441 x_uni_question_note_flag => P_One_Template.mUniQuestionNoteFlag,
442 x_uni_question_note_type => P_One_Template.mUniQuestionNoteType );
443
444 IF FND_API.To_Boolean( p_commit ) THEN
445 COMMIT WORK;
446 END IF;
447
448 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count ,
449 p_data => x_msg_data);
450
451 EXCEPTION
452 WHEN FND_API.G_EXC_ERROR THEN
453 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count ,
454 p_data => x_msg_data);
455 END Update_Template;
456
457 PROCEDURE Update_Template_Attributes (
458 p_api_version_number IN NUMBER,
459 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
460 p_commit IN VARCHAR := FND_API.G_FALSE,
461 p_template_id IN NUMBER,
462 p_template_attributes IN Template_Attribute_List,
463 x_msg_count OUT NOCOPY NUMBER,
464 x_msg_data OUT NOCOPY VARCHAR2,
465 x_return_status OUT NOCOPY VARCHAR2)
466 IS
467 l_api_name CONSTANT VARCHAR2(30) := 'Update_Template_Attributes';
468 l_api_verion CONSTANT NUMBER := 1.0;
469 l_template_count_with_id NUMBER;
470 l_stmt VARCHAR2(210);
471 l_JTF_OBJECT_CursorID NUMBER;
472 l_JTF_OBJECT_CODE_count NUMBER;
473 l_ROW_ID VARCHAR2(30);
474 l_current_date DATE :=FND_API.G_MISS_DATE;
475 l_created_by NUMBER :=FND_API.G_MISS_NUM;
476 l_login NUMBER :=FND_API.G_MISS_NUM;
477 l_last_updated_date DATE;
478 l_attribute_id NUMBER;
479
480 CURSOR C IS
481 Select count(*) From cs_tp_templates_b;
482
483 CURSOR Last_Updated_Date_C (v_template_id NUMBER,
484 v_other_id NUMBER,
485 v_jtf_object_code VARCHAR2) IS
486 Select last_update_date
487 From cs_tp_template_attribute
488 where template_id = v_template_id
489 and other_id = v_other_id
490 and object_code = v_jtf_object_code;
491
492 l_One_Template_Attribute Template_Attribute;
493
494 BEGIN
495 -- Initialize message list if p_init_msg_list is set to TRUE.
496 IF FND_API.to_Boolean( p_init_msg_list ) THEN
497 FND_MSG_PUB.initialize;
498 END IF;
499
500 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
501
502 -- perform validation, see if template id is valid
503 OPEN C;
504 FETCH C INTO l_template_count_with_id;
505 IF (l_template_count_with_id <=0 ) THEN
506 CLOSE C;
507 X_Return_Status := FND_API.G_RET_STS_ERROR;
508 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
509 END IF;
510 CLOSE C;
511
512 -- perform attribute error checking
513 Check_Attribute_Error (P_Template_Attributes, X_Return_Status);
514
515 /* Logic:
516 Loop through each attribute. If the attribute id is present,
517 perform update the record. Otherwise, insert into the cs_tp_template_attribute
518 table
519 */
520 FOR i IN P_Template_Attributes.FIRST..P_Template_Attributes.LAST LOOP
521
522 l_One_Template_Attribute := P_Template_Attributes (i);
523 If (nvl(l_One_Template_Attribute.mAttributeName,FND_API.G_MISS_CHAR)
524 = FND_API.G_MISS_CHAR) Then
525 X_Return_Status := FND_API.G_RET_STS_ERROR;
526 FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_ATTR_NAME');
527 FND_MSG_PUB.Add;
528 RAISE FND_API.G_EXC_ERROR;
529 End If;
530
531 If (l_One_Template_Attribute.mAttributeID is not NULL
532 and l_One_Template_Attribute.mAttributeID <> FND_API.G_MISS_NUM
533 and (l_One_Template_Attribute.mLast_Updated_Date is NULL
534 OR l_One_Template_Attribute.mLast_Updated_Date =
535 FND_API.G_MISS_CHAR)) Then
536 X_Return_Status := FND_API.G_RET_STS_ERROR;
537 FND_MESSAGE.SET_NAME('CS','CS_TP_TEM_AT_LUPD_NULL');
538 FND_MSG_PUB.Add;
539 RAISE FND_API.G_EXC_ERROR;
540 End If;
541
542 --check to see if the row is updated or inserted after the user queries the
543 --template attribute
544 Open Last_Updated_Date_C(P_Template_ID,
545 l_One_Template_Attribute.mOther_ID,
546 l_One_Template_Attribute.mJTF_OBJECT_CODE);
547 Fetch Last_Updated_Date_C Into l_last_updated_date;
548
549 If (Last_Updated_Date_C%Notfound) Then
550 Close Last_Updated_Date_C;
551 Elsif (l_One_Template_Attribute.mAttributeID Is Null
552 OR l_One_Template_Attribute.mAttributeID = FND_API.G_MISS_NUM) Then
553 -- row is already inserted
554 X_Return_Status := FND_API.G_RET_STS_ERROR;
555 FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_ATTR_UPDATED');
556 FND_MSG_PUB.Add;
557 RAISE FND_API.G_EXC_ERROR;
558 Elsif (l_last_updated_date >
559 TO_DATE (l_One_Template_Attribute.mLast_Updated_Date,
560 l_default_last_up_date_format ))
561 Then
562 -- row is already updated
563 X_Return_Status := FND_API.G_RET_STS_ERROR;
564 FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_ATTR_UPDATED');
565 FND_MSG_PUB.Add;
566 RAISE FND_API.G_EXC_ERROR;
567 End If;
568 If (Last_Updated_Date_C%ISOPEN) Then
569 Close Last_Updated_Date_C;
570 End If;
571
572 l_current_date := sysdate;
573 l_created_by := FND_GLOBAL.user_id;
574 l_login := fnd_global.login_id;
575
576 If (l_One_Template_Attribute.mAttributeID is NULL
577 OR l_One_Template_Attribute.mAttributeID = FND_API.G_MISS_NUM) Then
578 -- need to insert into the Attribute Table
579 Select CS_TP_TEMPLATE_ATTRIBUTE_S.NextVal Into l_attribute_id From dual;
580
581 CS_TP_TEMPLATE_ATTRIBUTE_PKG.INSERT_ROW (
582 x_rowid => l_Row_ID ,
583 x_template_attribute_id => l_attribute_id,
584 x_template_id => P_Template_ID,
585 x_other_id => l_One_Template_Attribute.mOther_ID,
586 x_object_code => l_One_Template_Attribute.mJTF_OBJECT_CODE,
587 x_start_threshold => l_One_Template_Attribute.mStartThreshold,
588 x_end_threshold => l_One_Template_Attribute.mEndThreshold,
589 x_attribute1 => l_One_Template_Attribute.mDefaultFlag,
590 x_creation_date => l_current_date,
591 x_created_by => l_created_by,
592 x_last_update_date => l_current_date,
593 x_last_updated_by => l_created_by,
594 x_last_update_login => l_login);
595 Else
596 CS_TP_TEMPLATE_ATTRIBUTE_PKG.UPDATE_ROW (
597 x_template_attribute_id => l_One_Template_Attribute.mAttributeID,
598 x_template_id => P_Template_ID,
599 x_other_id => l_One_Template_Attribute.mOther_ID,
600 x_object_code => l_One_Template_Attribute.mJTF_OBJECT_CODE,
601 x_start_threshold => l_One_Template_Attribute.mStartThreshold,
602 x_end_threshold => l_One_Template_Attribute.mEndThreshold,
603 x_attribute1 => l_One_Template_Attribute.mDefaultFlag,
604 x_last_update_date => l_current_date,
605 x_last_updated_by => l_created_by,
606 x_last_update_login => l_login);
607 End If;
608 END LOOP;
609
610 IF FND_API.To_Boolean( p_commit ) THEN
611 COMMIT WORK;
612 END IF;
613
614 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count ,
615 p_data => x_msg_data);
616
617 EXCEPTION
618 WHEN FND_API.G_EXC_ERROR THEN
619 x_return_status := FND_API.G_RET_STS_ERROR;
620 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
621 p_data => x_msg_data);
622 WHEN OTHERS THEN
623 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
624 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
625 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
626 END IF;
627 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
628 p_data => x_msg_data);
629 RAISE;
630 END Update_Template_Attributes;
631
632 PROCEDURE Update_Template_Links (
633 p_api_version_number IN NUMBER,
634 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
635 p_commit IN VARCHAR := FND_API.G_FALSE,
636 p_template_id IN NUMBER,
637 p_jtf_object_code IN VARCHAR2,
638 p_template_links IN Template_Link_List,
639 x_msg_count OUT NOCOPY NUMBER,
640 x_msg_data OUT NOCOPY VARCHAR2,
641 x_return_status OUT NOCOPY VARCHAR2)
642 IS
643 TYPE l_Need_To_Be_Delete_List_type is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
644 l_api_name CONSTANT VARCHAR2(30) := 'Update_Template_Links';
645 l_api_version CONSTANT NUMBER := 1.0;
646 l_JTF_OBJECT_CursorID NUMBER;
647 l_JTF_OBJECT_CODE_count NUMBER;
648 l_current_date DATE :=FND_API.G_MISS_DATE;
649 l_created_by NUMBER :=FND_API.G_MISS_NUM;
650 l_login NUMBER :=FND_API.G_MISS_NUM;
651 l_Row_ID VARCHAR2(30);
652 l_New_Link_id NUMBER;
653 l_One_Template_Link Template_Link;
654 l_Need_To_Be_Delete_List l_Need_To_Be_Delete_List_type;
655 l_template_count_with_id NUMBER;
656 l_stmt VARCHAR2(100);
657 i NUMBER;
658 CURSOR C IS
659 Select count(*)
660 From CS_TP_TEMPLATES_B
661 Where template_id = P_Template_ID;
662 CURSOR Need_To_Be_Delete_Cursor (v_current_date DATE) IS
663 Select link_id
664 From CS_TP_TEMPLATE_LINKS
665 Where template_id = P_Template_ID
666 and OBJECT_CODE = P_JTF_OBJECT_CODE
667 and last_update_date < v_current_date ;
668 BEGIN
669 IF FND_API.to_Boolean( p_init_msg_list ) THEN
670 FND_MSG_PUB.initialize;
671 END IF;
672
673 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
674
675 -- perform validation, see if template id is valid
676 Open C;
677 Fetch C Into l_template_count_with_id;
678 If (l_template_count_with_id <=0 ) Then
679 Close C;
680 X_Return_Status := FND_API.G_RET_STS_ERROR;
681 FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_Link_TID_INV');
682 FND_MSG_PUB.Add;
683 Raise FND_API.G_EXC_ERROR ;
684 End If;
685 Close C;
686 l_current_date := sysdate;
687 l_created_by := fnd_global.user_id;
688 l_login := fnd_global.login_id;
689
690 --loop through each link. If the link id is present, perform an update.
691 If (P_Template_Links.COUNT >0) Then
692 For i In P_Template_Links.FIRST..P_Template_Links.LAST Loop
693 l_One_Template_Link := P_Template_Links (i);
694 -- If the link_id is passed, modify the row, otherwise insert the row.
695 -- After insertion and modifying, delete the rows that have not be inserted
696 -- or modified in the table
697
698 If (l_One_Template_Link.mLinkID Is Null
699 Or l_One_Template_Link.mLinkID = FND_API.G_MISS_NUM) Then
700
701 --Get the template id from the next available sequence number
702 Select CS_TP_TEMPLATE_LINKS_S.nextval Into l_New_Link_id From dual;
703 CS_TP_TEMPLATE_LINKS_PKG.INSERT_ROW (
704 x_rowid => l_Row_ID ,
705 x_link_id => l_New_Link_id,
706 x_template_id => P_Template_ID,
707 x_other_id => l_One_Template_Link.mOther_ID,
708 x_lookup_code => l_One_Template_Link.lookup_Code,
709 x_lookup_type => l_One_Template_Link.Lookup_Type,
710 x_object_code => l_One_Template_Link.mJTF_OBJECT_CODE,
711 x_creation_date => l_current_date,
712 x_created_by => l_created_by,
713 x_last_update_date => l_current_date,
714 x_last_updated_by => l_created_by,
715 x_last_update_login => l_login);
716 Else
717 CS_TP_TEMPLATE_LINKS_PKG.UPDATE_ROW (
718 x_link_id => l_One_Template_Link.mLinkID,
719 x_template_id => P_Template_ID,
720 x_other_id => l_One_Template_Link.mOther_ID,
721 x_lookup_code => l_One_Template_Link.lookup_Code,
722 x_lookup_type => l_One_Template_Link.Lookup_Type,
723 x_object_code => l_One_Template_Link.mJTF_OBJECT_CODE,
724 x_last_update_date =>l_current_date,
725 x_last_updated_by => l_created_by,
726 x_last_update_login => l_login);
727
728
729 End If;
730 End loop;
731 End If; --P_Template_Links.count>0
732
733 -- now delete the rows that are neither updated nor inserted
734 i:=0;
735 Open Need_To_Be_Delete_Cursor(l_current_date);
736 Loop
737 Fetch Need_To_Be_Delete_Cursor Into l_Need_To_Be_Delete_List(i);
738 Exit When (Need_To_Be_Delete_Cursor%notfound);
739 i:=i+1;
740 End Loop;
741 Close Need_To_Be_Delete_Cursor;
742
743 If (l_Need_To_Be_Delete_List.COUNT > 0) Then
744 For i In l_Need_To_Be_Delete_List.FIRST..l_Need_To_Be_Delete_List.LAST Loop
745 CS_TP_TEMPLATE_LINKS_PKG.DELETE_ROW
746 ( X_LINK_ID =>l_Need_To_Be_Delete_List(i));
747 End Loop;
748 End If;
749
750 IF FND_API.To_Boolean( p_commit ) THEN
751 COMMIT WORK;
752 END IF;
753
754 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
755 p_data => x_msg_data);
756
757 EXCEPTION
758 WHEN FND_API.G_EXC_ERROR THEN
759 FND_MSG_PUB.Count_And_Get
760 (p_count => x_msg_count ,
761 p_data => x_msg_data);
762 END Update_Template_Links;
763
764
765 PROCEDURE Show_Templates (
766 p_api_version_number IN NUMBER,
767 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
768 p_commit IN VARCHAR2 := FND_API.G_FALSE,
769 P_Template_Name IN VARCHAR2,
770 P_Start_Template IN NUMBER,
771 P_End_Template IN NUMBER,
772 P_Display_Order IN VARCHAR2,
773 X_Msg_Count OUT NOCOPY NUMBER,
774 X_Msg_Data OUT NOCOPY VARCHAR2,
775 X_Return_Status OUT NOCOPY VARCHAR2,
776 X_Template_List_To_Show OUT NOCOPY Template_List,
777 X_Total_Templates OUT NOCOPY NUMBER,
778 X_Retrieved_Template_Num OUT NOCOPY NUMBER )
779 IS
780 l_api_name CONSTANT VARCHAR2(30) := 'Show_Templates';
781 l_api_version CONSTANT NUMBER := 1.0;
782 l_statement VARCHAR2(1000);
783 l_template_id NUMBER;
784 l_template_name VARCHAR2(500);
785 l_start_date_active DATE;
786 l_end_date_active DATE;
787 l_default_flag VARCHAR2(60);
788 l_short_code VARCHAR2(150);
789 l_date_format VARCHAR2(60) := FND_API.G_MISS_CHAR;
790 l_cursorid INTEGER;
791 l_last_updated_date DATE;
792 i NUMBER;
793 j NUMBER;
794 l_total_templates_notused NUMBER;
795 l_start_template NUMBER;
796 l_end_template NUMBER;
797 l_uni_question_note_flag VARCHAR2(1);
798 l_uni_question_note_type VARCHAR2(30);
799
800 BEGIN
801 -- Initialize message list if p_init_msg_list is set to TRUE.
802 IF FND_API.to_Boolean( p_init_msg_list ) THEN
803 FND_MSG_PUB.initialize;
804 END IF;
805 x_return_status := FND_API.G_RET_STS_SUCCESS;
806 l_start_template := P_Start_Template;
807 l_end_template := P_End_Template;
808
809 -- Check for null L_Start_Template and P_End_Template
810 If (l_start_template Is Null
811 Or l_start_template = FND_API.G_MISS_NUM) Then
812 l_start_template := 1;
813 End If;
814
815 -- If L_End_Template is NULL, set it to G_MISS_NUM
816 -- which should be a greater than any template number
817 If (l_end_template Is Null
818 Or l_end_template = FND_API.G_MISS_NUM) then
819 L_End_Template := FND_API.G_MISS_NUM;
820 End If;
821
822 -- validation
823 If (l_start_template > l_end_template
824 Or l_start_template <= 0 Or l_end_template <= 0) Then
825 X_Return_Status := FND_API.G_RET_STS_ERROR;
826 FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_INQUIRY_INVALID');
827 FND_MSG_PUB.Add;
828 RAISE FND_API.G_EXC_ERROR;
829 End If;
830
831 -- Construct query statement, open cursor, execute query statement, retrieve results
832 l_statement := ' SELECT
833 T.TEMPLATE_ID,
834 T.NAME,
835 T.START_DATE_ACTIVE,
836 T.END_DATE_ACTIVE,
837 T.DEFAULT_FLAG,
838 T.LAST_UPDATE_DATE,
839 T.ATTRIBUTE1,
840 T.UNI_QUESTION_NOTE_FLAG,
841 T.UNI_QUESTION_NOTE_TYPE ' ||
842 ' FROM CS_TP_TEMPLATES_VL T ';
843 If (p_template_name Is Not Null
844 and p_template_name <> FND_API.G_MISS_CHAR
845 and length(P_Template_Name) > 0) Then
846 l_statement := l_statement ||
847 ' WHERE UPPER(T.NAME) like UPPER(:v_Template_Name) ';
848 End If;
849
850 If (p_display_order Is Null
851 or p_display_order = FND_API.G_MISS_CHAR
852 or length(P_Display_Order)<= 0
853 or P_Display_Order =NORMAL) Then
854 l_statement := l_statement || ' ORDER BY T.LAST_UPDATE_DATE ';
855 Elsif (p_display_order=ALPHABATICAL) Then
856 l_statement := l_statement || ' ORDER BY T.NAME ';
857 Elsif (p_display_order = REVERSE_ALPHABATICAL) Then
858 l_statement := l_statement || ' ORDER BY T.NAME desc ';
859 Elsif (p_display_order = CRONOLOGICAL) Then
860 l_statement := l_statement || ' ORDER BY T.LAST_UPDATE_DATE ';
861 Elsif (p_display_order = REVERSE_CRONOLOGICAL) Then
862 l_statement := l_statement || ' ORDER BY T.LAST_UPDATE_DATE desc ';
863 End If;
864
865 -- Prepare dynamic sql statement
866 l_CursorID := dbms_sql.open_cursor;
867
868 dbms_sql.parse(l_CursorID, l_statement, dbms_sql.NATIVE);
869 dbms_sql.define_column(l_CursorID, 1, l_template_id);
870 dbms_sql.define_column(l_CursorID, 2, l_template_name,500);
871 dbms_sql.define_column(l_CursorID, 3, l_start_date_active);
872 dbms_sql.define_column(l_CursorID, 4, l_end_date_active);
873 dbms_sql.define_column(l_CursorID, 5, l_default_flag, 60);
874 dbms_sql.define_column(l_CursorID, 6, l_last_updated_date);
875 dbms_sql.define_column(l_CursorID, 7, l_short_code, 150);
876 dbms_sql.define_column(l_CursorID, 8, l_uni_question_note_flag,1);
877 dbms_sql.define_column(l_CursorID, 9, l_uni_question_note_type, 30);
878
879 If (p_template_name Is Not Null
880 and p_template_name <> FND_API.G_MISS_CHAR
881 and length(P_Template_Name) > 0) Then
882 dbms_sql.bind_variable(l_CursorID, 'v_Template_Name', P_Template_Name);
883 End If;
884
885 l_total_templates_notused := dbms_sql.execute(l_CursorID);
886
887 i:=1;
888 j:=0;
889 --l_date_format := get_date_format;
890 l_date_format := get_date_format_from_user_two;
891 l_default_update_format2 :=
892 get_date_format_from_user_two||' HH24:MI:SS';
893 While (dbms_sql.fetch_rows(l_CursorID) > 0) Loop
894 If (i >= l_start_template and i <= l_end_template) Then
895 dbms_sql.column_value(l_CursorID, 1, l_template_id);
896 dbms_sql.column_value(l_CursorID, 2, l_template_name);
897 dbms_sql.column_value(l_CursorID, 3, l_start_date_active);
898 dbms_sql.column_value(l_CursorID, 4, l_end_date_active);
899 dbms_sql.column_value(l_CursorID, 5, l_default_flag);
900 dbms_sql.column_value(l_CursorID, 6, l_last_updated_date);
901 dbms_sql.column_value(l_CursorID, 7, l_short_code);
902 dbms_sql.column_value(l_CursorID, 8, l_uni_question_note_flag);
903 dbms_sql.column_value(l_CursorID, 9, l_uni_question_note_type);
904
905 x_template_list_to_show(j).mTemplateID := l_template_id;
906 x_template_list_to_show(j).mTemplateName := l_template_name;
907 x_template_list_to_show(j).mShortCode := l_short_code;
908 x_template_list_to_show(j).mDefaultFlag := l_default_flag;
909 x_template_list_to_show(j).mLast_Updated_Date
910 := to_char( l_last_updated_date, l_default_update_format2);
911 x_template_list_to_show(j).mStartDate
912 := to_char (l_start_date_active, l_date_format);
913 x_template_list_to_show(j).mEndDate
914 := to_char( l_end_date_active, l_date_format);
915 x_template_list_to_show(j).mUniQuestionNoteFlag
916 := l_UNI_QUESTION_NOTE_FLAG;
917 x_template_list_to_show(j).mUniQuestionNoteType
918 := l_UNI_QUESTION_NOTE_TYPE;
919
920 j := j+1;
921 Elsif (i > L_End_Template) Then
922 null;
923 End If;
924 i := i+1;
925 End Loop;
926
927 dbms_sql.close_cursor(l_CursorID);
928 x_retrieved_template_num := j;
929 x_total_templates := i - 1;
930
931 IF FND_API.To_Boolean( p_commit ) THEN
932 COMMIT WORK;
933 END IF;
934
935 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
936 p_data => x_msg_data);
937 END Show_Templates;
938
939 PROCEDURE Show_Template (
940 p_api_version_number IN NUMBER,
941 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
942 p_commit IN VARCHAR := FND_API.G_FALSE,
943 p_template_id IN NUMBER,
944 x_msg_count OUT NOCOPY NUMBER,
945 x_msg_data OUT NOCOPY VARCHAR2,
946 x_return_status OUT NOCOPY VARCHAR2,
947 x_template_to_show OUT NOCOPY Template)
948 IS
949 l_api_name CONSTANT VARCHAR2(30) := 'Show_Template';
950 l_api_version CONSTANT NUMBER := 1.0;
951 l_date_format VARCHAR2(60) := FND_API.G_MISS_CHAR;
952 l_statement VARCHAR2(1000);
953 l_template_id NUMBER;
954 l_template_name VARCHAR2(500);
955 l_start_date_active DATE;
956 l_end_date_active DATE;
957 l_last_updated_date DATE;
958 l_default_flag VARCHAR2(60);
959 l_short_code VARCHAR2(150);
960 l_cursorid NUMBER;
961 l_total_templates_notused NUMBER;
962 l_uni_question_note_flag VARCHAR2(1);
963 l_uni_question_note_type VARCHAR2(30);
964
965 Cursor l_tp_templates_csr ( tempId NUMBER ) Is
966 Select T.TEMPLATE_ID,
967 T.NAME,
968 T.START_DATE_ACTIVE,
969 T.END_DATE_ACTIVE,
970 T.DEFAULT_FLAG,
971 T.LAST_UPDATE_DATE,
972 T.ATTRIBUTE1,
973 T.UNI_QUESTION_NOTE_FLAG,
974 T.UNI_QUESTION_NOTE_TYPE
975 From CS_TP_TEMPLATES_VL T
976 Where T.TEMPLATE_ID = tempId ;
977
978 l_tp_template_rec l_tp_templates_csr%ROWTYPE;
979
980 BEGIN
981 -- Initialize message list if p_init_msg_list is set to TRUE.
982 IF FND_API.to_Boolean( p_init_msg_list ) THEN
983 FND_MSG_PUB.initialize;
984 END IF;
985
986 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
987
988 -- Start API Body
989 -- l_date_format := get_calender_date_format;
990 l_date_format := get_date_format_from_user_two;
991 l_default_update_format2 :=
992 get_date_format_from_user_two||' HH24:MI:SS';
993
994 Open l_tp_templates_csr ( P_Template_ID );
995 Loop
996 Fetch l_tp_templates_csr Into l_tp_template_rec;
997 Exit When l_tp_templates_csr%NOTFOUND;
998 x_template_to_show.mTemplateID := l_tp_template_rec.TEMPLATE_ID;
999 x_template_to_show.mTemplateName := l_tp_template_rec.NAME;
1000 x_template_to_show.mDefaultFlag := l_tp_template_rec.DEFAULT_FLAG;
1001 x_template_to_show.mShortCode := l_tp_template_rec.attribute1;
1002 x_template_to_show.mLast_Updated_Date
1003 := to_char( l_tp_template_rec.LAST_UPDATE_DATE,
1004 l_default_update_format2);
1005 x_template_to_show.mStartDate
1006 := to_char (l_tp_template_rec.START_DATE_ACTIVE, l_date_format);
1007 x_template_to_show.mEndDate
1008 := to_char( l_tp_template_rec.END_DATE_ACTIVE, l_date_format);
1009 x_template_to_show.mUniQuestionNoteFlag
1010 := l_tp_template_rec.UNI_QUESTION_NOTE_FLAG;
1011 x_template_to_show.mUniQuestionNoteType
1012 := l_tp_template_rec.UNI_QUESTION_NOTE_TYPE;
1013 End Loop;
1014 Close l_tp_templates_csr;
1015
1016 IF FND_API.To_Boolean( p_commit ) THEN
1017 COMMIT WORK;
1018 END IF;
1019
1020 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
1021 p_data => x_msg_data);
1022
1023 EXCEPTION
1024 WHEN FND_API.G_EXC_ERROR THEN
1025 FND_MSG_PUB.Count_And_Get
1026 (p_count => x_msg_count ,
1027 p_data => x_msg_data
1028 );
1029 WHEN OTHERS THEN
1030 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1031 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1032 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
1033 END IF;
1034 FND_MSG_PUB.Count_And_Get
1035 (p_count => x_msg_count ,
1036 p_data => x_msg_data
1037 );
1038 Raise;
1039 END Show_Template;
1040
1041 PROCEDURE Show_Templates_With_Link (
1042 p_api_version_number IN NUMBER,
1043 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1044 p_commit IN VARCHAR := FND_API.G_FALSE,
1045 p_Object_Other_List IN OBJECT_OTHER_ID_PAIRS,
1046 X_Msg_Count OUT NOCOPY NUMBER,
1047 X_Msg_Data OUT NOCOPY VARCHAR2,
1048 X_Return_Status OUT NOCOPY VARCHAR2,
1049 X_Template_List OUT NOCOPY Template_List)
1050 IS
1051 l_api_name CONSTANT VARCHAR2(30) := 'Show_Templates_With_Link';
1052 l_api_version CONSTANT NUMBER := 1.0;
1053 l_date_format VARCHAR2(60) := FND_API.G_MISS_CHAR;
1054 i NUMBER;
1055 l_CursorID NUMBER;
1056 l_total_attribute_num NUMBER;
1057 Cursor_Statement VARCHAR2(1000);
1058 L_TEMPLATE_ID NUMBER;
1059 L_TEMPLATE_NAME VARCHAR2(1000);
1060 L_START_DATE DATE;
1061 L_END_DATE DATE;
1062 L_DEFAULT_FLAG VARCHAR2(100);
1063 L_LAST_UPDATE_DATE DATE;
1064 l_Short_Code VARCHAR2(150);
1065 l_Cursor_Statement VARCHAR2(2000);
1066 l_sel_template_from VARCHAR2(500);
1067 l_sel_template_select VARCHAR2(500);
1068 l_sel_template_where VARCHAR2(500);
1069 l_sel_template_stmt VARCHAR2(1500);
1070 firstOne NUMBER;
1071 profileValue VARCHAR2(1);
1072 profileOn BOOLEAN;
1073 NoProfileOn BOOLEAN;
1074 needBind JTF_NUMBER_TABLE := JTF_NUMBER_TABLE ();
1075 l_UNI_QUESTION_NOTE_FLAG VARCHAR2(1);
1076 l_UNI_QUESTION_NOTE_TYPE VARCHAR2(30);
1077 BEGIN
1078 -- Initialize message list if p_init_msg_list is set to TRUE.
1079 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1080 FND_MSG_PUB.initialize;
1081 END IF;
1082
1083 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
1084
1085 -- Start API Body
1086 l_date_format := get_date_format;
1087
1088 If (p_Object_Other_list Is Null Or p_Object_Other_list.COUNT <=0) Then
1089 Raise No_Data_Found;
1090 End If;
1091
1092 l_sel_template_from := '';
1093 l_sel_template_where := null;
1094
1095 --check to see whether the profile is on
1096 NoProfileOn := true;
1097 needBind.extend ( p_object_other_list.COUNT );
1098
1099 firstOne := null; -- by klou 04/30/02
1100
1101 For i In p_Object_Other_list.FIRST..p_Object_Other_list.last Loop
1102 profileOn := false;
1103 If ( P_Object_Other_list(i).mOBJECT_CODE = 'IBU_PRODUCT' ) Then
1104 profileValue := fnd_profile.value ( 'IBU_SR_TP_SEARCH_PRODUCT' ) ;
1105 If ( profileValue Is Not Null And profileValue = 'Y' ) Then
1106 profileOn := true;
1107 End If;
1108 Elsif ( P_Object_Other_list(i).mOBJECT_CODE = 'IBU_PLATFORM' ) Then
1109 profileValue := fnd_profile.value ( 'IBU_SR_TP_SEARCH_PLATFORM' ) ;
1110 If ( profileValue is not null And profileValue = 'Y' ) Then
1111 profileOn := true;
1112 End If;
1113 Elsif ( P_Object_Other_list(i).mOBJECT_CODE = 'IBU_LINK_URGENCY' ) Then
1114 profileValue := fnd_profile.value ( 'IBU_SR_TP_SEARCH_URGENCY' ) ;
1115 If ( profileValue Is Not Null And profileValue = 'Y' ) Then
1116 profileOn := true;
1117 End If;
1118 Elsif ( P_Object_Other_list(i).mOBJECT_CODE = 'IBU_TP_SR_TYPE' ) Then
1119 profileValue := fnd_profile.value ( 'IBU_SR_TP_SEARCH_SR_TYPE' ) ;
1120 If ( profileValue Is Not Null And profileValue = 'Y' ) Then
1121 profileOn := true;
1122 End If;
1123 Elsif ( P_Object_Other_list(i).mOBJECT_CODE = 'IBU_TP_SR_PROBCODE' ) Then
1124 profileValue := fnd_profile.value ( 'IBU_SR_TP_SEARCH_PROB_CODE' ) ;
1125 If ( profileValue Is Not Null And profileValue = 'Y' ) Then
1126 profileOn := true;
1127 End If;
1128 End If;
1129
1130 NoProfileOn := NoProfileOn and ( NOT profileOn);
1131 needBind ( i ) := 0;
1132
1133 If ( profileOn ) Then
1134 needBind ( i ) := 1;
1135 If ( l_sel_template_where Is Null ) Then
1136 firstOne := i;
1137 l_sel_template_from := 'CS_TP_TEMPLATE_LINKS A' || i;
1138 If (P_Object_Other_list(i).mOBJECT_CODE <> 'IBU_TP_SR_PROBCODE') Then
1139 l_sel_template_where := 'A' || i || '.OBJECT_CODE = :v_Object_Code'
1140 || i || ' and ' ||'A' || i
1141 || '.OTHER_ID = :v_Other_ID' || i ;
1142 Else
1143 l_sel_template_where := 'A' || i || '.OBJECT_CODE = :v_Object_Code'
1144 || i || ' and ' || 'A' || i
1145 || '.LOOKUP_CODE = :v_Other_Code' || i ;
1146 End If;
1147 Else
1148 l_sel_template_from := l_sel_template_from ||', CS_TP_TEMPLATE_LINKS A' || i;
1149
1150 If(P_Object_Other_list(i).mOBJECT_CODE <> 'IBU_TP_SR_PROBCODE') Then
1151 l_sel_template_where := l_sel_template_where || ' and ' ||'A' || i
1152 || '.OBJECT_CODE = :v_Object_Code' || i
1153 || ' and ' ||'A' || i || '.OTHER_ID = :v_Other_ID'
1154 || i || ' and ' || 'A' || i || '.TEMPLATE_ID = '
1155 || 'A' || firstOne || '.TEMPLATE_ID ';
1156 --p_Object_Other_list.FIRST
1157 Else
1158 If(P_Object_Other_list(i).mLOOKUP_CODE like 'NONE') Then
1159 l_sel_template_where := l_sel_template_where;
1160 Else
1161 l_sel_template_where := l_sel_template_where || ' and ' ||
1162 'A' || i || '.OBJECT_CODE = :v_Object_Code' || i || ' and ' ||
1163 'A' || i || '.LOOKUP_CODE = :v_Other_Code' || i || ' and ' ||
1164 'A' || i || '.TEMPLATE_ID = ' || 'A' || firstOne || '.TEMPLATE_ID ';
1165 End If;
1166 End If; -- end P_Object_Other_list IF
1167 End If; -- end l_sel_template_where IF
1168 End If; -- end profileOn IF
1169 End Loop;
1170
1171 IF NoProfileOn THEN
1172 Raise no_data_found;
1173 END IF;
1174
1175 -- added by klou 04/30/02
1176 If firstOne Is Not Null Then
1177 l_sel_template_select := 'A' || firstOne || '.template_id';
1178 End If;
1179
1180 l_sel_template_stmt := 'select ' || l_sel_template_select || ' from '
1181 || l_sel_template_from || ' where ' || l_sel_template_where ;
1182 l_Cursor_Statement :=
1183 ' select tb.template_id, ttl.name, tb.start_date_active, tb.end_date_active,'||
1184 ' tb.default_flag, tb.last_update_date, tb.attribute1,' ||
1185 ' tb.UNI_QUESTION_NOTE_FLAG, tb.UNI_QUESTION_NOTE_TYPE' ||
1186 ' from cs_tp_templates_b tb, cs_tp_templates_tl ttl ' ||
1187 ' where tB.TEMPLATE_ID = ttl.TEMPLATE_ID' ||
1188 ' and ttl.LANGUAGE = userenv(''LANG'') ' ||
1189 ' and exists ( '|| l_sel_template_stmt ||
1190 ' and a' || firstOne ||
1191 '.template_id=tb.template_id ) ' ||
1192 ' and trunc(sysdate) between trunc(nvl(tb.start_date_active, sysdate))' ||
1193 ' and trunc(nvl(tb.end_date_active, sysdate)) ';
1194
1195 l_CursorID := dbms_sql.open_cursor;
1196 dbms_sql.parse(l_CursorID, l_Cursor_Statement, dbms_sql.NATIVE);
1197
1198 For i In p_Object_Other_list.First..p_Object_Other_list.LAST Loop
1199 If ( needBind ( i) = 1 ) then
1200 If(p_Object_Other_list(i).mOBJECT_CODE <> 'IBU_TP_SR_PROBCODE') Then
1201 dbms_sql.bind_variable (l_CursorID, ':v_Other_ID' || i ,
1202 p_Object_Other_list(i).mother_id);
1203 dbms_sql.bind_variable (l_CursorID, ':v_Object_Code' || i,
1204 p_Object_Other_list(i).mobject_code);
1205 Else
1206 If(p_Object_Other_list(i).mLOOKUP_CODE <> 'NONE') Then
1207 dbms_sql.bind_variable (l_CursorID, ':v_Other_CODE' || i ,
1208 p_Object_Other_list(i).mLOOKUP_CODE);
1209 dbms_sql.bind_variable (l_CursorID, ':v_Object_Code' || i,
1210 p_Object_Other_list(i).mobject_code);
1211 End If;
1212 End If;
1213 End If; -- end needBind IF
1214 End Loop;
1215
1216 dbms_sql.define_column(l_CursorID, 1, L_TEMPLATE_ID);
1217 dbms_sql.define_column(l_CursorID, 2, L_TEMPLATE_NAME, 300);
1218 dbms_sql.define_column(l_CursorID, 3, L_START_DATE);
1219 dbms_sql.define_column(l_CursorID, 4, L_END_DATE);
1220 dbms_sql.define_column(l_CursorID, 5, L_DEFAULT_FLAG, 100);
1221 dbms_sql.define_column(l_CursorID, 6, L_LAST_UPDATE_DATE);
1222 dbms_sql.define_column(l_CursorID, 7, l_Short_Code, 150);
1223 dbms_sql.define_column(l_CursorID, 8, l_UNI_QUESTION_NOTE_FLAG, 1);
1224 dbms_sql.define_column(l_CursorID, 9, l_UNI_QUESTION_NOTE_TYPE, 30);
1225
1226 l_total_attribute_num := dbms_sql.execute(l_CursorID);
1227 i:=0;
1228 While (dbms_sql.fetch_rows(l_CursorID) > 0) Loop
1229
1230 dbms_sql.column_value(l_CursorID, 1, l_template_id);
1231 dbms_sql.column_value(l_CursorID, 2, l_template_name);
1232 dbms_sql.column_value(l_CursorID, 3, l_start_date);
1233 dbms_sql.column_value(l_CursorID, 4, l_end_date);
1234 dbms_sql.column_value(l_CursorID, 5, l_default_flag);
1235 dbms_sql.column_value(l_CursorID, 6, l_last_update_date);
1236 dbms_sql.column_value(l_CursorID, 7, l_short_code);
1237 dbms_sql.column_value(l_CursorID, 8, l_uni_question_note_flag);
1238 dbms_sql.column_value(l_CursorID, 9, l_uni_question_note_type);
1239
1240 x_template_list(i).mTemplateID := l_template_id;
1241 x_template_list(i).mTemplateName := l_template_name;
1242 x_template_list(i).mUniQuestionNoteFlag := l_uni_question_note_flag;
1243 x_template_list(i).mUniQuestionNoteType := l_uni_question_note_type;
1244 x_template_list(i).mDefaultFlag := l_default_flag;
1245 x_template_list(i).mShortCode := l_short_code;
1246 x_template_list(i).mLast_Updated_Date
1247 := to_char (l_last_update_date, l_default_last_up_date_format);
1248 x_template_list(i).mStartDate
1249 := to_char (l_start_date, l_date_format);
1250 x_template_list(i).mEndDate
1251 := to_char(l_end_date, l_date_format);
1252 i:=i+1;
1253 End Loop;
1254 dbms_sql.close_cursor(l_CursorID);
1255
1256 IF FND_API.To_Boolean( p_commit ) THEN
1257 COMMIT WORK;
1258 END IF;
1259
1260 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
1261 p_data => x_msg_data);
1262
1263 EXCEPTION
1264 WHEN FND_API.G_EXC_ERROR THEN
1265 FND_MSG_PUB.Count_And_Get
1266 (p_count => x_msg_count ,
1267 p_data => x_msg_data
1268 );
1269
1270 WHEN no_data_found THEN
1271 --x_return_status = FND_API.G_RET_STS_EXC_ERROR;
1272 FND_MSG_PUB.Count_And_Get
1273 (p_count => x_msg_count ,
1274 p_data => x_msg_data
1275 );
1276
1277 WHEN OTHERS THEN
1278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1279 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1280 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
1281 END IF;
1282 FND_MSG_PUB.Count_And_Get
1283 (p_count => x_msg_count ,
1284 p_data => x_msg_data
1285 );
1286 Raise;
1287 END Show_Templates_With_Link;
1288
1289 PROCEDURE Show_Template_Attributes(
1290 p_api_version_number IN NUMBER,
1291 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1292 p_commit IN VARCHAR := FND_API.G_FALSE,
1293 p_template_id IN NUMBER,
1294 p_jtf_object_code IN VARCHAR2,
1295 x_msg_count OUT NOCOPY NUMBER,
1296 x_msg_data OUT NOCOPY VARCHAR2,
1297 x_return_status OUT NOCOPY VARCHAR2,
1298 x_template_attributes OUT NOCOPY Template_Attribute_List )
1299 IS
1300 l_api_name CONSTANT VARCHAR2(30) := 'Show_Templates_Attributes';
1301 l_api_version CONSTANT NUMBER := 1.0;
1302 l_SELECT_ID VARCHAR2(60);
1303 l_SELECT_NAME VARCHAR2(120);
1304 l_FROM_TABLE VARCHAR2(120);
1305 l_ORDER_BY_CLAUSE VARCHAR2(120);
1306 l_where_clause VARCHAR2(120);
1307
1308 Cursor JTF_OBJ_C (v_jtf_obj_code VARCHAR2) Is
1309 Select SELECT_ID,
1310 SELECT_NAME,
1311 FROM_TABLE,
1312 WHERE_CLAUSE,
1313 ORDER_BY_CLAUSE
1314 From JTF_OBJECTS_VL
1315 Where OBJECT_CODE = v_jtf_obj_code;
1316
1317 l_statement VARCHAR2(1000);
1318 l_CursorID NUMBER;
1319 l_date_format VARCHAR2(60) := FND_API.G_MISS_CHAR;
1320 l_TEMPLATE_ATTRIBUTE_ID NUMBER;
1321 l_ATTRIBUTE_NAME VARCHAR2 (300);
1322 l_START_THRESHOLD NUMBER;
1323 l_END_THRESHOLD NUMBER;
1324 l_OTHER_ID NUMBER;
1325 l_LAST_UPDATE_DATE DATE;
1326 l_total_attribute_num NUMBER;
1327 l_defaultflag VARCHAR2(30);
1328 i NUMBER;
1329
1330 BEGIN
1331 -- Initialize message list if p_init_msg_list is set to TRUE.
1332 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1333 FND_MSG_PUB.initialize;
1334 END IF;
1335
1336 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
1337
1338 -- Start API Body
1339 l_date_format := get_date_format;
1340 Open JTF_OBJ_C (P_JTF_OBJECT_CODE);
1341 Fetch JTF_OBJ_C
1342 Into l_SELECT_ID, l_SELECT_NAME, l_FROM_TABLE, l_where_clause, l_ORDER_BY_CLAUSE;
1343 If (JTF_OBJ_C%notfound) Then
1344 Close JTF_OBJ_C;
1345 X_Return_Status := FND_API.G_RET_STS_ERROR;
1346 FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_ATTR_JTFOB');
1347 FND_MSG_PUB.Add;
1348 RAISE FND_API.G_EXC_ERROR;
1349 End If;
1350 Close JTF_OBJ_C;
1351
1352 If (l_ORDER_BY_CLAUSE Is Null
1353 Or l_ORDER_BY_CLAUSE = FND_API.G_MISS_CHAR
1354 Or length(l_ORDER_BY_CLAUSE)<=0 ) Then
1355
1356 l_statement := 'SELECT INT.TEMPLATE_ATTRIBUTE_ID, JTFO.'
1357 || l_SELECT_NAME
1358 || ', INT.START_THRESHOLD, INT.END_THRESHOLD, JTFO.'
1359 || l_SELECT_ID
1360 || ', INT.LAST_UPDATE_DATE, INT.ATTRIBUTE1 FROM (SELECT * FROM CS_TP_TEMPLATE_ATTRIBUTE ATTR, '
1361 || l_FROM_TABLE
1362 || ' INNER WHERE '
1363 || l_where_clause
1364 || ' and ATTR.OTHER_ID =INNER.'
1365 || l_SELECT_ID
1366 || ' AND ATTR.TEMPLATE_ID='
1367 || ':P_Template_ID'
1368 || ' AND ATTR.OBJECT_CODE ='
1369 || ':P_JTF_OBJECT_CODE'
1370 || ') INT, '
1371 || l_FROM_TABLE
1372 || ' JTFO WHERE INT.OTHER_ID(+) = JTFO.'
1373 || l_SELECT_ID
1374 || ' AND '
1375 || l_where_clause;
1376 Else
1377 l_statement := 'SELECT INT.TEMPLATE_ATTRIBUTE_ID, JTFO.'
1378 || l_SELECT_NAME
1379 || ', INT.START_THRESHOLD, INT.END_THRESHOLD, JTFO.'
1380 || l_SELECT_ID
1381 || ', INT.LAST_UPDATE_DATE, INT.ATTRIBUTE1 FROM (SELECT ATTR.* FROM CS_TP_TEMPLATE_ATTRIBUTE ATTR, '
1382 || l_FROM_TABLE
1383 || ' INNER WHERE '
1384 || l_where_clause
1385 || ' and ATTR.OTHER_ID =INNER.'
1386 || l_SELECT_ID
1387 || ' AND ATTR.TEMPLATE_ID='
1388 || ':P_Template_ID'
1389 || ' AND ATTR.OBJECT_CODE ='
1390 || ':P_JTF_OBJECT_CODE'
1391 || ') INT, '
1392 || l_FROM_TABLE
1393 || ' JTFO WHERE INT.OTHER_ID(+) = JTFO.'
1394 || l_SELECT_ID
1395 || ' AND '
1396 || l_where_clause
1397 || ' ORDER BY JTFO.'
1398 || l_ORDER_BY_CLAUSE;
1399 End If;
1400
1401 l_CursorID := dbms_sql.open_cursor;
1402 dbms_sql.parse(l_CursorID, l_statement, dbms_sql.NATIVE);
1403
1404 dbms_sql.bind_variable(l_CursorID, 'P_Template_ID', P_Template_ID);
1405 dbms_sql.bind_variable(l_CursorID, 'P_JTF_OBJECT_CODE', P_JTF_OBJECT_CODE);
1406
1407 dbms_sql.define_column(l_CursorID, 1, l_template_attribute_id);
1408 dbms_sql.define_column(l_CursorID, 2, l_attribute_name, 300);
1409 dbms_sql.define_column(l_CursorID, 3, l_start_threshold);
1410 dbms_sql.define_column(l_CursorID, 4, l_end_threshold);
1411 dbms_sql.define_column(l_CursorID, 5, l_other_id);
1412 dbms_sql.define_column(l_CursorID, 6, l_last_update_date);
1413 dbms_sql.define_column(l_CursorID, 7, l_defaultflag, 30);
1414
1415 l_total_attribute_num := dbms_sql.execute(l_CursorID);
1416
1417 i:=0;
1418 While (dbms_sql.fetch_rows(l_CursorID) > 0) Loop
1419 dbms_sql.column_value(l_CursorID, 1, l_template_attribute_id);
1420 dbms_sql.column_value(l_CursorID, 2, l_attribute_name);
1421 dbms_sql.column_value(l_CursorID, 3, l_start_threshold);
1422 dbms_sql.column_value(l_CursorID, 4, l_end_threshold);
1423 dbms_sql.column_value(l_CursorID, 5, l_other_id);
1424 dbms_sql.column_value(l_CursorID, 6, l_last_update_date);
1425 dbms_sql.column_value(l_CursorID, 7, l_defaultflag);
1426 x_template_attributes (i).mAttributeID := l_template_attribute_id;
1427 x_template_attributes (i).mAttributeName := l_attribute_name;
1428 x_template_attributes (i).mStartThreshold := l_start_threshold;
1429 x_template_attributes (i).mEndThreshold := l_end_threshold;
1430 x_template_attributes (i).mJTF_OBJECT_CODE := p_jtf_object_code ;
1431 x_template_attributes (i).mOther_ID := l_other_id;
1432 x_template_attributes (i).mDefaultFlag := l_defaultflag;
1433 x_template_attributes (i).mLast_Updated_Date
1434 := to_char (l_last_update_date, l_default_last_up_date_format);
1435
1436 i:= i+1;
1437 End Loop;
1438 dbms_sql.close_cursor(l_CursorID);
1439
1440 IF FND_API.To_Boolean( p_commit ) THEN
1441 COMMIT WORK;
1442 END IF;
1443
1444 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
1445 p_data => x_msg_data);
1446 EXCEPTION
1447 WHEN FND_API.G_EXC_ERROR THEN
1448 FND_MSG_PUB.Count_And_Get
1449 (p_count => x_msg_count ,
1450 p_data => x_msg_data
1451 );
1452 END Show_Template_Attributes;
1453
1454 PROCEDURE Show_Template_Links(
1455 p_api_version_number IN NUMBER,
1456 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1457 p_commit IN VARCHAR := FND_API.G_FALSE,
1458 P_Template_ID IN NUMBER,
1459 P_JTF_OBJECT_CODE IN VARCHAR2,
1460 X_Msg_Count OUT NOCOPY NUMBER,
1461 X_Msg_Data OUT NOCOPY VARCHAR2,
1462 X_Return_Status OUT NOCOPY VARCHAR2,
1463 X_Template_Links OUT NOCOPY Template_Link_List)
1464 IS
1465 l_api_name CONSTANT VARCHAR2(30) := 'Show_Templates_Attributes';
1466 l_api_version CONSTANT NUMBER := 1.0;
1467 l_SELECT_ID VARCHAR2(60);
1468 l_SELECT_NAME VARCHAR2(1000);
1469 l_SELECT_DETAIL VARCHAR2(1000);
1470 l_FROM_TABLE VARCHAR2(1000);
1471 l_ORDER_BY_CLAUSE VARCHAR2(1000);
1472 l_WHERE_CLAUSE VARCHAR2(1000);
1473
1474 Cursor JTF_OBJ_C (v_jtf_obj_code VARCHAR2) IS
1475 Select SELECT_ID,
1476 SELECT_NAME,
1477 SELECT_DETAILS,
1478 FROM_TABLE,
1479 WHERE_CLAUSE,
1480 ORDER_BY_CLAUSE
1481 From JTF_OBJECTS_VL
1482 Where OBJECT_CODE = v_jtf_obj_code;
1483
1484 l_statement VARCHAR2(1000);
1485 l_CursorID NUMBER;
1486 l_date_format VARCHAR2(60) := FND_API.G_MISS_CHAR;
1487 l_LINK_ID NUMBER;
1488 l_LINK_NAME VARCHAR2(300);
1489 l_LINK_DESC VARCHAR2(1000);
1490 l_OTHER_ID NUMBER;
1491 l_OTHER_CODE VARCHAR2(30);
1492 l_LAST_UPDATE_DATE DATE;
1493 l_total_attribute_num NUMBER;
1494 i NUMBER;
1495 BEGIN
1496 -- Initialize message list if p_init_msg_list is set to TRUE.
1497 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1498 FND_MSG_PUB.initialize;
1499 END IF;
1500
1501 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
1502
1503 -- Start API Body
1504 l_date_format := get_date_format;
1505 Open JTF_OBJ_C (P_JTF_OBJECT_CODE);
1506 Fetch JTF_OBJ_C
1507 Into l_SELECT_ID, l_SELECT_NAME, l_SELECT_DETAIL, l_FROM_TABLE,
1508 l_WHERE_CLAUSE, l_ORDER_BY_CLAUSE;
1509 If (JTF_OBJ_C%notfound) Then
1510 Close JTF_OBJ_C;
1511 X_Return_Status := FND_API.G_RET_STS_ERROR;
1512 FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_ATTR_JTFOB');
1513 FND_MSG_PUB.Add;
1514 RAISE FND_API.G_EXC_ERROR;
1515 End If;
1516 Close JTF_OBJ_C;
1517
1518 If(P_JTF_OBJECT_CODE <> 'IBU_TP_SR_PROBCODE') Then
1519 If (l_ORDER_BY_CLAUSE Is Null
1520 Or l_ORDER_BY_CLAUSE = FND_API.G_MISS_CHAR
1521 Or length(l_ORDER_BY_CLAUSE)<= 0) Then
1522
1523 l_statement := 'SELECT LINK.LINK_ID, '
1524 || l_SELECT_NAME
1525 || ', '
1526 || l_SELECT_DETAIL
1527 || ','
1528 || l_SELECT_ID
1529 || ', LINK.LAST_UPDATE_DATE FROM CS_TP_TEMPLATE_LINKS LINK, '
1530 || l_FROM_TABLE
1531 || ' WHERE '
1532 || l_WHERE_CLAUSE
1533 || ' AND LINK.OTHER_ID = '
1534 || l_SELECT_ID
1535 || ' AND LINK.TEMPLATE_ID = '
1536 || ' :P_Template_ID '
1537 || ' AND LINK.OBJECT_CODE ='''
1538 || P_JTF_OBJECT_CODE
1539 || '''';
1540 Else
1541 l_statement := 'SELECT LINK.LINK_ID, '
1542 || l_SELECT_NAME
1543 || ', '
1544 || l_SELECT_DETAIL
1545 || ', '
1546 || l_SELECT_ID
1547 || ', LINK.LAST_UPDATE_DATE FROM CS_TP_TEMPLATE_LINKS LINK, '
1548 || l_FROM_TABLE
1549 || ' WHERE '
1550 || l_WHERE_CLAUSE
1551 || ' AND LINK.OTHER_ID = '
1552 || l_SELECT_ID
1553 || ' AND LINK.TEMPLATE_ID = '
1554 || ' :P_Template_ID '
1555 || ' AND LINK.OBJECT_CODE ='''
1556 || P_JTF_OBJECT_CODE
1557 || ''' ORDER BY '
1558 || l_ORDER_BY_CLAUSE;
1559 End If;
1560 Else -- this is for the problem code case
1561 If (l_ORDER_BY_CLAUSE Is Null
1562 Or l_ORDER_BY_CLAUSE = FND_API.G_MISS_CHAR
1563 Or length(l_ORDER_BY_CLAUSE)<=0 ) Then
1564 l_statement := 'SELECT LINK.LINK_ID, '
1565 || l_SELECT_NAME
1566 || ', '
1567 || l_SELECT_DETAIL
1568 || ','
1569 || l_SELECT_ID
1570 || ', LINK.LAST_UPDATE_DATE FROM CS_TP_TEMPLATE_LINKS LINK, '
1571 || l_FROM_TABLE
1572 || ' WHERE '
1573 || l_WHERE_CLAUSE
1574 || ' AND LINK.LOOKUP_CODE = '
1575 || l_SELECT_ID
1576 || ' AND LINK.TEMPLATE_ID = '
1577 || ' :P_Template_ID '
1578 || ' AND LINK.OBJECT_CODE ='''
1579 || P_JTF_OBJECT_CODE
1580 || '''';
1581 Else
1582 l_statement := 'SELECT LINK.LINK_ID, '
1583 || l_SELECT_NAME
1584 || ', '
1585 || l_SELECT_DETAIL
1586 || ', '
1587 || l_SELECT_ID
1588 || ', LINK.LAST_UPDATE_DATE FROM CS_TP_TEMPLATE_LINKS LINK, '
1589 || l_FROM_TABLE
1590 || ' WHERE '
1591 || l_WHERE_CLAUSE
1592 || ' AND LINK.LOOKUP_CODE = '
1593 || l_SELECT_ID
1594 || ' AND LINK.TEMPLATE_ID = '
1595 || ' :P_Template_ID '
1596 || ' AND LINK.OBJECT_CODE ='''
1597 || P_JTF_OBJECT_CODE
1598 || ''' ORDER BY '
1599 || l_ORDER_BY_CLAUSE;
1600 End If;
1601 End If;
1602
1603 l_CursorID := dbms_sql.open_cursor;
1604 dbms_sql.parse(l_CursorID, l_statement, dbms_sql.NATIVE);
1605 dbms_sql.define_column(l_CursorID, 1, L_LINK_ID);
1606 dbms_sql.define_column(l_CursorID, 2, L_LINK_NAME, 300);
1607 dbms_sql.define_column(l_CursorID, 3, L_LINK_DESC, 1000);
1608
1609 If(P_JTF_OBJECT_CODE <> 'IBU_TP_SR_PROBCODE') Then
1610 dbms_sql.define_column(l_CursorID, 4, L_OTHER_ID);
1611 Else
1612 dbms_sql.define_column(l_CursorID, 4, L_OTHER_CODE, 30);
1613 End If;
1614
1615 dbms_sql.define_column(l_CursorID, 5, L_LAST_UPDATE_DATE);
1616
1617 dbms_sql.bind_variable(l_CursorID, 'P_Template_ID', P_Template_ID);
1618 l_total_attribute_num := dbms_sql.execute(l_CursorID);
1619
1620 i:=0;
1621 While (dbms_sql.fetch_rows(l_CursorID) > 0) Loop
1622 dbms_sql.column_value(l_CursorID, 1, l_link_id);
1623 dbms_sql.column_value(l_CursorID, 2, l_link_name);
1624 dbms_sql.column_value(l_CursorID, 3, l_link_desc);
1625
1626 If(P_JTF_OBJECT_CODE <> 'IBU_TP_SR_PROBCODE') Then
1627 dbms_sql.column_value(l_CursorID, 4, l_other_id);
1628 Else
1629 dbms_sql.column_value(l_CursorID, 4, l_other_code);
1630 End If;
1631
1632 dbms_sql.column_value(l_CursorID, 5, l_last_update_date);
1633 x_template_links (i).mLinkID := l_link_id;
1634 x_template_links (i).mLinkName := l_link_name;
1635 x_template_links (i).mLinkDesc := l_link_desc;
1636 x_template_links (i).mJTF_OBJECT_CODE := p_jtf_object_code;
1637
1638 If(P_JTF_OBJECT_CODE <> 'IBU_TP_SR_PROBCODE') Then
1639 x_template_links (i).mOther_ID := l_other_id;
1640 Else
1641 x_template_links (i).LOOKUP_CODE := l_other_code;
1642 End If;
1643 x_template_links (i).mLAST_UPDATED_DATE :=
1644 to_char (l_last_update_date, l_default_last_up_date_format);
1645
1646 i:= i+1;
1647 End Loop;
1648 dbms_sql.close_cursor(l_CursorID);
1649
1650 IF FND_API.To_Boolean( p_commit ) THEN
1651 COMMIT WORK;
1652 END IF;
1653
1654 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
1655 p_data => x_msg_data);
1656
1657 EXCEPTION
1658 WHEN FND_API.G_EXC_ERROR THEN
1659 FND_MSG_PUB.Count_And_Get
1660 (p_count => x_msg_count ,
1661 p_data => x_msg_data);
1662 END Show_Template_Links;
1663
1664 PROCEDURE Show_Non_Asso_Links(
1665 p_api_version_number IN NUMBER,
1666 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1667 p_commit IN VARCHAR := FND_API.G_FALSE,
1668 p_template_id IN NUMBER,
1669 p_jtf_object_code IN VARCHAR2,
1670 x_msg_count OUT NOCOPY NUMBER,
1671 x_msg_data OUT NOCOPY VARCHAR2,
1672 x_return_status OUT NOCOPY VARCHAR2,
1673 x_template_link_list OUT NOCOPY Template_Link_List)
1674 IS
1675 l_api_name CONSTANT VARCHAR2(30) := 'Show_Non_Asso_Links';
1676 l_api_version CONSTANT NUMBER := 1.0;
1677 l_select_id VARCHAR2(60);
1678 l_select_name VARCHAR2(1000);
1679 l_select_detail VARCHAR2(1000);
1680 l_from_table VARCHAR2(1000);
1681 l_order_by_clause VARCHAR2(1000);
1682 l_where_clause VARCHAR2(1000);
1683
1684 Cursor JTF_OBJ_C (v_jtf_obj_code VARCHAR2) Is
1685 Select SELECT_ID,
1686 SELECT_NAME,
1687 SELECT_DETAILS,
1688 FROM_TABLE,
1689 WHERE_CLAUSE,
1690 ORDER_BY_CLAUSE
1691 From JTF_OBJECTS_VL
1692 Where OBJECT_CODE = v_jtf_obj_code;
1693
1694 l_statement VARCHAR2(1000);
1695 l_CursorID NUMBER;
1696 l_date_format VARCHAR2(60) := FND_API.G_MISS_CHAR;
1697 l_LINK_NAME VARCHAR2(300);
1698 l_LINK_DETAIL VARCHAR2(1000);
1699 l_OTHER_ID NUMBER;
1700 l_OTHER_CODE VARCHAR2(30);
1701 l_LAST_UPDATE_DATE DATE;
1702 l_total_attribute_num NUMBER;
1703 i NUMBER;
1704
1705 BEGIN
1706 -- Initialize message list if p_init_msg_list is set to TRUE.
1707 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1708 FND_MSG_PUB.initialize;
1709 END IF;
1710
1711 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
1712
1713 -- Start API Body
1714 l_date_format := get_date_format;
1715 Open JTF_OBJ_C (P_JTF_OBJECT_CODE);
1716 Fetch JTF_OBJ_C
1717 Into l_SELECT_ID, l_SELECT_NAME,l_SELECT_DETAIL,
1718 l_FROM_TABLE,l_WHERE_CLAUSE, l_ORDER_BY_CLAUSE;
1719 If (JTF_OBJ_C%notfound) Then
1720 Close JTF_OBJ_C;
1721 X_Return_Status := FND_API.G_RET_STS_ERROR;
1722 FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_ATTR_JTFOB');
1723 FND_MSG_PUB.Add;
1724 RAISE FND_API.G_EXC_ERROR;
1725 End If;
1726 Close JTF_OBJ_C;
1727
1728 If(P_JTF_OBJECT_CODE <> 'IBU_TP_SR_PROBCODE') Then
1729 If (l_ORDER_BY_CLAUSE Is Null
1730 Or l_ORDER_BY_CLAUSE= FND_API.G_MISS_CHAR
1731 Or length(l_ORDER_BY_CLAUSE) <=0 ) Then
1732 l_statement := 'SELECT '
1733 || l_SELECT_ID
1734 || ', '
1735 || l_SELECT_NAME
1736 || ', '
1737 || l_SELECT_DETAIL
1738 || ' FROM '
1739 || l_FROM_TABLE
1740 || ' WHERE '
1741 || l_WHERE_CLAUSE
1742 || ' AND '
1743 || ' NOT EXISTS (SELECT '
1744 || '''x'''
1745 || ' FROM CS_TP_TEMPLATE_LINKS LINK WHERE LINK.TEMPLATE_ID = '
1746 || ' :P_Template_ID '
1747 || ' AND LINK.OBJECT_CODE ='''
1748 || P_JTF_OBJECT_CODE
1749 || ''' AND LINK.OTHER_ID = '
1750 || l_SELECT_ID
1751 || ' ) ';
1752 Else
1753 l_statement := 'SELECT '
1754 || l_SELECT_ID
1755 || ', '
1756 || l_SELECT_NAME
1757 || ', '
1758 || l_SELECT_DETAIL
1759 || ' FROM '
1760 || l_FROM_TABLE
1761 || ' WHERE '
1762 || l_WHERE_CLAUSE
1763 || ' AND '
1764 || ' NOT EXISTS (SELECT '
1765 || '''x'''
1766 || ' FROM CS_TP_TEMPLATE_LINKS LINK WHERE LINK.TEMPLATE_ID = '
1767 || ' :P_Template_ID '
1768 || ' AND LINK.OBJECT_CODE ='''
1769 || P_JTF_OBJECT_CODE
1770 || ''' AND LINK.OTHER_ID = '
1771 || l_SELECT_ID
1772 || ' ) ORDER BY '
1773 || l_ORDER_BY_CLAUSE;
1774 End If;
1775 Else
1776 If (l_ORDER_BY_CLAUSE Is Null
1777 Or l_ORDER_BY_CLAUSE = FND_API.G_MISS_CHAR
1778 Or length(l_ORDER_BY_CLAUSE) <=0 ) Then
1779 l_statement := 'SELECT '
1780 || l_SELECT_ID
1781 || ', '
1782 || l_SELECT_NAME
1783 || ', '
1784 || l_SELECT_DETAIL
1785 || ' FROM '
1786 || l_FROM_TABLE
1787 || ' WHERE '
1788 || l_WHERE_CLAUSE
1789 || ' AND '
1790 || l_SELECT_ID
1791 || ' NOT IN (SELECT LOOUP_CODE FROM CS_TP_TEMPLATE_LINKS LINK WHERE LINK.TEMPLATE_ID = '
1792 || ' :P_Template_ID '
1793 || ' AND LINK.OBJECT_CODE ='''
1794 || P_JTF_OBJECT_CODE
1795 || ''' ) ';
1796 Else
1797 l_statement := 'SELECT '
1798 || l_SELECT_ID
1799 || ', '
1800 || l_SELECT_NAME
1801 || ', '
1802 || l_SELECT_DETAIL
1803 || ' FROM '
1804 || l_FROM_TABLE
1805 || ' WHERE '
1806 || l_WHERE_CLAUSE
1807 || ' AND '
1808 || l_SELECT_ID
1809 || ' NOT IN (SELECT LOOKUP_CODE FROM CS_TP_TEMPLATE_LINKS LINK WHERE LINK.TEMPLATE_ID = '
1810 || ' :P_Template_ID '
1811 || ' AND LINK.OBJECT_CODE ='''
1812 || P_JTF_OBJECT_CODE
1813 || ''' ) ORDER BY '
1814 || l_ORDER_BY_CLAUSE;
1815 End If;
1816 End If;
1817
1818 l_CursorID := dbms_sql.open_cursor;
1819 dbms_sql.parse(l_CursorID, l_statement, dbms_sql.NATIVE);
1820
1821 If(P_JTF_OBJECT_CODE <> 'IBU_TP_SR_PROBCODE') Then
1822 dbms_sql.define_column(l_CursorID, 1, l_other_id);
1823 Else
1824 dbms_sql.define_column(l_CursorID, 1, l_other_code, 30);
1825 End If;
1826 dbms_sql.define_column(l_CursorID, 2, l_link_name, 300);
1827 dbms_sql.define_column(l_CursorID, 3, l_link_detail, 1000);
1828
1829 dbms_sql.bind_variable(l_CursorID, 'P_Template_ID', P_Template_ID);
1830 l_total_attribute_num := dbms_sql.execute(l_CursorID);
1831 i:=0;
1832 While (dbms_sql.fetch_rows(l_CursorID) > 0) Loop
1833 If(P_JTF_OBJECT_CODE <> 'IBU_TP_SR_PROBCODE') Then
1834 dbms_sql.column_value(l_CursorID, 1, l_other_id);
1835 Else
1836 dbms_sql.column_value(l_CursorID, 1, l_other_code);
1837 End If;
1838 dbms_sql.column_value(l_CursorID, 2, l_link_name);
1839 dbms_sql.column_value(l_CursorID, 3, l_link_detail);
1840 If(P_JTF_OBJECT_CODE <> 'IBU_TP_SR_PROBCODE') Then
1841 x_template_link_list (i).mOther_ID := l_other_id;
1842 Else
1843 x_template_link_list (i).LOOKUP_CODE := l_other_code;
1844 End If;
1845 x_template_link_list (i).mLinkName := l_link_name;
1846 x_template_link_list (i).mLinkDesc := l_link_detail;
1847
1848 i:= i+1;
1849 End Loop;
1850
1851 dbms_sql.close_cursor(l_CursorID);
1852
1853 IF FND_API.To_Boolean( p_commit ) THEN
1854 COMMIT WORK;
1855 END IF;
1856
1857 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
1858 p_data => x_msg_data);
1859
1860 EXCEPTION
1861 WHEN FND_API.G_EXC_ERROR THEN
1862 FND_MSG_PUB.Count_And_Get
1863 (p_count => x_msg_count ,
1864 p_data => x_msg_data
1865 );
1866 END Show_Non_Asso_Links;
1867
1868 PROCEDURE Show_Link_Attribute_List (
1869 p_api_version_number IN NUMBER,
1870 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1871 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1872 P_Identify IN VARCHAR2,
1873 X_Msg_Count OUT NOCOPY NUMBER,
1874 X_Msg_Data OUT NOCOPY VARCHAR2,
1875 X_Return_Status OUT NOCOPY VARCHAR2,
1876 X_IDName_Pairs OUT NOCOPY ID_NAME_PAIRS)
1877 IS
1878 l_api_name CONSTANT VARCHAR2(30) := 'Show_Link_Attribute_List';
1879 l_api_version CONSTANT NUMBER := 1.0;
1880 l_application_id CONSTANT NUMBER :=672;
1881 l_object_code VARCHAR2 (100);
1882 l_name VARCHAR2 (200);
1883 i NUMBER;
1884
1885 Cursor JTF_OBJECT_CURSOR (v_app_id NUMBER, v_object_function VARCHAR2)Is
1886 Select OBJECT_CODE,
1887 NAME
1888 From JTF_OBJECTS_VL
1889 Where APPLICATION_ID = v_app_id
1890 And OBJECT_FUNCTION like v_object_function;
1891
1892 BEGIN
1893 -- Initialize message list if p_init_msg_list is set to TRUE.
1894 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1895 FND_MSG_PUB.initialize;
1896 END IF;
1897
1898 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
1899
1900 -- Start API Body
1901 If (P_Identify like g_jtf_link) Then
1902 Open JTF_OBJECT_CURSOR (l_application_id, g_jtf_link);
1903 Elsif (P_Identify Like g_jtf_attribute) Then
1904 Open JTF_OBJECT_CURSOR (l_application_id, g_jtf_attribute);
1905 Else
1906 x_return_status := FND_API.G_RET_STS_ERROR;
1907 If (JTF_OBJECT_CURSOR%ISOPEN) Then
1908 Close JTF_OBJECT_CURSOR;
1909 End If;
1910 FND_MESSAGE.SET_NAME('CS','CS_TP_Link_Iden_INVALID');
1911 FND_MSG_PUB.Add;
1912 RAISE FND_API.G_EXC_ERROR;
1913 End If;
1914
1915 i:=0;
1916 Loop
1917 Fetch JTF_OBJECT_CURSOR Into l_OBJECT_CODE, l_NAME;
1918 Exit When (JTF_OBJECT_CURSOR%NOTFOUND);
1919 X_IDName_Pairs(i).mOBJECT_CODE := l_OBJECT_CODE;
1920 -- added by weim
1921 if(instr(l_NAME, ' ') = 1) then
1922 l_NAME := substr(l_NAME,2);
1923 end if;
1924
1925 X_IDName_Pairs(i).mNAME := l_NAME;
1926 i:=i+1;
1927 End Loop;
1928 Close JTF_OBJECT_CURSOR;
1929
1930 If (i<0) Then
1931 X_Return_Status := FND_API.G_RET_STS_ERROR;
1932 FND_MESSAGE.SET_NAME('CS','CS_TP_Link_Unseeded');
1933 FND_MSG_PUB.Add;
1934 RAISE FND_API.G_EXC_ERROR;
1935 End If;
1936
1937 IF FND_API.To_Boolean( p_commit ) THEN
1938 COMMIT WORK;
1939 END IF;
1940
1941 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
1942 p_data => x_msg_data);
1943 EXCEPTION
1944 WHEN FND_API.G_EXC_ERROR THEN
1945 FND_MSG_PUB.Count_And_Get
1946 (p_count => x_msg_count ,
1947 p_data => x_msg_data
1948 );
1949
1950 WHEN OTHERS THEN
1951 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1952 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1953 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
1954 END IF;
1955 FND_MSG_PUB.Count_And_Get
1956 (p_count => x_msg_count ,
1957 p_data => x_msg_data
1958 );
1959 Raise;
1960 END Show_Link_Attribute_List;
1961
1962 PROCEDURE Retrieve_Constants (
1963 p_api_version_number IN NUMBER,
1964 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1965 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1966 X_Msg_Count OUT NOCOPY NUMBER,
1967 X_Msg_Data OUT NOCOPY VARCHAR2,
1968 X_Return_Status OUT NOCOPY VARCHAR2,
1969 X_IDName_Pairs OUT NOCOPY ID_NAME_PAIRS)
1970 IS
1971
1972 BEGIN
1973 null;
1974 END;
1975
1976 PROCEDURE Show_Default_Template (
1977 p_api_version_number IN NUMBER,
1978 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1979 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1980 x_msg_count OUT NOCOPY NUMBER,
1981 x_msg_data OUT NOCOPY VARCHAR2,
1982 x_return_status OUT NOCOPY VARCHAR2,
1983 x_default_template OUT NOCOPY Template)
1984 IS
1985 l_api_name VARCHAR2(30) := 'Show_Default_Template';
1986 l_api_version CONSTANT NUMBER := 1.0;
1987 l_date_format VARCHAR2(60) := FND_API.G_MISS_CHAR;
1988 l_g_true CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
1989 l_statement VARCHAR2(1000);
1990 L_TEMPLATE_ID NUMBER;
1991 L_TEMPLATE_NAME VARCHAR2(500);
1992 L_START_DATE_ACTIVE DATE;
1993 L_END_DATE_ACTIVE DATE;
1994 L_LAST_UPDATED_DATE DATE;
1995 L_DEFAULT_FLAG VARCHAR2(60);
1996 l_Short_Code VARCHAR2(150);
1997 l_CursorID NUMBER;
1998 L_Total NUMBER;
1999
2000 Cursor l_tp_template_csr (dFlag VARCHAR2) IS
2001 Select T.TEMPLATE_ID,
2002 T.NAME,
2003 T.START_DATE_ACTIVE,
2004 T.END_DATE_ACTIVE,
2005 T.DEFAULT_FLAG,
2006 T.LAST_UPDATE_DATE,
2007 T.ATTRIBUTE1,
2008 T.UNI_QUESTION_NOTE_FLAG,
2009 T.UNI_QUESTION_NOTE_TYPE
2010 From CS_TP_TEMPLATES_VL T
2011 Where T.DEFAULT_FLAG = dFlag;
2012 l_tp_template_rec l_tp_template_csr%ROWTYPE;
2013 BEGIN
2014 -- Initialize message list if p_init_msg_list is set to TRUE.
2015 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2016 FND_MSG_PUB.initialize;
2017 END IF;
2018 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
2019
2020 -- Start API Body
2021 -- l_date_format := get_date_format;
2022 l_date_format := get_date_format_from_user_two;
2023 l_default_update_format2 :=
2024 get_date_format_from_user_two||' HH24:MI:SS';
2025
2026 Open l_tp_template_csr ( l_g_true );
2027 Loop
2028 Fetch l_tp_template_csr Into l_tp_template_rec;
2029 Exit When l_tp_template_csr%NOTFOUND;
2030
2031 x_default_template.mTemplateID := l_tp_template_rec.template_id;
2032 x_default_template.mTemplateName := l_tp_template_rec.name;
2033 x_default_template.mStartDate
2034 := to_char (l_tp_template_rec.start_date_active, l_date_format);
2035 x_default_template.mEndDate
2036 := to_char( l_tp_template_rec.end_date_active, l_date_format);
2037 x_default_template.mDefaultFlag := l_tp_template_rec.DEFAULT_FLAG;
2038 x_default_template.mLast_Updated_Date
2039 := to_char( l_tp_template_rec.last_update_date, l_default_update_format2);
2040 x_default_template.mShortCode := l_tp_template_rec.attribute1;
2041 x_default_template.mUniQuestionNoteFlag
2042 := l_tp_template_rec.uni_question_note_flag;
2043 x_default_template.mUniQuestionNoteType
2044 := l_tp_template_rec.uni_question_note_type;
2045
2046 End Loop;
2047 Close l_tp_template_csr;
2048
2049 IF FND_API.To_Boolean( p_commit ) THEN
2050 COMMIT WORK;
2051 END IF;
2052
2053 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
2054 p_data => x_msg_data);
2055 END Show_Default_Template;
2056
2057 PROCEDURE Show_Error_Message (
2058 p_api_version_number IN NUMBER,
2059 X_Out_Message OUT NOCOPY VARCHAR2)
2060 IS
2061 One_Message VARCHAR2(1000);
2062 i NUMBER;
2063 BEGIN
2064 i := 1;
2065 While (i <= FND_MSG_PUB.Count_Msg) Loop
2066 One_Message := FND_MSG_PUB.Get (p_msg_index => i,
2067 p_encoded => FND_API.G_FALSE);
2068 X_Out_Message := X_Out_Message || One_Message;
2069 i := i+1;
2070 End Loop;
2071
2072 END Show_Error_Message;
2073
2074
2075 PROCEDURE Copy_Template(
2076 p_api_version_number IN NUMBER,
2077 b_template_id IN NUMBER,
2078 x_template_name IN VARCHAR2,
2079 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2080 p_commit IN VARCHAR := FND_API.G_FALSE,
2081 x_msg_count OUT NOCOPY NUMBER,
2082 x_msg_data OUT NOCOPY VARCHAR2,
2083 x_return_status OUT NOCOPY VARCHAR2,
2084 x_template_id OUT NOCOPY NUMBER)
2085
2086 IS
2087
2088 l_api_name CONSTANT VARCHAR2(20) := 'Copy_Template';
2089 g_pkg_pkg_name CONSTANT VARCHAR2(20) := 'Copy_PVT';
2090 l_api_version CONSTANT NUMBER := 1.0;
2091 l_init_msg_list_true VARCHAR2(20) := FND_API.G_TRUE;
2092 l_init_msg_list_false VARCHAR2(20) := FND_API.G_FALSE;
2093 l_init_commit_true VARCHAR2(20) := FND_API.G_FALSE; --//false
2094 l_init_commit_false VARCHAR2(20) := FND_API.G_FALSE;
2095 l_template_general_info CS_TP_TEMPLATES_PVT.Template ;
2096 l_template_attr_namepairs CS_TP_TEMPLATES_PVT.ID_NAME_PAIRS;
2097 l_template_attr_namepair CS_TP_TEMPLATES_PVT.ID_NAME_PAIR;
2098 l_temp_counter NUMBER := 0;
2099 l_template_link_list CS_TP_TEMPLATES_PVT.Template_Link_List;
2100 l_medium_templink_list CS_TP_TEMPLATES_PVT.Template_Link_List;
2101 l_template_attribute_list CS_TP_TEMPLATES_PVT.Template_Attribute_list;
2102 l_template_attribute CS_TP_TEMPLATES_PVT.Template_Attribute;
2103 l_template_question_list CS_TP_QUESTIONS_PVT.Question_List;
2104 l_template_question_number NUMBER;
2105 l_templ_ques_retriv_num NUMBER;
2106 l_template_question CS_TP_QUESTIONS_PVT.Question;
2107 i NUMBER;
2108 initial_value NUMBER := 1;
2109 l_template_question_ID NUMBER;
2110 l_template_question_choice CS_TP_CHOICES_PVT.Choice;
2111 l_templ_quest_choice_list CS_TP_CHOICES_PVT.Choice_List;
2112 l_template_question_freetext CS_TP_CHOICES_PVT.FREETEXT;
2113 l_templ_quest_lookup_ID NUMBER;
2114 l_template_question_choice_ID NUMBER;
2115 l_template_question_text_ID NUMBER;
2116
2117 begin
2118 X_Template_ID := 0;
2119
2120 --/** a)Query the general information by the old given template ID
2121 CS_TP_TEMPLATES_PVT.Show_Template(
2122 p_api_version_number => p_api_version_number,
2123 p_init_msg_list => l_init_msg_list_true,
2124 p_commit => l_init_commit_true,
2125 P_Template_ID => B_Template_ID,
2126 X_Msg_Count => X_Msg_Count,
2127 X_Msg_Data => X_Msg_Data,
2128 X_Return_Status => X_Return_Status,
2129 X_Template_To_Show => l_template_general_info);
2130
2131
2132 --/** b) Generate one new template ID by these general information
2133 l_template_general_info.mTemplateName := X_Template_Name;
2134 l_template_general_info.mDefaultFlag := FND_API.G_FALSE;
2135
2136 CS_TP_TEMPLATES_PVT.Add_Template(p_api_version_number => p_api_version_number,
2137 p_init_msg_list => l_init_msg_list_true,
2138 p_commit => l_init_commit_true,
2139 P_One_Template => l_template_general_info,
2140 X_Msg_Count => X_Msg_Count,
2141 X_Msg_Data => X_Msg_Data,
2142 X_Return_Status => X_Return_Status,
2143 X_Template_ID => X_Template_ID);
2144
2145 --/** c) Query the template link information
2146 --/** c1) First need to query all the attributes lists codes
2147 CS_TP_TEMPLATES_PVT.Show_Link_Attribute_List (
2148 p_api_version_number => p_api_version_number,
2149 p_init_msg_list => l_init_msg_list_true,
2150 p_commit => l_init_commit_true,
2151 P_Identify => CS_TP_TEMPLATES_PVT.G_JTF_LINK,
2152 X_Msg_Count => X_Msg_Count,
2153 X_Msg_Data => X_Msg_Data,
2154 X_Return_Status => X_Return_Status,
2155 X_IDName_Pairs => l_template_attr_namepairs);
2156
2157 --/** c2) For each given attribute list code, we need to update and the new
2158 --/** generated template..
2159 While(l_template_attr_namepairs.EXISTS(l_temp_counter)) Loop
2160 l_template_attr_namepair.mOBJECT_CODE :=
2161 l_template_attr_namepairs(l_temp_counter).mOBJECT_CODE;
2162 l_template_attr_namepair.mNAME :=
2163 l_template_attr_namepairs(l_temp_counter).mNAME;
2164 --/** query the attr links
2165 CS_TP_TEMPLATES_PVT.Show_Template_Links(
2166 p_api_version_number => p_api_version_number,
2167 p_init_msg_list => l_init_msg_list_true,
2168 p_commit => l_init_commit_true,
2169 P_Template_ID => B_Template_ID,
2170 P_JTF_OBJECT_CODE => l_template_attr_namepair.mOBJECT_CODE,
2171 X_Msg_Count => X_Msg_Count,
2172 X_Msg_Data => X_Msg_Data,
2173 X_Return_Status => X_Return_Status,
2174 X_Template_Links => l_template_link_list);
2175
2176 If(l_template_link_list.exists(0)) Then
2177 For i IN l_template_link_list.FIRST..l_template_link_list.LAST Loop
2178 l_template_link_list(i).mLinkID := NULL;
2179 End Loop;
2180 End If;
2181 --dbms_output.put_line(' I am here four' );
2182
2183 CS_TP_TEMPLATES_PVT.Update_Template_Links (
2184 p_api_version_number => p_api_version_number,
2185 p_init_msg_list => l_init_msg_list_true,
2186 p_commit => l_init_commit_true ,
2187 P_Template_ID => X_Template_ID,
2188 P_JTF_OBJECT_CODE => l_template_attr_namepair.mOBJECT_CODE,
2189 P_Template_Links => l_template_link_list,
2190 X_Msg_Count => X_Msg_Count,
2191 X_Msg_Data => X_Msg_Data,
2192 X_Return_Status => X_Return_Status );
2193
2194 l_temp_counter := l_temp_counter + 1;
2195 End Loop;
2196
2197 l_temp_counter := 0; --// refresh this counter
2198
2199
2200 --/** e) Query the template threshvalue information
2201 --/** e1) Query the attribute lists
2202 CS_TP_TEMPLATES_PVT.Show_Link_Attribute_List (
2203 p_api_version_number => p_api_version_number,
2204 p_init_msg_list => l_init_msg_list_true,
2205 p_commit => l_init_commit_true,
2206 P_Identify => CS_TP_TEMPLATES_PVT.G_JTF_ATTRIBUTE,
2207 X_Msg_Count => X_Msg_Count,
2208 X_Msg_Data => X_Msg_Data,
2209 X_Return_Status => X_Return_Status,
2210 X_IDName_Pairs => l_template_attr_namepairs);
2211
2212
2213 --/** e2) For each attrlist, we need to query the old values and update the new
2214 --/** template.
2215 While(l_template_attr_namepairs.EXISTS(l_temp_counter)) Loop
2216 l_template_attr_namepair.mOBJECT_CODE :=
2217 l_template_attr_namepairs(l_temp_counter).mOBJECT_CODE;
2218 l_template_attr_namepair.mNAME :=
2219 l_template_attr_namepairs(l_temp_counter).mNAME;
2220
2221 --/** first query the old template value
2222 CS_TP_TEMPLATES_PVT.Show_Template_Attributes(
2223 p_api_version_number => p_api_version_number,
2224 p_init_msg_list => l_init_msg_list_true,
2225 p_commit => l_init_commit_true,
2226 P_Template_ID => B_Template_ID,
2227 P_JTF_OBJECT_CODE => l_template_attr_namepair.mOBJECT_CODE,
2228 X_Msg_Count => X_Msg_Count,
2229 X_Msg_Data => X_Msg_Data,
2230 X_Return_Status => X_Return_Status,
2231 X_Template_Attributes => l_template_attribute_list);
2232
2233 --dbms_output.put_line(' I am done here five');
2234 If(l_template_attribute_list.exists(0)) Then
2235 For i IN l_template_attribute_list.FIRST..l_template_attribute_list.LAST Loop
2236 l_template_attribute_list(i).mAttributeID := NULL;
2237 End Loop;
2238 End If;
2239 --/** update the new template value
2240 CS_TP_TEMPLATES_PVT.Update_Template_Attributes (
2241 p_api_version_number => p_api_version_number,
2242 p_init_msg_list => l_init_msg_list_true,
2243 p_commit => l_init_commit_true,
2244 P_Template_ID => X_Template_ID,
2245 P_Template_Attributes => l_template_attribute_list,
2246 X_Msg_Count => X_Msg_Count,
2247 X_Msg_Data => X_Msg_Data,
2248 X_Return_Status => X_Return_Status);
2249
2250 l_temp_counter := l_temp_counter + 1;
2251 End Loop;
2252 l_temp_counter := 0; --//** refresh this value
2253
2254 --/** g) Query the Quesions informaion by using the base template ID
2255 CS_TP_QUESTIONS_PVT.Show_Questions (
2256 p_api_version_number => p_api_version_number,
2257 p_init_msg_list => l_init_msg_list_true,
2258 p_commit => l_init_commit_true,
2259 P_Template_ID => B_Template_ID,
2260 P_Start_Question => NULL,
2261 P_End_Question => NULL,
2262 P_Display_Order => NULL,
2263 X_Msg_Count => X_Msg_Count,
2264 X_Msg_Data => X_Msg_Data,
2265 X_Return_Status => X_Return_Status,
2266 X_Question_List_To_Show => l_template_question_list,
2267 X_Total_Questions => l_template_question_number,
2268 X_Retrieved_Question_Number => l_templ_ques_retriv_num);
2269
2270
2271 --/** I) next is to add these questions to the new template
2272 If(l_template_question_list.exists(0)) Then
2273 For i IN l_template_question_list.FIRST..l_template_question_list.LAST Loop
2274 l_template_question.mQuestionName
2275 := l_template_question_list(i).mQuestionName;
2276 l_template_question.mAnswerType
2277 := l_template_question_list(i).mAnswerType;
2278 l_template_question.mMandatoryFlag
2279 := l_template_question_list(i).mMandatoryFlag;
2280 l_template_question.mScoringFlag
2281 := l_template_question_list(i).mScoringFlag;
2282 l_template_question.mLookUpID
2283 := l_template_question_list(i).mLookUpID;
2284 l_template_question.mNoteType
2285 := l_template_question_list(i).mNoteType;
2286 l_template_question.mShowOnCreationFlag
2287 := l_template_question_list(i).mShowOnCreationFlag;
2288
2289
2290 CS_TP_QUESTIONS_PVT.Add_Question(
2291 p_api_version_number => p_api_version_number,
2292 p_init_msg_list => l_init_msg_list_true,
2293 p_commit => l_init_commit_true,
2294 P_One_Question => l_template_question,
2295 p_Template_ID => X_Template_ID,
2296 X_Msg_Count => X_Msg_Count,
2297 X_Msg_Data => X_Msg_Data,
2298 X_Return_Status => X_Return_Status,
2299 X_Question_ID => l_template_question_ID);
2300
2301 Select Q.LOOKUP_ID
2302 Into l_templ_quest_lookup_ID
2303 From CS_TP_QUESTIONS_VL Q,
2304 CS_TP_LOOKUPS L,
2305 CS_TP_TEMPLATE_QUESTIONS TQ
2306 Where Q.LOOKUP_ID = L.LOOKUP_ID
2307 and TQ.QUESTION_ID = Q.QUESTION_ID
2308 and TQ.TEMPLATE_ID=X_Template_ID
2309 and Q.QUESTION_ID = l_template_question_ID;
2310
2311
2312 --//Next is to update the is new question conente according to the old
2313 --//Question content
2314 If(l_template_question_list(i).mAnswerType = 'CHOICE') Then
2315 CS_TP_CHOICES_PVT.Show_Choices (
2316 p_api_version_number => p_api_version_number,
2317 p_init_msg_list => l_init_msg_list_true,
2318 p_commit => l_init_commit_true,
2319 P_Lookup_Id => l_template_question_list(i).mLookUpID,
2320 P_Display_Order => NULL,
2321 X_Msg_Count => X_Msg_Count,
2322 X_Msg_Data => X_Msg_Data,
2323 X_Return_Status => X_Return_Status,
2324 X_Choice_List_To_Show => l_templ_quest_choice_list);
2325
2326 --//next is to add the choices
2327 While(l_templ_quest_choice_list.EXISTS(l_temp_counter)) Loop
2328 --//**l_template_question_choice.mChoiceID := l_template_question_choice_list(l_temp_counter).mChoiceID,
2329 l_template_question_choice.mChoiceName :=
2330 l_templ_quest_choice_list(l_temp_counter).mChoiceName;
2331 l_template_question_choice.mLookupID := l_templ_quest_lookup_ID;
2332 l_template_question_choice.mScore :=
2333 l_templ_quest_choice_list(l_temp_counter).mScore;
2334
2335 CS_TP_CHOICES_PVT.Add_Choice (
2336 p_api_version_number => p_api_version_number,
2337 p_init_msg_list => l_init_msg_list_true,
2338 p_commit => l_init_commit_true,
2339 p_One_Choice => l_template_question_choice,
2340 X_Msg_Count => X_Msg_Count,
2341 X_Msg_Data => X_Msg_Data,
2342 X_Return_Status => X_Return_Status,
2343 X_Choice_ID => l_template_question_choice_ID);
2344
2345 l_temp_counter := l_temp_counter + 1;
2346 End Loop;
2347 l_temp_counter := 0;
2348 End If;
2349 If(l_template_question_list(i).mAnswerType = 'FREETEXT') Then
2350
2351 CS_TP_CHOICES_PVT.Show_Freetext (
2352 p_api_version_number => p_api_version_number,
2353 p_init_msg_list => l_init_msg_list_true,
2354 p_commit => l_init_commit_true,
2355 P_Lookup_ID => l_template_question_list(i).mLookUpID,
2356 X_Msg_Count => X_Msg_Count,
2357 X_Msg_Data => X_Msg_Data,
2358 X_Return_Status => X_Return_Status,
2359 X_Freetext => l_template_question_freetext);
2360
2361 l_template_question_freetext.mLookUpID := l_templ_quest_lookup_ID;
2362
2363 CS_TP_CHOICES_PVT.Add_Freetext (
2364 p_api_version_number => p_api_version_number,
2365 p_init_msg_list => l_init_msg_list_true,
2366 p_commit => l_init_commit_true,
2367 P_One_Freetext => l_template_question_freetext,
2368 X_Msg_Count => X_Msg_Count,
2369 X_Msg_Data => X_Msg_Data,
2370 X_Return_Status => X_Return_Status,
2371 X_Freetext_ID => l_template_question_text_ID );
2372 End If;
2373
2374 End Loop;
2375 End If;
2376 COMMIT WORK;
2377 EXCEPTION
2378 WHEN OTHERS THEN
2379 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2380 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2381 FND_MSG_PUB.Add_Exc_Msg(g_pkg_pkg_name ,l_api_name);
2382 END IF;
2383 FND_MSG_PUB.Count_And_Get
2384 (p_count => X_Msg_Count ,
2385 p_data => X_Msg_Data
2386 );
2387 ROLLBACK WORK ; --//rollback all the work
2388 Raise;
2389 END Copy_Template;
2390
2391
2392 PROCEDURE Test_Template_Obsolete(
2393 p_api_version_number IN NUMBER,
2394 B_Template_ID IN NUMBER,
2395 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2396 p_commit IN VARCHAR := FND_API.G_FALSE,
2397 X_Msg_Count OUT NOCOPY NUMBER,
2398 X_Msg_Data OUT NOCOPY VARCHAR2,
2399 X_Return_Status OUT NOCOPY VARCHAR2,
2400 B_Obsolete OUT NOCOPY VARCHAR2)
2401
2402 IS
2403 l_api_name CONSTANT VARCHAR2(30) := 'test_template_obsolete';
2404 g_pkg_pkg_name CONSTANT VARCHAR2(30) := 'CS_TP_TEMPLATES_PVT';
2405 l_current_date DATE := sysdate;
2406 l_temp_date_format VARCHAR2(100); --//*:= get_calender_date_format;
2407 l_date_format VARCHAR2(100):= get_calender_date_format; --//** temp use
2408 l_template_general_info CS_TP_TEMPLATES_PVT.Template ;
2409 BEGIN
2410 l_temp_date_format := get_calender_date_format;
2411 CS_TP_TEMPLATES_PVT.Show_Template(
2412 p_api_version_number => p_api_version_number,
2413 p_init_msg_list => p_init_msg_list,
2414 p_commit => p_commit,
2415 P_Template_ID => B_Template_ID,
2416 X_Msg_Count => X_Msg_Count,
2417 X_Msg_Data => X_Msg_Data,
2418 X_Return_Status => X_Return_Status,
2419 X_Template_To_Show => l_template_general_info);
2420
2421 B_Obsolete := FND_API.G_FALSE ; --//** set default to be valide
2422
2423 l_date_format := get_date_format_from_user_two;
2424 If(l_template_general_info.mEndDate Is Not Null And
2425 (TO_DATE (l_template_general_info.mEndDate, l_date_format)) <
2426 l_current_date) Then
2427 -- (TO_DATE (l_current_date, l_date_format))) then
2428 B_Obsolete := FND_API.G_TRUE; --//** this template is obsolete;
2429 End If;
2430
2431 If (l_template_general_info.mStartDate Is Not Null And
2432 (TO_DATE (l_template_general_info.mStartDate, l_date_format)) >
2433 l_current_date) Then
2434 B_Obsolete := FND_API.G_TRUE;
2435 End If;
2436
2437 EXCEPTION
2438 WHEN OTHERS THEN
2439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2440 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2441 FND_MSG_PUB.Add_Exc_Msg(g_pkg_pkg_name ,l_api_name);
2442 END IF;
2443 FND_MSG_PUB.Count_And_Get
2444 (p_count => X_Msg_Count ,
2445 p_data => X_Msg_Data
2446 );
2447 Raise;
2448 END Test_Template_Obsolete;
2449
2450
2451 procedure Show_Template_Links_Two
2452 (
2453 p_api_version_number IN NUMBER,
2454 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2455 p_commit IN VARCHAR := FND_API.G_FALSE,
2456 P_Template_ID IN NUMBER,
2457 P_JTF_OBJECT_CODE IN VARCHAR2,
2458 p_start_link IN NUMBER,
2459 p_end_link IN NUMBER,
2460 X_Msg_Count OUT NOCOPY NUMBER,
2461 X_Msg_Data OUT NOCOPY VARCHAR2,
2462 X_Return_Status OUT NOCOPY VARCHAR2,
2463 X_Template_Links OUT NOCOPY Template_Link_List,
2464 X_Total_Link_Number OUT NOCOPY NUMBER,
2465 X_Retrieved_Link_Number OUT NOCOPY NUMBER )
2466 IS
2467 l_api_name CONSTANT VARCHAR2(30) := 'Show_Templates_Links_Two';
2468 l_api_version CONSTANT NUMBER := 1.0;
2469 l_SELECT_ID VARCHAR2(60);
2470 l_SELECT_NAME VARCHAR2(1000);
2471 l_SELECT_DETAIL VARCHAR2(1000);
2472 l_FROM_TABLE VARCHAR2(1000);
2473 l_ORDER_BY_CLAUSE VARCHAR2(1000);
2474 l_WHERE_CLAUSE VARCHAR2(1000);
2475
2476 Cursor JTF_OBJ_C (v_jtf_obj_code VARCHAR2) IS
2477 Select SELECT_ID,
2478 SELECT_NAME,
2479 SELECT_DETAILS,
2480 FROM_TABLE,
2481 WHERE_CLAUSE,
2482 ORDER_BY_CLAUSE
2483 From JTF_OBJECTS_VL
2484 Where OBJECT_CODE = v_jtf_obj_code;
2485
2486 l_statement VARCHAR2(1000);
2487 l_CursorID NUMBER;
2488 l_date_format VARCHAR2(60) := FND_API.G_MISS_CHAR;
2489 l_LINK_ID NUMBER;
2490 l_LINK_NAME VARCHAR2(300);
2491 l_LINK_DESC VARCHAR2(1000);
2492 l_OTHER_ID NUMBER;
2493 l_OTHER_CODE VARCHAR2(30);
2494 l_LAST_UPDATE_DATE DATE;
2495 l_total_attribute_num NUMBER;
2496 i NUMBER;
2497 j NUMBER;
2498 l_start_link NUMBER;
2499 l_end_link NUMBER;
2500
2501 BEGIN
2502 -- Initialize message list if p_init_msg_list is set to TRUE.
2503 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2504 FND_MSG_PUB.initialize;
2505 END IF;
2506
2507 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
2508
2509
2510 l_start_link := p_start_link;
2511 l_end_link := P_end_link;
2512
2513 -- Check for null L_Start_Link and P_End_Link
2514 If (l_start_link Is Null
2515 Or l_start_link = FND_API.G_MISS_NUM) Then
2516 l_start_link := 1;
2517 End If;
2518
2519 -- If L_End_LINK is NULL, set it to G_MISS_NUM
2520 -- which should be a greater than any template number
2521 If (l_end_link Is Null
2522 Or l_end_link = FND_API.G_MISS_NUM) then
2523 l_end_link := FND_API.G_MISS_NUM;
2524 End If;
2525
2526 -- validation
2527 If (l_start_link > l_end_link
2528 Or l_start_link <= 0 Or l_end_link <= 0) Then
2529 X_Return_Status := FND_API.G_RET_STS_ERROR;
2530 FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_LINK_INQUIRY_INVALID');
2531 FND_MSG_PUB.Add;
2532 RAISE FND_API.G_EXC_ERROR;
2533 End If;
2534
2535 -- Start API Body
2536 l_date_format := get_date_format;
2537 Open JTF_OBJ_C (P_JTF_OBJECT_CODE);
2538 Fetch JTF_OBJ_C
2539 Into l_SELECT_ID, l_SELECT_NAME, l_SELECT_DETAIL, l_FROM_TABLE,
2540 l_WHERE_CLAUSE, l_ORDER_BY_CLAUSE;
2541 If (JTF_OBJ_C%notfound) Then
2542 Close JTF_OBJ_C;
2543 X_Return_Status := FND_API.G_RET_STS_ERROR;
2544 FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_ATTR_JTFOB');
2545 FND_MSG_PUB.Add;
2546 RAISE FND_API.G_EXC_ERROR;
2547 End If;
2548 Close JTF_OBJ_C;
2549
2550 If(P_JTF_OBJECT_CODE <> 'IBU_TP_SR_PROBCODE') Then
2551 If (l_ORDER_BY_CLAUSE Is Null
2552 Or l_ORDER_BY_CLAUSE = FND_API.G_MISS_CHAR
2553 Or length(l_ORDER_BY_CLAUSE)<= 0) Then
2554
2555 l_statement := 'SELECT LINK.LINK_ID, '
2556 || l_SELECT_NAME
2557 || ', '
2558 || l_SELECT_DETAIL
2559 || ','
2560 || l_SELECT_ID
2561 || ', LINK.LAST_UPDATE_DATE FROM CS_TP_TEMPLATE_LINKS LINK, '
2562 || l_FROM_TABLE
2563 || ' WHERE '
2564 || l_WHERE_CLAUSE
2565 || ' AND LINK.OTHER_ID = '
2566 || l_SELECT_ID
2567 || ' AND LINK.TEMPLATE_ID = '
2568 || ' :P_Template_ID '
2569 || ' AND LINK.OBJECT_CODE ='''
2570 || P_JTF_OBJECT_CODE
2571 || '''';
2572 Else
2573 l_statement := 'SELECT LINK.LINK_ID, '
2574 || l_SELECT_NAME
2575 || ', '
2576 || l_SELECT_DETAIL
2577 || ', '
2578 || l_SELECT_ID
2579 || ', LINK.LAST_UPDATE_DATE FROM CS_TP_TEMPLATE_LINKS LINK, '
2580 || l_FROM_TABLE
2581 || ' WHERE '
2582 || l_WHERE_CLAUSE
2583 || ' AND LINK.OTHER_ID = '
2584 || l_SELECT_ID
2585 || ' AND LINK.TEMPLATE_ID = '
2586 || ' :P_Template_ID '
2587 || ' AND LINK.OBJECT_CODE ='''
2588 || P_JTF_OBJECT_CODE
2589 || ''' ORDER BY '
2590 || l_ORDER_BY_CLAUSE;
2591 End If;
2592 Else -- this is for the problem code case
2593 If (l_ORDER_BY_CLAUSE Is Null
2594 Or l_ORDER_BY_CLAUSE = FND_API.G_MISS_CHAR
2595 Or length(l_ORDER_BY_CLAUSE)<=0 ) Then
2596 l_statement := 'SELECT LINK.LINK_ID, '
2597 || l_SELECT_NAME
2598 || ', '
2599 || l_SELECT_DETAIL
2600 || ','
2601 || l_SELECT_ID
2602 || ', LINK.LAST_UPDATE_DATE FROM CS_TP_TEMPLATE_LINKS LINK, '
2603 || l_FROM_TABLE
2604 || ' WHERE '
2605 || l_WHERE_CLAUSE
2606 || ' AND LINK.LOOKUP_CODE = '
2607 || l_SELECT_ID
2608 || ' AND LINK.TEMPLATE_ID = '
2609 || ' :P_Template_ID '
2610 || ' AND LINK.OBJECT_CODE ='''
2611 || P_JTF_OBJECT_CODE
2612 || '''';
2613 Else
2614 l_statement := 'SELECT LINK.LINK_ID, '
2615 || l_SELECT_NAME
2616 || ', '
2617 || l_SELECT_DETAIL
2618 || ', '
2619 || l_SELECT_ID
2620 || ', LINK.LAST_UPDATE_DATE FROM CS_TP_TEMPLATE_LINKS LINK, '
2621 || l_FROM_TABLE
2622 || ' WHERE '
2623 || l_WHERE_CLAUSE
2624 || ' AND LINK.LOOKUP_CODE = '
2625 || l_SELECT_ID
2626 || ' AND LINK.TEMPLATE_ID = '
2627 || ' :P_Template_ID '
2628 || ' AND LINK.OBJECT_CODE ='''
2629 || P_JTF_OBJECT_CODE
2630 || ''' ORDER BY '
2631 || l_ORDER_BY_CLAUSE;
2632 End If;
2633 End If;
2634
2635 l_CursorID := dbms_sql.open_cursor;
2636 dbms_sql.parse(l_CursorID, l_statement, dbms_sql.NATIVE);
2637 dbms_sql.define_column(l_CursorID, 1, L_LINK_ID);
2638 dbms_sql.define_column(l_CursorID, 2, L_LINK_NAME, 300);
2639 dbms_sql.define_column(l_CursorID, 3, L_LINK_DESC, 1000);
2640
2641 If(P_JTF_OBJECT_CODE <> 'IBU_TP_SR_PROBCODE') Then
2642 dbms_sql.define_column(l_CursorID, 4, L_OTHER_ID);
2643 Else
2644 dbms_sql.define_column(l_CursorID, 4, L_OTHER_CODE, 30);
2645 End If;
2646
2647 dbms_sql.define_column(l_CursorID, 5, L_LAST_UPDATE_DATE);
2648
2649 dbms_sql.bind_variable(l_CursorID, 'P_Template_ID', P_Template_ID);
2650
2651 l_total_attribute_num := dbms_sql.execute(l_CursorID);
2652
2653 i:=1;
2654 j:=0;
2655
2656 While (dbms_sql.fetch_rows(l_CursorID) > 0) Loop
2657 If (i >= l_start_link and i <= l_end_link) Then
2658 dbms_sql.column_value(l_CursorID, 1, l_link_id);
2659 dbms_sql.column_value(l_CursorID, 2, l_link_name);
2660 dbms_sql.column_value(l_CursorID, 3, l_link_desc);
2661
2662 If(P_JTF_OBJECT_CODE <> 'IBU_TP_SR_PROBCODE') Then
2663 dbms_sql.column_value(l_CursorID, 4, l_other_id);
2664 Else
2665 dbms_sql.column_value(l_CursorID, 4, l_other_code);
2666 End If;
2667
2668 dbms_sql.column_value(l_CursorID, 5, l_last_update_date);
2669 x_template_links (j).mLinkID := l_link_id;
2670 x_template_links (j).mLinkName := l_link_name;
2671 x_template_links (j).mLinkDesc := l_link_desc;
2672 x_template_links (j).mJTF_OBJECT_CODE := p_jtf_object_code;
2673
2674 If(P_JTF_OBJECT_CODE <> 'IBU_TP_SR_PROBCODE') Then
2675 x_template_links (j).mOther_ID := l_other_id;
2676 Else
2677 x_template_links (j).LOOKUP_CODE := l_other_code;
2678 End If;
2679 x_template_links (j).mLAST_UPDATED_DATE :=
2680 to_char (l_last_update_date, l_default_last_up_date_format);
2681 j := j+1;
2682 else
2683 null;
2684 end if;
2685 i:= i+1;
2686 End Loop;
2687 dbms_sql.close_cursor(l_CursorID);
2688
2689 X_Total_Link_Number := i-1;
2690 X_Retrieved_Link_Number := j;
2691
2692 IF FND_API.To_Boolean( p_commit ) THEN
2693 COMMIT WORK;
2694 END IF;
2695
2696 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
2697 p_data => x_msg_data);
2698
2699 EXCEPTION
2700 WHEN FND_API.G_EXC_ERROR THEN
2701 FND_MSG_PUB.Count_And_Get
2702 (p_count => x_msg_count ,
2703 p_data => x_msg_data);
2704 END Show_Template_Links_Two;
2705
2706 PROCEDURE Show_Non_Asso_Links_Two(
2707 p_api_version_number IN NUMBER,
2708 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2709 p_commit IN VARCHAR := FND_API.G_FALSE,
2710 p_template_id IN NUMBER,
2711 p_jtf_object_code IN VARCHAR2,
2712 p_start_link IN NUMBER,
2713 p_end_link IN NUMBER,
2714 p_link_name IN VARCHAR2,
2715 x_msg_count OUT NOCOPY NUMBER,
2716 x_msg_data OUT NOCOPY VARCHAR2,
2717 x_return_status OUT NOCOPY VARCHAR2,
2718 x_template_link_list OUT NOCOPY Template_Link_List,
2719 X_Total_Link_Number OUT NOCOPY NUMBER,
2720 X_Retrieved_Link_Number OUT NOCOPY NUMBER)
2721
2722 IS
2723 l_api_name CONSTANT VARCHAR2(30) := 'Show_Non_Asso_Links_Two';
2724 l_api_version CONSTANT NUMBER := 1.0;
2725 l_select_id VARCHAR2(60);
2726 l_select_name VARCHAR2(1000);
2727 l_select_detail VARCHAR2(1000);
2728 l_from_table VARCHAR2(1000);
2729 l_order_by_clause VARCHAR2(1000);
2730 l_where_clause VARCHAR2(1000);
2731
2732 Cursor JTF_OBJ_C (v_jtf_obj_code VARCHAR2) Is
2733 Select SELECT_ID,
2734 SELECT_NAME,
2735 SELECT_DETAILS,
2736 FROM_TABLE,
2737 WHERE_CLAUSE,
2738 ORDER_BY_CLAUSE
2739 From JTF_OBJECTS_VL
2740 Where OBJECT_CODE = v_jtf_obj_code;
2741
2742 l_statement VARCHAR2(2000);
2743 l_CursorID NUMBER;
2744 l_date_format VARCHAR2(60) := FND_API.G_MISS_CHAR;
2745 l_LINK_NAME VARCHAR2(300);
2746 l_LINK_DETAIL VARCHAR2(1000);
2747 l_OTHER_ID NUMBER;
2748 l_OTHER_CODE VARCHAR2(30);
2749 l_LAST_UPDATE_DATE DATE;
2750 l_total_attribute_num NUMBER;
2751 i NUMBER;
2752 j NUMBER;
2753 l_start_link NUMBER;
2754 l_end_link NUMBER;
2755
2756 l_inv_org_id NUMBER; --wei ma change
2757 l_product_name_1 VARCHAR2(2);
2758 l_product_name_2 VARCHAR2(2);
2759 l_product_name_case_1 varchar2(4);
2760 l_product_name_case_2 varchar2(4);
2761 l_product_name_case_3 varchar2(4);
2762 l_product_name_case_4 varchar2(4);
2763 l_link_name_length NUMBER := 0;
2764 l_defined_order_exist boolean := false;
2765 l_statement_two varchar2(2000);
2766 l_from_number NUMBER;
2767 l_orderby_number NUMBER;
2768 l_CursorID_2 NUMBER;
2769 l_total_number NUMBER;
2770 BEGIN
2771 -- Initialize message list if p_init_msg_list is set to TRUE.
2772 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2773 FND_MSG_PUB.initialize;
2774 END IF;
2775
2776 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
2777
2778 l_start_link := p_start_link;
2779 l_end_link := P_end_link;
2780
2781 -- Check for null L_Start_Link and P_End_Link
2782 If (l_start_link Is Null
2783 Or l_start_link = FND_API.G_MISS_NUM) Then
2784 l_start_link := 1;
2785 End If;
2786
2787 -- If L_End_LINK is NULL, set it to G_MISS_NUM
2788 -- which should be a greater than any template number
2789 If (l_end_link Is Null
2790 Or l_end_link = FND_API.G_MISS_NUM) then
2791 l_end_link := FND_API.G_MISS_NUM;
2792 End If;
2793
2794 -- validation
2795 If (l_start_link > l_end_link
2796 Or l_start_link <= 0 Or l_end_link <= 0) Then
2797 X_Return_Status := FND_API.G_RET_STS_ERROR;
2798 FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_LINK_INQUIRY_INVALID');
2799 FND_MSG_PUB.Add;
2800 RAISE FND_API.G_EXC_ERROR;
2801 End If;
2802
2803 -- Start API Body
2804 l_date_format := get_date_format;
2805 Open JTF_OBJ_C (P_JTF_OBJECT_CODE);
2806 Fetch JTF_OBJ_C
2807 Into l_SELECT_ID, l_SELECT_NAME,l_SELECT_DETAIL,
2808 l_FROM_TABLE,l_WHERE_CLAUSE, l_ORDER_BY_CLAUSE;
2809 If (JTF_OBJ_C%notfound) Then
2810 Close JTF_OBJ_C;
2811 X_Return_Status := FND_API.G_RET_STS_ERROR;
2812 FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_ATTR_JTFOB');
2813 FND_MSG_PUB.Add;
2814 RAISE FND_API.G_EXC_ERROR;
2815 End If;
2816 Close JTF_OBJ_C;
2817
2818 -- here add the extra query by name function, here in order to
2819 -- minimize the change to the original code, we try to use the
2820 -- L_ORDER_BY_CLAUSE
2821
2822 If (l_ORDER_BY_CLAUSE is not Null and l_ORDER_BY_CLAUSE <>
2823 FND_API.G_MISS_CHAR and length(l_ORDER_BY_CLAUSE) > 0 ) then
2824 l_ORDER_BY_CLAUSE:= ' ORDER BY ' || l_ORDER_BY_CLAUSE ;
2825 l_defined_order_exist := true;
2826 end if;
2827
2828 -- wei ma change
2829 If (P_JTF_OBJECT_CODE<>'IBU_PRODUCT' and p_link_name Is Not Null and p_link_name <> FND_API.G_MISS_CHAR
2830 and length(p_link_name) > 0) Then
2831 If (l_ORDER_BY_CLAUSE Is Null Or
2832 l_ORDER_BY_CLAUSE= FND_API.G_MISS_CHAR
2833 Or length(l_ORDER_BY_CLAUSE) <=0 ) then
2834 l_ORDER_BY_CLAUSE:=
2835 ' and '||l_SELECT_NAME|| 'like '||':p_link_name';
2836 else
2837 l_ORDER_BY_CLAUSE:=
2838 ' and '||l_SELECT_NAME||' like '||':p_link_name'||l_ORDER_BY_CLAUSE;
2839 end if;
2840 End If;
2841
2842
2843 If (P_JTF_OBJECT_CODE = 'IBU_PRODUCT' and p_link_name Is Not Null and p_link_name <> FND_API.G_MISS_CHAR
2844 and length(p_link_name) > 0) Then
2845 if(length(p_link_name) = 1) then
2846 l_product_name_1 := substr(p_link_name, 1,1);
2847 l_product_name_case_1 := upper(l_product_name_1)||'%';
2848 l_product_name_case_2 := lower(l_product_name_1)||'%';
2849 l_link_name_length := 1;
2850 if(l_defined_order_exist) then
2851 l_ORDER_BY_CLAUSE:=
2852 ' and upper('||l_SELECT_NAME||') like '||'upper(:p_link_name)'||' and (' ||
2853 l_SELECT_NAME || ' like '||':p_product_name_case_1' || ' or '||
2854 l_SELECT_NAME || ' like '||':p_product_name_case_2' || ' ) '|| l_ORDER_BY_CLAUSE;
2855 else
2856 l_ORDER_BY_CLAUSE:=
2857 ' and upper('||l_SELECT_NAME||') like '||'upper(:p_link_name)'||' and (' ||
2858 l_SELECT_NAME || ' like '||':p_product_name_case_1' || ' or '||
2859 l_SELECT_NAME || ' like '||':p_product_name_case_2' || ' ) ';
2860 end if;
2861 else
2862 l_product_name_1 := substr(p_link_name, 1,1);
2863 l_product_name_2 := substr(p_link_name, 2, 1);
2864 l_product_name_case_1 := upper(l_product_name_1)||lower(l_product_name_2)||'%';
2865 l_product_name_case_2 := lower(l_product_name_1)||upper(l_product_name_2)||'%';
2866 l_product_name_case_3 := upper(l_product_name_1)||upper(l_product_name_2)||'%';
2867 l_product_name_case_4 := lower(l_product_name_1)||lower(l_product_name_2)||'%';
2868 l_link_name_length := 2;
2869 if(l_defined_order_exist) then
2870 l_ORDER_BY_CLAUSE:=
2871 ' and upper('||l_SELECT_NAME||') like '||'upper(:p_link_name)'||' and (' ||
2872 l_SELECT_NAME || ' like '||':p_product_name_case_1' || ' or '||
2873 l_SELECT_NAME || ' like '||':p_product_name_case_2' || ' or '||
2874 l_SELECT_NAME || ' like '||':p_product_name_case_3' || ' or '||
2875 l_SELECT_NAME || ' like '||':p_product_name_case_4' || ' ) '||l_ORDER_BY_CLAUSE;
2876 else
2877 l_ORDER_BY_CLAUSE:=
2878 ' and upper('||l_SELECT_NAME||') like '||'upper(:p_link_name)'||' and (' ||
2879 l_SELECT_NAME || ' like '||':p_product_name_case_1' || ' or '||
2880 l_SELECT_NAME || ' like '||':p_product_name_case_2' || ' or '||
2881 l_SELECT_NAME || ' like '||':p_product_name_case_3' || ' or '||
2882 l_SELECT_NAME || ' like '||':p_product_name_case_4' || ' ) ' ;
2883 end if;
2884 end if;
2885 End If;
2886
2887
2888 If(P_JTF_OBJECT_CODE <> 'IBU_TP_SR_PROBCODE') Then
2889 If (l_ORDER_BY_CLAUSE Is Null
2890 Or l_ORDER_BY_CLAUSE= FND_API.G_MISS_CHAR
2891 Or length(l_ORDER_BY_CLAUSE) <=0 ) Then
2892 l_statement := 'SELECT '
2893 || l_SELECT_ID
2894 || ', '
2895 || l_SELECT_NAME
2896 || ', '
2897 || l_SELECT_DETAIL
2898 || ' FROM '
2899 || l_FROM_TABLE
2900 || ' WHERE '
2901 || l_WHERE_CLAUSE
2902 || ' AND '
2903 || ' NOT EXISTS (SELECT '
2904 || '''x'''
2905 || ' FROM CS_TP_TEMPLATE_LINKS LINK WHERE LINK.TEMPLATE_ID = '
2906 || ':P_Template_ID'
2907 || ' AND LINK.OBJECT_CODE ='''
2908 || P_JTF_OBJECT_CODE
2909 || ''' AND LINK.OTHER_ID = '
2910 || l_SELECT_ID
2911 || ' ) ';
2912 Else
2913
2914 l_statement := 'SELECT '
2915 || l_SELECT_ID
2916 || ', '
2917 || l_SELECT_NAME
2918 || ', '
2919 || l_SELECT_DETAIL
2920 || ' FROM '
2921 || l_FROM_TABLE
2922 || ' WHERE '
2923 || l_WHERE_CLAUSE
2924 || ' AND '
2925 || ' NOT EXISTS (SELECT '
2926 || '''x'''
2927 || ' FROM CS_TP_TEMPLATE_LINKS LINK WHERE LINK.TEMPLATE_ID = '
2928 || ':P_Template_ID'
2929 || ' AND LINK.OBJECT_CODE ='''
2930 || P_JTF_OBJECT_CODE
2931 || ''' AND LINK.OTHER_ID = '
2932 || l_SELECT_ID
2933 || ' ) '
2934 || l_ORDER_BY_CLAUSE;
2935
2936 End If;
2937 Else
2938 If (l_ORDER_BY_CLAUSE Is Null
2939 Or l_ORDER_BY_CLAUSE = FND_API.G_MISS_CHAR
2940 Or length(l_ORDER_BY_CLAUSE) <=0 ) Then
2941 l_statement := 'SELECT '
2942 || l_SELECT_ID
2943 || ', '
2944 || l_SELECT_NAME
2945 || ', '
2946 || l_SELECT_DETAIL
2947 || ' FROM '
2948 || l_FROM_TABLE
2949 || ' WHERE '
2950 || l_WHERE_CLAUSE
2951 || ' AND '
2952 || ' NOT EXISTS (SELECT '
2953 || '''x'''
2954 || ' FROM CS_TP_TEMPLATE_LINKS LINK WHERE LINK.TEMPLATE_ID = '
2955 || ':P_Template_ID'
2956 || ' AND LINK.OBJECT_CODE ='''
2957 || P_JTF_OBJECT_CODE
2958 || ''' AND LINK.LOOKUP_CODE = '
2959 || l_SELECT_ID
2960 || ' ) ';
2961 Else
2962 l_statement := 'SELECT '
2963 || l_SELECT_ID
2964 || ', '
2965 || l_SELECT_NAME
2966 || ', '
2967 || l_SELECT_DETAIL
2968 || ' FROM '
2969 || l_FROM_TABLE
2970 || ' WHERE '
2971 || l_WHERE_CLAUSE
2972 || ' AND '
2973 || ' NOT EXISTS (SELECT '
2974 || '''x'''
2975 || ' FROM CS_TP_TEMPLATE_LINKS LINK WHERE LINK.TEMPLATE_ID = '
2976 || ':P_Template_ID'
2977 || ' AND LINK.OBJECT_CODE ='''
2978 || P_JTF_OBJECT_CODE
2979 || ''' AND LINK.LOOKUP_CODE = '
2980 || l_SELECT_ID
2981 || ' ) '
2982 || l_ORDER_BY_CLAUSE;
2983 End If;
2984 End If;
2985
2986 l_from_number := instr(l_statement, 'FROM');
2987 l_statement_two := 'SELECT count('
2988 || l_SELECT_ID
2989 ||') '
2990 ||substr(l_statement, l_from_number);
2991
2992 l_orderby_number := instr(l_statement_two, 'ORDER');
2993 if(l_orderby_number > 0) then
2994 l_statement_two := substr(l_statement_two, 1, l_orderby_number-1);
2995 end if;
2996
2997 l_CursorID := dbms_sql.open_cursor;
2998 l_CursorID_2 := dbms_sql.open_cursor;
2999 dbms_sql.parse(l_CursorID, l_statement, dbms_sql.NATIVE);
3000 dbms_sql.parse(l_CursorID_2, l_statement_two, dbms_sql.NATIVE);
3001
3002
3003 If(P_JTF_OBJECT_CODE <> 'IBU_TP_SR_PROBCODE') Then
3004 dbms_sql.define_column(l_CursorID, 1, l_other_id);
3005 Else
3006 dbms_sql.define_column(l_CursorID, 1, l_other_code, 30);
3007 End If;
3008 dbms_sql.define_column(l_CursorID, 2, l_link_name, 300);
3009 dbms_sql.define_column(l_CursorID, 3, l_link_detail, 1000);
3010
3011 dbms_sql.define_column(l_CursorID_2, 1, l_total_number);
3012
3013 dbms_sql.bind_variable(l_CursorID, 'P_Template_ID', P_Template_ID);
3014 dbms_sql.bind_variable(l_CursorID_2, 'P_Template_ID', P_Template_ID);
3015
3016 /* If(P_JTF_OBJECT_CODE = 'IBU_PRODUCT') Then
3017 l_inv_org_id := cs_std.get_item_valdn_orgzn_id();
3018 dbms_sql.bind_variable(l_CursorID, 'organizationID', l_inv_org_id);
3019 dbms_sql.bind_variable(l_CursorID_2, 'organizationID', l_inv_org_id);
3020 end if; */
3021
3022 If (l_ORDER_BY_CLAUSE Is not Null and
3023 l_ORDER_BY_CLAUSE <> FND_API.G_MISS_CHAR
3024 and length(l_ORDER_BY_CLAUSE) > 0 )then
3025 If (p_link_name Is Not Null and p_link_name <> FND_API.G_MISS_CHAR
3026 and length(p_link_name) > 0) Then
3027 dbms_sql.bind_variable(l_CursorID, 'p_link_name', p_link_name||'%');
3028 dbms_sql.bind_variable(l_CursorID_2, 'p_link_name', p_link_name||'%');
3029 end if ;
3030 end if ;
3031
3032 -- wei ma change
3033 if(P_JTF_OBJECT_CODE = 'IBU_PRODUCT') then
3034 if(l_link_name_length = 1) then
3035 dbms_sql.bind_variable(l_CursorID, 'p_product_name_case_1', l_product_name_case_1);
3036 dbms_sql.bind_variable(l_CursorID, 'p_product_name_case_2', l_product_name_case_2);
3037 dbms_sql.bind_variable(l_CursorID_2, 'p_product_name_case_1', l_product_name_case_1);
3038 dbms_sql.bind_variable(l_CursorID_2, 'p_product_name_case_2', l_product_name_case_2);
3039 end if;
3040 if(l_link_name_length = 2) then
3041 dbms_sql.bind_variable(l_CursorID, 'p_product_name_case_1', l_product_name_case_1);
3042 dbms_sql.bind_variable(l_CursorID, 'p_product_name_case_2', l_product_name_case_2);
3043 dbms_sql.bind_variable(l_CursorID, 'p_product_name_case_3', l_product_name_case_3);
3044 dbms_sql.bind_variable(l_CursorID, 'p_product_name_case_4', l_product_name_case_4);
3045 dbms_sql.bind_variable(l_CursorID_2, 'p_product_name_case_1', l_product_name_case_1);
3046 dbms_sql.bind_variable(l_CursorID_2, 'p_product_name_case_2', l_product_name_case_2);
3047 dbms_sql.bind_variable(l_CursorID_2, 'p_product_name_case_3', l_product_name_case_3);
3048 dbms_sql.bind_variable(l_CursorID_2, 'p_product_name_case_4', l_product_name_case_4);
3049 end if;
3050 end if;
3051
3052 l_total_attribute_num := dbms_sql.execute(l_CursorID);
3053 l_total_number := dbms_sql.execute(l_CursorID_2);
3054
3055 i:=1;
3056 j:=0;
3057
3058 While (dbms_sql.fetch_rows(l_CursorID) > 0) Loop
3059
3060 if(i > l_end_link) then
3061 exit;
3062 end if;
3063 If (i >= l_start_link and i <= l_end_link) Then
3064 If(P_JTF_OBJECT_CODE <> 'IBU_TP_SR_PROBCODE') Then
3065 dbms_sql.column_value(l_CursorID, 1, l_other_id);
3066 Else
3067 dbms_sql.column_value(l_CursorID, 1, l_other_code);
3068 End If;
3069 dbms_sql.column_value(l_CursorID, 2, l_link_name);
3070 dbms_sql.column_value(l_CursorID, 3, l_link_detail);
3071 If(P_JTF_OBJECT_CODE <> 'IBU_TP_SR_PROBCODE') Then
3072 x_template_link_list (i).mOther_ID := l_other_id;
3073 Else
3074 x_template_link_list (i).LOOKUP_CODE := l_other_code;
3075 End If;
3076 x_template_link_list (i).mLinkName := l_link_name;
3077 x_template_link_list (i).mLinkDesc := l_link_detail;
3078 j:= j+1;
3079 else
3080 null;
3081 end if;
3082
3083 i:= i+1;
3084 End Loop;
3085
3086 dbms_sql.close_cursor(l_CursorID);
3087
3088 While (dbms_sql.fetch_rows(l_CursorID_2) > 0) Loop
3089 dbms_sql.column_value(l_CursorID_2, 1, l_total_number);
3090 end loop;
3091 dbms_sql.close_cursor(l_CursorID_2);
3092
3093 --X_Total_Link_Number := i-1;'
3094 X_Total_Link_Number := l_total_number;
3095 X_Retrieved_Link_Number := j;
3096
3097 IF FND_API.To_Boolean( p_commit ) THEN
3098 COMMIT WORK;
3099 END IF;
3100
3101 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
3102 p_data => x_msg_data);
3103
3104 EXCEPTION
3105 WHEN FND_API.G_EXC_ERROR THEN
3106 FND_MSG_PUB.Count_And_Get
3107 (p_count => x_msg_count ,
3108 p_data => x_msg_data
3109 );
3110 END Show_Non_Asso_Links_Two;
3111
3112
3113
3114 PROCEDURE Delete_Template_Links (
3115 p_api_version_number IN NUMBER,
3116 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3117 p_commit IN VARCHAR := FND_API.G_FALSE,
3118 p_template_id IN NUMBER,
3119 p_jtf_object_code IN VARCHAR2,
3120 p_template_links IN Template_Link_List,
3121 x_msg_count OUT NOCOPY NUMBER,
3122 x_msg_data OUT NOCOPY VARCHAR2,
3123 x_return_status OUT NOCOPY VARCHAR2)
3124 IS
3125
3126 l_api_name CONSTANT VARCHAR2(30) := 'Update_Template_Links';
3127 l_api_version CONSTANT NUMBER := 1.0;
3128 l_JTF_OBJECT_CursorID NUMBER;
3129 l_JTF_OBJECT_CODE_count NUMBER;
3130 l_template_count_with_id NUMBER;
3131 l_One_Template_Link Template_Link;
3132 i NUMBER;
3133 CURSOR C IS
3134 Select count(*)
3135 From CS_TP_TEMPLATES_B
3136 Where template_id = P_Template_ID;
3137
3138 BEGIN
3139 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3140 FND_MSG_PUB.initialize;
3141 END IF;
3142
3143 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
3144
3145 -- perform validation, see if template id is valid
3146 Open C;
3147 Fetch C Into l_template_count_with_id;
3148 If (l_template_count_with_id <=0 ) Then
3149 Close C;
3150 X_Return_Status := FND_API.G_RET_STS_ERROR;
3151 FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_Link_TID_INV');
3152 FND_MSG_PUB.Add;
3153 Raise FND_API.G_EXC_ERROR ;
3154 End If;
3155 Close C;
3156
3157 If (P_Template_Links.COUNT >0) Then
3158 For i In P_Template_Links.FIRST..P_Template_Links.LAST Loop
3159 l_One_Template_Link := P_Template_Links (i);
3160 CS_TP_TEMPLATE_LINKS_PKG.DELETE_ROW
3161 ( X_LINK_ID =>l_One_Template_Link.mLinkID);
3162 End Loop;
3163 End If;
3164
3165 IF FND_API.To_Boolean(p_commit) THEN
3166 COMMIT WORK;
3167 END IF;
3168
3169 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
3170 p_data => x_msg_data);
3171
3172 EXCEPTION
3173 WHEN FND_API.G_EXC_ERROR THEN
3174 FND_MSG_PUB.Count_And_Get
3175 (p_count => x_msg_count ,
3176 p_data => x_msg_data);
3177 END Delete_Template_Links;
3178
3179
3180 PROCEDURE Add_Template_Links (
3181 p_api_version_number IN NUMBER,
3182 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3183 p_commit IN VARCHAR := FND_API.G_FALSE,
3184 p_template_id IN NUMBER,
3185 p_jtf_object_code IN VARCHAR2,
3186 p_template_links IN Template_Link_List,
3187 x_msg_count OUT NOCOPY NUMBER,
3188 x_msg_data OUT NOCOPY VARCHAR2,
3189 x_return_status OUT NOCOPY VARCHAR2)
3190 IS
3191 l_api_name CONSTANT VARCHAR2(30) := 'Add_Template_Links';
3192 l_api_version CONSTANT NUMBER := 1.0;
3193 l_JTF_OBJECT_CursorID NUMBER;
3194 l_JTF_OBJECT_CODE_count NUMBER;
3195 l_current_date DATE :=FND_API.G_MISS_DATE;
3196 l_created_by NUMBER :=FND_API.G_MISS_NUM;
3197 l_login NUMBER :=FND_API.G_MISS_NUM;
3198 l_Row_ID VARCHAR2(30);
3199 l_New_Link_id NUMBER;
3200 l_One_Template_Link Template_Link;
3201 l_template_count_with_id NUMBER;
3202 l_stmt VARCHAR2(100);
3203 i NUMBER;
3204 CURSOR C IS
3205 Select count(*)
3206 From CS_TP_TEMPLATES_B
3207 Where template_id = P_Template_ID;
3208
3209 BEGIN
3210 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3211 FND_MSG_PUB.initialize;
3212 END IF;
3213
3214 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
3215
3216 -- perform validation, see if template id is valid
3217 Open C;
3218 Fetch C Into l_template_count_with_id;
3219 If (l_template_count_with_id <=0 ) Then
3220 Close C;
3221 X_Return_Status := FND_API.G_RET_STS_ERROR;
3222 FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_Link_TID_INV');
3223 FND_MSG_PUB.Add;
3224 Raise FND_API.G_EXC_ERROR ;
3225 End If;
3226 Close C;
3227 l_current_date := sysdate;
3228 l_created_by := fnd_global.user_id;
3229 l_login := fnd_global.login_id;
3230
3231 --loop through each link. If the link id is present, perform an update.
3232 If (P_Template_Links.COUNT >0) Then
3233 For i In P_Template_Links.FIRST..P_Template_Links.LAST Loop
3234 l_One_Template_Link := P_Template_Links (i);
3235 -- If the link_id is passed, modify the row, otherwise insert the row.
3236
3237 If (l_One_Template_Link.mLinkID Is Null
3238 Or l_One_Template_Link.mLinkID = FND_API.G_MISS_NUM) Then
3239
3240 --Get the template id from the next available sequence number
3241 Select CS_TP_TEMPLATE_LINKS_S.nextval Into l_New_Link_id From dual;
3242 CS_TP_TEMPLATE_LINKS_PKG.INSERT_ROW (
3243 x_rowid => l_Row_ID ,
3244 x_link_id => l_New_Link_id,
3245 x_template_id => P_Template_ID,
3246 x_other_id => l_One_Template_Link.mOther_ID,
3247 x_lookup_code => l_One_Template_Link.lookup_Code,
3248 x_lookup_type => l_One_Template_Link.Lookup_Type,
3249 x_object_code => l_One_Template_Link.mJTF_OBJECT_CODE,
3250 x_creation_date => l_current_date,
3251 x_created_by => l_created_by,
3252 x_last_update_date => l_current_date,
3253 x_last_updated_by => l_created_by,
3254 x_last_update_login => l_login);
3255 Else
3256 CS_TP_TEMPLATE_LINKS_PKG.UPDATE_ROW (
3257 x_link_id => l_One_Template_Link.mLinkID,
3258 x_template_id => P_Template_ID,
3259 x_other_id => l_One_Template_Link.mOther_ID,
3260 x_lookup_code => l_One_Template_Link.lookup_Code,
3261 x_lookup_type => l_One_Template_Link.Lookup_Type,
3262 x_object_code => l_One_Template_Link.mJTF_OBJECT_CODE,
3263 x_last_update_date => l_current_date,
3264 x_last_updated_by => l_created_by,
3265 x_last_update_login => l_login);
3266
3267 End If;
3268 End loop;
3269 End If; --P_Template_Links.count>0
3270
3271 IF FND_API.To_Boolean( p_commit ) THEN
3272 COMMIT WORK;
3273 END IF;
3274
3275 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
3276 p_data => x_msg_data);
3277
3278 EXCEPTION
3279 WHEN FND_API.G_EXC_ERROR THEN
3280 FND_MSG_PUB.Count_And_Get
3281 (p_count => x_msg_count ,
3282 p_data => x_msg_data);
3283 END Add_Template_Links;
3284
3285
3286 END CS_TP_TEMPLATES_PVT;