[Home] [Help]
PACKAGE BODY: APPS.GMF_ACTUAL_COST_ADJUSTMENT_PVT
Source
1 PACKAGE BODY GMF_ACTUAL_COST_ADJUSTMENT_PVT AS
2 /* $Header: GMFVACAB.pls 120.1.12000000.2 2007/05/02 12:13:09 pmarada ship $ */
3
4 /********************************************************************
5 * PACKAGE *
6 * GMF_ACTUAL_COST_ADJUSTMENT_PVT *
7 * *
8 * TYPE *
9 * PRIVATE *
10 * *
11 * FUNCTION *
12 * Actual Cost Adjustment Creation, Updation, Query and Deletion *
13 * *
14 * PARAMETERS *
15 * N/A *
16 * *
17 * DESCRIPTION *
18 * This package contains public procedures relating to Actual Cost *
19 * Adjustment Creation, Updation, Query and Deletion *
20 * *
21 * Valid values for message levels are from 1-50. *
22 * 1 being least severe and 50 highest. *
23 * The pre-defined levels correspond to standard API *
24 * return status. Debug levels are used to control the amount of *
25 * debug information a program writes to the PL/SQL message table. *
26 * *
27 * G_MSG_LVL_UNEXP_ERROR CONSTANT NUMBER := 60; *
28 * G_MSG_LVL_ERROR CONSTANT NUMBER := 50; *
29 * G_MSG_LVL_SUCCESS CONSTANT NUMBER := 40; *
30 * G_MSG_LVL_DEBUG_HIGH CONSTANT NUMBER := 30; *
31 * G_MSG_LVL_DEBUG_MEDIUM CONSTANT NUMBER := 20; *
32 * G_MSG_LVL_DEBUG_LOW CONSTANT NUMBER := 10; *
33 * *
34 * HISTORY *
35 * 16-Sep-2005 Anand Thiyagarajan Created *
36 ********************************************************************/
37
38 /*******************
39 * Global variables *
40 *******************/
41
42 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMF_ACTUAL_COST_ADJUSTMENT_PVT';
43 G_DEBUG_LEVEL NUMBER(2) := FND_MSG_PUB.G_Msg_Level_Threshold;
44
45 /**************************************************************
46 * PROCEDURE *
47 * log_msg *
48 * *
49 * DESCRIPTION *
50 * This procedure logs messages to message stack. *
51 * *
52 * PARAMETERS *
53 * p_msg_lvl IN NUMBER(10) - Message Level *
54 * p_msg_text IN NUMBER(10) - Actual Message Text *
55 * *
56 * HISTORY *
57 * 16-SEP-2005 Anand Thiyagarajan Created *
58 * *
59 **************************************************************/
60 PROCEDURE LOG_MSG
61 (
62 p_msg_text IN VARCHAR2
63 )
64 IS
65 BEGIN
66 FND_MESSAGE.SET_NAME('GMF','GMF_API_DEBUG');
67 FND_MESSAGE.SET_TOKEN('MSG',p_msg_text);
68 FND_MSG_PUB.Add;
69 END log_msg ;
70
71 /********************************************************************
72 * PROCEDURE *
73 * CREATE_ACTUAL_COST_ADJUSTMENT *
74 * *
75 * TYPE *
76 * PUBLIC *
77 * *
78 * FUNCTION *
79 * Creates Actual Cost Adjustment based on the input into table *
80 * GMF_LOT_COST_ADJUSTMENTS *
81 * *
82 * PARAMETERS *
83 * IN : *
84 * p_api_version IN NUMBER *
85 * p_init_msg_list IN VARCHAR2 *
86 * p_adjustment_rec IN OUT NOCOPY Adjustment_Rec_Type *
87 * *
88 * OUT : *
89 * x_return_status OUT NOCOPY VARCHAR2 *
90 * x_msg_count OUT NOCOPY VARCHAR2 *
91 * x_msg_data OUT NOCOPY VARCHAR2 *
92 * *
93 * DESCRIPTION *
94 * This procedure creates Actual Cost Adjustments *
95 * *
96 * HISTORY *
97 * 16-Sep-2005 Anand Thiyagarajan Created *
98 ********************************************************************/
99 PROCEDURE CREATE_ACTUAL_COST_ADJUSTMENT
100 (
101 p_api_version IN NUMBER,
102 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
103 x_return_status OUT NOCOPY VARCHAR2,
104 x_msg_count OUT NOCOPY NUMBER,
105 x_msg_data OUT NOCOPY VARCHAR2,
106 p_adjustment_rec IN OUT NOCOPY GMF_ACTUAL_COST_ADJUSTMENT_PUB.ADJUSTMENT_REC_TYPE
107 )
108 IS
109
110 /******************
111 * Local Variables *
112 ******************/
113 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_ACTUAL_COST_ADJUSTMENT';
114 l_api_version CONSTANT NUMBER := 1.0 ;
115 l_cost_adjust_id CM_ADJS_DTL.COST_ADJUST_ID%TYPE;
116
117 BEGIN
118
119 /**********************************
120 * Standard Start of API savepoint *
121 **********************************/
122 SAVEPOINT CREATE_ACT_COST_ADJUSTMENT_PVT ;
123
124 /*************************************************************
125 * Initialize message list if p_init_msg_list is set to TRUE. *
126 *************************************************************/
127 IF FND_API.to_Boolean( p_init_msg_list )
128 THEN
129 FND_MSG_PUB.initialize;
130 END IF;
131
132 /*************************************************
133 * Standard call to check for call compatibility. *
134 *************************************************/
135 IF NOT FND_API.Compatible_API_Call
136 (
137 l_api_version,
138 p_api_version,
139 l_api_name,
140 G_PKG_NAME
141 )
142 THEN
143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
144 END IF;
145
146 /******************************************
147 * Initialize API return status to success *
148 ******************************************/
149 x_return_status := FND_API.G_RET_STS_SUCCESS;
150
151 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
152 THEN
153 log_msg('Beginning Private Create Actual Cost Adjustment API');
154 END IF;
155
156 IF p_adjustment_rec.cost_adjust_id IS NULL THEN
157 SELECT GEM5_COST_ADJUST_ID_S.NEXTVAL
158 INTO l_cost_adjust_id
159 FROM dual;
160 END IF;
161
162 IF p_adjustment_rec.cost_adjust_id IS NULL
163 THEN
164 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
165 THEN
166 log_msg ( ' Inserting Actual Cost Adjustments for '||
167 ' Item ' || p_adjustment_rec.inventory_item_id ||
168 ' Organization ' || p_adjustment_rec.organization_id ||
169 ' Cost Type ' || p_adjustment_rec.cost_type_id ||
170 ' Period Id ' || p_adjustment_rec.period_id ||
171 ' Cost Component Class ' || p_adjustment_rec.cost_cmpntcls_id ||
172 ' Analysis code ' || p_adjustment_rec.cost_analysis_code ||
173 ' Adjustment Indicator '|| p_adjustment_rec.adjustment_ind
174 );
175 END IF;
176
177 BEGIN
178 INSERT INTO cm_adjs_dtl
179 (
180 ORGANIZATION_ID
181 , INVENTORY_ITEM_ID
182 , COST_TYPE_ID
183 , PERIOD_ID
184 , COST_CMPNTCLS_ID
185 , COST_ANALYSIS_CODE
186 , COST_ADJUST_ID
187 , ADJUST_QTY
188 , ADJUST_QTY_UOM
189 , ADJUST_COST
190 , REASON_CODE
191 , ADJUST_STATUS
192 , CREATION_DATE
193 , LAST_UPDATE_LOGIN
194 , CREATED_BY
195 , LAST_UPDATE_DATE
196 , LAST_UPDATED_BY
197 , TEXT_CODE
198 , TRANS_CNT
199 , DELETE_MARK
200 , REQUEST_ID
201 , PROGRAM_APPLICATION_ID
202 , PROGRAM_ID
203 , PROGRAM_UPDATE_DATE
204 , ATTRIBUTE_CATEGORY
205 , ATTRIBUTE1
206 , ATTRIBUTE2
207 , ATTRIBUTE3
208 , ATTRIBUTE4
209 , ATTRIBUTE5
210 , ATTRIBUTE6
211 , ATTRIBUTE7
212 , ATTRIBUTE8
213 , ATTRIBUTE9
214 , ATTRIBUTE10
215 , ATTRIBUTE11
216 , ATTRIBUTE12
217 , ATTRIBUTE13
218 , ATTRIBUTE14
219 , ATTRIBUTE15
220 , ATTRIBUTE16
221 , ATTRIBUTE17
222 , ATTRIBUTE18
223 , ATTRIBUTE19
224 , ATTRIBUTE20
225 , ATTRIBUTE21
226 , ATTRIBUTE22
227 , ATTRIBUTE23
228 , ATTRIBUTE24
229 , ATTRIBUTE25
230 , ATTRIBUTE26
231 , ATTRIBUTE27
232 , ATTRIBUTE28
233 , ATTRIBUTE29
234 , ATTRIBUTE30
235 , ADJUSTMENT_IND
236 , SUBLEDGER_IND
237 , ADJUSTMENT_DATE
238 , GL_POSTED_IND
239 )
240 VALUES
241 (
242 p_adjustment_rec.organization_id
243 , p_adjustment_rec.inventory_item_id
244 , p_adjustment_rec.cost_type_id
245 , p_adjustment_rec.period_id
246 , p_adjustment_rec.cost_cmpntcls_id
247 , p_adjustment_rec.cost_analysis_code
248 , l_cost_adjust_id
249 , p_adjustment_rec.adjust_qty
250 , p_adjustment_rec.adjust_qty_uom
251 , p_adjustment_rec.adjust_cost
252 , p_adjustment_rec.reason_code
253 , p_adjustment_rec.adjust_status
254 , SYSDATE
255 , FND_GLOBAL.USER_ID
256 , FND_GLOBAL.USER_ID
257 , SYSDATE
258 , FND_GLOBAL.LOGIN_ID
259 , p_adjustment_rec.text_code
260 , 0
261 , 0
262 , p_adjustment_rec.request_id
263 , p_adjustment_rec.program_application_id
264 , p_adjustment_rec.program_id
265 , p_adjustment_rec.program_update_date
266 , p_adjustment_rec.attribute_category
267 , p_adjustment_rec.attribute1
268 , p_adjustment_rec.attribute2
269 , p_adjustment_rec.attribute3
270 , p_adjustment_rec.attribute4
271 , p_adjustment_rec.attribute5
272 , p_adjustment_rec.attribute6
273 , p_adjustment_rec.attribute7
274 , p_adjustment_rec.attribute8
275 , p_adjustment_rec.attribute9
276 , p_adjustment_rec.attribute10
277 , p_adjustment_rec.attribute11
278 , p_adjustment_rec.attribute12
279 , p_adjustment_rec.attribute13
280 , p_adjustment_rec.attribute14
281 , p_adjustment_rec.attribute15
282 , p_adjustment_rec.attribute16
283 , p_adjustment_rec.attribute17
284 , p_adjustment_rec.attribute18
285 , p_adjustment_rec.attribute19
286 , p_adjustment_rec.attribute20
287 , p_adjustment_rec.attribute21
288 , p_adjustment_rec.attribute22
289 , p_adjustment_rec.attribute23
290 , p_adjustment_rec.attribute24
291 , p_adjustment_rec.attribute25
292 , p_adjustment_rec.attribute26
293 , p_adjustment_rec.attribute27
294 , p_adjustment_rec.attribute28
295 , p_adjustment_rec.attribute29
296 , p_adjustment_rec.attribute30
297 , p_adjustment_rec.adjustment_ind
298 , p_adjustment_rec.subledger_ind
299 , p_adjustment_rec.adjustment_date
300 , 0
301 ) RETURNING cost_adjust_id INTO p_adjustment_rec.cost_adjust_id;
302
303 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
304 THEN
305 log_msg ( SQL%ROWCOUNT || ' Record Inserted for Actual Cost Adjustments for '||
306 ' Item ' || p_adjustment_rec.inventory_item_id ||
307 ' Organization ' || p_adjustment_rec.organization_id ||
308 ' Cost Type ' || p_adjustment_rec.cost_type_id ||
309 ' Period Id ' || p_adjustment_rec.period_id ||
310 ' Cost Component Class ' || p_adjustment_rec.cost_cmpntcls_id ||
311 ' Analysis code ' || p_adjustment_rec.cost_analysis_code ||
312 ' Adjustment Indicator '|| p_adjustment_rec.adjustment_ind
313 );
314
315 END IF;
316 EXCEPTION
317 WHEN OTHERS THEN
318 FND_MESSAGE.SET_NAME('GMF','GMF_API_ACA_INS_FAILED');
319 FND_MESSAGE.SET_TOKEN('ITEM', p_adjustment_rec.inventory_item_id);
320 FND_MESSAGE.SET_TOKEN('ORGANIZATION', p_adjustment_rec.organization_id);
321 FND_MESSAGE.SET_TOKEN('COST_TYPE', p_adjustment_rec.cost_type_id);
322 FND_MESSAGE.SET_TOKEN('PERIOD_ID', p_adjustment_rec.period_id);
323 FND_MESSAGE.SET_TOKEN('COST_CMPNT_CLS', p_adjustment_rec.cost_cmpntcls_id);
324 FND_MESSAGE.SET_TOKEN('COST_ANALYSIS_CODE', p_adjustment_rec.cost_analysis_code);
325 FND_MSG_PUB.Add;
326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
327 RAISE ;
328 END;
329
330 ELSIF p_adjustment_rec.cost_adjust_id IS NOT NULL
331 THEN
332 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
333 THEN
334 log_msg ( ' Actual Cost Adjustments for ' ||
335 ' Item ' || p_adjustment_rec.inventory_item_id ||
336 ' Organization ' || p_adjustment_rec.organization_id ||
337 ' Cost Type ' || p_adjustment_rec.cost_type_id ||
338 ' Period Id ' || p_adjustment_rec.period_id ||
339 ' Cost Component Class ' || p_adjustment_rec.cost_cmpntcls_id ||
340 ' Analysis code ' || p_adjustment_rec.cost_analysis_code ||
341 ' Adjustment Indicator '|| p_adjustment_rec.adjustment_ind ||
342 ' already exists '
343 ) ;
344 END IF;
345 END IF;
346
347 /**************************************************************************
348 * Standard call to get message count and if count is 1, get message info. *
349 **************************************************************************/
350 FND_MSG_PUB.Count_And_Get (
351 p_count => x_msg_count
352 , p_data => x_msg_data
353 );
354 EXCEPTION
355 WHEN FND_API.G_EXC_ERROR THEN
356 ROLLBACK TO CREATE_ACT_COST_ADJUSTMENT_PVT;
357 x_return_status := FND_API.G_RET_STS_ERROR ;
358 FND_MSG_PUB.Count_And_Get (
359 p_count => x_msg_count
360 , p_data => x_msg_data
361 );
362 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
363 ROLLBACK TO CREATE_ACT_COST_ADJUSTMENT_PVT;
364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
365 FND_MSG_PUB.Count_And_Get (
366 p_count => x_msg_count
367 , p_data => x_msg_data
368 );
369 WHEN OTHERS THEN
370 ROLLBACK TO CREATE_ACT_COST_ADJUSTMENT_PVT;
371 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
372 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
373 FND_MSG_PUB.Add_Exc_Msg (
374 G_PKG_NAME
375 , l_api_name
376 );
377 END IF;
378 FND_MSG_PUB.Count_And_Get (
379 p_count => x_msg_count
380 , p_data => x_msg_data
381 );
382 END CREATE_ACTUAL_COST_ADJUSTMENT;
383
384 /********************************************************************
385 * PROCEDURE *
386 * UPDATE_ACTUAL_COST_ADJUSTMENT *
387 * *
388 * TYPE *
389 * PUBLIC *
390 * *
391 * FUNCTION *
392 * Updates Actual Cost Adjustment based on the input into table *
393 * GMF_LOT_COST_ADJUSTMENTS *
394 * *
395 * PARAMETERS *
396 * IN : *
397 * p_api_version IN NUMBER *
398 * p_init_msg_list IN VARCHAR2 *
399 * p_adjustment_rec IN OUT NOCOPY Adjustment_Rec_Type *
400 * *
401 * OUT : *
402 * x_return_status OUT NOCOPY VARCHAR2 *
403 * x_msg_count OUT NOCOPY VARCHAR2 *
404 * x_msg_data OUT NOCOPY VARCHAR2 *
405 * *
406 * DESCRIPTION *
407 * This procedure updates Actual Cost Adjustments *
408 * *
409 * HISTORY *
410 * 16-Sep-2005 Anand Thiyagarajan Created *
411 ********************************************************************/
412
413 PROCEDURE UPDATE_ACTUAL_COST_ADJUSTMENT
414 (
415 p_api_version IN NUMBER,
416 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
417 x_return_status OUT NOCOPY VARCHAR2,
418 x_msg_count OUT NOCOPY NUMBER,
419 x_msg_data OUT NOCOPY VARCHAR2,
420 p_adjustment_rec IN OUT NOCOPY GMF_ACTUAL_COST_ADJUSTMENT_PUB.ADJUSTMENT_REC_TYPE
421 )
422 IS
423
424 /******************
425 * Local Variables *
426 ******************/
427 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ACTUAL_COST_ADJUSTMENT';
428 l_api_version CONSTANT NUMBER := 1.0 ;
429 l_cost_adjust_id CM_ADJS_DTL.COST_ADJUST_ID%TYPE;
430
431 BEGIN
432
433 /**********************************
434 * Standard Start of API savepoint *
435 **********************************/
436 SAVEPOINT UPDATE_ACT_COST_ADJUSTMENT_PVT ;
437
438 /*************************************************************
439 * Initialize message list if p_init_msg_list is set to TRUE. *
440 *************************************************************/
441 IF FND_API.to_Boolean( p_init_msg_list )
442 THEN
443 FND_MSG_PUB.initialize;
444 END IF;
445
446 /*************************************************
447 * Standard call to check for call compatibility. *
448 *************************************************/
449 IF NOT FND_API.Compatible_API_Call
450 (
451 l_api_version,
452 p_api_version,
453 l_api_name,
454 G_PKG_NAME
455 )
456 THEN
457 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
458 END IF;
459
460 /******************************************
461 * Initialize API return status to success *
462 ******************************************/
463 x_return_status := FND_API.G_RET_STS_SUCCESS;
464
465 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
466 THEN
467 log_msg('Beginning Private Update Actual Cost Adjustment API');
468 END IF;
469
470 IF p_adjustment_rec.cost_adjust_id IS NOT NULL
471 THEN
472 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
473 THEN
474 log_msg ( 'Updating Actual Cost Adjustment Record for Cost Adjustment Id ' || p_adjustment_rec.cost_adjust_id);
475 END IF;
476
477 BEGIN
478 UPDATE cm_adjs_dtl
479 SET cost_cmpntcls_id = p_adjustment_rec.cost_cmpntcls_id,
480 cost_analysis_code = p_adjustment_rec.cost_analysis_code,
481 adjust_qty = p_adjustment_rec.adjust_qty,
482 adjust_qty_uom = p_adjustment_rec.adjust_qty_uom,
483 adjust_cost = p_adjustment_rec.adjust_cost,
484 reason_code = p_adjustment_rec.reason_code,
485 adjustment_ind = p_adjustment_rec.adjustment_ind,
486 subledger_ind = p_adjustment_rec.subledger_ind,
487 adjustment_date = p_adjustment_rec.adjustment_date
488 WHERE cost_adjust_id = p_adjustment_rec.cost_adjust_id
489 AND delete_mark = 0
490 AND adjust_status <> 1;
491
492 EXCEPTION
493 WHEN OTHERS THEN
494 FND_MESSAGE.SET_NAME('GMF','GMF_API_ACA_UPD_FAILED');
495 FND_MESSAGE.SET_TOKEN('COST_ADJUST_ID', p_adjustment_rec.cost_adjust_id);
496 FND_MSG_PUB.Add;
497 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
498 RAISE ;
499 END ;
500 ELSIF p_adjustment_rec.cost_adjust_id IS NULL
501 THEN
502 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
503 THEN
504 log_msg ( ' Actual Cost Adjustment Record for ' ||
505 ' Cost Adjustment Id ' || p_adjustment_rec.cost_adjust_id ||
506 ' Doesn''t Exist');
507 END IF;
508 END IF;
509
510 /**************************************************************************
511 * Standard call to get message count and if count is 1, get message info. *
512 **************************************************************************/
513 FND_MSG_PUB.Count_And_Get (
514 p_count => x_msg_count
515 , p_data => x_msg_data
516 );
517 EXCEPTION
518 WHEN FND_API.G_EXC_ERROR THEN
519 ROLLBACK TO UPDATE_ACT_COST_ADJUSTMENT_PVT;
520 x_return_status := FND_API.G_RET_STS_ERROR ;
521 FND_MSG_PUB.Count_And_Get (
522 p_count => x_msg_count
523 , p_data => x_msg_data
524 );
525 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
526 ROLLBACK TO UPDATE_ACT_COST_ADJUSTMENT_PVT;
527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
528 FND_MSG_PUB.Count_And_Get (
529 p_count => x_msg_count
530 , p_data => x_msg_data
531 );
532 WHEN OTHERS THEN
533 ROLLBACK TO UPDATE_ACT_COST_ADJUSTMENT_PVT;
534 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
535 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
536 THEN
537 FND_MSG_PUB.Add_Exc_Msg (
538 G_PKG_NAME
539 , l_api_name
540 );
541 END IF;
542 FND_MSG_PUB.Count_And_Get (
543 p_count => x_msg_count
544 , p_data => x_msg_data
545 );
546 END UPDATE_ACTUAL_COST_ADJUSTMENT;
547
548 /********************************************************************
549 * PROCEDURE *
550 * DELETE_ACTUAL_COST_ADJUSTMENT *
551 * *
552 * TYPE *
553 * PUBLIC *
554 * *
555 * FUNCTION *
556 * Deletes Actual Cost Adjustment based on the input from table *
557 * GMF_LOT_COST_ADJUSTMENTS *
558 * *
559 * PARAMETERS *
560 * IN : *
561 * p_api_version IN NUMBER *
562 * p_init_msg_list IN VARCHAR2 *
563 * p_adjustment_rec IN OUT NOCOPY Adjustment_Rec_Type *
564 * *
565 * OUT : *
566 * x_return_status OUT NOCOPY VARCHAR2 *
567 * x_msg_count OUT NOCOPY VARCHAR2 *
568 * x_msg_data OUT NOCOPY VARCHAR2 *
569 * *
570 * DESCRIPTION *
571 * This procedure deletes Actual Cost Adjustments *
572 * *
573 * HISTORY *
574 * 16-Sep-2005 Anand Thiyagarajan Created *
575 ********************************************************************/
576
577 PROCEDURE DELETE_ACTUAL_COST_ADJUSTMENT
578 (
579 p_api_version IN NUMBER,
580 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
581 x_return_status OUT NOCOPY VARCHAR2,
582 x_msg_count OUT NOCOPY NUMBER,
583 x_msg_data OUT NOCOPY VARCHAR2,
584 p_adjustment_rec IN OUT NOCOPY GMF_ACTUAL_COST_ADJUSTMENT_PUB.ADJUSTMENT_REC_TYPE
585 )
586 IS
587
588 /******************
589 * Local Variables *
590 ******************/
591 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ACTUAL_COST_ADJUSTMENT';
592 l_api_version CONSTANT NUMBER := 1.0 ;
593 l_cost_adjust_id CM_ADJS_DTL.COST_ADJUST_ID%TYPE;
594
595 BEGIN
596
597 /**********************************
598 * Standard Start of API savepoint *
599 **********************************/
600 SAVEPOINT DELETE_ACT_COST_ADJUSTMENT_PVT ;
601
602 /*************************************************************
603 * Initialize message list if p_init_msg_list is set to TRUE. *
604 *************************************************************/
605 IF FND_API.to_Boolean( p_init_msg_list )
606 THEN
607 FND_MSG_PUB.initialize;
608 END IF;
609
610 /*************************************************
611 * Standard call to check for call compatibility. *
612 *************************************************/
613 IF NOT FND_API.Compatible_API_Call
614 (
615 l_api_version,
616 p_api_version,
617 l_api_name,
618 G_PKG_NAME
619 )
620 THEN
621 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
622 END IF;
623
624 /******************************************
625 * Initialize API return status to success *
626 ******************************************/
627 x_return_status := FND_API.G_RET_STS_SUCCESS;
628
629 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
630 THEN
631 log_msg('Beginning Private Delete Actual Cost Adjustment API');
632 END IF;
633
634 IF p_adjustment_rec.cost_adjust_id IS NOT NULL
635 THEN
636 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
637 THEN
638 log_msg ( 'Deleting Actual Cost Adjustment Record for Cost Adjustment Id ' || p_adjustment_rec.cost_adjust_id);
639 END IF;
640 BEGIN
641 DELETE cm_adjs_dtl
642 WHERE cost_adjust_id = p_adjustment_rec.cost_adjust_id
643 AND adjust_status <> 1;
644
645 IF SQL%NOTFOUND THEN
646 FND_MESSAGE.SET_NAME('GMF','GMF_API_ACA_DEL_FAILED');
647 FND_MESSAGE.SET_TOKEN('ADJUST_ID', p_adjustment_rec.cost_adjust_id);
648 FND_MSG_PUB.Add;
649 x_return_status := FND_API.G_RET_STS_ERROR ;
650 RAISE FND_API.G_EXC_ERROR;
651 END IF;
652 EXCEPTION
653 WHEN OTHERS THEN
654 IF (p_adjustment_rec.cost_adjust_id IS NOT NULL) OR (p_adjustment_rec.cost_adjust_id <> FND_API.G_MISS_NUM)
655 THEN
656 FND_MESSAGE.SET_NAME('GMF','GMF_API_ACA_DEL_FAILED');
657 FND_MESSAGE.SET_TOKEN('ADJUST_ID', p_adjustment_rec.cost_adjust_id);
658 FND_MSG_PUB.Add;
659 END IF ;
660 END;
661 ELSE
662 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
663 THEN
664 log_msg ( ' Actual Cost Adjustment Record for ' ||
665 ' Cost Adjustment Id ' || p_adjustment_rec.cost_adjust_id ||
666 ' Doesn''t Exist');
667 END IF;
668 END IF;
669
670 /**************************************************************************
671 * Standard call to get message count and if count is 1, get message info. *
672 **************************************************************************/
673 FND_MSG_PUB.Count_And_Get (
674 p_count => x_msg_count
675 , p_data => x_msg_data
676 );
677 EXCEPTION
678 WHEN FND_API.G_EXC_ERROR THEN
679 ROLLBACK TO DELETE_ACT_COST_ADJUSTMENT_PVT;
680 x_return_status := FND_API.G_RET_STS_ERROR ;
681 FND_MSG_PUB.Count_And_Get (
682 p_count => x_msg_count
683 , p_data => x_msg_data
684 );
685 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
686 ROLLBACK TO DELETE_ACT_COST_ADJUSTMENT_PVT;
687 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
688 FND_MSG_PUB.Count_And_Get (
689 p_count => x_msg_count
690 , p_data => x_msg_data
691 );
692 WHEN OTHERS THEN
693 ROLLBACK TO DELETE_ACT_COST_ADJUSTMENT_PVT;
694 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
695 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
696 THEN
697 FND_MSG_PUB.Add_Exc_Msg (
698 G_PKG_NAME
699 , l_api_name
700 );
701 END IF;
702 FND_MSG_PUB.Count_And_Get (
703 p_count => x_msg_count
704 , p_data => x_msg_data
705 );
706 END DELETE_ACTUAL_COST_ADJUSTMENT;
707
708 /********************************************************************
709 * PROCEDURE *
710 * GET_ACTUAL_COST_ADJUSTMENT *
711 * *
712 * TYPE *
713 * PUBLIC *
714 * *
715 * FUNCTION *
716 * Retrieves Actual Cost Adjustment based on the input from table *
717 * GMF_LOT_COST_ADJUSTMENTS *
718 * *
719 * PARAMETERS *
720 * IN : *
721 * p_api_version IN NUMBER *
722 * p_init_msg_list IN VARCHAR2 *
723 * p_adjustment_rec IN OUT NOCOPY Adjustment_Rec_Type *
724 * *
725 * OUT : *
726 * x_return_status OUT NOCOPY VARCHAR2 *
727 * x_msg_count OUT NOCOPY VARCHAR2 *
728 * x_msg_data OUT NOCOPY VARCHAR2 *
729 * *
730 * DESCRIPTION *
731 * This procedure retrieves Actual Cost Adjustments *
732 * *
733 * HISTORY *
734 * 16-Sep-2005 Anand Thiyagarajan Created *
735 ********************************************************************/
736 PROCEDURE GET_ACTUAL_COST_ADJUSTMENT
737 (
738 p_api_version IN NUMBER,
739 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
740 x_return_status OUT NOCOPY VARCHAR2,
741 x_msg_count OUT NOCOPY NUMBER,
742 x_msg_data OUT NOCOPY VARCHAR2,
743 p_adjustment_rec IN OUT NOCOPY GMF_ACTUAL_COST_ADJUSTMENT_PUB.ADJUSTMENT_REC_TYPE
744 )
745 IS
746
747 /******************
748 * Local Variables *
749 ******************/
750 l_api_name CONSTANT VARCHAR2(30) := 'GET_ACTUAL_COST_ADJUSTMENT';
751 l_api_version CONSTANT NUMBER := 1.0 ;
752 l_cost_adjust_id CM_ADJS_DTL.COST_ADJUST_ID%TYPE;
753 l_type NUMBER := 0;
754
755 /**********
756 * Cursors *
757 **********/
758
759 CURSOR CUR_ADJS_DTL
760 (
761 p_type IN NUMBER,
762 p_cost_adjust_id IN CM_ADJS_DTL.COST_ADJUST_ID%TYPE,
763 p_organization_id IN CM_ADJS_DTL.ORGANIZATION_ID%TYPE,
764 p_inventory_item_id IN CM_ADJS_DTL.INVENTORY_ITEM_ID%TYPE,
765 p_cost_type_id IN CM_ADJS_DTL.COST_TYPE_ID%TYPE,
766 p_period_id IN CM_ADJS_DTL.PERIOD_ID%TYPE,
767 p_cost_cmpntcls_id IN CM_ADJS_DTL.COST_CMPNTCLS_ID%TYPE,
768 p_cost_analysis_code IN CM_ADJS_DTL.COST_ANALYSIS_CODE%TYPE,
769 p_adjustment_ind IN CM_ADJS_DTL.ADJUSTMENT_IND%TYPE
770 )
771 IS
772 SELECT ORGANIZATION_ID, INVENTORY_ITEM_ID, COST_TYPE_ID, PERIOD_ID, COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
773 COST_ADJUST_ID, ADJUST_QTY, ADJUST_QTY_UOM, ADJUST_COST, REASON_CODE, ADJUST_STATUS, CREATION_DATE,
774 LAST_UPDATE_LOGIN, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, TEXT_CODE, TRANS_CNT, DELETE_MARK,
775 REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
776 ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
777 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18,
778 ATTRIBUTE19, ATTRIBUTE20, ATTRIBUTE21, ATTRIBUTE22, ATTRIBUTE23, ATTRIBUTE24, ATTRIBUTE25, ATTRIBUTE26,
779 ATTRIBUTE27, ATTRIBUTE28, ATTRIBUTE29, ATTRIBUTE30, ADJUSTMENT_IND, SUBLEDGER_IND, ADJUSTMENT_DATE
780 FROM cm_adjs_dtl
781 WHERE p_type = 1
782 AND COST_ADJUST_ID = p_cost_adjust_id
783 UNION
784 SELECT ORGANIZATION_ID, INVENTORY_ITEM_ID, COST_TYPE_ID, PERIOD_ID, COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
785 COST_ADJUST_ID, ADJUST_QTY, ADJUST_QTY_UOM, ADJUST_COST, REASON_CODE, ADJUST_STATUS, CREATION_DATE,
786 LAST_UPDATE_LOGIN, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, TEXT_CODE, TRANS_CNT, DELETE_MARK,
787 REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
788 ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
789 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18,
790 ATTRIBUTE19, ATTRIBUTE20, ATTRIBUTE21, ATTRIBUTE22, ATTRIBUTE23, ATTRIBUTE24, ATTRIBUTE25, ATTRIBUTE26,
791 ATTRIBUTE27, ATTRIBUTE28, ATTRIBUTE29, ATTRIBUTE30, ADJUSTMENT_IND, SUBLEDGER_IND, ADJUSTMENT_DATE
792 FROM cm_adjs_dtl
793 WHERE p_type = 2
794 AND ORGANIZATION_ID = p_organization_id
795 AND INVENTORY_ITEM_ID = p_inventory_item_id
796 AND COST_TYPE_ID = p_cost_type_id
797 AND PERIOD_ID = p_period_id
798 AND COST_CMPNTCLS_ID = p_cost_cmpntcls_id
799 AND COST_ANALYSIS_CODE = p_cost_analysis_code
800 AND ADJUSTMENT_IND = p_adjustment_ind;
801
802 BEGIN
803
804 /*************************************************************
805 * Initialize message list if p_init_msg_list is set to TRUE. *
806 *************************************************************/
807 IF FND_API.to_Boolean( p_init_msg_list )
808 THEN
809 FND_MSG_PUB.initialize;
810 END IF;
811
812 /*************************************************
813 * Standard call to check for call compatibility. *
814 *************************************************/
815 IF NOT FND_API.Compatible_API_Call
816 (
817 l_api_version,
818 p_api_version,
819 l_api_name,
820 G_PKG_NAME
821 )
822 THEN
823 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
824 END IF;
825
826 /******************************************
827 * Initialize API return status to success *
828 ******************************************/
829 x_return_status := FND_API.G_RET_STS_SUCCESS;
830
831 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
832 THEN
833 log_msg('Beginning Private Get Actual Cost Adjustment API');
834 END IF;
835
836 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
837 THEN
838 log_msg ( ' Retrieving Actual Cost Adjustments for ' ||
839 ' Cost Adjustment ID ' || p_adjustment_rec.cost_adjust_id ||
840 ' Item ' || p_adjustment_rec.inventory_item_id ||
841 ' Organization ' || p_adjustment_rec.organization_id ||
842 ' Cost Type ' || p_adjustment_rec.cost_type_id ||
843 ' Period Id ' || p_adjustment_rec.period_id ||
844 ' Cost Component Class ' || p_adjustment_rec.cost_cmpntcls_id ||
845 ' Analysis code ' || p_adjustment_rec.cost_analysis_code ||
846 ' Adjustment Indicator '|| p_adjustment_rec.adjustment_ind
847 );
848
849 END IF;
850
851 IF p_adjustment_rec.cost_adjust_id IS NOT NULL THEN
852 l_type := 1;
853 ELSIF p_adjustment_rec.organization_id IS NOT NULL
854 AND p_adjustment_rec.inventory_item_id IS NOT NULL
855 AND p_adjustment_rec.cost_type_id IS NOT NULL
856 AND p_adjustment_rec.period_id IS NOT NULL
857 AND p_adjustment_rec.cost_cmpntcls_id IS NOT NULL
858 AND p_adjustment_rec.cost_analysis_code IS NOT NULL
859 AND p_adjustment_rec.adjustment_ind IS NOT NULL
860 THEN
861 l_type := 2;
862 ELSE
863 l_type := 0;
864 END IF;
865
866 IF l_type > 0 THEN
867 FOR i IN cur_adjs_dtl
868 (
869 p_type => l_type,
870 p_cost_adjust_id => p_adjustment_rec.cost_adjust_id,
871 p_organization_id => p_adjustment_rec.organization_id,
872 p_inventory_item_id => p_adjustment_rec.inventory_item_id,
873 p_cost_type_id => p_adjustment_rec.cost_type_id,
874 p_period_id => p_adjustment_rec.period_id,
875 p_cost_cmpntcls_id => p_adjustment_rec.cost_cmpntcls_id,
876 p_cost_analysis_code => p_adjustment_rec.cost_analysis_code,
877 p_adjustment_ind => p_adjustment_rec.adjustment_ind
878 )
879 LOOP
880 p_adjustment_rec.organization_id := i.ORGANIZATION_ID;
881 p_adjustment_rec.inventory_item_id := i.INVENTORY_ITEM_ID;
882 p_adjustment_rec.cost_type_id := i.COST_TYPE_ID;
883 p_adjustment_rec.period_id := i.PERIOD_ID;
884 p_adjustment_rec.cost_cmpntcls_id := i.COST_CMPNTCLS_ID;
885 p_adjustment_rec.cost_analysis_code := i.COST_ANALYSIS_CODE;
886 p_adjustment_rec.cost_adjust_id := i.COST_ADJUST_ID;
887 p_adjustment_rec.adjust_qty := i.ADJUST_QTY;
888 p_adjustment_rec.adjust_qty_uom := i.ADJUST_QTY_UOM;
889 p_adjustment_rec.adjust_cost := i.ADJUST_COST;
890 p_adjustment_rec.reason_code := i.REASON_CODE;
891 p_adjustment_rec.adjust_status := i.ADJUST_STATUS;
892 p_adjustment_rec.creation_date := i.CREATION_DATE;
893 p_adjustment_rec.last_update_login := i.LAST_UPDATE_LOGIN;
894 p_adjustment_rec.created_by := i.CREATED_BY;
895 p_adjustment_rec.last_update_date := i.LAST_UPDATE_DATE;
896 p_adjustment_rec.last_updated_by := i.LAST_UPDATED_BY;
897 p_adjustment_rec.text_code := i.TEXT_CODE;
898 p_adjustment_rec.trans_cnt := i.TRANS_CNT;
899 p_adjustment_rec.delete_mark := i.DELETE_MARK;
900 p_adjustment_rec.request_id := i.REQUEST_ID;
901 p_adjustment_rec.program_application_id := i.PROGRAM_APPLICATION_ID;
902 p_adjustment_rec.program_id := i.PROGRAM_ID;
903 p_adjustment_rec.program_update_date := i.PROGRAM_UPDATE_DATE;
904 p_adjustment_rec.attribute_category := i.ATTRIBUTE_CATEGORY;
905 p_adjustment_rec.attribute1 := i.ATTRIBUTE1;
906 p_adjustment_rec.attribute2 := i.ATTRIBUTE2;
907 p_adjustment_rec.attribute3 := i.ATTRIBUTE3;
908 p_adjustment_rec.attribute4 := i.ATTRIBUTE4;
909 p_adjustment_rec.attribute5 := i.ATTRIBUTE5;
910 p_adjustment_rec.attribute6 := i.ATTRIBUTE6;
911 p_adjustment_rec.attribute7 := i.ATTRIBUTE7;
912 p_adjustment_rec.attribute8 := i.ATTRIBUTE8;
913 p_adjustment_rec.attribute9 := i.ATTRIBUTE9;
914 p_adjustment_rec.attribute10 := i.ATTRIBUTE10;
915 p_adjustment_rec.attribute11 := i.ATTRIBUTE11;
916 p_adjustment_rec.attribute12 := i.ATTRIBUTE12;
917 p_adjustment_rec.attribute13 := i.ATTRIBUTE13;
918 p_adjustment_rec.attribute14 := i.ATTRIBUTE14;
919 p_adjustment_rec.attribute15 := i.ATTRIBUTE15;
920 p_adjustment_rec.attribute16 := i.ATTRIBUTE16;
921 p_adjustment_rec.attribute17 := i.ATTRIBUTE17;
922 p_adjustment_rec.attribute18 := i.ATTRIBUTE18;
923 p_adjustment_rec.attribute19 := i.ATTRIBUTE19;
924 p_adjustment_rec.attribute20 := i.ATTRIBUTE20;
925 p_adjustment_rec.attribute21 := i.ATTRIBUTE21;
926 p_adjustment_rec.attribute22 := i.ATTRIBUTE22;
927 p_adjustment_rec.attribute23 := i.ATTRIBUTE23;
928 p_adjustment_rec.attribute24 := i.ATTRIBUTE24;
929 p_adjustment_rec.attribute25 := i.ATTRIBUTE25;
930 p_adjustment_rec.attribute26 := i.ATTRIBUTE26;
931 p_adjustment_rec.attribute27 := i.ATTRIBUTE27;
932 p_adjustment_rec.attribute28 := i.ATTRIBUTE28;
933 p_adjustment_rec.attribute29 := i.ATTRIBUTE29;
934 p_adjustment_rec.attribute30 := i.ATTRIBUTE30;
935 p_adjustment_rec.adjustment_ind := i.ADJUSTMENT_IND;
936 p_adjustment_rec.subledger_ind := i.SUBLEDGER_IND;
937 p_adjustment_rec.adjustment_date := i.ADJUSTMENT_DATE;
938 END LOOP;
939 ELSE
940 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level
941 THEN
942 log_msg ( ' No Actual Cost Adjustments retrieved for ' ||
943 ' Item ' || p_adjustment_rec.inventory_item_id ||
944 ' Organization ' || p_adjustment_rec.organization_id ||
945 ' Cost Type ' || p_adjustment_rec.cost_type_id ||
946 ' Period Id ' || p_adjustment_rec.period_id ||
947 ' Cost Component Class ' || p_adjustment_rec.cost_cmpntcls_id ||
948 ' Analysis code ' || p_adjustment_rec.cost_analysis_code ||
949 ' Adjustment Indicator ' || p_adjustment_rec.adjustment_ind
950 );
951 END IF;
952 RAISE FND_API.G_EXC_ERROR;
953 END IF;
954
955 /**************************************************************************
956 * Standard call to get message count and if count is 1, get message info. *
957 **************************************************************************/
958 FND_MSG_PUB.Count_And_Get (
959 p_count => x_msg_count
960 , p_data => x_msg_data
961 );
962 EXCEPTION
963 WHEN FND_API.G_EXC_ERROR THEN
964 x_return_status := FND_API.G_RET_STS_ERROR ;
965 FND_MSG_PUB.Count_And_Get (
966 p_count => x_msg_count
967 , p_data => x_msg_data
968 );
969 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
970 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
971 FND_MSG_PUB.Count_And_Get (
972 p_count => x_msg_count
973 , p_data => x_msg_data
974 );
975 WHEN OTHERS THEN
976 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
977 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
978 THEN
979 FND_MSG_PUB.Add_Exc_Msg (
980 G_PKG_NAME
981 , l_api_name
982 );
983 END IF;
984 FND_MSG_PUB.Count_And_Get (
985 p_count => x_msg_count
986 , p_data => x_msg_data
987 );
988 END GET_ACTUAL_COST_ADJUSTMENT;
989 END GMF_ACTUAL_COST_ADJUSTMENT_PVT;