[Home] [Help]
PACKAGE BODY: APPS.CSI_ASSET_VLD_PVT
Source
1 PACKAGE BODY CSI_Asset_vld_pvt AS
2 /* $Header: csivavb.pls 115.21 2003/09/04 00:39:31 sguthiva ship $ */
3
4 /*-----------------------------------------------------------*/
5 /* Procedure name: Check_Reqd_Param */
6 /* Description : To Check if the reqd parameter is passed */
7 /*-----------------------------------------------------------*/
8
9 PROCEDURE Check_Reqd_Param
10 (
11 p_number IN NUMBER,
12 p_param_name IN VARCHAR2,
13 p_api_name IN VARCHAR2
14 ) IS
15 BEGIN
16 IF (NVL(p_number,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) THEN
17 FND_MESSAGE.SET_NAME('CSI','CSI_API_REQD_PARAM_MISSING');
18 FND_MESSAGE.SET_TOKEN('API_NAME',p_api_name);
19 FND_MESSAGE.SET_TOKEN('MISSING_PARAM',p_param_name);
20 FND_MSG_PUB.Add;
21 RAISE FND_API.G_EXC_ERROR;
22 END IF;
23 END Check_Reqd_Param;
24
25 /*-----------------------------------------------------------*/
26 /* Procedure name: Check_Reqd_Param */
27 /* Description : To Check if the reqd parameter is passed */
28 /*-----------------------------------------------------------*/
29
30 PROCEDURE Check_Reqd_Param
31 (
32 p_variable IN VARCHAR2,
33 p_param_name IN VARCHAR2,
34 p_api_name IN VARCHAR2
35 ) IS
36 BEGIN
37 IF (NVL(p_variable,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR) THEN
38 FND_MESSAGE.SET_NAME('CSI','CSI_API_REQD_PARAM_MISSING');
39 FND_MESSAGE.SET_TOKEN('API_NAME',p_api_name);
40 FND_MESSAGE.SET_TOKEN('MISSING_PARAM',p_param_name);
41 FND_MSG_PUB.Add;
42 RAISE FND_API.G_EXC_ERROR;
43 END IF;
44 END Check_Reqd_Param;
45
46 /*-----------------------------------------------------------*/
47 /* Procedure name: Check_Reqd_Param */
48 /* Description : To Check if the reqd parameter is passed */
49 /*-----------------------------------------------------------*/
50
51 PROCEDURE Check_Reqd_Param
52 (
53 p_date IN DATE,
54 p_param_name IN VARCHAR2,
55 p_api_name IN VARCHAR2
56 ) IS
57 BEGIN
58 IF (NVL(p_date,FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE) THEN
59 FND_MESSAGE.SET_NAME('CSI','CSI_API_REQD_PARAM_MISSING');
60 FND_MESSAGE.SET_TOKEN('API_NAME',p_api_name);
61 FND_MESSAGE.SET_TOKEN('MISSING_PARAM',p_param_name);
62 FND_MSG_PUB.Add;
63 RAISE FND_API.G_EXC_ERROR;
64 END IF;
65 END Check_Reqd_Param;
66
67 /*-----------------------------------------------------------*/
68 /* Procedure name: Is_InstanceID_Valid */
69 /* Description : Check if the Instance Id exists */
70 /*-----------------------------------------------------------*/
71
72 FUNCTION Is_InstanceID_Valid
73 ( p_instance_id IN NUMBER,
74 p_check_for_instance_expiry IN VARCHAR2,
75 p_stack_err_msg IN BOOLEAN
76 ) RETURN BOOLEAN IS
77
78 l_dummy VARCHAR2(1);
79 l_return_value BOOLEAN := TRUE;
80
81 CURSOR c1 IS
82 SELECT 'x'
83 FROM csi_item_instances
84 WHERE instance_id = p_instance_id
85 and ((active_end_date is null) OR (active_end_date >= sysdate));
86
87 CURSOR c2 IS
88 SELECT 'x'
89 FROM csi_item_instances
90 WHERE instance_id = p_instance_id;
91 BEGIN
92 IF p_check_for_instance_expiry = fnd_api.g_true THEN
93 OPEN c1;
94 FETCH c1 INTO l_dummy;
95 IF c1%NOTFOUND THEN
99 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id);
96 l_return_value := FALSE;
97 IF ( p_stack_err_msg = TRUE ) THEN
98 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INSTANCE_ID');
100 FND_MSG_PUB.Add;
101 END IF;
102 END IF;
103 CLOSE c1;
104 ELSE
105 OPEN c2;
106 FETCH c2 INTO l_dummy;
107 IF c2%NOTFOUND THEN
108 l_return_value := FALSE;
109 IF ( p_stack_err_msg = TRUE ) THEN
110 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INSTANCE_ID');
111 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id);
112 FND_MSG_PUB.Add;
113 END IF;
114 END IF;
115 CLOSE c2;
116 END IF;
117
118 RETURN l_return_value;
119
120 END Is_InstanceID_Valid;
121
122
123 /*-----------------------------------------------------------*/
124 /* Procedure name: gen_inst_asset_id */
125 /* Description : Generate instance asset id from */
126 /* the sequence */
127 /*-----------------------------------------------------------*/
128
129 FUNCTION gen_inst_asset_id
130 RETURN NUMBER IS
131
132 l_inst_asset_id NUMBER;
133
134 BEGIN
135 SELECT CSI_I_ASSETS_S.nextval
136 INTO l_inst_asset_id
137 FROM sys.dual;
138
139 RETURN l_inst_asset_id;
140
141 END gen_inst_asset_id;
142
143 /*-----------------------------------------------------------*/
144 /* Procedure name: Is_Inst_assetID_exists */
145 /* Description : Check if the instance asset id */
146 /* exists in csi_i_assets */
147 /*-----------------------------------------------------------*/
148
149 FUNCTION Is_Inst_assetID_exists
150
151 ( p_instance_asset_id IN NUMBER,
152 p_stack_err_msg IN BOOLEAN
153 ) RETURN BOOLEAN IS
154
155 l_dummy VARCHAR2(1);
156 l_return_value BOOLEAN := TRUE;
157 BEGIN
158 SELECT 'x'
159 INTO l_dummy
160 FROM csi_i_assets
161 WHERE instance_asset_id = p_instance_asset_id ;
162
163 IF ( p_stack_err_msg = TRUE ) THEN
164 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_INST_ASSET_ID');
165 FND_MESSAGE.SET_TOKEN('INSTANCE_ASSET_ID',p_instance_asset_id);
166 FND_MSG_PUB.Add;
167 END IF;
168 RETURN l_return_value;
169 EXCEPTION
170 WHEN NO_DATA_FOUND THEN
171 l_return_value := FALSE;
172 RETURN l_return_value;
173 END Is_Inst_assetID_exists;
174
175 /*-----------------------------------------------------------*/
176 /* Procedure name: Is_Inst_asset_id_valid */
177 /* Description : Check if the instance asset id */
178 /* exists in csi_i_assets */
179 /*-----------------------------------------------------------*/
180
181 FUNCTION Is_Inst_asset_id_valid
182
183 ( p_instance_asset_id IN NUMBER,
184 p_stack_err_msg IN BOOLEAN
185 ) RETURN BOOLEAN IS
186
187 l_dummy VARCHAR2(1);
188 l_return_value BOOLEAN := TRUE;
189 BEGIN
190 SELECT 'x'
191 INTO l_dummy
192 FROM csi_i_assets
193 WHERE instance_asset_id = p_instance_asset_id ;
194 RETURN l_return_value;
195 EXCEPTION
196 WHEN NO_DATA_FOUND THEN
197 l_return_value := FALSE;
198 IF ( p_stack_err_msg = TRUE ) THEN
199 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_INST_ASSET_ID');
200 FND_MESSAGE.SET_TOKEN('INSTANCE_ASSET_ID',p_instance_asset_id);
201 FND_MSG_PUB.Add;
202 END IF;
203 RETURN l_return_value;
204 END Is_Inst_asset_id_valid;
205
206
207 /*-----------------------------------------------------------*/
208 /* Procedure name: Is_Update_Status_Exists */
209 /* Description : Check if the update status is */
210 /* defined in CSI_LOOKUPS */
211 /*-----------------------------------------------------------*/
212
213 FUNCTION Is_Update_Status_Exists
214 (
215 p_update_status IN VARCHAR2,
216 p_stack_err_msg IN BOOLEAN
217 ) RETURN BOOLEAN IS
218
219 l_dummy VARCHAR2(1);
220 l_return_value BOOLEAN := TRUE;
221 l_asset_lookup_type VARCHAR2(30) := 'CSI_ASSET_UPDATE_STATUS_CODE';
222
223 CURSOR c1 IS
224 SELECT 'x'
225 FROM CSI_LOOKUPS
226 WHERE UPPER(lookup_code) = UPPER(p_update_status)
227 AND lookup_type = l_asset_lookup_type;
228 BEGIN
229 OPEN c1;
230 FETCH c1 INTO l_dummy;
231 IF c1%NOTFOUND THEN
232 l_return_value := FALSE;
233 IF ( p_stack_err_msg = TRUE ) THEN
234 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_UPDATE_STATUS');
235 FND_MESSAGE.SET_TOKEN('UPDATE_STATUS',p_update_status);
236 FND_MSG_PUB.Add;
237 END IF;
238 END IF;
239 CLOSE c1;
240 RETURN l_return_value;
241
242 END Is_Update_Status_Exists;
243
244 /*-----------------------------------------------------------*/
245 /* Procedure name: Is_Quantity_Valid */
246 /* Description : Check if the asset quantity > 0 */
247 /*-----------------------------------------------------------*/
248
249 FUNCTION Is_Quantity_Valid
250 (
251 p_asset_quantity IN NUMBER,
252 p_stack_err_msg IN BOOLEAN
253 ) RETURN BOOLEAN IS
254
258 l_return_status := FALSE;
255 l_return_status BOOLEAN := TRUE;
256 BEGIN
257 IF (NVL(p_asset_quantity,-1) <= 0 ) THEN
259 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ASSET_QTY');
260 FND_MESSAGE.SET_TOKEN('QUANTITY',p_asset_quantity);
261 FND_MSG_PUB.Add;
262 END IF;
263
264 RETURN l_return_status;
265
266 END Is_Quantity_Valid;
267
268
269 /*-----------------------------------------------------------*/
270 /* Procedure name: gen_inst_asset_hist_id */
271 /* Description : Generate instance asset id from */
272 /* the sequence */
273 /*-----------------------------------------------------------*/
274
275 FUNCTION gen_inst_asset_hist_id
276 RETURN NUMBER IS
277
278 l_inst_asset_hist_id NUMBER;
279
280 BEGIN
281 SELECT CSI_I_ASSETS_H_S.nextval
282 INTO l_inst_asset_hist_id
283 FROM sys.dual;
284 RETURN l_inst_asset_hist_id;
285 END gen_inst_asset_hist_id;
286
287 /*-----------------------------------------------------------*/
288 /* Procedure name: Is_Asset_Comb_Valid */
289 /* Description : Check if the instance asset id and location */
290 /* id exists in fa_books */
291 /*-----------------------------------------------------------*/
292
293 FUNCTION Is_Asset_Comb_Valid
294 ( p_asset_id IN NUMBER,
295 p_book_type_code IN VARCHAR2,
296 p_stack_err_msg IN BOOLEAN
297 ) RETURN BOOLEAN IS
298 l_dummy VARCHAR2(1);
299 l_return_value BOOLEAN := TRUE;
300
301 CURSOR C1 IS
302 SELECT 'x'
303 FROM fa_books
304 WHERE asset_id = p_asset_id
305 and book_type_code = p_book_type_code
306 and rownum = 1 ;
307
308 BEGIN
309 OPEN C1;
310 FETCH C1 INTO l_dummy;
311 IF C1%NOTFOUND THEN
312 l_return_value := FALSE;
313 IF ( p_stack_err_msg = TRUE ) THEN
314 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_ASSET_COMB');
315 FND_MESSAGE.SET_TOKEN('ASSET_COMBINATION',p_asset_id||'-'||p_book_type_code);
316 FND_MSG_PUB.Add;
317 END IF;
318 END IF;
319 CLOSE C1;
320 RETURN l_return_value;
321 END Is_Asset_Comb_Valid;
322
323
324 /*-----------------------------------------------------------*/
325 /* Procedure name: Is_Asset_Location_Valid */
326 /* Description : Check if the instance location id */
327 /* exists in csi_a_locations */
328 /*-----------------------------------------------------------*/
329
330 FUNCTION Is_Asset_Location_Valid
331 ( p_location_id IN NUMBER,
332 p_stack_err_msg IN BOOLEAN
333 ) RETURN BOOLEAN IS
334 l_dummy VARCHAR2(1);
335 l_return_value BOOLEAN := TRUE;
336 BEGIN
337 SELECT 'x'
338 INTO l_dummy
339 FROM csi_a_locations
340 WHERE fa_location_id = p_location_id
341 and ((active_end_date is null) OR (active_end_date >= sysdate))
342 and ROWNUM = 1;
343
344 RETURN l_return_value;
345 EXCEPTION
346 WHEN NO_DATA_FOUND THEN
347 l_return_value := FALSE;
348 IF ( p_stack_err_msg = TRUE ) THEN
349 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_ASSET_LOCATION');
350 FND_MESSAGE.SET_TOKEN('ASSET_LOCATION_ID',p_location_id);
351 FND_MSG_PUB.Add;
352 END IF;
353 RETURN l_return_value;
354 END Is_Asset_Location_Valid;
355
356 /*-----------------------------------------------------------*/
357 /* Procedure name: Is_StartDate_Valid */
358 /* Description : Check if instance assets active start */
359 /* date is valid */
360 /*-----------------------------------------------------------*/
361
362 FUNCTION Is_StartDate_Valid
363 ( p_start_date IN DATE,
364 p_end_date IN DATE,
365 p_instance_id IN NUMBER,
366 p_check_for_instance_expiry IN VARCHAR2, -- Added for cse on 14-feb-03
367 p_stack_err_msg IN BOOLEAN
368 ) RETURN BOOLEAN IS
369
370 l_instance_start_date DATE;
371 l_instance_end_date DATE;
372 l_return_value BOOLEAN := TRUE;
373
374 CURSOR c1 IS
375 SELECT active_start_date,
376 active_end_date
377 FROM csi_item_instances
378 WHERE instance_id = p_instance_id
379 AND ((active_end_date IS NULL) OR (active_end_date >= SYSDATE));
380 BEGIN
381 IF ((p_end_date IS NOT NULL) AND (p_end_date <> FND_API.G_MISS_DATE))THEN
382 IF p_start_date > p_end_date THEN
383 l_return_value := FALSE;
384 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVAL_AST_START_DATE');
385 FND_MESSAGE.SET_TOKEN('START_DATE',p_start_date);
386 FND_MSG_PUB.Add;
387 RETURN l_return_value;
388 END IF;
389 END IF;
390
391 OPEN c1;
392 FETCH c1 INTO l_instance_start_date,l_instance_end_date;
393 IF p_check_for_instance_expiry = fnd_api.g_true -- Added for cse on 14-feb-03
394 THEN
395 IF c1%NOTFOUND THEN
396 l_return_value := FALSE;
397 IF ( p_stack_err_msg = TRUE ) THEN
398 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVAL_INST_START_DATE');
399 FND_MESSAGE.SET_TOKEN('ENTITY','ASSET');
400 FND_MSG_PUB.Add;
401 END IF;
402 RETURN l_return_value;
403 END IF;
404 END IF;
405 CLOSE c1;
406
407 IF (p_start_date < l_instance_start_date) AND (l_instance_start_date IS NOT NULL) THEN
408 l_return_value := FALSE;
409 IF ( p_stack_err_msg = TRUE ) THEN
410 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVAL_AST_START_DATE');
411 FND_MESSAGE.SET_TOKEN('START_DATE',p_start_date);
412 FND_MSG_PUB.Add;
413 END IF;
414 END IF;
415
416 IF ((l_instance_end_date IS NOT NULL) AND (p_start_date > l_instance_end_date)) THEN
417 IF p_check_for_instance_expiry = fnd_api.g_true -- Added for cse on 14-feb-03
418 THEN
419 l_return_value := FALSE;
420 IF ( p_stack_err_msg = TRUE ) THEN
421 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVAL_AST_START_DATE');
422 FND_MESSAGE.SET_TOKEN('START_DATE',p_start_date);
423 FND_MSG_PUB.Add;
424 END IF;
425 END IF;
426 END IF;
427 RETURN l_return_value;
428 END Is_StartDate_Valid;
429
430 /*----------------------------------------------------------*/
431 /* Function Name : Is_EndDate_Valid */
432 /* */
433 /* Description : This function checks if end date */
434 /* is valid */
435 /*----------------------------------------------------------*/
436
437 FUNCTION Is_EndDate_Valid
438 (
439 p_start_date IN DATE,
440 p_end_date IN DATE,
441 p_instance_id IN NUMBER,
442 p_inst_asset_id IN NUMBER,
443 p_txn_id IN NUMBER,
444 p_check_for_instance_expiry IN VARCHAR2, -- Added for cse on 14-feb-03
445 p_stack_err_msg IN BOOLEAN
446 ) RETURN BOOLEAN IS
447
448 l_return_value BOOLEAN := TRUE;
449 l_transaction_date date;
450
451 CURSOR c1 IS
452 SELECT active_start_date,
453 active_end_date
454 FROM csi_item_instances
455 WHERE instance_id = p_instance_id;
456
457 l_date_rec c1%ROWTYPE;
458
459 BEGIN
460
461 IF ((p_inst_asset_id IS NULL) OR (p_inst_asset_id = FND_API.G_MISS_NUM)) THEN
462 IF ((p_end_date IS NOT NULL) AND (p_end_date <> FND_API.G_MISS_DATE)) THEN
463
464 IF p_end_date < SYSDATE THEN
465 l_return_value := FALSE;
466 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVAL_AST_END_DATE');
467 FND_MESSAGE.SET_TOKEN('END_DATE',p_end_date);
468 FND_MSG_PUB.Add;
469 l_return_value := FALSE;
470 RETURN l_return_value;
471 END IF;
472 END IF;
473 RETURN l_return_value;
474 ELSE
475 IF p_end_date < SYSDATE THEN
476 SELECT MAX(t.transaction_date)
477 INTO l_transaction_date
478 FROM csi_i_assets_h s,
479 csi_transactions t
480 WHERE s.instance_asset_id=p_inst_asset_id
481 AND s.transaction_id=t.transaction_id
482 AND t.transaction_id <> nvl(p_txn_id, -999999);
483
484 IF l_transaction_date > p_end_date
485 THEN
486 fnd_message.set_name('CSI','CSI_HAS_TXNS');
487 fnd_message.set_token('END_DATE_ACTIVE',p_end_date);
488 fnd_msg_pub.add;
489 l_return_value := FALSE;
490 RETURN l_return_value;
491 END IF;
492 END IF;
493 IF ((p_end_date IS NOT NULL) AND (p_end_date <> FND_API.G_MISS_DATE)) then
494 OPEN c1;
495 FETCH c1 INTO l_date_rec;
496
497 IF (p_end_date > NVL(l_date_rec.active_end_date, p_end_date))
498 THEN
499 IF p_check_for_instance_expiry = fnd_api.g_true -- Added for cse on 14-feb-03
500 THEN
501 l_return_value := FALSE;
502 IF ( p_stack_err_msg = TRUE ) THEN
503 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVAL_AST_END_DATE');
504 FND_MESSAGE.SET_TOKEN('END_DATE',p_end_date);
505 FND_MSG_PUB.Add;
506 END IF;
507 RETURN l_return_value;
508 END IF;
509 END IF;
510
511 IF (p_end_date < NVL(l_date_rec.active_start_date,p_end_date))
512 THEN
513 l_return_value := FALSE;
514 IF ( p_stack_err_msg = TRUE ) THEN
515 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVAL_AST_END_DATE');
516 FND_MESSAGE.SET_TOKEN('END_DATE',p_end_date);
517 FND_MSG_PUB.Add;
518 END IF;
519 RETURN l_return_value;
520 END IF;
521 CLOSE c1;
522 END IF;
523 END IF;
524 RETURN l_return_value;
525 END Is_EndDate_Valid;
526
527 END CSI_Asset_vld_pvt ;
528