[Home] [Help]
PACKAGE BODY: APPS.GMF_PERIODCLOSE_PUB
Source
1 PACKAGE BODY GMF_PeriodClose_PUB AS
2 /* $Header: GMFPIAPB.pls 120.6 2006/07/25 10:27:36 jboppana noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMF_PeriodClose_PUB';
5 /* Package Level Constants */
6 C_MODULE CONSTANT VARCHAR2(80) := 'gmf.plsql.gmf_periodclose_pub';
7
8 C_LOG_FILE CONSTANT NUMBER(1) := 1;
9 C_OUT_FILE CONSTANT NUMBER(1) := 2;
10
11 /* forward declarations */
12 PROCEDURE Log_Msg(p_file IN NUMBER, p_msg IN VARCHAR2);
13
14 PROCEDURE Get_PendingTxnCount(
15 p_api_version IN NUMBER,
16 p_org_id IN INTEGER,
17 p_closing_period IN INTEGER,
18 p_sched_close_date IN DATE,
19 x_pend_receiving OUT NOCOPY INTEGER,
20 x_unproc_matl OUT NOCOPY INTEGER,
21 x_pend_matl OUT NOCOPY INTEGER,
22 x_pending_ship OUT NOCOPY INTEGER,
23 x_return_status OUT NOCOPY VARCHAR2
24 ) IS
25 l_tcount INTEGER;
26 l_in_rec_type WSH_INTEGRATION.ShpgUnTrxdInRecType;
27 l_out_rec_type WSH_INTEGRATION.ShpgUnTrxdOutRecType;
28 l_io_rec_type WSH_INTEGRATION.ShpgUnTrxdInOutRecType;
29 l_return_status VARCHAR2(200);
30 l_msg_count NUMBER;
31 l_msg_data VARCHAR2(200);
32 l_le_closing_fm_date DATE;
33 l_sched_close_date DATE;
34 l_legal_entity NUMBER := 0;
35
36 l_api_name CONSTANT VARCHAR2(30) := 'Get_PendingTxnCount';
37 l_api_version CONSTANT NUMBER := 1.0;
38 l_msg_level_threshold NUMBER;
39 l_stmt_num NUMBER := 0;
40 l_log_module VARCHAR2(80);
41
42 BEGIN
43
44 -- Standard Start of API savepoint
45 SAVEPOINT Get_PendingTcount_PUB;
46
47 l_log_module := c_module || '.Get_PendingTxnCount';
48 /* Log the parameters */
49 IF( fnd_log.level_procedure >= fnd_log.g_current_runtime_level )
50 THEN
51 fnd_log.string(fnd_log.level_procedure, l_log_module,'Begin...');
52 END IF;
53
54 Log_Msg(C_LOG_FILE, 'Get_PendingTxnCount.');
55 Log_Msg(C_LOG_FILE, 'Parameters: Api Version: ' || p_api_version||' org id :'||p_org_id ||
56 ' Closing period :' ||p_closing_period||' Schedule Close date: '||
57 TO_CHAR(p_sched_close_date,'yyyy/mm/dd hh24:mi:ss'));
58 -- Check for call compatibility
59 IF NOT FND_API.Compatible_API_Call
60 ( p_current_version_number => l_api_version,
61 p_caller_version_number => p_api_version,
62 p_api_name => l_api_name,
63 p_pkg_name => G_PKG_NAME
64 )
65 THEN
66 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
67 END IF;
68
69 -- Check for message level threshold
70 l_msg_level_threshold := FND_PROFILE.Value('FND_AS_MSG_LEVEL_THRESHOLD');
71
72 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
73 THEN
74 FND_MSG_PUB.Add_Exc_Msg(
75 p_pkg_name => G_PKG_NAME,
76 p_procedure_name => l_api_name,
77 p_error_text => SUBSTR(
78 l_stmt_num||':'||
79 p_org_id||','||
80 p_closing_period||','||
81 p_sched_close_date,
82 1,
83 240
84 )
85 );
86 END IF;
87
88 l_return_status := fnd_api.g_ret_sts_success;
89 l_msg_count := 0;
90 l_msg_data := '';
91
92 l_stmt_num := 5;
93 SELECT org_information2
94 INTO l_legal_entity
95 FROM hr_organization_information
96 WHERE organization_id = p_org_id
97 /** Bug#4496452 ANTHIYAG 08-May-2006 Start **/
98 AND org_information_context = 'Accounting Information';
99 /** Bug#4496452 ANTHIYAG 08-May-2006 End **/
100
101 IF( fnd_log.level_statement >= fnd_log.g_current_runtime_level )
102 THEN
103 fnd_log.string(fnd_log.level_statement, l_log_module,
104 ' Legal Entity Id is ' ||l_legal_entity||' for organization id'||p_org_id);
105 END IF;
106
107
108 l_stmt_num := 7;
109 l_sched_close_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
110 p_sched_close_date,
111 l_legal_entity);
112
113 l_sched_close_date := l_sched_close_date + 1 - (1/(24*3600));
114
115 /* Log the dates */
116 IF( fnd_log.level_statement >= fnd_log.g_current_runtime_level )
117 THEN
118 fnd_log.string(fnd_log.level_statement, l_log_module,
119 ' Per Sched. Close Date (le):' ||
120 TO_CHAR(p_sched_close_date,'yyyy/mm/dd hh24:mi:ss') ||
121 ' Per Sched. Close Date (db):' ||
122 TO_CHAR(l_sched_close_date,'yyyy/mm/dd hh24:mi:ss') );
123 END IF;
124
125 l_stmt_num := 10;
126 -- Unprocessed Material transactions (must resolve)
127 BEGIN
128 SELECT COUNT(*)
129 INTO l_tcount
130 FROM mtl_material_transactions_temp
131 WHERE organization_id = p_org_id
132 AND trunc(transaction_date) <= l_sched_close_date
133 AND NVL(transaction_status,0) <> 2; -- 2 indicates a save-only status
134
135 x_unproc_matl := l_tcount;
136
137 EXCEPTION
138 when NO_DATA_FOUND then
139 x_unproc_matl := 0;
140 when OTHERS then
141 x_unproc_matl := -1;
142 END;
143 IF( fnd_log.level_statement >= fnd_log.g_current_runtime_level )
144 THEN
145 fnd_log.string(fnd_log.level_statement, l_log_module,
146 'Count of Unprocessed Material transactions: ' ||x_unproc_matl );
147 END IF;
148
149
150 l_stmt_num := 20;
151 -- Pending shipping delivery transactions
152 -- This is either "must resolve" or "optional" depending on the client
153 -- extension introduced in ER 2342913.
154 BEGIN
155
156 l_stmt_num := 21;
157 SELECT period_start_date
158 INTO l_le_closing_fm_date
159 FROM org_acct_periods
160 WHERE acct_period_id = p_closing_period
161 AND organization_id = p_org_id;
162
163 l_stmt_num := 22;
164 l_in_rec_type.closing_fm_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
165 l_le_closing_fm_date,
166 l_legal_entity);
167
168 l_in_rec_type.api_version_number := 1.0;
169 l_in_rec_type.source_code := 'GMF';
170 l_in_rec_type.closing_to_date := l_sched_close_date;
171 l_in_rec_type.ORGANIZATION_ID := p_org_id;
172
173 l_stmt_num := 23;
174 Log_Msg(C_LOG_FILE, 'Calling WSH_INTEGRATION.Get_Untrxd_Shpg_Lines_Count.. ');
175 WSH_INTEGRATION.Get_Untrxd_Shpg_Lines_Count(
176 p_in_attributes => l_in_rec_type,
177 p_out_attributes => l_out_rec_type,
178 p_inout_attributes => l_io_rec_type,
179 x_return_status => l_return_status,
180 x_msg_count => l_msg_count,
181 x_msg_data => l_msg_data);
182 Log_Msg(C_LOG_FILE, 'Completed WSH_INTEGRATION.Get_Untrxd_Shpg_Lines_Count.. ');
183
184 IF l_return_status <> FND_API.g_ret_sts_success THEN
185 x_pending_ship := -1;
186 END IF;
187
188 x_pending_ship := l_out_rec_type.untrxd_rec_count;
189
190 END;
191
192 l_stmt_num := 30;
193 -- Unprocessed receiving transactions (optional)
194 BEGIN
195 SELECT COUNT(*)
196 INTO x_pend_receiving
197 FROM rcv_transactions_interface
198 WHERE to_organization_id = p_org_id
199 AND transaction_date <= l_sched_close_date
200 AND destination_type_code = 'INVENTORY';
201
202 EXCEPTION
203 when NO_DATA_FOUND then
204 x_pend_receiving := 0;
205 when OTHERS then
206 x_pend_receiving := -1;
207 END;
208
209 IF( fnd_log.level_statement >= fnd_log.g_current_runtime_level )
210 THEN
211 fnd_log.string(fnd_log.level_statement, l_log_module,
212 'Count of Unprocessed receiving transactions: ' ||x_pend_receiving );
213 END IF;
214
215 l_stmt_num := 40;
216 -- Pending material transactions (optional)
217 -- Need to ignore Ship Confirm Open Interface detail records.
218 -- these are stored in WSH_PICKING_DETAILS_INTERFACE, which is a view
219 -- on MTL_TRANSACTIONS_INTERFACE filtered by process_flag = 9
220 BEGIN
221
222 SELECT COUNT(*)
223 INTO x_pend_matl
224 FROM mtl_transactions_interface
225 WHERE organization_id = p_org_id
226 AND transaction_date <= l_sched_close_date
227 AND process_flag <> 9;
228
229 EXCEPTION
230 when NO_DATA_FOUND then
231 x_pend_matl := 0;
232 when OTHERS then
233 x_pend_matl := -1;
234 END;
235 IF( fnd_log.level_statement >= fnd_log.g_current_runtime_level )
236 THEN
237 fnd_log.string(fnd_log.level_statement, l_log_module,
238 'Count of Pending material transactions: ' ||x_pend_matl );
239 END IF;
240 /*
241 l_stmt_num := 20;
242 -- Uncosted Transactions (must resolve)
243 BEGIN
244 SELECT COUNT(*)
245 INTO x_uncost_matl
246 FROM mtl_material_transactions
247 WHERE organization_id = p_org_id
248 AND transaction_date <= l_sched_close_date
249 AND costed_flag is not null;
250
251 EXCEPTION
252 when NO_DATA_FOUND then
253 x_uncost_matl := 0;
254 when OTHERS then
255 x_uncost_matl := -1;
256 END;
257
258 l_stmt_num := 30;
259 -- Pending batch resource transactions (must resolve)
260 BEGIN
261 SELECT COUNT(*)
262 INTO x_uncost_rsrc_txns
263 FROM gme_resource_txns
264 -- WHERE organization_id = p_org_id
265 WHERE p_org_id = p_org_id
266 AND doc_type = 'PROD'
267 AND posted_ind != 0
268 AND completed_ind = 1
269 AND delete_mark = 0
270 AND trans_date >= l_in_rec_type.closing_fm_date
271 AND trans_date <= l_sched_close_date;
272
273 EXCEPTION
274 when NO_DATA_FOUND then
275 x_uncost_rsrc_txns := 0;
276 when OTHERS then
277 x_uncost_rsrc_txns := -1;
278 END;
279
280 l_stmt_num := 40;
281 -- Uncosted Production Batches (must resolve)
282 BEGIN
283 SELECT COUNT(*)
284 INTO x_uncost_prod_batches
285 FROM gme_batch_header
286 -- WHERE organization_id = p_org_id
287 WHERE p_org_id = p_org_id
288 AND gl_posted_ind != 0
289 AND delete_mark = 0
290 AND actual_cmplt_date >= l_in_rec_type.closing_fm_date
291 AND actual_cmplt_date <= l_sched_close_date;
292
293 EXCEPTION
294 when NO_DATA_FOUND then
295 x_uncost_prod_batches := 0;
296 when OTHERS then
297 x_uncost_prod_batches := -1;
298 END;
299 */
300 EXCEPTION
301 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
302 ROLLBACK TO Get_PendingTcount_PUB;
303 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
304 WHEN OTHERS THEN
305 ROLLBACK TO Get_PendingTcount_PUB;
306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
307 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
308 THEN
309 FND_MSG_PUB.Add_Exc_Msg(
310 p_pkg_name => G_PKG_NAME,
311 p_procedure_name => l_api_name,
312 p_error_text => l_stmt_num||SUBSTR(SQLERRM,1,235)
313 );
314 END IF;
315
316 END Get_PendingTxnCount;
317
318 PROCEDURE Verify_PeriodClose(
319 p_api_version IN NUMBER,
320 p_org_id IN NUMBER,
321 p_closing_acct_period_id IN NUMBER,
322 p_closing_end_date IN DATE,
323 x_open_period_exists OUT NOCOPY BOOLEAN,
324 x_proper_order OUT NOCOPY BOOLEAN,
325 x_end_date_is_past OUT NOCOPY BOOLEAN,
326 x_prompt_to_reclose OUT NOCOPY BOOLEAN,
327 x_return_status OUT NOCOPY VARCHAR2
328 ) IS
329
330 l_temp_id NUMBER;
331 l_le_sysdate DATE := NULL;
332 l_operating_unit NUMBER := 0;
333 l_log_module VARCHAR2(80);
334
335 -- Finds whether there are any prior open periods
336 CURSOR get_prior_open_period IS
337 SELECT acct_period_id
338 FROM org_acct_periods
339 WHERE organization_id = p_org_id
340 AND schedule_close_date = (SELECT MIN(oap1.schedule_close_date)
341 FROM org_acct_periods oap1, org_acct_periods oap2
342 WHERE
343 oap1.organization_id = p_org_id
344 AND (oap1.open_flag = 'Y' or oap1.open_flag = 'P')
345 and oap2.organization_id = oap1.organization_id
346 and oap1.schedule_close_date < oap2.schedule_close_date
347 and oap2.acct_period_id = p_closing_acct_period_id);
348
349
350 -- Finds whether the period is closed or not
351 CURSOR check_current_period_open IS
352 SELECT acct_period_id
353 FROM org_acct_periods
354 WHERE organization_id = p_org_id
355 AND acct_period_id = p_closing_acct_period_id
356 AND (open_flag = 'Y' or open_flag = 'P');
357
358
359 -- Finds the next period in org_acct_periods
360 /* CURSOR get_next_open_period IS
361 SELECT MIN(acct_period_id)
362 FROM org_acct_periods
363 WHERE organization_id = p_org_id
364 AND acct_period_id > p_closing_acct_period_id;
365
366 /* INVCONV
367 -- Checks if period is already in process of GL transfer
368 CURSOR get_download_in_process IS
369 SELECT acct_period_id
370 FROM org_gl_batches
371 WHERE organization_id = p_org_id
372 AND gl_batch_id = 0;
373 */
374
375 -- Checks if period is already in process of closing
376 /* rseshadr - Enabled the check below */
377 CURSOR check_reclose_period IS
378 SELECT acct_period_id
379 FROM org_acct_periods
380 WHERE organization_id = p_org_id
381 AND acct_period_id = p_closing_acct_period_id
382 AND period_close_date IS NOT NULL
383 AND open_flag = 'P';
384
385 l_api_name CONSTANT VARCHAR2(30) := 'Verify_PeriodClose';
386 l_api_version CONSTANT NUMBER := 1.0;
387 l_msg_level_threshold NUMBER;
388 l_stmt_num NUMBER := 0;
389
390 BEGIN
391
392 -- Standard Start of API savepoint
393 SAVEPOINT Verify_PeriodClose_PUB;
394 l_log_module := c_module || '.Verify_PeriodClose';
395 Log_Msg(C_LOG_FILE, 'Verify_PeriodClose.');
396 Log_Msg(C_LOG_FILE, 'Parameters: Api Version: ' || p_api_version||' org id :'||p_org_id ||
397 ' Closing Acct period :' ||p_closing_acct_period_id||' Closing End date: '||
398 TO_CHAR(p_closing_end_date ,'yyyy/mm/dd hh24:mi:ss'));
399 -- Check for call compatibility
400 IF NOT FND_API.Compatible_API_Call
401 ( p_current_version_number => l_api_version,
402 p_caller_version_number => p_api_version,
403 p_api_name => l_api_name,
404 p_pkg_name => G_PKG_NAME
405 )
406 THEN
407 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
408 END IF;
409
410 -- Check for message level threshold
411 l_msg_level_threshold := FND_PROFILE.Value('FND_AS_MSG_LEVEL_THRESHOLD');
412
413 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
414 THEN
415 FND_MSG_PUB.Add_Exc_Msg(
416 p_pkg_name => G_PKG_NAME,
417 p_procedure_name => l_api_name,
418 p_error_text => SUBSTR(
419 l_stmt_num||':'||
420 p_org_id||','||
421 p_closing_acct_period_id||','||
422 p_closing_end_date,
423 1,
424 240
425 )
426 );
427 END IF;
428
429 l_stmt_num := 10;
430
431 OPEN get_prior_open_period;
432 FETCH get_prior_open_period
433 INTO l_temp_id;
434
435 IF get_prior_open_period%FOUND THEN
436 x_proper_order := FALSE;
437 CLOSE get_prior_open_period;
438 GOTO procedure_end;
439 ELSE
440 x_proper_order := TRUE;
441 END IF;
442
443
444 CLOSE get_prior_open_period;
445
446 l_stmt_num := 20;
447 -- Check that the next period is open
448 OPEN check_current_period_open;
449 FETCH check_current_period_open
450 INTO l_temp_id;
451
452 IF check_current_period_open%FOUND THEN
453 x_open_period_exists := TRUE;
454 ELSE
455 x_open_period_exists := FALSE;
456 CLOSE check_current_period_open;
457 GOTO procedure_end;
458 END IF;
459
460 CLOSE check_current_period_open;
461
462 -- Check that the period's end date is < today,
463 -- adjusting for LE timezone.
464 l_stmt_num := 23;
465
466 SELECT org_information3
467 INTO l_operating_unit
468 FROM hr_organization_information
469 WHERE organization_id = p_org_id
470 AND org_information_context = 'Accounting Information';
471
472
473
474
475 IF( fnd_log.level_statement >= fnd_log.g_current_runtime_level )
476 THEN
477 fnd_log.string(fnd_log.level_statement, l_log_module,
478 ' Operating Unit is ' ||l_operating_unit||' for organization id'||p_org_id);
479 END IF;
480 l_stmt_num := 25;
481 l_le_sysdate := INV_LE_TIMEZONE_PUB.GET_LE_SYSDATE_FOR_OU(
482 l_operating_unit);
483
484 l_stmt_num := 27;
485 IF (p_closing_end_date < l_le_sysdate) THEN
486 x_end_date_is_past := TRUE;
487 ELSE
488 x_end_date_is_past := FALSE;
489 GOTO procedure_end;
490 END IF;
491
492 /* rseshadr - Added the check below */
493 l_stmt_num := 40;
494 -- See if this period is already processing. If so, prompt to reclose.
495 x_prompt_to_reclose := FALSE;
496
497 OPEN check_reclose_period;
498 FETCH check_reclose_period
499 INTO l_temp_id;
500
501 IF check_reclose_period%FOUND THEN
502 x_prompt_to_reclose := TRUE;
503 ELSE
504 x_prompt_to_reclose := FALSE;
505 END IF;
506
507 CLOSE check_reclose_period;
508 <<procedure_end >>
509 NULL;
510
511 EXCEPTION
512 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
513 ROLLBACK TO Verify_PeriodClose_PUB;
514 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
515 WHEN OTHERS THEN
516 ROLLBACK TO Verify_PeriodClose_PUB;
517 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
518 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
519 THEN
520 FND_MSG_PUB.Add_Exc_Msg(
521 p_pkg_name => G_PKG_NAME,
522 p_procedure_name => l_api_name,
523 p_error_text => l_stmt_num||SUBSTR(SQLERRM,1,235)
524 );
525 END IF;
526
527 END Verify_PeriodClose;
528
529 PROCEDURE Close_Period(
530 p_api_version IN NUMBER,
531 p_org_id IN NUMBER,
532 p_user_id IN NUMBER,
533 p_login_id IN NUMBER,
534 p_closing_acct_period_id IN NUMBER,
535 p_period_close_date IN DATE,
536 p_schedule_close_date IN DATE,
537 x_close_failed OUT NOCOPY BOOLEAN,
538 x_return_status OUT NOCOPY VARCHAR2,
539 x_req_id OUT NOCOPY NUMBER
540 ) IS
541
542 l_err_msg VARCHAR2(80);
543 l_indust VARCHAR2(10);
544 l_wip_installed BOOLEAN;
545 l_installation VARCHAR2(10);
546 l_return_code NUMBER;
547
548 l_api_name CONSTANT VARCHAR2(30) := 'Close_Period';
549 l_api_version CONSTANT NUMBER := 1.0;
550 l_msg_level_threshold NUMBER;
551 l_stmt_num NUMBER := 0;
552
553 l_req_id NUMBER := 0;
554 l_current_period_status VARCHAR2(1);
555
556 /* rseshadr */
557 e_perbal_failed EXCEPTION;
558
559 l_log_module VARCHAR2(80);
560
561
562 BEGIN
563
564 -- Standard Start of API savepoint
565 SAVEPOINT Close_Period_PUB;
566 l_log_module := c_module || '.Close_Period';
567 Log_Msg(C_LOG_FILE, 'Verify_PeriodClose.');
568 Log_Msg(C_LOG_FILE, 'Parameters: Api Version: ' || p_api_version||' org id :'||p_org_id ||
569 ' User id: '||p_user_id||' Org Id :' ||p_org_id||' Closing Acct period :' ||p_closing_acct_period_id||' Period Close date: '||
570 TO_CHAR(p_period_close_date ,'yyyy/mm/dd hh24:mi:ss')||' Schedule Close date: '||
571 TO_CHAR(p_schedule_close_date ,'yyyy/mm/dd hh24:mi:ss'));
572
573 -- Check for call compatibility
574 IF NOT FND_API.Compatible_API_Call
575 ( p_current_version_number => l_api_version,
576 p_caller_version_number => p_api_version,
577 p_api_name => l_api_name,
578 p_pkg_name => G_PKG_NAME
579 )
580 THEN
581 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
582 END IF;
583
584 -- Check for message level threshold
585 l_msg_level_threshold := FND_PROFILE.Value('FND_AS_MSG_LEVEL_THRESHOLD');
586
587 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
588 THEN
589 FND_MSG_PUB.Add_Exc_Msg(
590 p_pkg_name => G_PKG_NAME,
591 p_procedure_name => l_api_name,
592 p_error_text => SUBSTR(
593 l_stmt_num||':'||
594 p_org_id||','||
595 p_user_id||','||
596 p_login_id||','||
597 p_closing_acct_period_id||','||
598 p_period_close_date||','||
599 p_schedule_close_date||','||
600 1,
601 240
602 )
603 );
604 END IF;
605
606 l_stmt_num := 5;
607 -- Update period status to processing
608 /**
609 * rseshadr - Set status to Processing
610 * The period will be set to Closed once
611 * balances are compiled
612 **/
613 UPDATE org_acct_periods
614 SET
615 open_flag = 'P',
616 period_close_date = trunc(sysdate),
617 last_update_date = trunc(sysdate),
618 last_updated_by = p_user_id,
619 last_update_login = p_login_id
620 WHERE
621 acct_period_id = p_closing_acct_period_id AND
622 -- program level check to make sure that
623 -- the period is only closed once
624 open_flag = 'Y' AND
625 organization_id = p_org_id
626 ;
627
628 IF (SQL%NOTFOUND) THEN
629 RAISE NO_DATA_FOUND;
630 END IF;
631
632 --
633 -- we can submit the SLA accouting program for this org
634 --
635 /**
636 * rseshadr - Submit the period balance program
637 **/
638 l_req_id := fnd_request.submit_request(
639 application => 'GMF',
640 program => 'GMFPBAL',
641 description => NULL,
642 start_time => NULL,
643 sub_request => NULL,
644 argument1 => p_org_id,
645 argument2 => p_closing_acct_period_id
646 );
647
648 IF( l_req_id = 0 )
649 THEN
650 x_close_failed := TRUE;
651 RAISE e_perbal_failed;
652 END IF;
653
654 x_req_id := l_req_id;
655
656 COMMIT;
657
658 EXCEPTION
659 WHEN e_perbal_failed THEN
660 /* rseshadr */
661 ROLLBACK TO Close_Period_PUB;
662 x_return_status := FND_API.G_RET_STS_ERROR;
663 x_close_failed := TRUE;
664
665 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
666 ROLLBACK TO Close_Period_PUB;
667 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
668 x_close_failed := TRUE;
669
670 WHEN NO_DATA_FOUND THEN
671 ROLLBACK TO Close_Period_PUB;
672 x_close_failed := TRUE;
673
674 WHEN OTHERS THEN
675 ROLLBACK TO Close_Period_PUB;
676 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
677 x_close_failed := TRUE;
678 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
679 THEN
680 FND_MSG_PUB.Add_Exc_Msg(
681 p_pkg_name => G_PKG_NAME,
682 p_procedure_name => l_api_name,
683 p_error_text => l_stmt_num||SUBSTR(SQLERRM,1,235)
684 );
685 END IF;
686
687 END Close_Period;
688
689 /*****************************************************************************
690 * Procedure
691 * get_prev_inv_period_status
692 *
693 * DESCRIPTION
694 * Based on current OPM period end date, get prior Inventory Calendar and Period.
695 * Also, set x_close_status to TRUE if period is closed for all inv. orgs.
696 *
697 * Is getting called in
698 * 1. CMACPED.fmb and
699 * 2. SLA Accounting Pre-Processor Submission screen and wrapper.
700 *
701 * HISTORY
702 * 15-Nov-2005 Uday Moogala - Created
703 ******************************************************************************/
704 PROCEDURE get_prev_inv_period_status(
705 p_legal_entity_id IN VARCHAR2,
706 p_cost_type_id IN VARCHAR2,
707 p_period_end_date IN DATE,
708 x_close_status OUT NOCOPY BOOLEAN,
709 x_inv_period_year OUT NOCOPY NUMBER,
710 x_inv_period_num OUT NOCOPY NUMBER,
711 x_return_status OUT NOCOPY VARCHAR2,
712 x_errbuf OUT NOCOPY VARCHAR2
713 )
714 IS
715
716 CURSOR c_get_prev_period_end_date (
717 cp_le_id VARCHAR2,
718 cp_ct_id VARCHAR2,
719 cp_end_date DATE
720 )
721 IS
722 SELECT gps.period_id
723 FROM gmf_period_statuses gps
724 WHERE gps.legal_entity_id = cp_le_id
725 AND gps.cost_type_id = cp_ct_id
726 AND gps.end_date = cp_end_date
727 ORDER BY gps.end_date desc
728 ;
729
730 l_prev_period_id gmf_period_statuses.period_id%TYPE;
731 l_open_periods_cnt BINARY_INTEGER;
732
733 BEGIN
734
735 x_return_status := 'S';
736
737
738 --
739 -- First get prior period id based on the current
740 -- LE, CT and Period End Date
741 --
742 OPEN c_get_prev_period_end_date(p_legal_entity_id, p_cost_type_id, p_period_end_date);
743 FETCH c_get_prev_period_end_date INTO l_prev_period_id;
744 CLOSE c_get_prev_period_end_date;
745
746 IF l_prev_period_id IS NULL
747 THEN
748 x_return_status := 'E';
749 x_errbuf := 'No Prior Period for Legal Entity, Cost Type and Period End Date combination';
750 RETURN;
751 END IF;
752
753 --
754 -- Now using OPM's Prior Period, get the Inventory Period Year and Number.
755 --
756 SELECT oap.period_year, oap.period_num
757 INTO x_inv_period_year, x_inv_period_num
758 FROM org_acct_periods oap,
759 gmf_period_statuses gps,
760 hr_organization_information hoi
761 WHERE gps.period_id = l_prev_period_id
762 AND gps.legal_entity_id = hoi.org_information2
763 AND hoi.org_information_context = 'Accounting Information'
764 AND oap.organization_id = hoi.organization_id
765 AND oap.schedule_close_date = TRUNC(gps.end_date)
766 AND rownum = 1
767 ;
768
769 --
770 -- Now see whether period is closed for all Process Orgs.
771 --
772 SELECT SUM(decode(open_flag,'Y',1,'P',1, 0))
773 INTO l_open_periods_cnt
774 FROM org_acct_periods oap,
775 mtl_parameters mp,
776 hr_organization_information hoi
777 WHERE hoi.org_information2 = p_legal_entity_id
778 AND hoi.org_information_context = 'Accounting Information'
779 AND hoi.organization_id = oap.organization_id
780 AND hoi.organization_id = mp.organization_id
781 AND mp.process_enabled_flag = 'Y'
782 AND oap.period_year = x_inv_period_year
783 AND oap.period_num = x_inv_period_num
784 AND oap.schedule_close_date = TRUNC(p_period_end_date)
785 ;
786
787 IF l_open_periods_cnt > 0
788 THEN
789 x_close_status := FALSE;
790 ELSE
791 x_close_status := TRUE;
792 END IF;
793
794
795 EXCEPTION
796 WHEN NO_DATA_FOUND
797 THEN
798 x_return_status := 'E';
799 x_errbuf := 'No Inventory Prior Period found for Legal Entity, Cost Type and Period End Date combination';
800 END get_prev_inv_period_status;
801
802 /*======================================================================
803 * NAME
804 * Log_Msg
805 *
806 * DESCRIPTION
807 * Log messages to concurrent mgr log or output files
808 *
809 * HISTORY
810 * 03-Jun-05 Rajesh Seshadri created.
811 *
812 *====================================================================*/
813 PROCEDURE Log_Msg( p_file IN NUMBER, p_msg IN VARCHAR2)
814 IS
815
816 BEGIN
817
818 IF( p_file = 2 )
819 THEN
820 fnd_file.put_line(fnd_file.output, p_msg);
821 ELSE
822 fnd_file.put_line(fnd_file.log, p_msg);
823 END IF;
824
825 END Log_Msg;
826
827 END GMF_PeriodClose_PUB;