[Home] [Help]
PACKAGE BODY: APPS.AMS_ITEM_CATEGORY_PVT
Source
1 PACKAGE BODY AMS_ITEM_CATEGORY_PVT AS
2 /* $Header: amsvicab.pls 115.8 2002/11/14 00:55:42 abhola ship $ */
3
4 --------------------------------------------------------------
5 -- PROCEDURE
6 -- Create_Category_Assignment
7 --
8 --------------------------------------------------------------
9
10 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
11 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
12 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
13
14 PROCEDURE Create_Category_Assignment
15 (
16 p_api_version IN NUMBER,
17 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
18 p_commit IN VARCHAR2 := FND_API.G_FALSE,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_errorcode OUT NOCOPY NUMBER,
21 x_msg_count OUT NOCOPY NUMBER,
22 x_msg_data OUT NOCOPY VARCHAR2,
23 p_category_id IN NUMBER,
24 p_category_set_id IN NUMBER,
25 p_inventory_item_id IN NUMBER,
26 p_organization_id IN NUMBER
27 )
28 IS
29
30 l_api_version CONSTANT NUMBER := 1.0;
31 l_category_id NUMBER := p_category_id ;
32 l_category_set_id NUMBER := p_category_set_id ;
33 l_inventory_item_id NUMBER := p_inventory_item_id ;
34 l_organization_id NUMBER := p_organization_id ;
35
36
37 Cursor Get_owner_id IS
38 SELECT item_owner_id
39 FROM ams_item_attributes
40 WHERE inventory_item_id = p_inventory_item_id
41 AND organization_id = p_organization_id;
42
43 l_return_status_cue VARCHAR2(1);
44 l_owner_id NUMBER;
45
46
47 BEGIN
48
49 IF fnd_api.to_boolean (p_init_msg_list) THEN
50 fnd_msg_pub.initialize;
51 END IF;
52
53 INV_ITEM_CATEGORY_PUB.Create_Category_Assignment
54 (p_api_version => l_api_version,
55 p_init_msg_list => p_init_msg_list,
56 p_commit => p_commit,
57 x_return_status => x_return_status,
58 x_errorcode => x_errorcode ,
59 x_msg_count => x_msg_count,
60 x_msg_data => x_msg_data,
61 p_category_id => l_category_id,
62 p_category_set_id => l_category_set_id,
63 p_inventory_item_id => l_inventory_item_id,
64 p_organization_id => l_organization_id
65 );
66 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
67 RAISE FND_API.G_EXC_ERROR;
68
69 /***** commented by ABHOLA
70 ELSE
71 OPEN get_owner_id;
72 FETCH get_owner_id INTO l_owner_id;
73 CLOSE get_owner_id;
74
75 AMS_ObjectAttribute_PVT.modify_object_attribute(
76 p_api_version => l_api_version,
77 p_init_msg_list => FND_API.g_false,
78 p_commit => FND_API.g_false,
79 p_validation_level => FND_API.g_valid_level_full,
80 x_return_status => l_return_status_cue,
81 x_msg_count => x_msg_count,
82 x_msg_data => x_msg_data,
83 p_object_type => 'PROD',
84 p_object_id => l_owner_id ,
85 p_attr => 'PCAT',
86 p_attr_defined_flag => 'Y'
87 );
88 ***********************************************/
89 END IF;
90
91 IF FND_API.to_boolean(p_commit) THEN
92 COMMIT;
93 END IF;
94
95 FND_MSG_PUB.Count_AND_Get
96 ( p_count => x_msg_count,
97 p_data => x_msg_data,
98 p_encoded => FND_API.G_FALSE
99 );
100
101 EXCEPTION
102 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
104 FND_MSG_PUB.count_And_Get
105 ( p_count => x_msg_count,
106 p_data => x_msg_data,
107 p_encoded => FND_API.G_FALSE
108 );
109 WHEN FND_API.G_EXC_ERROR THEN
110 x_return_status := FND_API.G_RET_STS_ERROR;
111 FND_MSG_PUB.count_And_Get
112 ( p_count => x_msg_count,
113 p_data => x_msg_data,
114 p_encoded => FND_API.G_FALSE
115 );
116
117 END Create_Category_Assignment;
118
119
120 PROCEDURE Delete_Category_Assignment
121 (
122 p_api_version IN NUMBER,
123 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
124 p_commit IN VARCHAR2 := FND_API.G_FALSE,
125 x_return_status OUT NOCOPY VARCHAR2,
126 x_errorcode OUT NOCOPY NUMBER,
127 x_msg_count OUT NOCOPY NUMBER,
128 x_msg_data OUT NOCOPY VARCHAR2,
129 p_category_id IN NUMBER,
130 p_category_set_id IN NUMBER,
131 p_inventory_item_id IN NUMBER,
132 p_organization_id IN NUMBER
133 )
134 IS
135
136 l_api_version CONSTANT NUMBER := 1.0;
137 l_category_id NUMBER := p_category_id ;
138 l_category_set_id NUMBER := p_category_set_id ;
139 l_inventory_item_id NUMBER := p_inventory_item_id ;
140 l_organization_id NUMBER := p_organization_id ;
141
142 Cursor check_item is
143 SELECT 1
144 FROM mtl_item_categories
145 WHERE organization_id = p_organization_id
146 AND inventory_item_id = p_inventory_item_id;
147
148 Cursor Get_owner_id IS
149 SELECT item_owner_id
150 FROM ams_item_attributes
151 WHERE inventory_item_id = p_inventory_item_id
152 AND organization_id = p_organization_id;
153
154 l_return_status_cue VARCHAR2(1);
155 l_owner_id NUMBER;
156 l_dummy number :=0;
157 l_return_status VARCHAR2(1) ;
158 l_item_owner_id NUMBER;
159
160
161 BEGIN
162
163 IF fnd_api.to_boolean (p_init_msg_list) THEN
164 fnd_msg_pub.initialize;
165 END IF;
166
167 INV_ITEM_CATEGORY_PUB.Delete_Category_Assignment
168 (p_api_version => l_api_version,
169 p_init_msg_list => p_init_msg_list,
170 p_commit => p_commit,
171 x_return_status => x_return_status,
172 x_errorcode => x_errorcode ,
173 x_msg_count => x_msg_count,
174 x_msg_data => x_msg_data,
175 p_category_id => l_category_id,
176 p_category_set_id => l_category_set_id,
177 p_inventory_item_id => l_inventory_item_id,
178 p_organization_id => l_organization_id
179 );
180
181 -- ************************************************************
182 -- Call for cue card
183 -- ************************************************************
184
185 /*************************
186 --
187 -- commented call to object attr
188 --
189 OPEN check_item;
190 FETCH check_item INTO l_dummy;
191 CLOSE check_item;
192
193 OPEN get_owner_id;
194 FETCH get_owner_id INTO l_item_owner_id;
195 CLOSE get_owner_id;
196
197 IF l_dummy =1 THEN
198
199 AMS_ObjectAttribute_PVT.modify_object_attribute(
200 p_api_version => l_api_version,
201 p_init_msg_list => FND_API.g_false,
202 p_commit => FND_API.g_false,
203 p_validation_level => FND_API.g_valid_level_full,
204
205 x_return_status => l_return_status,
206 x_msg_count => x_msg_count,
207 x_msg_data => x_msg_data,
208
209 p_object_type => 'PROD',
210 p_object_id => l_item_owner_id ,
211 p_attr => 'PCAT',
212 p_attr_defined_flag => 'Y'
213 );
214 ELSE
215
216 AMS_ObjectAttribute_PVT.modify_object_attribute(
217 p_api_version => l_api_version,
218 p_init_msg_list => FND_API.g_false,
219 p_commit => FND_API.g_false,
220 p_validation_level => FND_API.g_valid_level_full,
221
222 x_return_status => l_return_status,
223 x_msg_count => x_msg_count,
224 x_msg_data => x_msg_data,
225
226 p_object_type => 'PROD',
227 p_object_id => l_item_owner_id ,
228 p_attr => 'PCAT',
229 p_attr_defined_flag => 'N'
230 );
231
232 END IF;
233 *********************************************/
234
235 IF FND_API.to_boolean(p_commit) THEN
236 COMMIT;
237 END IF;
238
239 FND_MSG_PUB.count_And_Get
240 ( p_count => x_msg_count,
241 p_data => x_msg_data,
242 p_encoded => FND_API.G_FALSE);
243
244
245 EXCEPTION
246 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
248 FND_MSG_PUB.count_And_Get
249 ( p_count => x_msg_count,
250 p_data => x_msg_data,
251 p_encoded => FND_API.G_FALSE );
252 WHEN FND_API.G_EXC_ERROR THEN
253 x_return_status := FND_API.G_RET_STS_ERROR;
254 FND_MSG_PUB.count_And_Get
255 ( p_count => x_msg_count,
256 p_data => x_msg_data,
257 p_encoded => FND_API.G_FALSE);
258 END Delete_Category_Assignment;
259
260 END;