[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Cursor to select attribute list for template */
Cursor c_sr_attr (p_start_date date) is
select inctype.name sr_type
, srattr.incident_type_id sr_type_id
, lkp.description sr_name
, srattr.sr_attribute_code sr_code
, srattr.sr_attr_mandatory_flag mandatory_flag
, srattr.sr_attribute_list_name sr_attr_list_name
, srattr.sr_attr_default_value default_value
, srattr.sr_attr_displayed_flag displayed_flag
, srattr.start_date_active start_date_active
, srattr.end_date_active end_date_active
, srattr.last_update_date last_update_date
, lkp.start_Date_active lkup_start_Date
, lkp.end_date_active lkup_end_Date
,srattr.sr_type_attr_seq_num
from cug_sr_type_attr_maps_vl srattr
, cs_incident_types_vl inctype
, fnd_lookup_values lkp
where srattr.incident_type_id = inctype.incident_type_id
and srattr.sr_attribute_code = lkp.lookup_code
and lkp.lookup_type = 'CUG_SR_TYPE_ATTRIBUTES'
and lkp.language = userenv('lang')
and (trunc(srattr.last_update_date) >= trunc(p_start_date)
or trunc(lkp.last_update_date) >= trunc(p_start_date))
-- and trunc(srattr.last_update_date) >= trunc(p_start_date)
order by sr_type , srattr.sr_type_attr_seq_num;
/* Cursor to select attribute ListName */
Cursor C_attr_listName (P_attr_code varchar2, p_start_date date) is
select lookup_code
, meaning
from fnd_lookup_values
where lookup_code = p_attr_code
and trunc(last_update_date) >= trunc(p_start_date);
select lnk.template_id template_id
from cs_tp_template_links lnk
, cs_tp_templates_vl tmpl
where tmpl.template_id = lnk.template_id
and tmpl.name = p_template_name;
SELECT tmpq.template_id template_id
, tmpq.question_id question_id
, quest.lookup_id lookup_id
, quest.name qname
, quest.text qtext
, quest.description qdesc
FROM cs_tp_template_questions tmpq
, cs_tp_questions_vl quest
WHERE tmpq.question_id = quest.question_id
AND tmpq.template_id = p_templ_id
AND quest.name = p_attr_name;
SELECT lookup_code lookup_code
, meaning meaning
, description description
, start_date_active start_date_active
, end_date_active end_date_active
, last_update_date last_update_date
FROM fnd_lookup_values
WHERE lookup_type = p_lookup_type
AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
and trunc(nvl(end_date_active,sysdate))
AND language = userenv('LANG');
select choice_id, value, default_choice_flag
from cs_tp_choices_vl
where lookup_id = p_lookup_id;
select freetext_id, lookup_id
from cs_tp_freetexts
where lookup_id = p_lookup_id;
l_attr_update_flag boolean;
l_update_flag boolean ;
/* Defining Record type's for insert/update*/
l_template_rec cs_tp_templates_pvt.template;
/* Opening the main cursor to fetch new/update sr attribute records to be processed */
fnd_file.put_line(fnd_file.log,'Start of SR Attribute Interface logic');
select to_date(p_date, 'YYYY/MM/DD HH24:MI:SS') INTO l_date from dual;
l_update_flag := false;
l_attr_update_flag := false;
l_template_rec.mlast_updated_date := l_sr_attr.last_update_date;
l_template_link_list(1).mLast_Updated_Date := l_sr_attr.last_update_date;
CS_TP_TEMPLATES_PVT.update_template_links
(
p_api_version_number => l_api_version,
p_init_msg_list => l_init_msg_list_true,
p_commit => l_init_commit_true,
p_template_id => l_tmpl_id,
p_jtf_object_code => l_template_link_list(1).mjtf_object_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
);
l_template_question_list.mlast_updated_date := l_sr_attr.last_update_date;
l_attr_update_flag := false;
l_attr_update_flag := true;
l_template_question_list.mlast_updated_date := to_char(l_sr_attr.last_update_date, 'MM/DD/YYYY/SSSSS');
fnd_file.put_line(fnd_file.log,'Calling the Update_question API, SR Type: '
|| l_sr_attr.sr_type || ' Attribute : '|| l_sr_attr.sr_name);
CS_TP_QUESTIONS_PVT.Update_Question
(
p_api_version_number => l_api_version,
p_init_msg_list => l_init_msg_list_true,
p_commit => l_init_commit_true,
p_one_question => l_template_question_list,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
fnd_file.put_line(fnd_file.log,'Calling CS_TP_LOOKUPS_PKG.UPDATE_ROW API, SR Type: '
|| l_sr_attr.sr_type || ' Attribute : '|| l_sr_attr.sr_name);
CS_TP_LOOKUPS_PKG.UPDATE_ROW
(
X_ROWID => l_rowid,
X_LOOKUP_ID => l_template_question_list.mlookupid,
X_LOOKUP_TYPE => l_template_question_list.manswertype,
X_DEFAULT_VALUE => l_sr_attr.default_value, --Bug 6705077
X_CREATION_DATE => sysdate,
X_CREATED_BY => FND_GLOBAL.user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => FND_GLOBAL.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id,
X_START_DATE_ACTIVE => NULL,
X_END_DATE_ACTIVE => NULL,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL
);
select l.lookup_id into l_tmpl_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 = l_tmpl_id
and q.question_id = l_tmpl_quest_id;
if l_attr_update_flag = TRUE then
-- only to do this if the attribute is in update mode
-- Call the delete program to delete all the records from the cs_tp_choices_tl
fnd_file.put_line(fnd_file.log,'Calling the delete Choice API, attribute is freetext and attribute is in updatemode, SR Type: '
|| l_sr_attr.sr_type || ' Attribute : '|| l_sr_attr.sr_name);
CS_TP_CHOICES_PVT.DELETE_CHOICE
(
p_api_version_number => l_api_version,
p_init_msg_list => l_init_msg_list_true,
p_commit => l_init_commit_true,
p_choice_id => l_tp_choice.choice_id,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
l_template_question_freetext.mlast_updated_date:= l_sr_attr.last_update_date;
if l_attr_update_flag = TRUE then
-- only to do this if the attribute is in update mode
-- Call the delete program to delete all the records from the cs_tp_choices_tl
fnd_file.put_line(fnd_file.log,'Calling the delete Choice API, attribute is in update mode, SR Type: '
|| l_sr_attr.sr_type || ' Attribute : '|| l_sr_attr.sr_name);
CS_TP_CHOICES_PVT.DELETE_CHOICE
(
p_api_version_number => l_api_version,
p_init_msg_list => l_init_msg_list_true,
p_commit => l_init_commit_true,
p_choice_id => l_tp_choice.choice_id,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
fnd_file.put_line(fnd_file.log,'Calling the delete Free_Text API incase attribute was defined as freetext before, attribute is in update mode, SR Type: '
|| l_sr_attr.sr_type || ' Attribute : '|| l_sr_attr.sr_name);
CS_TP_FREETEXTS_PKG.DELETE_ROW(l_tp_freetext.freetext_id);
l_template_question_choice.mlast_updated_date := l_tmpl_choice_lookup.last_update_date;
fnd_file.put_line(fnd_file.log,'Calling Delete Question API for attribute is enddated, SR Type: ' || l_sr_attr.sr_type);
cs_tp_questions_pvt.Delete_Question
(
p_api_version_number => l_api_version,
p_init_msg_list => l_init_msg_list_true,
p_commit => l_init_commit_true,
p_Question_ID => l_tmpl_attr_exists.question_id,
p_Template_ID => l_tmpl_attr_exists.template_id,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
PROCEDURE Update_Attr_ListName ( errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_lookup_type IN VARCHAR2)
IS
/* Cursor to query record from cug_sr_type_attr_maps_v table */
Cursor c_cug_sr_attr_ListName (p_lookup_type varchar2) is
select cs.name tmpl_name
, attr.sr_attribute_code attr_code
, lkps.lookup_type list_name
, lkps1.description sr_name
, attr.start_date_Active start_date_active
, attr.end_date_active end_date_active
from cug_sr_type_attr_maps_vl attr
, fnd_lookup_types lkps
, fnd_lookups lkps1
, cs_incident_types_vl cs
where lkps.lookup_type = attr.sr_attribute_list_name
and attr.sr_attribute_list_name = p_lookup_type
and lkps1.lookup_code = attr.sr_attribute_code
and cs.incident_type_id = attr.incident_type_id
and (trunc(nvl(attr.end_date_active,sysdate)) >= trunc(sysdate));
select lookup_code lookup_code
, meaning meaning
, description description
, lookup_type lookup_type
, start_date_active start_date_active
, end_date_active end_date_active
, last_update_date last_update_date
from fnd_lookup_values
where trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
and trunc(nvl(end_date_active,sysdate))
and lookup_type = p_lookup_type
and language = userenv('LANG');
select tmpl.name tmpl_name
, qvl.name name
, qvl.text text
, qvl.lookup_id lookup_id
, ch.lookup_type choice_id
from cs_tp_questions_vl qvl
, cs_tp_lookups ch
, cs_tp_template_questions tmpl_qa
, cs_tp_templates_vl tmpl
where qvl.lookup_id = ch.lookup_id
and ch.lookup_type = 'CHOICE'
and tmpl_qa.question_id = qvl.question_id
and tmpl_qa.template_id = tmpl.template_id
and qvl.name = p_attr_name
and tmpl.name = p_tmpl_name;
select choice_id
, lookup_id
, value
from cs_tp_choices_vl
where lookup_id = p_lookup_id;
/* open cursor for choices table and call delete api to delete existing record */
OPEN c_tp_question (l_cug_sr_attr_ListName.sr_name, l_cug_sr_attr_ListName.tmpl_name);
fnd_file.put_line(fnd_file.log,'Delete Choice before re-creating, '
|| 'SR Type : ' || l_cug_sr_attr_ListName.tmpl_name
|| 'Attribute Name : '
|| l_cug_sr_attr_ListName.sr_name || ' LOV Name : '
|| l_cug_sr_attr_ListName.list_name);
CS_TP_CHOICES_PVT.DELETE_CHOICE
(
p_api_version_number => l_api_version,
p_init_msg_list => l_init_msg_list_true,
p_commit => l_init_commit_true,
p_choice_id => l_tp_choice.choice_id,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
l_templ_question_list.mlast_updated_date := l_fnd_lookup.last_update_date;
END Update_Attr_ListName;