[Home] [Help]
PACKAGE BODY: APPS.INV_ITEM_CATALOG_ELE_PVT
Source
1 PACKAGE BODY INV_ITEM_CATALOG_ELE_PVT AS
2 /* $Header: INVCEAPB.pls 120.2 2006/09/15 10:09:09 supsrini ship $ */
3 ---------------------- Package variables and constants -----------------------
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_ITEM_CATALOG_ELE_PVT';
6
7 G_INV_APP_ID CONSTANT NUMBER := 401;
8 G_ELEM_VAL_LENGTH CONSTANT NUMBER := 30;
9 G_INV_APP_SHORT_NAME CONSTANT VARCHAR2(3) := 'INV';
10
11 ------------------------- Create_Category_Assignment -------------------------
12
13 PROCEDURE Catalog_Grp_Ele_Val_Assignment
14 (
15 p_api_version IN NUMBER
16 , p_init_msg_list IN VARCHAR2
17 , p_commit IN VARCHAR2
18 , p_validation_level IN NUMBER
19 , p_inventory_item_id IN NUMBER
20 , p_item_number IN VARCHAR2
21 , p_element_name IN VARCHAR2
22 , p_element_value IN VARCHAR2
23 , p_default_element_flag IN VARCHAR2
24 , x_return_status OUT NOCOPY VARCHAR2
25 , x_msg_count OUT NOCOPY NUMBER
26 , x_msg_data OUT NOCOPY VARCHAR2
27 )
28 IS
29 l_api_name CONSTANT VARCHAR2(30) := 'Catalog_Grp_Ele_Val_Assignment';
30 l_api_version CONSTANT NUMBER := 1.0;
31 Mctx INV_ITEM_MSG.Msg_Ctx_type;
32
33 l_element_name VARCHAR2(200);
34 l_exists VARCHAR2(1);
35 l_inventory_item_id NUMBER;
36 flex_id NUMBER;
37 l_grp_id NUMBER;
38
39 ret_code NUMBER := 0;
40 l_err_text VARCHAR2(2000);
41
42 l_commit VARCHAR2(1);
43 l_return_status VARCHAR2(1); -- := fnd_api.g_MISS_CHAR
44 l_msg_count NUMBER;
45 l_msg_data VARCHAR2(2000);
46 l_msg_name VARCHAR2(2000);
47 l_RETCODE NUMBER; -- G_SUCCESS, G_WARNING, G_ERROR
48 l_column_name VARCHAR2(30);
49 l_token VARCHAR2(30);
50 l_token_value VARCHAR2(30);
51 l_org_id NUMBER;
52
53 l_item_number VARCHAR2(40); --5522789
54
55 CURSOR org_item_exists_csr
56 ( p_inventory_item_id NUMBER
57 ) IS
58 SELECT organization_id
59 FROM mtl_system_items_b
60 WHERE inventory_item_id = p_inventory_item_id;
61
62
63 CURSOR catalog_group_element_csr (p_element_name VARCHAR2,p_inventory_item_id NUMBER)
64 IS
65 SELECT element_name
66 FROM mtl_descr_element_values
67 WHERE inventory_item_id = p_inventory_item_id
68 AND element_name = p_element_name;
69
70 CURSOR catalog_group_csr (p_inventory_item_id NUMBER)
71 IS
72 SELECT item_Catalog_group_id
73 FROM mtl_system_items_b
74 WHERE inventory_item_id = p_inventory_item_id
75 AND item_Catalog_group_id IS NOT NULL;
76
77
78 BEGIN
79
80 -- Set savepoint
81 SAVEPOINT Catalog_Grp_Ele_Val_Assign_PVT;
82
83 -- INVPUTLI.info('Add_Message: p_Msg_Name=' || p_Msg_Name);
84
85 --dbms_output.put_line('Enter INV_ITEM_CATALOG_ELE_PVT.Catalog_Grp_Ele_Val_Assignment');
86
87 -- Check for call compatibility
88 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
89 l_api_name, G_PKG_NAME)
90 THEN
91 RAISE FND_API.g_EXC_UNEXPECTED_ERROR;
92 END IF;
93
94 --dbms_output.put_line('Before Initialize message list.');
95
96 -- Initialize message list
97 IF (FND_API.To_Boolean (p_init_msg_list)) THEN
98 INV_ITEM_MSG.Initialize;
99 END IF;
100
101 -- Define message context
102 Mctx.Package_Name := G_PKG_NAME;
103 Mctx.Procedure_Name := l_api_name;
104
105 -- Initialize API return status to success
106 x_return_status := FND_API.g_RET_STS_SUCCESS;
107
108 -- Check for NULL parameter values
109
110 --dbms_output.put_line('Before IS NULL ; x_return_status = ' || x_return_status);
111
112 IF (( p_inventory_item_id IS NULL ) AND ( p_item_number IS NULL )) OR
113 ( p_element_name IS NULL )
114 THEN
115 INV_ITEM_MSG.Add_Error('INV_INVALID_ARG_NULL_VALUE');
116 RAISE FND_API.g_EXC_ERROR;
117 END IF;
118
119 INV_ITEM_MSG.Debug(Mctx, 'Validate item');
120
121 -- Validate item
122 INV_ITEM_MSG.Debug(Mctx, 'assign missing inventory_item_id');
123 l_inventory_item_id := p_inventory_item_id;
124
125 /** checking the validation level, to convert the item_number to item_id ***/
126 IF (p_validation_level IN (INV_ITEM_CATALOG_ELEM_PUB.g_VALIDATE_ALL,INV_ITEM_CATALOG_ELEM_PUB.g_VALIDATE_LEVEL_FULL)) THEN
127 IF ( p_item_number IS NOT NULL ) THEN
128 ret_code := INVPUOPI.mtl_pr_parse_item_name (
129 p_item_number,
130 flex_id,
131 l_err_text );
132
133 IF ( ret_code = 0 ) THEN
134 l_inventory_item_id := flex_id;
135 IF ( p_inventory_item_id <> -999 AND p_inventory_item_id <> l_inventory_item_id) THEN
136 l_return_status := fnd_api.g_RET_STS_ERROR;
137 l_msg_name := 'INV_CEOI_ITEM_NUM_ID_MISMATCH';
138 l_token := 'VALUE1';
139 l_token_value := p_item_number;
140 l_column_name := 'ITEM_NUMBER';
141 l_token := 'VALUE2';
142 l_token_value := p_inventory_item_id;
143 l_column_name := 'INVENTORY_ITEM_ID';
144 END IF;
145 ELSE
146 l_return_status := fnd_api.g_RET_STS_ERROR;
147 l_msg_name := 'INV_ICOI_INVALID_ITEM_NUMBER';
148 l_token := 'VALUE';
149 l_token_value := p_item_number;
150 l_column_name := 'ITEM_NUMBER';
151 END IF;
152
153 END IF;
154 IF (l_return_status = fnd_api.g_RET_STS_ERROR) THEN
155 INV_ITEM_MSG.Add_Message
156 ( p_Msg_Name => l_msg_name
157 , p_token1 => l_token
158 , p_value1 => l_token_value
159 , p_column_name => l_column_name
160 );
161 RAISE FND_API.g_EXC_ERROR;
162 END IF;
163
164 END IF;/*validation_level check***/
165
166 --dbms_output.put_line('Before OPEN org_item_exists_csr ; x_return_status = ' || x_return_status);
167
168 OPEN org_item_exists_csr (l_inventory_item_id);
169 FETCH org_item_exists_csr INTO l_org_id;
170 IF (org_item_exists_csr%NOTFOUND) THEN
171 CLOSE org_item_exists_csr;
172 INV_ITEM_MSG.Add_Error('INV_ORGITEM_ID_NOT_FOUND');
173 RAISE FND_API.g_EXC_ERROR;
174 END IF;
175 CLOSE org_item_exists_csr;
176
177 --dbms_output.put_line('After OPEN org_item_exists_csr ; x_return_status = ' || x_return_status);
178
179 INV_ITEM_MSG.Debug(Mctx, 'Validate catalog Group name');
180
181 -- Validate catalog group name
182
183 OPEN catalog_group_csr (l_inventory_item_id);
184 FETCH catalog_group_csr INTO l_grp_id;
185
186 IF (catalog_group_csr%NOTFOUND) THEN
187 CLOSE catalog_group_csr;
188 INV_ITEM_MSG.Add_Error('INV_CEOI_CAT_GRP_NOT_FOUND');
189 RAISE FND_API.g_EXC_ERROR;
190 END IF;
191 CLOSE catalog_group_csr;
192
193 INV_ITEM_MSG.Debug(Mctx, 'Validate catalog Element name');
194 -- Validate catalog element name
195
196 OPEN catalog_group_element_csr (p_element_name,l_inventory_item_id);
197 FETCH catalog_group_element_csr INTO l_element_name;
198
199 IF (catalog_group_element_csr%NOTFOUND) THEN
200 CLOSE catalog_group_element_csr;
201 INV_ITEM_MSG.Add_Error
202 ( p_Msg_Name =>'INV_CEOI_CAT_GRP_ELE_NOT_FOUND'
203 , p_token => 'ELEMENT_NAME'
204 , p_value => p_element_name );
205 RAISE FND_API.g_EXC_ERROR;
206 END IF;
207 CLOSE catalog_group_element_csr;
208
209 INV_ITEM_MSG.Debug(Mctx, 'Validate catalog Element value');
210 -- Validate catalog element value length
211
212 IF (length(p_element_value) > G_ELEM_VAL_LENGTH) THEN
213 INV_ITEM_MSG.Add_Error
214 ( p_Msg_Name =>'INV_CEOI_CAT_GRP_ELE_VAL_LEN_M'
215 , p_token => 'ELEMENT_NAME'
216 , p_value => p_element_name );
217 RAISE FND_API.g_EXC_ERROR;
218 END IF;
219
220 -- Bug: 4062893 Check whether user has EDIT privilege on item for EGO.
221 IF INV_EGO_REVISION_VALIDATE.Get_Process_Control ='EGO_ITEM_BULKLOAD'
222 AND INV_EGO_REVISION_VALIDATE.check_data_security(
223 p_function => 'EGO_EDIT_ITEM'
224 ,p_object_name => 'EGO_ITEM'
225 ,p_instance_pk1_value => l_inventory_item_id
226 ,p_instance_pk2_value => l_org_id
227 ,P_User_Id => FND_GLOBAL.user_id) <> 'T'
228 THEN
229 --Bug: 5522789 Tokenise the message to display item number.
230 IF p_item_number IS NULL THEN
231 Select concatenated_segments
232 into l_item_number
233 From mtl_system_items_b_kfv
234 where INVENTORY_ITEM_ID = l_inventory_item_id
235 AND organization_id = l_org_id;
236 ELSE
237 l_item_number := p_item_number;
238 END IF;
239
240 INV_ITEM_MSG.Add_Error
241 ( p_Msg_Name =>'INV_IOI_ITEM_UPDATE_PRIV'
242 , p_token => 'VALUE'
243 , p_value => l_item_number );
244 RAISE FND_API.g_EXC_ERROR;
245 END IF;
246
247 -- Update the production table
248
249 INV_ITEM_MSG.Debug(Mctx, 'begin UPDATE mtl_descr_element_values');
250
251 UPDATE mtl_descr_element_values
252 SET
253 element_value = p_element_value
254 , default_element_flag = decode(p_default_element_flag,'Y','Y','N')
255 , last_update_date = SYSDATE
256 , last_updated_by = FND_GLOBAL.user_id
257 , last_update_login = FND_GLOBAL.login_id
258 , request_id = FND_GLOBAL.conc_request_id
259 , program_application_id = FND_GLOBAL.prog_appl_id
260 , program_id = FND_GLOBAL.conc_program_id
261 , program_update_date = SYSDATE
262 WHERE
263 inventory_item_id = l_inventory_item_id
264 AND element_name = p_element_name;
265
266
267 INV_ITEM_MSG.Debug(Mctx, 'end UPDATE mtl_descr_element_values');
268
269 -- Standard check of p_commit
270 IF (FND_API.To_Boolean (p_commit)) THEN
271
272 INV_ITEM_MSG.Debug(Mctx, 'before COMMIT WORK');
273 COMMIT WORK;
274 END IF;
275
276 INV_ITEM_MSG.Count_And_Get
277 ( p_count => x_msg_count
278 , p_data => x_msg_data
279 );
280 -- Write all accumulated messages
281 INV_ITEM_MSG.Write_List (p_delete => TRUE);
282
283 EXCEPTION
284
285 WHEN FND_API.g_EXC_ERROR THEN
286 ROLLBACK TO Catalog_Grp_Ele_Val_Assign_PVT;
287
288 x_return_status := FND_API.g_RET_STS_ERROR;
289 INV_ITEM_MSG.Count_And_Get
290 ( p_count => x_msg_count
291 , p_data => x_msg_data
292 );
293
294 WHEN FND_API.g_EXC_UNEXPECTED_ERROR THEN
295 ROLLBACK TO Catalog_Grp_Ele_Val_Assign_PVT;
296
297 x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
298 INV_ITEM_MSG.Count_And_Get
299 ( p_count => x_msg_count
300 , p_data => x_msg_data
301 );
302
303 WHEN others THEN
304 ROLLBACK TO Catalog_Grp_Ele_Val_Assign_PVT;
305
306 x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
307 INV_ITEM_MSG.Add_Unexpected_Error (Mctx, SQLERRM);
308
309 INV_ITEM_MSG.Count_And_Get
310 ( p_count => x_msg_count
311 , p_data => x_msg_data
312 );
313
314 END Catalog_Grp_Ele_Val_Assignment;
315
316 END INV_ITEM_CATALOG_ELE_PVT;