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