[Home] [Help]
PACKAGE BODY: APPS.EAM_MATERIAL_VALIDATE_PUB
Source
1 PACKAGE BODY EAM_MATERIAL_VALIDATE_PUB AS
2 /* $Header: EAMPMTSB.pls 120.7 2006/04/06 23:51:46 gbadoni noship $ */
3
4
5 /***************************************************************************
6 --
7 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
8 -- All rights reserved.
9 --
10 -- FILENAME
11 --
12 -- EAMPMTSB.pls
13 --
14 -- DESCRIPTION
15 --
16 -- Body of package EAM_MATERIAL_VALIDATE_PUB
17 --
18 -- NOTES
19 --
20 -- HISTORY
21 --
22 -- 02-FEB-2005 Girish Rajan Initial Creation
23 ***************************************************************************/
24
25 /*******************************************************************
26 * Procedure : Get_Open_Qty
27 * Returns : Number
28 * Parameters IN : Required Quantity, Allocated Quantity, Issued Quantity
29 * Parameters OUT NOCOPY: Open Quantity
30 * Purpose : Calculate Open Quantity and return 0 if open quantity < 0
31 *********************************************************************/
32
33 FUNCTION Get_Open_Qty(p_required_quantity NUMBER, p_allocated_quantity NUMBER, p_issued_quantity NUMBER)
34 RETURN NUMBER
35 IS
36 l_open_qty NUMBER := 0;
37 BEGIN
38 l_open_qty := p_required_quantity - p_allocated_quantity - p_issued_quantity;
39 IF (l_open_qty < 0 ) THEN
40 RETURN 0;
41 END IF;
42 RETURN l_open_qty;
43 END Get_Open_Qty;
44
45
46 /*******************************************************************
47 * Procedure : Check_Shortage
48 * Returns : None
49 * Parameters IN : Wip Entity Id
50 * Parameters OUT NOCOPY: x_shortage_exists flag = 'Y' means there is material shortage
51 * = 'N' means there is no material shortage
52 * Purpose : For any given work order, this wrapper API will
53 * determine whether there is material shortage
54 * or not and then update that field at the work order
55 * level. API will return whether shortage
56 * exists in p_shortage_exists parameter.
57 *********************************************************************/
58
59 PROCEDURE Check_Shortage
60 (p_api_version IN NUMBER
61 , p_init_msg_lst IN VARCHAR2 := FND_API.G_FALSE
62 , p_commit IN VARCHAR2 := FND_API.G_FALSE
63 , x_return_status OUT NOCOPY VARCHAR2
64 , x_msg_count OUT NOCOPY NUMBER
65 , x_msg_data OUT NOCOPY VARCHAR2
66 , p_wip_entity_id IN NUMBER
67 , p_source_api IN VARCHAR2 DEFAULT null
68 , x_shortage_exists OUT NOCOPY VARCHAR2
69 )
70 IS
71 CURSOR get_materials_csr(p_wip_entity_id NUMBER) IS
72 SELECT wro.organization_id,
73 wro.wip_entity_id,
74 SUM(Get_Open_Qty(NVL(wro.required_quantity,0) ,
75 eam_material_allocqty_pkg.allocated_quantity(wro.wip_entity_id , wro.operation_seq_num,wro.organization_id,wro.inventory_item_id),
76 NVL(wro.quantity_issued,0))) open_quantity,
77 mtlbkfv.concatenated_segments inventory_item,
78 wro.inventory_item_id,
79 mtlbkfv.lot_control_code,
80 mtlbkfv.serial_number_control_code,
81 mtlbkfv.revision_qty_control_code
82 FROM mtl_system_items_b_kfv mtlbkfv,
83 wip_requirement_operations wro
84 WHERE wro.inventory_item_id=mtlbkfv.inventory_item_id
85 AND wro.organization_id = mtlbkfv.organization_id
86 AND wro.wip_entity_id = p_wip_entity_id
87 AND NVL(mtlbkfv.stock_enabled_flag,'N')='Y'
88 GROUP BY wro.organization_id,
89 wro.wip_entity_id,
90 wro.inventory_item_id,
91 mtlbkfv.concatenated_segments,
92 mtlbkfv.lot_control_code,
93 mtlbkfv.serial_number_control_code,
94 mtlbkfv.revision_qty_control_code;
95
96 CURSOR get_direct_items_csr(p_wip_entity_id NUMBER) IS
97 SELECT NVL(item_description, description) AS item_description,
98 SUM(Get_Open_Qty(required_quantity, quantity_received, 0)) open_quantity
99 FROM eam_direct_item_recs_v
100 WHERE wip_entity_id = p_wip_entity_id
101 GROUP BY item_description, description;
102
103 CURSOR wip_entity_name_csr(p_wip_entity_id NUMBER) IS
104 SELECT wip_entity_name
105 FROM wip_entities
106 WHERE wip_entity_id = p_wip_entity_id;
107
108 CURSOR get_yes_no(p_lookup_code NUMBER) IS
109 SELECT meaning
110 FROM mfg_lookups
111 WHERE lookup_type = 'EAM_YES_NO'
112 AND lookup_code = p_lookup_code
113 AND enabled_flag = 'Y'
114 AND (start_date_active is NULL OR sysdate >= start_date_active)
115 AND (end_date_active is NULL OR sysdate <= end_date_active);
116
117 CURSOR get_asset_number(p_wip_entity_id NUMBER) IS
118 SELECT instance_number
119 FROM csi_item_instances cii, wip_discrete_jobs wdj
120 WHERE decode(wdj.maintenance_object_type,3, wdj.maintenance_object_id,NULL) = cii.instance_id(+)
121 AND wdj.wip_entity_id = p_wip_entity_id;
122
123 l_is_revision_control BOOLEAN;
124 l_is_lot_control BOOLEAN;
125 l_is_serial_control BOOLEAN;
126 x_qoh NUMBER;
127 x_rqoh NUMBER;
128 x_qr NUMBER;
129 x_qs NUMBER;
130 x_att NUMBER;
131 x_atr NUMBER;
132 x_work_order_printed BOOLEAN :=FALSE;
133 x_tree_id NUMBER;
134 l_asset_sub_only BOOLEAN;
135 l_api_version CONSTANT NUMBER:=1;
136 l_api_name VARCHAR2(100);
137 l_unexpected_excep EXCEPTION;
138 l_wip_entity_name wip_entities.wip_entity_name%TYPE;
139 l_asset_number csi_item_instances.instance_number%TYPE;
140 l_yes_no mfg_lookups.meaning%TYPE;
141 BEGIN
142 l_asset_sub_only := FALSE;
143 l_api_name :='Check_Shortage';
144
145 -- Standard Start of API savepoint
146 SAVEPOINT Check_Shortage_Start;
147
148 IF NOT FND_API.Compatible_API_Call (l_api_version
149 ,p_api_version
150 ,l_api_name
151 ,G_PKG_NAME)
152 THEN
153 x_return_status := FND_API.g_ret_sts_unexp_error;
154 RAISE l_unexpected_excep;
155 END IF;
156
157 -- Check p_init_msg_list
158 IF FND_API.to_Boolean( p_init_msg_lst ) THEN
159 FND_MSG_PUB.initialize;
160 END IF;
161
162 -- Initialize API return status to success
163 x_return_status := FND_API.G_RET_STS_SUCCESS;
164 x_shortage_exists := 'N';
165
166 OPEN wip_entity_name_csr(p_wip_entity_id);
167 FETCH wip_entity_name_csr INTO l_wip_entity_name;
168 CLOSE wip_entity_name_csr;
169
170 OPEN get_asset_number(p_wip_entity_id);
171 FETCH get_asset_number INTO l_asset_number;
172 CLOSE get_asset_number;
173
174
175 FOR p_materials_csr IN get_materials_csr(p_wip_entity_id)
176 LOOP
177 x_att := 0;
178
179 IF (p_source_api = 'Concurrent') THEN
180 fnd_message.set_name('EAM','EAM_PROCESS_MATERIAL');
181 fnd_message.set_token('INVENTORY_ITEM',p_materials_csr.inventory_item,TRUE);
182 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
183 END IF;
184 IF (p_materials_csr.revision_qty_control_code = 2) THEN
185 l_is_revision_control:=TRUE;
186 ELSE
187 l_is_revision_control:=FALSE;
188 END IF;
189 IF (p_materials_csr.lot_control_code = 2) THEN
190 l_is_lot_control:=TRUE;
191 ELSE
192 l_is_lot_control:=FALSE;
193 END IF;
194 IF (p_materials_csr.serial_number_control_code = 1) THEN
195 l_is_serial_control:=FALSE;
196 ELSE
197 l_is_serial_control:=TRUE;
198 END IF;
199
200 IF (p_materials_csr.open_quantity > 0 ) THEN
201 Inv_Quantity_Tree_Grp.create_tree( p_api_version_number => p_api_version
202 , p_init_msg_lst => p_init_msg_lst
203 , x_return_status => x_return_status
204 , x_msg_count => x_msg_count
205 , x_msg_data => x_msg_data
206 , p_organization_id => p_materials_csr.organization_id
207 , p_inventory_item_id => p_materials_csr.inventory_item_id
208 , p_tree_mode => 2 -- available to transact
209 , p_is_revision_control => l_is_revision_control
210 , p_is_lot_control => l_is_lot_control
211 , p_is_serial_control => l_is_serial_control
212 , p_asset_sub_only => l_asset_sub_only
213 , x_tree_id => x_tree_id);
214
215 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
216 RAISE l_unexpected_excep;
217 END IF;
218
219 Inv_Quantity_Tree_Grp.query_tree
220 ( p_api_version_number => p_api_version
221 , p_init_msg_lst => p_init_msg_lst
222 , x_return_status => x_return_status
223 , x_msg_count => x_msg_count
224 , x_msg_data => x_msg_data
225 , p_tree_id => x_tree_id
226 , p_revision => null
227 , p_lot_number => null
228 , p_subinventory_code => null
229 , p_locator_id => null
230 , x_qoh => x_qoh
231 , x_rqoh => x_rqoh
232 , x_qr => x_qr
233 , x_qs => x_qs
234 , x_att => x_att
235 , x_atr => x_atr
236 , p_transfer_subinventory_code => null
237 );
238
239 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
240 RAISE l_unexpected_excep;
241 END IF;
242
243 END IF;
244 IF (p_materials_csr.open_quantity > x_att ) THEN
245 -- The update statement will be replaced by call to Work Order API
246 -- Waiting for necessary changed in Work Order API to be done
247 UPDATE eam_work_order_details
248 SET material_shortage_flag = 1,
249 material_shortage_check_date = sysdate,
250 last_update_date = sysdate,
251 last_updated_by = fnd_global.user_id,
252 last_update_login = fnd_global.login_id
253 WHERE wip_entity_id = p_wip_entity_id;
254 x_shortage_exists := 'Y';
255
256 IF (p_source_api = 'Concurrent') THEN
257 OPEN get_yes_no(1);
258 FETCH get_yes_no INTO l_yes_no;
259 CLOSE get_yes_no;
260
261 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<G_WORK_ORDER>');
262 IF (x_work_order_printed = FALSE) THEN
263 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<WORK_ORDER>' || l_wip_entity_name || '</WORK_ORDER>');
264 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ASSET_NUMBER>' || l_asset_number || '</ASSET_NUMBER>');
265 fnd_file.put_line(FND_FILE.OUTPUT, '<SHORTAGE_STATUS>' || l_yes_no|| '</SHORTAGE_STATUS>' );
266 x_work_order_printed :=TRUE;
267 END IF;
268 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<MATERIAL>' || p_materials_csr.inventory_item || '</MATERIAL>');
269 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<QUANTITY>' || to_char(p_materials_csr.open_quantity - x_att )|| '</QUANTITY>');
270 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</G_WORK_ORDER>');
271
272 ELSE
273 IF FND_API.To_Boolean( p_commit ) THEN
274 COMMIT;
275 END IF;
276 RETURN;
277 END IF;
278 END IF;
279 END LOOP;
280
281
282
283 FOR p_direct_items_csr IN get_direct_items_csr(p_wip_entity_id)
284 LOOP
285 IF (p_source_api = 'Concurrent') THEN
286 fnd_message.set_name('EAM','EAM_PROCESS_MATERIAL');
287 fnd_message.set_token('INVENTORY_ITEM',p_direct_items_csr.item_description,TRUE);
288 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
289 END IF;
290
291 IF (p_direct_items_csr.open_quantity > 0 ) THEN
292
293 -- The update statement will be replaced by call to Work Order API
294 -- Waiting for necessary changed in Work Order API to be done
295 UPDATE eam_work_order_details
296 SET material_shortage_flag = 1,
297 material_shortage_check_date = sysdate,
298 last_update_date = sysdate,
299 last_updated_by = fnd_global.user_id,
300 last_update_login = fnd_global.login_id
301 WHERE wip_entity_id = p_wip_entity_id;
302 x_shortage_exists := 'Y';
303
304 IF (p_source_api = 'Concurrent') THEN
305 OPEN get_yes_no(1);
306 FETCH get_yes_no INTO l_yes_no;
307 CLOSE get_yes_no;
308
309 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<G_WORK_ORDER>');
310
311 IF (x_work_order_printed = FALSE) THEN
312 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<WORK_ORDER>' || l_wip_entity_name || '</WORK_ORDER>');
313 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ASSET_NUMBER>' || l_asset_number || '</ASSET_NUMBER>');
314 fnd_file.put_line(FND_FILE.OUTPUT, '<SHORTAGE_STATUS>' || l_yes_no|| '</SHORTAGE_STATUS>' );
315 x_work_order_printed := TRUE;
316 END IF;
317
318 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<MATERIAL>' || p_direct_items_csr.item_description || '</MATERIAL>');
319 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<QUANTITY>' || p_direct_items_csr.open_quantity || '</QUANTITY>');
320 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</G_WORK_ORDER>');
321
322 ELSE
323 IF FND_API.To_Boolean( p_commit ) THEN
324 COMMIT;
325 END IF;
326 RETURN;
327 END IF;
328 END IF;
329 END LOOP;
330
331 IF (x_shortage_exists = 'N') THEN
332 OPEN get_yes_no(2);
333 FETCH get_yes_no INTO l_yes_no;
334 CLOSE get_yes_no;
335
336 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<G_WORK_ORDER>');
337 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<WORK_ORDER>' || l_wip_entity_name || '</WORK_ORDER>');
338 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ASSET_NUMBER>' || l_asset_number || '</ASSET_NUMBER>');
339 fnd_file.put_line(FND_FILE.OUTPUT, '<SHORTAGE_STATUS>' || l_yes_no||'</SHORTAGE_STATUS>' );
340 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</G_WORK_ORDER>');
341
342 UPDATE eam_work_order_details
343 SET material_shortage_flag = 2,
344 material_shortage_check_date = sysdate,
345 last_update_date = sysdate,
346 last_updated_by = fnd_global.user_id,
347 last_update_login = fnd_global.login_id
348 WHERE wip_entity_id = p_wip_entity_id;
349 END IF;
350
351 IF FND_API.To_Boolean( p_commit ) THEN
352 COMMIT;
353 END IF;
354
355 EXCEPTION
356 WHEN FND_API.G_EXC_ERROR THEN
357 ROLLBACK TO Check_Shortage_Start;
358 FND_MSG_PUB.Count_And_Get
359 (p_count => x_msg_count,
360 p_data => x_msg_data
361 );
362 x_return_status := FND_API.G_RET_STS_ERROR ;
363
364 IF (p_source_api = 'Concurrent') THEN
365 fnd_file.put_line(FND_FILE.LOG,x_msg_data);
366 -- FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<X_MSG_DATA>' || x_msg_data || '</X_MSG_DATA>');
367 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</G_WORK_ORDER>');
368 END IF;
369 x_shortage_exists := 'E';
370 UPDATE eam_work_order_details
371 SET material_shortage_flag = null,
372 material_shortage_check_date = sysdate,
373 last_update_date = sysdate,
374 last_updated_by = fnd_global.user_id,
375 last_update_login = fnd_global.login_id
376 WHERE wip_entity_id = p_wip_entity_id;
377 IF FND_API.To_Boolean( p_commit ) THEN
378 COMMIT;
379 END IF;
380
381 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
382 ROLLBACK TO Check_Shortage_Start;
383 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
384 FND_MSG_PUB.Count_And_Get
385 (p_count => x_msg_count,
386 p_data => x_msg_data
387 );
388 IF (p_source_api = 'Concurrent') THEN
389 fnd_file.put_line(FND_FILE.LOG,x_msg_data);
390 -- FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<X_MSG_DATA>' || x_msg_data || '</X_MSG_DATA>');
391 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</G_WORK_ORDER>');
392 END IF;
393 x_shortage_exists := 'E';
394 UPDATE eam_work_order_details
395 SET material_shortage_flag = null,
396 material_shortage_check_date = sysdate,
397 last_update_date = sysdate,
398 last_updated_by = fnd_global.user_id,
399 last_update_login = fnd_global.login_id
400 WHERE wip_entity_id = p_wip_entity_id;
401 IF FND_API.To_Boolean( p_commit ) THEN
402 COMMIT;
403 END IF;
404 WHEN OTHERS THEN
405 ROLLBACK TO Check_Shortage_Start;
406 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
407 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
408 FND_MSG_PUB.Add_Exc_Msg
409 (G_PKG_NAME,
410 l_api_name
411 );
412 END IF;
413 FND_MSG_PUB.Count_And_Get
414 (p_count => x_msg_count,
415 p_data => x_msg_data
416 );
417 IF (p_source_api = 'Concurrent') THEN
418 fnd_file.put_line(FND_FILE.LOG,x_msg_data);
419 -- FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<X_MSG_DATA>' || x_msg_data || '</X_MSG_DATA>');
420 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</G_WORK_ORDER>');
421 END IF;
422 x_shortage_exists := 'E';
423 UPDATE eam_work_order_details
424 SET material_shortage_flag = null,
425 material_shortage_check_date = sysdate,
426 last_update_date = sysdate,
427 last_updated_by = fnd_global.user_id,
428 last_update_login = fnd_global.login_id
429 WHERE wip_entity_id = p_wip_entity_id;
430 IF FND_API.To_Boolean( p_commit ) THEN
431 COMMIT;
432 END IF;
433 END Check_Shortage;
434
435 END EAM_MATERIAL_VALIDATE_PUB;