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