[Home] [Help]
PACKAGE BODY: APPS.PA_FORECAST_ITEMS_GEN_WF_PKG
Source
1 PACKAGE BODY PA_FORECAST_ITEMS_GEN_WF_PKG AS
2 /* $Header: PARFIWFB.pls 120.1 2005/08/19 16:51:56 mwasowic noship $ */
3 l_cannot_acquire_lock EXCEPTION;
4 ------------------------------------------------------------------------------------------------------------------
5 -- This procedure will launch the work flow for forecast item generation.
6 -- Input parameters
7 -- Parameters Type Required Description
8 -- p_assignment_id NUMBER YES It store the assignment id
9 -- p_resource_id NUMBER YES It store the resource id
10 -- p_asgmt_start_date DATE YES It store the assignment start date
11 -- p_asgmt_end_date DATE YES It store the assignment end date
12 -- p_action_mode VARCHAR2 YES It store the action mode i.e. MODIFY OR DELETE
13 --
14 -- Out parameters
15 --
16 --------------------------------------------------------------------------------------------------------------------
17 PROCEDURE Launch_WorkFlow_Fi_Gen ( p_assignment_id IN NUMBER,
18 p_resource_id IN NUMBER,
19 p_start_date IN DATE,
20 p_end_date IN DATE,
21 p_process_mode IN VARCHAR2,
22 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
23 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
24 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
25
26 IS
27
28 l_item_type VARCHAR2(8):='PARFIGEN';
29 l_item_key VARCHAR2(10);
30 l_result VARCHAR2(10);
31 l_x_return_status VARCHAR2(50);
32 l_lock_for VARCHAR2(5);
33 l_x_msg_count NUMBER;
34 l_x_msg_data VARCHAR2(50);
35 l_name pa_organizations_expend_v.name%TYPE;
36 l_save_thresh NUMBER ;
37 l_project_id NUMBER;
38 l_wf_type_code VARCHAR2(30);
39 l_err_code NUMBER := 0;
40 l_err_stage VARCHAR2(2000);
41 l_err_stack VARCHAR2(2000);
42
43 BEGIN
44 x_return_status := FND_API.G_RET_STS_SUCCESS;
45
46 -- User lock for the given assignment id
47 /* l_lock_for := 'ASGMT'; Fixed bug#1612856
48 IF ( NVL(p_assignment_id,0) <> 0 ) THEN
49 IF (PA_FORECAST_ITEMS_UTILS.Set_User_Lock (p_assignment_id,l_lock_for) <> 0) THEN
50 RAISE l_cannot_acquire_lock;
51 END IF;
52 END IF; */
53
54
55
56 -- Taking unique id for the work flow
57 SELECT 'FI-' || TO_CHAR(wf_forecast_item_gen_s.NEXTVAL)
58 INTO l_item_key
59 FROM DUAL;
60
61
62 l_wf_type_code := 'FORECAST_GENERATION';
63
64 IF NVL(p_assignment_id,0) <> 0 THEN
65
66 BEGIN
67
68 SELECT project_id INTO l_project_id
69 FROM pa_project_assignments
70 WHERE assignment_id = p_assignment_id;
71
72
73 EXCEPTION
74
75 WHEN no_data_found THEN
76 -- In delete assignment the assignment will not exist
77 -- in pa_project_assignments
78 l_project_id := null;
79 l_wf_type_code := 'FORECAST_DELETION';
80 null;
81 END;
82
83 END IF;
84
85 -- Setting thresold value to run the process in background
86 l_save_thresh := wf_engine.threshold ;
87
88 IF wf_engine.threshold < 0 THEN
89 wf_engine.threshold := l_save_thresh ;
90 END IF;
91 wf_engine.threshold := -1 ;
92 IF p_assignment_id IS NOT NULL AND p_resource_id IS NULL THEN
93 -- Selecting orgnization name to initialize the work flow attribute
94
95 BEGIN
96
97 /* Bug remmed out for bug 1777250 due to perf team request
98
99 SELECT name
100 INTO l_name
101 FROM pa_organizations_expend_v
102 WHERE organization_id = ( select expenditure_organization_id
103 FROM pa_project_assignments
104 WHERE assignment_id = p_assignment_id);
105
106 and instead used the select statement below note that also called the org_name
107 translation function.
108 */
109
110 select pa_expenditures_utils.GetOrgTlName(expenditure_organization_id)
111 into l_name
112 from pa_project_assignments
113 where assignment_id = p_assignment_id;
114
115 EXCEPTION
116
117 WHEN NO_DATA_FOUND THEN
118 null;
119 END;
120
121 ELSIF p_resource_id IS NOT NULL THEN
122
123 IF p_start_date IS NOT NULL AND p_end_date IS NOT NULL THEN
124
125 BEGIN
126
127 SELECT pa_expenditures_utils.GetOrgTlName(resource_organization_id)
128 INTO l_name
129 from pa_resources_denorm
130 WHERE resource_Id = p_resource_id
131 AND rownum = 1
132 AND ((trunc(p_start_date) BETWEEN
133 trunc(resource_effective_start_date) AND
134 NVL(resource_effective_end_date,SYSDATE+1))
135 OR (trunc( p_end_date) BETWEEN
136 trunc(resource_effective_start_date) AND
137 NVL(resource_effective_end_date,SYSDATE+1))
138 OR ( trunc(p_start_date) <
139 trunc(resource_effective_start_date) AND
140 trunc(p_end_date) >
141 NVL(resource_effective_end_date,SYSDATE+1)))
142 ORDER BY resource_effective_start_date;
143
144
145 EXCEPTION
146
147 WHEN NO_DATA_FOUND THEN
148 NULL;
149 END;
150
151 ELSIF p_start_date IS NOT NULL THEN
152
153 BEGIN
154
155 SELECT pa_expenditures_utils.GetOrgTlName(resource_organization_id)
156 INTO l_name
157 FROM pa_resources_denorm
158 WHERE resource_Id = p_resource_id
159 AND rownum = 1
160 AND trunc(p_start_date) BETWEEN trunc(resource_effective_start_date)
161 AND NVL(resource_effective_end_date,SYSDATE+1)
162 ORDER BY resource_effective_start_date;
163
164 EXCEPTION
165
166 WHEN NO_DATA_FOUND THEN
167 NULL;
168
169 END;
170
171 END IF;
172
173 END IF;
174
175 -- dbms_output.put_line('Create the process ');
176
177 -- Creating the work flow process
178 WF_ENGINE.CreateProcess( itemtype => l_item_type,
179 itemkey => l_item_key,
180 process => 'PA_FORECAST_ITEM_GEN') ;
181
182 -- dbms_output.put_line('Set the attribute 1');
183
184 -- Setting the attribute value for assignment id
185 WF_ENGINE.SetItemAttrNumber( itemtype => l_item_type,
186 itemkey => l_item_key,
187 aname => 'ASSIGNMENT_ID',
188 avalue => p_assignment_id);
189
190 -- Setting the attribute value for resource id
191 -- dbms_output.put_line('Set the attribute 2');
192 WF_ENGINE.SetItemAttrNumber( itemtype => l_item_type,
193 itemkey => l_item_key,
194 aname => 'RESOURCE_ID',
195 avalue => p_resource_id);
196
197 -- Setting the attribute value for asignment start date
198 -- dbms_output.put_line('Set the attribute 3');
199 WF_ENGINE.SetItemAttrDate( itemtype => l_item_type,
200 itemkey => l_item_key,
201 aname => 'ASGMT_START_DATE',
202 avalue => p_start_date);
203
204 -- Setting the attribute value for assignment end date
205 -- dbms_output.put_line('Set the attribute 4');
206 WF_ENGINE.SetItemAttrDate( itemtype => l_item_type,
207 itemkey => l_item_key,
208 aname => 'ASGMT_END_DATE',
209 avalue => p_end_date);
210
211 -- Setting the attribute value for process mode
212 -- dbms_output.put_line('Set the attribute 5');
213 WF_ENGINE.SetItemAttrText( itemtype => l_item_type,
214 itemkey => l_item_key,
215 aname => 'PROCESS_MODE',
216 avalue => p_process_mode);
217
218 -- Setting the attribute value for orgnization name
219 -- dbms_output.put_line('Set the attribute 6');
220 WF_ENGINE.SetItemAttrText( itemtype => l_item_type,
221 itemkey => l_item_key,
222 aname => 'ORGANIZATION_NAME',
223 avalue => l_name );
224
225 -- Setting the attribute value for Project Resource Administrator
226 -- dbms_output.put_line('Set the attribute 7');
227 WF_ENGINE.SetItemAttrText ( itemtype => l_item_type,
228 itemkey => l_item_key,
229 aname => 'PROJECT_RESOURCE_ADMINISTRATOR',
230 avalue => 'PASYSADMIN');
231
232 -- dbms_output.put_line('Start the process ');
233
234 -- Starting the work flow process and calling work flow api internaly
235 -- dbms_output.put_line('Set the attribute 8');
236 WF_ENGINE.StartProcess( itemtype => l_item_type,
237 itemkey => l_item_key);
238
239
240 IF p_assignment_id IS NOT NULL THEN
241
242 PA_WORKFLOW_UTILS.Insert_WF_Processes
243 (p_wf_type_code => l_wf_type_code
244 ,p_item_type => l_item_type
245 ,p_item_key => l_item_key
246 ,p_entity_key1 => to_char(l_project_id)
247 ,p_entity_key2 => to_char(p_assignment_id)
248 ,p_description => NULL
249 ,p_err_code => l_err_code
250 ,p_err_stage => l_err_stage
251 ,p_err_stack => l_err_stack
252 );
253 ELSE
254
255 PA_WORKFLOW_UTILS.Insert_WF_Processes
256 (p_wf_type_code => l_wf_type_code
257 ,p_item_type => l_item_type
258 ,p_item_key => l_item_key
259 ,p_entity_key1 => to_char(-99)
260 ,p_entity_key2 => to_char(p_resource_id)
261 ,p_description => NULL
262 ,p_err_code => l_err_code
263 ,p_err_stage => l_err_stage
264 ,p_err_stack => l_err_stack
265 );
266
267 END IF;
268 --Setting the original value
269 wf_engine.threshold := l_save_thresh;
270 EXCEPTION
271 WHEN l_cannot_acquire_lock THEN
272 PA_FORECAST_ITEMS_UTILS.log_message('Unable to set lock for ' || to_char(p_assignment_id));
273 x_return_status := FND_API.G_RET_STS_ERROR;
274
275 -- COMMIT;
276 END Launch_WorkFlow_Fi_Gen;
277
278
279
280 ------------------------------------------------------------------------------------------------------------------
281 -- This procedure will start the work flow processing.
282 -- Input parameters
283 -- Parameters Type Required Description
284 -- p_item_type VARCHAR2 YES It will be used to pass the parameter to work flow
285 -- p_item_key VARCHAR2 YES It will be used to pass the parameter to work flow
286 -- p_actid NUMBER YES It will be used to pass the parameter to work flow
287 -- p_fucmode VARCHAR2 YES It store the function mode i.e. RUN OR CANCEL
288 --
289 -- Out parameters
290 -- p_result VARCHAR2 YES It store the result i.e. commit for work flow
291 --
292 --------------------------------------------------------------------------------------------------------------------
293 PROCEDURE Start_Forecast_WF( p_item_type IN VARCHAR2,
294 p_item_key IN VARCHAR2,
295 p_actid IN NUMBER,
296 p_funcmode IN VARCHAR2,
297 p_result OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
298
299 l_assignment_id NUMBER;
300 l_resource_id NUMBER;
301 l_asgmt_start_date DATE;
302 l_asgmt_end_date DATE;
303 l_process_mode VARCHAR2(30);
304 l_orgz_name pa_organizations_expend_v.name%TYPE;
305 l_lock_for VARCHAR2(5);
306 li_lock_status NUMBER;
307 l_x_return_status VARCHAR2(50);
308 l_x_msg_count NUMBER;
309 l_x_msg_data VARCHAR2(50);
310 BEGIN
311 -- DBMS_OUTPUT.PUT_LINE('3');
312 -- assigning just to differentiate b/w resource record and assignment record
313 l_lock_for := 'ASGMT';
314
315 IF ( p_funcmode = 'RUN' ) THEN
316
317 BEGIN
318 l_assignment_id := WF_ENGINE.GetItemAttrNumber( p_item_type,
319 p_item_key,
320 'ASSIGNMENT_ID');
321
322 l_resource_id := WF_ENGINE.GetItemAttrNumber( p_item_type,
323 p_item_key,
324 'RESOURCE_ID');
325
326 l_asgmt_start_date := WF_ENGINE.GetItemAttrDate ( p_item_type,
327 p_item_key,
328 'ASGMT_START_DATE');
329
330 l_asgmt_end_date := WF_ENGINE.GetItemAttrDate ( p_item_type,
331 p_item_key,
332 'ASGMT_END_DATE');
333
334 l_process_mode := WF_ENGINE.GetItemAttrText ( p_item_type,
335 p_item_key,
336 'PROCESS_MODE');
337
338 l_orgz_name := WF_ENGINE.GetItemAttrText ( p_item_type,
339 p_item_key,
340 'ORGANIZATION_NAME');
341
342 -- User lock for the given assignment id
343
344 -- DBMS_OUTPUT.PUT_LINE('4');
345
346 IF ( NVL(l_assignment_id,0) <> 0 ) THEN
347 IF (PA_FORECAST_ITEMS_UTILS.Set_User_Lock (l_assignment_id,l_lock_for) <> 0) THEN
348 RAISE l_cannot_acquire_lock;
349 END IF;
350
351 SAVEPOINT l_forecast_item_gen;
352 IF (l_process_mode = 'DELETE') THEN
353 -- Call the Forecast Deletion API.
354 PA_FORECASTITEM_PVT.Delete_Forecast_Item(p_assignment_id => l_assignment_id,
355 p_resource_id => l_resource_id,
356 p_start_date => l_asgmt_start_date,
357 p_end_date => l_asgmt_end_date,
358 x_return_status => l_x_return_status,
359 x_msg_count => l_x_msg_count,
360 x_msg_data => l_x_msg_data );
361 ELSE
362 -- Call the Forecast Generation API.
363 -- DBMS_OUTPUT.PUT_LINE('4');
364
365 PA_FORECASTITEM_PVT.Create_Forecast_Item(p_assignment_id => l_assignment_id,
366 p_start_date => l_asgmt_start_date,
367 p_end_date => l_asgmt_end_date,
368 p_process_mode => l_process_mode,
369 x_return_status => l_x_return_status,
370 x_msg_count => l_x_msg_count,
371 x_msg_data => l_x_msg_data );
372 END IF;
373 ELSE
374 PA_FORECASTITEM_PVT.Create_Forecast_Item(p_resource_id => l_resource_id,
375 p_start_date => l_asgmt_start_date,
376 p_end_date => l_asgmt_end_date,
377 p_process_mode => l_process_mode,
378 x_return_status => l_x_return_status,
379 x_msg_count => l_x_msg_count,
380 x_msg_data => l_x_msg_data );
381 END IF;
382
383 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
384 p_result := 'COMPLETE:S';
385 ELSIF (l_x_return_status = FND_API.G_RET_STS_ERROR ) THEN
386 ROLLBACK to l_forecast_item_gen ;
387 WF_ENGINE.SetItemAttrText
388 ( itemtype => p_item_type
389 , itemkey => p_item_key
390 , aname => 'ERROR_MSG'
391 , avalue => l_x_msg_data
392 );
393
394 p_result := 'COMPLETE:F';
395 ELSIF (l_x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
396 ROLLBACK to l_forecast_item_gen ;
397 WF_ENGINE.SetItemAttrText
398 ( itemtype => p_item_type
399 , itemkey => p_item_key
400 , aname => 'ERROR_MSG'
401 , avalue => l_x_msg_data
402 );
403 p_result := 'COMPLETE:F';
404 END IF;
405 -- COMMIT;
406 -- p_result := 'COMPLETE:S';
407
408 EXCEPTION
409 WHEN l_cannot_acquire_lock THEN
410 PA_FORECAST_ITEMS_UTILS.log_message('Unable to set lock for ' || to_char(l_assignment_id));
411 END;
412
413 ELSIF ( p_funcmode = 'CANCEL' ) THEN
414
415 NULL;
416
417 END IF;
418 -- p_result := 'COMPLETE:F';
419
420 RETURN;
421
422 EXCEPTION
423 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
424 -- Setting the attribute value for Error
425 WF_ENGINE.SetItemAttrText
426 ( itemtype => p_item_type
427 , itemkey => p_item_key
428 , aname => 'ERROR_MSG'
429 , avalue => SQLCODE||SQLERRM
430 );
431 p_result := 'COMPLETE:F';
432 -- RAISE;
433 WHEN OTHERS THEN
434 -- Setting the attribute value for Error
435 WF_ENGINE.SetItemAttrText
436 ( itemtype => p_item_type
437 , itemkey => p_item_key
438 , aname => 'ERROR_MSG'
439 , avalue => SQLCODE||SQLERRM
440 );
441 p_result := 'COMPLETE:F';
442 /* RAISE; */
443 END Start_Forecast_WF;
444
445 END PA_FORECAST_ITEMS_GEN_WF_PKG;