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