[Home] [Help]
PACKAGE BODY: APPS.CSP_SUPERSESSIONS_PVT
Source
1 PACKAGE BODY CSP_SUPERSESSIONS_PVT AS
2 /* $Header: cspgsupb.pls 120.2 2006/12/05 18:49:37 jjalla noship $ */
3 -- Start of Comments
4 -- Package name : CSP_SUPERSESSIONS_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 Type v_cur_type IS REF CURSOR;
11 CURSOR get_planned_item(c_inventory_item_id NUMBER,c_org_id NUMBER)
12 IS
13 SELECT item_supplied
14 FROM csp_supersede_items
15 where INVENTORY_ITEM_ID = c_inventory_item_id
16 and ORGANIZATION_ID = c_org_id;
17
18 CURSOR is_item_scrap(c_inventory_item_id NUMBER, c_org_id number)
19 IS
20 SELECT decode(MTL_TRANSACTIONS_ENABLED_FLAG,'Y','N','N','Y')
21 FROM MTL_SYSTEM_ITEMS_B
22 WHERE inventory_item_id = c_inventory_item_id
23 AND ORGANIZATION_ID = c_org_id;
24
25 CURSOR get_replacing_item(c_master_org_id NUMBER, c_item_id NUMBER)
26 IS
27 select mri.related_item_id
28 from MTL_RELATED_ITEMS mri
29 where mri.ORGANIZATION_ID = nvl(c_master_org_id,mri.ORGANIZATION_ID)
30 and mri.RELATIONSHIP_TYPE_ID = 8
31 and mri.inventory_item_id = c_item_id;
32
33 CURSOR get_mster_org(c_organization_id NUMBER)
34 IS
35 SELECT master_organization_id
36 FROM mtl_parameters
37 WHERE organization_id = c_organization_id;
38
39 PROCEDURE BUILD_NOT_IN_CONDITION(p_master_org NUMBER, p_inventory_item_id NUMBER, x_where_string OUT NOCOPY varchar2);
40 PROCEDURE parse_supply_chain(p_org_id IN NUMBER
41 ,p_sub_inventory IN varchar2
42 ,p_inventory_item_id IN NUMBER
43 ,p_supply_level IN NUMBER
44 ,p_bilateral IN BOOLEAN
45 ,p_rop IN NUMBER
46 ,x_item_supplied OUT NOCOPY NUMBER
47 ,x_top_org_id OUT NOCOPY NUMBER
48 ,x_return_status OUT NOCOPY varchar2);
49 PROCEDURE get_item_planned;
50 PROCEDURE get_item_supplied;
51 PROCEDURE PURGE_OLD_SUPERSEDE_DATA;
52 PROCEDURE insert_item_supplied(p_planned_subinv_code IN varchar2
53 ,p_planned_org_id IN NUMBER
54 ,p_replaced_item_id IN NUMBER
55 ,p_supply_level IN NUMBER
56 ,p_master_org_id IN NUMBER
57 ,x_return_status OUT NOCOPY varchar2);
58
59 PROCEDURE insert_item_planned(p_master_org_id IN NUMBER
60 ,p_org_id IN NUMBER
61 ,p_item_id IN NUMBER
62 ,p_subinv_code IN VARCHAR2
63 ,p_supply_chain_id IN NUMBER
64 ,x_return_status OUT NOCOPY Varchar2);
65
66 PROCEDURE PROCESS_SUPERSESSIONS(errbuf OUT NOCOPY varchar2,
67 retcode OUT NOCOPY number) IS
68 BEGIN
69 purge_old_supersede_data;
70 get_item_planned;
71 get_item_supplied;
72 END PROCESS_SUPERSESSIONS;
73
74 PROCEDURE get_item_supplied IS
75
76 CURSOR get_master_organizations IS
77 select distinct(mp.MASTER_ORGANIZATION_ID)
78 from csp_planning_parameters cpp,mtl_parameters mp
79 where (cpp.ORGANIZATION_TYPE is not null or cpp.node_type = 'SUBINVENTORY')
80 and mp.ORGANIZATION_ID = cpp.ORGANIZATION_ID;
81
82 CURSOR get_supersede_items(c_master_org_id number)
83 IS
84 select mri.inventory_item_id ,
85 mri.related_item_id,
86 mri.reciprocal_flag
87 from MTL_RELATED_ITEMS mri
88 where mri.ORGANIZATION_ID = c_master_org_id
89 and mri.RELATIONSHIP_TYPE_ID = 8;
90
91 /*CURSOR get_supply_chain(c_master_org_id number,c_inv_item_id number)
92 IS
93 SELECT cscp.ORGANIZATION_ID,decode(cscp.SECONDARY_INVENTORY,'-',null,cscp.SECONDARY_INVENTORY),SUPPLY_LEVEL
94 FROM CSP_SUPPLY_CHAIN cscp, mtl_parameters mp
95 WHERE mp.MASTER_ORGANIZATION_ID = c_master_org_id
96 and cscp.INVENTORY_ITEM_ID = c_inv_item_id
97 --and cscp.SUPPLY_LEVEL = 1
98 and cscp.organization_id = mp.organization_id
99 and cscp.source_organization_id is not null;*/
100
101 CURSOR get_supply_chain(c_master_org_id number,c_inv_item_id number)
102 IS
103 SELECT DISTINCT csc.ORGANIZATION_ID,decode(csc.SECONDARY_INVENTORY,'-',null,csc.SECONDARY_INVENTORY),SUPPLY_LEVEL
104 FROM CSP_SUPPLY_CHAIN csc, mtl_parameters mp, csp_planning_parameters cpp
105 WHERE mp.MASTER_ORGANIZATION_ID = c_master_org_id
106 and csc.INVENTORY_ITEM_ID = c_inv_item_id
107 and csc.organization_id = mp.organization_id
108 and cpp.organization_id = csc.organization_id
109 and nvl(cpp.SECONDARY_INVENTORY,'-') = csc.SECONDARY_INVENTORY
110 and cpp.node_type = 'SUBINVENTORY' ;
111
112
113 CURSOR bilateral_relation_item(c_related_item_id number,c_master_org_id number,c_inventory_item_id number)
114 IS
115 select MRI.inventory_item_id
116 from MTL_RELATED_ITEMS mri , MTL_SYSTEM_ITEMS_B msi
117 where mri.ORGANIZATION_ID = c_master_org_id
118 and mri.RELATIONSHIP_TYPE_ID = 8
119 and mri.related_item_id = c_related_item_id
120 and mri.reciprocal_flag = 'Y'
121 and mri.inventory_item_id <> c_inventory_item_id
122 and MSI.ORGANIZATION_ID = c_master_org_id
123 and MSI.inventory_item_id = mri.inventory_item_id
124 and MSI.MTL_TRANSACTIONS_ENABLED_FLAG = 'Y';
125
126
127 l_master_org_id NUMBER;
128 l_related_item_id NUMBER;
129 l_reciprocal_flag VARCHAR2(10);
130 l_return_status VARCHAR2(3);
131 l_msg_data VARCHAR2(2000);
132 l_msg_count NUMBER;
133 l_planned_org NUMBER;
134 l_planned_sub_inv varchar2(30);
135 l_supply_level NUMBER;
136 l_replaced_item NUMBER;
137 BEGIN
138 OPEN get_master_organizations;
139 LOOP
140 FETCH get_master_organizations INTO l_master_org_id;
141 EXIT WHEN get_master_organizations % notfound;
142 -- DBMS_OUTPUT.PUT_LINE('Maste Organization ' || l_master_org_id);
143 OPEN get_supersede_items(l_master_org_id);
144 LOOP
145 FETCH get_supersede_items INTO l_replaced_item,l_related_item_id,l_reciprocal_flag;
146 EXIT WHEN get_supersede_items % notfound;
147 ----dbms_output.put_line('supersed Items ' || l_replaced_item || ' ' ||
148 -- l_related_item_id || ' ' || l_reciprocal_flag );
149 OPEN get_supply_chain(l_master_org_id,l_replaced_item);
150 LOOP
151 FETCH get_supply_chain INTO l_planned_org,l_planned_sub_inv,l_supply_level;
152 EXIT WHEN get_supply_chain%NOTFOUND ;
153 insert_item_supplied(l_planned_sub_inv
154 ,l_planned_org
155 ,l_replaced_item
156 ,l_supply_level
157 ,l_master_org_id
158 ,l_return_status);
159 END LOOP;
160 CLOSE get_supply_chain;
161 END LOOP;
162 CLOSE get_supersede_items;
163 END LOOP;
164 CLOSE get_master_organizations;
165 COMMIT work;
166 END get_item_supplied;
167 PROCEDURE insert_item_supplied(p_planned_subinv_code IN varchar2
168 ,p_planned_org_id IN NUMBER
169 ,p_replaced_item_id IN NUMBER
170 ,p_supply_level IN NUMBER
171 ,p_master_org_id IN NUMBER
172 ,x_return_status OUT NOCOPY varchar2) IS
173 l_inventory_item_Id NUMBER;
174 l_central_warehouse NUMBER;
175 l_rop NUMBER := 0;
176 l_top_org_id NUMBER;
177 l_scrap VARCHAR2(10);
178 l_item_supplied NUMBER := NULL;
179 l_return_status varchar2(10);
180 l_replacing_item NUMBER;
181 l_where_string varchar2(1500) := NULL;
182 l_sql_string varchar2(2000);
183 l_temp_replaced_item NUMBER;
184 l_cur v_cur_type;
185 l_bilateral_item NUMBER;
186 l_supersede_item BOOLEAN;
187 l_supersede_id NUMBER;
188
189 t_org_id NUMBER;
190 t_subinv_code varchar2(30);
191 t_source_org_id NUMBER;
192 t_source_subinv_code varchar2(30);
193 t_supply_level NUMBER;
194 l_org_type varchar2(30);
195
196 CURSOR get_rop_for_subinv
197 IS
198 SELECT NVL(MIN_MINMAX_QUANTITY,0)
199 from MTL_ITEM_SUB_INVENTORIES
200 where organization_id = p_planned_org_id
201 and inventory_item_id = l_inventory_item_Id
202 and SECONDARY_INVENTORY = p_planned_subinv_code;
203
204 CURSOR get_rop_for_org
205 IS
206 SELECT nvl(decode(cpr.newbuy_rop,0,null), NVL(MIN_MINMAX_QUANTITY,0))
207 from mtl_system_items_b msib, csp_plan_reorders cpr
208 where msib.organization_id = p_planned_org_id
209 and msib.inventory_item_id = l_inventory_item_Id
210 and cpr.organization_id = msib.organization_id
211 and cpr.inventory_item_id = msib.inventory_item_id ;
212 /* SELECT NVL(MIN_MINMAX_QUANTITY,0)
213 from mtl_system_items_b
214 where organization_id = p_planned_org_id
215 and inventory_item_id = l_inventory_item_Id;*/
216
217 CURSOR get_replacing_item(c_master_org_id NUMBER, c_item_id NUMBER)
218 IS
219 select mri.related_item_id
220 from MTL_RELATED_ITEMS mri
221 where mri.ORGANIZATION_ID = c_master_org_id
222 and mri.RELATIONSHIP_TYPE_ID = 8
223 and mri.inventory_item_id = c_item_id;
224
225 CURSOR get_sources(c_inv_item_id number,c_org_id number,c_secondary_inventory Varchar2,c_supply_level number)
226 IS
227 SELECT csc.SOURCE_ORGANIZATION_ID, decode(csc.SOURCE_SUBINVENTORY,'-',null),cpp.ORGANIZATION_TYPE
228 FROM CSP_SUPPLY_CHAIN csc,csp_planning_parameters cpp
229 WHERE cpp.organization_id = c_org_id
230 and (cpp.ORGANIZATION_TYPE IS NOT null or cpp.node_type = 'SUBINVENTORY')
231 and cpp.secondary_inventory = nvl(c_secondary_inventory,cpp.secondary_inventory)
232 and csc.INVENTORY_ITEM_ID = c_inv_item_id
233 and csc.SUPPLY_LEVEL = NVL(c_supply_level,csc.SUPPLY_LEVEL)
234 and csc.organization_id = c_org_id
235 and csc.SECONDARY_INVENTORY = decode(cpp.ORGANIZATION_TYPE , 'W', '-',nvl(c_secondary_inventory,csc.SECONDARY_INVENTORY));
236
237 BEGIN
238 l_inventory_item_Id := p_replaced_item_id ;
239 l_central_warehouse := NULL;
240 IF p_planned_subinv_code IS NULL THEN
241 OPEN get_rop_for_org;
242 LOOP
243 FETCH get_rop_for_org INTO l_rop;
244 EXIT WHEN get_rop_for_org % NOTFOUND ;
245 END LOOP;
246 CLOSE get_rop_for_org;
247 ELSE
248 OPEN get_rop_for_subinv;
249 LOOP
250 FETCH get_rop_for_subinv INTO l_rop;
251 EXIT WHEN get_rop_for_subinv % NOTFOUND ;
252 END LOOP;
253 CLOSE get_rop_for_subinv;
254 END IF;
255 --- l_rop := 1000;
256 LOOP
257 l_top_org_id := null;
258 ----dbms_output.put_line('lowest level '|| l_inventory_item_Id || ' ' || l_planned_org || ' ' || l_planned_sub_inv);
259 OPEN is_item_scrap(l_inventory_item_Id, p_planned_org_id);
260 LOOP
261 FETCH is_item_scrap INTO l_scrap;
262 EXIT WHEN is_item_scrap % NOTFOUND;
263 --dbms_output.put_line('scrap-- '|| l_scrap);
264 END LOOP;
265 CLOSE is_item_scrap;
266 IF l_scrap = 'N' THEN
267 CSP_SUPERSESSIONS_PVT.parse_supply_chain(p_org_id => p_planned_org_id
268 ,p_sub_inventory => p_planned_subinv_code
269 ,p_inventory_item_id => l_inventory_item_Id
270 ,p_bilateral => FALSE
271 ,p_rop => l_rop
272 ,p_supply_level => p_supply_level
273 ,x_item_supplied => l_item_supplied
274 ,x_top_org_id => l_top_org_id
275 ,x_return_status => l_return_status);
276 IF l_inventory_item_Id = p_replaced_item_id THEN
277 l_central_warehouse := l_top_org_id;
278 IF l_top_org_id IS NULL THEN
279 FND_MESSAGE.set_name('CSP','CSP_NO_TOP_ORG');
280 FND_MSG_PUB.add;
281 END IF;
282 END IF;
283 ELSE
284 IF l_inventory_item_Id = p_replaced_item_id THEN
285 t_org_id := p_planned_org_id;
286 t_subinv_code := p_planned_subinv_code;
287 t_supply_level:=1;
288
289 LOOP
290 t_source_org_id := null;
291 t_source_subinv_code := null;
292 l_org_type := null;
293 OPEN get_sources(l_inventory_item_Id,t_org_id, t_subinv_code,t_supply_level);
294 LOOP
295 FETCH get_sources INTO t_source_org_id, t_source_subinv_code,l_org_type;
296 EXIT WHEN get_sources % NOTFOUND;
297 END LOOP;
298 CLOSE get_sources;
299 IF t_source_org_id is NULL or l_org_type='W' THEN
300 l_central_warehouse := t_org_id;
301 EXIT;
302 ELSE
303 t_org_id := t_source_org_id;
304 t_subinv_code := t_source_subinv_code;
305 t_supply_level := t_supply_level + 1;
306 END IF;
307 END LOOP;
308 IF l_top_org_id IS NULL THEN
309 FND_MESSAGE.set_name('CSP','CSP_NO_TOP_ORG');
310 FND_MSG_PUB.add;
311 END IF;
312 END IF;
313 END IF;
314 IF l_item_supplied IS NULL THEN
315 IF l_where_string IS NULL THEN
316 l_temp_replaced_item := p_replaced_item_id;
317 LOOP
318 OPEN get_replacing_item(p_master_org_id,l_temp_replaced_item);
319 l_replacing_item := NULL;
320 LOOP
321 FETCH get_replacing_item INTO l_replacing_item;
322 EXIT WHEN get_replacing_item % NOTFOUND;
323 END LOOP;
324 CLOSE get_replacing_item;
325 IF l_replacing_item IS NOT NULL THEN
326 l_temp_replaced_item := l_replacing_item;
327 IF l_where_string IS NULL THEN
328 l_where_string := '(' || p_replaced_item_id || ',' || l_replacing_item ;
329 ELSE
330 l_where_string := l_where_string || ', ' || l_replacing_item ;
331 END IF;
332 ELSE
333 EXIT;
334 END IF;
335 END LOOP;
336 l_where_string := l_where_string || ')' ;
337 END IF;
338 l_sql_string := 'select MRI.inventory_item_id
339 from MTL_RELATED_ITEMS mri , MTL_SYSTEM_ITEMS_B msi
340 where mri.ORGANIZATION_ID =' || p_master_org_id ||
341 'and mri.RELATIONSHIP_TYPE_ID = 8
342 and mri.related_item_id =' || l_inventory_item_id ||
343 'and mri.reciprocal_flag =' || '''' || 'Y' || '''' ||
344 'and mri.inventory_item_id <>' || l_inventory_item_id ||
345 'and MSI.ORGANIZATION_ID =' || p_master_org_id ||
346 'and MSI.inventory_item_id = mri.inventory_item_id
347 and MSI.MTL_TRANSACTIONS_ENABLED_FLAG =' || '''' || 'Y' || '''' ||
348 'and MRI.inventory_item_id NOT IN ' || l_where_string;
349 OPEN l_cur FOR
350 l_sql_string;
351 --dbms_output.put_line('coming for bilateral ' ||l_inventory_item_id || ' ' || l_master_org_id );
352 --- OPEN bilateral_relation_item(l_inventory_item_id , l_master_org_id,l_inventory_item_id);
353 LOOP
354 FETCH l_cur INTO l_bilateral_item;
355 EXIT WHEN l_cur % NOTFOUND;
356 CSP_SUPERSESSIONS_PVT.parse_supply_chain(p_org_id => p_planned_org_id
357 ,p_sub_inventory => NULL
358 ,p_inventory_item_id => l_bilateral_item
359 ,p_bilateral => TRUE
360 ,p_supply_level => NULL
361 ,p_rop => l_rop
362 ,x_item_supplied => l_item_supplied
363 ,x_top_org_id => l_top_org_id
364 ,x_return_status => l_return_status);
365 IF l_item_supplied IS NOT NULL THEN
366 EXIT;
367 END IF;
368 END LOOP;
369 CLOSE l_cur;
370 END IF;
371 --dbms_output.put_line('for ' || l_inventory_item_Id );
372 IF l_item_supplied IS NULL THEN
373 l_supersede_item := FALSE;
374 l_replacing_item := NULL;
375 OPEN get_replacing_item(p_master_org_id,l_inventory_item_Id);
376 LOOP
377 FETCH get_replacing_item INTO l_replacing_item;
378 EXIT WHEN get_replacing_item % NOTFOUND;
379 IF l_replacing_item IS NOT NULL THEN
380 l_inventory_item_Id := l_replacing_item;
381 END IF;
382 END LOOP;
383 CLOSE get_replacing_item;
384 END IF;
385 IF l_item_supplied IS NOT NULL or l_replacing_item IS NULL THEN
386 EXIT;
387 END IF;
388 END LOOP;
389 IF l_item_supplied IS NULL THEN
390 OPEN get_planned_item(p_replaced_item_id,l_central_warehouse);
391 LOOP
392 FETCH get_planned_item INTO l_item_supplied;
393 EXIT WHEN get_planned_item % NOTFOUND;
394 END LOOP;
395 CLOSE get_planned_item;
396 END IF;
397 l_supersede_id := NULL;
398 IF l_item_supplied IS NULL THEN
399 l_item_supplied := l_inventory_item_Id;
400 END IF;
401 CSP_SUPERSEDE_ITEMS_PKG.insert_row(px_supersede_id => l_supersede_id
402 ,p_created_by => -1
403 ,p_creation_date => sysdate
404 ,p_last_updated_by => -1
405 ,p_last_update_date=> sysdate
406 ,p_last_update_login => -1
407 ,p_inventory_item_id => p_replaced_item_id
408 ,p_organization_id => p_planned_org_id
409 ,p_sub_inventory_code => nvl(p_planned_subinv_code,'-')
410 ,p_item_supplied => l_item_supplied
411 ,p_attribute_category => NULL
412 ,p_attribute1 => NULL
413 ,p_attribute2 => NULL
414 ,p_attribute3 => NULL
415 ,p_attribute4 => NULL
416 ,p_attribute5 => NULL
417 ,p_attribute6 => NULL
418 ,p_attribute7 => NULL
419 ,p_attribute8 => NULL
420 ,p_attribute9 => NULL
421 ,p_attribute10 => NULL
422 ,p_attribute11 => NULL
423 ,p_attribute12 => NULL
424 ,p_attribute13 => NULL
425 ,p_attribute14 => NULL
426 ,p_attribute15 => NULL);
427 --dbms_output.put_line( 'item supplied ********* ' || l_planned_org || ' ' || l_planned_sub_inv|| ' ' || l_item_supplied);
428 END insert_item_supplied;
429
430 PROCEDURE parse_supply_chain(p_org_id IN NUMBER
431 ,p_sub_inventory IN varchar2
432 ,p_inventory_item_id IN NUMBER
433 ,p_supply_level IN NUMBER
434 ,p_bilateral IN BOOLEAN
435 ,p_rop IN NUMBER
436 ,x_item_supplied OUT NOCOPY NUMBER
437 ,x_top_org_id OUT NOCOPY NUMBER
438 ,x_return_status OUT NOCOPY varchar2)
439 IS
440
441 CURSOR get_supply_level_in_SC(c_inv_item_id number,c_org_id number,c_secondary_inventory Varchar2,c_supply_level number)
442 IS
443 SELECT csc.ORGANIZATION_ID,decode(csc.SECONDARY_INVENTORY,'-',null,csc.SECONDARY_INVENTORY) ,csc.SOURCE_ORGANIZATION_ID, decode(csc.SOURCE_SUBINVENTORY,'-',null,csc.SOURCE_SUBINVENTORY)
444 FROM CSP_SUPPLY_CHAIN csc,csp_planning_parameters cpp
445 WHERE cpp.organization_id = c_org_id
446 and (cpp.ORGANIZATION_TYPE IS NOT null or cpp.node_type = 'SUBINVENTORY')
447 and cpp.secondary_inventory = nvl(c_secondary_inventory,cpp.secondary_inventory)
448 and csc.INVENTORY_ITEM_ID = c_inv_item_id
449 and csc.SUPPLY_LEVEL = NVL(c_supply_level,csc.SUPPLY_LEVEL)
450 and csc.organization_id = c_org_id
451 and csc.SECONDARY_INVENTORY = decode(cpp.ORGANIZATION_TYPE , 'W', '-',nvl(c_secondary_inventory,csc.SECONDARY_INVENTORY));
452
453 CURSOR get_supply_chain(c_inv_item_id number,c_org_id number,c_secondary_inventory Varchar2,c_supply_level number)
454 IS
455 SELECT csc.ORGANIZATION_ID,decode(csc.SECONDARY_INVENTORY,'-',null,csc.SECONDARY_INVENTORY),csc.SUPPLY_LEVEL
456 FROM CSP_SUPPLY_CHAIN csc,csp_planning_parameters cpp
457 WHERE cpp.organization_id = c_org_id
458 and (cpp.ORGANIZATION_TYPE IS NOT null or cpp.node_type = 'SUBINVENTORY')
459 and cpp.secondary_inventory = nvl(c_secondary_inventory,cpp.secondary_inventory)
460 and csc.INVENTORY_ITEM_ID = c_inv_item_id
461 and csc.SUPPLY_LEVEL = NVL(c_supply_level,csc.SUPPLY_LEVEL)
462 and csc.organization_id = c_org_id
463 and csc.SECONDARY_INVENTORY = decode(cpp.ORGANIZATION_TYPE , 'W', '-',nvl(c_secondary_inventory,csc.SECONDARY_INVENTORY));
464
465 CURSOR get_sources(c_inv_item_id number,c_org_id number,c_secondary_inventory Varchar2,c_supply_level number)
466 IS
467 SELECT csc.SOURCE_ORGANIZATION_ID, decode(csc.SOURCE_SUBINVENTORY,'-',null)
468 FROM CSP_SUPPLY_CHAIN csc,csp_planning_parameters cpp
469 WHERE cpp.organization_id = c_org_id
470 and (cpp.ORGANIZATION_TYPE IS NOT null or cpp.node_type = 'SUBINVENTORY')
471 and cpp.secondary_inventory = nvl(c_secondary_inventory,cpp.secondary_inventory)
472 and csc.INVENTORY_ITEM_ID = c_inv_item_id
473 and csc.SUPPLY_LEVEL = NVL(c_supply_level,csc.SUPPLY_LEVEL)
474 and csc.organization_id = c_org_id
475 and csc.SECONDARY_INVENTORY = decode(cpp.ORGANIZATION_TYPE , 'W', '-',nvl(c_secondary_inventory,csc.SECONDARY_INVENTORY));
476
477 l_supply_level NUMBER := 0;
478 l_highest_level_present BOOLEAN;
479 l_inventory_item_id NUMBER;
480 l_org_id NUMBER;
481 l_sec_subinventory VARCHAR2(30);
482 l_source_org_id NUMBER;
483 l_source_sub_inventory varchar2(30);
484 l_att NUMBER;
485 l_cumulative_att NUMBER;
486 l_onhand NUMBER;
487 l_return_status varchar2(5);
488 l_msg_data varchar2(2000);
489 l_msg_count NUMBER;
490 l_item_supplied NUMBER;
491 t_org_id NUMBER;
492 t_subinv_code varchar2(30);
493 t_source_org_id NUMBER;
494 t_source_subinv_code varchar2(30);
495 t_supply_level NUMBER;
496 l_planned_item NUMBER;
497 l_lower_supply_level NUMBER;
498 BEGIN
499 l_inventory_item_Id := p_inventory_item_id;
500 l_supply_level := p_supply_level;
501 OPEN get_supply_chain(p_inventory_item_Id,p_org_id ,p_sub_inventory,p_supply_level);
502 LOOP
503 FETCH get_supply_chain INTO l_org_id,l_sec_subinventory,l_lower_supply_level ;
504 EXIT WHEN get_supply_chain % NOTFOUND;
505 l_supply_level := l_lower_supply_level;
506 l_cumulative_att := 0;
507 LOOP
508 l_att := 0;
509 l_highest_level_present := FALSE ;
510 --dbms_output.put_line('before parse_supply_chain '|| l_inventory_item_Id || l_org_id || l_supply_level);
511
512 OPEN get_supply_level_in_SC(l_inventory_item_Id,l_org_id,l_sec_subinventory,l_supply_level);
513 LOOP
514 FETCH get_supply_level_in_SC INTO l_org_id,l_sec_subinventory,l_source_org_id,l_source_sub_inventory;
515 EXIT WHEN get_supply_level_in_SC % NOTFOUND ;
516 --dbms_output.put_line('get_supply_level_in_SC '|| 'org id' || l_org_id ||
517 -- 'sec inventory ' || l_sec_subinventory || 'source org id ' || l_source_org_id ||
518 -- 'source sub inv ' || l_source_sub_inventory || l_supply_level);
519
520 IF l_source_org_id IS NOT NULL THEN
521 l_highest_level_present := TRUE;
522 END IF;
523 END LOOP;
524 CLOSE get_supply_level_in_SC;
525 IF l_supply_level = l_lower_supply_level THEN
526 /*CSP_SCH_INT_PVT.CHECK_LOCAL_INVENTORY( p_org_id => l_org_id
527 ,p_subinv_code => l_sec_subinventory
528 ,p_item_id => l_inventory_item_Id
529 ,x_att => l_att
530 ,x_onhand => l_onhand
531 ,x_return_status => l_return_status
532 ,x_msg_data => l_msg_data
533 ,x_msg_count => l_msg_count);
534 --dbms_output.put_line('p_orgid P_sub_inv p_item_id x_attt ');
535 --dbms_output.put_line(l_org_id || ' ' || l_sec_subinventory || ' ' || l_inventory_item_Id || ' ' || l_att );
536 IF l_att <= p_rop THEN*/
537 t_org_id := l_org_id;
538 t_subinv_code := l_sec_subinventory;
539 t_supply_level:=1;
540 LOOP
541 t_source_org_id := null;
542 t_source_subinv_code := null;
543 OPEN get_sources(l_inventory_item_Id,t_org_id, t_subinv_code,t_supply_level);
544 LOOP
545 FETCH get_sources INTO t_source_org_id, t_source_subinv_code;
546 EXIT WHEN get_sources % NOTFOUND;
547 END LOOP;
548 CLOSE get_sources;
549 IF t_source_org_id is NULL THEN
550 x_top_org_id := t_org_id;
551 EXIT;
552 ELSE
553 t_org_id := t_source_org_id;
554 t_subinv_code := t_source_subinv_code;
555 t_supply_level := t_supply_level + 1;
556 END IF;
557 END LOOP;
558 OPEN get_planned_item(l_inventory_item_id,t_org_id);
559 LOOP
560 FETCH get_planned_item INTO l_planned_item;
561 EXIT WHEN get_planned_item % NOTFOUND;
562 END LOOP;
563 CLOSE get_planned_item;
564 IF l_planned_item = l_inventory_item_Id THEN
565 x_item_supplied := l_inventory_item_Id;
566 EXIT;
567 END IF;
568 IF l_source_org_id IS NOT NULL THEN
569 CSP_SCH_INT_PVT.CHECK_LOCAL_INVENTORY( p_org_id => l_source_org_id
570 ,p_revision => null
571 ,p_subinv_code => l_source_sub_inventory
572 ,p_item_id => l_inventory_item_Id
573 ,x_att => l_att
574 ,x_onhand => l_onhand
575 ,x_return_status => l_return_status
576 ,x_msg_data => l_msg_data
577 ,x_msg_count => l_msg_count);
578 l_cumulative_att := l_cumulative_att + l_att;
579 END IF;
580 --dbms_output.put_line('p_orgid P_sub_inv p_item_id x_attt ');
581 --dbms_output.put_line(l_source_org_id || ' ' || l_source_sub_inventory || ' ' || l_inventory_item_Id || ' ' || l_att );
582 --END IF;
583 ELSE
584 l_att := 0;
585 CSP_SCH_INT_PVT.CHECK_LOCAL_INVENTORY( p_org_id => l_source_org_id
586 ,p_revision => null
587 ,p_subinv_code => l_source_sub_inventory
588 ,p_item_id => l_inventory_item_Id
589 ,x_att => l_att
590 ,x_onhand => l_onhand
591 ,x_return_status => l_return_status
592 ,x_msg_data => l_msg_data
593 ,x_msg_count => l_msg_count);
594 --dbms_output.put_line('p_orgid P_sub_inv p_item_id x_attt ');
595 --dbms_output.put_line(l_source_org_id || ' ' || l_source_sub_inventory || ' ' || l_inventory_item_Id || ' ' || l_att );
596 l_cumulative_att := l_cumulative_att + l_att;
597 END IF;
598 IF l_cumulative_att > p_rop THEN
599 x_item_supplied := l_inventory_item_Id;
600 END IF;
601 IF x_item_supplied IS NOT NULL OR NOT l_highest_level_present THEN
602 EXIT;
603 END IF;
604 l_org_id := l_source_org_id;
605 l_sec_subinventory := l_source_sub_inventory;
606 l_supply_level := l_supply_level + 1;
607 END LOOP;
608 IF x_item_supplied IS NOT NULL THEN
609 EXIT;
610 END IF;
611 l_org_id := NULL;
612 l_sec_subinventory := NULL;
613 l_supply_level := NULL;
614 END LOOP;
615 CLOSE get_supply_chain;
616
617 END parse_supply_chain;
618
619 PROCEDURE get_item_planned IS
620
621 CURSOR get_master_organizations IS
622 select distinct(mp.MASTER_ORGANIZATION_ID)
623 from csp_planning_parameters cpp,mtl_parameters mp
624 where cpp.NODE_TYPE IN('SUBINVENTORY','ORGANIZATION_WH')
625 and mp.ORGANIZATION_ID = cpp.ORGANIZATION_ID;
626
627 CURSOR get_replaced_items(c_master_org_id number)
628 IS
629 select mri.inventory_item_id
630 from MTL_RELATED_ITEMS mri
631 where mri.ORGANIZATION_ID = c_master_org_id
632 and mri.RELATIONSHIP_TYPE_ID = 8;
633
634 CURSOR get_repairable_items(c_item_id NUMBER, c_master_org_id NUMBER)
635 IS
636 select mri.inventory_item_id
637 from MTL_RELATED_ITEMS mri
638 where mri.ORGANIZATION_ID = c_master_org_id
639 and mri.RELATIONSHIP_TYPE_ID = 18
640 and mri.inventory_item_id <> c_item_id
641 and mri.RELATED_ITEM_ID = c_item_id ;
642
643 /* CURSOR get_central_warehouse(c_master_org_id number,c_inv_item_id number)
644 IS
645 SELECT DISTINCT csc.ORGANIZATION_ID,DECODE(csc.SECONDARY_INVENTORY,'-',NULL)
646 FROM CSP_SUPPLY_CHAIN csc, mtl_parameters mp
647 WHERE mp.MASTER_ORGANIZATION_ID = c_master_org_id
648 and csc.INVENTORY_ITEM_ID = c_inv_item_id
649 and csc.organization_id = mp.organization_id
650 and csc.SOURCE_ORGANIZATION_ID IS NULL;*/
651 -- and decode(csc.SECONDARY_INVENTORY,'-', null,csc.SECONDARY_INVENTORY) IS NULL;
652
653 CURSOR get_central_warehouse(c_master_org_id number,c_inv_item_id number)
654 IS
655 SELECT DISTINCT csc.ORGANIZATION_ID,DECODE(csc.SECONDARY_INVENTORY,'-',NULL),csc.supply_level
656 FROM CSP_SUPPLY_CHAIN csc, mtl_parameters mp, csp_planning_parameters cpp
657 WHERE mp.MASTER_ORGANIZATION_ID = c_master_org_id
658 and csc.INVENTORY_ITEM_ID = c_inv_item_id
659 and csc.organization_id = mp.organization_id
660 and cpp.organization_id = csc.organization_id
661 and nvl(cpp.SECONDARY_INVENTORY,'-') = csc.SECONDARY_INVENTORY
662 and cpp.node_type = 'ORGANIZATION_WH'
663 order by csc.supply_level desc;
664
665
666
667 CURSOR get_replacing_item(c_master_org_id NUMBER, c_item_id NUMBER)
668 IS
669 SELECT mri.related_item_id
670 FROM MTL_RELATED_ITEMS mri
671 WHERE mri.ORGANIZATION_ID = c_master_org_id
672 AND mri.RELATIONSHIP_TYPE_ID = 8
673 AND mri.inventory_item_id = c_item_id;
674
675 CURSOR get_rop(c_item_id NUMBER, c_org_id NUMBER)
676 IS
677 /* SELECT NVL(MIN_MINMAX_QUANTITY,0)
678 from mtl_system_items_b
679 where organization_id = c_org_id
680 and inventory_item_id = c_item_id;*/
681 SELECT nvl(decode(cpr.newbuy_rop,0,null), NVL(MIN_MINMAX_QUANTITY,0))
682 from mtl_system_items_b msib, csp_plan_reorders cpr
683 where msib.organization_id = c_org_id
684 and msib.inventory_item_id = c_item_id
685 and cpr.organization_id = msib.organization_id
686 and cpr.inventory_item_id = msib.inventory_item_id ;
687
688
689 cursor check_item_planned_exists(c_org_id number,c_item_id number) is
690 select 'Y'
691 from csp_supersede_items
692 where organization_id=c_org_id
693 and inventory_item_id=c_item_id
694 and sub_inventory_code ='-';
695
696 l_item_being_planned NUMBER;
697 l_planned_item_id NUMBER;
698 l_master_org_id NUMBER;
699 l_top_org NUMBER;
700 l_cumulative_att NUMBER;
701 l_inv_item_id NUMBER;
702 l_repairable_item_flag boolean := false;
703 l_scrap varchar2(3);
704 l_return_status varchar2(10);
705 l_subinv_code varchar2(30);
706 l_supply_level number;
707 l_exists varchar2(3);
708
709 BEGIN
710 OPEN get_master_organizations;
711 LOOP
712 FETCH get_master_organizations INTO l_master_org_id;
713 EXIT WHEN get_master_organizations % NOTFOUND;
714 OPEN get_replaced_items(l_master_org_id);
715 LOOP
716 FETCH get_replaced_items INTO l_item_being_planned;
717 EXIT WHEN get_replaced_items % NOTFOUND;
718 OPEN get_central_warehouse(l_master_org_id,l_item_being_planned) ;
719 LOOP
720 FETCH get_central_warehouse INTO l_top_org,l_subinv_code,l_supply_level ;
721 EXIT WHEN get_central_warehouse % NOTFOUND;
722 l_exists := NULL;
723 /** some times same central org can be found in tow supply chains and hence above cursor can
724 return duplicate central warehouse hence we have to check whether item planned is already calculated or not **/
725 OPEN check_item_planned_exists(l_top_org,l_item_being_planned);
726 FETCH check_item_planned_exists INTO l_exists;
727 CLOSE check_item_planned_exists;
728 IF l_exists <> 'Y' or l_exists IS NULL THEN
729 insert_item_planned(p_master_org_id => l_master_org_id
730 ,p_org_id => l_top_org
731 ,p_item_id => l_item_being_planned
732 ,p_subinv_code => l_subinv_code
733 ,p_supply_chain_id => l_supply_level
734 ,x_return_status => l_return_status);
735 END IF;
736
737 END LOOP;
738 CLOSE get_central_warehouse;
739 END LOOP;
740 CLOSE get_replaced_items;
741 END LOOP;
742 CLOSE get_master_organizations;
743 COMMIT;
744 END get_item_planned;
745 PROCEDURE insert_item_planned(p_master_org_id IN NUMBER
746 ,p_org_id IN NUMBER
747 ,p_item_id IN NUMBER
748 ,p_subinv_code IN VARCHAR2
749 ,p_supply_chain_id IN NUMBER
750 ,x_return_status OUT NOCOPY Varchar2) IS
751 l_rop NUMBER ;
752 l_cumulative_att NUMBER := 0;
753 l_scrap varchar2(20);
754 l_att NUMBER;
755 l_onhand NUMBER;
756 l_return_status VARCHAR2(10);
757 l_msg_data VARCHAR2(2000);
758 l_msg_count NUMBER;
759 l_planned_item_id NUMBER;
760 l_temp_inv_item_id NUMBER;
761 l_org_id NUMBER;
762 l_sub_inv_code varchar2(30);
763 l_repairable_item_flag boolean := false;
764 repairable_count NUMBER;
765 l_position NUMBER := 0;
766 l_repairable_tbl number_arr;
767 l_sql_string varchar2(1000);
768 l_where_string varchar2(500) ;
769 l_cur v_cur_type;
770 l_repairable_item NUMBER;
771 l_item_id NUMBER;
772 l_replaced_item NUMBER;
773 l_supersede_id NUMBER;
774 l_item_being_planned NUMBER;
775 l_item_rec csp_planner_notifications.item_list_rectype;
776 l_excess_parts_tbl csp_planner_notifications.excess_parts_tbl;
777 l_repairable_to_self varchar2(3);
778 l_excess_quantity number;
779 l_subinv_code varchar2(30);
780 l_source_subinv_code varchar2(30);
781 l_supply_level number;
782 l_source_planned_item_id number;
783 l_source_org_id number;
784 l_previou_source_org_id number;
785
786 CURSOR get_rop
787 IS
788 /*SELECT NVL(MIN_MINMAX_QUANTITY,0)
789 from mtl_system_items_b
790 where organization_id = p_org_id
791 and inventory_item_id = p_item_id;*/
792 SELECT nvl(decode(cpr.newbuy_rop,0,null), NVL(MIN_MINMAX_QUANTITY,0))
793 from mtl_system_items_b msib, csp_plan_reorders cpr
794 where msib.organization_id = p_org_id
795 and msib.inventory_item_id = p_item_id
796 and cpr.organization_id = msib.organization_id
797 and cpr.inventory_item_id = msib.inventory_item_id ;
798
799
800 /* CURSOR get_defective_sources
801 IS
802 SELECT SOURCE_ORGANIZATION_ID, SOURCE_SUBINVENTORY
803 FROM CSP_SOURCES
804 WHERE ORGANIZATION_ID = p_org_id
805 AND CONDITION_TYPE = 'B';*/
806
807 CURSOR get_defective_sources
808 IS
809 select msv.source_organization_id,NULL
810 from csp_planning_parameters cpp, mrp_sources_V msv
811 where cpp.organization_id = p_org_id
812 and cpp.NODE_TYPE = 'ORGANIZATION_WH'
813 and msv.assignment_set_id = cpp.DEFECTIVE_ASSIGNMENT_SET_ID
814 and msv.inventory_item_id = l_item_id
815 and msv.organization_id = p_org_id;
816
817
818
819 CURSOR is_item_repairable_to_self IS
820 select decode(mri.inventory_item_id,p_item_id,'Y','N')
821 from mtl_related_items mri,mtl_parameters mp
822 where mp.organization_id = p_org_id
823 and mri.organization_id = mp.master_organization_id
824 and mri.inventory_item_id = p_item_id
825 and mri.RELATIONSHIP_TYPE_ID = 18;
826
827 cursor get_usable_subinventories IS
828 /* select msa.SECONDARY_INVENTORY_NAME
829 from MTL_SUBINVENTORIES_ALL_V msa , CSP_SEC_INVENTORIES_V csi
830 where msa.organization_id = p_org_id
831 and csi.organization_id = msa.organization_id
832 and csi.SECONDARY_INVENTORY_NAME = msa.SECONDARY_INVENTORY_NAME
833 and csi.condition_type <> 'B';*/
834 select msa.SECONDARY_INVENTORY_NAME
835 from mtl_secondary_inventories msa , CSP_SEC_INVENTORIES csi
836 where msa.organization_id = p_org_id
837 and csi.organization_id = msa.organization_id
838 and csi.SECONDARY_INVENTORY_NAME = msa.SECONDARY_INVENTORY_NAME
839 and csi.condition_type <> 'B';
840
841 cursor get_source_planned_item IS
842 select csi.item_supplied,csc.source_organization_id,csc.supply_level,csc.source_subinventory
843 from csp_supersede_items csi,csp_supply_chain csc
844 where csc.organization_id= l_previou_source_org_id
845 and csc.secondary_inventory = nvl(l_source_subinv_code,'-')
846 and csc.inventory_item_id = l_item_id
847 and csc.supply_level = l_supply_level
848 and csi.inventory_item_id(+) = l_item_id
849 and csi.organization_id (+) = csc.source_organization_id;
850
851 BEGIN
852 l_item_id := p_item_id;
853 l_supply_level := p_supply_chain_id;
854 l_source_subinv_code := p_subinv_code;
855 l_source_org_id := p_org_id;
856 OPEN get_rop;
857 LOOP
858 FETCH get_rop INTO l_rop;
859 EXIT WHEN get_rop % NOTFOUND ;
860 END LOOP;
861 CLOSE get_rop;
862 -- l_rop := 5000000;
863 LOOP
864 l_cumulative_att := 0;
865 OPEN is_item_scrap(l_item_id, l_source_org_id);
866 LOOP
867 FETCH is_item_scrap INTO l_scrap;
868 EXIT WHEN is_item_scrap % NOTFOUND;
869 --dbms_output.put_line('scrap-- '|| l_scrap);
870 END LOOP;
871 CLOSE is_item_scrap;
872 IF l_scrap = 'N' THEN
873 IF p_subinv_code is not null then
874 CSP_SCH_INT_PVT.CHECK_LOCAL_INVENTORY( p_org_id => l_source_org_id
875 ,p_revision => null
876 ,p_subinv_code => p_subinv_code
877 ,p_item_id => l_item_id
878 ,x_att => l_att
879 ,x_onhand => l_onhand
880 ,x_return_status => l_return_status
881 ,x_msg_data => l_msg_data
882 ,x_msg_count => l_msg_count);
883 l_cumulative_att := l_cumulative_att + l_att;
884 ELSE
885 OPEN get_usable_subinventories;
886 LOOP
887 FETCH get_usable_subinventories INTO l_subinv_code;
888 EXIT WHEN get_usable_subinventories % NOTFOUND;
889 CSP_SCH_INT_PVT.CHECK_LOCAL_INVENTORY( p_org_id => l_source_org_id
890 ,p_revision => null
891 ,p_subinv_code => l_subinv_code
892 ,p_item_id => l_item_id
893 ,x_att => l_att
894 ,x_onhand => l_onhand
895 ,x_return_status => l_return_status
896 ,x_msg_data => l_msg_data
897 ,x_msg_count => l_msg_count);
898 l_cumulative_att := l_att + l_cumulative_att ;
899 l_att := 0;
900 END LOOP;
901 CLOSE get_usable_subinventories;
902 END IF;
903
904 /*IF l_att >= l_rop THEN
905 IF l_repairable_item_flag THEN
906 l_planned_item_id := l_temp_inv_item_id;
907 ELSE
908 l_planned_item_id := l_item_id;
909 END IF;
910 EXIT;
911 ELSE*/
912 IF p_subinv_code IS NULL THEN
913 l_item_rec.inventory_item_id := l_item_id;
914 CSP_PLANNER_NOTIFICATIONS.Calculate_Excess(
915 p_organization_id => l_source_org_id
916 ,p_item_rec => l_item_rec
917 ,p_called_from => 'SUPERSEDE'
918 ,p_notification_id => null
919 ,x_excess_parts_tbl => l_excess_parts_tbl
920 ,x_return_status => l_return_status
921 ,x_msg_data => l_msg_data
922 ,x_msg_count => l_msg_count);
923 l_excess_quantity := 0;
924 IF l_return_status = FND_API.G_RET_STS_SUCCESS and l_excess_parts_tbl.count > 0 THEN
925 FOR I IN 1..l_excess_parts_tbl.count LOOP
926 l_excess_quantity := l_excess_quantity + l_excess_parts_tbl(I).quantity ;
927 -- l_cumulative_att := l_cumulative_att + l_excess_parts_tbl(I).quantity ;
928 END LOOP;
929 END IF;
930 l_cumulative_att := l_cumulative_att + l_excess_quantity ;
931 l_cumulative_att := l_cumulative_att + l_excess_quantity;
932 IF l_cumulative_att > l_rop and l_excess_quantity > 0 THEN
933 IF l_repairable_item_flag THEN
934 l_planned_item_id := l_temp_inv_item_id;
935 ELSE
936 l_planned_item_id := l_item_id;
937 END IF;
938 EXIT;
939 END IF;
940 OPEN is_item_repairable_to_self;
941 fetch is_item_repairable_to_self INTO l_repairable_to_self;
942 CLOSE is_item_repairable_to_self;
943 IF l_planned_item_id is null and (l_repairable_to_self= 'Y') THEN
944 OPEN get_defective_sources;
945 LOOP
946 FETCH get_defective_sources INTO l_org_id, l_sub_inv_code;
947 EXIT WHEN get_defective_sources % NOTFOUND;
948 l_att := 0;
949 CSP_SCH_INT_PVT.CHECK_LOCAL_INVENTORY(p_org_id => l_org_id
950 ,p_revision => null
951 ,p_subinv_code => l_sub_inv_code
952 ,p_item_id => l_item_id
953 ,x_att => l_att
954 ,x_onhand => l_onhand
955 ,x_return_status => l_return_status
956 ,x_msg_data => l_msg_data
957 ,x_msg_count => l_msg_count);
958 l_cumulative_att := l_cumulative_att + l_att ;
959 IF l_cumulative_att > l_rop and l_att > 0 THEN
960 IF l_repairable_item_flag THEN
961 l_planned_item_id := l_temp_inv_item_id;
962 ELSE
963 l_planned_item_id := l_item_id;
964 END IF;
965 EXIT;
966 END IF;
967 END LOOP;
968 CLOSE get_defective_sources;
969 END IF;
970 END IF;
971 -- END IF;
972 IF p_subinv_code IS NULL THEN
973 IF l_planned_item_id is null THEN
974 repairable_count := 1;
975 l_position := l_position + 1;
976 IF l_repairable_tbl.count = 0 THEN
977 l_temp_inv_item_id := l_item_id;
978 IF l_where_string IS NULL THEN
979 BUILD_NOT_IN_CONDITION(p_master_org_id,l_item_id, l_where_string);
980 END IF;
981 IF l_where_string IS NOT NULL THEN
982 l_sql_string := 'select mri.inventory_item_id
983 from MTL_RELATED_ITEMS mri,mtl_system_items_b msi
984 where mri.ORGANIZATION_ID = ' || p_org_id ||
985 'and mri.RELATIONSHIP_TYPE_ID = 18
986 and mri.inventory_item_id <> ' || l_item_id ||
987 'and mri.RELATED_ITEM_ID = ' || l_item_id ||
988 'and mri.inventory_item_id NOT IN ';
989 l_sql_string := l_sql_string || l_where_string ||
990 ' and msi.inventory_item_id = mri.inventory_item_id and
991 MSI.MTL_TRANSACTIONS_ENABLED_FLAG =' || '''' || 'Y' || '''' ||
992 ' and msi.organization_id = mri.ORGANIZATION_ID' ;
993 ELSE
994 l_sql_string := 'select mri.inventory_item_id
995 from MTL_RELATED_ITEMS mri,mtl_system_items_b msi
996 where mri.ORGANIZATION_ID = ' || p_org_id ||
997 'and mri.RELATIONSHIP_TYPE_ID = 18
998 and mri.inventory_item_id <> ' || l_item_id ||
999 'and mri.RELATED_ITEM_ID = ' || l_item_id ||
1000 ' and msi.inventory_item_id = mri.inventory_item_id and
1001 MSI.MTL_TRANSACTIONS_ENABLED_FLAG =' || '''' || 'Y' || '''' ||
1002 ' and msi.organization_id = mri.ORGANIZATION_ID' ;
1003 END IF;
1004 OPEN l_cur for
1005 l_sql_string;
1006 LOOP
1007 FETCH l_cur into l_repairable_item;
1008 EXIT WHEN l_cur % NOTFOUND;
1009 l_repairable_tbl(repairable_count) := l_repairable_item;
1010 repairable_count := repairable_count + 1;
1011 END LOOP;
1012 CLOSE l_cur;
1013 --l_position := 0;
1014 IF l_repairable_tbl.count > 0 THEN
1015 l_item_id := l_repairable_tbl(1);
1016 l_repairable_item_flag := TRUE;
1017 --l_position := 1;
1018 END IF;
1019 ELSIF l_repairable_tbl.count > l_position THEN
1020 -- l_position := l_position + 1;
1021 l_item_id := l_repairable_tbl(l_position);
1022 l_repairable_item_flag := TRUE;
1023 END IF;
1024 END IF;
1025 ELSE
1026 l_position := 1;
1027 END IF;
1028 ELSE
1029 l_position := 1;
1030 l_temp_inv_item_id := p_item_id ;
1031 END IF;
1032 IF l_planned_item_id is null and (l_position > l_repairable_tbl.count) then
1033 l_repairable_tbl.delete;
1034 l_repairable_item_flag := FALSE;
1035 -- Before going for replacing items check exisitence of higher levels in the supply chain if yes then check for the item availability in those orgs
1036 l_previou_source_org_id := l_source_org_id;
1037 OPEN get_source_planned_item;
1038 l_source_org_id := null;
1039 l_source_subinv_code := null;
1040 FETCH get_source_planned_item INTO l_source_planned_item_id ,l_source_org_id,l_supply_level,l_source_subinv_code;
1041 CLOSE get_source_planned_item;
1042
1043 IF l_source_planned_item_id = l_item_id and l_scrap = 'N' then
1044 l_planned_item_id := l_item_id;
1045 exit;
1046 ELSE
1047 IF l_source_org_id IS NULL or l_scrap = 'Y' THEN
1048 l_cumulative_att := 0;
1049 l_item_id := l_temp_inv_item_id;
1050 l_replaced_item := null;
1051 l_source_org_id := p_org_id;
1052 OPEN get_replacing_item(p_master_org_id,l_item_id);
1053 LOOP
1054 FETCH get_replacing_item INTO l_replaced_item;
1055 EXIT WHEN get_replacing_item % NOTFOUND;
1056 END LOOP;
1057 CLOSE get_replacing_item;
1058 IF l_replaced_item IS NOT NULL THEN
1059 l_item_id := l_replaced_item;
1060 ELSE
1061 l_planned_item_id := l_item_id;
1062 END IF;
1063 END IF;
1064 l_position := 0;
1065 END IF;
1066 END IF;
1067 IF l_planned_item_id IS NOT NULL THEN
1068 EXIT;
1069 END IF;
1070 END LOOP;
1071
1072 --dbms_output.put_line('planned item ' || l_planned_item_id);
1073 l_supersede_id := NULL;
1074 CSP_SUPERSEDE_ITEMS_PKG.insert_row(px_supersede_id => l_supersede_id
1075 ,p_created_by => -1
1076 ,p_creation_date => sysdate
1077 ,p_last_updated_by => -1
1078 ,p_last_update_date=> sysdate
1079 ,p_last_update_login => -1
1080 ,p_inventory_item_id => p_item_id
1081 ,p_organization_id => p_org_id
1082 ,p_sub_inventory_code => '-'
1083 ,p_item_supplied => l_planned_item_id
1084 ,p_attribute_category => NULL
1085 ,p_attribute1 => NULL
1086 ,p_attribute2 => NULL
1087 ,p_attribute3 => NULL
1088 ,p_attribute4 => NULL
1089 ,p_attribute5 => NULL
1090 ,p_attribute6 => NULL
1091 ,p_attribute7 => NULL
1092 ,p_attribute8 => NULL
1093 ,p_attribute9 => NULL
1094 ,p_attribute10 => NULL
1095 ,p_attribute11 => NULL
1096 ,p_attribute12 => NULL
1097 ,p_attribute13 => NULL
1098 ,p_attribute14 => NULL
1099 ,p_attribute15 => NULL);
1100 l_planned_item_id := NULL;
1101 END insert_item_planned;
1102 PROCEDURE PURGE_OLD_SUPERSEDE_DATA
1103 IS
1104 l_get_app_info boolean;
1105 l_status varchar2(10);
1106 l_industry varchar2(10);
1107 l_oracle_schema varchar2(10);
1108 BEGIN
1109 l_get_app_info := fnd_installation.get_app_info('CSP',l_status,l_industry, l_oracle_schema);
1110 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUPERSEDE_ITEMS' ;
1111 END PURGE_OLD_SUPERSEDE_DATA;
1112 PROCEDURE BUILD_NOT_IN_CONDITION(p_master_org NUMBER,p_inventory_item_id NUMBER, x_where_string OUT NOCOPY varchar2)
1113 IS
1114 l_temp_replaced_item NUMBER;
1115 l_replacing_item NUMBER;
1116 BEGIN
1117 l_temp_replaced_item := p_inventory_item_id;
1118 LOOP
1119 OPEN get_replacing_item(p_master_org,l_temp_replaced_item);
1120 l_replacing_item := NULL;
1121 LOOP
1122 FETCH get_replacing_item INTO l_replacing_item;
1123 EXIT WHEN get_replacing_item % NOTFOUND;
1124 END LOOP;
1125 CLOSE get_replacing_item;
1126 IF l_replacing_item IS NOT NULL THEN
1127 l_temp_replaced_item := l_replacing_item;
1128 IF x_where_string IS NULL THEN
1129 x_where_string := '(' || p_inventory_item_id || ',' || l_replacing_item ;
1130 ELSE
1131 x_where_string := x_where_string || ', ' || l_replacing_item ;
1132 END IF;
1133 ELSE
1134 EXIT;
1135 END IF;
1136 END LOOP;
1137 IF x_where_string IS NOT NULL THEN
1138 x_where_string := x_where_string || ')' ;
1139 END IF;
1140 END BUILD_NOT_IN_CONDITION;
1141 PROCEDURE check_for_supersede_item(p_inventory_item_id IN NUMBER
1142 ,p_organization_id IN NUMBER
1143 ,x_supersede_item OUT NOCOPY NUMBER)
1144 IS
1145
1146 CURSOR get_supersede_item(c_inventory_item_id NUMBER, c_organization_id NUMBER) IS
1147 SELECT mri.related_item_id
1148 FROM mtl_related_items mri, mtl_parameters mp, mtl_system_items_b msi
1149 WHERE mp.organization_id = c_organization_id
1150 AND mri.organization_id = mp.master_organization_id
1151 AND mri.inventory_item_id = c_inventory_item_id
1152 AND mri.RELATIONSHIP_TYPE_ID = 8
1153 AND msi.organization_id = mri.organization_id
1154 AND msi.inventory_item_id = mri.inventory_item_id;
1155 -- AND msi.MTL_TRANSACTIONS_ENABLED_FLAG = 'Y';
1156
1157 l_scrap VARCHAR2(5);
1158 l_inventory_item_id NUMBER;
1159 l_replacing_item NUMBER;
1160 BEGIN
1161 l_inventory_item_id := p_inventory_item_id;
1162 LOOP
1163 OPEN is_item_scrap(l_inventory_item_id,p_organization_id);
1164 LOOP
1165 FETCH is_item_scrap INTO l_scrap;
1166 EXIT WHEN is_item_scrap % NOTFOUND;
1167 END LOOP;
1168 CLOSE is_item_scrap;
1169 IF l_scrap = 'N' THEN
1170 x_supersede_item := l_inventory_item_id;
1171 EXIT;
1172 END IF;
1173 OPEN get_supersede_item(l_inventory_item_id,p_organization_id) ;
1174 l_replacing_item := NULL;
1175 LOOP
1176 FETCH get_supersede_item INTO l_replacing_item;
1177 EXIT WHEN get_supersede_item % NOTFOUND;
1178 l_inventory_item_id := l_replacing_item;
1179 END LOOP;
1180 CLOSE get_supersede_item;
1181 IF l_replacing_item IS NULL THEN
1182 x_supersede_item := l_inventory_item_id;
1183 EXIT;
1184 END IF;
1185 END LOOP;
1186 END check_for_supersede_item;
1187 PROCEDURE get_supersede_bilateral_items(p_inventory_item_id IN NUMBER
1188 ,p_organization_id IN NUMBER
1189 ,x_supersede_items OUT NOCOPY CSP_SUPERSESSIONS_PVT.number_arr)
1190 IS
1191 l_temp_replaced_item NUMBER;
1192 l_replacing_item NUMBER;
1193 l_master_org NUMBER;
1194 l_count NUMBER := 0 ;
1195 l_scrap VARCHAR2(5);
1196 l_not_in_string varchar2(1000);
1197 l_sql_string varchar2(2000);
1198 l_cur v_cur_type;
1199 l_bilaterla_item NUMBER;
1200
1201 BEGIN
1202
1203 OPEN get_mster_org(p_organization_id);
1204 LOOP
1205 FETCH get_mster_org INTO l_master_org ;
1206 EXIT WHEN get_mster_org % NOTFOUND;
1207 END LOOP;
1208 CLOSE get_mster_org;
1209
1210 l_temp_replaced_item := p_inventory_item_id;
1211 l_not_in_string := '(' || p_inventory_item_id ;
1212 LOOP
1213 OPEN get_replacing_item(l_master_org,l_temp_replaced_item);
1214 l_replacing_item := NULL;
1215 LOOP
1216 FETCH get_replacing_item INTO l_replacing_item;
1217 EXIT WHEN get_replacing_item % NOTFOUND;
1218 l_not_in_string := l_not_in_string || ',' || l_replacing_item ;
1219 END LOOP;
1220 CLOSE get_replacing_item;
1221 IF l_replacing_item IS NOT NULL THEN
1222 OPEN is_item_scrap(l_replacing_item,l_master_org);
1223 LOOP
1224 FETCH is_item_scrap INTO l_scrap;
1225 EXIT WHEN is_item_scrap % NOTFOUND;
1226 END LOOP;
1227 CLOSE is_item_scrap;
1228 IF l_scrap = 'N' THEN
1229 l_count := l_count + 1;
1230 x_supersede_items(l_count) := l_replacing_item ;
1231 END IF;
1232 l_temp_replaced_item := l_replacing_item;
1233 ELSE
1234 EXIT;
1235 END IF;
1236 END LOOP;
1237 l_not_in_string := l_not_in_string || ')' ;
1238 l_sql_string := 'select MRI.inventory_item_id
1239 from MTL_RELATED_ITEMS mri , MTL_SYSTEM_ITEMS_B msi
1240 where mri.ORGANIZATION_ID =' || l_master_org ||
1241 'and mri.RELATIONSHIP_TYPE_ID = 8
1242 and mri.related_item_id =' || p_inventory_item_id ||
1243 'and mri.reciprocal_flag =' || '''' || 'Y' || '''' ||
1244 'and mri.inventory_item_id <>' || p_inventory_item_id ||
1245 'and MSI.ORGANIZATION_ID =' || l_master_org ||
1246 'and MSI.inventory_item_id = mri.inventory_item_id
1247 and MSI.MTL_TRANSACTIONS_ENABLED_FLAG =' || '''' || 'Y' || '''' ||
1248 'and MRI.inventory_item_id NOT IN ' || l_not_in_string;
1249
1250 OPEN l_cur FOR l_sql_string;
1251 LOOP
1252 FETCH l_cur INTO l_bilaterla_item;
1253 EXIT WHEN l_cur % NOTFOUND;
1254 l_count := l_count + 1;
1255 x_supersede_items(l_count) := l_bilaterla_item ;
1256 END LOOP;
1257 CLOSE l_cur;
1258 END get_supersede_bilateral_items;
1259 PROCEDURE get_top_supersede_item(p_item_id IN NUMBER
1260 ,p_org_id IN NUMBER
1261 ,x_item_id OUT NOCOPY NUMBER)
1262 IS
1263 l_temp_replaced_item NUMBER;
1264 l_replacing_item NUMBER;
1265 l_master_org NUMBER;
1266 BEGIN
1267 l_temp_replaced_item := p_item_id ;
1268 IF p_org_id IS NOT NULL THEN
1269 OPEN get_mster_org(p_org_id);
1270 LOOP
1271 FETCH get_mster_org INTO l_master_org ;
1272 EXIT WHEN get_mster_org % NOTFOUND;
1273 END LOOP;
1274 CLOSE get_mster_org;
1275 END IF;
1276 LOOP
1277 OPEN get_replacing_item(l_master_org, l_temp_replaced_item) ;
1278 l_replacing_item := NULL;
1279 LOOP
1280 FETCH get_replacing_item INTO l_replacing_item;
1281 EXIT WHEN get_replacing_item % NOTFOUND;
1282 END LOOP;
1283 CLOSE get_replacing_item;
1284 IF l_replacing_item IS NOT NULL THEN
1285 l_temp_replaced_item := l_replacing_item;
1286 ELSE
1287 x_item_id := l_temp_replaced_item;
1288 EXIT;
1289 END IF;
1290 END LOOP;
1291 END get_top_supersede_item;
1292 PROCEDURE get_replaced_items_list(p_inventory_item_id IN NUMBER
1293 ,p_organization_id IN NUMBER
1294 ,x_replaced_item_list OUT NOCOPY VARCHAR2)
1295 IS
1296 CURSOR get_replaced_item(c_master_org_id NUMBER, c_item_id NUMBER)
1297 IS
1298 select mri.inventory_item_id
1299 from MTL_RELATED_ITEMS mri
1300 where mri.ORGANIZATION_ID = nvl(c_master_org_id,mri.ORGANIZATION_ID)
1301 and mri.RELATIONSHIP_TYPE_ID = 8
1302 and mri.related_item_id = c_item_id;
1303
1304 l_master_org NUMBER;
1305 l_temp_replacing_item NUMBER;
1306 l_replaced_item NUMBER;
1307 BEGIN
1308 x_replaced_item_list := NULL;
1309 l_temp_replacing_item := p_inventory_item_id ;
1310 IF p_organization_id IS NOT NULL THEN
1311 OPEN get_mster_org(p_organization_id);
1312 LOOP
1313 FETCH get_mster_org INTO l_master_org ;
1314 EXIT WHEN get_mster_org % NOTFOUND;
1315 END LOOP;
1316 CLOSE get_mster_org;
1317 END IF;
1318 LOOP
1319 OPEN get_replaced_item(l_master_org, l_temp_replacing_item) ;
1320 l_replaced_item := NULL;
1321 LOOP
1322 FETCH get_replaced_item INTO l_replaced_item;
1323 EXIT WHEN get_replaced_item % NOTFOUND;
1324 END LOOP;
1325 CLOSE get_replaced_item;
1326 IF l_replaced_item IS NOT NULL THEN
1327 IF x_replaced_item_list IS NULL THEN
1328 x_replaced_item_list := '(' || l_replaced_item ;
1329 ELSE
1330 x_replaced_item_list := x_replaced_item_list || ' ,' || l_replaced_item;
1331 END IF;
1332 l_temp_replacing_item := l_replaced_item ;
1333 ELSE
1334 IF x_replaced_item_list IS NOT NULL THEN
1335 x_replaced_item_list := x_replaced_item_list || ')' ;
1336 END IF;
1337 EXIT;
1338 END IF;
1339 END LOOP;
1340 END get_replaced_items_list;
1341
1342 PROCEDURE PROCESS_SUPERSESSIONS(p_level_id IN VARCHAR2
1343 ,p_commit IN VARCHAR2
1344 ,x_return_status OUT NOCOPY VARCHAR2
1345 ,x_msg_data OUT NOCOPY varchar2
1346 ,x_msg_count OUT NOCOPY NUMBER)
1347 IS
1348 l_organization_id NUMBER;
1349 l_subinv_code varchar2(30);
1350 l_master_org_id NUMBER;
1351 l_return_status varchar2(3);
1352
1353 CURSOR get_org_subinv IS
1354 select cpp.ORGANIZATION_ID, cpp.SECONDARY_INVENTORY, mp.master_organization_id
1355 from csp_planning_parameters cpp, mtl_parameters mp
1356 where cpp.level_id = p_level_id
1357 and mp.ORGANIZATION_ID = cpp.organization_id ;
1358
1359 CURSOR get_supply_chain IS
1360 select inventory_item_id,supply_level, SOURCE_ORGANIZATION_ID,supply_chain_id
1361 from csp_supply_chain
1362 where organization_id = l_organization_id
1363 and secondary_inventory = l_subinv_code;
1364
1365
1366 BEGIN
1367 x_return_status := FND_API.G_RET_STS_SUCCESS;
1368 savepoint PROCESS_SUPERSESSIONS;
1369 CSP_AUTO_ASLMSL_PVT.Create_supply_chain (
1370 P_Api_Version_Number => 1.0 ,
1371 P_Init_Msg_List => FND_API.G_TRUE,
1372 P_Commit => FND_API.G_FALSE,
1373 P_validation_level => FND_API.G_VALID_LEVEL_FULL,
1374 P_Level_id => p_level_id,
1375 X_Return_Status => x_return_status,
1376 X_Msg_Count => x_msg_count ,
1377 X_Msg_Data => x_msg_data
1378 );
1379 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1380 ROLLBACK TO PROCESS_SUPERSESSIONS;
1381 return;
1382 END IF;
1383 OPEN get_org_subinv;
1384 FETCH get_org_subinv INTO l_organization_id, l_subinv_code,l_master_org_id;
1385 CLOSE get_org_subinv;
1386 FOR gsc IN get_supply_chain LOOP
1387 IF gsc.source_organization_id IS NOT NULL THEN
1388 insert_item_supplied(p_planned_subinv_code => l_subinv_code
1389 ,p_planned_org_id => l_organization_id
1390 ,p_replaced_item_id => gsc.inventory_item_id
1391 ,p_supply_level => gsc.supply_level
1392 ,p_master_org_id => l_master_org_id
1393 ,x_return_status => l_return_status );
1394 ELSE
1395 insert_item_planned(p_master_org_id => l_master_org_id
1396 ,p_org_id => l_organization_id
1397 ,p_item_id => gsc.inventory_item_id
1398 ,p_subinv_code => l_subinv_code
1399 ,p_supply_chain_id => gsc.supply_chain_id
1400 ,x_return_status => l_return_status);
1401 END IF;
1402 END LOOP;
1403 IF FND_API.to_Boolean( p_commit)
1404 THEN
1405 commit work;
1406 END IF;
1407
1408 EXCEPTION
1409 WHEN OTHERS THEN
1410 x_return_status := FND_API.G_RET_STS_ERROR;
1411 ROLLBACK TO PROCESS_SUPERSESSIONS;
1412 END PROCESS_SUPERSESSIONS;
1413 PROCEDURE check_for_duplicate_parts(l_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
1414 ,p_org_id IN NUMBER
1415 ,x_return_status OUT NOCOPY varchar2
1416 ,x_message OUT NOCOPY varchar2
1417 ,x_msg_count OUT NOCOPY NUMBER) IS
1418 l_temp_item NUMBER;
1419 l_duplicate BOOLEAN := false ;
1420 l_duplicate_item NUMBER;
1421 l_supersede_item_id NUMBER;
1422 l_already_processed BOOLEAN := false ;
1423 l_temp_item_number varchar2(240);
1424 l_duplicate_item_number varchar2(240);
1425
1426
1427 TYPE supersede_item_rec_type IS RECORD(l_item NUMBER
1428 ,l_supersede_item NUMBER);
1429
1430 TYPE supersede_item_table_type IS TABLE OF supersede_item_rec_type;
1431
1432 l_supersede_items supersede_item_table_type;
1433
1434 CURSOR get_supersede_item(p_item_id number, p_org_id number) IS
1435 select RELATED_ITEM_ID
1436 from MTL_RELATED_ITEMS mriv, mtl_parameters mp
1437 where mp.organization_id = p_org_id
1438 and mriv.inventory_item_id = p_item_id
1439 and mriv. organization_id = mp.master_organization_id
1440 and mriv.RELATIONSHIP_TYPE_ID = 8;
1441
1442 cursor get_item_number(c_item_id number) IS
1443 select concatenated_segments
1444 from mtl_system_items_b_kfv
1445 where inventory_item_id = c_item_id
1446 and organization_id = cs_std.get_item_valdn_orgzn_id;
1447
1448
1449 BEGIN
1450 l_supersede_items := supersede_item_table_type();
1451 FOR I IN 1..l_parts_list.count LOOP
1452 l_temp_item := l_parts_list(I).item_id;
1453 WHILE l_temp_item is not null LOOP
1454 l_supersede_item_id := null;
1455 OPEN get_supersede_item(l_temp_item,p_org_id);
1456 LOOP
1457 FETCH get_supersede_item INTO l_supersede_item_id;
1458 EXIT WHEN get_supersede_item %NOTFOUND;
1459 IF l_supersede_item_id <> l_parts_list(I).item_id THEN
1460 FOR J IN 1..l_supersede_items.count LOOP
1461 /*IF l_supersede_items(J).l_supersede_item = l_supersede_item_id THEN
1462 l_already_processed := true;
1463 exit;
1464 END IF;*/
1465 IF l_supersede_item_id = l_supersede_items(J).l_supersede_item THEN
1466 l_duplicate := TRUE;
1467 l_duplicate_item := l_supersede_items(J).l_item;
1468 x_return_status := FND_API.G_RET_STS_ERROR;
1469 l_duplicate_item_number := null;
1470 OPEN get_item_number(l_duplicate_item);
1471 FETCH get_item_number INTO l_duplicate_item_number;
1472 CLOSE get_item_number;
1473 l_temp_item_number := null;
1474 OPEN get_item_number(l_parts_list(I).item_id);
1475 FETCH get_item_number INTO l_temp_item_number;
1476 CLOSE get_item_number;
1477 /* x_message := x_message || 'Item : ' || l_duplicate_item || 'and Item : ' || l_temp_item || 'has same supersede item(s) so please remove one' ;*/
1478 FND_MESSAGE.SET_NAME('CSP', 'CSP_SAME_SUPERSEDE_ITEMS');
1479 FND_MESSAGE.SET_TOKEN('ITEM1', l_duplicate_item_number, TRUE);
1480 FND_MESSAGE.SET_TOKEN('ITEM2',l_temp_item_number, TRUE);
1481 FND_MSG_PUB.ADD;
1482 fnd_msg_pub.count_and_get
1483 ( p_count => x_msg_count
1484 , p_data => x_message);
1485 exit;
1486 END IF;
1487 END LOOP;
1488 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1489 exit;
1490 END IF;
1491 IF NOT l_already_processed THEN
1492 FOR K IN 1..l_parts_list.count LOOP
1493 IF l_supersede_item_id = l_parts_list(K).item_id THEN
1494 x_return_status := FND_API.G_RET_STS_ERROR;
1495 l_duplicate_item_number := null;
1496 l_duplicate_item := l_supersede_item_id;
1497 l_temp_item := l_parts_list(I).item_id;
1498 OPEN get_item_number(l_duplicate_item);
1499 FETCH get_item_number INTO l_duplicate_item_number;
1500 CLOSE get_item_number;
1501 l_temp_item_number := null;
1502 OPEN get_item_number(l_temp_item);
1503 FETCH get_item_number INTO l_temp_item_number;
1504 CLOSE get_item_number;
1505 /* x_message := x_message || 'Item : ' || l_duplicate_item || 'and Item : ' || l_temp_item || 'has same supersede item(s) so please remove one' ;*/
1506 FND_MESSAGE.SET_NAME('CSP', 'CSP_DUPLICATE_ITEMS');
1507 FND_MESSAGE.SET_TOKEN('ITEM1', l_duplicate_item_number, TRUE);
1508 FND_MESSAGE.SET_TOKEN('ITEM2',l_temp_item_number, TRUE);
1509 FND_MSG_PUB.ADD;
1510 fnd_msg_pub.count_and_get
1511 ( p_count => x_msg_count
1512 , p_data => x_message);
1513 exit;
1514 END IF;
1515 END LOOP;
1516 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1517 exit;
1518 END IF;
1519 l_temp_item := null;
1520 IF NOT l_duplicate THEN
1521 l_supersede_items.extend;
1522 l_supersede_items(l_supersede_items.count).l_item := l_parts_list(I).item_id;
1523 l_supersede_items(l_supersede_items.count).l_supersede_item := l_supersede_item_id ;
1524 -- l_temp_item := l_supersede_item_id;
1525 ELSE
1526 exit;
1527 END IF;
1528 END IF;
1529 END IF;
1530 END LOOP;
1531 CLOSE get_supersede_item;
1532 l_temp_item := l_supersede_item_id;
1533 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1534 return;
1535 END IF;
1536 END LOOP;
1537 END LOOP;
1538 END check_for_duplicate_parts;
1539 END CSP_SUPERSESSIONS_PVT;