[Home] [Help]
PACKAGE BODY: APPS.WIP_EAM_WORKREQUEST_PVT
Source
1 PACKAGE BODY WIP_EAM_WORKREQUEST_PVT as
2 /* $Header: WIPVWRPB.pls 120.11.12020000.3 2012/12/04 08:30:00 vboddapa ship $ */
3 /* Modified by yjhabak for Work Request Enhancement Project BUG No : 2997297 */
4 -- Start of comments
5 -- API name : WIP_EAM_WORKREQUEST_PVT
6 -- Type : Public
7 -- Function :
8 -- Pre-reqs : None.
9 -- Parameters :
10 -- OUT x_return_status OUT VARCHAR2(1)
11 -- x_msg_count OUT NUMBER
12 -- x_msg_data OUT VARCHAR2(2000)
13 --
14 -- Version Current version 1.0 Anirban Dey
15 --
16 -- Notes : Note text
17 --
18 -- End of comments
19
20 G_PKG_NAME CONSTANT VARCHAR2(30) :='WIP_EAM_WORKREQUEST_PVT';
21
22 PROCEDURE create_work_request (
23 p_api_version IN NUMBER,
24 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
25 p_commit IN VARCHAR2 := FND_API.G_FALSE,
26 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
27 p_org_id IN NUMBER,
28 p_asset_group_id IN NUMBER,
29 p_asset_number IN VARCHAR2,
30 p_priority_id IN NUMBER,
31 p_request_by_date IN DATE,
32 p_request_log IN VARCHAR2,
33 p_owning_dept_id IN NUMBER,
34 p_user_id IN NUMBER,
35 p_work_request_type_id IN NUMBER,
36 p_maintenance_object_type IN NUMBER,
37 p_maintenance_object_id IN NUMBER,
38 p_eam_linear_id IN NUMBER DEFAULT NULL,
39 p_work_request_created_by IN NUMBER,
40 p_created_for IN NUMBER DEFAULT NULL,
41 p_phone_number IN VARCHAR2 DEFAULT NULL,
42 p_email IN VARCHAR2 DEFAULT NULL,
43 p_contact_preference IN NUMBER DEFAULT NULL,
44 p_notify_originator IN NUMBER DEFAULT NULL,
45 p_attribute_category IN VARCHAR2 DEFAULT NULL,
46 p_attribute1 IN VARCHAR2 DEFAULT NULL,
47 p_attribute2 IN VARCHAR2 DEFAULT NULL,
48 p_attribute3 IN VARCHAR2 DEFAULT NULL,
49 p_attribute4 IN VARCHAR2 DEFAULT NULL,
50 p_attribute5 IN VARCHAR2 DEFAULT NULL,
51 p_attribute6 IN VARCHAR2 DEFAULT NULL,
52 p_attribute7 IN VARCHAR2 DEFAULT NULL,
53 p_attribute8 IN VARCHAR2 DEFAULT NULL,
54 p_attribute9 IN VARCHAR2 DEFAULT NULL,
55 p_attribute10 IN VARCHAR2 DEFAULT NULL,
56 p_attribute11 IN VARCHAR2 DEFAULT NULL,
57 p_attribute12 IN VARCHAR2 DEFAULT NULL,
58 p_attribute13 IN VARCHAR2 DEFAULT NULL,
59 p_attribute14 IN VARCHAR2 DEFAULT NULL,
60 p_attribute15 IN VARCHAR2 DEFAULT NULL,
61 x_request_id OUT NOCOPY NUMBER,
62 x_status_id OUT NOCOPY NUMBER,
63 x_return_status OUT NOCOPY VARCHAR2,
64 x_msg_count OUT NOCOPY NUMBER,
65 x_msg_data OUT NOCOPY VARCHAR2
66 ) is
67 l_api_name CONSTANT VARCHAR2(30) := 'create_work_request';
68 l_api_version CONSTANT NUMBER := 1.0;
69 l_work_request_id NUMBER;
70 l_status_id NUMBER;
71 l_asset_activity_id NUMBER;
72 l_work_request_note_id NUMBER;
73 l_auto_approve_flag VARCHAR2(1);
74 l_standard_log VARCHAR2(2000);
75 l_stmt_num NUMBER;
76 l_request_log VARCHAR2(2000);
77 l_maintenance_object_id NUMBER;
78 l_maintenance_object_type NUMBER;
79 l_owning_dept_id NUMBER;
80 l_asset_num_reqd VARCHAR2(1);
81 l_work_request_auto_approve VARCHAR2(1);
82 l_user_name VARCHAR2(50);
83
84 BEGIN
85 l_asset_activity_id := -1;
86 l_stmt_num := 10;
87 -- Standard Start of API savepoint
88 SAVEPOINT create_work_request_pvt;
89 l_stmt_num := 20;
90 -- Standard call to check for call compatibility.
91 IF NOT fnd_api.compatible_api_call(
92 l_api_version
93 ,p_api_version
94 ,l_api_name
95 ,g_pkg_name) THEN
96 RAISE fnd_api.g_exc_unexpected_error;
97 END IF;
98 l_stmt_num := 30;
99 -- Initialize message list if p_init_msg_list is set to TRUE.
100 IF fnd_api.to_boolean(p_init_msg_list) THEN
101 fnd_msg_pub.initialize;
102 END IF;
103 l_stmt_num := 40;
104 -- Initialize API return status to success
105 x_return_status := fnd_api.g_ret_sts_success;
106 l_stmt_num := 50;
107 -- API body
108
109 -- remove the carriage return character i.e remove chr(13)
110 -- fix for bug 2104571
111 l_request_log := replace(p_request_log,fnd_global.local_chr(13)||fnd_global.local_chr(10),fnd_global.local_chr(10));
112
113 SELECT wip_eam_work_requests_s.nextval
114 INTO l_work_request_id FROM DUAL;
115 x_request_id := l_work_request_id;
116 l_stmt_num := 60;
117 BEGIN
118 select NVL(work_request_auto_approve,'N')
119 into l_auto_approve_flag
120 from wip_eam_parameters
121 where organization_id = p_org_id;
122 EXCEPTION
123 WHEN OTHERS THEN
124 l_auto_approve_flag := 'N';
125 END;
126 l_stmt_num := 70;
127 IF l_auto_approve_flag = 'Y' then
128 l_status_id := 3; -- 'Awaiting work order'
129 else
130 l_status_id := 1; -- 'Open'
131 END IF;
132 x_status_id := l_status_id;
133 l_stmt_num := 80;
134
135 if (p_maintenance_object_id is not null OR (p_asset_group_id <> 0 AND p_asset_group_id IS NOT NULL AND p_asset_number is not null)) then
136 if (p_maintenance_object_id is not null) then
137 l_maintenance_object_type := 3;
138 l_maintenance_object_id := p_maintenance_object_id;
139
140 else
141 IF (p_asset_group_id <> 0 AND p_asset_group_id IS NOT NULL
142 AND p_asset_number is not null) THEN
143 BEGIN
144 l_maintenance_object_type := 3;
145 SELECT instance_id
146 INTO l_maintenance_object_id
147 FROM csi_item_instances
148 WHERE
149 inventory_item_id = p_asset_group_id AND
150 serial_number = p_asset_number;
151 EXCEPTION
152 WHEN OTHERS THEN
153 x_return_status := 'E';
154 END;
155
156
157 end if;
158 end if;
159
160 -- if owning dept is not specified, derive it from asset or eam parameters
161 IF (p_owning_dept_id is null) then
162 begin
163 -- select owning dept from asset
164 SELECT eomd.owning_department_id
165 INTO l_owning_dept_id
166 FROM eam_org_maint_defaults eomd
167 WHERE eomd.object_type(+) = 50
168 AND eomd.object_id = l_maintenance_object_id
169 and organization_id = p_org_id;
170 exception
171 when no_data_found then
172 null;
173 end;
174
175 else
176 l_owning_dept_id := p_owning_dept_id;
177
178 END IF;
179
180 END IF;
181
182 /* added by sraval as WR were not getting created if asset_number
183 is null and eam_parameters.default_dept is null. Code was ignoring
184 passed owning_dept_id
185 */
186 IF (l_owning_dept_id is null) then
187 if (p_owning_dept_id is null) then
188 -- if asset does not have owning dept,
189 -- get owning dept from eam parameter
190 SELECT default_department_id
191 INTO l_owning_dept_id
192 FROM wip_eam_parameters
193 WHERE organization_id = p_org_id;
194 else
195 l_owning_dept_id := p_owning_dept_id;
196 end if;
197 END IF;
198
199 BEGIN
200 SELECT NVL(UPPER(wep.work_request_auto_approve),'N'), NVL(UPPER(work_request_asset_num_reqd),'Y')
201 INTO l_work_request_auto_approve, l_asset_num_reqd
202 FROM WIP_EAM_PARAMETERS wep
203 WHERE wep.organization_id = p_org_id;
204 EXCEPTION
205 WHEN OTHERS THEN
206 l_work_request_auto_approve := 'N';
207 l_asset_num_reqd := 'Y';
208 END;
209
210 IF (l_work_request_auto_approve = 'N' AND l_owning_dept_id IS NULL) THEN
211 FND_MESSAGE.SET_NAME ('WIP','WIP_EAM_WR_DEPT_MANDATORY');
212 FND_MSG_PUB.ADD;
213 RAISE FND_API.G_EXC_ERROR;
214 END IF;
215
216 -- Bug # 3574258
217 IF (l_asset_num_reqd = 'Y' AND l_maintenance_object_id IS NULL) THEN
218 FND_MESSAGE.SET_NAME ('EAM','EAM_ENTER_ASSET_NUMBER_FIELD');
219 FND_MSG_PUB.ADD;
220 RAISE FND_API.G_EXC_ERROR;
221 END IF;
222
223 INSERT INTO wip_eam_work_requests(
224 work_request_id,
225 work_request_number,
226 last_update_date,
227 last_updated_by,
228 creation_date,
229 created_by,
230 last_update_login,
231 asset_number,
232 asset_group,
233 organization_id,
234 work_request_status_id,
235 work_request_priority_id,
236 work_request_owning_dept,
237 wip_entity_id,
238 eam_linear_location_id,
239 expected_resolution_date,
240 description,
241 work_request_type_id,
242 work_request_auto_approve,
243 work_request_created_by,
244 maintenance_object_type,
245 maintenance_object_id,
246 created_for,
247 phone_number,
248 e_mail,
249 contact_preference,
250 notify_originator,
251 attribute_category,
252 attribute1,
253 attribute2,
254 attribute3,
255 attribute4,
256 attribute5,
257 attribute6,
258 attribute7,
259 attribute8,
260 attribute9,
261 attribute10,
262 attribute11,
263 attribute12,
264 attribute13,
265 attribute14,
266 attribute15
267 )
268 VALUES(
269 l_work_request_id,
270 to_char(l_work_request_id),
271 sysdate,
272 NVL(p_user_id,FND_GLOBAL.user_id),
273 sysdate,
274 NVL(p_user_id,FND_GLOBAL.user_id),
275 NVL(p_user_id,FND_GLOBAL.login_id),
276 p_asset_number,
277 p_asset_group_id,
278 p_org_id,
279 l_status_id,
280 p_priority_id,
281 l_owning_dept_id,
282 null,
283 p_eam_linear_id,
284 p_request_by_date,
285 substrb(l_request_log, 1, 240), --changed for the bug 9088315
286 p_work_request_type_id,
287 l_auto_approve_flag,
288 NVL(p_user_id,FND_GLOBAL.user_id),
289 l_maintenance_object_type,
290 l_maintenance_object_id,
291 nvl(p_created_for,FND_GLOBAL.user_id),
292 p_phone_number,
293 p_email,
294 p_contact_preference,
295 p_notify_originator,
296 p_attribute_category,
297 p_attribute1,
298 p_attribute2,
299 p_attribute3,
300 p_attribute4,
301 p_attribute5,
302 p_attribute6,
303 p_attribute7,
304 p_attribute8,
305 p_attribute9,
306 p_attribute10,
307 p_attribute11,
308 p_attribute12,
309 p_attribute13,
310 p_attribute14,
311 p_attribute15
312 );
313 l_stmt_num := 90;
314
315 BEGIN
316 SELECT user_name
317 INTO l_user_name
318 FROM fnd_user
319 WHERE user_id = p_user_id;
320 EXCEPTION
321 When NO_DATA_FOUND then
322 l_user_name := FND_GLOBAL.USER_NAME ;
323 END;
324
325 SELECT '*** '||l_user_name||' ('
326 ||to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS')||') *** '
327 INTO l_standard_log
328 FROM DUAL;
329 l_stmt_num := 100;
330 SELECT wip_eam_work_req_notes_s.nextval
331 INTO l_work_request_note_id
332 FROM dual;
333 l_stmt_num := 110;
334 INSERT INTO wip_eam_work_req_notes(
335 work_request_note_id,
336 last_update_date,
337 last_updated_by,
338 creation_date,
339 created_by,
340 last_update_login,
341 work_request_id,
342 notes,
343 work_request_note_type,
344 notification_id
345 )
346 VALUES(
347 l_work_request_note_id,
348 sysdate,
349 NVL(p_user_id,FND_GLOBAL.user_id),
350 sysdate,
351 NVL(p_user_id,FND_GLOBAL.user_id),
352 NVL(p_user_id,FND_GLOBAL.login_id),
353 l_work_request_id,
354 l_standard_log,
355 1, -- 1 for request log, 2 for approver log
356 null
357 );
358 l_stmt_num := 120;
359 IF p_request_log is not null then
360 l_stmt_num := 130;
361 SELECT wip_eam_work_req_notes_s.nextval
362 INTO l_work_request_note_id FROM DUAL;
363 l_stmt_num := 90;
364 INSERT INTO wip_eam_work_req_notes(
365 work_request_note_id,
366 last_update_date,
367 last_updated_by,
368 creation_date,
369 created_by,
370 last_update_login,
371 work_request_id,
372 notes,
373 work_request_note_type,
374 notification_id
375 )
376 VALUES(
377 l_work_request_note_id,
378 sysdate,
379 NVL(p_user_id,FND_GLOBAL.user_id),
380 sysdate,
381 NVL(p_user_id,FND_GLOBAL.user_id),
382 NVL(p_user_id,FND_GLOBAL.login_id),
383 l_work_request_id,
384 l_request_log,
385 1, -- 1 for request log, 2 for approver log
386 null
387 );
388 END IF;
389
390
391 /* Hook for Eam Asset Log #4141712 Begin*/
392
393 IF l_maintenance_object_id is NOT NULL THEN
394
395 EAM_ASSET_LOG_PVT.INSERT_ROW(
396 p_event_date => sysdate,
397 p_event_type => 'EAM_SYSTEM_EVENTS',
398 p_event_id => 4,
399 p_organization_id => p_org_id,
400 p_instance_id => l_maintenance_object_id,
401 p_comments => l_request_log,
402 p_reference => l_work_request_id,
403 p_ref_id => l_work_request_id,
404 p_instance_number => null,
405 p_employee_id => nvl(p_created_for,FND_GLOBAL.user_id),
406 x_return_status => x_return_status,
407 x_msg_count => x_msg_count,
408 x_msg_data => x_msg_data
409 );
410
411 END IF;
412
413 /* Hook for Eam Asset Log #4141712 End*/
414
415 -- End of API body.
416 l_stmt_num := 998;
417 -- Standard check of p_commit.
418 IF fnd_api.to_boolean(p_commit) THEN
419 COMMIT WORK;
420 END IF;
421 l_stmt_num := 999;
422 -- Standard call to get message count and if count is 1, get message info.
423 fnd_msg_pub.count_and_get(
424 p_encoded => fnd_api.g_false
425 ,p_count => x_msg_count
426 ,p_data => x_msg_data);
427 EXCEPTION
428 WHEN fnd_api.g_exc_error THEN
429 -- dbms_output.put_line ('Line = '||l_stmt_num);
430 ROLLBACK TO create_work_request_pvt;
431 x_return_status := fnd_api.g_ret_sts_error;
432 fnd_msg_pub.count_and_get(
433 p_encoded => fnd_api.g_false
434 ,p_count => x_msg_count
435 ,p_data => x_msg_data);
436 WHEN fnd_api.g_exc_unexpected_error THEN
437 -- dbms_output.put_line ('Line = '||l_stmt_num);
438 ROLLBACK TO create_work_request_pvt;
439 x_return_status := fnd_api.g_ret_sts_unexp_error;
440 fnd_msg_pub.count_and_get(
441 p_encoded => fnd_api.g_false
442 ,p_count => x_msg_count
443 ,p_data => x_msg_data);
444 WHEN OTHERS THEN
445 -- dbms_output.put_line ('Line = '||l_stmt_num);
446 ROLLBACK TO create_work_request_pvt;
447 x_return_status := fnd_api.g_ret_sts_unexp_error;
448 IF fnd_msg_pub.check_msg_level(
449 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
450 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name||'('||l_stmt_num||')');
451 END IF;
452 fnd_msg_pub.count_and_get(
453 p_encoded => fnd_api.g_false
454 ,p_count => x_msg_count
455 ,p_data => x_msg_data);
456 END create_work_request;
457
458 -- Bug # 3544248 - yjhabak
459 -- Function to find the actual value of the fileds based on the parameter p_from_public_api and fnd_api constants.
460 FUNCTION actaul_value_char(p_from_public_api VARCHAR2, p_old_value VARCHAR2, p_new_value VARCHAR2) RETURN VARCHAR2 is
461 result VARCHAR2(240);
462 BEGIN
463 result := null;
464 IF (p_from_public_api = 'N') THEN
465 result := p_new_value;
466 ELSE
467 IF (p_new_value is null) THEN
468 result := p_old_value;
469 ELSIF (p_new_value = fnd_api.g_miss_char) THEN
470 result := null;
471 ELSE
472 result := p_new_value;
473 END IF;
474 END IF;
475 RETURN(result);
476 END;
477
478 FUNCTION actaul_value_number(p_from_public_api VARCHAR2, p_old_value NUMBER, p_new_value NUMBER) RETURN NUMBER is
479 result NUMBER;
480 BEGIN
481 result := 0;
482 IF (p_from_public_api = 'N') THEN
483 result := p_new_value;
484 ELSE
485 IF (p_new_value is null) THEN
486 result := p_old_value;
487 ELSIF (p_new_value = fnd_api.g_miss_num) THEN
488 result := null;
489 ELSE
490 result := p_new_value;
491 END IF;
492 END IF;
493 RETURN(result);
494 END;
495
496 -- function to return notes for descriptive flex field context codes
497
498 FUNCTION get_dff_notes(l_new_attribute VARCHAR2, l_old_attribute VARCHAR2, l_dffprompt VARCHAR2, l_null VARCHAR2)
499 RETURN VARCHAR2 is
500 l_dff_notes VARCHAR2(2000);
501 l_new_attribute1 varchar2(250);
502 BEGIN
503 IF l_new_attribute IS NULL THEN
504 l_new_attribute1 := l_null;
505 ELSE
506 l_new_attribute1 := l_new_attribute;
507 END IF;
508 IF ( l_old_attribute IS NULL ) THEN
509 l_dff_notes := '@@@ '||l_dffprompt||' : '||l_new_attribute1;
510 ELSE
511 l_dff_notes := '@@@ '||l_dffprompt||' : '||l_old_attribute||' -> '||l_new_attribute1;
512 END IF;
513 RETURN(l_dff_notes);
514 END;
515 -- function to find if a and b are same or not
516 -- returns 1 if they differ
517 FUNCTION isdifferent(a VARCHAR2, b VARCHAR2) RETURN NUMBER is
518 result NUMBER;
519 BEGIN
520 result := 0;
521 IF (a IS NULL ) then
522 IF (b IS NOT NULL) then
523 result := 1;
524 END IF;
525 ELSIF (b IS NULL) then
526 result := 1;
527 ELSIF (a <> b) then
528 result := 1;
529 END IF;
530 RETURN(result);
531 END;
532
533 FUNCTION isdifferent_number(a NUMBER, b NUMBER) RETURN NUMBER is
534 result NUMBER;
535 BEGIN
536 result := 0;
537 IF (a IS NULL ) then
538 IF (b IS NOT NULL) then
539 result := 1;
540 END IF;
541 ELSIF (b IS NULL) then
542 result := 1;
543 ELSIF (a <> b) then
544 result := 1;
545 END IF;
546 RETURN(result);
547 END;
548
549 -- To find the DFF attribute's user defined prompt name.
550 FUNCTION dff_prompt_name (
551 appl_short_name IN fnd_application.application_short_name%TYPE,
552 flexfield_name IN fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE,
553 attribute_name IN fnd_descr_flex_col_usage_vl.end_user_column_name%TYPE ,
554 attribute_category IN fnd_descr_flex_contexts.descriptive_flex_context_code%TYPE := 'Global Data Elements')
555 RETURN fnd_descr_flex_col_usage_vl.form_above_prompt%TYPE IS
556
557 flexfield fnd_dflex.dflex_r;
558 flexinfo fnd_dflex.dflex_dr;
559 context fnd_dflex.context_r;
560 segments fnd_dflex.segments_dr;
561 l_prompt fnd_descr_flex_col_usage_vl.form_above_prompt%TYPE;
562 l_i number;
563
564 BEGIN
565
566 fnd_dflex.get_flexfield(appl_short_name => appl_short_name,
567 flexfield_name => flexfield_name,
568 flexfield => flexfield,
569 flexinfo => flexinfo);
570
571 context.flexfield := flexfield;
572 context.context_code := attribute_category;
573 if (attribute_category is NULL OR attribute_category =' ') THEN
574 context.context_code := 'Global Data Elements';
575 END IF;
576 fnd_dflex.get_segments(context => context,
577 segments => segments);
578 l_prompt := NULL;
579
580 FOR l_i IN 1..segments.nsegments LOOP
581 IF segments.application_column_name(l_i) = attribute_name THEN
582 l_prompt := segments.row_prompt(l_i);
583 END IF;
584 END LOOP;
585
586 IF l_prompt is NULL AND attribute_name IS NOT NULL THEN
587 context.context_code := 'Global Data Elements';
588 fnd_dflex.get_segments(context => context,
589 segments => segments);
590 FOR l_i IN 1..segments.nsegments LOOP
591 IF segments.application_column_name(l_i) = attribute_name THEN
592 l_prompt := segments.row_prompt(l_i);
593 END IF;
594 END LOOP;
595 END IF;
596
597 return(l_prompt);
598 end;
599
600
601 PROCEDURE update_work_request (
602 p_api_version IN NUMBER,
603 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
604 p_commit IN VARCHAR2 := FND_API.G_FALSE,
605 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
606 p_org_id IN NUMBER,
607 p_asset_group_id IN NUMBER,
608 p_asset_number IN VARCHAR2,
609 p_request_id IN NUMBER,
610 p_status_id IN NUMBER,
611 p_priority_id IN NUMBER,
612 p_request_by_date IN DATE,
613 p_request_log IN VARCHAR2,
614 p_work_request_type_id IN NUMBER,
615 p_eam_linear_id IN NUMBER DEFAULT NULL,
616 p_owning_dept_id IN NUMBER,
617 p_user_id IN NUMBER,
618 p_created_for IN NUMBER,
619 p_phone_number IN VARCHAR2,
620 p_email IN VARCHAR2,
621 p_contact_preference IN NUMBER,
622 p_notify_originator IN NUMBER,
623 p_attribute_category IN VARCHAR2,
624 p_attribute1 IN VARCHAR2,
625 p_attribute2 IN VARCHAR2,
626 p_attribute3 IN VARCHAR2,
627 p_attribute4 IN VARCHAR2,
628 p_attribute5 IN VARCHAR2,
629 p_attribute6 IN VARCHAR2,
630 p_attribute7 IN VARCHAR2,
631 p_attribute8 IN VARCHAR2,
632 p_attribute9 IN VARCHAR2,
633 p_attribute10 IN VARCHAR2,
634 p_attribute11 IN VARCHAR2,
635 p_attribute12 IN VARCHAR2,
636 p_attribute13 IN VARCHAR2,
637 p_attribute14 IN VARCHAR2,
638 p_attribute15 IN VARCHAR2,
639 p_from_public_api IN VARCHAR2 DEFAULT 'N',
640 x_return_status OUT NOCOPY VARCHAR2,
641 x_msg_count OUT NOCOPY NUMBER,
642 x_msg_data OUT NOCOPY VARCHAR2
643 ) is
644 l_api_name CONSTANT VARCHAR2(30) := 'update_work_request';
645 l_api_version CONSTANT NUMBER := 1.0;
646 l_work_request_note_id NUMBER;
647 l_owning_dept_id NUMBER;
648 l_null VARCHAR2(10);
649 l_old_asset_number VARCHAR2(30);
650 l_old_asset_group_id NUMBER;
651 l_old_priority_id NUMBER;
652 l_old_owning_dept_id NUMBER;
653 l_old_request_by_date DATE;
654 l_old_status_id NUMBER;
655 l_old_work_request_type_id NUMBER;
656 l_old_eam_linear_id NUMBER;
657 l_old_created_for NUMBER;
658 l_old_phone_number VARCHAR2(4000);
659 l_old_email VARCHAR2(240);
660 l_old_contact_preference NUMBER;
661 l_old_notify_originator NUMBER;
662 l_old_attribute1 VARCHAR2(150);
663 l_old_attribute_category VARCHAR2(30);
664 l_old_attribute2 VARCHAR2(150);
665 l_old_attribute3 VARCHAR2(150);
666 l_old_attribute4 VARCHAR2(150);
667 l_old_attribute5 VARCHAR2(150);
668 l_old_attribute6 VARCHAR2(150);
669 l_old_attribute7 VARCHAR2(150);
670 l_old_attribute8 VARCHAR2(150);
671 l_old_attribute9 VARCHAR2(150);
672 l_old_attribute10 VARCHAR2(150);
673 l_old_attribute11 VARCHAR2(150);
674 l_old_attribute12 VARCHAR2(150);
675 l_old_attribute13 VARCHAR2(150);
676 l_old_attribute14 VARCHAR2(150);
677 l_old_attribute15 VARCHAR2(150);
678 l_new_asset_number VARCHAR2(30);
679 l_new_asset_group_id NUMBER;
680 l_new_priority_id NUMBER;
681 l_new_owning_dept_id NUMBER;
682 l_new_request_by_date DATE;
683 l_new_status_id NUMBER;
684 l_new_work_request_type_id NUMBER;
685 l_new_eam_linear_id NUMBER;
686 l_new_created_for NUMBER;
687 l_new_phone_number VARCHAR2(4000);
688 l_new_email VARCHAR2(240);
689 l_new_contact_preference NUMBER;
690 l_new_notify_originator NUMBER;
691 l_new_attribute1 VARCHAR2(150);
692 l_new_attribute_category VARCHAR2(30);
693 l_new_attribute2 VARCHAR2(150);
694 l_new_attribute3 VARCHAR2(150);
695 l_new_attribute4 VARCHAR2(150);
696 l_new_attribute5 VARCHAR2(150);
697 l_new_attribute6 VARCHAR2(150);
698 l_new_attribute7 VARCHAR2(150);
699 l_new_attribute8 VARCHAR2(150);
700 l_new_attribute9 VARCHAR2(150);
701 l_new_attribute10 VARCHAR2(150);
702 l_new_attribute11 VARCHAR2(150);
703 l_new_attribute12 VARCHAR2(150);
704 l_new_attribute13 VARCHAR2(150);
705 l_new_attribute14 VARCHAR2(150);
706 l_new_attribute15 VARCHAR2(150);
707 l_standard_log VARCHAR2(2000);
708 l_request_log VARCHAR2(2000);
709 l_another_log VARCHAR2(2000);
710 l_old_data VARCHAR2(80);
711 l_new_data VARCHAR2(80);
712 l_dff_notes VARCHAR2(4000);
713 l_new_maintenance_object_id NUMBER;
714 l_old_maintenance_object_id NUMBER;
715 l_counter NUMBER;
716 l_extended_log_flag VARCHAR2(1);
717 l_work_request_auto_approve VARCHAR2(1);
718 l_dffprompt fnd_descr_flex_col_usage_vl.form_above_prompt%TYPE;
719 l_stmt_num NUMBER;
720 l_results_out VARCHAR2(200);
721 l_error_message VARCHAR2(200);
722 WF_ERROR EXCEPTION;
723 l_old_wf_item_type varchar2(8);
724 l_old_wf_item_key varchar2(240);
725 l_timezone_id NUMBER;
726 l_timezone_code VARCHAR2(50);
727 l_asset_num_reqd VARCHAR2(1);
728 l_user_name VARCHAR2(50);
729 l_maintenance_object_type NUMBER; --15898728
730
731 BEGIN
732
733
734
735 l_stmt_num := 10;
736 -- Standard Start of API savepoint
737 SAVEPOINT update_work_request_pvt;
738 l_stmt_num := 20;
739 -- Standard call to check for call compatibility.
740 IF NOT fnd_api.compatible_api_call(
741 l_api_version
742 ,p_api_version
743 ,l_api_name
744 ,g_pkg_name) THEN
745 RAISE fnd_api.g_exc_unexpected_error;
746 END IF;
747 l_stmt_num := 30;
748 -- Initialize message list if p_init_msg_list is set to TRUE.
749 IF fnd_api.to_boolean(p_init_msg_list) THEN
750 fnd_msg_pub.initialize;
751 END IF;
752 -- Initialize API return status to success
753 x_return_status := fnd_api.g_ret_sts_success;
754
755 -- API body
756
757 l_stmt_num := 40;
758 SELECT asset_number,
759 asset_group,
760 maintenance_object_id,
761 work_request_priority_id,
762 work_request_owning_dept,
763 expected_resolution_date,
764 work_request_status_id,
765 work_request_type_id,
766 eam_linear_location_id,
767 created_for,
768 phone_number,
769 e_mail,
770 contact_preference,
771 notify_originator,
772 attribute_category,
773 attribute1,
774 attribute2,
775 attribute3,
776 attribute4,
777 attribute5,
778 attribute6,
779 attribute7,
780 attribute8,
781 attribute9,
782 attribute10,
783 attribute11,
784 attribute12,
785 attribute13,
786 attribute14,
787 attribute15
788 INTO l_old_asset_number,
789 l_old_asset_group_id,
790 l_old_maintenance_object_id,
791 l_old_priority_id,
792 l_old_owning_dept_id,
793 l_old_request_by_date,
794 l_old_status_id,
795 l_old_work_request_type_id,
796 l_old_eam_linear_id,
797 l_old_created_for,
798 l_old_phone_number,
799 l_old_email,
800 l_old_contact_preference,
801 l_old_notify_originator,
802 l_old_attribute_category,
803 l_old_attribute1,
804 l_old_attribute2,
805 l_old_attribute3,
806 l_old_attribute4,
807 l_old_attribute5,
808 l_old_attribute6,
809 l_old_attribute7,
810 l_old_attribute8,
811 l_old_attribute9,
812 l_old_attribute10,
813 l_old_attribute11,
814 l_old_attribute12,
815 l_old_attribute13,
816 l_old_attribute14,
817 l_old_attribute15
818 FROM wip_eam_work_requests
819 WHERE work_request_id = p_request_id;
820
821 l_stmt_num := 45;
822
823 -- Bug # 3544248.
824 l_new_asset_number := actaul_value_char(p_from_public_api,l_old_asset_number,p_asset_number);
825 l_new_asset_group_id := actaul_value_number(p_from_public_api,l_old_asset_group_id,p_asset_group_id);
826 l_new_priority_id := actaul_value_number(p_from_public_api,l_old_priority_id, p_priority_id);
827 l_new_owning_dept_id := actaul_value_number(p_from_public_api,l_old_owning_dept_id,p_owning_dept_id);
828 l_new_status_id := actaul_value_number(p_from_public_api,l_old_status_id,p_status_id);
829 l_new_work_request_type_id := actaul_value_number(p_from_public_api,l_old_work_request_type_id,p_work_request_type_id);
830 l_new_eam_linear_id := actaul_value_number(p_from_public_api,l_old_eam_linear_id,p_eam_linear_id);
831 l_new_created_for := actaul_value_number(p_from_public_api,l_old_created_for,p_created_for);
832 l_new_phone_number := actaul_value_char(p_from_public_api,l_old_phone_number,p_phone_number);
833 l_new_email := actaul_value_char(p_from_public_api,l_old_email,p_email);
834 l_new_contact_preference := actaul_value_number(p_from_public_api,l_old_contact_preference,p_contact_preference);
835 l_new_notify_originator := actaul_value_number(p_from_public_api,l_old_notify_originator,p_notify_originator);
836 l_new_attribute_category := actaul_value_char(p_from_public_api,l_old_attribute_category, p_attribute_category);
837 l_new_attribute1 := actaul_value_char(p_from_public_api,l_old_attribute1, p_attribute1);
838 l_new_attribute2 := actaul_value_char(p_from_public_api,l_old_attribute2, p_attribute2);
839 l_new_attribute3 := actaul_value_char(p_from_public_api,l_old_attribute3, p_attribute3);
840 l_new_attribute4 := actaul_value_char(p_from_public_api,l_old_attribute4, p_attribute4);
841 l_new_attribute5 := actaul_value_char(p_from_public_api,l_old_attribute5, p_attribute5);
842 l_new_attribute6 := actaul_value_char(p_from_public_api,l_old_attribute6, p_attribute6);
843 l_new_attribute7 := actaul_value_char(p_from_public_api,l_old_attribute7, p_attribute7);
844 l_new_attribute8 := actaul_value_char(p_from_public_api,l_old_attribute8, p_attribute8);
845 l_new_attribute9 := actaul_value_char(p_from_public_api,l_old_attribute9, p_attribute9);
846 l_new_attribute10 := actaul_value_char(p_from_public_api,l_old_attribute10, p_attribute10);
847 l_new_attribute11 := actaul_value_char(p_from_public_api,l_old_attribute11, p_attribute11);
848 l_new_attribute12 := actaul_value_char(p_from_public_api,l_old_attribute12, p_attribute12);
849 l_new_attribute13 := actaul_value_char(p_from_public_api,l_old_attribute13, p_attribute13);
850 l_new_attribute14 := actaul_value_char(p_from_public_api,l_old_attribute14, p_attribute14);
851 l_new_attribute15 := actaul_value_char(p_from_public_api,l_old_attribute15, p_attribute15);
852
853 l_stmt_num := 50;
854
855 -- Bug # 3574258
856 BEGIN
857 SELECT NVL(UPPER(work_request_asset_num_reqd),'Y')
858 INTO l_asset_num_reqd
859 FROM WIP_EAM_PARAMETERS wep
860 WHERE wep.organization_id = p_org_id;
861 EXCEPTION
862 WHEN NO_DATA_FOUND THEN
863 l_asset_num_reqd := 'Y';
864 END;
865
866 IF (l_asset_num_reqd = 'Y' AND l_new_asset_number IS NULL) THEN
867 FND_MESSAGE.SET_NAME ('EAM','EAM_ENTER_ASSET_NUMBER_FIELD');
868 FND_MSG_PUB.ADD;
869 RAISE FND_API.G_EXC_ERROR;
870 END IF;
871
872 BEGIN
873 SELECT user_name
874 INTO l_user_name
875 FROM fnd_user
876 WHERE user_id = p_user_id;
877 EXCEPTION
878 When NO_DATA_FOUND then
879 l_user_name := FND_GLOBAL.USER_NAME ;
880 END;
881
882 SELECT '*** '||l_user_name||' ('
883 ||to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS')||') *** '
884 INTO l_standard_log
885 FROM DUAL;
886 l_another_log := l_standard_log;
887 l_stmt_num := 60;
888
889 SELECT wip_eam_work_req_notes_s.nextval
890 INTO l_work_request_note_id
891 FROM dual;
892 l_stmt_num := 70;
893 INSERT INTO wip_eam_work_req_notes(
894 work_request_note_id,
895 last_update_date,
896 last_updated_by,
897 creation_date,
898 created_by,
899 last_update_login,
900 work_request_id,
901 notes,
902 work_request_note_type,
903 notification_id
904 )
905 values(
906 l_work_request_note_id,
907 sysdate,
908 NVL(p_user_id,FND_GLOBAL.user_id),
909 sysdate,
910 NVL(p_user_id,FND_GLOBAL.user_id),
911 NVL(p_user_id,FND_GLOBAL.login_id),
912 p_request_id,
913 l_standard_log,
914 1, -- 1 for request log, 2 for approver log
915 null -- Don't know what will be put here for notification_id
916 );
917
918
919 IF (l_new_asset_group_id <> 0 AND l_new_asset_group_id IS NOT NULL) THEN
920 BEGIN
921 l_maintenance_object_type := 3; --15898728
922 SELECT instance_id
923 INTO l_new_maintenance_object_id
924 FROM csi_item_instances
925 WHERE
926 inventory_item_id = l_new_asset_group_id AND
927 serial_number = l_new_asset_number;
928 EXCEPTION
929 WHEN NO_DATA_FOUND THEN
930 x_return_status := 'E';
931 raise fnd_api.g_exc_error;
932 END;
933 ELSE
934 l_new_maintenance_object_id := l_old_maintenance_object_id;
935 END IF;
936
937 -- Since decode function was not returning time value, using the below logic. Bug # 3179096.
938 IF (p_from_public_api = 'N') THEN
939 l_new_request_by_date := p_request_by_date;
940 ELSE
941 IF (p_request_by_date is null) THEN
942 l_new_request_by_date := l_old_request_by_date;
943 ELSE
944 IF (p_request_by_date = fnd_api.g_miss_date) THEN
945 l_new_request_by_date := null;
946 ELSE
947 l_new_request_by_date := p_request_by_date;
948 END IF;
949 END IF;
950 END IF;
951
952 UPDATE wip_eam_work_requests SET
953 last_update_date = sysdate,
954 last_updated_by = NVL(p_user_id,FND_GLOBAL.user_id),
955 last_update_login = NVL(p_user_id,FND_GLOBAL.login_id),
956 work_request_priority_id = l_new_priority_id,
957 work_request_owning_dept = l_new_owning_dept_id,
958 work_request_status_id = l_new_status_id,
959 expected_resolution_date = l_new_request_by_date,
960 work_request_type_id = l_new_work_request_type_id,
961 eam_linear_location_id = l_new_eam_linear_id,
962 asset_number = l_new_asset_number,
963 asset_group = l_new_asset_group_id,
964 maintenance_object_id = l_new_maintenance_object_id,
965 maintenance_object_type = l_maintenance_object_type,
966 created_for = l_new_created_for,
967 phone_number = l_new_phone_number,
968 e_mail = l_new_email,
969 contact_preference = l_new_contact_preference,
970 notify_originator = l_new_notify_originator,
971 ATTRIBUTE_CATEGORY = l_new_attribute_category,
972 ATTRIBUTE1 = l_new_attribute1,
973 ATTRIBUTE2 = l_new_attribute2,
974 ATTRIBUTE3 = l_new_attribute3,
975 ATTRIBUTE4 = l_new_attribute4,
976 ATTRIBUTE5 = l_new_attribute5,
977 ATTRIBUTE6 = l_new_attribute6,
978 ATTRIBUTE7 = l_new_attribute7,
979 ATTRIBUTE8 = l_new_attribute8,
980 ATTRIBUTE9 = l_new_attribute9,
981 ATTRIBUTE10 = l_new_attribute10,
982 ATTRIBUTE11 = l_new_attribute11,
983 ATTRIBUTE12 = l_new_attribute12,
984 ATTRIBUTE13 = l_new_attribute13,
985 ATTRIBUTE14 = l_new_attribute14,
986 ATTRIBUTE15 = l_new_attribute15
987 WHERE work_request_id=p_request_id;
988
989 BEGIN
990 SELECT NVL(UPPER(wep.work_req_extended_log_flag), 'N'), NVL(UPPER(wep.work_request_auto_approve),'N')
991 INTO l_extended_log_flag, l_work_request_auto_approve
992 FROM WIP_EAM_PARAMETERS wep
993 WHERE wep.organization_id = p_org_id;
994
995 EXCEPTION
996 WHEN OTHERS THEN
997 l_extended_log_flag := 'Y';
998 END;
999
1000
1001 -- Check the extended log setting from wip_eam_parameters
1002
1003 IF (l_extended_log_flag = 'Y') THEN
1004 l_stmt_num := 140;
1005
1006 FND_MESSAGE.SET_NAME ('WIP', 'WIP_EAM_DISPLAY_NULL');
1007 l_null := FND_MESSAGE.GET;
1008
1009 IF ( isdifferent(l_old_asset_number, l_new_asset_number) = 1 OR
1010 isdifferent_number(l_old_asset_group_id, l_new_asset_group_id) = 1) THEN
1011 l_stmt_num := 230;
1012
1013 IF (isdifferent_number(l_old_asset_group_id, l_new_asset_group_id) = 1) THEN
1014 SELECT wip_eam_work_req_notes_s.nextval
1015 INTO l_work_request_note_id
1016 FROM dual;
1017 IF (l_old_asset_group_id IS NOT NULL) THEN
1018 BEGIN
1019 SELECT concatenated_segments INTO l_old_data
1020 FROM mtl_system_items_b_kfv
1021 WHERE organization_id = p_org_id
1022 AND inventory_item_id = l_old_asset_group_id;
1023 EXCEPTION
1024 WHEN NO_DATA_FOUND THEN
1025 l_old_data := l_null;
1026 END;
1027 ELSE
1028 l_old_data := l_null;
1029 END IF;
1030 IF (l_new_asset_group_id IS NOT NULL) THEN
1031 BEGIN
1032 SELECT concatenated_segments INTO l_new_data
1033 FROM mtl_system_items_b_kfv
1034 WHERE organization_id = p_org_id
1035 AND inventory_item_id = l_new_asset_group_id;
1036 EXCEPTION
1037 WHEN NO_DATA_FOUND THEN
1038 l_new_data := l_null;
1039 END;
1040 ELSE
1041 l_new_data := l_null;
1042 END IF;
1043 FND_MESSAGE.SET_NAME ('WIP', 'WIP_EAM_ASSET_GROUP_PROMPT');
1044
1045 INSERT INTO wip_eam_work_req_notes(
1046 work_request_note_id,
1047 last_update_date,
1048 last_updated_by,
1049 creation_date,
1050 created_by,
1051 last_update_login,
1052 work_request_id,
1053 notes,
1054 work_request_note_type,
1055 notification_id
1056 )
1057 VALUES(
1058 l_work_request_note_id,
1059 sysdate,
1060 NVL(p_user_id,FND_GLOBAL.user_id),
1061 sysdate,
1062 NVL(p_user_id,FND_GLOBAL.user_id),
1063 NVL(p_user_id,FND_GLOBAL.login_id),
1064 p_request_id,
1065 '@@@ '||FND_MESSAGE.GET||' : '||l_old_data||' -> '||l_new_data,
1066 1, -- 1 for request log, 2 for approver log
1067 null
1068 );
1069 END IF;
1070 SELECT wip_eam_work_req_notes_s.nextval
1071 INTO l_work_request_note_id
1072 FROM dual;
1073 FND_MESSAGE.SET_NAME ('WIP', 'WIP_EAM_ASSET_NUMBER_PROMPT');
1074
1075 INSERT INTO wip_eam_work_req_notes(
1076 work_request_note_id,
1077 last_update_date,
1078 last_updated_by,
1079 creation_date,
1080 created_by,
1081 last_update_login,
1082 work_request_id,
1083 notes,
1084 work_request_note_type,
1085 notification_id
1086 )
1087 VALUES(
1088 l_work_request_note_id,
1089 sysdate,
1090 NVL(p_user_id,FND_GLOBAL.user_id),
1091 sysdate,
1092 NVL(p_user_id,FND_GLOBAL.user_id),
1093 NVL(p_user_id,FND_GLOBAL.login_id),
1094 p_request_id,
1095 '@@@ '||FND_MESSAGE.GET||' : '||nvl(l_old_asset_number,l_null)||' -> '||nvl(l_new_asset_number,l_null),
1096 1, -- 1 for request log, 2 for approver log
1097 null
1098 );
1099 END IF;
1100
1101 IF (isdifferent_number(l_old_priority_id,l_new_priority_id) = 1) THEN
1102 l_stmt_num := 150;
1103 SELECT wip_eam_work_req_notes_s.nextval
1104 INTO l_work_request_note_id
1105 FROM dual;
1106 l_stmt_num := 160;
1107 BEGIN
1108 SELECT meaning
1109 INTO l_old_data
1110 FROM MFG_LOOKUPS
1111 WHERE lookup_type = 'WIP_EAM_ACTIVITY_PRIORITY'
1112 AND lookup_code = l_old_priority_id;
1113 EXCEPTION
1114 WHEN NO_DATA_FOUND THEN
1115 l_old_data := l_null;
1116 END;
1117 l_stmt_num := 170;
1118 BEGIN
1119 SELECT meaning
1120 INTO l_new_data
1121 FROM MFG_LOOKUPS
1122 WHERE lookup_type = 'WIP_EAM_ACTIVITY_PRIORITY'
1123 AND lookup_code = l_new_priority_id;
1124 EXCEPTION
1125 WHEN NO_DATA_FOUND THEN
1126 l_new_data := l_null;
1127 END;
1128 FND_MESSAGE.SET_NAME ('WIP', 'WIP_EAM_PRIORITY_PROMPT');
1129 l_stmt_num := 180;
1130
1131 INSERT INTO wip_eam_work_req_notes(
1132 work_request_note_id,
1133 last_update_date,
1134 last_updated_by,
1135 creation_date,
1136 created_by,
1137 last_update_login,
1138 work_request_id,
1139 notes,
1140 work_request_note_type,
1141 notification_id
1142 )
1143 VALUES(
1144 l_work_request_note_id,
1145 sysdate,
1146 NVL(p_user_id,FND_GLOBAL.user_id),
1147 sysdate,
1148 NVL(p_user_id,FND_GLOBAL.user_id),
1149 NVL(p_user_id,FND_GLOBAL.login_id),
1150 p_request_id,
1151 '@@@ '||FND_MESSAGE.GET||' : '||l_old_data||' -> '||l_new_data,
1152 1, -- 1 for request log, 2 for approver log
1153 null
1154 );
1155 END IF;
1156 l_stmt_num := 182;
1157
1158 IF (isdifferent_number(l_old_work_request_type_id,l_new_work_request_type_id) = 1) THEN
1159 l_stmt_num := 150;
1160 SELECT wip_eam_work_req_notes_s.nextval
1161 INTO l_work_request_note_id
1162 FROM DUAL;
1163 l_stmt_num := 184;
1164 BEGIN
1165 SELECT meaning
1166 INTO l_old_data
1167 FROM MFG_LOOKUPS
1168 WHERE lookup_type = 'WIP_EAM_WORK_REQ_TYPE'
1169 AND lookup_code = NVL(l_old_work_request_type_id,0);
1170 EXCEPTION
1171 When NO_DATA_FOUND then
1172 l_old_data := l_null;
1173 END;
1174 l_stmt_num := 186;
1175 BEGIN
1176 SELECT meaning
1177 INTO l_new_data
1178 FROM MFG_LOOKUPS
1179 WHERE lookup_type = 'WIP_EAM_WORK_REQ_TYPE'
1180 AND lookup_code = l_new_work_request_type_id;
1181 EXCEPTION
1182 When NO_DATA_FOUND then
1183 l_new_data := l_null;
1184 END;
1185 FND_MESSAGE.SET_NAME ('WIP', 'WIP_EAM_TYPE_PROMPT');
1186 l_stmt_num := 188;
1187
1188 INSERT INTO wip_eam_work_req_notes(
1189 work_request_note_id,
1190 last_update_date,
1191 last_updated_by,
1192 creation_date,
1193 created_by,
1194 last_update_login,
1195 work_request_id,
1196 notes,
1197 work_request_note_type,
1198 notification_id
1199 )
1200 VALUES(
1201 l_work_request_note_id,
1202 sysdate,
1203 NVL(p_user_id,FND_GLOBAL.user_id),
1204 sysdate,
1205 NVL(p_user_id,FND_GLOBAL.user_id),
1206 NVL(p_user_id,FND_GLOBAL.login_id),
1207 p_request_id,
1208 '@@@ '||FND_MESSAGE.GET||' : '||l_old_data||' -> '||l_new_data,
1209 1, -- 1 for request log, 2 for approver log
1210 null
1211 );
1212 END IF;
1213
1214 IF (isdifferent_number(l_old_status_id, l_new_status_id) = 1) THEN
1215 l_stmt_num := 190;
1216 SELECT wip_eam_work_req_notes_s.nextval
1217 INTO l_work_request_note_id
1218 FROM DUAL;
1219 l_stmt_num := 200;
1220 BEGIN
1221 SELECT meaning
1222 INTO l_old_data
1223 FROM MFG_LOOKUPS
1224 WHERE lookup_type = 'WIP_EAM_WORK_REQ_STATUS'
1225 AND lookup_code = l_old_status_id;
1226 EXCEPTION
1227 WHEN NO_DATA_FOUND THEN
1228 l_old_data := l_null;
1229 END;
1230 l_stmt_num := 210;
1231 BEGIN
1232 SELECT meaning
1233 INTO l_new_data
1234 FROM MFG_LOOKUPS
1235 WHERE lookup_type = 'WIP_EAM_WORK_REQ_STATUS'
1236 AND lookup_code = l_new_status_id;
1237 EXCEPTION
1238 WHEN NO_DATA_FOUND THEN
1239 l_new_data := l_null;
1240 END;
1241
1242 FND_MESSAGE.SET_NAME ('WIP', 'WIP_EAM_STATUS_PROMPT');
1243 l_stmt_num := 220;
1244 INSERT INTO wip_eam_work_req_notes(
1245 work_request_note_id,
1246 last_update_date,
1247 last_updated_by,
1248 creation_date,
1249 created_by,
1250 last_update_login,
1251 work_request_id,
1252 notes,
1253 work_request_note_type,
1254 notification_id
1255 )
1256 VALUES(
1257 l_work_request_note_id,
1258 sysdate,
1259 NVL(p_user_id,FND_GLOBAL.user_id),
1260 sysdate,
1261 NVL(p_user_id,FND_GLOBAL.user_id),
1262 NVL(p_user_id,FND_GLOBAL.login_id),
1263 p_request_id,
1264 '@@@ '||FND_MESSAGE.GET||' : '||l_old_data||' -> '||l_new_data,
1265 1, -- 1 for request log, 2 for approver log
1266 null
1267 );
1268 END IF;
1269
1270 IF (isdifferent_number(l_old_owning_dept_id, l_new_owning_dept_id) = 1) THEN
1271 l_stmt_num := 230;
1272 SELECT wip_eam_work_req_notes_s.nextval
1273 INTO l_work_request_note_id
1274 FROM dual;
1275 l_stmt_num := 240;
1276 BEGIN
1277 SELECT department_code
1278 INTO l_old_data
1279 FROM BOM_DEPARTMENTS
1280 WHERE department_id = l_old_owning_dept_id
1281 AND organization_id = p_org_id;
1282 EXCEPTION
1283 WHEN NO_DATA_FOUND THEN
1284 l_old_data := l_null;
1285 END;
1286 l_stmt_num := 250;
1287 BEGIN
1288 SELECT department_code
1289 INTO l_new_data
1290 FROM BOM_DEPARTMENTS
1291 WHERE department_id = l_new_owning_dept_id
1292 AND organization_id = p_org_id;
1293 EXCEPTION
1294 WHEN NO_DATA_FOUND THEN
1295 l_new_data := l_null;
1296 END;
1297 FND_MESSAGE.SET_NAME ('WIP', 'WIP_EAM_DEPT_PROMPT');
1298 l_stmt_num := 260;
1299
1300 INSERT INTO wip_eam_work_req_notes(
1301 work_request_note_id,
1302 last_update_date,
1303 last_updated_by,
1304 creation_date,
1305 created_by,
1306 last_update_login,
1307 work_request_id,
1308 notes,
1309 work_request_note_type,
1310 notification_id
1311 )
1312 VALUES(
1313 l_work_request_note_id,
1314 sysdate,
1315 NVL(p_user_id,FND_GLOBAL.user_id),
1316 sysdate,
1317 NVL(p_user_id,FND_GLOBAL.user_id),
1318 NVL(p_user_id,FND_GLOBAL.login_id),
1319 p_request_id,
1320 '@@@ '||FND_MESSAGE.GET||' : '||l_old_data||' -> '||l_new_data,
1321 1, -- 1 for request log, 2 for approver log
1322 null
1323 );
1324 END IF;
1325
1326 IF (l_old_request_by_date <> l_new_request_by_date) THEN
1327 FND_MESSAGE.SET_NAME ('WIP','WIP_EAM_REQ_BY_DATE_PROMPT');
1328 l_stmt_num := 270;
1329 SELECT wip_eam_work_req_notes_s.nextval
1330 INTO l_work_request_note_id
1331 FROM DUAL;
1332 -- To display server time zone code.
1333 SELECT fnd_profile.value('SERVER_TIMEZONE_ID') INTO l_timezone_id FROM DUAL;
1334 l_timezone_code := null;
1335 IF (l_timezone_id is not null) THEN
1336 SELECT timezone_code INTO l_timezone_code FROM fnd_timezones_vl WHERE upgrade_tz_id = l_timezone_id;
1337 IF (l_timezone_code IS NOT null) THEN
1338 l_timezone_code := '('||l_timezone_code||')';
1339 END IF;
1340 END IF;
1341 l_stmt_num := 280;
1342 INSERT INTO wip_eam_work_req_notes(
1343 work_request_note_id,
1344 last_update_date,
1345 last_updated_by,
1346 creation_date,
1347 created_by,
1348 last_update_login,
1349 work_request_id,
1350 notes,
1351 work_request_note_type,
1352 notification_id
1353 )
1354 VALUES(
1355 l_work_request_note_id,
1356 sysdate,
1357 NVL(p_user_id,FND_GLOBAL.user_id),
1358 sysdate,
1359 NVL(p_user_id,FND_GLOBAL.user_id),
1360 NVL(p_user_id,FND_GLOBAL.login_id),
1361 p_request_id,
1362 '@@@ '||FND_MESSAGE.GET||l_timezone_code ||' : '||to_char(l_old_request_by_date, 'dd-MON-yyyy hh24:mi:ss')||' -> '
1363 ||to_char(l_new_request_by_date, 'dd-MON-yyyy hh24:mi:ss') ,
1364 1, -- 1 for request log, 2 for approver log
1365 null
1366 );
1367 --l_another_log := l_another_log ||chr(10) ||'@@@ '||FND_MESSAGE.GET||' : '||l_old_request_by_date||' -> ' ||p_request_by_date;
1368 END IF;
1369
1370 IF (isdifferent_number(l_old_created_for, l_new_created_for) = 1) THEN
1371 l_stmt_num := 150;
1372 SELECT wip_eam_work_req_notes_s.nextval
1373 INTO l_work_request_note_id
1374 FROM DUAL;
1375 l_stmt_num := 184;
1376 BEGIN
1377 SELECT user_name
1378 INTO l_old_data
1379 FROM fnd_user
1380 WHERE user_id = l_old_created_for;
1381 EXCEPTION
1382 When NO_DATA_FOUND then
1383 l_old_data := l_null;
1384 END;
1385 BEGIN
1386 SELECT user_name
1387 INTO l_new_data
1388 FROM fnd_user
1389 WHERE user_id = l_new_created_for;
1390 EXCEPTION
1391 When NO_DATA_FOUND then
1392 l_new_data := l_null;
1393 END;
1394 FND_MESSAGE.SET_NAME ('WIP', 'WIP_EAM_CREATED_FOR_PROMPT');
1395
1396 INSERT INTO wip_eam_work_req_notes(
1397 work_request_note_id,
1398 last_update_date,
1399 last_updated_by,
1400 creation_date,
1401 created_by,
1402 last_update_login,
1403 work_request_id,
1404 notes,
1405 work_request_note_type,
1406 notification_id
1407 )
1408 VALUES(
1409 l_work_request_note_id,
1410 sysdate,
1411 NVL(p_user_id,FND_GLOBAL.user_id),
1412 sysdate,
1413 NVL(p_user_id,FND_GLOBAL.user_id),
1414 NVL(p_user_id,FND_GLOBAL.login_id),
1415 p_request_id,
1416 '@@@ '||FND_MESSAGE.GET||' : '||l_old_data||' -> '||l_new_data,
1417 1, -- 1 for request log, 2 for approver log
1418 null
1419 );
1420 END IF;
1421
1422 IF (isdifferent(l_old_email, l_new_email) = 1) THEN
1423 SELECT wip_eam_work_req_notes_s.nextval
1424 INTO l_work_request_note_id
1425 FROM DUAL;
1426 FND_MESSAGE.SET_NAME ('WIP', 'WIP_EAM_EMAIL_PROMPT');
1427 INSERT INTO wip_eam_work_req_notes(
1428 work_request_note_id,
1429 last_update_date,
1430 last_updated_by,
1431 creation_date,
1432 created_by,
1433 last_update_login,
1434 work_request_id,
1435 notes,
1436 work_request_note_type,
1437 notification_id
1438 )
1439 VALUES(
1440 l_work_request_note_id,
1441 sysdate,
1442 NVL(p_user_id,FND_GLOBAL.user_id),
1443 sysdate,
1444 NVL(p_user_id,FND_GLOBAL.user_id),
1445 NVL(p_user_id,FND_GLOBAL.login_id),
1446 p_request_id,
1447 '@@@ '||FND_MESSAGE.GET||' : '||nvl(l_old_email,l_null)||' -> '||nvl(l_new_email,l_null),
1448 1, -- 1 for request log, 2 for approver log
1449 null
1450 );
1451 END IF;
1452
1453 IF (isdifferent(l_old_phone_number, l_new_phone_number) = 1) THEN
1454 SELECT wip_eam_work_req_notes_s.nextval
1455 INTO l_work_request_note_id
1456 FROM DUAL;
1457 FND_MESSAGE.SET_NAME ('WIP', 'WIP_EAM_PHONE_NUMBER_PROMPT');
1458 INSERT INTO wip_eam_work_req_notes(
1459 work_request_note_id,
1460 last_update_date,
1461 last_updated_by,
1462 creation_date,
1463 created_by,
1464 last_update_login,
1465 work_request_id,
1466 notes,
1467 work_request_note_type,
1468 notification_id
1469 )
1470 VALUES(
1471 l_work_request_note_id,
1472 sysdate,
1473 NVL(p_user_id,FND_GLOBAL.user_id),
1474 sysdate,
1475 NVL(p_user_id,FND_GLOBAL.user_id),
1476 NVL(p_user_id,FND_GLOBAL.login_id),
1477 p_request_id,
1478 '@@@ '||FND_MESSAGE.GET||' : '||nvl(l_old_phone_number,l_null)||' -> '||nvl(l_new_phone_number,l_null),
1479 1, /* 1 for request log, 2 for approver log*/
1480 null
1481 );
1482 END IF;
1483
1484 IF (isdifferent_number(l_new_notify_originator, l_old_notify_originator) = 1) THEN
1485 SELECT wip_eam_work_req_notes_s.nextval
1486 INTO l_work_request_note_id
1487 FROM DUAL;
1488 BEGIN
1489 SELECT meaning
1490 INTO l_old_data
1491 FROM MFG_LOOKUPS
1492 WHERE lookup_type = 'SYS_YES_NO'
1493 AND lookup_code = l_old_notify_originator;
1494 EXCEPTION
1495 When NO_DATA_FOUND then
1496 l_old_data := l_null;
1497 END;
1498 BEGIN
1499 SELECT meaning
1500 INTO l_new_data
1501 FROM MFG_LOOKUPS
1502 WHERE lookup_type = 'SYS_YES_NO'
1503 AND lookup_code = l_new_notify_originator;
1504 EXCEPTION
1505 When NO_DATA_FOUND then
1506 l_new_data := l_null;
1507 END;
1508 FND_MESSAGE.SET_NAME ('WIP', 'WIP_EAM_NOTIFY_USER_PROMPT');
1509 INSERT INTO wip_eam_work_req_notes(
1510 work_request_note_id,
1511 last_update_date,
1512 last_updated_by,
1513 creation_date,
1514 created_by,
1515 last_update_login,
1516 work_request_id,
1517 notes,
1518 work_request_note_type,
1519 notification_id
1520 )
1521 VALUES(
1522 l_work_request_note_id,
1523 sysdate,
1524 NVL(p_user_id,FND_GLOBAL.user_id),
1525 sysdate,
1526 NVL(p_user_id,FND_GLOBAL.user_id),
1527 NVL(p_user_id,FND_GLOBAL.login_id),
1528 p_request_id,
1529 '@@@ '||FND_MESSAGE.GET||' : '||l_old_data||' -> '||l_new_data,
1530 1, -- 1 for request log, 2 for approver log
1531 null
1532 );
1533 END IF;
1534
1535 IF (isdifferent_number(l_new_contact_preference,l_old_contact_preference) = 1) THEN
1536 SELECT wip_eam_work_req_notes_s.nextval
1537 INTO l_work_request_note_id
1538 FROM DUAL;
1539 BEGIN
1540 SELECT meaning
1541 INTO l_old_data
1542 FROM MFG_LOOKUPS
1543 WHERE lookup_type = 'WIP_EAM_CONTACT_PREFERENCE'
1544 AND lookup_code = l_old_contact_preference;
1545 EXCEPTION
1546 When NO_DATA_FOUND then
1547 l_old_data := l_null;
1548 END;
1549 BEGIN
1550 SELECT meaning
1551 INTO l_new_data
1552 FROM MFG_LOOKUPS
1553 WHERE lookup_type = 'WIP_EAM_CONTACT_PREFERENCE'
1554 AND lookup_code = l_new_contact_preference;
1555 EXCEPTION
1556 When NO_DATA_FOUND then
1557 l_new_data := l_null;
1558 END;
1559 FND_MESSAGE.SET_NAME ('WIP', 'WIP_EAM_CONTACT_PREF_PROMPT');
1560 INSERT INTO wip_eam_work_req_notes(
1561 work_request_note_id,
1562 last_update_date,
1563 last_updated_by,
1564 creation_date,
1565 created_by,
1566 last_update_login,
1567 work_request_id,
1568 notes,
1569 work_request_note_type,
1570 notification_id
1571 )
1572 VALUES(
1573 l_work_request_note_id,
1574 sysdate,
1575 NVL(p_user_id,FND_GLOBAL.user_id),
1576 sysdate,
1577 NVL(p_user_id,FND_GLOBAL.user_id),
1578 NVL(p_user_id,FND_GLOBAL.login_id),
1579 p_request_id,
1580 '@@@ '||FND_MESSAGE.GET||' : '||l_old_data||' -> '||l_new_data,
1581 1, -- 1 for request log, 2 for approver log
1582 null
1583 );
1584 END IF;
1585
1586 IF (isdifferent(l_new_attribute_category,l_old_attribute_category) = 1) THEN
1587 SELECT wip_eam_work_req_notes_s.nextval
1588 INTO l_work_request_note_id
1589 FROM DUAL;
1590 FND_MESSAGE.SET_NAME ('WIP', 'WIP_EAM_CONTEXT_VALUE_PROMPT');
1591 INSERT INTO wip_eam_work_req_notes(
1592 work_request_note_id,
1593 last_update_date,
1594 last_updated_by,
1595 creation_date,
1596 created_by,
1597 last_update_login,
1598 work_request_id,
1599 notes,
1600 work_request_note_type,
1601 notification_id
1602 )
1603 VALUES(
1604 l_work_request_note_id,
1605 sysdate,
1606 NVL(p_user_id,FND_GLOBAL.user_id),
1607 sysdate,
1608 NVL(p_user_id,FND_GLOBAL.user_id),
1609 NVL(p_user_id,FND_GLOBAL.login_id),
1610 p_request_id,
1611 '@@@ '||FND_MESSAGE.GET||' : '||nvl(l_old_attribute_category,l_null)||' -> '||nvl(l_new_attribute_category,l_null),
1612 1, -- 1 for request log, 2 for approver log
1613 null
1614 );
1615 END IF;
1616
1617 IF (isdifferent(l_new_attribute1,l_old_attribute1) = 1) THEN
1618 l_dffprompt := NULL;
1619 l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE1',l_new_attribute_category);
1620 IF (l_dffprompt IS NOT NULL) THEN
1621 l_dff_notes := get_dff_notes(l_new_attribute1,l_old_attribute1,l_dffprompt,l_null);
1622 SELECT wip_eam_work_req_notes_s.nextval
1623 INTO l_work_request_note_id
1624 FROM DUAL;
1625 INSERT INTO wip_eam_work_req_notes(
1626 work_request_note_id,
1627 last_update_date,
1628 last_updated_by,
1629 creation_date,
1630 created_by,
1631 last_update_login,
1632 work_request_id,
1633 notes,
1634 work_request_note_type,
1635 notification_id
1636 )
1637 VALUES(
1638 l_work_request_note_id,
1639 sysdate,
1640 NVL(p_user_id,FND_GLOBAL.user_id),
1641 sysdate,
1642 NVL(p_user_id,FND_GLOBAL.user_id),
1643 NVL(p_user_id,FND_GLOBAL.login_id),
1644 p_request_id,
1645 l_dff_notes,
1646 1, -- 1 for request log, 2 for approver log
1647 null
1648 );
1649 END IF;
1650 END IF;
1651
1652 IF (isdifferent(l_new_attribute2,l_old_attribute2) = 1) THEN
1653 l_dffprompt := NULL;
1654 l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE2',l_new_attribute_category);
1655 IF (l_dffprompt IS NOT NULL) THEN
1656 l_dff_notes := get_dff_notes(l_new_attribute2,l_old_attribute2,l_dffprompt,l_null);
1657 SELECT wip_eam_work_req_notes_s.nextval
1658 INTO l_work_request_note_id
1659 FROM DUAL;
1660 INSERT INTO wip_eam_work_req_notes(
1661 work_request_note_id,
1662 last_update_date,
1663 last_updated_by,
1664 creation_date,
1665 created_by,
1666 last_update_login,
1667 work_request_id,
1668 notes,
1669 work_request_note_type,
1670 notification_id
1671 )
1672 VALUES(
1673 l_work_request_note_id,
1674 sysdate,
1675 NVL(p_user_id,FND_GLOBAL.user_id),
1676 sysdate,
1677 NVL(p_user_id,FND_GLOBAL.user_id),
1678 NVL(p_user_id,FND_GLOBAL.login_id),
1679 p_request_id,
1680 l_dff_notes,
1681 1, -- 1 for request log, 2 for approver log
1682 null
1683 );
1684 END IF;
1685 END IF;
1686
1687 IF (isdifferent(l_new_attribute3,l_old_attribute3) = 1) THEN
1688 l_dffprompt := NULL;
1689 l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE3',l_new_attribute_category);
1690 IF (l_dffprompt IS NOT NULL) THEN
1691 l_dff_notes := get_dff_notes(l_new_attribute3,l_old_attribute3,l_dffprompt,l_null);
1692 SELECT wip_eam_work_req_notes_s.nextval
1693 INTO l_work_request_note_id
1694 FROM DUAL;
1695 INSERT INTO wip_eam_work_req_notes(
1696 work_request_note_id,
1697 last_update_date,
1698 last_updated_by,
1699 creation_date,
1700 created_by,
1701 last_update_login,
1702 work_request_id,
1703 notes,
1704 work_request_note_type,
1705 notification_id
1706 )
1707 VALUES(
1708 l_work_request_note_id,
1709 sysdate,
1710 NVL(p_user_id,FND_GLOBAL.user_id),
1711 sysdate,
1712 NVL(p_user_id,FND_GLOBAL.user_id),
1713 NVL(p_user_id,FND_GLOBAL.login_id),
1714 p_request_id,
1715 l_dff_notes,
1716 1, -- 1 for request log, 2 for approver log
1717 null
1718 );
1719 END IF;
1720 END IF;
1721
1722 IF (isdifferent(l_new_attribute3,l_old_attribute3) = 1) THEN
1723 l_dffprompt := NULL;
1724 l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE3',l_new_attribute_category);
1725 IF (l_dffprompt IS NOT NULL) THEN
1726 l_dff_notes := get_dff_notes(l_new_attribute3,l_old_attribute3,l_dffprompt,l_null);
1727 SELECT wip_eam_work_req_notes_s.nextval
1728 INTO l_work_request_note_id
1729 FROM DUAL;
1730 INSERT INTO wip_eam_work_req_notes(
1731 work_request_note_id,
1732 last_update_date,
1733 last_updated_by,
1734 creation_date,
1735 created_by,
1736 last_update_login,
1737 work_request_id,
1738 notes,
1739 work_request_note_type,
1740 notification_id
1741 )
1742 VALUES(
1743 l_work_request_note_id,
1744 sysdate,
1745 NVL(p_user_id,FND_GLOBAL.user_id),
1746 sysdate,
1747 NVL(p_user_id,FND_GLOBAL.user_id),
1748 NVL(p_user_id,FND_GLOBAL.login_id),
1749 p_request_id,
1750 l_dff_notes,
1751 1, -- 1 for request log, 2 for approver log
1752 null
1753 );
1754 END IF;
1755 END IF;
1756
1757 IF (isdifferent(l_new_attribute4,l_old_attribute4) = 1) THEN
1758 l_dffprompt := NULL;
1759 l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE4',l_new_attribute_category);
1760 IF (l_dffprompt IS NOT NULL) THEN
1761 l_dff_notes := get_dff_notes(l_new_attribute4,l_old_attribute4,l_dffprompt,l_null);
1762 SELECT wip_eam_work_req_notes_s.nextval
1763 INTO l_work_request_note_id
1764 FROM DUAL;
1765 INSERT INTO wip_eam_work_req_notes(
1766 work_request_note_id,
1767 last_update_date,
1768 last_updated_by,
1769 creation_date,
1770 created_by,
1771 last_update_login,
1772 work_request_id,
1773 notes,
1774 work_request_note_type,
1775 notification_id
1776 )
1777 VALUES(
1778 l_work_request_note_id,
1779 sysdate,
1780 NVL(p_user_id,FND_GLOBAL.user_id),
1781 sysdate,
1782 NVL(p_user_id,FND_GLOBAL.user_id),
1783 NVL(p_user_id,FND_GLOBAL.login_id),
1784 p_request_id,
1785 l_dff_notes,
1786 1, -- 1 for request log, 2 for approver log
1787 null
1788 );
1789 END IF;
1790 END IF;
1791
1792 IF (isdifferent(l_new_attribute5,l_old_attribute5) = 1) THEN
1793 l_dffprompt := NULL;
1794 l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE5',l_new_attribute_category);
1795 IF (l_dffprompt IS NOT NULL) THEN
1796 l_dff_notes := get_dff_notes(l_new_attribute5,l_old_attribute5,l_dffprompt,l_null);
1797 SELECT wip_eam_work_req_notes_s.nextval
1798 INTO l_work_request_note_id
1799 FROM DUAL;
1800 INSERT INTO wip_eam_work_req_notes(
1801 work_request_note_id,
1802 last_update_date,
1803 last_updated_by,
1804 creation_date,
1805 created_by,
1806 last_update_login,
1807 work_request_id,
1808 notes,
1809 work_request_note_type,
1810 notification_id
1811 )
1812 VALUES(
1813 l_work_request_note_id,
1814 sysdate,
1815 NVL(p_user_id,FND_GLOBAL.user_id),
1816 sysdate,
1817 NVL(p_user_id,FND_GLOBAL.user_id),
1818 NVL(p_user_id,FND_GLOBAL.login_id),
1819 p_request_id,
1820 l_dff_notes,
1821 1, -- 1 for request log, 2 for approver log
1822 null
1823 );
1824 END IF;
1825 END IF;
1826
1827 IF (isdifferent(l_new_attribute6,l_old_attribute6) = 1) THEN
1828 l_dffprompt := NULL;
1829 l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE6',l_new_attribute_category);
1830 IF (l_dffprompt IS NOT NULL) THEN
1831 l_dff_notes := get_dff_notes(l_new_attribute6,l_old_attribute6,l_dffprompt,l_null);
1832 SELECT wip_eam_work_req_notes_s.nextval
1833 INTO l_work_request_note_id
1834 FROM DUAL;
1835 INSERT INTO wip_eam_work_req_notes(
1836 work_request_note_id,
1837 last_update_date,
1838 last_updated_by,
1839 creation_date,
1840 created_by,
1841 last_update_login,
1842 work_request_id,
1843 notes,
1844 work_request_note_type,
1845 notification_id
1846 )
1847 VALUES(
1848 l_work_request_note_id,
1849 sysdate,
1850 NVL(p_user_id,FND_GLOBAL.user_id),
1851 sysdate,
1852 NVL(p_user_id,FND_GLOBAL.user_id),
1853 NVL(p_user_id,FND_GLOBAL.login_id),
1854 p_request_id,
1855 l_dff_notes,
1856 1, -- 1 for request log, 2 for approver log
1857 null
1858 );
1859 END IF;
1860 END IF;
1861
1862 IF (isdifferent(l_new_attribute7,l_old_attribute7) = 1) THEN
1863 l_dffprompt := NULL;
1864 l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE7',l_new_attribute_category);
1865 IF (l_dffprompt IS NOT NULL) THEN
1866 l_dff_notes := get_dff_notes(l_new_attribute7,l_old_attribute7,l_dffprompt,l_null);
1867 SELECT wip_eam_work_req_notes_s.nextval
1868 INTO l_work_request_note_id
1869 FROM DUAL;
1870 INSERT INTO wip_eam_work_req_notes(
1871 work_request_note_id,
1872 last_update_date,
1873 last_updated_by,
1874 creation_date,
1875 created_by,
1876 last_update_login,
1877 work_request_id,
1878 notes,
1879 work_request_note_type,
1880 notification_id
1881 )
1882 VALUES(
1883 l_work_request_note_id,
1884 sysdate,
1885 NVL(p_user_id,FND_GLOBAL.user_id),
1886 sysdate,
1887 NVL(p_user_id,FND_GLOBAL.user_id),
1888 NVL(p_user_id,FND_GLOBAL.login_id),
1889 p_request_id,
1890 l_dff_notes,
1891 1, -- 1 for request log, 2 for approver log
1892 null
1893 );
1894 END IF;
1895 END IF;
1896
1897 IF (isdifferent(l_new_attribute8,l_old_attribute8) = 1) THEN
1898 l_dffprompt := NULL;
1899 l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE8',l_new_attribute_category);
1900 IF (l_dffprompt IS NOT NULL) THEN
1901 l_dff_notes := get_dff_notes(l_new_attribute8,l_old_attribute8,l_dffprompt,l_null);
1902 SELECT wip_eam_work_req_notes_s.nextval
1903 INTO l_work_request_note_id
1904 FROM DUAL;
1905 INSERT INTO wip_eam_work_req_notes(
1906 work_request_note_id,
1907 last_update_date,
1908 last_updated_by,
1909 creation_date,
1910 created_by,
1911 last_update_login,
1912 work_request_id,
1913 notes,
1914 work_request_note_type,
1915 notification_id
1916 )
1917 VALUES(
1918 l_work_request_note_id,
1919 sysdate,
1920 NVL(p_user_id,FND_GLOBAL.user_id),
1921 sysdate,
1922 NVL(p_user_id,FND_GLOBAL.user_id),
1923 NVL(p_user_id,FND_GLOBAL.login_id),
1924 p_request_id,
1925 l_dff_notes,
1926 1, -- 1 for request log, 2 for approver log
1927 null
1928 );
1929 END IF;
1930 END IF;
1931
1932 IF (isdifferent(l_new_attribute9,l_old_attribute9) = 1) THEN
1933 l_dffprompt := NULL;
1934 l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE9',l_new_attribute_category);
1935 IF (l_dffprompt IS NOT NULL) THEN
1936 l_dff_notes := get_dff_notes(l_new_attribute9,l_old_attribute9,l_dffprompt,l_null);
1937 SELECT wip_eam_work_req_notes_s.nextval
1938 INTO l_work_request_note_id
1939 FROM DUAL;
1940 INSERT INTO wip_eam_work_req_notes(
1941 work_request_note_id,
1942 last_update_date,
1943 last_updated_by,
1944 creation_date,
1945 created_by,
1946 last_update_login,
1947 work_request_id,
1948 notes,
1949 work_request_note_type,
1950 notification_id
1951 )
1952 VALUES(
1953 l_work_request_note_id,
1954 sysdate,
1955 NVL(p_user_id,FND_GLOBAL.user_id),
1956 sysdate,
1957 NVL(p_user_id,FND_GLOBAL.user_id),
1958 NVL(p_user_id,FND_GLOBAL.login_id),
1959 p_request_id,
1960 l_dff_notes,
1961 1, -- 1 for request log, 2 for approver log
1962 null
1963 );
1964 END IF;
1965 END IF;
1966
1967 IF (isdifferent(l_new_attribute10,l_old_attribute10) = 1) THEN
1968 l_dffprompt := NULL;
1969 l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE10',l_new_attribute_category);
1970 IF (l_dffprompt IS NOT NULL) THEN
1971 l_dff_notes := get_dff_notes(l_new_attribute10,l_old_attribute10,l_dffprompt,l_null);
1972 SELECT wip_eam_work_req_notes_s.nextval
1973 INTO l_work_request_note_id
1974 FROM DUAL;
1975 INSERT INTO wip_eam_work_req_notes(
1976 work_request_note_id,
1977 last_update_date,
1978 last_updated_by,
1979 creation_date,
1980 created_by,
1981 last_update_login,
1982 work_request_id,
1983 notes,
1984 work_request_note_type,
1985 notification_id
1986 )
1987 VALUES(
1988 l_work_request_note_id,
1989 sysdate,
1990 NVL(p_user_id,FND_GLOBAL.user_id),
1991 sysdate,
1992 NVL(p_user_id,FND_GLOBAL.user_id),
1993 NVL(p_user_id,FND_GLOBAL.login_id),
1994 p_request_id,
1995 l_dff_notes,
1996 1, -- 1 for request log, 2 for approver log
1997 null
1998 );
1999 END IF;
2000 END IF;
2001
2002 IF (isdifferent(l_new_attribute11,l_old_attribute11) = 1) THEN
2003 l_dffprompt := NULL;
2004 l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE11',l_new_attribute_category);
2005 IF (l_dffprompt IS NOT NULL) THEN
2006 l_dff_notes := get_dff_notes(l_new_attribute11,l_old_attribute11,l_dffprompt,l_null);
2007 SELECT wip_eam_work_req_notes_s.nextval
2008 INTO l_work_request_note_id
2009 FROM DUAL;
2010 INSERT INTO wip_eam_work_req_notes(
2011 work_request_note_id,
2012 last_update_date,
2013 last_updated_by,
2014 creation_date,
2015 created_by,
2016 last_update_login,
2017 work_request_id,
2018 notes,
2019 work_request_note_type,
2020 notification_id
2021 )
2022 VALUES(
2023 l_work_request_note_id,
2024 sysdate,
2025 NVL(p_user_id,FND_GLOBAL.user_id),
2026 sysdate,
2027 NVL(p_user_id,FND_GLOBAL.user_id),
2028 NVL(p_user_id,FND_GLOBAL.login_id),
2029 p_request_id,
2030 l_dff_notes,
2031 1, -- 1 for request log, 2 for approver log
2032 null
2033 );
2034 END IF;
2035 END IF;
2036
2037 IF (isdifferent(l_new_attribute12,l_old_attribute12) = 1) THEN
2038 l_dffprompt := NULL;
2039 l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE12',l_new_attribute_category);
2040 IF (l_dffprompt IS NOT NULL) THEN
2041 l_dff_notes := get_dff_notes(l_new_attribute12,l_old_attribute12,l_dffprompt,l_null);
2042 SELECT wip_eam_work_req_notes_s.nextval
2043 INTO l_work_request_note_id
2044 FROM DUAL;
2045 INSERT INTO wip_eam_work_req_notes(
2046 work_request_note_id,
2047 last_update_date,
2048 last_updated_by,
2049 creation_date,
2050 created_by,
2051 last_update_login,
2052 work_request_id,
2053 notes,
2054 work_request_note_type,
2055 notification_id
2056 )
2057 VALUES(
2058 l_work_request_note_id,
2059 sysdate,
2060 NVL(p_user_id,FND_GLOBAL.user_id),
2061 sysdate,
2062 NVL(p_user_id,FND_GLOBAL.user_id),
2063 NVL(p_user_id,FND_GLOBAL.login_id),
2064 p_request_id,
2065 l_dff_notes,
2066 1, -- 1 for request log, 2 for approver log
2067 null
2068 );
2069 END IF;
2070 END IF;
2071
2072 IF (isdifferent(l_new_attribute13,l_old_attribute13) = 1) THEN
2073 l_dffprompt := NULL;
2074 l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE13',l_new_attribute_category);
2075 IF (l_dffprompt IS NOT NULL) THEN
2076 l_dff_notes := get_dff_notes(l_new_attribute13,l_old_attribute13,l_dffprompt,l_null);
2077 SELECT wip_eam_work_req_notes_s.nextval
2078 INTO l_work_request_note_id
2079 FROM DUAL;
2080 INSERT INTO wip_eam_work_req_notes(
2081 work_request_note_id,
2082 last_update_date,
2083 last_updated_by,
2084 creation_date,
2085 created_by,
2086 last_update_login,
2087 work_request_id,
2088 notes,
2089 work_request_note_type,
2090 notification_id
2091 )
2092 VALUES(
2093 l_work_request_note_id,
2094 sysdate,
2095 NVL(p_user_id,FND_GLOBAL.user_id),
2096 sysdate,
2097 NVL(p_user_id,FND_GLOBAL.user_id),
2098 NVL(p_user_id,FND_GLOBAL.login_id),
2099 p_request_id,
2100 l_dff_notes,
2101 1, -- 1 for request log, 2 for approver log
2102 null
2103 );
2104 END IF;
2105 END IF;
2106
2107 IF (isdifferent(l_new_attribute14,l_old_attribute14) = 1) THEN
2108 l_dffprompt := NULL;
2109 l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE14',l_new_attribute_category);
2110 IF (l_dffprompt IS NOT NULL) THEN
2111 l_dff_notes := get_dff_notes(l_new_attribute14,l_old_attribute14,l_dffprompt,l_null);
2112 SELECT wip_eam_work_req_notes_s.nextval
2113 INTO l_work_request_note_id
2114 FROM DUAL;
2115 INSERT INTO wip_eam_work_req_notes(
2116 work_request_note_id,
2117 last_update_date,
2118 last_updated_by,
2119 creation_date,
2120 created_by,
2121 last_update_login,
2122 work_request_id,
2123 notes,
2124 work_request_note_type,
2125 notification_id
2126 )
2127 VALUES(
2128 l_work_request_note_id,
2129 sysdate,
2130 NVL(p_user_id,FND_GLOBAL.user_id),
2131 sysdate,
2132 NVL(p_user_id,FND_GLOBAL.user_id),
2133 NVL(p_user_id,FND_GLOBAL.login_id),
2134 p_request_id,
2135 l_dff_notes,
2136 1, -- 1 for request log, 2 for approver log
2137 null
2138 );
2139 END IF;
2140 END IF;
2141
2142 IF (isdifferent(l_new_attribute15,l_old_attribute15) = 1) THEN
2143 l_dffprompt := NULL;
2144 l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE15',l_new_attribute_category);
2145 IF (l_dffprompt IS NOT NULL) THEN
2146 l_dff_notes := get_dff_notes(l_new_attribute15,l_old_attribute15,l_dffprompt,l_null);
2147 SELECT wip_eam_work_req_notes_s.nextval
2148 INTO l_work_request_note_id
2149 FROM DUAL;
2150 INSERT INTO wip_eam_work_req_notes(
2151 work_request_note_id,
2152 last_update_date,
2153 last_updated_by,
2154 creation_date,
2155 created_by,
2156 last_update_login,
2157 work_request_id,
2158 notes,
2159 work_request_note_type,
2160 notification_id
2161 )
2162 VALUES(
2163 l_work_request_note_id,
2164 sysdate,
2165 NVL(p_user_id,FND_GLOBAL.user_id),
2166 sysdate,
2167 NVL(p_user_id,FND_GLOBAL.user_id),
2168 NVL(p_user_id,FND_GLOBAL.login_id),
2169 p_request_id,
2170 l_dff_notes,
2171 1, -- 1 for request log, 2 for approver log
2172 null
2173 );
2174 END IF;
2175 END IF;
2176
2177 END IF; /* End extended log IF LOOP */
2178 IF p_request_log is not null THEN
2179 l_stmt_num := 290;
2180 -- check if description field is null for this work request id
2181 BEGIN
2182 SELECT count(*)
2183 INTO l_counter
2184 FROM wip_eam_work_requests
2185 WHERE work_request_id = p_request_id And Description Is Null;
2186 EXCEPTION
2187 WHEN OTHERS THEN
2188 l_counter := 0;
2189 END;
2190 IF l_counter <> 0 then
2191 l_stmt_num := 300;
2192 UPDATE wip_eam_work_requests
2193 SET description = p_request_log
2194 WHERE work_request_id = p_request_id;
2195 END IF;
2196 -- end check
2197 l_stmt_num := 310;
2198 SELECT wip_eam_work_req_notes_s.nextval
2199 INTO l_work_request_note_id
2200 FROM dual;
2201 l_stmt_num := 320;
2202 INSERT INTO wip_eam_work_req_notes(
2203 work_request_note_id,
2204 last_update_date,
2205 last_updated_by,
2206 creation_date,
2207 created_by,
2208 last_update_login,
2209 work_request_id,
2210 notes,
2211 work_request_note_type,
2212 notification_id
2213 )
2214 VALUES(
2215 l_work_request_note_id,
2216 sysdate,
2217 NVL(p_user_id,FND_GLOBAL.user_id),
2218 sysdate,
2219 NVL(p_user_id,FND_GLOBAL.user_id),
2220 NVL(p_user_id,FND_GLOBAL.login_id),
2221 p_request_id,
2222 p_request_log,
2223 1, -- 1 for request log, 2 for approver log
2224 null
2225 );
2226 --else
2227 -- l_request_log := chr(10) || l_another_log;
2228 END IF;
2229 l_stmt_num := 998;
2230
2231 /* bug 10399281 */
2232 IF (l_old_owning_dept_id <> l_new_owning_dept_id AND l_work_request_auto_approve = 'N' AND (l_new_status_id = 1 OR l_new_status_id = 2 OR l_new_status_id = 3)) THEN
2233
2234 -- get the old notification details
2235 begin
2236 select decode(wf_item_type,null,'EAMWRAP',wf_item_type),decode(wf_item_key,null,work_request_id,wf_item_key)
2237 into l_old_wf_item_type,l_old_wf_item_key
2238 from wip_eam_work_requests
2239 where work_request_id = p_request_id;
2240
2241 exception
2242 when others then
2243 raise WF_ERROR;
2244 end;
2245
2246 -- if Status is Open or Addn. Info then remove prev. notification
2247 if (l_new_status_id = 1 OR l_new_status_id = 2) then
2248 -- remove notification for old department
2249 wf_engine.AbortProcess(itemtype => l_old_wf_item_type,
2250 itemkey => l_old_wf_item_key);
2251 end if;
2252
2253 -- since dept has been updated, generate a new notification
2254 -- for the new department
2255
2256 wip_eam_wrapproval_pvt.StartWRAProcess (
2257 p_work_request_id => p_request_id,
2258 p_asset_number => l_new_asset_number,
2259 p_asset_group => l_new_asset_group_id,
2260 p_asset_location => null,
2261 p_organization_id => p_org_id,
2262 p_work_request_status_id => l_new_status_id,
2263 p_work_request_priority_id =>l_new_priority_id,
2264 p_work_request_owning_dept_id => l_new_owning_dept_id,
2265 p_expected_resolution_date => l_new_request_by_date,
2266 p_work_request_type_id => l_new_work_request_type_id,
2267 p_notes => p_request_log,
2268 p_notify_originator => l_new_notify_originator,
2269 p_resultout => l_results_out,
2270 p_error_message => l_error_message
2271 ) ;
2272
2273 IF (l_results_out <> FND_API.G_RET_STS_SUCCESS) THEN
2274 FND_MESSAGE.SET_NAME ('EAM','EAM_WR_ERROR');
2275 FND_MSG_PUB.ADD;
2276 RAISE FND_API.G_EXC_ERROR;
2277 -- ELSE for bug # 13355580
2278 -- commit ; for bug # 13355580
2279 END IF ;
2280
2281 -- if status is Addn Info OR Awaiting Work Order then change status back to Open
2282 if (l_new_status_id = 3 Or l_new_status_id = 2) then
2283 --set Work Request status back to Open
2284 update wip_eam_work_requests
2285 set work_request_status_id = 1
2286 where work_request_id = p_request_id;
2287 end if;
2288
2289 END IF;
2290
2291 -- End of API body.
2292 -- Standard check of p_commit.
2293 IF fnd_api.to_boolean(p_commit) THEN
2294 COMMIT WORK;
2295 END IF;
2296
2297 l_stmt_num := 999;
2298 -- Standard call to get message count and if count is 1, get message info.
2299 fnd_msg_pub.count_and_get(
2300 p_encoded => fnd_api.g_false
2301 ,p_count => x_msg_count
2302 ,p_data => x_msg_data);
2303
2304 EXCEPTION
2305 When WF_ERROR then
2306 ROLLBACK TO update_work_request_pvt;
2307 x_return_status := fnd_api.g_ret_sts_error;
2308 fnd_msg_pub.count_and_get(
2309 p_encoded => fnd_api.g_false
2310 ,p_count => x_msg_count
2311 ,p_data => x_msg_data);
2312 WHEN fnd_api.g_exc_error THEN
2313 ROLLBACK TO update_work_request_pvt;
2314 x_return_status := fnd_api.g_ret_sts_error;
2315 fnd_msg_pub.count_and_get(
2316 p_encoded => fnd_api.g_false
2317 ,p_count => x_msg_count
2318 ,p_data => x_msg_data);
2319 WHEN fnd_api.g_exc_unexpected_error THEN
2320 ROLLBACK TO update_work_request_pvt;
2321 x_return_status := fnd_api.g_ret_sts_unexp_error;
2322 fnd_msg_pub.count_and_get(
2323 p_encoded => fnd_api.g_false
2324 ,p_count => x_msg_count
2325 ,p_data => x_msg_data);
2326 WHEN OTHERS THEN
2327 ROLLBACK TO update_work_request_pvt;
2328 x_return_status := fnd_api.g_ret_sts_unexp_error;
2329 IF fnd_msg_pub.check_msg_level(
2330 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2331 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name||'('||l_stmt_num||')');
2332 END IF;
2333 fnd_msg_pub.count_and_get(
2334 p_encoded => fnd_api.g_false
2335 ,p_count => x_msg_count
2336 ,p_data => x_msg_data);
2337 END update_work_request;
2338
2339 procedure return_dept_id (
2340 p_org_id IN NUMBER,
2341 p_dept_name IN VARCHAR2,
2342 x_dept_id OUT NOCOPY NUMBER,
2343 x_return_status out NOCOPY VARCHAR2,
2344 x_msg_count out NOCOPY NUMBER,
2345 x_msg_data out NOCOPY VARCHAR2
2346 ) is
2347 BEGIN
2348 SELECT department_id INTO x_dept_id
2349 FROM BOM_DEPARTMENTS
2350 WHERE DEPARTMENT_CODE = p_dept_name
2351 And Organization_Id = p_org_id;
2352 EXCEPTION
2353 When others then
2354 x_dept_id := NULL;
2355 END;
2356
2357 function validate_department(p_organization_id in number,
2358 p_department_id in number) return boolean
2359 is
2360 l_count number := 0;
2361 begin
2362 begin
2363 SELECT count(*) INTO l_count
2364 FROM BOM_DEPARTMENTS BD
2365 WHERE BD.ORGANIZATION_ID = p_organization_id
2366 AND BD.DEPARTMENT_ID = p_department_id
2367 AND nvl(BD.DISABLE_DATE,sysdate+1) > sysdate;
2368 exception
2369 when others then
2370 return FALSE;
2371 end;
2372
2373 IF l_count = 0 THEN
2374 return FALSE;
2375 ELSE
2376 return TRUE;
2377 END IF;
2378
2379 end validate_department;
2380
2381 function validate_lookup(p_lookup_code in number,
2382 p_lookup_type in varchar2) return boolean
2383 is
2384 l_count number;
2385 begin
2386 begin
2387 select count(*) into l_count
2388 from mfg_lookups
2389 where lookup_code = p_lookup_code
2390 and lookup_type = p_lookup_type
2391 and enabled_flag = 'Y'
2392 and sysdate between nvl(start_date_active,sysdate-1)
2393 and nvl(end_date_active,sysdate+1);
2394 exception
2395 when others then
2396 return FALSE;
2397 end;
2398
2399 IF l_count = 0 THEN
2400 return FALSE;
2401 ELSE
2402 return TRUE;
2403 END IF;
2404
2405 end validate_lookup;
2406
2407 function validate_user_id(p_user_id in number) return boolean is
2408 l_count number;
2409
2410 begin
2411 select count(*)
2412 into l_count
2413 from fnd_user
2414 where user_id = p_user_id
2415 and sysdate between nvl(start_date,sysdate-1) AND nvl(end_date,sysdate+1);
2416
2417 IF l_count = 0 THEN
2418 return false;
2419 else return true;
2420 END IF;
2421
2422 exception
2423 when others then
2424 return false;
2425
2426 end validate_user_id;
2427
2428 -- Bug # 3553217.
2429 -- To raise error if new value is different from old value
2430 -- validate_for_num_change for datatype NUMBER
2431 -- validate_for_char_change for datatype VARCHAR2
2432 -- validate_for_date_change for datatype DATE
2433
2434 PROCEDURE validate_for_num_change (p_old_value IN NUMBER, p_new_value IN NUMBER,
2435 p_msg IN VARCHAR2, p_attr IN VARCHAR2,
2436 x_return_flag in out NOCOPY BOOLEAN) IS
2437 result NUMBER;
2438 BEGIN
2439 result := 1;
2440 IF (p_new_value is not null) THEN
2441 IF (p_new_value = fnd_api.g_miss_num) THEN
2442 IF (p_old_value is not null) THEN
2443 result := 0;
2444 END IF;
2445 ELSE
2446 IF NOT(p_new_value = p_old_value) THEN
2447 result := 0;
2448 END IF;
2449 END IF;
2450 END IF;
2451 IF (result = 0) THEN
2452 FND_MESSAGE.SET_NAME('EAM', p_msg);
2453 IF p_attr is not null then
2454 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', p_attr || to_char(p_new_value));
2455 END IF;
2456 FND_MSG_PUB.ADD;
2457 x_return_flag := FALSE;
2458 RAISE FND_API.G_EXC_ERROR;
2459 END IF;
2460 END validate_for_num_change;
2461
2462 PROCEDURE validate_for_char_change (p_old_value IN VARCHAR2, p_new_value IN VARCHAR2,
2463 p_msg IN VARCHAR2, p_attr IN VARCHAR2,
2464 x_return_flag in out NOCOPY BOOLEAN) IS
2465 result NUMBER;
2466 BEGIN
2467 result := 1;
2468 IF (p_new_value is not null) THEN
2469 IF (p_new_value = fnd_api.g_miss_char) THEN
2470 IF (p_old_value is not null) THEN
2471 result := 0;
2472 END IF;
2473 ELSE
2474 IF NOT(p_new_value = p_old_value) THEN
2475 result := 0;
2476 END IF;
2477 END IF;
2478 END IF;
2479 IF (result = 0) THEN
2480 FND_MESSAGE.SET_NAME('EAM', p_msg);
2481 IF p_attr is not null then
2482 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', p_attr || p_new_value);
2483 END IF;
2484 FND_MSG_PUB.ADD;
2485 x_return_flag := FALSE;
2486 RAISE FND_API.G_EXC_ERROR;
2487 END IF;
2488 END validate_for_char_change;
2489
2490 PROCEDURE validate_for_date_change (p_old_value IN DATE, p_new_value IN DATE,
2491 p_msg IN VARCHAR2, p_attr IN VARCHAR2,
2492 x_return_flag IN OUT NOCOPY BOOLEAN) IS
2493 result NUMBER;
2494 BEGIN
2495 result := 1;
2496 IF (p_new_value is not null) THEN
2497 IF (p_new_value = fnd_api.g_miss_date) THEN
2498 IF (p_old_value is not null) THEN
2499 result := 0;
2500 END IF;
2501 ELSE
2502 IF NOT(p_new_value = p_old_value) THEN
2503 result := 0;
2504 END IF;
2505 END IF;
2506 END IF;
2507 IF (result = 0) THEN
2508 FND_MESSAGE.SET_NAME('EAM', p_msg);
2509 IF p_attr is not null then
2510 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', p_attr || to_char(p_new_value, 'YYYY-MON-DD'));
2511 END IF;
2512 FND_MSG_PUB.ADD;
2513 x_return_flag := FALSE;
2514 RAISE FND_API.G_EXC_ERROR;
2515 END IF;
2516 END validate_for_date_change;
2517
2518 procedure validate_work_request (
2519 p_api_version in NUMBER,
2520 p_init_msg_list in VARCHAR2:= FND_API.G_FALSE,
2521 p_mode in VARCHAR2,
2522 p_org_id in NUMBER,
2523 p_request_id in NUMBER,
2524 p_asset_group_id in NUMBER,
2525 p_asset_number in VARCHAR2,
2526 p_priority_id in NUMBER,
2527 p_status_id in NUMBER,
2528 p_request_by_date in DATE,
2529 p_request_log in VARCHAR2,
2530 p_owning_dept_id in NUMBER,
2531 p_user_id in NUMBER,
2532 p_work_request_type_id in NUMBER,
2533 p_maintenance_object_type IN NUMBER,
2534 p_maintenance_object_id IN NUMBER,
2535 p_eam_linear_id in NUMBER default null,
2536 p_attribute_category in VARCHAR2 default null,
2537 p_attribute1 IN VARCHAR2 default null,
2538 p_attribute2 IN VARCHAR2 default null,
2539 p_attribute3 IN VARCHAR2 default null,
2540 p_attribute4 IN VARCHAR2 default null,
2541 p_attribute5 IN VARCHAR2 default null,
2542 p_attribute6 IN VARCHAR2 default null,
2543 p_attribute7 IN VARCHAR2 default null,
2544 p_attribute8 IN VARCHAR2 default null,
2545 p_attribute9 IN VARCHAR2 default null,
2546 p_attribute10 IN VARCHAR2 default null,
2547 p_attribute11 IN VARCHAR2 default null,
2548 p_attribute12 IN VARCHAR2 default null,
2549 p_attribute13 IN VARCHAR2 default null,
2550 p_attribute14 IN VARCHAR2 default null,
2551 p_attribute15 IN VARCHAR2 default null,
2552 p_created_for IN NUMBER default null,
2553 p_phone_number IN VARCHAR2 default null,
2554 p_email IN VARCHAR2 default null,
2555 p_contact_preference IN NUMBER default null,
2556 p_notify_originator IN NUMBER default null,
2557 x_return_flag out NOCOPY BOOLEAN,
2558 x_return_status out NOCOPY VARCHAR2,
2559 x_msg_count out NOCOPY NUMBER,
2560 x_msg_data out NOCOPY VARCHAR2
2561 ) is
2562
2563 l_api_name CONSTANT VARCHAR2(30) := 'validate_work_request';
2564 l_api_version CONSTANT NUMBER := 1.0;
2565 l_dummy_val NUMBER;
2566 l_stmt_num NUMBER;
2567 l_dummy_char VARCHAR2 (30);
2568 l_auto_approve VARCHAR2(1) := null;
2569 l_eam_item_type NUMBER := null;
2570 l_x_error_segments number;
2571 l_x_error_message varchar2(2000);
2572 l_org_id NUMBER;
2573 l_asset_group_id NUMBER;
2574 l_asset_number VARCHAR2(30);
2575 l_priority_id NUMBER;
2576 l_status_id NUMBER;
2577 l_request_by_date DATE;
2578 l_owning_dept_id NUMBER;
2579 l_work_request_type_id NUMBER;
2580 l_created_for NUMBER ;
2581 l_validate BOOLEAN := TRUE;
2582 l_valid_user_id BOOLEAN := TRUE;
2583
2584 BEGIN
2585
2586 IF NOT fnd_api.compatible_api_call(
2587 l_api_version
2588 ,p_api_version
2589 ,l_api_name
2590 ,g_pkg_name) THEN
2591 RAISE fnd_api.g_exc_unexpected_error;
2592 END IF;
2593 l_stmt_num := 0;
2594
2595 -- Initialize message list if p_init_msg_list is set to TRUE.
2596 IF fnd_api.to_boolean(p_init_msg_list) THEN
2597 fnd_msg_pub.initialize;
2598 END IF;
2599
2600 -- Initialize API return status to success
2601 x_return_status := fnd_api.g_ret_sts_success;
2602
2603 l_stmt_num := 10;
2604
2605 -- Initialize API return flag to true
2606 x_return_flag := TRUE;
2607
2608
2609
2610 l_stmt_num := 20;
2611
2612 -- Added for Bug# 13466741
2613 -- check p_user_id field
2614 IF (p_user_id is not null) then
2615 l_valid_user_id := validate_user_id(p_user_id);
2616 end IF;
2617
2618 --If calling mode is CREATE API the validate the asset
2619 IF p_mode = 'CREATE' THEN
2620
2621
2622 l_stmt_num := 30;
2623
2624 IF p_org_id IS NULL THEN
2625 FND_MESSAGE.SET_NAME('WIP', 'WIP_ATTRIBUTE_REQUIRED');
2626 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'p_org_id');
2627 FND_MSG_PUB.ADD;
2628 x_return_flag := FALSE;
2629 RAISE FND_API.G_EXC_ERROR;
2630 END IF;
2631
2632 begin
2633 select nvl(work_request_auto_approve,'N')
2634 into l_auto_approve
2635 from wip_eam_parameters
2636 where organization_id = p_org_id;
2637 exception
2638 when others then
2639 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
2640 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Organization Id: ' || p_org_id);
2641 FND_MSG_PUB.ADD;
2642 x_return_flag := FALSE;
2643 RAISE FND_API.G_EXC_ERROR;
2644 end;
2645
2646 l_stmt_num := 40;
2647
2648
2649
2650 l_stmt_num := 50;
2651
2652 -- check that such an asset does indeed exist
2653
2654 IF (p_maintenance_object_id is not null) then
2655
2656 BEGIN
2657 -- check that the asset is maintainable
2658
2659 SELECT nvl(cii.maintainable_flag, 'Y'), cii.serial_number, msi.eam_item_type
2660 INTO l_dummy_char, l_asset_number,l_dummy_val
2661 FROM csi_item_instances cii, mtl_system_items msi
2662 WHERE cii.instance_id = p_maintenance_object_id AND
2663 cii.last_vld_organization_id = msi.organization_id AND
2664 cii.inventory_item_id = msi.inventory_item_id ;
2665
2666 EXCEPTION
2667 WHEN NO_DATA_FOUND THEN
2668 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
2669 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Asset Number: ' || l_asset_number || ', '|| 'Org Id: ' || p_org_id || ' and Group Id: ' || p_asset_group_id);
2670 FND_MSG_PUB.ADD;
2671 x_return_flag := FALSE;
2672 RAISE FND_API.G_EXC_ERROR;
2673 END;
2674
2675 IF (l_dummy_char <> 'Y' and (l_dummy_val = 1 OR l_dummy_val = 3)) THEN
2676 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_ASSET_NOT_MAINTAINABLE');
2677 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'l_asset_number');
2678 FND_MSG_PUB.ADD;
2679 x_return_flag := FALSE;
2680 RAISE FND_API.G_EXC_ERROR;
2681 END IF;
2682 else
2683
2684 IF (p_asset_number IS NOT NULL or p_asset_group_id is not null) THEN
2685
2686
2687 l_stmt_num := 60;
2688
2689 BEGIN
2690 -- check that the asset is maintainable
2691 -- Bug # 3553217.
2692 SELECT nvl(cii.maintainable_flag, 'Y'), eam_item_type
2693 INTO l_dummy_char, l_dummy_val
2694 FROM CSI_ITEM_INSTANCES CII, MTL_SYSTEM_ITEMS MSI
2695 WHERE cii.serial_number = p_asset_number AND
2696 cii.inventory_item_id = p_asset_group_id AND
2697 cii.last_vld_organization_id = msi.organization_id AND
2698 cii.inventory_item_id = msi.inventory_item_id ;
2699
2700 EXCEPTION
2701 WHEN NO_DATA_FOUND THEN
2702 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
2703 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Asset Number: ' || p_asset_number || ', '|| 'Org Id: ' || p_org_id || ' and Group Id: ' || p_asset_group_id);
2704 FND_MSG_PUB.ADD;
2705 x_return_flag := FALSE;
2706 RAISE FND_API.G_EXC_ERROR;
2707 END;
2708
2709 IF (l_dummy_char <> 'Y' and (l_dummy_val = 1 OR l_dummy_val = 3)) THEN
2710 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_ASSET_NOT_MAINTAINABLE');
2711 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'p_asset_number');
2712 FND_MSG_PUB.ADD;
2713 x_return_flag := FALSE;
2714 RAISE FND_API.G_EXC_ERROR;
2715 END IF;
2716
2717 END IF;
2718 END if;
2719
2720 l_stmt_num := 60;
2721
2722
2723 l_stmt_num := 70;
2724
2725
2726 -- check if the priority id is a valid entry
2727 -- currently the lookup_type is WIP_EAM_ACTIVITY_PRIORITY
2728 -- but will later change to WIP_EAM_JOB_REQ_PRIORITY
2729 IF p_priority_id IS NOT NULL THEN
2730 if validate_lookup(p_priority_id,'WIP_EAM_ACTIVITY_PRIORITY') = FALSE then
2731
2732 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
2733 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Priority Id: ' || to_char(p_priority_id));
2734 FND_MSG_PUB.ADD;
2735 x_return_flag := FALSE;
2736 RAISE FND_API.G_EXC_ERROR;
2737 end if;
2738 ELSE
2739 FND_MESSAGE.SET_NAME('WIP', 'WIP_ATTRIBUTE_REQUIRED');
2740 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'p_priority_id');
2741 FND_MSG_PUB.ADD;
2742 x_return_flag := FALSE;
2743 RAISE FND_API.G_EXC_ERROR;
2744 END IF;
2745
2746 l_stmt_num := 80;
2747
2748 IF p_request_by_date IS NOT NULL THEN
2749 IF p_request_by_date < sysdate THEN
2750 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
2751 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Request By Date: ' || p_request_by_date);
2752 FND_MSG_PUB.ADD;
2753 x_return_flag := FALSE;
2754 RAISE FND_API.G_EXC_ERROR;
2755 END IF;
2756 ELSE
2757 FND_MESSAGE.SET_NAME('WIP', 'WIP_ATTRIBUTE_REQUIRED');
2758 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'p_request_by_date');
2759 FND_MSG_PUB.ADD;
2760 x_return_flag := FALSE;
2761 RAISE FND_API.G_EXC_ERROR;
2762 END IF;
2763
2764 l_stmt_num := 90;
2765
2766 IF p_owning_dept_id IS NULL THEN
2767 if l_auto_approve is not null AND l_auto_approve ='N' then
2768 FND_MESSAGE.SET_NAME('WIP', 'WIP_ATTRIBUTE_REQUIRED');
2769 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'p_owning_dept_id');
2770 FND_MSG_PUB.ADD;
2771 x_return_flag := FALSE;
2772 RAISE FND_API.G_EXC_ERROR;
2773 end if;
2774 ELSE
2775 if validate_department(p_org_id,p_owning_dept_id) = FALSE then
2776
2777 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
2778 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Owning Dept. Id: ' || to_char(p_owning_dept_id));
2779 FND_MSG_PUB.ADD;
2780 x_return_flag := FALSE;
2781 RAISE FND_API.G_EXC_ERROR;
2782 end if;
2783 END IF;
2784
2785 l_stmt_num := 100;
2786
2787 IF p_work_request_type_id IS NOT NULL THEN
2788 if validate_lookup(p_work_request_type_id,'WIP_EAM_WORK_REQ_TYPE') = FALSE then
2789 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
2790 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Work Request Type Id: ' || to_char(p_work_request_type_id));
2791 FND_MSG_PUB.ADD;
2792 x_return_flag := FALSE;
2793 RAISE FND_API.G_EXC_ERROR;
2794 end if;
2795 END IF;
2796
2797 l_stmt_num := 110;
2798
2799 -- validate the eam linear id
2800
2801 IF (p_eam_linear_id IS NOT NULL) THEN
2802 l_validate := eam_common_utilities_pvt.validate_linear_id(p_eam_linear_id);
2803 IF (NOT l_validate) THEN
2804 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
2805 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'EAM Linear Location Id: ' || to_char(p_eam_linear_id));
2806 FND_MSG_PUB.ADD;
2807 x_return_flag := FALSE;
2808 RAISE FND_API.G_EXC_ERROR;
2809 END IF;
2810 END IF;
2811
2812 --validate descriptive flex fields
2813 if (validate_desc_flex_field
2814 (
2815 p_app_short_name => 'WIP'
2816 ,p_desc_flex_name => 'WIP_EAM_WORK_REQUESTS'
2817 ,p_ATTRIBUTE_CATEGORY => p_attribute_category
2818 ,p_ATTRIBUTE1 => p_attribute1
2819 ,p_ATTRIBUTE2 => p_attribute2
2820 ,p_ATTRIBUTE3 => p_attribute3
2821 ,p_ATTRIBUTE4 => p_attribute4
2822 ,p_ATTRIBUTE5 => p_attribute5
2823 ,p_ATTRIBUTE6 => p_attribute6
2824 ,p_ATTRIBUTE7 => p_attribute7
2825 ,p_ATTRIBUTE8 => p_attribute8
2826 ,p_ATTRIBUTE9 => p_attribute9
2827 ,p_ATTRIBUTE10 => p_attribute10
2828 ,p_ATTRIBUTE11 => p_attribute11
2829 ,p_ATTRIBUTE12 => p_attribute12
2830 ,p_ATTRIBUTE13 => p_attribute13
2831 ,p_ATTRIBUTE14 => p_attribute14
2832 ,p_ATTRIBUTE15 => p_attribute15
2833 ,x_error_segments => l_x_error_segments
2834 ,x_error_message => l_x_error_message
2835 )
2836 ) = FALSE then
2837 FND_MESSAGE.SET_NAME('WIP','WIP_INVALID_ATTRIBUTE');
2838 FND_MSG_PUB.ADD;
2839 RAISE FND_API.G_EXC_ERROR;
2840
2841 end if;
2842
2843 -- check user_id field
2844 if l_valid_user_id = FALSE then
2845 FND_MESSAGE.SET_NAME('WIP', 'WIP_ML_CREATED_BY');
2846 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Created By: ' || p_user_id);
2847 FND_MSG_PUB.ADD;
2848 x_return_flag := FALSE;
2849 RAISE FND_API.G_EXC_ERROR;
2850 end if;
2851
2852 -- check created_for field
2853 IF (p_created_for is not null) then
2854 if validate_user_id(p_created_for) = FALSE then
2855 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_CREATED_FOR');
2856 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Created For: ' || p_created_for);
2857 FND_MSG_PUB.ADD;
2858 x_return_flag := FALSE;
2859 RAISE FND_API.G_EXC_ERROR;
2860 END IF;
2861 end if;
2862
2863 -- check contact preference
2864 IF p_contact_preference is not null then
2865 if validate_lookup(p_contact_preference,'WIP_EAM_CONTACT_PREFERENCE') = FALSE then
2866 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
2867 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Contact Preference: ' || to_char(p_contact_preference));
2868 FND_MSG_PUB.ADD;
2869 x_return_flag := FALSE;
2870 RAISE FND_API.G_EXC_ERROR;
2871 end if;
2872
2873 end if;
2874
2875 -- check notify originator
2876 IF p_notify_originator is not null then
2877 if validate_lookup(p_notify_originator,'SYS_YES_NO') = FALSE then
2878 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
2879 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Notify Originator: ' || to_char(p_notify_originator));
2880 FND_MSG_PUB.ADD;
2881 x_return_flag := FALSE;
2882 RAISE FND_API.G_EXC_ERROR;
2883 end if;
2884
2885 end if;
2886
2887 END IF;
2888
2889 -- IF the calling mode is an update API, check if it is a valid work request
2890
2891 IF p_mode = 'UPDATE' THEN
2892
2893 l_stmt_num := 120;
2894
2895 -- check if the required parameters are not null values
2896
2897 IF p_request_id IS NULL THEN
2898 FND_MESSAGE.SET_NAME('WIP', 'WIP_ATTRIBUTE_REQUIRED');
2899 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'p_request_id');
2900 FND_MSG_PUB.ADD;
2901 x_return_flag := FALSE;
2902 RAISE FND_API.G_EXC_ERROR;
2903 ELSE
2904 -- check if the request actually exists
2905 BEGIN
2906 SELECT organization_id, asset_group, asset_number,work_request_priority_id, work_request_status_id, expected_resolution_date, work_request_owning_dept, work_request_type_id, created_for
2907 INTO l_org_id, l_asset_group_id, l_asset_number, l_priority_id, l_status_id, l_request_by_date, l_owning_dept_id, l_work_request_type_id, l_created_for
2908 FROM wip_eam_work_requests
2909 WHERE work_request_id = p_request_id;
2910
2911 EXCEPTION
2912 WHEN NO_DATA_FOUND THEN
2913 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
2914 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Request Id: ' || to_char(p_request_id));
2915 FND_MSG_PUB.ADD;
2916 x_return_flag := FALSE;
2917 RAISE FND_API.G_EXC_ERROR;
2918 END;
2919 END IF;
2920
2921 l_stmt_num := 130;
2922
2923 IF p_org_id = FND_API.G_MISS_NUM THEN
2924 FND_MESSAGE.SET_NAME('WIP', 'WIP_ATTRIBUTE_REQUIRED');
2925 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'p_org_id');
2926 FND_MSG_PUB.ADD;
2927 x_return_flag := FALSE;
2928 RAISE FND_API.G_EXC_ERROR;
2929 END IF;
2930
2931 IF (p_org_id <> l_org_id) THEN
2932 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
2933 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Org Id: ' || to_char(p_org_id));
2934 FND_MSG_PUB.ADD;
2935 x_return_flag := FALSE;
2936 RAISE FND_API.G_EXC_ERROR;
2937 END IF;
2938
2939 l_stmt_num := 140;
2940
2941 -- check if the org id is valid
2942 BEGIN
2943 SELECT WORK_REQUEST_AUTO_APPROVE INTO l_auto_approve
2944 FROM WIP_EAM_PARAMETERS
2945 WHERE organization_id = p_org_id;
2946 EXCEPTION
2947 WHEN NO_DATA_FOUND THEN
2948 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
2949 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Org Id: ' || to_char(p_org_id));
2950 FND_MSG_PUB.ADD;
2951 x_return_flag := FALSE;
2952 RAISE FND_API.G_EXC_ERROR;
2953 END;
2954
2955 --
2956 IF p_asset_group_id is not null then
2957 begin
2958 select eam_item_type
2959 into l_eam_item_type
2960 from mtl_system_items
2961 where inventory_item_id = p_asset_group_id
2962 and organization_id = p_org_id;
2963 exception
2964 when no_data_found then
2965 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
2966 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Asset Number: ' || p_asset_number || ', '|| 'Org Id: ' || p_org_id || ' and Group Id: ' || p_asset_group_id);
2967 FND_MSG_PUB.ADD;
2968 x_return_flag := FALSE;
2969 RAISE FND_API.G_EXC_ERROR;
2970 end;
2971
2972 if p_asset_number is null then
2973 --FILL error: Asset Number needs to be entered if Asset Group is entered
2974 FND_MESSAGE.SET_NAME('WIP', 'WIP_ATTRIBUTE_REQUIRED');
2975 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Asset Number');
2976 FND_MSG_PUB.ADD;
2977 x_return_flag := FALSE;
2978 RAISE FND_API.G_EXC_ERROR;
2979 end if;
2980
2981 -- Bug # 3553217.
2982 if (l_eam_item_type =1 OR l_eam_item_type = 3) then
2983 -- If Capital Asset or Rebuild Inventory
2984 BEGIN
2985 -- check that the asset is maintainable
2986 SELECT nvl(maintainable_flag, 'Y') into l_dummy_char
2987 FROM CSI_ITEM_INSTANCES cii
2988 WHERE cii.serial_number = p_asset_number AND
2989 cii.inventory_item_id = p_asset_group_id;
2990
2991 EXCEPTION
2992 WHEN NO_DATA_FOUND THEN
2993 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
2994 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Asset Number: ' || p_asset_number || ', '|| 'Org Id: ' || p_org_id || ' and Group Id: ' || p_asset_group_id);
2995 FND_MSG_PUB.ADD;
2996 x_return_flag := FALSE;
2997 RAISE FND_API.G_EXC_ERROR;
2998 END;
2999
3000 IF (l_dummy_char <> 'Y') THEN
3001 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_ASSET_NOT_MAINTAINABLE');
3002 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'p_asset_number');
3003 FND_MSG_PUB.ADD;
3004 x_return_flag := FALSE;
3005 RAISE FND_API.G_EXC_ERROR;
3006 END IF;
3007
3008 else
3009 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
3010 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Asset Number: ' || p_asset_number || ', '|| 'Org Id: ' || p_org_id || ' and Group Id: ' || p_asset_group_id);
3011 FND_MSG_PUB.ADD;
3012 x_return_flag := FALSE;
3013 RAISE FND_API.G_EXC_ERROR;
3014 END IF;
3015 ELSIF (p_asset_number is not null) then
3016 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
3017 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Asset Number: ' || p_asset_number || ', '|| 'Org Id: ' || p_org_id || ' and Group Id: ' || p_asset_group_id);
3018 FND_MSG_PUB.ADD;
3019 x_return_flag := FALSE;
3020 RAISE FND_API.G_EXC_ERROR;
3021 end if;
3022
3023
3024 l_stmt_num := 150;
3025
3026
3027 l_stmt_num := 160;
3028
3029 IF p_request_by_date = FND_API.G_MISS_DATE THEN
3030 FND_MESSAGE.SET_NAME('INV', 'INV_ATTRIBUTE_REQUIRED');
3031 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'p_request_by_date');
3032 FND_MSG_PUB.ADD;
3033 x_return_flag := FALSE;
3034 RAISE FND_API.G_EXC_ERROR;
3035 ELSE
3036 IF p_request_by_date < sysdate THEN
3037 validate_for_date_change(l_request_by_date, p_request_by_date, 'EAM_WR_CANNOT_UPDATE', null, x_return_flag);
3038 END IF;
3039 END IF;
3040
3041
3042 -- check if the status id provided is valid
3043
3044 IF p_status_id is not null THEN
3045 if validate_lookup(p_status_id,'WIP_EAM_WORK_REQ_STATUS') = FALSE then
3046
3047 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
3048 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Status Id: ' || to_char(p_status_id));
3049 FND_MSG_PUB.ADD;
3050 x_return_flag := FALSE;
3051 RAISE FND_API.G_EXC_ERROR;
3052 END IF;
3053 ELSE
3054 IF p_status_id = FND_API.G_MISS_NUM THEN
3055 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
3056 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Status Id: ' || to_char(p_status_id));
3057 FND_MSG_PUB.ADD;
3058 x_return_flag := FALSE;
3059 RAISE FND_API.G_EXC_ERROR;
3060 END IF;
3061 END IF;
3062
3063 l_stmt_num := 170;
3064
3065 -- check if the priority id provided is valid
3066
3067 IF p_priority_id is not null THEN
3068 if validate_lookup(p_priority_id, 'WIP_EAM_ACTIVITY_PRIORITY') = FALSE then
3069
3070 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
3071 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Priority Id: ' || to_char(p_priority_id));
3072 FND_MSG_PUB.ADD;
3073 x_return_flag := FALSE;
3074 RAISE FND_API.G_EXC_ERROR;
3075 END IF;
3076 ELSE
3077 IF p_priority_id = FND_API.G_MISS_NUM THEN
3078 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
3079 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Priority Id: ' || to_char(p_priority_id));
3080 FND_MSG_PUB.ADD;
3081 x_return_flag := FALSE;
3082 RAISE FND_API.G_EXC_ERROR;
3083 END IF;
3084 END IF;
3085
3086 l_stmt_num := 180;
3087
3088 -- check if the work request type id provided is valid
3089 IF p_work_request_type_id is not null THEN
3090 if validate_lookup(p_work_request_type_id,'WIP_EAM_WORK_REQ_TYPE') = FALSE then
3091
3092 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
3093 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Work Request Type Id: ' || to_char(p_work_request_type_id));
3094 FND_MSG_PUB.ADD;
3095 x_return_flag := FALSE;
3096 RAISE FND_API.G_EXC_ERROR;
3097 END IF;
3098 END IF;
3099
3100 l_stmt_num := 190;
3101
3102 -- check if the owning dept id provided is valid
3103
3104 IF p_owning_dept_id is not null THEN
3105 IF p_owning_dept_id = FND_API.G_MISS_NUM THEN
3106 FND_MESSAGE.SET_NAME('WIP', 'WIP_ATTRIBUTE_REQUIRED');
3107 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'p_owning_dept_id');
3108 FND_MSG_PUB.ADD;
3109 x_return_flag := FALSE;
3110 RAISE FND_API.G_EXC_ERROR;
3111 ELSIF validate_department(p_org_id,p_owning_dept_id) = FALSE then
3112 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
3113 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Owning Dept. Id: ' || to_char(p_priority_id));
3114 FND_MSG_PUB.ADD;
3115 x_return_flag := FALSE;
3116 RAISE FND_API.G_EXC_ERROR;
3117 END IF;
3118 END IF;
3119
3120 -- check user_id field
3121 if l_valid_user_id = FALSE then
3122 FND_MESSAGE.SET_NAME('WIP', 'WIP_ML_LAST_UPDATED_BY');
3123 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Last Updated By: ' || p_user_id);
3124 FND_MSG_PUB.ADD;
3125 x_return_flag := FALSE;
3126 RAISE FND_API.G_EXC_ERROR;
3127 end if;
3128
3129 -- check the created for field
3130 if p_created_for is not null then
3131 if validate_user_id(p_created_for) = FALSE then
3132 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_CREATED_FOR');
3133 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Created For: ' || p_created_for);
3134 FND_MSG_PUB.ADD;
3135 x_return_flag := FALSE;
3136 RAISE FND_API.G_EXC_ERROR;
3137 end if;
3138 end if;
3139
3140 -- check contact preference
3141 IF p_contact_preference is not null then
3142 if validate_lookup(p_contact_preference,'WIP_EAM_CONTACT_PREFERENCE') = FALSE then
3143 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
3144 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Contact Preference: ' || to_char(p_contact_preference));
3145 FND_MSG_PUB.ADD;
3146 x_return_flag := FALSE;
3147 RAISE FND_API.G_EXC_ERROR;
3148 end if;
3149
3150 end if;
3151
3152 -- check notify originator
3153 IF p_notify_originator is not null then
3154 if validate_lookup(p_notify_originator,'SYS_YES_NO') = FALSE then
3155 FND_MESSAGE.SET_NAME('WIP', 'WIP_INVALID_ATTRIBUTE');
3156 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Notify Originator: ' || to_char(p_notify_originator));
3157 FND_MSG_PUB.ADD;
3158 x_return_flag := FALSE;
3159 RAISE FND_API.G_EXC_ERROR;
3160 end if;
3161 end if;
3162
3163 --need to add code to validate flexfield
3164 if (validate_desc_flex_field
3165 (
3166 p_app_short_name => 'WIP'
3167 ,p_desc_flex_name => 'WIP_EAM_WORK_REQUESTS'
3168 ,p_attribute_category => p_attribute_category
3169 ,p_attribute1 => p_attribute1
3170 ,p_attribute2 => p_attribute2
3171 ,p_attribute3 => p_attribute3
3172 ,p_attribute4 => p_attribute4
3173 ,p_attribute5 => p_attribute5
3174 ,p_attribute6 => p_attribute6
3175 ,p_attribute7 => p_attribute7
3176 ,p_attribute8 => p_attribute8
3177 ,p_attribute9 => p_attribute9
3178 ,p_attribute10 => p_attribute10
3179 ,p_attribute11 => p_attribute11
3180 ,p_attribute12 => p_attribute12
3181 ,p_attribute13 => p_attribute13
3182 ,p_attribute14 => p_attribute14
3183 ,p_attribute15 => p_attribute15
3184 ,x_error_segments => l_x_error_segments
3185 ,x_error_message => l_x_error_message
3186 )
3187 ) = FALSE then
3188 FND_MESSAGE.SET_NAME('WIP','WIP_INVALID_ATTRIBUTE');
3189 FND_MSG_PUB.ADD;
3190 RAISE FND_API.G_EXC_ERROR;
3191
3192 end if;
3193
3194 -- Bug # 3553217.
3195 if (l_status_id in (4,5,6)) then
3196 validate_for_char_change(l_asset_number, p_asset_number, 'EAM_WR_ATTR_NOT_UPD', 'ASSET_NUMBER: ', x_return_flag);
3197 validate_for_num_change(l_status_id, p_status_id,'EAM_WR_ATTR_NOT_UPD', 'WORK_REQUEST_STATUS_ID: ', x_return_flag);
3198 validate_for_num_change(l_priority_id, p_priority_id, 'EAM_WR_ATTR_NOT_UPD', 'WORK_REQUEST_PRIORITY_ID: ', x_return_flag);
3199 validate_for_num_change(l_owning_dept_id, p_owning_dept_id, 'EAM_WR_ATTR_NOT_UPD', 'WORK_REQUEST_OWNING_DEPT: ', x_return_flag);
3200 validate_for_num_change(l_work_request_type_id, p_work_request_type_id, 'EAM_WR_ATTR_NOT_UPD', 'WORK_REQUEST_TYPE_ID: ', x_return_flag);
3201 validate_for_date_change(l_request_by_date, p_request_by_date, 'EAM_WR_ATTR_NOT_UPD', 'EXPECTED_RESOLUTION_DATE: ', x_return_flag);
3202 end if;
3203
3204 if (l_request_by_date < sysdate) then
3205 /*commenting for bug 8565111, for enabling status change through import API*/
3206 -- validate_for_num_change(l_status_id, p_status_id,'EAM_WR_CANNOT_UPDATE', null, x_return_flag);
3207 validate_for_num_change(l_priority_id, p_priority_id, 'EAM_WR_CANNOT_UPDATE', null, x_return_flag);
3208 validate_for_num_change(l_owning_dept_id, p_owning_dept_id, 'EAM_WR_CANNOT_UPDATE', null, x_return_flag);
3209 validate_for_num_change(l_work_request_type_id, p_work_request_type_id, 'EAM_WR_CANNOT_UPDATE', null, x_return_flag);
3210 validate_for_date_change(l_request_by_date, p_request_by_date, 'EAM_WR_CANNOT_UPDATE', null, x_return_flag);
3211 end if;
3212
3213 if (l_auto_approve is null or l_auto_approve = 'N') then
3214 if (l_status_id in (1,2)) then
3215 validate_for_num_change(l_status_id, p_status_id,'EAM_WR_STATUS_UPD_ERR', null, x_return_flag);
3216 end if;
3217 end if;
3218
3219 l_stmt_num := 200;
3220
3221 END IF;
3222
3223 EXCEPTION
3224 WHEN fnd_api.g_exc_error THEN
3225 x_return_status := fnd_api.g_ret_sts_error;
3226 fnd_msg_pub.count_and_get(
3227 p_encoded => fnd_api.g_false
3228 ,p_count => x_msg_count
3229 ,p_data => x_msg_data);
3230 WHEN fnd_api.g_exc_unexpected_error THEN
3231 x_return_status := fnd_api.g_ret_sts_unexp_error;
3232 fnd_msg_pub.count_and_get(
3233 p_encoded => fnd_api.g_false
3234 ,p_count => x_msg_count
3235 ,p_data => x_msg_data);
3236 WHEN OTHERS THEN
3237 x_return_status := fnd_api.g_ret_sts_unexp_error;
3238 IF fnd_msg_pub.check_msg_level(
3239 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3240 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name||'('||l_stmt_num||')');
3241 fnd_msg_pub.count_and_get(
3242 p_encoded => fnd_api.g_false
3243 ,p_count => x_msg_count
3244 ,p_data => x_msg_data);
3245 END IF;
3246 end validate_work_request;
3247
3248
3249 Procedure Auto_Approve_Check(
3250 p_api_version in NUMBER,
3251 p_init_msg_list in VARCHAR2 := FND_API.G_FALSE,
3252 p_commit in VARCHAR2 := FND_API.G_FALSE,
3253 p_validation_level in NUMBER := FND_API.G_VALID_LEVEL_FULL,
3254 p_org_id in NUMBER,
3255 x_return_check out NOCOPY VARCHAR2,
3256 x_return_status out NOCOPY VARCHAR2,
3257 x_msg_count out NOCOPY NUMBER,
3258 x_msg_data out NOCOPY VARCHAR2
3259 )is
3260 l_api_name CONSTANT VARCHAR2(30) := 'Auto_Approve_Check';
3261 l_api_version CONSTANT NUMBER := 115.0;
3262 l_auto_approve_flag VARCHAR2(1);
3263 l_standard_log VARCHAR2(2000);
3264 l_stmt_num NUMBER;
3265 BEGIN
3266 l_stmt_num := 10;
3267 -- Standard Start of API savepoint
3268 SAVEPOINT auto_approve_check_pvt;
3269 l_stmt_num := 20;
3270 -- Standard call to check for call compatibility.
3271 IF NOT fnd_api.compatible_api_call(
3272 l_api_version
3273 ,p_api_version
3274 ,l_api_name
3275 ,g_pkg_name) THEN
3276 RAISE fnd_api.g_exc_unexpected_error;
3277 END IF;
3278 l_stmt_num := 30;
3279 -- Initialize message list if p_init_msg_list is set to TRUE.
3280 IF fnd_api.to_boolean(p_init_msg_list) THEN
3281 fnd_msg_pub.initialize;
3282 END IF;
3283 l_stmt_num := 40;
3284 -- Initialize API return status to success
3285 x_return_status := fnd_api.g_ret_sts_success;
3286 BEGIN
3287 l_stmt_num := 50;
3288 select NVL(work_request_auto_approve,'N')
3289 into l_auto_approve_flag
3290 from wip_eam_parameters
3291 where organization_id = p_org_id;
3292 EXCEPTION
3293 WHEN no_data_found THEN
3294 l_auto_approve_flag := 'N';
3295 END;
3296 l_stmt_num := 60;
3297 x_return_check := l_auto_approve_flag ;
3298 -- End API Body
3299 l_stmt_num := 998;
3300 -- Standard check of p_commit.
3301 IF fnd_api.to_boolean(p_commit) THEN
3302 COMMIT WORK;
3303 END IF;
3304 l_stmt_num := 999;
3305 -- Standard call to get message count and if count is 1, get message info.
3306 fnd_msg_pub.count_and_get(
3307 p_encoded => fnd_api.g_false
3308 ,p_count => x_msg_count
3309 ,p_data => x_msg_data);
3310 EXCEPTION
3311 WHEN fnd_api.g_exc_error THEN
3312 ROLLBACK TO auto_approve_check_pvt;
3313 x_return_status := fnd_api.g_ret_sts_error;
3314 fnd_msg_pub.count_and_get(
3315 p_encoded => fnd_api.g_false
3316 ,p_count => x_msg_count
3317 ,p_data => x_msg_data);
3318 WHEN fnd_api.g_exc_unexpected_error THEN
3319 ROLLBACK TO auto_approve_check_pvt;
3320 x_return_status := fnd_api.g_ret_sts_unexp_error;
3321 fnd_msg_pub.count_and_get(
3322 p_encoded => fnd_api.g_false
3323 ,p_count => x_msg_count
3324 ,p_data => x_msg_data);
3325 WHEN OTHERS THEN
3326 ROLLBACK TO auto_approve_check_pvt;
3327 x_return_status := fnd_api.g_ret_sts_unexp_error;
3328 IF fnd_msg_pub.check_msg_level(
3329 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3330 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name||'('||l_stmt_num||')');
3331 END IF;
3332 fnd_msg_pub.count_and_get(
3333 p_encoded => fnd_api.g_false
3334 ,p_count => x_msg_count
3335 ,p_data => x_msg_data);
3336 END Auto_Approve_Check;
3337
3338 procedure create_and_approve(
3339 p_api_version in NUMBER,
3340 p_init_msg_list in VARCHAR2 := FND_API.G_FALSE,
3341 p_commit in VARCHAR2 := FND_API.G_FALSE,
3342 p_validation_level in NUMBER := FND_API.G_VALID_LEVEL_FULL,
3343 p_org_id in NUMBER,
3344 p_asset_group_id in NUMBER,
3345 p_asset_number in VARCHAR2,
3346 p_priority_id in NUMBER,
3347 p_request_by_date in DATE,
3348 p_request_log in VARCHAR2,
3349 p_owning_dept_id in NUMBER,
3350 p_user_id in NUMBER,
3351 p_work_request_type_id IN NUMBER,
3352 p_maintenance_object_type IN NUMBER,
3353 p_maintenance_object_id IN NUMBER,
3354 p_eam_linear_id IN NUMBER DEFAULT NULL,
3355 p_asset_location IN NUMBER,
3356 p_expected_resolution_date IN DATE,
3357 p_work_request_created_by IN NUMBER,
3358 p_created_for IN NUMBER DEFAULT NULL,
3359 p_phone_number IN VARCHAR2 DEFAULT NULL,
3360 p_email IN VARCHAR2 DEFAULT NULL,
3361 p_contact_preference IN NUMBER DEFAULT NULL,
3362 p_notify_originator IN NUMBER DEFAULT NULL,
3363 p_attribute_category IN VARCHAR2 DEFAULT NULL,
3364 p_attribute1 IN VARCHAR2 DEFAULT NULL,
3365 p_attribute2 IN VARCHAR2 DEFAULT NULL,
3366 p_attribute3 IN VARCHAR2 DEFAULT NULL,
3367 p_attribute4 IN VARCHAR2 DEFAULT NULL,
3368 p_attribute5 IN VARCHAR2 DEFAULT NULL,
3369 p_attribute6 IN VARCHAR2 DEFAULT NULL,
3370 p_attribute7 IN VARCHAR2 DEFAULT NULL,
3371 p_attribute8 IN VARCHAR2 DEFAULT NULL,
3372 p_attribute9 IN VARCHAR2 DEFAULT NULL,
3373 p_attribute10 IN VARCHAR2 DEFAULT NULL,
3374 p_attribute11 IN VARCHAR2 DEFAULT NULL,
3375 p_attribute12 IN VARCHAR2 DEFAULT NULL,
3376 p_attribute13 IN VARCHAR2 DEFAULT NULL,
3377 p_attribute14 IN VARCHAR2 DEFAULT NULL,
3378 p_attribute15 IN VARCHAR2 DEFAULT NULL,
3379 x_work_request_id OUT NOCOPY NUMBER,
3380 x_resultout OUT NOCOPY VARCHAR2 ,
3381 x_error_message OUT NOCOPY VARCHAR2,
3382 x_return_status out NOCOPY VARCHAR2,
3383 x_msg_count out NOCOPY NUMBER,
3384 x_msg_data out NOCOPY VARCHAR2
3385 )
3386 IS
3387 l_api_name CONSTANT VARCHAR2(30) := 'create_and_approve';
3388 l_api_version CONSTANT NUMBER := 1.0;
3389 l_work_request_id NUMBER;
3390 l_status_id NUMBER;
3391 l_return_status VARCHAR2(30);
3392 l_msg_count NUMBER;
3393 l_msg_data VARCHAR2(30);
3394 l_results_out VARCHAR2(200);
3395 l_error_message VARCHAR2(200);
3396 WF_ERROR EXCEPTION;
3397 l_asset_location_id NUMBER;
3398 l_expected_resolution_date DATE;
3399
3400 l_stmt_number NUMBER;
3401 l_stmt_num NUMBER;
3402 l_work_request_auto_approve varchar2(1);
3403 l_owning_dept_id NUMBER;
3404 l_wf_item_type VARCHAR2(8) := 'EAMWRAP';
3405 l_wf_item_key VARCHAR2(240);
3406 l_instance_id NUMBER;
3407
3408 BEGIN
3409 l_stmt_number := 10;
3410 -- Standard Start of API savepoint
3411 SAVEPOINT create_and_approve;
3412 l_stmt_num := 20;
3413 -- Standard call to check for call compatibility.
3414 IF NOT fnd_api.compatible_api_call(
3415 l_api_version
3416 ,p_api_version
3417 ,l_api_name
3418 ,g_pkg_name) THEN
3419 RAISE fnd_api.g_exc_unexpected_error;
3420 END IF;
3421 l_stmt_num := 30;
3422 -- Initialize message list if p_init_msg_list is set to TRUE.
3423 IF fnd_api.to_boolean(p_init_msg_list) THEN
3424 fnd_msg_pub.initialize;
3425 END IF;
3426 -- Initialize API return status to success
3427 x_return_status := fnd_api.g_ret_sts_success;
3428 -- API body
3429
3430 if (p_maintenance_object_id is not null) then
3431 l_instance_id := p_maintenance_object_id;
3432 else
3433 IF (p_asset_group_id <> 0 AND p_asset_group_id IS NOT NULL) THEN
3434 begin
3435
3436 select instance_id into l_instance_id
3437 from csi_item_instances cii
3438 where cii.inventory_item_id = p_asset_group_id
3439 and cii.serial_number = p_asset_number;
3440 exception
3441 when others then
3442 raise;
3443 end;
3444 end if;
3445
3446 end if;
3447
3448 -- if owning dept is not specified, derive it from asset or eam parameters
3449 IF (p_owning_dept_id is null) then
3450 IF (p_maintenance_object_id is not null) then
3451 begin
3452
3453 SELECT owning_department_id
3454 into l_owning_dept_id
3455 from eam_org_maint_defaults
3456 where object_type = 50
3457 and object_id = p_maintenance_object_id
3458 and organization_id = p_org_id;
3459 exception
3460 when no_data_found then
3461 null;
3462 end;
3463
3464 else
3465 -- select owning dept from asset
3466 IF (p_asset_group_id <> 0 AND p_asset_group_id IS NOT NULL) THEN
3467 begin
3468
3469 select instance_id into l_instance_id
3470 from csi_item_instances cii
3471 where cii.inventory_item_id = p_asset_group_id
3472 and cii.serial_number = p_asset_number;
3473
3474
3475 SELECT owning_department_id
3476 INTO l_owning_dept_id
3477 FROM eam_org_maint_defaults eomd
3478 WHERE eomd.organization_id = p_org_id
3479
3480 and eomd.object_type(+) = 50
3481 and eomd.object_id (+) = l_instance_id;
3482 exception
3483 when no_data_found then
3484 null;
3485 end;
3486 END IF;
3487 end if;
3488
3489 IF (l_owning_dept_id is null) then
3490 -- select owning dept from eam parameters
3491 select default_department_id
3492 into l_owning_dept_id
3493 from wip_eam_parameters
3494 where organization_id = p_org_id;
3495 END IF;
3496 ELSE
3497 l_owning_dept_id := p_owning_dept_id;
3498 END IF;
3499
3500
3501 WIP_EAM_WORKREQUEST_PVT.create_work_request(
3502 p_api_version => p_api_version ,
3503 p_init_msg_list => p_init_msg_list ,
3504 p_commit => fnd_api.g_false ,
3505 p_validation_level => p_validation_level ,
3506 p_org_id => p_org_id,
3507 p_asset_group_id => p_asset_group_id ,
3508 p_asset_number => p_asset_number ,
3509 p_priority_id => p_priority_id,
3510 p_request_by_date => p_request_by_date,
3511 p_request_log => p_request_log,
3512 p_owning_dept_id => l_owning_dept_id,
3513 p_user_id => p_user_id ,
3514 p_work_request_type_id => p_work_request_type_id,
3515 p_maintenance_object_id => l_instance_id,
3516 p_eam_linear_id => p_eam_linear_id,
3517 p_work_request_created_by => p_work_request_created_by,
3518 p_created_for => p_created_for,
3519 p_phone_number => p_phone_number,
3520 p_email => p_email,
3521 p_contact_preference => p_contact_preference,
3522 p_notify_originator => p_notify_originator,
3523 p_attribute_category => p_attribute_category,
3524 p_attribute1 => p_attribute1,
3525 p_attribute2 => p_attribute2,
3526 p_attribute3 => p_attribute3,
3527 p_attribute4 => p_attribute4,
3528 p_attribute5 => p_attribute5,
3529 p_attribute6 => p_attribute6,
3530 p_attribute7 => p_attribute7,
3531 p_attribute8 => p_attribute8,
3532 p_attribute9 => p_attribute9,
3533 p_attribute10 => p_attribute10,
3534 p_attribute11 => p_attribute11,
3535 p_attribute12 => p_attribute12,
3536 p_attribute13 => p_attribute13,
3537 p_attribute14 => p_attribute14,
3538 p_attribute15 => p_attribute15,
3539 x_request_id => l_work_request_id,
3540 x_status_id => l_status_id,
3541 x_return_status => l_return_status,
3542 x_msg_count => l_msg_count,
3543 x_msg_data => l_msg_data
3544 );
3545 l_stmt_number := 20;
3546 --dbms_output.put_line('Inside: Work Reques ID ='||l_work_request_id);
3547
3548 SELECT work_request_auto_approve
3549 INTO l_work_request_auto_approve
3550 FROM wip_eam_parameters
3551 WHERE organization_id = p_org_id;
3552
3553 IF l_work_request_auto_approve = 'N' then
3554
3555 IF p_asset_location is null then
3556
3557 BEGIN
3558 SELECT area_id
3559 INTO l_asset_location_id
3560 FROM eam_org_maint_defaults eomd
3561 WHERE eomd.organization_id = p_org_id
3562 AND eomd.object_type = 50
3563 AND eomd.object_id = l_instance_id;
3564 EXCEPTION
3565 WHEN no_data_found then
3566 null;
3567 END;
3568 ELSE
3569 l_asset_location_id := p_asset_location;
3570
3571 END IF;
3572
3573 IF p_expected_resolution_date is null then
3574 l_expected_resolution_date := p_request_by_date;
3575 ELSE
3576 l_expected_resolution_date := p_expected_resolution_date;
3577 END IF;
3578
3579 WIP_EAM_WRAPPROVAL_PVT.StartWRAProcess (
3580 p_work_request_id => l_work_request_id,
3581 p_asset_number => p_asset_number ,
3582 p_asset_group => p_asset_group_id ,
3583 p_maintenance_object_id => l_instance_id, -- Bug 8786980
3584 p_asset_location => l_asset_location_id ,
3585 p_organization_id => p_org_id ,
3586 p_work_request_status_id => l_status_id ,
3587 p_work_request_priority_id => p_priority_id ,
3588 p_work_request_owning_dept_id => l_owning_dept_id,
3589 p_expected_resolution_date => l_expected_resolution_date ,
3590 p_work_request_type_id => p_work_request_type_id ,
3591 p_notes => p_request_log ,
3592 p_notify_originator => p_notify_originator,
3593 p_resultout => l_results_out ,
3594 p_error_message => l_error_message
3595 );
3596
3597 --dbms_output.put_line('After workflow and status is'||l_results_out);
3598 If (l_results_out <> FND_API.G_RET_STS_SUCCESS) then
3599 --dbms_output.put_line('Error:'||l_results_out);
3600 x_resultout := l_results_out;
3601 x_error_message := l_error_message;
3602 x_work_request_id := l_work_request_id;
3603 raise WF_ERROR;
3604 Else
3605
3606 x_resultout := l_results_out;
3607 x_error_message := l_error_message;
3608 x_work_request_id := l_work_request_id;
3609 -- commit ; for bug # 13355580
3610 END IF ;
3611 else
3612 x_resultout := l_return_status;
3613 x_work_request_id := l_work_request_id;
3614 -- commit ; for bug # 13355580
3615
3616 END IF;
3617 l_stmt_number := 30;
3618
3619
3620 -- End of API body.
3621 l_stmt_num := 998;
3622 -- Standard check of p_commit.
3623 IF fnd_api.to_boolean(p_commit) THEN
3624 COMMIT WORK;
3625 END IF;
3626 l_stmt_num := 999;
3627 -- Standard call to get message count and if count is 1, get message info.
3628 fnd_msg_pub.count_and_get(
3629 p_encoded => fnd_api.g_false
3630 ,p_count => x_msg_count
3631 ,p_data => x_msg_data);
3632 EXCEPTION
3633 When WF_ERROR then
3634 ROLLBACK TO create_and_approve;
3635 x_return_status := fnd_api.g_ret_sts_error;
3636 fnd_msg_pub.add_exc_msg(g_pkg_name, x_error_message);
3637 fnd_msg_pub.count_and_get(
3638 p_encoded => fnd_api.g_false
3639 ,p_count => x_msg_count
3640 ,p_data => x_msg_data);
3641 WHEN fnd_api.g_exc_error THEN
3642 -- dbms_output.put_line ('Line = '||l_stmt_num);
3643 ROLLBACK TO create_and_approve;
3644 x_return_status := fnd_api.g_ret_sts_error;
3645 fnd_msg_pub.count_and_get(
3646 p_encoded => fnd_api.g_false
3647 ,p_count => x_msg_count
3648 ,p_data => x_msg_data);
3649 WHEN fnd_api.g_exc_unexpected_error THEN
3650 -- dbms_output.put_line ('Line = '||l_stmt_num);
3651 ROLLBACK TO create_and_approve;
3652 x_return_status := fnd_api.g_ret_sts_unexp_error;
3653 fnd_msg_pub.count_and_get(
3654 p_encoded => fnd_api.g_false
3655 ,p_count => x_msg_count
3656 ,p_data => x_msg_data);
3657 WHEN OTHERS THEN
3658 -- dbms_output.put_line ('Line = '||l_stmt_num);
3659 ROLLBACK TO create_and_approve;
3660 x_return_status := fnd_api.g_ret_sts_unexp_error;
3661 IF fnd_msg_pub.check_msg_level(
3662 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3663 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name||'('||l_stmt_num||')');
3664 END IF;
3665 fnd_msg_pub.count_and_get(
3666 p_encoded => fnd_api.g_false
3667 ,p_count => x_msg_count
3668 ,p_data => x_msg_data);
3669 END;
3670
3671 PROCEDURE check_product_install(
3672 p_api_version IN NUMBER,
3673 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3674 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3675 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3676 p_appl_id IN NUMBER,
3677 p_dep_appl_id IN Number,
3678 x_installed OUT NOCOPY NUMBER,
3679 x_return_status OUT NOCOPY VARCHAR2,
3680 x_msg_count OUT NOCOPY NUMBER,
3681 x_msg_data OUT NOCOPY VARCHAR2
3682 ) IS
3683 l_api_name CONSTANT VARCHAR2(30) := 'check_product_install';
3684 l_api_version CONSTANT NUMBER := 1.0;
3685 l_installed BOOLEAN;
3686 l_indust VARCHAR2(10);
3687 l_cs_installed VARCHAR2(10);
3688 l_stmt_num NUMBER;
3689
3690 BEGIN
3691 l_stmt_num := 1;
3692 -- Standard Start of API savepoint
3693 SAVEPOINT check_product_install;
3694 -- Standard call to check for call compatibility.
3695 IF NOT fnd_api.compatible_api_call(l_api_version,p_api_version,l_api_name,g_pkg_name) THEN
3696 RAISE fnd_api.g_exc_unexpected_error;
3697 END IF;
3698 -- Initialize message list if p_init_msg_list is set to TRUE.
3699 IF fnd_api.to_boolean(p_init_msg_list) THEN
3700 fnd_msg_pub.initialize;
3701 END IF;
3702 -- Initialize API return status to success
3703 x_return_status := fnd_api.g_ret_sts_success;
3704
3705 l_stmt_num := 5;
3706 -- API body
3707 l_installed := fnd_installation.get(appl_id => p_appl_id,
3708 dep_appl_id => p_dep_appl_id,
3709 status => l_cs_installed,
3710 industry => l_indust);
3711 l_stmt_num := 10;
3712 IF (l_installed = TRUE) THEN
3713 x_installed := 1;
3714 ELSE
3715 x_installed := 0;
3716 END IF;
3717
3718 l_stmt_num := 15;
3719 -- End of API body.
3720 -- Standard check of p_commit.
3721 IF fnd_api.to_boolean(p_commit) THEN
3722 COMMIT WORK;
3723 END IF;
3724 -- Standard call to get message count and if count is 1, get message info.
3725 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3726 p_count => x_msg_count,
3727 p_data => x_msg_data);
3728 l_stmt_num := 20;
3729
3730 EXCEPTION
3731 WHEN fnd_api.g_exc_error THEN
3732 ROLLBACK TO check_product_install;
3733 x_return_status := fnd_api.g_ret_sts_error;
3734 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3735 p_count => x_msg_count,
3736 p_data => x_msg_data);
3737
3738 WHEN fnd_api.g_exc_unexpected_error THEN
3739 ROLLBACK TO check_product_install;
3740 x_return_status := fnd_api.g_ret_sts_unexp_error;
3741 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3742 p_count => x_msg_count,
3743 p_data => x_msg_data);
3744
3745 WHEN OTHERS THEN
3746 ROLLBACK TO check_product_install;
3747 x_return_status := fnd_api.g_ret_sts_unexp_error;
3748 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3749 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name||'('||l_stmt_num||')');
3750 END IF;
3751 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
3752 p_count => x_msg_count,
3753 p_data => x_msg_data);
3754
3755 END check_product_install;
3756
3757 function validate_desc_flex_field
3758 (
3759 p_app_short_name IN VARCHAR:='EAM',
3760 p_desc_flex_name IN VARCHAR,
3761 p_ATTRIBUTE_CATEGORY IN VARCHAR2 default null,
3762 p_ATTRIBUTE1 IN VARCHAR2 default null,
3763 p_ATTRIBUTE2 IN VARCHAR2 default null,
3764 p_ATTRIBUTE3 IN VARCHAR2 default null,
3765 p_ATTRIBUTE4 IN VARCHAR2 default null,
3766 p_ATTRIBUTE5 IN VARCHAR2 default null,
3767 p_ATTRIBUTE6 IN VARCHAR2 default null,
3768 p_ATTRIBUTE7 IN VARCHAR2 default null,
3769 p_ATTRIBUTE8 IN VARCHAR2 default null,
3770 p_ATTRIBUTE9 IN VARCHAR2 default null,
3771 p_ATTRIBUTE10 IN VARCHAR2 default null,
3772 p_ATTRIBUTE11 IN VARCHAR2 default null,
3773 p_ATTRIBUTE12 IN VARCHAR2 default null,
3774 p_ATTRIBUTE13 IN VARCHAR2 default null,
3775 p_ATTRIBUTE14 IN VARCHAR2 default null,
3776 p_ATTRIBUTE15 IN VARCHAR2 default null,
3777 x_error_segments OUT NOCOPY NUMBER,
3778 x_error_message OUT NOCOPY VARCHAR2
3779 )
3780 return boolean
3781 is
3782 l_validated boolean;
3783 begin
3784 x_error_segments:=null;
3785 x_error_message:=null;
3786
3787 FND_FLEX_DESCVAL.set_context_value(p_attribute_category);
3788 fnd_flex_descval.set_column_value('ATTRIBUTE1', p_ATTRIBUTE1);
3789 fnd_flex_descval.set_column_value('ATTRIBUTE2', p_ATTRIBUTE2);
3790 fnd_flex_descval.set_column_value('ATTRIBUTE3', p_ATTRIBUTE3);
3791 fnd_flex_descval.set_column_value('ATTRIBUTE4', p_ATTRIBUTE4);
3792 fnd_flex_descval.set_column_value('ATTRIBUTE5', p_ATTRIBUTE5);
3793 fnd_flex_descval.set_column_value('ATTRIBUTE6', p_ATTRIBUTE6);
3794 fnd_flex_descval.set_column_value('ATTRIBUTE7', p_ATTRIBUTE7);
3795 fnd_flex_descval.set_column_value('ATTRIBUTE8', p_ATTRIBUTE8);
3796 fnd_flex_descval.set_column_value('ATTRIBUTE9', p_ATTRIBUTE9);
3797 fnd_flex_descval.set_column_value('ATTRIBUTE10', p_ATTRIBUTE10);
3798 fnd_flex_descval.set_column_value('ATTRIBUTE11', p_ATTRIBUTE11);
3799 fnd_flex_descval.set_column_value('ATTRIBUTE12', p_ATTRIBUTE12);
3800 fnd_flex_descval.set_column_value('ATTRIBUTE13', p_ATTRIBUTE13);
3801 fnd_flex_descval.set_column_value('ATTRIBUTE14', p_ATTRIBUTE14);
3802 fnd_flex_descval.set_column_value('ATTRIBUTE15', p_ATTRIBUTE15);
3803
3804 l_validated:= FND_FLEX_DESCVAL.validate_desccols(
3805 p_app_short_name,
3806 p_desc_flex_name,
3807 'I',
3808 sysdate ) ;
3809
3810 if (l_validated) then
3811 return true;
3812 else
3813 x_error_segments:=FND_FLEX_DESCVAL.error_segment;
3814 x_error_message:=fnd_flex_descval.error_message;
3815 return false;
3816 end if;
3817 end validate_desc_flex_field;
3818
3819 END WIP_EAM_WORKREQUEST_PVT;