[Home] [Help]
PACKAGE BODY: APPS.EAM_SRAPPROVAL_PVT
Source
1 PACKAGE BODY EAM_SRAPPROVAL_PVT AS
2 /*$Header: EAMVSRAB.pls 120.5 2005/10/11 17:31:27 sraval ship $ */
3
4 function service_request_created(
5 p_subscription_guid in raw,
6 p_event in out NOCOPY wf_event_t
7 ) return varchar2
8 is
9 L_EVENT_NAME varchar2(240);
10 L_EVENT_KEY varchar2(240);
11 itemtype varchar2(30);
12 itemkey varchar2(30) ;
13 l_application_id NUMBER;
14 l_application_code varchar2(32);
15 l_incident_number cs_incidents_all_b.incident_number%type;
16 l_incident_id cs_incidents_all_b.incident_id%type;
17
18 l_owning_department_id cs_incidents_all_b.owning_department_id%type;
19 l_owning_department_code bom_departments.department_code%type;
20 l_owning_department_role varchar2(80);
21 l_inventory_item_id cs_incidents_all_b.inventory_item_id%type;
22 l_maintenance_flag varchar2(1);
23 l_concatenated_segments mtl_system_items_kfv.concatenated_segments%type;
24 l_conc_segments_description mtl_system_items.description%type;
25
26 l_role_name varchar2(80);
27
28 l_organization_code org_organization_definitions.organization_code%type;
29 l_responsibility_id varchar2(80);
30 l_responsibility_appl_id varchar2(30);
31 l_resp_string varchar2(30);
32 l_display_name varchar2(80);
33 l_incident_date date;
34 l_expected_resolution_date date;
35 l_default_department_id wip_eam_parameters.default_department_id%type;
36 l_return_status varchar2(240);
37 l_incident_status_id cs_incidents_all_b.incident_status_id%type;
38 l_incident_severity_id cs_incidents_all_b.incident_severity_id%type;
39 l_customer_id cs_incidents_all_b.customer_id%type;
40 l_service_rec cs_servicerequest_pub.service_request_rec_type;
41 l_sr_user_id number;
42 l_sr_resp_id number;
43 l_sr_resp_appl_id number;
44 l_notes_rec cs_servicerequest_pub.notes_table;
45 l_contacts_rec cs_servicerequest_pub.contacts_table;
46 l_sr_api_version number;
47 l_object_version_number number;
48 l_sr_return_status varchar2(1);
49 l_sr_msg_count number;
50 l_sr_msg_data varchar2(2000);
51 l_sr_workflow_process_id number;
52 l_sr_interaction_id number;
53 l_workflow_process varchar2(30);
54 l_instance_id number;
55 l_instance_number varchar2(30);
56 l_instance_description csi_item_instances.instance_description%type;
57 l_maint_organization_id cs_incidents_all_b.maint_organization_id%type;
58 begin
59
60 itemtype := 'EAMSRAPR';
61 l_sr_api_version := 3.0;
62 l_workflow_process := 'EAMSRAPR_PROCESS';
63
64
65 -- get the service request number from the event message
66 l_incident_number := wf_event.getValueForParameter('REQUEST_NUMBER',p_event.Parameter_List);
67 l_sr_user_id := wf_event.getValueForParameter('USER_ID',p_event.Parameter_List);
68 l_sr_resp_id := wf_event.getValueForParameter('RESP_ID',p_event.Parameter_List);
69 l_sr_resp_appl_id := wf_event.getValueForParameter('RESP_APPL_ID',p_event.Parameter_List);
70
71 -- get the service request id and service request type
72
73 begin
74 select
75 cia.incident_id,cia.owning_department_id,cia.inventory_item_id
76 ,cia.maint_organization_id,cia.incident_date, cia.expected_resolution_date,cit.maintenance_flag
77 ,cia.incident_status_id,cia.incident_severity_id,cia.customer_id,cia.object_version_number
78 ,cia.customer_product_id
79 into
80 l_incident_id,l_owning_department_id,l_inventory_item_id
81 ,l_maint_organization_id, l_incident_date, l_expected_resolution_date,l_maintenance_flag
82 ,l_incident_status_id,l_incident_severity_id,l_customer_id,l_object_version_number
83 ,l_instance_id
84 from cs_incidents_vl_sec cia, cs_incident_types_vl_sec cit
85 where cia.incident_number = l_incident_number
86 and cia.incident_type_id = cit.incident_type_id;
87 exception
88 when others then
89 return 'WARNING';
90 end;
91
92 itemkey := l_incident_id;
93
94
95 -- if service request type is maintenance, then proceed else terminate
96 if (l_maintenance_flag is not null AND l_maintenance_flag = 'Y') then
97 -- if Asset Number is specified on Service Request
98 if (l_instance_id is not null) then
99 select instance_number,instance_description,concatenated_segments,msi.description
100 into l_instance_number,l_instance_description,l_concatenated_segments,l_conc_segments_description
101 from csi_item_instances cii, mtl_system_items_kfv msik, mtl_system_items msi
102 where cii.last_vld_organization_id = msi.organization_id
103 and cii.inventory_item_id = msi.inventory_item_id
104 and msi.organization_id = msik.organization_id
105 and msi.inventory_item_id = msik.inventory_item_id
106 and cii.instance_id = l_instance_id
107 ;
108
109 -- if only Asset Group is specified and asset number is not
110 elsif (l_instance_id is null AND l_inventory_item_id is not null) then
111 select msik.concatenated_segments,msik.description
112 into l_concatenated_segments,l_conc_segments_description
113 from mtl_system_items_kfv msik, mtl_system_items msi
114 where msi.organization_id = msik.organization_id
115 and msi.inventory_item_id = msik.inventory_item_id
116 and msi.inventory_item_id = l_inventory_item_id
117 and msi.organization_id = l_maint_organization_id;
118 -- if asset number is specified and asset group is not
119 -- dont know if this can be possible though
120 elsif (l_instance_id is not null AND l_inventory_item_id is null) then
121 null;
122 end if;
123
124 -- if owning department is not specified on Service Request
125 if (l_owning_department_id is null) then
126 -- get default department id for organization
127 select default_department_id
128 into l_default_department_id
129 from wip_eam_parameters
130 where organization_id = l_maint_organization_id;
131
132 if (l_default_department_id is not null) then
133 -- update the service request owning department id
134 l_owning_department_id := l_default_department_id;
135
136 select department_code
137 into l_owning_department_code
138 from bom_departments
139 where department_id = l_owning_department_id
140 and organization_id = l_maint_organization_id;
141
142 -- call service request update API to update the owning dept on Service Request
143 -- fnd_global.apps_initialize is required as Service Request have a security access.
144 -- As suggested by Service team, we need to set the responsibility and user before Service Request can be updated.
145 fnd_global.apps_initialize(
146 user_id => l_sr_user_id,
147 resp_id => l_sr_resp_id,
148 resp_appl_id => l_sr_resp_appl_id
149 );
150 cs_servicerequest_pub.initialize_rec(l_service_rec);
151 l_service_rec.owning_department_id := l_owning_department_id;
152 cs_servicerequest_pub.update_serviceRequest(
153 p_api_version => l_sr_api_version
154 ,p_request_id => l_incident_id
155 ,p_service_request_rec => l_service_rec
156 ,p_object_version_number => l_object_version_number
157 ,p_notes => l_notes_rec
158 ,p_contacts => l_contacts_rec
159 ,p_last_updated_by => l_sr_user_id
160 ,p_last_update_date => sysdate
161 ,p_resp_appl_id => l_sr_resp_appl_id
162 ,p_resp_id => l_sr_resp_id
163 ,x_return_status => l_sr_return_status
164 ,x_msg_count => l_sr_msg_count
165 ,x_msg_data => l_sr_msg_data
166 ,x_workflow_process_id => l_sr_workflow_process_id
167 ,x_interaction_id => l_sr_interaction_id
168 );
169
170 if (l_sr_return_status <> FND_API.G_RET_STS_SUCCESS) then
171 return 'WARNING';
172 end if;
173 end if;
174 else
175 select department_code
176 into l_owning_department_code
177 from bom_departments
178 where department_id = l_owning_department_id
179 and organization_id = l_maint_organization_id;
180
181 end if;
182
183 if (l_owning_department_id is not null) then
184
185 -- get responsibility from dept. approvers
186 select beda.responsibility_id,beda.responsibility_application_id
187 into l_responsibility_id, l_responsibility_appl_id
188 from bom_eam_dept_approvers beda
189 where
190 beda.dept_id = l_owning_department_id
191 and beda.organization_id = l_maint_organization_id;
192
193 l_resp_string := 'FND_RESP';
194
195 -- get role from dept-responsibility combinations
196 l_responsibility_appl_id := l_resp_string || l_responsibility_appl_id;
197 wf_directory.GetRoleName(l_responsibility_appl_id ,l_responsibility_id,l_role_name,l_display_name);
198 end if;
199
200 wf_engine.SetItemAttrNumber( itemtype => itemtype,
201 itemkey => itemkey,
202 aname => 'ORGANIZATION_ID',
203 avalue => l_maint_organization_id);
204
205 wf_engine.SetItemAttrText(itemtype => itemtype,
206 itemkey => itemkey,
207 aname => 'ORGANIZATION_CODE',
208 avalue => l_organization_code);
209
210 wf_engine.SetItemAttrText(itemtype => itemtype,
211 itemkey => itemkey,
212 aname => 'ASSET_GROUP_SEGMENTS',
213 avalue => l_concatenated_segments);
214
215 wf_engine.SetItemAttrText(itemtype => itemtype,
216 itemkey => itemkey,
217 aname => 'ASSET_NUMBER',
218 avalue => l_instance_number);
219
220 wf_engine.SetItemAttrText(itemtype => itemtype,
221 itemkey => itemkey,
222 aname => 'DEPARTMENT_CODE',
223 avalue => l_owning_department_code);
224
225
226 wf_engine.SetItemAttrDate( itemtype => itemtype,
227 itemkey => itemkey,
228 aname => 'EXPECTED_RESOLUTION_DATE',
229 avalue => l_expected_resolution_date);
230
231 wf_engine.SetItemAttrDate( itemtype => itemtype,
232 itemkey => itemkey,
233 aname => 'INCIDENT_DATE',
234 avalue => l_incident_date);
235
236
237 -- set the department responsibility to the approver role
238 wf_engine.SetItemAttrText(itemtype=>itemtype,
239 itemkey =>itemkey,
240 aname=> 'DEPT_RESPONSIBILTY',
241 avalue=> l_role_name);
242
243 wf_engine.SetItemAttrNumber( itemtype => itemtype,
244 itemkey => itemkey,
245 aname => 'INCIDENT_SEVERITY_ID',
246 avalue => l_incident_severity_id);
247
248 wf_engine.SetItemAttrNumber( itemtype => itemtype,
249 itemkey => itemkey,
250 aname => 'INCIDENT_STATUS_ID',
251 avalue => l_incident_status_id);
252
253 wf_engine.SetItemAttrNumber( itemtype => itemtype,
254 itemkey => itemkey,
255 aname => 'CUSTOMER_ID',
256 avalue => l_customer_id);
257
258 /*call Workflow default Rule function */
259 l_return_status:=WF_RULE.DEFAULT_RULE(p_subscription_guid=>p_subscription_guid,p_event=>p_event);
260
261
262
263 end if;
264 return 'SUCCESS';
265 exception
266 when others then
267 wf_core.context('EAM_SRAPPROVAL_PVT','service_request_created',
268 p_event.getEventName(),p_subscription_guid);
269 wf_event.setErrorInfo(p_event,'WARNING');
270 return 'WARNING';
271
272 end;
273
274
275
276 --
277 function Service_Request_Updated(
278 p_subscription_guid in raw,
279 p_event in out NOCOPY wf_event_t
280 ) return varchar2
281 is
282 L_EVENT_NAME varchar2(240);
283 L_EVENT_KEY varchar2(240);
284 itemtype varchar2(30);
285 itemkey varchar2(30) ;
286 l_application_id NUMBER;
287 l_application_code varchar2(32);
288 l_incident_number cs_incidents_all_b.incident_number%type;
289 l_incident_id cs_incidents_all_b.incident_id%type;
290
291 l_owning_department_id cs_incidents_all_b.owning_department_id%type;
292 l_owning_department_code bom_departments.department_code%type;
293 l_owning_department_role varchar2(80);
294 l_inventory_item_id cs_incidents_all_b.inventory_item_id%type;
295 l_maintenance_flag varchar2(1);
296 l_concatenated_segments mtl_system_items_kfv.concatenated_segments%type;
297 l_conc_segments_description mtl_system_items.description%type ;
298
299 l_role_name varchar2(80);
300
301 l_organization_code org_organization_definitions.organization_code%type;
302 l_responsibility_id varchar2(80);
303 l_responsibility_appl_id varchar2(30);
304 l_resp_string varchar2(30);
305 l_display_name varchar2(80);
306 l_incident_date date;
307 l_expected_resolution_date date;
308 l_default_department_id wip_eam_parameters.default_department_id%type;
309 l_return_status varchar2(240);
310 l_incident_status_id cs_incidents_all_b.incident_status_id%type;
311 l_incident_severity_id cs_incidents_all_b.incident_severity_id%type;
312 l_customer_id cs_incidents_all_b.customer_id%type;
313 l_service_rec cs_servicerequest_pub.service_request_rec_type;
314 l_sr_user_id number;
315 l_sr_resp_id number;
316 l_sr_resp_appl_id number;
317 l_notes_rec cs_servicerequest_pub.notes_table;
318 l_contacts_rec cs_servicerequest_pub.contacts_table;
319 l_sr_api_version number;
320 l_object_version_number number;
321 l_sr_return_status varchar2(1);
322 l_sr_msg_count number;
323 l_sr_msg_data varchar2(2000);
324 l_sr_workflow_process_id number;
325 l_sr_interaction_id number;
326 /*Commented for bug 4488769:
327 l_prev_severity_id number;
328 l_prev_type_id number;
329 l_prev_status_id number;
330 l_prev_urgency_id number;
331 l_prev_summary cs_incidents_all_b.summary%type;
332 l_request_status_old number;
333 l_prev_owner_id number;
334 End Commented for Bug 4488769*/
335 l_instance_id number;
336 l_instance_number varchar2(30);
337 l_instance_description csi_item_instances.instance_description%type;
338 l_maint_organization_id cs_incidents_all_b.maint_organization_id%type;
339
340 begin
341 itemtype := 'EAMSRAPR';
342 l_sr_api_version := 3.0;
343
344 -- get the service request number from the event message
345 l_incident_number := wf_event.getValueForParameter('REQUEST_NUMBER',p_event.Parameter_List);
346 l_sr_user_id := wf_event.getValueForParameter('USER_ID',p_event.Parameter_List);
347 l_sr_resp_id := wf_event.getValueForParameter('RESP_ID',p_event.Parameter_List);
348 l_sr_resp_appl_id := wf_event.getValueForParameter('RESP_APPL_ID',p_event.Parameter_List);
349 /*
350 l_prev_type_id := wf_event.getValueForParameter('PREV_TYPE_ID',p_event.Parameter_List);
351 l_prev_severity_id := wf_event.getValueForParameter('PREV_SEVERITY_ID',p_event.Parameter_List);
352 l_prev_status_id := wf_event.getValueForParameter('PREV_STATUS_ID',p_event.Parameter_List);
353 l_prev_urgency_id := wf_event.getValueForParameter('PREV_URGENCY_ID',p_event.Parameter_List);
354 l_prev_summary := wf_event.getValueForParameter('PREV_SUMMARY',p_event.Parameter_List);
355 l_request_status_old := wf_event.getValueForParameter('REQUEST_STATUS_OLD',p_event.Parameter_List);
356 l_prev_owner_id := wf_event.getValueForParameter('PREV_OWNER_ID',p_event.Parameter_List);
357 */
358 -- get the service request id and service request type
359 select
360 cia.incident_id,cia.owning_department_id,cia.inventory_item_id,cia.maint_organization_id,
361 cia.incident_date, cia.expected_resolution_date,cit.maintenance_flag
362 ,cia.customer_product_id
363 into
364 l_incident_id,l_owning_department_id,l_inventory_item_id,l_maint_organization_id
365 ,l_incident_date, l_expected_resolution_date,l_maintenance_flag
366 ,l_instance_id
367 from cs_incidents_vl_sec cia, cs_incident_types_vl_sec cit
368 where cia.incident_number = l_incident_number
369 and cia.incident_type_id = cit.incident_type_id;
370
371 -- if service request type is maintenance, then proceed else terminate
372 if (l_maintenance_flag is not null AND l_maintenance_flag = 'Y') then
373 -- if owning department is not specified on Service Request
374 if (l_owning_department_id is null) then
375 -- get default department id for organization
376 select default_department_id
377 into l_default_department_id
378 from wip_eam_parameters
379 where organization_id = l_maint_organization_id;
380
381 if (l_default_department_id is not null) then
382 -- update the service request owning department id
383 l_owning_department_id := l_default_department_id;
384
385 select department_code
386 into l_owning_department_code
387 from bom_departments
388 where department_id = l_owning_department_id
389 and organization_id = l_maint_organization_id;
390
391 -- call service request update API to update the owning dept on Service Request
392 fnd_global.apps_initialize(
393 user_id => l_sr_user_id,
394 resp_id => l_sr_resp_id,
395 resp_appl_id => l_sr_resp_appl_id
396 );
397 cs_servicerequest_pub.initialize_rec(l_service_rec);
398 l_service_rec.owning_department_id := l_owning_department_id;
399 cs_servicerequest_pub.update_serviceRequest(
400 p_api_version => l_sr_api_version
401 ,p_request_id => l_incident_id
402 ,p_service_request_rec => l_service_rec
403 ,p_object_version_number => l_object_version_number
404 ,p_notes => l_notes_rec
405 ,p_contacts => l_contacts_rec
406 ,p_last_updated_by => l_sr_user_id
407 ,p_last_update_date => sysdate
408 ,p_resp_appl_id => l_sr_resp_appl_id
409 ,p_resp_id => l_sr_resp_id
410 ,x_return_status => l_sr_return_status
411 ,x_msg_count => l_sr_msg_count
412 ,x_msg_data => l_sr_msg_data
413 ,x_workflow_process_id => l_sr_workflow_process_id
414 ,x_interaction_id => l_sr_interaction_id
415 );
416
417 if (l_sr_return_status <> FND_API.G_RET_STS_SUCCESS) then
418 return 'WARNING';
419 end if;
420 end if;
421 else
422 select department_code
423 into l_owning_department_code
424 from bom_departments
425 where department_id = l_owning_department_id
426 and organization_id = l_maint_organization_id;
427 end if;
428
429 /*call Workflow default Rule function */
430 l_return_status:=WF_RULE.DEFAULT_RULE(p_subscription_guid=>p_subscription_guid,p_event=>p_event);
431 end if;
432 return 'SUCCESS';
433 exception
434 when others then
435 wf_core.context('EAM_SRAPPROVAL_PVT','service_request_updated',
436 p_event.getEventName(),p_subscription_guid);
437 wf_event.setErrorInfo(p_event,'WARNING');
438 return 'ERROR';
439 end;
440
441
442 Function return_department_id
443 (
444 p_maintenance_org_id in number, -- OPTIONAL, null can be passed
445 p_inventory_item_id in number, -- OPTIONAL, null can be passed
446 p_customer_product_id in number -- OPTIONAL, null can be passed
447 ) return number
448 is
449 l_default_department_id number;
450 begin
451 if (p_maintenance_org_id is not null) then
452
453 if p_customer_product_id is null then
454 begin
455 select default_department_id
456 into l_default_department_id
457 from wip_eam_parameters
458 where organization_id = p_maintenance_org_id;
459 exception
460 when no_data_found then
461 null;
462 end;
463
464 else
465 begin
466 select owning_department_id
467 into l_default_department_id
468 from eam_org_maint_defaults
469 where object_type = 50
470 and object_id = p_customer_product_id
471 and organization_id = p_maintenance_org_id;
472 exception
473 when no_data_found then
474 null;
475 end;
476
477 if l_default_department_id is null then
478 begin
479
480 select default_department_id
481 into l_default_department_id
482 from wip_eam_parameters
483 where organization_id = p_maintenance_org_id;
484 exception
485 when no_data_found then
486 null;
487 end;
488
489 end if;
490 end if;
491 end if;
492
493 return l_default_department_id;
494 exception
495 WHEN NO_DATA_FOUND THEN
496 return l_default_department_id;
497 end return_department_id;
498
499 END EAM_SRAPPROVAL_PVT;