[Home] [Help]
PACKAGE BODY: APPS.CST_ACCOUNTINGPERIOD_PUB
Source
1 PACKAGE BODY CST_AccountingPeriod_PUB AS
2 /* $Header: CSTPAPEB.pls 120.9.12010000.8 2008/12/16 20:54:42 hyu 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;
30 l_io_rec_type WSH_INTEGRATION.ShpgUnTrxdInOutRecType;
31 l_return_status VARCHAR2(200);
32 l_msg_count NUMBER;
33 l_msg_data VARCHAR2(200);
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 is not null;
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
187 AND transaction_date <= l_sched_close_date;
188
189 x_pending_wsm := x_pending_wsm + l_tcount;
190
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
340 l_stmt_num := 110;
341 SELECT COUNT(*)
342 INTO l_tcount
343 FROM cst_lc_adj_interface
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
434 WHERE organization_id = p_org_id
431 CURSOR check_if_duplicating IS
432 SELECT period_start_date
433 FROM org_acct_periods
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||','||
489 p_login_id||','||
490 p_acct_period_type||','||
491 p_org_period_set_name||','||
492 p_open_period_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
590 p_user_id, -- last_updated_by
587 x_new_acct_period_id, -- acct_period_id
588 p_org_id, -- organization_id
589 SYSDATE, -- last_update_date
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
738
735
736
737
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
890 x_prompt_to_reclose := TRUE;
887 INTO l_temp_id;
888
889 IF check_reclose_period%FOUND THEN
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 = 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;
1035 END IF;
1032 GOTO error_label;
1033 ELSE
1034 x_wip_failed := FALSE;
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;
1065 -- check if there are unprocessed transactions in MMTT/MMT/WCTI
1066 SELECT COUNT(*)
1067 INTO l_count
1068 FROM mtl_material_transactions_temp
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 is not null
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'
1182 -- Launch reconciliation report
1179 AND TO_NUMBER(HOI.org_information1) = GL.ledger_id;
1180
1181 l_stmt_num := 170;
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
1219 <<procedure_end>>
1220 COMMIT;
1221
1222 EXCEPTION
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||','||
1316 p_login_id,
1313 p_new_end_date||','||
1314 p_changed_acct_period_id||','||
1315 p_user_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 = 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 = trunc(SYSDATE),
1370 last_updated_by = p_user_id,
1371 last_update_login = p_login_id
1372 WHERE organization_id = p_org_id
1373 AND acct_period_id =
1374 (SELECT MIN(acct_period_id)
1375 FROM org_acct_periods
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
1469 WHEN OTHERS THEN
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;
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
1511 l_return_status VARCHAR2(1) := '0';
1512
1513 l_api_name CONSTANT VARCHAR2(30) := 'Summarize_Period';
1514 l_api_version CONSTANT NUMBER := 1.0;
1515 l_msg_level_threshold NUMBER;
1516 l_stmt_num NUMBER := 0;
1517 l_msg_count NUMBER := 0;
1518 l_msg_data VARCHAR2(2000);
1519
1520 NO_PREV_SUMMARY_EXISTS EXCEPTION;
1521
1522 BEGIN
1523
1524 -- Standard Start of API savepoint
1525 SAVEPOINT Summarize_Period_PUB;
1526
1527 -- Check for call compatibility
1528 IF NOT FND_API.Compatible_API_Call
1529 ( p_current_version_number => l_api_version,
1530 p_caller_version_number => p_api_version,
1531 p_api_name => l_api_name,
1532 p_pkg_name => G_PKG_NAME
1533 )
1534 THEN
1535 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1536 END IF;
1537
1538 -- Check for message level threshold
1539 l_msg_level_threshold := FND_PROFILE.Value('FND_AS_MSG_LEVEL_THRESHOLD');
1540
1541 FND_MSG_PUB.Initialize;
1542
1543 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
1544 THEN
1545 FND_MSG_PUB.Add_Exc_Msg(
1546 p_pkg_name => G_PKG_NAME,
1547 p_procedure_name => l_api_name,
1548 p_error_text => SUBSTR(
1549 l_stmt_num||':'||
1550 p_org_id||','||
1551 p_period_id||','||
1552 p_to_date||','||
1553 p_user_id||','||
1554 p_login_id,
1555 1,
1556 240
1557 )
1558 );
1559 END IF;
1560
1561 l_stmt_num := 5;
1562
1563 SELECT legal_entity
1564 INTO l_legal_entity
1565 FROM cst_acct_info_v
1566 WHERE organization_id = p_org_id;
1567
1568 l_stmt_num := 7;
1569 SELECT period_start_date, schedule_close_date
1570 INTO l_le_period_start_date, l_le_to_date
1571 FROM org_acct_periods
1572 WHERE organization_id = p_org_id
1573 AND acct_period_id = p_period_id;
1574
1575 l_stmt_num := 8;
1576 l_period_start_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
1577 l_le_period_start_date,
1578 l_legal_entity);
1579
1580 l_stmt_num := 10;
1581 IF p_to_date IS NULL THEN
1582 l_to_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
1583 l_le_to_date,
1584 l_legal_entity);
1585 ELSE
1586 l_to_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
1587 p_to_date,
1588 l_legal_entity);
1589 END IF;
1590
1591 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1592 THEN
1593 FND_MSG_PUB.Add_Exc_Msg(
1594 p_pkg_name => G_PKG_NAME,
1595 p_procedure_name => l_api_name,
1596 p_error_text => l_stmt_num||': Using start date of ' || to_char(l_period_start_date,'DD-MON-YYYY HH24:MI:SS')
1597 );
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 to date of ' || to_char(l_to_date,'DD-MON-YYYY HH24:MI:SS')
1602 );
1603 END IF;
1604
1608 INTO l_prior_period_id
1605 l_stmt_num := 20;
1606 --find id of the previous period
1607 SELECT MAX(acct_period_id)
1609 FROM org_acct_periods
1610 WHERE organization_id = p_org_id
1611 AND acct_period_id < p_period_id;
1612
1613 l_stmt_num := 30;
1614 --if summarized_flag in org_acct_periods is 'N' and data exists in CPCS
1615 --for the same period, delete the rows from CPCS.
1616
1617 SELECT count(*)
1618 INTO l_resummarize
1619 FROM org_acct_periods
1620 WHERE organization_id = p_org_id
1621 AND acct_period_id = p_period_id
1622 AND summarized_flag = 'N'
1623 AND EXISTS
1624 (SELECT 'Data exists in CPCS'
1625 FROM cst_period_close_summary
1626 WHERE organization_id = p_org_id
1627 AND acct_period_id = p_period_id);
1628
1629 IF (l_resummarize > 0) THEN
1630
1631 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1632 THEN
1633 FND_MSG_PUB.Add_Exc_Msg(
1634 p_pkg_name => G_PKG_NAME,
1635 p_procedure_name => l_api_name,
1636 p_error_text => l_stmt_num||': Resummarizing: data exists in CPCS for org/period '
1637 ||p_org_id || '/' || p_period_id
1638 );
1639 END IF;
1640
1641 l_stmt_num := 35;
1642
1643 DELETE cst_period_close_summary
1644 WHERE organization_id = p_org_id
1645 AND acct_period_id >= p_period_id;
1646
1647 /* Updating org_acct_periods in case the customer has not updated summarized_flag
1648 for all succeeding periods */
1649 l_stmt_num := 37;
1650 UPDATE org_acct_periods
1651 SET summarized_flag = 'N'
1652 WHERE organization_id = p_org_id
1653 AND acct_period_id >= p_period_id
1654 AND summarized_flag = 'Y';
1655 END IF;
1656
1657 l_stmt_num := 40;
1658 --check if previous period is summarized
1659 SELECT count(*)
1660 INTO l_prev_summary
1661 FROM org_acct_periods
1662 WHERE organization_id = p_org_id
1663 AND acct_period_id = l_prior_period_id
1664 AND summarized_flag = 'Y';
1665
1666 --check if CPCS is empty
1667 SELECT count(*)
1668 INTO l_cpcs_count
1669 FROM cst_period_close_summary
1670 WHERE organization_id = p_org_id
1671 AND rownum = 1;
1672
1673 l_stmt_num := 45;
1674 --find default category set
1675 SELECT category_set_id
1676 INTO l_category_set_id
1677 FROM mtl_default_category_sets
1678 WHERE functional_area_id = 5; -- Costing functional area
1679
1680 IF (l_cpcs_count = 0) THEN
1681
1682 l_stmt_num := 50;
1683 --find date to rollback to for initialization
1684
1685 SELECT NVL(OAP1.schedule_close_date+1-(1/(24*3600)),
1686 OAP2.period_start_date-(1/(24*3600)))
1687 INTO l_le_prior_end_date
1688 FROM org_acct_periods OAP1,
1689 org_acct_periods OAP2
1690 WHERE OAP1.organization_id(+) = OAP2.organization_id
1691 AND OAP1.acct_period_id(+) = l_prior_period_id
1692 AND OAP2.organization_id = p_org_id
1693 AND OAP2.acct_period_id = p_period_id;
1694
1695 l_stmt_num := 51;
1696 l_prior_end_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
1697 l_le_prior_end_date,
1698 l_legal_entity);
1699
1700 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1701 THEN
1702 FND_MSG_PUB.Add_Exc_Msg(
1703 p_pkg_name => G_PKG_NAME,
1704 p_procedure_name => l_api_name,
1705 p_error_text => l_stmt_num||': Initializing new summary history in CPCS for org '
1706 ||p_org_id|| ', rolling back to ' || to_char(l_prior_end_date,'DD-MON-YYYY HH24:MI:SS')
1707 );
1708 END IF;
1709
1710 l_stmt_num := 52;
1711 CST_Inventory_PUB.Calculate_InventoryValue(
1712 p_api_version => 1.0,
1713 p_init_msg_list => CST_Utility_PUB.Get_False,
1714 p_organization_id => p_org_id,
1715 p_onhand_value => 1,
1716 p_intransit_value => 1,
1717 p_receiving_value => 0,
1718 p_valuation_date => l_prior_end_date,
1719 p_cost_type_id => NULL,
1720 p_item_from => NULL,
1721 p_item_to => NULL,
1722 p_category_set_id => l_category_set_id,
1723 p_category_from => NULL,
1724 p_category_to => NULL,
1725 p_cost_group_from => NULL,
1726 p_cost_group_to => NULL,
1730 p_zero_cost_only => NULL,
1727 p_subinventory_from => NULL,
1728 p_subinventory_to => NULL,
1729 p_qty_by_revision => NULL,
1731 p_zero_qty => NULL,
1732 p_expense_item => NULL,
1733 p_expense_sub => NULL,
1734 p_unvalued_txns => 0,
1735 p_receipt => 1,
1736 p_shipment => 1,
1737 x_return_status => l_return_status,
1738 x_msg_count => l_msg_count,
1739 x_msg_data => l_msg_data
1740 );
1741
1742 l_stmt_num := 54;
1743 INSERT
1744 INTO cst_per_close_summary_temp(
1745 cost_group_id,
1746 subinventory_code,
1747 inventory_item_id,
1748 accounted_value,
1749 rollback_value,
1750 rollback_qty,
1751 rollback_onhand_value,
1752 rollback_intransit_value)
1753 SELECT CIQT.cost_group_id,
1754 CIQT.subinventory_code,
1755 CIQT.inventory_item_id,
1756 0 accounted_value,
1757 SUM(NVL(CIQT.rollback_qty,0))*NVL(CICT.item_cost,0) rollback_value,
1758 SUM(NVL(CIQT.rollback_qty,0)),
1759 SUM(DECODE(CIQT.qty_source,
1760 3,NVL(CIQT.rollback_qty,0),
1761 4,NVL(CIQT.rollback_qty,0),
1762 5,NVL(CIQT.rollback_qty,0),
1763 0))*NVL(CICT.item_cost,0) rollback_onhand_value,
1764 SUM(DECODE(CIQT.qty_source,
1765 6,NVL(CIQT.rollback_qty,0),
1766 7,NVL(CIQT.rollback_qty,0),
1767 8,NVL(CIQT.rollback_qty,0),
1768 0))*NVL(CICT.item_cost,0) rollback_intransit_value
1769 FROM cst_inv_qty_temp CIQT,
1770 cst_inv_cost_temp CICT
1771 WHERE CIQT.organization_id = p_org_id
1772 AND CIQT.organization_id = CICT.organization_id
1773 AND NVL(CIQT.cost_group_id,-1) =
1774 NVL(CICT.cost_group_id,NVL(CIQT.cost_group_id,-1))
1775 AND CIQT.inventory_item_id = CICT.inventory_item_id
1776 AND CICT.cost_source = 2 -- PAST
1777 GROUP BY
1778 CIQT.organization_id,
1779 CIQT.cost_group_id,
1780 CIQT.subinventory_code,
1781 CIQT.inventory_item_id,
1782 CICT.item_cost;
1783
1784 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1785 THEN
1786 FND_MSG_PUB.Add_Exc_Msg(
1787 p_pkg_name => G_PKG_NAME,
1788 p_procedure_name => l_api_name,
1789 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
1790 ' rows to CPCST for initialization'
1791 );
1792 END IF;
1793
1794 l_stmt_num := 56;
1795 DELETE CST_ITEM_LIST_TEMP;
1796 DELETE CST_CG_LIST_TEMP;
1797 DELETE CST_SUB_LIST_TEMP;
1798 DELETE CST_INV_QTY_TEMP;
1799 DELETE CST_INV_COST_TEMP;
1800
1801 l_stmt_num := 57;
1802 INSERT
1803 INTO cst_inv_qty_temp(
1804 qty_source,
1805 organization_id,
1806 cost_group_id,
1807 subinventory_code,
1808 inventory_item_id,
1809 accounted_value)
1810
1811 SELECT 1, -- PRIOR ONHAND
1812 p_org_id organization_id,
1813 CPCST.cost_group_id,
1814 CPCST.subinventory_code,
1815 CPCST.inventory_item_id,
1816 CPCST.rollback_onhand_value
1817 FROM cst_per_close_summary_temp CPCST
1818 WHERE CPCST.rollback_onhand_value <> 0
1819
1820 UNION ALL
1821
1822 SELECT 2, -- PRIOR INTRANSIT
1823 p_org_id organization_id,
1824 CPCST.cost_group_id,
1825 CPCST.subinventory_code,
1826 CPCST.inventory_item_id,
1827 CPCST.rollback_intransit_value
1828 FROM cst_per_close_summary_temp CPCST
1829 WHERE CPCST.rollback_intransit_value <> 0
1830
1831 UNION ALL
1832
1833 SELECT 21, -- CUMULATIVE ONHAND
1834 p_org_id organization_id,
1835 CPCST.cost_group_id,
1836 CPCST.subinventory_code,
1837 CPCST.inventory_item_id,
1838 CPCST.rollback_onhand_value
1839 FROM cst_per_close_summary_temp CPCST
1840 WHERE CPCST.rollback_onhand_value <> 0
1841
1842 UNION ALL
1843
1847 CPCST.subinventory_code,
1844 SELECT 22, -- CUMULATIVE INTRANSIT
1845 p_org_id organization_id,
1846 CPCST.cost_group_id,
1848 CPCST.inventory_item_id,
1849 CPCST.rollback_intransit_value
1850 FROM cst_per_close_summary_temp CPCST
1851 WHERE CPCST.rollback_intransit_value <> 0;
1852
1853 l_stmt_num := 59;
1854 IF (p_simulation = 1) THEN
1855 DELETE cst_per_close_summary_temp;
1856 END IF;
1857
1858 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1859 THEN
1860 FND_MSG_PUB.Add_Exc_Msg(
1861 p_pkg_name => G_PKG_NAME,
1862 p_procedure_name => l_api_name,
1863 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
1864 ' rows to CIQT as baseline from CPCST'
1865 );
1866 END IF;
1867
1868 ELSIF (l_prev_summary <> 1) THEN
1869 --only the first unsummarized period should be summarizable if
1870 --there is existing information in CPCS.
1871 RAISE NO_PREV_SUMMARY_EXISTS;
1872 END IF;
1873
1874 IF (l_cpcs_count > 0) THEN
1875 l_stmt_num := 60;
1876 --we did not already insert baseline from
1877 --CPCST initialization, so insert from CPCS
1878 INSERT
1879 INTO cst_inv_qty_temp(
1880 qty_source,
1881 organization_id,
1882 cost_group_id,
1883 subinventory_code,
1884 inventory_item_id,
1885 accounted_value)
1886 SELECT
1887 1, -- PRIOR ONHAND
1888 p_org_id organization_id,
1889 CPCS.cost_group_id,
1890 CPCS.subinventory_code,
1891 CPCS.inventory_item_id,
1892 CPCS.rollback_onhand_value
1893 FROM
1894 cst_period_close_summary CPCS
1895 WHERE CPCS.organization_id = p_org_id
1896 AND CPCS.acct_period_id = NVL(l_prior_period_id,-1)
1897
1898 UNION ALL
1899
1900 SELECT
1901 2, -- PRIOR INTRANSIT
1902 p_org_id organization_id,
1903 CPCS.cost_group_id,
1904 CPCS.subinventory_code,
1905 CPCS.inventory_item_id,
1906 CPCS.rollback_intransit_value
1907 FROM
1908 cst_period_close_summary CPCS
1909 WHERE CPCS.organization_id = p_org_id
1910 AND CPCS.acct_period_id = NVL(l_prior_period_id,-1)
1911
1912 UNION ALL
1913
1914 SELECT
1915 21, -- CUMULATIVE ONHAND
1916 p_org_id organization_id,
1917 CPCS.cost_group_id,
1918 CPCS.subinventory_code,
1919 CPCS.inventory_item_id,
1920 CPCS.cumulative_onhand_mta
1921 FROM
1922 cst_period_close_summary CPCS
1923 WHERE CPCS.organization_id = p_org_id
1924 AND CPCS.acct_period_id = NVL(l_prior_period_id,-1)
1925
1926 UNION ALL
1927
1928 SELECT
1929 22, -- CUMULATIVE INTRANSIT
1930 p_org_id organization_id,
1931 CPCS.cost_group_id,
1932 CPCS.subinventory_code,
1933 CPCS.inventory_item_id,
1934 CPCS.cumulative_intransit_mta
1935 FROM
1936 cst_period_close_summary CPCS
1937 WHERE CPCS.organization_id = p_org_id
1938 AND CPCS.acct_period_id = NVL(l_prior_period_id,-1);
1939
1940 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1941 THEN
1942 FND_MSG_PUB.Add_Exc_Msg(
1943 p_pkg_name => G_PKG_NAME,
1944 p_procedure_name => l_api_name,
1945 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
1946 ' rows to CIQT as baseline from CPCS'
1947 );
1948 END IF;
1949
1950 END IF;
1951
1952 l_stmt_num := 65;
1953 SELECT primary_cost_method
1954 INTO l_cost_method
1955 FROM mtl_parameters
1956 WHERE organization_id = p_org_id;
1957
1958 l_stmt_num := 70;
1959 --summarize accounted value from MTA where
1960 --the primary quantity is the same in MTA and MMT
1961 INSERT
1962 INTO cst_inv_qty_temp(
1963 qty_source,
1964 organization_id,
1965 cost_group_id,
1966 subinventory_code,
1967 inventory_item_id,
1968 accounted_value)
1969 SELECT 11, -- CURRENT ONHAND
1970 p_org_id organization_id,
1971 DECODE(MTA.transaction_source_type_id,
1972 5,
1973 DECODE(
1974 l_cost_method,
1975 2,
1976 NVL(MMT.transfer_cost_group_id,
1977 MMT.cost_group_id),
1978 MMT.cost_group_id),
1979 MMT.cost_group_id),
1980 DECODE(MTA.transaction_source_type_id,
1981 5,
1982 DECODE(
1983 l_cost_method,
1984 2,
1985 DECODE(MMT.transfer_cost_group_id,
1986 NULL, MMT.subinventory_code,
1987 /* Bug 3500534
1991 are handled as normal issue to WIP rather than common. */
1988 It is possible to have normal issue to WIP transactions in
1989 average costing organizations with transfer_cost_group_id
1990 = cost_group_id. The following condition ensures such cases
1992 MMT.cost_group_id, MMT.subinventory_code,
1993 NULL),
1994 MMT.subinventory_code),
1995 MMT.subinventory_code),
1996 MMT.inventory_item_id,
1997 SUM(MTA.base_transaction_value)
1998 FROM mtl_material_transactions MMT,
1999 mtl_transaction_accounts MTA /*,
2000 mtl_secondary_inventories SUB */
2001 WHERE MTA.accounting_line_type = 1 -- inventory
2002 AND MTA.transaction_date >= l_period_start_date
2003 AND MTA.transaction_date <= l_to_date+1-(1/(24*3600))
2004 AND MTA.organization_id = p_org_id
2005 /* AND SUB.organization_id (+) = MMT.organization_id
2006 AND SUB.secondary_inventory_name (+) = MMT.subinventory_code
2007 AND NVL(SUB.asset_inventory,1) = 1 */
2008 AND (sign(MMT.primary_quantity) = sign(MTA.primary_quantity)/*BUG7326014*/
2009 OR
2010 MMT.transaction_action_id = 24)
2011 AND MMT.transaction_id = MTA.transaction_id
2012 AND MMT.transaction_type_id <> 25
2013 GROUP BY
2014 DECODE(MTA.transaction_source_type_id,
2015 5,
2016 DECODE(
2017 l_cost_method,
2018 2,
2019 NVL(MMT.transfer_cost_group_id,
2020 MMT.cost_group_id),
2021 MMT.cost_group_id),
2022 MMT.cost_group_id),
2023 DECODE(MTA.transaction_source_type_id,
2024 5,
2025 DECODE(
2026 l_cost_method,
2027 2,
2028 DECODE(MMT.transfer_cost_group_id,
2029 NULL, MMT.subinventory_code,
2030 /* Bug 3500534
2031 It is possible to have normal issue to WIP transactions in
2032 average costing organizations with transfer_cost_group_id
2033 = cost_group_id. The following condition ensures such cases
2034 are handled as normal issue to WIP rather than common. */
2035 MMT.cost_group_id, MMT.subinventory_code,
2036 NULL),
2037 MMT.subinventory_code),
2038 MMT.subinventory_code),
2039 MMT.inventory_item_id;
2040
2041 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2042 THEN
2043 FND_MSG_PUB.Add_Exc_Msg(
2044 p_pkg_name => G_PKG_NAME,
2045 p_procedure_name => l_api_name,
2046 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
2047 ' rows to CIQT for same MMT MTA primary quantity'
2048 );
2049 END IF;
2050
2051 l_stmt_num := 80;
2052 --summarize accounted value from MTA where
2053 --the primary quantity is different in MTA and MMT (using transfer sub, org, etc)
2054 INSERT
2055 INTO cst_inv_qty_temp(
2056 qty_source,
2057 organization_id,
2058 cost_group_id,
2059 subinventory_code,
2060 inventory_item_id,
2061 accounted_value)
2062 SELECT 11, -- CURRENT ONHAND
2063 p_org_id organization_id,
2064 MMT.transfer_cost_group_id,
2065 MMT.transfer_subinventory,
2066 MMT.inventory_item_id,
2067 SUM(MTA.base_transaction_value)
2068 FROM mtl_material_transactions MMT,
2069 mtl_transaction_accounts MTA /*,
2070 mtl_secondary_inventories SUB */
2071 WHERE MTA.accounting_line_type = 1 -- inventory
2072 AND MTA.transaction_date >= l_period_start_date
2073 AND MTA.transaction_date <= l_to_date+1-(1/(24*3600))
2074 AND MTA.organization_id = p_org_id
2075 /* AND SUB.organization_id (+) = MMT.transfer_organization_id
2076 AND SUB.secondary_inventory_name (+) = MMT.transfer_subinventory
2077 AND NVL(SUB.asset_inventory,1) = 1 */
2078 AND sign(MMT.primary_quantity )<>sign( MTA.primary_quantity)/*BUG7326014*/
2079 AND MMT.transaction_id = MTA.transaction_id
2080 AND MMT.transaction_action_id in (1,2,3,5,28,55)
2081 GROUP BY
2082 MMT.transfer_cost_group_id,
2083 MMT.transfer_subinventory,
2084 MMT.inventory_item_id;
2085
2086 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2087 THEN
2088 FND_MSG_PUB.Add_Exc_Msg(
2089 p_pkg_name => G_PKG_NAME,
2090 p_procedure_name => l_api_name,
2091 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
2092 ' rows to CIQT for different MMT MTA primary quantity'
2093 );
2094 END IF;
2095
2096 l_stmt_num := 85;
2097 --summarize intransit value from MTA
2098 INSERT
2099 INTO cst_inv_qty_temp(
2100 qty_source,
2101 organization_id,
2102 cost_group_id,
2103 subinventory_code,
2104 inventory_item_id,
2105 accounted_value)
2106 SELECT 12, -- CURRENT INTRANSIT
2107 p_org_id organization_id,
2108 DECODE(MMT.transaction_action_id,
2109 24,MMT.cost_group_id,
2110 MMT.transfer_cost_group_id),
2111 NULL,
2112 MMT.inventory_item_id,
2113 SUM(MTA.base_transaction_value)
2114 FROM mtl_material_transactions MMT,
2115 mtl_transaction_accounts MTA
2116 WHERE MTA.accounting_line_type = 14 -- intransit account
2117 AND MTA.transaction_date >= l_period_start_date
2118 AND MTA.transaction_date <= l_to_date+1-(1/(24*3600))
2119 AND MTA.organization_id = p_org_id
2120 AND MMT.transaction_id = MTA.transaction_id
2121 GROUP
2122 BY MMT.inventory_item_id,
2123 DECODE(MMT.transaction_action_id,
2124 24,MMT.cost_group_id,
2125 MMT.transfer_cost_group_id);
2126
2127 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2128 THEN
2129 FND_MSG_PUB.Add_Exc_Msg(
2130 p_pkg_name => G_PKG_NAME,
2131 p_procedure_name => l_api_name,
2132 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
2133 ' rows to CIQT for intransit quantity'
2134 );
2135 END IF;
2136
2137 l_stmt_num := 90;
2138 --function call to calculate onhand value
2139 CST_Inventory_PUB.Calculate_InventoryValue(
2140 p_api_version => 1.0,
2141 p_init_msg_list => CST_Utility_PUB.Get_False,
2142 p_organization_id => p_org_id,
2143 p_onhand_value => 1,
2144 p_intransit_value => 1,
2145 p_receiving_value => 0,
2146 p_valuation_date => l_to_date+1-(1/(24*3600)),
2147 p_cost_type_id => NULL,
2148 p_item_from => NULL,
2149 p_item_to => NULL,
2150 p_category_set_id => l_category_set_id,
2151 p_category_from => NULL,
2152 p_category_to => NULL,
2153 p_cost_group_from => NULL,
2154 p_cost_group_to => NULL,
2155 p_subinventory_from => NULL,
2156 p_subinventory_to => NULL,
2157 p_qty_by_revision => NULL,
2158 p_zero_cost_only => NULL,
2159 p_zero_qty => NULL,
2160 p_expense_item => NULL,
2161 p_expense_sub => NULL,
2162 p_unvalued_txns => 0,
2163 p_receipt => 1,
2164 p_shipment => 1,
2165 x_return_status => l_return_status,
2166 x_msg_count => l_msg_count,
2167 x_msg_data => l_msg_data
2168 );
2169
2170 l_stmt_num := 100;
2171
2172 --choose which table to insert
2173 IF (p_simulation = 1) THEN
2174 l_stmt_num := 110;
2175 x_return_status := '3';
2176 --period open -> CPCST (simulation)
2177 INSERT
2178 INTO cst_per_close_summary_temp(
2179 cost_group_id,
2180 subinventory_code,
2181 inventory_item_id,
2182 accounted_value,
2186 CIQT.subinventory_code,
2183 rollback_value,
2184 rollback_qty)
2185 SELECT CIQT.cost_group_id,
2187 CIQT.inventory_item_id,
2188 SUM(DECODE(CIQT.qty_source,
2189 21,0,
2190 22,0,
2191 NVL(CIQT.accounted_value,0))) accounted_value,
2192 SUM(NVL(CIQT.rollback_qty,0))*NVL(CICT.item_cost,0) rollback_value,
2193 SUM(NVL(CIQT.rollback_qty,0))
2194 FROM cst_inv_qty_temp CIQT,
2195 cst_inv_cost_temp CICT
2196 WHERE CIQT.organization_id = p_org_id
2197 AND CIQT.organization_id = CICT.organization_id(+)
2198 AND NVL(CIQT.cost_group_id,-1) =
2199 NVL(CICT.cost_group_id,NVL(CIQT.cost_group_id,-1))
2200 AND CIQT.inventory_item_id = CICT.inventory_item_id(+)
2201 AND CICT.cost_source(+) = 2 -- PAST
2202 GROUP BY
2203 CIQT.cost_group_id,
2204 CIQT.subinventory_code,
2205 CIQT.inventory_item_id,
2206 CICT.item_cost;
2207
2208 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2209 THEN
2210 FND_MSG_PUB.Add_Exc_Msg(
2211 p_pkg_name => G_PKG_NAME,
2212 p_procedure_name => l_api_name,
2213 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
2214 ' rows to CPCST for simulation purposes'
2215 );
2216 END IF;
2217
2218 ELSE
2219
2220 l_stmt_num := 120;
2221 x_return_status := '2';
2222 --period closed -> CPCS
2223 INSERT
2224 INTO cst_period_close_summary(
2225 acct_period_id,
2226 organization_id,
2227 cost_group_id,
2228 subinventory_code,
2229 inventory_item_id,
2230 accounted_value,
2231 rollback_value,
2232 rollback_quantity,
2233 rollback_onhand_value,
2234 rollback_intransit_value,
2235 accounted_onhand_value,
2236 accounted_intransit_value,
2237 onhand_value_discrepancy,
2238 intransit_value_discrepancy,
2239 cumulative_onhand_mta,
2240 cumulative_intransit_mta,
2241 last_update_date,
2242 last_updated_by,
2243 creation_date,
2244 creation_by)
2245 SELECT p_period_id,
2246 CIQT.organization_id,
2247 CIQT.cost_group_id,
2248 CIQT.subinventory_code,
2249 CIQT.inventory_item_id,
2250 SUM(DECODE(CIQT.qty_source,
2251 21,0,
2252 22,0,
2253 NVL(CIQT.accounted_value,0))) accounted_value,
2254 SUM(NVL(CIQT.rollback_qty,0))*NVL(CICT.item_cost,0) rollback_value,
2255 SUM(NVL(CIQT.rollback_qty,0)),
2256 SUM(DECODE(CIQT.qty_source,
2257 3,NVL(CIQT.rollback_qty,0),
2258 4,NVL(CIQT.rollback_qty,0),
2259 5,NVL(CIQT.rollback_qty,0),
2260 0))*NVL(CICT.item_cost,0) rollback_onhand_value,
2261 SUM(DECODE(CIQT.qty_source,
2262 6,NVL(CIQT.rollback_qty,0),
2263 7,NVL(CIQT.rollback_qty,0),
2264 8,NVL(CIQT.rollback_qty,0),
2265 0))*NVL(CICT.item_cost,0) rollback_intransit_value,
2266 SUM(DECODE(CIQT.qty_source,
2267 1,NVL(CIQT.accounted_value,0),
2268 11,NVL(CIQT.accounted_value,0),
2269 0)) accounted_onhand_value,
2270 SUM(DECODE(CIQT.qty_source,
2271 2,NVL(CIQT.accounted_value,0),
2272 12,NVL(CIQT.accounted_value,0),
2273 0)) accounted_intransit_value,
2274 SUM(DECODE(CIQT.qty_source,
2275 3,NVL(CIQT.rollback_qty,0),
2276 4,NVL(CIQT.rollback_qty,0),
2277 5,NVL(CIQT.rollback_qty,0),
2278 0))*NVL(CICT.item_cost,0) -
2279 SUM(DECODE(CIQT.qty_source,
2280 1,NVL(CIQT.accounted_value,0),
2281 11,NVL(CIQT.accounted_value,0),
2282 0)) onhand_value_discrepancy,
2283 SUM(DECODE(CIQT.qty_source,
2284 6,NVL(CIQT.rollback_qty,0),
2285 7,NVL(CIQT.rollback_qty,0),
2286 8,NVL(CIQT.rollback_qty,0),
2287 0))*NVL(CICT.item_cost,0) -
2288 SUM(DECODE(CIQT.qty_source,
2289 2,NVL(CIQT.accounted_value,0),
2290 12,NVL(CIQT.accounted_value,0),
2291 0)) intransit_value_discrepancy,
2292 SUM(DECODE(CIQT.qty_source,
2293 11,NVL(CIQT.accounted_value,0),
2294 21,NVL(CIQT.accounted_value,0),
2295 0)) cumulative_onhand_mta,
2296 SUM(DECODE(CIQT.qty_source,
2297 12,NVL(CIQT.accounted_value,0),
2298 22,NVL(CIQT.accounted_value,0),
2299 0)) cumulative_intransit_mta,
2300 SYSDATE,
2301 1,
2302 SYSDATE,
2303 1
2304 FROM cst_inv_qty_temp CIQT,
2305 cst_inv_cost_temp CICT
2306 WHERE CIQT.organization_id = p_org_id
2307 AND CIQT.organization_id = CICT.organization_id(+)
2308 AND NVL(CIQT.cost_group_id,-1) =
2309 NVL(CICT.cost_group_id,NVL(CIQT.cost_group_id,-1))
2310 AND CIQT.inventory_item_id = CICT.inventory_item_id(+)
2311 AND CICT.cost_source(+) = 2 -- PAST
2312 GROUP BY
2313 CIQT.organization_id,
2314 CIQT.cost_group_id,
2315 CIQT.subinventory_code,
2316 CIQT.inventory_item_id,
2317 CICT.item_cost;
2318
2319 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2320 THEN
2321 FND_MSG_PUB.Add_Exc_Msg(
2322 p_pkg_name => G_PKG_NAME,
2323 p_procedure_name => l_api_name,
2324 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
2325 ' rows to CPCS for org/period ' || p_org_id || '/' || p_period_id
2326 );
2327 END IF;
2328
2329 l_stmt_num := 130;
2330 UPDATE org_acct_periods
2331 SET summarized_flag = 'Y'
2332
2333 WHERE organization_id = p_org_id
2334 AND acct_period_id = p_period_id;
2335
2336 END IF;
2337
2338 EXCEPTION
2339 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2340 ROLLBACK TO Summarize_Period_PUB;
2341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2342 WHEN NO_PREV_SUMMARY_EXISTS THEN
2343 ROLLBACK TO Summarize_Period_PUB;
2344 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2348 p_pkg_name => G_PKG_NAME,
2345 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2346 THEN
2347 FND_MSG_PUB.Add_Exc_Msg(
2349 p_procedure_name => l_api_name,
2350 p_error_text => l_stmt_num||': Only first unsummarized period can be summarized'
2351 );
2352 END IF;
2353
2354 WHEN OTHERS THEN
2355 ROLLBACK TO Summarize_Period_PUB;
2356 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2357 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2358 THEN
2359 FND_MSG_PUB.Add_Exc_Msg(
2360 p_pkg_name => G_PKG_NAME,
2361 p_procedure_name => l_api_name,
2362 p_error_text => l_stmt_num||SUBSTR(SQLERRM,1,235)
2363 );
2364 END IF;
2365
2366 END Summarize_Period;
2367
2368 END CST_AccountingPeriod_PUB;