[Home] [Help]
PACKAGE BODY: APPS.CST_ACCOUNTINGPERIOD_PUB
Source
1 PACKAGE BODY CST_AccountingPeriod_PUB AS
2 /* $Header: CSTPAPEB.pls 120.18.12020000.4 2012/11/27 14:50:21 mpuranik ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_AccountingPeriod_PUB';
5
6 PROCEDURE Get_PendingTcount(
7 p_api_version IN NUMBER,
8 p_org_id IN INTEGER,
9 p_closing_period IN INTEGER,
10 p_sched_close_date IN DATE,
11 x_pend_receiving OUT NOCOPY INTEGER,
12 x_unproc_matl OUT NOCOPY INTEGER,
13 x_pend_matl OUT NOCOPY INTEGER,
14 x_uncost_matl OUT NOCOPY INTEGER,
15 x_pend_move OUT NOCOPY INTEGER,
16 x_pend_wip_cost OUT NOCOPY INTEGER,
17 x_uncost_wsm OUT NOCOPY INTEGER,
18 x_pending_wsm OUT NOCOPY INTEGER,
19 x_pending_ship OUT NOCOPY INTEGER,
20 /* Support for LCM */
21 x_pending_lcm OUT NOCOPY INTEGER,
22 x_released_work_orders OUT NOCOPY INTEGER,
23 x_return_status OUT NOCOPY VARCHAR2
24 ) IS
25 l_tcount INTEGER;
26 l_eam_enabled VARCHAR2(1);
27 l_lcm_enabled VARCHAR2(1); /* Support for LCM */
28 l_in_rec_type WSH_INTEGRATION.ShpgUnTrxdInRecType;
29 l_out_rec_type WSH_INTEGRATION.ShpgUnTrxdOutRecType;
33 l_msg_data VARCHAR2(200);
30 l_io_rec_type WSH_INTEGRATION.ShpgUnTrxdInOutRecType;
31 l_return_status VARCHAR2(200);
32 l_msg_count NUMBER;
34 l_le_closing_fm_date DATE;
35 l_sched_close_date DATE;
36 l_legal_entity NUMBER := 0;
37
38 l_api_name CONSTANT VARCHAR2(30) := 'Get_PendingTcount';
39 l_api_version CONSTANT NUMBER := 1.0;
40 l_msg_level_threshold NUMBER;
41 l_stmt_num NUMBER := 0;
42
43 BEGIN
44
45 -- Standard Start of API savepoint
46 SAVEPOINT Get_PendingTcount_PUB;
47
48 -- Check for call compatibility
49 IF NOT FND_API.Compatible_API_Call
50 ( p_current_version_number => l_api_version,
51 p_caller_version_number => p_api_version,
52 p_api_name => l_api_name,
53 p_pkg_name => G_PKG_NAME
54 )
55 THEN
56 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
57 END IF;
58
59 -- Check for message level threshold
60 l_msg_level_threshold := FND_PROFILE.Value('FND_AS_MSG_LEVEL_THRESHOLD');
61
62 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
63 THEN
64 FND_MSG_PUB.Add_Exc_Msg(
65 p_pkg_name => G_PKG_NAME,
66 p_procedure_name => l_api_name,
67 p_error_text => SUBSTR(
68 l_stmt_num||':'||
69 p_org_id||','||
70 p_closing_period||','||
71 p_sched_close_date,
72 1,
73 240
74 )
75 );
76 END IF;
77
78 l_return_status := fnd_api.g_ret_sts_success;
79 l_msg_count := 0;
80 l_msg_data := '';
81
82 l_stmt_num := 5;
83 SELECT legal_entity
84 INTO l_legal_entity
85 FROM cst_acct_info_v
86 WHERE organization_id = p_org_id;
87
88 l_stmt_num := 7;
89 l_sched_close_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
90 p_sched_close_date,
91 l_legal_entity);
92
93 l_sched_close_date := l_sched_close_date + 1 - (1/(24*3600));
94
95 l_stmt_num := 10;
96 -- Unprocessed Material transactions (must resolve)
97 BEGIN
98 SELECT COUNT(*)
99 INTO l_tcount
100 FROM mtl_material_transactions_temp
101 WHERE organization_id = p_org_id
102 AND transaction_date <= l_sched_close_date
103 AND NVL(transaction_status,0) <> 2; -- 2 indicates a save-only status
104
105 x_unproc_matl := l_tcount;
106
107 EXCEPTION
108 when NO_DATA_FOUND then
109 x_unproc_matl := 0;
110 when OTHERS then
111 x_unproc_matl := -1;
112 END;
113
114 l_stmt_num := 20;
115 -- Uncosted Transactions (must resolve)
116 BEGIN
117 SELECT /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */
118 COUNT(*)
119 INTO l_tcount
120 FROM mtl_material_transactions MMT
121 WHERE organization_id = p_org_id
122 AND transaction_date <= l_sched_close_date
123 AND costed_flag IN('N','E');-- bug 13054482 Changed the not null condition, as it causes a index full scan
124
125 x_uncost_matl := l_tcount;
126
127 EXCEPTION
128 when NO_DATA_FOUND then
129 x_uncost_matl := 0;
130 when OTHERS then
131 x_uncost_matl := -1;
132 END;
133
134 l_stmt_num := 30;
135 -- Pending WIP costing transactions (must resolve)
136 BEGIN
137 SELECT COUNT(*)
138 INTO l_tcount
139 FROM wip_cost_txn_interface
140 WHERE organization_id = p_org_id
141 AND transaction_date <= l_sched_close_date;
142
143 x_pend_wip_cost := l_tcount;
144
145 EXCEPTION
146 when NO_DATA_FOUND then
147 x_pend_wip_cost := 0;
148 when OTHERS then
149 x_pend_wip_cost := -1;
150 END;
151
152 l_stmt_num := 40;
153 -- Uncosted WSM transactions (must resolve)
154 /* Bug# 3926917: Period Close Diagnostics Project
155 Uncosted WSM transactions are available in MMT from 11.5.9
156 Hence need not be looked up separately. */
157
158 x_uncost_wsm := 0;
159
160 -- Pending WSM interface transactions (must resolve)
161 /* Bug# 3926917: Period Close Diagnostics Project
162 Added check on two new interface tables wsm_lot_move_txn_interface
163 And wsm_lot_split_merges_interface */
164
165 BEGIN
166
167 l_stmt_num := 50;
168 -- Pending Split Merge Transactions interface
169
170 SELECT COUNT(*)
171 INTO l_tcount
172 FROM wsm_split_merge_txn_interface
173 WHERE organization_id = p_org_id
174 AND process_status <> wip_constants.completed
175 AND transaction_date <= l_sched_close_date;
176
177 x_pending_wsm := l_tcount;
178
179 l_stmt_num := 52;
180 -- Pending Lot Move Transactions Interface
181
182 SELECT COUNT(*)
183 INTO l_tcount
184 FROM wsm_lot_move_txn_interface
185 WHERE organization_id = p_org_id
186 AND status <> wip_constants.completed
190
187 AND transaction_date <= l_sched_close_date;
188
189 x_pending_wsm := x_pending_wsm + l_tcount;
191 l_stmt_num := 55;
192 -- Pending Lot Split Merges Interface
193
194 SELECT COUNT(*)
195 INTO l_tcount
196 FROM wsm_lot_split_merges_interface
197 WHERE organization_id = p_org_id
198 AND process_status <> wip_constants.completed
199 AND transaction_date <= l_sched_close_date;
200
201 x_pending_wsm := x_pending_wsm + l_tcount;
202
203 EXCEPTION
204 when NO_DATA_FOUND then
205 x_pending_wsm := 0;
206 when OTHERS then
207 x_pending_wsm := -1;
208 END;
209
210 l_stmt_num := 60;
211 -- Pending shipping delivery transactions
212 -- This is either "must resolve" or "optional" depending on the client
213 -- extension introduced in ER 2342913.
214 BEGIN
215
216 l_stmt_num := 63;
217 SELECT period_start_date
218 INTO l_le_closing_fm_date
219 FROM org_acct_periods
220 WHERE acct_period_id = p_closing_period
221 AND organization_id = p_org_id;
222
223 l_stmt_num := 65;
224 l_in_rec_type.closing_fm_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
225 l_le_closing_fm_date,
226 l_legal_entity);
227
228 l_in_rec_type.api_version_number := 1.0;
229 l_in_rec_type.source_code := 'CST';
230 l_in_rec_type.closing_to_date := l_sched_close_date;
231 l_in_rec_type.ORGANIZATION_ID := p_org_id;
232
233 l_stmt_num := 67;
234 WSH_INTEGRATION.Get_Untrxd_Shpg_Lines_Count(
235 p_in_attributes => l_in_rec_type,
236 p_out_attributes => l_out_rec_type,
237 p_inout_attributes => l_io_rec_type,
238 x_return_status => l_return_status,
239 x_msg_count => l_msg_count,
240 x_msg_data => l_msg_data);
241
242 IF l_return_status <> FND_API.g_ret_sts_success THEN
243 x_pending_ship := -1;
244 END IF;
245
246 x_pending_ship := l_out_rec_type.untrxd_rec_count + l_out_rec_type.receiving_rec_count;
247
248 END;
249
250 l_stmt_num := 70;
251 -- Unprocessed receiving transactions (optional)
252 BEGIN
253 SELECT COUNT(*)
254 INTO l_tcount
255 FROM rcv_transactions_interface
256 WHERE to_organization_id = p_org_id
257 AND transaction_date <= l_sched_close_date
258 AND destination_type_code in ('INVENTORY','SHOP FLOOR');
259
260 x_pend_receiving := l_tcount;
261
262 EXCEPTION
263 when NO_DATA_FOUND then
264 x_pend_receiving := 0;
265 when OTHERS then
266 x_pend_receiving := -1;
267 END;
268
269 l_stmt_num := 80;
270 -- Pending material transactions (optional)
271 -- Need to ignore Ship Confirm Open Interface detail records.
272 -- these are stored in WSH_PICKING_DETAILS_INTERFACE, which is a view
273 -- on MTL_TRANSACTIONS_INTERFACE filtered by process_flag = 9
274 BEGIN
275
276 SELECT COUNT(*)
277 INTO l_tcount
278 FROM mtl_transactions_interface
279 WHERE organization_id = p_org_id
280 AND transaction_date <= l_sched_close_date
281 AND process_flag <> 9;
282
283 x_pend_matl := l_tcount;
284
285 EXCEPTION
286 when NO_DATA_FOUND then
287 x_pend_matl := 0;
288 when OTHERS then
289 x_pend_matl := -1;
290 END;
291
292 l_stmt_num := 90;
293 -- Pending shop floor move transactions (optional)
294 BEGIN
295 SELECT COUNT(*)
296 INTO l_tcount
297 FROM wip_move_txn_interface
298 WHERE organization_id = p_org_id
299 AND transaction_date <= l_sched_close_date;
300
301 x_pend_move := l_tcount;
302
303 EXCEPTION
304 when NO_DATA_FOUND then
305 x_pend_move := 0;
306 when OTHERS then
307 x_pend_move := -1;
308 END;
309
310 l_stmt_num := 100;
311 -- Released EAM work orders (optional)
312 BEGIN
313 SELECT NVL(eam_enabled_flag, 'N'), NVL(lcm_enabled_flag, 'N') /* Support for LCM */
314 INTO l_eam_enabled, l_lcm_enabled
315 FROM mtl_parameters
316 WHERE organization_id = p_org_id;
317
318 IF (l_eam_enabled = 'Y') THEN
319 SELECT count(*)
320 INTO l_tcount
321 FROM wip_discrete_jobs WDJ, wip_entities WE
322 WHERE WDJ.organization_id = p_org_id
323 AND WDJ.scheduled_completion_date <= p_sched_close_date
324 AND WDJ.status_type = 3 -- Released
325 AND WDJ.wip_entity_id = WE.wip_entity_id
326 AND WDJ.organization_id = WE.organization_id
327 AND WE.entity_type = 6; -- Maintenance Work Order
328 ELSE
329 l_tcount := 0;
330 END IF;
331
332 x_released_work_orders := l_tcount;
333
334 END;
335
336
337 /* Support for Landed Cost Management: Pending landed cost adjustment transactions */
338 IF l_lcm_enabled = 'Y' THEN
339
343 FROM cst_lc_adj_interface
340 l_stmt_num := 110;
341 SELECT COUNT(*)
342 INTO l_tcount
344 WHERE organization_id = p_org_id
345 AND transaction_date <= l_sched_close_date;
346
347 x_pending_lcm := l_tcount;
348
349 ELSE
350 x_pending_lcm := 0;
351
352 END IF;
353
354 EXCEPTION
355 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
356 ROLLBACK TO Get_PendingTcount_PUB;
357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
358 WHEN OTHERS THEN
359 ROLLBACK TO Get_PendingTcount_PUB;
360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
361 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
362 THEN
363 FND_MSG_PUB.Add_Exc_Msg(
364 p_pkg_name => G_PKG_NAME,
365 p_procedure_name => l_api_name,
366 p_error_text => l_stmt_num||SUBSTR(SQLERRM,1,235)
367 );
368 END IF;
369
370 END Get_PendingTcount;
371
372
373
374 PROCEDURE Open_Period(
375 p_api_version IN NUMBER,
376 p_org_id IN NUMBER,
377 p_user_id IN NUMBER,
378 p_login_id IN NUMBER,
379 p_acct_period_type IN VARCHAR2,
380 p_org_period_set_name IN VARCHAR2,
381 p_open_period_name IN VARCHAR2,
382 p_open_period_year IN NUMBER,
383 p_open_period_num IN NUMBER,
384 x_last_scheduled_close_date IN OUT NOCOPY DATE,
385 p_period_end_date IN DATE,
386 x_prior_period_open OUT NOCOPY BOOLEAN,
387 x_new_acct_period_id IN OUT NOCOPY NUMBER,
388 x_duplicate_open_period OUT NOCOPY BOOLEAN,
389 x_commit_complete OUT NOCOPY BOOLEAN,
390 x_return_status OUT NOCOPY VARCHAR2
391 ) IS
392
393 l_period_count INTEGER;
394 l_dummy_period_start DATE;
395 l_first_period INTEGER;
396 l_err_msg VARCHAR2(80);
397 l_indust VARCHAR2(10);
398 l_return_code NUMBER;
399 l_wip_installed BOOLEAN;
400 l_installation VARCHAR2(10);
401
402 -- Retrieve close date of last open period
403 CURSOR get_last_close_date IS
404 SELECT NVL(MAX(schedule_close_date), sysdate),
405 count(*)
406 FROM org_acct_periods
407 WHERE organization_id = p_org_id;
408
409 -- Check that there is no period prior to one we are trying to open
410 -- that is in GL_PERIODS but not open (i.e. not in ORG_ACCT_PERIODS)
411 -- A status of %NOTFOUND indicates it is okay to open the next period.
412 CURSOR check_prior_open_period IS
413 SELECT start_date
414 FROM gl_periods
415 WHERE end_date < p_period_end_date
416 AND end_date >= x_last_scheduled_close_date
417 AND (period_name, period_year) not in
418 (select period_name, period_year
419 from org_acct_periods
420 where organization_id = p_org_id)
421 AND period_type = p_acct_period_type
422 AND period_set_name = p_org_period_set_name
423 AND adjustment_period_flag = 'N';
424
425 -- Get next period id
426 CURSOR get_new_period_id IS
427 SELECT org_acct_periods_s.nextval
428 FROM sys.dual;
429
430 -- See if another open period process has already committed same data
431 CURSOR check_if_duplicating IS
432 SELECT period_start_date
433 FROM org_acct_periods
434 WHERE organization_id = p_org_id
435 AND period_name = p_open_period_name
436 AND period_year = p_open_period_year
437 AND period_num = p_open_period_num
438 AND acct_period_id <> x_new_acct_period_id;
439
440 --BUG#5903883
441 CURSOR c_org_acct_unique IS
442 SELECT NULL
443 FROM org_acct_periods
444 WHERE organization_id = p_org_id
445 AND period_year = p_open_period_year
446 AND period_name = p_open_period_name
447 AND period_num = p_open_period_num;
448
449 l_test VARCHAR2(1);
450
451
452
453 l_api_name CONSTANT VARCHAR2(30) := 'Open_Period';
454 l_api_version CONSTANT NUMBER := 1.0;
455 l_msg_level_threshold NUMBER;
456 l_stmt_num NUMBER := 0;
457
458 org_acct_periods_u2 EXCEPTION;
459 PRAGMA EXCEPTION_INIT(org_acct_periods_u2,-1);
460
461 BEGIN
462
463 -- Standard Start of API savepoint
464 SAVEPOINT Open_Period_PUB;
465
466 -- Check for call compatibility
467 IF NOT FND_API.Compatible_API_Call
468 ( p_current_version_number => l_api_version,
469 p_caller_version_number => p_api_version,
470 p_api_name => l_api_name,
471 p_pkg_name => G_PKG_NAME
472 )
473 THEN
474 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
475 END IF;
476
477 -- Check for message level threshold
478 l_msg_level_threshold := FND_PROFILE.Value('FND_AS_MSG_LEVEL_THRESHOLD');
479
480 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
481 THEN
482 FND_MSG_PUB.Add_Exc_Msg(
483 p_pkg_name => G_PKG_NAME,
484 p_procedure_name => l_api_name,
485 p_error_text => SUBSTR(
486 l_stmt_num||':'||
487 p_org_id||','||
488 p_user_id||','||
492 p_open_period_name||','||
489 p_login_id||','||
490 p_acct_period_type||','||
491 p_org_period_set_name||','||
493 p_open_period_year||','||
494 p_open_period_num||','||
495 p_period_end_date,
496 1,
497 240
498 )
499 );
500 END IF;
501
502 x_commit_complete := FALSE;
503 x_duplicate_open_period := FALSE;
504
505 l_stmt_num := 10;
506 -- Get the close date of the last open period, as well as
507 -- the period_id of the next period to open, unless this is
508 -- the first period opened
509 OPEN get_last_close_date;
510 FETCH get_last_close_date
511 INTO x_last_scheduled_close_date, l_period_count;
512
513 IF l_period_count = 0 THEN
514 l_first_period := 1;
515 ELSE
516 l_first_period := 0;
517 END IF;
518
519 CLOSE get_last_close_date;
520
521 l_stmt_num := 20;
522 -- Verify that the prior period is open
523 OPEN check_prior_open_period;
524 FETCH check_prior_open_period
525 INTO l_dummy_period_start;
526
527 IF check_prior_open_period%NOTFOUND THEN
528 x_prior_period_open := TRUE;
529 ELSE
530 x_prior_period_open := FALSE;
531 GOTO procedure_end;
532 END IF;
533 CLOSE check_prior_open_period;
534
535 l_stmt_num := 30;
536 -- Get the next available period_id for the new opened period
537 OPEN get_new_period_id;
538 FETCH get_new_period_id
539 INTO x_new_acct_period_id;
540
541 IF get_new_period_id%NOTFOUND THEN
542 x_new_acct_period_id := 0;
543 GOTO procedure_end;
544 END IF;
545
546 CLOSE get_new_period_id;
547
548
549
550
551 --{BUG#5903883
552 OPEN c_org_acct_unique;
553 FETCH c_org_acct_unique INTO l_test;
554 IF c_org_acct_unique%FOUND THEN
555 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_CHANGED');
556 FND_MSG_PUB.ADD;
557 x_return_status := FND_API.G_RET_STS_ERROR;
558 END IF;
559 CLOSE c_org_acct_unique;
560
561 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
562 RAISE FND_API.G_EXC_ERROR;
563 END IF;
564 --}
565
566
567
568 l_stmt_num := 40;
569 -- Insert record into org_acct_periods to open the period
570 INSERT INTO org_acct_periods
571 (acct_period_id,
572 organization_id,
573 last_update_date,
574 last_updated_by,
575 creation_date,
576 created_by,
577 period_set_name,
578 period_year,
579 period_num,
580 period_name,
581 description,
582 period_start_date,
583 schedule_close_date,
584 open_flag,
585 last_update_login)
586 SELECT
587 x_new_acct_period_id, -- acct_period_id
588 p_org_id, -- organization_id
589 SYSDATE, -- last_update_date
590 p_user_id, -- last_updated_by
591 SYSDATE, -- creation_date
592 p_user_id, -- created_by
593 GLP.period_set_name, -- period_set_name
594 GLP.period_year, -- period_year
595 GLP.period_num, -- period_num
596 GLP.period_name, -- period_name
597 GLP.description, -- description
598
599 -- period_start_date
600 decode(l_first_period,
601 1, GLP.start_date,
602 x_last_scheduled_close_date+1),
603
604 GLP.end_date, -- schedule_close_date
605 'Y', -- open_flag
606 -1 -- last_update_login
607 FROM gl_periods GLP
608 WHERE GLP.period_set_name = p_org_period_set_name
609 AND GLP.period_name = p_open_period_name
610 AND GLP.period_type = p_acct_period_type
611 AND GLP.adjustment_period_flag = 'N'
612 AND (GLP.period_name, GLP.period_year)
613 NOT IN
614 (SELECT period_name, period_year
615 FROM org_acct_periods
616 WHERE organization_id = p_org_id)
617 AND NOT EXISTS
618 (SELECT period_start_date
619 FROM org_acct_periods
620 WHERE organization_id = p_org_id
621 AND period_year = p_open_period_year
622 AND period_name = p_open_period_name
623 AND period_num = p_open_period_num);
624
625 l_stmt_num := 50;
626 -- Update WIP costing if WIP is installed
627
628 l_wip_installed := fnd_installation.get(appl_id => 706,
629 dep_appl_id => 706,
630 status => l_installation,
631 industry => l_indust);
632
633 IF (l_wip_installed) THEN
634 l_return_code := CSTPCWPB.WIPCBR( p_org_id,
635 p_user_id,
636 p_login_id,
637 x_new_acct_period_id,
638 l_err_msg);
639 ELSE
640 l_return_code := 0;
641 END IF;
642
643 IF (l_return_code <> 0) THEN
644 l_err_msg := l_return_code || l_err_msg;
645 GOTO error_label;
646 END IF;
647
648 l_stmt_num := 60;
649 -- Prior to commit, ensure that no one else has simultaneously tried to
650 -- open the period ...
651 -- Check if it already exists with a different period_id.
652
653 OPEN check_if_duplicating;
654 FETCH check_if_duplicating
655 INTO l_dummy_period_start;
656
657 IF check_if_duplicating%FOUND then
658 x_duplicate_open_period := TRUE;
659 GOTO error_label;
660 END IF;
661
662 l_stmt_num := 70;
663 -- Update last_schedule_close_date with newly opened period's
664 -- scheduled close date
665 SELECT NVL(MAX(schedule_close_date), SYSDATE)
666 INTO x_last_scheduled_close_date
667 FROM org_acct_periods
668 WHERE organization_id = p_org_id;
669
670 GOTO success_label;
671
672 <<error_label>>
673 ROLLBACK;
674 raise_application_error(-20010, sqlerrm||'---'||l_err_msg);
675 GOTO procedure_end;
676
677 <<success_label>>
678 COMMIT;
679 x_commit_complete := TRUE;
680 RETURN;
681
682 <<procedure_end>>
683 NULL;
684
685 EXCEPTION
686 WHEN org_acct_periods_u2 THEN
687 ROLLBACK TO Open_Period_PUB;
688 IF INSTRB(SQLERRM,'ORG_ACCT_PERIODS_U2') <> 0 THEN
689 x_return_status := FND_API.G_RET_STS_ERROR;
690 ELSE
691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
692 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
693 THEN
694 FND_MSG_PUB.Add_Exc_Msg(
695 p_pkg_name => G_PKG_NAME,
696 p_procedure_name => l_api_name,
697 p_error_text => l_stmt_num||SUBSTR(SQLERRM,1,235)
698 );
699 END IF;
700 END IF;
701
702 WHEN FND_API.G_EXC_ERROR THEN
703 --
704 -- Ensure the rollback is happening
705 --
706 ROLLBACK TO Open_Period_PUB;
707 x_return_status := FND_API.G_RET_STS_ERROR;
708
709 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
710 ROLLBACK TO Open_Period_PUB;
711 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
712
713 WHEN OTHERS THEN
714 ROLLBACK TO Open_Period_PUB;
715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
716 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
717 THEN
718 FND_MSG_PUB.Add_Exc_Msg(
719 p_pkg_name => G_PKG_NAME,
720 p_procedure_name => l_api_name,
721 p_error_text => l_stmt_num||SUBSTR(SQLERRM,1,235)
722 );
723 END IF;
724
725 END Open_Period;
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746 PROCEDURE Verify_PeriodClose(
747 p_api_version IN NUMBER,
748 p_org_id IN NUMBER,
749 p_closing_acct_period_id IN NUMBER,
750 p_closing_end_date IN DATE,
751 x_open_period_exists OUT NOCOPY BOOLEAN,
752 x_proper_order OUT NOCOPY BOOLEAN,
753 x_end_date_is_past OUT NOCOPY BOOLEAN,
754 x_download_in_process OUT NOCOPY BOOLEAN,
755 x_prompt_to_reclose OUT NOCOPY BOOLEAN,
756 x_return_status OUT NOCOPY VARCHAR2
757 ) IS
758
759 l_temp_id NUMBER;
760 l_le_sysdate DATE := NULL;
761 l_operating_unit NUMBER := 0;
762
763 -- Finds the earliest period that can be closed
764 CURSOR get_next_period_to_close IS
765 SELECT acct_period_id
766 FROM org_acct_periods
767 WHERE organization_id = p_org_id
768 AND schedule_close_date = (SELECT MIN(schedule_close_date)
769 FROM org_acct_periods
770 WHERE organization_id = p_org_id
771 AND (open_flag = 'Y' or open_flag = 'P'));
772
773 -- Finds the next period in org_acct_periods
774 CURSOR get_next_open_period IS
775 SELECT MIN(acct_period_id)
776 FROM org_acct_periods
777 WHERE organization_id = p_org_id
778 AND acct_period_id > p_closing_acct_period_id;
779
780 -- Checks if period is already in process of closing
781 CURSOR check_reclose_period IS
782 SELECT acct_period_id
783 FROM org_acct_periods
784 WHERE organization_id = p_org_id
785 AND acct_period_id = p_closing_acct_period_id
786 AND period_close_date IS NOT NULL
787 AND open_flag = 'P';
788
789 l_api_name CONSTANT VARCHAR2(30) := 'Verify_PeriodClose';
790 l_api_version CONSTANT NUMBER := 1.0;
791 l_msg_level_threshold NUMBER;
792 l_stmt_num NUMBER := 0;
793
794 BEGIN
795
796 -- Standard Start of API savepoint
797 SAVEPOINT Verify_PeriodClose_PUB;
798
799 -- Check for call compatibility
800 IF NOT FND_API.Compatible_API_Call
801 ( p_current_version_number => l_api_version,
802 p_caller_version_number => p_api_version,
803 p_api_name => l_api_name,
804 p_pkg_name => G_PKG_NAME
805 )
806 THEN
807 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
808 END IF;
809
810 -- Check for message level threshold
811 l_msg_level_threshold := FND_PROFILE.Value('FND_AS_MSG_LEVEL_THRESHOLD');
812
813 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
814 THEN
815 FND_MSG_PUB.Add_Exc_Msg(
816 p_pkg_name => G_PKG_NAME,
817 p_procedure_name => l_api_name,
818 p_error_text => SUBSTR(
819 l_stmt_num||':'||
820 p_org_id||','||
821 p_closing_acct_period_id||','||
822 p_closing_end_date,
823 1,
824 240
825 )
826 );
827 END IF;
828
829 x_download_in_process := FALSE;
830
831 l_stmt_num := 10;
832 -- Check that this is the next period to close
833 OPEN get_next_period_to_close;
834 FETCH get_next_period_to_close
835 INTO l_temp_id;
836
837 IF (l_temp_id = p_closing_acct_period_id) THEN
838 x_proper_order := TRUE;
839 ELSE
840 x_proper_order := FALSE;
841 GOTO procedure_end;
842 END IF;
843
844 CLOSE get_next_period_to_close;
845
846 l_stmt_num := 20;
847 -- Check that the next period is open
848 OPEN get_next_open_period;
849 FETCH get_next_open_period
850 INTO l_temp_id;
851
852 IF get_next_open_period%FOUND THEN
853 x_open_period_exists := TRUE;
854 ELSE
855 x_open_period_exists := FALSE;
856 GOTO procedure_end;
857 END IF;
858
859 CLOSE get_next_open_period;
860
861 -- Check that the period's end date is < today,
862 -- adjusting for LE timezone.
863 l_stmt_num := 30;
864 SELECT operating_unit
865 INTO l_operating_unit
866 FROM cst_acct_info_v
867 WHERE organization_id = p_org_id;
868
869 l_stmt_num := 40;
870 l_le_sysdate := INV_LE_TIMEZONE_PUB.GET_LE_SYSDATE_FOR_OU(
871 l_operating_unit);
872
873 l_stmt_num := 50;
874 IF (p_closing_end_date < l_le_sysdate) THEN
875 x_end_date_is_past := TRUE;
876 ELSE
877 x_end_date_is_past := FALSE;
878 GOTO procedure_end;
879 END IF;
880
881 l_stmt_num := 60;
882 -- See if this period is already processing. If so, prompt to reclose.
883 x_prompt_to_reclose := FALSE;
884
885 OPEN check_reclose_period;
886 FETCH check_reclose_period
887 INTO l_temp_id;
888
889 IF check_reclose_period%FOUND THEN
890 x_prompt_to_reclose := TRUE;
891 ELSE
892 x_prompt_to_reclose := FALSE;
893 END IF;
894
895 CLOSE check_reclose_period;
896
897 <<procedure_end >>
898 NULL;
899
900 EXCEPTION
901 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
902 ROLLBACK TO Verify_PeriodClose_PUB;
903 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
904 WHEN OTHERS THEN
905 ROLLBACK TO Verify_PeriodClose_PUB;
906 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
907 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
908 THEN
909 FND_MSG_PUB.Add_Exc_Msg(
910 p_pkg_name => G_PKG_NAME,
911 p_procedure_name => l_api_name,
912 p_error_text => l_stmt_num||SUBSTR(SQLERRM,1,235)
913 );
914 END IF;
915
916 END Verify_PeriodClose;
917
918 PROCEDURE Close_Period(
919 p_api_version IN NUMBER,
920 p_org_id IN NUMBER,
921 p_user_id IN NUMBER,
922 p_login_id IN NUMBER,
923 p_closing_acct_period_id IN NUMBER,
924 x_wip_failed IN OUT NOCOPY BOOLEAN,
925 x_close_failed OUT NOCOPY BOOLEAN,
926 x_req_id IN OUT NOCOPY NUMBER,
927 x_unprocessed_txns OUT NOCOPY BOOLEAN,
928 x_rec_rpt_launch_failed OUT NOCOPY BOOLEAN,
929 x_return_status OUT NOCOPY VARCHAR2
930 ) IS
931
932 l_err_msg VARCHAR2(80);
933 l_indust VARCHAR2(10);
934 l_wip_installed BOOLEAN;
935 l_installation VARCHAR2(10);
936 l_return_code NUMBER;
937
938 l_api_name CONSTANT VARCHAR2(30) := 'Close_Period';
939 l_api_version CONSTANT NUMBER := 1.0;
940 l_msg_level_threshold NUMBER;
941 l_stmt_num NUMBER := 0;
942
943 l_message VARCHAR2(255);
944
945 l_rep_type NUMBER := 0;
946 l_currency_code VARCHAR2(15);
947 COULD_NOT_LAUNCH_REC_RPT EXCEPTION;
948
949 l_sched_close_date DATE;
950 l_period_start_date DATE;
951 l_legal_entity NUMBER;
952 l_count NUMBER;
953 l_unprocessed_table VARCHAR2(30);
954 UNPROCESSED_TXNS_EXIST EXCEPTION;
955
956 BEGIN
957
958 -- Standard Start of API savepoint
959 SAVEPOINT Close_Period_PUB;
960
961 -- Check for call compatibility
962 IF NOT FND_API.Compatible_API_Call
963 ( p_current_version_number => l_api_version,
964 p_caller_version_number => p_api_version,
965 p_api_name => l_api_name,
966 p_pkg_name => G_PKG_NAME
967 )
968 THEN
969 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
970 END IF;
971
972 l_stmt_num := 10;
973 -- Check for message level threshold
974 l_msg_level_threshold := FND_PROFILE.Value('FND_AS_MSG_LEVEL_THRESHOLD');
975
976 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
977 THEN
978 FND_MSG_PUB.Add_Exc_Msg(
979 p_pkg_name => G_PKG_NAME,
980 p_procedure_name => l_api_name,
981 p_error_text => SUBSTR(
982 l_stmt_num||':'||
983 p_org_id||','||
984 p_user_id||','||
985 p_login_id||','||
986 p_closing_acct_period_id,
987 1,
988 240
989 )
990 );
991 END IF;
992
993 l_stmt_num := 20;
994 -- Update period status to processing
995 UPDATE org_acct_periods
996 SET
997 open_flag = 'P',
998 period_close_date = trunc(sysdate),
999 last_update_date = sysdate, --trunc(sysdate),
1000 last_updated_by = p_user_id,
1001 last_update_login = p_login_id
1002 WHERE acct_period_id = p_closing_acct_period_id
1003 AND organization_id = p_org_id
1004 -- program level check to make sure that
1005 -- the period is only closed once
1006 AND open_flag = 'Y';
1007
1008 IF (SQL%NOTFOUND) THEN
1009 RAISE NO_DATA_FOUND;
1010 END IF;
1011
1012 l_stmt_num := 30;
1013 -- Update WIP costing if WIP is installed
1014 l_wip_installed := fnd_installation.get(appl_id => 706,
1015 dep_appl_id => 706,
1016 status => l_installation,
1017 industry => l_indust);
1018
1019 l_stmt_num := 40;
1020 IF (l_wip_installed) THEN
1021 l_return_code := CSTPWPVR.REPVAR(
1022 p_org_id,
1023 p_closing_acct_period_id,
1024 p_user_id,
1025 p_login_id,
1026 l_err_msg
1027 );
1028 END IF;
1029
1030 IF (l_wip_installed AND l_return_code <> 0) THEN
1031 x_wip_failed := TRUE;
1032 GOTO error_label;
1033 ELSE
1034 x_wip_failed := FALSE;
1035 END IF;
1036
1037 l_stmt_num := 50;
1038 SELECT period_start_date, schedule_close_date
1039 INTO l_period_start_date, l_sched_close_date
1040 FROM org_acct_periods
1041 WHERE acct_period_id = p_closing_acct_period_id
1042 AND organization_id = p_org_id;
1043
1044 l_stmt_num := 60;
1045 SELECT legal_entity
1046 INTO l_legal_entity
1047 FROM cst_acct_info_v
1048 WHERE organization_id = p_org_id;
1049
1050 l_stmt_num := 70;
1051 l_period_start_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
1052 l_period_start_date,
1053 l_legal_entity
1054 );
1055
1056 l_stmt_num := 80;
1057 l_sched_close_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
1058 l_sched_close_date,
1059 l_legal_entity
1060 );
1061
1062 l_sched_close_date := l_sched_close_date + 1;
1063
1064 l_stmt_num := 90;
1068 FROM mtl_material_transactions_temp
1065 -- check if there are unprocessed transactions in MMTT/MMT/WCTI
1066 SELECT COUNT(*)
1067 INTO l_count
1069 WHERE organization_id = p_org_id
1070 AND transaction_date < l_sched_close_date
1071 AND NVL(transaction_status,0) <> 2
1072 AND rownum = 1; -- transaction_status = 2 indicates a save-only status
1073
1074 IF l_count <> 0 THEN
1075 l_unprocessed_table := 'MTL_MATERIAL_TRANSACTIONS_TEMP';
1076 RAISE UNPROCESSED_TXNS_EXIST;
1077 END IF;
1078
1079 l_stmt_num := 100;
1080 SELECT /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */
1081 COUNT(*)
1082 INTO l_count
1083 FROM mtl_material_transactions MMT
1084 WHERE organization_id = p_org_id
1085 AND transaction_date < l_sched_close_date
1086 AND costed_flag IN('N','E')-- bug 13054482 Changed the not null condition, as it causes a index full scan
1087 AND rownum = 1;
1088
1089 IF l_count <> 0 THEN
1090 l_unprocessed_table := 'MTL_MATERIAL_TRANSACTIONS';
1091 RAISE UNPROCESSED_TXNS_EXIST;
1092 END IF;
1093
1094 l_stmt_num := 110;
1095 SELECT COUNT(*)
1096 INTO l_count
1097 FROM wip_cost_txn_interface
1098 WHERE organization_id = p_org_id
1099 AND transaction_date < l_sched_close_date
1100 AND rownum = 1;
1101
1102 IF l_count <> 0 THEN
1103 l_unprocessed_table := 'WIP_COST_TXN_INTERFACE';
1104 RAISE UNPROCESSED_TXNS_EXIST;
1105 END IF;
1106
1107 l_stmt_num := 120;
1108 SELECT COUNT(*)
1109 INTO l_count
1110 FROM wsm_split_merge_transactions
1111 WHERE organization_id = p_org_id
1112 AND costed <> wip_constants.completed
1113 AND transaction_date < l_sched_close_date
1114 AND rownum = 1;
1115
1116 IF l_count <> 0 THEN
1117 l_unprocessed_table := 'WSM_SPLIT_MERGE_TRANSACTIONS';
1118 RAISE UNPROCESSED_TXNS_EXIST;
1119 END IF;
1120
1121 l_stmt_num := 130;
1122 SELECT COUNT(*)
1123 INTO l_count
1124 FROM wsm_split_merge_txn_interface
1125 WHERE organization_id = p_org_id
1126 AND process_status <> wip_constants.completed
1127 AND transaction_date < l_sched_close_date
1128 AND rownum = 1;
1129
1130 IF l_count <> 0 THEN
1131 l_unprocessed_table := 'WSM_SPLIT_MERGE_TXN_INTERFACE';
1132 RAISE UNPROCESSED_TXNS_EXIST;
1133 END IF;
1134
1135 l_stmt_num := 140;
1136 UPDATE org_acct_periods
1137 SET summarized_flag = 'N',
1138 open_flag = 'N'
1139 WHERE organization_id = p_org_id
1140 AND acct_period_id = p_closing_acct_period_id;
1141
1142 -- if x_req_id remains at -1 then we did not attempt to launch CSTRPCRE
1143 x_req_id := -1;
1144
1145 IF (FND_PROFILE.VALUE('CST_PERIOD_SUMMARY') = '1') THEN
1146
1147 l_stmt_num := 150;
1148 SELECT ML.lookup_code
1149 INTO l_rep_type
1150 FROM mfg_lookups ML,
1151 mtl_parameters MP
1152 WHERE MP.organization_id = p_org_id
1153 AND ML.lookup_type = 'CST_PER_CLOSE_REP_TYPE'
1154 AND ML.lookup_code =
1155 DECODE(MP.primary_cost_method,
1156 1,DECODE(
1157 MP.wms_enabled_flag,
1158 'Y',1,
1159 DECODE(
1160 MP.cost_group_accounting,
1161 1,DECODE(
1162 MP.project_reference_enabled,
1163 1,1,
1164 2
1165 ),
1166 2
1167 )
1168 ),
1169 1
1170 );
1171
1172 l_stmt_num := 160;
1173 SELECT GL.currency_code
1174 INTO l_currency_code
1175 FROM hr_organization_information HOI,
1176 gl_ledgers GL
1177 WHERE HOI.organization_id = p_org_id
1178 AND HOI.org_information_context = 'Accounting Information'
1179 AND TO_NUMBER(HOI.org_information1) = GL.ledger_id;
1180
1181 l_stmt_num := 170;
1182 -- Launch reconciliation report
1183 x_req_id := FND_REQUEST.submit_request(
1184 application => 'BOM',
1185 program => 'CSTRPCRE',
1186 description => NULL,
1187 start_time => NULL,
1188 sub_request => FALSE,
1189 argument1 => p_org_id,
1190 argument2 => FND_PROFILE.VALUE('MFG_CHART_OF_ACCOUNTS_ID'),
1191 argument3 => l_rep_type,
1192 argument4 => 1,
1193 argument5 => p_closing_acct_period_id,
1194 argument6 => NULL,
1195 argument7 => NULL,
1196 argument8 => NULL,
1197 argument9 => NULL,
1198 argument10 => NULL,
1199 argument11 => NULL,
1200 argument12 => NULL,
1201 argument13 => l_currency_code,
1202 argument14 => FND_PROFILE.VALUE('DISPLAY_INVERSE_RATE'),
1203 argument15 => 2,
1204 argument16 => 1);
1205
1206 IF x_req_id = 0 THEN
1207 RAISE COULD_NOT_LAUNCH_REC_RPT;
1208 END IF;
1209 END IF;
1210
1211 GOTO procedure_end;
1212
1213 <<error_label>>
1214 ROLLBACK TO Close_Period_PUB;
1215 IF (x_wip_failed) THEN
1216 raise_application_error(-20000, l_err_msg);
1217 END IF;
1218
1222 EXCEPTION
1219 <<procedure_end>>
1220 COMMIT;
1221
1223 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1224 ROLLBACK TO Close_Period_PUB;
1225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1226 WHEN NO_DATA_FOUND THEN
1227 ROLLBACK TO Close_Period_PUB;
1228 x_close_failed := TRUE;
1229 WHEN UNPROCESSED_TXNS_EXIST THEN
1230 ROLLBACK TO Close_Period_PUB;
1231 UPDATE org_acct_periods
1232 SET open_flag = 'N'
1233 WHERE organization_id = p_org_id
1234 AND acct_period_id = p_closing_acct_period_id;
1235 x_unprocessed_txns := TRUE;
1236 WHEN COULD_NOT_LAUNCH_REC_RPT THEN
1237 COMMIT;
1238 x_rec_rpt_launch_failed := TRUE;
1239 WHEN OTHERS THEN
1240 ROLLBACK TO Close_Period_PUB;
1241 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1242 raise_application_error(-20000, 'statement ' || l_stmt_num || ':' || SQLERRM);
1243 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1244 THEN
1245 FND_MSG_PUB.Add_Exc_Msg(
1246 p_pkg_name => G_PKG_NAME,
1247 p_procedure_name => l_api_name,
1248 p_error_text => l_stmt_num||SUBSTR(SQLERRM,1,235)
1249 );
1250 END IF;
1251
1252 END Close_Period;
1253
1254 PROCEDURE Update_EndDate(
1255 p_api_version IN NUMBER,
1256 p_org_id IN NUMBER,
1257 p_new_end_date IN DATE,
1258 p_changed_acct_period_id IN NUMBER,
1259 p_user_id IN NUMBER,
1260 p_login_id IN NUMBER,
1261 x_period_order OUT NOCOPY BOOLEAN,
1262 x_update_failed OUT NOCOPY BOOLEAN,
1263 x_return_status OUT NOCOPY VARCHAR2
1264 ) IS
1265
1266 l_next_periods_enddate DATE;
1267 l_prior_periods_enddate DATE;
1268
1269 CURSOR get_prior_periods_enddate IS
1270 SELECT NVL(MAX(schedule_close_date), p_new_end_date - 1)
1271 FROM org_acct_periods
1272 WHERE organization_id = p_org_id
1273 AND acct_period_id < p_changed_acct_period_id;
1274
1275 CURSOR get_next_periods_enddate IS
1276 SELECT NVL(MIN(schedule_close_date), p_new_end_date + 1)
1277 FROM org_acct_periods
1278 WHERE organization_id = p_org_id
1279 AND acct_period_id > p_changed_acct_period_id;
1280
1281 l_api_name CONSTANT VARCHAR2(30) := 'Update_EndDate';
1282 l_api_version CONSTANT NUMBER := 1.0;
1283 l_msg_level_threshold NUMBER;
1284 l_stmt_num NUMBER := 0;
1285
1286 BEGIN
1287
1288 -- Standard Start of API savepoint
1289 SAVEPOINT Update_EndDate_PUB;
1290
1291 -- Check for call compatibility
1292 IF NOT FND_API.Compatible_API_Call
1293 ( p_current_version_number => l_api_version,
1294 p_caller_version_number => p_api_version,
1295 p_api_name => l_api_name,
1296 p_pkg_name => G_PKG_NAME
1297 )
1298 THEN
1299 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1300 END IF;
1301
1302 -- Check for message level threshold
1303 l_msg_level_threshold := FND_PROFILE.Value('FND_AS_MSG_LEVEL_THRESHOLD');
1304
1305 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
1306 THEN
1307 FND_MSG_PUB.Add_Exc_Msg(
1308 p_pkg_name => G_PKG_NAME,
1309 p_procedure_name => l_api_name,
1310 p_error_text => SUBSTR(
1311 l_stmt_num||':'||
1312 p_org_id||','||
1313 p_new_end_date||','||
1314 p_changed_acct_period_id||','||
1315 p_user_id||','||
1316 p_login_id,
1317 1,
1318 240
1319 )
1320 );
1321 END IF;
1322
1323 l_stmt_num := 10;
1324 -- Verify that new end date is after prior period's end date
1325 OPEN get_prior_periods_enddate;
1326 FETCH get_prior_periods_enddate
1327 INTO l_prior_periods_enddate;
1328
1329 IF get_prior_periods_enddate%NOTFOUND THEN
1330 GOTO exception_label;
1331 END IF;
1332
1333 CLOSE get_prior_periods_enddate;
1334
1335 l_stmt_num := 20;
1336 -- Verify that new end date is before following period's end date
1337 OPEN get_next_periods_enddate;
1338 FETCH get_next_periods_enddate
1339 INTO l_next_periods_enddate;
1340
1341 IF get_next_periods_enddate%NOTFOUND THEN
1342 GOTO exception_label;
1343 END IF;
1344
1345 CLOSE get_next_periods_enddate;
1346
1347 l_stmt_num := 30;
1348 IF ((p_new_end_date <= l_prior_periods_enddate) OR
1349 (p_new_end_date >= l_next_periods_enddate)) THEN
1350
1351 x_period_order := FALSE;
1352
1353 ELSE
1354
1355 x_period_order := TRUE;
1356
1357 -- Update end date for this period
1358 UPDATE org_acct_periods
1359 SET schedule_close_date = p_new_end_date,
1360 last_update_date = sysdate, --trunc(SYSDATE),
1361 last_updated_by = p_user_id,
1362 last_update_login = p_login_id
1363 WHERE organization_id = p_org_id
1364 AND acct_period_id = p_changed_acct_period_id;
1365
1366 -- Update start date for next period
1367 UPDATE org_acct_periods
1368 SET period_start_date = p_new_end_date + 1,
1369 last_update_date = sysdate, --trunc(SYSDATE),
1370 last_updated_by = p_user_id,
1371 last_update_login = p_login_id
1375 FROM org_acct_periods
1372 WHERE organization_id = p_org_id
1373 AND acct_period_id =
1374 (SELECT MIN(acct_period_id)
1376 WHERE acct_period_id > p_changed_acct_period_id
1377 AND organization_id = p_org_id);
1378
1379 END IF;
1380
1381 x_update_failed := FALSE;
1382
1383 COMMIT;
1384 GOTO procedure_end;
1385
1386 <<exception_label>>
1387 x_update_failed := TRUE;
1388
1389 <<procedure_end>>
1390 NULL;
1391
1392 EXCEPTION
1393 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1394 ROLLBACK TO Update_EndDate_PUB;
1395 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1396 WHEN OTHERS THEN
1397 ROLLBACK TO Update_EndDate_PUB;
1398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1399 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1400 THEN
1401 FND_MSG_PUB.Add_Exc_Msg(
1402 p_pkg_name => G_PKG_NAME,
1403 p_procedure_name => l_api_name,
1404 p_error_text => l_stmt_num||SUBSTR(SQLERRM,1,235)
1405 );
1406 END IF;
1407 x_update_failed := TRUE;
1408
1409 END Update_EndDate;
1410
1411 PROCEDURE Revert_PeriodStatus(
1412 p_api_version IN NUMBER,
1413 p_org_id IN NUMBER,
1414 x_acct_period_id IN NUMBER,
1415 x_revert_complete OUT NOCOPY BOOLEAN,
1416 x_return_status OUT NOCOPY VARCHAR2
1417 ) IS
1418
1419 l_api_name CONSTANT VARCHAR2(30) := 'Revert_PeriodStatus';
1420 l_api_version CONSTANT NUMBER := 1.0;
1421 l_msg_level_threshold NUMBER;
1422 l_stmt_num NUMBER := 0;
1423
1424 BEGIN
1425
1426 -- Standard Start of API savepoint
1427 SAVEPOINT Revert_PeriodStatus_PUB;
1428
1429 -- Check for call compatibility
1430 IF NOT FND_API.Compatible_API_Call
1431 ( p_current_version_number => l_api_version,
1432 p_caller_version_number => p_api_version,
1433 p_api_name => l_api_name,
1434 p_pkg_name => G_PKG_NAME
1435 )
1436 THEN
1437 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1438 END IF;
1439
1440 -- Check for message level threshold
1441 l_msg_level_threshold := FND_PROFILE.Value('FND_AS_MSG_LEVEL_THRESHOLD');
1442
1443 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
1444 THEN
1445 FND_MSG_PUB.Add_Exc_Msg(
1446 p_pkg_name => G_PKG_NAME,
1447 p_procedure_name => l_api_name,
1448 p_error_text => SUBSTR(
1449 l_stmt_num||':'||
1450 p_org_id,
1451 1,
1452 240
1453 )
1454 );
1455 END IF;
1456
1457 l_stmt_num := 10;
1458 DELETE FROM org_acct_periods
1459 WHERE organization_id = p_org_id
1460 AND acct_period_id = x_acct_period_id;
1461
1462 COMMIT;
1463 x_revert_complete := TRUE;
1464
1465 EXCEPTION
1466 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1467 ROLLBACK TO Revert_PeriodStatus_PUB;
1468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1469 WHEN OTHERS THEN
1470 ROLLBACK TO Revert_PeriodStatus_PUB;
1471 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1472 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1473 THEN
1474 FND_MSG_PUB.Add_Exc_Msg(
1475 p_pkg_name => G_PKG_NAME,
1476 p_procedure_name => l_api_name,
1477 p_error_text => l_stmt_num||SUBSTR(SQLERRM,1,235)
1478 );
1479 END IF;
1480 x_revert_complete := FALSE;
1481
1482 END Revert_PeriodStatus;
1483
1484 PROCEDURE Summarize_Period(
1485 p_api_version IN NUMBER,
1486 p_org_id IN NUMBER,
1487 p_period_id IN NUMBER,
1488 p_to_date IN DATE,
1489 p_user_id IN NUMBER,
1490 p_login_id IN NUMBER,
1491 p_simulation IN NUMBER,
1492 x_return_status OUT NOCOPY VARCHAR2,
1493 x_msg_data OUT NOCOPY VARCHAR2
1494 ) IS
1495
1496 l_legal_entity NUMBER := 0;
1497 l_le_to_date DATE := NULL;
1498 l_to_date DATE := NULL;
1499 l_le_period_start_date DATE := NULL;
1500 l_period_start_date DATE := NULL;
1501 l_le_prior_end_date DATE := NULL;
1502 l_prior_end_date DATE := NULL;
1503 l_resummarize NUMBER := 0;
1504 l_prior_period_id NUMBER := 0;
1505 l_prev_summary NUMBER := 0;
1506 l_cpcs_count NUMBER := 0;
1507 l_current_period_closed NUMBER := 0;
1508 l_category_set_id NUMBER := 0;
1509 l_cost_method NUMBER := 0;
1510 /* Bug 14036099 */
1511 l_zero_val_hook NUMBER := 0;
1512 l_return_status VARCHAR2(1) := '0';
1513
1514 l_api_name CONSTANT VARCHAR2(30) := 'Summarize_Period';
1515 l_api_version CONSTANT NUMBER := 1.0;
1516 l_msg_level_threshold NUMBER;
1517 l_stmt_num NUMBER := 0;
1518 l_msg_count NUMBER := 0;
1519 l_msg_data VARCHAR2(2000);
1520 /* Bug 14036099 */
1521 l_err_num NUMBER := 0;
1522 l_err_code VARCHAR2(240) := '';
1523 l_err_msg VARCHAR2(8000) := '';
1524
1525 NO_PREV_SUMMARY_EXISTS EXCEPTION;
1526 ZERO_VAL_SUMMARY_OPTION_ERROR EXCEPTION;
1527 BEGIN
1528
1529 -- Standard Start of API savepoint
1530 SAVEPOINT Summarize_Period_PUB;
1531
1532 -- Check for call compatibility
1533 IF NOT FND_API.Compatible_API_Call
1534 ( p_current_version_number => l_api_version,
1538 )
1535 p_caller_version_number => p_api_version,
1536 p_api_name => l_api_name,
1537 p_pkg_name => G_PKG_NAME
1539 THEN
1540 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1541 END IF;
1542
1543 -- Check for message level threshold
1544 l_msg_level_threshold := FND_PROFILE.Value('FND_AS_MSG_LEVEL_THRESHOLD');
1545
1546 FND_MSG_PUB.Initialize;
1547
1548 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
1549 THEN
1550 FND_MSG_PUB.Add_Exc_Msg(
1551 p_pkg_name => G_PKG_NAME,
1552 p_procedure_name => l_api_name,
1553 p_error_text => SUBSTR(
1554 l_stmt_num||':'||
1555 p_org_id||','||
1556 p_period_id||','||
1557 p_to_date||','||
1558 p_user_id||','||
1559 p_login_id,
1560 1,
1561 240
1562 )
1563 );
1564 END IF;
1565
1566 l_stmt_num := 5;
1567
1568 SELECT legal_entity
1569 INTO l_legal_entity
1570 FROM cst_acct_info_v
1571 WHERE organization_id = p_org_id;
1572
1573 l_stmt_num := 7;
1574 SELECT period_start_date, schedule_close_date
1575 INTO l_le_period_start_date, l_le_to_date
1576 FROM org_acct_periods
1577 WHERE organization_id = p_org_id
1578 AND acct_period_id = p_period_id;
1579
1580 l_stmt_num := 8;
1581 l_period_start_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
1582 l_le_period_start_date,
1583 l_legal_entity);
1584
1585 l_stmt_num := 10;
1586 IF p_to_date IS NULL THEN
1587 l_to_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
1588 l_le_to_date,
1589 l_legal_entity);
1590 ELSE
1591 l_to_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
1592 p_to_date,
1593 l_legal_entity);
1594 END IF;
1595
1596 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1597 THEN
1598 FND_MSG_PUB.Add_Exc_Msg(
1599 p_pkg_name => G_PKG_NAME,
1600 p_procedure_name => l_api_name,
1601 p_error_text => l_stmt_num||': Using start date of ' || to_char(l_period_start_date,'DD-MON-YYYY HH24:MI:SS')
1602 );
1603 FND_MSG_PUB.Add_Exc_Msg(
1604 p_pkg_name => G_PKG_NAME,
1605 p_procedure_name => l_api_name,
1606 p_error_text => l_stmt_num||': Using to date of ' || to_char(l_to_date,'DD-MON-YYYY HH24:MI:SS')
1607 );
1608 END IF;
1609
1610 /* Bug 14036099 */
1611 l_stmt_num := 5;
1612 l_zero_val_hook := CST_Common_hooks.get_prd_cls_zeroval_option(
1613 i_org_id => p_org_id,
1614 i_acct_period_id => p_period_id,
1615 o_err_num => l_err_num,
1616 o_err_code => l_err_code,
1617 o_err_msg => l_err_msg
1618 );
1619
1620 IF (l_zero_val_hook <> 0 AND l_zero_val_hook <> 1) OR (l_err_num <> 0) THEN
1621
1622 IF l_err_num = 0 THEN
1623 l_err_msg := 'Invalid return value from hook: ' || l_zero_val_hook;
1624 END IF;
1625
1626 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1627 THEN
1628 FND_MSG_PUB.Add_Exc_Msg(
1629 p_pkg_name => G_PKG_NAME,
1630 p_procedure_name => l_api_name,
1631 p_error_text => l_stmt_num||': Error in CST_Common_hooks.get_prd_cls_zeroval_option: ' || l_err_msg || '(' || l_err_code || ')'
1632 );
1633 END IF;
1634
1635 RAISE ZERO_VAL_SUMMARY_OPTION_ERROR;
1636
1637 END IF;
1638
1639 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1640 THEN
1641 FND_MSG_PUB.Add_Exc_Msg(
1642 p_pkg_name => G_PKG_NAME,
1643 p_procedure_name => l_api_name,
1644 p_error_text => l_stmt_num||': Period Close Summarization option for zero value items is: ' || l_zero_val_hook
1645 );
1646 END IF;
1647
1648 l_stmt_num := 20;
1649 --find id of the previous period
1650 SELECT MAX(acct_period_id)
1651 INTO l_prior_period_id
1652 FROM org_acct_periods
1653 WHERE organization_id = p_org_id
1654 AND acct_period_id < p_period_id;
1655
1656 l_stmt_num := 30;
1657 --if summarized_flag in org_acct_periods is 'N' and data exists in CPCS
1658 --for the same period, delete the rows from CPCS.
1659
1660 SELECT count(*)
1661 INTO l_resummarize
1662 FROM org_acct_periods
1663 WHERE organization_id = p_org_id
1664 AND acct_period_id = p_period_id
1665 AND summarized_flag = 'N'
1666 AND EXISTS
1667 (SELECT 'Data exists in CPCS'
1668 FROM cst_period_close_summary
1669 WHERE organization_id = p_org_id
1670 AND acct_period_id = p_period_id);
1671
1672 IF (l_resummarize > 0) THEN
1673
1674 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1675 THEN
1676 FND_MSG_PUB.Add_Exc_Msg(
1677 p_pkg_name => G_PKG_NAME,
1678 p_procedure_name => l_api_name,
1679 p_error_text => l_stmt_num||': Resummarizing: data exists in CPCS for org/period '
1680 ||p_org_id || '/' || p_period_id
1681 );
1682 END IF;
1683
1684 l_stmt_num := 35;
1685
1686 DELETE cst_period_close_summary
1687 WHERE organization_id = p_org_id
1691 for all succeeding periods */
1688 AND acct_period_id >= p_period_id;
1689
1690 /* Updating org_acct_periods in case the customer has not updated summarized_flag
1692 l_stmt_num := 37;
1693 UPDATE org_acct_periods
1694 SET summarized_flag = 'N'
1695 WHERE organization_id = p_org_id
1696 AND acct_period_id >= p_period_id
1697 AND summarized_flag = 'Y';
1698 END IF;
1699
1700 l_stmt_num := 40;
1701 --check if previous period is summarized
1702 SELECT count(*)
1703 INTO l_prev_summary
1704 FROM org_acct_periods
1705 WHERE organization_id = p_org_id
1706 AND acct_period_id = l_prior_period_id
1707 AND summarized_flag = 'Y';
1708
1709 --check if CPCS is empty
1710 SELECT count(*)
1711 INTO l_cpcs_count
1712 FROM cst_period_close_summary
1713 WHERE organization_id = p_org_id
1714 AND rownum = 1;
1715
1716 l_stmt_num := 45;
1717 --find default category set
1718 SELECT category_set_id
1719 INTO l_category_set_id
1720 FROM mtl_default_category_sets
1721 WHERE functional_area_id = 5; -- Costing functional area
1722
1723 IF (l_cpcs_count = 0) THEN
1724
1725 l_stmt_num := 50;
1726 --find date to rollback to for initialization
1727
1728 SELECT NVL(OAP1.schedule_close_date+1-(1/(24*3600)),
1729 OAP2.period_start_date-(1/(24*3600)))
1730 INTO l_le_prior_end_date
1731 FROM org_acct_periods OAP1,
1732 org_acct_periods OAP2
1733 WHERE OAP1.organization_id(+) = OAP2.organization_id
1734 AND OAP1.acct_period_id(+) = l_prior_period_id
1735 AND OAP2.organization_id = p_org_id
1736 AND OAP2.acct_period_id = p_period_id;
1737
1738 l_stmt_num := 51;
1739 l_prior_end_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
1740 l_le_prior_end_date,
1741 l_legal_entity);
1742
1743 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1744 THEN
1745 FND_MSG_PUB.Add_Exc_Msg(
1746 p_pkg_name => G_PKG_NAME,
1747 p_procedure_name => l_api_name,
1748 p_error_text => l_stmt_num||': Initializing new summary history in CPCS for org '
1749 ||p_org_id|| ', rolling back to ' || to_char(l_prior_end_date,'DD-MON-YYYY HH24:MI:SS')
1750 );
1751 END IF;
1752
1753 l_stmt_num := 52;
1754 CST_Inventory_PUB.Calculate_InventoryValue(
1755 p_api_version => 1.0,
1756 p_init_msg_list => CST_Utility_PUB.Get_False,
1757 p_organization_id => p_org_id,
1758 p_onhand_value => 1,
1759 p_intransit_value => 1,
1760 p_receiving_value => 0,
1761 p_valuation_date => l_prior_end_date,
1762 p_cost_type_id => NULL,
1763 p_item_from => NULL,
1764 p_item_to => NULL,
1765 p_category_set_id => l_category_set_id,
1766 p_category_from => NULL,
1767 p_category_to => NULL,
1768 p_cost_group_from => NULL,
1769 p_cost_group_to => NULL,
1770 p_subinventory_from => NULL,
1771 p_subinventory_to => NULL,
1772 p_qty_by_revision => NULL,
1773 p_zero_cost_only => NULL,
1774 p_zero_qty => NULL,
1775 p_expense_item => NULL,
1776 p_expense_sub => NULL,
1777 p_unvalued_txns => 0,
1778 p_receipt => 1,
1779 p_shipment => 1,
1780 x_return_status => l_return_status,
1781 x_msg_count => l_msg_count,
1782 x_msg_data => l_msg_data
1783 );
1784
1785 l_stmt_num := 54;
1786 INSERT
1787 INTO cst_per_close_summary_temp(
1788 cost_group_id,
1789 subinventory_code,
1790 inventory_item_id,
1791 accounted_value,
1792 rollback_value,
1793 rollback_qty,
1794 rollback_onhand_value,
1795 rollback_intransit_value)
1796 SELECT CIQT.cost_group_id,
1797 CIQT.subinventory_code,
1798 CIQT.inventory_item_id,
1799 0 accounted_value,
1800 SUM(NVL(CIQT.rollback_qty,0))*NVL(CICT.item_cost,0) rollback_value,
1801 SUM(NVL(CIQT.rollback_qty,0)),
1802 SUM(DECODE(CIQT.qty_source,
1803 3,NVL(CIQT.rollback_qty,0),
1804 4,NVL(CIQT.rollback_qty,0),
1805 5,NVL(CIQT.rollback_qty,0),
1806 0))*NVL(CICT.item_cost,0) rollback_onhand_value,
1807 SUM(DECODE(CIQT.qty_source,
1808 6,NVL(CIQT.rollback_qty,0),
1809 7,NVL(CIQT.rollback_qty,0),
1810 8,NVL(CIQT.rollback_qty,0),
1811 0))*NVL(CICT.item_cost,0) rollback_intransit_value
1812 FROM cst_inv_qty_temp CIQT,
1813 cst_inv_cost_temp CICT
1814 WHERE CIQT.organization_id = p_org_id
1815 AND CIQT.organization_id = CICT.organization_id
1816 AND NVL(CIQT.cost_group_id,-1) =
1817 NVL(CICT.cost_group_id,NVL(CIQT.cost_group_id,-1))
1818 AND CIQT.inventory_item_id = CICT.inventory_item_id
1819 AND CICT.cost_source = 2 -- PAST
1820 GROUP BY
1821 CIQT.organization_id,
1822 CIQT.cost_group_id,
1823 CIQT.subinventory_code,
1824 CIQT.inventory_item_id,
1825 CICT.item_cost
1829 l_zero_val_hook = 0;
1826 /* Bug 14036099*/
1827 HAVING SUM(NVL(CIQT.rollback_qty,0))*NVL(CICT.item_cost,0) <> 0 OR
1828 SUM(NVL(CIQT.rollback_qty,0)) <> 0 OR
1830
1831
1832 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1833 THEN
1834 FND_MSG_PUB.Add_Exc_Msg(
1835 p_pkg_name => G_PKG_NAME,
1836 p_procedure_name => l_api_name,
1837 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
1838 ' rows to CPCST for initialization'
1839 );
1840 END IF;
1841
1842 l_stmt_num := 56;
1843 DELETE CST_ITEM_LIST_TEMP;
1844 DELETE CST_CG_LIST_TEMP;
1845 DELETE CST_SUB_LIST_TEMP;
1846 DELETE CST_INV_QTY_TEMP;
1847 DELETE CST_INV_COST_TEMP;
1848
1849 l_stmt_num := 57;
1850 INSERT
1851 INTO cst_inv_qty_temp(
1852 qty_source,
1853 organization_id,
1854 cost_group_id,
1855 subinventory_code,
1856 inventory_item_id,
1857 accounted_value)
1858
1859 SELECT 1, -- PRIOR ONHAND
1860 p_org_id organization_id,
1861 CPCST.cost_group_id,
1862 CPCST.subinventory_code,
1863 CPCST.inventory_item_id,
1864 CPCST.rollback_onhand_value
1865 FROM cst_per_close_summary_temp CPCST
1866 WHERE CPCST.rollback_onhand_value <> 0
1867
1868 UNION ALL
1869
1870 SELECT 2, -- PRIOR INTRANSIT
1871 p_org_id organization_id,
1872 CPCST.cost_group_id,
1873 CPCST.subinventory_code,
1874 CPCST.inventory_item_id,
1875 CPCST.rollback_intransit_value
1876 FROM cst_per_close_summary_temp CPCST
1877 WHERE CPCST.rollback_intransit_value <> 0
1878
1879 UNION ALL
1880
1881 SELECT 21, -- CUMULATIVE ONHAND
1882 p_org_id organization_id,
1883 CPCST.cost_group_id,
1884 CPCST.subinventory_code,
1885 CPCST.inventory_item_id,
1886 CPCST.rollback_onhand_value
1887 FROM cst_per_close_summary_temp CPCST
1888 WHERE CPCST.rollback_onhand_value <> 0
1889
1890 UNION ALL
1891
1892 SELECT 22, -- CUMULATIVE INTRANSIT
1893 p_org_id organization_id,
1894 CPCST.cost_group_id,
1895 CPCST.subinventory_code,
1896 CPCST.inventory_item_id,
1897 CPCST.rollback_intransit_value
1898 FROM cst_per_close_summary_temp CPCST
1899 WHERE CPCST.rollback_intransit_value <> 0;
1900
1901 l_stmt_num := 59;
1902 IF (p_simulation = 1) THEN
1903 DELETE cst_per_close_summary_temp;
1904 END IF;
1905
1906 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1907 THEN
1908 FND_MSG_PUB.Add_Exc_Msg(
1909 p_pkg_name => G_PKG_NAME,
1910 p_procedure_name => l_api_name,
1911 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
1912 ' rows to CIQT as baseline from CPCST'
1913 );
1914 END IF;
1915
1916 ELSIF (l_prev_summary <> 1) THEN
1917 --only the first unsummarized period should be summarizable if
1918 --there is existing information in CPCS.
1919 RAISE NO_PREV_SUMMARY_EXISTS;
1920 END IF;
1921
1922 IF (l_cpcs_count > 0) THEN
1923 l_stmt_num := 60;
1924 --we did not already insert baseline from
1925 --CPCST initialization, so insert from CPCS
1926 /* Bug 14036099
1927 INSERT
1928 INTO cst_inv_qty_temp(
1929 qty_source,
1930 organization_id,
1931 cost_group_id,
1932 subinventory_code,
1933 inventory_item_id,
1934 accounted_value)
1935 SELECT
1936 1, -- PRIOR ONHAND
1937 p_org_id organization_id,
1938 CPCS.cost_group_id,
1939 CPCS.subinventory_code,
1940 CPCS.inventory_item_id,
1941 CPCS.rollback_onhand_value
1942 FROM
1943 cst_period_close_summary CPCS
1944 WHERE CPCS.organization_id = p_org_id
1945 AND CPCS.acct_period_id = NVL(l_prior_period_id,-1)
1946
1947 UNION ALL
1948
1949 SELECT
1950 2, -- PRIOR INTRANSIT
1951 p_org_id organization_id,
1952 CPCS.cost_group_id,
1953 CPCS.subinventory_code,
1954 CPCS.inventory_item_id,
1955 CPCS.rollback_intransit_value
1956 FROM
1957 cst_period_close_summary CPCS
1958 WHERE CPCS.organization_id = p_org_id
1959 AND CPCS.acct_period_id = NVL(l_prior_period_id,-1)
1960
1961 UNION ALL
1962
1963 SELECT
1964 21, -- CUMULATIVE ONHAND
1965 p_org_id organization_id,
1966 CPCS.cost_group_id,
1967 CPCS.subinventory_code,
1968 CPCS.inventory_item_id,
1969 CPCS.cumulative_onhand_mta
1970 FROM
1971 cst_period_close_summary CPCS
1972 WHERE CPCS.organization_id = p_org_id
1973 AND CPCS.acct_period_id = NVL(l_prior_period_id,-1)
1974
1975 UNION ALL
1976
1977 SELECT
1978 22, -- CUMULATIVE INTRANSIT
1979 p_org_id organization_id,
1980 CPCS.cost_group_id,
1981 CPCS.subinventory_code,
1982 CPCS.inventory_item_id,
1983 CPCS.cumulative_intransit_mta
1984 FROM
1985 cst_period_close_summary CPCS
1989 /* Bug 14036099 */
1986 WHERE CPCS.organization_id = p_org_id
1987 AND CPCS.acct_period_id = NVL(l_prior_period_id,-1); */
1988
1990 INSERT ALL
1991 INTO cst_inv_qty_temp (
1992 qty_source,
1993 organization_id,
1994 cost_group_id,
1995 subinventory_code,
1996 inventory_item_id,
1997 accounted_value)
1998 VALUES (1, -- PRIOR ONHAND
1999 organization_id,
2000 cost_group_id,
2001 subinventory_code,
2002 inventory_item_id,
2003 rollback_onhand_value)
2004 INTO cst_inv_qty_temp (
2005 qty_source,
2006 organization_id,
2007 cost_group_id,
2008 subinventory_code,
2009 inventory_item_id,
2010 accounted_value)
2011 VALUES (2, -- PRIOR INTRANSIT
2012 organization_id,
2013 cost_group_id,
2014 subinventory_code,
2015 inventory_item_id,
2016 rollback_intransit_value)
2017 INTO cst_inv_qty_temp (
2018 qty_source,
2019 organization_id,
2020 cost_group_id,
2021 subinventory_code,
2022 inventory_item_id,
2023 accounted_value)
2024 VALUES (21, -- CUMULATIVE ONHAND
2025 organization_id,
2026 cost_group_id,
2027 subinventory_code,
2028 inventory_item_id,
2029 cumulative_onhand_mta)
2030 INTO cst_inv_qty_temp (
2031 qty_source,
2032 organization_id,
2033 cost_group_id,
2034 subinventory_code,
2035 inventory_item_id,
2036 accounted_value)
2037 VALUES (22, -- CUMULATIVE INTRANSIT
2038 organization_id,
2039 cost_group_id,
2040 subinventory_code,
2041 inventory_item_id,
2042 cumulative_intransit_mta)
2043 SELECT p_org_id organization_id,
2044 CPCS.cost_group_id,
2045 CPCS.subinventory_code,
2046 CPCS.inventory_item_id,
2047 CPCS.rollback_onhand_value,
2048 CPCS.rollback_intransit_value,
2049 CPCS.cumulative_onhand_mta,
2050 CPCS.cumulative_intransit_mta
2051 FROM
2052 cst_period_close_summary CPCS
2053 WHERE CPCS.organization_id = p_org_id
2054 AND CPCS.acct_period_id = NVL(l_prior_period_id,-1)
2055 AND (rollback_quantity <> 0 OR
2056 rollback_onhand_value <> 0 OR
2057 rollback_intransit_value <> 0 OR
2058 accounted_onhand_value <> 0 OR
2059 accounted_intransit_value <> 0 OR
2060 l_zero_val_hook = 0);
2061
2062 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2063 THEN
2064 FND_MSG_PUB.Add_Exc_Msg(
2065 p_pkg_name => G_PKG_NAME,
2066 p_procedure_name => l_api_name,
2067 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
2068 ' rows to CIQT as baseline from CPCS'
2069 );
2070 END IF;
2071
2072 END IF;
2073
2074 l_stmt_num := 65;
2075 SELECT primary_cost_method
2076 INTO l_cost_method
2077 FROM mtl_parameters
2078 WHERE organization_id = p_org_id;
2079
2080 l_stmt_num := 70;
2081 --summarize accounted value from MTA where
2082 --the primary quantity is the same in MTA and MMT
2083 INSERT
2084 INTO cst_inv_qty_temp(
2085 qty_source,
2086 organization_id,
2087 cost_group_id,
2088 subinventory_code,
2089 inventory_item_id,
2090 accounted_value)
2091 SELECT 11, -- CURRENT ONHAND
2092 p_org_id organization_id,
2093 DECODE(MTA.transaction_source_type_id,
2094 5,
2095 DECODE(
2096 l_cost_method,
2097 2,
2098 NVL(MMT.transfer_cost_group_id,
2099 MMT.cost_group_id),
2100 MMT.cost_group_id),
2101 MMT.cost_group_id),
2102 DECODE(MTA.transaction_source_type_id,
2103 5,
2104 DECODE(
2105 l_cost_method,
2106 2,
2107 DECODE(MMT.transfer_cost_group_id,
2108 NULL, MMT.subinventory_code,
2109 /* Bug 3500534
2110 It is possible to have normal issue to WIP transactions in
2111 average costing organizations with transfer_cost_group_id
2112 = cost_group_id. The following condition ensures such cases
2113 are handled as normal issue to WIP rather than common. */
2114 MMT.cost_group_id, MMT.subinventory_code,
2115 NULL),
2116 MMT.subinventory_code),
2117 MMT.subinventory_code),
2118 MMT.inventory_item_id,
2119 SUM(MTA.base_transaction_value)
2120 FROM mtl_material_transactions MMT,
2121 mtl_transaction_accounts MTA /*,
2122 mtl_secondary_inventories SUB */
2123 WHERE MTA.accounting_line_type = 1 -- inventory
2124 AND MTA.transaction_date >= l_period_start_date
2125 AND MTA.transaction_date <= l_to_date+1-(1/(24*3600))
2126 AND MTA.organization_id = p_org_id
2127 /* AND SUB.organization_id (+) = MMT.organization_id
2128 AND SUB.secondary_inventory_name (+) = MMT.subinventory_code
2129 AND NVL(SUB.asset_inventory,1) = 1 */
2130 AND (sign(MMT.primary_quantity) = sign(MTA.primary_quantity)/*BUG7326014*/
2131 OR
2132 MMT.transaction_action_id = 24)
2133 AND MMT.transaction_id = MTA.transaction_id
2134 AND MMT.transaction_type_id <> 25
2135 GROUP BY
2136 DECODE(MTA.transaction_source_type_id,
2137 5,
2138 DECODE(
2139 l_cost_method,
2140 2,
2141 NVL(MMT.transfer_cost_group_id,
2142 MMT.cost_group_id),
2143 MMT.cost_group_id),
2144 MMT.cost_group_id),
2145 DECODE(MTA.transaction_source_type_id,
2146 5,
2147 DECODE(
2148 l_cost_method,
2149 2,
2150 DECODE(MMT.transfer_cost_group_id,
2151 NULL, MMT.subinventory_code,
2152 /* Bug 3500534
2153 It is possible to have normal issue to WIP transactions in
2154 average costing organizations with transfer_cost_group_id
2155 = cost_group_id. The following condition ensures such cases
2156 are handled as normal issue to WIP rather than common. */
2157 MMT.cost_group_id, MMT.subinventory_code,
2158 NULL),
2159 MMT.subinventory_code),
2160 MMT.subinventory_code),
2161 MMT.inventory_item_id
2162 /* Bug 14036099 */
2163 HAVING (SUM(base_transaction_value) <> 0
2164 OR l_zero_val_hook = 0);
2165
2166 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2167 THEN
2168 FND_MSG_PUB.Add_Exc_Msg(
2169 p_pkg_name => G_PKG_NAME,
2170 p_procedure_name => l_api_name,
2171 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
2172 ' rows to CIQT for same MMT MTA primary quantity'
2173 );
2174 END IF;
2175
2176 l_stmt_num := 80;
2177 --summarize accounted value from MTA where
2178 --the primary quantity is different in MTA and MMT (using transfer sub, org, etc)
2179 INSERT
2180 INTO cst_inv_qty_temp(
2181 qty_source,
2182 organization_id,
2183 cost_group_id,
2184 subinventory_code,
2185 inventory_item_id,
2186 accounted_value)
2187 SELECT 11, -- CURRENT ONHAND
2188 p_org_id organization_id,
2189 MMT.transfer_cost_group_id,
2190 MMT.transfer_subinventory,
2191 MMT.inventory_item_id,
2192 SUM(MTA.base_transaction_value)
2193 FROM mtl_material_transactions MMT,
2194 mtl_transaction_accounts MTA /*,
2195 mtl_secondary_inventories SUB */
2196 WHERE MTA.accounting_line_type = 1 -- inventory
2197 AND MTA.transaction_date >= l_period_start_date
2198 AND MTA.transaction_date <= l_to_date+1-(1/(24*3600))
2199 AND MTA.organization_id = p_org_id
2200 /* AND SUB.organization_id (+) = MMT.transfer_organization_id
2201 AND SUB.secondary_inventory_name (+) = MMT.transfer_subinventory
2202 AND NVL(SUB.asset_inventory,1) = 1 */
2203 AND sign(MMT.primary_quantity )<>sign( MTA.primary_quantity)/*BUG7326014*/
2204 AND MMT.transaction_id = MTA.transaction_id
2205 AND MMT.transaction_action_id in (1,2,3,5,28,55)
2206 GROUP BY
2207 MMT.transfer_cost_group_id,
2208 MMT.transfer_subinventory,
2209 MMT.inventory_item_id
2210 /* Bug 14036099 */
2211 HAVING (SUM(base_transaction_value) <> 0
2212 OR l_zero_val_hook = 0);
2213
2214 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2215 THEN
2216 FND_MSG_PUB.Add_Exc_Msg(
2217 p_pkg_name => G_PKG_NAME,
2218 p_procedure_name => l_api_name,
2219 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
2220 ' rows to CIQT for different MMT MTA primary quantity'
2221 );
2222 END IF;
2223
2224 l_stmt_num := 85;
2225 --summarize intransit value from MTA
2226 INSERT
2227 INTO cst_inv_qty_temp(
2228 qty_source,
2229 organization_id,
2230 cost_group_id,
2231 subinventory_code,
2232 inventory_item_id,
2233 accounted_value)
2234 SELECT 12, -- CURRENT INTRANSIT
2235 p_org_id organization_id,
2236 DECODE(MMT.transaction_action_id,
2237 24,MMT.cost_group_id,
2238 /* Bug 9764385 */
2239 15,MMT.cost_group_id,
2240 22,MMT.cost_group_id,
2241 MMT.transfer_cost_group_id),
2242 NULL,
2243 MMT.inventory_item_id,
2244 SUM(MTA.base_transaction_value)
2245 FROM mtl_material_transactions MMT,
2246 mtl_transaction_accounts MTA
2247 WHERE MTA.accounting_line_type = 14 -- intransit account
2248 AND MTA.transaction_date >= l_period_start_date
2249 AND MTA.transaction_date <= l_to_date+1-(1/(24*3600))
2250 AND MTA.organization_id = p_org_id
2251 AND MMT.transaction_id = MTA.transaction_id
2252 GROUP
2253 BY MMT.inventory_item_id,
2254 DECODE(MMT.transaction_action_id,
2255 24,MMT.cost_group_id,
2256 /* Bug 9764385 */
2257 15,MMT.cost_group_id,
2258 22,MMT.cost_group_id,
2259 MMT.transfer_cost_group_id)
2260 /* Bug 14036099 */
2261 HAVING (SUM(MTA.base_transaction_value) <> 0
2262 OR l_zero_val_hook = 0);
2263
2264 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2265 THEN
2266 FND_MSG_PUB.Add_Exc_Msg(
2267 p_pkg_name => G_PKG_NAME,
2268 p_procedure_name => l_api_name,
2269 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
2270 ' rows to CIQT for intransit quantity'
2271 );
2272 END IF;
2273
2274 l_stmt_num := 90;
2275 --function call to calculate onhand value
2276 CST_Inventory_PUB.Calculate_InventoryValue(
2277 p_api_version => 1.0,
2278 p_init_msg_list => CST_Utility_PUB.Get_False,
2279 p_organization_id => p_org_id,
2280 p_onhand_value => 1,
2281 p_intransit_value => 1,
2282 p_receiving_value => 0,
2283 p_valuation_date => l_to_date+1-(1/(24*3600)),
2284 p_cost_type_id => NULL,
2285 p_item_from => NULL,
2286 p_item_to => NULL,
2287 p_category_set_id => l_category_set_id,
2288 p_category_from => NULL,
2289 p_category_to => NULL,
2290 p_cost_group_from => NULL,
2291 p_cost_group_to => NULL,
2292 p_subinventory_from => NULL,
2293 p_subinventory_to => NULL,
2294 p_qty_by_revision => NULL,
2295 p_zero_cost_only => NULL,
2296 p_zero_qty => NULL,
2297 p_expense_item => NULL,
2298 p_expense_sub => NULL,
2299 p_unvalued_txns => 0,
2300 p_receipt => 1,
2301 p_shipment => 1,
2302 x_return_status => l_return_status,
2303 x_msg_count => l_msg_count,
2304 x_msg_data => l_msg_data
2305 );
2306
2307 l_stmt_num := 100;
2308
2309 --choose which table to insert
2310 IF (p_simulation = 1) THEN
2311 l_stmt_num := 110;
2312 x_return_status := '3';
2313 --period open -> CPCST (simulation)
2314 INSERT
2315 INTO cst_per_close_summary_temp(
2316 cost_group_id,
2317 subinventory_code,
2318 inventory_item_id,
2319 accounted_value,
2320 rollback_value,
2321 rollback_qty)
2322 SELECT CIQT.cost_group_id,
2323 CIQT.subinventory_code,
2324 CIQT.inventory_item_id,
2325 SUM(DECODE(CIQT.qty_source,
2326 21,0,
2327 22,0,
2328 NVL(CIQT.accounted_value,0))) accounted_value,
2329 SUM(NVL(CIQT.rollback_qty,0))*NVL(CICT.item_cost,0) rollback_value,
2330 SUM(NVL(CIQT.rollback_qty,0))
2331 FROM cst_inv_qty_temp CIQT,
2332 cst_inv_cost_temp CICT
2333 WHERE CIQT.organization_id = p_org_id
2334 AND CIQT.organization_id = CICT.organization_id(+)
2335 AND NVL(CIQT.cost_group_id,-1) =
2336 NVL(CICT.cost_group_id,NVL(CIQT.cost_group_id,-1))
2337 AND CIQT.inventory_item_id = CICT.inventory_item_id(+)
2338 AND CICT.cost_source(+) = 2 -- PAST
2339 GROUP BY
2340 CIQT.cost_group_id,
2341 CIQT.subinventory_code,
2342 CIQT.inventory_item_id,
2343 CICT.item_cost
2344 /* Bug 14036099 */
2345 HAVING SUM(DECODE(CIQT.qty_source,
2346 21,0,
2347 22,0,
2348 NVL(CIQT.accounted_value,0))) <> 0
2349 OR SUM(NVL(CIQT.rollback_qty,0))*NVL(CICT.item_cost,0) <> 0
2350 OR SUM(NVL(CIQT.rollback_qty,0)) <> 0
2351 OR l_zero_val_hook = 0;
2352
2353 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2354 THEN
2355 FND_MSG_PUB.Add_Exc_Msg(
2356 p_pkg_name => G_PKG_NAME,
2357 p_procedure_name => l_api_name,
2358 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
2359 ' rows to CPCST for simulation purposes'
2360 );
2361 END IF;
2362
2363 ELSE
2364
2365 l_stmt_num := 120;
2366 x_return_status := '2';
2367 --period closed -> CPCS
2368 INSERT
2369 INTO cst_period_close_summary(
2370 acct_period_id,
2371 organization_id,
2372 cost_group_id,
2373 subinventory_code,
2374 inventory_item_id,
2375 accounted_value,
2376 rollback_value,
2377 rollback_quantity,
2378 rollback_onhand_value,
2379 rollback_intransit_value,
2380 accounted_onhand_value,
2381 accounted_intransit_value,
2382 onhand_value_discrepancy,
2383 intransit_value_discrepancy,
2384 cumulative_onhand_mta,
2385 cumulative_intransit_mta,
2386 last_update_date,
2387 last_updated_by,
2388 creation_date,
2389 creation_by)
2390 SELECT p_period_id,
2391 CIQT.organization_id,
2392 CIQT.cost_group_id,
2393 CIQT.subinventory_code,
2394 CIQT.inventory_item_id,
2395 SUM(DECODE(CIQT.qty_source,
2396 21,0,
2397 22,0,
2398 NVL(CIQT.accounted_value,0))) accounted_value,
2399 SUM(NVL(CIQT.rollback_qty,0))*NVL(CICT.item_cost,0) rollback_value,
2400 SUM(NVL(CIQT.rollback_qty,0)),
2401 SUM(DECODE(CIQT.qty_source,
2402 3,NVL(CIQT.rollback_qty,0),
2403 4,NVL(CIQT.rollback_qty,0),
2404 5,NVL(CIQT.rollback_qty,0),
2405 0))*NVL(CICT.item_cost,0) rollback_onhand_value,
2406 SUM(DECODE(CIQT.qty_source,
2407 6,NVL(CIQT.rollback_qty,0),
2408 7,NVL(CIQT.rollback_qty,0),
2409 8,NVL(CIQT.rollback_qty,0),
2410 0))*NVL(CICT.item_cost,0) rollback_intransit_value,
2411 SUM(DECODE(CIQT.qty_source,
2412 1,NVL(CIQT.accounted_value,0),
2413 11,NVL(CIQT.accounted_value,0),
2414 0)) accounted_onhand_value,
2415 SUM(DECODE(CIQT.qty_source,
2416 2,NVL(CIQT.accounted_value,0),
2417 12,NVL(CIQT.accounted_value,0),
2418 0)) accounted_intransit_value,
2419 SUM(DECODE(CIQT.qty_source,
2420 3,NVL(CIQT.rollback_qty,0),
2421 4,NVL(CIQT.rollback_qty,0),
2422 5,NVL(CIQT.rollback_qty,0),
2423 0))*NVL(CICT.item_cost,0) -
2424 SUM(DECODE(CIQT.qty_source,
2425 1,NVL(CIQT.accounted_value,0),
2426 11,NVL(CIQT.accounted_value,0),
2427 0)) onhand_value_discrepancy,
2428 SUM(DECODE(CIQT.qty_source,
2429 6,NVL(CIQT.rollback_qty,0),
2430 7,NVL(CIQT.rollback_qty,0),
2431 8,NVL(CIQT.rollback_qty,0),
2432 0))*NVL(CICT.item_cost,0) -
2433 SUM(DECODE(CIQT.qty_source,
2434 2,NVL(CIQT.accounted_value,0),
2435 12,NVL(CIQT.accounted_value,0),
2436 0)) intransit_value_discrepancy,
2437 SUM(DECODE(CIQT.qty_source,
2438 11,NVL(CIQT.accounted_value,0),
2439 21,NVL(CIQT.accounted_value,0),
2440 0)) cumulative_onhand_mta,
2441 SUM(DECODE(CIQT.qty_source,
2442 12,NVL(CIQT.accounted_value,0),
2443 22,NVL(CIQT.accounted_value,0),
2444 0)) cumulative_intransit_mta,
2445 SYSDATE,
2446 1,
2447 SYSDATE,
2448 1
2449 FROM cst_inv_qty_temp CIQT,
2450 cst_inv_cost_temp CICT
2451 WHERE CIQT.organization_id = p_org_id
2452 AND CIQT.organization_id = CICT.organization_id(+)
2453 AND NVL(CIQT.cost_group_id,-1) =
2454 NVL(CICT.cost_group_id,NVL(CIQT.cost_group_id,-1))
2455 AND CIQT.inventory_item_id = CICT.inventory_item_id(+)
2456 AND CICT.cost_source(+) = 2 -- PAST
2457 GROUP BY
2458 CIQT.organization_id,
2459 CIQT.cost_group_id,
2460 CIQT.subinventory_code,
2461 CIQT.inventory_item_id,
2462 CICT.item_cost
2463 /* Bug 14036099 */
2464 HAVING SUM(NVL(CIQT.rollback_qty,0)) <> 0
2465 OR SUM(DECODE(CIQT.qty_source,
2466 3,NVL(CIQT.rollback_qty,0),
2467 4,NVL(CIQT.rollback_qty,0),
2468 5,NVL(CIQT.rollback_qty,0),
2469 0))*NVL(CICT.item_cost,0) <> 0
2470 OR SUM(DECODE(CIQT.qty_source,
2471 6,NVL(CIQT.rollback_qty,0),
2472 7,NVL(CIQT.rollback_qty,0),
2473 8,NVL(CIQT.rollback_qty,0),
2474 0))*NVL(CICT.item_cost,0) <> 0
2475 OR SUM(DECODE(CIQT.qty_source,
2476 1,NVL(CIQT.accounted_value,0),
2477 11,NVL(CIQT.accounted_value,0),
2478 0)) <> 0
2479 OR SUM(DECODE(CIQT.qty_source,
2480 2,NVL(CIQT.accounted_value,0),
2481 12,NVL(CIQT.accounted_value,0),
2482 0)) <> 0
2483 OR l_zero_val_hook = 0;
2484
2485 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2486 THEN
2487 FND_MSG_PUB.Add_Exc_Msg(
2488 p_pkg_name => G_PKG_NAME,
2489 p_procedure_name => l_api_name,
2490 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
2491 ' rows to CPCS for org/period ' || p_org_id || '/' || p_period_id
2492 );
2493 END IF;
2494
2495 l_stmt_num := 130;
2496 UPDATE org_acct_periods
2497 SET summarized_flag = 'Y'
2498
2499 WHERE organization_id = p_org_id
2500 AND acct_period_id = p_period_id;
2501
2502 END IF;
2503
2504 EXCEPTION
2505 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2506 ROLLBACK TO Summarize_Period_PUB;
2507 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2508
2509 WHEN ZERO_VAL_SUMMARY_OPTION_ERROR THEN
2510 ROLLBACK TO Summarize_Period_PUB;
2511 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2512
2513 WHEN NO_PREV_SUMMARY_EXISTS THEN
2514 ROLLBACK TO Summarize_Period_PUB;
2515 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2516 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2517 THEN
2518 FND_MSG_PUB.Add_Exc_Msg(
2519 p_pkg_name => G_PKG_NAME,
2520 p_procedure_name => l_api_name,
2521 p_error_text => l_stmt_num||': Only first unsummarized period can be summarized'
2522 );
2523 END IF;
2524
2525 WHEN OTHERS THEN
2526 ROLLBACK TO Summarize_Period_PUB;
2527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2528 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2529 THEN
2530 FND_MSG_PUB.Add_Exc_Msg(
2531 p_pkg_name => G_PKG_NAME,
2532 p_procedure_name => l_api_name,
2533 p_error_text => l_stmt_num||SUBSTR(SQLERRM,1,235)
2534 );
2535 END IF;
2536
2537 END Summarize_Period;
2538
2539 END CST_AccountingPeriod_PUB;