[Home] [Help]
PACKAGE BODY: APPS.GME_BATCHSTEP_ACT_PVT
Source
1 PACKAGE BODY gme_batchstep_act_pvt AS
2 /* $Header: GMEVACTB.pls 120.2.12020000.2 2012/09/24 17:27:46 gmurator ship $ */
3 /* Global variables */
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_BATCHSTEP_ACT_PVT';
5 /* Global variables */
6 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
7
8 /*===========================================================================================
9 Procedure
10 validate_param
11 Description
12 This procedure is used to validate parameter combo provided to identify a batch step
13 passed to activity APIs
14 Parameters
15 (p_org_code,p_batch_no and step_no ) to uniquely identify a step
16 x_return_status reflects return status of the API
17 =============================================================================================*/
18 PROCEDURE validate_param (
19 p_org_code IN VARCHAR2 := NULL,
20 p_batch_no IN VARCHAR2 := NULL,
21 p_batchstep_no IN NUMBER := NULL,
22 p_activity IN VARCHAR2 := NULL,
23 x_batch_id OUT NOCOPY NUMBER,
24 x_batchstep_id OUT NOCOPY NUMBER,
25 x_activity_id OUT NOCOPY NUMBER,
26 x_batch_status OUT NOCOPY NUMBER,
27 x_step_status OUT NOCOPY NUMBER,
28 x_return_status OUT NOCOPY VARCHAR2
29 )
30 IS
31 l_api_name CONSTANT VARCHAR2 (30) := 'validate_param';
32
33 CURSOR cur_get_batch_dtl (v_organization_id NUMBER, v_batch_no VARCHAR2)
34 IS
35 SELECT batch_id, batch_status
36 FROM gme_batch_header
37 WHERE organization_id = v_organization_id AND batch_no = v_batch_no;
38
39 CURSOR cur_get_batchstep_dtl (v_batch_id NUMBER, v_batchstep_no NUMBER)
40 IS
41 SELECT batchstep_id, step_status
42 FROM gme_batch_steps
43 WHERE batch_id = v_batch_id AND batchstep_no = v_batchstep_no;
44
45 CURSOR cur_get_activity_id (
46 v_step_id NUMBER,
47 v_activity VARCHAR2,
48 v_batch_id NUMBER
49 )
50 IS
51 SELECT batchstep_activity_id
52 FROM gme_batch_step_activities
53 WHERE batchstep_id = v_step_id
54 AND batch_id = v_batch_id
55 AND activity = v_activity;
56 BEGIN
57 IF (NVL (g_debug, 0) IN
58 (gme_debug.g_log_statement, gme_debug.g_log_procedure)
59 )
60 THEN
61 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
62 || 'Entering'
63 );
64 END IF;
65
66 /* Set the return status to success initially */
67 x_return_status := fnd_api.g_ret_sts_success;
68
69 IF p_org_code IS NULL
70 THEN
71 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
72 'FIELD_NAME',
73 'ORGANIZATION'
74 );
75 RAISE fnd_api.g_exc_error;
76 ELSIF p_batch_no IS NULL
77 THEN
78 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
79 'FIELD_NAME',
80 'BATCH NUMBER'
81 );
82 RAISE fnd_api.g_exc_error;
83 ELSIF p_batchstep_no IS NULL
84 THEN
85 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
86 'FIELD_NAME',
87 'BATCH STEP NUMBER'
88 );
89 RAISE fnd_api.g_exc_error;
90 END IF;
91
92 -- Validate input param one by one to see if it identifies an activity correctly
93 OPEN cur_get_batch_dtl (gme_common_pvt.g_organization_id, p_batch_no);
94 FETCH cur_get_batch_dtl INTO x_batch_id, x_batch_status;
95
96 IF cur_get_batch_dtl%NOTFOUND
97 THEN
98 CLOSE cur_get_batch_dtl;
99 gme_common_pvt.log_message ('GME_BATCH_NOT_FOUND');
100 RAISE fnd_api.g_exc_error;
101 END IF;
102
103 CLOSE cur_get_batch_dtl;
104 -- use batch_id to fetch batchstep_id
105 OPEN cur_get_batchstep_dtl (x_batch_id, p_batchstep_no);
106 FETCH cur_get_batchstep_dtl INTO x_batchstep_id, x_step_status;
107
108 IF cur_get_batchstep_dtl%NOTFOUND
109 THEN
110 CLOSE cur_get_batchstep_dtl;
111 gme_common_pvt.log_message ('PC_INV_BATCHSTEP_NO');
112 RAISE fnd_api.g_exc_error;
113 END IF;
114
115 CLOSE cur_get_batchstep_dtl;
116
117 IF p_activity IS NOT NULL
118 THEN
119 -- fetch activity_id if activity is provided
120 OPEN cur_get_activity_id (x_batchstep_id, p_activity, x_batch_id);
121 FETCH cur_get_activity_id INTO x_activity_id;
122
123 IF cur_get_activity_id%NOTFOUND
124 THEN
125 CLOSE cur_get_activity_id;
126 gme_common_pvt.log_message ('GME_STEP_ACTIVITY_NOT_FOUND',
127 'ACTIVITY',
128 p_activity,
129 'STEP_NO',
130 p_batchstep_no
131 );
132 RAISE fnd_api.g_exc_error;
133 END IF;
134
135 CLOSE cur_get_activity_id;
136 END IF;
137
138 IF (NVL (g_debug, 0) IN
139 (gme_debug.g_log_statement, gme_debug.g_log_procedure)
140 )
141 THEN
142 gme_debug.put_line ( g_pkg_name
143 || '.'
144 || l_api_name
145 || ':'
146 || 'Exiting with '
147 || x_return_status
148 );
149 END IF;
150 EXCEPTION
151 WHEN fnd_api.g_exc_error
152 THEN
153 x_return_status := fnd_api.g_ret_sts_error;
154 WHEN fnd_api.g_exc_unexpected_error
155 THEN
156 x_return_status := fnd_api.g_ret_sts_unexp_error;
157
158 IF (NVL (g_debug, 0) > 0)
159 THEN
160 gme_debug.put_line ( g_pkg_name
161 || '.'
162 || l_api_name
163 || ':'
164 || 'UNEXPECTED:'
165 || SQLERRM
166 );
167 END IF;
168 WHEN OTHERS
169 THEN
170 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
171 x_return_status := fnd_api.g_ret_sts_unexp_error;
172
173 IF (NVL (g_debug, 0) > 0)
174 THEN
175 gme_debug.put_line ( g_pkg_name
176 || '.'
177 || l_api_name
178 || ':'
179 || 'OTHERS:'
180 || SQLERRM
181 );
182 END IF;
183 END validate_param;
184
185 /*===========================================================================================
186 Procedure
187 validate_activity_param
188 Description
189 This procedure is used to validate all parameters passed to insert activity API
190 Parameters
191
192 x_return_status reflects return status of the API
193 =============================================================================================*/
194 PROCEDURE validate_activity_param (
195 p_batchstep_activity_rec IN gme_batch_step_activities%ROWTYPE,
196 p_step_id IN NUMBER,
197 p_validate_flexfield IN VARCHAR2
198 DEFAULT fnd_api.g_false,
199 p_action IN VARCHAR2,
200 x_batchstep_activity_rec OUT NOCOPY gme_batch_step_activities%ROWTYPE,
201 x_step_status OUT NOCOPY NUMBER,
202 x_return_status OUT NOCOPY VARCHAR2
203 )
204 IS
205 l_api_name CONSTANT VARCHAR2 (30) := 'validate_activity_param';
206 l_batch_asqc NUMBER;
207 l_batchstep_activity_rec gme_batch_step_activities%ROWTYPE;
208 l_step_dtl gme_batch_steps%ROWTYPE;
209 l_dummy NUMBER;
210 l_activity_factor NUMBER;
211 l_batch_type NUMBER;
212
213 CURSOR cur_check_activity (v_activity VARCHAR2)
214 IS
215 SELECT 1
216 FROM fm_actv_mst
217 WHERE activity = v_activity AND delete_mark = 0;
218
219 CURSOR cur_get_step_dtl (v_step_id NUMBER)
220 IS
221 SELECT *
222 FROM gme_batch_steps
223 WHERE batchstep_id = v_step_id;
224
225 CURSOR cur_get_batch_asqc (v_batch_id NUMBER)
226 IS
227 SELECT automatic_step_calculation
228 FROM gme_batch_header
229 WHERE batch_id = v_batch_id;
230
231 CURSOR cur_check_fpo (v_step_id NUMBER)
232 IS
233 SELECT batch_type
234 FROM gme_batch_header b, gme_batch_steps s
235 WHERE b.batch_id = s.batch_id AND batchstep_id = v_step_id;
236 BEGIN
237 IF (NVL (g_debug, 0) IN
238 (gme_debug.g_log_statement, gme_debug.g_log_procedure)
239 )
240 THEN
241 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
242 || 'Entering'
243 );
244 END IF;
245
246 /* Set the return status to success initially */
247 x_return_status := fnd_api.g_ret_sts_success;
248
249 --{
250 IF (p_action = 'INSERT')
251 THEN
252 -- check FPO
253 OPEN cur_check_fpo (p_step_id);
254 FETCH cur_check_fpo INTO l_batch_type;
255
256 IF l_batch_type = 10
257 THEN
258 CLOSE cur_check_fpo;
259 gme_common_pvt.log_message ('GME_FPO_ACTV_NO_INS');
260 RAISE fnd_api.g_exc_error;
261 END IF;
262
263 CLOSE cur_check_fpo;
264 -- check activity
265 OPEN cur_check_activity (p_batchstep_activity_rec.activity);
266 FETCH cur_check_activity INTO l_dummy;
267
268 IF cur_check_activity%NOTFOUND
269 THEN
270 CLOSE cur_check_activity;
271 gme_common_pvt.log_message ('GME_BAD_ACTIVITY');
272 RAISE fnd_api.g_exc_error;
273 END IF;
274
275 CLOSE cur_check_activity;
276 -- Fetch step info
277 OPEN cur_get_step_dtl (p_step_id);
278 FETCH cur_get_step_dtl INTO l_step_dtl;
279
280 IF cur_get_step_dtl%NOTFOUND
281 THEN
282 CLOSE cur_get_step_dtl;
283 gme_common_pvt.log_message ('GME_BATCH_STEP_NOT_FOUND',
284 'STEP_ID',
285 p_step_id
286 );
287 RAISE fnd_api.g_exc_error;
288 END IF;
289
290 CLOSE cur_get_step_dtl;
291
292 IF l_step_dtl.step_status IN (4, 5)
293 THEN -- Closed or cancelled step
294 gme_common_pvt.log_message ('PC_STEP_STATUS_ERR');
295 RAISE fnd_api.g_exc_error;
296 END IF;
297
298 -- check ASQC property - can't insert into WIP step for ASQC batch
299 OPEN cur_get_batch_asqc (l_step_dtl.batch_id);
300 FETCH cur_get_batch_asqc INTO l_batch_asqc;
301 CLOSE cur_get_batch_asqc;
302
303 IF l_batch_asqc = 1 AND l_step_dtl.step_status = 2
304 THEN
305 gme_common_pvt.log_message ('GME_INVALID_ASQC_ACTION_ACTV');
306 RAISE fnd_api.g_exc_error;
307 END IF;
308
309 -- check plan activity factor
310 IF l_step_dtl.step_status = 1
311 AND p_batchstep_activity_rec.plan_activity_factor IS NULL
312 THEN
313 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
314 'FIELD_NAME',
315 'PLAN_ACTIVITY_FACTOR'
316 );
317 RAISE fnd_api.g_exc_error;
318 END IF;
319
320 -- check actual activity factor
321 IF l_step_dtl.step_status IN (2, 3)
322 AND p_batchstep_activity_rec.actual_activity_factor IS NULL
323 THEN
324 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
325 'FIELD_NAME',
326 'ACTUAL_ACTIVITY_FACTOR'
327 );
328 RAISE fnd_api.g_exc_error;
329 END IF;
330
331 l_batchstep_activity_rec := p_batchstep_activity_rec;
332 l_batchstep_activity_rec.material_ind := NULL;
333
334 IF l_step_dtl.step_status IN (2, 3)
335 THEN
336 l_batchstep_activity_rec.plan_activity_factor := NULL;
337 ELSIF l_step_dtl.step_status = 1
338 THEN
339 l_batchstep_activity_rec.actual_activity_factor := NULL;
340 END IF;
341
342 IF l_step_dtl.step_status IN (1, 2)
343 THEN
344 -- Check that the activity plan dates fall within the step dates.
345 IF p_batchstep_activity_rec.plan_start_date IS NOT NULL
346 AND ( p_batchstep_activity_rec.plan_start_date <
347 l_step_dtl.plan_start_date
348 OR p_batchstep_activity_rec.plan_start_date >
349 l_step_dtl.plan_cmplt_date
350 )
351 THEN
352 gme_common_pvt.log_message ('GME_ACTV_PLAN_DATE',
353 'ACTIVITY',
354 p_batchstep_activity_rec.activity
355 );
356 RAISE fnd_api.g_exc_error;
357 END IF;
358
359 IF p_batchstep_activity_rec.plan_start_date IS NOT NULL
360 AND p_batchstep_activity_rec.plan_cmplt_date IS NOT NULL
361 AND (p_batchstep_activity_rec.plan_start_date >
362 p_batchstep_activity_rec.plan_cmplt_date
363 )
364 THEN
365 gme_common_pvt.log_message ('PM_BADSTARTDATE');
366 RAISE fnd_api.g_exc_error;
367 END IF;
368
369 IF p_batchstep_activity_rec.plan_cmplt_date IS NOT NULL
370 AND (p_batchstep_activity_rec.plan_cmplt_date >
371 l_step_dtl.plan_cmplt_date
372 )
373 THEN
374 gme_common_pvt.log_message ('GME_ACTV_PLAN_DATE',
375 'ACTIVITY',
376 p_batchstep_activity_rec.activity
377 );
378 RAISE fnd_api.g_exc_error;
379 END IF;
380
381 -- Default the plan dates if they were not passed in
382 IF p_batchstep_activity_rec.plan_start_date IS NULL
383 THEN
384 l_batchstep_activity_rec.plan_start_date :=
385 l_step_dtl.plan_start_date
386 + NVL (p_batchstep_activity_rec.offset_interval / 24, 0);
387 END IF;
388
389 IF p_batchstep_activity_rec.plan_cmplt_date IS NULL
390 THEN
391 l_batchstep_activity_rec.plan_cmplt_date :=
392 l_step_dtl.plan_cmplt_date;
393 END IF;
394 ELSE
395 l_batchstep_activity_rec.plan_start_date := NULL;
396 l_batchstep_activity_rec.plan_cmplt_date := NULL;
397 END IF;
398
399 IF l_step_dtl.step_status IN (2, 3)
400 THEN
401 -- Check that actual start date is not in the future
402 IF p_batchstep_activity_rec.actual_start_date IS NOT NULL
403 AND p_batchstep_activity_rec.actual_start_date >
404 gme_common_pvt.g_timestamp
405 THEN
406 gme_common_pvt.log_message (p_message_code => 'SY_NOFUTUREDATE',
407 p_product_code => 'GMA'
408 );
409 RAISE fnd_api.g_exc_error;
410 END IF;
411
412 -- Check that the activity actual dates fall within the step dates.
413 IF p_batchstep_activity_rec.actual_start_date IS NOT NULL
414 AND ( p_batchstep_activity_rec.actual_start_date <
415 l_step_dtl.actual_start_date
416 OR p_batchstep_activity_rec.actual_start_date >
417 l_step_dtl.actual_cmplt_date
418 )
419 THEN
420 gme_common_pvt.log_message ('GME_ACTV_ACTUAL_DATE',
421 'ACTIVITY',
422 p_batchstep_activity_rec.activity
423 );
424 RAISE fnd_api.g_exc_error;
425 END IF;
426
427 -- default actual start date if not supplied
428 IF p_batchstep_activity_rec.actual_start_date IS NULL
429 THEN
430 l_batchstep_activity_rec.actual_start_date :=
431 l_step_dtl.actual_start_date;
432 END IF;
433 ELSE
434 l_batchstep_activity_rec.actual_start_date := NULL;
435 END IF;
436
437 IF l_step_dtl.step_status = 3
438 THEN
439 -- Check that actual cmplt date is not in the future
440 IF p_batchstep_activity_rec.actual_cmplt_date IS NOT NULL
441 AND p_batchstep_activity_rec.actual_cmplt_date >
442 gme_common_pvt.g_timestamp
443 THEN
444 fnd_message.set_name ('GMA', 'SY_NOFUTUREDATE');
445 fnd_msg_pub.ADD;
446 RAISE fnd_api.g_exc_error;
447 END IF;
448
449 IF p_batchstep_activity_rec.actual_start_date IS NOT NULL
450 AND p_batchstep_activity_rec.actual_cmplt_date IS NOT NULL
451 AND (p_batchstep_activity_rec.actual_start_date >
452 p_batchstep_activity_rec.actual_cmplt_date
453 )
454 THEN
455 gme_common_pvt.log_message ('PM_BADSTARTDATE');
456 RAISE fnd_api.g_exc_error;
457 END IF;
458
459 IF p_batchstep_activity_rec.actual_cmplt_date IS NOT NULL
460 AND p_batchstep_activity_rec.actual_cmplt_date >
461 l_step_dtl.actual_cmplt_date
462 THEN
463 gme_common_pvt.log_message ('GME_ACTV_ACTUAL_DATE',
464 'ACTIVITY',
465 p_batchstep_activity_rec.activity
466 );
467 RAISE fnd_api.g_exc_error;
468 END IF;
469
470 IF p_batchstep_activity_rec.actual_cmplt_date IS NULL
471 THEN
472 l_batchstep_activity_rec.actual_cmplt_date :=
473 l_step_dtl.actual_cmplt_date;
474 END IF;
475 ELSE
476 l_batchstep_activity_rec.actual_cmplt_date := NULL;
477 END IF;
478
479 x_step_status := l_step_dtl.step_status;
480
481 -- ensure activity factor is NOT 0
482 IF x_step_status = 1
483 THEN
484 l_activity_factor :=
485 p_batchstep_activity_rec.plan_activity_factor;
486 ELSE
487 l_activity_factor :=
488 p_batchstep_activity_rec.actual_activity_factor;
489 END IF;
490
491 IF l_activity_factor = 0
492 THEN
493 gme_common_pvt.log_message ('GME_ZERO_ACT_FACT');
494 RAISE fnd_api.g_exc_error;
495 END IF;
496
497 --{
498 IF ( (fnd_api.to_boolean (p_validate_flexfield))
499 AND ( p_batchstep_activity_rec.attribute_category IS NOT NULL
500 OR p_batchstep_activity_rec.attribute1 IS NOT NULL
501 OR p_batchstep_activity_rec.attribute2 IS NOT NULL
502 OR p_batchstep_activity_rec.attribute3 IS NOT NULL
503 OR p_batchstep_activity_rec.attribute4 IS NOT NULL
504 OR p_batchstep_activity_rec.attribute5 IS NOT NULL
505 OR p_batchstep_activity_rec.attribute6 IS NOT NULL
506 OR p_batchstep_activity_rec.attribute7 IS NOT NULL
507 OR p_batchstep_activity_rec.attribute8 IS NOT NULL
508 OR p_batchstep_activity_rec.attribute9 IS NOT NULL
509 OR p_batchstep_activity_rec.attribute10 IS NOT NULL
510 OR p_batchstep_activity_rec.attribute11 IS NOT NULL
511 OR p_batchstep_activity_rec.attribute12 IS NOT NULL
512 OR p_batchstep_activity_rec.attribute13 IS NOT NULL
513 OR p_batchstep_activity_rec.attribute14 IS NOT NULL
514 OR p_batchstep_activity_rec.attribute15 IS NOT NULL
515 OR p_batchstep_activity_rec.attribute16 IS NOT NULL
516 OR p_batchstep_activity_rec.attribute17 IS NOT NULL
517 OR p_batchstep_activity_rec.attribute18 IS NOT NULL
518 OR p_batchstep_activity_rec.attribute19 IS NOT NULL
519 OR p_batchstep_activity_rec.attribute20 IS NOT NULL
520 OR p_batchstep_activity_rec.attribute21 IS NOT NULL
521 OR p_batchstep_activity_rec.attribute22 IS NOT NULL
522 OR p_batchstep_activity_rec.attribute23 IS NOT NULL
523 OR p_batchstep_activity_rec.attribute24 IS NOT NULL
524 OR p_batchstep_activity_rec.attribute25 IS NOT NULL
525 OR p_batchstep_activity_rec.attribute26 IS NOT NULL
526 OR p_batchstep_activity_rec.attribute27 IS NOT NULL
527 OR p_batchstep_activity_rec.attribute28 IS NOT NULL
528 OR p_batchstep_activity_rec.attribute29 IS NOT NULL
529 OR p_batchstep_activity_rec.attribute30 IS NOT NULL
530 )
531 )
532 THEN
533 gme_validate_flex_fld_pvt.validate_flex_step_activities (p_step_activities => p_batchstep_activity_rec,
534 x_step_activities => x_batchstep_activity_rec,
535 x_return_status => x_return_status
536 );
537
538 IF (x_return_status = fnd_api.g_ret_sts_error)
539 THEN
540 RAISE fnd_api.g_exc_error;
541 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
542 THEN
543 RAISE fnd_api.g_exc_unexpected_error;
544 END IF;
545
546 l_batchstep_activity_rec.attribute_category :=
547 x_batchstep_activity_rec.attribute_category;
548 l_batchstep_activity_rec.attribute1 :=
549 x_batchstep_activity_rec.attribute1;
550 l_batchstep_activity_rec.attribute2 :=
551 x_batchstep_activity_rec.attribute2;
552 l_batchstep_activity_rec.attribute3 :=
553 x_batchstep_activity_rec.attribute3;
554 l_batchstep_activity_rec.attribute4 :=
555 x_batchstep_activity_rec.attribute4;
556 l_batchstep_activity_rec.attribute5 :=
557 x_batchstep_activity_rec.attribute5;
558 l_batchstep_activity_rec.attribute6 :=
559 x_batchstep_activity_rec.attribute6;
560 l_batchstep_activity_rec.attribute7 :=
561 x_batchstep_activity_rec.attribute7;
562 l_batchstep_activity_rec.attribute8 :=
563 x_batchstep_activity_rec.attribute8;
564 l_batchstep_activity_rec.attribute9 :=
565 x_batchstep_activity_rec.attribute9;
566 l_batchstep_activity_rec.attribute10 :=
567 x_batchstep_activity_rec.attribute10;
568 l_batchstep_activity_rec.attribute11 :=
569 x_batchstep_activity_rec.attribute11;
570 l_batchstep_activity_rec.attribute12 :=
571 x_batchstep_activity_rec.attribute12;
572 l_batchstep_activity_rec.attribute13 :=
573 x_batchstep_activity_rec.attribute13;
574 l_batchstep_activity_rec.attribute14 :=
575 x_batchstep_activity_rec.attribute14;
576 l_batchstep_activity_rec.attribute15 :=
577 x_batchstep_activity_rec.attribute15;
578 l_batchstep_activity_rec.attribute16 :=
579 x_batchstep_activity_rec.attribute16;
580 l_batchstep_activity_rec.attribute17 :=
581 x_batchstep_activity_rec.attribute17;
582 l_batchstep_activity_rec.attribute18 :=
583 x_batchstep_activity_rec.attribute18;
584 l_batchstep_activity_rec.attribute19 :=
585 x_batchstep_activity_rec.attribute19;
586 l_batchstep_activity_rec.attribute20 :=
587 x_batchstep_activity_rec.attribute20;
588 l_batchstep_activity_rec.attribute21 :=
589 x_batchstep_activity_rec.attribute21;
590 l_batchstep_activity_rec.attribute22 :=
591 x_batchstep_activity_rec.attribute22;
592 l_batchstep_activity_rec.attribute23 :=
593 x_batchstep_activity_rec.attribute23;
594 l_batchstep_activity_rec.attribute24 :=
595 x_batchstep_activity_rec.attribute24;
596 l_batchstep_activity_rec.attribute25 :=
597 x_batchstep_activity_rec.attribute25;
598 l_batchstep_activity_rec.attribute26 :=
599 x_batchstep_activity_rec.attribute26;
600 l_batchstep_activity_rec.attribute27 :=
601 x_batchstep_activity_rec.attribute27;
602 l_batchstep_activity_rec.attribute28 :=
603 x_batchstep_activity_rec.attribute28;
604 l_batchstep_activity_rec.attribute29 :=
605 x_batchstep_activity_rec.attribute29;
606 l_batchstep_activity_rec.attribute30 :=
607 x_batchstep_activity_rec.attribute30;
608 END IF; --}
609 END IF; --}
610
611 x_batchstep_activity_rec := l_batchstep_activity_rec;
612
613 IF (NVL (g_debug, 0) IN
614 (gme_debug.g_log_statement, gme_debug.g_log_procedure)
615 )
616 THEN
617 gme_debug.put_line ( g_pkg_name
618 || '.'
619 || l_api_name
620 || ':'
621 || 'Exiting with '
622 || x_return_status
623 );
624 END IF;
625 EXCEPTION
626 WHEN fnd_api.g_exc_error
627 THEN
628 x_return_status := fnd_api.g_ret_sts_error;
629 WHEN fnd_api.g_exc_unexpected_error
630 THEN
631 x_return_status := fnd_api.g_ret_sts_unexp_error;
632
633 IF (NVL (g_debug, 0) > 0)
634 THEN
635 gme_debug.put_line ( g_pkg_name
636 || '.'
637 || l_api_name
638 || ':'
639 || 'UNEXPECTED:'
640 || SQLERRM
641 );
642 END IF;
643 WHEN OTHERS
644 THEN
645 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
646 x_return_status := fnd_api.g_ret_sts_unexp_error;
647
648 IF (NVL (g_debug, 0) > 0)
649 THEN
650 gme_debug.put_line ( g_pkg_name
651 || '.'
652 || l_api_name
653 || ':'
654 || 'OTHERS:'
655 || SQLERRM
656 );
657 END IF;
658 END validate_activity_param;
659
660 /*===========================================================================================
661 Procedure
662 insert_batchstep_activity
663 Description
664 This procedure is used to insert activity for a batch step
665 Parameters
666 p_batchstep_id OR (p_org_code,p_batch_no,step_no) to uniquely identify a step
667 p_batchstep_activity_rec GME_BATCH_STEP_ACTIVITIES%ROWTYPE - details of the activity
668 p_batchstep_resource_tbl gme_create_step_pvt.resources_tab - details of the rsrc
669 x_batchstep_activity_rec GME_BATCH_STEP_ACTIVITIES%ROWTYPE - returns the newly inserted row
670 x_return_status reflects return status of the API
671
672 History
673 23-SEP-2012 G. Muratore Bug 14637233
674 Comment out unnecessary validation when inserting an activity.
675 =============================================================================================*/
676 PROCEDURE insert_batchstep_activity (
677 p_batchstep_activity_rec IN gme_batch_step_activities%ROWTYPE,
678 p_batchstep_resource_tbl IN gme_create_step_pvt.resources_tab,
679 p_org_code IN VARCHAR2 := NULL,
680 p_batch_no IN VARCHAR2 := NULL,
681 p_batchstep_no IN NUMBER := NULL,
682 p_ignore_qty_below_cap IN VARCHAR2
683 DEFAULT fnd_api.g_false,
684 p_validate_flexfield IN VARCHAR2
685 DEFAULT fnd_api.g_false,
686 x_batchstep_activity_rec OUT NOCOPY gme_batch_step_activities%ROWTYPE,
687 x_return_status OUT NOCOPY VARCHAR2
688 )
689 IS
690 l_api_name CONSTANT VARCHAR2 (30)
691 := 'insert_batchstep_activity';
692 l_batch_id NUMBER;
693 l_batchstep_id NUMBER;
694 l_activity_id NUMBER;
695 l_batch_status NUMBER;
696 l_step_status NUMBER;
697 l_organization_id PLS_INTEGER;
698 l_batchstep_activity_rec gme_batch_step_activities%ROWTYPE;
699 l_batchstep_activity_out_rec gme_batch_step_activities%ROWTYPE;
700 l_batchstep_resource_rec gme_batch_step_resources%ROWTYPE;
701 l_batchstep_resource_out_rec gme_batch_step_resources%ROWTYPE;
702 l_prim_rsrc_count NUMBER;
703 l_rsrc_id PLS_INTEGER;
704
705 -- Define CURSORS
706 CURSOR cur_validate_step (v_step_id NUMBER)
707 IS
708 SELECT batch_id
709 FROM gme_batch_steps
710 WHERE batchstep_id = v_step_id;
711 BEGIN
712 IF (NVL (g_debug, 0) IN
713 (gme_debug.g_log_statement, gme_debug.g_log_procedure)
714 )
715 THEN
716 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
717 || 'Entering'
718 );
719 END IF;
720
721 /* Set the return status to success initially */
722 x_return_status := fnd_api.g_ret_sts_success;
723
724 IF p_batchstep_activity_rec.activity IS NULL
725 THEN
726 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
727 'FIELD_NAME',
728 'ACTIVITY'
729 );
730 RAISE fnd_api.g_exc_error;
731 END IF;
732
733 IF p_batchstep_activity_rec.batchstep_id IS NOT NULL
734 THEN
735 -- validate the key provided
736 l_batchstep_id := p_batchstep_activity_rec.batchstep_id;
737 OPEN cur_validate_step (l_batchstep_id);
738 FETCH cur_validate_step INTO l_batch_id;
739
740 IF cur_validate_step%NOTFOUND
741 THEN
742 CLOSE cur_validate_step;
743 gme_common_pvt.log_message ('GME_BATCH_STEP_NOT_FOUND',
744 'STEP_ID',
745 l_batchstep_id
746 );
747 RAISE fnd_api.g_exc_error;
748 END IF;
749
750 CLOSE cur_validate_step;
751 ELSE
752 -- validate the combination provided
753 validate_param (p_org_code => p_org_code,
754 p_batch_no => p_batch_no,
755 p_batchstep_no => p_batchstep_no,
756 p_activity => p_batchstep_activity_rec.activity,
757 x_batch_id => l_batch_id,
758 x_batchstep_id => l_batchstep_id,
759 x_activity_id => l_activity_id,
760 x_batch_status => l_batch_status,
761 x_step_status => l_step_status,
762 x_return_status => x_return_status
763 );
764
765 IF (NVL (g_debug, 0) = gme_debug.g_log_statement)
766 THEN
767 gme_debug.put_line ( g_pkg_name
768 || '.'
769 || l_api_name
770 || ':'
771 || 'after the call to procedure validate_param '
772 || x_return_status
773 );
774 END IF;
775
776 IF (x_return_status = fnd_api.g_ret_sts_error)
777 THEN
778 RAISE fnd_api.g_exc_error;
779 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
780 THEN
781 RAISE fnd_api.g_exc_unexpected_error;
782 END IF;
783 END IF;
784
785 validate_activity_param (p_batchstep_activity_rec => p_batchstep_activity_rec,
786 p_step_id => l_batchstep_id,
787 p_validate_flexfield => p_validate_flexfield,
788 p_action => 'INSERT',
789 x_batchstep_activity_rec => l_batchstep_activity_rec,
790 x_step_status => l_step_status,
791 x_return_status => x_return_status
792 );
793
794 IF (NVL (g_debug, 0) = gme_debug.g_log_statement)
795 THEN
796 gme_debug.put_line ( g_pkg_name
797 || '.'
798 || l_api_name
799 || ':'
800 || 'after the call to procedure validate_activity_param '
801 || x_return_status
802 );
803 END IF;
804
805 IF (x_return_status = fnd_api.g_ret_sts_error)
806 THEN
807 RAISE fnd_api.g_exc_error;
808 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
809 THEN
810 RAISE fnd_api.g_exc_unexpected_error;
811 END IF;
812
813 l_batchstep_activity_rec.batch_id := l_batch_id;
814 l_batchstep_activity_rec.batchstep_id := l_batchstep_id;
815 l_batchstep_activity_rec.oprn_line_id := NULL;
816 l_batchstep_activity_rec.offset_interval :=
817 NVL (l_batchstep_activity_rec.offset_interval, 0);
818 l_batchstep_activity_rec.delete_mark := 0;
819
820 -- check that there are resources and that there is one (and only one) primary resource
821 IF p_batchstep_resource_tbl.COUNT = 0
822 THEN
823 gme_common_pvt.log_message (p_message_code => 'GME_RESOURCE_NOT_ATTACH',
824 p_token1_name => 'ACTIVITY',
825 p_token1_value => p_batchstep_activity_rec.activity,
826 p_token2_name => 'STEPNO',
827 p_token2_value => p_batchstep_no
828 );
829 RAISE fnd_api.g_exc_error;
830 END IF;
831
832 l_prim_rsrc_count := 0;
833
834 FOR i IN 1 .. p_batchstep_resource_tbl.COUNT
835 LOOP
836 -- Bug 14637233 - Comment following block. There is no need to validate
837 -- the existence of the resource in the batch when inserting a new activity.
838 /*
839 gme_batchstep_rsrc_pvt.validate_param (p_org_code => p_org_code,
840 p_batch_no => p_batch_no,
841 p_batchstep_no => p_batchstep_no,
842 p_activity => p_batchstep_activity_rec.activity,
843 p_resource => p_batchstep_resource_tbl (i
844 ).resources,
845 x_organization_id => l_organization_id,
846 x_batch_id => l_batch_id,
847 x_batchstep_id => l_batchstep_id,
848 x_activity_id => l_activity_id,
849 x_rsrc_id => l_rsrc_id,
850 x_step_status => l_step_status,
851 x_return_status => x_return_status
852 );
853
854 IF (x_return_status = fnd_api.g_ret_sts_error)
855 THEN
856 RAISE fnd_api.g_exc_error;
857 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
858 THEN
859 RAISE fnd_api.g_exc_unexpected_error;
860 END IF;
861
862 gme_batchstep_rsrc_pvt.validate_rsrc_param (p_batchstep_resource_rec => p_batchstep_resource_tbl (i
863 ),
864 p_activity_id => l_activity_id,
865 p_ignore_qty_below_cap => p_ignore_qty_below_cap,
866 p_validate_flexfield => p_validate_flexfield,
867 p_action => 'INSERT',
868 x_batchstep_resource_rec => l_batchstep_resource_rec,
869 x_step_status => l_step_status,
870 x_return_status => x_return_status
871 );
872
873 IF (x_return_status = fnd_api.g_ret_sts_error)
874 THEN
875 RAISE fnd_api.g_exc_error;
876 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
877 THEN
878 RAISE fnd_api.g_exc_unexpected_error;
879 END IF;
880 */
881 IF NVL (p_batchstep_resource_tbl (i).prim_rsrc_ind, 0) = 1
882 THEN
883 l_prim_rsrc_count := l_prim_rsrc_count + 1;
884 END IF;
885 END LOOP;
886
887 IF l_prim_rsrc_count <> 1
888 THEN
889 gme_common_pvt.log_message ('GME_ONLY_ONE_PRIM_RSRC',
890 'ACTIVITY',
891 l_batchstep_activity_rec.activity,
892 'STEPNO',
893 p_batchstep_no
894 );
895 RAISE fnd_api.g_exc_error;
896 END IF;
897
898 IF (NVL (g_debug, 0) = gme_debug.g_log_statement)
899 THEN
900 gme_debug.put_line ( g_pkg_name
901 || '.'
902 || l_api_name
903 || ':'
904 || 'before call to GME_BATCH_STEP_ACTIVITIES_DBL.insert_row batch_id='
905 || l_batchstep_activity_rec.batch_id
906 || ' and step_id ='
907 || l_batchstep_activity_rec.batchstep_id
908 );
909 END IF;
910
911 IF NOT (gme_batch_step_activities_dbl.insert_row (l_batchstep_activity_rec,
912 l_batchstep_activity_out_rec
913 )
914 )
915 THEN
916 RAISE fnd_api.g_exc_error;
917 END IF;
918
919 IF (NVL (g_debug, -1) = gme_debug.g_log_statement)
920 THEN
921 gme_debug.put_line ( g_pkg_name
922 || '.'
923 || l_api_name
924 || ':'
925 || 'after call to GME_BATCH_STEP_ACTIVITIES_DBL.insert_row activity_id='
926 || l_batchstep_activity_out_rec.batchstep_activity_id
927 );
928 END IF;
929
930 gme_batch_step_chg_pvt.set_activity_sequence_num (p_batchstep_activity_rec.batch_id
931 );
932
933 FOR i IN 1 .. p_batchstep_resource_tbl.COUNT
934 LOOP
935 l_batchstep_resource_rec := p_batchstep_resource_tbl (i);
936 l_batchstep_resource_rec.batch_id :=
937 l_batchstep_activity_rec.batch_id;
938 l_batchstep_resource_rec.batchstep_id :=
939 l_batchstep_activity_rec.batchstep_id;
940 l_batchstep_resource_rec.batchstep_activity_id :=
941 l_batchstep_activity_out_rec.batchstep_activity_id;
942 gme_batchstep_rsrc_pvt.insert_batchstep_rsrc (p_batchstep_resource_rec => l_batchstep_resource_rec,
943 x_batchstep_resource_rec => l_batchstep_resource_out_rec,
944 x_return_status => x_return_status
945 );
946
947 IF (x_return_status = fnd_api.g_ret_sts_error)
948 THEN
949 RAISE fnd_api.g_exc_error;
950 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
951 THEN
952 RAISE fnd_api.g_exc_unexpected_error;
953 END IF;
954 END LOOP;
955
956 x_batchstep_activity_rec := l_batchstep_activity_out_rec;
957
958 IF (NVL (g_debug, 0) IN
959 (gme_debug.g_log_statement, gme_debug.g_log_procedure)
960 )
961 THEN
962 gme_debug.put_line ( g_pkg_name
963 || '.'
964 || l_api_name
965 || ':'
966 || 'Exiting with '
967 || x_return_status
968 );
969 END IF;
970 EXCEPTION
971 WHEN fnd_api.g_exc_error
972 THEN
973 x_batchstep_activity_rec := NULL;
974 x_return_status := fnd_api.g_ret_sts_error;
975 WHEN fnd_api.g_exc_unexpected_error
976 THEN
977 x_batchstep_activity_rec := NULL;
978 x_return_status := fnd_api.g_ret_sts_unexp_error;
979
980 IF (NVL (g_debug, 0) > 0)
981 THEN
982 gme_debug.put_line ( g_pkg_name
983 || '.'
984 || l_api_name
985 || ':'
986 || 'UNEXPECTED:'
987 || SQLERRM
988 );
989 END IF;
990 WHEN OTHERS
991 THEN
992 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
993 x_batchstep_activity_rec := NULL;
994 x_return_status := fnd_api.g_ret_sts_unexp_error;
995
996 IF (NVL (g_debug, 0) > 0)
997 THEN
998 gme_debug.put_line ( g_pkg_name
999 || '.'
1000 || l_api_name
1001 || ':'
1002 || 'OTHERS:'
1003 || SQLERRM
1004 );
1005 END IF;
1006 END insert_batchstep_activity;
1007
1008 /*===========================================================================================
1009 Procedure
1010 update_batchstep_activity
1011 Description
1012 This procedure is used to update an activity
1013 Parameters
1014 p_batchstep_activity_id OR
1015 (p_org_code,p_batch_no,step_no,activity) to uniquely identify an activity
1016 p_batchstep_activity_rec GME_BATCH_STEP_ACTIVITIES%ROWTYPE - details of the activity
1017 x_batchstep_activity_rec returns the newly updated row for further processing
1018 x_return_status reflects return status of the API
1019 History
1020 =============================================================================================*/
1021 PROCEDURE update_batchstep_activity (
1022 p_batchstep_activity_rec IN gme_batch_step_activities%ROWTYPE,
1023 p_org_code IN VARCHAR2 := NULL,
1024 p_batch_no IN VARCHAR2 := NULL,
1025 p_batchstep_no IN NUMBER := NULL,
1026 p_validate_flexfield IN VARCHAR2
1027 DEFAULT fnd_api.g_false,
1028 x_batchstep_activity_rec OUT NOCOPY gme_batch_step_activities%ROWTYPE,
1029 x_return_status OUT NOCOPY VARCHAR2
1030 )
1031 IS
1032 l_api_name CONSTANT VARCHAR2 (30)
1033 := 'update_batchstep_activity';
1034 l_batch_id NUMBER;
1035 l_activity_id NUMBER;
1036 l_activity gme_batch_step_activities.activity%TYPE;
1037 l_batchstep_id NUMBER;
1038 l_batch_status NUMBER;
1039 l_step_status NUMBER;
1040 l_batch_type NUMBER;
1041 l_field_updated BOOLEAN := FALSE;
1042 l_upd_plan_date BOOLEAN := FALSE;
1043 l_upd_act_date BOOLEAN := FALSE;
1044 l_update_resources BOOLEAN := FALSE;
1045 l_flex_validate BOOLEAN := FALSE;
1046 l_last_update_date DATE;
1047 l_batchstep_activity_rec gme_batch_step_activities%ROWTYPE;
1048 l_batchstep_activity_out_rec gme_batch_step_activities%ROWTYPE;
1049 --Bug 3027004 Mohit Kapoor Added two variables
1050 l_inv_trans_count NUMBER;
1051 l_rsrc_trans_count NUMBER;
1052
1053 -- Define CURSORS
1054
1055 CURSOR cur_dates (v_act_id NUMBER)
1056 IS
1057 SELECT *
1058 FROM gme_batch_step_resources
1059 WHERE batchstep_activity_id = v_act_id;
1060
1061 l_temp_resources cur_dates%ROWTYPE;
1062 l_resource VARCHAR2 (1000);
1063
1064 CURSOR cur_get_dtl (v_act_id NUMBER)
1065 IS
1066 SELECT a.batch_id, a.batchstep_id, step_status, activity
1067 FROM gme_batch_step_activities a, gme_batch_steps s
1068 WHERE batchstep_activity_id = v_act_id
1069 AND a.batchstep_id = s.batchstep_id;
1070
1071 CURSOR cur_get_step_dtl (v_step_id NUMBER)
1072 IS
1073 SELECT *
1074 FROM gme_batch_steps
1075 WHERE batchstep_id = v_step_id;
1076
1077 l_step_dtl gme_batch_steps%ROWTYPE;
1078
1079 CURSOR cur_get_batch (v_batch_id NUMBER)
1080 IS
1081 SELECT *
1082 FROM gme_batch_header
1083 WHERE batch_id = v_batch_id;
1084
1085 l_batch_hdr gme_batch_header%ROWTYPE;
1086
1087 CURSOR cur_check_fpo (v_actv_id NUMBER)
1088 IS
1089 SELECT batch_type
1090 FROM gme_batch_header b, gme_batch_step_activities a
1091 WHERE b.batch_id = a.batch_id AND batchstep_activity_id = v_actv_id;
1092
1093 CURSOR cur_lock_actv (v_activity_id NUMBER)
1094 IS
1095 SELECT last_update_date
1096 FROM gme_batch_step_activities
1097 WHERE batchstep_activity_id = v_activity_id
1098 FOR UPDATE OF last_update_date NOWAIT;
1099
1100 l_seq_dep_ind gme_batch_step_activities.sequence_dependent_ind%TYPE;
1101 BEGIN
1102 IF (NVL (g_debug, 0) IN
1103 (gme_debug.g_log_statement, gme_debug.g_log_procedure)
1104 )
1105 THEN
1106 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1107 || 'Entering'
1108 );
1109 END IF;
1110
1111 /* Set the return status to success initially */
1112 x_return_status := fnd_api.g_ret_sts_success;
1113
1114 IF ( p_batchstep_activity_rec.activity IS NULL
1115 AND p_batchstep_activity_rec.batchstep_activity_id IS NULL
1116 )
1117 THEN
1118 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
1119 'FIELD_NAME',
1120 'ACTIVITY'
1121 );
1122 RAISE fnd_api.g_exc_error;
1123 END IF;
1124
1125 IF p_batchstep_activity_rec.batchstep_activity_id IS NOT NULL
1126 THEN
1127 -- validate the key provided
1128 l_activity_id := p_batchstep_activity_rec.batchstep_activity_id;
1129 OPEN cur_get_dtl (l_activity_id);
1130 FETCH cur_get_dtl INTO l_batch_id, l_batchstep_id, l_step_status,
1131 l_activity;
1132
1133 IF cur_get_dtl%NOTFOUND
1134 THEN
1135 CLOSE cur_get_dtl;
1136 gme_common_pvt.log_message ('GME_ACTID_NOT_FOUND');
1137 RAISE fnd_api.g_exc_error;
1138 END IF;
1139
1140 CLOSE cur_get_dtl;
1141 ELSE
1142 -- validate the combination provided
1143 l_activity := p_batchstep_activity_rec.activity;
1144 validate_param (p_org_code => p_org_code,
1145 p_batch_no => p_batch_no,
1146 p_batchstep_no => p_batchstep_no,
1147 p_activity => l_activity,
1148 x_batch_id => l_batch_id,
1149 x_batchstep_id => l_batchstep_id,
1150 x_activity_id => l_activity_id,
1151 x_batch_status => l_batch_status,
1152 x_step_status => l_step_status,
1153 x_return_status => x_return_status
1154 );
1155
1156 IF (NVL (g_debug, 0) = gme_debug.g_log_statement)
1157 THEN
1158 gme_debug.put_line ( g_pkg_name
1159 || '.'
1160 || l_api_name
1161 || ':'
1162 || 'after the call to procedure validate_param '
1163 || x_return_status
1164 );
1165 END IF;
1166
1167 IF (x_return_status = fnd_api.g_ret_sts_error)
1168 THEN
1169 RAISE fnd_api.g_exc_error;
1170 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
1171 THEN
1172 RAISE fnd_api.g_exc_unexpected_error;
1173 END IF;
1174 END IF;
1175
1176 -- Check if this is an FPO
1177 OPEN cur_check_fpo (l_activity_id);
1178 FETCH cur_check_fpo INTO l_batch_type;
1179
1180 IF l_batch_type = 10
1181 THEN
1182 CLOSE cur_check_fpo;
1183 gme_common_pvt.log_message ('GME_FPO_ACTV_NO_EDIT');
1184 RAISE fnd_api.g_exc_error;
1185 END IF;
1186
1187 CLOSE cur_check_fpo;
1188 OPEN cur_lock_actv (l_activity_id);
1189 FETCH cur_lock_actv INTO l_last_update_date;
1190 CLOSE cur_lock_actv;
1191
1192 IF (NVL (g_debug, -1) = gme_debug.g_log_statement)
1193 THEN
1194 gme_debug.put_line (' Successfully locked the activity');
1195 END IF;
1196
1197 IF NOT (gme_batch_step_activities_dbl.fetch_row (p_batchstep_activity_rec,
1198 l_batchstep_activity_rec
1199 )
1200 )
1201 THEN
1202 RAISE fnd_api.g_exc_error;
1203 END IF;
1204
1205 l_seq_dep_ind := l_batchstep_activity_rec.sequence_dependent_ind;
1206
1207 IF l_step_status IN (4, 5)
1208 THEN
1209 gme_common_pvt.log_message ('PC_STEP_STATUS_ERR');
1210 RAISE fnd_api.g_exc_error;
1211 END IF;
1212
1213 IF p_batchstep_activity_rec.offset_interval = fnd_api.g_miss_num
1214 THEN
1215 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
1216 'FIELD_NAME',
1217 'offset_interval'
1218 );
1219 RAISE fnd_api.g_exc_error;
1220 ELSIF ( p_batchstep_activity_rec.offset_interval IS NOT NULL
1221 AND (l_batchstep_activity_rec.offset_interval <>
1222 p_batchstep_activity_rec.offset_interval
1223 )
1224 )
1225 THEN
1226 l_field_updated := TRUE;
1227 l_batchstep_activity_rec.offset_interval :=
1228 p_batchstep_activity_rec.offset_interval;
1229 END IF;
1230
1231 OPEN cur_get_step_dtl (l_batchstep_id);
1232 FETCH cur_get_step_dtl INTO l_step_dtl;
1233 CLOSE cur_get_step_dtl;
1234
1235 IF l_step_status IN (1, 2)
1236 THEN
1237 IF p_batchstep_activity_rec.plan_start_date = fnd_api.g_miss_date
1238 THEN
1239 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
1240 'FIELD_NAME',
1241 'plan_start_date'
1242 );
1243 RAISE fnd_api.g_exc_error;
1244 ELSIF p_batchstep_activity_rec.plan_start_date IS NOT NULL
1245 AND (p_batchstep_activity_rec.plan_start_date <>
1246 l_batchstep_activity_rec.plan_start_date
1247 )
1248 THEN
1249 -- Check that the activity plan dates fall within the step dates.
1250 IF p_batchstep_activity_rec.plan_start_date <
1251 l_step_dtl.plan_start_date
1252 OR p_batchstep_activity_rec.plan_start_date >
1253 l_step_dtl.plan_cmplt_date
1254 THEN
1255 gme_common_pvt.log_message ('GME_ACTV_PLAN_DATE',
1256 'ACTIVITY',
1257 l_activity
1258 );
1259 RAISE fnd_api.g_exc_error;
1260 END IF;
1261
1262 l_field_updated := TRUE;
1263 l_upd_plan_date := TRUE;
1264 l_batchstep_activity_rec.plan_start_date :=
1265 p_batchstep_activity_rec.plan_start_date;
1266 END IF;
1267
1268 IF p_batchstep_activity_rec.plan_cmplt_date = fnd_api.g_miss_date
1269 THEN
1270 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
1271 'FIELD_NAME',
1272 'plan_cmplt_date'
1273 );
1274 RAISE fnd_api.g_exc_error;
1275 ELSIF p_batchstep_activity_rec.plan_cmplt_date IS NOT NULL
1276 AND (p_batchstep_activity_rec.plan_cmplt_date <>
1277 l_batchstep_activity_rec.plan_cmplt_date
1278 )
1279 THEN
1280 -- Check that the activity plan dates fall within the step dates.
1281 IF p_batchstep_activity_rec.plan_cmplt_date >
1282 l_step_dtl.plan_cmplt_date
1283 THEN
1284 gme_common_pvt.log_message ('GME_ACTV_PLAN_DATE',
1285 'ACTIVITY',
1286 l_activity
1287 );
1288 RAISE fnd_api.g_exc_error;
1289 END IF;
1290
1291 l_field_updated := TRUE;
1292 l_upd_plan_date := TRUE;
1293 l_batchstep_activity_rec.plan_cmplt_date :=
1294 p_batchstep_activity_rec.plan_cmplt_date;
1295 END IF;
1296
1297 IF ( l_upd_plan_date
1298 AND l_batchstep_activity_rec.plan_start_date >
1299 l_batchstep_activity_rec.plan_cmplt_date
1300 )
1301 THEN
1302 gme_common_pvt.log_message ('PM_BADSTARTDATE');
1303 RAISE fnd_api.g_exc_error;
1304 END IF;
1305 END IF;
1306
1307 IF l_step_dtl.step_status IN (2, 3)
1308 THEN
1309 IF p_batchstep_activity_rec.actual_start_date = fnd_api.g_miss_date
1310 THEN
1311 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
1312 'FIELD_NAME',
1313 'actual_start_date'
1314 );
1315 RAISE fnd_api.g_exc_error;
1316 ELSIF p_batchstep_activity_rec.actual_start_date IS NOT NULL
1317 AND (p_batchstep_activity_rec.actual_start_date <>
1318 l_batchstep_activity_rec.actual_start_date
1319 )
1320 THEN
1321 -- Check that actual start date is not in the future
1322 IF p_batchstep_activity_rec.actual_start_date >
1323 gme_common_pvt.g_timestamp
1324 THEN
1325 fnd_message.set_name ('GMA', 'SY_NOFUTUREDATE');
1326 fnd_msg_pub.ADD;
1327 RAISE fnd_api.g_exc_error;
1328 END IF;
1329
1330 -- Check that the activity actual dates fall within the step dates.
1331 IF ( p_batchstep_activity_rec.actual_start_date <
1332 l_step_dtl.actual_start_date
1333 OR p_batchstep_activity_rec.actual_start_date >
1334 l_step_dtl.actual_cmplt_date
1335 )
1336 THEN
1337 gme_common_pvt.log_message ('GME_ACTV_ACTUAL_DATE',
1338 'ACTIVITY',
1339 l_activity
1340 );
1341 RAISE fnd_api.g_exc_error;
1342 END IF;
1343
1344 l_field_updated := TRUE;
1345 l_upd_act_date := TRUE;
1346 l_batchstep_activity_rec.actual_start_date :=
1347 p_batchstep_activity_rec.actual_start_date;
1348 END IF;
1349 END IF;
1350
1351 IF l_step_dtl.step_status = 3
1352 THEN
1353 IF p_batchstep_activity_rec.actual_cmplt_date = fnd_api.g_miss_date
1354 THEN
1355 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
1356 'FIELD_NAME',
1357 'actual_cmplt_date'
1358 );
1359 RAISE fnd_api.g_exc_error;
1360 ELSIF p_batchstep_activity_rec.actual_cmplt_date IS NOT NULL
1361 AND (p_batchstep_activity_rec.actual_cmplt_date <>
1362 l_batchstep_activity_rec.actual_cmplt_date
1363 )
1364 THEN
1365 -- Check that actual cmplt date is not in the future
1366 IF p_batchstep_activity_rec.actual_cmplt_date >
1367 gme_common_pvt.g_timestamp
1368 THEN
1369 fnd_message.set_name ('GMA', 'SY_NOFUTUREDATE');
1370 fnd_msg_pub.ADD;
1371 RAISE fnd_api.g_exc_error;
1372 END IF;
1373
1374 IF p_batchstep_activity_rec.actual_cmplt_date IS NOT NULL
1375 AND p_batchstep_activity_rec.actual_cmplt_date >
1376 l_step_dtl.actual_cmplt_date
1377 THEN
1378 gme_common_pvt.log_message ('GME_ACTV_ACTUAL_DATE',
1379 'ACTIVITY',
1380 l_activity
1381 );
1382 RAISE fnd_api.g_exc_error;
1383 END IF;
1384
1385 l_field_updated := TRUE;
1386 l_upd_act_date := TRUE;
1387 l_batchstep_activity_rec.actual_cmplt_date :=
1388 p_batchstep_activity_rec.actual_cmplt_date;
1389 END IF;
1390 END IF;
1391
1392 IF l_upd_act_date
1393 AND (l_batchstep_activity_rec.actual_start_date >
1394 l_batchstep_activity_rec.actual_cmplt_date
1395 )
1396 THEN
1397 gme_common_pvt.log_message ('PM_BADSTARTDATE');
1398 RAISE fnd_api.g_exc_error;
1399 END IF;
1400
1401 -- check that activities dates are within resource dates
1402 IF l_upd_plan_date OR l_upd_act_date
1403 THEN
1404 OPEN cur_dates (l_activity_id);
1405
1406 LOOP
1407 FETCH cur_dates INTO l_temp_resources;
1408 EXIT WHEN cur_dates%NOTFOUND;
1409
1410 IF ( l_batchstep_activity_rec.actual_start_date IS NOT NULL
1411 AND l_batchstep_activity_rec.actual_start_date >
1412 l_temp_resources.actual_start_date
1413 )
1414 OR ( l_batchstep_activity_rec.actual_cmplt_date IS NOT NULL
1415 AND l_batchstep_activity_rec.actual_cmplt_date <
1416 l_temp_resources.actual_cmplt_date
1417 )
1418 OR ( l_batchstep_activity_rec.plan_start_date IS NOT NULL
1419 AND l_batchstep_activity_rec.plan_start_date >
1420 l_temp_resources.plan_start_date
1421 )
1422 OR ( l_batchstep_activity_rec.plan_cmplt_date IS NOT NULL
1423 AND l_batchstep_activity_rec.plan_cmplt_date <
1424 l_temp_resources.plan_cmplt_date
1425 )
1426 THEN
1427 l_resource := l_resource || ' ' || l_temp_resources.resources;
1428 END IF;
1429 END LOOP;
1430
1431 CLOSE cur_dates;
1432
1433 IF l_resource IS NOT NULL
1434 THEN
1435 gme_common_pvt.log_message ('GME_RSRC_DATES_NOT_ALLOWED',
1436 'RESOURCE',
1437 'l_resource'
1438 );
1439 RAISE fnd_api.g_exc_error;
1440 END IF;
1441 END IF;
1442
1443 --fix to g_miss_num and comparison to not NULL for l_batchstep_activity_rec
1444 IF p_batchstep_activity_rec.plan_activity_factor =
1445 fnd_api.g_miss_num
1446 AND l_batchstep_activity_rec.plan_activity_factor IS NOT NULL
1447 THEN
1448 gme_common_pvt.log_message ('GME_PLAN_ACTV_FACT_NOTNULL');
1449 RAISE fnd_api.g_exc_error;
1450 END IF;
1451
1452 IF ( p_batchstep_activity_rec.plan_activity_factor IS NOT NULL
1453 AND l_batchstep_activity_rec.plan_activity_factor IS NULL
1454 )
1455 OR (p_batchstep_activity_rec.plan_activity_factor <>
1456 l_batchstep_activity_rec.plan_activity_factor
1457 )
1458 THEN
1459 IF l_step_status = 1
1460 THEN
1461 l_field_updated := TRUE;
1462 l_update_resources := TRUE;
1463 l_batchstep_activity_rec.plan_activity_factor :=
1464 p_batchstep_activity_rec.plan_activity_factor;
1465 END IF;
1466 END IF;
1467
1468 --fix to g_miss_num and comparison to not NULL for l_batchstep_activity_rec
1469 IF p_batchstep_activity_rec.actual_activity_factor =
1470 fnd_api.g_miss_num
1471 AND l_batchstep_activity_rec.actual_activity_factor IS NOT NULL
1472 THEN
1473 gme_common_pvt.log_message ('GME_ACTUAL_ACTV_FACT_NOTNULL');
1474 RAISE fnd_api.g_exc_error;
1475 END IF;
1476
1477 IF ( p_batchstep_activity_rec.actual_activity_factor IS NOT NULL
1478 AND l_batchstep_activity_rec.actual_activity_factor IS NULL
1479 )
1480 OR (p_batchstep_activity_rec.actual_activity_factor <>
1481 l_batchstep_activity_rec.actual_activity_factor
1482 )
1483 THEN
1484 IF l_step_status IN (2, 3)
1485 THEN
1486 l_field_updated := TRUE;
1487 l_update_resources := TRUE;
1488 l_batchstep_activity_rec.actual_activity_factor :=
1489 p_batchstep_activity_rec.actual_activity_factor;
1490 END IF;
1491 END IF;
1492
1493 IF (fnd_api.to_boolean (p_validate_flexfield))
1494 THEN
1495 IF NVL (p_batchstep_activity_rec.attribute_category, ' ') <>
1496 NVL (l_batchstep_activity_rec.attribute_category, ' ')
1497 OR NVL (p_batchstep_activity_rec.attribute1, ' ') <>
1498 NVL (l_batchstep_activity_rec.attribute1, ' ')
1499 OR NVL (p_batchstep_activity_rec.attribute2, ' ') <>
1500 NVL (l_batchstep_activity_rec.attribute2, ' ')
1501 OR NVL (p_batchstep_activity_rec.attribute3, ' ') <>
1502 NVL (l_batchstep_activity_rec.attribute3, ' ')
1503 OR NVL (p_batchstep_activity_rec.attribute4, ' ') <>
1504 NVL (l_batchstep_activity_rec.attribute4, ' ')
1505 OR NVL (p_batchstep_activity_rec.attribute5, ' ') <>
1506 NVL (l_batchstep_activity_rec.attribute5, ' ')
1507 OR NVL (p_batchstep_activity_rec.attribute6, ' ') <>
1508 NVL (l_batchstep_activity_rec.attribute6, ' ')
1509 OR NVL (p_batchstep_activity_rec.attribute7, ' ') <>
1510 NVL (l_batchstep_activity_rec.attribute7, ' ')
1511 OR NVL (p_batchstep_activity_rec.attribute8, ' ') <>
1512 NVL (l_batchstep_activity_rec.attribute8, ' ')
1513 OR NVL (p_batchstep_activity_rec.attribute9, ' ') <>
1514 NVL (l_batchstep_activity_rec.attribute9, ' ')
1515 OR NVL (p_batchstep_activity_rec.attribute10, ' ') <>
1516 NVL (l_batchstep_activity_rec.attribute10, ' ')
1517 OR NVL (p_batchstep_activity_rec.attribute11, ' ') <>
1518 NVL (l_batchstep_activity_rec.attribute11, ' ')
1519 OR NVL (p_batchstep_activity_rec.attribute12, ' ') <>
1520 NVL (l_batchstep_activity_rec.attribute12, ' ')
1521 OR NVL (p_batchstep_activity_rec.attribute13, ' ') <>
1522 NVL (l_batchstep_activity_rec.attribute13, ' ')
1523 OR NVL (p_batchstep_activity_rec.attribute14, ' ') <>
1524 NVL (l_batchstep_activity_rec.attribute14, ' ')
1525 OR NVL (p_batchstep_activity_rec.attribute15, ' ') <>
1526 NVL (l_batchstep_activity_rec.attribute15, ' ')
1527 OR NVL (p_batchstep_activity_rec.attribute16, ' ') <>
1528 NVL (l_batchstep_activity_rec.attribute16, ' ')
1529 OR NVL (p_batchstep_activity_rec.attribute17, ' ') <>
1530 NVL (l_batchstep_activity_rec.attribute17, ' ')
1531 OR NVL (p_batchstep_activity_rec.attribute18, ' ') <>
1532 NVL (l_batchstep_activity_rec.attribute18, ' ')
1533 OR NVL (p_batchstep_activity_rec.attribute19, ' ') <>
1534 NVL (l_batchstep_activity_rec.attribute19, ' ')
1535 OR NVL (p_batchstep_activity_rec.attribute20, ' ') <>
1536 NVL (l_batchstep_activity_rec.attribute20, ' ')
1537 OR NVL (p_batchstep_activity_rec.attribute21, ' ') <>
1538 NVL (l_batchstep_activity_rec.attribute21, ' ')
1539 OR NVL (p_batchstep_activity_rec.attribute22, ' ') <>
1540 NVL (l_batchstep_activity_rec.attribute22, ' ')
1541 OR NVL (p_batchstep_activity_rec.attribute23, ' ') <>
1542 NVL (l_batchstep_activity_rec.attribute23, ' ')
1543 OR NVL (p_batchstep_activity_rec.attribute24, ' ') <>
1544 NVL (l_batchstep_activity_rec.attribute24, ' ')
1545 OR NVL (p_batchstep_activity_rec.attribute25, ' ') <>
1546 NVL (l_batchstep_activity_rec.attribute25, ' ')
1547 OR NVL (p_batchstep_activity_rec.attribute26, ' ') <>
1548 NVL (l_batchstep_activity_rec.attribute26, ' ')
1549 OR NVL (p_batchstep_activity_rec.attribute27, ' ') <>
1550 NVL (l_batchstep_activity_rec.attribute27, ' ')
1551 OR NVL (p_batchstep_activity_rec.attribute28, ' ') <>
1552 NVL (l_batchstep_activity_rec.attribute28, ' ')
1553 OR NVL (p_batchstep_activity_rec.attribute29, ' ') <>
1554 NVL (l_batchstep_activity_rec.attribute29, ' ')
1555 OR NVL (p_batchstep_activity_rec.attribute30, ' ') <>
1556 NVL (l_batchstep_activity_rec.attribute30, ' ')
1557 THEN
1558 l_flex_validate := TRUE;
1559 END IF;
1560
1561 IF l_flex_validate
1562 THEN
1563 l_field_updated := TRUE;
1564 gme_validate_flex_fld_pvt.validate_flex_step_activities (p_step_activities => p_batchstep_activity_rec,
1565 x_step_activities => x_batchstep_activity_rec,
1566 x_return_status => x_return_status
1567 );
1568
1569 IF (NVL (g_debug, -1) = gme_debug.g_log_statement)
1570 THEN
1571 gme_debug.put_line ( 'gme_validate_flex_fld_pvt.validate_flex_step_activities '
1572 || x_return_status
1573 );
1574 END IF;
1575
1576 IF (x_return_status = fnd_api.g_ret_sts_error)
1577 THEN
1578 RAISE fnd_api.g_exc_error;
1579 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
1580 THEN
1581 RAISE fnd_api.g_exc_unexpected_error;
1582 END IF;
1583
1584 -- copy all the validated flex fields
1585 l_batchstep_activity_rec.attribute_category :=
1586 x_batchstep_activity_rec.attribute_category;
1587 l_batchstep_activity_rec.attribute1 :=
1588 x_batchstep_activity_rec.attribute1;
1589 l_batchstep_activity_rec.attribute2 :=
1590 x_batchstep_activity_rec.attribute2;
1591 l_batchstep_activity_rec.attribute3 :=
1592 x_batchstep_activity_rec.attribute3;
1593 l_batchstep_activity_rec.attribute4 :=
1594 x_batchstep_activity_rec.attribute4;
1595 l_batchstep_activity_rec.attribute5 :=
1596 x_batchstep_activity_rec.attribute5;
1597 l_batchstep_activity_rec.attribute6 :=
1598 x_batchstep_activity_rec.attribute6;
1599 l_batchstep_activity_rec.attribute7 :=
1600 x_batchstep_activity_rec.attribute7;
1601 l_batchstep_activity_rec.attribute8 :=
1602 x_batchstep_activity_rec.attribute8;
1603 l_batchstep_activity_rec.attribute9 :=
1604 x_batchstep_activity_rec.attribute9;
1605 l_batchstep_activity_rec.attribute10 :=
1606 x_batchstep_activity_rec.attribute10;
1607 l_batchstep_activity_rec.attribute11 :=
1608 x_batchstep_activity_rec.attribute11;
1609 l_batchstep_activity_rec.attribute12 :=
1610 x_batchstep_activity_rec.attribute12;
1611 l_batchstep_activity_rec.attribute13 :=
1612 x_batchstep_activity_rec.attribute13;
1613 l_batchstep_activity_rec.attribute14 :=
1614 x_batchstep_activity_rec.attribute14;
1615 l_batchstep_activity_rec.attribute15 :=
1616 x_batchstep_activity_rec.attribute15;
1617 l_batchstep_activity_rec.attribute16 :=
1618 x_batchstep_activity_rec.attribute16;
1619 l_batchstep_activity_rec.attribute17 :=
1620 x_batchstep_activity_rec.attribute17;
1621 l_batchstep_activity_rec.attribute18 :=
1622 x_batchstep_activity_rec.attribute18;
1623 l_batchstep_activity_rec.attribute19 :=
1624 x_batchstep_activity_rec.attribute19;
1625 l_batchstep_activity_rec.attribute20 :=
1626 x_batchstep_activity_rec.attribute20;
1627 l_batchstep_activity_rec.attribute21 :=
1628 x_batchstep_activity_rec.attribute21;
1629 l_batchstep_activity_rec.attribute22 :=
1630 x_batchstep_activity_rec.attribute22;
1631 l_batchstep_activity_rec.attribute23 :=
1632 x_batchstep_activity_rec.attribute23;
1633 l_batchstep_activity_rec.attribute24 :=
1634 x_batchstep_activity_rec.attribute24;
1635 l_batchstep_activity_rec.attribute25 :=
1636 x_batchstep_activity_rec.attribute25;
1637 l_batchstep_activity_rec.attribute26 :=
1638 x_batchstep_activity_rec.attribute26;
1639 l_batchstep_activity_rec.attribute27 :=
1640 x_batchstep_activity_rec.attribute27;
1641 l_batchstep_activity_rec.attribute28 :=
1642 x_batchstep_activity_rec.attribute28;
1643 l_batchstep_activity_rec.attribute29 :=
1644 x_batchstep_activity_rec.attribute29;
1645 l_batchstep_activity_rec.attribute30 :=
1646 x_batchstep_activity_rec.attribute30;
1647 END IF;
1648 END IF;
1649
1650 IF NOT (l_field_updated)
1651 THEN
1652 gme_common_pvt.log_message ('GME_NO_CHANGE_TO_UPD');
1653 RAISE fnd_api.g_exc_error;
1654 END IF;
1655
1656 IF (NVL (g_debug, -1) = gme_debug.g_log_statement)
1657 THEN
1658 gme_debug.put_line ('before update of activity');
1659 END IF;
1660
1661 IF l_update_resources
1662 THEN
1663 OPEN cur_get_batch (l_batch_id);
1664 FETCH cur_get_batch INTO l_batch_hdr;
1665 CLOSE cur_get_batch;
1666
1667 -- load temp table so that save_batch routine does resource txn consolidation
1668 IF l_batch_hdr.update_inventory_ind = 'Y'
1669 THEN
1670 gme_trans_engine_util.load_rsrc_trans (p_batch_row => l_batch_hdr,
1671 x_rsc_row_count => l_rsrc_trans_count,
1672 x_return_status => x_return_status
1673 );
1674
1675 IF (NVL (g_debug, 0) = gme_debug.g_log_statement)
1676 THEN
1677 gme_debug.put_line ( g_pkg_name
1678 || '.'
1679 || l_api_name
1680 || ':'
1681 || 'after call to GME_TRANS_ENGINE_UTIL.load_rsrc_trans return status is '
1682 || x_return_status
1683 );
1684 END IF;
1685
1686 IF (x_return_status = fnd_api.g_ret_sts_error)
1687 THEN
1688 RAISE fnd_api.g_exc_error;
1689 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
1690 THEN
1691 RAISE fnd_api.g_exc_unexpected_error;
1692 END IF;
1693 END IF;
1694
1695 gme_update_step_qty_pvt.update_resources (p_batch_hdr_rec => l_batch_hdr,
1696 p_batch_step_rec => l_step_dtl,
1697 p_batchstep_activities_rec => l_batchstep_activity_rec,
1698 x_return_status => x_return_status
1699 );
1700
1701 IF (NVL (g_debug, 0) = gme_debug.g_log_statement)
1702 THEN
1703 gme_debug.put_line ( g_pkg_name
1704 || '.'
1705 || l_api_name
1706 || ':'
1707 || 'after call to GME_UPDATE_STEP_QTY_PVT.update_resources return status is '
1708 || x_return_status
1709 );
1710 END IF;
1711
1712 IF (x_return_status = fnd_api.g_ret_sts_error)
1713 THEN
1714 RAISE fnd_api.g_exc_error;
1715 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
1716 THEN
1717 RAISE fnd_api.g_exc_unexpected_error;
1718 END IF;
1719 END IF;
1720
1721 IF NOT (gme_batch_step_activities_dbl.update_row (l_batchstep_activity_rec
1722 )
1723 )
1724 THEN
1725 RAISE fnd_api.g_exc_error;
1726 END IF;
1727
1728 IF l_seq_dep_ind <> l_batchstep_activity_rec.sequence_dependent_ind
1729 THEN
1730 gme_batch_step_chg_pvt.set_activity_sequence_num (l_batchstep_activity_rec.batch_id
1731 );
1732 END IF;
1733
1734 /*Fetching the values from the db again to get the latest seq_dep_ind.*/
1735 IF NOT (gme_batch_step_activities_dbl.fetch_row (l_batchstep_activity_rec,
1736 l_batchstep_activity_rec
1737 )
1738 )
1739 THEN
1740 RAISE fnd_api.g_exc_error;
1741 END IF;
1742
1743 x_batchstep_activity_rec := l_batchstep_activity_rec;
1744
1745 IF (NVL (g_debug, 0) IN
1746 (gme_debug.g_log_statement, gme_debug.g_log_procedure)
1747 )
1748 THEN
1749 gme_debug.put_line ( g_pkg_name
1750 || '.'
1751 || l_api_name
1752 || ':'
1753 || 'Exiting with '
1754 || x_return_status
1755 );
1756 END IF;
1757 EXCEPTION
1758 WHEN app_exception.record_lock_exception
1759 THEN
1760 gme_common_pvt.log_message ('GME_RECORD_LOCKED',
1761 'TABLE_NAME',
1762 'GME_BATCH_STEP_ACTIVITIES',
1763 'RECORD',
1764 'Activity',
1765 'KEY',
1766 l_activity
1767 );
1768 x_return_status := fnd_api.g_ret_sts_error;
1769 WHEN fnd_api.g_exc_error
1770 THEN
1771 x_return_status := fnd_api.g_ret_sts_error;
1772 WHEN fnd_api.g_exc_unexpected_error
1773 THEN
1774 x_return_status := fnd_api.g_ret_sts_unexp_error;
1775
1776 IF (NVL (g_debug, 0) > 0)
1777 THEN
1778 gme_debug.put_line ( g_pkg_name
1779 || '.'
1780 || l_api_name
1781 || ':'
1782 || 'UNEXPECTED:'
1783 || SQLERRM
1784 );
1785 END IF;
1786 WHEN OTHERS
1787 THEN
1788 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1789 x_return_status := fnd_api.g_ret_sts_unexp_error;
1790
1791 IF (NVL (g_debug, 0) > 0)
1792 THEN
1793 gme_debug.put_line ( g_pkg_name
1794 || '.'
1795 || l_api_name
1796 || ':'
1797 || 'OTHERS:'
1798 || SQLERRM
1799 );
1800 END IF;
1801 END update_batchstep_activity;
1802
1803 /*===========================================================================================
1804 Procedure
1805 delete_batchstep_activity
1806 Description
1807 This procedure is used to delete an activity and all it's children
1808 Parameters
1809 p_batchstep_activity_id OR
1810 (p_org_code,p_batch_no,step_no,activity) to uniquely identify an activity
1811 x_return_status reflects return status of the API
1812
1813 =============================================================================================*/
1814 PROCEDURE delete_batchstep_activity (
1815 p_batchstep_activity_id IN NUMBER := NULL,
1816 p_org_code IN VARCHAR2 := NULL,
1817 p_batch_no IN VARCHAR2 := NULL,
1818 p_batchstep_no IN NUMBER := NULL,
1819 p_activity IN VARCHAR2 := NULL,
1820 x_return_status OUT NOCOPY VARCHAR2
1821 )
1822 IS
1823 l_api_name CONSTANT VARCHAR2 (30) := 'delete_batchstep_activity';
1824 l_step_status NUMBER;
1825 l_batch_status NUMBER;
1826 l_batch_type NUMBER;
1827 l_activity_id NUMBER;
1828 l_batch_id NUMBER;
1829 l_batchstep_id NUMBER;
1830 l_batchstep_activity_rec gme_batch_step_activities%ROWTYPE;
1831 x_batchstep_activity_rec gme_batch_step_activities%ROWTYPE;
1832 l_cnt NUMBER;
1833 l_activity gme_batch_step_activities.activity%TYPE;
1834 l_last_update_date DATE;
1835 l_inv_trans_count NUMBER;
1836 l_rsrc_trans_count NUMBER;
1837 l_batch_hdr gme_batch_header%ROWTYPE;
1838
1839 CURSOR cur_get_batch (v_batch_id NUMBER)
1840 IS
1841 SELECT *
1842 FROM gme_batch_header
1843 WHERE batch_id = v_batch_id;
1844
1845 CURSOR cur_get_step_dtl (v_batchstep_act_id NUMBER)
1846 IS
1847 SELECT step_status, batch_status, a.batch_id, s.batchstep_id,
1848 batchstep_activity_id, activity
1849 FROM gme_batch_steps s,
1850 gme_batch_step_activities a,
1851 gme_batch_header b
1852 WHERE batchstep_activity_id = v_batchstep_act_id
1853 AND b.batch_id = a.batch_id
1854 AND s.batchstep_id = a.batchstep_id;
1855
1856 CURSOR cur_get_activity_count (v_step_id NUMBER)
1857 IS
1858 SELECT COUNT (1)
1859 FROM gme_batch_step_activities
1860 WHERE batchstep_id = v_step_id;
1861
1862 CURSOR cur_check_fpo (v_actv_id NUMBER)
1863 IS
1864 SELECT batch_type
1865 FROM gme_batch_header b, gme_batch_step_activities a
1866 WHERE b.batch_id = a.batch_id AND batchstep_activity_id = v_actv_id;
1867
1868 CURSOR cur_lock_actv (v_activity_id NUMBER)
1869 IS
1870 SELECT last_update_date
1871 FROM gme_batch_step_activities
1872 WHERE batchstep_activity_id = v_activity_id
1873 FOR UPDATE OF last_update_date NOWAIT;
1874 BEGIN
1875 IF (NVL (g_debug, 0) IN
1876 (gme_debug.g_log_statement, gme_debug.g_log_procedure)
1877 )
1878 THEN
1879 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1880 || 'Entering'
1881 );
1882 END IF;
1883
1884 /* Set the return status to success initially */
1885 x_return_status := fnd_api.g_ret_sts_success;
1886
1887 IF (p_batchstep_activity_id IS NULL AND p_activity IS NULL)
1888 THEN
1889 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
1890 'FIELD_NAME',
1891 'ACTIVITY'
1892 );
1893 RAISE fnd_api.g_exc_error;
1894 ELSE
1895 IF p_batchstep_activity_id IS NULL
1896 THEN
1897 validate_param (p_org_code => p_org_code,
1898 p_batch_no => p_batch_no,
1899 p_batchstep_no => p_batchstep_no,
1900 p_activity => p_activity,
1901 x_batch_id => l_batch_id,
1902 x_batchstep_id => l_batchstep_id,
1903 x_activity_id => l_activity_id,
1904 x_batch_status => l_batch_status,
1905 x_step_status => l_step_status,
1906 x_return_status => x_return_status
1907 );
1908
1909 IF (NVL (g_debug, 0) = gme_debug.g_log_statement)
1910 THEN
1911 gme_debug.put_line ( g_pkg_name
1912 || '.'
1913 || l_api_name
1914 || ':'
1915 || 'after the call to procedure validate_param '
1916 || x_return_status
1917 );
1918 END IF;
1919
1920 IF (x_return_status = fnd_api.g_ret_sts_error)
1921 THEN
1922 RAISE fnd_api.g_exc_error;
1923 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
1924 THEN
1925 RAISE fnd_api.g_exc_unexpected_error;
1926 END IF;
1927
1928 l_activity := p_activity;
1929 ELSE
1930 l_activity_id := p_batchstep_activity_id;
1931 OPEN cur_get_step_dtl (l_activity_id);
1932 FETCH cur_get_step_dtl INTO l_step_status,
1933 l_batch_status,
1934 l_batch_id,
1935 l_batchstep_id,
1936 l_activity_id,
1937 l_activity;
1938
1939 IF cur_get_step_dtl%NOTFOUND
1940 THEN
1941 CLOSE cur_get_step_dtl;
1942 gme_common_pvt.log_message ('GME_ACTID_NOT_FOUND');
1943 RAISE fnd_api.g_exc_error;
1944 END IF;
1945
1946 CLOSE cur_get_step_dtl;
1947 END IF;
1948 END IF;
1949
1950 -- check FPO
1951 OPEN cur_check_fpo (l_activity_id);
1952 FETCH cur_check_fpo INTO l_batch_type;
1953
1954 IF l_batch_type = 10
1955 THEN
1956 CLOSE cur_check_fpo;
1957 gme_common_pvt.log_message ('GME_FPO_ACTV_NO_DEL');
1958 RAISE fnd_api.g_exc_error;
1959 END IF;
1960
1961 CLOSE cur_check_fpo;
1962 OPEN cur_lock_actv (l_activity_id);
1963 FETCH cur_lock_actv INTO l_last_update_date;
1964 CLOSE cur_lock_actv;
1965
1966 IF (NVL (g_debug, -1) = gme_debug.g_log_statement)
1967 THEN
1968 gme_debug.put_line (' Successfully locked the activity');
1969 END IF;
1970
1971 IF l_batch_status <> 1
1972 THEN
1973 gme_common_pvt.log_message ('PM_WRONG_STATUS');
1974 RAISE fnd_api.g_exc_error;
1975 END IF;
1976
1977 OPEN cur_get_activity_count (l_batchstep_id);
1978 FETCH cur_get_activity_count INTO l_cnt;
1979
1980 IF l_cnt = 1
1981 THEN
1982 gme_common_pvt.log_message ('GME_ACTIVITY_NOT_ATTACH');
1983 RAISE fnd_api.g_exc_error;
1984 END IF;
1985
1986 l_batchstep_activity_rec.batch_id := l_batch_id;
1987 l_batchstep_activity_rec.batchstep_id := l_batchstep_id;
1988 l_batchstep_activity_rec.batchstep_activity_id := l_activity_id;
1989 -- load temp table so that save_batch routine does resource txn consolidation
1990 OPEN cur_get_batch (l_batch_id);
1991 FETCH cur_get_batch INTO l_batch_hdr;
1992 CLOSE cur_get_batch;
1993
1994 IF l_batch_hdr.update_inventory_ind = 'Y'
1995 THEN
1996 gme_trans_engine_util.load_rsrc_trans (p_batch_row => l_batch_hdr,
1997 x_rsc_row_count => l_rsrc_trans_count,
1998 x_return_status => x_return_status
1999 );
2000
2001 IF (NVL (g_debug, 0) = gme_debug.g_log_statement)
2002 THEN
2003 gme_debug.put_line ( g_pkg_name
2004 || '.'
2005 || l_api_name
2006 || ':'
2007 || 'after call to GME_TRANS_ENGINE_UTIL.load_rsrc_trans return status is '
2008 || x_return_status
2009 );
2010 END IF;
2011
2012 IF (x_return_status = fnd_api.g_ret_sts_error)
2013 THEN
2014 RAISE fnd_api.g_exc_error;
2015 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2016 THEN
2017 RAISE fnd_api.g_exc_unexpected_error;
2018 END IF;
2019 END IF;
2020
2021 gme_delete_batch_step_pvt.delete_activity (p_batch_step_activities_rec => l_batchstep_activity_rec,
2022 x_return_status => x_return_status
2023 );
2024
2025 IF (NVL (g_debug, -1) = gme_debug.g_log_statement)
2026 THEN
2027 gme_debug.put_line ( 'after call to gme_delete_batch_step_pvt.delete_activity return status is '
2028 || x_return_status
2029 );
2030 END IF;
2031
2032 IF (x_return_status = fnd_api.g_ret_sts_error)
2033 THEN
2034 RAISE fnd_api.g_exc_error;
2035 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2036 THEN
2037 RAISE fnd_api.g_exc_unexpected_error;
2038 END IF;
2039
2040 IF (NVL (g_debug, 0) IN
2041 (gme_debug.g_log_statement, gme_debug.g_log_procedure)
2042 )
2043 THEN
2044 gme_debug.put_line ( g_pkg_name
2045 || '.'
2046 || l_api_name
2047 || ':'
2048 || 'Exiting with '
2049 || x_return_status
2050 );
2051 END IF;
2052 EXCEPTION
2053 WHEN app_exception.record_lock_exception
2054 THEN
2055 gme_common_pvt.log_message ('GME_RECORD_LOCKED',
2056 'TABLE_NAME',
2057 'GME_BATCH_STEP_ACTIVITIES',
2058 'RECORD',
2059 'Activity',
2060 'KEY',
2061 l_activity
2062 );
2063 x_return_status := fnd_api.g_ret_sts_error;
2064 WHEN fnd_api.g_exc_error
2065 THEN
2066 x_return_status := fnd_api.g_ret_sts_error;
2067 WHEN fnd_api.g_exc_unexpected_error
2068 THEN
2069 x_return_status := fnd_api.g_ret_sts_unexp_error;
2070
2071 IF (NVL (g_debug, 0) > 0)
2072 THEN
2073 gme_debug.put_line ( g_pkg_name
2074 || '.'
2075 || l_api_name
2076 || ':'
2077 || 'UNEXPECTED:'
2078 || SQLERRM
2079 );
2080 END IF;
2081 WHEN OTHERS
2082 THEN
2083 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2084 x_return_status := fnd_api.g_ret_sts_unexp_error;
2085
2086 IF (NVL (g_debug, 0) > 0)
2087 THEN
2088 gme_debug.put_line ( g_pkg_name
2089 || '.'
2090 || l_api_name
2091 || ':'
2092 || 'OTHERS:'
2093 || SQLERRM
2094 );
2095 END IF;
2096 END delete_batchstep_activity;
2097 END gme_batchstep_act_pvt;