1 PACKAGE BODY GMI_Move_Order_LINES_PVT AS
2 /* $Header: GMIVMOLB.pls 115.20 2004/02/05 17:04:30 lswamy ship $ */
3 /* +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | FILENAME |
9 | GMIVMOLB.pls |
10 | |
11 | DESCRIPTION |
12 | This package contains Private Routines relating to GMI |
13 | Move Order LINES. |
14 | |
15 | |
16 | |
17 | HISTORY |
18 | 03-MAY-2000 Hverddin Created |
19 | |
20 +=========================================================================+
21 API Name : GMI_Move_Order_LINES_PVT
22 Type : Global
23 -
24 Pre-reqs : N/A
25 Parameters: Per function
26
27 Current Vers : 1.0
28 */
29
30 G_PKG_NAME CONSTANT VARCHAR2(30):='GMI_MOVE_ORDER_LINES_PVT';
31
32 PROCEDURE Process_Move_Order_LINES
33 (
34 p_api_version_number IN NUMBER
35 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
36 , p_validation_flag IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
37 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
38 , p_mo_line_tbl IN GMI_Move_Order_Global.MO_LINE_TBL
39 , x_mo_line_tbl OUT NOCOPY GMI_Move_Order_Global.MO_LINE_TBL
40 , x_return_status OUT NOCOPY VARCHAR2
41 , x_msg_count OUT NOCOPY NUMBER
42 , x_msg_data OUT NOCOPY VARCHAR2
43 )
44 IS
45 l_api_name CONSTANT VARCHAR2 (30) := 'PROCESS_MOVE_ORDER_LINES';
46 l_api_version_number CONSTANT NUMBER := 1.0;
47 l_msg_count NUMBER :=0;
48 l_msg_data VARCHAR2(2000);
49 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
50 l_mo_line_rec GMI_MOVE_ORDER_GLOBAL.mo_line_rec;
51 l_mo_line_tbl GMI_MOVE_ORDER_GLOBAL.mo_line_tbl;
52 l_opm_item_id NUMBER;
53 l_opm_noninv_ind NUMBER;
54 l_opm_lot_ctl NUMBER;
55 l_opm_loct_ctl NUMBER;
56 l_def_count NUMBER := 0;
57 l_whse_code VARCHAR2(4);
58 l_default_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
59
60 CURSOR get_opm_item IS
61 Select distinct ic.item_id ,
62 ic.noninv_ind,
63 ic.lot_ctl,
64 ic.loct_ctl
65 From ic_item_mst ic,
66 mtl_system_items mtl
67 Where mtl.inventory_item_id = l_mo_line_rec.inventory_item_id
68 And mtl.segment1= ic.item_no;
69
70 CURSOR get_detailed_qtys IS
71 Select sum(abs(trans_qty)), sum(abs(trans_qty2))
72 From ic_tran_pnd
73 Where line_id = l_mo_line_rec.txn_source_line_id
74 -- And item_id = l_opm_item_id (commenting this line so that index on line_id gets used)
75 And lot_id <> 0
76 And doc_type = 'OMSO'
77 And delete_mark = 0
78 And completed_ind = 0;
79
80 CURSOR get_default_detailed_qtys IS
81 Select sum(abs(trans_qty)), sum(abs(trans_qty2))
82 From ic_tran_pnd
83 Where line_id = l_mo_line_rec.txn_source_line_id
84 -- And item_id = l_opm_item_id (commenting this line so that index on line_id gets used)
85 And lot_id = 0
86 And doc_type = 'OMSO'
87 And delete_mark = 0
88 And completed_ind = 0;
89
90 CURSOR mo_line_exist IS
91 Select line_id
92 From ic_txn_request_lines
93 Where txn_source_line_id = l_mo_line_rec.txn_source_line_id;
94
95 IC$DEFAULT_LOCT VARCHAR2(255) := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
96 l_trans_id NUMBER;
97
98 /* Uday Phadtare Bug 2973135 */
99 CURSOR get_default_trans(p_line_id NUMBER, p_item_id NUMBER) IS
100 SELECT count(*)
101 FROM ic_tran_pnd
102 WHERE line_id = p_line_id
103 AND doc_type = 'OMSO'
104 -- AND item_id = p_item_id (commenting this line so that index on line_id gets used)
105 AND staged_ind = 0
106 AND completed_ind = 0
107 AND delete_mark = 0
108 AND lot_id = 0
109 AND location = ic$default_loct
110 AND line_detail_id IS NULL;
111
112 /* Uday Phadtare Bug 2973135 */
113 -- Get whse information
114 CURSOR get_whse_code( p_organization_id NUMBER ) IS
115 SELECT whse_code
116 FROM IC_WHSE_MST
117 WHERE mtl_organization_id = p_organization_id;
118
119 BEGIN
120 /* Standard Start OF API savepoint */
121 SAVEPOINT move_order_LINES;
122 gmi_reservation_util.println('In move_order_lines_pvt');
123
124 /* DBMS_OUTPUT.PUT_LINE('IN MOVE ORDER LINES'); */
125
126 /* Standard call to check for call compatibility. */
127
128 IF NOT FND_API.Compatible_API_CALL ( l_api_version_number
129 , p_api_version_number
130 , l_api_name
131 , G_PKG_NAME
132 )
133 THEN
134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
135 END IF;
136
137 /* Initialize message list if p_int_msg_lst is set TRUE. */
138 IF FND_API.to_boolean(p_init_msg_lst)
139 THEN
140 FND_MSG_PUB.Initialize;
141 END IF;
142 /* Initialize API return status to sucess */
143
144 x_return_status := FND_API.G_RET_STS_SUCCESS;
145
146 WSH_Util_Core.PrintLn('Move_Order_LINES PVT COUNT=> '||p_mo_line_tbl.COUNT);
147 gmi_reservation_util.PrintLn('Move_Order_LINES PVT COUNT=> '||p_mo_line_tbl.COUNT);
148
149 FOR I in 1..p_mo_line_TBL.COUNT LOOP
150 gmi_reservation_util.println('In loop');
151 l_mo_line_rec := p_mo_line_tbl(I);
152 WSH_Util_Core.PrintLn('move order line for source line_id > '|| l_mo_line_rec.txn_source_line_id );
153 WSH_Util_Core.PrintLn('Check Missing For Row > '|| I );
154 IF check_required( p_mo_line_rec => l_mo_line_rec) THEN
155 FND_MESSAGE.SET_NAME('GMI','Required Values Missing In Lines');
156 RAISE FND_API.G_EXC_ERROR;
157 END IF;
158 /* Need To Set l_trolin_rec.primary_quantity */
159 /* Initially Lets Set it to Requested Quantity ( quantity) */
160 /* Since they should be the same. */
161 l_mo_line_rec.primary_quantity := l_mo_line_rec.quantity;
162 WSH_Util_Core.PrintLn('Operation Row ==> '||l_mo_line_rec.operation);
163 IF l_mo_line_rec.operation = INV_GLOBALS.G_OPR_DELETE THEN
164 /* physically delete this row */
165 /* This first Queries To see If Any Reservations Exist */
166 /* ( GMI Transcations) Then deletes or Updates. */
167 /* Else it will just delete this line. */
168 gmi_reservation_util.println('Operation delete');
169 GMI_Move_Order_LINE_Util.delete_Row( l_mo_line_rec.LINE_id);
170 ELSE
171 gmi_reservation_util.println('Going to fetch opm_item');
172 Open get_opm_item;
173 Fetch get_opm_item Into l_opm_item_id,
174 l_opm_noninv_ind,
175 l_opm_lot_ctl,
176 l_opm_loct_ctl;
177 Close get_opm_item;
178
179 IF l_mo_line_rec.operation = INV_GLOBALS.G_OPR_UPDATE THEN
180 gmi_reservation_util.println('Operation update');
181 IF (l_opm_lot_ctl <> 0 OR l_opm_loct_ctl <> 0) THEN
182 gmi_reservation_util.println('Going to fetch get_detailed_qtys ');
183 Open get_detailed_qtys;
184 Fetch get_detailed_qtys Into l_mo_line_rec.quantity_detailed,
185 l_mo_line_rec.secondary_quantity_detailed;
186 Close get_detailed_qtys;
187
188 -- Bug 1987780, Sept-2001, odaboval added the delivered qty :
189 IF ( NVL(l_mo_line_rec.quantity_delivered,0) > NVL(l_mo_line_rec.quantity_detailed, 0) )
190 THEN
191 l_mo_line_rec.quantity_delivered := l_mo_line_rec.quantity_detailed;
192 l_mo_line_rec.secondary_quantity_delivered := l_mo_line_rec.secondary_quantity_detailed;
193 END IF;
194 ELSE
195 gmi_reservation_util.println('Going to fetch get_default_detailed_qtys');
196 Open get_default_detailed_qtys;
197 Fetch get_default_detailed_qtys Into l_mo_line_rec.quantity_detailed,
198 l_mo_line_rec.secondary_quantity_detailed;
199 Close get_default_detailed_qtys;
200 END IF;
201 END IF;
202
203 /* Is This correct To Set Status Date */
204 l_mo_line_rec.status_date := NVL(l_mo_line_rec.status_date,SYSDATE);
205
206 /* Set Generic defaults */
207 l_mo_line_rec.last_update_date := SYSDATE;
208 l_mo_line_rec.last_updated_by := FND_GLOBAL.USER_ID;
209 l_mo_line_rec.last_update_login := FND_GLOBAL.USER_ID;
210
211 IF l_mo_line_rec.operation = INV_GLOBALS.G_OPR_UPDATE THEN
212 /* This will first check if old_line quantity or line_status */
213 /* or quantity_detailed is different from New Then Call */
214 /* query to get reservations and do update logic. */
215 /* Else it will just update this row. */
216 gmi_reservation_util.println('Going to update row');
217 GMI_Move_Order_LINE_Util.update_Row( l_mo_line_rec);
218 ELSIF l_mo_line_rec.operation = INV_GLOBALS.G_OPR_CREATE THEN
219 /* Set create defaults */
220 gmi_reservation_util.println('Going to get get new id');
221 l_mo_line_rec.creation_date := SYSDATE;
222 l_mo_line_rec.created_by := FND_GLOBAL.USER_ID;
223 /* Get New LINES Id Via Sequence */
224 -- BEGIN Bug 2628244 - Use of sequence MTL_TXN_REQUEST_LINES_S instead of gmi_mo_LINE_id_s
225 --select gmi_mo_LINE_id_s.nextval
226 select MTL_TXN_REQUEST_LINES_S.nextval
227 -- END Bug 2628244
228 INTO l_mo_line_rec.LINE_id
229 FROM DUAL;
230
231 WSH_Util_Core.PrintLn('Insert For Row > '|| I);
232 gmi_reservation_util.println('Going to intsert line in move order ');
233 GMI_Move_Order_LINE_Util.Insert_Row( l_mo_line_rec);
234
235 /* Begin Bug 2973135 Uday Phadtare */
236 --Check if default transaction exists
237 OPEN get_default_trans(l_mo_line_rec.txn_source_line_id, l_opm_item_id);
238 FETCH get_default_trans into l_def_count;
239 CLOSE get_default_trans;
240
241 /* create a default lot transaction from scratch if it does not exist */
242 IF l_def_count = 0 THEN
243 OPEN get_whse_code(l_mo_line_rec.organization_id);
244 FETCH get_whse_code INTO l_whse_code;
245 CLOSE get_whse_code;
246
247 GMI_Reservation_Util.PrintLn('Transaction with default : NOTFOUND ');
248 GMI_RESERVATION_UTIL.Create_dflt_lot_from_scratch
249 ( p_whse_code => l_whse_code
250 , p_line_id => l_mo_line_rec.txn_source_line_id
251 , p_item_id => l_opm_item_id
252 , p_qty1 => l_mo_line_rec.primary_quantity
253 , p_qty2 => l_mo_line_rec.secondary_quantity
254 , x_return_status => l_return_status
255 , x_msg_count => x_msg_count
256 , x_msg_data => x_msg_data
257 );
258
259 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
260 GMI_reservation_Util.PrintLn('Error returned by Create_dflt_lot_from_scratch in Process_Move_Order_LINES');
261 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
262 FND_MESSAGE.SET_TOKEN('BY_PROC','GMI_Reservation_Util.create_dflt_lot_from_scratch');
263 FND_MESSAGE.SET_TOKEN('WHERE',G_PKG_NAME||'.'||l_api_name);
264 RAISE FND_API.G_EXC_ERROR;
265 END IF;
266
267 GMI_RESERVATION_UTIL.find_default_lot
268 ( x_return_status => x_return_status,
269 x_msg_count => x_msg_count,
270 x_msg_data => x_msg_data,
271 x_reservation_id => l_trans_id,
272 p_line_id => l_mo_line_rec.txn_source_line_id
273 );
274 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
275 GMI_RESERVATION_UTIL.println('Error returned by find default lot');
276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
277 END IF;
278
279 l_default_tran_rec.trans_id := l_trans_id;
280
281 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND(l_default_tran_rec, l_default_tran_rec) THEN
282 GMI_RESERVATION_UTIL.PrintLn('balancing default lot for line_id '|| l_mo_line_rec.txn_source_line_id);
283 GMI_RESERVATION_UTIL.balance_default_lot
284 ( p_ic_default_rec => l_default_tran_rec
285 , p_opm_item_id => l_default_tran_rec.item_id
286 , x_return_status => x_return_status
287 , x_msg_count => x_msg_count
288 , x_msg_data => x_msg_data
289 );
290 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
291 GMI_reservation_Util.PrintLn('Error returned by balance_default_lot in Process_Move_Order_LINES');
292 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
293 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
294 FND_MESSAGE.Set_Token('WHERE', 'Delete_Reservation');
295 FND_MSG_PUB.Add;
296 RAISE FND_API.G_EXC_ERROR;
297 END IF;
298 END IF;
299 END IF;
300 /* End Bug 2973135 Uday Phadtare */
301
302 END IF;
303 END IF;
304
305 l_mo_line_tbl(I) := l_mo_line_rec;
306 gmi_reservation_util.println('Value of line id for l_mo_line_rec is '||l_mo_line_rec.line_id);
307 END LOOP;
308
309 /* Load Output table */
310
311 x_mo_line_tbl := l_mo_line_tbl;
312
313 WSH_Util_Core.PrintLn('Count MOL Table => '|| x_mo_line_tbl.COUNT);
314
315 /* FND_MESSAGE.Set_Name('GMI','Entering_GMI_Create_Move_Order_LINES'); */
316 /* FND_MSG_PUB.Add; */
317 /* RAISE FND_API.G_EXC_ERROR; */
318
319 EXCEPTION
320 WHEN fnd_api.g_exc_error THEN
321 x_return_status := fnd_api.g_ret_sts_error;
322
323 /* Get message count and data */
324 FND_MSG_PUB.count_and_get
325 ( p_count => x_msg_count
326 , p_data => x_msg_data
327 );
328
329 WHEN OTHERS THEN
330 x_return_status := fnd_api.g_ret_sts_error;
331
332 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
333 , l_api_name
334 );
335
336
337 /* Get message count and data */
338 FND_MSG_PUB.count_and_get
339 ( p_count => x_msg_count
340 , p_data => x_msg_data
341 );
342
343
344
345 END Process_Move_Order_LINES;
346
347 FUNCTION check_required
348 (
349 p_mo_line_rec IN GMI_MOVE_ORDER_GLOBAL.mo_line_rec
350 )
351 RETURN BOOLEAN
352 IS
353 BEGIN
354
355 WSH_Util_Core.PrintLn('Operation Row ==> '||p_mo_line_rec.operation);
356 WSH_Util_Core.PrintLn('header id ==> '||p_mo_line_rec.header_id);
357 WSH_Util_Core.PrintLn('line num ==> '||p_mo_line_rec.line_number);
358 WSH_Util_Core.PrintLn('org id ==> '||p_mo_line_rec.organization_id);
359 WSH_Util_Core.PrintLn('Item id ==> '||p_mo_line_rec.inventory_item_id);
360 WSH_Util_Core.PrintLn('uom code ==> '||p_mo_line_rec.uom_code);
361 WSH_Util_Core.PrintLn('quantity ==> '||p_mo_line_rec.quantity);
362 WSH_Util_Core.PrintLn('status ==> '||p_mo_line_rec.line_status);
363 WSH_Util_Core.PrintLn('trans id ==> '||p_mo_line_rec.transaction_type_id);
364
365 IF ( p_mo_line_rec.operation = INV_GLOBALS.G_OPR_CREATE) THEN
366
367 IF p_mo_line_rec.header_id is NULL OR
368 p_mo_line_rec.line_number is NULL OR
369 p_mo_line_rec.organization_id is NULL OR
370 p_mo_line_rec.inventory_item_id is NULL OR
371 p_mo_line_rec.uom_code is NULL OR
372 p_mo_line_rec.quantity is NULL OR
373 p_mo_line_rec.line_status is NULL OR
374 p_mo_line_rec.transaction_type_id is NULL THEN
375
376 RETURN TRUE;
377
378 ELSE
379 RETURN FALSE;
380
381 END IF;
382
383
384 ELSIF ( p_mo_line_rec.operation = INV_GLOBALS.G_OPR_UPDATE) THEN
385
386 IF p_mo_line_rec.header_id is NULL OR
387 p_mo_line_rec.line_id is NULL OR
388 p_mo_line_rec.line_number is NULL OR
389 p_mo_line_rec.organization_id is NULL OR
390 p_mo_line_rec.inventory_item_id is NULL OR
391 p_mo_line_rec.uom_code is NULL OR
392 p_mo_line_rec.quantity is NULL OR
393 p_mo_line_rec.line_status is NULL OR
394 p_mo_line_rec.transaction_type_id is NULL THEN
395
396 RETURN TRUE;
397
398 ELSE
399 RETURN FALSE;
400
401 END IF;
402
403 /* This should Catch DELETE, LOCK_ROW and QUERY */
404 /* Which all need a LINES ID. */
405 ELSE
406
407 IF p_mo_line_rec.header_id is NULL OR
408 p_mo_line_rec.LINE_id is NULL THEN
409 RETURN TRUE;
410 ELSE
411 RETURN FALSE;
412 END IF;
413
414 END IF;
415
416 RETURN TRUE;
417
418
419 EXCEPTION
420 WHEN OTHERS THEN
421 FND_MESSAGE.SET_NAME('GMI','UNEXPECTED ERROR CHECK MISSING');
422 RETURN TRUE;
423
424 END CHECK_REQUIRED;
425
426
427 END GMI_Move_Order_LINES_PVT;
[Home] [Help]