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