[Home] [Help]
PACKAGE BODY: APPS.CSF_PREVENTIVE_MAINTENANCE_PVT
Source
1 PACKAGE BODY CSF_PREVENTIVE_MAINTENANCE_PVT as
2 /* $Header: csfvpmtb.pls 120.12 2006/10/09 22:40:45 ibalint noship $ */
3 -- Start of Comments
4 -- Package name : CSF_Preventive_Maintenance_PVT
5 -- Purpose : Preventive Maintenace Concurrent Program API.
6 -- History : Initial version for release 11.5.9
7 -- : Replaced ahl_unit_effectivities_b with ahl_unit_effectivities_app_v
8 -- for 11.5.10.
9 -- NOTE :
10 -- End of Comments
11
12 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSF_Preventive_Maintenance_PVT';
13 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csfvpmtb.pls';
14 g_retcode number := 0;
15
16 Procedure Add_Err_Msg Is
17 l_msg_index_out NUMBER;
18 x_msg_data_temp Varchar2(2000);
19 x_msg_data Varchar2(4000);
20 Begin
21 If fnd_msg_pub.count_msg > 0 Then
22 FOR i IN REVERSE 1..fnd_msg_pub.count_msg Loop
23 fnd_msg_pub.get(p_msg_index => i,
24 p_encoded => 'F',
25 p_data => x_msg_data_temp,
26 p_msg_index_out => l_msg_index_out);
27 x_msg_data := x_msg_data || x_msg_data_temp;
28 End Loop;
29 FND_FILE.put_line(FND_FILE.log,x_msg_data);
30 fnd_msg_pub.delete_msg;
31 g_retcode := 1;
32 End if;
33 End;
34
35 PROCEDURE Generate_SR_Tasks (
36 errbuf OUT NOCOPY VARCHAR2,
37 retcode OUT NOCOPY NUMBER,
38 P_Api_Version_Number IN NUMBER,
39 p_period_size IN NUMBER
40 )
41 IS
42 l_api_name CONSTANT VARCHAR2(30) := 'Generate_SR_Tasks';
43 l_api_version_number CONSTANT NUMBER := 1.0;
44 l_return_status_full VARCHAR2(1);
45 l_access_flag VARCHAR2(1);
46 l_sqlcode Number;
47 l_sqlerrm Varchar2(2000);
48
49 X_Return_Status VARCHAR2(1);
50 X_Msg_Count NUMBER;
51 X_Msg_Data VARCHAR2(2000);
52
53 Cursor c_ump is
54 Select csi.owner_party_id,
55 csi.install_location_id ,
56 csi.instance_id,
57 csi.instance_number,
58 csi.inventory_item_id,
59 csi.last_vld_organization_id,
60 csi.serial_number,
61 -- get install location only if type code is hz_party_sites or hz_locations
62 -- for SR validation
63 decode(csi.location_type_code,'HZ_PARTY_SITES',
64 csi.location_id,'HZ_LOCATIONS',
65 csi.location_id,null) location_id,
66 csi.location_type_code,
67 csi.owner_party_account_id,
68 csi.external_reference,
69 csi.system_id,
70 --- get BILL_TO,SHIP_TO parties
71 csi.owner_party_id billto_party_id,
72 csi.owner_party_id shipto_party_id,
73 --- get party_site_id of use type BILL_TO and SHIP_TO
74 aueb.unit_effectivity_id,
75 amh.title,
76 aueb.mr_header_id,
77 aueb.program_mr_header_id,
78 aueb.service_line_id contract_service_id,
79 aueb.due_date,
80 aueb.earliest_due_date,
81 aueb.latest_due_date,
82 amh.description,
83 hp.party_type
84 From ahl_unit_effectivities_app_v aueb,
85 csi_item_instances csi,
86 ahl_mr_headers_vl amh,
87 hz_parties hp
88 Where nvl(aueb.earliest_due_date,aueb.due_date) <= trunc(sysdate) + p_period_size
89 -- Get only the open UMPs and SR not created
90 and (aueb.status_code is NULL or aueb.status_code = 'INIT-DUE')
91 -- Application_usg_code PM for Preventive Maintenance seeded for CMRO 11.5.10 changes
92 and aueb.application_usg_code = 'PM'
93 and aueb.unit_effectivity_id not in (select object_id
94 from cs_incident_links cil
95 where cil.object_type = 'AHL_UMP_EFF'
96 and cil.link_type_id = 6)
97 -- link_type_id 6 is 'REFERS TO' seeded value
98 and csi.instance_id = aueb.csi_item_instance_id
99 and amh.mr_header_id = aueb.mr_header_id
100 and hp.party_id = csi.owner_party_id;
101
102 Cursor c_route(p_mr_header_id Number) is
103 Select arb.route_id,
104 arb.task_template_group_id
105 From ahl_routes_b arb,
106 ahl_mr_routes amr
107 Where amr.mr_header_id = p_mr_header_id
108 and arb.route_id = amr.route_id;
109
110 cursor c_contacts(p_party_id NUMBER) Is
111 Select hr.party_id,
112 hcp.contact_point_id,
113 hcp.contact_point_type,
114 hcp.primary_flag,
115 decode(primary_flag,'Y','Y',NULL)
116 primary_contact,
117 timezone_id
118 From Hz_Relationships hr,
119 Hz_Parties hp_obj,
120 Hz_Parties hp_sub,
121 Hz_Contact_points hcp
122 Where hr.object_id = p_party_id
123 and hr.status = 'A'
124 and NVL(hr.start_date, SYSDATE-1) < SYSDATE
125 and NVL(hr.end_date, SYSDATE+1) > SYSDATE
126 and hp_sub.party_id = hr.subject_id
127 and hp_sub.status = 'A'
128 and hp_sub.party_type = 'PERSON'
129 and hp_obj.party_id = hr.object_id
130 and hp_obj.status = 'A'
131 and hp_obj.party_type = 'ORGANIZATION'
132 and hcp.owner_table_id(+) = hr.party_id
133 and hcp.owner_table_name(+) = 'HZ_PARTIES'
134 and hcp.status(+) = 'A';
135
136 cursor c_billto_shipto(p_billto_party NUMBER,p_shipto_party NUMBER) is
137 select hr.object_id,max(hr.party_id) party_id
138 from hz_relationships hr,hz_parties hp
139 where hr.object_id in (p_billto_party,p_shipto_party)
140 AND hr.status = 'A'
141 AND NVL(hr.start_date, SYSDATE-1) < SYSDATE
142 AND NVL(hr.end_date, SYSDATE+1) > SYSDATE
143 AND hp.party_id = hr.subject_id
144 AND hp.party_type = 'PERSON'
145 AND hp.status = 'A'
146 group by hr.object_id;
147
148 cursor c_bill_ship_sites(p_party_id number) is
149 select hps1.party_site_id billto_site_id,
150 hps2.party_site_id shipto_site_id
151 From hz_party_sites hps1,
152 hz_party_sites hps2,
153 hz_party_site_uses hpsu1,
154 hz_party_site_uses hpsu2
155 Where hps1.party_id = p_party_id
156 and hpsu1.party_site_id = hps1.party_site_id
157 and hpsu1.site_use_type = 'BILL_TO'
158 and hpsu1.status = 'A'
159 and hpsu1.primary_per_type = 'Y'
160 and trunc(SYSDATE) BETWEEN TRUNC(NVL(hpsu1.begin_date,SYSDATE)) and
161 TRUNC(NVL(hpsu1.end_date,SYSDATE))
162 and hps2.party_id = p_party_id
163 and hpsu2.party_site_id = hps2.party_site_id
164 and hpsu2.site_use_type = 'SHIP_TO'
165 and hpsu2.status = 'A'
166 and hpsu2.primary_per_type = 'Y'
167 and hps1.status = 'A'
168 and hps2.status = 'A'
169 and trunc(SYSDATE) between TRUNC(NVL(hpsu2.begin_date,SYSDATE)) and
170 TRUNC(NVL(hpsu2.end_date,SYSDATE));
171
172 TYPE task_type is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
173 conf_task_id task_type;
174 conf_object_version_number task_type;
175
176 l_ump_rec c_ump%ROWTYPE;
177 l_route_rec c_route%ROWTYPE;
178 l_contacts_rec c_contacts%ROWTYPE;
179 l_billto_shipto_rec c_billto_shipto%ROWTYPE;
180 l_service_request_rec cs_servicerequest_pub.service_request_rec_type;
181 l_notes_table cs_servicerequest_pub.notes_table;
182 l_contacts_table cs_servicerequest_pub.contacts_table;
183 l_link_rec cs_incidentlinks_pub.cs_incident_link_rec_type;
184 l_billto_site_id number;
185 l_shipto_site_id number;
186 l_msg_index_out number;
187 task_created BOOLEAN := FALSE;
188 l_task_id NUMBER;
189 l_index NUMBER := 0;
190 l_primary_contact VARCHAR2(1);
191
192 x_Task_Details_Tbl jtf_tasks_pub.task_details_tbl;
193 x_request_id Number;
194 x_request_number Number;
195 x_interaction_id Number;
196 x_workflow_process_id Number;
197 x_individual_owner Number;
198 x_group_owner Number;
199 x_individual_type Varchar2(200);
200 x_object_version_number Number;
201 x_reciprocal_link_id Number;
202 x_link_id Number;
203 x_pm_conf_reqd Varchar2(1);
204
205
206 /* rhungund : note
207 added the following 2 local variables for ER 3919796 */
208
209 l_product_number VARCHAR2(30);
210 l_serial_number VARCHAR2(30);
211
212
213 /* rhungund : note
214 added the following 2 local variables for ER 3956663 */
215 l_no_contacts_table cs_servicerequest_pub.contacts_table;
216 l_no_notes_table cs_servicerequest_pub.notes_table;
217
218
219
220 /* rhungund - note
221 Adding the cursor to get incident location id given a party site id
222 Adding a local variable to hold the location id
223
224 This resolves bug 4012520
225 */
226 CURSOR c_get_hz_location_csr IS
227 SELECT location_id from HZ_PARTY_SITES WHERE party_site_id = l_ump_rec.location_id;
228
229 l_hz_location_id NUMBER;
230
231
232
233 /*rhungund - begin - changes to associate access hours to PM tasks */
234 l_acc_hr_id NUMBER;
235 l_acchr_loc_id NUMBER;
236 l_acchr_ct_site_id NUMBER;
237 l_acchr_ct_id NUMBER;
238 l_acchrs_found BOOLEAN;
239 l_address_id_to_pass NUMBER;
240 l_location_id_to_pass NUMBER;
241
242 CURSOR c_acchrs_location_csr IS
243 SELECT * from csf_map_access_hours_vl where
244 customer_location_id = l_acchr_loc_id;
245
246 CURSOR c_acchrs_ctsite_csr IS
247 SELECT * from csf_map_access_hours_vl where
248 customer_id = l_acchr_ct_id and
249 customer_site_id = l_acchr_ct_site_id;
250
251 CURSOR c_acchrs_ct_csr IS
252 SELECT * from csf_map_access_hours_vl where
253 customer_id = l_acchr_ct_id;
254
255 l_acchrs_setups_rec c_acchrs_location_csr%ROWTYPE;
256
257 /*rhungund - end - changes to associate access hours to PM tasks */
258
259
260
261
262 BEGIN
263 retcode := g_retcode;
264 -- Standard Start of API savepoint
265 SAVEPOINT Generate_SR_Tasks_PVT;
266
267 -- Standard call to check for call compatibility.
268 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
269 p_api_version_number,
270 l_api_name,
271 G_PKG_NAME)
272 THEN
273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
274 END IF;
275
276
277 -- Initialize message list
278 FND_MSG_PUB.initialize;
279
280
281 -- Initialize API return status to SUCCESS
282 x_return_status := FND_API.G_RET_STS_SUCCESS;
283
284
285 --
286 -- API body
287 --
288
289 Open c_ump;
290 LOOP
291 Fetch c_ump into l_ump_rec;
292 Exit When c_ump%NOTFOUND;
293 --- reuse the savepoint to rollback or commit transaction for each UMP
294 SAVEPOINT Generate_SR_Tasks_PVT;
295 FND_FILE.put_line(FND_FILE.log,'MESSAGE: Processing Effectivity==='||l_ump_rec.unit_effectivity_id);
296
297 l_product_number := l_ump_rec.instance_number;
298 l_serial_number := l_ump_rec.serial_number;
299
300
301 cs_servicerequest_pub.initialize_rec(l_service_request_rec);
302 l_service_request_rec.type_id :=
303 fnd_profile.value('csfpm_incident_type');
304 l_service_request_rec.status_id :=
305 fnd_profile.value('csfpm_incident_status');
306
307
308 l_service_request_rec.summary := l_ump_rec.title;
309 l_service_request_rec.caller_type := l_ump_rec.party_type;
310 l_service_request_rec.customer_id := l_ump_rec.owner_party_id;
311 l_service_request_rec.customer_product_id := l_ump_rec.instance_id;
312 l_service_request_rec.inventory_item_id := l_ump_rec.inventory_item_id;
313 l_service_request_rec.inventory_org_id := l_ump_rec.last_vld_organization_id;
314 l_service_request_rec.current_serial_number := l_ump_rec.serial_number;
315 l_service_request_rec.exp_resolution_date := greatest(l_ump_rec.latest_due_date,sysdate);
316 --l_service_request_rec.install_site_id := l_ump_rec.location_id;
317 l_service_request_rec.account_id := l_ump_rec.owner_party_account_id;
318 l_service_request_rec.contract_service_id := l_ump_rec.contract_service_id;
319 l_service_request_rec.sr_creation_channel := 'AUTOMATIC';
320 l_service_request_rec.external_reference := l_ump_rec.external_reference;
321 l_service_request_rec.system_id := l_ump_rec.system_id;
322 l_service_request_rec.creation_program_code := 'PMCON'; -- Preventive Maintenance concurrent program seeded value
323 l_service_request_rec.last_update_program_code := 'PMCON';
324 l_service_request_rec.program_id := fnd_global.conc_program_id;
325 l_service_request_rec.program_application_id := fnd_global.prog_appl_id;
326 l_service_request_rec.conc_request_id := fnd_global.conc_request_id;
327 l_service_request_rec.program_login_id := fnd_global.conc_login_id;
328
329 l_service_request_rec.bill_to_party_id := l_ump_rec.billto_party_id;
330 l_service_request_rec.ship_to_party_id := l_ump_rec.shipto_party_id;
331
332 FND_FILE.put_line(FND_FILE.log,'MESSAGE: Processing Instance Number='||l_service_request_rec.customer_product_id);
333 /* rhungund - note
334 Adding the following 2 rec parameters to address bug 4379140
335 */
336 l_service_request_rec.bill_to_account_id := l_ump_rec.owner_party_account_id;
337 l_service_request_rec.ship_to_account_id := l_ump_rec.owner_party_account_id;
338
339
340 /* rhungund - note
341 Adding the incident location id to the service request record if
342 item instance's current location type code = HZ_PARTY_SITE
343
344 This resolves bug 4012520
345 */
346 IF (l_ump_rec.location_type_code is not null and
347 l_ump_rec.location_type_code = 'HZ_PARTY_SITES') THEN
348
349 l_service_request_rec.incident_location_id :=l_ump_rec.location_id;
350 l_service_request_rec.incident_location_type :='HZ_PARTY_SITE';
351
352 END IF;
353 /* ibalint bug 5183551 */
354
355
356 IF (l_ump_rec.location_type_code is not null and
357 l_ump_rec.location_type_code = 'HZ_LOCATION') THEN
358
359 --l_service_request_rec.incident_location_id :=l_ump_rec.location_id;
360 l_service_request_rec.incident_location_type :='HZ_LOCATION';
361 l_service_request_rec.install_site_id := l_ump_rec.install_location_id;
362
363
364
365 END IF;
366
367 --FND_FILE.put_line(FND_FILE.log,'MESSAGE: install_site_id*='||l_service_request_rec.install_site_id);
368
369
370 -- Get bill to and ship to sites
371 l_billto_site_id := null;
372 l_shipto_site_id := null;
373 open c_bill_ship_sites(l_ump_rec.billto_party_id);
374 fetch c_bill_ship_sites into l_billto_site_id,l_shipto_site_id;
375 close c_bill_ship_sites;
376 l_service_request_rec.bill_to_site_id := l_billto_site_id;
377 l_service_request_rec.ship_to_site_id := l_shipto_site_id;
378
379 -- Get Billto,Shipto contacts
380 l_billto_shipto_rec := NULL;
381 open c_billto_shipto(l_service_request_rec.bill_to_party_id,
382 l_service_request_rec.ship_to_party_id);
383 LOOP
384 Fetch c_billto_shipto into l_billto_shipto_rec;
385 EXIT WHEN c_billto_shipto%NOTFOUND;
386 If l_billto_shipto_rec.object_id = l_service_request_rec.bill_to_party_id Then
387 l_service_request_rec.bill_to_contact_id := l_billto_shipto_rec.party_id;
388 ElsIf l_billto_shipto_rec.object_id = l_service_request_rec.ship_to_party_id Then
389 l_service_request_rec.ship_to_contact_id := l_billto_shipto_rec.party_id;
390 End If;
391 END LOOP;
392 close c_billto_shipto;
393 l_service_request_rec.owner_id := NULL;
394 l_service_request_rec.time_zone_id := NULL;
395 l_contacts_table.DELETE;
396 l_primary_contact := NULL;
397 l_index := 0;
398 l_contacts_rec := NULL;
399 open c_contacts (l_ump_rec.owner_party_id);
400 LOOP
401 Fetch c_contacts INTO l_contacts_rec;
402 EXIT WHEN c_contacts%NOTFOUND;
403 -- Check to ensure that there would only one primary
404 -- contact
405 If not (nvl(l_primary_contact,'N') = 'Y' and
406 nvl(l_contacts_rec.primary_flag,'N') = 'Y') Then
407 l_index := l_index + 1;
408 l_contacts_table(l_index).party_id := l_contacts_rec.party_id;
409 l_contacts_table(l_index).contact_point_id := l_contacts_rec.contact_point_id;
410 l_contacts_table(l_index).contact_point_type := l_contacts_rec.contact_point_type;
411 l_contacts_table(l_index).contact_type := 'PARTY_RELATIONSHIP';
412 l_contacts_table(l_index).primary_flag := l_contacts_rec.primary_flag;
413 l_primary_contact := nvl(l_primary_contact,l_contacts_rec.primary_contact);
414 l_service_request_rec.time_zone_id := l_contacts_rec.timezone_id;
415 End If;
416 END LOOP;
417 close c_contacts;
418 l_index := 0;
419 /*rhungund : note
420 The following IF block is added to address ER 3956663
421 If a customer has no primary contact(s), we will not
422 pass any contacts while creating the service request.
423 This is to cirumvent an existing issue with Create_ServiceRequest() API
424 which disallows creation of service request if 'ORG' customer type
425 has no primary contacts
426
427 Instead, after creating the SR, we update the SR with the list of contacts.
428 Update_ServiceRequest() API has no such restriction as it's create counterpart does.
429
430 The Update_ServiceRequest() API is also newly added to address the same ER.
431 */
432 FND_FILE.put_line(FND_FILE.log,'MESSAGE: before calling cs_servicerequest_pub.Create_ServiceRequest API');
433
434 IF (nvl(l_primary_contact,'N') = 'N') THEN
435
436 cs_servicerequest_pub.Create_ServiceRequest
437 ( p_api_version => 3.0,
438 p_init_msg_list => FND_API.G_FALSE,
439 p_commit => FND_API.G_FALSE,
440 x_return_status => x_return_status,
441 x_msg_count => x_msg_count,
442 x_msg_data => x_msg_data,
443 p_resp_appl_id => FND_GLOBAL.RESP_APPL_ID,
444 p_resp_id => FND_GLOBAL.RESP_ID,
445 p_user_id => fnd_global.user_id,
446 p_login_id => fnd_global.conc_login_id,
447 p_org_id => fnd_profile.value('ORG_ID'),
448 p_request_id => null,
449 p_request_number => null,
450 p_service_request_rec => l_service_request_rec,
451 p_notes => l_notes_table,
452 p_contacts => l_no_contacts_table,
453 p_auto_assign => 'Y',
454 x_request_id => x_request_id,
455 x_request_number => x_request_number,
456 x_interaction_id => x_interaction_id,
457 x_workflow_process_id => x_workflow_process_id,
458 x_individual_owner => x_individual_owner,
459 x_group_owner => x_group_owner,
460 x_individual_type => x_individual_type );
461
462
463 ELSE
464
465 cs_servicerequest_pub.Create_ServiceRequest
466 ( p_api_version => 3.0,
467 p_init_msg_list => FND_API.G_FALSE,
468 p_commit => FND_API.G_FALSE,
469 x_return_status => x_return_status,
470 x_msg_count => x_msg_count,
471 x_msg_data => x_msg_data,
472 p_resp_appl_id => FND_GLOBAL.RESP_APPL_ID,
473 p_resp_id => FND_GLOBAL.RESP_ID,
474 p_user_id => fnd_global.user_id,
475 p_login_id => fnd_global.conc_login_id,
476 p_org_id => fnd_profile.value('ORG_ID'),
477 p_request_id => null,
478 p_request_number => null,
479 p_service_request_rec => l_service_request_rec,
480 p_notes => l_notes_table,
481 p_contacts => l_contacts_table,
482 p_auto_assign => 'Y',
483 x_request_id => x_request_id,
484 x_request_number => x_request_number,
485 x_interaction_id => x_interaction_id,
486 x_workflow_process_id => x_workflow_process_id,
487 x_individual_owner => x_individual_owner,
488 x_group_owner => x_group_owner,
489 x_individual_type => x_individual_type );
490
491 END IF;
492 FND_FILE.put_line(FND_FILE.log,'MESSAGE: after calling cs_servicerequest_pub.Create_ServiceRequest API x_return_status='||x_return_status);
493
494 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
495 fnd_message.set_name('CSF','CSF_PM_SR_CREATION_ERROR');
496 fnd_message.set_token('VALUE1',l_ump_rec.unit_effectivity_id);
497 fnd_message.set_token('VALUE2',l_product_number);
498 fnd_message.set_token('VALUE3',l_serial_number);
499 fnd_msg_pub.add;
500 Add_Err_Msg;
501 ElsIf (nvl(x_individual_owner,0) <= 0 and nvl(x_group_owner,0) <= 0) Then
502 fnd_message.set_name('CSF','CSF_PM_SR_OWNER_ERROR');
503 fnd_message.set_token('VALUE1',l_ump_rec.unit_effectivity_id);
504 fnd_message.set_token('VALUE2',nvl(x_individual_owner,x_group_owner));
505 fnd_message.set_token('VALUE3',nvl(x_individual_type,'RS_GROUP'));
506 fnd_message.set_token('VALUE4',l_product_number);
507 fnd_message.set_token('VALUE5',l_serial_number);
508 fnd_msg_pub.add;
509 Add_Err_Msg;
510 Else
511
512 /* rhungund: note
513 Calling Update_ServiceRequest() API to update the just created SR
514 with the list of contacts
515 */
516
517 l_index := l_contacts_table.FIRST;
518 IF (nvl(l_primary_contact,'N') = 'N' and l_index is not null) THEN
519 CS_ServiceRequest_PUB.Update_ServiceRequest
520 (
521 p_api_version => 3.0,
522 p_init_msg_list => fnd_api.g_false,
523 p_commit => fnd_api.g_false,
524 x_return_status => x_return_status,
525 x_msg_count => x_msg_count,
526 x_msg_data => x_msg_data,
527 p_request_id => x_request_id,
528 p_last_updated_by => fnd_global.user_id,
529 p_last_update_date => sysdate,
530 p_object_version_number=> x_object_version_number,
531 p_resp_appl_id => FND_GLOBAL.RESP_APPL_ID,
532 p_resp_id => FND_GLOBAL.RESP_ID,
533 p_service_request_rec => l_service_request_rec,
534 p_notes => l_no_notes_table,
535 p_contacts => l_contacts_table,
536 x_interaction_id => x_interaction_id,
537 x_workflow_process_id => x_workflow_process_id
538 );
539
540 If x_return_status <> FND_API.G_RET_STS_SUCCESS Then
541 fnd_message.set_name('CSF','CSF_PM_SR_UPDATE_ERROR');
542 fnd_message.set_token('VALUE1',x_request_id);
543 fnd_msg_pub.add;
544 Add_Err_Msg;
545 End If;
546
547 END IF;
548
549
550
551
552
553 --- Create link between UMP and Service Request
554 l_link_rec := NULL;
555 l_link_rec.subject_id := x_request_id;
556 l_link_rec.subject_type := 'SR';
557 l_link_rec.object_id := l_ump_rec.unit_effectivity_id;
558 l_link_rec.object_number := l_ump_rec.title;
559 l_link_rec.object_type := 'AHL_UMP_EFF';
560 l_link_rec.link_type_id := 6;
561 l_link_rec.request_id := fnd_global.conc_request_id;
562 l_link_rec.program_application_id := fnd_global.prog_appl_id;
563 l_link_rec.program_id := fnd_global.conc_program_id;
564 l_link_rec.program_update_date := sysdate;
565 cs_incidentlinks_pub.create_incidentlink(
566 p_api_version => 2.0,
567 p_init_msg_list => FND_API.G_FALSE,
568 p_commit => FND_API.G_FALSE,
569 p_resp_appl_id => FND_GLOBAL.RESP_APPL_ID,
570 p_resp_id => FND_GLOBAL.RESP_ID,
571 p_user_id => FND_GLOBAL.USER_ID,
572 p_login_id => NULL,
573 p_org_id => fnd_profile.value('ORG_ID'),
574 p_link_rec => l_link_rec,
575 x_return_status => x_return_status,
576 x_msg_count => x_msg_count,
577 x_msg_data => x_msg_data,
578 x_object_version_number => x_object_version_number,
579 x_reciprocal_link_id => x_reciprocal_link_id,
580 x_link_id => x_link_id);
581
582
583 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
584 fnd_message.set_name('CSF','CSF_PM_SR_LINK_CREATION_ERROR');
585 fnd_message.set_token('VALUE1',l_ump_rec.unit_effectivity_id);
586 fnd_message.set_token('VALUE2',l_product_number);
587 fnd_message.set_token('VALUE3',l_serial_number);
588 fnd_msg_pub.add;
589 Add_Err_Msg;
590 End If;
591 End If;
592
593
594 If nvl(x_link_id,0) > 0 Then
595 Open c_route(l_ump_rec.mr_header_id);
596 Loop
597 Fetch c_route into l_route_rec;
598 Exit When c_route%NOTFOUND;
599 x_task_details_tbl.delete;
600 task_created := FALSE;
601 If l_route_rec.task_template_group_id is not null
602 Then
603
604 --decide if we pass p_address_id or p_location_id
605 IF (l_ump_rec.location_type_code is not null and
606 l_ump_rec.location_type_code = 'HZ_PARTY_SITES') Then
607
608 l_address_id_to_pass := l_ump_rec.location_id;
609 l_location_id_to_pass := null;
610 END IF;
611 IF (l_ump_rec.location_type_code is not null and
612 l_ump_rec.location_type_code = 'HZ_LOCATIONS') Then
613
614 l_address_id_to_pass := null;
615 l_location_id_to_pass := l_ump_rec.location_id;
616 END IF;
617
618 jtf_tasks_pub.create_task_from_template (
619 p_api_version => 1.0,
620 p_init_msg_list => FND_API.G_FALSE,
621 p_commit => FND_API.G_FALSE,
622 p_task_template_group_id => l_route_rec.task_template_group_id,
623 p_owner_type_code => nvl(x_individual_type,'RS_GROUP'),
624 p_owner_id => nvl(x_individual_owner,x_group_owner),
625 p_source_object_id => x_request_id,
626 p_source_object_name => x_request_number,
627 x_return_status => x_return_status,
628 x_msg_count => x_msg_count,
629 x_msg_data => x_msg_data,
630 x_task_details_tbl => x_task_details_tbl,
631 p_customer_id => l_ump_rec.owner_party_id,
632 p_address_id => l_address_id_to_pass, --l_ump_rec.install_location_id,--l_ump_rec.location_id,
633 p_location_id => l_location_id_to_pass,
634 p_planned_start_date => nvl(l_ump_rec.earliest_due_date,l_ump_rec.due_date),
635 p_planned_end_date => to_date(nvl(l_ump_rec.latest_due_date,l_ump_rec.due_date)
636 || ' 23:59:59', 'dd-mon-yy hh24:mi:ss'),
637 p_timezone_id => l_service_request_rec.time_zone_id);
638 FND_FILE.put_line(FND_FILE.log,'MESSAGE:after create task x_return_status !='||x_return_status);
639 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
640 fnd_message.set_name('CSF','CSF_PM_TASK_CREATION_ERROR');
641 fnd_message.set_token('VALUE1',l_ump_rec.unit_effectivity_id);
642 fnd_message.set_token('VALUE2',l_route_rec.route_id);
643 fnd_message.set_token('VALUE3',x_individual_owner);
644 fnd_message.set_token('VALUE4',x_individual_type);
645 fnd_message.set_token('VALUE5',l_product_number);
646 fnd_message.set_token('VALUE6',l_serial_number);
647 fnd_msg_pub.add;
648 Add_Err_Msg;
649 Else
650 task_created := TRUE;
651 If l_ump_rec.location_id is Null then
652 --If l_ump_rec.install_location_id is Null then fnd_message.set_name('CSF','CSF_PM_TASK_ADDRESS_MSG');
653 fnd_message.set_token('VALUE1',x_request_number);
654 fnd_message.set_token('VALUE2',l_product_number);
655 fnd_message.set_token('VALUE3',l_serial_number);
656 fnd_msg_pub.add;
657 Add_Err_Msg;
658 End If;
659 End If;
660 Else
661 fnd_message.set_name('CSF','CSF_PM_TASK_TEMPLATE_INVALID');
662 fnd_message.set_token('VALUE1',l_ump_rec.unit_effectivity_id);
663 fnd_message.set_token('VALUE2',l_route_rec.route_id);
664 fnd_message.set_token('VALUE3',l_product_number);
665 fnd_message.set_token('VALUE4',l_serial_number);
666 fnd_msg_pub.add;
667 Add_Err_Msg;
668 End If;
669 End Loop;
670 Close c_route;
671 End If;
672 -- Commit the transaction or Rollback the transaction if no tasks are created for the service request.
673
674 If task_created Then
675 -- If 1=1 Then
676 COMMIT WORK;
677 FND_FILE.put_line(FND_FILE.log,'MESSAGE:after commit');
678 -- Customer confirmation process
679 -- Get customer cofirmation flag from contracts
680 oks_pm_entitlements_pub.Get_PM_Confirmation
681 (p_api_version => 1.0
682 ,p_init_msg_list => FND_API.G_FALSE
683 ,p_service_line_id => l_ump_rec.contract_service_id
684 ,p_program_id => l_ump_rec.program_mr_header_id
685 ,p_Activity_id => l_ump_rec.mr_header_id
686 ,x_return_status => x_return_status
687 ,x_msg_count => x_msg_count
688 ,x_msg_data => x_msg_data
689 ,x_pm_conf_reqd => x_pm_conf_reqd);
690
691 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
692 Add_Err_Msg;
693 Else
694 -- If customer conformation required then update all
695 -- the tasks statuses to CONFIRM status from profile option
696 -- csfpm_task_confirm_status
697 If x_pm_conf_reqd = 'Y' Then
698 SAVEPOINT Generate_SR_Tasks_PVT;
699
700 SELECT task_id,object_version_number
701 BULK COLLECT INTO conf_task_id,conf_object_version_number
702 FROM jtf_tasks_b
703 WHERE source_object_id = x_request_id;
704
705 FOR i in 1..conf_task_id.COUNT Loop
706 csf_tasks_pub.Update_Task_status
707 (p_api_version => 1.0,
708 p_init_msg_list => FND_API.G_FALSE,
709 p_commit => fnd_api.g_false,
710 p_task_id => conf_task_id(i),
711 p_task_status_id => fnd_profile.value('csfpm_task_confirm_status'),
712 p_object_version_number => conf_object_version_number(i),
713 x_return_status => x_return_status,
714 x_msg_count => x_msg_count,
715 x_msg_data => x_msg_data );
716 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
717 fnd_message.set_name('CSF','CSF_PM_TASK_UPDATE_ERROR');
718 fnd_message.set_token('VALUE1',x_request_number);
719 fnd_message.set_token('VALUE2',conf_task_id(i));
720 fnd_message.set_token('VALUE3',l_product_number);
721 fnd_message.set_token('VALUE4',l_serial_number);
722 fnd_msg_pub.add;
723 Add_Err_Msg;
724 End If;
725
726
727 END LOOP;
728 COMMIT WORK;
729
730 ELSE /* if cust confirmation is not required by contracts, check at the task templ level */
731 null;
732 End If; /* End of If x_pm_conf_reqd = 'Y' */
733 End If;
734
735
736 /* rhungund - begin - changes to associate access hours to the PM tasks */
737 If task_created Then
738 /*
739 1) Check if access hours setups are done for the location
740 2) Else, check if access hours setups are done for the ct + ct site combination
741 3) Else, check if access hours setups are done for the ct
742 4) Create access hours for the task, if acc hrs setups are found for the just created task
743 */
744
745 l_acchr_ct_id := l_ump_rec.owner_party_id;
746
747 IF (l_ump_rec.location_type_code = 'HZ_LOCATIONS') THEN
748 l_acchr_loc_id := l_ump_rec.location_id;
749 OPEN c_acchrs_location_csr;
750 FETCH c_acchrs_location_csr INTO l_acchrs_setups_rec;
751 IF (c_acchrs_location_csr%NOTFOUND) THEN
752 OPEN c_acchrs_ct_csr;
753 FETCH c_acchrs_ct_csr INTO l_acchrs_setups_rec;
754 IF (c_acchrs_ct_csr%NOTFOUND) THEN
755 l_acchrs_found := false;
756 ELSE
757 l_acchrs_found := true;
758 END IF;
759 close c_acchrs_ct_csr;
760 ELSE
761 l_acchrs_found := true;
762 END IF;
763 close c_acchrs_location_csr;
764
765 ELSIF(l_ump_rec.location_type_code = 'HZ_PARTY_SITES') THEN
766 l_acchr_ct_site_id := l_ump_rec.location_id;
767
768 OPEN c_acchrs_ctsite_csr;
769 FETCH c_acchrs_ctsite_csr INTO l_acchrs_setups_rec;
770 IF (c_acchrs_ctsite_csr%NOTFOUND) THEN
771 OPEN c_acchrs_ct_csr;
772 FETCH c_acchrs_ct_csr INTO l_acchrs_setups_rec;
773 IF (c_acchrs_ct_csr%NOTFOUND) THEN
774 l_acchrs_found := false;
775 ELSE
776 l_acchrs_found := true;
777 END IF;
778 close c_acchrs_ct_csr;
779 ELSE
780 l_acchrs_found := true;
781 END IF;
782 close c_acchrs_ctsite_csr;
783 END IF;
784
785 IF (l_acchrs_found = true) THEN
786
787 SELECT task_id,object_version_number
788 BULK COLLECT INTO conf_task_id,conf_object_version_number
789 FROM jtf_tasks_b
790 WHERE source_object_id = x_request_id;
791
792
793 FOR i in 1..conf_task_id.COUNT Loop
794 CSF_ACCESS_HOURS_PUB.CREATE_ACCESS_HOURS(
795 x_ACCESS_HOUR_ID => l_acc_hr_id,
796 p_API_VERSION => 1.0 ,
797 p_init_msg_list => NULL,
798 p_TASK_ID => conf_task_id(i),
799 p_ACCESS_HOUR_REQD => l_acchrs_setups_rec.accesshour_required,
800 p_AFTER_HOURS_FLAG => l_acchrs_setups_rec.after_hours_flag,
801 p_MONDAY_FIRST_START => l_acchrs_setups_rec.MONDAY_FIRST_START,
802 p_MONDAY_FIRST_END => l_acchrs_setups_rec.MONDAY_FIRST_END,
803 p_MONDAY_SECOND_START => l_acchrs_setups_rec.MONDAY_SECOND_START,
804 p_MONDAY_SECOND_END => l_acchrs_setups_rec.MONDAY_SECOND_END,
805 p_TUESDAY_FIRST_START => l_acchrs_setups_rec.TUESDAY_FIRST_START,
806 p_TUESDAY_FIRST_END => l_acchrs_setups_rec.TUESDAY_FIRST_END,
807 p_TUESDAY_SECOND_START => l_acchrs_setups_rec.TUESDAY_SECOND_START,
808 p_TUESDAY_SECOND_END => l_acchrs_setups_rec.TUESDAY_SECOND_END,
809 p_WEDNESDAY_FIRST_START => l_acchrs_setups_rec.WEDNESDAY_FIRST_START,
810 p_WEDNESDAY_FIRST_END => l_acchrs_setups_rec.WEDNESDAY_FIRST_END,
811 p_WEDNESDAY_SECOND_START => l_acchrs_setups_rec.WEDNESDAY_SECOND_START,
812 p_WEDNESDAY_SECOND_END => l_acchrs_setups_rec.WEDNESDAY_SECOND_END,
813 p_THURSDAY_FIRST_START => l_acchrs_setups_rec.THURSDAY_FIRST_START,
814 p_THURSDAY_FIRST_END => l_acchrs_setups_rec.THURSDAY_FIRST_END,
815 p_THURSDAY_SECOND_START => l_acchrs_setups_rec.THURSDAY_SECOND_START,
816 p_THURSDAY_SECOND_END => l_acchrs_setups_rec.THURSDAY_SECOND_END,
817 p_FRIDAY_FIRST_START => l_acchrs_setups_rec.FRIDAY_FIRST_START,
818 p_FRIDAY_FIRST_END => l_acchrs_setups_rec.FRIDAY_FIRST_END,
819 p_FRIDAY_SECOND_START => l_acchrs_setups_rec.FRIDAY_SECOND_START,
820 p_FRIDAY_SECOND_END => l_acchrs_setups_rec.FRIDAY_SECOND_END,
821 p_SATURDAY_FIRST_START => l_acchrs_setups_rec.SATURDAY_FIRST_START,
822 p_SATURDAY_FIRST_END => l_acchrs_setups_rec.SATURDAY_FIRST_END,
823 p_SATURDAY_SECOND_START => l_acchrs_setups_rec.SATURDAY_SECOND_START,
824 p_SATURDAY_SECOND_END => l_acchrs_setups_rec.SATURDAY_SECOND_END,
825 p_SUNDAY_FIRST_START => l_acchrs_setups_rec.SUNDAY_FIRST_START,
826 p_SUNDAY_FIRST_END => l_acchrs_setups_rec.SUNDAY_FIRST_END,
827 p_SUNDAY_SECOND_START => l_acchrs_setups_rec.SUNDAY_SECOND_START,
828 p_SUNDAY_SECOND_END => l_acchrs_setups_rec.SUNDAY_SECOND_END,
829 p_DESCRIPTION => l_acchrs_setups_rec.DESCRIPTION,
830 px_object_version_number => x_object_version_number,
831 p_CREATED_BY => null,
832 p_CREATION_DATE => null,
833 p_LAST_UPDATED_BY => null,
834 p_LAST_UPDATE_DATE => null,
835 p_LAST_UPDATE_LOGIN => null,
836 x_return_status => x_return_status,
837 x_msg_count => x_msg_count,
838 x_msg_data => x_msg_data );
839
840
841
842 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
843 fnd_message.set_name('CSF','CSF_PM_TASK_UPDATE_ERROR');
844 fnd_message.set_token('VALUE1',x_request_number);
845 fnd_message.set_token('VALUE2',conf_task_id(i));
846 fnd_message.set_token('VALUE3',l_product_number);
847 fnd_message.set_token('VALUE4',l_serial_number);
848 fnd_msg_pub.add;
849 Add_Err_Msg;
850 End If;
851
852
853 END LOOP;
854
855
856 COMMIT WORK;
857 END IF;
858
859 END IF;
860 /* rhungund - end - changes to associate access hours to the PM tasks */
861
862
863
864
865
866
867 Else
868
869
870 ROLLBACK to Generate_SR_Tasks_PVT;
871 End If;
872
873
874 END LOOP;
875 FND_FILE.put_line(FND_FILE.log,'MESSAGE: End of loop of UMPs !!! ');
876
877 If not fnd_profile.save('CSFPM_LAST_RUN_DATE',trunc(sysdate),'SITE') Then
878
879
880 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
881 End If;
882 COMMIT WORK;
883
884 --
885 -- End of API body
886 --
887
888 -- Standard call to get message count and if count is 1, get message info.
889 FND_MSG_PUB.Count_And_Get
890 ( p_count => x_msg_count,
891 p_data => x_msg_data
892 );
893 retcode := g_retcode;
894 EXCEPTION
895 WHEN FND_API.G_EXC_ERROR THEN
896 FND_FILE.put_line(FND_FILE.log,'MESSAGE: Inside exception 1');
897 retcode := 1;
898 errbuf := X_Msg_Data;
899 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
900 P_API_NAME => L_API_NAME
901 ,P_PKG_NAME => G_PKG_NAME
902 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
903 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
904 ,X_MSG_COUNT => X_MSG_COUNT
905 ,X_MSG_DATA => X_MSG_DATA
906 ,X_RETURN_STATUS => X_RETURN_STATUS);
907 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
908 FND_FILE.put_line(FND_FILE.log,'MESSAGE: Inside exception 2');
909 retcode := 1;
910 errbuf := X_Msg_Data;
911 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
912 P_API_NAME => L_API_NAME
913 ,P_PKG_NAME => G_PKG_NAME
914 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
915 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
916 ,X_MSG_COUNT => X_MSG_COUNT
917 ,X_MSG_DATA => X_MSG_DATA
918 ,X_RETURN_STATUS => X_RETURN_STATUS);
919 WHEN OTHERS THEN
920 FND_FILE.put_line(FND_FILE.log,'MESSAGE: Inside exception 3');
921 retcode := 1;
922 errbuf := X_Msg_Data;
923 l_sqlcode := SQLCODE;
924 l_sqlerrm := SQLERRM;
925 /* JTF_PLSQL_API.HANDLE_EXCEPTIONS(
926 P_API_NAME => L_API_NAME
927 ,P_PKG_NAME => G_PKG_NAME
928 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
929 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
930 ,P_SQLCODE => l_sqlcode
931 ,P_SQLERRM => l_sqlerrm
932 ,X_MSG_COUNT => X_MSG_COUNT
933 ,X_MSG_DATA => X_MSG_DATA
934 ,X_RETURN_STATUS => X_RETURN_STATUS); */
935 End Generate_SR_Tasks;
936
937
938
939 PROCEDURE update_ump (
940 errbuf OUT NOCOPY VARCHAR2,
941 retcode OUT NOCOPY NUMBER,
942 P_Api_Version_Number IN NUMBER
943 )
944 IS
945 l_api_name CONSTANT VARCHAR2(30) := 'Update_UMP';
946 l_api_version_number CONSTANT NUMBER := 1.0;
947 l_return_status_full VARCHAR2(1);
948 l_access_flag VARCHAR2(1);
949 l_sqlcode Number;
950 l_sqlerrm Varchar2(2000);
951 l_Rollback Varchar2(1) := 'Y';
952
953 X_Return_Status VARCHAR2(1);
954 X_Msg_Count NUMBER;
955 X_Msg_Data VARCHAR2(2000);
956
957 Cursor c_ump is
958 Select aueb.unit_effectivity_id,
959 cil.subject_id incident_id,
960 csi.close_date,
961 csi.customer_product_id,
962 cccv.counter_id,
963 cccv.counter_name,
964 nvl(cccv.net_reading,0) net_reading
965 From ahl_unit_effectivities_app_v aueb,
966 cs_incident_links cil,
967 cs_incidents_all_b csi,
968 csi_cp_counters_v cccv
969 Where (aueb.status_code is NULL
970 or aueb.status_code = 'INIT-DUE')
971 -- Application_usg_code PM for Preventive Maintenance seeded for CMRO 11.5.10 changes
972 and aueb.application_usg_code = 'PM'
973 and cil.object_id = aueb.unit_effectivity_id
974 and cil.object_type = 'AHL_UMP_EFF'
975 and cil.link_type_id = 6
976 and csi.incident_id = cil.subject_id
977 and csi.status_flag = 'C'
978 and cccv.customer_product_id(+) = aueb.csi_item_instance_id
979 order by aueb.unit_effectivity_id;
980
981 l_unit_effectivity_tbl ahl_ump_unitmaint_pvt.unit_effectivity_tbl_type;
982 l_unit_threshold_tbl ahl_ump_unitmaint_pvt.Unit_Threshold_tbl_type;
983 l_unit_accomplish_tbl ahl_ump_unitmaint_pvt.unit_accomplish_tbl_type;
984 l_count Number := 0;
985 l_index Number := 0;
986 l_prev_effectivity_id Number := -1;
987
988 BEGIN
989 -- Standard Start of API savepoint
990 SAVEPOINT update_ump_pvt;
991
992 -- Standard call to check for call compatibility.
993 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
994 p_api_version_number,
995 l_api_name,
996 G_PKG_NAME)
997 THEN
998 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
999 END IF;
1000
1001
1002 -- Initialize message list if p_init_msg_list is set to TRUE.
1003 FND_MSG_PUB.initialize;
1004
1005
1006 -- Initialize API return status to SUCCESS
1007 x_return_status := FND_API.G_RET_STS_SUCCESS;
1008
1009 --
1010 -- API body
1011 --
1012 FOR ump_rec in c_ump LOOP
1013 IF l_prev_effectivity_id <> ump_rec.unit_effectivity_id THEN
1014 -- Values to update AHL_UNIT_EFFECTIVITIES_B table.This
1015 -- check is to Record only unique values of unit_effectivity_id
1016 l_index := l_index + 1;
1017 l_unit_effectivity_tbl(l_index).UNIT_EFFECTIVITY_ID := ump_rec.unit_effectivity_id;
1018 l_unit_effectivity_tbl(l_index).STATUS_CODE := 'ACCOMPLISHED';
1019 l_unit_effectivity_tbl(l_index).ACCOMPLISHED_DATE := ump_rec.close_date;
1020 l_prev_effectivity_id := ump_rec.unit_effectivity_id;
1021 END IF;
1022 If ump_rec.counter_id is not null Then
1023 -- Counter values to update the ahl_unit_accomplishmnts table
1024 -- for accomplished UMPs. A product instance can have more than one
1025 -- counter
1026 l_count := l_count + 1;
1027 l_unit_accomplish_tbl(l_count).unit_effectivity_id := ump_rec.unit_effectivity_id;
1028 l_unit_accomplish_tbl(l_count).counter_id := ump_rec.counter_id;
1029 l_unit_accomplish_tbl(l_count).counter_name := ump_rec.counter_name;
1030 l_unit_accomplish_tbl(l_count).counter_value := ump_rec.net_reading;
1031 l_unit_accomplish_tbl(l_count).operation_flag := 'C';
1032 End If;
1033 END LOOP;
1034 ahl_ump_unitmaint_pub.capture_mr_updates
1035 (p_api_version => 1.0,
1036 p_init_msg_list => FND_API.G_FALSE,
1037 p_commit => FND_API.G_FALSE,
1038 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1039 p_unit_effectivity_tbl => l_unit_effectivity_tbl,
1040 p_x_unit_threshold_tbl => l_unit_threshold_tbl,
1041 p_x_unit_accomplish_tbl => l_unit_accomplish_tbl,
1042 x_return_status => x_return_status,
1043 x_msg_count => x_msg_count,
1044 x_msg_data => x_msg_data);
1045 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
1046 Add_Err_Msg;
1047 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1048 End If;
1049
1050 COMMIT WORK;
1051
1052 --
1053 -- End of API body
1054 --
1055
1056 -- Standard call to get message count and if count is 1, get message info.
1057 FND_MSG_PUB.Count_And_Get
1058 ( p_count => x_msg_count,
1059 p_data => x_msg_data
1060 );
1061 retcode := g_retcode;
1062
1063 EXCEPTION
1064 WHEN FND_API.G_EXC_ERROR THEN
1065 retcode := 2;
1066 errbuf := X_Msg_Data;
1067 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1068 P_API_NAME => L_API_NAME
1069 ,P_PKG_NAME => G_PKG_NAME
1070 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1071 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1072 ,X_MSG_COUNT => X_MSG_COUNT
1073 ,X_MSG_DATA => X_MSG_DATA
1074 ,X_RETURN_STATUS => X_RETURN_STATUS);
1075 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1076 retcode := 2;
1077 errbuf := X_Msg_Data;
1078 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1079 P_API_NAME => L_API_NAME
1080 ,P_PKG_NAME => G_PKG_NAME
1081 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1082 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1083 ,X_MSG_COUNT => X_MSG_COUNT
1084 ,X_MSG_DATA => X_MSG_DATA
1085 ,X_RETURN_STATUS => X_RETURN_STATUS);
1086 WHEN OTHERS THEN
1087 retcode := 2;
1088 errbuf := X_Msg_Data;
1089 l_sqlcode := SQLCODE;
1090 l_sqlerrm := SQLERRM;
1091 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1092 P_API_NAME => L_API_NAME
1093 ,P_PKG_NAME => G_PKG_NAME
1094 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
1095 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1096 ,P_SQLCODE => l_sqlcode
1097 ,P_SQLERRM => l_sqlerrm
1098 ,X_MSG_COUNT => X_MSG_COUNT
1099 ,X_MSG_DATA => X_MSG_DATA
1100 ,X_RETURN_STATUS => X_RETURN_STATUS);
1101 End update_ump;
1102
1103 PROCEDURE update_sr_tasks (
1104 errbuf OUT NOCOPY VARCHAR2,
1105 retcode OUT NOCOPY NUMBER,
1106 P_Api_Version_Number IN NUMBER
1107 )
1108 IS
1109 l_api_name CONSTANT VARCHAR2(30) := 'update_sr_tasks';
1110 l_api_version_number CONSTANT NUMBER := 1.0;
1111 l_return_status_full VARCHAR2(1);
1112 l_access_flag VARCHAR2(1);
1113 l_sqlcode Number;
1114 l_sqlerrm Varchar2(2000);
1115
1116 X_Return_Status VARCHAR2(1);
1117 X_Msg_Count NUMBER;
1118 X_Msg_Data VARCHAR2(2000);
1119
1120 Cursor c_incidents is
1121 Select aueb.unit_effectivity_id,
1122 cil.subject_id ,
1123 cil.link_id,
1124 csi.close_date,
1125 csi.customer_product_id,
1126 csi.object_version_number,
1127 jtb.task_id,
1128 jtb.planned_start_date,
1129 jtb.planned_end_date,
1130 jtb.scheduled_start_date,
1131 jtb.scheduled_end_date,
1132 jtb.actual_start_date,
1133 jtb.actual_end_date,
1134 jtb.object_version_number tasks_object_version
1135 From ahl_unit_effectivities_app_v aueb,
1136 cs_incident_links cil,
1137 cs_incidents_all_b csi,
1138 jtf_tasks_b jtb
1139 Where aueb.status_code in ('TERMINATED','EXCEPTION')
1140 -- Application_usg_code PM for Preventive Maintenance seeded for CMRO 11.5.10 changes
1141 and aueb.application_usg_code = 'PM'
1142 and cil.object_id = aueb.unit_effectivity_id
1143 and cil.object_type = 'AHL_UMP_EFF'
1144 and cil.link_type_id = 6
1145 and csi.incident_id = cil.subject_id
1146 and jtb.source_object_id = cil.subject_id
1147 order by cil.subject_id;
1148
1149 l_prev_rec c_incidents%ROWTYPE;
1150 incident_rec c_incidents%ROWTYPE;
1151 sr_update_success BOOLEAN;
1152
1153 x_interaction_id Number;
1154
1155 BEGIN
1156 -- Standard Start of API savepoint
1157 SAVEPOINT update_sr_tasks_pvt;
1158
1159 -- Standard call to check for call compatibility.
1160 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1161 p_api_version_number,
1162 l_api_name,
1163 G_PKG_NAME)
1164 THEN
1165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1166 END IF;
1167
1168 FND_MSG_PUB.initialize;
1169
1170 -- Initialize API return status to SUCCESS
1171 x_return_status := FND_API.G_RET_STS_SUCCESS;
1172
1173 --
1174 -- API body
1175 --
1176
1177 open c_incidents;
1178 LOOP
1179 Fetch c_incidents into incident_rec;
1180 EXIT WHEN c_incidents%NOTFOUND;
1181 If nvl(l_prev_rec.subject_id,-1) <> incident_rec.subject_id Then
1182 COMMIT WORK;
1183 SAVEPOINT update_sr_tasks_pvt;
1184 l_prev_rec := incident_rec;
1185 sr_update_success := FALSE;
1186 cs_servicerequest_pub.Update_Status
1187 (p_api_version => 2.0,
1188 p_init_msg_list => FND_API.G_FALSE,
1189 p_commit => FND_API.G_FALSE,
1190 x_return_status => x_return_status,
1191 x_msg_count => x_msg_count,
1192 x_msg_data => x_msg_data,
1193 p_resp_appl_id => FND_GLOBAL.RESP_APPL_ID,
1194 p_resp_id => FND_GLOBAL.RESP_ID,
1195 p_user_id => FND_GLOBAL.USER_ID,
1196 p_login_id => fnd_global.conc_login_id,
1197 p_request_id => incident_rec.subject_id,
1198 p_request_number => NULL,
1199 p_object_version_number => incident_rec.object_version_number,
1200 -- status code CLOSED seeded in cs_incident_statuses
1201 p_status_id => 4,
1202 p_status => 'CLOSED',
1203 x_interaction_id => x_interaction_id);
1204 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
1205 fnd_message.set_name('CSF','CSF_PM_SR_UPDATE_ERROR');
1206 fnd_message.set_token('VALUE1',incident_rec.subject_id);
1207 fnd_msg_pub.add;
1208 Add_Err_Msg;
1209 ROLLBACK TO update_sr_tasks_pvt;
1210 Else
1211 cs_incidentlinks_pub.DELETE_INCIDENTLINK (
1212 P_API_VERSION => 2.0,
1213 P_INIT_MSG_LIST => FND_API.G_FALSE,
1214 P_COMMIT => FND_API.G_FALSE,
1215 P_RESP_APPL_ID => NULL,
1216 P_RESP_ID => NULL,
1217 P_USER_ID => NULL,
1218 P_LOGIN_ID => FND_GLOBAL.CONC_LOGIN_ID,
1219 P_ORG_ID => NULL,
1220 P_LINK_ID => incident_rec.link_id,
1221 X_RETURN_STATUS => x_return_status,
1222 X_MSG_COUNT => x_msg_count,
1223 X_MSG_DATA => x_msg_data);
1224 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
1225 fnd_message.set_name('CSF','CSF_PM_SR_LINK_DELETE_ERROR');
1226 fnd_message.set_token('VALUE1',incident_rec.subject_id);
1227 fnd_msg_pub.add;
1228 Add_Err_Msg;
1229 ROLLBACK TO update_sr_tasks_pvt;
1230 Else
1231 sr_update_success := TRUE;
1232 End If;
1233 End If;
1234 End If;
1235 If sr_update_success Then
1236 csf_tasks_pub.Update_Task
1237 (p_api_version => 1.0,
1238 p_init_msg_list => FND_API.G_FALSE,
1239 p_commit => fnd_api.g_false,
1240 p_task_id => incident_rec.task_id,
1241 p_object_version_number => incident_rec.tasks_object_version,
1242 p_planned_start_date => incident_rec.planned_start_date,
1243 p_planned_end_date => incident_rec.planned_end_date,
1244 p_scheduled_start_date => incident_rec.scheduled_start_date,
1245 p_scheduled_end_date => incident_rec.scheduled_end_date,
1246 p_actual_start_date => incident_rec.actual_start_date,
1247 p_actual_end_date => incident_rec.actual_end_date,
1248 p_task_status_id => fnd_profile.value('csf_default_task_cancelled_status'), -- Task cancelled status
1249 x_return_status => x_return_status,
1250 x_msg_count => x_msg_count,
1251 x_msg_data => x_msg_data );
1252 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
1253 fnd_message.set_name('CSF','CSF_PM_TASK_UPDATE_ERROR');
1254 fnd_message.set_token('VALUE1',incident_rec.subject_id);
1255 fnd_message.set_token('VALUE2',incident_rec.task_id);
1256 fnd_msg_pub.add;
1257 Add_Err_Msg;
1258 ROLLBACK TO update_sr_tasks_pvt;
1259 End If;
1260 End if;
1261 END LOOP;
1262 COMMIT WORK;
1263 --
1264 -- End of API body
1265 --
1266
1267 -- Standard call to get message count and if count is 1, get message info.
1268 FND_MSG_PUB.Count_And_Get
1269 ( p_count => x_msg_count,
1270 p_data => x_msg_data
1271 );
1272 retcode := g_retcode;
1273 EXCEPTION
1274 WHEN FND_API.G_EXC_ERROR THEN
1275 retcode := 2;
1276 errbuf := X_Msg_Data;
1277 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1278 P_API_NAME => L_API_NAME
1279 ,P_PKG_NAME => G_PKG_NAME
1280 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1281 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1282 ,X_MSG_COUNT => X_MSG_COUNT
1283 ,X_MSG_DATA => X_MSG_DATA
1284 ,X_RETURN_STATUS => X_RETURN_STATUS);
1285 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1286 retcode := 2;
1287 errbuf := X_Msg_Data;
1288 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1289 P_API_NAME => L_API_NAME
1290 ,P_PKG_NAME => G_PKG_NAME
1291 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1292 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1293 ,X_MSG_COUNT => X_MSG_COUNT
1294 ,X_MSG_DATA => X_MSG_DATA
1295 ,X_RETURN_STATUS => X_RETURN_STATUS);
1296 WHEN OTHERS THEN
1297 retcode := 2;
1298 errbuf := X_Msg_Data;
1299 l_sqlcode := SQLCODE;
1300 l_sqlerrm := SQLERRM;
1301 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1302 P_API_NAME => L_API_NAME
1303 ,P_PKG_NAME => G_PKG_NAME
1304 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
1305 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1306 ,P_SQLCODE => l_sqlcode
1307 ,P_SQLERRM => l_sqlerrm
1308 ,X_MSG_COUNT => X_MSG_COUNT
1309 ,X_MSG_DATA => X_MSG_DATA
1310 ,X_RETURN_STATUS => X_RETURN_STATUS);
1311
1312 End update_sr_tasks;
1313
1314 End CSF_Preventive_Maintenance_PVT;
1315