1 PACKAGE BODY INVIDSCS as
2 /* $Header: INVIDSCB.pls 120.7.12010000.3 2009/05/26 07:06:59 maychen ship $ */
3
4 TYPE ORG_LISTS IS TABLE OF MTL_ITEM_CATEGORIES.ORGANIZATION_ID%TYPE;
5
6 PROCEDURE CHECK_CAT_SET_MANDATORY(
7 current_cat_set_id IN NUMBER,
8 func_area_flag1 OUT NOCOPY VARCHAR2,
9 func_area_flag2 OUT NOCOPY VARCHAR2,
10 func_area_flag3 OUT NOCOPY VARCHAR2,
11 func_area_flag4 OUT NOCOPY VARCHAR2,
12 func_area_flag5 OUT NOCOPY VARCHAR2,
13 func_area_flag6 OUT NOCOPY VARCHAR2,
14 func_area_flag7 OUT NOCOPY VARCHAR2,
15 func_area_flag8 OUT NOCOPY VARCHAR2,--Bug : 2527058
16 func_area_flag9 OUT NOCOPY VARCHAR2,
17 func_area_flag10 OUT NOCOPY VARCHAR2,
18 func_area_flag11 OUT NOCOPY VARCHAR2
19 ) IS
20
21 temp varchar2(10);
22
23 BEGIN
24 null;
25
26 BEGIN
27 select 'X' into temp
28 from MTL_DEFAULT_CATEGORY_SETS MDCS
29 where MDCS.category_set_id = current_cat_set_id
30 and MDCS.functional_area_id = 1;
31 /*If no data found then set flag to 'N'*/
32 func_area_flag1 := 'Y';
33 EXCEPTION
34 WHEN NO_DATA_FOUND THEN
35 func_area_flag1 := 'N';
36 END;
37
38 BEGIN
39 select 'X' into temp
40 from MTL_DEFAULT_CATEGORY_SETS MDCS
41 where MDCS.category_set_id = current_cat_set_id
42 and MDCS.functional_area_id = 2;
43 /*If no data found then set flag to 'N'*/
44 func_area_flag2 := 'Y';
45 EXCEPTION
46 WHEN NO_DATA_FOUND THEN
47 func_area_flag2 := 'N';
48 END;
49
50 BEGIN
51 select 'X' into temp
52 from MTL_DEFAULT_CATEGORY_SETS MDCS
53 where MDCS.category_set_id = current_cat_set_id
54 and MDCS.functional_area_id = 3;
55 /*If no data found then set flag to 'N'*/
56 func_area_flag3 := 'Y';
57 EXCEPTION
58 WHEN NO_DATA_FOUND THEN
59 func_area_flag3 := 'N';
60 END;
61
62 BEGIN
63 select 'X' into temp
64 from MTL_DEFAULT_CATEGORY_SETS MDCS
65 where MDCS.category_set_id = current_cat_set_id
66 and MDCS.functional_area_id = 4;
67 /*If no data found then set flag to 'N'*/
68 func_area_flag4 := 'Y';
69 EXCEPTION
70 WHEN NO_DATA_FOUND THEN
71 func_area_flag4 := 'N';
72 END;
73
74 BEGIN
75 select 'X' into temp
76 from MTL_DEFAULT_CATEGORY_SETS MDCS
77 where MDCS.category_set_id = current_cat_set_id
78 and MDCS.functional_area_id = 5;
79 /*If no data found then set flag to 'N'*/
80 func_area_flag5 := 'Y';
81 EXCEPTION
82 WHEN NO_DATA_FOUND THEN
83 func_area_flag5 := 'N';
84 END;
85
86 BEGIN
87 select 'X' into temp
88 from MTL_DEFAULT_CATEGORY_SETS MDCS
89 where MDCS.category_set_id = current_cat_set_id
90 and MDCS.functional_area_id = 6;
91 /*If no data found then set flag to 'N'*/
92 func_area_flag6 := 'Y';
93 EXCEPTION
94 WHEN NO_DATA_FOUND THEN
95 func_area_flag6 := 'N';
96 END;
97
98 BEGIN
99 select 'X' into temp
100 from MTL_DEFAULT_CATEGORY_SETS MDCS
101 where MDCS.category_set_id = current_cat_set_id
102 and MDCS.functional_area_id = 7;
103 /*If no data found then set flag to 'N'*/
104 func_area_flag7 := 'Y';
105 EXCEPTION
106 WHEN NO_DATA_FOUND THEN
107 func_area_flag7 := 'N';
108 END;
109 --Bug : 2527058 Added
110 BEGIN
111 select 'X' into temp
112 from MTL_DEFAULT_CATEGORY_SETS MDCS
113 where MDCS.category_set_id = current_cat_set_id
114 and MDCS.functional_area_id = 8;
115 /*If no data found then set flag to 'N'*/
116 func_area_flag8 := 'Y';
117 EXCEPTION
118 WHEN NO_DATA_FOUND THEN
119 func_area_flag8 := 'N';
120 END;
121
122 BEGIN
123 select 'X' into temp
124 from MTL_DEFAULT_CATEGORY_SETS MDCS
125 where MDCS.category_set_id = current_cat_set_id
126 and MDCS.functional_area_id = 9;
127 /*If no data found then set flag to 'N'*/
128 func_area_flag9 := 'Y';
129 EXCEPTION
130 WHEN NO_DATA_FOUND THEN
131 func_area_flag9 := 'N';
132 END;
133
134 BEGIN
135 select 'X' into temp
136 from MTL_DEFAULT_CATEGORY_SETS MDCS
137 where MDCS.category_set_id = current_cat_set_id
138 and MDCS.functional_area_id = 10;
139 /*If no data found then set flag to 'N'*/
140 func_area_flag10 := 'Y';
141 EXCEPTION
142 WHEN NO_DATA_FOUND THEN
143 func_area_flag10 := 'N'; --5330858 : Previously setting flag6 = 'N'
144 END;
145
146 BEGIN
147 select 'X' into temp
148 from MTL_DEFAULT_CATEGORY_SETS MDCS
149 where MDCS.category_set_id = current_cat_set_id
150 and MDCS.functional_area_id = 11;
151 /*If no data found then set flag to 'N'*/
152 func_area_flag11 := 'Y';
153 EXCEPTION
154 WHEN NO_DATA_FOUND THEN
155 func_area_flag11 := 'N';
156 END;
157
158 END CHECK_CAT_SET_MANDATORY;
159
160
161
162 PROCEDURE GET_ITEM_DEFINING_FLAGS(
163 current_item_id IN NUMBER,
164 current_org_id IN NUMBER,
165 inv_item_flag OUT NOCOPY VARCHAR2,
166 purch_item_flag OUT NOCOPY VARCHAR2,
167 int_order_flag OUT NOCOPY VARCHAR2,
168 serv_item_flag OUT NOCOPY VARCHAR2,
169 cost_enab_flag OUT NOCOPY VARCHAR2,
170 engg_item_flag OUT NOCOPY VARCHAR2,
171 cust_order_flag OUT NOCOPY VARCHAR2,
172 mrp_plan_code OUT NOCOPY NUMBER,
173 eam_item_type OUT NOCOPY NUMBER, --Bug : 2527058
174 contract_item_type OUT NOCOPY VARCHAR2
175 ) IS
176 BEGIN
177
178 select INVENTORY_ITEM_FLAG, PURCHASING_ITEM_FLAG,
179 INTERNAL_ORDER_FLAG, decode(SERVICE_ITEM_FLAG,'Y',SERVICE_ITEM_FLAG,
180 SERVICEABLE_PRODUCT_FLAG),
181 COSTING_ENABLED_FLAG, ENG_ITEM_FLAG, CUSTOMER_ORDER_FLAG,
182 MRP_PLANNING_CODE,EAM_ITEM_TYPE,CONTRACT_ITEM_TYPE_CODE --Bug: 2527058
183 into inv_item_flag, purch_item_flag,
184 int_order_flag, serv_item_flag,
185 cost_enab_flag, engg_item_flag, cust_order_flag,
186 mrp_plan_code, eam_item_type, contract_item_type --Bug: 2527058
187 from MTL_SYSTEM_ITEMS MSI
188 where MSI.inventory_item_id = current_item_id
189 and MSI.organization_id = current_org_id;
190
191 END GET_ITEM_DEFINING_FLAGS;
192
193 PROCEDURE INSERT_CATSET_CHILD_ORGS(
194 current_inv_item_id IN NUMBER,
195 current_org_id IN NUMBER,
196 current_master_org_id IN NUMBER,
197 current_cat_set_id IN NUMBER,
198 current_cat_id IN NUMBER,
199 cat_set_control_level IN NUMBER,
200 current_created_by IN NUMBER := NULL -- Added Bug-6045867
201 )
202 IS
203
204 l_organizations_rec ORG_LISTS;
205
206 BEGIN
207 IF (cat_set_control_level = 1) THEN
208 BEGIN
209
210 select
211 p.organization_id
212 BULK COLLECT INTO
213 l_organizations_rec
214 from mtl_parameters p
215 where p.master_organization_id = current_master_org_id
216 and p.organization_id <> current_master_org_id
217 and exists
218 (select 'x'
219 from mtl_system_items i
220 where i.inventory_item_id = current_inv_item_id
221 and i.organization_id = p.organization_id)
222 /* Bug: 4932378 and exists
223 (select 'x'
224 from org_organization_definitions ood
225 where ood.organization_id = p.organization_id
226 and ood.inventory_enabled_flag = 'Y')*/;
227
228 FORALL I IN l_organizations_rec.FIRST .. l_organizations_rec.LAST
229 insert into mtl_item_categories
230 (inventory_item_id,
231 category_set_id,
232 category_id,
233 last_update_date,
234 last_updated_by,
235 creation_date,
236 created_by,
237 last_update_login,
238 program_application_id,
239 program_id,
240 program_update_date,
241 request_id,
242 organization_id)
243 VALUES( current_inv_item_id,
244 current_cat_set_id,
245 current_cat_id,
246 sysdate,
247 -- current_created_by, -- bug 6045867 -- Commented for bug-6782351
248 NVL(current_created_by, FND_GLOBAL.USER_ID), -- NVL added for bug-6782351
249 sysdate,
250 -- current_created_by, -- bug 6045867 -- Commented for bug-6782351
251 NVL(current_created_by, FND_GLOBAL.USER_ID), -- NVL added for bug-6782351
252 -1,
253 -1,
254 -1,
255 sysdate,
256 -1,
257 l_organizations_rec(i));
258
259 BEGIN
260 IF l_organizations_rec.COUNT > 0 THEN
261 FOR I IN l_organizations_rec.FIRST .. l_organizations_rec.LAST LOOP
262 INV_ITEM_EVENTS_PVT.Raise_Events(
263 p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT'
264 ,p_dml_type => 'CREATE'
265 ,p_inventory_item_id => current_inv_item_id
266 ,p_organization_id => l_organizations_rec(i)
267 ,p_category_set_id => current_cat_set_id
268 ,p_category_id => current_cat_id);
269 END LOOP;
270 END IF;
271 EXCEPTION
272 WHEN OTHERS THEN NULL;
273 END;
274
275
276 EXCEPTION
277 WHEN NO_DATA_FOUND THEN NULL;
278 END;
279
280 END IF;
281 END INSERT_CATSET_CHILD_ORGS;
282
283 PROCEDURE UPDATE_CATSET_CHILD_ORGS(
284 current_inv_item_id IN NUMBER,
285 current_org_id IN NUMBER,
286 current_master_org_id IN NUMBER,
287 current_cat_set_id IN NUMBER,
288 current_cat_id IN NUMBER,
289 cat_set_control_level IN NUMBER,
290 old_cat_id IN NUMBER,
291 current_last_updated_by IN NUMBER := NULL -- Added Bug-4949084
292 )
293 IS
294
295 l_organizations_rec ORG_LISTS;
296
297 BEGIN
298 IF (cat_set_control_level = 1) THEN
299 BEGIN
300 /* Bug 7626142 hint syntax change */
301 update /*+ INDEX(c MTL_ITEM_CATEGORIES_U1) */
302 mtl_item_categories c
303 set c.category_id = current_cat_id,
304 c.last_update_date = sysdate,
305 c.last_updated_by = NVL(current_last_updated_by, FND_GLOBAL.USER_ID) -- Added Bug-4949084 @ 4886176
306 where c.inventory_item_id = current_inv_item_id
307 and c.category_set_id = current_cat_set_id
308 and c.category_id = old_cat_id
309 and c.organization_id in
310 (select p.organization_id from mtl_parameters p
311 where p.master_organization_id =
312 current_master_org_id
313 and exists (select 'x' from mtl_system_items i
314 where i.inventory_item_id =
315 current_inv_item_id
316 and i.organization_id = p.organization_id)
317 /* Bug: 4932378
318 and exists (select 'x' from org_organization_definitions ood
319 where ood.organization_id = p.organization_id
320 and ood.inventory_enabled_flag = 'Y')*/)
321 RETURNING organization_id
322 BULK COLLECT INTO l_organizations_rec;
323
324 BEGIN
325 IF l_organizations_rec.COUNT > 0 THEN
326 FOR I IN l_organizations_rec.FIRST .. l_organizations_rec.LAST LOOP
327 INV_ITEM_EVENTS_PVT.Raise_Events(
328 p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT'
329 ,p_dml_type => 'UPDATE'
330 ,p_inventory_item_id => current_inv_item_id
331 ,p_organization_id => l_organizations_rec(i)
332 ,p_category_set_id => current_cat_set_id
333 ,p_category_id => current_cat_id);
334 END LOOP;
335 END IF;
336 EXCEPTION
337 WHEN OTHERS THEN NULL;
338 END;
339
340
341 EXCEPTION
342 WHEN NO_DATA_FOUND THEN NULL;
343 END;
344 END IF;
345 END UPDATE_CATSET_CHILD_ORGS;
346
347
348
349 PROCEDURE redefault_material_overheads (
350 current_inv_item_id IN NUMBER,
351 current_org_id IN NUMBER,
352 current_master_org_id IN NUMBER,
353 current_cat_set_id IN NUMBER,
354 current_cat_id IN NUMBER,
355 cat_set_control_level IN NUMBER,
356 current_cst_item_type IN NUMBER,
357 current_last_updated_by IN NUMBER
358 )
359 IS
360 CURSOR other_orgs_cur IS
361 select organization_id
362 from mtl_system_items
363 where
364 inventory_item_id = current_inv_item_id and
365 organization_id <> current_org_id and
366 organization_id in
367 ( select organization_id
368 from mtl_parameters
369 where
370 master_organization_id = current_master_org_id
371 )
372 ;
373 tmp_default_cat_set_id number;
374 tmp_category_id number;
375 proceed_flag char;
376 tmp_cost_method number;
377 tmp_cst_lot_size number;
378 tmp_cst_shrink_rate number;
379 tmp_cst_return number;
380 tmp_cst_error varchar2(100);
381 tmp_organization_id number;
382 BEGIN
383
384 proceed_flag := 'N';
385
386 -- get default category set for Costing (functional area 5)
387 select category_set_id
388 into tmp_default_cat_set_id
389 from mtl_default_category_sets
390 where functional_area_id = 5;
391
392 -- This is called from post-update.
393 -- fires only after update is done. if updated, and if cat set id
394 -- is def cat set, then proceed.
395 if current_cat_set_id = tmp_default_cat_set_id then
396 proceed_flag := 'Y';
397 end if;
398
399 -- if redefaulting is necessary then
400 if proceed_flag = 'Y' then
401
402 INVIDSCS.get_costing_values (
403 current_inv_item_id,
404 current_org_id,
405 tmp_cost_method,
406 tmp_cst_lot_size,
407 tmp_cst_shrink_rate
408 );
409
410 CSTPPCAT.CSTPCCAT (
411 current_inv_item_id,
412 current_org_id,
413 current_last_updated_by,
414 tmp_cost_method,
415 current_cst_item_type,
416 tmp_cst_lot_size,
417 tmp_cst_shrink_rate,
418 tmp_cst_return,
419 tmp_cst_error
420 );
421
422 end if;
423
424 -- if category is controll at master level (item)
425 if (proceed_flag = 'Y') and
426 (cat_set_control_level = 1) then
427 OPEN other_orgs_cur;
428 LOOP
429 FETCH other_orgs_cur into tmp_organization_id;
430 EXIT when other_orgs_cur%NOTFOUND;
431
432 INVIDSCS.get_costing_values (
433 current_inv_item_id,
434 tmp_organization_id,
435 tmp_cost_method,
436 tmp_cst_lot_size,
437 tmp_cst_shrink_rate
438 );
439
440 CSTPPCAT.CSTPCCAT (
441 current_inv_item_id,
442 tmp_organization_id,
443 current_last_updated_by,
444 tmp_cost_method,
445 current_cst_item_type,
446 tmp_cst_lot_size,
447 tmp_cst_shrink_rate,
448 tmp_cst_return,
449 tmp_cst_error
450 );
451
452 END LOOP;
453 CLOSE other_orgs_cur;
454 end if;
455
456 END redefault_material_overheads;
457
458
459 PROCEDURE get_costing_values (
460 tmp_inv_item_id IN NUMBER,
461 tmp_organization_id IN NUMBER,
462 tmp_cost_method OUT NOCOPY NUMBER,
463 tmp_cst_lot_size OUT NOCOPY NUMBER,
464 tmp_cst_shrink_rate OUT NOCOPY NUMBER
465 )
466 IS
467
468 BEGIN
469
470 --dbms_output.put_line ( 'Inside get_costing_values ' );
471
472 select primary_cost_method
473 into tmp_cost_method
474 from mtl_parameters
475 where organization_id = tmp_organization_id;
476
477 begin
478 select lot_size, shrinkage_rate
479 into tmp_cst_lot_size, tmp_cst_shrink_rate
480 from cst_item_costs
481 where
482 inventory_item_id = tmp_inv_item_id
483 and organization_id = tmp_organization_id
484 and cost_type_id = tmp_cost_method; /* Bug 7312887 : Changed from cost_type_id = 1*/
485
486 exception
487 when NO_DATA_FOUND then
488 tmp_cst_lot_size := null;
489 tmp_cst_shrink_rate := null;
490 end;
491
492
493 EXCEPTION
494 when NO_DATA_FOUND then
495 tmp_cost_method := null;
496 tmp_cst_lot_size := null;
497 tmp_cst_shrink_rate := null;
498
499 END get_costing_values;
500
501
502
503 /* The following procedure called ONLY when category_set control level is "Item"
504 */
505 /* Prasad Peddamatham - 12/8/2000
506 Added current_cat_id parameter to allow the deletion of a Item Category
507 Assignment based upon Multiple Item Category Assignment flag
508 */
509 PROCEDURE DELETE_CATSET_CHILD_ORGS(
510 current_inv_item_id IN NUMBER,
511 current_master_org_id IN NUMBER,
512 current_cat_set_id IN NUMBER,
513 current_cat_id IN NUMBER
514 )
515 IS
516
517 l_organizations_rec ORG_LISTS;
518
519 BEGIN
520 /* Prasad Peddamatham - 12/8/2000
521 added the clause : c.category_id = current_cat_id
522 to be able include the category_id while deletion of "Item Category"
523 Assignment.
524 In 11.5.3, the Multiple Item Category Assignment is allowed.
525 That is Assigning an Item to multiple categories within a CategorySet.
526 This caused the Unique Index on MTL_CATEGORIES to be the following
527 4 columns: INVENTORY_ITEM_ID, ORG_ID, CATEGORY_SET_ID, CATEGORY_ID.
528 So, during Deleting need to use all these 4 column values to delete
529 a single assignment.
530 */
531
532 delete from mtl_item_categories c
533 where c.inventory_item_id = current_inv_item_id
534 and c.category_set_id = current_cat_set_id
535 and c.category_id = current_cat_id
536 and c.organization_id in
537 (select p.organization_id
538 from mtl_parameters p
539 where p.master_organization_id = current_master_org_id)
540 RETURNING organization_id
541 BULK COLLECT INTO
542 l_organizations_rec;
543
544
545 BEGIN
546 IF l_organizations_rec.COUNT > 0 THEN
547 FOR I IN l_organizations_rec.FIRST .. l_organizations_rec.LAST LOOP
548 IF current_master_org_id <> l_organizations_rec(i) THEN
549 INV_ITEM_EVENTS_PVT.Raise_Events(
550 p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT'
551 ,p_dml_type => 'DELETE'
552 ,p_inventory_item_id => current_inv_item_id
553 ,p_organization_id => l_organizations_rec(i)
554 ,p_category_set_id => current_cat_set_id
555 ,p_category_id => current_cat_id);
556 END IF;
557 END LOOP;
558 END IF;
559 EXCEPTION
560 WHEN OTHERS THEN NULL;
561 END;
562
563
564 EXCEPTION
565 WHEN NO_DATA_FOUND THEN NULL;
566 END DELETE_CATSET_CHILD_ORGS;
567
568
569 END INVIDSCS;
[Home] [Help]