[Home] [Help]
PACKAGE BODY: APPS.EAM_MATERIAL_VALIDATE_PUB
Source
4
1 PACKAGE BODY EAM_MATERIAL_VALIDATE_PUB AS
2 /* $Header: EAMPMTSB.pls 120.19 2012/03/30 10:14:15 somitra ship $ */
3
5 /***************************************************************************
9 --
6 --
7 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
8 -- All rights reserved.
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 NVL(wro.required_quantity,0) required_quantity,
78 NVL(wro.quantity_issued,0) issued_quantity,
79 escapeXMLEam(mtlbkfv.concatenated_segments) inventory_item,
80 wro.inventory_item_id,
81 mtlbkfv.lot_control_code,
82 mtlbkfv.serial_number_control_code,
83 mtlbkfv.revision_qty_control_code
84 FROM mtl_system_items_b_kfv mtlbkfv,
85 wip_requirement_operations wro
86 WHERE wro.inventory_item_id=mtlbkfv.inventory_item_id
87 AND wro.organization_id = mtlbkfv.organization_id
88 AND wro.wip_entity_id = p_wip_entity_id
89 AND NVL(mtlbkfv.stock_enabled_flag,'N')='Y'
90 GROUP BY wro.organization_id,
91 wro.wip_entity_id,
92 wro.inventory_item_id,
93 mtlbkfv.concatenated_segments,
94 mtlbkfv.lot_control_code,
95 mtlbkfv.serial_number_control_code,
96 mtlbkfv.revision_qty_control_code,
97 NVL(wro.required_quantity,0) ,
98 NVL(wro.quantity_issued,0);
99
100 CURSOR get_direct_items_csr(p_wip_entity_id NUMBER) IS
101 SELECT NVL(escapeXMLEam(item_description),escapeXMLEam(description)) AS item_description,
102 SUM(Get_Open_Qty(required_quantity, quantity_received, 0)) open_quantity
103 FROM eam_direct_item_recs_v
104 WHERE wip_entity_id = p_wip_entity_id
105 GROUP BY item_description, description;
106
107 CURSOR wip_entity_name_csr(p_wip_entity_id NUMBER) IS
108 SELECT wip_entity_name
109 FROM wip_entities
110 WHERE wip_entity_id = p_wip_entity_id;
111
112 CURSOR get_yes_no(p_lookup_code NUMBER) IS
113 SELECT meaning
114 FROM mfg_lookups
115 WHERE lookup_type = 'EAM_YES_NO'
116 AND lookup_code = p_lookup_code
117 AND enabled_flag = 'Y'
118 AND (start_date_active is NULL OR sysdate >= start_date_active)
119 AND (end_date_active is NULL OR sysdate <= end_date_active);
120
121 CURSOR get_asset_number(p_wip_entity_id NUMBER) IS
122 SELECT escapeXMLEam(instance_number) instance_number --for bugfix 10192902
123 FROM csi_item_instances cii, wip_discrete_jobs wdj
124 WHERE decode(wdj.maintenance_object_type,3, wdj.maintenance_object_id,NULL) = cii.instance_id(+)
125 AND wdj.wip_entity_id = p_wip_entity_id;
126
127 l_is_revision_control BOOLEAN;
128 l_is_lot_control BOOLEAN;
129 l_is_serial_control BOOLEAN;
130 x_qoh NUMBER;
131 x_rqoh NUMBER;
132 x_qr NUMBER;
133 x_qs NUMBER;
134 x_att NUMBER;
135 x_atr NUMBER;
136 x_work_order_printed BOOLEAN :=FALSE;
137 x_tree_id NUMBER;
138 l_asset_sub_only BOOLEAN;
142 l_wip_entity_name wip_entities.wip_entity_name%TYPE;
139 l_api_version CONSTANT NUMBER:=1;
140 l_api_name VARCHAR2(100);
141 l_unexpected_excep EXCEPTION;
143 l_asset_number csi_item_instances.instance_number%TYPE;
144 l_yes_no mfg_lookups.meaning%TYPE;
145 BEGIN
146 l_asset_sub_only := FALSE;
147 l_api_name :='Check_Shortage';
148
149 -- Standard Start of API savepoint
150 SAVEPOINT Check_Shortage_Start;
151
152 IF NOT FND_API.Compatible_API_Call (l_api_version
153 ,p_api_version
154 ,l_api_name
155 ,G_PKG_NAME)
156 THEN
157 x_return_status := FND_API.g_ret_sts_unexp_error;
158 RAISE l_unexpected_excep;
159 END IF;
160
161 -- Check p_init_msg_list
162 IF FND_API.to_Boolean( p_init_msg_lst ) THEN
163 FND_MSG_PUB.initialize;
164 END IF;
165
166 -- Initialize API return status to success
167 x_return_status := FND_API.G_RET_STS_SUCCESS;
168 x_shortage_exists := 'N';
169
170 OPEN wip_entity_name_csr(p_wip_entity_id);
171 FETCH wip_entity_name_csr INTO l_wip_entity_name;
172 CLOSE wip_entity_name_csr;
173
174 OPEN get_asset_number(p_wip_entity_id);
175 FETCH get_asset_number INTO l_asset_number;
176 CLOSE get_asset_number;
177
178
179 FOR p_materials_csr IN get_materials_csr(p_wip_entity_id)
180 LOOP
181 x_att := 0;
182
183 IF (p_source_api = 'Concurrent') THEN
184 fnd_message.set_name('EAM','EAM_PROCESS_MATERIAL');
185 fnd_message.set_token('INVENTORY_ITEM',p_materials_csr.inventory_item,TRUE);
186 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
187 END IF;
188 IF (p_materials_csr.revision_qty_control_code = 2) THEN
189 l_is_revision_control:=TRUE;
190 ELSE
191 l_is_revision_control:=FALSE;
192 END IF;
193 IF (p_materials_csr.lot_control_code = 2) THEN
194 l_is_lot_control:=TRUE;
195 ELSE
196 l_is_lot_control:=FALSE;
197 END IF;
198 IF (p_materials_csr.serial_number_control_code = 1) THEN
199 l_is_serial_control:=FALSE;
200 ELSE
201 l_is_serial_control:=TRUE;
202 END IF;
203 --Bug 10149516.Negative ATT is calculated in this condition.That needs check too against an open quantity of 0.
204 --Bug 10234020.Changed if condition.Condition is failing when all qunatities are satisfied.
205 IF (p_materials_csr.required_quantity > p_materials_csr.issued_quantity ) THEN
206 inv_quantity_tree_pub.clear_quantity_cache;
207 Inv_Quantity_Tree_Grp.create_tree( p_api_version_number => p_api_version
208 , p_init_msg_lst => p_init_msg_lst
209 , x_return_status => x_return_status
210 , x_msg_count => x_msg_count
211 , x_msg_data => x_msg_data
212 , p_organization_id => p_materials_csr.organization_id
213 , p_inventory_item_id => p_materials_csr.inventory_item_id
214 , p_tree_mode => 2 -- available to transact
215 , p_is_revision_control => l_is_revision_control
216 , p_is_lot_control => l_is_lot_control
217 , p_is_serial_control => l_is_serial_control
218 , p_asset_sub_only => l_asset_sub_only
219 , x_tree_id => x_tree_id);
220
221 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
222 RAISE l_unexpected_excep;
223 END IF;
224
225 Inv_Quantity_Tree_Grp.query_tree
226 ( p_api_version_number => p_api_version
227 , p_init_msg_lst => p_init_msg_lst
228 , x_return_status => x_return_status
229 , x_msg_count => x_msg_count
230 , x_msg_data => x_msg_data
231 , p_tree_id => x_tree_id
232 , p_revision => null
233 , p_lot_number => null
234 , p_subinventory_code => null
235 , p_locator_id => null
236 , x_qoh => x_qoh
237 , x_rqoh => x_rqoh
238 , x_qr => x_qr
239 , x_qs => x_qs
240 , x_att => x_att
241 , x_atr => x_atr
242 , p_transfer_subinventory_code => null
243 );
244
245 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
246 RAISE l_unexpected_excep;
247 END IF;
248
249 END IF;
250 IF (p_materials_csr.open_quantity > x_att ) THEN
251 -- The update statement will be replaced by call to Work Order API
252 -- Waiting for necessary changed in Work Order API to be done
253 UPDATE eam_work_order_details
254 SET material_shortage_flag = 1,
255 material_shortage_check_date = sysdate,
256 last_update_date = sysdate,
257 last_updated_by = fnd_global.user_id,
258 last_update_login = fnd_global.login_id
259 WHERE wip_entity_id = p_wip_entity_id;
260 x_shortage_exists := 'Y';
261
262 IF (p_source_api = 'Concurrent') THEN
263 OPEN get_yes_no(1);
264 FETCH get_yes_no INTO l_yes_no;
265 CLOSE get_yes_no;
266
267 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<G_WORK_ORDER>');
268 IF (x_work_order_printed = FALSE) THEN
269 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<WORK_ORDER>' || l_wip_entity_name || '</WORK_ORDER>');
270 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ASSET_NUMBER>' || l_asset_number || '</ASSET_NUMBER>');
271 fnd_file.put_line(FND_FILE.OUTPUT, '<SHORTAGE_STATUS>' || l_yes_no|| '</SHORTAGE_STATUS>' );
275 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<QUANTITY>' || to_char(p_materials_csr.open_quantity - x_att )|| '</QUANTITY>');
272 x_work_order_printed :=TRUE;
273 END IF;
274 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<MATERIAL>' || p_materials_csr.inventory_item || '</MATERIAL>');
276 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</G_WORK_ORDER>');
277
278 ELSE
279 IF FND_API.To_Boolean( p_commit ) THEN
280 COMMIT;
281 END IF;
282 RETURN;
283 END IF;
284 END IF;
285 END LOOP;
286
287
288
289 FOR p_direct_items_csr IN get_direct_items_csr(p_wip_entity_id)
290 LOOP
291 IF (p_source_api = 'Concurrent') THEN
292 fnd_message.set_name('EAM','EAM_PROCESS_MATERIAL');
293 fnd_message.set_token('INVENTORY_ITEM',p_direct_items_csr.item_description,TRUE);
294 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
295 END IF;
296
297 IF (p_direct_items_csr.open_quantity > 0 ) THEN
298
299 -- The update statement will be replaced by call to Work Order API
300 -- Waiting for necessary changed in Work Order API to be done
301 UPDATE eam_work_order_details
302 SET material_shortage_flag = 1,
303 material_shortage_check_date = sysdate,
304 last_update_date = sysdate,
305 last_updated_by = fnd_global.user_id,
306 last_update_login = fnd_global.login_id
307 WHERE wip_entity_id = p_wip_entity_id;
308 x_shortage_exists := 'Y';
309
310 IF (p_source_api = 'Concurrent') THEN
311 OPEN get_yes_no(1);
312 FETCH get_yes_no INTO l_yes_no;
313 CLOSE get_yes_no;
314
315 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<G_WORK_ORDER>');
316
317 IF (x_work_order_printed = FALSE) THEN
318 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<WORK_ORDER>' || l_wip_entity_name || '</WORK_ORDER>');
319 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ASSET_NUMBER>' || l_asset_number || '</ASSET_NUMBER>');
320 fnd_file.put_line(FND_FILE.OUTPUT, '<SHORTAGE_STATUS>' || l_yes_no|| '</SHORTAGE_STATUS>' );
321 x_work_order_printed := TRUE;
322 END IF;
323
324 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<MATERIAL>' || p_direct_items_csr.item_description || '</MATERIAL>');
325 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<QUANTITY>' || p_direct_items_csr.open_quantity || '</QUANTITY>');
326 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</G_WORK_ORDER>');
327
328 ELSE
329 IF FND_API.To_Boolean( p_commit ) THEN
330 COMMIT;
331 END IF;
332 RETURN;
333 END IF;
334 END IF;
335 END LOOP;
336
337 IF (x_shortage_exists = 'N') THEN
338 IF (p_source_api = 'Concurrent') THEN --fix for 8840976
339 OPEN get_yes_no(2);
340 FETCH get_yes_no INTO l_yes_no;
341 CLOSE get_yes_no;
342
343 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<G_WORK_ORDER>');
344 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<WORK_ORDER>' || l_wip_entity_name || '</WORK_ORDER>');
345 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ASSET_NUMBER>' || l_asset_number || '</ASSET_NUMBER>');
346 fnd_file.put_line(FND_FILE.OUTPUT, '<SHORTAGE_STATUS>' || l_yes_no||'</SHORTAGE_STATUS>' );
347 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</G_WORK_ORDER>');
348 END IF;
349 UPDATE eam_work_order_details
350 SET material_shortage_flag = 2,
351 material_shortage_check_date = sysdate,
352 last_update_date = sysdate,
353 last_updated_by = fnd_global.user_id,
354 last_update_login = fnd_global.login_id
355 WHERE wip_entity_id = p_wip_entity_id;
356 END IF;
357
358 IF FND_API.To_Boolean( p_commit ) THEN
359 COMMIT;
360 END IF;
361
362 EXCEPTION
363 WHEN FND_API.G_EXC_ERROR THEN
364 ROLLBACK TO Check_Shortage_Start;
365 FND_MSG_PUB.Count_And_Get
366 (p_count => x_msg_count,
367 p_data => x_msg_data
368 );
369 x_return_status := FND_API.G_RET_STS_ERROR ;
370
371 IF (p_source_api = 'Concurrent') THEN
372 fnd_file.put_line(FND_FILE.LOG,x_msg_data);
373 -- FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<X_MSG_DATA>' || x_msg_data || '</X_MSG_DATA>');
374 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</G_WORK_ORDER>');
375 END IF;
376 x_shortage_exists := 'E';
377 UPDATE eam_work_order_details
378 SET material_shortage_flag = null,
379 material_shortage_check_date = sysdate,
380 last_update_date = sysdate,
381 last_updated_by = fnd_global.user_id,
382 last_update_login = fnd_global.login_id
383 WHERE wip_entity_id = p_wip_entity_id;
384 IF FND_API.To_Boolean( p_commit ) THEN
385 COMMIT;
386 END IF;
387
388 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
389 ROLLBACK TO Check_Shortage_Start;
390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
391 FND_MSG_PUB.Count_And_Get
392 (p_count => x_msg_count,
393 p_data => x_msg_data
394 );
395 IF (p_source_api = 'Concurrent') THEN
396 fnd_file.put_line(FND_FILE.LOG,x_msg_data);
397 -- FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<X_MSG_DATA>' || x_msg_data || '</X_MSG_DATA>');
398 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</G_WORK_ORDER>');
399 END IF;
400 x_shortage_exists := 'E';
401 UPDATE eam_work_order_details
402 SET material_shortage_flag = null,
403 material_shortage_check_date = sysdate,
404 last_update_date = sysdate,
405 last_updated_by = fnd_global.user_id,
406 last_update_login = fnd_global.login_id
407 WHERE wip_entity_id = p_wip_entity_id;
408 IF FND_API.To_Boolean( p_commit ) THEN
409 COMMIT;
410 END IF;
411 WHEN OTHERS THEN
412 ROLLBACK TO Check_Shortage_Start;
413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
414 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
415 FND_MSG_PUB.Add_Exc_Msg
416 (G_PKG_NAME,
417 l_api_name
418 );
419 END IF;
420 FND_MSG_PUB.Count_And_Get
421 (p_count => x_msg_count,
422 p_data => x_msg_data
423 );
424 IF (p_source_api = 'Concurrent') THEN
425 fnd_file.put_line(FND_FILE.LOG,x_msg_data);
426 -- FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<X_MSG_DATA>' || x_msg_data || '</X_MSG_DATA>');
427 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</G_WORK_ORDER>');
428 END IF;
429 x_shortage_exists := 'E';
430 UPDATE eam_work_order_details
431 SET material_shortage_flag = null,
432 material_shortage_check_date = sysdate,
433 last_update_date = sysdate,
434 last_updated_by = fnd_global.user_id,
435 last_update_login = fnd_global.login_id
436 WHERE wip_entity_id = p_wip_entity_id;
437 IF FND_API.To_Boolean( p_commit ) THEN
438 COMMIT;
439 END IF;
440 END Check_Shortage;
441
442 /*Added this function for bug 9827868*/
443
444 FUNCTION escapeXMLEam(str VARCHAR2) RETURN VARCHAR2
445 IS
446 ret_str VARCHAR2(4000) := str;
447 BEGIN
448 ret_str := REPLACE(ret_str, '&','&');
449 ret_str := REPLACE(ret_str, '<','<');
450 ret_str := REPLACE(ret_str, '>','>');
451 ret_str := REPLACE(ret_str, '"','"');
452 ret_str := REPLACE(ret_str, '''',''');
453
454 return ret_str;
455
456 END escapeXMLEam;
457
458 END EAM_MATERIAL_VALIDATE_PUB;