[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Delete_Template_Link |
| Add_Template_Link |
| 115.43 26-FEB-2003 WMA |
| In produce Show_Link_attribute_list |
| if the first character of the arrtibute name (l_name) is space |
| remove it( 2824600) |
| 115.44 01-FEB-2004 WMA |
| change the template ID and jtf_code to be dynamically bind |
| in procedure Show_template_attributes. |
| 115.45 13-OCT-2004 WMA |
| change procedure show_template_non_asso_links_two for performance|
| tuning. |
| 115.46 18-APR-2005 WMA |
| Handle the date format issue, change the literal queries. |
| it is copied from version: 115.44.11510.4. |
+============================================================================*/
-- ---------------------------------------------------------
-- Define global variables and types
-- ---------------------------------------------------------
l_default_last_up_date_format CONSTANT VARCHAR2(30) := 'MM/DD/YYYY/SSSSS';
l_default_update_format2 VARCHAR2(100) := '';
select cs_tp_templates_s.nextval into l_template_id from dual;
CS_TP_TEMPLATES_PKG.INSERT_ROW (
x_rowid => l_rowid,
x_template_id => l_template_id,
x_default_flag => nvl(p_one_template.mDefaultFlag, 'F'),
x_start_date_active => to_date (p_one_template.mStartDate, l_date_format),
x_end_date_active => to_date (p_one_template.mEndDate, l_date_format),
x_name => p_one_template.mTemplateName,
x_description => null,
x_creation_date => l_current_date,
x_created_by => l_created_by,
x_last_update_date => l_current_date,
x_last_updated_by => l_created_by,
x_last_update_login => l_login,
x_attribute1 => p_one_template.mShortCode,
x_uni_question_note_flag => p_one_template.mUniquestionNoteFlag,
x_uni_question_note_type => p_one_template.mUniquestionNoteType);
PROCEDURE Delete_Template (
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR := FND_API.G_FALSE,
p_template_id IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30):= 'Delete_Template';
CS_TP_TEMPLATES_PKG.DELETE_ROW (P_Template_ID);
END Delete_Template;
PROCEDURE Update_Template (
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
p_commit IN VARCHAR := FND_API.G_FALSE,
p_one_template IN Template,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30):= 'Update_Template';
l_last_updated_date DATE;
l_last_updated_by NUMBER :=FND_API.G_MISS_NUM;
Select last_update_date From CS_TP_TEMPLATES_B
Where TEMPLATE_ID = p_one_template.mTemplateID;
Fetch c Into l_last_updated_date;
If (P_One_Template.mLast_Updated_Date Is Null
OR length(P_One_Template.mLast_Updated_Date) <=0
OR P_One_Template.mLast_Updated_Date = FND_API.G_MISS_CHAR) then
X_Return_Status := FND_API.G_RET_STS_ERROR;
FND_MESSAGE.SET_NAME('CS','CS_TP_LASTUPDATE_DATE_NULL');
l_default_update_format2 :=
get_date_format_from_user_two||' HH24:MI:SS';
If (l_last_updated_date >
to_date(P_One_Template.mLast_Updated_Date, l_default_update_format2)) Then
X_Return_Status := FND_API.G_RET_STS_ERROR;
FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_UPDATED');
l_last_updated_by := fnd_global.user_id;
CS_TP_TEMPLATES_PKG.Update_Row (
x_template_id => P_One_Template.mTemplateID,
x_default_flag => P_One_Template.mDefaultFlag,
x_start_date_active => TO_DATE (P_One_Template.mStartDate, l_date_format),
x_end_date_active => TO_DATE (P_One_Template.mEndDate, l_date_format),
x_name => P_One_Template.mTemplateName,
x_description => NULL,
x_last_update_date => l_current_date,
x_last_updated_by => l_last_updated_by,
x_last_update_login => l_login,
x_attribute1 => P_One_Template.mShortCode,
x_uni_question_note_flag => P_One_Template.mUniQuestionNoteFlag,
x_uni_question_note_type => P_One_Template.mUniQuestionNoteType );
END Update_Template;
PROCEDURE Update_Template_Attributes (
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR := FND_API.G_FALSE,
p_template_id IN NUMBER,
p_template_attributes IN Template_Attribute_List,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Template_Attributes';
l_last_updated_date DATE;
Select count(*) From cs_tp_templates_b;
CURSOR Last_Updated_Date_C (v_template_id NUMBER,
v_other_id NUMBER,
v_jtf_object_code VARCHAR2) IS
Select last_update_date
From cs_tp_template_attribute
where template_id = v_template_id
and other_id = v_other_id
and object_code = v_jtf_object_code;
perform update the record. Otherwise, insert into the cs_tp_template_attribute
table
*/
FOR i IN P_Template_Attributes.FIRST..P_Template_Attributes.LAST LOOP
l_One_Template_Attribute := P_Template_Attributes (i);
and (l_One_Template_Attribute.mLast_Updated_Date is NULL
OR l_One_Template_Attribute.mLast_Updated_Date =
FND_API.G_MISS_CHAR)) Then
X_Return_Status := FND_API.G_RET_STS_ERROR;
Open Last_Updated_Date_C(P_Template_ID,
l_One_Template_Attribute.mOther_ID,
l_One_Template_Attribute.mJTF_OBJECT_CODE);
Fetch Last_Updated_Date_C Into l_last_updated_date;
If (Last_Updated_Date_C%Notfound) Then
Close Last_Updated_Date_C;
FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_ATTR_UPDATED');
Elsif (l_last_updated_date >
TO_DATE (l_One_Template_Attribute.mLast_Updated_Date,
l_default_last_up_date_format ))
Then
-- row is already updated
X_Return_Status := FND_API.G_RET_STS_ERROR;
FND_MESSAGE.SET_NAME('CS','CS_TP_TEMPLATE_ATTR_UPDATED');
If (Last_Updated_Date_C%ISOPEN) Then
Close Last_Updated_Date_C;
Select CS_TP_TEMPLATE_ATTRIBUTE_S.NextVal Into l_attribute_id From dual;
CS_TP_TEMPLATE_ATTRIBUTE_PKG.INSERT_ROW (
x_rowid => l_Row_ID ,
x_template_attribute_id => l_attribute_id,
x_template_id => P_Template_ID,
x_other_id => l_One_Template_Attribute.mOther_ID,
x_object_code => l_One_Template_Attribute.mJTF_OBJECT_CODE,
x_start_threshold => l_One_Template_Attribute.mStartThreshold,
x_end_threshold => l_One_Template_Attribute.mEndThreshold,
x_attribute1 => l_One_Template_Attribute.mDefaultFlag,
x_creation_date => l_current_date,
x_created_by => l_created_by,
x_last_update_date => l_current_date,
x_last_updated_by => l_created_by,
x_last_update_login => l_login);
CS_TP_TEMPLATE_ATTRIBUTE_PKG.UPDATE_ROW (
x_template_attribute_id => l_One_Template_Attribute.mAttributeID,
x_template_id => P_Template_ID,
x_other_id => l_One_Template_Attribute.mOther_ID,
x_object_code => l_One_Template_Attribute.mJTF_OBJECT_CODE,
x_start_threshold => l_One_Template_Attribute.mStartThreshold,
x_end_threshold => l_One_Template_Attribute.mEndThreshold,
x_attribute1 => l_One_Template_Attribute.mDefaultFlag,
x_last_update_date => l_current_date,
x_last_updated_by => l_created_by,
x_last_update_login => l_login);
END Update_Template_Attributes;
PROCEDURE Update_Template_Links (
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR := FND_API.G_FALSE,
p_template_id IN NUMBER,
p_jtf_object_code IN VARCHAR2,
p_template_links IN Template_Link_List,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
TYPE l_Need_To_Be_Delete_List_type is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Template_Links';
l_Need_To_Be_Delete_List l_Need_To_Be_Delete_List_type;
Select count(*)
From CS_TP_TEMPLATES_B
Where template_id = P_Template_ID;
CURSOR Need_To_Be_Delete_Cursor (v_current_date DATE) IS
Select link_id
From CS_TP_TEMPLATE_LINKS
Where template_id = P_Template_ID
and OBJECT_CODE = P_JTF_OBJECT_CODE
and last_update_date < v_current_date ;
Select CS_TP_TEMPLATE_LINKS_S.nextval Into l_New_Link_id From dual;
CS_TP_TEMPLATE_LINKS_PKG.INSERT_ROW (
x_rowid => l_Row_ID ,
x_link_id => l_New_Link_id,
x_template_id => P_Template_ID,
x_other_id => l_One_Template_Link.mOther_ID,
x_lookup_code => l_One_Template_Link.lookup_Code,
x_lookup_type => l_One_Template_Link.Lookup_Type,
x_object_code => l_One_Template_Link.mJTF_OBJECT_CODE,
x_creation_date => l_current_date,
x_created_by => l_created_by,
x_last_update_date => l_current_date,
x_last_updated_by => l_created_by,
x_last_update_login => l_login);
CS_TP_TEMPLATE_LINKS_PKG.UPDATE_ROW (
x_link_id => l_One_Template_Link.mLinkID,
x_template_id => P_Template_ID,
x_other_id => l_One_Template_Link.mOther_ID,
x_lookup_code => l_One_Template_Link.lookup_Code,
x_lookup_type => l_One_Template_Link.Lookup_Type,
x_object_code => l_One_Template_Link.mJTF_OBJECT_CODE,
x_last_update_date =>l_current_date,
x_last_updated_by => l_created_by,
x_last_update_login => l_login);
Open Need_To_Be_Delete_Cursor(l_current_date);
Fetch Need_To_Be_Delete_Cursor Into l_Need_To_Be_Delete_List(i);
Exit When (Need_To_Be_Delete_Cursor%notfound);
Close Need_To_Be_Delete_Cursor;
If (l_Need_To_Be_Delete_List.COUNT > 0) Then
For i In l_Need_To_Be_Delete_List.FIRST..l_Need_To_Be_Delete_List.LAST Loop
CS_TP_TEMPLATE_LINKS_PKG.DELETE_ROW
( X_LINK_ID =>l_Need_To_Be_Delete_List(i));
END Update_Template_Links;
l_last_updated_date DATE;
l_statement := ' SELECT
T.TEMPLATE_ID,
T.NAME,
T.START_DATE_ACTIVE,
T.END_DATE_ACTIVE,
T.DEFAULT_FLAG,
T.LAST_UPDATE_DATE,
T.ATTRIBUTE1,
T.UNI_QUESTION_NOTE_FLAG,
T.UNI_QUESTION_NOTE_TYPE ' ||
' FROM CS_TP_TEMPLATES_VL T ';
l_statement := l_statement || ' ORDER BY T.LAST_UPDATE_DATE ';
l_statement := l_statement || ' ORDER BY T.LAST_UPDATE_DATE ';
l_statement := l_statement || ' ORDER BY T.LAST_UPDATE_DATE desc ';
dbms_sql.define_column(l_CursorID, 6, l_last_updated_date);
l_default_update_format2 :=
get_date_format_from_user_two||' HH24:MI:SS';
dbms_sql.column_value(l_CursorID, 6, l_last_updated_date);
x_template_list_to_show(j).mLast_Updated_Date
:= to_char( l_last_updated_date, l_default_update_format2);
l_last_updated_date DATE;
Select T.TEMPLATE_ID,
T.NAME,
T.START_DATE_ACTIVE,
T.END_DATE_ACTIVE,
T.DEFAULT_FLAG,
T.LAST_UPDATE_DATE,
T.ATTRIBUTE1,
T.UNI_QUESTION_NOTE_FLAG,
T.UNI_QUESTION_NOTE_TYPE
From CS_TP_TEMPLATES_VL T
Where T.TEMPLATE_ID = tempId ;
l_default_update_format2 :=
get_date_format_from_user_two||' HH24:MI:SS';
x_template_to_show.mLast_Updated_Date
:= to_char( l_tp_template_rec.LAST_UPDATE_DATE,
l_default_update_format2);
L_LAST_UPDATE_DATE DATE;
l_sel_template_select VARCHAR2(500);
l_sel_template_select := 'A' || firstOne || '.template_id';
l_sel_template_stmt := 'select ' || l_sel_template_select || ' from '
|| l_sel_template_from || ' where ' || l_sel_template_where ;
' select tb.template_id, ttl.name, tb.start_date_active, tb.end_date_active,'||
' tb.default_flag, tb.last_update_date, tb.attribute1,' ||
' tb.UNI_QUESTION_NOTE_FLAG, tb.UNI_QUESTION_NOTE_TYPE' ||
' from cs_tp_templates_b tb, cs_tp_templates_tl ttl ' ||
' where tB.TEMPLATE_ID = ttl.TEMPLATE_ID' ||
' and ttl.LANGUAGE = userenv(''LANG'') ' ||
' and exists ( '|| l_sel_template_stmt ||
' and a' || firstOne ||
'.template_id=tb.template_id ) ' ||
' and trunc(sysdate) between trunc(nvl(tb.start_date_active, sysdate))' ||
' and trunc(nvl(tb.end_date_active, sysdate)) ';
dbms_sql.define_column(l_CursorID, 6, L_LAST_UPDATE_DATE);
dbms_sql.column_value(l_CursorID, 6, l_last_update_date);
x_template_list(i).mLast_Updated_Date
:= to_char (l_last_update_date, l_default_last_up_date_format);
l_SELECT_ID VARCHAR2(60);
l_SELECT_NAME VARCHAR2(120);
Select SELECT_ID,
SELECT_NAME,
FROM_TABLE,
WHERE_CLAUSE,
ORDER_BY_CLAUSE
From JTF_OBJECTS_VL
Where OBJECT_CODE = v_jtf_obj_code;
l_LAST_UPDATE_DATE DATE;
Into l_SELECT_ID, l_SELECT_NAME, l_FROM_TABLE, l_where_clause, l_ORDER_BY_CLAUSE;
l_statement := 'SELECT INT.TEMPLATE_ATTRIBUTE_ID, JTFO.'
|| l_SELECT_NAME
|| ', INT.START_THRESHOLD, INT.END_THRESHOLD, JTFO.'
|| l_SELECT_ID
|| ', INT.LAST_UPDATE_DATE, INT.ATTRIBUTE1 FROM (SELECT * FROM CS_TP_TEMPLATE_ATTRIBUTE ATTR, '
|| l_FROM_TABLE
|| ' INNER WHERE '
|| l_where_clause
|| ' and ATTR.OTHER_ID =INNER.'
|| l_SELECT_ID
|| ' AND ATTR.TEMPLATE_ID='
|| ':P_Template_ID'
|| ' AND ATTR.OBJECT_CODE ='
|| ':P_JTF_OBJECT_CODE'
|| ') INT, '
|| l_FROM_TABLE
|| ' JTFO WHERE INT.OTHER_ID(+) = JTFO.'
|| l_SELECT_ID
|| ' AND '
|| l_where_clause;
l_statement := 'SELECT INT.TEMPLATE_ATTRIBUTE_ID, JTFO.'
|| l_SELECT_NAME
|| ', INT.START_THRESHOLD, INT.END_THRESHOLD, JTFO.'
|| l_SELECT_ID
|| ', INT.LAST_UPDATE_DATE, INT.ATTRIBUTE1 FROM (SELECT ATTR.* FROM CS_TP_TEMPLATE_ATTRIBUTE ATTR, '
|| l_FROM_TABLE
|| ' INNER WHERE '
|| l_where_clause
|| ' and ATTR.OTHER_ID =INNER.'
|| l_SELECT_ID
|| ' AND ATTR.TEMPLATE_ID='
|| ':P_Template_ID'
|| ' AND ATTR.OBJECT_CODE ='
|| ':P_JTF_OBJECT_CODE'
|| ') INT, '
|| l_FROM_TABLE
|| ' JTFO WHERE INT.OTHER_ID(+) = JTFO.'
|| l_SELECT_ID
|| ' AND '
|| l_where_clause
|| ' ORDER BY JTFO.'
|| l_ORDER_BY_CLAUSE;
dbms_sql.define_column(l_CursorID, 6, l_last_update_date);
dbms_sql.column_value(l_CursorID, 6, l_last_update_date);
x_template_attributes (i).mLast_Updated_Date
:= to_char (l_last_update_date, l_default_last_up_date_format);
l_SELECT_ID VARCHAR2(60);
l_SELECT_NAME VARCHAR2(1000);
l_SELECT_DETAIL VARCHAR2(1000);
Select SELECT_ID,
SELECT_NAME,
SELECT_DETAILS,
FROM_TABLE,
WHERE_CLAUSE,
ORDER_BY_CLAUSE
From JTF_OBJECTS_VL
Where OBJECT_CODE = v_jtf_obj_code;
l_LAST_UPDATE_DATE DATE;
Into l_SELECT_ID, l_SELECT_NAME, l_SELECT_DETAIL, l_FROM_TABLE,
l_WHERE_CLAUSE, l_ORDER_BY_CLAUSE;
l_statement := 'SELECT LINK.LINK_ID, '
|| l_SELECT_NAME
|| ', '
|| l_SELECT_DETAIL
|| ','
|| l_SELECT_ID
|| ', LINK.LAST_UPDATE_DATE FROM CS_TP_TEMPLATE_LINKS LINK, '
|| l_FROM_TABLE
|| ' WHERE '
|| l_WHERE_CLAUSE
|| ' AND LINK.OTHER_ID = '
|| l_SELECT_ID
|| ' AND LINK.TEMPLATE_ID = '
|| ' :P_Template_ID '
|| ' AND LINK.OBJECT_CODE ='''
|| P_JTF_OBJECT_CODE
|| '''';
l_statement := 'SELECT LINK.LINK_ID, '
|| l_SELECT_NAME
|| ', '
|| l_SELECT_DETAIL
|| ', '
|| l_SELECT_ID
|| ', LINK.LAST_UPDATE_DATE FROM CS_TP_TEMPLATE_LINKS LINK, '
|| l_FROM_TABLE
|| ' WHERE '
|| l_WHERE_CLAUSE
|| ' AND LINK.OTHER_ID = '
|| l_SELECT_ID
|| ' AND LINK.TEMPLATE_ID = '
|| ' :P_Template_ID '
|| ' AND LINK.OBJECT_CODE ='''
|| P_JTF_OBJECT_CODE
|| ''' ORDER BY '
|| l_ORDER_BY_CLAUSE;
l_statement := 'SELECT LINK.LINK_ID, '
|| l_SELECT_NAME
|| ', '
|| l_SELECT_DETAIL
|| ','
|| l_SELECT_ID
|| ', LINK.LAST_UPDATE_DATE FROM CS_TP_TEMPLATE_LINKS LINK, '
|| l_FROM_TABLE
|| ' WHERE '
|| l_WHERE_CLAUSE
|| ' AND LINK.LOOKUP_CODE = '
|| l_SELECT_ID
|| ' AND LINK.TEMPLATE_ID = '
|| ' :P_Template_ID '
|| ' AND LINK.OBJECT_CODE ='''
|| P_JTF_OBJECT_CODE
|| '''';
l_statement := 'SELECT LINK.LINK_ID, '
|| l_SELECT_NAME
|| ', '
|| l_SELECT_DETAIL
|| ', '
|| l_SELECT_ID
|| ', LINK.LAST_UPDATE_DATE FROM CS_TP_TEMPLATE_LINKS LINK, '
|| l_FROM_TABLE
|| ' WHERE '
|| l_WHERE_CLAUSE
|| ' AND LINK.LOOKUP_CODE = '
|| l_SELECT_ID
|| ' AND LINK.TEMPLATE_ID = '
|| ' :P_Template_ID '
|| ' AND LINK.OBJECT_CODE ='''
|| P_JTF_OBJECT_CODE
|| ''' ORDER BY '
|| l_ORDER_BY_CLAUSE;
dbms_sql.define_column(l_CursorID, 5, L_LAST_UPDATE_DATE);
dbms_sql.column_value(l_CursorID, 5, l_last_update_date);
x_template_links (i).mLAST_UPDATED_DATE :=
to_char (l_last_update_date, l_default_last_up_date_format);
l_select_id VARCHAR2(60);
l_select_name VARCHAR2(1000);
l_select_detail VARCHAR2(1000);
Select SELECT_ID,
SELECT_NAME,
SELECT_DETAILS,
FROM_TABLE,
WHERE_CLAUSE,
ORDER_BY_CLAUSE
From JTF_OBJECTS_VL
Where OBJECT_CODE = v_jtf_obj_code;
l_LAST_UPDATE_DATE DATE;
Into l_SELECT_ID, l_SELECT_NAME,l_SELECT_DETAIL,
l_FROM_TABLE,l_WHERE_CLAUSE, l_ORDER_BY_CLAUSE;
l_statement := 'SELECT '
|| l_SELECT_ID
|| ', '
|| l_SELECT_NAME
|| ', '
|| l_SELECT_DETAIL
|| ' FROM '
|| l_FROM_TABLE
|| ' WHERE '
|| l_WHERE_CLAUSE
|| ' AND '
|| ' NOT EXISTS (SELECT '
|| '''x'''
|| ' FROM CS_TP_TEMPLATE_LINKS LINK WHERE LINK.TEMPLATE_ID = '
|| ' :P_Template_ID '
|| ' AND LINK.OBJECT_CODE ='''
|| P_JTF_OBJECT_CODE
|| ''' AND LINK.OTHER_ID = '
|| l_SELECT_ID
|| ' ) ';
l_statement := 'SELECT '
|| l_SELECT_ID
|| ', '
|| l_SELECT_NAME
|| ', '
|| l_SELECT_DETAIL
|| ' FROM '
|| l_FROM_TABLE
|| ' WHERE '
|| l_WHERE_CLAUSE
|| ' AND '
|| ' NOT EXISTS (SELECT '
|| '''x'''
|| ' FROM CS_TP_TEMPLATE_LINKS LINK WHERE LINK.TEMPLATE_ID = '
|| ' :P_Template_ID '
|| ' AND LINK.OBJECT_CODE ='''
|| P_JTF_OBJECT_CODE
|| ''' AND LINK.OTHER_ID = '
|| l_SELECT_ID
|| ' ) ORDER BY '
|| l_ORDER_BY_CLAUSE;
l_statement := 'SELECT '
|| l_SELECT_ID
|| ', '
|| l_SELECT_NAME
|| ', '
|| l_SELECT_DETAIL
|| ' FROM '
|| l_FROM_TABLE
|| ' WHERE '
|| l_WHERE_CLAUSE
|| ' AND '
|| l_SELECT_ID
|| ' NOT IN (SELECT LOOUP_CODE FROM CS_TP_TEMPLATE_LINKS LINK WHERE LINK.TEMPLATE_ID = '
|| ' :P_Template_ID '
|| ' AND LINK.OBJECT_CODE ='''
|| P_JTF_OBJECT_CODE
|| ''' ) ';
l_statement := 'SELECT '
|| l_SELECT_ID
|| ', '
|| l_SELECT_NAME
|| ', '
|| l_SELECT_DETAIL
|| ' FROM '
|| l_FROM_TABLE
|| ' WHERE '
|| l_WHERE_CLAUSE
|| ' AND '
|| l_SELECT_ID
|| ' NOT IN (SELECT LOOKUP_CODE FROM CS_TP_TEMPLATE_LINKS LINK WHERE LINK.TEMPLATE_ID = '
|| ' :P_Template_ID '
|| ' AND LINK.OBJECT_CODE ='''
|| P_JTF_OBJECT_CODE
|| ''' ) ORDER BY '
|| l_ORDER_BY_CLAUSE;
Select OBJECT_CODE,
NAME
From JTF_OBJECTS_VL
Where APPLICATION_ID = v_app_id
And OBJECT_FUNCTION like v_object_function;
L_LAST_UPDATED_DATE DATE;
Select T.TEMPLATE_ID,
T.NAME,
T.START_DATE_ACTIVE,
T.END_DATE_ACTIVE,
T.DEFAULT_FLAG,
T.LAST_UPDATE_DATE,
T.ATTRIBUTE1,
T.UNI_QUESTION_NOTE_FLAG,
T.UNI_QUESTION_NOTE_TYPE
From CS_TP_TEMPLATES_VL T
Where T.DEFAULT_FLAG = dFlag;
l_default_update_format2 :=
get_date_format_from_user_two||' HH24:MI:SS';
x_default_template.mLast_Updated_Date
:= to_char( l_tp_template_rec.last_update_date, l_default_update_format2);
CS_TP_TEMPLATES_PVT.Update_Template_Links (
p_api_version_number => p_api_version_number,
p_init_msg_list => l_init_msg_list_true,
p_commit => l_init_commit_true ,
P_Template_ID => X_Template_ID,
P_JTF_OBJECT_CODE => l_template_attr_namepair.mOBJECT_CODE,
P_Template_Links => l_template_link_list,
X_Msg_Count => X_Msg_Count,
X_Msg_Data => X_Msg_Data,
X_Return_Status => X_Return_Status );
CS_TP_TEMPLATES_PVT.Update_Template_Attributes (
p_api_version_number => p_api_version_number,
p_init_msg_list => l_init_msg_list_true,
p_commit => l_init_commit_true,
P_Template_ID => X_Template_ID,
P_Template_Attributes => l_template_attribute_list,
X_Msg_Count => X_Msg_Count,
X_Msg_Data => X_Msg_Data,
X_Return_Status => X_Return_Status);
Select Q.LOOKUP_ID
Into l_templ_quest_lookup_ID
From CS_TP_QUESTIONS_VL Q,
CS_TP_LOOKUPS L,
CS_TP_TEMPLATE_QUESTIONS TQ
Where Q.LOOKUP_ID = L.LOOKUP_ID
and TQ.QUESTION_ID = Q.QUESTION_ID
and TQ.TEMPLATE_ID=X_Template_ID
and Q.QUESTION_ID = l_template_question_ID;
l_SELECT_ID VARCHAR2(60);
l_SELECT_NAME VARCHAR2(1000);
l_SELECT_DETAIL VARCHAR2(1000);
Select SELECT_ID,
SELECT_NAME,
SELECT_DETAILS,
FROM_TABLE,
WHERE_CLAUSE,
ORDER_BY_CLAUSE
From JTF_OBJECTS_VL
Where OBJECT_CODE = v_jtf_obj_code;
l_LAST_UPDATE_DATE DATE;
Into l_SELECT_ID, l_SELECT_NAME, l_SELECT_DETAIL, l_FROM_TABLE,
l_WHERE_CLAUSE, l_ORDER_BY_CLAUSE;
l_statement := 'SELECT LINK.LINK_ID, '
|| l_SELECT_NAME
|| ', '
|| l_SELECT_DETAIL
|| ','
|| l_SELECT_ID
|| ', LINK.LAST_UPDATE_DATE FROM CS_TP_TEMPLATE_LINKS LINK, '
|| l_FROM_TABLE
|| ' WHERE '
|| l_WHERE_CLAUSE
|| ' AND LINK.OTHER_ID = '
|| l_SELECT_ID
|| ' AND LINK.TEMPLATE_ID = '
|| ' :P_Template_ID '
|| ' AND LINK.OBJECT_CODE ='''
|| P_JTF_OBJECT_CODE
|| '''';
l_statement := 'SELECT LINK.LINK_ID, '
|| l_SELECT_NAME
|| ', '
|| l_SELECT_DETAIL
|| ', '
|| l_SELECT_ID
|| ', LINK.LAST_UPDATE_DATE FROM CS_TP_TEMPLATE_LINKS LINK, '
|| l_FROM_TABLE
|| ' WHERE '
|| l_WHERE_CLAUSE
|| ' AND LINK.OTHER_ID = '
|| l_SELECT_ID
|| ' AND LINK.TEMPLATE_ID = '
|| ' :P_Template_ID '
|| ' AND LINK.OBJECT_CODE ='''
|| P_JTF_OBJECT_CODE
|| ''' ORDER BY '
|| l_ORDER_BY_CLAUSE;
l_statement := 'SELECT LINK.LINK_ID, '
|| l_SELECT_NAME
|| ', '
|| l_SELECT_DETAIL
|| ','
|| l_SELECT_ID
|| ', LINK.LAST_UPDATE_DATE FROM CS_TP_TEMPLATE_LINKS LINK, '
|| l_FROM_TABLE
|| ' WHERE '
|| l_WHERE_CLAUSE
|| ' AND LINK.LOOKUP_CODE = '
|| l_SELECT_ID
|| ' AND LINK.TEMPLATE_ID = '
|| ' :P_Template_ID '
|| ' AND LINK.OBJECT_CODE ='''
|| P_JTF_OBJECT_CODE
|| '''';
l_statement := 'SELECT LINK.LINK_ID, '
|| l_SELECT_NAME
|| ', '
|| l_SELECT_DETAIL
|| ', '
|| l_SELECT_ID
|| ', LINK.LAST_UPDATE_DATE FROM CS_TP_TEMPLATE_LINKS LINK, '
|| l_FROM_TABLE
|| ' WHERE '
|| l_WHERE_CLAUSE
|| ' AND LINK.LOOKUP_CODE = '
|| l_SELECT_ID
|| ' AND LINK.TEMPLATE_ID = '
|| ' :P_Template_ID '
|| ' AND LINK.OBJECT_CODE ='''
|| P_JTF_OBJECT_CODE
|| ''' ORDER BY '
|| l_ORDER_BY_CLAUSE;
dbms_sql.define_column(l_CursorID, 5, L_LAST_UPDATE_DATE);
dbms_sql.column_value(l_CursorID, 5, l_last_update_date);
x_template_links (j).mLAST_UPDATED_DATE :=
to_char (l_last_update_date, l_default_last_up_date_format);
l_select_id VARCHAR2(60);
l_select_name VARCHAR2(1000);
l_select_detail VARCHAR2(1000);
Select SELECT_ID,
SELECT_NAME,
SELECT_DETAILS,
FROM_TABLE,
WHERE_CLAUSE,
ORDER_BY_CLAUSE
From JTF_OBJECTS_VL
Where OBJECT_CODE = v_jtf_obj_code;
l_LAST_UPDATE_DATE DATE;
Into l_SELECT_ID, l_SELECT_NAME,l_SELECT_DETAIL,
l_FROM_TABLE,l_WHERE_CLAUSE, l_ORDER_BY_CLAUSE;
' and '||l_SELECT_NAME|| 'like '||':p_link_name';
' and '||l_SELECT_NAME||' like '||':p_link_name'||l_ORDER_BY_CLAUSE;
' and upper('||l_SELECT_NAME||') like '||'upper(:p_link_name)'||' and (' ||
l_SELECT_NAME || ' like '||':p_product_name_case_1' || ' or '||
l_SELECT_NAME || ' like '||':p_product_name_case_2' || ' ) '|| l_ORDER_BY_CLAUSE;
' and upper('||l_SELECT_NAME||') like '||'upper(:p_link_name)'||' and (' ||
l_SELECT_NAME || ' like '||':p_product_name_case_1' || ' or '||
l_SELECT_NAME || ' like '||':p_product_name_case_2' || ' ) ';
' and upper('||l_SELECT_NAME||') like '||'upper(:p_link_name)'||' and (' ||
l_SELECT_NAME || ' like '||':p_product_name_case_1' || ' or '||
l_SELECT_NAME || ' like '||':p_product_name_case_2' || ' or '||
l_SELECT_NAME || ' like '||':p_product_name_case_3' || ' or '||
l_SELECT_NAME || ' like '||':p_product_name_case_4' || ' ) '||l_ORDER_BY_CLAUSE;
' and upper('||l_SELECT_NAME||') like '||'upper(:p_link_name)'||' and (' ||
l_SELECT_NAME || ' like '||':p_product_name_case_1' || ' or '||
l_SELECT_NAME || ' like '||':p_product_name_case_2' || ' or '||
l_SELECT_NAME || ' like '||':p_product_name_case_3' || ' or '||
l_SELECT_NAME || ' like '||':p_product_name_case_4' || ' ) ' ;
l_statement := 'SELECT '
|| l_SELECT_ID
|| ', '
|| l_SELECT_NAME
|| ', '
|| l_SELECT_DETAIL
|| ' FROM '
|| l_FROM_TABLE
|| ' WHERE '
|| l_WHERE_CLAUSE
|| ' AND '
|| ' NOT EXISTS (SELECT '
|| '''x'''
|| ' FROM CS_TP_TEMPLATE_LINKS LINK WHERE LINK.TEMPLATE_ID = '
|| ':P_Template_ID'
|| ' AND LINK.OBJECT_CODE ='''
|| P_JTF_OBJECT_CODE
|| ''' AND LINK.OTHER_ID = '
|| l_SELECT_ID
|| ' ) ';
l_statement := 'SELECT '
|| l_SELECT_ID
|| ', '
|| l_SELECT_NAME
|| ', '
|| l_SELECT_DETAIL
|| ' FROM '
|| l_FROM_TABLE
|| ' WHERE '
|| l_WHERE_CLAUSE
|| ' AND '
|| ' NOT EXISTS (SELECT '
|| '''x'''
|| ' FROM CS_TP_TEMPLATE_LINKS LINK WHERE LINK.TEMPLATE_ID = '
|| ':P_Template_ID'
|| ' AND LINK.OBJECT_CODE ='''
|| P_JTF_OBJECT_CODE
|| ''' AND LINK.OTHER_ID = '
|| l_SELECT_ID
|| ' ) '
|| l_ORDER_BY_CLAUSE;
l_statement := 'SELECT '
|| l_SELECT_ID
|| ', '
|| l_SELECT_NAME
|| ', '
|| l_SELECT_DETAIL
|| ' FROM '
|| l_FROM_TABLE
|| ' WHERE '
|| l_WHERE_CLAUSE
|| ' AND '
|| ' NOT EXISTS (SELECT '
|| '''x'''
|| ' FROM CS_TP_TEMPLATE_LINKS LINK WHERE LINK.TEMPLATE_ID = '
|| ':P_Template_ID'
|| ' AND LINK.OBJECT_CODE ='''
|| P_JTF_OBJECT_CODE
|| ''' AND LINK.LOOKUP_CODE = '
|| l_SELECT_ID
|| ' ) ';
l_statement := 'SELECT '
|| l_SELECT_ID
|| ', '
|| l_SELECT_NAME
|| ', '
|| l_SELECT_DETAIL
|| ' FROM '
|| l_FROM_TABLE
|| ' WHERE '
|| l_WHERE_CLAUSE
|| ' AND '
|| ' NOT EXISTS (SELECT '
|| '''x'''
|| ' FROM CS_TP_TEMPLATE_LINKS LINK WHERE LINK.TEMPLATE_ID = '
|| ':P_Template_ID'
|| ' AND LINK.OBJECT_CODE ='''
|| P_JTF_OBJECT_CODE
|| ''' AND LINK.LOOKUP_CODE = '
|| l_SELECT_ID
|| ' ) '
|| l_ORDER_BY_CLAUSE;
l_statement_two := 'SELECT count('
|| l_SELECT_ID
||') '
||substr(l_statement, l_from_number);
PROCEDURE Delete_Template_Links (
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR := FND_API.G_FALSE,
p_template_id IN NUMBER,
p_jtf_object_code IN VARCHAR2,
p_template_links IN Template_Link_List,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Template_Links';
Select count(*)
From CS_TP_TEMPLATES_B
Where template_id = P_Template_ID;
CS_TP_TEMPLATE_LINKS_PKG.DELETE_ROW
( X_LINK_ID =>l_One_Template_Link.mLinkID);
END Delete_Template_Links;
Select count(*)
From CS_TP_TEMPLATES_B
Where template_id = P_Template_ID;
Select CS_TP_TEMPLATE_LINKS_S.nextval Into l_New_Link_id From dual;
CS_TP_TEMPLATE_LINKS_PKG.INSERT_ROW (
x_rowid => l_Row_ID ,
x_link_id => l_New_Link_id,
x_template_id => P_Template_ID,
x_other_id => l_One_Template_Link.mOther_ID,
x_lookup_code => l_One_Template_Link.lookup_Code,
x_lookup_type => l_One_Template_Link.Lookup_Type,
x_object_code => l_One_Template_Link.mJTF_OBJECT_CODE,
x_creation_date => l_current_date,
x_created_by => l_created_by,
x_last_update_date => l_current_date,
x_last_updated_by => l_created_by,
x_last_update_login => l_login);
CS_TP_TEMPLATE_LINKS_PKG.UPDATE_ROW (
x_link_id => l_One_Template_Link.mLinkID,
x_template_id => P_Template_ID,
x_other_id => l_One_Template_Link.mOther_ID,
x_lookup_code => l_One_Template_Link.lookup_Code,
x_lookup_type => l_One_Template_Link.Lookup_Type,
x_object_code => l_One_Template_Link.mJTF_OBJECT_CODE,
x_last_update_date => l_current_date,
x_last_updated_by => l_created_by,
x_last_update_login => l_login);