[Home] [Help]
PACKAGE BODY: APPS.GMP_RESOURCE_DTL_PUB
Source
1 PACKAGE BODY GMP_RESOURCE_DTL_PUB AS
2 /* $Header: GMPRSDTB.pls 120.6.12020000.2 2012/07/24 15:17:19 vkinduri ship $ */
3
4 /* =================================================================== */
5 /* Procedure: */
6 /* insert_resource_dtl */
7 /* */
8 /* DESCRIPTION: */
9 /* */
10 /* API returns (x_return_code) = 'S' if the insert into resources */
11 /* header (cr_rsrc_mst ) table is successfully. */
12 /* */
13 /* History : */
14 /* Sridhar 09-SEP-2002 Initial implementation */
15 /* =================================================================== */
16 PROCEDURE insert_resource_dtl
17 ( p_api_version IN NUMBER := 1
18 , p_init_msg_list IN BOOLEAN := TRUE
19 , p_commit IN BOOLEAN := FALSE
20 , p_resources IN cr_rsrc_dtl%ROWTYPE
21 , p_rsrc_instances IN resource_instances_tbl
22 , x_message_count OUT NOCOPY NUMBER
23 , x_message_list OUT NOCOPY VARCHAR2
24 , x_return_status IN OUT NOCOPY VARCHAR2
25 ) IS
26
27 /* Local variable section */
28 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_RESOURCE_DTL';
29 l_row_id ROWID;
30 v_resource_id number ;
31 v_instance_id number ;
32 l_return_status VARCHAR2(1);
33 g_return_status VARCHAR2(1);
34 v_std_usage_uom VARCHAR2(4);
35 v_capacity_uom VARCHAR2(4);
36 v_min_capacity NUMBER;
37 v_max_capacity NUMBER;
38 l_resources_rec cr_rsrc_dtl%ROWTYPE;
39 l_rsrc_instances resource_instances_tbl;
40
41 /* Define Exceptions */
42 resource_dtl_creation_failure EXCEPTION;
43 instance_creation_failure EXCEPTION;
44 resource_required EXCEPTION;
45 resource_id_required EXCEPTION;
46 instance_id_required EXCEPTION;
47 invalid_version EXCEPTION;
48
49 CURSOR Cur_resource_id IS
50 SELECT BOM_RESOURCES_S.nextval
51 FROM sys.dual;
52
53 CURSOR Cur_instance_id IS
54 SELECT GMP_RESOURCE_INSTANCES_S.nextval
55 FROM sys.DUAL;
56
57 /* B4724360 Rajesh Patangya INVCONV */
58 CURSOR Cur_uom IS
59 SELECT STD_USAGE_UOM,CAPACITY_UM,MIN_CAPACITY,MAX_CAPACITY
60 FROM cr_rsrc_mst
61 WHERE resources = l_resources_rec.resources
62 AND delete_mark = 0;
63
64 BEGIN
65
66 v_resource_id := NULL ;
67 v_instance_id := NULL ;
68 l_return_status := FND_API.G_RET_STS_SUCCESS;
69 g_return_status := FND_API.G_RET_STS_SUCCESS;
70
71 SAVEPOINT create_resource_dtl;
72
73 fnd_file.put_line(fnd_file.log,'CreateResourceDtlPub');
74
75 /* Set the return status to success initially */
76 x_return_status := FND_API.G_RET_STS_SUCCESS;
77 l_resources_rec := p_resources;
78 l_rsrc_instances := p_rsrc_instances;
79
80 /* Initialize message list and count if needed */
81 IF p_init_msg_list THEN
82 fnd_msg_pub.initialize;
83 END IF;
84
85 /* Make sure we are call compatible */
86 IF NOT FND_API.compatible_api_call ( GMP_RESOURCE_DTL_PUB.m_api_version
87 ,p_api_version
88 ,'INSERT_RESOURCE_DTL'
89 ,GMP_RESOURCE_DTL_PUB.m_pkg_name) THEN
90 x_return_status := FND_API.G_RET_STS_ERROR;
91 RAISE invalid_version;
92 END IF;
93
94 /* B4724360 Rajesh Patangya INVCONV */
95 IF (l_resources_rec.resources IS NOT NULL) AND
96 (l_resources_rec.organization_id IS NOT NULL) THEN
97 /* Validation 1. Check if this resources that is created does
98 not exists in the database. */
99 check_data(
100 l_resources_rec.organization_id,
101 l_resources_rec.resources,
102 l_resources_rec.resource_id, -- INVCONV - included resource id
103 l_resources_rec.group_resource,
104 l_resources_rec.assigned_qty,
105 l_resources_rec.daily_avail_use,
106 l_resources_rec.usage_uom, /* B4724360 - INVCONV */
107 l_resources_rec.nominal_cost,
108 l_resources_rec.inactive_ind,
109 l_resources_rec.ideal_capacity,
110 l_resources_rec.min_capacity,
111 l_resources_rec.max_capacity,
112 l_resources_rec.capacity_um, /* B4724360 - INVCONV */
113 l_resources_rec.capacity_constraint,
114 l_resources_rec.capacity_tolerance,
115 l_resources_rec.schedule_ind,
116 l_resources_rec.utilization,
117 l_resources_rec.efficiency,
118 l_resources_rec.calendar_code, /* B4724360 - INVCONV */
119 nvl(l_resources_rec.batchable_flag,0), /* BUG 4157063 */
120 l_resources_rec.batch_window, /* BUG 4157063 */
121 x_message_count,
122 x_message_list,
123 l_return_status);
124
125 /* After Validating the data , Insert the Resource Detail rows
126 only if the Return Status is 'S' else,. error Out
127 */
128
129 IF l_return_status = 'E' THEN /* resource return value */
130 RAISE resource_dtl_creation_failure;
131 ELSE /* Insert the Resource Data now */
132
133 OPEN Cur_resource_id;
134 v_resource_id := NULL ;
135 FETCH Cur_resource_id INTO v_resource_id;
136 CLOSE Cur_resource_id;
137
138 OPEN Cur_uom;
139 FETCH Cur_uom INTO v_std_usage_uom,
140 v_capacity_uom,
141 v_min_capacity,
142 v_max_capacity;
143 CLOSE Cur_uom;
144
145 /* the following lines will allow the User to have his own
146 values, instead if duplicating from the generic resource
147 values if min,max values are entered
148 */
149
150 IF l_resources_rec.min_capacity is NOT NULL
151 THEN
152 v_min_capacity := l_resources_rec.min_capacity;
153 END IF;
154
155 IF l_resources_rec.max_capacity is NOT NULL
156 THEN
157 v_max_capacity := l_resources_rec.max_capacity;
158 END IF;
159
160 /* Making the Capacity Tolerance field NULL if
161 Capacity Constraint field has value = 0
162 */
163 IF l_resources_rec.capacity_constraint = 0
164 THEN
165 l_resources_rec.capacity_tolerance := NULL;
166 END IF;
167
168 insert_detail_rows
169 (
170 l_resources_rec.organization_id, /* B4724360 - INVCONV */
171 l_resources_rec.resources,
172 l_resources_rec.group_resource,
173 l_resources_rec.assigned_qty,
174 l_resources_rec.daily_avail_use,
175 v_std_usage_uom,
176 l_resources_rec.nominal_cost,
177 l_resources_rec.inactive_ind,
178 sysdate,
179 FND_GLOBAL.user_id, /* Bug 6412180 */
180 sysdate,
181 FND_GLOBAL.user_id, /* Bug 6412180 */
182 FND_GLOBAL.user_id, /* Bug 6412180 */
183 l_resources_rec.trans_cnt,
184 0,
185 l_resources_rec.text_code,
186 l_resources_rec.ideal_capacity,
187 v_min_capacity,
188 v_max_capacity,
189 v_capacity_uom,
190 v_resource_id,
191 l_resources_rec.capacity_constraint,
192 l_resources_rec.capacity_tolerance,
193 l_resources_rec.schedule_ind,
194 l_resources_rec.utilization,
195 l_resources_rec.efficiency,
196 l_resources_rec.planning_exception_set, /* Bug # 6413873 */
197 l_resources_rec.calendar_code, /* B4724360 - INVCONV */
198 l_resources_rec.sds_window,
199 nvl(l_resources_rec.batchable_flag,0), /* BUG 4157063 */
200 l_resources_rec.batch_window /* BUG 4157063 */
201 );
202 x_return_status := l_return_status;
203 /* ------------------- Resource Instances starts ------------------- */
204 IF ((l_resources_rec.schedule_ind = 2 ) AND
205 (l_rsrc_instances.COUNT = l_resources_rec.assigned_qty))
206 THEN
207 FOR j IN 1..l_rsrc_instances.COUNT
208 LOOP /* Instance loop */
209 IF l_rsrc_instances(j).eff_start_date IS NULL THEN
210 l_rsrc_instances(j).eff_start_date := SYSDATE ;
211 END IF;
212
213 OPEN Cur_instance_id;
214 v_instance_id := NULL ;
215 FETCH Cur_instance_id INTO v_instance_id;
216 CLOSE Cur_instance_id;
217
218 check_instance_data (
219 v_resource_id
220 ,v_instance_id
221 ,l_rsrc_instances(j).vendor_id
222 ,l_rsrc_instances(j).eff_start_date
223 ,l_rsrc_instances(j).eff_end_date
224 ,l_rsrc_instances(j).maintenance_interval
225 ,l_rsrc_instances(j).inactive_ind
226 ,l_rsrc_instances(j).calibration_frequency
227 ,l_rsrc_instances(j).calibration_item_id
228 ,x_message_count
229 ,x_message_list
230 ,g_return_status );
231
232 x_return_status := g_return_status;
233
234 IF g_return_status = 'E' THEN /* Instance return status */
235 RAISE instance_creation_failure;
236 ELSE /* Insert the Resource Instance row */
237
238 insert_resource_instance
239 (
240 v_resource_id
241 ,v_instance_id
242 ,j /* p_instance_number */
243 ,l_rsrc_instances(j).vendor_id
244 ,l_rsrc_instances(j).model_number
245 ,l_rsrc_instances(j).serial_number
246 ,l_rsrc_instances(j).tracking_number
247 ,l_rsrc_instances(j).eff_start_date
248 ,l_rsrc_instances(j).eff_end_date
249 ,l_rsrc_instances(j).last_maintenance_date
250 ,l_rsrc_instances(j).maintenance_interval
251 ,l_rsrc_instances(j).inactive_ind
252 ,l_rsrc_instances(j).calibration_frequency
253 ,l_rsrc_instances(j).calibration_period
254 ,l_rsrc_instances(j).calibration_item_id
255 ,l_rsrc_instances(j).last_calibration_date
256 ,l_rsrc_instances(j).next_calibration_date
257 ,l_rsrc_instances(j).last_certification_date
258 ,l_rsrc_instances(j).certified_by
259 ,sysdate
260 ,FND_GLOBAL.user_id /* Bug 6412180 */
261 ,sysdate
262 ,FND_GLOBAL.user_id /* Bug 6412180 */
263 ,FND_GLOBAL.user_id /* Bug 6412180 */
264 ) ;
265 END IF; /* Instance return status */
266 END LOOP ; /* Instance loop */
267 END IF; /* Insert Resource Instance only if Schedule Ind = 2 */
268 /* ------------------- Resource Instances ends ------------------- */
269
270 END IF; /* resource return value */
271 ELSE
272 RAISE resource_id_required;
273 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
274 FND_MSG_PUB.ADD;
275 END IF; /* p_resources.resource_id IS NOT NULL */
276
277 fnd_msg_pub.count_and_get (
278 p_count => x_message_count
279 ,p_encoded => FND_API.g_false
280 ,p_data => x_message_list);
281
282 IF x_message_count = 0 THEN
283 -- x_return_status := 'S' ;
284 fnd_file.put_line(fnd_file.log,'Resource Detail was created successfully');
285 END IF;
286
287 fnd_file.put_line(fnd_file.log,'Completed '||l_api_name ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
288 EXCEPTION
289 WHEN resource_dtl_creation_failure OR invalid_version
290 OR instance_creation_failure THEN
291
292 ROLLBACK TO SAVEPOINT create_resource_dtl;
293 fnd_msg_pub.count_and_get (
294 p_count => x_message_count
295 ,p_encoded => FND_API.g_false
296 ,p_data => x_message_list);
297 x_return_status := FND_API.G_RET_STS_ERROR;
298
299 WHEN resource_id_required THEN
300 x_return_status := FND_API.G_RET_STS_ERROR;
301 FND_MESSAGE.SET_NAME('GMA','SY_REQUIRED');
302 FND_MSG_PUB.ADD;
303 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
304
305 WHEN instance_id_required THEN
306 x_return_status := FND_API.G_RET_STS_ERROR;
307 FND_MESSAGE.SET_NAME('GMA','SY_REQUIRED');
308 FND_MSG_PUB.ADD;
309 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
310
311 WHEN OTHERS THEN
312 ROLLBACK TO SAVEPOINT create_resource_dtl;
313 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
314 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
315 FND_MSG_PUB.ADD;
316 fnd_msg_pub.count_and_get (
317 p_count => x_message_count
318 ,p_encoded => FND_API.g_false
319 ,p_data => x_message_list);
320 x_return_status := FND_API.g_ret_sts_unexp_error;
321 END insert_resource_dtl;
322
323 /* =============================================================== */
324 /* Procedure: */
325 /* check_data */
326 /* */
327 /* DESCRIPTION: */
328 /* */
329 /* The following Procedure checks the Record and then Inserts */
330 /* the row into cr_rsrc_mst table and Returns S code if inserted */
331 /* Successfully */
332 /* */
333 /* History : */
334 /* Sgidugu 09/10/2002 Initial implementation */
335 /* Rajesh 11/28/2007 BUG 4157063 Resource batching */
336 /* =============================================================== */
337 PROCEDURE check_data
338 (
339 p_organization_id IN NUMBER, /* B4724360 - INVCONV */
340 p_resources IN VARCHAR2,
341 p_resource_id IN NUMBER, /* B4724360 - INVCONV */
342 p_group_resource IN VARCHAR2,
343 p_assigned_qty IN integer,
344 p_daily_avl_use IN NUMBER,
345 p_usage_um IN VARCHAR2,
346 p_nominal_cost IN NUMBER,
347 p_inactive_ind IN NUMBER,
348 p_ideal_capacity IN NUMBER,
349 p_min_capacity IN NUMBER,
350 p_max_capacity IN NUMBER,
351 p_capacity_uom IN VARCHAR2,
352 p_capacity_constraint IN NUMBER,
353 p_capacity_tolerance IN NUMBER,
354 p_schedule_ind IN NUMBER,
355 p_utilization IN NUMBER,
356 p_efficiency IN NUMBER,
357 p_calendar_code IN VARCHAR2, /* B4724360 - INVCONV */
358 p_batchable_flag IN NUMBER, /* BUG 4157063 */
359 p_batch_window IN NUMBER, /* BUG 4157063 */
360 x_message_count OUT NOCOPY NUMBER,
361 x_message_list OUT NOCOPY VARCHAR2,
362 x_return_status OUT NOCOPY VARCHAR2
363 ) IS
364
365
366 /* B4724360 Rajesh Patangya -Changed the source for organizations*/
367 CURSOR Cur_orgn_code IS
368 SELECT COUNT(1)
369 FROM mtl_parameters
370 where organization_id = p_organization_id
371 and process_enabled_flag = 'Y';
372
373 CURSOR Cur_resources IS
374 SELECT COUNT(1)
375 FROM cr_rsrc_mst
376 where resources = p_resources
377 and delete_mark = 0;
378
379 /* B4724360 Rajesh Patangya changed the where clause include org+id and resource_id*/
380 CURSOR Cur_check_dup IS
381 SELECT COUNT(1)
382 FROM cr_rsrc_dtl
383 WHERE organization_id = p_organization_id
384 AND resource_id = p_resource_id;
385
386 /* Bug 13588606 - TST122:ABLE TO CREATE DUPLICATE PLANT RESOURCE USING GMP_RESOURCE_DTL_PUB API */
387 CURSOR Cur_check_dup_rsc IS
388 SELECT COUNT(1)
389 FROM cr_rsrc_dtl
390 WHERE organization_id = p_organization_id
391 AND resources = p_resources;
392
393 /* B4724360 - INVCONV */
394 CURSOR Cur_usage_um IS
395 SELECT COUNT(1)
396 FROM mtl_units_of_measure
397 WHERE uom_code = p_usage_um;
398
399 /* B4724360 - INVCONV - changed the source of uom*/
400 CURSOR Cur_capacity_uom IS
401 SELECT COUNT(1)
402 FROM mtl_units_of_measure
403 WHERE uom_code = p_capacity_uom;
404
405 CURSOR Cur_calendar_code IS
406 SELECT COUNT(1)
407 FROM bom_calendars
408 WHERE calendar_code = p_calendar_code;
409
410 l_return_val varchar2(16);
411 X_count number;
412 l_count1 number;
413 l_count2 number;
414 l_count3 number;
415 l_count4 number;
416 l_count5 number;
417 x_temp number;
418 X_field varchar2(2000);
419 X_value varchar2(2000);
420 X_msg varchar2(2000);
421
422 INVALID_ORGN_CODE EXCEPTION;
423 CR_POSITIVE EXCEPTION;
424 INVALID_USAGE_UM EXCEPTION;
425 INVALID_CAPACITY_UOM EXCEPTION;
426 INVALID_UOM_TYPE EXCEPTION;
427 QC_MIN_MAX_SPEC EXCEPTION;
428 CAPACITY_NOT_IN_RANGE EXCEPTION;
429 INVALID_VALUE EXCEPTION;
430 BAD_RESOURCE EXCEPTION;
431 DUPLICATE_RECORD EXCEPTION;
432 RESOURCE_REQUIRED EXCEPTION;
433 MIN_MAX_CAPACITY_REQUIRED EXCEPTION;
434 ASSIGNED_QTY_REQUIRED EXCEPTION;
435
436 BEGIN
437
438 X_count := 0;
439 l_count1 := 0;
440 l_count2 := 0;
441 l_count3 := 0;
442 l_count4 := 0;
443 l_count5 := 0;
444
445 X_field := '';
446 X_value := '';
447 X_msg := '';
448
449 /* Validate Orgn_code and the Resources if they already exist */
450
451 x_return_status := 'S' ;
452 OPEN Cur_orgn_code;
453 FETCH Cur_orgn_code INTO X_count;
454 CLOSE Cur_orgn_code;
455 IF (X_count = 0) THEN
456 x_return_status := 'E';
457 RAISE INVALID_ORGN_CODE;
458 END IF;
459
460 IF (p_resources IS NOT NULL) THEN
461 OPEN Cur_resources;
462 FETCH Cur_resources INTO l_count1;
463 CLOSE Cur_resources;
464 IF l_count1 = 0 then
465 x_return_status := 'E';
466 RAISE BAD_RESOURCE;
467 END IF;
468 END IF;
469
470 IF p_resources IS NULL THEN
471 x_return_status := 'E';
472 X_msg := 'Resources';
473 RAISE RESOURCE_REQUIRED;
474 ELSE
475 IF v_update_flag <> 'Y' THEN
476
477 OPEN Cur_check_dup;
478 FETCH Cur_check_dup INTO l_count2;
479 CLOSE Cur_check_dup;
480 IF (l_count2 >0) THEN
481 x_return_status := 'E';
482 RAISE DUPLICATE_RECORD;
483 END IF;
484
485 l_count2 := 0 ;
486 OPEN Cur_check_dup_rsc;
487 FETCH Cur_check_dup_rsc INTO l_count2;
488 CLOSE Cur_check_dup_rsc;
489 IF (l_count2 >0) THEN
490 x_return_status := 'E';
491 RAISE DUPLICATE_RECORD;
492 END IF;
493
494 END IF;
495 END IF;
496
497
498 /* Validate Assigned Qty */
499 IF p_assigned_qty <= 0 THEN
500 x_return_status := 'E';
501 X_field := 'Assigned Qty';
502 X_value := p_assigned_qty;
503 RAISE INVALID_VALUE;
504 END IF;
505
506 IF (p_schedule_ind = 2) AND (v_update_flag = 'Y') THEN
507 NULL;
508 ELSIF p_assigned_qty IS NULL THEN
509 x_return_status := 'E';
510 X_msg := 'Assigned Qty';
511 RAISE ASSIGNED_QTY_REQUIRED;
512 END IF;
513
514 /* Validate Daily Avail Use */
515 IF p_daily_avl_use < 0 THEN
516 x_return_status := 'E';
517 RAISE CR_POSITIVE;
518 END IF;
519
520 /* Check Usage_um if they already exist */
521
522 IF (p_usage_um is NOT NULL ) then
523 x_return_status := 'S';
524 OPEN Cur_usage_um;
525 FETCH Cur_usage_um INTO l_count2;
526 CLOSE Cur_usage_um;
527
528 IF l_count2 = 0 then
529 x_return_status := 'E';
530 RAISE INVALID_USAGE_UM;
531 END IF;
532 END IF; /* End if for usage_um */
533
534 /* Check Capacity UOM if they already exist */
535
536 IF (p_capacity_uom is NOT NULL) then
537 x_return_status := 'S';
538 OPEN Cur_capacity_uom;
539 FETCH Cur_capacity_uom INTO l_count3;
540 CLOSE Cur_capacity_uom;
541
542 IF l_count3 = 0 then
543 x_return_status := 'E';
544 RAISE INVALID_CAPACITY_UOM;
545 END IF;
546 END IF; /* End if for capacity_uom */
547
548
549 IF p_nominal_cost < 0 THEN
550 x_return_status := 'E';
551 X_field := 'NominalCost';
552 X_value := p_nominal_cost;
553 RAISE INVALID_VALUE;
554 END IF;
555
556 IF p_inactive_ind NOT IN (0,1)
557 THEN
558 x_return_status := 'E';
559 X_field := 'Inactive Indicator';
560 X_value := p_inactive_ind;
561 RAISE INVALID_VALUE;
562 END IF;
563
564 /* Check if Min Capacity is < 0 */
565
566 IF (p_min_capacity < 0) THEN
567 x_return_status := 'E';
568 X_field := 'Minimum Capacity';
569 X_value := p_min_capacity;
570 RAISE INVALID_VALUE;
571 END IF;
572
573 /* Check if Min Capacity is greater than Max Capacity */
574 IF (nvl(p_min_capacity,0) > nvl(p_max_capacity,999999.99)) THEN
575 x_return_status := 'E';
576 RAISE QC_MIN_MAX_SPEC;
577 END IF;
578
579 IF (p_max_capacity < 0) THEN
580 x_return_status := 'E';
581 X_field := 'Maximum Capacity';
582 X_value := p_max_capacity;
583 RAISE INVALID_VALUE;
584 END IF;
585
586 /* Check if Ideal Capacity falls in the range */
587 IF (p_ideal_capacity > p_max_capacity) OR
588 (p_ideal_capacity < p_min_capacity) THEN
589 x_return_status := 'E';
590 RAISE CAPACITY_NOT_IN_RANGE;
591 END IF;
592
593 /* Check if Capacity Constraint has valid values - 0 or 1 */
594 IF p_capacity_constraint NOT IN (0,1)
595 THEN
596 x_return_status := 'E';
597 X_field := 'Capacity Constraint';
598 X_value := p_capacity_constraint;
599 RAISE INVALID_VALUE;
600 END IF ;
601
602 /* Check if Capacity Constraint = 1,then
603 Min,Max,Capacity Uom is required
604 */
605 IF (p_capacity_constraint = 1)
606 THEN
607 IF (p_min_capacity is NULL) OR
608 (p_max_capacity is NULL) OR (p_capacity_uom is NULL)
609 THEN
610 x_return_status := 'E';
611 X_msg := 'Min/Max/Capacity Uom';
612 RAISE MIN_MAX_CAPACITY_REQUIRED;
613 END IF ;
614 END IF ;
615
616 /* Check if Schedule Ind has Valid Values 0,1,2 */
617 /* HALUTHRA Bug:7637373 DEC 12, Including schedule_ind = 3 in the IF condition so that resource with DO NOT PLAN type schedule indicator can be included */
618
619 IF p_schedule_ind NOT IN (0,1,2,3)
620 THEN
621 x_return_status := 'E';
622 X_field := 'Schedule Indicator';
623 X_value := p_schedule_ind;
624 RAISE INVALID_VALUE;
625 END IF;
626
627 /* B4724360 - INVCONV */
628 IF (p_calendar_code IS NOT NULL) THEN
629 OPEN Cur_calendar_code;
630 FETCH Cur_calendar_code INTO l_count5;
631 CLOSE Cur_calendar_code;
632 IF (l_count5 = 0) THEN
633 x_return_status := 'E';
634 X_field := 'Calendar Code';
635 RAISE INVALID_VALUE;
636 END IF;
637 END IF;
638
639 /* BUG 4157063 Check Resource batching */
640 IF nvl(p_batchable_flag,0) NOT IN (0,1) THEN
641 x_return_status := 'E';
642 X_field := 'Batchable Flag';
643 X_value := p_batchable_flag;
644 RAISE INVALID_VALUE;
645 ELSE
646 IF nvl(p_batchable_flag,0) = 1 THEN
647 IF (p_batch_window IS NULL) THEN
648 x_return_status := 'E';
649 X_field := 'Batch window';
650 RAISE INVALID_VALUE;
651 END IF;
652 ELSE
653 IF (p_batch_window IS NOT NULL) THEN
654 x_return_status := 'E';
655 X_field := 'Batch window';
656 RAISE INVALID_VALUE;
657 END IF;
658 END IF;
659 END IF;
660
661 EXCEPTION
662 WHEN INVALID_VALUE THEN
663 x_return_status := FND_API.G_RET_STS_ERROR;
664 FND_MESSAGE.SET_NAME('GMP','GMP_INVALID_VALUE');
665 FND_MESSAGE.SET_TOKEN('FIELD',X_field);
666 FND_MESSAGE.SET_TOKEN('VALUE',X_value);
667 FND_MSG_PUB.ADD;
668 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
669
670 WHEN INVALID_ORGN_CODE THEN
671 x_return_status := FND_API.G_RET_STS_ERROR;
672 FND_MESSAGE.SET_NAME('GMI','IC_ORGNCODERR');
673 FND_MSG_PUB.ADD;
674 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
675
676 WHEN BAD_RESOURCE THEN
677 x_return_status := FND_API.G_RET_STS_ERROR;
678 FND_MESSAGE.SET_NAME('GMP','BAD_RESOURCE');
679 FND_MSG_PUB.ADD;
680 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
681
682 WHEN CR_POSITIVE THEN
683 x_return_status := FND_API.G_RET_STS_ERROR;
684 FND_MESSAGE.SET_NAME('GMP','CR_POSITIVE');
685 fnd_file.put_line(fnd_file.log,'Qty Entered is < 0');
686 FND_MSG_PUB.ADD;
687 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
688
689 WHEN MIN_MAX_CAPACITY_REQUIRED THEN
690 x_return_status := FND_API.G_RET_STS_ERROR;
691 FND_MESSAGE.SET_NAME('GMA','SY_REQUIRED');
692 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
693 FND_MSG_PUB.ADD;
694 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
695
696 WHEN ASSIGNED_QTY_REQUIRED THEN
697 x_return_status := FND_API.G_RET_STS_ERROR;
698 FND_MESSAGE.SET_NAME('GMA','SY_REQUIRED');
699 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
700 FND_MSG_PUB.ADD;
701 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
702
703 WHEN INVALID_USAGE_UM THEN
704 x_return_status := FND_API.G_RET_STS_ERROR;
705 FND_MESSAGE.SET_NAME('GMA','SY_INVALID_UM_CODE');
706 FND_MSG_PUB.ADD;
707 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
708
709 WHEN INVALID_CAPACITY_UOM THEN
710 x_return_status := FND_API.G_RET_STS_ERROR;
711 FND_MESSAGE.SET_NAME('GMA','SY_INVALID_UM_CODE');
712 FND_MSG_PUB.ADD;
713 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
714
715 WHEN INVALID_UOM_TYPE THEN
716 x_return_status := FND_API.G_RET_STS_ERROR;
717 FND_MESSAGE.SET_NAME('GMA','SY_INVALID_UM_CODE');
718 FND_MSG_PUB.ADD;
719 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
720
721 WHEN CAPACITY_NOT_IN_RANGE THEN
722 x_return_status := FND_API.G_RET_STS_ERROR;
723 FND_MESSAGE.SET_NAME('GMP','CR_CAPACITY_NOT_IN_RANGE');
724 FND_MSG_PUB.ADD;
725 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
726
727 WHEN QC_MIN_MAX_SPEC THEN
728 x_return_status := FND_API.G_RET_STS_ERROR;
729 FND_MESSAGE.SET_NAME('GMD','QC_MIN_MAX_SPEC');
730 FND_MSG_PUB.ADD;
731 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
732
733 WHEN RESOURCE_REQUIRED THEN
734 x_return_status := FND_API.G_RET_STS_ERROR;
735 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
736 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
737 FND_MSG_PUB.ADD;
738 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
739
740 WHEN DUPLICATE_RECORD THEN
741 x_return_status := FND_API.G_RET_STS_ERROR;
742 FND_MESSAGE.SET_NAME('GMP', 'PS_DUP_REC');
743 FND_MSG_PUB.ADD;
744 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
745
746 END check_data;
747
748 /* =============================================================== */
749 /* Procedure: */
750 /* insert_detail_rows */
751 /* */
752 /* DESCRIPTION: */
753 /* */
754 /* The following Procedure checks the Record and then Inserts */
755 /* the row into cr_rsrc_dtl table and Returns S code if inserted */
756 /* Successfully */
757 /* */
758 /* History : */
759 /* Sgidugu 09/10/2002 Initial implementation */
760 /* Kaushek B 13/09/07 B6413873 Added parameter */
761 /* planning_exception_set and insert value of planning_exception_set*/
762 /* =============================================================== */
763 PROCEDURE insert_detail_rows
764 (
765 p_organization_id IN NUMBER, /* B4724360 - INVCONV */
766 p_resources IN varchar2,
767 p_group_resource IN VARCHAR2,
768 p_assigned_qty IN NUMBER,
769 p_daily_avail_use IN NUMBER,
770 p_usage_um IN VARCHAR2,
771 p_nominal_cost IN NUMBER,
772 p_inactive_ind IN NUMBER,
773 p_creation_date IN DATE,
774 p_created_by IN NUMBER,
775 p_last_update_date IN DATE,
776 p_last_updated_by IN NUMBER,
777 p_last_update_login IN NUMBER,
778 p_trans_cnt IN NUMBER,
779 p_delete_mark IN NUMBER,
780 p_text_code IN NUMBER,
781 p_ideal_capacity IN NUMBER,
782 p_min_capacity IN NUMBER,
783 p_max_capacity IN NUMBER,
784 p_capacity_uom IN VARCHAR2,
785 p_resource_id IN NUMBER,
786 p_capacity_constraint IN NUMBER,
787 p_capacity_tolerance IN NUMBER,
788 p_schedule_ind IN NUMBER,
789 p_utilization IN NUMBER,
790 p_efficiency IN NUMBER,
791 p_planning_exception_set IN VARCHAR2, /* Bug # 6413873 */
792 p_calendar_code IN VARCHAR2, /* B4724360 - INVCONV */
793 p_sds_window IN NUMBER, /* B7637373 - VPEDARLA */
794 p_batchable_flag IN NUMBER, /* BUG 4157063 */
795 p_batch_window IN NUMBER /* BUG 4157063 */
796 ) IS
797
798 BEGIN
799
800 INSERT INTO CR_RSRC_DTL(
801 ORGANIZATION_ID /* B4724360 - INVCONV */
802 ,RESOURCES
803 ,GROUP_RESOURCE
804 ,ASSIGNED_QTY
805 ,DAILY_AVAIL_USE
806 ,USAGE_UOM /* B4724360 - INVCONV */
807 ,NOMINAL_COST
808 ,INACTIVE_IND
809 ,CREATION_DATE
810 ,CREATED_BY
811 ,LAST_UPDATE_DATE
812 ,LAST_UPDATED_BY
813 ,LAST_UPDATE_LOGIN
814 ,TRANS_CNT
815 ,DELETE_MARK
816 ,TEXT_CODE
817 ,IDEAL_CAPACITY
818 ,MIN_CAPACITY
819 ,MAX_CAPACITY
820 ,CAPACITY_UM /* B4724360 - INVCONV */
821 ,RESOURCE_ID
822 ,CAPACITY_CONSTRAINT
823 ,CAPACITY_TOLERANCE
824 ,SCHEDULE_IND
825 ,UTILIZATION
826 ,EFFICIENCY
827 ,PLANNING_EXCEPTION_SET /* Bug # 6413873 */
828 ,CALENDAR_CODE /* B4724360 - INVCONV */
829 ,BATCHABLE_FLAG /* BUG 4157063 */
830 ,BATCH_WINDOW /* BUG 4157063 */
831 ,SDS_WINDOW /* B7637373 - VPEDARLA */
832 )
833 values (
834 p_organization_id , /* B4724360 - INVCONV */
835 p_resources ,
836 nvl(p_group_resource,p_resources),
837 p_assigned_qty ,
838 p_daily_avail_use,
839 p_usage_um ,
840 p_nominal_cost ,
841 p_inactive_ind ,
842 p_creation_date ,
843 p_created_by ,
844 p_last_update_date ,
845 p_last_updated_by ,
846 p_last_update_login ,
847 p_trans_cnt ,
848 p_delete_mark ,
849 p_text_code ,
850 p_ideal_capacity ,
851 p_min_capacity ,
852 p_max_capacity ,
853 p_capacity_uom ,
854 p_resource_id ,
855 p_capacity_constraint ,
856 p_capacity_tolerance ,
857 p_schedule_ind ,
858 p_utilization ,
859 p_efficiency ,
860 p_planning_exception_set, /* Bug # 6413873 */
861 p_calendar_code ,/* B4724360 - INVCONV */
862 p_batchable_flag , /* BUG 4157063 */
863 p_batch_window , /* BUG 4157063 */
864 p_sds_window /* B7637373 - VPEDARLA */
865 );
866
867 END insert_detail_rows;
868
869 /* =============================================================== */
870 /* Procedure: */
871 /* insert_resource_instance */
872 /* */
873 /* DESCRIPTION: */
874 /* */
875 /* The following Procedure checks the Record and then Inserts */
876 /* the row into cr_rsrc_dtl table and Returns S code if inserted */
877 /* Successfully */
878 /* */
879 /* History : */
880 /* Sgidugu 09/10/2002 Initial implementation */
881 /* =============================================================== */
882 PROCEDURE insert_resource_instance (
883 p_resource_id IN NUMBER
884 ,p_instance_id IN NUMBER
885 ,p_instance_number IN NUMBER
886 ,p_vendor_id IN NUMBER
887 ,p_model_number IN VARCHAR2
888 ,p_serial_number IN VARCHAR2
889 ,p_tracking_number IN VARCHAR2
890 ,p_eff_start_date IN DATE
891 ,p_eff_end_date IN DATE
892 ,p_last_maintenance_date IN DATE
893 ,p_maintenance_interval IN NUMBER
894 ,p_inactive_ind IN NUMBER
895 ,p_calibration_frequency IN NUMBER
896 ,p_calibration_period IN VARCHAR2
897 ,p_calibration_item_id IN NUMBER
898 ,p_last_calibration_date IN DATE
899 ,p_next_calibration_date IN DATE
900 ,p_last_certification_date IN DATE
901 ,p_certified_by IN VARCHAR2
902 ,p_creation_date IN DATE
903 ,p_created_by IN NUMBER
904 ,p_last_update_date IN DATE
905 ,p_last_updated_by IN NUMBER
906 ,p_last_update_login IN NUMBER ) IS
907
908 BEGIN
909
910 INSERT INTO gmp_resource_instances
911 (
912 RESOURCE_ID
913 ,INSTANCE_ID
914 ,INSTANCE_NUMBER
915 ,VENDOR_ID
916 ,MODEL_NUMBER
917 ,SERIAL_NUMBER
918 ,TRACKING_NUMBER
919 ,EFF_START_DATE
920 ,EFF_END_DATE
921 ,LAST_MAINTENANCE_DATE
922 ,MAINTENANCE_INTERVAL
923 ,INACTIVE_IND
924 ,CALIBRATION_FREQUENCY
925 ,CALIBRATION_PERIOD
926 ,CALIBRATION_ITEM_ID
927 ,LAST_CALIBRATION_DATE
928 ,NEXT_CALIBRATION_DATE
929 ,LAST_CERTIFICATION_DATE
930 ,CERTIFIED_BY
931 ,CREATION_DATE
932 ,CREATED_BY
933 ,LAST_UPDATE_DATE
934 ,LAST_UPDATED_BY
935 ,LAST_UPDATE_LOGIN
936 )
937 values (
938 p_resource_id
939 ,p_instance_id
940 ,p_instance_number
941 ,p_vendor_id
942 ,p_model_number
943 ,p_serial_number
944 ,p_tracking_number
945 ,p_eff_start_date
946 ,p_eff_end_date
947 ,p_last_maintenance_date
948 ,p_maintenance_interval
949 ,p_inactive_ind
950 ,p_calibration_frequency
951 ,p_calibration_period
952 ,p_calibration_item_id
953 ,p_last_calibration_date
954 ,p_next_calibration_date
955 ,p_last_certification_date
956 ,p_certified_by
957 ,p_creation_date
958 ,p_created_by
959 ,p_last_update_date
960 ,p_last_updated_by
961 ,p_last_update_login
962 ) ;
963
964 END insert_resource_instance ;
965
966 /* =============================================================== */
967 /* Procedure: */
968 /* update_resource_dtl */
969 /* */
970 /* DESCRIPTION: */
971 /* */
972 /* API returns (x_return_code) = 'S' if the update into Generic */
973 /* Resource Table */
974 /* */
975 /* History : */
976 /* Sgidugu 09/04/2002 Initial implementation */
977 /* =============================================================== */
978 PROCEDURE update_resource_dtl
979 ( p_api_version IN NUMBER := 1
980 , p_init_msg_list IN BOOLEAN := TRUE
981 , p_commit IN BOOLEAN := FALSE
982 , p_resources IN cr_rsrc_dtl%ROWTYPE
983 , x_message_count OUT NOCOPY NUMBER
984 , x_message_list OUT NOCOPY VARCHAR2
985 , x_return_status OUT NOCOPY VARCHAR2
986 ) IS
987
988 /* Local variable section */
989 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_DTL';
990 l_return_status VARCHAR2(1) ;
991 g_return_status VARCHAR2(1) ;
992 X_msg varchar2(2000);
993
994 /* Define Exceptions */
995 resource_update_failure EXCEPTION;
996 invalid_version EXCEPTION;
997 RESOURCES_REQUIRED EXCEPTION;
998 RESOURCE_ID_REQUIRED EXCEPTION;
999
1000
1001 BEGIN
1002 l_return_status := FND_API.G_RET_STS_SUCCESS;
1003 g_return_status := FND_API.G_RET_STS_SUCCESS;
1004 X_msg := '';
1005
1006 SAVEPOINT update_resource_dtl;
1007 fnd_file.put_line(fnd_file.log,'UpdateResourcePub');
1008
1009 /* Set the return status to success initially */
1010 x_return_status := FND_API.G_RET_STS_SUCCESS;
1011
1012 /* Initialize message list and count if needed */
1013 IF p_init_msg_list THEN
1014 fnd_msg_pub.initialize;
1015 END IF;
1016
1017 /* Make sure we are call compatible */
1018 IF NOT FND_API.compatible_api_call ( GMP_RESOURCE_DTL_PUB.m_api_version
1019 ,p_api_version
1020 ,l_api_name
1021 ,GMP_RESOURCE_DTL_PUB.m_pkg_name) THEN
1022 x_return_status := FND_API.G_RET_STS_ERROR;
1023 RAISE invalid_version;
1024 END IF;
1025
1026 /* Set the Update Flag to Yes */
1027 v_update_flag := 'Y';
1028
1029 IF (p_resources.resource_id IS NULL) THEN
1030 IF ((p_resources.resources IS NULL) AND
1031 (p_resources.organization_id IS NULL)) THEN
1032 x_return_status := 'E';
1033 X_msg := 'Resources and orgn_code OR Resource_id ';
1034 RAISE resources_required;
1035 ELSE
1036 x_return_status := 'S';
1037 END IF ;
1038 ELSE
1039 x_return_status := 'S';
1040 END IF ;
1041
1042 check_data(
1043 p_resources.organization_id, /* B4724360 - INVCONV */
1044 p_resources.resources,
1045 p_resources.resource_id, /* B4724360 - INVCONV */
1046 p_resources.group_resource,
1047 p_resources.assigned_qty,
1048 p_resources.daily_avail_use,
1049 p_resources.usage_uom, /* B4724360 - INVCONV */
1050 p_resources.nominal_cost,
1051 p_resources.inactive_ind,
1052 p_resources.ideal_capacity,
1053 p_resources.min_capacity,
1054 p_resources.max_capacity,
1055 p_resources.capacity_um, /* B4724360 - INVCONV */
1056 p_resources.capacity_constraint,
1057 p_resources.capacity_tolerance,
1058 p_resources.schedule_ind,
1059 p_resources.utilization,
1060 p_resources.efficiency,
1061 p_resources.calendar_code, /* B4724360 - INVCONV */
1062 nvl(p_resources.batchable_flag,0), /* BUG 4157063 */
1063 p_resources.batch_window, /* BUG 4157063 */
1064 x_message_count,
1065 x_message_list,
1066 l_return_status);
1067
1068 x_return_status := l_return_status ;
1069
1070 IF l_return_status = 'E' THEN
1071 RAISE resource_update_failure;
1072 ELSE
1073 /* Update the Resource Data now */
1074
1075 update_detail_rows(
1076 p_resources.organization_id, /* B4724360 - INVCONV */
1077 p_resources.resources,
1078 p_resources.group_resource,
1079 p_resources.assigned_qty,
1080 p_resources.daily_avail_use,
1081 p_resources.usage_uom, /* B4724360 - INVCONV */
1082 p_resources.nominal_cost,
1083 p_resources.inactive_ind,
1084 sysdate,
1085 FND_GLOBAL.user_id, /* Bug 6412180 */
1086 sysdate,
1087 FND_GLOBAL.user_id, /* Bug 6412180 */
1088 FND_GLOBAL.user_id, /* Bug 6412180 */
1089 p_resources.trans_cnt,
1090 p_resources.delete_mark,
1091 p_resources.text_code,
1092 p_resources.ideal_capacity,
1093 p_resources.min_capacity,
1094 p_resources.max_capacity,
1095 p_resources.capacity_um, /* B4724360 - INVCONV */
1096 p_resources.resource_id,
1097 p_resources.capacity_constraint,
1098 p_resources.capacity_tolerance,
1099 p_resources.schedule_ind,
1100 p_resources.utilization,
1101 p_resources.efficiency,
1102 p_resources.sds_window, /* B7637373 - VPEDARLA */
1103 p_resources.planning_exception_set, /* Bug # 6413873 */
1104 p_resources.calendar_code, /* B4724360 - INVCONV */
1105 nvl(p_resources.batchable_flag,0), /* BUG 4157063 */
1106 p_resources.batch_window, /* BUG 4157063 */
1107 g_return_status
1108 );
1109 x_return_status := g_return_status ;
1110 END IF; /* Return status */
1111
1112 /* set the Update flag back to 'No' */
1113 v_update_flag := 'N';
1114
1115 /* Check if work was done */
1116 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1117 RAISE resource_update_failure;
1118 END IF; /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS */
1119
1120 fnd_msg_pub.count_and_get (
1121 p_count => x_message_count
1122 ,p_encoded => FND_API.g_false
1123 ,p_data => x_message_list);
1124
1125 IF x_message_count = 0 THEN
1126 fnd_file.put_line(fnd_file.log,'Resource was Updated successfullly');
1127 END IF;
1128
1129 fnd_file.put_line(fnd_file.log,'Completed '||l_api_name ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
1130
1131 EXCEPTION
1132
1133 WHEN resource_update_failure OR invalid_version THEN
1134 ROLLBACK TO SAVEPOINT update_resource_dtl;
1135 fnd_file.put_line (fnd_file.log,m_pkg_name||'.'||l_api_name||':'||'API not complete');
1136 fnd_msg_pub.count_and_get (
1137 p_count => x_message_count
1138 ,p_encoded => FND_API.g_false
1139 ,p_data => x_message_list);
1140 x_return_status := FND_API.G_RET_STS_ERROR;
1141
1142 WHEN RESOURCES_REQUIRED THEN
1143 x_return_status := 'E' ;
1144 FND_MESSAGE.SET_NAME('GMA','SY_REQUIRED');
1145 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
1146 FND_MSG_PUB.ADD;
1147 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
1148 WHEN OTHERS THEN
1149 ROLLBACK TO SAVEPOINT update_resource_dtl;
1150 fnd_file.put_line (fnd_file.log,m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
1151 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1152 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1153 FND_MSG_PUB.ADD;
1154 fnd_msg_pub.count_and_get (
1155 p_count => x_message_count
1156 ,p_encoded => FND_API.g_false
1157 ,p_data => x_message_list);
1158 x_return_status := FND_API.g_ret_sts_unexp_error;
1159 END update_resource_dtl;
1160
1161 /* =============================================================== */
1162 /* Procedure: */
1163 /* update_detail_rows */
1164 /* */
1165 /* DESCRIPTION: */
1166 /* */
1167 /* The following Procedure checks the Record and then Inserts */
1168 /* the row into cr_rsrc_mst table and Returns S code if inserted */
1169 /* Successfully */
1170 /* */
1171 /* History : */
1172 /* Sgidugu 09/10/2002 Initial implementation */
1173 /* Sgidugu 04/12/2002 Count is not Updated if Schedule Ind = 2 */
1174 /* Kaushek B 13/09/07 B6413873 Added parameter planning_exception_set */
1175 /* =============================================================== */
1176
1177 PROCEDURE update_detail_rows
1178 (
1179 p_organization_id IN NUMBER, /* B4724360 - INVCONV */
1180 p_resources IN VARCHAR2,
1181 p_group_resource IN VARCHAR2,
1182 p_assigned_qty IN NUMBER,
1183 p_daily_avail_use IN NUMBER,
1184 p_usage_um IN VARCHAR2,
1185 p_nominal_cost IN NUMBER,
1186 p_inactive_ind IN NUMBER,
1187 p_creation_date IN DATE,
1188 p_created_by IN NUMBER,
1189 p_last_update_date IN DATE,
1190 p_last_updated_by IN NUMBER,
1191 p_last_update_login IN NUMBER,
1192 p_trans_cnt IN NUMBER,
1193 p_delete_mark IN NUMBER,
1194 p_text_code IN NUMBER,
1195 p_ideal_capacity IN NUMBER,
1196 p_min_capacity IN NUMBER,
1197 p_max_capacity IN NUMBER,
1198 p_capacity_uom IN VARCHAR2,
1199 p_resource_id IN NUMBER,
1200 p_capacity_constraint IN NUMBER,
1201 p_capacity_tolerance IN NUMBER,
1202 p_schedule_ind IN NUMBER,
1203 p_utilization IN NUMBER,
1204 p_efficiency IN NUMBER,
1205 p_sds_window IN NUMBER, /* B7637373 - VPEDARLA */
1206 p_planning_exception_set IN VARCHAR2, /* Bug # 6413873 */
1207 p_calendar_code IN VARCHAR2, /* B4724360 - INVCONV */
1208 p_batchable_flag IN NUMBER, /* BUG 4157063 */
1209 p_batch_window IN NUMBER, /* BUG 4157063 */
1210 x_return_status OUT NOCOPY VARCHAR2
1211 ) IS
1212
1213 BEGIN
1214
1215 IF p_schedule_ind = 2 THEN
1216 UPDATE cr_rsrc_dtl
1217 SET group_resource = p_group_resource,
1218 daily_avail_use = p_daily_avail_use,
1219 usage_uom = p_usage_um, /* B4724360 - INVCONV */
1220 nominal_cost = p_nominal_cost,
1221 inactive_ind = p_inactive_ind,
1222 last_update_date = p_last_update_date,
1223 last_updated_by = p_last_updated_by,
1224 last_update_login = p_last_update_login,
1225 trans_cnt = p_trans_cnt,
1226 delete_mark = p_delete_mark,
1227 text_code = p_text_code,
1228 ideal_capacity = p_ideal_capacity,
1229 min_capacity = p_min_capacity,
1230 max_capacity = p_max_capacity,
1231 capacity_um = p_capacity_uom, /* B4724360 - INVCONV */
1232 capacity_constraint = p_capacity_constraint,
1233 capacity_tolerance = p_capacity_tolerance,
1234 schedule_ind = p_schedule_ind,
1235 utilization = p_utilization,
1236 efficiency = p_efficiency,
1237 sds_window = p_sds_window, /* B7637373 - VPEDARLA */
1238 planning_exception_set = p_planning_exception_set, /* Bug # 6413873 */
1239 calendar_code = p_calendar_code , /* B4724360 - INVCONV */
1240 batchable_flag = nvl(p_batchable_flag,0) , /* BUG 4157063 */
1241 batch_window = p_batch_window /* BUG 4157063 */
1242 WHERE resource_id = p_resource_id
1243 AND organization_id = p_organization_id;
1244 ELSE
1245 UPDATE cr_rsrc_dtl
1246 SET group_resource = p_group_resource,
1247 assigned_qty = p_assigned_qty,
1248 daily_avail_use = p_daily_avail_use,
1249 usage_uom = p_usage_um, /* B4724360 - INVCONV */
1250 nominal_cost = p_nominal_cost,
1251 inactive_ind = p_inactive_ind,
1252 last_update_date = p_last_update_date,
1253 last_updated_by = p_last_updated_by,
1254 last_update_login = p_last_update_login,
1255 trans_cnt = p_trans_cnt,
1256 delete_mark = p_delete_mark,
1257 text_code = p_text_code,
1258 ideal_capacity = p_ideal_capacity,
1259 min_capacity = p_min_capacity,
1260 max_capacity = p_max_capacity,
1261 capacity_uom = p_capacity_uom, /* B4724360 - INVCONV */
1262 capacity_constraint = p_capacity_constraint,
1263 capacity_tolerance = p_capacity_tolerance,
1264 schedule_ind = p_schedule_ind,
1265 utilization = p_utilization,
1266 efficiency = p_efficiency,
1267 sds_window = p_sds_window, /* B7637373 - VPEDARLA */
1268 planning_exception_set = p_planning_exception_set, /* Bug # 6413873 */
1269 calendar_code = p_calendar_code , /* B4724360 - INVCONV */
1270 batchable_flag = nvl(p_batchable_flag,0) , /* BUG 4157063 */
1271 batch_window = p_batch_window /* BUG 4157063 */
1272 WHERE resource_id = p_resource_id
1273 AND organization_id = p_organization_id;
1274 END IF ;
1275
1276 if (sql%notfound) then
1277 raise no_data_found;
1278 end if;
1279 x_return_status := 'S' ;
1280
1281 EXCEPTION
1282 WHEN no_data_found THEN
1283 ROLLBACK TO SAVEPOINT update_instances;
1284 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1285 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1286 FND_MSG_PUB.ADD;
1287 x_return_status := FND_API.g_ret_sts_unexp_error;
1288 WHEN OTHERS THEN
1289 ROLLBACK TO SAVEPOINT update_resource_dtl;
1290 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1291 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1292 FND_MSG_PUB.ADD;
1293 x_return_status := FND_API.g_ret_sts_unexp_error;
1294 END update_detail_rows;
1295
1296 /* =============================================================== */
1297 /* Procedure: */
1298 /* update_instances */
1299 /* */
1300 /* DESCRIPTION: */
1301 /* */
1302 /* API returns (x_return_code) = 'S' if the update into Generic */
1303 /* Resource Table */
1304 /* */
1305 /* History : */
1306 /* Rajesh Patangya 11/27/2002 Initial implementation */
1307 /* =============================================================== */
1308 PROCEDURE update_instances
1309 ( p_api_version IN NUMBER := 1
1310 , p_init_msg_list IN BOOLEAN := TRUE
1311 , p_commit IN BOOLEAN := FALSE
1312 , p_instances IN gmp_resource_instances%ROWTYPE
1313 , x_message_count OUT NOCOPY NUMBER
1314 , x_message_list OUT NOCOPY VARCHAR2
1315 , x_return_status OUT NOCOPY VARCHAR2
1316 ) IS
1317
1318 /* Local variable section */
1319 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_INSTANCES';
1320 l_return_status VARCHAR2(1) ;
1321 g_return_status VARCHAR2(1) ;
1322 v_eff_start_date DATE ;
1323 X_msg VARCHAR2(2000);
1324
1325 /* Define Exceptions */
1326 instance_update_failure EXCEPTION;
1327 invalid_version EXCEPTION;
1328 RESOURCES_REQUIRED EXCEPTION;
1329
1330 BEGIN
1331 l_return_status := FND_API.G_RET_STS_SUCCESS;
1332 g_return_status := FND_API.G_RET_STS_SUCCESS;
1333 v_eff_start_date := NULL ;
1334 X_msg := '';
1335
1336 SAVEPOINT update_instances;
1337 fnd_file.put_line(fnd_file.log,'UpdateInstancePub');
1338
1339 /* Set the return status to success initially */
1340 x_return_status := FND_API.G_RET_STS_SUCCESS;
1341
1342 /* Initialize message list and count if needed */
1343 IF p_init_msg_list THEN
1344 fnd_msg_pub.initialize;
1345 END IF;
1346
1347 /* Make sure we are call compatible */
1348 IF NOT FND_API.compatible_api_call ( GMP_RESOURCE_DTL_PUB.m_api_version
1349 ,p_api_version
1350 ,l_api_name
1351 ,GMP_RESOURCE_DTL_PUB.m_pkg_name) THEN
1352 x_return_status := FND_API.G_RET_STS_ERROR;
1353 RAISE invalid_version;
1354 END IF;
1355
1356 /* Set the Update Flag to Yes */
1357 v_update_flag := 'Y';
1358
1359 IF ((p_instances.resource_id IS NOT NULL) AND
1360 (p_instances.instance_id IS NOT NULL)) THEN
1361
1362 IF p_instances.eff_start_date IS NULL THEN
1363 v_eff_start_date := SYSDATE ;
1364 ELSE
1365 v_eff_start_date := p_instances.eff_start_date ;
1366 END IF;
1367
1368 check_instance_data (
1369 p_instances.resource_id
1370 ,p_instances.instance_id
1371 ,p_instances.vendor_id
1372 ,v_eff_start_date
1373 ,p_instances.eff_end_date
1374 ,p_instances.maintenance_interval
1375 ,p_instances.inactive_ind
1376 ,p_instances.calibration_frequency
1377 ,p_instances.calibration_item_id
1378 ,x_message_count
1379 ,x_message_list
1380 ,x_return_status );
1381 x_return_status := g_return_status ;
1382
1383 IF l_return_status = 'E' THEN
1384 RAISE instance_update_failure;
1385 ELSE
1386 /* Update the Instance Data now */
1387 update_instance_row(
1388 p_instances.resource_id
1389 ,p_instances.instance_id
1390 ,p_instances.instance_number
1391 ,p_instances.vendor_id
1392 ,p_instances.model_number
1393 ,p_instances.serial_number
1394 ,p_instances.tracking_number
1395 ,p_instances.eff_start_date
1396 ,p_instances.eff_end_date
1397 ,p_instances.last_maintenance_date
1398 ,p_instances.maintenance_interval
1399 ,p_instances.inactive_ind
1400 ,p_instances.calibration_frequency
1401 ,p_instances.calibration_period
1402 ,p_instances.calibration_item_id
1403 ,p_instances.last_calibration_date
1404 ,p_instances.next_calibration_date
1405 ,p_instances.last_certification_date
1406 ,p_instances.certified_by
1407 ,sysdate
1408 ,FND_GLOBAL.user_id /* Bug 6412180 */
1409 ,sysdate
1410 ,FND_GLOBAL.user_id /* Bug 6412180 */
1411 ,FND_GLOBAL.user_id /* Bug 6412180 */
1412 ,g_return_status
1413 );
1414 x_return_status := g_return_status ;
1415 END IF;
1416 ELSE
1417 x_return_status := 'E';
1418 X_msg := 'Instance_id/resource_id';
1419 RAISE resources_required;
1420 END IF; /* p_resources.resource_id IS NOT NULL */
1421
1422 /* set the Update flag back to 'No' */
1423 v_update_flag := 'N';
1424
1425 /* Check if work was done */
1426 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1427 RAISE instance_update_failure;
1428 END IF; /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS */
1429
1430 fnd_msg_pub.count_and_get (
1431 p_count => x_message_count
1432 ,p_encoded => FND_API.g_false
1433 ,p_data => x_message_list);
1434
1435 IF x_message_count = 0 THEN
1436 fnd_file.put_line(fnd_file.log,'Resource was Updated successfullly');
1437 END IF;
1438
1439 fnd_file.put_line(fnd_file.log,'Completed '||l_api_name ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
1440
1441 EXCEPTION
1442 WHEN instance_update_failure OR invalid_version THEN
1443 ROLLBACK TO SAVEPOINT update_instances;
1444 fnd_file.put_line (fnd_file.log,m_pkg_name||'.'||l_api_name||':'||'API not complete');
1445 fnd_msg_pub.count_and_get (
1446 p_count => x_message_count
1447 ,p_encoded => FND_API.g_false
1448 ,p_data => x_message_list);
1449 x_return_status := FND_API.G_RET_STS_ERROR;
1450 WHEN RESOURCES_REQUIRED THEN
1451 FND_MESSAGE.SET_NAME('GMA','SY_REQUIRED');
1452 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
1453 FND_MSG_PUB.ADD;
1454 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
1455 WHEN OTHERS THEN
1456 ROLLBACK TO SAVEPOINT update_instances;
1457 fnd_file.put_line (fnd_file.log,m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
1458 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1459 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1460 FND_MSG_PUB.ADD;
1461 fnd_msg_pub.count_and_get (
1462 p_count => x_message_count
1463 ,p_encoded => FND_API.g_false
1464 ,p_data => x_message_list);
1465 x_return_status := FND_API.g_ret_sts_unexp_error;
1466 END update_instances;
1467
1468
1469 /* =============================================================== */
1470 /* Procedure: */
1471 /* update_instance_row */
1472 /* */
1473 /* DESCRIPTION: */
1474 /* */
1475 /* The following Procedure Updates the information in */
1476 /* gmp_resource_instances table and Returns S code if updated */
1477 /* Successfully */
1478 /* */
1479 /* History : */
1480 /* Sgidugu 09/10/2002 Initial implementation */
1481 /* =============================================================== */
1482 PROCEDURE update_instance_row
1483 (
1484 p_resource_id IN NUMBER
1485 ,p_instance_id IN NUMBER
1486 ,p_instance_number IN NUMBER
1487 ,p_vendor_id IN NUMBER
1488 ,p_model_number IN VARCHAR2
1489 ,p_serial_number IN VARCHAR2
1490 ,p_tracking_number IN VARCHAR2
1491 ,p_eff_start_date IN DATE
1492 ,p_eff_end_date IN DATE
1493 ,p_last_maintenance_date IN DATE
1494 ,p_maintenance_interval IN NUMBER
1495 ,p_inactive_ind IN NUMBER
1496 ,p_calibration_frequency IN NUMBER
1497 ,p_calibration_period IN VARCHAR2
1498 ,p_calibration_item_id IN NUMBER
1499 ,p_last_calibration_date IN DATE
1500 ,p_next_calibration_date IN DATE
1501 ,p_last_certification_date IN DATE
1502 ,p_certified_by IN VARCHAR2
1503 ,p_creation_date IN DATE
1504 ,p_created_by IN NUMBER
1505 ,p_last_update_date IN DATE
1506 ,p_last_updated_by IN NUMBER
1507 ,p_last_update_login IN NUMBER
1508 ,x_return_status OUT NOCOPY VARCHAR2
1509 ) IS
1510
1511 BEGIN
1512
1513 UPDATE gmp_resource_instances set
1514 vendor_id = p_vendor_id
1515 ,model_number = p_model_number
1516 ,serial_number = p_serial_number
1517 ,tracking_number = p_tracking_number
1518 ,eff_start_date = p_eff_start_date
1519 ,eff_end_date = p_eff_end_date
1520 ,last_maintenance_date = p_last_maintenance_date
1521 ,maintenance_interval = p_maintenance_interval
1522 ,inactive_ind = p_inactive_ind
1523 ,calibration_frequency = p_calibration_frequency
1524 ,calibration_period = p_calibration_period
1525 ,calibration_item_id = p_calibration_item_id
1526 ,last_calibration_date = p_last_calibration_date
1527 ,next_calibration_date = p_next_calibration_date
1528 ,last_certification_date = p_last_certification_date
1529 ,certified_by = p_certified_by
1530 ,creation_date = p_creation_date
1531 ,created_by = p_created_by
1532 ,last_update_date = p_last_update_date
1533 ,last_updated_by = p_last_updated_by
1534 ,last_update_login = p_last_update_login
1535 WHERE resource_id = p_resource_id
1536 AND instance_id = p_instance_id ;
1537
1538 If (sql%notfound) THEN
1539 raise no_data_found;
1540 END IF;
1541 x_return_status := 'S' ;
1542
1543 EXCEPTION
1544 WHEN no_data_found THEN
1545 ROLLBACK TO SAVEPOINT update_instances;
1546 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1547 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1548 FND_MSG_PUB.ADD;
1549 x_return_status := FND_API.g_ret_sts_unexp_error;
1550 WHEN OTHERS THEN
1551 ROLLBACK TO SAVEPOINT update_instances;
1552 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1553 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1554 FND_MSG_PUB.ADD;
1555 x_return_status := FND_API.g_ret_sts_unexp_error;
1556 END update_instance_row ;
1557
1558 /* =============================================================== */
1559 /* Procedure: */
1560 /* delete_resources */
1561 /* */
1562 /* DESCRIPTION: */
1563 /* */
1564 /* API returns (x_return_code) = 'S' if the delete Resources */
1565 /* was Successful */
1566 /* */
1567 /* History : */
1568 /* Sgidugu 09/04/2002 Initial implementation */
1569 /* =============================================================== */
1570 PROCEDURE delete_resources
1571 ( p_api_version IN NUMBER := 1
1572 , p_init_msg_list IN BOOLEAN := TRUE
1573 , p_commit IN BOOLEAN := FALSE
1574 , p_organization_id IN cr_rsrc_dtl.organization_id%TYPE
1575 , p_resources IN cr_rsrc_dtl.resources%TYPE
1576 , x_message_count OUT NOCOPY NUMBER
1577 , x_message_list OUT NOCOPY VARCHAR2
1578 , x_return_status OUT NOCOPY VARCHAR2
1579 ) IS
1580
1581 /* Local variable section */
1582 l_counter number ;
1583 v_resource_id number ;
1584 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCES';
1585 l_return_status VARCHAR2(1) ;
1586
1587 /* Define Exceptions */
1588 resource_delete_failure EXCEPTION;
1589 invalid_version EXCEPTION;
1590
1591
1592 CURSOR Cur_resource_id IS
1593 SELECT resource_id
1594 FROM cr_rsrc_dtl
1595 WHERE organization_id = p_organization_id
1596 AND resources = p_resources;
1597
1598 BEGIN
1599
1600 l_counter := 0;
1601 v_resource_id := 0;
1602 l_return_status := FND_API.G_RET_STS_SUCCESS;
1603
1604 SAVEPOINT delete_resources;
1605 fnd_file.put_line(fnd_file.log,'DeleteResourcePub');
1606
1607 /* Set the return status to success initially */
1608 x_return_status := FND_API.G_RET_STS_SUCCESS;
1609
1610 /* Initialize message list and count if needed */
1611 IF p_init_msg_list THEN
1612 fnd_msg_pub.initialize;
1613 END IF;
1614
1615 /* Make sure we are call compatible */
1616 IF NOT FND_API.compatible_api_call ( GMP_RESOURCE_DTL_PUB.m_api_version
1617 ,p_api_version
1618 ,l_api_name
1619 ,GMP_RESOURCE_DTL_PUB.m_pkg_name) THEN
1620 x_return_status := FND_API.G_RET_STS_ERROR;
1621 RAISE invalid_version;
1622 END IF;
1623
1624 OPEN Cur_resource_id;
1625 FETCH Cur_resource_id INTO v_resource_id;
1626 CLOSE Cur_resource_id;
1627
1628 IF (v_resource_id = 0 ) then
1629 l_return_status := 'E';
1630 GMD_DEBUG.PUT_LINE('Resource to be deleted Does Not Exist ');
1631 FND_MSG_PUB.ADD;
1632 RAISE resource_delete_failure;
1633 ELSE
1634 -- Added code Rajesh Patangya
1635 -- Resource exception
1636
1637 delete from gmp_rsrc_excp_asnmt
1638 WHERE resource_id = v_resource_id ;
1639
1640 -- Resource unavailable time
1641 delete from gmp_rsrc_unavail_man
1642 WHERE resource_id = v_resource_id ;
1643
1644 -- Resource instances
1645 delete from gmp_resource_instances
1646 where resource_id = v_resource_id;
1647
1648 -- Resource available
1649 delete from gmp_resource_avail
1650 where resource_id = v_resource_id
1651 and organization_id = p_organization_id
1652 and RESOURCE_INSTANCE_ID IS NOT NULL ;
1653
1654 -- Resource details
1655 delete from cr_rsrc_dtl
1656 where resource_id = v_resource_id
1657 and organization_id = p_organization_id;
1658
1659 END IF;
1660
1661 fnd_msg_pub.count_and_get (
1662 p_count => x_message_count
1663 ,p_encoded => FND_API.g_false
1664 ,p_data => x_message_list);
1665
1666 IF x_message_count = 0 THEN
1667 fnd_file.put_line(fnd_file.log,'Resource was deleted successfully');
1668 END IF;
1669
1670 fnd_file.put_line(fnd_file.log,'Completed '||l_api_name ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
1671
1672 EXCEPTION
1673 WHEN resource_delete_failure OR invalid_version THEN
1674 ROLLBACK TO SAVEPOINT delete_resources;
1675 fnd_file.put_line(fnd_file.log,m_pkg_name||'.'||l_api_name||':'||'API not complete');
1676 fnd_msg_pub.count_and_get (
1677 p_count => x_message_count
1678 ,p_encoded => FND_API.g_false
1679 ,p_data => x_message_list);
1680 x_return_status := FND_API.G_RET_STS_ERROR;
1681 WHEN OTHERS THEN
1682 ROLLBACK TO SAVEPOINT delete_resources;
1683 fnd_file.put_line(fnd_file.log,m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
1684 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1685 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1686 FND_MSG_PUB.ADD;
1687 fnd_msg_pub.count_and_get (
1688 p_count => x_message_count
1689 ,p_encoded => FND_API.g_false
1690 ,p_data => x_message_list);
1691 x_return_status := FND_API.g_ret_sts_unexp_error;
1692 END delete_resources;
1693
1694 /* =============================================================== */
1695 /* Procedure: */
1696 /* check_instance_data */
1697 /* */
1698 /* DESCRIPTION: */
1699 /* */
1700 /* The following Procedure checks the Record and then Inserts */
1701 /* the row into cr_rsrc_mst table and Returns S code if inserted */
1702 /* Successfully */
1703 /* */
1704 /* History : */
1705 /* Sgidugu 09/10/2002 Initial implementation */
1706 /* =============================================================== */
1707 PROCEDURE check_instance_data
1708 (
1709 p_resource_id IN NUMBER
1710 ,p_instance_id IN NUMBER
1711 ,p_vendor_id IN NUMBER
1712 ,p_eff_start_date IN DATE
1713 ,p_eff_end_date IN DATE
1714 ,p_maintenance_interval IN NUMBER
1715 ,p_inactive_ind IN NUMBER
1716 ,p_calibration_frequency IN NUMBER
1717 ,p_calibration_item_id IN NUMBER
1718 ,x_message_count OUT NOCOPY NUMBER
1719 ,x_message_list OUT NOCOPY VARCHAR2
1720 ,x_return_status OUT NOCOPY VARCHAR2
1721 ) IS
1722
1723 CURSOR Cur_check_dup IS
1724 SELECT COUNT(1)
1725 FROM gmp_resource_instances
1726 where instance_id = p_instance_id
1727 and resource_id = p_resource_id ;
1728
1729 CURSOR Cur_vendor_id IS
1730 SELECT COUNT(1)
1731 FROM po_vendors
1732 where vendor_id = p_vendor_id
1733 and enabled_flag = 'Y' ;
1734
1735 CURSOR Cur_check_item IS
1736 SELECT COUNT(1)
1737 FROM ic_item_mst
1738 where item_id = p_calibration_item_id
1739 and delete_mark = 0 ;
1740
1741 INVALID_VALUE EXCEPTION;
1742 BAD_RESOURCE EXCEPTION;
1743 DUPLICATE_RECORD EXCEPTION;
1744 RESOURCE_REQUIRED EXCEPTION;
1745 INVALID_DATE_RANGE EXCEPTION;
1746
1747 X_field varchar2(2000) ;
1748 X_value varchar2(2000) ;
1749 X_msg varchar2(2000) ;
1750 X_count number ;
1751 l_count1 number ;
1752 l_count2 number ;
1753 l_count3 number ;
1754 l_count4 number ;
1755 l_sy_date date ;
1756
1757 BEGIN
1758
1759 X_field := '';
1760 X_value := '';
1761 X_msg := '';
1762 X_count := 0;
1763 l_count1 := 0;
1764 l_count2 := 0;
1765 l_count3 := 0;
1766 l_count4 := 0;
1767 l_sy_date := NULL;
1768 x_return_status := 'S' ;
1769
1770 /* Check for valid item */
1771 IF (p_calibration_item_id IS NOT NULL) THEN
1772 OPEN Cur_check_item;
1773 FETCH Cur_check_item INTO l_count3;
1774 CLOSE Cur_check_item;
1775 IF (l_count3 <> 1) THEN
1776 x_return_status := 'E';
1777 X_field := 'Calibration Item ';
1778 X_value := p_calibration_item_id;
1779 RAISE INVALID_VALUE;
1780 END IF;
1781 END IF;
1782
1783 /* Check for valid vendor */
1784 IF (p_vendor_id IS NOT NULL) THEN
1785 OPEN Cur_vendor_id;
1786 FETCH Cur_vendor_id INTO l_count2;
1787 CLOSE Cur_vendor_id;
1788 IF (l_count2 <> 1) THEN
1789 x_return_status := 'E';
1790 X_field := 'Vendor Identification';
1791 X_value := p_vendor_id;
1792 RAISE INVALID_VALUE;
1793 END IF;
1794 END IF;
1795
1796 /* Check for Duplicate record */
1797 IF (p_resource_id IS NULL) OR (p_instance_id is NULL) THEN
1798 x_return_status := 'E';
1799 X_msg := 'Resource or Instance ';
1800 RAISE RESOURCE_REQUIRED;
1801 ELSE
1802 x_return_status := 'S';
1803 IF v_update_flag <> 'Y' THEN
1804 OPEN Cur_check_dup;
1805 FETCH Cur_check_dup INTO l_count1;
1806 CLOSE Cur_check_dup;
1807 IF (l_count1 >0) THEN
1808 x_return_status := 'E';
1809 RAISE DUPLICATE_RECORD;
1810 END IF;
1811 END IF;
1812 END IF;
1813
1814 /* Check if Inactive Ind has Valid Values 0,1,2 */
1815
1816 IF (p_inactive_ind = 0) OR (p_inactive_ind = 1) THEN
1817 x_return_status := 'S';
1818 ELSE
1819 x_return_status := 'E';
1820 X_field := 'Inactive Indicator';
1821 X_value := p_inactive_ind;
1822 RAISE INVALID_VALUE;
1823 END IF;
1824
1825 IF (p_calibration_frequency < 0) THEN
1826 x_return_status := 'E';
1827 X_field := 'Calibration Frequency';
1828 X_value := p_calibration_frequency;
1829 RAISE INVALID_VALUE;
1830 END IF;
1831
1832 IF (p_maintenance_interval < 0) THEN
1833 x_return_status := 'E';
1834 X_field := 'Maintenance Interval';
1835 X_value := p_maintenance_interval;
1836 RAISE INVALID_VALUE;
1837 END IF;
1838
1839 BEGIN
1840 SELECT to_date((FND_PROFILE.VALUE('SY$MAX_DATE')),'YYYY/MM/DD')
1841 INTO l_sy_date FROM SYS.DUAL ;
1842 x_return_status := 'S';
1843 EXCEPTION
1844 WHEN OTHERS THEN
1845 x_return_status := 'E';
1846 X_field := 'System Max Date';
1847 X_value := l_sy_date ;
1848 RAISE INVALID_VALUE;
1849 END ;
1850
1851 IF p_eff_start_date > l_sy_date THEN
1852 x_return_status := 'E';
1853 X_field := 'Effective Start Date ';
1854 X_value := p_eff_start_date;
1855 RAISE INVALID_VALUE;
1856 END IF;
1857
1858 IF p_eff_end_date > l_sy_date THEN
1859 x_return_status := 'E';
1860 X_field := 'Effective End Date ';
1861 X_value := p_eff_end_date;
1862 RAISE INVALID_VALUE;
1863 END IF;
1864
1865 IF p_eff_end_date < p_eff_start_date THEN
1866 x_return_status := 'E';
1867 RAISE INVALID_DATE_RANGE;
1868 END IF;
1869
1870 EXCEPTION
1871
1872 WHEN INVALID_DATE_RANGE THEN
1873 x_return_status := FND_API.G_RET_STS_ERROR;
1874 FND_MESSAGE.SET_NAME('GMP','MR_STARTENDDATEERR');
1875 FND_MSG_PUB.ADD;
1876 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count,p_data=>x_message_list);
1877
1878 WHEN INVALID_VALUE THEN
1879 x_return_status := FND_API.G_RET_STS_ERROR;
1880 FND_MESSAGE.SET_NAME('GMP','GMP_INVALID_VALUE');
1881 FND_MESSAGE.SET_TOKEN('FIELD',X_field);
1882 FND_MESSAGE.SET_TOKEN('VALUE',X_value);
1883 FND_MSG_PUB.ADD;
1884 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count,p_data=>x_message_list);
1885
1886 WHEN RESOURCE_REQUIRED THEN
1887 x_return_status := FND_API.G_RET_STS_ERROR;
1888 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
1889 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
1890 FND_MSG_PUB.ADD;
1891 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count,p_data=>x_message_list);
1892
1893 WHEN DUPLICATE_RECORD THEN
1894 x_return_status := FND_API.G_RET_STS_ERROR;
1895 FND_MESSAGE.SET_NAME('GMP', 'PS_DUP_REC');
1896 FND_MSG_PUB.ADD;
1897 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count,p_data=>x_message_list);
1898
1899 END check_instance_data ;
1900
1901 END GMP_RESOURCE_DTL_PUB;