[Home] [Help]
PACKAGE BODY: APPS.GMF_PERIODCLOSE_PVT
Source
1 PACKAGE BODY GMF_PeriodClose_PVT AS
2 /* $Header: GMFVIAPB.pls 120.6 2006/11/09 13:57:18 anthiyag noship $ */
3 /*======================================================================+
4 | Copyright (c) 2005 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | GMF_PeriodClose_PVT |
10 | |
11 | DESCRIPTION |
12 | Period Close Private API for Process Organizations |
13 | Generates period ending balances for process organizations |
14 | |
15 | HISTORY |
16 | |
17 | 03-Jun-05 Rajesh Seshadri - Created |
18 +======================================================================*/
19
20 /* Package Level Constants */
21 C_MODULE CONSTANT VARCHAR2(80) := 'gmf.plsql.gmf_periodclose_pvt';
22
23 C_LOG_FILE CONSTANT NUMBER(1) := 1;
24 C_OUT_FILE CONSTANT NUMBER(1) := 2;
25
26 /* forward declarations */
27 PROCEDURE Log_Msg(p_file IN NUMBER, p_msg IN VARCHAR2);
28
29 PROCEDURE End_Process (
30 p_errstat IN VARCHAR2,
31 p_errmsg IN VARCHAR2
32 );
33
34 PROCEDURE Reset_Period_Status(
35 p_organization_id IN NUMBER,
36 p_acct_period_id IN NUMBER
37 );
38
39
40 /*======================================================================
41 * NAME
42 * Compile_Period_Balances
43 *
44 * DESCRIPTION
45 * Period Balances Concurrent Program for Process Orgs
46 *
47 * HISTORY
48 * 03-Jun-05 Rajesh Seshadri created.
49 *
50 *====================================================================*/
51 PROCEDURE Compile_Period_Balances (
52 x_errbuf OUT NOCOPY VARCHAR2,
53 x_retcode OUT NOCOPY VARCHAR2,
54 p_organization_id IN NUMBER,
55 p_closing_acct_period_id IN NUMBER
56 )
57 IS
58
59 l_log_module VARCHAR2(80);
60
61 l_return_status VARCHAR2(1);
62 l_return_msg VARCHAR2(240);
63
64 CURSOR c_per(p_organization_id IN NUMBER, p_acct_period_id IN NUMBER)
65 IS
66 SELECT
67 mp.organization_code,
68 UPPER(nvl(mp.process_enabled_flag,'N')) process_flag,
69 oacp.period_name,
70 oacp.period_close_date,
71 oacp.schedule_close_date,
72 oacp.open_flag
73 FROM
74 mtl_parameters mp,
75 org_acct_periods oacp
76 WHERE
77 mp.organization_id = p_organization_id AND
78 mp.organization_id = oacp.organization_id AND
79 oacp.acct_period_id = p_acct_period_id
80 ;
81
82 l_le_schedule_close_date DATE;
83 l_schedule_close_date DATE;
84 l_rollback_to_date DATE;
85
86 l_legal_entity_id NUMBER(15);
87
88 l_organization_code VARCHAR2(3);
89 l_org_process_flag VARCHAR2(1);
90 l_period_name org_acct_periods.period_name%TYPE;
91 l_le_period_close_date DATE;
92 l_period_close_date DATE;
93 l_per_open_flag VARCHAR2(1);
94
95 e_inv_per_bal_failed EXCEPTION;
96
97 BEGIN
98 SAVEPOINT s_compile_period_balances;
99
100 l_log_module := c_module || '.Compile_Period_Balances';
101
102 /* Uncomment to run from command line */
103 -- FND_FILE.PUT_NAMES('gmfviapb.log','gmfviapb.out','/appslog/opm_top/utl/opmmodv/log');
104
105 /* Log the parameters */
106 IF( fnd_log.level_procedure >= fnd_log.g_current_runtime_level )
107 THEN
108 fnd_log.string(fnd_log.level_procedure, l_log_module,'Begin...');
109 END IF;
110
111 Log_Msg(C_LOG_FILE, 'Compiling Period Balances for Process Orgs.');
112 Log_Msg(C_LOG_FILE, 'Parameters: Organization_id: ' || p_organization_id ||
113 ' Period Id: ' || p_closing_acct_period_id );
114
115 /*
116 * Validate the parameters
117 * Validate if the org is a process org
118 * All prior periods must be closed
119 * The current period must not be closed already
120 */
121
122 OPEN c_per(p_organization_id, p_closing_acct_period_id);
123 FETCH c_per INTO
124 l_organization_code, l_org_process_flag,
125 l_period_name, l_le_period_close_date, l_le_schedule_close_date,
126 l_per_open_flag;
127
128 IF( c_per%NOTFOUND )
129 THEN
130 CLOSE c_per;
131 l_return_msg := 'Error: Unable to retrieve period information';
132 Log_Msg(C_LOG_FILE, l_return_msg);
133 RAISE_APPLICATION_ERROR(-20101, l_return_msg);
134 END IF;
135 CLOSE c_per;
136
137 IF( l_org_process_flag <> 'Y' )
138 THEN
139 l_return_msg := 'Error: Org is not a Process Inventory Organization';
140 Log_Msg(C_LOG_FILE,l_return_msg);
141 RAISE_APPLICATION_ERROR(-20102, l_return_msg);
142 END IF;
143
144 IF( l_per_open_flag = 'N' AND l_le_period_close_date IS NOT NULL )
145 THEN
146 l_return_msg := 'Error: period is already closed';
147 Log_Msg(C_LOG_FILE,l_return_msg);
148 RAISE_APPLICATION_ERROR(-20103, l_return_msg);
149 END IF;
150
151 SELECT org_information2 INTO l_legal_entity_id
152 FROM hr_organization_information
153 WHERE organization_id = p_organization_id
154 AND org_information_context = 'Accounting Information';
155
156 /* Get the server date for schedule close date */
157 l_schedule_close_date := inv_le_timezone_pub.get_server_day_time_for_le(
158 p_le_date => l_le_schedule_close_date,
159 p_le_id => l_legal_entity_id );
160
161 l_schedule_close_date := l_schedule_close_date + 1 - 1/(24*3600);
162
163 /* Log the dates */
164 IF( fnd_log.level_statement >= fnd_log.g_current_runtime_level )
165 THEN
166 fnd_log.string(fnd_log.level_statement, l_log_module,
167 ' Per Sched. Close Date (le):' ||
168 TO_CHAR(l_le_schedule_close_date,'yyyy/mm/dd hh24:mi:ss') ||
169 ' Per Sched. Close Date (db):' ||
170 TO_CHAR(l_schedule_close_date,'yyyy/mm/dd hh24:mi:ss') );
171 END IF;
172
173 IF( SYSDATE <= l_schedule_close_date )
174 THEN
175 l_return_msg := 'Error: Period end date has not been reached';
176 Log_Msg(C_LOG_FILE,l_return_msg);
177 RAISE_APPLICATION_ERROR(-20105, l_return_msg);
178 END IF;
179
180 /* Bug#5652481 ANTHIYAG 09-Nov-2006 Start */
181 /* if there are already some prelim rows from some prior prelim close -- delete them*/
182 DELETE FROM gmf_period_balances
183 WHERE acct_period_id = p_closing_acct_period_id
184 AND organization_id = p_organization_id;
185 IF (SQL%NOTFOUND) THEN
186 NULL;
187 Log_Msg(C_LOG_FILE,' No rows found in gmf_period_balances to delete.');
188 ELSE
189 Log_Msg(C_LOG_FILE,' Deleted '||SQL%ROWCOUNT||' rows from gmf_period_balances.');
190 END IF;
191 /* Bug#5652481 ANTHIYAG 09-Nov-2006 End */
192
193 Log_Msg(C_LOG_FILE,'Beginning Inventory Balance compilation for ');
194 Log_Msg(C_LOG_FILE,'Organization: ' || l_organization_code || ' Period: ' ||
195 l_period_name);
196
197 Compile_Inv_Period_Balances(
198 p_organization_id => p_organization_id,
199 p_closing_acct_period_id => p_closing_acct_period_id,
200 p_schedule_close_date => l_schedule_close_date,
201 p_final_close => 1,
202 x_return_status => l_return_status,
203 x_return_msg => l_return_msg
204 );
205
206 IF( l_return_status <> FND_API.G_RET_STS_SUCCESS )
207 THEN
208 x_errbuf := l_return_msg;
209 x_retcode := 2;
210 RAISE e_inv_per_bal_failed;
211 END IF;
212
213 Log_Msg(C_LOG_FILE,'Inventory Balance compilation completed.');
214
215 /* All done, update period status to Closed */
216 UPDATE org_acct_periods
217 SET
218 open_flag = 'N',
219 summarized_flag = 'Y'
220 WHERE
221 organization_id = p_organization_id AND
222 acct_period_id = p_closing_acct_period_id;
223
224 COMMIT;
225
226 Log_Msg(C_LOG_FILE,'Inventory Period is closed');
227
228 /* Set conc mgr. return status */
229 x_retcode := 0;
230 x_errbuf := NULL;
231 End_Process('NORMAL', NULL);
232
233 IF( fnd_log.level_procedure >= fnd_log.g_current_runtime_level )
234 THEN
235 fnd_log.string(fnd_log.level_procedure, l_log_module,'...End');
236 END IF;
237
238 Log_Msg(C_LOG_FILE, 'Period Balances process completed');
239
240 EXCEPTION
241 WHEN e_inv_per_bal_failed THEN
242 ROLLBACK TO s_compile_period_balances;
243 Reset_Period_Status(p_organization_id, p_closing_acct_period_id);
244 x_retcode := 2;
245 x_errbuf := 'Compilation of inventory period balances failed';
246 End_Process('ERROR', x_errbuf);
247
248 WHEN others THEN
249 ROLLBACK TO s_compile_period_balances;
250 Reset_Period_Status(p_organization_id, p_closing_acct_period_id);
251 x_retcode := 2;
252 x_errbuf := SQLCODE || ' ' || SQLERRM;
253 End_Process('ERROR', x_errbuf);
254
255 END Compile_Period_Balances;
256
257 /*======================================================================
258 * NAME
259 * Compile_Inv_Period_Balances
260 *
261 * DESCRIPTION
262 * Generate Period Balances for Process Orgs
263 * Called from the period balances concurrent program above
264 *
265 * Validations:
266 * Org must be a process org
267 * Current period must be open and not pending close
268 * All prior periods must also be closed for this org
269 *
270 * Approach:
271 * First we get the onhand balance from MOQD
272 * Then we rollback transactions until we hit period end date
273 * Finally the balance is written to period balances table
274 * Intransit balances are also maintained for the org
275 *
276 * HISTORY
277 * 03-Jun-05 Rajesh Seshadri created.
278 *
279 *====================================================================*/
280 PROCEDURE Compile_Inv_Period_Balances (
281 p_organization_id IN NUMBER,
282 p_closing_acct_period_id IN NUMBER,
283 p_schedule_close_date IN DATE,
284 p_final_close IN NUMBER,
285 x_return_status OUT NOCOPY VARCHAR2,
286 x_return_msg OUT NOCOPY VARCHAR2
287 )
288 IS
289
290 l_log_module VARCHAR2(80);
291 l_rollback_to_date DATE;
292
293 /* onhand quantities */
294 CURSOR c_onhand(p_organization_id IN NUMBER)
295 IS
296 SELECT
297 moq.organization_id,
298 moq.cost_group_id,
299 moq.subinventory_code,
300 moq.inventory_item_id,
301 moq.locator_id,
302 moq.lot_number,
303 SUM(moq.primary_transaction_quantity) pri_qty, /*bug 5463187*/
304 SUM(NVL(moq.secondary_transaction_quantity,0)) sec_qty
305 FROM
306 mtl_onhand_quantities_detail moq
307 WHERE
308 moq.organization_id = p_organization_id AND
309 moq.is_consigned = 2 /* moq does not have sec qty */
310 GROUP BY
311 moq.organization_id,
312 moq.cost_group_id,
313 moq.subinventory_code,
314 moq.inventory_item_id,
315 moq.locator_id,
316 moq.lot_number
317 ;
318
319 r_onhand c_onhand%ROWTYPE;
320
321 /* Txns to rollback */
322 CURSOR c_txns(
323 p_organization_id IN NUMBER,
324 p_rollback_to_date IN DATE)
325 IS
326 SELECT /* lot controlled items */
327 mmt.organization_id,
328 mmt.cost_group_id,
329 mmt.subinventory_code,
330 mmt.inventory_item_id,
331 mmt.locator_id,
332 mtln.lot_number,
333 SUM(mtln.primary_quantity) pri_qty,
334 SUM(NVL(mtln.secondary_transaction_quantity,0)) sec_qty
335 FROM
336 mtl_transaction_lot_numbers mtln,
337 mtl_material_transactions mmt
338 WHERE
339 mmt.transaction_id = mtln.transaction_id AND
340 mmt.organization_id = p_organization_id AND
341 mmt.transaction_date > p_rollback_to_date AND
342 /* Ignore consigned */
343 mmt.organization_id = NVL(mmt.owning_organization_id, mmt.organization_id) AND
344 NVL(mmt.owning_tp_type,2) = 2 AND
345 /* Ignore Logical Txns */
346 NVL(mmt.logical_transaction,-1) <> 1
347 /* TBD: do we need to exclude any specific txns in process orgs */
348 GROUP BY
349 mmt.organization_id,
350 mmt.cost_group_id,
351 mmt.subinventory_code,
352 mmt.inventory_item_id,
353 mmt.locator_id,
354 mtln.lot_number
355 UNION ALL
356 SELECT /*+ INDEX(mmt mtl_material_transactions_n5) */ /* non lot controlled items */
357 mmt.organization_id,
358 mmt.cost_group_id,
359 mmt.subinventory_code,
360 mmt.inventory_item_id,
361 mmt.locator_id,
362 null lot_number,
363 SUM(mmt.primary_quantity) pri_qty,
364 SUM(NVL(mmt.secondary_transaction_quantity,0)) sec_qty
365 FROM
366 mtl_system_items_b msi,
367 mtl_material_transactions mmt
368 WHERE
369 mmt.inventory_item_id = msi.inventory_item_id AND
370 mmt.organization_id = msi.organization_id AND
371 msi.lot_control_code = 1 AND /* no lot control */
372 mmt.organization_id = p_organization_id AND
373 mmt.transaction_date > p_rollback_to_date AND
374 /* Ignore consigned */
375 mmt.organization_id = NVL(mmt.owning_organization_id, mmt.organization_id) AND
376 NVL(mmt.owning_tp_type,2) = 2 AND
377 /* Ignore Logical Txns */
378 NVL(mmt.logical_transaction,-1) <> 1
379 /* TBD: do we need to exclude any specific txns in process orgs */
380 GROUP BY
381 mmt.organization_id,
382 mmt.cost_group_id,
383 mmt.subinventory_code,
384 mmt.inventory_item_id,
385 mmt.locator_id
386 ;
387
388 r_txns c_txns%ROWTYPE;
389
390 /* retrieve balances from temp table */
391 CURSOR c_bal_tmp
392 (
393 p_organization_id NUMBER, /* Bug#5652481 ANTHIYAG 09-Nov-2006 */
394 p_acct_period_id NUMBER /* Bug#5652481 ANTHIYAG 09-Nov-2006 */
395 )
396 IS
397 SELECT
398 pbt.organization_id,
399 pbt.cost_group_id,
400 pbt.subinventory_code,
401 pbt.inventory_item_id,
402 pbt.locator_id,
403 pbt.lot_number,
404 SUM(pbt.primary_quantity) pri_qty,
405 SUM(NVL(pbt.secondary_quantity,0)) sec_qty
406 FROM
407 gmf_period_balances_gt pbt
408 WHERE
409 organization_id = p_organization_id /* Bug#5652481 ANTHIYAG 09-Nov-2006 */
410 AND acct_period_id = p_acct_period_id /* Bug#5652481 ANTHIYAG 09-Nov-2006 */
411 GROUP BY
412 pbt.organization_id,
413 pbt.cost_group_id,
414 pbt.subinventory_code,
415 pbt.inventory_item_id,
416 pbt.locator_id,
417 pbt.lot_number
418 HAVING
419 ( SUM(pbt.primary_quantity) <> 0 OR
420 SUM(NVL(pbt.secondary_quantity,0)) <> 0 )
421 ;
422
423 BEGIN
424
425 l_log_module := c_module || '.Compile_Inv_Period_Balances';
426
427 /* Log the parameters */
428 IF( fnd_log.level_procedure >= fnd_log.g_current_runtime_level )
429 THEN
430 fnd_log.string(fnd_log.level_procedure, l_log_module,'Begin...');
431 END IF;
432
433 /* Retrieve additional information */
434
435 /* Open cursors in read only mode */
436 COMMIT;
437 EXECUTE IMMEDIATE 'SET TRANSACTION READ ONLY';
438
439 OPEN c_onhand(p_organization_id);
440 /* SELECT SYSDATE INTO l_rollback_to_date FROM DUAL; */
441 l_rollback_to_date := p_schedule_close_date;
442 OPEN c_txns(p_organization_id, l_rollback_to_date);
443
444 COMMIT;
445
446 SAVEPOINT s_compile_inv_period_balances;
447
448 /* Retrieve current onhand balance and write it in temp table */
449 IF( fnd_log.level_statement >= fnd_log.g_current_runtime_level )
450 THEN
451 fnd_log.string(fnd_log.level_statement, 'l_log_module','Inserting Onhand Balances');
452 END IF;
453
454 <<onhand_balance>>
455 LOOP
456 FETCH c_onhand INTO r_onhand;
457 EXIT WHEN c_onhand%NOTFOUND;
458
459 /* insert into balances table */
460 INSERT INTO gmf_period_balances_gt (
461 source_type_id,
462 acct_period_id,
463 organization_id,
464 cost_group_id,
465 subinventory_code,
466 inventory_item_id,
467 lot_number,
468 locator_id,
469 primary_quantity,
470 secondary_quantity,
471 intransit_primary_quantity,
472 intransit_secondary_quantity,
473 accounted_value,
474 intransit_accounted_value
475 )
476 VALUES
477 (
478 1, /* onhand */
479 p_closing_acct_period_id,
480 p_organization_id,
481 r_onhand.cost_group_id,
482 r_onhand.subinventory_code,
483 r_onhand.inventory_item_id,
484 r_onhand.lot_number,
485 r_onhand.locator_id,
486 r_onhand.pri_qty,
487 r_onhand.sec_qty,
488 0, /* intransit pri qty */
489 0, /* intransit sec qty */
490 0, /* accounted_value */
491 0 /* intransit accounted value */
492 );
493
494 END LOOP onhand_balance;
495
496 /* Rollback transactions until we hit the period close date */
497 IF( fnd_log.level_statement >= fnd_log.g_current_runtime_level )
498 THEN
499 fnd_log.string(fnd_log.level_statement, 'l_log_module','Rolling back transactions');
500 END IF;
501
502 <<mtl_transactions>>
503 LOOP
504 FETCH c_txns INTO r_txns;
505 EXIT WHEN c_txns%NOTFOUND;
506
507 INSERT INTO gmf_period_balances_gt (
508 source_type_id,
509 acct_period_id,
510 organization_id,
511 cost_group_id,
512 subinventory_code,
513 inventory_item_id,
514 lot_number,
515 locator_id,
516 primary_quantity,
517 secondary_quantity,
518 intransit_primary_quantity,
519 intransit_secondary_quantity,
520 accounted_value,
521 intransit_accounted_value
522 )
523 VALUES
524 (
525 2, /* txns */
526 p_closing_acct_period_id,
527 p_organization_id,
528 r_txns.cost_group_id,
529 r_txns.subinventory_code,
530 r_txns.inventory_item_id,
531 r_txns.lot_number,
532 r_txns.locator_id,
533 -1 * r_txns.pri_qty,
534 -1 * r_txns.sec_qty,
535 0, /* intransit pri qty */
536 0, /* intransit sec qty */
537 0, /* accounted_value */
538 0 /* intransit accounted value */
539 );
540
541 END LOOP mtl_transactions;
542
543 /* Insert/Update balances table */
544 IF( fnd_log.level_statement >= fnd_log.g_current_runtime_level )
545 THEN
546 fnd_log.string(fnd_log.level_statement, 'l_log_module','Inserting into balances');
547 END IF;
548
549 FOR r_bal_tmp IN c_bal_tmp (p_organization_id => p_organization_id, p_acct_period_id => p_closing_acct_period_id) /* Bug#5652481 ANTHIYAG 09-Nov-2006 */
550 LOOP
551
552 INSERT INTO gmf_period_balances (
553 period_balance_id,
554 acct_period_id,
555 organization_id,
556 cost_group_id,
557 subinventory_code,
558 inventory_item_id,
559 lot_number,
560 locator_id,
561 primary_quantity,
562 secondary_quantity,
563 intransit_primary_quantity,
564 intransit_secondary_quantity,
565 accounted_value,
566 intransit_accounted_value,
567 costed_flag,
568 creation_date,
569 created_by,
570 last_update_date,
571 last_updated_by,
572 last_update_login,
573 request_id,
574 program_application_id,
575 program_id,
576 program_update_date,
577 period_close_status
578 )
579 VALUES
580 (
581 gmf_period_balances_s.nextval,
582 p_closing_acct_period_id,
583 p_organization_id,
584 r_bal_tmp.cost_group_id,
585 r_bal_tmp.subinventory_code,
586 r_bal_tmp.inventory_item_id,
587 r_bal_tmp.lot_number,
588 r_bal_tmp.locator_id,
589 r_bal_tmp.pri_qty,
590 r_bal_tmp.sec_qty,
591 0, /* intransit pri qty */
592 0, /* intransit sec qty */
593 0, /* accounted value */
594 0, /* intransit accounted value */
595 'N', /* costed flag */
596 sysdate,
597 fnd_global.user_id,
598 sysdate,
599 fnd_global.user_id,
600 fnd_global.login_id,
601 fnd_global.conc_request_id,
602 fnd_global.prog_appl_id,
603 fnd_global.conc_program_id,
604 sysdate,
605 decode(p_final_close,1,'F','P')
606 );
607
608 END LOOP;
609
610 /* commit and exit */
611 COMMIT;
612
613 x_return_status := 'S';
614 x_return_msg := NULL;
615
616 IF( fnd_log.level_procedure >= fnd_log.g_current_runtime_level )
617 THEN
618 fnd_log.string(fnd_log.level_procedure, 'l_log_module','...End');
619 END IF;
620
621 END Compile_Inv_Period_Balances;
622
623 /*======================================================================
624 * NAME
625 * Log_Msg
626 *
627 * DESCRIPTION
628 * Log messages to concurrent mgr log or output files
629 *
630 * HISTORY
631 * 03-Jun-05 Rajesh Seshadri created.
632 *
633 *====================================================================*/
634 PROCEDURE Log_Msg( p_file IN NUMBER, p_msg IN VARCHAR2)
635 IS
636
637 BEGIN
638
639 IF( p_file = 2 )
640 THEN
641 fnd_file.put_line(fnd_file.output, p_msg);
642 ELSE
643 fnd_file.put_line(fnd_file.log, p_msg);
644 END IF;
645
646 END Log_Msg;
647
648 /*======================================================================
649 * NAME
650 * End_Process
651 *
652 * DESCRIPTION
653 * Log messages to concurrent mgr log or output files
654 *
655 * INPUT PARAMETERS
656 * p_errstat - Completion status, must be one of
657 * 'NORMAL', 'WARNING', or 'ERROR'
658 * p_errmsg - Completion message to be passed back
659 *
660 * HISTORY
661 * 03-Jun-05 Rajesh Seshadri created.
662 *
663 *====================================================================*/
664 PROCEDURE End_Process (
665 p_errstat IN VARCHAR2,
666 p_errmsg IN VARCHAR2
667 )
668 IS
669 l_retval BOOLEAN;
670 BEGIN
671
672 l_retval := fnd_concurrent.set_completion_status(p_errstat,p_errmsg);
673
674 END End_Process;
675
676
677 /*======================================================================
678 * NAME
679 * Reset_Period_Status
680 *
681 * DESCRIPTION
682 * Reset_Period_Status
683 *
684 * INPUT PARAMETERS
685 * organization_id, acct_period_id
686 *====================================================================*/
687
688 PROCEDURE Reset_Period_Status(
689 p_organization_id IN NUMBER,
690 p_acct_period_id IN NUMBER
691 )
692 IS
693
694 BEGIN
695 UPDATE org_acct_periods
696 SET
697 open_flag = 'Y',
698 summarized_flag = NULL
699 WHERE
700 organization_id = p_organization_id AND
701 p_acct_period_id = p_acct_period_id;
702
703 END Reset_Period_Status;
704
705
706
707 /*======================================================================
708 * NAME
709 * Compile_Prelim_Period_Balances
710 *
711 * DESCRIPTION
712 * Close Inv period -- either prelim close or final close
713 *
714 * INPUT PARAMETERS
715 * legal entity id
716 * Fisacl year
717 * Fiscal Period
718 * Final Close --- Y if it is final close, N if it is Prelim Close
719 * Org Code -- can be null. If NULL close the period for all the organizations in the LE.
720 If value is passed, close the period for the entered organization.
721 *
722 * HISTORY
723 * 19-Jun-06 Jahnavi Boppana created.
724 *
725 *====================================================================*/
726
727
728 PROCEDURE Compile_Period_Balances_LE(
729 x_errbuf OUT NOCOPY VARCHAR2,
730 x_retcode OUT NOCOPY VARCHAR2,
731 p_le_id IN NUMBER,
732 p_fiscal_year IN NUMBER,
733 p_fiscal_period IN NUMBER,
734 p_final_close IN VARCHAR2,
735 p_org_code IN VARCHAR2
736 )
737 IS
738 l_log_module VARCHAR2(80);
739
740 l_return_status VARCHAR2(20);
741 l_return_msg VARCHAR2(240);
742
743 l_period_set_name VARCHAR2(15);
744
745 l_row_count NUMBER;
746 l_return_status1 NUMBER;
747
748 l_final_close NUMBER;
749
750 l_organization_id NUMBER;
751 l_acct_period_id NUMBER;
752 l_start_date DATE;
753 l_close_date DATE;
754
755 l_proper_order BOOLEAN := TRUE;
756 l_open_period_exists BOOLEAN := TRUE;
757 l_end_date_is_past BOOLEAN := TRUE;
758 l_prompt_to_reclose BOOLEAN := TRUE;
759 l_prior_acct_period_id NUMBER;
760
761 l_msg_count NUMBER;
762 l_period_close VARCHAR2(1);
763 l_msg_data VARCHAR2(30);
764 l_close_failed BOOLEAN;
765 l_period_name VARCHAR2(15);
766
767 l_server_close_date DATE;
768 l_le_server_offset NUMBER;
769 l_pend_receiving NUMBER;
770 l_unproc_matl NUMBER;
771 l_pend_matl NUMBER;
772 l_pend_ship NUMBER;
773 l_uncost_matl NUMBER;
774
775 l_user_id fnd_user.user_id%TYPE;
776 l_user fnd_user.user_name%TYPE;
777 l_login_id NUMBER;
778 l_prog_appl_id NUMBER;
779 l_program_id NUMBER;
780 l_request_id NUMBER;
781 l_failed NUMBER;
782
783 gmf_process_org_gt_failed EXCEPTION;
784
785
786
787 CURSOR get_process_org IS
788 SELECT organization_code,
789 organization_id
790 FROM gmf_process_organizations_gt
791 ORDER BY organization_code;
792
793 /* get the period to close*/
794 CURSOR cur_period_to_close(p_org_id IN NUMBER, p_year IN NUMBER, p_period IN NUMBER, p_period_set_name in varchar2)
795 IS
796 SELECT acct_period_id, period_start_date start_date, schedule_close_date close_date, period_name
797 FROM ORG_ACCT_PERIODS
798 WHERE organization_id = p_org_id
799 and period_set_name = p_period_Set_name
800 and period_year = p_year
801 and period_num = p_period
802 ORDER by schedule_close_date;
803
804 BEGIN
805
806 SAVEPOINT Compile_Period_Balances_LE;
807
808
809 l_log_module := c_module || '.Compile_Period_Balances_LE';
810
811 /* Uncomment to run from command line */
812 -- FND_FILE.PUT_NAMES('gmfviapb.log','gmfviapb.out','/appslog/opm_top/utl/opmmodv/log');
813
814 /* Log the parameters */
815 IF( fnd_log.level_procedure >= fnd_log.g_current_runtime_level )
816 THEN
817 fnd_log.string(fnd_log.level_procedure, l_log_module,'Begin...');
818 END IF;
819
820
821 Log_Msg(C_LOG_FILE, 'Compiling Period Balances for Process Orgs.');
822 IF p_final_close = 'Y'
823 THEN
824 Log_Msg(C_LOG_FILE, 'The period is selected for final close');
825 ELSE
826 Log_Msg(C_LOG_FILE, 'The period is selected for preliminary close');
827 END IF;
828
829 Log_Msg(C_LOG_FILE, 'Parameters: Legal Entity_Id : '||p_le_id||' Organization_id: ' || p_org_code ||
830 ' Fiscal Year: '||p_fiscal_year||' Fiscal Period: ' ||p_fiscal_period||' Final Close: '||p_final_close);
831
832
833 l_failed := 0;
834
835 IF( p_le_id IS NULL OR
836 p_fiscal_year IS NULL OR
837 p_fiscal_period IS NULL OR
838 p_final_close IS NULL)
839 THEN
840 l_return_msg := 'Not all input parameters entered';
841 Log_Msg(C_LOG_FILE, l_return_msg);
842 RAISE_APPLICATION_ERROR(-20101, l_return_msg);
843 END IF;
844
845 l_user_id := FND_PROFILE.VALUE('USER_ID');
846
847 SELECT user_name INTO l_user
848 FROM fnd_user
849 WHERE user_id = l_user_id;
850
851 l_login_id := FND_GLOBAL.LOGIN_ID;
852 l_prog_appl_id := FND_GLOBAL.PROG_APPL_ID;
853 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
854 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
855
856
857 IF p_final_close = 'Y'
858 THEN
859 l_final_close := 1;
860 ELSE
861 l_final_close := 0;
862 END IF;
863
864
865 begin
866 select b.period_set_name
867 into l_period_Set_name
868 from gmf_fiscal_policies a,
869 gl_ledgers b
870 where a.legal_entity_id = p_le_id
871 and b.ledger_id = a.ledger_id;
872 exception
873 when others then
874 l_period_Set_name := NULL;
875 end;
876
877 Log_Msg(C_LOG_FILE,'Period Set Name for Legal Entity => ' ||p_le_id||' is '||l_period_Set_name);
878
879
880 GMF_ORGANIZATIONS_PKG.get_process_organizations
881 (
882 p_Legal_Entity_id => p_le_id,
883 p_From_Orgn_Code => p_org_code,
884 p_To_Orgn_Code => p_org_code,
885 x_Row_Count => l_row_count,
886 x_Return_Status => l_return_status1
887 );
888
889 IF (l_return_status1 <> 0) THEN
890 RAISE gmf_process_org_gt_failed;
891 END IF;
892
893
894 Log_Msg(C_LOG_FILE, 'Loaded '||l_row_count||' Process Organizations for Legal Entity => ' ||p_le_id);
895
896
897
898 FOR org_rec IN get_process_org LOOP
899 SAVEPOINT Compile_Period_Balances_LE;
900 l_organization_id := org_rec.organization_id;
901 Log_Msg(C_LOG_FILE, 'Processing Period Close for Organization : ' || org_rec.organization_code);
902
903 OPEN cur_period_to_close(l_organization_id, p_fiscal_year, p_fiscal_period, l_period_set_name);
904 FETCH cur_period_to_close INTO l_acct_period_id,l_start_date,l_close_date, l_period_name;
905 IF cur_period_to_close%notfound THEN
906 Log_Msg(C_LOG_FILE, ' There is no Open/Closed Period for Organization : ' || org_rec.organization_code||
907 '. The period may be not yet opened.');
908 CLOSE cur_period_to_close;
909 GOTO period_close;
910 END IF;
911 CLOSE cur_period_to_close;
912 Log_Msg(C_LOG_FILE,' Processing Period : ' || l_period_name);
913
914 GMF_PeriodClose_PUB.Verify_PeriodClose
915 (
916 p_api_version => 1.0,
917 p_org_id => l_organization_id,
918 p_closing_acct_period_id => l_acct_period_id,
919 p_closing_end_date => l_close_date,
920 x_open_period_exists => l_open_period_exists,
921 x_proper_order => l_proper_order,
922 x_end_date_is_past => l_end_date_is_past,
923 x_prompt_to_reclose => l_prompt_to_reclose,
924 x_return_status => l_return_status
925 );
926
927 IF (NOT l_proper_order) AND l_final_close = 1 THEN
928 Log_Msg(C_LOG_FILE,' Period ' || l_period_name ||' cannot be closed until prior open periods are closed');
929 Log_Msg(C_LOG_FILE,' Skipping closing of this period because of the above error');
930 l_failed := l_failed + 1;
931 GOTO period_close;
932 ELSIF (NOT l_open_period_exists) THEN
933 Log_Msg(C_LOG_FILE,' Cannot close the period '|| l_period_name || '. Period is already final closed for this organization');
934 Log_Msg(C_LOG_FILE,' Skipping closing of this period because of the above error');
935 l_failed := l_failed + 1;
936 GOTO period_close;
937 ELSIF (NOT l_end_date_is_past) THEN
938 l_server_close_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE
939 (l_close_date + .99999, p_le_id);
940
941 l_le_server_offset := l_close_date + .99999 - l_server_close_date;
942
943 if (l_start_date - l_le_server_offset > sysdate) then
944 Log_Msg(C_LOG_FILE,' Cannot close this period because it starts in the future.');
945 Log_Msg(C_LOG_FILE,' Skipping closing of this period because of the above error');
946 l_failed := l_failed + 1;
947 GOTO period_close;
948 end if;
949 end if;
950
951
952
953 /* we reach here if the 1) period is not already final closed
954 2) prior periods are closed in case of final close
955 3) period doesnot start in future.*/
956
957 IF l_final_close = 1 THEN /*check for pending transactions only for final close*/
958 GMF_PeriodClose_PUB.Get_PendingTxnCount(
959 p_api_version => 1.0,
960 p_org_id => l_organization_id,
961 p_closing_period => l_acct_period_id,
962 p_sched_close_date => l_close_date,
963 x_pend_receiving => l_pend_receiving,
964 x_unproc_matl => l_unproc_matl,
965 x_pend_matl => l_pend_matl,
966 x_pending_ship => l_pend_ship,
967 x_return_status => l_return_status);
968
969 IF l_return_status <> fnd_api.g_ret_sts_success THEN
970 Log_Msg(C_LOG_FILE,' GMF_PeriodClose_PUB.Get_PendingTxnCount failed');
971 Log_Msg(C_LOG_FILE,' Skipping closing of this period because of the above error');
972 l_failed := l_failed + 1;
973 GOTO period_close;
974 END IF;
975
976 IF (l_pend_receiving > 0 OR l_unproc_matl > 0 OR l_pend_matl > 0 OR l_pend_ship > 0 ) THEN
977 IF l_pend_receiving > 0 THEN
978 Log_Msg(C_LOG_FILE,' Pending receiving transactions: '||l_pend_receiving);
979 END IF;
980 IF l_unproc_matl > 0 THEN
981 Log_Msg(C_LOG_FILE,' Unprocessed Material transactions: '||l_unproc_matl);
982 END IF;
983 IF l_pend_matl > 0 THEN
984 Log_Msg(C_LOG_FILE,' Pending Material transactions: '||l_pend_matl);
985 END IF;
986 IF l_pend_ship > 0 THEN
987 Log_Msg(C_LOG_FILE,' Pending Shipping transactions: '||l_pend_ship);
988 END IF;
989 Log_Msg(C_LOG_FILE,' Skipping closing of this period because of the existing pending transactions');
990 l_failed := l_failed + 1;
991 GOTO period_close;
992 END IF;
993
994 INV_LOGICAL_TRANSACTIONS_PUB.Check_Accounting_Period_Close
995 (
996 x_return_status => l_return_status,
997 x_msg_count => l_msg_count,
998 x_msg_data => l_msg_data,
999 x_period_close => l_period_close,
1000 p_api_version_number => 1.0,
1001 p_init_msg_lst => 'F',
1002 p_organization_id => l_organization_id,
1003 p_org_id => null,
1004 p_period_start_date => l_start_date,
1005 p_period_end_date => l_close_date
1006 );
1007
1008 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1009 Log_Msg(C_LOG_FILE,' INV_LOGICAL_TRANSACTIONS_PUB.Check_Accounting_Period_Close failed');
1010 Log_Msg(C_LOG_FILE,' Skipping closing of this period because of the above error');
1011 l_failed := l_failed + 1;
1012 GOTO period_close;
1013 END IF;
1014 IF (l_period_close = 'N') THEN
1015 Log_Msg(C_LOG_FILE,' Deferred INV Logical transactions exist for Period with Start Date '||to_char(l_start_date,'dd-mon-yyyy hh24:mi:ss')||' and End Date '||to_char(l_close_date,'dd-mon-yyyy hh24:mi:ss'));
1016 Log_Msg(C_LOG_FILE,' Skipping closing of this period because of the above error');
1017 l_failed := l_failed + 1;
1018 GOTO period_close;
1019 END IF;
1020
1021 /* you are here if all the checks are successful*/
1022 UPDATE org_acct_periods
1023 SET
1024 open_flag = 'P',
1025 period_close_date = trunc(sysdate),
1026 last_update_date = trunc(sysdate),
1027 last_updated_by = l_user_id,
1028 last_update_login = l_login_id
1029 WHERE
1030 acct_period_id = l_acct_period_id AND
1031 -- program level check to make sure that
1032 -- the period is only closed once
1033 open_flag = 'Y' AND
1034 organization_id = l_organization_id AND
1035 period_set_name = l_period_set_name;
1036
1037 IF (SQL%NOTFOUND) THEN
1038 Log_Msg(C_LOG_FILE,' Failed Updating org_acct_periods to pending.');
1039 RAISE NO_DATA_FOUND;
1040 END IF;
1041
1042 END IF;
1043
1044 /* we reach here if the 1) period is not already final closed
1045 2) prior periods are closed in case of final close
1046 3) period doesnot start in future.
1047 4) no pending txns in case of final close
1048 5) no deferred inv logical txns in case of final close.*/
1049
1050 l_server_close_date := inv_le_timezone_pub.get_server_day_time_for_le(l_close_date,p_le_id);
1051 l_server_close_date := l_server_close_date + 1 - 1/(24*3600);
1052
1053 /* Log the dates */
1054 Log_Msg(C_LOG_FILE,' Per Sched. Close Date (le):' || TO_CHAR(l_close_date,'yyyy/mm/dd hh24:mi:ss') ||
1055 ' Per Sched. Close Date (db):' || TO_CHAR(l_server_close_date,'yyyy/mm/dd hh24:mi:ss') );
1056
1057
1058 /* dont check for end_daate if it is prelim close*/
1059 IF l_final_close = 1 THEN
1060 IF( SYSDATE <= l_server_close_date )
1061 THEN
1062 l_return_msg := ' Error: Period end date has not been reached';
1063 Log_Msg(C_LOG_FILE,l_return_msg);
1064 Reset_Period_Status(l_organization_id, l_acct_period_id);
1065 l_failed := l_failed + 1;
1066 GOTO period_close;
1067 END IF;
1068 END IF;
1069
1070 /* if there are already some prelim rows from some prior prelim close -- delete them*/
1071 DELETE FROM gmf_period_balances
1072 WHERE acct_period_id = l_acct_period_id
1073 AND organization_id = l_organization_id;
1074 IF (SQL%NOTFOUND) THEN
1075 NULL;
1076 Log_Msg(C_LOG_FILE,' No rows found in gmf_period_balances to delete.');
1077 ELSE
1078 Log_Msg(C_LOG_FILE,' Deleted '||SQL%ROWCOUNT||' rows from gmf_period_balances.');
1079 END IF;
1080
1081 Log_Msg(C_LOG_FILE,' Beginning Inventory Balance compilation for Organization: ' || org_rec.organization_code || ' Period: ' ||l_period_name);
1082
1083 Compile_Inv_Period_Balances(
1084 p_organization_id => l_organization_id,
1085 p_closing_acct_period_id => l_acct_period_id,
1086 p_schedule_close_date => l_server_close_date,
1087 p_final_close => l_final_close,
1088 x_return_status => l_return_status,
1089 x_return_msg => l_return_msg
1090 );
1091
1092 IF( l_return_status <> FND_API.G_RET_STS_SUCCESS )
1093 THEN
1094 x_errbuf := l_return_msg;
1095 x_retcode := 2;
1096 Reset_Period_Status(l_organization_id, l_acct_period_id);
1097 Log_Msg(C_LOG_FILE,' Error: Compilation of inventory period balances failed');
1098 l_failed := l_failed + 1;
1099 GOTO period_close;
1100 /*RAISE e_inv_per_bal_failed;*/
1101 END IF;
1102
1103 SAVEPOINT Compile_Period_Balances_LE;
1104
1105 Log_Msg(C_LOG_FILE,' Inventory Balance compilation completed.');
1106
1107 /* All done, update period status to Closed */
1108 IF l_final_close = 1 THEN
1109 UPDATE org_acct_periods
1110 SET
1111 open_flag = 'N',
1112 summarized_flag = 'Y'
1113 WHERE
1114 organization_id = l_organization_id AND
1115 acct_period_id = l_acct_period_id;
1116
1117 IF (SQL%NOTFOUND) THEN
1118 Log_Msg(C_LOG_FILE,' Failed Updating org_acct_periods to closed');
1119 RAISE NO_DATA_FOUND;
1120 END IF;
1121
1122 END IF;
1123
1124
1125 COMMIT;
1126
1127 Log_Msg(C_LOG_FILE,' Inventory Period is closed');
1128 <<period_close>>
1129 NULL;
1130 END LOOP;
1131 Log_Msg(C_LOG_FILE, 'Compile Period Balances LE process completed');
1132 /* Set conc mgr. return status */
1133 IF l_failed > 0 THEN /* period close for atleast one org failed*/
1134 x_retcode := 2;
1135 x_errbuf := 'Period Close process failed for one of the orgs.';
1136 Log_Msg(C_LOG_FILE,'Period Close process failed for one of the orgs.');
1137 End_Process('ERROR', x_errbuf);
1138 ELSE
1139 x_retcode := 1;
1140 x_errbuf := NULL;
1141 End_Process('NORMAL', NULL);
1142
1143 END IF;
1144
1145
1146
1147
1148
1149 EXCEPTION
1150 WHEN gmf_process_org_gt_failed THEN
1151 ROLLBACK TO Compile_Period_Balances_LE;
1152 x_retcode := 2;
1153 x_errbuf := 'Loading gmf_process_organizations_gt failed';
1154 End_Process('ERROR', x_errbuf);
1155
1156 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1157 ROLLBACK TO Compile_Period_Balances_LE;
1158 Reset_Period_Status(l_organization_id, l_acct_period_id);
1159 x_retcode := 2;
1160 x_errbuf := 'Unexpected error';
1161 End_Process('ERROR', x_errbuf);
1162
1163 WHEN NO_DATA_FOUND THEN
1164 ROLLBACK TO Compile_Period_Balances_LE;
1165 Reset_Period_Status(l_organization_id, l_acct_period_id);
1166 x_retcode := 2;
1167 x_errbuf := 'No data found';
1168 End_Process('ERROR', x_errbuf);
1169
1170 WHEN others THEN
1171 ROLLBACK TO Compile_Period_Balances_LE;
1172 Reset_Period_Status(l_organization_id, l_acct_period_id);
1173 x_retcode := 2;
1174 x_errbuf := SQLCODE || ' ' || SQLERRM;
1175 End_Process('ERROR', x_errbuf);
1176
1177 END Compile_Period_Balances_LE;
1178
1179
1180 END GMF_PeriodClose_PVT;