[Home] [Help]
PACKAGE BODY: APPS.GMO_SWORKBENCH_PVT
Source
1 PACKAGE BODY GMO_SWORKBENCH_PVT AS
2 /* $Header: GMOVSWBB.pls 120.6 2012/01/11 12:10:21 rborpatl ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(40) := 'GMO_SWORKBENCH_PVT';
4 PROCEDURE UPDATE_PLANNING_STATUS
5 (
6 P_API_VERSION IN NUMBER,
7 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
8 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
9 X_MSG_COUNT OUT NOCOPY NUMBER,
10 X_MSG_DATA OUT NOCOPY VARCHAR2,
11 P_RESERVATION_ID IN NUMBER,
12 P_DISPENSE_ID IN NUMBER,
13 P_DISPENSED_DATE IN DATE,
14 P_DISPENSE_TYPE IN VARCHAR2,
15 P_DISPENSE_AREA_ID IN NUMBER,
16 P_DISP_ORG_ID IN NUMBER
17 ) is
18 gmo_dispensing_planning_rec GMO_DISPENSING_PLANNING%ROWTYPE;
19 task_id GMO_DISPENSING_PLANNING.PLANNED_TASK_ID%TYPE;
20 l_count number;
21 l_total_disp_count NUMBER;
22 l_total_unplanned_count NUMBER;
23 L_CREATION_DATE DATE;
24 L_CREATED_BY NUMBER;
25 L_LAST_UPDATE_DATE DATE;
26 L_LAST_UPDATED_BY NUMBER;
27 L_LAST_UPDATE_LOGIN NUMBER;
28 L_DISP_AREA_ID NUMBER;
29
30 --Bug 13545234
31 CURSOR c_planning_rec_details (cp_reservation_id gmo_dispensing_planning_rec.RESERVATION_ID%TYPE,
32 cp_dispense_id gmo_dispensing_planning_rec.DISPENSE_ID%TYPE) IS
33 select *
34 --into gmo_dispensing_planning_rec
35 from gmo_dispensing_planning
36 where reservation_id = cp_reservation_id --P_RESERVATION_ID
37 AND DISPENSE_ID = cp_dispense_id --P_DISPENSE_ID
38 and status ='DISPENSD';
39
40
41 BEGIN
42
43 select count(*) into l_count from gmo_dispensing_planning
44 where reservation_id = P_RESERVATION_ID
45 and status = 'PLANNED';
46 if (l_count > 0) then -- for planned dispense and partial dispenses /MAIN IF/
47 if ( P_DISPENSE_TYPE = 'DISPENSE' ) THEN --DISPENSE IF
48 update gmo_dispensing_planning
49 set
50 status = 'DISPENSD',
51 dispense_id = P_DISPENSE_ID,
52 dispensed_date = P_DISPENSED_DATE
53 where reservation_id = P_RESERVATION_ID
54 and status ='PLANNED';
55 -- for partial dispense
56 elsif ( P_DISPENSE_TYPE = 'PDISPENSE' ) then
57
58 --copy the record
59 select * into gmo_dispensing_planning_rec from gmo_dispensing_planning
60 where reservation_id = P_RESERVATION_ID
61 and status ='PLANNED';
62
63 --change the status to dispensed
64 update gmo_dispensing_planning
65 set
66 status = 'DISPENSD',
67 dispense_id = P_DISPENSE_ID,
68 dispensed_date = P_DISPENSED_DATE
69 where reservation_id = P_RESERVATION_ID
70 and status ='PLANNED';
71
72 GMO_UTILITIES.GET_WHO_COLUMNS
73 (
74 X_CREATION_DATE => L_CREATION_DATE,
75 X_CREATED_BY => L_CREATED_BY,
76 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
77 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
78 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
79 );
80 insert into gmo_dispensing_planning (
81 PLANNED_TASK_ID,
82 RESERVATION_ID,
83 DISPENSE_AREA_ID ,
84 PRIORITY ,
85 PLANNED_DISPENSING_DATE,
86 OPER_ID,
87 STATUS ,
88 DISPENSE_ID ,
89 DISPENSED_DATE,
90 CREATED_BY ,
91 CREATION_DATE ,
92 LAST_UPDATED_BY ,
93 LAST_UPDATE_DATE,
94 LAST_UPDATE_LOGIN
95 )
96 values
97 (
98 GMO_DISPENSING_PLANNING_S.nextval, --- get the next value from sequence
99 P_RESERVATION_ID,
100 gmo_dispensing_planning_rec.DISPENSE_AREA_ID,
101 gmo_dispensing_planning_rec.PRIORITY,
102 gmo_dispensing_planning_rec.PLANNED_DISPENSING_DATE,
103 gmo_dispensing_planning_rec.OPER_ID,
104 --'PDISPENSE', ---signifying partial dispense----
105 gmo_dispensing_planning_rec.status,
106 null,
107 gmo_dispensing_planning_rec.DISPENSED_DATE,
108 --L_CREATED_BY,
109 gmo_dispensing_planning_rec.created_by,
110 L_CREATION_DATE,
111 --L_LAST_UPDATED_BY,
112 gmo_dispensing_planning_rec.last_updated_by,
113 L_LAST_UPDATE_DATE,
114 --L_LAST_UPDATE_LOGIN
115 gmo_dispensing_planning_rec.last_update_login
116 );
117 /*
118 The Changes Made to accommodate Reverse Dispense STARTS-
119 */
120 end if; --- dispense_type /END DISPENSE IF/
121 else --- l_count = 0 for unplanned dispenses /ELSE OF MAIN IF/
122
123 if (P_DISPENSE_TYPE = 'RDISPENSE') THEN --REV DISPENSE IF
124
125 select count(*) into l_total_unplanned_count from gmo_dispensing_planning
126 where reservation_id = P_RESERVATION_ID
127 and status = 'UNPLANNED';
128
129 SELECT Count(*) INTO l_total_disp_count FROM gmo_dispensing_planning
130 WHERE reservation_id = P_RESERVATION_ID
131 AND status='DISPENSD';
132
133 GMO_UTILITIES.GET_WHO_COLUMNS
134 (
135 X_CREATION_DATE => L_CREATION_DATE,
136 X_CREATED_BY => L_CREATED_BY,
137 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
138 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
139 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
140 );
141
142 IF( l_total_unplanned_count = 0) THEN /*Changes made as a part of Bug 13545234*/
143
144 OPEN c_planning_rec_details(P_RESERVATION_ID,P_DISPENSE_ID);
145 LOOP
146 FETCH c_planning_rec_details INTO gmo_dispensing_planning_rec ;
147 EXIT WHEN c_planning_rec_details%NOTFOUND;
148 IF (l_total_disp_count > 0) THEN
149
150 --change the status to dispensed
151
152 update gmo_dispensing_planning
153 set status = 'UNPLANNED',
154 planned_dispensing_date=NULL,
155 dispense_id = NULL,
156 dispensed_date = NULL,
157 LAST_UPDATE_DATE=L_LAST_UPDATE_DATE,
158 last_update_login=L_LAST_UPDATE_LOGIN
159 where dispense_id = P_DISPENSE_ID
160 AND reservation_id = P_RESERVATION_ID
161 and status ='DISPENSD';
162
163 --
164 ELSE
165 insert into gmo_dispensing_planning (
166 PLANNED_TASK_ID,
167 RESERVATION_ID,
168 DISPENSE_AREA_ID ,
169 PRIORITY ,
170 PLANNED_DISPENSING_DATE,
171 OPER_ID,
172 STATUS ,
173 DISPENSE_ID ,
174 DISPENSED_DATE,
175 CREATED_BY ,
176 CREATION_DATE ,
177 LAST_UPDATED_BY ,
178 LAST_UPDATE_DATE,
179 LAST_UPDATE_LOGIN
180 )
181 values
182 (
183 GMO_DISPENSING_PLANNING_S.nextval, --- get the next value from sequence
184 P_RESERVATION_ID,
185 gmo_dispensing_planning_rec.DISPENSE_AREA_ID,
186 gmo_dispensing_planning_rec.PRIORITY,
187 NULL,
188 gmo_dispensing_planning_rec.OPER_ID,
189 --'PDISPENSE', ---signifying partial dispense----
190 'UNPLANNED',
191 null,
192 null,
193 --L_CREATED_BY,
194 gmo_dispensing_planning_rec.created_by,
195 L_CREATION_DATE,
196 --L_LAST_UPDATED_BY,
197 gmo_dispensing_planning_rec.last_updated_by,
198 L_LAST_UPDATE_DATE,
199 --L_LAST_UPDATE_LOGIN
200 gmo_dispensing_planning_rec.last_update_login
201 );
202 END IF; -- end of unplanned count if -- changes end for Bug 13545234
203 END LOOP;
204 CLOSE c_planning_rec_details;
205 END IF; --end rev dis if
206
207 /*
208 The changes made to accommodate reverse dispense ends
209 */
210
211 ELSE -- else of rdispense
212
213 GMO_UTILITIES.GET_WHO_COLUMNS
214 (
215 X_CREATION_DATE => L_CREATION_DATE,
216 X_CREATED_BY => L_CREATED_BY,
217 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
218 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
219 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
220 );
221
222 select count(*) into l_count from gmo_dispensing_planning
223 where reservation_id = P_RESERVATION_ID
224 and status = 'UNPLANNED';
225
226 if( l_count = 0) then
227 insert into gmo_dispensing_planning
228 (
229 PLANNED_TASK_ID,
230 RESERVATION_ID,
231 DISPENSE_AREA_ID,
232 PRIORITY,
233 PLANNED_DISPENSING_DATE,
234 OPER_ID,
235 STATUS,
236 DISPENSE_ID,
237 dispensed_date,
238 CREATED_BY,
239 CREATION_DATE,
240 LAST_UPDATED_BY,
241 LAST_UPDATE_DATE,
242 LAST_UPDATE_LOGIN
243 )
244 values
245 (
246 GMO_DISPENSING_PLANNING_S.nextval, --- get the next value from sequence
247 P_RESERVATION_ID,
248 P_DISPENSE_AREA_ID,
249 'MEDIUM',
250 null,
251 null,
252 'DISPENSD',
253 P_DISPENSE_ID,
254 P_DISPENSED_DATE,
255 L_CREATED_BY,
256 L_CREATION_DATE,
257 L_LAST_UPDATED_BY,
258 L_LAST_UPDATE_DATE,
259 L_LAST_UPDATE_LOGIN
260 );
261
262 ELSE
263
264 update gmo_dispensing_planning
265 set
266 status = 'DISPENSD',
267 dispense_id = P_DISPENSE_ID,
268 dispense_area_id = P_DISPENSE_AREA_ID,
269 dispensed_date = P_DISPENSED_DATE
270 where reservation_id = P_RESERVATION_ID
271 and status ='UNPLANNED';
272
273 end if;
274
275 select DISPENSE_AREA_ID into L_DISP_AREA_ID
276 from gmo_dispense_area_b ar
277 where ar.organization_id = P_DISP_ORG_ID and ar.default_area_ind = 'Y';
278
279 insert into gmo_dispensing_planning
280 (
281 PLANNED_TASK_ID,
282 RESERVATION_ID,
283 DISPENSE_AREA_ID,
284 PRIORITY,
285 PLANNED_DISPENSING_DATE,
286 OPER_ID,
287 STATUS,
288 DISPENSE_ID,
289 dispensed_date,
290 CREATED_BY,
291 CREATION_DATE,
292 LAST_UPDATED_BY,
293 LAST_UPDATE_DATE,
294 LAST_UPDATE_LOGIN
295 )
296 values
297 (
298 GMO_DISPENSING_PLANNING_S.nextval,
299 P_RESERVATION_ID,
300 L_DISP_AREA_ID,
301 'MEDIUM',
302 null,
303 null,
304 'UNPLANNED',
305 null,
306 null,
307 L_CREATED_BY,
308 L_CREATION_DATE,
309 L_LAST_UPDATED_BY,
310 L_LAST_UPDATE_DATE,
311 L_LAST_UPDATE_LOGIN
312 );
313 END IF; --END OF REVERSE DISP
314
315 end if; --- l_count --END OF MAIN IF
316
317 x_return_status := GMO_CONSTANTS_GRP.RETURN_STATUS_SUCCESS;
318 EXCEPTION
319 WHEN OTHERS THEN
320 X_RETURN_STATUS := GMO_CONSTANTS_GRP.RETURN_STATUS_UNEXP_ERROR;
321 FND_MESSAGE.SET_NAME('GMO','GMO_UNEXPECTED_DB_ERR');
322 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
323 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
324 X_MSG_DATA := fnd_message.get;
325 end; ---procedure;
326
327
328
329 FUNCTION GET_TASK_PERCENTAGE(area_id number,max_no_of_tasks number,date_value Date) RETURN Number
330 IS
331 plannedPer number default 0;
332 dispensedPer number default 0;
333 totalPer number default 0;
334
335 begin
336
337 select count(*) into plannedPer from gmo_dispensing_planning
338 where dispense_area_id = area_id
339 and to_char(planned_dispensing_date,'dd-mon-rr') = to_char(date_value,'dd-mon-rr')
340 and status = 'PLANNED'
341 and dispense_id is null;
342
343 select count(*) into dispensedPer from gmo_dispensing_planning
344 where dispense_area_id = area_id
345 and to_char(dispensed_date,'dd-mon-rr') = to_char(date_value,'dd-mon-rr')
346 and dispense_id is not null;
347
348
349
350 totalPer := plannedPer + dispensedPer;
351
352 if(max_no_of_tasks = -1) then
353 return totalPer;
354 else
355 totalPer := (totalPer *100)/max_no_of_tasks;
356 return round(totalPer,2);
357 end if;
358 end;
359
360
361
362
363 FUNCTION GET_TASK_PERCENTAGE(area_id number,max_no_of_tasks number,date_value Date,oper NUMBER) RETURN Number
364 IS
365 plannedPer number default 0;
366 dispensedPer number default 0;
367 totalPer number default 0;
368 begin
369
370 select count(*) into plannedPer from gmo_dispensing_planning
371 where dispense_area_id = area_id
372 and to_char(planned_dispensing_date,'dd-mon-rr') = to_char(date_value,'dd-mon-rr')
373 and OPER_ID = oper
374 and status = 'PLANNED'
375 and dispense_id is null;
376
377 select count(*) into dispensedPer from gmo_dispensing_planning
378 where dispense_area_id = area_id
379 and to_char(dispensed_date,'dd-mon-rr') = to_char(date_value,'dd-mon-rr')
380 and OPER_ID = oper
381 and dispense_id is not null;
382
383
384
385
386 totalPer := plannedPer + dispensedPer;
387 if(max_no_of_tasks = -1) then
388 return totalPer;
389 else
390 totalPer := (totalPer *100)/max_no_of_tasks;
391 return round(totalPer,2);
392 end if;
393 end;
394
395
396 FUNCTION GET_WEEKLY_TASK_PERCENTAGE(area_id number,max_no_of_tasks number,week_start_date Date,week_end_date Date) RETURN Number
397 IS
398
399 plannedPer number default 0;
400 dispensedPer number default 0;
401 totalPer number default 0;
402
403 noofdays number;
404 begin
405 select count(*) into plannedPer from gmo_dispensing_planning
406 where dispense_area_id = area_id
407 and planned_dispensing_date between week_start_date and week_end_date
408 and status = 'PLANNED'
409 and dispense_id is null;
410
411 select count(*) into dispensedPer from gmo_dispensing_planning
412 where dispense_area_id = area_id
413 and dispensed_date between week_start_date and week_end_date
414 and dispense_id is not null;
415
416 noofdays := (week_end_date - week_start_date);
417 noofdays := noofdays + 1;
418
419
420
421 totalPer := plannedPer + dispensedPer;
422 if(max_no_of_tasks = -1) then
423 return totalPer;
424 else
425
426 totalPer := (totalPer *100)/(noofdays * max_no_of_tasks);
427 return round(totalPer,2);
428 end if;
429 end;
430
431 FUNCTION GET_WEEKLY_TASK_PERCENTAGE(area_id number,max_no_of_tasks number,week_start_date Date,week_end_date Date,oper NUMBER) RETURN Number
432 IS
433 plannedPer number default 0;
434 dispensedPer number default 0;
435 totalPer number default 0;
436 noofdays number;
437 begin
438
439 select count(*) into plannedPer from gmo_dispensing_planning
440 where dispense_area_id = area_id
441 and OPER_ID = oper
442 and planned_dispensing_date between week_start_date and week_end_date
443 and status = 'PLANNED'
444 and dispense_id is null;
445
446 select count(*) into dispensedPer from gmo_dispensing_planning
447 where dispense_area_id = area_id
448 and OPER_ID = oper
449 and dispensed_date between week_start_date and week_end_date
450 and dispense_id is not null;
451
452
453 noofdays := (week_end_date - week_start_date);
454 noofdays := noofdays + 1;
455
456
457
458 totalPer := plannedPer + dispensedPer;
459 if(max_no_of_tasks = -1) then
460 return totalPer;
461 else
462 totalPer := (totalPer *100)/(noofdays * max_no_of_tasks);
463 return round(totalPer,2);
464 end if;
465 end;
466
467
468
469
470
471 function get_days(timevalue varchar2)return number
472 as
473 position number;
474 days varchar2(10);
475 begin
476 position := instr(timevalue,':',1,1);
477
478 if (position > 1) then
479
480 days := substr(timevalue,1,position-1);
481
482
483
484 elsif (position = 1 ) then
485 days := '0';
486 else days := timevalue;
487 end if;
488
489 return to_number(days);
490
491 end;
492
493 function get_hours(timevalue varchar2)return number
494 as
495 position1 number;
496 position2 number;
497 hours varchar2(2);
498 begin
499 position1 := instr(timevalue,':',1,1);
500 position2 := instr(timevalue,':',1,2);
501
502
503
504 if (position1 > 1) then
505
506 if(position2 = 0) then
507 hours := substr(timevalue,position1+1);
508 elsif (position2 - position1 > 1) then
509 hours := substr(timevalue,position1+1,position2-position1-1);
510 else hours := '0';
511 end if;
512 else
513 hours := '0';
514 end if;
515
516 return to_number(hours);
517
518 end;
519
520
521 function get_minutes(timevalue varchar2)return number
522 as
523 position number;
524 mins varchar2(2);
525 begin
526
527 position := instr(timevalue,':',1,2);
528
529
530 if (position = 0 ) then
531 mins := '0';
532 else
533 mins := substr(timevalue,position+1);
534 end if;
535
536 return to_number(mins);
537
538 end;
539
540
541
542
543
544 procedure create_material_reservation(
545 p_org_id IN NUMBER
546 ,p_material_detail_id IN NUMBER
547 ,p_resv_qty IN NUMBER DEFAULT NULL
548 ,p_sec_resv_qty IN NUMBER DEFAULT NULL
549 ,p_resv_um IN VARCHAR2 DEFAULT NULL
550 ,p_subinventory IN VARCHAR2 DEFAULT NULL
551 ,p_locator_id IN NUMBER DEFAULT NULL
552 ,p_lot_number IN VARCHAR2 DEFAULT NULL
553 ,x_res_id OUT NOCOPY NUMBER
554 ,x_msg_data OUT NOCOPY VARCHAR2
555 , x_msg_count OUT NOCOPY NUMBER
556 ,x_return_status OUT NOCOPY VARCHAR2
557 )
558 IS
559 l_matl_dtl_rec gme_material_details%ROWTYPE;
560 error_get_item EXCEPTION;
561 CURSOR cur_get_mtl_dtl_rec (v_org_id NUMBER, v_material_detail_id NUMBER)
562 IS
563 SELECT *
564 FROM gme_material_details
565 WHERE material_detail_id = v_material_detail_id
566 AND organization_id = v_org_id;
567 BEGIN
568 OPEN cur_get_mtl_dtl_rec (p_org_id, p_material_detail_id);
569 FETCH cur_get_mtl_dtl_rec INTO l_matl_dtl_rec;
570 CLOSE cur_get_mtl_dtl_rec;
571 IF l_matl_dtl_rec.material_detail_id IS NULL THEN -- not found
572 RAISE error_get_item;
573 END IF;
574 /*ns_debug_call('p_org_id'|| p_org_id||
575 'p_material_detail_id'||p_material_detail_id||
576 'p_resv_qty'||p_resv_qty||
577 'p_sec_resv_qty'||p_sec_resv_qty||
578 'p_resv_um'||p_resv_um||
579 'p_subinventory'||p_subinventory||
580 'p_locator_id'||p_locator_id||
581 'p_lot_number'||p_lot_number
582 );
583 */
584 create_material_reservation
585 (p_matl_dtl_rec => l_matl_dtl_rec
586 ,p_resv_qty => p_resv_qty
587 ,p_sec_resv_qty => p_sec_resv_qty
588 ,p_resv_um => p_resv_um
589 ,p_subinventory => p_subinventory
590 ,p_locator_id => p_locator_id
591 ,p_lot_number => p_lot_number
592 ,x_return_status => x_return_status
593 ,x_msg_count => x_msg_count
594 ,x_msg_data => x_msg_data
595 );
596 --ns_debug_call('in create1'|| x_msg_data );
597 select reservation_id into x_res_id from mtl_reservations RES,gme_material_details GMD where GMD.organization_id =p_org_id and
598 GMD.material_detail_id =p_material_detail_id and RES.DEMAND_SOURCE_HEADER_ID (+) = GMD.BATCH_ID
599 AND RES.DEMAND_SOURCE_LINE_ID(+) = GMD.MATERIAL_DETAIL_ID;
600 EXCEPTION
601 WHEN error_get_item THEN
602 x_return_status := fnd_api.g_ret_sts_error;
603 WHEN OTHERS THEN
604 --ns_debug_call('create Mtl Reservation : ERROR_TEXT :'||SQLERRM);
605 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
606 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
607 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
608 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
609 END create_material_reservation;
610 PROCEDURE create_material_reservation (
611 p_matl_dtl_rec IN gme_material_details%ROWTYPE
612 ,p_resv_qty IN NUMBER DEFAULT NULL
613 ,p_sec_resv_qty IN NUMBER DEFAULT NULL
614 ,p_resv_um IN VARCHAR2 DEFAULT NULL
615 ,p_subinventory IN VARCHAR2 DEFAULT NULL
616 ,p_locator_id IN NUMBER DEFAULT NULL
617 ,p_lot_number IN VARCHAR2 DEFAULT NULL
618 ,x_msg_data OUT NOCOPY VARCHAR2
619 , x_msg_count OUT NOCOPY NUMBER
620 ,x_return_status OUT NOCOPY VARCHAR2)
621 IS
622 l_api_name CONSTANT VARCHAR2 (30) := 'create_material_reservation';
623 l_return_status VARCHAR2 (1);
624 l_msg_count NUMBER;
625 l_msg_data VARCHAR2 (2000);
626 l_qty_reserved NUMBER;
627 l_reservation_id NUMBER;
628 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
629 l_in_serial_num inv_reservation_global.serial_number_tbl_type;
630 l_out_serial_num inv_reservation_global.serial_number_tbl_type;
631 --Bug#4604943
632 invalid_mtl_for_rsrv EXCEPTION;
633 create_resvn_err EXCEPTION;
634 l_get_revision mtl_onhand_quantities%ROWTYPE;
635 CURSOR cur_get_revision (v_org_id NUMBER, v_inventory_item_id NUMBER,v_subinv varchar2,v_lot varchar2,v_locator number)
636 IS
637 SELECT *
638 FROM mtl_onhand_quantities
639 WHERE inventory_item_id = v_inventory_item_id
640 AND organization_id = v_org_id
641 AND subinventory_code = v_subinv
642 AND ((v_lot IS NULL AND lot_number is null) or (lot_number = v_lot))
643 AND ((v_locator IS NULL AND locator_id is null) or (locator_id = v_locator));
644 BEGIN
645 /* IF g_debug <= gme_debug.g_log_procedure THEN
646 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
647 || l_api_name);
648 gme_debug.put_line ('input value p_resv_qty => ' || p_resv_qty);
649 gme_debug.put_line ('input value p_sec_resv_qty => ' || p_sec_resv_qty);
650 gme_debug.put_line ('input value plan_qty => ' || p_matl_dtl_rec.plan_qty);
651 gme_debug.put_line ('input value resv_um => ' || p_resv_um );
652 gme_debug.put_line ('input value release_type => ' || p_matl_dtl_rec.release_type );
653 END IF;*/
654 x_return_status := fnd_api.g_ret_sts_success;
655 --Bug#4604943 Begin validate the batch and material line
656 validate_mtl_for_reservation(
657 p_material_detail_rec => p_matl_dtl_rec
658 ,x_return_status => l_return_status
659 ,x_msg_count => x_msg_count
660 ,x_msg_data => x_msg_data);
661 IF l_return_status <> fnd_api.g_ret_sts_success THEN
662 RAISE invalid_mtl_for_rsrv;
663 END IF;
664 --Bug#4604943 End
665 l_rsv_rec.requirement_date := p_matl_dtl_rec.material_requirement_date;
666 l_rsv_rec.organization_id := p_matl_dtl_rec.organization_id;
667 l_rsv_rec.inventory_item_id := p_matl_dtl_rec.inventory_item_id;
668 l_rsv_rec.demand_source_type_id := gme_common_pvt.g_txn_source_type;
669 l_rsv_rec.demand_source_header_id := p_matl_dtl_rec.batch_id;
670 l_rsv_rec.demand_source_line_id := p_matl_dtl_rec.material_detail_id;
671 l_rsv_rec.reservation_uom_code := NVL (p_resv_um, p_matl_dtl_rec.dtl_um);
672 l_rsv_rec.reservation_quantity :=
673 NVL (p_resv_qty, p_matl_dtl_rec.plan_qty);
674 l_rsv_rec.secondary_reservation_quantity := p_sec_resv_qty;
675 if(p_matl_dtl_rec.revision is not null)then
676 l_rsv_rec.revision := p_matl_dtl_rec.revision;
677 else
678 OPEN cur_get_revision (p_matl_dtl_rec.organization_id , p_matl_dtl_rec.inventory_item_id
679 ,p_subinventory,p_lot_number,p_locator_id);
680 FETCH cur_get_revision INTO l_get_revision;
681 CLOSE cur_get_revision;
682 l_rsv_rec.revision :=l_get_revision.revision;
683 end if;
684 l_rsv_rec.subinventory_code := p_subinventory;
685 l_rsv_rec.locator_id := p_locator_id;
686 l_rsv_rec.lot_number := p_lot_number;
687 l_rsv_rec.lpn_id := NULL;
688 l_rsv_rec.demand_source_name := NULL;
689 l_rsv_rec.demand_source_delivery := NULL;
690 l_rsv_rec.primary_uom_code := NULL;
691 l_rsv_rec.primary_uom_id := NULL;
692 l_rsv_rec.secondary_uom_code := NULL;
693 l_rsv_rec.secondary_uom_id := NULL;
694 l_rsv_rec.reservation_uom_id := NULL;
695 l_rsv_rec.ship_ready_flag := NULL;
696 l_rsv_rec.attribute_category := NULL;
697 l_rsv_rec.attribute1 := NULL;
698 l_rsv_rec.attribute2 := NULL;
699 l_rsv_rec.attribute3 := NULL;
700 l_rsv_rec.attribute4 := NULL;
701 l_rsv_rec.attribute5 := NULL;
702 l_rsv_rec.attribute6 := NULL;
703 l_rsv_rec.attribute7 := NULL;
704 l_rsv_rec.attribute8 := NULL;
705 l_rsv_rec.attribute9 := NULL;
706 l_rsv_rec.attribute10 := NULL;
707 l_rsv_rec.attribute11 := NULL;
708 l_rsv_rec.attribute12 := NULL;
709 l_rsv_rec.attribute13 := NULL;
710 l_rsv_rec.attribute14 := NULL;
711 l_rsv_rec.attribute15 := NULL;
712 l_rsv_rec.subinventory_id := NULL;
713 l_rsv_rec.lot_number_id := NULL;
714 l_rsv_rec.pick_slip_number := NULL;
715 l_rsv_rec.primary_reservation_quantity := NULL;
716 l_rsv_rec.detailed_quantity := NULL;
717 l_rsv_rec.secondary_detailed_quantity := NULL;
718 l_rsv_rec.autodetail_group_id := NULL;
719 l_rsv_rec.external_source_code := NULL;
720 l_rsv_rec.external_source_line_id := NULL;
721 l_rsv_rec.supply_source_type_id :=
722 inv_reservation_global.g_source_type_inv;
723 l_rsv_rec.supply_source_header_id := NULL;
724 l_rsv_rec.supply_source_line_id := NULL;
725 l_rsv_rec.supply_source_name := NULL;
726 l_rsv_rec.supply_source_line_detail := NULL;
727 /* IF (g_debug <= gme_debug.g_log_statement) THEN
728 gme_debug.put_line ('Calling inv_reservation_pub.create_reservation');
729 END IF;*/
730 -- nsinghi Bug5176319. Commented p_force_reservation_flag parameter. As per inv team, onhand could be -ve
731 -- before reservation, and hence this parameter should not be used.
732 inv_reservation_pub.create_reservation
733 (p_api_version_number => 1.0
734 ,p_init_msg_lst => fnd_api.g_false
735 ,x_return_status => l_return_status
736 ,x_msg_count => x_msg_count
737 ,x_msg_data => x_msg_data
738 ,p_rsv_rec => l_rsv_rec
739 ,p_serial_number => l_in_serial_num
740 ,x_serial_number => l_out_serial_num
741 ,p_partial_reservation_flag => fnd_api.g_true
742 -- ,p_force_reservation_flag => fnd_api.g_true
743 ,p_validation_flag => fnd_api.g_true
744 ,x_quantity_reserved => l_qty_reserved
745 ,x_reservation_id => l_reservation_id
746 ,p_partial_rsv_exists => TRUE);
747 --ns_debug_call('in create2'|| x_msg_data );
748 /* IF (g_debug <= gme_debug.g_log_unexpected) THEN
749 gme_debug.put_line ( g_pkg_name
750 || '.'
751 || l_api_name
752 || ' inv_reservation_pub.create_reservation returns status of '
753 || l_return_status
754 || ' for material_detail_id '
755 || p_matl_dtl_rec.material_detail_id
756 || ' qty reserved IS '
757 || l_qty_reserved );
758 END IF;*/
759 IF (l_return_status IN
760 (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) ) THEN
761 RAISE create_resvn_err;
762 END IF;
763 /* IF g_debug <= gme_debug.g_log_procedure THEN
764 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
765 END IF;*/
766 EXCEPTION
767 WHEN create_resvn_err THEN
768 /*IF (g_debug <= gme_debug.g_log_error) THEN
769 gme_debug.put_line
770 ( 'inv_reservation_pub.create_reservation returns '
771 || l_return_status);
772 gme_debug.put_line ('error message is ' || l_msg_data);
773 END IF;*/
774 x_return_status := l_return_status;
775 --Bug#4604943 just pass the actual return status from validate procedure
776 WHEN invalid_mtl_for_rsrv THEN
777 x_return_status := l_return_status;
778 WHEN OTHERS THEN
779 /* IF g_debug <= gme_debug.g_log_unexpected THEN
780 gme_debug.put_line ( 'When others exception in '
781 || g_pkg_name
782 || '.'
783 || l_api_name
784 || ' Error is '
785 || SQLERRM);
786 END IF;*/
787 --fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
788 x_return_status := fnd_api.g_ret_sts_unexp_error;
789 END create_material_reservation;
790 PROCEDURE validate_mtl_for_reservation(
791 p_material_detail_rec IN GME_MATERIAL_DETAILS%ROWTYPE
792 ,x_msg_data OUT NOCOPY VARCHAR2
793 , x_msg_count OUT NOCOPY NUMBER
794 , x_return_status OUT NOCOPY VARCHAR2) IS
795 l_api_name VARCHAR2(30) := 'VALIDATE_MTL_FOR_RESERVATION';
796 l_batch_header_rec gme_batch_header%ROWTYPE;
797 l_step_id NUMBER;
798 l_step_status NUMBER;
799 fetch_failure EXCEPTION;
800 demand_line_error EXCEPTION;
801 batch_status_error EXCEPTION;
802 BEGIN
803 x_return_status := fnd_api.g_ret_sts_success;
804 l_batch_header_rec.batch_id := p_material_detail_rec.batch_id;
805 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header_rec, l_batch_header_rec)) THEN
806 RAISE fetch_failure;
807 END IF;
808 IF l_batch_header_rec.batch_status = gme_common_pvt.g_batch_pending THEN
809 --pending batch just return
810 /* IF g_debug <= gme_debug.g_log_statement THEN
811 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Batch is Pending status');
812 END IF;*/
813 RETURN;
814 ELSIF l_batch_header_rec.batch_status = gme_common_pvt.g_batch_wip THEN
815 /* In WIP Bathes, do not create reservations for automatic and automatic by step if assoc step is not released*/
816 IF p_material_detail_rec.release_type IN (gme_common_pvt.g_mtl_manual_release,gme_common_pvt.g_mtl_incremental_release) THEN
817 /* IF g_debug <= gme_debug.g_log_statement THEN
818 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Batch is in WIP and material line is manual/incremental');
819 END IF;*/
820 RETURN;
821 ELSIF p_material_detail_rec.release_type = gme_common_pvt.g_mtl_autobystep_release THEN
822 /* if automatic by step then check step status */
823 /* IF g_debug <= gme_debug.g_log_statement THEN
824 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Batch is in WIP and material line is Autoby step');
825 END IF;*/
826 IF NOT gme_common_pvt.get_assoc_step(p_material_detail_rec.material_detail_id,l_step_id,l_step_status) THEN
827 RAISE demand_line_error;
828 ELSIF l_step_id IS NOT NULL AND NVL(l_step_status,-1) <> gme_common_pvt.g_step_pending THEN
829 RAISE demand_line_error;
830 END IF;
831 ELSE
832 /* IF g_debug <= gme_debug.g_log_statement THEN
833 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Batch is in WIP and material line is automatic');
834 END IF;*/
835 RAISE demand_line_error;
836 END IF; /*end of validations for WIP Batch*/
837 ELSE
838 RAISE batch_status_error;
839 END IF; /* outer most if */
840 --ns_debug_call('in validate'|| x_msg_data );
841 EXCEPTION
842 WHEN fetch_failure THEN
843 x_return_status := fnd_api.g_ret_sts_error;
844 WHEN demand_line_error THEN
845 gme_common_pvt.log_message('GME_INVALID_DEMAND_LINE');
846 x_msg_data:='GME_INVALID_DEMAND_LINE';
847 x_return_status := fnd_api.g_ret_sts_error;
848 WHEN batch_status_error THEN
849 gme_common_pvt.log_message('GME_INVALID_BATCH_STATUS','PROCESS','RESERVATIONS');
850 x_msg_data:='GME_INVALID_BATCH_STATUS';
851 x_return_status := fnd_api.g_ret_sts_error;
852 WHEN OTHERS THEN
853 /* IF g_debug <= gme_debug.g_log_unexpected THEN
854 gme_debug.put_line ( 'When others exception in '
855 || g_pkg_name
856 || '.'
857 || l_api_name
858 || ' Error is '
859 || SQLERRM);
860 END IF;*/
861 --fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
862 x_return_status := fnd_api.g_ret_sts_unexp_error;
863 END validate_mtl_for_reservation;
864 PROCEDURE update_reservation (
865 p_reservation_id IN NUMBER
866 ,p_revision IN VARCHAR2 DEFAULT NULL
867 ,p_subinventory IN VARCHAR2 DEFAULT NULL
868 ,p_locator_id IN NUMBER DEFAULT NULL
869 ,p_lot_number IN VARCHAR2 DEFAULT NULL
870 ,p_new_qty IN NUMBER DEFAULT NULL
871 ,p_new_sec_qty IN NUMBER DEFAULT NULL
872 ,p_new_uom IN VARCHAR2 DEFAULT NULL
873 ,p_new_date IN DATE DEFAULT NULL
874 ,x_return_status OUT NOCOPY VARCHAR2
875 ,x_msg_data OUT NOCOPY VARCHAR2
876 , x_msg_count OUT NOCOPY NUMBER)
877 IS
878 l_api_name CONSTANT VARCHAR2 (30) := 'update_reservation';
879 l_return_status VARCHAR2 (1);
880 l_msg_count NUMBER;
881 l_msg_data VARCHAR2 (2000);
882 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
883 l_orig_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
884 l_serial_number inv_reservation_global.serial_number_tbl_type;
885 update_resvn_error EXCEPTION;
886 query_resvn_error EXCEPTION;
887 l_get_revision mtl_onhand_quantities%ROWTYPE;
888 CURSOR cur_get_revision (v_org_id NUMBER, v_inventory_item_id NUMBER,v_subinv varchar2,v_lot varchar2,v_locator number)
889 IS
890 SELECT *
891 FROM mtl_onhand_quantities
892 WHERE inventory_item_id = v_inventory_item_id
893 AND organization_id = v_org_id
894 AND subinventory_code = v_subinv
895 AND ((v_lot IS NULL AND lot_number is null) or (lot_number = v_lot))
896 AND ((v_locator IS NULL AND locator_id is null) or (locator_id = v_locator));
897 BEGIN
898 /* IF g_debug <= gme_debug.g_log_procedure THEN
899 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
900 || l_api_name);
901 END IF;*/
902 x_return_status := fnd_api.g_ret_sts_success;
903 query_reservation (p_reservation_id => p_reservation_id
904 ,x_reservation_rec => l_orig_rsv_rec
905 ,x_return_status => l_return_status);
906 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
907 RAISE query_resvn_error;
908 END IF;
909 l_rsv_rec.reservation_id := p_reservation_id;
910 if(p_revision is not null)then
911 l_rsv_rec.revision := p_revision;
912 else
913 OPEN cur_get_revision (l_orig_rsv_rec.organization_id , l_orig_rsv_rec.inventory_item_id
914 ,p_subinventory,p_lot_number,p_locator_id);
915 FETCH cur_get_revision INTO l_get_revision;
916 CLOSE cur_get_revision;
917 l_rsv_rec.revision :=l_get_revision.revision;
918 end if;
919 l_rsv_rec.subinventory_code := p_subinventory;
920 l_rsv_rec.locator_id := p_locator_id;
921 l_rsv_rec.lot_number := p_lot_number;
922 l_rsv_rec.reservation_quantity := p_new_qty;
923 l_rsv_rec.secondary_reservation_quantity := p_new_sec_qty;
924 l_rsv_rec.reservation_uom_code := p_new_uom;
925 l_rsv_rec.requirement_date := p_new_date;
926
927 inv_reservation_pub.update_reservation
928 (p_api_version_number => 1.0
929 ,p_init_msg_lst => fnd_api.g_false
930 ,x_return_status => l_return_status
931 ,x_msg_count => x_msg_count
932 ,x_msg_data => x_msg_data
933 ,p_original_rsv_rec => l_orig_rsv_rec
934 ,p_to_rsv_rec => l_rsv_rec
935 ,p_original_serial_number => l_serial_number
936 ,p_to_serial_number => l_serial_number
937 ,p_validation_flag => fnd_api.g_true
938 ,p_check_availability => fnd_api.g_true);
939
940
941 EXCEPTION
942 WHEN query_resvn_error THEN
943 x_return_status := l_return_status;
944 WHEN update_resvn_error THEN
945 x_return_status := l_return_status;
946 WHEN OTHERS THEN
947 /* IF g_debug <= gme_debug.g_log_unexpected THEN
948 gme_debug.put_line ( 'When others exception in '
949 || g_pkg_name
950 || '.'
951 || l_api_name
952 || ' Error is '
953 || SQLERRM);
954 END IF;*/
955 --fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
956 x_return_status := fnd_api.g_ret_sts_unexp_error;
957 END update_reservation;
958
959 PROCEDURE query_reservation (
960 p_reservation_id IN NUMBER
961 ,x_reservation_rec OUT NOCOPY inv_reservation_global.mtl_reservation_rec_type
962 ,x_return_status OUT NOCOPY VARCHAR2)
963 IS
964 l_api_name CONSTANT VARCHAR2 (30) := 'query_reservation';
965 l_return_status VARCHAR2 (1);
966 l_error_code NUMBER;
967 l_rsv_count NUMBER;
968 l_msg_count NUMBER;
969 l_msg_data VARCHAR2 (2000);
970 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
971 l_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
972 l_serial_number inv_reservation_global.serial_number_tbl_type;
973 update_resvn_error EXCEPTION;
974 BEGIN
975 /*IF g_debug <= gme_debug.g_log_procedure THEN
976 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
977 || l_api_name);
978 END IF;*/
979 x_return_status := fnd_api.g_ret_sts_success;
980 l_rsv_rec.reservation_id := p_reservation_id;
981 /* IF g_debug <= gme_debug.g_log_statement THEN
982 gme_debug.put_line
983 ( g_pkg_name
984 || '.'
985 || l_api_name
986 || ':Calling inv_reservation_pub.query_reservation with reservation_id = '
987 || p_reservation_id);
988 END IF;*/
989 inv_reservation_pub.query_reservation
990 (p_api_version_number => 1.0
991 ,p_init_msg_lst => fnd_api.g_false
992 ,x_return_status => l_return_status
993 ,x_msg_count => l_msg_count
994 ,x_msg_data => l_msg_data
995 ,p_query_input => l_rsv_rec
996 ,p_lock_records => fnd_api.g_false
997 ,p_sort_by_req_date => inv_reservation_global.g_query_no_sort
998 ,p_cancel_order_mode => inv_reservation_global.g_cancel_order_no
999 ,x_mtl_reservation_tbl => l_rsv_tbl
1000 ,x_mtl_reservation_tbl_count => l_rsv_count
1001 ,x_error_code => l_error_code);
1002 /* IF g_debug <= gme_debug.g_log_statement THEN
1003 gme_debug.put_line
1004 ( g_pkg_name
1005 || '.'
1006 || l_api_name
1007 || 'Return status from inv_reservation_pub.query_reservation is '
1008 || l_return_status);
1009 gme_debug.put_line ( g_pkg_name
1010 || '.'
1011 || l_api_name
1012 || 'Error is :'
1013 || l_msg_data);
1014 END IF;*/
1015 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1016 RAISE update_resvn_error;
1017 END IF;
1018 x_reservation_rec := l_rsv_tbl (1);
1019 /* IF g_debug <= gme_debug.g_log_procedure THEN
1020 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1021 END IF;*/
1022 EXCEPTION
1023 WHEN update_resvn_error THEN
1024 x_return_status := l_return_status;
1025 WHEN OTHERS THEN
1026 /* IF g_debug <= gme_debug.g_log_unexpected THEN
1027 gme_debug.put_line ( 'When others exception in '
1028 || g_pkg_name
1029 || '.'
1030 || l_api_name
1031 || ' Error is '
1032 || SQLERRM);
1033 END IF;
1034 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);*/
1035 x_return_status := fnd_api.g_ret_sts_unexp_error;
1036 END query_reservation;
1037
1038
1039
1040
1041 END; ---package;