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