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