[Home] [Help]
PACKAGE BODY: APPS.GMF_ITEMCOST_PVT
Source
1 PACKAGE BODY GMF_ITEMCOST_PVT AS
2 /* $Header: GMFVCSTB.pls 120.1.12000000.3 2007/05/11 17:47:32 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 : GMFVCSTB.pls |
11 --| Package Name : GMF_ItemCost_PVT |
12 --| API name : GMF_ItemCost_PVT |
13 --| Type : Public |
14 --| Pre-reqs : N/A |
15 --| Function : Item Cost 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_Item_Cost |
25 --| Update_Item_Cost |
26 --| Delete_Item_Cost |
27 --| Get_Item_Cost |
28 --| |
29 --| Notes |
30 --| This package contains public procedures relating to Item Cost |
31 --| creation, updatation and deletetion. |
32 --| |
33 --| HISTORY |
34 --| 27/Feb/2001 Uday Moogala Created Bug# 1418689 |
35 --| |
36 --| 30-OCT-2002 RajaSekhar 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 --+==========================================================================+
46 -- End of comments
47
48 /*******************
49 * Global variables *
50 *******************/
51 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMF_ItemCost_PVT';
52 G_debug_level NUMBER(2) := FND_MSG_PUB.G_Msg_Level_Threshold; -- Use this variable everywhere to decide to log a debug msg.
53
54 -- Func start of comments
55 --+==========================================================================+
56 --| Procedure Name |
57 --| log_msg |
58 --| |
59 --| DESCRIPTION |
60 --| This procedure logs messages to message stack. |
61 --| |
62 --| PARAMETERS |
63 --| p_msg_lvl IN NUMBER(10) - Message Level |
64 --| p_msg_text IN NUMBER(10) - Actual Message Text |
65 --| |
66 --| RETURNS |
67 --| |
68 --| HISTORY |
69 --| 27/02/2001 Uday Moogla - Created |
70 --| |
71 --+==========================================================================+
72 -- Func end of comments
73
74 PROCEDURE log_msg
75 (
76 p_msg_text IN VARCHAR2
77 )
78 IS
79 BEGIN
80 FND_MESSAGE.SET_NAME('GMF','GMF_API_DEBUG');
81 FND_MESSAGE.SET_TOKEN('MSG',p_msg_text);
82 FND_MSG_PUB.Add;
83 END log_msg ;
84 --
85 -- Func start of comments
86 --+==========================================================================+
87 --| Function Name |
88 --| check_records_exist |
89 --| |
90 --| DESCRIPTION |
91 --| This procedure checks for the existance of records for a given |
92 --| organization, inventory item id, period id, cost method |
93 --| cost component class analysis code and cost level |
94 --| USAGE |
95 --| In case of insert API, if record exists raise error. |
96 --| In case of update/delete API, if record does not exists raise error|
97 --| |
98 --| PARAMETERS |
99 --| p_organization_id |
100 --| p_inventory_item_id |
101 --| p_period_id |
102 --| p_cost_type_id |
103 --| p_cost_cmpntcls_id |
104 --| p_cost_analysis_code |
105 --| p_cost_level |
106 --| |
107 --| RETURNS |
108 --| TRUE : If records exist |
109 --| FALSE : If records does not exist |
110 --| |
111 --| HISTORY |
112 --| 10-may-07 pmarada - created, bug 5586406 |
113 --| |
114 --+==========================================================================+
115 -- Func end of comments
116 FUNCTION check_records_exist
117 (
118 p_organization_id IN cm_cmpt_dtl.organization_id%TYPE,
119 p_inventory_item_id IN cm_cmpt_dtl.inventory_item_id%TYPE,
120 p_period_id IN cm_cmpt_dtl.period_id%TYPE,
121 p_cost_type_id IN cm_cmpt_dtl.cost_type_id%TYPE ,
122 p_cost_cmpntcls_id IN cm_cmpt_dtl.cost_cmpntcls_id%TYPE,
123 p_cost_analysis_code IN cm_cmpt_dtl.cost_analysis_code%TYPE,
124 p_cost_level IN cm_cmpt_dtl.cost_level%TYPE
125 )
126 RETURN BOOLEAN IS
127
128
129 CURSOR Cur_cmpt_dtl
130 ( cp_organization_id cm_cmpt_dtl.organization_id%TYPE,
131 cp_inventory_item_id cm_cmpt_dtl.inventory_item_id%TYPE,
132 cp_period_id cm_cmpt_dtl.period_id%TYPE,
133 cp_cost_type_id cm_cmpt_dtl.cost_type_id%TYPE ,
134 cp_cost_cmpntcls_id cm_cmpt_dtl.cost_cmpntcls_id%TYPE,
135 cp_cost_analysis_code cm_cmpt_dtl.cost_analysis_code%TYPE,
136 cp_cost_level cm_cmpt_dtl.cost_level%TYPE
137 )
138 IS
139 SELECT 'x'
140 FROM cm_cmpt_dtl
141 WHERE organization_id = cp_organization_id
142 AND inventory_item_id = cp_inventory_item_id
143 AND period_id = cp_period_id
144 AND cost_type_id = cp_cost_type_id
145 AND cost_cmpntcls_id = cp_cost_cmpntcls_id
146 AND cost_analysis_code= cp_cost_analysis_code
147 AND cost_level = cp_cost_level;
148
149 l_rec_found VARCHAR2(10);
150 BEGIN
151
152 l_rec_found := NULL;
153 OPEN Cur_cmpt_dtl(p_organization_id, p_inventory_item_id, p_period_id,
154 p_cost_type_id, p_cost_cmpntcls_id, p_cost_analysis_code, p_cost_level) ;
155 FETCH Cur_cmpt_dtl INTO l_rec_found;
156 CLOSE Cur_cmpt_dtl;
157 IF (l_rec_found IS NOT NULL) THEN
158 RETURN TRUE;
159 ELSE
160 RETURN FALSE ;
161 END IF;
162
163 END check_records_exist;
164
165 --Start of comments
166 --+========================================================================+
167 --| API Name : Create_Item_Cost |
168 --| TYPE : Public |
169 --| Function : Creates a new Item Cost based on the input into table |
170 --| CM_CMPT_DTL |
171 --| Pre-reqa : None. |
172 --| Parameters : |
173 --| IN : |
174 --| p_api_version IN NUMBER - Required |
175 --| p_init_msg_list IN VARCHAR2 - Optional |
176 --| p_commit IN VARCHAR2 - Optional |
177 --| p_header_rec IN Header_Rec_Type |
178 --| p_this_level_dtl_tbl IN This_Level_Dtl_Tbl_Type |
179 --| p_lower_level_dtl_Tbl IN Lower_Level_Dtl_Tbl_Type |
180 --| OUT : |
181 --| x_return_status OUT VARCHAR2 |
182 --| x_msg_count OUT NUMBER |
183 --| x_msg_data OUT VARCHAR2 |
184 --| x_costcmpnt_ids OUT costcmpnt_ids_tbl_type |
185 --| |
186 --| Version : |
187 --| Current Version : 2.0 |
188 --| Previous Version : 1.0 |
189 --| Initial Version : 1.0 |
190 --| |
191 --| Notes : |
192 --| |
193 --| HISTORY |
194 --| 01-Mar-01 Uday Moogala - Created |
195 --| 10-may-07 Prasad marada Bug 5586406 Added duplicate record check |
196 --| |
197 --+========================================================================+
198 -- End of comments
199
200 PROCEDURE Create_Item_Cost
201 (
202 p_api_version IN NUMBER,
203 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
204 p_commit IN VARCHAR2 := FND_API.G_FALSE,
205 x_return_status OUT NOCOPY VARCHAR2,
206 x_msg_count OUT NOCOPY NUMBER,
207 x_msg_data OUT NOCOPY VARCHAR2,
208 p_header_rec IN GMF_ItemCost_PUB.Header_Rec_Type,
209 p_this_level_dtl_tbl IN GMF_ItemCost_PUB.This_Level_Dtl_Tbl_Type,
210 p_lower_level_dtl_Tbl IN GMF_ItemCost_PUB.Lower_Level_Dtl_Tbl_Type,
211 p_user_id IN fnd_user.user_id%TYPE,
212 x_costcmpnt_ids OUT NOCOPY GMF_ItemCost_PUB.costcmpnt_ids_tbl_type
213 )
214 IS
215
216 /******************
217 * Local Variables *
218 ******************/
219 l_api_name CONSTANT VARCHAR2(30) := 'Create_Item_Cost' ;
220 l_api_version CONSTANT NUMBER := 3.0 ;
221 l_cmpntcost_id cm_cmpt_dtl.cmpntcost_id%TYPE ;
222 l_idx NUMBER(10) := 0 ;
223
224 BEGIN
225 /**********************************
226 * Standard Start of API savepoint *
227 **********************************/
228 SAVEPOINT CREATE_ITEM_COST_PVT ;
229
230 /*************************************************************
231 * Initialize message list if p_init_msg_list is set to TRUE. *
232 *************************************************************/
233 IF FND_API.to_Boolean( p_init_msg_list )
234 THEN
235 FND_MSG_PUB.initialize;
236 END IF;
237
238 /*************************************************
239 * Standard call to check for call compatibility. *
240 *************************************************/
241 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
242 THEN
243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
244 END IF;
245
246 /******************************************
247 * Initialize API return status to success *
248 ******************************************/
249 x_return_status := FND_API.G_RET_STS_SUCCESS;
250
251 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
252 THEN
253 log_msg('Beginning Private Create Item Cost API.');
254 END IF;
255
256 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
257 THEN
258 log_msg(' Inserting Component Costs for Item ' || p_header_rec.inventory_item_id ||
259 ' Organization Id ' || p_header_rec.organization_id ||
260 ' Period Id ' || p_header_rec.period_id ||
261 ' Cost Type Id ' || p_header_rec.cost_type_id);
262 log_msg(' This level Count : ' || p_this_level_dtl_tbl.count);
263 END IF;
264
265 FOR i in 1..p_this_level_dtl_tbl.count
266 LOOP
267
268 /*Check for duplicate record bug 5586406 */
269 IF check_records_exist(
270 p_organization_id => p_header_rec.organization_id,
271 p_inventory_item_id => p_header_rec.inventory_item_id,
272 p_period_id => p_header_rec.period_id,
273 p_cost_type_id => p_header_rec.cost_type_id,
274 p_cost_cmpntcls_id => p_this_level_dtl_tbl(i).cost_cmpntcls_id,
275 p_cost_analysis_code => p_this_level_dtl_tbl(i).cost_analysis_code,
276 p_cost_level => 0
277 ) THEN
278 FND_MESSAGE.SET_NAME('GMF','GMF_API_DUPLICATE_ITEM_COST');
279 FND_MESSAGE.SET_TOKEN('ORGANIZATION_ID',p_header_rec.organization_id);
280 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID',p_header_rec.inventory_item_id);
281 FND_MESSAGE.SET_TOKEN('PERIOD_ID',p_header_rec.period_id);
282 FND_MESSAGE.SET_TOKEN('COST_TYPE_ID',p_header_rec.cost_type_id);
283 FND_MESSAGE.SET_TOKEN('COST_CMPNTCLS_ID',p_this_level_dtl_tbl(i).cost_cmpntcls_id);
284 FND_MESSAGE.SET_TOKEN('COST_ANALYSIS_CODE',p_this_level_dtl_tbl(i).cost_analysis_code);
285 FND_MESSAGE.SET_TOKEN('COST_LEVEL',0);
286 FND_MSG_PUB.Add;
287 RAISE FND_API.G_EXC_ERROR;
288 END IF;
289 /* end duplicate record check */
290
291 SELECT gem5_cmpnt_cost_id_s.NEXTVAL
292 INTO l_cmpntcost_id
293 FROM DUAL ;
294
295 /***********************************************************************************************
296 * Using anonymous block to capture any error for the current record. Duplicate record check is *
297 * not done in public API because of the performance considerations. *
298 * In case of failure error msg will be logged and will continue with the next record *
299 ***********************************************************************************************/
300 BEGIN
301 INSERT INTO cm_cmpt_dtl
302 (
303 cmpntcost_id,
304 inventory_item_id,
305 organization_id,
306 period_id,
307 cost_type_id,
308 cost_cmpntcls_id,
309 cost_analysis_code,
310 cost_level,
311 cmpnt_cost,
312 burden_ind,
313 fmeff_id,
314 rollover_ind,
315 total_qty,
316 costcalc_orig,
317 rmcalc_type,
318 rollup_ref_no,
319 acproc_id,
320 trans_cnt,
321 text_code,
322 delete_mark,
323 request_id,
324 program_application_id,
325 program_id,
326 program_update_date,
327 attribute1,
328 attribute2,
329 attribute3,
330 attribute4,
331 attribute5,
332 attribute6,
333 attribute7,
334 attribute8,
335 attribute9,
336 attribute10,
337 attribute11,
338 attribute12,
339 attribute13,
340 attribute14,
341 attribute15,
342 attribute16,
343 attribute17,
344 attribute18,
345 attribute19,
346 attribute20,
347 attribute21,
348 attribute22,
349 attribute23,
350 attribute24,
351 attribute25,
352 attribute26,
353 attribute27,
354 attribute28,
355 attribute29,
356 attribute30,
357 attribute_category,
358 creation_date,
359 created_by,
360 last_update_date,
361 last_updated_by,
362 last_update_login
363 )
364 VALUES
365 (
366 l_cmpntcost_id,
367 p_header_rec.inventory_item_id,
368 p_header_rec.organization_id,
369 p_header_rec.period_id,
370 p_header_rec.cost_type_id,
371 p_this_level_dtl_tbl(i).cost_cmpntcls_id,
372 p_this_level_dtl_tbl(i).cost_analysis_code,
373 0, -- Cost Level
374 p_this_level_dtl_tbl(i).cmpnt_cost,
375 p_this_level_dtl_tbl(i).burden_ind,
376 '', -- effectivity id
377 0, -- rollover indicator
378 decode(p_this_level_dtl_tbl(i).total_qty, FND_API.G_MISS_NUM, '', p_this_level_dtl_tbl(i).total_qty),
379 decode(p_this_level_dtl_tbl(i).costcalc_orig, FND_API.G_MISS_NUM, '', p_this_level_dtl_tbl(i).costcalc_orig),
380 decode(p_this_level_dtl_tbl(i).rmcalc_type, FND_API.G_MISS_NUM, '', p_this_level_dtl_tbl(i).rmcalc_type),
381 '', -- rollup ref#
382 '',-- acproc_id
383 '', -- trans cnt
384 '', -- text code
385 0, --delete mark
386 '',
387 '',
388 '',
389 '',
390 p_this_level_dtl_tbl(i).attribute1,
391 p_this_level_dtl_tbl(i).attribute2,
392 p_this_level_dtl_tbl(i).attribute3,
393 p_this_level_dtl_tbl(i).attribute4,
394 p_this_level_dtl_tbl(i).attribute5,
395 p_this_level_dtl_tbl(i).attribute6,
396 p_this_level_dtl_tbl(i).attribute7,
397 p_this_level_dtl_tbl(i).attribute8,
398 p_this_level_dtl_tbl(i).attribute9,
399 p_this_level_dtl_tbl(i).attribute10,
400 p_this_level_dtl_tbl(i).attribute11,
401 p_this_level_dtl_tbl(i).attribute12,
402 p_this_level_dtl_tbl(i).attribute13,
403 p_this_level_dtl_tbl(i).attribute14,
404 p_this_level_dtl_tbl(i).attribute15,
405 p_this_level_dtl_tbl(i).attribute16,
406 p_this_level_dtl_tbl(i).attribute17,
407 p_this_level_dtl_tbl(i).attribute18,
408 p_this_level_dtl_tbl(i).attribute19,
409 p_this_level_dtl_tbl(i).attribute20,
410 p_this_level_dtl_tbl(i).attribute21,
411 p_this_level_dtl_tbl(i).attribute22,
412 p_this_level_dtl_tbl(i).attribute23,
413 p_this_level_dtl_tbl(i).attribute24,
414 p_this_level_dtl_tbl(i).attribute25,
415 p_this_level_dtl_tbl(i).attribute26,
416 p_this_level_dtl_tbl(i).attribute27,
417 p_this_level_dtl_tbl(i).attribute28,
418 p_this_level_dtl_tbl(i).attribute29,
419 p_this_level_dtl_tbl(i).attribute30,
420 p_this_level_dtl_tbl(i).attribute_category,
421 sysdate,
422 p_user_id,
423 sysdate,
424 p_user_id,
425 FND_GLOBAL.LOGIN_ID
426 );
427
428 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
429 THEN
430 log_msg(' 1 this level row inserted for ' ||
431 ' Cmptcls Id ' || p_this_level_dtl_tbl(i).cost_cmpntcls_id ||
432 ' Analysis Code ' || p_this_level_dtl_tbl(i).cost_analysis_code ||
433 ' Cmpntcost Id ' || l_cmpntcost_id);
434 END IF;
435
436 l_idx := l_idx + 1 ;
437
438 x_costcmpnt_ids(l_idx).cost_cmpntcls_id := p_this_level_dtl_tbl(i).cost_cmpntcls_id ;
439 x_costcmpnt_ids(l_idx).cost_analysis_code := p_this_level_dtl_tbl(i).cost_analysis_code ;
440 x_costcmpnt_ids(l_idx).cost_level := 0 ;
441 x_costcmpnt_ids(l_idx).cmpntcost_id := l_cmpntcost_id ;
442
443 EXCEPTION
444 WHEN OTHERS THEN
445 x_costcmpnt_ids.delete ;
446 FND_MESSAGE.SET_NAME('GMF','GMF_API_IC_THISLVL_INS_FAILED');
447 FND_MESSAGE.SET_TOKEN('CMPNTCLS_ID', p_this_level_dtl_tbl(i).cost_cmpntcls_id);
448 FND_MESSAGE.SET_TOKEN('ALYS_CODE', p_this_level_dtl_tbl(i).cost_analysis_code);
449 FND_MSG_PUB.Add;
450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
451 RAISE ;
452 END ;
453 END LOOP ;
454 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
455 THEN
456 log_msg('Lower level Count : ' || p_lower_level_dtl_tbl.count);
457 END IF;
458
459 FOR i in 1..p_lower_level_dtl_tbl.count
460 LOOP
461 /*Check for duplicate record bug 5586406 */
462 IF check_records_exist(
463 p_organization_id => p_header_rec.organization_id,
464 p_inventory_item_id => p_header_rec.inventory_item_id,
465 p_period_id => p_header_rec.period_id,
466 p_cost_type_id => p_header_rec.cost_type_id,
467 p_cost_cmpntcls_id => p_lower_level_dtl_tbl(i).cost_cmpntcls_id,
468 p_cost_analysis_code => p_lower_level_dtl_tbl(i).cost_analysis_code,
469 p_cost_level => 1
470 ) THEN
471 FND_MESSAGE.SET_NAME('GMF','GMF_API_DUPLICATE_ITEM_COST');
472 FND_MESSAGE.SET_TOKEN('ORGANIZATION_ID',p_header_rec.organization_id);
473 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID',p_header_rec.inventory_item_id);
474 FND_MESSAGE.SET_TOKEN('PERIOD_ID',p_header_rec.period_id);
475 FND_MESSAGE.SET_TOKEN('COST_TYPE_ID',p_header_rec.cost_type_id);
476 FND_MESSAGE.SET_TOKEN('COST_CMPNTCLS_ID',p_lower_level_dtl_tbl(i).cost_cmpntcls_id);
477 FND_MESSAGE.SET_TOKEN('COST_ANALYSIS_CODE',p_lower_level_dtl_tbl(i).cost_analysis_code);
478 FND_MESSAGE.SET_TOKEN('COST_LEVEL',1);
479 FND_MSG_PUB.Add;
480 RAISE FND_API.G_EXC_ERROR;
481 END IF;
482 /* end duplicate record check */
483
484 SELECT gem5_cmpnt_cost_id_s.NEXTVAL
485 INTO l_cmpntcost_id
486 FROM DUAL ;
487
488 /***********************************************************************************************
489 * Using anonymous block to capture any error for the current record. Duplicate record check is *
490 * not done in public API because of the performance considerations. *
491 * In case of failure error msg will be logged and will continue with the next record *
492 ***********************************************************************************************/
493 BEGIN
494 INSERT INTO cm_cmpt_dtl
495 (
496 cmpntcost_id,
497 inventory_item_id,
498 organization_id,
499 period_id,
500 cost_type_id,
501 cost_cmpntcls_id,
502 cost_analysis_code,
503 cost_level,
504 cmpnt_cost,
505 burden_ind,
506 fmeff_id,
507 rollover_ind,
508 total_qty,
509 costcalc_orig,
510 rmcalc_type,
511 rollup_ref_no,
512 acproc_id,
513 trans_cnt,
514 text_code,
515 delete_mark,
516 request_id,
517 program_application_id,
518 program_id,
519 program_update_date,
520 attribute1,
521 attribute2,
522 attribute3,
523 attribute4,
524 attribute5,
525 attribute6,
526 attribute7,
527 attribute8,
528 attribute9,
529 attribute10,
530 attribute11,
531 attribute12,
532 attribute13,
533 attribute14,
534 attribute15,
535 attribute16,
536 attribute17,
537 attribute18,
538 attribute19,
539 attribute20,
540 attribute21,
541 attribute22,
542 attribute23,
543 attribute24,
544 attribute25,
545 attribute26,
546 attribute27,
547 attribute28,
548 attribute29,
549 attribute30,
550 attribute_category,
551 creation_date,
552 created_by,
553 last_update_date,
554 last_updated_by,
555 last_update_login
556 )
557 VALUES
558 (
559 l_cmpntcost_id,
560 p_header_rec.inventory_item_id,
561 p_header_rec.organization_id,
562 p_header_rec.period_id,
563 p_header_rec.cost_type_id,
564 p_lower_level_dtl_tbl(i).cost_cmpntcls_id,
565 p_lower_level_dtl_tbl(i).cost_analysis_code,
566 1, -- cost level : this level
567 p_lower_level_dtl_tbl(i).cmpnt_cost,
568 0, --p_lower_level_dtl_tbl(i).burden_ind
569 '', -- effectivity id
570 0, -- rollover indicator
571 '', -- total qty
572 3, -- costcalc_orig insert default value 3 as API Load
573 '', -- rmcalc_type
574 '', -- rollup ref#
575 '', --acproc_id
576 '', -- trans_cnt
577 '', -- text_code
578 0, -- delete mark
579 '', -- request id
580 '', -- appl id
581 '', -- program id
582 '', -- program_update_date
583 '', -- attribute1
584 '',
585 '',
586 '',
587 '',
588 '',
589 '',
590 '',
591 '',
592 '',
593 '',
594 '',
595 '',
596 '',
597 '',
598 '',
599 '',
600 '',
601 '',
602 '',
603 '',
604 '',
605 '',
606 '',
607 '',
608 '',
609 '',
610 '',
611 '',
612 '', -- attribute30
613 '', -- attribute_category
614 sysdate,
615 p_user_id,
616 sysdate,
617 p_user_id,
618 FND_GLOBAL.LOGIN_ID
619 );
620
621 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
622 THEN
623 log_msg(' 1 lower level row inserted for ' ||
624 ' Cmptcls Id ' || p_lower_level_dtl_Tbl(i).cost_cmpntcls_id ||
625 ' Analysis Code ' || p_lower_level_dtl_Tbl(i).cost_analysis_code ||
626 ' Cmpntcost Id ' || l_cmpntcost_id);
627 END IF;
628
629 l_idx := l_idx + 1 ;
630
631 x_costcmpnt_ids(l_idx).cost_cmpntcls_id := p_lower_level_dtl_Tbl(i).cost_cmpntcls_id ;
632 x_costcmpnt_ids(l_idx).cost_analysis_code := p_lower_level_dtl_Tbl(i).cost_analysis_code ;
633 x_costcmpnt_ids(l_idx).cost_level := 1 ;
634 x_costcmpnt_ids(l_idx).cmpntcost_id := l_cmpntcost_id ;
635 EXCEPTION
636 WHEN OTHERS THEN
637 x_costcmpnt_ids.delete ;
638 FND_MESSAGE.SET_NAME('GMF','GMF_API_IC_LWRLVL_INS_FAILED');
639 FND_MESSAGE.SET_TOKEN('CMPNTCLS_ID', p_lower_level_dtl_Tbl(i).cost_cmpntcls_id);
640 FND_MESSAGE.SET_TOKEN('ALYS_CODE', p_lower_level_dtl_Tbl(i).cost_analysis_code);
641 FND_MSG_PUB.Add;
642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
643 RAISE ;
644 END ;
645 END LOOP ;
646 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
647 THEN
648 log_msg( x_costcmpnt_ids.count || ' Component cost row(s) inserted');
649 END IF;
650
651 /******************************
652 * Standard check of p_commit. *
653 ******************************/
654 IF FND_API.To_Boolean (p_commit)
655 THEN
656 COMMIT WORK;
657 END IF;
658
659 /**************************************************************************
660 * Standard call to get message count and if count is 1, get message info. *
661 **************************************************************************/
662 FND_MSG_PUB.Count_And_Get
663 (
664 p_count => x_msg_count,
665 p_data => x_msg_data
666 );
667 EXCEPTION
668 WHEN FND_API.G_EXC_ERROR THEN
669 ROLLBACK TO Create_Item_Cost_PVT;
670 x_return_status := FND_API.G_RET_STS_ERROR ;
671 FND_MSG_PUB.Count_And_Get
672 (
673 p_count => x_msg_count,
674 p_data => x_msg_data
675 );
676 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
677 ROLLBACK TO Create_Item_Cost_PVT;
678 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
679 FND_MSG_PUB.Count_And_Get
680 (
681 p_count => x_msg_count,
682 p_data => x_msg_data
683 );
684 WHEN OTHERS THEN
685 ROLLBACK TO Create_Item_Cost_PVT;
686 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
687 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
688 THEN
689 FND_MSG_PUB.Add_Exc_Msg
690 (
691 G_PKG_NAME,
692 l_api_name
693 );
694 END IF;
695 FND_MSG_PUB.Count_And_Get
696 (
697 p_count => x_msg_count,
698 p_data => x_msg_data
699 );
700 END CREATE_ITEM_COST;
701
702 --Start of comments
703 --+========================================================================+
704 --| API Name : Update_Item_Cost |
705 --| TYPE : Public |
706 --| Function : Updates Item Cost based on the input into CM_CMPT_DTL |
707 --| Pre-reqa : None. |
708 --| Parameters : |
709 --| IN : |
710 --| p_api_version IN NUMBER - Required |
711 --| p_init_msg_list IN VARCHAR2 - Optional |
712 --| p_commit IN VARCHAR2 - Optional |
713 --| p_header_rec IN Header_Rec_Type |
714 --| p_this_level_dtl_tbl IN This_Level_Dtl_Tbl_Type |
715 --| p_lower_level_dtl_Tbl IN Lower_Level_Dtl_Tbl_Type |
716 --| OUT : |
717 --| x_return_status OUT VARCHAR2 |
718 --| x_msg_count OUT NUMBER |
719 --| x_msg_data OUT VARCHAR2 |
720 --| |
721 --| Version : |
722 --| Current Version : 2.0 |
723 --| Previous Version : 1.0 |
724 --| Initial Version : 1.0 |
725 --| |
726 --| Notes : |
727 --| |
728 --| HISTORY |
729 --| 01-Mar-01 Uday Moogala - Created |
730 --| 05-Apr-07 Prasad marada Bug 5586406, Modified the Cost level =1 in the |
731 --| lower level cost update where clause |
732 --| |
733 --+========================================================================+
734 -- End of comments
735
736 PROCEDURE Update_Item_Cost
737 (
738 p_api_version IN NUMBER,
739 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
740 p_commit IN VARCHAR2 := FND_API.G_FALSE,
741 x_return_status OUT NOCOPY VARCHAR2,
742 x_msg_count OUT NOCOPY NUMBER,
743 x_msg_data OUT NOCOPY VARCHAR2,
744 p_header_rec IN GMF_ItemCost_PUB.Header_Rec_Type,
745 p_this_level_dtl_tbl IN GMF_ItemCost_PUB.This_Level_Dtl_Tbl_Type,
746 p_lower_level_dtl_Tbl IN GMF_ItemCost_PUB.Lower_Level_Dtl_Tbl_Type,
747 p_user_id IN fnd_user.user_id%TYPE
748 )
749 IS
750
751 /******************
752 * Local Variables *
753 ******************/
754 l_api_name CONSTANT VARCHAR2(30) := 'Update_Item_Cost' ;
755 l_api_version CONSTANT NUMBER := 3.0 ;
756 BEGIN
757 /**********************************
758 * Standard Start of API savepoint *
759 **********************************/
760 SAVEPOINT UPDATE_ITEM_COST_PVT ;
761
762 /*************************************************************
763 * Initialize message list if p_init_msg_list is set to TRUE. *
764 *************************************************************/
765 IF FND_API.to_Boolean( p_init_msg_list )
766 THEN
767 FND_MSG_PUB.initialize;
768 END IF;
769
770 /*************************************************
771 * Standard call to check for call compatibility. *
772 *************************************************/
773 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
774 THEN
775 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
776 END IF;
777
778 /******************************************
779 * Initialize API return status to success *
780 ******************************************/
781 x_return_status := FND_API.G_RET_STS_SUCCESS;
782
783 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
784 THEN
785 log_msg('Beginning Private Update Item Cost API.');
786 END IF;
787
788 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
789 THEN
790 log_msg(' This level Count : ' || p_this_level_dtl_tbl.count);
791 log_msg(' Processing Component Costs for Item ' || p_header_rec.inventory_item_id ||
792 ' Organization Id ' || p_header_rec.organization_id ||
793 ' Period Id ' || p_header_rec.Period_id ||
794 ' Cost Type Id ' || p_header_rec.cost_type_id) ;
795 END IF;
796 FOR i in 1..p_this_level_dtl_tbl.count
797 LOOP
798
799 /***********************************************************************************************
800 * Using anonymous block to capture any error for the current record. Duplicate record check is *
801 * not done in public API because of the performance considerations. *
802 * In case of failure error msg will be logged. *
803 ***********************************************************************************************/
804 BEGIN
805 IF (p_this_level_dtl_tbl(i).cmpntcost_id IS NOT NULL) AND (p_this_level_dtl_tbl(i).cmpntcost_id <> FND_API.G_MISS_NUM)
806 THEN
807 IF p_this_level_dtl_tbl(i).delete_mark = 0
808 THEN
809 UPDATE cm_cmpt_dtl
810 SET cmpnt_cost = decode(p_this_level_dtl_tbl(i).cmpnt_cost, FND_API.G_MISS_NUM, NULL, NULL, cmpnt_cost, p_this_level_dtl_tbl(i).cmpnt_cost),
811 burden_ind = decode(p_this_level_dtl_tbl(i).burden_ind, FND_API.G_MISS_NUM, NULL, NULL, burden_ind, p_this_level_dtl_tbl(i).burden_ind),
812 total_qty = decode(p_this_level_dtl_tbl(i).total_qty, FND_API.G_MISS_NUM, NULL, NULL, total_qty, p_this_level_dtl_tbl(i).total_qty),
813 costcalc_orig= decode(p_this_level_dtl_tbl(i).costcalc_orig, FND_API.G_MISS_NUM, NULL, NULL, costcalc_orig, p_this_level_dtl_tbl(i).costcalc_orig),
814 rmcalc_type = decode(p_this_level_dtl_tbl(i).rmcalc_type, FND_API.G_MISS_NUM, NULL, NULL, rmcalc_type, p_this_level_dtl_tbl(i).rmcalc_type),
815 delete_mark = 0,
816 ATTRIBUTE1 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE1, p_this_level_dtl_tbl(i).ATTRIBUTE1),
817 ATTRIBUTE2 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE2, p_this_level_dtl_tbl(i).ATTRIBUTE2),
818 ATTRIBUTE3 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE3, p_this_level_dtl_tbl(i).ATTRIBUTE3),
819 ATTRIBUTE4 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE4, p_this_level_dtl_tbl(i).ATTRIBUTE4),
820 ATTRIBUTE5 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE5, p_this_level_dtl_tbl(i).ATTRIBUTE5),
821 ATTRIBUTE6 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE6, p_this_level_dtl_tbl(i).ATTRIBUTE6),
822 ATTRIBUTE7 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE7, p_this_level_dtl_tbl(i).ATTRIBUTE7),
823 ATTRIBUTE8 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE8, p_this_level_dtl_tbl(i).ATTRIBUTE8),
824 ATTRIBUTE9 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE9, p_this_level_dtl_tbl(i).ATTRIBUTE9),
825 ATTRIBUTE10 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE10, p_this_level_dtl_tbl(i).ATTRIBUTE10),
826 ATTRIBUTE11 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE11, p_this_level_dtl_tbl(i).ATTRIBUTE11),
827 ATTRIBUTE12 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE12, p_this_level_dtl_tbl(i).ATTRIBUTE12),
828 ATTRIBUTE13 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE13, p_this_level_dtl_tbl(i).ATTRIBUTE13),
829 ATTRIBUTE14 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE14, p_this_level_dtl_tbl(i).ATTRIBUTE14),
830 ATTRIBUTE15 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE15, p_this_level_dtl_tbl(i).ATTRIBUTE15),
831 ATTRIBUTE16 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE16, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE16, p_this_level_dtl_tbl(i).ATTRIBUTE16),
832 ATTRIBUTE17 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE17, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE17, p_this_level_dtl_tbl(i).ATTRIBUTE17),
833 ATTRIBUTE18 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE18, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE18, p_this_level_dtl_tbl(i).ATTRIBUTE18),
834 ATTRIBUTE19 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE19, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE19, p_this_level_dtl_tbl(i).ATTRIBUTE19),
835 ATTRIBUTE20 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE20, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE20, p_this_level_dtl_tbl(i).ATTRIBUTE20),
836 ATTRIBUTE21 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE21, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE21, p_this_level_dtl_tbl(i).ATTRIBUTE21),
837 ATTRIBUTE22 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE22, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE22, p_this_level_dtl_tbl(i).ATTRIBUTE22),
838 ATTRIBUTE23 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE23, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE23, p_this_level_dtl_tbl(i).ATTRIBUTE23),
839 ATTRIBUTE24 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE24, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE24, p_this_level_dtl_tbl(i).ATTRIBUTE24),
840 ATTRIBUTE25 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE25, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE25, p_this_level_dtl_tbl(i).ATTRIBUTE25),
841 ATTRIBUTE26 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE26, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE26, p_this_level_dtl_tbl(i).ATTRIBUTE26),
842 ATTRIBUTE27 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE27, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE27, p_this_level_dtl_tbl(i).ATTRIBUTE27),
843 ATTRIBUTE28 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE28, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE28, p_this_level_dtl_tbl(i).ATTRIBUTE28),
844 ATTRIBUTE29 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE29, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE29, p_this_level_dtl_tbl(i).ATTRIBUTE29),
845 ATTRIBUTE30 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE30, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE30, p_this_level_dtl_tbl(i).ATTRIBUTE30),
846 attribute_category = decode(p_this_level_dtl_tbl(i).attribute_category, FND_API.G_MISS_CHAR, NULL, NULL, attribute_category, p_this_level_dtl_tbl(i).attribute_category),
847 last_update_date = sysdate,
848 last_updated_by = p_user_id,
849 last_update_login = FND_GLOBAL.LOGIN_ID
850 WHERE cmpntcost_id = p_this_level_dtl_tbl(i).cmpntcost_id
851 AND cost_level = 0;
852 ELSE -- delete the record i.e mark for purge
853 UPDATE cm_cmpt_dtl
854 SET delete_mark = 1,
855 last_update_date = sysdate,
856 last_updated_by = p_user_id,
857 last_update_login = FND_GLOBAL.LOGIN_ID
858 WHERE cmpntcost_id = p_this_level_dtl_tbl(i).cmpntcost_id
859 AND cost_level = 0;
860 END IF ;
861
862 IF SQL%NOTFOUND
863 THEN
864 IF p_this_level_dtl_tbl(i).delete_mark = 0
865 THEN
866 FND_MESSAGE.SET_NAME('GMF','GMF_API_UPD_IC_NOT_FOUND_ID');
867 FND_MESSAGE.SET_TOKEN('CMPNTCOST_ID', p_this_level_dtl_tbl(i).cmpntcost_id);
868 FND_MSG_PUB.Add;
869 ELSE
870 FND_MESSAGE.SET_NAME('GMF','GMF_API_DEL_IC_NOT_FOUND_ID');
871 FND_MESSAGE.SET_TOKEN('CMPNTCOST_ID', p_this_level_dtl_tbl(i).cmpntcost_id);
872 FND_MSG_PUB.Add;
873 END IF ;
874 x_return_status := FND_API.G_RET_STS_ERROR ;
875 EXIT ;
876 ELSE
877 IF p_this_level_dtl_tbl(i).delete_mark = 0
878 THEN
879 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
880 THEN
881 log_msg(' 1 row updated for Component Cost Id ' || p_this_level_dtl_tbl(i).cmpntcost_id);
882 END IF;
883 ELSE
884 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
885 THEN
886 log_msg(' 1 row deleted for Component Cost Id ' || p_this_level_dtl_tbl(i).cmpntcost_id);
887 END IF;
888 END IF ;
889 END IF ;
890 ELSE -- else if cmpntcost_id is not passed
891 IF p_this_level_dtl_tbl(i).delete_mark = 0
892 THEN
893 UPDATE cm_cmpt_dtl
894 SET cmpnt_cost = decode(p_this_level_dtl_tbl(i).cmpnt_cost, FND_API.G_MISS_NUM, NULL, NULL, cmpnt_cost, p_this_level_dtl_tbl(i).cmpnt_cost),
895 burden_ind = decode(p_this_level_dtl_tbl(i).burden_ind, FND_API.G_MISS_NUM, NULL, NULL, burden_ind, p_this_level_dtl_tbl(i).burden_ind),
896 total_qty = decode(p_this_level_dtl_tbl(i).total_qty, FND_API.G_MISS_NUM, NULL, NULL, total_qty, p_this_level_dtl_tbl(i).total_qty),
897 costcalc_orig= decode(p_this_level_dtl_tbl(i).costcalc_orig, FND_API.G_MISS_NUM, NULL, NULL, costcalc_orig, p_this_level_dtl_tbl(i).costcalc_orig),
898 rmcalc_type = decode(p_this_level_dtl_tbl(i).rmcalc_type, FND_API.G_MISS_NUM, NULL, NULL, rmcalc_type, p_this_level_dtl_tbl(i).rmcalc_type),
899 delete_mark = 0,
900 ATTRIBUTE1 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE1, p_this_level_dtl_tbl(i).ATTRIBUTE1),
901 ATTRIBUTE2 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE2, p_this_level_dtl_tbl(i).ATTRIBUTE2),
902 ATTRIBUTE3 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE3, p_this_level_dtl_tbl(i).ATTRIBUTE3),
903 ATTRIBUTE4 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE4, p_this_level_dtl_tbl(i).ATTRIBUTE4),
904 ATTRIBUTE5 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE5, p_this_level_dtl_tbl(i).ATTRIBUTE5),
905 ATTRIBUTE6 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE6, p_this_level_dtl_tbl(i).ATTRIBUTE6),
906 ATTRIBUTE7 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE7, p_this_level_dtl_tbl(i).ATTRIBUTE7),
907 ATTRIBUTE8 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE8, p_this_level_dtl_tbl(i).ATTRIBUTE8),
908 ATTRIBUTE9 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE9, p_this_level_dtl_tbl(i).ATTRIBUTE9),
909 ATTRIBUTE10 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE10, p_this_level_dtl_tbl(i).ATTRIBUTE10),
910 ATTRIBUTE11 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE11, p_this_level_dtl_tbl(i).ATTRIBUTE11),
911 ATTRIBUTE12 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE12, p_this_level_dtl_tbl(i).ATTRIBUTE12),
912 ATTRIBUTE13 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE13, p_this_level_dtl_tbl(i).ATTRIBUTE13),
913 ATTRIBUTE14 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE14, p_this_level_dtl_tbl(i).ATTRIBUTE14),
914 ATTRIBUTE15 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE15, p_this_level_dtl_tbl(i).ATTRIBUTE15),
915 ATTRIBUTE16 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE16, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE16, p_this_level_dtl_tbl(i).ATTRIBUTE16),
916 ATTRIBUTE17 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE17, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE17, p_this_level_dtl_tbl(i).ATTRIBUTE17),
917 ATTRIBUTE18 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE18, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE18, p_this_level_dtl_tbl(i).ATTRIBUTE18),
918 ATTRIBUTE19 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE19, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE19, p_this_level_dtl_tbl(i).ATTRIBUTE19),
919 ATTRIBUTE20 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE20, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE20, p_this_level_dtl_tbl(i).ATTRIBUTE20),
920 ATTRIBUTE21 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE21, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE21, p_this_level_dtl_tbl(i).ATTRIBUTE21),
921 ATTRIBUTE22 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE22, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE22, p_this_level_dtl_tbl(i).ATTRIBUTE22),
922 ATTRIBUTE23 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE23, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE23, p_this_level_dtl_tbl(i).ATTRIBUTE23),
923 ATTRIBUTE24 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE24, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE24, p_this_level_dtl_tbl(i).ATTRIBUTE24),
924 ATTRIBUTE25 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE25, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE25, p_this_level_dtl_tbl(i).ATTRIBUTE25),
925 ATTRIBUTE26 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE26, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE26, p_this_level_dtl_tbl(i).ATTRIBUTE26),
926 ATTRIBUTE27 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE27, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE27, p_this_level_dtl_tbl(i).ATTRIBUTE27),
927 ATTRIBUTE28 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE28, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE28, p_this_level_dtl_tbl(i).ATTRIBUTE28),
928 ATTRIBUTE29 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE29, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE29, p_this_level_dtl_tbl(i).ATTRIBUTE29),
929 ATTRIBUTE30 = decode(p_this_level_dtl_tbl(i).ATTRIBUTE30, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE30, p_this_level_dtl_tbl(i).ATTRIBUTE30),
930 attribute_category = decode(p_this_level_dtl_tbl(i).attribute_category, FND_API.G_MISS_CHAR, NULL, NULL, attribute_category, p_this_level_dtl_tbl(i).attribute_category),
931 last_update_date = sysdate,
932 last_updated_by = p_user_id,
933 last_update_login = FND_GLOBAL.LOGIN_ID
934 WHERE inventory_item_id = p_header_rec.inventory_item_id
935 AND organization_id = p_header_rec.organization_id
936 AND period_id = p_header_rec.period_id
937 AND cost_type_id = p_header_rec.cost_type_id
938 AND cost_cmpntcls_id = p_this_level_dtl_tbl(i).cost_cmpntcls_id
939 AND cost_analysis_code = p_this_level_dtl_tbl(i).cost_analysis_code
940 AND cost_level = 0;
941 ELSE
942 UPDATE cm_cmpt_dtl
943 SET delete_mark = 1,
944 last_update_date = sysdate,
945 last_updated_by = p_user_id,
946 last_update_login = FND_GLOBAL.LOGIN_ID
947 WHERE inventory_item_id = p_header_rec.inventory_item_id
948 AND organization_id = p_header_rec.organization_id
949 AND period_id = p_header_rec.period_id
950 AND cost_type_id = p_header_rec.cost_type_id
951 AND cost_cmpntcls_id = p_this_level_dtl_tbl(i).cost_cmpntcls_id
952 AND cost_analysis_code = p_this_level_dtl_tbl(i).cost_analysis_code
953 AND cost_level = 0;
954 END IF ;
955
956 IF SQL%NOTFOUND THEN
957 IF p_this_level_dtl_tbl(i).delete_mark = 0 THEN
958 FND_MESSAGE.SET_NAME('GMF','GMF_API_UPD_IC_NOT_FOUND_DTL');
959 FND_MESSAGE.SET_TOKEN('CMPNTCLS_ID', p_this_level_dtl_tbl(i).cost_cmpntcls_id);
960 FND_MESSAGE.SET_TOKEN('ALYS_CODE', p_this_level_dtl_tbl(i).cost_analysis_code);
961 FND_MSG_PUB.Add;
962 ELSE
963 FND_MESSAGE.SET_NAME('GMF','GMF_API_DEL_IC_NOT_FOUND_DTL');
964 FND_MESSAGE.SET_TOKEN('CMPNTCLS_ID', p_this_level_dtl_tbl(i).cost_cmpntcls_id);
965 FND_MESSAGE.SET_TOKEN('ALYS_CODE', p_this_level_dtl_tbl(i).cost_analysis_code);
966 FND_MSG_PUB.Add;
967 END IF ;
968 x_return_status := FND_API.G_RET_STS_ERROR ;
969 EXIT ;
970 ELSE
971 IF p_this_level_dtl_tbl(i).delete_mark = 0
972 THEN
973 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
974 THEN
975 log_msg(' 1 row updated for Component Class Id ' || p_this_level_dtl_tbl(i).cost_cmpntcls_id ||
976 ' Analysis Code ' || p_this_level_dtl_tbl(i).cost_analysis_code);
977 END IF;
978 ELSE
979 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
980 THEN
981 log_msg(' 1 row deleted for Component Class Id ' || p_this_level_dtl_tbl(i).cost_cmpntcls_id ||
982 ' Analysis Code ' || p_this_level_dtl_tbl(i).cost_analysis_code);
983 END IF;
984 END IF ;
985 END IF ;
986 END IF ;
987 EXCEPTION
988 WHEN OTHERS THEN
989 IF p_this_level_dtl_tbl(i).delete_mark = 0
990 THEN
991 IF (p_this_level_dtl_tbl(i).cmpntcost_id IS NOT NULL) OR (p_this_level_dtl_tbl(i).cmpntcost_id <> FND_API.G_MISS_NUM)
992 THEN
993 FND_MESSAGE.SET_NAME('GMF','GMF_API_IC_UPD_FAILED_ID');
994 FND_MESSAGE.SET_TOKEN('CMPNTCOST_ID', p_this_level_dtl_tbl(i).cmpntcost_id);
995 FND_MSG_PUB.Add;
996 ELSE
997 FND_MESSAGE.SET_NAME('GMF','GMF_API_IC_UPD_FAILED_DTLS');
998 FND_MESSAGE.SET_TOKEN('CMPNTCLS_ID', p_this_level_dtl_tbl(i).cost_cmpntcls_id);
999 FND_MESSAGE.SET_TOKEN('ALYS_CODE', p_this_level_dtl_tbl(i).cost_analysis_code);
1000 FND_MSG_PUB.Add;
1001 END IF ;
1002 ELSE
1003 IF (p_this_level_dtl_tbl(i).cmpntcost_id IS NOT NULL) OR (p_this_level_dtl_tbl(i).cmpntcost_id <> FND_API.G_MISS_NUM)
1004 THEN
1005 FND_MESSAGE.SET_NAME('GMF','GMF_API_IC_DEL_FAILED_ID');
1006 FND_MESSAGE.SET_TOKEN('CMPNTCOST_ID', p_this_level_dtl_tbl(i).cmpntcost_id);
1007 FND_MSG_PUB.Add;
1008 ELSE
1009 FND_MESSAGE.SET_NAME('GMF','GMF_API_IC_DEL_FAILED_DTLS');
1010 FND_MESSAGE.SET_TOKEN('CMPNTCLS_ID', p_this_level_dtl_tbl(i).cost_cmpntcls_id);
1011 FND_MESSAGE.SET_TOKEN('ALYS_CODE', p_this_level_dtl_tbl(i).cost_analysis_code);
1012 FND_MSG_PUB.Add;
1013 END IF ;
1014 END IF ;
1015 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1016 RAISE ;
1017 END ;
1018 END LOOP ;
1019 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
1020 THEN
1021 log_msg('Lower level Count : ' || p_lower_level_dtl_tbl.count);
1022 END IF;
1023 FOR i in 1..p_lower_level_dtl_tbl.count
1024 LOOP
1025 BEGIN
1026 IF (p_lower_level_dtl_tbl(i).cmpntcost_id IS NOT NULL) AND (p_lower_level_dtl_tbl(i).cmpntcost_id <> FND_API.G_MISS_NUM)
1027 THEN
1028 IF p_lower_level_dtl_tbl(i).delete_mark = 0
1029 THEN
1030 UPDATE cm_cmpt_dtl
1031 SET cmpnt_cost = decode(p_lower_level_dtl_tbl(i).cmpnt_cost, FND_API.G_MISS_NUM, NULL, NULL, cmpnt_cost, p_lower_level_dtl_tbl(i).cmpnt_cost),
1032 delete_mark = 0,
1033 last_update_date = sysdate,
1034 last_updated_by = p_user_id,
1035 last_update_login = FND_GLOBAL.LOGIN_ID
1036 WHERE cmpntcost_id = p_lower_level_dtl_tbl(i).cmpntcost_id
1037 AND cost_level = 1;
1038 ELSE
1039 UPDATE cm_cmpt_dtl
1040 SET delete_mark = 1,
1041 last_update_date = sysdate,
1042 last_updated_by = p_user_id,
1043 last_update_login = FND_GLOBAL.LOGIN_ID
1044 WHERE cmpntcost_id = p_lower_level_dtl_tbl(i).cmpntcost_id
1045 AND cost_level = 1;
1046 END IF ;
1047
1048 IF SQL%NOTFOUND
1049 THEN
1050 IF p_lower_level_dtl_tbl(i).delete_mark = 0
1051 THEN
1052 FND_MESSAGE.SET_NAME('GMF','GMF_API_UPD_IC_NOT_FOUND_ID');
1053 FND_MESSAGE.SET_TOKEN('CMPNTCOST_ID', p_lower_level_dtl_tbl(i).cmpntcost_id);
1054 FND_MSG_PUB.Add;
1055 ELSE
1056 FND_MESSAGE.SET_NAME('GMF','GMF_API_DEL_IC_NOT_FOUND_ID');
1057 FND_MESSAGE.SET_TOKEN('CMPNTCOST_ID', p_lower_level_dtl_tbl(i).cmpntcost_id);
1058 FND_MSG_PUB.Add;
1059 END IF ;
1060 x_return_status := FND_API.G_RET_STS_ERROR ;
1061 EXIT ;
1062 ELSE
1063 IF p_lower_level_dtl_tbl(i).delete_mark = 0
1064 THEN
1065 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
1066 THEN
1067 log_msg(' 1 row updated for Component Cost Id ' || p_lower_level_dtl_tbl(i).cmpntcost_id);
1068 END IF;
1069 ELSE
1070 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
1071 THEN
1072 log_msg(' 1 row deleted for Component Cost Id ' || p_lower_level_dtl_tbl(i).cmpntcost_id);
1073 END IF;
1074 END IF ;
1075 END IF ;
1076 ELSE -- cmpntcost_id is not passed
1077 IF p_lower_level_dtl_tbl(i).delete_mark = 0
1078 THEN
1079 UPDATE cm_cmpt_dtl
1080 SET cmpnt_cost = decode(p_lower_level_dtl_tbl(i).cmpnt_cost, FND_API.G_MISS_NUM, NULL, NULL, cmpnt_cost, p_lower_level_dtl_tbl(i).cmpnt_cost ),
1081 delete_mark = 0,
1082 last_update_date = sysdate,
1083 last_updated_by = p_user_id,
1084 last_update_login = FND_GLOBAL.LOGIN_ID
1085
1086 WHERE inventory_item_id = p_header_rec.inventory_item_id
1087 AND organization_id = p_header_rec.organization_id
1088 AND period_id = p_header_rec.period_id
1089 AND cost_type_id = p_header_rec.cost_type_id
1090 AND cost_cmpntcls_id = p_lower_level_dtl_tbl(i).cost_cmpntcls_id
1091 AND cost_analysis_code = p_lower_level_dtl_tbl(i).cost_analysis_code
1092 AND cost_level = 1;
1093 ELSE
1094 UPDATE cm_cmpt_dtl
1095 SET delete_mark = 1,
1096 last_update_date = sysdate,
1097 last_updated_by = p_user_id,
1098 last_update_login = FND_GLOBAL.LOGIN_ID
1099 WHERE inventory_item_id = p_header_rec.inventory_item_id
1100 AND organization_id = p_header_rec.organization_id
1101 AND period_id = p_header_rec.period_id
1102 AND cost_type_id = p_header_rec.cost_type_id
1103 AND cost_cmpntcls_id = p_lower_level_dtl_tbl(i).cost_cmpntcls_id
1104 AND cost_analysis_code = p_lower_level_dtl_tbl(i).cost_analysis_code
1105 AND cost_level = 1;
1106 END IF ;
1107 IF SQL%NOTFOUND
1108 THEN
1109 IF p_lower_level_dtl_tbl(i).delete_mark = 0
1110 THEN
1111 FND_MESSAGE.SET_NAME('GMF','GMF_API_UPD_IC_NOT_FOUND_DTL');
1112 FND_MESSAGE.SET_TOKEN('CMPNTCLS_ID', p_lower_level_dtl_tbl(i).cost_cmpntcls_id);
1113 FND_MESSAGE.SET_TOKEN('ALYS_CODE', p_lower_level_dtl_tbl(i).cost_analysis_code);
1114 FND_MSG_PUB.Add;
1115 ELSE
1116 FND_MESSAGE.SET_NAME('GMF','GMF_API_DEL_IC_NOT_FOUND_DTL');
1117 FND_MESSAGE.SET_TOKEN('CMPNTCLS_ID', p_lower_level_dtl_tbl(i).cost_cmpntcls_id);
1118 FND_MESSAGE.SET_TOKEN('ALYS_CODE', p_lower_level_dtl_tbl(i).cost_analysis_code);
1119 FND_MSG_PUB.Add;
1120 END IF ;
1121 x_return_status := FND_API.G_RET_STS_ERROR ;
1122 EXIT ;
1123 ELSE
1124 IF p_lower_level_dtl_tbl(i).delete_mark = 0
1125 THEN
1126 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
1127 THEN
1128 log_msg(' 1 row updated for Component Class Id ' || p_lower_level_dtl_tbl(i).cost_cmpntcls_id ||
1129 ' Analysis Code ' || p_lower_level_dtl_tbl(i).cost_analysis_code);
1130 END IF;
1131 ELSE
1132 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
1133 THEN
1134 log_msg(' 1 row deleted for Component Class Id ' ||p_lower_level_dtl_tbl(i).cost_cmpntcls_id ||
1135 ' Analysis Code ' || p_lower_level_dtl_tbl(i).cost_analysis_code);
1136 END IF;
1137 END IF ;
1138 END IF ;
1139 END IF ; --cmpntcost_id check
1140 EXCEPTION
1141 WHEN OTHERS THEN
1142 IF p_lower_level_dtl_tbl(i).delete_mark = 0
1143 THEN
1144 IF (p_lower_level_dtl_tbl(i).cmpntcost_id IS NOT NULL) OR (p_lower_level_dtl_tbl(i).cmpntcost_id <> FND_API.G_MISS_NUM)
1145 THEN
1146 FND_MESSAGE.SET_NAME('GMF','GMF_API_IC_UPD_FAILED_ID');
1147 FND_MESSAGE.SET_TOKEN('CMPNTCOST_ID', p_lower_level_dtl_tbl(i).cmpntcost_id);
1148 FND_MSG_PUB.Add;
1149 ELSE
1150 FND_MESSAGE.SET_NAME('GMF','GMF_API_IC_UPD_FAILED_DTLS');
1151 FND_MESSAGE.SET_TOKEN('CMPNTCLS_ID', p_lower_level_dtl_tbl(i).cost_cmpntcls_id);
1152 FND_MESSAGE.SET_TOKEN('ALYS_CODE', p_lower_level_dtl_tbl(i).cost_analysis_code);
1153 FND_MSG_PUB.Add;
1154 END IF ;
1155 ELSE
1156 IF (p_lower_level_dtl_tbl(i).cmpntcost_id IS NOT NULL) OR (p_lower_level_dtl_tbl(i).cmpntcost_id <> FND_API.G_MISS_NUM)
1157 THEN
1158 FND_MESSAGE.SET_NAME('GMF','GMF_API_IC_DEL_FAILED_ID');
1159 FND_MESSAGE.SET_TOKEN('CMPNTCOST_ID', p_lower_level_dtl_tbl(i).cmpntcost_id);
1160 FND_MSG_PUB.Add;
1161 ELSE
1162 FND_MESSAGE.SET_NAME('GMF','GMF_API_IC_DEL_FAILED_DTLS');
1163 FND_MESSAGE.SET_TOKEN('CMPNTCLS_ID', p_lower_level_dtl_tbl(i).cost_cmpntcls_id);
1164 FND_MESSAGE.SET_TOKEN('ALYS_CODE', p_lower_level_dtl_tbl(i).cost_analysis_code);
1165 FND_MSG_PUB.Add;
1166 END IF ;
1167 END IF ;
1168 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1169 RAISE ;
1170 END ;
1171 END LOOP ;
1172
1173 /******************************
1174 * Standard check of p_commit. *
1175 ******************************/
1176 IF FND_API.To_Boolean( p_commit )
1177 THEN
1178 COMMIT WORK;
1179 END IF;
1180
1181 /**************************************************************************
1182 * Standard call to get message count and if count is 1, get message info. *
1183 **************************************************************************/
1184 FND_MSG_PUB.Count_And_Get
1185 (
1186 p_count => x_msg_count,
1187 p_data => x_msg_data
1188 );
1189 EXCEPTION
1190 WHEN FND_API.G_EXC_ERROR THEN
1191 ROLLBACK TO UPDATE_ITEM_COST_PVT;
1192 x_return_status := FND_API.G_RET_STS_ERROR ;
1193 FND_MSG_PUB.Count_And_Get
1194 (
1195 p_count => x_msg_count,
1196 p_data => x_msg_data
1197 );
1198 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1199 ROLLBACK TO UPDATE_ITEM_COST_PVT;
1200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1201 FND_MSG_PUB.Count_And_Get
1202 (
1203 p_count => x_msg_count,
1204 p_data => x_msg_data
1205 );
1206 WHEN OTHERS THEN
1207 ROLLBACK TO Update_Item_Cost_PVT;
1208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1209 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1210 THEN
1211 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1212 END IF;
1213 FND_MSG_PUB.Count_And_Get
1214 (
1215 p_count => x_msg_count,
1216 p_data => x_msg_data
1217 );
1218 END UPDATE_ITEM_COST ;
1219
1220 --Start of comments
1221 --+========================================================================+
1222 --| API Name : Get_Item_Cost |
1223 --| TYPE : Private |
1224 --| Function : Retrieve Item Cost based on the input from table |
1225 --| CM_CMPT_DTL |
1226 --| Pre-reqa : None. |
1227 --| Parameters : |
1228 --| IN : |
1229 --| p_api_version IN NUMBER - Required |
1230 --| p_init_msg_list IN VARCHAR2 - Optional |
1231 --| p_header_rec IN Header_Rec_Type |
1232 --| x_this_level_dtl_tbl IN This_Level_Dtl_Tbl_Type |
1233 --| x_lower_level_dtl_Tbl IN Lower_Level_Dtl_Tbl_Type |
1234 --| OUT : |
1235 --| x_return_status OUT VARCHAR2 |
1236 --| x_msg_count OUT NUMBER |
1237 --| x_msg_data OUT VARCHAR2 |
1238 --| |
1239 --| Version : |
1240 --| Current Version : 2.0 |
1241 --| Previous Version : 1.0 |
1242 --| Initial Version : 1.0 |
1243 --| |
1244 --| Notes : |
1245 --| |
1246 --| HISTORY |
1247 --| 01-Mar-01 Uday Moogala - Created |
1248 --| |
1249 --+========================================================================+
1250 -- End of comments
1251 PROCEDURE Get_Item_Cost
1252 (
1253 p_api_version IN NUMBER,
1254 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1255 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1256 x_return_status OUT NOCOPY VARCHAR2,
1257 x_msg_count OUT NOCOPY NUMBER,
1258 x_msg_data OUT NOCOPY VARCHAR2,
1259 p_header_rec IN GMF_ItemCost_PUB.Header_Rec_Type,
1260 x_this_level_dtl_tbl OUT NOCOPY GMF_ItemCost_PUB.This_Level_Dtl_Tbl_Type,
1261 x_lower_level_dtl_Tbl OUT NOCOPY GMF_ItemCost_PUB.Lower_Level_Dtl_Tbl_Type
1262 )
1263 IS
1264
1265 /******************
1266 * Local Variables *
1267 ******************/
1268 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Item_Cost' ;
1269 l_api_version CONSTANT NUMBER := 3.0 ;
1270 l_idx NUMBER := 0 ;
1271 l_idx1 NUMBER := 0 ;
1272
1273 /**********
1274 * Cursors *
1275 **********/
1276 CURSOR cm_cmpt_dtl
1277 IS
1278 SELECT cd.cmpntcost_id, cd.cost_cmpntcls_id, cm.cost_cmpntcls_code,
1279 cd.cost_analysis_code, cd.cmpnt_cost, cd.burden_ind,
1280 cd.total_qty, cd.costcalc_orig, cd.rmcalc_type, cd.cost_level,
1281 cd.delete_mark, cd.attribute1,cd.attribute2, cd.attribute3,
1282 cd.attribute4, cd.attribute5, cd.attribute6, cd.attribute7,
1283 cd.attribute8, cd.attribute9, cd.attribute10, cd.attribute11,
1284 cd.attribute12, cd.attribute13, cd.attribute14, cd.attribute15,
1285 cd.attribute16, cd.attribute17, cd.attribute18, cd.attribute19,
1286 cd.attribute20, cd.attribute21, cd.attribute22, cd.attribute23,
1287 cd.attribute24, cd.attribute25, cd.attribute26, cd.attribute27,
1288 cd.attribute28, cd.attribute29, cd.attribute30, cd.attribute_category
1289 FROM cm_cmpt_mst cm, cm_cmpt_dtl cd
1290 WHERE cd.inventory_item_id = p_header_rec.inventory_item_id
1291 AND cd.organization_id = p_header_rec.organization_id
1292 AND cd.period_id = p_header_rec.period_id
1293 AND cd.cost_type_id = p_header_rec.cost_type_id
1294 ORDER BY cd.cost_cmpntcls_id, cd.cost_analysis_code;
1295 BEGIN
1296
1297 /**********************************
1298 * Standard Start of API savepoint *
1299 **********************************/
1300 SAVEPOINT GET_ITEM_COST_PVT;
1301
1302 /*************************************************************
1303 * Initialize message list if p_init_msg_list is set to TRUE. *
1304 *************************************************************/
1305 IF FND_API.to_Boolean( p_init_msg_list )
1306 THEN
1307 FND_MSG_PUB.initialize;
1308 END IF;
1309
1310 /*************************************************
1311 * Standard call to check for call compatibility. *
1312 *************************************************/
1313 IF NOT FND_API.Compatible_API_Call ( l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1314 THEN
1315 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1316 END IF;
1317
1318 /******************************************
1319 * Initialize API return status to success *
1320 ******************************************/
1321 x_return_status := FND_API.G_RET_STS_SUCCESS;
1322
1323 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
1324 THEN
1325 log_msg('Beginning Private Get Item Cost API.');
1326 END IF;
1327
1328 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
1329 THEN
1330 log_msg(' Retrieving Component Costs for Item ' || p_header_rec.inventory_item_id ||
1331 ' Organization id ' || p_header_rec.organization_id ||
1332 ' Period Id' || p_header_rec.Period_id ||
1333 ' Cost Type Id ' || p_header_rec.cost_type_id) ;
1334 END IF;
1335
1336 FOR cr_rec IN cm_cmpt_dtl
1337 LOOP
1338 IF cr_rec.cost_level = 0
1339 THEN
1340 l_idx := l_idx + 1 ;
1341 x_this_level_dtl_tbl(l_idx).cmpntcost_id := cr_rec.cmpntcost_id ;
1342 x_this_level_dtl_tbl(l_idx).cost_cmpntcls_id := cr_rec.cost_cmpntcls_id ;
1343 x_this_level_dtl_tbl(l_idx).cost_cmpntcls_code := cr_rec.cost_cmpntcls_code ;
1344 x_this_level_dtl_tbl(l_idx).cost_analysis_code := cr_rec.cost_analysis_code ;
1345 x_this_level_dtl_tbl(l_idx).cmpnt_cost := cr_rec.cmpnt_cost ;
1346 x_this_level_dtl_tbl(l_idx).burden_ind := cr_rec.burden_ind ;
1347 x_this_level_dtl_tbl(l_idx).total_qty := cr_rec.total_qty ;
1348 x_this_level_dtl_tbl(l_idx).costcalc_orig := cr_rec.costcalc_orig ;
1349 x_this_level_dtl_tbl(l_idx).rmcalc_type := cr_rec.rmcalc_type ;
1350 x_this_level_dtl_tbl(l_idx).delete_mark := cr_rec.delete_mark ;
1351 x_this_level_dtl_tbl(l_idx).attribute1 := cr_rec.attribute1 ;
1352 x_this_level_dtl_tbl(l_idx).attribute2 := cr_rec.attribute2 ;
1353 x_this_level_dtl_tbl(l_idx).attribute3 := cr_rec.attribute3 ;
1354 x_this_level_dtl_tbl(l_idx).attribute4 := cr_rec.attribute4 ;
1355 x_this_level_dtl_tbl(l_idx).attribute5 := cr_rec.attribute5 ;
1356 x_this_level_dtl_tbl(l_idx).attribute6 := cr_rec.attribute6 ;
1357 x_this_level_dtl_tbl(l_idx).attribute7 := cr_rec.attribute7 ;
1358 x_this_level_dtl_tbl(l_idx).attribute8 := cr_rec.attribute8 ;
1359 x_this_level_dtl_tbl(l_idx).attribute9 := cr_rec.attribute9 ;
1360 x_this_level_dtl_tbl(l_idx).attribute10 := cr_rec.attribute10 ;
1361 x_this_level_dtl_tbl(l_idx).attribute11 := cr_rec.attribute11 ;
1362 x_this_level_dtl_tbl(l_idx).attribute12 := cr_rec.attribute12 ;
1363 x_this_level_dtl_tbl(l_idx).attribute13 := cr_rec.attribute13 ;
1364 x_this_level_dtl_tbl(l_idx).attribute14 := cr_rec.attribute14 ;
1365 x_this_level_dtl_tbl(l_idx).attribute15 := cr_rec.attribute15 ;
1366 x_this_level_dtl_tbl(l_idx).attribute16 := cr_rec.attribute16 ;
1367 x_this_level_dtl_tbl(l_idx).attribute17 := cr_rec.attribute17 ;
1368 x_this_level_dtl_tbl(l_idx).attribute18 := cr_rec.attribute18 ;
1369 x_this_level_dtl_tbl(l_idx).attribute19 := cr_rec.attribute19 ;
1370 x_this_level_dtl_tbl(l_idx).attribute20 := cr_rec.attribute20 ;
1371 x_this_level_dtl_tbl(l_idx).attribute21 := cr_rec.attribute21 ;
1372 x_this_level_dtl_tbl(l_idx).attribute22 := cr_rec.attribute22 ;
1373 x_this_level_dtl_tbl(l_idx).attribute23 := cr_rec.attribute23 ;
1374 x_this_level_dtl_tbl(l_idx).attribute24 := cr_rec.attribute24 ;
1375 x_this_level_dtl_tbl(l_idx).attribute25 := cr_rec.attribute25 ;
1376 x_this_level_dtl_tbl(l_idx).attribute26 := cr_rec.attribute26 ;
1377 x_this_level_dtl_tbl(l_idx).attribute27 := cr_rec.attribute27 ;
1378 x_this_level_dtl_tbl(l_idx).attribute28 := cr_rec.attribute28 ;
1379 x_this_level_dtl_tbl(l_idx).attribute29 := cr_rec.attribute29 ;
1380 x_this_level_dtl_tbl(l_idx).attribute30 := cr_rec.attribute30 ;
1381 x_this_level_dtl_tbl(l_idx).attribute_category := cr_rec.attribute_category ;
1382 ELSE
1383 l_idx1 := l_idx1 + 1 ;
1384 x_lower_level_dtl_tbl(l_idx1).cmpntcost_id := cr_rec.cmpntcost_id ; x_lower_level_dtl_tbl(l_idx1).cost_cmpntcls_id := cr_rec.cost_cmpntcls_id ;
1385 x_lower_level_dtl_tbl(l_idx1).cost_cmpntcls_code := cr_rec.cost_cmpntcls_code ;
1386 x_lower_level_dtl_tbl(l_idx1).cost_analysis_code := cr_rec.cost_analysis_code ;
1387 x_lower_level_dtl_tbl(l_idx1).cmpnt_cost := cr_rec.cmpnt_cost ;
1388 END IF ;
1389 END LOOP ;
1390
1391 /**************************************************************************
1392 * Standard call to get message count and if count is 1, get message info. *
1393 **************************************************************************/
1394 FND_MSG_PUB.Count_And_Get
1395 (
1396 p_count => x_msg_count,
1397 p_data => x_msg_data
1398 );
1399
1400 EXCEPTION
1401 WHEN FND_API.G_EXC_ERROR THEN
1402 ROLLBACK TO GET_ITEM_COST_PVT;
1403 x_return_status := FND_API.G_RET_STS_ERROR ;
1404 FND_MSG_PUB.Count_And_Get
1405 (
1406 p_count => x_msg_count,
1407 p_data => x_msg_data
1408 );
1409 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1410 ROLLBACK TO GET_ITEM_COST_PVT;
1411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1412 FND_MSG_PUB.Count_And_Get
1413 (
1414 p_count => x_msg_count,
1415 p_data => x_msg_data
1416 );
1417 WHEN OTHERS THEN
1418 ROLLBACK TO GET_ITEM_COST_PVT;
1419 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1420 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1421 THEN
1422 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1423 END IF;
1424 FND_MSG_PUB.Count_And_Get
1425 (
1426 p_count => x_msg_count,
1427 p_data => x_msg_data
1428 );
1429 END GET_ITEM_COST ;
1430
1431 END GMF_ITEMCOST_PVT;