1 PACKAGE BODY CSP_RESOURCE_PUB AS
2 /* $Header: cspgtreb.pls 120.1 2005/10/11 23:49:01 hhaugeru noship $ */
3 -- Start of Comments
4 -- Package name : CSP_RESOURCE_PUB
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_RESOURCE_PUB';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspgtreb.pls';
13
14 Procedure ASSIGN_RESOURCE_INV_LOC (
15 -- Start of Comments
16 -- Procedure : ASSIGN_RESOURCE_INV_LOC
17 -- Purpose : This procedure is used to create, update and delete the assignment of resource to different
18 -- inventory locations. It assigns a resource only to those inventory locations that
19 -- contain spares and is in the operation unit.
20 --
21 -- History :
22 -- UserID Date Comments
23 -- ----------- -------- --------------------------
24 -- klou 01/20/00 Modify exception handlings and message libraries such that they are compliant with
25 -- CRM standard.
26 -- klou 01/12/00 Add CSP_Resource_PVT.CSP_Rec_Type record parameters and p_action_code.
27 -- p_action_code: 0 = insert, 1 = update, 2 = delete.
28 -- Add update and delete operations.
29 -- klou 01/11/99 Add validatoins on Resource_id and Resource_type.
30 -- klou 12/16/99 Include validation of subinventory_type against Part-in and Part-out default code.
31 -- klou 12/15/99 a. replace the use of count() with exception no_data_found to check whether data exists in the table.
32 -- b. include standard exception handling.
33 -- klou 12/14/99 Comment out validations for resource_id an resource_type because the jtf_resource_extn table is corrupted
34 -- klou 11/09/99 Create.
35 --
36 -- NOTES: If validations have been done in the precedent procedure from which this one is being called, doing a
37 -- full validation here is unnecessary. To avoid repeating the same validations, you can set the
38 -- p_validation_level to fnd_api.g_valid_level_none when making the procedure call. However, it is your
39 -- responsibility to make sure all proper validations have been done before calling this procedure.
43 --
40 -- You are recommended to let this procedure handle the validations if you are not sure.
41 --
42 -- NOTES: This procedure does not consider the fnd_api.g_miss_num and fnd_api.g_miss_char.
44 -- CAUTIONS: This procedure *ALWAYS* calls other procedures with validation_level set to FND_API.G_VALID_LEVEL_NONE.
45 -- If you do not do your own validations before calling this procedure, you should set the p_validation_level
46 -- to FND_API.G_VALID_LEVEL_FULL when making the call.
47 --
48 --End of Comments
49 P_Api_Version_Number IN NUMBER
50 ,P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
51 ,P_Commit IN VARCHAR2 := FND_API.G_FALSE
52 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
53 ,p_action_code IN NUMBER -- 0 = insert, 1 = update, 2 = delete
54 ,px_CSP_INV_LOC_ASSIGNMENT_ID IN OUT NOCOPY NUMBER
55 ,p_CREATED_BY IN NUMBER
56 ,p_CREATION_DATE IN DATE
57 ,p_LAST_UPDATED_BY IN NUMBER
58 ,p_LAST_UPDATE_DATE IN DATE
59 ,p_LAST_UPDATE_LOGIN IN NUMBER
60 ,p_RESOURCE_ID IN NUMBER
61 ,p_ORGANIZATION_ID IN NUMBER
62 ,p_SUBINVENTORY_CODE IN VARCHAR2
63 ,p_LOCATOR_ID IN NUMBER
64 ,p_RESOURCE_TYPE IN VARCHAR2
65 ,p_EFFECTIVE_DATE_START IN DATE
66 ,p_EFFECTIVE_DATE_END IN DATE
67 ,p_DEFAULT_CODE IN VARCHAR2
68 ,p_ATTRIBUTE_CATEGORY IN VARCHAR2 := NULL
69 ,p_ATTRIBUTE1 IN VARCHAR2 := NULL
70 ,p_ATTRIBUTE2 IN VARCHAR2 := NULL
71 ,p_ATTRIBUTE3 IN VARCHAR2 := NULL
72 ,p_ATTRIBUTE4 IN VARCHAR2 := NULL
73 ,p_ATTRIBUTE5 IN VARCHAR2 := NULL
74 ,p_ATTRIBUTE6 IN VARCHAR2 := NULL
75 ,p_ATTRIBUTE7 IN VARCHAR2 := NULL
76 ,p_ATTRIBUTE8 IN VARCHAR2 := NULL
77 ,p_ATTRIBUTE9 IN VARCHAR2 := NULL
78 ,p_ATTRIBUTE10 IN VARCHAR2 := NULL
79 ,p_ATTRIBUTE11 IN VARCHAR2 := NULL
80 ,p_ATTRIBUTE12 IN VARCHAR2 := NULL
81 ,p_ATTRIBUTE13 IN VARCHAR2 := NULL
82 ,p_ATTRIBUTE14 IN VARCHAR2 := NULL
83 ,p_ATTRIBUTE15 IN VARCHAR2 := NULL
84 ,x_return_status OUT NOCOPY VARCHAR2
85 ,x_msg_count OUT NOCOPY NUMBER
86 ,x_msg_data OUT NOCOPY VARCHAR2
87 )
88
89 IS
90 l_api_version_number CONSTANT NUMBER := 1.0;
91 l_api_name CONSTANT VARCHAR2(30) := 'Assign_Resource_Inv_Loc';
92 l_csp_rec CSP_RESOURCE_PVT.CSP_Rec_Type;
93 l_return_status VARCHAR2(1);
94 l_msg_count NUMBER := 0;
95 l_msg_data VARCHAR2(500);
96 l_check_existence NUMBER := 0;
97 l_resource_type VARCHAR2(50);
98 l_assignment_id NUMBER;
99 l_default_code VARCHAR2(10) := p_default_code;
100 l_invalid_default_code VARCHAR2(1) := FND_API.G_FALSE;
101 EXCP_USER_DEFINED EXCEPTION;
102 l_record_status VARCHAR2(1) := FND_API.G_TRUE;
103 l_resource_name VARCHAR2(360);
104
105 BEGIN
106 -- Start of API savepoint
107 SAVEPOINT ASSIGN_RESOURCE_INV_LOC_PUB;
108
109 -- initialize message list
110 IF fnd_api.to_boolean(p_init_msg_list) THEN
111 FND_MSG_PUB.initialize;
112 END IF;
113
114 -- Standard call to check for call compatibility.
115 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
116 p_api_version_number,
117 l_api_name,
118 G_PKG_NAME)
119 THEN
120 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
121 END IF;
122 -- END IF;
123
124 -- check p_action_code
125 /*IF p_action_code NOT IN (0, 1, 2) THEN
126 l_msg_data := 'p_action_code must be 0, 1, or 2.';
127 RAISE EXCP_USER_DEFINED;
128 END IF;*/
129
130 IF p_action_code in (0,1) THEN
131 IF p_validation_level = FND_API.G_VALID_LEVEL_FULL THEN
132
133 -- start full validations
134 IF p_organization_id IS NULL THEN
135 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
136 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_organization_id', TRUE);
137 FND_MSG_PUB.ADD;
138 RAISE EXCP_USER_DEFINED;
139 ELSIF p_organization_id IS NOT NULL AND p_action_code IN (0,1) THEN
140 -- check whether the organizaton exists.
141 BEGIN
142 select organization_id into l_check_existence
143 from mtl_parameters
144 where organization_id = p_organization_id;
145 EXCEPTION
146 WHEN NO_DATA_FOUND THEN
147 FND_MESSAGE.SET_NAME ('INV', 'INVALID ORGANIZATION');
148 FND_MSG_PUB.ADD;
149 RAISE EXCP_USER_DEFINED;
150 WHEN OTHERS THEN
151 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
152 fnd_message.set_token('ERR_FIELD', 'p_organization_id', TRUE);
153 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
154 fnd_message.set_token('TABLE', 'mtl_organizations', TRUE);
155 FND_MSG_PUB.ADD;
156 RAISE EXCP_USER_DEFINED;
160 END IF;
157 END;
158 ELSE -- it must be p_organization_id = null and action_code = 2. do nothing for this case.
159 NULL;
161
162 IF p_subinventory_code IS NULL THEN
163 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
164 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_subinventory_code', TRUE);
165 FND_MSG_PUB.ADD;
166 RAISE EXCP_USER_DEFINED;
167 -- check whether the subinventory is a Spare subinventory in the organization.
168 ELSE
169 /* BEGIN
170 SELECT SECONDARY_INVENTORY_ID INTO l_check_existence
171 FROM csp_sec_inventories
172 WHERE organization_id = p_organization_id
173 AND secondary_inventory_name = p_subinventory_code;
174
175 EXCEPTION
176 WHEN NO_DATA_FOUND THEN
177 fnd_message.set_name('CSP', 'CSP_NOT_SPARES_SUB');
178 fnd_msg_pub.add;
179 RAISE EXCP_USER_DEFINED;
180 WHEN TOO_MANY_ROWS THEN
181 -- this is a valid situation
182 NULL;
183 WHEN OTHERS THEN
184 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
185 fnd_message.set_token('ERR_FIELD', 'p_subinventory_code', TRUE);
186 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
187 fnd_message.set_token('TABLE', 'csp_sec_inventories', TRUE);
188 FND_MSG_PUB.ADD;
189 RAISE EXCP_USER_DEFINED;
190 END;
191 */
192 -- valide the p_locator_id if it is not null
193 IF p_locator_id IS NOT NULL THEN
194 BEGIN
195 SELECT inventory_location_id INTO l_check_existence
196 FROM mtl_item_locations
197 WHERE organization_id = p_organization_id
198 AND subinventory_code = p_subinventory_code
199 AND inventory_location_id = p_locator_id;
200
201 EXCEPTION
202 WHEN NO_DATA_FOUND THEN
203 fnd_message.set_name('INV', 'INV_LOCATOR_NOT_AVAILABLE');
204 fnd_msg_pub.add;
205 RAISE EXCP_USER_DEFINED;
206 /*WHEN TOO_MANY_ROWS THEN
207 l_msg_data := 'More than one same Locator ID was found. The locator table might not be set up correctly.';
208 RAISE EXCP_USER_DEFINED;*/
209 WHEN OTHERS THEN
210 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
211 fnd_message.set_token('ERR_FIELD', 'p_locator_id', TRUE);
212 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
213 fnd_message.set_token('TABLE', 'mtl_item_locations', TRUE);
214 fnd_msg_pub.ADD;
215 --l_msg_data := 'Unexpected errors occurred while validating the Locator ID. Please contact your system administrator.';
216 RAISE EXCP_USER_DEFINED;
217 END;
218 END IF;
219 END IF;
220
221 IF p_resource_id IS NULL THEN
222 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
223 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_resource_id', TRUE);
224 FND_MSG_PUB.ADD;
225 RAISE EXCP_USER_DEFINED;
226
227 -- check whether the resource id and resource type exist.
228 ELSE
229 BEGIN
230 -- SELECT resource_id INTO l_check_existence
231 -- FROM jtf_rs_all_resources_vl
232 -- WHERE resource_id = p_resource_id
233 -- AND resource_type = p_resource_type;
234 select csf_util_pvt.get_object_name(p_resource_type, p_resource_id) into l_resource_name
235 from dual;
236
237 EXCEPTION
238 WHEN NO_DATA_FOUND THEN
239 fnd_message.set_name('CSP', 'CSP_INVALID_RES_ID_TYPE');
240 fnd_msg_pub.add;
241 RAISE EXCP_USER_DEFINED;
242 /*WHEN TOO_MANY_ROWS THEN
243 l_msg_data := 'Duplicate Resource ID found. There may be an error in your Resource table.';
244 RAISE EXCP_USER_DEFINED;*/
245 WHEN OTHERS THEN
246 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
247 fnd_message.set_token('ERR_FIELD', 'p_resource_id', TRUE);
248 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
249 fnd_message.set_token('TABLE', 'jtf_rs_all_resources_vl', TRUE);
250 fnd_msg_pub.ADD;
251 RAISE EXCP_USER_DEFINED;
252 END;
253 END IF;
254
255
256 END IF; -- end of validaitons
257
258 /* --
259 Validate the default code:
260 p_default_code = 'IN', validate whether the p_subinventory_code is of a 'G' (good) type.
261 p_default_code = 'OUT', validate whether the p_subinventory-code is of a 'B' (bad) type.
262 p_default_code = NOT NULL, not 'IN' and not 'OUT', create a warning message and then set it to NULL
263 p_default_code = NULL, do nothing.
264 -- */
265 DECLARE
266 l_subinventory_type VARCHAR2(10);
267 l_good_type VARCHAR2(1) := fnd_api.g_false;
268 l_bad_type VARCHAR2(1) := fnd_api.g_false;
269 CURSOR c_Get_Condition_Type IS
270 SELECT condition_type
271 FROM csp_sec_inventories
272 WHERE secondary_inventory_name = p_subinventory_code
273 AND organization_id = p_organization_id;
274
275 BEGIN
276 --find the condition type of p_subinventory_code
280 FETCH c_Get_Condition_Type into l_subinventory_type;
277 OPEN c_Get_Condition_Type;
278 LOOP
279
281 EXIT WHEN c_Get_Condition_Type%NOTFOUND;
282 if l_subinventory_type = 'G' then
283 l_good_type := fnd_api.g_true;
284 elsif l_subinventory_type = 'B' then
285 l_bad_type := fnd_api.g_true;
286 else null;
287 end if;
288 END LOOP;
289 CLOSE c_Get_Condition_Type;
290
291 IF upper(l_default_code) = 'IN' THEN
292 IF fnd_api.to_boolean(l_bad_type) AND NOT fnd_api.to_boolean(l_good_type) THEN
293
294 fnd_message.set_name('CSP', 'CSP_INVALID_IN_OUT_SUB');
295 fnd_msg_pub.ADD;
296 RAISE EXCP_USER_DEFINED;
297 END IF;
298
299 ELSIF upper(l_default_code) = 'OUT' THEN
300 IF fnd_api.to_boolean(l_good_type) AND NOT fnd_api.to_boolean(l_bad_type)THEN
301
302 fnd_message.set_name('CSP', 'CSP_INVALID_IN_OUT_SUB');
303 fnd_msg_pub.ADD;
304 RAISE EXCP_USER_DEFINED;
305 END IF;
306
307 ELSIF l_default_code IS NOT NULL AND NOT fnd_api.to_boolean(l_good_type)
308 AND NOT fnd_api.to_boolean(l_bad_type) AND p_action_code = 0 THEN
309
310 fnd_message.set_name('CSP', 'CSP_RES_INV_WARNING');
311 fnd_msg_pub.ADD;
312 l_default_code := NULL;
313 ELSE NULL;
314 END IF;
315
316 END;
317 END IF;
318
319 IF p_action_code = 0 THEN
320 -- now we are ready to call the insert operation.
321 IF px_CSP_INV_LOC_ASSIGNMENT_ID IS NOT NULL THEN
322 -- we have to find out whether the record ready exists.
323 BEGIN
324 SELECT csp_inv_loc_assignment_id INTO l_check_existence
325 FROM csp_inv_loc_assignments
326 WHERE csp_inv_loc_assignment_id = px_csp_inv_loc_assignment_id;
327
328 RAISE TOO_MANY_ROWS;
329 EXCEPTION
330 WHEN NO_DATA_FOUND THEN
331 -- It's a good case.
332 NULL;
333 WHEN TOO_MANY_ROWS THEN
334 fnd_message.set_name('CSP', 'CSP_DUPLICATE_RECORD');
335 fnd_msg_pub.ADD;
336 RAISE EXCP_USER_DEFINED;
337 WHEN OTHERS THEN
338 --l_msg_data := SQLERRM(SQLCODE);
339 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
340 fnd_message.set_token('ERR_FIELD', 'px_csp_inv_loc_assignment_id', TRUE);
341 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
342 fnd_message.set_token('TABLE', 'csp_inv_loc_assignments', TRUE);
343 fnd_msg_pub.ADD;
344 RAISE EXCP_USER_DEFINED;
345 END;
346 END IF;
347
348 /* IF p_EFFECTIVE_DATE_START IS NULL OR p_EFFECTIVE_DATE_END IS NULL THEN
349 fnd_message.set_name('CSP', 'CSP_INVALID_START_END_DATES');
350 fnd_msg_pub.add;
351 RAISE EXCP_USER_DEFINED;
352 END IF;
353 */
354 -- validate whether the combination already exists, if yes, raise the EXCP_DUPLICATE_RECORD exception
355 validate_assignment_record (
356 p_resource_id => p_resource_id
357 ,p_resource_type => p_resource_type
358 ,p_organization_id => p_organization_id
359 ,p_subinventory_code => p_subinventory_code
360 ,p_default_code => p_default_code
361 ,x_return_status => l_record_status);
362
363 IF FND_API.to_Boolean(l_record_status ) THEN
364 fnd_message.set_name('CSP', 'CSP_DUPLICATE_RECORD');
365 fnd_msg_pub.ADD;
366 RAISE EXCP_USER_DEFINED;
367 END IF;
368
369 -- construct the l_csp_rec record
370 l_csp_rec.CSP_INV_LOC_ASSIGNMENT_ID := px_CSP_INV_LOC_ASSIGNMENT_ID;
371 l_csp_rec.CREATED_BY := p_CREATED_BY;
372 l_csp_rec.CREATION_DATE := p_CREATION_DATE;
373 l_csp_rec.LAST_UPDATED_BY := p_LAST_UPDATED_BY;
374 l_csp_rec.LAST_UPDATE_DATE := p_LAST_UPDATE_DATE;
375 l_csp_rec.LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN;
376 l_csp_rec.RESOURCE_ID := p_RESOURCE_ID;
377 l_csp_rec.ORGANIZATION_ID := p_ORGANIZATION_ID;
378 l_csp_rec.SUBINVENTORY_CODE := p_SUBINVENTORY_CODE;
379 l_csp_rec.LOCATOR_ID := p_LOCATOR_ID;
380 l_csp_rec.RESOURCE_TYPE := p_RESOURCE_TYPE;
381 l_csp_rec.EFFECTIVE_DATE_START := p_EFFECTIVE_DATE_START;
382 l_csp_rec.EFFECTIVE_DATE_END := p_EFFECTIVE_DATE_END;
383 l_csp_rec.DEFAULT_CODE := p_DEFAULT_CODE;
384 l_csp_rec.ATTRIBUTE_CATEGORY := p_ATTRIBUTE_CATEGORY;
385 l_csp_rec.ATTRIBUTE1 := p_ATTRIBUTE1;
386 l_csp_rec.ATTRIBUTE2 := p_ATTRIBUTE2;
387 l_csp_rec.ATTRIBUTE3 := p_ATTRIBUTE3;
388 l_csp_rec.ATTRIBUTE4 := p_ATTRIBUTE4;
389 l_csp_rec.ATTRIBUTE5 := p_ATTRIBUTE5;
390 l_csp_rec.ATTRIBUTE6 := p_ATTRIBUTE6;
391 l_csp_rec.ATTRIBUTE7 := p_ATTRIBUTE7;
392 l_csp_rec.ATTRIBUTE8 := p_ATTRIBUTE8;
393 l_csp_rec.ATTRIBUTE9 := p_ATTRIBUTE9;
394 l_csp_rec.ATTRIBUTE10 := p_ATTRIBUTE10;
395 l_csp_rec.ATTRIBUTE11 := p_ATTRIBUTE11;
399 l_csp_rec.ATTRIBUTE15 := p_ATTRIBUTE15;
396 l_csp_rec.ATTRIBUTE12 := p_ATTRIBUTE12;
397 l_csp_rec.ATTRIBUTE13 := p_ATTRIBUTE13;
398 l_csp_rec.ATTRIBUTE14 := p_ATTRIBUTE14;
400
401 CSP_RESOURCE_PVT.Create_resource(
402 P_Api_Version_Number => p_api_version_number,
403 P_Init_Msg_List => FND_API.G_TRUE,
404 P_Commit => FND_API.G_FALSE,
405 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
406 P_CSP_Rec => l_csp_rec,
407 X_CSP_INV_LOC_ASSIGNMENT_ID => l_assignment_id,
408 X_Return_Status => l_return_status,
409 X_Msg_Count => l_msg_count,
410 X_Msg_Data => l_msg_data
411 );
412 ELSIF p_action_code in(1, 2) THEN
413
414 -- make sure the record exists.
415 IF px_CSP_INV_LOC_ASSIGNMENT_ID IS NULL THEN
416 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
417 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'px_csp_inv_loc_assignment_id', TRUE);
418 FND_MSG_PUB.ADD;
419 RAISE EXCP_USER_DEFINED;
420 ELSE
421 BEGIN
422 SELECT csp_inv_loc_assignment_id INTO l_check_existence
423 FROM csp_inv_loc_assignments
424 WHERE csp_inv_loc_assignment_id = px_csp_inv_loc_assignment_id;
425
426
427 EXCEPTION
428 WHEN NO_DATA_FOUND THEN
429 fnd_message.set_name('CSP', 'CSP_INVALID_ASSIGNMENT_ID');
430 fnd_message.set_token('ASSIGNMENT_ID', to_char(px_csp_inv_loc_assignment_id), TRUE);
431 fnd_msg_pub.add;
432 RAISE EXCP_USER_DEFINED;
433 -- WHEN TOO_MANY_ROWS THEN
434 -- l_msg_data := 'Too many Assignment ID '||px_csp_inv_loc_assignment_id||' found. You may have a data setup problem.';
435 -- RAISE EXCP_USER_DEFINED;
436 WHEN OTHERS THEN
437 --l_msg_data := SQLERRM(SQLCODE);
438 -- l_msg_data := l_msg_data||'. Unexpected errors occurred while validating the Assignment ID. Please contact your system administrator.';
439 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
440 fnd_message.set_token('ERR_FIELD', 'px_csp_inv_loc_assignment_id', TRUE);
441 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
442 fnd_message.set_token('TABLE', 'csp_inv_loc_assignments', TRUE);
443 fnd_msg_pub.ADD;
444 RAISE EXCP_USER_DEFINED;
445 END;
446 -- construct the l_csp_rec record
447 l_csp_rec.CSP_INV_LOC_ASSIGNMENT_ID := px_CSP_INV_LOC_ASSIGNMENT_ID;
448 l_csp_rec.CREATED_BY := p_CREATED_BY;
449 l_csp_rec.CREATION_DATE := p_CREATION_DATE;
450 l_csp_rec.LAST_UPDATED_BY := p_LAST_UPDATED_BY;
451 l_csp_rec.LAST_UPDATE_DATE := p_LAST_UPDATE_DATE;
452 l_csp_rec.LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN;
453 l_csp_rec.RESOURCE_ID := p_RESOURCE_ID;
454 l_csp_rec.ORGANIZATION_ID := p_ORGANIZATION_ID;
455 l_csp_rec.SUBINVENTORY_CODE := p_SUBINVENTORY_CODE;
456 l_csp_rec.LOCATOR_ID := p_LOCATOR_ID;
457 l_csp_rec.RESOURCE_TYPE := p_RESOURCE_TYPE;
458 l_csp_rec.EFFECTIVE_DATE_START := p_EFFECTIVE_DATE_START;
459 l_csp_rec.EFFECTIVE_DATE_END := p_EFFECTIVE_DATE_END;
460 l_csp_rec.DEFAULT_CODE := p_DEFAULT_CODE;
461 l_csp_rec.ATTRIBUTE_CATEGORY := p_ATTRIBUTE_CATEGORY;
462 l_csp_rec.ATTRIBUTE1 := p_ATTRIBUTE1;
463 l_csp_rec.ATTRIBUTE2 := p_ATTRIBUTE2;
464 l_csp_rec.ATTRIBUTE3 := p_ATTRIBUTE3;
465 l_csp_rec.ATTRIBUTE4 := p_ATTRIBUTE4;
466 l_csp_rec.ATTRIBUTE5 := p_ATTRIBUTE5;
467 l_csp_rec.ATTRIBUTE6 := p_ATTRIBUTE6;
468 l_csp_rec.ATTRIBUTE7 := p_ATTRIBUTE7;
469 l_csp_rec.ATTRIBUTE8 := p_ATTRIBUTE8;
470 l_csp_rec.ATTRIBUTE9 := p_ATTRIBUTE9;
471 l_csp_rec.ATTRIBUTE10 := p_ATTRIBUTE10;
472 l_csp_rec.ATTRIBUTE11 := p_ATTRIBUTE11;
473 l_csp_rec.ATTRIBUTE12 := p_ATTRIBUTE12;
474 l_csp_rec.ATTRIBUTE13 := p_ATTRIBUTE13;
475 l_csp_rec.ATTRIBUTE14 := p_ATTRIBUTE14;
476 l_csp_rec.ATTRIBUTE15 := p_ATTRIBUTE15;
477
478 IF p_action_code = 1 THEN
479 -- we have to make sure that by updating an existing a record. A
480 -- duplicate record will not be created.
481 DECLARE
482 l_csp_rec_update CSP_RESOURCE_PVT.CSP_Rec_Type;
483 l_result NUMBER;
484 CURSOR C_Get_Inv_Loc_Assignments IS
485 SELECT CSP_INV_LOC_ASSIGNMENT_ID ,
486 CREATED_BY ,
487 CREATION_DATE ,
488 LAST_UPDATED_BY ,
489 LAST_UPDATE_DATE ,
490 LAST_UPDATE_LOGIN ,
491 RESOURCE_ID ,
492 ORGANIZATION_ID ,
493 SUBINVENTORY_CODE ,
494 LOCATOR_ID ,
495 RESOURCE_TYPE ,
496 EFFECTIVE_DATE_START ,
497 EFFECTIVE_DATE_END ,
501 ATTRIBUTE2 ,
498 DEFAULT_CODE ,
499 ATTRIBUTE_CATEGORY ,
500 ATTRIBUTE1 ,
502 ATTRIBUTE3 ,
503 ATTRIBUTE4 ,
504 ATTRIBUTE5 ,
505 ATTRIBUTE6 ,
506 ATTRIBUTE7 ,
507 ATTRIBUTE8 ,
508 ATTRIBUTE9 ,
509 ATTRIBUTE10 ,
510 ATTRIBUTE11 ,
511 ATTRIBUTE12 ,
512 ATTRIBUTE13 ,
513 ATTRIBUTE14 ,
514 ATTRIBUTE15
515 FROM csp_inv_loc_assignments
516 WHERE csp_inv_loc_assignment_id = px_csp_inv_loc_assignment_id;
517
518 BEGIN
519
520 OPEN C_Get_Inv_Loc_Assignments;
521 FETCH C_Get_Inv_Loc_Assignments INTO l_csp_rec_update;
522 IF C_Get_Inv_Loc_Assignments%NOTFOUND THEN
523 CLOSE C_Get_Inv_Loc_Assignments;
524 fnd_message.set_name('CSP', 'CSP_INVALID_ASSIGNMENT_ID');
525 fnd_message.set_token('ASSIGNMENT_ID', to_char(px_csp_inv_loc_assignment_id), TRUE);
526 fnd_msg_pub.add;
527 RAISE EXCP_USER_DEFINED;
528 END IF;
529 CLOSE C_Get_Inv_Loc_Assignments;
530
531 SELECT csp_inv_loc_assignment_id into l_result
532 FROM csp_inv_loc_assignments
533 WHERE resource_id = p_resource_id
534 AND resource_type = p_resource_type
535 AND organization_id = p_organization_id
536 AND subinventory_code = p_subinventory_code
537 AND default_code = p_default_code
538 AND csp_inv_loc_assignment_id <> px_csp_inv_loc_assignment_id;
539
540 RAISE TOO_MANY_ROWS;
541 EXCEPTION
542 WHEN NO_DATA_FOUND THEN
543 /*
544 -- The update will not create a duplicate record.
545 -- But we also need to check the subinventory In-Good, and Out-Bad
546 -- conditions are maintained after the update.
547 IF p_subinventory_code IS NOT NULL OR p_default_code IS NOT NULL THEN
548 /* --
549 Validate the default code:
550 p_default_code = 'IN', validate whether the p_subinventory_code is of a 'G' (good) type.
551 p_default_code = 'OUT', validate whether the p_subinventory-code is of a 'B' (bad) type.
552 p_default_code = NOT NULL, not 'IN' and not 'OUT', create a warning message and then set it to NULL
553 p_default_code = NULL, do nothing.
554 -- */
555 /*DECLARE
556 l_subinventory_type VARCHAR2(10);
557 l_good_type VARCHAR2(1) := fnd_api.g_false;
558 l_bad_type VARCHAR2(1) := fnd_api.g_false;
559 CURSOR c_Get_Condition_Type IS
560 SELECT condition_type
561 FROM csp_sec_inventories
562 WHERE secondary_inventory_name = decode(p_subinventory_code, null, l_csp_rec_update.subinventory_code, p_subinventory_code)
563 AND organization_id = decode(p_organization_id, null, l_csp_rec_update.organization_id, p_organization_id);
564
565 BEGIN
566 --find the condition type of p_subinventory_code
567 OPEN c_Get_Condition_Type;
568 LOOP
569 FETCH c_Get_Condition_Type into l_subinventory_type;
570 EXIT WHEN c_Get_Condition_Type%NOTFOUND;
571
572 if l_subinventory_type = 'G' then
573 l_good_type := fnd_api.g_true;
574 elsif l_subinventory_type = 'B' then
575 l_bad_type := fnd_api.g_true;
576 else null;
577 end if;
578 END LOOP;
579 IF c_Get_Condition_Type%rowcount = 0 THEN
580 l_msg_data := 'The subinventory is not a spares inventory.';
581 CLOSE c_Get_Condition_Type;
582 RAISE EXCP_USER_DEFINED;
583 END IF;
584 CLOSE c_Get_Condition_Type;
585
586 IF upper(nvl(p_default_code, l_csp_rec_update.default_code)) = 'IN' THEN
587 IF fnd_api.to_boolean(l_bad_type) AND NOT fnd_api.to_boolean(l_good_type) THEN
588 l_msg_data := 'Only a Good subinventory is allowed to be assigned as a Part-In subinventory.';
592
589 l_msg_data := l_msg_data ||' Please check the condition type of subinventory '||p_subinventory_code||'.';
590 RAISE EXCP_USER_DEFINED;
591 END IF;
593 ELSIF upper(nvl(p_default_code, l_csp_rec_update.default_code)) = 'OUT' THEN
594 IF fnd_api.to_boolean(l_good_type) AND NOT fnd_api.to_boolean(l_bad_type)THEN
595 l_msg_data := 'Only a Bad subinventory is allowed to be assigned as a Part-Out subinventory.';
596 l_msg_data := l_msg_data ||' Please check the condition type of subinventory '||p_subinventory_code||'.';
597 RAISE EXCP_USER_DEFINED;
598 END IF;
599
600 -- ELSIF decode(p_default_code, null, l_csp_rec_update.default_code, p_default_code) IS NOT NULL AND NOT fnd_api.to_boolean(l_good_type)
601 -- AND NOT fnd_api.to_boolean(l_bad_type) THEN
602 -- l_msg_data := 'Warning: Default Code is not an "IN" or an "OUT" type. Please query the record and re-assign a valid Default Code.';
603 -- fnd_message.set_name(l_msg_data, 'API_ASSIGN_RESOURCE_INV_LOC');
604 -- fnd_message.set_token('ROUTINE', 'Assign_Resource_Inv_Loc');
605 -- fnd_msg_pub.ADD;
606 -- l_default_code := NULL;
607 ELSE NULL;
608 END IF;
609 END;
610 END IF;
611 */
612
613 /*
614 -- we also need to make sure the resource_type and resource_id are valid
615 BEGIN
616 SELECT resource_id INTO l_check_existence
617 FROM jtf_rs_all_resources_vl
618 WHERE resource_id = decode(p_resource_id, null, l_csp_rec_update.resource_id, p_resource_id)
619 AND resource_type = decode(p_resource_type, null, l_csp_rec_update.resource_type, p_resource_type);
620
621 EXCEPTION
622 WHEN NO_DATA_FOUND THEN
623 l_msg_data := 'Resource ID or Resource Type is invalid.';
624 RAISE EXCP_USER_DEFINED;
625 WHEN TOO_MANY_ROWS THEN
626 l_msg_data := 'Duplicate Resource ID found. There may be an error in your Resource table.';
627 RAISE EXCP_USER_DEFINED;
628 WHEN OTHERS THEN
629 l_msg_data := 'Unexpected errors found while validating the Resource ID. Please contact your system administrator.';
630 RAISE EXCP_USER_DEFINED;
631 END;
632 */
633 NULL;
634 WHEN TOO_MANY_ROWS THEN
635 fnd_message.set_name('CSP', 'CSP_DUPLICATE_RECORD');
636 fnd_msg_pub.add;
637 RAISE EXCP_USER_DEFINED;
638 WHEN EXCP_USER_DEFINED THEN
639 RAISE EXCP_USER_DEFINED;
640 WHEN OTHERS THEN
641 -- l_msg_data := SQLERRM(SQLCODE);
642 --l_msg_data := l_msg_data||'. This update operation is not allowed because updating this record creates a duplicate record with an existing one.';
643 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
644 fnd_message.set_token('ERR_FIELD', 'px_csp_inv_loc_assignment_id', TRUE);
645 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
646 fnd_message.set_token('TABLE', 'csp_inv_loc_assignments', TRUE);
647 fnd_msg_pub.ADD;
648 RAISE EXCP_USER_DEFINED;
649 END;
650
651 -- call the update procedure
652 IF l_csp_rec.last_update_date IS NULL THEN
653 l_csp_rec.last_update_date := sysdate;
654 END IF;
655 IF l_csp_rec.creation_date IS NULL THEN
656 BEGIN
657 SELECT creation_date INTO l_csp_rec.creation_date
658 FROM csp_inv_loc_assignments
659 WHERE csp_inv_loc_assignment_id = l_csp_rec.csp_inv_loc_assignment_id;
660 END;
661 END IF;
662 CSP_RESOURCE_PVT.Update_resource(
663 P_Api_Version_Number => p_api_version_number,
664 P_Init_Msg_List => FND_API.G_TRUE,
665 P_Commit => FND_API.G_FALSE,
666 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
667 P_CSP_Rec => l_csp_rec,
668 X_Return_Status => l_return_status,
669 X_Msg_Count => l_msg_count,
670 X_Msg_Data => l_msg_data
671 );
672 ELSE
673 -- call the delete procedure
674 CSP_RESOURCE_PVT.Delete_resource(
675 P_Api_Version_Number => p_api_version_number,
676 P_Init_Msg_List => FND_API.G_TRUE,
677 P_Commit => FND_API.G_FALSE,
678 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
679 P_CSP_Rec => l_csp_rec,
680 X_Return_Status => l_return_status,
681 X_Msg_Count => l_msg_count,
682 X_Msg_Data => l_msg_data
683 );
684 END IF;
685 END IF;
686 ELSE
687 -- l_msg_data := 'p_action_code must be 0, 1, or 2.';
688 fnd_message.set_name('INV', 'INV-INVALID ACTION');
689 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
690 fnd_msg_pub.add;
691 RAISE EXCP_USER_DEFINED;
692 END IF;
693
694 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
695 RAISE FND_API.G_EXC_ERROR;
696 ELSE
697 x_return_status := fnd_api.g_ret_sts_success;
698 px_CSP_INV_LOC_ASSIGNMENT_ID := l_assignment_id;
699 IF fnd_api.to_boolean(p_commit) THEN
700 commit work;
701 END IF;
702 END IF;
703
704 EXCEPTION
705 WHEN EXCP_USER_DEFINED THEN
706
707 fnd_msg_pub.count_and_get
708 ( p_count => x_msg_count
709 , p_data => x_msg_data);
710 x_return_status := FND_API.G_RET_STS_ERROR;
711
712 WHEN FND_API.G_EXC_ERROR THEN
713
714 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
715 P_API_NAME => L_API_NAME
716 ,P_PKG_NAME => G_PKG_NAME
717 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
718 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
719 ,X_MSG_COUNT => X_MSG_COUNT
720 ,X_MSG_DATA => X_MSG_DATA
721 ,X_RETURN_STATUS => X_RETURN_STATUS);
722
723 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
724
725 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
726 P_API_NAME => L_API_NAME
727 ,P_PKG_NAME => G_PKG_NAME
728 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
729 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
730 ,X_MSG_COUNT => X_MSG_COUNT
731 ,X_MSG_DATA => X_MSG_DATA
732 ,X_RETURN_STATUS => X_RETURN_STATUS);
733
734 WHEN OTHERS THEN
735
736 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
737 P_API_NAME => L_API_NAME
738 ,P_PKG_NAME => G_PKG_NAME
739 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
740 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
741 ,X_MSG_COUNT => X_MSG_COUNT
742 ,X_MSG_DATA => X_MSG_DATA
743 ,X_RETURN_STATUS => X_RETURN_STATUS);
744
745 END ASSIGN_RESOURCE_INV_LOC;
746
747
748 PROCEDURE Validate_Assignment_Record (
749 -- Start of Comments
750 -- Procedure : Validate_Assignment_Record
751 -- Purpose : Thie procedure validates whether an assignment record already exists for a resource.
752 -- A resource is allowed to be assigned to different subinventory but duplicate record
753 -- is not allowed.
754 -- History :
755 -- 11-Dev-1999, modified to include the case where p_default_code is null.
756 -- 15-Dec-1999, created by vernon.
757 --
758 -- Note : If the record already exists, x_return_status is set to fnd_api.g_true, if not to fnd_api.g_false.
759 --End of Comments
760
761 p_resource_id IN NUMBER
762 ,p_resource_type IN VARCHAR2
763 ,p_organization_id IN NUMBER
764 ,p_subinventory_code IN VARCHAR2
765 ,p_default_code IN VARCHAR2
766 ,x_return_status OUT NOCOPY VARCHAR2)
767 IS
768 l_result NUMBER := -1;
769
770 BEGIN
771 IF p_default_code IS NULL THEN
772 SELECT csp_inv_loc_assignment_id into l_result
773 FROM csp_inv_loc_assignments
774 WHERE resource_id = p_resource_id
775 AND resource_type = p_resource_type
776 AND organization_id = p_organization_id
777 AND subinventory_code = p_subinventory_code;
778 -- AND default_code IS NULL;
779 ELSE
780 SELECT csp_inv_loc_assignment_id into l_result
781 FROM csp_inv_loc_assignments
782 WHERE resource_id = p_resource_id
783 AND resource_type = p_resource_type
784 AND organization_id = p_organization_id
785 AND subinventory_code = p_subinventory_code;
786 -- AND default_code = p_default_code;
787 END IF;
788
789
790 -- If the no_data_found exception was not thrown by the above statement, the record should already exits.
791 x_return_status := FND_API.G_TRUE;
792
793 EXCEPTION
794 WHEN NO_DATA_FOUND THEN
795 x_return_status := FND_API.G_FALSE;
796 WHEN OTHERS THEN
797 -- any other exceptions besides no_data_found should be considered invalid situations.
798 x_return_status := FND_API.G_TRUE;
799
800 END Validate_Assignment_Record;
801
802 End CSP_RESOURCE_PUB;
[Home] [Help]