[Home] [Help]
PACKAGE BODY: APPS.GML_BATCH_OM_UTIL
Source
1 PACKAGE body GML_BATCH_OM_UTIL AS
2 /* $Header: GMLOUTLB.pls 120.0 2005/05/25 16:44:49 appldev noship $
3 +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | FILENAME |
9 | GMIURSVS.pls |
10 | |
11 | DESCRIPTION |
12 | This package contains private utilities relating to OPM |
13 | reservation. |
14 | |
15 | |
16 | HISTORY |
17 | Aug-18-2003 Liping Gao Created |
18 +=========================================================================+
19 API Name : GML_BATCH_OM_UTIL
20 Type : Private
21 Function : This package contains Private Utilities procedures used to
22 OPM reservation for a batch.
23 Pre-reqs : N/A
24 Parameters: Per function
25
26 Current Vers : 1.0
27
28 */
29 G_PKG_NAME CONSTANT VARCHAR2(30):='GML_BATCH_OM_UTIL';
30
31 PROCEDURE query_reservation
32 (
33 P_So_line_rec IN GML_BATCH_OM_UTIL.so_line_rec
34 , P_Batch_line_rec IN GML_BATCH_OM_UTIL.batch_line_rec
35 , P_Gme_om_reservation_rec IN OUT NOCOPY GML_BATCH_OM_UTIL.gme_om_reservation_rec
36 , X_return_status OUT NOCOPY VARCHAR2
37 , X_msg_cont OUT NOCOPY NUMBER
38 , X_msg_data OUT NOCOPY VARCHAR2
39 ) IS
40 l_res_rec gml_batch_so_reservations%rowtype;
41 BEGIN
42 x_return_status := FND_API.G_RET_STS_SUCCESS;
43 GMI_RESERVATION_UTIL.Println(' In query reservation');
44 GMI_RESERVATION_UTIL.Println(' In query reservation, so_line_id '|| p_so_line_rec.so_line_id);
45 GMI_RESERVATION_UTIL.Println(' In query reservation, batch_line_id '|| p_batch_line_rec.batch_line_id);
46 GMI_RESERVATION_UTIL.Println(' In query reservation, batch_res '|| p_gme_om_reservation_rec.batch_res_id);
47 IF p_so_line_rec.so_line_id is not null AND p_batch_line_rec.batch_line_id is not null THEN
48 Select *
49 Into l_res_rec
50 From gml_batch_so_reservations
51 Where so_line_id = p_so_line_rec.so_line_id
52 and batch_line_id = p_batch_line_rec.batch_line_id;
53 END IF;
54 IF p_so_line_rec.so_line_id is not null AND p_batch_line_rec.batch_line_id is null THEN
55 Select *
56 Into l_res_rec
57 From gml_batch_so_reservations
58 Where so_line_id = p_so_line_rec.so_line_id;
59 END IF;
60 IF p_so_line_rec.so_line_id is null AND p_batch_line_rec.batch_line_id is not null THEN
61 Select *
62 Into l_res_rec
63 From gml_batch_so_reservations
64 Where batch_line_id = p_batch_line_rec.batch_line_id;
65 END IF;
66 IF p_gme_om_reservation_rec.batch_res_id is not null THEN
67 Select *
68 Into l_res_rec
69 From gml_batch_so_reservations
70 Where batch_res_id = p_gme_om_reservation_rec.batch_res_id;
71 END IF;
72 p_gme_om_reservation_rec.batch_id := l_res_rec.batch_id;
73 p_gme_om_reservation_rec.batch_line_id := l_res_rec.batch_line_id;
74 p_gme_om_reservation_rec.so_line_id := l_res_rec.so_line_id;
75 p_gme_om_reservation_rec.order_id := l_res_rec.order_id;
76 p_gme_om_reservation_rec.delivery_detail_id := l_res_rec.delivery_detail_id;
77 p_gme_om_reservation_rec.mo_line_id := l_res_rec.mo_line_id;
78 p_gme_om_reservation_rec.reserved_qty := l_res_rec.reserved_qty;
79 p_gme_om_reservation_rec.reserved_qty2 := l_res_rec.reserved_qty2;
80 p_gme_om_reservation_rec.uom1 := l_res_rec.qty_uom;
81 p_gme_om_reservation_rec.uom2 := l_res_rec.qty2_uom;
82 p_gme_om_reservation_rec.whse_code := l_res_rec.whse_code;
83 p_gme_om_reservation_rec.organization_id := l_res_rec.organization_id;
84 p_gme_om_reservation_rec.batch_type := l_res_rec.batch_type;
85
86 END query_reservation;
87
88 PROCEDURE insert_reservation
89 (
90 P_Gme_om_reservation_rec IN GML_BATCH_OM_UTIL.gme_om_reservation_rec
91 , X_return_status OUT NOCOPY VARCHAR2
92 , X_msg_cont OUT NOCOPY NUMBER
93 , X_msg_data OUT NOCOPY VARCHAR2
94 ) IS
95 l_batch_res_id NUMBER;
96 BEGIN
97 x_return_status := FND_API.G_RET_STS_SUCCESS;
98 GMI_RESERVATION_UTIL.Println(' In insert reservations');
99 /* insert gme_om_reservation_rec into gml_batch_so_reservations table */
100 select gml_so_reservation_s.nextval
101 into l_batch_res_id
102 from dual;
103 Insert Into gml_batch_so_reservations
104 (
105 batch_res_id
106 , batch_id
107 , batch_line_id
108 , so_line_id
109 , order_id
110 , rule_id
111 , delivery_detail_id
112 , mo_line_id
113 , item_id
114 , reserved_qty
115 , reserved_qty2
116 , qty_uom
117 , qty2_uom
118 , whse_code
119 , organization_id
120 , allocated_ind
121 , batch_type
122 , delete_mark
123 , created_by
124 , creation_date
125 , last_updated_by
126 , last_update_date
127 )
128 Values
129 (
130 l_batch_res_id
131 , p_gme_om_reservation_rec.batch_id
132 , p_gme_om_reservation_rec.batch_line_id
133 , p_gme_om_reservation_rec.so_line_id
134 , p_gme_om_reservation_rec.order_id
135 , p_gme_om_reservation_rec.rule_id
136 , p_gme_om_reservation_rec.delivery_detail_id
137 , p_gme_om_reservation_rec.mo_line_id
138 , p_gme_om_reservation_rec.item_id
139 , p_gme_om_reservation_rec.reserved_qty
140 , p_gme_om_reservation_rec.reserved_qty2
141 , p_gme_om_reservation_rec.uom1
142 , p_gme_om_reservation_rec.uom2
143 , p_gme_om_reservation_rec.whse_code
144 , p_gme_om_reservation_rec.organization_id
145 , 0
146 , p_gme_om_reservation_rec.batch_type
147 , 0
148 , fnd_global.user_id
149 , sysdate
150 , fnd_global.user_id
151 , sysdate
152 );
153 GMI_RESERVATION_UTIL.Println(' In insert reservations, new batch_res_id '||l_batch_res_id);
154 EXCEPTION
155 WHEN FND_API.G_EXC_ERROR THEN
156 /* Get message count and data*/
157 FND_MSG_PUB.count_and_get
158 ( p_count => x_msg_cont
159 , p_data => x_msg_data
160 );
161 GMI_RESERVATION_UTIL.println('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
162 WHEN OTHERS THEN
163 --FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
164 -- , 'check_reservations'
165 -- );
166 --/* Get message count and data*/
167 --FND_MSG_PUB.count_and_get
168 -- ( p_count => x_msg_cont
169 -- , p_data => x_msg_data
170 -- );
171 GMI_RESERVATION_UTIL.println('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
172
173 END insert_reservation;
174
175 PROCEDURE update_reservation
176 (
177 P_Gme_om_reservation_rec IN GML_BATCH_OM_UTIL.gme_om_reservation_rec
178 , X_return_status OUT NOCOPY VARCHAR2
179 , X_msg_cont OUT NOCOPY NUMBER
180 , X_msg_data OUT NOCOPY VARCHAR2
181 ) IS
182 BEGIN
183 x_return_status := FND_API.G_RET_STS_SUCCESS;
184 update gml_batch_so_reservations
185 Set
186 batch_id = p_gme_om_reservation_rec.batch_id
187 , batch_line_id = p_gme_om_reservation_rec.batch_line_id
188 , so_line_id = p_gme_om_reservation_rec.so_line_id
189 , order_id = p_gme_om_reservation_rec.order_id
190 , delivery_detail_id = p_gme_om_reservation_rec.delivery_detail_id
191 , mo_line_id = p_gme_om_reservation_rec.mo_line_id
192 , reserved_qty = p_gme_om_reservation_rec.reserved_qty
193 , reserved_qty2 = p_gme_om_reservation_rec.reserved_qty2
194 , qty_uom = p_gme_om_reservation_rec.uom1
195 , qty2_uom = p_gme_om_reservation_rec.uom2
196 , whse_code = p_gme_om_reservation_rec.whse_code
197 , organization_id = p_gme_om_reservation_rec.organization_id
198 , batch_type = p_gme_om_reservation_rec.batch_type
199 , delete_mark = p_gme_om_reservation_rec.delete_mark
200 , last_updated_by = fnd_global.user_id
201 , last_update_date = sysdate
202 Where batch_res_id = p_gme_om_reservation_rec.batch_res_id;
203 END update_reservation;
204
205 PROCEDURE delete_reservation
206 (
207 P_Batch_res_id IN NUMBER default null
208 , P_Batch_line_id IN NUMBER default null
209 , P_Batch_id IN NUMBER default null
210 , X_return_status OUT NOCOPY VARCHAR2
211 , X_msg_cont OUT NOCOPY NUMBER
212 , X_msg_data OUT NOCOPY VARCHAR2
213 ) IS
214 BEGIN
215 x_return_status := FND_API.G_RET_STS_SUCCESS;
216 IF p_batch_res_id is not null Then
217 update gml_batch_so_reservations
218 set delete_mark = 1
219 Where batch_res_id = p_batch_res_id;
220 END IF;
221 IF p_batch_line_id is not null Then
222 update gml_batch_so_reservations
223 set delete_mark = 1
224 Where batch_line_id = p_batch_line_id;
225 END IF;
226 IF p_batch_id is not null Then
227 update gml_batch_so_reservations
228 set delete_mark = 1
229 Where batch_id = p_batch_id;
230 END IF;
231 END delete_reservation;
232
233 PROCEDURE query_alloc_history
234 (
235 P_alloc_history_rec IN OUT NOCOPY GML_BATCH_OM_UTIL.alloc_history_rec
236 , X_return_status OUT NOCOPY VARCHAR2
237 , X_msg_cont OUT NOCOPY NUMBER
238 , X_msg_data OUT NOCOPY VARCHAR2
239 ) IS
240 l_alloc_rec_id NUMBER;
241 l_history_row gml_batch_so_alloc_history%rowtype;
242 l_history_rec GML_BATCH_OM_UTIL.alloc_history_rec;
243 BEGIN
244 x_return_status := FND_API.G_RET_STS_SUCCESS;
245 IF p_alloc_history_rec.alloc_rec_id is not null THEN
246 Select *
247 Into l_history_row
248 from gml_batch_so_alloc_history
249 Where alloc_rec_id = p_alloc_history_rec.alloc_rec_id;
250 END IF;
251 l_history_rec.Batch_id := l_history_row.batch_id;
252 l_history_rec.Batch_line_id := l_history_row.batch_line_id;
253 l_history_rec.So_line_id := l_history_row.line_id;
254 l_history_rec.Batch_res_id := l_history_row.batch_res_id;
255 l_history_rec.Batch_trans_id := l_history_row.batch_trans_id;
256 l_history_rec.trans_id := l_history_row.trans_id;
257 l_history_rec.Whse_code := l_history_row.whse_code;
258 l_history_rec.Reserved_qty := l_history_row.reserved_qty;
259 l_history_rec.Reserved_qty2 := l_history_row.reserved_qty2;
260 l_history_rec.Trans_um := l_history_row.trans_um;
261 l_history_rec.Trans_um2 := l_history_row.trans_um2;
262 l_history_rec.rule_id := l_history_row.rule_id;
263 l_history_rec.failure_reason := l_history_row.failure_reason;
264 l_history_rec.lot_id := l_history_row.lot_id;
265 l_history_rec.location := l_history_row.location;
266
267 p_alloc_history_rec := l_history_rec;
268
269 END query_alloc_history;
270
271 PROCEDURE insert_alloc_history
272 (
273 P_alloc_history_rec IN GML_BATCH_OM_UTIL.alloc_history_rec
274 , X_return_status OUT NOCOPY VARCHAR2
275 , X_msg_cont OUT NOCOPY NUMBER
276 , X_msg_data OUT NOCOPY VARCHAR2
277 ) IS
278 l_alloc_rec_id NUMBER;
279 BEGIN
280 x_return_status := FND_API.G_RET_STS_SUCCESS;
281 /* insert gme_om_reservation_rec into gml_batch_so_reservations table */
282 GMI_RESERVATION_UTIL.println('inserting alloc history');
283 select gml_so_alloc_history_s.nextval
284 into l_alloc_rec_id
285 from dual;
286 Insert Into gml_batch_so_alloc_history
287 ( Alloc_rec_id
288 , Batch_res_id
289 , Batch_id
290 , Trans_id
291 , line_id
292 , Lot_id
293 , Location
294 , Whse_code
295 , Rule_id
296 , Failure_reason
297 , batch_trans_id
298 , batch_line_id
299 , delete_mark
300 , CREATION_DATE
301 , CREATED_BY
302 , LAST_UPDATED_DATE
303 , LAST_UPDATED_BY
304 )
305 Values
306 (
307 l_alloc_rec_id
308 , p_alloc_history_rec.batch_res_id
309 , p_alloc_history_rec.batch_id
310 , p_alloc_history_rec.trans_id
311 , p_alloc_history_rec.so_line_id
312 , p_alloc_history_rec.lot_id
313 , p_alloc_history_rec.location
314 , p_alloc_history_rec.whse_code
315 , p_alloc_history_rec.rule_id
316 , p_alloc_history_rec.failure_reason
317 , p_alloc_history_rec.batch_trans_id
318 , p_alloc_history_rec.batch_line_id
319 , 0
320 , sysdate
321 , fnd_global.user_id
322 , sysdate
323 , fnd_global.user_id
324 );
325 GMI_RESERVATION_UTIL.println(' alloc_rec_id '|| l_alloc_rec_id);
326
327 EXCEPTION
328 WHEN OTHERS THEN
329 GMI_RESERVATION_UTIL.println('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
330
331 END insert_alloc_history;
332
333 FUNCTION check_reservation
334 (
335 P_Batch_res_id IN NUMBER default null
336 , P_Batch_line_id IN NUMBER default null
337 , P_Batch_id IN NUMBER default null
338 , P_so_line_id IN NUMBER default null
339 , P_delivery_detail_id IN NUMBER default null
340 , X_return_status OUT NOCOPY VARCHAR2
341 , X_msg_cont OUT NOCOPY NUMBER
342 , X_msg_data OUT NOCOPY VARCHAR2
343 ) RETURN BOOLEAN IS
344
345 l_exist Number; -- default := 0;
346
347 BEGIN
348 x_return_status := FND_API.G_RET_STS_SUCCESS;
349 GMI_reservation_Util.PrintLn('Check reservation ');
350 GMI_reservation_Util.PrintLn(' p_batch_res_id '||p_batch_res_id);
351 GMI_reservation_Util.PrintLn(' p_batch_line_id '||p_batch_line_id);
352 GMI_reservation_Util.PrintLn(' p_batch_id '||p_batch_id);
353 GMI_reservation_Util.PrintLn(' p_so_line_id '||p_so_line_id);
354
355 l_exist := 0;
356 IF p_batch_id is not null then
357 Select count(*)
358 Into l_exist
359 From gml_batch_so_reservations
360 Where batch_id = p_batch_id
361 and delete_mark = 0
362 and (reserved_qty > 0 or allocated_ind = 1)
363 ;
364 IF SQL%NOTFOUND or (sqlcode=1403) or l_exist = 0 THEN
365 --GMI_RESERVATION_UTIL.println('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
366 return false;
367 END IF;
368 End if;
369 IF p_batch_line_id is not null then
370 Select count(*)
371 Into l_exist
372 From gml_batch_so_reservations
373 Where batch_line_id = p_batch_line_id
374 and delete_mark = 0
375 and (reserved_qty > 0 or allocated_ind = 1)
376 ;
377 GMI_reservation_Util.PrintLn(' exist ? '||l_exist);
378 IF SQL%NOTFOUND or (sqlcode=1403) or l_exist = 0 THEN
379 return false;
380 END IF;
381 End if;
382 IF p_so_line_id is not null then
383 Select count(*)
384 Into l_exist
385 From gml_batch_so_reservations
386 Where so_line_id = p_so_line_id
387 and delete_mark = 0
388 and (reserved_qty > 0 or allocated_ind = 1)
389 ;
390 IF SQL%NOTFOUND or (sqlcode=1403) or l_exist = 0 THEN
391 return false;
392 END IF;
393 End if;
394 IF p_delivery_detail_id is not null then
395 Select count(*)
396 Into l_exist
397 From gml_batch_so_reservations
398 Where delivery_detail_id = p_delivery_detail_id
399 and delete_mark = 0
400 and (reserved_qty > 0 or allocated_ind = 1)
401 ;
402 IF SQL%NOTFOUND or (sqlcode=1403) or l_exist = 0 THEN
403 return false;
404 END IF;
405 End if;
406
407 return true;
408
409 EXCEPTION
410 WHEN FND_API.G_EXC_ERROR THEN
411 /* Get message count and data*/
412 FND_MSG_PUB.count_and_get
413 ( p_count => x_msg_cont
414 , p_data => x_msg_data
415 );
416 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u EXCEPTION: Expected');
417 WHEN OTHERS THEN
418 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
419 , 'check_reservations'
420 );
421 /* Get message count and data*/
422 --FND_MSG_PUB.count_and_get
423 -- ( p_count => x_msg_cont
424 -- , p_data => x_msg_data
425 -- );
426 GMI_RESERVATION_UTIL.println('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
427 return false;
428 END check_reservation;
429
430 PROCEDURE check_rules
431 (
432 P_Gme_om_config_assign IN GML_BATCH_OM_UTIL.gme_om_config_assign
433 , X_count OUT NOCOPY NUMBER
434 , X_rule_id OUT NOCOPY NUMBER
435 , X_return_status OUT NOCOPY VARCHAR2
436 , X_msg_cont OUT NOCOPY NUMBER
437 , X_msg_data OUT NOCOPY VARCHAR2
438 ) IS
439
440 l_rule_id NUMBER;
441 l_rule_count NUMBER;
442 l_rule_assign_id NUMBER;
443 l_where_clause VARCHAR2(500);
444
445 Cursor get_the_rule IS
446 Select decode(site_use_id, null, 0, site_use_id) site_use_id
447 , decode(customer_id, null, 0, customer_id) customer_id
448 , decode(item_id, null, 0, item_id) item_id
449 , decode(allocation_class, null, ' ', allocation_class) allocation_class
450 , rule_assign_id
451 , rule_id
452 From gml_batch_so_rule_assignments
453 Where whse_code = p_gme_om_config_assign.whse_code
454 and (item_id = p_gme_om_config_assign.item_id
455 or item_id is null )
456 and (allocation_class = p_gme_om_config_assign.allocation_class
457 or allocation_class is null)
458 and (customer_id = p_gme_om_config_assign.customer_id
459 or customer_id is null)
460 and (site_use_id = p_gme_om_config_assign.site_use_id
461 or site_use_id is null)
462 and delete_mark = 0
463 Order by
464 1 desc
465 , 2 desc
466 , 3 desc
467 , 4 desc
468 ;
469
470 l_rule_assign_rec get_the_rule%rowtype;
471
472 TYPE rc IS REF CURSOR;
473 check_rule_assign rc;
474
475 BEGIN
476 x_return_status := FND_API.G_RET_STS_SUCCESS;
477 /* get the most suitable rule first */
478 GMI_reservation_Util.PrintLn('IN check Rule');
479 Open get_the_rule;
480 Fetch get_the_rule
481 Into l_rule_assign_rec.site_use_id
482 , l_rule_assign_rec.customer_id
483 , l_rule_assign_rec.item_id
484 , l_rule_assign_rec.allocation_class
485 , l_rule_assign_id
486 , l_rule_id
487 ;
488 Close get_the_rule;
489
490 /*Select rule_id
491 , rule_assign_id
492 Into l_rule_id
493 , l_rule_assign_id
494 , nvl(item_id, 0)
495 , nvl(allocation_class, '0')
496 , nvl(customer_id, 0)
497 , nvl(site_use_id, 0)
498 From gml_batch_so_rule_assignments
499 Where whse_code = p_gme_om_config_assign.whse_code
500 and (item_id = nvl(p_gme_om_config_assign.item_id,0)
501 or item_id is null )
502 and (allocation_class = nvl(p_gme_om_config_assign.allocation_class,'0')
503 or allocation_class is null)
504 and (customer_id = nvl(p_gme_om_config_assign.customer_id,0)
505 or customer_id is null)
506 and (site_use_id = nvl(p_gme_om_config_assign.site_use_id,0)
507 or site_use_id is null)
508 and delete_mark = 0
509 Order by
510 site_use_id desc
511 , customer_id desc
512 , item_id desc
513 , allocation_class desc
514 ;*/
515
516 GMI_reservation_Util.PrintLn('check Rule, rule_id '||l_rule_id);
517 /*IF get_the_rule%Notfound Then
518 GMI_reservation_Util.PrintLn('Rule is not found');
519 x_count := 0;
520 x_rule_id := null;
521 return;
522 END IF;*/
523
524 /* get this record from the assignment table */
525 /*Select item_id
526 , allocation_class
527 , customer_id
528 , site_use_id
529 Into l_rule_assign_rec.item_id
530 , l_rule_assign_rec.allocation_class
531 , l_rule_assign_rec.customer_id
532 , l_rule_assign_rec.site_use_id
533 From gml_batch_so_rule_assignments
534 Where rule_assign_id = l_rule_assign_id;
535 */
536
537 /* check the uniqueness
538 * construct the where clause
539 */
540 l_where_clause := 'delete_mark = 0 and whse_code = ';
541 l_where_clause := l_where_clause || '''';
542 l_where_clause := l_where_clause || p_gme_om_config_assign.whse_code;
543 l_where_clause := l_where_clause || '''';
544
545 If nvl(l_rule_assign_rec.item_id, 0) <> 0 THEN
546 l_where_clause := l_where_clause || ' And item_id = '|| l_rule_assign_rec.item_id ;
547 else
548 l_where_clause := l_where_clause || ' And item_id is null ';
549 End If;
550 If nvl(l_rule_assign_rec.allocation_class, ' ') <> ' ' THEN
551 l_where_clause := l_where_clause || ' And allocation_class = ';
552 l_where_clause := l_where_clause || '''';
553 l_where_clause := l_where_clause || l_rule_assign_rec.allocation_class ;
554 l_where_clause := l_where_clause || '''';
555 else
556 l_where_clause := l_where_clause || ' And allocation_class is null ';
557 End If;
558 If nvl(l_rule_assign_rec.customer_id, 0) <> 0 THEN
559 l_where_clause := l_where_clause || ' And customer_id = '||l_rule_assign_rec.customer_id ;
560 else
561 l_where_clause := l_where_clause || ' And customer_id is null ';
562 End If;
563 If nvl(l_rule_assign_rec.site_use_id, 0) <> 0 THEN
564 l_where_clause := l_where_clause || ' And site_use_id = '||l_rule_assign_rec.site_use_id ;
565 else
566 l_where_clause := l_where_clause || ' And site_use_id is null ';
567 End If;
568
569 GMI_reservation_Util.PrintLn('check Rule, to check_rule_assign');
570 GMI_reservation_Util.PrintLn('check Rule,where clause '||l_where_clause);
571 OPEN check_rule_assign for
572 'SELECT count(*) FROM gml_batch_so_rule_assignments WHERE '
573 || l_where_clause ;
574 Fetch check_rule_assign Into l_rule_count;
575 Close check_rule_assign;
576
577 GMI_reservation_Util.PrintLn('check Rule, l_rule_count '||l_rule_count);
578 x_count := l_rule_count;
579 x_rule_id := l_rule_id;
580
581 END check_rules;
582
583 PROCEDURE get_rule
584 (
585 P_so_line_rec IN GML_BATCH_OM_UTIL.so_line_rec
586 , P_batch_line_rec IN GML_BATCH_OM_UTIL.batch_line_rec
587 , X_gme_om_rule_rec OUT NOCOPY GML_BATCH_OM_UTIL.gme_om_rule_rec
588 , X_return_status OUT NOCOPY VARCHAR2
589 , X_msg_cont OUT NOCOPY NUMBER
590 , X_msg_data OUT NOCOPY VARCHAR2
591 ) IS
592 l_rule_count NUMBER ; -- default := 0 ;
593 l_rule_id NUMBER ;
594 l_inventory_item_id NUMBER ;
595 l_organization_id NUMBER ;
596 l_so_line_id NUMBER ;
597 i NUMBER ;
598 j NUMBER ;
599 l_rule_rec gml_batch_so_rules%rowtype;
600 l_Gme_om_config_assign GML_BATCH_OM_UTIL.gme_om_config_assign;
601 l_cust_site so_lineTabTyp ;
602 l_so_line_rec GML_BATCH_OM_UTIL.so_line_rec;
603 l_cust_diff NUMBER ;
604 l_site_diff NUMBER ;
605 l_org_diff NUMBER ;
606
607 Cursor get_so_line_ids (p_batch_line_id in NUMBER) is
608 Select distinct so_line_id
609 From gml_batch_so_reservations
610 Where batch_line_id = p_batch_line_id;
611
612 Cursor get_line_info (p_so_line_id IN NUMBER) is
613 Select sold_to_org_id
614 , ship_to_org_id
615 , inventory_item_id
616 , ship_from_org_id
617 From oe_order_lines_all
618 Where line_id = p_so_line_id;
619
620 Cursor get_alloc_class
621 ( p_inv_item_id IN NUMBER
622 , p_org_id IN NUMBER)
623 IS
624 Select ic.alloc_class
625 , ic.item_id
626 From ic_item_mst ic
627 , mtl_system_items mtl
628 Where ic.item_no = mtl.segment1
629 and mtl.inventory_item_id = p_inv_item_id
630 and mtl.organization_id = p_org_id;
631
632 Cursor get_whse_code (p_org_id IN NUMBER) IS
633 Select whse_code
634 From ic_whse_mst
635 Where mtl_organization_id = p_org_id;
636
637 BEGIN
638 x_return_status := FND_API.G_RET_STS_SUCCESS;
639 l_so_line_rec := p_so_line_rec;
640
641 /* assign the gme_om_config_assign record */
642 /* if batch line is passed
643 * check all the orders reserved against the batch line
644 * if conflict, go to next level of hierarchy
645 * inventory item id would be the same
646 */
647 l_cust_site.delete;
648 IF p_batch_line_rec.batch_line_id is not null THEN -- batch line is passed
649 /* get all the so_line_id from batch reservation record */
650 GMI_reservation_Util.PrintLn('get_rule: batch line passed');
651 i:= 1;
652 for so_line_ids in get_so_line_ids (p_batch_line_rec.batch_line_id ) loop
653 l_so_line_id := so_line_ids.so_line_id;
654 GMI_reservation_Util.PrintLn('get_rule: so_line_id '||l_so_line_id);
655 Open get_line_info(l_so_line_id) ;
656 Fetch get_line_info
657 Into
658 l_cust_site(i).customer_id
659 , l_cust_site(i).site_use_id
660 , l_inventory_item_id
661 , l_cust_site(i).organization_id
662 ;
663 Close get_line_info;
664 GMI_reservation_Util.PrintLn('get_rule: batch line customer_id '||l_cust_site(i).customer_id);
665 i := i+1;
666 end loop;
667 i :=1;
668 j := 1;
669 l_cust_diff := 0;
670 l_site_diff := 0;
671 l_org_diff := 0;
672 GMI_reservation_Util.PrintLn('get_rule: cust_site count '|| l_cust_site.count);
673 for i in 1..l_cust_site.count Loop
674 for j in (i+1)..l_cust_site.count Loop
675 GMI_reservation_Util.PrintLn('get_rule: j '|| j);
676 if nvl(l_cust_site(i).customer_id,0) <> nvl(l_cust_site(j).customer_id,0) Then
677 l_cust_diff := 1;
678 END IF;
679 if l_cust_site(i).site_use_id <> l_cust_site(j).site_use_id Then
680 l_site_diff := 1;
681 END IF;
682 if l_cust_site(i).organization_id <> l_cust_site(j).organization_id Then
683 l_org_diff := 1;
684 END IF;
685 end loop;
686 end loop;
687 IF l_site_diff = 1 THEN
688 l_gme_om_config_assign.site_use_id := null;
689 END IF;
690 IF l_cust_diff = 1 THEN
691 l_gme_om_config_assign.customer_id := null;
692 END IF;
693 IF l_org_diff = 1 THEN
694 null;
695 /* GMI_reservation_Util.PrintLn('org is different ');
696 */
697 END IF;
698 l_gme_om_config_assign.customer_id := l_cust_site(1).customer_id;
699 l_gme_om_config_assign.site_use_id := l_cust_site(1).site_use_id;
700 l_organization_id := l_cust_site(1).organization_id;
701 END IF;
702
703 /* if so line is passed, use this so line */
704 IF p_so_line_rec.so_line_id is not null THEN
705 GMI_reservation_Util.PrintLn('get_rule: so_line_rec so_line_id is not null '||p_so_line_rec.so_line_id);
706 l_so_line_id := p_so_line_rec.so_line_id;
707 Open get_line_info(l_so_line_id);
708 Fetch get_line_info
709 Into l_gme_om_config_assign.customer_id
710 , l_gme_om_config_assign.site_use_id
711 , l_so_line_rec.inventory_item_id
712 , l_so_line_rec.ship_from_org_id
713 ;
714 Close get_line_info ;
715 END IF;
716 /* assign l_org_id from the passed value, this way, caller can specify whse
717 * even with batch_line only passed*/
718 IF p_so_line_rec.ship_from_org_id is not null THEN
719 l_so_line_rec.ship_from_org_id := p_so_line_rec.ship_from_org_id;
720 END IF;
721 IF l_so_line_rec.ship_from_org_id is not null THEN
722 l_organization_id := l_so_line_rec.ship_from_org_id;
723 END IF;
724 IF l_so_line_rec.inventory_item_id is not null THEN
725 l_inventory_item_id := l_so_line_rec.inventory_item_id;
726 END IF;
727
728 GMI_reservation_Util.PrintLn('get_rule: customer_id '||l_gme_om_config_assign.customer_id);
729 GMI_reservation_Util.PrintLn('get_rule: site_use_id '||l_gme_om_config_assign.site_use_id);
730 GMI_reservation_Util.PrintLn('get_rule: l_organization_id '||l_organization_id);
731 GMI_reservation_Util.PrintLn('get_rule: l_inventory_item_id '||l_inventory_item_id);
732
733 Open get_alloc_class(l_inventory_item_id
734 , l_organization_id)
735 ;
736 Fetch get_alloc_class
737 Into l_gme_om_config_assign.allocation_class
738 , l_gme_om_config_assign.item_id;
739 Close get_alloc_class;
740 IF l_so_line_rec.whse_code is null THEN
741 Open get_whse_code (l_organization_id);
742 Fetch get_whse_code Into l_gme_om_config_assign.whse_code;
743 Close get_whse_code;
744 ELSE
745 l_gme_om_config_assign.whse_code := l_so_line_rec.whse_code;
746 END IF;
747 GMI_reservation_Util.PrintLn('get_rule: allocation_class '||l_gme_om_config_assign.allocation_class);
748 GMI_reservation_Util.PrintLn('get_rule: item_id '||l_gme_om_config_assign.item_id);
749 GMI_reservation_Util.PrintLn('get_rule: whse_code '||l_gme_om_config_assign.whse_code);
750
751 /* check rules first to see the uniqueness, if not, error */
752 GML_BATCH_OM_UTIL.check_rules
753 (
754 P_Gme_om_config_assign => l_gme_om_config_assign
755 , X_count => l_rule_count
756 , X_rule_id => l_rule_id
757 , X_return_status => x_return_status
758 , X_msg_cont => x_msg_cont
759 , X_msg_data => x_msg_data
760 );
761
762 IF x_return_status <> fnd_api.g_ret_sts_success Then
763 GMI_reservation_Util.PrintLn('OM_UTIL, checking rule failure');
764 --FND_MESSAGE.SET_NAME('GMI','GMI_QTY_RSV_NOT_FOUND');
765 --FND_MESSAGE.Set_Token('WHERE', 'Check rules');
766 --FND_MSG_PUB.ADD;
767 RAISE FND_API.G_EXC_ERROR;
768 END IF;
769 IF l_rule_count = 0 Then
770 GMI_reservation_Util.PrintLn('OM_UTIL, No rule found');
771 --FND_MESSAGE.SET_NAME('GMI','GMI_QTY_RSV_NOT_FOUND');
772 --FND_MESSAGE.Set_Token('WHERE', 'Check rules');
773 --FND_MSG_PUB.ADD;
774 RAISE FND_API.G_EXC_ERROR;
775 END IF;
776 IF l_rule_count > 1 Then
777 GMI_reservation_Util.PrintLn('OM_UTIL, Multiple rules found');
778 --FND_MESSAGE.SET_NAME('GMI','GMI_QTY_RSV_NOT_FOUND');
779 --FND_MESSAGE.Set_Token('WHERE', 'Check rules');
780 --FND_MSG_PUB.ADD;
781 RAISE FND_API.G_EXC_ERROR;
782 END IF;
783 /* get the rule rec if it is unique */
784 Select *
785 Into l_rule_rec
786 From gml_batch_so_rules
787 Where rule_id = l_rule_id;
788 /* fill in the rec type */
789 x_gme_om_rule_rec.Rule_id := l_rule_rec.rule_id;
790 x_gme_om_rule_rec.Rule_name := l_rule_rec.rule_name;
791 x_gme_om_rule_rec.DAYS_BEFORE_SHIP_DATE := l_rule_rec.days_before_ship_date;
792 x_gme_om_rule_rec.DAYS_AFTER_SHIP_DATE := l_rule_rec.days_after_ship_date;
793 x_gme_om_rule_rec.BATCH_STATUS := l_rule_rec.batch_status;
794 x_gme_om_rule_rec.ALLOCATION_TOLERANCE := l_rule_rec.allocation_tolerance;
795 x_gme_om_rule_rec.ALLOCATION_PRIORITY := l_rule_rec.allocation_priority;
796 x_gme_om_rule_rec.AUTO_PICK_CONFIRM := l_rule_rec.auto_pick_confirm;
797 x_gme_om_rule_rec.BATCH_NOTIFICATION := l_rule_rec.batch_notification;
798 x_gme_om_rule_rec.ORDER_NOTIFICATION := l_rule_rec.order_notification;
799 x_gme_om_rule_rec.Enable_FPO := l_rule_rec.enable_fpo;
800 x_gme_om_rule_rec.rule_type := l_rule_rec.rule_type;
801 x_gme_om_rule_rec.batch_type_to_create := l_rule_rec.batch_type_to_create;
802 x_gme_om_rule_rec.batch_creation_user := l_rule_rec.batch_creation_user;
803 x_gme_om_rule_rec.check_availability := l_rule_rec.check_availability;
804 x_gme_om_rule_rec.auto_lot_generation := l_rule_rec.auto_lot_generation;
805 x_gme_om_rule_rec.firmed_ind := l_rule_rec.firmed_ind;
806 x_gme_om_rule_rec.reserve_max_tolerance := l_rule_rec.reserve_max_tolerance;
807 x_gme_om_rule_rec.copy_attachments := l_rule_rec.copy_attachments;
808 x_gme_om_rule_rec.sales_order_attachment := l_rule_rec.sales_order_attachment;
809 x_gme_om_rule_rec.batch_attachment := l_rule_rec.batch_attachment;
810 x_gme_om_rule_rec.batch_creation_notification := l_rule_rec.batch_creation_notification;
811 GMI_reservation_Util.PrintLn('get_rule: rule name '|| x_gme_om_rule_rec.rule_name);
812
813 x_return_status := FND_API.G_RET_STS_SUCCESS;
814
815 EXCEPTION
816 WHEN FND_API.G_EXC_ERROR THEN
817 x_return_status := FND_API.G_RET_STS_ERROR;
818 /* Get message count and data*/
819 FND_MSG_PUB.count_and_get
820 ( p_count => x_msg_cont
821 , p_data => x_msg_data
822 );
823 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u EXCEPTION: Expected');
824 WHEN OTHERS THEN
825 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
826
827 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
828 , 'get_rule'
829 );
830 /* Get message count and data*/
831 FND_MSG_PUB.count_and_get
832 ( p_count => x_msg_cont
833 , p_data => x_msg_data
834 );
835 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u EXCEPTION: Others');
836 GMI_RESERVATION_UTIL.println('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
837
838 END get_Rule;
839
840 END GML_BATCH_OM_UTIL;