[Home] [Help]
PACKAGE BODY: APPS.CSP_PLANNER_NOTIFICATIONS
Source
1 PACKAGE BODY CSP_PLANNER_NOTIFICATIONS AS
2 /* $Header: cspvppnb.pls 120.2 2011/08/01 23:56:31 hhaugeru ship $ */
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 min(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||'||'||''''||
1484 l_structure_rec.segment_separator||''''||'||';
1485 END IF;
1486 END LOOP;
1487
1488 -- retrieve item category concatenated flexfield
1489 l_mcat_segs := '';
1490 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MCAT');
1491 l_structure_rec := FND_FLEX_KEY_API.find_structure
1492 ( l_flexfield_rec
1493 , p_cat_structure_id
1494 );
1495 FND_FLEX_KEY_API.get_segments
1496 ( flexfield => l_flexfield_rec
1497 , structure => l_structure_rec
1498 , nsegments => l_segment_number
1499 , segments => l_segment_tbl
1500 );
1501 FOR l_idx IN 1..l_segment_number LOOP
1502 l_segment_rec := FND_FLEX_KEY_API.find_segment
1503 ( l_flexfield_rec
1504 , l_structure_rec
1505 , l_segment_tbl(l_idx)
1506 );
1507 l_mcat_segs := l_mcat_segs ||'B.'||l_segment_rec.column_name;
1508 IF l_idx < l_segment_number THEN
1509 l_mcat_segs := l_mcat_segs||'||'||''''||
1510 l_structure_rec.segment_separator||''''||'||';
1511 END IF;
1512 END LOOP;
1513
1514 IF p_item_lo IS NOT NULL AND p_item_hi IS NOT NULL THEN
1515 l_range_sql := l_mstk_segs||' BETWEEN '''||p_item_lo||''''||
1516 ' AND '''||p_item_hi||'''';
1517 ELSIF p_item_lo IS NOT NULL AND p_item_hi IS NULL THEN
1518 l_range_sql := l_mstk_segs||' >= '''||p_item_lo||'''';
1519 ELSIF p_item_lo IS NULL AND p_item_hi IS NOT NULL THEN
1520 l_range_sql := l_mstk_segs||' <= '''||p_item_hi||'''';
1521 END IF;
1522
1523 IF (l_range_sql is not null) THEN
1524 lx_range_sql := l_range_sql;
1525 l_range_sql := null;
1526 END IF;
1527
1528 IF p_cat_lo IS NOT NULL AND p_cat_hi IS NOT NULL THEN
1529 l_range_sql := l_mcat_segs||' BETWEEN '''||p_cat_lo||''''||
1530 ' AND '''||p_cat_hi||'''';
1531 ELSIF p_cat_lo IS NOT NULL AND p_cat_hi IS NULL THEN
1532 l_range_Sql := l_mcat_segs||' >= '''||p_cat_lo||'''';
1533 ELSIF p_cat_lo IS NULL AND p_cat_hi IS NOT NULL THEN
1534 l_range_sql := l_mcat_segs||' <= '''||p_cat_hi||'''';
1535 END IF;
1536
1537 IF (l_range_Sql is not null) THEN
1538 lx_range_sql := lx_Range_sql || ' and' || l_range_Sql;
1539 l_range_sql := null;
1540 END IF;
1541
1542 if p_planner_lo is not null and p_planner_hi is not null then
1543 l_RANGE_SQL := 'c.planner_code between ' ||''''||P_planner_LO||'''' ||
1544 ' and '|| ''''||P_planner_HI||'''';
1545 elsif p_planner_lo is not null then
1546 l_RANGE_SQL := 'c.planner_code >= ' ||''''||P_planner_LO||'''';
1547 elsif p_PLANNER_hi is not null then
1548 l_RANGE_SQL := 'c.planner_code <= ' ||''''||P_PLANNER_HI||'''';
1549 end if;
1550
1551 if l_range_sql is not null then
1552 lx_range_sql := lx_range_sql||' and '|| l_range_sql;
1553 l_range_sql := null;
1554 end if;
1555
1556 if P_LOT_CTL = 1 then
1557 l_RANGE_SQL := 'c.lot_control_code = 2';
1558 elsif P_LOT_CTL = 2 then
1559 l_RANGE_SQL := 'c.lot_control_code <> 2';
1560 end if;
1561
1562 if l_range_sql is not null then
1563 lx_range_sql := lx_range_sql||' and '|| l_range_sql;
1564 l_range_sql := null;
1565 end if;
1566
1567 x_range_Sql := lx_range_sql;
1568
1569 END;
1570
1571 PROCEDURE Build_Item_Cat_Select(p_Cat_structure_id IN NUMBER
1572 ,x_item_select OUT NOCOPY VARCHAR2
1573 ,x_cat_Select OUT NOCOPY VARCHAR2
1574 ) IS
1575 l_flexfield_rec FND_FLEX_KEY_API.flexfield_type;
1576 l_structure_rec FND_FLEX_KEY_API.structure_type;
1577 l_segment_rec FND_FLEX_KEY_API.segment_type;
1578 l_segment_tbl FND_FLEX_KEY_API.segment_list;
1579 l_segment_number NUMBER;
1580 l_mstk_segs VARCHAR2(850);
1581 l_mcat_segs VARCHAR2(850);
1582 BEGIN
1583 FND_FLEX_KEY_API.set_session_mode('customer_data');
1584
1585 -- retrieve system item concatenated flexfield
1586 l_mstk_segs := '';
1587 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MSTK');
1588 l_structure_rec := FND_FLEX_KEY_API.find_structure(l_flexfield_rec, 101);
1589 FND_FLEX_KEY_API.get_segments
1590 ( flexfield => l_flexfield_rec
1591 , structure => l_structure_rec
1592 , nsegments => l_segment_number
1593 , segments => l_segment_tbl
1594 );
1595 FOR l_idx IN 1..l_segment_number LOOP
1596 l_segment_rec := FND_FLEX_KEY_API.find_segment
1597 ( l_flexfield_rec
1598 , l_structure_rec
1599 , l_segment_tbl(l_idx)
1600 );
1601 l_mstk_segs := l_mstk_segs ||'C.'||l_segment_rec.column_name;
1602 IF l_idx < l_segment_number THEN
1603 l_mstk_segs := l_mstk_segs||'||'||''''||
1604 l_structure_rec.segment_separator||''''||'||';
1605
1606 END IF;
1607 END LOOP;
1608
1609 -- retrieve item category concatenated flexfield
1610 l_mcat_segs := '';
1611 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MCAT');
1612 l_structure_rec := FND_FLEX_KEY_API.find_structure
1613 ( l_flexfield_rec
1614 , p_cat_structure_id
1615 );
1616 FND_FLEX_KEY_API.get_segments
1617 ( flexfield => l_flexfield_rec
1618 , structure => l_structure_rec
1619 , nsegments => l_segment_number
1620 , segments => l_segment_tbl
1621 );
1622 FOR l_idx IN 1..l_segment_number LOOP
1623 l_segment_rec := FND_FLEX_KEY_API.find_segment
1624 ( l_flexfield_rec
1625 , l_structure_rec
1626 , l_segment_tbl(l_idx)
1627 );
1628 l_mcat_segs := l_mcat_segs ||'B.'||l_segment_rec.column_name;
1629 IF l_idx < l_segment_number THEN
1630 l_mcat_segs := l_mcat_segs||'||'||''''||
1631 l_structure_rec.segment_separator||''''||'||';
1632 END IF;
1633 END LOOP;
1634
1635 x_item_select := '('||l_mstk_Segs||')';
1636 x_cat_select := '('||l_mcat_Segs||')';
1637
1638 END;
1639
1640 PROCEDURE re_po( item_id IN NUMBER
1641 , qty IN NUMBER
1642 , nb_time IN DATE
1643 , uom IN VARCHAR2
1644 , accru_acct IN NUMBER
1645 , ipv_acct IN NUMBER
1646 , budget_acct IN NUMBER
1647 , charge_acct IN NUMBER
1648 , purch_flag IN VARCHAR2
1649 , order_flag IN VARCHAR2
1650 , transact_flag IN VARCHAR2
1651 , unit_price IN NUMBER
1652 , user_id IN NUMBER
1653 , sysd IN DATE
1654 , organization_id IN NUMBER
1655 , approval IN NUMBER
1656 , src_type IN NUMBER
1657 , encum_flag IN VARCHAR2
1658 , customer_id IN NUMBER
1659 , employee_id IN NUMBER
1660 , description IN VARCHAR2
1661 , src_org IN NUMBER
1662 , src_subinv IN VARCHAR2
1663 , subinv IN VARCHAR2
1664 , location_id IN NUMBER
1665 , po_org_id IN NUMBER
1666 , p_pur_revision IN NUMBER
1667 , x_ret_stat OUT NOCOPY VARCHAR2
1668 , x_ret_mesg OUT NOCOPY VARCHAR2) IS
1669
1670 item_rev_ctl NUMBER := 0;
1671 item_rev VARCHAR2(4) := '@@@';
1672 profile_val NUMBER;
1673 orgn_id NUMBER := organization_id;
1674
1675 po_exc EXCEPTION;
1676
1677 BEGIN
1678 --
1679 -- Do not create a requisition if any of the following apply:
1680 -- 1. Source type (Inventory/Supplier/Subinventory) is not specified
1681 -- 2. Item is not transactable
1682 -- 3. Source type is Inventory (1) but "Internal Orders Enabled"
1683 -- is not checked
1684 -- 4. Source type is Supplier (2) but "Purchasable" flag unchecked
1685 --
1686 IF (src_type IS NULL)
1687 OR
1688 (transact_flag <> 'Y')
1689 OR
1690 (src_type = 1 AND order_flag <> 'Y')
1691 OR
1692 (src_type = 2 AND purch_flag <> 'Y')
1693 THEN
1694 /* print_debug('Null src type or invalid transact_flag, order_flag or purch_flag'
1695 , 're_po', 9);
1696 */
1697 RAISE po_exc;
1698 END IF;
1699
1700 IF (charge_acct IS NULL)
1701 OR (accru_acct IS NULL)
1702 OR (ipv_acct IS NULL)
1703 OR ((encum_flag <> 'N') AND (budget_acct is NULL))
1704 THEN
1705 --print_debug('Charge/accrual/IPV/budget accts not setup correctly.', 're_po', 9);
1706 RAISE po_exc;
1707 END IF;
1708
1709 IF NVL(customer_id,0) < 0
1710 THEN
1711 --print_debug('Invalid customer ID: ' || to_char(customer_id), 're_po', 9);
1712 RAISE po_exc;
1713 END IF;
1714
1715 --
1716 -- Fix for bug 774532:
1717 -- To get the item revisions, if profile is Yes
1718 -- or if profile is NULL AND item is revision controlled
1719 --
1720
1721 IF (p_pur_revision IS NULL)
1722 THEN
1723 SELECT MAX(revision_qty_control_code)
1724 INTO item_rev_ctl
1725 FROM mtl_system_items msi
1726 WHERE msi.organization_id = orgn_id
1727 AND msi.inventory_item_id = item_id;
1728 END IF ;
1729
1730 --print_debug('Rev ctl: ' || to_char(item_rev_ctl), 're_po', 9);
1731
1732 IF (p_pur_revision = 1
1733 OR ((p_pur_revision IS NULL) AND ( item_rev_ctl = 2)))
1734 THEN
1735 SELECT MAX(revision)
1736 INTO item_rev
1737 FROM mtl_item_revisions mir
1738 WHERE inventory_item_id = item_id
1739 AND organization_id = orgn_id
1740 AND effectivity_date < SYSDATE
1741 AND effectivity_date =
1742 (
1743 SELECT MAX(effectivity_date)
1744 FROM mtl_item_revisions mir1
1745 WHERE mir1.inventory_item_id = mir.inventory_item_id
1746 AND mir1.organization_id = mir.organization_id
1747 AND effectivity_date < SYSDATE
1748 );
1749 --print_debug('Item rev: ' || item_rev, 're_po', 9);
1750 END IF;
1751
1752 IF (src_type <> 3 )
1753 THEN
1754 --print_debug('Inserting into PO_REQUISITIONS_INTERFACE_ALL', 're_po', 9);
1755
1756 INSERT INTO po_requisitions_interface_all(
1757 LAST_UPDATE_DATE,
1758 LAST_UPDATED_BY,
1759 ITEM_DESCRIPTION,
1760 CREATION_DATE,
1761 CREATED_BY,
1762 PREPARER_ID,
1763 INTERFACE_SOURCE_CODE,
1764 REQUISITION_TYPE,
1765 AUTHORIZATION_STATUS,
1766 SOURCE_TYPE_CODE,
1767 SOURCE_ORGANIZATION_ID,
1768 SOURCE_SUBINVENTORY,
1769 DESTINATION_ORGANIZATION_ID,
1770 DESTINATION_SUBINVENTORY,
1771 DELIVER_TO_REQUESTOR_ID,
1772 DESTINATION_TYPE_CODE,
1773 UOM_CODE,
1774 DELIVER_TO_LOCATION_ID,
1775 ITEM_ID,
1776 ITEM_REVISION,
1777 QUANTITY,
1778 NEED_BY_DATE,
1779 GL_DATE,
1780 CHARGE_ACCOUNT_ID,
1781 ACCRUAL_ACCOUNT_ID,
1782 VARIANCE_ACCOUNT_ID,
1783 BUDGET_ACCOUNT_ID,
1784 AUTOSOURCE_FLAG,
1785 ORG_ID)
1786 VALUES (
1787 sysdate,
1788 user_id,
1789 description,
1790 sysdate,
1791 user_id,
1792 employee_id,
1793 'INV',
1794 DECODE(src_type, 1, 'INTERNAL', 'PURCHASE'),
1795 DECODE(APPROVAL,1,'INCOMPLETE',2,'APPROVED'),
1796 DECODE(src_type, 1, 'INVENTORY', 'VENDOR'),
1797 src_org,
1798 src_subinv,
1799 organization_id,
1800 subinv,
1801 employee_id,
1802 'INVENTORY',
1803 uom,
1804 location_id,
1805 item_id,
1806 DECODE(item_rev,'@@@',NULL,item_rev),
1807 qty,
1808 trunc(nb_time),
1809 SYSDATE,
1810 charge_acct,
1811 accru_acct,
1812 ipv_acct,
1813 budget_acct,
1814 'P',
1815 po_org_id);
1816
1817 END IF;
1818
1819 x_ret_stat := FND_API.G_RET_STS_SUCCESS;
1820 x_ret_mesg := '';
1821
1822 EXCEPTION
1823 WHEN OTHERS THEN
1824 --print_debug(sqlcode || ', ' || sqlerrm, 're_po', 1);
1825
1826 SELECT meaning
1827 INTO x_ret_mesg
1828 FROM mfg_lookups
1829 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
1830 AND lookup_code = 1;
1831
1832 x_ret_stat := FND_API.G_RET_STS_ERROR;
1833 END re_po;
1834
1835 PROCEDURE re_wip( item_id IN NUMBER
1836 , qty IN NUMBER
1837 , nb_time IN DATE
1838 , uom IN VARCHAR2
1839 , wip_id IN NUMBER
1840 , user_id IN NUMBER
1841 , sysd IN DATE
1842 , organization_id IN NUMBER
1843 , approval IN NUMBER
1844 , build_in_wip IN VARCHAR2
1845 , pick_components IN VARCHAR2
1846 , x_ret_stat OUT NOCOPY VARCHAR2
1847 , x_ret_mesg OUT NOCOPY VARCHAR2) IS
1848
1849 wip_exc EXCEPTION;
1850
1851 BEGIN
1852 IF build_in_wip <> 'Y' OR pick_components <> 'N' THEN
1853 RAISE wip_exc;
1854 ELSE
1855 -- print_debug('Inserting into WIP_JOB_SCHEDULE_INTERFACE', 're_wip', 9);
1856 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
1857 LAST_UPDATE_DATE,
1858 LAST_UPDATED_BY,
1859 CREATION_DATE,
1860 CREATED_BY,
1861 GROUP_ID,
1862 PROCESS_PHASE,
1863 PROCESS_STATUS,
1864 ORGANIZATION_ID,
1865 LOAD_TYPE,
1866 LAST_UNIT_COMPLETION_DATE,
1867 PRIMARY_ITEM_ID,
1868 START_QUANTITY,
1869 STATUS_TYPE)
1870 VALUES(
1871 sysd,
1872 user_id,
1873 sysd,
1874 user_id,
1875 WIP_ID,
1876 2,
1877 1,
1878 organization_id,
1879 1,
1880 nb_time,
1881 item_id,
1882 qty,
1883 DECODE(approval,1,1,2,3));
1884 END IF;
1885
1886 x_ret_stat := FND_API.G_RET_STS_SUCCESS;
1887 x_ret_mesg := '';
1888
1889 EXCEPTION
1890 WHEN OTHERS THEN
1891 --print_debug(sqlcode || ', ' || sqlerrm, 're_wip', 1);
1892
1893 SELECT meaning
1894 INTO x_ret_mesg
1895 FROM mfg_lookups
1896 WHERE lookup_type = 'INV_MMX_RPT_MSGS'
1897 AND lookup_code = 2;
1898
1899 x_ret_stat := FND_API.G_RET_STS_ERROR;
1900 END re_wip;
1901
1902 PROCEDURE Calculate_Excess(
1903 p_organization_id IN NUMBER
1904 ,p_item_rec IN csp_planner_notifications.item_list_rectype
1905 ,p_called_from IN VARCHAR2 := 'NOTIF'
1906 ,p_notification_id IN NUMBER := null
1907 ,p_order_by_date IN DATE := sysdate
1908 ,x_excess_parts_tbl OUT NOCOPY csp_planner_notifications.excess_parts_tbl
1909 ,x_return_status OUT NOCOPY VARCHAR2
1910 ,x_msg_data OUT NOCOPY VARCHAR2
1911 ,x_msg_count OUT NOCOPY NUMBER) IS
1912 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1913 l_msg_data VARCHAR2(1000);
1914 l_msg_count NUMBER;
1915 l_api_name CONSTANT VARCHAR2(30) := 'calculate_excess';
1916
1917 l_mcat_struct_id NUMBER;
1918 l_category_Set_id NUMBER;
1919 l_item_select VARCHAR2(800);
1920 l_cat_select VARCHAR2(800);
1921 l_item VARCHAR2(800);
1922 l_range_sql VARCHAR2(2000);
1923 l_order_by VARCHAR2(50);
1924 l_user_id NUMBER;
1925 l_employee_id NUMBER;
1926 l_Excess_qty NUMBER;
1927 l_organization_type VARCHAR2(10);
1928 l_condition_type VARCHAR2(10);
1929 idx NUMBER := 1;
1930 l_item_minmax_flag NUMBER;
1931 l_sub_minmax_flag NUMBER;
1932
1933 l_onhand_source NUMBER := 3;
1934 l_qoh NUMBER;
1935 l_rqoh NUMBER;
1936 l_qr NUMBER;
1937 l_qs NUMBER;
1938 l_att NUMBER;
1939 l_atr NUMBER;
1940
1941 CURSOR excess_sources_cur IS
1942 select misl.source_organization_id
1943 from MRP_ITEM_SOURCING_LEVELS_V misl, csp_planning_parameters cpp
1944 where cpp.organization_id = p_organization_id
1945 and misl.organization_id = cpp.organization_id
1946 and misl.assignment_set_id =cpp.usable_assignment_set_id
1947 and inventory_item_id = p_item_rec.inventory_item_id
1948 and SOURCE_TYPE = 1
1949 and sourcing_level = (select min(sourcing_level) from MRP_ITEM_SOURCING_LEVELS_V
1950 where organization_id = p_organization_id
1951 and assignment_set_id = cpp.usable_assignment_set_id
1952 and inventory_item_id = p_item_rec.inventory_item_id
1953 and sourcing_level not in (2,9));
1954
1955 CURSOR u_wrhs_subinv_cur(p_orgn_id NUMBER) IS
1956 SELECT secondary_inventory_name
1957 FROM mtl_secondary_inventories
1958 WHERE organization_id = p_orgn_id
1959 AND availability_type = 1;
1960 /* AND secondary_inventory_name NOT IN
1961 (SELECT secondary_inventory_name
1962 FROM csp_sec_inventories
1963 WHERE condition_type = 'B'
1964 AND organization_id = p_orgn_id);
1965 */
1966
1967 CURSOR d_wrhs_subinv_cur(p_orgn_id NUMBER) IS
1968 SELECT secondary_inventory_name
1969 FROM csp_sec_inventories
1970 WHERE organization_id = p_orgn_id
1971 AND condition_type = 'G';
1972
1973 CURSOR employee_id_cur IS
1974 SELECT employee_id
1975 FROM fnd_user
1976 WHERE user_id = l_user_id;
1977
1978 BEGIN
1979 SAVEPOINT Calculate_Excess_PUB;
1980 --initialize return status
1981 x_return_status := FND_API.G_RET_STS_SUCCESS;
1982
1983 /* Validate cat set and MCAT struct */
1984 IF p_item_rec.category_set_id is not null then
1985 SELECT STRUCTURE_ID
1986 into l_mcat_struct_id
1987 FROM MTL_CATEGORY_SETS
1988 WHERE CATEGORY_SET_ID = p_item_rec.category_set_id;
1989 ELSE
1990 SELECT CSET.CATEGORY_SET_ID, CSET.STRUCTURE_ID
1991 INTO l_category_set_id, l_mcat_struct_id
1992 FROM MTL_CATEGORY_SETS CSET,
1993 MTL_DEFAULT_CATEGORY_SETS DEF
1994 WHERE DEF.CATEGORY_SET_ID = CSET.CATEGORY_SET_ID
1995 AND DEF.FUNCTIONAL_AREA_ID = 1;
1996 END IF;
1997
1998 Build_item_cat_Select(l_mcat_struct_id,
1999 l_item_Select,
2000 l_cat_select);
2001
2002 l_order_by := ' order by 1' ;
2003 l_user_id := nvl(fnd_global.user_id, 0) ;
2004 IF (p_item_rec.employee_id IS NOT NULL) THEN
2005 OPEN employee_id_cur;
2006 FETCH employee_id_cur INTO l_employee_id;
2007 CLOSE employee_id_cur;
2008 ELSE
2009 l_employee_id := p_item_rec.employee_id;
2010 END IF;
2011
2012 Begin
2013 SELECT concatenated_segments
2014 INTO l_item
2015 FROM mtl_system_items_kfv
2016 WHERE inventory_item_id = p_item_rec.inventory_item_id;
2017 Exception
2018 WHEN OTHERS THEN
2019 null;
2020 END;
2021
2022 Build_range_sql(
2023 p_cat_structure_id => l_mcat_struct_id
2024 , p_cat_lo => null
2025 , p_cat_hi => null
2026 , p_item_lo => l_item
2027 , p_item_hi => l_item
2028 , p_planner_lo => null
2029 , p_planner_hi => null
2030 , p_lot_ctl => nvl(p_item_rec.lot_control, 3)
2031 , x_range_sql => l_range_sql);
2032
2033 idx := 1;
2034
2035 FOR esc IN excess_sources_cur LOOP
2036 l_excess_qty := 0;
2037
2038 BEGIN
2039 SELECT organization_type,
2040 condition_type
2041 INTO l_organization_type,
2042 l_condition_type
2043 FROM csp_planning_parameters
2044 WHERE organization_id = esc.source_organization_id
2045 AND secondary_inventory IS NULL;
2046 EXCEPTION
2047 WHEN NO_DATA_FOUND THEN
2048 l_organization_type := 'F';
2049 WHEN OTHERS THEN
2050 l_organization_type := null;
2051 END;
2052 -- Find out if item is min-max planned in source or not.
2053 -- If not, calculate ATT for all source orgn and subinv
2054 -- Else, call Inventory API to calculate max.
2055 BEGIN
2056 SELECT inventory_planning_code
2057 INTO l_item_minmax_flag
2058 FROM mtl_system_items
2059 where organization_id = esc.source_organization_id
2060 and inventory_item_id = p_item_rec.inventory_item_id;
2061 EXCEPTION
2062 when no_data_found then
2063 l_item_minmax_flag := 0;
2064 END;
2065
2066 IF (l_organization_type = 'W') THEN
2067 IF (l_item_minmax_flag <> 2) THEN -- not minmax planned
2068 l_onhand_source := 2; --only nettable subinvs
2069 INV_Quantity_Tree_PUB.Query_Quantities
2070 ( p_api_version_number => 1.0
2071 , p_init_msg_lst => 'F'
2072 , x_return_status => l_return_status
2073 , x_msg_count => l_msg_count
2074 , x_msg_data => l_msg_data
2075 , p_organization_id => esc.source_organization_id
2076 , p_inventory_item_id => p_item_Rec.inventory_item_id
2077 , p_tree_mode => 2
2078 , p_onhand_source => l_onhand_source
2079 , p_is_revision_control => FALSE
2080 , p_is_lot_control => FALSE
2081 , p_is_serial_control => FALSE
2082 , p_lot_expiration_date => sysdate
2083 , p_revision => NULL
2084 , p_lot_number => NULL
2085 , p_subinventory_code => NULL
2086 , p_locator_id => NULL
2087 , x_qoh => l_qoh
2088 , x_rqoh => l_rqoh
2089 , x_qr => l_qr
2090 , x_qs => l_qs
2091 , x_att => l_att
2092 , x_atr => l_atr
2093 );
2094 l_excess_qty := l_att;
2095 ELSE
2096 CSP_MINMAX_PVT.run_min_max_plan (
2097 p_item_select => l_item_select
2098 , p_handle_rep_item => 2
2099 , p_pur_revision => nvl(fnd_profile.value('INV_PURCHASING_BY_REVISION'),2)
2100 , p_cat_select => l_Cat_select
2101 , p_cat_set_id => nvl(p_item_rec.Category_set_id, l_category_set_id)
2102 , p_mcat_struct => l_mcat_struct_id
2103 , p_level => 1 -- run at orgn level
2104 , p_restock => 2 -- no restock
2105 , p_include_nonnet => 2 -- do not include non nettable subinv
2106 , p_include_po => nvl(p_item_rec.include_po, 1)
2107 , p_include_wip => nvl(p_item_rec.include_wip, 1)
2108 , p_include_if => nvl(p_item_rec.include_iface_sup, 1)
2109 , p_net_rsv => nvl(p_item_rec.net_rsv, 1)
2110 , p_net_unrsv => nvl(p_item_rec.net_unrsv, 1)
2111 , p_net_wip => nvl(p_item_rec.net_wip, 1)
2112 , p_org_id => esc.source_organization_id
2113 , p_user_id => l_user_id
2114 , p_employee_id => l_employee_id
2115 , p_subinv => null
2116 , p_dd_loc_id => p_item_rec.dd_loc_id
2117 , p_wip_batch_id => null --l_wip_batch_id
2118 , p_approval => to_number(nvl(FND_PROFILE.VALUE('INV_MINMAX_REORDER_APPROVED'),'1'))
2119 , p_buyer_hi => null --p_buyer_hi
2120 , p_buyer_lo => null --p_buyer_lo
2121 , p_range_buyer => null --l_range_buyer
2122 , p_cust_id => null --l_cust_id
2123 , p_po_org_id => null --l_po_org_id
2124 , p_range_sql => l_range_Sql
2125 , p_sort => 1 --p_sort
2126 , p_selection => 2 -- items above maximum quantity
2127 , p_sysdate => sysdate
2128 , p_s_cutoff => nvl(p_item_rec.s_cutoff, sysdate)
2129 , p_d_cutoff => nvl(p_item_rec.d_cutoff, sysdate)
2130 , p_order_by => l_order_by
2131 , p_encum_flag => null --l_encum_flag
2132 , p_cal_code => null --l_cal_code
2133 , p_exception_set_id => null --l_exception_set_id
2134 , x_return_status => l_Return_status
2135 , x_msg_data => l_msg_data);
2136
2137 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2138 RAISE FND_API.G_EXC_ERROR;
2139 ELSE
2140 BEGIN
2141 SELECT (tot_avail_qty - max_qty) excess_qty
2142 INTO l_excess_qty
2143 FROM INV_MIN_MAX_TEMP
2144 WHERE item_Segments = l_item;
2145
2146 EXCEPTION
2147 WHEN NO_DATA_FOUND THEN
2148 l_Excess_qty := -1;
2149 WHEN OTHERS THEN
2150 l_Excess_qty := -2;
2151 END;
2152
2153 -- cleanup inv_min_max_Temp
2154 DELETE FROM INV_MIN_MAX_TEMP;
2155 END IF;
2156 END IF;
2157 IF (nvl(l_excess_qty, 0) > 0) THEN
2158 x_excess_parts_tbl(idx).quantity := l_excess_qty;
2159 x_excess_parts_tbl(idx).inventory_item_id := p_item_rec.inventory_item_id;
2160 x_excess_parts_tbl(idx).source_org_id := esc.source_organization_id;
2161 x_excess_parts_tbl(idx).source_subinv := null;
2162
2163 IF (p_called_from = 'NOTIF') THEN
2164 Create_Notification_Details(
2165 p_notification_id => p_notification_id,
2166 p_order_by_dt => p_order_by_date,
2167 p_source_type => 'EXCESS',
2168 p_parts_rec => x_excess_parts_tbl(idx));
2169 END If;
2170 idx := idx + 1;
2171 END If;
2172 ELSIF (l_organization_type = 'F') THEN
2173 -- field engineers organization, loop thru all usable subinv and
2174 -- run min max at subinv level for all subinvs if item is minmax planned
2175 -- else calculate ATT for each usable subinv
2176 FOR rsc IN d_wrhs_subinv_cur(esc.source_organization_id) LOOP
2177 BEGIN
2178 SELECT inventory_planning_code
2179 INTO l_sub_minmax_flag
2180 FROM mtl_item_sub_inventories
2181 WHERE organization_id = esc.source_organization_id
2182 AND secondary_inventory = rsc.secondary_inventory_name
2183 AND inventory_item_id = p_item_rec.inventory_item_id;
2184 EXCEPTION
2185 WHEN NO_DATA_FOUND THEN
2186 l_sub_minmax_flag := 0;
2187 END;
2188
2189 l_Excess_qty := 0;
2190 IF (l_item_minmax_flag = 2 AND l_sub_minmax_flag = 2) THEN
2191 -- minmax planned
2192 CSP_MINMAX_PVT.run_min_max_plan (
2193 p_item_select => l_item_select
2194 , p_handle_rep_item => 2
2195 , p_pur_revision => nvl(fnd_profile.value('INV_PURCHASING_BY_REVISION'),2)
2196 , p_cat_select => l_Cat_select
2197 , p_cat_set_id => nvl(p_item_rec.Category_set_id, l_category_set_id)
2198 , p_mcat_struct => l_mcat_struct_id
2199 , p_level => 2 -- run at organization level
2200 , p_restock => 2 -- no restock
2201 , p_include_nonnet => 1 -- include non nettable subinv
2202 , p_include_po => nvl(p_item_rec.include_po, 1)
2203 , p_include_wip => nvl(p_item_rec.include_wip, 1)
2204 , p_include_if => nvl(p_item_rec.include_iface_sup, 1)
2205 , p_net_rsv => nvl(p_item_rec.net_rsv, 1)
2206 , p_net_unrsv => nvl(p_item_rec.net_unrsv, 1)
2207 , p_net_wip => nvl(p_item_rec.net_wip, 1)
2208 , p_org_id => esc.source_organization_id
2209 , p_user_id => l_user_id
2210 , p_employee_id => l_employee_id
2211 , p_subinv => rsc.secondary_inventory_name
2212 , p_dd_loc_id => p_item_rec.dd_loc_id
2213 , p_wip_batch_id => null --l_wip_batch_id
2214 , p_approval => to_number(nvl(FND_PROFILE.VALUE('INV_MINMAX_REORDER_APPROVED'),'1'))
2215 , p_buyer_hi => null --p_buyer_hi
2216 , p_buyer_lo => null --p_buyer_lo
2217 , p_range_buyer => null --l_range_buyer
2218 , p_cust_id => null --l_cust_id
2219 , p_po_org_id => null --l_po_org_id
2220 , p_range_sql => l_range_Sql
2221 , p_sort => 1 --p_sort
2222 , p_selection => 2 -- items above maximum quantity
2223 , p_sysdate => sysdate
2224 , p_s_cutoff => nvl(p_item_rec.s_cutoff, sysdate)
2225 , p_d_cutoff => nvl(p_item_rec.d_cutoff, sysdate)
2226 , p_order_by => l_order_by
2227 , p_encum_flag => null --l_encum_flag
2228 , p_cal_code => null --l_cal_code
2229 , p_exception_set_id => null --l_exception_set_id
2230 , x_return_status => l_Return_status
2231 , x_msg_data => l_msg_data);
2232
2233 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2234 RAISE FND_API.G_EXC_ERROR;
2235 ELSE
2236 BEGIN
2237 SELECT (tot_avail_qty - max_qty) excess_qty
2238 INTO l_excess_qty
2239 FROM INV_MIN_MAX_TEMP
2240 WHERE item_Segments = l_item;
2241 EXCEPTION
2242 WHEN NO_DATA_FOUND THEN
2243 l_Excess_qty := -1;
2244 WHEN OTHERS THEN
2245 l_Excess_qty := -1;
2246 END;
2247 -- cleanup inv_min_max_Temp
2248 DELETE FROM INV_MIN_MAX_TEMP;
2249 END If;
2250 ELSE
2251 INV_Quantity_Tree_PUB.Query_Quantities
2252 ( p_api_version_number => 1.0
2253 , p_init_msg_lst => 'F'
2254 , x_return_status => l_return_status
2255 , x_msg_count => l_msg_count
2256 , x_msg_data => l_msg_data
2257 , p_organization_id => esc.source_organization_id
2258 , p_inventory_item_id => p_item_Rec.inventory_item_id
2259 , p_tree_mode => 2
2260 , p_onhand_source => l_onhand_source
2261 , p_is_revision_control => FALSE
2262 , p_is_lot_control => FALSE
2263 , p_is_serial_control => FALSE
2264 , p_lot_expiration_date => sysdate
2265 , p_revision => NULL
2266 , p_lot_number => NULL
2267 , p_subinventory_code => rsc.secondary_inventory_name
2268 , p_locator_id => NULL
2269 , x_qoh => l_qoh
2270 , x_rqoh => l_rqoh
2271 , x_qr => l_qr
2272 , x_qs => l_qs
2273 , x_att => l_att
2274 , x_atr => l_atr
2275 );
2276 l_excess_qty := l_att;
2277
2278 END IF;
2279
2280 IF (nvl(l_excess_qty, 0) > 0) THEN
2281 -- create output record
2282 x_excess_parts_tbl(idx).quantity := l_Excess_qty;
2283 x_excess_parts_tbl(idx).inventory_item_id := p_item_rec.inventory_item_id;
2284 x_excess_parts_tbl(idx).source_org_id := esc.source_organization_id;
2285 x_excess_parts_tbl(idx).source_subinv := rsc.secondary_inventory_name;
2286 IF (p_called_from = 'NOTIF') THEN
2287 Create_Notification_Details(
2288 p_notification_id => p_notification_id,
2289 p_order_by_dt => p_order_by_date,
2290 p_source_type => 'EXCESS',
2291 p_parts_rec => x_excess_parts_tbl(idx));
2292 END If;
2293 idx := idx + 1;
2294 END IF;
2295 END LOOP;
2296 END IF;
2297 END LOOP;
2298
2299 EXCEPTION
2300 WHEN FND_API.G_EXC_ERROR THEN
2301 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2302 P_API_NAME => L_API_NAME
2303 ,P_PKG_NAME => G_PKG_NAME
2304 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2305 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2306 ,X_MSG_COUNT => X_MSG_COUNT
2307 ,X_MSG_DATA => X_MSG_DATA
2308 ,X_RETURN_STATUS => X_RETURN_STATUS);
2309 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2310 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2311 P_API_NAME => L_API_NAME
2312 ,P_PKG_NAME => G_PKG_NAME
2313 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2314 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2315 ,X_MSG_COUNT => X_MSG_COUNT
2316 ,X_MSG_DATA => X_MSG_DATA
2317 ,X_RETURN_STATUS => X_RETURN_STATUS);
2318 WHEN OTHERS THEN
2319
2320 Rollback to calculate_excess_pub;
2321 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
2322 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
2323 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
2324 FND_MSG_PUB.ADD;
2325 fnd_msg_pub.count_and_get
2326 ( p_count => x_msg_count
2327 , p_data => x_msg_data);
2328 x_return_status := FND_API.G_RET_STS_ERROR;
2329 END;
2330
2331 PROCEDURE Generate_Repair_Recomm(
2332 p_notification_id IN NUMBER
2333 ,p_organization_id IN NUMBER
2334 ,p_inventory_item_id IN NUMBER
2335 ,p_order_by_Date IN DATE
2336 ,p_supercess_item_yn IN VARCHAR2
2337 ) IS
2338
2339 CURSOR repair_sources_cur IS
2340 select misl.source_organization_id
2341 from MRP_ITEM_SOURCING_LEVELS_V misl, csp_planning_parameters cpp
2342 where cpp.organization_id = p_organization_id
2343 and misl.organization_id = cpp.organization_id
2344 and misl.assignment_set_id =cpp.defective_assignment_set_id
2345 and inventory_item_id = p_inventory_item_id
2346 and SOURCE_TYPE = 1
2347 and sourcing_level = (select min(sourcing_level) from MRP_ITEM_SOURCING_LEVELS_V
2348 where organization_id = p_organization_id
2349 and assignment_set_id = cpp.defective_assignment_set_id
2350 and inventory_item_id = p_inventory_item_id
2351 and sourcing_level not in (2,9));
2352
2353 CURSOR repair_suppliers_cur IS
2354 select misl.source_type, misl.source_organization_id
2355 from MRP_ITEM_SOURCING_LEVELS_V misl, csp_planning_parameters cpp
2356 where cpp.organization_id = p_organization_id
2357 and misl.organization_id = cpp.organization_id
2358 and misl.assignment_set_id =cpp.repair_assignment_set_id
2359 and inventory_item_id = p_inventory_item_id
2360 and SOURCE_TYPE in (1,3)
2361 and sourcing_level = (select min(sourcing_level) from MRP_ITEM_SOURCING_LEVELS_V
2362 where organization_id = p_organization_id
2363 and assignment_set_id = cpp.repair_assignment_set_id
2364 and inventory_item_id = p_inventory_item_id
2365 and sourcing_level not in (2,9))
2366 order by misl.rank;
2367
2368 CURSOR d_wrhs_subinv_cur(p_orgn_id NUMBER) IS
2369 SELECT secondary_inventory_name
2370 FROM mtl_secondary_inventories
2371 WHERE organization_id = p_orgn_id
2372 AND secondary_inventory_name NOT IN
2373 (SELECT secondary_inventory_name
2374 FROM csp_sec_inventories
2375 WHERE condition_type = 'G'
2376 AND organization_id = p_orgn_id);
2377
2378 CURSOR u_wrhs_subinv_cur(p_orgn_id NUMBER) IS
2379 SELECT secondary_inventory_name
2380 FROM csp_sec_inventories
2381 WHERE organization_id = p_orgn_id
2382 AND condition_type = 'B';
2383
2384 l_onhand_source NUMBER := 3;
2385 l_return_status VARCHAR2(1);
2386 l_msg_count NUMBER;
2387 l_msg_data VARCHAR2(1000);
2388 l_qoh NUMBER;
2389 l_rqoh NUMBER;
2390 l_qr NUMBER;
2391 l_qs NUMBER;
2392 l_att NUMBER;
2393 l_atr NUMBER;
2394 l_repair_qty NUMBER;
2395 l_organization_type VARCHAR2(10);
2396 l_condition_type VARCHAR2(10);
2397 l_parts_rec csp_planner_notifications.excess_parts_rectype;
2398
2399 l_Serviceable VARCHAR2(30);
2400 l_repair_supplier_id NUMBER;
2401 l_source_type VARCHAR2(30):= 'REPAIR';
2402 BEGIN
2403 l_return_status := FND_API.G_RET_STS_SUCCESS;
2404
2405 -- Find repair supplier for the warehouse
2406 FOR rsc IN repair_suppliers_cur LOOP
2407 IF (rsc.source_type = 1) THEN
2408 BEGIN
2409 select serv_req_enabled_code
2410 into l_Serviceable
2411 from mtl_system_items
2412 where inventory_item_id = p_inventory_item_id
2413 and organization_id = rsc.source_organization_id;
2414 EXCEPTION
2415 when no_data_found then
2416 null;
2417 END;
2418 IF l_serviceable = 'E' THEN
2419 l_repair_supplier_id := rsc.source_organization_id;
2420 l_source_type := 'REPAIR';
2421 exit;
2422 END IF;
2423 ELSE
2424 l_source_type := 'EXTREPAIR';
2425 exit;
2426 END IF;
2427 END LOOP;
2428
2429 FOR rep IN repair_sources_cur LOOP
2430 BEGIN
2431 SELECT organization_type,
2432 nvl(condition_type, 'G')
2433 INTO l_organization_type,
2434 l_condition_type
2435 FROM csp_planning_parameters
2436 WHERE organization_id = rep.source_organization_id
2437 AND secondary_inventory IS NULL;
2438 EXCEPTION
2439 WHEN NO_DATA_FOUND THEN
2440 l_organization_type := 'F';
2441 WHEN OTHERS THEN
2442 l_organization_type := null;
2443 END;
2444
2445 IF (l_organization_type = 'W') THEN
2446 l_repair_qty := 0;
2447 IF (l_condition_type = 'B') THEN
2448 FOR wsc IN d_wrhs_subinv_cur(rep.source_organization_id) LOOP
2449 INV_Quantity_Tree_PUB.Query_Quantities
2450 ( p_api_version_number => 1.0
2451 , p_init_msg_lst => 'F'
2452 , x_return_status => l_return_status
2453 , x_msg_count => l_msg_count
2454 , x_msg_data => l_msg_data
2455 , p_organization_id => rep.source_organization_id
2456 , p_inventory_item_id => p_inventory_item_id
2457 , p_tree_mode => 2
2458 , p_onhand_source => l_onhand_source -- need to check out
2459 , p_is_revision_control => FALSE
2460 , p_is_lot_control => FALSE
2461 , p_is_serial_control => FALSE
2462 , p_lot_expiration_date => sysdate
2463 , p_revision => NULL
2464 , p_lot_number => NULL
2465 , p_subinventory_code => wsc.secondary_inventory_name
2466 , p_locator_id => NULL
2467 , x_qoh => l_qoh
2468 , x_rqoh => l_rqoh
2469 , x_qr => l_qr
2470 , x_qs => l_qs
2471 , x_att => l_att
2472 , x_atr => l_atr
2473 );
2474 IF (l_att > 0) THEN
2475 l_repair_qty := l_repair_qty + l_att;
2476 END IF;
2477 END LOOP;
2478 IF (l_repair_qty > 0) THEN
2479 -- call create notification details
2480 l_parts_rec.inventory_item_id := p_inventory_item_id;
2481 l_parts_Rec.source_org_id := rep.source_organization_id;
2482 l_parts_rec.source_subinv := null;
2483 l_parts_rec.quantity := l_repair_qty;
2484 l_parts_rec.repair_supplier_id := l_repair_supplier_id;
2485
2486 Create_Notification_Details(
2487 p_source_type => l_source_type
2488 ,p_order_by_dt => p_order_by_date
2489 ,p_notification_id => p_notification_id
2490 ,p_parts_rec => l_parts_Rec);
2491 END IF;
2492 ELSE -- if warehouse is usable
2493 FOR wsc IN u_wrhs_subinv_cur(rep.source_organization_id) LOOP
2494 INV_Quantity_Tree_PUB.Query_Quantities
2495 ( p_api_version_number => 1.0
2496 , p_init_msg_lst => 'F'
2497 , x_return_status => l_return_status
2498 , x_msg_count => l_msg_count
2499 , x_msg_data => l_msg_data
2500 , p_organization_id => rep.source_organization_id
2501 , p_inventory_item_id => p_inventory_item_id
2502 , p_tree_mode => 2
2503 , p_onhand_source => l_onhand_source -- need to check out
2504 , p_is_revision_control => FALSE
2505 , p_is_lot_control => FALSE
2506 , p_is_serial_control => FALSE
2507 , p_lot_expiration_date => sysdate
2508 , p_revision => NULL
2509 , p_lot_number => NULL
2510 , p_subinventory_code => wsc.secondary_inventory_name
2511 , p_locator_id => NULL
2512 , x_qoh => l_qoh
2513 , x_rqoh => l_rqoh
2514 , x_qr => l_qr
2515 , x_qs => l_qs
2516 , x_att => l_att
2517 , x_atr => l_atr
2518 );
2519 IF (l_att > 0) THEN
2520 l_repair_qty := l_repair_qty + l_att;
2521 END IF;
2522 END LOOP;
2523 IF (l_repair_qty > 0) THEN
2524 -- call create notification details
2525 l_parts_rec.inventory_item_id := p_inventory_item_id;
2526 l_parts_Rec.source_org_id := rep.source_organization_id;
2527 l_parts_rec.source_subinv := null;
2528 l_parts_rec.quantity := l_repair_qty;
2529 l_parts_rec.repair_supplier_id := l_repair_supplier_id;
2530
2531 Create_Notification_Details(
2532 p_source_type => l_source_type
2533 ,p_order_by_dt => p_order_by_date
2534 ,p_notification_id => p_notification_id
2535 ,p_parts_rec => l_parts_Rec);
2536 END IF;
2537 END IF;
2538 ELSE -- if FE organization
2539 FOR rsc IN u_wrhs_subinv_cur(rep.source_organization_id) LOOP
2540 --calculate ATT for each of the defective subinvs
2541 INV_Quantity_Tree_PUB.Query_Quantities
2542 ( p_api_version_number => 1.0
2543 , p_init_msg_lst => 'F'
2544 , x_return_status => l_return_status
2545 , x_msg_count => l_msg_count
2546 , x_msg_data => l_msg_data
2547 , p_organization_id => rep.source_organization_id
2548 , p_inventory_item_id => p_inventory_item_id
2549 , p_tree_mode => 2
2550 , p_onhand_source => l_onhand_source -- need to check out
2551 , p_is_revision_control => FALSE
2552 , p_is_lot_control => FALSE
2553 , p_is_serial_control => FALSE
2554 , p_lot_expiration_date => sysdate
2555 , p_revision => NULL
2556 , p_lot_number => NULL
2557 , p_subinventory_code => rsc.secondary_inventory_name
2558 , p_locator_id => NULL
2559 , x_qoh => l_qoh
2560 , x_rqoh => l_rqoh
2561 , x_qr => l_qr
2562 , x_qs => l_qs
2563 , x_att => l_att
2564 , x_atr => l_atr
2565 );
2566 IF (l_Att > 0) THEN
2567 -- call create notification details
2568 l_parts_rec.inventory_item_id := p_inventory_item_id;
2569 l_parts_Rec.source_org_id := rep.source_organization_id;
2570 l_parts_rec.source_subinv := rsc.secondary_inventory_name;
2571 l_parts_rec.quantity := l_att;
2572 l_parts_rec.repair_supplier_id := l_repair_supplier_id;
2573
2574 Create_Notification_Details(
2575 p_source_type => l_source_type
2576 ,p_order_by_dt => p_order_by_date
2577 ,p_notification_id => p_notification_id
2578 ,p_parts_rec => l_parts_Rec);
2579 END IF;
2580 END LOOP;
2581 END IF;
2582 END LOOP;
2583 -- elsif supercessed_item,
2584 IF (p_supercess_item_yn = 'Y') THEN
2585 -- calculate repair quantity in current organization.
2586 INV_Quantity_Tree_PUB.Query_Quantities
2587 ( p_api_version_number => 1.0
2588 , p_init_msg_lst => 'F'
2589 , x_return_status => l_return_status
2590 , x_msg_count => l_msg_count
2591 , x_msg_data => l_msg_data
2592 , p_organization_id => p_organization_id
2593 , p_inventory_item_id => p_inventory_item_id
2594 , p_tree_mode => 2
2595 , p_onhand_source => l_onhand_source -- need to check out
2596 , p_is_revision_control => FALSE
2597 , p_is_lot_control => FALSE
2598 , p_is_serial_control => FALSE
2599 , p_lot_expiration_date => sysdate
2600 , p_revision => NULL
2601 , p_lot_number => NULL
2602 , p_subinventory_code => NULL
2603 , p_locator_id => NULL
2604 , x_qoh => l_qoh
2605 , x_rqoh => l_rqoh
2606 , x_qr => l_qr
2607 , x_qs => l_qs
2608 , x_att => l_att
2609 , x_atr => l_atr
2610 );
2611 IF (l_att > 0) THEN
2612 -- call create notification details
2613 l_parts_rec.inventory_item_id := p_inventory_item_id;
2614 l_parts_Rec.source_org_id := p_organization_id;
2615 l_parts_rec.source_subinv := NULL; --rep.source_subinventory;
2616 l_parts_rec.quantity := l_att;
2617 l_parts_rec.repair_supplier_id := l_repair_supplier_id;
2618
2619 Create_Notification_Details(
2620 p_source_type => l_source_type
2621 ,p_order_by_Dt => p_order_by_date
2622 ,p_notification_id => p_notification_id
2623 ,p_parts_rec => l_parts_Rec);
2624 END IF;
2625 END IF;
2626
2627 END;
2628
2629 PROCEDURE Cleanup_Notifications(p_organization_id NUMBER) IS
2630 BEGIN
2631 DELETE FROM csp_notification_Details
2632 WHERE notification_id in
2633 (SELECT notification_id
2634 FROM csp_notifications
2635 WHERE trunc(nvl(suppress_end_date, sysdate)) <= trunc(sysdate)
2636 AND organization_id = p_organization_id);
2637
2638 DELETE FROM csp_notifications
2639 WHERE trunc(nvl(suppress_end_date, sysdate)) <= trunc(sysdate)
2640 AND organization_id = p_organization_id;
2641 END;
2642
2643 PROCEDURE Create_Notification_Details(
2644 p_source_type IN VARCHAR2
2645 ,p_order_by_dt IN DATE := sysdate
2646 ,p_notification_id IN NUMBER
2647 ,p_parts_rec IN csp_planner_notifications.excess_parts_rectype) IS
2648 l_notif_detail_id NUMBER;
2649 BEGIN
2650
2651 l_notif_detail_id := NULL;
2652 CSP_Notification_Details_PKG.Insert_Row(
2653 px_NOTIFICATION_DETAIL_ID => l_notif_detail_id
2654 ,p_NOTIFICATION_ID => p_notification_id
2655 ,p_INVENTORY_ITEM_ID => p_parts_rec.inventory_item_id
2656 ,p_AVAILABLE_QUANTITY => p_parts_rec.quantity
2657 ,p_ORDER_BY_DATE => nvl(p_order_by_dt,sysdate)
2658 ,p_SOURCE_TYPE => p_source_type
2659 ,p_SOURCE_ORGANIZATION_ID => p_parts_rec.source_org_id
2660 ,p_SOURCE_SUBINVENTORY => p_parts_rec.source_subinv
2661 ,p_CREATED_BY => nvl(fnd_global.user_id, 0)
2662 ,p_CREATION_DATE => sysdate
2663 ,p_LAST_UPDATED_BY => nvl(fnd_global.user_id, 0)
2664 ,p_LAST_UPDATE_DATE => sysdate
2665 ,p_LAST_UPDATE_LOGIN => nvl(fnd_global.login_id, -1)
2666 ,p_ATTRIBUTE_CATEGORY => null
2667 ,p_ATTRIBUTE1 => null
2668 ,p_ATTRIBUTE2 => null
2669 ,p_ATTRIBUTE3 => null
2670 ,p_ATTRIBUTE4 => null
2671 ,p_ATTRIBUTE5 => null
2672 ,p_ATTRIBUTE6 => null
2673 ,p_ATTRIBUTE7 => null
2674 ,p_ATTRIBUTE8 => null
2675 ,p_ATTRIBUTE9 => null
2676 ,p_ATTRIBUTE10 => null
2677 ,p_ATTRIBUTE11 => null
2678 ,p_ATTRIBUTE12 => null
2679 ,p_ATTRIBUTE13 => null
2680 ,p_ATTRIBUTE14 => null
2681 ,p_ATTRIBUTE15 => null
2682 ,p_REPAIR_SUPPLIER_ID => p_parts_rec.repair_supplier_id
2683 ,p_ORDER_NUMBER => NULL
2684 );
2685 END;
2686 END;