[Home] [Help]
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
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
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;
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;
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;
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: -- ---------------------------------------------------------
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,
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,
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);
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);
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:
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
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;
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;
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;
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
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;
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;
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 ,
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,
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)
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;
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
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
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,
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;
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
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;
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:
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;
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:
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;
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;
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;
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;
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;
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
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;
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;
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;
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;
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';
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;
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;
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 ,
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:
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,
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,
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;
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:
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
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;
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;
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:
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
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;
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;
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;
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
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;
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;
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;
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
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;
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 >
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
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;
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;
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 (
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 ,
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
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 ;
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 ,
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,
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,
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;
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;
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;
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;
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;
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;
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;
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 (
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 ,
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,
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,
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;
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;
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
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
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
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
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;
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
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;
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
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:
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 ,
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,
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;
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;
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;
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 ,
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: );
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
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,
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);
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;
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:
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 ,
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: );
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: );
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
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,
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,
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;
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;
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;
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
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
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 ,
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,
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,
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;
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;
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);
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;
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
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
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: || ', '
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 ,
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;
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,
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,
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);
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;
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);
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;
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
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: || ', '
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: || ', '
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 ,
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,
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,
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;
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);
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');
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
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;
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;
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 ,
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: );
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
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,
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)
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,
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)
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);
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;
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:
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;
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 ,
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:
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,
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)
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 ;
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;
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;
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;
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,
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
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,
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)
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)) <
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)) >
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
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
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,
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,
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;
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;
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;
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
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
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
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;
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;
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;
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
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
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: || ', '
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 ,
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;
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,
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,
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);
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;
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:
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
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
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
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;
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;
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;
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:
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
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
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)||'%';
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: || ', '
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: || ', '
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||'%');
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 ;
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 ,
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: );
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,
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,
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;
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;
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;
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
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 ,
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,
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,
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;
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;
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;
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;
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;
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;
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;
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 (
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 ,
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;