[Home] [Help]
PACKAGE BODY: APPS.GME_BATCHSTEP_ACT_PVT
Source
1 PACKAGE BODY gme_batchstep_act_pvt AS
2 /* $Header: GMEVACTB.pls 120.2 2005/09/07 12:50:10 snene noship $ */
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 PROCEDURE insert_batchstep_activity (
673 p_batchstep_activity_rec IN gme_batch_step_activities%ROWTYPE,
674 p_batchstep_resource_tbl IN gme_create_step_pvt.resources_tab,
675 p_org_code IN VARCHAR2 := NULL,
676 p_batch_no IN VARCHAR2 := NULL,
677 p_batchstep_no IN NUMBER := NULL,
678 p_ignore_qty_below_cap IN VARCHAR2
679 DEFAULT fnd_api.g_false,
680 p_validate_flexfield IN VARCHAR2
681 DEFAULT fnd_api.g_false,
682 x_batchstep_activity_rec OUT NOCOPY gme_batch_step_activities%ROWTYPE,
683 x_return_status OUT NOCOPY VARCHAR2
684 )
685 IS
686 l_api_name CONSTANT VARCHAR2 (30)
687 := 'insert_batchstep_activity';
688 l_batch_id NUMBER;
689 l_batchstep_id NUMBER;
690 l_activity_id NUMBER;
691 l_batch_status NUMBER;
692 l_step_status NUMBER;
693 l_organization_id PLS_INTEGER;
694 l_batchstep_activity_rec gme_batch_step_activities%ROWTYPE;
695 l_batchstep_activity_out_rec gme_batch_step_activities%ROWTYPE;
696 l_batchstep_resource_rec gme_batch_step_resources%ROWTYPE;
697 l_batchstep_resource_out_rec gme_batch_step_resources%ROWTYPE;
698 l_prim_rsrc_count NUMBER;
699 l_rsrc_id PLS_INTEGER;
700
701 -- Define CURSORS
702 CURSOR cur_validate_step (v_step_id NUMBER)
703 IS
704 SELECT batch_id
705 FROM gme_batch_steps
706 WHERE batchstep_id = v_step_id;
707 BEGIN
708 IF (NVL (g_debug, 0) IN
709 (gme_debug.g_log_statement, gme_debug.g_log_procedure)
710 )
711 THEN
712 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
713 || 'Entering'
714 );
715 END IF;
716
717 /* Set the return status to success initially */
718 x_return_status := fnd_api.g_ret_sts_success;
719
720 IF p_batchstep_activity_rec.activity IS NULL
721 THEN
722 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
723 'FIELD_NAME',
724 'ACTIVITY'
725 );
726 RAISE fnd_api.g_exc_error;
727 END IF;
728
729 IF p_batchstep_activity_rec.batchstep_id IS NOT NULL
730 THEN
731 -- validate the key provided
732 l_batchstep_id := p_batchstep_activity_rec.batchstep_id;
733 OPEN cur_validate_step (l_batchstep_id);
734 FETCH cur_validate_step INTO l_batch_id;
735
736 IF cur_validate_step%NOTFOUND
737 THEN
738 CLOSE cur_validate_step;
739 gme_common_pvt.log_message ('GME_BATCH_STEP_NOT_FOUND',
740 'STEP_ID',
741 l_batchstep_id
742 );
743 RAISE fnd_api.g_exc_error;
744 END IF;
745
746 CLOSE cur_validate_step;
747 ELSE
748 -- validate the combination provided
749 validate_param (p_org_code => p_org_code,
750 p_batch_no => p_batch_no,
751 p_batchstep_no => p_batchstep_no,
752 p_activity => p_batchstep_activity_rec.activity,
753 x_batch_id => l_batch_id,
754 x_batchstep_id => l_batchstep_id,
755 x_activity_id => l_activity_id,
756 x_batch_status => l_batch_status,
757 x_step_status => l_step_status,
758 x_return_status => x_return_status
759 );
760
761 IF (NVL (g_debug, 0) = gme_debug.g_log_statement)
762 THEN
763 gme_debug.put_line ( g_pkg_name
764 || '.'
765 || l_api_name
766 || ':'
767 || 'after the call to procedure validate_param '
768 || x_return_status
769 );
770 END IF;
771
772 IF (x_return_status = fnd_api.g_ret_sts_error)
773 THEN
774 RAISE fnd_api.g_exc_error;
775 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
776 THEN
777 RAISE fnd_api.g_exc_unexpected_error;
778 END IF;
779 END IF;
780
781 validate_activity_param (p_batchstep_activity_rec => p_batchstep_activity_rec,
782 p_step_id => l_batchstep_id,
783 p_validate_flexfield => p_validate_flexfield,
784 p_action => 'INSERT',
785 x_batchstep_activity_rec => l_batchstep_activity_rec,
786 x_step_status => l_step_status,
787 x_return_status => x_return_status
788 );
789
790 IF (NVL (g_debug, 0) = gme_debug.g_log_statement)
791 THEN
792 gme_debug.put_line ( g_pkg_name
793 || '.'
794 || l_api_name
795 || ':'
796 || 'after the call to procedure validate_activity_param '
797 || x_return_status
798 );
799 END IF;
800
801 IF (x_return_status = fnd_api.g_ret_sts_error)
802 THEN
803 RAISE fnd_api.g_exc_error;
804 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
805 THEN
806 RAISE fnd_api.g_exc_unexpected_error;
807 END IF;
808
809 l_batchstep_activity_rec.batch_id := l_batch_id;
810 l_batchstep_activity_rec.batchstep_id := l_batchstep_id;
811 l_batchstep_activity_rec.oprn_line_id := NULL;
812 l_batchstep_activity_rec.offset_interval :=
813 NVL (l_batchstep_activity_rec.offset_interval, 0);
814 l_batchstep_activity_rec.delete_mark := 0;
815
816 -- check that there are resources and that there is one (and only one) primary resource
817 IF p_batchstep_resource_tbl.COUNT = 0
818 THEN
819 gme_common_pvt.log_message (p_message_code => 'GME_RESOURCE_NOT_ATTACH',
820 p_token1_name => 'ACTIVITY',
821 p_token1_value => p_batchstep_activity_rec.activity,
822 p_token2_name => 'STEPNO',
823 p_token2_value => p_batchstep_no
824 );
825 RAISE fnd_api.g_exc_error;
826 END IF;
827
828 l_prim_rsrc_count := 0;
829
830 FOR i IN 1 .. p_batchstep_resource_tbl.COUNT
831 LOOP
832 gme_batchstep_rsrc_pvt.validate_param (p_org_code => p_org_code,
833 p_batch_no => p_batch_no,
834 p_batchstep_no => p_batchstep_no,
835 p_activity => p_batchstep_activity_rec.activity,
836 p_resource => p_batchstep_resource_tbl (i
837 ).resources,
838 x_organization_id => l_organization_id,
839 x_batch_id => l_batch_id,
840 x_batchstep_id => l_batchstep_id,
841 x_activity_id => l_activity_id,
842 x_rsrc_id => l_rsrc_id,
843 x_step_status => l_step_status,
844 x_return_status => x_return_status
845 );
846
847 IF (x_return_status = fnd_api.g_ret_sts_error)
848 THEN
849 RAISE fnd_api.g_exc_error;
850 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
851 THEN
852 RAISE fnd_api.g_exc_unexpected_error;
853 END IF;
854
855 gme_batchstep_rsrc_pvt.validate_rsrc_param (p_batchstep_resource_rec => p_batchstep_resource_tbl (i
856 ),
857 p_activity_id => l_activity_id,
858 p_ignore_qty_below_cap => p_ignore_qty_below_cap,
859 p_validate_flexfield => p_validate_flexfield,
860 p_action => 'INSERT',
861 x_batchstep_resource_rec => l_batchstep_resource_rec,
862 x_step_status => l_step_status,
863 x_return_status => x_return_status
864 );
865
866 IF (x_return_status = fnd_api.g_ret_sts_error)
867 THEN
868 RAISE fnd_api.g_exc_error;
869 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
870 THEN
871 RAISE fnd_api.g_exc_unexpected_error;
872 END IF;
873
874 IF NVL (p_batchstep_resource_tbl (i).prim_rsrc_ind, 0) = 1
875 THEN
876 l_prim_rsrc_count := l_prim_rsrc_count + 1;
877 END IF;
878 END LOOP;
879
880 IF l_prim_rsrc_count <> 1
881 THEN
882 gme_common_pvt.log_message ('GME_ONLY_ONE_PRIM_RSRC',
883 'ACTIVITY',
884 l_batchstep_activity_rec.activity,
885 'STEPNO',
886 p_batchstep_no
887 );
888 RAISE fnd_api.g_exc_error;
889 END IF;
890
891 IF (NVL (g_debug, 0) = gme_debug.g_log_statement)
892 THEN
893 gme_debug.put_line ( g_pkg_name
894 || '.'
895 || l_api_name
896 || ':'
897 || 'before call to GME_BATCH_STEP_ACTIVITIES_DBL.insert_row batch_id='
898 || l_batchstep_activity_rec.batch_id
899 || ' and step_id ='
900 || l_batchstep_activity_rec.batchstep_id
901 );
902 END IF;
903
904 IF NOT (gme_batch_step_activities_dbl.insert_row (l_batchstep_activity_rec,
905 l_batchstep_activity_out_rec
906 )
907 )
908 THEN
909 RAISE fnd_api.g_exc_error;
910 END IF;
911
912 IF (NVL (g_debug, -1) = gme_debug.g_log_statement)
913 THEN
914 gme_debug.put_line ( g_pkg_name
915 || '.'
916 || l_api_name
917 || ':'
918 || 'after call to GME_BATCH_STEP_ACTIVITIES_DBL.insert_row activity_id='
919 || l_batchstep_activity_out_rec.batchstep_activity_id
920 );
921 END IF;
922
923 gme_batch_step_chg_pvt.set_activity_sequence_num (p_batchstep_activity_rec.batch_id
924 );
925
926 FOR i IN 1 .. p_batchstep_resource_tbl.COUNT
927 LOOP
928 l_batchstep_resource_rec := p_batchstep_resource_tbl (i);
929 l_batchstep_resource_rec.batch_id :=
930 l_batchstep_activity_rec.batch_id;
931 l_batchstep_resource_rec.batchstep_id :=
932 l_batchstep_activity_rec.batchstep_id;
933 l_batchstep_resource_rec.batchstep_activity_id :=
934 l_batchstep_activity_out_rec.batchstep_activity_id;
935 gme_batchstep_rsrc_pvt.insert_batchstep_rsrc (p_batchstep_resource_rec => l_batchstep_resource_rec,
936 x_batchstep_resource_rec => l_batchstep_resource_out_rec,
937 x_return_status => x_return_status
938 );
939
940 IF (x_return_status = fnd_api.g_ret_sts_error)
941 THEN
942 RAISE fnd_api.g_exc_error;
943 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
944 THEN
945 RAISE fnd_api.g_exc_unexpected_error;
946 END IF;
947 END LOOP;
948
949 x_batchstep_activity_rec := l_batchstep_activity_out_rec;
950
951 IF (NVL (g_debug, 0) IN
952 (gme_debug.g_log_statement, gme_debug.g_log_procedure)
953 )
954 THEN
955 gme_debug.put_line ( g_pkg_name
956 || '.'
957 || l_api_name
958 || ':'
959 || 'Exiting with '
960 || x_return_status
961 );
962 END IF;
963 EXCEPTION
964 WHEN fnd_api.g_exc_error
965 THEN
966 x_batchstep_activity_rec := NULL;
967 x_return_status := fnd_api.g_ret_sts_error;
968 WHEN fnd_api.g_exc_unexpected_error
969 THEN
970 x_batchstep_activity_rec := NULL;
971 x_return_status := fnd_api.g_ret_sts_unexp_error;
972
973 IF (NVL (g_debug, 0) > 0)
974 THEN
975 gme_debug.put_line ( g_pkg_name
976 || '.'
977 || l_api_name
978 || ':'
979 || 'UNEXPECTED:'
980 || SQLERRM
981 );
982 END IF;
983 WHEN OTHERS
984 THEN
985 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
986 x_batchstep_activity_rec := NULL;
987 x_return_status := fnd_api.g_ret_sts_unexp_error;
988
989 IF (NVL (g_debug, 0) > 0)
990 THEN
991 gme_debug.put_line ( g_pkg_name
992 || '.'
993 || l_api_name
994 || ':'
995 || 'OTHERS:'
996 || SQLERRM
997 );
998 END IF;
999 END insert_batchstep_activity;
1000
1001 /*===========================================================================================
1002 Procedure
1003 update_batchstep_activity
1004 Description
1005 This procedure is used to update an activity
1006 Parameters
1007 p_batchstep_activity_id OR
1008 (p_org_code,p_batch_no,step_no,activity) to uniquely identify an activity
1009 p_batchstep_activity_rec GME_BATCH_STEP_ACTIVITIES%ROWTYPE - details of the activity
1010 x_batchstep_activity_rec returns the newly updated row for further processing
1011 x_return_status reflects return status of the API
1012 History
1013 =============================================================================================*/
1014 PROCEDURE update_batchstep_activity (
1015 p_batchstep_activity_rec IN gme_batch_step_activities%ROWTYPE,
1016 p_org_code IN VARCHAR2 := NULL,
1017 p_batch_no IN VARCHAR2 := NULL,
1018 p_batchstep_no IN NUMBER := NULL,
1019 p_validate_flexfield IN VARCHAR2
1020 DEFAULT fnd_api.g_false,
1021 x_batchstep_activity_rec OUT NOCOPY gme_batch_step_activities%ROWTYPE,
1022 x_return_status OUT NOCOPY VARCHAR2
1023 )
1024 IS
1025 l_api_name CONSTANT VARCHAR2 (30)
1026 := 'update_batchstep_activity';
1027 l_batch_id NUMBER;
1028 l_activity_id NUMBER;
1029 l_activity gme_batch_step_activities.activity%TYPE;
1030 l_batchstep_id NUMBER;
1031 l_batch_status NUMBER;
1032 l_step_status NUMBER;
1033 l_batch_type NUMBER;
1034 l_field_updated BOOLEAN := FALSE;
1035 l_upd_plan_date BOOLEAN := FALSE;
1036 l_upd_act_date BOOLEAN := FALSE;
1037 l_update_resources BOOLEAN := FALSE;
1038 l_flex_validate BOOLEAN := FALSE;
1039 l_last_update_date DATE;
1040 l_batchstep_activity_rec gme_batch_step_activities%ROWTYPE;
1041 l_batchstep_activity_out_rec gme_batch_step_activities%ROWTYPE;
1042 --Bug 3027004 Mohit Kapoor Added two variables
1043 l_inv_trans_count NUMBER;
1044 l_rsrc_trans_count NUMBER;
1045
1046 -- Define CURSORS
1047
1048 CURSOR cur_dates (v_act_id NUMBER)
1049 IS
1050 SELECT *
1051 FROM gme_batch_step_resources
1052 WHERE batchstep_activity_id = v_act_id;
1053
1054 l_temp_resources cur_dates%ROWTYPE;
1055 l_resource VARCHAR2 (1000);
1056
1057 CURSOR cur_get_dtl (v_act_id NUMBER)
1058 IS
1059 SELECT a.batch_id, a.batchstep_id, step_status, activity
1060 FROM gme_batch_step_activities a, gme_batch_steps s
1061 WHERE batchstep_activity_id = v_act_id
1062 AND a.batchstep_id = s.batchstep_id;
1063
1064 CURSOR cur_get_step_dtl (v_step_id NUMBER)
1065 IS
1066 SELECT *
1067 FROM gme_batch_steps
1068 WHERE batchstep_id = v_step_id;
1069
1070 l_step_dtl gme_batch_steps%ROWTYPE;
1071
1072 CURSOR cur_get_batch (v_batch_id NUMBER)
1073 IS
1074 SELECT *
1075 FROM gme_batch_header
1076 WHERE batch_id = v_batch_id;
1077
1078 l_batch_hdr gme_batch_header%ROWTYPE;
1079
1080 CURSOR cur_check_fpo (v_actv_id NUMBER)
1081 IS
1082 SELECT batch_type
1083 FROM gme_batch_header b, gme_batch_step_activities a
1084 WHERE b.batch_id = a.batch_id AND batchstep_activity_id = v_actv_id;
1085
1086 CURSOR cur_lock_actv (v_activity_id NUMBER)
1087 IS
1088 SELECT last_update_date
1089 FROM gme_batch_step_activities
1090 WHERE batchstep_activity_id = v_activity_id
1091 FOR UPDATE OF last_update_date NOWAIT;
1092
1093 l_seq_dep_ind gme_batch_step_activities.sequence_dependent_ind%TYPE;
1094 BEGIN
1095 IF (NVL (g_debug, 0) IN
1096 (gme_debug.g_log_statement, gme_debug.g_log_procedure)
1097 )
1098 THEN
1099 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1100 || 'Entering'
1101 );
1102 END IF;
1103
1104 /* Set the return status to success initially */
1105 x_return_status := fnd_api.g_ret_sts_success;
1106
1107 IF ( p_batchstep_activity_rec.activity IS NULL
1108 AND p_batchstep_activity_rec.batchstep_activity_id IS NULL
1109 )
1110 THEN
1111 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
1112 'FIELD_NAME',
1113 'ACTIVITY'
1114 );
1115 RAISE fnd_api.g_exc_error;
1116 END IF;
1117
1118 IF p_batchstep_activity_rec.batchstep_activity_id IS NOT NULL
1119 THEN
1120 -- validate the key provided
1121 l_activity_id := p_batchstep_activity_rec.batchstep_activity_id;
1122 OPEN cur_get_dtl (l_activity_id);
1123 FETCH cur_get_dtl INTO l_batch_id, l_batchstep_id, l_step_status,
1124 l_activity;
1125
1126 IF cur_get_dtl%NOTFOUND
1127 THEN
1128 CLOSE cur_get_dtl;
1129 gme_common_pvt.log_message ('GME_ACTID_NOT_FOUND');
1130 RAISE fnd_api.g_exc_error;
1131 END IF;
1132
1133 CLOSE cur_get_dtl;
1134 ELSE
1135 -- validate the combination provided
1136 l_activity := p_batchstep_activity_rec.activity;
1137 validate_param (p_org_code => p_org_code,
1138 p_batch_no => p_batch_no,
1139 p_batchstep_no => p_batchstep_no,
1140 p_activity => l_activity,
1141 x_batch_id => l_batch_id,
1142 x_batchstep_id => l_batchstep_id,
1143 x_activity_id => l_activity_id,
1144 x_batch_status => l_batch_status,
1145 x_step_status => l_step_status,
1146 x_return_status => x_return_status
1147 );
1148
1149 IF (NVL (g_debug, 0) = gme_debug.g_log_statement)
1150 THEN
1151 gme_debug.put_line ( g_pkg_name
1152 || '.'
1153 || l_api_name
1154 || ':'
1155 || 'after the call to procedure validate_param '
1156 || x_return_status
1157 );
1158 END IF;
1159
1160 IF (x_return_status = fnd_api.g_ret_sts_error)
1161 THEN
1162 RAISE fnd_api.g_exc_error;
1163 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
1164 THEN
1165 RAISE fnd_api.g_exc_unexpected_error;
1166 END IF;
1167 END IF;
1168
1169 -- Check if this is an FPO
1170 OPEN cur_check_fpo (l_activity_id);
1171 FETCH cur_check_fpo INTO l_batch_type;
1172
1173 IF l_batch_type = 10
1174 THEN
1175 CLOSE cur_check_fpo;
1176 gme_common_pvt.log_message ('GME_FPO_ACTV_NO_EDIT');
1177 RAISE fnd_api.g_exc_error;
1178 END IF;
1179
1180 CLOSE cur_check_fpo;
1181 OPEN cur_lock_actv (l_activity_id);
1182 FETCH cur_lock_actv INTO l_last_update_date;
1183 CLOSE cur_lock_actv;
1184
1185 IF (NVL (g_debug, -1) = gme_debug.g_log_statement)
1186 THEN
1187 gme_debug.put_line (' Successfully locked the activity');
1188 END IF;
1189
1190 IF NOT (gme_batch_step_activities_dbl.fetch_row (p_batchstep_activity_rec,
1191 l_batchstep_activity_rec
1192 )
1193 )
1194 THEN
1195 RAISE fnd_api.g_exc_error;
1196 END IF;
1197
1198 l_seq_dep_ind := l_batchstep_activity_rec.sequence_dependent_ind;
1199
1200 IF l_step_status IN (4, 5)
1201 THEN
1202 gme_common_pvt.log_message ('PC_STEP_STATUS_ERR');
1203 RAISE fnd_api.g_exc_error;
1204 END IF;
1205
1206 IF p_batchstep_activity_rec.offset_interval = fnd_api.g_miss_num
1207 THEN
1208 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
1209 'FIELD_NAME',
1210 'offset_interval'
1211 );
1212 RAISE fnd_api.g_exc_error;
1213 ELSIF ( p_batchstep_activity_rec.offset_interval IS NOT NULL
1214 AND (l_batchstep_activity_rec.offset_interval <>
1215 p_batchstep_activity_rec.offset_interval
1216 )
1217 )
1218 THEN
1219 l_field_updated := TRUE;
1220 l_batchstep_activity_rec.offset_interval :=
1221 p_batchstep_activity_rec.offset_interval;
1222 END IF;
1223
1224 OPEN cur_get_step_dtl (l_batchstep_id);
1225 FETCH cur_get_step_dtl INTO l_step_dtl;
1226 CLOSE cur_get_step_dtl;
1227
1228 IF l_step_status IN (1, 2)
1229 THEN
1230 IF p_batchstep_activity_rec.plan_start_date = fnd_api.g_miss_date
1231 THEN
1232 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
1233 'FIELD_NAME',
1234 'plan_start_date'
1235 );
1236 RAISE fnd_api.g_exc_error;
1237 ELSIF p_batchstep_activity_rec.plan_start_date IS NOT NULL
1238 AND (p_batchstep_activity_rec.plan_start_date <>
1239 l_batchstep_activity_rec.plan_start_date
1240 )
1241 THEN
1242 -- Check that the activity plan dates fall within the step dates.
1243 IF p_batchstep_activity_rec.plan_start_date <
1244 l_step_dtl.plan_start_date
1245 OR p_batchstep_activity_rec.plan_start_date >
1246 l_step_dtl.plan_cmplt_date
1247 THEN
1248 gme_common_pvt.log_message ('GME_ACTV_PLAN_DATE',
1249 'ACTIVITY',
1250 l_activity
1251 );
1252 RAISE fnd_api.g_exc_error;
1253 END IF;
1254
1255 l_field_updated := TRUE;
1256 l_upd_plan_date := TRUE;
1257 l_batchstep_activity_rec.plan_start_date :=
1258 p_batchstep_activity_rec.plan_start_date;
1259 END IF;
1260
1261 IF p_batchstep_activity_rec.plan_cmplt_date = fnd_api.g_miss_date
1262 THEN
1263 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
1264 'FIELD_NAME',
1265 'plan_cmplt_date'
1266 );
1267 RAISE fnd_api.g_exc_error;
1268 ELSIF p_batchstep_activity_rec.plan_cmplt_date IS NOT NULL
1269 AND (p_batchstep_activity_rec.plan_cmplt_date <>
1270 l_batchstep_activity_rec.plan_cmplt_date
1271 )
1272 THEN
1273 -- Check that the activity plan dates fall within the step dates.
1274 IF p_batchstep_activity_rec.plan_cmplt_date >
1275 l_step_dtl.plan_cmplt_date
1276 THEN
1277 gme_common_pvt.log_message ('GME_ACTV_PLAN_DATE',
1278 'ACTIVITY',
1279 l_activity
1280 );
1281 RAISE fnd_api.g_exc_error;
1282 END IF;
1283
1284 l_field_updated := TRUE;
1285 l_upd_plan_date := TRUE;
1286 l_batchstep_activity_rec.plan_cmplt_date :=
1287 p_batchstep_activity_rec.plan_cmplt_date;
1288 END IF;
1289
1290 IF ( l_upd_plan_date
1291 AND l_batchstep_activity_rec.plan_start_date >
1292 l_batchstep_activity_rec.plan_cmplt_date
1293 )
1294 THEN
1295 gme_common_pvt.log_message ('PM_BADSTARTDATE');
1296 RAISE fnd_api.g_exc_error;
1297 END IF;
1298 END IF;
1299
1300 IF l_step_dtl.step_status IN (2, 3)
1301 THEN
1302 IF p_batchstep_activity_rec.actual_start_date = fnd_api.g_miss_date
1303 THEN
1304 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
1305 'FIELD_NAME',
1306 'actual_start_date'
1307 );
1308 RAISE fnd_api.g_exc_error;
1309 ELSIF p_batchstep_activity_rec.actual_start_date IS NOT NULL
1310 AND (p_batchstep_activity_rec.actual_start_date <>
1311 l_batchstep_activity_rec.actual_start_date
1312 )
1313 THEN
1314 -- Check that actual start date is not in the future
1315 IF p_batchstep_activity_rec.actual_start_date >
1316 gme_common_pvt.g_timestamp
1317 THEN
1318 fnd_message.set_name ('GMA', 'SY_NOFUTUREDATE');
1319 fnd_msg_pub.ADD;
1320 RAISE fnd_api.g_exc_error;
1321 END IF;
1322
1323 -- Check that the activity actual dates fall within the step dates.
1324 IF ( p_batchstep_activity_rec.actual_start_date <
1325 l_step_dtl.actual_start_date
1326 OR p_batchstep_activity_rec.actual_start_date >
1327 l_step_dtl.actual_cmplt_date
1328 )
1329 THEN
1330 gme_common_pvt.log_message ('GME_ACTV_ACTUAL_DATE',
1331 'ACTIVITY',
1332 l_activity
1333 );
1334 RAISE fnd_api.g_exc_error;
1335 END IF;
1336
1337 l_field_updated := TRUE;
1338 l_upd_act_date := TRUE;
1339 l_batchstep_activity_rec.actual_start_date :=
1340 p_batchstep_activity_rec.actual_start_date;
1341 END IF;
1342 END IF;
1343
1344 IF l_step_dtl.step_status = 3
1345 THEN
1346 IF p_batchstep_activity_rec.actual_cmplt_date = fnd_api.g_miss_date
1347 THEN
1348 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
1349 'FIELD_NAME',
1350 'actual_cmplt_date'
1351 );
1352 RAISE fnd_api.g_exc_error;
1353 ELSIF p_batchstep_activity_rec.actual_cmplt_date IS NOT NULL
1354 AND (p_batchstep_activity_rec.actual_cmplt_date <>
1355 l_batchstep_activity_rec.actual_cmplt_date
1356 )
1357 THEN
1358 -- Check that actual cmplt date is not in the future
1359 IF p_batchstep_activity_rec.actual_cmplt_date >
1360 gme_common_pvt.g_timestamp
1361 THEN
1362 fnd_message.set_name ('GMA', 'SY_NOFUTUREDATE');
1363 fnd_msg_pub.ADD;
1364 RAISE fnd_api.g_exc_error;
1365 END IF;
1366
1367 IF p_batchstep_activity_rec.actual_cmplt_date IS NOT NULL
1368 AND p_batchstep_activity_rec.actual_cmplt_date >
1369 l_step_dtl.actual_cmplt_date
1370 THEN
1371 gme_common_pvt.log_message ('GME_ACTV_ACTUAL_DATE',
1372 'ACTIVITY',
1373 l_activity
1374 );
1375 RAISE fnd_api.g_exc_error;
1376 END IF;
1377
1378 l_field_updated := TRUE;
1379 l_upd_act_date := TRUE;
1380 l_batchstep_activity_rec.actual_cmplt_date :=
1381 p_batchstep_activity_rec.actual_cmplt_date;
1382 END IF;
1383 END IF;
1384
1385 IF l_upd_act_date
1386 AND (l_batchstep_activity_rec.actual_start_date >
1387 l_batchstep_activity_rec.actual_cmplt_date
1388 )
1389 THEN
1390 gme_common_pvt.log_message ('PM_BADSTARTDATE');
1391 RAISE fnd_api.g_exc_error;
1392 END IF;
1393
1394 -- check that activities dates are within resource dates
1395 IF l_upd_plan_date OR l_upd_act_date
1396 THEN
1397 OPEN cur_dates (l_activity_id);
1398
1399 LOOP
1400 FETCH cur_dates INTO l_temp_resources;
1401 EXIT WHEN cur_dates%NOTFOUND;
1402
1403 IF ( l_batchstep_activity_rec.actual_start_date IS NOT NULL
1404 AND l_batchstep_activity_rec.actual_start_date >
1405 l_temp_resources.actual_start_date
1406 )
1407 OR ( l_batchstep_activity_rec.actual_cmplt_date IS NOT NULL
1408 AND l_batchstep_activity_rec.actual_cmplt_date <
1409 l_temp_resources.actual_cmplt_date
1410 )
1411 OR ( l_batchstep_activity_rec.plan_start_date IS NOT NULL
1412 AND l_batchstep_activity_rec.plan_start_date >
1413 l_temp_resources.plan_start_date
1414 )
1415 OR ( l_batchstep_activity_rec.plan_cmplt_date IS NOT NULL
1416 AND l_batchstep_activity_rec.plan_cmplt_date <
1417 l_temp_resources.plan_cmplt_date
1418 )
1419 THEN
1420 l_resource := l_resource || ' ' || l_temp_resources.resources;
1421 END IF;
1422 END LOOP;
1423
1424 CLOSE cur_dates;
1425
1426 IF l_resource IS NOT NULL
1427 THEN
1428 gme_common_pvt.log_message ('GME_RSRC_DATES_NOT_ALLOWED',
1429 'RESOURCE',
1430 'l_resource'
1431 );
1432 RAISE fnd_api.g_exc_error;
1433 END IF;
1434 END IF;
1435
1436 --fix to g_miss_num and comparison to not NULL for l_batchstep_activity_rec
1437 IF p_batchstep_activity_rec.plan_activity_factor =
1438 fnd_api.g_miss_num
1439 AND l_batchstep_activity_rec.plan_activity_factor IS NOT NULL
1440 THEN
1441 gme_common_pvt.log_message ('GME_PLAN_ACTV_FACT_NOTNULL');
1442 RAISE fnd_api.g_exc_error;
1443 END IF;
1444
1445 IF ( p_batchstep_activity_rec.plan_activity_factor IS NOT NULL
1446 AND l_batchstep_activity_rec.plan_activity_factor IS NULL
1447 )
1448 OR (p_batchstep_activity_rec.plan_activity_factor <>
1449 l_batchstep_activity_rec.plan_activity_factor
1450 )
1451 THEN
1452 IF l_step_status = 1
1453 THEN
1454 l_field_updated := TRUE;
1455 l_update_resources := TRUE;
1456 l_batchstep_activity_rec.plan_activity_factor :=
1457 p_batchstep_activity_rec.plan_activity_factor;
1458 END IF;
1459 END IF;
1460
1461 --fix to g_miss_num and comparison to not NULL for l_batchstep_activity_rec
1462 IF p_batchstep_activity_rec.actual_activity_factor =
1463 fnd_api.g_miss_num
1464 AND l_batchstep_activity_rec.actual_activity_factor IS NOT NULL
1465 THEN
1466 gme_common_pvt.log_message ('GME_ACTUAL_ACTV_FACT_NOTNULL');
1467 RAISE fnd_api.g_exc_error;
1468 END IF;
1469
1470 IF ( p_batchstep_activity_rec.actual_activity_factor IS NOT NULL
1471 AND l_batchstep_activity_rec.actual_activity_factor IS NULL
1472 )
1473 OR (p_batchstep_activity_rec.actual_activity_factor <>
1474 l_batchstep_activity_rec.actual_activity_factor
1475 )
1476 THEN
1477 IF l_step_status IN (2, 3)
1478 THEN
1479 l_field_updated := TRUE;
1480 l_update_resources := TRUE;
1481 l_batchstep_activity_rec.actual_activity_factor :=
1482 p_batchstep_activity_rec.actual_activity_factor;
1483 END IF;
1484 END IF;
1485
1486 IF (fnd_api.to_boolean (p_validate_flexfield))
1487 THEN
1488 IF NVL (p_batchstep_activity_rec.attribute_category, ' ') <>
1489 NVL (l_batchstep_activity_rec.attribute_category, ' ')
1490 OR NVL (p_batchstep_activity_rec.attribute1, ' ') <>
1491 NVL (l_batchstep_activity_rec.attribute1, ' ')
1492 OR NVL (p_batchstep_activity_rec.attribute2, ' ') <>
1493 NVL (l_batchstep_activity_rec.attribute2, ' ')
1494 OR NVL (p_batchstep_activity_rec.attribute3, ' ') <>
1495 NVL (l_batchstep_activity_rec.attribute3, ' ')
1496 OR NVL (p_batchstep_activity_rec.attribute4, ' ') <>
1497 NVL (l_batchstep_activity_rec.attribute4, ' ')
1498 OR NVL (p_batchstep_activity_rec.attribute5, ' ') <>
1499 NVL (l_batchstep_activity_rec.attribute5, ' ')
1500 OR NVL (p_batchstep_activity_rec.attribute6, ' ') <>
1501 NVL (l_batchstep_activity_rec.attribute6, ' ')
1502 OR NVL (p_batchstep_activity_rec.attribute7, ' ') <>
1503 NVL (l_batchstep_activity_rec.attribute7, ' ')
1504 OR NVL (p_batchstep_activity_rec.attribute8, ' ') <>
1505 NVL (l_batchstep_activity_rec.attribute8, ' ')
1506 OR NVL (p_batchstep_activity_rec.attribute9, ' ') <>
1507 NVL (l_batchstep_activity_rec.attribute9, ' ')
1508 OR NVL (p_batchstep_activity_rec.attribute10, ' ') <>
1509 NVL (l_batchstep_activity_rec.attribute10, ' ')
1510 OR NVL (p_batchstep_activity_rec.attribute11, ' ') <>
1511 NVL (l_batchstep_activity_rec.attribute11, ' ')
1512 OR NVL (p_batchstep_activity_rec.attribute12, ' ') <>
1513 NVL (l_batchstep_activity_rec.attribute12, ' ')
1514 OR NVL (p_batchstep_activity_rec.attribute13, ' ') <>
1515 NVL (l_batchstep_activity_rec.attribute13, ' ')
1516 OR NVL (p_batchstep_activity_rec.attribute14, ' ') <>
1517 NVL (l_batchstep_activity_rec.attribute14, ' ')
1518 OR NVL (p_batchstep_activity_rec.attribute15, ' ') <>
1519 NVL (l_batchstep_activity_rec.attribute15, ' ')
1520 OR NVL (p_batchstep_activity_rec.attribute16, ' ') <>
1521 NVL (l_batchstep_activity_rec.attribute16, ' ')
1522 OR NVL (p_batchstep_activity_rec.attribute17, ' ') <>
1523 NVL (l_batchstep_activity_rec.attribute17, ' ')
1524 OR NVL (p_batchstep_activity_rec.attribute18, ' ') <>
1525 NVL (l_batchstep_activity_rec.attribute18, ' ')
1526 OR NVL (p_batchstep_activity_rec.attribute19, ' ') <>
1527 NVL (l_batchstep_activity_rec.attribute19, ' ')
1528 OR NVL (p_batchstep_activity_rec.attribute20, ' ') <>
1529 NVL (l_batchstep_activity_rec.attribute20, ' ')
1530 OR NVL (p_batchstep_activity_rec.attribute21, ' ') <>
1531 NVL (l_batchstep_activity_rec.attribute21, ' ')
1532 OR NVL (p_batchstep_activity_rec.attribute22, ' ') <>
1533 NVL (l_batchstep_activity_rec.attribute22, ' ')
1534 OR NVL (p_batchstep_activity_rec.attribute23, ' ') <>
1535 NVL (l_batchstep_activity_rec.attribute23, ' ')
1536 OR NVL (p_batchstep_activity_rec.attribute24, ' ') <>
1537 NVL (l_batchstep_activity_rec.attribute24, ' ')
1538 OR NVL (p_batchstep_activity_rec.attribute25, ' ') <>
1539 NVL (l_batchstep_activity_rec.attribute25, ' ')
1540 OR NVL (p_batchstep_activity_rec.attribute26, ' ') <>
1541 NVL (l_batchstep_activity_rec.attribute26, ' ')
1542 OR NVL (p_batchstep_activity_rec.attribute27, ' ') <>
1543 NVL (l_batchstep_activity_rec.attribute27, ' ')
1544 OR NVL (p_batchstep_activity_rec.attribute28, ' ') <>
1545 NVL (l_batchstep_activity_rec.attribute28, ' ')
1546 OR NVL (p_batchstep_activity_rec.attribute29, ' ') <>
1547 NVL (l_batchstep_activity_rec.attribute29, ' ')
1548 OR NVL (p_batchstep_activity_rec.attribute30, ' ') <>
1549 NVL (l_batchstep_activity_rec.attribute30, ' ')
1550 THEN
1551 l_flex_validate := TRUE;
1552 END IF;
1553
1554 IF l_flex_validate
1555 THEN
1556 l_field_updated := TRUE;
1557 gme_validate_flex_fld_pvt.validate_flex_step_activities (p_step_activities => p_batchstep_activity_rec,
1558 x_step_activities => x_batchstep_activity_rec,
1559 x_return_status => x_return_status
1560 );
1561
1562 IF (NVL (g_debug, -1) = gme_debug.g_log_statement)
1563 THEN
1564 gme_debug.put_line ( 'gme_validate_flex_fld_pvt.validate_flex_step_activities '
1565 || x_return_status
1566 );
1567 END IF;
1568
1569 IF (x_return_status = fnd_api.g_ret_sts_error)
1570 THEN
1571 RAISE fnd_api.g_exc_error;
1572 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
1573 THEN
1574 RAISE fnd_api.g_exc_unexpected_error;
1575 END IF;
1576
1577 -- copy all the validated flex fields
1578 l_batchstep_activity_rec.attribute_category :=
1579 x_batchstep_activity_rec.attribute_category;
1580 l_batchstep_activity_rec.attribute1 :=
1581 x_batchstep_activity_rec.attribute1;
1582 l_batchstep_activity_rec.attribute2 :=
1583 x_batchstep_activity_rec.attribute2;
1584 l_batchstep_activity_rec.attribute3 :=
1585 x_batchstep_activity_rec.attribute3;
1586 l_batchstep_activity_rec.attribute4 :=
1587 x_batchstep_activity_rec.attribute4;
1588 l_batchstep_activity_rec.attribute5 :=
1589 x_batchstep_activity_rec.attribute5;
1590 l_batchstep_activity_rec.attribute6 :=
1591 x_batchstep_activity_rec.attribute6;
1592 l_batchstep_activity_rec.attribute7 :=
1593 x_batchstep_activity_rec.attribute7;
1594 l_batchstep_activity_rec.attribute8 :=
1595 x_batchstep_activity_rec.attribute8;
1596 l_batchstep_activity_rec.attribute9 :=
1597 x_batchstep_activity_rec.attribute9;
1598 l_batchstep_activity_rec.attribute10 :=
1599 x_batchstep_activity_rec.attribute10;
1600 l_batchstep_activity_rec.attribute11 :=
1601 x_batchstep_activity_rec.attribute11;
1602 l_batchstep_activity_rec.attribute12 :=
1603 x_batchstep_activity_rec.attribute12;
1604 l_batchstep_activity_rec.attribute13 :=
1605 x_batchstep_activity_rec.attribute13;
1606 l_batchstep_activity_rec.attribute14 :=
1607 x_batchstep_activity_rec.attribute14;
1608 l_batchstep_activity_rec.attribute15 :=
1609 x_batchstep_activity_rec.attribute15;
1610 l_batchstep_activity_rec.attribute16 :=
1611 x_batchstep_activity_rec.attribute16;
1612 l_batchstep_activity_rec.attribute17 :=
1613 x_batchstep_activity_rec.attribute17;
1614 l_batchstep_activity_rec.attribute18 :=
1615 x_batchstep_activity_rec.attribute18;
1616 l_batchstep_activity_rec.attribute19 :=
1617 x_batchstep_activity_rec.attribute19;
1618 l_batchstep_activity_rec.attribute20 :=
1619 x_batchstep_activity_rec.attribute20;
1620 l_batchstep_activity_rec.attribute21 :=
1621 x_batchstep_activity_rec.attribute21;
1622 l_batchstep_activity_rec.attribute22 :=
1623 x_batchstep_activity_rec.attribute22;
1624 l_batchstep_activity_rec.attribute23 :=
1625 x_batchstep_activity_rec.attribute23;
1626 l_batchstep_activity_rec.attribute24 :=
1627 x_batchstep_activity_rec.attribute24;
1628 l_batchstep_activity_rec.attribute25 :=
1629 x_batchstep_activity_rec.attribute25;
1630 l_batchstep_activity_rec.attribute26 :=
1631 x_batchstep_activity_rec.attribute26;
1632 l_batchstep_activity_rec.attribute27 :=
1633 x_batchstep_activity_rec.attribute27;
1634 l_batchstep_activity_rec.attribute28 :=
1635 x_batchstep_activity_rec.attribute28;
1636 l_batchstep_activity_rec.attribute29 :=
1637 x_batchstep_activity_rec.attribute29;
1638 l_batchstep_activity_rec.attribute30 :=
1639 x_batchstep_activity_rec.attribute30;
1640 END IF;
1641 END IF;
1642
1643 IF NOT (l_field_updated)
1644 THEN
1645 gme_common_pvt.log_message ('GME_NO_CHANGE_TO_UPD');
1646 RAISE fnd_api.g_exc_error;
1647 END IF;
1648
1649 IF (NVL (g_debug, -1) = gme_debug.g_log_statement)
1650 THEN
1651 gme_debug.put_line ('before update of activity');
1652 END IF;
1653
1654 IF l_update_resources
1655 THEN
1656 OPEN cur_get_batch (l_batch_id);
1657 FETCH cur_get_batch INTO l_batch_hdr;
1658 CLOSE cur_get_batch;
1659
1660 -- load temp table so that save_batch routine does resource txn consolidation
1661 IF l_batch_hdr.update_inventory_ind = 'Y'
1662 THEN
1663 gme_trans_engine_util.load_rsrc_trans (p_batch_row => l_batch_hdr,
1664 x_rsc_row_count => l_rsrc_trans_count,
1665 x_return_status => x_return_status
1666 );
1667
1668 IF (NVL (g_debug, 0) = gme_debug.g_log_statement)
1669 THEN
1670 gme_debug.put_line ( g_pkg_name
1671 || '.'
1672 || l_api_name
1673 || ':'
1674 || 'after call to GME_TRANS_ENGINE_UTIL.load_rsrc_trans return status is '
1675 || x_return_status
1676 );
1677 END IF;
1678
1679 IF (x_return_status = fnd_api.g_ret_sts_error)
1680 THEN
1681 RAISE fnd_api.g_exc_error;
1682 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
1683 THEN
1684 RAISE fnd_api.g_exc_unexpected_error;
1685 END IF;
1686 END IF;
1687
1688 gme_update_step_qty_pvt.update_resources (p_batch_hdr_rec => l_batch_hdr,
1689 p_batch_step_rec => l_step_dtl,
1690 p_batchstep_activities_rec => l_batchstep_activity_rec,
1691 x_return_status => x_return_status
1692 );
1693
1694 IF (NVL (g_debug, 0) = gme_debug.g_log_statement)
1695 THEN
1696 gme_debug.put_line ( g_pkg_name
1697 || '.'
1698 || l_api_name
1699 || ':'
1700 || 'after call to GME_UPDATE_STEP_QTY_PVT.update_resources return status is '
1701 || x_return_status
1702 );
1703 END IF;
1704
1705 IF (x_return_status = fnd_api.g_ret_sts_error)
1706 THEN
1707 RAISE fnd_api.g_exc_error;
1708 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
1709 THEN
1710 RAISE fnd_api.g_exc_unexpected_error;
1711 END IF;
1712 END IF;
1713
1714 IF NOT (gme_batch_step_activities_dbl.update_row (l_batchstep_activity_rec
1715 )
1716 )
1717 THEN
1718 RAISE fnd_api.g_exc_error;
1719 END IF;
1720
1721 IF l_seq_dep_ind <> l_batchstep_activity_rec.sequence_dependent_ind
1722 THEN
1723 gme_batch_step_chg_pvt.set_activity_sequence_num (l_batchstep_activity_rec.batch_id
1724 );
1725 END IF;
1726
1727 /*Fetching the values from the db again to get the latest seq_dep_ind.*/
1728 IF NOT (gme_batch_step_activities_dbl.fetch_row (l_batchstep_activity_rec,
1729 l_batchstep_activity_rec
1730 )
1731 )
1732 THEN
1733 RAISE fnd_api.g_exc_error;
1734 END IF;
1735
1736 x_batchstep_activity_rec := l_batchstep_activity_rec;
1737
1738 IF (NVL (g_debug, 0) IN
1739 (gme_debug.g_log_statement, gme_debug.g_log_procedure)
1740 )
1741 THEN
1742 gme_debug.put_line ( g_pkg_name
1743 || '.'
1744 || l_api_name
1745 || ':'
1746 || 'Exiting with '
1747 || x_return_status
1748 );
1749 END IF;
1750 EXCEPTION
1751 WHEN app_exception.record_lock_exception
1752 THEN
1753 gme_common_pvt.log_message ('GME_RECORD_LOCKED',
1754 'TABLE_NAME',
1755 'GME_BATCH_STEP_ACTIVITIES',
1756 'RECORD',
1757 'Activity',
1758 'KEY',
1759 l_activity
1760 );
1761 x_return_status := fnd_api.g_ret_sts_error;
1762 WHEN fnd_api.g_exc_error
1763 THEN
1764 x_return_status := fnd_api.g_ret_sts_error;
1765 WHEN fnd_api.g_exc_unexpected_error
1766 THEN
1767 x_return_status := fnd_api.g_ret_sts_unexp_error;
1768
1769 IF (NVL (g_debug, 0) > 0)
1770 THEN
1771 gme_debug.put_line ( g_pkg_name
1772 || '.'
1773 || l_api_name
1774 || ':'
1775 || 'UNEXPECTED:'
1776 || SQLERRM
1777 );
1778 END IF;
1779 WHEN OTHERS
1780 THEN
1781 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1782 x_return_status := fnd_api.g_ret_sts_unexp_error;
1783
1784 IF (NVL (g_debug, 0) > 0)
1785 THEN
1786 gme_debug.put_line ( g_pkg_name
1787 || '.'
1788 || l_api_name
1789 || ':'
1790 || 'OTHERS:'
1791 || SQLERRM
1792 );
1793 END IF;
1794 END update_batchstep_activity;
1795
1796 /*===========================================================================================
1797 Procedure
1798 delete_batchstep_activity
1799 Description
1800 This procedure is used to delete an activity and all it's children
1801 Parameters
1802 p_batchstep_activity_id OR
1803 (p_org_code,p_batch_no,step_no,activity) to uniquely identify an activity
1804 x_return_status reflects return status of the API
1805
1806 =============================================================================================*/
1807 PROCEDURE delete_batchstep_activity (
1808 p_batchstep_activity_id IN NUMBER := NULL,
1809 p_org_code IN VARCHAR2 := NULL,
1810 p_batch_no IN VARCHAR2 := NULL,
1811 p_batchstep_no IN NUMBER := NULL,
1812 p_activity IN VARCHAR2 := NULL,
1813 x_return_status OUT NOCOPY VARCHAR2
1814 )
1815 IS
1816 l_api_name CONSTANT VARCHAR2 (30) := 'delete_batchstep_activity';
1817 l_step_status NUMBER;
1818 l_batch_status NUMBER;
1819 l_batch_type NUMBER;
1820 l_activity_id NUMBER;
1821 l_batch_id NUMBER;
1822 l_batchstep_id NUMBER;
1823 l_batchstep_activity_rec gme_batch_step_activities%ROWTYPE;
1824 x_batchstep_activity_rec gme_batch_step_activities%ROWTYPE;
1825 l_cnt NUMBER;
1826 l_activity gme_batch_step_activities.activity%TYPE;
1827 l_last_update_date DATE;
1828 l_inv_trans_count NUMBER;
1829 l_rsrc_trans_count NUMBER;
1830 l_batch_hdr gme_batch_header%ROWTYPE;
1831
1832 CURSOR cur_get_batch (v_batch_id NUMBER)
1833 IS
1834 SELECT *
1835 FROM gme_batch_header
1836 WHERE batch_id = v_batch_id;
1837
1838 CURSOR cur_get_step_dtl (v_batchstep_act_id NUMBER)
1839 IS
1840 SELECT step_status, batch_status, a.batch_id, s.batchstep_id,
1841 batchstep_activity_id, activity
1842 FROM gme_batch_steps s,
1843 gme_batch_step_activities a,
1844 gme_batch_header b
1845 WHERE batchstep_activity_id = v_batchstep_act_id
1846 AND b.batch_id = a.batch_id
1847 AND s.batchstep_id = a.batchstep_id;
1848
1849 CURSOR cur_get_activity_count (v_step_id NUMBER)
1850 IS
1851 SELECT COUNT (1)
1852 FROM gme_batch_step_activities
1853 WHERE batchstep_id = v_step_id;
1854
1855 CURSOR cur_check_fpo (v_actv_id NUMBER)
1856 IS
1857 SELECT batch_type
1858 FROM gme_batch_header b, gme_batch_step_activities a
1859 WHERE b.batch_id = a.batch_id AND batchstep_activity_id = v_actv_id;
1860
1861 CURSOR cur_lock_actv (v_activity_id NUMBER)
1862 IS
1863 SELECT last_update_date
1864 FROM gme_batch_step_activities
1865 WHERE batchstep_activity_id = v_activity_id
1866 FOR UPDATE OF last_update_date NOWAIT;
1867 BEGIN
1868 IF (NVL (g_debug, 0) IN
1869 (gme_debug.g_log_statement, gme_debug.g_log_procedure)
1870 )
1871 THEN
1872 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1873 || 'Entering'
1874 );
1875 END IF;
1876
1877 /* Set the return status to success initially */
1878 x_return_status := fnd_api.g_ret_sts_success;
1879
1880 IF (p_batchstep_activity_id IS NULL AND p_activity IS NULL)
1881 THEN
1882 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED',
1883 'FIELD_NAME',
1884 'ACTIVITY'
1885 );
1886 RAISE fnd_api.g_exc_error;
1887 ELSE
1888 IF p_batchstep_activity_id IS NULL
1889 THEN
1890 validate_param (p_org_code => p_org_code,
1891 p_batch_no => p_batch_no,
1892 p_batchstep_no => p_batchstep_no,
1893 p_activity => p_activity,
1894 x_batch_id => l_batch_id,
1895 x_batchstep_id => l_batchstep_id,
1896 x_activity_id => l_activity_id,
1897 x_batch_status => l_batch_status,
1898 x_step_status => l_step_status,
1899 x_return_status => x_return_status
1900 );
1901
1902 IF (NVL (g_debug, 0) = gme_debug.g_log_statement)
1903 THEN
1904 gme_debug.put_line ( g_pkg_name
1905 || '.'
1906 || l_api_name
1907 || ':'
1908 || 'after the call to procedure validate_param '
1909 || x_return_status
1910 );
1911 END IF;
1912
1913 IF (x_return_status = fnd_api.g_ret_sts_error)
1914 THEN
1915 RAISE fnd_api.g_exc_error;
1916 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
1917 THEN
1918 RAISE fnd_api.g_exc_unexpected_error;
1919 END IF;
1920
1921 l_activity := p_activity;
1922 ELSE
1923 l_activity_id := p_batchstep_activity_id;
1924 OPEN cur_get_step_dtl (l_activity_id);
1925 FETCH cur_get_step_dtl INTO l_step_status,
1926 l_batch_status,
1927 l_batch_id,
1928 l_batchstep_id,
1929 l_activity_id,
1930 l_activity;
1931
1932 IF cur_get_step_dtl%NOTFOUND
1933 THEN
1934 CLOSE cur_get_step_dtl;
1935 gme_common_pvt.log_message ('GME_ACTID_NOT_FOUND');
1936 RAISE fnd_api.g_exc_error;
1937 END IF;
1938
1939 CLOSE cur_get_step_dtl;
1940 END IF;
1941 END IF;
1942
1943 -- check FPO
1944 OPEN cur_check_fpo (l_activity_id);
1945 FETCH cur_check_fpo INTO l_batch_type;
1946
1947 IF l_batch_type = 10
1948 THEN
1949 CLOSE cur_check_fpo;
1950 gme_common_pvt.log_message ('GME_FPO_ACTV_NO_DEL');
1951 RAISE fnd_api.g_exc_error;
1952 END IF;
1953
1954 CLOSE cur_check_fpo;
1955 OPEN cur_lock_actv (l_activity_id);
1956 FETCH cur_lock_actv INTO l_last_update_date;
1957 CLOSE cur_lock_actv;
1958
1959 IF (NVL (g_debug, -1) = gme_debug.g_log_statement)
1960 THEN
1961 gme_debug.put_line (' Successfully locked the activity');
1962 END IF;
1963
1964 IF l_batch_status <> 1
1965 THEN
1966 gme_common_pvt.log_message ('PM_WRONG_STATUS');
1967 RAISE fnd_api.g_exc_error;
1968 END IF;
1969
1970 OPEN cur_get_activity_count (l_batchstep_id);
1971 FETCH cur_get_activity_count INTO l_cnt;
1972
1973 IF l_cnt = 1
1974 THEN
1975 gme_common_pvt.log_message ('GME_ACTIVITY_NOT_ATTACH');
1976 RAISE fnd_api.g_exc_error;
1977 END IF;
1978
1979 l_batchstep_activity_rec.batch_id := l_batch_id;
1980 l_batchstep_activity_rec.batchstep_id := l_batchstep_id;
1981 l_batchstep_activity_rec.batchstep_activity_id := l_activity_id;
1982 -- load temp table so that save_batch routine does resource txn consolidation
1983 OPEN cur_get_batch (l_batch_id);
1984 FETCH cur_get_batch INTO l_batch_hdr;
1985 CLOSE cur_get_batch;
1986
1987 IF l_batch_hdr.update_inventory_ind = 'Y'
1988 THEN
1989 gme_trans_engine_util.load_rsrc_trans (p_batch_row => l_batch_hdr,
1990 x_rsc_row_count => l_rsrc_trans_count,
1991 x_return_status => x_return_status
1992 );
1993
1994 IF (NVL (g_debug, 0) = gme_debug.g_log_statement)
1995 THEN
1996 gme_debug.put_line ( g_pkg_name
1997 || '.'
1998 || l_api_name
1999 || ':'
2000 || 'after call to GME_TRANS_ENGINE_UTIL.load_rsrc_trans return status is '
2001 || x_return_status
2002 );
2003 END IF;
2004
2005 IF (x_return_status = fnd_api.g_ret_sts_error)
2006 THEN
2007 RAISE fnd_api.g_exc_error;
2008 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2009 THEN
2010 RAISE fnd_api.g_exc_unexpected_error;
2011 END IF;
2012 END IF;
2013
2014 gme_delete_batch_step_pvt.delete_activity (p_batch_step_activities_rec => l_batchstep_activity_rec,
2015 x_return_status => x_return_status
2016 );
2017
2018 IF (NVL (g_debug, -1) = gme_debug.g_log_statement)
2019 THEN
2020 gme_debug.put_line ( 'after call to gme_delete_batch_step_pvt.delete_activity return status is '
2021 || x_return_status
2022 );
2023 END IF;
2024
2025 IF (x_return_status = fnd_api.g_ret_sts_error)
2026 THEN
2027 RAISE fnd_api.g_exc_error;
2028 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2029 THEN
2030 RAISE fnd_api.g_exc_unexpected_error;
2031 END IF;
2032
2033 IF (NVL (g_debug, 0) IN
2034 (gme_debug.g_log_statement, gme_debug.g_log_procedure)
2035 )
2036 THEN
2037 gme_debug.put_line ( g_pkg_name
2038 || '.'
2039 || l_api_name
2040 || ':'
2041 || 'Exiting with '
2042 || x_return_status
2043 );
2044 END IF;
2045 EXCEPTION
2046 WHEN app_exception.record_lock_exception
2047 THEN
2048 gme_common_pvt.log_message ('GME_RECORD_LOCKED',
2049 'TABLE_NAME',
2050 'GME_BATCH_STEP_ACTIVITIES',
2051 'RECORD',
2052 'Activity',
2053 'KEY',
2054 l_activity
2055 );
2056 x_return_status := fnd_api.g_ret_sts_error;
2057 WHEN fnd_api.g_exc_error
2058 THEN
2059 x_return_status := fnd_api.g_ret_sts_error;
2060 WHEN fnd_api.g_exc_unexpected_error
2061 THEN
2062 x_return_status := fnd_api.g_ret_sts_unexp_error;
2063
2064 IF (NVL (g_debug, 0) > 0)
2065 THEN
2066 gme_debug.put_line ( g_pkg_name
2067 || '.'
2068 || l_api_name
2069 || ':'
2070 || 'UNEXPECTED:'
2071 || SQLERRM
2072 );
2073 END IF;
2074 WHEN OTHERS
2075 THEN
2076 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2077 x_return_status := fnd_api.g_ret_sts_unexp_error;
2078
2079 IF (NVL (g_debug, 0) > 0)
2080 THEN
2081 gme_debug.put_line ( g_pkg_name
2082 || '.'
2083 || l_api_name
2084 || ':'
2085 || 'OTHERS:'
2086 || SQLERRM
2087 );
2088 END IF;
2089 END delete_batchstep_activity;
2090 END gme_batchstep_act_pvt;