[Home] [Help]
PACKAGE BODY: APPS.CSP_EXCESS_PARTS_PVT
Source
1 PACKAGE BODY CSP_EXCESS_PARTS_PVT AS
2 /* $Header: cspvpexb.pls 120.3 2006/06/08 20:35:15 ajosephg noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30):='CSP_EXCESS_PARTS_PVT';
4 G_FILE_NAME CONSTANT VARCHAR2(30):='cspvpexb.pls';
5
6 g_node_level_id Varchar2(2000):= 1;
7
8 PROCEDURE NODE_LEVEL_ID(p_level_id IN VARCHAR2)
9 IS
10 BEGIN
11 g_node_level_id := p_level_id;
12 End;
13
14 FUNCTION NODE_LEVEL_ID return VARCHAR2 is
15 BEGIN
16 return(g_node_level_id);
17 End;
18
19 Procedure excess_parts
20 ( errbuf OUT NOCOPY varchar2
21 ,retcode OUT NOCOPY number
22 ,p_org_id IN NUMBER
23 ,P_level_id IN VARCHAR2
24 ,p_level IN NUMBER
25 ,P_SUBINV_ENABLE_FLAG IN NUMBER
26 ,p_subinv IN VARCHAR2
27 ,p_selection IN NUMBER
28 ,p_cat_set_id IN NUMBER
29 ,p_catg_struct_id IN NUMBER
30 ,p_Catg_lo IN VARCHAR2
31 ,p_catg_hi IN VARCHAR2
32 ,p_item_lo IN VARCHAR2
33 ,p_item_hi IN VARCHAR2
34 ,p_planner_lo IN VARCHAR2
35 ,p_planner_hi IN VARCHAR2
36 ,p_buyer_lo IN VARCHAR2
37 ,p_buyer_hi IN VARCHAR2
38 ,p_sort IN VARCHAR2
39 ,p_d_cutoff IN VARCHAR2
40 ,p_d_cutoff_rel IN NUMBER
41 ,p_s_cutoff IN VARCHAR2
42 ,p_s_cutoff_rel IN NUMBER
43 ,p_user_id IN NUMBER
44 ,p_restock IN NUMBER
45 ,p_handle_rep_item IN NUMBER
46 ,p_dd_loc_id IN NUMBER
47 ,p_net_unrsv IN NUMBER
48 ,p_net_rsv IN NUMBER
49 ,p_net_wip IN NUMBER
50 ,p_include_po IN NUMBER
51 ,p_include_wip IN NUMBER
52 ,p_include_if IN NUMBER
53 ,p_include_nonnet IN NUMBER
54 ,p_lot_ctl IN NUMBER
55 ,p_display_mode IN NUMBER
56 ,p_show_desc IN NUMBER
57 ,p_pur_revision IN NUMBER
58 ,p_called_from IN VARCHAR2
59 ) IS
60
61 l_api_version_number CONSTANT NUMBER := 1.0;
62 l_api_name CONSTANT VARCHAR2(30) := 'Create Excess Parts';
63 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
64 l_msg_count NUMBER;
65 l_msg_data VARCHAR2(2000);
66
67 l_user_id NUMBER;
68 l_login_id NUMBER;
69 l_today DATE;
70 l_employee_id NUMBER;
71 l_Restock NUMBER;
72 l_d_cutoff DATE;
73 l_s_cutoff DATE;
74 l_po_org_id NUMBER;
75 l_org_name VARCHAR2(2000);
76 l_encum_flag VARCHAR2(30) := 'N';
77 l_cal_code VARCHAR2(30);
78 l_exception_set_id NUMBER;
79 l_mcat_struct_id NUMBER;
80 l_category_set_id NUMBER;
81 l_range_buyer VARCHAR2(240) := '1=1';
82 l_range_sql VARCHAR2(2000);
83 l_item_select VARCHAr2(800);
84 l_Cat_Select VARCHAR2(800);
85 l_order_by VARCHAr2(30);
86 l_cust_id NUMBER;
87 l_wip_batch_id NUMBER;
88 error_message VARCHAR2(80);
89 l_need_by_date DATE;
90 l_order_by_date DATE;
91 l_est_date DATE;
92 l_lead_time NUMBER;
93 l_notification_id NUMBER;
94 l_count NUMBER;
95
96 l_level NUMBER;
97 p_organization_id NUMBER;
98 p_subinventory_code VARCHAR2(2000);
99 p_condition_type VARCHAR2(200);
100 l_item_id Number;
101
102 l_total_onhand number := 0;
103 l_onhand number := 0;
104 l_demand number := 0;
105 l_previous_item_id number;
106 l_excess number := 0;
107 l_max number := 0;
108 x_excess_line_id number;
109 l_excess_rule_id number;
110 L_LOC_ASSIGNMENT_ID number;
111
112 CURSOR employee_id_cur IS
113 SELECT employee_id
114 FROM fnd_user
115 WHERE user_id = l_user_id;
116
117 CURSOR minmax_rslts_cur IS
118 SELECT ITEM_SEGMENTS
119 , DESCRIPTION
120 , ERROR
121 , SORTEE
122 , MIN_QTY
123 , MAX_QTY
124 , ONHAND_QTY
125 , SUPPLY_QTY
126 , DEMAND_QTY
127 , TOT_AVAIL_QTY
128 , MIN_ORD_QTY
129 , MAX_ORD_QTY
130 , FIX_MULT
131 , REORD_QTY
132 FROM INV_MIN_MAX_TEMP;
133
134 l_minmax_rslts_rec minmax_rslts_cur%ROWTYPE;
135
136 CURSOR item_attr_cur(p_item_Segments VARCHAR2,
137 p_organization_id NUMBER) IS
138 SELECT c.description description,
139 c.repetitive_planning_flag repetitive_planned_item,
140 c.fixed_lead_time fixed_lead_time,
141 c.variable_lead_time variable_lead_time,
142 NVL(c.preprocessing_lead_time, 0) +
143 NVL(c.full_lead_time, 0) +
144 NVL(c.postprocessing_lead_time, 0) buying_lead_time,
145 c.primary_uom_code primary_uom,
146 p.ap_accrual_account accru_acct,
147 p.invoice_price_var_account ipv_acct,
148 NVL(c.encumbrance_account, p.encumbrance_account) budget_acct,
149 DECODE(c.inventory_asset_flag, 'Y', p.material_account,
150 NVL(c.expense_account, p.expense_account)) charge_acct,
151 NVL(c.source_type, p.source_type) src_type,
152 DECODE(c.source_type, NULL,
153 DECODE(p.source_type, NULL, NULL, p.source_organization_id),
154 c.source_organization_id) src_org,
155 DECODE(c.source_type, NULL,
156 DECODE(p.source_type, NULL, NULL, p.source_subinventory),
157 c.source_subinventory) src_subinv,
158 c.purchasing_enabled_flag purch_flag,
159 c.internal_order_enabled_flag order_flag,
160 c.mtl_transactions_enabled_flag transact_flag,
161 c.list_price_per_unit unit_price,
162 c.planning_make_buy_code mbf,
163 c.inventory_item_id item_id,
164 c.planner_code planner,
165 build_in_wip_flag build_in_wip,
166 pick_components_flag pick_components
167 FROM mtl_system_items_kfv c,
168 mtl_parameters p
169 WHERE c.concatenated_segments = p_item_Segments
170 AND c.organization_id = p.organization_id
171 AND p.organization_id = p_organization_id;
172
173 l_item_attr_rec item_attr_cur%ROWTYPE;
174
175 CURSOR PLANNING_NODE_REC IS
176 SELECT cpp.NODE_TYPE,cpp.ORGANIZATION_ID,cpp.SECONDARY_INVENTORY,cpp.CONDITION_TYPE,
177 cpp.planning_parameters_id,cpp.level_id,csin.parts_loop_id,csin.hierarchy_node_id,
178 csin.owner_resource_id, csin.owner_resource_type
179 FROM CSP_PLANNING_PARAMETERS cpp,csp_sec_inventories csin
180 WHERE LEVEL_ID LIKE p_level_id||'%'
181 and cpp.organization_id = csin.organization_id(+)
182 and cpp.secondary_inventory = csin.secondary_inventory_name(+);
183
184 cursor effective_subinv(p_resource_id Number,
185 p_resource_type varchar2,
186 p_organization_id Number,
187 p_subinventory_code Varchar2)
188 is
189 select CSP_INV_LOC_ASSIGNMENT_ID from csp_inv_loc_assignments
190 where resource_id = p_resource_id and
191 resource_type = p_resource_type and
192 organization_id = p_organization_id and
193 SUBINVENTORY_CODE = p_subinventory_code and
194 (EFFECTIVE_DATE_END is null or trunc(EFFECTIVE_DATE_END) > trunc(sysdate));
195
196 Cursor INV_MIN_MAX_TEMP IS
197 SELECT ITEM_SEGMENTS,MIN_QTY,MAX_QTY,ONHAND_QTY,SUPPLY_QTY,DEMAND_QTY,
198 TOT_AVAIL_QTY,MIN_ORD_QTY,MAX_ORD_QTY,FIX_MULT,REORD_QTY
199 FROM INV_MIN_MAX_TEMP;
200
201 cursor c_org_items is
202 select distinct moq.inventory_item_id,
203 nvl(msib.max_minmax_quantity,0) max,
204 revision_qty_control_code
205 from mtl_onhand_quantities_detail moq,
206 mtl_system_items_b msib
207 where moq.organization_id = p_organization_id
208 and msib.organization_id = moq.organization_id
209 and msib.inventory_item_id = moq.inventory_item_id
210 and nvl(msib.INVENTORY_PLANNING_CODE,6) = 6;
211
212 cursor c_org_subinventories(c_inventory_item_id number) is
213 select distinct moq.subinventory_code
214 from mtl_onhand_quantities moq,
215 csp_planning_parameters cpp
216 where moq.organization_id = p_organization_id
217 and moq.inventory_item_id = c_inventory_item_id
218 and cpp.organization_id = moq.organization_id
219 and cpp.secondary_inventory = moq.subinventory_code
220 and cpp.condition_type = 'G';
221
222 cursor c_subinventories is
223 select msi.secondary_inventory_name
224 from mtl_secondary_inventories msi,
225 csp_planning_parameters cpp
226 where msi.organization_id = p_organization_id
227 and msi.secondary_inventory_name = nvl(p_subinventory_code,msi.secondary_inventory_name)
228 and cpp.organization_id = msi.organization_id
229 and cpp.secondary_inventory = msi.secondary_inventory_name
230 and cpp.condition_type = 'G';
231
232 cursor c_sub_items(c_subinventory_code varchar2) is
233 select mosv.inventory_item_id,
234 nvl(misi.max_minmax_quantity,0) max,
235 msib.revision_qty_control_code
236 from mtl_onhand_sub_v mosv,
237 mtl_item_sub_inventories misi,
238 mtl_system_items_b msib
239 where mosv.organization_id = p_organization_id
240 and mosv.subinventory_code = c_subinventory_code
241 and misi.organization_id(+) = mosv.organization_id
242 and misi.inventory_item_id(+) = mosv.inventory_item_id
243 and misi.secondary_inventory(+) = mosv.subinventory_code
244 and msib.organization_id = mosv.organization_id
245 and msib.inventory_item_id = mosv.inventory_item_id
246 and nvl(misi.INVENTORY_PLANNING_CODE,6) = 6
247 /* Added to avoid duplicate rows of revision controled item */
248 group by mosv.inventory_item_id,
249 misi.max_minmax_quantity,
250 msib.revision_qty_control_code;
251 Begin
252 SAVEPOINT Create_excess_parts_PUB;
253
254 SELECT Sysdate INTO l_today FROM dual;
255 l_user_id := nvl(fnd_global.user_id, 0) ;
256 l_login_id := nvl(fnd_global.login_id, -1);
257
258 FOR Rec IN PLANNING_NODE_REC LOOP
259 IF (Rec.NODE_TYPE <> 'REGION' AND Rec.ORGANIZATION_ID is NOT NULL) THEN
260
261 If (Rec.owner_resource_id is NOT NULL and
262 Rec.NODE_TYPE = 'SUBINVENTORY' and
263 Rec.SECONDARY_INVENTORY is NOT NULL) THEN
264
265 open effective_subinv(Rec.owner_resource_id,Rec.owner_resource_type,Rec.ORGANIZATION_ID,Rec.SECONDARY_INVENTORY);
266 fetch effective_subinv into L_LOC_ASSIGNMENT_ID;
267 close effective_subinv;
268 End if;
269
270 If (Rec.NODE_TYPE = 'ORGANIZATION_WH') OR
271 (Rec.NODE_TYPE = 'SUBINVENTORY' and
272 Rec.SECONDARY_INVENTORY is NOT NULL and
273 (Rec.owner_resource_id is NULL or
274 (Rec.owner_resource_id is NOT NULL and
275 L_LOC_ASSIGNMENT_ID is NOT NULL)
276 )
277 )
278 THEN
279
280 IF (Rec.NODE_TYPE = 'SUBINVENTORY' and Rec.SECONDARY_INVENTORY is NOT NULL) THEN
281 p_organization_id := Rec.ORGANIZATION_ID;
282 l_level := 2;
283 p_subinventory_code := Rec.SECONDARY_INVENTORY;
284 p_condition_type := REC.CONDITION_TYPE;
285 Elsif (Rec.NODE_TYPE = 'ORGANIZATION_WH') THEN
286 p_organization_id := Rec.ORGANIZATION_ID;
287 l_level := 1;
288 p_subinventory_code := Null;
289 p_condition_type := Null;
290 End if;
291
292 --Delete remaining open excess lines from previous run
293 if p_called_from = 'STD' then
294 clean_up(p_organization_id => p_organization_id,
295 p_subinventory_code => p_subinventory_code,
296 p_condition_type => p_condition_type);
297 end if;
298 -- 1. get values of all parameters for calling run_min_max_plan
299 if p_restock = 1 and p_dd_loc_id is null then
300 begin
301 Select MEANING
302 into error_message
303 FROM MFG_LOOKUPS
304 WHERE LOOKUP_TYPE='INV_MMX_RPT_MSGS'
305 and LOOKUP_CODE = 4;
306 exception
307 when others then
308 null;
309 end;
310 end if;
311
312 -- get employee id
313 OPEN employee_id_cur;
314 FETCH employee_id_cur INTO l_employee_id;
315 CLOSE employee_id_cur;
316
317 l_d_cutoff := to_date(p_d_cutoff,'YYYY/MM/DD HH24:MI:SS');
318 l_s_cutoff := to_date(p_s_cutoff,'YYYY/MM/DD HH24:MI:SS');
319 l_D_CUTOFF := NVL(l_D_CUTOFF, SYSDATE);
320 l_S_CUTOFF := NVL(l_S_CUTOFF, SYSDATE);
321
322 IF (P_D_CUTOFF_REL IS NOT NULL) THEN
323 l_D_CUTOFF := NVL(l_D_CUTOFF, sysdate) + P_D_CUTOFF_REL;
324 END IF;
325
326 IF (P_S_CUTOFF_REL IS NOT NULL) THEN
327 l_S_CUTOFF := NVL(l_S_CUTOFF, sysdate) + P_S_CUTOFF_REL;
328 END IF;
329
330 /* get encum flag, org name, PO org ID */
331 declare
332 l_operating_unit number;
333 begin
334 select operating_unit, substr(organization_name,1,30), operating_unit
335 into l_operating_unit, l_org_name, l_po_org_id
336 from org_organization_definitions
337 where organization_id = p_organization_id;
338
339 select nvl(req_encumbrance_flag, 'N')
340 into l_encum_flag
341 from financials_system_params_all
342 where nvl(org_id,-11)=nvl(l_operating_unit,-11);
343 end;
344
345 /* get calendar */
346 select p.calendar_code, p.calendar_exception_set_id
347 into l_cal_code, l_exception_set_id
348 from mtl_parameters p
349 where p.organization_id = p_organization_id;
350
351 /* Validate cat set and MCAT struct */
352 IF (p_cat_set_id is not null and p_catg_struct_id is not null) then
353 SELECT STRUCTURE_ID
354 into l_mcat_struct_id
355 FROM MTL_CATEGORY_SETS
356 WHERE CATEGORY_SET_ID = p_cat_set_id;
357 ELSE
358 SELECT CSET.CATEGORY_SET_ID, CSET.STRUCTURE_ID
359 INTO l_category_set_id, l_mcat_struct_id
360 FROM MTL_CATEGORY_SETS CSET,
361 MTL_DEFAULT_CATEGORY_SETS DEF
362 WHERE DEF.CATEGORY_SET_ID = CSET.CATEGORY_SET_ID
363 AND DEF.FUNCTIONAL_AREA_ID = 1;
364 END IF;
365
366 IF p_buyer_lo is not null and p_buyer_hi is not null then
367 L_RANGE_BUYER := 'v.full_name between ' ||''''||P_BUYER_LO||
368 '''' || ' and ' || ''''||P_BUYER_HI||'''';
369 ELSIF p_BUYER_lo is not null then
370 L_RANGE_BUYER := 'v.full_name >= ' ||''''||P_BUYER_LO||'''';
371 ELSIF p_BUYER_hi is not null then
372 L_RANGE_BUYER := 'v.full_name <= ' ||''''||P_BUYER_HI||'''';
373 END IF;
374
375 /* set order by clause */
376 IF P_sort=1 then
377 l_order_by := ' order by 1';
378 ELSIF P_sort = 2 then
379 l_order_by := ' order by 13,1';
380 ELSIF P_sort = 3 then
381 l_order_by := ' order by 11,1';
382 ELSIF P_sort = 4 then
383 l_order_by := ' order by 12,1';
384 END IF;
385
386 Build_item_cat_select(
387 p_Cat_Structure_id => l_mcat_struct_id,
388 x_item_select => l_item_Select,
389 x_cat_Select => l_cat_select);
390
391 Build_range_sql(
392 p_cat_structure_id => l_mcat_Struct_id
393 , p_cat_lo => p_Catg_lo
394 , p_cat_hi => p_catg_hi
395 , p_item_lo => p_item_lo
396 , p_item_hi => p_item_hi
397 , p_planner_lo => p_planner_lo
398 , p_planner_hi => p_planner_hi
399 , p_lot_ctl => p_lot_Ctl
400 , x_range_sql => l_range_sql);
401
402 IF p_dd_loc_id is not null THEN
403 -- get customer id
404 BEGIN
405 select customer_id
406 into l_cust_id
407 from po_location_associations
408 where location_id = P_dd_loc_id;
409 EXCEPTION
410 when no_data_found then
411 l_cust_id := 0;
412 END;
413 END IF;
414
415 select WIP_JOB_SCHEDULE_INTERFACE_S.nextval
416 into l_WIP_BATCH_ID
417 from dual;
418
419 if p_called_from <> 'PART_STATUS' and nvl(p_condition_type,'B') = 'B' then
420 defective_return(p_organization_id,
421 p_subinventory_code,
422 rec.planning_parameters_id,
423 rec.level_id,
424 rec.parts_loop_id,
425 rec.hierarchy_node_id,
426 p_called_from);
427 end if;
428
429 if nvl(p_condition_type,'G') = 'G' then
430 CSP_MINMAX_PVT.run_min_max_plan(
431 p_item_select => l_item_select
432 , p_handle_rep_item => p_handle_rep_item
433 , p_pur_revision => nvl(fnd_profile.value('INV_PURCHASING_BY_REVISION'),2)
434 , p_cat_select => l_Cat_select
435 , p_cat_set_id => nvl(p_Cat_set_id,l_category_set_id)
436 , p_mcat_struct => l_mcat_struct_id
437 , p_level => l_level
438 , p_restock => 2
439 , p_include_nonnet => p_include_nonnet
440 , p_include_po => p_include_po
441 , p_include_wip => p_include_wip
442 , p_include_if => p_include_if
443 , p_net_rsv => p_net_rsv
444 , p_net_unrsv => p_net_unrsv
445 , p_net_wip => p_net_wip
446 , p_org_id => p_organization_id
447 , p_user_id => l_user_id
448 , p_employee_id => l_employee_id
449 , p_subinv => p_subinventory_code
450 , p_dd_loc_id => p_dd_loc_id
451 , p_wip_batch_id => l_wip_batch_id
452 , p_approval => to_number(nvl(FND_PROFILE.VALUE('INV_MINMAX_REORDER_APPROVED'),'1'))
453 , p_buyer_hi => p_buyer_hi
454 , p_buyer_lo => p_buyer_lo
455 , p_range_buyer => l_range_buyer
456 , p_cust_id => l_cust_id
457 , p_po_org_id => l_po_org_id
458 , p_range_sql => l_range_Sql
459 , p_sort => p_sort
460 , p_selection => 2 -- items above maximum quantity
461 , p_sysdate => l_today
462 , p_s_cutoff => l_s_cutoff
463 , p_d_cutoff => l_d_cutoff
464 , p_order_by => l_order_by
465 , p_encum_flag => l_encum_flag
466 , p_cal_code => l_cal_code
467 , p_exception_set_id => l_exception_set_id
468 , x_return_status => l_Return_status
469 , x_msg_data => l_msg_data);
470
471 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
472 RAISE FND_API.G_EXC_ERROR;
473 END IF;
474
475 --ORGANIZATION Level
476 --if l_level = 1 then
477
478 For INV_REC IN INV_MIN_MAX_TEMP LOOP
479
480 Begin
481 SELECT msik.inventory_item_id
482 INTO l_item_id
483 FROM mtl_system_items_kfv msik
484 WHERE msik.concatenated_segments = inv_rec.item_segments
485 AND msik.organization_id = p_organization_id;
486 Exception
487 When no_data_found then
488 l_item_id := Null;
489 End;
490
491 if p_called_from = 'PART_STATUS' then
492 insert into csp_sup_dem_sub_temp(
493 inventory_item_id,
494 organization_id,
495 subinventory_code,
496 planning_parameters_id,
497 level_id,
498 parts_loop_id,
499 hierarchy_node_id,
500 excess_quantity)
501 values(
502 l_item_id,
503 p_organization_id,
504 p_subinventory_code,
505 rec.planning_parameters_id,
506 rec.level_id,
507 rec.parts_loop_id,
508 rec.hierarchy_node_id,
509 NVL(inv_rec.TOT_AVAIL_QTY,0) - NVL(inv_rec.SUPPLY_QTY,0) - NVL(inv_rec.MAX_QTY,0));
510 else
511 x_excess_line_id := null;
512 csp_excess_lists_pkg.Insert_Row(
513 px_EXCESS_LINE_ID => x_excess_line_id,
514 p_CREATED_BY => fnd_global.user_id,
515 p_CREATION_DATE => sysdate,
516 p_LAST_UPDATED_BY => fnd_global.user_id,
517 p_LAST_UPDATE_DATE => sysdate,
518 p_LAST_UPDATE_LOGIN => null,
519 p_ORGANIZATION_ID => p_organization_id,
520 p_SUBINVENTORY_CODE => p_subinventory_code,
521 p_CONDITION_CODE => 'G',
522 p_INVENTORY_ITEM_ID => l_item_id,
523 p_EXCESS_QUANTITY => NVL(inv_rec.TOT_AVAIL_QTY,0) - NVL(inv_rec.SUPPLY_QTY,0) - NVL(inv_rec.MAX_QTY,0),
524 p_EXCESS_STATUS => 'P',
525 p_REQUISITION_LINE_ID => null,
526 p_RETURNED_QUANTITY => null,
527 p_current_return_qty => null,
528 p_ATTRIBUTE_CATEGORY => null,
529 p_ATTRIBUTE1 => null,
530 p_ATTRIBUTE2 => null,
531 p_ATTRIBUTE3 => null,
532 p_ATTRIBUTE4 => null,
533 p_ATTRIBUTE5 => null,
534 p_ATTRIBUTE6 => null,
535 p_ATTRIBUTE7 => null,
536 p_ATTRIBUTE8 => null,
537 p_ATTRIBUTE9 => null,
538 p_ATTRIBUTE10 => null,
539 p_ATTRIBUTE11 => null,
540 p_ATTRIBUTE12 => null,
541 p_ATTRIBUTE13 => null,
542 p_ATTRIBUTE14 => null,
543 p_ATTRIBUTE15 => null);
544 end if;
545 End loop;
546
547
548 --ORGANIZATION Level
549
550 if l_level = 1 then
551 for coi in c_org_items loop
552 l_total_onhand := 0;
553 l_onhand := 0;
554 l_demand := 0;
555 l_excess := 0;
556
557 --for cos in c_org_subinventories(coi.inventory_item_id) loop
558 l_onhand := csp_excess_parts_pvt.onhand(
559 p_organization_id => p_organization_id,
560 p_inventory_item_id => coi.inventory_item_id,
561 --p_subinventory_code => cos.subinventory_code,
562 p_subinventory_code => NULL,
563 p_revision_qty_control_code => coi.revision_qty_control_code,
564 p_include_nonnet => p_include_nonnet,
565 p_planning_level => l_level);
566
567 l_total_onhand := l_total_onhand + l_onhand;
568 --end loop;
569
570 l_demand := csp_excess_parts_pvt.demand(
571 p_organization_id => p_organization_id,
572 p_inventory_item_id => coi.inventory_item_id,
573 p_subinventory_code => null,
574 p_include_nonnet => p_include_nonnet,
575 p_planning_level => l_level,
576 p_net_unreserved => p_net_unrsv,
577 p_net_reserved => p_net_rsv,
578 p_net_wip => p_net_wip,
579 p_demand_cutoff => P_D_CUTOFF_REL); -- number of days
580
581 l_excess := nvl(l_total_onhand,0) - nvl(l_demand,0);
582
583 if l_excess > 0 then
584 if p_called_from = 'PART_STATUS' then
585 insert into csp_sup_dem_sub_temp(
586 inventory_item_id,
587 organization_id,
588 subinventory_code,
589 planning_parameters_id,
590 level_id,
591 parts_loop_id,
592 hierarchy_node_id,
593 excess_quantity)
594 values(
595 coi.inventory_item_id,
596 p_organization_id,
597 null,
598 rec.planning_parameters_id,
599 rec.level_id,
600 rec.parts_loop_id,
601 rec.hierarchy_node_id,
602 l_excess);
603 else
604 x_excess_line_id := null;
605 csp_excess_lists_pkg.Insert_Row(
606 px_EXCESS_LINE_ID => x_excess_line_id,
607 p_CREATED_BY => fnd_global.user_id,
608 p_CREATION_DATE => sysdate,
609 p_LAST_UPDATED_BY => fnd_global.user_id,
610 p_LAST_UPDATE_DATE => sysdate,
611 p_LAST_UPDATE_LOGIN => null,
612 p_ORGANIZATION_ID => p_organization_id,
613 p_SUBINVENTORY_CODE => null,
614 p_CONDITION_CODE => 'G',
615 p_INVENTORY_ITEM_ID => coi.inventory_item_id,
616 p_EXCESS_QUANTITY => l_excess,
617 p_EXCESS_STATUS => 'P',
618 p_REQUISITION_LINE_ID => null,
619 p_RETURNED_QUANTITY => null,
620 p_current_return_qty => null,
621 p_ATTRIBUTE_CATEGORY => null,
622 p_ATTRIBUTE1 => null,
623 p_ATTRIBUTE2 => null,
624 p_ATTRIBUTE3 => null,
625 p_ATTRIBUTE4 => null,
626 p_ATTRIBUTE5 => null,
627 p_ATTRIBUTE6 => null,
628 p_ATTRIBUTE7 => null,
629 p_ATTRIBUTE8 => null,
630 p_ATTRIBUTE9 => null,
631 p_ATTRIBUTE10 => null,
632 p_ATTRIBUTE11 => null,
633 p_ATTRIBUTE12 => null,
634 p_ATTRIBUTE13 => null,
635 p_ATTRIBUTE14 => null,
636 p_ATTRIBUTE15 => null);
637 end if;
638 end if;
639
640 end loop;
641 end if;
642
643 -- SUBINVENTORY Level
644 if l_level = 2 then
645 for curs in c_subinventories loop
646 for csin in c_sub_items(curs.secondary_inventory_name) loop
647 l_onhand := csp_excess_parts_pvt.onhand(
648 p_organization_id => p_organization_id,
649 p_inventory_item_id => csin.inventory_item_id,
650 p_subinventory_code => curs.secondary_inventory_name,
651 p_revision_qty_control_code => csin.revision_qty_control_code,
652 p_include_nonnet => p_include_nonnet,
653 p_planning_level => l_level);
654
655 l_demand := csp_excess_parts_pvt.demand(
656 p_organization_id => p_organization_id,
657 p_inventory_item_id => csin.inventory_item_id,
658 p_subinventory_code => curs.secondary_inventory_name,
659 p_include_nonnet => p_include_nonnet,
660 p_planning_level => l_level,
661 p_net_unreserved => p_net_unrsv,
662 p_net_reserved => p_net_rsv,
663 p_net_wip => p_net_wip,
664 p_demand_cutoff => P_D_CUTOFF_REL);
665
666 l_excess := nvl(l_onhand,0) - nvl(l_demand,0);
667
668 if nvl(l_excess,0) > 0 then
669 if p_called_from = 'PART_STATUS' then
670 insert into csp_sup_dem_sub_temp(
671 inventory_item_id,
672 organization_id,
673 subinventory_code,
674 planning_parameters_id,
675 level_id,
676 parts_loop_id,
677 hierarchy_node_id,
678 excess_quantity)
679 values(
680 csin.inventory_item_id,
681 p_organization_id,
682 curs.secondary_inventory_name,
683 rec.planning_parameters_id,
684 rec.level_id,
685 rec.parts_loop_id,
686 rec.hierarchy_node_id,
687 l_excess);
688 else
689 x_excess_line_id := null;
690 csp_excess_lists_pkg.Insert_Row(
691 px_EXCESS_LINE_ID => x_excess_line_id,
692 p_CREATED_BY => fnd_global.user_id,
693 p_CREATION_DATE => sysdate,
694 p_LAST_UPDATED_BY => fnd_global.user_id,
695 p_LAST_UPDATE_DATE => sysdate,
696 p_LAST_UPDATE_LOGIN => null,
697 p_ORGANIZATION_ID => p_organization_id,
698 p_SUBINVENTORY_CODE => curs.secondary_inventory_name,
699 p_CONDITION_CODE => 'G',
700 p_INVENTORY_ITEM_ID => csin.inventory_item_id,
701 p_EXCESS_QUANTITY => l_excess,
702 p_EXCESS_STATUS => 'P',
703 p_REQUISITION_LINE_ID => null,
704 p_RETURNED_QUANTITY => null,
705 p_current_return_qty => null,
706 p_ATTRIBUTE_CATEGORY => null,
707 p_ATTRIBUTE1 => null,
708 p_ATTRIBUTE2 => null,
709 p_ATTRIBUTE3 => null,
710 p_ATTRIBUTE4 => null,
711 p_ATTRIBUTE5 => null,
712 p_ATTRIBUTE6 => null,
713 p_ATTRIBUTE7 => null,
714 p_ATTRIBUTE8 => null,
715 p_ATTRIBUTE9 => null,
716 p_ATTRIBUTE10 => null,
717 p_ATTRIBUTE11 => null,
718 p_ATTRIBUTE12 => null,
719 p_ATTRIBUTE13 => null,
720 p_ATTRIBUTE14 => null,
721 p_ATTRIBUTE15 => null);
722 end if;
723 end if;
724
725 end loop;
726 end loop;
727 end if;
728 if p_called_from <> 'PART_STATUS' then
729 l_excess_rule_id := get_business_rule(
730 p_organization_id => p_organization_id,
731 p_subinventory_code => p_subinventory_code);
732
733 if l_excess_rule_id is not null then
734 csp_excess_parts_pvt.apply_business_rules(
735 p_organization_id => p_organization_id,
736 p_subinventory_code => p_subinventory_code,
737 p_excess_rule_id => l_excess_rule_id);
738 else
739 update csp_excess_lists
740 set excess_status = 'O'
741 where excess_status = 'P';
742 commit;
743 end if;
744 end if;
745 end if;
746 end if;
747 End if;
748 Delete from INV_MIN_MAX_TEMP;
749 End loop;
750 end;
751
752 procedure apply_business_rules(
753 p_organization_id number,
754 p_subinventory_code varchar2,
755 p_excess_rule_id number) as
756
757
758 cursor business_rule is
759 select cerb.excess_rule_id,
760 cerb.total_max_excess,
761 cerb.line_max_excess,
762 cerb.total_excess_value,
763 cerb.days_since_receipt,
764 cerb.top_excess_lines,
765 cerb.category_set_id,
766 cerb.category_id
767 from csp_excess_rules_b cerb
768 where excess_rule_id = p_excess_rule_id;
769
770 br_rec business_rule%rowtype;
771
772 cursor excess_value is
773 select sum(cel.excess_quantity * NVL(ITEM_COST,0))
774 from CST_ITEM_COSTS cic,
775 CST_COST_TYPES cct,
776 csp_excess_lists cel
777 where cic.ORGANIZATION_ID = cel.organization_id
778 and cic.inventory_item_id = cel.inventory_item_id
779 and cic.COST_TYPE_ID = cct.COST_TYPE_ID
780 and cct.COST_TYPE_ID = cct.DEFAULT_COST_TYPE_ID
781 and cel.excess_status = 'P';
782
783 cursor org_max_value is
784 select sum(msib.max_minmax_quantity * NVL(ITEM_COST,0))
785 from CST_ITEM_COSTS cic,
786 CST_COST_TYPES cct,
787 mtl_system_items_b msib
788 where msib.organization_id = p_organization_id
789 and cic.ORGANIZATION_ID = msib.organization_id
790 and cic.inventory_item_id = msib.inventory_item_id
791 and cic.COST_TYPE_ID = cct.COST_TYPE_ID
792 and cct.COST_TYPE_ID = cct.DEFAULT_COST_TYPE_ID
793 and msib.max_minmax_quantity > 0;
794
795 cursor sub_max_value is
796 select sum(misi.max_minmax_quantity * nvl(cic.item_cost,0))
797 from cst_item_costs cic,
798 cst_cost_types cct,
799 mtl_item_sub_inventories misi
800 where misi.organization_id = p_organization_id
801 and misi.secondary_inventory = p_subinventory_code
802 and cic.organization_id = misi.organization_id
803 and cic.inventory_item_id = misi.inventory_item_id
804 and cic.cost_type_id = cct.cost_type_id
805 and cct.cost_type_id = cct.default_cost_type_id
806 and misi.max_minmax_quantity > 0;
807
808 cursor org_line_quantity is
809 select cel.excess_line_id,
810 cel.excess_quantity,
811 msib.max_minmax_quantity
812 from csp_excess_lists cel,
813 mtl_system_items_b msib
814 where cel.organization_id = p_organization_id
815 and cel.organization_id = msib.organization_id
816 and cel.inventory_item_id = msib.inventory_item_id
817 and cel.excess_status = 'P';
818
819 cursor sub_line_quantity is
820 select cel.excess_line_id,
821 cel.excess_quantity,
822 misi.max_minmax_quantity
823 from csp_excess_lists cel,
824 mtl_item_sub_inventories misi
825 where cel.organization_id = p_organization_id
826 and cel.organization_id = misi.organization_id
827 and cel.subinventory_code = misi.secondary_inventory
828 and cel.inventory_item_id = misi.inventory_item_id
829 and cel.excess_status = 'P';
830
831 cursor org_recently_received(p_inventory_item_id number) is
832 select mmt.transaction_date
833 from mtl_material_transactions mmt
834 where mmt.organization_id = p_organization_id
835 and mmt.inventory_item_id = p_inventory_item_id
836 and mmt.transaction_action_id in (2,3,12,27,31)
837 and mmt.transaction_quantity > 0
838 order by transaction_id desc;
839
840 cursor sub_recently_received(p_inventory_item_id number) is
841 select mmt.transaction_date
842 from mtl_material_transactions mmt
843 where mmt.organization_id = p_organization_id
844 and mmt.subinventory_code = p_subinventory_code
845 and mmt.inventory_item_id = p_inventory_item_id
846 and mmt.transaction_action_id in (2,3,12,27,31)
847 and mmt.transaction_quantity > 0
848 order by transaction_id desc;
849
850 cursor excess_lines is
851 select cel.excess_line_id,
852 cel.inventory_item_id
853 from csp_excess_lists cel
854 where cel.organization_id = p_organization_id
855 and cel.excess_status = 'P';
856
857 cursor excess_line_value is
858 select cel.excess_line_id,
859 cel.excess_quantity * NVL(ITEM_COST,0) value
860 from CST_ITEM_COSTS cic,
861 CST_COST_TYPES cct,
862 csp_excess_lists cel
863 where cel.organization_id = p_organization_id
864 and cic.ORGANIZATION_ID = cel.organization_id
865 and cic.inventory_item_id = cel.inventory_item_id
866 and cic.COST_TYPE_ID = cct.COST_TYPE_ID
867 and cct.COST_TYPE_ID = cct.DEFAULT_COST_TYPE_ID
868 and cel.excess_status = 'P'
869 order by value desc;
870
871
872 l_excess_value number;
873 l_max_value number;
874 l_excess_percentage number;
875 l_line_quantity number;
876 l_received_date date;
877 l_days_since_receipt number;
878 l_counter number;
879 l_value number;
880
881
882 begin
883 -- Fetch business rules
884 open business_rule;
885 fetch business_rule into br_rec;
886 close business_rule;
887
888 if br_rec.category_set_id is not null then
889 delete from csp_excess_lists cel
890 where cel.excess_status = 'P'
891 and cel.inventory_item_id in
892 (select inventory_item_id
893 from mtl_item_categories
894 where category_set_id = br_rec.category_set_id
895 and category_id = nvl(br_rec.category_id,category_id)
896 and organization_id = cel.organization_id);
897 end if;
898
899
900 -- % OF MAX VALUE
901 if br_rec.total_max_excess is not null then
902 open excess_value;
903 fetch excess_value into l_excess_value;
904 close excess_value;
905
906 if p_subinventory_code is null then
907 open org_max_value;
908 fetch org_max_value into l_max_value;
909 close org_max_value;
910 else
911 open sub_max_value;
912 fetch sub_max_value into l_max_value;
913 close sub_max_value;
914 end if;
915
916 if nvl(l_max_value,0) > 0 then
917 l_excess_percentage := nvl(l_excess_value,0) / l_max_value * 100;
918
919 if l_excess_percentage < br_rec.total_max_excess then
920 delete from csp_excess_lists
921 where excess_status = 'P';
922 null; --exit;
923 end if;
924 end if;
925 end if;
926
927 -- % of line quantity PL dependent
928 if br_rec.line_max_excess is not null then
929 if p_subinventory_code is null then
930 for olq in org_line_quantity loop
931 -- Avoid divisor equal to zero
932 if nvl(olq.max_minmax_quantity,0) <> 0 then
933 l_line_quantity := nvl(olq.excess_quantity,0) / nvl(olq.max_minmax_quantity,1) * 100;
934 if l_line_quantity < nvl(br_rec.line_max_excess,0) then
935 delete from csp_excess_lists
936 where excess_line_id = olq.excess_line_id;
937 end if;
938 end if;
939 end loop;
940 else
941 for slq in sub_line_quantity loop
942 -- Avoid divisor equal to zero
943 if nvl(slq.max_minmax_quantity,0) <> 0 then
944 l_line_quantity := nvl(slq.excess_quantity,0) / nvl(slq.max_minmax_quantity,1) * 100;
945 if l_line_quantity < nvl(br_rec.line_max_excess,0) then
946 delete from csp_excess_lists
947 where excess_line_id = slq.excess_line_id;
948 end if;
949 end if;
950 end loop;
951 end if;
952 end if;
953
954 -- Recently Received PL dependent
955 if br_rec.days_since_receipt is not null then
956 for el in excess_lines loop
957 if p_subinventory_code is null then
958 open org_recently_received(el.inventory_item_id);
959 fetch org_recently_received into l_received_date;
960 close org_recently_received;
961 l_days_since_receipt := sysdate - nvl(l_received_date,sysdate - br_rec.days_since_receipt);
962 if l_days_since_receipt < br_rec.days_since_receipt then
963 delete from csp_excess_lists
964 where excess_line_id = el.excess_line_id;
965 end if;
966 else
967 open sub_recently_received(el.inventory_item_id);
968 fetch sub_recently_received into l_received_date;
969 close sub_recently_received;
970 l_days_since_receipt := sysdate - nvl(l_received_date,sysdate - br_rec.days_since_receipt);
971 if l_days_since_receipt < br_rec.days_since_receipt then
972 delete from csp_excess_lists
973 where excess_line_id = el.excess_line_id;
974 end if;
975 end if;
976 end loop;
977 end if;
978
979 -- % of the total excess value
980 if nvl(br_rec.total_excess_value,0) > 0 then
981 open excess_value;
982 fetch excess_value into l_excess_value;
983 close excess_value;
984
985 if nvl(l_excess_value,0) > 0 then
986 l_excess_percentage := 0;
987 l_value := 0;
988 for elv in excess_line_value loop
989 if l_excess_percentage > br_rec.total_excess_value then
990 delete from csp_excess_lists
991 where excess_line_id = elv.excess_line_id;
992 end if;
993 l_value := l_value + nvl(elv.value,0);
994 l_excess_percentage := l_value / l_excess_value * 100;
995 end loop;
996 end if;
997 end if;
998
999 -- Top X list
1000 if nvl(br_rec.top_excess_lines,0) > 0 then
1001 l_counter := 0;
1002 for elv in excess_line_value loop
1003 l_counter := l_counter + 1;
1004 if l_counter <= br_rec.top_excess_lines then
1005 update csp_excess_lists
1006 set excess_status = 'O'
1007 where excess_line_id = elv.excess_line_id;
1008 else
1009 exit;
1010 end if;
1011 end loop;
1012 delete from csp_excess_lists
1013 where excess_status = 'P';
1014 end if;
1015 -- Remaining excess lines will be comitted
1016 update csp_excess_lists
1017 set excess_status = 'O'
1018 where excess_status = 'P';
1019 commit;
1020 end apply_business_rules;
1021
1022 procedure defective_return(
1023 p_organization_id number,
1024 p_subinventory_code varchar2,
1025 p_planning_parameters_id number,
1026 p_level_id varchar2,
1027 p_parts_loop_id number,
1028 p_hierarchy_node_id number,
1029 p_called_from varchar2) is
1030
1031 cursor defectives is
1032 select mosv.organization_id,
1033 mosv.subinventory_code,
1034 mosv.inventory_item_id,
1035 total_qoh excess_quantity
1036 from mtl_onhand_sub_v mosv,
1037 csp_sec_inventories csin
1038 where mosv.organization_id = p_organization_id
1039 and csin.organization_id = mosv.organization_id
1040 and csin.secondary_inventory_name = mosv.subinventory_code
1041 and csin.condition_type = 'B'
1042 and csin.secondary_inventory_name = nvl(p_subinventory_code,csin.secondary_inventory_name)
1043 and total_qoh > 0;
1044
1045 x_excess_line_id number;
1046
1047 begin
1048
1049 for d in defectives loop
1050 if p_called_from = 'PART_STATUS' then
1051 insert into csp_sup_dem_sub_temp(
1052 inventory_item_id,
1053 organization_id,
1054 subinventory_code,
1055 planning_parameters_id,
1056 level_id,
1057 parts_loop_id,
1058 hierarchy_node_id,
1059 excess_quantity)
1060 values(
1061 d.inventory_item_id,
1062 d.organization_id,
1063 d.subinventory_code,
1064 p_planning_parameters_id,
1065 p_level_id,
1066 p_parts_loop_id,
1067 p_hierarchy_node_id,
1068 d.excess_quantity);
1069 else
1070 x_excess_line_id := null;
1071 csp_excess_lists_pkg.Insert_Row(
1072 px_EXCESS_LINE_ID => x_excess_line_id,
1073 p_CREATED_BY => fnd_global.user_id,
1074 p_CREATION_DATE => sysdate,
1075 p_LAST_UPDATED_BY => fnd_global.user_id,
1076 p_LAST_UPDATE_DATE => sysdate,
1077 p_LAST_UPDATE_LOGIN => null,
1078 p_ORGANIZATION_ID => d.organization_id,
1079 p_SUBINVENTORY_CODE => d.subinventory_code,
1080 p_CONDITION_CODE => 'B',
1081 p_INVENTORY_ITEM_ID => d.inventory_item_id,
1082 p_EXCESS_QUANTITY => d.excess_quantity,
1083 p_EXCESS_STATUS => 'O',
1084 p_REQUISITION_LINE_ID => null,
1085 p_RETURNED_QUANTITY => null,
1086 p_current_return_qty => null,
1087 p_ATTRIBUTE_CATEGORY => null,
1088 p_ATTRIBUTE1 => null,
1089 p_ATTRIBUTE2 => null,
1090 p_ATTRIBUTE3 => null,
1091 p_ATTRIBUTE4 => null,
1092 p_ATTRIBUTE5 => null,
1093 p_ATTRIBUTE6 => null,
1094 p_ATTRIBUTE7 => null,
1095 p_ATTRIBUTE8 => null,
1096 p_ATTRIBUTE9 => null,
1097 p_ATTRIBUTE10 => null,
1098 p_ATTRIBUTE11 => null,
1099 p_ATTRIBUTE12 => null,
1100 p_ATTRIBUTE13 => null,
1101 p_ATTRIBUTE14 => null,
1102 p_ATTRIBUTE15 => null);
1103 end if;
1104 end loop;
1105 commit;
1106 exception
1107 when others then
1108 null;
1109 end;
1110
1111 procedure clean_up(
1112 p_organization_id number,
1113 p_subinventory_code varchar2,
1114 p_condition_type varchar2) is
1115 begin
1116 if p_subinventory_code is null then
1117 delete from csp_excess_lists
1118 where organization_id = p_organization_id
1119 and condition_code = nvl(p_condition_type,condition_code)
1120 and excess_status = 'O';
1121 else
1122 delete from csp_excess_lists
1123 where organization_id = p_organization_id
1124 and subinventory_code = nvl(p_subinventory_code,subinventory_code)
1125 and condition_code = nvl(p_condition_type,condition_code)
1126 and excess_status = 'O';
1127 end if;
1128 exception
1129 when no_data_found then
1130 null;
1131 when others then
1132 null;
1133 end;
1134
1135 PROCEDURE Build_Item_Cat_Select(p_Cat_structure_id IN NUMBER
1136 ,x_item_select OUT NOCOPY VARCHAR2
1137 ,x_cat_Select OUT NOCOPY VARCHAR2
1138 ) IS
1139 l_flexfield_rec FND_FLEX_KEY_API.flexfield_type;
1140 l_structure_rec FND_FLEX_KEY_API.structure_type;
1141 l_segment_rec FND_FLEX_KEY_API.segment_type;
1142 l_segment_tbl FND_FLEX_KEY_API.segment_list;
1143 l_segment_number NUMBER;
1144 l_mstk_segs VARCHAR2(850);
1145 l_mcat_segs VARCHAR2(850);
1146 BEGIN
1147 FND_FLEX_KEY_API.set_session_mode('customer_data');
1148
1149 -- retrieve system item concatenated flexfield
1150 l_mstk_segs := '';
1151 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MSTK');
1152 l_structure_rec := FND_FLEX_KEY_API.find_structure(l_flexfield_rec, 101);
1153 FND_FLEX_KEY_API.get_segments
1154 ( flexfield => l_flexfield_rec
1155 , structure => l_structure_rec
1156 , nsegments => l_segment_number
1157 , segments => l_segment_tbl
1158 );
1159 FOR l_idx IN 1..l_segment_number LOOP
1160 l_segment_rec := FND_FLEX_KEY_API.find_segment
1161 ( l_flexfield_rec
1162 , l_structure_rec
1163 , l_segment_tbl(l_idx)
1164 );
1165 l_mstk_segs := l_mstk_segs ||'C.'||l_segment_rec.column_name;
1166 IF l_idx < l_segment_number THEN
1167 l_mstk_segs := l_mstk_segs||'||'||l_structure_rec.segment_separator;
1168 END IF;
1169 END LOOP;
1170
1171 -- retrieve item category concatenated flexfield
1172 l_mcat_segs := '';
1173 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MCAT');
1174 l_structure_rec := FND_FLEX_KEY_API.find_structure
1175 ( l_flexfield_rec
1176 , p_cat_structure_id
1177 );
1178 FND_FLEX_KEY_API.get_segments
1179 ( flexfield => l_flexfield_rec
1180 , structure => l_structure_rec
1181 , nsegments => l_segment_number
1182 , segments => l_segment_tbl
1183 );
1184 FOR l_idx IN 1..l_segment_number LOOP
1185 l_segment_rec := FND_FLEX_KEY_API.find_segment
1186 ( l_flexfield_rec
1187 , l_structure_rec
1188 , l_segment_tbl(l_idx)
1189 );
1190 l_mcat_segs := l_mcat_segs ||'B.'||l_segment_rec.column_name;
1191 IF l_idx < l_segment_number THEN
1192 l_mcat_segs := l_mcat_segs||'||'||''''||
1193 l_structure_rec.segment_separator||''''||'||';
1194 END IF;
1195 END LOOP;
1196
1197 x_item_select := '('||l_mstk_Segs||')';
1198 x_cat_select := '('||l_mcat_Segs||')';
1199 END;
1200
1201 PROCEDURE Build_Range_Sql
1202 ( p_cat_structure_id IN NUMBER
1203 , p_cat_lo IN VARCHAR2
1204 , p_cat_hi IN VARCHAR2
1205 , p_item_lo IN VARCHAR2
1206 , p_item_hi IN VARCHAR2
1207 , p_planner_lo IN VARCHAR2
1208 , p_planner_hi IN VARCHAR2
1209 , p_lot_ctl IN NUMBER
1210 , x_range_sql OUT NOCOPY VARCHAR2
1211 )
1212 IS
1213 l_flexfield_rec FND_FLEX_KEY_API.flexfield_type;
1214 l_structure_rec FND_FLEX_KEY_API.structure_type;
1215 l_segment_rec FND_FLEX_KEY_API.segment_type;
1216 l_segment_tbl FND_FLEX_KEY_API.segment_list;
1217 l_segment_number NUMBER;
1218 l_mstk_segs VARCHAR2(850);
1219 l_mcat_segs VARCHAR2(850);
1220 --l_mcat_w VARCHAR2(2000);
1221 --l_mstk_w VARCHAR2(2000);
1222 l_range_sql VARCHAr2(2000);
1223 lx_range_sql VARCHAR2(4000) := '1=1';
1224 BEGIN
1225
1226 FND_FLEX_KEY_API.set_session_mode('customer_data');
1227
1228 -- retrieve system item concatenated flexfield
1229 l_mstk_segs := '';
1230 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MSTK');
1231 l_structure_rec := FND_FLEX_KEY_API.find_structure(l_flexfield_rec, 101);
1232 FND_FLEX_KEY_API.get_segments
1233 ( flexfield => l_flexfield_rec
1234 , structure => l_structure_rec
1235 , nsegments => l_segment_number
1236 , segments => l_segment_tbl
1237 );
1238 FOR l_idx IN 1..l_segment_number LOOP
1239 l_segment_rec := FND_FLEX_KEY_API.find_segment
1240 ( l_flexfield_rec
1241 , l_structure_rec
1242 , l_segment_tbl(l_idx)
1243 );
1244 l_mstk_segs := l_mstk_segs ||'C.'||l_segment_rec.column_name;
1245 IF l_idx < l_segment_number THEN
1246 l_mstk_segs := l_mstk_segs||'||'||l_structure_rec.segment_separator;
1247 END IF;
1248 END LOOP;
1249
1250 -- retrieve item category concatenated flexfield
1251 l_mcat_segs := '';
1252 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MCAT');
1253 l_structure_rec := FND_FLEX_KEY_API.find_structure
1254 ( l_flexfield_rec
1255 , p_cat_structure_id
1256 );
1257 FND_FLEX_KEY_API.get_segments
1258 ( flexfield => l_flexfield_rec
1259 , structure => l_structure_rec
1260 , nsegments => l_segment_number
1261 , segments => l_segment_tbl
1262 );
1263 FOR l_idx IN 1..l_segment_number LOOP
1264 l_segment_rec := FND_FLEX_KEY_API.find_segment
1265 ( l_flexfield_rec
1266 , l_structure_rec
1267 , l_segment_tbl(l_idx)
1268 );
1269 l_mcat_segs := l_mcat_segs ||'B.'||l_segment_rec.column_name;
1270 IF l_idx < l_segment_number THEN
1271 l_mcat_segs := l_mcat_segs||'||'||''''||
1272 l_structure_rec.segment_separator||''''||'||';
1273 END IF;
1274 END LOOP;
1275
1276 IF p_item_lo IS NOT NULL AND p_item_hi IS NOT NULL THEN
1277 l_range_sql := l_mstk_segs||' BETWEEN '''||p_item_lo||''''||
1278 ' AND '''||p_item_hi||'''';
1279 ELSIF p_item_lo IS NOT NULL AND p_item_hi IS NULL THEN
1280 l_range_sql := l_mstk_segs||' >= '''||p_item_lo||'''';
1281 ELSIF p_item_lo IS NULL AND p_item_hi IS NOT NULL THEN
1282 l_range_sql := l_mstk_segs||' <= '''||p_item_hi||'''';
1283 END IF;
1284
1285 IF (l_range_sql is not null) THEN
1286 lx_range_sql := l_range_sql;
1287 l_range_sql := null;
1288 END IF;
1289
1290 IF p_cat_lo IS NOT NULL AND p_cat_hi IS NOT NULL THEN
1291 l_range_sql := l_mcat_segs||' BETWEEN '''||p_cat_lo||''''||
1292 ' AND '''||p_cat_hi||'''';
1293 ELSIF p_cat_lo IS NOT NULL AND p_cat_hi IS NULL THEN
1294 l_range_Sql := l_mcat_segs||' >= '''||p_cat_lo||'''';
1295 ELSIF p_cat_lo IS NULL AND p_cat_hi IS NOT NULL THEN
1296 l_range_sql := l_mcat_segs||' <= '''||p_cat_hi||'''';
1297 END IF;
1298
1299 IF (l_range_Sql is not null) THEN
1300 lx_range_sql := lx_Range_sql || ' and' || l_range_Sql;
1301 l_range_sql := null;
1302 END IF;
1303
1304 if p_planner_lo is not null and p_planner_hi is not null then
1305 l_RANGE_SQL := 'c.planner_code between ' ||''''||P_planner_LO||'''' ||
1306 ' and '|| ''''||P_planner_HI||'''';
1307 elsif p_planner_lo is not null then
1308 l_RANGE_SQL := 'c.planner_code >= ' ||''''||P_planner_LO||'''';
1309 elsif p_PLANNER_hi is not null then
1310 l_RANGE_SQL := 'c.planner_code <= ' ||''''||P_PLANNER_HI||'''';
1311 end if;
1312
1313 if l_range_sql is not null then
1314 lx_range_sql := lx_range_sql||' and '|| l_range_sql;
1315 l_range_sql := null;
1316 end if;
1317
1318 if P_LOT_CTL = 1 then
1319 l_RANGE_SQL := 'c.lot_control_code = 2';
1320 elsif P_LOT_CTL = 2 then
1321 l_RANGE_SQL := 'c.lot_control_code <> 2';
1322 end if;
1323
1324 if l_range_sql is not null then
1325 lx_range_sql := lx_range_sql||' and '|| l_range_sql;
1326 l_range_sql := null;
1327 end if;
1328
1329 x_range_Sql := lx_range_sql;
1330 END;
1331
1332 FUNCTION get_business_rule(
1333 p_organization_id IN NUMBER,
1334 p_subinventory_code IN VARCHAR2)
1335 return number is
1336
1337 l_excess_rule_id number;
1338
1339 cursor subinventory_br is
1340 select cpp.excess_rule_id
1341 from csp_planning_parameters cpp
1342 where cpp.organization_id = p_organization_id
1343 and cpp.secondary_inventory = p_subinventory_code;
1344
1345 cursor organization_br is
1346 select cpp.excess_rule_id
1347 from csp_planning_parameters cpp
1348 where cpp.organization_id = p_organization_id
1349 and cpp.secondary_inventory is null;
1350
1351 begin
1352
1353 If p_organization_id is not null and p_subinventory_code is not null then
1354 open subinventory_br;
1355 fetch subinventory_br into l_excess_rule_id;
1356 close subinventory_br;
1357 elsif p_organization_id is not null and p_subinventory_code is null then
1358 open organization_br;
1359 fetch organization_br into l_excess_rule_id;
1360 close organization_br;
1361 end if;
1362 return(l_excess_rule_id);
1363 end;
1364
1365 FUNCTION onhand
1366 ( p_organization_id IN NUMBER,
1367 p_inventory_item_id IN NUMBER,
1368 p_subinventory_code IN VARCHAR2,
1369 p_revision_qty_control_code IN NUMBER,
1370 p_include_nonnet IN NUMBER,
1371 p_planning_level IN NUMBER
1372 )
1373 return number is
1374
1375 x_return_status VARCHAR2(1);
1376 x_msg_count NUMBER;
1377 x_msg_data VARCHAR2(2000);
1378 l_onhand_source NUMBER := 3;
1379 l_subinventory_code VARCHAR2(30);
1380 l_qoh NUMBER;
1381 l_rqoh NUMBER;
1382 l_qr NUMBER;
1383 l_qs NUMBER;
1384 l_att NUMBER;
1385 l_atr NUMBER;
1386 l_total_qoh NUMBER := null;
1387
1388 cursor revisions is
1389 select revision
1390 from mtl_item_revisions
1391 where organization_id = p_organization_id
1392 and inventory_item_id = p_inventory_item_id;
1393
1394 BEGIN
1395
1396 IF (p_include_nonnet = 2) THEN
1397 l_onhand_source := 2;
1398 END IF;
1399
1400 if p_revision_qty_control_code = 2 then -- Revision control
1401
1402 for r in revisions loop
1403 inv_quantity_tree_pub.query_quantities
1404 ( p_api_version_number => 1.0
1405 , p_organization_id => p_organization_id
1406 , p_inventory_item_id => p_inventory_item_id
1407 , p_subinventory_code => p_subinventory_code
1408 , x_qoh => l_qoh
1409 , x_atr => l_atr
1410 , p_init_msg_lst => fnd_api.g_false
1411 , p_tree_mode => inv_quantity_tree_pvt.g_transaction_mode
1412 , p_is_revision_control => TRUE
1413 , p_is_lot_control => NULL
1414 , p_is_serial_control => NULL
1415 , p_revision => r.revision
1416 , p_lot_number => NULL
1417 , p_locator_id => NULL
1418 , x_rqoh => l_rqoh
1419 , x_qr => l_qr
1420 , x_qs => l_qs
1421 , x_att => l_att
1422 , x_return_status => x_return_status
1423 , x_msg_count => x_msg_count
1424 , x_msg_data => x_msg_data
1425 );
1426
1427 -- qoh quantity on hand
1428 -- rqoh reservable quantity on hand
1429 -- qr quantity reserved
1430 -- att available to transact
1431 -- atr available to reserve
1432
1433 --l_total_qoh := nvl(l_total_qoh,0) + nvl(l_qoh,0);
1434 l_total_qoh := nvl(l_total_qoh,0) + nvl(l_atr,0);
1435 end loop;
1436 return(l_total_qoh);
1437
1438 else -- Not revision controlled item
1439
1440 Inv_quantity_tree_pub.query_quantities
1441 ( p_api_version_number => 1.0
1442 , p_organization_id => p_organization_id
1443 , p_inventory_item_id => p_inventory_item_id
1444 , p_subinventory_code => p_subinventory_code
1445 , x_qoh => l_qoh
1446 , x_atr => l_atr
1447 , p_init_msg_lst => fnd_api.g_false
1448 , p_tree_mode => inv_quantity_tree_pvt.g_transaction_mode
1449 , p_is_revision_control => NULL
1450 , p_is_lot_control => NULL
1451 , p_is_serial_control => NULL
1452 , p_revision => NULL
1453 , p_lot_number => NULL
1454 , p_locator_id => NULL
1455 , x_rqoh => l_rqoh
1456 , x_qr => l_qr
1457 , x_qs => l_qs
1458 , x_att => l_att
1459 , x_return_status => x_return_status
1460 , x_msg_count => x_msg_count
1461 , x_msg_data => x_msg_data
1462 );
1463 if x_return_status = 'S' then
1464 return(l_atr);
1465 else
1466 return(0);
1467 end if;
1468 end if;
1469 end;
1470
1471 function demand(
1472 p_organization_id number,
1473 p_inventory_item_id number,
1474 p_subinventory_code varchar2,
1475 p_include_nonnet number, -- 2
1476 p_planning_level number, -- 2
1477 p_net_unreserved number, -- 1
1478 p_net_reserved number, -- 1
1479 p_net_wip number, -- 1
1480 p_demand_cutoff number) -- number of days
1481 return Number is
1482
1483 qty number;
1484 total number;
1485 l_total_demand_qty number;
1486 l_demand_qty number;
1487 l_total_reserve_qty number;
1488
1489
1490 begin
1491 total := 0;
1492 l_total_demand_qty := 0;
1493 l_demand_qty := 0;
1494 l_total_reserve_qty := 0;
1495
1496 -- select unreserved qty from mtl_demand for non oe rows.
1497 select sum(PRIMARY_UOM_QUANTITY- GREATEST(NVL(RESERVATION_QUANTITY,0),nvl(COMPLETED_QUANTITY,0)))
1498 into qty
1499 from mtl_demand
1500 WHERE RESERVATION_TYPE = 1
1501 AND p_net_unreserved = 1
1502 AND parent_demand_id IS NULL
1503 AND ORGANIZATION_ID = p_organization_id
1504 and PRIMARY_UOM_QUANTITY > GREATEST(NVL(RESERVATION_QUANTITY,0),
1505 nvl(COMPLETED_QUANTITY,0))
1506
1507 and INVENTORY_ITEM_ID = p_inventory_item_id
1508 and REQUIREMENT_DATE <= sysdate + p_demand_cutoff
1509 and demand_source_type not in (2,8,12)
1510 and (p_planning_level = 1 or
1511 SUBINVENTORY = p_subinventory_code) -- Included later for ORG Level
1512 and (SUBINVENTORY is null or
1513 p_planning_level = 2 or
1514 EXISTS (SELECT 1
1515 FROM MTL_SECONDARY_INVENTORIES S
1516 WHERE S.ORGANIZATION_ID = p_organization_id
1517 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY
1518 AND S.availability_type = DECODE(p_include_nonnet,
1519 1,
1520 S.availability_type,
1521 1)));
1522
1523 total := total + nvl(qty,0);
1524
1525
1526 -- select the reserved quantity from mtl_reservations for non OE rows
1527 select sum(PRIMARY_RESERVATION_QUANTITY)
1528 into qty
1529 from mtl_reservations
1530 where p_net_reserved = 1
1531 and ORGANIZATION_ID = p_organization_id
1532 and INVENTORY_ITEM_ID = p_inventory_item_id
1533 and REQUIREMENT_DATE <= sysdate + p_demand_cutoff
1534 and demand_source_type_id not in (2,8,12)
1535 and (p_planning_level = 1 or
1536 SUBINVENTORY_CODE = p_subinventory_code) -- Included later for ORG Level
1537 and (SUBINVENTORY_CODE is null or
1538 p_planning_level = 2 or
1539 EXISTS (SELECT 1
1540 FROM MTL_SECONDARY_INVENTORIES S
1541 WHERE S.ORGANIZATION_ID = p_organization_id
1542 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
1543 AND S.availability_type = DECODE(p_include_nonnet,
1544 1,
1545 S.availability_type,
1546 1)));
1547
1548 total := total + nvl(qty,0);
1549
1550
1551 -- get the total demand which is the difference between the
1552 -- ordered qty. and the shipped qty.
1553 -- This gives the total demand including the reserved
1554 -- and the unreserved material.
1555 if p_net_unreserved = 1 then
1556 select SUM(INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(SHIP_FROM_ORG_ID,
1557 INVENTORY_ITEM_ID, ORDER_QUANTITY_UOM, Nvl(ordered_quantity,0)) -
1558 get_shipped_qty(p_organization_id,p_inventory_item_id, ool.line_id))
1559 into l_total_demand_qty
1560 from oe_order_lines_all ool
1561 where ship_from_org_id = p_organization_id
1562 and open_flag = 'Y'
1563 and INVENTORY_ITEM_ID = p_inventory_item_id
1564 and schedule_ship_date <= sysdate + p_demand_cutoff
1565 AND DECODE(OOL.SOURCE_DOCUMENT_TYPE_ID, 10, 8,DECODE(OOL.LINE_CATEGORY_CODE, 'ORDER',2,12)) IN (2,8,12)
1566 and ((p_planning_level = 1 AND DECODE(OOL.SOURCE_DOCUMENT_TYPE_ID, 10, 8,DECODE(OOL.LINE_CATEGORY_CODE, 'ORDER',2,12)) <> 8 ) OR
1567 SUBINVENTORY = p_subinventory_code) -- Included later for ORG Level
1568 and (SUBINVENTORY is null or
1569 p_planning_level = 2 or
1570 EXISTS (SELECT 1
1571 FROM MTL_SECONDARY_INVENTORIES S
1572 WHERE S.ORGANIZATION_ID = p_organization_id
1573 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY
1574 AND S.availability_type = DECODE(p_include_nonnet,
1575 1,
1576 S.availability_type,
1577 1)));
1578
1579 end if;
1580
1581 -- find out the reserved qty for the material from mtl_reservations
1582 if ((p_net_reserved = 1 or p_net_unreserved = 1) and
1583 (nvl(p_net_reserved,0) <> 1 and nvl(p_net_unreserved,0) <> 1)) then
1584 select sum(PRIMARY_RESERVATION_QUANTITY)
1585 into l_total_reserve_qty
1586 from mtl_reservations
1587 WHERE ORGANIZATION_ID = p_organization_id
1588 and INVENTORY_ITEM_ID = p_inventory_item_id
1589 and REQUIREMENT_DATE <= sysdate + p_demand_cutoff
1590 and demand_source_type_id in (2,8,12)
1591 and ((p_planning_level = 1 AND demand_source_type_id <> 8 ) OR
1592 SUBINVENTORY_CODE = p_subinventory_code) -- Included later for ORG Level
1593 and (SUBINVENTORY_CODE is null or
1594 p_planning_level = 2 or
1595 EXISTS (SELECT 1
1596 FROM MTL_SECONDARY_INVENTORIES S
1597 WHERE S.ORGANIZATION_ID = p_organization_id
1598 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
1599 AND S.availability_type = DECODE(p_include_nonnet,
1600 1,
1601 S.availability_type,
1602 1)));
1603 end if;
1604
1605 -- total demand is calculated as follows:
1606 -- if we have to consider both unreserved matl and reserved matl. then the
1607 -- demand is simply the total demand = ordered qty - shipped qty.
1608 -- elsif we have to take into account only reserved matl. then the
1609 -- demand is simply the reservations from mtl_reservations for the matl.
1610 -- elsif we have to take into account just the unreserved matl. then the
1611 -- demand is total demand - the reservations for the material.
1612 if p_net_unreserved = 1 and p_net_reserved = 1 then
1613 l_demand_qty := Nvl(l_total_demand_qty,0);
1614 elsif p_net_reserved = 1 then
1615 l_demand_qty := Nvl(l_total_reserve_qty,0);
1616 elsif p_net_unreserved = 1 then
1617 l_demand_qty := Nvl(l_total_demand_qty,0) - Nvl(l_total_reserve_qty,0);
1618 end if;
1619 total := total + nvl(l_demand_qty,0);
1620
1621
1622 -- Take care of internal orders for org level planning
1623 if p_planning_level = 1 then
1624 l_total_demand_qty := 0;
1625 l_demand_qty := 0;
1626 l_total_reserve_qty := 0;
1627
1628 -- get the total demand which is the difference between the
1629 -- ordered qty. and the shipped qty.
1630 -- This gives the total demand including the reserved
1631 -- and the unreserved material.
1632 if p_net_unreserved = 1 then
1633 select SUM(INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(SHIP_FROM_ORG_ID,
1634 INVENTORY_ITEM_ID, ORDER_QUANTITY_UOM, Nvl(ordered_quantity,0)) -
1635 get_shipped_qty(p_organization_id,p_inventory_item_id, so.line_id))
1636 into l_total_demand_qty
1637 from oe_order_lines_all so,
1638 po_requisition_headers_all poh,
1639 po_requisition_lines_all pol
1640 where so.ORIG_SYS_DOCUMENT_REF = poh.segment1
1641 and poh.requisition_header_id = pol .requisition_header_id
1642 and so.orig_sys_line_ref = pol.line_num
1643 and ( pol.DESTINATION_ORGANIZATION_ID <> p_organization_id or
1644 (pol.DESTINATION_ORGANIZATION_ID = p_organization_id and -- Added code Bug#1012179
1645 pol.DESTINATION_TYPE_CODE = 'EXPENSE')
1646 )
1647 and so.ship_from_org_ID = p_organization_id
1648 and so.open_flag = 'Y'
1649 and so.INVENTORY_ITEM_ID = p_inventory_item_id
1650 and schedule_ship_date <= sysdate + p_demand_cutoff
1651 and DECODE(so.SOURCE_DOCUMENT_TYPE_ID, 10, 8,DECODE(so.LINE_CATEGORY_CODE, 'ORDER',2,12)) = 8
1652 and (SUBINVENTORY is null or
1653 EXISTS (SELECT 1
1654 FROM MTL_SECONDARY_INVENTORIES S
1655 WHERE S.ORGANIZATION_ID = p_organization_id
1656 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY
1657 AND S.availability_type = DECODE(p_include_nonnet,
1658 1,
1659 S.availability_type,
1660 1)));
1661 end if;
1662
1663 -- find out the reserved qty for the material from mtl_reservations
1664 if ((p_net_reserved = 1 or p_net_unreserved = 1) and
1665 (nvl(p_net_reserved,0) <> 1 and nvl(p_net_unreserved,0) <> 1)) then
1666 -- Include the reserved demand from mtl_reservations
1667 select sum(PRIMARY_RESERVATION_QUANTITY)
1668 into l_total_reserve_qty
1669 from mtl_reservations md, oe_order_lines_all so,
1670 po_req_distributions_all pod,
1671 po_requisition_lines_all pol
1672 where md.DEMAND_SOURCE_LINE_ID = so.LINE_ID
1673 and so.ORIG_SYS_LINE_REF = pod.DISTRIBUTION_ID
1674 and pod.REQUISITION_LINE_ID = pol.REQUISITION_LINE_ID
1675 and (pol.DESTINATION_ORGANIZATION_ID <> p_organization_id or
1676 (pol.DESTINATION_ORGANIZATION_ID = p_organization_id
1677 and -- Added code Bug#1012179
1678 pol.DESTINATION_TYPE_CODE = 'EXPENSE')
1679 )
1680 and ORGANIZATION_ID = p_organization_id
1681 and md.INVENTORY_ITEM_ID = p_inventory_item_id
1682 and REQUIREMENT_DATE <= sysdate + p_demand_cutoff
1683 and demand_source_type_id = 8
1684 and (SUBINVENTORY_CODE is null or
1685 EXISTS (SELECT 1
1686 FROM MTL_SECONDARY_INVENTORIES S
1687 WHERE S.ORGANIZATION_ID = p_organization_id
1688 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
1689 AND S.availability_type = DECODE(p_include_nonnet,
1690 1,
1691 S.availability_type,
1692 1)));
1693
1694 end if;
1695
1696 -- total demand is calculated as follows:
1697 -- if we have to consider both unreserved matl and reserved matl. then the
1698 -- demand is simply the total demand = ordered qty - shipped qty.
1699 -- elsif we have to take into account only reserved matl. then the
1700 -- demand is simply the reservations from mtl_reservations for the matl.
1701 -- elsif we have to take into account just the unreserved matl. then the
1702 -- demand is total demand - the reservations for the material.
1703 if p_net_unreserved = 1 and p_net_reserved = 1 then
1704 l_demand_qty := Nvl(l_total_demand_qty,0);
1705 elsif p_net_reserved = 1 then
1706 l_demand_qty := Nvl(l_total_reserve_qty,0);
1707 elsif p_net_unreserved = 1 then
1708 l_demand_qty := Nvl(l_total_demand_qty,0) - Nvl(l_total_reserve_qty,0);
1709 end if;
1710 total := total + nvl(l_demand_qty,0);
1711 end if;
1712
1713 -- WIP Reservations from mtl_demand
1714 select sum(PRIMARY_UOM_QUANTITY - GREATEST(NVL(RESERVATION_QUANTITY,0),
1715 nvl(COMPLETED_QUANTITY,0)))
1716 into qty
1717 from mtl_demand
1718 where RESERVATION_TYPE = 3
1719 and ORGANIZATION_ID = p_organization_id
1720 and PRIMARY_UOM_QUANTITY > GREATEST(NVL(RESERVATION_QUANTITY,0),
1721 nvl(COMPLETED_QUANTITY,0))
1722 and INVENTORY_ITEM_ID = p_inventory_item_id
1723 and REQUIREMENT_DATE <= sysdate + p_demand_cutoff
1724 and p_net_reserved = 1
1725 and p_planning_level = 1;
1726
1727 -- SUBINVENTORY IS Always expected to be Null when Reservation_type is 3.
1728
1729 total := total + nvl(qty,0);
1730
1731 -- Wip Components are to be included at the Org Level Planning only
1732 -- Qty Issued Substracted from the Qty Required
1733 if (p_net_wip = 1 and p_planning_level = 1)
1734 then
1735 select sum(o.required_quantity - o.quantity_issued)
1736 into qty
1737 from wip_discrete_jobs d, wip_requirement_operations o
1738 where o.wip_entity_id = d.wip_entity_id
1739 and o.organization_id = d.organization_id
1740 and d.organization_id = p_organization_id
1741 and o.inventory_item_id = p_inventory_item_id
1742 and o.date_required <= sysdate + p_demand_cutoff
1743 and o.required_quantity > 0
1744 and o.required_quantity > o.quantity_issued
1745 and o.operation_seq_num > 0
1746 and d.status_type in (1,3,4,6) -- Excluded 5 from selection Bug#1016495
1747 and o.wip_supply_type <> 6;
1748 total := total + nvl(qty,0);
1749 -- Demand Qty to be added for a released repetitve schedule
1750 -- Bug#691471
1751 select sum(o.required_quantity - o.quantity_issued)
1752 into qty
1753 from wip_repetitive_schedules r, wip_requirement_operations o
1754 where o.wip_entity_id = r.wip_entity_id
1755 and o.organization_id = r.organization_id
1756 and r.organization_id = p_organization_id
1757 and o.inventory_item_id = p_inventory_item_id
1758 and o.date_required <= sysdate + p_demand_cutoff
1759 and o.required_quantity > 0
1760 and o.required_quantity > o.quantity_issued
1761 and o.operation_seq_num > 0
1762 and r.status_type in (1,3,4,6) -- Excluded 5 from selection Bug#1016495
1763 and o.wip_supply_type <> 6;
1764 total := total + nvl(qty,0);
1765 end if;
1766
1767
1768 -- Include move orders
1769 -- leave out the closed or cancelled lines
1770 -- select only the issue from stores for org level planning
1771 -- Also select those lines for the sub level planning.
1772 /* SELECT sum(quantity - Nvl(quantity_delivered,0))
1773 INTO qty
1774 FROM mtl_txn_request_lines_v
1775 WHERE organization_id = p_organization_id
1776 AND inventory_item_id = p_inventory_item_id
1777 AND line_status NOT IN (5,6)
1778 AND transaction_action_id = 1
1779 AND (p_planning_level = 1 or
1780 from_subinventory_code = p_subinventory_code) -- Included later for ORG Level
1781 AND ( from_subinventory_code is null or
1782 p_planning_level = 2 or
1783 EXISTS (SELECT 1
1784 FROM MTL_SECONDARY_INVENTORIES S
1785 WHERE S.ORGANIZATION_ID = p_organization_id
1786 AND S.SECONDARY_INVENTORY_NAME = from_subinventory_code
1787 AND S.availability_type = DECODE(p_include_nonnet,
1788 1,S.availability_type,1)))
1789 AND date_required <= sysdate + p_demand_cutoff;
1790 */
1791
1792 SELECT SUM(MTRL.QUANTITY - NVL(MTRL.QUANTITY_DELIVERED,0))
1793 INTO qty
1794 FROM MTL_TXN_REQUEST_LINES MTRL,
1795 MTL_TRANSACTION_TYPES MTT
1796 WHERE MTT.TRANSACTION_TYPE_ID = MTRL.TRANSACTION_TYPE_ID
1797 AND MTRL.ORGANIZATION_ID = p_organization_id
1798 AND MTRL.INVENTORY_ITEM_ID = p_inventory_item_id
1799 AND MTRL.LINE_STATUS NOT IN (5,6)
1800 AND MTT.TRANSACTION_ACTION_ID = 1
1801 AND (p_planning_level = 1 OR
1802 MTRL.FROM_SUBINVENTORY_CODE = p_subinventory_code)
1803 AND (MTRL.FROM_SUBINVENTORY_CODE IS NULL OR
1804 p_planning_level = 2 OR
1805 EXISTS (SELECT 1
1806 FROM MTL_SECONDARY_INVENTORIES S
1807 WHERE S.ORGANIZATION_ID = p_organization_id
1808 AND S.SECONDARY_INVENTORY_NAME = MTRL.FROM_SUBINVENTORY_CODE
1809 AND S.AVAILABILITY_TYPE = DECODE(p_include_nonnet,
1810 1,S.AVAILABILITY_TYPE,1)))
1811 AND MTRL.DATE_REQUIRED <= sysdate + p_demand_cutoff;
1812
1813
1814 total := total + Nvl(qty,0);
1815
1816 -- Include the sub transfer and the staging transfer move orders
1817 -- for sub level planning
1818 SELECT sum(quantity - Nvl(quantity_delivered,0))
1819 INTO qty
1820 FROM mtl_txn_request_lines_v
1821 WHERE organization_id = p_organization_id
1822 AND inventory_item_id = p_inventory_item_id
1823 AND line_status NOT IN (5,6)
1824 AND transaction_action_id IN (2,28)
1825 AND p_planning_level = 2
1826 AND from_subinventory_code = p_subinventory_code
1827 AND date_required <= sysdate + p_demand_cutoff;
1828 total := total + Nvl(qty,0);
1829
1830 return(total);
1831 exception
1832 when others then
1833 return(0);
1834 end;
1835
1836 function get_shipped_qty
1837 (p_organization_id IN NUMBER,
1838 p_inventory_item_id IN NUMBER,
1839 p_order_line_id IN NUMBER
1840 ) return NUMBER
1841 IS
1842 l_shipped_qty NUMBER := 0;
1843 BEGIN
1844 BEGIN
1845 SELECT SUM(primary_quantity)
1846 INTO l_shipped_qty
1847 FROM mtl_material_transactions
1848 WHERE transaction_action_id = 1
1849 AND source_line_id = p_order_line_id
1850 AND organization_id = p_organization_id
1851 AND inventory_item_id = p_inventory_item_id;
1852 EXCEPTION
1853 WHEN OTHERS THEN
1854 l_shipped_qty := 0;
1855 END ;
1856
1857 IF l_shipped_qty IS NULL THEN l_shipped_qty := 0;
1858 ELSE l_shipped_qty := -1 * l_shipped_qty;
1859 END IF;
1860
1861 RETURN l_shipped_qty;
1862 END get_shipped_qty;
1863 end;