[Home] [Help]
PACKAGE BODY: APPS.EAM_CONSTRUCTION_UNIT_PVT
Source
1 PACKAGE BODY EAM_CONSTRUCTION_UNIT_PVT as
2 /* $Header: EAMVCUB.pls 120.0.12010000.5 2008/12/15 08:13:55 dsingire noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_CONSTRUCTION_UNIT_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'EAMVCUB.pls';
6
7 --Procedures and functions used for debug purpose
8 PROCEDURE debug(p_message IN varchar2) IS
9 BEGIN
10 IF get_debug = 'Y' THEN
11 EAM_ERROR_MESSAGE_PVT.Write_Debug(p_message);
12 END IF;
13 EXCEPTION
14 WHEN others THEN
15 null;
16 END debug;
17
18 PROCEDURE set_debug
19 IS
20 BEGIN
21 g_debug_flag := NVL(fnd_profile.value('EAM_DEBUG'), 'N');
22 END Set_Debug;
23
24 FUNCTION get_debug RETURN VARCHAR2
25 IS
26 BEGIN
27 RETURN g_debug_flag;
28 END;
29
30
31 PROCEDURE create_construction_unit(
32 p_api_version IN NUMBER
33 ,p_commit IN VARCHAR2
34 ,p_cu_rec IN EAM_CONSTRUCTION_UNIT_PUB.CU_rec
35 ,p_cu_activity_tbl IN EAM_CONSTRUCTION_UNIT_PUB.CU_Activity_tbl
36 ,x_cu_id OUT NOCOPY NUMBER
37 ,x_return_status OUT NOCOPY VARCHAR2
38 ,x_msg_count OUT NOCOPY NUMBER
39 ,x_msg_data OUT NOCOPY VARCHAR2
40
41 ) IS
42 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_CONSTRUCTION_UNIT';
43 l_api_version CONSTANT NUMBER := 1.0;
44 l_debug_level NUMBER := 0;
45 l_cu_rec EAM_CONSTRUCTION_UNIT_PUB.CU_rec;
46 l_cu_activity_tbl EAM_CONSTRUCTION_UNIT_PUB.CU_Activity_tbl;
47 l_cu_activity_rec EAM_CONSTRUCTION_UNIT_PUB.CU_Activity_rec;
48 l_time_stamp DATE := SYSDATE;
49 l_cu_id NUMBER;
50 l_cu_activity_id NUMBER;
51 l_activity_index NUMBER;
52 l_temp VARCHAR(10);
53 l_qtantity NUMBER;
54 l_multiplier NUMBER;
55 BEGIN
56 -- Standard Start of API savepoint
57 SAVEPOINT create_construction_unit;
58
59 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
60 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
61 END IF;
62
63 -- Initialize message list if p_init_msg_list is set to TRUE.
64 FND_MSG_PUB.initialize;
65
66
67 l_cu_rec := p_cu_rec;
68 l_cu_activity_tbl := p_cu_activity_tbl;
69 x_return_status := fnd_api.G_RET_STS_SUCCESS;
70
71 IF (l_debug_level > 0) THEN
72 debug('CREATE_CONSTRUCTION_UNIT');
73 END IF;
74
75 --If the debug level = 2 then dump all the parameters values.
76 IF (l_debug_level > 1) THEN
77 debug('CREATE_CONSTRUCTION_UNIT ' ||
78 p_api_version ||'-'||
79 p_commit);
80 debug('Dumping values of p_cu_rec');
81 debug('CU_ID : ' || p_cu_rec.CU_ID);
82 debug('CU_NAME : ' || p_cu_rec.CU_NAME);
83 debug('DESCRIPTION : ' || p_cu_rec.DESCRIPTION);
84 debug('ORGANIZATION_ID : ' || p_cu_rec.ORGANIZATION_ID);
85 debug('CU_EFFECTIVE_FROM : ' || p_cu_rec.CU_EFFECTIVE_FROM);
86 debug('CU_EFFECTIVE_TO : ' || p_cu_rec.CU_EFFECTIVE_TO);
87 debug('ATTRIBUTE_CATEGORY : ' || p_cu_rec.attribute_category);
88 debug('ATTRIBUTE1 : ' || p_cu_rec.ATTRIBUTE1);
89 debug('ATTRIBUTE2 : ' || p_cu_rec.ATTRIBUTE2);
90 debug('ATTRIBUTE3 : ' || p_cu_rec.ATTRIBUTE3);
91 debug('ATTRIBUTE4 : ' || p_cu_rec.ATTRIBUTE4);
92 debug('ATTRIBUTE5 : ' || p_cu_rec.ATTRIBUTE5);
93 debug('ATTRIBUTE6 : ' || p_cu_rec.ATTRIBUTE6);
94 debug('ATTRIBUTE7 : ' || p_cu_rec.ATTRIBUTE7);
95 debug('ATTRIBUTE8 : ' || p_cu_rec.ATTRIBUTE8);
96 debug('ATTRIBUTE9 : ' || p_cu_rec.ATTRIBUTE9);
97 debug('ATTRIBUTE10 : ' || p_cu_rec.ATTRIBUTE10);
98 debug('ATTRIBUTE11 : ' || p_cu_rec.ATTRIBUTE11);
99 debug('ATTRIBUTE12 : ' || p_cu_rec.ATTRIBUTE12);
100 debug('ATTRIBUTE13 : ' || p_cu_rec.ATTRIBUTE13);
101 debug('ATTRIBUTE14 : ' || p_cu_rec.ATTRIBUTE14);
102 debug('ATTRIBUTE15 : ' || p_cu_rec.ATTRIBUTE15);
103 END IF;
104
105 validate_cu_details(
106 p_api_version => p_api_version
107 ,p_commit => p_commit
108 ,p_cu_rec => p_cu_rec
109 ,p_cu_activity_tbl => p_cu_activity_tbl
110 ,p_action => 'CREATE'
111 ,x_return_status => x_return_status
112 ,x_msg_count => x_msg_count
113 ,x_msg_data => x_msg_data
114 );
115
116 --Check for x_return_status
117 IF x_return_status = fnd_api.g_ret_sts_error THEN
118 RAISE FND_API.G_EXC_ERROR;
119 END IF;
120
121 IF l_cu_rec.CU_ID IS NULL OR l_cu_rec.CU_ID = FND_API.G_MISS_NUM THEN
122 l_cu_id := NULL;
123 ELSE
124 l_cu_id := l_cu_rec.CU_ID;
125 END IF;
126
127 EAM_CONSTRUCTION_UNIT_PKG.Insert_CU_Row(
128 px_cu_id => l_cu_id
129 ,p_cu_name => l_cu_rec.CU_NAME
130 ,p_description => l_cu_rec.DESCRIPTION
131 ,p_organization_id => l_cu_rec.ORGANIZATION_ID
132 ,p_cu_effective_from => l_cu_rec.CU_EFFECTIVE_FROM
133 ,p_cu_effective_to => l_cu_rec.CU_EFFECTIVE_TO
134 ,p_attribute_category => l_cu_rec.attribute_category
135 ,p_attribute1 => l_cu_rec.attribute1
136 ,p_attribute2 => l_cu_rec.attribute2
137 ,p_attribute3 => l_cu_rec.attribute3
138 ,p_attribute4 => l_cu_rec.attribute4
139 ,p_attribute5 => l_cu_rec.attribute5
140 ,p_attribute6 => l_cu_rec.attribute6
141 ,p_attribute7 => l_cu_rec.attribute7
142 ,p_attribute8 => l_cu_rec.attribute8
143 ,p_attribute9 => l_cu_rec.attribute9
144 ,p_attribute10 => l_cu_rec.attribute10
145 ,p_attribute11 => l_cu_rec.attribute11
146 ,p_attribute12 => l_cu_rec.attribute12
147 ,p_attribute13 => l_cu_rec.attribute13
148 ,p_attribute14 => l_cu_rec.attribute14
149 ,p_attribute15 => l_cu_rec.attribute15
150 ,p_creation_date => SYSDATE
151 ,p_created_by => FND_GLOBAL.USER_ID
152 ,p_last_update_date => SYSDATE
153 ,p_last_updated_by => FND_GLOBAL.USER_ID
154 ,p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID
155 );
156
157 x_cu_id := l_cu_id;
158 FOR l_activity_index IN 1 .. l_cu_activity_tbl.Count LOOP
159 l_cu_activity_rec := NULL;
160 l_cu_activity_rec := l_cu_activity_tbl(l_activity_index);
161 l_cu_activity_id := NULL;
162
163 EAM_CONSTRUCTION_UNIT_PKG.Insert_CU_Activity_Row(
164 px_cu_detail_id => l_cu_activity_id
165 ,p_cu_id => l_cu_id
166 ,p_acct_class_code => l_cu_activity_rec.ACCT_CLASS_CODE
167 ,p_activity_id => l_cu_activity_rec.ACTIVITY_ID
168 ,p_cu_activity_qty => l_cu_activity_rec.CU_ACTIVITY_QTY
169 ,p_cu_activity_effective_from => l_cu_activity_rec.CU_ACTIVITY_EFFECTIVE_FROM
170 ,p_cu_activity_effective_to => l_cu_activity_rec.CU_ACTIVITY_EFFECTIVE_TO
171 ,p_creation_date => SYSDATE
172 ,p_created_by => FND_GLOBAL.USER_ID
173 ,p_last_update_date => SYSDATE
174 ,p_last_updated_by => FND_GLOBAL.USER_ID
175 ,p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID
176 );
177
178 END LOOP;
179 -- Standard check of p_commit.
180 IF FND_API.To_Boolean( p_commit ) THEN
181 COMMIT WORK;
182 END IF;
183
184 EXCEPTION
185 WHEN FND_API.G_EXC_ERROR THEN
186 ROLLBACK TO create_construction_unit;
187 RETURN;
188 WHEN OTHERS THEN
189 ROLLBACK TO create_construction_unit;
190 x_return_status := fnd_api.g_ret_sts_error;
191 fnd_message.set_name('EAM','EAM_CU_UNEXP_SQL_ERROR');
192 fnd_message.set_token('API_NAME',l_api_name);
193 fnd_message.set_token('SQL_ERROR',SQLERRM);
194 FND_MSG_PUB.Add;
195 x_msg_data := fnd_message.get;
196 END create_construction_unit;
197
198 PROCEDURE update_construction_unit(
199 p_api_version IN NUMBER
200 ,p_commit IN VARCHAR2
201 ,p_cu_rec IN EAM_CONSTRUCTION_UNIT_PUB.CU_rec
202 ,p_cu_activity_tbl IN EAM_CONSTRUCTION_UNIT_PUB.CU_Activity_tbl
203 ,x_cu_id OUT NOCOPY NUMBER
204 ,x_return_status OUT NOCOPY VARCHAR2
205 ,x_msg_count OUT NOCOPY NUMBER
206 ,x_msg_data OUT NOCOPY VARCHAR2
207
208 ) IS
209 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONSTRUCTION_UNIT';
210 l_api_version CONSTANT NUMBER := 1.0;
211 l_debug_level NUMBER := 0;
212 l_cu_rec EAM_CONSTRUCTION_UNIT_PUB.CU_rec;
213 l_cu_activity_tbl EAM_CONSTRUCTION_UNIT_PUB.CU_Activity_tbl;
214 l_cu_activity_rec EAM_CONSTRUCTION_UNIT_PUB.CU_Activity_rec;
215 l_time_stamp DATE := SYSDATE;
216 l_cu_id NUMBER;
217 l_cu_activity_id NUMBER;
218 l_activity_index NUMBER;
219 l_temp VARCHAR(10);
220 l_qtantity NUMBER;
221 l_multiplier NUMBER;
222 BEGIN
223 -- Standard Start of API savepoint
224 SAVEPOINT update_construction_unit;
225
226 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
228 END IF;
229
230 l_cu_rec := p_cu_rec;
231 l_cu_activity_tbl := p_cu_activity_tbl;
232 x_return_status := fnd_api.G_RET_STS_SUCCESS;
233
234 -- Initialize message list if p_init_msg_list is set to TRUE.
235 FND_MSG_PUB.initialize;
236
237 IF (l_debug_level > 0) THEN
238 debug('UPDATE_CONSTRUCTION_UNIT');
239 END IF;
240
241 --If the debug level = 2 then dump all the parameters values.
242 IF (l_debug_level > 1) THEN
243 debug('UPDATE_CONSTRUCTION_UNIT' ||
244 To_Char(p_api_version) ||'-'||
245 p_commit);
246 debug('Dumping values of p_cu_rec');
247 debug('CU_ID : ' || p_cu_rec.CU_ID);
248 debug('CU_NAME : ' || p_cu_rec.CU_NAME);
249 debug('DESCRIPTION : ' || p_cu_rec.DESCRIPTION);
250 debug('ORGANIZATION_ID : ' || p_cu_rec.ORGANIZATION_ID);
251 debug('CU_EFFECTIVE_FROM : ' || p_cu_rec.CU_EFFECTIVE_FROM);
252 debug('CU_EFFECTIVE_TO : ' || p_cu_rec.CU_EFFECTIVE_TO);
253 debug('ATTRIBUTE_CATEGORY : ' || p_cu_rec.attribute_category);
254 debug('ATTRIBUTE1 : ' || p_cu_rec.ATTRIBUTE1);
255 debug('ATTRIBUTE2 : ' || p_cu_rec.ATTRIBUTE2);
256 debug('ATTRIBUTE3 : ' || p_cu_rec.ATTRIBUTE3);
257 debug('ATTRIBUTE4 : ' || p_cu_rec.ATTRIBUTE4);
258 debug('ATTRIBUTE5 : ' || p_cu_rec.ATTRIBUTE5);
259 debug('ATTRIBUTE6 : ' || p_cu_rec.ATTRIBUTE6);
260 debug('ATTRIBUTE7 : ' || p_cu_rec.ATTRIBUTE7);
261 debug('ATTRIBUTE8 : ' || p_cu_rec.ATTRIBUTE8);
262 debug('ATTRIBUTE9 : ' || p_cu_rec.ATTRIBUTE9);
263 debug('ATTRIBUTE10 : ' || p_cu_rec.ATTRIBUTE10);
264 debug('ATTRIBUTE11 : ' || p_cu_rec.ATTRIBUTE11);
265 debug('ATTRIBUTE12 : ' || p_cu_rec.ATTRIBUTE12);
266 debug('ATTRIBUTE13 : ' || p_cu_rec.ATTRIBUTE13);
267 debug('ATTRIBUTE14 : ' || p_cu_rec.ATTRIBUTE14);
268 debug('ATTRIBUTE15 : ' || p_cu_rec.ATTRIBUTE15);
269 END IF;
270
271 validate_cu_details(
272 p_api_version => p_api_version
273 ,p_commit => p_commit
274 ,p_cu_rec => p_cu_rec
275 ,p_cu_activity_tbl => p_cu_activity_tbl
276 ,p_action => 'UPDATE'
277 ,x_return_status => x_return_status
278 ,x_msg_count => x_msg_count
279 ,x_msg_data => x_msg_data
280 );
281
282 --Check for x_return_status
283 IF x_return_status = fnd_api.g_ret_sts_error THEN
284 RAISE FND_API.G_EXC_ERROR;
285 END IF;
286
287 IF l_cu_rec.CU_ID IS NULL OR l_cu_rec.CU_ID = FND_API.G_MISS_NUM THEN
288 FND_MESSAGE.SET_NAME('EAM','EAM_CU_INVALID_CUID');
289 FND_MSG_PUB.Add;
290 RAISE FND_API.G_EXC_ERROR;
291 ELSE
292 l_cu_id := l_cu_rec.CU_ID;
293 END IF;
294
295 EAM_CONSTRUCTION_UNIT_PKG.Update_CU_Row(
296 p_cu_id => l_cu_id
297 ,p_cu_name => l_cu_rec.CU_NAME
298 ,p_description => l_cu_rec.DESCRIPTION
299 ,p_organization_id => l_cu_rec.ORGANIZATION_ID
300 ,p_cu_effective_from => l_cu_rec.CU_EFFECTIVE_FROM
301 ,p_cu_effective_to => l_cu_rec.CU_EFFECTIVE_TO
302 ,p_attribute_category => l_cu_rec.attribute_category
303 ,p_attribute1 => l_cu_rec.attribute1
304 ,p_attribute2 => l_cu_rec.attribute2
305 ,p_attribute3 => l_cu_rec.attribute3
306 ,p_attribute4 => l_cu_rec.attribute4
307 ,p_attribute5 => l_cu_rec.attribute5
308 ,p_attribute6 => l_cu_rec.attribute6
309 ,p_attribute7 => l_cu_rec.attribute7
310 ,p_attribute8 => l_cu_rec.attribute8
311 ,p_attribute9 => l_cu_rec.attribute9
312 ,p_attribute10 => l_cu_rec.attribute10
313 ,p_attribute11 => l_cu_rec.attribute11
314 ,p_attribute12 => l_cu_rec.attribute12
315 ,p_attribute13 => l_cu_rec.attribute13
316 ,p_attribute14 => l_cu_rec.attribute14
317 ,p_attribute15 => l_cu_rec.attribute15
318 ,p_last_update_date => SYSDATE
319 ,p_last_updated_by => FND_GLOBAL.USER_ID
320 ,p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID
321 );
322
323 x_cu_id := l_cu_id;
324
325 FOR l_activity_index IN 1 .. l_cu_activity_tbl.Count LOOP
326 l_cu_activity_rec := NULL;
327 l_cu_activity_rec := l_cu_activity_tbl(l_activity_index);
328
329 IF l_cu_activity_rec.CU_DETAIL_ID IS NULL OR l_cu_activity_rec.CU_DETAIL_ID = FND_API.G_MISS_NUM THEN
330 l_cu_activity_id := NULL;
331
332 EAM_CONSTRUCTION_UNIT_PKG.Insert_CU_Activity_Row(
333 px_cu_detail_id => l_cu_activity_id
334 ,p_cu_id => l_cu_id
335 ,p_acct_class_code => l_cu_activity_rec.ACCT_CLASS_CODE
336 ,p_activity_id => l_cu_activity_rec.ACTIVITY_ID
337 ,p_cu_activity_qty => l_cu_activity_rec.CU_ACTIVITY_QTY
338 ,p_cu_activity_effective_from => l_cu_activity_rec.CU_ACTIVITY_EFFECTIVE_FROM
339 ,p_cu_activity_effective_to => l_cu_activity_rec.CU_ACTIVITY_EFFECTIVE_TO
340 ,p_creation_date => SYSDATE
341 ,p_created_by => FND_GLOBAL.USER_ID
342 ,p_last_update_date => SYSDATE
343 ,p_last_updated_by => FND_GLOBAL.USER_ID
344 ,p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID
345 );
346 ELSE
347 l_cu_activity_id := l_cu_activity_rec.CU_DETAIL_ID;
348
349 EAM_CONSTRUCTION_UNIT_PKG.Update_CU_Activity_Row(
350 p_cu_detail_id => l_cu_activity_id
351 ,p_cu_id => l_cu_id
352 ,p_acct_class_code => l_cu_activity_rec.ACCT_CLASS_CODE
353 ,p_activity_id => l_cu_activity_rec.ACTIVITY_ID
354 ,p_cu_activity_qty => l_cu_activity_rec.CU_ACTIVITY_QTY
355 ,p_cu_activity_effective_from => l_cu_activity_rec.CU_ACTIVITY_EFFECTIVE_FROM
356 ,p_cu_activity_effective_to => l_cu_activity_rec.CU_ACTIVITY_EFFECTIVE_TO
357 ,p_last_update_date => SYSDATE
358 ,p_last_updated_by => FND_GLOBAL.USER_ID
359 ,p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID
360 );
361 END IF;
362 END LOOP;
363 -- Standard check of p_commit.
364 IF FND_API.To_Boolean( p_commit ) THEN
365 COMMIT WORK;
366 END IF;
367 EXCEPTION
368 WHEN FND_API.G_EXC_ERROR THEN
369 ROLLBACK TO update_construction_unit;
370 RETURN;
371 WHEN OTHERS THEN
372 ROLLBACK TO update_construction_unit;
373 x_return_status := fnd_api.g_ret_sts_error;
374 fnd_message.set_name('EAM','EAM_CU_UNEXP_SQL_ERROR');
375 fnd_message.set_token('API_NAME',l_api_name);
376 fnd_message.set_token('SQL_ERROR',SQLERRM);
377 FND_MSG_PUB.Add;
378 x_msg_data := fnd_message.get;
379 END update_construction_unit;
380
381
382 PROCEDURE copy_construction_unit(
383 p_api_version IN NUMBER
384 ,p_commit IN VARCHAR2
385 ,p_cu_rec IN EAM_CONSTRUCTION_UNIT_PUB.CU_rec
386 ,p_cu_activity_tbl IN EAM_CONSTRUCTION_UNIT_PUB.CU_Activity_tbl
387 ,p_source_cu_id_tbl IN EAM_CONSTRUCTION_UNIT_PUB.CU_ID_tbl
388 ,x_cu_id OUT NOCOPY NUMBER
389 ,x_return_status OUT NOCOPY VARCHAR2
390 ,x_msg_count OUT NOCOPY NUMBER
391 ,x_msg_data OUT NOCOPY VARCHAR2
392 ) IS
393 l_api_name CONSTANT VARCHAR2(30) := 'COPY_CONSTRUCTION_UNIT';
394 l_api_version CONSTANT NUMBER := 1.0;
395 l_debug_level NUMBER := 0;
396 l_temp VARCHAR2(10);
397 BEGIN
398 -- Standard Start of API savepoint
399 SAVEPOINT copy_construction_unit;
400
401 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
402 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
403 END IF;
404
405 x_return_status := fnd_api.G_RET_STS_SUCCESS;
406
407 -- Initialize message list if p_init_msg_list is set to TRUE.
408 FND_MSG_PUB.initialize;
409
410 IF (l_debug_level > 0) THEN
411 debug('copy_construction_unit');
412 END IF;
413
414 --If the debug level = 2 then dump all the parameters values.
415 IF (l_debug_level > 1) THEN
416 debug('copy_construction_unit' ||
417 p_api_version ||'-'||
418 p_commit);
419 debug('Dumping values of p_cu_rec');
420 debug('CU_ID : ' || p_cu_rec.CU_ID);
421 debug('CU_NAME : ' || p_cu_rec.CU_NAME);
422 debug('DESCRIPTION : ' || p_cu_rec.DESCRIPTION);
423 debug('ORGANIZATION_ID : ' || p_cu_rec.ORGANIZATION_ID);
424 debug('CU_EFFECTIVE_FROM : ' || p_cu_rec.CU_EFFECTIVE_FROM);
425 debug('CU_EFFECTIVE_TO : ' || p_cu_rec.CU_EFFECTIVE_TO);
426 debug('ATTRIBUTE_CATEGORY : ' || p_cu_rec.attribute_category);
427 debug('ATTRIBUTE1 : ' || p_cu_rec.ATTRIBUTE1);
428 debug('ATTRIBUTE2 : ' || p_cu_rec.ATTRIBUTE2);
429 debug('ATTRIBUTE3 : ' || p_cu_rec.ATTRIBUTE3);
430 debug('ATTRIBUTE4 : ' || p_cu_rec.ATTRIBUTE4);
431 debug('ATTRIBUTE5 : ' || p_cu_rec.ATTRIBUTE5);
432 debug('ATTRIBUTE6 : ' || p_cu_rec.ATTRIBUTE6);
433 debug('ATTRIBUTE7 : ' || p_cu_rec.ATTRIBUTE7);
434 debug('ATTRIBUTE8 : ' || p_cu_rec.ATTRIBUTE8);
435 debug('ATTRIBUTE9 : ' || p_cu_rec.ATTRIBUTE9);
436 debug('ATTRIBUTE10 : ' || p_cu_rec.ATTRIBUTE10);
437 debug('ATTRIBUTE11 : ' || p_cu_rec.ATTRIBUTE11);
438 debug('ATTRIBUTE12 : ' || p_cu_rec.ATTRIBUTE12);
439 debug('ATTRIBUTE13 : ' || p_cu_rec.ATTRIBUTE13);
440 debug('ATTRIBUTE14 : ' || p_cu_rec.ATTRIBUTE14);
441 debug('ATTRIBUTE15 : ' || p_cu_rec.ATTRIBUTE15);
442 END IF;
443
444 IF p_cu_activity_tbl.Count < 1 THEN
445 FND_MESSAGE.SET_NAME('EAM','EAM_CU_ATLEAST_ONE_ACTIVITY');
446 FND_MSG_PUB.Add;
447 RAISE FND_API.G_EXC_ERROR;
448 END IF;
449
450 FOR l_assign_count IN p_cu_activity_tbl.FIRST..p_cu_activity_tbl.LAST
451 LOOP
452 IF p_cu_activity_tbl.EXISTS(l_assign_count)
453 AND NVL(p_cu_activity_tbl(l_assign_count).CU_ASSIGN_TO_ORG ,FND_API.G_MISS_CHAR) = 'Y' THEN
454
455 BEGIN
456 SELECT 'X'
457 INTO l_temp
458 FROM mtl_system_items_b
459 WHERE inventory_item_id = p_cu_activity_tbl(l_assign_count).ACTIVITY_ID
460 AND organization_id = p_cu_rec.ORGANIZATION_ID;
461
462 EXCEPTION
463 WHEN No_Data_Found THEN
464 EAM_Activity_PUB.Activity_org_assign (
465 p_api_version => 1.0,
466 x_return_status => x_return_status,
467 x_msg_count => x_msg_count,
468 x_msg_data => x_msg_data,
469 p_org_id => p_cu_rec.ORGANIZATION_ID,
470 p_activity_id => p_cu_activity_tbl(l_assign_count).ACTIVITY_ID);
471
472 IF x_return_status = fnd_api.g_ret_sts_error THEN
473 RAISE FND_API.G_EXC_ERROR;
474 END IF;
475 END;
476 END IF;
477
478 END LOOP;
479
480
481 create_construction_unit(
482 p_api_version => p_api_version
483 ,p_commit => p_commit
484 ,p_cu_rec => p_cu_rec
485 ,p_cu_activity_tbl => p_cu_activity_tbl
486 ,x_cu_id => x_cu_id
487 ,x_return_status => x_return_status
488 ,x_msg_count => x_msg_count
489 ,x_msg_data => x_msg_data
490 );
491
492 FOR cu_id_ind IN p_source_cu_id_tbl.FIRST .. p_source_cu_id_tbl.LAST LOOP
493 fnd_attached_documents2_pkg.copy_attachments(
494 X_from_entity_name => 'EAM_CONSTRUCTION_UNIT',
495 X_from_pk1_value => p_source_cu_id_tbl(cu_id_ind).CU_ID,
496 X_from_pk2_value => '',
497 X_from_pk3_value => '',
498 X_from_pk4_value => '',
499 X_from_pk5_value => '',
500 X_to_entity_name => 'EAM_CONSTRUCTION_UNIT',
501 X_to_pk1_value => x_cu_id,
502 X_to_pk2_value => '',
503 X_to_pk3_value => '',
504 X_to_pk4_value => '',
505 X_to_pk5_value => '',
506 X_created_by => FND_GLOBAL.USER_ID,
507 X_last_update_login => '',
508 X_program_application_id=> '',
509 X_program_id => '',
510 X_request_id => ''
511 );
512 END LOOP;
513
514
515 --Check for x_return_status
516 IF x_return_status = fnd_api.g_ret_sts_error THEN
517 RAISE FND_API.G_EXC_ERROR;
518 END IF;
519
520 EXCEPTION
521 WHEN FND_API.G_EXC_ERROR THEN
522 ROLLBACK TO copy_construction_unit;
523 x_return_status := fnd_api.g_ret_sts_error;
524 RETURN;
525 WHEN OTHERS THEN
526 ROLLBACK TO copy_construction_unit;
527 x_return_status := fnd_api.g_ret_sts_error;
528 fnd_message.set_name('EAM','EAM_CU_UNEXP_SQL_ERROR');
529 fnd_message.set_token('API_NAME',l_api_name);
530 fnd_message.set_token('SQL_ERROR',SQLERRM);
531 FND_MSG_PUB.Add;
532 x_msg_data := fnd_message.get;
533
534 END copy_construction_unit;
535
536
537
538 PROCEDURE validate_cu_details(
539 p_api_version IN NUMBER
540 ,p_commit IN VARCHAR2
541 ,p_cu_rec IN EAM_CONSTRUCTION_UNIT_PUB.CU_rec
542 ,p_cu_activity_tbl IN EAM_CONSTRUCTION_UNIT_PUB.CU_Activity_tbl
543 ,p_action IN VARCHAR2
544 ,x_return_status OUT NOCOPY VARCHAR2
545 ,x_msg_count OUT NOCOPY NUMBER
546 ,x_msg_data OUT NOCOPY VARCHAR2
547 ) IS
548 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_CU_DETAILS';
549 l_api_version CONSTANT NUMBER := 1.0;
550 l_debug_level NUMBER := 0;
551 l_cu_rec EAM_CONSTRUCTION_UNIT_PUB.CU_rec;
552 l_cu_activity_tbl EAM_CONSTRUCTION_UNIT_PUB.CU_Activity_tbl;
553 l_cu_activity_rec EAM_CONSTRUCTION_UNIT_PUB.CU_Activity_rec;
554 l_activity_index NUMBER;
555 l_temp VARCHAR2(10);
556 l_qtantity NUMBER;
557 l_activity_name VARCHAR2(2000);
558 l_org_code VARCHAR2(2000);
559 BEGIN
560
561 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
562 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
563 END IF;
564
565 IF (l_debug_level > 0) THEN
566 debug('VALIDATE_CU_DETAILS');
567 END IF;
568
569 x_return_status := fnd_api.G_RET_STS_SUCCESS;
570 l_cu_rec := p_cu_rec;
571
572 IF (l_cu_rec.CU_NAME IS NULL OR l_cu_rec.CU_NAME = FND_API.G_MISS_CHAR) THEN
573 FND_MESSAGE.SET_NAME('EAM','EAM_CU_NAME_NULL');
574 FND_MSG_PUB.Add;
575 RAISE FND_API.G_EXC_ERROR;
576 ELSE
577 BEGIN
578 SELECT 'X'
579 INTO l_temp
580 FROM eam_construction_units
581 WHERE cu_name = l_cu_rec.CU_NAME
582 AND cu_id <> Nvl(l_cu_rec.CU_ID, FND_API.G_MISS_NUM);
583
584 FND_MESSAGE.SET_NAME('EAM','EAM_CU_NAME_DUPLICATE');
585 FND_MSG_PUB.Add;
586 RAISE FND_API.G_EXC_ERROR;
587 EXCEPTION
588 WHEN No_Data_Found THEN
589 NULL;
590 END;
591 END IF;
592
593 IF Nvl(l_cu_rec.cu_effective_from,FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE THEN
594 IF p_action = 'CREATE' THEN
595 l_cu_rec.cu_effective_from := SYSDATE;
596 ELSE
597 FND_MESSAGE.SET_NAME('EAM','EAM_CU_EFFECTIVE_FROM_NULL');
598 FND_MSG_PUB.Add;
599 RAISE FND_API.G_EXC_ERROR;
600 END IF;
601 END IF;
602
603 IF Nvl(l_cu_rec.cu_effective_to,FND_API.G_MISS_DATE) <> FND_API.G_MISS_DATE THEN
604 IF l_cu_rec.cu_effective_to < l_cu_rec.cu_effective_from THEN
605 FND_MESSAGE.SET_NAME('EAM','EAM_CU_EFFECTIVE_TO_FROM_ERROR');
606 FND_MSG_PUB.Add;
607 RAISE FND_API.G_EXC_ERROR;
608 END IF;
609 END IF;
610
611 IF p_cu_activity_tbl.Count < 1 THEN
612 FND_MESSAGE.SET_NAME('EAM','EAM_CU_ATLEAST_ONE_ACTIVITY');
613 FND_MSG_PUB.Add;
614 RAISE FND_API.G_EXC_ERROR;
615 END IF;
616
617 l_cu_activity_tbl := p_cu_activity_tbl;
618
619 -- Valication of CU Activity details - Begin
620 FOR l_activity_index IN 1 .. l_cu_activity_tbl.Count LOOP
621 l_cu_activity_rec := NULL;
622 l_cu_activity_rec := l_cu_activity_tbl(l_activity_index);
623
624 IF (l_debug_level > 1) THEN
625 debug('Dumping values of l_cu_activity_rec');
626 debug('CU_ID : ' || l_cu_activity_rec.CU_ID);
627 debug('CU_DETAIL_ID : ' || l_cu_activity_rec.CU_DETAIL_ID);
628 debug('ACCT_CLASS_CODE : ' || l_cu_activity_rec.ACCT_CLASS_CODE);
629 debug('ACTIVITY_ID : ' || l_cu_activity_rec.ACTIVITY_ID);
630 debug('CU_ACTIVITY_QTY : ' || l_cu_activity_rec.CU_ACTIVITY_QTY);
631 debug('CU_ACTIVITY_EFFECTIVE_FROM : ' || l_cu_activity_rec.CU_ACTIVITY_EFFECTIVE_FROM);
632 debug('CU_ACTIVITY_EFFECTIVE_TO : ' || l_cu_activity_rec.CU_ACTIVITY_EFFECTIVE_TO);
633 debug('CU_ASSIGN_TO_ORG : ' || l_cu_activity_rec.CU_ASSIGN_TO_ORG);
634 END IF;
635
636
637 BEGIN
638 SELECT concatenated_segments
639 INTO l_activity_name
640 FROM mtl_system_items_kfv
641 WHERE inventory_item_id = l_cu_activity_rec.ACTIVITY_ID
642 AND ROWNUM < 2 ;
643
644
645 SELECT organization_code
646 INTO l_org_code
647 FROM mtl_parameters
648 WHERE organization_id = l_cu_rec.ORGANIZATION_ID
649 AND ROWNUM < 2 ;
650
651 EXCEPTION
652 WHEN No_Data_Found THEN
653 FND_MESSAGE.SET_NAME('EAM','EAM_CU_INVALID_ACTIVITY');
654 FND_MESSAGE.SET_TOKEN('ACTIVITY',l_cu_activity_rec.ACTIVITY_ID);
655 FND_MSG_PUB.Add;
656 RAISE FND_API.G_EXC_ERROR;
657 WHEN OTHERS THEN
658 NULL;
659 END;
660
661 BEGIN
662 SELECT 'X'
663 INTO l_temp
664 FROM mtl_system_items_b
665 WHERE inventory_item_id = l_cu_activity_rec.ACTIVITY_ID
666 AND organization_id = l_cu_rec.ORGANIZATION_ID;
667
668 EXCEPTION
669 WHEN No_Data_Found THEN
670 FND_MESSAGE.SET_NAME('EAM','EAM_CU_ACTIVITY_NOT_ASSIGNED');
671 FND_MESSAGE.SET_TOKEN('ACTIVITY',l_activity_name);
672 FND_MESSAGE.SET_TOKEN('ORG',l_org_code);
673 FND_MSG_PUB.Add;
674 RAISE FND_API.G_EXC_ERROR;
675 END;
676
677 IF Nvl(l_cu_activity_rec.ACCT_CLASS_CODE, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
678 BEGIN
679 SELECT 'X'
680 INTO l_temp
681 FROM WIP_ACCOUNTING_CLASSES
682 WHERE organization_id = l_cu_rec.ORGANIZATION_ID
683 AND class_code = l_cu_activity_rec.ACCT_CLASS_CODE
684 AND ((disable_date IS NULL )OR (disable_date > SYSDATE))
685 AND class_type = 6;
686
687 EXCEPTION
688 WHEN No_Data_Found THEN
689 FND_MESSAGE.SET_NAME('EAM','EAM_CU_ACCTCLASS_NOT_ASSIGNED');
690 FND_MESSAGE.SET_TOKEN('ACCTCLASS',l_cu_activity_rec.ACCT_CLASS_CODE);
691 FND_MESSAGE.SET_TOKEN('ORG',l_org_code);
692 FND_MSG_PUB.Add;
693 RAISE FND_API.G_EXC_ERROR;
694 END;
695 END IF;
696
697 l_qtantity := l_cu_activity_rec.CU_ACTIVITY_QTY;
698 IF l_qtantity*100 <> Round(l_qtantity*100) THEN
699 FND_MESSAGE.SET_NAME('EAM','EAM_CU_ACT_QTY_DECIMAL');
700 FND_MSG_PUB.Add;
701 RAISE FND_API.G_EXC_ERROR;
702 END IF;
703
704 IF l_qtantity < 0 THEN
705 FND_MESSAGE.SET_NAME('EAM','EAM_CU_ACT_QTY_POSITIVE');
706 FND_MSG_PUB.Add;
707 RAISE FND_API.G_EXC_ERROR;
708 END IF;
709
710 IF Nvl(l_cu_activity_rec.CU_ACTIVITY_EFFECTIVE_FROM, FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE THEN
711 FND_MESSAGE.SET_NAME('EAM','EAM_CU_ACT_EFFECTIVE_FROM_NULL');
712 FND_MSG_PUB.Add;
713 RAISE FND_API.G_EXC_ERROR;
714 END IF;
715
716 IF l_cu_rec.CU_EFFECTIVE_FROM > l_cu_activity_rec.CU_ACTIVITY_EFFECTIVE_FROM THEN
717 FND_MESSAGE.SET_NAME('EAM','EAM_CU_ACT_CU_EFFECTIVE_FROM');
718 FND_MSG_PUB.Add;
719 RAISE FND_API.G_EXC_ERROR;
720 END IF;
721
722 IF Nvl(l_cu_activity_rec.CU_ACTIVITY_EFFECTIVE_TO,FND_API.G_MISS_DATE) <> FND_API.G_MISS_DATE THEN
723 IF l_cu_activity_rec.CU_ACTIVITY_EFFECTIVE_TO < l_cu_activity_rec.CU_ACTIVITY_EFFECTIVE_FROM THEN
724 FND_MESSAGE.SET_NAME('EAM','EAM_CU_ACT_EFFT_TO_FROM_ERROR');
725 FND_MSG_PUB.Add;
726 RAISE FND_API.G_EXC_ERROR;
727 END IF;
728 END IF;
729 END LOOP;
730 -- Valication of CU Activity details - End
731
732 EXCEPTION
733 WHEN FND_API.G_EXC_ERROR THEN
734 x_return_status := fnd_api.g_ret_sts_error;
735 x_msg_data := dump_error_stack;
736 WHEN OTHERS THEN
737 x_return_status := fnd_api.g_ret_sts_error;
738 fnd_message.set_name('EAM','EAM_CU_UNEXP_SQL_ERROR');
739 fnd_message.set_token('API_NAME',l_api_name);
740 fnd_message.set_token('SQL_ERROR',SQLERRM);
741 FND_MSG_PUB.Add;
742 x_msg_data := fnd_message.get;
743 END validate_cu_details;
744
745 FUNCTION dump_error_stack RETURN varchar2
746 IS
747 l_msg_count number;
748 l_msg_data varchar2(2000);
749 l_msg_index_out number;
750 x_msg_data varchar2(4000);
751 BEGIN
752 x_msg_data := null;
753 fnd_msg_pub.count_and_get(
754 p_count => l_msg_count,
755 p_data => l_msg_data);
756
757 FOR l_ind IN 1..l_msg_count
758 LOOP
759 fnd_msg_pub.get(
760 p_msg_index => l_ind,
761 p_encoded => fnd_api.g_false,
762 p_data => l_msg_data,
763 p_msg_index_out => l_msg_index_out);
764
765 x_msg_data := ltrim(x_msg_data||' '||l_msg_data);
766 IF length(x_msg_data) > 1999 THEN
767 x_msg_data := substr(x_msg_data, 1, 1999);
768 exit;
769 END IF;
770 END LOOP;
771 RETURN x_msg_data;
772 EXCEPTION
773 when others then
774 RETURN x_msg_data;
775 END dump_error_stack;
776
777 End EAM_CONSTRUCTION_UNIT_PVT;