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