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