[Home] [Help]
PACKAGE BODY: APPS.GMP_APS_WRITER
Source
1 PACKAGE BODY gmp_aps_writer AS
2 /* $Header: GMPAPSWB.pls 120.15.12010000.1 2008/07/30 06:14:50 appldev ship $ */
3
4 /*
5 REM+==========================================================================+
6 REM| PROCEDURE NAME |
7 REM| main_process |
8 REM| DESCRIPTION |
9 REM| This procedure will update all the information related to a batch |
10 REM| 1. This procedure will be called from GMPAPSNW/RS screen. |
11 REM| 2. Materail transactions are calculated by OPM(GME logic) |
12 REM| |
13 REM| PARAMETERS |
14 REM| p_batch_id - Batch ID |
15 REM| p_group_id - Group ID |
16 REM| p_header_id - Header ID |
17 REM| p_start_date - Batch Start Date |
18 REM| p_end_date - Batch Start Date |
19 REM| p_required_completion - Batch Completion Date |
20 REM| p_order_priority - Batch Order Priority |
21 REM| p_organization_id - Batch Organizaiton |
22 REM| p_eff_id - Batch Validity Rule ID |
23 REM| p_action_type - Batch type (1 = New, 3 = Reschedule) |
24 REM| p_creation_date - Batch Creation Date |
25 REM| p_user_id - User ID |
26 REM| p_login_id - Application Login ID |
27 REM| |
28 REM| AUTHOR |
29 REM| R Patangya Created 25-MAY-2003 |
30 REM| HISTORY |
31 REM| Enhancements (APS K -- R12): 10-DEC-2004 (B3710615) |
32 REM| A. Do not plan Resources in place of secondary resources |
33 REM| NOTE |
34 REM| Hard Link, complex routs and MTQ deos not affect the detail feedback. |
35 REM| |
36 REM+==========================================================================+
37 */
38 PROCEDURE main_process(
39 p_batch_id IN NUMBER,
40 p_group_id IN NUMBER,
41 p_header_id IN NUMBER,
42 p_start_date IN DATE,
43 p_end_date IN DATE,
44 p_required_completion IN DATE, -- For R12.0
45 p_order_priority IN NUMBER, -- For R12.0
46 p_organization_id IN NUMBER, -- For R12.0
47 p_eff_id IN NUMBER,
48 p_action_type IN NUMBER,
49 p_creation_date IN DATE,
50 p_user_id IN NUMBER,
51 p_login_id IN NUMBER,
52 return_msg OUT NOCOPY VARCHAR2,
53 return_status OUT NOCOPY NUMBER) IS
54
55 /* Local array definition */
56 TYPE ref_cursor_typ IS REF CURSOR;
57
58 TYPE operations_typ IS RECORD
59 (
60 batchstep_id NUMBER(20),
61 batchstep_no NUMBER(10),
62 oprn_id NUMBER(16),
63 operation_seq_num NUMBER(10),
64 first_unit_start_date DATE,
65 last_unit_completion_date DATE,
66 bo_last_update NUMBER(20),
67 step_status NUMBER(4),
68 aps_oper_count NUMBER(10),
69 gme_oper_count NUMBER(10),
70 max_step_date DATE -- B5473156
71 );
72 TYPE operations_tbl IS TABLE OF operations_typ INDEX by BINARY_INTEGER;
73 operation_tab operations_tbl;
74 oper_cnt INTEGER; /* Number of rows in operations_cursor */
75
76 TYPE oper_rsrc_typ IS RECORD
77 (
78 operation_seq_num NUMBER(10),
79 schedule_seq_num NUMBER(10), -- For R12.0
80 resource_seq_num NUMBER(20),
81 batchstep_id NUMBER(20),
82 organization_id NUMBER(20), -- For R12.0
83 batchstep_activity_id NUMBER(20),
84 batchstep_resource_id NUMBER(20),
85 activity VARCHAR2(25),
86 aps_resource VARCHAR2(20),
87 aps_resource_id NUMBER(20),
88 gme_resource VARCHAR2(20),
89 aps_uom_code VARCHAR2(3),
90 gme_uom_code VARCHAR2(3),
91 assigned_units NUMBER,
92 plan_rsrc_count NUMBER,
93 plan_rsrc_usage NUMBER,
94 sequence_dependent_usage NUMBER, -- For R12.0
95 start_date DATE,
96 completion_date DATE,
97 act_start_date DATE,
98 act_end_date DATE,
99 aps_rsrc_usage NUMBER,
100 aps_charges NUMBER, -- For R12.0
101 scale_type NUMBER, -- For R12.0
102 aps_data_use NUMBER,
103 Aoperation_seq_num NUMBER(20),
104 Aschedule_seq_num NUMBER(20), -- For R12.0
105 bsa_lup NUMBER(20),
106 bsr_lup NUMBER(20),
107 gme_actv_count NUMBER(10),
108 aps_actv_count NUMBER(10),
109 gme_rsrc_count NUMBER(10),
110 aps_rsrc_count NUMBER(10),
111 setup_id NUMBER, -- For R12.0
112 group_sequence_id NUMBER, -- For R12.0
113 group_sequence_number NUMBER, -- For R12.0
114 firm_flag NUMBER -- For R12.0
115 );
116 TYPE oper_rsrc_tbl IS TABLE OF oper_rsrc_typ INDEX by BINARY_INTEGER;
117 or_tab oper_rsrc_tbl;
118 or_cnt INTEGER; /* Number of rows in oper_rsrc cursor */
119
120 TYPE activity_typ IS RECORD
121 (
122 organization_id NUMBER(20), -- For R12.0
123 batchstep_id NUMBER(20),
124 batchstep_activity_id NUMBER(20),
125 start_date DATE,
126 end_date DATE,
127 uom_code VARCHAR2(3),
128 operation_seq_num NUMBER(20),
129 schedule_seq_num NUMBER(20), -- For R12.0
130 resource_seq_num NUMBER(20)
131 );
132 TYPE activity_tbl IS TABLE OF activity_typ INDEX by BINARY_INTEGER;
133 act_tab activity_tbl;
134 act_cnt INTEGER; /* Number of rows in activity cursor */
135
136 TYPE rsrc_tran_typ IS RECORD
137 (
138 batchstep_resource_id NUMBER(20),
139 organization_id NUMBER(20), -- For R12.0
140 operation_seq_num NUMBER(20),
141 schedule_seq_num NUMBER(20), -- For R12.0
142 resource_seq_num NUMBER(20),
143 parent_seq_num NUMBER(20), -- For R12.0
144 aps_resource_id NUMBER(20),
145 aps_resource VARCHAR2(32),
146 aps_uom_code VARCHAR2(3), -- For R12.0
147 assigned_units NUMBER,
148 resource_hour NUMBER,
149 start_date DATE,
150 completion_date DATE,
151 resource_instance_id NUMBER -- For R12.0
152 );
153 TYPE rsrc_tran_tbl IS TABLE OF rsrc_tran_typ INDEX by BINARY_INTEGER;
154 rsrc_tran_tab rsrc_tran_tbl;
155 rtran_cnt INTEGER; /* Number of rows in rsrc_tran cursor */
156
157 cur_operations ref_cursor_typ;
158 cur_oper_rsrc ref_cursor_typ;
159 cur_rsrc_tran ref_cursor_typ;
160 operations_cursor VARCHAR2(15000) ;
161 oper_rsrc_cursor VARCHAR2(30000) ;
162 rsrc_tran_cursor VARCHAR2(15000) ;
163
164 i INTEGER ;
165 j INTEGER ;
166 k INTEGER ;
167 l INTEGER ;
168 end_tran NUMBER ;
169 batch_valid NUMBER ;
170 new_batchstep_resource_id NUMBER ;
171 old_activity_id NUMBER ;
172 vreturn_status NUMBER ;
173 lreturn_status NUMBER ;
174 areturn_status NUMBER ;
175 breturn_status NUMBER ;
176 rreturn_status NUMBER ;
177 sreturn_status NUMBER ;
178 mreturn_status NUMBER ;
179 treturn_status NUMBER ;
180 xreturn_status NUMBER ;
181 t_batch_status NUMBER ;
182 t_struc_size NUMBER ;
183 batch_last_update DATE ;
184 rsrc_cnt NUMBER ;
185 rsrc_usg NUMBER ;
186 t_due_date DATE ; -- For R12.0
187 t_seq_dep_ind NUMBER ; -- For R12.0
188 t_max_step_date DATE ;
189 t_firm_flag NUMBER ; -- B5897392
190
191 BEGIN
192 /* Initialize all the variables */
193 operations_cursor := NULL;
194 oper_rsrc_cursor := NULL;
195 rsrc_tran_cursor := NULL;
196 i := 1;
197 j := 1;
198 k := 1;
199 l := 1;
200 end_tran := 0 ;
201 batch_valid := 0 ;
202 new_batchstep_resource_id := 0 ;
203 batch_last_update := NULL ;
204 rsrc_cnt := 0 ;
205 rsrc_usg := 0 ;
206 t_batch_status := 0 ;
207 t_struc_size := 0 ;
208 old_activity_id := 0;
209 vreturn_status := -1 ;
210 lreturn_status := -1 ;
211 areturn_status := -1 ;
212 breturn_status := -1 ;
213 rreturn_status := -1 ;
214 sreturn_status := -1 ;
215 mreturn_status := -1 ;
216 treturn_status := -1 ;
217 xreturn_status := -1 ;
218 t_due_date := p_required_completion ; -- For R12.0
219 t_firm_flag := 0 ; -- B5897392
220
221 -- find out the last collection
222 orig_last_update_date := p_creation_date;
223
224 -- Initialize message list
225 fnd_msg_pub.initialize;
226
227 /* Set the savepoint before proceeding */
228 SAVEPOINT Before_Main_Program ;
229
230 /* B5897392 get the firm_flag at header level */
231 BEGIN
232 SELECT firm_flag INTO t_firm_flag FROM gmp_aps_output_tbl
233 WHERE batch_id = p_batch_id
234 AND process_id = p_group_id
235 AND header_id = p_header_id ;
236 EXCEPTION
237 WHEN OTHERS THEN
238 fnd_msg_pub.add_exc_msg('gmp_aps_writer','main_process');
239 e_msg := e_msg || ' Main Porgam Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
240 return_status := -131 ;
241 return_msg := e_msg ;
242 END;
243
244 IF p_action_type = 1 THEN
245 -- For new batch validation
246 validate_structure (p_eff_id, p_organization_id, p_group_id, p_header_id,
247 t_struc_size, vreturn_status);
248 -- log_message('After Validate ' ) ;
249 ELSE
250 vreturn_status := 0 ;
251 -- For R12.0
252 t_due_date := NULL ; -- For reschdule batches this must be NULL
253 END IF ;
254
255 IF vreturn_status >= 0 THEN
256 lock_batch_details(p_batch_id, t_batch_status,
257 batch_last_update, lreturn_status) ; -- For R12.0
258 END IF ; /* vreturned_status */
259
260 IF lreturn_status >= 0 THEN
261
262 IF (batch_last_update > orig_last_update_date) AND
263 (p_action_type <> 1) THEN
264 fnd_message.set_name('GMP','GMP_BATCH_HEADER_CHANGED');
265 fnd_msg_pub.add ;
266 e_msg := e_msg || ' Batch header is changed.';
267 breturn_status := -1 ;
268 ELSIF (p_start_date < sysdate AND p_action_type = 1) THEN
269 fnd_message.set_name('GMP','GMP_BATCH_START_DATE_PAST_DUE');
270 fnd_msg_pub.add ;
271 e_msg := e_msg || ' New Batch start date is past due.' ;
272 breturn_status := -1 ;
273 ELSIF (t_batch_status > 2 OR t_batch_status <= 0) AND
274 (p_action_type <> 1) THEN
275 fnd_message.set_name('GMP','GMP_BATCH_STATUS_NOT_PENDING');
276 fnd_msg_pub.add ;
277 e_msg := e_msg || ' Batch is not in pending.' ;
278 breturn_status := -1 ;
279 ELSE
280 update_batch_header(
281 p_batch_id,
282 p_start_date,
283 p_end_date,
284 t_due_date, -- For R12.0
285 p_order_priority, -- For R12.0
286 t_batch_status,
287 t_firm_flag, -- B5897392
288 p_user_id,
289 p_login_id,
290 breturn_status);
291 END IF;
292 -- log_message('After Batch Header');
293
294 END IF ; /* lreturn_status */
295
296 -- In case of New batch APS provide operation/resource row
297 -- if resource usage is ZERO
298 -- But for reschedule batch No rows are provided by OPM and hence APS
299 operations_cursor := ' SELECT '
300 ||' nvl(gbs.batchstep_id,0), '
301 ||' gbs.batchstep_no, '
302 ||' gbs.oprn_id, '
303 ||' gad.operation_seq_num, '
304 ||' gad.first_unit_start_date, '
305 ||' gad.last_unit_completion_date, '
306 ||' gbs.bo_last_update, '
307 ||' gbs.step_status , '
308 ||' gad.oper_count, '
309 ||' gbs.oper_count, '
310 ||' gad.Max_Step '
311 ||' FROM '
312 ||' ( SELECT '
313 ||' b.operation_seq_num, '
314 ||' b.first_unit_start_date, '
315 ||' b.last_unit_completion_date, '
316 ||' COUNT(distinct b.operation_seq_num) OVER (PARTITION BY '
317 ||' b.parent_header_id, b.group_id) oper_count , '
318 ||' max(b.last_unit_completion_date) OVER (PARTITION BY a.batch_id) Max_Step '
319 ||' FROM gmp_aps_output_tbl a,'
320 ||' gmp_aps_output_dtl b '
321 ||' WHERE b.load_type = 3 '
322 ||' AND b.parent_header_id = a.header_id '
323 ||' AND b.group_id = a.process_id '
324 ||' AND b.organization_id = a.organization_id ' -- For R12.0
325 ||' AND a.process_id = :pgpr '
326 ||' AND a.header_id = :phdr '
327 ||' ) gad , '
328 ||' ( SELECT batchstep_id, '
329 ||' batchstep_no, '
330 ||' oprn_id, '
331 ||' DECODE(sign(:lup '
332 ||' - last_update_date), 1,1,0,1,-1,-600) bo_last_update, '
333 ||' step_status , '
334 ||' COUNT(distinct batchstep_no) '
335 ||' OVER (PARTITION BY batch_id) oper_count '
336 ||' FROM gme_batch_steps '
337 -- B5714301, changed the position of operation count
338 -- ||' WHERE batch_id = :pbatch1 '
339 ||' WHERE batchstep_id IN ( select batchstep_id from gme_batch_steps '
340 ||' WHERE batch_id = :pbatch1 ) '
341 ||' AND step_status in (1,2) '
342 ||' AND delete_mark = 0 '
343 -- B5473156, This check is not required as per scenario in the bug
344 -- ||' AND (plan_cmplt_date > plan_start_date OR :patype = 1 )'
345 ||' ) gbs '
346 ||' WHERE gad.operation_seq_num = gbs.batchstep_no (+) '
347 ||' ORDER BY gbs.batchstep_id, gad.operation_seq_num ' ;
348
349 oper_cnt := 1 ;
350 t_max_step_date := NULL ;
351 IF breturn_status >= 0 THEN
352
353 OPEN cur_operations FOR operations_cursor USING p_group_id,
354 p_header_id, orig_last_update_date, p_batch_id ;
355 -- , p_action_type ;
356
357 LOOP
358 FETCH cur_operations INTO operation_tab(oper_cnt);
359 EXIT WHEN cur_operations%NOTFOUND;
360
361 IF p_action_type <> 1 THEN
362
363 IF operation_tab(oper_cnt).batchstep_id = 0 THEN
364 fnd_message.set_name('GMP','GMP_OPER_DELETED');
365 fnd_msg_pub.add ;
366 e_msg := e_msg || ' Operation deleted.';
367 sreturn_status := -1 ;
368 EXIT ;
369 ELSIF (operation_tab(oper_cnt).gme_oper_count <>
370 operation_tab(oper_cnt).aps_oper_count) THEN
371 fnd_message.set_name('GMP','GMP_NUMBER_OF_OPER_MISMATCH');
372 fnd_msg_pub.add ;
373 e_msg := e_msg || ' Number of operation does not match.';
374 sreturn_status := -1 ;
375 EXIT ;
376 ELSIF (operation_tab(oper_cnt).bo_last_update < 0) AND
377 (operation_tab(oper_cnt).step_status = 1) THEN
378 -- If step is in pending and last update changed, We are not
379 -- Updating the batch
380 fnd_message.set_name('GMP','GMP_BATCH_STEP_CHANGED');
381 fnd_msg_pub.add ;
382 e_msg := e_msg || ' Pending Step/Operation is changed.' ;
383 sreturn_status := -1 ;
384 EXIT;
385 ELSIF (operation_tab(oper_cnt).first_unit_start_date < sysdate) AND
386 (operation_tab(oper_cnt).step_status = 1) AND t_batch_status = 2 THEN
387 -- For WIP batch, step is pending and step start date is past due,
388 -- We are not Updating the batch
389 fnd_message.set_name('GMP','GMP_STEP_PAST_DUE');
390 fnd_msg_pub.add ;
391 e_msg := e_msg || ' WIP batch, Pending Step is past due.' ;
392 sreturn_status := -1 ;
393 EXIT;
394 ELSE
395 sreturn_status := 0 ;
396 END IF ;
397
398 END IF ; /* action type */
399
400 t_max_step_date := operation_tab(oper_cnt).max_step_date ;
401
402 IF (operation_tab(oper_cnt).step_status = 1) THEN
403 -- Update the Steps only if it pending
404
405 IF p_action_type = 1 THEN
406 update_batch_steps(
407 p_batch_id,
408 operation_tab(oper_cnt).operation_seq_num,
409 operation_tab(oper_cnt).batchstep_id,
410 operation_tab(oper_cnt).first_unit_start_date,
411 operation_tab(oper_cnt).last_unit_completion_date,
412 operation_tab(oper_cnt).last_unit_completion_date, /* B5454215 */
413 p_user_id,
414 p_login_id,
415 sreturn_status);
416 ELSE
417 update_batch_steps(
418 p_batch_id,
419 operation_tab(oper_cnt).operation_seq_num,
420 operation_tab(oper_cnt).batchstep_id,
421 operation_tab(oper_cnt).first_unit_start_date,
422 operation_tab(oper_cnt).last_unit_completion_date,
423 NULL, /* B5454215 */
424 p_user_id,
425 p_login_id,
426 sreturn_status);
427 END IF;
428
429 IF sreturn_status < 0 THEN
430 fnd_message.set_name('GMP','GMP_STEP_UPDATE_FAILED');
431 fnd_msg_pub.add ;
432 e_msg := e_msg || ' Failed: Update to Step/Operation' ;
433 EXIT;
434 END IF ;
435 ELSE
436 -- No Update to steps (WIP or completed)
437 BEGIN
438 UPDATE gmp_aps_output_dtl
439 SET load_type = (load_type * -1)
440 WHERE operation_seq_num =
441 operation_tab(oper_cnt).operation_seq_num
442 AND wip_entity_id = p_batch_id
443 AND organization_id = p_organization_id -- For R12.0
444 AND group_id = p_group_id
445 AND parent_header_id = p_header_id ;
446 EXCEPTION
447 WHEN OTHERS THEN
448 fnd_msg_pub.add_exc_msg('gmp_aps_writer','main_process');
449 e_msg := e_msg || ' WIP/completed step: '||TO_CHAR(SQLCODE)||': '||SQLERRM ;
450 sreturn_status := -1 ;
451 EXIT;
452 END;
453
454 END IF ; /* step_status check */
455
456 oper_cnt := oper_cnt + 1;
457 END LOOP;
458 CLOSE cur_operations;
459 time_stamp;
460 oper_cnt := oper_cnt - 1;
461 log_message(' Step/Operation size is = ' || to_char(oper_cnt)) ;
462
463 END IF ; /* Breturn_status */
464
465 -- B5473156, Update the Batch Plan Completion Date
466 IF (p_end_date < t_max_step_date) AND (sreturn_status >= 0) THEN
467 breturn_status := -1 ;
468
469 update_batch_header(
470 p_batch_id,
471 p_start_date,
472 t_max_step_date,
473 t_due_date, -- For R12.0
474 p_order_priority, -- For R12.0
475 t_batch_status,
476 t_firm_flag, -- B5897392
477 p_user_id,
478 p_login_id,
479 breturn_status);
480
481 IF breturn_status < 0 THEN
482 fnd_message.set_name('GMP','GMP_STEP_UPDATE_FAILED');
483 fnd_msg_pub.add ;
484 e_msg := e_msg || ' Failed: Update to Batch End Date' ;
485 sreturn_status := -1 ;
486 ELSE
487 log_message(' SET Batch End Date to Maximum of Step End Date ');
488 END IF ;
489 END IF; /* t_max_step_date */
490
491 BEGIN
492
493 UPDATE GMP_APS_OUTPUT_DTL gad
494 SET attribute9 = ( SELECT gbr.batchstep_resource_id
495 FROM GME_BATCH_HEADER gbh,
496 GME_BATCH_STEPS gbs,
497 GME_BATCH_STEP_ACTIVITIES gba,
498 GME_BATCH_STEP_RESOURCES gbr,
499 CR_RSRC_DTL crd
500 WHERE gbh.batch_id = gbs.batch_id
501 AND gbs.batchstep_id = gba.batchstep_id
502 AND gbs.batchstep_id = gbr.batchstep_id
503 AND gba.batchstep_activity_id = gbr.batchstep_activity_id
504 AND gbr.resources = crd.resources
505 AND gbh.organization_id = crd.organization_id -- For R12.0
506 AND gbh.organization_id = gbr.organization_id -- For R12.0
507 AND gbr.prim_rsrc_ind <> 1
508 AND crd.resource_id = gad.resource_id_new
509 AND gbh.batch_id = gad.wip_entity_id
510 AND gbs.batchstep_no = gad.operation_seq_num
511 AND gba.sequence_dependent_ind = gad.schedule_seq_num )
512 WHERE gad.wip_entity_id = p_batch_id
513 AND gad.group_id = p_group_id
514 AND gad.parent_header_id = p_header_id
515 AND gad.organization_id = p_organization_id
516 AND gad.load_type = 1 ;
517
518 -- log_message( p_group_id || '-' || p_header_id || '-'||
519 -- p_batch_id ||' Org ' || p_organization_id );
520
521 EXCEPTION
522 WHEN NO_DATA_FOUND THEN
523 null;
524 WHEN OTHERS THEN
525 fnd_msg_pub.add_exc_msg('gmp_aps_writer','main_process');
526 e_msg := e_msg || ' Attribute9: '||TO_CHAR(SQLCODE)||': '||SQLERRM ;
527 sreturn_status := -1 ;
528 END ;
529
530 -- Update Resources / Activities
531 oper_rsrc_cursor := ' SELECT '
532 ||' final.batchstep_no , '
533 ||' final.schedule_seq_num , ' -- For R12.0
534 ||' aps.resource_seq_num , '
535 ||' final.batchstep_id , '
536 ||' aps.organization_id, '
537 ||' final.batchstep_activity_id , '
538 ||' final.batchstep_resource_id , '
539 ||' final.activity , '
540 ||' aps.resources , '
541 ||' aps.resource_id_new, '
542 ||' final.resources , '
543 ||' aps.uom_code, '
544 ||' final.uom_code, '
545 ||' aps.assigned_units , '
546 ||' final.plan_rsrc_count, '
547 -- Alternate for primary resource For R12.0
548 ||' (final.plan_rsrc_usage * '
549 ||' NVL((SELECT cam.runtime_factor FROM cr_ares_mst cam '
550 ||' WHERE cam.delete_mark = 0 '
551 ||' AND nvl(aps.replacement_group_num,0) <> 0 '
552 ||' AND final.prim_rsrc_ind = 1 '
553 ||' AND final.resources <> aps.resources '
554 ||' AND aps.attribute9 is null '
555 ||' AND final.resources = cam.primary_resource '
556 ||' AND aps.resources = cam.alternate_resource '
557 ||' ),1 ) ), ' -- GME Resource Usage
558 ||' aps.sequence_dependent_usage, ' -- For R12.0
559 ||' aps.start_date, '
560 ||' aps.completion_date, '
561 -- Select Min activity start date For R12.0
562 ||' MIN(aps.act_start_date) OVER (PARTITION BY '
563 ||' final.batchstep_activity_id), '
564 ||' MAX(aps.completion_date) OVER (PARTITION BY '
565 ||' final.batchstep_activity_id), '
566 ||' aps.resource_hour, ' -- APS Resource Usage
567 -- Is Charge exists
568 ||' ( SELECT count(*) from gmp_aps_output_dtl '
569 ||' WHERE wip_entity_id = aps.wip_entity_id '
570 ||' AND parent_header_id = aps.parent_header_id '
571 ||' AND group_id = aps.group_id '
572 ||' AND load_type = 10 '
573 ||' AND operation_seq_num = aps.operation_seq_num '
574 ||' AND schedule_seq_num = aps.schedule_seq_num '
575 ||' AND resource_id_new = aps.resource_id_new ) Charges_present, '
576 ||' final.scale_type, '
577 -- Only Use APS data if alternate resource factor is 1
578 ||' SUM( '
579 ||' DECODE(final.rsrc_count,1,0, '
580 ||' DECODE( '
581 ||' NVL((SELECT cam.runtime_factor FROM cr_ares_mst cam '
582 ||' WHERE cam.delete_mark = 0 '
583 ||' AND nvl(aps.replacement_group_num,0) <> 0 '
584 ||' AND final.prim_rsrc_ind = 1 '
585 ||' AND final.resources <> aps.resources '
586 ||' AND aps.attribute9 is null '
587 ||' AND final.resources = cam.primary_resource '
588 ||' AND aps.resources = cam.alternate_resource ) '
589 ||' ,1),1,final.Batch_rsrc_Avg,9) '
590 ||' ) '
591 ||' ) OVER '
592 ||' (PARTITION BY final.batchstep_activity_id), ' ; -- aps or gme use
593
594 IF p_action_type = 1 THEN -- (New Batch)
595 oper_rsrc_cursor := oper_rsrc_cursor
596 ||' to_number(null), '
597 ||' to_number(null), '
598 ||' to_number(null), '
599 ||' to_number(null), '
600 ||' to_number(null), '
601 ||' to_number(null), '
602 ||' to_number(null), '
603 ||' to_number(null), ' ;
604 ELSE -- (Reschedule Batch)
605 oper_rsrc_cursor := oper_rsrc_cursor
606 ||' nvl(aps.operation_seq_num,0) , '
607 ||' nvl(aps.schedule_seq_num,0) , ' -- For R12.0
608 ||' final.bsa_last_update, '
609 ||' final.bsr_last_update, '
610 ||' final.act_count , '
611 ||' nvl(aps.activity_count,0) , '
612 ||' final.rsrc_count , '
613 ||' nvl(aps.rsrc_count,0), ' ;
614 END IF;
615
616 oper_rsrc_cursor := oper_rsrc_cursor
617 ||' aps.setup_id , ' -- For R12.0
618 ||' aps.group_sequence_id , ' -- For R12.0
619 ||' aps.group_sequence_number, ' -- For R12.0
620 ||' aps.firm_flag ' -- For R12.0
621 ||' FROM '
622 ||' ( '
623 ||' SELECT '
624 ||' gsa.batch_id, '
625 ||' gsa.batchstep_id, '
626 ||' gbs.batchstep_no, '
627 ||' gsa.batchstep_activity_id, '
628 ||' gsa.activity, '
629 ||' gsa.offset_interval, '
630 ||' nvl(gsa.sequence_dependent_ind,0) schedule_seq_num,' -- For R12.0
631 ||' gsr.batchstep_resource_id , '
632 ||' gsr.resources, '
633 ||' gsr.scale_type, '
634 ||' gsr.prim_rsrc_ind, '
635 ||' gsr.plan_rsrc_usage, '
636 ||' gsr.plan_rsrc_count, '
637 ||' DECODE(sign(ceil(gsr.plan_rsrc_usage) - '
638 ||' (AVG(ceil(gsr.plan_rsrc_usage)/gsr.plan_rsrc_count) '
639 ||' OVER (PARTITION BY gsr.batchstep_activity_id)) '
640 ||' ) ,0,0,1,1,-1,1 ) Batch_rsrc_Avg ,'
641 ||' COUNT(distinct gbs.batchstep_no) '
642 ||' OVER (PARTITION BY gsr.batch_id) oper_count, '
643 ||' COUNT(distinct gsa.batchstep_activity_id) '
644 ||' OVER (PARTITION BY gbs.batchstep_no) act_count, '
645 ||' COUNT(gsr.resources) '
646 -- For R12.0
647 ||' OVER (PARTITION BY gbs.batchstep_no, '
648 ||' gsa.batchstep_activity_id, '
649 ||' DECODE(crd.schedule_ind,1,1,2,1,0,1) ) rsrc_count, '
650 ||' gsr.usage_um uom_code,'
651 ||' DECODE(sign(:LUP1 '
652 ||' - gsr.last_update_date), 1,1,0,1,-1,-500) bsr_last_update, '
653 ||' DECODE(sign(:LUP2 '
654 ||' - gsa.last_update_date), 1,1,0,1,-1,-500) bsa_last_update '
655 -- For R12.0
656 ||' FROM gme_batch_step_activities gsa, '
657 ||' gme_batch_steps gbs, '
658 ||' gme_batch_step_resources gsr, '
659 ||' cr_rsrc_dtl crd ' -- For R12.0
660 ||' WHERE '
661 ||' gsr.batch_id = gsa.batch_id '
662 ||' AND gsr.batchstep_activity_id = gsa.batchstep_activity_id '
663 ||' AND crd.resources = gsr.resources ' -- For R12.0
664 ||' AND crd.organization_id = gsr.organization_id ' -- For R12.0
665 ||' AND crd.delete_mark = 0 ' -- For R12.0
666 ||' AND crd.schedule_ind <> 3 ' -- For R12.0
667 ||' AND gsr.plan_rsrc_usage > 0 ' -- For R12.0
668 ||' AND gsa.batch_id = :PBATCH1 '
669 ||' AND gsa.delete_mark = 0 '
670 ||' AND gbs.delete_mark = 0 '
671 ||' AND gbs.batch_id = gsa.batch_id '
672 ||' AND gsa.batchstep_id = gbs.batchstep_id '
673 ||' ) final, '
674 ||' ( '
675 ||' SELECT gad.wip_entity_id, gad.organization_id, ' -- For R12.0
676 ||' gad.parent_header_id , '
677 ||' gad.group_id , '
678 ||' gad.operation_seq_num, '
679 ||' gad.resource_seq_num, '
680 ||' gad.schedule_seq_num, ' -- For R12.0
681 ||' gad.assigned_units , '
682 ||' gad.resource_id_new , '
683 ||' gad.resource_id_old , '
684 ||' gad.attribute9, '
685 ||' crd.resources , '
686 ||' gad.uom_code , '
687 ||' gad.replacement_group_num , '
688 ||' gad.setup_id , ' -- For R12.0
689 ||' gad.group_sequence_id , ' -- For R12.0
690 ||' gad.group_sequence_number , ' -- For R12.0
691 ||' gad.firm_flag , ' -- For R12.0
692 ||' gad.start_date, '
693 ||' gad.completion_date, '
694 -- For R12.0
695 ||' DECODE(seq.start_date, NULL,gad.start_date, '
696 ||' seq.start_Date) act_start_date, '
697 ||' seq.start_date seq_start_date, '
698 ||' seq.sequence_dependent_usage, '
699 ||' nvl(fnd_number.canonical_to_number(gad.attribute1),0) resource_hour, '
700 /*sowsubra B4629277 - changed to_number to fnd_number.canonical_to_number*/
701 ||' MAX(nvl(to_number(gad.attribute1),0)) '
702 ||' OVER (PARTITION BY gad.operation_seq_num, '
703 ||' gad.schedule_seq_num ) aps_max_usage, '
704 ||' COUNT(distinct gad.operation_seq_num) '
705 ||' OVER (PARTITION BY gad.wip_entity_id) oper_count, '
706 ||' COUNT(distinct gad.schedule_seq_num) '
707 ||' OVER (PARTITION BY gad.operation_seq_num) activity_count, '
708 ||' ( COUNT(gad.resource_id_new) '
709 ||' OVER (PARTITION BY gad.operation_seq_num, '
710 ||' gad.schedule_seq_num ) '
711 ||' - crd.delete_mark ) rsrc_count '
712 ||' FROM gmp_aps_output_dtl gad, '
713 ||' gmp_aps_output_tbl gao, '
714 ||' cr_rsrc_dtl crd, '
715 -- Sequence depdendency selection For R12.0
716 ||' ( SELECT operation_seq_num, parent_seq_num, schedule_seq_num, '
717 ||' wip_entity_id, resource_id_new , '
718 ||' TO_NUMBER(attribute1) sequence_dependent_usage, '
719 ||' MIN(start_date) OVER (PARTITION BY '
720 ||' group_id, parent_header_id, schedule_seq_num) start_date '
721 ||' FROM gmp_aps_output_dtl '
722 ||' WHERE parent_header_id = :phdr2 '
723 ||' AND group_id = :PGRP2 '
724 ||' AND wip_entity_id = :PBATCH2 '
725 ||' AND load_type = 1 '
726 ||' AND parent_seq_num IS NOT NULL '
727 ||' ) seq '
728 ||' WHERE gad.parent_header_id = gao.header_id '
729 ||' AND gad.group_id = gao.process_id '
730 ||' AND gao.header_id = :PHDR3 '
731 ||' AND gad.group_id = :PGRP3 '
732 ||' AND gad.wip_entity_id = :PBATCH3 '
733 ||' AND gao.batch_id = gad.wip_entity_id '
734 ||' AND gad.load_type = 1 '
735 ||' AND gad.parent_seq_num IS NULL ' -- For R12.0
736 ||' AND gad.resource_id_new = crd.resource_id '
737 ||' AND crd.organization_id = gao.organization_id '
738 -- For R12.0
739 ||' AND gad.wip_entity_id = seq.wip_entity_id (+) '
740 ||' AND gad.resource_id_new = seq.resource_id_new (+) '
741 ||' AND gad.operation_seq_num = seq.operation_seq_num (+) '
742 ||' AND gad.schedule_seq_num = seq.schedule_seq_num (+) '
743 ||' ) APS '
744 ||' WHERE ' ;
745
746 -- For R12.0
747 IF p_action_type = 1 THEN -- (New Batch)
748 oper_rsrc_cursor := oper_rsrc_cursor
749 ||' final.batch_id = aps.wip_entity_id '
750 ||' AND final.schedule_seq_num = aps.schedule_seq_num '
751 ||' AND final.batchstep_no = aps.operation_seq_num '
752 ||' AND ( '
753 ||' ( final.resources = aps.resources '
754 ||' AND nvl(final.prim_rsrc_ind,0) <> 1 '
755 ||' AND final.batchstep_resource_id = aps.attribute9 ) '
756 ||' OR '
757 ||' ( final.resources = aps.resources '
758 ||' AND nvl(aps.replacement_group_num,0) = 0 '
759 ||' AND nvl(final.prim_rsrc_ind,0) = 1 )'
760 ||' OR '
761 ||' ( final.resources <> aps.resources '
762 ||' AND nvl(final.prim_rsrc_ind,0) = 1 '
763 ||' AND nvl(aps.replacement_group_num,0) <> 0 '
764 ||' AND aps.attribute9 is null ) '
765 ||' ) '
766 ||' ORDER BY '
767 ||' final.batchstep_no, final.schedule_seq_num ' ;
768 ELSE -- (Reschedule Batch)
769 oper_rsrc_cursor := oper_rsrc_cursor
770 ||' final.batchstep_resource_id = aps.resource_seq_num (+) '
771 ||' AND final.schedule_seq_num = aps.schedule_seq_num (+) '
772 ||' AND final.batchstep_no = aps.operation_seq_num (+) '
773 ||' ORDER BY '
774 ||' final.batchstep_no, final.schedule_seq_num ' ;
775 END IF ;
776
777 -- log_message('After Load 1 Cursor ');
778 or_cnt := 1 ;
779 act_cnt := 1 ;
780 IF sreturn_status >= 0 THEN
781 OPEN cur_oper_rsrc FOR oper_rsrc_cursor USING orig_last_update_date,
782 orig_last_update_date, p_batch_id, p_header_id, p_group_id, p_batch_id,
783 p_header_id, p_group_id, p_batch_id ;
784
785 LOOP
786 FETCH cur_oper_rsrc INTO or_tab(or_cnt);
787 EXIT WHEN cur_oper_rsrc%NOTFOUND;
788 IF p_action_type <> 1 THEN
789 -- For reschedule batch
790
791 /* log_message(or_tab(or_cnt).schedule_seq_num || '--' ||
792 or_tab(or_cnt).resource_seq_num || '--' ||
793 or_tab(or_cnt).gme_rsrc_count || '--' ||
794 or_tab(or_cnt).aps_rsrc_count); */
795
796 IF or_tab(or_cnt).Aoperation_seq_num = 0 THEN
797 fnd_message.set_name('GMP','GMP_OPERATION_CHANGED');
798 fnd_msg_pub.add ;
799 e_msg := e_msg || ' Operation Changed. ';
800 batch_valid := -1 ;
801 EXIT ;
802 ELSIF or_tab(or_cnt).Aschedule_seq_num = 0 THEN -- For R12.0
803 fnd_message.set_name('GMP','GMP_ACTIVITY_CHANGED');
804 fnd_msg_pub.add ;
805 e_msg := e_msg || ' Activity Changed.';
806 batch_valid := -1 ;
807 EXIT ;
808 ELSIF or_tab(or_cnt).gme_actv_count <>
809 or_tab(or_cnt).aps_actv_count THEN
810 fnd_message.set_name('GMP','GMP_ACTV_CNT_MISMATCH');
811 fnd_msg_pub.add ;
812 e_msg := e_msg || ' Number of activities does not match.';
813 batch_valid := -1 ;
814 EXIT ;
815
816 ELSIF or_tab(or_cnt).gme_rsrc_count <>
817 or_tab(or_cnt).aps_rsrc_count THEN
818 fnd_message.set_name('GMP','GMP_RSRC_CNT_MISMATCH');
819 fnd_msg_pub.add ;
820 e_msg := e_msg || ' Number of resources does not match.';
821 batch_valid := -1 ;
822 EXIT ;
823 ELSIF or_tab(or_cnt).bsa_lup < 0 THEN
824 fnd_message.set_name('GMP','GMP_ACTIVITY_CHANGED');
825 fnd_msg_pub.add ;
826 e_msg := e_msg || ' Activity updated.';
827 batch_valid := -1 ;
828 EXIT ;
829 ELSIF or_tab(or_cnt).bsr_lup < 0 THEN
830 fnd_message.set_name('GMP','GMP_ACT_RSRC_CHANGED');
831 fnd_msg_pub.add ;
832 e_msg := e_msg || ' Activity Resource changed.';
833 batch_valid := -1 ;
834 EXIT ;
835 ELSE
836 batch_valid := 0 ;
837 END IF; /* Validation check */
838
839 ELSE
840 batch_valid := 0 ;
841 END IF; /* p_action_type check */
842
843 IF or_cnt = 1 THEN
844 -- First row should be written
845 act_cnt := 1 ;
846 act_tab(act_cnt).organization_id := or_tab(or_cnt).organization_id ;
847 act_tab(act_cnt).batchstep_id := or_tab(or_cnt).batchstep_id ;
848 act_tab(act_cnt).start_date := or_tab(or_cnt).act_start_date ;
849 act_tab(act_cnt).end_date := or_tab(or_cnt).act_end_date ;
850 act_tab(act_cnt).uom_code := or_tab(or_cnt).gme_uom_code ;
851 act_tab(act_cnt).operation_seq_num := or_tab(or_cnt).operation_seq_num ;
852 -- For 12.0
853 act_tab(act_cnt).schedule_seq_num := or_tab(or_cnt).schedule_seq_num;
854 act_tab(act_cnt).resource_seq_num := or_tab(or_cnt).resource_seq_num;
855 act_tab(act_cnt).batchstep_activity_id :=
856 or_tab(or_cnt).batchstep_activity_id ;
857
858 old_activity_id := or_tab(or_cnt).batchstep_activity_id;
859 ELSE
860 IF or_tab(or_cnt).batchstep_activity_id <> old_activity_id THEN
861 act_cnt := act_cnt + 1;
862
863 act_tab(act_cnt).organization_id := or_tab(or_cnt).organization_id ;
864 act_tab(act_cnt).batchstep_id := or_tab(or_cnt).batchstep_id ;
865 act_tab(act_cnt).start_date := or_tab(or_cnt).act_start_date ;
866 act_tab(act_cnt).end_date := or_tab(or_cnt).act_end_date ;
867 act_tab(act_cnt).uom_code := or_tab(or_cnt).gme_uom_code ;
868 act_tab(act_cnt).operation_seq_num := or_tab(or_cnt).operation_seq_num ;
869 -- For 12.0
870 act_tab(act_cnt).schedule_seq_num := or_tab(or_cnt).schedule_seq_num;
871 act_tab(act_cnt).resource_seq_num := or_tab(or_cnt).resource_seq_num;
872 act_tab(act_cnt).batchstep_activity_id :=
873 or_tab(or_cnt).batchstep_activity_id ;
874
875 END IF ;
876
877 END IF ;
878 old_activity_id := or_tab(or_cnt).batchstep_activity_id;
879 or_cnt := or_cnt + 1;
880 END LOOP;
881 CLOSE cur_oper_rsrc;
882 or_cnt := or_cnt - 1;
883 time_stamp;
884 log_message('Operation Resource size is = ' || to_char(or_cnt)) ;
885
886 -- Last row only if it is not the first row
887 IF (or_tab(or_cnt).batchstep_activity_id <> old_activity_id)
888 AND (or_cnt > 1) THEN
889
890 act_cnt := act_cnt + 1;
891 act_tab(act_cnt).organization_id := or_tab(or_cnt).organization_id ;
892 act_tab(act_cnt).batchstep_id := or_tab(or_cnt).batchstep_id ;
893 act_tab(act_cnt).start_date := or_tab(or_cnt).act_start_date ;
894 act_tab(act_cnt).end_date := or_tab(or_cnt).act_end_date ;
895 act_tab(act_cnt).uom_code := or_tab(or_cnt).gme_uom_code ;
896 act_tab(act_cnt).operation_seq_num := or_tab(or_cnt).operation_seq_num ;
897 -- For 12.0
898 act_tab(act_cnt).schedule_seq_num := or_tab(or_cnt).schedule_seq_num;
899 act_tab(act_cnt).resource_seq_num := or_tab(or_cnt).resource_seq_num;
900 act_tab(act_cnt).batchstep_activity_id :=
901 or_tab(or_cnt).batchstep_activity_id ;
902
903 END IF ;
904 time_stamp;
905 log_message('Activity size is = ' || to_char(act_cnt)) ;
906
907 IF batch_valid = 0 THEN
908 -- log_message('Batch is Valid ');
909 j := 1 ;
910 FOR j IN 1..or_cnt LOOP
911
912 rsrc_cnt := 0 ;
913 rsrc_usg := 0 ;
914
915 -- In a activity resource usage of multiple resources are different
916 -- then use GME data as APS transaction data is not correct i.e.
917 -- Simultanesous resources with different speed (Use GME data)
918
919 IF or_tab(j).aps_charges <> 0 THEN
920 -- mattt with charges and DS the usage and count can change
921 -- for a resource, Charges exists then use APS data
922 or_tab(j).aps_data_use := 0 ; -- APS Way
923 Rsrc_usg := or_tab(j).aps_rsrc_usage ;
924 rsrc_cnt := or_tab(j).assigned_units ;
925
926 ELSE
927 -- Charges are not present and type is scale by charge
928 IF or_tab(j).scale_type = 2 THEN
929 or_tab(j).scale_type := 1 ; -- Change to proportional
930 ELSE
931 NULL ; -- Do not change scale type
932 END IF;
933
934 IF (or_tab(j).aps_data_use <> 0) AND
935 (or_tab(j).aps_rsrc_usage = or_tab(j).plan_rsrc_usage) AND
936 (or_tab(j).assigned_units = or_tab(j).plan_rsrc_count)
937 THEN
938 or_tab(j).aps_data_use := 0 ;
939 END IF ;
940
941 IF or_tab(j).aps_data_use = 0 THEN
942 -- APS Way
943 Rsrc_usg := or_tab(j).aps_rsrc_usage ;
944 rsrc_cnt := or_tab(j).assigned_units ;
945 ELSE
946 -- GME Way (or_tab(j).aps_data_use <> 0 )
947 Rsrc_usg := or_tab(j).plan_rsrc_usage ;
948 rsrc_cnt := or_tab(j).plan_rsrc_count ;
949 END IF;
950
951 END IF; /* Charges End if */
952
953 IF (or_tab(j).aps_data_use <> 0 ) THEN
954 -- Decision: Use GME data
955 -- log_message('Inside GME Way ');
956
957 update_step_resources(
958 p_batch_id,
959 or_tab(j).organization_id, -- For R12.0,
960 or_tab(j).batchstep_resource_id,
961 rsrc_usg,
962 or_tab(j).sequence_dependent_usage, -- For R12.0
963 or_tab(j).gme_resource,
964 or_tab(j).aps_resource,
965 or_tab(j).start_date,
966 or_tab(j).completion_date,
967 or_tab(j).gme_uom_code ,
968 rsrc_cnt,
969 or_tab(j).aps_data_use,
970 or_tab(j).setup_id , -- For R12.0
971 or_tab(j).group_sequence_id , -- For R12.0
972 or_tab(j).group_sequence_number, -- For R12.0
973 or_tab(j).firm_flag , -- For R12.0
974 or_tab(j).scale_type, -- For R12.0
975 p_user_id,
976 p_login_id,
977 new_batchstep_resource_id,
978 rreturn_status );
979
980 IF rreturn_status < 0 THEN
981 fnd_message.set_name('GMP','GMP_STEP_RESOURCE_FAILED');
982 fnd_msg_pub.add ;
983 e_msg := e_msg || ' Step Resource failed: GME way' ;
984 log_message(e_msg) ;
985 EXIT;
986 END IF ; /* rreturn_status */
987
988 ELSIF (or_tab(j).aps_data_use = 0 ) THEN
989 -- Decision: Use APS data
990 -- log_message('Inside APS Way ');
991
992 update_step_resources(
993 p_batch_id,
994 or_tab(j).organization_id, -- For R12.0,
995 or_tab(j).batchstep_resource_id,
996 rsrc_usg,
997 or_tab(j).sequence_dependent_usage, -- For R12.0
998 or_tab(j).gme_resource,
999 or_tab(j).aps_resource,
1000 or_tab(j).start_date,
1001 or_tab(j).completion_date,
1002 or_tab(j).gme_uom_code ,
1003 rsrc_cnt,
1004 or_tab(j).aps_data_use,
1005 or_tab(j).setup_id , -- For R12.0
1006 or_tab(j).group_sequence_id , -- For R12.0
1007 or_tab(j).group_sequence_number, -- For R12.0
1008 or_tab(j).firm_flag , -- For R12.0
1009 or_tab(j).scale_type, -- For R12.0
1010 p_user_id,
1011 p_login_id,
1012 new_batchstep_resource_id,
1013 rreturn_status );
1014
1015 IF rreturn_status < 0 THEN
1016 fnd_message.set_name('GMP','GMP_STEP_RESOURCE_FAILED');
1017 fnd_msg_pub.add ;
1018 e_msg := e_msg || ' Step Resource failed:APS Way' ;
1019 log_message(e_msg) ;
1020 EXIT;
1021 ELSE
1022
1023 BEGIN
1024 IF p_action_type = 1 THEN
1025 -- New batch
1026
1027 UPDATE GMP_APS_OUTPUT_DTL
1028 SET attribute9 = new_batchstep_resource_id,
1029 attribute10 = or_tab(j).gme_uom_code
1030 WHERE load_type IN (4,9) -- For R12.0
1031 AND resource_id_new = or_tab(j).aps_resource_id
1032 AND group_id = p_group_id
1033 AND parent_header_id = p_header_id
1034 AND operation_seq_num = or_tab(j).operation_seq_num
1035 -- For R12.0
1036 AND schedule_seq_num = or_tab(j).schedule_seq_num
1037 -- PS Issue B6045398, PS engine is sending resource_seq_num NULL for laod_type = 1
1038 AND ( ( resource_seq_num = NVL(or_tab(j).resource_seq_num,resource_seq_num)
1039 AND parent_seq_num IS NULL )
1040 OR
1041 (
1042 parent_seq_num = NVL(or_tab(j).resource_seq_num,parent_seq_num)
1043 AND parent_seq_num IS NOT NULL )
1044 ) ;
1045
1046 ELSE
1047 -- Reschedule batch (Update the count as APS does not provide)
1048 UPDATE GMP_APS_OUTPUT_DTL
1049 SET attribute9 = new_batchstep_resource_id,
1050 attribute10 = or_tab(j).gme_uom_code,
1051 assigned_units = rsrc_cnt
1052 WHERE load_type IN (4,9) -- For R12.0
1053 AND resource_id_new = or_tab(j).aps_resource_id
1054 AND group_id = p_group_id
1055 AND parent_header_id = p_header_id
1056 AND operation_seq_num = or_tab(j).operation_seq_num
1057 -- For R12.0
1058 AND schedule_seq_num = or_tab(j).schedule_seq_num
1059 AND ( ( resource_seq_num = or_tab(j).resource_seq_num
1060 AND parent_seq_num IS NULL )
1061 OR
1062 (
1063 parent_seq_num = or_tab(j).resource_seq_num
1064 AND parent_seq_num IS NOT NULL )
1065 ) ;
1066
1067 END IF; /* action type */
1068
1069 EXCEPTION
1070 WHEN OTHERS THEN
1071 fnd_msg_pub.add_exc_msg('gmp_aps_writer','main_process');
1072 e_msg := e_msg || ' Transactions: '||TO_CHAR(SQLCODE)||': '||SQLERRM;
1073 rreturn_status := -13 ;
1074 EXIT;
1075 END;
1076 END IF ; /* rreturn_status */
1077
1078 END IF ; /* aps_data_use */
1079
1080 END LOOP; /* Operation Resource Loop */
1081 END IF ; /* Batch Valid */
1082
1083 END IF ; /* Sreturn_status */
1084
1085 IF rreturn_status >= 0 THEN
1086 -- Update activities
1087 FOR i IN 1..act_cnt LOOP
1088
1089 update_batch_activities(
1090 p_batch_id,
1091 act_tab(i).organization_id, -- For R12.0
1092 act_tab(i).batchstep_id,
1093 act_tab(i).batchstep_activity_id,
1094 act_tab(i).start_date,
1095 act_tab(i).end_date,
1096 act_tab(i).uom_code,
1097 p_user_id,
1098 p_login_id,
1099 areturn_status);
1100
1101 IF areturn_status < 0 THEN
1102 fnd_message.set_name('GMP','GMP_ACTIVITY_UPDATE_FAIL');
1103 fnd_msg_pub.add ;
1104 e_msg := e_msg || ' Update to Activities is failed' ;
1105 log_message(e_msg) ;
1106 EXIT;
1107 END IF ;
1108
1109 END LOOP ;
1110
1111 END IF; /* End if for rreturn_status */
1112
1113 -- NOTE:
1114 -- GMPOUTIB.pls will populate column resource_instance_number with
1115 -- WIP resource_instance_id then update GMP_APS_OUTPUT_DTL table
1116 -- resource_instance_id column
1117 -- with actual resource_instance_id from GMP_RESOURCE_INSTACNES table
1118
1119 -- Update the resource level transactions if instance level transactions
1120 -- are present
1121 BEGIN
1122
1123 UPDATE gmp_aps_output_dtl
1124 SET load_type = (load_type * -1)
1125 WHERE load_type = 4
1126 AND group_id = p_group_id
1127 AND parent_header_id = p_header_id
1128 -- PS Issue, B6051303 Alternate resource Issue
1129 AND (operation_Seq_num,nvl(parent_seq_num,resource_seq_num),
1130 schedule_seq_num) IN
1131 ( SELECT b.operation_Seq_num,
1132 nvl(b.parent_seq_num,b.resource_seq_num),
1133 b.schedule_seq_num
1134 FROM gmp_aps_output_dtl b
1135 WHERE b.group_id = p_group_id
1136 AND b.parent_header_id = p_header_id
1137 AND b.load_type = 9 ) ;
1138 EXCEPTION
1139 WHEN NO_DATA_FOUND THEN
1140 NULL ;
1141 WHEN OTHERS THEN
1142 fnd_msg_pub.add_exc_msg('gmp_aps_writer','main_process');
1143 e_msg := e_msg || ' WIP/completed step: '||TO_CHAR(SQLCODE)||': '||SQLERRM ;
1144 areturn_status := -1 ;
1145 END;
1146
1147 -- Select resource transactions before any update
1148 rsrc_tran_cursor := ' SELECT '
1149 ||' gad.attribute9, ' -- Batchstep resource ID
1150 ||' gao.organization_id, ' -- For R12.0
1151 ||' gad.operation_seq_num, '
1152 ||' gad.schedule_seq_num, ' -- For R12.0
1153 ||' gad.resource_seq_num, '
1154 ||' gad.parent_seq_num, ' -- For R12.0
1155 ||' gad.resource_id_new, '
1156 ||' crd.resources, '
1157 ||' gad.attribute10, ' -- uom_code
1158 ||' gad.assigned_units, '
1159 ||' nvl(fnd_number.canonical_to_number(gad.attribute1),0) resource_hour, '
1160 -- sowsubra B4629277 changed to_number to fnd_number.canonical_to_number
1161 ||' gad.start_date, '
1162 ||' gad.completion_date, '
1163 ||' gad.resource_instance_id ' -- For R12.0
1164 ||' FROM gmp_aps_output_dtl gad, '
1165 ||' gmp_aps_output_tbl gao, '
1166 ||' cr_rsrc_dtl crd '
1167 ||' WHERE '
1168 ||' gad.load_type in (4,9) '
1169 ||' AND gad.parent_header_id = gao.header_id '
1170 ||' AND gad.group_id = gao.process_id '
1171 ||' AND gad.wip_entity_id = gao.batch_id '
1172 ||' AND gao.process_id = :pgpr '
1173 ||' AND gao.header_id = :phdr '
1174 ||' AND gad.resource_id_new = crd.resource_id '
1175 ||' AND crd.organization_id = gao.organization_id ' -- For R12.0
1176 ||' AND nvl(to_number(gad.attribute9),0) > 0 ' -- batchstep_resource_id
1177 ||' AND nvl(fnd_number.canonical_to_number(gad.attribute1),0) > 0 '
1178 -- sowsubra B4629277 changed to_number to fnd_number.canonical_to_number
1179 ||' AND gao.batch_id = :pbatch1 ' ;
1180
1181 IF (areturn_status >= 0 ) THEN
1182 rtran_cnt := 1 ;
1183 -- Changes for Resource Instances
1184 OPEN cur_rsrc_tran FOR rsrc_tran_cursor USING
1185 p_group_id, p_header_id, p_batch_id ;
1186 LOOP
1187 FETCH cur_rsrc_tran INTO rsrc_tran_tab(rtran_cnt);
1188 EXIT WHEN cur_rsrc_tran%NOTFOUND;
1189 l := 1 ;
1190 end_tran := rsrc_tran_tab(rtran_cnt).assigned_units ;
1191
1192 FOR l in 1..end_tran
1193 LOOP /* Expansion Loop starts */
1194
1195 -- For R12.0
1196 log_message(rsrc_tran_tab(rtran_cnt).parent_seq_num || '-' ||
1197 rsrc_tran_tab(rtran_cnt).resource_instance_id ) ;
1198 IF (rsrc_tran_tab(rtran_cnt).parent_seq_num IS NOT NULL) AND
1199 (rsrc_tran_tab(rtran_cnt).resource_instance_id IS NOT NULL) THEN
1200 t_seq_dep_ind := 1 ;
1201 ELSE
1202 t_seq_dep_ind := 0 ;
1203 END IF ;
1204
1205 update_resource_transactions(
1206 p_batch_id,
1207 rsrc_tran_tab(rtran_cnt).batchstep_resource_id,
1208 rsrc_tran_tab(rtran_cnt).organization_id, -- For R12.0
1209 (rsrc_tran_tab(rtran_cnt).resource_hour/end_tran),
1210 rsrc_tran_tab(rtran_cnt).aps_resource,
1211 rsrc_tran_tab(rtran_cnt).start_date,
1212 rsrc_tran_tab(rtran_cnt).completion_date,
1213 rsrc_tran_tab(rtran_cnt).aps_uom_code ,
1214 rsrc_tran_tab(rtran_cnt).resource_instance_id , -- For R12.0
1215 t_seq_dep_ind, -- For R12.0
1216 p_user_id,
1217 p_login_id,
1218 treturn_status );
1219
1220 IF treturn_status < 0 THEN
1221 EXIT;
1222 END IF ;
1223
1224 END LOOP; /* Expansion Loop Ends */
1225
1226 rtran_cnt := rtran_cnt + 1;
1227
1228 IF treturn_status < 0 THEN
1229 fnd_message.set_name('GMP','GMP_RSRC_TRANS_UPDATE_FAIL');
1230 fnd_msg_pub.add ;
1231 e_msg := e_msg || ' Update to Resource Transaction is failed' ;
1232 EXIT;
1233 END IF ;
1234
1235 END LOOP;
1236 CLOSE cur_rsrc_tran;
1237 time_stamp;
1238 rtran_cnt := rtran_cnt - 1;
1239 log_message(' Resource Transaction size is = ' || to_char(rtran_cnt)) ;
1240 IF rtran_cnt = 0 THEN
1241 treturn_status := 0 ;
1242 END IF;
1243
1244 IF (treturn_status >= 0) THEN
1245 update_materails( p_batch_id,
1246 p_organization_id,
1247 mreturn_status) ;
1248
1249 IF mreturn_status < 0 THEN
1250 fnd_message.set_name('GMP','GMP_MATL_UPDATE_FAIL');
1251 fnd_msg_pub.add ;
1252 e_msg := e_msg || ' Materail Update is failed' ;
1253 END IF ;
1254 END IF ;
1255
1256 -- Charge information
1257 Insert_charges( p_batch_id,
1258 p_group_id ,
1259 p_header_id,
1260 xreturn_status) ;
1261
1262 IF xreturn_status < 0 THEN
1263 fnd_message.set_name('GMP','GMP_RSRC_CHRGS_UPDATE_FAIL');
1264 fnd_msg_pub.add ;
1265 e_msg := e_msg || ' Charges Insert is failed' ;
1266 END IF ;
1267
1268 END IF; /* end if for areturn_status */
1269
1270 IF vreturn_status < 0 THEN
1271 return_status := vreturn_status ;
1272 ROLLBACK TO SAVEPOINT Before_Main_Program ;
1273 ELSIF lreturn_status < 0 THEN
1274 return_status := lreturn_status ;
1275 ROLLBACK TO SAVEPOINT Before_Main_Program ;
1276 ELSIF breturn_status < 0 THEN
1277 return_status := breturn_status ;
1278 ROLLBACK TO SAVEPOINT Before_Main_Program ;
1279 ELSIF sreturn_status < 0 THEN
1280 return_status := sreturn_status ;
1281 ROLLBACK TO SAVEPOINT Before_Main_Program ;
1282 ELSIF rreturn_status < 0 THEN
1283 return_status := rreturn_status ;
1284 ROLLBACK TO SAVEPOINT Before_Main_Program ;
1285 ELSIF areturn_status < 0 THEN
1286 return_status := areturn_status ;
1287 ROLLBACK TO SAVEPOINT Before_Main_Program ;
1288 ELSIF treturn_status < 0 THEN
1289 return_status := treturn_status ;
1290 log_message('TR = ' || treturn_status);
1291 ROLLBACK TO SAVEPOINT Before_Main_Program ;
1292 ELSIF xreturn_status < 0 THEN
1293 return_status := xreturn_status ;
1294 log_message('XR = ' || xreturn_status);
1295 ROLLBACK TO SAVEPOINT Before_Main_Program ;
1296 ELSIF mreturn_status < 0 THEN -- For R12.0
1297 return_status := mreturn_status ;
1298 log_message('MR = ' || mreturn_status);
1299 ROLLBACK TO SAVEPOINT Before_Main_Program ;
1300 ELSE
1301 IF p_action_type <> 1 THEN
1302 -- Reschedule form is not calling GME routine and hence once
1303 -- the batch is updated successfully, then processed_ind = 0
1304 UPDATE gmp_aps_output_tbl
1305 SET processed_ind = 0
1306 WHERE batch_id = p_batch_id
1307 AND process_id = p_group_id
1308 AND header_id = p_header_id ;
1309 END IF;
1310
1311 return_status := 0 ;
1312 COMMIT ;
1313 END IF;
1314 return_msg := e_msg ;
1315
1316 EXCEPTION
1317 WHEN OTHERS THEN
1318 fnd_msg_pub.add_exc_msg('gmp_aps_writer','main_process');
1319 e_msg := e_msg || ' Main Porgam Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
1320 return_status := -99 ;
1321 return_msg := e_msg ;
1322 END main_process ;
1323
1324 /*
1325 REM+=========================================================================+
1326 REM| PROCEDURE NAME |
1327 REM| update_step_resources |
1328 REM| DESCRIPTION |
1329 REM| This procedure will update the step resources plan start and end date|
1330 REM| HISTORY |
1331 REM| Rajesh Patangya |
1332 REM+=========================================================================+
1333 */
1334 PROCEDURE update_step_resources(
1335 pbatch_id IN NUMBER,
1336 porganization_id IN NUMBER, -- For R12.0
1337 pstep_resource_id IN NUMBER,
1338 prsrc_usage IN NUMBER,
1339 psequence_dep_usage IN NUMBER, -- For R12.0
1340 pgme_resource IN VARCHAR2,
1341 paps_resource IN VARCHAR2,
1342 pstart_date IN DATE,
1343 pend_date IN DATE,
1344 pbs_usage_uom IN VARCHAR2, -- Gme UOM code
1345 passigned_unit IN NUMBER,
1346 paps_data_use IN NUMBER,
1347 psetup_id IN NUMBER, -- For R12.0
1348 pgroup_sequence_id IN NUMBER, -- For R12.0
1349 pgroup_sequence_number IN NUMBER, -- For R12.0
1350 pfirm_flag IN NUMBER, -- For R12.0
1351 pscale_type IN NUMBER, -- For R12.0
1352 puser_id IN NUMBER,
1353 plogin_id IN NUMBER,
1354 pnew_act_res OUT NOCOPY NUMBER,
1355 return_status OUT NOCOPY NUMBER )
1356 IS
1357
1358 v_batch_id NUMBER ;
1359 v_organization_id NUMBER ; -- For R12.0
1360 v_step_resource_id NUMBER ;
1361 v_resource_id NUMBER ;
1362 v_end_date DATE ;
1363 temp_date DATE ;
1364
1365 v_o_resources VARCHAR2(16) ;
1366 v_n_resources VARCHAR2(16) ;
1367 v_uom_code VARCHAR2(3) ;
1368 v_rsrc_usage NUMBER ;
1369 v_assigned_unit NUMBER ;
1370
1371 v_in_step_res_row gme_batch_step_resources%ROWTYPE; /* Added for NOCOPY */
1372 v_step_res_row gme_batch_step_resources%ROWTYPE;
1373
1374 l INTEGER ;
1375 tran_status NUMBER ;
1376
1377 BEGIN
1378
1379 v_batch_id := 0;
1380 l := 1;
1381 tran_status := -1 ;
1382 return_status := 0;
1383
1384 gme_common_pvt.set_timestamp ;
1385 gme_common_pvt.g_timestamp := sysdate ;
1386 gme_common_pvt.g_user_ident := puser_id;
1387 gme_common_pvt.g_login_id := plogin_id;
1388 v_batch_id := pbatch_id;
1389 v_organization_id := porganization_id ; -- For R12.0
1390 v_rsrc_usage := prsrc_usage;
1391 v_assigned_unit := passigned_unit;
1392 v_step_resource_id := pstep_resource_id ;
1393 v_o_resources := pgme_resource;
1394 v_n_resources := paps_resource;
1395 v_uom_code := pbs_usage_uom ;
1396 v_end_date := pend_date ;
1397 temp_date := NULL;
1398 v_in_step_res_row.batchstep_resource_id := -1;
1399
1400 -- Delete resource transactions for current batchstep resource */
1401 DELETE gme_resource_txns
1402 WHERE doc_id = v_batch_id
1403 AND resource_usage > 0
1404 AND line_id= v_step_resource_id ;
1405
1406 IF v_o_resources = v_n_resources THEN
1407 pnew_act_res := v_step_resource_id ;
1408
1409 IF paps_data_use = 0 THEN
1410 -- APS way
1411 UPDATE gme_batch_step_resources
1412 SET
1413 plan_start_date = pstart_date,
1414 plan_cmplt_date = pend_date,
1415 plan_rsrc_usage = prsrc_usage,
1416 plan_rsrc_count = v_assigned_unit,
1417 sequence_dependent_id = psetup_id , -- For R12.0
1418 sequence_dependent_usage = psequence_dep_usage, -- For R12.0
1419 group_sequence_id = pgroup_sequence_id , -- For R12.0
1420 group_sequence_number = pgroup_sequence_number ,-- For R12.0
1421 firm_type = pfirm_flag , -- For R12.0
1422 scale_type = pscale_type , -- For R12.0
1423 last_update_date = SYSDATE,
1424 last_updated_by = puser_id
1425 WHERE
1426 batchstep_resource_id = v_step_resource_id;
1427
1428 ELSE
1429 -- GME way
1430 UPDATE gme_batch_step_resources
1431 SET
1432 plan_start_date = pstart_date,
1433 plan_cmplt_date = pend_date,
1434 sequence_dependent_id = psetup_id , -- For R12.0
1435 sequence_dependent_usage = psequence_dep_usage, -- For R12.0
1436 group_sequence_id = pgroup_sequence_id , -- For R12.0
1437 group_sequence_number = pgroup_sequence_number ,-- For R12.0
1438 firm_type = pfirm_flag , -- For R12.0
1439 scale_type = pscale_type , -- For R12.0
1440 last_update_date = SYSDATE,
1441 last_updated_by = puser_id
1442 WHERE
1443 batchstep_resource_id = v_step_resource_id;
1444
1445 END IF ; /* APS data Use */
1446
1447 IF SQL%NOTFOUND THEN
1448 return_status := -1;
1449 END IF;
1450 -- Transaction will be modified later
1451 ELSE
1452 -- Alternate resource
1453 v_in_step_res_row.batchstep_resource_id := v_step_resource_id;
1454
1455 IF NOT GME_BATCH_STEP_RESOURCES_DBL.fetch_row(v_in_step_res_row,
1456 v_step_res_row) THEN
1457 return_status := -2;
1458 ELSE
1459 v_step_res_row.plan_start_date := pstart_date;
1460 v_step_res_row.plan_cmplt_date := pend_date;
1461 v_step_res_row.resources := v_n_resources;
1462 -- For R12.0
1463 v_in_step_res_row.organization_id := porganization_id;
1464 v_step_res_row.sequence_dependent_id := psetup_id ;
1465 v_step_res_row.sequence_dependent_usage := psequence_dep_usage ;
1466 v_step_res_row.plan_rsrc_usage := prsrc_usage;
1467 v_step_res_row.group_sequence_id := pgroup_sequence_id;
1468 v_step_res_row.group_sequence_number := pgroup_sequence_number;
1469 v_step_res_row.firm_type := pfirm_flag ;
1470 v_step_res_row.scale_type := pscale_type ;
1471
1472 IF paps_data_use = 0 THEN
1473 -- APS count may be different from GME
1474 v_step_res_row.plan_rsrc_count := v_assigned_unit;
1475 END IF ;
1476
1477 temp_date := pstart_date + ((v_rsrc_usage/24) / v_assigned_unit ) ;
1478
1479 IF (temp_date > v_end_date) THEN
1480 return_status := -3;
1481 fnd_message.set_name('GMP','GMP_SEND_GREATER_START');
1482 fnd_msg_pub.add ;
1483 e_msg := e_msg || ' Step End date greater than start date.';
1484
1485 END IF;
1486
1487 DELETE gme_batch_step_resources
1488 WHERE batchstep_resource_id = v_step_resource_id;
1489
1490 IF SQL%NOTFOUND THEN
1491 return_status := -4;
1492 ELSE
1493 IF NOT GME_BATCH_STEP_RESOURCES_DBL.insert_row
1494 (v_step_res_row, v_in_step_res_row) THEN
1495 return_status := -5;
1496 ELSE
1497 pnew_act_res := v_in_step_res_row.batchstep_resource_id;
1498 END IF;
1499
1500 END IF; /* Delete NOTFOUND */
1501
1502 END IF; /* fetch_row */
1503
1504 END IF; /* v_o_resources */
1505
1506 IF (v_rsrc_usage > 0 AND return_status = 0 AND paps_data_use <> 0 ) THEN
1507 -- GME way, only if usage is greater than Zero
1508 l := 1 ;
1509 v_rsrc_usage := (v_rsrc_usage / v_assigned_unit) ;
1510 v_end_date := pstart_date + (v_rsrc_usage/24) ;
1511
1512 FOR l in 1..v_assigned_unit
1513 LOOP /* Expansion Loop starts */
1514 update_resource_transactions(
1515 v_batch_id ,
1516 pnew_act_res ,
1517 v_organization_id, -- For R12.0
1518 v_rsrc_usage ,
1519 v_n_resources , -- alternate or auxillary
1520 pstart_date ,
1521 v_end_date ,
1522 v_uom_code , -- Changed from 3 char to 4 character
1523 NULL , -- Resource Instance Id
1524 0 , -- Sequence Depdent Indicator
1525 puser_id ,
1526 plogin_id ,
1527 tran_status );
1528
1529 IF tran_status < 0 THEN
1530 fnd_message.set_name('GMP','GMP_RSRC_TRANS_UPDATE_FAIL');
1531 fnd_msg_pub.add ;
1532 e_msg := e_msg || ' Failed: Resource Transaction' ;
1533 return_status := -6 ;
1534 EXIT;
1535 ELSE
1536 return_status := 0 ;
1537 END IF ;
1538
1539 END LOOP ; /* Expansion Loop ends */
1540 END IF ; /* return status */
1541
1542 EXCEPTION
1543 WHEN OTHERS THEN
1544 return_status := -96;
1545 fnd_msg_pub.add_exc_msg('gmp_aps_writer','update_step_resources');
1546 e_msg := e_msg || ' Update Step Resources Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
1547 END update_step_resources;
1548
1549 /*
1550 REM+=========================================================================+
1551 REM| PROCEDURE NAME |
1552 REM| update_resource_transactions |
1553 REM| DESCRIPTION |
1554 REM| This procedure will update the resource instance start and end date |
1555 REM| HISTORY |
1556 REM| Rajesh Patangya |
1557 REM+=========================================================================+
1558 */
1559 PROCEDURE update_resource_transactions(
1560 pbatch_id IN NUMBER,
1561 pbstep_rsrc_id IN NUMBER,
1562 porganization_id IN NUMBER, -- For R12.0
1563 prsrc_hour IN NUMBER,
1564 paps_resource IN VARCHAR2,
1565 pstart_date IN DATE,
1566 pend_date IN DATE,
1567 puom_code IN VARCHAR2,
1568 prsrc_inst_id IN NUMBER, -- For R12.0 resource_instance_id
1569 pseq_dep_ind IN NUMBER, -- For R12.0 sequence dependent
1570 puser_id IN NUMBER,
1571 plogin_id IN NUMBER,
1572 return_status OUT NOCOPY NUMBER ) IS
1573
1574 v_in_trans_row gme_resource_txns%ROWTYPE; /* Added for NOCOPY */
1575 v_trans_row gme_resource_txns%ROWTYPE;
1576 l_doc_type VARCHAR2(5);
1577 BEGIN
1578 return_status := 0;
1579 /* B5470072, Resource Transaction should have doc_type of FPO */
1580 BEGIN
1581 SELECT DECODE(nvl(batch_type,0),0,'PROD',10,'FPO') into l_doc_type
1582 FROM gme_batch_header where batch_id = pbatch_id ;
1583 EXCEPTION
1584 WHEN NO_DATA_FOUND THEN
1585 l_doc_type := 'PROD' ;
1586 END ;
1587
1588 v_in_trans_row.line_id := pbstep_rsrc_id;
1589 v_in_trans_row.organization_id := porganization_id ; -- For R12.0
1590 v_in_trans_row.doc_type := l_doc_type ;
1591 v_in_trans_row.doc_id := pbatch_id;
1592 v_in_trans_row.line_type := 0;
1593 v_in_trans_row.resources := paps_resource;
1594 -- Resource_hour populated in ATTRIBUTE1 column of WIP detail
1595 -- by APS is used as resource usage for transaction
1596 v_in_trans_row.resource_usage := prsrc_hour;
1597 v_in_trans_row.TRANS_QTY_UM := puom_code; -- For R12.0 (4 character)
1598 v_in_trans_row.trans_date := pstart_date;
1599 v_in_trans_row.completed_ind := 0;
1600 v_in_trans_row.posted_ind := 0;
1601 v_in_trans_row.start_date := pstart_date;
1602 v_in_trans_row.end_date := pend_date;
1603 v_in_trans_row.creation_date := SYSDATE;
1604 v_in_trans_row.last_update_date := SYSDATE;
1605 v_in_trans_row.created_by := puser_id;
1606 v_in_trans_row.last_updated_by := puser_id;
1607 v_in_trans_row.last_update_login := plogin_id;
1608 v_in_trans_row.instance_id := prsrc_inst_id; -- For R12.0
1609 v_in_trans_row.delete_mark := 0;
1610 v_in_trans_row.sequence_dependent_ind := pseq_dep_ind ; -- For R12.0
1611 v_in_trans_row.overrided_protected_ind := 'N';
1612 gme_common_pvt.set_timestamp ;
1613 gme_common_pvt.g_timestamp := sysdate ;
1614 gme_common_pvt.g_user_ident := puser_id;
1615 gme_common_pvt.g_login_id := plogin_id;
1616
1617 -- This is not going to change For R12.0
1618 IF NOT gme_resource_txns_dbl.insert_row
1619 (v_in_trans_row, v_trans_row) THEN
1620 return_status := -1;
1621 ELSE
1622 return_status := 0;
1623 END IF;
1624
1625 EXCEPTION
1626 WHEN OTHERS THEN
1627 return_status := -97;
1628 fnd_msg_pub.add_exc_msg('gmp_aps_writer','update_resource_transactions');
1629 e_msg := e_msg || ' Update SResource Transaction Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
1630 END update_resource_transactions;
1631
1632 /*
1633 REM+=========================================================================+
1634 REM| PROCEDURE NAME |
1635 REM| update_batch_activities |
1636 REM| DESCRIPTION |
1637 REM| This procedure will update the activity plan start and end date |
1638 REM| HISTORY |
1639 REM| Rajesh Patangya |
1640 REM+=========================================================================+
1641 */
1642 PROCEDURE update_batch_activities(
1643 pbatch_id IN NUMBER,
1644 porganization_id IN NUMBER, -- For R12.0
1645 pstep_id IN NUMBER,
1646 pactivity_id IN NUMBER,
1647 pstart_date IN DATE,
1648 pend_date IN DATE,
1649 puom_hour IN VARCHAR2,
1650 puser_id IN NUMBER,
1651 plogin_id IN NUMBER,
1652 return_status OUT NOCOPY NUMBER)
1653 IS
1654
1655 v_activity_id NUMBER ;
1656 v_step_id NUMBER ;
1657 v_batch_id NUMBER ;
1658 found NUMBER ;
1659 v_trn_start_date DATE ;
1660 v_trn_end_date DATE ;
1661 v_start_date DATE ;
1662 v_end_date DATE ;
1663 v_hour_uom VARCHAR2(3) ;
1664 v_organization_id NUMBER ;
1665 v_zero_res_id NUMBER ;
1666 v_offset_interval NUMBER ;
1667 temp_date DATE ;
1668
1669 v_trans_row gme_resource_txns%ROWTYPE;
1670
1671 -- Activities, its resources and resource transactions with ZERO usage
1672 -- or not convertible UOM
1673 CURSOR get_zero_non_usage IS
1674 SELECT
1675 gsr.batchstep_resource_id, gsr.resources, gsr.plan_rsrc_count,
1676 DECODE(gsr.plan_rsrc_usage, 0, 0, inv_convert.inv_um_convert(-1,38,
1677 gsr.plan_rsrc_usage,u2.uom_code,u1.uom_code,NULL,NULL)) plan_rsrc_usage,
1678 gsr.offset_interval,
1679 gsr.plan_start_date,
1680 gsr.plan_cmplt_date,
1681 crd.schedule_ind
1682 FROM
1683 gme_batch_step_resources gsr,
1684 cr_rsrc_dtl crd,
1685 mtl_units_of_measure u1,
1686 mtl_units_of_measure u2
1687 WHERE
1688 gsr.batchstep_activity_id = v_activity_id
1689 AND crd.resources = gsr.resources -- For R12.0
1690 AND crd.organization_id = v_organization_id -- For R12.0
1691 AND gsr.organization_id = crd.organization_id -- For R12.0
1692 AND crd.delete_mark = 0 -- For R12.0
1693 AND u1.uom_code = gsr.usage_um
1694 AND u2.uom_code = v_hour_uom
1695 AND (gsr.plan_rsrc_usage = 0 OR
1696 u1.uom_class <> u2.uom_class OR
1697 crd.schedule_ind = 3 ); -- For R12.0
1698 BEGIN
1699 return_status := 0;
1700 v_activity_id := pactivity_id;
1701 v_step_id := pstep_id;
1702 v_batch_id := pbatch_id;
1703 v_start_date := pstart_date ;
1704 v_end_date := pend_date ;
1705 v_organization_id := porganization_id ;
1706 found := 0;
1707 v_trn_start_date := NULL;
1708 v_trn_end_date := NULL;
1709 v_hour_uom := NULL;
1710 v_zero_res_id := 0;
1711 v_offset_interval := 0;
1712 temp_date := NULL;
1713
1714 UPDATE gme_batch_step_activities
1715 SET
1716 plan_start_date = v_start_date,
1717 plan_cmplt_date = v_end_date,
1718 last_update_date = SYSDATE,
1719 last_updated_by = puser_id
1720 WHERE batchstep_activity_id = v_activity_id;
1721
1722 IF SQL%NOTFOUND THEN
1723 return_status := -1;
1724 ELSE
1725 v_hour_uom := puom_hour;
1726 FOR v_zero IN get_zero_non_usage LOOP
1727 v_zero_res_id := v_zero.batchstep_resource_id;
1728 v_offset_interval := v_zero.offset_interval/24;
1729 found := 0;
1730
1731 IF v_zero.plan_rsrc_usage = 0 THEN
1732
1733 temp_date := v_start_date + v_offset_interval;
1734 IF temp_date > v_end_date THEN
1735 v_offset_interval := 0;
1736 END IF;
1737
1738 v_trn_start_date := v_start_date + v_offset_interval;
1739 v_trn_end_date := v_start_date + v_offset_interval;
1740
1741 UPDATE
1742 gme_batch_step_resources
1743 SET
1744 plan_start_date = v_trn_start_date,
1745 plan_cmplt_date = v_trn_end_date,
1746 last_update_date = SYSDATE,
1747 last_updated_by = puser_id
1748 WHERE
1749 batchstep_resource_id = v_zero_res_id;
1750
1751 IF SQL%NOTFOUND THEN
1752 return_status := -2;
1753 ELSE
1754 found := 1;
1755 END IF;
1756 ELSIF v_zero.plan_rsrc_usage < 0 THEN
1757
1758 -- Delete resource transactions for sequence depedent Usage */
1759 DELETE gme_resource_txns
1760 WHERE doc_id = v_batch_id
1761 AND nvl(sequence_dependent_ind,0) > 0
1762 AND line_id= v_zero_res_id ;
1763
1764 v_trn_start_date := v_start_date;
1765 v_trn_end_date := v_end_date;
1766
1767 UPDATE
1768 gme_batch_step_resources
1769 SET
1770 plan_start_date = v_trn_start_date,
1771 plan_cmplt_date = v_trn_end_date,
1772 last_update_date = SYSDATE,
1773 last_updated_by = puser_id
1774 WHERE
1775 batchstep_resource_id = v_zero_res_id;
1776
1777 IF SQL%NOTFOUND THEN
1778 return_status := -3;
1779 ELSE
1780 found := 1;
1781 END IF;
1782
1783 ELSIF (v_zero.plan_rsrc_usage > 0 AND v_zero.schedule_ind = 3) THEN
1784 -- DO NOT PLAN resource, we do not apply offset, start and end date
1785 -- must fall between activity used
1786 temp_date := v_start_date +
1787 ( (v_zero.plan_rsrc_usage / v_zero.plan_rsrc_count) / 24 ) ;
1788
1789 IF temp_date > v_end_date THEN
1790 fnd_message.set_name('GMP','GMP_USER_RSRC');
1791 fnd_msg_pub.add ;
1792 e_msg := e_msg || ' User must maintain the DO NOT PLAN resource dates. ' || v_zero.resources;
1793 ELSE
1794
1795 -- Delete resource transactions for sequence depedent Usage */
1796 DELETE gme_resource_txns
1797 WHERE doc_id = v_batch_id
1798 AND nvl(sequence_dependent_ind,0) > 0
1799 AND line_id= v_zero_res_id ;
1800
1801 v_trn_start_date := v_start_date ;
1802 v_trn_end_date := temp_date ;
1803
1804 UPDATE
1805 gme_batch_step_resources
1806 SET
1807 plan_start_date = v_trn_start_date,
1808 plan_cmplt_date = v_trn_end_date,
1809 last_update_date = SYSDATE,
1810 last_updated_by = puser_id
1811 WHERE
1812 batchstep_resource_id = v_zero_res_id;
1813
1814 IF SQL%NOTFOUND THEN
1815 return_status := -5;
1816 ELSE
1817 found := 1;
1818 END IF;
1819
1820 END IF; /* Date check */
1821 END IF;
1822
1823 IF found = 1 THEN
1824 UPDATE
1825 gme_resource_txns
1826 SET
1827 start_date = v_trn_start_date,
1828 end_date = v_trn_end_date,
1829 trans_date = v_trn_start_date,
1830 last_update_date = SYSDATE,
1831 last_updated_by = puser_id,
1832 instance_id = NULL,
1833 delete_mark = 0,
1834 sequence_dependent_ind = 0,
1835 overrided_protected_ind = 'N',
1836 last_update_login = plogin_id
1837 WHERE
1838 doc_id = v_batch_id
1839 AND doc_type in ('PROD','FPO')
1840 AND line_id = v_zero_res_id
1841 AND completed_ind = 0
1842 AND delete_mark = 0;
1843
1844 IF SQL%NOTFOUND THEN
1845 return_status := -6;
1846 END IF;
1847
1848 END IF; /* found */
1849
1850 END LOOP;
1851
1852 END IF; /* Not found */
1853 EXCEPTION
1854 WHEN OTHERS THEN
1855 return_status := -98;
1856 fnd_msg_pub.add_exc_msg('gmp_aps_writer','update_batch_activities');
1857 e_msg := e_msg || ' Update Step Activities Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
1858 END update_batch_activities;
1859
1860 /*
1861 REM+=========================================================================+
1862 REM| PROCEDURE NAME |
1863 REM| update_materails |
1864 REM| DESCRIPTION |
1865 REM| This procedure will update the Materail deatails as per GME rules |
1866 REM| HISTORY |
1867 REM| Rajesh Patangya |
1868 REM| If the item is associated to step and NOT having release_type of |
1869 REM| Automatic (0) in the material detail then the step's plan_start_date |
1870 REM| will be used for all ingredients (line_type= -1) and plan_cmplt_date |
1871 REM| for all products and byproducts (line_type = 1 or 2). |
1872 REM| If the item is not associated to step OR Item is associated to step and |
1873 REM| having release_type of Automatic (0) in the material detail then the |
1874 REM| batch's plan_start_date will be used for all ingredients (line_type= -1)|
1875 REM| and plan_cmplt_date for all products and byproducts (line_type = 1 or 2)|
1876 REM| |
1877 REM+=========================================================================+
1878 */
1879 PROCEDURE update_materails (
1880 pbatch_id IN NUMBER,
1881 porganization_id IN NUMBER,
1882 return_status OUT NOCOPY NUMBER)
1883 IS
1884
1885 v_batch_id NUMBER ;
1886 v_organization_id NUMBER ;
1887 v_material_detail_id NUMBER ;
1888 v_line_type NUMBER ;
1889 m_return_status VARCHAR2(1);
1890
1891 CURSOR get_step_material IS
1892 SELECT gmd.material_detail_id, gmd.line_type
1893 FROM gme_material_details gmd
1894 WHERE gmd.batch_id = v_batch_id
1895 AND gmd.organization_id = v_organization_id ;
1896
1897 BEGIN
1898 return_status := 0;
1899 m_return_status := NULL;
1900 v_batch_id := pbatch_id;
1901 v_organization_id := porganization_id;
1902 v_material_detail_id := 0;
1903 v_line_type := 0;
1904
1905 -- API will check the step and materail association for the batch and
1906 -- decide the materail requirement date should be step start/End OR
1907 -- Batch start/End Date. This will also ensures the further impact on
1908 -- Move order and allocations for the batch.
1909
1910 OPEN get_step_material;
1911 LOOP
1912 FETCH get_step_material INTO v_material_detail_id, v_line_type;
1913 EXIT WHEN get_step_material%NOTFOUND;
1914
1915 gme_api_grp.update_material_date(
1916 v_material_detail_id, -- p_material_detail_id,
1917 NULL, -- p_material_date
1918 m_return_status);
1919
1920 IF m_return_status = 'S' THEN
1921 return_status := 0 ;
1922 ELSE
1923 -- Basically E and U
1924 return_status := -19;
1925 EXIT ;
1926 END IF;
1927
1928 END LOOP;
1929 CLOSE get_step_material;
1930 EXCEPTION
1931 WHEN OTHERS THEN
1932 return_status := -89;
1933 fnd_msg_pub.add_exc_msg('gmp_aps_writer','update_materails');
1934 e_msg := e_msg || ' Update Materails Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
1935 END update_materails;
1936
1937 /*
1938 REM+=========================================================================+
1939 REM| PROCEDURE NAME |
1940 REM| update_batch_steps |
1941 REM| DESCRIPTION |
1942 REM| This procedure will update the step plan start and end date |
1943 REM| HISTORY |
1944 REM| Rajesh Patangya |
1945 REM| |
1946 REM+=========================================================================+
1947 */
1948 PROCEDURE update_batch_steps(
1949 pbatch_id IN NUMBER,
1950 pstep_no IN NUMBER,
1951 pstep_id IN NUMBER,
1952 pstart_date IN DATE,
1953 pend_date IN DATE,
1954 pdue_date IN DATE, -- B5454215
1955 puser_id IN NUMBER,
1956 plogin_id IN NUMBER,
1957 return_status OUT NOCOPY NUMBER)
1958 IS
1959
1960 v_plan_charges NUMBER ;
1961
1962 BEGIN
1963 return_status := 0;
1964 v_plan_charges := 0;
1965
1966 -- For R12.0
1967 BEGIN
1968 SELECT count(*) INTO v_plan_charges
1969 FROM
1970 gmp_aps_output_dtl gad,
1971 gme_batch_steps gbs
1972 WHERE gad.wip_entity_id = pbatch_id
1973 AND gad.load_type = 10
1974 AND gbs.batch_id = gad.wip_entity_id
1975 AND gbs.batchstep_no = pstep_no
1976 AND gbs.batchstep_no = gad.operation_seq_num
1977 AND gbs.delete_mark = 0
1978 AND gbs.step_status = 1 ;
1979 EXCEPTION
1980 WHEN NO_DATA_FOUND THEN
1981 v_plan_charges := 0;
1982 WHEN OTHERS THEN
1983 return_status := -995;
1984 fnd_msg_pub.add_exc_msg('gmp_aps_writer','update_batch_steps');
1985 e_msg := e_msg || ' CHARGE in Step Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
1986 END;
1987
1988 IF pdue_date IS NOT NULL THEN
1989 -- For New Batch only B5454215
1990
1991 IF v_plan_charges <> 0 THEN
1992
1993 UPDATE gme_batch_steps
1994 SET plan_start_date = pstart_date,
1995 plan_cmplt_date = pend_date,
1996 due_date = pdue_date,
1997 -- For R12.0
1998 plan_charges = DECODE(step_status,1,v_plan_charges,plan_charges),
1999 last_update_date = SYSDATE,
2000 last_updated_by = puser_id
2001 WHERE batch_id = pbatch_id
2002 AND batchstep_no = pstep_no
2003 AND batchstep_id = pstep_id ;
2004
2005 ELSE
2006
2007 UPDATE gme_batch_steps
2008 SET plan_start_date = pstart_date,
2009 plan_cmplt_date = pend_date,
2010 -- For R12.0
2011 due_date = pdue_date,
2012 last_update_date = SYSDATE,
2013 last_updated_by = puser_id
2014 WHERE batch_id = pbatch_id
2015 AND batchstep_no = pstep_no
2016 AND batchstep_id = pstep_id ;
2017
2018 END IF; /* Plan Charges */
2019
2020 ELSE
2021
2022 IF v_plan_charges <> 0 THEN
2023
2024 UPDATE gme_batch_steps
2025 SET plan_start_date = pstart_date,
2026 plan_cmplt_date = pend_date,
2027 -- For R12.0
2028 plan_charges = DECODE(step_status,1,v_plan_charges,plan_charges),
2029 last_update_date = SYSDATE,
2030 last_updated_by = puser_id
2031 WHERE batch_id = pbatch_id
2032 AND batchstep_no = pstep_no
2033 AND batchstep_id = pstep_id ;
2034
2035 ELSE
2036
2037 UPDATE gme_batch_steps
2038 SET plan_start_date = pstart_date,
2039 plan_cmplt_date = pend_date,
2040 last_update_date = SYSDATE,
2041 last_updated_by = puser_id
2042 WHERE batch_id = pbatch_id
2043 AND batchstep_no = pstep_no
2044 AND batchstep_id = pstep_id ;
2045
2046 END IF; /* Plan Charges */
2047
2048 END IF; /* v_due_date */
2049
2050 IF SQL%NOTFOUND THEN
2051 return_status := -11;
2052 END IF; /* Not found */
2053
2054 EXCEPTION
2055 WHEN OTHERS THEN
2056 return_status := -95;
2057 fnd_msg_pub.add_exc_msg('gmp_aps_writer','update_batch_steps');
2058 e_msg := e_msg || ' Update Batch Step Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
2059 END update_batch_steps;
2060
2061 /*
2062 REM+=========================================================================+
2063 REM| PROCEDURE NAME |
2064 REM| update_batch_header |
2065 REM| DESCRIPTION |
2066 REM| This procedure will update the batch plan start and end date |
2067 REM| HISTORY |
2068 REM| Rajesh Patangya |
2069 REM| |
2070 REM+=========================================================================+
2071 */
2072 PROCEDURE update_batch_header(
2073 pbatch_id IN NUMBER,
2074 pstart_date IN DATE,
2075 pend_date IN DATE,
2076 preq_completion_date IN DATE, -- For R12.0
2077 pord_priority IN NUMBER, -- For R12.0
2078 pbatch_status IN NUMBER,
2079 pfirm_flag IN NUMBER, -- B5897392
2080 puser_id IN NUMBER,
2081 plogin_id IN NUMBER,
2082 return_status OUT NOCOPY NUMBER)
2083 IS
2084
2085 v_batch_status NUMBER ;
2086
2087 BEGIN
2088 return_status := 0;
2089 v_batch_status := pbatch_status ;
2090
2091 IF v_batch_status = 1 THEN
2092 -- pending
2093 -- Only Update the new batch with due_date informnation,
2094 -- otherwise take the same date what GME have, basically reschedule batch
2095 UPDATE gme_batch_header
2096 SET
2097 plan_start_date = pstart_date,
2098 plan_cmplt_date = pend_date,
2099 due_date = NVL(preq_completion_date,gme_batch_header.due_date),
2100 order_priority = NVL(pord_priority,gme_batch_header.order_priority),
2101 firmed_ind = pfirm_flag, -- B5897392
2102 last_update_date = SYSDATE,
2103 last_updated_by = puser_id,
2104 last_update_login = plogin_id
2105 -- finite_scheduled_ind = 1 /*B5186781*/
2106 WHERE batch_id = pbatch_id;
2107 ELSE
2108 -- In Wip status
2109 -- No changes for WIP batch for due_date informnation
2110 UPDATE gme_batch_header
2111 SET
2112 plan_cmplt_date = pend_date,
2113 order_priority = NVL(pord_priority,gme_batch_header.order_priority),
2114 firmed_ind = pfirm_flag, -- B5897392
2115 last_update_date = SYSDATE,
2116 last_updated_by = puser_id,
2117 last_update_login = plogin_id
2118 -- finite_scheduled_ind = 1 /*B5186781*/
2119 WHERE batch_id = pbatch_id;
2120 END IF;
2121
2122 IF SQL%NOTFOUND THEN
2123 return_status := -12;
2124 END IF; /* Not found */
2125
2126 EXCEPTION
2127 WHEN OTHERS THEN
2128 return_status := -94;
2129 fnd_msg_pub.add_exc_msg('gmp_aps_writer','update_batch_header');
2130 e_msg := e_msg || ' Update Batch Header Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
2131 END update_batch_header;
2132
2133 /*
2134 REM+=========================================================================+
2135 REM| PROCEDURE NAME |
2136 REM| lock_batch_details |
2137 REM| DESCRIPTION |
2138 REM| This procedure will select for update all of the batch details |
2139 REM| except for the transactions. |
2140 REM| HISTORY |
2141 REM| Rajesh Patangya |
2142 REM+=========================================================================+
2143 */
2144 PROCEDURE lock_batch_details(
2145 pbatch_id IN NUMBER,
2146 pbatch_status OUT NOCOPY NUMBER,
2147 pbatch_last_update OUT NOCOPY DATE,
2148 return_status OUT NOCOPY NUMBER)
2149 IS
2150
2151 l_batch_id NUMBER ;
2152 l_batch_status NUMBER ;
2153 l_batch_last_update DATE ;
2154
2155 v_batch_id NUMBER ;
2156 found NUMBER ;
2157
2158 /* lock the batch header being updated */
2159 CURSOR lock_batch_header IS
2160 SELECT
2161 batch_id, batch_status, last_update_date
2162 FROM
2163 gme_batch_header
2164 WHERE
2165 batch_id = v_batch_id
2166 FOR UPDATE NOWAIT;
2167
2168 /* lock all of the batch steps for update */
2169 CURSOR lock_batch_steps IS
2170 SELECT
2171 batch_id
2172 FROM
2173 gme_batch_steps
2174 WHERE
2175 batch_id = v_batch_id
2176 FOR UPDATE NOWAIT;
2177
2178 /* lock all of the batch step activities for update */
2179 CURSOR lock_batch_activities IS
2180 SELECT
2181 batch_id
2182 FROM
2183 gme_batch_step_activities
2184 WHERE
2185 batch_id = v_batch_id
2186 FOR UPDATE NOWAIT;
2187
2188 /* lock all of the batch step resources for update */
2189 CURSOR lock_batch_resources IS
2190 SELECT
2191 batch_id
2192 FROM
2193 gme_batch_step_resources
2194 WHERE
2195 batch_id = v_batch_id
2196 FOR UPDATE NOWAIT;
2197
2198 BEGIN
2199
2200 l_batch_id := 0;
2201 l_batch_status := 0;
2202 l_batch_last_update := NULL;
2203 found := 0;
2204
2205 return_status := 0;
2206 v_batch_id := pbatch_id;
2207
2208 OPEN lock_batch_header;
2209 LOOP
2210 FETCH lock_batch_header INTO l_batch_id, l_batch_status,
2211 l_batch_last_update;
2212 EXIT WHEN lock_batch_header%NOTFOUND;
2213 pbatch_status := l_batch_status ;
2214 pbatch_last_update := l_batch_last_update;
2215 found := 1;
2216 END LOOP;
2217 CLOSE lock_batch_header;
2218 IF found = 0 THEN
2219 return_status := -1;
2220 ELSE
2221 found := 0;
2222 OPEN lock_batch_steps;
2223 LOOP
2224 FETCH lock_batch_steps INTO l_batch_id;
2225 EXIT WHEN lock_batch_steps%NOTFOUND;
2226 found := 1;
2227 END LOOP;
2228 CLOSE lock_batch_steps;
2229 IF found = 0 THEN
2230 return_status := -2;
2231 ELSE
2232 found := 0;
2233 OPEN lock_batch_activities;
2234 LOOP
2235 FETCH lock_batch_activities INTO l_batch_id;
2236 EXIT WHEN lock_batch_activities%NOTFOUND;
2237 found := 1;
2238 END LOOP;
2239 CLOSE lock_batch_activities;
2240 IF found = 0 THEN
2241 return_status := -3;
2242 ELSE
2243 found := 0;
2244 OPEN lock_batch_resources;
2245 LOOP
2246 FETCH lock_batch_resources INTO l_batch_id;
2247 EXIT WHEN lock_batch_resources%NOTFOUND;
2248 found := 1;
2249 END LOOP;
2250 CLOSE lock_batch_resources;
2251 IF found = 0 THEN
2252 return_status := -4;
2253 END IF;
2254 END IF;
2255 END IF;
2256 END IF;
2257
2258 EXCEPTION
2259 WHEN OTHERS THEN
2260 return_status := -92;
2261 fnd_msg_pub.add_exc_msg('gmp_aps_writer','lock_batch_details');
2262 e_msg := e_msg || ' Loocking Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
2263 END lock_batch_details;
2264
2265 /*
2266 REM+=========================================================================+
2267 REM| PROCEDURE NAME |
2268 REM| validate_structure |
2269 REM| DESCRIPTION |
2270 REM| This procedure validate the structure for new batch with the APS |
2271 REM| information coming back. |
2272 REM| 1. Number of Operations(Insert/Update/Delete |
2273 REM| 2. Number of Activities(Insert/Update/Delete |
2274 REM| 3. Number of resources (Insert/Update/Delete |
2275 REM| 4. Change Recipe/Validity Rule OR Routing/formula header |
2276 REM| NOTE : |
2277 REM| We are not validating materials overrides as per discussion |
2278 REM| HISTORY |
2279 REM| Rajesh Patangya |
2280 REM| B3583620 - Rearranged the Group By clause and made unique in APS table |
2281 REM| Kaushek B |
2282 REM| B6407903 - Used TRUNC for all the date columns used in the structure_cursor|
2283 REM+=========================================================================+
2284 */
2285 PROCEDURE validate_structure (
2286 pfmeff_id IN NUMBER,
2287 porganization_id IN NUMBER, -- For R12.0
2288 pgroup_id IN NUMBER,
2289 pheader_id IN NUMBER,
2290 struc_size OUT NOCOPY NUMBER,
2291 return_status OUT NOCOPY NUMBER)
2292 IS
2293
2294 /* Local array definition */
2295 TYPE ref_cursor_typ IS REF CURSOR;
2296
2297 TYPE structure_typ IS RECORD
2298 (
2299 recipe_id NUMBER(16),
2300 formula_id NUMBER(16),
2301 routing_id NUMBER(16),
2302 routingstep_id NUMBER(16),
2303 routingstep_no NUMBER(8),
2304 oprn_id NUMBER(16),
2305 oprn_line_id NUMBER(16),
2306 oprn_no VARCHAR2(40),
2307 activity VARCHAR2(40),
2308 resource_seq_num NUMBER(8),
2309 recipe_change NUMBER(16), -- For R12.0
2310 validity_rule_change NUMBER(16), -- For R12.0
2311 formula_header_change NUMBER(16), -- For R12.0
2312 formula_detail_change NUMBER(16), -- For R12.0
2313 routing_header_change NUMBER(16), -- For R12.0
2314 rtg_detail_change NUMBER(16),
2315 rtg_oper_change NUMBER(16),
2316 rtg_activity_change NUMBER(16),
2317 opm_resource_change NUMBER(16), -- This will be used as opm resource sum
2318 opm_oper_sum NUMBER(16),
2319 opm_activity_sum NUMBER(16),
2320 aps_opeartion_seq_num NUMBER(8),
2321 aps_resource_seq_num NUMBER(8),
2322 aps_oper_sum NUMBER(16),
2323 aps_resource_sum NUMBER(16),
2324 aps_activity_sum NUMBER(16)
2325 );
2326 TYPE structure_tbl IS TABLE OF structure_typ INDEX by BINARY_INTEGER;
2327 structure_tab structure_tbl;
2328
2329 structure_size INTEGER; /* Number of rows */
2330
2331 cur_structure ref_cursor_typ;
2332 structure_cursor VARCHAR2(10000) ;
2333
2334 BEGIN
2335
2336 return_status := 0 ;
2337 structure_cursor := NULL;
2338
2339 -- Finally it is the summary of OPM and APS information
2340 structure_cursor := ' SELECT opm.recipe_id, opm.formula_id, '
2341 ||' opm.routing_id, opm.routingstep_id, opm.routingstep_no, '
2342 ||' opm.oprn_id, opm.oprn_line_id, opm.oprn_no, opm.activity, '
2343 ||' opm.resource_seq_num, opm.recipe_change, opm.validity_rule_change,'
2344 ||' opm.formula_header_change, opm.formula_detail_change, '
2345 ||' opm.routing_header_change, '
2346 ||' opm.rtg_detail_change, opm.rtg_oper_change, '
2347 ||' opm.rtg_activity_change, opm.opm_resource_change, '
2348 ||' opm.opm_oper_sum, opm.opm_activity_sum,'
2349 ||' aps.operation_seq_num, aps.resource_seq_num, '
2350 ||' aps.aps_oper_sum, aps.aps_resource_sum, aps.aps_activity_sum '
2351 ||' FROM ( '
2352 -- Find the count of routing operations, activity
2353 -- find the routing detail change, operation change, activity change
2354 ||' SELECT recipe_id, formula_id, routing_id, routingstep_id, '
2355 ||' routingstep_no, oprn_id, oprn_line_id, activity, oprn_no , '
2356 ||' seq_dep_ind resource_seq_num, '
2357 ||' offset_interval, '
2358 ||' gr_last_date recipe_change,'
2359 ||' ffe_last_date validity_rule_change, '
2360 ||' ffm_last_date formula_header_change,'
2361 ||' fmd_last_date formula_detail_change,'
2362 ||' frh_last_date routing_header_change,'
2363 ||' SUM(frd_last_date) '
2364 ||' OVER (PARTITION BY routing_id) rtg_detail_change , '
2365 ||' SUM(fom_last_date) '
2366 ||' OVER (PARTITION BY routing_id) rtg_oper_change , '
2367 ||' SUM(goa_last_date) '
2368 ||' OVER (PARTITION BY routing_id) rtg_activity_change , '
2369 ||' opm_resource_change , '
2370 ||' opm_oper_sum, '
2371 -- PS Issue B6045398, Activity count is incorrect
2372 ||' COUNT(unique oprn_line_id) OVER (PARTITION BY '
2373 ||' routing_id,oprn_id ) opm_activity_sum '
2374 ||' FROM ( '
2375 ||' SELECT gr.recipe_id, gr.formula_id, gr.routing_id, '
2376 ||' frd.routingstep_id, frd.routingstep_no, '
2377 ||' nvl(goa.sequence_dependent_ind,0) seq_dep_ind, g1.gen_lupd, '
2378 ||' goa.offset_interval, '
2379 -- B5714301, changed the position of operation count
2380 ||' COUNT(unique frd.routingstep_no) OVER (PARTITION BY '
2381 ||' gr.routing_id) opm_oper_sum, '
2382 -- Recipe/Validity Rule OR Routing/formula header changed
2383 ||' DECODE(sign(g1.gen_lupd '
2384 ||' - trunc(gr.last_update_date)), 1,1,0,1,-1,-600) gr_last_date, '
2385 ||' DECODE(sign(g1.gen_lupd '
2386 ||' - trunc(ffe.last_update_date)), 1,1,0,1,-1,-600) ffe_last_date,'
2387 ||' DECODE(sign(g1.gen_lupd '
2388 ||' - trunc(ffm.last_update_date)), 1,1,0,1,-1,-600) ffm_last_date, '
2389 ||' ( SELECT sum(DECODE(sign(gen_lupd'
2390 ||' - trunc(fmd.last_update_date)), 1,1,0,1,-1,-600))'
2391 ||' FROM fm_matl_dtl fmd '
2392 ||' WHERE fmd.formula_id = gr.formula_id) fmd_last_date,'
2393 ||' DECODE(sign(g1.gen_lupd '
2394 ||' - trunc(frh.last_update_date)), 1,1,0,1,-1,-600) frh_last_date, '
2395 ||' DECODE(sign(g1.gen_lupd '
2396 ||' - trunc(frd.last_update_date)), 1,1,0,1,-1,-600) frd_last_date, '
2397 ||' fom.oprn_id, fom.oprn_no,'
2398 ||' DECODE(sign(g1.gen_lupd '
2399 ||' - trunc(fom.last_update_date)), 1,1,0,1,-1,-600) fom_last_date, '
2400 ||' goa.oprn_line_id, goa.activity, '
2401 ||' DECODE(sign(g1.gen_lupd '
2402 ||' - trunc(goa.last_update_date)), 1,1,0,1,-1,-600) goa_last_date, '
2403 ||' nvl((SELECT SUM(DECODE(sign(g1.gen_lupd '
2404 ||' - trunc(gor.last_update_date)), 1,1,0,1,-1,-600))'
2405 ||' FROM'
2406 ||' gmd_operation_resources gor, '
2407 ||' cr_rsrc_dtl crd '
2408 ||' WHERE'
2409 ||' goa.oprn_line_id = gor.oprn_line_id '
2410 ||' AND crd.organization_id = :porgid ' -- For R12.0
2411 ||' AND crd.resources = gor.resources ' -- For R12.0
2412 ||' AND crd.delete_mark = 0 ' -- For R12.0
2413 ||' AND crd.schedule_ind <> 3 ' -- Do Not plan
2414 /*
2415 OPM is sending ZERO resource usages along with routing, if it is not a primary resource. APS is sending back these resources may be with usage or ZERO resource usage. We have to consider this resource for validation, but for final update
2416 we have to use GME WAY to update this type of resource. This is only
2417 applicable for NEW BATCH */
2418 -- ||' AND gor.resource_usage > 0' -- Do Not plan R12
2419 ||' GROUP BY gor.oprn_line_id '
2420 ||' ),0) opm_resource_change'
2421 ||' FROM '
2422 ||' gmd_recipes_b gr, '
2423 ||' gmd_recipe_validity_rules ffe, '
2424 ||' fm_form_mst ffm, '
2425 ||' fm_rout_hdr frh, '
2426 ||' fm_rout_dtl frd , '
2427 ||' gmd_operations fom, '
2428 ||' gmd_operation_activities goa, '
2429 -- B6051303, PS Issue
2430 ||' ( SELECT creation_date gen_lupd '
2431 ||' FROM GMP_APS_OUTPUT_TBL WHERE process_id = :pgrp1 '
2432 ||' AND header_id = :phdr1 ) g1 '
2433 ||' WHERE gr.recipe_id = ffe.recipe_id '
2434 ||' AND gr.routing_id = frh.routing_id '
2435 ||' AND gr.formula_id = ffm.formula_id '
2436 ||' AND frd.routing_id = gr.routing_id '
2437 ||' AND frd.oprn_id = fom.oprn_id '
2438 ||' AND fom.oprn_id = goa.oprn_id '
2439 ||' AND ffe.recipe_validity_rule_id = :eff1 '
2440 ||' ) '
2441 ||' WHERE opm_resource_change <> 0 '
2442 ||' ) OPM , '
2443 ||' ( '
2444 -- Query will take count at operation, activity for resources
2445 ||' SELECT a.operation_seq_num, a.schedule_seq_num resource_seq_num, '
2446 ||' count(unique a.operation_seq_num) ' /* B3583620 */
2447 ||' OVER (PARTITION BY b.process_id, b.header_id) aps_oper_sum, '
2448 ||' count(unique a.schedule_seq_num) ' /* B3583620 */
2449 ||' OVER (PARTITION BY a.operation_seq_num ) aps_activity_sum, '
2450 ||' count(a.resource_id_new) '
2451 ||' OVER (PARTITION BY a.operation_seq_num, '
2452 ||' a.schedule_seq_num) aps_resource_sum '
2453 ||' FROM gmp_aps_output_dtl a, '
2454 ||' gmp_aps_output_tbl b '
2455 ||' WHERE a.parent_header_id = b.header_id '
2456 ||' AND a.group_id = b.process_id '
2457 ||' AND b.process_id = :pgrp2 '
2458 ||' AND b.header_id = :phdr2 '
2459 ||' AND b.effectivity_id = :eff2 '
2460 ||' AND a.load_type = 1 '
2461 ||' AND a.parent_seq_num IS NULL '
2462 ||' ) APS '
2463 ||' WHERE opm.resource_seq_num(+) = aps.resource_seq_num '
2464 ||' AND opm.routingstep_no(+) = aps.operation_seq_num '
2465 ||' AND opm.routingstep_no IS NOT NULL ' ;
2466
2467 structure_size := 1;
2468 OPEN cur_structure FOR structure_cursor USING
2469 porganization_id, pgroup_id, pheader_id, pfmeff_id,
2470 pgroup_id, pheader_id, pfmeff_id ;
2471
2472 LOOP
2473 FETCH cur_structure INTO structure_tab(structure_size);
2474 EXIT WHEN cur_structure%NOTFOUND;
2475 IF structure_tab(structure_size).recipe_change < 0 THEN
2476 fnd_message.set_name('GMP','GMP_RECIPE_CHANGED');
2477 fnd_msg_pub.add ;
2478 e_msg := e_msg || ' Recipe is Changed';
2479 return_status := -1 ;
2480 EXIT ;
2481
2482 ELSIF structure_tab(structure_size).validity_rule_change < 0 THEN
2483 fnd_message.set_name('GMP','GMP_RECIPE_VR_CHANGED');
2484 fnd_msg_pub.add ;
2485 e_msg := e_msg || ' Recipe/Validity Rule changed' ;
2486 return_status := -1 ;
2487 EXIT ;
2488
2489 ELSIF structure_tab(structure_size).formula_header_change < 0 THEN
2490 fnd_message.set_name('GMP','GMP_FORMULA_CHANGED');
2491 fnd_msg_pub.add ;
2492 e_msg := e_msg || ' Formula Header/detail is Changed ';
2493 return_status := -1 ;
2494 EXIT ;
2495
2496 ELSIF structure_tab(structure_size).formula_detail_change < 0 THEN
2497 fnd_message.set_name('GMP','GMP_FORMULA_CHANGED');
2498 fnd_msg_pub.add ;
2499 e_msg := e_msg || ' Formula Header/detail is Changed ';
2500 return_status := -1 ;
2501 EXIT ;
2502
2503 ELSIF structure_tab(structure_size).routing_header_change < 0 THEN
2504 fnd_message.set_name('GMP','GMP_ROUTING_CHANGED');
2505 fnd_msg_pub.add ;
2506 e_msg := e_msg || ' Routing Header is changed';
2507 return_status := -1 ;
2508 EXIT ;
2509
2510 ELSIF structure_tab(structure_size).routingstep_no IS NULL THEN
2511 fnd_message.set_name('GMP','GMP_ROUTING_ACT_CHANGED');
2512 fnd_msg_pub.add ;
2513 e_msg := e_msg || ' Routing Opeartion/Activity deleted';
2514 return_status := -1 ;
2515 EXIT ;
2516 ELSIF (structure_tab(structure_size).opm_resource_change <>
2517 structure_tab(structure_size).aps_resource_sum) THEN
2518 fnd_message.set_name('GMP','GMP_RSRC_MISMATCH');
2519 fnd_msg_pub.add ;
2520 e_msg := e_msg || ' Routing resources added or deleted. Details of the batch will NOT be synchronized.' ;
2521 return_status := -1 ;
2522 EXIT ;
2523 ELSIF (structure_tab(structure_size).opm_oper_sum <>
2524 structure_tab(structure_size).aps_oper_sum ) THEN
2525 fnd_message.set_name('GMP','GMP_OPERATION_MISMATCH');
2526 fnd_msg_pub.add ;
2527 e_msg := e_msg || ' Routing steps added or deleted. Details of the batch will NOT be synchronized.' ;
2528 return_status := -1 ;
2529 EXIT ;
2530 ELSIF (structure_tab(structure_size).opm_activity_sum <>
2531 structure_tab(structure_size).aps_activity_sum) THEN
2532 fnd_message.set_name('GMP','GMP_ACTIVITY_MISMATCH');
2533 fnd_msg_pub.add ;
2534 e_msg := e_msg || ' Routing activity added or deleted. Details of the batch will NOT be synchronized.' ;
2535 return_status := -1 ;
2536 EXIT ;
2537 ELSIF (structure_tab(structure_size).rtg_detail_change < 0) THEN
2538 fnd_message.set_name('GMP','GMP_OPERATION_CHANGED');
2539 fnd_msg_pub.add ;
2540 e_msg := e_msg || ' Details of a step have changed. Details of the batch will NOT be synchronized.' ;
2541 return_status := -1 ;
2542 EXIT ;
2543 ELSIF (structure_tab(structure_size).rtg_oper_change < 0) THEN
2544 fnd_message.set_name('GMP','GMP_OPERATION_CHANGED');
2545 fnd_msg_pub.add ;
2546 e_msg := e_msg || ' Details of a step have changed. Details of the batch will NOT be synchronized.' ;
2547 return_status := -1 ;
2548 EXIT ;
2549 ELSIF (structure_tab(structure_size).rtg_activity_change < 0) THEN
2550 fnd_message.set_name('GMP','GMP_ROUTING_ACT_CHANGED');
2551 fnd_msg_pub.add ;
2552 e_msg := e_msg || ' An activity in the routing has changed. Details of the batch will NOT be synchronized.';
2553 return_status := -1 ;
2554 EXIT ;
2555 ELSIF (structure_tab(structure_size).opm_resource_change < 0) THEN
2556 -- MEssage is OK
2557 fnd_message.set_name('GMP','GMP_ROUTING_CHANGED2');
2558 fnd_msg_pub.add ;
2559 e_msg := e_msg || ' Routing resources have changed. Details of the batch will NOT be synchronized.' ;
2560 return_status := -1 ;
2561 EXIT ;
2562 ELSE
2563 return_status := 0 ;
2564 END IF ;
2565
2566 structure_size := structure_size + 1;
2567 END LOOP;
2568 CLOSE cur_structure;
2569 time_stamp;
2570
2571 structure_size := structure_size - 1;
2572 log_message('Structure size is = ' || to_char(structure_size)) ;
2573
2574 struc_size := structure_size ; /*B3583620 */
2575 EXCEPTION
2576 WHEN OTHERS THEN
2577 return_status := -91;
2578 fnd_msg_pub.add_exc_msg('gmp_aps_writer','valildate_structure');
2579 e_msg := e_msg || ' Validation Failure '||TO_CHAR(SQLCODE) || ': '||SQLERRM;
2580 END validate_structure ;
2581
2582 /*
2583 REM+=========================================================================+
2584 REM| PROCEDURE NAME |
2585 REM| log_message |
2586 REM| DESCRIPTION |
2587 REM| |
2588 REM| HISTORY |
2589 REM| Rajesh Patangya |
2590 REM+=========================================================================+
2591 */
2592 PROCEDURE log_message(
2593 pbuff VARCHAR2)
2594 IS
2595 BEGIN
2596 fnd_file.put_line(fnd_file.log, pbuff);
2597 END log_message;
2598
2599 /*
2600 REM+=========================================================================+
2601 REM| PROCEDURE NAME |
2602 REM| time_stamp |
2603 REM| DESCRIPTION |
2604 REM| |
2605 REM| HISTORY |
2606 REM| Rajesh Patangya |
2607 REM+=========================================================================+
2608 */
2609 PROCEDURE time_stamp IS
2610
2611 cur_time VARCHAR2(25) ;
2612 BEGIN
2613 cur_time := NULL ;
2614
2615 SELECT to_char(sysdate,'DD-MON-RRRR HH24:MI:SS')
2616 INTO cur_time FROM sys.dual ;
2617
2618 log_message(cur_time);
2619 EXCEPTION
2620 WHEN OTHERS THEN
2621 fnd_msg_pub.add_exc_msg('gmp_aps_writer','time_stamp');
2622 e_msg := e_msg || ' time_stamp Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
2623 END time_stamp ;
2624
2625 /*
2626 REM+=========================================================================+
2627 REM| PROCEDURE NAME |
2628 REM| update_activity_offsets (Bug # 3679906) |
2629 REM| DESCRIPTION |
2630 REM| This procedure is called by update_batches and also by the |
2631 REM| new batch/reschedule forms to update the activity |
2632 REM| offsets for each of the batch |
2633 REM| HISTORY |
2634 REM| Rajesh Patangya |
2635 REM+=========================================================================+
2636 */
2637 PROCEDURE update_activity_offsets ( batch_id IN NUMBER) IS
2638
2639 TYPE offset_rec IS RECORD(
2640 batchstep_id NUMBER(20),
2641 batchstep_no NUMBER(10),
2642 batchstep_activity_id NUMBER(20),
2643 activity VARCHAR2(16),
2644 offset_interval NUMBER,
2645 oprn_line_id NUMBER(20),
2646 actual_usage NUMBER ,
2647 start_date VARCHAR2(30),
2648 completion_date VARCHAR2(30),
2649 prev_act_offset NUMBER(15),
2650 final_offset NUMBER
2651 );
2652
2653 TYPE offset_tbl IS TABLE OF offset_rec INDEX by BINARY_INTEGER;
2654 offset_tab offset_tbl;
2655
2656 TYPE batch_activity_cur IS REF CURSOR;
2657 c_batch_dtl batch_activity_cur;
2658
2659 v_batch_cursor VARCHAR2(15000) ;
2660 v_batch_id NUMBER;
2661 act_count NUMBER;
2662 p NUMBER;
2663 batch_size NUMBER;
2664
2665 BEGIN
2666
2667 v_batch_cursor := NULL;
2668 p := 0;
2669 v_batch_id := batch_id;
2670 act_count := 1;
2671 batch_size := 1;
2672
2673 v_batch_cursor := ' SELECT batchstep_id '
2674 ||' batchstep_no, '
2675 ||' batchstep_activity_id activity_id, '
2676 ||' activity, '
2677 ||' offset_interval orig_offset, '
2678 ||' oprn_line_id, '
2679 ||' actual_usage, '
2680 ||' to_char(plan_start_date,'||''''||'DD-MON-YYYY HH24:MI:SS'||''''||') start_date, '
2681 ||' to_char(plan_cmplt_date,'||''''||'DD-MON-YYYY HH24:MI:SS'||''''||') completion_date, '
2682 ||' DECODE( sign(batchstep_no - NVL((lag(batchstep_no,1) over(order by batchstep_id)),0) '
2683 ||' ),-1,0,1,0,(lag(actual_usage,1) over(order by batchstep_id)) ) prev_act_offset, '
2684 ||' (SUM(actual_usage) '
2685 ||' OVER (PARTITION BY batchstep_no order by batchstep_no '
2686 ||' ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - actual_usage) final_offset '
2687 ||' FROM ( '
2688 ||' SELECT distinct '
2689 ||' gsa.batchstep_id, '
2690 ||' gbs.batchstep_no, '
2691 ||' gsa.batchstep_activity_id, '
2692 ||' gsa.activity, '
2693 ||' gsa.offset_interval, '
2694 ||' nvl(gsa.oprn_line_id,0) oprn_line_id, '
2695 ||' gsa.plan_start_date, '
2696 ||' gsa.plan_cmplt_date, '
2697 ||' DECODE(nvl(gsa.sequence_dependent_ind,0),1,1,0) , '
2698 ||' max((gsr.plan_rsrc_usage/gsr.plan_rsrc_count)) actual_usage '
2699 ||' FROM gme_batch_step_activities gsa,'
2700 ||' gme_batch_step_resources gsr,'
2701 ||' gme_batch_steps gbs '
2702 ||' WHERE gsa.batch_id = :p_batch_id '
2703 ||' AND gbs.batch_id = gsa.batch_id '
2704 ||' AND gsr.batch_id = gsa.batch_id '
2705 ||' AND gsa.delete_mark = 0 '
2706 ||' AND gbs.delete_mark = 0 '
2707 ||' AND gbs.step_status = 1 '
2708 ||' AND gsa.batchstep_id = gbs.batchstep_id '
2709 ||' AND gsr.batchstep_activity_id = gsa.batchstep_activity_id '
2710 ||' AND gsr.prim_rsrc_ind = 1 '
2711 ||' GROUP BY '
2712 ||' gsa.batchstep_id, '
2713 ||' gbs.batchstep_no, '
2714 ||' gsa.batchstep_activity_id, '
2715 ||' gsa.activity, '
2716 ||' gsa.offset_interval, '
2717 ||' gsa.oprn_line_id, '
2718 ||' gsa.plan_start_date, '
2719 ||' gsa.plan_cmplt_date, '
2720 ||' DECODE(nvl(gsa.sequence_dependent_ind,0),1,1,0) '
2721 ||' ORDER BY gbs.batchstep_no, '
2722 ||' DECODE(nvl(gsa.sequence_dependent_ind,0),1,1,0) DESC, '
2723 ||' gsa.offset_interval, gsa.activity, nvl(gsa.oprn_line_id,0) '
2724 ||' ) ';
2725
2726 OPEN c_batch_dtl FOR v_batch_cursor USING v_batch_id ;
2727 LOOP
2728 FETCH c_batch_dtl INTO offset_tab(act_count);
2729 EXIT WHEN c_batch_dtl%NOTFOUND ;
2730 act_count := act_count + 1;
2731 END LOOP;
2732 CLOSE c_batch_dtl ;
2733
2734 batch_size := act_count - 1;
2735
2736 FOR p IN 1..batch_size
2737 LOOP
2738 UPDATE gme_batch_step_activities
2739 SET offset_interval = offset_tab(p).final_offset
2740 WHERE batch_id = v_batch_id
2741 AND batchstep_id = offset_tab(p).batchstep_id
2742 AND batchstep_activity_id = offset_tab(p).batchstep_activity_id
2743 AND oprn_line_id = offset_tab(p).oprn_line_id;
2744 END LOOP;
2745
2746 COMMIT;
2747
2748 EXCEPTION
2749 WHEN OTHERS THEN
2750 fnd_msg_pub.add_exc_msg('gmp_aps_writer','update_activity_offsets');
2751 e_msg := e_msg || ' update_activity_offsets Failure '|| TO_CHAR(SQLCODE)
2752 ||': '||SQLERRM;
2753
2754 END update_activity_offsets;
2755
2756 /*
2757 REM+=========================================================================+
2758 REM| PROCEDURE NAME |
2759 REM| update_batches (Bug # 3679906) |
2760 REM| DESCRIPTION |
2761 REM| This procedure is called by the concurernt program for all the |
2762 REM| batches to make GME batches in sync with APS suggestions |
2763 REM| which in turn calls the update_activity_offsets to update activity |
2764 REM| offsets in each btach once the APS engine has completed. |
2765 REM| |
2766 REM| HISTORY |
2767 REM| Rajesh Patangya |
2768 REM+=========================================================================+
2769 */
2770 PROCEDURE update_batches IS
2771
2772 TYPE batch_fet_cur IS REF CURSOR;
2773 c_batch_id_cur batch_fet_cur;
2774
2775 v_batch_sql VARCHAR2(4000) ;
2776 m_batch_id NUMBER;
2777
2778 BEGIN
2779
2780 v_batch_sql := NULL;
2781
2782 v_batch_sql := ' SELECT batch_id FROM gme_batch_header '
2783 || ' WHERE delete_mark = 0 AND batch_status IN (1,2) ';
2784
2785 OPEN c_batch_id_cur FOR v_batch_sql ;
2786 LOOP
2787 FETCH c_batch_id_cur INTO m_batch_id;
2788 EXIT WHEN c_batch_id_cur%NOTFOUND ;
2789
2790 update_activity_offsets (m_batch_id);
2791
2792 END LOOP;
2793 CLOSE c_batch_id_cur ;
2794
2795 END update_batches;
2796
2797 /*
2798 REM+=========================================================================+
2799 REM| PROCEDURE NAME |
2800 REM| Insert_charges |
2801 REM| DESCRIPTION |
2802 REM| This procedure Deletes/Inserts the pending step charges |
2803 REM| HISTORY |
2804 REM| Rajesh Patangya |
2805 REM+=========================================================================+
2806 */
2807 PROCEDURE insert_charges (
2808 pbatch_id IN NUMBER,
2809 pgroup_id IN NUMBER,
2810 pheader_id IN NUMBER,
2811 return_status OUT NOCOPY NUMBER)
2812 IS
2813
2814 BEGIN
2815 BEGIN
2816 -- NOTE: The steps wll not be having activity Sequence Number ??
2817 -- For WIP steps No deletes
2818 DELETE from GME_BATCH_STEP_CHARGES
2819 WHERE batch_id = pbatch_id
2820 AND BATCHSTEP_ID IN ( SELECT batchstep_id
2821 FROM gme_batch_steps
2822 WHERE batch_id = pbatch_id
2823 AND step_status = 1 );
2824 EXCEPTION
2825 WHEN NO_DATA_FOUND THEN
2826 NULL ;
2827 WHEN OTHERS THEN
2828 return_status := -90;
2829 fnd_msg_pub.add_exc_msg('gmp_aps_writer','Insert_charges');
2830 e_msg := e_msg || ' Delete Charge Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
2831 END ;
2832
2833 -- WIP steps needs to be populated with activity_seq Number
2834 /* mattt remember the case where we have more than one activity involved
2835 in the charge hence you will get two rows back here. we need to just use
2836 the first activity where charegeble resources exist
2837 Hence Minmimum activity seq Number is taken */
2838 -- UPDATE GME_BATCH_STEP_CHARGES
2839 -- set ACTIVITY_SEQUENCE_NUMBER = (SELECT min(gsa.sequence_dependent_ind)
2840 -- FROM gme_batch_step_activities gsa,
2841 -- gme_batch_step_resources gsr,
2842 -- gme_batch_steps gbs
2843 -- WHERE gsa.batchstep_id = GME_BATCH_STEP_CHARGES.batchstep_id
2844 -- AND gsa.batchstep_id = gbs.batchstep_id
2845 -- AND gsa.batchstep_id = gbs.batchstep_id
2846 -- AND gsr.batch_id = gsa.batch_id
2847 -- AND gsr.batchstep_activity_id = gsa.batchstep_activity_id
2848 -- AND gsr.resources = GME_BATCH_STEP_CHARGES.resources
2849 -- AND gbs.batch_id = pbatch_id
2850 -- AND gbs.step_status = 2 )
2851 -- WHERE batch_id = pbatch_id ;
2852
2853 -- For WIP steps No inserts
2854 INSERT INTO GME_BATCH_STEP_CHARGES
2855 (
2856 BATCH_ID,
2857 BATCHSTEP_ID,
2858 ACTIVITY_SEQUENCE_NUMBER,
2859 RESOURCES,
2860 CHARGE_NUMBER,
2861 CHARGE_QUANTITY,
2862 PLAN_START_DATE,
2863 PLAN_CMPLT_DATE,
2864 LAST_UPDATE_LOGIN,
2865 LAST_UPDATED_BY,
2866 LAST_UPDATE_DATE,
2867 CREATION_DATE,
2868 CREATED_BY
2869 )
2870 SELECT gad.wip_entity_id,
2871 gbs.batchstep_id, -- Operation Id
2872 gad.schedule_seq_num, -- Activity Number
2873 crd.resources ,
2874 gad.charge_number,
2875 gad.required_quantity,
2876 gad.start_date,
2877 gad.completion_date,
2878 gad.last_update_login,
2879 gad.last_updated_by,
2880 gad.last_update_date,
2881 gad.creation_date,
2882 gad.created_by
2883 FROM gmp_aps_output_dtl gad,
2884 gmp_aps_output_tbl gao,
2885 gme_batch_steps gbs,
2886 cr_rsrc_dtl crd
2887 WHERE gad.parent_header_id = gao.header_id
2888 AND gad.group_id = gao.process_id
2889 AND gad.organization_id = gao.organization_id
2890 AND gad.wip_entity_id = pbatch_id
2891 AND gao.process_id = pgroup_id
2892 AND gao.header_id = pheader_id
2893 AND gao.batch_id = gad.wip_entity_id
2894 AND gad.load_type = 10
2895 AND gad.resource_id_new = crd.resource_id
2896 AND gad.organization_id = crd.organization_id
2897 AND gbs.batchstep_no = gad.operation_seq_num
2898 AND gbs.batch_id = gao.batch_id
2899 AND gbs.delete_mark = 0
2900 AND gbs.step_status = 1 ; -- Pending steps
2901
2902 return_status := 1 ;
2903 EXCEPTION
2904 WHEN NO_DATA_FOUND THEN
2905 return_status := 1 ;
2906 WHEN OTHERS THEN
2907 return_status := -90;
2908 fnd_msg_pub.add_exc_msg('gmp_aps_writer','Insert_charges');
2909 e_msg := e_msg || ' Insert_charges Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM;
2910 END Insert_charges ;
2911
2912 END gmp_aps_writer;