1 PACKAGE BODY AP_UTILITIES_PKG AS
2 /* $Header: aputilsb.pls 120.30.12010000.3 2009/02/26 11:24:55 rseeta ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_UTILITIES_PKG';
5 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
6 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
7 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
8 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
9 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
10 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
11 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
12
13 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
14 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
15 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
16 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
17 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
18 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
19 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
20 G_MODULE_NAME CONSTANT VARCHAR2(50) :='AP.PLSQL.AP_UTILITIES_PKG';
21
22 function AP_Get_Displayed_Field
23 (LookupType IN varchar2
24 ,LookupCode IN varchar2)
25 return varchar2
26 is
27 --
28 cursor c_lookup is
29 select displayed_field
30 from ap_lookup_codes
31 where (lookup_code = LookupCode)
32 and (lookup_type = LookupType);
33 output_string ap_lookup_codes.displayed_field%TYPE;
34 --
35 BEGIN
36 --
37 open c_lookup;
38 fetch c_lookup into output_string;
39 --
40 IF c_lookup%NOTFOUND THEN
41 raise NO_DATA_FOUND;
42 END IF;
43 --
44 close c_lookup;
45 return(output_string);
46 --
47 END AP_Get_Displayed_Field;
48 --
49 --
50 function Ap_Round_Currency
51 (P_Amount IN number
52 ,P_Currency_Code IN varchar2)
53 return number is
54 l_rounded_amount number;
55 l_minimum_acct_unit FND_CURRENCIES.minimum_accountable_unit%TYPE;
56 l_precision FND_CURRENCIES.precision%TYPE;
57
58 begin
59
60 /* Bug 5572876. Ccaching is done for Currency Data */
61 If g_fnd_currency_code_t.COUNT > 0 Then
62
63 If g_fnd_currency_code_t.Exists(p_currency_code) Then
64
65 l_minimum_acct_unit := g_fnd_currency_code_t(p_currency_code).minimum_accountable_unit;
66 l_precision := g_fnd_currency_code_t(p_currency_code).precision;
67
68 Else
69
70 Begin
71 select FC.minimum_accountable_unit, FC.precision
72 into l_minimum_acct_unit, l_precision
73 from fnd_currencies FC
74 where FC.currency_code = P_Currency_Code;
75
76 g_fnd_currency_code_t(p_currency_code).minimum_accountable_unit := l_minimum_acct_unit;
77 g_fnd_currency_code_t(p_currency_code).precision := l_precision;
78 g_fnd_currency_code_t(p_currency_code).currency_code := p_currency_code;
79 Exception
80 When No_Data_Found Then
81 /* Bug 5722538. If p_currency_code is null then assignments
82 to plsql table based on null index fails
83 hence commenting out the statements and assigining null to l_minimum_acct_unit
84 and l_precision. */
85 l_minimum_acct_unit := NULL;
86 l_precision := NULL;
87 End;
88
89 End If;
90
91 Else
92
93 Begin
94 select FC.minimum_accountable_unit, FC.precision
95 into l_minimum_acct_unit, l_precision
96 from fnd_currencies FC
97 where FC.currency_code = P_Currency_Code;
98
99 g_fnd_currency_code_t(p_currency_code).minimum_accountable_unit := l_minimum_acct_unit;
100 g_fnd_currency_code_t(p_currency_code).precision := l_precision;
101 g_fnd_currency_code_t(p_currency_code).currency_code := p_currency_code;
102
103 Exception
104 When No_Data_Found Then
105 /* Bug 5722538. If p_currency_code is null then assignments
106 to plsql table based on null index fails
107 hence commenting out the statements and assigining null to l_minimum_acct_unit
108 and l_precision. */
109 l_minimum_acct_unit := NULL;
110 l_precision := NULL;
111 End;
112
113 End If;
114
115
116 If l_minimum_acct_unit Is Null Then
117 /* Bug 5722538. L_precion can be also be null if the p_currency_code
118 is null */
119 If l_precision Is Not Null Then
120 l_rounded_amount := round(P_Amount, l_precision);
121 Else
122 l_rounded_amount := NULL;
123 End If;
124 Else
125 l_rounded_amount := round(P_amount/l_minimum_acct_unit)*l_minimum_acct_unit;
126 End If;
127
128 --
129 return(l_rounded_amount);
130 --
131 EXCEPTION
132
133 WHEN NO_DATA_FOUND THEN
134
135 /* Note: this segment of code affects the purity of the function
136 (ie with it, we cannot guarantee that package/dbms state
137 will not be altered). Such guarantees are necessary in
138 order to use a stored function in the select-list of a
139 query. Therefore, I am commenting it out NOCOPY and simply
140 returning null if no record is retrieved.
141 --
142 raise_application_error(-20000,'APUT002/No such currency ' ||
143 P_Currency_Code);
144 */
145
146 return (null);
147 --
148 end AP_ROUND_CURRENCY;
149 --
150
151 --===========================================================================
152 -- AP_Round_Tax: Function that rounds a tax amount.
153 -- This function was created as part of the development of
154 -- Consumption tax. It calculates rounding based on a
155 -- rounding rule passed on to the function.
156 -- Parameters:
157 -- P_Amount: Amount to be rounded
158 -- P_Currency_Code: Currency Code for the document tax is on.
159 -- P_Round_Rule: Rounding rule to follow (U for Up, D for Down,
160 -- N for Nearest)
161 -- P_Calling_Sequence: Debugging string to indicate the path
162 -- of module calls to be printed out NOCOPY upon
163 -- error.
164 -- Returns: Rounded Amount
165 --===========================================================================
166 function Ap_Round_Tax
167 (P_Amount IN number
168 ,P_Currency_Code IN varchar2
169 ,P_Round_Rule IN varchar2
170 ,P_Calling_Sequence IN varchar2)
171 return number is
172 l_func_currency varchar2(15);
173 l_fc_precision number; -- precision from currency
174 l_fc_min_acct_unit number; -- mac from currency
175 l_precision number; -- precision to be used
176 l_min_acct_unit number; -- mac to be used
177 l_rounded_amount number;
178 l_debug_loc varchar2(30) := 'Ap_Round_Tax';
179 l_curr_calling_sequence varchar2(2000);
180 l_debug_info varchar2(100);
181 begin
182
183 -------------------------- DEBUG INFORMATION ------------------------------
184 --AP_LOGGING_PKG.AP_Begin_Block(l_debug_loc);
185
186 l_curr_calling_sequence := 'AP_UTILITIES_PKG.'||l_debug_loc||'<-'||p_calling_sequence;
187
188 l_debug_info := 'Retrieve the precision, mac and functional currency';
189 --AP_LOGGING_PKG.AP_Log(l_debug_info, l_debug_loc);
190 ---------------------------------------------------------------------------
191 -- eTax Uptake
192 -- This select has been modified to obsolete FIN.minimum_accountable_unit
193 -- and FC.precision from ap.financials_system_params_all table.
194 -- All tax setup has been moved to eTax.
195 -- This function may be obsolete totally later on.
196
197 SELECT SP.base_currency_code,
198 FC.precision,
199 FC.minimum_accountable_unit
200 INTO l_func_currency,
201 l_fc_precision,
202 l_fc_min_acct_unit
203 FROM ap_system_parameters SP,
204 fnd_currencies FC
205 WHERE FC.currency_code = P_Currency_Code;
206
207
208 -------------------------- DEBUG INFORMATION ------------------------------
209 l_debug_info := 'Calculate Rounded Amount';
210 --AP_LOGGING_PKG.AP_Log(l_debug_info, l_debug_loc);
211 ---------------------------------------------------------------------------
212 --
213 -- If the invoice is in the functional currency then we need
214 -- to evaluate which precision/mac to use i.e. the financials options
215 -- one or the currency one.
216 -- Else, if the invoice is in a foreign currency we always use
217 -- the financials options precision and mac.
218 -- NOTE: This use of precision/mac is only valid for tax calculation
219 --
220 IF (P_Currency_Code = l_func_currency) THEN
221 --
222 -- When calculating tax in functional currency it is common to want
223 -- to use the minimum precision available. In our case that translates
224 -- to taking the least between the financials options precision and the
225 -- currency precision together with taking the greatest between the
226 -- financials options mac and the currency mac.
227 -- Do not use precision or mac defined in financials_system_parameters
228 -- eTax Uptake
229
230 l_precision := l_fc_precision;
231 l_min_acct_unit := l_fc_min_acct_unit;
232
233 ELSE
234 l_precision := l_fc_precision;
235 l_min_acct_unit := l_fc_min_acct_unit;
236 END IF;
237
238 --
239 -- Do actual rounding calculation
240 --
241 IF (l_min_acct_unit is null) THEN
242 IF (nvl(P_Round_Rule, 'N') = 'D') THEN
243 l_rounded_amount := TRUNC(P_Amount, l_precision);
244 ELSIF (nvl(P_Round_Rule, 'N') = 'U') THEN
245 IF (P_Amount = TRUNC(P_Amount, l_precision)) THEN
246 l_rounded_amount := P_Amount;
247 ELSE
248 l_rounded_amount := ROUND(P_Amount+(SIGN(P_Amount) *
249 (POWER(10,(l_precision*(-1)))/2)),
250 l_precision);
251 END IF;
252 ELSE /* Round Nearest by default */
253 l_rounded_amount := ROUND(P_Amount, l_precision);
254 END IF;
255 ELSE
256 IF (nvl(P_Round_Rule, 'N') = 'D') THEN
257 l_rounded_amount := SIGN(P_Amount)*(FLOOR(ABS(P_Amount)/l_min_acct_unit)
258 * l_min_acct_unit);
259 ELSIF (nvl(P_Round_Rule, 'N') = 'U') THEN
260 l_rounded_amount := SIGN(P_Amount)*(CEIL(ABS(P_Amount)/l_min_acct_unit)
261 * l_min_acct_unit);
262
263 ELSE
264 l_rounded_amount := ROUND(P_Amount/l_min_acct_unit)*l_min_acct_unit;
265 END IF;
266 END IF;
267
268 ---------------------------- DEBUG INFORMATION ----------------------------
269 --AP_LOGGING_PKG.AP_End_Block(l_debug_loc);
270 ---------------------------------------------------------------------------
271 return(l_rounded_amount);
272
273 EXCEPTION
274 WHEN OTHERS THEN
275 --AP_LOGGING_PKG.AP_End_Block(l_debug_loc);
276 IF (SQLCODE <> -20001) THEN
277 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
278 FND_MESSAGE.SET_TOKEN('ERROR', 'SQLERRM');
279 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
280 FND_MESSAGE.SET_TOKEN('PARAMETERS',
281 'Amount to Round = '||to_char(p_amount)
282 ||', Currency Code = '||p_currency_code
283 ||', Rounding Rule = '||p_round_rule);
284 END IF;
285 APP_EXCEPTION.RAISE_EXCEPTION;
286 --
287 end AP_ROUND_TAX;
288
289
290 function Ap_Round_Non_Rec_Tax
291 (P_Amount IN number
292 ,P_Currency_Code IN varchar2
293 ,P_Round_Rule IN varchar2
294 ,P_Calling_Sequence IN varchar2)
295 return number is
296 l_fc_precision number; -- precision from currency
297 l_fc_min_acct_unit number; -- mac from currency
298 l_precision number; -- precision to be used
299 l_min_acct_unit number; -- mac to be used
300 l_rounded_amount number;
301 l_debug_loc varchar2(30) := 'Ap_Round_Non_Rec_Tax';
302 l_curr_calling_sequence varchar2(2000);
303 l_debug_info varchar2(100);
304 begin
305 -------------------------- DEBUG INFORMATION -------------------------
306 -----
307 --AP_LOGGING_PKG.AP_Begin_Block(l_debug_loc);
308
309 l_curr_calling_sequence := 'AP_UTILITIES_PKG.'||l_debug_loc||'<-'||p_calling_sequence;
310
311 -------------------------- DEBUG INFORMATION ------------------------
312 ------
313 l_debug_info := 'Calculate Rounded Amount';
314 --AP_LOGGING_PKG.AP_Log(l_debug_info, l_debug_loc);
315 ---------------------------------------------------------------------
316 ------
317 SELECT FC.precision,
318 FC.minimum_accountable_unit
319 INTO l_fc_precision,
320 l_fc_min_acct_unit
321 FROM fnd_currencies FC
322 WHERE FC.currency_code = P_Currency_Code;
323
324 l_precision := l_fc_precision;
325 l_min_acct_unit := l_fc_min_acct_unit;
326 IF (l_min_acct_unit is null) THEN
327 IF (nvl(P_Round_Rule, 'N') = 'D') THEN
328 l_rounded_amount := TRUNC(P_Amount, l_precision);
329 ELSIF (nvl(P_Round_Rule, 'N') = 'U') THEN
330 IF (P_Amount = TRUNC(P_Amount, l_precision)) THEN
331 l_rounded_amount := P_Amount;
332 ELSE
333 l_rounded_amount := ROUND(P_Amount+(SIGN(P_Amount) *
334 (POWER(10,(l_precision*(-1)
335 ))/2)),
336 l_precision);
337 END IF;
338 ELSE /* Round Nearest by default */
339 l_rounded_amount := ROUND(P_Amount, l_precision);
340 END IF;
341 ELSE
342 IF (nvl(P_Round_Rule, 'N') = 'D') THEN
343 l_rounded_amount := SIGN(P_Amount)*(FLOOR(ABS(P_Amount)/l_min_acct_unit)
344 * l_min_acct_unit);
345 ELSIF (nvl(P_Round_Rule, 'N') = 'U') THEN
346 l_rounded_amount := SIGN(P_Amount)*(CEIL(ABS(P_Amount)/l_min_acct_unit)
347 * l_min_acct_unit);
348 ELSE
349 l_rounded_amount := ROUND(P_Amount/l_min_acct_unit)*l_min_acct_unit;
350 END IF;
351 END IF;
352 return(l_rounded_amount);
353
354 EXCEPTION
355 WHEN OTHERS THEN
356 --AP_LOGGING_PKG.AP_End_Block(l_debug_loc);
357 IF (SQLCODE <> -20001) THEN
358 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
359 FND_MESSAGE.SET_TOKEN('ERROR', 'SQLERRM');
360 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence
361 );
362 FND_MESSAGE.SET_TOKEN('PARAMETERS',
363 'Amount to Round = '||to_char(p_amount)
364 ||', Currency Code = '||p_currency_code
365 ||', Rounding Rule = '||p_round_rule);
366 END IF;
367 APP_EXCEPTION.RAISE_EXCEPTION;
368 end AP_Round_Non_Rec_Tax;
369
370 function Ap_Round_Precision
371 (P_Amount IN number
372 ,P_Min_unit IN number
373 ,P_Precision IN number
374 ) return number
375 is
376 l_rounded_amount number;
377 begin
378 --
379 select decode(P_Min_unit,
380 null, round(P_Amount, P_Precision),
381 round(P_Amount/P_Min_unit) * P_Min_unit)
382 into l_rounded_amount
383 from sys.dual;
384 --
385 return(l_rounded_amount);
386 --
387 end AP_Round_Precision;
388
389 -----------------------------------------------------------------------
390 -- function get_current_gl_date() takes argument P_Date and
391 -- returns the open period in which P_Date falls. If P_Date
392 -- does not fall within an open period, the function will return null
393 -----------------------------------------------------------------------
394 -- Bug 2106121. This function is returning null Period when the
395 -- p_date is the last day of the month with a time stamp. Added trunc
396 -- function to return the correct period.
397
398 function get_current_gl_date (P_Date IN date,
399 P_Org_ID IN number default
400 mo_global.get_current_org_id) return varchar2
401 is
402 cursor l_current_cursor is
403 SELECT period_name
404 FROM gl_period_statuses GLPS,
405 ap_system_parameters_all SP
406 WHERE application_id = 200
407 AND sp.org_id = P_Org_Id
408 AND GLPS.set_of_books_id = SP.set_of_books_id
409 AND trunc(P_Date) BETWEEN start_date AND end_date
410 AND closing_status in ('O', 'F')
411 AND NVL(adjustment_period_flag, 'N') = 'N';
412
413 l_period_name gl_period_statuses.period_name%TYPE := '';
414 l_acct_date_org Varchar2(30);
415 l_api_name CONSTANT VARCHAR2(200) := 'Get_Current_Gl_Date';
416 l_debug_info Varchar2(2000);
417
418 begin
419
420 l_debug_info := 'Begining of Function';
421 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
422 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
423 END IF;
424
425 l_acct_date_org := To_Char(P_Date, 'DD-MON-YYYY')||'-'||To_Char(P_Org_Id);
426
427 l_debug_info := 'Index Value: '||l_acct_date_org;
428 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
429 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
430 END IF;
431
432
433 If g_curr_period_name_t.Count > 0 Then
434
435 If g_curr_period_name_t.exists(l_acct_date_org) Then
436
437 l_period_name := g_curr_period_name_t(l_acct_date_org).period_name;
438
439 l_debug_info := 'Period Name from existing plsql table for index found: '||l_period_name;
440 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
441 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
442 END IF;
443
444 Else
445
446 open l_current_cursor;
447 fetch l_current_cursor into l_period_name;
448 close l_current_cursor;
449
450 g_curr_period_name_t(l_acct_date_org).period_name := l_period_name;
451
452 l_debug_info :='Period Name from existing plsql table for index not found: '||l_period_name;
453 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
454 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
455 END IF;
456
457
458 End If;
459
460 Else
461
462 open l_current_cursor;
463 fetch l_current_cursor into l_period_name;
464 close l_current_cursor;
465
466 g_curr_period_name_t(l_acct_date_org).period_name := l_period_name;
467
468 l_debug_info := 'Period Name not there in plsql table : '||l_period_name;
469 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
470 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
471 END IF;
472
473 End If;
474
475 return (l_period_name);
476
477 end get_current_gl_date;
478
479 --Bug1715368 The function get_gl_period_name is added to get the
480 --period name of the date passed irrespective of the period status.
481 -----------------------------------------------------------------------
482 -- function get_gl_period_name() takes argument P_Date and
483 -- returns the period name of the P_Date.
484 -----------------------------------------------------------------------
485 function get_gl_period_name (P_Date IN date,
486 P_Org_ID IN NUMBER DEFAULT
487 mo_global.get_current_org_id) return varchar2
488 is
489 cursor l_current_cursor is
490 SELECT period_name
491 FROM gl_period_statuses GLPS,
492 ap_system_parameters_all SP
493 WHERE application_id = 200
494 AND sp.org_id = p_org_id
495 AND GLPS.set_of_books_id = SP.set_of_books_id
496 /* Bug 5368685 */
497 AND trunc(P_Date) BETWEEN start_date AND end_date
498 AND NVL(adjustment_period_flag, 'N') = 'N';
499
500 l_period_name gl_period_statuses.period_name%TYPE := '';
501
502 begin
503
504 open l_current_cursor;
505 fetch l_current_cursor into l_period_name;
506 close l_current_cursor;
507
508
509 return (l_period_name);
510
511 end get_gl_period_name;
512
513 -----------------------------------------------------------------------
514 -- function get_open_gl_date() takes argument P_Date and
515 -- returns the name and start GL date of the open/future period that falls on
516 -- or after P_Date. The GL date and period name are written to
517 -- IN OUT NOCOPY parameters, P_GL_Date and P_Period_Name, passed to the
518 -- procedure. If there is no open period, the procedure returns
519 -- null in the IN OUT NOCOPY parameters.
520 -----------------------------------------------------------------------
521 -- Bug 5572876. Changes related to caching is done
522 procedure get_open_gl_date
523 (P_Date IN date
524 ,P_Period_Name OUT NOCOPY varchar2
525 ,P_GL_Date OUT NOCOPY date
526 ,P_Org_Id IN number DEFAULT
527 mo_global.get_current_org_id)
528 is
529 cursor l_open_cursor is
530 SELECT MIN(start_date),
531 period_name
532 FROM gl_period_statuses GLPS,
533 ap_system_parameters_all SP
534 WHERE application_id = 200
535 AND sp.org_id = P_Org_Id
536 AND GLPS.set_of_books_id = SP.set_of_books_id
537 AND end_date >= P_Date --Bug6809792
538 AND closing_status in ('O', 'F')
539 AND NVL(adjustment_period_flag, 'N') = 'N'
540 GROUP BY period_name
541 ORDER BY MIN(start_date);
542
543 l_start_date date := '';
544 l_period_name gl_period_statuses.period_name%TYPE := '';
545 l_acct_date_org Varchar2(30);
546 l_api_name CONSTANT VARCHAR2(200) := 'Get_Open_Gl_Date';
547 l_debug_info Varchar2(2000);
548
549
550 begin
551
552 l_debug_info := 'Begining of Function';
553 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
554 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
555 END IF;
556
557 l_acct_date_org := To_Char(P_Date, 'DD-MON-YYYY')||'-'||To_Char(P_Org_Id);
558
559 l_debug_info := 'Index Value: '||l_acct_date_org;
560 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
561 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
562 END IF;
563
564
565 If g_open_period_name_t.Count > 0 Then
566
567 If g_open_period_name_t.exists(l_acct_date_org) Then
568
569 l_period_name := g_open_period_name_t(l_acct_date_org).period_name;
570 l_start_date := g_open_period_name_t(l_acct_date_org).start_date;
571
572 l_debug_info := 'Period Name from existing plsql table for index found: '||l_period_name;
573 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
574 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
575 END IF;
576
577 Else
578
579 open l_open_cursor;
580 fetch l_open_cursor into l_start_date, l_period_name;
581 close l_open_cursor;
582
583 l_debug_info:='Period Name from existing plsql table for index not found: '||l_period_name;
584 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
585 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
586 END IF;
587
588
589 g_open_period_name_t(l_acct_date_org).period_name := l_period_name;
590 g_open_period_name_t(l_acct_date_org).start_date := l_start_date;
591
592 End If;
593
594 Else
595
596 open l_open_cursor;
597 fetch l_open_cursor into l_start_date, l_period_name;
598 close l_open_cursor;
599
600 g_open_period_name_t(l_acct_date_org).period_name := l_period_name;
601 g_open_period_name_t(l_acct_date_org).start_date := l_start_date;
602
603 l_debug_info := 'Period Name not there in plsql table: '||l_period_name;
604 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
605 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
606 END IF;
607
608 End If;
609
610 P_Period_Name := l_period_name;
611 P_GL_Date := l_start_date;
612
613 end get_open_gl_date;
614
615 -----------------------------------------------------------------------
616 -- function get_only_open_gl_date() takes argument P_Date and
617 -- returns the name and start GL date of the open period that falls on
618 -- or after P_Date. The GL date and period name are written to
619 -- IN OUT NOCOPY parameters, P_GL_Date and P_Period_Name, passed to the
620 -- procedure. If there is no open period, the procedure returns
621 -- null in the IN OUT NOCOPY parameters.
622 -- Bug 5572876. Changes related to caching is done
623 -----------------------------------------------------------------------
624 procedure get_only_open_gl_date
625 (P_Date IN date
626 ,P_Period_Name OUT NOCOPY varchar2
627 ,P_GL_Date OUT NOCOPY date
628 ,P_Org_Id IN number DEFAULT
629 mo_global.get_current_org_id)
630 is
631 cursor l_open_cursor is
632 SELECT MIN(start_date),
633 period_name
634 FROM gl_period_statuses GLPS,
635 ap_system_parameters_all SP --8281653
636 WHERE application_id = 200
637 AND SP.org_id = P_Org_Id
638 AND GLPS.set_of_books_id = SP.set_of_books_id
639 AND (start_date > P_Date OR
640 P_Date BETWEEN start_date AND end_date)
641 AND closing_status = 'O'
642 AND NVL(adjustment_period_flag, 'N') = 'N'
643 GROUP BY period_name
644 ORDER BY MIN(start_date);
645
646 l_start_date date := '';
647 l_period_name gl_period_statuses.period_name%TYPE := '';
648
649 begin
650
651 open l_open_cursor;
652 fetch l_open_cursor into l_start_date, l_period_name;
653 close l_open_cursor;
654
655 P_Period_Name := l_period_name;
656 P_GL_Date := l_start_date;
657
658 end get_only_open_gl_date;
659
660 -----------------------------------------------------------------------
661 -- Function get_exchange_rate() takes arguments exchange_rate_type,
662 -- exchange_date, and currency_code and returns the exchange_rate.
663 -- If no rate can be determined, the function will return null
664 -----------------------------------------------------------------------
665 function get_exchange_rate(
666 p_from_currency_code varchar2,
667 p_to_currency_code varchar2,
668 p_exchange_rate_type varchar2,
669 p_exchange_date date,
670 p_calling_sequence in varchar2) return number is
671
672 l_rate number := '';
673 current_calling_sequence VARCHAR2(2000);
674 debug_info VARCHAR2(100);
675
676 begin
677 -- Update the calling sequence
678 --
679 current_calling_sequence :=
680 'AP_UTILITIES_PKG.get_exchange_rate<-'||P_Calling_Sequence;
681
682 debug_info := 'Calling GL API to get the rate';
683 l_rate := gl_currency_api.get_rate(p_from_currency_code, p_to_currency_code,
684 p_exchange_date, p_exchange_rate_type);
685
686 return(l_rate);
687
688 EXCEPTION
689 WHEN gl_currency_api.NO_RATE THEN
690 return(l_rate);
691 WHEN OTHERS THEN
692 /* Note: this segment of code affects the purity of the function
693 (ie with it, we cannot guarantee that package/dbms state
694 will not be altered). Such guarantees are necessary in
695 order to use a stored function in the select-list of a
696 query. Therefore, I am commenting it out NOCOPY and simply
697 returning null if no record is retrieved.
698
699 if (SQLCODE <> -20001) then
700 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
701 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
702 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
703 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'p_from_currency_code = '||p_from_currency_code
704 ||', p_to_currency_code = '||p_to_currency_code
705 ||', p_exchange_rate_type = ' ||p_exchange_rate_type
706 ||', p_exchange_date = ' ||TO_CHAR(p_exchange_date));
707 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
708 end if;
709 APP_EXCEPTION.RAISE_EXCEPTION;
710 */
711 return(l_rate);
712 end get_exchange_rate;
713
714 --------------------------------------------------------------------
715 -- Procedure that will set the given profile option to the
716 -- given value on the server
717 ---------------------------------------------------------------------
718
719 PROCEDURE Set_Profile(p_profile_option IN vARCHAR2,
720 p_profile_value IN VARCHAR2) IS
721 BEGIN
722
723 FND_PROFILE.PUT(p_profile_option, p_profile_value);
724
725 END Set_Profile;
726
727 --------------------------------------------------------------------
728 -- Procedure that will get the AP_DEBUG message from the message
729 -- stack into a text buffer
730 ---------------------------------------------------------------------
731
732 PROCEDURE AP_Get_Message(p_err_txt OUT NOCOPY VARCHAR2) IS
733 BEGIN
734
735 p_err_txt := fnd_message.get;
736
737 END AP_Get_Message;
738
739 --MO Access Control: Added the following new function for the
740 --multi org access control project.
741 --This function returns the current responsibility name the user is in.
742 FUNCTION Get_Window_Title RETURN VARCHAR2 IS
743
744 l_application_id fnd_responsibility_vl.application_id%type;
745 l_resp_id fnd_responsibility_vl.responsibility_id%TYPE;
746 l_wnd_context fnd_responsibility_vl.responsibility_name%TYPE;
747
748 BEGIN
749
750 fnd_profile.get('RESP_ID',l_resp_id);
751 fnd_profile.get('RESP_APPL_ID',l_application_id);
752
753
754 SELECT responsibility_name
755 INTO l_wnd_context
756 FROM fnd_responsibility_vl
757 WHERE application_id = l_application_id
758 AND responsibility_id = l_resp_id;
759
760
761 return(l_wnd_context);
762
763
764 END Get_Window_Title;
765
766
767 FUNCTION Get_Window_Session_Title RETURN VARCHAR2 IS
768 l_multi_org VARCHAR2(1);
769 l_multi_cur VARCHAR2(1);
770 l_wnd_context VARCHAR2(60);
771 l_id VARCHAR2(15);
772 BEGIN
773 /*
774 ***
775 *** Get multi-org and MRC information on the current
776 *** prodcut installation.
777 ***
778 */
779 SELECT nvl(multi_org_flag, 'N')
780 , nvl(multi_currency_flag, 'N')
781 INTO l_multi_org
782 , l_multi_cur
783 FROM fnd_product_groups;
784
785 /*
786 ***
787 *** Case #1 : Non-Multi-Org or Multi-SOB
788 ***
789 *** A. MRC not installed, OR
790 *** MRC installed, Non-Primary/Reporting Books
791 *** Form Name (SOB Short Name) - Context Info
792 *** e.g. Maintain Forecast (US OPS) - Forecast Context Info
793 ***
794 *** B. MRC installed, Primary Books
795 *** Form Name (SOB Short Name: Primary Currency) - Context Info
796 *** e.g. Maintain Forecast (US OPS: USD) - Forecast Context Info
797 ***
798 *** C. MRC installed, Reporting Books
799 *** Form Name (SOB Short Name: Reporting Currency) - Context Info
800 *** e.g. Maintain Forecast (US OPS: EUR) - Forecast Context Info
801 ***
802 */
803
804 IF (l_multi_org = 'N') THEN
805 BEGIN
806 select substrb((g.SHORT_NAME || decode(g.mrc_sob_type_code, 'N', NULL,
807 decode(l_multi_cur, 'N', NULL,
808 ': ' || substr(g.currency_code, 1, 5)))),1,60)
809 into l_wnd_context
810 from gl_sets_of_books g
811 , ap_system_parameters aps
812 where aps.SET_OF_BOOKS_ID = g.SET_OF_BOOKS_ID;
813 EXCEPTION
814 WHEN NO_DATA_FOUND THEN
815 return (null);
816 END;
817
818 /*
819 ***
820 *** Case #2 : Multi-Org
821 ***
822 *** A. MRC not installed, OR
823 *** MRC installed, Non-Primary/Reporting Books
824 *** Form Name (OU Name) - Context Info
825 *** e.g. Maintain Forecast (US West) - Forecast Context Info
826 ***
827 *** B. MRC installed, Primary Books
828 *** Form Name (OU Name: Primary Currency) - Context Info
829 *** e.g. Maintain Forecast (US West: USD) - Forecast Context Info
830 ***
831 *** C. MRC installed, Reporting Books
832 *** Form Name (OU Name: Reporting Currency) - Context Info
833 *** e.g. Maintain Forecast (US West: EUR) - Forecast Context Info
834 ***
835 */
836 ELSE
837
838 --Bug 1696006 replace this line
839 -- FND_PROFILE.GET ('ORG_ID', l_id);
840 --with the following lines
841 l_id := substrb(userenv('CLIENT_INFO'),1,10);
842 if substrb(l_id,1,1) = ' ' then
843 l_id := NULL;
844 end if;
845 --End Bug 1696006
846
847 BEGIN
848 select substrb((substr(h.Name, 1, 53)
849 || decode(g.mrc_sob_type_code, 'N', NULL,
850 decode(l_multi_cur, 'N', NULL,
851 ': ' || substr(g.currency_code, 1, 5)))),1,60)
852 into l_wnd_context
853 from gl_sets_of_books g,
854 ap_system_parameters aps,
855 hr_operating_units h
856 where h.organization_id = to_number(l_id)
857 and aps.set_of_books_id = g.set_of_books_id;
858 EXCEPTION
859 WHEN NO_DATA_FOUND THEN
860 return (NULL);
861 END;
862
863
864 END IF;
865
866 return l_wnd_context;
867
868 END Get_Window_Session_Title;
869
870 FUNCTION overlay_segments (
871 p_balancing_segment IN VARCHAR2,
872 p_cost_center_segment IN VARCHAR2,
873 p_account_segment IN VARCHAR2,
874 p_concatenated_segments IN VARCHAR2,
875 p_ccid IN OUT NOCOPY NUMBER,
876 p_set_of_books_id IN NUMBER,
877 p_overlay_mode IN VARCHAR2,
878 p_unbuilt_flex OUT NOCOPY VARCHAR2,
879 p_reason_unbuilt_flex OUT NOCOPY VARCHAR2,
880 p_resp_appl_id IN NUMBER,
881 p_resp_id IN NUMBER,
882 p_user_id IN NUMBER,
883 p_calling_sequence IN VARCHAR2,
884 p_ccid_to_segs IN VARCHAR2 Default NULL)
885
886 RETURN BOOLEAN IS
887
888 overlay_segments_failure EXCEPTION;
889 l_ccid NUMBER := p_ccid;
890 l_chart_of_accounts_id NUMBER;
891 l_segments FND_FLEX_EXT.SEGMENTARRAY;
892 l_partial_segments FND_FLEX_EXT.SEGMENTARRAY;
893 l_num_segments NUMBER;
894 l_account_segment_num NUMBER;
895 l_balancing_segment_num NUMBER;
896 l_cost_center_segment_num NUMBER;
897 l_partial_num_segments NUMBER;
898 l_overlayed_segments VARCHAR2(2000);
899 l_unbuilt_flex VARCHAR2(240):='';
900 l_reason_unbuilt_flex VARCHAR2(2000):='';
901 l_result BOOLEAN;
902 l_segment_delimiter VARCHAR2(1);
903 l_counter NUMBER;
904 current_calling_sequence VARCHAR2(2000);
905 debug_info VARCHAR2(500);
906
907 BEGIN
908 -- Update the calling sequence
909 --
910 current_calling_sequence := 'AP_UTILITIES_PKG.Overlay_Segments<-'||P_calling_sequence;
911
912 -----------------------------------------------------------
913 -- Reject if it's item line but no account info
914 -----------------------------------------------------------
915 debug_info := 'Select Charts of Account';
916
917
918
919 SELECT chart_of_accounts_id
920 INTO l_chart_of_accounts_id
921 FROM gl_sets_of_books
922 WHERE set_of_books_id = p_set_of_books_id;
923
924 debug_info := 'Get segment delimiter';
925
926
927 l_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
928 'SQLGL',
929 'GL#',
930 l_chart_of_accounts_id);
931
932
933
934 IF (l_segment_delimiter IS NULL) THEN
935 l_reason_unbuilt_flex := FND_MESSAGE.GET;
936 END IF;
937
938 -- Get Segment array for the input ccid
939 --
940
941 IF (l_ccid IS NOT NULL) Then
942
943 debug_info := 'Get segment array';
944 l_result := FND_FLEX_EXT.GET_SEGMENTS(
945 'SQLGL',
946 'GL#',
947 l_chart_of_accounts_id,
948 l_ccid,
949 l_num_segments,
950 l_segments);
951
952
953 IF (NOT l_result) THEN
954 l_reason_unbuilt_flex := FND_MESSAGE.GET;
955 END IF;
956
957 END IF; -- l_ccid not null
958 --
959 -- Get concatenated segments from ccid
960 IF (nvl(p_ccid_to_segs,'N') = 'Y') Then
961 l_overlayed_segments := FND_FLEX_EXT.Concatenate_Segments(l_num_segments,
962 l_segments,
963 l_segment_delimiter);
964
965 IF (NOT l_result) THEN
966 l_reason_unbuilt_flex := FND_MESSAGE.GET;
967 l_ccid := -1;
968 END IF;
969
970 p_ccid := l_ccid;
971 p_unbuilt_flex := l_overlayed_segments;
972 p_reason_unbuilt_flex := 'Used for deriving segments from ccid';
973 Return(TRUE);
974 END IF;
975
976
977 -- Get the partial segment array
978 --
979 IF (p_concatenated_segments IS NOT NULL) THEN
980
981 debug_info := 'Get Partial segment array';
982
983 l_partial_num_segments := FND_FLEX_EXT.breakup_segments(p_concatenated_segments,
984 l_segment_delimiter,
985 l_partial_segments); --OUT
986
987 END IF;
988 -- Overlay partial with original
989 -- only if l_num_segments = l_partial_num_segments
990
991 IF ((l_ccid IS NOT NULL) AND (p_concatenated_segments IS NOT NULL)) Then
992 IF (l_num_segments = l_partial_num_segments) Then
993
994
995
996 debug_info := 'Overlay Partial segment array';
997
998 For l_counter IN 1..l_num_segments LOOP
999
1000
1001 IF (l_partial_segments(l_counter) IS NOT NULL) Then
1002
1003 l_segments(l_counter) := l_partial_segments(l_counter);
1004
1005 End If;
1006
1007
1008
1009 END LOOP;
1010
1011 ELSE
1012 -- Reject Inconsistent Segments
1013 --
1014 p_ccid := -1;
1015 p_reason_unbuilt_flex := 'Inconsistent Segments';
1016 p_unbuilt_flex := Null;
1017 RETURN(TRUE);
1018
1019 END IF;
1020
1021 ElSIF ((l_ccid IS NULL) AND (p_concatenated_segments IS NOT NULL)) Then
1022
1023 -- we want to overlay concatenated segment
1024 l_segments := l_partial_segments;
1025 l_num_segments := l_partial_num_segments;
1026
1027 END IF; -- l_ccid is not null
1028
1029 -- Get the segment num for
1030 -- GL_ACCOUNT , GL_BALANCING and GL_COST_CENTER
1031
1032
1033 l_result := FND_FLEX_KEY_API.GET_SEG_ORDER_BY_QUAL_NAME(
1034 101,
1035 'GL#',
1036 l_chart_of_accounts_id,
1037 'GL_ACCOUNT',
1038 l_account_segment_num);
1039
1040
1041 IF (NOT l_result) THEN
1042 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1043 END IF;
1044
1045
1046 l_result := FND_FLEX_KEY_API.GET_SEG_ORDER_BY_QUAL_NAME(
1047 101,
1048 'GL#',
1049 l_chart_of_accounts_id,
1050 'GL_BALANCING',
1051 l_balancing_segment_num);
1052
1053
1054 IF (NOT l_result) THEN
1055 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1056 END IF;
1057
1058
1059 l_result := FND_FLEX_KEY_API.GET_SEG_ORDER_BY_QUAL_NAME(
1060 101,
1061 'GL#',
1062 l_chart_of_accounts_id,
1063 'FA_COST_CTR',
1064 l_cost_center_segment_num);
1065
1066
1067 IF (NOT l_result) THEN
1068 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1069 END IF;
1070
1071
1072
1073 -- Now overlay the Account, balancing and Cost Center segments
1074 -- if not null.
1075
1076
1077 IF (p_balancing_segment IS NOT NULL) Then
1078
1079 debug_info := 'Overlay balancing segment ';
1080 l_segments(l_balancing_segment_num) := p_balancing_segment;
1081
1082 End IF;
1083
1084
1085 IF (p_cost_center_segment IS NOT NULL) Then
1086
1087 debug_info := 'Overlay Cost Center segment ';
1088 l_segments(l_cost_center_segment_num) := p_cost_center_segment;
1089
1090 End IF;
1091
1092 IF (p_account_segment IS NOT NULL) Then
1093
1094 debug_info := 'Overlay Account segment ';
1095 l_segments(l_account_segment_num) := p_account_segment;
1096
1097 End IF;
1098
1099
1100 -- Get Concat Segments Back
1101 -- from seg array
1102
1103 l_overlayed_segments := FND_FLEX_EXT.Concatenate_Segments(l_num_segments,
1104 l_segments,
1105 l_segment_delimiter);
1106
1107 IF (NOT l_result) THEN
1108 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1109 END IF;
1110
1111
1112
1113
1114
1115 -- only if for creation (Use Validate segs with
1116 -- CHECK_COMBINATION and CREATE_COMBINATION)
1117 IF (p_overlay_mode = 'CHECK') Then
1118
1119
1120 debug_info := 'Validate Overlayed segments ';
1121 IF (fnd_flex_keyval.validate_segs('CHECK_COMBINATION' ,
1122 'SQLGL',
1123 'GL#',
1124 l_chart_of_accounts_id,
1125 l_overlayed_segments,
1126 'V',
1127 SYSDATE,
1128 'ALL',
1129 NULL,
1130 NULL,
1131 NULL,
1132 NULL,
1133 FALSE,
1134 FALSE,
1135 p_resp_appl_id,
1136 p_resp_id,
1137 p_user_id) <> TRUE) Then
1138
1139 l_ccid := -1;
1140 l_reason_unbuilt_flex := fnd_flex_keyval.error_message;
1141 l_unbuilt_flex := l_overlayed_segments;
1142
1143 Else
1144
1145 l_ccid := 666;
1146 l_reason_unbuilt_flex := NULL;
1147 l_unbuilt_flex := NULL;
1148 END IF;
1149
1150
1151 ELSIF (p_overlay_mode = 'CREATE') Then
1152
1153 debug_info := 'Create Overlayed segments ';
1154 IF (fnd_flex_keyval.validate_segs('CREATE_COMBINATION' ,
1155 'SQLGL',
1156 'GL#',
1157 l_chart_of_accounts_id,
1158 l_overlayed_segments,
1159 'V',
1160 SYSDATE,
1161 'ALL',
1162 NULL,
1163 NULL,
1164 NULL,
1165 NULL,
1166 FALSE,
1167 FALSE,
1168 p_resp_appl_id,
1169 p_resp_id,
1170 p_user_id) <> TRUE) Then
1171
1172 l_ccid := -1;
1173 l_reason_unbuilt_flex := fnd_flex_keyval.error_message;
1174 l_unbuilt_flex := l_overlayed_segments;
1175
1176 Else
1177
1178 l_ccid := fnd_flex_keyval.combination_id;
1179 l_reason_unbuilt_flex := NULL;
1180 l_unbuilt_flex := NULL;
1181
1182 END IF;
1183
1184 -- Bug 1414119 Added the ELSIF condition below to avoid autonomous
1185 -- transaction insert for new code combinations when dynamic insert
1186 -- is on.
1187
1188 ELSIF (p_overlay_mode = 'CREATE_COMB_NO_AT') Then
1189
1190 debug_info := 'Create Overlayed segments ';
1191 IF (fnd_flex_keyval.validate_segs('CREATE_COMB_NO_AT' ,
1192 'SQLGL',
1193 'GL#',
1194 l_chart_of_accounts_id,
1195 l_overlayed_segments,
1196 'V',
1197 SYSDATE,
1198 'ALL',
1199 NULL,
1200 NULL,
1201 NULL,
1202 NULL,
1203 FALSE,
1204 FALSE,
1205 p_resp_appl_id,
1206 p_resp_id,
1207 p_user_id) <> TRUE) Then
1208
1209 l_ccid := -1;
1210 l_reason_unbuilt_flex := fnd_flex_keyval.error_message;
1211 l_unbuilt_flex := l_overlayed_segments;
1212
1213 Else
1214
1215 l_ccid := fnd_flex_keyval.combination_id;
1216 l_reason_unbuilt_flex := NULL;
1217 l_unbuilt_flex := NULL;
1218
1219 END IF;
1220
1221
1222 END IF;
1223
1224 --
1225 -- Return value
1226 -- Bug 3621994 added if condition. CCID should be returned with the overlayed
1227 -- value only if the mode is not check . (Due to 3282531).
1228 IF (p_overlay_mode <> 'CHECK') Then
1229 p_ccid := l_ccid;
1230
1231 End IF;
1232
1233 p_unbuilt_flex := l_unbuilt_flex;
1234 p_reason_unbuilt_flex := l_reason_unbuilt_flex;
1235
1236 RETURN (TRUE);
1237
1238
1239 EXCEPTION
1240
1241 WHEN OTHERS THEN
1242
1243
1244 if (SQLCODE <> -20001) then
1245 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1246 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1247 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1248 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1249 end if;
1250
1251
1252 RETURN(FALSE);
1253
1254 END Overlay_Segments;
1255
1256 --following function added for BUG 1909374
1257 FUNCTION overlay_segments_by_gldate(
1258 p_balancing_segment IN VARCHAR2,
1259 p_cost_center_segment IN VARCHAR2,
1260 p_account_segment IN VARCHAR2,
1261 p_concatenated_segments IN VARCHAR2,
1262 p_ccid IN OUT NOCOPY NUMBER,
1263 p_accounting_date IN DATE,
1264 p_set_of_books_id IN NUMBER,
1265 p_overlay_mode IN VARCHAR2,
1266 p_unbuilt_flex OUT NOCOPY VARCHAR2,
1267 p_reason_unbuilt_flex OUT NOCOPY VARCHAR2,
1268 p_resp_appl_id IN NUMBER,
1269 p_resp_id IN NUMBER,
1270 p_user_id IN NUMBER,
1271 p_calling_sequence IN VARCHAR2,
1272 p_ccid_to_segs IN VARCHAR2 Default NULL)
1273
1274 RETURN BOOLEAN IS
1275
1276 overlay_segments_failure EXCEPTION;
1277 l_ccid NUMBER := p_ccid;
1278 l_chart_of_accounts_id NUMBER;
1279 l_segments FND_FLEX_EXT.SEGMENTARRAY;
1280 l_partial_segments FND_FLEX_EXT.SEGMENTARRAY;
1281 l_num_segments NUMBER;
1282 l_account_segment_num NUMBER;
1283 l_balancing_segment_num NUMBER;
1284 l_cost_center_segment_num NUMBER;
1285 l_partial_num_segments NUMBER;
1286 l_overlayed_segments VARCHAR2(2000);
1287 l_unbuilt_flex VARCHAR2(240):='';
1288 l_reason_unbuilt_flex VARCHAR2(2000):='';
1289 l_result BOOLEAN;
1290 l_segment_delimiter VARCHAR2(1);
1291 l_counter NUMBER;
1292 current_calling_sequence VARCHAR2(2000);
1293 debug_info VARCHAR2(500);
1294
1295 BEGIN
1296 -- Update the calling sequence
1297 --
1298 current_calling_sequence := 'AP_UTILITIES_PKG.Overlay_Segments<-'||P_calling_sequence;
1299
1300 -----------------------------------------------------------
1301 -- Reject if it's item line but no account info
1302 -----------------------------------------------------------
1303 debug_info := 'Select Charts of Account';
1304
1305
1306
1307 SELECT chart_of_accounts_id
1308 INTO l_chart_of_accounts_id
1309 FROM gl_sets_of_books
1310 WHERE set_of_books_id = p_set_of_books_id;
1311
1312 debug_info := 'Get segment delimiter';
1313
1314
1315 l_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
1316 'SQLGL',
1317 'GL#',
1318 l_chart_of_accounts_id);
1319
1320
1321
1322 IF (l_segment_delimiter IS NULL) THEN
1323 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1324 END IF;
1325
1326 -- Get Segment array for the input ccid
1327 --
1328
1329 IF (l_ccid IS NOT NULL) Then
1330
1331 debug_info := 'Get segment array';
1332 l_result := FND_FLEX_EXT.GET_SEGMENTS(
1333 'SQLGL',
1334 'GL#',
1335 l_chart_of_accounts_id,
1336 l_ccid,
1337 l_num_segments,
1338 l_segments);
1339
1340
1341 IF (NOT l_result) THEN
1342 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1343 END IF;
1344
1345 END IF; -- l_ccid not null
1346 --
1347 -- Get concatenated segments from ccid
1348 IF (nvl(p_ccid_to_segs,'N') = 'Y') Then
1349 l_overlayed_segments := FND_FLEX_EXT.Concatenate_Segments(l_num_segments,
1350 l_segments,
1351 l_segment_delimiter);
1352
1353 IF (NOT l_result) THEN
1354 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1355 l_ccid := -1;
1356 END IF;
1357
1358 p_ccid := l_ccid;
1359 p_unbuilt_flex := l_overlayed_segments;
1360 p_reason_unbuilt_flex := 'Used for deriving segments from ccid';
1361 Return(TRUE);
1362 END IF;
1363
1364
1365 -- Get the partial segment array
1366 --
1367 IF (p_concatenated_segments IS NOT NULL) THEN
1368
1369 debug_info := 'Get Partial segment array';
1370
1371 l_partial_num_segments := FND_FLEX_EXT.breakup_segments(p_concatenated_segments,
1372 l_segment_delimiter,
1373 l_partial_segments); --OUT
1374
1375 END IF;
1376 -- Overlay partial with original
1377 -- only if l_num_segments = l_partial_num_segments
1378
1379 IF ((l_ccid IS NOT NULL) AND (p_concatenated_segments IS NOT NULL)) Then
1380 IF (l_num_segments = l_partial_num_segments) Then
1381
1382
1383
1384 debug_info := 'Overlay Partial segment array';
1385
1386 For l_counter IN 1..l_num_segments LOOP
1387
1388
1389 IF (l_partial_segments(l_counter) IS NOT NULL) Then
1390
1391 l_segments(l_counter) := l_partial_segments(l_counter);
1392
1393 End If;
1394
1395
1396
1397 END LOOP;
1398
1399 ELSE
1400 -- Reject Inconsistent Segments
1401 --
1402 p_ccid := -1;
1403 p_reason_unbuilt_flex := 'Inconsistent Segments';
1404 p_unbuilt_flex := Null;
1405 RETURN(TRUE);
1406
1407 END IF;
1408
1409 ElSIF ((l_ccid IS NULL) AND (p_concatenated_segments IS NOT NULL)) Then
1410
1411 -- we want to overlay concatenated segment
1412 l_segments := l_partial_segments;
1413 l_num_segments := l_partial_num_segments;
1414
1415 END IF; -- l_ccid is not null
1416
1417 -- Get the segment num for
1418 -- GL_ACCOUNT , GL_BALANCING and GL_COST_CENTER
1419
1420
1421 l_result := FND_FLEX_KEY_API.GET_SEG_ORDER_BY_QUAL_NAME(
1422 101,
1423 'GL#',
1424 l_chart_of_accounts_id,
1425 'GL_ACCOUNT',
1426 l_account_segment_num);
1427
1428
1429 IF (NOT l_result) THEN
1430 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1431 END IF;
1432
1433
1434 l_result := FND_FLEX_KEY_API.GET_SEG_ORDER_BY_QUAL_NAME(
1435 101,
1436 'GL#',
1437 l_chart_of_accounts_id,
1438 'GL_BALANCING',
1439 l_balancing_segment_num);
1440
1441
1442 IF (NOT l_result) THEN
1443 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1444 END IF;
1445
1446
1447 l_result := FND_FLEX_KEY_API.GET_SEG_ORDER_BY_QUAL_NAME(
1448 101,
1449 'GL#',
1450 l_chart_of_accounts_id,
1451 'FA_COST_CTR',
1452 l_cost_center_segment_num);
1453
1454
1455 IF (NOT l_result) THEN
1456 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1457 END IF;
1458
1459
1460
1461 -- Now overlay the Account, balancing and Cost Center segments
1462 -- if not null.
1463
1464
1465 IF (p_balancing_segment IS NOT NULL) Then
1466
1467 debug_info := 'Overlay balancing segment ';
1468 l_segments(l_balancing_segment_num) := p_balancing_segment;
1469
1470 End IF;
1471
1472
1473 IF (p_cost_center_segment IS NOT NULL) Then
1474
1475 debug_info := 'Overlay Cost Center segment ';
1476 l_segments(l_cost_center_segment_num) := p_cost_center_segment;
1477
1478 End IF;
1479
1480 IF (p_account_segment IS NOT NULL) Then
1481
1482 debug_info := 'Overlay Account segment ';
1483 l_segments(l_account_segment_num) := p_account_segment;
1484
1485 End IF;
1486
1487
1488 -- Get Concat Segments Back
1489 -- from seg array
1490
1491 l_overlayed_segments := FND_FLEX_EXT.Concatenate_Segments(l_num_segments,
1492 l_segments,
1493 l_segment_delimiter);
1494
1495 IF (NOT l_result) THEN
1496 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1497 END IF;
1498
1499
1500
1501
1502
1503 -- only if for creation (Use Validate segs with
1504 -- CHECK_COMBINATION and CREATE_COMBINATION)
1505 IF (p_overlay_mode = 'CHECK') Then
1506
1507
1508 debug_info := 'Validate Overlayed segments ';
1509 IF (fnd_flex_keyval.validate_segs('CHECK_COMBINATION' ,
1510 'SQLGL',
1511 'GL#',
1512 l_chart_of_accounts_id,
1513 l_overlayed_segments,
1514 'V',
1515 p_accounting_date,
1516 'ALL',
1517 NULL,
1518 NULL,
1519 NULL,
1520 NULL,
1521 FALSE,
1522 FALSE,
1523 p_resp_appl_id,
1524 p_resp_id,
1525 p_user_id) <> TRUE) Then
1526
1527 l_ccid := -1;
1528 l_reason_unbuilt_flex := fnd_flex_keyval.error_message;
1529 l_unbuilt_flex := l_overlayed_segments;
1530
1531 Else
1532
1533 l_ccid := 666;
1534 l_reason_unbuilt_flex := NULL;
1535 l_unbuilt_flex := NULL;
1536 END IF;
1537
1538
1539 ELSIF (p_overlay_mode = 'CREATE') Then
1540
1541 debug_info := 'Create Overlayed segments ';
1542 IF (fnd_flex_keyval.validate_segs('CREATE_COMBINATION' ,
1543 'SQLGL',
1544 'GL#',
1545 l_chart_of_accounts_id,
1546 l_overlayed_segments,
1547 'V',
1548 p_accounting_date,
1549 'ALL',
1550 NULL,
1551 NULL,
1552 NULL,
1553 NULL,
1554 FALSE,
1555 FALSE,
1556 p_resp_appl_id,
1557 p_resp_id,
1558 p_user_id) <> TRUE) Then
1559
1560 l_ccid := -1;
1561 l_reason_unbuilt_flex := fnd_flex_keyval.error_message;
1562 l_unbuilt_flex := l_overlayed_segments;
1563
1564 Else
1565
1566 l_ccid := fnd_flex_keyval.combination_id;
1567 l_reason_unbuilt_flex := NULL;
1568 l_unbuilt_flex := NULL;
1569
1570 END IF;
1571
1572 -- Bug 1414119 Added the ELSIF condition below to avoid autonomous
1573 -- transaction insert for new code combinations when dynamic insert
1574 -- is on.
1575
1576 ELSIF (p_overlay_mode = 'CREATE_COMB_NO_AT') Then
1577
1578 debug_info := 'Create Overlayed segments ';
1579 IF (fnd_flex_keyval.validate_segs('CREATE_COMB_NO_AT' ,
1580 'SQLGL',
1581 'GL#',
1582 l_chart_of_accounts_id,
1583 l_overlayed_segments,
1584 'V',
1585 p_accounting_date,
1586 'ALL',
1587 NULL,
1588 NULL,
1589 NULL,
1590 NULL,
1591 FALSE,
1592 FALSE,
1593 p_resp_appl_id,
1594 p_resp_id,
1595 p_user_id) <> TRUE) Then
1596
1597 l_ccid := -1;
1598 l_reason_unbuilt_flex := fnd_flex_keyval.error_message;
1599 l_unbuilt_flex := l_overlayed_segments;
1600
1601 Else
1602
1603 l_ccid := fnd_flex_keyval.combination_id;
1604 l_reason_unbuilt_flex := NULL;
1605 l_unbuilt_flex := NULL;
1606
1607 END IF;
1608
1609
1610 END IF;
1611
1612 --
1613 -- Return value
1614 p_ccid := l_ccid;
1615 p_unbuilt_flex := l_unbuilt_flex;
1616 p_reason_unbuilt_flex := l_reason_unbuilt_flex;
1617
1618 RETURN (TRUE);
1619
1620
1621 EXCEPTION
1622
1623 WHEN OTHERS THEN
1624
1625
1626 if (SQLCODE <> -20001) then
1627 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1628 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1629 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1630 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1631 end if;
1632
1633
1634 RETURN(FALSE);
1635
1636 END Overlay_Segments_by_gldate;
1637
1638
1639 FUNCTION check_partial(
1640 p_concatenated_segments IN VARCHAR2,
1641 p_partial_segments_flag OUT NOCOPY VARCHAR2,
1642 p_set_of_books_id IN NUMBER,
1643 p_error_message OUT NOCOPY VARCHAR2,
1644 p_calling_sequence IN VARCHAR2)
1645
1646 RETURN BOOLEAN IS
1647
1648 l_chart_of_accounts_id NUMBER;
1649 l_segments FND_FLEX_EXT.SEGMENTARRAY;
1650 l_num_segments NUMBER;
1651 l_segment_delimiter VARCHAR2(1);
1652 current_calling_sequence VARCHAR2(2000);
1653 debug_info VARCHAR2(500);
1654
1655
1656
1657 BEGIN
1658 -- Update the calling sequence
1659 --
1660 current_calling_sequence := 'AP_UTILITIES_PKG.Check_Partial<-'||P_calling_sequence;
1661
1662
1663
1664 debug_info := 'Select Charts of Account';
1665
1666 SELECT chart_of_accounts_id
1667 INTO l_chart_of_accounts_id
1668 FROM gl_sets_of_books
1669 WHERE set_of_books_id = p_set_of_books_id;
1670
1671 debug_info := 'Get Segment Delimiter';
1672
1673 l_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
1674 'SQLGL',
1675 'GL#',
1676 l_chart_of_accounts_id);
1677
1678
1679
1680 IF (l_segment_delimiter IS NULL) THEN
1681 p_error_message:= FND_MESSAGE.GET;
1682 END IF;
1683
1684 debug_info := 'Break Segments';
1685 l_num_segments := FND_FLEX_EXT.breakup_segments(p_concatenated_segments,
1686 l_segment_delimiter,
1687 l_segments); --OUT
1688
1689 p_partial_segments_flag := 'N';
1690
1691 For l_counter IN 1..l_num_segments LOOP
1692
1693
1694 IF (l_segments(l_counter) IS NULL) Then
1695
1696 p_partial_segments_flag := 'Y';
1697
1698 End If;
1699
1700
1701 END LOOP;
1702 RETURN (TRUE);
1703
1704 EXCEPTION
1705
1706
1707
1708 WHEN OTHERS THEN
1709
1710 if (SQLCODE <> -20001) then
1711 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1712 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1713 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1714 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1715 end if;
1716 RETURN(FALSE);
1717
1718 END Check_partial;
1719
1720 FUNCTION IS_CCID_VALID ( p_ccid IN NUMBER,
1721 p_chart_of_accounts_id IN NUMBER,
1722 p_date IN DATE ,
1723 p_calling_sequence IN VARCHAR2 )
1724
1725 RETURN BOOLEAN IS
1726
1727 -- Bug 3621994 -Deleted the unnecessary variables (due to 3086316)
1728
1729 l_enabled_flag gl_code_combinations.enabled_flag%type;
1730 current_calling_sequence VARCHAR2(2000);
1731 debug_info VARCHAR2(500);
1732
1733 BEGIN
1734 -- Update the calling sequence
1735 --
1736 current_calling_sequence := 'AP_UTILITIES_PKG.Ccid_Valid<-'||P_calling_sequence;
1737
1738 debug_info := 'Validate ccid as a whole';
1739
1740 If (( fnd_flex_keyval.validate_ccid(
1741 APPL_SHORT_NAME =>'SQLGL',
1742 KEY_FLEX_CODE => 'GL#',
1743 STRUCTURE_NUMBER =>p_chart_of_accounts_id,
1744 COMBINATION_ID =>p_ccid,
1745 DISPLAYABLE =>'ALL',
1746 DATA_SET => NULL,
1747 VRULE => NULL,
1748 SECURITY => 'ENFORCE',
1749 GET_COLUMNS => NULL,
1750 RESP_APPL_ID => FND_GLOBAL.resp_appl_id,
1751 RESP_ID => FND_GLOBAL.resp_id,
1752 USER_ID => FND_GLOBAL.user_id))) then
1753
1754 If (NOT ( fnd_flex_keyval.is_valid ) OR
1755 (fnd_flex_keyval.is_secured)) then
1756 RETURN(FALSE);
1757 end if;
1758
1759 Else
1760 RETURN(FALSE);
1761 End if;
1762
1763 -- Bug 3621994 - Removed following code to get and validate segments as It
1764 --Is redundant and casused performance issue (due to 3086316)
1765
1766 /* Bug: 3486932 Check to see if the ccid is enabled in GL. If the ccid
1767 is not enabled then return -1 */
1768
1769 SELECT nvl(enabled_flag,'N')
1770 INTO l_enabled_flag
1771 FROM gl_code_combinations
1772 WHERE code_combination_id = p_ccid
1773 AND chart_of_accounts_id = p_chart_of_accounts_id
1774 -- Bug 3486932 - Added the following conditions to verify if GL account
1775 -- is valid and summary flag and template id are proper.
1776
1777 -- Bug 3379623 deleted the previous AND stmt and added the below two.
1778 AND NVL(start_date_active, TRUNC(p_date)) <= TRUNC(p_date)
1779 AND NVL(end_date_active,
1780 TO_DATE('12/31/4012','MM/DD/YYYY')) >= TRUNC(p_date)
1781 AND summary_flag = 'N'
1782 AND template_id is NULL;
1783
1784 IF l_enabled_flag = 'N' then
1785
1786 Return (FALSE);
1787
1788 End If;
1789
1790
1791 RETURN (TRUE);
1792
1793 EXCEPTION
1794
1795 WHEN OTHERS THEN
1796
1797 if (SQLCODE <> -20001) then
1798 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1799 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1800 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1801 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1802 end if;
1803 RETURN(FALSE);
1804
1805 END IS_CCID_VALID;
1806
1807
1808
1809 -- MO Access Control: Added the parameter p_org_id.
1810 -- including default to the current_org_id
1811
1812 FUNCTION Get_Inventory_Org(P_org_id Number default mo_global.get_current_org_id) return NUMBER IS
1813 inv_org_id financials_system_parameters.inventory_organization_id%TYPE;
1814 BEGIN
1815
1816 select inventory_organization_id
1817 into inv_org_id
1818 from financials_system_parameters
1819 where org_id = p_org_id;
1820
1821 return(inv_org_id);
1822
1823 EXCEPTION
1824 WHEN NO_DATA_FOUND THEN
1825 return(null);
1826 END Get_Inventory_Org;
1827
1828
1829 PROCEDURE mc_flag_enabled( p_sob_id IN NUMBER,
1830 p_appl_id IN NUMBER,
1831 p_org_id IN NUMBER,
1832 p_fa_book_code IN VARCHAR2,
1833 p_base_currency IN VARCHAR2,
1834 p_mc_flag_enabled OUT NOCOPY VARCHAR2,
1835 p_calling_sequence IN VARCHAR2) IS
1836
1837 loop_index NUMBER := 1;
1838 l_sob_list gl_mc_info.r_sob_list:= gl_mc_info.r_sob_list() ;
1839 current_calling_sequence VARCHAR2(2000);
1840 debug_info VARCHAR2(500);
1841 NO_GL_DATA EXCEPTION;
1842
1843 BEGIN
1844 -- Update the calling sequence
1845 --
1846 current_calling_sequence := 'AP_UTILITIES_PKG.mc_flag_enabled<-'||p_calling_sequence;
1847
1848 debug_info := 'Calling GL package to get a list of reporting set of books';
1849 gl_mc_info.get_associated_sobs (p_sob_id,
1850 p_appl_id, p_org_id, NULL,
1851 l_sob_list);
1852 p_mc_flag_enabled := 'Y';
1853
1854 debug_info := 'Loop through every set of books and see if all the reporting currency is euro derived';
1855
1856 WHILE loop_index <= l_sob_list.count LOOP
1857 if ( gl_currency_api.is_fixed_rate (p_base_currency,
1858 l_sob_list(loop_index).r_sob_curr,
1859 sysdate) <> 'Y' ) then
1860 p_mc_flag_enabled := 'N';
1861 EXIT;
1862 end if;
1863 if ( gl_currency_api.is_fixed_rate (p_base_currency,
1864 l_sob_list(loop_index).r_sob_curr,
1865 sysdate) is NULL ) then
1866 raise NO_GL_DATA;
1867 end if;
1868 loop_index := loop_index +1;
1869 END LOOP;
1870
1871 EXCEPTION
1872 WHEN NO_GL_DATA THEN
1873
1874 p_mc_flag_enabled := NULL;
1875
1876 if (SQLCODE <> -20001) then
1877 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1878 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1879 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1880 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1881 end if;
1882
1883 END mc_flag_enabled;
1884
1885
1886 function AP_Get_Sob_Order_Col
1887 (P_Primary_SOB_ID IN number
1888 ,P_Secondary_SOB_ID IN number
1889 ,P_SOB_ID IN number
1890 ,P_ORG_ID IN number
1891 ,P_Calling_Sequence IN varchar2)
1892 return NUMBER is
1893 l_primary_sob_id number;
1894 l_sob_order_col number;
1895 current_calling_sequence varchar2(2000);
1896 debug_info varchar2(500);
1897 begin
1898 -- Update the calling sequence
1899 --
1900 current_calling_sequence :=
1901 'AP_UTILITIES_PKG.AP_Get_Sob_Order_Col<-'||p_calling_sequence;
1902 debug_info := 'Getting the order column value';
1903 l_primary_sob_id :=GL_MC_INFO.get_source_ledger_id(P_SOB_ID,200,P_ORG_ID,'');
1904
1905 /*
1906 SELECT DISTINCT primary_set_of_books_id
1907 INTO l_primary_sob_id
1908 FROM gl_mc_reporting_options
1909 WHERE reporting_set_of_books_id = P_SOB_ID
1910 AND application_id = 200;
1911 */
1912 if (l_primary_sob_id = P_Primary_SOB_ID) then
1913 l_sob_order_col := 1;
1914 else
1915 l_sob_order_col := 2;
1916 end if;
1917
1918 return (l_sob_order_col);
1919
1920 EXCEPTION
1921 WHEN OTHERS THEN
1922
1923 if (SQLCODE <> -20001) then
1924 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1925 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1926 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1927 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1928 end if;
1929 return NULL;
1930
1931 end AP_Get_Sob_Order_Col;
1932
1933
1934 FUNCTION get_charge_account
1935 ( p_ccid IN NUMBER,
1936 p_chart_of_accounts_id IN NUMBER,
1937 p_calling_sequence IN VARCHAR2)
1938 RETURN VARCHAR2 IS
1939
1940 current_calling_sequence VARCHAR2(2000);
1941 debug_info VARCHAR2(500);
1942 l_return_val VARCHAR2(2000);
1943 BEGIN
1944 -- Update the calling sequence
1945 --
1946 current_calling_sequence := 'AP_UTILITIES_PKG.get_charge_account<-'||p_calling_sequence;
1947 debug_info := 'Calling fnd function to validate ccid';
1948
1949 if p_ccid <> -1 then
1950
1951 l_return_val := FND_FLEX_EXT.GET_SEGS(
1952 APPLICATION_SHORT_NAME => 'SQLGL',
1953 KEY_FLEX_CODE => 'GL#',
1954 STRUCTURE_NUMBER => P_CHART_OF_ACCOUNTS_ID,
1955 COMBINATION_ID => P_CCID);
1956
1957 else
1958 l_return_val := null;
1959 end if;
1960 return (l_return_val);
1961
1962 EXCEPTION
1963 WHEN OTHERS THEN
1964
1965 if (SQLCODE <> -20001) then
1966 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1967 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1968 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1969 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1970 end if;
1971 return NULL;
1972
1973 END get_charge_account;
1974
1975 FUNCTION get_invoice_status(p_invoice_id IN NUMBER,
1976 p_calling_sequence IN VARCHAR2)
1977 RETURN VARCHAR2
1978 IS
1979 l_force_revalidation_flag VARCHAR2(1); --bug7244642
1980 invoice_status VARCHAR2(80);
1981 invoice_approval_status VARCHAR2(25);
1982 invoice_approval_flag VARCHAR2(1);
1983 distribution_approval_flag VARCHAR2(1);
1984 encumbrance_flag VARCHAR2(1);
1985 invoice_holds NUMBER;
1986 l_org_id NUMBER;
1987 l_curr_calling_sequence VARCHAR2(2000);
1988 l_debug_info VARCHAR2(100);
1989
1990
1991 ---------------------------------------------------------------------
1992 -- Declare cursor to establish the invoice-level approval flag
1993 --
1994 -- The first select simply looks at the match status flag for the
1995 -- distributions. The rest is to cover one specific case when some
1996 -- of the distributions are tested (T or A) and some are untested
1997 -- (NULL). The status should be needs reapproval (N).
1998 --
1999 CURSOR approval_cursor IS
2000 SELECT match_status_flag
2001 FROM ap_invoice_distributions_all
2002 WHERE invoice_id = p_invoice_id
2003 UNION
2004 SELECT 'N'
2005 FROM ap_invoice_distributions_all
2006 WHERE invoice_id = p_invoice_id
2007 AND match_status_flag IS NULL
2008 AND EXISTS
2009 (SELECT 'There are both untested and tested lines'
2010 FROM ap_invoice_distributions_all
2011 WHERE invoice_id = p_invoice_id
2012 AND match_status_flag IN ('T','A'))
2013 UNION -- Bug 6866672
2014 SELECT 'N'
2015 FROM ap_invoice_lines_all ail, ap_invoices_all ai
2016 WHERE ai.invoice_id = p_invoice_id
2017 AND ai.invoice_id = ail.invoice_id
2018 AND ai.cancelled_date is NULL
2019 AND NOT EXISTS
2020 (SELECT 1
2021 FROM ap_invoice_distributions_all
2022 WHERE invoice_id = p_invoice_id
2023 AND invoice_line_number = ail.line_number)
2024 AND ail.amount <> 0; -- Bug 6911199. Should ignore 0 Line Amounts.(Also one test case is an open issue)
2025
2026 BEGIN
2027
2028 l_curr_calling_sequence := 'AP_UTILITIES_PKG.'||'
2029 <-'||p_calling_sequence;
2030
2031 l_debug_info := 'Getting org_id';
2032 ---------------------------------------------------------------------
2033 -- Get the org_id
2034 --
2035 SELECT org_id
2036 INTO l_org_id
2037 FROM ap_invoices_all
2038 WHERE invoice_id = p_invoice_id;
2039
2040 ---------------------------------------------------------------------
2041 l_debug_info := 'Getting encumbrance flag';
2042
2043 -- Get the encumbrance flag
2044 --
2045 -- Fix for 1407074. Substituting the org_id with -99 if it's null
2046
2047 SELECT NVL(purch_encumbrance_flag,'N')
2048 INTO encumbrance_flag
2049 FROM financials_system_params_all
2050 WHERE NVL(org_id, -99) = NVL(l_org_id, -99);
2051
2052 ---------------------------------------------------------------------
2053 l_debug_info := 'Get hold count for invoice';
2054
2055 -- Get the number of holds for the invoice
2056 --
2057 SELECT count(*)
2058 INTO invoice_holds
2059 FROM ap_holds_all
2060 WHERE invoice_id = p_invoice_id
2061 AND release_lookup_code is NULL;
2062
2063 ---------------------------------------------------------------------
2064 -- bug7244642
2065 l_debug_info := 'get the force revalidation flag on the Invoice header';
2066
2067 BEGIN
2068
2069 SELECT nvl(ai.force_revalidation_flag, 'N')
2070 INTO l_force_revalidation_flag
2071 FROM ap_invoices_all ai
2072 WHERE ai.invoice_id = p_invoice_id;
2073
2074 EXCEPTION
2075 WHEN OTHERS THEN
2076 null;
2077
2078 END;
2079
2080 -- Establish the invoice-level approval flag
2081 --
2082 -- Use the following ordering sequence to determine the
2083 -- invoice-level approval flag:
2084 --
2085 -- 'N' - Needs Reapproval
2086 -- 'T' - Tested
2087 -- 'A' - Approved
2088 -- '' - Never Approved
2089 --
2090 -- Initialize invoice-level approval flag
2091 --
2092 invoice_approval_flag := '';
2093
2094 l_debug_info := 'Open approval_cursor';
2095
2096 OPEN approval_cursor;
2097
2098 LOOP
2099 l_debug_info := 'Fetching approval_cursor';
2100 FETCH approval_cursor INTO distribution_approval_flag;
2101 EXIT WHEN approval_cursor%NOTFOUND;
2102
2103 IF (distribution_approval_flag = 'N') THEN
2104 invoice_approval_flag := 'N';
2105 ELSIF (distribution_approval_flag = 'T' AND
2106 (invoice_approval_flag <> 'N'
2107 or invoice_approval_flag is null)) THEN
2108 invoice_approval_flag := 'T';
2109 ELSIF (distribution_approval_flag = 'A' AND
2110 (invoice_approval_flag NOT IN ('N','T')
2111 or invoice_approval_flag is null)) THEN
2112 invoice_approval_flag := 'A';
2113 END IF;
2114
2115 END LOOP;
2116 l_debug_info := 'Closing approval_cursor';
2117 CLOSE approval_cursor;
2118
2119 IF (invoice_approval_flag = 'A') THEN
2120 invoice_approval_status := 'APPROVED';
2121 ELSIF (invoice_approval_flag is null) THEN
2122 invoice_approval_status := 'NEVER APPROVED';
2123 ELSIF (invoice_approval_flag = 'N') THEN
2124 invoice_approval_status := 'NEEDS REAPPROVAL';
2125 ELSIF (invoice_approval_flag = 'T') THEN
2126 IF (encumbrance_flag = 'Y') THEN
2127 invoice_approval_status := 'NEEDS REAPPROVAL';
2128 ELSE
2129 invoice_approval_status := 'APPROVED';
2130 END IF;
2131 END IF; -- invoice_approval_flag
2132
2133 -- bug7244642
2134 IF ((invoice_approval_status = 'APPROVED') AND
2135 (l_force_revalidation_flag = 'Y')) THEN
2136 invoice_approval_status := 'NEEDS REAPPROVAL';
2137 END IF;
2138
2139 IF (invoice_approval_status = 'APPROVED') THEN
2140 invoice_status := 'UNACCOUNTED';
2141 ELSE
2142 invoice_status := 'UNAPPROVED';
2143 END IF;
2144
2145 RETURN(invoice_status);
2146 EXCEPTION
2147 WHEN OTHERS THEN
2148 if (SQLCODE <> -20001) then
2149 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
2150 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
2151 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
2152 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2153 'Invoice id = '||to_char(p_invoice_id)
2154 ||', Org id = '||l_org_id
2155 ||', Encumbrance flag = '||encumbrance_flag);
2156 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2157 end if;
2158 APP_EXCEPTION.RAISE_EXCEPTION;
2159 END get_invoice_status;
2160 ---------------------------------------------------------------------
2161
2162 -- Added by Bug:2022200
2163 -- Function net_invoice_amount returns the Net Invoice amount
2164 -- after subtracting the Prepayment amount applied and the
2165 -- Amount Withheld from the original Invoice amount.
2166 -- This function has been created because till release 11.0
2167 -- we used to reduce the Invoice amount by the prepaid amount
2168 -- and the amount withheld. This was discontinued in release 11.5.
2169 -- From release 11.5 Invoice amount is not changed. So to use
2170 -- any code from earlier releases where the reduced invoice amount
2171 -- is important, this function can be used.
2172 -- Modify this function to get the prepay_amount from the lines
2173 -- instead of distributions. The prepay_invoice_id and prepay_line_number
2174 -- columns will be populated for exclusive tax lines created for
2175 -- prepayment application. For the inclusive case the PREPAY line
2176 -- will include the tax amount applied.
2177
2178 FUNCTION net_invoice_amount(p_invoice_id IN NUMBER)
2179 RETURN NUMBER
2180 IS
2181 l_prepay_amount NUMBER := 0;
2182 l_net_inv_amount NUMBER := 0;
2183 BEGIN
2184 SELECT nvl((0 - sum(AIL.amount)),0)
2185 INTO l_prepay_amount
2186 FROM ap_invoice_lines_all AIL
2187 WHERE AIL.invoice_id = p_invoice_id
2188 AND nvl(AIL.invoice_includes_prepay_flag,'N') = 'N'
2189 AND (AIL.line_type_lookup_code = 'PREPAY'
2190 OR (AIL.line_type_lookup_code = 'TAX'
2191 AND AIL.prepay_invoice_id IS NOT NULL
2192 AND AIL.prepay_line_number IS NOT NULL));
2193
2194 SELECT nvl(AI.invoice_amount,0)- l_prepay_amount
2195 - nvl(AP_INVOICES_UTILITY_PKG.get_amount_withheld(p_invoice_id),0)
2196 INTO l_net_inv_amount
2197 FROM ap_invoices_all AI
2198 WHERE AI.invoice_id = p_invoice_id;
2199
2200
2201 RETURN(l_net_inv_amount);
2202
2203 END net_invoice_amount;
2204
2205 -------------------------------------------------------------------------------
2206 -- PROCEDURE Build_Offset_Account
2207 -- Given the base account and the overlay account, this procedure builds the new
2208 -- offSET account by overlaying them in the appropriate way determined by the
2209 -- auto-offset system option.
2210
2211 -- Parameters
2212 ----------
2213 -- Base_CCID - the account on which the overlaying will be done. In the
2214 -- case of invoices, this is the liability account.
2215 -- Overlay_CCID - the account whose segments will be used to do the
2216 -- overlaying onto the base account. In the case of invoices,
2217 -- this is the expense account.
2218 -- Accounting_Date - The date the flexbuilder will validate the ccid.
2219 -- Result_CCID - OUT NOCOPY param fOR the resulting offSET account
2220 -- Reason_Unbuilt_Flex - IN/OUT param. If (and only if) the account could
2221 -- not be built, it sends back the reason why
2222 -- flexbuilding failed. Otherwise, it goes back with
2223 -- the same value it came in with.
2224 -------------------------------------------------------------------------------
2225 PROCEDURE BUILD_OFFSET_ACCOUNT
2226 (P_base_ccid IN NUMBER
2227 ,P_overlay_ccid IN NUMBER
2228 ,P_accounting_date IN DATE
2229 ,P_result_ccid OUT NOCOPY NUMBER
2230 ,P_Reason_Unbuilt_Flex OUT NOCOPY VARCHAR2
2231 ,P_calling_sequence IN VARCHAR2
2232 ) IS
2233
2234 l_base_segments FND_FLEX_EXT.SEGMENTARRAY ;
2235 l_overlay_segments FND_FLEX_EXT.SEGMENTARRAY ;
2236 l_segments FND_FLEX_EXT.SEGMENTARRAY ;
2237 l_num_of_segments NUMBER ;
2238 l_result BOOLEAN ;
2239 l_curr_calling_sequence VARCHAR2(2000);
2240 G_flex_qualifier_name VARCHAR2(100);
2241 l_primary_sob_id AP_SYSTEM_PARAMETERS.set_of_books_id%TYPE;
2242 l_liability_post_lookup_code AP_SYSTEM_PARAMETERS.liability_post_lookup_code%TYPE;
2243 l_chart_of_accts_id GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE;
2244 G_flex_segment_num NUMBER;
2245
2246
2247 BEGIN
2248
2249 SELECT set_of_books_id,
2250 nvl(liability_post_lookup_code, 'NONE')
2251 INTO l_primary_sob_id,
2252 l_liability_post_lookup_code
2253 FROM ap_system_parameters;
2254
2255 SELECT chart_of_accounts_id
2256 INTO l_chart_of_accts_id
2257 FROM gl_sets_of_books
2258 WHERE set_of_books_id = l_primary_sob_id;
2259
2260 -- Get flexfield qualifier segment number
2261 IF (l_Liability_Post_Lookup_Code = 'ACCOUNT_SEGMENT_VALUE') THEN
2262
2263 G_flex_qualifier_name := 'GL_ACCOUNT' ;
2264
2265 ELSIF (l_Liability_Post_Lookup_Code = 'BALANCING_SEGMENT') THEN
2266
2267 G_flex_qualifier_name := 'GL_BALANCING' ;
2268
2269 END IF;
2270
2271 l_result := FND_FLEX_KEY_API.GET_SEG_ORDER_BY_QUAL_NAME(
2272 101, 'GL#',
2273 l_chart_of_accts_id,
2274 G_flex_qualifier_name,
2275 G_flex_segment_num);
2276
2277
2278 l_curr_calling_sequence := 'AP_ACCOUNTING_MAIN_PKG.Build_Offset_Account<-'
2279 || P_calling_sequence;
2280
2281 -- Get the segments of the two given accounts
2282 IF (NOT FND_FLEX_EXT.GET_SEGMENTS('SQLGL', 'GL#',
2283 l_chart_of_accts_id,
2284 P_base_ccid, l_num_of_segments,
2285 l_base_segments)
2286 ) THEN
2287
2288 -- Print reason why flex failed
2289 P_result_ccid := -1;
2290 P_reason_unbuilt_flex := 'INVALID ACCOUNT';
2291 RETURN ;
2292
2293 END IF;
2294
2295
2296 IF (NOT FND_FLEX_EXT.GET_SEGMENTS('SQLGL', 'GL#',
2297 l_chart_of_accts_id,
2298 P_overlay_ccid, l_num_of_segments,
2299 l_overlay_segments)
2300 ) THEN
2301 -- Print reason why flex failed
2302 P_result_ccid := -1;
2303 P_reason_unbuilt_flex := 'INVALID ACCOUNT';
2304 RETURN ;
2305
2306 END IF;
2307
2308 /*
2309 Overlay segments depending on system option
2310 Case 1: Account Segment Overlay
2311 Base A A [A] A
2312 Overlay B B [B] B
2313 Result B B [A] B
2314
2315 Case 2: Balancing Segment Overlay
2316 Base [A] A A A
2317 Overlay [B] B B B
2318 Result [B] A A A
2319 */
2320
2321 FOR i IN 1.. l_num_of_segments LOOP
2322
2323
2324 IF (G_Flex_Qualifier_Name = 'GL_ACCOUNT') THEN
2325
2326 -- Case 1: Account segment overlay
2327 IF (i = G_flex_segment_num) THEN
2328 l_segments(i) := l_base_segments(i);
2329 ELSE
2330 l_segments(i) := l_overlay_segments(i);
2331 END IF;
2332
2333 ELSIF (G_Flex_Qualifier_Name = 'GL_BALANCING') THEN
2334
2335 -- Case 2: Balancing segment overlay
2336 IF (i = G_flex_segment_num) THEN
2337 l_segments(i) := l_overlay_segments(i);
2338 ELSE
2339 l_segments(i) := l_base_segments(i);
2340 END IF;
2341
2342 END IF;
2343
2344 END LOOP;
2345
2346 -- Get ccid fOR overlayed segments
2347 l_result := FND_FLEX_EXT.GET_COMBINATION_ID('SQLGL', 'GL#',
2348 l_chart_of_accts_id,
2349 P_accounting_date, l_num_of_segments,
2350 l_segments, P_result_ccid) ;
2351
2352 IF (NOT l_result) THEN
2353
2354 -- Store reason why flex failed
2355 P_result_ccid := -1;
2356 P_reason_unbuilt_flex := 'INVALID ACCOUNT';
2357
2358 END IF;
2359
2360 EXCEPTION
2361 WHEN OTHERS THEN
2362 IF (SQLCODE <> -20001) THEN
2363 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
2364 'CALLING_SEQUENCE', l_curr_calling_sequence,
2365 FALSE);
2366 END IF;
2367 APP_EXCEPTION.RAISE_EXCEPTION;
2368
2369 END Build_Offset_Account;
2370
2371 -----------------------------------------------------------------------------------------------------
2372 -- Function get_auto_offsets_segments returns either ACCOUNTING or BALANCING segment of the
2373 -- input ccid.
2374 --
2375 -- For e.g. Accounting Flexfield Structure is
2376 -- Balancing Segment- Cost Center - Account
2377 -- 100 - 213 - 3000
2378 --
2379 -- Case 1 : Auto-offsets to Balancing
2380 -- Function returns "100" i.e Balancing Segment
2381 --
2382 --
2383 -- Case 2 : Auto-offsets to Accounting
2384 -- Function returns "100213" i.e. Concatenated segments except Accounting Segment
2385 --
2386 --
2387 ----------------------------------------------------------------------------------------
2388 FUNCTION get_auto_offsets_segments
2389 (P_base_ccid IN NUMBER) return varchar2 is
2390
2391
2392 l_base_segments FND_FLEX_EXT.SEGMENTARRAY ;
2393 l_overlay_segments FND_FLEX_EXT.SEGMENTARRAY ;
2394 l_segments FND_FLEX_EXT.SEGMENTARRAY ;
2395 l_num_of_segments NUMBER ;
2396 l_result BOOLEAN ;
2397 l_curr_calling_sequence VARCHAR2(2000);
2398 G_flex_qualifier_name VARCHAR2(100);
2399 l_primary_sob_id AP_SYSTEM_PARAMETERS.set_of_books_id%TYPE;
2400 l_liability_post_lookup_code AP_SYSTEM_PARAMETERS.liability_post_lookup_code%TYPE;
2401 l_chart_of_accts_id GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE;
2402 G_flex_segment_num NUMBER;
2403 l_return_segments varchar2(200) := null;
2404
2405
2406 BEGIN
2407
2408 ----------------------------------------------------------------------------------------
2409 -- Get Set of Books and Auto-offsets Option info
2410
2411 SELECT set_of_books_id,
2412 nvl(liability_post_lookup_code, 'NONE')
2413 INTO l_primary_sob_id,
2414 l_liability_post_lookup_code
2415 FROM ap_system_parameters;
2416
2417 -----------------------------------------------------------------------------------------
2418 -- Get Chart of Accounts Information
2419
2420 SELECT chart_of_accounts_id
2421 INTO l_chart_of_accts_id
2422 FROM gl_sets_of_books
2423 WHERE set_of_books_id = l_primary_sob_id;
2424
2425 -----------------------------------------------------------------------------------------
2426 -- Get flexfield qualifier segment number
2427
2428 IF (l_Liability_Post_Lookup_Code = 'ACCOUNT_SEGMENT_VALUE') THEN
2429
2430 G_flex_qualifier_name := 'GL_ACCOUNT' ;
2431
2432 ELSIF (l_Liability_Post_Lookup_Code = 'BALANCING_SEGMENT') THEN
2433
2434 G_flex_qualifier_name := 'GL_BALANCING' ;
2435
2436 ELSIF (l_liability_post_lookup_code = 'NONE') then
2437
2438 return null;
2439
2440 END IF;
2441
2442 l_result := FND_FLEX_KEY_API.GET_SEG_ORDER_BY_QUAL_NAME(
2443 101, 'GL#',
2444 l_chart_of_accts_id,
2445 G_flex_qualifier_name,
2446 G_flex_segment_num);
2447
2448 -----------------------------------------------------------------------------------------
2449 -- Get the segments of the given account
2450
2451 IF (NOT FND_FLEX_EXT.GET_SEGMENTS('SQLGL', 'GL#',
2452 l_chart_of_accts_id,
2453 P_base_ccid, l_num_of_segments,
2454 l_base_segments)
2455 ) THEN
2456
2457 -- Print reason why flex failed
2458 --P_result_ccid := -1;
2459 --P_reason_unbuilt_flex := 'INVALID ACCOUNT';
2460 RETURN -1 ;
2461
2462 END IF;
2463
2464 ---------------------------------------------------------------------------------------
2465 -- Get the Balancing Segment or Accounting Segment based on the auto-offset option
2466
2467
2468 FOR i IN 1.. l_num_of_segments LOOP
2469
2470 IF (G_Flex_Qualifier_Name = 'GL_BALANCING') THEN
2471
2472 IF (i = G_flex_segment_num) THEN
2473 l_segments(i) := l_base_segments(i);
2474 l_return_segments := l_segments(i);
2475 END IF;
2476
2477 ELSIF (G_Flex_Qualifier_Name = 'GL_ACCOUNT') THEN
2478
2479 IF (i = G_flex_segment_num) THEN
2480 l_segments(i) := l_base_segments(i);
2481 ELSE
2482 l_segments(i) := l_base_segments(i);
2483 l_return_segments := l_return_segments || l_segments(i) ;
2484 END IF;
2485
2486 END IF;
2487
2488 END LOOP;
2489
2490 return l_return_segments;
2491
2492 EXCEPTION
2493 WHEN OTHERS THEN
2494 IF (SQLCODE <> -20001) THEN
2495 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
2496 'CALLING_SEQUENCE', l_curr_calling_sequence,
2497 FALSE);
2498 END IF;
2499 -- return l_number;
2500 APP_EXCEPTION.RAISE_EXCEPTION;
2501
2502
2503 END get_auto_offsets_segments;
2504
2505 -----------------------------------------------------------------------------------------------------
2506 -- This function is a modified version of the original one above.
2507 -- We modified it to accept more parameters but do less work.
2508 -- Created for bug 2475913.
2509 ----------------------------------------------------------------------------------------
2510 FUNCTION get_auto_offsets_segments
2511 (P_base_ccid IN NUMBER,
2512 P_flex_qualifier_name IN VARCHAR2,
2513 P_flex_segment_num IN NUMBER,
2514 P_chart_of_accts_id IN GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE
2515 ) return varchar2 is
2516
2517
2518 l_base_segments FND_FLEX_EXT.SEGMENTARRAY ;
2519 l_overlay_segments FND_FLEX_EXT.SEGMENTARRAY ;
2520 l_segments FND_FLEX_EXT.SEGMENTARRAY ;
2521 l_result BOOLEAN ;
2522 l_curr_calling_sequence VARCHAR2(2000);
2523 l_return_segments varchar2(200) := null;
2524 l_num_of_segments NUMBER;
2525
2526
2527 BEGIN
2528
2529 -----------------------------------------------------------------------------------------
2530 -- Get the segments of the given account
2531
2532 IF (NOT FND_FLEX_EXT.GET_SEGMENTS('SQLGL', 'GL#',
2533 P_chart_of_accts_id,
2534 P_base_ccid, l_num_of_segments,
2535 l_base_segments)
2536 ) THEN
2537
2538 -- Print reason why flex failed
2539 --P_result_ccid := -1;
2540 --P_reason_unbuilt_flex := 'INVALID ACCOUNT';
2541 RETURN -1 ;
2542
2543 END IF;
2544
2545 ---------------------------------------------------------------------------------------
2546 -- Get the Balancing Segment or Accounting Segment based on the auto-offset option
2547
2548
2549 FOR i IN 1.. l_num_of_segments LOOP
2550
2551 IF (P_Flex_Qualifier_Name = 'GL_BALANCING') THEN
2552
2553 IF (i = P_flex_segment_num) THEN
2554 l_segments(i) := l_base_segments(i);
2555 l_return_segments := l_segments(i);
2556 END IF;
2557
2558 ELSIF (P_Flex_Qualifier_Name = 'GL_ACCOUNT') THEN
2559
2560 IF (i = P_flex_segment_num) THEN
2561 l_segments(i) := l_base_segments(i);
2562 ELSE
2563 l_segments(i) := l_base_segments(i);
2564 l_return_segments := l_return_segments || l_segments(i) ;
2565 END IF;
2566
2567 END IF;
2568
2569 END LOOP;
2570
2571 return l_return_segments;
2572
2573 EXCEPTION
2574 WHEN OTHERS THEN
2575 IF (SQLCODE <> -20001) THEN
2576 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
2577 'CALLING_SEQUENCE', l_curr_calling_sequence,
2578 FALSE);
2579 END IF;
2580
2581 -- return l_number;
2582 APP_EXCEPTION.RAISE_EXCEPTION;
2583
2584
2585 END get_auto_offsets_segments;
2586
2587 FUNCTION delete_invoice_from_interface(p_invoice_id_table in number_table_type,
2588 p_invoice_line_id_table in number_table_type,
2589 p_calling_sequence VARCHAR2) return boolean as
2590
2591 current_calling_sequence VARCHAR2(2000);
2592 debug_info VARCHAR2(500);
2593
2594 BEGIN
2595 current_calling_sequence :=
2596 'AP_UTILITIES_PKG.delete_invoice_from_interface<-'||P_calling_sequence;
2597 debug_info := 'Delete records from rejection and interface tables';
2598
2599 /* Delete invoices from interface */
2600 forall i in nvl(p_invoice_id_table.first,0)..nvl(p_invoice_id_table.last,0)
2601 delete from ap_invoices_interface where invoice_id = p_invoice_id_table(i);
2602 /* Delete invoice lines from interface */
2603 forall i in nvl(p_invoice_id_table.first,0)..nvl(p_invoice_id_table.last,0)
2604 delete from ap_invoice_lines_interface where invoice_id = p_invoice_id_table(i);
2605
2606 /* Delete invoice rejections from the rejections table */
2607 forall i in nvl(p_invoice_id_table.first,0)..nvl(p_invoice_id_table.last,0)
2608 delete from ap_interface_rejections
2609 where parent_id = p_invoice_id_table(i) and
2610 parent_table = 'AP_INVOICES_INTERFACE';
2611
2612 /* Delete invoice lines rejections from the rejections table */
2613 forall i in nvl(p_invoice_line_id_table.first,0)..nvl(p_invoice_line_id_table.last,0)
2614 delete from ap_interface_rejections
2615 where parent_id = p_invoice_line_id_table(i) and
2616 parent_table = 'AP_INVOICE_LINES_INTERFACE';
2617 return TRUE;
2618 EXCEPTION
2619 WHEN OTHERS THEN
2620 if (SQLCODE <> -20001) then
2621 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
2622 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
2623 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
2624 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
2625 end if;
2626 RETURN(FALSE);
2627 END delete_invoice_from_interface ;
2628
2629 -- Added following function for Exchange Rate Calculation Project.
2630 FUNCTION calculate_user_xrate(P_invoice_curr IN VARCHAR2,
2631 P_base_curr IN VARCHAR2,
2632 P_exchange_date IN DATE,
2633 P_exchange_rate_type IN VARCHAR2)
2634 RETURN VARCHAR2
2635 IS
2636 l_calc_user_xrate VARCHAR2(1);
2637
2638 BEGIN
2639
2640 SELECT nvl(calc_user_xrate, 'N')
2641 INTO l_calc_user_xrate
2642 FROM ap_system_parameters;
2643
2644 IF (P_exchange_rate_type = 'User') THEN
2645 IF (gl_euro_user_rate_api.is_cross_rate(P_invoice_curr,
2646 P_base_curr,
2647 P_exchange_date,
2648 P_exchange_rate_type) = 'N') THEN
2649 RETURN l_calc_user_xrate;
2650 END IF;
2651 END IF;
2652
2653 RETURN 'N';
2654
2655 END calculate_user_xrate;
2656
2657 -- Added the following function to get the GL Batch Name
2658 -- Function Called by Posted Payment Register and Posted Invoice Register.
2659 -- Inputs : Batch Id , GL and Subledger Link Id
2660 -- Output : GL batch Name for that Batch Id.
2661 FUNCTION get_gl_batch_name(P_batch_id IN NUMBER, P_GL_SL_link_id IN NUMBER, P_ledger_id IN NUMBER)
2662 RETURN varchar2 AS
2663 ret_val varchar2(100);
2664 BEGIN
2665 IF P_batch_id = -999 and P_gl_sl_link_id is not NULL THEN
2666 BEGIN
2667 /* Definitely a Batch Name is avaialble with the LINK */
2668 SELECT jb.name INTO ret_val
2669 FROM gl_je_batches jb
2670 WHERE JB.je_batch_id IN (SELECT IR.je_batch_id
2671 FROM gl_import_references IR
2672 WHERE IR.gl_sl_link_id = P_GL_SL_link_id
2673 AND IR.gl_sl_link_table = 'APECL');
2674 EXCEPTION
2675 WHEN NO_DATA_FOUND THEN
2676 /* Link is available and No Batch Name then We don't
2677 want such rows */
2678 SELECT '-999' into ret_val
2679 FROM DUAL
2680 WHERE NOT EXISTS (SELECT 'this link id exists in IR'
2681 FROM gl_import_references IR
2682 WHERE IR.gl_sl_link_id=P_gl_sl_link_id
2683 AND IR.gl_sl_link_table = 'APECL');
2684 END;
2685 RETURN ret_val;
2686 ELSIF P_batch_id = -999 and P_gl_sl_link_id is NULL THEN
2687 /* No Link NO Batch, we would print such rows */
2688 return '-999';
2689 ELSE
2690 /* The Batch Name is Provided */
2691 SELECT jb.name INTO ret_val
2692 FROM gl_je_batches jb
2693 WHERE JB.je_batch_id = P_batch_id
2694 and P_GL_SL_link_id IN
2695 (SELECT IR.gl_sl_link_id
2696 FROM gl_import_references IR, gl_je_headers JEH
2697 WHERE IR.je_header_id = JEH.je_header_id
2698 AND JEH.ledger_id = P_Ledger_id
2699 AND JEH.je_batch_id = P_batch_id
2700 AND IR.gl_sl_link_table = 'APECL');
2701 RETURN ret_val;
2702 END IF;
2703 EXCEPTION
2704 WHEN OTHERS THEN
2705 return NULL;
2706 END get_gl_batch_name;
2707
2708 -- Bug 2249806
2709 -- Code modified by MSWAMINA
2710 -- Added a new stored function for the performance reasons
2711 -- This will get the Lookup code and the lookup type
2712 -- as the input arguments and will return the corresponding
2713 -- Meaning as output.
2714 -- This function is created inorder to avoid the reparsing
2715 -- of these simple/common SQLs in our reports.
2716
2717 FUNCTION FND_Get_Displayed_Field
2718 (LookupType IN VARCHAR2
2719 ,LookupCode IN VARCHAR2
2720 ) RETURN VARCHAR2 IS
2721
2722 CURSOR c_lookup is
2723 SELECT meaning
2724 FROM fnd_lookups
2725 WHERE (lookup_code = LookupCode)
2726 AND (lookup_type = LookupType);
2727 output_string fnd_lookups.meaning%TYPE;
2728 --
2729 BEGIN
2730 --
2731 open c_lookup;
2732 fetch c_lookup into output_string;
2733 --
2734 IF c_lookup%NOTFOUND THEN
2735 raise NO_DATA_FOUND;
2736 END IF;
2737 --
2738 close c_lookup;
2739 return(output_string);
2740
2741 EXCEPTION
2742 WHEN NO_DATA_FOUND THEN
2743 return NULL;
2744
2745 END FND_Get_Displayed_Field;
2746
2747 -- Bug 2693900. Forward porting Bug 2610252.
2748 --Bug2610252 The function get_reversal_gl_date is added to get
2749 --gl date for reversed distribution. If the date passed is not
2750 --in an open period then first day of the next open period will
2751 --be returned otherwise an error message will be displayed.
2752 -----------------------------------------------------------------------
2753 --function get_reversal_gl_date takes argument P_Date and
2754 --returns gl date
2755 -----------------------------------------------------------------------
2756 -- Bug 5584997. Added the P_org_id
2757 Function get_reversal_gl_date(P_date in date,
2758 P_Org_Id In Number) return date
2759 is
2760 l_open_gl_date date :='';
2761 l_period_name gl_period_statuses.period_name%TYPE := '';
2762 begin
2763 l_period_name := ap_utilities_pkg.get_current_gl_date(p_date, P_org_id);
2764 if l_period_name is null then
2765 ap_utilities_pkg.get_open_gl_date(p_date,
2766 l_period_name,
2767 l_open_gl_date,
2768 P_org_id);
2769 if l_period_name is null then
2770 RAISE NO_DATA_FOUND;
2771 end if;
2772 else
2773 l_open_gl_date := p_date;
2774 end if;
2775 return l_open_gl_date;
2776 EXCEPTION
2777 WHEN NO_DATA_FOUND THEN
2778 FND_MESSAGE.SET_NAME('SQLAP', 'AP_CANCEL_NO_OPEN_FUT_PERIOD');
2779 FND_MESSAGE.SET_TOKEN('DATE', to_char(p_date, 'dd-mon-yyyy'));
2780 APP_EXCEPTION.RAISE_EXCEPTION;
2781 End get_reversal_gl_date;
2782
2783 --Bug2610252 The function get_reversal_period is added to get
2784 --period for reversed distribution. If the date passed is not
2785 --in an open period then period name of the next open period will
2786 --be returned otherwise an error message will be displayed.
2787 -----------------------------------------------------------------------
2788 --function get_reversal_period takes argument P_Date and
2789 --returns period name
2790 -----------------------------------------------------------------------
2791 -- Bug 5584997. Added the P_org_id
2792 Function get_reversal_period(P_date in date,
2793 P_org_id In Number) return varchar2
2794 is
2795 l_open_gl_date date :='';
2796 l_period_name gl_period_statuses.period_name%TYPE := '';
2797 begin
2798 l_period_name := ap_utilities_pkg.get_current_gl_date(p_date, p_org_id);
2799 if l_period_name is null then
2800 ap_utilities_pkg.get_open_gl_date(p_date,
2801 l_period_name,
2802 l_open_gl_date,
2803 p_org_id);
2804 if l_period_name is null then
2805 RAISE NO_DATA_FOUND;
2806 end if;
2807 else
2808 l_open_gl_date := p_date;
2809 end if;
2810 return l_period_name;
2811 EXCEPTION
2812 WHEN NO_DATA_FOUND THEN
2813 FND_MESSAGE.SET_NAME('SQLAP', 'AP_CANCEL_NO_OPEN_FUT_PERIOD');
2814 FND_MESSAGE.SET_TOKEN('DATE', to_char(p_date, 'dd-mon-yyyy'));
2815 APP_EXCEPTION.RAISE_EXCEPTION;
2816 End get_reversal_period;
2817
2818 /* =======================================================================*/
2819 /* New Function pa_flexbuild was created for in the scope of the Invoice */
2820 /* Lines Project - Stage 1 */
2821 /* =======================================================================*/
2822
2823 FUNCTION pa_flexbuild(
2824 p_vendor_id IN NUMBER,
2825 p_employee_id IN NUMBER,
2826 p_set_of_books_id IN NUMBER,
2827 p_chart_of_accounts_id IN NUMBER,
2828 p_base_currency_code IN VARCHAR2,
2829 p_Accounting_date IN DATE,
2830 p_award_id IN NUMBER,
2831 P_project_id IN AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
2832 p_task_id IN AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
2833 p_expenditure_type IN
2834 AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
2835 p_expenditure_org_id IN
2836 AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
2837 p_expenditure_item_date IN
2838 AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ITEM_DATE%TYPE,
2839 p_employee_ccid IN NUMBER, --Bug5003249
2840 p_web_parameter_id IN NUMBER, --Bug5003249
2841 p_invoice_type_lookup_code IN VARCHAR2, --Bug5003249
2842 p_default_last_updated_by IN NUMBER,
2843 p_default_last_update_login IN NUMBER,
2844 p_pa_default_dist_ccid OUT NOCOPY NUMBER,
2845 p_pa_concatenated_segments OUT NOCOPY VARCHAR2,
2846 p_debug_Info OUT NOCOPY VARCHAR2,
2847 p_debug_Context OUT NOCOPY VARCHAR2,
2848 p_calling_sequence IN VARCHAR2,
2849 p_default_dist_ccid IN AP_INVOICE_LINES.DEFAULT_DIST_CCID%TYPE --bug 5386396
2850 ) RETURN BOOLEAN
2851 IS
2852 procedure_billable_flag VARCHAR2(60) := '';
2853 l_concat_ids VARCHAR2(200);
2854 l_errmsg VARCHAR2(200);
2855 l_concat_descrs VARCHAR2(500);
2856 l_concat_segs VARCHAR2(2000);
2857 current_calling_sequence VARCHAR2(2000);
2858 debug_info VARCHAR2(500);
2859
2860 l_pa_installed VARCHAR2(10);
2861 l_status VARCHAR2(10);
2862 l_industry VARCHAR2(10);
2863 l_attribute_category AP_INVOICES_ALL.attribute_category%TYPE;
2864 l_attribute1 AP_INVOICES_ALL.attribute1%TYPE;
2865 l_attribute2 AP_INVOICES_ALL.attribute2%TYPE;
2866 l_attribute3 AP_INVOICES_ALL.attribute3%TYPE;
2867 l_attribute4 AP_INVOICES_ALL.attribute4%TYPE;
2868 l_attribute5 AP_INVOICES_ALL.attribute5%TYPE;
2869 l_attribute6 AP_INVOICES_ALL.attribute6%TYPE;
2870 l_attribute7 AP_INVOICES_ALL.attribute7%TYPE;
2871 l_attribute8 AP_INVOICES_ALL.attribute8%TYPE;
2872 l_attribute9 AP_INVOICES_ALL.attribute9%TYPE;
2873 l_attribute10 AP_INVOICES_ALL.attribute10%TYPE;
2874 l_attribute11 AP_INVOICES_ALL.attribute11%TYPE;
2875 l_attribute12 AP_INVOICES_ALL.attribute12%TYPE;
2876 l_attribute13 AP_INVOICES_ALL.attribute13%TYPE;
2877 l_attribute14 AP_INVOICES_ALL.attribute14%TYPE;
2878 l_attribute15 AP_INVOICES_ALL.attribute15%TYPE;
2879 li_attribute_category AP_INVOICES_ALL.attribute_category%TYPE;
2880 li_attribute1 AP_INVOICE_LINES_ALL.attribute1%TYPE;
2881 li_attribute2 AP_INVOICE_LINES_ALL.attribute2%TYPE;
2882 li_attribute3 AP_INVOICE_LINES_ALL.attribute3%TYPE;
2883 li_attribute4 AP_INVOICE_LINES_ALL.attribute4%TYPE;
2884 li_attribute5 AP_INVOICE_LINES_ALL.attribute5%TYPE;
2885 li_attribute6 AP_INVOICE_LINES_ALL.attribute6%TYPE;
2886 li_attribute7 AP_INVOICE_LINES_ALL.attribute7%TYPE;
2887 li_attribute8 AP_INVOICE_LINES_ALL.attribute8%TYPE;
2888 li_attribute9 AP_INVOICE_LINES_ALL.attribute9%TYPE;
2889 li_attribute10 AP_INVOICE_LINES_ALL.attribute10%TYPE;
2890 li_attribute11 AP_INVOICE_LINES_ALL.attribute11%TYPE;
2891 li_attribute12 AP_INVOICE_LINES_ALL.attribute12%TYPE;
2892 li_attribute13 AP_INVOICE_LINES_ALL.attribute13%TYPE;
2893 li_attribute14 AP_INVOICE_LINES_ALL.attribute14%TYPE;
2894 li_attribute15 AP_INVOICE_LINES_ALL.attribute15%TYPE;
2895
2896 BEGIN
2897
2898 -- Update the calling sequence
2899 --
2900 current_calling_sequence :=
2901 'AP_IMPORT_UTILITIES_PKG.pa_flexbuild<-'||P_calling_sequence;
2902
2903 --------------------------------------------------------------------------
2904 -- Step 1 - Flexbuild
2905 --------------------------------------------------------------------------
2906
2907 -- Flexbuild using Workflow.
2908
2909 debug_info := '(PA Flexbuild 1) Call pa_acc_gen_wf_pkg.'||
2910 'ap_inv_generate_account for flexbuilding';
2911 --Bug5003249 Start
2912 If (p_invoice_type_lookup_code = 'EXPENSE REPORT') then
2913 IF ( NOT pa_acc_gen_wf_pkg.ap_er_generate_account (
2914 p_project_id => p_project_id,
2915 p_task_id => p_task_id,
2916 p_expenditure_type => p_expenditure_type,
2917 p_vendor_id => P_VENDOR_ID,
2918 p_expenditure_organization_id => P_EXPENDITURE_ORG_ID,
2919 p_expenditure_item_date => P_EXPENDITURE_ITEM_DATE,
2920 p_billable_flag => procedure_billable_flag,
2921 p_chart_of_accounts_id => P_CHART_OF_ACCOUNTS_ID,
2922 p_calling_module => 'APXINWKB',
2923 p_employee_id => P_employee_id,
2924 p_employee_ccid => p_employee_ccid,
2925 p_expense_type => p_web_parameter_id,
2926 p_expense_cc => null,
2927 P_ATTRIBUTE_CATEGORY => LI_ATTRIBUTE_CATEGORY,
2928 P_ATTRIBUTE1 => LI_ATTRIBUTE1,
2929 P_ATTRIBUTE2 => LI_ATTRIBUTE2,
2930 P_ATTRIBUTE3 => LI_ATTRIBUTE3,
2931 P_ATTRIBUTE4 => LI_ATTRIBUTE4,
2932 P_ATTRIBUTE5 => LI_ATTRIBUTE5,
2933 P_ATTRIBUTE6 => LI_ATTRIBUTE6,
2934 P_ATTRIBUTE7 => LI_ATTRIBUTE7,
2935 P_ATTRIBUTE8 => LI_ATTRIBUTE8,
2936 P_ATTRIBUTE9 => LI_ATTRIBUTE9,
2937 P_ATTRIBUTE10 => LI_ATTRIBUTE10,
2938 P_ATTRIBUTE11 => LI_ATTRIBUTE11,
2939 P_ATTRIBUTE12 => LI_ATTRIBUTE12,
2940 P_ATTRIBUTE13 => LI_ATTRIBUTE13,
2941 P_ATTRIBUTE14 => LI_ATTRIBUTE14,
2942 P_ATTRIBUTE15 => LI_ATTRIBUTE15,
2943 P_LINE_ATTRIBUTE_CATEGORY => L_ATTRIBUTE_CATEGORY,
2944 P_LINE_ATTRIBUTE1 => L_ATTRIBUTE1,
2945 P_LINE_ATTRIBUTE2 => L_ATTRIBUTE2,
2946 P_LINE_ATTRIBUTE3 => L_ATTRIBUTE3,
2947 P_LINE_ATTRIBUTE4 => L_ATTRIBUTE4,
2948 P_LINE_ATTRIBUTE5 => L_ATTRIBUTE5,
2949 P_LINE_ATTRIBUTE6 => L_ATTRIBUTE6,
2950 P_LINE_ATTRIBUTE7 => L_ATTRIBUTE7,
2951 P_LINE_ATTRIBUTE8 => L_ATTRIBUTE8,
2952 P_LINE_ATTRIBUTE9 => L_ATTRIBUTE9,
2953 P_LINE_ATTRIBUTE10 => L_ATTRIBUTE10,
2954 P_LINE_ATTRIBUTE11 => L_ATTRIBUTE11,
2955 P_LINE_ATTRIBUTE12 => L_ATTRIBUTE12,
2956 P_LINE_ATTRIBUTE13 => L_ATTRIBUTE13,
2957 P_LINE_ATTRIBUTE14 => L_ATTRIBUTE14,
2958 P_LINE_ATTRIBUTE15 => L_ATTRIBUTE15,
2959 x_return_ccid => P_PA_DEFAULT_DIST_CCID,
2960 x_concat_segs => l_concat_segs,
2961 x_concat_ids => l_concat_ids,
2962 x_concat_descrs => l_concat_descrs,
2963 x_error_message => l_errmsg,
2964 P_award_id => P_AWARD_ID, --Bug5198018
2965 p_input_ccid => p_default_dist_ccid --bug 5386396
2966 ))THEN
2967 --
2968 -- Show error message
2969 --
2970 debug_info :=
2971 '(PA Flexbuild 1 ) pa_acc_gen_wf_pkg.ap_er_generate_account Failed ';
2972 p_debug_info := debug_info || ': Error encountered';
2973 p_debug_Context := current_calling_sequence;
2974 RETURN(FALSE);
2975
2976 END IF;
2977 else
2978
2979 IF ( NOT pa_acc_gen_wf_pkg.ap_inv_generate_account (
2980 p_project_id => p_project_id,
2981 p_task_id => p_task_id,
2982 P_AWARD_ID => p_award_id, --Bug5198018
2983 p_expenditure_type => p_expenditure_type,
2984 p_vendor_id => p_vendor_id,
2985 p_expenditure_organization_id => p_expenditure_org_id,
2986 p_expenditure_item_date => p_expenditure_item_date,
2987 p_billable_flag => procedure_billable_flag,
2988 p_chart_of_accounts_id => P_chart_of_accounts_id,
2989 p_accounting_date => P_accounting_date,
2990 P_ATTRIBUTE_CATEGORY => li_attribute_category,
2991 P_ATTRIBUTE1 => li_attribute1,
2992 P_ATTRIBUTE2 => li_attribute2,
2993 P_ATTRIBUTE3 => li_attribute3,
2994 P_ATTRIBUTE4 => li_attribute4,
2995 P_ATTRIBUTE5 => li_attribute5,
2996 P_ATTRIBUTE6 => li_attribute6,
2997 P_ATTRIBUTE7 => li_attribute7,
2998 P_ATTRIBUTE8 => li_attribute8,
2999 P_ATTRIBUTE9 => li_attribute9,
3000 P_ATTRIBUTE10 => li_attribute10,
3001 P_ATTRIBUTE11 => li_attribute11,
3002 P_ATTRIBUTE12 => li_attribute12,
3003 P_ATTRIBUTE13 => li_attribute13,
3004 P_ATTRIBUTE14 => li_attribute14,
3005 P_ATTRIBUTE15 => li_attribute15,
3006 P_DIST_ATTRIBUTE_CATEGORY => l_attribute_category,
3007 P_DIST_ATTRIBUTE1 => l_attribute1,
3008 P_DIST_ATTRIBUTE2 => l_attribute2,
3009 P_DIST_ATTRIBUTE3 => l_attribute3,
3010 P_DIST_ATTRIBUTE4 => l_attribute4,
3011 P_DIST_ATTRIBUTE5 => l_attribute5,
3012 P_DIST_ATTRIBUTE6 => l_attribute6,
3013 P_DIST_ATTRIBUTE7 => l_attribute7,
3014 P_DIST_ATTRIBUTE8 => l_attribute8,
3015 P_DIST_ATTRIBUTE9 => l_attribute9,
3016 P_DIST_ATTRIBUTE10 => l_attribute10,
3017 P_DIST_ATTRIBUTE11 => l_attribute11,
3018 P_DIST_ATTRIBUTE12 => l_attribute12,
3019 P_DIST_ATTRIBUTE13 => l_attribute13,
3020 P_DIST_ATTRIBUTE14 => l_attribute14,
3021 P_DIST_ATTRIBUTE15 => l_attribute15,
3022 x_return_ccid => P_PA_DEFAULT_DIST_CCID, -- OUT
3023 x_concat_segs => l_concat_segs, -- OUT NOCOPY
3024 x_concat_ids => l_concat_ids, -- OUT NOCOPY
3025 x_concat_descrs => l_concat_descrs, -- OUT NOCOPY
3026 x_error_message => l_errmsg)) THEN -- OUT NOCOPY
3027
3028 -- Show error message
3029 debug_info :=
3030 '(PA Flexbuild 1 ) pa_acc_gen_wf_pkg.ap_inv_generate_account Failed ';
3031 p_debug_info := debug_info || ': Error encountered';
3032 p_debug_Context := current_calling_sequence;
3033 RETURN(FALSE);
3034 END IF;
3035 End if;
3036
3037 -------------------------------------------------------------------------
3038 -- Step 2 - Return Concatenated Segments
3039 --------------------------------------------------------------------------
3040 debug_info := '(PA Flexbuild 2) Return Concatenated Segments';
3041 P_PA_CONCATENATED_SEGMENTS := l_concat_segs;
3042 debug_info :=
3043 'p_pa_default_dist_ccid = '||to_char(p_pa_default_dist_ccid)
3044 ||' p_pa_concatenated_segments = '||p_pa_concatenated_segments
3045 ||' l_concat_segs = ' ||l_concat_segs
3046 ||' l_concat_ids = ' ||l_concat_ids
3047 ||' procedure_billable_flag = ' ||procedure_billable_flag
3048 ||' l_concat_descrs = ' ||l_concat_descrs
3049 ||' l_errmsg = ' ||l_errmsg;
3050
3051 RETURN(TRUE);
3052
3053 EXCEPTION
3054 WHEN OTHERS THEN
3055 p_debug_info := debug_info || ': Error encountered in Flexbuild';
3056 p_debug_Context := current_calling_sequence;
3057
3058 IF (SQLCODE < 0) then
3059 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3060 AP_IMPORT_UTILITIES_PKG.print(
3061 AP_IMPORT_INVOICES_PKG.g_debug_switch, SQLERRM);
3062 END IF;
3063 END IF;
3064
3065 RETURN (FALSE);
3066 END pa_flexbuild;
3067
3068 PROCEDURE Get_Invoice_LE (
3069 p_vendor_site_id IN NUMBER,
3070 p_inv_liab_ccid IN NUMBER,
3071 p_org_id IN NUMBER,
3072 p_le_id OUT NOCOPY NUMBER) IS
3073
3074 l_ptop_le_info XLE_BUSINESSINFO_GRP.ptop_le_rec;
3075 l_le_return_status varchar2(1);
3076 l_msg_data varchar2(1000);
3077
3078 l_bill_to_location_id NUMBER(15);
3079 l_supp_site_liab_ccid NUMBER(15);
3080 l_ccid_to_api NUMBER(15);
3081 l_valid_le VARCHAR2(100);
3082
3083 BEGIN
3084 -- Get Bill TO Location and Liab Acct from Supplier Site
3085 BEGIN
3086 SELECT bill_to_location_id,
3087 accts_pay_code_combination_id
3088 INTO l_bill_to_location_id,
3089 l_supp_site_liab_ccid
3090 FROM po_vendor_sites
3091 WHERE vendor_site_id = p_vendor_site_id;
3092
3093 l_ccid_to_api := NVL(p_inv_liab_ccid,
3094 l_supp_site_liab_ccid);
3095 EXCEPTION
3096 WHEN OTHERS THEN
3097 l_bill_to_location_id := NULL;
3098 l_ccid_to_api := p_inv_liab_ccid;
3099 END;
3100 --
3101 -- Call LE API
3102 XLE_BUSINESSINFO_GRP.Get_PurchasetoPay_Info
3103 (l_le_return_status,
3104 l_msg_data,
3105 null,
3106 null,
3107 l_bill_to_location_id,
3108 l_ccid_to_api,
3109 p_org_id,
3110 l_ptop_le_info);
3111
3112 IF (l_le_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3113 p_le_id := l_ptop_le_info.legal_entity_id;
3114 END IF;
3115
3116 EXCEPTION
3117 WHEN OTHERS THEN
3118 APP_EXCEPTION.RAISE_EXCEPTION;
3119 END Get_Invoice_LE;
3120
3121 FUNCTION Get_Check_LE (
3122 p_bank_acct_use_id IN NUMBER)
3123 RETURN NUMBER IS
3124 l_legal_entity_id NUMBER;
3125 BEGIN
3126
3127 SELECT account_owner_org_id
3128 INTO l_legal_entity_id
3129 FROM ce_bank_accounts cba,
3130 ce_bank_acct_uses_all cbau
3131 WHERE cbau.bank_account_id = cba.bank_account_id
3132 AND cbau.bank_acct_use_id = p_bank_acct_use_id;
3133
3134 RETURN (l_legal_entity_id);
3135
3136 EXCEPTION
3137 WHEN OTHERS THEN
3138 APP_EXCEPTION.RAISE_EXCEPTION;
3139 END Get_Check_LE;
3140
3141 /*==========================================================
3142 | PROCEDURE - getInvoiceLEInfo
3143 | This is a wrapper for get_invoice_le()
3144 | with more detailed L.E. info returned
3145 *=========================================================*/
3146 PROCEDURE getInvoiceLEInfo (
3147 p_vendor_site_id IN NUMBER,
3148 p_inv_liab_ccid IN NUMBER,
3149 p_org_id IN NUMBER,
3150 p_le_id OUT NOCOPY NUMBER,
3151 p_le_name OUT NOCOPY VARCHAR2,
3152 p_le_registration_num OUT NOCOPY VARCHAR2,
3153 p_le_address1 OUT NOCOPY VARCHAR2,
3154 p_le_city OUT NOCOPY VARCHAR2,
3155 p_le_postal_code OUT NOCOPY VARCHAR2,
3156 p_le_country OUT NOCOPY VARCHAR2) IS
3157
3158 l_ptop_le_info XLE_BUSINESSINFO_GRP.ptop_le_rec;
3159 l_le_return_status varchar2(1);
3160 l_msg_data varchar2(1000);
3161
3162 l_bill_to_location_id NUMBER(15);
3163 l_supp_site_liab_ccid NUMBER(15);
3164 l_ccid_to_api NUMBER(15);
3165 l_valid_le VARCHAR2(100);
3166
3167 BEGIN
3168 -- Get Bill TO Location and Liab Acct from Supplier Site
3169 BEGIN
3170 SELECT bill_to_location_id,
3171 accts_pay_code_combination_id
3172 INTO l_bill_to_location_id,
3173 l_supp_site_liab_ccid
3174 FROM po_vendor_sites
3175 WHERE vendor_site_id = p_vendor_site_id;
3176
3177 l_ccid_to_api := NVL(p_inv_liab_ccid,
3178 l_supp_site_liab_ccid);
3179 EXCEPTION
3180 WHEN OTHERS THEN
3181 l_bill_to_location_id := NULL;
3182 l_ccid_to_api := p_inv_liab_ccid;
3183 END;
3184 --
3185 -- Call LE API
3186 XLE_BUSINESSINFO_GRP.Get_PurchasetoPay_Info
3187 (l_le_return_status,
3188 l_msg_data,
3189 null,
3190 null,
3191 l_bill_to_location_id,
3192 l_ccid_to_api,
3193 p_org_id,
3194 l_ptop_le_info);
3195
3196 IF (l_le_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3197 p_le_id := l_ptop_le_info.legal_entity_id;
3198 p_le_name := l_ptop_le_info.name;
3199 p_le_registration_num := l_ptop_le_info.registration_number;
3200 -- p_le_party_id := l_ptop_le_info.party_id;
3201 p_le_address1 := l_ptop_le_info.address_line_1;
3202 p_le_city := l_ptop_le_info.town_or_city;
3203 p_le_postal_code := l_ptop_le_info.postal_code;
3204 p_le_country := l_ptop_le_info.country;
3205 END IF;
3206
3207 EXCEPTION
3208 WHEN OTHERS THEN
3209 APP_EXCEPTION.RAISE_EXCEPTION;
3210 END getInvoiceLEInfo;
3211
3212 PROCEDURE Delete_AP_Profiles
3213 (P_Profile_Option_Name IN VARCHAR2)
3214 IS
3215 BEGIN
3216 FND_PROFILE_OPTIONS_PKG.Delete_Row(P_Profile_Option_Name);
3217 EXCEPTION
3218 WHEN NO_DATA_FOUND THEN
3219 NULL;
3220 WHEN OTHERS THEN
3221 NULL;
3222 END Delete_AP_Profiles;
3223
3224 /*
3225 3881457 : Create new function that returns the closing status of the
3226 period that the input date is in.
3227 */
3228 FUNCTION PERIOD_STATUS (p_gl_date IN DATE)
3229 RETURN VARCHAR2 IS
3230
3231 CURSOR c_input_period IS
3232 SELECT GLPS.closing_status
3233 FROM gl_period_statuses GLPS,
3234 ap_system_parameters SP
3235 WHERE GLPS.application_id = 200
3236 AND GLPS.set_of_books_id = SP.set_of_books_id
3237 AND TRUNC(p_gl_date) BETWEEN GLPS.start_date AND GLPS.end_date
3238 AND NVL(GLPS.adjustment_period_flag, 'N') = 'N';
3239
3240 v_closing_status GL_PERIOD_STATUSES.CLOSING_STATUS%TYPE;
3241
3242 BEGIN
3243
3244 OPEN c_input_period;
3245
3246 FETCH c_input_period
3247 INTO v_closing_status;
3248
3249 CLOSE c_input_period;
3250
3251 RETURN v_closing_status;
3252
3253 END PERIOD_STATUS;
3254
3255
3256 PROCEDURE clob_to_file
3257 (p_xml_clob IN CLOB) IS
3258
3259 l_clob_size NUMBER;
3260 l_offset NUMBER;
3261 l_chunk_size INTEGER;
3262 l_chunk VARCHAR2(32767);
3263 l_log_module VARCHAR2(240);
3264
3265 BEGIN
3266
3267
3268 l_clob_size := dbms_lob.getlength(p_xml_clob);
3269
3270 IF (l_clob_size = 0) THEN
3271 RETURN;
3272 END IF;
3273
3274 l_offset := 1;
3275 l_chunk_size := 3000;
3276
3277 WHILE (l_clob_size > 0) LOOP
3278 l_chunk := dbms_lob.substr (p_xml_clob, l_chunk_size, l_offset);
3279 fnd_file.put
3280 (which => fnd_file.output
3281 ,buff => l_chunk);
3282
3283 l_clob_size := l_clob_size - l_chunk_size;
3284 l_offset := l_offset + l_chunk_size;
3285 END LOOP;
3286
3287 fnd_file.new_line(fnd_file.output,1);
3288
3289 EXCEPTION
3290 WHEN OTHERS THEN
3291 APP_EXCEPTION.RAISE_EXCEPTION;
3292
3293 END clob_to_file;
3294
3295 FUNCTION pa_period_status(
3296 p_gl_date IN DATE,
3297 p_org_id IN number default
3298 mo_global.get_current_org_id) RETURN varchar2 IS
3299
3300 CURSOR c_closing_status IS
3301 SELECT GLPS.closing_status
3302 FROM gl_period_statuses GLPS,
3303 ap_system_parameters SP
3304 WHERE GLPS.application_id = 8721
3305 AND SP.org_id = P_Org_Id
3306 AND GLPS.set_of_books_id = SP.set_of_books_id
3307 AND TRUNC(p_gl_date) BETWEEN GLPS.start_date AND GLPS.end_date
3308 AND NVL(GLPS.adjustment_period_flag, 'N') = 'N';
3309
3310 v_closing_status GL_PERIOD_STATUSES.CLOSING_STATUS%TYPE;
3311
3312 BEGIN
3313 OPEN c_closing_status;
3314
3315 FETCH c_closing_status
3316 INTO v_closing_status;
3317
3318 CLOSE c_closing_status;
3319
3320 RETURN v_closing_status;
3321 END pa_period_status;
3322
3323 /*============================================================================
3324 | FUNCTION - Get_PO_REVERSED_ENCUMB_AMOUNT
3325 |
3326 | DESCRIPTION
3327 | fetch the amount of PO encumbrance reversed against the given PO
3328 | distribution from all invoices for a given date range in functional
3329 | currency. Calculation includes PO encumbrance which are in GL only.
3330 | In case Invoice encumbrance type is the same as PO encumbrance, we
3331 | need to exclude the variance.
3332 | it returns actual amount or 0 if there is po reversed encumbrance
3333 | line existing, otherwise returns NULL.
3334 |
3335 | This function is only applicable to pre 11i and 11i data. Due to R12
3336 | new funds/encumbrance solution. AP will be maintaining this function
3337 | for pre-upgrade data because of PSA team decide no upgrade for
3338 | AP_ENCUMBRANCE_LINES_ALL table.
3339 |
3340 | PARAMETERS
3341 | P_Po_distribution_id - po_distribution_id (in)
3342 | P_Start_date - Start gl date (in)
3343 | P_End_date - End gl date (in)
3344 | P_Calling_Sequence - debug usage
3345 |
3346 | KNOWN ISSUES:
3347 |
3348 | NOTES:
3349 |
3350 | 1. In case user changes the purchase order encumbrance
3351 | type or Invoice encumbrance type after invoice is
3352 | validated, this API might not return a valid value.
3353 |
3354 | MODIFICATION HISTORY
3355 | Date Author Description of Change
3356 | 2-18-06 sfeng move function to different pkg and
3357 | back out the change of bug 3851654
3358 |
3359 *==========================================================================*/
3360
3361 FUNCTION Get_PO_Reversed_Encumb_Amount(
3362 P_Po_Distribution_Id IN NUMBER,
3363 P_Start_gl_Date IN DATE,
3364 P_End_gl_Date IN DATE,
3365 P_Calling_Sequence IN VARCHAR2 DEFAULT NULL)
3366
3367 RETURN NUMBER
3368 IS
3369
3370 l_current_calling_sequence VARCHAR2(2000);
3371 l_procedure_name CONSTANT VARCHAR2(60) := 'Get_PO_Reversed_Encumb_Amount';
3372 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3373
3374 l_unencumbered_amount NUMBER;
3375 l_upg_unencumbered_amount NUMBER;
3376 l_total_unencumbered_amount NUMBER;
3377
3378 CURSOR po_enc_reversed_cur IS
3379 SELECT sum(nvl(ael.accounted_cr,0) - nvl(ael.accounted_dr,0) )
3380 FROM AP_INVOICE_DISTRIBUTIONS aid,
3381 AP_ENCUMBRANCE_LINES ael,
3382 financials_system_parameters fsp
3383 WHERE aid.po_distribution_id = P_po_distribution_id
3384 AND aid.invoice_distribution_id = ael.invoice_distribution_id
3385 AND ( ( p_start_gl_date is not null
3386 and p_start_gl_date <= ael.accounting_date ) or
3387 ( p_start_gl_date is null ) )
3388 AND ( (p_end_gl_date is not null
3389 and p_end_gl_date >= ael.accounting_date ) or
3390 (p_end_gl_date is null ) )
3391 AND ael.encumbrance_line_type not in ('IPV', 'ERV', 'QV','AV')
3392 AND nvl(aid.org_id,-1) = nvl(fsp.org_id,-1)
3393 AND ael.encumbrance_type_id = fsp.purch_encumbrance_type_id;
3394
3395
3396 -- Bug 7004146, added the condition on the historical flag
3397 -- and line types, to make sure that only the pre-11i
3398 -- distributions are picked up.
3399 CURSOR upgraded_po_enc_rev_cur IS
3400 SELECT sum (nvl(nvl(aid.base_amount,aid.amount),0) -
3401 nvl(aid.base_invoice_price_variance ,0) -
3402 nvl(aid.exchange_rate_variance,0) -
3403 nvl(aid.base_quantity_variance,0))
3404 FROM ap_invoice_distributions aid,
3405 po_distributions pd,
3406 financials_system_parameters fs
3407 where aid.po_distribution_id = p_po_distribution_id
3408 and aid.po_distribution_id = pd.po_distribution_id
3409 and nvl(aid.org_id,-1) = nvl(fs.org_id,-1)
3410 and fs.inv_encumbrance_type_id <> fs.purch_encumbrance_type_id
3411 and NVL(PD.accrue_on_receipt_flag,'N') = 'N'
3412 AND AID.po_distribution_id is not null
3413 AND nvl(aid.match_status_flag, 'N') = 'A'
3414 AND nvl(aid.encumbered_flag, 'N') = 'Y'
3415 AND nvl(aid.historical_flag, 'N') = 'Y'
3416 AND aid.line_type_lookup_code NOT IN ('IPV', 'ERV', 'TIPV', 'TERV', 'TRV', 'QV', 'AV')
3417 AND (aid.accrual_posted_flag = 'Y' or aid.cash_posted_flag = 'Y')
3418 AND (( p_start_gl_date is not null and p_start_gl_date <= aid.accounting_date) or (p_start_gl_date is null))
3419 AND ((p_end_gl_date is not null and p_end_gl_date >= aid.accounting_date) or (p_end_gl_date is null))
3420 AND NOT EXISTS (SELECT 'release 11.5 encumbrance'
3421 from ap_encumbrance_lines_all ael
3422 where ael.invoice_distribution_id = aid.invoice_distribution_id)
3423 -- bug 7225570
3424 AND aid.bc_event_id is null
3425 AND NOT EXISTS (SELECT 'release 11.5 encumbrance tax'
3426 from ap_encumbrance_lines_all ael
3427 where ael.invoice_distribution_id = aid.charge_applicable_to_dist_id);
3428
3429 BEGIN
3430
3431 l_current_calling_sequence := 'AP_UTILITIES_PKG.'
3432 || 'Get_PO_Reversed_Encumb_Amount<-'
3433 || P_calling_sequence;
3434
3435 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3436
3437 l_log_msg := 'Begin of procedure '|| l_procedure_name;
3438 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3439 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3440 G_MODULE_NAME||l_procedure_name||'.begin',
3441 l_log_msg);
3442 END IF;
3443
3444 -----------------------------------------------------------
3445 l_log_msg := 'Start to Open the po_encumbrance_cur' ;
3446 -----------------------------------------------------------
3447 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3448 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3449 G_MODULE_NAME||l_procedure_name||'.begin',
3450 l_log_msg);
3451 END IF;
3452
3453
3454 OPEN po_enc_reversed_cur;
3455 FETCH po_enc_reversed_cur INTO
3456 l_unencumbered_amount;
3457
3458 IF (po_enc_reversed_cur%NOTFOUND) THEN
3459 -----------------------------------------------------------
3460 l_log_msg := 'NO encumbrance line exists' ;
3461 -----------------------------------------------------------
3462 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3463 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3464 G_MODULE_NAME||l_procedure_name||'.begin',
3465 l_log_msg);
3466 END IF;
3467
3468 l_unencumbered_amount := NULL;
3469 END IF;
3470
3471 CLOSE po_enc_reversed_cur;
3472
3473 -----------------------------------------------------------
3474 l_log_msg := 'close the cursor po_enc_reversed_cur' ;
3475 -----------------------------------------------------------
3476 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3477 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3478 G_MODULE_NAME||l_procedure_name||'.begin',
3479 l_log_msg);
3480 END IF;
3481
3482
3483 OPEN upgraded_po_enc_rev_cur;
3484 -----------------------------------------------------------
3485 l_log_msg := 'Open upgraded_po_enc_rev_cur' ;
3486 -----------------------------------------------------------
3487 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3488 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3489 G_MODULE_NAME||l_procedure_name||'.begin',
3490 l_log_msg);
3491 END IF;
3492
3493 FETCH upgraded_po_enc_rev_cur INTO
3494 l_upg_unencumbered_amount;
3495
3496 IF (upgraded_po_enc_rev_cur%NOTFOUND) THEN
3497 -----------------------------------------------------------
3498 l_log_msg := 'NO upgraded encumbrance reversals exist' ;
3499 -----------------------------------------------------------
3500 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3501 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3502 G_MODULE_NAME||l_procedure_name||'.begin',
3503 l_log_msg);
3504 END IF;
3505
3506 l_upg_unencumbered_amount := NULL;
3507 END IF;
3508
3509 CLOSE upgraded_po_enc_rev_cur;
3510
3511
3512 IF (l_unencumbered_amount is not null or l_upg_unencumbered_amount is not null) THEN
3513 l_total_unencumbered_amount := nvl(l_unencumbered_amount,0) + nvl(l_upg_unencumbered_amount,0);
3514 ELSE
3515 l_total_unencumbered_amount := NULL;
3516 END IF;
3517
3518 RETURN (l_total_unencumbered_amount);
3519
3520 EXCEPTION
3521 WHEN OTHERS THEN
3522
3523 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
3524 FND_LOG.STRING(G_LEVEL_EXCEPTION,
3525 G_MODULE_NAME || l_procedure_name,
3526 'EXCEPTION');
3527 END IF;
3528
3529 IF ( po_enc_reversed_cur%ISOPEN ) THEN
3530 CLOSE po_enc_reversed_cur;
3531 END IF;
3532
3533 IF ( upgraded_po_enc_rev_cur%ISOPEN ) THEN
3534 CLOSE upgraded_po_enc_rev_cur;
3535 END IF;
3536
3537 RAISE;
3538 END Get_PO_Reversed_Encumb_Amount;
3539
3540 /* Bug 5572876. Asset Book for Ledger is cached */
3541 Function Ledger_Asset_Book (P_ledger_id IN Number)
3542 Return Varchar2
3543 IS
3544 l_asset_book fa_book_controls.book_type_code%TYPE;
3545
3546 BEGIN
3547
3548
3549 If g_asset_book_code_t.count > 0 Then
3550
3551 If g_asset_book_code_t.exists(p_ledger_id) Then
3552
3553 l_asset_book := g_asset_book_code_t(p_ledger_id).asset_book_code;
3554
3555 Else
3556
3557 Begin
3558 SELECT book_type_code
3559 INTO l_asset_book
3560 FROM fa_book_controls fc
3561 WHERE fc.book_class = 'CORP0RATE'
3562 AND fc.set_of_books_id = p_ledger_id
3563 AND fc.date_ineffective IS NULL;
3564 Exception
3565 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
3566 l_asset_book := NULL;
3567 End;
3568
3569 g_asset_book_code_t(p_ledger_id).asset_book_code := l_asset_book;
3570
3571 End If;
3572
3573 Else
3574
3575 Begin
3576 SELECT book_type_code
3577 INTO l_asset_book
3578 FROM fa_book_controls fc
3579 WHERE fc.book_class = 'CORP0RATE'
3580 AND fc.set_of_books_id = p_ledger_id
3581 AND fc.date_ineffective IS NULL;
3582 Exception
3583 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
3584 l_asset_book := NULL;
3585 End;
3586
3587 g_asset_book_code_t(p_ledger_id).asset_book_code := l_asset_book;
3588
3589 End If;
3590
3591 Return (l_asset_book);
3592
3593 End Ledger_Asset_Book;
3594
3595
3596 --Function Get_CCR_Status, added for the R12 FSIO gap--
3597 --bug6053476
3598
3599 FUNCTION get_ccr_status(P_object_id IN NUMBER,
3600 P_object_type IN VARCHAR2
3601 )
3602 return VARCHAR2 IS
3603
3604 l_return_status VARCHAR2(255);
3605 l_msg_count NUMBER;
3606 l_msg_data VARCHAR2(255);
3607 l_ccr_id NUMBER;
3608 l_out_status VARCHAR2(1);
3609 l_error_code NUMBER;
3610
3611 BEGIN
3612
3613 FV_CCR_GRP.fv_is_ccr(
3614 p_api_version => 1.0,
3615 p_init_msg_list => FND_API.G_FALSE,
3616 P_object_id => P_object_id,
3617 P_object_type => P_object_type,
3618 x_return_status => l_return_status,
3619 x_msg_count => l_msg_count,
3620 x_msg_data => l_msg_data,
3621 x_ccr_id => l_ccr_id,
3622 x_out_status => l_out_status,
3623 x_error_code => l_error_code
3624 );
3625
3626 IF l_out_status is not Null THEN
3627 Return l_out_status;
3628 ELSE
3629 Return 'F';
3630 END IF;
3631
3632 EXCEPTION
3633 When Others Then
3634 Return 'F';
3635 END get_ccr_status;
3636
3637 /*--------------------------------------------------------------------------
3638 * * Function get_gl_natural_account
3639 * * Input parameters:
3640 * * p_coa_id: Chart of Accounts ID
3641 * * p_ccid: Code Combination ID
3642 * * This function returns the value of the natural segment
3643 * * of a CCID that is passed as input parameter to it.
3644 * *
3645 * * Remarks: Bug 6980939 - Added the Function.
3646 * *------------------------------------------------------------------------*/
3647
3648 FUNCTION get_gl_natural_account(
3649 p_coa_id IN NUMBER,
3650 p_ccid IN NUMBER,
3651 P_calling_sequence IN VARCHAR2 DEFAULT NULL
3652 )
3653 RETURN VARCHAR2 IS
3654
3655 l_success BOOLEAN;
3656 l_segment_num VARCHAR2(15);
3657 l_nat_account VARCHAR2(25);
3658 l_coa_ccid VARCHAR2(35); -- bug 7172942
3659 l_current_calling_sequence VARCHAR2(2000);
3660 l_debug_info VARCHAR2(2000);
3661 l_api_name CONSTANT VARCHAR2(100) := 'get_gl_natural_account';
3662 e_api_failure EXCEPTION;
3663
3664 BEGIN
3665
3666 l_current_calling_sequence := P_calling_sequence||'->'||'get_gl_natural_account';
3667
3668 l_debug_info := 'Begin of get_gl_natural_account';
3669 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3670 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
3671 END IF;
3672
3673 /* Bug 7172942 - Added caching logic to improve performance. */
3674
3675 l_coa_ccid := to_char(p_coa_id)||'-'||to_char(p_ccid);
3676
3677 IF (( g_natural_acct_seg_t.count > 0 ) AND (g_natural_acct_seg_t.exists(l_coa_ccid))) Then
3678 l_nat_account := g_natural_acct_seg_t(l_coa_ccid).natural_acct_seg;
3679 ELSE
3680 --Bug 7172942
3681 l_debug_info := 'Natural Segment not found in Cache';
3682 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3683 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
3684 END IF;
3685
3686 l_success := FND_FLEX_APIS.get_segment_column(101,
3687 'GL#',
3688 p_coa_id,
3689 'GL_ACCOUNT',
3690 l_segment_num);
3691
3692 l_debug_info := 'FND API returned Natural Account: '||To_Char(l_segment_num);
3693 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3694 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
3695 END IF;
3696
3697 IF (l_success = FALSE) THEN
3698 l_debug_info := 'FND API Failed';
3699 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3700 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
3701 END IF;
3702 RAISE e_api_failure;
3703 END IF;
3704
3705 l_debug_info := 'Dyn SQL to be run for ccid: '||p_ccid||' and CoA ID:'||p_coa_id;
3706 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3707 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
3708 END IF;
3709
3710 EXECUTE IMMEDIATE 'SELECT '|| l_segment_num ||
3711 ' from gl_code_combinations where code_combination_id = :a '
3712 INTO l_nat_account USING p_ccid;
3713
3714 l_debug_info := 'Natural account: '||l_nat_account||'. end of function call. ';
3715 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3716 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
3717 END IF;
3718
3719 --Bug 7172942
3720 g_natural_acct_seg_t(l_coa_ccid).natural_acct_seg := l_nat_account ;
3721 END IF;
3722 --End Bug 7172942
3723
3724 RETURN l_nat_account;
3725
3726 EXCEPTION
3727 WHEN OTHERS THEN
3728 IF (SQLCODE <> -20001 ) THEN
3729 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3730 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3731 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence);
3732 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
3733
3734 APP_EXCEPTION.RAISE_EXCEPTION;
3735
3736 END IF;
3737
3738 END get_gl_natural_account;
3739
3740
3741 END AP_UTILITIES_PKG;
[Home] [Help]