[Home] [Help]
PACKAGE BODY: APPS.GMF_ALLOC_PROC
Source
1 PACKAGE BODY GMF_ALLOC_PROC AS
2 /* $Header: gmfalocb.pls 120.11.12020000.2 2012/07/24 15:08:28 spabolu ship $ */
3
4 /*****************************************************************************
5 * PROCEDURE
6 * cost_alloc_proc
7 *
8 * DESCRIPTION
9 * This procedure allocates the expenses and is the
10 * main call.
11 *
12 * INPUT PARAMETERS
13 *
14 * v_from_alloc_code = From Allocation Code.
15 * v_to_alloc_code = To Allocation Code.
16 * v_refresh_interface = 0 do not refresh the interface table
17 * 1 Refresh the interface table.
18 *
19 * OUTPUT PARAMETERS
20 * errbuf holds the exception information
21 *
22 * HISTORY
23 * Jatinder Gogna - Changed fnd_flex_value to fnd_flex_values_vl
24 * for description columns
25 *
26 * Manish Gupta 02-MAR-99 Bug 841019
27 * Commented Package DBMS_OUTPUT.
28 * Jatinder Gogna -05/05/99 -Changed substr and instr to substrb and instrb
29 * as per AOL standards.
30 * Chetan Nagar 09/21/1999 Bug# 1001193
31 * Changed the format for variable X_amount from NUMBER(15) to NUMBER
32 * in procedure put_alloc_expenses.
33 * 28-Sep-1999 Jatinder Gogna - B1008555
34 * Modified to get the segment reference form Oracle Apps
35 * to take care of cases where segment_number and segment_name
36 * may be out of sequence.
37 * Chetan Nagar 10/20/1999 B1043070
38 * UTF issues resolved.
39 * 05-Nov-1999 Rajesh Seshadri Bug 1039469 - Performance enhancement
40 * The gmf_gl_get_balances package is redesigned to fill a table with
41 * the code combinations and then select from this table. The queries
42 * were also rewritten to enhance their performance.
43 * Wrapped all calls to fnd_file inside a procedure and included
44 * trace messages throughout for easier debugging
45 * 30-Oct-2002 R.Sharath Kumar Bug# 2641405
46 * Added NOCOPY hint
47 * 24-SEP-2003 Venkat Chukkapalli Bug# 3150227
48 * Modified code in parse_account() to parse OPM/OF accounts based on
49 * OF segment_num and not based on OF application_column_name.
50 * 26-SEP-2003 Venkat Chukkapalli Bug# 3163804
51 * Removed 3150227 fix and added code in parse_account() to
52 * initialize PL/SQL table x_segment.
53 * 26-JUL-2007 Himadri Chakroborty Bug #6133153
54 *changed the size of the variables l_fiscal_year and l_period_type to 15
55 * 09-Oct-2008 Pramod B.H. Bug 7458002
56 * a)Modified procedure process_alloc_dtl() to merge records into gl_aloc_dtl
57 * instead of deleting and inserting rows in gl_aloc_dtl.
58 * b)Modified procedure delete_allocations() to delete only obsoleted rows
59 * in gl_aloc_dtl.
60 * 31-Aug-2009 Parag Kanetkar Bug 9931797 Delete allocations for only those codes
61 * for which process is being run.
62 * 14-Jul-2011 Pramod B.H. Bug#12600219
63 * (i)Modified procedure put_alloc_expenses(). Now passing new out parameter
64 * X_to_segment_ccid in the call to gmf_gl_get_balances.proc_gl_get_balances().
65 * This account Id is now used to create record in gl_aloc_inp.
66 * (ii)Now printing few existing debug messages (debug level 3) into
67 * the logfile itself instead of the output file.
68 * Bug 14178149 Prasad marad. Added a ref cursor object to the argument of
69 * proc_gl_get_balances just to maintain the state and ref_cursor cannot be declared
70 * global
71 ******************************************************************************/
72 /* Package body global variables */
73 g_calendar_code cm_cldr_hdr.calendar_code%type;
74 g_period_code cm_cldr_dtl.period_code%type;
75 g_start_date DATE;
76 g_end_date DATE;
77 g_cost_type_id NUMBER(15);
78 g_le_name VARCHAR2(240);
79 g_structure_number NUMBER;
80 g_calling_module VARCHAR2(4000);
81 g_period_id NUMBER(15);
82 g_cost_mthd_code cm_mthd_mst.cost_mthd_code%type;
83 g_legal_entity_id NUMBER(15);
84 g_segment_delimeter VARCHAR2(2);
85 g_ledgername VARCHAR2(30);
86 g_chart_of_accounts_id NUMBER;
87
88
89 g_periodname VARCHAR2(15);
90 g_periodstatus VARCHAR2(1);
91 g_periodyear NUMBER(5);
92 g_periodnumber NUMBER(15);
93 g_quarternum NUMBER(15);
94 g_fiscal_year_desc VARCHAR2(240);
95 g_statuscode NUMBER(19);
96 g_period_start_date DATE;
97 g_period_end_date DATE;
98 g_calendar_name VARCHAR2(15);
99 g_period_type VARCHAR2(15);
100 g_fiscal_year VARCHAR2(10);
101
102 PROCEDURE end_proc (errbuf varchar2);
103
104 PROCEDURE alloc_log_msg( pi_file IN NUMBER, pi_msg IN VARCHAR2);
105
106 PROCEDURE cost_alloc_proc(errbuf OUT NOCOPY VARCHAR2,
107 retcode OUT NOCOPY VARCHAR2,
108 p_legal_entity_id IN NUMBER,
109 p_calendar_code IN VARCHAR2,
110 p_period_code IN VARCHAR2,
111 p_cost_type_id IN NUMBER,
112 p_fiscal_year IN VARCHAR2,
113 p_period_num IN NUMBER,
114 v_from_alloc_code IN gl_aloc_mst.alloc_code%TYPE,
115 v_to_alloc_code IN gl_aloc_mst.alloc_code%TYPE,
116 v_refresh_interface IN VARCHAR2
117 )
118 IS
119 x_status NUMBER(10);
120 x_refresh_int NUMBER;
121 x_retval BOOLEAN;
122 l_prev_req_count NUMBER;
123 BEGIN
124 g_cost_type_id := p_cost_type_id;
125 g_legal_entity_id := p_legal_entity_id;
126 g_periodyear := p_fiscal_year;
127 g_periodnumber := p_period_num;
128 g_calendar_code := p_calendar_code;
129 g_period_code := p_period_code;
130
131 BEGIN
132 SELECT COUNT(1)
133 INTO l_prev_req_count
134 FROM fnd_concurrent_requests
135 WHERE concurrent_program_id IN (SELECT a.concurrent_program_id
136 FROM fnd_concurrent_programs a,
137 fnd_application b
138 WHERE a.application_id = b.application_id
139 AND b.application_short_name = 'GMF'
140 AND a.concurrent_program_name = 'COSTALOC')
141 AND status_code in ('I','Q')
142 AND argument1 = p_legal_entity_id
143 AND argument2 = p_calendar_code
144 AND argument3 = p_period_code
145 AND argument4 = p_cost_type_id;
146 EXCEPTION
147 WHEN OTHERS THEN
148 l_prev_req_count := 0;
149 END;
150
151 IF (nvl(l_prev_req_count,0) > 0) THEN
152 alloc_log_msg(C_LOG_FILE, 'Process is already submitted for these parameters.');
153 retcode := 3;
154 errbuf := 'Process is already submitted for these parameters.';
155 RETURN;
156 END IF;
157
158 /**
159 * Uncomment the line below to wrote to a local file
160 * fnd_file.put_names( 'gmfaloc.log','gmfaloc.out','/tmp' );
161 */
162 alloc_log_msg( C_LOG_FILE, 'Starting Cost Allocation process' );
163 alloc_log_msg( C_LOG_FILE, 'Legal Entity Id: '|| g_legal_entity_id);
164 alloc_log_msg( C_LOG_FILE, 'Cost Calendar : '|| g_calendar_code);
165 alloc_log_msg( C_LOG_FILE, 'Period : '|| g_period_code );
166 alloc_log_msg( C_LOG_FILE, 'Cost Type : '|| g_cost_type_id );
167 alloc_log_msg( C_LOG_FILE, 'Fiscal Year : '|| g_periodyear );
168 alloc_log_msg( C_LOG_FILE, 'Fiscal Period : '|| g_periodnumber);
169 alloc_log_msg( C_LOG_FILE, 'Allocation Codes: ' || v_from_alloc_code || ' - ' ||v_to_alloc_code );
170 gmf_util.trace( 'Refresh Option: ' || v_refresh_interface , 1, 2 );
171
172
173 x_refresh_int := TO_NUMBER(v_refresh_interface);
174
175 IF( x_refresh_int = 1 )
176 THEN
177 alloc_log_msg( C_LOG_FILE, 'Refresh Interface: Yes' );
178 ELSE
179 alloc_log_msg( C_LOG_FILE, 'Refresh Interface: No' );
180 END IF;
181 /* sschinch INVCONV*/
182 BEGIN
183 IF (p_calendar_code IS NOT NULL AND p_period_code IS NOT NULL) THEN
184 SELECT period_id
185 INTO g_period_id
186 FROM gmf_period_statuses
187 WHERE legal_entity_id = p_legal_entity_id
188 AND calendar_code = p_calendar_code
189 AND period_code = p_period_code
190 AND cost_type_id = p_cost_type_id;
191
192 SELECT cost_mthd_code
193 INTO g_cost_mthd_code
194 FROM cm_mthd_mst
195 WHERE cost_type_id = p_cost_type_id;
196 END IF;
197 EXCEPTION
198 WHEN NO_DATA_FOUND THEN
199 alloc_log_msg(C_LOG_FILE, 'Invalid period ID');
200 retcode := 3;
201 errbuf := 'Invalid Period ID ';
202 RETURN;
203 END;
204
205 x_status := get_legal_entity_details;
206 IF (x_status < 0) THEN
207 retcode := 3;
208 errbuf := 'No fiscal policy defined ';
209 RETURN;
210 END IF;
211
212 alloc_log_msg(C_LOG_FILE, 'Legal Entity: ' || g_le_name);
213 alloc_log_msg(C_LOG_FILE, 'Ledger Id: ' || ' ('||to_char(p_fiscal_plcy.ledger_id)||')');
214 /*alloc_log_msg(C_LOG_FILE, 'Segment Delimiter '||''''||p_fiscal_plcy.segment_delimiter||'''');*/
215
216 /*The procedure below allocates the cost.*/
217 cost_allocate(v_from_alloc_code,v_to_alloc_code,x_refresh_int,x_status);
218
219 IF (x_status < 0) THEN
220 x_retval := fnd_concurrent.set_completion_status('ERROR',NULL);
221 END IF;
222 COMMIT;
223
224 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
225 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,C_MODULE||'.end','Cost Allocation process completed successfully');
226 END IF;
227
228 EXCEPTION
229 WHEN utl_file.invalid_path THEN
230 retcode := 3;
231 errbuf := 'Invalid path - '||to_char(SQLCODE) || ' ' || SQLERRM;
232 end_proc (errbuf);
233 WHEN utl_file.invalid_mode THEN
234 retcode := 3;
235 errbuf := 'Invalid Mode - '||to_char(SQLCODE) || ' ' || SQLERRM;
236 end_proc (errbuf);
237 WHEN utl_file.invalid_filehandle then
238 retcode := 3;
239 errbuf := 'Invalid filehandle - '||to_char(SQLCODE) || ' ' || SQLERRM;
240 end_proc (errbuf);
241 WHEN utl_file.invalid_operation then
242 retcode := 3;
243 errbuf := 'Invalid operation - '||to_char(SQLCODE) || ' ' || SQLERRM;
244 end_proc (errbuf);
245 WHEN utl_file.write_error then
246 retcode := 3;
247 errbuf := 'Write error - '||to_char(SQLCODE) || ' ' || SQLERRM;
248 end_proc (errbuf);
249 WHEN others THEN
250 retcode := 3;
251 errbuf := to_char(SQLCODE) || ' ' || SQLERRM;
252 end_proc (errbuf);
253 END cost_alloc_proc;
254
255
256 PROCEDURE end_proc (errbuf varchar2) IS
257 x_retval boolean;
258 BEGIN
259 x_retval := fnd_concurrent.set_completion_status('ERROR',NULL);
260 /* INVCONV sschinch */
261 -- Update the status to not running
262 /* UPDATE cm_alpr_ctl
263 SET running_ind = 0,
264 ended_on = sysdate
265 WHERE calendar_code = P_control_record.calendar_code
266 AND period_code = P_control_record.period_code;
267 */
268 COMMIT;
269
270 END end_proc;
271
272
273 /******************************************************************************
274 * PROCEDURE
275 * delete_allocations
276 * DESCRIPTION
277 * Deletes all allocations for the current calendar and period.
278 *
279 * INPUT PARAMETERS
280 * v_from_alloc_code
281 * v_to_alloc_code
282 * v_calendar_code
283 * v_period
284 *
285 * AUTHOR
286 * Sukarna Reddy 09/24/98
287 *
288 * OUTPUT PARAMETERS
289 * v_status = 0 No row found for deletion and can continue
290 * = -1 Fatal Error
291 *
292 * 31-Aug-2009 Parag Kanetkar Bug 9931797 Delete allocations for only those codes
293 * for which process is being run.
294 *******************************************************************************/
295
296 PROCEDURE delete_allocations(
297 v_from_alloc_code VARCHAR2,
298 v_to_alloc_code VARCHAR2,
299 v_status OUT NOCOPY NUMBER
300 )
301 IS
302 -- Bug 12561766 begins
303 CURSOR events_To_Delete IS
304 SELECT event_id, ledger_id FROM gmf_xla_extract_headers
305 WHERE entity_code = 'REVALUATION'
306 AND event_class_code = 'GLCOSTALOC'
307 AND accounted_flag IS NOT NULL
308 AND transaction_id IN
309 (SELECT allocdtl_id
310 FROM gl_aloc_dtl
311 WHERE period_id = g_period_id
312 AND cost_type_id = g_cost_type_id
313 AND nvl(gl_posted_ind, 0) <> 1
314 AND (alloc_id,line_no) IN
315 (SELECT b.alloc_id, b.line_no
316 FROM gl_aloc_mst m,gl_aloc_bas b
317 WHERE m.alloc_id = b.alloc_id
318 AND m.legal_entity_id = g_legal_entity_id
319 AND ( b.delete_mark = 1 OR m.delete_mark = 1)
320 AND m.alloc_code BETWEEN nvl(v_from_alloc_code,m.alloc_code) and
321 nvl(v_to_alloc_code ,m.alloc_code)
322 )
323 );
324
325 l_event_count NUMBER := 0;
326 -- Bug 12561766 ends
327
328 l_local_module VARCHAR2(80) := '.delete_allocations';
329 BEGIN
330 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
331 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,c_module||l_local_module||'.begin','Deleting Allocations');
332 END IF;
333 /* Bug 7458002 - Commented
334 DELETE FROM
335 gl_aloc_dtl
336 WHERE
337 period_id = g_period_id AND
338 cost_type_id = g_cost_type_id
339 AND alloc_id IN (
340 SELECT alloc_id
341 FROM gl_aloc_mst
342 WHERE legal_entity_id = g_legal_entity_id
343 AND alloc_code between nvl(v_from_alloc_code,alloc_code)
344 AND nvl(v_to_alloc_code,alloc_code)
345 ); */
346
347 /* Bug 7458002 - To delete only obsoleted rows in gl_aloc_dtl */
348 /* Bug 9931797 Customer may be running for only a subset of allocation code
349 Prior query then deletes rest of the data */
350 /* DELETE FROM
351 gl_aloc_dtl
352 WHERE period_id = g_period_id
353 AND cost_type_id = g_cost_type_id
354 AND (alloc_id,line_no) NOT IN (
355 SELECT b.alloc_id, b.line_no
356 FROM gl_aloc_mst m,gl_aloc_bas b
357 WHERE m.alloc_id = b.alloc_id
358 AND m.legal_entity_id = g_legal_entity_id
359 AND b.delete_mark = 0
360 AND m.delete_mark = 0
361 AND m.alloc_code BETWEEN nvl(v_from_alloc_code,m.alloc_code) and
362 nvl(v_to_alloc_code ,m.alloc_code)
363 ); */
364
365 /* Bug 9931797 Replacement */
366 -- Begin Bug 12561766
367 FOR l_event IN events_To_Delete LOOP
368
369 l_event_count := l_event_count + 1;
370
371 INSERT INTO xla_events_int_gt
372 (entity_id
373 ,application_id
374 ,ledger_id
375 ,entity_code
376 ,event_status_code
377 ,event_id
378 )
379 SELECT
380 xe.entity_id
381 ,xe.application_id
382 ,l_event.ledger_id
383 ,'REVALUATION'
384 ,xe.event_status_code
385 ,xe.event_id
386 FROM xla_events xe
387 WHERE xe.application_id = 555
388 AND xe.event_id = l_event.event_id
389 AND xe.event_type_code = 'GLCOSTALOC';
390
391 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
392 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,c_module||l_local_module, 'Found Expense Allocation event to Delete '||l_event.event_id);
393 END IF;
394
395 END LOOP;
396
397 alloc_log_msg( C_LOG_FILE, ' Number of Allocation events to delete = ' || l_event_count );
398
399 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
400 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,c_module||l_local_module, 'Deleting Expense Allocation Events Extract Lines ');
401 END IF;
402
403 DELETE FROM gmf_xla_extract_lines
404 WHERE Header_id IN (SELECT header_id FROM gmf_xla_extract_headers
405 WHERE entity_code = 'REVALUATION'
406 AND event_class_code = 'GLCOSTALOC'
407 AND accounted_flag IS NOT NULL
408 AND event_id IN (select event_id from xla_events_int_gt)
409 );
410
411 alloc_log_msg( C_LOG_FILE, TO_CHAR(SQL%ROWCOUNT) || ' Rows deleted from gmf_xla_extract_lines. ' );
412
413 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
414 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,c_module||l_local_module, 'Deleting Expense Allocation Events Extract Headers ');
415 END IF;
416
417 DELETE FROM gmf_xla_extract_headers
418 WHERE entity_code = 'REVALUATION'
419 AND event_class_code = 'GLCOSTALOC'
420 AND accounted_flag IS NOT NULL
421 AND event_id IN (select event_id from xla_events_int_gt);
422
423 alloc_log_msg( C_LOG_FILE, TO_CHAR(SQL%ROWCOUNT) || ' Rows deleted from gmf_xla_extract_lines. ' );
424
425 IF (l_event_count >= 1) THEN
426 alloc_log_msg( C_LOG_FILE, ' Calling xla_events_pkg.delete_bulk_events api to delete '|| l_event_count ||' invalid events.');
427 xla_events_pkg.delete_bulk_events( p_application_id => 555);
428 delete from xla_events_int_gt ;
429 END IF;
430 -- End Bug 12561766
431
432 DELETE FROM
433 gl_aloc_dtl
434 WHERE period_id = g_period_id
435 AND cost_type_id = g_cost_type_id
436 AND nvl(gl_posted_ind, 0) <> 1 -- Bug 12561766
437 AND (alloc_id,line_no) IN (
438 SELECT b.alloc_id, b.line_no
439 FROM gl_aloc_mst m,gl_aloc_bas b
440 WHERE m.alloc_id = b.alloc_id
441 AND m.legal_entity_id = g_legal_entity_id
442 AND ( b.delete_mark = 1 OR m.delete_mark = 1)
443 AND m.alloc_code BETWEEN nvl(v_from_alloc_code,m.alloc_code) and
444 nvl(v_to_alloc_code ,m.alloc_code)
445 );
446
447
448
449 alloc_log_msg( C_LOG_FILE, TO_CHAR(SQL%ROWCOUNT) || ' Rows deleted from Allocations table gl_aloc_dtl' );
450
451 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
452 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,c_module||l_local_module||'.end','Deleting Allocations');
453 END IF;
454
455 EXCEPTION
456 WHEN NO_DATA_FOUND THEN
457 alloc_log_msg(C_LOG_FILE, '0 Rows deleted from gl_aloc_dtl ');
458 v_status := 0;
459 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
460 THEN
461 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,c_module||l_local_module,'0 Rows deleted from gl_aloc_dtl');
462 END IF;
463
464 WHEN OTHERS THEN
465 alloc_log_msg(C_LOG_FILE, to_char(SQLCODE) || ' ' || SQLERRM);
466 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
467 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,c_module||l_local_module,to_char(SQLCODE) || ' ' || SQLERRM);
468 END IF;
469 v_status := -1;
470
471 END delete_allocations;
472
473 /********************************************************************************
474 * FUNCTION
475 * get_legal_entity_details
476 *
477 * DESCRIPTION
478 *
479 * The procedure below retrives the company details and caches the information
480 * in package specification variable record.
481 *
482 * AUTHOR
483 * sukarna Reddy
484 *
485 * INPUT PARAMETERS
486 * <None>
487 * OUTPUT PARAMETERS
488 * <None>
489 * RETURN
490 * -1 = No Calendar Exist
491 * -2 = No Fiscal policy Exist
492 * -3 = Failed to retrives Account Masks
493 * HISTORY
494 * 10-Nov-99 Rajesh Seshadri - changed the cur_get_of_seg_deli to use a
495 * a parameter instead of a global variable
496 *
497 ********************************************************************************/
498
499 FUNCTION get_legal_entity_details RETURN NUMBER IS
500 CURSOR Cur_get_le(p_period_id NUMBER) IS
501 SELECT gps.legal_entity_id,
502 xep.name
503 FROM gmf_period_statuses gps,
504 xle_entity_profiles xep
505 WHERE gps.period_id = p_period_id
506 AND gps.legal_entity_id = xep.legal_entity_id
507 AND gps.delete_mark = 0;
508
509 CURSOR Cur_get_fiscal_plcy(p_le_id NUMBER) IS
510 SELECT gfp.*
511 FROM gmf_fiscal_policies gfp
512 WHERE gfp.legal_entity_id = p_le_id
513 AND gfp.delete_mark = 0;
514
515 CURSOR cur_get_of_seg_deli( p_ledger_id gl_ledgers.ledger_id%TYPE )
516 IS
517 SELECT
518 concatenated_segment_delimiter,fifstr.id_flex_num
519 FROM
520 gl_ledger_le_v gll,
521 fnd_id_flex_structures fifstr,
522 fnd_application fa
523 WHERE
524 gll.chart_of_accounts_id = fifstr.id_flex_num
525 AND gll.ledger_id = p_ledger_id
526 AND fifstr.id_flex_code = 'GL#'
527 AND fifstr.application_id = fa.application_id
528 AND fa.application_short_name = 'SQLGL';
529
530 Cur_fiscal_plcy Cur_get_fiscal_plcy%ROWTYPE;
531 /*x_co_code sy_orgn_mst.co_code%TYPE; INVCONV sschinch*/
532 x_le_id NUMBER(15);
533 x_status NUMBER(10);
534 l_local_module VARCHAR2(80);
535 BEGIN
536 l_local_module := c_module||'.get_legal_entity_details';
537
538 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
539 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,l_local_module||'.begin','Starting');
540 END IF;
541
542
543 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
544 THEN
545 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_local_module,'Retrieving Legal Entity information for calendar: '|| g_calendar_code ||' and Period: '||g_period_code);
546 END IF;
547
548 OPEN Cur_get_le(g_period_id);
549 FETCH Cur_get_le INTO x_le_id,g_le_name;
550 IF (Cur_get_le%NOTFOUND) THEN
551 CLOSE Cur_get_le;
552
553 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
554 THEN
555 /*alloc_log_msg(2, 'No legal entity exists for cost calendar: ' ||g_calendar_code);*/
556 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,l_local_module,'No legal entity exists for cost calendar: ' ||g_calendar_code ||' and Period: '||g_period_code);
557 END IF;
558 RETURN(-1); /* No calendar exists.*/
559 END IF;
560 CLOSE Cur_get_le;
561
562 /* Fetch the fiscal policy information for the company retrieved */
563 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
564 THEN
565 /*alloc_log_msg( 2,'Retrieving Fiscal policy for legal entity: ' ||g_le_name );*/
566 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,l_local_module,'Retrieving Fiscal policy for legal entity: ' ||g_le_name);
567 END IF;
568
569 OPEN Cur_get_fiscal_plcy(X_le_id);
570 FETCH Cur_get_fiscal_plcy INTO P_fiscal_plcy;
571 IF (Cur_get_fiscal_plcy%NOTFOUND) THEN
572 CLOSE Cur_get_fiscal_plcy;
573 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
574 THEN
575 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,l_local_module,'Retrieving Fiscal policy for legal entity: ' ||g_le_name);
576 END IF;
577 alloc_log_msg(C_LOG_FILE, 'No fiscal policy is defined for legal_entity ' || g_le_name);
578 RETURN(-2); /* No fiscal policy is defined for this company.*/
579 END IF;
580 CLOSE Cur_get_fiscal_plcy;
581
582 OPEN cur_get_of_seg_deli( P_fiscal_plcy.ledger_id);
583 FETCH cur_get_of_seg_deli INTO g_segment_delimeter,g_structure_number;
584 CLOSE cur_get_of_seg_deli;
585
586 /* When Failed to retrieve Segment Delimiter from oracle Financials then
587 assign the default segment delimeter defined in the fiscal policy */
588
589 /*IF (P_of_segment_delimeter IS NULL) THEN
590 P_of_segment_delimeter := P_fiscal_plcy.segment_delimiter;
591 alloc_log_msg(2, 'No OF Delimiter defined, using OPM delimiter: ' || P_fiscal_plcy.segment_delimiter);
592 END IF;
593 */
594
595 alloc_log_msg( C_LOG_FILE, 'Retrieved Fiscal Policy Details successfully' );
596
597 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
598 THEN
599 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,l_local_module||'.end','Retrieved discal policy details successfully');
600 END IF;
601 RETURN(0);
602 END get_legal_entity_details;
603
604
605 /****************************************************************************
606 * PROCEDURE
607 * Delete_interface
608 *
609 * DESCRIPTION
610 * Deletes the interface row for a given criteria when refresh ind is set.
611 *
612 * AUTHOR
613 * Sukarna Reddy
614 *
615 * INPUT PARAMETERS
616 *
617 * v_calendar_code = Calendar code
618 * v_period_code = period code
619 *
620 * OUTPUT PARAMETERS
621 *
622 * v_status = 0 No rows to delete
623 * = -1 Fatal Error
624 * HISTORY
625 * Sukarna Reddy Modified code for convergence July 20
626 * prasad marada bug13803220 deleting from gl_aloc)inp based on allocation code parameters
627 ****************************************************************************/
628
629 PROCEDURE delete_interface(v_from_alloc_code VARCHAR2,
630 v_to_alloc_code VARCHAR2,
631 v_status OUT NOCOPY NUMBER) IS
632 l_local_module VARCHAR2(80) := '.delete_interface';
633 BEGIN
634 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
635 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,c_module||l_local_module||'.begin','Delete interface');
636 END IF;
637
638 alloc_log_msg(C_LOG_FILE, 'Deleting rows from gl_aloc_inp for calendar '||g_calendar_code ||
639 ' and period '||g_period_code || ' and cost type '||g_cost_mthd_code ||' From Alloc code '||v_from_alloc_code ||'To Alloc code '||v_to_alloc_code );
640
641 DELETE
642 FROM gl_aloc_inp
643 WHERE calendar_code = g_calendar_code
644 AND period_code = g_period_code
645 AND alloc_id IN (SELECT b.alloc_id
646 FROM gl_aloc_mst m, gl_aloc_bas b
647 WHERE m.alloc_id = b.alloc_id
648 AND m.legal_entity_id = g_legal_entity_id
649 AND m.alloc_code BETWEEN nvl(v_from_alloc_code, m.alloc_code) AND
650 nvl(v_to_alloc_code, m.alloc_code)
651 );
652
653 v_status :=0;
654
655 alloc_log_msg( C_LOG_FILE, TO_CHAR(SQL%ROWCOUNT) || ' Rows deleted from Interface table gl_aloc_inp' );
656
657 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
658 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,c_module||l_local_module||'.end','Delete interface');
659 END IF;
660
661 EXCEPTION
662
663 WHEN NO_DATA_FOUND THEN
664 alloc_log_msg(C_LOG_FILE, '0 rows deleted from gl_aloc_inp');
665 v_status := 0;
666 WHEN OTHERS THEN
667 alloc_log_msg(C_LOG_FILE, to_char(SQLCODE) || ' ' || SQLERRM);
668 v_status := -1;
669 END delete_interface;
670
671 /****************************************************************************
672 * PROCEDURE
673 * cost_allocate
674 * DESCRIPTION
675 * Deletes the existing allocations for current calendar and period specified
676 * if refresh indicator is 1 , deletes interface rows from gl_alloc_inp for
677 * calendar and period specified.Populates the interface table picking
678 * rows from expense table, allocation basis table. if refresh interface is
679 * not set , refreshes the interface table with fixed percentages in the
680 * allocation basis. Finally calculates the percentages and expense amount
681 * and populates allocation table.
682 *
683 * AUTHOR
684 * Sukarna Reddy Date : 09/18/98
685 *
686 * INPUT PARAMETERS
687 *
688 * v_from_alloc_code
689 * v_to_alloc_code
690 * v_refresh_interface
691 *
692 * OUTPUT PARAMETERS
693 * v_status = -1 Fatal error occured while deleting allocations
694 * = -2 Fatal error occured while deleting interface rows.
695 * = 0 Successfull
696 *
697 * HISTORY
698 * Chetan Nagar 19-Feb-2001 B1418787
699 * List out all the burden codes that have total fixed percentage
700 * more than 100. These allocation codes will be ignored from processing.
701 * Sukarna Reddy Modified code for convergence July 2005
702 ***************************************************************************/
703
704 PROCEDURE cost_allocate(v_from_alloc_code VARCHAR2,
705 v_to_alloc_code VARCHAR2,
706 v_refresh_interface NUMBER,
707 v_status OUT NOCOPY NUMBER
708 )
709 IS
710 x_status NUMBER;
711
712 CURSOR cur_alloc_fixed_invalid IS
713 SELECT m.alloc_code alloc_code,
714 sum(b.fixed_percent) total_percentage
715 FROM gl_aloc_mst m, gl_aloc_bas b
716 WHERE m.alloc_id = b.alloc_id
717 AND m.legal_entity_id = g_legal_entity_id
718 AND b.alloc_method = 1
719 AND m.alloc_code BETWEEN NVL(v_from_alloc_code,m.alloc_code)
720 AND nvl(v_to_alloc_code,m.alloc_code)
721 AND m.delete_mark = 0
722 AND b.delete_mark = 0
723 GROUP BY m.alloc_code
724 HAVING sum(b.fixed_percent) <> 100
725 ORDER BY 1;
726 l_local_module VARCHAR2(80) := '.cost_allocate';
727 l_previous_module VARCHAR2(4000);
728 BEGIN
729 l_previous_module := g_calling_module;
730 g_calling_module := g_calling_module||l_local_module;
731
732 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
733 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,g_calling_module||'.begin','Allocating Cost');
734 END IF;
735
736 /* B1418787 List out all the allocation codes that exceed 100% */
737 alloc_log_msg( C_LOG_FILE, 'Following allocation codes will be ignored as the total percentage does not equal 100.');
738
739 alloc_log_msg( C_LOG_FILE, 'Allocation Code - Total Percentage');
740 alloc_log_msg( C_LOG_FILE, '=============== ================');
741
742 FOR cur_alloc_fixed_invalid_tmp IN cur_alloc_fixed_invalid LOOP
743 alloc_log_msg( C_LOG_FILE, rpad(cur_alloc_fixed_invalid_tmp.alloc_code, 15, ' ') || ' - ' ||rpad(cur_alloc_fixed_invalid_tmp.total_percentage, 16, ' '));
744 END LOOP;
745 /* Bug 7458002 - Commented
746 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
747 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,g_calling_module||'.delete_allocations','deleting Allocations ...');
748 END IF;
749 delete_allocations(v_from_alloc_code, v_to_alloc_code, x_status);
750
751 IF (x_status < 0)
752 THEN
753 v_status := -1;
754 return;
755 END IF;*/
756
757 IF (v_refresh_interface = 1)
758 THEN
759 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
760 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,g_calling_module||'.delete_interface','deleting Interface data...');
761 END IF;
762
763 delete_interface(v_from_alloc_code, v_to_alloc_code, x_status );
764 IF (x_status < 0)
765 THEN
766 v_status := -2;
767 return;
768 END IF;
769 alloc_log_msg( C_LOG_FILE, 'Retrieving Expenses ...');
770
771 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
772 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,g_calling_module||'.get_expenses','Retrieving Expenses ....');
773 END IF;
774 get_expenses(v_from_alloc_code, v_to_alloc_code);
775
776 g_calling_module := l_previous_module||l_local_module;
777
778 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
779 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,g_calling_module||'.get_alloc_basis','Retrieving Basis Information ....');
780 END IF;
781 alloc_log_msg( C_LOG_FILE, 'Retrieving Basis Information ...');
782 get_alloc_basis(v_from_alloc_code, v_to_alloc_code);
783 g_calling_module := l_previous_module||l_local_module;
784 ELSE
785 /* Do not refresh the interface */
786
787 alloc_log_msg( C_LOG_FILE, 'Processing Fixed Precentage Information ...');
788
789 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
790 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,g_calling_module||'.refresh_fixed','Processing Fixed Percentage Information ....');
791 END IF;
792 refresh_fixed(v_from_alloc_code,v_to_alloc_code);
793 g_calling_module := l_previous_module||l_local_module;
794 END IF;
795
796 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
797 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,g_calling_module||'.process_alloc_dtl','Allocating Expenses ...');
798 END IF;
799
800 alloc_log_msg( C_LOG_FILE, 'Allocating Expenses ...');
801 process_alloc_dtl(v_from_alloc_code,v_to_alloc_code);
802 g_calling_module := l_previous_module||l_local_module;
803
804 COMMIT;
805 v_status := 0;
806
807 EXCEPTION
808 WHEN others THEN
809 alloc_log_msg( C_LOG_FILE, to_char(SQLCODE) || ' ' || SQLERRM);
810 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
811 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,g_calling_module,to_char(SQLCODE) || ' ' || SQLERRM);
812 END IF;
813 v_status := -1;
814 END cost_allocate;
815
816
817 /*****************************************************************
818 * PROCEDURE
819 * get_expenses
820 *
821 * DESCRIPTION
822 * Retrieves the expense accounts from expenses table such as
823 * gl_aloc_exp and populates the interface table with the
824 * expense amount retrieved from oracle financials.
825 *
826 * AUTHOR
827 * sukarna Reddy 09/18/98
828 *
829 * INPUT PARAMETERS
830 * v_from_alloc_code
831 * v_to_alloc_code
832 *
833 * OUTPUT PARAMETERS
834 * <None>
835 * HISTORY
836 * Sukarna Reddy modified code for convergence July 2005.
837 ******************************************************************/
838
839 PROCEDURE get_expenses(v_from_alloc_code VARCHAR2,
840 v_to_alloc_code VARCHAR2)
841 IS
842
843 CURSOR cur_get_exp IS
844 SELECT
845 a.alloc_code as exp_alloc_code,
846 e.alloc_id as exp_alloc_id,
847 e.line_no as exp_line_no,
848 e.from_account_id as exp_from_account,
849 e.to_account_id as exp_to_account,
850 e.balance_type as exp_balance_type, e.exp_ytd_ptd
851 FROM
852 gl_aloc_exp e,
853 gl_aloc_mst a
854 WHERE
855 e.alloc_id = a.alloc_id
856 and a.legal_entity_id = g_legal_entity_id
857 and e.delete_mark = 0
858 and a.delete_mark = 0
859 and a.alloc_code between nvl(v_from_alloc_code,a.alloc_code) and
860 nvl(v_to_alloc_code,a.alloc_code)
861 and 100 =
862 (select decode(max(b.alloc_method), 0, 100, 1, sum(fixed_percent))
863 from gl_aloc_bas b
864 where b.alloc_id = a.alloc_id
865 and b.delete_mark = 0
866 )
867 ;
868
869 l_local_module VARCHAR2(80) := '.get_expenses';
870 l_previous_module VARCHAR2(4000);
871 BEGIN
872 l_previous_module := g_calling_module;
873 g_calling_module := g_calling_module||l_local_module;
874
875 FOR cur_get_exp_tmp IN cur_get_exp
876 LOOP
877 alloc_log_msg( C_LOG_FILE, ' Processing allocation code '||cur_get_exp_tmp.exp_alloc_code);
878
879 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
880 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,g_calling_module||'.put_alloc_expenses',' Processing allocation code '||cur_get_exp_tmp.exp_alloc_code);
881 END IF;
882
883 put_alloc_expenses(
884 cur_get_exp_tmp.exp_alloc_id,
885 cur_get_exp_tmp.exp_line_no,
886 cur_get_exp_tmp.exp_from_account,
887 cur_get_exp_tmp.exp_to_account,
888 cur_get_exp_tmp.exp_balance_type,
889 cur_get_exp_tmp.exp_ytd_ptd,
890 1
891 );
892
893 END LOOP;
894
895 g_calling_module := l_previous_module;
896
897 END get_expenses;
898
899 /********************************************************************
900 * PROCEDURE
901 * put_alloc_expenses
902 *
903 * DESCRIPTION
904 * Retrieves the balances from Oracle financials for a range of
905 * accounts specified and populates the interface table with the
906 * expense information.
907 *
908 * AUTHOR
909 * Sukarna Reddy 09/18/98
910 *
911 * INPUT PARAMETERS
912 * v_alloc_id
913 * v_line_no
914 * v_from_segment
915 * v_to_segment
916 * v_balance_type
917 * v_ytd_ptd
918 * v_account_type
919 *
920 * OUTPUT PARAMETERS
921 * <None>
922 *
923 * HISTORY
924 * Sukarna Reddy Modified code for convergence. July 2005.
925 * Himadri Chakroborty Modified code for Bug 6133153.July 2007.
926 * 31-DEC-2009 Uday Phadtare Bug 9241807.
927 * When enabled segments in Accounting Flexfield are not in sequence, concatenated_segments
928 * string need to be formatted properly before passing it to API fnd_flex_ext.get_ccid()
929 * to avoid error 'Values have not been entered for one or more required segments'.
930 * Bug 14178149 Prasad marada. Added a ref cursor object to the argument of
931 * proc_gl_get_balances just to maintain the state and ref_cursor cannot be declared
932 * global
933 **********************************************************************/
934
935 PROCEDURE put_alloc_expenses
936 (
937 v_alloc_id NUMBER,
938 v_line_no VARCHAR2,
939 v_from_segment NUMBER,
940 v_to_segment NUMBER,
941 v_balance_type NUMBER,
942 v_ytd_ptd NUMBER,
943 v_account_type NUMBER
944 )
945 IS
946 CURSOR cur_bal_type IS
947 SELECT decode(v_balance_type,'0','A','1','B','2','A')
948 FROM dual;
949
950 CURSOR cur_currency IS
951 SELECT decode(v_balance_type,'0','STAT',NULL)
952 FROM dual;
953
954 CURSOR Cur_get_ledgerinfo
955 (
956 p_ledger_id gl_period_statuses.ledger_id%TYPE
957 )
958 IS
959 SELECT gl.short_name,
960 gl.period_set_name,
961 gl.accounted_period_type,
962 gl.chart_of_accounts_id
963 FROM gl_ledgers gl
964 WHERE gl.ledger_id = P_ledger_id
965 AND rownum = 1;
966
967 X_chart_of_accounts NUMBER(10);
968 X_period_num NUMBER(10);
969 X_period_year NUMBER(10);
970 X_currency_code VARCHAR2(4);
971 X_to_segment VARCHAR2(4000);
972 X_to_segment_ccid NUMBER DEFAULT 0; /*Bug#12600219*/
973 X_in_actual_flag VARCHAR2(2);
974 X_amount NUMBER DEFAULT 0;
975 X_segment_delimiter VARCHAR(2);
976 X_row_to_fetch NUMBER(10) DEFAULT 0;
977 X_error_status NUMBER(10) DEFAULT 0;
978 X_from_segment VARCHAR2(4000);
979 X_in_ytd_ptd NUMBER(10);
980 x_created_by NUMBER(10) DEFAULT FND_PROFILE.VALUE('USER_ID');
981 x_return_status BOOLEAN;
982 l_local_module VARCHAR2(80) := '.put_alloc_expenses';
983 l_previous_module VARCHAR2(4000);
984 l_mesg_text VARCHAR2(100);
985 l_prev_local_module VARCHAR2(4000);
986 x_to_account_id NUMBER(15);
987 l_to_segment_id NUMBER;
988 l_from_segment_id NUMBER;
989 l_n_segs NUMBER;
990 l_seg_array fnd_flex_ext.SegmentArray;
991 l_gl_fiscal_year NUMBER(15);
992 l_gl_period NUMBER(15);
993 l_return_status NUMBER(5);
994 /* B6133153 GL allocation of expenses are not showing after running the GL allocation process */
995 -- l_fiscal_year VARCHAR2(10);
996 -- l_period_type VARCHAR2(10);
997 l_fiscal_year VARCHAR2(15);
998 l_period_type VARCHAR2(15);
999 x_ledger_id NUMBER := P_fiscal_plcy.ledger_id;
1000 X_account_type VARCHAR2(4);
1001 l_new_x_to_segment VARCHAR2(4000); --Bug 9241807
1002 X_cur_gl_get_balances GMF_GL_GET_BALANCES.cur_gl_get_balances_rc; /* Bug 14178149 */
1003
1004 BEGIN
1005 l_previous_module := g_calling_module;
1006 g_calling_module := g_calling_module||l_local_module;
1007 l_from_segment_id := v_from_segment;
1008 l_to_segment_id := v_to_segment;
1009
1010 IF x_ledger_id IS NOT NULL THEN
1011 OPEN Cur_get_ledgerinfo(x_ledger_id);
1012 FETCH Cur_get_ledgerinfo INTO g_ledgername, l_fiscal_year, l_period_type, g_chart_of_accounts_id;
1013 IF Cur_get_ledgerinfo%NOTFOUND THEN
1014 CLOSE Cur_get_ledgerinfo;
1015 FND_MESSAGE.SET_NAME('GMF', 'GL_NO_FISCAL_POLICY');
1016 alloc_log_msg(C_LOG_FILE,FND_MESSAGE.GET);
1017 END IF;
1018 CLOSE Cur_get_ledgerinfo;
1019 IF g_ledgername IS NULL THEN
1020 FND_MESSAGE.SET_NAME('GMF', 'GL_SOB_NOTSETUP');
1021 alloc_log_msg(C_LOG_FILE,FND_MESSAGE.GET);
1022 END IF;
1023 END IF;
1024
1025 open cur_bal_type;
1026 fetch cur_bal_type INTO x_in_actual_flag;
1027 close cur_bal_type;
1028
1029 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1030 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,g_calling_module,'Calling FND_FLEX_EXT.GET_SEGMENTS() API');
1031 END IF;
1032
1033 x_return_status := fnd_flex_ext.get_segments (
1034 application_short_name => 'SQLGL',
1035 key_flex_code => 'GL#',
1036 structure_number => g_structure_number,
1037 combination_id => v_from_segment,
1038 n_segments => l_n_segs,
1039 segments => l_seg_array
1040 );
1041
1042 IF (l_n_segs > 0) THEN
1043 FOR i IN 1..l_n_segs LOOP
1044 IF (l_seg_array(i) IS NOT NULL) THEN
1045 IF x_from_segment IS NOT NULL THEN
1046 x_from_segment := x_from_segment||g_segment_delimeter||l_seg_array(i);
1047 ELSE
1048 x_from_segment := l_seg_array(i);
1049 END IF;
1050 END IF;
1051 END LOOP;
1052 gmf_util.trace( ' From segment '||x_from_segment, 2, C_LOG_FILE );
1053 ELSE
1054 alloc_log_msg(C_LOG_FILE,FND_MESSAGE.GET);
1055 RETURN;
1056 END IF;
1057
1058 x_return_status := fnd_flex_ext.get_segments(application_short_name => 'SQLGL',
1059 key_flex_code => 'GL#',
1060 structure_number => g_structure_number,
1061 combination_id => v_to_segment,
1062 n_segments => l_n_segs,
1063 segments => l_seg_array
1064 );
1065 IF (l_n_segs > 0) THEN
1066 FOR i IN 1..l_n_segs LOOP
1067 IF x_to_segment IS NOT NULL THEN
1068 x_to_segment := x_to_segment||g_segment_delimeter||l_seg_array(i);
1069 ELSE
1070 x_to_segment := l_seg_array(i);
1071 END IF;
1072 END LOOP;
1073 gmf_util.trace( ' To segment '||x_to_segment, 2, C_LOG_FILE );
1074 ELSE
1075 alloc_log_msg(C_LOG_FILE,FND_MESSAGE.GET);
1076 RETURN;
1077 END IF;
1078
1079 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1080 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,g_calling_module,'Finished Calling FND_FLEX_EXT.GET_SEGMENTS() API');
1081 END IF;
1082
1083 x_in_ytd_ptd := v_ytd_ptd;
1084
1085 /* B8432783 Added back the currency code selection */
1086 /* Decode the currency code value for GEMMS balance_type. */
1087 OPEN cur_currency;
1088 FETCH cur_currency INTO X_currency_code;
1089 CLOSE cur_currency;
1090
1091 IF X_CURRENCY_CODE IS NULL THEN
1092 X_CURRENCY_CODE := P_fiscal_plcy.base_currency_code;
1093 END IF;
1094
1095 l_prev_local_module := g_calling_module;
1096
1097 WHILE (x_error_status <> 100 or x_error_status < 0)
1098 LOOP
1099
1100 gmf_util.trace( ' Values before call to get balances: ' ||
1101 X_ledger_id || ' , ' ||
1102 g_chart_of_accounts_id || ' , ' ||
1103 g_periodyear || ' , ' ||
1104 g_periodnumber || ' , ' ||
1105 X_account_type || ' , ' ||
1106 X_currency_code || ' , ' ||
1107 X_from_segment || ' , ' ||
1108 X_to_segment || ' , [' ||
1109 X_to_segment_ccid || '], ' || /*Bug#12600219*/
1110 X_in_actual_flag || ' , ' ||
1111 X_in_ytd_ptd || ' , ' ||
1112 X_amount || ' , ' ||
1113 X_segment_delimiter || ' , ' ||
1114 X_row_to_fetch || ' , ' ||
1115 X_error_status
1116 , 3, C_LOG_FILE );
1117
1118 gmf_gl_get_balances.proc_gl_get_balances(
1119 X_ledger_id,
1120 g_chart_of_accounts_id,
1121 g_periodyear,
1122 g_periodnumber,
1123 X_account_type,
1124 X_currency_code,
1125 X_from_segment,
1126 X_to_segment,
1127 X_in_actual_flag,
1128 X_in_ytd_ptd,
1129 X_amount,
1130 X_segment_delimiter,
1131 X_row_to_fetch,
1132 X_error_status,
1133 X_to_segment_ccid, /*Bug#12600219*/
1134 X_cur_gl_get_balances /* Bug 14178149 */
1135 );
1136
1137 /** Bug#12600219 - Commenting following code, as now we will be using X_to_segment_ccid to identify account
1138 --Begin Bug 9241807
1139 SELECT REPLACE(
1140 REPLACE(
1141 REPLACE(
1142 x_to_segment,
1143 X_segment_delimiter||X_segment_delimiter,
1144 X_segment_delimiter
1145 ),
1146 X_segment_delimiter||X_segment_delimiter,
1147 X_segment_delimiter
1148 ),
1149 X_segment_delimiter||X_segment_delimiter,
1150 X_segment_delimiter
1151 )
1152 INTO l_new_x_to_segment FROM dual;
1153 --End Bug 9241807
1154
1155 **/
1156
1157 gmf_util.trace( ' Values after call to get balances: ' ||
1158 X_ledger_id || ' , ' ||
1159 g_chart_of_accounts_id || ' , ' ||
1160 g_periodyear || ' , ' ||
1161 g_periodnumber || ' , ' ||
1162 X_account_type || ' , ' ||
1163 X_currency_code || ' , ' ||
1164 X_from_segment || ' , ' ||
1165 X_to_segment || ' , [' ||
1166 X_to_segment_ccid || '], ' || /*Bug#12600219*/
1167 l_new_x_to_segment || ' , ' ||
1168 X_in_actual_flag || ' , ' ||
1169 X_in_ytd_ptd || ' , ' ||
1170 X_amount || ' , ' ||
1171 X_segment_delimiter || ' , ' ||
1172 X_row_to_fetch || ' , ' ||
1173 X_error_status
1174 , 3, C_LOG_FILE );
1175
1176 /* Format the to segment brought from of table to OPM account key.*/
1177
1178 x_error_status := nvl(x_error_status,0);
1179
1180 IF (x_error_status <> 100 AND x_error_status >= 0) THEN
1181 /* INVCONV sschinch */
1182 /**Bug#12600219 - Replaced this code with following, as now we are using X_to_segment_ccid to identify account
1183 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1184 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,g_calling_module,'Calling FND_FLEX_EXT.GET_COMBINATION_ID() API');
1185 END IF;
1186 x_to_account_id := fnd_flex_ext.get_ccid(application_short_name => 'SQLGL',
1187 key_flex_code => 'GL#',
1188 structure_number => g_structure_number,
1189 validation_date => to_char(SYSDATE,FND_FLEX_EXT.DATE_FORMAT),
1190 concatenated_segments => l_new_x_to_segment
1191 );
1192 IF (x_to_account_id > 0) THEN
1193 insert_alloc_inp(v_alloc_id,
1194 v_line_no,
1195 v_account_type,
1196 x_to_account_id,
1197 x_amount
1198 );
1199 alloc_log_msg(C_LOG_FILE, ' Account: '||x_to_segment|| ' Amount = '||to_char(x_amount));
1200 ELSE
1201 l_mesg_text := FND_MESSAGE.GET;
1202 alloc_log_msg(C_LOG_FILE,l_mesg_text);
1203 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1204 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,g_calling_module,'Error returned from FND_FLEX_EXT.GET_COMBINATION_ID() API '||l_mesg_text);
1205 END IF;
1206 END IF;
1207 **/
1208
1209 /*****Bug#12600219 (START) - Inserting X_to_segment_ccid itself into gl_aloc_inp ******/
1210 IF (X_to_segment_ccid > 0) THEN
1211 insert_alloc_inp(v_alloc_id,
1212 v_line_no,
1213 v_account_type,
1214 X_to_segment_ccid,
1215 x_amount
1216 );
1217 alloc_log_msg(C_LOG_FILE, ' AccountId: '||to_char(X_to_segment_ccid)|| ' Amount = '||to_char(x_amount));
1218
1219 /* Now, fetching account using ccid just to display it in log (START)*/
1220 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1221 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,g_calling_module,'Calling FND_FLEX_EXT.GET_SEGMENTS() API');
1222 END IF;
1223
1224 x_return_status := fnd_flex_ext.get_segments(application_short_name => 'SQLGL',
1225 key_flex_code => 'GL#',
1226 structure_number => g_structure_number,
1227 combination_id => X_to_segment_ccid,
1228 n_segments => l_n_segs,
1229 segments => l_seg_array
1230 );
1231 x_to_segment:= NULL; /*re-initialize*/
1232 IF (l_n_segs > 0) THEN
1233 FOR i IN 1..l_n_segs LOOP
1234 IF x_to_segment IS NOT NULL THEN
1235 x_to_segment := x_to_segment||g_segment_delimeter||l_seg_array(i);
1236 ELSE
1237 x_to_segment := l_seg_array(i);
1238 END IF;
1239 END LOOP;
1240 alloc_log_msg(C_LOG_FILE,' [Acct:'||x_to_segment||']' );
1241 ELSE
1242 alloc_log_msg(C_LOG_FILE,FND_MESSAGE.GET);
1243 RETURN;
1244 END IF;
1245
1246 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1247 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,g_calling_module,'Finished Calling FND_FLEX_EXT.GET_SEGMENTS() API');
1248 END IF;
1249 /* Now, fetching account using ccid just to display it in log (END)*/
1250 ELSE
1251 alloc_log_msg(C_LOG_FILE, ' WARNING: Incorrect X_to_segment_ccid, NOT inserting into gl_aloc_inp : '||to_char(X_to_segment_ccid));
1252 END IF;
1253
1254 /*****Bug#12600219 (END) - Inserting X_to_segment_ccid itself into gl_aloc_inp ******/
1255
1256 END IF;
1257 /* end SSCHINCH */
1258 END LOOP;
1259
1260 x_error_status := 0;
1261 g_calling_module := l_previous_module;
1262
1263 END put_alloc_expenses;
1264
1265 /*******************************************************************
1266 * PROCEDURE
1267 * insert_alloc_inp
1268 *
1269 * DESCRIPTION
1270 * Inserts a row in to gl_aloc_inp.
1271 *
1272 * AUTHOR
1273 * Sukarna Reddy 09/18/98
1274 *
1275 * INPUT PARAMETERS
1276 * v_alloc_id
1277 * v_line_no
1278 * v_account_type
1279 * v_amount
1280 *
1281 * OUTPUT PARAMETERS
1282 * <None>
1283 *
1284 * HISTORY
1285 * Modified code for inventory convergence sschinch July 2005
1286 *******************************************************************/
1287
1288 PROCEDURE insert_alloc_inp(
1289 v_alloc_id NUMBER,
1290 v_line_no VARCHAR2,
1291 v_account_type NUMBER,
1292 v_to_segment NUMBER,
1293 v_amount NUMBER
1294 )
1295
1296 IS
1297 l_local_module VARCHAR2(80):= '.insert_alloc_inp';
1298 BEGIN
1299 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1300 THEN
1301 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,g_calling_module||l_local_module||'.begin','Inserting ...');
1302 END IF;
1303
1304 INSERT
1305 INTO
1306 gl_aloc_inp
1307 (
1308 gl_aloc_inp_id,
1309 calendar_code,
1310 period_code,
1311 alloc_id,
1312 line_no,
1313 account_key_type,
1314 account_id,
1315 amount,
1316 creation_date,
1317 created_by,
1318 last_update_date,
1319 last_updated_by,
1320 last_update_login,
1321 trans_cnt,
1322 delete_mark,
1323 text_code
1324 )
1325 VALUES
1326 (
1327 gem5_gl_aloc_inp_id_s.nextval,
1328 g_calendar_code,
1329 g_period_code,
1330 v_alloc_id,
1331 v_line_no,
1332 v_account_type,
1333 v_to_segment,
1334 nvl(v_amount,0),
1335 sysdate,
1336 P_created_by,
1337 sysdate,
1338 P_created_by,
1339 NULL,
1340 0,
1341 0,
1342 NULL
1343 );
1344 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1345 THEN
1346 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,g_calling_module||l_local_module||'.end','Completed Inserting data into interface...');
1347 END IF;
1348 EXCEPTION
1349 WHEN OTHERS THEN
1350 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1351 THEN
1352 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,g_calling_module||l_local_module,to_char(SQLCODE)||' '||SQLERRM);
1353 END IF;
1354 END insert_alloc_inp;
1355
1356 /* *********************************************************
1357 * PROCEDURE
1358 * get_alloc_basis
1359 *
1360 * DESCRIPTION
1361 * Retrieves the allocation basis information,Inserts basis
1362 * Information with balances retrieved from financials
1363 * AUTHOR
1364 * Sukarna Reddy 09/18/98
1365 *
1366 * INPUT PARAMETERS
1367 * v_co_code
1368 * v_from_alloc_code
1369 * v_to_alloc_code
1370 *
1371 * OUTPUT PARAMETERS
1372 * <None>
1373 *
1374 * HISTORY
1375 * Ignore all the burden codes that have total
1376 * fixed percentage not equal to 100.
1377 ************************************************************/
1378 PROCEDURE get_alloc_basis(
1379 v_from_alloc_code VARCHAR2,
1380 v_to_alloc_code VARCHAR2
1381 )
1382 IS
1383
1384 CURSOR cur_alloc_basis IS
1385 SELECT m.alloc_id,
1386 m.alloc_code,
1387 b.line_no,
1388 b.alloc_method,
1389 b.basis_account_id,
1390 b.balance_type,
1391 b.bas_ytd_ptd,
1392 b.fixed_percent
1393 FROM gl_aloc_mst m, gl_aloc_bas b
1394 WHERE m.alloc_id = b.alloc_id
1395 AND m.legal_entity_id = g_legal_entity_id
1396 AND m.alloc_code BETWEEN NVL(v_from_alloc_code,m.alloc_code) AND nvl(v_to_alloc_code,m.alloc_code)
1397 AND m.delete_mark = 0
1398 AND b.delete_mark = 0
1399 AND (b.alloc_method = 0 OR
1400 (b.alloc_method = 1 AND 100 = ( SELECT sum(bb.fixed_percent)
1401 FROM gl_aloc_bas bb
1402 WHERE bb.alloc_id = b.alloc_id and
1403 bb.delete_mark = 0
1404 )
1405 )
1406 )
1407
1408 ORDER BY 1,2;
1409
1410
1411 BEGIN
1412 FOR cur_alloc_basis_tmp IN cur_alloc_basis LOOP
1413 IF (cur_alloc_basis_tmp.alloc_method = 0) THEN
1414
1415 alloc_log_msg(C_LOG_FILE, ' Allocation code: '||cur_alloc_basis_tmp.alloc_code);
1416 put_alloc_expenses(cur_alloc_basis_tmp.alloc_id,
1417 cur_alloc_basis_tmp.line_no,
1418 cur_alloc_basis_tmp.basis_account_id, /* INVCONV sschinch */
1419 cur_alloc_basis_tmp.basis_account_id,
1420 cur_alloc_basis_tmp.balance_type,
1421 cur_alloc_basis_tmp.bas_ytd_ptd,
1422 0);
1423 ELSIF(cur_alloc_basis_tmp.alloc_method = 1) THEN
1424 insert_alloc_inp(cur_alloc_basis_tmp.alloc_id,
1425 cur_alloc_basis_tmp.line_no,
1426 0,
1427 NULL,
1428 cur_alloc_basis_tmp.fixed_percent);
1429 END IF;
1430 END LOOP;
1431 END get_alloc_basis;
1432
1433 /***************************************************************
1434 * PROCEDURE
1435 * refresh_fixed
1436 *
1437 * DESCRIPTION
1438 * Refreshes the interface table with the fixed percent information
1439 * from allocation basis table if at all it got modified. This
1440 * procedure deletes all the fixed percent rows from interface table
1441 * and inserts into the interface table with new fixed percent rows
1442 * values from allocation basis table.
1443 *
1444 * AUTHOR
1445 * Sukarna Reddy 09/17/98
1446 *
1447 INPUT PARAMETERS
1448 * v_from_alloc_code
1449 * v_to_alloc_code
1450 *
1451 * OUTPUT PARAMETERS
1452 * <None>
1453 *
1454 * HISTORY
1455 * Ignore all the burden codes that have total fixed percentage
1456 * not equal to 100.
1457 ***************************************************************/
1458
1459 PROCEDURE refresh_fixed(v_from_alloc_code VARCHAR2,v_to_alloc_code VARCHAR2) IS
1460 CURSOR cur_alloc_fixed IS
1461 SELECT m.alloc_id,
1462 b.line_no,
1463 b.alloc_method,
1464 b.fixed_percent
1465 FROM gl_aloc_mst m, gl_aloc_bas b
1466 WHERE m.alloc_id = b.alloc_id
1467 AND m.legal_entity_id = g_legal_entity_id
1468 AND b.alloc_method = 1
1469 AND m.alloc_code BETWEEN NVL(v_from_alloc_code,m.alloc_code) AND nvl(v_to_alloc_code,m.alloc_code)
1470 AND m.delete_mark = 0
1471 AND b.delete_mark = 0
1472 AND 100 = ( SELECT sum(bb.fixed_percent)
1473 FROM gl_aloc_bas bb
1474 WHERE bb.alloc_id = b.alloc_id and
1475 bb.delete_mark = 0
1476 )
1477 ORDER BY 1,2;
1478 l_local_module VARCHAR2(80) := '.refresh_fixed';
1479 l_previous_module VARCHAR2(4000);
1480 BEGIN
1481 l_previous_module := g_calling_module;
1482 g_calling_module := g_calling_module||l_local_module;
1483 alloc_log_msg( C_LOG_FILE, 'Deleting rows from Interface table gl_aloc_inp ...' );
1484
1485 BEGIN
1486 DELETE
1487 FROM gl_aloc_inp a
1488 WHERE a.account_key_type = 0
1489 and a.calendar_code = g_calendar_code
1490 AND a.period_code = g_period_code
1491 and a.alloc_id in (
1492 select m.alloc_id
1493 from gl_aloc_mst m,gl_aloc_bas b
1494 where m.legal_entity_id = g_legal_entity_id
1495 and m.alloc_id = b.alloc_id
1496 and b.alloc_method = 1
1497 and m.alloc_code between nvl(v_from_alloc_code,m.alloc_code)
1498 and nvl(v_to_alloc_code,m.alloc_code)
1499 );
1500
1501 alloc_log_msg( C_LOG_FILE, TO_CHAR(SQL%ROWCOUNT) || ' Rows deleted from Interface table gl_aloc_inp' );
1502 EXCEPTION
1503 WHEN NO_DATA_FOUND THEN
1504 alloc_log_msg(C_LOG_FILE, '0 Rows deleted from gl_aloc_inp for fixed');
1505
1506 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1507 THEN
1508 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,g_calling_module,'0 Rows deleted from gl_aloc_inp for fixed');
1509 END IF;
1510 WHEN OTHERS THEN
1511 alloc_log_msg(C_LOG_FILE, to_char(SQLCODE) || ' ' || SQLERRM);
1512
1513 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1514 THEN
1515 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,g_calling_module,to_char(SQLCODE) || ' ' || SQLERRM);
1516 END IF;
1517 END ;
1518 g_calling_module := g_calling_module||l_local_module;
1519 FOR cur_aloc_fixed_tmp IN cur_alloc_fixed LOOP
1520 insert_alloc_inp(cur_aloc_fixed_tmp.alloc_id,
1521 cur_aloc_fixed_tmp.line_no,
1522 0,
1523 NULL,
1524 cur_aloc_fixed_tmp.fixed_percent
1525 );
1526
1527 END LOOP;
1528 g_calling_module := l_previous_module;
1529 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1530 THEN
1531 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,g_calling_module||l_local_module||'.end','Completed successfully');
1532 END IF;
1533
1534 END refresh_fixed;
1535
1536 /********************************************************
1537 * PROCEDURE
1538 * process_alloc_dtl
1539 *
1540 * DESCRIPTION
1541 * Retrieves all interface rows for a particular calendar
1542 * and period and calculates the allocated amount,
1543 * allocated percentage and populate allocation detail
1544 * table.
1545 * AUTHOR
1546 * Sukarna Reddy 09/18/98
1547 *
1548 * INPUT PARAMETERS
1549 * v_from_alloc_code
1550 * v_to_alloc_code
1551 *
1552 * OUTPUT PARAMETERS
1553 * <None>
1554 *
1555 * HISTORY
1556 *
1557 *
1558 ********************************************************/
1559
1560 PROCEDURE process_alloc_dtl(v_from_alloc_code VARCHAR2,
1561 v_to_alloc_code VARCHAR2) IS
1562
1563 x_status NUMBER; /*B7458002*/
1564
1565 CURSOR cur_glaloc_inp IS
1566 SELECT *
1567 FROM gl_aloc_inp i
1568 WHERE i.calendar_code = g_calendar_code
1569 AND i.period_code = g_period_code
1570 AND i.delete_mark = 0
1571 AND account_key_type = 0
1572 AND i.alloc_id IN (SELECT b.alloc_id
1573 FROM gl_aloc_mst m,gl_aloc_bas b
1574 where m.alloc_id = b.alloc_id
1575 AND m.legal_entity_id = g_legal_entity_id
1576 AND b.delete_mark = 0
1577 AND m.delete_mark = 0
1578 AND m.alloc_code BETWEEN nvl(v_from_alloc_code,m.alloc_code) and
1579 nvl(v_to_alloc_code ,m.alloc_code))
1580 ORDER BY alloc_id;
1581 CURSOR cur_alloc_code(v_alloc_id gl_aloc_mst.alloc_id%TYPE) IS
1582 SELECT alloc_code
1583 FROM gl_aloc_mst
1584 WHERE alloc_id = v_alloc_id;
1585
1586 x_prev_alloc_id gl_aloc_mst.alloc_id%TYPE DEFAULT -99;
1587 x_prev_basis_amount gl_aloc_inp.amount%TYPE DEFAULT -99;
1588 x_expense_amount gl_aloc_inp.amount%TYPE;
1589 x_alloc_percent NUMBER;
1590 x_allocated_amount gl_aloc_inp.amount%type;
1591 x_alloc_code gl_aloc_mst.alloc_code%TYPE;
1592 l_local_module VARCHAR2(80) := '.process_aloc_dtl';
1593 BEGIN
1594 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1595 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,g_calling_module||l_local_module||'.begin',' Starting..');
1596 END IF;
1597
1598 FOR cur_alocinp_tmp IN cur_glaloc_inp LOOP
1599 IF (X_prev_alloc_id <> cur_alocinp_tmp.alloc_id) THEN
1600 OPEN cur_alloc_code (cur_alocinp_tmp.alloc_id);
1601 FETCH cur_alloc_code INTO X_alloc_code;
1602 CLOSE cur_alloc_code;
1603
1604 alloc_log_msg(C_LOG_FILE, ' Allocation Code: ' || x_alloc_code);
1605
1606 /* Select total basis amount for calculating percentage.*/
1607 SELECT sum(amount) INTO X_prev_basis_amount
1608 FROM gl_aloc_inp
1609 WHERE alloc_id = cur_alocinp_tmp.alloc_id
1610 AND calendar_code = g_calendar_code
1611 AND period_code = g_period_code
1612 AND account_key_type = 0;
1613 alloc_log_msg(C_LOG_FILE, ' Total Basis amount '||to_char(x_prev_basis_amount));
1614
1615 /* Select total expense amount for allocation.*/
1616 SELECT sum(amount)
1617 INTO x_expense_amount
1618 FROM gl_aloc_inp
1619 WHERE alloc_id = cur_alocinp_tmp.alloc_id
1620 AND calendar_code = g_calendar_code
1621 AND period_code = g_period_code
1622 AND account_key_type = 1;
1623 alloc_log_msg(C_LOG_FILE, ' Total Expense amount '||to_char(X_expense_amount));
1624 x_prev_alloc_id := cur_alocinp_tmp.alloc_id;
1625 END IF;
1626 IF (X_prev_basis_amount > 0) THEN
1627 /* If the basis row is for fixed percentage.*/
1628 IF cur_alocinp_tmp.account_id IS NULL THEN
1629 x_alloc_percent := cur_alocinp_tmp.amount/100;
1630 ELSE
1631 x_alloc_percent := (cur_alocinp_tmp.amount/X_prev_basis_amount);
1632 END IF;
1633 x_allocated_amount := x_expense_amount*x_alloc_percent;
1634 x_alloc_percent := x_alloc_percent * 100;
1635
1636 alloc_log_msg(C_LOG_FILE, ' Line: '||to_char (cur_alocinp_tmp.line_no) || ', % = '||to_char(round(x_alloc_percent,4)) ||
1637 ', Allocated Expense = ' ||to_char(round(x_allocated_amount,4)));
1638
1639 /* Bug 7458002 - Commented
1640 INSERT INTO gl_aloc_dtl
1641 (
1642 PERIOD_ID,
1643 COST_TYPE_ID,
1644 ALLOC_ID,
1645 LINE_NO,
1646 ALLOCDTL_ID,
1647 PERCENT_ALLOCATION,
1648 ALLOCATED_EXPENSE_AMT,
1649 AC_STATUS,
1650 CREATION_DATE,
1651 CREATED_BY,
1652 LAST_UPDATE_LOGIN,
1653 LAST_UPDATE_DATE,
1654 LAST_UPDATED_BY,
1655 TRANS_CNT,
1656 DELETE_MARK,
1657 TEXT_CODE,
1658 REQUEST_ID,
1659 PROGRAM_APPLICATION_ID,
1660 PROGRAM_ID,
1661 PROGRAM_UPDATE_DATE
1662 )
1663 VALUES
1664 (
1665 g_period_id,
1666 g_cost_type_id,
1667 cur_alocinp_tmp.alloc_id,
1668 cur_alocinp_tmp.line_no,
1669 gem5_allocdtl_id_s.nextval,
1670 NVL(x_alloc_percent,0),
1671 NVL(x_allocated_amount,0),
1672 0,
1673 SYSDATE,
1674 P_created_by,
1675 P_login_id,
1676 SYSDATE,
1677 P_created_by,
1678 0,
1679 0,
1680 NULL,
1681 P_request_id,
1682 P_prog_application_id,
1683 P_program_id,
1684 SYSDATE
1685 ); */
1686 /* Bug 7458002 - replaced Insert with Merge */
1687 MERGE INTO gl_aloc_dtl gdtl
1688 USING ( SELECT g_period_id period_id,
1689 g_cost_type_id cost_type_id,
1690 cur_alocinp_tmp.alloc_id alloc_id,
1691 cur_alocinp_tmp.line_no line_no
1692 FROM dual
1693 ) ginp
1694 ON ( gdtl.period_id = ginp.period_id
1695 AND gdtl.cost_type_id = ginp.cost_type_id
1696 AND gdtl.alloc_id = ginp.alloc_id
1697 AND gdtl.line_no = ginp.line_no
1698 )
1699 WHEN MATCHED THEN
1700 UPDATE SET
1701 gdtl.PERCENT_ALLOCATION = NVL(x_alloc_percent,0)
1702 , gdtl.ALLOCATED_EXPENSE_AMT = NVL(x_allocated_amount,0)
1703 , gdtl.AC_STATUS = 0
1704 , gdtl.LAST_UPDATE_LOGIN = P_login_id
1705 , gdtl.LAST_UPDATE_DATE = SYSDATE
1706 , gdtl.LAST_UPDATED_BY = P_created_by
1707 , gdtl.TRANS_CNT = 0
1708 , gdtl.DELETE_MARK = 0
1709 , gdtl.TEXT_CODE = NULL
1710 , gdtl.REQUEST_ID = P_request_id
1711 , gdtl.PROGRAM_APPLICATION_ID = P_prog_application_id
1712 , gdtl.PROGRAM_ID = P_program_id
1713 , gdtl.PROGRAM_UPDATE_DATE = SYSDATE
1714 WHEN NOT MATCHED THEN
1715 INSERT
1716 (
1717 PERIOD_ID,
1718 COST_TYPE_ID,
1719 ALLOC_ID,
1720 LINE_NO,
1721 ALLOCDTL_ID,
1722 PERCENT_ALLOCATION,
1723 ALLOCATED_EXPENSE_AMT,
1724 AC_STATUS,
1725 CREATION_DATE,
1726 CREATED_BY,
1727 LAST_UPDATE_LOGIN,
1728 LAST_UPDATE_DATE,
1729 LAST_UPDATED_BY,
1730 TRANS_CNT,
1731 DELETE_MARK,
1732 TEXT_CODE,
1733 REQUEST_ID,
1734 PROGRAM_APPLICATION_ID,
1735 PROGRAM_ID,
1736 PROGRAM_UPDATE_DATE
1737 )
1738 VALUES
1739 (
1740 g_period_id,
1741 g_cost_type_id,
1742 cur_alocinp_tmp.alloc_id,
1743 cur_alocinp_tmp.line_no,
1744 gem5_allocdtl_id_s.nextval,
1745 NVL(x_alloc_percent,0),
1746 NVL(x_allocated_amount,0),
1747 0,
1748 SYSDATE,
1749 P_created_by,
1750 P_login_id,
1751 SYSDATE,
1752 P_created_by,
1753 0,
1754 0,
1755 NULL,
1756 P_request_id,
1757 P_prog_application_id,
1758 P_program_id,
1759 SYSDATE
1760 )
1761 ;
1762 END IF;
1763 END LOOP;
1764
1765 /* Bug 7458002 - To delete obsoleted rows in gl_aloc_dtl (Start) */
1766 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1767 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,g_calling_module||l_local_module||'.delete_allocations','deleting Allocations ...');
1768 END IF;
1769 delete_allocations(v_from_alloc_code, v_to_alloc_code, x_status);
1770
1771 IF (x_status < 0)
1772 THEN
1773 return;
1774 END IF;
1775 /* Bug 7458002 - End */
1776
1777 EXCEPTION
1778 WHEN OTHERS THEN
1779 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1780 THEN
1781 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,g_calling_module||l_local_module,to_char(SQLCODE)||' '||SQLERRM);
1782 END IF;
1783 END process_alloc_dtl;
1784
1785
1786
1787
1788
1789 /*********************************************************************
1790 * PROCEDURE
1791 * alloc_log_msg
1792 * DESCRIPTION
1793 * Writes messages to FND_FILE.LOG or FND_FILE.OUTPUT
1794 *
1795 * INPUT PARAMETERS
1796 * pi_file : indicates LOG(=1) or OUTPUT(=2) file
1797 * pi_msg : message to be printed
1798 *
1799 * OUTPUT PARAMETERS
1800 * <None>
1801 *
1802 * HISTORY
1803 * 11-Nov-99 Rajesh Seshadri
1804 *
1805 *********************************************************************/
1806
1807 PROCEDURE alloc_log_msg(
1808 pi_file IN NUMBER,
1809 pi_msg IN VARCHAR2
1810 )
1811 IS
1812 l_dt VARCHAR2(64);
1813 BEGIN
1814
1815 l_dt := TO_CHAR( SYSDATE, 'YYYY-MM-DD HH24:MI:SS' );
1816 IF( pi_file = C_OUT_FILE)
1817 THEN
1818 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, pi_msg || ' ' || l_dt );
1819 ELSE
1820 FND_FILE.PUT_LINE( FND_FILE.LOG, pi_msg || ' ' || l_dt );
1821 END IF;
1822 END alloc_log_msg;
1823 END GMF_ALLOC_PROC;