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