1 Package Body CUG_LAUNCH_WORKFLOW_PVT AS
2 /* $Header: CUGWFLNB.pls 120.2 2007/12/06 05:09:06 gasankar noship $ */
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter procedure, function bodies as shown below
13
14 G_PKG_NAME CONSTANT VARCHAR2(30) := 'cug_launch_workflow_pvt';
15
16
17 PROCEDURE launch_workflow (
18 p_api_version IN NUMBER ,
19 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
20 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
21 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL ,
22 x_return_status OUT NOCOPY VARCHAR2 ,
23 x_msg_count OUT NOCOPY NUMBER ,
24 x_msg_data OUT NOCOPY VARCHAR2 ,
25 p_incident_id IN NUMBER ,
26 p_source IN VARCHAR2 DEFAULT NULL ,
27 p_incident_status_id IN NUMBER ,
28 p_initiator_user_id IN NUMBER DEFAULT NULL ,
29 p_initiator_resp_id IN NUMBER DEFAULT NULL ,
30 p_initiator_resp_appl_id IN NUMBER DEFAULT NULL
31 )
32
33
34 IS
35
36 l_api_name CONSTANT VARCHAR2(30) := 'launch_workflow' ;
37 l_api_version CONSTANT NUMBER := 2.0 ;
38
39 l_itemkey VARCHAR2(240);
40 l_wf_process_id NUMBER;
41 l_initiator_role VARCHAR2(100);
42 l_initiator_display_name VARCHAR2(240);
43
44 CURSOR l_servereq_csr IS
45 SELECT CSI.incident_number,CSI.workflow_process_id,CSI.incident_type_id,CST.name,CST.workflow,
46 CST.autolaunch_workflow_flag
47 FROM cs_incidents_all_b CSI, cs_incident_types_vl CST
48 WHERE CSI.incident_id = p_incident_id
49 AND CST.incident_type_id = CSI.incident_type_id
50 FOR UPDATE OF workflow_process_id NOWAIT;
51
52 l_servereq_csr_rec l_servereq_csr%ROWTYPE;
53
54 BEGIN
55 -- Standard start of API savepoint
56 -- SAVEPOINT launch_workflow_pvt;
57
58
59 -- Standard call to check for call compatibility
60 IF NOT FND_API.Compatible_API_Call(
61 l_api_version ,
62 p_api_version ,
63 l_api_name ,
64 G_PKG_NAME )
65 THEN
66 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
67 END IF;
68
69 --Initialize message listif p_init_msg_list is set to TRUE.
70 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
71 FND_MSG_PUB.initialize;
72 END IF;
73
74 -- Initialize API return status to success
75 x_return_status := FND_API.G_RET_STS_SUCCESS;
76
77 -- API body
78
79 -- The workflow is to be launched only after the
80 -- service request has been commited
81 -- (make sure the current record is commited before launching the workflow.
82 -- This is necessary because the workflow process needs to obtain a lock on the record) and
83 -- the status of the service request should be OPEN
84
85
86
87 IF (p_incident_id IS NOT NULL AND p_incident_status_id IS NOT NULL) THEN
88
89 OPEN l_servereq_csr;
90 -- LOOP
91 FETCH l_servereq_csr INTO l_servereq_csr_rec;
92 -- EXIT WHEN l_servereq_csr%NOTFOUND;
93
94 -- Construct the unique item key
95 SELECT cs_wf_process_id_s.NEXTVAL INTO l_wf_process_id FROM DUAL;
96 l_itemkey := l_servereq_csr_rec.incident_number || '-' || to_char(l_wf_process_id);
97
98 -- Update the workflow process ID of the request
99 IF TO_NUMBER(FND_PROFILE.VALUE('USER_ID')) IS NOT NULL THEN
100 UPDATE CS_INCIDENTS_ALL_B
101 SET workflow_process_id = l_wf_process_id,
102 last_updated_by = TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
103 last_update_date = sysdate
104 WHERE CURRENT OF l_ServeReq_csr;
105 ELSE
106 UPDATE CS_INCIDENTS_ALL_B
107 SET workflow_process_id = l_wf_process_id,
108 last_update_date = sysdate
109 WHERE CURRENT OF l_ServeReq_csr;
110 END IF;
111
112 COMMIT ; --GASANKAR
113
114 -- Create and launch the Workflow process if the status of the SR is OPEN
115 IF (p_source = 'FORM') THEN
116 -- AND (l_servereq_csr_rec.autolaunch_workflow_flag = 'Y') THEN
117
118 wf_engine.CreateProcess (Itemtype => 'SERVEREQ',
119 Itemkey => l_itemkey,
120 process => l_servereq_csr_rec.workflow);
121
122 wf_engine.startprocess (itemtype => 'SERVEREQ',
123 itemkey => l_itemkey);
124
125 -- Launch the workflow to get customer approval if the package is being called from workflow
126 ELSIF (p_source IS NULL) THEN
127
128 wf_engine.CreateProcess (Itemtype => 'SERVEREQ',
129 Itemkey => l_itemkey,
130 process => 'CUG_GENERIC_WORKFLOW');
131
132 wf_engine.startprocess (itemtype => 'SERVEREQ',
133 itemkey => l_itemkey);
134
135 END IF;
136
137
138
139 COMMIT;
140 -- END LOOP;
141 -- CLOSE l_ServeReq_csr;
142
143 END IF;
144
145 -- Endof API body.
146
147 -- Standard check for p_commit.
148
149 IF FND_API.To_Boolean ( p_commit ) THEN
150 COMMIT WORK;
151 END IF;
152
153
154 -- Standard call to get messgage count and if count is 1, get message info.
155
156 FND_MSG_PUB.Count_And_get (
157 p_count => x_msg_count,
158 p_data => x_msg_data
159 );
160
161
162 EXCEPTION
163
164 WHEN FND_API.G_EXC_ERROR THEN
165 IF (l_servereq_csr%ISOPEN) THEN
166 CLOSE l_servereq_csr;
167 END IF;
168 -- ROLLBACK TO launch_workflow_pvt;
169 x_return_status := FND_API.G_RET_STS_ERROR;
170 FND_MSG_PUB.Count_And_Get
171 (
172 p_count => x_msg_count,
173 p_data => x_msg_data
174 );
175
176 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
177 IF (l_servereq_csr%ISOPEN) THEN
178 CLOSE l_servereq_csr;
179 END IF;
180 -- ROLLBACK TO launch_workflow_pvt;
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
182 FND_MSG_PUB.Count_And_Get
183 (
184 p_count => x_msg_count,
185 p_data => x_msg_data
186 );
187
188 WHEN OTHERS THEN
189 IF (l_servereq_csr%ISOPEN) THEN
190 CLOSE l_servereq_csr;
191 END IF;
192 -- ROLLBACK TO launch_workflow_pvt;
193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
194 IF FND_MSG_PUB.Check_Msg_Level
195 ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
196 THEN
197 FND_MSG_PUB.Add_Exc_Msg
198 (
199 G_PKG_NAME,
200 l_api_name
201 );
202 END IF;
203 FND_MSG_PUB.Count_And_Get
204 (
205 p_count => x_msg_count,
206 p_data => x_msg_data
207 );
208
209 END launch_workflow;
210
211 END;
[Home] [Help]