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