[Home] [Help]
PACKAGE BODY: APPS.CST_MGD_INFL_ADJUSTMENT_PVT
Source
1 PACKAGE BODY CST_MGD_INFL_ADJUSTMENT_PVT AS
2 /* $Header: CSTVIADB.pls 120.13.12010000.2 2008/10/29 21:54:04 vjavli ship $ */
3
4 --===================
5 -- TYPES
6 --===================
7 TYPE Transfer_Rec_Type IS RECORD
8 ( transaction_id NUMBER
9 , inventory_item_id NUMBER
10 , organization_id NUMBER
11 , acct_period_id NUMBER
12 , last_update_date DATE
13 , last_updated_by NUMBER
14 , creation_date DATE
15 , created_by NUMBER
16 , last_update_login NUMBER
17 , request_id NUMBER
18 , program_application_id NUMBER
19 , program_id NUMBER
20 , program_update_date DATE
21 , country_code VARCHAR2(2)
22 , transfer_organization_id NUMBER
23 , entered_dr NUMBER
24 , entered_cr NUMBER
25 );
26
27 TYPE Transfer_Tbl_Rec_Type IS TABLE OF Transfer_Rec_Type
28 INDEX BY BINARY_INTEGER;
29
30 --===================
31 -- CONSTANTS
32 --===================
33
34 --================================
35 -- PRIVATE VARIABLES AND CONSTANTS
36 --================================
37 g_period_not_closed_exc EXCEPTION;
38 g_no_hist_data_exc EXCEPTION;
39 g_no_data_previous_data_exc EXCEPTION;
40 g_acct_ccid_null_exc EXCEPTION;
41 g_tnsf_period_gap_exc EXCEPTION;
42
43 G_MODULE_HEAD CONSTANT VARCHAR2(50) := 'cst.plsql.' || G_PKG_NAME || '.';
44
45 --===================
46 -- PRIVATE PROCEDURES
47 --===================
48
49 --========================================================================
50 -- PROCEDURE : Check_Period_Close PRIVATE
51 -- PARAMETERS: p_org_id Organization ID
52 -- : p_acct_period_id Account period ID
53 -- COMMENT : This procedure check if an accounting period is closed.
54 -- EXCEPTIONS: g_period_not_closed_exc Period is not closed
55 --========================================================================
56 PROCEDURE Check_Period_Close
57 ( p_org_id IN NUMBER
58 , p_acct_period_id IN NUMBER
59 )
60 IS
61 l_routine CONSTANT VARCHAR2(30) := 'check_period_close';
62
63 l_period_close_date DATE;
64 BEGIN
65
66 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
67 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
68 ,G_MODULE_HEAD || l_routine || '.begin'
69 ,l_routine || '<'
70 );
71 END IF;
72
73 SELECT
74 Period_Close_Date
75 INTO
76 l_period_close_date
77 FROM
78 ORG_ACCT_PERIODS
79 WHERE Organization_ID = p_org_id
80 AND Acct_Period_ID = p_acct_period_id
81 AND Open_Flag = 'N'
82 AND Period_Close_Date IS NOT NULL;
83
84 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
85 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
86 ,G_MODULE_HEAD || l_routine || '.end'
87 ,l_routine || '>'
88 );
89 END IF;
90
91 EXCEPTION
92
93 WHEN NO_DATA_FOUND THEN
94 FND_MESSAGE.Set_Name('BOM', 'CST_MGD_INFL_PER_NOT_CLOSED');
95 FND_MSG_PUB.Add;
96 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
97 THEN
98 FND_MSG_PUB.Add_Exc_Msg
99 ( G_PKG_NAME
100 , 'Check_Period_Close'
101 );
102 END IF;
103 RAISE g_period_not_closed_exc;
104 WHEN OTHERS THEN
105 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
106 THEN
107 FND_MSG_PUB.Add_Exc_Msg
108 ( G_PKG_NAME
109 , 'Check_Period_Close'
110 );
111 END IF;
112 RAISE;
113
114 END Check_Period_Close;
115
116
117 --========================================================================
118 -- PROCEDURE : Get_Previous_Acct_Period_ID PRIVATE
119 -- PARAMETERS: p_organization_id Organization ID
120 -- p_acct_period_id Account period ID
121 -- x_prev_acct_period_id Perious period account period ID
122 -- x_prev_sch_close_date Perious period schedule close date
123 -- COMMENT : This procedure retrieves previous period account period ID
124 -- and scheduled close date.
125 -- EXCEPTIONS:
126 --========================================================================
127 PROCEDURE Get_Previous_Acct_Period_ID
128 ( p_organization_id IN NUMBER
129 , p_acct_period_id IN NUMBER
130 , x_prev_acct_period_id OUT NOCOPY NUMBER
131 , x_prev_sch_close_date OUT NOCOPY DATE
132 )
133 IS
134 l_routine CONSTANT VARCHAR2(30) := 'get_previous_acct_period_id';
135 BEGIN
136
137 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
138 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
139 ,G_MODULE_HEAD || l_routine || '.begin'
140 ,l_routine || '<'
141 );
142 END IF;
143
144 SELECT
145 Schedule_Close_Date
146 , Acct_Period_ID
147 INTO
148 x_prev_sch_close_date
149 , x_prev_acct_period_id
150 FROM
151 ORG_ACCT_PERIODS
152 WHERE Organization_ID = p_organization_id
153 AND Acct_Period_ID = (SELECT
154 MAX(Acct_Period_ID)
155 FROM
156 CST_MGD_INFL_ADJ_PER_STATUSES
157 WHERE Organization_ID = p_organization_id
158 AND Status = 'FINAL')
159 AND Open_Flag = 'N'
160 AND Period_Close_Date IS NOT NULL;
161
162 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
163 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
164 ,G_MODULE_HEAD || l_routine || '.end'
165 ,l_routine || '>'
166 );
167 END IF;
168
169 EXCEPTION
170 WHEN NO_DATA_FOUND THEN
171 NULL;
172
173 WHEN OTHERS THEN
174 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
175 THEN
176 FND_MSG_PUB.Add_Exc_Msg
177 ( G_PKG_NAME
178 , 'Get_Previous_Acct_Period_ID'
179 );
180 END IF;
181 RAISE;
182
183 END Get_Previous_Acct_Period_ID;
184
185 --========================================================================
186 -- PROCEDURE : Get_Prev_Org_Acct_Period_ID PRIVATE
187 -- PARAMETERS: p_organization_id Organization ID
188 -- p_acct_period_id Account period ID
189 -- x_previous_acct_period_id
190 -- Inventory org previous period ID
191 -- COMMENT : This procedure retrieves the inventory organization's previous
192 -- period account period ID regardless if inflation adjustment
193 -- has been run for the period or not. If found, return the ID,
194 -- else return NULL. This procedure does not validate the
195 -- current period, just get the previous inventory period.
196 -- EXCEPTIONS: when no date found, do nothing.
197 --========================================================================
198 PROCEDURE Get_Prev_Org_Acct_Period_ID
199 ( p_organization_id IN NUMBER
200 , p_acct_period_id IN NUMBER
201 , x_prev_org_acct_period_id OUT NOCOPY NUMBER
202 )
203 IS
204 l_prev_org_acct_period_id NUMBER;
205 l_cur_org_acct_period_val NUMBER;
206 BEGIN
207 SELECT oap2.period_year * 10000 + oap2.period_num
208 INTO l_cur_org_acct_period_val
209 FROM ORG_ACCT_PERIODS oap2
210 WHERE oap2.organization_id = p_organization_id
211 AND oap2.acct_period_id = p_acct_period_id;
212
213 SELECT oap.acct_period_id
214 INTO l_prev_org_acct_period_id
215 FROM ORG_ACCT_PERIODS oap
216 WHERE oap.period_year * 10000 + oap.period_num =
217 (SELECT MAX(oap2.period_year * 10000 + oap2.period_num)
218 FROM ORG_ACCT_PERIODS oap2
219 WHERE oap2.organization_id = p_organization_id
220 AND (oap2.period_year * 10000 + oap2.period_num) <
221 l_cur_org_acct_period_val
222 )
223 AND oap.organization_id = p_organization_id;
224
225 x_prev_org_acct_period_id := l_prev_org_acct_period_id;
226
227 EXCEPTION
228 WHEN NO_DATA_FOUND THEN
229 NULL;
230 WHEN OTHERS THEN
231 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
232 THEN
233 FND_MSG_PUB.Add_Exc_Msg
234 ( G_PKG_NAME
235 , 'Get_Prev_Org_Acct_Period_ID'
236 );
237 END IF;
238 RAISE;
239
240 END Get_Prev_Org_Acct_Period_ID;
241
242 --========================================================================
243 -- PROCEDURE : Get_Curr_Period_Start_Date PRIVATE
244 -- PARAMETERS: p_org_id Organization ID
245 -- p_acct_period_id Account period ID
246 -- x_curr_period_start_date Current period start date
247 -- x_curr_period_end_date Current period schedule
248 -- close date
249 -- COMMENT : This procedure returns the current period start date
250 -- EXCEPTIONS:
251 --========================================================================
252 PROCEDURE Get_Curr_Period_Start_Date
253 ( p_org_id IN NUMBER
254 , p_acct_period_id IN NUMBER
255 , x_curr_period_start_date OUT NOCOPY DATE
256 , x_curr_period_end_date OUT NOCOPY DATE
257 )
258 IS
259 l_routine CONSTANT VARCHAR2(30) := 'get_curr_period_start_date';
260 BEGIN
261
262 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
263 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
264 ,G_MODULE_HEAD || l_routine || '.begin'
265 ,l_routine || '<'
266 );
267 END IF;
268
269 SELECT
270 Period_Start_Date
271 , Schedule_Close_Date
272 INTO
273 x_curr_period_start_date
274 , x_curr_period_end_date
275 FROM
276 ORG_ACCT_PERIODS
277 WHERE Acct_Period_ID = p_acct_period_id
278 AND Organization_ID = p_org_id;
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 EXCEPTION
288
289 WHEN OTHERS THEN
290 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
291 THEN
292 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
293 , 'Get_Curr_Period_Start_Date'
294 );
295 END IF;
296 RAISE;
297
298 END Get_Curr_Period_Start_Date;
299
300
301 --=======================================================================
302 -- PROCEDURE : Get_Previous_Period_Info PRIVATE
303 -- PARAMETERS: p_country_code Country code
304 -- p_organization_id Organization ID
305 -- p_inventory_item_id Inventory item ID
306 -- p_acct_period_id Account period ID
307 -- p_prev_acct_period_id Previous account period id
308 -- p_cost_group_id Cost Group Id
309 -- x_previous_qty Previous period quantity
310 -- x_previous_cost Previous period total cost
311 -- x_previous_inflation_adj Previous period inflation
312 -- adjustment
313 -- COMMENT : This procedure returns previous inflation adjustment
314 -- data
315 -- EXCEPTIONS:
316 -- made obsolete g_no_data_previous_data_exc No rows selected
317 -- part of bug#1474753 fix
318 -- removed historical flag parameter.
319 --========================================================================
320 PROCEDURE Get_Previous_Period_Info
321 ( p_country_code IN VARCHAR2
322 , p_organization_id IN NUMBER
323 , p_inventory_item_id IN NUMBER
324 , p_acct_period_id IN NUMBER
325 , p_prev_acct_period_id IN NUMBER
326 , p_cost_group_id IN CST_COST_GROUPS.cost_group_id%TYPE
327 , x_previous_qty OUT NOCOPY NUMBER
328 , x_previous_cost OUT NOCOPY NUMBER
329 , x_previous_inflation_adj OUT NOCOPY NUMBER
330 )
331 IS
332 l_routine CONSTANT VARCHAR2(30) := 'get_previous_period_info';
333 l_previous_qty NUMBER;
334 l_previous_cost NUMBER;
335 l_previous_inflation_adj NUMBER;
336 l_item_exists_infl VARCHAR2(1);
337 l_item_exists_cst VARCHAR2(1);
338 l_prev_org_acct_period_id NUMBER;
339 l_previous_unit_cost NUMBER;
340
341 BEGIN
342
343 -- initialize
344 l_previous_qty := 0;
345 l_previous_unit_cost := 0;
346 l_previous_cost := 0;
347 l_previous_inflation_adj := 0;
348
349 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
350 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
351 ,G_MODULE_HEAD || l_routine || '.begin'
352 ,l_routine || '<'
353 );
354 END IF;
355
356 SELECT
357 NVL((Actual_Inflation_Adj - ABS(Issue_Inflation_Adj)),0)
358 , NVL((Actual_Qty - ABS(Issue_Qty)),0)
359 , NVL((Actual_Cost - ABS(Issue_Cost)),0)
360 INTO
361 l_previous_inflation_adj
362 , l_previous_qty
363 , l_previous_cost
364 FROM
365 CST_MGD_INFL_ADJUSTED_COSTS
366 WHERE Country_Code = nvl(p_country_code, country_code)
367 AND Acct_Period_ID = p_prev_acct_period_id
368 AND Organization_ID = p_organization_id
369 AND Inventory_Item_ID = p_inventory_item_id;
370
371 x_previous_qty := l_previous_qty;
372 x_previous_cost := l_previous_cost;
373 x_previous_inflation_adj := l_previous_inflation_adj;
374
375 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
376 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
377 ,G_MODULE_HEAD || l_routine || '.end'
378 ,l_routine || '>'
379 );
380 END IF;
381
382 EXCEPTION
383
384 WHEN NO_DATA_FOUND THEN
385 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
386 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
387 , G_MODULE_HEAD || l_routine || '.inflprevpd'
388 , 'Inflation Adjustments for previous period not found'
389 );
390 END IF;
391
392 -- Get the previous accounting period if any and get the cost from
393 -- the CST view since no inflation adjustment has been run for
394 -- such period.
395 --
396 Get_Prev_Org_Acct_Period_ID
397 ( p_organization_id => p_organization_id
398 , p_acct_period_id => p_acct_period_id
399 , x_prev_org_acct_period_id => l_prev_org_acct_period_id
400 );
401
402 IF l_prev_org_acct_period_id IS NULL THEN
403
404 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
405 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
406 , G_MODULE_HEAD || l_routine || '.inflprevpd'
407 , 'This is the first accounting period of organization'
408 );
409 END IF;
410 --
411 x_previous_qty := 0;
412 x_previous_cost := 0;
413 x_previous_inflation_adj := 0;
414
415 ELSE
416 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
417 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
418 , G_MODULE_HEAD || l_routine || '.inflprevpd'
419 , 'A previous accounting period exist for this organization'
420 );
421 END IF;
422
423 /* Bug 4912789 the following query is replaced because CST_PER_CLOSE_DTLS_V is a Non Mergeable View
424 SELECT NVL(SUM(period_end_quantity),0)
425 , NVL(SUM(period_end_unit_cost),0)
426 INTO l_previous_qty
427 , l_previous_unit_cost
428 FROM CST_PER_CLOSE_DTLS_V
429 WHERE organization_id = p_organization_id
430 AND inventory_item_id = p_inventory_item_id
431 AND acct_period_id = l_prev_org_acct_period_id
432 AND cost_group_id = NVL(p_cost_group_id,cost_group_id); */
433
434 SELECT NVL(SUM(period_end_quantity),0)
435 , NVL(SUM(period_end_unit_cost*period_end_quantity),0)
436 INTO l_previous_qty
437 , l_previous_cost
438 FROM (
439 SELECT rollback_quantity period_end_quantity,
440 decode(rollback_quantity,0,0,rollback_value/rollback_quantity) period_end_unit_cost
441 FROM cst_period_close_summary
442 WHERE organization_id = p_organization_id
443 AND inventory_item_id = p_inventory_item_id
444 AND acct_period_id = l_prev_org_acct_period_id
445 AND cost_group_id = NVL(p_cost_group_id,cost_group_id)
446 UNION ALL
447 SELECT period_end_quantity, period_end_unit_cost
448 FROM mtl_per_close_dtls
449 WHERE organization_id = p_organization_id
450 AND inventory_item_id = p_inventory_item_id
451 AND acct_period_id = l_prev_org_acct_period_id
452 AND cost_group_id = NVL(p_cost_group_id,cost_group_id)
453 );
454
455
456 -- ================================================================
457 -- Bug#4130232 fix: Previous Cost is the Previous Period Total Cost
458 -- ================================================================
459 x_previous_qty := l_previous_qty;
460 x_previous_cost:= l_previous_cost;
461 x_previous_inflation_adj := 0;
462
463 END IF;
464
465 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
466 FND_LOG.string(FND_LOG.LEVEL_EVENT
467 , G_MODULE_HEAD || l_routine || '.inflprevinfo'
468 , 'Previous Period Quantity:' || x_previous_qty ||
469 ' Previous Period Unit Cost:' || l_previous_unit_cost ||
470 ' Previous Period Total Cost:' || x_previous_cost ||
471 ' Previous Inflation Adjustment:' || x_previous_inflation_adj
472 );
473 END IF;
474
475
476 WHEN OTHERS THEN
477 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
478 THEN
479 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
480 , 'Get_Previous_Period_Info'
481 );
482 END IF;
483 RAISE;
484
485 END Get_Previous_Period_Info;
486
487
488 --========================================================================
489 -- PROCEDURE : Get_Purchase_Qty PRIVATE
490 -- PARAMETERS: p_org_id Organization ID
491 -- p_inventory_item_id Inventory item ID
492 -- p_acct_period_id Account period ID
493 -- p_cost_group_id Cost Group ID
494 -- x_purchase_qty Purchase quantity in period
495 -- COMMENT : This procedure returns the purchase quantity incurred in
496 -- a period.
497 --========================================================================
498 PROCEDURE Get_Purchase_Qty
499 ( p_org_id IN NUMBER
500 , p_inventory_item_id IN NUMBER
501 , p_acct_period_id IN NUMBER
502 , p_cost_group_id IN CST_COST_GROUPS.cost_group_id%TYPE
503 , x_purchase_qty OUT NOCOPY NUMBER
504 )
505 IS
506 l_routine CONSTANT VARCHAR2(30) := 'get_purchase_qty';
507 BEGIN
508
509 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
510 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
511 ,G_MODULE_HEAD || l_routine || '.begin'
512 ,l_routine || '<'
513 );
514 END IF;
515
516 -- get purchase quantity for adjustment period
517 SELECT
518 NVL(SUM(Primary_Quantity), 0)
519 INTO
520 x_purchase_qty
521 FROM
522 MTL_MATERIAL_TRANSACTIONS
523 WHERE Acct_Period_ID = p_acct_period_id
524 AND Organization_ID = p_org_id
525 AND Inventory_Item_ID = p_inventory_item_id
526 AND Primary_Quantity > 0
527 AND Cost_Group_ID = p_cost_group_id
528 AND Transfer_Organization_ID IS NULL
529 AND nvl(owning_tp_type,2) <> 1
530 AND transaction_id NOT IN (SELECT transaction_id
531 FROM mtl_material_transactions
532 WHERE acct_period_id = p_acct_period_id
533 AND organization_id = p_org_id
534 AND inventory_item_id = p_inventory_item_id
535 AND transaction_source_type_id = 13
536 AND transaction_action_id = 24)
537 AND transaction_id NOT IN (SELECT transaction_id
538 FROM mtl_material_transactions
539 WHERE acct_period_id = p_acct_period_id
540 AND organization_id = p_org_id
541 AND inventory_item_id = p_inventory_item_id
542 AND transaction_source_type_id = 13
543 AND transaction_action_id = 5);
544
545
546 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
547 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
548 ,G_MODULE_HEAD || l_routine || '.end'
549 ,l_routine || '>'
550 );
551 END IF;
552
553 EXCEPTION
554
555 WHEN OTHERS THEN
556 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
557 THEN
558 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
559 , 'Get_Purchase_Qty'
560 );
561 END IF;
562 RAISE;
563
564 END Get_Purchase_Qty;
565
566
567 --========================================================================
568 -- PROCEDURE : Get_Issue_Qty PRIVATE
569 -- PARAMETERS: p_org_id Organization ID
570 -- p_inventory_item_id Inventory item ID
571 -- p_acct_period_id Account period ID
572 -- p_cost_group_id Cost Group ID
573 -- x_issue_qty Issue quantity in period
574 -- COMMENT : This procedure returns the issue quantity incurred in
575 -- a period.
576 --========================================================================
577 PROCEDURE Get_Issue_Qty
578 ( p_org_id IN NUMBER
579 , p_inventory_item_id IN NUMBER
580 , p_acct_period_id IN NUMBER
581 , p_cost_group_id IN CST_COST_GROUPS.cost_group_id%TYPE
582 , x_issue_qty OUT NOCOPY NUMBER
583 )
584 IS
585 BEGIN
586
587 -- get issue qty
588 SELECT
589 NVL(SUM(Primary_Quantity), 0)
590 INTO
591 x_issue_qty
592 FROM
593 MTL_MATERIAL_TRANSACTIONS
594 WHERE Acct_Period_ID = p_acct_period_id
595 AND Organization_ID = p_org_id
596 AND Inventory_Item_ID = p_inventory_item_id
597 AND Primary_Quantity < 0
598 AND Cost_Group_ID = p_cost_group_id
599 AND Transfer_Organization_ID IS NULL
600 AND nvl(owning_tp_type,2) <> 1
601 AND transaction_id NOT IN (SELECT transaction_id
602 FROM mtl_material_transactions
603 WHERE acct_period_id = p_acct_period_id
604 AND organization_id = p_org_id
605 AND inventory_item_id = p_inventory_item_id
606 AND transaction_source_type_id = 13
607 AND transaction_action_id = 5);
608
609 EXCEPTION
610
611 WHEN OTHERS THEN
612 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
613 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
614 , 'Get_Issue_Qty'
615 );
616 END IF;
617 RAISE;
618
619 END Get_Issue_Qty;
620
621
622 --========================================================================
623 -- PROCEDURE : Transfer_Tbl_Default PRIVATE
624 -- PARAMETERS: p_inventory_item_id Inventory item ID
625 -- p_organization_id Organization ID
626 -- p_acct_period_id Account period ID
627 -- p_country_code Country code
628 -- p_transfer_org_id Transfer organization ID
629 -- x_transfer_rec Transfer data record
630 -- COMMENT : This procedure defaults the transfer organization record
631 --========================================================================
632 PROCEDURE Transfer_Tbl_Default
633 ( p_transaction_id IN NUMBER
634 , p_inventory_item_id IN NUMBER
635 , p_organization_id IN NUMBER
636 , p_acct_period_id IN NUMBER
637 , p_country_code IN VARCHAR2
638 , p_transfer_org_id IN NUMBER
639 , x_transfer_rec OUT NOCOPY Transfer_Rec_Type
640 )
641 IS
642 l_transfer_rec Transfer_Rec_Type;
643 BEGIN
644
645 -- default transfer entries table information
646 l_transfer_rec.transaction_id := p_transaction_id;
647 l_transfer_rec.inventory_item_id := p_inventory_item_id;
648 l_transfer_rec.organization_id := p_organization_id;
649 l_transfer_rec.acct_period_id := p_acct_period_id;
650 l_transfer_rec.last_update_date := SYSDATE;
651 l_transfer_rec.last_updated_by :=
652 NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),0);
653 l_transfer_rec.creation_date := SYSDATE;
654 l_transfer_rec.created_by :=
655 NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),0);
656 l_transfer_rec.last_update_login :=
657 TO_NUMBER(FND_PROFILE.Value('LOGIN_ID'));
658 l_transfer_rec.request_id :=
659 TO_NUMBER(FND_PROFILE.Value('CONC_REQUEST_ID'));
660 l_transfer_rec.program_application_id :=
661 TO_NUMBER(FND_PROFILE.Value('PROG_APPL_ID'));
662 l_transfer_rec.program_id :=
663 TO_NUMBER(FND_PROFILE.Value('CONC_PROG_ID'));
664 l_transfer_rec.program_update_date := SYSDATE;
665 l_transfer_rec.country_code := p_country_code;
666 l_transfer_rec.transfer_organization_id := p_transfer_org_id;
667
668 x_transfer_rec := l_transfer_rec;
669
670 EXCEPTION
671 WHEN OTHERS THEN
672 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
673 THEN
674 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
675 , 'Transfer_Tbl_Default'
676 );
677 END IF;
678 RAISE;
679
680 END Transfer_Tbl_Default;
681
682 /* historical data check removed bug#1474753 fix
683 --========================================================================
684 -- PROCEDURE : Check_First_Time PRIVATE
685 -- PARAMETERS: p_country_code Country code
686 -- p_org_id Organization ID
687 -- x_get_hist_data_flag Historical data flag
688 -- COMMENT : This procedure determines if the process is running for
689 -- the first time.
690 -- EXCEPTIONS:
691 -- made obsolete g_no_hist_data_exc No historical data
692 -- as part of bug#1474753 fix
693 --========================================================================
694 PROCEDURE Check_First_Time
695 ( p_country_code IN VARCHAR2
696 , p_org_id IN NUMBER
697 , x_get_hist_data_flag OUT NOCOPY VARCHAR2
698 )
699 IS
700 l_status NUMBER;
701 l_missing_hist_data_exc EXCEPTION;
702 BEGIN
703
704 x_get_hist_data_flag := 'N';
705
706 -- check for first time
707 -- if there is data for more than
708 -- 1 period then it's not first time
709 SELECT
710 COUNT(DISTINCT(Acct_Period_ID))
711 INTO
712 l_status
713 FROM
714 CST_MGD_INFL_ADJUSTED_COSTS
715 WHERE Country_Code = p_country_code
716 AND Organization_ID = p_org_id;
717
718 -- removed as part of bug#1474753 fix
719 -- IF l_status < 1
720 -- THEN
721 -- RAISE l_missing_hist_data_exc;
722 -- ELSIF l_status = 1
723 -- THEN
724 -- x_get_hist_data_flag := 'Y';
725 -- ELSE
726 -- x_get_hist_data_flag := 'N';
727 -- END IF;
728
729
730 -- introduced as part of bug#1474753 fix
731 -- set only the flag according to status
732 IF l_status >= 1
733 THEN
734 x_get_hist_data_flag := 'Y';
735 ELSE
736 x_get_hist_data_flag := 'N';
737 END IF;
738
739
740 EXCEPTION
741
742 WHEN l_missing_hist_data_exc THEN
743 FND_MESSAGE.Set_Name('BOM', 'CST_MGD_INFL_NO_HIST_DATA');
744 FND_MSG_PUB.Add;
745 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
746 THEN
747 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
748 , 'Check_First_Time'
749 );
750 END IF;
751 RAISE g_no_hist_data_exc;
752 WHEN OTHERS THEN
753 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
754 THEN
755 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
756 , 'Check_First_Time'
757 );
758 END IF;
759 RAISE;
760
761 END Check_First_Time;
762
763 */
764
765 --========================================================================
766 -- FUNCTION : Infl_Item_Category PRIVATE
767 -- PARAMETERS: p_inventory_item_id Inventory Item ID
768 -- p_org_id Organization ID
769 -- p_category_set_id Item Category Set ID
770 -- p_category_id Item Category ID
771 -- COMMENT : This function returns 'Y' if the item requires inflation
772 -- adjustment.
773 -- EXCEPTIONS: g_no_hist_data_exc No historical data
774 --========================================================================
775 FUNCTION Infl_Item_Category
776 ( p_inventory_item_id IN NUMBER
777 , p_org_id IN NUMBER
778 , p_category_set_id IN NUMBER
779 , p_category_id IN NUMBER
780 )
781 RETURN VARCHAR2 IS
782
783 l_item_valid_flag VARCHAR2(1);
784 l_record_count NUMBER;
785
786 BEGIN
787 l_item_valid_flag := 'N';
788
789 IF (p_category_set_id IS NOT NULL) AND (p_category_id IS NULL) THEN
790 SELECT
791 COUNT(1)
792 INTO
793 l_record_count
794 FROM
795 MTL_ITEM_CATEGORIES
796 WHERE Inventory_Item_ID = p_inventory_item_id
797 AND Organization_ID = p_org_id
798 AND Category_Set_ID = p_category_set_id;
799
800 IF l_record_count = 1 THEN
801 l_item_valid_flag := 'Y';
802 END IF;
803 ELSIF (p_category_set_id IS NULL) AND (p_category_id IS NOT NULL) THEN
804 SELECT
805 COUNT(1)
806 INTO
807 l_record_count
808 FROM
809 MTL_ITEM_CATEGORIES
810 WHERE Inventory_Item_ID = p_inventory_item_id
811 AND Organization_ID = p_org_id
812 AND Category_ID = p_category_id;
813
814 IF l_record_count > 0 THEN
815 l_item_valid_flag := 'Y';
816 END IF;
817 ELSIF (p_category_set_id IS NOT NULL) AND (p_category_id IS NOT NULL) THEN
818 SELECT
819 COUNT(1)
820 INTO
821 l_record_count
822 FROM
823 MTL_ITEM_CATEGORIES
824 WHERE Inventory_Item_ID = p_inventory_item_id
825 AND Organization_ID = p_org_id
826 AND Category_Set_ID = p_category_set_id
827 AND Category_ID = p_category_id;
828
829 IF l_record_count = 1 THEN
830 l_item_valid_flag := 'Y';
831 END IF;
832 ELSIF (p_category_set_id IS NULL) AND (p_category_id IS NULL) THEN
833 l_item_valid_flag := 'Y';
834 END IF;
835
836 RETURN l_item_valid_flag;
837
838 EXCEPTION
839
840 WHEN OTHERS THEN
841 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
842 THEN
843 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
844 , 'Infl_Item_Category'
845 );
846 END IF;
847 RAISE;
848
849 END Infl_Item_Category;
850
851
852 --========================================================================
853 -- PROCEDURE : Calc_Inflation_Adj PRIVATE
854 -- PARAMETERS: p_inflation_adjustment_rec Inflation data record
855 -- p_inflation_index_value Inflation index value
856 -- p_prev_acct_period_id Previous account period id
857 -- p_cost_group_id Cost Group Id
858 -- x_inflation_adjustment_rec Inflation data record
859 -- x_tnsf_out_entry_tbl_rec Transfer out table record
860 -- x_tnsf_in_entry_tbl_rec Transfer in table record
861 -- COMMENT : This procedure calculates the inflation adjustment for a
862 -- period.
863 -- *** Begin cost of an item is it's ending cost in the previous
864 -- period. It is NOT average unit cost * total quantity.
865 -- EXCEPTIONS: g_tnsf_period_gap_exc Inflation period gap in
866 -- transfer organization
867 --========================================================================
868 PROCEDURE Calc_Inflation_Adj
869 ( p_inflation_adjustment_rec IN
870 CST_MGD_INFL_ADJUSTMENT_PUB.Inflation_Adjustment_Rec_Type
871 , p_inflation_index_value IN NUMBER
872 , p_prev_acct_period_id IN NUMBER
873 , p_cost_group_id IN CST_COST_GROUPS.cost_group_id%TYPE
874 , x_inflation_adjustment_rec OUT NOCOPY
875 CST_MGD_INFL_ADJUSTMENT_PUB.Inflation_Adjustment_Rec_Type
876 , x_tnsf_out_entry_tbl_rec OUT NOCOPY Transfer_Tbl_Rec_Type
877 , x_tnsf_in_entry_tbl_rec OUT NOCOPY Transfer_Tbl_Rec_Type
878 )
879 IS
880 l_routine CONSTANT VARCHAR2(30) := 'calc_inflation_adj';
881
882 l_inflation_adjustment_rec
883 CST_MGD_INFL_ADJUSTMENT_PUB.Inflation_Adjustment_Rec_Type;
884 l_tnsf_out_entry_tbl_rec Transfer_Tbl_Rec_Type;
885 l_tnsf_in_entry_tbl_rec Transfer_Tbl_Rec_Type;
886 l_onhand_unit_infl_cost NUMBER;
887 l_out_index NUMBER := 1;
888 l_in_index NUMBER := 1;
889 l_transfer_in_begin_qty NUMBER;
890 l_transfer_in_begin_cost NUMBER;
891 l_transfer_in_begin_infl_adj NUMBER;
892 l_transfer_in_prev_infl_adj NUMBER;
893 l_transfer_in_purchase_qty NUMBER;
894 l_transfer_in_unit_infl_cost NUMBER;
895 l_actual_unit_infl_cost NUMBER;
896 l_inv_inflation_CR NUMBER := 0;
897 l_inv_inflation_DR NUMBER := 0;
898 l_monetary_corr_CR NUMBER := 0;
899 l_sales_cost_inf_DR NUMBER := 0;
900 l_previous_infl_adj NUMBER;
901 l_transfer_hist_data_flag VARCHAR2(1);
902 l_tnsf_prev_acct_per_id NUMBER;
903 l_tnsf_prev_sch_close_date DATE;
904 l_tnsf_curr_per_start_date DATE;
905 l_tnsf_curr_per_end_date DATE;
906 l_transfer_org_code VARCHAR2(3);
907 l_err_transfer_org_id NUMBER;
908 l_transfer_in_cg_id CST_COST_GROUPS.cost_group_id%TYPE;
909
910 l_tnsf_period_gap_exc EXCEPTION;
911
912 -- ===============================================================
913 -- Bug#2949878 fix: cursor modified
914 -- transfer_cost_group_id removed
915 -- sub-query from org_acct_periods removed instead new condition
916 -- added to retrieve the corresponding transfer account period id
917 -- Bug#2912818 fix: Exclude consigned inventory transactions
918 -- nvl(mtl.owning_tp_type,2) <> 1 added
919 -- ===============================================================
920 CURSOR l_transfer_in_item_csr IS
921 SELECT
922 MTL.Transaction_ID
923 , MTL.Transfer_Organization_ID
924 , NVL(Primary_Quantity, 0) Transfer_In_Qty
925 , ORG.Acct_Period_ID TNSF_Acct_Period_ID
926 FROM
927 MTL_MATERIAL_TRANSACTIONS MTL
928 , ORG_ACCT_PERIODS ORG
929 WHERE MTL.Organization_ID = p_inflation_adjustment_rec.organization_id
930 AND MTL.Inventory_Item_ID = p_inflation_adjustment_rec.inventory_item_id
931 AND MTL.Acct_Period_ID = p_inflation_adjustment_rec.acct_period_id
932 AND MTL.Primary_Quantity > 0
933 AND MTL.Cost_Group_ID = p_cost_group_id
934 AND MTL.Transfer_Organization_ID <> MTL.Organization_ID
935 AND MTL.Transfer_Organization_ID IS NOT NULL
936 AND ORG.Organization_ID = MTL.Transfer_Organization_ID
937 AND MTL.transaction_date BETWEEN
938 TRUNC(ORG.period_start_date)
939 AND (TRUNC(ORG.schedule_close_date) + (86399/86400))
940 AND ORG.period_close_date IS NOT NULL
941 AND ORG.open_flag <> 'Y'
942 AND NVL(MTL.owning_tp_type,2) <> 1
943 ORDER BY
944 trunc(MTL.transaction_date)
945 , MTL.creation_date
946 , MTL.transaction_id;
947
948 -- bug#2949878 fix: transfer_cost_group_id removed from query
949 -- bug#2912818 fix: nvl(owning_tp_type,2) <> 1 added
950 CURSOR l_transfer_out_item_csr IS
951 SELECT
952 Transaction_ID
953 , Transfer_Organization_ID
954 , NVL(Primary_Quantity, 0) Transfer_Out_Qty
955 FROM
956 MTL_MATERIAL_TRANSACTIONS
957 WHERE Organization_ID = p_inflation_adjustment_rec.organization_id
958 AND Inventory_Item_ID = p_inflation_adjustment_rec.inventory_item_id
959 AND Acct_Period_ID = p_inflation_adjustment_rec.acct_period_id
960 AND Primary_Quantity < 0
961 AND Cost_Group_ID = p_cost_group_id
962 AND Transfer_Organization_ID <> Organization_ID
963 AND Transfer_Organization_ID IS NOT NULL
964 AND NVL(owning_tp_type,2) <> 1
965 ORDER BY
966 trunc(transaction_date)
967 , creation_date
968 , transaction_id;
969
970 -- Bug#4395397 fix: cursor to retrieve cost group of transfer in organization
971 CURSOR c_transfer_in_cg_cur(c_transfer_in_org_id NUMBER)
972 IS
973 SELECT
974 default_cost_group_id
975 FROM MTL_PARAMETERS
976 WHERE organization_id = c_transfer_in_org_id;
977
978
979 -- local debug variables to use within loop
980 l_debug_level NUMBER;
981 l_state_level NUMBER;
982
983 BEGIN
984
985 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
986 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
987 ,G_MODULE_HEAD || l_routine || '.begin'
988 ,l_routine || '<'
989 );
990 END IF;
991
992 -- Assign local debug variables to use within loop
993 l_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
994 l_state_level := FND_LOG.LEVEL_STATEMENT;
995 -- initialize
996 l_transfer_hist_data_flag := 'N';
997
998 l_inflation_adjustment_rec := p_inflation_adjustment_rec;
999
1000 /* get previous period data */
1001
1002 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1003 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1004 , G_MODULE_HEAD || l_routine || '.infladjrec'
1005 , 'Inflation Adjustment record details: '
1006 || 'Country Code:' || l_inflation_adjustment_rec.country_code || ' Organization Id:' || to_char(l_inflation_adjustment_rec.organization_id)
1007 || ' Inventory Id:' || to_char(l_inflation_adjustment_rec.inventory_item_id)
1008 || ' Accounting Period:' || to_char(l_inflation_adjustment_rec.acct_period_id));
1009 END IF;
1010
1011
1012 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1013 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1014 , G_MODULE_HEAD || l_routine || '.prevpddtl'
1015 , 'Previous Period details: ' || ' Previous Period Id:'|| p_prev_acct_period_id
1016 );
1017 END IF;
1018
1019 Get_Previous_Period_Info
1020 ( p_country_code => l_inflation_adjustment_rec.country_code
1021 , p_organization_id => l_inflation_adjustment_rec.organization_id
1022 , p_inventory_item_id => l_inflation_adjustment_rec.inventory_item_id
1023 , p_acct_period_id => l_inflation_adjustment_rec.acct_period_id
1024 , p_prev_acct_period_id => p_prev_acct_period_id
1025 , p_cost_group_id => p_cost_group_id
1026 , x_previous_qty => l_inflation_adjustment_rec.begin_qty
1027 , x_previous_cost => l_inflation_adjustment_rec.begin_cost
1028 , x_previous_inflation_adj => l_previous_infl_adj
1029 );
1030
1031 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1032 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1033 , G_MODULE_HEAD || l_routine || '.prevpdinfl'
1034 , 'Previous Period Inflation: ' || 'Begin Qty:' ||
1035 to_char(l_inflation_adjustment_rec.begin_qty) ||
1036 ' Begin Cost:' || to_char(l_inflation_adjustment_rec.begin_cost) || ' Previous Inflation Adj:' || to_char(l_previous_infl_adj)
1037 );
1038 END IF;
1039
1040 l_inflation_adjustment_rec.begin_inflation_adj :=
1041 ((l_inflation_adjustment_rec.begin_cost + l_previous_infl_adj) *
1042 p_inflation_index_value) + l_previous_infl_adj;
1043
1044 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1045 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1046 ,G_MODULE_HEAD || l_routine || '.begininfltot'
1047 ,'Beginning Total Inflation:' || to_char(l_inflation_adjustment_rec.begin_inflation_adj)
1048 );
1049 END IF;
1050
1051 -- debit begin inflation adjustment
1052 l_inv_inflation_DR := l_inflation_adjustment_rec.begin_inflation_adj -
1053 l_previous_infl_adj;
1054
1055 -- credit monetary correction with begin inflation adjustment
1056 l_monetary_corr_CR := l_inflation_adjustment_rec.begin_inflation_adj -
1057 l_previous_infl_adj;
1058
1059 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1060 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1061 , G_MODULE_HEAD || l_routine || '.dbcrinfl'
1062 , 'Debit begin inflation:' || to_char(l_inv_inflation_DR) ||
1063 ' Credit begin inflation:' || to_char(l_monetary_corr_CR)
1064 );
1065 END IF;
1066
1067 /* Calc. Beginning Unit Inflation Cost */
1068
1069 -- get purchase quantity for adjustment period
1070 Get_Purchase_Qty
1071 ( p_org_id => l_inflation_adjustment_rec.organization_id
1072 , p_inventory_item_id => l_inflation_adjustment_rec.inventory_item_id
1073 , p_acct_period_id => l_inflation_adjustment_rec.acct_period_id
1074 , p_cost_group_id => p_cost_group_id
1075 , x_purchase_qty => l_inflation_adjustment_rec.purchase_qty
1076 );
1077
1078 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1079 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1080 , G_MODULE_HEAD || l_routine || '.purchqty'
1081 , 'Purchase Qty:' || to_char(l_inflation_adjustment_rec.purchase_qty)
1082 );
1083 END IF;
1084
1085 l_inflation_adjustment_rec.purchase_cost :=
1086 l_inflation_adjustment_rec.purchase_qty *
1087 l_inflation_adjustment_rec.item_unit_cost;
1088
1089 IF (l_inflation_adjustment_rec.begin_qty +
1090 l_inflation_adjustment_rec.purchase_qty) = 0
1091 THEN
1092 l_onhand_unit_infl_cost := 0;
1093 ELSE
1094 l_onhand_unit_infl_cost :=
1095 l_inflation_adjustment_rec.begin_inflation_adj/
1096 (l_inflation_adjustment_rec.begin_qty +
1097 l_inflation_adjustment_rec.purchase_qty);
1098 END IF;
1099
1100 /* adjust for transfers */
1101
1102 -- initiate actual balances
1103 l_inflation_adjustment_rec.actual_qty :=
1104 l_inflation_adjustment_rec.begin_qty +
1105 l_inflation_adjustment_rec.purchase_qty;
1106
1107 l_inflation_adjustment_rec.actual_inflation_adj :=
1108 l_inflation_adjustment_rec.begin_inflation_adj;
1109
1110 -- get transfer out information
1111 FOR l_transfer_out_info IN l_transfer_out_item_csr
1112 LOOP
1113
1114 IF (l_state_level >= l_debug_level) THEN
1115 FND_LOG.string(l_state_level
1116 , G_MODULE_HEAD || l_routine || '.trnsoutinfo'
1117 , 'Transfer out information: ' || 'Transaction Id:' ||
1118 to_char(l_transfer_out_info.Transaction_ID) ||
1119 ' Transfer Org Id:' || to_char(l_transfer_out_info.Transfer_Organization_ID)
1120 );
1121 END IF;
1122
1123
1124 Transfer_Tbl_Default
1125 ( p_transaction_id => l_transfer_out_info.Transaction_ID
1126 , p_inventory_item_id => l_inflation_adjustment_rec.inventory_item_id
1127 , p_organization_id => l_inflation_adjustment_rec.organization_id
1128 , p_acct_period_id => l_inflation_adjustment_rec.acct_period_id
1129 , p_country_code => l_inflation_adjustment_rec.country_code
1130 , p_transfer_org_id => l_transfer_out_info.Transfer_Organization_ID
1131 , x_transfer_rec => l_tnsf_out_entry_tbl_rec(l_out_index)
1132 );
1133
1134 l_tnsf_out_entry_tbl_rec(l_out_index).entered_dr :=
1135 ABS(l_transfer_out_info.Transfer_Out_Qty * l_onhand_unit_infl_cost);
1136 l_tnsf_out_entry_tbl_rec(l_out_index).entered_cr := 0;
1137
1138 -- credit adjusting org. with what's debitted for opposing org.
1139 l_inv_inflation_CR := l_inv_inflation_CR +
1140 l_tnsf_out_entry_tbl_rec(l_out_index).entered_dr;
1141
1142 IF (l_state_level >= l_debug_level) THEN
1143 FND_LOG.string(l_state_level
1144 , G_MODULE_HEAD || l_routine || '.trnsoutcrinfo'
1145 , 'Transfer Out Qty:' || to_char(l_transfer_out_info.Transfer_Out_Qty) || ' Inflation credit:' || to_char(l_inv_inflation_CR)
1146 );
1147 END IF;
1148
1149 -- Transfer_Out_Qty is negative for transfer out
1150 l_inflation_adjustment_rec.actual_qty :=
1151 l_inflation_adjustment_rec.actual_qty +
1152 l_transfer_out_info.Transfer_Out_Qty;
1153
1154 IF (l_state_level >= l_debug_level) THEN
1155 FND_LOG.string(l_state_level
1156 , G_MODULE_HEAD || l_routine || '.actualqty'
1157 , 'Actual Qty:' || to_char(l_inflation_adjustment_rec.actual_qty)
1158 );
1159 END IF;
1160
1161 l_inflation_adjustment_rec.actual_inflation_adj :=
1162 l_inflation_adjustment_rec.actual_inflation_adj -
1163 l_tnsf_out_entry_tbl_rec(l_out_index).entered_dr;
1164
1165
1166 IF (l_state_level >= l_debug_level) THEN
1167 FND_LOG.string(l_state_level
1168 , G_MODULE_HEAD || l_routine || '.actualinfl'
1169 , 'Actual inflation adjustment:' ||
1170 to_char(l_inflation_adjustment_rec.actual_inflation_adj)
1171 );
1172 END IF;
1173
1174 l_out_index := l_out_index + 1;
1175 END LOOP;
1176
1177 -- get transfer in information
1178 FOR l_transfer_in_info IN l_transfer_in_item_csr
1179 LOOP
1180
1181 IF (l_state_level >= l_debug_level) THEN
1182 FND_LOG.string(l_state_level
1183 , G_MODULE_HEAD || l_routine || '.trnsininfo'
1184 , 'Transfer in information: ' || 'Transaction Id:' ||
1185 to_char(l_transfer_in_info.Transaction_ID) ||
1186 ' Transfer Org Id:' || to_char(l_transfer_in_info.Transfer_Organization_ID)
1187 );
1188 END IF;
1189
1190 OPEN c_transfer_in_cg_cur(l_transfer_in_info.transfer_organization_id);
1191 FETCH c_transfer_in_cg_cur
1192 INTO l_transfer_in_cg_id;
1193 CLOSE c_transfer_in_cg_cur;
1194
1195 IF (l_state_level >= l_debug_level) THEN
1196 FND_LOG.string(l_state_level
1197 ,G_MODULE_HEAD || l_routine || '.transcgid'
1198 ,'Transfer in cost group id: ' || l_transfer_in_cg_id
1199 );
1200 END IF;
1201
1202
1203 Transfer_Tbl_Default
1204 ( p_transaction_id => l_transfer_in_info.Transaction_ID
1205 , p_inventory_item_id => l_inflation_adjustment_rec.inventory_item_id
1206 , p_organization_id => l_inflation_adjustment_rec.organization_id
1207 , p_acct_period_id => l_inflation_adjustment_rec.acct_period_id
1208 , p_country_code => l_inflation_adjustment_rec.country_code
1209 , p_transfer_org_id => l_transfer_in_info.Transfer_Organization_ID
1210 , x_transfer_rec => l_tnsf_in_entry_tbl_rec(l_in_index)
1211 );
1212
1213 /* get transfer-in unit inflation cost */
1214
1215 /* historical data check removed as part of bug#1474753 fix
1216 Check_First_Time
1217 ( p_country_code => l_inflation_adjustment_rec.country_code
1218 , p_org_id => l_transfer_in_info.Transfer_Organization_ID
1219 , x_get_hist_data_flag => l_transfer_hist_data_flag
1220 );
1221 */
1222
1223 -- Get previous account period id and scheduled close date
1224 -- for transfer in organization.
1225 Get_Previous_Acct_Period_ID
1226 ( p_organization_id => l_transfer_in_info.Transfer_Organization_ID
1227 , p_acct_period_id => l_transfer_in_info.TNSF_Acct_Period_ID
1228 , x_prev_acct_period_id => l_tnsf_prev_acct_per_id
1229 , x_prev_sch_close_date => l_tnsf_prev_sch_close_date
1230 );
1231
1232 -- set to mid night 23:59:59
1233 l_tnsf_prev_sch_close_date :=
1234 TRUNC(l_tnsf_prev_sch_close_date) + (86399/86400);
1235
1236 IF (l_state_level >= l_debug_level) THEN
1237 FND_LOG.string(l_state_level
1238 ,G_MODULE_HEAD || l_routine || '.tsfact'
1239 ,'Tnsf Prev. Acct Period Id:' || l_tnsf_prev_acct_per_id || ' ' || l_tnsf_prev_sch_close_date
1240 );
1241 END IF;
1242
1243 -- Get current period start date.
1244 Get_Curr_Period_Start_Date
1245 ( p_org_id => l_transfer_in_info.Transfer_Organization_ID
1246 , p_acct_period_id => l_transfer_in_info.TNSF_Acct_Period_ID
1247 , x_curr_period_start_date => l_tnsf_curr_per_start_date
1248 , x_curr_period_end_date => l_tnsf_curr_per_end_date
1249 );
1250
1251 l_tnsf_curr_per_start_date := TRUNC(l_tnsf_curr_per_start_date);
1252 l_tnsf_curr_per_end_date := TRUNC(l_tnsf_curr_per_end_date) + (86399/86400);
1253
1254 IF (l_state_level >= l_debug_level) THEN
1255 FND_LOG.string(l_state_level
1256 ,G_MODULE_HEAD || l_routine || '.tnsdte'
1257 ,'Transfer Current Period Start Date:' || TO_CHAR(l_tnsf_curr_per_start_date, 'DD-MON-YYYY HH24:MI:SS') || ' ' || TO_CHAR(l_tnsf_curr_per_end_date, 'DD-MON-YYYY HH24:MI:SS')
1258 );
1259 END IF;
1260
1261 -- Check if the previous period exists
1262 IF (l_tnsf_prev_sch_close_date IS NOT NULL) THEN
1263 -- Check inflation adjustment period gap
1264 IF l_tnsf_curr_per_start_date > l_tnsf_prev_sch_close_date + 1
1265 THEN
1266 l_err_transfer_org_id := l_transfer_in_info.Transfer_Organization_ID;
1267 RAISE l_tnsf_period_gap_exc;
1268 END IF;
1269 END IF;
1270
1271 IF (l_state_level >= l_debug_level) THEN
1272 FND_LOG.string(l_state_level
1273 , G_MODULE_HEAD || l_routine || '.trnsinpdstart'
1274 , 'Transfer in current period start date:' || to_char(l_tnsf_curr_per_start_date, 'DD-MON-YYYY HH24:MI:SS') || ' Transfer in previous schedule close date:' || to_char(l_tnsf_prev_sch_close_date, 'DD-MON-YYYY HH24:MI:SS')
1275 );
1276 END IF;
1277
1278 -- FP:11i9-11i12:Bug#4420392: Transfer in Cost Group id used
1279 Get_Previous_Period_Info
1280 ( p_country_code => l_inflation_adjustment_rec.country_code
1281 , p_organization_id =>
1282 l_transfer_in_info.Transfer_Organization_ID
1283 , p_inventory_item_id =>
1284 l_inflation_adjustment_rec.inventory_item_id
1285 , p_acct_period_id =>
1286 l_transfer_in_info.TNSF_Acct_Period_ID
1287 , p_prev_acct_period_id => l_tnsf_prev_acct_per_id
1288 , p_cost_group_id => l_transfer_in_cg_id
1289 , x_previous_qty => l_transfer_in_begin_qty
1290 , x_previous_cost => l_transfer_in_begin_cost
1291 , x_previous_inflation_adj => l_transfer_in_prev_infl_adj
1292 );
1293
1294 IF (l_state_level >= l_debug_level) THEN
1295 FND_LOG.string(l_state_level
1296 , G_MODULE_HEAD || l_routine || '.trnsinprevinfl'
1297 , 'Transfer in previous period inflation: ' ||
1298 'Begin qty:' || to_char(l_transfer_in_begin_qty) ||
1299 ' Begin Cost:' || to_char(l_transfer_in_begin_cost)
1300 || 'Previous inflation:' || to_char(l_transfer_in_prev_infl_adj)
1301 );
1302 END IF;
1303
1304 l_transfer_in_begin_infl_adj :=
1305 ((l_transfer_in_begin_cost + l_transfer_in_prev_infl_adj) *
1306 p_inflation_index_value) + l_transfer_in_prev_infl_adj;
1307
1308 -- FP:11i9-11i12:Bug#4420392: Transfer in Cost Group id used
1309 Get_Purchase_Qty
1310 ( p_org_id => l_transfer_in_info.Transfer_Organization_ID
1311 , p_inventory_item_id => l_inflation_adjustment_rec.inventory_item_id
1312 , p_acct_period_id => l_transfer_in_info.TNSF_Acct_Period_ID
1313 , p_cost_group_id => l_transfer_in_cg_id
1314 , x_purchase_qty => l_transfer_in_purchase_qty
1315 );
1316
1317 IF (l_state_level >= l_debug_level) THEN
1318 FND_LOG.string(l_state_level
1319 , G_MODULE_HEAD || l_routine || '.trnsinpurchqty'
1320 , 'Transfer in purchase qty: ' || l_transfer_in_purchase_qty
1321 );
1322 END IF;
1323
1324 IF (l_transfer_in_begin_qty + l_transfer_in_purchase_qty) = 0
1325 THEN
1326 l_transfer_in_unit_infl_cost := 0;
1327 ELSE
1328 l_transfer_in_unit_infl_cost := l_transfer_in_begin_infl_adj/
1329 (l_transfer_in_begin_qty +
1330 l_transfer_in_purchase_qty);
1331 END IF;
1332
1333 IF (l_state_level >= l_debug_level) THEN
1334 FND_LOG.string(l_state_level
1335 , G_MODULE_HEAD || l_routine || '.trnsinunitinfl'
1336 , 'Transfer in unit inflation cost:' || to_char(l_transfer_in_unit_infl_cost)
1337 );
1338 END IF;
1339
1340 -- account entry for opposing organization
1341 l_tnsf_in_entry_tbl_rec(l_in_index).entered_cr :=
1342 l_transfer_in_info.Transfer_In_Qty * l_transfer_in_unit_infl_cost;
1343 l_tnsf_in_entry_tbl_rec(l_in_index).entered_dr := 0;
1344
1345 -- debit adjusting org. with what's credited to the opposing org.
1346 l_inv_inflation_DR := l_inv_inflation_DR +
1347 l_tnsf_in_entry_tbl_rec(l_in_index).entered_cr;
1348
1349 -- Update rolling balances
1350 l_inflation_adjustment_rec.actual_qty :=
1351 l_inflation_adjustment_rec.actual_qty +
1352 l_transfer_in_info.Transfer_In_Qty;
1353
1354 l_inflation_adjustment_rec.actual_inflation_adj :=
1355 l_inflation_adjustment_rec.actual_inflation_adj +
1356 l_tnsf_in_entry_tbl_rec(l_in_index).entered_cr;
1357
1358
1359 IF (l_state_level >= l_debug_level) THEN
1360 FND_LOG.string(l_state_level
1361 , G_MODULE_HEAD || l_routine || '.trnsininflcr'
1362 , 'Transfer in entered cr:' || to_char(l_tnsf_in_entry_tbl_rec(l_in_index).entered_cr) || 'Inflation Dr:' || to_char(l_inv_inflation_DR)
1363 );
1364 END IF;
1365
1366 l_in_index := l_in_index + 1;
1367 END LOOP;
1368
1369 l_inflation_adjustment_rec.actual_cost :=
1370 l_inflation_adjustment_rec.actual_qty *
1371 l_inflation_adjustment_rec.item_unit_cost;
1372
1373 IF (l_state_level >= l_debug_level) THEN
1374 FND_LOG.string(l_state_level
1375 , G_MODULE_HEAD || l_routine || '.actualcost'
1376 , 'Actual Cost:' || to_char(l_inflation_adjustment_rec.actual_cost)
1377 );
1378 END IF;
1379
1380 -- update unit inflation cost
1381 IF l_inflation_adjustment_rec.actual_qty = 0
1382 THEN
1383 l_actual_unit_infl_cost := 0;
1384 ELSE
1385 l_actual_unit_infl_cost :=
1386 l_inflation_adjustment_rec.actual_inflation_adj/
1387 l_inflation_adjustment_rec.actual_qty;
1388 END IF;
1389
1390 IF (l_state_level >= l_debug_level) THEN
1391 FND_LOG.string(l_state_level
1392 , G_MODULE_HEAD || l_routine || '.actualqty'
1393 , 'Actual Qty:' || l_inflation_adjustment_rec.actual_qty
1394 );
1395
1396 FND_LOG.string(l_state_level
1397 , G_MODULE_HEAD || l_routine || '.actualunitinfl'
1398 , 'Actual Unit Inflation Cost:' || to_char(l_actual_unit_infl_cost)
1399 );
1400
1401 END IF;
1402
1403 /* Adjust for issues */
1404
1405 -- get issue qty and cost
1406 Get_Issue_Qty
1407 ( p_org_id => l_inflation_adjustment_rec.organization_id
1408 , p_inventory_item_id => l_inflation_adjustment_rec.inventory_item_id
1409 , p_acct_period_id => l_inflation_adjustment_rec.acct_period_id
1410 , p_cost_group_id => p_cost_group_id
1411 , x_issue_qty => l_inflation_adjustment_rec.issue_qty
1412 );
1413
1414 IF (l_state_level >= l_debug_level) THEN
1415 FND_LOG.string(l_state_level
1416 , G_MODULE_HEAD || l_routine || '.issueqty'
1417 , 'Issue Quantity:' || to_char(l_inflation_adjustment_rec.issue_qty)
1418 );
1419 END IF;
1420
1421 l_inflation_adjustment_rec.Issue_Inflation_Adj :=
1422 l_inflation_adjustment_rec.issue_qty * l_actual_unit_infl_cost;
1423
1424 l_inflation_adjustment_rec.issue_cost :=
1425 l_inflation_adjustment_rec.issue_qty *
1426 l_inflation_adjustment_rec.item_unit_cost;
1427
1428 -- issue accounting entry
1429 l_sales_cost_inf_DR := l_inflation_adjustment_rec.issue_inflation_adj;
1430
1431 -- =======================================================================
1432 -- Bug#4552111 fix: formual changed so that l_inv_inflation_CR is negative
1433 -- =======================================================================
1434 l_inv_inflation_CR :=
1435 l_inflation_adjustment_rec.Issue_Inflation_Adj - l_inv_inflation_CR;
1436
1437 l_inflation_adjustment_rec.inventory_adj_acct_cr := l_inv_inflation_CR;
1438 l_inflation_adjustment_rec.inventory_adj_acct_dr := l_inv_inflation_DR;
1439 l_inflation_adjustment_rec.monetary_corr_acct_cr := l_monetary_corr_CR;
1440 l_inflation_adjustment_rec.sales_cost_acct_dr := l_sales_cost_inf_DR;
1441 l_inflation_adjustment_rec.historical_flag := 'N';
1442
1443
1444 IF (l_state_level >= l_debug_level) THEN
1445 FND_LOG.string(l_state_level
1446 , G_MODULE_HEAD || l_routine || '.infladjrec1'
1447 , 'Inflation Adjustment record information: '
1448 || 'Begin:' || to_char(l_inflation_adjustment_rec.begin_inflation_adj) || ' Actual:' || to_char(l_inflation_adjustment_rec.actual_inflation_adj)
1449 );
1450
1451 FND_LOG.string(l_state_level
1452 , G_MODULE_HEAD || l_routine || '.infladjrec2'
1453 , ' Issue:' || to_char(l_inflation_adjustment_rec.issue_inflation_adj) || ' Credit:' || to_char(l_inflation_adjustment_rec.inventory_adj_acct_cr) || ' Debit:' || to_char(l_inflation_adjustment_rec.inventory_adj_acct_dr)
1454 );
1455
1456 FND_LOG.string(l_state_level
1457 , G_MODULE_HEAD || l_routine || '.infladjrec3'
1458 , ' Monetary Credit:' || to_char(l_inflation_adjustment_rec.monetary_corr_acct_cr) || ' Sales cost Debit' || to_char(l_inflation_adjustment_rec.sales_cost_acct_dr)
1459 );
1460 END IF;
1461
1462 -- return parameters
1463 x_inflation_adjustment_rec := l_inflation_adjustment_rec;
1464 x_tnsf_out_entry_tbl_rec := l_tnsf_out_entry_tbl_rec;
1465 x_tnsf_in_entry_tbl_rec := l_tnsf_in_entry_tbl_rec;
1466
1467 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1468 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1469 ,G_MODULE_HEAD || l_routine || '.end'
1470 ,l_routine || '>'
1471 );
1472 END IF;
1473
1474 EXCEPTION
1475
1476 WHEN l_tnsf_period_gap_exc THEN
1477 SELECT
1478 Organization_Code
1479 INTO
1480 l_transfer_org_code
1481 FROM
1482 MTL_PARAMETERS
1483 WHERE
1484 Organization_ID = l_err_transfer_org_id;
1485
1486 FND_MESSAGE.Set_Name('BOM', 'CST_MGD_INFL_PER_GAP_TNSF');
1487 FND_MESSAGE.Set_Token('ORG', l_transfer_org_code);
1488 FND_MSG_PUB.Add;
1489 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1490 THEN
1491 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1492 , 'Calc_Inflation_Adj'
1493 );
1494 END IF;
1495 RAISE g_tnsf_period_gap_exc;
1496 WHEN OTHERS THEN
1497 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1498 THEN
1499 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1500 , 'Calc_Inflation_Adj'
1501 );
1502 END IF;
1503 RAISE;
1504
1505 END Calc_Inflation_Adj;
1506
1507
1508 --========================================================================
1509 -- PROCEDURE : Insert_Inflation_Adj PRIVATE
1510 -- PARAMETERS: p_inflation_adjustment_rec Inflation data record
1511 -- COMMENT : This procedure inserts inflation adjustment data.
1512 --========================================================================
1513 PROCEDURE Insert_Inflation_Adj
1514 ( p_inflation_adjustment_rec IN
1515 CST_MGD_INFL_ADJUSTMENT_PUB.Inflation_Adjustment_Rec_Type
1516 )
1517 IS
1518 BEGIN
1519
1520 INSERT INTO
1521 CST_MGD_INFL_ADJUSTED_COSTS(
1522 Inventory_Item_ID
1523 , Organization_ID
1524 , Acct_Period_ID
1525 , Last_Update_Date
1526 , Last_Updated_By
1527 , Creation_Date
1528 , Created_By
1529 , Last_Update_Login
1530 , Request_ID
1531 , Program_Application_ID
1532 , Program_ID
1533 , Program_Update_Date
1534 , Country_Code
1535 , Begin_Qty
1536 , Begin_Cost
1537 , Begin_Inflation_Adj
1538 , Purchase_Qty
1539 , Purchase_Cost
1540 , Actual_Qty
1541 , Actual_Cost
1542 , Actual_Inflation_Adj
1543 , Issue_Qty
1544 , Issue_Cost
1545 , Issue_Inflation_Adj
1546 , Inventory_Adj_Acct_CR
1547 , Inventory_Adj_Acct_DR
1548 , Monetary_Corr_Acct_CR
1549 , Sales_Cost_Acct_DR
1550 , Historical_Flag
1551 )
1552 VALUES(
1553 p_inflation_adjustment_rec.inventory_item_id
1554 , p_inflation_adjustment_rec.organization_id
1555 , p_inflation_adjustment_rec.acct_period_id
1556 , p_inflation_adjustment_rec.last_update_date
1557 , p_inflation_adjustment_rec.last_updated_by
1558 , p_inflation_adjustment_rec.creation_date
1559 , p_inflation_adjustment_rec.created_by
1560 , p_inflation_adjustment_rec.last_update_login
1561 , p_inflation_adjustment_rec.request_id
1562 , p_inflation_adjustment_rec.program_application_id
1563 , p_inflation_adjustment_rec.program_id
1564 , p_inflation_adjustment_rec.program_update_date
1565 , p_inflation_adjustment_rec.country_code
1566 , p_inflation_adjustment_rec.begin_qty
1567 , p_inflation_adjustment_rec.begin_cost
1568 , p_inflation_adjustment_rec.begin_inflation_adj
1569 , p_inflation_adjustment_rec.purchase_qty
1570 , p_inflation_adjustment_rec.purchase_cost
1571 , p_inflation_adjustment_rec.actual_qty
1572 , p_inflation_adjustment_rec.actual_cost
1573 , p_inflation_adjustment_rec.actual_inflation_adj
1574 , p_inflation_adjustment_rec.issue_qty
1575 , p_inflation_adjustment_rec.issue_cost
1576 , p_inflation_adjustment_rec.issue_inflation_adj
1577 , p_inflation_adjustment_rec.inventory_adj_acct_cr
1578 , p_inflation_adjustment_rec.inventory_adj_acct_dr
1579 , p_inflation_adjustment_rec.monetary_corr_acct_cr
1580 , p_inflation_adjustment_rec.sales_cost_acct_dr
1581 , p_inflation_adjustment_rec.historical_flag
1582 );
1583
1584 EXCEPTION
1585
1586 WHEN OTHERS THEN
1587 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1588 THEN
1589 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1590 , 'Insert_Inflation_Adj'
1591 );
1592 END IF;
1593 RAISE;
1594
1595 END Insert_Inflation_Adj;
1596
1597
1598 --========================================================================
1599 -- PROCEDURE : Insert_Transfer_Entries PRIVATE
1600 -- PARAMETERS: p_tnsf_acct_entry_rec Transfer organization account
1601 -- record
1602 -- COMMENT : This procedure inserts transfer organization account
1603 -- entries
1604 --========================================================================
1605 PROCEDURE Insert_Transfer_Entries
1606 ( p_tnsf_acct_entry_rec IN Transfer_Rec_Type
1607 )
1608 IS
1609 BEGIN
1610
1611 INSERT INTO
1612 CST_MGD_INFL_TSF_ORG_ENTRIES(
1613 Transaction_ID
1614 , Inventory_Item_ID
1615 , Organization_ID
1616 , Acct_Period_ID
1617 , Last_Update_Date
1618 , Last_Updated_By
1619 , Creation_Date
1620 , Created_By
1621 , Last_Update_Login
1622 , Request_ID
1623 , Program_Application_ID
1624 , Program_ID
1625 , Program_Update_Date
1626 , Country_Code
1627 , Transfer_Organization_ID
1628 , Entered_DR
1629 , Entered_CR
1630 )
1631 VALUES(
1632 p_tnsf_acct_entry_rec.transaction_id
1633 , p_tnsf_acct_entry_rec.inventory_item_id
1634 , p_tnsf_acct_entry_rec.organization_id
1635 , p_tnsf_acct_entry_rec.acct_period_id
1636 , p_tnsf_acct_entry_rec.last_update_date
1637 , p_tnsf_acct_entry_rec.last_updated_by
1638 , p_tnsf_acct_entry_rec.creation_date
1639 , p_tnsf_acct_entry_rec.created_by
1640 , p_tnsf_acct_entry_rec.last_update_login
1641 , p_tnsf_acct_entry_rec.request_id
1642 , p_tnsf_acct_entry_rec.program_application_id
1643 , p_tnsf_acct_entry_rec.program_id
1644 , p_tnsf_acct_entry_rec.program_update_date
1645 , p_tnsf_acct_entry_rec.country_code
1646 , p_tnsf_acct_entry_rec.transfer_organization_id
1647 , p_tnsf_acct_entry_rec.entered_dr
1648 , p_tnsf_acct_entry_rec.entered_cr
1649 );
1650
1651 EXCEPTION
1652
1653 WHEN OTHERS THEN
1654 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1655 THEN
1656 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1657 , 'Insert_Transfer_Entries'
1658 );
1659 END IF;
1660 RAISE;
1661
1662 END Insert_Transfer_Entries;
1663
1664
1665 --========================================================================
1666 -- PROCEDURE : Create_Inflation_Adjusted_Cost PRIVATE
1667 -- PARAMETERS: p_api_version_number known api versionerror buffer
1668 -- p_init_msg_list to reset list
1669 -- x_return_status return status
1670 -- x_msg_count number of messages in the list
1671 -- x_msg_data text of messages
1672 -- p_inflation_index_value Inflation index value
1673 -- p_prev_acct_period_id Previous account period id
1674 -- p_inflation_adjustment_rec Inflation data record
1675 -- p_cost_group_id Cost Group Id
1676 -- VERSION : current version 1.0
1677 -- initial version 1.0
1678 -- COMMENT : This API is called by concurrent program.
1679 --=======================================================================
1680 PROCEDURE Create_Inflation_Adjusted_Cost
1681 ( p_api_version_number IN NUMBER
1682 , p_init_msg_list IN VARCHAR2
1683 , x_return_status OUT NOCOPY VARCHAR2
1684 , x_msg_count OUT NOCOPY NUMBER
1685 , x_msg_data OUT NOCOPY VARCHAR2
1686 , p_inflation_index_value IN NUMBER
1687 , p_prev_acct_period_id IN NUMBER
1688 , p_inflation_adjustment_rec IN
1689 CST_MGD_INFL_ADJUSTMENT_PUB.Inflation_Adjustment_Rec_Type
1690 , p_cost_group_id IN CST_COST_GROUPS.cost_group_id%TYPE
1691 )
1692 IS
1693 l_routine CONSTANT VARCHAR2(30) := 'create_inflation_adjusted_cost';
1694
1695 l_return_status VARCHAR2(1);
1696 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1697 L_API_NAME CONSTANT VARCHAR2(30)
1698 := 'Create_Inflation_Adjusted_Cost';
1699 l_inflation_adjustment_rec
1700 CST_MGD_INFL_ADJUSTMENT_PUB.Inflation_Adjustment_Rec_Type;
1701 l_infl_adjustment_out_rec
1702 CST_MGD_INFL_ADJUSTMENT_PUB.Inflation_Adjustment_Rec_Type;
1703 l_tnsf_out_entry_tbl_rec Transfer_Tbl_Rec_Type;
1704 l_tnsf_in_entry_tbl_rec Transfer_Tbl_Rec_Type;
1705 l_index BINARY_INTEGER;
1706 BEGIN
1707
1708 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1709 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1710 ,G_MODULE_HEAD || l_routine || '.begin'
1711 ,l_routine || '<'
1712 );
1713 END IF;
1714
1715 x_return_status := FND_API.G_RET_STS_SUCCESS;
1716
1717 -- Standard call to check for call compatibility
1718 IF NOT FND_API.Compatible_API_Call
1719 ( L_API_VERSION_NUMBER
1720 , p_api_version_number
1721 , L_API_NAME
1722 , G_PKG_NAME
1723 )
1724 THEN
1725 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1726 END IF;
1727
1728 -- Initialize message stack if required
1729 IF FND_API.to_Boolean(p_init_msg_list)
1730 THEN
1731 FND_MSG_PUB.initialize;
1732 END IF;
1733
1734 l_inflation_adjustment_rec := p_inflation_adjustment_rec;
1735
1736 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1737 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1738 ,G_MODULE_HEAD || l_routine || '.begin'
1739 ,l_routine || '<'
1740 );
1741 END IF;
1742
1743 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1744 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1745 ,G_MODULE_HEAD || l_routine || '.infladjorgacct'
1746 ,'Inflation Adjustment Rec: Organization Id:' ||
1747 l_inflation_adjustment_rec.organization_id ||
1748 ' Acct Period Id:' || l_inflation_adjustment_rec.acct_period_id
1749 );
1750 END IF;
1751
1752 -- Calculate inflation adjustment
1753 Calc_Inflation_Adj
1754 ( p_inflation_adjustment_rec => l_inflation_adjustment_rec
1755 , p_inflation_index_value => p_inflation_index_value
1756 , p_prev_acct_period_id => p_prev_acct_period_id
1757 , p_cost_group_id => p_cost_group_id
1758 , x_inflation_adjustment_rec => l_infl_adjustment_out_rec
1759 , x_tnsf_out_entry_tbl_rec => l_tnsf_out_entry_tbl_rec
1760 , x_tnsf_in_entry_tbl_rec => l_tnsf_in_entry_tbl_rec
1761 );
1762
1763 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1764 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1765 ,G_MODULE_HEAD || l_routine || '.aftercalcinfl'
1766 , 'After Calc_Inflation_Adj'
1767 );
1768 END IF;
1769
1770 Insert_Inflation_Adj
1771 ( p_inflation_adjustment_rec => l_infl_adjustment_out_rec
1772 );
1773
1774 -- store transfer out organization account entries
1775 l_index := NVL(l_tnsf_out_entry_tbl_rec.FIRST, 0);
1776 IF l_index > 0
1777 THEN
1778 LOOP
1779 IF NVL(l_tnsf_out_entry_tbl_rec(l_index).entered_cr, 0) +
1780 NVL(l_tnsf_out_entry_tbl_rec(l_index).entered_dr, 0)
1781 <> 0
1782 THEN
1783 Insert_Transfer_Entries
1784 ( p_tnsf_acct_entry_rec => l_tnsf_out_entry_tbl_rec(l_index)
1785 );
1786 END IF;
1787 EXIT WHEN l_index = l_tnsf_out_entry_tbl_rec.LAST;
1788 l_index := l_tnsf_out_entry_tbl_rec.NEXT(l_index);
1789 END LOOP;
1790 END IF;
1791
1792 -- store transfer in organization account entries
1793 l_index := NVL(l_tnsf_in_entry_tbl_rec.FIRST, 0);
1794 IF l_index > 0
1795 THEN
1796 LOOP
1797 IF NVL(l_tnsf_in_entry_tbl_rec(l_index).entered_cr, 0) +
1798 NVL(l_tnsf_in_entry_tbl_rec(l_index).entered_dr, 0)
1799 <> 0
1800 THEN
1801 Insert_Transfer_Entries
1802 ( p_tnsf_acct_entry_rec => l_tnsf_in_entry_tbl_rec(l_index)
1803 );
1804 END IF;
1805 EXIT WHEN l_index = l_tnsf_in_entry_tbl_rec.LAST;
1806 l_index := l_tnsf_in_entry_tbl_rec.NEXT(l_index);
1807 END LOOP;
1808 END IF;
1809
1810 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1811 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1812 ,G_MODULE_HEAD || l_routine || '.end'
1813 ,l_routine || '>'
1814 );
1815 END IF;
1816
1817 EXCEPTION
1818
1819 WHEN FND_API.G_EXC_ERROR THEN
1820 x_return_status := FND_API.G_RET_STS_ERROR;
1821 -- Get message count and data
1822 FND_MSG_PUB.Count_And_Get
1823 ( p_count => x_msg_count
1824 , p_data => x_msg_data
1825 );
1826 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1827 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1828 -- Get message count and data
1829 FND_MSG_PUB.Count_And_Get
1830 ( p_count => x_msg_count
1831 , p_data => x_msg_data
1832 );
1833 WHEN OTHERS THEN
1834 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1835 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1836 THEN
1837 FND_MSG_PUB.Add_Exc_Msg
1838 ( G_PKG_NAME
1839 , 'Create_Inflation_Adjusted_Cost'
1840 );
1841 END IF;
1842 -- Get message count and data
1843 FND_MSG_PUB.Count_And_Get
1844 ( p_count => x_msg_count
1845 , p_data => x_msg_data
1846 );
1847
1848 END Create_Inflation_Adjusted_Cost;
1849
1850
1851 --========================================================================
1852 -- PROCEDURE : Get_Acct_CCID PRIVATE
1853 -- PARAMETERS: p_country_code Country code
1854 -- p_org_id Organization ID
1855 -- p_inv_item_id Inventory item ID
1856 -- x_inv_adj_ccid Inventory inflation account CCID
1857 -- x_monetary_corr_ccid Monetary correction account CCID
1858 -- x_sales_cost_ccid Sales cost account CCID
1859 -- COMMENT : This procedure return the account CCIDs.
1860 -- EXCEPTIONS: g_acct_ccid_null_exc Missing CCID
1861 --========================================================================
1862 PROCEDURE Get_Acct_CCID
1863 ( p_country_code IN VARCHAR2
1864 , p_org_id IN NUMBER
1865 , p_inv_item_id IN NUMBER
1866 , x_inv_adj_ccid OUT NOCOPY VARCHAR2
1867 , x_monetary_corr_ccid OUT NOCOPY VARCHAR2
1868 , x_sales_cost_ccid OUT NOCOPY VARCHAR2
1869 )
1870 IS
1871
1872 l_routine CONSTANT VARCHAR2(30) := 'get_acct_ccid';
1873
1874 l_inv_adj_ccid VARCHAR2(150);
1875 l_monetary_corr_ccid VARCHAR2(150);
1876 l_sales_cost_ccid VARCHAR2(150);
1877 l_err_item_code VARCHAR2(40);
1878 l_acct_ccid_null_exc EXCEPTION;
1879 BEGIN
1880
1881 SELECT
1882 Global_Attribute3
1883 , Global_Attribute4
1884 , Global_Attribute5
1885 INTO
1886 l_inv_adj_ccid
1887 , l_monetary_corr_ccid
1888 , l_sales_cost_ccid
1889 FROM
1890 MTL_SYSTEM_ITEMS
1891 WHERE Organization_ID = p_org_id
1892 AND Inventory_Item_ID = p_inv_item_id
1893 AND SUBSTR(GLOBAL_ATTRIBUTE_CATEGORY, 4,2) = p_country_code;
1894
1895 IF (l_inv_adj_ccid IS NULL)
1896 OR
1897 (l_monetary_corr_ccid IS NULL)
1898 OR
1899 (l_sales_cost_ccid IS NULL)
1900 THEN
1901 RAISE l_acct_ccid_null_exc;
1902 END IF;
1903
1904 x_inv_adj_ccid := l_inv_adj_ccid;
1905 x_monetary_corr_ccid := l_monetary_corr_ccid;
1906 x_sales_cost_ccid := l_sales_cost_ccid;
1907
1908 EXCEPTION
1909 WHEN NO_DATA_FOUND THEN
1910 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1911 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
1912 ,G_MODULE_HEAD || l_routine || '.nodatafound'
1913 ,'Organization Id:' || p_org_id || ' Inventory Item Id:' ||
1914 p_inv_item_id || ' Country Code:' || p_country_code
1915 );
1916 END IF;
1917 RAISE;
1918
1919 WHEN l_acct_ccid_null_exc THEN
1920 SELECT
1921 Concatenated_Segments
1922 INTO
1923 l_err_item_code
1924 FROM
1925 MTL_SYSTEM_ITEMS_KFV
1926 WHERE Organization_ID = p_org_id
1927 AND Inventory_Item_ID = p_inv_item_id;
1928 FND_MESSAGE.Set_Name('BOM', 'CST_MGD_INFL_ACCT_CCID_NULL');
1929 FND_MESSAGE.Set_Token('ITEM', l_err_item_code);
1930 FND_MSG_PUB.Add;
1931 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1932 THEN
1933 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1934 , 'Get_Acct_CCID'
1935 );
1936 END IF;
1937 RAISE g_acct_ccid_null_exc;
1938 WHEN OTHERS THEN
1939 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1940 THEN
1941 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1942 , 'Get_Acct_CCID'
1943 );
1944 END IF;
1945 RAISE;
1946
1947 END Get_Acct_CCID;
1948
1949
1950 --========================================================================
1951 -- PROCEDURE : Get_Set_Of_Books_ID PRIVATE
1952 -- PARAMETERS: p_org_id Organization ID
1953 -- x_set_of_books_id Set of books ID
1954 -- COMMENT : This procedure returns the set of books id.
1955 -- EXCEPTIONS:
1956 --========================================================================
1957 PROCEDURE Get_Set_Of_Books_ID
1958 ( p_org_id IN NUMBER
1959 , x_set_of_books_id OUT NOCOPY NUMBER
1960 )
1961 IS
1962 l_set_of_books_id NUMBER;
1963
1964 BEGIN
1965
1966 SELECT
1967 Set_Of_Books_ID
1968 INTO
1969 x_set_of_books_id
1970 FROM
1971 gl_sets_of_books
1972 , hr_organization_information
1973 WHERE set_of_books_id = org_information1
1974 AND upper(org_information_context) = upper('Accounting Information')
1975 AND organization_id = p_org_id;
1976
1977 EXCEPTION
1978
1979 WHEN OTHERS THEN
1980 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1981 THEN
1982 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1983 , 'Get_Set_Of_Books_ID'
1984 );
1985 END IF;
1986 RAISE;
1987
1988 END Get_Set_Of_Books_ID;
1989
1990
1991 --========================================================================
1992 -- PROCEDURE : Get_Currency_Code PRIVATE
1993 -- PARAMETERS: p_set_of_books_id Set of books ID
1994 -- x_currency_code Currency code
1995 -- COMMENT : This procedure returns the currency code for a set of books
1996 -- EXCEPTIONS:
1997 --========================================================================
1998 PROCEDURE Get_Currency_Code
1999 ( p_set_of_books_id IN NUMBER
2000 , x_currency_code OUT NOCOPY VARCHAR2
2001 )
2002 IS
2003 BEGIN
2004
2005 SELECT
2006 Currency_Code
2007 INTO
2008 x_currency_code
2009 FROM
2010 GL_SETS_OF_BOOKS
2011 WHERE Set_Of_Books_ID = p_set_of_books_id;
2012
2013 EXCEPTION
2014
2015 WHEN OTHERS THEN
2016 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2017 THEN
2018 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2019 , 'Get_Currency_Code'
2020 );
2021 END IF;
2022 RAISE;
2023
2024 END Get_Currency_Code;
2025
2026
2027 --========================================================================
2028 -- PROCEDURE : GL_Interface_Default PRIVATE
2029 -- PARAMETERS: p_country_code Country code
2030 -- p_org_id Organization ID
2031 -- p_inv_item_id Inventory item ID
2032 -- p_acct_period_id Accout period id
2033 -- p_inventory_adj_acct_cr Credit entry for inventory
2034 -- inflation account
2035 -- p_inventory_adj_acct_dr Debit entry for inventory
2036 -- inflation account
2037 -- p_monetary_corr_acct_cr Credit entry for monetary
2038 -- correction account
2039 -- p_sales_cost_acct_dr Debit entry for sales cost account
2040 -- p_set_of_books_id Set of books id
2041 -- p_currency_code Currency code
2042 -- p_user_category_name User JE category name
2043 -- p_user_source_name User JE source name
2044 -- x_acct_entry_tbl_rec Account entry table record
2045 -- COMMENT : This procedure defaults value for GL_INTERFACE
2046 --========================================================================
2047 PROCEDURE GL_Interface_Default
2048 ( p_country_code IN VARCHAR2
2049 , p_org_id IN NUMBER
2050 , p_inv_item_id IN NUMBER
2051 , p_acct_period_id IN NUMBER
2052 , p_inventory_adj_acct_cr IN NUMBER
2053 , p_inventory_adj_acct_dr IN NUMBER
2054 , p_monetary_corr_acct_cr IN NUMBER
2055 , p_sales_cost_acct_dr IN NUMBER
2056 , p_set_of_books_id IN NUMBER
2057 , p_currency_code IN VARCHAR2
2058 , p_user_category_name IN VARCHAR2
2059 , p_user_source_name IN VARCHAR2
2060 , p_accounting_date IN DATE
2061 , x_acct_entry_tbl_rec OUT NOCOPY Infl_Adj_Acct_Tbl_Rec_Type
2062 )
2063 IS
2064 l_routine CONSTANT VARCHAR2(30) := 'gl_interface_default';
2065
2066 l_acct_entry_tbl_rec Infl_Adj_Acct_Tbl_Rec_Type;
2067 l_inv_adj_ccid VARCHAR2(150);
2068 l_monetary_corr_ccid VARCHAR2(150);
2069 l_sales_cost_ccid VARCHAR2(150);
2070 l_tnsf_set_of_books_id NUMBER;
2071 l_tnsf_currency_code VARCHAR2(15);
2072 l_net_inv_acct_entry NUMBER;
2073 l_counter NUMBER;
2074
2075 -- Bug#4376862 fix (base bug#4363532 fix) : imbalance in GL_INTERFACE postings
2076 -- To balance the Debit and Credit entries inorder to post into GL_INTERFACE
2077 l_total_credit NUMBER;
2078 l_total_debit NUMBER;
2079 l_precision NUMBER;
2080 l_imbalance NUMBER;
2081 l_ctr_count NUMBER;
2082
2083 CURSOR l_transfer_org_csr IS
2084 SELECT
2085 Transfer_Organization_ID
2086 , NVL(SUM(Entered_CR), 0) Entered_CR
2087 , NVL(SUM(Entered_DR), 0) Entered_DR
2088 FROM
2089 CST_MGD_INFL_TSF_ORG_ENTRIES
2090 WHERE Acct_Period_ID = p_acct_period_id
2091 AND Organization_ID = p_org_id
2092 AND Inventory_Item_ID = p_inv_item_id
2093 AND Country_Code = p_country_code
2094 GROUP BY Transfer_Organization_ID;
2095
2096 -- Cursor to retrieve the precision of a currency code
2097 CURSOR precision_cur(c_currency_code VARCHAR2)
2098 IS
2099 SELECT
2100 nvl(precision,0)
2101 FROM fnd_currencies
2102 WHERE currency_code = c_currency_code;
2103
2104
2105 -- local debug vairables to use within loop
2106 l_debug_level NUMBER;
2107 l_state_level NUMBER;
2108
2109 BEGIN
2110
2111 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2112 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2113 ,G_MODULE_HEAD || l_routine || '.begin'
2114 ,l_routine || '<'
2115 );
2116 END IF;
2117
2118 Get_Acct_CCID( p_country_code => p_country_code
2119 , p_org_id => p_org_id
2120 , p_inv_item_id => p_inv_item_id
2121 , x_inv_adj_ccid => l_inv_adj_ccid
2122 , x_monetary_corr_ccid => l_monetary_corr_ccid
2123 , x_sales_cost_ccid => l_sales_cost_ccid
2124 );
2125
2126 -- Retrieve precision of a currency code
2127 OPEN precision_cur(p_currency_code);
2128 FETCH precision_cur
2129 INTO l_precision;
2130 IF precision_cur%NOTFOUND THEN
2131 -- a record must exist. This scenario should not occur
2132 l_precision := 0;
2133 END IF;
2134 CLOSE precision_cur;
2135
2136 -- Bug#4376862 fix (Base Bug#4363532 fix)
2137 -- intialize l_total_credit and l_total_debit
2138 l_total_credit := 0;
2139 l_total_debit := 0;
2140
2141 -- Assign local debug variables to use within loop
2142 l_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2143 l_state_level := FND_LOG.LEVEL_STATEMENT;
2144
2145 -- FP:11i9-11i12:Bug#4369851 fix (Base Bug#4306670 fix)
2146 -- Inflation Adjustment account cannot be split into two
2147 -- components - inflation adjustment monetary and inflation -- adjustment sales cost; since each account has to be
2148 -- summarized in GL_INTERFACE
2149 -- Therefore net inventory inflation adjustment is modified
2150 FOR l_counter IN 1..3
2151 LOOP
2152 l_acct_entry_tbl_rec(l_counter).status := 'NEW';
2153 l_acct_entry_tbl_rec(l_counter).set_of_books_id := p_set_of_books_id;
2154
2155 l_acct_entry_tbl_rec(l_counter).user_je_source_name :=
2156 p_user_source_name;
2157
2158 l_acct_entry_tbl_rec(l_counter).user_je_category_name :=
2159 p_user_category_name;
2160
2161 l_acct_entry_tbl_rec(l_counter).accounting_date := p_accounting_date;
2162 l_acct_entry_tbl_rec(l_counter).currency_code := p_currency_code;
2163 l_acct_entry_tbl_rec(l_counter).date_created := SYSDATE;
2164
2165 l_acct_entry_tbl_rec(l_counter).created_by :=
2166 NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),0);
2167
2168 l_acct_entry_tbl_rec(l_counter).actual_flag := 'A';
2169
2170 -- ======================================================================================
2171 -- inv. inflation account
2172 -- FP:11i9-11i12:Bug#4369851 fix (base bug#4306670 fix)
2173 -- inflation adjustment cannot be split into two components
2174 -- Each account is summarized
2175 -- NOTE: l_net_inv_acct_entry := abs(p_inventory_adj_acct_cr) -
2176 -- p_inventory_adj_acct_dr
2177 -- Base bug#4456502 second fix: l_net_inv_acct_entry :=
2178 -- round(abs(p_inventory_adj_acct_cr),l_precision) -
2179 -- round(abs(p_inventory_adj_acct_dr),l_precision)
2180 -- --------------------------------------------------------------------------------------
2181 -- FP Bug#7346248 fix: inflation adjustment account logic modified
2182 -- l_net_inv_acct_entry := round(p_inventory_adj_acct_cr, l_precision) +
2183 -- round(p_inventory_adj_acct_dr, l_precision)
2184 -- if l_net_inv_acct_entry is positive then l_total_debit := abs(l_net_inv_acct_entry)
2185 -- if l_net_inv_acct_entry is negative then l_total_credit := abs(l_net_inv_acct_entry)
2186 -- ======================================================================================
2187
2188 IF l_counter = 1
2189 THEN
2190 l_acct_entry_tbl_rec(l_counter).code_combination_id := l_inv_adj_ccid;
2191
2192 l_net_inv_acct_entry := round(p_inventory_adj_acct_cr,l_precision) +
2193 round(p_inventory_adj_acct_dr,l_precision);
2194
2195 IF (l_net_inv_acct_entry > 0) THEN
2196 l_acct_entry_tbl_rec(l_counter).entered_dr := round(l_net_inv_acct_entry,l_precision);
2197 l_total_debit :=
2198 l_total_debit + l_acct_entry_tbl_rec(l_counter).entered_dr;
2199 ELSE
2200 l_acct_entry_tbl_rec(l_counter).entered_cr := round(ABS(l_net_inv_acct_entry),l_precision);
2201 l_total_credit :=
2202 l_total_credit + l_acct_entry_tbl_rec(l_counter).entered_cr;
2203 END IF;
2204
2205 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2206 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2207 ,G_MODULE_HEAD || l_routine || '.inflacct'
2208 ,'Inventory Inflation Adjustment Account'
2209 );
2210
2211 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2212 ,G_MODULE_HEAD || l_routine || '.infldrcr'
2213 ,'Entered Dr:' || l_acct_entry_tbl_rec(l_counter).entered_dr
2214 || ' Entered Cr:' || l_acct_entry_tbl_rec(l_counter).entered_cr
2215 );
2216 END IF;
2217
2218 -- ========================================================================
2219 -- Bug#4225409 fix:out of balance fix in GL_INTERFACE when -ve inflation
2220 -- index; monetary correction acct cr to be posted in entered_dr if value
2221 -- is -ve
2222 -- ========================================================================
2223 -- monetary account
2224 ELSIF l_counter = 2
2225 THEN
2226 l_acct_entry_tbl_rec(l_counter).code_combination_id :=
2227 l_monetary_corr_ccid;
2228
2229 IF SIGN(p_monetary_corr_acct_cr) = 1 THEN
2230 -- value is positive
2231 l_acct_entry_tbl_rec(l_counter).entered_cr := round(p_monetary_corr_acct_cr,l_precision);
2232 l_total_credit :=
2233 l_total_credit + l_acct_entry_tbl_rec(l_counter).entered_cr;
2234 ELSE
2235 -- value is negative or zero
2236 l_acct_entry_tbl_rec(l_counter).entered_dr := round(ABS(p_monetary_corr_acct_cr),l_precision);
2237 l_total_debit :=
2238 l_total_debit + l_acct_entry_tbl_rec(l_counter).entered_dr;
2239 END IF;
2240
2241 IF (l_state_level >= l_debug_level) THEN
2242 FND_LOG.string(l_state_level
2243 ,G_MODULE_HEAD || l_routine || '.monacct'
2244 , 'Monetary Account'
2245 );
2246
2247 FND_LOG.string(l_state_level
2248 ,G_MODULE_HEAD || l_routine || '.mondrcr'
2249 ,'Entered Cr:' || l_acct_entry_tbl_rec(l_counter).entered_cr || 'Entered Dr:' || l_acct_entry_tbl_rec(l_counter).entered_dr
2250 );
2251 END IF;
2252
2253 -- sales cost account
2254 -- ========================================================================
2255 -- Bug#4225409 fix:out of balance fix in GL_INTERFACE when -ve inflation
2256 -- index; sales cost acct dr to be posted in entered_cr if value
2257 -- is -ve
2258 -- If the value is positive, post it in ENTERED_CR
2259 -- if the value is negative, post it in ENTERED_DR
2260 -- ========================================================================
2261 ELSIF l_counter = 3
2262 THEN
2263 l_acct_entry_tbl_rec(l_counter).code_combination_id :=
2264 l_sales_cost_ccid;
2265 IF SIGN(p_sales_cost_acct_dr) = 1 THEN
2266 -- value is positive
2267 l_acct_entry_tbl_rec(l_counter).entered_cr := round(p_sales_cost_acct_dr,l_precision);
2268 l_total_credit :=
2269 l_total_credit + l_acct_entry_tbl_rec(l_counter).entered_cr;
2270 ELSE
2271 -- value is negative
2272 l_acct_entry_tbl_rec(l_counter).entered_dr := round(ABS(p_sales_cost_acct_dr),l_precision);
2273 l_total_debit :=
2274 l_total_debit + l_acct_entry_tbl_rec(l_counter).entered_dr;
2275 END IF;
2276
2277 IF (l_state_level >= l_debug_level) THEN
2278 FND_LOG.string(l_state_level
2279 ,G_MODULE_HEAD || l_routine || '.salesacct'
2280 , 'Sales Cost Account'
2281 );
2282
2283 FND_LOG.string(l_state_level
2284 ,G_MODULE_HEAD || l_routine || '.salesdrcr'
2285 ,'Entered Dr:' || l_acct_entry_tbl_rec(l_counter).entered_dr || 'Entered Cr:' || l_acct_entry_tbl_rec(l_counter).entered_cr
2286 );
2287 END IF;
2288
2289 END IF;
2290
2291 END LOOP;
2292
2293 l_counter := 4;
2294 -- for transfer organizations
2295 -- Base Bug#4456502 second fix:l_net_inv_acct_entry := round(abs(p_inventory_adj_acct_cr),l_precision)
2296 -- round(abs(p_inventory_adj_acct_dr),l_precision)
2297 -- Bug#4376862 fix(Base bug#4363532 fix): rounding issue
2298 FOR l_transfer_acct IN l_transfer_org_csr
2299 LOOP
2300 Get_Acct_CCID( p_country_code => p_country_code
2301 , p_org_id =>
2302 l_transfer_acct.Transfer_Organization_ID
2303 , p_inv_item_id => p_inv_item_id
2304 , x_inv_adj_ccid => l_inv_adj_ccid
2305 , x_monetary_corr_ccid => l_monetary_corr_ccid
2306 , x_sales_cost_ccid => l_sales_cost_ccid
2307 );
2308
2309 Get_Set_Of_Books_ID( p_org_id =>
2310 l_transfer_acct.Transfer_Organization_ID
2311 , x_set_of_books_id => l_tnsf_set_of_books_id
2312 );
2313
2314 Get_Currency_Code( p_set_of_books_id => l_tnsf_set_of_books_id
2315 , x_currency_code => l_tnsf_currency_code
2316 );
2317
2318 l_acct_entry_tbl_rec(l_counter).status := 'NEW';
2319 l_acct_entry_tbl_rec(l_counter).set_of_books_id :=
2320 l_tnsf_set_of_books_id;
2321
2322 l_acct_entry_tbl_rec(l_counter).user_je_source_name :=
2323 p_user_source_name;
2324
2325 l_acct_entry_tbl_rec(l_counter).user_je_category_name :=
2326 p_user_category_name;
2327
2328 l_acct_entry_tbl_rec(l_counter).accounting_date := p_accounting_date;
2329 l_acct_entry_tbl_rec(l_counter).currency_code :=
2330 l_tnsf_currency_code;
2331
2332 l_acct_entry_tbl_rec(l_counter).date_created := SYSDATE;
2333
2334 l_acct_entry_tbl_rec(l_counter).created_by :=
2335 NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),0);
2336
2337 l_acct_entry_tbl_rec(l_counter).actual_flag := 'A';
2338 l_acct_entry_tbl_rec(l_counter).code_combination_id := l_inv_adj_ccid;
2339
2340 l_net_inv_acct_entry :=
2341 round(ABS(l_transfer_acct.Entered_CR),l_precision) - round(ABS(l_transfer_acct.Entered_DR),l_precision);
2342
2343 IF (l_net_inv_acct_entry > 0)
2344 THEN
2345 l_acct_entry_tbl_rec(l_counter).entered_cr := round(l_net_inv_acct_entry,l_precision);
2346 l_total_credit := l_total_credit + l_acct_entry_tbl_rec(l_counter).entered_cr;
2347 ELSE
2348 l_acct_entry_tbl_rec(l_counter).entered_dr :=
2349 round(ABS(l_net_inv_acct_entry),l_precision);
2350 l_total_debit := l_total_debit + l_acct_entry_tbl_rec(l_counter).entered_dr;
2351 END IF;
2352
2353 IF (l_state_level >= l_debug_level) THEN
2354 FND_LOG.string(l_state_level
2355 ,G_MODULE_HEAD || l_routine || '.transorg'
2356 , 'Transfer Organization Id:' || l_transfer_acct.Transfer_Organization_ID
2357 );
2358
2359 FND_LOG.string(l_state_level
2360 ,G_MODULE_HEAD || l_routine || '.crdr'
2361 ,'Entered Cr:' || l_acct_entry_tbl_rec(l_counter).entered_cr || ' Entered Dr:' || l_acct_entry_tbl_rec(l_counter).entered_dr
2362 );
2363
2364 END IF;
2365
2366 l_counter := l_counter + 1;
2367
2368 END LOOP;
2369
2370 -- =================================================================
2371 -- Bug#4376862 fix (Base bug#4363532 fix): balance debit and credit
2372 -- Perform balancing the accounts inorder to post into GL_INTERFACE
2373 -- =================================================================
2374 IF (l_state_level >= l_debug_level) THEN
2375 FND_LOG.string(l_state_level
2376 ,G_MODULE_HEAD || l_routine || '.totdrcr'
2377 ,'Total Debit:' || l_total_debit || ' Total Credit:' || l_total_credit
2378 );
2379 END IF;
2380
2381 l_imbalance := l_total_debit - l_total_credit;
2382
2383 IF (l_state_level >= l_debug_level) THEN
2384 FND_LOG.string(l_state_level
2385 ,G_MODULE_HEAD || l_routine || '.imbal'
2386 , 'Imbalance Value:' || l_imbalance
2387 );
2388 END IF;
2389
2390
2391 IF SIGN(l_imbalance) = 1 THEN
2392 -- positive, add the imbalance to credit a/c (increase the value)
2393 l_ctr_count := 0;
2394 FOR l_ctr_idx IN 1..l_counter
2395 LOOP
2396 -- set the counter to get the counter of the a/c
2397 l_ctr_count := l_ctr_count + 1;
2398 IF l_acct_entry_tbl_rec(l_ctr_idx).entered_cr IS NOT NULL THEN
2399
2400 l_acct_entry_tbl_rec(l_ctr_idx).entered_cr :=
2401 l_acct_entry_tbl_rec(l_ctr_idx).entered_cr + l_imbalance;
2402
2403 IF (l_state_level >= l_debug_level) THEN
2404 FND_LOG.string(l_state_level
2405 ,G_MODULE_HEAD || l_routine || '.balentcr'
2406 , 'Counter of the a/c:' || l_ctr_count ||
2407 ' Balanced Entered Cr:' || l_acct_entry_tbl_rec(l_ctr_idx).entered_cr
2408 );
2409 END IF;
2410
2411 EXIT;
2412 END IF;
2413 END LOOP;
2414
2415 ELSIF SIGN(l_imbalance) = -1 THEN
2416 -- set the counter to get the counter of the a/c
2417 l_ctr_count := 0;
2418 -- negative, add the imbalance to debit a/c (increase the value)
2419 FOR l_ctr_idx IN 1..l_counter
2420 LOOP
2421 -- set the counter to get the counter of the a/c
2422 l_ctr_count := l_ctr_count + 1;
2423 IF l_acct_entry_tbl_rec(l_ctr_idx).entered_dr IS NOT NULL THEN
2424
2425 l_acct_entry_tbl_rec(l_ctr_idx).entered_dr :=
2426 l_acct_entry_tbl_rec(l_ctr_idx).entered_dr + ABS(l_imbalance);
2427
2428 IF (l_state_level >= l_debug_level) THEN
2429 FND_LOG.string(l_state_level
2430 ,G_MODULE_HEAD || l_routine || '.balentdr'
2431 , 'Counter of the a/c:' || l_ctr_count ||
2432 ' Balanced Entered Dr:' || l_acct_entry_tbl_rec(l_ctr_idx).entered_dr
2433 );
2434 END IF;
2435
2436 EXIT;
2437 END IF;
2438 END LOOP;
2439
2440 END IF;
2441
2442 x_acct_entry_tbl_rec := l_acct_entry_tbl_rec;
2443
2444 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2445 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2446 ,G_MODULE_HEAD || l_routine || '.end'
2447 ,l_routine || '>'
2448 );
2449 END IF;
2450
2451 EXCEPTION
2452
2453 WHEN OTHERS THEN
2454 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2455 THEN
2456 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2457 , 'GL_Interface_Default'
2458 );
2459 END IF;
2460 RAISE;
2461
2462 END GL_Interface_Default;
2463
2464
2465 --========================================================================
2466 -- PROCEDURE : Create_Journal_Entries PRIVATE
2467 -- PARAMETERS: p_infl_adj_acct_rec Inflation account record
2468 -- COMMENT : This procedure crreates the account entry data.
2469 --========================================================================
2470 PROCEDURE Create_Journal_Entries
2471 ( p_infl_adj_acct_rec IN Infl_Adj_Acct_Rec_Type
2472 )
2473 IS
2474 BEGIN
2475
2476 INSERT INTO
2477 GL_INTERFACE(
2478 Status
2479 , Set_Of_Books_ID
2480 , User_JE_Source_Name
2481 , User_JE_Category_Name
2482 , Accounting_Date
2483 , Currency_Code
2484 , Date_Created
2485 , Created_By
2486 , Actual_Flag
2487 , Entered_DR
2488 , Entered_CR
2489 , Code_Combination_ID
2490 )
2491 VALUES(
2492 p_infl_adj_acct_rec.Status
2493 , p_infl_adj_acct_rec.Set_Of_Books_ID
2494 , p_infl_adj_acct_rec.User_JE_Source_Name
2495 , p_infl_adj_acct_rec.User_JE_Category_Name
2496 , p_infl_adj_acct_rec.Accounting_Date
2497 , p_infl_adj_acct_rec.Currency_Code
2498 , p_infl_adj_acct_rec.Date_Created
2499 , p_infl_adj_acct_rec.Created_By
2500 , p_infl_adj_acct_rec.Actual_Flag
2501 , p_infl_adj_acct_rec.Entered_DR
2502 , p_infl_adj_acct_rec.Entered_CR
2503 , p_infl_adj_acct_rec.Code_Combination_ID
2504 );
2505
2506 EXCEPTION
2507
2508 WHEN OTHERS THEN
2509 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2510 THEN
2511 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2512 , 'Create_Journal_Entries'
2513 );
2514 END IF;
2515 RAISE;
2516
2517 END Create_Journal_Entries;
2518
2519
2520 --========================================================================
2521 -- PROCEDURE : Create_Infl_Period_Status PRIVATE
2522 -- PARAMETERS: p_org_id Organization ID
2523 -- p_acct_period_id Account period ID
2524 -- x_return_status Return error if failed
2525 -- COMMENT : This procedure makes the inflation adjusted period status
2526 -- to PROCESS
2527 -- USAGE : This procedue is used in Calculate_Adjustment at the end
2528 -- inflation processor run to set the inflation status
2529 -- EXCEPTIONS: g_exception1 exception description
2530 --========================================================================
2531 PROCEDURE Create_Infl_Period_Status
2532 ( p_org_id IN NUMBER
2533 , p_acct_period_id IN NUMBER
2534 , x_return_status OUT NOCOPY VARCHAR2
2535 )
2536 IS
2537 l_routine CONSTANT VARCHAR2(30) := 'create_infl_period_status';
2538
2539 BEGIN
2540
2541 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2542 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2543 ,G_MODULE_HEAD || l_routine || '.begin'
2544 ,l_routine || '<'
2545 );
2546 END IF;
2547
2548 x_return_status := FND_API.G_RET_STS_SUCCESS;
2549
2550 INSERT INTO
2551 CST_MGD_INFL_ADJ_PER_STATUSES(
2552 Organization_ID
2553 , Acct_Period_ID
2554 , Last_Update_Date
2555 , Last_Updated_By
2556 , Creation_Date
2557 , Created_By
2558 , Last_Update_Login
2559 , Request_ID
2560 , Program_Application_ID
2561 , Program_ID
2562 , Program_Update_Date
2563 , STATUS
2564 )
2565 VALUES(
2566 p_org_id
2567 , p_acct_period_id
2568 , SYSDATE
2569 , NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),0)
2570 , SYSDATE
2571 , NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),0)
2572 , TO_NUMBER(FND_PROFILE.Value('LOGIN_ID'))
2573 , TO_NUMBER(FND_PROFILE.Value('CONC_REQUEST_ID'))
2574 , TO_NUMBER(FND_PROFILE.Value('PROG_APPL_ID'))
2575 , TO_NUMBER(FND_PROFILE.Value('CONC_PROG_ID'))
2576 , SYSDATE
2577 , 'PROCESS'
2578 );
2579
2580 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2581 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2582 ,G_MODULE_HEAD || l_routine || '.end'
2583 ,l_routine || '>'
2584 );
2585 END IF;
2586
2587 EXCEPTION
2588
2589 WHEN OTHERS THEN
2590 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2591 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2592 THEN
2593 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2594 , 'Create_Infl_Period_Status'
2595 );
2596 END IF;
2597 RAISE;
2598
2599 END Create_Infl_Period_Status;
2600
2601
2602 --========================================================================
2603 -- PROCEDURE : Update_Infl_Period_Status PRIVATE
2604 -- PARAMETERS: p_org_id Organization ID
2605 -- p_acct_period_id Account period ID
2606 -- x_return_status Return error if failed
2607 -- COMMENT : This procedure makes the inflation adjusted period status
2608 -- to FINAL
2609 -- USAGE : This procedure is used in Transfer_to_GL at the end
2610 -- to set the inflation status FINAL
2611 -- EXCEPTIONS: g_exception1 exception description
2612 --========================================================================
2613 PROCEDURE Update_Infl_Period_Status
2614 ( p_org_id IN NUMBER
2615 , p_acct_period_id IN NUMBER
2616 , x_return_status OUT NOCOPY VARCHAR2
2617 )
2618 IS
2619 BEGIN
2620
2621 x_return_status := FND_API.G_RET_STS_SUCCESS;
2622
2623 UPDATE CST_MGD_INFL_ADJ_PER_STATUSES
2624 SET Status = 'FINAL'
2625 , Last_Update_Date = SYSDATE
2626 , Last_Updated_By = NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),0)
2627 , Creation_Date = SYSDATE
2628 , Created_By = NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),0)
2629 , Last_Update_Login = TO_NUMBER(FND_PROFILE.Value('LOGIN_ID'))
2630 , Request_ID = TO_NUMBER(FND_PROFILE.Value('CONC_REQUEST_ID'))
2631 , Program_Application_ID = TO_NUMBER(FND_PROFILE.Value('PROG_APPLD_ID'))
2632 , Program_ID = TO_NUMBER(FND_PROFILE.Value('CONC_PROG_ID'))
2633 , Program_Update_Date = SYSDATE
2634 WHERE organization_id = p_org_id
2635 AND acct_period_id = p_acct_period_id;
2636
2637 EXCEPTION
2638
2639 WHEN OTHERS THEN
2640 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2641 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2642 THEN
2643 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2644 , 'Create_Period_Status'
2645 );
2646 END IF;
2647 RAISE;
2648
2649 END Update_Infl_Period_Status;
2650
2651
2652 --========================================================================
2653 -- PROCEDURE : Validate_Hist_Attributes PRIVATE
2654 -- PARAMETERS: p_historical_infl_adj_rec Historical data record
2655 -- x_return_status Return error if failed
2656 -- COMMENT : This procedure validates historical data
2657 --========================================================================
2658 PROCEDURE Validate_Hist_Attributes
2659 ( p_historical_infl_adj_rec IN
2660 CST_MGD_INFL_ADJUSTMENT_PUB.Inflation_Adjustment_Rec_Type
2661 , x_return_status OUT NOCOPY VARCHAR2
2662 )
2663 IS
2664 BEGIN
2665
2666 x_return_status := FND_API.G_RET_STS_SUCCESS;
2667
2668 -- Validate historical inflation adjustment attributes
2669 IF (p_historical_infl_adj_rec.country_code IS NULL)
2670 OR
2671 (LENGTH(p_historical_infl_adj_rec.country_code) <> 2)
2672 THEN
2673 x_return_status := FND_API.G_RET_STS_ERROR;
2674 END IF;
2675
2676 IF (p_historical_infl_adj_rec.organization_id IS NULL)
2677 THEN
2678 x_return_status := FND_API.G_RET_STS_ERROR;
2679 END IF;
2680
2681 IF (p_historical_infl_adj_rec.acct_period_id IS NULL)
2682 THEN
2683 x_return_status := FND_API.G_RET_STS_ERROR;
2684 END IF;
2685
2686 IF (p_historical_infl_adj_rec.inventory_item_id IS NULL)
2687 THEN
2688 x_return_status := FND_API.G_RET_STS_ERROR;
2689 END IF;
2690
2691 IF (p_historical_infl_adj_rec.begin_qty IS NULL)
2692 -- OR
2693 -- (p_historical_infl_adj_rec.begin_qty < 0)
2694 THEN
2695 x_return_status := FND_API.G_RET_STS_ERROR;
2696 END IF;
2697
2698 IF (p_historical_infl_adj_rec.begin_cost IS NULL)
2699 -- OR
2700 -- (p_historical_infl_adj_rec.begin_cost < 0)
2701 THEN
2702 x_return_status := FND_API.G_RET_STS_ERROR;
2703 END IF;
2704
2705 IF (p_historical_infl_adj_rec.begin_inflation_adj IS NULL)
2706 -- OR
2707 -- (p_historical_infl_adj_rec.begin_inflation_adj < 0)
2708 THEN
2709 x_return_status := FND_API.G_RET_STS_ERROR;
2710 END IF;
2711
2712 EXCEPTION
2713
2714 WHEN OTHERS THEN
2715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2716 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2717 THEN
2718 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2719 , 'Validate_Hist_Attributes'
2720 );
2721 END IF;
2722 RAISE;
2723
2724 END Validate_Hist_Attributes;
2725
2726
2727 --========================================================================
2728 -- PROCEDURE : Hist_Default PRIVATE
2729 -- PARAMETERS: p_historical_infl_adj_rec Historical data record
2730 -- x_historical_infl_adj_rec Historical data record
2731 -- COMMENT : This procedure defaults historical data
2732 --========================================================================
2733 PROCEDURE Hist_Default
2734 ( p_historical_infl_adj_rec IN
2735 CST_MGD_INFL_ADJUSTMENT_PUB.Inflation_Adjustment_Rec_Type
2736 , x_historical_infl_adj_rec OUT NOCOPY
2737 CST_MGD_INFL_ADJUSTMENT_PUB.Inflation_Adjustment_Rec_Type
2738 )
2739 IS
2740 l_historical_infl_adj_rec
2741 CST_MGD_INFL_ADJUSTMENT_PUB.Inflation_Adjustment_Rec_Type;
2742 BEGIN
2743
2744 l_historical_infl_adj_rec := p_historical_infl_adj_rec;
2745
2746 IF (l_historical_infl_adj_rec.last_update_date IS NULL)
2747 THEN
2748 l_historical_infl_adj_rec.last_update_date := SYSDATE;
2749 END IF;
2750
2751 IF (l_historical_infl_adj_rec.last_updated_by IS NULL)
2752 THEN
2753 l_historical_infl_adj_rec.last_updated_by :=
2754 NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),0);
2755 END IF;
2756
2757 IF (l_historical_infl_adj_rec.creation_date IS NULL)
2758 THEN
2759 l_historical_infl_adj_rec.creation_date := SYSDATE;
2760 END IF;
2761
2762 IF (l_historical_infl_adj_rec.created_by IS NULL)
2763 THEN
2764 l_historical_infl_adj_rec.created_by :=
2765 NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),0);
2766 END IF;
2767
2768 IF (l_historical_infl_adj_rec.last_update_login IS NULL)
2769 THEN
2770 l_historical_infl_adj_rec.last_update_login :=
2771 TO_NUMBER(FND_PROFILE.Value('LOGIN_ID'));
2772 END IF;
2773
2774 IF (l_historical_infl_adj_rec.request_id IS NULL)
2775 THEN
2776 l_historical_infl_adj_rec.request_id :=
2777 TO_NUMBER(FND_PROFILE.Value('CONC_REQUEST_ID'));
2778 END IF;
2779
2780 IF (l_historical_infl_adj_rec.program_application_id IS NULL)
2781 THEN
2782 l_historical_infl_adj_rec.program_application_id :=
2783 TO_NUMBER(FND_PROFILE.Value('PROG_APPL_ID'));
2784 END IF;
2785
2786 IF (l_historical_infl_adj_rec.program_id IS NULL)
2787 THEN
2788 l_historical_infl_adj_rec.program_id :=
2789 TO_NUMBER(FND_PROFILE.Value('CONC_PROG_ID'));
2790 END IF;
2791
2792 IF (l_historical_infl_adj_rec.program_update_date IS NULL)
2793 THEN
2794 l_historical_infl_adj_rec.program_update_date := SYSDATE;
2795 END IF;
2796
2797 l_historical_infl_adj_rec.purchase_qty := NULL;
2798 l_historical_infl_adj_rec.purchase_cost := NULL;
2799 l_historical_infl_adj_rec.actual_qty :=
2800 l_historical_infl_adj_rec.begin_qty;
2801 l_historical_infl_adj_rec.actual_cost :=
2802 l_historical_infl_adj_rec.begin_cost;
2803 l_historical_infl_adj_rec.actual_inflation_adj :=
2804 l_historical_infl_adj_rec.begin_inflation_adj;
2805 l_historical_infl_adj_rec.issue_qty := 0;
2806 l_historical_infl_adj_rec.issue_cost := 0;
2807 l_historical_infl_adj_rec.issue_inflation_adj := 0;
2808 l_historical_infl_adj_rec.inventory_adj_acct_cr := 0;
2809 l_historical_infl_adj_rec.inventory_adj_acct_dr := 0;
2810 l_historical_infl_adj_rec.monetary_corr_acct_cr := 0;
2811 l_historical_infl_adj_rec.sales_cost_acct_dr := 0;
2812 l_historical_infl_adj_rec.historical_flag := 'Y';
2813
2814 x_historical_infl_adj_rec := l_historical_infl_adj_rec;
2815
2816 EXCEPTION
2817
2818 WHEN OTHERS THEN
2819 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2820 THEN
2821 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2822 , 'Hist_Default'
2823 );
2824 END IF;
2825 RAISE;
2826
2827 END Hist_Default;
2828
2829
2830 --========================================================================
2831 -- PROCEDURE : Get_Period_End_Avg_Cost PRIVATE
2832 -- PARAMETERS: p_acct_period_id Account period ID
2833 -- p_org_id Organization ID
2834 -- p_inv_item_id Inventory item ID
2835 -- p_cost_group_id Cost Group Id
2836 -- x_period_end_item_avg_cost Period end item unit average
2837 -- cost
2838 -- COMMENT : This procedure returns period end item unit average cost.
2839 -- cost group id NVL syntax added to support the inventory master book rpt
2840 -- EXCEPTIONS:
2841 --========================================================================
2842 PROCEDURE Get_Period_End_Avg_Cost
2843 ( p_acct_period_id IN NUMBER
2844 , p_org_id IN NUMBER
2845 , p_inv_item_id IN NUMBER
2846 , p_cost_group_id IN CST_COST_GROUPS.cost_group_id%TYPE
2847 , x_period_end_item_avg_cost OUT NOCOPY NUMBER
2848 )
2849 IS
2850 l_routine CONSTANT VARCHAR2(30) := 'get_period_end_avg_cost';
2851
2852 -- cursor to retrieve period end unit cost from view
2853 CURSOR period_end_unit_cost_cursor(c_org_id NUMBER
2854 ,c_acct_period_id NUMBER
2855 ,c_inv_item_id NUMBER
2856 ,c_cost_group_id CST_COST_GROUPS.cost_group_id%TYPE
2857 )
2858 IS
2859 SELECT
2860 SUM(Period_End_Unit_Cost)
2861 , DECODE(SUM(NVL(ABS(Period_End_Quantity), 1) * Period_End_Unit_Cost), 0, SUM(Period_End_Unit_Cost)/COUNT(*), SUM(NVL(ABS(Period_End_Quantity), 1) * Period_End_Unit_Cost)) /
2862 DECODE(SUM(NVL(ABS(Period_End_Quantity), 1)), 0, 1, SUM(NVL(ABS(Period_End_Quantity), 1)))
2863 FROM (
2864 SELECT rollback_quantity period_end_quantity,
2865 decode(rollback_quantity,0,0,rollback_value/rollback_quantity) period_end_unit_cost
2866 FROM cst_period_close_summary
2867 WHERE Organization_ID = c_org_id
2868 AND Acct_Period_ID = c_acct_period_id
2869 AND Inventory_Item_ID = c_inv_item_id
2870 AND Cost_Group_ID = NVL(c_cost_group_id,Cost_Group_ID)
2871 UNION ALL
2872 SELECT period_end_quantity, period_end_unit_cost
2873 FROM mtl_per_close_dtls
2874 WHERE Organization_ID = c_org_id
2875 AND Acct_Period_ID = c_acct_period_id
2876 AND Inventory_Item_ID = c_inv_item_id
2877 AND Cost_Group_ID = NVL(c_cost_group_id,Cost_Group_ID)
2878 );
2879
2880 l_sum_per_end_unit_cost NUMBER;
2881 l_nd_per_end_cost_exc EXCEPTION;
2882
2883 BEGIN
2884
2885 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2886 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2887 ,G_MODULE_HEAD || l_routine || '.begin'
2888 ,l_routine || '<'
2889 );
2890 END IF;
2891
2892 -- Retrieve Period End Unit Cost
2893 OPEN period_end_unit_cost_cursor(p_org_id
2894 ,p_acct_period_id
2895 ,p_inv_item_id
2896 ,p_cost_group_id
2897 );
2898 FETCH period_end_unit_cost_cursor
2899 INTO
2900 l_sum_per_end_unit_cost
2901 , x_period_end_item_avg_cost;
2902
2903 CLOSE period_end_unit_cost_cursor;
2904
2905 IF l_sum_per_end_unit_cost IS NULL
2906 THEN
2907 RAISE l_nd_per_end_cost_exc;
2908 END IF;
2909
2910 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2911 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2912 ,G_MODULE_HEAD || l_routine || '.end'
2913 ,l_routine || '>'
2914 );
2915 END IF;
2916
2917 EXCEPTION
2918
2919 WHEN l_nd_per_end_cost_exc THEN
2920 FND_MESSAGE.Set_Name('BOM', 'CST_MGD_INFL_UNIT_COST_NULL');
2921 FND_MSG_PUB.Add;
2922 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2923 THEN
2924 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2925 , 'Get_Period_End_Avg_Cost'
2926 );
2927 END IF;
2928
2929 WHEN OTHERS THEN
2930 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2931 THEN
2932 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2933 , 'Get_Period_End_Avg_Cost'
2934 );
2935 END IF;
2936 RAISE;
2937
2938 END Get_Period_End_Avg_Cost;
2939
2940
2941 END CST_MGD_INFL_ADJUSTMENT_PVT;