[Home] [Help]
PACKAGE BODY: APPS.GMF_SUBLEDGER_PKG
Source
1 PACKAGE BODY gmf_subledger_pkg AS
2 /* $Header: gmfslupb.pls 120.10 2006/12/12 18:13:30 anthiyag noship $ */
3
4 /*****************************************************************************
5 * PACKAGE
6 * gmf_subledger_pkg
7 *
8 * DESCRIPTION
9 * Subledger Update Process pkg
10 *
11 * CONTENTS
12 * PROCEDURE test_update ( ... )
13 *
14 * NOTES
15 * scheduled_on in control table is always sysdate since we are called
16 * at the appropriate time by conc.mgr.
17 *
18 * HISTORY
19 * 24-Dec-2002 Rajesh Seshadri - Created
20 * 14-Apr-2004 Dinesh Vadivel - Bug # 3196846
21 * Added Lot Cost Adjustment related changes TDD 13.13.5
22 *
23 * TBD
24 * - messages using msg dict.
25 *
26 ******************************************************************************/
27
28 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
29 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
30 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
31 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
32 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
33 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
34 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
35 G_MODULE_NAME CONSTANT VARCHAR2(50) :='GMF.PLSQL.GMF_SUBLEDGER_PKG.';
36
37 g_log_msg FND_LOG_MESSAGES.message_text%TYPE;
38
39 g_legal_entity_id NUMBER;
40 g_legal_entity_name VARCHAR2(250);
41 g_ledger_id NUMBER;
42 g_ledger_name VARCHAR2(250);
43 g_process_category VARCHAR2(250);
44
45 g_cost_type_id NUMBER; /* New cost type terminology */
46 g_cost_type_code VARCHAR2(40); /* cm_mthd_mst.cost_mthd_code */
47 g_cost_method_type NUMBER; /* cm_mthd_mst.cost_type */
48 g_cost_method VARCHAR2(100); /* lkup 'GMF_COST_METHOD' meaning */
49 g_default_cost_type_id NUMBER;
50
51 g_crev_curr_cost_type_id NUMBER;
52 g_crev_curr_cost_mthd_code VARCHAR2(30);
53 g_crev_curr_calendar VARCHAR2(30);
54 g_crev_curr_period VARCHAR2(30);
55 g_crev_curr_period_id NUMBER;
56
57 g_crev_prev_cost_type_id NUMBER;
58 g_crev_prev_cost_mthd VARCHAR2(30);
59 g_crev_prev_calendar VARCHAR2(30);
60 g_crev_prev_period VARCHAR2(30);
61 g_crev_prev_period_id NUMBER;
62
63 g_crev_gl_trans_date DATE;
64
65 /* forward declarations */
66 PROCEDURE end_process (
67 p_errstat IN VARCHAR2,
68 p_errmsg IN VARCHAR2
69 );
70
71 PROCEDURE inter_mod_cal_conv(
72 x_inv_fiscal_year OUT NOCOPY NUMBER,
73 x_inv_period OUT NOCOPY NUMBER,
74 x_inv_per_synch OUT NOCOPY VARCHAR2,
75 x_inv_per_start_date OUT NOCOPY DATE,
76 x_inv_per_end_date OUT NOCOPY DATE,
77 x_retstatus OUT NOCOPY VARCHAR2,
78 x_errbuf OUT NOCOPY VARCHAR2 );
79
80 /************************************************************************************************
81 * PROCEDURE
82 * update_process
83 *
84 * DESCRIPTION
85 * Wrapper to the subledger update concurrent program. Accepts the
86 * parameters to the subledger process, validates it, inserts the control
87 * record, then submits the subledger process as a child request. It puts
88 * itself in a paused state till the program completes and returns the
89 * status back to the ccm.
90 *
91 * INPUT PARAMETERS
92 * All parameters to the conc. request
93 *
94 * HISTORY
95 * 26-Dec-2002 Rajesh Seshadri
96 *
97 * 14-Apr-2004 Dinesh Vadivel Bug # 3196846 Lot Cost Adjsutment related changes TDD 13.13.5
98 * Now allowing the process to be submitted for CM source even if
99 * GL Cost Method is a Lot Cost Method.Also, skipping the validation of
100 * "revaluation parameter" for Lot Cost Method.
101 *************************************************************************************************/
102 PROCEDURE update_process(
103 x_errbuf OUT NOCOPY VARCHAR2
104 , x_retcode OUT NOCOPY VARCHAR2
105 , p_legal_entity_id IN VARCHAR2
106 , p_ledger_id IN VARCHAR2
107 , p_cost_type_id IN VARCHAR2
108 , p_gl_fiscal_year IN VARCHAR2
109 , p_gl_period IN VARCHAR2
110 , p_test_posting IN VARCHAR2
111 , p_open_gl_date IN VARCHAR2
112 , p_posting_start_date IN VARCHAR2
113 , p_posting_end_date IN VARCHAR2
114 , p_post_if_no_cost IN VARCHAR2
115 , p_process_category IN VARCHAR2
116 , p_crev_curr_calendar IN VARCHAR2
117 , p_crev_curr_period IN VARCHAR2
118 , p_crev_prev_cost_type_id IN VARCHAR2
119 , p_crev_prev_calendar IN VARCHAR2
120 , p_crev_prev_period IN VARCHAR2
121 , p_crev_gl_trans_date IN VARCHAR2
122 /* start invconv umoogala
123 p_post_cm IN VARCHAR2,
124 p_post_ic IN VARCHAR2,
125 p_post_om IN VARCHAR2,
126 p_post_pm IN VARCHAR2,
127 p_post_pur IN VARCHAR2
128 */
129 ) AS
130
131 l_closed_per_ind NUMBER(3) := 0;
132 l_open_gl_fiscal_year NUMBER(15);
133 l_open_gl_period NUMBER(15);
134
135 /* Start INVCONV umoogala
136 l_inv_fiscal_year ic_cldr_dtl.fiscal_year%TYPE;
137 l_inv_period ic_cldr_dtl.period%TYPE;
138 */
139 l_inv_fiscal_year org_acct_periods.period_year%TYPE;
140 l_inv_period org_acct_periods.period_num%TYPE;
141
142 l_subledger_ref_no NUMBER(15) := NULL;
143
144 l_conc_id NUMBER(15) := 0;
145 l_msg_text VARCHAR2(2000);
146
147 l_retstatus VARCHAR2(1);
148 l_errbuf VARCHAR2(2000);
149
150 /* conc status */
151 l_conc_req_status BOOLEAN;
152 l_conc_phase VARCHAR2(240);
153 l_conc_status VARCHAR2(240);
154 l_conc_dev_phase VARCHAR2(240);
155 l_conc_dev_status VARCHAR2(240);
156 l_conc_msg VARCHAR2(240);
157
158 /* req globals for sub-re quests */
159 l_req_data VARCHAR2(10);
160 l_child_conc_id NUMBER(15);
161
162 /* Start INVCONV umoogala
163 l_crev_curr_mthd VARCHAR2(4);
164 l_crev_curr_calendar VARCHAR2(4);
165 l_crev_curr_period VARCHAR2(4);
166 l_crev_prev_mthd VARCHAR2(4);
167 l_crev_prev_calendar VARCHAR2(4);
168 l_crev_prev_period VARCHAR2(4);
169 */
170 l_crev_curr_cost_type_id NUMBER;
171 l_crev_curr_period_id NUMBER;
172
173 l_crev_prev_cost_type_id NUMBER;
174 l_crev_prev_period_id NUMBER;
175
176 l_crev_gl_trans_date DATE;
177 l_crev_inv_prev_period_id NUMBER;
178
179 l_lot_actual_cost NUMBER;
180 l_post_cm VARCHAR2(2);
181
182 /* exceptions */
183 e_all_done EXCEPTION;
184 e_req_submit_error EXCEPTION;
185 e_validation_failed EXCEPTION;
186 e_ctlrec_failed EXCEPTION;
187 e_reval_error EXCEPTION;
188
189
190 /* Start INVCONV umoogala
191 CURSOR c_fiscal_policy(cp_co_code VARCHAR2)
192 IS
193 SELECT NVL(mthd.lot_actual_cost,0)
194 FROM gl_plcy_mst plcy, cm_mthd_mst mthd
195 WHERE plcy.co_code = cp_co_code
196 ;
197 */
198 l_procedure_name CONSTANT VARCHAR2(30) := 'UPDATE_PROCESS';
199
200 BEGIN
201
202 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
203
204 g_log_msg := 'Begin of procedure '|| l_procedure_name;
205
206 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
207 THEN
208 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
209 END IF;
210
211
212 l_req_data := FND_CONC_GLOBAL.REQUEST_DATA;
213
214 IF( l_req_data IS NOT NULL )
215 THEN
216 l_child_conc_id := TO_NUMBER(l_req_data);
217
218 /* now get the status for this req id */
219 l_conc_req_status := fnd_concurrent.get_request_status(
220 request_id => l_child_conc_id,
221 appl_shortname => NULL,
222 program => NULL,
223 phase => l_conc_phase,
224 status => l_conc_status,
225 dev_phase => l_conc_dev_phase,
226 dev_status => l_conc_dev_status,
227 message => l_conc_msg)
228 ;
229
230 x_errbuf := l_conc_msg;
231
232 IF( l_conc_dev_phase = 'COMPLETE' )
233 THEN
234 IF( l_conc_dev_status = 'NORMAL' )
235 THEN
236 end_process('NORMAL',l_conc_msg);
237 x_retcode := 0;
238 ELSIF( l_conc_dev_status = 'WARNING' )
239 THEN
240 end_process('WARNING', l_conc_msg);
241 x_retcode := 1;
242 ELSE
243 end_process('ERROR', l_conc_msg);
244 x_retcode := 3;
245 END IF;
246 ELSE
247 /* What to do for all other phases? raise a warning */
248 end_process('WARNING', l_conc_dev_phase || ':' ||
249 l_conc_dev_status || ': ' || l_conc_msg);
250 x_retcode := 1;
251 END IF;
252
253 RETURN;
254 END IF;
255
256
257 --
258 -- Populate Global variables
259 --
260 g_log_msg := l_procedure_name || ': Populate Global variables';
261 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
262 THEN
263 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
264 END IF;
265
266 g_legal_entity_id := TO_NUMBER(p_legal_entity_id);
267 g_ledger_id := TO_NUMBER(p_ledger_id);
268 g_cost_type_id := TO_NUMBER(p_cost_type_id);
269
270
271 /* Bug#5708175 ANTHIYAG 12-Dec-2006 Start */
272 /*************************************
273 SELECT le.organization_name, led.name,
274 mthd.cost_type, mthd.cost_mthd_code, lk.meaning,
275 mthd.default_lot_cost_type_id
276 INTO g_legal_entity_name, g_ledger_name,
277 g_cost_method_type, g_cost_type_code, g_cost_method,
278 g_default_cost_type_id
279 FROM org_organization_definitions le, gl_ledgers led,
280 cm_mthd_mst mthd, gem_lookups lk
281 WHERE le.organization_id = g_legal_entity_id
282 AND led.ledger_id = g_ledger_id
283 AND mthd.cost_type_id = g_cost_type_id
284 AND lk.lookup_type = 'GMF_COST_METHOD'
285 AND lk.lookup_code = mthd.cost_type;
286 **************************************/
287 BEGIN
288 SELECT gle.legal_entity_name
289 INTO g_legal_entity_name
290 FROM gmf_legal_entities gle
291 WHERE gle.legal_entity_id = g_legal_entity_id ;
292 EXCEPTION
293 WHEN NO_DATA_FOUND then
294 g_log_msg := l_procedure_name || ': No data found in gmf_legal_entities query';
295 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
296 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
297 END IF;
298 RAISE;
299 END;
300 BEGIN
301 SELECT gl.name
302 INTO g_ledger_name
303 FROM gl_ledgers gl
304 WHERE gl.ledger_id = g_ledger_id;
305 EXCEPTION
306 WHEN NO_DATA_FOUND then
307 g_log_msg := l_procedure_name || ': No data found in gl_ledgers query';
308 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
309 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
310 END IF;
311 RAISE;
312 END;
313 BEGIN
314 SELECT mthd.cost_type,
315 mthd.cost_mthd_code,
316 lk.meaning,
317 nvl(mthd.default_lot_cost_type_id, -1)
318 INTO g_cost_method_type,
319 g_cost_type_code,
320 g_cost_method,
321 g_default_cost_type_id
322 FROM cm_mthd_mst mthd,
323 gem_lookups lk
324 WHERE mthd.cost_type_id = g_cost_type_id
325 AND lk.lookup_type = 'GMF_COST_METHOD'
326 AND lk.lookup_code = mthd.cost_type ;
327 EXCEPTION
328 WHEN NO_DATA_FOUND then
329 g_log_msg := l_procedure_name || ': No data found in cost types query';
330 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
331 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
332 END IF;
333 RAISE;
334 END;
335 /* Bug#5708175 ANTHIYAG 12-Dec-2006 End */
336
337 g_process_category := p_process_category;
338
339 g_crev_curr_cost_type_id := g_cost_type_id;
340 g_crev_curr_cost_mthd_code := g_cost_type_code;
341 g_crev_curr_calendar := p_crev_curr_calendar;
342 g_crev_curr_period := p_crev_curr_period;
343
344 g_crev_prev_cost_type_id := TO_NUMBER(p_crev_prev_cost_type_id);
345 g_crev_prev_calendar := p_crev_prev_calendar;
346 g_crev_prev_period := p_crev_prev_period;
347
348 g_crev_gl_trans_date := FND_DATE.canonical_to_date(p_crev_gl_trans_date);
349
350
351 IF g_crev_curr_calendar IS NULL OR g_crev_curr_period IS NULL OR
352 g_crev_prev_cost_type_id IS NULL OR g_crev_prev_calendar IS NULL OR
353 g_crev_prev_period IS NULL
354 THEN
355
356 -- IF p_post_cm = 1 or p_process_category = 'REVALUATION_TRANSACTIONS'
357 IF p_process_category = 'REVALUATION_TRANSACTIONS'
358 THEN
359 fnd_message.set_name('GMF','CM_NO_RVAL_PARMS');
360 x_errbuf := fnd_message.get;
361 RAISE e_reval_error;
362 END IF;
363
364 g_crev_curr_period_id := NULL;
365 g_crev_prev_period_id := NULL;
366 g_crev_prev_cost_mthd := NULL;
367
368 ELSE
369
370 g_log_msg := l_procedure_name || ': query cost reval data';
371 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
372 THEN
373 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
374 END IF;
375
376
377 SELECT curr.period_id, prev.period_id,
378 mthd.cost_mthd_code
379 INTO g_crev_curr_period_id, g_crev_prev_period_id,
380 g_crev_prev_cost_mthd
381 FROM gmf_period_statuses curr, gmf_period_statuses prev, cm_mthd_mst mthd
382 WHERE curr.legal_entity_id = g_legal_entity_id
383 AND curr.cost_type_id = g_cost_type_id
384 AND curr.calendar_code = g_crev_curr_calendar
385 AND curr.period_code = g_crev_curr_period
386 AND prev.legal_entity_id = g_legal_entity_id
387 AND prev.cost_type_id = g_crev_prev_cost_type_id
388 AND prev.calendar_code = g_crev_prev_calendar
389 AND prev.period_code = g_crev_prev_period
390 AND mthd.cost_type_id = g_crev_prev_cost_type_id
391 ;
392 END IF;
393
394 --
395 -- End of -- Populate Global variables
396 --
397
398
399 gmf_util.log('Starting GMF SLA Pre-Processor program:');
400 gmf_util.log(' Legal Entity =>' || g_legal_entity_name);
401 gmf_util.log(' Ledger =>' || g_ledger_name);
402 gmf_util.log(' Cost Type =>' || g_cost_type_code);
403 gmf_util.log(' Cost Method =>' || g_cost_method);
404 gmf_util.log(' Process Category =>' || g_process_category);
405
406 gmf_util.log(' gl_fiscal_year =>' || p_gl_fiscal_year);
407 gmf_util.log(' gl_period =>' || p_gl_period);
408 gmf_util.log(' test_posting =>' || p_test_posting);
409
410 gmf_util.log(' open_gl_date =>' || p_open_gl_date);
411
412 gmf_util.log(' posting_start_date =>' || p_posting_start_date);
413 gmf_util.log(' posting_end_date =>' || p_posting_end_date);
414
415 gmf_util.log(' post_if_no_cost =>' || p_post_if_no_cost);
416
417 /* Start INVCONV umoogala
418 gmf_util.log(' post CM =>' || p_post_cm);
419 gmf_util.log(' post IC =>' || p_post_ic);
420 gmf_util.log(' post OM =>' || p_post_om);
421 gmf_util.log(' post OP =>' || p_post_op);
422 gmf_util.log(' post PM =>' || p_post_pm);
423 gmf_util.log(' post PO =>' || p_post_po);
424 gmf_util.log(' post PUR =>' || p_post_pur);
425 */
426
427 /* Validate input params */
428 g_log_msg := l_procedure_name || ': calling validate_parameters procedure';
429 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
430 THEN
431 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
432 END IF;
433
434 validate_parameters(
435 p_gl_fiscal_year => p_gl_fiscal_year,
436 p_gl_period => p_gl_period,
437 p_test_posting => p_test_posting,
438 p_posting_start_date => p_posting_start_date,
439 p_posting_end_date => p_posting_end_date,
440 p_open_gl_date => p_open_gl_date,
441 /* Start INVCONV umoogala
442 p_co_code => p_co_code,
443 p_post_cm => p_post_cm,
444 p_post_ic => p_post_ic,
445 p_post_om => p_post_om,
446 p_post_op => p_post_op,
447 p_post_pm => p_post_pm,
448 p_post_po => p_post_po,
449 p_post_pur => p_post_pur,
450 */
451 x_closed_per_ind => l_closed_per_ind,
452 x_crev_gl_trans_date => l_crev_gl_trans_date,
453 x_open_gl_fiscal_year => l_open_gl_fiscal_year,
454 x_open_gl_period => l_open_gl_period,
455 /* Start INVCONV umoogala
456 x_crev_curr_mthd => l_crev_curr_mthd,
457 x_crev_curr_calendar => l_crev_curr_calendar,
458 x_crev_curr_period => l_crev_curr_period,
459 x_crev_prev_mthd => l_crev_prev_mthd,
460 x_crev_prev_calendar => l_crev_prev_calendar,
461 x_crev_prev_period => l_crev_prev_period,
462 */
463 x_inv_fiscal_year => l_inv_fiscal_year,
464 x_inv_period => l_inv_period,
465 x_retstatus => l_retstatus,
466 x_errbuf => l_errbuf
467 );
468
469 IF( l_retstatus <> 'S' )
470 THEN
471 x_errbuf := l_errbuf;
472 RAISE e_validation_failed;
473 END IF;
474
475 /* insert the control record */
476 g_log_msg := l_procedure_name || ': inserting the control record into gl_subr_sta';
477 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
478 THEN
479 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
480 END IF;
481
482
483 insert_control_record(
484 p_user_id => FND_GLOBAL.user_id,
485 p_gl_fiscal_year => TO_NUMBER(p_gl_fiscal_year),
486 p_gl_period => TO_NUMBER(p_gl_period),
487 p_posting_start_date => FND_DATE.canonical_to_date(p_posting_start_date),
488 p_posting_end_date => FND_DATE.canonical_to_date(p_posting_end_date),
489 p_test_posting => p_test_posting,
490 /* Start INVCONV umoogala
491 p_co_code => p_co_code,
492 p_post_cm => l_post_cm, --p_post_cm,
493 p_post_ic => p_post_ic,
494 p_post_om => p_post_om,
495 p_post_op => p_post_op,
496 p_post_pm => p_post_pm,
497 p_post_po => p_post_po,
498 p_post_pur => p_post_pur,
499 */
500 p_closed_per_ind => l_closed_per_ind,
501 p_open_gl_date => FND_DATE.canonical_to_date(p_open_gl_date),
502 p_crev_gl_trans_date => l_crev_gl_trans_date,
503 p_open_gl_fiscal_year => l_open_gl_fiscal_year,
504 p_open_gl_period => l_open_gl_period,
505 p_post_if_no_cost => p_post_if_no_cost,
506 p_default_language => USERENV('LANG'),
507 /* Start INVCONV umoogala
508 p_crev_curr_mthd => l_crev_curr_mthd,
509 P_crev_curr_calendar => l_crev_curr_calendar,
510 p_crev_curr_period => l_crev_curr_period,
511 p_crev_prev_mthd => l_crev_prev_mthd,
512 p_crev_prev_calendar => l_crev_prev_calendar,
513 p_crev_prev_period => l_crev_prev_period,
514 */
515 p_inv_fiscal_year => l_inv_fiscal_year,
516 p_inv_period => l_inv_period,
517 x_subledger_ref_no => l_subledger_ref_no,
518 x_retstatus => l_retstatus,
519 x_errbuf => l_errbuf
520 );
521
522 IF( l_retstatus <> 'S' )
523 THEN
524 x_errbuf := l_errbuf;
525 RAISE e_ctlrec_failed;
526 END IF;
527
528 COMMIT;
529
530 g_log_msg := l_procedure_name || ': Submitting concurrent request';
531 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
532 THEN
533 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
534 END IF;
535
536
537 l_conc_id := FND_REQUEST.SUBMIT_REQUEST(
538 'GMF','GMFXUPD','OPM Subledger Accounting Pre-Processor',
539 fnd_date.date_to_canonical(SYSDATE),
540 TRUE, '-r',TO_CHAR(l_subledger_ref_no),
541 CHR(0),'','','','','','','','','','','','',
542 '','','','','','','','','','','','','','','',
543 '','','','','','','','','','','','','','','',
544 '','','','','','','','','','','','','','','',
545 '','','','','','','','','','','','','','','',
546 '','','','','','','','','','','','','','','',
547 '','','','','','','','','','');
548
549 IF (l_conc_id = 0)
550 THEN
551 l_msg_text := FND_MESSAGE.get;
552 RAISE e_req_submit_error;
553 ELSE
554 UPDATE gl_subr_sta
555 SET request_id = l_conc_id
556 WHERE reference_no = l_subledger_ref_no;
557
558 COMMIT;
559 END IF;
560
561 fnd_message.set_name('GMF','GL_NOTE_REF_NO');
562 fnd_message.set_token('S1', l_subledger_ref_no);
563 l_msg_text := fnd_message.get;
564 gmf_util.log(l_msg_text);
565
566 g_log_msg := l_procedure_name || ': concurrent request submitted. Reference#: ' || l_subledger_ref_no;
567 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
568 THEN
569 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
570 END IF;
571
572 /* wait for child request to complete */
573 FND_CONC_GLOBAL.SET_REQ_GLOBALS(
574 conc_status => 'PAUSED',
575 request_data => l_conc_id);
576
577 x_retcode := 0;
578 x_errbuf := 'Concurrent request submitted. Reference#: ' || TO_CHAR(l_subledger_ref_no) ;
579
580 EXCEPTION
581 WHEN e_req_submit_error THEN
582 x_retcode := 3;
583 x_errbuf := l_msg_text;
584
585 g_log_msg := l_procedure_name || ': e_req_submit_error: ' || x_errbuf;
586 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
587 THEN
588 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
589 END IF;
590
591 end_process('ERROR', l_msg_text);
592
593 WHEN e_validation_failed THEN
594 x_retcode := 3;
595
596 g_log_msg := 'e_validation_failed: ' || x_errbuf;
597 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
598 THEN
599 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
600 END IF;
601
602 end_process('ERROR', l_errbuf);
603
604 WHEN e_ctlrec_failed THEN
605 x_retcode := 3;
606
607 g_log_msg := 'e_ctlrec_failed: ' || x_errbuf;
608 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
609 THEN
610 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
611 END IF;
612
613 end_process('ERROR', l_errbuf);
614
615 WHEN e_reval_error THEN
616 x_retcode := 3;
617
618 g_log_msg := 'error: ' || x_errbuf;
619 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
620 THEN
621 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
622 END IF;
623
624 WHEN others THEN
625 x_retcode := 3;
626 x_errbuf := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
627
628 g_log_msg := 'in when other. error: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
629 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
630 THEN
631 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
632 END IF;
633
634 end_process('ERROR', x_errbuf);
635 -- TBD RAISE;
636
637 END update_process;
638
639 /*****************************************************************************
640 * PROCEDURE
641 * end_process
642 *
643 * DESCRIPTION
644 * Sets the concurrent manager completion status
645 *
646 * INPUT PARAMETERS
647 * pi_errstat - Completion status, must be one of 'NORMAL', 'WARNING', or
648 * 'ERROR'
649 * pi_errmsg - Completion message to be passed back
650 *
651 * HISTORY
652 * 26-Dec-2002 Rajesh Seshadri
653 *
654 ******************************************************************************/
655
656 PROCEDURE end_process (
657 p_errstat IN VARCHAR2,
658 p_errmsg IN VARCHAR2
659 )
660 AS
661 l_retval BOOLEAN;
662 l_procedure_name CONSTANT VARCHAR2(30) := 'END_PROCESS';
663 BEGIN
664
665 g_log_msg := 'Begin of procedure '|| l_procedure_name;
666
667 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
668 THEN
669 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
670 END IF;
671
672 l_retval := fnd_concurrent.set_completion_status(p_errstat,p_errmsg);
673
674 END end_process;
675
676 /*********************************************************************************************
677 * PROCEDURE
678 * validate_parameters
679 *
680 * DESCRIPTION
681 * Validates the input parameters
682 *
683 * INPUT PARAMETERS
684 * All parameters to the conc. request
685 *
686 * HISTORY
687 * 26-Dec-2002 Rajesh Seshadri
688 *
689 * 14-Apr-2004 Dinesh Vadivel Bug #3196846 Lot Cost Adjustment related changes. TDD 13.13.5
690 * Now allowing the process to be submitted for CM source even
691 * if GL Cost Method is a Lot Cost Method. Also, skipping the
692 * validation of "revaluation parameter" for Lot Cost Method.
693 * 30-Oct-2006 Anand Thiyagarajan Bug#5623121
694 * Modifications to convert the GL start and End dates, which are considered to be in LE Timezone
695 * to Server Timezone before comparing them with the Posting Start and end dates passed in Server Time Zones.
696 *
697 *********************************************************************************************/
698 PROCEDURE validate_parameters(
699 p_gl_fiscal_year IN VARCHAR2,
700 p_gl_period IN VARCHAR2,
701 p_test_posting IN VARCHAR2,
702 p_open_gl_date IN VARCHAR2,
703 p_posting_start_date IN VARCHAR2,
704 p_posting_end_date IN VARCHAR2,
705 /* Start INVCONV umoogala
706 p_co_code IN VARCHAR2,
707 p_post_cm IN VARCHAR2,
708 p_post_ic IN VARCHAR2,
709 p_post_om IN VARCHAR2,
710 p_post_op IN VARCHAR2,
711 p_post_pm IN VARCHAR2,
712 p_post_po IN VARCHAR2,
713 p_post_pur IN VARCHAR2,
714 */
715 x_closed_per_ind OUT NOCOPY NUMBER,
716 x_crev_gl_trans_date OUT NOCOPY DATE,
717 x_open_gl_fiscal_year OUT NOCOPY NUMBER,
718 x_open_gl_period OUT NOCOPY NUMBER,
719 /* Start INVCONV umoogala
720 x_crev_curr_mthd OUT NOCOPY VARCHAR2,
721 x_crev_curr_calendar OUT NOCOPY VARCHAR2,
722 x_crev_curr_period OUT NOCOPY VARCHAR2,
723 x_crev_prev_mthd OUT NOCOPY VARCHAR2,
724 x_crev_prev_calendar OUT NOCOPY VARCHAR2,
725 x_crev_prev_period OUT NOCOPY VARCHAR2,
726 */
727 x_inv_fiscal_year OUT NOCOPY NUMBER,
728 x_inv_period OUT NOCOPY NUMBER,
729 x_retstatus OUT NOCOPY VARCHAR2,
730 x_errbuf OUT NOCOPY VARCHAR2
731 ) AS
732
733 /* fiscal year */
734 CURSOR c_fiscal_year(cp_le_id NUMBER, cp_ledger_id NUMBER,
735 cp_fiscal_year NUMBER)
736 IS
737 SELECT DISTINCT glp.period_year
738 FROM
739 gl_periods glp,
740 gl_period_sets gps,
741 gl_sets_of_books gsb
742 WHERE
743 glp.period_year = cp_fiscal_year
744 AND gsb.set_of_books_id = cp_ledger_id
745 AND gsb.period_set_name = glp.period_set_name
746 AND gsb.accounted_period_type = glp.period_type
747 AND glp.period_set_name = gps.period_set_name
748 ;
749
750 /* fiscal period */
751 CURSOR c_gl_period(cp_le_id NUMBER, cp_ledger_id NUMBER,
752 cp_gl_fiscal_year NUMBER, cp_gl_period NUMBER,
753 cp_gl_date DATE)
754 IS
755 SELECT glp.period_name, glp.period_year, glp.period_num,
756 glp.start_date, glp.end_date, sts.closing_status
757 FROM
758 gl_periods glp,
759 gl_period_statuses sts,
760 gl_sets_of_books gsob
761 WHERE
762 glp.period_set_name = gsob.period_set_name -- use the sob period-name
763 AND glp.period_type = gsob.accounted_period_type -- and sob period-type
764 AND gsob.set_of_books_id = cp_ledger_id
765 AND glp.period_year = NVL(cp_gl_fiscal_year, glp.period_year)
766 AND glp.period_num = NVL(cp_gl_period, glp.period_num)
767 AND NVL(trunc(cp_gl_date), glp.start_date)
768 BETWEEN glp.start_date AND glp.end_date
769 AND glp.period_name = sts.period_name -- for use of sts_u2 index
770 AND glp.period_num = sts.period_num
771 AND glp.period_year = sts.period_year
772 AND sts.set_of_books_id = cp_ledger_id
773 AND sts.application_id = (
774 SELECT application_id
775 FROM fnd_application
776 WHERE application_short_name = 'SQLGL')
777 ;
778
779 l_gl_fiscal_year NUMBER(15);
780 l_gl_period NUMBER(15);
781
782 l_open_gl_date DATE;
783 l_posting_start_date DATE;
784 l_posting_end_date DATE;
785
786 l_gl_period_name gl_periods.period_name%TYPE;
787 l_gl_period_year gl_periods.period_year%TYPE;
788 l_gl_period_num gl_periods.period_num%TYPE;
789
790 l_gl_per_start_date DATE;
791 l_gl_per_real_start_date DATE;
792 l_gl_per_end_date DATE;
793 l_gl_per_real_end_date DATE;
794
795 l_gl_period_status gl_period_statuses.closing_status%TYPE;
796 l_gl_period_status_2 gl_period_statuses.closing_status%TYPE;
797
798 l_closed_per_ind NUMBER(2) := 0;
799 l_crev_gl_trans_date DATE;
800 l_crev_gl_date DATE;
801
802 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_PARAMETERS';
803
804 l_co_source VARCHAR2(1);
805 l_source_selected BOOLEAN;
806
807 l_retstatus VARCHAR2(1);
808 l_inv_fiscal_year org_acct_periods.period_year%TYPE := NULL;
809 l_inv_period org_acct_periods.period_num%TYPE := NULL;
810 l_errbuf VARCHAR2(2000);
811 l_lot_actual_cost NUMBER := 0;
812 l_post_cm VARCHAR2(2);
813
814 /* exceptions */
815 e_invalid_parameter EXCEPTION;
816
817 BEGIN
818
819 g_log_msg := 'Begin of procedure '|| l_procedure_name;
820
821 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
822 THEN
823 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
824 END IF;
825
826 --
827 -- Validating GL Fiscal Year
828 --
829 g_log_msg := 'validating GL Fiscal Year';
830 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
831 THEN
832 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
833 END IF;
834
835
836 l_gl_fiscal_year := TO_NUMBER(p_gl_fiscal_year);
837
838 OPEN c_fiscal_year(g_legal_entity_id, g_ledger_id, l_gl_fiscal_year);
839 FETCH c_fiscal_year INTO l_gl_fiscal_year;
840 IF( c_fiscal_year%NOTFOUND )
841 THEN
842 x_errbuf := 'Invalid GL Fiscal Year: ' || l_gl_fiscal_year ;
843 CLOSE c_fiscal_year;
844 RAISE e_invalid_parameter;
845 END IF;
846 CLOSE c_fiscal_year;
847
848
849 --
850 -- Validating GL Period
851 --
852 g_log_msg := 'validating GL Period';
853 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
854 THEN
855 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
856 END IF;
857
858 l_gl_period := TO_NUMBER(p_gl_period);
859
860 OPEN c_gl_period(g_legal_entity_id, g_ledger_id, l_gl_fiscal_year, l_gl_period, null);
861 FETCH c_gl_period INTO l_gl_period_name, l_gl_period_year, l_gl_period_num,
862 l_gl_per_start_date, l_gl_per_end_date, l_gl_period_status;
863 IF( c_gl_period%NOTFOUND )
864 THEN
865 x_errbuf := 'Invalid GL Period: ' || l_gl_period ;
866 CLOSE c_gl_period;
867 RAISE e_invalid_parameter;
868 END IF;
869 CLOSE c_gl_period;
870
871 --
872 -- Validating date ranges
873 --
874 g_log_msg := 'validating date ranges';
875 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
876 THEN
877 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
878 END IF;
879
880
881 l_gl_per_real_start_date := gmf_legal_entity_tz.convert_le_to_srv_tz(l_gl_per_start_date, g_legal_entity_id); /* Bug#5623121 ANTHIYAG 30-Oct-2006 */
882 l_gl_per_real_end_date := gmf_legal_entity_tz.convert_le_to_srv_tz(l_gl_per_end_date + 1 - (1/86400), g_legal_entity_id); /* Bug#5623121 ANTHIYAG 30-Oct-2006 */
883
884 l_open_gl_date := FND_DATE.canonical_to_date(p_open_gl_date);
885 l_posting_start_date := FND_DATE.canonical_to_date(p_posting_start_date);
886 l_posting_end_date := FND_DATE.canonical_to_date(p_posting_end_date);
887
888 /* Validate if dates are correct */
889 IF( l_posting_start_date > l_posting_end_date )
890 THEN
891 fnd_message.set_name('GMF', 'GL_INVALID_DATERANGE');
892 x_errbuf := fnd_message.get;
893 RAISE e_invalid_parameter;
894 END IF;
895
896 --
897 -- Validating posting dates against GL Period
898 --
899 g_log_msg := 'Validating posting dates against GL Period';
900 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
901 THEN
902 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
903 END IF;
904
905
906 /* Validate against the periods real start and end dates */
907 /* Note: is this additional validation necessary? */
908 IF (l_posting_start_date < l_gl_per_real_start_date /* Bug#5623121 ANTHIYAG 30-Oct-2006 */
909 AND l_gl_per_real_start_date IS NOT NULL)
910 THEN
911 FND_MESSAGE.SET_NAME('GMF','GMF_INVALID_DATE_FOR_PERIOD');
912 FND_MESSAGE.SET_TOKEN('S1', fnd_date.date_to_displayDT(gmf_legal_entity_tz.convert_srv_to_le(g_legal_entity_id, l_posting_start_date), 'FND_NO_CONVERT'));
913 FND_MESSAGE.SET_TOKEN('S2', fnd_date.date_to_displayDT(gmf_legal_entity_tz.convert_srv_to_le(g_legal_entity_id, l_gl_per_real_start_date), 'FND_NO_CONVERT'));
914 FND_MESSAGE.SET_TOKEN('S3', fnd_date.date_to_displayDT(gmf_legal_entity_tz.convert_srv_to_le(g_legal_entity_id, l_gl_per_real_end_date), 'FND_NO_CONVERT'));
915 x_errbuf := fnd_message.get;
916 RAISE e_invalid_parameter;
917 END IF;
918
919 IF (l_posting_end_date > l_gl_per_real_end_date /* Bug#5623121 ANTHIYAG 30-Oct-2006 */
920 AND l_gl_per_real_end_date IS NOT NULL)
921 THEN
922 FND_MESSAGE.SET_NAME('GMF','GMF_INVALID_DATE_FOR_PERIOD');
923 FND_MESSAGE.SET_TOKEN('S1', fnd_date.date_to_displayDT(gmf_legal_entity_tz.convert_srv_to_le(g_legal_entity_id, l_posting_end_date), 'FND_NO_CONVERT'));
924 FND_MESSAGE.SET_TOKEN('S2', fnd_date.date_to_displayDT(gmf_legal_entity_tz.convert_srv_to_le(g_legal_entity_id, l_gl_per_real_start_date), 'FND_NO_CONVERT'));
925 FND_MESSAGE.SET_TOKEN('S3', fnd_date.date_to_displayDT(gmf_legal_entity_tz.convert_srv_to_le(g_legal_entity_id, l_gl_per_real_end_date), 'FND_NO_CONVERT'));
926 x_errbuf := fnd_message.get;
927 RAISE e_invalid_parameter;
928 END IF;
929
930 --
931 -- check the status of gl_period passed in
932 --
933 g_log_msg := 'verifying the status of gl_period passed in. status: ' || l_gl_period_status;
934 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
935 THEN
936 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
937 END IF;
938
939
940 IF( l_gl_period_status NOT IN ('F','N','O') )
941 THEN
942 IF( l_open_gl_date IS NULL )
943 THEN
944 x_errbuf := 'GL Period is closed. Open GL date required' ;
945 g_log_msg := 'error: ' || x_errbuf;
946 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
947 THEN
948 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
949 END IF;
950
951 RAISE e_invalid_parameter;
952 END IF;
953
954 /* get the open gl date's year/period */
955 OPEN c_gl_period(g_legal_entity_id, g_ledger_id, NULL, NULL, l_open_gl_date);
956 FETCH c_gl_period INTO l_gl_period_name, l_gl_period_year, l_gl_period_num,
957 l_gl_per_start_date, l_gl_per_end_date, l_gl_period_status_2;
958
959 IF( c_gl_period%NOTFOUND )
960 THEN
961 x_errbuf := 'Unable to find period for Open GL Date: ' || to_char(l_open_gl_date,'YYYY/MM/DD HH24:MI:SS') ;
962 CLOSE c_gl_period;
963 g_log_msg := 'error: ' || x_errbuf;
964 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
965 THEN
966 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
967 END IF;
968
969
970 RAISE e_invalid_parameter;
971 END IF;
972 CLOSE c_gl_period;
973
974 IF( l_gl_period_status_2 NOT IN ('F','N','O') )
975 THEN
976 x_errbuf := 'Open GL Date not in an Open GL Period' ;
977 g_log_msg := 'error: ' || x_errbuf;
978 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
979 THEN
980 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
981 END IF;
982
983 RAISE e_invalid_parameter;
984 END IF;
985
986 l_closed_per_ind := 1;
987 x_open_gl_fiscal_year := l_gl_period_year;
988 x_open_gl_period := l_gl_period_num;
989
990 ELSE
991 l_closed_per_ind := 0; /* Bug 2230751 */
992 END IF; /* closed per */
993
994 x_closed_per_ind := l_closed_per_ind;
995
996 /* validate the sources */
997 l_source_selected := TRUE;
998
999
1000 /**
1001 * check costing parameters
1002 * Note: the rval current period dates must be checked against
1003 * the gl period's real start and end date
1004 * we already validate if the posting st/end dates are within
1005 * the real st/end dates.
1006 */
1007
1008 IF (g_process_category = 'REVALUATION_TRANSACTIONS')
1009 AND (g_cost_method_type <> 6) /* Non-Lot Cost Method */
1010 THEN
1011 g_log_msg := 'calling check_costing procedure';
1012 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1013 THEN
1014 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1015 END IF;
1016
1017
1018 check_costing(
1019 p_test_posting => p_test_posting,
1020 p_period_start_date => l_gl_per_real_start_date,
1021 p_period_end_date => l_gl_per_real_end_date,
1022 p_closed_period_ind => l_closed_per_ind,
1023 p_open_gl_date => l_open_gl_date,
1024 x_crev_gl_trans_date => x_crev_gl_trans_date,
1025 x_inv_fiscal_year => l_inv_fiscal_year,
1026 x_inv_period => l_inv_period,
1027 x_retstatus => l_retstatus,
1028 x_errbuf => l_errbuf)
1029 ;
1030
1031 IF( l_retstatus <> 'S' )
1032 THEN
1033 x_errbuf := l_errbuf;
1034 g_log_msg := l_procedure_name || ': error returned from check_costing procedure. ' ||
1035 'error: ' || x_errbuf;
1036 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1037 THEN
1038 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1039 END IF;
1040
1041
1042 RAISE e_invalid_parameter;
1043 ELSE
1044 x_inv_fiscal_year := l_inv_fiscal_year;
1045 x_inv_period := l_inv_period;
1046 END IF;
1047 END IF;
1048
1049 x_retstatus := 'S';
1050
1051 EXCEPTION
1052 WHEN e_invalid_parameter THEN
1053 x_retstatus := 'E';
1054
1055 WHEN others THEN
1056 x_retstatus := 'E';
1057 x_errbuf := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
1058
1059 g_log_msg := 'in when other. error: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
1060 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1061 THEN
1062 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1063 END IF;
1064 END validate_parameters;
1065
1066 /*****************************************************************************
1067 * PROCEDURE
1068 * insert_control_record
1069 *
1070 * DESCRIPTION
1071 * Inserts a control record after verifying there is no other running or
1072 * scheduled process for the same parameters.
1073 *
1074 * INPUT PARAMETERS
1075 * All columns of the control table
1076 *
1077 * ASSUMPTIONS
1078 * All column data have been validated and if CM source is selected then
1079 * appropriate INV calendar parameters have also been validated.
1080 *
1081 * HISTORY
1082 * 26-Dec-2002 Rajesh Seshadri
1083 *
1084 ******************************************************************************/
1085 PROCEDURE insert_control_record(
1086 p_user_id IN NUMBER,
1087 p_gl_fiscal_year IN NUMBER,
1088 p_gl_period IN NUMBER,
1089 p_posting_start_date IN DATE,
1090 p_posting_end_date IN DATE,
1091 p_test_posting IN VARCHAR2,
1092 /* Start INVCONV umoogala
1093 p_post_cm IN VARCHAR2,
1094 p_post_ic IN VARCHAR2,
1095 p_post_om IN VARCHAR2,
1096 p_post_op IN VARCHAR2,
1097 p_post_pm IN VARCHAR2,
1098 p_post_po IN VARCHAR2,
1099 p_post_pur IN VARCHAR2,
1100 */
1101 p_closed_per_ind IN NUMBER,
1102 p_open_gl_date IN DATE,
1103 p_crev_gl_trans_date IN DATE,
1104 p_open_gl_fiscal_year IN NUMBER,
1105 p_open_gl_period IN NUMBER,
1106 p_post_if_no_cost IN VARCHAR2,
1107 p_default_language IN VARCHAR2,
1108 /* Start INVCONV umoogala
1109 p_crev_curr_mthd IN VARCHAR2,
1110 p_crev_curr_calendar IN VARCHAR2,
1111 p_crev_curr_period IN VARCHAR2,
1112 p_crev_prev_mthd IN VARCHAR2,
1113 p_crev_prev_calendar IN VARCHAR2,
1114 p_crev_prev_period IN VARCHAR2,
1115 */
1116 p_inv_fiscal_year IN VARCHAR2,
1117 p_inv_period IN NUMBER,
1118 x_subledger_ref_no OUT NOCOPY NUMBER,
1119 x_retstatus OUT NOCOPY VARCHAR2,
1120 x_errbuf OUT NOCOPY VARCHAR2
1121 ) AS
1122
1123
1124 CURSOR c_sch(cp_le_id NUMBER, cp_ledger_id NUMBER, cp_cost_type_id NUMBER,
1125 cp_gl_fiscal_year NUMBER, cp_gl_period NUMBER,
1126 cp_post_cm VARCHAR2, cp_post_ic VARCHAR2,
1127 cp_post_om VARCHAR2, cp_post_pm VARCHAR2,
1128 cp_post_pur VARCHAR2 )
1129 IS
1130 SELECT reference_no, request_id, count(*) over()
1131 FROM gl_subr_sta
1132 WHERE legal_entity_id = cp_le_id
1133 AND ledger_id = cp_ledger_id
1134 AND cost_type_id = cp_cost_type_id
1135 AND fiscal_year = cp_gl_fiscal_year
1136 AND period = cp_gl_period
1137 AND completion_ind = 0
1138 AND stop_ind = 0
1139 AND rownum = 1
1140 AND ((post_ic = cp_post_ic AND post_ic = 1) OR
1141 (post_pm = cp_post_pm AND post_pm = 1) OR
1142 (post_cm = cp_post_cm AND post_cm = 1) OR
1143 (post_om = cp_post_om AND post_om = 1) OR
1144 (post_pur = cp_post_pur AND post_pur = 1))
1145 ;
1146
1147
1148 l_reference_no gl_subr_sta.reference_no%TYPE;
1149 l_request_id gl_subr_sta.request_id%TYPE;
1150 l_sch_count NUMBER(15);
1151 l_subledger_ref_no NUMBER(15) := NULL;
1152
1153 e_insert_error EXCEPTION;
1154 l_procedure_name CONSTANT VARCHAR2(30) := 'INSERT_CONTROL_RECORD';
1155
1156 l_post_cm VARCHAR2(1) := 0;
1157 l_post_ic VARCHAR2(1) := 0;
1158 l_post_om VARCHAR2(1) := 0;
1159 l_post_pm VARCHAR2(1) := 0;
1160 l_post_pur VARCHAR2(1) := 0;
1161
1162 l_period_id gmf_period_statuses.period_id%TYPE;
1163
1164
1165 BEGIN
1166
1167 g_log_msg := 'Begin of procedure '|| l_procedure_name;
1168
1169 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1170 THEN
1171 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1172 END IF;
1173
1174 x_retstatus := 'E';
1175
1176 IF (g_process_category = 'REVALUATION_TRANSACTIONS') THEN l_post_cm := 1; END IF;
1177 IF (g_process_category = 'INVENTORY_TRANSACTIONS') THEN l_post_ic := 1; END IF;
1178 IF (g_process_category = 'PRODUCTION_TRANSACTIONS') THEN l_post_pm := 1; END IF;
1179 IF (g_process_category = 'ORDER_MANAGEMENT') THEN l_post_om := 1; END IF;
1180 IF (g_process_category = 'PURCHASING_TRANSACTIONS') THEN l_post_pur := 1; END IF;
1181
1182 /* check for already running or scheduled process for same params */
1183 g_log_msg := 'check for already running or scheduled process for same params';
1184
1185 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1186 THEN
1187 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1188 END IF;
1189
1190 open c_sch (g_legal_entity_id, g_ledger_id, g_cost_type_id,
1191 p_gl_fiscal_year, p_gl_period,
1192 l_post_cm, l_post_ic, l_post_om, l_post_pm, l_post_pur);
1193 FETCH c_sch INTO l_reference_no, l_request_id, l_sch_count;
1194 CLOSE c_sch;
1195
1196 IF( l_sch_count > 0 )
1197 THEN
1198 fnd_message.set_name('GMF','GL_TRN_POST_SCHEDULED');
1199 fnd_message.set_token('S1', l_reference_no);
1200 fnd_message.set_token('S2', l_request_id);
1201 x_errbuf := fnd_message.get;
1202 RAISE e_insert_error;
1203 END IF;
1204
1205 IF g_cost_method_type = 6
1206 THEN
1207 l_period_id := 0;
1208 ELSE
1209 /* Getting period_id for LE, CT and dates */
1210 g_log_msg := 'Getting period_id for LE, CT and dates.';
1211
1212 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1213 THEN
1214 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1215 END IF;
1216
1217 BEGIN
1218 SELECT period_id
1219 INTO l_period_id
1220 FROM gmf_period_statuses prdsta
1221 WHERE
1222 prdsta.legal_entity_id = g_legal_entity_id
1223 AND prdsta.cost_type_id = g_cost_type_id
1224 AND p_posting_start_date between prdsta.start_date and prdsta.end_date
1225 AND p_posting_end_date between prdsta.start_date and prdsta.end_date
1226 ;
1227 EXCEPTION
1228 WHEN NO_DATA_FOUND
1229 THEN
1230 fnd_message.set_name('GMF','GMF_PERIOD_NOT_FOUND');
1231 fnd_message.set_token('S1', g_legal_entity_name);
1232 fnd_message.set_token('S2', g_cost_type_code);
1233 fnd_message.set_token('S3', p_posting_start_date);
1234 fnd_message.set_token('S4', p_posting_end_date);
1235 x_errbuf := fnd_message.get;
1236 RAISE e_insert_error;
1237 END;
1238 END IF;
1239
1240 /* insert the control record */
1241 INSERT INTO gl_subr_sta
1242 (
1243 co_code,
1244 current_state,
1245 start_time,
1246 end_time,
1247 completion_ind,
1248 started_by,
1249 stop_ind,
1250 reference_no,
1251 fiscal_year,
1252 period,
1253 period_start_date,
1254 period_end_date,
1255 test_posting,
1256 scheduled_on,
1257 aborted_by,
1258 update_stage,
1259 errors_found,
1260 errors_posted,
1261 errors_limit,
1262 rows_posted,
1263 abort_reason,
1264 creation_date,
1265 created_by,
1266 delete_mark,
1267 in_use,
1268 last_update_date,
1269 last_updated_by,
1270 closed_per_ind,
1271 gl_date,
1272 gl_fiscal_year,
1273 gl_period,
1274 incl_no_cost,
1275 default_language,
1276 crev_curr_mthd,
1277 crev_curr_calendar,
1278 crev_curr_period,
1279 crev_prev_mthd,
1280 crev_prev_calendar,
1281 crev_prev_period,
1282 crev_gl_trans_date,
1283 crev_inv_prev_cal,
1284 crev_inv_prev_per,
1285 legal_entity_id,
1286 legal_entity_name,
1287 ledger_id,
1288 process_category,
1289 cost_type_id,
1290 period_id,
1291 cost_mthd_code,
1292 cost_type,
1293 default_cost_type_id,
1294 default_cost_mthd_code,
1295 cost_basis,
1296 extract_hdr_rows_posted,
1297 extract_line_rows_posted,
1298 crev_curr_cost_type_id,
1299 crev_curr_period_id,
1300 crev_prev_cost_type_id,
1301 crev_prev_period_id,
1302 post_cm,
1303 post_ic,
1304 post_om,
1305 post_pm,
1306 post_pur,
1307 base_currency
1308 )
1309 SELECT
1310 NULL, /* co_code */
1311 0, /* current_state */
1312 NULL, /* start_time */
1313 NULL, /* end_time */
1314 0, /* completion_ind */
1315 p_user_id, /* started_by */
1316 0, /* stop_ind */
1317 gem5_reference_no_s.NEXTVAL,/* reference_no */
1318 p_gl_fiscal_year, /* fiscal_year */
1319 p_gl_period, /* period */
1320 p_posting_start_date, /* period_start_date */
1321 p_posting_end_date, /* period_end_date */
1322 DECODE(p_test_posting,'N',0,1),
1323 FND_DATE.date_to_canonical(SYSDATE), /* scheduled_on - always sysdate */
1324 NULL, /* aborted_by */
1325 0, /* update_stage */
1326 0, /* errors_found */
1327 0, /* errors_posted */
1328 0, /* errors_limit */
1329 0, /* rows_posted */
1330 NULL, /* abort_reason */
1331 SYSDATE, /* creation_date */
1332 p_user_id, /* created_by */
1333 0, /* delete_mark */
1334 0, /* in_use */
1335 SYSDATE, /* last_update_date */
1336 p_user_id, /* last_updated_by */
1337 p_closed_per_ind, /* closed_per_ind */
1338 p_open_gl_date, /* gl_date */
1339 p_open_gl_fiscal_year, /* gl_fiscal_year */
1340 p_open_gl_period, /* gl_period */
1341 DECODE(p_post_if_no_cost,'Y',1,0), /* incl_no_cost */
1342 p_default_language, /* default_language */
1343 g_crev_curr_cost_mthd_code, /* crev_curr_mthd */
1344 g_crev_curr_calendar, /* crev_curr_calendar */
1345 g_crev_curr_period, /* crev_curr_period */
1346 g_crev_prev_cost_mthd, /* crev_prev_mthd */
1347 g_crev_prev_calendar, /* crev_prev_calendar */
1348 g_crev_prev_period, /* crev_prev_period */
1349 g_crev_gl_trans_date,
1350 p_inv_fiscal_year,
1351 p_inv_period,
1352 g_legal_entity_id,
1353 g_legal_entity_name,
1354 g_ledger_id,
1355 g_process_category,
1356 g_cost_type_id,
1357 l_period_id,
1358 g_cost_type_code, /* cost_mthd_code */
1359 g_cost_method_type, /* cost_type */
1360 g_default_cost_type_id,
1361 DECODE(g_default_cost_type_id, NULL, NULL, /* default_lot_cost_mthd */
1362 (SELECT default_lot_cost_mthd from cm_mthd_mst
1363 WHERE cost_type_id = g_default_cost_type_id
1364 AND delete_mark = 0)),
1365 plcy.cost_basis,
1366 0, /* extract_hdr_rows_posted, */
1367 0, /* extract_line_rows_posted, */
1368 g_crev_curr_cost_type_id,
1369 g_crev_curr_period_id,
1370 g_crev_prev_cost_type_id,
1371 g_crev_prev_period_id,
1372 l_post_cm,
1373 l_post_ic,
1374 l_post_om,
1375 l_post_pm,
1376 l_post_pur,
1377 plcy.base_currency_code
1378 FROM
1379 gmf_fiscal_policies plcy
1380 WHERE
1381 plcy.legal_entity_id = g_legal_entity_id
1382 AND plcy.delete_mark = 0
1383 ;
1384
1385 IF sql%rowcount = 0
1386 THEN
1387 x_errbuf := l_procedure_name || ': failed to insert control record';
1388 g_log_msg := 'failed to insert control record';
1389 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1390 THEN
1391 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1392 END IF;
1393
1394 RAISE e_insert_error;
1395 END IF;
1396
1397 x_subledger_ref_no := l_subledger_ref_no;
1398
1399 x_retstatus := 'S';
1400
1401 g_log_msg := l_procedure_name || ': ' || sql%rowcount || ' control record inserted into gl_subr_sta table. ' ||
1402 ' end of procedure';
1403 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1404 THEN
1405 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1406 END IF;
1407
1408
1409 EXCEPTION
1410 WHEN e_insert_error
1411 THEN
1412
1413 g_log_msg := x_errbuf;
1414 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1415 THEN
1416 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1417 END IF;
1418
1419 x_retstatus := 'E';
1420
1421 END insert_control_record;
1422
1423 /*****************************************************************************
1424 # PROCEDURE
1425 # check_costing
1426 # SYNOPSIS
1427 # proc check_costing
1428 # DESCRIPTION
1429 # procedure to fetch cost revaluation parameters and other
1430 # initializations for cost revalualtion.
1431 # HISTORY
1432 # Sukarna Reddy dt 01/08/01 Bug 1108647 Perform Posting only if
1433 # the inventory period is final close. In case of test posting
1434 # preliminary closed inventory period is OK.
1435 # Venkat Chukkapalli 06/19/01 Bug 1837429 Modified the check to
1436 # make sure that subledger period start and end dates are within
1437 # current reval period.
1438 # Anand Thiyagarajan Bug#5623121 30-Oct-2006
1439 # Modified Code to fetch the prior period inventory period details
1440 # and the status of preliminary close from the gmf_period_balances
1441 # table rather than calling the procedure gmf_periodclose_pub
1442 ******************************************************************************/
1443
1444 PROCEDURE check_costing(
1445 p_test_posting IN VARCHAR2,
1446 p_period_start_date IN DATE,
1447 p_period_end_date IN DATE,
1448 p_closed_period_ind IN NUMBER,
1449 p_open_gl_date IN DATE,
1450 x_crev_gl_trans_date OUT NOCOPY DATE,
1451 /* Start INVCONV umoogala
1452 x_crev_curr_mthd OUT NOCOPY VARCHAR2,
1453 x_crev_curr_calendar OUT NOCOPY VARCHAR2,
1454 x_crev_curr_period OUT NOCOPY VARCHAR2,
1455 x_crev_prev_mthd OUT NOCOPY VARCHAR2,
1456 x_crev_prev_calendar OUT NOCOPY VARCHAR2,
1457 x_crev_prev_period OUT NOCOPY VARCHAR2,
1458 */
1459 x_inv_fiscal_year OUT NOCOPY NUMBER,
1460 x_inv_period OUT NOCOPY NUMBER,
1461 x_retstatus OUT NOCOPY VARCHAR2,
1462 x_errbuf OUT NOCOPY VARCHAR2
1463 )
1464 IS
1465
1466
1467 CURSOR c_stend(cp_period_id NUMBER)
1468 IS
1469 SELECT start_date, end_date
1470 FROM gmf_period_statuses
1471 WHERE period_id = cp_period_id
1472 ;
1473
1474 lc_prior_stend_tmp c_stend%ROWTYPE;
1475 lc_curr_stend_tmp c_stend%ROWTYPE;
1476
1477 -- X_crev_prior_end_date cm_cldr_dtl.end_date%TYPE;
1478 -- X_crev_curr_start_date cm_cldr_dtl.start_date%TYPE;
1479
1480 /* TBD - what about cost_mthd ? */
1481 CURSOR c_chk_consq_perd(cp_le_id number, cp_cost_type_id number,
1482 cp_prior_end_date DATE, cp_curr_start_date DATE)
1483 IS
1484 SELECT COUNT(1)
1485 FROM gmf_period_statuses
1486 WHERE legal_entity_id = cp_le_id
1487 -- AND cost_type_id = cp_cost_type_id
1488 AND start_date >= cp_prior_end_date
1489 AND end_date <= cp_curr_start_date
1490 ;
1491
1492 CURSOR c_check_icperd(cp_co_code VARCHAR2,
1493 cp_inv_fiscal_year VARCHAR2, cp_inv_period NUMBER) IS
1494 SELECT closed_period_ind
1495 FROM ic_cldr_dtl
1496 WHERE
1497 orgn_code = cp_co_code
1498 AND fiscal_year = cp_inv_fiscal_year
1499 AND period = cp_inv_period;
1500
1501 /* Bug#5623121 ANTHIYAG 30-Oct-2006 Start */
1502 CURSOR cur_prior_period_id
1503 (
1504 p_legal_entity_id NUMBER,
1505 p_cost_type_id NUMBER,
1506 p_period_start_date DATE,
1507 p_period_end_date DATE
1508 )
1509 IS
1510 SELECT preprd.period_id
1511 FROM gmf_period_statuses prdsta,
1512 gmf_period_statuses preprd
1513 WHERE prdsta.legal_entity_id = p_legal_entity_id
1514 AND prdsta.cost_type_id = p_cost_type_id
1515 AND preprd.legal_entity_id = prdsta.legal_entity_id
1516 AND preprd.cost_type_id = prdsta.cost_type_id
1517 AND p_period_start_date between prdsta.start_date and prdsta.end_date
1518 AND p_period_end_date between prdsta.start_date and prdsta.end_date
1519 AND preprd.end_date < prdsta.end_date
1520 ORDER BY preprd.end_date desc;
1521
1522 CURSOR cur_inv_period_status
1523 (
1524 p_period_id NUMBER
1525 )
1526 IS
1527 SELECT nvl(SUM(decode(nvl(gpb.period_close_status,'~'),'F',1,'P',1, 0)),0) as close_status
1528 FROM org_acct_periods oap,
1529 hr_organization_information hoi,
1530 mtl_parameters mp,
1531 gmf_period_statuses gps,
1532 gl_ledgers gl,
1533 gmf_period_balances gpb
1534 WHERE gps.period_id = p_period_id
1535 AND hoi.org_information2 = gps.legal_entity_id
1536 AND hoi.org_information1 = gl.ledger_id
1537 AND oap.period_set_name = gl.period_set_name
1538 AND hoi.org_information_context = 'Accounting Information'
1539 AND hoi.organization_id = oap.organization_id
1540 AND hoi.organization_id = mp.organization_id
1541 AND mp.process_enabled_flag = 'Y'
1542 AND oap.schedule_close_date = TRUNC(gps.end_date)
1543 AND oap.organization_id = gpb.organization_id
1544 AND oap.acct_period_id = gpb.acct_period_id;
1545
1546 l_prior_period_id NUMBER;
1547 l_close_status NUMBER;
1548 /* Bug#5623121 ANTHIYAG 30-Oct-2006 End */
1549
1550 l_close_per_ind NUMBER(5);
1551 X_count NUMBER(15);
1552 X_rvar NUMBER(15);
1553
1554 l_inv_fiscal_year ic_cldr_hdr.fiscal_year%TYPE;
1555 l_inv_period ic_cldr_dtl.period%TYPE;
1556 l_inv_per_start_date DATE;
1557 l_inv_per_end_date DATE;
1558 l_inv_per_synch VARCHAR2(1);
1559 l_retstatus VARCHAR2(1);
1560 l_errbuf VARCHAR2(2000);
1561
1562 e_reval_error exception;
1563
1564 l_procedure_name CONSTANT VARCHAR2(30) := 'CHECK_COSTING';
1565
1566 l_inv_period_close_status BOOLEAN;
1567
1568 BEGIN
1569
1570 g_log_msg := 'Begin of procedure '|| l_procedure_name;
1571
1572 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1573 THEN
1574 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1575 END IF;
1576
1577 x_retstatus := 'E';
1578
1579
1580 IF g_crev_curr_period_id IS NULL or g_crev_prev_period_id IS NULL
1581 THEN
1582 fnd_message.set_name('GMF','CM_NO_RVAL_PARMS');
1583 x_errbuf := fnd_message.get;
1584 RAISE e_reval_error;
1585 END IF;
1586
1587
1588 --
1589 -- Check to see if current and prior period are successive
1590 --
1591 -- First get the start and end date for the cost reval prior period.
1592 -- OPEN c_stend(p_co_code, lc_reval_tmp.crev_prior_calendar, lc_reval_tmp.crev_prior_period);
1593 --
1594 g_log_msg := 'Check to see if current and prior period are successive';
1595 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1596 THEN
1597 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1598 END IF;
1599
1600
1601 OPEN c_stend(g_crev_prev_period_id);
1602 FETCH c_stend INTO lc_prior_stend_tmp;
1603 IF( c_stend%NOTFOUND )
1604 THEN
1605 CLOSE c_stend;
1606 -- fnd_message.set_name('GMF','GMF_BAD_CREV_PRIOR_PERIOD');
1607 x_errbuf := 'Unable to find period dates for prior period' ;
1608 RAISE e_reval_error;
1609 END IF;
1610 CLOSE c_stend;
1611
1612 --
1613 -- Now get the start and end date for the cost reval current period.
1614 --
1615 g_log_msg := 'Now get the start and end date for the cost reval current period';
1616 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1617 THEN
1618 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1619 END IF;
1620
1621 OPEN c_stend(g_crev_curr_period_id);
1622 FETCH c_stend INTO lc_curr_stend_tmp;
1623 IF( c_stend%NOTFOUND )
1624 THEN
1625 CLOSE c_stend;
1626 x_errbuf := 'Unable to find period dates for current period' ;
1627 RAISE e_reval_error;
1628 END IF;
1629 CLOSE c_stend;
1630
1631 /*
1632 * Check if there is any period between the end_date of prior period and
1633 * start_date of current period. If there are any then period are not
1634 * consecutive. Display the message and return the error
1635 */
1636 g_log_msg := 'Check for consecutive periods';
1637 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1638 THEN
1639 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1640 END IF;
1641
1642
1643 --
1644 -- Get the no of rows between the periods
1645 --
1646 OPEN c_chk_consq_perd(g_legal_entity_id, g_cost_type_id,
1647 lc_prior_stend_tmp.end_date, lc_curr_stend_tmp.start_date);
1648 FETCH c_chk_consq_perd INTO X_count;
1649 CLOSE c_chk_consq_perd;
1650 IF( X_count > 0 )
1651 THEN
1652 fnd_message.set_name('GMF','CM_NOT_CONSEC_PRD');
1653 x_errbuf := fnd_message.get;
1654 RAISE e_reval_error;
1655 END IF;
1656
1657 --
1658 -- Get the inventory period for the costing period
1659 -- TBD how to send errors from this proc?
1660 --
1661
1662 /* Bug#5623121 ANTHIYAG 30-Oct-2006 Start */
1663 g_log_msg := l_procedure_name || ': Getting the inventory period for the Previous costing period';
1664 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1665 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1666 END IF;
1667
1668 /******************************************************************************************
1669 * Based on LE, Cost type, Period Start and End Dates fetch the Previous costing period id *
1670 ******************************************************************************************/
1671 OPEN cur_prior_period_id (
1672 p_legal_entity_id => g_legal_entity_id,
1673 p_cost_type_id => g_cost_type_id,
1674 p_period_start_date => p_period_start_date,
1675 p_period_end_date => p_period_end_date
1676 );
1677 FETCH cur_prior_period_id INTO l_prior_period_id;
1678 CLOSE cur_prior_period_id;
1679
1680 /*************************************************************************
1681 * Fetch Inventory period and year for the Prior Period Costing Period Id *
1682 *************************************************************************/
1683 BEGIN
1684 SELECT period_year,
1685 period_num
1686 INTO x_inv_fiscal_year,
1687 x_inv_period
1688 FROM org_acct_periods oap,
1689 hr_organization_information hoi,
1690 gmf_period_statuses gps,
1691 gl_ledgers gl
1692 WHERE gps.period_id = l_prior_period_id
1693 AND hoi.org_information2 = gps.legal_entity_id
1694 AND hoi.org_information1 = gl.ledger_id
1695 AND oap.period_set_name = gl.period_set_name
1696 AND hoi.org_information_context = 'Accounting Information'
1697 AND hoi.organization_id = oap.organization_id
1698 AND oap.schedule_close_date = TRUNC(gps.end_date)
1699 AND ROWNUM = 1;
1700 EXCEPTION
1701 WHEN no_data_found THEN
1702 x_inv_fiscal_year := NULL;
1703 x_inv_period := NULL;
1704 END;
1705
1706 g_log_msg := l_procedure_name || ': Inventory Period Year and Number fetched is : ' || x_inv_fiscal_year ||'/'|| x_inv_period;
1707 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1708 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1709 END IF;
1710
1711 /*************************************************************************************
1712 * Check if atleast premilinary close has been done for the Previous Inventory Period *
1713 *************************************************************************************/
1714 OPEN cur_inv_period_status (p_period_id => l_prior_period_id);
1715 FETCH cur_inv_period_status INTO l_close_status;
1716 CLOSE cur_inv_period_status;
1717
1718 IF (NVL (l_close_status, 0) <= 0) THEN
1719 g_log_msg := l_procedure_name || ': Inventory Period Year and Number: ' || x_inv_fiscal_year ||'/'|| x_inv_period || ' is not closed ';
1720 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1721 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1722 END IF;
1723 FND_MESSAGE.SET_NAME ('GMF', 'CM_AC_INVENT_NOT_CL');
1724 x_errbuf := fnd_message.get;
1725 RAISE e_reval_error;
1726 END IF;
1727 /* Bug#5623121 ANTHIYAG 30-Oct-2006 End */
1728
1729 --
1730 -- Check current costing period is equivalent to gl yr/prd
1731 -- Bug 1837429
1732 --
1733 g_log_msg := l_procedure_name || ': Check current costing period is equivalent to gl yr/prd';
1734 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1735 THEN
1736 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1737 END IF;
1738
1739 IF( p_period_start_date < lc_curr_stend_tmp.start_date OR
1740 p_period_start_date > lc_curr_stend_tmp.end_date OR
1741 p_period_end_date < lc_curr_stend_tmp.start_date OR
1742 p_period_end_date > lc_curr_stend_tmp.end_date)
1743 THEN
1744 fnd_message.set_name('GMF','GL_INVALID_CURR_REVALPRD');
1745 x_errbuf := fnd_message.get;
1746 RAISE e_reval_error;
1747 END IF;
1748
1749
1750 g_log_msg := l_procedure_name || ': Checking whether GL Date ' || to_char(g_crev_gl_trans_date, 'DD-MON-YYYY') ||
1751 ' is with data range: ' || to_char(p_period_start_date, 'DD-MON-YYYY') || ' and ' || to_char(p_period_end_date, 'DD-MON-YYYY');
1752 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1753 THEN
1754 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1755 END IF;
1756
1757 /* Bug 2230751 */
1758 IF (p_closed_period_ind = 0)
1759 THEN
1760 IF (gmf_legal_entity_tz.convert_le_to_srv_tz(g_crev_gl_trans_date, g_legal_entity_id) NOT BETWEEN p_period_start_date and p_period_end_date) /* Bug#5623121 ANTHIYAG 30-Oct-2006 */
1761 THEN
1762 fnd_message.set_name('GMF','GMF_GL_DATE_MUST_WITHIN_PERIOD');
1763 fnd_message.set_token('START_DATE', fnd_date.date_to_displayDT(gmf_legal_entity_tz.convert_srv_to_le(g_legal_entity_id, p_period_start_date), 'FND_NO_CONVERT')); /* Bug#5623121 ANTHIYAG 30-Oct-2006 */
1764 fnd_message.set_token('END_DATE',fnd_date.date_to_displayDT(gmf_legal_entity_tz.convert_srv_to_le(g_legal_entity_id, p_period_end_date), 'FND_NO_CONVERT')); /* Bug#5623121 ANTHIYAG 30-Oct-2006 */
1765 x_errbuf := fnd_message.get;
1766 RAISE e_reval_error;
1767 ELSE
1768 x_crev_gl_trans_date := g_crev_gl_trans_date;
1769 END IF;
1770 ELSIF(p_closed_period_ind = 1)
1771 THEN
1772 x_crev_gl_trans_date := p_open_gl_date;
1773 g_crev_gl_trans_date := p_open_gl_date;
1774 END IF;
1775
1776 x_retstatus := 'S';
1777
1778 EXCEPTION
1779 WHEN e_reval_error THEN
1780 x_retstatus := 'E';
1781
1782 g_log_msg := l_procedure_name || ': error: ' || x_errbuf;
1783 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1784 THEN
1785 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1786 END IF;
1787
1788 END check_costing;
1789
1790 /*##########################################################################
1791 # NAME
1792 # inter_mod_cal_conv
1793 #
1794 # RETURNS
1795 # The matching Inv fiscal year and period if they exist.
1796 #
1797 # In addition we return the following information in x_inv_per_synch
1798 # if there is a matching Inv period.
1799 #
1800 # Y - If the Inv period is identical to the source-period
1801 # (the start/end dates match)
1802 # N - If the Inv period is not identical to the source-period
1803 # (the end date matches but not the start date)
1804 #
1805 # If a matching Inv period does not exist or in case of db or unknown
1806 # errors we return 'E' in the status.
1807 #
1808 # NOTES:
1809 # The second cursor for the inv periods as found in GLSLDDS.fmb
1810 # is not needed as the same info can be retrieved by fetching again
1811 # from the first cursor on ic_cldr tables. Again this is only needed
1812 # for the start date of the matching inv period.
1813 # The start/end dates are not used elsewhere!!
1814 #
1815 # HISTORY
1816 # 12-Nov-99 Rajesh Seshadri Bug 1064535 - use delete_mark on ic_cldr_hdr
1817 ############################################################################# */
1818
1819 PROCEDURE inter_mod_cal_conv(
1820 x_inv_fiscal_year OUT NOCOPY NUMBER,
1821 x_inv_period OUT NOCOPY NUMBER,
1822 x_inv_per_synch OUT NOCOPY VARCHAR2,
1823 x_inv_per_start_date OUT NOCOPY DATE,
1824 x_inv_per_end_date OUT NOCOPY DATE,
1825 x_retstatus OUT NOCOPY VARCHAR2,
1826 x_errbuf OUT NOCOPY VARCHAR2 )
1827 IS
1828
1829 /* Start INVCONV umoogala
1830 CURSOR c_cmsrc_info(cp_co_code VARCHAR2, cp_source_calendar VARCHAR2, cp_source_period VARCHAR2) IS
1831 */
1832 CURSOR c_cmsrc_info(cp_period_id number)
1833 IS
1834 SELECT start_date, end_date
1835 FROM gmf_period_statuses
1836 WHERE period_id = cp_period_id
1837 ;
1838
1839 lc_cmcal_info c_cmsrc_info%ROWTYPE;
1840
1841
1842 CURSOR c_ictrg_info(cp_le_id NUMBER, cp_cm_end_date DATE)
1843 IS
1844 SELECT
1845 d1.period_year fiscal_year, d1.period_num period,
1846 d1.period_start_date begin_date, d2.schedule_close_date period_end_date
1847 FROM
1848 org_organization_definitions org,
1849 org_acct_periods d1,
1850 org_acct_periods d2
1851 WHERE
1852 org.legal_entity = cp_le_id
1853 AND d2.period_year = d1.period_year
1854 AND org.organization_id = d1.organization_id
1855 AND org.organization_id = d2.organization_id
1856 AND TRUNC(d1.schedule_close_date+1-1/86400) = TRUNC(cp_cm_end_date)
1857 AND d2.schedule_close_date <= d1.schedule_close_date
1858 ORDER BY
1859 d2.schedule_close_date desc
1860 ;
1861
1862 lc_iccal_info c_ictrg_info%ROWTYPE;
1863
1864 --
1865 -- to get the begin date of ic period
1866 --
1867 l_is_first_inv_period BOOLEAN;
1868 lc_iccal_info2 c_ictrg_info%ROWTYPE;
1869 e_invalid_inv_period EXCEPTION;
1870 l_procedure_name CONSTANT VARCHAR2(30) := 'INTER_MOD_CAL_CONV';
1871
1872 BEGIN
1873
1874 g_log_msg := 'Begin of procedure '|| l_procedure_name;
1875
1876 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1877 THEN
1878 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1879 END IF;
1880
1881 x_inv_per_synch := 'N';
1882 x_retstatus := 'E';
1883
1884 --
1885 --
1886 --
1887 OPEN c_cmsrc_info(g_crev_prev_period_id);
1888 FETCH c_cmsrc_info INTO lc_cmcal_info;
1889 IF (c_cmsrc_info%NOTFOUND) THEN
1890 CLOSE c_cmsrc_info;
1891 x_errbuf := 'Unable to find source period end dates' ;
1892 RAISE e_invalid_inv_period;
1893 END IF;
1894 CLOSE c_cmsrc_info;
1895
1896 l_is_first_inv_period := FALSE;
1897
1898 --
1899 -- find matching inv period
1900 --
1901 OPEN c_ictrg_info(g_legal_entity_id, lc_cmcal_info.end_date);
1902 FETCH c_ictrg_info INTO lc_iccal_info;
1903 IF (c_ictrg_info%NOTFOUND)
1904 THEN
1905 CLOSE c_ictrg_info;
1906 x_errbuf := 'Unable to find matching inv period' ;
1907 RAISE e_invalid_inv_period;
1908 END IF;
1909
1910 --
1911 -- see if there is another row available.
1912 -- set FLAG when no more rows.
1913 --
1914 FETCH c_ictrg_info INTO lc_iccal_info2;
1915 IF( c_ictrg_info%NOTFOUND )
1916 THEN
1917 l_is_first_inv_period := TRUE;
1918 END IF;
1919 CLOSE c_ictrg_info;
1920
1921 --
1922 --
1923 --
1924 x_inv_per_end_date := lc_iccal_info.period_end_date;
1925
1926 IF( l_is_first_inv_period )
1927 THEN
1928 x_inv_per_start_date := lc_iccal_info.begin_date;
1929 ELSE
1930 x_inv_per_start_date := lc_iccal_info2.period_end_date + 1;
1931 END IF;
1932
1933 IF( TRUNC(x_inv_per_start_date) = TRUNC(lc_cmcal_info.start_date) )
1934 THEN
1935 x_inv_per_synch := 'Y';
1936 END IF;
1937
1938 x_inv_fiscal_year := lc_iccal_info.fiscal_year;
1939 x_inv_period := lc_iccal_info.period;
1940
1941 x_retstatus := 'S';
1942
1943 EXCEPTION
1944 WHEN e_invalid_inv_period THEN
1945 x_retstatus := 'E';
1946
1947 g_log_msg := l_procedure_name || ': error: ' || x_errbuf;
1948 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1949 THEN
1950 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1951 END IF;
1952
1953 END inter_mod_cal_conv;
1954
1955 PROCEDURE populate_global (
1956 p_legal_entity_id IN VARCHAR2
1957 , p_ledger_id IN VARCHAR2
1958 , p_cost_type_id IN VARCHAR2
1959 , p_post_cm IN VARCHAR2
1960 , p_crev_curr_calendar IN VARCHAR2
1961 , p_crev_curr_period IN VARCHAR2
1962 , p_crev_prev_cost_type_id IN VARCHAR2
1963 , p_crev_prev_calendar IN VARCHAR2
1964 , p_crev_prev_period IN VARCHAR2
1965 , p_crev_gl_trans_date IN VARCHAR2
1966 )
1967 IS
1968 l_procedure_name CONSTANT VARCHAR2(30) := 'POPULATE_GLOBAL';
1969 BEGIN
1970
1971 --
1972 -- Populate Global variables
1973 --
1974 g_log_msg := l_procedure_name || ': Populate Global variables';
1975 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1976 THEN
1977 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
1978 END IF;
1979
1980 IF p_post_cm = 1
1981 THEN
1982 g_process_category := 'REVALUATION_TRANSACTIONS';
1983 ELSE
1984 g_process_category := NULL; /* Bug#5623121 ANTHIYAG 30-Oct-2006 */
1985 END IF;
1986
1987 g_legal_entity_id := TO_NUMBER(p_legal_entity_id);
1988 g_ledger_id := TO_NUMBER(p_ledger_id);
1989 g_cost_type_id := TO_NUMBER(p_cost_type_id);
1990
1991 /* Bug#5708175 ANTHIYAG 12-Dec-2006 Start */
1992 /*************************************
1993 SELECT le.organization_name, led.name,
1994 mthd.cost_type, mthd.cost_mthd_code, lk.meaning,
1995 mthd.default_lot_cost_type_id
1996 INTO g_legal_entity_name, g_ledger_name,
1997 g_cost_method_type, g_cost_type_code, g_cost_method,
1998 g_default_cost_type_id
1999 FROM org_organization_definitions le, gl_ledgers led,
2000 cm_mthd_mst mthd, gem_lookups lk
2001 WHERE le.organization_id = g_legal_entity_id
2002 AND led.ledger_id = g_ledger_id
2003 AND mthd.cost_type_id = g_cost_type_id
2004 AND lk.lookup_type = 'GMF_COST_METHOD'
2005 AND lk.lookup_code = mthd.cost_type;
2006 **************************************/
2007 BEGIN
2008 SELECT gle.legal_entity_name
2009 INTO g_legal_entity_name
2010 FROM gmf_legal_entities gle
2011 WHERE gle.legal_entity_id = g_legal_entity_id ;
2012 EXCEPTION
2013 WHEN NO_DATA_FOUND then
2014 g_log_msg := l_procedure_name || ': No data found in gmf_legal_entities query';
2015 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2016 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
2017 END IF;
2018 RAISE;
2019 END;
2020 BEGIN
2021 SELECT gl.name
2022 INTO g_ledger_name
2023 FROM gl_ledgers gl
2024 WHERE gl.ledger_id = g_ledger_id;
2025 EXCEPTION
2026 WHEN NO_DATA_FOUND then
2027 g_log_msg := l_procedure_name || ': No data found in gl_ledgers query';
2028 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2029 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
2030 END IF;
2031 RAISE;
2032 END;
2033 BEGIN
2034 SELECT mthd.cost_type,
2035 mthd.cost_mthd_code,
2036 lk.meaning,
2037 nvl(mthd.default_lot_cost_type_id, -1)
2038 INTO g_cost_method_type,
2039 g_cost_type_code,
2040 g_cost_method,
2041 g_default_cost_type_id
2042 FROM cm_mthd_mst mthd,
2043 gem_lookups lk
2044 WHERE mthd.cost_type_id = g_cost_type_id
2045 AND lk.lookup_type = 'GMF_COST_METHOD'
2046 AND lk.lookup_code = mthd.cost_type ;
2047 EXCEPTION
2048 WHEN NO_DATA_FOUND then
2049 g_log_msg := l_procedure_name || ': No data found in cost types query';
2050 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2051 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
2052 END IF;
2053 RAISE;
2054 END;
2055 /* Bug#5708175 ANTHIYAG 12-Dec-2006 End */
2056
2057 g_crev_curr_cost_type_id := g_cost_type_id;
2058 g_crev_curr_cost_mthd_code := g_cost_type_code;
2059 g_crev_curr_calendar := p_crev_curr_calendar;
2060 g_crev_curr_period := p_crev_curr_period;
2061
2062 g_crev_prev_cost_type_id := TO_NUMBER(p_crev_prev_cost_type_id);
2063 g_crev_prev_calendar := p_crev_prev_calendar;
2064 g_crev_prev_period := p_crev_prev_period;
2065
2066 -- g_crev_gl_trans_date := FND_DATE.canonical_to_date(p_crev_gl_trans_date);
2067 g_crev_gl_trans_date := p_crev_gl_trans_date;
2068
2069
2070 IF g_cost_method_type <> 6
2071 THEN
2072 IF g_crev_curr_calendar IS NULL OR g_crev_curr_period IS NULL OR
2073 g_crev_prev_cost_type_id IS NULL OR g_crev_prev_calendar IS NULL OR
2074 g_crev_prev_period IS NULL
2075 THEN
2076 g_crev_curr_period_id := NULL;
2077 g_crev_prev_period_id := NULL;
2078 g_crev_prev_cost_mthd := NULL;
2079
2080 ELSE
2081
2082 g_log_msg := l_procedure_name || ': query cost reval data';
2083 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
2084 THEN
2085 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, g_log_msg);
2086 END IF;
2087
2088
2089 SELECT curr.period_id, prev.period_id,
2090 mthd.cost_mthd_code
2091 INTO g_crev_curr_period_id, g_crev_prev_period_id,
2092 g_crev_prev_cost_mthd
2093 FROM gmf_period_statuses curr, gmf_period_statuses prev, cm_mthd_mst mthd
2094 WHERE curr.legal_entity_id = g_legal_entity_id
2095 AND curr.cost_type_id = g_cost_type_id
2096 AND curr.calendar_code = g_crev_curr_calendar
2097 AND curr.period_code = g_crev_curr_period
2098 AND prev.legal_entity_id = g_legal_entity_id
2099 AND prev.cost_type_id = g_crev_prev_cost_type_id
2100 AND prev.calendar_code = g_crev_prev_calendar
2101 AND prev.period_code = g_crev_prev_period
2102 AND mthd.cost_type_id = g_crev_prev_cost_type_id
2103 ;
2104 END IF;
2105 END IF;
2106
2107 --
2108 -- End of -- Populate Global variables
2109 --
2110
2111 END populate_global;
2112
2113 END gmf_subledger_pkg;