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