[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
oracle.apps.cs.sr.ServiceRequest.updated .
These subscriptions executes the old BES converted seeded workflow CALL
SUPPORT, as well as any client custom BES workflows. Also, these subscriptions
are executed Synchronously, the same as the old seeded workflows. This is
important since the calling wrapper API needs to update the workflow_process_id
if the workflow was launched.
If this custom rule function is not able to launch the workflow process
because the proces is not BES compatible, then the CS event wrapper API
Raise_ServiceRequest_Event() will try to launch the workflow process using
the old non-BES workflow API calls, i.e., CreateProcess(), StartProcess() .
This provides backward compatibility for those clients who has custom
workflow processes but has not converted to BES.
***************************************************************************************/
FUNCTION CS_Custom_Rule_Func (p_subscription_guid in raw,
p_event in out nocopy WF_EVENT_T) return varchar2 is
l_event_name VARCHAR2(240) := p_event.getEventName( );
SELECT nvl(status.close_flag,'N') close_flag,
inc.workflow_process_id,
cit.AUTOLAUNCH_WORKFLOW_FLAG,
cit.WORKFLOW,
inc.resource_type,
inc.incident_owner_id,
inc.incident_id,
inc.object_version_number
FROM cs_incident_statuses status,
cs_incidents_all_b inc,
cs_incident_types cit
WHERE inc.incident_number = l_request_number
AND inc.incident_status_id = status.incident_status_id
and cit.incident_type_id = inc.incident_type_id;
SELECT end_date
FROM wf_items
WHERE item_type = 'SERVEREQ'
AND item_key like l_request_number||'-%'
AND item_key NOT like l_request_number||'%EVT'
AND end_date IS NULL;
select wfa.name
from WF_ACTIVITIES wfa
where wfa.item_type = 'SERVEREQ'
and wfa.type ='EVENT'
and wfa.name = l_wf_proc_activity_name;
select wfpa.ACTIVITY_NAME
from wf_process_activities wfpa
where wfpa.PROCESS_ITEM_TYPE = 'SERVEREQ'
and wfpa.PROCESS_NAME = l_process_name
and wfpa.START_END = 'START'
ORDER BY wfpa.process_version DESC;
WHEN l_UPDATE_FAILED THEN
IF sel_workflow_csr%ISOPEN THEN
CLOSE sel_workflow_csr;
p_USER_ID IN NUMBER DEFAULT FND_GLOBAL.USER_ID, -- p_last_updated_by from Update_ServiceREquest()
p_RESP_ID IN NUMBER, -- p_resp_id from Update_ServiceREquest()
p_RESP_APPL_ID IN NUMBER, -- p_resp_appl_id from Update_ServiceREquest()
p_Old_SR_Rec IN CS_ServiceRequest_PVT.service_request_rec_type,
p_New_SR_Rec IN CS_ServiceRequest_PVT.service_request_rec_type,
p_Contacts_Table IN CS_ServiceRequest_PVT.contacts_table,
p_Link_Rec IN CS_INCIDENTLINKS_PVT.CS_INCIDENT_LINK_REC_TYPE,
p_wf_process_id IN NUMBER, -- from Update_ServiceRequest() parameter list, important
-- to pass this to prevent unwanted recursive calls
p_owner_id IN NUMBER, -- passed by CIC
p_wf_manual_launch IN VARCHAR2 , -- flag for event raised from UI launch_wf().
-- p_Event_Code for manually launched workflow
-- should always be UPDATE_SERVICE_REQUEST
x_wf_process_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_dummy VARCHAR2(240);
SELECT P.PARTY_NAME
FROM HZ_PARTIES P,
CS_HZ_SR_CONTACT_POINTS C
WHERE P.PARTY_ID = l_contact_party_id AND C.CONTACT_TYPE <> 'EMPLOYEE' AND C.PARTY_ID= P.PARTY_ID
UNION
SELECT P.FULL_NAME
FROM PER_ALL_PEOPLE_F P
WHERE P.PERSON_ID = l_contact_party_id AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(P.EFFECTIVE_START_DATE, SYSDATE))
AND TRUNC(NVL(P.EFFECTIVE_END_DATE, SYSDATE))
AND EXISTS (SELECT 1
FROM CS_HZ_SR_CONTACT_POINTS C
WHERE C.CONTACT_TYPE = 'EMPLOYEE'
AND C.PARTY_ID = P.PERSON_ID) ;
SELECT p.PARTY_NAME
FROM HZ_PARTIES p, cs_hz_sr_contact_points c
WHERE p.PARTY_ID = c.party_id and
c.contact_type <> 'EMPLOYEE' and c.party_id=l_contact_party_id
UNION
select p.full_name
from per_all_people_f p, cs_hz_sr_contact_points c
where p.person_id = c.party_id
and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(p.effective_start_date, SYSDATE))
and TRUNC(NVL(p.effective_end_date, SYSDATE))
and c.contact_type = 'EMPLOYEE' and c.party_id=l_contact_party_id;
SELECT nvl(status.close_flag,'N') close_flag,
inc.workflow_process_id,
cit.AUTOLAUNCH_WORKFLOW_FLAG,
cit.WORKFLOW,
inc.resource_type,
inc.incident_owner_id,
inc.incident_id,
inc.object_version_number
FROM cs_incident_statuses status,
cs_incidents_all_b inc,
cs_incident_types cit
WHERE inc.incident_number = p_Incident_Number
AND inc.incident_status_id = status.incident_status_id
and cit.incident_type_id = inc.incident_type_id;
select emp.source_id , emp.resource_name
from jtf_rs_resource_extns_vl emp
where emp.resource_id = p_resource_id;
to the update_servicerequest() api. This prevents another
seeded workflow from launching by looking if one is already
running.
**/
CURSOR l_servereq_csr IS
SELECT end_date,item_key
FROM wf_items
WHERE item_type = 'SERVEREQ'
AND item_key like p_Incident_Number||'-%'
AND item_key NOT like p_Incident_Number||'%EVT'
AND end_date IS NULL;
SELECT display_name,expiration_date
FROM wf_local_roles
WHERE name = c_role_name;
SELECT name
FROM cs_party_roles_vl
WHERE party_role_code = p_party_role_code;
SELECT csat.INCIDENT_STATUS_ID
--INTO l_status_id
FROM CS_SR_ACTION_TRIGGERS csat,
CS_SR_ACTION_DETAILS csad,
CS_SR_EVENT_CODES_B cec
WHERE
cec.WF_BUSINESS_EVENT_ID = 'oracle.apps.cs.sr.ServiceRequest.statuschanged'
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 csad.resolution_code IS NULL
and csat.incident_status_id IS NOT NULL
and csat.incident_status_id = (select incident_status_id from cs_incidents_all_b
where incident_number = p_incident_number)
and csad.action_code like 'NOTIFY%';
CS_WF_AUTO_NTFY_UPDATE_PKG.get_fnd_user_role
( p_fnd_user_id => p_USER_ID,
x_role_name => l_initiator_role,
x_role_display_name => l_dummy );
SELECT name
INTO l_old_request_status
FROM cs_incident_statuses_vl
WHERE INCIDENT_STATUS_ID = p_Old_SR_Rec.STATUS_ID;
SELECT name
INTO l_link_type_name
FROM CS_SR_LINK_TYPES_VL
WHERE link_type_id = p_Link_Rec.LINK_TYPE_ID;
IF (p_Event_Code = 'UPDATE_SERVICE_REQUEST') THEN
/* Roopa - begin
Fix for bug # 2809232
*/
wf_event.AddParameterToList(p_name => 'PREV_TYPE_ID',
p_value => p_Old_SR_Rec.type_id,
p_parameterlist => l_param_list);
SELECT cs_wf_process_id_s.nextval
INTO l_event_id
FROM dual;
SELECT cs_wf_process_id_s.nextval
INTO l_event_id
FROM dual;
from the SR api we need to interpret the update condition and raise this
event accordingly. inserting the following if .
Roopa - end
***********************************************/
IF (l_new_contact_point_name IS NOT NULL) THEN
SELECT cs_wf_process_id_s.nextval
INTO l_event_id
FROM dual;
SELECT cs_wf_process_id_s.nextval
INTO l_event_id
FROM dual;
l_business_event := 'oracle.apps.cs.sr.ServiceRequest.updated';
ELSIF (p_Event_Code = 'RELATIONSHIP_DELETE_FOR_SR') THEN
IF (p_Link_Rec.LINK_TYPE_ID IS NOT NULL) THEN
/* Roopa - Fix for bug 3528510 */
/* The following 2 parameters are added to the payload so that
the business event wf will catch the non-SR -> SR link scenario
*/
wf_event.AddParameterToList(p_name => 'LINK_SUBJECT_TYPE',
p_value => p_link_rec.subject_type,
p_parameterlist => l_param_list);
l_business_event := 'oracle.apps.cs.sr.ServiceRequest.relationshipdeleted';
we need to interpret the update condition and raise this
event accordingly. Commenting out the following elsif
Roopa - end*/
-- ELSIF (p_Event_Code = 'ADD_NEW_CONTACT_TO_SR') THEN
-- IF (l_new_contact_point_name IS NOT NULL) THEN
-- l_business_event := 'oracle.apps.cs.sr.ServiceRequest.newcontactadded';
SELECT cs_wf_process_id_s.nextval
INTO l_event_id
FROM dual;
l_param_list.DELETE;
IF (p_Event_Code = 'CREATE_SERVICE_REQUEST' OR p_Event_Code = 'UPDATE_SERVICE_REQUEST') THEN
OPEN l_sel_request_csr;
-- update SR table with the workflow process ID used in the event.
-- If not (workflow process may not be BES converted), try to launch
-- the workflow using the old workflow APIs.
OPEN l_servereq_csr;
It was decided that Update_ServiceRequest API need not be called
just to update the workflow process id of the SR that too with full validation on.
An explicit update should suffice. Hence commenting out the following code and
replacing it with an explicit update statement
*/
l_pos := INSTR(l_itemkey, '-',-1,1); -- Bug#4007088
UPDATE CS_INCIDENTS_ALL_B set WORKFLOW_PROCESS_ID = x_wf_process_id
WHERE INCIDENT_ID = l_sel_request_rec.incident_id;
-- Update/Create SR api, or any other API. This is only
-- set to 'Y' when called from the tools menu of the SR UI.
--
p_wf_manual_launch => 'N',
p_workflow_process_id => out_wf_process_id,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
END IF; -- IF (p_Event_Code='CREATE_SERVICE_REQUEST' OR 'UPDATE_SERVICE_REQUEST')
l_param_list.DELETE;
SELECT employee_id INTO l_employee_id
FROM fnd_user
WHERE user_id = p_fnd_user_id;