[Home] [Help]
PACKAGE BODY: APPS.GMP_WPS_WRITER
Source
1 PACKAGE BODY gmp_wps_writer AS
2 /* $Header: GMPWPSWB.pls 120.3 2005/10/13 13:03:43 asatpute noship $ */
3
4 v_cp_enabled BOOLEAN := FALSE;
5
6 /***********************************************************************
7 *
8 * NAME
9 * update_batch_header
10 *
11 * DESCRIPTION
12 * This procedure will update the batch plan start and end date after
13 * once the WPS scheduling engine has completed.
14 * HISTORY
15 * M Craig created
16 * Rajesh Patangya Removed Materail Update R12.0.
17 ************************************************************************/
18 PROCEDURE update_batch_header(
19 pbatch_id IN NUMBER,
20 pstart_date IN NUMBER,
21 pend_date IN NUMBER,
22 plast_update IN NUMBER,
23 phorizon IN NUMBER,
24 puser_id IN NUMBER,
25 plogin_id IN NUMBER,
26 return_status OUT NOCOPY NUMBER)
27
28 IS
29
30 v_batch_id NUMBER;
31 last_update_date DATE;
32 v_start_date DATE;
33 v_end_date DATE;
34
35 CURSOR validate_batch_header IS
36 SELECT
37 gbh.batch_status,
38 gbh.last_update_date
39 FROM
40 gme_batch_header gbh
41 WHERE
42 batch_id = v_batch_id;
43
44 v_batch_status NUMBER;
45 v_last_update_date DATE;
46
47 BEGIN
48
49 v_batch_id := pbatch_id;
50 return_status := 0;
51 v_start_date := wip_datetimes.float_to_DT(pstart_date/1440+phorizon);
52 v_end_date := wip_datetimes.float_to_DT(pend_date/1440+phorizon);
53 v_batch_status := 0;
54
55 OPEN validate_batch_header;
56 FETCH validate_batch_header INTO v_batch_status, v_last_update_date;
57
58 IF validate_batch_header%NOTFOUND THEN
59 return_status := -1;
60 ELSE
61 last_update_date := wip_datetimes.float_to_DT(plast_update/1440+phorizon+1);
62
63 IF v_last_update_date > last_update_date THEN
64 return_status := -2;
65 ELSIF v_batch_status > 2 THEN
66 return_status := 0;
67 ELSE
68 IF v_batch_status = 1 THEN
69 UPDATE
70 gme_batch_header
71 SET
72 plan_start_date = v_start_date,
73 plan_cmplt_date = v_end_date,
74 finite_scheduled_ind = 1,
75 last_update_date = SYSDATE,
76 last_updated_by = puser_id,
77 last_update_login = plogin_id
78 WHERE
79 batch_id = v_batch_id;
80 ELSE
81 UPDATE
82 gme_batch_header
83 SET
84 plan_cmplt_date = v_end_date,
85 finite_scheduled_ind = 1,
86 last_update_date = SYSDATE,
87 last_updated_by = puser_id,
88 last_update_login = plogin_id
89 WHERE
90 batch_id = v_batch_id;
91 END IF;
92 IF SQL%NOTFOUND THEN
93 return_status := -3;
94 END IF;
95 END IF;
96 END IF;
97
98 CLOSE validate_batch_header;
99
100 EXCEPTION
101 WHEN OTHERS THEN
102 return_status := -99;
103 log_message('Failure occured during Batch Header Update: ' || pbatch_id);
104 log_message(sqlerrm);
105
106 END update_batch_header;
107
108 /***********************************************************************
109 *
110 * NAME
111 * update_batch_steps
112 *
113 * DESCRIPTION
114 * This procedure will update the batch step plan start and end date after
115 * the WPS scheduling engine has completed.
116 *
117 * HISTORY
118 * M Craig -- created
119 * Rajesh Patangya -- Modified for Release 12.0
120 * If the item is associated to step and NOT having release_type of
121 * Automatic (0) in the material detail then the step's plan_start_date
122 * will be used for all ingredients (line_type= -1) and plan_cmplt_date
123 * for all products and byproducts (line_type = 1 or 2).
124 * If the item is not associated to step OR Item is associated to step and
125 * having release_type of Automatic (0) in the material detail then the
126 * batch's plan_start_date will be used for all ingredients (line_type= -1)
127 * and plan_cmplt_date for all products and byproducts (line_type = 1 or 2)
128 *
129 ************************************************************************/
130 PROCEDURE update_batch_steps(
131 pbatch_id IN NUMBER,
132 pstep_no IN NUMBER_TBL_TYPE,
133 pstep_id IN NUMBER_TBL_TYPE,
134 pstart_date IN NUMBER_TBL_TYPE,
135 pend_date IN NUMBER_TBL_TYPE,
136 plast_update IN NUMBER_TBL_TYPE,
137 phorizon IN NUMBER,
138 puser_id IN NUMBER,
139 plogin_id IN NUMBER,
140 pnum_rows IN NUMBER,
141 return_status OUT NOCOPY NUMBER)
142
143 IS
144
145 v_batch_id NUMBER ;
146 v_step_no NUMBER ;
147 v_step_id NUMBER ;
148 v_start_date DATE;
149 v_end_date DATE;
150 num_rows NUMBER ;
151 last_update_date DATE;
152
153 CURSOR validate_step IS
154 SELECT
155 gbs.last_update_date,
156 gbs.step_status
157 FROM
158 gme_batch_steps gbs
159 WHERE
160 gbs.batch_id = v_batch_id
161 AND gbs.batchstep_no = v_step_no;
162
163 v_last_update_date DATE;
164 v_step_status NUMBER;
165 v_material_detail_id NUMBER ;
166 v_line_type NUMBER ;
167 m_return_status VARCHAR2(1);
168
169 CURSOR get_step_material IS
170 SELECT material_detail_id, line_type
171 FROM gme_material_details
172 WHERE batch_id = v_batch_id ;
173
174 BEGIN
175
176 return_status := 0;
177 v_batch_id := pbatch_id;
178 v_step_no := 0;
179 v_step_id := 0;
180 num_rows := 0;
181
182 v_step_status := 0;
183 m_return_status := NULL;
184 v_material_detail_id := 0;
185 v_line_type := 0;
186
187 FOR i IN 1..pnum_rows LOOP
188 v_step_no := pstep_no(i);
189 v_step_id := pstep_id(i);
190 OPEN validate_step;
191 FETCH validate_step INTO v_last_update_date, v_step_status;
192
193 IF validate_step%NOTFOUND THEN
194 return_status := -1;
195 ELSIF v_step_status > 1 THEN
196 return_status := -2;
197 ELSE
198 last_update_date := wip_datetimes.float_to_DT(plast_update(i)/1440+phorizon+1);
199 IF v_last_update_date > last_update_date THEN
200 return_status := -2;
201 ELSE
202 v_start_date := wip_datetimes.float_to_DT(pstart_date(i)/1440+phorizon);
203 v_end_date := wip_datetimes.float_to_DT(pend_date(i)/1440+phorizon);
204
205 UPDATE
206 gme_batch_steps
207 SET
208 plan_start_date = v_start_date,
209 plan_cmplt_date = v_end_date,
210 last_update_date = SYSDATE,
211 last_updated_by = puser_id
212 WHERE
213 batch_id = v_batch_id
214 AND batchstep_no = v_step_no;
215
216 IF SQL%NOTFOUND THEN
217 return_status := -2;
218 ELSE
219 /* Update to the charges */
220 BEGIN
221 UPDATE GME_BATCH_STEP_CHARGES
222 SET
223 PLAN_START_DATE = v_start_date,
224 plan_cmplt_date = v_end_date,
225 last_update_date = SYSDATE,
226 last_updated_by = puser_id
227 WHERE
228 batch_id = v_batch_id
229 AND batchstep_id = v_step_id;
230
231 num_rows := num_rows + 1;
232
233 EXCEPTION
234 WHEN NO_DATA_FOUND THEN
235 num_rows := num_rows + 1;
236 WHEN OTHERS THEN
237 return_status := -88;
238 log_message('Failure occured Charge Update: ' || pbatch_id);
239 log_message(sqlerrm);
240 END ;
241
242 END IF;
243 END IF;
244 END IF;
245 CLOSE validate_step;
246
247 EXIT WHEN return_status < 0;
248
249 END LOOP;
250
251 IF return_status = 0 THEN
252
253 -- API will check the step and materail association for the batch and
254 -- decide the materail requirement date, should be step start/End OR
255 -- Batch start/End Date. This will also ensures the further impact on
256 -- Move order and allocations and reservations for the batch.
257
258 OPEN get_step_material;
259 LOOP
260 FETCH get_step_material INTO v_material_detail_id, v_line_type;
261 EXIT WHEN get_step_material%NOTFOUND;
262
263 GME_API_GRP.update_material_date(
264 v_material_detail_id, -- p_material_detail_id,
265 NULL, -- p_material_date
266 m_return_status);
267
268 IF m_return_status = 'S' THEN
269 return_status := 0 ;
270 ELSE
271 -- Basically E and U
272 return_status := -3;
273 EXIT ;
274 END IF;
275 END LOOP;
276 CLOSE get_step_material;
277
278 END IF;
279
280 IF return_status = 0 THEN
281 return_status := num_rows;
282 END IF;
283
284
285 EXCEPTION
286 WHEN OTHERS THEN
287 return_status := -98;
288 log_message('Failure occured during Batch Step Update: ' || pbatch_id);
289 log_message(sqlerrm);
290 END update_batch_steps;
291
292
293 /***********************************************************************
294 *
295 * NAME
296 * update_batch_activities
297 *
298 * DESCRIPTION
299 * This procedure will update the batch step activity plan start and
300 * end date once the WPS scheduling engine has completed.
301 * HISTORY
302 * M Craig
303 * Rajesh Patangya -- Modified for Release 12.0
304 ************************************************************************/
305 PROCEDURE update_batch_activities(
306 pbatch_id IN NUMBER,
307 pstep_id IN NUMBER,
308 pactivity_id IN NUMBER,
309 pstart_date IN NUMBER,
310 pend_date IN NUMBER,
311 plast_update IN NUMBER,
312 phorizon IN NUMBER,
313 puom_hour IN VARCHAR2,
314 puser_id IN NUMBER,
315 plogin_id IN NUMBER,
316 return_status OUT NOCOPY NUMBER)
317
318 IS
319
320 v_activity_id NUMBER;
321 v_step_id NUMBER;
322 v_batch_id NUMBER;
323 found NUMBER;
324 v_trn_start_date DATE;
325 v_trn_end_date DATE;
326 v_start_date DATE;
327 v_end_date DATE;
328 v_hour_uom VARCHAR2(3);
329 v_trans_row gme_resource_txns%ROWTYPE;
330
331 last_update_date DATE;
332
333 CURSOR validate_activity IS
334 SELECT
335 gsa.last_update_date,
336 gbs.step_status
337 FROM
338 gme_batch_steps gbs,
339 gme_batch_step_activities gsa
340 WHERE
341 gbs.batchstep_id = v_step_id
342 AND gbs.batchstep_id = gsa.batchstep_id
343 AND gsa.batchstep_activity_id = v_activity_id;
344
345 v_last_update_date DATE;
346 v_step_status NUMBER;
347
348 -- Activities, its resources and resource transactions with ZERO usage
349 CURSOR get_zero_non_usage IS
350 SELECT
351 gsr.batchstep_resource_id,
352 DECODE(gsr.plan_rsrc_usage, 0, 0, inv_convert.inv_um_convert(-1,38,
353 gsr.plan_rsrc_usage,u2.uom_code,u1.uom_code,NULL,NULL)) plan_rsrc_usage,
354 gsr.offset_interval,
355 gsr.plan_start_date,
356 gsr.plan_cmplt_date
357 FROM
358 gme_batch_step_resources gsr,
359 mtl_units_of_measure u1,
360 mtl_units_of_measure u2
361 WHERE
362 gsr.batchstep_activity_id = v_activity_id
363 AND u1.uom_code = gsr.usage_um
364 AND u2.uom_code = v_hour_uom
365 AND (gsr.plan_rsrc_usage = 0 OR
366 u1.uom_class <> u2.uom_class) ;
367
368 v_zero_res_id NUMBER;
369 v_offset_interval NUMBER;
370 temp_date DATE;
371
372 BEGIN
373
374 return_status := 0;
375 v_activity_id := 0;
376 v_step_id := 0;
377 v_batch_id := 0;
378 found := 0;
379 v_hour_uom := NULL;
380 v_step_status := 0;
381 v_zero_res_id := 0;
382 v_offset_interval := 0;
383 temp_date := NULL;
384
385 v_activity_id := pactivity_id;
386 v_step_id := pstep_id;
387 v_batch_id := pbatch_id;
388
389 OPEN validate_activity;
390 FETCH validate_activity INTO v_last_update_date, v_step_status;
391
392 IF validate_activity%NOTFOUND THEN
393 return_status := -1;
394 ELSIF v_step_status > 1 THEN
395 return_status := -2;
396 ELSE
397 last_update_date := wip_datetimes.float_to_DT(plast_update/1440+phorizon+1);
398 IF v_last_update_date > last_update_date THEN
399 return_status := -3;
400 ELSE
401 v_start_date := wip_datetimes.float_to_DT(pstart_date/1440+phorizon);
402 v_end_date := wip_datetimes.float_to_DT(pend_date/1440+phorizon);
403
404 UPDATE
405 gme_batch_step_activities
406 SET
407 plan_start_date = v_start_date,
408 plan_cmplt_date = v_end_date,
409 last_update_date = SYSDATE,
410 last_updated_by = puser_id
411 WHERE
412 batchstep_activity_id = v_activity_id;
413
414 IF SQL%NOTFOUND THEN
415 return_status := -4;
416 ELSE
417
418 v_hour_uom := puom_hour;
419 FOR v_zero IN get_zero_non_usage LOOP
420
421 v_zero_res_id := v_zero.batchstep_resource_id;
422 v_offset_interval := v_zero.offset_interval/24;
423 found := 0;
424
425 IF v_zero.plan_rsrc_usage = 0 THEN
426
427 temp_date := v_start_date + v_offset_interval;
428 IF temp_date > v_end_date THEN
429 v_offset_interval := 0;
430 END IF;
431 v_trn_start_date := v_start_date + v_offset_interval;
432 v_trn_end_date := v_start_date + v_offset_interval;
433 UPDATE
434 gme_batch_step_resources
435 SET
436 plan_start_date = v_trn_start_date,
437 plan_cmplt_date = v_trn_end_date,
438 last_update_date = SYSDATE,
439 last_updated_by = puser_id
440 WHERE
441 batchstep_resource_id = v_zero_res_id;
442
443 IF SQL%NOTFOUND THEN
444 return_status := -5;
445 ELSE
446 found := 1;
447 END IF;
448 ELSIF v_zero.plan_rsrc_usage < 0 THEN
449
450 v_trn_start_date:= v_start_date;
451 v_trn_end_date:= v_end_date;
452 UPDATE
453 gme_batch_step_resources
454 SET
455 plan_start_date = v_trn_start_date,
456 plan_cmplt_date = v_trn_end_date,
457 last_update_date = SYSDATE,
458 last_updated_by = puser_id
459 WHERE
460 batchstep_resource_id = v_zero_res_id;
461
462 IF SQL%NOTFOUND THEN
463 return_status := -7;
464 ELSE
465 found := 1;
466 END IF;
467 END IF;
468
469 IF found = 1 THEN
470 UPDATE
471 gme_resource_txns
472 SET
473 start_date = v_trn_start_date,
474 end_date = v_trn_end_date,
475 last_update_date = SYSDATE,
476 last_updated_by = puser_id
477 WHERE
478 doc_id = v_batch_id
479 AND doc_type = 'PROD'
480 AND line_id = v_zero_res_id
481 AND completed_ind = 0
482 AND delete_mark = 0;
483
484 IF SQL%NOTFOUND THEN
485 return_status := -6;
486 END IF;
487 END IF;
488 END LOOP;
489 END IF;
490 END IF;
491 END IF;
492
493 CLOSE validate_activity;
494
495 IF return_status >= 0 THEN
496 return_status := 1;
497 END IF;
498
499 EXCEPTION
500 WHEN OTHERS THEN
501 return_status := -97;
502 log_message('Failure occured during Batch Step Activities Update: '
503 || pbatch_id);
504 log_message(sqlerrm);
505
506 END update_batch_activities;
507
508 /***********************************************************************
509 *
510 * NAME
511 * update_batch_resources
512 *
513 * DESCRIPTION
514 * This procedure will update the batch step resource plan start and end date
515 * once the WPS scheduling engine has completed.
516 * HISTORY
517 * M Craig
518 * Rajesh Patangya -- Modified for Release 12.0
519 ************************************************************************/
520 PROCEDURE update_batch_resources(
521 pbatch_id IN NUMBER,
522 pstep_id IN NUMBER_TBL_TYPE,
523 pact_res_id IN NUMBER_TBL_TYPE,
524 pres_usage IN NUMBER_TBL_TYPE,
525 presource_id IN NUMBER_TBL_TYPE,
526 psetup_id IN NUMBER_TBL_TYPE,
527 pstart_date IN NUMBER_TBL_TYPE,
528 pend_date IN NUMBER_TBL_TYPE,
529 plast_update IN NUMBER_TBL_TYPE,
530 pseq_dep_usage IN NUMBER_TBL_TYPE,
531 phorizon IN NUMBER,
532 puom_hour IN VARCHAR2,
533 puser_id IN NUMBER,
534 plogin_id IN NUMBER,
535 pres_rows IN NUMBER,
536 return_status OUT NOCOPY NUMBER,
537 pnew_act_res IN OUT NOCOPY NUMBER_TBL_TYPE)
538
539 IS
540
541 v_batch_id NUMBER ;
542 v_act_resource_id NUMBER ;
543 v_resource_id NUMBER ;
544 v_step_id NUMBER ;
545 v_start_date DATE;
546 v_end_date DATE;
547 v_setup_id NUMBER;
548 last_update_date DATE;
549 v_res_usage NUMBER ;
550 v_seq_dep_usage NUMBER;
551 v_step_act_id NUMBER;
552 v_in_step_res_row gme_batch_step_resources%ROWTYPE; /* Added for NOCOPY */
553 v_step_res_row gme_batch_step_resources%ROWTYPE;
554
555 CURSOR validate_step_resource IS
556 SELECT
557 gsr.organization_id,
558 gsr.last_update_date,
559 gbs.step_status,
560 gsr.resources,
561 crd.resources,
562 gsr.usage_um,
563 gsr.batchstep_activity_id
564 FROM
565 gme_batch_steps gbs,
566 gme_batch_step_resources gsr,
567 cr_rsrc_dtl crd
568 WHERE
569 gbs.batchstep_id = v_step_id
570 AND gbs.batchstep_id = gsr.batchstep_id
571 AND gsr.batchstep_resource_id = v_act_resource_id
572 AND crd.resource_id = v_resource_id
573 AND crd.organization_id = gsr.organization_id ;
574
575 v_activity_id NUMBER;
576 v_organization_id NUMBER;
577 v_resources VARCHAR2(16);
578 v_step_resource_id NUMBER ;
579
580 CURSOR validate_alt_resource IS
581 SELECT
582 gsr.batchstep_resource_id
583 FROM
584 gme_batch_step_resources gsr
585 WHERE
586 gsr.batchstep_activity_id = v_activity_id
587 AND gsr.resources = v_resources
588 AND gsr.organization_id = v_organization_id;
589
590 v_last_update_date DATE;
591 v_step_status NUMBER;
592 v_o_resources VARCHAR2(16);
593 v_n_resources VARCHAR2(16);
594 v_uom_code VARCHAR2(4);
595 row_cnt NUMBER;
596
597 BEGIN
598
599 return_status := 0;
600 v_act_resource_id := 0;
601 v_resource_id := 0;
602 v_step_id := 0;
603 v_setup_id := 0;
604 v_res_usage := 0;
605 v_seq_dep_usage := 0;
606 v_step_act_id := 0;
607
608 v_activity_id := 0;
609 v_resources := NULL;
610 v_step_resource_id := 0 ;
611
612 v_step_status := 0;
613 row_cnt := 0;
614 v_batch_id := pbatch_id;
615
616 -- Rajesh Patangya, Changed for R12.0
617 gme_common_pvt.set_timestamp ;
618 gme_common_pvt.g_timestamp := sysdate ;
619 gme_common_pvt.g_user_ident := puser_id;
620 gme_common_pvt.g_login_id := plogin_id;
621
622 FOR i IN 1..pres_rows LOOP
623 v_act_resource_id := pact_res_id(i);
624 v_in_step_res_row.batchstep_resource_id := -1;
625 v_step_id := pstep_id(i);
626 v_resource_id := presource_id(i);
627 v_res_usage := pres_usage(i)/60;
628
629 OPEN validate_step_resource;
630 FETCH validate_step_resource INTO v_organization_id, v_last_update_date,
631 v_step_status,v_o_resources, v_n_resources, v_uom_code, v_step_act_id;
632
633 IF validate_step_resource%NOTFOUND THEN
634 return_status := -1;
635 ELSIF v_step_status > 1 THEN
636 return_status := -2;
637 ELSE
638 last_update_date := wip_datetimes.float_to_DT(plast_update(i)/1440+phorizon+1);
639 IF v_last_update_date > last_update_date THEN
640 return_status := -3;
641 ELSE
642 v_start_date := wip_datetimes.float_to_DT(pstart_date(i)/1440+phorizon);
643 v_end_date := wip_datetimes.float_to_DT(pend_date(i)/1440+phorizon);
644 v_seq_dep_usage :=
645 inv_convert.inv_um_convert(-1,38,(pseq_dep_usage(i)/60),puom_hour,
646 v_uom_code,NULL,NULL);
647
648 IF v_o_resources = v_n_resources THEN
649 pnew_act_res(i) := pact_res_id(i);
650
651 UPDATE
652 gme_batch_step_resources
653 SET
654 plan_start_date = v_start_date,
655 plan_cmplt_date = v_end_date,
656 sequence_dependent_usage = v_seq_dep_usage,
657 last_update_date = SYSDATE,
658 last_updated_by = puser_id
659 WHERE
660 batchstep_resource_id = v_act_resource_id;
661
662 IF SQL%NOTFOUND THEN
663 return_status := -4;
664 ELSE
665 row_cnt := row_cnt + 1;
666 END IF;
667 ELSE
668
669 v_resources := v_n_resources;
670 v_activity_id := v_step_act_id;
671 OPEN validate_alt_resource;
672 FETCH validate_alt_resource INTO v_step_resource_id;
673
674 IF validate_alt_resource%FOUND THEN
675 return_status := -8;
676 ELSE
677 v_in_step_res_row.batchstep_resource_id := v_act_resource_id;
678 IF NOT GME_BATCH_STEP_RESOURCES_DBL.fetch_row(v_in_step_res_row,
679 v_step_res_row) THEN
680 return_status := -5;
681 ELSE
682
683 DELETE
684 gme_batch_step_resources
685 WHERE
686 batchstep_resource_id = v_act_resource_id;
687
688 IF SQL%NOTFOUND THEN
689 return_status := -6;
690 ELSE
691
692 v_step_res_row.plan_start_date := v_start_date;
693 v_step_res_row.plan_cmplt_date := v_end_date;
694 v_step_res_row.resources := v_n_resources;
695 v_step_res_row.sequence_dependent_usage := v_seq_dep_usage;
696 v_step_res_row.plan_rsrc_usage :=
697 inv_convert.inv_um_convert(-1,38,v_res_usage,puom_hour,
698 v_uom_code,NULL,NULL);
699
700 IF NOT GME_BATCH_STEP_RESOURCES_DBL.insert_row
701 (v_step_res_row, v_in_step_res_row) THEN
702 return_status := -7;
703 ELSE
704 pnew_act_res(i) := v_in_step_res_row.batchstep_resource_id;
705 row_cnt := row_cnt + 1;
706 END IF;
707 END IF;
708 END IF;
709 END IF;
710 END IF;
711 END IF;
712 END IF;
713
714 CLOSE validate_step_resource;
715
716 EXIT WHEN return_status < 0;
717
718 /* delete all resource transactions for current step resource */
719 DELETE
720 gme_resource_txns
721 WHERE
722 doc_id = v_batch_id
723 AND line_id = v_act_resource_id
724 AND doc_type = v_doc_prod
725 AND organization_id = v_organization_id
726 AND completed_ind = 0;
727
728 END LOOP;
729
730 IF return_status >= 0 THEN
731 return_status := row_cnt;
732 END IF;
733
734
735 EXCEPTION
736 WHEN OTHERS THEN
737 return_status := -96;
738 log_message('Failure occured during Batch Step Resources Update: '
739 || pbatch_id);
740 log_message(sqlerrm);
741
742 END update_batch_resources;
743
744 /***********************************************************************
745 *
746 * NAME
747 * update_operation_resources
748 *
749 * DESCRIPTION
750 * This procedure will update the batch step activities, resource and
751 * resource transactions from the operation resource. Sequence
752 * dependent usage and transactions are included
753 * HISTORY
754 * M Craig
755 * Rajesh Patangya -- Modified for Release 12.0
756 ************************************************************************/
757 PROCEDURE update_operation_resources(
758 pbatch_id IN NUMBER,
759 pactivity_id IN NUMBER,
760 pact_start_date IN NUMBER,
761 pact_end_date IN NUMBER,
762 pact_last_update IN NUMBER,
763 pstep_id IN NUMBER_TBL_TYPE,
764 pact_res_id IN NUMBER_TBL_TYPE,
765 presource_id IN NUMBER_TBL_TYPE,
766 presource_usage IN NUMBER_TBL_TYPE,
767 psetup_id IN NUMBER_TBL_TYPE,
768 pres_start_date IN NUMBER_TBL_TYPE,
769 pres_end_date IN NUMBER_TBL_TYPE,
770 plast_update IN NUMBER_TBL_TYPE,
771 pseq_dep_usage IN NUMBER_TBL_TYPE,
772 ptrn_act_res_id IN NUMBER_TBL_TYPE,
773 ptrn_resource_id IN NUMBER_TBL_TYPE,
774 ptrn_rsrc_count IN NUMBER_TBL_TYPE,
775 ptrn_seq_dep IN NUMBER_TBL_TYPE,
776 ptrn_start_date IN NUMBER_TBL_TYPE,
777 ptrn_end_date IN NUMBER_TBL_TYPE,
778 ptrn_instance_id IN NUMBER_TBL_TYPE,
779 phorizon IN NUMBER,
780 puom_hour IN VARCHAR2,
781 puser_id IN NUMBER,
782 plogin_id IN NUMBER,
783 pres_rows IN NUMBER,
784 ptrn_rows IN NUMBER,
785 return_status OUT NOCOPY NUMBER)
786
787 IS
788
789 areturn_status NUMBER;
790 rreturn_status NUMBER;
791 treturn_status NUMBER;
792 v_step_id NUMBER;
793 new_act_res NUMBER_TBL_TYPE;
794
795 BEGIN
796
797 areturn_status := 0;
798 rreturn_status := 0;
799 treturn_status := 0;
800 v_step_id := 0;
801 return_status := 0;
802
803 v_step_id := pstep_id(1);
804 update_batch_activities(
805 pbatch_id,
806 v_step_id,
807 pactivity_id,
808 pact_start_date,
809 pact_end_date,
810 pact_last_update,
811 phorizon,
812 puom_hour,
813 puser_id,
814 plogin_id,
815 areturn_status);
816
817 IF areturn_status < 1 THEN
818 return_status := -1;
819 ELSE
820
821 update_batch_resources(
822 pbatch_id,
823 pstep_id,
824 pact_res_id,
825 presource_usage,
826 presource_id,
827 psetup_id,
828 pres_start_date,
829 pres_end_date,
830 plast_update,
831 pseq_dep_usage,
832 phorizon,
833 puom_hour,
834 puser_id,
835 plogin_id,
836 pres_rows,
837 rreturn_status,
838 new_act_res);
839
840 IF rreturn_status < 1 THEN
841 return_status := -2;
842 ELSE
843
844 update_resource_transactions(
845 pbatch_id,
846 ptrn_act_res_id,
847 ptrn_resource_id,
848 ptrn_instance_id,
849 ptrn_rsrc_count,
850 ptrn_seq_dep,
851 ptrn_start_date,
852 ptrn_end_date,
853 phorizon,
854 puom_hour,
855 puser_id,
856 plogin_id,
857 pres_rows,
858 ptrn_rows,
859 treturn_status,
860 pact_res_id,
861 new_act_res);
862
863 IF treturn_status < 1 THEN
864 return_status := -3;
865 ELSE
866 return_status := areturn_status + rreturn_status + treturn_status;
867 END IF;
868 END IF;
869 END IF;
870
871 EXCEPTION
872 WHEN OTHERS THEN
873 return_status := -95;
874 log_message('Failure occured during Operation Resource Update: '
875 || pbatch_id);
876 log_message(sqlerrm);
877
878 END update_operation_resources;
879
880 /***********************************************************************
881 *
882 * NAME
883 * update_resource_transactions
884 *
885 * DESCRIPTION
886 * This procedure will update batch resource instance transactions
887 * once the WPS scheduling engine has completed.
888 * HISTORY
889 * M Craig
890 * Rajesh Patangya -- Modified for Release 12.0
891 ************************************************************************/
892 PROCEDURE update_resource_transactions(
893 pbatch_id IN NUMBER,
894 pact_res_id IN NUMBER_TBL_TYPE,
895 presource_id IN NUMBER_TBL_TYPE,
896 pinstance_id IN NUMBER_TBL_TYPE,
897 prsrc_count IN NUMBER_TBL_TYPE,
898 pseq_dep_ind IN NUMBER_TBL_TYPE,
899 pstart_date IN NUMBER_TBL_TYPE,
900 pend_date IN NUMBER_TBL_TYPE,
901 phorizon IN NUMBER,
902 puom_hour IN VARCHAR2,
903 puser_id IN NUMBER,
904 plogin_id IN NUMBER,
905 pres_rows IN NUMBER,
906 ptrn_rows IN NUMBER,
907 return_status OUT NOCOPY NUMBER,
908 porig_act_res IN NUMBER_TBL_TYPE,
909 pnew_act_res IN NUMBER_TBL_TYPE)
910
911 IS
912
913 v_batch_id NUMBER;
914 v_resource_id NUMBER;
915 v_act_resource_id NUMBER;
916 v_start_date DATE;
917 v_end_date DATE;
918 v_res_usage NUMBER;
919 temp_date NUMBER;
920 row_cnt NUMBER;
921 v_in_trans_row gme_resource_txns%ROWTYPE; /* Added for NOCOPY */
922 v_trans_row gme_resource_txns%ROWTYPE;
923
924 CURSOR validate_resource IS
925 SELECT
926 crd.schedule_ind,
927 crd.resources,
928 gsr.usage_um,
929 gbh.ORGANIZATION_ID
930 FROM
931 cr_rsrc_dtl crd,
932 gme_batch_step_resources gsr,
933 gme_batch_header gbh
934 WHERE
935 crd.resource_id = v_resource_id
936 AND crd.delete_mark = 0
937 AND gsr.batchstep_resource_id = v_act_resource_id
938 AND gbh.batch_id = v_batch_id
939 AND gbh.ORGANIZATION_ID = gsr.ORGANIZATION_ID
940 AND crd.ORGANIZATION_ID = gsr.ORGANIZATION_ID;
941
942 v_resources VARCHAR2(16);
943 v_schedule_ind NUMBER;
944 v_uom_code VARCHAR2(3);
945 v_ORGANIZATION_ID NUMBER;
946
947 BEGIN
948
949 return_status := 0;
950 v_batch_id := 0;
951 v_resource_id := 0;
952 v_act_resource_id := 0;
953 v_res_usage := 0;
954 temp_date := 0;
955 row_cnt := 0;
956
957 v_schedule_ind := 0;
958 v_batch_id := pbatch_id;
959
960 FOR i IN 1..ptrn_rows LOOP
961 v_resource_id := presource_id(i);
962
963 v_act_resource_id := -1;
964 FOR k IN 1..pres_rows LOOP
965 IF porig_act_res(k) = pact_res_id(i) THEN
966 v_act_resource_id := pnew_act_res(k);
967 END IF;
968 EXIT WHEN porig_act_res(k) = pact_res_id(i);
969 END LOOP;
970
971 OPEN validate_resource;
972 FETCH validate_resource INTO v_schedule_ind, v_resources, v_uom_code,
973 v_ORGANIZATION_ID;
974
975 IF validate_resource%NOTFOUND THEN
976 return_status := -1;
977 ELSE
978 temp_date := (pend_date(i) - pstart_date(i))/60;
979 v_res_usage := inv_convert.inv_um_convert(-1,38,temp_date,
980 puom_hour,v_uom_code,NULL,NULL);
981 v_start_date := wip_datetimes.float_to_DT(pstart_date(i)/1440+phorizon);
982 v_end_date := wip_datetimes.float_to_DT(pend_date(i)/1440+phorizon);
983
984 v_in_trans_row.line_id := v_act_resource_id;
985 v_in_trans_row.ORGANIZATION_ID := v_ORGANIZATION_ID; -- For R12.0
986 v_in_trans_row.doc_type := v_doc_prod;
987 v_in_trans_row.doc_id := v_batch_id;
988 v_in_trans_row.line_type := 0;
989 v_in_trans_row.resources := v_resources;
990 v_in_trans_row.resource_usage := v_res_usage;
991 v_in_trans_row.TRANS_QTY_UM := v_uom_code; -- For R12.0
992 v_in_trans_row.trans_date := SYSDATE;
993 v_in_trans_row.completed_ind := 0;
994 v_in_trans_row.posted_ind := 0;
995 v_in_trans_row.overrided_protected_ind := 'N';
996 v_in_trans_row.start_date := v_start_date;
997 v_in_trans_row.end_date := v_end_date;
998 v_in_trans_row.creation_date := SYSDATE;
999 v_in_trans_row.last_update_date := SYSDATE;
1000 v_in_trans_row.created_by := puser_id;
1001 v_in_trans_row.last_updated_by := puser_id;
1002 v_in_trans_row.last_update_login := plogin_id;
1003 v_in_trans_row.delete_mark := 0;
1004 v_in_trans_row.sequence_dependent_ind := pseq_dep_ind(i);
1005 v_in_trans_row.instance_id := pinstance_id(i);
1006 IF v_in_trans_row.instance_id = 0 OR v_in_trans_row.instance_id = -1 THEN
1007 v_in_trans_row.instance_id := NULL;
1008 END IF;
1009
1010 gme_common_pvt.set_timestamp ;
1011 gme_common_pvt.g_timestamp := sysdate ;
1012 gme_common_pvt.g_user_ident := puser_id;
1013 gme_common_pvt.g_login_id := plogin_id;
1014
1015 FOR j IN 1..prsrc_count(i) LOOP
1016 -- This is not going to change For R12.0
1017 IF NOT gme_resource_txns_dbl.insert_row
1018 (v_in_trans_row, v_trans_row) THEN
1019 return_status := -2;
1020 EXIT;
1021 ELSE
1022 row_cnt := row_cnt + 1;
1023 END IF;
1024 END LOOP;
1025 END IF;
1026
1027 CLOSE validate_resource;
1028 EXIT WHEN return_status < 0;
1029
1030 END LOOP;
1031
1032 IF return_status >= 0 THEN
1033 return_status := row_cnt;
1034 END IF;
1035
1036 EXCEPTION
1037 WHEN OTHERS THEN
1038 return_status := -94;
1039 log_message('Failure occured during Resource Transaction Insert: '
1040 || pbatch_id);
1041 log_message(sqlerrm);
1042
1043 END update_resource_transactions;
1044
1045 /***********************************************************************
1046 *
1047 * NAME
1048 * lock_batch_details
1049 *
1050 * DESCRIPTION
1051 * This procedure will select for update all of the batch details
1052 * except for the transactions.
1053 * HISTORY
1054 * M Craig
1055 * Rajesh Patangya -- Modified for Release 12.0
1056 ************************************************************************/
1057 PROCEDURE lock_batch_details(
1058 pbatch_id IN NUMBER,
1059 return_status OUT NOCOPY NUMBER)
1060
1061 IS
1062
1063 l_batch_id NUMBER;
1064 v_batch_id NUMBER;
1065 found NUMBER;
1066
1067 /* lock the batch header being updated */
1068 CURSOR lock_batch_header IS
1069 SELECT
1070 batch_id
1071 FROM
1072 gme_batch_header
1073 WHERE
1074 batch_id = v_batch_id
1075 FOR UPDATE NOWAIT;
1076
1077 /* lock all of the batch steps for update */
1078 CURSOR lock_batch_steps IS
1079 SELECT
1080 batch_id
1081 FROM
1082 gme_batch_steps
1083 WHERE
1084 batch_id = v_batch_id
1085 FOR UPDATE NOWAIT;
1086
1087 /* lock all of the batch step activities for update */
1088 CURSOR lock_batch_activities IS
1089 SELECT
1090 batch_id
1091 FROM
1092 gme_batch_step_activities
1093 WHERE
1094 batch_id = v_batch_id
1095 FOR UPDATE NOWAIT;
1096
1097 /* lock all of the batch step resources for update */
1098 CURSOR lock_batch_resources IS
1099 SELECT
1100 batch_id
1101 FROM
1102 gme_batch_step_resources
1103 WHERE
1104 batch_id = v_batch_id
1105 FOR UPDATE NOWAIT;
1106
1107 BEGIN
1108
1109 return_status := 0;
1110 l_batch_id := 0;
1111 found := 0;
1112
1113 v_batch_id := pbatch_id;
1114
1115 OPEN lock_batch_header;
1116 LOOP
1117 FETCH lock_batch_header INTO l_batch_id;
1118 EXIT WHEN lock_batch_header%NOTFOUND;
1119 found := 1;
1120 END LOOP;
1121 CLOSE lock_batch_header;
1122 IF found = 0 THEN
1123 return_status := -1;
1124 ELSE
1125 found := 0;
1126 OPEN lock_batch_steps;
1127 LOOP
1128 FETCH lock_batch_steps INTO l_batch_id;
1129 EXIT WHEN lock_batch_steps%NOTFOUND;
1130 found := 1;
1131 END LOOP;
1132 CLOSE lock_batch_steps;
1133 IF found = 0 THEN
1134 return_status := -1;
1135 ELSE
1136 found := 0;
1137 OPEN lock_batch_activities;
1138 LOOP
1139 FETCH lock_batch_activities INTO l_batch_id;
1140 EXIT WHEN lock_batch_activities%NOTFOUND;
1141 found := 1;
1142 END LOOP;
1143 CLOSE lock_batch_activities;
1144 IF found = 0 THEN
1145 return_status := -1;
1146 ELSE
1147 found := 0;
1148 OPEN lock_batch_resources;
1149 LOOP
1150 FETCH lock_batch_resources INTO l_batch_id;
1151 EXIT WHEN lock_batch_resources%NOTFOUND;
1152 found := 1;
1153 END LOOP;
1154 CLOSE lock_batch_resources;
1155 IF found = 0 THEN
1156 return_status := -1;
1157 END IF;
1158 END IF;
1159 END IF;
1160 END IF;
1161
1162 EXCEPTION
1163 WHEN OTHERS THEN
1164 return_status := -1;
1165 log_message('Failure occured during Lock of Production Batch Details: '
1166 || pbatch_id);
1167 log_message(sqlerrm);
1168
1169 END lock_batch_details;
1170
1171 /***********************************************************************
1172 *
1173 * NAME
1174 * log_message
1175 *
1176 * DESCRIPTION
1177 * This procedure will print the the string passed to it
1178 * HISTORY
1179 * Rajesh Patangya
1180 ************************************************************************/
1181 PROCEDURE log_message(
1182 pbuff VARCHAR2)
1183 IS
1184 BEGIN
1185 IF v_cp_enabled THEN
1186 fnd_file.put_line(fnd_file.log, pbuff);
1187 ELSE
1188 /* Bug# 1374205 - Commented the statement dbms_output.put_line - 08/07/00
1189 Uncomment the Following statement for Debugging Purposes */
1190 NULL;
1191 END IF;
1192 END log_message;
1193
1194 /*+==========================================================================+
1195 | FUNCTION NAME
1196 | get_wps_atr
1197 |
1198 | USAGE
1199 | Return ATR quantity at org, item level using Item tree
1200 |
1201 | ARGUMENTS
1202 | p_api_version API Version of this procedure. Current version is 1.0
1203 | p_init_msg_list fnd_api.g_false or fnd_api.g_true is passed as input to
1204 | determine whether to Initialize message list or not
1205 | x_return_status Returns the status to indicate success or failure of execution
1206 | x_msg_count Returns number of error message in the error message stack in
1207 | case of failure
1208 | x_msg_data Returns the error message in case of failure
1209 |
1210 | RETURNS
1211 | returns via x_ OUT
1212 |
1213 | HISTORY
1214 | Created 11-Jul-2005 Rajesh Patangya
1215 +==========================================================================+ */
1216 FUNCTION get_wps_atr(
1217 p_organization_id IN NUMBER,
1218 p_inventory_item_id IN NUMBER ) RETURN NUMBER IS
1219
1220 p_demand_source_type_id NUMBER ;
1221 p_tree_mode INTEGER ;
1222 p_api_version_number NUMBER ;
1223 p_init_msg_lst VARCHAR2(2000) ;
1224 p_is_serial_control BOOLEAN;
1225 p_demand_source_header_id NUMBER ;
1226 p_demand_source_line_id NUMBER ;
1227 p_demand_source_name VARCHAR2(50) ;
1228 p_lot_expiration_date DATE ;
1229 l_api_name CONSTANT VARCHAR2 (30) := 'QUERY_QUANTITIES';
1230 l_is_revision_control BOOLEAN;
1231 l_is_lot_control BOOLEAN;
1232 x_return_status VARCHAR2(1) ;
1233 p_onhand_source NUMBER ;
1234 p_grade_code VARCHAR2(150);
1235 p_revision VARCHAR2(30);
1236 p_lot_number VARCHAR2(80);
1237 p_subinventory_code VARCHAR2(30);
1238 p_locator_id NUMBER ;
1239 x_qoh NUMBER ;
1240 x_rqoh NUMBER ;
1241 x_qr NUMBER ;
1242 x_qs NUMBER ;
1243 x_att NUMBER ;
1244 x_atr NUMBER ;
1245 x_sqoh NUMBER ;
1246 x_srqoh NUMBER ;
1247 x_sqr NUMBER ;
1248 x_sqs NUMBER ;
1249 x_satt NUMBER ;
1250 x_satr NUMBER ;
1251 X_msg_data VARCHAR2(2000);
1252 x_msg_count NUMBER(5);
1253 p_cost_group_id NUMBER;
1254 p_transfer_locator_id NUMBER;
1255 p_lpn_id NUMBER;
1256 p_transfer_subinventory_code VARCHAR2(30);
1257
1258 BEGIN
1259 p_lpn_id := NULL;
1260 p_transfer_locator_id := NULL;
1261 p_cost_group_id := NULL;
1262 p_transfer_subinventory_code := NULL;
1263 p_demand_source_type_id := gme_common_pvt.g_txn_source_type ;
1264 X_msg_data := NULL;
1265 x_msg_count := NULL;
1266 x_return_status := fnd_api.g_ret_sts_success;
1267 p_api_version_number := 1 ;
1268 p_init_msg_lst := fnd_api.g_false ;
1269 p_tree_mode := 1 ; -- for Reservations
1270 p_is_serial_control := FALSE;
1271
1272 p_demand_source_header_id := -9999 ;
1273 p_demand_source_line_id := -9999 ;
1274 p_demand_source_name := NULL ;
1275 p_lot_expiration_date := NULL ;
1276
1277 l_is_revision_control := FALSE;
1278 l_is_lot_control := FALSE;
1279 p_onhand_source := inv_quantity_tree_pvt.g_all_subs ;
1280 p_grade_code := NULL;
1281 p_transfer_subinventory_code := NULL;
1282 p_cost_group_id := NULL;
1283 p_lpn_id := NULL;
1284 p_transfer_locator_id := NULL;
1285 p_revision := NULL;
1286 p_lot_number := NULL;
1287 p_subinventory_code := NULL;
1288 p_locator_id := NULL;
1289
1290 inv_quantity_tree_pub.query_quantities
1291 (p_api_version_number => p_api_version_number
1292 ,p_init_msg_lst => p_init_msg_lst
1293 ,x_return_status => x_return_status
1294 ,x_msg_count => x_msg_count
1295 ,x_msg_data => x_msg_data
1296 ,p_organization_id => p_organization_id
1297 ,p_inventory_item_id => p_inventory_item_id
1298 ,p_tree_mode => p_tree_mode
1299 ,p_is_revision_control => l_is_revision_control
1300 ,p_is_lot_control => l_is_lot_control
1301 ,p_is_serial_control => p_is_serial_control
1302 ,p_grade_code => p_grade_code
1303 ,p_demand_source_type_id => p_demand_source_type_id
1304 ,p_demand_source_header_id => p_demand_source_header_id
1305 ,p_demand_source_line_id => p_demand_source_line_id
1306 ,p_demand_source_name => p_demand_source_name
1307 ,p_lot_expiration_date => p_lot_expiration_date
1308 ,p_revision => p_revision
1309 ,p_lot_number => p_lot_number
1310 ,p_subinventory_code => p_subinventory_code
1311 ,p_locator_id => p_locator_id
1312 ,p_onhand_source => p_onhand_source
1313 ,x_qoh => x_qoh
1314 ,x_rqoh => x_rqoh
1315 ,x_qr => x_qr
1316 ,x_qs => x_qs
1317 ,x_att => x_att
1318 ,x_atr => x_atr
1319 ,x_sqoh => x_sqoh
1320 ,x_srqoh => x_srqoh
1321 ,x_sqr => x_sqr
1322 ,x_sqs => x_sqs
1323 ,x_satt => x_satt
1324 ,x_satr => x_satr
1325 ,p_transfer_subinventory_code => p_transfer_subinventory_code
1326 ,p_cost_group_id => p_cost_group_id
1327 ,p_lpn_id => p_lpn_id
1328 ,p_transfer_locator_id => p_transfer_locator_id);
1329
1330 RETURN x_atr ;
1331
1332 EXCEPTION
1333 WHEN fnd_api.g_exc_error THEN
1334 x_return_status := fnd_api.g_ret_sts_error;
1335 fnd_msg_pub.count_and_get (p_count => x_msg_count
1336 ,p_data => x_msg_data);
1337 RETURN -1;
1338 WHEN fnd_api.g_exc_unexpected_error THEN
1339 x_return_status := fnd_api.g_ret_sts_unexp_error;
1340 fnd_msg_pub.count_and_get (p_count => x_msg_count
1341 ,p_data => x_msg_data);
1342 RETURN -2;
1343 WHEN OTHERS THEN
1344 x_return_status := fnd_api.g_ret_sts_unexp_error;
1345 fnd_msg_pub.count_and_get (p_count => x_msg_count
1346 ,p_data => x_msg_data);
1347 RETURN -3;
1348 END get_wps_atr;
1349
1350
1351 /*+==========================================================================+
1352 | FUNCTION NAME
1353 | get_wps_onhand
1354 |
1355 | USAGE
1356 | Return onhand quantity at org, item level using Item tree
1357 |
1358 | ARGUMENTS
1359 | p_api_version API Version of this procedure. Current version is 1.0
1360 | p_init_msg_list fnd_api.g_false or fnd_api.g_true is passed as input to
1361 | determine whether to Initialize message list or not
1362 | x_return_status Returns the status to indicate success or failure of execution
1363 | x_msg_count Returns number of error message in the error message stack in
1364 | case of failure
1365 | x_msg_data Returns the error message in case of failure
1366 |
1367 | RETURNS
1368 | returns via x_ OUT
1369 |
1370 | HISTORY
1371 | Created 02-Aug-2005 Rajesh Patangya
1372 +==========================================================================+ */
1373 FUNCTION get_wps_onhand(
1374 p_organization_id IN NUMBER,
1375 p_inventory_item_id IN NUMBER ) RETURN NUMBER IS
1376
1377 p_demand_source_type_id NUMBER ;
1378 p_tree_mode INTEGER ;
1379 p_api_version_number NUMBER ;
1380 p_init_msg_lst VARCHAR2(2000) ;
1381 p_is_serial_control BOOLEAN;
1382 p_demand_source_header_id NUMBER ;
1383 p_demand_source_line_id NUMBER ;
1384 p_demand_source_name VARCHAR2(50) ;
1385 p_lot_expiration_date DATE ;
1386 l_api_name CONSTANT VARCHAR2 (30) := 'QUERY_QUANTITIES';
1387 l_is_revision_control BOOLEAN;
1388 l_is_lot_control BOOLEAN;
1389 x_return_status VARCHAR2(1) ;
1390 p_onhand_source NUMBER ;
1391 p_grade_code VARCHAR2(150);
1392 p_revision VARCHAR2(30);
1393 p_lot_number VARCHAR2(80);
1394 p_subinventory_code VARCHAR2(30);
1395 p_locator_id NUMBER ;
1396 x_qoh NUMBER ;
1397 x_rqoh NUMBER ;
1398 x_qr NUMBER ;
1399 x_qs NUMBER ;
1400 x_att NUMBER ;
1401 x_atr NUMBER ;
1402 x_sqoh NUMBER ;
1403 x_srqoh NUMBER ;
1404 x_sqr NUMBER ;
1405 x_sqs NUMBER ;
1406 x_satt NUMBER ;
1407 x_satr NUMBER ;
1408 X_msg_data VARCHAR2(2000);
1409 x_msg_count NUMBER(5);
1410 p_cost_group_id NUMBER;
1411 p_transfer_locator_id NUMBER;
1412 p_lpn_id NUMBER;
1413 p_transfer_subinventory_code VARCHAR2(30);
1414 TYPE gmp_cursor_typ IS REF CURSOR;
1415 cur_get_onhand gmp_cursor_typ;
1416
1417
1418 BEGIN
1419 -- synonyms used in this program
1420 -- qoh quantity on hand
1421 -- rqoh reservable quantity on hand
1422 -- qr quantity reserved
1423 -- qs quantity suggested
1424 -- att available to transact
1425 -- atr available to reserve
1426 -- sqoh secondary quantity on hand R12
1427 -- srqoh secondary reservable quantity on hand R12
1428 -- sqr secondary quantity reserved R12
1429 -- sqs secondare quantity suggested R12
1430 -- satt secondary available to transact R12
1431 -- satr secondary available to reserve R12
1432
1433 ----- p_lpn_id := NULL;
1434 ----- p_transfer_locator_id := NULL;
1435 ----- p_cost_group_id := NULL;
1436 ----- p_transfer_subinventory_code := NULL;
1437 ----- p_demand_source_type_id := gme_common_pvt.g_txn_source_type ;
1438 ----- X_msg_data := NULL;
1439 ----- x_msg_count := NULL;
1440 ----- x_return_status := fnd_api.g_ret_sts_success;
1441 ----- p_api_version_number := 1 ;
1442 ----- p_init_msg_lst := fnd_api.g_false ;
1443
1444 /* Transaction Mode will return on hand quantity and avaialble to transact
1445 quantity, This onhand quantity does not consider the material statuses,
1446 If material status controlled on hand is required, then select on hand
1447 quantity from here and select all the transactions to come up with the
1448 desired quantity e.g. if the onhand quantity only for WIP issue
1449 enabled will not be returned from this tree */
1450 /* On hand = ATT + Reserved Quantity */
1451 /* The items shall be revision or serial or lot controlled */
1452 /* The items will be restricted to a particular subinventory or loactor */
1453 /* Subinventory and locator (stock locator) have master detail relationship */
1454 /* GME looks for most restrictive material status, i.e. if at any one level
1455 of material status is disallowed means it is disallowed, before allowing the
1456 item to be used in a batch */
1457
1458 ----- p_tree_mode := 2 ; -- in transaction Mode
1459 ----- p_is_serial_control := FALSE;
1460 -----
1461 ----- p_demand_source_header_id := -9999 ;
1462 ----- p_demand_source_line_id := -9999 ;
1463 ----- p_demand_source_name := NULL ;
1464 ----- p_lot_expiration_date := NULL ;
1465 -----
1466 ----- l_is_revision_control := FALSE;
1467 ----- l_is_lot_control := FALSE;
1468 ----- p_onhand_source := inv_quantity_tree_pvt.g_all_subs ;
1469 ----- p_grade_code := NULL;
1470 ----- p_transfer_subinventory_code := NULL;
1471 ----- p_cost_group_id := NULL;
1472 ----- p_lpn_id := NULL;
1473 ----- p_transfer_locator_id := NULL;
1474 ----- p_revision := NULL;
1475 ----- p_lot_number := NULL;
1476 ----- p_subinventory_code := NULL;
1477 ----- p_locator_id := NULL;
1478 -----
1479 ----- inv_quantity_tree_pub.query_quantities
1480 ----- (p_api_version_number => p_api_version_number
1481 ----- ,p_init_msg_lst => p_init_msg_lst
1482 ----- ,x_return_status => x_return_status
1483 ----- ,x_msg_count => x_msg_count
1484 ----- ,x_msg_data => x_msg_data
1485 ----- ,p_organization_id => p_organization_id
1486 ----- ,p_inventory_item_id => p_inventory_item_id
1487 ----- ,p_tree_mode => p_tree_mode
1488 ----- ,p_is_revision_control => l_is_revision_control
1489 ----- ,p_is_lot_control => l_is_lot_control
1490 ----- ,p_is_serial_control => p_is_serial_control
1491 ----- ,p_grade_code => p_grade_code
1492 ----- ,p_demand_source_type_id => p_demand_source_type_id
1493 ----- ,p_demand_source_header_id => p_demand_source_header_id
1494 ----- ,p_demand_source_line_id => p_demand_source_line_id
1495 ----- ,p_demand_source_name => p_demand_source_name
1496 ----- ,p_lot_expiration_date => p_lot_expiration_date
1497 ----- ,p_revision => p_revision
1498 ----- ,p_lot_number => p_lot_number
1499 ----- ,p_subinventory_code => p_subinventory_code
1500 ----- ,p_locator_id => p_locator_id
1501 ----- ,p_onhand_source => p_onhand_source
1502 ----- ,x_qoh => x_qoh
1503 ----- ,x_rqoh => x_rqoh
1504 ----- ,x_qr => x_qr
1505 ----- ,x_qs => x_qs
1506 ----- ,x_att => x_att
1507 ----- ,x_atr => x_atr
1508 ----- ,x_sqoh => x_sqoh
1509 ----- ,x_srqoh => x_srqoh
1510 ----- ,x_sqr => x_sqr
1511 ----- ,x_sqs => x_sqs
1512 ----- ,x_satt => x_satt
1513 ----- ,x_satr => x_satr
1514 ----- ,p_transfer_subinventory_code => p_transfer_subinventory_code
1515 ----- ,p_cost_group_id => p_cost_group_id
1516 ----- ,p_lpn_id => p_lpn_id
1517 ----- ,p_transfer_locator_id => p_transfer_locator_id);
1518 -----
1519 OPEN cur_get_onhand FOR
1520 SELECT sum(quantity)
1521 FROM gmp_nettable_onhands_v
1522 WHERE organization_id = p_organization_id
1523 AND inventory_item_id = p_inventory_item_id ;
1524
1525 FETCH cur_get_onhand into x_qoh ;
1526
1527 CLOSE cur_get_onhand ;
1528
1529 RETURN x_qoh ;
1530
1531 EXCEPTION
1532 WHEN fnd_api.g_exc_error THEN
1533 x_return_status := fnd_api.g_ret_sts_error;
1534 fnd_msg_pub.count_and_get (p_count => x_msg_count
1535 ,p_data => x_msg_data);
1536 RETURN -1;
1537 WHEN fnd_api.g_exc_unexpected_error THEN
1538 x_return_status := fnd_api.g_ret_sts_unexp_error;
1539 fnd_msg_pub.count_and_get (p_count => x_msg_count
1540 ,p_data => x_msg_data);
1541 RETURN -2;
1542 WHEN OTHERS THEN
1543 x_return_status := fnd_api.g_ret_sts_unexp_error;
1544 fnd_msg_pub.count_and_get (p_count => x_msg_count
1545 ,p_data => x_msg_data);
1546 RETURN -3;
1547 END get_wps_onhand;
1548
1549 END;