[Home] [Help]
PACKAGE BODY: APPS.GMI_ALLOCATE_INVENTORY_PVT
Source
1 PACKAGE BODY GMI_ALLOCATE_INVENTORY_PVT AS
2 /* $Header: GMIVALIB.pls 120.1 2005/08/30 08:23:33 nchekuri noship $ */
3
4 /* Global variables */
5 G_PKG_NAME CONSTANT VARCHAR2(30):='GMI_ALLOCATE_INVENTORY_PVT';
6
7
8 /* +=========================================================================+
9 | PROCEDURE NAME |
10 | Allocate Line |
11 | |
12 | |
13 | DESCRIPTION |
14 | Analyze available inventory at the most detailed level to locate |
15 | stock suitable for allocation to the current shipment/order line. |
16 | Allocation must be in accordance with the rules defined in the |
17 | allocation parameters op_alot_prm. |
18 | The quantity successfully allocated is returned expressed in the |
19 | inventory item primary and secondary unit of measure. |
20 | |
21 | PARAMETERS |
22 | p_line_id IN NUMBER |
23 | p_trans_date IN DATE |
24 | p_ic_item_mst IN ic_item_mst%ROWTYPE |
25 | p_ic_whse_mst IN ic_whse_mst%ROWTYPE |
26 | p_op_alot_prm IN op_alot_prm%ROWTYPE |
27 | x_allocated_qty1 OUT NUMBER |
28 | x_allocated_qty2 OUT NUMBER |
29 | x_return_status OUT VARCHAR2 |
30 | x_msg_count OUT NUMBER |
31 | x_msg_data OUT VARCHAR2 |
32 | |
33 | HISTORY |
34 | 15-DEC-1999 K.Y.Hunt Created |
35 | APR-2003 NC Added logic for Auto Alloc Batch Enhacements |
36 | and did some cleanup. |
37 +=========================================================================+
38 */
39 PROCEDURE ALLOCATE_LINE
40 ( p_allocation_rec IN GMI_AUTO_ALLOCATE_PUB.gmi_allocation_rec
41 , p_ic_item_mst IN ic_item_mst%ROWTYPE
42 , p_ic_whse_mst IN ic_whse_mst%ROWTYPE
43 , p_op_alot_prm IN op_alot_prm%ROWTYPE
44 , p_batch_id IN NUMBER
45 , x_allocated_qty1 OUT NOCOPY NUMBER
46 , x_allocated_qty2 OUT NOCOPY NUMBER
47 , x_return_status OUT NOCOPY VARCHAR2
48 , x_msg_count OUT NOCOPY NUMBER
49 , x_msg_data OUT NOCOPY VARCHAR2
50 )
51 IS
52 l_api_name CONSTANT VARCHAR2 (30) := 'ALLOCATE_LINE';
53 l_msg_count NUMBER :=0;
54 l_msg_data VARCHAR2(2000);
55 l_return_status VARCHAR2(1);
56 l_allocation_successful VARCHAR2(1);
57 l_available_inventory1 NUMBER;
58 l_inventory_qty1 NUMBER :=0;
59 l_unallocated_qty1 NUMBER(19,9) :=0;
60 l_unallocated_qty2 NUMBER(19,9);
61 l_allocated_qty1 NUMBER :=0;
62 l_allocated_qty2 NUMBER :=0;
63 l_trans_qty1 NUMBER :=0;
64 l_trans_qty2 NUMBER(19,9) :=0;
65 l_loct_onhand NUMBER;
66 l_loct_onhand2 NUMBER;
67 l_commit_qty NUMBER;
68 l_commit_qty2 NUMBER;
69 l_lot_no IC_LOTS_MST.LOT_NO%TYPE :=0;
70 l_sublot_no IC_LOTS_MST.SUBLOT_NO%TYPE :=0;
71 l_loct_ctl NUMBER(2);
72 l_lot_id IC_LOTS_MST.LOT_ID%TYPE :=0;
73 l_lot_status IC_TRAN_PND.LOT_STATUS%TYPE;
74 l_lot_created IC_LOTS_MST.LOT_CREATED%TYPE;
75 l_location IC_TRAN_PND.LOCATION%TYPE;
76 l_expire_date IC_LOTS_MST.EXPIRE_DATE%TYPE;
77 l_qc_grade IC_LOTS_MST.QC_GRADE%TYPE;
78 l_shelf_date IC_TRAN_PND.TRANS_DATE%TYPE;
79
80 ll_shelf_date VARCHAR2(32);
81 l_from_expiration_date VARCHAR2(32);
82 l_to_expiration_date VARCHAR2(32);
83 l_from_creation_date VARCHAR2(32);
84 l_to_creation_date VARCHAR2(32);
85 /*
86 ll_shelf_date IC_TRAN_PND.TRANS_DATE%TYPE;
87 l_from_expiration_date IC_TRAN_PND.TRANS_DATE%TYPE;
88 l_to_expiration_date IC_TRAN_PND.TRANS_DATE%TYPE;
89 l_from_creation_date IC_TRAN_PND.TRANS_DATE%TYPE;
90 l_to_creation_date IC_TRAN_PND.TRANS_DATE%TYPE;
91 */
92 l_override_rules NUMBER DEFAULT 0;
93 l_whse_code IC_WHSE_MST.WHSE_CODE%TYPE;
94 l_IC$DEFAULT_LOCT IC_LOCT_MST.LOCATION%TYPE;
95 l_where_clause VARCHAR2(3000):= NULL;
96 l_order_by VARCHAR2(1000):= NULL;
97 l_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
98 l_tran_row IC_TRAN_PND%ROWTYPE;
99 l_allocation_rec GMI_AUTO_ALLOCATE_PUB.gmi_allocation_rec;
100 l_batch_rec GMI_AUTO_ALLOCATION_BATCH%ROWTYPE;
101 l_lot_indivisible NUMBER;
102 l_lot_qty NUMBER;
103 l_lots_specified NUMBER DEFAULT 1; /* 0=No lots specified, 1= 1 specified,2= Multiple specified */
104 l_overpick_enabled VARCHAR2(1);
105
106 --2722339 EMC Auto Alloc QC Spec Match Project
107 l_grade_or_qcmatch_flag NUMBER DEFAULT 0;
108 l_prm_prefqc_grade VARCHAR2(10) DEFAULT null;
109 find_cust_spec_rec GMD_SPEC_MATCH_GRP.customer_spec_rec_type;
110 l_out_rec BOOLEAN;
111 l_spec_hdr_id NUMBER;
112 l_spec_vr_id NUMBER;
113 l_spec_return_status VARCHAR2(1);
114 l_header_id NUMBER;
115 l_schedule_ship_date DATE;
116 l_org_id NUMBER;
117 l_sold_to_org_id NUMBER;
118 l_ship_to_org_id NUMBER;
119 l_preferred_grade IC_LOTS_MST.QC_GRADE%TYPE;
120 l_message_data VARCHAR2(500);
121 l_alloc_all_lot_flag VARCHAR2(1);
122
123
124 result_lot_match_tbl GMD_SPEC_MATCH_GRP.result_lot_match_tbl;
125 result_flag NUMBER DEFAULT 0 ;
126 x2_return_status VARCHAR2(1);
127 x2_message_data VARCHAR2(500);
128
129 CURSOR Get_order_line_info IS
130 Select
131 header_id,
132 sold_to_org_id,
133 schedule_ship_date,
134 ship_to_org_id,
135 org_id,
136 preferred_grade
137 From oe_order_lines_all
138 Where line_id = p_allocation_rec.line_id;
139
140 CURSOR Get_Batch_Rec_Cur(p_batch_id NUMBER) IS
141 SELECT *
142 FROM gmi_auto_allocation_batch
143 WHERE batch_id = p_batch_id;
144
145 TYPE rc IS REF CURSOR;
146 ic_inventory_view_c1 rc;
147
148 BEGIN
149 /*Initialize return status to success
150 ====================================*/
151 GMI_Reservation_Util.PrintLn('(Alloc PVT) GMIVALIB.pls Alloc Inventory Pvt');
152
153 x_return_status := FND_API.G_RET_STS_SUCCESS;
154
155 /*Standard Start OF API savepoint
156 ================================*/
157 SAVEPOINT allocate_line;
158 gmi_reservation_util.println('OPM Allocation Engine - start allocate line',1);
159
160 /*Get required system constants
161 ==============================*/
162 l_IC$DEFAULT_LOCT := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
163 l_overpick_enabled := FND_PROFILE.VALUE('WSH_OVERPICK_ENABLED');
164
165 /* dbms_output.put_line('default loct is ' || l_IC$DEFAULT_LOCT); */
166 /* NC */
167 IF (NVL(p_batch_id,0) <> 0) THEN
168 OPEN Get_Batch_Rec_Cur(p_batch_id);
169 FETCH Get_Batch_Rec_Cur INTO l_batch_rec;
170
171 IF(Get_Batch_Rec_Cur%NOTFOUND) THEN
172 CLOSE Get_Batch_Rec_Cur;
173 END IF;
174
175 CLOSE Get_Batch_Rec_Cur;
176 END IF;
177
178 IF( NVL(p_batch_id,0) = 0 )
179 THEN
180 l_override_rules := 0;
181 ELSIF(l_batch_rec.override_rules = 'Y')
182 THEN
183 l_override_rules := 1;
184 ELSE l_override_rules := 0;
185 END IF;
186
187 l_lot_qty := p_op_alot_prm.lot_qty;
188
189 /*Check allocation horizon
190 =========================*/
191
192 /* dbms_output.put_line */
193 /* ('allocation horizon is set to '|| p_op_alot_prm.alloc_horizon); */
194 /* dbms_output.put_line */
195 /* ('trans date ' || p_allocation_rec.trans_date || ' vs system date ' || SYSDATE); */
196
197 gmi_reservation_util.println
198 ('OPM Allocation Engine - allocation parameter used is ' || p_op_alot_prm.allocrule_id);
199 gmi_reservation_util.println
200 ('OPM Allocation Engine - allocation horizon is ' || p_op_alot_prm.alloc_horizon);
201
202 IF (l_override_rules = 0 AND p_op_alot_prm.alloc_horizon > 0) AND
203 (p_allocation_rec.trans_date > (SYSDATE + p_op_alot_prm.alloc_horizon)) THEN
204 /* dbms_output.put_line('allocation horizon is out - using '|| p_op_alot_prm.alloc_horizon); */
205 GMI_RESERVATION_UTIL.println('Allocation Horizon error: Scheduled ship date falls outside the allocation horizon');
206 FND_MESSAGE.SET_NAME('GML','SO_E_ALLOC_HORIZON_ERR');
207 FND_MSG_PUB.Add;
208 RAISE FND_API.G_EXC_ERROR;
209 END IF;
210
211 /*If order UM differs from inventory UM, conversion is required.
212 The allocations are recorded as transactions written in the inventory UM
213 =======================================================================*/
214 IF (p_allocation_rec.order_um1 <> p_ic_item_mst.item_um) THEN
215 GMICUOM.icuomcv(pitem_id => p_ic_item_mst.item_id,
216 plot_id => l_lot_id,
217 pcur_qty => p_allocation_rec.order_qty1,
218 pcur_uom => p_allocation_rec.order_um1,
219 pnew_uom => p_ic_item_mst.item_um,
220 onew_qty => l_inventory_qty1);
221 ELSE
222 l_inventory_qty1 := p_allocation_rec.order_qty1;
223 END IF;
224
225 l_unallocated_qty1 := l_inventory_qty1;
226
227
228 /*Build WHERE clause according to the item/whse attributes
229 =========================================================*/
230 gmi_reservation_util.println('OPM ALLOCATION ENGINE - build where clause',1);
231 /* dbms_output.put_line('BUILD WHERE CLAUSE '); */
232 IF (l_override_rules = 1)
233 THEN
234 l_shelf_date := p_allocation_rec.trans_date;
235 ELSE
236 l_shelf_date := p_allocation_rec.trans_date + p_op_alot_prm.shelf_days;
237 END IF;
238 ll_shelf_date := TO_CHAR(l_shelf_date,'DD-MON-YYYY, HH:MI:SS');
239
240 l_whse_code := p_ic_whse_mst.whse_code;
241
242 l_where_clause :=
243 'item_id = '|| p_ic_item_mst.item_id ;
244 l_where_clause := l_where_clause ||' AND whse_code = ';
245 l_where_clause := l_where_clause || '''';
246 l_where_clause := l_where_clause || l_whse_code ;
247 l_where_clause := l_where_clause || '''';
248 l_where_clause := l_where_clause ||' AND loct_onhand >= 0 ';
249
250 /* expiration date */
251 l_where_clause := l_where_clause ||' AND expire_date > ';
252 l_where_clause := l_where_clause ||'TO_DATE( ';
253 l_where_clause := l_where_clause || '''';
254 l_where_clause := l_where_clause ||ll_shelf_date;
255 l_where_clause := l_where_clause || '''';
256 l_where_clause := l_where_clause || ',';
257 l_where_clause := l_where_clause || '''';
258 l_where_clause := l_where_clause || 'DD-MON-YYYY, HH:MI:SS';
259 l_where_clause := l_where_clause || '''';
260 l_where_clause := l_where_clause || ')';
261
262 IF (p_ic_item_mst.lot_ctl = 0) THEN
263 l_where_clause := l_where_clause ||
264 ' and lot_id = 0 ';
265 ELSE
266 l_where_clause := l_where_clause ||
267 ' and lot_id > 0 ';
268 END IF;
269
270 /*Overall location control is determined by looking at both the item and whse
271 =============================================================================*/
272 l_loct_ctl := p_ic_item_mst.loct_ctl * p_ic_whse_mst.loct_ctl;
273 GMI_RESERVATION_UTIL.PrintLn('loct ctl set to ' || l_loct_ctl || ' from ' || p_ic_item_mst.loct_ctl
274 || ' and ' || p_ic_whse_mst.loct_ctl );
275
276 IF l_loct_ctl = 0 THEN
277 l_where_clause := l_where_clause || ' AND location = ';
278 l_where_clause := l_where_clause || '''' ;
279 l_where_clause := l_where_clause || l_IC$default_loct ;
280 l_where_clause := l_where_clause || '''';
281 ELSE
282 l_where_clause := l_where_clause || ' AND location <> ';
283 l_where_clause := l_where_clause || '''';
284 l_where_clause := l_where_clause || l_IC$default_loct ;
285 l_where_clause := l_where_clause || '''';
286 END IF;
287
288 /*Apply preferred QC grade if appropriate
289 ========================================*/
290 GMI_RESERVATION_UTIL.println('(ALLOC PVT) :l_allocation_rec.prefqc_grade'|| l_allocation_rec.prefqc_grade);
291 GMI_Reservation_Util.PrintLn('(Alloc PVT) p_allocation_rec.prefqc_grade = ' || p_allocation_rec.prefqc_grade);
292 GMI_Reservation_Util.PrintLn('(Alloc PVT) p_op_alot_prm.prefqc_grade = ' || p_op_alot_prm.prefqc_grade);
293
294 --2722339 EMC Auto Alloc QC Spec Match Project
295 --If matching on Grade, preference is given to grade taken from sales order
296 --line. Otherwise, grade is taken from Sales Order/Shipping Parameter form.
297
298
299 IF (l_override_rules = 1)
300 THEN
301 l_grade_or_qcmatch_flag := 0;
302 l_prm_prefqc_grade := NULL;
303 ELSE
304 l_grade_or_qcmatch_flag := p_op_alot_prm.grade_or_qc_flag;
305 l_prm_prefqc_grade := p_op_alot_prm.prefqc_grade;
306 END IF;
307
308
309 l_allocation_rec.prefqc_grade := p_allocation_rec.prefqc_grade;
310 GMI_RESERVATION_UTIL.println('Im here :l_allocation_rec.prefqc_grade'|| l_allocation_rec.prefqc_grade);
311
312 IF (NVL(l_grade_or_qcmatch_flag,0) = 0) THEN
313 IF (l_override_rules = 1) THEN
314 l_allocation_rec.prefqc_grade := null;
315 ELSE
316 IF p_ic_item_mst.grade_ctl = 1 AND
317 l_allocation_rec.prefqc_grade is NOT NULL THEN
318 l_where_clause := l_where_clause || ' and qc_grade = :v_qc_grade ';
322 l_prm_prefqc_grade IS NOT NULL THEN
319 l_allocation_rec.prefqc_grade := p_allocation_rec.prefqc_grade;
320 GMI_Reservation_Util.PrintLn('(Alloc PVT) l_where_clause 1= ' || l_where_clause);
321 ELSIF p_ic_item_mst.grade_ctl = 1 AND
323 l_where_clause := l_where_clause || ' and qc_grade = :v_qc_grade ';
324 l_allocation_rec.prefqc_grade := l_prm_prefqc_grade;
325 GMI_Reservation_Util.PrintLn('(Alloc PVT) l_where_clause 2= ' || l_where_clause);
326 END IF;
327 END IF;
328 END IF;
329
330 /*
331 IF (NVL(l_grade_or_qcmatch_flag,0) = 0) THEN
332
333 IF ( p_ic_item_mst.grade_ctl = 1 AND l_allocation_rec.prefqc_grade is NOT NULL)
334 THEN
335 l_allocation_rec.prefqc_grade := p_allocation_rec.prefqc_grade;
336 ELSIF( p_ic_item_mst.grade_ctl = 1 AND l_prm_prefqc_grade IS NOT NULL)
337 THEN
338 l_allocation_rec.prefqc_grade := l_prm_prefqc_grade;
339 END IF;
340
341 GMI_RESERVATION_UTIL.println('l_allocation_rec.prefqc_grade'|| l_allocation_rec.prefqc_grade);
342
343 l_where_clause := l_where_clause || ' and qc_grade = ';
344 l_where_clause := l_where_clause || '''' ;
345 l_where_clause := l_where_clause || l_allocation_rec.prefqc_grade;
346 l_where_clause := l_where_clause || '''';
347
348 GMI_Reservation_Util.PrintLn('(Alloc PVT) l_where_clause 1= ' || l_where_clause);
349 END IF;
350 */
351
352 GMI_RESERVATION_UTIL.PrintLn('(Auto_Alloc) 1 WHERE clause is now '|| l_where_clause);
353 GMI_RESERVATION_UTIL.PrintLn('WHSE IS '|| p_ic_whse_mst.whse_code);
354 GMI_RESERVATION_UTIL.PrintLn('item_no item_id: ' ||p_ic_item_mst.item_no ||' '|| p_ic_item_mst.item_id);
355 GMI_RESERVATION_UTIL.PrintLn('QC Grade to be used is '|| p_allocation_rec.prefqc_grade);
356
357 GMI_RESERVATION_UTIL.PrintLn('Using a shelf date of '|| l_shelf_date );
358
359 IF(l_batch_rec.from_lot_no <> FND_API.G_MISS_CHAR AND
360 nvl (l_batch_rec.from_lot_no,'%$%') <> '%$%')
361 THEN
362 l_where_clause := l_where_clause || ' and lot_no >= ';
363 l_where_clause := l_where_clause || '''' ;
364 l_where_clause := l_where_clause || l_batch_rec.from_lot_no ;
365 l_where_clause := l_where_clause || '''';
366
367 IF (l_batch_rec.to_lot_no <> FND_API.G_MISS_CHAR AND
368 nvl (l_batch_rec.to_lot_no,'%$%') <> '%$%')
369 THEN
370 IF( l_batch_rec.from_lot_no <> l_batch_rec.to_lot_no)
371 THEN
372 l_lots_specified:= 2;
373 END IF;
374 END IF;
375 ELSE
376 l_lots_specified := 0;
377 END IF;
378
379
380 IF(l_batch_rec.to_lot_no <> FND_API.G_MISS_CHAR AND
381 nvl (l_batch_rec.to_lot_no,'%$%') <> '%$%')
382 THEN
383 l_where_clause := l_where_clause || ' and lot_no <= ';
384 l_where_clause := l_where_clause || '''' ;
385 l_where_clause := l_where_clause || l_batch_rec.to_lot_no ;
386 l_where_clause := l_where_clause || '''';
387
388 IF(l_lots_specified = 0)
389 THEN
390 l_lots_specified := 1;
391 END IF;
392 END IF;
393
394 GMI_RESERVATION_UTIL.PrintLn('(Auto_Alloc) 2 WHERE clause is now '|| l_where_clause);
395
396 IF ( (l_lots_specified = 0 OR l_lots_specified = 2) AND l_batch_rec.alloc_all_lot_flag = 'Y'
397 AND l_override_rules <> 1 AND l_lot_qty = 1)
398 THEN
399 GMI_RESERVATION_UTIL.PrintLn('WARNING! Allocation Parameters Conflict with Allocation Criteria on the form...');
400 GMI_RESERVATION_UTIL.PrintLn('Allocation Parametrs require that allocation is made from a single lot. Allocate All Specified Lots is chosen on the screen and No Lots/Multiple Lots are specified; Returning........');
401 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
402 RETURN;
403 END IF;
404
405
406 IF (l_batch_rec.from_sublot_no <> FND_API.G_MISS_CHAR and
407 nvl(l_batch_rec.from_sublot_no,'%$%') <> '%$%')
408 THEN
409 l_where_clause := l_where_clause || ' and sublot_no >= ';
410 l_where_clause := l_where_clause || '''' ;
411 l_where_clause := l_where_clause || l_batch_rec.from_sublot_no ;
412 l_where_clause := l_where_clause || '''';
413 END IF;
414
415 IF (l_batch_rec.to_sublot_no <> FND_API.G_MISS_CHAR and
416 nvl(l_batch_rec.to_sublot_no,'%$%') <> '%$%')
417 THEN
418 l_where_clause := l_where_clause || ' and sublot_no <= ';
419 l_where_clause := l_where_clause || '''' ;
420 l_where_clause := l_where_clause || l_batch_rec.to_sublot_no ;
421 l_where_clause := l_where_clause || '''';
422 END IF;
423
424 GMI_RESERVATION_UTIL.PrintLn('From creation date'|| l_batch_rec.from_creation_date);
425 GMI_RESERVATION_UTIL.PrintLn('to_char(from creation date)'|| to_char(l_batch_rec.from_creation_date));
426 GMI_RESERVATION_UTIL.PrintLn('(Auto_Alloc) 3 WHERE clause is now '|| l_where_clause);
427
428 IF ( TO_CHAR(l_batch_rec.from_creation_date) <> FND_API.G_MISS_CHAR AND
429 TO_CHAR(l_batch_rec.from_creation_date, 'DD-MON-YYYY') <> ' ')
430 THEN
431 l_from_creation_date := TO_CHAR(l_batch_rec.from_creation_date,'DD-MON-YYYY');
432
433 l_where_clause := l_where_clause || ' and lot_created >= ';
434 l_where_clause := l_where_clause ||'TO_DATE( ';
435 l_where_clause := l_where_clause || '''';
436 l_where_clause := l_where_clause ||l_from_creation_date;
440 l_where_clause := l_where_clause || 'DD-MON-YYYY HH:MI:SS';
437 l_where_clause := l_where_clause || '''';
438 l_where_clause := l_where_clause || ',';
439 l_where_clause := l_where_clause || '''';
441 l_where_clause := l_where_clause || '''';
442 l_where_clause := l_where_clause || ')';
443 END IF;
444
445 GMI_RESERVATION_UTIL.PrintLn('(Auto_alloc)4 WHERE clause is now '|| l_where_clause);
446
447 IF ( TO_CHAR(l_batch_rec.to_creation_date) <> FND_API.G_MISS_CHAR AND
448 TO_CHAR(l_batch_rec.to_creation_date, 'DD-MON-YYYY') <> ' ')
449 THEN
450 l_to_creation_date := TO_CHAR((l_batch_rec.to_creation_date + 1),'DD-MON-YYYY');
451
452 l_where_clause := l_where_clause || ' and lot_created <= ';
453 l_where_clause := l_where_clause ||'TO_DATE( ';
454 l_where_clause := l_where_clause || '''';
455 l_where_clause := l_where_clause ||l_to_creation_date;
456 l_where_clause := l_where_clause || '''';
457 l_where_clause := l_where_clause || ',';
458 l_where_clause := l_where_clause || '''';
459 l_where_clause := l_where_clause || 'DD-MON-YYYY HH:MI:SS';
460 l_where_clause := l_where_clause || '''';
461 l_where_clause := l_where_clause || ')';
462 END IF;
463
464 IF ( TO_CHAR(l_batch_rec.from_expiration_date) <> FND_API.G_MISS_CHAR AND
465 TO_CHAR(l_batch_rec.from_expiration_date, 'DD-MON-YYYY') <> ' ')
466 THEN
467 l_from_expiration_date := TO_CHAR(l_batch_rec.from_expiration_date,'DD-MON-YYYY');
468
469 l_where_clause := l_where_clause || ' and expire_date >= ';
470 l_where_clause := l_where_clause ||'TO_DATE( ';
471 l_where_clause := l_where_clause || '''';
472 l_where_clause := l_where_clause ||l_from_expiration_date;
473 l_where_clause := l_where_clause || '''';
474 l_where_clause := l_where_clause || ',';
475 l_where_clause := l_where_clause || '''';
476 l_where_clause := l_where_clause || 'DD-MON-YYYY';
477 l_where_clause := l_where_clause || '''';
478 l_where_clause := l_where_clause || ')';
479
480 END IF;
481
482 GMI_RESERVATION_UTIL.PrintLn('(Auto_Alloc) 5 WHERE clause is now '|| l_where_clause);
483
484 IF ( TO_CHAR(l_batch_rec.to_expiration_date) <> FND_API.G_MISS_CHAR AND
485 TO_CHAR(l_batch_rec.to_expiration_date, 'DD-MON-YYYY') <> ' ')
486 THEN
487 l_to_expiration_date := TO_CHAR((l_batch_rec.to_expiration_date+1),'DD-MON-YYYY');
488
489 l_where_clause := l_where_clause || ' and expire_date <= ';
490 l_where_clause := l_where_clause ||'TO_DATE( ';
491 l_where_clause := l_where_clause || '''';
492 l_where_clause := l_where_clause ||l_to_expiration_date;
493 l_where_clause := l_where_clause || '''';
494 l_where_clause := l_where_clause || ',';
495 l_where_clause := l_where_clause || '''';
496 l_where_clause := l_where_clause || 'DD-MON-YYYY';
497 l_where_clause := l_where_clause || '''';
498 l_where_clause := l_where_clause || ')';
499 END IF;
500
501
502 IF ( l_batch_rec.lot_status <> FND_API.G_MISS_CHAR AND
503 nvl(l_batch_rec.lot_status,'%$%') <> '%$%')
504 THEN
505 l_where_clause := l_where_clause || ' and lot_status = ';
506 l_where_clause := l_where_clause || '''' ;
507 l_where_clause := l_where_clause || l_batch_rec.lot_status;
508 l_where_clause := l_where_clause || '''';
509 END IF;
510
511 IF ( l_batch_rec.location <> FND_API.G_MISS_CHAR AND
512 nvl(l_batch_rec.location,'%$%') <> '%$%')
513 THEN
514 l_where_clause := l_where_clause || ' and location = ';
515 l_where_clause := l_where_clause || '''' ;
516 l_where_clause := l_where_clause || l_batch_rec.location;
517 l_where_clause := l_where_clause || '''';
518 END IF;
519 GMI_RESERVATION_UTIL.PrintLn('(Auto_Alloc) 6 WHERE clause is now '|| l_where_clause);
520
521 /*Order rows according to the allocation method chosen
522 =====================================================*/
523 IF ( l_override_rules = 0 ) THEN
524 IF (p_op_alot_prm.alloc_method = 0) THEN /* FIFO */
525 l_order_by := ' lot_created' ;
526 ELSE /* FEFO */
527 l_order_by := ' expire_date';
528 END IF;
529 ELSE
530 l_order_by := ' NULL';
531 END IF;
532
533 GMI_Reservation_Util.PrintLn('About to open cursor for dynamic SQL');
534 gmi_reservation_util.println('OPM ALLOCATION ENGINE - open cursor for dynamic SQL',1);
535
536 GMI_Reservation_Util.PrintLn('(Alloc PVT) l_grade_or_qcmatch_flag = ' || l_grade_or_qcmatch_flag);
537 GMI_Reservation_Util.PrintLn('(Alloc PVT) p_ic_item_mst.grade_ctl = ' || p_ic_item_mst.grade_ctl);
538
539 --2722339 EMC Auto Alloc QC Spec Match Project
540 --When Customer Spec is indicated on OPALOTED.fmb, grade is not used
541 --in select. When Grade is specified and a grade is specified on the
542 --sales order line and/or OPALOTED.fmb, grade is used as part of the select
543 --criteria. When either item is not grade controlled or no grade is
544 --specified on so line or form,no grade is used as part of select.
545 IF (NVL(l_grade_or_qcmatch_flag,0) = 1 ) THEN
546 GMI_Reservation_Util.PrintLn('(Alloc PVT) Select A ') ;
547
548 OPEN ic_inventory_view_c1 for
549 'SELECT sum(loct_onhand),sum(loct_onhand2),
550 sum(commit_qty),sum(commit_qty2),
551 sum(loct_onhand) + sum(commit_qty),
555 WHERE ' || l_where_clause ||
552 lot_no,sublot_no,lot_id,lot_status,
553 lot_created,location,expire_date,qc_grade
554 FROM ic_item_inv_v
556 ' GROUP BY lot_no,sublot_no,lot_id,lot_status,
557 lot_created,location,expire_date,qc_grade' ||
558 ' HAVING sum(loct_onhand) + sum(commit_qty) > 0 ' ||
559 ' ORDER BY ' || l_order_by;
560 ELSIF
561 (NVL(l_grade_or_qcmatch_flag,0) = 0 AND
562 p_ic_item_mst.grade_ctl = 1 AND
563 l_allocation_rec.prefqc_grade is NOT NULL) THEN
564 BEGIN
565
566 GMI_Reservation_Util.PrintLn('(Alloc PVT) Select B ') ;
567 GMI_RESERVATION_UTIL.PRINTLN('where clause B'|| l_where_clause);
568 GMI_RESERVATION_UTIL.PRINTLN('l_order_by'|| l_order_by);
569
570 OPEN ic_inventory_view_c1 for
571 'SELECT sum(loct_onhand),sum(loct_onhand2),
572 sum(commit_qty),sum(commit_qty2),
573 sum(loct_onhand) + sum(commit_qty),
574 lot_no,sublot_no,lot_id,lot_status,
575 lot_created,location,expire_date,qc_grade
576 FROM ic_item_inv_v
577 WHERE ' || l_where_clause ||
578 ' GROUP BY lot_no,sublot_no,lot_id,lot_status,
579 lot_created,location,expire_date,qc_grade' ||
580 ' HAVING sum(loct_onhand) + sum(commit_qty) > 0 ' ||
581 ' ORDER BY ' || l_order_by
582 using l_allocation_rec.prefqc_grade;
583
584 EXCEPTION
585 WHEN OTHERS THEN
586 GMI_RESERVATION_UTIL.println('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
587 END;
588 GMI_Reservation_Util.PrintLn('(Alloc PVT) Select B after query') ;
589 ELSIF
590 (NVL(l_grade_or_qcmatch_flag,0) = 0 AND
591 (p_ic_item_mst.grade_ctl <> 1 OR
592 l_allocation_rec.prefqc_grade is NULL)) THEN
593 GMI_Reservation_Util.PrintLn('(Alloc PVT) Select C ') ;
594 GMI_Reservation_Util.PrintLn('(Alloc PVT) where clause' ||l_where_clause) ;
595
596 OPEN ic_inventory_view_c1 for
597 'SELECT sum(loct_onhand),sum(loct_onhand2),
598 sum(commit_qty),sum(commit_qty2),
599 sum(loct_onhand) + sum(commit_qty),
600 lot_no,sublot_no,lot_id,lot_status,
601 lot_created,location,expire_date,qc_grade
602 FROM ic_item_inv_v
603 WHERE ' || l_where_clause ||
604 ' GROUP BY lot_no,sublot_no,lot_id,lot_status,
605 lot_created,location,expire_date,qc_grade' ||
606 ' HAVING sum(loct_onhand) + sum(commit_qty) > 0 '||
607 ' ORDER BY ' || l_order_by;
608 END IF;
609
610 --2722339 EMC Auto Alloc QC Spec Match Project
611 --Call GMD_QC_SPEC_MATCH logic to determine whether spec_hdr_id exists.
612 GMI_RESERVATION_UTIL.println('Alloc PVT after the cursor IF');
613
614 IF (NVL(l_grade_or_qcmatch_flag,0) = 1) THEN
615 OPEN Get_order_line_info;
616 FETCH Get_order_line_info INTO
617 l_header_id,
618 l_sold_to_org_id,
619 l_schedule_ship_date,
620 l_ship_to_org_id,
621 l_org_id,
622 l_preferred_grade;
623 CLOSE Get_order_line_info;
624
625 find_cust_spec_rec.item_id := p_ic_item_mst.item_id;
626 --find_cust_spec_rec.grade := l_allocation_rec.prefqc_grade;
627 find_cust_spec_rec.orgn_code := NULL;
628 find_cust_spec_rec.whse_code := p_ic_whse_mst.whse_code;
629 find_cust_spec_rec.cust_id := l_sold_to_org_id;
630 find_cust_spec_rec.date_effective := l_schedule_ship_date;
631 find_cust_spec_rec.org_id := l_org_id;
632 find_cust_spec_rec.ship_to_site_id := l_ship_to_org_id;
633 find_cust_spec_rec.order_id := l_header_id;
634 find_cust_spec_rec.order_line := NULL;
635 find_cust_spec_rec.order_line_id :=p_allocation_rec.line_id;
636 find_cust_spec_rec.look_in_other_orgn := 'Y';
637 find_cust_spec_rec.exact_match := 'N';
638
639 GMI_Reservation_Util.PrintLn('(Alloc PVT) Calling GMD_SPEC_MATCH_GRP.find_customer_spec ') ;
640 l_out_rec := GMD_SPEC_MATCH_GRP.find_customer_spec
641 (p_customer_spec_rec => find_cust_spec_rec
642 ,x_spec_id => l_spec_hdr_id
643 ,x_spec_vr_id => l_spec_vr_id
644 ,x_return_status => l_spec_return_status
645 ,x_message_data => l_message_data );
646 END IF;
647 GMI_Reservation_Util.PrintLn('(Alloc PVT) l_spec_hdr_id = ' || nvl(l_spec_hdr_id,0));
648
649
650 --If spec_hdr_id exists, determine whether a customer match exists.
651 --Otherwise, go through regular allocation code.
652
653 LOOP
654 GMI_Reservation_Util.PrintLn('(Alloc PVT) l_allocated_qty1 = ' || l_allocated_qty1) ;
655 GMI_Reservation_Util.PrintLn('(Alloc PVT) l_inventory_qty1 = ' || l_inventory_qty1) ;
656
657 IF (p_batch_id IS NULL OR l_batch_rec.alloc_all_lot_flag <> 'Y' OR
658 l_overpick_enabled = 'N')
659 THEN
660 EXIT WHEN l_allocated_qty1 >= l_inventory_qty1;
661 END IF;
662 -- EXIT WHEN l_allocated_qty1 = l_inventory_qty1;
663 --l_allocation_successful := NULL;
664
665 FETCH ic_inventory_view_c1 INTO l_loct_onhand,l_loct_onhand2,
666 l_commit_qty,l_commit_qty2,l_available_inventory1,
667 l_lot_no,l_sublot_no,l_lot_id,l_lot_status,
668 l_lot_created,l_location,l_expire_date,l_qc_grade;
669
670 IF ic_inventory_view_c1%NOTFOUND THEN
674
671 GMI_Reservation_Util.PrintLn('(Alloc PVT) Inside LOOP no rows bailing') ;
672 gmi_reservation_util.println('OPM ALLOCATION ENGINE - no inventory rows found',1);
673 END IF;
675 EXIT WHEN ic_inventory_view_c1%NOTFOUND;
676
677 --2722339 EMC Auto Alloc QC Spec Match Project
678 --If spec_hdr_id exists, determine whether a customer match exists.
679 --Otherwise, go through regular allocation code.
680
681 IF ( l_spec_hdr_id >0) THEN
682 /* Commented since these dont exist in 12.0 ; P1 SCM Build bug #4561095
683 result_lot_match_tbl(1).item_id := p_ic_item_mst.item_id;
684 result_lot_match_tbl(1).lot_id := l_lot_id;
685 result_lot_match_tbl(1).whse_code := p_ic_whse_mst.whse_code;
686 result_lot_match_tbl(1).location := l_location;
687 */
688
689 GMI_Reservation_Util.PrintLn('(Alloc PVT) calling GMD_SPEC_MATCH_GRP.get_spec_match');
690
691 GMD_SPEC_MATCH_GRP.get_result_match_for_spec
692 ( p_spec_id => l_spec_hdr_id
693 , p_lots => result_lot_match_tbl
694 , x_return_status => x2_return_status
695 , x_message_data => x2_message_data) ;
696
697 GMI_Reservation_Util.PrintLn('(Alloc PVT) result_lot_match_tbl.COUNT = ' || nvl(result_lot_match_tbl.COUNT,0));
698 GMI_Reservation_Util.PrintLn('(Alloc PVT) result_lot_match_tbl(1).spec_match_type = ' || result_lot_match_tbl(1).spec_match_type);
699
700
701 IF (x2_return_status = 'S' AND result_lot_match_tbl.COUNT >0) THEN
702 IF (result_lot_match_tbl(1).spec_match_type = 'A') THEN
703 result_flag := 1;
704 GMI_Reservation_Util.PrintLn('(Alloc PVT) result_flag = ' || result_flag);
705 END IF;
706 END IF;
707 END IF; /*end l_spec_hdr_id >0 condition 1 */
708
709 /*Interpret the rules on spliting the lot
710 ========================================*/
711 GMI_RESERVATION_UTIL.PrintLn('examining whse/lot_no/id/location/grade '
712 || p_ic_whse_mst.whse_code || '/' || l_lot_no || '/' ||
713 l_lot_id || '/' || l_location || '/' || l_qc_grade );
714
715 gmi_reservation_util.println('examining whse/lot_no/id/location/grade '
716 || p_ic_whse_mst.whse_code || '/' || l_lot_no || '/' ||
717 l_lot_id || '/' || l_location || '/' || l_qc_grade );
718
719 GMI_RESERVATION_UTIL.PrintLn('onhand is ' || l_loct_onhand);
720 GMI_RESERVATION_UTIL.PrintLn('committed is ' || l_commit_qty );
721 GMI_RESERVATION_UTIL.PrintLn('available is ' || l_available_inventory1);
722 GMI_RESERVATION_UTIL.PrintLn('what is lot_qty config '|| p_op_alot_prm.lot_qty);
723
724
725 --2722339 EMC Auto Alloc QC Spec Match Project
726 --If Customer Spec indicated, allocate only to matched result rows
727
728 GMI_Reservation_Util.PrintLn('(Alloc PVT) result flag : ' ||result_flag ) ;
729 -- Bug 3180256 (adding NVL below)
730 IF (result_flag = 1 OR NVL(l_grade_or_qcmatch_flag,0) = 0) THEN
731 GMI_Reservation_Util.PrintLn('(Alloc PVT) Following regular logic flow.') ;
732 l_lot_indivisible := p_ic_item_mst.lot_indivisible;
733 IF( l_batch_rec.alloc_all_lot_flag = 'Y' OR
734 l_batch_rec.lots_indivisible_flag = 'Y')
735 THEN
736 l_lot_indivisible := 1;
737 END IF;
738 l_lot_qty := p_op_alot_prm.lot_qty;
739 IF(l_batch_rec.override_rules = 'Y')
740 THEN
741 l_lot_qty := 0;
742 END IF;
743 IF (l_lot_indivisible = 0)
744 THEN /* lot can be split */
745 GMI_RESERVATION_UTIL.PrintLn('lot_indivisible is off');
746 IF (l_available_inventory1 >= l_unallocated_qty1) THEN
747 l_trans_qty1 := l_unallocated_qty1;
748 l_allocated_qty1 := l_allocated_qty1 + l_unallocated_qty1;
749 l_unallocated_qty1 := 0;
750 /* demand fulfilled so invoke transaction processor */
751 l_allocation_successful := 'Y';
752 ELSIF (l_lot_qty = 0 )
753 /* Use any number of lots */
754 THEN
755 GMI_RESERVATION_UTIL.PrintLn('any number of lots ');
756 GMI_RESERVATION_UTIL.PrintLn('available is less than required ' ||
757 l_available_inventory1 || ' ' || l_unallocated_qty1);
758 l_trans_qty1 := l_available_inventory1;
759 l_allocated_qty1 := l_allocated_qty1 + l_available_inventory1;
760 l_unallocated_qty1 := l_inventory_qty1 - l_allocated_qty1;
761 /* demand fulfilled so invoke transaction processor */
762 l_allocation_successful := 'Y';
763 END IF;
764 END IF;
765 /* Deal with the lot indivisible scenario
766 ========================================*/
767 IF (l_lot_indivisible = 1)
768 /* lot must not be split */
769 THEN
770 GMI_RESERVATION_UTIL.PrintLn('lot_indivisible is ON');
771 IF (l_available_inventory1 <= l_unallocated_qty1) THEN
772 GMI_RESERVATION_UTIL.PrintLn('less inventory than we need'
773 || l_available_inventory1 || ' versus ' || l_unallocated_qty1);
774 l_trans_qty1 := l_available_inventory1;
775 l_allocated_qty1 := l_allocated_qty1 + l_available_inventory1;
776 l_unallocated_qty1 := l_inventory_qty1 - l_allocated_qty1;
780 ELSIF (l_overpick_enabled = 'Y')THEN
777 /* invoke transaction processor */
778 l_allocation_successful := 'Y';
779
781 GMI_RESERVATION_UTIL.PrintLn('Lot indivisible is On or Alloc_all_lot_flag = Y');
782 GMI_RESERVATION_UTIL.PrintLn('Allocating all the available inv for this row');
783 l_trans_qty1 := l_available_inventory1;
784 l_allocated_qty1 := l_allocated_qty1 + l_available_inventory1;
785 l_unallocated_qty1 := l_inventory_qty1 - l_allocated_qty1;
786 l_allocation_successful := 'Y';
787 END IF;
788 END IF;
789
790 /*If suitable stock has been located, write a transaction to ic_tran_pnd
791 =======================================================================*/
792 GMI_Reservation_Util.PrintLn('(Alloc PVT) l_allocation_successful = ' || l_allocation_successful);
793 If l_allocation_successful = 'Y' THEN
794 /*Convert allocated qty to secondary UM where appropriate
795 ========================================================*/
796 GMI_RESERVATION_UTIL.PrintLn('secondary UM conv');
797 IF p_ic_item_mst.dualum_ind > 0 THEN
798 GMICUOM.icuomcv(pitem_id => p_ic_item_mst.item_id,
799 plot_id => l_lot_id,
800 pcur_qty => l_trans_qty1,
801 pcur_uom => p_ic_item_mst.item_um,
802 pnew_uom => p_ic_item_mst.item_um2,
803 onew_qty => l_trans_qty2);
804 END IF;
805 GMI_RESERVATION_UTIL.PrintLn('invoke txn engine');
806 GMI_RESERVATION_UTIL.PrintLn('write txn for qty ' || l_trans_qty1);
807 gmi_reservation_util.println('OPM ALLOCATION ENGINE - invoke transaction engine',1);
808 /*Set up parameters in readiness for writing a pending transaction
809 =================================================================*/
810 l_tran_rec.trans_id := NULL;
811 l_tran_rec.item_id := p_ic_item_mst.item_id;
812 l_tran_rec.line_id := p_allocation_rec.line_id;
813 l_tran_rec.co_code := p_allocation_rec.co_code;
814 l_tran_rec.orgn_code := p_ic_whse_mst.orgn_code;
815 l_tran_rec.whse_code := p_ic_whse_mst.whse_code;
816 l_tran_rec.lot_id := l_lot_id;
817 l_tran_rec.location := l_location;
818 l_tran_rec.doc_id := p_allocation_rec.doc_id;
819 l_tran_rec.doc_type := 'OMSO';
820 l_tran_rec.doc_line := p_allocation_rec.doc_line;
821 l_tran_rec.line_type := 0;
822 l_tran_rec.trans_date := p_allocation_rec.trans_date;
823 l_tran_rec.trans_qty := l_trans_qty1 * -1;
824 IF p_ic_item_mst.dualum_ind > 0 THEN
825 l_tran_rec.trans_qty2 := l_trans_qty2 * -1;
826 ELSE
827 l_tran_rec.trans_qty2 := NULL;
828 END IF;
829
830 IF p_ic_item_mst.grade_ctl > 0 THEN
831 l_tran_rec.qc_grade := l_qc_grade;
832 ELSE
833 l_tran_rec.qc_grade := NULL;
834 END IF;
835
836 IF p_ic_item_mst.lot_ctl > 0 THEN
837 l_tran_rec.lot_status := l_lot_status;
838 ELSE
839 l_tran_rec.lot_status := NULL;
840 END IF;
841 l_tran_rec.trans_um := p_ic_item_mst.item_um;
842 l_tran_rec.trans_um2 := p_ic_item_mst.item_um2;
843 l_tran_rec.non_inv := p_ic_item_mst.NONINV_IND;
844 l_tran_rec.create_lot_index := 0;
845 l_tran_rec.staged_ind := 0;
846 l_tran_rec.text_code := NULL;
847 l_tran_rec.user_id := p_allocation_rec.user_id;
848 l_tran_rec.line_detail_id := p_allocation_rec.line_detail_id;
849
850 GMI_Reservation_Util.PrintLn('(Alloc PVT) inserting trans ' );
851 GMI_SHIPPING_UTIL.print_debug(l_tran_rec,'inserting');
852 GMI_TRANS_ENGINE_PUB.create_pending_transaction
853 (p_api_version => 1.0,
854 p_init_msg_list => FND_API.G_TRUE,
855 p_commit => FND_API.G_FALSE,
856 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
857 p_tran_rec => l_tran_rec,
858 x_tran_row => l_tran_row,
859 x_return_status => l_return_status,
860 x_msg_count => l_msg_count,
861 x_msg_data => l_msg_data
862 );
863
864 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
865 CLOSE ic_inventory_view_c1;
866 RAISE FND_API.G_EXC_ERROR;
867 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
868 /* dbms_output.put_line('unexpected error back from txn engine'); */
869 CLOSE ic_inventory_view_c1;
870 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
871 END IF;
872 END IF; /* ===== END OF ALLOCATION */
873 END IF; /* result_flag = 1 OR l_grade_qcmatch_flag = 0*/
874 --2722339 EMC Auto Alloc QC Spec Match Project
875 result_flag :=0;
876 GMI_Reservation_Util.PrintLn('(Alloc PVT) end of try, result_flag = ' || result_flag);
877 END LOOP;
878 CLOSE ic_inventory_view_c1;
879
883 and reset quantities to their original values
880
881 /* dbms_output.put_line('End of allocation loop'); */
882 /*If partial allocation is NOT allowed, rollback any partial allocation
884 Also if the item is not lot/location controlled, it is not possible to
885 record a partial allocation; the whole quantity must be written against
886 the default lot/location.
887 ========================================================================*/
888
889 IF (l_unallocated_qty1 > 0 and (p_op_alot_prm.partial_ind = 0 AND (l_override_rules = 0 ) ) )
890 OR (l_unallocated_qty1 > 0 and p_ic_item_mst.lot_ctl = 0 AND l_loct_ctl = 0)
891 THEN
892 /* dbms_output.put_line */
893 /* ('Demand not fully met and partial alloc not allowed - special rollback'); */
894 GMI_Reservation_Util.PrintLn('(Alloc PVT) partial not allowed, roll back ');
895 GMI_Reservation_Util.PrintLn('(Alloc PVT) partial _ind'|| p_op_alot_prm.partial_ind);
896 GMI_Reservation_Util.PrintLn('(Alloc PVT) lot_ctl '|| p_ic_item_mst.lot_ctl);
897 GMI_Reservation_Util.PrintLn('(Alloc PVT) loct_ctl '|| l_loct_ctl);
898 ROLLBACK to allocate_line;
899 l_allocated_qty1 := 0;
900 l_allocated_qty2 := 0;
901 l_unallocated_qty1 := l_inventory_qty1;
902 l_unallocated_qty2 := p_allocation_rec.order_qty2;
903 END IF;
904
905 /* dbms_output.put_line('carry out final UM conversions'); */
906
907 /*Convert allocated qty to secondary um if necessary
908 ===================================================*/
909 IF l_allocated_qty1 > 0 AND p_ic_item_mst.dualum_ind > 0 THEN
910 /* dbms_output.put_line('convert allocated_qty to secondary'); */
911 l_lot_id := 0;
912 GMICUOM.icuomcv(pitem_id => p_ic_item_mst.item_id,
913 plot_id => l_lot_id,
914 pcur_qty => l_allocated_qty1,
915 pcur_uom => p_ic_item_mst.item_um,
916 pnew_uom => p_ic_item_mst.item_um2,
917 onew_qty => l_allocated_qty2);
918 l_unallocated_qty2 := p_allocation_rec.order_qty2 - l_allocated_qty2;
919 ELSIF l_allocated_qty1 = 0 AND p_ic_item_mst.dualum_ind > 0 THEN
920 l_unallocated_qty2 := p_allocation_rec.order_qty2;
921 ELSIF p_ic_item_mst.dualum_ind = 0 THEN
922 l_allocated_qty2 := NULL;
923 l_unallocated_qty2 := NULL;
924 END IF;
925 /*Convert allocated qty back to order_um if necessary
926 ====================================================*/
927 IF (l_allocated_qty1) > 0 AND
928 (p_allocation_rec.order_um1 <> p_ic_item_mst.item_um) THEN
929 l_lot_id := 0;
930 /* dbms_output.put_line('convert allocated_qty to order_um'); */
931 GMICUOM.icuomcv(pitem_id => p_ic_item_mst.item_id,
932 plot_id => l_lot_id,
933 pcur_qty => l_allocated_qty1,
934 pcur_uom => p_ic_item_mst.item_um,
935 pnew_uom => p_allocation_rec.order_um1,
936 onew_qty => x_allocated_qty1);
937 ELSE
938 x_allocated_qty1 := l_allocated_qty1;
939 END IF;
940 x_allocated_qty2 := l_allocated_qty2;
941
942 /*Adjust the DEFAULT LOT transaction to reflect unallocated_qty
943 =============================================================*/
944 IF p_allocation_rec.prefqc_grade IS NOT NULL THEN
945 l_allocation_rec.prefqc_grade := p_allocation_rec.prefqc_grade;
946 ELSE
947 l_allocation_rec.prefqc_grade := l_prm_prefqc_grade;
948 END IF;
949 GMI_ALLOCATE_INVENTORY_PVT.Balance_Default_Lot
950 (p_default_qty1 => l_unallocated_qty1 * -1,
951 p_default_qty2 => l_unallocated_qty2 * -1,
952 p_allocation_rec => p_allocation_rec,
953 p_ic_item_mst => p_ic_item_mst,
954 p_ic_whse_mst => p_ic_whse_mst,
955 x_return_status => l_return_status,
956 x_msg_count => l_msg_count,
957 x_msg_data => l_msg_data
958 );
959
960 GMI_Reservation_Util.PrintLn('(Alloc PVT) exiting, x_allocated_qty1 '|| x_allocated_qty1);
961 GMI_Reservation_Util.PrintLn('(Alloc PVT) exiting, x_allocated_qty2 '|| x_allocated_qty2);
962 /* EXCEPTION HANDLING
963 ====================*/
964
965 EXCEPTION
966
967 WHEN FND_API.G_EXC_ERROR THEN
968 x_return_status := FND_API.G_RET_STS_ERROR;
969 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
970 , p_count => x_msg_count
971 , p_data => x_msg_data
972 );
973
974 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
975 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
976 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
977 , p_count => x_msg_count
978 , p_data => x_msg_data
979 );
980 WHEN OTHERS THEN
981 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
982
983 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
984 , l_api_name
985 );
986
987 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
988 , p_count => x_msg_count
989 , p_data => x_msg_data
990 );
991
995 | PROCEDURE NAME |
992 END Allocate_Line;
993
994 /* +=========================================================================+
996 | Balance Default Lot |
997 | |
998 | USAGE |
999 | Ensure any unallocated qty is recorded against the DEFAULT LOT |
1000 | |
1001 | |
1002 | DESCRIPTION |
1003 | Ensure any unallocated qty is recorded against the DEFAULT LOT |
1004 | |
1005 | PARAMETERS |
1006 | |
1007 | HISTORY |
1008 | 15-DEC-1999 K.Y.Hunt Created |
1009 +=========================================================================+
1010 */
1011 PROCEDURE BALANCE_DEFAULT_LOT
1012 ( p_default_qty1 IN NUMBER
1013 , p_default_qty2 IN NUMBER
1014 , p_allocation_rec IN GMI_AUTO_ALLOCATE_PUB.gmi_allocation_rec
1015 , p_ic_item_mst IN ic_item_mst%ROWTYPE
1016 , p_ic_whse_mst IN ic_whse_mst%ROWTYPE
1017 , x_return_status OUT NOCOPY VARCHAR2
1018 , x_msg_count OUT NOCOPY NUMBER
1019 , x_msg_data OUT NOCOPY VARCHAR2
1020 )
1021 IS
1022 l_api_name CONSTANT VARCHAR2 (30) := 'BALANCE_DEFAULT_LOT';
1023 IC$DEFAULT_LOCT VARCHAR2(255);
1024 l_trans_id NUMBER;
1025 l_msg_count NUMBER :=0;
1026 l_msg_data VARCHAR2(2000);
1027 l_return_status VARCHAR2(1);
1028 l_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
1029 l_tran_row IC_TRAN_PND%ROWTYPE;
1030 l_default_trans ic_tran_pnd%ROWTYPE;
1031
1032 CURSOR ic_tran_pnd_c1 is
1033 SELECT /*+ INDEX (ic_tran_pnd, ic_tran_pndi3) */ *
1034 FROM ic_tran_pnd
1035 WHERE item_id = p_ic_item_mst.item_id AND
1036 line_id = p_allocation_rec.line_id AND
1037 lot_id = 0 AND
1038 doc_type= 'OMSO' AND
1039 completed_ind = 0 AND
1040 delete_mark = 0 AND
1041 location = IC$DEFAULT_LOCT;
1042
1043 BEGIN
1044
1045 /* dbms_output.put_line('start of balance_default_lot '); */
1046 /* dbms_output.put_line('one and two are ' || p_default_qty1 || ' and ' || p_default_qty2); */
1047 /*Initialize return status to success
1048 ====================================*/
1049 x_return_status := FND_API.G_RET_STS_SUCCESS;
1050
1051 IC$DEFAULT_LOCT := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
1052
1053 /* dbms_output.put_line('DEFAULT LOT retrieved as '|| IC$DEFAULT_LOCT); */
1054
1055 /*Determine whether or not a row exists for the DEFAULT LOT
1056 ==========================================================*/
1057 OPEN ic_tran_pnd_c1;
1058 FETCH ic_tran_pnd_c1 INTO l_default_trans;
1059 IF ic_tran_pnd_c1%NOTFOUND THEN
1060 CLOSE ic_tran_pnd_c1;
1061 /*Set up parameters in readiness for writing a pending transaction
1062 =================================================================*/
1063 l_tran_rec.trans_id := NULL;
1064 l_tran_rec.item_id := p_ic_item_mst.item_id;
1065 l_tran_rec.line_id := p_allocation_rec.line_id;
1066 l_tran_rec.co_code := p_allocation_rec.co_code;
1067 l_tran_rec.orgn_code := p_ic_whse_mst.orgn_code;
1068 l_tran_rec.whse_code := p_ic_whse_mst.whse_code;
1069 l_tran_rec.lot_id := 0;
1070 l_tran_rec.location := IC$DEFAULT_LOCT;
1071 l_tran_rec.doc_id := p_allocation_rec.doc_id;
1072 l_tran_rec.doc_type := 'OMSO';
1073 l_tran_rec.doc_line := p_allocation_rec.doc_line;
1074 l_tran_rec.line_type := 0;
1075 l_tran_rec.trans_date := p_allocation_rec.trans_date;
1076 l_tran_rec.trans_qty := p_default_qty1;
1077 IF p_ic_item_mst.dualum_ind > 0 THEN
1078 l_tran_rec.trans_qty2 := p_default_qty2;
1079 ELSE
1080 l_tran_rec.trans_qty2 := NULL;
1081 END IF;
1082
1083 /* If there is a preferred grade, target this within the transaction
1084 details. Grade may be used within planning in the future so it should
1085 be logged.
1086 =====================================================================*/
1087 IF p_allocation_rec.prefqc_grade is NOT NULL THEN
1088 l_tran_rec.qc_grade := p_allocation_rec.prefqc_grade;
1089 END IF;
1090
1091 /* Regardless of lot control attribute, set lot status to null for
1092 the DEFAULT posting. This will highlight the quantity as nettable
1093 for MRP purposes.
1094 ===================================================================*/
1095 l_tran_rec.lot_status := NULL;
1096 l_tran_rec.trans_um := p_ic_item_mst.item_um;
1097 l_tran_rec.trans_um2 := p_ic_item_mst.item_um2;
1098 l_tran_rec.staged_ind := 0;
1099 l_tran_rec.text_code := NULL;
1100 l_tran_rec.user_id := p_allocation_rec.user_id;
1101 l_tran_rec.non_inv := p_ic_item_mst.NONINV_IND;
1102 l_tran_rec.create_lot_index := 0;
1103 /* dbms_output.put_line('write a NEW default LOT txn'); */
1104 GMI_TRANS_ENGINE_PUB.create_pending_transaction
1108 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1105 (p_api_version => 1.0,
1106 p_init_msg_list => FND_API.G_TRUE,
1107 p_commit => FND_API.G_FALSE,
1109 p_tran_rec => l_tran_rec,
1110 x_tran_row => l_tran_row,
1111 x_return_status => l_return_status,
1112 x_msg_count => l_msg_count,
1113 x_msg_data => l_msg_data
1114 );
1115
1116 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1117 RAISE FND_API.G_EXC_ERROR;
1118 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1119 /* dbms_output.put_line('unexpected error return from txn engine'); */
1120 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1121 END IF;
1122 ELSE
1123 CLOSE ic_tran_pnd_c1;
1124
1125 /*This is a UPDATE scenario so ..........
1126 Set up parameters in readiness for updating the pending transaction
1127 ====================================================================*/
1128 l_tran_rec.trans_id := l_default_trans.trans_id;
1129 l_tran_rec.item_id := l_default_trans.item_id;
1130 l_tran_rec.line_id := l_default_trans.line_id;
1131 l_tran_rec.co_code := l_default_trans.co_code;
1132 l_tran_rec.orgn_code := l_default_trans.orgn_code;
1133 l_tran_rec.whse_code := l_default_trans.whse_code;
1134 l_tran_rec.lot_id := l_default_trans.lot_id;
1135 l_tran_rec.location := l_default_trans.location;
1136 l_tran_rec.doc_id := l_default_trans.doc_id;
1137 l_tran_rec.doc_type := 'OMSO';
1138 l_tran_rec.doc_line := l_default_trans.doc_line;
1139 l_tran_rec.line_type := l_default_trans.line_type;
1140 l_tran_rec.trans_date := l_default_trans.trans_date;
1141 l_tran_rec.trans_qty := p_default_qty1;
1142 IF p_ic_item_mst.dualum_ind > 0 THEN
1143 l_tran_rec.trans_qty2 := p_default_qty2;
1144 ELSE
1145 l_tran_rec.trans_qty2 := NULL;
1146 END IF;
1147
1148 IF p_ic_item_mst.grade_ctl > 0 THEN
1149 l_tran_rec.qc_grade := p_allocation_rec.prefqc_grade;
1150 ELSE
1151 l_tran_rec.qc_grade := NULL;
1152 END IF;
1153
1154 l_tran_rec.lot_status := p_ic_item_mst.lot_status;
1155 l_tran_rec.trans_um := l_default_trans.trans_um;
1156 l_tran_rec.trans_um2 := l_default_trans.trans_um2;
1157 l_tran_rec.staged_ind := l_default_trans.staged_ind;
1158 l_tran_rec.user_id := p_allocation_rec.user_id;
1159 l_tran_rec.non_inv := p_ic_item_mst.NONINV_IND;
1160 l_tran_rec.create_lot_index := 0;
1161 /* dbms_output.put_line('UPDATE the existing default LOT txn'); */
1162 GMI_TRANS_ENGINE_PUB.update_pending_transaction
1163 (p_api_version => 1.0,
1164 p_init_msg_list => FND_API.G_TRUE,
1165 p_commit => FND_API.G_FALSE,
1166 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1167 p_tran_rec => l_tran_rec,
1168 x_tran_row => l_tran_row,
1169 x_return_status => l_return_status,
1170 x_msg_count => l_msg_count,
1171 x_msg_data => l_msg_data
1172 );
1173 END IF;
1174
1175
1176 /* EXCEPTION HANDLING
1177 ====================*/
1178
1179 EXCEPTION
1180 WHEN FND_API.G_EXC_ERROR THEN
1181 x_return_status := FND_API.G_RET_STS_ERROR;
1182 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
1183 , p_count => x_msg_count
1184 , p_data => x_msg_data
1185 );
1186
1187 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1189 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1190 , p_count => x_msg_count
1191 , p_data => x_msg_data
1192 );
1193 WHEN OTHERS THEN
1194 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1195
1196 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1197 , l_api_name
1198 );
1199
1200 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1201 , p_count => x_msg_count
1202 , p_data => x_msg_data
1203 );
1204
1205 END Balance_Default_Lot;
1206
1207 /* +=========================================================================+
1208 | FUNCTION NAME |
1209 | Check_existing_allocations |
1210 | |
1211 | TYPE |
1212 | PRIVATE |
1213 | |
1214 | USAGE |
1215 | Used to determine whether allocations already exist against the |
1219 | This function checks ic_tran_pnd for outstanding allocations |
1216 | order/shipment line in question |
1217 | |
1218 | DESCRIPTION |
1220 | against the order/shipment line. |
1221 | |
1222 | PARAMETERS |
1223 | p_doc_id Surrogate key relating to the order/shipment |
1224 | p_line_id Surrogate key realting to the order line |
1225 | |
1226 | RETURNS |
1227 | BOOLEAN |
1228 | |
1229 | HISTORY |
1230 | 06-JAN-2000 Karen Y. Hunt Created |
1231 +=========================================================================+
1232 */
1233 FUNCTION Check_existing_allocations
1234 ( p_doc_id IN ic_tran_pnd.doc_id%TYPE
1235 , p_line_id IN ic_tran_pnd.line_id%TYPE
1236 , p_lot_ctl IN ic_item_mst.lot_ctl%TYPE
1237 , p_item_loct_ctl IN ic_item_mst.loct_ctl%TYPE
1238 , p_whse_loct_ctl IN ic_whse_mst.loct_ctl%TYPE
1239 )
1240 RETURN BOOLEAN
1241 IS
1242 l_api_name CONSTANT VARCHAR2 (30) := 'CHECK_EXISTING_ALLOCATIONS';
1243 l_sum_trans_qty NUMBER;
1244 IC$DEFAULT_LOCT VARCHAR2(255);
1245
1246 /* Scenario a) item has lot control */
1247 /* ================================ */
1248 CURSOR ic_tran_pnd_c1 IS
1249 SELECT /*+ INDEX (ic_tran_pnd, ic_tran_pndi3) */
1250 sum(trans_qty)
1251 FROM
1252 ic_tran_pnd
1253 WHERE
1254 doc_id = p_doc_id AND
1255 line_id = p_line_id AND
1256 doc_type = 'OMSO' AND
1257 lot_id > 0 AND
1258 -- completed_ind = 0 AND
1259 delete_mark = 0;
1260
1261 /* Scenario b) item has location control */
1262 /* ===================================== */
1263 CURSOR ic_tran_pnd_c2 IS
1264 SELECT /*+ INDEX (ic_tran_pnd, ic_tran_pndi3) */
1265 sum(trans_qty)
1266 FROM
1267 ic_tran_pnd
1268 WHERE
1269 doc_id = p_doc_id AND
1270 line_id = p_line_id AND
1271 location <>IC$DEFAULT_LOCT AND
1272 doc_type = 'OMSO' AND
1273 -- completed_ind = 0 AND
1274 delete_mark = 0;
1275
1276 /* Scenario c) item has NO lot or location control */
1277 /* =============================================== */
1278 CURSOR ic_tran_pnd_c3 IS
1279 SELECT /*+ INDEX (ic_tran_pnd, ic_tran_pndi3) */
1280 sum(trans_qty)
1281 FROM
1282 ic_tran_pnd
1283 WHERE
1284 doc_id = p_doc_id AND
1285 line_id = p_line_id AND
1286 doc_type = 'OMSO' AND
1287 -- completed_ind = 0 AND
1288 delete_mark = 0;
1289
1290
1291 BEGIN
1292 IF p_lot_ctl > 0 THEN
1293 /* dbms_output.put_line('check allocations for lot control '); */
1294 OPEN ic_tran_pnd_c1;
1295 FETCH ic_tran_pnd_c1 into l_sum_trans_qty;
1296 IF (ic_tran_pnd_c1%NOTFOUND) THEN
1297 l_sum_trans_qty :=0;
1298 END IF;
1299 CLOSE ic_tran_pnd_c1;
1300 ELSIF (p_item_loct_ctl > 0 OR p_whse_loct_ctl > 0) THEN
1301 /* dbms_output.put_line('check allocations for location control '); */
1302 IC$DEFAULT_LOCT := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
1303 OPEN ic_tran_pnd_c2;
1304 FETCH ic_tran_pnd_c2 into l_sum_trans_qty;
1305 IF (ic_tran_pnd_c2%NOTFOUND) THEN
1306 l_sum_trans_qty :=0;
1307 END IF;
1308 CLOSE ic_tran_pnd_c2;
1309 ELSE
1310 /* dbms_output.put_line('check allocations - no lot/loct control '); */
1311 OPEN ic_tran_pnd_c3;
1312 FETCH ic_tran_pnd_c3 into l_sum_trans_qty;
1313 IF (ic_tran_pnd_c3%NOTFOUND) THEN
1314 l_sum_trans_qty :=0;
1315 END IF;
1316 CLOSE ic_tran_pnd_c3;
1317 END IF;
1318
1319
1320 IF l_sum_trans_qty <> 0 THEN
1321 RETURN TRUE;
1322 ELSE
1323 RETURN FALSE;
1324 END IF;
1325
1326 EXCEPTION
1327 WHEN OTHERS THEN
1328 /* dbms_output.put_line('problem checking existing allocations '); */
1329 RAISE;
1330
1331 END Check_existing_allocations;
1332
1333 /* +=========================================================================+
1334 | FUNCTION NAME |
1335 | Allocations_Exist |
1336 | |
1337 | TYPE |
1338 | PRIVATE |
1339 | |
1340 | USAGE |
1341 | Used to determine whether allocations already exist against the |
1342 | order/shipment line in question |
1343 | |
1344 | DESCRIPTION |
1345 | This function checks ic_tran_pnd for outstanding allocations |
1346 | against the order/shipment line. |
1347 | |
1348 | PARAMETERS |
1349 | p_doc_id Surrogate key relating to the order/shipment |
1350 | p_line_id Surrogate key realting to the order line |
1351 | |
1352 | RETURNS |
1353 | BOOLEAN |
1354 | |
1355 | HISTORY |
1356 | 06-JAN-2000 Karen Y. Hunt Created |
1357 +=========================================================================+
1358 */
1359 FUNCTION Unstaged_Allocations_Exist
1360 ( p_doc_id IN ic_tran_pnd.doc_id%TYPE
1361 , p_line_id IN ic_tran_pnd.line_id%TYPE
1362 )
1363 RETURN BOOLEAN
1364 IS
1365 l_api_name CONSTANT VARCHAR2 (30) := 'ALLOCATIONS_EXIST';
1366 l_sum_trans_qty NUMBER;
1367 IC$DEFAULT_LOCT VARCHAR2(255) := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
1368
1369 CURSOR ic_tran_pnd_c1 IS
1370 SELECT /*+ INDEX (ic_tran_pnd, ic_tran_pndi3) */
1371 sum(trans_qty)
1372 FROM
1373 ic_tran_pnd
1374 WHERE
1375 doc_id = p_doc_id AND
1376 line_id = p_line_id AND
1377 doc_type = 'OMSO' AND
1378 ((lot_id > 0) OR
1379 (location <>IC$DEFAULT_LOCT)) AND
1380 staged_ind <> 1 AND
1381 doc_type = 'OMSO' AND
1382 completed_ind = 0 AND
1383 delete_mark = 0;
1384
1385 BEGIN
1386 OPEN ic_tran_pnd_c1;
1387 FETCH ic_tran_pnd_c1 into l_sum_trans_qty;
1388 IF (ic_tran_pnd_c1%NOTFOUND) THEN
1389 l_sum_trans_qty :=0;
1390 END IF;
1391
1392 IF l_sum_trans_qty <> 0 THEN
1393 RETURN TRUE;
1394 ELSE
1395 RETURN FALSE;
1396 END IF;
1397
1398 EXCEPTION
1399 WHEN OTHERS THEN
1400 RAISE;
1401
1402 END Unstaged_Allocations_Exist;
1403
1404 END GMI_ALLOCATE_INVENTORY_PVT;