[Home] [Help]
PACKAGE BODY: APPS.GMF_BURDENDETAILS_PVT
Source
1 PACKAGE BODY GMF_BurdenDetails_PVT AS
2 /* $Header: GMFVBRDB.pls 120.2.12000000.2 2007/04/04 12:11:18 pmarada ship $ */
3
4 -- Start of comments
5 --+==========================================================================+
6 --| Copyright (c) 1998 Oracle Corporation |
7 --| Redwood Shores, CA, USA |
8 --| All rights reserved. |
9 --+==========================================================================+
10 --| File Name : GMFVBRDB.pls |
11 --| Package Name : GMF_BurdenDetails_PVT |
12 --| API name : GMF_BurdenDetails_PVT |
13 --| Type : Public |
14 --| Pre-reqs : N/A |
15 --| Function : Burden Details creation, updatation and deletion. |
16 --| |
17 --| Parameters : N/A |
18 --| |
19 --| Current Vers : 2.0 |
20 --| Previous Vers : 1.0 |
21 --| Initial Vers : 1.0 |
22 --| |
23 --| Contents |
24 --| Create_Burden_Details |
25 --| Update_Burden_Details |
26 --| Delete_Burden_Details |
27 --| Get_Burden_Details |
28 --| |
29 --| Notes |
30 --| This package contains public procedures relating to Burden Details |
31 --| creation, updatation and deletetion. |
32 --| |
33 --| HISTORY |
34 --| 12/Apr/2001 Uday Moogala Created Bug# 1418689 |
35 --| |
36 --| 30/Oct/2002 R.Sharath Kumar Bug# 2641405 Added NOCOPY hint |
37 --| 05/NOV/2002 Uday Moogala Bug# 2659435 |
38 --| Performance related fixes. |
39 --| 1. remove G_MISS_xxx assignments. |
40 --| 2. Conditionally calling debug routine. |
41 --| Also, fixed issues found during unit testing. Search for the bug |
42 --| number to find the fixes. |
43 --| 24/DEC/2002 Uday Moogala Bug# 2722404 |
44 --| Removed creation_date and created_by from update stmts. |
45 --| 20-Oct-2005 Prasad marada, Bug 4689137 Modified as per convergence |
46 --+==========================================================================+
47 -- End of comments
48
49
50
51 PROCEDURE log_msg -- Bug 2659435: Removed first paramter for debug level
52 (
53 p_msg_text IN VARCHAR2
54 );
55 --
56 FUNCTION check_records_exist
57 (
58 p_organization_id IN cm_brdn_dtl.organization_id%TYPE,
59 p_inventory_item_id IN cm_brdn_dtl.inventory_item_id%TYPE,
60 p_resources IN cm_brdn_dtl.resources%TYPE,
61 p_period_id IN cm_brdn_dtl.period_id%TYPE,
62 p_cost_type_id IN cm_brdn_dtl.cost_type_id%TYPE ,
63 p_cost_cmpntcls_id IN cm_brdn_dtl.cost_cmpntcls_id%TYPE,
64 p_cost_analysis_code IN cm_brdn_dtl.cost_analysis_code%TYPE
65 ) RETURN BOOLEAN;
66
67 -- Global variables
68 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMF_BurdenDetails_PVT';
69
70 G_debug_level NUMBER(2) := FND_MSG_PUB.G_Msg_Level_Threshold; -- Use this variable everywhere
71 -- to decide to log a debug msg.
72
73
74 --Start of comments
75 --+========================================================================+
76 --| API Name : Create_Burden_Details |
77 --| TYPE : Public |
78 --| Function : Creates a new Burden Details based on the input into table|
79 --| CM_CMPT_DTL |
80 --| Pre-reqa : None. |
81 --| Parameters : |
82 --| IN : |
83 --| p_api_version IN NUMBER - Required |
84 --| p_init_msg_list IN VARCHAR2 - Optional |
85 --| p_commit IN VARCHAR2 - Optional |
86 --| p_header_rec IN Burden_Header_Rec_Type |
87 --| p_dtl_tbl IN Burden_Dtl_Tbl_Type |
88 --| p_user_id IN NUMBER |
89 --| OUT : |
90 --| x_return_status OUT VARCHAR2 |
91 --| x_msg_count OUT NUMBER |
92 --| x_msg_data OUT VARCHAR2 |
93 --| x_burdenline_ids OUT Burdenline_Ids_Tbl_Type |
94 --| |
95 --| Version : |
96 --| Current Version : 2.0 |
97 --| Previous Version : 1.0 |
98 --| Initial Version : 1.0 |
99 --| |
100 --| Notes : |
101 --| |
102 --| HISTORY |
103 --| 01-Mar-01 Uday Moogala - Created |
104 --| 01-apr-07 prasad marada bug 5589409, added check_record_exists |
105 --| procedure call |
106 --+========================================================================+
107 -- End of comments
108
109 PROCEDURE Create_Burden_Details
110 (
111 p_api_version IN NUMBER ,
112 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
113 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
114
115 x_return_status OUT NOCOPY VARCHAR2 ,
116 x_msg_count OUT NOCOPY VARCHAR2 ,
117 x_msg_data OUT NOCOPY VARCHAR2 ,
118
119 p_header_rec IN GMF_BurdenDetails_PUB.Burden_Header_Rec_Type ,
120 p_dtl_tbl IN GMF_BurdenDetails_PUB.Burden_Dtl_Tbl_Type ,
121 p_user_id IN fnd_user.user_id%TYPE ,
122 p_burden_factor_tbl IN Burden_factor_Tbl_Type ,
123
124 x_burdenline_ids OUT NOCOPY GMF_BurdenDetails_PUB.Burdenline_Ids_Tbl_Type
125 )
126 IS
127
128 l_api_name CONSTANT VARCHAR2(30) := 'Create_Burden_Details' ;
129 l_api_version CONSTANT NUMBER := 2.0 ;
130
131 l_burdenline_id cm_brdn_dtl.burdenline_id%TYPE ;
132 l_idx NUMBER(10) := 0 ;
133 BEGIN
134
135 -- Standard Start of API savepoint
136 SAVEPOINT Create_Burden_Details_PVT ;
137
138 -- Initialize message list if p_init_msg_list is set to TRUE.
139 IF FND_API.to_Boolean( p_init_msg_list ) THEN
140 FND_MSG_PUB.initialize;
141 END IF;
142
143 -- Standard call to check for call compatibility.
144 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
145 p_api_version ,
146 l_api_name ,
147 G_PKG_NAME )
148 THEN
149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
150 END IF;
151
152 -- Initialize API return status to success
153 x_return_status := FND_API.G_RET_STS_SUCCESS;
154
155 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
156 log_msg('Beginning Private Create Burden Details API.');
157 END IF;
158
159 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
160 log_msg('Burden Detail Count : ' || p_dtl_tbl.count);
161 log_msg('Inserting Burden Details for Item ' || p_header_rec.inventory_item_id ||
162 ' organization_id ' || p_header_rec.organization_id || ' organization_code ' || p_header_rec.organization_code ||
163 ' Calendar ' || p_header_rec.calendar_code || ' Period ' || p_header_rec.Period_code ||
164 ' Mthd ' || p_header_rec.cost_mthd_code ) ;
165 END IF;
166
167
168 FOR i in 1..p_dtl_tbl.count
169 LOOP
170 -- check if there exists any burden for the same record
171 IF check_records_exist(
172 p_organization_id => p_header_rec.organization_id,
173 p_inventory_item_id => p_header_rec.inventory_item_id,
174 p_resources => p_dtl_tbl(i).resources,
175 p_period_id => p_header_rec.period_id,
176 p_cost_type_id => p_header_rec.cost_type_id,
177 p_cost_cmpntcls_id => p_dtl_tbl(i).cost_cmpntcls_id,
178 p_cost_analysis_code => p_dtl_tbl(i).cost_analysis_code
179 ) THEN
180 FND_MESSAGE.SET_NAME('GMF','GMF_API_DUPLICATE_BRDN_COST');
181 FND_MESSAGE.SET_TOKEN('ORGANIZATION_ID',p_header_rec.organization_id);
182 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID',p_header_rec.inventory_item_id);
183 FND_MESSAGE.SET_TOKEN('RESOURCES',p_dtl_tbl(i).resources);
184 FND_MESSAGE.SET_TOKEN('PERIOD_ID',p_header_rec.period_id);
185 FND_MESSAGE.SET_TOKEN('COST_TYPE_ID',p_header_rec.cost_type_id);
186 FND_MESSAGE.SET_TOKEN('COST_CMPNTCLS_ID',p_dtl_tbl(i).cost_cmpntcls_id);
187 FND_MESSAGE.SET_TOKEN('COST_ANALYSIS_CODE',p_dtl_tbl(i).cost_analysis_code);
188 FND_MSG_PUB.Add;
189 RAISE FND_API.G_EXC_ERROR;
190 END IF;
191
192 SELECT gem5_burdenline_id_S.NEXTVAL
193 INTO l_burdenline_id
194 FROM DUAL ;
195
196 --
197 -- Using anonymous block to capture any error for the current record. Duplicate record check is
198 -- not done in public API because of the performance considerations.
199 -- In case of failure error msg will be logged and will continue with the next record
200 --
201
202 BEGIN
203 INSERT INTO cm_brdn_dtl
204 (
205 burdenline_id
206 , resources
207 , cost_cmpntcls_id
208 , cost_analysis_code
209 , burden_usage
210 , item_qty
211 , burden_qty
212 , burden_factor
213 , rollover_ind
214 , cmpntcost_id
215 , trans_cnt
216 , delete_mark
217 , text_code
218 , created_by
219 , creation_date
220 , last_updated_by
221 , last_update_login
222 , last_update_date
223 , request_id
224 , program_application_id
225 , program_id
226 , program_update_date
227 , organization_id
228 , inventory_item_id
229 , period_id
230 , cost_type_id
231 , item_uom
232 , burden_uom
233 )
234 VALUES
235 (
236 l_burdenline_id
237 , p_dtl_tbl(i).resources
238 , p_dtl_tbl(i).cost_cmpntcls_id
239 , p_dtl_tbl(i).cost_analysis_code
240 , p_dtl_tbl(i).burden_usage
241 , p_dtl_tbl(i).item_qty
242 , p_dtl_tbl(i).burden_qty
243 , p_burden_factor_tbl(i).burden_factor
244 , 0 -- rollover indicator
245 , '' -- Component Cost Id
246 , '' -- trans cnt
247 , 0 --delete mark
248 , '' -- text code
249 , p_user_id
250 , sysdate
251 , p_user_id
252 , FND_GLOBAL.LOGIN_ID
253 , sysdate
254 , '' -- request_id
255 , '' -- program_application_id
256 , '' -- program_id
257 , '' -- program_update_date
258 , p_header_rec.organization_id
259 , p_header_rec.inventory_item_id
260 , p_header_rec.period_id
261 , p_header_rec.cost_type_id
262 , p_dtl_tbl(i).item_uom
263 , p_dtl_tbl(i).burden_uom
264 );
265
266 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
267 log_msg( '1 row inserted for Resource ' || p_dtl_tbl(i).resources ||
268 ' Cmptcls Id ' || p_dtl_tbl(i).cost_cmpntcls_id || ' Alys Code ' || p_dtl_tbl(i).cost_analysis_code ||
269 ' Burdenline Id ' || l_burdenline_id);
270 END IF;
271
272 l_idx := l_idx + 1 ;
273
274 x_burdenline_ids(l_idx).resources := p_dtl_tbl(i).resources ;
275 x_burdenline_ids(l_idx).cost_cmpntcls_id := p_dtl_tbl(i).cost_cmpntcls_id ;
276 x_burdenline_ids(l_idx).cost_analysis_code := p_dtl_tbl(i).cost_analysis_code ;
277 x_burdenline_ids(l_idx).burdenline_id := l_burdenline_id ;
278
279 EXCEPTION
280 WHEN OTHERS THEN
281 x_burdenline_ids.delete ;
282 FND_MESSAGE.SET_NAME('GMF','GMF_API_BRDN_INSERT_FAILED');
283 FND_MESSAGE.SET_TOKEN('RESOURCE', p_dtl_tbl(i).resources);
284 FND_MESSAGE.SET_TOKEN('CMPNTCLS_ID', p_dtl_tbl(i).cost_cmpntcls_id);
285 FND_MESSAGE.SET_TOKEN('ALYS_CODE', p_dtl_tbl(i).cost_analysis_code);
286 FND_MSG_PUB.Add;
287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
288 RAISE ;
289 END ;
290 END LOOP ;
291
292 -- Standard check of p_commit.
293 IF FND_API.To_Boolean( p_commit ) THEN
294 COMMIT WORK;
295 END IF;
296
297 -- Standard call to get message count and if count is 1, get message info.
298 FND_MSG_PUB.Count_And_Get
299 ( p_count => x_msg_count ,
300 p_data => x_msg_data
301 );
302
303 EXCEPTION
304 WHEN FND_API.G_EXC_ERROR THEN
305 ROLLBACK TO Create_Burden_Details_PVT;
306 x_return_status := FND_API.G_RET_STS_ERROR ;
307 FND_MSG_PUB.Count_And_Get
308 ( p_count => x_msg_count ,
309 p_data => x_msg_data
310 );
311 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
312 ROLLBACK TO Create_Burden_Details_PVT;
313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
314 FND_MSG_PUB.Count_And_Get
315 ( p_count => x_msg_count ,
316 p_data => x_msg_data
317 );
318 WHEN OTHERS THEN
319 ROLLBACK TO Create_Burden_Details_PVT;
320 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
321 IF FND_MSG_PUB.Check_Msg_Level
322 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
323 THEN
324 FND_MSG_PUB.Add_Exc_Msg
325 ( G_PKG_NAME ,
326 l_api_name
327 );
328 END IF;
329 FND_MSG_PUB.Count_And_Get
330 ( p_count => x_msg_count ,
331 p_data => x_msg_data
332 );
333 END Create_Burden_Details;
334
335
336 --Start of comments
337 --+========================================================================+
338 --| API Name : Update_Burden_Details |
339 --| TYPE : Public |
340 --| Function : Updates Burden Details based on the input into CM_CMPT_DTL|
341 --| Pre-reqa : None. |
342 --| Parameters : |
343 --| IN : |
344 --| p_api_version IN NUMBER - Required |
345 --| p_init_msg_list IN VARCHAR2 - Optional |
346 --| p_commit IN VARCHAR2 - Optional |
347 --| p_header_rec IN Burden_Header_Rec_Type |
348 --| p_dtl_tbl IN Burden_Dtl_Tbl_Type |
349 --| p_user_id IN NUMBER |
350 --| OUT : |
351 --| x_return_status OUT VARCHAR2 |
352 --| x_msg_count OUT NUMBER |
353 --| x_msg_data OUT VARCHAR2 |
354 --| |
355 --| Version : |
356 --| Current Version : 2.0 |
357 --| Previous Version : 1.0 |
358 --| Initial Version : 1.0 |
359 --| |
360 --| Notes : |
361 --| |
362 --| HISTORY |
363 --| 01-Mar-01 Uday Moogala - Created |
364 --| |
365 --+========================================================================+
366 -- End of comments
367
368 PROCEDURE Update_Burden_Details
369 (
370 p_api_version IN NUMBER ,
371 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
372 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
373
374 x_return_status OUT NOCOPY VARCHAR2 ,
375 x_msg_count OUT NOCOPY VARCHAR2 ,
376 x_msg_data OUT NOCOPY VARCHAR2 ,
377
378 p_header_rec IN GMF_BurdenDetails_PUB.Burden_Header_Rec_Type ,
379 p_dtl_tbl IN GMF_BurdenDetails_PUB.Burden_Dtl_Tbl_Type ,
380 p_user_id IN fnd_user.user_id%TYPE ,
381 p_burden_factor_tbl IN Burden_factor_Tbl_Type
382 )
383 IS
384 l_api_name CONSTANT VARCHAR2(30) := 'Update_Burden_Details' ;
385 l_api_version CONSTANT NUMBER := 2.0 ;
386
387 l_no_rows_upd NUMBER ;
388 BEGIN
389
390 -- Standard Start of API savepoint
391 SAVEPOINT Update_Burden_Details_PVT;
392
393 -- Initialize message list if p_init_msg_list is set to TRUE.
394 IF FND_API.to_Boolean( p_init_msg_list ) THEN
395 FND_MSG_PUB.initialize;
396 END IF;
397
398 -- Standard call to check for call compatibility.
399 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
400 p_api_version ,
401 l_api_name ,
402 G_PKG_NAME )
403 THEN
404 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
405 END IF;
406
407 -- Initialize API return status to success
408 x_return_status := FND_API.G_RET_STS_SUCCESS;
409
410 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
411 log_msg('Beginning Update Burden Details process.');
412 END IF;
413
414 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
415 log_msg('Burden Detail Count : ' || p_dtl_tbl.count);
416 log_msg('Processing Burden Details for Item ' || p_header_rec.inventory_item_id ||
417 ' organization_id ' || p_header_rec.organization_id || ' organization_code ' || p_header_rec.organization_code ||
418 ' Calendar ' || p_header_rec.calendar_code || ' Period ' || p_header_rec.Period_code ||
419 ' Mthd ' || p_header_rec.cost_mthd_code ) ;
420 END IF;
421
422 FOR i in 1..p_dtl_tbl.count
423 LOOP
424
425 --
426 -- Using anonymous block to capture any error for the current record. Duplicate record check is
427 -- not done in public API because of the performance considerations.
428 -- In case of failure error msg will be logged and will continue with the next record
429 --
430 BEGIN
431 IF (p_dtl_tbl(i).burdenline_id IS NOT NULL) AND -- Bug 2659435 OR to AND
432 (p_dtl_tbl(i).burdenline_id <> FND_API.G_MISS_NUM) THEN
433
434 IF p_dtl_tbl(i).delete_mark = 0 THEN -- Update
435 UPDATE cm_brdn_dtl
436 SET
437 /* Bug 2659435: key columns should not be changed.
438 resources = decode(p_dtl_tbl(i).resources
439 , FND_API.G_MISS_CHAR, NULL
440 , NULL, resources
441 , p_dtl_tbl(i).resources )
442 , cost_cmpntcls_id = decode(p_dtl_tbl(i).cost_cmpntcls_id
443 , FND_API.G_MISS_NUM, NULL
444 , NULL, cost_cmpntcls_id
445 , p_dtl_tbl(i).cost_cmpntcls_id )
446 , cost_analysis_code= decode(p_dtl_tbl(i).cost_analysis_code
447 , FND_API.G_MISS_CHAR, NULL
448 , NULL, cost_analysis_code
449 , p_dtl_tbl(i).cost_analysis_code )
450 */
451 burden_usage = decode(p_dtl_tbl(i).burden_usage
452 , FND_API.G_MISS_NUM, NULL
453 , NULL, burden_usage
454 , p_dtl_tbl(i).burden_usage )
455 , item_qty = decode(p_dtl_tbl(i).item_qty
456 , FND_API.G_MISS_NUM, NULL
457 , NULL, item_qty
458 , p_dtl_tbl(i).item_qty )
459 , item_uom = decode(p_dtl_tbl(i).item_uom
460 , FND_API.G_MISS_CHAR, NULL
461 , NULL, item_uom
462 , p_dtl_tbl(i).item_uom )
463 , burden_qty = decode(p_dtl_tbl(i).burden_qty
464 , FND_API.G_MISS_NUM, NULL
465 , NULL, burden_qty
466 , p_dtl_tbl(i).burden_qty )
467 , burden_uom = decode(p_dtl_tbl(i).burden_uom
468 , FND_API.G_MISS_CHAR, NULL
469 , NULL, burden_uom
470 , p_dtl_tbl(i).burden_uom )
471 , burden_factor = decode(p_burden_factor_tbl(i).burden_factor
472 , FND_API.G_MISS_NUM, NULL
473 , NULL, burden_factor
474 , p_burden_factor_tbl(i).burden_factor )
475 , delete_mark = 0
476 -- , creation_date = sysdate -- Bug 2722404
477 -- , created_by = p_user_id
478 , last_update_date = sysdate
479 , last_updated_by = p_user_id
480 , last_update_login = FND_GLOBAL.LOGIN_ID
481 WHERE
482 burdenline_id = p_dtl_tbl(i).burdenline_id
483 ;
484 ELSE -- delete the record i.e mark for purge
485 UPDATE cm_brdn_dtl
486 SET
487 delete_mark = 1
488 -- , creation_date = sysdate -- Bug 2722404
489 -- , created_by = p_user_id
490 , last_update_date = sysdate
491 , last_updated_by = p_user_id
492 , last_update_login = FND_GLOBAL.LOGIN_ID
493 WHERE
494 burdenline_id = p_dtl_tbl(i).burdenline_id
495 ;
496 END IF ;
497
498 IF SQL%NOTFOUND THEN -- burden details not found
499 IF p_dtl_tbl(i).delete_mark = 0 THEN
500 FND_MESSAGE.SET_NAME('GMF','GMF_API_BRDN_NOT_FOUND_FOR_ID');
501 FND_MESSAGE.SET_TOKEN('BURDENLINE_ID', p_dtl_tbl(i).burdenline_id);
502 FND_MSG_PUB.Add;
503 ELSE
504 FND_MESSAGE.SET_NAME('GMF','GMF_API_DEL_BRDN_NOT_FOUND_ID');
505 FND_MESSAGE.SET_TOKEN('OVERHEADLINE_ID', p_dtl_tbl(i).burdenline_id);
506 FND_MSG_PUB.Add;
507 END IF ;
508 x_return_status := FND_API.G_RET_STS_ERROR ;
509 EXIT ;
510 ELSE
511 l_no_rows_upd := l_no_rows_upd + 1 ;
512
513 IF p_dtl_tbl(i).delete_mark = 0 THEN
514 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
515 log_msg( '1 row updated for Burdenline Id ' || p_dtl_tbl(i).burdenline_id);
516 END IF;
517 ELSE
518 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
519 log_msg( '1 row deleted for Burdenline Id ' || p_dtl_tbl(i).burdenline_id);
520 END IF;
521 END IF ;
522 END IF ;
523
524 ELSE -- Burdenline_Id is not supplied.
525 IF p_dtl_tbl(i).delete_mark = 0 THEN -- Update
526 UPDATE cm_brdn_dtl
527 SET
528 /* Bug 2659435: key columns should not be changed.
529 resources = decode(p_dtl_tbl(i).resources
530 , FND_API.G_MISS_CHAR, NULL
531 , NULL, resources
532 , p_dtl_tbl(i).resources )
533 , cost_cmpntcls_id = decode(p_dtl_tbl(i).cost_cmpntcls_id
534 , FND_API.G_MISS_NUM, NULL
535 , NULL, cost_cmpntcls_id
536 , p_dtl_tbl(i).cost_cmpntcls_id )
537 , cost_analysis_code= decode(p_dtl_tbl(i).cost_analysis_code
538 , FND_API.G_MISS_CHAR, NULL
539 , NULL, cost_analysis_code
540 , p_dtl_tbl(i).cost_analysis_code )
541 */
542 burden_usage = decode(p_dtl_tbl(i).burden_usage
543 , FND_API.G_MISS_NUM, NULL
544 , NULL, burden_usage
545 , p_dtl_tbl(i).burden_usage )
546 , item_qty = decode(p_dtl_tbl(i).item_qty
547 , FND_API.G_MISS_NUM, NULL
548 , NULL, item_qty
549 , p_dtl_tbl(i).item_qty )
550 , item_uom = decode(p_dtl_tbl(i).item_uom
551 , FND_API.G_MISS_CHAR, NULL
552 , NULL, item_uom
553 , p_dtl_tbl(i).item_uom )
554 , burden_qty = decode(p_dtl_tbl(i).burden_qty
555 , FND_API.G_MISS_NUM, NULL
556 , NULL, burden_qty
557 , p_dtl_tbl(i).burden_qty )
558 , burden_uom = decode(p_dtl_tbl(i).burden_uom
559 , FND_API.G_MISS_CHAR, NULL
560 , NULL, burden_uom
561 , p_dtl_tbl(i).burden_uom )
562 , burden_factor = decode(p_burden_factor_tbl(i).burden_factor
563 , FND_API.G_MISS_NUM, NULL
564 , NULL, burden_factor
565 , p_burden_factor_tbl(i).burden_factor )
566 , delete_mark = 0
567 -- , creation_date = sysdate -- Bug 2722404
568 -- , created_by = p_user_id
569 , last_update_date = sysdate
570 , last_updated_by = p_user_id
571 , last_update_login = FND_GLOBAL.LOGIN_ID
572 WHERE
573 organization_id = p_header_rec.organization_id
574 AND inventory_item_id = p_header_rec.inventory_item_id
575 AND period_id = p_header_rec.period_id
576 AND cost_type_id = p_header_rec.cost_type_id
577 AND resources = p_dtl_tbl(i).resources
578 AND cost_cmpntcls_id = p_dtl_tbl(i).cost_cmpntcls_id
579 AND cost_analysis_code = p_dtl_tbl(i).cost_analysis_code
580 ;
581 ELSE -- delete the record i.e mark for purge
582 UPDATE cm_brdn_dtl
583 SET
584 delete_mark = 1
585 -- , creation_date = sysdate -- Bug 2722404
586 -- , created_by = p_user_id
587 , last_update_date = sysdate
588 , last_updated_by = p_user_id
589 , last_update_login = FND_GLOBAL.LOGIN_ID
590 WHERE
591 organization_id = p_header_rec.organization_id
592 AND inventory_item_id = p_header_rec.inventory_item_id
593 AND period_id = p_header_rec.period_id
594 AND cost_type_id = p_header_rec.cost_type_id
595 AND resources = p_dtl_tbl(i).resources
596 AND cost_cmpntcls_id = p_dtl_tbl(i).cost_cmpntcls_id
597 AND cost_analysis_code = p_dtl_tbl(i).cost_analysis_code
598 ;
599 END IF ;
600
601
602 IF SQL%NOTFOUND THEN -- burden details not found
603 IF p_dtl_tbl(i).delete_mark = 0 THEN
604 FND_MESSAGE.SET_NAME('GMF','GMF_API_BRDN_NOT_FOUND_FOR_DTL');
605 FND_MESSAGE.SET_TOKEN('RESOURCE', p_dtl_tbl(i).resources);
606 FND_MESSAGE.SET_TOKEN('CMPNTCLS_ID', p_dtl_tbl(i).cost_cmpntcls_id);
607 FND_MESSAGE.SET_TOKEN('ALYS_CODE', p_dtl_tbl(i).cost_analysis_code);
608 FND_MSG_PUB.Add;
609 ELSE
610 FND_MESSAGE.SET_NAME('GMF','GMF_API_DEL_BRDN_NOT_FOUND_DTL');
611 FND_MESSAGE.SET_TOKEN('RESOURCE', p_dtl_tbl(i).resources);
612 FND_MESSAGE.SET_TOKEN('CMPNTCLS_ID', p_dtl_tbl(i).cost_cmpntcls_id);
613 FND_MESSAGE.SET_TOKEN('ALYS_CODE', p_dtl_tbl(i).cost_analysis_code);
614 FND_MSG_PUB.Add;
615 END IF ;
616 x_return_status := FND_API.G_RET_STS_ERROR ;
617 EXIT ;
618 ELSE
619 l_no_rows_upd := l_no_rows_upd + 1 ;
620
621 IF p_dtl_tbl(i).delete_mark = 0 THEN
622 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
623 log_msg( '1 row updated for Resource ' || p_dtl_tbl(i).resources ||
624 ' Cmptcls Id ' || p_dtl_tbl(i).cost_cmpntcls_id || ' Alys Code ' || p_dtl_tbl(i).cost_analysis_code);
625 END IF;
626 ELSE
627 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
628 log_msg( '1 row deleted for Resource ' || p_dtl_tbl(i).resources ||
629 ' Cmptcls Id ' || p_dtl_tbl(i).cost_cmpntcls_id || ' Alys Code ' || p_dtl_tbl(i).cost_analysis_code);
630 END IF;
631 END IF ;
632 END IF ;
633
634 END IF ;
635
636 EXCEPTION
637 WHEN OTHERS THEN
638 IF p_dtl_tbl(i).delete_mark = 0 THEN -- Update
639 IF (p_dtl_tbl(i).burdenline_id IS NOT NULL) OR -- burdenline_id is sent
640 (p_dtl_tbl(i).burdenline_id <> FND_API.G_MISS_NUM) THEN
641 FND_MESSAGE.SET_NAME('GMF','GMF_API_BRDN_UPD_FAILED_ID');
642 FND_MESSAGE.SET_TOKEN('OVERHEADLINE_ID','Burdenline Id ' || p_dtl_tbl(i).burdenline_id);
643 FND_MSG_PUB.Add;
644 ELSE
645 FND_MESSAGE.SET_NAME('GMF','GMF_API_BRDN_UPD_FAILED_DTLS');
646 FND_MESSAGE.SET_TOKEN('RESOURCE', p_dtl_tbl(i).resources);
647 FND_MESSAGE.SET_TOKEN('CMPNTCLS_ID', p_dtl_tbl(i).cost_cmpntcls_id);
648 FND_MESSAGE.SET_TOKEN('ALYS_CODE', p_dtl_tbl(i).cost_analysis_code);
649 FND_MSG_PUB.Add;
650 END IF ;
651 ELSE -- delete
652 IF (p_dtl_tbl(i).burdenline_id IS NOT NULL) OR -- burdenline_id is sent
653 (p_dtl_tbl(i).burdenline_id <> FND_API.G_MISS_NUM) THEN
654 FND_MESSAGE.SET_NAME('GMF','GMF_API_BRDN_DEL_FAILED_ID');
655 FND_MESSAGE.SET_TOKEN('OVERHEADLINE_ID','Burdenline Id ' || p_dtl_tbl(i).burdenline_id);
656 FND_MSG_PUB.Add;
657 ELSE
658 FND_MESSAGE.SET_NAME('GMF','GMF_API_BRDN_DEL_FAILED_DTLS');
659 FND_MESSAGE.SET_TOKEN('RESOURCE', p_dtl_tbl(i).resources);
660 FND_MESSAGE.SET_TOKEN('CMPNTCLS_ID', p_dtl_tbl(i).cost_cmpntcls_id);
661 FND_MESSAGE.SET_TOKEN('ALYS_CODE', p_dtl_tbl(i).cost_analysis_code);
662 FND_MSG_PUB.Add;
663 END IF ;
664 END IF ;
665 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
666 RAISE ;
667 END ;
668 END LOOP ;
669
670 -- Standard check of p_commit.
671 IF FND_API.To_Boolean( p_commit ) THEN
672 COMMIT WORK;
673 END IF;
674
675 -- Standard call to get message count and if count is 1, get message info.
676 FND_MSG_PUB.Count_And_Get
677 ( p_count => x_msg_count ,
678 p_data => x_msg_data
679 );
680
681 EXCEPTION
682 WHEN FND_API.G_EXC_ERROR THEN
683 ROLLBACK TO Update_Burden_Details_PVT;
684 x_return_status := FND_API.G_RET_STS_ERROR ;
685 FND_MSG_PUB.Count_And_Get
686 ( p_count => x_msg_count ,
687 p_data => x_msg_data
688 );
689 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
690 ROLLBACK TO Update_Burden_Details_PVT;
691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
692 FND_MSG_PUB.Count_And_Get
693 ( p_count => x_msg_count ,
694 p_data => x_msg_data
695 );
696 WHEN OTHERS THEN
697 ROLLBACK TO Update_Burden_Details_PVT;
698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
699 IF FND_MSG_PUB.Check_Msg_Level
700 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
701 THEN
702 FND_MSG_PUB.Add_Exc_Msg
703 ( G_PKG_NAME ,
704 l_api_name
705 );
706 END IF;
707 FND_MSG_PUB.Count_And_Get
708 ( p_count => x_msg_count ,
709 p_data => x_msg_data
710 );
711
712 END Update_Burden_Details ;
713
714 --Start of comments
715 --+========================================================================+
716 --| API Name : Get_Burden_Details |
717 --| TYPE : Private |
718 --| Function : Retrieve Burden Details based on the input from CM_BRDN_DTL|
719 --| Pre-reqa : None. |
720 --| Parameters : |
721 --| IN : |
722 --| p_api_version IN NUMBER - Required |
723 --| p_init_msg_list IN VARCHAR2 - Optional |
724 --| p_header_rec IN Burden_Header_Rec_Type |
725 --| OUT : |
726 --| x_return_status OUT VARCHAR2 |
727 --| x_msg_count OUT NUMBER |
728 --| x_msg_data OUT VARCHAR2 |
729 --| x_dtl_tbl OUT Burden_Dtl_Tbl_Type |
730 --| |
731 --| Version : |
732 --| Current Version : 2.0 |
733 --| Previous Version : 1.0 |
734 --| Initial Version : 1.0 |
735 --| |
736 --| Notes : |
737 --| |
738 --| HISTORY |
739 --| 26-Apr-01 Uday Moogala - Created |
740 --| |
741 --+========================================================================+
742 -- End of comments
743 PROCEDURE Get_Burden_Details
744 (
745 p_api_version IN NUMBER ,
746 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
747
748 x_return_status OUT NOCOPY VARCHAR2 ,
749 x_msg_count OUT NOCOPY VARCHAR2 ,
750 x_msg_data OUT NOCOPY VARCHAR2 ,
751
752 p_header_rec IN GMF_BurdenDetails_PUB.Burden_Header_Rec_Type ,
753
754 x_dtl_tbl OUT NOCOPY GMF_BurdenDetails_PUB.Burden_Dtl_Tbl_Type
755 )
756 IS
757
758 l_api_name CONSTANT VARCHAR2(30) := 'Get_Burden_Details' ;
759 l_api_version CONSTANT NUMBER := 2.0 ;
760
761 l_idx NUMBER := 0 ;
762
763 CURSOR cm_brdn_dtl
764 IS
765 SELECT
766 b.burdenline_id
767 , b.resources
768 , b.cost_cmpntcls_id
769 , c.cost_cmpntcls_code
770 , b.cost_analysis_code
771 , b.burden_usage
772 , b.item_qty
773 , b.item_uom
774 , b.burden_qty
775 , b.burden_uom
776 , b.burden_factor
777 , b.delete_mark
778 FROM
779 cm_cmpt_mst c, cm_brdn_dtl b
780 WHERE
781 b.organization_id = p_header_rec.organization_id
782 AND b.inventory_item_id = p_header_rec.inventory_item_id
783 AND b.period_id = p_header_rec.period_id
784 AND b.cost_type_id = p_header_rec.cost_type_id
785 AND c.cost_cmpntcls_id = b.cost_cmpntcls_id
786 ORDER BY
787 b.resources
788 , b.cost_cmpntcls_id
789 , b.cost_analysis_code
790 ;
791 BEGIN
792
793 -- Standard Start of API savepoint
794 SAVEPOINT Get_Burden_Details_PVT;
795 -- Initialize message list if p_init_msg_list is set to TRUE.
796 IF FND_API.to_Boolean( p_init_msg_list ) THEN
797 FND_MSG_PUB.initialize;
798 END IF;
799
800 -- Standard call to check for call compatibility.
801 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
802 p_api_version ,
803 l_api_name ,
804 G_PKG_NAME )
805 THEN
806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
807 END IF;
808
809 -- Initialize API return status to success
810 x_return_status := FND_API.G_RET_STS_SUCCESS;
811
812 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
813 log_msg('Beginning Private Get Item Cost API.');
814 END IF;
815
816 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
817 log_msg('Retrieving Burden Details for Item ' || p_header_rec.inventory_item_id ||
818 ' organization_id ' || p_header_rec.organization_id || ' organization_code ' || p_header_rec.organization_code ||
819 ' Calendar ' || p_header_rec.calendar_code || ' Period ' || p_header_rec.Period_code ||
820 ' Mthd ' || p_header_rec.cost_mthd_code ) ;
821 END IF;
822
823 FOR cr_rec IN cm_brdn_dtl
824 LOOP
825 l_idx := l_idx + 1 ;
826 x_dtl_tbl(l_idx).burdenline_id := cr_rec.burdenline_id ;
827 x_dtl_tbl(l_idx).resources := cr_rec.resources ;
828 x_dtl_tbl(l_idx).cost_cmpntcls_id := cr_rec.cost_cmpntcls_id ;
829 x_dtl_tbl(l_idx).cost_cmpntcls_code := cr_rec.cost_cmpntcls_code ;
830 x_dtl_tbl(l_idx).cost_analysis_code := cr_rec.cost_analysis_code ;
831 x_dtl_tbl(l_idx).burden_usage := cr_rec.burden_usage ;
832 x_dtl_tbl(l_idx).item_qty := cr_rec.item_qty ;
833 x_dtl_tbl(l_idx).item_uom := cr_rec.item_uom ;
834 x_dtl_tbl(l_idx).burden_qty := cr_rec.burden_qty ;
835 x_dtl_tbl(l_idx).burden_uom := cr_rec.burden_uom ;
836 x_dtl_tbl(l_idx).burden_factor := cr_rec.burden_factor ;
837 x_dtl_tbl(l_idx).delete_mark := cr_rec.delete_mark ;
838
839 END LOOP ;
840
841
842 /*
843 -- Standard check of p_commit.
844 IF FND_API.To_Boolean( p_commit ) THEN
845 COMMIT WORK;
846 END IF;
847 */
848
849 -- Standard call to get message count and if count is 1, get message info.
850 FND_MSG_PUB.Count_And_Get
851 ( p_count => x_msg_count ,
852 p_data => x_msg_data
853 );
854
855 EXCEPTION
856 WHEN FND_API.G_EXC_ERROR THEN
857 ROLLBACK TO Get_Burden_Details_PVT;
858 x_return_status := FND_API.G_RET_STS_ERROR ;
859 FND_MSG_PUB.Count_And_Get
860 ( p_count => x_msg_count ,
861 p_data => x_msg_data
862 );
863 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
864 ROLLBACK TO Get_Burden_Details_PVT;
865 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
866 FND_MSG_PUB.Count_And_Get
867 ( p_count => x_msg_count ,
868 p_data => x_msg_data
869 );
870 WHEN OTHERS THEN
871 ROLLBACK TO Get_Burden_Details_PVT;
872 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
873 IF FND_MSG_PUB.Check_Msg_Level
874 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
875 THEN
876 FND_MSG_PUB.Add_Exc_Msg
877 ( G_PKG_NAME ,
878 l_api_name
879 );
880 END IF;
881 FND_MSG_PUB.Count_And_Get
882 ( p_count => x_msg_count ,
883 p_data => x_msg_data
884 );
885 END Get_Burden_Details ;
886 --
887 -- Func start of comments
888 --+==========================================================================+
889 --| Function Name |
890 --| check_records_exist |
891 --| |
892 --| DESCRIPTION |
893 --| This procedure checks for the existance of records for a given |
894 --| organization, inventory item id, resource, period id, cost method |
895 --| cost component class and analysis code |
896 --| USAGE |
897 --| In case of insert API, if record exists raise error. |
898 --| In case of update/delete API, if record does not exists raise error|
899 --| |
900 --| PARAMETERS |
901 --| p_organization_id organization id |
902 --| p_inventory_item_id |
903 --| p_resources |
904 --| p_period_id |
905 --| p_cost_type_id |
906 --| p_cost_cmpntcls_id |
907 --| p_cost_analysis_code |
908 --| |
909 --| RETURNS |
910 --| TRUE : If records exist |
911 --| FALSE : If records does not exist |
912 --| |
913 --| HISTORY |
914 --| 3-apr-07 pmarada - created, bug 5589409 |
915 --| |
916 --+==========================================================================+
917 -- Func end of comments
918
919 FUNCTION check_records_exist
920 (
921 p_organization_id IN cm_brdn_dtl.organization_id%TYPE,
922 p_inventory_item_id IN cm_brdn_dtl.inventory_item_id%TYPE,
923 p_resources IN cm_brdn_dtl.resources%TYPE,
924 p_period_id IN cm_brdn_dtl.period_id%TYPE,
925 p_cost_type_id IN cm_brdn_dtl.cost_type_id%TYPE ,
926 p_cost_cmpntcls_id IN cm_brdn_dtl.cost_cmpntcls_id%TYPE,
927 p_cost_analysis_code IN cm_brdn_dtl.cost_analysis_code%TYPE
928 )
929 RETURN BOOLEAN
930 IS
931 CURSOR Cur_burden_dtl
932 ( cp_organization_id cm_brdn_dtl.organization_id%TYPE,
933 cp_inventory_item_id cm_brdn_dtl.inventory_item_id%TYPE,
934 cp_resources cm_brdn_dtl.resources%TYPE ,
935 cp_period_id cm_brdn_dtl.period_id%TYPE,
936 cp_cost_type_id cm_brdn_dtl.cost_type_id%TYPE ,
937 cp_cost_cmpntcls_id cm_brdn_dtl.cost_cmpntcls_id%TYPE,
938 cp_cost_analysis_code cm_brdn_dtl.cost_analysis_code%TYPE
939 )
940 IS
941 SELECT 'x'
942 FROM cm_brdn_dtl
943 WHERE organization_id = cp_organization_id
944 AND inventory_item_id = cp_inventory_item_id
945 AND resources = cp_resources
946 AND period_id = cp_period_id
947 AND cost_type_id = cp_cost_type_id
948 AND cost_cmpntcls_id = cp_cost_cmpntcls_id
949 AND cost_analysis_code= cp_cost_analysis_code;
950
951 l_rec_found VARCHAR2(10);
952 BEGIN
953
954 l_rec_found := NULL;
955 OPEN Cur_burden_dtl(p_organization_id, p_inventory_item_id, p_resources, p_period_id,
956 p_cost_type_id, p_cost_cmpntcls_id, p_cost_analysis_code) ;
957 FETCH Cur_burden_dtl INTO l_rec_found;
958 CLOSE Cur_burden_dtl;
959 IF (l_rec_found IS NOT NULL) THEN
960 RETURN TRUE;
961 ELSE
962 RETURN FALSE ;
963 END IF;
964
965 END check_records_exist;
966
967 -- Func start of comments
968 --+==========================================================================+
969 --| Procedure Name |
970 --| log_msg |
971 --| |
972 --| DESCRIPTION |
973 --| This procedure logs messages to message stack. |
974 --| |
975 --| PARAMETERS |
976 --| p_msg_lvl IN NUMBER(10) - Message Level |
977 --| p_msg_text IN NUMBER(10) - Actual Message Text |
978 --| |
979 --| RETURNS |
980 --| |
981 --| HISTORY |
982 --| 27/02/2001 Uday Moogla - Created |
983 --| |
984 --+==========================================================================+
985 -- Func end of comments
986
987 PROCEDURE log_msg
988 (
989 p_msg_text IN VARCHAR2
990 )
991 IS
992 BEGIN
993
994 -- IF FND_MSG_PUB.Check_Msg_Level (p_msg_lvl) THEN -- Bug 2659435
995 FND_MESSAGE.SET_NAME('GMF','GMF_API_DEBUG');
996 FND_MESSAGE.SET_TOKEN('MSG',p_msg_text);
997 FND_MSG_PUB.Add;
998 -- END IF;
999
1000 END log_msg ;
1001
1002 END GMF_BurdenDetails_PVT;