[Home] [Help]
PACKAGE BODY: APPS.CST_MGD_INFL_ADJUSTMENT_CP
Source
1 PACKAGE BODY CST_MGD_INFL_ADJUSTMENT_CP AS
2 /* $Header: CSTCIADB.pls 120.6 2006/02/26 22:44:36 vmutyala noship $ */
3
4 --================================
5 -- PRIVATE CONSTANTS AND VARIABLES
6 --================================
7 g_period_is_final_exc EXCEPTION;
8 g_price_index_exc EXCEPTION;
9
10 g_infl_index_value_null_exc EXCEPTION;
11
12 G_MODULE_HEAD CONSTANT VARCHAR2(50) := 'cst.plsql.' || G_PKG_NAME || '.';
13
14 --===================
15 -- PRIVATE PROCEDURES
16 --===================
17
18 --=========================================================================
19 -- PROCEDURE : Check_Inflation_Process_Run PRIVATE
20 -- PARAMETERS: p_org_id Organization ID
21 -- p_acct_period_id Account period ID
22 -- x_return_status Return Status of procedure
23 -- COMMENT : This procedure checks whether inflation adjustment processor
24 -- has been run for the organization in that accounting
25 -- period id
26 -- This procedure will use the status table:
27 -- CST_MGD_INFL_ADJ_PER_STATUSES
28 -- USAGE : Used for validation in Transfer_to_GL Process
29 -- EXCEPTIONS: l_infl_processor_run_exc
30 --========================================================================
31 PROCEDURE Check_Inflation_Process_Run (
32 p_org_id IN NUMBER
33 , p_acct_period_id IN NUMBER
34 , x_return_status OUT NOCOPY VARCHAR2
35 )
36 IS
37 l_infl_processor_run_exc EXCEPTION;
38 l_process_count NUMBER;
39
40 -- check if the inflation processor is run for the organization
41 -- in that accounting period range
42 CURSOR inflation_check_cur(c_org_id NUMBER
43 ,c_acct_period_id NUMBER
44 )
45 IS
46 SELECT
47 COUNT(*)
48 FROM cst_mgd_infl_adj_per_statuses
49 WHERE organization_ID = c_org_id
50 AND acct_period_id = c_acct_period_id
51 AND status = 'PROCESS';
52
53 BEGIN
54
55 OPEN inflation_check_cur(p_org_id
56 ,p_acct_period_id
57 );
58
59 FETCH inflation_check_cur
60 INTO l_process_count;
61
62 CLOSE inflation_check_cur;
63
64 IF l_process_count = 0
65 THEN
66 -- Inflation Adjustment Processor not run yet
67 RAISE l_infl_processor_run_exc;
68 END IF;
69
70 x_return_status := FND_API.G_RET_STS_SUCCESS;
71
72 EXCEPTION
73 WHEN l_infl_processor_run_exc THEN
74 FND_MESSAGE.Set_Name('BOM', 'CST_INFL_PROCESSOR_RUN');
75 FND_MSG_PUB.Add;
76 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
77 THEN
78 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
79 , 'Check_Inflation_Process_Run'
80 );
81 END IF;
82 x_return_status := FND_API.G_RET_STS_ERROR;
83 RAISE;
84
85 WHEN OTHERS THEN
86 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
87 THEN
88 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
89 , 'Check_Inflation_Process_Run'
90 );
91 END IF;
92 RAISE;
93
94 END Check_Inflation_Process_Run;
95
96
97 --========================================================================
98 -- PROCEDURE : Check_Period_Status PRIVATE
99 -- PARAMETERS: p_country_code Country code
100 -- p_org_id Organization ID
101 -- p_acct_period_id Account period ID
102 -- COMMENT : This procedure checks if inflation adjustment for a
103 -- period is marked "FINAL"
104 -- EXCEPTIONS: g_period_is_final_exc Period is final
105 --========================================================================
106 PROCEDURE Check_Period_Status (
107 p_country_code IN VARCHAR2
108 , p_org_id IN NUMBER
109 , p_acct_period_id IN NUMBER
110 )
111 IS
112 l_routine CONSTANT VARCHAR2(30) := 'check_period_status';
113 l_status NUMBER;
114 l_period_is_final_exc EXCEPTION;
115 BEGIN
116
117 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
118 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
119 ,G_MODULE_HEAD || l_routine || '.begin'
120 ,l_routine || '<'
121 );
122 END IF;
123
124 -- check if period has been adjusted and posted to GL
125 SELECT
126 COUNT(1)
127 INTO
128 l_status
129 FROM
130 CST_MGD_INFL_ADJ_PER_STATUSES a
131 , ORG_ACCT_PERIODS b
132 WHERE a.Organization_ID = p_org_id
133 AND a.Acct_Period_ID = p_acct_period_id
134 AND a.Status = 'FINAL'
135 AND b.Acct_Period_ID = p_acct_period_id
136 AND b.Organization_ID = p_org_id
137 AND b.Open_Flag = 'N'
138 AND b.Period_Close_Date IS NOT NULL;
139
140 IF l_status > 0
141 THEN
142 RAISE l_period_is_final_exc;
143 END IF;
144
145 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
146 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
147 ,G_MODULE_HEAD || l_routine || '.end'
148 ,l_routine || '>'
149 );
150 END IF;
151
152 EXCEPTION
153
154 WHEN l_period_is_final_exc THEN
155 FND_MESSAGE.Set_Name('BOM', 'CST_MGD_INFL_PERIOD_FINAL');
156 FND_MSG_PUB.Add;
157 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
158 THEN
159 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
160 , 'Check_Period_Status'
161 );
162 END IF;
163 RAISE g_period_is_final_exc;
164 WHEN OTHERS THEN
165 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
166 THEN
167 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
168 , 'Check_Period_Status'
169 );
170 END IF;
171 RAISE;
172
173 END Check_Period_Status;
174
175
176 --========================================================================
177 -- PROCEDURE : Get_Inflation_Index_Value PRIVATE
178 -- PARAMETERS: p_org_id Organization ID
179 -- p_acct_period_id Account period ID
180 -- p_inflation_index Inflation index
181 -- x_inflation_index_value Inflation index value
182 -- COMMENT : This procedure retrieves the inflation value (%) to be used
183 -- for adjustment from inflation index.
184 -- EXCEPTIONS: OTHERS
185 --========================================================================
186 PROCEDURE Get_Inflation_Index_Value (
187 p_org_id IN NUMBER
188 , p_acct_period_id IN NUMBER
189 , p_inflation_index IN VARCHAR2
190 , x_inflation_index_value OUT NOCOPY NUMBER
191 )
192 IS
193 l_routine CONSTANT VARCHAR2(30) := 'get_inflation_index_value';
194 BEGIN
195
196 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
197 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
198 ,G_MODULE_HEAD || l_routine || '.begin'
199 ,l_routine || '<'
200 );
201 END IF;
202
203 SELECT
204 Price_Index_Value/100
205 INTO
206 x_inflation_index_value
207 FROM
208 FA_PRICE_INDEX_VALUES a
209 , FA_PRICE_INDEXES b
210 , ORG_ACCT_PERIODS c
211 WHERE c.Acct_Period_ID = p_acct_period_id
212 AND c.Organization_ID = p_org_id
213 AND b.Price_Index_Name = p_inflation_index
214 AND a.Price_Index_ID = b.Price_Index_ID
215 AND a.From_Date = c.Period_Start_Date
216 AND a.To_Date = c.Schedule_Close_Date
217 AND c.Open_Flag = 'N';
218
219 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
220 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
221 ,G_MODULE_HEAD || l_routine || '.end'
222 ,l_routine || '>'
223 );
224 END IF;
225
226 EXCEPTION
227 WHEN NO_DATA_FOUND THEN
228 FND_MESSAGE.Set_Name('BOM', 'CST_MGD_INFL_PRICE_INDEX');
229 FND_MSG_PUB.Add;
230 RAISE g_price_index_exc;
231 WHEN OTHERS THEN
232 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
233 THEN
234 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
235 , 'Get_Inflation_Index_Value'
236 );
237 END IF;
238 RAISE;
239
240 END Get_Inflation_Index_Value;
241
242
243 --========================================================================
244 -- PROCEDURE : Get_valid_cost_group PRIVATE
245 -- PARAMETERS: p_org_id Organization ID
246 -- x_cost_group_id valid cost group id
247 -- COMMENT : Procedure to get the valid cost group
248 --========================================================================
249 PROCEDURE Get_valid_cost_group (
250 p_org_id IN NUMBER
251 ,x_cost_group_id OUT NOCOPY CST_COST_GROUPS.cost_group_id%TYPE
252 )
253 IS
254 l_routine CONSTANT VARCHAR2(30) := 'get_valid_cost_group';
255
256 -- get the default cost group
257 CURSOR c_default_cost_group_cur IS
258 SELECT
259 default_cost_group_id
260 FROM MTL_PARAMETERS
261 WHERE organization_id = p_org_id;
262
263 BEGIN
264
265 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
266 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
267 ,G_MODULE_HEAD || l_routine || '.begin'
268 ,l_routine || '<'
269 );
270 END IF;
271
272 -- Get the default cost group
273 OPEN c_default_cost_group_cur;
274
275 FETCH c_default_cost_group_cur
276 INTO x_cost_group_id;
277
278 CLOSE c_default_cost_group_cur;
279
280 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
281 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
282 ,G_MODULE_HEAD || l_routine || '.end'
283 ,l_routine || '>'
284 );
285 END IF;
286
287 END;
288
289
290 --========================================================================
291 -- PROCEDURE : Calculate_Adjustment PRIVATE
292 -- PARAMETERS: x_errbuf error buffer
293 -- x_retcode 0 success, 1 warning, 2 error
294 -- p_org_id Organization ID
295 -- p_country_code Country code
296 -- p_acct_period_id Account period ID
297 -- p_inflation_index Inflation index
298 -- COMMENT : This is the concurrent program for inflation adjustment.
299 --========================================================================
300 PROCEDURE Calculate_Adjustment (
301 x_errbuf OUT NOCOPY VARCHAR2
302 , x_retcode OUT NOCOPY VARCHAR2
303 , p_org_id IN NUMBER
304 , p_country_code IN VARCHAR2
305 , p_acct_period_id IN NUMBER
306 , p_inflation_index IN VARCHAR2
307 )
308 IS
309 l_routine CONSTANT VARCHAR2(30) := 'calculate_adjustment';
310
311 l_api_version_number NUMBER := 1.0;
312 l_org_id NUMBER;
313 l_inv_item_id NUMBER;
314 l_item_unit_avg_cost NUMBER;
315 l_status NUMBER;
316 l_return_status VARCHAR2(1);
317 l_last_closed_period_id NUMBER;
318 l_last_adjusted_period_id NUMBER;
319 l_last_acct_period_id NUMBER;
320 l_inflation_index_value NUMBER;
321 l_msg_count NUMBER;
322 l_msg_data VARCHAR2(100);
323 l_get_hist_data_flag VARCHAR2(1);
324 l_prev_acct_period_id NUMBER;
325 l_prev_sch_close_date DATE;
326 l_curr_period_start_date DATE;
327 l_curr_period_end_date DATE;
328 l_profile_category_name VARCHAR2(100);
329 l_category_id VARCHAR2(100);
330 l_profile_category_set_name VARCHAR2(100);
331 l_category_set_id VARCHAR2(100);
332 l_cost_group_id CST_COST_GROUPS.cost_group_id%TYPE;
333 l_inflation_adjustment_rec
334 CST_MGD_INFL_ADJUSTMENT_PUB.Inflation_Adjustment_Rec_Type;
335 l_period_gap_exc EXCEPTION;
336 l_period_is_missing_exc EXCEPTION;
337 l_missing_hist_data_exc EXCEPTION;
338
339 --Bug 3889172
340 l_wms_org_exc EXCEPTION;
341 l_wms_enabled_flag VARCHAR2(1);
342
343 /* Bug 5026760 Non Mergeable View cst_per_close_dtls_v replaced by union on base tables */
344 CURSOR l_inv_items_csr(c_acct_period_id NUMBER
345 ,c_organization_id NUMBER
346 ,c_cost_group_id NUMBER
347 ,c_category_set_id NUMBER
348 ,c_category_id NUMBER
349 ) IS
350 SELECT
351 DISTINCT inventory_item_id
352 FROM
353 cst_period_close_summary
354 WHERE acct_period_id = c_acct_period_id
355 AND organization_id = c_organization_id
356 AND cost_group_id = c_cost_group_id
357 AND CST_MGD_INFL_ADJUSTMENT_PVT.Infl_Item_Category(inventory_item_id
358 ,c_organization_id
359 ,c_category_set_id
360 ,c_category_id
361 ) = 'Y'
362 UNION
363 SELECT
364 DISTINCT inventory_item_id
365 FROM
366 mtl_per_close_dtls
367 WHERE acct_period_id = c_acct_period_id
368 AND organization_id = c_organization_id
369 AND cost_group_id = c_cost_group_id
370 AND CST_MGD_INFL_ADJUSTMENT_PVT.Infl_Item_Category(inventory_item_id
371 ,c_organization_id
372 ,c_category_set_id
373 ,c_category_id
374 ) = 'Y';
375
376
377 -- debug level variables to use within loop
378 l_debug_level NUMBER;
379 l_state_level NUMBER;
380
381 BEGIN
382
383 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
384 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
385 ,G_MODULE_HEAD || l_routine || '.begin'
386 ,l_routine || '<'
387 );
388 END IF;
389
390 -- initialize item category and item category set profile options
391 l_profile_category_name := 'CST_MGD_INFL_ADJ_CTG';
392 l_profile_category_set_name := 'CST_MGD_INFL_ADJ_CTG_SET';
393
394 -- initialize debug level variables to use within loop
395 l_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
396 l_state_level := FND_LOG.LEVEL_STATEMENT;
397
398 -- initialize the message stack
399 FND_MSG_PUB.Initialize;
400
401 -- Bug # 3889172 check if the Org is WMS enabled.
402 SELECT WMS_ENABLED_FLAG
403 INTO l_wms_enabled_flag
404 FROM MTL_PARAMETERS
405 WHERE ORGANIZATION_ID = p_org_id;
406
407 IF l_wms_enabled_flag = 'Y' THEN
408 RAISE l_wms_org_exc;
409 END IF;
410
414 );
411 CST_MGD_INFL_ADJUSTMENT_PVT.Check_Period_Close
412 ( p_org_id => p_org_id
413 , p_acct_period_id => p_acct_period_id
415
416 -- Check if the inflation is posted to GL for the current period
417 Check_Period_Status( p_country_code => p_country_code
418 , p_org_id => p_org_id
419 , p_acct_period_id => p_acct_period_id
420 );
421
422 /* removed as part of bug#1474753 fix
423 -- Check for historical data
424 CST_MGD_INFL_ADJUSTMENT_PVT.Check_First_Time
425 ( p_country_code => p_country_code
426 , p_org_id => p_org_id
427 , x_get_hist_data_flag => l_get_hist_data_flag
428 );
429 */
430
431
432 -- Get previous account period id and scheduled close date.
433 -- previous period obtained only if the previous period inflation
434 -- is transferred to GL,otherwise returns null
435 --
436 CST_MGD_INFL_ADJUSTMENT_PVT.Get_Previous_Acct_Period_ID
437 ( p_organization_id => p_org_id
438 , p_acct_period_id => p_acct_period_id
439 , x_prev_acct_period_id => l_prev_acct_period_id
440 , x_prev_sch_close_date => l_prev_sch_close_date
441 );
442
443
444 -- Close date set to 23:59:59
445 IF l_prev_sch_close_date IS NOT NULL THEN
446 l_prev_sch_close_date := TRUNC(l_prev_sch_close_date) + (86399/86400);
447 END IF;
448
449 IF (FND_LOG.LEVEL_STATEMENT >= l_debug_level) THEN
450 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
451 ,G_MODULE_HEAD || l_routine || '.prevsch'
452 ,'Previous Acct Period Id:' || l_prev_acct_period_id || ' ' ||'Previous Schedule Close Date:' || TO_CHAR(l_prev_sch_close_date,'DD-MON-YYYY HH24:MI:SS')
453 );
454 END IF;
455
456 -- Get current period start date.
457 CST_MGD_INFL_ADJUSTMENT_PVT.Get_Curr_Period_Start_Date
458 ( p_org_id => p_org_id
459 , p_acct_period_id => p_acct_period_id
460 , x_curr_period_start_date => l_curr_period_start_date
461 , x_curr_period_end_date => l_curr_period_end_date
462 );
463
464
465 -- From date is at midnight for the day
466 -- bug#5012817 fix: remove canonical to date as it is already in
467 -- date format
468 l_curr_period_start_date := TRUNC(l_curr_period_start_date);
469
470 -- The to date is at 23:59:59 of that date entered
471 l_curr_period_end_date := TRUNC(l_curr_period_end_date) + (86399/86400);
472
473 IF (FND_LOG.LEVEL_STATEMENT >= l_debug_level) THEN
474 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
475 ,G_MODULE_HEAD || l_routine || '.curpddt'
476 ,'Current Period Start Date:' || TO_CHAR(l_curr_period_start_date,'DD-MON-YYYY HH24:MI:SS') || ' ' ||'Current Period End Date:' || TO_CHAR(l_curr_period_end_date,'DD-MON-YYYY HH24:MI:SS')
477 );
478 END IF;
479
480 -- check if the previous period exists
481 IF l_prev_sch_close_date IS NOT NULL THEN
482 -- Check inflation adjustment period gap.
483 IF l_curr_period_start_date > l_prev_sch_close_date + 1
484 THEN
485 RAISE l_period_gap_exc;
486 END IF;
487 END IF;
488
489 Get_Inflation_Index_Value
490 ( p_org_id => p_org_id
491 , p_acct_period_id => p_acct_period_id
492 , p_inflation_index => p_inflation_index
493 , x_inflation_index_value => l_inflation_index_value
494 );
495
496 IF (l_state_level >= l_debug_level) THEN
497 FND_LOG.string(l_state_level
498 , G_MODULE_HEAD || l_routine || '.itemcat'
499 , 'Item Category Set Id:' || l_category_set_id ||
500 ' Item Category Id:' || l_category_id
501 );
502 END IF;
503
504 -- delete pre-existing data if it exists
505 DELETE FROM
506 CST_MGD_INFL_ADJUSTED_COSTS
507 WHERE Organization_ID = p_org_id
508 AND Acct_Period_ID = p_acct_period_id
509 AND Country_Code = p_country_code
510 AND Historical_Flag = 'N';
511
512 DELETE FROM
513 CST_MGD_INFL_TSF_ORG_ENTRIES
514 WHERE Organization_ID = p_org_id
515 AND Acct_Period_ID = p_acct_period_id
516 AND Country_Code = p_country_code;
517
518 -- ========================================================================
519 -- Delete the status record only if not posted to GL
520 -- If the entries are posted to GL, then the status will be FINAL
521 -- This is to delete the status record from previous inflation run (if any)
522 -- where the entries are not yet posted to GL
523 -- ========================================================================
524 DELETE FROM
525 CST_MGD_INFL_ADJ_PER_STATUSES
526 WHERE organization_id = p_org_id
527 AND acct_period_id = p_acct_period_id
528 AND status = 'PROCESS';
529
530 FND_PROFILE.Get(l_profile_category_name, l_category_id);
531 FND_PROFILE.Get(l_profile_category_set_name, l_category_set_id);
532
533 IF (l_state_level >= l_debug_level) THEN
534 FND_LOG.string(l_state_level
535 , G_MODULE_HEAD || l_routine || '.itemcat'
536 , 'Item Category Set Id:' || l_category_set_id ||
537 ' Item Category Id:' || l_category_id
538 );
542 -- Get valid cost group
539 END IF;
540
541
543 Get_valid_cost_group( p_org_id
544 ,l_cost_group_id);
545
546
547 IF (FND_LOG.LEVEL_EVENT >= l_debug_level) THEN
548 FND_LOG.string(FND_LOG.LEVEL_EVENT
549 , G_MODULE_HEAD || l_routine || '.cg_group'
550 , 'Cost Group Id:' || to_char(l_cost_group_id)
551 );
552 END IF;
553
554 -- Calculate inflation adjustment for each item
555 FOR k IN l_inv_items_csr(p_acct_period_id
556 ,p_org_id
557 ,l_cost_group_id
558 ,TO_NUMBER(l_category_set_id)
559 ,TO_NUMBER(l_category_id)
560 ) LOOP
561
562 l_inv_item_id := k.inventory_item_id;
563
564 IF (l_state_level >= l_debug_level) THEN
565 FND_LOG.string(l_state_level
566 , G_MODULE_HEAD || l_routine || '.itemcsr'
567 , 'Item Id:' || l_inv_item_id || ' ' ||
568 'Org Id:' || p_org_id || ' ' ||
569 'Country Code:' || p_country_code || ' ' ||
570 'Acct Period Id:' || p_acct_period_id
571 );
572 END IF;
573
574 l_inflation_adjustment_rec.country_code := p_country_code;
575 l_inflation_adjustment_rec.organization_id := p_org_id;
576 l_inflation_adjustment_rec.acct_period_id := p_acct_period_id;
577 l_inflation_adjustment_rec.inventory_item_id := l_inv_item_id;
578 l_inflation_adjustment_rec.category_id :=
579 TO_NUMBER(l_category_id);
580 l_inflation_adjustment_rec.category_set_id :=
581 TO_NUMBER(l_category_set_id);
582 l_inflation_adjustment_rec.last_update_date := SYSDATE;
583 l_inflation_adjustment_rec.last_updated_by :=
584 NVL(TO_NUMBER(fnd_profile.value('USER_ID')),0);
585 l_inflation_adjustment_rec.creation_date := SYSDATE;
586 l_inflation_adjustment_rec.created_by :=
587 NVL(TO_NUMBER(fnd_profile.value('USER_ID')),0);
588 l_inflation_adjustment_rec.last_update_login :=
589 TO_NUMBER(fnd_profile.value('LOGIN_ID'));
590 l_inflation_adjustment_rec.request_id :=
591 TO_NUMBER(fnd_profile.value('CONC_REQUEST_ID'));
592 l_inflation_adjustment_rec.program_application_id :=
593 TO_NUMBER(fnd_profile.value('PROG_APPL_ID'));
594 l_inflation_adjustment_rec.program_id :=
595 TO_NUMBER(fnd_profile.value('CONC_PROG_ID'));
596 l_inflation_adjustment_rec.program_update_date := SYSDATE;
597
598 -- get period end item unit avg. cost
599 CST_MGD_INFL_ADJUSTMENT_PVT.Get_Period_End_Avg_Cost
600 ( p_acct_period_id => p_acct_period_id
601 , p_org_id => p_org_id
602 , p_inv_item_id => l_inv_item_id
603 , p_cost_group_id => l_cost_group_id
604 , x_period_end_item_avg_cost => l_item_unit_avg_cost
605 );
606
607 l_inflation_adjustment_rec.item_unit_cost := l_item_unit_avg_cost;
608
609 IF (l_state_level >= l_debug_level) THEN
610 FND_LOG.string(l_state_level
611 , G_MODULE_HEAD || l_routine || '.itemunitavgcost'
612 , 'Item Unit Average Cost:' || l_item_unit_avg_cost
613 );
614 END IF;
615
616 -- calling inflation adjustment engine
617 CST_MGD_INFL_ADJUSTMENT_PVT.Create_Inflation_Adjusted_Cost
618 ( p_api_version_number => l_api_version_number
619 , p_init_msg_list => FND_API.G_FALSE
620 , x_return_status => l_return_status
621 , x_msg_count => l_msg_count
622 , x_msg_data => l_msg_data
623 , p_inflation_index_value => l_inflation_index_value
624 , p_prev_acct_period_id => l_prev_acct_period_id
625 , p_inflation_adjustment_rec => l_inflation_adjustment_rec
626 , p_cost_group_id => l_cost_group_id
627 );
628 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
629 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
630 END IF;
631
632 END LOOP; -- for item cursor
633
634 -- ======================================================
635 -- Set the inflation processor status to PROCESS
636 -- Insert a record with status PROCESS
637 -- ======================================================
638 CST_MGD_INFL_ADJUSTMENT_PVT.Create_Infl_Period_Status
639 ( p_org_id => p_org_id
640 , p_acct_period_id => p_acct_period_id
641 , x_return_status => l_return_status
642 );
643
644 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
645 THEN
646 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
647 END IF;
648
649 -- report success
650 x_errbuf := NULL;
651 x_retcode := 0;
652
653 IF (FND_LOG.LEVEL_PROCEDURE >= l_debug_level) THEN
654 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
655 ,G_MODULE_HEAD || l_routine || '.end'
656 ,l_routine || '>'
660 EXCEPTION
657 );
658 END IF;
659
661
662 -- Bug # 3889172
663 WHEN l_wms_org_exc THEN
664 FND_MESSAGE.set_name('BOM', 'CST_MGD_INFL_WMS_ORG');
665 FND_MSG_PUB.Add;
666 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
667 THEN
668 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
669 , 'Calculate_Adjustment'
670 );
671 END IF;
672 x_errbuf := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
673 x_retcode := 2;
674
675 WHEN l_period_gap_exc THEN
676 FND_MESSAGE.Set_Name('BOM', 'CST_MGD_INFL_PERIOD_GAP');
677 FND_MSG_PUB.Add;
678 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
679 THEN
680 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
681 , 'Calculate_Adjustment'
682 );
683 END IF;
684 x_errbuf := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
685 x_retcode := 2;
686 WHEN g_price_index_exc THEN
687 x_errbuf := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
688 x_retcode := 2;
689 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
690 x_errbuf := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
691 x_retcode := 2;
692 WHEN OTHERS THEN
693 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
694 THEN
695 FND_MSG_PUB.Add_Exc_Msg
696 ( G_PKG_NAME
697 , 'Calculate_Adjustment'
698 );
699 END IF;
700 x_errbuf := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
701 x_retcode := 2;
702
703 END Calculate_Adjustment;
704
705
706 --=======================================================================i
707 -- PROCEDURE : Get_User_Category_Name PRIVATE
708 -- PARAMETERS: p_je_category_name JE catergory name
709 -- x_user_category_name User catergory name
710 -- COMMENT : This procedure takes je_category_name and returns
711 -- user_category_name.
712 -- EXCEPTIONS: OTHERS
713 --========================================================================
714 PROCEDURE Get_User_Category_Name (
715 p_je_category_name IN VARCHAR2
716 , x_user_category_name OUT NOCOPY VARCHAR2
717 )
718 IS
719 BEGIN
720
721 SELECT
722 User_JE_Category_Name
723 INTO
724 x_user_category_name
725 FROM
726 GL_JE_CATEGORIES
727 WHERE JE_Category_Name = p_je_category_name;
728
729 EXCEPTION
730
731 WHEN OTHERS THEN
732 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
733 THEN
734 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
735 , 'Get_User_Category_Name'
736 );
737 END IF;
738 RAISE;
739
740 END Get_User_Category_Name;
741
742
743 --========================================================================
744 -- PROCEDURE : Get_User_Source_Name PRIVATE
745 -- PARAMETERS: p_je_source_name JE source name
746 -- x_user_source_name User source name
747 -- COMMENT : This procedure takes je_source_name and returns
748 -- user_source_name.
749 -- EXCEPTIONS: OTHERS
750 --========================================================================
751 PROCEDURE Get_User_Source_Name (
752 p_je_source_name IN VARCHAR2
753 , x_user_source_name OUT NOCOPY VARCHAR2
754 )
755 IS
756 BEGIN
757
758 SELECT
759 User_JE_Source_Name
760 INTO
761 x_user_source_name
762 FROM
763 GL_JE_SOURCES
764 WHERE JE_Source_Name = p_je_source_name;
765
766 EXCEPTION
767
768 WHEN OTHERS THEN
769 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
770 THEN
771 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
772 , 'Get_User_Source_Name'
773 );
774 END IF;
775 RAISE;
776
777 END Get_User_Source_Name;
778
779
780 --=======================================================================
781 -- PROCEDURE : Transfer_to_GL PRIVATE
782 -- PARAMETERS: x_errbuf error buffer
783 -- x_retcode 0 success, 1 warning, 2 error
784 -- p_org_id Organization ID
785 -- p_country_code Country code
786 -- p_acct_period_id Account perio ID
787 -- COMMENT : This concurrent program creates account entries for
788 -- inflation adjusted items and set the period to final.
789 --========================================================================
790 PROCEDURE Transfer_to_GL (
791 x_errbuf OUT NOCOPY VARCHAR2
792 , x_retcode OUT NOCOPY VARCHAR2
793 , p_org_id IN NUMBER
794 , p_country_code IN VARCHAR2
795 , p_acct_period_id IN NUMBER
796 )
797 IS
798 l_routine CONSTANT VARCHAR2(30) := 'transfer_to_gl';
799 l_acct_entry_tbl_rec
800 CST_MGD_INFL_ADJUSTMENT_PVT.Infl_Adj_Acct_Tbl_Rec_Type;
801 l_transfer_entry_tbl_rec
802 CST_MGD_INFL_ADJUSTMENT_PVT.Infl_Adj_Acct_Tbl_Rec_Type;
803 l_status NUMBER;
804 l_infl_adj_item_id NUMBER;
805 l_inventory_adj_acct_cr NUMBER;
806 l_inventory_adj_acct_dr NUMBER;
807 l_monetary_corr_acct_cr NUMBER;
808 l_sales_cost_acct_dr NUMBER;
809 l_transfer_org_id NUMBER;
810 l_transfer_acct_cr NUMBER;
811 l_transfer_acct_dr NUMBER;
812 l_index BINARY_INTEGER;
813 l_return_status VARCHAR2(1);
814 l_set_of_books_id NUMBER;
815 l_currency_code VARCHAR2(15);
816 l_user_category_name VARCHAR(25);
817 l_user_source_name VARCHAR(25);
818 l_curr_period_start_date DATE;
819 l_curr_period_end_date DATE;
820
821 no_infl_processor_run_exc EXCEPTION;
822
823 CURSOR l_infl_adj_item_csr IS
824 SELECT
825 ADJ.Inventory_Item_ID
826 , ADJ.Inventory_Adj_Acct_CR
827 , ADJ.Inventory_Adj_Acct_DR
828 , ADJ.Monetary_Corr_Acct_CR
829 , ADJ.Sales_Cost_Acct_DR
830 FROM
831 CST_MGD_INFL_ADJUSTED_COSTS ADJ
835 AND ADJ.Organization_ID = p_org_id
832 , MTL_SYSTEM_ITEMS SYS
833 WHERE ADJ.Country_Code = p_country_code
834 AND ADJ.Acct_Period_ID = p_acct_period_id
836 AND ADJ.Inventory_Item_ID = SYS.Inventory_Item_ID
837 AND SYS.Organization_ID = p_org_id;
838 BEGIN
839
840 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
841 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
842 ,G_MODULE_HEAD || l_routine || '.begin'
843 ,l_routine || '<'
844 );
845 END IF;
846
847 -- initialize the message stack
848 FND_MSG_PUB.Initialize;
849
850 -- Check whether transfer to GL already run
851 Check_Period_Status
852 ( p_country_code => p_country_code
853 , p_org_id => p_org_id
854 , p_acct_period_id => p_acct_period_id
855 );
856
857 -- Check Inflation processor run
858 Check_Inflation_Process_Run
859 ( p_org_id => p_org_id
860 , p_acct_period_id => p_acct_period_id
861 , x_return_status => l_return_status
862 );
863
864 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
865 RAISE no_infl_processor_run_exc;
866 END IF;
867
868
869 CST_MGD_INFL_ADJUSTMENT_PVT.Get_Set_Of_Books_ID
870 ( p_org_id => p_org_id
871 , x_set_of_books_id => l_set_of_books_id
872 );
873
874 CST_MGD_INFL_ADJUSTMENT_PVT.Get_Currency_Code
875 ( p_set_of_books_id => l_set_of_books_id
876 , x_currency_code => l_currency_code
877 );
878
879 Get_User_Category_Name
880 ( p_je_category_name => 'Adjustment'
881 , x_user_category_name => l_user_category_name
882 );
883
884 Get_User_Source_Name
885 ( p_je_source_name => 'Inflation'
886 , x_user_source_name => l_user_source_name
887 );
888
889 CST_MGD_INFL_ADJUSTMENT_PVT.Get_Curr_Period_Start_Date
890 ( p_org_id => p_org_id
891 , p_acct_period_id => p_acct_period_id
892 , x_curr_period_start_date => l_curr_period_start_date
893 , x_curr_period_end_date => l_curr_period_end_date
894 );
895
896 -- From date is at midnight for the day
897 -- bug#5012817 fix: remove canonical to date as it is already in
898 -- date format
899 l_curr_period_start_date := TRUNC(l_curr_period_start_date);
900
901 -- The to date is at 23:59:59 of that date entered
902 l_curr_period_end_date := TRUNC(l_curr_period_end_date) + (86399/86400);
903
904
905 OPEN l_infl_adj_item_csr;
906 LOOP
907 FETCH
908 l_infl_adj_item_csr
909 INTO
910 l_infl_adj_item_id
911 , l_inventory_adj_acct_cr
912 , l_inventory_adj_acct_dr
913 , l_monetary_corr_acct_cr
914 , l_sales_cost_acct_dr;
915 EXIT WHEN l_infl_adj_item_csr%NOTFOUND;
916
917 CST_MGD_INFL_ADJUSTMENT_PVT.GL_Interface_Default
918 ( p_country_code => p_country_code
919 , p_org_id => p_org_id
920 , p_inv_item_id => l_infl_adj_item_id
921 , p_acct_period_id => p_acct_period_id
922 , p_inventory_adj_acct_cr => l_inventory_adj_acct_cr
923 , p_inventory_adj_acct_dr => l_inventory_adj_acct_dr
924 , p_monetary_corr_acct_cr => l_monetary_corr_acct_cr
925 , p_sales_cost_acct_dr => l_sales_cost_acct_dr
926 , p_set_of_books_id => l_set_of_books_id
927 , p_currency_code => l_currency_code
928 , p_user_category_name => l_user_category_name
929 , p_user_source_name => l_user_source_name
930 , p_accounting_date => l_curr_period_end_date
931 , x_acct_entry_tbl_rec => l_acct_entry_tbl_rec
932 );
933
934 -- Post journal into GL_INTERFACE
935 l_index := NVL(l_acct_entry_tbl_rec.FIRST, 0);
936 IF l_index > 0
937 THEN
938 LOOP
939 IF NVL(l_acct_entry_tbl_rec(l_index).entered_cr, 0) +
940 NVL(l_acct_entry_tbl_rec(l_index).entered_dr, 0)
941 <> 0
942 THEN
943 CST_MGD_INFL_ADJUSTMENT_PVT.Create_Journal_Entries
944 ( p_infl_adj_acct_rec => l_acct_entry_tbl_rec(l_index)
945 );
946 END IF;
947 EXIT WHEN l_index = l_acct_entry_tbl_rec.LAST;
948 l_index := l_acct_entry_tbl_rec.NEXT(l_index);
949 END LOOP;
950 END IF;
951 END LOOP;
952
953 -- Set period status to final
954 -- Update status table to 'FINAL'
955 CST_MGD_INFL_ADJUSTMENT_PVT.Update_Infl_Period_Status
956 ( p_org_id => p_org_id
957 , p_acct_period_id => p_acct_period_id
958 , x_return_status => l_return_status
959 );
960
961 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
962 THEN
963 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
964 END IF;
965
966 -- report success
967 x_errbuf := NULL;
968 x_retcode := 0;
969
970 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
971 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
972 ,G_MODULE_HEAD || l_routine || '.end'
973 ,l_routine || '>'
974 );
975 END IF;
976
977 EXCEPTION
978 WHEN no_infl_processor_run_exc THEN
979 x_errbuf := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
980 x_retcode := 2;
981
982 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
983 x_errbuf := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
984 x_retcode := 2;
985 WHEN OTHERS THEN
986 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
987 THEN
988 FND_MSG_PUB.Add_Exc_Msg
989 ( G_PKG_NAME
990 , 'Transfer_to_GL'
991 );
992 END IF;
993 x_errbuf := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
994 x_retcode := 2;
995
996 END Transfer_to_GL;
997
998
999 END CST_MGD_INFL_ADJUSTMENT_CP;