DBA Data[Home] [Help]

PACKAGE BODY: APPS.GMP_APS_WRITER

Source


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