[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_update_conditions_list VARCHAR2(4000);
l_update_actions_list VARCHAR2(4000);
SELECT csad.event_condition_id,
csad.action_code,
to_char(null) notification_template_id,
to_char(csad.event_condition_id) || csad.action_code index_cols,
to_char(null) role_group_type,
to_char(null) role_group_code
FROM CS_SR_ACTION_TRIGGERS csat,
CS_SR_ACTION_DETAILS csad,
CS_SR_EVENT_CODES_B cec
WHERE
cec.WF_BUSINESS_EVENT_ID = l_event_name
and csat.EVENT_CODE = cec.EVENT_CODE
and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(csat.start_date_active, SYSDATE))
and TRUNC(NVL(csat.end_date_active, SYSDATE))
and csad.event_condition_id = csat.event_condition_id
and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(csad.start_date_active, SYSDATE))
and TRUNC(NVL(csad.end_date_active, SYSDATE))
and csat.from_to_status_code IS NULL
and (csad.incident_status_id IS NOT NULL OR
csad.resolution_code IS NOT NULL)
and csat.relationship_type_id IN
( select cil.link_type_id
FROM cs_incident_links cil
WHERE cil.subject_id = l_request_id)
-- We need to add this to differentiate it from notification rules.
-- We may have a ntfxn rule without a template_id (partially created).
and csad.action_code NOT like 'NOTIFY%'
UNION
SELECT csad.event_condition_id,
csad.action_code,
to_char(null) notification_template_id,
to_char(csad.event_condition_id) || csad.action_code index_cols,
to_char(null) role_group_type,
to_char(null) role_group_code
FROM CS_SR_ACTION_TRIGGERS csat,
CS_SR_ACTION_DETAILS csad,
CS_SR_EVENT_CODES_B cec
WHERE
cec.WF_BUSINESS_EVENT_ID = l_event_name
and csat.EVENT_CODE = cec.EVENT_CODE
and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(csat.start_date_active, SYSDATE))
and TRUNC(NVL(csat.end_date_active, SYSDATE))
and csad.event_condition_id = csat.event_condition_id
and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(csad.start_date_active, SYSDATE))
and TRUNC(NVL(csad.end_date_active, SYSDATE))
and csat.from_to_status_code IS NOT NULL
and csat.relationship_type_id IS NULL
and csat.incident_status_id IS NOT NULL
-- We need to add this to differentiate it from notification rules.
-- We may have a ntfxn rule without a template_id (partially created).
and csad.action_code NOT like 'NOTIFY%'
and csad.relationship_type_id IN
( select cil.link_type_id
FROM cs_incident_links cil
WHERE cil.subject_id = l_request_id)
/******************
-- For 11.5.9, we are not supporting any Update Rules defined by the user.
-- So csad.relationship_type_id will always have a value as defined from the
-- seeded Rule from HLD:
-- Rule 2: If the status of the service request changes to Closed,
-- and it has a (outgoing) Root cause of relationship, then,
-- update status of all related service requests to Clear.
-- and csad.relationship_type_id IS NULL OR
***************/
-- This part of the query is for Notification Rules
UNION
SELECT csad.event_condition_id,
csad.action_code,
csad.notification_template_id,
to_char(csad.event_condition_id) || csad.action_code index_cols,
role_group_type,
role_group_code
FROM CS_SR_ACTION_TRIGGERS csat,
CS_SR_ACTION_DETAILS csad,
CS_SR_EVENT_CODES_B cec
/* 03/01/2004 - RHUNGUND - Bug fix for 3412833
Commenting the following table from the FROM clause since it was resulting
in a cartesian join
CS_SR_ACTION_CODES_B cac
*/
WHERE
cec.WF_BUSINESS_EVENT_ID = l_event_name
and csat.EVENT_CODE = cec.EVENT_CODE
and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(csat.start_date_active, SYSDATE))
and TRUNC(NVL(csat.end_date_active, SYSDATE))
and csad.event_condition_id = csat.event_condition_id
and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(csad.start_date_active, SYSDATE))
and TRUNC(NVL(csad.end_date_active, SYSDATE))
and csad.notification_template_id IS NOT NULL
-- We need to add this since a notification rule can still be created (partially)
-- without a message template.
and csad.action_code like 'NOTIFY%'
ORDER BY index_cols;
SELECT INCIDENT_ID
FROM cs_incidents_all_b
WHERE INCIDENT_NUMBER = l_request_number;
SELECT INCIDENT_NUMBER
FROM cs_incidents_all_b
WHERE INCIDENT_ID = l_request_id;
SELECT subject_type FROM
cs_incident_links
WHERE subject_id = l_request_number;
l_event_name = 'oracle.apps.cs.sr.ServiceRequest.relationshipdeleted') THEN
l_subject_type := WF_ENGINE.GetItemAttrText(
itemtype => itemtype,
itemkey => itemkey,
aname => 'LINK_SUBJECT_TYPE' );
l_event_name = 'oracle.apps.cs.sr.ServiceRequest.relationshipdeleted') AND
l_subject_type <> 'SR') THEN
CLOSE sel_incident_id_csr;
Build a condition_id/action_code list for update rules.
**************/
ELSE -- IF (sel_action_rec.notification_template_id IS NOT NULL)
-- Check for the length, not to exceed max WF text length of 4000.
IF (nvl(LENGTH(l_update_conditions_list), 0) +
nvl(LENGTH(l_event_condition_id), 0) + 1) <= 4000 OR
(nvl(LENGTH(l_update_actions_list), 0) +
nvl(LENGTH(sel_action_rec.action_code), 0) + 1 ) <= 4000 THEN
IF l_update_conditions_list IS NULL THEN
l_update_conditions_list := l_event_condition_id;
l_update_actions_list := sel_action_rec.action_code;
l_update_conditions_list := l_update_conditions_list || ' ' || l_event_condition_id;
l_update_actions_list := l_update_actions_list || ' ' || sel_action_rec.action_code;
aname => 'MORE_UPDATE_ACTION_LIST',
avalue => 'Y' );
IF (l_update_conditions_list IS NOT NULL OR l_notify_conditions_list IS NOT NULL) THEN
WF_ENGINE.SetItemAttrText(
itemtype => itemtype,
itemkey => itemkey,
aname => 'NTFY_CONDITION_LIST',
avalue => l_notify_conditions_list );
aname => 'UPDATE_CONDITION_LIST',
avalue => l_update_conditions_list );
aname => 'UPDATE_ACTION_LIST',
avalue => l_update_actions_list );
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Check_Rules_For_Event',
itemtype, itemkey, actid, funmode);
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Check_Rules_For_Event',
itemtype, itemkey, actid, funmode);
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Check_Notification_Rules',
itemtype, itemkey, actid, funmode);
SELECT link_type_id
FROM CS_SR_LINK_TYPES_VL
WHERE name = l_trigger_link_type;
SELECT csad.event_condition_id,
csad.action_code,
csad.notification_template_id, /** this is the WF message name **/
csad.relationship_type_id detail_link_type,
csad.role_group_type,
csad.role_group_code,
csat.from_to_status_code,
csat.incident_status_id trigger_incident_status_id,
csat.relationship_type_id trigger_link_type
FROM CS_SR_ACTION_TRIGGERS csat,
CS_SR_ACTION_DETAILS csad,
CS_SR_EVENT_CODES_B cec
WHERE
cec.WF_BUSINESS_EVENT_ID = l_event_name
and csat.EVENT_CODE = cec.EVENT_CODE
and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(csat.start_date_active, SYSDATE))
and TRUNC(NVL(csat.end_date_active, SYSDATE))
and csad.event_condition_id = csat.event_condition_id
and csad.event_condition_id = l_event_condition_id
and csad.action_code = l_action_code
and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(csad.start_date_active, SYSDATE))
and TRUNC(NVL(csad.end_date_active, SYSDATE))
and csad.notification_template_id IS NOT NULL
-- We need to add this since a notification rule can still be created (partially)
-- without a message template.
and csad.action_code like 'NOTIFY%';
SELECT
cil.object_id,
cil.link_id,
inc.incident_owner_id
FROM cs_incident_links cil,
cs_incidents_all_b inc
WHERE cil.subject_id = l_request_id
and inc.incident_id = cil.object_id
ORDER BY cil.object_id;
SELECT
cil.object_id,
cil.link_id,
inc.incident_owner_id
FROM cs_incident_links cil,
cs_incidents_all_b inc
WHERE cil.subject_id = l_request_id
AND cil.link_type_id = l_relationship_type_id
AND inc.incident_id = cil.object_id
ORDER BY cil.object_id;
himself has updated the SR
*/
l_incident_owner_id NUMBER;
SELECT incident_owner_id FROM cs_incidents_all_b WHERE
incident_id = l_request_id;
SELECT resource_id FROM jtf_rs_resource_extns emp, fnd_user users WHERE
emp.source_id = users.employee_id and
users.user_id = l_user_id;
SELECT resource_id from jtf_rs_resource_extns emp WHERE
source_id = l_prev_owner_id;
SELECT party_role_code
FROM cs_party_role_group_maps
WHERE party_role_group_code = l_party_role_group_code
AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
and trunc(nvl(end_date_active,sysdate));
SELECT INCIDENT_ID
INTO l_request_id
FROM cs_incidents_all_b
WHERE INCIDENT_NUMBER = l_request_number;
SELECT name
INTO l_request_status_temp
FROM CS_INCIDENT_STATUSES_VL
WHERE INCIDENT_STATUS_ID = sel_event_action_rec.trigger_incident_status_id;
/*** Rule 7 : Notify the service request owner, when a link type is deleted***/
/*** between the current service request to another service request ***/
/*** IF (sel_event_action_rec.from_to_status_code IS NOT NULL) ***/
ELSIF (sel_event_action_rec.trigger_link_type IS NOT NULL) THEN
/********************
This section is for relationship created/deleted events
********************/
l_trigger_link_type := WF_ENGINE.GetItemAttrText(
itemtype => itemtype,
itemkey => itemkey,
aname => 'NTFY_LINK_TYPE');
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Get_Recipients_To_Notify',
itemtype, itemkey, actid, funmode);
SELECT hcp.EMAIL_ADDRESS,
cshcp.party_id,
to_number(NULL) person_id,
cshcp.contact_type,
cshcp.sr_contact_point_id
FROM hz_contact_points hcp,
cs_hz_sr_contact_points cshcp
WHERE cshcp.INCIDENT_ID = l_request_id
AND cshcp.PRIMARY_FLAG = 'Y'
AND cshcp.CONTACT_POINT_TYPE = 'EMAIL'
AND hcp.CONTACT_POINT_ID = cshcp.CONTACT_POINT_ID
AND cshcp.contact_type <> 'EMPLOYEE'
AND cshcp.party_role_code = 'CONTACT'
union
select ppf.email_address,
ppf.party_id,
ppf.person_id,
cshcp.contact_type,
cshcp.sr_contact_point_id
from per_all_people_f ppf,
cs_hz_sr_contact_points cshcp
where cshcp.INCIDENT_ID = l_request_id
AND cshcp.PRIMARY_FLAG = 'Y'
AND cshcp.CONTACT_POINT_TYPE = 'EMAIL'
AND ppf.person_id = cshcp.party_id
AND cshcp.contact_type = 'EMPLOYEE'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(ppf.effective_start_date, SYSDATE))
AND TRUNC(NVL(ppf.effective_end_date, SYSDATE));
SELECT cshcp.party_id,
cshcp.contact_type,
cshcp.sr_contact_point_id,
cshcp.contact_point_type,
cshcp.contact_point_id
FROM cs_hz_sr_contact_points cshcp
WHERE cshcp.INCIDENT_ID = l_request_id
AND cshcp.PRIMARY_FLAG = 'Y'
AND cshcp.party_role_code = 'CONTACT'
AND cshcp.end_date_active IS NULL;--12.1.3 Inactivate Contact Points
SELECT cshcp.sr_contact_point_id,
cshcp.contact_point_id
FROM cs_hz_sr_contact_points cshcp
WHERE cshcp.INCIDENT_ID = l_request_id
AND cshcp.party_role_code = 'CONTACT'
AND cshcp.contact_point_type = 'EMAIL'
AND cshcp.party_id = p_party_id
AND cshcp.end_date_active IS NULL;--12.1.3 Inactivate Contact Points
SELECT email_address
FROM hz_contact_points
WHERE contact_point_id = p_contact_point_id
AND contact_point_type = 'EMAIL'
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = p_party_id ;
SELECT email_address
FROM hz_contact_points
WHERE contact_point_type = 'EMAIL'
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = p_party_id
ORDER BY Primary_flag DESC;
SELECT ppf.email_address
--ppf.party_id,
--ppf.person_id
FROM per_workforce_x ppf
WHERE ppf.person_id = p_person_id;
SELECT hzp.person_first_name first_name,
hzp.person_last_name last_name
FROM hz_parties hzp,
hz_relationships hzr
WHERE hzr.PARTY_ID = p_party_id
AND hzr.SUBJECT_ID = hzp.PARTY_ID
AND hzr.SUBJECT_TYPE = 'PERSON';
SELECT hzp.person_first_name first_name,
hzp.person_last_name last_name
FROM hz_parties hzp
WHERE hzp.PARTY_ID = p_party_id;
SELECT ppf.first_name first_name,
ppf.last_name last_name
FROM per_workforce_x ppf
WHERE ppf.person_id = p_party_id;
SELECT hcp.EMAIL_ADDRESS,
cshcp.party_id,
to_number(NULL) person_id,
cshcp.contact_type,
cshcp.sr_contact_point_id
FROM hz_contact_points hcp,
cs_hz_sr_contact_points cshcp
WHERE cshcp.INCIDENT_ID = l_request_id
AND cshcp.CONTACT_POINT_TYPE = 'EMAIL'
AND hcp.CONTACT_POINT_ID = cshcp.CONTACT_POINT_ID
AND cshcp.contact_type <> 'EMPLOYEE'
AND cshcp.party_role_code = 'CONTACT'
UNION
select ppf.email_address,
ppf.party_id,
ppf.person_id,
cshcp.contact_type,
cshcp.sr_contact_point_id
from per_all_people_f ppf,
cs_hz_sr_contact_points cshcp
where cshcp.INCIDENT_ID = l_request_id
AND cshcp.CONTACT_POINT_TYPE = 'EMAIL'
AND ppf.person_id = cshcp.party_id
AND cshcp.contact_type = 'EMPLOYEE'
AND cshcp.party_role_code = 'CONTACT'
AND cshcp.end_date_active IS NULL--12.1.3 Inactivate Contact Points
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(ppf.effective_start_date, SYSDATE))
AND TRUNC(NVL(ppf.effective_end_date, SYSDATE));
SELECT hcp.EMAIL_ADDRESS,
cshcp.party_id,
to_number(NULL) person_id,
cshcp.contact_type,
cshcp.sr_contact_point_id
FROM hz_contact_points hcp,
cs_hz_sr_contact_points cshcp
WHERE cshcp.INCIDENT_ID = l_request_id
AND cshcp.CONTACT_POINT_TYPE = 'EMAIL'
AND hcp.CONTACT_POINT_ID = cshcp.CONTACT_POINT_ID
AND cshcp.contact_type <> 'EMPLOYEE'
AND cshcp.party_role_code = l_party_role_code
UNION
select ppf.email_address,
ppf.party_id,
ppf.person_id,
cshcp.contact_type,
cshcp.sr_contact_point_id
from per_all_people_f ppf,
cs_hz_sr_contact_points cshcp
where cshcp.INCIDENT_ID = l_request_id
AND cshcp.CONTACT_POINT_TYPE = 'EMAIL'
AND ppf.person_id = cshcp.party_id
AND cshcp.contact_type = 'EMPLOYEE'
AND cshcp.party_role_code = l_party_role_code
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(ppf.effective_start_date, SYSDATE))
AND TRUNC(NVL(ppf.effective_end_date, SYSDATE));
SELECT hcp.EMAIL_ADDRESS,
cshcp.party_id,
to_number(NULL) person_id,
cshcp.contact_type,
cshcp.sr_contact_point_id
FROM hz_contact_points hcp,
cs_hz_sr_contact_points cshcp
WHERE
hcp.CONTACT_POINT_TYPE = 'EMAIL'
AND hcp.CONTACT_POINT_ID = l_contact_point_id
AND hcp.CONTACT_POINT_ID = cshcp.CONTACT_POINT_ID
AND cshcp.contact_type <> 'EMPLOYEE'
AND cshcp.party_role_code = 'CONTACT'
UNION
SELECT ppf.EMAIL_ADDRESS,
ppf.party_id,
ppf.person_id,
cshcp.contact_type,
cshcp.sr_contact_point_id
FROM per_all_people_f ppf,
cs_hz_sr_contact_points cshcp
WHERE
cshcp.CONTACT_POINT_TYPE = 'EMAIL'
AND ppf.PERSON_ID = l_contact_point_id
AND cshcp.PARTY_ID = ppf.person_id
AND cshcp.contact_type = 'EMPLOYEE'
AND cshcp.party_role_code = 'CONTACT'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(ppf.effective_start_date, SYSDATE))
AND TRUNC(NVL(ppf.effective_end_date, SYSDATE));
SELECT hcp.EMAIL_ADDRESS,
cshcp.party_id,
to_number(NULL) person_id,
cshcp.contact_type,
cshcp.sr_contact_point_id,
cpr.name,
cpr.party_role_code
FROM hz_contact_points hcp,
cs_hz_sr_contact_points cshcp,
cs_party_roles_tl cpr
WHERE
hcp.CONTACT_POINT_TYPE = 'EMAIL'
AND hcp.CONTACT_POINT_ID = l_contact_point_id
AND hcp.CONTACT_POINT_ID = cshcp.CONTACT_POINT_ID
AND cshcp.party_role_code = cpr.party_role_code
AND cpr.language = userenv('LANG')
AND cshcp.contact_type <> 'EMPLOYEE'
AND cshcp.party_role_code <> 'CONTACT'
UNION
SELECT ppf.EMAIL_ADDRESS,
ppf.party_id,
ppf.person_id,
cshcp.contact_type,
cshcp.sr_contact_point_id,
cpr.name,
cpr.party_role_code
FROM per_all_people_f ppf,
cs_hz_sr_contact_points cshcp,
cs_party_roles_tl cpr
WHERE
cshcp.CONTACT_POINT_TYPE = 'EMAIL'
AND ppf.PERSON_ID = l_contact_point_id
AND cshcp.PARTY_ID = ppf.person_id
AND cshcp.contact_type = 'EMPLOYEE'
AND cshcp.party_role_code = cpr.party_role_code
AND cpr.language = userenv('LANG')
AND cshcp.party_role_code <> 'CONTACT'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(ppf.effective_start_date, SYSDATE))
AND TRUNC(NVL(ppf.effective_end_date, SYSDATE));
SELECT
inc.incident_number,
inc.incident_owner_id,
cil.object_id,
cil.link_type_id,
cilt.name link_type_name
FROM cs_incident_links cil,
cs_incidents_all_b inc,
CS_SR_LINK_TYPES_VL cilt
WHERE cil.subject_id = l_request_id
and cil.object_id = l_notify_subject_id
and inc.incident_id = cil.object_id
and cilt.link_type_id(+) = cil.link_type_id;
himself has updated the SR
*/
l_incident_owner_id NUMBER;
SELECT resource_id FROM jtf_rs_resource_extns emp, fnd_user users WHERE
emp.source_id = users.employee_id and
users.user_id = l_user_id;
SELECT source_id FROM jtf_rs_resource_extns emp
WHERE emp.resource_id = sel_link_rec.incident_owner_id;
SELECT incident_type_id,incident_status_id,incident_urgency_id,incident_severity_id,summary
FROM cs_incidents_all_vl inc
WHERE inc.incident_number = l_request_number;
l_updated_by VARCHAR2(240);
SELECT a.lookup_code, a.meaning FROM cs_lookups a
WHERE a.lookup_type = 'CS_SR_UPDATED_FIELDS';
SELECT source_last_name,source_first_name
from jtf_rs_resource_extns a, fnd_user b, cs_incidents_all_b c
WHERE c.last_updated_by = b.user_id and
b.employee_id = a.source_id and
c.incident_number = l_request_number;
SELECT source_last_name,source_first_name
from jtf_rs_resource_extns a, fnd_user b, cs_incidents_all_b c
WHERE c.created_by = b.user_id and
b.employee_id = a.source_id and
c.incident_number = l_request_number;
SELECT name from CS_KB_SETS_VL
WHERE set_number = l_solution_number
and status='PUB';
SELECT a.summary, b.name
FROM cs_incidents_all_vl a,
cs_incident_statuses_vl b
WHERE a.incident_status_id = b.incident_status_id AND
a.incident_id = l_request_id;
SELECT cilt.name link_type_name
FROM cs_incident_links cil,
CS_SR_LINK_TYPES_VL cilt
WHERE cilt.link_type_id = cil.link_type_id AND
cil.subject_id = l_notify_subject_id AND
cil.object_id = l_request_id;
SELECT hzp.person_first_name first_name,
hzp.person_last_name last_name,
cshcp.contact_type,
cshcp.contact_point_id
FROM hz_parties hzp,
hz_relationships hzr,
cs_hz_sr_contact_points cshcp
WHERE cshcp.INCIDENT_ID = l_request_id
AND cshcp.sr_contact_point_id = l_tmp_contact_point_id
AND cshcp.CONTACT_POINT_TYPE = 'EMAIL'
AND cshcp.contact_type = 'PARTY_RELATIONSHIP'
AND cshcp.PARTY_ID = hzr.PARTY_ID
AND hzr.SUBJECT_ID = hzp.PARTY_ID
AND hzr.SUBJECT_TYPE = 'PERSON'
UNION
SELECT hzp.person_first_name first_name,
hzp.person_last_name last_name,
cshcp.contact_type,
cshcp.contact_point_id
FROM hz_parties hzp,
cs_hz_sr_contact_points cshcp
WHERE cshcp.INCIDENT_ID = l_request_id
AND cshcp.sr_contact_point_id = l_tmp_contact_point_id
AND cshcp.CONTACT_POINT_TYPE = 'EMAIL'
AND cshcp.contact_type = 'PERSON'
AND cshcp.PARTY_ID = hzp.PARTY_ID
UNION
SELECT ppf.first_name first_name,
ppf.last_name last_name,
cshcp.contact_type,
cshcp.contact_point_id
FROM per_all_people_f ppf,
cs_hz_sr_contact_points cshcp
WHERE cshcp.INCIDENT_ID = l_request_id
AND cshcp.sr_contact_point_id = l_tmp_contact_point_id
AND cshcp.CONTACT_POINT_TYPE = 'EMAIL'
AND cshcp.contact_type = 'EMPLOYEE'
AND cshcp.PARTY_ID = ppf.PERSON_ID;*/
SELECT hzp.person_first_name first_name,
hzp.person_last_name last_name
FROM hz_parties hzp,
hz_relationships hzr,
cs_hz_sr_contact_points cshcp
WHERE cshcp.INCIDENT_ID = l_request_id
AND cshcp.sr_contact_point_id = l_tmp_contact_point_id
AND cshcp.CONTACT_POINT_TYPE = 'EMAIL'
AND cshcp.contact_type = 'PARTY_RELATIONSHIP'
AND cshcp.PARTY_ID = hzr.PARTY_ID
AND hzr.SUBJECT_ID = hzp.PARTY_ID
AND hzr.SUBJECT_TYPE = 'PERSON'
UNION --start bug 12666217
SELECT hzp.person_first_name first_name,
hzp.person_last_name last_name
FROM hz_parties hzp,
cs_hz_sr_contact_points cshcp
WHERE cshcp.INCIDENT_ID = l_request_id
AND cshcp.sr_contact_point_id = l_tmp_contact_point_id
AND cshcp.CONTACT_POINT_TYPE = 'EMAIL'
AND cshcp.contact_type = 'PERSON'
AND cshcp.PARTY_ID = hzp.PARTY_ID
UNION -- start Bug 12610714
SELECT ppf.first_name first_name,
ppf.last_name last_name
From per_all_people_f ppf,
cs_hz_sr_contact_points cshcp
WHERE cshcp.INCIDENT_ID = l_request_id
AND cshcp.sr_contact_point_id = l_tmp_contact_point_id
AND cshcp.CONTACT_POINT_TYPE = 'EMAIL'
AND cshcp.contact_type = 'EMPLOYEE'
AND cshcp.PARTY_ID = ppf.PERSON_ID
AND ppf.effective_end_date = (select max(ppf1.effective_end_date)
from per_all_people_f ppf1,
cs_hz_sr_contact_points cshcp1
WHERE cshcp1.INCIDENT_ID = l_request_id
AND cshcp1.sr_contact_point_id = l_tmp_contact_point_id
AND cshcp1.CONTACT_POINT_TYPE = 'EMAIL'
AND cshcp1.contact_type = 'EMPLOYEE'
AND cshcp1.PARTY_ID = ppf1.PERSON_ID);
/*SELECT first_name first_name,
last_name last_name
FROM (
SELECT ppf.first_name first_name,
ppf.last_name last_name
FROM per_all_people_f ppf,
cs_hz_sr_contact_points cshcp
WHERE cshcp.INCIDENT_ID = l_request_id
AND cshcp.sr_contact_point_id = l_tmp_contact_point_id
AND cshcp.CONTACT_POINT_TYPE = 'EMAIL'
AND cshcp.contact_type = 'EMPLOYEE'
AND cshcp.PARTY_ID = ppf.PERSON_ID
order by ppf.effective_end_date desc) ;
select text_value from wf_item_attribute_values
where item_type='SERVEREQ'
and name = 'CONTACT_PARTY_ID_LIST'
and item_key = l_temp_item_key;
select WEB_HTML_CALL from fnd_form_functions
where FUNCTION_NAME='IBU_SR_DETAILS';
select name,
substrb(display_name,1,360),
email_address,
notification_preference,
language
from wf_users
where orig_system = p_orig_system
and orig_system_id = p_orig_system_id
order by status, start_date;
SELECT name
FROM cs_party_roles_vl
WHERE party_role_code = p_party_role_code;
SELECT NVL(html_notification,'N')
FROM cs_sr_action_details
WHERE event_condition_id = l_event_condition_id
AND action_code = l_action_code ;
create a list of SR attributes that were updated, which will later
be included in the ntfxn message text.
****************************/
FOR i in l_sel_lookup_value_csr LOOP
IF(i.lookup_code = 'CS_SR_SEVERITY') THEN
IF(l_prev_severity_id <> l_sel_curr_sr_details_rec.incident_severity_id) THEN
l_changed_field := i.meaning;
l_updated_by := l_get_resource_name_rec.source_first_name || ' '
|| l_get_resource_name_rec.source_last_name;
aname => 'UPDATED_BY',
avalue => l_updated_by );
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Set_Notification_Details',
itemtype, itemkey, actid, funmode);
SELECT PARTY_NAME
FROM HZ_PARTIES
WHERE
PARTY_ID = l_contact_party_id;
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Create_Contact_Interaction',
itemtype, itemkey, actid, funmode);
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'All_Interactions_Created',
itemtype, itemkey, actid, funmode);
SELECT csad.event_condition_id,
csad.action_code,
csad.notification_template_id, /** this is the WF message name **/
csad.relationship_type_id detail_link_type
FROM CS_SR_ACTION_DETAILS csad
WHERE csad.event_condition_id = l_event_condition_id
and csad.action_code = l_action_code
and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(csad.start_date_active, SYSDATE))
and TRUNC(NVL(csad.end_date_active, SYSDATE))
and csad.notification_template_id IS NOT NULL
and csad.action_code like 'NOTIFY%';
SELECT
cil.object_id,
cil.link_id,
inc.incident_owner_id
FROM cs_incident_links cil,
cs_incidents_all_b inc
WHERE cil.subject_id = l_request_id
and inc.incident_id = cil.object_id
and cil.object_id > l_subject_id
ORDER BY cil.object_id;
SELECT
cil.object_id,
cil.link_id,
inc.incident_owner_id
FROM cs_incident_links cil,
cs_incidents_all_b inc
WHERE cil.subject_id = l_request_id
AND cil.link_type_id = l_relationship_type_id
AND inc.incident_id = cil.object_id
and cil.object_id > l_subject_id
ORDER BY cil.object_id;
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'All_Recipients_Notified',
itemtype, itemkey, actid, funmode);
SELECT csad.event_condition_id,
csad.action_code,
csad.notification_template_id
FROM CS_SR_ACTION_TRIGGERS csat,
CS_SR_ACTION_DETAILS csad,
CS_SR_EVENT_CODES_B cec
WHERE
cec.WF_BUSINESS_EVENT_ID = l_event_name
and csat.EVENT_CODE = cec.EVENT_CODE
and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(csat.start_date_active, SYSDATE))
and TRUNC(NVL(csat.end_date_active, SYSDATE))
and csad.event_condition_id = csat.event_condition_id
and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(csad.start_date_active, SYSDATE))
and TRUNC(NVL(csad.end_date_active, SYSDATE))
and csad.notification_template_id IS NOT NULL
and csad.action_code like 'NOTIFY%'
AND TO_CHAR(csad.event_condition_id) || csad.action_code >
TO_CHAR(l_event_condition_id) || l_action_code
ORDER BY TO_CHAR(1) || 2;
/** IF (l_update_conditions_list IS NOT NULL AND l_update_actions_list IS NOT NULL) **/
END IF;
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Verify_Notify_Rules_Done',
itemtype, itemkey, actid, funmode);
l_update_conditions_list VARCHAR2(4000);
l_update_actions_list VARCHAR2(4000);
l_update_conditions_list := WF_ENGINE.GetItemAttrText(
itemtype => itemtype,
itemkey => itemkey,
aname => 'UPDATE_CONDITION_LIST' );
l_update_actions_list := WF_ENGINE.GetItemAttrText(
itemtype => itemtype,
itemkey => itemkey,
aname => 'UPDATE_ACTION_LIST' );
IF (l_update_conditions_list IS NOT NULL AND l_update_actions_list IS NOT NULL) THEN
pull_from_list(itemlist => l_update_conditions_list,
element => l_element);
pull_from_list(itemlist => l_update_actions_list,
element => l_action_code);
aname => 'UPDATE_CONDITION_LIST',
avalue => l_update_conditions_list );
aname => 'UPDATE_ACTION_LIST',
avalue => l_update_actions_list );
aname => 'UPDATE_EVENT_CONDITION_ID',
avalue => l_event_condition_id );
aname => 'UPDATE_ACTION_CODE',
avalue => l_action_code );
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Check_Status_Rules',
itemtype, itemkey, actid, funmode);
SELECT csat.relationship_type_id trigger_link_type,
csat.from_to_status_code,
csat.incident_status_id trigger_incident_status_id,
csad.relationship_type_id detail_link_type,
csad.incident_status_id detail_incident_status_id,
csad.resolution_code
FROM CS_SR_EVENT_CODES_B cec,
CS_SR_ACTION_TRIGGERS csat,
CS_SR_ACTION_DETAILS csad
WHERE
cec.WF_BUSINESS_EVENT_ID = l_event_name
and cec.EVENT_CODE = csat.EVENT_CODE
and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(csat.start_date_active, SYSDATE))
and TRUNC(NVL(csat.end_date_active, SYSDATE))
and csat.event_condition_id = csad.event_condition_id
and csad.event_condition_id = l_event_condition_id
and csad.action_code = l_action_code
and csad.action_code NOT like 'NOTIFY%'
and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(csad.start_date_active, SYSDATE))
and TRUNC(NVL(csad.end_date_active, SYSDATE));
SELECT cil.link_type_id,
cil.object_id,
cil.link_id
FROM cs_incident_links cil
WHERE cil.subject_id = l_request_id
AND cil.link_type_id = l_relationship_type_id
ORDER BY cil.object_id;
aname => 'UPDATE_EVENT_CONDITION_ID' );
aname => 'UPDATE_ACTION_CODE' );
SELECT name
INTO l_request_status_temp
FROM CS_INCIDENT_STATUSES_VL
WHERE INCIDENT_STATUS_ID = sel_event_action_rec.trigger_incident_status_id;
SELECT name
INTO l_relationship_type_name
FROM CS_SR_LINK_TYPES_VL
WHERE link_type_id = l_relationship_type_id;
aname => 'UPDATE_RELATIONSHIP_TYPE',
avalue => l_relationship_type_name);
aname => 'UPDATE_LINKED_SUBJECT_LIST',
avalue => l_linked_subject_list );
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Get_Links_For_Rule',
itemtype, itemkey, actid, funmode);
aname => 'UPDATE_LINKED_SUBJECT_LIST' );
aname => 'UPDATE_SUBJECT_ID',
avalue => TO_NUMBER(l_element) );
aname => 'UPDATE_LINKED_SUBJECT_LIST',
avalue => l_linked_subject_list );
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Execute_Rules_Per_SR',
itemtype, itemkey, actid, funmode);
PROCEDURE Verify_Update_Valid( itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funmode IN VARCHAR2,
result OUT NOCOPY VARCHAR2 ) IS
l_subject_id CS_INCIDENT_LINKS.SUBJECT_ID%TYPE;
l_sr_updated NUMBER;
SELECT cil.link_id
FROM cs_incident_links cil,
cs_incidents_all_b inc
WHERE cil.subject_id = lv_incident_id
-- Hard coded values for 'CAUSED BY' (2) and 'DUPLICATE OF' (3).
AND cil.link_type_id = 2
AND cil.object_id = inc.incident_id
AND inc.status_flag = 'O' ;
SELECT nvl(status.close_flag,'N'),
inc.incident_status_id
FROM cs_incident_statuses status, cs_incidents_all_b inc
WHERE inc.incident_id = lv_incident_id
AND inc.incident_status_id = status.incident_status_id;
aname => 'UPDATE_SUBJECT_ID' );
aname => 'UPDATE_ACTION_CODE' );
l_sr_updated := WF_ENGINE.GetItemAttrNumber(
itemtype => itemtype,
itemkey => itemkey,
aname => 'REQUEST_ID' );
l_sr_updated := l_subject_id;
OPEN sel_close_flag_csr(l_sr_updated);
If the SR which needs to be updated has a status
with 'closed flag' ON, DO NOT update the status
of the service request.
**********************************************/
IF (l_close_flag = 'Y' OR l_incident_status_id = l_subject_status_id) THEN
result := 'COMPLETE:N';
During automatic update, workflow process should
not close a service request if it has outgoing
links of type duplicate of/caused by to open
service requests.
********************************************/
IF (l_subject_status_id IS NULL) THEN -- Updating resolution code only, not status.
result := 'COMPLETE:Y';
SELECT nvl(status.close_flag,'N')
INTO l_close_flag
FROM cs_incident_statuses status
WHERE status.incident_status_id = l_subject_status_id;
OPEN sel_related_sr_cur(l_sr_updated);
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Verify_Update_Valid',
itemtype, itemkey, actid, funmode);
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Verify_Update_Valid',
itemtype, itemkey, actid, funmode);
END Verify_Update_Valid;
PROCEDURE Update_SR( itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funmode IN VARCHAR2,
result OUT NOCOPY VARCHAR2 ) IS
l_request_number VARCHAR2(64);
SELECT object_version_number, incident_owner_id, incident_id
FROM cs_incidents_all_b
WHERE incident_number = l_request_number;
SELECT object_version_number, incident_owner_id
FROM cs_incidents_all_b
WHERE incident_id = l_subject_id;
aname => 'UPDATE_ACTION_CODE' );
aname => 'UPDATE_SUBJECT_ID' );
SELECT nvl(status.close_flag,'N')
INTO l_close_flag
FROM cs_incident_statuses status
WHERE status.incident_status_id = l_subject_status_id;
l_service_request_rec.last_update_program_code := 'SUPPORT.WF';
CS_ServiceRequest_PVT.Update_ServiceRequest
( p_api_version => 3.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_request_id => l_request_id,
--p_request_id => l_subject_id,
p_last_updated_by => l_user_id,
p_last_update_date => sysdate,
p_service_request_rec => l_service_request_rec,
p_notes => l_notes,
p_contacts => l_contacts,
p_object_version_number=> l_object_version_number,
p_resp_appl_id => l_resp_appl_id,
p_resp_id => l_resp_id,
--p_workflow_process_id => l_wf_process_id,
x_interaction_id => out_interaction_id,
x_workflow_process_id => out_wf_process_id
);
result := 'COMPLETE:UPDATED';
aname => 'UPDATE_ERROR_DATA',
avalue => l_error_text );
proc_name => 'Update_Status',
arg1 => 'p_user_id=>'||l_user_id,
arg2 => 'p_org_id=>'||l_org_id,
arg3 => 'p_request_number=>'||l_request_number,
arg4 => 'p_status_id=>'||l_new_status_id,
arg5 => 'p_msg_data=>'||l_msg_data );
l_errmsg_name := 'CS_SR_CANT_UPDATE_STATUS';
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Update_SR',
itemtype, itemkey, actid, funmode);
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Update_SR',
itemtype, itemkey, actid, funmode);
END Update_SR;
SELECT inc.incident_number, emp.source_id
FROM jtf_rs_resource_extns emp ,
cs_incidents_all_b inc
WHERE emp.resource_id = inc.incident_owner_id
AND inc.incident_id = l_subject_id;
aname => 'UPDATE_ACTION_CODE' );
aname => 'UPDATE_SUBJECT_ID' );
SELECT incident_number, incident_owner_id
INTO l_request_number, l_subject_owner_id
FROM cs_incidents_all_b
WHERE incident_id = l_subject_id;
aname => 'UPDATE_REQUEST_ROLE',
avalue => l_owner_role );
aname => 'UPDATE_REQUEST_NUMBER',
avalue => l_request_number );
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Set_Notify_Error',
itemtype, itemkey, actid, funmode);
SELECT cil.link_type_id,
cil.object_id,
cil.link_id
FROM cs_incident_links cil,
CS_SR_LINK_TYPES_VL cilt
WHERE cil.subject_id = l_request_id
AND cil.link_type_id = cilt.link_type_id
AND cilt.name = l_relationship_type_name
AND cil.object_id > l_subject_id
ORDER BY cil.object_id;
aname => 'UPDATE_LINKED_SUBJECT_LIST' );
aname => 'UPDATE_RELATIONSHIP_TYPE');
aname => 'UPDATE_SUBJECT_ID');
aname => 'UPDATE_LINKED_SUBJECT_LIST',
avalue => l_linked_subject_list );
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Verify_All_Links_Done',
itemtype, itemkey, actid, funmode);
PROCEDURE Verify_Update_Rules_Done( itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funmode IN VARCHAR2,
result OUT NOCOPY VARCHAR2 ) IS
l_update_conditions_list VARCHAR2(4000);
l_update_actions_list VARCHAR2(4000);
SELECT csad.event_condition_id,
csad.action_code,
to_char(csad.event_condition_id) || csad.action_code index_cols
FROM CS_SR_ACTION_TRIGGERS csat,
CS_SR_ACTION_DETAILS csad,
CS_SR_EVENT_CODES_B cec
WHERE
cec.WF_BUSINESS_EVENT_ID = l_event_name
and csat.EVENT_CODE = cec.EVENT_CODE
and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(csat.start_date_active, SYSDATE))
and TRUNC(NVL(csat.end_date_active, SYSDATE))
and csad.event_condition_id = csat.event_condition_id
and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(csad.start_date_active, SYSDATE))
and TRUNC(NVL(csad.end_date_active, SYSDATE))
and csat.from_to_status_code IS NULL
and csad.action_code NOT like 'NOTIFY%'
and (csad.incident_status_id IS NOT NULL OR
csad.resolution_code IS NOT NULL)
and csat.relationship_type_id IN
( select cil.link_type_id
FROM cs_incident_links cil
WHERE cil.subject_id = l_request_id)
AND TO_CHAR(csad.event_condition_id) || csad.action_code >
TO_CHAR(l_event_condition_id) || l_action_code
UNION
SELECT csad.event_condition_id,
csad.action_code,
to_char(csad.event_condition_id) || csad.action_code index_cols
FROM CS_SR_ACTION_TRIGGERS csat,
CS_SR_ACTION_DETAILS csad,
CS_SR_EVENT_CODES_B cec
WHERE
cec.WF_BUSINESS_EVENT_ID = l_event_name
and csat.EVENT_CODE = cec.EVENT_CODE
and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(csat.start_date_active, SYSDATE))
and TRUNC(NVL(csat.end_date_active, SYSDATE))
and csad.event_condition_id = csat.event_condition_id
and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(csad.start_date_active, SYSDATE))
and TRUNC(NVL(csad.end_date_active, SYSDATE))
and csat.from_to_status_code IS NOT NULL
and csat.relationship_type_id IS NULL
and csat.incident_status_id IS NOT NULL
and csad.action_code NOT like 'NOTIFY%'
and csad.relationship_type_id IN
( select cil.link_type_id
FROM cs_incident_links cil
WHERE cil.subject_id = l_request_id)
AND TO_CHAR(csad.event_condition_id) || csad.action_code >
TO_CHAR(l_event_condition_id) || l_action_code
ORDER BY index_cols;
l_update_conditions_list := WF_ENGINE.GetItemAttrText(
itemtype => itemtype,
itemkey => itemkey,
aname => 'UPDATE_CONDITION_LIST' );
l_update_actions_list := WF_ENGINE.GetItemAttrText(
itemtype => itemtype,
itemkey => itemkey,
aname => 'UPDATE_ACTION_LIST' );
IF (l_update_conditions_list IS NOT NULL AND l_update_actions_list IS NOT NULL) THEN
result := 'COMPLETE:N';
aname => 'MORE_UPDATE_ACTION_LIST');
aname => 'MORE_UPDATE_ACTION_LIST',
avalue => '' );
aname => 'UPDATE_EVENT_CONDITION_ID');
aname => 'UPDATE_ACTION_CODE');
IF (nvl(LENGTH(l_update_conditions_list),0) +
nvl(LENGTH(l_char_event_condition_id),0) + 1) <= 4000 OR
(nvl(LENGTH(l_update_actions_list),0) +
nvl(LENGTH(sel_action_rec.action_code),0) + 1 ) <= 4000 THEN
IF l_update_conditions_list IS NULL THEN
l_update_conditions_list := l_char_event_condition_id;
l_update_actions_list := sel_action_rec.action_code;
l_update_conditions_list := l_update_conditions_list || ' ' || l_char_event_condition_id;
l_update_actions_list := l_update_actions_list || ' ' || sel_action_rec.action_code;
aname => 'MORE_UPDATE_ACTION_LIST',
avalue => 'Y' );
IF (l_update_conditions_list IS NOT NULL AND l_update_actions_list IS NOT NULL) THEN
WF_ENGINE.SetItemAttrText(
itemtype => itemtype,
itemkey => itemkey,
aname => 'UPDATE_CONDITION_LIST',
avalue => l_update_conditions_list );
aname => 'UPDATE_ACTION_LIST',
avalue => l_update_actions_list );
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Verify_Update_Rules_Done',
itemtype, itemkey, actid, funmode);
END Verify_Update_Rules_Done;
SELECT *
FROM CS_INCIDENTS_WORKFLOW_V
WHERE INCIDENT_NUMBER = l_request_number;
SELECT INC.INCIDENT_ID,
INC.SUMMARY,
INC.INCIDENT_OWNER_ID,
INC.INVENTORY_ITEM_ID,
INC.EXPECTED_RESOLUTION_DATE,
INC.INCIDENT_DATE,
INC.CUSTOMER_PRODUCT_ID,
TYPE.NAME INCIDENT_TYPE,
SEVERITY.NAME SEVERITY,
STATUS.NAME STATUS_CODE,
URGENCY.NAME URGENCY,
RA2.PARTY_NAME CUSTOMER_NAME,
CSLKUP.DESCRIPTION PROBLEM_CODE_DESCRIPTION,
MTL.DESCRIPTION PRODUCT_DESCRIPTION,
INC.PROBLEM_CODE --5245018
FROM CS_INCIDENTS_ALL_VL INC,
--CS_INCIDENT_TYPES_VL TYPE,
CS_INCIDENT_TYPES_TL TYPE,
--CS_INCIDENT_SEVERITIES_VL SEVERITY,
CS_INCIDENT_SEVERITIES_TL SEVERITY,
CS_INCIDENT_STATUSES_VL STATUS,
--CS_INCIDENT_URGENCIES_VL URGENCY,
CS_INCIDENT_URGENCIES_TL URGENCY,
HZ_PARTIES RA2,
CS_LOOKUPS CSLKUP,
--MTL_SYSTEM_ITEMS_VL MTL
MTL_SYSTEM_ITEMS_TL MTL
WHERE INC.INCIDENT_NUMBER = l_request_number
AND INC.INCIDENT_TYPE_ID = TYPE.INCIDENT_TYPE_ID
AND TYPE.LANGUAGE = userenv('LANG')
AND INC.INCIDENT_STATUS_ID = STATUS.INCIDENT_STATUS_ID
AND INC.INCIDENT_URGENCY_ID = URGENCY.INCIDENT_URGENCY_ID(+)
AND URGENCY.LANGUAGE(+) = userenv('LANG')
AND INC.CUSTOMER_ID = RA2.PARTY_ID(+)
AND INC.INCIDENT_SEVERITY_ID = SEVERITY.INCIDENT_SEVERITY_ID
AND SEVERITY.LANGUAGE(+) = userenv('LANG')
AND INC.PROBLEM_CODE = CSLKUP.LOOKUP_CODE(+)
AND CSLKUP.LOOKUP_TYPE(+) = 'REQUEST_PROBLEM_CODE'
AND MTL.INVENTORY_ITEM_ID(+) = INC.INVENTORY_ITEM_ID
AND MTL.LANGUAGE(+) = userenv('LANG')
AND (MTL.ORGANIZATION_ID = CS_STD.Get_Item_Valdn_Orgzn_ID OR MTL.ORGANIZATION_ID IS NULL);
SELECT emp.source_id
FROM jtf_rs_resource_extns emp
WHERE emp.resource_id = l_incident_owner_id;
select emp.source_id , emp.resource_name
from jtf_rs_resource_extns_vl emp
where emp.resource_id = p_resource_id;
SELECT display_name,expiration_date
FROM wf_local_roles
WHERE name = c_role_name;
wf_core.context( pkg_name => 'CS_WF_AUTO_NTFY_UPDATE_PKG',
proc_name => 'Get_Request_Attributes',
arg1 => 'p_employee_id=>'|| l_get_source_id_rec.source_id);
raised, the event is placed in the WF_DEFERRED queue. If the SR is updated
before the event is de-queued, these attributes would reflect the latest data,
including those which were changed, and not those when the SR was created.
The disadvantage of adding this as part of the parameter list is the per-
formance cost. The more parameters are added when raising the event, the
more theperformance deteriorates (as pointed out by the Workflow team. max.
number of parameters in an event is 100).
*******/
-- Initialize item attributes that will remain constant
WF_ENGINE.SetItemAttrDate(
itemtype => 'SERVEREQ',
itemkey => itemkey,
aname => 'REQUEST_DATE',
avalue => l_ServiceRequest_rec.incident_date );
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Get_Request_Attributes',
itemtype, itemkey, actid, funmode);
SELECT employee_id INTO l_employee_id
FROM fnd_user
WHERE user_id = p_fnd_user_id;
select b.interaction_id
from jtf_ih_activities a,
jtf_ih_interactions b
where a.doc_ref='SR'
and a.doc_id = c_incident_id
and a.interaction_id = b.interaction_id
and b.active = 'Y';
select outcome_id,
outcome_short_desc,
result_required,
result_id,
result_short_desc,
reason_required,
reason_id,
reason_short_desc
from jtf_ih_wrap_ups_vl
where wrap_id = c_wrap_id;
SELECT nvl(action_item_id,0)
FROM jtf_ih_action_items_vl
--WHERE action_item = 'Sr'
WHERE action_item = l_action_item
AND rownum < 2;
SELECT nvl(action_id,0)
FROM jtf_ih_actions_vl
--WHERE action = 'Automated Email Sent'
WHERE action = l_action
AND rownum < 2;
SELECT incident_number
FROM cs_incidents_all_b
WHERE incident_id = p_incident_id;
SELECT PARTY_NAME
FROM HZ_PARTIES
WHERE
PARTY_ID = p_party_id;
SELECT a.resource_id
into l_interaction_rec.resource_id
FROM jtf_rs_resource_extns a
WHERE a.user_id = l_user_id;
FND_MESSAGE.Set_Token('API_NAME', 'CS_WF_AUTO_NTFY_UPDATE_PKG.Create_Interaction_Activity');
FND_MSG_PUB.Add_Exc_Msg('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Create_Interaction_Activity');
SELECT display_name,expiration_date
FROM wf_local_roles
WHERE name = c_user_name;
SELECT display_name,expiration_date
FROM wf_local_roles
WHERE name = c_role_name;
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION')) THEN
dbg_msg := ('In CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION Procedure');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION')) THEN
dbg_msg := ('l_request_id : '||l_request_id);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION')) THEN
dbg_msg := ('Processing HTML Notification following set of attributes');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION')) THEN
dbg_msg := ('Calling CS_WF_AUTO_NTFY_UPDATE_PKG.Set_HTML_Notification_Details Procedure');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION')) THEN
dbg_msg := ('After Calling CS_WF_AUTO_NTFY_UPDATE_PKG.Set_HTML_Notification_Details Procedure');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION')) THEN
dbg_msg := ('Prepare_HTML_Notification Proc Result : '||result);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.PREPARE_HTML_NOTIFICATION', dbg_msg);
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Send_HTML_Notification',
itemtype, itemkey, actid, funmode);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.SET_HTML_NOTIFICATION_DETAILS')) THEN
dbg_msg := ('In CS_WF_AUTO_NTFY_UPDATE_PKG.Set_HTML_Notification_Details Procedure');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.SET_HTML_NOTIFICATION_DETAILS', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.SET_HTML_NOTIFICATION_DETAILS')) THEN
dbg_msg := ('l_mail_pref : '||l_mail_pref);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.SET_HTML_NOTIFICATION_DETAILS', dbg_msg);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.SET_HTML_NOTIFICATION_DETAILS', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.SET_HTML_NOTIFICATION_DETAILS')) THEN
dbg_msg := ('l_count : '||l_count||' l_count1'||l_count1);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.SET_HTML_NOTIFICATION_DETAILS', dbg_msg);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.SET_HTML_NOTIFICATION_DETAILS', dbg_msg);
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Verify_Notify_Rules_Done',
p_itemtype, p_itemkey, p_actid, p_funmode);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.ARE_ALL_HTML_RECIPS_NOTIFIED')) THEN
dbg_msg := ('In CS_WF_AUTO_NTFY_UPDATE_PKG.Are_All_HTML_Recips_Notified Procedure');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.ARE_ALL_HTML_RECIPS_NOTIFIED', dbg_msg);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_WF_AUTO_NTFY_UPDATE_PKG.ARE_ALL_HTML_RECIPS_NOTIFIED', dbg_msg);
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Are_All_HTML_Recips_Notified',
itemtype, itemkey, actid, funmode);
SELECT party_role_code
FROM cs_party_role_group_maps
WHERE party_role_group_code = l_party_role_group_code
AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
and trunc(nvl(end_date_active,sysdate));
WF_CORE.Context('CS_WF_AUTO_NTFY_UPDATE_PKG', 'Create_Role_List',
p_itemtype, p_itemkey, p_actid, p_funmode);