[Home] [Help]
PACKAGE BODY: APPS.GME_BATCHSTEP_RSRC_PVT
Source
1 PACKAGE BODY gme_batchstep_rsrc_pvt AS
2 /* $Header: GMEVRSRB.pls 120.3 2005/10/11 07:48:35 pxkumar noship $
3 *****************************************************************
4 * *
5 * Package GME_BATCHSTEP_RSRC_PVT *
6 * *
7 * Contents INSERT RESOURCE *
8 * UPDATE RESOURCE *
9 * DELETE RESOURCE *
10 * *
11 * Use This is the private layer of the GME Batch Step *
12 * Resources. *
13 * *
14 * History *
15 * K.Y.Hunt *
16 * Reworked for Inventory Convergence. 02-APR-2005 *
17 * Pawan Kumar 10-Oct-2005 Bug-4175041 *
18 * Added the interdependency validation for the resource count *
19 * and resource usage in update_batchstep_resource procedure *
20 *****************************************************************
21 */
22 /* Global variables */
23 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_BATCHSTEP_RSRC_PVT';
24 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
25
26 /*===========================================================================================
27 Procedure
28 validate_param
29 Description
30 Procedure to validate parameter combination provided to identify an activity for the
31 resource APIs
32 Parameters
33 (p_org_code,p_batch_no,p_batchstep_no and p_activity ) to uniquely identify an activity
34 x_return_status reflects return status of the API
35 =============================================================================================*/
36 PROCEDURE validate_param (
37 p_org_code IN VARCHAR2 := NULL
38 ,p_batch_no IN VARCHAR2 := NULL
39 ,p_batchstep_no IN NUMBER := NULL
40 ,p_activity IN VARCHAR2 := NULL
41 ,p_resource IN VARCHAR2 := NULL
42 ,x_organization_id OUT NOCOPY NUMBER
43 ,x_batch_id OUT NOCOPY NUMBER
44 ,x_batchstep_id OUT NOCOPY NUMBER
45 ,x_activity_id OUT NOCOPY NUMBER
46 ,x_rsrc_id OUT NOCOPY NUMBER
47 ,x_step_status OUT NOCOPY NUMBER
48 ,x_return_status OUT NOCOPY VARCHAR2)
49 IS
50 l_api_name CONSTANT VARCHAR2 (30) := 'validate_param';
51 l_organization_id NUMBER;
52 l_batch_id NUMBER;
53 l_batchstep_id NUMBER;
54 l_activity_id NUMBER;
55 l_resource VARCHAR2 (16);
56 l_step_status NUMBER;
57 l_rsrc_id NUMBER;
58 l_batch_type NUMBER;
59 l_rsrc_not_found BOOLEAN;
60
61 CURSOR cur_get_batch_dtl (
62 v_organization_code VARCHAR2
63 ,v_batch_no VARCHAR2)
64 IS
65 SELECT bh.organization_id, bh.batch_id, bh.batch_type
66 FROM gme_batch_header bh, mtl_parameters mp
67 WHERE mp.organization_code = v_organization_code
68 AND mp.organization_id = bh.organization_id
69 AND bh.batch_no = v_batch_no
70 AND batch_type = 0;
71
72 CURSOR cur_get_batchstep_dtl (v_batch_id NUMBER, v_batchstep_no NUMBER)
73 IS
74 SELECT batchstep_id, step_status
75 FROM gme_batch_steps
76 WHERE batch_id = v_batch_id AND batchstep_no = v_batchstep_no;
77
78 CURSOR cur_get_activity_id (
79 v_step_id NUMBER
80 ,v_activity VARCHAR2
81 ,v_batch_id NUMBER)
82 IS
83 SELECT batchstep_activity_id
84 FROM gme_batch_step_activities
85 WHERE batchstep_id = v_step_id
86 AND batch_id = v_batch_id
87 AND activity = v_activity;
88
89 CURSOR cur_fetch_resource_dtl (v_activity_id NUMBER, v_resource VARCHAR2)
90 IS
91 SELECT batchstep_resource_id
92 FROM gme_batch_step_resources
93 WHERE batchstep_activity_id = v_activity_id
94 AND resources = v_resource;
95
96 batch_not_found EXCEPTION;
97 batchstep_not_found EXCEPTION;
98 stepactivity_not_found EXCEPTION;
99 resource_not_found EXCEPTION;
100 input_param_missing EXCEPTION;
101 BEGIN
102 IF g_debug <= gme_debug.g_log_procedure THEN
103 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
104 || l_api_name);
105 gme_debug.put_line ( g_pkg_name
106 || '.'
107 || l_api_name
108 || ' input org_code =>'
109 || p_org_code);
110 gme_debug.put_line ( g_pkg_name
111 || '.'
112 || l_api_name
113 || ' input batch_no =>'
114 || p_batch_no);
115 gme_debug.put_line ( g_pkg_name
116 || '.'
117 || l_api_name
118 || ' input batchstep_no =>'
119 || p_batchstep_no);
120 gme_debug.put_line ( g_pkg_name
121 || '.'
122 || l_api_name
123 || ' input activity =>'
124 || p_activity);
125 gme_debug.put_line ( g_pkg_name
126 || '.'
127 || l_api_name
128 || ' input resource =>'
129 || p_resource);
130 gme_debug.put_line ( g_pkg_name
131 || '.'
132 || l_api_name
133 || ' **********************************');
134 END IF;
135
136 /* Initially let us assign the return status to success */
137 x_return_status := fnd_api.g_ret_sts_success;
138
139 IF p_org_code IS NULL THEN
140 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
141 ,'FIELD_NAME'
142 ,'ORGANIZATION');
143 RAISE input_param_missing;
144 ELSIF p_batch_no IS NULL THEN
145 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
146 ,'FIELD_NAME'
147 ,'BATCH NUMBER');
148 RAISE input_param_missing;
149 ELSIF p_batchstep_no IS NULL THEN
150 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
151 ,'FIELD_NAME'
152 ,'BATCH STEP NUMBER');
153 RAISE input_param_missing;
154 ELSIF p_activity IS NULL THEN
155 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
156 ,'FIELD_NAME'
157 ,'ACTIVITY');
158 RAISE input_param_missing;
159 END IF;
160
161 -- Validate input param one by one to see if it identifies a resource/activity correctly
162 OPEN cur_get_batch_dtl (p_org_code, p_batch_no);
163
164 FETCH cur_get_batch_dtl
165 INTO l_organization_id, l_batch_id, l_batch_type;
166
167 IF cur_get_batch_dtl%NOTFOUND THEN
168 CLOSE cur_get_batch_dtl;
169
170 gme_common_pvt.log_message ('GME_BATCH_NOT_FOUND');
171 RAISE batch_not_found;
172 END IF;
173
174 CLOSE cur_get_batch_dtl;
175
176 x_organization_id := l_organization_id;
177 x_batch_id := l_batch_id;
178
179 -- use batch_id to fetch batchstep_id
180 OPEN cur_get_batchstep_dtl (l_batch_id, p_batchstep_no);
181
182 FETCH cur_get_batchstep_dtl
183 INTO l_batchstep_id, l_step_status;
184
185 IF cur_get_batchstep_dtl%NOTFOUND THEN
186 CLOSE cur_get_batchstep_dtl;
187
188 gme_common_pvt.log_message ('GME_BATCH_STEP_NOT_FOUND'
189 ,'STEP_ID'
190 ,p_batchstep_no);
191 RAISE batchstep_not_found;
192 END IF;
193
194 CLOSE cur_get_batchstep_dtl;
195
196 x_step_status := l_step_status;
197 x_batchstep_id := l_batchstep_id;
198
199 -- fetch activity and resource id
200 -- Bug 2651359 - rework done for issue where same activity exists more than once in a
201 -- step and specified rsrc exists only in second or later occurrence of the activity
202 FOR step_activity IN cur_get_activity_id (l_batchstep_id
203 ,p_activity
204 ,l_batch_id) LOOP
205 IF cur_get_activity_id%FOUND THEN
206 l_activity_id := step_activity.batchstep_activity_id;
207 x_activity_id := l_activity_id;
208
209 IF p_resource IS NOT NULL THEN
210 OPEN cur_fetch_resource_dtl (l_activity_id, p_resource);
211
212 FETCH cur_fetch_resource_dtl
213 INTO l_rsrc_id;
214
215 IF cur_fetch_resource_dtl%NOTFOUND THEN
216 CLOSE cur_fetch_resource_dtl;
217
218 l_rsrc_not_found := TRUE;
219 ELSE
220 CLOSE cur_fetch_resource_dtl;
221
222 l_rsrc_not_found := FALSE;
223 x_rsrc_id := l_rsrc_id;
224 EXIT;
225 END IF;
226 END IF;
227 ELSE
228 gme_common_pvt.log_message ('GME_STEP_ACTIVITY_NOT_FOUND'
229 ,'ACTIVITY'
230 ,p_activity
231 ,'STEP_NO'
232 ,p_batchstep_no);
233 RAISE stepactivity_not_found;
234 END IF;
235 END LOOP;
236
237 -- If resource was not found in any activity then report error
238 IF l_rsrc_not_found THEN
239 gme_common_pvt.log_message ('GME_RSRC_NOT_FOUND'
240 ,'RESOURCE'
241 ,p_resource
242 ,'ACTIVITY'
243 ,p_activity);
244 RAISE resource_not_found;
245 END IF;
246
247 IF g_debug <= gme_debug.g_log_statement THEN
248 gme_debug.put_line ( g_pkg_name
249 || '.'
250 || l_api_name
251 || ' output organization =>'
252 || x_organization_id);
253 gme_debug.put_line ( g_pkg_name
254 || '.'
255 || l_api_name
256 || ' output batch_id =>'
257 || x_batch_id);
258 gme_debug.put_line ( g_pkg_name
259 || '.'
260 || l_api_name
261 || ' output batchstep_id =>'
262 || x_batchstep_id);
263 gme_debug.put_line ( g_pkg_name
264 || '.'
265 || l_api_name
266 || ' output activity_id =>'
267 || x_activity_id);
268 gme_debug.put_line ( g_pkg_name
269 || '.'
270 || l_api_name
271 || ' output rsrc_id =>'
272 || x_rsrc_id);
273 gme_debug.put_line ( g_pkg_name
274 || '.'
275 || l_api_name
276 || ' output step_status =>'
277 || x_step_status);
278 gme_debug.put_line ( ' Completed private layer '
279 || l_api_name
280 || ' at '
281 || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS') );
282 END IF;
283 EXCEPTION
284 WHEN batch_not_found OR batchstep_not_found OR input_param_missing THEN
285 x_return_status := fnd_api.g_ret_sts_error;
286 WHEN stepactivity_not_found OR resource_not_found THEN
287 x_return_status := fnd_api.g_ret_sts_error;
288 WHEN OTHERS THEN
289 x_return_status := fnd_api.g_ret_sts_unexp_error;
290 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
291 END validate_param;
292
293 /*===========================================================================================
294 Procedure
295 validate_rsrc_param
296 Description
297 Procedure is used to validate all parameters passed to rsrc APIs
298 Parameters
299
300 x_return_status reflects return status of the API
301 =============================================================================================*/
302 PROCEDURE validate_rsrc_param (
303 p_batchstep_resource_rec IN gme_batch_step_resources%ROWTYPE
304 ,p_activity_id IN NUMBER
308 DEFAULT fnd_api.g_false
305 ,p_ignore_qty_below_cap IN VARCHAR2
306 DEFAULT fnd_api.g_false
307 ,p_validate_flexfield IN VARCHAR2
309 ,p_action IN VARCHAR2
310 ,x_batchstep_resource_rec OUT NOCOPY gme_batch_step_resources%ROWTYPE
311 ,x_step_status OUT NOCOPY NUMBER
312 ,x_return_status OUT NOCOPY VARCHAR2)
313 IS
314 l_api_name CONSTANT VARCHAR2 (30) := 'validate_rsrc_param';
315 l_step_status NUMBER;
316 l_activity_id NUMBER;
317 l_batch_id NUMBER;
318 l_count_int NUMBER (10);
319 l_batch_asqc NUMBER;
320 l_activity_factor NUMBER;
321 l_dummy NUMBER;
322 l_return_status VARCHAR2 (2);
323 l_field_updated BOOLEAN := FALSE;
324 l_act_plan_start_date DATE;
325 l_act_plan_cmplt_date DATE;
326 l_act_actual_start_date DATE;
327 l_act_actual_cmplt_date DATE;
328 l_step_qty_um VARCHAR2 (4);
329 l_batchstep_resource_rec gme_batch_step_resources%ROWTYPE;
330 l_batchstep_resource_rec_out gme_batch_step_resources%ROWTYPE;
331
332 CURSOR cur_get_step_dtl_from_act (v_act_id NUMBER)
333 IS
334 SELECT a.step_status, a.batch_id, a.step_qty_um
335 FROM gme_batch_steps a, gme_batch_step_activities b
336 WHERE b.batchstep_activity_id = v_act_id
337 AND a.batch_id = b.batch_id
338 AND a.batchstep_id = b.batchstep_id;
339
340 CURSOR cur_get_activity_dtl (v_status NUMBER, v_activity_id NUMBER)
341 IS
342 SELECT DECODE (v_status
343 ,1, plan_activity_factor
344 ,actual_activity_factor)
345 ,plan_start_date, plan_cmplt_date, actual_start_date
346 ,actual_cmplt_date
347 FROM gme_batch_step_activities
348 WHERE batchstep_activity_id = v_activity_id;
349
350 CURSOR cur_get_activity_detail (v_activity_id NUMBER)
351 IS
352 SELECT plan_start_date, plan_cmplt_date
353 FROM gme_batch_step_activities
354 WHERE batchstep_activity_id = v_activity_id;
355
356 CURSOR cur_get_batch_asqc (v_batch_id NUMBER)
357 IS
358 SELECT automatic_step_calculation
359 FROM gme_batch_header
360 WHERE batch_id = v_batch_id;
361
362 CURSOR cur_get_cost_cmpnt (v_cost_cmpntcls_id NUMBER)
363 IS
364 SELECT 1
365 FROM cm_cmpt_mst
366 WHERE cost_cmpntcls_id = v_cost_cmpntcls_id;
367
368 CURSOR cur_get_analysis_code (v_cost_analysis_code VARCHAR2)
369 IS
370 SELECT 1
371 FROM cm_alys_mst
372 WHERE cost_analysis_code = v_cost_analysis_code;
373
374 invalid_step_status EXCEPTION;
375 invalid_activity_factor EXCEPTION;
376 invalid_asqc EXCEPTION;
377 invalid_action EXCEPTION;
378 cost_cmpnt_not_found EXCEPTION;
379 analysis_code_not_found EXCEPTION;
380 invalid_prim_rsrc_ind EXCEPTION;
381 input_param_missing EXCEPTION;
382 invalid_date EXCEPTION;
383 date_outside_range EXCEPTION;
384 invalid_scale_type EXCEPTION;
385 process_qty_error EXCEPTION;
386 error_condition EXCEPTION;
387 flex_validation_error EXCEPTION;
388 flex_consolidation_error EXCEPTION;
389 rsrc_fetch_error EXCEPTION;
390 BEGIN
391 IF g_debug <= gme_debug.g_log_procedure THEN
392 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
393 || l_api_name);
394 gme_debug.put_line ( g_pkg_name
395 || '.'
396 || l_api_name
397 || ' action is '
398 || p_action);
399 END IF;
400
401 /* Initially let us assign the return status to success */
402 x_return_status := fnd_api.g_ret_sts_success;
403
404 IF p_action = 'INSERT' THEN
405 /* Validations for Insert processing */
406 --check analysis code
407 IF g_debug <= gme_debug.g_log_procedure THEN
408 gme_debug.put_line ( g_pkg_name
409 || '.'
410 || l_api_name
411 || ' validate cost analysis code '
412 || p_batchstep_resource_rec.cost_analysis_code);
413 END IF;
414
415 IF p_batchstep_resource_rec.cost_analysis_code IS NULL THEN
416 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
417 ,'FIELD_NAME'
418 ,'COST_ANALYSIS_CODE');
419 RAISE input_param_missing;
420 ELSE
421 OPEN cur_get_analysis_code
422 (p_batchstep_resource_rec.cost_analysis_code);
423
424 FETCH cur_get_analysis_code
428 CLOSE cur_get_analysis_code;
425 INTO l_dummy;
426
427 IF cur_get_analysis_code%NOTFOUND THEN
429
430 fnd_message.set_name ('GMD', 'GMD_INVALID_COST_ANLYS_CODE');
431 fnd_msg_pub.ADD;
432 RAISE analysis_code_not_found;
433 END IF;
434
435 CLOSE cur_get_analysis_code;
436 END IF;
437
438 --check cost cmpnt id
439 IF g_debug <= gme_debug.g_log_procedure THEN
440 gme_debug.put_line ( g_pkg_name
441 || '.'
442 || l_api_name
443 || ' validate cost component class'
444 || p_batchstep_resource_rec.cost_cmpntcls_id);
445 END IF;
446
447 IF p_batchstep_resource_rec.cost_cmpntcls_id IS NULL THEN
448 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
449 ,'FIELD_NAME'
450 ,'COST_COMPONENT_CLASS_ID');
451 RAISE input_param_missing;
452 ELSE
453 OPEN cur_get_cost_cmpnt
454 (p_batchstep_resource_rec.cost_cmpntcls_id);
455
456 FETCH cur_get_cost_cmpnt
457 INTO l_dummy;
458
459 IF cur_get_cost_cmpnt%NOTFOUND THEN
460 fnd_message.set_name ('GMD', 'GMD_INVALID_COST_CMPNTCLS_ID');
461 fnd_msg_pub.ADD;
462
463 CLOSE cur_get_cost_cmpnt;
464
465 RAISE cost_cmpnt_not_found;
466 END IF;
467
468 CLOSE cur_get_cost_cmpnt;
469 END IF;
470
471 -- check scale_type
472 IF g_debug <= gme_debug.g_log_procedure THEN
473 gme_debug.put_line ( g_pkg_name
474 || '.'
475 || l_api_name
476 || ' validate scale type '
477 || p_batchstep_resource_rec.scale_type);
478 END IF;
479
480 IF p_batchstep_resource_rec.scale_type IS NULL THEN
481 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
482 ,'FIELD_NAME'
483 ,'SCALE_TYPE');
484 RAISE input_param_missing;
485 ELSIF (NOT (lookup_code_valid ('GMD_RESOURCE_SCALE_TYPE'
486 ,p_batchstep_resource_rec.scale_type) ) ) THEN
487 gme_common_pvt.log_message ('GME_INVALID_SCALE_TYPE');
488 RAISE invalid_scale_type;
489 END IF;
490
491 -- prim rsrc ind
492 IF g_debug <= gme_debug.g_log_procedure THEN
493 gme_debug.put_line ( g_pkg_name
494 || '.'
495 || l_api_name
496 || ' validate primary_resource '
497 || p_batchstep_resource_rec.prim_rsrc_ind);
498 END IF;
499
500 IF p_batchstep_resource_rec.prim_rsrc_ind IS NULL THEN
501 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
502 ,'FIELD_NAME'
503 ,'Primary_Resource Indicator');
504 RAISE input_param_missing;
505 ELSIF (NOT (lookup_code_valid ('GMD_PRIM_RSRC_IND'
506 ,p_batchstep_resource_rec.prim_rsrc_ind) ) ) THEN
507 gme_common_pvt.log_message ('GME_INV_PRM_RSRC_IND');
508 RAISE invalid_prim_rsrc_ind;
509 END IF;
510
511 l_batchstep_resource_rec := p_batchstep_resource_rec;
512 -- FETCH step_id
513 l_activity_id := p_activity_id;
514
515 OPEN cur_get_step_dtl_from_act (p_activity_id);
516
517 FETCH cur_get_step_dtl_from_act
518 INTO l_step_status, l_batch_id, l_step_qty_um;
519
520 CLOSE cur_get_step_dtl_from_act;
521
522 l_batchstep_resource_rec.batch_id := l_batch_id;
523 l_batchstep_resource_rec.batchstep_activity_id := l_activity_id;
524 l_batchstep_resource_rec.resource_qty_um := l_step_qty_um;
525
526 IF g_debug <= gme_debug.g_log_procedure THEN
527 gme_debug.put_line ( g_pkg_name
528 || '.'
529 || l_api_name
530 || ' validate step_status for activity_id '
531 || p_activity_id
532 || ' status is '
533 || l_step_status);
534 END IF;
535
536 IF l_step_status IN (4, 5) THEN
537 gme_common_pvt.log_message ('PC_STEP_STATUS_ERR');
538 RAISE invalid_step_status;
539 END IF;
540
541 -- check ASQC property
542 OPEN cur_get_batch_asqc (l_batch_id);
543
544 FETCH cur_get_batch_asqc
545 INTO l_batch_asqc;
546
547 CLOSE cur_get_batch_asqc;
548
549 IF l_batch_asqc = 1 AND l_step_status = 2 THEN
550 gme_common_pvt.log_message ('GME_INVALID_ASQC_ACTION');
551 RAISE invalid_asqc;
552 END IF;
553
557 gme_debug.put_line ( g_pkg_name
554 x_step_status := l_step_status;
555
556 IF g_debug <= gme_debug.g_log_procedure THEN
558 || '.'
559 || l_api_name
560 || ' get activity detail for step_status '
561 || l_step_status
562 || ' with activity '
563 || l_activity_id);
564 END IF;
565
566 OPEN cur_get_activity_dtl (l_step_status, l_activity_id);
567
568 FETCH cur_get_activity_dtl
569 INTO l_activity_factor, l_act_plan_start_date
570 ,l_act_plan_cmplt_date, l_act_actual_start_date
571 ,l_act_actual_cmplt_date;
572
573 CLOSE cur_get_activity_dtl;
574
575 -- check activity factor
576 IF g_debug <= gme_debug.g_log_procedure THEN
577 gme_debug.put_line ( g_pkg_name
578 || '.'
579 || l_api_name
580 || ' validate activity factor of '
581 || l_activity_factor);
582 END IF;
583
584 IF l_activity_factor <= 0 THEN
585 gme_common_pvt.log_message ('GME_INVALID_ACTIVITY_FACTOR');
586 RAISE invalid_activity_factor;
587 END IF;
588
589 -- check for count and usage values
590 -- Pawan Kumar added for integer value of the count only and changed to l_rec
591 -- variable in rest of procedure
592 -- trunc the plan_rsrc_count and actual_rsrc_count
593 IF p_batchstep_resource_rec.plan_rsrc_count IS NOT NULL THEN
594 l_batchstep_resource_rec.plan_rsrc_count :=
595 TRUNC (p_batchstep_resource_rec.plan_rsrc_count);
596
597 IF g_debug <= gme_debug.g_log_procedure THEN
598 gme_debug.put_line
599 ( g_pkg_name
600 || '.'
601 || l_api_name
602 || ' integer value needed for plan_rsrc_count '
603 || p_batchstep_resource_rec.plan_rsrc_count);
604 END IF;
605
606 IF p_batchstep_resource_rec.plan_rsrc_count <>
607 l_batchstep_resource_rec.plan_rsrc_count THEN
608 gme_common_pvt.log_message ('GME_INVALID_FIELD'
609 ,'FIELD'
610 ,'plan_rsrc_count');
611 RAISE error_condition;
612 END IF;
613 END IF;
614
615 IF p_batchstep_resource_rec.actual_rsrc_count IS NOT NULL THEN
616 l_batchstep_resource_rec.actual_rsrc_count :=
617 TRUNC (p_batchstep_resource_rec.actual_rsrc_count);
618
619 IF g_debug <= gme_debug.g_log_procedure THEN
620 gme_debug.put_line
621 ( g_pkg_name
622 || '.'
623 || l_api_name
624 || ' integer value needed for actual_rsrc_count '
625 || p_batchstep_resource_rec.actual_rsrc_count);
626 END IF;
627
628 IF p_batchstep_resource_rec.actual_rsrc_count <>
629 l_batchstep_resource_rec.actual_rsrc_count THEN
630 gme_common_pvt.log_message ('GME_INVALID_FIELD'
631 ,'FIELD'
632 ,'actual_rsrc_count');
633 RAISE error_condition;
634 END IF;
635 END IF;
636
637 l_count_int :=
638 NVL (l_batchstep_resource_rec.plan_rsrc_count
639 ,l_batchstep_resource_rec.actual_rsrc_count);
640
641 IF (l_batchstep_resource_rec.plan_rsrc_count) <= 0
642 OR ( ( NVL (l_batchstep_resource_rec.plan_rsrc_count, 0)
643 - NVL (l_count_int, 0) ) > 0)
644 OR ( ( NVL (l_batchstep_resource_rec.actual_rsrc_count, 0)
645 - NVL (l_count_int, 0) ) > 0)
646 OR (l_batchstep_resource_rec.actual_rsrc_count) <= 0
647 OR p_batchstep_resource_rec.plan_rsrc_qty < 0
648 OR p_batchstep_resource_rec.actual_rsrc_qty < 0
649 OR p_batchstep_resource_rec.plan_rsrc_usage < 0
650 OR p_batchstep_resource_rec.actual_rsrc_usage < 0 THEN
651 fnd_message.set_name ('GMI', 'IC_INV_QTY');
652 fnd_msg_pub.ADD;
653 RAISE error_condition;
654 END IF;
655
656 IF l_step_status = 1 THEN
657 IF l_batchstep_resource_rec.plan_rsrc_count IS NULL THEN
658 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
659 ,'FIELD_NAME'
660 ,'plan_rsrc_count');
661 RAISE input_param_missing;
662 END IF;
663
667 ,'plan_rsrc_usage');
664 IF p_batchstep_resource_rec.plan_rsrc_usage IS NULL THEN
665 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
666 ,'FIELD_NAME'
668 RAISE input_param_missing;
669 END IF;
670
671 IF (l_batch_asqc = 0) THEN
672 IF p_batchstep_resource_rec.plan_rsrc_qty IS NULL THEN
673 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
674 ,'FIELD_NAME'
675 ,'plan_rsrc_qty');
676 RAISE input_param_missing;
677 END IF;
678 ELSE
679 IF p_batchstep_resource_rec.plan_rsrc_qty IS NOT NULL THEN
680 gme_common_pvt.log_message ('GME_INPUT_PARAM_IGNORED'
681 ,'FIELD'
682 ,'plan_rsrc_qty');
683 END IF;
684 END IF;
685 ELSIF l_step_status IN (2, 3) THEN
686 IF l_batchstep_resource_rec.actual_rsrc_count IS NULL THEN
687 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
688 ,'FIELD_NAME'
689 ,'actual_rsrc_count');
690 RAISE input_param_missing;
691 END IF;
692
693 IF p_batchstep_resource_rec.actual_rsrc_usage IS NULL THEN
694 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
695 ,'FIELD_NAME'
696 ,'actual_rsrc_usage');
697 RAISE input_param_missing;
698 END IF;
699
700 IF (l_batch_asqc = 0) THEN
701 IF (p_batchstep_resource_rec.actual_rsrc_qty IS NULL) THEN
702 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
703 ,'FIELD_NAME'
704 ,'actual_rsrc_qty');
705 RAISE input_param_missing;
706 END IF;
707 ELSE
708 IF p_batchstep_resource_rec.actual_rsrc_qty IS NOT NULL THEN
709 gme_common_pvt.log_message ('GME_INPUT_PARAM_IGNORED'
710 ,'FIELD'
711 ,'actual_rsrc_qty');
712 END IF;
713 END IF;
714 END IF;
715
716 -- moved date validation out of above if condn as planned dates are defaulted when
717 -- we insert rsrc in WIP step. which is not true for count and usage and other flds
718 IF g_debug <= gme_debug.g_log_procedure THEN
719 gme_debug.put_line ( g_pkg_name
720 || '.'
721 || l_api_name
722 || ' start date validations ');
723 END IF;
724
725 IF l_step_status IN (1, 2, 3) THEN
726 IF p_batchstep_resource_rec.plan_start_date IS NOT NULL
727 AND p_batchstep_resource_rec.plan_cmplt_date IS NOT NULL
728 AND l_step_status = 1 THEN
732 RAISE invalid_date;
729 IF p_batchstep_resource_rec.plan_start_date >
730 p_batchstep_resource_rec.plan_cmplt_date THEN
731 gme_common_pvt.log_message ('PM_BADSTARTDATE');
733 END IF;
734
735 IF NOT (date_within_activity_dates
736 (l_activity_id
737 ,1
738 ,p_batchstep_resource_rec.plan_start_date) ) THEN
739 gme_common_pvt.log_message
740 ('GME_RSRC_DATES_NOT_ALLOWED'
741 ,'RESOURCE'
742 ,p_batchstep_resource_rec.resources);
743 RAISE date_outside_range;
744 END IF;
745
746 l_batchstep_resource_rec.plan_start_date :=
747 p_batchstep_resource_rec.plan_start_date;
748
749 IF NOT (date_within_activity_dates
750 (l_activity_id
751 ,1
752 ,p_batchstep_resource_rec.plan_cmplt_date) ) THEN
753 gme_common_pvt.log_message
754 ('GME_RSRC_DATES_NOT_ALLOWED'
755 ,'RESOURCE'
756 ,p_batchstep_resource_rec.resources);
757 RAISE date_outside_range;
758 END IF;
759
760 l_batchstep_resource_rec.plan_cmplt_date :=
761 p_batchstep_resource_rec.plan_cmplt_date;
762 ELSE
763 IF ( p_batchstep_resource_rec.plan_start_date IS NULL
764 OR l_step_status = 3) THEN
765 l_batchstep_resource_rec.plan_start_date :=
766 l_act_plan_start_date;
767 ELSE
768 IF NOT (date_within_activity_dates
769 (l_activity_id
770 ,1
771 ,p_batchstep_resource_rec.plan_start_date) ) THEN
772 gme_common_pvt.log_message
773 ('GME_RSRC_DATES_NOT_ALLOWED'
774 ,'RESOURCE'
775 ,p_batchstep_resource_rec.resources);
776 RAISE date_outside_range;
777 END IF;
778
779 l_batchstep_resource_rec.plan_start_date :=
780 p_batchstep_resource_rec.plan_start_date;
781 END IF;
782
783 IF ( p_batchstep_resource_rec.plan_cmplt_date IS NULL
784 OR l_step_status = 3) THEN
785 l_batchstep_resource_rec.plan_cmplt_date :=
786 l_act_plan_cmplt_date;
787 ELSE
788 IF NOT (date_within_activity_dates
789 (l_activity_id
790 ,1
791 ,p_batchstep_resource_rec.plan_cmplt_date) ) THEN
792 gme_common_pvt.log_message
793 ('GME_RSRC_DATES_NOT_ALLOWED'
794 ,'RESOURCE'
795 ,p_batchstep_resource_rec.resources);
796 RAISE date_outside_range;
797 END IF;
798
799 l_batchstep_resource_rec.plan_cmplt_date :=
800 p_batchstep_resource_rec.plan_cmplt_date;
801 END IF;
802 END IF;
803
804 IF l_step_status IN (2, 3) THEN
805 IF p_batchstep_resource_rec.actual_start_date IS NOT NULL
806 AND p_batchstep_resource_rec.actual_cmplt_date IS NOT NULL
807 AND l_step_status = 3 THEN
808 IF p_batchstep_resource_rec.actual_start_date >
809 p_batchstep_resource_rec.actual_cmplt_date THEN
810 gme_common_pvt.log_message ('PM_BADSTARTDATE');
811 RAISE invalid_date;
812 END IF;
813
814 IF NOT (date_within_activity_dates
815 (l_activity_id
816 ,3
817 ,p_batchstep_resource_rec.actual_start_date) ) THEN
818 gme_common_pvt.log_message
819 ('GME_RSRC_DATES_NOT_ALLOWED'
820 ,'RESOURCE'
821 ,p_batchstep_resource_rec.resources);
822 RAISE date_outside_range;
823 END IF;
824
825 l_batchstep_resource_rec.actual_start_date :=
826 p_batchstep_resource_rec.actual_start_date;
827
828 IF NOT (date_within_activity_dates
829 (l_activity_id
830 ,3
831 ,p_batchstep_resource_rec.actual_cmplt_date) ) THEN
832 gme_common_pvt.log_message
836 RAISE date_outside_range;
833 ('GME_RSRC_DATES_NOT_ALLOWED'
834 ,'RESOURCE'
835 ,p_batchstep_resource_rec.resources);
837 END IF;
838
839 l_batchstep_resource_rec.actual_cmplt_date :=
840 p_batchstep_resource_rec.actual_cmplt_date;
841 ELSE
842 IF p_batchstep_resource_rec.actual_start_date IS NULL THEN
843 l_batchstep_resource_rec.actual_start_date :=
844 l_act_actual_start_date;
845 ELSE
846 IF p_batchstep_resource_rec.actual_start_date >
847 gme_common_pvt.g_timestamp THEN
848 fnd_message.set_name ('GMA', 'SY_NOFUTUREDATE');
849 fnd_msg_pub.ADD;
850 RAISE date_outside_range;
851 END IF;
852
853 IF NOT (date_within_activity_dates
854 (l_activity_id
855 ,3
856 ,p_batchstep_resource_rec.actual_start_date) ) THEN
857 gme_common_pvt.log_message
858 ('GME_RSRC_DATES_NOT_ALLOWED'
859 ,'RESOURCE'
860 ,p_batchstep_resource_rec.resources);
861 RAISE date_outside_range;
862 END IF;
863
864 l_batchstep_resource_rec.actual_start_date :=
865 p_batchstep_resource_rec.actual_start_date;
866 END IF;
867
868 IF ( l_step_status = 3
869 AND p_batchstep_resource_rec.actual_cmplt_date IS NULL) THEN
870 l_batchstep_resource_rec.actual_cmplt_date :=
871 l_act_actual_cmplt_date;
872 ELSIF l_step_status = 3
873 AND p_batchstep_resource_rec.actual_cmplt_date IS NOT NULL THEN
874 IF p_batchstep_resource_rec.actual_start_date >
875 gme_common_pvt.g_timestamp THEN
876 fnd_message.set_name ('GMA', 'SY_NOFUTUREDATE');
877 fnd_msg_pub.ADD;
878 RAISE date_outside_range;
879 END IF;
880
881 IF NOT (date_within_activity_dates
882 (l_activity_id
883 ,3
884 ,p_batchstep_resource_rec.actual_cmplt_date) ) THEN
885 gme_common_pvt.log_message
886 ('GME_RSRC_DATES_NOT_ALLOWED'
887 ,'RESOURCE'
888 ,p_batchstep_resource_rec.resources);
889 RAISE date_outside_range;
890 END IF;
891
892 l_batchstep_resource_rec.actual_cmplt_date :=
893 p_batchstep_resource_rec.actual_cmplt_date;
894 END IF;
895 END IF;
896 END IF;
897 END IF;
898
899 /* Additional Validations for action INSERT */
900 IF l_batchstep_resource_rec.offset_interval IS NULL THEN
901 l_batchstep_resource_rec.offset_interval := 0;
902 END IF;
903
904 -- null out values of actual fields for pending step
905 IF l_step_status = 1 THEN
906 l_batchstep_resource_rec.actual_rsrc_count := NULL;
907 l_batchstep_resource_rec.actual_rsrc_usage := NULL;
908 l_batchstep_resource_rec.actual_rsrc_qty := NULL;
909 l_batchstep_resource_rec.actual_start_date := NULL;
910 l_batchstep_resource_rec.actual_cmplt_date := NULL;
911 ELSIF l_step_status IN (2, 3) THEN
912 l_batchstep_resource_rec.plan_rsrc_count := NULL;
913 l_batchstep_resource_rec.plan_rsrc_usage := NULL;
914 l_batchstep_resource_rec.plan_rsrc_qty := NULL;
915 END IF;
916
917 IF gme_common_pvt.is_qty_below_capacity
918 (p_batch_step_resources_rec => l_batchstep_resource_rec) THEN
919 gme_common_pvt.log_message ('GME_RESOURCE_PROCESS_QUANTITY'
920 ,'RESOURCE'
921 ,l_batchstep_resource_rec.resources);
922
923 IF p_ignore_qty_below_cap = fnd_api.g_false THEN
924 RAISE process_qty_error;
925 END IF;
926 END IF;
927
928 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
929 gme_debug.put_line ('bef ins' || l_batchstep_resource_rec.batch_id);
930 END IF;
931 ELSIF p_action = 'UPDATE' THEN
932 /* ============================ */
933 --NULL;
934 -- check for count and usage values
935 l_count_int :=
936 NVL (p_batchstep_resource_rec.plan_rsrc_count
937 ,p_batchstep_resource_rec.actual_rsrc_count);
938
942 OR p_batchstep_resource_rec.actual_rsrc_count <= 0
939 IF p_batchstep_resource_rec.plan_rsrc_count <= 0
940 OR ( ( NVL (p_batchstep_resource_rec.plan_rsrc_count, 0)
941 - NVL (l_count_int, 0) ) > 0)
943 OR ( ( NVL (p_batchstep_resource_rec.actual_rsrc_count, 0)
944 - NVL (l_count_int, 0) ) > 0)
945 OR p_batchstep_resource_rec.plan_rsrc_qty < 0
946 OR p_batchstep_resource_rec.actual_rsrc_qty < 0
947 OR p_batchstep_resource_rec.plan_rsrc_usage < 0
948 OR p_batchstep_resource_rec.actual_rsrc_usage < 0 THEN
949 fnd_message.set_name ('GMI', 'IC_INV_QTY');
950 fnd_msg_pub.ADD;
951 RAISE error_condition;
952 END IF;
953
954 IF l_step_status IN (4, 5) THEN
955 gme_common_pvt.log_message ('PC_STEP_STATUS_ERR');
956 RAISE invalid_step_status;
957 END IF;
958
959 IF NOT (gme_batch_step_resources_dbl.fetch_row
960 (p_batchstep_resource_rec
961 ,l_batchstep_resource_rec) ) THEN
962 RAISE rsrc_fetch_error;
963 END IF;
964
965 OPEN cur_get_batch_asqc (p_batchstep_resource_rec.batch_id);
966
967 FETCH cur_get_batch_asqc
968 INTO l_batch_asqc;
969
970 CLOSE cur_get_batch_asqc;
971
972 /* Bug 3620264 - compare analysis code to G_MISS_CHAR instead of G_MISS_NUM */
973 IF p_batchstep_resource_rec.cost_analysis_code = fnd_api.g_miss_char THEN
974 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
975 ,'FIELD_NAME'
976 ,'COST_ANALYSIS_CODE');
977 RAISE input_param_missing;
978 ELSIF ( p_batchstep_resource_rec.cost_analysis_code IS NOT NULL
979 AND (l_batchstep_resource_rec.cost_analysis_code <>
980 p_batchstep_resource_rec.cost_analysis_code) ) THEN
981 OPEN cur_get_analysis_code
982 (p_batchstep_resource_rec.cost_analysis_code);
983
984 FETCH cur_get_analysis_code
985 INTO l_dummy;
986
987 IF cur_get_analysis_code%NOTFOUND THEN
988 CLOSE cur_get_analysis_code;
989
990 fnd_message.set_name ('GMD', 'GMD_INVALID_COST_ANLYS_CODE');
991 fnd_msg_pub.ADD;
992 RAISE analysis_code_not_found;
993 END IF;
994
995 CLOSE cur_get_analysis_code;
996
997 l_field_updated := TRUE;
998 l_batchstep_resource_rec.cost_analysis_code :=
999 p_batchstep_resource_rec.cost_analysis_code;
1000 END IF;
1001
1002 IF p_batchstep_resource_rec.cost_cmpntcls_id = fnd_api.g_miss_num THEN
1003 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
1004 ,'FIELD_NAME'
1005 ,'COST_COMPONENT_CLASS_ID');
1006 RAISE input_param_missing;
1007 ELSIF ( p_batchstep_resource_rec.cost_cmpntcls_id IS NOT NULL
1008 AND (l_batchstep_resource_rec.cost_cmpntcls_id <>
1009 p_batchstep_resource_rec.cost_cmpntcls_id) ) THEN
1010 OPEN cur_get_cost_cmpnt
1011 (p_batchstep_resource_rec.cost_cmpntcls_id);
1012
1013 FETCH cur_get_cost_cmpnt
1014 INTO l_dummy;
1015
1016 IF cur_get_cost_cmpnt%NOTFOUND THEN
1017 fnd_message.set_name ('GMD', 'GMD_INVALID_COST_CMPNTCLS_ID');
1018 fnd_msg_pub.ADD;
1019
1020 CLOSE cur_get_cost_cmpnt;
1021
1022 RAISE cost_cmpnt_not_found;
1023 END IF;
1024
1025 CLOSE cur_get_cost_cmpnt;
1026
1027 l_field_updated := TRUE;
1028 l_batchstep_resource_rec.cost_cmpntcls_id :=
1029 p_batchstep_resource_rec.cost_cmpntcls_id;
1030 END IF;
1031
1032 IF p_batchstep_resource_rec.scale_type = fnd_api.g_miss_num THEN
1033 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
1034 ,'FIELD_NAME'
1035 ,'scale_type');
1036 RAISE input_param_missing;
1037 ELSIF ( p_batchstep_resource_rec.scale_type IS NOT NULL
1038 AND (l_batchstep_resource_rec.scale_type <>
1039 p_batchstep_resource_rec.scale_type) ) THEN
1040 IF (NOT (lookup_code_valid ('GMD_RESOURCE_SCALE_TYPE'
1041 ,p_batchstep_resource_rec.scale_type) ) ) THEN
1042 gme_common_pvt.log_message ('GME_INVALID_SCALE_TYPE');
1043 RAISE invalid_scale_type;
1044 END IF;
1045
1046 l_field_updated := TRUE;
1047 l_batchstep_resource_rec.scale_type :=
1048 p_batchstep_resource_rec.scale_type;
1049 END IF;
1050
1051 IF p_batchstep_resource_rec.prim_rsrc_ind = fnd_api.g_miss_num THEN
1052 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
1053 ,'FIELD_NAME'
1054 ,'prim_rsrc_ind');
1058 p_batchstep_resource_rec.prim_rsrc_ind) ) THEN
1055 RAISE input_param_missing;
1056 ELSIF ( p_batchstep_resource_rec.prim_rsrc_ind IS NOT NULL
1057 AND (l_batchstep_resource_rec.prim_rsrc_ind <>
1059 IF (NOT (lookup_code_valid ('GMD_PRIM_RSRC_IND'
1060 ,p_batchstep_resource_rec.prim_rsrc_ind) ) ) THEN
1061 gme_common_pvt.log_message ('GME_INV_PRM_RSRC_IND');
1062 RAISE invalid_prim_rsrc_ind;
1063 END IF;
1064
1065 l_field_updated := TRUE;
1066 l_batchstep_resource_rec.prim_rsrc_ind :=
1067 p_batchstep_resource_rec.prim_rsrc_ind;
1068 END IF;
1069
1070 --Pawan Kumar added trunc to give only integer value to count
1071 IF p_batchstep_resource_rec.plan_rsrc_count = fnd_api.g_miss_num
1072 AND l_step_status = 1 THEN
1073 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
1074 ,'FIELD_NAME'
1075 ,'plan_rsrc_cout');
1076 RAISE input_param_missing;
1077 ELSIF p_batchstep_resource_rec.plan_rsrc_count IS NOT NULL THEN
1078 IF p_batchstep_resource_rec.plan_rsrc_count <>
1079 TRUNC (p_batchstep_resource_rec.plan_rsrc_count) THEN
1080 gme_common_pvt.log_message ('GME_INVALID_FIELD'
1081 ,'FIELD'
1082 ,'plan_rsrc_count');
1083 RAISE error_condition;
1084 END IF;
1085
1086 IF l_step_status = 3 THEN
1087 gme_common_pvt.log_message ('GME_INV_ACT_STEP_STATUS'
1088 ,'FIELD'
1089 ,'plan_rsrc_count');
1090 RAISE invalid_action;
1091 ELSIF l_step_status = 2 THEN
1092 gme_common_pvt.log_message ('GME_UPD_NT_ALLOWED'
1093 ,'FIELD'
1094 ,'plan_rsrc_count');
1095 RAISE invalid_action;
1096 ELSIF (TRUNC (p_batchstep_resource_rec.plan_rsrc_count) <>
1097 l_batchstep_resource_rec.plan_rsrc_count) THEN
1098 l_field_updated := TRUE;
1099 l_batchstep_resource_rec.plan_rsrc_count :=
1100 TRUNC (p_batchstep_resource_rec.plan_rsrc_count);
1101 END IF;
1102 END IF;
1103
1104 IF p_batchstep_resource_rec.actual_rsrc_count =
1105 fnd_api.g_miss_num
1106 AND l_step_status = 3 THEN
1107 l_batchstep_resource_rec.actual_rsrc_count :=
1108 l_batchstep_resource_rec.plan_rsrc_count;
1109 ELSIF p_batchstep_resource_rec.actual_rsrc_count IS NOT NULL THEN
1110 IF p_batchstep_resource_rec.actual_rsrc_count <>
1111 TRUNC (p_batchstep_resource_rec.actual_rsrc_count) THEN
1112 gme_common_pvt.log_message ('GME_INVALID_FIELD'
1113 ,'FIELD'
1114 ,'actual_rsrc_count');
1115 RAISE error_condition;
1116 END IF;
1117
1118 IF l_step_status = 1 THEN
1119 gme_common_pvt.log_message ('GME_INV_ACT_STEP_STATUS'
1120 ,'FIELD'
1121 ,'actual_rsrc_count');
1122 RAISE invalid_action;
1123 --ELSIF (trunc(p_batchstep_resource_rec.actual_rsrc_count) <> l_batchstep_resource_rec.actual_rsrc_count) THEN
1124 --Rishi Varma B3865212 30/09/2004
1125 ELSIF (TRUNC (p_batchstep_resource_rec.actual_rsrc_count) <>
1126 NVL (l_batchstep_resource_rec.actual_rsrc_count, 0) ) THEN
1127 l_field_updated := TRUE;
1128 l_batchstep_resource_rec.actual_rsrc_count :=
1129 TRUNC (p_batchstep_resource_rec.actual_rsrc_count);
1130 END IF;
1131 --Pawan added for bug 4175041
1132 /* When the actual resource count is null in the database and we are trying to update actual resource usage without the
1133 actual resource count, then user will be given error message that actual resource count is required.*/
1134 ELSE
1135 IF (l_batchstep_resource_rec.actual_rsrc_count IS NULL AND p_batchstep_resource_rec.actual_rsrc_usage IS NOT NULL) THEN
1136 gme_common_pvt.log_message('GME_FIELD_VALUE_REQUIRED','FIELD_NAME', 'actual_rsrc_count');
1137 RAISE input_param_missing;
1138 END IF;
1139 END IF;
1140
1141 IF p_batchstep_resource_rec.plan_rsrc_usage = fnd_api.g_miss_num
1142 AND l_step_status = 1 THEN
1143 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
1144 ,'FIELD_NAME'
1145 ,'plan_rsrc_usage');
1146 RAISE input_param_missing;
1147 ELSIF p_batchstep_resource_rec.plan_rsrc_usage IS NOT NULL THEN
1148 IF l_step_status = 3 THEN
1149 gme_common_pvt.log_message ('GME_INV_ACT_STEP_STATUS'
1150 ,'FIELD'
1151 ,'plan_rsrc_usage');
1152 RAISE invalid_action;
1153 ELSIF (l_step_status = 2 OR l_batch_asqc = 1) THEN
1157 RAISE invalid_action;
1154 gme_common_pvt.log_message ('GME_UPD_NT_ALLOWED'
1155 ,'FIELD'
1156 ,'plan_rsrc_usage');
1158 ELSIF (p_batchstep_resource_rec.plan_rsrc_usage <>
1159 l_batchstep_resource_rec.plan_rsrc_usage) THEN
1160 l_field_updated := TRUE;
1161 l_batchstep_resource_rec.plan_rsrc_usage :=
1162 p_batchstep_resource_rec.plan_rsrc_usage;
1163 END IF;
1164 END IF;
1165
1166 IF p_batchstep_resource_rec.actual_rsrc_usage =
1167 fnd_api.g_miss_num
1168 AND l_step_status = 3 THEN
1169 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
1170 ,'FIELD_NAME'
1171 ,'actual_rsrc_usage');
1172 RAISE input_param_missing;
1173 ELSIF p_batchstep_resource_rec.actual_rsrc_usage IS NOT NULL THEN
1174 IF l_step_status = 1 THEN
1175 gme_common_pvt.log_message ('GME_INV_ACT_STEP_STATUS'
1176 ,'FIELD'
1177 ,'actual_rsrc_usage');
1178 RAISE invalid_action;
1179 ELSIF (l_batch_asqc = 1) THEN
1180 --Pawan Kumar changed the token
1181 gme_common_pvt.log_message ('GME_UPD_RSRC_NT_WRK_ASQCBTCH');
1182 RAISE invalid_action;
1183 --Pawan Kumar added nvl for proper update
1184 ELSIF (p_batchstep_resource_rec.actual_rsrc_usage <>
1185 NVL (l_batchstep_resource_rec.actual_rsrc_usage
1186 ,-1) ) THEN
1187 l_field_updated := TRUE;
1188 l_batchstep_resource_rec.actual_rsrc_usage :=
1189 p_batchstep_resource_rec.actual_rsrc_usage;
1190 END IF;
1191 --Pawan added for bug 4175041
1192 /* When the actual resource count is null in the database and we are trying to update actual resource usage without the
1193 actual resource count, then user will be given error message that actual resource count is required.*/
1194 ELSE
1195 IF (l_batchstep_resource_rec.actual_rsrc_count IS NULL AND p_batchstep_resource_rec.actual_rsrc_usage IS NOT NULL) THEN
1196 gme_common_pvt.log_message('GME_FIELD_VALUE_REQUIRED','FIELD_NAME', 'actual_rsrc_count');
1197 RAISE input_param_missing;
1198 END IF;
1199 END IF;
1200
1201 IF ( p_batchstep_resource_rec.usage_um IS NOT NULL
1202 AND p_batchstep_resource_rec.usage_um <>
1203 l_batchstep_resource_rec.usage_um) THEN
1204 gme_common_pvt.log_message ('GME_UPD_NT_ALLOWED'
1205 ,'FIELD'
1206 ,'usage_uom');
1207 RAISE error_condition;
1208 END IF;
1209
1210 IF p_batchstep_resource_rec.plan_rsrc_qty = fnd_api.g_miss_num
1211 AND l_step_status = 1 THEN
1212 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
1213 ,'FIELD_NAME'
1214 ,'plan_rsrc_qty');
1215 RAISE input_param_missing;
1216 ELSIF (p_batchstep_resource_rec.plan_rsrc_qty IS NOT NULL) THEN
1217 IF l_batch_asqc = 1 THEN
1218 gme_common_pvt.log_message ('GME_ASQC_NO_PLAN_RSRC_QTY');
1219 RAISE error_condition;
1220 ELSIF l_step_status = 3 THEN
1221 gme_common_pvt.log_message ('GME_INV_ACT_STEP_STATUS'
1222 ,'FIELD'
1223 ,'plan_rsrc_qty');
1224 RAISE invalid_action;
1225 ELSIF (l_step_status = 2 OR l_batch_asqc = 1) THEN
1226 gme_common_pvt.log_message ('GME_UPD_NT_ALLOWED'
1227 ,'FIELD'
1228 ,'plan_rsrc_qty');
1229 RAISE invalid_action;
1230 ELSIF (p_batchstep_resource_rec.plan_rsrc_qty <>
1231 l_batchstep_resource_rec.plan_rsrc_qty) THEN
1232 l_field_updated := TRUE;
1233 l_batchstep_resource_rec.plan_rsrc_qty :=
1234 p_batchstep_resource_rec.plan_rsrc_qty;
1235 END IF;
1236 END IF;
1237
1238 IF p_batchstep_resource_rec.actual_rsrc_qty = fnd_api.g_miss_num
1239 AND l_step_status = 3 THEN
1240 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
1241 ,'FIELD_NAME'
1242 ,'actual_rsrc_qty');
1243 RAISE input_param_missing;
1244 ELSIF p_batchstep_resource_rec.actual_rsrc_qty IS NOT NULL THEN
1245 IF l_batch_asqc = 1 THEN
1246 gme_common_pvt.log_message ('GME_ASQC_NO_ACT_RSRC_QTY');
1247 RAISE error_condition;
1248 ELSIF l_step_status = 1 THEN
1249 gme_common_pvt.log_message ('GME_INV_ACT_STEP_STATUS'
1250 ,'FIELD'
1251 ,'actual_rsrc_qty');
1252 RAISE invalid_action;
1256 ,-1) ) THEN
1253 --Pawan Kumar added nvl for proper update
1254 ELSIF (p_batchstep_resource_rec.actual_rsrc_qty <>
1255 NVL (l_batchstep_resource_rec.actual_rsrc_qty
1257 l_field_updated := TRUE;
1258 l_batchstep_resource_rec.actual_rsrc_qty :=
1259 p_batchstep_resource_rec.actual_rsrc_qty;
1260 END IF;
1261 END IF;
1262
1263 IF ( p_batchstep_resource_rec.resource_qty_um IS NOT NULL
1264 AND p_batchstep_resource_rec.resource_qty_um <>
1265 l_batchstep_resource_rec.resource_qty_um) THEN
1266 gme_common_pvt.log_message ('GME_UPD_NT_ALLOWED'
1267 ,'FIELD'
1268 ,'resource_qty_uom');
1269 RAISE error_condition;
1270 END IF;
1271
1272 IF l_step_status IN (1, 2, 3) THEN
1273 IF p_batchstep_resource_rec.plan_start_date IS NOT NULL
1274 AND p_batchstep_resource_rec.plan_cmplt_date IS NOT NULL
1275 AND l_step_status = 1 THEN
1276 IF p_batchstep_resource_rec.plan_start_date >
1277 p_batchstep_resource_rec.plan_cmplt_date THEN
1278 gme_common_pvt.log_message ('PM_BADSTARTDATE');
1279 RAISE invalid_date;
1280 END IF;
1281
1282 IF NOT (date_within_activity_dates
1283 (l_activity_id
1284 ,l_step_status
1285 ,p_batchstep_resource_rec.plan_start_date) ) THEN
1286 gme_common_pvt.log_message
1287 ('GME_RSRC_DATES_NOT_ALLOWED'
1288 ,'RESOURCE'
1289 ,p_batchstep_resource_rec.resources);
1290 RAISE date_outside_range;
1291 END IF;
1292
1293 l_field_updated := TRUE;
1294 l_batchstep_resource_rec.plan_start_date :=
1295 p_batchstep_resource_rec.plan_start_date;
1296
1297 IF NOT (date_within_activity_dates
1298 (l_activity_id
1299 ,l_step_status
1300 ,p_batchstep_resource_rec.plan_cmplt_date) ) THEN
1301 gme_common_pvt.log_message
1302 ('GME_RSRC_DATES_NOT_ALLOWED'
1303 ,'RESOURCE'
1304 ,p_batchstep_resource_rec.resources);
1305 RAISE date_outside_range;
1306 END IF;
1307
1308 l_field_updated := TRUE;
1309 l_batchstep_resource_rec.plan_cmplt_date :=
1310 p_batchstep_resource_rec.plan_cmplt_date;
1311 ELSE
1312 IF (p_batchstep_resource_rec.plan_start_date IS NOT NULL) THEN
1313 IF NOT (date_within_activity_dates
1314 (l_activity_id
1315 ,1
1316 ,p_batchstep_resource_rec.plan_start_date) ) THEN
1317 gme_common_pvt.log_message
1318 ('GME_RSRC_DATES_NOT_ALLOWED'
1319 ,'RESOURCE'
1320 ,p_batchstep_resource_rec.resources);
1321 RAISE date_outside_range;
1322 END IF;
1323
1324 IF l_batchstep_resource_rec.plan_cmplt_date IS NOT NULL
1325 AND p_batchstep_resource_rec.plan_start_date >
1326 l_batchstep_resource_rec.plan_cmplt_date THEN
1327 gme_common_pvt.log_message ('PM_BADSTARTDATE');
1328 RAISE invalid_date;
1329 END IF;
1330
1331 l_field_updated := TRUE;
1332 l_batchstep_resource_rec.plan_start_date :=
1333 p_batchstep_resource_rec.plan_start_date;
1334 END IF;
1335
1336 IF (p_batchstep_resource_rec.plan_cmplt_date IS NOT NULL) THEN
1337 IF NOT (date_within_activity_dates
1338 (l_activity_id
1339 ,1
1340 ,p_batchstep_resource_rec.plan_cmplt_date) ) THEN
1341 gme_common_pvt.log_message
1342 ('GME_RSRC_DATES_NOT_ALLOWED'
1343 ,'RESOURCE'
1344 ,p_batchstep_resource_rec.resources);
1345 RAISE date_outside_range;
1346 END IF;
1347
1348 IF l_batchstep_resource_rec.plan_start_date IS NOT NULL
1349 AND l_batchstep_resource_rec.plan_start_date >
1350 p_batchstep_resource_rec.plan_cmplt_date THEN
1351 gme_common_pvt.log_message ('PM_BADSTARTDATE');
1352 RAISE invalid_date;
1353 END IF;
1354
1355 l_field_updated := TRUE;
1356 l_batchstep_resource_rec.plan_cmplt_date :=
1360
1357 p_batchstep_resource_rec.plan_cmplt_date;
1358 END IF;
1359 END IF;
1361 IF l_step_status IN (2, 3) THEN
1362 IF p_batchstep_resource_rec.actual_start_date IS NOT NULL
1363 AND p_batchstep_resource_rec.actual_cmplt_date IS NOT NULL
1364 AND l_step_status = 3 THEN
1365 IF p_batchstep_resource_rec.actual_start_date >
1366 p_batchstep_resource_rec.actual_cmplt_date THEN
1367 gme_common_pvt.log_message ('PM_BADSTARTDATE');
1368 RAISE invalid_date;
1369 END IF;
1370
1371 IF p_batchstep_resource_rec.actual_start_date =
1372 fnd_api.g_miss_date THEN
1373 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
1374 ,'FIELD_NAME'
1375 ,'actual_start_date');
1376 RAISE input_param_missing;
1377 END IF;
1378
1379 IF NOT (date_within_activity_dates
1380 (l_activity_id
1381 ,3
1382 ,p_batchstep_resource_rec.actual_start_date) ) THEN
1383 gme_common_pvt.log_message
1384 ('GME_RSRC_DATES_NOT_ALLOWED'
1385 ,'RESOURCE'
1386 ,p_batchstep_resource_rec.resources);
1387 RAISE date_outside_range;
1388 END IF;
1389
1390 l_field_updated := TRUE;
1391 l_batchstep_resource_rec.actual_start_date :=
1392 p_batchstep_resource_rec.actual_start_date;
1393
1394 IF p_batchstep_resource_rec.actual_cmplt_date =
1395 fnd_api.g_miss_date THEN
1396 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
1397 ,'FIELD_NAME'
1398 ,'actual_cmplt_date');
1399 RAISE input_param_missing;
1400 END IF;
1401
1402 IF NOT (date_within_activity_dates
1403 (l_activity_id
1404 ,3
1405 ,p_batchstep_resource_rec.actual_cmplt_date) ) THEN
1406 gme_common_pvt.log_message
1407 ('GME_RSRC_DATES_NOT_ALLOWED'
1408 ,'RESOURCE'
1409 ,p_batchstep_resource_rec.resources);
1410 RAISE date_outside_range;
1411 END IF;
1412
1413 l_field_updated := TRUE;
1414 l_batchstep_resource_rec.actual_cmplt_date :=
1415 p_batchstep_resource_rec.actual_cmplt_date;
1416 ELSE
1417 IF p_batchstep_resource_rec.actual_start_date IS NOT NULL THEN
1418 IF p_batchstep_resource_rec.actual_start_date =
1419 fnd_api.g_miss_date THEN
1420 gme_common_pvt.log_message
1421 ('GME_FIELD_VALUE_REQUIRED'
1422 ,'FIELD_NAME'
1423 ,'actual_start_date');
1424 RAISE input_param_missing;
1425 END IF;
1426
1427 --Pawan Kumar added code for actual start date
1428 IF p_batchstep_resource_rec.actual_start_date >
1429 gme_common_pvt.g_timestamp THEN
1430 fnd_message.set_name ('GMA', 'SY_NOFUTUREDATE');
1431 fnd_msg_pub.ADD;
1432 RAISE date_outside_range;
1433 END IF;
1434
1435 IF NOT (date_within_activity_dates
1436 (l_activity_id
1437 ,3
1438 ,p_batchstep_resource_rec.actual_start_date) ) THEN
1439 gme_common_pvt.log_message
1440 ('GME_RSRC_DATES_NOT_ALLOWED'
1441 ,'RESOURCE'
1442 ,p_batchstep_resource_rec.resources);
1443 RAISE date_outside_range;
1444 END IF;
1445
1446 IF l_batchstep_resource_rec.actual_cmplt_date IS NOT NULL
1447 AND p_batchstep_resource_rec.actual_start_date >
1448 l_batchstep_resource_rec.actual_cmplt_date THEN
1449 gme_common_pvt.log_message ('PM_BADSTARTDATE');
1450 RAISE invalid_date;
1451 END IF;
1452
1453 l_field_updated := TRUE;
1454 l_batchstep_resource_rec.actual_start_date :=
1455 p_batchstep_resource_rec.actual_start_date;
1456 END IF;
1457
1458 IF ( p_batchstep_resource_rec.actual_cmplt_date IS NOT NULL
1462 gme_common_pvt.log_message
1459 AND l_step_status = 3) THEN
1460 IF p_batchstep_resource_rec.actual_cmplt_date =
1461 fnd_api.g_miss_date THEN
1463 ('GME_FIELD_VALUE_REQUIRED'
1464 ,'FIELD_NAME'
1465 ,'actual_cmplt_date');
1466 RAISE input_param_missing;
1467 END IF;
1468
1469 IF NOT (date_within_activity_dates
1470 (l_activity_id
1471 ,3
1472 ,p_batchstep_resource_rec.actual_cmplt_date) ) THEN
1473 gme_common_pvt.log_message
1474 ('GME_RSRC_DATES_NOT_ALLOWED'
1475 ,'RESOURCE'
1476 ,p_batchstep_resource_rec.resources);
1477 RAISE date_outside_range;
1478 END IF;
1479
1480 IF l_batchstep_resource_rec.actual_start_date IS NOT NULL
1481 AND p_batchstep_resource_rec.actual_cmplt_date <
1482 l_batchstep_resource_rec.actual_start_date THEN
1483 gme_common_pvt.log_message ('PM_BADSTARTDATE');
1484 RAISE invalid_date;
1485 END IF;
1486
1487 l_field_updated := TRUE;
1488 l_batchstep_resource_rec.actual_cmplt_date :=
1489 p_batchstep_resource_rec.actual_cmplt_date;
1490 END IF;
1491 END IF;
1492
1493 IF l_step_status = 2 THEN
1494 OPEN cur_get_activity_detail (l_activity_id);
1495
1496 FETCH cur_get_activity_detail
1497 INTO l_act_plan_start_date, l_act_plan_cmplt_date;
1498
1499 CLOSE cur_get_activity_detail;
1500
1501 IF p_batchstep_resource_rec.plan_start_date IS NULL
1502 AND l_batchstep_resource_rec.plan_start_date IS NULL THEN
1503 l_field_updated := TRUE;
1504 l_batchstep_resource_rec.plan_start_date :=
1505 l_act_plan_start_date;
1506 END IF;
1507
1508 IF p_batchstep_resource_rec.plan_cmplt_date IS NULL
1509 AND l_batchstep_resource_rec.plan_cmplt_date IS NULL THEN
1510 l_field_updated := TRUE;
1511 l_batchstep_resource_rec.plan_cmplt_date :=
1512 l_act_plan_cmplt_date;
1513 END IF;
1514 END IF;
1515 END IF; -- l_step_status IN 1,2
1516 END IF; -- l_step_status IN 1,2,3
1517
1518 IF gme_common_pvt.is_qty_below_capacity
1519 (p_batch_step_resources_rec => l_batchstep_resource_rec) THEN
1520 gme_common_pvt.log_message ('GME_RESOURCE_PROCESS_QUANTITY'
1521 ,'RESOURCE'
1522 ,l_batchstep_resource_rec.resources);
1523
1524 IF p_ignore_qty_below_cap = fnd_api.g_false THEN
1525 RAISE process_qty_error;
1526 END IF;
1527 END IF;
1528
1529 /* consolidate flexfield values from the input rec and the existing rec ahead of updating the table */
1530 consolidate_flexfields
1531 (p_new_batchstep_resource_rec => p_batchstep_resource_rec
1532 ,p_old_batchstep_resource_rec => l_batchstep_resource_rec
1533 ,p_validate_flexfield => p_validate_flexfield
1534 ,x_batchstep_resource_rec => l_batchstep_resource_rec_out
1535 ,x_return_status => l_return_status);
1536
1537 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1538 RAISE flex_consolidation_error;
1539 ELSE
1540 l_batchstep_resource_rec := l_batchstep_resource_rec_out;
1541 END IF;
1542 END IF; -- p_action =
1543
1544 /* Flexfield Validation */
1545 /* =====================*/
1546 IF g_debug <= gme_debug.g_log_procedure THEN
1547 gme_debug.put_line ( g_pkg_name
1548 || '.'
1549 || l_api_name
1550 || ' start flexfield validation ');
1551 END IF;
1552
1553 IF p_validate_flexfield = fnd_api.g_true THEN
1554 gme_validate_flex_fld_pvt.validate_flex_step_resources
1555 (p_step_resources => l_batchstep_resource_rec
1556 ,x_step_resources => l_batchstep_resource_rec_out
1557 ,x_return_status => l_return_status);
1558
1559 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1560 RAISE flex_validation_error;
1561 ELSE
1562 l_batchstep_resource_rec := l_batchstep_resource_rec_out;
1563 END IF;
1564 END IF;
1565
1569
1566 /* Populate the output batchstep resource rec*/
1567 /* ========================================= */
1568 x_batchstep_resource_rec := l_batchstep_resource_rec;
1570 IF g_debug <= gme_debug.g_log_statement THEN
1571 gme_debug.put_line ( ' Completed '
1572 || l_api_name
1573 || ' at '
1574 || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS') );
1575 END IF;
1576 EXCEPTION
1577 WHEN fnd_api.g_exc_error THEN
1578 x_return_status := fnd_api.g_ret_sts_error;
1579 WHEN invalid_step_status OR invalid_asqc OR invalid_activity_factor OR invalid_date OR date_outside_range OR invalid_action THEN
1580 x_return_status := fnd_api.g_ret_sts_error;
1581 WHEN cost_cmpnt_not_found OR analysis_code_not_found OR invalid_prim_rsrc_ind OR invalid_scale_type OR input_param_missing OR error_condition OR flex_validation_error OR flex_consolidation_error OR rsrc_fetch_error THEN
1582 x_return_status := fnd_api.g_ret_sts_error;
1583 WHEN OTHERS THEN
1584 x_return_status := fnd_api.g_ret_sts_unexp_error;
1585 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1586 END validate_rsrc_param;
1587
1588 /*===========================================================================================
1589 Procedure
1590 insert_batchstep_rsrc
1591 Description
1592 Procedure is used to insert rsrc for an activity
1593 Parameters
1594 p_batchstep_resource_rec Input Row from GME_BATCH_STEP_RESOURCES
1595 x_batchstep_resource_rec Output Row from GME_BATCH_STEP_RESOURCES
1596 x_return_status reflects return status of the API
1597 =============================================================================================*/
1598 PROCEDURE insert_batchstep_rsrc (
1599 p_batchstep_resource_rec IN gme_batch_step_resources%ROWTYPE
1600 ,x_batchstep_resource_rec OUT NOCOPY gme_batch_step_resources%ROWTYPE
1601 ,x_return_status OUT NOCOPY VARCHAR2)
1602 IS
1603 l_api_name CONSTANT VARCHAR2 (30) := 'insert_batchstep_rsrc';
1604 l_batchstep_resource_rec gme_batch_step_resources%ROWTYPE;
1605 l_batchstep_resource_out_rec gme_batch_step_resources%ROWTYPE;
1606 l_batch_header gme_batch_header%ROWTYPE;
1607 l_batch_header_out gme_batch_header%ROWTYPE;
1608 l_return_status VARCHAR2 (2);
1609 l_rsrc_trans_count NUMBER;
1610 l_capacity_constraint NUMBER;
1611 l_max_step_capacity NUMBER;
1612
1613 -- Define CURSORS
1614 CURSOR cur_get_step_dtls (v_batchstep_id NUMBER)
1615 IS
1616 SELECT max_step_capacity
1617 FROM gme_batch_steps
1618 WHERE batchstep_id = v_batchstep_id;
1619
1620 CURSOR cur_get_rsrc_hdr (v_resources VARCHAR2)
1621 IS
1622 SELECT capacity_constraint
1623 FROM cr_rsrc_mst
1624 WHERE resources = v_resources;
1625
1626 validation_failure EXCEPTION;
1627 rsrc_insert_failure EXCEPTION;
1628 rsrc_not_found EXCEPTION;
1629 error_condition EXCEPTION;
1630 BEGIN
1631 IF g_debug <= gme_debug.g_log_procedure THEN
1632 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1633 || l_api_name);
1634 END IF;
1635
1636 /* Initially let us assign the return status to success */
1637 x_return_status := fnd_api.g_ret_sts_success;
1638 l_batchstep_resource_rec := p_batchstep_resource_rec;
1639
1640 IF l_batchstep_resource_rec.offset_interval IS NULL THEN
1641 l_batchstep_resource_rec.offset_interval := 0;
1642 END IF;
1643
1644 -- load temp table so that save_batch routine does resource txn consolidation
1645 -- since we are inserting a new resource rsrc txn temp table would not have any data in it
1646 -- after inserting the resource we would have corresponding txn inserted
1647 l_batch_header.batch_id := l_batchstep_resource_rec.batch_id;
1648
1649 IF NOT gme_batch_header_dbl.fetch_row
1650 (p_batch_header => l_batch_header
1651 ,x_batch_header => l_batch_header_out) THEN
1652 RAISE error_condition;
1653 END IF;
1654
1655 l_batch_header := l_batch_header_out;
1656
1657 IF l_batch_header.update_inventory_ind = 'Y' THEN
1658 gme_trans_engine_util.load_rsrc_trans
1659 (p_batch_row => l_batch_header
1660 ,x_rsc_row_count => l_rsrc_trans_count
1661 ,x_return_status => l_return_status);
1662
1663 IF l_return_status <> 'S' THEN
1664 RAISE error_condition;
1665 END IF;
1666 END IF;
1667
1668 gme_resource_engine_pvt.resource_dtl_process
1669 (p_step_resources_rec => l_batchstep_resource_rec
1670 ,p_action_code => 'INSERT'
1671 ,p_check_prim_rsrc => TRUE
1672 ,x_step_resources_rec => l_batchstep_resource_out_rec
1673 ,x_return_status => l_return_status);
1674
1678
1675 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1676 gme_debug.put_line ('after insert ' || l_return_status);
1677 END IF;
1679 IF l_return_status <> 'S' THEN
1680 RAISE rsrc_insert_failure;
1681 ELSE
1682 --Rishi Varma bug 3307549 13-05-2005
1683 gme_batch_step_chg_pvt.set_sequence_dependent_id
1684 (l_batch_header.batch_id);
1685
1686 -- UPDATE rsrc max capacity if required
1687 OPEN cur_get_step_dtls (l_batchstep_resource_rec.batchstep_id);
1688
1689 FETCH cur_get_step_dtls
1690 INTO l_max_step_capacity;
1691
1692 CLOSE cur_get_step_dtls;
1693
1694 OPEN cur_get_rsrc_hdr (l_batchstep_resource_rec.resources);
1695
1696 FETCH cur_get_rsrc_hdr
1697 INTO l_capacity_constraint;
1698
1699 IF cur_get_rsrc_hdr%NOTFOUND THEN
1700 CLOSE cur_get_rsrc_hdr;
1701
1702 fnd_message.set_name ('GMD', 'FM_BAD_RESOURCE');
1703 fnd_msg_pub.ADD;
1704 RAISE rsrc_not_found;
1705 END IF;
1706
1707 CLOSE cur_get_rsrc_hdr;
1708
1709 IF ( l_capacity_constraint = 1
1710 AND l_batchstep_resource_rec.max_capacity < l_max_step_capacity) THEN
1711 -- CALL DBL with updated max_capacity
1712 UPDATE gme_batch_steps
1713 SET max_step_capacity = l_batchstep_resource_rec.max_capacity
1714 ,last_update_date = gme_common_pvt.g_timestamp
1715 ,last_updated_by = gme_common_pvt.g_user_ident
1716 ,last_update_login = gme_common_pvt.g_login_id
1717 WHERE batchstep_id = l_batchstep_resource_rec.batchstep_id
1718 AND batch_id = l_batchstep_resource_rec.batch_id;
1719 END IF;
1720 END IF;
1721
1722 x_batchstep_resource_rec := l_batchstep_resource_out_rec;
1723
1724 IF g_debug <= gme_debug.g_log_statement THEN
1725 gme_debug.put_line ( ' Completed '
1726 || l_api_name
1727 || ' at '
1728 || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS') );
1729 END IF;
1730 EXCEPTION
1731 WHEN validation_failure OR rsrc_not_found OR rsrc_insert_failure OR error_condition THEN
1732 x_return_status := fnd_api.g_ret_sts_error;
1733 WHEN OTHERS THEN
1734 x_return_status := fnd_api.g_ret_sts_unexp_error;
1735 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1736 END insert_batchstep_rsrc;
1737
1738 /*===========================================================================================
1739 Procedure
1740 update_batchstep_rsrc
1741 Description
1742 Procedure to update resource for an activity
1743 Parameters
1744 p_batchstep_resource_rec Input Row from GME_BATCH_STEP_RESOURCES
1745 x_batchstep_resource_rec Output Row from GME_BATCH_STEP_RESOURCES
1746 x_return_status reflects return status of the API
1747 History
1748 Inventory Convergence Project - March 2005
1749 =============================================================================================*/
1753 ,x_return_status OUT NOCOPY VARCHAR2)
1750 PROCEDURE update_batchstep_rsrc (
1751 p_batchstep_resource_rec IN gme_batch_step_resources%ROWTYPE
1752 ,x_batchstep_resource_rec OUT NOCOPY gme_batch_step_resources%ROWTYPE
1754 IS
1755 l_api_name CONSTANT VARCHAR2 (30) := 'update_batchstep_rsrc';
1756 l_dummy NUMBER;
1757 l_rsrc_id NUMBER;
1758 l_batch_id NUMBER;
1759 l_activity_id NUMBER;
1760 l_batchstep_id NUMBER;
1761 l_batch_status NUMBER;
1762 l_step_status NUMBER;
1763 l_capacity_constraint NUMBER;
1764 l_return_status VARCHAR2 (2);
1765 l_max_step_capacity NUMBER;
1766 l_batch_asqc NUMBER;
1767 l_field_updated BOOLEAN := FALSE;
1768 l_resource VARCHAR2 (16);
1769 l_act_plan_start_date DATE;
1770 l_act_plan_cmplt_date DATE;
1771 l_inv_trans_count NUMBER;
1772 l_rsrc_trans_count NUMBER;
1773 l_count_int NUMBER (10);
1774 l_flex_validate BOOLEAN := FALSE;
1775 l_batchstep_resource_rec gme_batch_step_resources%ROWTYPE;
1776 l_batchstep_resource_out_rec gme_batch_step_resources%ROWTYPE;
1777 l_batch_header gme_batch_header%ROWTYPE;
1778
1779 /* CURSOR DECLARATIONS
1780 ====================== */
1781 CURSOR cur_get_step_dtl (v_resource_id NUMBER)
1782 IS
1783 SELECT a.batchstep_id, a.step_status, b.batchstep_activity_id
1784 ,a.batch_id, b.resources
1785 FROM gme_batch_steps a, gme_batch_step_resources b
1786 WHERE b.batchstep_resource_id = v_resource_id
1787 AND a.batch_id = b.batch_id
1788 AND a.batchstep_id = b.batchstep_id;
1789
1790 CURSOR cur_get_step_capacity (v_batchstep_id NUMBER)
1791 IS
1792 SELECT max_step_capacity
1793 FROM gme_batch_steps;
1794
1795 CURSOR cur_get_rsrc_dtl (v_resources VARCHAR2, v_orgn_code VARCHAR2)
1796 IS
1797 SELECT capacity_constraint
1798 FROM cr_rsrc_dtl
1799 WHERE resources = v_resources AND orgn_code = v_orgn_code;
1800
1801 CURSOR cur_get_rsrc_hdr (v_resources VARCHAR2)
1802 IS
1803 SELECT capacity_constraint
1804 FROM cr_rsrc_mst
1805 WHERE resources = v_resources;
1806
1807 CURSOR cur_validate_batch_type (v_rsrc_id NUMBER)
1808 IS
1809 SELECT 1
1810 FROM gme_batch_header a, gme_batch_step_resources b
1811 WHERE a.batch_id = b.batch_id
1812 AND b.batchstep_resource_id = v_rsrc_id
1813 AND a.batch_type = 10;
1814
1815 rsrc_update_failure EXCEPTION;
1816 input_param_missing EXCEPTION;
1817 validate_param_failed EXCEPTION;
1818 rsrc_fetch_error EXCEPTION;
1819 process_qty_error EXCEPTION;
1820 rsrc_not_valid EXCEPTION;
1824 invalid_scale_type EXCEPTION;
1821 analysis_code_not_found EXCEPTION;
1822 cost_cmpnt_not_found EXCEPTION;
1823 invalid_action EXCEPTION;
1825 invalid_prim_rsrc_ind EXCEPTION;
1826 invalid_date EXCEPTION;
1827 date_outside_range EXCEPTION;
1828 rsrc_not_found EXCEPTION;
1829 validation_failure EXCEPTION;
1830 no_change EXCEPTION;
1831 invalid_step_status EXCEPTION;
1832 error_condition EXCEPTION;
1833 BEGIN
1834 IF g_debug <= gme_debug.g_log_procedure THEN
1835 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1836 || l_api_name);
1837 END IF;
1838
1839 /* Initially let us assign the return status to success */
1840 x_return_status := fnd_api.g_ret_sts_success;
1841 /* Get Batch step resource info */
1842 l_rsrc_id := p_batchstep_resource_rec.batchstep_resource_id;
1843
1844 OPEN cur_get_step_dtl (l_rsrc_id);
1845
1846 FETCH cur_get_step_dtl
1847 INTO l_batchstep_id, l_step_status, l_activity_id, l_batch_id
1848 ,l_resource;
1849
1850 IF cur_get_step_dtl%NOTFOUND THEN
1851 CLOSE cur_get_step_dtl;
1852
1853 gme_common_pvt.log_message ('GME_INVALID_RSRC_ID');
1854 RAISE rsrc_not_valid;
1855 END IF;
1856
1857 CLOSE cur_get_step_dtl;
1858
1859 -- make sure resource id does not belong to an FPO
1860 OPEN cur_validate_batch_type (l_rsrc_id);
1861
1862 FETCH cur_validate_batch_type
1863 INTO l_dummy;
1864
1865 IF cur_validate_batch_type%FOUND THEN
1866 CLOSE cur_validate_batch_type;
1867
1868 gme_common_pvt.log_message ('GME_FPO_RSRC_NO_EDIT');
1869 RAISE rsrc_not_valid;
1870 END IF;
1871
1872 CLOSE cur_validate_batch_type;
1873
1874 -- load temp table so that save_batch routine does resource txn consolidation
1875 l_batch_header.batch_id := p_batchstep_resource_rec.batch_id;
1876
1877 IF NOT gme_batch_header_dbl.fetch_row (p_batch_header => l_batch_header
1878 ,x_batch_header => l_batch_header) THEN
1879 RAISE error_condition;
1880 END IF;
1881
1882 IF l_batch_header.update_inventory_ind = 'Y' THEN
1883 gme_trans_engine_util.load_rsrc_trans
1884 (p_batch_row => l_batch_header
1885 ,x_rsc_row_count => l_rsrc_trans_count
1886 ,x_return_status => l_return_status);
1887
1888 IF l_return_status <> x_return_status THEN
1889 RAISE error_condition;
1890 END IF;
1891 END IF;
1892
1893 gme_resource_engine_pvt.resource_dtl_process
1894 (p_step_resources_rec => p_batchstep_resource_rec
1895 ,p_action_code => 'UPDATE'
1896 ,p_check_prim_rsrc => TRUE
1897 ,x_step_resources_rec => l_batchstep_resource_out_rec
1898 ,x_return_status => l_return_status);
1899
1900 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1901 gme_debug.put_line ('aft upd' || l_return_status);
1902 END IF;
1903
1904 IF l_return_status <> 'S' THEN
1905 RAISE rsrc_update_failure;
1906 ELSE
1907 -- UPDATE rsrc max capacity if required after fetching capacity constraint from resource
1908 OPEN cur_get_step_capacity (p_batchstep_resource_rec.batchstep_id);
1909
1910 FETCH cur_get_step_capacity
1911 INTO l_max_step_capacity;
1912
1913 CLOSE cur_get_step_capacity;
1914
1915 OPEN cur_get_rsrc_dtl (l_resource
1916 ,p_batchstep_resource_rec.organization_id);
1917
1918 FETCH cur_get_rsrc_dtl
1919 INTO l_capacity_constraint;
1920
1921 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1922 gme_debug.put_line ('after rsrc dtl fetch ');
1923 END IF;
1924
1925 IF (cur_get_rsrc_dtl%NOTFOUND) THEN
1926 OPEN cur_get_rsrc_hdr (l_resource);
1927
1928 FETCH cur_get_rsrc_hdr
1929 INTO l_capacity_constraint;
1930
1931 IF cur_get_rsrc_hdr%NOTFOUND THEN
1932 CLOSE cur_get_rsrc_dtl;
1933
1934 CLOSE cur_get_rsrc_hdr;
1935
1936 fnd_message.set_name ('GMD', 'FM_BAD_RESOURCE');
1937 fnd_msg_pub.ADD;
1938 RAISE rsrc_not_found;
1939 END IF;
1940
1941 CLOSE cur_get_rsrc_hdr;
1942 END IF;
1943
1944 CLOSE cur_get_rsrc_dtl;
1945
1946 IF ( l_capacity_constraint = 1
1947 AND l_batchstep_resource_out_rec.max_capacity <
1948 l_max_step_capacity) THEN
1949 -- CALL DBL with updated max_capacity
1950 UPDATE gme_batch_steps
1951 SET max_step_capacity =
1952 l_batchstep_resource_out_rec.max_capacity
1953 WHERE batchstep_id = l_batchstep_id AND batch_id = l_batch_id;
1954 END IF;
1955 END IF;
1956
1957 x_batchstep_resource_rec := l_batchstep_resource_out_rec;
1958
1959 IF g_debug <= gme_debug.g_log_statement THEN
1960 gme_debug.put_line ( ' Completed '
1961 || l_api_name
1962 || ' at '
1966 WHEN validation_failure OR analysis_code_not_found OR cost_cmpnt_not_found OR rsrc_fetch_error THEN
1963 || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS') );
1964 END IF;
1965 EXCEPTION
1967 x_return_status := fnd_api.g_ret_sts_error;
1968 WHEN rsrc_not_valid OR rsrc_update_failure OR input_param_missing THEN
1969 x_return_status := fnd_api.g_ret_sts_error;
1970 WHEN rsrc_not_found OR validate_param_failed OR invalid_action OR invalid_scale_type OR process_qty_error OR invalid_date OR date_outside_range OR invalid_prim_rsrc_ind OR no_change OR invalid_step_status OR error_condition THEN
1971 x_return_status := fnd_api.g_ret_sts_error;
1972 WHEN OTHERS THEN
1973 x_return_status := fnd_api.g_ret_sts_unexp_error;
1974 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1975 END update_batchstep_rsrc;
1976
1977 /*===========================================================================================
1978 Procedure
1979 delete_batchstep_rsrc
1980 Description
1981 Procedure to delete batchstep resource
1982 Parameters
1983 p_batchstep_resource_rec batchstep resource row targetted for deletion
1984 x_return_status reflects return status of the API
1985 =============================================================================================*/
1986 PROCEDURE delete_batchstep_rsrc (
1987 p_batchstep_resource_rec IN gme_batch_step_resources%ROWTYPE
1988 ,x_return_status OUT NOCOPY VARCHAR2)
1989 IS
1990 l_api_name CONSTANT VARCHAR2 (30) := 'delete_batchstep_rsrc';
1991 l_batchstep_resource_rec gme_batch_step_resources%ROWTYPE;
1992 l_batch_header gme_batch_header%ROWTYPE;
1993 l_return_status VARCHAR2 (2);
1994 l_rsrc_trans_count NUMBER;
1995 error_deleting_rsrc EXCEPTION;
1996 error_condition EXCEPTION;
1997 BEGIN
1998 IF g_debug <= gme_debug.g_log_procedure THEN
1999 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
2000 || l_api_name);
2001 END IF;
2002
2003 /* Initially let us assign the return status to success */
2004 x_return_status := fnd_api.g_ret_sts_success;
2005 -- load temp table so that save_batch routine does resource txn consolidation
2006 l_batch_header.batch_id := p_batchstep_resource_rec.batch_id;
2007
2008 IF NOT gme_batch_header_dbl.fetch_row (p_batch_header => l_batch_header
2009 ,x_batch_header => l_batch_header) THEN
2010 gme_common_pvt.log_message ('GME_BATCH_NOT_FOUND');
2011 RAISE error_condition;
2012 END IF;
2013
2014 IF l_batch_header.update_inventory_ind = 'Y' THEN
2015 gme_trans_engine_util.load_rsrc_trans
2016 (p_batch_row => l_batch_header
2017 ,x_rsc_row_count => l_rsrc_trans_count
2018 ,x_return_status => l_return_status);
2019
2020 IF l_return_status <> x_return_status THEN
2021 RAISE error_condition;
2022 END IF;
2023 END IF;
2024
2025 gme_resource_engine_pvt.resource_dtl_process
2026 (p_step_resources_rec => p_batchstep_resource_rec
2027 ,p_action_code => 'DELETE'
2028 ,p_check_prim_rsrc => TRUE
2029 ,x_step_resources_rec => l_batchstep_resource_rec
2030 ,x_return_status => l_return_status);
2031
2032 IF g_debug <= gme_debug.g_log_statement THEN
2033 gme_debug.put_line ( 'delete batchsetp resource returns '
2034 || l_return_status);
2035 END IF;
2036
2037 IF l_return_status <> 'S' THEN
2038 RAISE error_deleting_rsrc;
2039 END IF;
2040
2041 IF g_debug <= gme_debug.g_log_statement THEN
2042 gme_debug.put_line ( ' Completed '
2043 || l_api_name
2044 || ' at '
2045 || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS') );
2046 END IF;
2047 EXCEPTION
2048 WHEN error_deleting_rsrc OR error_condition THEN
2049 x_return_status := fnd_api.g_ret_sts_error;
2050 WHEN OTHERS THEN
2051 x_return_status := fnd_api.g_ret_sts_unexp_error;
2052 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2053 END delete_batchstep_rsrc;
2054
2055 FUNCTION date_within_activity_dates (
2056 p_batchstep_activity_id NUMBER
2057 ,p_step_status NUMBER
2058 ,p_date DATE)
2059 RETURN BOOLEAN
2060 IS
2061 l_api_name CONSTANT VARCHAR2 (30) := 'date_within_activity_dates';
2062 l_plan_start_date DATE;
2063 l_plan_cmplt_date DATE;
2064 l_actual_start_date DATE;
2065 l_actual_cmplt_date DATE;
2066
2067 CURSOR cur_get_act_dates
2068 IS
2069 SELECT plan_start_date, plan_cmplt_date, actual_start_date
2070 ,actual_cmplt_date
2071 FROM gme_batch_step_activities
2072 WHERE batchstep_activity_id = p_batchstep_activity_id;
2073 BEGIN
2074 IF g_debug <= gme_debug.g_log_procedure THEN
2075 gme_debug.put_line ('Entering api ' ||
2076 g_pkg_name || '.' ||
2077 l_api_name);
2078 gme_debug.put_line ('Input step status is ' ||
2079 p_step_status ||
2080 'Input batchstep activity id is '||
2081 p_batchstep_activity_id);
2082 END IF;
2083
2084 OPEN cur_get_act_dates;
2085
2089
2086 FETCH cur_get_act_dates
2087 INTO l_plan_start_date, l_plan_cmplt_date, l_actual_start_date
2088 ,l_actual_cmplt_date;
2090 CLOSE cur_get_act_dates;
2091
2092 IF (g_debug <= gme_debug.g_log_statement) THEN
2093 gme_debug.put_line ( g_pkg_name
2094 || '.'
2095 || l_api_name
2096 || ':'
2097 || 'Input date =>'
2098 || p_date
2099 || 'plan start =>'
2100 || l_plan_start_date
2101 || 'plan cmplt =>'
2102 || l_plan_cmplt_date
2103 || 'actual start=>'
2104 || l_actual_start_date
2105 || 'actual cmplt=>'
2106 || l_actual_cmplt_date);
2107 END IF;
2108
2109 --Pawan Kumar added the NVL
2110 IF p_step_status = 1 THEN
2111 IF ( p_date >= l_plan_start_date
2112 AND p_date <= NVL (l_plan_cmplt_date, SYSDATE) ) THEN
2113 RETURN TRUE;
2114 ELSE
2115 RETURN FALSE;
2116 END IF;
2117 END IF;
2118
2119 IF p_step_status = 3 THEN
2120 IF ( p_date >= l_actual_start_date
2121 AND p_date <= NVL (l_actual_cmplt_date, SYSDATE) ) THEN
2122 RETURN TRUE;
2123 ELSE
2124 RETURN FALSE;
2125 END IF;
2126 END IF;
2127 END date_within_activity_dates;
2128
2129 FUNCTION lookup_code_valid (p_lookup_type VARCHAR2, p_lookup_code VARCHAR2)
2130 RETURN BOOLEAN
2131 IS
2132 CURSOR cur_validate_from_lookup
2133 IS
2134 SELECT 1
2135 FROM gem_lookups
2136 WHERE lookup_type = p_lookup_type AND lookup_code = p_lookup_code;
2137
2138 l_dummy NUMBER;
2139 BEGIN
2140 IF p_lookup_type IS NOT NULL AND p_lookup_code IS NOT NULL THEN
2141 OPEN cur_validate_from_lookup;
2142
2143 FETCH cur_validate_from_lookup
2144 INTO l_dummy;
2145
2146 IF cur_validate_from_lookup%NOTFOUND THEN
2147 CLOSE cur_validate_from_lookup;
2148
2149 RETURN FALSE;
2150 END IF;
2151
2152 CLOSE cur_validate_from_lookup;
2153
2154 RETURN TRUE;
2155 END IF;
2156
2157 RETURN FALSE;
2158 END lookup_code_valid;
2159
2160 /*===========================================================================================
2161 Procedure
2162 consolidate_flexfields
2163 Description
2164 Move input attribute values into the output record structure prior to validation or update
2165 processing.
2166 If p_validate_flexfield is TRUE, just move the value. The validation processing will
2167 do the rest.
2168 If flexfield validation is FALSE, then interpret the input values according to these rules
2169 NULL means update value not supplied so retain the original (old) value
2170 G_MISS_??? means update with a NULL value
2171 NOT NULL update with the supplied value
2172 Parameters
2173 p_new_batchstep_resource_rec input record with values to be applied as updates
2174 p_old_batchstep_resource_rec original record retrieved from the database
2175 p_validate_flexfield indicates whethere validation required or not
2176 x_batchstep_resource_rec Consolidation of the inputs above
2177 x_return_status Return status
2178 =============================================================================================*/
2179 PROCEDURE consolidate_flexfields (
2180 p_new_batchstep_resource_rec IN gme_batch_step_resources%ROWTYPE
2181 ,p_old_batchstep_resource_rec IN gme_batch_step_resources%ROWTYPE
2182 ,p_validate_flexfield IN VARCHAR2
2183 DEFAULT fnd_api.g_false
2184 ,x_batchstep_resource_rec OUT NOCOPY gme_batch_step_resources%ROWTYPE
2185 ,x_return_status OUT NOCOPY VARCHAR2)
2186 IS
2187 l_api_name CONSTANT VARCHAR2 (30) := 'consolidate_flexfields';
2188 l_batchstep_resource_rec gme_batch_step_resources%ROWTYPE;
2189 l_return_status VARCHAR2 (2);
2190 error_condition EXCEPTION;
2191 BEGIN
2192 IF g_debug <= gme_debug.g_log_procedure THEN
2193 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
2194 || l_api_name);
2195 END IF;
2196
2197 /* Initially let us assign the return status to success */
2198 x_return_status := fnd_api.g_ret_sts_success;
2199 l_batchstep_resource_rec := p_old_batchstep_resource_rec;
2200
2201 IF p_validate_flexfield = fnd_api.g_false THEN
2202 -- start with the old record
2203 -- field values will be overwritten by data from the new record as appropriate
2204 -- Follow these rules when interpreting inputs from the new rec:
2205 -- NULL means update value not supplied so retain the original (old) value
2206 -- G_MISS_??? means update with a NULL value
2207 -- NOT NULL update with the supplied value
2208 IF p_new_batchstep_resource_rec.attribute_category IS NOT NULL THEN
2209 IF p_new_batchstep_resource_rec.attribute_category =
2210 fnd_api.g_miss_char THEN
2211 l_batchstep_resource_rec.attribute_category := NULL;
2212 ELSE
2213 l_batchstep_resource_rec.attribute_category :=
2214 p_new_batchstep_resource_rec.attribute_category;
2215 END IF;
2216 END IF;
2220 l_batchstep_resource_rec.attribute1 := NULL;
2217
2218 IF p_new_batchstep_resource_rec.attribute1 IS NOT NULL THEN
2219 IF p_new_batchstep_resource_rec.attribute1 = fnd_api.g_miss_char THEN
2221 ELSE
2222 l_batchstep_resource_rec.attribute1 :=
2223 p_new_batchstep_resource_rec.attribute1;
2224 END IF;
2225 END IF;
2226
2227 IF p_new_batchstep_resource_rec.attribute2 IS NOT NULL THEN
2228 IF p_new_batchstep_resource_rec.attribute2 = fnd_api.g_miss_char THEN
2229 l_batchstep_resource_rec.attribute2 := NULL;
2230 ELSE
2231 l_batchstep_resource_rec.attribute2 :=
2232 p_new_batchstep_resource_rec.attribute2;
2233 END IF;
2234 END IF;
2235
2236 IF p_new_batchstep_resource_rec.attribute3 IS NOT NULL THEN
2237 IF p_new_batchstep_resource_rec.attribute3 = fnd_api.g_miss_char THEN
2238 l_batchstep_resource_rec.attribute3 := NULL;
2239 ELSE
2240 l_batchstep_resource_rec.attribute3 :=
2241 p_new_batchstep_resource_rec.attribute3;
2242 END IF;
2243 END IF;
2244
2245 IF p_new_batchstep_resource_rec.attribute4 IS NOT NULL THEN
2246 IF p_new_batchstep_resource_rec.attribute4 = fnd_api.g_miss_char THEN
2247 l_batchstep_resource_rec.attribute4 := NULL;
2248 ELSE
2249 l_batchstep_resource_rec.attribute4 :=
2250 p_new_batchstep_resource_rec.attribute4;
2251 END IF;
2252 END IF;
2253
2254 IF p_new_batchstep_resource_rec.attribute5 IS NOT NULL THEN
2255 IF p_new_batchstep_resource_rec.attribute5 = fnd_api.g_miss_char THEN
2256 l_batchstep_resource_rec.attribute5 := NULL;
2257 ELSE
2258 l_batchstep_resource_rec.attribute5 :=
2259 p_new_batchstep_resource_rec.attribute5;
2260 END IF;
2261 END IF;
2262
2263 IF p_new_batchstep_resource_rec.attribute6 IS NOT NULL THEN
2264 IF p_new_batchstep_resource_rec.attribute6 = fnd_api.g_miss_char THEN
2265 l_batchstep_resource_rec.attribute6 := NULL;
2266 ELSE
2267 l_batchstep_resource_rec.attribute6 :=
2268 p_new_batchstep_resource_rec.attribute6;
2269 END IF;
2270 END IF;
2271
2272 IF p_new_batchstep_resource_rec.attribute7 IS NOT NULL THEN
2273 IF p_new_batchstep_resource_rec.attribute7 = fnd_api.g_miss_char THEN
2274 l_batchstep_resource_rec.attribute7 := NULL;
2275 ELSE
2276 l_batchstep_resource_rec.attribute7 :=
2277 p_new_batchstep_resource_rec.attribute7;
2278 END IF;
2279 END IF;
2280
2281 IF p_new_batchstep_resource_rec.attribute8 IS NOT NULL THEN
2282 IF p_new_batchstep_resource_rec.attribute8 = fnd_api.g_miss_char THEN
2283 l_batchstep_resource_rec.attribute8 := NULL;
2284 ELSE
2285 l_batchstep_resource_rec.attribute8 :=
2286 p_new_batchstep_resource_rec.attribute8;
2287 END IF;
2288 END IF;
2289
2290 IF p_new_batchstep_resource_rec.attribute9 IS NOT NULL THEN
2291 IF p_new_batchstep_resource_rec.attribute9 = fnd_api.g_miss_char THEN
2292 l_batchstep_resource_rec.attribute9 := NULL;
2293 ELSE
2294 l_batchstep_resource_rec.attribute9 :=
2295 p_new_batchstep_resource_rec.attribute9;
2296 END IF;
2297 END IF;
2298
2299 IF p_new_batchstep_resource_rec.attribute10 IS NOT NULL THEN
2300 IF p_new_batchstep_resource_rec.attribute10 = fnd_api.g_miss_char THEN
2301 l_batchstep_resource_rec.attribute10 := NULL;
2302 ELSE
2303 l_batchstep_resource_rec.attribute10 :=
2304 p_new_batchstep_resource_rec.attribute10;
2305 END IF;
2306 END IF;
2307
2308 IF p_new_batchstep_resource_rec.attribute11 IS NOT NULL THEN
2309 IF p_new_batchstep_resource_rec.attribute11 = fnd_api.g_miss_char THEN
2310 l_batchstep_resource_rec.attribute11 := NULL;
2311 ELSE
2312 l_batchstep_resource_rec.attribute11 :=
2313 p_new_batchstep_resource_rec.attribute11;
2314 END IF;
2315 END IF;
2316
2317 IF p_new_batchstep_resource_rec.attribute12 IS NOT NULL THEN
2318 IF p_new_batchstep_resource_rec.attribute12 = fnd_api.g_miss_char THEN
2319 l_batchstep_resource_rec.attribute12 := NULL;
2320 ELSE
2321 l_batchstep_resource_rec.attribute12 :=
2322 p_new_batchstep_resource_rec.attribute12;
2323 END IF;
2324 END IF;
2325
2326 IF p_new_batchstep_resource_rec.attribute13 IS NOT NULL THEN
2327 IF p_new_batchstep_resource_rec.attribute13 = fnd_api.g_miss_char THEN
2328 l_batchstep_resource_rec.attribute13 := NULL;
2329 ELSE
2330 l_batchstep_resource_rec.attribute13 :=
2331 p_new_batchstep_resource_rec.attribute13;
2332 END IF;
2333 END IF;
2334
2335 IF p_new_batchstep_resource_rec.attribute14 IS NOT NULL THEN
2336 IF p_new_batchstep_resource_rec.attribute14 = fnd_api.g_miss_char THEN
2337 l_batchstep_resource_rec.attribute14 := NULL;
2338 ELSE
2342 END IF;
2339 l_batchstep_resource_rec.attribute14 :=
2340 p_new_batchstep_resource_rec.attribute14;
2341 END IF;
2343
2344 IF p_new_batchstep_resource_rec.attribute15 IS NOT NULL THEN
2345 IF p_new_batchstep_resource_rec.attribute15 = fnd_api.g_miss_char THEN
2346 l_batchstep_resource_rec.attribute15 := NULL;
2347 ELSE
2348 l_batchstep_resource_rec.attribute15 :=
2349 p_new_batchstep_resource_rec.attribute15;
2350 END IF;
2351 END IF;
2352
2353 IF p_new_batchstep_resource_rec.attribute16 IS NOT NULL THEN
2354 IF p_new_batchstep_resource_rec.attribute16 = fnd_api.g_miss_char THEN
2355 l_batchstep_resource_rec.attribute16 := NULL;
2356 ELSE
2357 l_batchstep_resource_rec.attribute16 :=
2358 p_new_batchstep_resource_rec.attribute16;
2359 END IF;
2360 END IF;
2361
2362 IF p_new_batchstep_resource_rec.attribute17 IS NOT NULL THEN
2363 IF p_new_batchstep_resource_rec.attribute17 = fnd_api.g_miss_char THEN
2364 l_batchstep_resource_rec.attribute17 := NULL;
2365 ELSE
2366 l_batchstep_resource_rec.attribute17 :=
2367 p_new_batchstep_resource_rec.attribute17;
2368 END IF;
2369 END IF;
2370
2371 IF p_new_batchstep_resource_rec.attribute18 IS NOT NULL THEN
2372 IF p_new_batchstep_resource_rec.attribute18 = fnd_api.g_miss_char THEN
2373 l_batchstep_resource_rec.attribute18 := NULL;
2374 ELSE
2375 l_batchstep_resource_rec.attribute18 :=
2376 p_new_batchstep_resource_rec.attribute18;
2377 END IF;
2378 END IF;
2379
2380 IF p_new_batchstep_resource_rec.attribute19 IS NOT NULL THEN
2381 IF p_new_batchstep_resource_rec.attribute19 = fnd_api.g_miss_char THEN
2382 l_batchstep_resource_rec.attribute19 := NULL;
2383 ELSE
2384 l_batchstep_resource_rec.attribute19 :=
2385 p_new_batchstep_resource_rec.attribute19;
2386 END IF;
2387 END IF;
2388
2389 IF p_new_batchstep_resource_rec.attribute20 IS NOT NULL THEN
2390 IF p_new_batchstep_resource_rec.attribute20 = fnd_api.g_miss_char THEN
2391 l_batchstep_resource_rec.attribute20 := NULL;
2392 ELSE
2393 l_batchstep_resource_rec.attribute20 :=
2394 p_new_batchstep_resource_rec.attribute20;
2395 END IF;
2396 END IF;
2397
2398 IF p_new_batchstep_resource_rec.attribute21 IS NOT NULL THEN
2399 IF p_new_batchstep_resource_rec.attribute21 = fnd_api.g_miss_char THEN
2400 l_batchstep_resource_rec.attribute21 := NULL;
2401 ELSE
2402 l_batchstep_resource_rec.attribute21 :=
2403 p_new_batchstep_resource_rec.attribute21;
2404 END IF;
2405 END IF;
2406
2407 IF p_new_batchstep_resource_rec.attribute22 IS NOT NULL THEN
2408 IF p_new_batchstep_resource_rec.attribute22 = fnd_api.g_miss_char THEN
2409 l_batchstep_resource_rec.attribute22 := NULL;
2410 ELSE
2411 l_batchstep_resource_rec.attribute22 :=
2412 p_new_batchstep_resource_rec.attribute22;
2413 END IF;
2414 END IF;
2415
2416 IF p_new_batchstep_resource_rec.attribute23 IS NOT NULL THEN
2417 IF p_new_batchstep_resource_rec.attribute23 = fnd_api.g_miss_char THEN
2418 l_batchstep_resource_rec.attribute23 := NULL;
2419 ELSE
2420 l_batchstep_resource_rec.attribute23 :=
2421 p_new_batchstep_resource_rec.attribute23;
2422 END IF;
2423 END IF;
2424
2425 IF p_new_batchstep_resource_rec.attribute24 IS NOT NULL THEN
2426 IF p_new_batchstep_resource_rec.attribute24 = fnd_api.g_miss_char THEN
2427 l_batchstep_resource_rec.attribute24 := NULL;
2428 ELSE
2429 l_batchstep_resource_rec.attribute24 :=
2430 p_new_batchstep_resource_rec.attribute24;
2431 END IF;
2432 END IF;
2433
2434 IF p_new_batchstep_resource_rec.attribute25 IS NOT NULL THEN
2435 IF p_new_batchstep_resource_rec.attribute25 = fnd_api.g_miss_char THEN
2436 l_batchstep_resource_rec.attribute25 := NULL;
2437 ELSE
2438 l_batchstep_resource_rec.attribute25 :=
2439 p_new_batchstep_resource_rec.attribute25;
2440 END IF;
2441 END IF;
2442
2443 IF p_new_batchstep_resource_rec.attribute26 IS NOT NULL THEN
2444 IF p_new_batchstep_resource_rec.attribute26 = fnd_api.g_miss_char THEN
2445 l_batchstep_resource_rec.attribute26 := NULL;
2446 ELSE
2447 l_batchstep_resource_rec.attribute26 :=
2448 p_new_batchstep_resource_rec.attribute26;
2449 END IF;
2450 END IF;
2451
2452 IF p_new_batchstep_resource_rec.attribute27 IS NOT NULL THEN
2453 IF p_new_batchstep_resource_rec.attribute27 = fnd_api.g_miss_char THEN
2454 l_batchstep_resource_rec.attribute27 := NULL;
2455 ELSE
2456 l_batchstep_resource_rec.attribute27 :=
2457 p_new_batchstep_resource_rec.attribute27;
2458 END IF;
2459 END IF;
2460
2464 ELSE
2461 IF p_new_batchstep_resource_rec.attribute28 IS NOT NULL THEN
2462 IF p_new_batchstep_resource_rec.attribute28 = fnd_api.g_miss_char THEN
2463 l_batchstep_resource_rec.attribute28 := NULL;
2465 l_batchstep_resource_rec.attribute28 :=
2466 p_new_batchstep_resource_rec.attribute28;
2467 END IF;
2468 END IF;
2469
2470 IF p_new_batchstep_resource_rec.attribute29 IS NOT NULL THEN
2471 IF p_new_batchstep_resource_rec.attribute29 = fnd_api.g_miss_char THEN
2472 l_batchstep_resource_rec.attribute29 := NULL;
2473 ELSE
2474 l_batchstep_resource_rec.attribute29 :=
2475 p_new_batchstep_resource_rec.attribute29;
2476 END IF;
2477 END IF;
2478
2479 IF p_new_batchstep_resource_rec.attribute30 IS NOT NULL THEN
2480 IF p_new_batchstep_resource_rec.attribute30 = fnd_api.g_miss_char THEN
2481 l_batchstep_resource_rec.attribute30 := NULL;
2482 ELSE
2483 l_batchstep_resource_rec.attribute30 :=
2484 p_new_batchstep_resource_rec.attribute30;
2485 END IF;
2486 END IF;
2487 ELSE
2488 /* validate flexfield is set True, so flex field handling is not dealt with here */
2489 /* It will be dealt with by the validation procedure. */
2490 /* On this basis, retain the new update values */
2491 l_batchstep_resource_rec.attribute_category :=
2492 p_new_batchstep_resource_rec.attribute_category;
2493 l_batchstep_resource_rec.attribute1 :=
2494 p_new_batchstep_resource_rec.attribute1;
2495 l_batchstep_resource_rec.attribute2 :=
2496 p_new_batchstep_resource_rec.attribute2;
2497 l_batchstep_resource_rec.attribute3 :=
2498 p_new_batchstep_resource_rec.attribute3;
2499 l_batchstep_resource_rec.attribute4 :=
2500 p_new_batchstep_resource_rec.attribute4;
2501 l_batchstep_resource_rec.attribute5 :=
2502 p_new_batchstep_resource_rec.attribute5;
2503 l_batchstep_resource_rec.attribute6 :=
2504 p_new_batchstep_resource_rec.attribute6;
2505 l_batchstep_resource_rec.attribute7 :=
2506 p_new_batchstep_resource_rec.attribute7;
2507 l_batchstep_resource_rec.attribute8 :=
2508 p_new_batchstep_resource_rec.attribute8;
2509 l_batchstep_resource_rec.attribute9 :=
2510 p_new_batchstep_resource_rec.attribute9;
2511 l_batchstep_resource_rec.attribute10 :=
2512 p_new_batchstep_resource_rec.attribute10;
2513 l_batchstep_resource_rec.attribute11 :=
2514 p_new_batchstep_resource_rec.attribute11;
2515 l_batchstep_resource_rec.attribute12 :=
2516 p_new_batchstep_resource_rec.attribute12;
2517 l_batchstep_resource_rec.attribute13 :=
2518 p_new_batchstep_resource_rec.attribute13;
2519 l_batchstep_resource_rec.attribute14 :=
2520 p_new_batchstep_resource_rec.attribute14;
2521 l_batchstep_resource_rec.attribute15 :=
2522 p_new_batchstep_resource_rec.attribute15;
2523 l_batchstep_resource_rec.attribute16 :=
2524 p_new_batchstep_resource_rec.attribute16;
2525 l_batchstep_resource_rec.attribute17 :=
2526 p_new_batchstep_resource_rec.attribute17;
2527 l_batchstep_resource_rec.attribute18 :=
2528 p_new_batchstep_resource_rec.attribute18;
2529 l_batchstep_resource_rec.attribute19 :=
2530 p_new_batchstep_resource_rec.attribute19;
2531 l_batchstep_resource_rec.attribute20 :=
2532 p_new_batchstep_resource_rec.attribute20;
2533 l_batchstep_resource_rec.attribute21 :=
2534 p_new_batchstep_resource_rec.attribute21;
2535 l_batchstep_resource_rec.attribute22 :=
2536 p_new_batchstep_resource_rec.attribute22;
2537 l_batchstep_resource_rec.attribute23 :=
2538 p_new_batchstep_resource_rec.attribute23;
2539 l_batchstep_resource_rec.attribute24 :=
2540 p_new_batchstep_resource_rec.attribute24;
2541 l_batchstep_resource_rec.attribute25 :=
2542 p_new_batchstep_resource_rec.attribute25;
2543 l_batchstep_resource_rec.attribute26 :=
2544 p_new_batchstep_resource_rec.attribute26;
2545 l_batchstep_resource_rec.attribute27 :=
2546 p_new_batchstep_resource_rec.attribute27;
2547 l_batchstep_resource_rec.attribute28 :=
2548 p_new_batchstep_resource_rec.attribute28;
2549 l_batchstep_resource_rec.attribute29 :=
2550 p_new_batchstep_resource_rec.attribute29;
2551 l_batchstep_resource_rec.attribute30 :=
2552 p_new_batchstep_resource_rec.attribute30;
2553 END IF;
2554
2555 x_batchstep_resource_rec := l_batchstep_resource_rec;
2556
2557 IF g_debug <= gme_debug.g_log_statement THEN
2558 gme_debug.put_line ( ' Completed '
2559 || l_api_name
2560 || ' at '
2561 || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS') );
2562 END IF;
2563 EXCEPTION
2564 WHEN OTHERS THEN
2565 x_return_status := fnd_api.g_ret_sts_unexp_error;
2566 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2567 END consolidate_flexfields;
2568 END gme_batchstep_rsrc_pvt;