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