[Home] [Help]
PACKAGE BODY: APPS.ENI_CONFIG_ITEMS_PKG
Source
1 Package Body ENI_CONFIG_ITEMS_PKG AS
2 /* $Header: ENICTOIB.pls 115.3 2004/05/26 09:10:43 pthambu noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ENI_CONFIG_ITEMS_PKG';
5
6 PROCEDURE Create_config_items( p_api_version NUMBER,
7 p_init_msg_list VARCHAR2 := 'F',
8 p_star_record CTO_ENI_WRAPPER.star_rec_type,
9 x_return_status OUT NOCOPY VARCHAR2,
10 x_msg_count OUT NOCOPY NUMBER,
11 x_msg_data OUT NOCOPY VARCHAR2)
12
13 IS
14
15 l_inv_category_set number := null;
16 l_vbh_category_set number := null;
17 l_inventory_item_id NUMBER;
18
19 cursor c_catg_set is
20 Select category_set_id, functional_area_id
21 from mtl_default_category_sets
22 where functional_area_id in(1,11);
23
24 BEGIN
25
26 l_inventory_item_id := p_star_record.inventory_item_id;
27
28 FOR i in c_catg_set LOOP
29
30 IF i.functional_area_id = 1 THEN
31 l_inv_category_set := i.category_set_id;
32 END IF;
33
34 IF i.functional_area_id = 11 THEN
35 l_vbh_category_set := i.category_set_id;
36 END IF;
37
38 END LOOP;
39
40 INSERT INTO eni_oltp_item_star(
41 id,
42 value,
43 inventory_item_id,
44 organization_id,
45 inv_category_id,
46 inv_category_set_id,
47 inv_concat_seg,
48 vbh_category_id,
49 vbh_category_set_id,
50 vbh_concat_seg,
51 master_id,
52 creation_date,
53 last_update_date,
54 item_catalog_group_id,
55 primary_uom_code
56 )
57 SELECT
58 mti.inventory_item_id || '-' || mti.organization_id id,
59 mti.concatenated_segments || ' (' || mtp.organization_code || ')' value,
60 mti.inventory_item_id inventory_item_id,
61 mti.organization_id organization_id,
62 nvl(mic.category_id,-1) inv_category_id,
63 nvl(mic.category_Set_id,l_inv_category_set) inv_category_Set_id,
64 nvl(kfv.concatenated_segments,'Unassigned') inv_concat_seg,
65 nvl(mic1.category_id, -1) vbh_category_id,
66 nvl(mic1.category_set_id, l_vbh_category_set) vbh_category_set_id,
67 nvl(kfv1.concatenated_segments, 'Unassigned') vbh_concat_seg,
68 decode(mti.organization_id,mtp.master_organization_id,null,mti.inventory_item_id || '-' || mtp.master_organization_id)
69 master_id,
70 mti.creation_date creation_date,
71 mti.last_update_date last_update_date,
72 nvl(mti.item_catalog_group_id,-1) item_catalog_group_id,
73 mti.primary_uom_code
74 FROM mtl_system_items_b_kfv mti,
75 mtl_parameters mtp,
76 mtl_item_categories mic ,
77 mtl_item_categories mic1 ,
78 mtl_categories_b_kfv kfv ,
79 mtl_categories_b_kfv kfv1
80 WHERE mtp.organization_id=mti.organization_id
81 AND mti.inventory_item_id = l_inventory_item_id
82 AND mic.organization_id(+) = mti.organization_id
83 AND mic.inventory_item_id(+) = mti.inventory_item_id
84 AND mic.category_id = kfv.category_id (+)
85 and mic.category_set_id(+) = l_inv_category_set
86 AND mic1.organization_id(+) = mti.organization_id
87 AND mic1.inventory_item_id(+) = mti.inventory_item_id
88 AND mic1.category_id = kfv1.category_id (+)
89 AND mic1.category_set_id(+) = l_vbh_category_set
90 AND NOT EXISTS(SELECT null FROM eni_oltp_item_star
91 WHERE inventory_item_id = l_inventory_item_id
92 AND organization_id = mti.organization_id);
93
94 X_RETURN_STATUS := 'S';
95
96 EXCEPTION
97 WHEN OTHERS THEN
98 X_RETURN_STATUS := 'U';
99 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
100 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name, 'CREATE_CONFIG_ITEMS', SQLERRM);
101 END IF;
102 FND_MSG_PUB.COUNT_AND_GET(P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
103
104 END Create_config_items;
105
106
107 End ENI_CONFIG_ITEMS_PKG;