[Home] [Help]
PACKAGE BODY: APPS.CSP_PC_FORM_PICKLINES
Source
1 Package Body CSP_PC_FORM_PICKLINES AS
2 /* $Header: cspgtplb.pls 115.11 2002/11/26 06:45:56 hhaugeru ship $ */
3 -- Start of Commetns --
4 -- Purpose: wrapper for csp picklist lines private procedure which calls picklist lines table handlers
5 --
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- --------- ------ ------------------------------------------
9 -- klou 04/03/00 Modify procedure to fix bug 1238607.
10 -- klou 02/08/00 Add standard messages.
11 -- klou 01/12/00 Replace change AS_UTILITY call with JTF_PLSQL_API.
12 -- klou 01/03/00 Modify the validations so that when a not null picklist_line_id is passed for
13 -- insert operation, it checks whether an identical picklist_line_id exists. It yes,
14 -- raise an exception because picklist_line_id should be unique.
15 -- klou 12/23/99 Add validations to creation_date and last_update_date
16 -- klou 12/22/99 Add validations.
17 -- Notes: The following columns should not be null when creating a new record.
18 -- PICKLIST_LINE_ID NOT NULL
19 -- CREATED_BY NOT NULL
20 -- CREATION_DATE NOT NULL
21 -- LAST_UPDATED_BY NOT NULL
22 -- LAST_UPDATE_DATE NOT NULL
23 -- LAST_UPDATE_LOGIN
24 -- PICKLIST_LINE_NUMBER NOT NULL
25 -- PICKLIST_HEADER_ID NOT NULL
26 -- LINE_ID NOT NULL
27 -- INVENTORY_ITEM_ID NOT NULL
28 -- UOM_CODE NOT NULL
29 -- QUANTITY_PICKED NOT NULL
30 -- TRANSACTION_TEMP_ID NOT NULL
31 -- End of Comments
32
33 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_PC_FORM_PICKLINES';
34 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspgtplb.pls';
35 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
36 G_LOGIN_ID NUMBER := FND_GLOBAL.LOGIN_ID;
37
38 PROCEDURE Validate_And_Write (
42 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
39 P_Api_Version_Number IN NUMBER,
40 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
41 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
43 p_action_code IN NUMBER, /* 0 = insert, 1 = update, 2 = delete */
44 px_PICKLIST_LINE_ID IN OUT NOCOPY NUMBER,
45 p_CREATED_BY IN NUMBER := FND_API.G_MISS_NUM,
46 p_CREATION_DATE IN DATE := FND_API.G_MISS_DATE,
47 p_LAST_UPDATED_BY IN NUMBER := FND_API.G_MISS_NUM,
48 p_LAST_UPDATE_DATE IN DATE := FND_API.G_MISS_DATE,
49 p_LAST_UPDATE_LOGIN IN NUMBER := FND_API.G_MISS_NUM,
50 p_PICKLIST_LINE_NUMBER IN NUMBER := FND_API.G_MISS_NUM,
51 p_picklist_header_id IN NUMBER := FND_API.G_MISS_NUM,
52 p_LINE_ID IN NUMBER := FND_API.G_MISS_NUM,
53 p_INVENTORY_ITEM_ID IN NUMBER := FND_API.G_MISS_NUM,
54 p_UOM_CODE IN VARCHAR2 := FND_API.G_MISS_CHAR,
55 p_REVISION IN VARCHAR2 := FND_API.G_MISS_CHAR,
56 p_QUANTITY_PICKED IN NUMBER := FND_API.G_MISS_NUM,
57 p_TRANSACTION_TEMP_ID IN NUMBER := FND_API.G_MISS_NUM,
58 p_ATTRIBUTE_CATEGORY IN VARCHAR2 := FND_API.G_MISS_CHAR,
59 p_ATTRIBUTE1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
60 p_ATTRIBUTE2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
61 p_ATTRIBUTE3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
62 p_ATTRIBUTE4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
63 p_ATTRIBUTE5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
64 p_ATTRIBUTE6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
65 p_ATTRIBUTE7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
66 p_ATTRIBUTE8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
67 p_ATTRIBUTE9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
68 p_ATTRIBUTE10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
69 p_ATTRIBUTE11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
70 p_ATTRIBUTE12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
71 p_ATTRIBUTE13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
72 p_ATTRIBUTE14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
73 p_ATTRIBUTE15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
74 x_return_status OUT NOCOPY VARCHAR2,
75 x_msg_count OUT NOCOPY NUMBER,
76 x_msg_data OUT NOCOPY VARCHAR2)
77 IS
78 l_picklist_line_rec CSP_PICKLIST_LINES_PVT.picklist_line_Rec_Type;
79 l_api_version_number CONSTANT NUMBER := 1.0;
80 l_api_name CONSTANT VARCHAR2(20) := 'Validate_And_Write';
81 l_msg_data VARCHAR2(300);
82 l_check_existence NUMBER := 0;
83 l_check_var VARCHAR2(20);
84 l_return_status VARCHAR2(1);
85 l_msg_count NUMBER := 0;
86 l_picklist_header_id NUMBER := p_picklist_header_id;
87 l_commit VARCHAR2(1) := FND_API.G_FALSE;
88 l_organization_id NUMBER;
89 l_picklist_line_id NUMBER := 0;
90 l_creation_date DATE := p_creation_date;
91 l_last_update_date DATE := p_last_update_date;
92 -- for inserting data, the validation_level should be none
93 -- because we do not want to call the core apps standard validations.
94 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
95 EXCP_USER_DEFINED EXCEPTION;
96 l_created_by NUMBER := p_created_by;
97 l_last_update_login NUMBER := p_last_update_login;
98 l_last_updated_by NUMBER := p_last_updated_by;
99 Cursor l_Get_Creation_Date_Csr Is
100 Select creation_date
101 From csp_picklist_lines
102 Where picklist_line_id = px_PICKLIST_LINE_ID;
103
104 BEGIN
105 SAVEPOINT Validate_And_Write_PUB;
106 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
107 -- initialize message list
108 FND_MSG_PUB.initialize;
109 END IF;
110
111 -- Standard call to check for call compatibility.
112 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
113 p_api_version_number,
114 l_api_name,
115 G_PKG_NAME)
116 THEN
117 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
118 END IF;
119
120 --validating p_action_code
121 IF p_action_code NOT IN (0, 1, 2) OR p_action_code IS NULL THEN
122 fnd_message.set_name ('INV', 'INV-INVALID ACTION');
123 fnd_message.set_token ('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
124 fnd_msg_pub.add;
125 RAISE EXCP_USER_DEFINED;
126 END IF;
127
128 IF p_action_code = 0 THEN
129 -- validate the all NOT NULL columns.
130 IF px_picklist_line_id IS NOT NULL THEN
131 -- valdiate whether an identical picklist_line_id already exists.
132 BEGIN
133 SELECt picklist_line_id into l_check_existence
134 FROM CSP_PICKLIST_LINES
135 WHERE picklist_line_id = px_picklist_line_id;
136
140 EXCEPTION
137 fnd_message.set_name ('CSP', 'CSP_DUPLICATE_RECORD');
138 fnd_msg_pub.add;
139 RAISE EXCP_USER_DEFINED;
141 WHEN NO_DATA_FOUND THEN
142 NULL;
143 END;
144 End IF;
145
146 --validating quantity_picked
147 IF nvl(p_quantity_picked, fnd_api.g_miss_num) = fnd_api.g_miss_num OR p_quantity_picked < 0 THEN
148 fnd_message.set_name ('CSP', 'CSP_INVALID_QTY_PICKED');
149 fnd_msg_pub.add;
150 RAISE EXCP_USER_DEFINED;
151 END IF;
152
153 -- Validate the move order line id
154 IF nvl(p_line_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
155 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
156 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_line_id', FALSE);
157 FND_MSG_PUB.ADD;
158 RAISE EXCP_USER_DEFINED;
159 ELSE
160 BEGIN
161 select line_id into l_check_existence
162 from csp_moveorder_lines
163 where line_id = p_line_id;
164 Exception
165 WHEN NO_DATA_FOUND THEN
166 fnd_message.set_name ('CSP', 'CSP_INVALID_MOVEORDER_LINE');
167 fnd_message.set_token ('LINE_ID', to_char(p_line_id), FALSE);
168 fnd_msg_pub.add;
169 RAISE EXCP_USER_DEFINED;
170 WHEN TOO_MANY_ROWS THEN
171 -- This is normal. One move order line id can map to many transaction_temp_id's.
172 NULL;
173 WHEN OTHERS THEN
174 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
175 fnd_message.set_token('ERR_FIELD', 'p_line_id', FALSE);
176 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
177 fnd_message.set_token('TABLE', 'CSP_MOVEORDER_LINES', FALSE);
178 FND_MSG_PUB.ADD;
179 RAISE EXCP_USER_DEFINED;
180 END;
181 END IF;
182
183 --validate p_picklist_header_id
184 IF nvl(p_picklist_header_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
185 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
186 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_picklist_header_id', FALSE);
187 FND_MSG_PUB.ADD;
188 RAISE EXCP_USER_DEFINED;
189 ELSE
190 BEGIN
191 -- organization id will be used to validate the item id
192 select organization_id into l_organization_id
193 from csp_picklist_headers
194 where picklist_header_id = p_picklist_header_id;
195 EXCEPTION
196 WHEN NO_DATA_FOUND THEN
197 fnd_message.set_name ('CSP', 'CSP_INVALID_PICKLIST_HEADER');
198 fnd_message.set_token ('HEADER_ID', to_char(p_picklist_header_id), FALSE);
199 fnd_msg_pub.add;
200 RAISE EXCP_USER_DEFINED;
201 WHEN OTHERS THEN
202 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
203 fnd_message.set_token('ERR_FIELD', 'p_picklist_header_id', FALSE);
204 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
205 fnd_message.set_token('TABLE', 'CSP_PICKLIST_HEADERS', FALSE);
206 FND_MSG_PUB.ADD;
207 RAISE EXCP_USER_DEFINED;
208 END;
209 END IF;
210
211 --validating inventory_item_id
212 IF nvl(p_inventory_item_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
213 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
214 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_inventory_item_id ', FALSE);
215 FND_MSG_PUB.ADD;
216 RAISE EXCP_USER_DEFINED;
217 ELSE
218 BEGIN
219 -- validate whether the inventory_item_is exists in the given oranization_id
220 select inventory_item_id into l_check_existence
221 from mtl_system_items_kfv
222 where inventory_item_id = p_inventory_item_id
223 and organization_id = l_organization_id;
224 EXCEPTION
225 WHEN NO_DATA_FOUND THEN
226 fnd_message.set_name('INV', 'INV-NO ITEM RECROD');
227 fnd_msg_pub.add;
228 RAISE EXCP_USER_DEFINED;
229 WHEN OTHERS THEN
230 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
231 fnd_message.set_token('ERR_FIELD', 'p_inventory_item_id', FALSE);
232 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
233 fnd_message.set_token('TABLE', 'MTL_SYSTEM_ITEMS', FALSE);
234 FND_MSG_PUB.ADD;
235 RAISE EXCP_USER_DEFINED;
236 END;
237 END IF;
238
239 -- Validate the Picklist_Line_Number
240 IF nvl(p_picklist_line_number, 0) < 1 OR p_picklist_line_number = fnd_api.g_miss_num THEN
241 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
242 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_picklist_line_number', FALSE);
243 FND_MSG_PUB.ADD;
244 RAISE EXCP_USER_DEFINED;
245 END IF;
246
247 IF nvl(p_transaction_temp_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
251 RAISE EXCP_USER_DEFINED;
248 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
249 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_transaction_temp_id', FALSE);
250 FND_MSG_PUB.ADD;
252 ELSE
253 BEGIN
254 -- validate whether the transaction temp id is valid
255 select transaction_temp_id into l_check_existence
256 from mtl_material_transactions_temp
257 where transaction_temp_id = p_transaction_temp_id
258 and inventory_item_id = p_inventory_item_id
259 and move_order_line_id = p_line_id;
260 EXCEPTION
261 WHEN NO_DATA_FOUND THEN
262 fnd_message.set_name ('CSP', 'CSP_INVALID_TXN_TEMP_ID');
263 fnd_message.set_token ('ID', to_char(px_picklist_line_id), FALSE);
264 fnd_msg_pub.add;
265 RAISE EXCP_USER_DEFINED;
266 WHEN OTHERS THEN
267 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
268 fnd_message.set_token('ERR_FIELD', 'p_transaction_temp_id', FALSE);
269 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
270 fnd_message.set_token('TABLE', 'MTL_MATERIAL_TRANSACTIONS_TEMP', FALSE);
271 FND_MSG_PUB.ADD;
272 RAISE EXCP_USER_DEFINED;
273 END;
274 END IF;
275
276 IF nvl(p_uom_code, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
277 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
278 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_uom_code', FALSE);
279 FND_MSG_PUB.ADD;
280 RAISE EXCP_USER_DEFINED;
281 ELSE
282 -- validate the UOM code
283 BEGIN
284 select UOM_CODE into l_check_var
285 from mtl_units_of_measure
286 where UOM_CODE = p_uom_code;
287 EXCEPTION
288 WHEN NO_DATA_FOUND THEN
289 fnd_message.set_name('INV', 'INV_UOM_NOTFOUND');
290 fnd_message.set_token('UOM', p_uom_code, FALSE);
291 fnd_msg_pub.add;
292 RAISE EXCP_USER_DEFINED;
293 WHEN OTHERS THEN
294 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
295 fnd_message.set_token('ERR_FIELD', 'p_line_id', FALSE);
296 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
297 fnd_message.set_token('TABLE', 'MTL_UNITS_OF_MEASURE', FALSE);
298 FND_MSG_PUB.ADD;
299 RAISE EXCP_USER_DEFINED;
300 END;
301 END IF;
302
303 -- check creation_date and last_update_date
304 IF nvl(l_creation_date, fnd_api.g_miss_date) = fnd_api.g_miss_date THEN
305 l_creation_date := sysdate;
306 END IF;
307
308 IF nvl(l_last_update_date, fnd_api.g_miss_date) = fnd_api.g_miss_date THEN
309 l_last_update_date := sysdate;
310 END IF;
311
312 IF nvl(l_created_by, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
313 l_created_by := g_user_id;
314 END IF;
315
316 IF nvl(l_last_update_login, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
317 l_last_update_login := g_login_id;
318 END IF;
319
320 IF nvl(l_last_updated_by, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
321 l_last_updated_by := g_user_id;
322 END IF;
323
324 ELSIF p_action_code = 1 THEN
325 IF px_picklist_line_id IS NULL THEN
326 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
327 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'px_picklist_line_id', FALSE);
328 FND_MSG_PUB.ADD;
329 RAISE EXCP_USER_DEFINED;
330 ELSE
331 BEGIN
332 select picklist_line_id into l_check_existence
333 from csp_picklist_lines
334 where picklist_line_id = px_picklist_line_id;
335 EXCEPTION
336 WHEN NO_DATA_FOUND THEN
337 fnd_message.set_name ('CSP', 'CSP_INVALID_PICKLIST');
338 fnd_message.set_token ('LINE_ID', to_char(px_picklist_line_id), FALSE);
339 fnd_msg_pub.add;
340 RAISE EXCP_USER_DEFINED;
341 WHEN OTHERS THEN
342 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
343 fnd_message.set_token('ERR_FIELD', 'px_picklist_line_id', FALSE);
344 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
345 fnd_message.set_token('TABLE', 'CSP_PICKLIST_LINES', FALSE);
346 FND_MSG_PUB.ADD;
347 RAISE EXCP_USER_DEFINED;
348 END;
349 END IF;
350
351 -- validate the pick list header id associated with the pick list line id
352 IF nvl(p_picklist_header_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
353 --validate the pick list header id.
354 BEGIN
355 -- for bug 1238607.
356 -- Since we are updating the picklist_header_id, we do not need to check whether it exists in the
357 -- csp_picklist_lines table. Instead, we need to make sure that it exists in the csp_picklist_headers
361 where picklist_header_id = p_picklist_header_id;
358 -- table.
359 select picklist_header_id into l_check_existence
360 from csp_picklist_headers
362
363 -- find the organization_id based on the p_pick_line_id
364 select organization_id into l_organization_id
365 from csp_picklist_headers
366 where picklist_header_id = p_picklist_header_id;
367
368 EXCEPTION
369 WHEN NO_DATA_FOUND THEN
370 fnd_message.set_name('CSP', 'CSP_INVALID_PICKLIST_HEADER');
371 fnd_message.set_token ('HEADER_ID', to_char(p_picklist_header_id), FALSE);
372 -- l_msg_data := 'Pick List Header ID does not exist in the organization.';
373 RAISE EXCP_USER_DEFINED;
374 WHEN OTHERS THEN
375 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
376 fnd_message.set_token('ERR_FIELD', 'px_picklist_line_id', FALSE);
377 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
378 fnd_message.set_token('TABLE', 'CSP_PICKLIST_LINES', FALSE);
379 FND_MSG_PUB.ADD;
380 RAISE EXCP_USER_DEFINED;
381 END;
382
383 ELSE -- if the header_id is null
384 -- find the organization_id based on the p_pick_line_id
385 select organization_id into l_organization_id
386 from csp_picklist_headers
387 where picklist_header_id = (select picklist_header_id
388 from csp_picklist_lines
389 where picklist_line_id = px_picklist_line_id);
390 END IF;
391
392 --validating inventory_item_id
393 IF nvl(p_inventory_item_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
394 BEGIN
395 -- validate whether the inventory_item_is exists in the given oranization_id
396 select inventory_item_id into l_check_existence
397 from mtl_system_items_kfv
398 where inventory_item_id = p_inventory_item_id
399 and organization_id = l_organization_id;
400 EXCEPTION
401 WHEN NO_DATA_FOUND THEN
402 fnd_message.set_name('INV', 'INV-NO ITEM RECROD');
403 fnd_msg_pub.add;
404 RAISE EXCP_USER_DEFINED;
405 WHEN OTHERS THEN
406 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
407 fnd_message.set_token('ERR_FIELD', 'p_inventory_item_id', FALSE);
408 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
409 fnd_message.set_token('TABLE', 'MTL_SYSTEM_ITEMS', FALSE);
410 FND_MSG_PUB.ADD;
411 RAISE EXCP_USER_DEFINED;
412 END;
413 END IF;
414
415 -- Validate the move order line id
416 IF nvl(p_line_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
417 BEGIN
418 select line_id into l_check_existence
419 from csp_moveorder_lines
420 where line_id = p_line_id;
421 EXCEPTION
422 WHEN NO_DATA_FOUND THEN
423 fnd_message.set_name ('CSP', 'CSP_INVALID_MOVEORDER_LINE');
424 fnd_message.set_token ('LINE_ID', to_char(p_line_id), FALSE);
425 fnd_msg_pub.add;
426 RAISE EXCP_USER_DEFINED;
427 WHEN TOO_MANY_ROWS THEN
428 -- This is normal. One move order line id can map to many transaction_temp_id's.
429 NULL;
430 WHEN OTHERS THEN
431 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
432 fnd_message.set_token('ERR_FIELD', 'p_line_id', FALSE);
433 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
434 fnd_message.set_token('TABLE', 'CSP_MOVEORDER_LINES', FALSE);
435 FND_MSG_PUB.ADD;
436 RAISE EXCP_USER_DEFINED;
437 END;
438 END IF;
439
440 IF nvl(p_uom_code, fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
441 -- validate the UOM code
442 BEGIN
443 select UOM_CODE into l_check_var
444 from mtl_units_of_measure
445 where UOM_CODE = p_uom_code;
446 EXCEPTION
447 WHEN NO_DATA_FOUND THEN
448 fnd_message.set_name('INV', 'INV_UOM_NOTFOUND');
449 fnd_message.set_token('UOM', p_uom_code, FALSE);
450 fnd_msg_pub.add;
451 RAISE EXCP_USER_DEFINED;
452 WHEN OTHERS THEN
453 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
454 fnd_message.set_token('ERR_FIELD', 'p_line_id', FALSE);
455 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
456 fnd_message.set_token('TABLE', 'MTL_UNITS_OF_MEASURE', FALSE);
457 FND_MSG_PUB.ADD;
458 RAISE EXCP_USER_DEFINED;
459 END;
460 END IF;
461
462 IF nvl(p_transaction_temp_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
463 BEGIN
464 -- validate whether the transaction temp id is valid
468 EXCEPTION
465 select transaction_temp_id into l_check_existence
466 from mtl_material_transactions_temp
467 where transaction_temp_id = p_transaction_temp_id;
469 WHEN NO_DATA_FOUND THEN
470 fnd_message.set_name ('CSP', 'CSP_INVALID_TXN_TEMP_ID');
471 fnd_message.set_token ('ID', to_char(px_picklist_line_id), FALSE);
472 fnd_msg_pub.add;
473 RAISE EXCP_USER_DEFINED;
474 WHEN OTHERS THEN
475 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
476 fnd_message.set_token('ERR_FIELD', 'p_transaction_temp_id', FALSE);
477 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
478 fnd_message.set_token('TABLE', 'MTL_MATERIAL_TRANSACTIONS_TEMP', FALSE);
479 FND_MSG_PUB.ADD;
480 RAISE EXCP_USER_DEFINED;
481 END;
482 END IF;
483
484 -- validate the creation_date
485 IF nvl(l_creation_date, fnd_api.g_miss_date) = fnd_api.g_miss_date THEN
486 Open l_Get_Creation_Date_Csr;
487 Fetch l_Get_Creation_Date_Csr into l_creation_date;
488 If l_Get_Creation_Date_Csr%NOTFOUND Then
489 Close l_Get_Creation_Date_Csr;
490 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
491 fnd_message.set_token('ERR_FIELD', 'p_cretaion_date', FALSE);
492 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
493 fnd_message.set_token('TABLE', 'CSP_PICKLIST_LINES', FALSE);
494 FND_MSG_PUB.ADD;
495 RAISE EXCP_USER_DEFINED;
496 End if;
497 Close l_Get_Creation_Date_Csr;
498 End if;
499
500 IF nvl(l_last_update_date, fnd_api.g_miss_date) = fnd_api.g_miss_date THEN
501 l_last_update_date := sysdate;
502 END IF;
503
504 ELSE -- p_action_code = 2
505 IF px_picklist_line_id IS NULL THEN
506 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
507 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'px_picklist_line_id', FALSE);
508 FND_MSG_PUB.ADD;
509 RAISE EXCP_USER_DEFINED;
510 ELSE
511 BEGIN
512 select picklist_line_id into l_check_existence
513 from csp_picklist_lines
514 where picklist_line_id = px_picklist_line_id;
515 EXCEPTION
516 WHEN NO_DATA_FOUND THEN
517 fnd_message.set_name ('CSP', 'CSP_INVALID_PICKLIST');
518 fnd_message.set_token ('LINE_ID', to_char(px_picklist_line_id), FALSE);
519 fnd_msg_pub.add;
520 RAISE EXCP_USER_DEFINED;
521 WHEN OTHERS THEN
522 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
523 fnd_message.set_token('ERR_FIELD', 'px_picklist_line_id', FALSE);
524 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
525 fnd_message.set_token('TABLE', 'CSP_PICKLIST_LINES', FALSE);
526 FND_MSG_PUB.ADD;
527 RAISE EXCP_USER_DEFINED;
528 END;
529 END IF;
530 END IF;
531
532
533 -- create picklist line record type
534 l_picklist_line_rec.picklist_line_id := px_picklist_line_id;
535 l_picklist_line_rec.created_by := nvl(l_CREATED_BY, fnd_api.g_miss_num);
536 l_picklist_line_rec.creation_date := l_CREATION_DATE;
537 l_picklist_line_rec.last_updated_by := nvl(l_LAST_UPDATED_BY, fnd_api.g_miss_num);
538 l_picklist_line_rec.last_update_date := l_LAST_UPDATE_DATE;
539 l_picklist_line_rec.last_update_login := l_LAST_UPDATE_LOGIN;
540 l_picklist_line_rec.picklist_line_number:= nvl(p_PICKLIST_LINE_NUMBER, fnd_api.g_miss_num);
541 l_picklist_line_rec.picklist_header_id := nvl(p_picklist_header_id, fnd_api.g_miss_num);
542 l_picklist_line_rec.LINE_ID := nvl(p_LINE_ID, fnd_api.g_miss_num);
543 l_picklist_line_rec.INVENTORY_ITEM_ID := nvl(p_INVENTORY_ITEM_ID, fnd_api.g_miss_num);
544 l_picklist_line_rec.UOM_CODE := nvl(p_UOM_CODE, fnd_api.g_miss_char);
545 l_picklist_line_rec.REVISION := p_REVISION;
546 l_picklist_line_rec.QUANTITY_PICKED := nvl(p_QUANTITY_PICKED, fnd_api.g_miss_num);
547 l_picklist_line_rec.TRANSACTION_TEMP_ID := nvl(p_TRANSACTION_TEMP_ID, fnd_api.g_miss_num);
548 l_picklist_line_rec.ATTRIBUTE_CATEGORY := p_ATTRIBUTE_CATEGORY;
549 l_picklist_line_rec.ATTRIBUTE1 := p_ATTRIBUTE1;
550 l_picklist_line_rec.ATTRIBUTE2 := p_ATTRIBUTE2;
551 l_picklist_line_rec.ATTRIBUTE3 := p_ATTRIBUTE3;
552 l_picklist_line_rec.ATTRIBUTE4 := p_ATTRIBUTE4;
553 l_picklist_line_rec.ATTRIBUTE5 := p_ATTRIBUTE5;
554 l_picklist_line_rec.ATTRIBUTE6 := p_ATTRIBUTE6;
555 l_picklist_line_rec.ATTRIBUTE7 := p_ATTRIBUTE7;
556 l_picklist_line_rec.ATTRIBUTE8 := p_ATTRIBUTE8;
557 l_picklist_line_rec.ATTRIBUTE9 := p_ATTRIBUTE9;
558 l_picklist_line_rec.ATTRIBUTE10 := p_ATTRIBUTE10;
559 l_picklist_line_rec.ATTRIBUTE11 := p_ATTRIBUTE11;
560 l_picklist_line_rec.ATTRIBUTE12 := p_ATTRIBUTE12;
561 l_picklist_line_rec.ATTRIBUTE13 := p_ATTRIBUTE13;
562 l_picklist_line_rec.ATTRIBUTE14 := p_ATTRIBUTE14;
563 l_picklist_line_rec.ATTRIBUTE15 := p_ATTRIBUTE15;
564
565
566 IF p_action_code = 0 THEN
567 csp_picklist_lines_pvt.Create_picklist_lines(
568 P_Api_Version_Number => p_api_version_number,
569 P_Init_Msg_List => P_Init_Msg_List,
570 P_Commit => P_Commit,
571 p_validation_level => l_validation_level,
572 P_picklist_line_Rec => l_picklist_line_rec,
573 X_PICKLIST_LINE_ID => l_picklist_line_id,
574 X_Return_Status => x_return_status,
575 X_Msg_Count => x_msg_count,
576 X_Msg_Data => x_msg_data
577 );
578
579 ELSIF p_action_code = 1 THEN
580 csp_picklist_lines_pvt.Update_picklist_lines(
581 P_Api_Version_Number => p_api_version_number,
582 P_Init_Msg_List => P_Init_Msg_List,
583 P_Commit => P_Commit,
584 p_validation_level => l_validation_level,
585 --P_Identity_Salesforce_Id IN NUMBER := NULL,
586 P_picklist_line_Rec => l_picklist_line_rec,
587 X_Return_Status => l_return_status,
588 X_Msg_Count => x_msg_count,
589 X_Msg_Data => x_msg_data
590 );
591
592 ELSIF p_action_code = 2 THEN
593 csp_picklist_lines_pvt.Delete_picklist_lines(
594 P_Api_Version_Number => p_api_version_number,
595 P_Init_Msg_List => P_Init_Msg_List,
596 p_Commit => P_Commit,
597 p_validation_level => l_validation_level,
598 --P_identity_salesforce_id IN NUMBER := NULL,
599 P_picklist_line_Rec => l_picklist_line_rec,
600 X_Return_Status => l_return_status,
601 X_Msg_Count => x_msg_count,
602 X_Msg_Data => x_msg_data
603 );
604
605 END IF;
606
607 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
608 RAISE FND_API.G_EXC_ERROR;
609 END IF;
610
611 x_return_status := FND_API.G_RET_STS_SUCCESS;
612 px_picklist_line_id := l_picklist_line_id;
613 IF fnd_api.to_boolean(p_commit) THEN
614 commit work;
615 END IF;
616
617
618 EXCEPTION
619 WHEN EXCP_USER_DEFINED THEN
620 Rollback to Validate_And_Write_PUB;
621 x_return_status := FND_API.G_RET_STS_ERROR;
622 fnd_msg_pub.count_and_get
623 ( p_count => x_msg_count
624 , p_data => x_msg_data);
625 WHEN FND_API.G_EXC_ERROR THEN
626 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
627 P_API_NAME => L_API_NAME
628 ,P_PKG_NAME => G_PKG_NAME
629 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
630 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
631 ,X_MSG_COUNT => X_MSG_COUNT
632 ,X_MSG_DATA => X_MSG_DATA
633 ,X_RETURN_STATUS => X_RETURN_STATUS);
634 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
635 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
636 P_API_NAME => L_API_NAME
637 ,P_PKG_NAME => G_PKG_NAME
638 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
639 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
640 ,X_MSG_COUNT => X_MSG_COUNT
641 ,X_MSG_DATA => X_MSG_DATA
642 ,X_RETURN_STATUS => X_RETURN_STATUS);
643 WHEN OTHERS THEN
644 Rollback to Validate_And_Write_PUB;
645 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
646 fnd_message.set_token ('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
647 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
648 fnd_msg_pub.add;
649 fnd_msg_pub.count_and_get
650 ( p_count => x_msg_count
651 , p_data => x_msg_data);
652 x_return_status := fnd_api.g_ret_sts_error;
653
654 END Validate_And_Write;
655
656 END CSP_PC_FORM_PICKLINES;