[Home] [Help]
PACKAGE BODY: APPS.CSP_EXCESS_PARTS_PVT
Source
1 PACKAGE BODY CSP_EXCESS_PARTS_PVT AS
2 /* $Header: cspvpexb.pls 120.24 2011/10/05 08:37:49 htank 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 -- bug # 8518127
113 v_excess_part CSP_EXCESS_LISTS_PKG.EXCESS_RECORD_TYPE := CSP_EXCESS_LISTS_PKG.G_MISS_EXCESS_REC;
114 CURSOR employee_id_cur IS
115 SELECT employee_id
116 FROM fnd_user
117 WHERE user_id = l_user_id;
118
119 CURSOR minmax_rslts_cur IS
120 SELECT ITEM_SEGMENTS
121 , DESCRIPTION
122 , ERROR
123 , SORTEE
124 , MIN_QTY
125 , MAX_QTY
126 , ONHAND_QTY
127 , SUPPLY_QTY
128 , DEMAND_QTY
129 , TOT_AVAIL_QTY
130 , MIN_ORD_QTY
131 , MAX_ORD_QTY
132 , FIX_MULT
133 , REORD_QTY
134 FROM INV_MIN_MAX_TEMP;
135
136 l_minmax_rslts_rec minmax_rslts_cur%ROWTYPE;
137
138 CURSOR item_attr_cur(p_item_Segments VARCHAR2,
139 p_organization_id NUMBER) IS
140 SELECT c.description description,
141 c.repetitive_planning_flag repetitive_planned_item,
142 c.fixed_lead_time fixed_lead_time,
143 c.variable_lead_time variable_lead_time,
144 NVL(c.preprocessing_lead_time, 0) +
145 NVL(c.full_lead_time, 0) +
146 NVL(c.postprocessing_lead_time, 0) buying_lead_time,
147 c.primary_uom_code primary_uom,
148 p.ap_accrual_account accru_acct,
149 p.invoice_price_var_account ipv_acct,
150 NVL(c.encumbrance_account, p.encumbrance_account) budget_acct,
151 DECODE(c.inventory_asset_flag, 'Y', p.material_account,
152 NVL(c.expense_account, p.expense_account)) charge_acct,
153 NVL(c.source_type, p.source_type) src_type,
154 DECODE(c.source_type, NULL,
155 DECODE(p.source_type, NULL, NULL, p.source_organization_id),
156 c.source_organization_id) src_org,
157 DECODE(c.source_type, NULL,
158 DECODE(p.source_type, NULL, NULL, p.source_subinventory),
159 c.source_subinventory) src_subinv,
160 c.purchasing_enabled_flag purch_flag,
161 c.internal_order_enabled_flag order_flag,
162 c.mtl_transactions_enabled_flag transact_flag,
163 c.list_price_per_unit unit_price,
164 c.planning_make_buy_code mbf,
165 c.inventory_item_id item_id,
166 c.planner_code planner,
167 build_in_wip_flag build_in_wip,
168 pick_components_flag pick_components
169 FROM mtl_system_items_kfv c,
170 mtl_parameters p
171 WHERE c.concatenated_segments = p_item_Segments
172 AND c.organization_id = p.organization_id
173 AND p.organization_id = p_organization_id;
174
175 l_item_attr_rec item_attr_cur%ROWTYPE;
176
177 CURSOR PLANNING_NODE_REC IS
178 SELECT cpp.NODE_TYPE,cpp.ORGANIZATION_ID,cpp.SECONDARY_INVENTORY,cpp.CONDITION_TYPE,
179 cpp.planning_parameters_id,cpp.level_id,csin.parts_loop_id,csin.hierarchy_node_id,
180 csin.owner_resource_id, csin.owner_resource_type
181 FROM CSP_PLANNING_PARAMETERS cpp,csp_sec_inventories csin
182 WHERE LEVEL_ID LIKE p_level_id||'%'
183 and cpp.organization_id = csin.organization_id(+)
184 and cpp.secondary_inventory = csin.secondary_inventory_name(+);
185
186 cursor effective_subinv(p_resource_id Number,
187 p_resource_type varchar2,
188 p_organization_id Number,
189 p_subinventory_code Varchar2)
190 is
191 select CSP_INV_LOC_ASSIGNMENT_ID from csp_inv_loc_assignments
192 where resource_id = p_resource_id and
193 resource_type = p_resource_type and
194 organization_id = p_organization_id and
195 SUBINVENTORY_CODE = p_subinventory_code and
196 (EFFECTIVE_DATE_END is null or trunc(EFFECTIVE_DATE_END) > trunc(sysdate));
197
198 Cursor INV_MIN_MAX_TEMP IS
199 SELECT ITEM_SEGMENTS,MIN_QTY,MAX_QTY,ONHAND_QTY,SUPPLY_QTY,DEMAND_QTY,
200 TOT_AVAIL_QTY,MIN_ORD_QTY,MAX_ORD_QTY,FIX_MULT,REORD_QTY
201 FROM INV_MIN_MAX_TEMP;
202
203 cursor c_org_items is
204 select distinct moq.inventory_item_id,
205 nvl(msib.max_minmax_quantity,0) max,
206 revision_qty_control_code
207 from mtl_onhand_quantities_detail moq,
208 mtl_system_items_b msib
209 where moq.organization_id = p_organization_id
210 and msib.organization_id = moq.organization_id
211 and msib.inventory_item_id = moq.inventory_item_id
212 and nvl(msib.INVENTORY_PLANNING_CODE,6) = 6;
213
214 cursor c_org_subinventories(c_inventory_item_id number) is
215 select distinct moq.subinventory_code
216 from mtl_onhand_quantities moq,
217 csp_planning_parameters cpp
218 where moq.organization_id = p_organization_id
219 and moq.inventory_item_id = c_inventory_item_id
220 and cpp.organization_id = moq.organization_id
221 and cpp.secondary_inventory = moq.subinventory_code
222 and cpp.condition_type = 'G';
223
224 cursor c_subinventories is
225 select msi.secondary_inventory_name
226 from mtl_secondary_inventories msi,
227 csp_planning_parameters cpp
228 where msi.organization_id = p_organization_id
229 and msi.secondary_inventory_name = nvl(p_subinventory_code,msi.secondary_inventory_name)
230 and cpp.organization_id = msi.organization_id
231 and cpp.secondary_inventory = msi.secondary_inventory_name
232 and cpp.condition_type = 'G';
233
234 cursor c_sub_items(c_subinventory_code varchar2) is
235 select mosv.inventory_item_id,
236 nvl(misi.max_minmax_quantity,0) max,
237 msib.revision_qty_control_code
238 from mtl_onhand_sub_v mosv,
239 mtl_item_sub_inventories misi,
240 mtl_system_items_b msib
241 where mosv.organization_id = p_organization_id
242 and mosv.subinventory_code = c_subinventory_code
243 and misi.organization_id(+) = mosv.organization_id
244 and misi.inventory_item_id(+) = mosv.inventory_item_id
245 and misi.secondary_inventory(+) = mosv.subinventory_code
246 and msib.organization_id = mosv.organization_id
247 and msib.inventory_item_id = mosv.inventory_item_id
248 and nvl(misi.INVENTORY_PLANNING_CODE,6) = 6
249 /* Added to avoid duplicate rows of revision controled item */
250 group by mosv.inventory_item_id,
251 misi.max_minmax_quantity,
252 msib.revision_qty_control_code;
253 Begin
254 SAVEPOINT Create_excess_parts_PUB;
255
256 SELECT Sysdate INTO l_today FROM dual;
257 l_user_id := nvl(fnd_global.user_id, 0) ;
258 l_login_id := nvl(fnd_global.login_id, -1);
259
260 FOR Rec IN PLANNING_NODE_REC LOOP
261 IF (Rec.NODE_TYPE <> 'REGION' AND Rec.ORGANIZATION_ID is NOT NULL) THEN
262
263 If (Rec.owner_resource_id is NOT NULL and
264 Rec.NODE_TYPE = 'SUBINVENTORY' and
265 Rec.SECONDARY_INVENTORY is NOT NULL) THEN
266
267 open effective_subinv(Rec.owner_resource_id,Rec.owner_resource_type,Rec.ORGANIZATION_ID,Rec.SECONDARY_INVENTORY);
268 fetch effective_subinv into L_LOC_ASSIGNMENT_ID;
269 close effective_subinv;
270 End if;
271
272 If (Rec.NODE_TYPE = 'ORGANIZATION_WH') OR
273 (Rec.NODE_TYPE = 'SUBINVENTORY' and
274 Rec.SECONDARY_INVENTORY is NOT NULL and
275 (Rec.owner_resource_id is NULL or
276 (Rec.owner_resource_id is NOT NULL and
277 L_LOC_ASSIGNMENT_ID is NOT NULL)
278 )
279 )
280 THEN
281
282 IF (Rec.NODE_TYPE = 'SUBINVENTORY' and Rec.SECONDARY_INVENTORY is NOT NULL) THEN
283 p_organization_id := Rec.ORGANIZATION_ID;
284 l_level := 2;
285 p_subinventory_code := Rec.SECONDARY_INVENTORY;
286 p_condition_type := REC.CONDITION_TYPE;
287 Elsif (Rec.NODE_TYPE = 'ORGANIZATION_WH') THEN
288 p_organization_id := Rec.ORGANIZATION_ID;
289 l_level := 1;
290 p_subinventory_code := Null;
291 p_condition_type := Null;
292 End if;
293
294 --Delete remaining open excess lines from previous run
295 if p_called_from = 'STD' then
296 clean_up(p_organization_id => p_organization_id,
297 p_subinventory_code => p_subinventory_code,
298 p_condition_type => p_condition_type);
299 end if;
300 -- 1. get values of all parameters for calling run_min_max_plan
301 if p_restock = 1 and p_dd_loc_id is null then
302 begin
303 Select MEANING
304 into error_message
305 FROM MFG_LOOKUPS
306 WHERE LOOKUP_TYPE='INV_MMX_RPT_MSGS'
307 and LOOKUP_CODE = 4;
308 exception
309 when others then
310 null;
311 end;
312 end if;
313
314 -- get employee id
315 OPEN employee_id_cur;
316 FETCH employee_id_cur INTO l_employee_id;
317 CLOSE employee_id_cur;
318
319 l_d_cutoff := to_date(p_d_cutoff,'YYYY/MM/DD HH24:MI:SS');
320 l_s_cutoff := to_date(p_s_cutoff,'YYYY/MM/DD HH24:MI:SS');
321 l_D_CUTOFF := NVL(l_D_CUTOFF, SYSDATE);
322 l_S_CUTOFF := NVL(l_S_CUTOFF, SYSDATE);
323
324 IF (P_D_CUTOFF_REL IS NOT NULL) THEN
325 l_D_CUTOFF := NVL(l_D_CUTOFF, sysdate) + P_D_CUTOFF_REL;
326 END IF;
327
328 IF (P_S_CUTOFF_REL IS NOT NULL) THEN
329 l_S_CUTOFF := NVL(l_S_CUTOFF, sysdate) + P_S_CUTOFF_REL;
330 END IF;
331
332 /* get encum flag, org name, PO org ID */
333 declare
334 l_operating_unit number;
335 begin
336 select operating_unit, substr(organization_name,1,30), operating_unit
337 into l_operating_unit, l_org_name, l_po_org_id
338 from org_organization_definitions
339 where organization_id = p_organization_id;
340
341 select nvl(req_encumbrance_flag, 'N')
342 into l_encum_flag
343 from financials_system_params_all
344 where nvl(org_id,-11)=nvl(l_operating_unit,-11);
345 end;
346
347 /* get calendar */
348 select p.calendar_code, p.calendar_exception_set_id
349 into l_cal_code, l_exception_set_id
350 from mtl_parameters p
351 where p.organization_id = p_organization_id;
352
353 /* Validate cat set and MCAT struct */
354 IF (p_cat_set_id is not null and p_catg_struct_id is not null) then
355 SELECT STRUCTURE_ID
356 into l_mcat_struct_id
357 FROM MTL_CATEGORY_SETS
358 WHERE CATEGORY_SET_ID = p_cat_set_id;
359 ELSE
360 SELECT CSET.CATEGORY_SET_ID, CSET.STRUCTURE_ID
361 INTO l_category_set_id, l_mcat_struct_id
362 FROM MTL_CATEGORY_SETS CSET,
363 MTL_DEFAULT_CATEGORY_SETS DEF
364 WHERE DEF.CATEGORY_SET_ID = CSET.CATEGORY_SET_ID
365 AND DEF.FUNCTIONAL_AREA_ID = 1;
366 END IF;
367
368 IF p_buyer_lo is not null and p_buyer_hi is not null then
369 L_RANGE_BUYER := 'v.full_name between ' ||''''||P_BUYER_LO||
370 '''' || ' and ' || ''''||P_BUYER_HI||'''';
371 ELSIF p_BUYER_lo is not null then
372 L_RANGE_BUYER := 'v.full_name >= ' ||''''||P_BUYER_LO||'''';
373 ELSIF p_BUYER_hi is not null then
374 L_RANGE_BUYER := 'v.full_name <= ' ||''''||P_BUYER_HI||'''';
375 END IF;
376
377 /* set order by clause */
378 IF P_sort=1 then
379 l_order_by := ' order by 1';
380 ELSIF P_sort = 2 then
381 l_order_by := ' order by 13,1';
382 ELSIF P_sort = 3 then
383 l_order_by := ' order by 11,1';
384 ELSIF P_sort = 4 then
385 l_order_by := ' order by 12,1';
386 END IF;
387
388 Build_item_cat_select(
389 p_Cat_Structure_id => l_mcat_struct_id,
390 x_item_select => l_item_Select,
391 x_cat_Select => l_cat_select);
392
393 Build_range_sql(
394 p_cat_structure_id => l_mcat_Struct_id
395 , p_cat_lo => p_Catg_lo
396 , p_cat_hi => p_catg_hi
397 , p_item_lo => p_item_lo
398 , p_item_hi => p_item_hi
399 , p_planner_lo => p_planner_lo
400 , p_planner_hi => p_planner_hi
401 , p_lot_ctl => p_lot_Ctl
402 , x_range_sql => l_range_sql);
403
404 IF p_dd_loc_id is not null THEN
405 -- get customer id
406 BEGIN
407 select customer_id
408 into l_cust_id
409 from po_location_associations
410 where location_id = P_dd_loc_id;
411 EXCEPTION
412 when no_data_found then
413 l_cust_id := 0;
414 END;
415 END IF;
416
417 select WIP_JOB_SCHEDULE_INTERFACE_S.nextval
418 into l_WIP_BATCH_ID
419 from dual;
420
421 if p_called_from <> 'PART_STATUS' and nvl(p_condition_type,'B') = 'B' then
422 defective_return(p_organization_id,
423 p_subinventory_code,
424 rec.planning_parameters_id,
425 rec.level_id,
426 rec.parts_loop_id,
427 rec.hierarchy_node_id,
428 p_called_from);
429 end if;
430
431 if nvl(p_condition_type,'G') = 'G' then
432 CSP_MINMAX_PVT.run_min_max_plan(
433 p_item_select => l_item_select
434 , p_handle_rep_item => p_handle_rep_item
435 , p_pur_revision => nvl(fnd_profile.value('INV_PURCHASING_BY_REVISION'),2)
436 , p_cat_select => l_Cat_select
437 , p_cat_set_id => nvl(p_Cat_set_id,l_category_set_id)
438 , p_mcat_struct => l_mcat_struct_id
439 , p_level => l_level
440 , p_restock => 2
441 , p_include_nonnet => p_include_nonnet
442 , p_include_po => p_include_po
443 , p_include_wip => p_include_wip
444 , p_include_if => p_include_if
445 , p_net_rsv => p_net_rsv
446 , p_net_unrsv => p_net_unrsv
447 , p_net_wip => p_net_wip
448 , p_org_id => p_organization_id
449 , p_user_id => l_user_id
450 , p_employee_id => l_employee_id
451 , p_subinv => p_subinventory_code
452 , p_dd_loc_id => p_dd_loc_id
453 , p_wip_batch_id => l_wip_batch_id
454 , p_approval => to_number(nvl(FND_PROFILE.VALUE('INV_MINMAX_REORDER_APPROVED'),'1'))
455 , p_buyer_hi => p_buyer_hi
456 , p_buyer_lo => p_buyer_lo
457 , p_range_buyer => l_range_buyer
458 , p_cust_id => l_cust_id
459 , p_po_org_id => l_po_org_id
460 , p_range_sql => l_range_Sql
461 , p_sort => p_sort
462 , p_selection => 2 -- items above maximum quantity
463 , p_sysdate => l_today
464 , p_s_cutoff => l_s_cutoff
465 , p_d_cutoff => l_d_cutoff
466 , p_order_by => l_order_by
467 , p_encum_flag => l_encum_flag
468 , p_cal_code => l_cal_code
469 , p_exception_set_id => l_exception_set_id
470 , x_return_status => l_Return_status
471 , x_msg_data => l_msg_data);
472
473 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
474 RAISE FND_API.G_EXC_ERROR;
475 END IF;
476
477 --ORGANIZATION Level
478 --if l_level = 1 then
479
480 For INV_REC IN INV_MIN_MAX_TEMP LOOP
481
482 Begin
483 SELECT msik.inventory_item_id
484 INTO l_item_id
485 FROM mtl_system_items_kfv msik
486 WHERE msik.concatenated_segments = inv_rec.item_segments
487 AND msik.organization_id = p_organization_id;
488 Exception
489 When no_data_found then
490 l_item_id := Null;
491 End;
492
493 if p_called_from = 'PART_STATUS' then
494 insert into csp_sup_dem_sub_temp(
495 inventory_item_id,
496 organization_id,
497 subinventory_code,
498 planning_parameters_id,
499 level_id,
500 parts_loop_id,
501 hierarchy_node_id,
502 excess_quantity)
503 values(
504 l_item_id,
505 p_organization_id,
506 p_subinventory_code,
507 rec.planning_parameters_id,
508 rec.level_id,
509 rec.parts_loop_id,
510 rec.hierarchy_node_id,
511 NVL(inv_rec.TOT_AVAIL_QTY,0) - NVL(inv_rec.SUPPLY_QTY,0) - NVL(inv_rec.MAX_QTY,0));
512 else
513 x_excess_line_id := null;
514 /*
515 csp_excess_lists_pkg.Insert_Row(
516 px_EXCESS_LINE_ID => x_excess_line_id,
517 p_CREATED_BY => fnd_global.user_id,
518 p_CREATION_DATE => sysdate,
519 p_LAST_UPDATED_BY => fnd_global.user_id,
520 p_LAST_UPDATE_DATE => sysdate,
521 p_LAST_UPDATE_LOGIN => null,
522 p_ORGANIZATION_ID => p_organization_id,
523 p_SUBINVENTORY_CODE => p_subinventory_code,
524 p_CONDITION_CODE => 'G',
525 p_INVENTORY_ITEM_ID => l_item_id,
526 p_EXCESS_QUANTITY => NVL(inv_rec.TOT_AVAIL_QTY,0) - NVL(inv_rec.SUPPLY_QTY,0) - NVL(inv_rec.MAX_QTY,0),
527 p_EXCESS_STATUS => 'P',
528 p_REQUISITION_LINE_ID => null,
529 p_RETURNED_QUANTITY => null,
530 p_current_return_qty => null,
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 */
548
549
550 v_excess_part := CSP_EXCESS_LISTS_PKG.G_MISS_EXCESS_REC;
551 v_excess_part.CREATED_BY := fnd_global.user_id;
552 v_excess_part.CREATION_DATE := sysdate;
553 v_excess_part.LAST_UPDATED_BY := fnd_global.user_id;
554 v_excess_part.LAST_UPDATE_DATE := sysdate;
555 v_excess_part.ORGANIZATION_ID := p_organization_id;
556 v_excess_part.SUBINVENTORY_CODE := p_subinventory_code;
557 v_excess_part.CONDITION_CODE := 'G';
558 v_excess_part.INVENTORY_ITEM_ID := l_item_id;
559 v_excess_part.EXCESS_QUANTITY := NVL(inv_rec.TOT_AVAIL_QTY,0) - NVL(inv_rec.SUPPLY_QTY,0) - NVL(inv_rec.MAX_QTY,0);
560 v_excess_part.EXCESS_STATUS := 'P';
561
562 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
563 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
564 'csp.plsql.CSP_EXCESS_PARTS_PVT.excess_parts',
565 'Calling populate_excess_list 1');
566 end if;
567
568 populate_excess_list(v_excess_part);
569
570 end if;
571 End loop;
572
573 update CSP_SEC_INVENTORIES
574 set last_excess_run_date = sysdate
575 where organization_id = p_organization_id
576 and secondary_inventory_name = nvl(p_subinventory_code, secondary_inventory_name);
577
578
579 --ORGANIZATION Level
580
581 if l_level = 1 then
582 for coi in c_org_items loop
583 l_total_onhand := 0;
584 l_onhand := 0;
585 l_demand := 0;
586 l_excess := 0;
587
588 --for cos in c_org_subinventories(coi.inventory_item_id) loop
589 l_onhand := csp_excess_parts_pvt.onhand(
590 p_organization_id => p_organization_id,
591 p_inventory_item_id => coi.inventory_item_id,
592 --p_subinventory_code => cos.subinventory_code,
593 p_subinventory_code => NULL,
594 p_revision_qty_control_code => coi.revision_qty_control_code,
595 p_include_nonnet => p_include_nonnet,
596 p_planning_level => l_level);
597
598 l_total_onhand := l_total_onhand + l_onhand;
599 --end loop;
600
601 l_demand := csp_excess_parts_pvt.demand(
602 p_organization_id => p_organization_id,
603 p_inventory_item_id => coi.inventory_item_id,
604 p_subinventory_code => null,
605 p_include_nonnet => p_include_nonnet,
606 p_planning_level => l_level,
607 p_net_unreserved => p_net_unrsv,
608 p_net_reserved => p_net_rsv,
609 p_net_wip => p_net_wip,
610 p_demand_cutoff => P_D_CUTOFF_REL); -- number of days
611
612 l_excess := nvl(l_total_onhand,0) - nvl(l_demand,0);
613
614 if l_excess > 0 then
615 if p_called_from = 'PART_STATUS' then
616 insert into csp_sup_dem_sub_temp(
617 inventory_item_id,
618 organization_id,
619 subinventory_code,
620 planning_parameters_id,
621 level_id,
622 parts_loop_id,
623 hierarchy_node_id,
624 excess_quantity)
625 values(
626 coi.inventory_item_id,
627 p_organization_id,
628 null,
629 rec.planning_parameters_id,
630 rec.level_id,
631 rec.parts_loop_id,
632 rec.hierarchy_node_id,
633 l_excess);
634 else
635 x_excess_line_id := null;
636 /*
637 csp_excess_lists_pkg.Insert_Row(
638 px_EXCESS_LINE_ID => x_excess_line_id,
639 p_CREATED_BY => fnd_global.user_id,
640 p_CREATION_DATE => sysdate,
641 p_LAST_UPDATED_BY => fnd_global.user_id,
642 p_LAST_UPDATE_DATE => sysdate,
643 p_LAST_UPDATE_LOGIN => null,
644 p_ORGANIZATION_ID => p_organization_id,
645 p_SUBINVENTORY_CODE => null,
646 p_CONDITION_CODE => 'G',
647 p_INVENTORY_ITEM_ID => coi.inventory_item_id,
648 p_EXCESS_QUANTITY => l_excess,
649 p_EXCESS_STATUS => 'P',
650 p_REQUISITION_LINE_ID => null,
651 p_RETURNED_QUANTITY => null,
652 p_current_return_qty => null,
653 p_ATTRIBUTE_CATEGORY => null,
654 p_ATTRIBUTE1 => null,
655 p_ATTRIBUTE2 => null,
656 p_ATTRIBUTE3 => null,
657 p_ATTRIBUTE4 => null,
658 p_ATTRIBUTE5 => null,
659 p_ATTRIBUTE6 => null,
660 p_ATTRIBUTE7 => null,
661 p_ATTRIBUTE8 => null,
662 p_ATTRIBUTE9 => null,
663 p_ATTRIBUTE10 => null,
664 p_ATTRIBUTE11 => null,
665 p_ATTRIBUTE12 => null,
666 p_ATTRIBUTE13 => null,
667 p_ATTRIBUTE14 => null,
668 p_ATTRIBUTE15 => null);
669 */
670
671 v_excess_part := CSP_EXCESS_LISTS_PKG.G_MISS_EXCESS_REC;
672 v_excess_part.CREATED_BY := fnd_global.user_id;
673 v_excess_part.CREATION_DATE := sysdate;
674 v_excess_part.LAST_UPDATED_BY := fnd_global.user_id;
675 v_excess_part.LAST_UPDATE_DATE := sysdate;
676 v_excess_part.ORGANIZATION_ID := p_organization_id;
677 v_excess_part.CONDITION_CODE := 'G';
678 v_excess_part.INVENTORY_ITEM_ID := coi.inventory_item_id;
679 v_excess_part.EXCESS_QUANTITY := l_excess;
680 v_excess_part.EXCESS_STATUS := 'P';
681
682 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
683 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
684 'csp.plsql.CSP_EXCESS_PARTS_PVT.excess_parts',
685 'Calling populate_excess_list 2');
686 end if;
687
688 populate_excess_list(v_excess_part);
689
690 end if;
691 end if;
692
693 end loop;
694
695 update CSP_SEC_INVENTORIES
696 set last_excess_run_date = sysdate
697 where organization_id = p_organization_id;
698
699 end if;
700
701 -- SUBINVENTORY Level
702 if l_level = 2 then
703 for curs in c_subinventories loop
704 for csin in c_sub_items(curs.secondary_inventory_name) loop
705 l_onhand := csp_excess_parts_pvt.onhand(
706 p_organization_id => p_organization_id,
707 p_inventory_item_id => csin.inventory_item_id,
708 p_subinventory_code => curs.secondary_inventory_name,
709 p_revision_qty_control_code => csin.revision_qty_control_code,
710 p_include_nonnet => p_include_nonnet,
711 p_planning_level => l_level);
712
713 l_demand := csp_excess_parts_pvt.demand(
714 p_organization_id => p_organization_id,
715 p_inventory_item_id => csin.inventory_item_id,
716 p_subinventory_code => curs.secondary_inventory_name,
717 p_include_nonnet => p_include_nonnet,
718 p_planning_level => l_level,
719 p_net_unreserved => p_net_unrsv,
720 p_net_reserved => p_net_rsv,
721 p_net_wip => p_net_wip,
722 p_demand_cutoff => P_D_CUTOFF_REL);
723
724 l_excess := nvl(l_onhand,0) - nvl(l_demand,0);
725
726 if nvl(l_excess,0) > 0 then
727 if p_called_from = 'PART_STATUS' then
728 insert into csp_sup_dem_sub_temp(
729 inventory_item_id,
730 organization_id,
731 subinventory_code,
732 planning_parameters_id,
733 level_id,
734 parts_loop_id,
735 hierarchy_node_id,
736 excess_quantity)
737 values(
738 csin.inventory_item_id,
739 p_organization_id,
740 curs.secondary_inventory_name,
741 rec.planning_parameters_id,
742 rec.level_id,
743 rec.parts_loop_id,
744 rec.hierarchy_node_id,
745 l_excess);
746 else
747 x_excess_line_id := null;
748 /*
749 csp_excess_lists_pkg.Insert_Row(
750 px_EXCESS_LINE_ID => x_excess_line_id,
751 p_CREATED_BY => fnd_global.user_id,
752 p_CREATION_DATE => sysdate,
753 p_LAST_UPDATED_BY => fnd_global.user_id,
754 p_LAST_UPDATE_DATE => sysdate,
755 p_LAST_UPDATE_LOGIN => null,
756 p_ORGANIZATION_ID => p_organization_id,
757 p_SUBINVENTORY_CODE => curs.secondary_inventory_name,
758 p_CONDITION_CODE => 'G',
759 p_INVENTORY_ITEM_ID => csin.inventory_item_id,
760 p_EXCESS_QUANTITY => l_excess,
761 p_EXCESS_STATUS => 'P',
762 p_REQUISITION_LINE_ID => null,
763 p_RETURNED_QUANTITY => null,
764 p_current_return_qty => null,
765 p_ATTRIBUTE_CATEGORY => null,
766 p_ATTRIBUTE1 => null,
767 p_ATTRIBUTE2 => null,
768 p_ATTRIBUTE3 => null,
769 p_ATTRIBUTE4 => null,
770 p_ATTRIBUTE5 => null,
771 p_ATTRIBUTE6 => null,
772 p_ATTRIBUTE7 => null,
773 p_ATTRIBUTE8 => null,
774 p_ATTRIBUTE9 => null,
775 p_ATTRIBUTE10 => null,
776 p_ATTRIBUTE11 => null,
777 p_ATTRIBUTE12 => null,
778 p_ATTRIBUTE13 => null,
779 p_ATTRIBUTE14 => null,
780 p_ATTRIBUTE15 => null);
781 */
782
783 v_excess_part := CSP_EXCESS_LISTS_PKG.G_MISS_EXCESS_REC;
784 v_excess_part.CREATED_BY := fnd_global.user_id;
785 v_excess_part.CREATION_DATE := sysdate;
786 v_excess_part.LAST_UPDATED_BY := fnd_global.user_id;
787 v_excess_part.LAST_UPDATE_DATE := sysdate;
788 v_excess_part.ORGANIZATION_ID := p_organization_id;
789 v_excess_part.SUBINVENTORY_CODE := curs.secondary_inventory_name;
790 v_excess_part.CONDITION_CODE := 'G';
791 v_excess_part.INVENTORY_ITEM_ID := csin.inventory_item_id;
792 v_excess_part.EXCESS_QUANTITY := l_excess;
793 v_excess_part.EXCESS_STATUS := 'P';
794
795 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
796 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
797 'csp.plsql.CSP_EXCESS_PARTS_PVT.excess_parts',
798 'Calling populate_excess_list 3');
799 end if;
800
801 populate_excess_list(v_excess_part);
802 end if;
803 end if;
804
805 end loop;
806
807 update CSP_SEC_INVENTORIES
808 set last_excess_run_date = sysdate
809 where organization_id = p_organization_id
810 and secondary_inventory_name = nvl(curs.secondary_inventory_name, secondary_inventory_name);
811
812 end loop;
813 end if;
814 if p_called_from <> 'PART_STATUS' then
815 l_excess_rule_id := get_business_rule(
816 p_organization_id => p_organization_id,
817 p_subinventory_code => p_subinventory_code);
818
819 if l_excess_rule_id is not null then
820 csp_excess_parts_pvt.apply_business_rules(
821 p_organization_id => p_organization_id,
822 p_subinventory_code => p_subinventory_code,
823 p_excess_rule_id => l_excess_rule_id);
824 else
825 update csp_excess_lists
826 set excess_status = 'O'
827 where excess_status = 'P';
828 commit;
829 end if;
830 end if;
831 end if;
832 end if;
833 End if;
834 Delete from INV_MIN_MAX_TEMP;
835 End loop;
836
837 exception
838 when others then
839 null;
840 end;
841
842 procedure apply_business_rules(
843 p_organization_id number,
844 p_subinventory_code varchar2,
845 p_excess_rule_id number) as
846
847
848 cursor business_rule is
849 select cerb.excess_rule_id,
850 cerb.total_max_excess,
851 cerb.line_max_excess,
852 cerb.total_excess_value,
853 cerb.days_since_receipt,
854 cerb.top_excess_lines,
855 cerb.category_set_id,
856 cerb.category_id
857 from csp_excess_rules_b cerb
858 where excess_rule_id = p_excess_rule_id;
859
860 br_rec business_rule%rowtype;
861
862 cursor excess_value is
863 select sum(cel.excess_quantity * NVL(ITEM_COST,0))
864 from CST_ITEM_COSTS cic,
865 CST_COST_TYPES cct,
866 csp_excess_lists cel
867 where cic.ORGANIZATION_ID = cel.organization_id
868 and cic.inventory_item_id = cel.inventory_item_id
869 and cic.COST_TYPE_ID = cct.COST_TYPE_ID
870 and cct.COST_TYPE_ID = cct.DEFAULT_COST_TYPE_ID
871 and cel.excess_status = 'P';
872
873 cursor org_max_value is
874 select sum(msib.max_minmax_quantity * NVL(ITEM_COST,0))
875 from CST_ITEM_COSTS cic,
876 CST_COST_TYPES cct,
877 mtl_system_items_b msib
878 where msib.organization_id = p_organization_id
879 and cic.ORGANIZATION_ID = msib.organization_id
880 and cic.inventory_item_id = msib.inventory_item_id
881 and cic.COST_TYPE_ID = cct.COST_TYPE_ID
882 and cct.COST_TYPE_ID = cct.DEFAULT_COST_TYPE_ID
883 and msib.max_minmax_quantity > 0;
884
885 cursor sub_max_value is
886 select sum(misi.max_minmax_quantity * nvl(cic.item_cost,0))
887 from cst_item_costs cic,
888 cst_cost_types cct,
889 mtl_item_sub_inventories misi
890 where misi.organization_id = p_organization_id
891 and misi.secondary_inventory = p_subinventory_code
892 and cic.organization_id = misi.organization_id
893 and cic.inventory_item_id = misi.inventory_item_id
894 and cic.cost_type_id = cct.cost_type_id
895 and cct.cost_type_id = cct.default_cost_type_id
896 and misi.max_minmax_quantity > 0;
897
898 cursor org_line_quantity is
899 select cel.excess_line_id,
900 cel.excess_quantity,
901 msib.max_minmax_quantity
902 from csp_excess_lists cel,
903 mtl_system_items_b msib
904 where cel.organization_id = p_organization_id
905 and cel.organization_id = msib.organization_id
906 and cel.inventory_item_id = msib.inventory_item_id
907 and cel.excess_status = 'P';
908
909 cursor sub_line_quantity is
910 select cel.excess_line_id,
911 cel.excess_quantity,
912 misi.max_minmax_quantity
913 from csp_excess_lists cel,
914 mtl_item_sub_inventories misi
915 where cel.organization_id = p_organization_id
916 and cel.organization_id = misi.organization_id
917 and cel.subinventory_code = misi.secondary_inventory
918 and cel.inventory_item_id = misi.inventory_item_id
919 and cel.excess_status = 'P';
920
921 cursor org_recently_received(p_inventory_item_id number) is
922 select mmt.transaction_date
923 from mtl_material_transactions mmt
924 where mmt.organization_id = p_organization_id
925 and mmt.inventory_item_id = p_inventory_item_id
926 and mmt.transaction_action_id in (2,3,12,27,31)
927 and mmt.transaction_quantity > 0
928 and mmt.transaction_date > sysdate - br_rec.days_since_receipt;
929
930 cursor sub_recently_received(p_inventory_item_id number) is
931 select mmt.transaction_date
932 from mtl_material_transactions mmt
933 where mmt.organization_id = p_organization_id
934 and mmt.subinventory_code = p_subinventory_code
935 and mmt.inventory_item_id = p_inventory_item_id
936 and mmt.transaction_action_id in (2,3,12,27,31)
937 and mmt.transaction_quantity > 0
938 and mmt.transaction_date > sysdate - br_rec.days_since_receipt;
939
940 cursor excess_lines is
941 select cel.excess_line_id,
942 cel.inventory_item_id
943 from csp_excess_lists cel
944 where cel.organization_id = p_organization_id
945 and cel.excess_status = 'P';
946
947 cursor excess_line_value is
948 select cel.excess_line_id,
949 cel.excess_quantity * NVL(ITEM_COST,0) value
950 from CST_ITEM_COSTS cic,
951 CST_COST_TYPES cct,
952 csp_excess_lists cel
953 where cel.organization_id = p_organization_id
954 and cic.ORGANIZATION_ID = cel.organization_id
955 and cic.inventory_item_id = cel.inventory_item_id
956 and cic.COST_TYPE_ID = cct.COST_TYPE_ID
957 and cct.COST_TYPE_ID = cct.DEFAULT_COST_TYPE_ID
958 and cel.excess_status = 'P'
959 order by value desc;
960
961
962 l_excess_value number;
963 l_max_value number;
964 l_excess_percentage number;
965 l_line_quantity number;
966 l_received_date date;
967 l_days_since_receipt number;
968 l_counter number;
969 l_value number;
970
971
972 begin
973 -- Fetch business rules
974 open business_rule;
975 fetch business_rule into br_rec;
976 close business_rule;
977
978 if br_rec.category_set_id is not null then
979 delete from csp_excess_lists cel
980 where cel.excess_status = 'P'
981 and cel.inventory_item_id in
982 (select inventory_item_id
983 from mtl_item_categories
984 where category_set_id = br_rec.category_set_id
985 and category_id = nvl(br_rec.category_id,category_id)
986 and organization_id = cel.organization_id);
987 end if;
988
989
990 -- % OF MAX VALUE
991 if br_rec.total_max_excess is not null then
992 open excess_value;
993 fetch excess_value into l_excess_value;
994 close excess_value;
995
996 if p_subinventory_code is null then
997 open org_max_value;
998 fetch org_max_value into l_max_value;
999 close org_max_value;
1000 else
1001 open sub_max_value;
1002 fetch sub_max_value into l_max_value;
1003 close sub_max_value;
1004 end if;
1005
1006 if nvl(l_max_value,0) > 0 then
1007 l_excess_percentage := nvl(l_excess_value,0) / l_max_value * 100;
1008
1009 if l_excess_percentage < br_rec.total_max_excess then
1010 delete from csp_excess_lists
1011 where excess_status = 'P';
1012 null; --exit;
1013 end if;
1014 end if;
1015 end if;
1016
1017 -- % of line quantity PL dependent
1018 if br_rec.line_max_excess is not null then
1019 if p_subinventory_code is null then
1020 for olq in org_line_quantity loop
1021 -- Avoid divisor equal to zero
1022 if nvl(olq.max_minmax_quantity,0) <> 0 then
1023 l_line_quantity := nvl(olq.excess_quantity,0) / nvl(olq.max_minmax_quantity,1) * 100;
1024 if l_line_quantity < nvl(br_rec.line_max_excess,0) then
1025 delete from csp_excess_lists
1026 where excess_line_id = olq.excess_line_id;
1027 end if;
1028 end if;
1029 end loop;
1030 else
1031 for slq in sub_line_quantity loop
1032 -- Avoid divisor equal to zero
1033 if nvl(slq.max_minmax_quantity,0) <> 0 then
1034 l_line_quantity := nvl(slq.excess_quantity,0) / nvl(slq.max_minmax_quantity,1) * 100;
1035 if l_line_quantity < nvl(br_rec.line_max_excess,0) then
1036 delete from csp_excess_lists
1037 where excess_line_id = slq.excess_line_id;
1038 end if;
1039 end if;
1040 end loop;
1041 end if;
1042 end if;
1043
1044 -- Recently Received PL dependent
1045 if br_rec.days_since_receipt is not null then
1046 for el in excess_lines loop
1047 if p_subinventory_code is null then
1048 l_received_date := null;
1049 open org_recently_received(el.inventory_item_id);
1050 fetch org_recently_received into l_received_date;
1051 close org_recently_received;
1052 if l_received_date is not null then
1053 delete from csp_excess_lists
1054 where excess_line_id = el.excess_line_id;
1055 end if;
1056 else
1057 l_received_date := null;
1058 open sub_recently_received(el.inventory_item_id);
1059 fetch sub_recently_received into l_received_date;
1060 close sub_recently_received;
1061 if l_received_date is not null then
1062 delete from csp_excess_lists
1063 where excess_line_id = el.excess_line_id;
1064 end if;
1065 end if;
1066 end loop;
1067 end if;
1068
1069 -- % of the total excess value
1070 if nvl(br_rec.total_excess_value,0) > 0 then
1071 open excess_value;
1072 fetch excess_value into l_excess_value;
1073 close excess_value;
1074
1075 if nvl(l_excess_value,0) > 0 then
1076 l_excess_percentage := 0;
1077 l_value := 0;
1078 for elv in excess_line_value loop
1079 if l_excess_percentage > br_rec.total_excess_value then
1080 delete from csp_excess_lists
1081 where excess_line_id = elv.excess_line_id;
1082 end if;
1083 l_value := l_value + nvl(elv.value,0);
1084 l_excess_percentage := l_value / l_excess_value * 100;
1085 end loop;
1086 end if;
1087 end if;
1088
1089 -- Top X list
1090 if nvl(br_rec.top_excess_lines,0) > 0 then
1091 l_counter := 0;
1092 for elv in excess_line_value loop
1093 l_counter := l_counter + 1;
1094 if l_counter <= br_rec.top_excess_lines then
1095 update csp_excess_lists
1096 set excess_status = 'O'
1097 where excess_line_id = elv.excess_line_id;
1098 else
1099 exit;
1100 end if;
1101 end loop;
1102 delete from csp_excess_lists
1103 where excess_status = 'P';
1104 end if;
1105 -- Remaining excess lines will be comitted
1106 update csp_excess_lists
1107 set excess_status = 'O'
1108 where excess_status = 'P';
1109 commit;
1110 end apply_business_rules;
1111
1112 procedure defective_return(
1113 p_organization_id number,
1114 p_subinventory_code varchar2,
1115 p_planning_parameters_id number,
1116 p_level_id varchar2,
1117 p_parts_loop_id number,
1118 p_hierarchy_node_id number,
1119 p_called_from varchar2) is
1120
1121 cursor defectives is
1122 select mosv.organization_id,
1123 mosv.subinventory_code,
1124 mosv.inventory_item_id,
1125 total_qoh excess_quantity
1126 from mtl_onhand_sub_v mosv,
1127 csp_sec_inventories csin
1128 where mosv.organization_id = p_organization_id
1129 and csin.organization_id = mosv.organization_id
1130 and csin.secondary_inventory_name = mosv.subinventory_code
1131 and csin.condition_type = 'B'
1132 and csin.secondary_inventory_name = nvl(p_subinventory_code,csin.secondary_inventory_name)
1133 and total_qoh > 0;
1134
1135 x_excess_line_id number;
1136
1137 v_excess_part CSP_EXCESS_LISTS_PKG.EXCESS_RECORD_TYPE := CSP_EXCESS_LISTS_PKG.G_MISS_EXCESS_REC;
1138 begin
1139
1140 for d in defectives loop
1141 if p_called_from = 'PART_STATUS' then
1142 insert into csp_sup_dem_sub_temp(
1143 inventory_item_id,
1144 organization_id,
1145 subinventory_code,
1146 planning_parameters_id,
1147 level_id,
1148 parts_loop_id,
1149 hierarchy_node_id,
1150 excess_quantity)
1151 values(
1152 d.inventory_item_id,
1153 d.organization_id,
1154 d.subinventory_code,
1155 p_planning_parameters_id,
1156 p_level_id,
1157 p_parts_loop_id,
1158 p_hierarchy_node_id,
1159 d.excess_quantity);
1160 else
1161 x_excess_line_id := null;
1162 /*
1163 csp_excess_lists_pkg.Insert_Row(
1164 px_EXCESS_LINE_ID => x_excess_line_id,
1165 p_CREATED_BY => fnd_global.user_id,
1166 p_CREATION_DATE => sysdate,
1167 p_LAST_UPDATED_BY => fnd_global.user_id,
1168 p_LAST_UPDATE_DATE => sysdate,
1169 p_LAST_UPDATE_LOGIN => null,
1170 p_ORGANIZATION_ID => d.organization_id,
1171 p_SUBINVENTORY_CODE => d.subinventory_code,
1172 p_CONDITION_CODE => 'B',
1173 p_INVENTORY_ITEM_ID => d.inventory_item_id,
1174 p_EXCESS_QUANTITY => d.excess_quantity,
1175 p_EXCESS_STATUS => 'O',
1176 p_REQUISITION_LINE_ID => null,
1177 p_RETURNED_QUANTITY => null,
1178 p_current_return_qty => null,
1179 p_ATTRIBUTE_CATEGORY => null,
1180 p_ATTRIBUTE1 => null,
1181 p_ATTRIBUTE2 => null,
1182 p_ATTRIBUTE3 => null,
1183 p_ATTRIBUTE4 => null,
1184 p_ATTRIBUTE5 => null,
1185 p_ATTRIBUTE6 => null,
1186 p_ATTRIBUTE7 => null,
1187 p_ATTRIBUTE8 => null,
1188 p_ATTRIBUTE9 => null,
1189 p_ATTRIBUTE10 => null,
1190 p_ATTRIBUTE11 => null,
1191 p_ATTRIBUTE12 => null,
1192 p_ATTRIBUTE13 => null,
1193 p_ATTRIBUTE14 => null,
1194 p_ATTRIBUTE15 => null);
1195 */
1196
1197 v_excess_part := CSP_EXCESS_LISTS_PKG.G_MISS_EXCESS_REC;
1198 v_excess_part.CREATED_BY := fnd_global.user_id;
1199 v_excess_part.CREATION_DATE := sysdate;
1200 v_excess_part.LAST_UPDATED_BY := fnd_global.user_id;
1201 v_excess_part.LAST_UPDATE_DATE := sysdate;
1202 v_excess_part.ORGANIZATION_ID := d.organization_id;
1203 v_excess_part.SUBINVENTORY_CODE := d.subinventory_code;
1204 v_excess_part.CONDITION_CODE := 'B';
1205 v_excess_part.INVENTORY_ITEM_ID := d.inventory_item_id;
1206 v_excess_part.EXCESS_QUANTITY := d.excess_quantity;
1207 v_excess_part.EXCESS_STATUS := 'O';
1208
1209 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1210 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1211 'csp.plsql.CSP_EXCESS_PARTS_PVT.excess_parts',
1212 'Calling populate_excess_list 4');
1213 end if;
1214
1215 populate_excess_list(v_excess_part);
1216 end if;
1217 end loop;
1218
1219 -- update CSP_SEC_INVENTORIES
1220 update CSP_SEC_INVENTORIES
1221 set last_excess_run_date = sysdate
1222 where organization_id = p_organization_id
1223 and secondary_inventory_name = nvl(p_subinventory_code, secondary_inventory_name);
1224
1225 commit;
1226 exception
1227 when others then
1228 null;
1229 end;
1230
1231 procedure clean_up(
1232 p_organization_id number,
1233 p_subinventory_code varchar2,
1234 p_condition_type varchar2) is
1235 begin
1236 if p_subinventory_code is null then
1237 delete from csp_excess_lists
1238 where organization_id = p_organization_id
1239 and condition_code = nvl(p_condition_type,condition_code)
1240 and excess_status = 'O';
1241 else
1242 delete from csp_excess_lists
1243 where organization_id = p_organization_id
1244 and subinventory_code = nvl(p_subinventory_code,subinventory_code)
1245 and condition_code = nvl(p_condition_type,condition_code)
1246 and excess_status = 'O';
1247 end if;
1248 commit;
1249 exception
1250 when no_data_found then
1251 null;
1252 when others then
1253 null;
1254 end;
1255
1256 PROCEDURE Build_Item_Cat_Select(p_Cat_structure_id IN NUMBER
1257 ,x_item_select OUT NOCOPY VARCHAR2
1258 ,x_cat_Select OUT NOCOPY VARCHAR2
1259 ) IS
1260 l_flexfield_rec FND_FLEX_KEY_API.flexfield_type;
1261 l_structure_rec FND_FLEX_KEY_API.structure_type;
1262 l_segment_rec FND_FLEX_KEY_API.segment_type;
1263 l_segment_tbl FND_FLEX_KEY_API.segment_list;
1264 l_segment_number NUMBER;
1265 l_mstk_segs VARCHAR2(850);
1266 l_mcat_segs VARCHAR2(850);
1267 BEGIN
1268 FND_FLEX_KEY_API.set_session_mode('customer_data');
1269
1270 -- retrieve system item concatenated flexfield
1271 l_mstk_segs := '';
1272 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MSTK');
1273 l_structure_rec := FND_FLEX_KEY_API.find_structure(l_flexfield_rec, 101);
1274 FND_FLEX_KEY_API.get_segments
1275 ( flexfield => l_flexfield_rec
1276 , structure => l_structure_rec
1277 , nsegments => l_segment_number
1278 , segments => l_segment_tbl
1279 );
1280 FOR l_idx IN 1..l_segment_number LOOP
1281 l_segment_rec := FND_FLEX_KEY_API.find_segment
1282 ( l_flexfield_rec
1283 , l_structure_rec
1284 , l_segment_tbl(l_idx)
1285 );
1286 l_mstk_segs := l_mstk_segs ||'C.'||l_segment_rec.column_name;
1287 IF l_idx < l_segment_number THEN
1288 l_mstk_segs := l_mstk_segs||'||'||l_structure_rec.segment_separator;
1289 END IF;
1290 END LOOP;
1291
1292 -- retrieve item category concatenated flexfield
1293 l_mcat_segs := '';
1294 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MCAT');
1295 l_structure_rec := FND_FLEX_KEY_API.find_structure
1296 ( l_flexfield_rec
1297 , p_cat_structure_id
1298 );
1299 FND_FLEX_KEY_API.get_segments
1300 ( flexfield => l_flexfield_rec
1301 , structure => l_structure_rec
1302 , nsegments => l_segment_number
1303 , segments => l_segment_tbl
1304 );
1305 FOR l_idx IN 1..l_segment_number LOOP
1306 l_segment_rec := FND_FLEX_KEY_API.find_segment
1307 ( l_flexfield_rec
1308 , l_structure_rec
1309 , l_segment_tbl(l_idx)
1310 );
1311 l_mcat_segs := l_mcat_segs ||'B.'||l_segment_rec.column_name;
1312 IF l_idx < l_segment_number THEN
1313 l_mcat_segs := l_mcat_segs||'||'||''''||
1314 l_structure_rec.segment_separator||''''||'||';
1315 END IF;
1316 END LOOP;
1317
1318 x_item_select := '('||l_mstk_Segs||')';
1319 x_cat_select := '('||l_mcat_Segs||')';
1320 END;
1321
1322 PROCEDURE Build_Range_Sql
1323 ( p_cat_structure_id IN NUMBER
1324 , p_cat_lo IN VARCHAR2
1325 , p_cat_hi IN VARCHAR2
1326 , p_item_lo IN VARCHAR2
1327 , p_item_hi IN VARCHAR2
1328 , p_planner_lo IN VARCHAR2
1329 , p_planner_hi IN VARCHAR2
1330 , p_lot_ctl IN NUMBER
1331 , x_range_sql OUT NOCOPY VARCHAR2
1332 )
1333 IS
1334 l_flexfield_rec FND_FLEX_KEY_API.flexfield_type;
1335 l_structure_rec FND_FLEX_KEY_API.structure_type;
1336 l_segment_rec FND_FLEX_KEY_API.segment_type;
1337 l_segment_tbl FND_FLEX_KEY_API.segment_list;
1338 l_segment_number NUMBER;
1339 l_mstk_segs VARCHAR2(850);
1340 l_mcat_segs VARCHAR2(850);
1341 --l_mcat_w VARCHAR2(2000);
1342 --l_mstk_w VARCHAR2(2000);
1343 l_range_sql VARCHAr2(2000);
1344 lx_range_sql VARCHAR2(4000) := '1=1';
1345 BEGIN
1346
1347 FND_FLEX_KEY_API.set_session_mode('customer_data');
1348
1349 -- retrieve system item concatenated flexfield
1350 l_mstk_segs := '';
1351 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MSTK');
1352 l_structure_rec := FND_FLEX_KEY_API.find_structure(l_flexfield_rec, 101);
1353 FND_FLEX_KEY_API.get_segments
1354 ( flexfield => l_flexfield_rec
1355 , structure => l_structure_rec
1356 , nsegments => l_segment_number
1357 , segments => l_segment_tbl
1358 );
1359 FOR l_idx IN 1..l_segment_number LOOP
1360 l_segment_rec := FND_FLEX_KEY_API.find_segment
1361 ( l_flexfield_rec
1362 , l_structure_rec
1363 , l_segment_tbl(l_idx)
1364 );
1365 l_mstk_segs := l_mstk_segs ||'C.'||l_segment_rec.column_name;
1366 IF l_idx < l_segment_number THEN
1367 l_mstk_segs := l_mstk_segs||'||'||l_structure_rec.segment_separator;
1368 END IF;
1369 END LOOP;
1370
1371 -- retrieve item category concatenated flexfield
1372 l_mcat_segs := '';
1373 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MCAT');
1374 l_structure_rec := FND_FLEX_KEY_API.find_structure
1375 ( l_flexfield_rec
1376 , p_cat_structure_id
1377 );
1378 FND_FLEX_KEY_API.get_segments
1379 ( flexfield => l_flexfield_rec
1380 , structure => l_structure_rec
1381 , nsegments => l_segment_number
1382 , segments => l_segment_tbl
1383 );
1384 FOR l_idx IN 1..l_segment_number LOOP
1385 l_segment_rec := FND_FLEX_KEY_API.find_segment
1386 ( l_flexfield_rec
1387 , l_structure_rec
1388 , l_segment_tbl(l_idx)
1389 );
1390 l_mcat_segs := l_mcat_segs ||'B.'||l_segment_rec.column_name;
1391 IF l_idx < l_segment_number THEN
1392 l_mcat_segs := l_mcat_segs||'||'||''''||
1393 l_structure_rec.segment_separator||''''||'||';
1394 END IF;
1395 END LOOP;
1396
1397 IF p_item_lo IS NOT NULL AND p_item_hi IS NOT NULL THEN
1398 l_range_sql := l_mstk_segs||' BETWEEN '''||p_item_lo||''''||
1399 ' AND '''||p_item_hi||'''';
1400 ELSIF p_item_lo IS NOT NULL AND p_item_hi IS NULL THEN
1401 l_range_sql := l_mstk_segs||' >= '''||p_item_lo||'''';
1402 ELSIF p_item_lo IS NULL AND p_item_hi IS NOT NULL THEN
1403 l_range_sql := l_mstk_segs||' <= '''||p_item_hi||'''';
1404 END IF;
1405
1406 IF (l_range_sql is not null) THEN
1407 lx_range_sql := l_range_sql;
1408 l_range_sql := null;
1409 END IF;
1410
1411 IF p_cat_lo IS NOT NULL AND p_cat_hi IS NOT NULL THEN
1412 l_range_sql := l_mcat_segs||' BETWEEN '''||p_cat_lo||''''||
1413 ' AND '''||p_cat_hi||'''';
1414 ELSIF p_cat_lo IS NOT NULL AND p_cat_hi IS NULL THEN
1415 l_range_Sql := l_mcat_segs||' >= '''||p_cat_lo||'''';
1416 ELSIF p_cat_lo IS NULL AND p_cat_hi IS NOT NULL THEN
1417 l_range_sql := l_mcat_segs||' <= '''||p_cat_hi||'''';
1418 END IF;
1419
1420 IF (l_range_Sql is not null) THEN
1421 lx_range_sql := lx_Range_sql || ' and' || l_range_Sql;
1422 l_range_sql := null;
1423 END IF;
1424
1425 if p_planner_lo is not null and p_planner_hi is not null then
1426 l_RANGE_SQL := 'c.planner_code between ' ||''''||P_planner_LO||'''' ||
1427 ' and '|| ''''||P_planner_HI||'''';
1428 elsif p_planner_lo is not null then
1429 l_RANGE_SQL := 'c.planner_code >= ' ||''''||P_planner_LO||'''';
1430 elsif p_PLANNER_hi is not null then
1431 l_RANGE_SQL := 'c.planner_code <= ' ||''''||P_PLANNER_HI||'''';
1432 end if;
1433
1434 if l_range_sql is not null then
1435 lx_range_sql := lx_range_sql||' and '|| l_range_sql;
1436 l_range_sql := null;
1437 end if;
1438
1439 if P_LOT_CTL = 1 then
1440 l_RANGE_SQL := 'c.lot_control_code = 2';
1441 elsif P_LOT_CTL = 2 then
1442 l_RANGE_SQL := 'c.lot_control_code <> 2';
1443 end if;
1444
1445 if l_range_sql is not null then
1446 lx_range_sql := lx_range_sql||' and '|| l_range_sql;
1447 l_range_sql := null;
1448 end if;
1449
1450 x_range_Sql := lx_range_sql;
1451 END;
1452
1453 FUNCTION get_business_rule(
1454 p_organization_id IN NUMBER,
1455 p_subinventory_code IN VARCHAR2)
1456 return number is
1457
1458 l_excess_rule_id number;
1459
1460 cursor subinventory_br is
1461 select cpp.excess_rule_id
1462 from csp_planning_parameters cpp
1463 where cpp.organization_id = p_organization_id
1464 and cpp.secondary_inventory = p_subinventory_code;
1465
1466 cursor organization_br is
1467 select cpp.excess_rule_id
1468 from csp_planning_parameters cpp
1469 where cpp.organization_id = p_organization_id
1470 and cpp.secondary_inventory is null;
1471
1472 begin
1473
1474 If p_organization_id is not null and p_subinventory_code is not null then
1475 open subinventory_br;
1476 fetch subinventory_br into l_excess_rule_id;
1477 close subinventory_br;
1478 elsif p_organization_id is not null and p_subinventory_code is null then
1479 open organization_br;
1480 fetch organization_br into l_excess_rule_id;
1481 close organization_br;
1482 end if;
1483 return(l_excess_rule_id);
1484 end;
1485
1486 FUNCTION onhand
1487 ( p_organization_id IN NUMBER,
1488 p_inventory_item_id IN NUMBER,
1489 p_subinventory_code IN VARCHAR2,
1490 p_revision_qty_control_code IN NUMBER,
1491 p_include_nonnet IN NUMBER,
1492 p_planning_level IN NUMBER
1493 )
1494 return number is
1495
1496 x_return_status VARCHAR2(1);
1497 x_msg_count NUMBER;
1498 x_msg_data VARCHAR2(2000);
1499 l_onhand_source NUMBER := 3;
1500 l_subinventory_code VARCHAR2(30);
1501 l_qoh NUMBER;
1502 l_rqoh NUMBER;
1503 l_qr NUMBER;
1504 l_qs NUMBER;
1505 l_att NUMBER;
1506 l_atr NUMBER;
1507 l_total_qoh NUMBER := null;
1508
1509 cursor revisions is
1510 select revision
1511 from mtl_item_revisions
1512 where organization_id = p_organization_id
1513 and inventory_item_id = p_inventory_item_id;
1514
1515 BEGIN
1516
1517 IF (p_include_nonnet = 2) THEN
1518 l_onhand_source := 2;
1519 END IF;
1520
1521 if p_revision_qty_control_code = 2 then -- Revision control
1522
1523 for r in revisions loop
1524 inv_quantity_tree_pub.query_quantities
1525 ( p_api_version_number => 1.0
1526 , p_organization_id => p_organization_id
1527 , p_inventory_item_id => p_inventory_item_id
1528 , p_subinventory_code => p_subinventory_code
1529 , x_qoh => l_qoh
1530 , x_atr => l_atr
1531 , p_init_msg_lst => fnd_api.g_false
1532 , p_tree_mode => inv_quantity_tree_pvt.g_transaction_mode
1533 , p_is_revision_control => TRUE
1534 , p_is_lot_control => NULL
1535 , p_is_serial_control => NULL
1536 , p_revision => r.revision
1537 , p_lot_number => NULL
1538 , p_locator_id => NULL
1539 , x_rqoh => l_rqoh
1540 , x_qr => l_qr
1541 , x_qs => l_qs
1542 , x_att => l_att
1543 , x_return_status => x_return_status
1544 , x_msg_count => x_msg_count
1545 , x_msg_data => x_msg_data
1546 );
1547
1548 -- qoh quantity on hand
1549 -- rqoh reservable quantity on hand
1550 -- qr quantity reserved
1551 -- att available to transact
1552 -- atr available to reserve
1553
1554 --l_total_qoh := nvl(l_total_qoh,0) + nvl(l_qoh,0);
1555 l_total_qoh := nvl(l_total_qoh,0) + nvl(l_atr,0);
1556 end loop;
1557 return(l_total_qoh);
1558
1559 else -- Not revision controlled item
1560
1561 Inv_quantity_tree_pub.query_quantities
1562 ( p_api_version_number => 1.0
1563 , p_organization_id => p_organization_id
1564 , p_inventory_item_id => p_inventory_item_id
1565 , p_subinventory_code => p_subinventory_code
1566 , x_qoh => l_qoh
1567 , x_atr => l_atr
1568 , p_init_msg_lst => fnd_api.g_false
1569 , p_tree_mode => inv_quantity_tree_pvt.g_transaction_mode
1570 , p_is_revision_control => NULL
1571 , p_is_lot_control => NULL
1572 , p_is_serial_control => NULL
1573 , p_revision => NULL
1574 , p_lot_number => NULL
1575 , p_locator_id => NULL
1576 , x_rqoh => l_rqoh
1577 , x_qr => l_qr
1578 , x_qs => l_qs
1579 , x_att => l_att
1580 , x_return_status => x_return_status
1581 , x_msg_count => x_msg_count
1582 , x_msg_data => x_msg_data
1583 );
1584 if x_return_status = 'S' then
1585 return(l_atr);
1586 else
1587 return(0);
1588 end if;
1589 end if;
1590 end;
1591
1592 function demand(
1593 p_organization_id number,
1594 p_inventory_item_id number,
1595 p_subinventory_code varchar2,
1596 p_include_nonnet number, -- 2
1597 p_planning_level number, -- 2
1598 p_net_unreserved number, -- 1
1599 p_net_reserved number, -- 1
1600 p_net_wip number, -- 1
1601 p_demand_cutoff number) -- number of days
1602 return Number is
1603
1604 qty number;
1605 total number;
1606 l_total_demand_qty number;
1607 l_demand_qty number;
1608 l_total_reserve_qty number;
1609
1610
1611 begin
1612 total := 0;
1613 l_total_demand_qty := 0;
1614 l_demand_qty := 0;
1615 l_total_reserve_qty := 0;
1616
1617 -- select unreserved qty from mtl_demand for non oe rows.
1618 select sum(PRIMARY_UOM_QUANTITY- GREATEST(NVL(RESERVATION_QUANTITY,0),nvl(COMPLETED_QUANTITY,0)))
1619 into qty
1620 from mtl_demand
1621 WHERE RESERVATION_TYPE = 1
1622 AND p_net_unreserved = 1
1623 AND parent_demand_id IS NULL
1624 AND ORGANIZATION_ID = p_organization_id
1625 and PRIMARY_UOM_QUANTITY > GREATEST(NVL(RESERVATION_QUANTITY,0),
1626 nvl(COMPLETED_QUANTITY,0))
1627
1628 and INVENTORY_ITEM_ID = p_inventory_item_id
1629 and REQUIREMENT_DATE <= sysdate + p_demand_cutoff
1630 and demand_source_type not in (2,8,12)
1631 and (p_planning_level = 1 or
1632 SUBINVENTORY = p_subinventory_code) -- Included later for ORG Level
1633 and (SUBINVENTORY is null or
1634 p_planning_level = 2 or
1635 EXISTS (SELECT 1
1636 FROM MTL_SECONDARY_INVENTORIES S
1637 WHERE S.ORGANIZATION_ID = p_organization_id
1638 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY
1639 AND S.availability_type = DECODE(p_include_nonnet,
1640 1,
1641 S.availability_type,
1642 1)));
1643
1644 total := total + nvl(qty,0);
1645
1646
1647 -- select the reserved quantity from mtl_reservations for non OE rows
1648 select sum(PRIMARY_RESERVATION_QUANTITY)
1649 into qty
1650 from mtl_reservations
1651 where p_net_reserved = 1
1652 and ORGANIZATION_ID = p_organization_id
1653 and INVENTORY_ITEM_ID = p_inventory_item_id
1654 and REQUIREMENT_DATE <= sysdate + p_demand_cutoff
1655 and demand_source_type_id not in (2,8,12)
1656 and (p_planning_level = 1 or
1657 SUBINVENTORY_CODE = p_subinventory_code) -- Included later for ORG Level
1658 and (SUBINVENTORY_CODE is null or
1659 p_planning_level = 2 or
1660 EXISTS (SELECT 1
1661 FROM MTL_SECONDARY_INVENTORIES S
1662 WHERE S.ORGANIZATION_ID = p_organization_id
1663 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
1664 AND S.availability_type = DECODE(p_include_nonnet,
1665 1,
1666 S.availability_type,
1667 1)));
1668
1669 total := total + nvl(qty,0);
1670
1671
1672 -- get the total demand which is the difference between the
1673 -- ordered qty. and the shipped qty.
1674 -- This gives the total demand including the reserved
1675 -- and the unreserved material.
1676 if p_net_unreserved = 1 then
1677 select SUM(INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(SHIP_FROM_ORG_ID,
1678 INVENTORY_ITEM_ID, ORDER_QUANTITY_UOM, Nvl(ordered_quantity,0)) -
1679 get_shipped_qty(p_organization_id,p_inventory_item_id, ool.line_id))
1680 into l_total_demand_qty
1681 from oe_order_lines_all ool
1682 where ship_from_org_id = p_organization_id
1683 and open_flag = 'Y'
1684 and INVENTORY_ITEM_ID = p_inventory_item_id
1685 and schedule_ship_date <= sysdate + p_demand_cutoff
1686 AND DECODE(OOL.SOURCE_DOCUMENT_TYPE_ID, 10, 8,DECODE(OOL.LINE_CATEGORY_CODE, 'ORDER',2,12)) IN (2,8,12)
1687 and ((p_planning_level = 1 AND DECODE(OOL.SOURCE_DOCUMENT_TYPE_ID, 10, 8,DECODE(OOL.LINE_CATEGORY_CODE, 'ORDER',2,12)) <> 8 ) OR
1688 SUBINVENTORY = p_subinventory_code) -- Included later for ORG Level
1689 and (SUBINVENTORY is null or
1690 p_planning_level = 2 or
1691 EXISTS (SELECT 1
1692 FROM MTL_SECONDARY_INVENTORIES S
1693 WHERE S.ORGANIZATION_ID = p_organization_id
1694 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY
1695 AND S.availability_type = DECODE(p_include_nonnet,
1696 1,
1697 S.availability_type,
1698 1)));
1699
1700 end if;
1701
1702 -- find out the reserved qty for the material from mtl_reservations
1703 if ((p_net_reserved = 1 or p_net_unreserved = 1) and
1704 (nvl(p_net_reserved,0) <> 1 and nvl(p_net_unreserved,0) <> 1)) then
1705 select sum(PRIMARY_RESERVATION_QUANTITY)
1706 into l_total_reserve_qty
1707 from mtl_reservations
1708 WHERE ORGANIZATION_ID = p_organization_id
1709 and INVENTORY_ITEM_ID = p_inventory_item_id
1710 and REQUIREMENT_DATE <= sysdate + p_demand_cutoff
1711 and demand_source_type_id in (2,8,12)
1712 and ((p_planning_level = 1 AND demand_source_type_id <> 8 ) OR
1713 SUBINVENTORY_CODE = p_subinventory_code) -- Included later for ORG Level
1714 and (SUBINVENTORY_CODE is null or
1715 p_planning_level = 2 or
1716 EXISTS (SELECT 1
1717 FROM MTL_SECONDARY_INVENTORIES S
1718 WHERE S.ORGANIZATION_ID = p_organization_id
1719 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
1720 AND S.availability_type = DECODE(p_include_nonnet,
1721 1,
1722 S.availability_type,
1723 1)));
1724 end if;
1725
1726 -- total demand is calculated as follows:
1727 -- if we have to consider both unreserved matl and reserved matl. then the
1728 -- demand is simply the total demand = ordered qty - shipped qty.
1729 -- elsif we have to take into account only reserved matl. then the
1730 -- demand is simply the reservations from mtl_reservations for the matl.
1731 -- elsif we have to take into account just the unreserved matl. then the
1732 -- demand is total demand - the reservations for the material.
1733 if p_net_unreserved = 1 and p_net_reserved = 1 then
1734 l_demand_qty := Nvl(l_total_demand_qty,0);
1735 elsif p_net_reserved = 1 then
1736 l_demand_qty := Nvl(l_total_reserve_qty,0);
1737 elsif p_net_unreserved = 1 then
1738 l_demand_qty := Nvl(l_total_demand_qty,0) - Nvl(l_total_reserve_qty,0);
1739 end if;
1740 total := total + nvl(l_demand_qty,0);
1741
1742
1743 -- Take care of internal orders for org level planning
1744 if p_planning_level = 1 then
1745 l_total_demand_qty := 0;
1746 l_demand_qty := 0;
1747 l_total_reserve_qty := 0;
1748
1749 -- get the total demand which is the difference between the
1750 -- ordered qty. and the shipped qty.
1751 -- This gives the total demand including the reserved
1752 -- and the unreserved material.
1753 if p_net_unreserved = 1 then
1754 select SUM(INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(SHIP_FROM_ORG_ID,
1755 INVENTORY_ITEM_ID, ORDER_QUANTITY_UOM, Nvl(ordered_quantity,0)) -
1756 get_shipped_qty(p_organization_id,p_inventory_item_id, so.line_id))
1757 into l_total_demand_qty
1758 from oe_order_lines_all so,
1759 po_requisition_headers_all poh,
1760 po_requisition_lines_all pol
1761 where so.ORIG_SYS_DOCUMENT_REF = poh.segment1
1762 and poh.requisition_header_id = pol .requisition_header_id
1763 and so.orig_sys_line_ref = pol.line_num
1764 and ( pol.DESTINATION_ORGANIZATION_ID <> p_organization_id or
1765 (pol.DESTINATION_ORGANIZATION_ID = p_organization_id and -- Added code Bug#1012179
1766 pol.DESTINATION_TYPE_CODE = 'EXPENSE')
1767 )
1768 and so.ship_from_org_ID = p_organization_id
1769 and so.open_flag = 'Y'
1770 and so.INVENTORY_ITEM_ID = p_inventory_item_id
1771 and schedule_ship_date <= sysdate + p_demand_cutoff
1772 and DECODE(so.SOURCE_DOCUMENT_TYPE_ID, 10, 8,DECODE(so.LINE_CATEGORY_CODE, 'ORDER',2,12)) = 8
1773 and (SUBINVENTORY is null or
1774 EXISTS (SELECT 1
1775 FROM MTL_SECONDARY_INVENTORIES S
1776 WHERE S.ORGANIZATION_ID = p_organization_id
1777 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY
1778 AND S.availability_type = DECODE(p_include_nonnet,
1779 1,
1780 S.availability_type,
1781 1)));
1782 end if;
1783
1784 -- find out the reserved qty for the material from mtl_reservations
1785 if ((p_net_reserved = 1 or p_net_unreserved = 1) and
1786 (nvl(p_net_reserved,0) <> 1 and nvl(p_net_unreserved,0) <> 1)) then
1787 -- Include the reserved demand from mtl_reservations
1788 select sum(PRIMARY_RESERVATION_QUANTITY)
1789 into l_total_reserve_qty
1790 from mtl_reservations md, oe_order_lines_all so,
1791 po_req_distributions_all pod,
1792 po_requisition_lines_all pol
1793 where md.DEMAND_SOURCE_LINE_ID = so.LINE_ID
1794 and so.ORIG_SYS_LINE_REF = pod.DISTRIBUTION_ID
1795 and pod.REQUISITION_LINE_ID = pol.REQUISITION_LINE_ID
1796 and (pol.DESTINATION_ORGANIZATION_ID <> p_organization_id or
1797 (pol.DESTINATION_ORGANIZATION_ID = p_organization_id
1798 and -- Added code Bug#1012179
1799 pol.DESTINATION_TYPE_CODE = 'EXPENSE')
1800 )
1801 and ORGANIZATION_ID = p_organization_id
1802 and md.INVENTORY_ITEM_ID = p_inventory_item_id
1803 and REQUIREMENT_DATE <= sysdate + p_demand_cutoff
1804 and demand_source_type_id = 8
1805 and (SUBINVENTORY_CODE is null or
1806 EXISTS (SELECT 1
1807 FROM MTL_SECONDARY_INVENTORIES S
1808 WHERE S.ORGANIZATION_ID = p_organization_id
1809 AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
1810 AND S.availability_type = DECODE(p_include_nonnet,
1811 1,
1812 S.availability_type,
1813 1)));
1814
1815 end if;
1816
1817 -- total demand is calculated as follows:
1818 -- if we have to consider both unreserved matl and reserved matl. then the
1819 -- demand is simply the total demand = ordered qty - shipped qty.
1820 -- elsif we have to take into account only reserved matl. then the
1821 -- demand is simply the reservations from mtl_reservations for the matl.
1822 -- elsif we have to take into account just the unreserved matl. then the
1823 -- demand is total demand - the reservations for the material.
1824 if p_net_unreserved = 1 and p_net_reserved = 1 then
1825 l_demand_qty := Nvl(l_total_demand_qty,0);
1826 elsif p_net_reserved = 1 then
1827 l_demand_qty := Nvl(l_total_reserve_qty,0);
1828 elsif p_net_unreserved = 1 then
1829 l_demand_qty := Nvl(l_total_demand_qty,0) - Nvl(l_total_reserve_qty,0);
1830 end if;
1831 total := total + nvl(l_demand_qty,0);
1832 end if;
1833
1834 -- WIP Reservations from mtl_demand
1835 select sum(PRIMARY_UOM_QUANTITY - GREATEST(NVL(RESERVATION_QUANTITY,0),
1836 nvl(COMPLETED_QUANTITY,0)))
1837 into qty
1838 from mtl_demand
1839 where RESERVATION_TYPE = 3
1840 and ORGANIZATION_ID = p_organization_id
1841 and PRIMARY_UOM_QUANTITY > GREATEST(NVL(RESERVATION_QUANTITY,0),
1842 nvl(COMPLETED_QUANTITY,0))
1843 and INVENTORY_ITEM_ID = p_inventory_item_id
1844 and REQUIREMENT_DATE <= sysdate + p_demand_cutoff
1845 and p_net_reserved = 1
1846 and p_planning_level = 1;
1847
1848 -- SUBINVENTORY IS Always expected to be Null when Reservation_type is 3.
1849
1850 total := total + nvl(qty,0);
1851
1852 -- Wip Components are to be included at the Org Level Planning only
1853 -- Qty Issued Substracted from the Qty Required
1854 if (p_net_wip = 1 and p_planning_level = 1)
1855 then
1856 select sum(o.required_quantity - o.quantity_issued)
1857 into qty
1858 from wip_discrete_jobs d, wip_requirement_operations o
1859 where o.wip_entity_id = d.wip_entity_id
1860 and o.organization_id = d.organization_id
1861 and d.organization_id = p_organization_id
1862 and o.inventory_item_id = p_inventory_item_id
1863 and o.date_required <= sysdate + p_demand_cutoff
1864 and o.required_quantity > 0
1865 and o.required_quantity > o.quantity_issued
1866 and o.operation_seq_num > 0
1867 and d.status_type in (1,3,4,6) -- Excluded 5 from selection Bug#1016495
1868 and o.wip_supply_type <> 6;
1869 total := total + nvl(qty,0);
1870 -- Demand Qty to be added for a released repetitve schedule
1871 -- Bug#691471
1872 select sum(o.required_quantity - o.quantity_issued)
1873 into qty
1874 from wip_repetitive_schedules r, wip_requirement_operations o
1875 where o.wip_entity_id = r.wip_entity_id
1876 and o.organization_id = r.organization_id
1877 and r.organization_id = p_organization_id
1878 and o.inventory_item_id = p_inventory_item_id
1879 and o.date_required <= sysdate + p_demand_cutoff
1880 and o.required_quantity > 0
1881 and o.required_quantity > o.quantity_issued
1882 and o.operation_seq_num > 0
1883 and r.status_type in (1,3,4,6) -- Excluded 5 from selection Bug#1016495
1884 and o.wip_supply_type <> 6;
1885 total := total + nvl(qty,0);
1886 end if;
1887
1888
1889 -- Include move orders
1890 -- leave out the closed or cancelled lines
1891 -- select only the issue from stores for org level planning
1892 -- Also select those lines for the sub level planning.
1893 /* SELECT sum(quantity - Nvl(quantity_delivered,0))
1894 INTO qty
1895 FROM mtl_txn_request_lines_v
1896 WHERE organization_id = p_organization_id
1897 AND inventory_item_id = p_inventory_item_id
1898 AND line_status NOT IN (5,6)
1899 AND transaction_action_id = 1
1900 AND (p_planning_level = 1 or
1901 from_subinventory_code = p_subinventory_code) -- Included later for ORG Level
1902 AND ( from_subinventory_code is null or
1903 p_planning_level = 2 or
1904 EXISTS (SELECT 1
1905 FROM MTL_SECONDARY_INVENTORIES S
1906 WHERE S.ORGANIZATION_ID = p_organization_id
1907 AND S.SECONDARY_INVENTORY_NAME = from_subinventory_code
1908 AND S.availability_type = DECODE(p_include_nonnet,
1909 1,S.availability_type,1)))
1910 AND date_required <= sysdate + p_demand_cutoff;
1911 */
1912
1913 SELECT SUM(MTRL.QUANTITY - NVL(MTRL.QUANTITY_DELIVERED,0))
1914 INTO qty
1915 FROM MTL_TXN_REQUEST_LINES MTRL,
1916 MTL_TRANSACTION_TYPES MTT
1917 WHERE MTT.TRANSACTION_TYPE_ID = MTRL.TRANSACTION_TYPE_ID
1918 AND MTRL.ORGANIZATION_ID = p_organization_id
1919 AND MTRL.INVENTORY_ITEM_ID = p_inventory_item_id
1920 AND MTRL.LINE_STATUS NOT IN (5,6)
1921 AND MTT.TRANSACTION_ACTION_ID = 1
1922 AND (p_planning_level = 1 OR
1923 MTRL.FROM_SUBINVENTORY_CODE = p_subinventory_code)
1924 AND (MTRL.FROM_SUBINVENTORY_CODE IS NULL OR
1925 p_planning_level = 2 OR
1926 EXISTS (SELECT 1
1927 FROM MTL_SECONDARY_INVENTORIES S
1928 WHERE S.ORGANIZATION_ID = p_organization_id
1929 AND S.SECONDARY_INVENTORY_NAME = MTRL.FROM_SUBINVENTORY_CODE
1930 AND S.AVAILABILITY_TYPE = DECODE(p_include_nonnet,
1931 1,S.AVAILABILITY_TYPE,1)))
1932 AND MTRL.DATE_REQUIRED <= sysdate + p_demand_cutoff;
1933
1934
1935 total := total + Nvl(qty,0);
1936
1937 -- Include the sub transfer and the staging transfer move orders
1938 -- for sub level planning
1939 SELECT sum(quantity - Nvl(quantity_delivered,0))
1940 INTO qty
1941 FROM mtl_txn_request_lines_v
1942 WHERE organization_id = p_organization_id
1943 AND inventory_item_id = p_inventory_item_id
1944 AND line_status NOT IN (5,6)
1945 AND transaction_action_id IN (2,28)
1946 AND p_planning_level = 2
1947 AND from_subinventory_code = p_subinventory_code
1948 AND date_required <= sysdate + p_demand_cutoff;
1949 total := total + Nvl(qty,0);
1950
1951 return(total);
1952 exception
1953 when others then
1954 return(0);
1955 end;
1956
1957 function get_shipped_qty
1958 (p_organization_id IN NUMBER,
1959 p_inventory_item_id IN NUMBER,
1960 p_order_line_id IN NUMBER
1961 ) return NUMBER
1962 IS
1963 l_shipped_qty NUMBER := 0;
1964 BEGIN
1965 BEGIN
1966 SELECT SUM(primary_quantity)
1967 INTO l_shipped_qty
1968 FROM mtl_material_transactions
1969 WHERE transaction_action_id = 1
1970 AND source_line_id = p_order_line_id
1971 AND organization_id = p_organization_id
1972 AND inventory_item_id = p_inventory_item_id;
1973 EXCEPTION
1974 WHEN OTHERS THEN
1975 l_shipped_qty := 0;
1976 END ;
1977
1978 IF l_shipped_qty IS NULL THEN l_shipped_qty := 0;
1979 ELSE l_shipped_qty := -1 * l_shipped_qty;
1980 END IF;
1981
1982 RETURN l_shipped_qty;
1983 END get_shipped_qty;
1984
1985 -- added by htank for Reverse Logistic project
1986 PROCEDURE populate_excess_list (
1987 p_excess_part IN OUT nocopy CSP_EXCESS_LISTS_PKG.EXCESS_RECORD_TYPE,
1988 p_is_insert_record IN VARCHAR2 default 'Y'
1989 ) IS
1990
1991 CURSOR c_get_return_info (cv_ORGANIZATION_ID NUMBER,
1992 cv_SUBINVENTORY_CODE VARCHAR2) IS
1993 select
1994 CSI.RETURN_ORGANIZATION_ID,
1995 CSI.RETURN_SUBINVENTORY_NAME
1996 from
1997 CSP_SEC_INVENTORIES CSI
1998 where
1999 CSI.SECONDARY_INVENTORY_NAME = cv_SUBINVENTORY_CODE
2000 and CSI.ORGANIZATION_ID = cv_ORGANIZATION_ID;
2001
2002 v_ret_org_id NUMBER;
2003 v_ret_sub_inv VARCHAR2(10);
2004 v_excess_records CSP_EXCESS_LISTS_PKG.EXCESS_TBL_TYPE;
2005 v_excess_part CSP_EXCESS_LISTS_PKG.EXCESS_RECORD_TYPE;
2006 x_excess_line_id NUMBER;
2007
2008 -- routing rule change
2009 v_return_rule_id number;
2010 v_return_type varchar2(1);
2011 x_return_status varchar2(1);
2012 x_msg_count number;
2013 x_msg_data varchar2(4000);
2014 v_rule_dest_org_id number;
2015 v_rule_dest_subinv varchar2(30);
2016
2017 cursor get_rule_destination is
2018 SELECT dest_org_id,
2019 dest_subinv
2020 FROM csp_return_routing_rules
2021 WHERE rule_id = v_return_rule_id;
2022 BEGIN
2023
2024 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2025 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2026 'csp.plsql.CSP_EXCESS_PARTS_PVT.populate_excess_list',
2027 'Begin...');
2028 end if;
2029
2030 IF p_excess_part.ORGANIZATION_ID IS NOT NULL THEN
2031
2032 -- set default return information
2033
2034 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2035 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2036 'csp.plsql.CSP_EXCESS_PARTS_PVT.populate_excess_list',
2037 'Fetching return information from c_get_return_info');
2038 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2039 'csp.plsql.CSP_EXCESS_PARTS_PVT.populate_excess_list',
2040 'p_excess_part.ORGANIZATION_ID = '
2041 || p_excess_part.ORGANIZATION_ID);
2042 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2043 'csp.plsql.CSP_EXCESS_PARTS_PVT.populate_excess_list',
2044 'p_excess_part.SUBINVENTORY_CODE = '
2045 || p_excess_part.SUBINVENTORY_CODE);
2046 end if;
2047
2048 OPEN c_get_return_info(p_excess_part.ORGANIZATION_ID,
2049 p_excess_part.SUBINVENTORY_CODE);
2050 FETCH c_get_return_info INTO v_ret_org_id, v_ret_sub_inv;
2051 CLOSE c_get_return_info;
2052
2053 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2054 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2055 'csp.plsql.CSP_EXCESS_PARTS_PVT.populate_excess_list',
2056 'Return information from c_get_return_info');
2057 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2058 'csp.plsql.CSP_EXCESS_PARTS_PVT.populate_excess_list',
2059 'v_ret_org_id = ' || v_ret_org_id);
2060 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2061 'csp.plsql.CSP_EXCESS_PARTS_PVT.populate_excess_list',
2062 'v_ret_sub_inv = ' || v_ret_sub_inv);
2063 end if;
2064
2065 -- call custom code to override return information if any
2066 v_excess_part := p_excess_part;
2067 v_excess_part.RETURN_ORGANIZATION_ID := v_ret_org_id;
2068 v_excess_part.RETURN_SUBINVENTORY_NAME := v_ret_sub_inv;
2069
2070 -- New Retun Routing Rules
2071 -- After populating Destination Based on the Setup at
2072 -- Resource Addresses and Subinventort form, we will override it
2073 -- if we found any return routing rule for the given inputs
2074
2075 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2076 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2077 'csp.plsql.CSP_EXCESS_PARTS_PVT.populate_excess_list',
2078 'Calling find_best_routing_rule....');
2079 end if;
2080
2081 if v_excess_part.CONDITION_CODE = 'G' then
2082 v_return_type := 'E'; -- Excess
2083 elsif v_excess_part.CONDITION_CODE = 'A' then
2084 v_return_type := 'A'; -- DOA
2085 else
2086 v_return_type := 'D'; -- Defective
2087 end if;
2088
2089 find_best_routing_rule (
2090 p_source_type => 'I' -- Always Internal Source
2091 , p_source_org_id => v_excess_part.ORGANIZATION_ID
2092 , p_source_subinv => v_excess_part.SUBINVENTORY_CODE
2093 , p_source_terr_id => NULL
2094 , p_ret_trans_type => v_return_type
2095 , p_item_id => v_excess_part.INVENTORY_ITEM_ID
2096 , x_rule_id => v_return_rule_id
2097 , x_return_status => x_return_status
2098 , x_msg_count => x_msg_count
2099 , x_msg_data => x_msg_data
2100 );
2101
2102 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2103 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2104 'csp.plsql.CSP_EXCESS_PARTS_PVT.populate_excess_list',
2105 'returned from find_best_routing_rule with x_return_status = ' || x_return_status
2106 || ' and v_return_rule_id = ' || v_return_rule_id);
2107 end if;
2108
2109 if v_return_rule_id is not null then
2110 open get_rule_destination;
2111 fetch get_rule_destination into v_rule_dest_org_id, v_rule_dest_subinv;
2112 close get_rule_destination;
2113
2114 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2115 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2116 'csp.plsql.CSP_EXCESS_PARTS_PVT.populate_excess_list',
2117 'Destination for rule is: v_rule_dest_org_id=' || v_rule_dest_org_id
2118 || ', v_rule_dest_subinv=' || v_rule_dest_subinv);
2119 end if;
2120
2121 if v_rule_dest_org_id is not null then
2122 v_excess_part.RETURN_ORGANIZATION_ID := v_rule_dest_org_id;
2123 v_excess_part.RETURN_SUBINVENTORY_NAME := v_rule_dest_subinv;
2124 end if;
2125 end if;
2126
2127 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2128 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2129 'csp.plsql.CSP_EXCESS_PARTS_PVT.populate_excess_list',
2130 'Calling custom code...CSP_EXCESS_PARTS_CUST.excess_parts');
2131 end if;
2132
2133 v_excess_records := CSP_EXCESS_PARTS_CUST.excess_parts(v_excess_part);
2134
2135 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2136 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2137 'csp.plsql.CSP_EXCESS_PARTS_PVT.populate_excess_list',
2138 'Got v_excess_records with count = ' || v_excess_records.count);
2139 end if;
2140
2141 FOR i IN 1..v_excess_records.count LOOP
2142 v_excess_part := v_excess_records(i);
2143
2144 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2145 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2146 'csp.plsql.CSP_EXCESS_PARTS_PVT.populate_excess_list',
2147 'p_is_insert_record = ' || p_is_insert_record);
2148 end if;
2149
2150 if nvl(p_is_insert_record, 'N') = 'Y' then
2151
2152 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2153 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2154 'csp.plsql.CSP_EXCESS_PARTS_PVT.populate_excess_list',
2155 'Calling csp_excess_lists_pkg.Insert_Row for i = ' || i);
2156 end if;
2157
2158 csp_excess_lists_pkg.Insert_Row(
2159 px_EXCESS_LINE_ID => x_excess_line_id,
2160 p_CREATED_BY => v_excess_part.CREATED_BY,
2161 p_CREATION_DATE => v_excess_part.CREATION_DATE,
2162 p_LAST_UPDATED_BY => v_excess_part.LAST_UPDATED_BY,
2163 p_LAST_UPDATE_DATE => v_excess_part.LAST_UPDATE_DATE,
2164 p_LAST_UPDATE_LOGIN => v_excess_part.LAST_UPDATE_LOGIN,
2165 p_ORGANIZATION_ID => v_excess_part.ORGANIZATION_ID,
2166 p_SUBINVENTORY_CODE => v_excess_part.SUBINVENTORY_CODE,
2167 p_CONDITION_CODE => v_excess_part.CONDITION_CODE,
2168 p_INVENTORY_ITEM_ID => v_excess_part.INVENTORY_ITEM_ID,
2169 p_EXCESS_QUANTITY => v_excess_part.EXCESS_QUANTITY,
2170 p_EXCESS_STATUS => v_excess_part.EXCESS_STATUS,
2171 p_RETURN_ORG_ID => v_excess_part.RETURN_ORGANIZATION_ID,
2172 p_RETURN_SUB_INV => v_excess_part.RETURN_SUBINVENTORY_NAME,
2173 p_REQUISITION_LINE_ID => v_excess_part.REQUISITION_LINE_ID,
2174 p_RETURNED_QUANTITY => v_excess_part.RETURNED_QUANTITY,
2175 p_current_return_qty => v_excess_part.CURRENT_RETURN_QTY,
2176 p_ATTRIBUTE_CATEGORY => v_excess_part.ATTRIBUTE_CATEGORY,
2177 p_ATTRIBUTE1 => v_excess_part.ATTRIBUTE1,
2178 p_ATTRIBUTE2 => v_excess_part.ATTRIBUTE2,
2179 p_ATTRIBUTE3 => v_excess_part.ATTRIBUTE3,
2180 p_ATTRIBUTE4 => v_excess_part.ATTRIBUTE4,
2181 p_ATTRIBUTE5 => v_excess_part.ATTRIBUTE5,
2182 p_ATTRIBUTE6 => v_excess_part.ATTRIBUTE6,
2183 p_ATTRIBUTE7 => v_excess_part.ATTRIBUTE7,
2184 p_ATTRIBUTE8 => v_excess_part.ATTRIBUTE8,
2185 p_ATTRIBUTE9 => v_excess_part.ATTRIBUTE9,
2186 p_ATTRIBUTE10 => v_excess_part.ATTRIBUTE10,
2187 p_ATTRIBUTE11 => v_excess_part.ATTRIBUTE11,
2188 p_ATTRIBUTE12 => v_excess_part.ATTRIBUTE12,
2189 p_ATTRIBUTE13 => v_excess_part.ATTRIBUTE13,
2190 p_ATTRIBUTE14 => v_excess_part.ATTRIBUTE14,
2191 p_ATTRIBUTE15 => v_excess_part.ATTRIBUTE15);
2192
2193 x_excess_line_id := null;
2194 end if;
2195 END LOOP;
2196
2197 END IF;
2198
2199 p_excess_part := v_excess_part;
2200
2201 END; -- End of populate_excess_list
2202
2203 -- This procedure will return the best match Return Routing
2204 -- Rule ID for given inputs
2205 -- This will be called by the Setup UI's simulation process
2206 -- and also, it will be called by the actual destination default
2207 -- process.
2208 procedure find_best_routing_rule (
2209 p_source_type IN VARCHAR2
2210 , p_source_org_id IN NUMBER
2211 , p_source_subinv IN VARCHAR2
2212 , p_source_terr_id IN NUMBER
2213 , p_ret_trans_type IN VARCHAR2
2214 , p_item_id IN NUMBER
2215 , x_rule_id OUT NOCOPY NUMBER
2216 , x_return_status OUT NOCOPY VARCHAR2
2217 , x_msg_count OUT NOCOPY NUMBER
2218 , x_msg_data OUT NOCOPY VARCHAR2
2219 ) IS
2220
2221 v_match_rule_id number;
2222 v_match_rule_weight number;
2223
2224 cursor c_Scan_Subinv
2225 (
2226 v_source_org_id number,
2227 v_source_subinv varchar2,
2228 v_ret_trans_type varchar2,
2229 v_item_id number
2230 )
2231 is
2232 select * from (
2233 select distinct
2234 (decode(r.source_org_id, v_source_org_id, 1, 0)+
2235 decode(r.source_subinv, v_source_subinv, 2, 0)+
2236 decode(r.return_type, v_ret_trans_type, 4, 0)+
2237 decode(r.inv_cat_set_id, mtl.category_set_id, 8, 0)+
2238 decode(r.inv_cat_id, mtl.category_id, 16, 0)+
2239 decode(r.inv_item_id, v_item_id, 32, 0)) as weigth,
2240 r.rule_id
2241 from
2242 (
2243 select rule_id from csp_return_routing_rules where source_org_id = v_source_org_id
2244 and nvl(source_type, 'I') = 'I' and enabled = 'Y'
2245 and sysdate between nvl(start_active_date, sysdate)
2246 and nvl(end_active_date, sysdate + 1)
2247 union
2248 select rule_id from csp_return_routing_rules where source_subinv = v_source_subinv
2249 and nvl(source_type, 'I') = 'I' and enabled = 'Y'
2250 and sysdate between nvl(start_active_date, sysdate)
2251 and nvl(end_active_date, sysdate + 1)
2252 union
2253 select rule_id from csp_return_routing_rules where return_type = v_ret_trans_type
2254 and nvl(source_type, 'I') = 'I' and enabled = 'Y'
2255 and sysdate between nvl(start_active_date, sysdate)
2256 and nvl(end_active_date, sysdate + 1)
2257 union
2258 select rule_id from csp_return_routing_rules
2259 where inv_cat_set_id in (select distinct category_set_id
2260 from MTL_ITEM_CATEGORIES
2261 where inventory_item_id = v_item_id
2262 and organization_id = v_source_org_id)
2263 and nvl(source_type, 'I') = 'I' and enabled = 'Y'
2264 and sysdate between nvl(start_active_date, sysdate)
2265 and nvl(end_active_date, sysdate + 1)
2266 union
2267 select rule_id from csp_return_routing_rules
2268 where inv_cat_id in (select distinct category_id
2269 from MTL_ITEM_CATEGORIES
2270 where inventory_item_id = v_item_id
2271 and organization_id = v_source_org_id)
2272 and nvl(source_type, 'I') = 'I' and enabled = 'Y'
2273 and sysdate between nvl(start_active_date, sysdate)
2274 and nvl(end_active_date, sysdate + 1)
2275 union
2276 select rule_id from csp_return_routing_rules where inv_item_id = v_item_id
2277 and nvl(source_type, 'I') = 'I' and enabled = 'Y'
2278 and sysdate between nvl(start_active_date, sysdate)
2279 and nvl(end_active_date, sysdate + 1)
2280 union
2281 select rule_id from csp_return_routing_rules
2282 where nvl(source_type, 'I') = 'I' and enabled = 'Y'
2283 and sysdate between nvl(start_active_date, sysdate)
2284 and nvl(end_active_date, sysdate + 1)
2285 and decode(source_org_id, 0, null, source_org_id) is null
2286 and source_subinv is null
2287 and return_type is null
2288 and decode(inv_cat_set_id, 0, null, inv_cat_set_id) is null
2289 and decode(inv_cat_id, 0, null, inv_cat_id) is null
2290 and decode(inv_item_id, 0, null, inv_item_id) is null
2291 ) prob,
2292 csp_return_routing_rules r,
2293 MTL_ITEM_CATEGORIES mtl
2294 where r.rule_id = prob.rule_id
2295 and mtl.inventory_item_id = v_item_id
2296 and mtl.organization_id = v_source_org_id
2297 and nvl(decode(r.source_org_id, 0, null, r.source_org_id), v_source_org_id) = v_source_org_id
2298 and nvl(r.source_subinv, nvl(v_source_subinv, 'NULL')) = nvl(v_source_subinv, 'NULL')
2299 and nvl(r.return_type, v_ret_trans_type) = v_ret_trans_type
2300 and nvl(decode(r.inv_cat_set_id, 0, null, r.inv_cat_set_id), mtl.category_set_id) = mtl.category_set_id
2301 and nvl(decode(r.inv_cat_id, 0, null, r.inv_cat_id), mtl.category_id) = mtl.category_id
2302 and nvl(decode(r.inv_item_id, 0, null, r.inv_item_id), v_item_id) = v_item_id
2303 order by 1 desc
2304 ) intable where rownum = 1;
2305
2306 cursor c_Scan_Terr
2307 (
2308 v_source_terr_id number,
2309 v_source_terr_type varchar2,
2310 v_ret_trans_type varchar2,
2311 v_item_id number
2312 )
2313 is
2314 select * from (
2315 select distinct
2316 (decode(r.source_terr_id, v_source_terr_id, 1, 0)+
2317 decode(r.return_type, v_ret_trans_type, 4, 0)+
2318 decode(r.inv_cat_set_id, mtl.category_set_id, 8, 0)+
2319 decode(r.inv_cat_id, mtl.category_id, 16, 0)+
2320 decode(r.inv_item_id, v_item_id, 32, 0)) as weigth,
2321 r.rule_id
2322 from
2323 (
2324 select rule_id from csp_return_routing_rules where source_terr_id = v_source_terr_id
2325 and nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
2326 and sysdate between nvl(start_active_date, sysdate)
2327 and nvl(end_active_date, sysdate + 1)
2328 union
2329 select rule_id from csp_return_routing_rules where return_type = v_ret_trans_type
2330 and nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
2331 and sysdate between nvl(start_active_date, sysdate)
2332 and nvl(end_active_date, sysdate + 1)
2333 union
2334 select rule_id from csp_return_routing_rules
2335 where inv_cat_set_id in (select distinct category_set_id
2336 from MTL_ITEM_CATEGORIES
2337 where inventory_item_id = v_item_id
2338 and organization_id = cs_std.get_item_valdn_orgzn_id)
2339 and nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
2340 and sysdate between nvl(start_active_date, sysdate)
2341 and nvl(end_active_date, sysdate + 1)
2342 union
2343 select rule_id from csp_return_routing_rules
2344 where inv_cat_id in (select distinct category_id
2345 from MTL_ITEM_CATEGORIES
2346 where inventory_item_id = v_item_id
2347 and organization_id = cs_std.get_item_valdn_orgzn_id)
2348 and nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
2349 and sysdate between nvl(start_active_date, sysdate)
2350 and nvl(end_active_date, sysdate + 1)
2351 union
2352 select rule_id from csp_return_routing_rules where inv_item_id = v_item_id
2353 and nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
2354 and sysdate between nvl(start_active_date, sysdate)
2355 and nvl(end_active_date, sysdate + 1)
2356 union
2357 select rule_id from csp_return_routing_rules
2358 where nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
2359 and sysdate between nvl(start_active_date, sysdate)
2360 and nvl(end_active_date, sysdate + 1)
2361 and decode(source_terr_id, 0, null, source_terr_id) is null
2362 and return_type is null
2363 and decode(inv_cat_set_id, 0, null, inv_cat_set_id) is null
2364 and decode(inv_cat_id, 0, null, inv_cat_id) is null
2365 and decode(inv_item_id, 0, null, inv_item_id) is null
2366 ) prob,
2367 csp_return_routing_rules r,
2368 MTL_ITEM_CATEGORIES mtl
2369 where r.rule_id = prob.rule_id
2370 and mtl.inventory_item_id = v_item_id
2371 and mtl.organization_id = cs_std.get_item_valdn_orgzn_id
2372 and nvl(decode(r.source_terr_id, 0, null, r.source_terr_id), v_source_terr_id) = v_source_terr_id
2373 and nvl(r.return_type, v_ret_trans_type) = v_ret_trans_type
2374 and nvl(decode(r.inv_cat_set_id, 0, null, r.inv_cat_set_id), mtl.category_set_id) = mtl.category_set_id
2375 and nvl(decode(r.inv_cat_id, 0, null, r.inv_cat_id), mtl.category_id) = mtl.category_id
2376 and nvl(decode(r.inv_item_id, 0, null, r.inv_item_id), v_item_id) = v_item_id
2377 order by 1 desc
2378 ) intable where rownum = 1;
2379
2380 cursor c_Source_Org_Type (v_org_id number, v_subinv varchar2) is
2381 select
2382 nvl(stocking_site_type, 'MANNED')
2383 from csp_planning_parameters
2384 where organization_id = v_org_id
2385 and nvl(secondary_inventory, 'NULL') = nvl(v_subinv, 'NULL');
2386
2387 l_Source_Org_Type varchar2(20);
2388 l_city varchar2(60);
2389 l_postal_code varchar2(60);
2390 l_state varchar2(60);
2391 l_province varchar2(60);
2392 l_county varchar2(60);
2393 l_country varchar2(60);
2394
2395 cursor c_get_tech_add (v_org_id number, v_subinv varchar2) is
2396 SELECT HLOC.CITY,
2397 HLOC.POSTAL_CODE,
2398 HLOC.STATE,
2399 HLOC.PROVINCE,
2400 HLOC.COUNTY,
2401 HLOC.COUNTRY
2402 FROM csp_sec_inventories CINV,
2403 HZ_LOCATIONS HLOC,
2404 csp_rs_cust_relations rcr,
2405 hz_cust_acct_sites_All cas,
2406 hz_cust_site_uses_all csu,
2407 hz_party_sites ps
2408 WHERE CINV.ORGANIZATION_ID = v_org_id
2409 AND CINV.secondary_inventory_name = v_subinv
2410 AND CINV.owner_resource_id = rcr.RESOURCE_ID
2411 AND CINV.owner_resource_type = rcr.RESOURCE_TYPE
2412 AND rcr.customer_id = cas.cust_account_id
2413 AND cas.cust_acct_site_id = csu.cust_acct_site_id
2414 AND csu.site_use_code = 'SHIP_TO'
2415 AND csu.PRIMARY_FLAG = 'Y'
2416 AND csu.STATUS = 'A'
2417 AND cas.status = 'A'
2418 AND cas.party_site_id = ps.party_site_id
2419 AND HLOC.LOCATION_ID = ps.location_id
2420 AND rownum = 1;
2421
2422 l_hz_location_id number;
2423
2424 cursor c_get_ware_hz_loc (v_org_id number, v_subinv varchar2) is
2425 select
2426 c.hz_location_id,
2427 h.city,
2428 h.postal_code,
2429 h.state,
2430 h.province,
2431 h.county,
2432 h.country
2433 from
2434 csp_planning_parameters c,
2435 hz_locations h
2436 where c.organization_id = v_org_id
2437 and nvl(c.secondary_inventory, 'NULL') = nvl(v_subinv, 'NULL')
2438 and c.hz_location_id = h.location_id;
2439
2440 cursor c_get_ware_hr_loc (v_org_id number, v_subinv varchar2) is
2441 select
2442 hrloc.town_or_city as city,
2443 hrloc.postal_code as postal_code,
2444 null as state,
2445 null as province,
2446 null as county,
2447 hrloc.country as country
2448 from
2449 MTL_SECONDARY_INVENTORIES sub,
2450 hr_all_organization_units org,
2451 hr_locations_all hrloc
2452 where org.organization_id = v_org_id
2453 and org.organization_id = sub.organization_id(+)
2454 and sub.secondary_inventory_name(+) = v_subinv
2455 and nvl(sub.location_id, org.location_id) = hrloc.location_id;
2456
2457 cursor c_terr_result is
2458 select
2459 tall.terr_id
2460 from
2461 jtf_terr_all tall,
2462 jtf_terr_results_gt_mt tmt,
2463 JTF_TERR_TYPES_ALL tty
2464 where
2465 tall.terr_id = tmt.terr_id
2466 and tty.application_short_name = 'CSP'
2467 and tty.org_id = tall.org_id
2468 and tall.territory_type_id = tty.terr_type_id
2469 and tty.enabled_flag = 'Y'
2470 and tall.enabled_flag = 'Y'
2471 and tall.start_date_active <= sysdate
2472 and nvl(tall.end_date_active, sysdate+1) > sysdate
2473 order by tmt.absolute_rank desc;
2474
2475 l_sr_rec JTF_TERRITORY_PUB.JTF_Serv_Req_rec_type;
2476 l_source_terr_id number;
2477
2478 BEGIN
2479
2480 x_return_status := 'S';
2481
2482 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2483 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2484 'csp.plsql.CSP_EXCESS_PARTS_PVT.find_best_routing_rule',
2485 'Begin...');
2486 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2487 'csp.plsql.CSP_EXCESS_PARTS_PVT.find_best_routing_rule',
2488 'p_source_type=' || p_source_type
2489 || ', p_source_org_id=' || p_source_org_id
2490 || ', p_source_subinv=' || p_source_subinv
2491 || ', p_source_terr_id=' || p_source_terr_id
2492 || ', p_ret_trans_type=' || p_ret_trans_type
2493 || ', p_item_id=' || p_item_id);
2494 end if; -- End of FND Logger If block
2495
2496 v_match_rule_id := null;
2497 v_match_rule_weight := null;
2498
2499 if p_source_type = 'I' then
2500
2501 open c_Scan_Subinv (p_source_org_id,
2502 p_source_subinv,
2503 p_ret_trans_type,
2504 p_item_id);
2505 fetch c_Scan_Subinv into v_match_rule_weight, v_match_rule_id;
2506 close c_Scan_Subinv;
2507
2508 -- if no rule found based on source organization and subinv
2509 -- then find out the territory id for this source
2510 -- and then search for Internal Territory type
2511 -- rules for this territory source
2512
2513 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2514 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2515 'csp.plsql.CSP_EXCESS_PARTS_PVT.find_best_routing_rule',
2516 'v_match_rule_id=' || v_match_rule_id
2517 || ', v_match_rule_weight=' || v_match_rule_weight);
2518 end if; -- End of FND Logger If block
2519
2520 if v_match_rule_id is null then
2521 open c_Source_Org_Type(p_source_org_id, p_source_subinv);
2522 fetch c_Source_Org_Type into l_Source_Org_Type;
2523 close c_Source_Org_Type;
2524
2525 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2526 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2527 'csp.plsql.CSP_EXCESS_PARTS_PVT.find_best_routing_rule',
2528 'l_Source_Org_Type=' || l_Source_Org_Type);
2529 end if; -- End of FND Logger If block
2530
2531 if l_Source_Org_Type = 'TECHNICIAN' then
2532
2533 open c_get_tech_add(p_source_org_id, p_source_subinv);
2534 fetch c_get_tech_add into l_city,
2535 l_postal_code,
2536 l_state,
2537 l_province,
2538 l_county,
2539 l_country;
2540 close c_get_tech_add;
2541
2542 else -- it is a warehouse
2543
2544 -- first try to get HZ_location mentioned in the planner's desktop
2545 -- if not found then get hr_location for the organization
2546 l_hz_location_id := null;
2547 open c_get_ware_hz_loc(p_source_org_id, p_source_subinv);
2548 fetch c_get_ware_hz_loc into l_hz_location_id,
2549 l_city,
2550 l_postal_code,
2551 l_state,
2552 l_province,
2553 l_county,
2554 l_country;
2555 close c_get_ware_hz_loc;
2556
2557 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2558 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2559 'csp.plsql.CSP_EXCESS_PARTS_PVT.find_best_routing_rule',
2560 'l_hz_location_id=' || l_hz_location_id);
2561 end if; -- End of FND Logger If block
2562
2563 -- no hz_location_id found? then check for hr_location
2564 if l_hz_location_id is null then
2565
2566 open c_get_ware_hr_loc(p_source_org_id, p_source_subinv);
2567 fetch c_get_ware_hr_loc into l_city,
2568 l_postal_code,
2569 l_state,
2570 l_province,
2571 l_county,
2572 l_country;
2573 close c_get_ware_hr_loc;
2574
2575 end if; -- end of if l_hz_location_id is null
2576
2577 end if; -- end if if l_Source_Org_Type = 'TECHNICIAN'
2578
2579 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2580 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2581 'csp.plsql.CSP_EXCESS_PARTS_PVT.find_best_routing_rule',
2582 'l_city=' || l_city
2583 || ', l_postal_code=' || l_postal_code
2584 || ', l_state=' || l_state
2585 || ', l_province=' || l_province
2586 || ', l_county=' || l_county
2587 || ', l_country=' || l_country);
2588 end if; -- End of FND Logger If block
2589
2590 if l_country is not null then
2591
2592 -- so, we have geographycal data now
2593 -- call territory API to get all matching territories
2594 -- and find the best one
2595
2596 l_sr_rec.CITY := l_city;
2597 l_sr_rec.POSTAL_CODE := l_postal_code;
2598 l_sr_rec.STATE := l_state;
2599 l_sr_rec.PROVINCE := l_province;
2600 l_sr_rec.COUNTY := l_county;
2601 l_sr_rec.COUNTRY := l_country;
2602
2603 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2604 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2605 'csp.plsql.CSP_EXCESS_PARTS_PVT.find_best_routing_rule',
2606 'Calling JTY_TERR_SPARES_PVT.process_match_terr_spares ...');
2607 end if; -- End of FND Logger If block
2608
2609 JTY_TERR_SPARES_PVT.process_match_terr_spares (
2610 p_api_version_number => 1.0,
2611 p_init_msg_list => fnd_api.g_true,
2612 p_TerrServReq_Rec => l_sr_rec,
2613 p_Resource_Type => null,
2614 p_Role => null,
2615 p_plan_start_date => null,
2616 p_plan_end_date => null,
2617 x_return_status => x_return_status,
2618 x_msg_count => x_msg_count,
2619 X_msg_data => X_msg_data
2620 );
2621
2622 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2623 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2624 'csp.plsql.CSP_EXCESS_PARTS_PVT.find_best_routing_rule',
2625 'Status returned from JTY_TERR_SPARES_PVT.process_match_terr_spares = '
2626 || x_return_status);
2627 end if; -- End of FND Logger If block
2628
2629 if x_return_status = 'S' then
2630
2631 begin
2632 for c_terr in c_terr_result loop
2633
2634 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2635 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2636 'csp.plsql.CSP_EXCESS_PARTS_PVT.find_best_routing_rule',
2637 'c_terr.terr_id =' || c_terr.terr_id);
2638 end if; -- End of FND Logger If block
2639
2640 l_source_terr_id := c_terr.terr_id;
2641
2642 if l_source_terr_id is not null then
2643
2644 open c_Scan_Terr (l_source_terr_id,
2645 'T',
2646 p_ret_trans_type,
2647 p_item_id);
2648 fetch c_Scan_Terr into v_match_rule_weight, v_match_rule_id;
2649 close c_Scan_Terr;
2650
2651 exit when v_match_rule_id is not null;
2652
2653 end if; -- if l_source_terr_id is not null
2654
2655 end loop; -- for c_terr in c_terr_result loop
2656
2657 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2658 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2659 'csp.plsql.CSP_EXCESS_PARTS_PVT.find_best_routing_rule',
2660 'FOR loop ended with v_match_rule_id = ' || v_match_rule_id);
2661 end if; -- End of FND Logger If block
2662
2663 exception
2664 when NO_DATA_FOUND then
2665 v_match_rule_id := null;
2666 end;
2667
2668 end if; -- end of if x_return_status = 'S'
2669
2670 end if; -- end of if l_country is not null
2671
2672 end if; -- end for if v_match_rule_id is null condition
2673
2674 elsif p_source_type = 'C' then -- Else of if p_source_type = 'I' (p_source_type = 'C')
2675
2676 open c_Scan_Terr (p_source_terr_id,
2677 'C',
2678 p_ret_trans_type,
2679 p_item_id);
2680 fetch c_Scan_Terr into v_match_rule_weight, v_match_rule_id;
2681 close c_Scan_Terr;
2682
2683 elsif p_source_type = 'T' then -- Else of if p_source_type = 'I' (p_source_type = 'C')
2684
2685 open c_Scan_Terr (p_source_terr_id,
2686 'T',
2687 p_ret_trans_type,
2688 p_item_id);
2689 fetch c_Scan_Terr into v_match_rule_weight, v_match_rule_id;
2690 close c_Scan_Terr;
2691
2692 end if; -- End of if p_source_type = 'I'
2693
2694 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2695 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2696 'csp.plsql.CSP_EXCESS_PARTS_PVT.find_best_routing_rule',
2697 'v_match_rule_id=' || v_match_rule_id
2698 || ', v_match_rule_weight=' || v_match_rule_weight);
2699 end if; -- End of FND Logger If block
2700
2701 if v_match_rule_id is not null then
2702 x_rule_id := v_match_rule_id;
2703 end if;
2704
2705 END; -- End of procedure find_best_routing_rule
2706
2707 procedure charges_return_routing(
2708 p_return_type in varchar2,
2709 p_hz_location_id in number,
2710 p_item_id in number,
2711 x_operating_unit out nocopy number,
2712 x_organization_id out nocopy number,
2713 x_subinventory_code out nocopy varchar2,
2714 x_hz_location_id out nocopy number,
2715 x_hr_location_id out nocopy number,
2716 x_return_status out nocopy varchar2,
2717 x_msg_count out nocopy number,
2718 x_msg_data out nocopy varchar2) is
2719
2720 cursor c_terr_results is
2721 select tall.terr_id
2722 from jtf_terr_all tall,
2723 jtf_terr_results_gt_mt tmt,
2724 jtf_terr_types_all tty
2725 where tall.terr_id = tmt.terr_id
2726 and tty.application_short_name = 'CSP'
2727 and tty.org_id = tall.org_id
2728 and tall.territory_type_id = tty.terr_type_id
2729 and tty.enabled_flag = 'Y'
2730 and tall.enabled_flag='Y'
2731 and tall.start_date_active <=sysdate
2732 and nvl(tall.end_date_active,sysdate+1) >= sysdate
2733 order by tmt.absolute_rank desc;
2734
2735 cursor c_address is
2736 select hl.city,hl.postal_code,hl.state,hl.province,hl.county,hl.country
2737 from hz_locations hl
2738 where hl.location_id = p_hz_location_id;
2739
2740 cursor c_destination(p_return_rule_id number) is
2741 select crrr.dest_org_id,
2742 crrr.dest_subinv,
2743 cpp.hz_location_id,
2744 hoa.location_id,
2745 ood.operating_unit
2746 from csp_return_routing_rules crrr,
2747 hr_organization_units hoa,
2748 csp_planning_parameters cpp,
2749 org_organization_definitions ood
2750 where crrr.rule_id = p_return_rule_id
2751 and crrr.dest_subinv is null
2752 and crrr.dest_org_id = hoa.organization_id
2753 and ood.organization_id = hoa.organization_id
2754 and cpp.organization_id (+) = crrr.dest_org_id
2755 and cpp.secondary_inventory (+) = crrr.dest_subinv
2756 union
2757 select crrr.dest_org_id,
2758 crrr.dest_subinv,
2759 cpp.hz_location_id,
2760 msi.location_id,
2761 ood.operating_unit
2762 from csp_return_routing_rules crrr,
2763 mtl_secondary_inventories msi,
2764 csp_planning_parameters cpp,
2765 org_organization_definitions ood
2766 where crrr.rule_id = p_return_rule_id
2767 and crrr.dest_org_id = msi.organization_id
2768 and crrr.dest_subinv = msi.secondary_inventory_name
2769 and ood.organization_id = msi.organization_id
2770 and cpp.organization_id (+) = crrr.dest_org_id
2771 and cpp.secondary_inventory (+) = crrr.dest_subinv
2772 and crrr.dest_subinv is not null;
2773
2774 l_sr_rec JTF_TERRITORY_PUB.JTF_Serv_Req_rec_type;
2775 x_return_rule_id number := null;
2776
2777 begin
2778 x_return_status := FND_API.G_RET_STS_SUCCESS;
2779 -- Find Territory
2780 open c_address;
2781 fetch c_address into l_sr_rec.city,
2782 l_sr_rec.postal_code,
2783 l_sr_rec.state,
2784 l_sr_rec.province,
2785 l_sr_rec.county,
2786 l_sr_rec.country;
2787 close c_address;
2788
2789 jty_terr_spares_pvt.process_match_terr_spares(
2790 p_api_version_number => 1.0,
2791 p_init_msg_list => fnd_api.g_true,
2792 p_terrservreq_rec => l_sr_rec,
2793 p_resource_type => null,
2794 p_role => null,
2795 p_plan_start_date => null,
2796 p_plan_end_date => null,
2797 x_return_status => x_return_status,
2798 x_msg_count => x_msg_count,
2799 x_msg_data => x_msg_data);
2800
2801 -- Loop through territories
2802 for ctr in c_terr_results loop
2803 -- Look for routing rule for territory
2804 find_best_routing_rule(
2805 p_source_type => 'T',
2806 p_source_org_id => null,
2807 p_source_subinv => null,
2808 p_source_terr_id => ctr.terr_id,
2809 p_ret_trans_type => p_return_type,
2810 p_item_id => p_item_id,
2811 x_rule_id => x_return_rule_id,
2812 x_return_status => x_return_status,
2813 x_msg_count => x_msg_count,
2814 x_msg_data => x_msg_data);
2815 if x_return_rule_id is not null then
2816 exit;
2817 end if;
2818 end loop;
2819
2820 if x_return_rule_id is not null then
2821 open c_destination(x_return_rule_id);
2822 fetch c_destination into x_organization_id,
2823 x_subinventory_code,
2824 x_hz_location_id,
2825 x_hr_location_id,
2826 x_operating_unit;
2827 close c_destination;
2828 else
2829 fnd_message.set_name('CSP', 'CSP_NO_ROUTING_RULE');
2830 fnd_msg_pub.add;
2831 fnd_msg_pub.count_and_get
2832 ( p_count => x_msg_count
2833 , p_data => x_msg_data);
2834 x_return_status := FND_API.G_RET_STS_ERROR;
2835 end if;
2836
2837 end charges_return_routing;
2838
2839 end;