[Home] [Help]
PACKAGE BODY: APPS.INV_SHORTCHECKEXEC_PVT
Source
1 PACKAGE BODY INV_ShortCheckExec_PVT AS
2 /* $Header: INVSEPVB.pls 120.12 2007/12/03 09:25:32 aambulka ship $*/
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_ShortCheckExec_PVT';
4 -- Start OF comments
5 -- API name : ExecCheck
6 -- TYPE : Private
7 -- Pre-reqs : None
8 -- FUNCTION :
9 -- Parameters:
10 -- IN :
11 -- p_api_version IN NUMBER (required)
12 -- API Version of this procedure
13 --
14 -- p_init_msg_list IN VARCHAR2 (optional)
15 -- DEFAULT = FND_API.G_FALSE,
16 --
17 -- p_commit IN VARCHAR2 (optional)
18 -- DEFAULT = FND_API.G_FALSE
19 --
20 --
21 -- OUT NOCOPY /* file.sql.39 change */ :
22 -- x_return_status OUT NOCOPY /* file.sql.39 change */ NUMBER
23 -- Result of all the operations
24 --
25 -- x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
26 --
27 -- x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
28 --
29 -- Version: Current Version 1.0
30 -- Changed : Nothing
31 -- No Previous Version 0.0
32 -- Initial version 1.0
33 -- Notes :
34 -- END OF comments
35 PROCEDURE ExecCheck (
36 p_api_version IN NUMBER ,
37 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
38 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
39 x_return_status IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
40 x_msg_count IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
41 x_msg_data IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
42 p_sum_detail_flag IN NUMBER,
43 p_organization_id IN NUMBER,
44 p_inventory_item_id IN NUMBER,
45 p_comp_att_qty_flag IN NUMBER,
46 p_primary_quantity IN NUMBER DEFAULT 0,
47 x_seq_num IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
48 x_check_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2
49 )
50 IS
51 L_api_version CONSTANT NUMBER := 1.0;
52 L_api_name CONSTANT VARCHAR2(30) := 'ExecCheck';
53 L_WIP_short_quantity NUMBER;
54 L_OE_short_quantity NUMBER;
55 L_Statement_not_found EXCEPTION;
56 L_count NUMBER; -- Added for Bug #4474266
57
58
59 -- Bug #4474266 Added the following cursor
60 CURSOR L_Quantity_crs (p_organization_id IN NUMBER,
61 p_seq_num IN NUMBER) IS
62 SELECT sum(quantity_open) short_quantity,
63 inventory_item_id
64 FROM mtl_short_chk_temp
65 WHERE organization_id = p_organization_id
66 AND seq_num = p_seq_num -- Bug 5081665: filter on seq_num
67 group by inventory_item_id;
68
69 --
70 PROCEDURE ExecStatement ( p_organization_id IN NUMBER,
71 p_inventory_item_id IN NUMBER,
72 p_sum_detail_flag IN NUMBER,
73 x_seq_num OUT NOCOPY /* file.sql.39 change */ NUMBER,
74 x_wip_short_quantity OUT NOCOPY /* file.sql.39 change */ NUMBER,
75 x_oe_short_quantity OUT NOCOPY /* file.sql.39 change */ NUMBER )
76 IS
77 L_Statement LONG;
78 L_ExecStatement_crs NUMBER;
79 L_RowsProcessed NUMBER;
80 --
81 -- Cursor for shortage statements
82 CURSOR L_Statement_crs ( p_organization_id IN NUMBER,
83 p_sum_detail_flag IN NUMBER ) IS
84 SELECT short_statement
85 FROM mtl_short_chk_statements
86 WHERE organization_id = p_organization_id
87 AND detail_sum_flag = p_sum_detail_flag;
88 --
89 BEGIN
90 -- Get statement
91 OPEN L_Statement_crs ( p_organization_id,
92 p_sum_detail_flag );
93 FETCH L_Statement_crs INTO L_Statement;
94 IF L_Statement_crs%NOTFOUND THEN
95 RAISE L_Statement_not_found;
96 END IF;
97 CLOSE L_Statement_crs;
98 --
99 -- Execute statement
100 L_ExecStatement_crs := dbms_sql.open_cursor;
101 dbms_sql.parse(L_ExecStatement_crs,L_Statement,dbms_sql.v7);
102 dbms_sql.bind_variable(L_ExecStatement_crs,':organization_id',
103 p_organization_id);
104 dbms_sql.bind_variable(L_ExecStatement_crs,':inventory_item_id',
105 p_inventory_item_id);
106 -- If statement type is summary then bind wip and oe short quantity
107 IF p_sum_detail_flag = 2 THEN
108 dbms_sql.bind_variable(L_ExecStatement_crs,':wip_short_quantity',
109 0);
110 dbms_sql.bind_variable(L_ExecStatement_crs,':oe_short_quantity',
111 0);
112 END IF;
113 -- If statement type is detail then pull sequence number and bind it
114 IF p_sum_detail_flag = 1 THEN
115 SELECT mtl_short_chk_temp_s.NEXTVAL
116 INTO x_seq_num
117 FROM dual;
118 --
119 dbms_sql.bind_variable(L_ExecStatement_crs,':seq_num',
120 x_seq_num);
121 ELSE
122 x_seq_num := NULL;
123 END IF;
124 L_RowsProcessed := dbms_sql.execute(L_ExecStatement_crs);
125 -- If statement type is summary then pick up the wip and oe
126 -- short quantity
127 IF p_sum_detail_flag = 2 THEN
128 dbms_sql.variable_value(L_ExecStatement_crs,':wip_short_quantity',
129 x_wip_short_quantity);
130 dbms_sql.variable_value(L_ExecStatement_crs,':oe_short_quantity',
131 x_oe_short_quantity);
132 END IF;
133 dbms_sql.close_cursor(L_ExecStatement_crs);
134 END ExecStatement;
135 --
136 FUNCTION Compare (
137 p_organization_id IN NUMBER,
138 p_inventory_item_id IN NUMBER,
139 p_short_quantity IN NUMBER )
140 RETURN VARCHAR2
141 IS
142 L_ATT_qty NUMBER;
143 L_primary_quantity NUMBER;
144 L_adj_qty NUMBER;
145 L_qty_on_hand NUMBER;
146 L_qty_res_on_hand NUMBER;
147 L_qty_res NUMBER;
148 L_qty_sug NUMBER;
149 L_qty_atr NUMBER;
150 L_api_return_status VARCHAR2(1);
151 BEGIN
152
153 -- Clearing the quantity cache
154 --inv_quantity_tree_pub.clear_quantity_cache;
155
156 -- Call quantity tree to obtain the quantity available to transact
157 INV_Quantity_Tree_PUB.Query_Quantities
158 (
159 p_api_version_number => 1.0
160 , p_init_msg_lst => fnd_api.g_false
161 , x_return_status => L_api_return_status
162 , x_msg_count => x_msg_count
163 , x_msg_data => x_msg_data
164 , p_organization_id => p_organization_id
165 , p_inventory_item_id => p_inventory_item_id
166 , p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
167 , p_is_revision_control => FALSE
168 , p_is_lot_control => FALSE
169 , p_is_serial_control => FALSE
170 , p_revision => NULL
171 , p_lot_number => NULL
172 , p_subinventory_code => NULL
173 , p_locator_id => NULL
174 , x_qoh => L_qty_on_hand
175 , x_rqoh => L_qty_res_on_hand
176 , x_qr => L_qty_res
177 , x_qs => L_qty_sug
178 , x_att => L_ATT_qty
179 , x_atr => L_qty_atr
180 );
181 IF L_api_return_status <> fnd_api.g_ret_sts_success THEN
182 RAISE fnd_api.g_exc_unexpected_error;
183 END IF;
184
185 /* Old code which did not use the quantity tree
186 SELECT NVL(SUM(transaction_quantity),0)
187 INTO L_ATT_qty
188 FROM mtl_att_qty_v
189 WHERE organization_id = p_organization_id
190 AND inventory_item_id = p_inventory_item_id;
191 */
192
193 -- Now compare the ATT qty (but if it is a background transaction:
194 -- minus the receipt quantity which is already included)
195 -- against the summarized short quantity
196 IF p_sum_detail_flag = 1 THEN
197 L_primary_quantity := p_primary_quantity;
198 ELSE
199 L_primary_quantity := 0;
200 END IF;
201
202 -- Compute the adjusted ATT qty; the ATT qty without the primary
203 -- quantity, or 0 if negative
204 L_adj_qty := L_ATT_qty - L_primary_quantity;
205 IF (L_adj_qty < 0) THEN
206 L_adj_qty := 0;
207 END IF;
208
209 -- Compare the adjusted ATT quantity (supply) to the shortage quantity (demand)
210 IF L_adj_qty >= p_short_quantity THEN
211 return (FND_API.G_FALSE);
212 ELSE
213 return (FND_API.G_TRUE);
214 END IF;
215 END Compare;
216 --
217 BEGIN
218 -- Standard Call to check for call compatibility
219 IF NOT FND_API.Compatible_API_Call(l_api_version
220 , p_api_version
221 , l_api_name
222 , G_PKG_NAME) THEN
223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
224 END IF;
225 --
226 -- Initialize message list if p_init_msg_list is set to true
227 IF FND_API.to_Boolean(p_init_msg_list) THEN
228 FND_MSG_PUB.initialize;
229 END IF;
230 --
231 -- Initialize API return status to access
232 x_return_status := FND_API.G_RET_STS_SUCCESS;
233 --
234 -- Initialize x_check_result
235 x_check_result := FND_API.G_FALSE;
236 -- See what statement we have to execute
237
238 -- Bug #4474266 Added the IF condition for p_sum_detail_flag = 0
239
240 IF p_sum_detail_flag = 0 THEN
241 select count(*) into L_count from mtl_short_chk_temp;
242
243 IF L_count > 0 then
244 Begin
245 FOR L_Quantity_rec in L_Quantity_crs (p_organization_id, x_seq_num)
246 LOOP
247 x_check_result := Compare (
248 p_organization_id => p_organization_id,
249 p_inventory_item_id => L_Quantity_rec.inventory_item_id,
250 p_short_quantity => L_Quantity_rec.short_quantity);
251
252 IF x_check_result = FND_API.G_FALSE THEN
253 delete from mtl_short_chk_temp
254 where inventory_item_id = L_Quantity_rec.inventory_item_id
255 and seq_num = x_seq_num -- Bug 5081665: filter on seq_num
256 and organization_id = p_organization_id;
257 END IF;
258 END LOOP;
259 end;
260 END IF;
261
262 ELSIF p_sum_detail_flag = 1 THEN
263 -- detail statement
264 -- If we have to compare the short quantity with the orgs ATT quantity,
265 -- we execute first the summary statement to see if there are any
266 -- shortages
267 IF p_comp_att_qty_flag = 1 THEN
268 -- Get statement and execute it
269 ExecStatement (
270 p_organization_id => p_organization_id,
271 p_inventory_item_id => p_inventory_item_id,
272 p_sum_detail_flag => 2,
273 x_seq_num => x_seq_num,
274 x_wip_short_quantity => L_WIP_short_quantity,
275 x_oe_short_quantity => L_OE_short_quantity
276 );
277 -- Compare short and att quantity (both are in primary uom)
278 x_check_result := Compare (
279 p_organization_id => p_organization_id,
280 p_inventory_item_id => p_inventory_item_id,
281 p_short_quantity =>
282 L_WIP_short_quantity + L_OE_short_quantity);
283 END IF;
284 -- Now execute the detail statement if shortage exists or
285 -- parameter p_comp_att_qty_flag has been set to No
286 IF p_sum_detail_flag = 1 AND (p_comp_att_qty_flag = 2 OR
287 x_check_result = FND_API.G_TRUE) THEN
288 -- Get statement and execute it
289 ExecStatement (
290 p_organization_id => p_organization_id,
291 p_inventory_item_id => p_inventory_item_id,
292 p_sum_detail_flag => 1,
293 x_seq_num => x_seq_num,
294 x_wip_short_quantity => L_WIP_short_quantity,
295 x_oe_short_quantity => L_OE_short_quantity
296 );
297 -- Set x_check_result according to mtl_short_chk_temp table contents
298 BEGIN
299 SELECT FND_API.G_TRUE
300 INTO x_check_result
301 FROM mtl_short_chk_temp
302 WHERE seq_num = x_seq_num
303 AND rownum < 2;
304 EXCEPTION
305 WHEN NO_DATA_FOUND THEN
306 x_check_result := FND_API.G_FALSE;
307 END;
308 END IF;
309 ELSIF p_sum_detail_flag = 2 THEN
310 -- summary statement
311 -- Get statement and execute it
312 ExecStatement (
313 p_organization_id => p_organization_id,
314 p_inventory_item_id => p_inventory_item_id,
315 p_sum_detail_flag => 2,
316 x_seq_num => x_seq_num,
317 x_wip_short_quantity => L_WIP_short_quantity,
318 x_oe_short_quantity => L_OE_short_quantity
319 );
320 -- Compare short and att quantity (both are in primary uom)
321 x_check_result := Compare (
322 p_organization_id => p_organization_id,
323 p_inventory_item_id => p_inventory_item_id,
324 p_short_quantity =>
325 L_WIP_short_quantity + L_OE_short_quantity);
326 END IF;
327 --
328 -- Standard check of p_commit
329 IF FND_API.to_Boolean(p_commit) THEN
330 COMMIT;
331 END IF;
332 -- Standard call to get message count and if count is 1, get message info
333 FND_MSG_PUB.Count_And_Get
334 (p_count => x_msg_count
335 , p_data => x_msg_data);
336 EXCEPTION
337 WHEN FND_API.G_EXC_ERROR THEN
338 --
339 x_return_status := FND_API.G_RET_STS_ERROR;
340 --
341 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
342 , p_data => x_msg_data);
343 --
344 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
345 --
346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
347 --
348 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
349 , p_data => x_msg_data);
350 --
351 WHEN L_Statement_not_found THEN
352 --
353 x_return_status := FND_API.G_RET_STS_ERROR;
354 --
355 FND_MESSAGE.SET_NAME('INV','INV_SHORT_STATEMENT_NOT_FOUND');
356 FND_MSG_PUB.Add;
357 --
358 WHEN OTHERS THEN
359 --
360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
361 --
362 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
363 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
364 END IF;
365 --
366 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
367 , p_data => x_msg_data);
368 END;
369 -- Start OF comments
370 -- API name : CheckPrerequisites
371 -- TYPE : Private
372 -- Pre-reqs : None
373 -- FUNCTION :
374 -- Parameters:
375 -- IN :
376 -- p_api_version IN NUMBER (required)
377 -- API Version of this procedure
378 --
379 -- p_init_msg_list IN VARCHAR2 (optional)
380 -- DEFAULT = FND_API.G_FALSE,
381 --
382 -- p_commit IN VARCHAR2 (optional)
383 -- DEFAULT = FND_API.G_FALSE
384 --
385 --
386 -- OUT NOCOPY /* file.sql.39 change */ :
387 -- x_return_status OUT NOCOPY /* file.sql.39 change */ NUMBER
388 -- Result of all the operations
389 --
390 -- x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
391 --
392 -- x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
393 --
394 -- Version: Current Version 1.0
395 -- Changed : Nothing
396 -- No Previous Version 0.0
397 -- Initial version 1.0
398 -- Notes :
399 -- END OF comments
400 PROCEDURE CheckPrerequisites (
401 p_api_version IN NUMBER ,
402 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
403 x_return_status IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
404 x_msg_count IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
405 x_msg_data IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
406 p_sum_detail_flag IN NUMBER,
407 p_organization_id IN NUMBER,
408 p_inventory_item_id IN NUMBER,
409 p_transaction_type_id IN NUMBER,
410 x_check_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2
411 )
412 IS
413 L_api_version CONSTANT NUMBER := 1.0;
414 L_api_name CONSTANT VARCHAR2(30) := 'CheckPrerequisites';
415 L_TransTypeFlag NUMBER;
416 L_ItemFlag NUMBER;
417 --
418 CURSOR L_TransType_crs ( p_transaction_type_id IN NUMBER,
419 p_sum_detail_flag IN NUMBER ) IS
420 SELECT DECODE(p_sum_detail_flag,1,shortage_msg_background_flag,
421 2,shortage_msg_online_flag)
422 FROM mtl_transaction_types
423 WHERE transaction_type_id = p_transaction_type_id;
424 --
425 CURSOR L_Item_crs ( p_organization_id IN NUMBER,
426 p_inventory_item_id IN NUMBER ) IS
427 SELECT DECODE(check_shortages_flag,'Y',1,'N',2,NULL)
428 FROM mtl_system_items
429 WHERE inventory_item_id = p_inventory_item_id
430 AND organization_id = p_organization_id;
431 --
432 BEGIN
433 -- Standard Call to check for call compatibility
434 IF NOT FND_API.Compatible_API_Call(l_api_version
435 , p_api_version
436 , l_api_name
437 , G_PKG_NAME) THEN
438 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
439 END IF;
440 --
441 -- Initialize message list if p_init_msg_list is set to true
442 IF FND_API.to_Boolean(p_init_msg_list) THEN
443 FND_MSG_PUB.initialize;
444 END IF;
445 --
446 -- Initialize API return status to access
447 x_return_status := FND_API.G_RET_STS_SUCCESS;
448 --
449 -- Initialize x_check_result to FND_API.G_FALSE
450 x_check_result := FND_API.G_FALSE;
451 --
452 -- Check if transaction type allows shortage message for given
453 -- message type (but only if p_transaction_type_id is not null)
454 IF p_transaction_type_id IS NOT NULL THEN
455 OPEN L_TransType_crs ( p_transaction_type_id,
456 p_sum_detail_flag );
457 FETCH L_TransType_crs INTO L_TransTypeFlag;
458 CLOSE L_TransType_crs;
459 ELSE
460 L_TransTypeFlag := 1;
461 END IF;
462 IF L_TransTypeFlag = 1 THEN
463 -- Check if item allows shortage message for given message type
464 -- If so, set x_check_result to FND_API.G_TRUE
465 OPEN L_Item_crs ( p_organization_id,
466 p_inventory_item_id );
467 FETCH L_Item_crs INTO L_ItemFlag;
468 CLOSE L_Item_crs;
469 IF L_ItemFlag = 1 THEN
470 x_check_result := FND_API.G_TRUE;
471 END IF;
472 END IF;
473 --
474 -- Standard call to get message count and if count is 1, get message info
475 FND_MSG_PUB.Count_And_Get
476 (p_count => x_msg_count
477 , p_data => x_msg_data);
478 EXCEPTION
479 WHEN FND_API.G_EXC_ERROR THEN
480 --
481 x_return_status := FND_API.G_RET_STS_ERROR;
482 --
483 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
484 , p_data => x_msg_data);
485 --
486 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
487 --
488 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
489 --
490 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
491 , p_data => x_msg_data);
492 --
493 WHEN OTHERS THEN
494 --
495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
496 --
497 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
498 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
499 END IF;
500 --
501 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
502 , p_data => x_msg_data);
503 END;
504 -- Start OF comments
505 -- API name : SendNotifications
506 -- TYPE : Private
507 -- Pre-reqs : None
508 -- FUNCTION :
509 -- Parameters:
510 -- IN :
511 -- p_api_version IN NUMBER (required)
512 -- API Version of this procedure
513 --
514 -- p_init_msg_list IN VARCHAR2 (optional)
515 -- DEFAULT = FND_API.G_FALSE,
516 --
517 -- p_commit IN VARCHAR2 (optional)
518 -- DEFAULT = FND_API.G_FALSE
519 --
520 --
521 -- OUT NOCOPY /* file.sql.39 change */ :
522 -- x_return_status OUT NOCOPY /* file.sql.39 change */ NUMBER
523 -- Result of all the operations
524 --
525 -- x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
526 --
527 -- x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
528 --
529 -- Version: Current Version 1.0
530 -- Changed : Nothing
531 -- No Previous Version 0.0
532 -- Initial version 1.0
533 -- Notes :
534 -- END OF comments
535 PROCEDURE SendNotifications (
536 p_api_version IN NUMBER ,
537 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
538 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
539 x_return_status IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
540 x_msg_count IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
541 x_msg_data IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
542 p_organization_id IN NUMBER,
543 p_inventory_item_id IN NUMBER,
544 p_seq_num IN NUMBER,
545 p_notification_type IN VARCHAR2
546 )
547 IS
548 L_api_version CONSTANT NUMBER := 1.0;
549 L_api_name CONSTANT VARCHAR2(30) := 'SendNotifications';
550 L_user_name VARCHAR2(100);
551 L_notification_id NUMBER;
552 L_item_conc_segments mtl_system_items_kfv.concatenated_segments%TYPE;
553 L_organization_code VARCHAR2(3);
554 L_msg_name VARCHAR2(30);
555 l_org_id NUMBER; -- MOAC parameter
556 l_ou_org_id NUMBER; --Bug#6509349
557
558 --
559 CURSOR L_Item_crs IS
560 SELECT concatenated_segments
561 FROM mtl_system_items_kfv
562 WHERE inventory_item_id = p_inventory_item_id
563 AND organization_id = p_organization_id;
564 --
565 CURSOR L_Org_crs IS
566 SELECT organization_code
567 FROM mtl_parameters
568 WHERE organization_id = p_organization_id;
569 --
570 CURSOR L_ShortParam_csr ( p_organization_id IN NUMBER ) IS
571 SELECT DECODE(check_wip_flag,1,wip_notif_comp_planner_flag,2)
572 wip_notif_comp_planner_flag,
573 DECODE(check_wip_flag,1,wip_notif_ass_planner_flag,2)
574 wip_notif_ass_planner_flag,
575 DECODE(check_wip_flag,1,wip_notif_comp_buyer_flag,2)
576 wip_notif_comp_buyer_flag,
577 DECODE(check_wip_flag,1,wip_notif_job_creator_flag,2)
578 wip_notif_job_creator_flag,
579 DECODE(check_oe_flag,1,oe_notif_item_planner_flag,2)
580 oe_notif_item_planner_flag,
581 DECODE(check_oe_flag,1,oe_notif_so_creator_flag,2)
582 oe_notif_so_creator_flag
583 FROM mtl_short_chk_param
584 WHERE organization_id = p_organization_id;
585 --
586 L_ShortParam_rec L_ShortParam_csr%ROWTYPE;
587 --
588 CURSOR L_ShortTemp_csr IS
589 -- WIP component planner
590 SELECT DISTINCT FU.user_name user_name
591 FROM mtl_planners MP,
592 mtl_system_items MSI,
593 fnd_user FU,
594 mtl_short_chk_temp MSCT
595 WHERE L_ShortParam_rec.wip_notif_comp_planner_flag = 1
596 AND MSCT.object_type IN (1,2)
597 AND MSCT.seq_num = p_seq_num
598 AND MSCT.inventory_item_id = MSI.inventory_item_id
599 AND MSCT.organization_id = MSI.organization_id
600 AND MSI.planner_code = MP.planner_code
601 AND MSI.organization_id = MP.organization_id
602 AND FU.employee_id = MP.employee_id
603 -- WIP assembly planner (discrete jobs)
604 UNION
605 SELECT DISTINCT FU.user_name
606 FROM mtl_planners MP,
607 mtl_system_items MSI,
608 wip_entities WE,
609 fnd_user FU,
610 mtl_short_chk_temp MSCT
611 WHERE L_ShortParam_rec.wip_notif_ass_planner_flag = 1
612 AND MSCT.seq_num = p_seq_num
613 AND MSCT.object_type = 1
614 AND MSCT.object_id = WE.wip_entity_id
615 AND WE.primary_item_id = MSI.inventory_item_id
616 AND MSCT.organization_id = MSI.organization_id
617 AND MSI.planner_code = MP.planner_code
618 AND MSI.organization_id = MP.organization_id
619 AND FU.employee_id = MP.employee_id
620 -- WIP assembly planner (repetitive schedules)
621 UNION
622 SELECT DISTINCT FU.user_name
623 FROM mtl_planners MP,
624 mtl_system_items MSI,
625 wip_repetitive_schedules WRS,
626 wip_repetitive_items WRI,
627 fnd_user FU,
628 mtl_short_chk_temp MSCT
629 WHERE L_ShortParam_rec.wip_notif_ass_planner_flag = 1
630 AND MSCT.seq_num = p_seq_num
631 AND MSCT.object_type = 2
632 AND MSCT.object_id = WRS.repetitive_schedule_id
633 AND WRI.wip_entity_id = WRS.wip_entity_id
634 AND WRI.line_id = WRS.line_id
635 AND WRI.organization_id = MSCT.organization_id
636 AND WRI.primary_item_id = MSI.inventory_item_id
637 AND MSCT.organization_id = MSI.organization_id
638 AND MSI.planner_code = MP.planner_code
639 AND MSI.organization_id = MP.organization_id
640 AND FU.employee_id = MP.employee_id
641 -- WIP component buyer
642 UNION
643 SELECT DISTINCT FU.user_name
644 FROM mtl_system_items MSI,
645 fnd_user FU,
646 mtl_short_chk_temp MSCT
647 WHERE L_ShortParam_rec.wip_notif_comp_buyer_flag = 1
648 AND MSCT.seq_num = p_seq_num
649 AND MSCT.object_type IN (1,2)
650 AND MSCT.inventory_item_id = MSI.inventory_item_id
651 AND MSCT.organization_id = MSI.organization_id
652 AND FU.employee_id = MSI.buyer_id
653 -- WIP discrete job creator
654 UNION
655 SELECT DISTINCT FU.user_name
656 FROM fnd_user FU,
657 wip_discrete_jobs WDJ,
658 mtl_short_chk_temp MSCT
659 WHERE L_ShortParam_rec.wip_notif_job_creator_flag = 1
660 AND MSCT.seq_num = p_seq_num
661 AND MSCT.object_type = 1
662 AND MSCT.object_id = WDJ.wip_entity_id
663 AND MSCT.organization_id = WDJ.organization_id
664 AND WDJ.created_by = FU.user_id
665 -- WIP repetitive schedule creator
666 UNION
667 SELECT DISTINCT FU.user_name
668 FROM fnd_user FU,
669 wip_repetitive_schedules WRS,
670 mtl_short_chk_temp MSCT
671 WHERE L_ShortParam_rec.wip_notif_job_creator_flag = 1
672 AND MSCT.seq_num = p_seq_num
673 AND MSCT.object_type = 2
674 AND MSCT.object_id = WRS.repetitive_schedule_id
675 AND MSCT.organization_id = WRS.organization_id
676 AND WRS.created_by = FU.user_id
677 -- OE item planner
678 UNION
679 SELECT DISTINCT FU.user_name user_name
680 FROM mtl_planners MP,
681 mtl_system_items MSI,
682 fnd_user FU,
683 mtl_short_chk_temp MSCT
684 WHERE L_ShortParam_rec.oe_notif_item_planner_flag = 1
685 AND MSCT.object_type = 4
686 AND MSCT.seq_num = p_seq_num
687 AND MSCT.inventory_item_id = MSI.inventory_item_id
688 AND MSCT.organization_id = MSI.organization_id
689 AND MSI.planner_code = MP.planner_code
690 AND MSI.organization_id = MP.organization_id
691 AND FU.employee_id = MP.employee_id
692 -- OE sales order creator
693 UNION
694 SELECT DISTINCT FU.user_name
695 FROM fnd_user FU,
696 oe_order_headers SH,
697 mtl_short_chk_temp MSCT
698 WHERE L_ShortParam_rec.oe_notif_so_creator_flag = 1
699 AND MSCT.seq_num = p_seq_num
700 AND MSCT.object_type = 4
701 AND MSCT.object_id = SH.header_id
702 AND SH.created_by = FU.user_id;
703 --
704 BEGIN
705
706 -- MOAC : Added multi org init procedure as secured synonym
707 -- oe_order_headers requires OU context.
708
709 MO_GLOBAL.init('INV');
710
711 -- MOAC : Check if the specified operating unit exists in
712 -- the session's access control list.
713 /*Bug#6509349 :We should pass operating unit org id and
714 not the organization id picked from MMT.Getting
715 Operating unit organizaiton id from below query.*/
716 Begin
717 SELECT hoi.org_information3 into l_ou_org_id
718 FROM hr_organization_information hoi
719 WHERE hoi.org_information_context ='Accounting Information'
720 AND hoi.organization_id = p_organization_id ;
721
722 EXCEPTION
723 WHEN OTHERS THEN
724 inv_log_util.trace(sqlerrm,'INV_ShortStatement_PVT.sendnotifications',9);
725 x_return_status := FND_API.G_RET_STS_ERROR;
726 RAISE FND_API.G_EXC_ERROR;
727 END ;
728
729 -- l_org_id := MO_GLOBAL.get_valid_org(p_organization_id);
730 l_org_id := MO_GLOBAL.get_valid_org(l_ou_org_id); --Bug#6509349
731
732 IF l_org_id is NULL THEN
733 x_return_status := FND_API.G_RET_STS_ERROR;
734 RAISE FND_API.G_EXC_ERROR;
735 END IF;
736
737 -- MOAC : Sets the application context for the current org
738 -- if the specified operating unit exists in
739 -- the session's access control list.
740
741 MO_GLOBAL.set_policy_context('S',l_org_id);
742
743 -- Standard Call to check for call compatibility
744 IF NOT FND_API.Compatible_API_Call(l_api_version
745 , p_api_version
746 , l_api_name
747 , G_PKG_NAME) THEN
748 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
749 END IF;
750 --
751 -- Initialize message list if p_init_msg_list is set to true
752 IF FND_API.to_Boolean(p_init_msg_list) THEN
753 FND_MSG_PUB.initialize;
754 END IF;
755 --
756 -- Initialize API return status to access
757 x_return_status := FND_API.G_RET_STS_SUCCESS;
758 --
759 -- Get item concatenated segments
760 OPEN L_Item_crs;
761 FETCH L_Item_crs INTO L_item_conc_segments;
762 CLOSE L_Item_crs;
763 --
764 -- Get organization code
765 OPEN L_Org_crs;
766 FETCH L_Org_crs INTO L_organization_code;
767 CLOSE L_Org_crs;
768 --
769 -- Get the notification recipients from the shortage parameter
770 OPEN L_ShortParam_csr ( p_organization_id );
771 FETCH L_ShortParam_csr INTO L_ShortParam_rec;
772 IF L_ShortParam_csr%NOTFOUND THEN
773 FND_MESSAGE.SET_NAME('INV','INV_SHORT_PARAMETER_NOT_FOUND');
774 FND_MSG_PUB.Add;
775 RAISE FND_API.G_EXC_ERROR;
776 END IF;
777 CLOSE L_ShortParam_csr;
778 --
779 -- Set message name according to input parameter
780 IF p_notification_type = 'R' THEN
781 L_msg_name := 'INV_SHORTAGE_EXISTS';
782 ELSE
783 L_msg_name := 'INV_SHORTAGE_EXISTS_REPORT';
784 END IF;
785 --
786 -- Go through the shortage temp table and call the wf message api to
787 -- send the shortage message
788 --
789 OPEN L_ShortTemp_csr;
790 LOOP
791 FETCH L_ShortTemp_csr INTO L_user_name;
792 EXIT WHEN L_ShortTemp_csr%NOTFOUND;
793 -- Call the send message wf procedure
794 L_notification_id := WF_NOTIFICATION.Send (
795 role => L_user_name,
796 msg_type => 'INVSHMSG',
797 msg_name => L_msg_name,
798 due_date => NULL,
799 callback => NULL,
800 context => NULL,
801 send_comment => NULL );
802 -- Set message attributes
803 -- Open Form Command for View shortage form
804 -- Added call to wf_notification.denormalize_notification for bug 3101169
805 WF_NOTIFICATION.SetAttrText (
806 nid => L_notification_id,
807 aname => 'OPEN_FORM_COMMAND',
808 avalue => 'INV_INVSHINQ:ORG_ID="'||'&'||
809 'ORGANIZATION_ID" ITEM_ID="'||'&'||'INVENTORY_ITEM_ID"' );
810 -- Item id
811 WF_NOTIFICATION.SetAttrNumber (
812 nid => L_notification_id,
813 aname => 'INVENTORY_ITEM_ID',
814 avalue => p_inventory_item_id );
815 WF_NOTIFICATION.Denormalize_Notification(L_notification_id);
816 -- Item concatenated segments
817 WF_NOTIFICATION.SetAttrText (
818 nid => L_notification_id,
819 aname => 'INVENTORY_ITEM_CONC_SEGMENTS',
820 avalue => L_item_conc_segments );
821 WF_NOTIFICATION.Denormalize_Notification(L_notification_id);
822 -- Organization id
823 WF_NOTIFICATION.SetAttrNumber (
824 nid => L_notification_id,
825 aname => 'ORGANIZATION_ID',
826 avalue => p_organization_id );
827 WF_NOTIFICATION.Denormalize_Notification(L_notification_id);
828 -- Organization code
829 WF_NOTIFICATION.SetAttrText (
830 nid => L_notification_id,
831 aname => 'ORGANIZATION_CODE',
832 avalue => L_organization_code );
833 WF_NOTIFICATION.Denormalize_Notification(L_notification_id);
834 --
835 END LOOP;
836 CLOSE L_ShortTemp_csr;
837 -- Standard check of p_commit
838 IF FND_API.to_Boolean(p_commit) THEN
839 COMMIT;
840 END IF;
841 -- Standard call to get message count and if count is 1, get message info
842 FND_MSG_PUB.Count_And_Get
843 (p_count => x_msg_count
844 , p_data => x_msg_data);
845 EXCEPTION
846 WHEN FND_API.G_EXC_ERROR THEN
847 --
848 x_return_status := FND_API.G_RET_STS_ERROR;
849 --
850 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
851 , p_data => x_msg_data);
852 --
853 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
854 --
855 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
856 --
857 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
858 , p_data => x_msg_data);
859 --
860 WHEN OTHERS THEN
861 --
862 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
863 --
864 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
865 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
866 END IF;
867 --
868 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
869 , p_data => x_msg_data);
870 END;
871 -- Start OF comments
872 -- API name : PurgeTempTable
873 -- TYPE : Private
874 -- Pre-reqs : None
875 -- FUNCTION :
876 -- Parameters:
877 -- IN :
878 -- p_api_version IN NUMBER (required)
879 -- API Version of this procedure
880 --
881 -- p_init_msg_list IN VARCHAR2 (optional)
882 -- DEFAULT = FND_API.G_FALSE,
883 --
884 -- p_commit IN VARCHAR2 (optional)
885 -- DEFAULT = FND_API.G_FALSE
886 --
887 -- p_seq_num IN NUMBER
888 -- Sequence number of rows which have to be deleted
889 --
890 --
891 -- OUT NOCOPY /* file.sql.39 change */ :
892 -- x_return_status OUT NOCOPY /* file.sql.39 change */ NUMBER
893 -- Result of all the operations
894 --
895 -- x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
896 --
897 -- x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
898 --
899 --
900 -- Version: Current Version 1.0
901 -- Changed : Nothing
902 -- No Previous Version 0.0
903 -- Initial version 1.0
904 -- Notes :
905 -- END OF comments
906 PROCEDURE PurgeTempTable (
907 p_api_version IN NUMBER ,
908 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
909 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
910 x_return_status IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
911 x_msg_count IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
912 x_msg_data IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
913 p_seq_num IN NUMBER
914 )
915 IS
916 L_api_version CONSTANT NUMBER := 1.0;
917 L_api_name CONSTANT VARCHAR2(30) := 'PurgeTempTable';
918 BEGIN
919 -- Standard Call to check for call compatibility
920 IF NOT FND_API.Compatible_API_Call(l_api_version
921 , p_api_version
922 , l_api_name
923 , G_PKG_NAME) THEN
924 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
925 END IF;
926 --
927 -- Initialize message list if p_init_msg_list is set to true
928 IF FND_API.to_Boolean(p_init_msg_list) THEN
929 FND_MSG_PUB.initialize;
930 END IF;
931 --
932 -- Initialize API return status to success
933 x_return_status := FND_API.G_RET_STS_SUCCESS;
934 --
935 -- Purge all rows with the given sequence number
936 DELETE FROM mtl_short_chk_temp
937 WHERE seq_num = p_seq_num;
938 --
939 -- Standard check of p_commit
940 IF FND_API.to_Boolean(p_commit) THEN
941 COMMIT;
942 END IF;
943 -- Standard call to get message count and if count is 1, get message info
944 FND_MSG_PUB.Count_And_Get
945 (p_count => x_msg_count
946 , p_data => x_msg_data);
947 EXCEPTION
948 WHEN FND_API.G_EXC_ERROR THEN
949 --
950 x_return_status := FND_API.G_RET_STS_ERROR;
951 --
952 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
953 , p_data => x_msg_data);
954 --
955 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
956 --
957 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
958 --
959 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
960 , p_data => x_msg_data);
961 --
962 WHEN OTHERS THEN
963 --
964 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
965 --
966 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
967 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
968 END IF;
969 --
970 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
971 , p_data => x_msg_data);
972 END;
973 -- Start OF comments
974 -- API name : PrepareMessage
975 -- TYPE : Private
976 -- Pre-reqs : None
977 -- FUNCTION : This API prepares a shortage message for the given item/org
978 -- and pushes it onto the message stack. After calling this
979 -- API, caller should call fnd_message.retrieve and
980 -- fnd_message.show in order to display the message.
981 -- Parameters:
982 -- IN :
983 -- p_inventory_item_id IN NUMBER (required)
984 -- The inventory item ID which a shortage message should be created for
985 --
986 -- p_organization_id IN NUMBER (required)
987 -- The inventory organization ID in which the shortage occurred
988 -- END OF comments
989 PROCEDURE PrepareMessage (
990 p_inventory_item_id IN NUMBER,
991 p_organization_id IN NUMBER
992 )
993 IS
994 L_item_conc_segments mtl_system_items_kfv.concatenated_segments%TYPE;
995 BEGIN
996 BEGIN
997 SELECT concatenated_segments
998 INTO L_item_conc_segments
999 FROM mtl_system_items_kfv
1000 WHERE organization_id = p_organization_id
1001 AND inventory_item_id = p_inventory_item_id;
1002 EXCEPTION
1003 WHEN OTHERS THEN
1004 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1005 END;
1006 fnd_message.set_name('INV','INV_SHORTAGE_EXISTS');
1007 fnd_message.set_token('ITEM',L_item_conc_segments);
1008 EXCEPTION
1009 WHEN OTHERS THEN
1010 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1011 END PrepareMessage;
1012 -- Added for bug 5081655: calculate open qty for repetitive schedules
1013 FUNCTION get_rep_curr_open_qty
1014 ( p_organization_id IN NUMBER
1015 , p_wip_entity_id IN NUMBER
1016 , p_repetitive_schedule_id IN NUMBER
1017 , p_first_unit_start_date IN DATE
1018 , p_processing_work_days IN NUMBER
1019 , p_operation_seq_num IN NUMBER
1020 , p_inventory_item_id IN NUMBER
1021 , p_quantity_issued IN NUMBER
1022 ) RETURN NUMBER IS
1023 l_open_qty NUMBER;
1024 l_qty_allocated NUMBER;
1025 l_num_days NUMBER;
1026 BEGIN
1027 IF TRUNC(p_first_unit_start_date) > TRUNC(sysdate) THEN
1028 RETURN 0;
1029 END IF;
1030
1031 BEGIN
1032 SELECT ( NVL(bcd2.seq_num, (bcd2.next_seq_num - 1))
1033 - NVL(bcd1.seq_num, bcd1.next_seq_num)
1034 + 1
1035 )
1036 INTO l_num_days
1037 FROM mtl_parameters mp
1038 , bom_calendar_dates bcd1
1039 , bom_calendar_dates bcd2
1040 WHERE mp.organization_id = p_organization_id
1041 AND bcd1.calendar_code = mp.calendar_code
1042 AND bcd1.exception_set_id = mp.calendar_exception_set_id
1043 AND bcd1.calendar_date = TRUNC(p_first_unit_start_date)
1044 AND bcd2.calendar_code = mp.calendar_code
1045 AND bcd2.exception_set_id = mp.calendar_exception_set_id
1046 AND bcd2.calendar_date = TRUNC(sysdate);
1047 EXCEPTION
1048 WHEN OTHERS THEN
1049 inv_log_util.trace(sqlerrm,'INV_ShortStatement_PVT.get_rep_curr_open_qty',9);
1050 l_num_days := 0;
1051 END;
1052
1053 IF NVL(l_num_days,0) <= 0 THEN
1054 RETURN 0;
1055 END IF;
1056
1057 l_num_days := LEAST(l_num_days, NVL(p_processing_work_days,0));
1058
1059 l_qty_allocated :=
1060 wip_picking_pub.quantity_allocated( p_wip_entity_id => p_wip_entity_id
1061 , p_operation_seq_num => p_operation_seq_num
1062 , p_organization_id => p_organization_id
1063 , p_inventory_item_id => p_inventory_item_id
1064 , p_repetitive_schedule_id => p_repetitive_schedule_id
1065 , p_quantity_issued => p_quantity_issued
1066 );
1067
1068 SELECT LEAST( ( wro.required_quantity *
1069 DECODE( NVL(wp.include_component_yield,1)
1070 , 2, NVL(wro.component_yield_factor,1)
1071 , 1
1072 )
1073 - p_quantity_issued
1074 - NVL(l_qty_allocated,0)
1075 - NVL( wo.CUMULATIVE_SCRAP_QUANTITY * wro.QUANTITY_PER_ASSEMBLY
1076 / DECODE( NVL(wp.include_component_yield,1)
1077 , 2, 1
1078 , NVL(wro.component_yield_factor,1)
1079 )
1080 , 0
1081 )
1082 )
1083 , ( wrs.daily_production_rate * wro.quantity_per_assembly * l_num_days)
1084 + NVL(wro.quantity_backordered, 0)
1085 )
1086 INTO l_open_qty
1087 FROM wip_parameters wp
1088 , wip_requirement_operations wro
1089 , wip_operations wo
1090 , wip_repetitive_schedules wrs
1091 WHERE wp.organization_id = p_organization_id
1092 AND wro.organization_id = wp.organization_id
1093 AND wro.wip_entity_id = p_wip_entity_id
1094 AND wro.repetitive_schedule_id = p_repetitive_schedule_id
1095 AND wro.operation_seq_num = p_operation_seq_num
1096 AND wro.inventory_item_id = p_inventory_item_id
1097 AND wro.required_quantity > (wro.quantity_issued + NVL(l_qty_allocated,0))
1098 AND wro.repetitive_schedule_id = wo.repetitive_schedule_id (+)
1099 AND wro.operation_seq_num = wo.operation_seq_num (+)
1100 AND wrs.organization_id = wro.organization_id
1101 AND wrs.wip_entity_id = wro.wip_entity_id
1102 AND wrs.repetitive_schedule_id = wro.repetitive_schedule_id;
1103
1104 RETURN NVL(l_open_qty,0);
1105
1106 EXCEPTION
1107 WHEN OTHERS THEN
1108 inv_log_util.trace(sqlerrm,'INV_ShortStatement_PVT.get_rep_curr_open_qty',9);
1109 RETURN 0;
1110 END get_rep_curr_open_qty;
1111
1112 END INV_ShortCheckExec_PVT;