[Home] [Help]
PACKAGE BODY: APPS.CSP_PLANNER_NOTIFICATIONS
Source
1 PACKAGE BODY CSP_PLANNER_NOTIFICATIONS AS
2 /* $Header: cspvppnb.pls 120.0 2005/05/25 11:28:34 appldev noship $ */
3 --
4 -- Purpose: This package will hold all APIs related to the creation of
5 -- planner notifications and recommendations for the notifications
6 --
7 -- MODIFICATION HISTORY
8 -- Person Date Comments
9 -- phegde 16th April 2002 Created new Package body
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'csp_planner_notification';
12
13 -- Start of Forward declarations
14
15 PROCEDURE Build_Range_Sql
16 ( p_cat_structure_id IN NUMBER
17 , p_cat_lo IN VARCHAR2
18 , p_cat_hi IN VARCHAR2
19 , p_item_lo IN VARCHAR2
20 , p_item_hi IN VARCHAR2
21 , p_planner_lo IN VARCHAR2
22 , p_planner_hi IN VARCHAR2
23 , p_lot_ctl IN NUMBER
24 , x_range_sql OUT NOCOPY VARCHAR2
25 );
26
27 PROCEDURE Build_item_cat_select(p_Cat_structure_id IN NUMBER
28 ,x_item_select OUT NOCOPY VARCHAR2
29 ,x_cat_Select OUT NOCOPY VARCHAR2
30 );
31
32 PROCEDURE re_po( item_id IN NUMBER
33 , qty IN NUMBER
34 , nb_time IN DATE
35 , uom IN VARCHAR2
36 , accru_acct IN NUMBER
37 , ipv_acct IN NUMBER
38 , budget_acct IN NUMBER
39 , charge_acct IN NUMBER
40 , purch_flag IN VARCHAR2
41 , order_flag IN VARCHAR2
42 , transact_flag IN VARCHAR2
43 , unit_price IN NUMBER
44 , user_id IN NUMBER
45 , sysd IN DATE
46 , organization_id IN NUMBER
47 , approval IN NUMBER
48 , src_type IN NUMBER
49 , encum_flag IN VARCHAR2
50 , customer_id IN NUMBER
51 , employee_id IN NUMBER
52 , description IN VARCHAR2
53 , src_org IN NUMBER
54 , src_subinv IN VARCHAR2
55 , subinv IN VARCHAR2
56 , location_id IN NUMBER
57 , po_org_id IN NUMBER
58 , p_pur_revision IN NUMBER
59 , x_ret_stat OUT NOCOPY VARCHAR2
60 , x_ret_mesg OUT NOCOPY VARCHAR2);
61
62 PROCEDURE re_wip( item_id IN NUMBER
63 , qty IN NUMBER
64 , nb_time IN DATE
65 , uom IN VARCHAR2
66 , wip_id IN NUMBER
67 , user_id IN NUMBER
68 , sysd IN DATE
69 , organization_id IN NUMBER
70 , approval IN NUMBER
71 , build_in_wip IN VARCHAR2
72 , pick_components IN VARCHAR2
73 , x_ret_stat OUT NOCOPY VARCHAR2
74 , x_ret_mesg OUT NOCOPY VARCHAR2) ;
75
76 PROCEDURE Create_Notification_Details(
77 p_source_type IN VARCHAR2
78 ,p_order_by_dt IN DATE := sysdate
79 ,p_notification_id IN NUMBER
80 ,p_parts_rec IN csp_planner_notifications.excess_parts_rectype);
81
82 PROCEDURE Generate_Repair_Recomm(
83 p_notification_id IN NUMBER
84 ,p_organization_id IN NUMBER
85 ,p_inventory_item_id IN NUMBER
86 ,p_order_by_date IN DATE
87 ,p_supercess_item_yn IN VARCHAR2
88 );
89
90 PROCEDURE Cleanup_Notifications(p_organization_id NUMBER);
91
92 -- End of forward declarations
93
94 PROCEDURE Create_Notifications
95 ( errbuf OUT NOCOPY varchar2
96 ,retcode OUT NOCOPY number
97 ,p_api_version IN NUMBER
98 ,p_organization_id IN NUMBER
99 ,p_level IN NUMBER
100 ,p_notif_for_io IN NUMBER
101 ,p_notif_for_po IN NUMBER
102 ,p_notif_for_wip IN NUMBER
103 ,p_category_set_id IN NUMBER
104 ,p_category_struct_id IN NUMBER
105 ,p_Category_lo IN VARCHAR2
106 ,p_category_hi IN VARCHAR2
107 ,p_item_lo IN VARCHAR2
108 ,p_item_hi IN VARCHAR2
109 ,p_planner_lo IN VARCHAR2
110 ,p_planner_hi IN VARCHAR2
111 ,p_buyer_lo IN VARCHAR2
112 ,p_buyer_hi IN VARCHAR2
113 ,p_d_cutoff_date IN VARCHAR2
114 ,p_d_offset IN NUMBER
115 ,p_s_cutoff_date IN VARCHAR2
116 ,p_s_offset IN NUMBER
117 ,p_restock IN NUMBER
118 ,p_repitem IN VARCHAR2
119 ,p_dd_loc_id IN NUMBER -- default deliver to loc
120 ,p_net_rsv IN NUMBER
121 ,p_net_unrsv IN NUMBER
122 ,p_net_wip IN NUMBER
123 ,p_include_po IN NUMBER
124 ,p_include_wip IN NUMBER
125 ,p_include_iface_sup IN NUMBER
126 ,p_include_nonnet_sub IN NUMBER
127 ,p_lot_control IN NUMBER
128 ,p_sort IN VARCHAR2 := '1'
129 ) IS
130 l_api_version_number CONSTANT NUMBER := 1.0;
131 l_api_name CONSTANT VARCHAR2(30) := 'create_notifications';
132 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
133 l_msg_count NUMBER;
134 l_msg_data VARCHAR2(2000);
135 l_user_id NUMBER;
136 l_login_id NUMBER;
137 l_today DATE;
138 l_employee_id NUMBER;
139 l_Restock NUMBER;
140 l_d_cutoff DATE;
141 l_s_cutoff DATE;
142 l_po_org_id NUMBER;
143 l_org_name VARCHAR2(240);
144 l_encum_flag VARCHAR2(30) := 'N';
145 l_cal_code VARCHAR2(240);
146 l_exception_set_id NUMBER;
147 l_mcat_struct_id NUMBER;
148 l_category_set_id NUMBER;
149 l_range_buyer VARCHAR2(240) := '1=1';
150 l_range_sql VARCHAR2(2000);
151 l_item_select VARCHAr2(800);
152 l_Cat_Select VARCHAR2(800);
153 l_order_by VARCHAr2(30);
154 l_cust_id NUMBER;
155 l_wip_batch_id NUMBER;
156 error_message VARCHAR2(80);
157 l_need_by_date DATE;
158 l_order_by_date DATE;
159 l_est_date DATE;
160 l_lead_time NUMBER;
161 l_notification_id NUMBER;
162 l_count NUMBER;
163 l_header_rec csp_parts_requirement.header_Rec_type;
164 l_line_Tbl csp_parts_requirement.line_tbl_type;
165 l_related_item NUMBER;
166
167 CURSOR employee_id_cur IS
168 SELECT employee_id
169 FROM fnd_user
170 WHERE user_id = l_user_id;
171
172 CURSOR minmax_rslts_cur IS
173 SELECT ITEM_SEGMENTS
174 , DESCRIPTION
175 , ERROR
176 , SORTEE
177 , MIN_QTY
178 , MAX_QTY
179 , ONHAND_QTY
180 , SUPPLY_QTY
181 , DEMAND_QTY
182 , TOT_AVAIL_QTY
183 , MIN_ORD_QTY
184 , MAX_ORD_QTY
185 , FIX_MULT
186 , REORD_QTY
187 FROM INV_MIN_MAX_TEMP;
188
189 l_minmax_rslts_rec minmax_rslts_cur%ROWTYPE;
190
191 CURSOR item_attr_cur(p_item_Segments VARCHAR2,
192 p_organization_id NUMBER) IS
193 SELECT c.description description,
194 c.repetitive_planning_flag repetitive_planned_item,
195 c.fixed_lead_time fixed_lead_time,
196 c.variable_lead_time variable_lead_time,
197 NVL(c.preprocessing_lead_time, 0) +
198 NVL(c.full_lead_time, 0) +
199 NVL(c.postprocessing_lead_time, 0) buying_lead_time,
200 c.primary_uom_code primary_uom,
201 p.ap_accrual_account accru_acct,
202 p.invoice_price_var_account ipv_acct,
203 NVL(c.encumbrance_account, p.encumbrance_account) budget_acct,
204 DECODE(c.inventory_asset_flag, 'Y', p.material_account,
205 NVL(c.expense_account, p.expense_account)) charge_acct,
206 NVL(c.source_type, p.source_type) src_type,
207 DECODE(c.source_type, NULL,
208 DECODE(p.source_type, NULL, NULL, p.source_organization_id),
209 c.source_organization_id) src_org,
210 DECODE(c.source_type, NULL,
211 DECODE(p.source_type, NULL, NULL, p.source_subinventory),
212 c.source_subinventory) src_subinv,
213 c.purchasing_enabled_flag purch_flag,
214 c.internal_order_enabled_flag order_flag,
215 c.mtl_transactions_enabled_flag transact_flag,
216 c.list_price_per_unit unit_price,
217 c.planning_make_buy_code mbf,
218 c.inventory_item_id item_id,
219 c.planner_code planner,
220 build_in_wip_flag build_in_wip,
221 pick_components_flag pick_components
222 FROM mtl_system_items_kfv c,
223 mtl_parameters p
224 WHERE c.concatenated_segments = p_item_Segments
225 AND c.organization_id = p.organization_id
226 AND p.organization_id = p_organization_id;
227
228 CURSOR open_notifs_cur IS
229 SELECT notification_id,
230 inventory_item_id,
231 notification_type,
232 quantity,
233 need_date
234 FROM csp_notifications
235 WHERE organization_id = p_organization_id;
236
237 CURSOR supercess_items_cur(p_item_id NUMBER) IS
238 SELECT inventory_item_id
239 FROM mtl_related_items_view
240 WHERE relationship_type_id = 18
241 AND related_item_id = p_item_id;
242
243 l_item_attr_rec item_attr_cur%ROWTYPE;
244
245 BEGIN
246 SAVEPOINT Create_Notifications_PUB;
247
248 -- Standard call to check for call compatibility.
249 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
250 p_api_version,
251 l_api_name,
252 G_PKG_NAME)
253 THEN
254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
255 END IF;
256
257 -- initialize return status, get audit columns
258 --x_return_status := FND_API.G_RET_STS_SUCCESS;
259 SELECT Sysdate INTO l_today FROM dual;
260 l_user_id := nvl(fnd_global.user_id, 0) ;
261 l_login_id := nvl(fnd_global.login_id, -1);
262
263 Cleanup_Notifications(p_organization_id);
264
265 IF (p_notif_for_io = 2 and p_notif_for_po = 2 and
266 p_notif_for_wip = 2 and p_restock = 2) THEN
267 -- no notifications, no restock, so just return
268 return;
269 ELSIF (p_notif_for_io = 2 and p_notif_for_po = 2 and
270 p_notif_for_wip = 2 and p_Restock = 1) THEN
271 -- no notifications, restock = yes, just call run min_mx with restock = yes
272 l_restock := 1;
273 ELSE
274 -- notifications for atleast one type is yes, call run min_max with
275 -- restock = no, create notifications after min_max is run.
276 l_restock := 2;
277 END IF;
278
279 -- 1. get values of all parameters for calling run_min_max_plan
280 if p_restock = 1 and p_dd_loc_id is null then
281 begin
282 Select MEANING
283 into error_message
284 FROM MFG_LOOKUPS
285 WHERE LOOKUP_TYPE='INV_MMX_RPT_MSGS'
286 and LOOKUP_CODE = 4;
287 exception
288 when others then
289 null;
290 end;
291 --x_Return_status := 'E';
292 end if;
293
294 -- get employee id
295 OPEN employee_id_cur;
296 FETCH employee_id_cur INTO l_employee_id;
297 CLOSE employee_id_cur;
298 l_d_cutoff := to_date(p_d_cutoff_date,'YYYY/MM/DD HH24:MI:SS');
299 l_s_cutoff := to_date(p_s_cutoff_date,'YYYY/MM/DD HH24:MI:SS');
300 l_D_CUTOFF := NVL(l_D_CUTOFF, SYSDATE);
301 l_S_CUTOFF := NVL(l_S_CUTOFF, SYSDATE);
302
303 IF (P_D_OFFSET IS NOT NULL) THEN
304 l_D_CUTOFF := NVL(l_D_CUTOFF, sysdate) + P_D_OFFSET;
305 END IF;
306
307 IF (P_S_OFFSET IS NOT NULL) THEN
308 l_S_CUTOFF := NVL(l_S_CUTOFF, sysdate) + P_S_OFFSET;
309 END IF;
310
311 /* get encum flag, org name, PO org ID */
312 declare
313 l_operating_unit number;
314 begin
315
316 select operating_unit, substr(organization_name,1,30), operating_unit
317 into l_operating_unit, l_org_name, l_po_org_id
318 from org_organization_definitions
319 where organization_id = p_organization_id;
320
321 select nvl(req_encumbrance_flag, 'N')
322 into l_encum_flag
323 from financials_system_params_all
324 where nvl(org_id,-11)=nvl(l_operating_unit,-11);
325
326 end;
327
328 /* get calendar */
329 select p.calendar_code, p.calendar_exception_set_id
330 into l_cal_code, l_exception_set_id
331 from mtl_parameters p
332 where p.organization_id = p_organization_id;
333
334 /* Validate cat set and MCAT struct */
335 IF (p_category_set_id is not null and p_category_struct_id is not null) then
336 SELECT STRUCTURE_ID
337 into l_mcat_struct_id
338 FROM MTL_CATEGORY_SETS
339 WHERE CATEGORY_SET_ID = p_category_set_id;
340 ELSE
341 SELECT CSET.CATEGORY_SET_ID, CSET.STRUCTURE_ID
342 INTO l_category_set_id, l_mcat_struct_id
343 FROM MTL_CATEGORY_SETS CSET,
344 MTL_DEFAULT_CATEGORY_SETS DEF
345 WHERE DEF.CATEGORY_SET_ID = CSET.CATEGORY_SET_ID
346 AND DEF.FUNCTIONAL_AREA_ID = 1;
347 END IF;
348
349 IF p_buyer_lo is not null and p_buyer_hi is not null then
350 L_RANGE_BUYER := 'v.full_name between ' ||''''||P_BUYER_LO||
351 '''' || ' and ' || ''''||P_BUYER_HI||'''';
352 ELSIF p_BUYER_lo is not null then
353 L_RANGE_BUYER := 'v.full_name >= ' ||''''||P_BUYER_LO||'''';
354 ELSIF p_BUYER_hi is not null then
355 L_RANGE_BUYER := 'v.full_name <= ' ||''''||P_BUYER_HI||'''';
356 END IF;
357
358 /* set order by clause */
359
360 IF P_sort=1 then
361 l_order_by := ' order by 1';
362 ELSIF P_sort = 2 then
363 l_order_by := ' order by 13,1';
364 ELSIF P_sort = 3 then
365 l_order_by := ' order by 11,1';
366 ELSIF P_sort = 4 then
367 l_order_by := ' order by 12,1';
368 END IF;
369
370 Build_item_cat_select(
371 p_Cat_Structure_id => l_mcat_struct_id,
372 x_item_select => l_item_Select,
373 x_cat_Select => l_cat_select);
374
375 Build_range_sql(
376 p_cat_structure_id => l_mcat_Struct_id
377 , p_cat_lo => p_Category_lo
378 , p_cat_hi => p_category_hi
379 , p_item_lo => p_item_lo
380 , p_item_hi => p_item_hi
381 , p_planner_lo => p_planner_lo
382 , p_planner_hi => p_planner_hi
383 , p_lot_ctl => p_lot_Control
384 , x_range_sql => l_range_sql);
385
386 IF p_dd_loc_id is not null THEN
387 -- get customer id
388 BEGIN
389 select customer_id
390 into l_cust_id
391 from po_location_associations
392 where location_id = P_dd_loc_id;
393 EXCEPTION
394 when no_data_found then
395 l_cust_id := 0;
396 END;
397 END IF;
398
399 select WIP_JOB_SCHEDULE_INTERFACE_S.nextval
400 into l_WIP_BATCH_ID
401 from dual;
402
403 -- call to min_max API
404 CSP_MINMAX_PVT.run_min_max_plan (
405 p_item_select => l_item_select
406 , p_handle_rep_item => p_repitem
407 , p_pur_revision => nvl(fnd_profile.value('INV_PURCHASING_BY_REVISION'),2)
408 , p_cat_select => l_Cat_select
409 , p_cat_set_id => p_Category_set_id
410 , p_mcat_struct => l_mcat_struct_id
411 , p_level => 1 -- always run at organization level
412 , p_restock => l_Restock
413 , p_include_nonnet => p_include_nonnet_sub
414 , p_include_po => p_include_po
415 , p_include_wip => p_include_wip
416 , p_include_if => p_include_iface_sup
417 , p_net_rsv => p_net_rsv
418 , p_net_unrsv => p_net_unrsv
419 , p_net_wip => p_net_wip
420 , p_org_id => p_organization_id
421 , p_user_id => l_user_id
422 , p_employee_id => l_employee_id
423 , p_subinv => null
424 , p_dd_loc_id => p_dd_loc_id
425 , p_wip_batch_id => l_wip_batch_id
426 , p_approval => to_number(nvl(FND_PROFILE.VALUE('INV_MINMAX_REORDER_APPROVED'),'1'))
427 , p_buyer_hi => p_buyer_hi
428 , p_buyer_lo => p_buyer_lo
429 , p_range_buyer => l_range_buyer
430 , p_cust_id => l_cust_id
431 , p_po_org_id => l_po_org_id
432 , p_range_sql => l_range_Sql
433 , p_sort => p_sort
434 , p_selection => 1 -- items under minimum quantity
435 , p_sysdate => l_today
436 , p_s_cutoff => l_s_cutoff
437 , p_d_cutoff => l_d_cutoff
438 , p_order_by => l_order_by
439 , p_encum_flag => l_encum_flag
440 , p_cal_code => l_cal_code
441 , p_exception_set_id => l_exception_set_id
442 , x_return_status => l_Return_status
443 , x_msg_data => l_msg_data);
444
445 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
446 RAISE FND_API.G_EXC_ERROR;
447 END IF;
448
449 -- 3. if p_restock is 'yes', and notifications for any src type is yes
450 -- create notifications for that src type and restock the rest
451
452 IF (l_Restock = 2) THEN -- inventory min-max did not restock
453
454 FOR l_index IN minmax_rslts_cur LOOP
455 OPEN item_Attr_cur(l_index.ITEM_SEGMENTS, p_organization_id);
456 FETCH item_Attr_cur into l_item_attr_rec;
457 CLOSE item_attr_cur;
458
459 -- check if item is on a suppressed notification
460 begin
461 SELECT count(inventory_item_id)
462 INTO l_count
463 FROM csp_notifications
464 WHERE organization_id = p_organization_id
465 AND inventory_item_id = l_item_attr_rec.item_id
466 AND nvl(suppress_end_date, sysdate) >= sysdate;
467 exception
468 when others then
469 null;
470 end;
471
472 IF (l_count = 0) THEN -- only if item is not on suppresses notif
473 -- calculate need by date based on usage
474 csp_auto_aslmsl_pvt.calculate_needby_date
475 (p_api_version_number => 1.0,
476 p_init_msg_list => FND_API.G_FALSE,
477 p_commit => FND_API.G_FALSE,
478 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
479 p_inventory_item_id => l_item_attr_rec.item_id,
480 p_organization_id => p_organization_id,
481 p_onhand_quantity => l_index.tot_Avail_qty,
482 x_needby_date => l_need_by_date,
483 x_return_status => l_return_status,
484 x_msg_count => l_msg_count,
485 x_msg_data => l_msg_data);
486
487 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
488 RAISE FND_API.G_EXC_ERROR;
489 END IF;
490
491 IF (l_item_attr_rec.repetitive_planned_item = 'Y' AND p_repitem = 1) OR
492 (l_item_attr_rec.repetitive_planned_item = 'N' AND l_item_attr_rec.mbf = 2) THEN
493 IF (l_need_by_date IS NULL) THEN
494 SELECT c1.calendar_date
495 INTO l_need_by_date
496 FROM bom_calendar_dates c1,
497 bom_calendar_dates c
498 WHERE c1.calendar_code = c.calendar_code
499 AND c1.exception_set_id = c.exception_set_id
500 AND c1.seq_num = c.next_seq_num + CEIL(l_item_attr_rec.buying_lead_time)
501 AND c.calendar_code = l_cal_code
502 AND c.exception_set_id = l_exception_set_id
503 AND c.calendar_date = trunc(sysdate);
504
505 END If;
506
507 -- since we are planning at org level, if source_type is Subinventory
508 -- no action is taken
509 IF l_item_attr_rec.src_type = 3 THEN
510 null;
511 ELSIF (l_item_Attr_Rec.src_type = 1) THEN
512 -- if source type is 1-Inventory, internal purchase req
513 IF (p_notif_for_io = 1) THEN
514 -- create notifications for IO
515 l_notification_id := null;
516 csp_notifications_pkg.insert_row(
517 px_notification_id => l_notification_id,
518 p_created_by => l_user_id,
519 p_creation_date => sysdate,
520 p_last_updated_by => l_user_id,
521 p_last_update_date => sysdate,
522 p_last_update_login => l_login_id,
523 p_planner_code => l_item_attr_rec.planner,
524 p_parts_loop_id => null,
525 p_organization_id => p_organization_id,
526 p_inventory_item_id => l_item_attr_rec.item_id,
527 p_notification_date => sysdate,
528 p_reason => 'N',
529 p_status => '1',
530 p_quantity => l_index.reord_qty,
531 p_attribute_category=> null,
532 p_attribute1 => null,
533 p_attribute2 => null,
534 p_attribute3 => null,
535 p_attribute4 => null,
536 p_attribute5 => null,
537 p_attribute6 => null,
538 p_attribute7 => null,
539 p_attribute8 => null,
540 p_attribute9 => null,
541 p_attribute10 => null,
542 p_attribute11 => null,
543 p_attribute12 => null,
544 p_attribute13 => null,
545 p_attribute14 => null,
546 p_attribute15 => null,
547 p_need_date => l_need_by_date,
548 p_suppress_end_date => null,
549 p_notification_type => 'IO');
550
551 ELSIF (p_Restock = 1) THEN -- restock only if p_restock is yes.
552 -- call process_order for creating internal orders
553 l_header_rec.dest_organization_id := p_organization_id;
554 l_header_Rec.need_by_date := l_need_by_date;
555 l_header_rec.operation := 'CREATE';
556 l_header_rec.ship_to_location_id := p_dd_loc_id;
557 FND_PROFILE.GET('CSP_ORDER_TYPE', l_header_rec.order_type_id);
558
559 l_line_tbl(1).line_num := 1;
560 l_line_tbl(1).inventory_item_id := l_item_attr_rec.item_id;
561 l_line_tbl(1).quantity := l_index.reord_qty;
562 l_line_tbl(1).ordered_quantity := l_index.reord_qty;
563 l_line_Tbl(1).unit_of_measure := l_item_Attr_rec.primary_uom;
564 l_line_Tbl(1).source_organization_id := l_item_Attr_rec.src_org;
565 l_line_Tbl(1).source_subinventory := l_item_attr_Rec.src_subinv;
566 l_line_tbl(1).booked_flag := 'Y';
567
568 -- call process order
569 csp_parts_order.process_order(
570 p_api_version => l_api_Version_number
571 ,p_Init_Msg_List => null
572 ,p_commit => null
573 ,px_header_rec => l_header_Rec
574 ,px_line_table => l_Line_Tbl
575 ,p_process_type => 'BOTH'
576 ,x_return_status => l_return_status
577 ,x_msg_count => l_msg_count
578 ,x_msg_data => l_msg_data
579 );
580
581 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
582 RAISE FND_API.G_EXC_ERROR;
583 END IF;
584 END IF;
585 ELSIF (l_item_attr_rec.src_type = 2) THEN
586 -- if source type is 2-Supplier, external purchase req.
587 IF (p_notif_for_po = 1) THEN
588 -- create notifications for PO
589 l_notification_id := null;
590 csp_notifications_pkg.insert_row(
591 px_notification_id => l_notification_id,
592 p_created_by => l_user_id,
593 p_creation_date => sysdate,
594 p_last_updated_by => l_user_id,
595 p_last_update_date => sysdate,
596 p_last_update_login => l_login_id,
597 p_planner_code => l_item_attr_rec.planner,
598 p_parts_loop_id => null,
599 p_organization_id => p_organization_id,
600 p_inventory_item_id => l_item_attr_rec.item_id,
601 p_notification_date => sysdate,
602 p_reason => 'N',
603 p_status => '1',
604 p_quantity => l_index.reord_qty,
605 p_attribute_category=> null,
606 p_attribute1 => null,
607 p_attribute2 => null,
608 p_attribute3 => null,
609 p_attribute4 => null,
610 p_attribute5 => null,
611 p_attribute6 => null,
612 p_attribute7 => null,
613 p_attribute8 => null,
614 p_attribute9 => null,
615 p_attribute10 => null,
616 p_attribute11 => null,
617 p_attribute12 => null,
618 p_attribute13 => null,
619 p_attribute14 => null,
620 p_attribute15 => null,
621 p_need_date => l_need_by_date,
622 p_suppress_end_date => null,
623 p_notification_type => 'PO');
624
625 ELSIF (p_restock = 1) THEN
626 -- call re_po for creating pur req.
627 re_po(
628 item_id => l_item_attr_rec.item_id
629 , qty => l_index.reord_qty
630 , nb_time => l_need_by_date
631 , uom => l_item_Attr_rec.primary_uom
632 , accru_acct => l_item_attr_rec.accru_acct
633 , ipv_acct => l_item_attr_rec.ipv_Acct
634 , budget_acct => l_item_attr_rec.budget_acct
635 , charge_acct => l_item_attr_rec.charge_Acct
636 , purch_flag => l_item_attr_rec.purch_flag
637 , order_flag => l_item_attr_Rec.order_flag
638 , transact_flag => l_item_attr_rec.transact_flag
639 , unit_price => l_item_Attr_rec.unit_price
640 , user_id => l_user_id
641 , sysd => sysdate
642 , organization_id => p_organization_id
643 , approval => to_number(nvl(FND_PROFILE.VALUE('INV_MINMAX_REORDER_APPROVED'),'1'))
644 , src_type => l_item_attr_rec.src_Type
645 , encum_flag => l_encum_flag
646 , customer_id => l_cust_id
647 , employee_id => l_employee_id
648 , description => l_item_attr_rec.description
649 , src_org => l_item_Attr_rec.src_org
650 , src_subinv => l_item_attr_Rec.src_subinv
651 , subinv => null
652 , location_id => p_dd_loc_id
653 , po_org_id => l_po_org_id
654 , p_pur_revision => nvl(fnd_profile.value('INV_PURCHASING_BY_REVISION'),2)
655 , x_ret_stat => l_return_status
656 , x_ret_mesg => l_msg_data);
657
658 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
659 RAISE FND_API.G_EXC_ERROR;
660 END IF;
661 END IF;
662 ELSE
663 -- no source type defined, create a notification with source missing.
664 l_notification_id := null;
665 csp_notifications_pkg.insert_row(
666 px_notification_id => l_notification_id,
667 p_created_by => l_user_id,
668 p_creation_date => sysdate,
669 p_last_updated_by => l_user_id,
670 p_last_update_date => sysdate,
671 p_last_update_login => l_login_id,
672 p_planner_code => l_item_attr_rec.planner,
673 p_parts_loop_id => null,
674 p_organization_id => p_organization_id,
675 p_inventory_item_id => l_item_attr_rec.item_id,
676 p_notification_date => sysdate,
677 p_reason => 'N',
678 p_status => '1',
679 p_quantity => l_index.reord_qty,
680 p_attribute_category=> null,
681 p_attribute1 => null,
682 p_attribute2 => null,
683 p_attribute3 => null,
684 p_attribute4 => null,
685 p_attribute5 => null,
686 p_attribute6 => null,
687 p_attribute7 => null,
688 p_attribute8 => null,
689 p_attribute9 => null,
690 p_attribute10 => null,
691 p_attribute11 => null,
692 p_attribute12 => null,
693 p_attribute13 => null,
694 p_attribute14 => null,
695 p_attribute15 => null,
696 p_need_date => null, --l_need_by_date,
697 p_suppress_end_date => null,
698 p_notification_type => 'NS');
699 END IF;
700 ELSE -- wip item
701 IF (l_need_by_date IS NULL) THEN -- need by date is null
702 l_lead_time := nvl(l_item_attr_rec.fixed_lead_time, 0) +
703 (l_index.reord_qty * nvl(l_item_attr_rec.variable_lead_time,0));
704 BEGIN
705 SELECT c1.calendar_date
706 INTO l_need_by_date
707 FROM bom_calendar_dates c1,
708 bom_calendar_dates c
709 WHERE c1.calendar_code = c.calendar_code
710 AND c1.exception_set_id = c.exception_set_id
711 AND c1.seq_num = (c.next_seq_num + CEIL(l_lead_time))
712 AND c.calendar_code = l_cal_code
713 AND c.exception_set_id = l_exception_set_id
714 AND c.calendar_date = trunc(sysdate);
715 EXCEPTION
716 WHEN NO_DATA_FOUND THEN
717 l_need_by_date := sysdate;
718 END;
719 END If;
720
721 IF (p_notif_for_wip = 1) THEN
722 -- create notifications for make items
723 l_notification_id := null;
724 csp_notifications_pkg.insert_row(
725 px_notification_id => l_notification_id,
726 p_created_by => l_user_id,
727 p_creation_date => sysdate,
728 p_last_updated_by => l_user_id,
729 p_last_update_date => sysdate,
730 p_last_update_login => l_login_id,
731 p_planner_code => l_item_attr_rec.planner,
732 p_parts_loop_id => null,
733 p_organization_id => p_organization_id,
734 p_inventory_item_id => l_item_attr_rec.item_id,
735 p_notification_date => sysdate,
736 p_reason => 'N',
737 p_status => '1',
738 p_quantity => l_index.reord_qty,
739 p_attribute_category=> null,
740 p_attribute1 => null,
741 p_attribute2 => null,
742 p_attribute3 => null,
743 p_attribute4 => null,
744 p_attribute5 => null,
745 p_attribute6 => null,
746 p_attribute7 => null,
747 p_attribute8 => null,
748 p_attribute9 => null,
749 p_attribute10 => null,
750 p_attribute11 => null,
751 p_attribute12 => null,
752 p_attribute13 => null,
753 p_attribute14 => null,
754 p_attribute15 => null,
755 p_need_date => l_need_by_date,
756 p_suppress_end_date => null,
757 p_notification_type => 'WIP');
758
759 ELSIF (p_restock = 1) THEN
760 -- call re_wip
761 re_wip( item_id => l_item_attr_rec.item_id
762 , qty => l_index.REORD_QTY
763 , nb_time => null
764 , uom => l_item_Attr_rec.primary_uom
765 , wip_id => l_wip_batch_id
766 , user_id => l_user_id
767 , sysd => sysdate
768 , organization_id => p_organization_id
769 , approval => to_number(nvl(FND_PROFILE.VALUE('INV_MINMAX_REORDER_APPROVED'),'1'))
770 , build_in_wip => l_item_attr_rec.build_in_wip
771 , pick_components => l_item_attr_rec.pick_components
772 , x_ret_stat => l_return_status
773 , x_ret_mesg => l_msg_data);
774 END IF;
775 END IF;
776 END IF;
777 END LOOP;
778 -- cleanup INV_MIN_MAX_TEMP table
779 delete from INV_MIN_MAX_TEMP;
780 END IF;
781
782 -- create notifications for excess on order
783
784 -- call min max api with selection as above max qty
785 CSP_MINMAX_PVT.run_min_max_plan (
786 p_item_select => l_item_select
787 , p_handle_rep_item => p_repitem
788 , p_pur_revision => nvl(fnd_profile.value('INV_PURCHASING_BY_REVISION'),2)
789 , p_cat_select => l_Cat_select
790 , p_cat_set_id => p_Category_set_id
791 , p_mcat_struct => l_mcat_struct_id
792 , p_level => 1 -- always run at organization level
793 , p_restock => 2
794 , p_include_nonnet => p_include_nonnet_sub
795 , p_include_po => p_include_po
796 , p_include_wip => p_include_wip
797 , p_include_if => p_include_iface_sup
798 , p_net_rsv => p_net_rsv
799 , p_net_unrsv => p_net_unrsv
800 , p_net_wip => p_net_wip
801 , p_org_id => p_organization_id
802 , p_user_id => l_user_id
803 , p_employee_id => l_employee_id
804 , p_subinv => null
805 , p_dd_loc_id => p_dd_loc_id
806 , p_wip_batch_id => l_wip_batch_id
807 , p_approval => to_number(nvl(FND_PROFILE.VALUE('INV_MINMAX_REORDER_APPROVED'),'1'))
808 , p_buyer_hi => p_buyer_hi
809 , p_buyer_lo => p_buyer_lo
810 , p_range_buyer => l_range_buyer
811 , p_cust_id => l_cust_id
812 , p_po_org_id => l_po_org_id
813 , p_range_sql => l_range_Sql
814 , p_sort => p_sort
815 , p_selection => 2 -- items above maximum quantity
816 , p_sysdate => l_today
817 , p_s_cutoff => (l_s_cutoff + 10000)
818 , p_d_cutoff => l_d_cutoff
819 , p_order_by => l_order_by
820 , p_encum_flag => l_encum_flag
821 , p_cal_code => l_cal_code
822 , p_exception_set_id => l_exception_set_id
823 , x_return_status => l_Return_status
824 , x_msg_data => l_msg_data);
825
826 -- for all records in inv_min_max_temp with supply qty > 0
827 -- create EOO notifications
828 declare
829 l_item_id NUMBER;
830 l_planner VARCHAR2(30);
831 l_edq_multiple NUMBER;
832 l_min_value NUMBER;
833 l_Edq NUMBER;
834 l_item_cost NUMBER;
835 l_limit NUMBER;
836 l_EOO_Qty NUMBER;
837 l_supply_qty NUMBER;
838 l_tot_avail_qty NUMBER;
839 begin
840 FOR mrc in minmax_rslts_cur LOOP
841 SELECT msik.planner_code,
842 msik.inventory_item_id
843 INTO l_planner,
844 l_item_id
845 FROM mtl_system_items_kfv msik
846 WHERE msik.concatenated_segments = mrc.item_segments
847 AND msik.organization_id = p_organization_id;
848
849 -- check if item is on a suppressed notification
850 l_count := 0;
851 begin
852 SELECT count(inventory_item_id)
853 INTO l_count
854 FROM csp_notifications
855 WHERE organization_id = p_organization_id
856 AND inventory_item_id = l_item_id
857 AND nvl(suppress_end_date, sysdate) >= sysdate;
858 exception
859 when others then
860 null;
861 end;
862
863 IF (l_count = 0) THEN -- only if item is not on suppresses notif
864
865 BEGIN
866 SELECT sum(pol.quantity)
867 INTO l_supply_qty
868 FROM po_requisition_headers_all poh,
869 po_requisition_lines_all pol
870 WHERE poh.authorization_status = 'INCOMPLETE'
871 AND pol.requisition_header_id = poh.requisition_header_id
872 AND pol.destination_type_code = 'INVENTORY'
873 AND pol.item_id = l_item_id
874 AND pol.destination_organization_id = p_organization_id;
875 EXCEPTION
876 when no_Data_found then
877 l_supply_qty := 0;
878 END;
879
880 l_tot_Avail_qty := nvl(mrc.TOT_AVAIL_QTY,0) + nvl(l_supply_qty, 0);
881 l_supply_qty := nvl(l_supply_qty,0) + nvl(mrc.supply_qty,0);
882
883 IF (nvl(l_SUPPLY_QTY,0) > 0) THEN
884 select edq_multiple,
885 minimum_Value
886 into l_Edq_multiple,
887 l_min_Value
888 from csp_planning_parameters
889 where organization_id = p_organization_id
890 and secondary_inventory is null;
891
892 l_edq := mrc.MAX_QTY - mrc.MIN_QTY;
893 l_limit := (mrc.min_qty + (l_edq * nvl(l_edq_multiple, 1)));
894 IF ((l_TOT_AVAIL_QTY - l_limit) >= l_supply_qty) THEN
895 l_EOO_Qty := l_SUPPLY_QTY;
896 ELSE
897 l_EOO_Qty := l_TOT_AVAIL_QTY - l_limit;
898 END IF;
899
900 IF (l_EOO_qty > 0) THEN
901 BEGIN
902 SELECT cic.item_cost
903 INTO l_item_cost
904 FROM cst_item_costs cic,
905 mtl_parameters mp
906 WHERE cic.inventory_item_id = l_item_id
907 AND cic.organization_id = mp.organization_id
908 AND cic.cost_type_id = mp.primary_cost_method
909 AND mp.organization_id = p_organization_id;
910 EXCEPTION
911 WHEN no_data_found then
912 l_item_cost := 0;
913 END;
914
915 IF ((nvl(l_item_cost,0) * l_EOO_qty) > nvl(l_min_Value, 0)) THEN
916 l_notification_id := null;
917 csp_notifications_pkg.insert_row(
918 px_notification_id => l_notification_id,
919 p_created_by => l_user_id,
920 p_creation_date => sysdate,
921 p_last_updated_by => l_user_id,
922 p_last_update_date => sysdate,
923 p_last_update_login => l_login_id,
924 p_planner_code => l_planner,
925 p_parts_loop_id => null,
926 p_organization_id => p_organization_id,
927 p_inventory_item_id => l_item_id,
928 p_notification_date => sysdate,
929 p_reason => 'N',
930 p_status => '1',
931 p_quantity => l_EOO_qty,
932 p_attribute_category=> null,
933 p_attribute1 => null,
934 p_attribute2 => null,
935 p_attribute3 => null,
936 p_attribute4 => null,
937 p_attribute5 => null,
938 p_attribute6 => null,
939 p_attribute7 => null,
940 p_attribute8 => null,
941 p_attribute9 => null,
942 p_attribute10 => null,
943 p_attribute11 => null,
944 p_attribute12 => null,
945 p_attribute13 => null,
946 p_attribute14 => null,
947 p_attribute15 => null,
948 p_need_date => null,
949 p_suppress_end_date => null,
950 p_notification_type => 'EOO');
951 END IF;
952 END IF;
953 END IF;
954 END IF;
955 END LOOP;
956 end;
957 -- cleanup minmax temp tbl
958 DELETE FROM INV_MIN_MAX_TEMP;
959
960 -- For all IO/PO/WIP notifs in notifications tbl,
961 -- create recommendations for excess, repair and new buy/make
962 -- For all EOO notifications,
963 -- 1. go thru requisitions and req interface tbls for IO and
964 -- PO cancel recommendations
965 -- 2. go thru wip interface and wip jobs tbls for make cancel recomm
966 FOR onc IN open_notifs_cur LOOP
967 IF (onc.notification_type <> 'EOO') THEN
968
969 declare
970 l_item_rec CSP_PLANNER_NOTIFICATIONS.item_list_rectype;
971 l_excess_parts_tbl CSP_PLANNER_NOTIFICATIONS.excess_parts_tbl;
972 l_fixed_lt NUMBER;
973 l_variable_lt NUMBER;
974 l_buying_lt NUMBER;
975 begin
976 l_item_rec.inventory_item_id := onc.inventory_item_id;
977 l_item_rec.category_set_id := p_Category_set_id;
978 l_item_rec.d_cutoff := l_d_cutoff;
979 l_item_rec.s_cutoff := l_s_cutoff;
980 l_item_rec.repitem := p_repitem;
981 l_item_rec.net_rsv := p_net_rsv;
982 l_item_rec.net_unrsv := p_net_unrsv;
983 l_item_rec.net_wip := p_net_wip;
984 l_item_Rec.include_po := p_include_po;
985 l_item_rec.include_wip := p_include_wip;
986 l_item_rec.include_iface_sup := p_include_iface_sup;
987 l_item_rec.include_nonnet_sub := 2;
988 l_item_rec.lot_control := p_lot_control;
989 l_item_Rec.employee_id := l_employee_id;
990
991 select c.fixed_lead_time fixed_lead_time,
992 c.variable_lead_time variable_lead_time,
993 NVL(c.preprocessing_lead_time, 0) +
994 NVL(c.full_lead_time, 0) +
995 NVL(c.postprocessing_lead_time, 0) buying_lead_time
996 into l_fixed_lt,
997 l_Variable_lt,
998 l_buying_lt
999 from mtl_system_items c
1000 where c.inventory_item_id = onc.inventory_item_id
1001 and organization_id = p_organization_id;
1002
1003 IF (onc.notification_type IN ('IO', 'PO')) THEN
1004 --
1005 -- Lead time for buy items is sum of POSTPROCESSING_LEAD_TIME,
1006 -- PREPROCESSING_LEAD_TIME AND PROCESSING_LEAD_TIME (sub level)
1007 -- OR POSTPROCESSING_LEAD_TIME, PREPROCESSING_LEAD_TIME
1008 -- AND FULL_LEAD_TIME (item level)
1009 --
1010 -- Here, total lead time is the total buying Lead time
1011 --
1012
1013 BEGIN
1014 SELECT c1.calendar_date
1015 INTO l_est_date
1016 FROM bom_calendar_dates c1,
1017 bom_calendar_dates c
1018 WHERE c1.calendar_code = c.calendar_code
1019 AND c1.exception_set_id = c.exception_set_id
1020 AND c1.seq_num = c.prior_seq_num - CEIL(l_buying_lt)
1021 AND c.calendar_code = l_cal_code
1022 AND c.exception_set_id = l_exception_set_id
1023 AND c.calendar_date = trunc(onc.need_date);
1024
1025 IF (l_est_date >= trunc(sysdate)) THEN
1026 l_order_by_date := l_est_date;
1027 ELSE
1028 l_order_by_date := trunc(sysdate);
1029 END IF;
1030 EXCEPTION
1031 WHEN NO_DATA_FOUND THEN -- need by date not defined in calendar
1032 l_order_by_date := l_need_by_date - CEIL(l_buying_lt);
1033 WHEN OTHERS THEN
1034 null;
1035 END;
1036 ELSIF (onc.notification_type = 'WIP') THEN
1037 l_lead_time := NVL(l_fixed_lt,0) +
1038 NVL(l_variable_lt,0) * onc.quantity;
1039 BEGIN
1040 SELECT c1.calendar_date
1041 INTO l_est_date
1042 FROM bom_calendar_dates c1,
1043 bom_calendar_dates c
1044 WHERE c1.calendar_code = c.calendar_code
1045 AND c1.exception_set_id = c.exception_set_id
1046 AND c1.seq_num = (c.prior_seq_num - CEIL(l_lead_time))
1047 AND c.calendar_code = l_cal_code
1048 AND c.exception_set_id = l_exception_set_id
1049 AND c.calendar_date = trunc(onc.need_date);
1050
1051 IF (l_est_date >= trunc(sysdate)) THEN
1052 l_order_by_date := l_est_date;
1053 ELSE
1054 l_order_by_date := trunc(sysdate);
1055 END IF;
1056 EXCEPTION
1057 WHEN NO_DATA_FOUND THEN -- need by date not defined in calendar
1058 l_order_by_date := l_need_by_date - CEIL(l_lead_time);
1059 WHEN OTHERS THEN
1060 null;
1061 END;
1062 END IF;
1063
1064 -- generate excess recommendations
1065 Calculate_Excess(
1066 p_organization_id => p_organization_id
1067 ,p_item_rec => l_item_Rec
1068 ,p_called_from => 'NOTIF'
1069 ,p_notification_id => onc.notification_id
1070 ,p_order_by_date => l_order_by_date
1071 ,x_excess_parts_tbl => l_Excess_parts_Tbl
1072 ,x_return_status => l_return_status
1073 ,x_msg_data => l_msg_Data
1074 ,x_msg_count => l_msg_count);
1075
1076 -- generate repair recommendations
1077 -- check to see if item can be repaired to itself
1078 begin
1079 SELECT related_item_id
1080 INTO l_related_item
1081 FROM mtl_related_items_view
1082 WHERE relationship_type_id = 18
1083 AND inventory_item_id = onc.inventory_item_id;
1084 exception
1085 when NO_DATA_FOUND then
1086 Generate_Repair_Recomm(
1087 p_notification_id => onc.notification_id
1088 ,p_organization_id => p_organization_id
1089 ,p_inventory_item_id => onc.inventory_item_id
1090 ,p_order_by_date => l_order_by_date
1091 ,p_supercess_item_yn => 'N'
1092 );
1093 when TOO_MANY_ROWS then
1094 null;
1095 end;
1096 -- generate repair recommendations for all superceded items
1097 FOR sic IN supercess_items_cur(onc.inventory_item_id) LOOP
1098 Generate_Repair_Recomm(
1099 p_notification_id => onc.notification_id
1100 ,p_organization_id => p_organization_id
1101 ,p_inventory_item_id => sic.inventory_item_id
1102 ,p_order_by_date => l_order_by_date
1103 ,p_supercess_item_yn => 'Y'
1104 );
1105 END LOOP;
1106
1107 -- generate new buy recommendations if notifications type is not
1108 -- 'No source Notification'
1109 IF (onc.notification_type <> 'NS') THEN
1110 declare
1111 l_parts_rec CSP_PLANNER_NOTIFICATIONS.excess_parts_rectype;
1112 l_business_rule_rec CSP_PLANNER_NOTIFICATIONS.business_rule_rectype;
1113 l_source_type VARCHAR2(30) := 'IO';
1114 l_business_rule_id NUMBER;
1115 l_create_notif VARCHAR2(1) := 'Y';
1116 l_total_excess NUMBER;
1117 l_total_repair NUMBER;
1118 l_item_cost NUMBER;
1119 l_tracking_signal NUMBER;
1120
1121 CURSOR tracking_signal_cur IS
1122 SELECT tracking_signal
1123 FROM csp_usage_headers
1124 WHERE organization_id = p_organization_id
1125 AND inventory_item_id = onc.inventory_item_id
1126 AND header_Data_type = 4;
1127
1128 CURSOR item_attr_cur1(p_item_id NUMBER) IS
1129 SELECT c.description description,
1130 c.repetitive_planning_flag repetitive_planned_item,
1131 c.fixed_lead_time fixed_lead_time,
1132 c.variable_lead_time variable_lead_time,
1133 NVL(c.preprocessing_lead_time, 0) +
1134 NVL(c.full_lead_time, 0) +
1135 NVL(c.postprocessing_lead_time, 0) buying_lead_time,
1136 c.primary_uom_code primary_uom,
1137 p.ap_accrual_account accru_acct,
1138 p.invoice_price_var_account ipv_acct,
1139 NVL(c.encumbrance_account, p.encumbrance_account) budget_acct,
1140 DECODE(c.inventory_asset_flag, 'Y', p.material_account,
1141 NVL(c.expense_account, p.expense_account)) charge_acct,
1142 NVL(c.source_type, p.source_type) src_type,
1143 DECODE(c.source_type, NULL,
1144 DECODE(p.source_type, NULL, NULL, p.source_organization_id),
1145 c.source_organization_id) src_org,
1146 DECODE(c.source_type, NULL,
1147 DECODE(p.source_type, NULL, NULL, p.source_subinventory),
1148 c.source_subinventory) src_subinv,
1149 c.purchasing_enabled_flag purch_flag,
1150 c.internal_order_enabled_flag order_flag,
1151 c.mtl_transactions_enabled_flag transact_flag,
1152 c.list_price_per_unit unit_price,
1153 c.planning_make_buy_code mbf,
1154 c.planner_code planner,
1155 build_in_wip_flag build_in_wip,
1156 pick_components_flag pick_components
1157 FROM mtl_system_items_kfv c,
1158 mtl_parameters p
1159 WHERE c.inventory_item_id = p_item_id
1160 AND c.organization_id = p.organization_id
1161 AND p.organization_id = p_organization_id;
1162
1163 l_item_attr_rec1 item_attr_cur1%ROWTYPE;
1164 begin
1165 -- If restock = 'Y', look at the business rules for automating the notification.
1166 IF (p_restock = 1) THEN
1167 begin
1168 SELECT notification_rule_id
1169 INTO l_business_rule_id
1170 FROM csp_planning_parameters
1171 WHERE organization_id = p_organization_id
1172 AND node_type = 'ORGANIZATION_WH';
1173
1174 IF l_business_rule_id IS NOT NULL THEN
1175 SELECT IO_Excess_Value
1176 ,IO_Repair_Value
1177 ,IO_Recommend_Value
1178 ,IO_Tracking_Signal_Max
1179 ,IO_Tracking_Signal_Min
1180 ,REQ_Excess_Value
1181 ,REQ_Repair_Value
1182 ,REQ_Recommend_Value
1183 ,REQ_Tracking_Signal_Max
1184 ,REQ_Tracking_Signal_Min
1185 ,WIP_Order_Excess_Value
1186 ,WIP_Order_Repair_Value
1187 ,WIP_Order_Recommend_Value
1188 ,WIP_Order_Tracking_Signal_Max
1189 ,WIP_Order_Tracking_Signal_Min
1190 INTO l_business_rule_rec
1191 FROM csp_notification_rules_vl
1192 WHERE notification_rule_id = l_business_rule_id;
1193
1194 begin
1195 SELECT cic.item_cost
1196 INTO l_item_cost
1197 FROM cst_item_costs cic,
1198 mtl_parameters mp
1199 WHERE cic.inventory_item_id = onc.inventory_item_id
1200 AND cic.organization_id = mp.organization_id
1201 AND cic.cost_type_id = mp.primary_cost_method
1202 AND mp.organization_id = p_organization_id;
1203 exception
1204 when no_data_found THEN
1205 l_item_cost := 0;
1206 end;
1207
1208 SELECT nvl(SUM(DECODE(cnd.source_type, 'EXCESS', cnd.available_quantity, null)), 0) AS Excess_Qty,
1209 nvl(SUM(DECODE(cnd.source_type, 'REPAIR', cnd.available_quantity, null)), 0) AS Repair_Qty
1210 INTO l_total_excess, l_total_repair
1211 FROM csp_notification_details cnd
1212 WHERE notification_id = onc.notification_id;
1213
1214 IF (onc.notification_type = 'IO') THEN
1215 IF (((l_total_excess * nvl(l_item_cost,0)) <= nvl(l_business_rule_rec.IO_Excess_Value,0)) AND
1216 ((l_total_repair * nvl(l_item_cost,0)) <= nvl(l_business_rule_rec.IO_Repair_Value,0)) AND
1217 ((onc.quantity * nvl(l_item_cost,0)) <= nvl(l_business_rule_rec.IO_recommend_value,0))) THEN
1218 -- calculate tracking signal and test
1219 OPEN tracking_signal_cur;
1220 FETCH tracking_signal_cur INTO l_Tracking_signal;
1221 CLOSE tracking_Signal_cur;
1222
1223 IF ((nvl(l_Tracking_signal,0) >= nvl(l_business_rule_rec.IO_Tracking_Signal_Min, 0)) AND
1224 (nvl(l_Tracking_signal,0) <= nvl(l_business_rule_rec.IO_Tracking_Signal_Max,0))) THEN
1225
1226 l_create_notif := 'N';
1227
1228 OPEN item_attr_cur1(onc.inventory_item_id);
1229 FETCH item_attr_cur1 INTO l_item_attr_rec1;
1230 CLOSE item_Attr_cur1;
1231
1232 -- call process_order for creating internal orders
1233 l_header_rec.dest_organization_id := p_organization_id;
1234 l_header_Rec.need_by_date := onc.need_Date;
1235 l_header_rec.operation := 'CREATE';
1236 l_header_rec.ship_to_location_id := p_dd_loc_id;
1237 FND_PROFILE.GET('CSP_ORDER_TYPE', l_header_rec.order_type_id);
1238
1239 l_line_tbl(1).line_num := 1;
1240 l_line_tbl(1).inventory_item_id := onc.inventory_item_id;
1241 l_line_tbl(1).quantity := onc.quantity;
1242 l_line_tbl(1).ordered_quantity := onc.quantity;
1243 l_line_Tbl(1).unit_of_measure := l_item_Attr_rec1.primary_uom;
1244 l_line_Tbl(1).source_organization_id := l_item_Attr_rec1.src_org;
1245 l_line_Tbl(1).source_subinventory := l_item_attr_Rec1.src_subinv;
1246 l_line_tbl(1).booked_flag := 'Y';
1247
1248 -- call process order
1249 csp_parts_order.process_order(
1250 p_api_version => l_api_Version_number
1251 ,p_Init_Msg_List => null
1252 ,p_commit => null
1253 ,px_header_rec => l_header_Rec
1254 ,px_line_table => l_Line_Tbl
1255 ,p_process_type => 'BOTH'
1256 ,x_return_status => l_return_status
1257 ,x_msg_count => l_msg_count
1258 ,x_msg_data => l_msg_data
1259 );
1260
1261 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1262 RAISE FND_API.G_EXC_ERROR;
1263 ELSE
1264 update csp_notifications
1265 set status = 5
1266 where notification_id = onc.notification_id;
1267
1268 IF ((l_total_excess > 0) OR (l_total_repair > 0)) THEN
1269 DELETE FROM csp_notification_Details
1270 WHERE notification_id = onc.notification_id;
1271 END IF;
1272 END IF;
1273 ELSE
1274 l_create_notif := 'Y';
1275 END IF;
1276 ELSE
1277 l_create_notif := 'Y';
1278 END IF;
1279 ELSIF (onc.notification_type = 'PO') THEN
1280 -- check PO parameters
1281 IF (((l_total_excess * nvl(l_item_cost,0)) <= nvl(l_business_rule_rec.REQ_Excess_Value,0)) AND
1282 ((l_total_repair * nvl(l_item_cost,0)) <= nvl(l_business_rule_rec.REQ_Repair_Value,0)) AND
1283 ((onc.quantity * nvl(l_item_cost,0)) <= nvl(l_business_rule_rec.REQ_recommend_value,0))) THEN
1284 -- calculate tracking signal and test
1285 OPEN tracking_signal_cur;
1286 FETCH tracking_signal_cur INTO l_Tracking_signal;
1287 CLOSE tracking_Signal_cur;
1288
1289 IF ((nvl(l_Tracking_signal,0) >= nvl(l_business_rule_rec.REQ_Tracking_Signal_Min, 0)) AND
1290 (nvl(l_Tracking_signal, 0) <= nvl(l_business_rule_rec.REQ_Tracking_Signal_Max, 0))) THEN
1291
1292 l_create_notif := 'N';
1293
1294 OPEN item_attr_cur1(onc.inventory_item_id);
1295 FETCH item_attr_cur1 INTO l_item_attr_rec1;
1296 CLOSE item_Attr_cur1;
1297
1298 re_po(
1299 item_id => onc.inventory_item_id
1300 , qty => onc.quantity
1301 , nb_time => onc.need_date
1302 , uom => l_item_Attr_rec1.primary_uom
1303 , accru_acct => l_item_attr_rec1.accru_acct
1304 , ipv_acct => l_item_attr_rec1.ipv_Acct
1305 , budget_acct => l_item_attr_rec1.budget_acct
1306 , charge_acct => l_item_attr_rec1.charge_Acct
1307 , purch_flag => l_item_attr_rec1.purch_flag
1308 , order_flag => l_item_attr_Rec1.order_flag
1309 , transact_flag => l_item_attr_rec1.transact_flag
1310 , unit_price => l_item_Attr_rec1.unit_price
1311 , user_id => l_user_id
1312 , sysd => sysdate
1313 , organization_id => p_organization_id
1314 , approval => to_number(nvl(FND_PROFILE.VALUE('INV_MINMAX_REORDER_APPROVED'),'1'))
1315 , src_type => l_item_attr_rec1.src_Type
1316 , encum_flag => l_encum_flag
1317 , customer_id => l_cust_id
1318 , employee_id => l_employee_id
1319 , description => l_item_attr_rec1.description
1320 , src_org => l_item_Attr_rec1.src_org
1321 , src_subinv => l_item_attr_Rec1.src_subinv
1322 , subinv => null
1323 , location_id => p_dd_loc_id
1324 , po_org_id => l_po_org_id
1325 , p_pur_revision => nvl(fnd_profile.value('INV_PURCHASING_BY_REVISION'),2)
1326 , x_ret_stat => l_return_status
1327 , x_ret_mesg => l_msg_data);
1328
1329 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1330 RAISE FND_API.G_EXC_ERROR;
1331 ELSE
1332 update csp_notifications
1333 set status = 5
1334 where notification_id = onc.notification_id;
1335
1336 IF ((l_total_excess > 0) OR (l_total_repair > 0)) THEN
1337 DELETE FROM csp_notification_Details
1338 WHERE notification_id = onc.notification_id;
1339 END IF;
1340 END IF;
1341 ELSE
1342 l_create_notif := 'Y';
1343 END IF;
1344 ELSE
1345 l_create_notif := 'Y';
1346 END IF;
1347 ELSIF (onc.notification_type = 'WIP') THEN
1348 IF (((l_total_excess * nvl(l_item_cost,0)) <= nvl(l_business_rule_rec.WIP_Excess_Value,0)) AND
1349 ((l_total_repair * nvl(l_item_cost,0)) <= nvl(l_business_rule_rec.WIP_Repair_Value,0)) AND
1350 ((onc.quantity * nvl(l_item_cost,0)) <= nvl(l_business_rule_rec.WIP_Recommend_value,0))) THEN
1351 -- calculate tracking signal and test
1352 OPEN tracking_signal_cur;
1353 FETCH tracking_signal_cur INTO l_Tracking_signal;
1354 CLOSE tracking_Signal_cur;
1355
1356 IF ((nvl(l_Tracking_signal,0) >= nvl(l_business_rule_rec.WIP_Tracking_Signal_Min, 0)) AND
1357 (nvl(l_Tracking_signal,0) <= nvl(l_business_rule_rec.WIP_Tracking_Signal_Max, 0))) THEN
1358
1359 l_create_notif := 'N';
1360
1361 OPEN item_attr_cur1(onc.inventory_item_id);
1362 FETCH item_attr_cur1 INTO l_item_attr_rec1;
1363 CLOSE item_Attr_cur1;
1364
1365 re_wip( item_id => onc.inventory_item_id
1366 , qty => onc.quantity
1367 , nb_time => null
1368 , uom => l_item_Attr_rec1.primary_uom
1369 , wip_id => l_wip_batch_id
1370 , user_id => l_user_id
1371 , sysd => sysdate
1372 , organization_id => p_organization_id
1373 , approval => to_number(nvl(FND_PROFILE.VALUE('INV_MINMAX_REORDER_APPROVED'),'1'))
1374 , build_in_wip => l_item_attr_rec1.build_in_wip
1375 , pick_components => l_item_attr_rec1.pick_components
1376 , x_ret_stat => l_return_status
1377 , x_ret_mesg => l_msg_data);
1378 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1379 RAISE FND_API.G_EXC_ERROR;
1380 ELSE
1381 update csp_notifications
1382 set status = 5
1383 where notification_id = onc.notification_id;
1384
1385 IF ((l_total_excess > 0) OR (l_total_repair > 0)) THEN
1386 DELETE FROM csp_notification_Details
1387 WHERE notification_id = onc.notification_id;
1388 END IF;
1389 END IF;
1390 ELSE
1391 l_create_notif := 'Y';
1392 END IF;
1393 ELSE
1394 l_create_notif := 'Y';
1395 END IF;
1396 END IF;
1397 ELSE
1398 l_Create_notif := 'Y';
1399 END IF;
1400 exception
1401 when no_data_found then
1402 l_create_notif := 'Y';
1403 end;
1404 END IF;
1405 IF (l_Create_notif = 'Y') THEN
1406 l_parts_rec.inventory_item_id := onc.inventory_item_id;
1407 l_parts_rec.quantity := onc.quantity;
1408 l_source_type := onc.notification_type;
1409
1410 select DECODE(c.source_type, NULL,
1411 DECODE(p.source_type, NULL, NULL, p.source_organization_id),
1412 c.source_organization_id) src_org,
1413 DECODE(c.source_type, NULL,
1414 DECODE(p.source_type, NULL, NULL, p.source_subinventory),
1415 c.source_subinventory) src_subinv
1416 into l_parts_rec.source_org_id,
1417 l_parts_rec.source_subinv
1418 from mtl_system_items c,
1419 mtl_parameters p
1420 where c.inventory_item_id = l_parts_rec.inventory_item_id
1421 and c.organization_id = p.organization_id
1422 and p.organization_id = p_organization_id;
1423
1424 Create_Notification_Details(
1425 p_source_type => l_source_type
1426 ,p_order_by_dt => l_order_by_date
1427 ,p_notification_id => onc.notification_id
1428 ,p_parts_rec => l_parts_rec);
1429 END IF;
1430 end;
1431 END IF;
1432 end;
1433 END IF;
1434 END LOOP;
1435
1436 END Create_Notifications;
1437
1438 PROCEDURE Build_Range_Sql
1439 ( p_cat_structure_id IN NUMBER
1440 , p_cat_lo IN VARCHAR2
1441 , p_cat_hi IN VARCHAR2
1442 , p_item_lo IN VARCHAR2
1443 , p_item_hi IN VARCHAR2
1444 , p_planner_lo IN VARCHAR2
1445 , p_planner_hi IN VARCHAR2
1446 , p_lot_ctl IN NUMBER
1447 , x_range_sql OUT NOCOPY VARCHAR2
1448 )
1449 IS
1450 l_flexfield_rec FND_FLEX_KEY_API.flexfield_type;
1451 l_structure_rec FND_FLEX_KEY_API.structure_type;
1452 l_segment_rec FND_FLEX_KEY_API.segment_type;
1453 l_segment_tbl FND_FLEX_KEY_API.segment_list;
1454 l_segment_number NUMBER;
1455 l_mstk_segs VARCHAR2(850);
1456 l_mcat_segs VARCHAR2(850);
1457 --l_mcat_w VARCHAR2(2000);
1458 --l_mstk_w VARCHAR2(2000);
1459 l_range_sql VARCHAr2(2000);
1460 lx_range_sql VARCHAR2(4000) := '1=1';
1461 BEGIN
1462
1463 FND_FLEX_KEY_API.set_session_mode('customer_data');
1464
1465 -- retrieve system item concatenated flexfield
1466 l_mstk_segs := '';
1467 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MSTK');
1468 l_structure_rec := FND_FLEX_KEY_API.find_structure(l_flexfield_rec, 101);
1469 FND_FLEX_KEY_API.get_segments
1470 ( flexfield => l_flexfield_rec
1471 , structure => l_structure_rec
1472 , nsegments => l_segment_number
1473 , segments => l_segment_tbl
1474 );
1475 FOR l_idx IN 1..l_segment_number LOOP
1476 l_segment_rec := FND_FLEX_KEY_API.find_segment
1477 ( l_flexfield_rec
1478 , l_structure_rec
1479 , l_segment_tbl(l_idx)
1480 );
1481 l_mstk_segs := l_mstk_segs ||'C.'||l_segment_rec.column_name;
1482 IF l_idx < l_segment_number THEN
1483 l_mstk_segs := l_mstk_segs||'||'||l_structure_rec.segment_separator;
1484 END IF;
1485 END LOOP;
1486
1487 -- retrieve item category concatenated flexfield
1488 l_mcat_segs := '';
1489 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MCAT');
1490 l_structure_rec := FND_FLEX_KEY_API.find_structure
1491 ( l_flexfield_rec
1492 , p_cat_structure_id
1493 );
1494 FND_FLEX_KEY_API.get_segments
1495 ( flexfield => l_flexfield_rec
1496 , structure => l_structure_rec
1497 , nsegments => l_segment_number
1498 , segments => l_segment_tbl
1499 );
1500 FOR l_idx IN 1..l_segment_number LOOP
1501 l_segment_rec := FND_FLEX_KEY_API.find_segment
1502 ( l_flexfield_rec
1503 , l_structure_rec
1504 , l_segment_tbl(l_idx)
1505 );
1506 l_mcat_segs := l_mcat_segs ||'B.'||l_segment_rec.column_name;
1507 IF l_idx < l_segment_number THEN
1508 l_mcat_segs := l_mcat_segs||'||'||''''||
1509 l_structure_rec.segment_separator||''''||'||';
1510 END IF;
1511 END LOOP;
1512
1513 IF p_item_lo IS NOT NULL AND p_item_hi IS NOT NULL THEN
1514 l_range_sql := l_mstk_segs||' BETWEEN '''||p_item_lo||''''||
1515 ' AND '''||p_item_hi||'''';
1516 ELSIF p_item_lo IS NOT NULL AND p_item_hi IS NULL THEN
1517 l_range_sql := l_mstk_segs||' >= '''||p_item_lo||'''';
1518 ELSIF p_item_lo IS NULL AND p_item_hi IS NOT NULL THEN
1519 l_range_sql := l_mstk_segs||' <= '''||p_item_hi||'''';
1520 END IF;
1521
1522 IF (l_range_sql is not null) THEN
1523 lx_range_sql := l_range_sql;
1524 l_range_sql := null;
1525 END IF;
1526
1527 IF p_cat_lo IS NOT NULL AND p_cat_hi IS NOT NULL THEN
1528 l_range_sql := l_mcat_segs||' BETWEEN '''||p_cat_lo||''''||
1529 ' AND '''||p_cat_hi||'''';
1530 ELSIF p_cat_lo IS NOT NULL AND p_cat_hi IS NULL THEN
1531 l_range_Sql := l_mcat_segs||' >= '''||p_cat_lo||'''';
1532 ELSIF p_cat_lo IS NULL AND p_cat_hi IS NOT NULL THEN
1533 l_range_sql := l_mcat_segs||' <= '''||p_cat_hi||'''';
1534 END IF;
1535
1536 IF (l_range_Sql is not null) THEN
1537 lx_range_sql := lx_Range_sql || ' and' || l_range_Sql;
1538 l_range_sql := null;
1539 END IF;
1540
1541 if p_planner_lo is not null and p_planner_hi is not null then
1542 l_RANGE_SQL := 'c.planner_code between ' ||''''||P_planner_LO||'''' ||
1543 ' and '|| ''''||P_planner_HI||'''';
1544 elsif p_planner_lo is not null then
1545 l_RANGE_SQL := 'c.planner_code >= ' ||''''||P_planner_LO||'''';
1546 elsif p_PLANNER_hi is not null then
1547 l_RANGE_SQL := 'c.planner_code <= ' ||''''||P_PLANNER_HI||'''';
1548 end if;
1549
1550 if l_range_sql is not null then
1551 lx_range_sql := lx_range_sql||' and '|| l_range_sql;
1552 l_range_sql := null;
1553 end if;
1554
1555 if P_LOT_CTL = 1 then
1556 l_RANGE_SQL := 'c.lot_control_code = 2';
1557 elsif P_LOT_CTL = 2 then
1558 l_RANGE_SQL := 'c.lot_control_code <> 2';
1559 end if;
1560
1561 if l_range_sql is not null then
1562 lx_range_sql := lx_range_sql||' and '|| l_range_sql;
1563 l_range_sql := null;
1564 end if;
1565
1566 x_range_Sql := lx_range_sql;
1567
1568 END;
1569
1570 PROCEDURE Build_Item_Cat_Select(p_Cat_structure_id IN NUMBER
1571 ,x_item_select OUT NOCOPY VARCHAR2
1572 ,x_cat_Select OUT NOCOPY VARCHAR2
1573 ) IS
1574 l_flexfield_rec FND_FLEX_KEY_API.flexfield_type;
1575 l_structure_rec FND_FLEX_KEY_API.structure_type;
1576 l_segment_rec FND_FLEX_KEY_API.segment_type;
1577 l_segment_tbl FND_FLEX_KEY_API.segment_list;
1578 l_segment_number NUMBER;
1579 l_mstk_segs VARCHAR2(850);
1580 l_mcat_segs VARCHAR2(850);
1581 BEGIN
1582 FND_FLEX_KEY_API.set_session_mode('customer_data');
1583
1584 -- retrieve system item concatenated flexfield
1585 l_mstk_segs := '';
1586 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MSTK');
1587 l_structure_rec := FND_FLEX_KEY_API.find_structure(l_flexfield_rec, 101);
1588 FND_FLEX_KEY_API.get_segments
1589 ( flexfield => l_flexfield_rec
1590 , structure => l_structure_rec
1591 , nsegments => l_segment_number
1592 , segments => l_segment_tbl
1593 );
1594 FOR l_idx IN 1..l_segment_number LOOP
1595 l_segment_rec := FND_FLEX_KEY_API.find_segment
1596 ( l_flexfield_rec
1597 , l_structure_rec
1598 , l_segment_tbl(l_idx)
1599 );
1600 l_mstk_segs := l_mstk_segs ||'C.'||l_segment_rec.column_name;
1601 IF l_idx < l_segment_number THEN
1602 l_mstk_segs := l_mstk_segs||'||'||l_structure_rec.segment_separator;
1603 END IF;
1604 END LOOP;
1605
1606 -- retrieve item category concatenated flexfield
1607 l_mcat_segs := '';
1608 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MCAT');
1609 l_structure_rec := FND_FLEX_KEY_API.find_structure
1610 ( l_flexfield_rec
1611 , p_cat_structure_id
1612 );
1613 FND_FLEX_KEY_API.get_segments
1614 ( flexfield => l_flexfield_rec
1615 , structure => l_structure_rec
1616 , nsegments => l_segment_number
1617 , segments => l_segment_tbl
1618 );
1619 FOR l_idx IN 1..l_segment_number LOOP
1620 l_segment_rec := FND_FLEX_KEY_API.find_segment
1621 ( l_flexfield_rec
1622 , l_structure_rec
1623 , l_segment_tbl(l_idx)
1624 );
1625 l_mcat_segs := l_mcat_segs ||'B.'||l_segment_rec.column_name;
1626 IF l_idx < l_segment_number THEN
1627 l_mcat_segs := l_mcat_segs||'||'||''''||
1628 l_structure_rec.segment_separator||''''||'||';
1629 END IF;
1630 END LOOP;
1631
1632 x_item_select := '('||l_mstk_Segs||')';
1633 x_cat_select := '('||l_mcat_Segs||')';
1634
1635 END;
1636
1637 PROCEDURE re_po( item_id IN NUMBER
1638 , qty IN NUMBER
1639 , nb_time IN DATE
1640 , uom IN VARCHAR2
1641 , accru_acct IN NUMBER
1642 , ipv_acct IN NUMBER
1643 , budget_acct IN NUMBER
1644 , charge_acct IN NUMBER
1645 , purch_flag IN VARCHAR2
1646 , order_flag IN VARCHAR2
1647 , transact_flag IN VARCHAR2
1648 , unit_price IN NUMBER
1649 , user_id IN NUMBER
1650 , sysd IN DATE
1651 , organization_id IN NUMBER
1652 , approval IN NUMBER
1653 , src_type IN NUMBER
1654 , encum_flag IN VARCHAR2
1655 , customer_id IN NUMBER
1656 , employee_id IN NUMBER
1657 , description IN VARCHAR2
1658 , src_org IN NUMBER
1659 , src_subinv IN VARCHAR2
1660 , subinv IN VARCHAR2
1661 , location_id IN NUMBER
1662 , po_org_id IN NUMBER
1663 , p_pur_revision IN NUMBER
1664 , x_ret_stat OUT NOCOPY VARCHAR2
1665 , x_ret_mesg OUT NOCOPY VARCHAR2) IS
1666
1667 item_rev_ctl NUMBER := 0;
1668 item_rev VARCHAR2(4) := '@@@';
1669 profile_val NUMBER;
1670 orgn_id NUMBER := organization_id;
1671
1672 po_exc EXCEPTION;
1673
1674 BEGIN
1675 --
1676 -- Do not create a requisition if any of the following apply:
1677 -- 1. Source type (Inventory/Supplier/Subinventory) is not specified
1678 -- 2. Item is not transactable
1679 -- 3. Source type is Inventory (1) but "Internal Orders Enabled"
1680 -- is not checked
1681 -- 4. Source type is Supplier (2) but "Purchasable" flag unchecked
1682 --
1683 IF (src_type IS NULL)
1684 OR
1685 (transact_flag <> 'Y')
1686 OR
1687 (src_type = 1 AND order_flag <> 'Y')
1688 OR
1689 (src_type = 2 AND purch_flag <> 'Y')
1690 THEN
1691 /* print_debug('Null src type or invalid transact_flag, order_flag or purch_flag'
1692 , 're_po', 9);
1693 */
1694 RAISE po_exc;
1695 END IF;
1696
1697 IF (charge_acct IS NULL)
1698 OR (accru_acct IS NULL)
1699 OR (ipv_acct IS NULL)
1700 OR ((encum_flag <> 'N') AND (budget_acct is NULL))
1701 THEN
1702 --print_debug('Charge/accrual/IPV/budget accts not setup correctly.', 're_po', 9);
1703 RAISE po_exc;
1704 END IF;
1705
1706 IF NVL(customer_id,0) < 0
1707 THEN
1708 --print_debug('Invalid customer ID: ' || to_char(customer_id), 're_po', 9);
1709 RAISE po_exc;
1710 END IF;
1711
1712 --
1713 -- Fix for bug 774532:
1714 -- To get the item revisions, if profile is Yes
1715 -- or if profile is NULL AND item is revision controlled
1716 --
1717
1718 IF (p_pur_revision IS NULL)
1719 THEN
1720 SELECT MAX(revision_qty_control_code)
1721 INTO item_rev_ctl
1722 FROM mtl_system_items msi
1723 WHERE msi.organization_id = orgn_id
1724 AND msi.inventory_item_id = item_id;
1725 END IF ;
1726
1727 --print_debug('Rev ctl: ' || to_char(item_rev_ctl), 're_po', 9);
1728
1729 IF (p_pur_revision = 1
1730 OR ((p_pur_revision IS NULL) AND ( item_rev_ctl = 2)))
1731 THEN
1732 SELECT MAX(revision)
1733 INTO item_rev
1734 FROM mtl_item_revisions mir
1735 WHERE inventory_item_id = item_id
1736 AND organization_id = orgn_id
1737 AND effectivity_date < SYSDATE
1738 AND effectivity_date =
1739 (
1740 SELECT MAX(effectivity_date)
1741 FROM mtl_item_revisions mir1
1742 WHERE mir1.inventory_item_id = mir.inventory_item_id
1743 AND mir1.organization_id = mir.organization_id
1744 AND effectivity_date < SYSDATE
1745 );
1746 --print_debug('Item rev: ' || item_rev, 're_po', 9);
1747 END IF;
1748
1749 IF (src_type <> 3 )
1750 THEN
1751 --print_debug('Inserting into PO_REQUISITIONS_INTERFACE_ALL', 're_po', 9);
1752
1753 INSERT INTO po_requisitions_interface_all(
1754 LAST_UPDATE_DATE,
1755 LAST_UPDATED_BY,
1756 ITEM_DESCRIPTION,
1757 CREATION_DATE,
1758 CREATED_BY,
1759 PREPARER_ID,
1760 INTERFACE_SOURCE_CODE,
1761 REQUISITION_TYPE,
1762 AUTHORIZATION_STATUS,
1763 SOURCE_TYPE_CODE,
1764 SOURCE_ORGANIZATION_ID,
1765 SOURCE_SUBINVENTORY,
1766 DESTINATION_ORGANIZATION_ID,
1767 DESTINATION_SUBINVENTORY,
1768 DELIVER_TO_REQUESTOR_ID,
1769 DESTINATION_TYPE_CODE,
1770 UOM_CODE,
1771 DELIVER_TO_LOCATION_ID,
1772 ITEM_ID,
1773 ITEM_REVISION,
1774 QUANTITY,
1775 NEED_BY_DATE,
1776 GL_DATE,
1777 CHARGE_ACCOUNT_ID,
1778 ACCRUAL_ACCOUNT_ID,
1779 VARIANCE_ACCOUNT_ID,
1780 BUDGET_ACCOUNT_ID,
1781 AUTOSOURCE_FLAG,
1782 ORG_ID)
1783 VALUES (
1784 sysdate,
1785 user_id,
1786 description,
1787 sysdate,
1788 user_id,
1789 employee_id,
1790 'INV',
1791 DECODE(src_type, 1, 'INTERNAL', 'PURCHASE'),
1792 DECODE(APPROVAL,1,'INCOMPLETE',2,'APPROVED'),
1793 DECODE(src_type, 1, 'INVENTORY', 'VENDOR'),
1794 src_org,
1795 src_subinv,
1796 organization_id,
1797 subinv,
1798 employee_id,
1799 'INVENTORY',
1800 uom,
1801 location_id,
1802 item_id,
1803 DECODE(item_rev,'@@@',NULL,item_rev),
1804 qty,
1805 trunc(nb_time),
1806 SYSDATE,
1807 charge_acct,
1808 accru_acct,
1809 ipv_acct,
1810 budget_acct,
1811 'P',
1812 po_org_id);
1813
1814 END IF;
1815
1816 x_ret_stat := FND_API.G_RET_STS_SUCCESS;
1817 x_ret_mesg := '';
1818
1819 EXCEPTION
1820 WHEN OTHERS THEN
1821 --print_debug(sqlcode || ', ' || sqlerrm, 're_po', 1);
1822
1823 SELECT meaning
1824 INTO x_ret_mesg
1825 FROM mfg_lookups
1826 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
1827 AND lookup_code = 1;
1828
1829 x_ret_stat := FND_API.G_RET_STS_ERROR;
1830 END re_po;
1831
1832 PROCEDURE re_wip( item_id IN NUMBER
1833 , qty IN NUMBER
1834 , nb_time IN DATE
1835 , uom IN VARCHAR2
1836 , wip_id IN NUMBER
1837 , user_id IN NUMBER
1838 , sysd IN DATE
1839 , organization_id IN NUMBER
1840 , approval IN NUMBER
1841 , build_in_wip IN VARCHAR2
1842 , pick_components IN VARCHAR2
1843 , x_ret_stat OUT NOCOPY VARCHAR2
1844 , x_ret_mesg OUT NOCOPY VARCHAR2) IS
1845
1846 wip_exc EXCEPTION;
1847
1848 BEGIN
1849 IF build_in_wip <> 'Y' OR pick_components <> 'N' THEN
1850 RAISE wip_exc;
1851 ELSE
1852 -- print_debug('Inserting into WIP_JOB_SCHEDULE_INTERFACE', 're_wip', 9);
1853 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
1854 LAST_UPDATE_DATE,
1855 LAST_UPDATED_BY,
1856 CREATION_DATE,
1857 CREATED_BY,
1858 GROUP_ID,
1859 PROCESS_PHASE,
1860 PROCESS_STATUS,
1861 ORGANIZATION_ID,
1862 LOAD_TYPE,
1863 LAST_UNIT_COMPLETION_DATE,
1864 PRIMARY_ITEM_ID,
1865 START_QUANTITY,
1866 STATUS_TYPE)
1867 VALUES(
1868 sysd,
1869 user_id,
1870 sysd,
1871 user_id,
1872 WIP_ID,
1873 2,
1874 1,
1875 organization_id,
1876 1,
1877 nb_time,
1878 item_id,
1879 qty,
1880 DECODE(approval,1,1,2,3));
1881 END IF;
1882
1883 x_ret_stat := FND_API.G_RET_STS_SUCCESS;
1884 x_ret_mesg := '';
1885
1886 EXCEPTION
1887 WHEN OTHERS THEN
1888 --print_debug(sqlcode || ', ' || sqlerrm, 're_wip', 1);
1889
1890 SELECT meaning
1891 INTO x_ret_mesg
1892 FROM mfg_lookups
1893 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
1894 AND lookup_code = 2;
1895
1896 x_ret_stat := FND_API.G_RET_STS_ERROR;
1897 END re_wip;
1898
1899 PROCEDURE Calculate_Excess(
1900 p_organization_id IN NUMBER
1901 ,p_item_rec IN csp_planner_notifications.item_list_rectype
1902 ,p_called_from IN VARCHAR2 := 'NOTIF'
1903 ,p_notification_id IN NUMBER := null
1904 ,p_order_by_date IN DATE := sysdate
1905 ,x_excess_parts_tbl OUT NOCOPY csp_planner_notifications.excess_parts_tbl
1906 ,x_return_status OUT NOCOPY VARCHAR2
1907 ,x_msg_data OUT NOCOPY VARCHAR2
1908 ,x_msg_count OUT NOCOPY NUMBER) IS
1909 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1910 l_msg_data VARCHAR2(1000);
1911 l_msg_count NUMBER;
1912 l_api_name CONSTANT VARCHAR2(30) := 'calculate_excess';
1913
1914 l_mcat_struct_id NUMBER;
1915 l_category_Set_id NUMBER;
1916 l_item_select VARCHAR2(800);
1917 l_cat_select VARCHAR2(800);
1918 l_item VARCHAR2(800);
1919 l_range_sql VARCHAR2(2000);
1920 l_order_by VARCHAR2(50);
1921 l_user_id NUMBER;
1922 l_employee_id NUMBER;
1923 l_Excess_qty NUMBER;
1924 l_organization_type VARCHAR2(10);
1925 l_condition_type VARCHAR2(10);
1926 idx NUMBER := 1;
1927 l_item_minmax_flag NUMBER;
1928 l_sub_minmax_flag NUMBER;
1929
1930 l_onhand_source NUMBER := 3;
1931 l_qoh NUMBER;
1932 l_rqoh NUMBER;
1933 l_qr NUMBER;
1934 l_qs NUMBER;
1935 l_att NUMBER;
1936 l_atr NUMBER;
1937
1938 CURSOR excess_sources_cur IS
1939 select misl.source_organization_id
1940 from MRP_ITEM_SOURCING_LEVELS_V misl, csp_planning_parameters cpp
1941 where cpp.organization_id = p_organization_id
1942 and misl.organization_id = cpp.organization_id
1943 and misl.assignment_set_id =cpp.usable_assignment_set_id
1944 and inventory_item_id = p_item_rec.inventory_item_id
1945 and SOURCE_TYPE = 1
1946 and sourcing_level = (select min(sourcing_level) from MRP_ITEM_SOURCING_LEVELS_V
1947 where organization_id = p_organization_id
1948 and assignment_set_id = cpp.usable_assignment_set_id
1949 and inventory_item_id = p_item_rec.inventory_item_id
1950 and sourcing_level not in (2,9));
1951
1952 CURSOR u_wrhs_subinv_cur(p_orgn_id NUMBER) IS
1953 SELECT secondary_inventory_name
1954 FROM mtl_secondary_inventories
1955 WHERE organization_id = p_orgn_id
1956 AND availability_type = 1;
1957 /* AND secondary_inventory_name NOT IN
1958 (SELECT secondary_inventory_name
1959 FROM csp_sec_inventories
1960 WHERE condition_type = 'B'
1961 AND organization_id = p_orgn_id);
1962 */
1963
1964 CURSOR d_wrhs_subinv_cur(p_orgn_id NUMBER) IS
1965 SELECT secondary_inventory_name
1966 FROM csp_sec_inventories
1967 WHERE organization_id = p_orgn_id
1968 AND condition_type = 'G';
1969
1970 CURSOR employee_id_cur IS
1971 SELECT employee_id
1972 FROM fnd_user
1973 WHERE user_id = l_user_id;
1974
1975 BEGIN
1976 SAVEPOINT Calculate_Excess_PUB;
1977 --initialize return status
1978 x_return_status := FND_API.G_RET_STS_SUCCESS;
1979
1980 /* Validate cat set and MCAT struct */
1981 IF p_item_rec.category_set_id is not null then
1982 SELECT STRUCTURE_ID
1983 into l_mcat_struct_id
1984 FROM MTL_CATEGORY_SETS
1985 WHERE CATEGORY_SET_ID = p_item_rec.category_set_id;
1986 ELSE
1987 SELECT CSET.CATEGORY_SET_ID, CSET.STRUCTURE_ID
1988 INTO l_category_set_id, l_mcat_struct_id
1989 FROM MTL_CATEGORY_SETS CSET,
1990 MTL_DEFAULT_CATEGORY_SETS DEF
1991 WHERE DEF.CATEGORY_SET_ID = CSET.CATEGORY_SET_ID
1992 AND DEF.FUNCTIONAL_AREA_ID = 1;
1993 END IF;
1994
1995 Build_item_cat_Select(l_mcat_struct_id,
1996 l_item_Select,
1997 l_cat_select);
1998
1999 l_order_by := ' order by 1' ;
2000 l_user_id := nvl(fnd_global.user_id, 0) ;
2001 IF (p_item_rec.employee_id IS NOT NULL) THEN
2002 OPEN employee_id_cur;
2003 FETCH employee_id_cur INTO l_employee_id;
2004 CLOSE employee_id_cur;
2005 ELSE
2006 l_employee_id := p_item_rec.employee_id;
2007 END IF;
2008
2009 Begin
2010 SELECT concatenated_segments
2011 INTO l_item
2012 FROM mtl_system_items_kfv
2013 WHERE inventory_item_id = p_item_rec.inventory_item_id;
2014 Exception
2015 WHEN OTHERS THEN
2016 null;
2017 END;
2018
2019 Build_range_sql(
2020 p_cat_structure_id => l_mcat_struct_id
2021 , p_cat_lo => null
2022 , p_cat_hi => null
2023 , p_item_lo => l_item
2024 , p_item_hi => l_item
2025 , p_planner_lo => null
2026 , p_planner_hi => null
2027 , p_lot_ctl => nvl(p_item_rec.lot_control, 3)
2028 , x_range_sql => l_range_sql);
2029
2030 idx := 1;
2031
2032 FOR esc IN excess_sources_cur LOOP
2033 l_excess_qty := 0;
2034
2035 BEGIN
2036 SELECT organization_type,
2037 condition_type
2038 INTO l_organization_type,
2039 l_condition_type
2040 FROM csp_planning_parameters
2041 WHERE organization_id = esc.source_organization_id
2042 AND secondary_inventory IS NULL;
2043 EXCEPTION
2044 WHEN NO_DATA_FOUND THEN
2045 l_organization_type := 'F';
2046 WHEN OTHERS THEN
2047 l_organization_type := null;
2048 END;
2049 -- Find out if item is min-max planned in source or not.
2050 -- If not, calculate ATT for all source orgn and subinv
2051 -- Else, call Inventory API to calculate max.
2052 BEGIN
2053 SELECT inventory_planning_code
2054 INTO l_item_minmax_flag
2055 FROM mtl_system_items
2056 where organization_id = esc.source_organization_id
2057 and inventory_item_id = p_item_rec.inventory_item_id;
2058 EXCEPTION
2059 when no_data_found then
2060 l_item_minmax_flag := 0;
2061 END;
2062
2063 IF (l_organization_type = 'W') THEN
2064 IF (l_item_minmax_flag <> 2) THEN -- not minmax planned
2065 l_onhand_source := 2; --only nettable subinvs
2066 INV_Quantity_Tree_PUB.Query_Quantities
2067 ( p_api_version_number => 1.0
2068 , p_init_msg_lst => 'F'
2069 , x_return_status => l_return_status
2070 , x_msg_count => l_msg_count
2071 , x_msg_data => l_msg_data
2072 , p_organization_id => esc.source_organization_id
2073 , p_inventory_item_id => p_item_Rec.inventory_item_id
2074 , p_tree_mode => 2
2075 , p_onhand_source => l_onhand_source
2076 , p_is_revision_control => FALSE
2077 , p_is_lot_control => FALSE
2078 , p_is_serial_control => FALSE
2079 , p_lot_expiration_date => sysdate
2080 , p_revision => NULL
2081 , p_lot_number => NULL
2082 , p_subinventory_code => NULL
2083 , p_locator_id => NULL
2084 , x_qoh => l_qoh
2085 , x_rqoh => l_rqoh
2086 , x_qr => l_qr
2087 , x_qs => l_qs
2088 , x_att => l_att
2089 , x_atr => l_atr
2090 );
2091 l_excess_qty := l_att;
2092 ELSE
2093 CSP_MINMAX_PVT.run_min_max_plan (
2094 p_item_select => l_item_select
2095 , p_handle_rep_item => 2
2096 , p_pur_revision => nvl(fnd_profile.value('INV_PURCHASING_BY_REVISION'),2)
2097 , p_cat_select => l_Cat_select
2098 , p_cat_set_id => nvl(p_item_rec.Category_set_id, l_category_set_id)
2099 , p_mcat_struct => l_mcat_struct_id
2100 , p_level => 1 -- run at orgn level
2101 , p_restock => 2 -- no restock
2102 , p_include_nonnet => 2 -- do not include non nettable subinv
2103 , p_include_po => nvl(p_item_rec.include_po, 1)
2104 , p_include_wip => nvl(p_item_rec.include_wip, 1)
2105 , p_include_if => nvl(p_item_rec.include_iface_sup, 1)
2106 , p_net_rsv => nvl(p_item_rec.net_rsv, 1)
2107 , p_net_unrsv => nvl(p_item_rec.net_unrsv, 1)
2108 , p_net_wip => nvl(p_item_rec.net_wip, 1)
2109 , p_org_id => esc.source_organization_id
2110 , p_user_id => l_user_id
2111 , p_employee_id => l_employee_id
2112 , p_subinv => null
2113 , p_dd_loc_id => p_item_rec.dd_loc_id
2114 , p_wip_batch_id => null --l_wip_batch_id
2115 , p_approval => to_number(nvl(FND_PROFILE.VALUE('INV_MINMAX_REORDER_APPROVED'),'1'))
2116 , p_buyer_hi => null --p_buyer_hi
2117 , p_buyer_lo => null --p_buyer_lo
2118 , p_range_buyer => null --l_range_buyer
2119 , p_cust_id => null --l_cust_id
2120 , p_po_org_id => null --l_po_org_id
2121 , p_range_sql => l_range_Sql
2122 , p_sort => 1 --p_sort
2123 , p_selection => 2 -- items above maximum quantity
2124 , p_sysdate => sysdate
2125 , p_s_cutoff => nvl(p_item_rec.s_cutoff, sysdate)
2126 , p_d_cutoff => nvl(p_item_rec.d_cutoff, sysdate)
2127 , p_order_by => l_order_by
2128 , p_encum_flag => null --l_encum_flag
2129 , p_cal_code => null --l_cal_code
2130 , p_exception_set_id => null --l_exception_set_id
2131 , x_return_status => l_Return_status
2132 , x_msg_data => l_msg_data);
2133
2134 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2135 RAISE FND_API.G_EXC_ERROR;
2136 ELSE
2137 BEGIN
2138 SELECT (tot_avail_qty - max_qty) excess_qty
2139 INTO l_excess_qty
2140 FROM INV_MIN_MAX_TEMP
2141 WHERE item_Segments = l_item;
2142
2143 EXCEPTION
2144 WHEN NO_DATA_FOUND THEN
2145 l_Excess_qty := -1;
2146 WHEN OTHERS THEN
2147 l_Excess_qty := -2;
2148 END;
2149
2150 -- cleanup inv_min_max_Temp
2151 DELETE FROM INV_MIN_MAX_TEMP;
2152 END IF;
2153 END IF;
2154 IF (nvl(l_excess_qty, 0) > 0) THEN
2155 x_excess_parts_tbl(idx).quantity := l_excess_qty;
2156 x_excess_parts_tbl(idx).inventory_item_id := p_item_rec.inventory_item_id;
2157 x_excess_parts_tbl(idx).source_org_id := esc.source_organization_id;
2158 x_excess_parts_tbl(idx).source_subinv := null;
2159
2160 IF (p_called_from = 'NOTIF') THEN
2161 Create_Notification_Details(
2162 p_notification_id => p_notification_id,
2163 p_order_by_dt => p_order_by_date,
2164 p_source_type => 'EXCESS',
2165 p_parts_rec => x_excess_parts_tbl(idx));
2166 END If;
2167 idx := idx + 1;
2168 END If;
2169 ELSIF (l_organization_type = 'F') THEN
2170 -- field engineers organization, loop thru all usable subinv and
2171 -- run min max at subinv level for all subinvs if item is minmax planned
2172 -- else calculate ATT for each usable subinv
2173 FOR rsc IN d_wrhs_subinv_cur(esc.source_organization_id) LOOP
2174 BEGIN
2175 SELECT inventory_planning_code
2176 INTO l_sub_minmax_flag
2177 FROM mtl_item_sub_inventories
2178 WHERE organization_id = esc.source_organization_id
2179 AND secondary_inventory = rsc.secondary_inventory_name
2180 AND inventory_item_id = p_item_rec.inventory_item_id;
2181 EXCEPTION
2182 WHEN NO_DATA_FOUND THEN
2183 l_sub_minmax_flag := 0;
2184 END;
2185
2186 l_Excess_qty := 0;
2187 IF (l_item_minmax_flag = 2 AND l_sub_minmax_flag = 2) THEN
2188 -- minmax planned
2189 CSP_MINMAX_PVT.run_min_max_plan (
2190 p_item_select => l_item_select
2191 , p_handle_rep_item => 2
2192 , p_pur_revision => nvl(fnd_profile.value('INV_PURCHASING_BY_REVISION'),2)
2193 , p_cat_select => l_Cat_select
2194 , p_cat_set_id => nvl(p_item_rec.Category_set_id, l_category_set_id)
2195 , p_mcat_struct => l_mcat_struct_id
2196 , p_level => 2 -- run at organization level
2197 , p_restock => 2 -- no restock
2198 , p_include_nonnet => 1 -- include non nettable subinv
2199 , p_include_po => nvl(p_item_rec.include_po, 1)
2200 , p_include_wip => nvl(p_item_rec.include_wip, 1)
2201 , p_include_if => nvl(p_item_rec.include_iface_sup, 1)
2202 , p_net_rsv => nvl(p_item_rec.net_rsv, 1)
2203 , p_net_unrsv => nvl(p_item_rec.net_unrsv, 1)
2204 , p_net_wip => nvl(p_item_rec.net_wip, 1)
2205 , p_org_id => esc.source_organization_id
2206 , p_user_id => l_user_id
2207 , p_employee_id => l_employee_id
2208 , p_subinv => rsc.secondary_inventory_name
2209 , p_dd_loc_id => p_item_rec.dd_loc_id
2210 , p_wip_batch_id => null --l_wip_batch_id
2211 , p_approval => to_number(nvl(FND_PROFILE.VALUE('INV_MINMAX_REORDER_APPROVED'),'1'))
2212 , p_buyer_hi => null --p_buyer_hi
2213 , p_buyer_lo => null --p_buyer_lo
2214 , p_range_buyer => null --l_range_buyer
2215 , p_cust_id => null --l_cust_id
2216 , p_po_org_id => null --l_po_org_id
2217 , p_range_sql => l_range_Sql
2218 , p_sort => 1 --p_sort
2219 , p_selection => 2 -- items above maximum quantity
2220 , p_sysdate => sysdate
2221 , p_s_cutoff => nvl(p_item_rec.s_cutoff, sysdate)
2222 , p_d_cutoff => nvl(p_item_rec.d_cutoff, sysdate)
2223 , p_order_by => l_order_by
2224 , p_encum_flag => null --l_encum_flag
2225 , p_cal_code => null --l_cal_code
2226 , p_exception_set_id => null --l_exception_set_id
2227 , x_return_status => l_Return_status
2228 , x_msg_data => l_msg_data);
2229
2230 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2231 RAISE FND_API.G_EXC_ERROR;
2232 ELSE
2233 BEGIN
2234 SELECT (tot_avail_qty - max_qty) excess_qty
2235 INTO l_excess_qty
2236 FROM INV_MIN_MAX_TEMP
2237 WHERE item_Segments = l_item;
2238 EXCEPTION
2239 WHEN NO_DATA_FOUND THEN
2240 l_Excess_qty := -1;
2241 WHEN OTHERS THEN
2242 l_Excess_qty := -1;
2243 END;
2244 -- cleanup inv_min_max_Temp
2245 DELETE FROM INV_MIN_MAX_TEMP;
2246 END If;
2247 ELSE
2248 INV_Quantity_Tree_PUB.Query_Quantities
2249 ( p_api_version_number => 1.0
2250 , p_init_msg_lst => 'F'
2251 , x_return_status => l_return_status
2252 , x_msg_count => l_msg_count
2253 , x_msg_data => l_msg_data
2254 , p_organization_id => esc.source_organization_id
2255 , p_inventory_item_id => p_item_Rec.inventory_item_id
2256 , p_tree_mode => 2
2257 , p_onhand_source => l_onhand_source
2258 , p_is_revision_control => FALSE
2259 , p_is_lot_control => FALSE
2260 , p_is_serial_control => FALSE
2261 , p_lot_expiration_date => sysdate
2262 , p_revision => NULL
2263 , p_lot_number => NULL
2264 , p_subinventory_code => rsc.secondary_inventory_name
2265 , p_locator_id => NULL
2266 , x_qoh => l_qoh
2267 , x_rqoh => l_rqoh
2268 , x_qr => l_qr
2269 , x_qs => l_qs
2270 , x_att => l_att
2271 , x_atr => l_atr
2272 );
2273 l_excess_qty := l_att;
2274
2275 END IF;
2276
2277 IF (nvl(l_excess_qty, 0) > 0) THEN
2278 -- create output record
2279 x_excess_parts_tbl(idx).quantity := l_Excess_qty;
2280 x_excess_parts_tbl(idx).inventory_item_id := p_item_rec.inventory_item_id;
2281 x_excess_parts_tbl(idx).source_org_id := esc.source_organization_id;
2282 x_excess_parts_tbl(idx).source_subinv := rsc.secondary_inventory_name;
2283 IF (p_called_from = 'NOTIF') THEN
2284 Create_Notification_Details(
2285 p_notification_id => p_notification_id,
2286 p_order_by_dt => p_order_by_date,
2287 p_source_type => 'EXCESS',
2288 p_parts_rec => x_excess_parts_tbl(idx));
2289 END If;
2290 idx := idx + 1;
2291 END IF;
2292 END LOOP;
2293 END IF;
2294 END LOOP;
2295
2296 EXCEPTION
2297 WHEN FND_API.G_EXC_ERROR THEN
2298 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2299 P_API_NAME => L_API_NAME
2300 ,P_PKG_NAME => G_PKG_NAME
2301 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2302 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2303 ,X_MSG_COUNT => X_MSG_COUNT
2304 ,X_MSG_DATA => X_MSG_DATA
2305 ,X_RETURN_STATUS => X_RETURN_STATUS);
2306 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2307 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2308 P_API_NAME => L_API_NAME
2309 ,P_PKG_NAME => G_PKG_NAME
2310 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2311 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2312 ,X_MSG_COUNT => X_MSG_COUNT
2313 ,X_MSG_DATA => X_MSG_DATA
2314 ,X_RETURN_STATUS => X_RETURN_STATUS);
2315 WHEN OTHERS THEN
2316
2317 Rollback to calculate_excess_pub;
2318 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
2319 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
2320 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
2321 FND_MSG_PUB.ADD;
2322 fnd_msg_pub.count_and_get
2323 ( p_count => x_msg_count
2324 , p_data => x_msg_data);
2325 x_return_status := FND_API.G_RET_STS_ERROR;
2326 END;
2327
2328 PROCEDURE Generate_Repair_Recomm(
2329 p_notification_id IN NUMBER
2330 ,p_organization_id IN NUMBER
2331 ,p_inventory_item_id IN NUMBER
2332 ,p_order_by_Date IN DATE
2333 ,p_supercess_item_yn IN VARCHAR2
2334 ) IS
2335
2336 CURSOR repair_sources_cur IS
2337 select misl.source_organization_id
2338 from MRP_ITEM_SOURCING_LEVELS_V misl, csp_planning_parameters cpp
2339 where cpp.organization_id = p_organization_id
2340 and misl.organization_id = cpp.organization_id
2341 and misl.assignment_set_id =cpp.defective_assignment_set_id
2342 and inventory_item_id = p_inventory_item_id
2343 and SOURCE_TYPE = 1
2344 and sourcing_level = (select min(sourcing_level) from MRP_ITEM_SOURCING_LEVELS_V
2345 where organization_id = p_organization_id
2346 and assignment_set_id = cpp.defective_assignment_set_id
2347 and inventory_item_id = p_inventory_item_id
2348 and sourcing_level not in (2,9));
2349
2350 CURSOR repair_suppliers_cur IS
2351 select misl.source_type, misl.source_organization_id
2352 from MRP_ITEM_SOURCING_LEVELS_V misl, csp_planning_parameters cpp
2353 where cpp.organization_id = p_organization_id
2354 and misl.organization_id = cpp.organization_id
2355 and misl.assignment_set_id =cpp.repair_assignment_set_id
2356 and inventory_item_id = p_inventory_item_id
2357 and SOURCE_TYPE in (1,3)
2358 and sourcing_level = (select min(sourcing_level) from MRP_ITEM_SOURCING_LEVELS_V
2359 where organization_id = p_organization_id
2360 and assignment_set_id = cpp.repair_assignment_set_id
2361 and inventory_item_id = p_inventory_item_id
2362 and sourcing_level not in (2,9))
2363 order by misl.rank;
2364
2365 CURSOR d_wrhs_subinv_cur(p_orgn_id NUMBER) IS
2366 SELECT secondary_inventory_name
2367 FROM mtl_secondary_inventories
2368 WHERE organization_id = p_orgn_id
2369 AND secondary_inventory_name NOT IN
2370 (SELECT secondary_inventory_name
2371 FROM csp_sec_inventories
2372 WHERE condition_type = 'G'
2373 AND organization_id = p_orgn_id);
2374
2375 CURSOR u_wrhs_subinv_cur(p_orgn_id NUMBER) IS
2376 SELECT secondary_inventory_name
2377 FROM csp_sec_inventories
2378 WHERE organization_id = p_orgn_id
2379 AND condition_type = 'B';
2380
2381 l_onhand_source NUMBER := 3;
2382 l_return_status VARCHAR2(1);
2383 l_msg_count NUMBER;
2384 l_msg_data VARCHAR2(1000);
2385 l_qoh NUMBER;
2386 l_rqoh NUMBER;
2387 l_qr NUMBER;
2388 l_qs NUMBER;
2389 l_att NUMBER;
2390 l_atr NUMBER;
2391 l_repair_qty NUMBER;
2392 l_organization_type VARCHAR2(10);
2393 l_condition_type VARCHAR2(10);
2394 l_parts_rec csp_planner_notifications.excess_parts_rectype;
2395
2396 l_Serviceable VARCHAR2(30);
2397 l_repair_supplier_id NUMBER;
2398 l_source_type VARCHAR2(30):= 'REPAIR';
2399 BEGIN
2400 l_return_status := FND_API.G_RET_STS_SUCCESS;
2401
2402 -- Find repair supplier for the warehouse
2403 FOR rsc IN repair_suppliers_cur LOOP
2404 IF (rsc.source_type = 1) THEN
2405 BEGIN
2406 select serv_req_enabled_code
2407 into l_Serviceable
2408 from mtl_system_items
2409 where inventory_item_id = p_inventory_item_id
2410 and organization_id = rsc.source_organization_id;
2411 EXCEPTION
2412 when no_data_found then
2413 null;
2414 END;
2415 IF l_serviceable = 'E' THEN
2416 l_repair_supplier_id := rsc.source_organization_id;
2417 l_source_type := 'REPAIR';
2418 exit;
2419 END IF;
2420 ELSE
2421 l_source_type := 'EXTREPAIR';
2422 exit;
2423 END IF;
2424 END LOOP;
2425
2426 FOR rep IN repair_sources_cur LOOP
2427 BEGIN
2428 SELECT organization_type,
2429 nvl(condition_type, 'G')
2430 INTO l_organization_type,
2431 l_condition_type
2432 FROM csp_planning_parameters
2433 WHERE organization_id = rep.source_organization_id
2434 AND secondary_inventory IS NULL;
2435 EXCEPTION
2436 WHEN NO_DATA_FOUND THEN
2437 l_organization_type := 'F';
2438 WHEN OTHERS THEN
2439 l_organization_type := null;
2440 END;
2441
2442 IF (l_organization_type = 'W') THEN
2443 l_repair_qty := 0;
2444 IF (l_condition_type = 'B') THEN
2445 FOR wsc IN d_wrhs_subinv_cur(rep.source_organization_id) LOOP
2446 INV_Quantity_Tree_PUB.Query_Quantities
2447 ( p_api_version_number => 1.0
2448 , p_init_msg_lst => 'F'
2449 , x_return_status => l_return_status
2450 , x_msg_count => l_msg_count
2451 , x_msg_data => l_msg_data
2452 , p_organization_id => rep.source_organization_id
2453 , p_inventory_item_id => p_inventory_item_id
2454 , p_tree_mode => 2
2455 , p_onhand_source => l_onhand_source -- need to check out
2456 , p_is_revision_control => FALSE
2457 , p_is_lot_control => FALSE
2458 , p_is_serial_control => FALSE
2459 , p_lot_expiration_date => sysdate
2460 , p_revision => NULL
2461 , p_lot_number => NULL
2462 , p_subinventory_code => wsc.secondary_inventory_name
2463 , p_locator_id => NULL
2464 , x_qoh => l_qoh
2465 , x_rqoh => l_rqoh
2466 , x_qr => l_qr
2467 , x_qs => l_qs
2468 , x_att => l_att
2469 , x_atr => l_atr
2470 );
2471 IF (l_att > 0) THEN
2472 l_repair_qty := l_repair_qty + l_att;
2473 END IF;
2474 END LOOP;
2475 IF (l_repair_qty > 0) THEN
2476 -- call create notification details
2477 l_parts_rec.inventory_item_id := p_inventory_item_id;
2478 l_parts_Rec.source_org_id := rep.source_organization_id;
2479 l_parts_rec.source_subinv := null;
2480 l_parts_rec.quantity := l_repair_qty;
2481 l_parts_rec.repair_supplier_id := l_repair_supplier_id;
2482
2483 Create_Notification_Details(
2484 p_source_type => l_source_type
2485 ,p_order_by_dt => p_order_by_date
2486 ,p_notification_id => p_notification_id
2487 ,p_parts_rec => l_parts_Rec);
2488 END IF;
2489 ELSE -- if warehouse is usable
2490 FOR wsc IN u_wrhs_subinv_cur(rep.source_organization_id) LOOP
2491 INV_Quantity_Tree_PUB.Query_Quantities
2492 ( p_api_version_number => 1.0
2493 , p_init_msg_lst => 'F'
2494 , x_return_status => l_return_status
2495 , x_msg_count => l_msg_count
2496 , x_msg_data => l_msg_data
2497 , p_organization_id => rep.source_organization_id
2498 , p_inventory_item_id => p_inventory_item_id
2499 , p_tree_mode => 2
2500 , p_onhand_source => l_onhand_source -- need to check out
2501 , p_is_revision_control => FALSE
2502 , p_is_lot_control => FALSE
2503 , p_is_serial_control => FALSE
2504 , p_lot_expiration_date => sysdate
2505 , p_revision => NULL
2506 , p_lot_number => NULL
2507 , p_subinventory_code => wsc.secondary_inventory_name
2508 , p_locator_id => NULL
2509 , x_qoh => l_qoh
2510 , x_rqoh => l_rqoh
2511 , x_qr => l_qr
2512 , x_qs => l_qs
2513 , x_att => l_att
2514 , x_atr => l_atr
2515 );
2516 IF (l_att > 0) THEN
2517 l_repair_qty := l_repair_qty + l_att;
2518 END IF;
2519 END LOOP;
2520 IF (l_repair_qty > 0) THEN
2521 -- call create notification details
2522 l_parts_rec.inventory_item_id := p_inventory_item_id;
2523 l_parts_Rec.source_org_id := rep.source_organization_id;
2524 l_parts_rec.source_subinv := null;
2525 l_parts_rec.quantity := l_repair_qty;
2526 l_parts_rec.repair_supplier_id := l_repair_supplier_id;
2527
2528 Create_Notification_Details(
2529 p_source_type => l_source_type
2530 ,p_order_by_dt => p_order_by_date
2531 ,p_notification_id => p_notification_id
2532 ,p_parts_rec => l_parts_Rec);
2533 END IF;
2534 END IF;
2535 ELSE -- if FE organization
2536 FOR rsc IN u_wrhs_subinv_cur(rep.source_organization_id) LOOP
2537 --calculate ATT for each of the defective subinvs
2538 INV_Quantity_Tree_PUB.Query_Quantities
2539 ( p_api_version_number => 1.0
2540 , p_init_msg_lst => 'F'
2541 , x_return_status => l_return_status
2542 , x_msg_count => l_msg_count
2543 , x_msg_data => l_msg_data
2544 , p_organization_id => rep.source_organization_id
2545 , p_inventory_item_id => p_inventory_item_id
2546 , p_tree_mode => 2
2547 , p_onhand_source => l_onhand_source -- need to check out
2548 , p_is_revision_control => FALSE
2549 , p_is_lot_control => FALSE
2550 , p_is_serial_control => FALSE
2551 , p_lot_expiration_date => sysdate
2552 , p_revision => NULL
2553 , p_lot_number => NULL
2554 , p_subinventory_code => rsc.secondary_inventory_name
2555 , p_locator_id => NULL
2556 , x_qoh => l_qoh
2557 , x_rqoh => l_rqoh
2558 , x_qr => l_qr
2559 , x_qs => l_qs
2560 , x_att => l_att
2561 , x_atr => l_atr
2562 );
2563 IF (l_Att > 0) THEN
2564 -- call create notification details
2565 l_parts_rec.inventory_item_id := p_inventory_item_id;
2566 l_parts_Rec.source_org_id := rep.source_organization_id;
2567 l_parts_rec.source_subinv := rsc.secondary_inventory_name;
2568 l_parts_rec.quantity := l_att;
2569 l_parts_rec.repair_supplier_id := l_repair_supplier_id;
2570
2571 Create_Notification_Details(
2572 p_source_type => l_source_type
2573 ,p_order_by_dt => p_order_by_date
2574 ,p_notification_id => p_notification_id
2575 ,p_parts_rec => l_parts_Rec);
2576 END IF;
2577 END LOOP;
2578 END IF;
2579 END LOOP;
2580 -- elsif supercessed_item,
2581 IF (p_supercess_item_yn = 'Y') THEN
2582 -- calculate repair quantity in current organization.
2583 INV_Quantity_Tree_PUB.Query_Quantities
2584 ( p_api_version_number => 1.0
2585 , p_init_msg_lst => 'F'
2586 , x_return_status => l_return_status
2587 , x_msg_count => l_msg_count
2588 , x_msg_data => l_msg_data
2589 , p_organization_id => p_organization_id
2590 , p_inventory_item_id => p_inventory_item_id
2591 , p_tree_mode => 2
2592 , p_onhand_source => l_onhand_source -- need to check out
2593 , p_is_revision_control => FALSE
2594 , p_is_lot_control => FALSE
2595 , p_is_serial_control => FALSE
2596 , p_lot_expiration_date => sysdate
2597 , p_revision => NULL
2598 , p_lot_number => NULL
2599 , p_subinventory_code => NULL
2600 , p_locator_id => NULL
2601 , x_qoh => l_qoh
2602 , x_rqoh => l_rqoh
2603 , x_qr => l_qr
2604 , x_qs => l_qs
2605 , x_att => l_att
2606 , x_atr => l_atr
2607 );
2608 IF (l_att > 0) THEN
2609 -- call create notification details
2610 l_parts_rec.inventory_item_id := p_inventory_item_id;
2611 l_parts_Rec.source_org_id := p_organization_id;
2612 l_parts_rec.source_subinv := NULL; --rep.source_subinventory;
2613 l_parts_rec.quantity := l_att;
2614 l_parts_rec.repair_supplier_id := l_repair_supplier_id;
2615
2616 Create_Notification_Details(
2617 p_source_type => l_source_type
2618 ,p_order_by_Dt => p_order_by_date
2619 ,p_notification_id => p_notification_id
2620 ,p_parts_rec => l_parts_Rec);
2621 END IF;
2622 END IF;
2623
2624 END;
2625
2626 PROCEDURE Cleanup_Notifications(p_organization_id NUMBER) IS
2627 BEGIN
2628 DELETE FROM csp_notification_Details
2629 WHERE notification_id in
2630 (SELECT notification_id
2631 FROM csp_notifications
2632 WHERE trunc(nvl(suppress_end_date, sysdate)) <= trunc(sysdate)
2633 AND organization_id = p_organization_id);
2634
2635 DELETE FROM csp_notifications
2636 WHERE trunc(nvl(suppress_end_date, sysdate)) <= trunc(sysdate)
2637 AND organization_id = p_organization_id;
2638 END;
2639
2640 PROCEDURE Create_Notification_Details(
2641 p_source_type IN VARCHAR2
2642 ,p_order_by_dt IN DATE := sysdate
2643 ,p_notification_id IN NUMBER
2644 ,p_parts_rec IN csp_planner_notifications.excess_parts_rectype) IS
2645 l_notif_detail_id NUMBER;
2646 BEGIN
2647
2648 l_notif_detail_id := NULL;
2649 CSP_Notification_Details_PKG.Insert_Row(
2650 px_NOTIFICATION_DETAIL_ID => l_notif_detail_id
2651 ,p_NOTIFICATION_ID => p_notification_id
2652 ,p_INVENTORY_ITEM_ID => p_parts_rec.inventory_item_id
2653 ,p_AVAILABLE_QUANTITY => p_parts_rec.quantity
2654 ,p_ORDER_BY_DATE => nvl(p_order_by_dt,sysdate)
2655 ,p_SOURCE_TYPE => p_source_type
2656 ,p_SOURCE_ORGANIZATION_ID => p_parts_rec.source_org_id
2657 ,p_SOURCE_SUBINVENTORY => p_parts_rec.source_subinv
2658 ,p_CREATED_BY => nvl(fnd_global.user_id, 0)
2659 ,p_CREATION_DATE => sysdate
2660 ,p_LAST_UPDATED_BY => nvl(fnd_global.user_id, 0)
2661 ,p_LAST_UPDATE_DATE => sysdate
2662 ,p_LAST_UPDATE_LOGIN => nvl(fnd_global.login_id, -1)
2663 ,p_ATTRIBUTE_CATEGORY => null
2664 ,p_ATTRIBUTE1 => null
2665 ,p_ATTRIBUTE2 => null
2666 ,p_ATTRIBUTE3 => null
2667 ,p_ATTRIBUTE4 => null
2668 ,p_ATTRIBUTE5 => null
2669 ,p_ATTRIBUTE6 => null
2670 ,p_ATTRIBUTE7 => null
2671 ,p_ATTRIBUTE8 => null
2672 ,p_ATTRIBUTE9 => null
2673 ,p_ATTRIBUTE10 => null
2674 ,p_ATTRIBUTE11 => null
2675 ,p_ATTRIBUTE12 => null
2676 ,p_ATTRIBUTE13 => null
2677 ,p_ATTRIBUTE14 => null
2678 ,p_ATTRIBUTE15 => null
2679 ,p_REPAIR_SUPPLIER_ID => p_parts_rec.repair_supplier_id
2680 ,p_ORDER_NUMBER => NULL
2681 );
2682 END;
2683 END;