1 PACKAGE BODY AP_UTILITIES_PKG AS
2 /* $Header: aputilsb.pls 120.51.12020000.2 2012/07/13 19:42:12 mkmeda 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 --Bug 13975870
858 and h.organization_id = aps.org_id
859 --Bug 13975870
860 and aps.set_of_books_id = g.set_of_books_id;
861 EXCEPTION
862 WHEN NO_DATA_FOUND THEN
863 return (NULL);
864 END;
865
866
867 END IF;
868
869 return l_wnd_context;
870
871 END Get_Window_Session_Title;
872
873 FUNCTION overlay_segments (
874 p_balancing_segment IN VARCHAR2,
875 p_cost_center_segment IN VARCHAR2,
876 p_account_segment IN VARCHAR2,
877 p_concatenated_segments IN VARCHAR2,
878 p_ccid IN OUT NOCOPY NUMBER,
879 p_set_of_books_id IN NUMBER,
880 p_overlay_mode IN VARCHAR2,
881 p_unbuilt_flex OUT NOCOPY VARCHAR2,
882 p_reason_unbuilt_flex OUT NOCOPY VARCHAR2,
883 p_resp_appl_id IN NUMBER,
884 p_resp_id IN NUMBER,
885 p_user_id IN NUMBER,
886 p_calling_sequence IN VARCHAR2,
887 p_ccid_to_segs IN VARCHAR2 Default NULL,
888 p_accounting_date IN DATE DEFAULT SYSDATE) --7531219
889
890 RETURN BOOLEAN IS
891
892 overlay_segments_failure EXCEPTION;
893 l_ccid NUMBER := p_ccid;
894 l_chart_of_accounts_id NUMBER;
895 l_segments FND_FLEX_EXT.SEGMENTARRAY;
896 l_partial_segments FND_FLEX_EXT.SEGMENTARRAY;
897 l_num_segments NUMBER;
898 l_account_segment_num NUMBER;
899 l_balancing_segment_num NUMBER;
900 l_cost_center_segment_num NUMBER;
901 l_partial_num_segments NUMBER;
902 l_overlayed_segments VARCHAR2(2000);
903 l_unbuilt_flex VARCHAR2(240):='';
904 l_reason_unbuilt_flex VARCHAR2(2000):='';
905 l_result BOOLEAN;
906 l_segment_delimiter VARCHAR2(1);
907 l_counter NUMBER;
908 current_calling_sequence VARCHAR2(2000);
909 debug_info VARCHAR2(500);
910
911 BEGIN
912 -- Update the calling sequence
913 --
914 current_calling_sequence := 'AP_UTILITIES_PKG.Overlay_Segments<-'||P_calling_sequence;
915
916 -----------------------------------------------------------
917 -- Reject if it's item line but no account info
918 -----------------------------------------------------------
919 debug_info := 'Select Charts of Account';
920
921
922
923 SELECT chart_of_accounts_id
924 INTO l_chart_of_accounts_id
925 FROM gl_sets_of_books
926 WHERE set_of_books_id = p_set_of_books_id;
927
928 debug_info := 'Get segment delimiter';
929
930
931 l_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
932 'SQLGL',
933 'GL#',
934 l_chart_of_accounts_id);
935
936
937
938 IF (l_segment_delimiter IS NULL) THEN
939 l_reason_unbuilt_flex := FND_MESSAGE.GET;
940 END IF;
941
942 -- Get Segment array for the input ccid
943 --
944
945 IF (l_ccid IS NOT NULL) Then
946
947 debug_info := 'Get segment array';
948 l_result := FND_FLEX_EXT.GET_SEGMENTS(
949 'SQLGL',
950 'GL#',
951 l_chart_of_accounts_id,
952 l_ccid,
953 l_num_segments,
954 l_segments);
955
956
957 IF (NOT l_result) THEN
958 l_reason_unbuilt_flex := FND_MESSAGE.GET;
959 END IF;
960
961 END IF; -- l_ccid not null
962 --
963 -- Get concatenated segments from ccid
964 IF (nvl(p_ccid_to_segs,'N') = 'Y') Then
965 l_overlayed_segments := FND_FLEX_EXT.Concatenate_Segments(l_num_segments,
966 l_segments,
967 l_segment_delimiter);
968
969 IF (NOT l_result) THEN
970 l_reason_unbuilt_flex := FND_MESSAGE.GET;
971 l_ccid := -1;
972 END IF;
973
974 p_ccid := l_ccid;
975 p_unbuilt_flex := l_overlayed_segments;
976 p_reason_unbuilt_flex := 'Used for deriving segments from ccid';
977 Return(TRUE);
978 END IF;
979
980
981 -- Get the partial segment array
982 --
983 IF (p_concatenated_segments IS NOT NULL) THEN
984
985 debug_info := 'Get Partial segment array';
986
987 l_partial_num_segments := FND_FLEX_EXT.breakup_segments(p_concatenated_segments,
988 l_segment_delimiter,
989 l_partial_segments); --OUT
990
991 END IF;
992 -- Overlay partial with original
993 -- only if l_num_segments = l_partial_num_segments
994
995 IF ((l_ccid IS NOT NULL) AND (p_concatenated_segments IS NOT NULL)) Then
996 IF (l_num_segments = l_partial_num_segments) Then
997
998
999
1000 debug_info := 'Overlay Partial segment array';
1001
1002 For l_counter IN 1..l_num_segments LOOP
1003
1004
1005 IF (l_partial_segments(l_counter) IS NOT NULL) Then
1006
1007 l_segments(l_counter) := l_partial_segments(l_counter);
1008
1009 End If;
1010
1011
1012
1013 END LOOP;
1014
1015 ELSE
1016 -- Reject Inconsistent Segments
1017 --
1018 p_ccid := -1;
1019 p_reason_unbuilt_flex := 'Inconsistent Segments';
1020 p_unbuilt_flex := Null;
1021 RETURN(TRUE);
1022
1023 END IF;
1024
1025 ElSIF ((l_ccid IS NULL) AND (p_concatenated_segments IS NOT NULL)) Then
1026
1027 -- we want to overlay concatenated segment
1028 l_segments := l_partial_segments;
1029 l_num_segments := l_partial_num_segments;
1030
1031 END IF; -- l_ccid is not null
1032
1033 -- Get the segment num for
1034 -- GL_ACCOUNT , GL_BALANCING and GL_COST_CENTER
1035
1036
1037 l_result := FND_FLEX_KEY_API.GET_SEG_ORDER_BY_QUAL_NAME(
1038 101,
1039 'GL#',
1040 l_chart_of_accounts_id,
1041 'GL_ACCOUNT',
1042 l_account_segment_num);
1043
1044
1045 IF (NOT l_result) THEN
1046 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1047 END IF;
1048
1049
1050 l_result := FND_FLEX_KEY_API.GET_SEG_ORDER_BY_QUAL_NAME(
1051 101,
1052 'GL#',
1053 l_chart_of_accounts_id,
1054 'GL_BALANCING',
1055 l_balancing_segment_num);
1056
1057
1058 IF (NOT l_result) THEN
1059 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1060 END IF;
1061
1062
1063 l_result := FND_FLEX_KEY_API.GET_SEG_ORDER_BY_QUAL_NAME(
1064 101,
1065 'GL#',
1066 l_chart_of_accounts_id,
1067 'FA_COST_CTR',
1068 l_cost_center_segment_num);
1069
1070
1071 IF (NOT l_result) THEN
1072 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1073 END IF;
1074
1075
1076
1077 -- Now overlay the Account, balancing and Cost Center segments
1078 -- if not null.
1079
1080
1081 IF (p_balancing_segment IS NOT NULL) Then
1082
1083 debug_info := 'Overlay balancing segment ';
1084 l_segments(l_balancing_segment_num) := p_balancing_segment;
1085
1086 End IF;
1087
1088
1089 IF (p_cost_center_segment IS NOT NULL) Then
1090
1091 debug_info := 'Overlay Cost Center segment ';
1092 l_segments(l_cost_center_segment_num) := p_cost_center_segment;
1093
1094 End IF;
1095
1096 IF (p_account_segment IS NOT NULL) Then
1097
1098 debug_info := 'Overlay Account segment ';
1099 l_segments(l_account_segment_num) := p_account_segment;
1100
1101 End IF;
1102
1103
1104 -- Get Concat Segments Back
1105 -- from seg array
1106
1107 l_overlayed_segments := FND_FLEX_EXT.Concatenate_Segments(l_num_segments,
1108 l_segments,
1109 l_segment_delimiter);
1110
1111 IF (NOT l_result) THEN
1112 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1113 END IF;
1114
1115
1116
1117
1118
1119 -- only if for creation (Use Validate segs with
1120 -- CHECK_COMBINATION and CREATE_COMBINATION)
1121 IF (p_overlay_mode = 'CHECK') Then
1122
1123
1124 debug_info := 'Validate Overlayed segments ';
1125 IF (fnd_flex_keyval.validate_segs('CHECK_COMBINATION' ,
1126 'SQLGL',
1127 'GL#',
1128 l_chart_of_accounts_id,
1129 l_overlayed_segments,
1130 'V',
1131 nvl(p_accounting_date, sysdate), -- 7531219
1132 'ALL',
1133 NULL,
1134 NULL,
1135 NULL,
1136 NULL,
1137 FALSE,
1138 FALSE,
1139 p_resp_appl_id,
1140 p_resp_id,
1141 p_user_id) <> TRUE) Then
1142
1143 l_ccid := -1;
1144 l_reason_unbuilt_flex := fnd_flex_keyval.error_message;
1145 l_unbuilt_flex := l_overlayed_segments;
1146
1147 Else
1148
1149 l_ccid := 666;
1150 l_reason_unbuilt_flex := NULL;
1151 l_unbuilt_flex := NULL;
1152 END IF;
1153
1154
1155 ELSIF (p_overlay_mode = 'CREATE') Then
1156
1157 debug_info := 'Create Overlayed segments ';
1158 IF (fnd_flex_keyval.validate_segs('CREATE_COMBINATION' ,
1159 'SQLGL',
1160 'GL#',
1161 l_chart_of_accounts_id,
1162 l_overlayed_segments,
1163 'V',
1164 nvl(p_accounting_date, sysdate), --7531219
1165 'ALL',
1166 NULL,
1167 NULL,
1168 NULL,
1169 NULL,
1170 FALSE,
1171 FALSE,
1172 p_resp_appl_id,
1173 p_resp_id,
1174 p_user_id) <> TRUE) Then
1175
1176 l_ccid := -1;
1177 l_reason_unbuilt_flex := fnd_flex_keyval.error_message;
1178 l_unbuilt_flex := l_overlayed_segments;
1179
1180 Else
1181
1182 l_ccid := fnd_flex_keyval.combination_id;
1183 l_reason_unbuilt_flex := NULL;
1184 l_unbuilt_flex := NULL;
1185
1186 END IF;
1187
1188 -- Bug 1414119 Added the ELSIF condition below to avoid autonomous
1189 -- transaction insert for new code combinations when dynamic insert
1190 -- is on.
1191
1192 ELSIF (p_overlay_mode = 'CREATE_COMB_NO_AT') Then
1193
1194 debug_info := 'Create Overlayed segments ';
1195 IF (fnd_flex_keyval.validate_segs('CREATE_COMB_NO_AT' ,
1196 'SQLGL',
1197 'GL#',
1198 l_chart_of_accounts_id,
1199 l_overlayed_segments,
1200 'V',
1201 nvl(p_accounting_date, sysdate), --7531219
1202 'ALL',
1203 NULL,
1204 NULL,
1205 NULL,
1206 NULL,
1207 FALSE,
1208 FALSE,
1209 p_resp_appl_id,
1210 p_resp_id,
1211 p_user_id) <> TRUE) Then
1212
1213 l_ccid := -1;
1214 l_reason_unbuilt_flex := fnd_flex_keyval.error_message;
1215 l_unbuilt_flex := l_overlayed_segments;
1216
1217 Else
1218
1219 l_ccid := fnd_flex_keyval.combination_id;
1220 l_reason_unbuilt_flex := NULL;
1221 l_unbuilt_flex := NULL;
1222
1223 END IF;
1224
1225
1226 END IF;
1227
1228 --
1229 -- Return value
1230 -- Bug 3621994 added if condition. CCID should be returned with the overlayed
1231 -- value only if the mode is not check . (Due to 3282531).
1232 IF (p_overlay_mode <> 'CHECK') Then
1233 p_ccid := l_ccid;
1234
1235 End IF;
1236
1237 p_unbuilt_flex := l_unbuilt_flex;
1238 p_reason_unbuilt_flex := l_reason_unbuilt_flex;
1239
1240 RETURN (TRUE);
1241
1242
1243 EXCEPTION
1244
1245 WHEN OTHERS THEN
1246
1247
1248 if (SQLCODE <> -20001) then
1249 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1250 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1251 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1252 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1253 end if;
1254
1255
1256 RETURN(FALSE);
1257
1258 END Overlay_Segments;
1259
1260 --following function added for BUG 1909374
1261 FUNCTION overlay_segments_by_gldate(
1262 p_balancing_segment IN VARCHAR2,
1263 p_cost_center_segment IN VARCHAR2,
1264 p_account_segment IN VARCHAR2,
1265 p_concatenated_segments IN VARCHAR2,
1266 p_ccid IN OUT NOCOPY NUMBER,
1267 p_accounting_date IN DATE,
1268 p_set_of_books_id IN NUMBER,
1269 p_overlay_mode IN VARCHAR2,
1270 p_unbuilt_flex OUT NOCOPY VARCHAR2,
1271 p_reason_unbuilt_flex OUT NOCOPY VARCHAR2,
1272 p_resp_appl_id IN NUMBER,
1273 p_resp_id IN NUMBER,
1274 p_user_id IN NUMBER,
1275 p_calling_sequence IN VARCHAR2,
1276 p_ccid_to_segs IN VARCHAR2 Default NULL)
1277
1278 RETURN BOOLEAN IS
1279
1280 overlay_segments_failure EXCEPTION;
1281 l_ccid NUMBER := p_ccid;
1282 l_chart_of_accounts_id NUMBER;
1283 l_segments FND_FLEX_EXT.SEGMENTARRAY;
1284 l_partial_segments FND_FLEX_EXT.SEGMENTARRAY;
1285 l_num_segments NUMBER;
1286 l_account_segment_num NUMBER;
1287 l_balancing_segment_num NUMBER;
1288 l_cost_center_segment_num NUMBER;
1289 l_partial_num_segments NUMBER;
1290 l_overlayed_segments VARCHAR2(2000);
1291 l_unbuilt_flex VARCHAR2(240):='';
1292 l_reason_unbuilt_flex VARCHAR2(2000):='';
1293 l_result BOOLEAN;
1294 l_segment_delimiter VARCHAR2(1);
1295 l_counter NUMBER;
1296 current_calling_sequence VARCHAR2(2000);
1297 debug_info VARCHAR2(500);
1298
1299 BEGIN
1300 -- Update the calling sequence
1301 --
1302 current_calling_sequence := 'AP_UTILITIES_PKG.Overlay_Segments<-'||P_calling_sequence;
1303
1304 -----------------------------------------------------------
1305 -- Reject if it's item line but no account info
1306 -----------------------------------------------------------
1307 debug_info := 'Select Charts of Account';
1308
1309
1310
1311 SELECT chart_of_accounts_id
1312 INTO l_chart_of_accounts_id
1313 FROM gl_sets_of_books
1314 WHERE set_of_books_id = p_set_of_books_id;
1315
1316 debug_info := 'Get segment delimiter';
1317
1318
1319 l_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
1320 'SQLGL',
1321 'GL#',
1322 l_chart_of_accounts_id);
1323
1324
1325
1326 IF (l_segment_delimiter IS NULL) THEN
1327 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1328 END IF;
1329
1330 -- Get Segment array for the input ccid
1331 --
1332
1333 IF (l_ccid IS NOT NULL) Then
1334
1335 debug_info := 'Get segment array';
1336 l_result := FND_FLEX_EXT.GET_SEGMENTS(
1337 'SQLGL',
1338 'GL#',
1339 l_chart_of_accounts_id,
1340 l_ccid,
1341 l_num_segments,
1342 l_segments);
1343
1344
1345 IF (NOT l_result) THEN
1346 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1347 END IF;
1348
1349 END IF; -- l_ccid not null
1350 --
1351 -- Get concatenated segments from ccid
1352 IF (nvl(p_ccid_to_segs,'N') = 'Y') Then
1353 l_overlayed_segments := FND_FLEX_EXT.Concatenate_Segments(l_num_segments,
1354 l_segments,
1355 l_segment_delimiter);
1356
1357 IF (NOT l_result) THEN
1358 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1359 l_ccid := -1;
1360 END IF;
1361
1362 p_ccid := l_ccid;
1363 p_unbuilt_flex := l_overlayed_segments;
1364 p_reason_unbuilt_flex := 'Used for deriving segments from ccid';
1365 Return(TRUE);
1366 END IF;
1367
1368
1369 -- Get the partial segment array
1370 --
1371 IF (p_concatenated_segments IS NOT NULL) THEN
1372
1373 debug_info := 'Get Partial segment array';
1374
1375 l_partial_num_segments := FND_FLEX_EXT.breakup_segments(p_concatenated_segments,
1376 l_segment_delimiter,
1377 l_partial_segments); --OUT
1378
1379 END IF;
1380 -- Overlay partial with original
1381 -- only if l_num_segments = l_partial_num_segments
1382
1383 IF ((l_ccid IS NOT NULL) AND (p_concatenated_segments IS NOT NULL)) Then
1384 IF (l_num_segments = l_partial_num_segments) Then
1385
1386
1387
1388 debug_info := 'Overlay Partial segment array';
1389
1390 For l_counter IN 1..l_num_segments LOOP
1391
1392
1393 IF (l_partial_segments(l_counter) IS NOT NULL) Then
1394
1395 l_segments(l_counter) := l_partial_segments(l_counter);
1396
1397 End If;
1398
1399
1400
1401 END LOOP;
1402
1403 ELSE
1404 -- Reject Inconsistent Segments
1405 --
1406 p_ccid := -1;
1407 p_reason_unbuilt_flex := 'Inconsistent Segments';
1408 p_unbuilt_flex := Null;
1409 RETURN(TRUE);
1410
1411 END IF;
1412
1413 ElSIF ((l_ccid IS NULL) AND (p_concatenated_segments IS NOT NULL)) Then
1414
1415 -- we want to overlay concatenated segment
1416 l_segments := l_partial_segments;
1417 l_num_segments := l_partial_num_segments;
1418
1419 END IF; -- l_ccid is not null
1420
1421 -- Get the segment num for
1422 -- GL_ACCOUNT , GL_BALANCING and GL_COST_CENTER
1423
1424
1425 l_result := FND_FLEX_KEY_API.GET_SEG_ORDER_BY_QUAL_NAME(
1426 101,
1427 'GL#',
1428 l_chart_of_accounts_id,
1429 'GL_ACCOUNT',
1430 l_account_segment_num);
1431
1432
1433 IF (NOT l_result) THEN
1434 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1435 END IF;
1436
1437
1438 l_result := FND_FLEX_KEY_API.GET_SEG_ORDER_BY_QUAL_NAME(
1439 101,
1440 'GL#',
1441 l_chart_of_accounts_id,
1442 'GL_BALANCING',
1443 l_balancing_segment_num);
1444
1445
1446 IF (NOT l_result) THEN
1447 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1448 END IF;
1449
1450
1451 l_result := FND_FLEX_KEY_API.GET_SEG_ORDER_BY_QUAL_NAME(
1452 101,
1453 'GL#',
1454 l_chart_of_accounts_id,
1455 'FA_COST_CTR',
1456 l_cost_center_segment_num);
1457
1458
1459 IF (NOT l_result) THEN
1460 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1461 END IF;
1462
1463
1464
1465 -- Now overlay the Account, balancing and Cost Center segments
1466 -- if not null.
1467
1468
1469 IF (p_balancing_segment IS NOT NULL) Then
1470
1471 debug_info := 'Overlay balancing segment ';
1472 l_segments(l_balancing_segment_num) := p_balancing_segment;
1473
1474 End IF;
1475
1476
1477 IF (p_cost_center_segment IS NOT NULL) Then
1478
1479 debug_info := 'Overlay Cost Center segment ';
1480 l_segments(l_cost_center_segment_num) := p_cost_center_segment;
1481
1482 End IF;
1483
1484 IF (p_account_segment IS NOT NULL) Then
1485
1486 debug_info := 'Overlay Account segment ';
1487 l_segments(l_account_segment_num) := p_account_segment;
1488
1489 End IF;
1490
1491
1492 -- Get Concat Segments Back
1493 -- from seg array
1494
1495 l_overlayed_segments := FND_FLEX_EXT.Concatenate_Segments(l_num_segments,
1496 l_segments,
1497 l_segment_delimiter);
1498
1499 IF (NOT l_result) THEN
1500 l_reason_unbuilt_flex := FND_MESSAGE.GET;
1501 END IF;
1502
1503
1504
1505
1506
1507 -- only if for creation (Use Validate segs with
1508 -- CHECK_COMBINATION and CREATE_COMBINATION)
1509 IF (p_overlay_mode = 'CHECK') Then
1510
1511
1512 debug_info := 'Validate Overlayed segments ';
1513 IF (fnd_flex_keyval.validate_segs('CHECK_COMBINATION' ,
1514 'SQLGL',
1515 'GL#',
1516 l_chart_of_accounts_id,
1517 l_overlayed_segments,
1518 'V',
1519 p_accounting_date,
1520 'ALL',
1521 NULL,
1522 NULL,
1523 NULL,
1524 NULL,
1525 FALSE,
1526 FALSE,
1527 p_resp_appl_id,
1528 p_resp_id,
1529 p_user_id) <> TRUE) Then
1530
1531 l_ccid := -1;
1532 l_reason_unbuilt_flex := fnd_flex_keyval.error_message;
1533 l_unbuilt_flex := l_overlayed_segments;
1534
1535 Else
1536
1537 l_ccid := 666;
1538 l_reason_unbuilt_flex := NULL;
1539 l_unbuilt_flex := NULL;
1540 END IF;
1541
1542
1543 ELSIF (p_overlay_mode = 'CREATE') Then
1544
1545 debug_info := 'Create Overlayed segments ';
1546 IF (fnd_flex_keyval.validate_segs('CREATE_COMBINATION' ,
1547 'SQLGL',
1548 'GL#',
1549 l_chart_of_accounts_id,
1550 l_overlayed_segments,
1551 'V',
1552 p_accounting_date,
1553 'ALL',
1554 NULL,
1555 NULL,
1556 NULL,
1557 NULL,
1558 FALSE,
1559 FALSE,
1560 p_resp_appl_id,
1561 p_resp_id,
1562 p_user_id) <> TRUE) Then
1563
1564 l_ccid := -1;
1565 l_reason_unbuilt_flex := fnd_flex_keyval.error_message;
1566 l_unbuilt_flex := l_overlayed_segments;
1567
1568 Else
1569
1570 l_ccid := fnd_flex_keyval.combination_id;
1571 l_reason_unbuilt_flex := NULL;
1572 l_unbuilt_flex := NULL;
1573
1574 END IF;
1575
1576 -- Bug 1414119 Added the ELSIF condition below to avoid autonomous
1577 -- transaction insert for new code combinations when dynamic insert
1578 -- is on.
1579
1580 ELSIF (p_overlay_mode = 'CREATE_COMB_NO_AT') Then
1581
1582 debug_info := 'Create Overlayed segments ';
1583 IF (fnd_flex_keyval.validate_segs('CREATE_COMB_NO_AT' ,
1584 'SQLGL',
1585 'GL#',
1586 l_chart_of_accounts_id,
1587 l_overlayed_segments,
1588 'V',
1589 p_accounting_date,
1590 'ALL',
1591 NULL,
1592 NULL,
1593 NULL,
1594 NULL,
1595 FALSE,
1596 FALSE,
1597 p_resp_appl_id,
1598 p_resp_id,
1599 p_user_id) <> TRUE) Then
1600
1601 l_ccid := -1;
1602 l_reason_unbuilt_flex := fnd_flex_keyval.error_message;
1603 l_unbuilt_flex := l_overlayed_segments;
1604
1605 Else
1606
1607 l_ccid := fnd_flex_keyval.combination_id;
1608 l_reason_unbuilt_flex := NULL;
1609 l_unbuilt_flex := NULL;
1610
1611 END IF;
1612
1613
1614 END IF;
1615
1616 --
1617 -- Return value
1618 p_ccid := l_ccid;
1619 p_unbuilt_flex := l_unbuilt_flex;
1620 p_reason_unbuilt_flex := l_reason_unbuilt_flex;
1621
1622 RETURN (TRUE);
1623
1624
1625 EXCEPTION
1626
1627 WHEN OTHERS THEN
1628
1629
1630 if (SQLCODE <> -20001) then
1631 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1632 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1633 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1634 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1635 end if;
1636
1637
1638 RETURN(FALSE);
1639
1640 END Overlay_Segments_by_gldate;
1641
1642
1643 FUNCTION check_partial(
1644 p_concatenated_segments IN VARCHAR2,
1645 p_partial_segments_flag OUT NOCOPY VARCHAR2,
1646 p_set_of_books_id IN NUMBER,
1647 p_error_message OUT NOCOPY VARCHAR2,
1648 p_calling_sequence IN VARCHAR2)
1649
1650 RETURN BOOLEAN IS
1651
1652 l_chart_of_accounts_id NUMBER;
1653 l_segments FND_FLEX_EXT.SEGMENTARRAY;
1654 l_num_segments NUMBER;
1655 l_segment_delimiter VARCHAR2(1);
1656 current_calling_sequence VARCHAR2(2000);
1657 debug_info VARCHAR2(500);
1658
1659
1660
1661 BEGIN
1662 -- Update the calling sequence
1663 --
1664 current_calling_sequence := 'AP_UTILITIES_PKG.Check_Partial<-'||P_calling_sequence;
1665
1666
1667
1668 debug_info := 'Select Charts of Account';
1669
1670 SELECT chart_of_accounts_id
1671 INTO l_chart_of_accounts_id
1672 FROM gl_sets_of_books
1673 WHERE set_of_books_id = p_set_of_books_id;
1674
1675 debug_info := 'Get Segment Delimiter';
1676
1677 l_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
1678 'SQLGL',
1679 'GL#',
1680 l_chart_of_accounts_id);
1681
1682
1683
1684 IF (l_segment_delimiter IS NULL) THEN
1685 p_error_message:= FND_MESSAGE.GET;
1686 END IF;
1687
1688 debug_info := 'Break Segments';
1689 l_num_segments := FND_FLEX_EXT.breakup_segments(p_concatenated_segments,
1690 l_segment_delimiter,
1691 l_segments); --OUT
1692
1693 p_partial_segments_flag := 'N';
1694
1695 For l_counter IN 1..l_num_segments LOOP
1696
1697
1698 IF (l_segments(l_counter) IS NULL) Then
1699
1700 p_partial_segments_flag := 'Y';
1701
1702 End If;
1703
1704
1705 END LOOP;
1706 RETURN (TRUE);
1707
1708 EXCEPTION
1709
1710
1711
1712 WHEN OTHERS THEN
1713
1714 if (SQLCODE <> -20001) then
1715 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1716 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1717 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1718 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1719 end if;
1720 RETURN(FALSE);
1721
1722 END Check_partial;
1723
1724 FUNCTION IS_CCID_VALID ( p_ccid IN NUMBER,
1725 p_chart_of_accounts_id IN NUMBER,
1726 p_date IN DATE ,
1727 p_calling_sequence IN VARCHAR2 )
1728
1729 RETURN BOOLEAN IS
1730
1731 -- Bug 3621994 -Deleted the unnecessary variables (due to 3086316)
1732
1733 l_enabled_flag gl_code_combinations.enabled_flag%type;
1734 current_calling_sequence VARCHAR2(2000);
1735 debug_info VARCHAR2(500);
1736
1737 BEGIN
1738 -- Update the calling sequence
1739 --
1740 current_calling_sequence := 'AP_UTILITIES_PKG.Ccid_Valid<-'||P_calling_sequence;
1741
1742 debug_info := 'Validate ccid as a whole';
1743
1744 If (( fnd_flex_keyval.validate_ccid(
1745 APPL_SHORT_NAME =>'SQLGL',
1746 KEY_FLEX_CODE => 'GL#',
1747 STRUCTURE_NUMBER =>p_chart_of_accounts_id,
1748 COMBINATION_ID =>p_ccid,
1749 DISPLAYABLE =>'ALL',
1750 DATA_SET => NULL,
1751 VRULE => NULL,
1752 SECURITY => 'ENFORCE',
1753 GET_COLUMNS => NULL,
1754 RESP_APPL_ID => FND_GLOBAL.resp_appl_id,
1755 RESP_ID => FND_GLOBAL.resp_id,
1756 USER_ID => FND_GLOBAL.user_id))) then
1757
1758 If (NOT ( fnd_flex_keyval.is_valid ) OR
1759 (fnd_flex_keyval.is_secured)) then
1760 RETURN(FALSE);
1761 end if;
1762
1763 Else
1764 RETURN(FALSE);
1765 End if;
1766
1767 -- Bug 3621994 - Removed following code to get and validate segments as It
1768 --Is redundant and casused performance issue (due to 3086316)
1769
1770 /* Bug: 3486932 Check to see if the ccid is enabled in GL. If the ccid
1771 is not enabled then return -1 */
1772
1773 SELECT nvl(enabled_flag,'N')
1774 INTO l_enabled_flag
1775 FROM gl_code_combinations
1776 WHERE code_combination_id = p_ccid
1777 AND chart_of_accounts_id = p_chart_of_accounts_id
1778 -- Bug 3486932 - Added the following conditions to verify if GL account
1779 -- is valid and summary flag and template id are proper.
1780
1781 -- Bug 3379623 deleted the previous AND stmt and added the below two.
1782 AND NVL(start_date_active, TRUNC(p_date)) <= TRUNC(p_date)
1783 AND NVL(end_date_active,
1784 TO_DATE('12/31/4012','MM/DD/YYYY')) >= TRUNC(p_date)
1785 AND summary_flag = 'N'
1786 AND template_id is NULL;
1787
1788 IF l_enabled_flag = 'N' then
1789
1790 Return (FALSE);
1791
1792 End If;
1793
1794
1795 RETURN (TRUE);
1796
1797 EXCEPTION
1798
1799 WHEN OTHERS THEN
1800
1801 if (SQLCODE <> -20001) then
1802 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1803 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1804 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1805 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1806 end if;
1807 RETURN(FALSE);
1808
1809 END IS_CCID_VALID;
1810
1811
1812
1813 -- MO Access Control: Added the parameter p_org_id.
1814 -- including default to the current_org_id
1815
1816 FUNCTION Get_Inventory_Org(P_org_id Number default mo_global.get_current_org_id) return NUMBER IS
1817 inv_org_id financials_system_parameters.inventory_organization_id%TYPE;
1818 BEGIN
1819
1820 select inventory_organization_id
1821 into inv_org_id
1822 from financials_system_parameters
1823 where org_id = p_org_id;
1824
1825 return(inv_org_id);
1826
1827 EXCEPTION
1828 WHEN NO_DATA_FOUND THEN
1829 return(null);
1830 END Get_Inventory_Org;
1831
1832
1833 PROCEDURE mc_flag_enabled( p_sob_id IN NUMBER,
1834 p_appl_id IN NUMBER,
1835 p_org_id IN NUMBER,
1836 p_fa_book_code IN VARCHAR2,
1837 p_base_currency IN VARCHAR2,
1838 p_mc_flag_enabled OUT NOCOPY VARCHAR2,
1839 p_calling_sequence IN VARCHAR2) IS
1840
1841 loop_index NUMBER := 1;
1842 l_sob_list gl_mc_info.r_sob_list:= gl_mc_info.r_sob_list() ;
1843 current_calling_sequence VARCHAR2(2000);
1844 debug_info VARCHAR2(500);
1845 NO_GL_DATA EXCEPTION;
1846
1847 BEGIN
1848 -- Update the calling sequence
1849 --
1850 current_calling_sequence := 'AP_UTILITIES_PKG.mc_flag_enabled<-'||p_calling_sequence;
1851
1852 debug_info := 'Calling GL package to get a list of reporting set of books';
1853 gl_mc_info.get_associated_sobs (p_sob_id,
1854 p_appl_id, p_org_id, NULL,
1855 l_sob_list);
1856 p_mc_flag_enabled := 'Y';
1857
1858 debug_info := 'Loop through every set of books and see if all the reporting currency is euro derived';
1859
1860 WHILE loop_index <= l_sob_list.count LOOP
1861 if ( gl_currency_api.is_fixed_rate (p_base_currency,
1862 l_sob_list(loop_index).r_sob_curr,
1863 sysdate) <> 'Y' ) then
1864 p_mc_flag_enabled := 'N';
1865 EXIT;
1866 end if;
1867 if ( gl_currency_api.is_fixed_rate (p_base_currency,
1868 l_sob_list(loop_index).r_sob_curr,
1869 sysdate) is NULL ) then
1870 raise NO_GL_DATA;
1871 end if;
1872 loop_index := loop_index +1;
1873 END LOOP;
1874
1875 EXCEPTION
1876 WHEN NO_GL_DATA THEN
1877
1878 p_mc_flag_enabled := NULL;
1879
1880 if (SQLCODE <> -20001) then
1881 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1882 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1883 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1884 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1885 end if;
1886
1887 END mc_flag_enabled;
1888
1889
1890 function AP_Get_Sob_Order_Col
1891 (P_Primary_SOB_ID IN number
1892 ,P_Secondary_SOB_ID IN number
1893 ,P_SOB_ID IN number
1894 ,P_ORG_ID IN number
1895 ,P_Calling_Sequence IN varchar2)
1896 return NUMBER is
1897 l_primary_sob_id number;
1898 l_sob_order_col number;
1899 current_calling_sequence varchar2(2000);
1900 debug_info varchar2(500);
1901 begin
1902 -- Update the calling sequence
1903 --
1904 current_calling_sequence :=
1905 'AP_UTILITIES_PKG.AP_Get_Sob_Order_Col<-'||p_calling_sequence;
1906 debug_info := 'Getting the order column value';
1907 l_primary_sob_id :=GL_MC_INFO.get_source_ledger_id(P_SOB_ID,200,P_ORG_ID,'');
1908
1909 /*
1910 SELECT DISTINCT primary_set_of_books_id
1911 INTO l_primary_sob_id
1912 FROM gl_mc_reporting_options
1913 WHERE reporting_set_of_books_id = P_SOB_ID
1914 AND application_id = 200;
1915 */
1916 if (l_primary_sob_id = P_Primary_SOB_ID) then
1917 l_sob_order_col := 1;
1918 else
1919 l_sob_order_col := 2;
1920 end if;
1921
1922 return (l_sob_order_col);
1923
1924 EXCEPTION
1925 WHEN OTHERS THEN
1926
1927 if (SQLCODE <> -20001) then
1928 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1929 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1930 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1931 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1932 end if;
1933 return NULL;
1934
1935 end AP_Get_Sob_Order_Col;
1936
1937
1938 FUNCTION get_charge_account
1939 ( p_ccid IN NUMBER,
1940 p_chart_of_accounts_id IN NUMBER,
1941 p_calling_sequence IN VARCHAR2)
1942 RETURN VARCHAR2 IS
1943
1944 current_calling_sequence VARCHAR2(2000);
1945 debug_info VARCHAR2(500);
1946 l_return_val VARCHAR2(2000);
1947 BEGIN
1948 -- Update the calling sequence
1949 --
1950 current_calling_sequence := 'AP_UTILITIES_PKG.get_charge_account<-'||p_calling_sequence;
1951 debug_info := 'Calling fnd function to validate ccid';
1952
1953 if p_ccid <> -1 then
1954
1955 l_return_val := FND_FLEX_EXT.GET_SEGS(
1956 APPLICATION_SHORT_NAME => 'SQLGL',
1957 KEY_FLEX_CODE => 'GL#',
1958 STRUCTURE_NUMBER => P_CHART_OF_ACCOUNTS_ID,
1959 COMBINATION_ID => P_CCID);
1960
1961 else
1962 l_return_val := null;
1963 end if;
1964 return (l_return_val);
1965
1966 EXCEPTION
1967 WHEN OTHERS THEN
1968
1969 if (SQLCODE <> -20001) then
1970 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
1971 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1972 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1973 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
1974 end if;
1975 return NULL;
1976
1977 END get_charge_account;
1978
1979 FUNCTION get_invoice_status(p_invoice_id IN NUMBER,
1980 p_calling_sequence IN VARCHAR2)
1981 RETURN VARCHAR2
1982 IS
1983 l_force_revalidation_flag VARCHAR2(1); --bug7244642
1984 invoice_status VARCHAR2(80);
1985 invoice_approval_status VARCHAR2(25);
1986 invoice_approval_flag VARCHAR2(1);
1987 distribution_approval_flag VARCHAR2(1);
1988 encumbrance_flag VARCHAR2(1);
1989 invoice_holds NUMBER;
1990 l_org_id NUMBER;
1991 l_curr_calling_sequence VARCHAR2(2000);
1992 l_debug_info VARCHAR2(100);
1993
1994
1995 ---------------------------------------------------------------------
1996 -- Declare cursor to establish the invoice-level approval flag
1997 --
1998 -- The first select simply looks at the match status flag for the
1999 -- distributions. The rest is to cover one specific case when some
2000 -- of the distributions are tested (T or A) and some are untested
2001 -- (NULL). The status should be needs reapproval (N).
2002 --
2003 CURSOR approval_cursor IS
2004 SELECT match_status_flag
2005 FROM ap_invoice_distributions_all
2006 WHERE invoice_id = p_invoice_id
2007 UNION
2008 SELECT 'N'
2009 FROM ap_invoice_distributions_all
2010 WHERE invoice_id = p_invoice_id
2011 AND match_status_flag IS NULL
2012 AND EXISTS
2013 (SELECT 'There are both untested and tested lines'
2014 FROM ap_invoice_distributions_all
2015 WHERE invoice_id = p_invoice_id
2016 AND match_status_flag IN ('T','A'))
2017 UNION -- Bug 6866672
2018 SELECT 'N'
2019 FROM ap_invoice_lines_all ail, ap_invoices_all ai
2020 WHERE ai.invoice_id = p_invoice_id
2021 AND ai.invoice_id = ail.invoice_id
2022 AND ai.cancelled_date is NULL
2023 AND NOT EXISTS
2024 (SELECT 1
2025 FROM ap_invoice_distributions_all
2026 WHERE invoice_id = p_invoice_id
2027 AND invoice_line_number = ail.line_number)
2028 AND ail.amount <> 0; -- Bug 6911199. Should ignore 0 Line Amounts.(Also one test case is an open issue)
2029
2030 BEGIN
2031
2032 l_curr_calling_sequence := 'AP_UTILITIES_PKG.'||'
2033 <-'||p_calling_sequence;
2034
2035 l_debug_info := 'Getting org_id';
2036 ---------------------------------------------------------------------
2037 -- Get the org_id
2038 --
2039 SELECT org_id
2040 INTO l_org_id
2041 FROM ap_invoices_all
2042 WHERE invoice_id = p_invoice_id;
2043
2044 ---------------------------------------------------------------------
2045 l_debug_info := 'Getting encumbrance flag';
2046
2047 -- Get the encumbrance flag
2048 --
2049 -- Fix for 1407074. Substituting the org_id with -99 if it's null
2050
2051 SELECT NVL(purch_encumbrance_flag,'N')
2052 INTO encumbrance_flag
2053 FROM financials_system_params_all
2054 WHERE NVL(org_id, -99) = NVL(l_org_id, -99);
2055
2056 ---------------------------------------------------------------------
2057 l_debug_info := 'Get hold count for invoice';
2058
2059 -- Get the number of holds for the invoice
2060 --
2061 SELECT count(*)
2062 INTO invoice_holds
2063 FROM ap_holds_all
2064 WHERE invoice_id = p_invoice_id
2065 AND release_lookup_code is NULL;
2066
2067 ---------------------------------------------------------------------
2068 -- bug7244642
2069 l_debug_info := 'get the force revalidation flag on the Invoice header';
2070
2071 BEGIN
2072
2073 SELECT nvl(ai.force_revalidation_flag, 'N')
2074 INTO l_force_revalidation_flag
2075 FROM ap_invoices_all ai
2076 WHERE ai.invoice_id = p_invoice_id;
2077
2078 EXCEPTION
2079 WHEN OTHERS THEN
2080 null;
2081
2082 END;
2083
2084 -- Establish the invoice-level approval flag
2085 --
2086 -- Use the following ordering sequence to determine the
2087 -- invoice-level approval flag:
2088 --
2089 -- 'N' - Needs Reapproval
2090 -- 'T' - Tested
2091 -- 'A' - Approved
2092 -- '' - Never Approved
2093 --
2094 -- Initialize invoice-level approval flag
2095 --
2096 invoice_approval_flag := '';
2097
2098 l_debug_info := 'Open approval_cursor';
2099
2100 OPEN approval_cursor;
2101
2102 LOOP
2103 l_debug_info := 'Fetching approval_cursor';
2104 FETCH approval_cursor INTO distribution_approval_flag;
2105 EXIT WHEN approval_cursor%NOTFOUND;
2106
2107 IF (distribution_approval_flag = 'N') THEN
2108 invoice_approval_flag := 'N';
2109 ELSIF (distribution_approval_flag = 'T' AND
2110 (invoice_approval_flag <> 'N'
2111 or invoice_approval_flag is null)) THEN
2112 invoice_approval_flag := 'T';
2113 ELSIF (distribution_approval_flag = 'A' AND
2114 (invoice_approval_flag NOT IN ('N','T')
2115 or invoice_approval_flag is null)) THEN
2116 invoice_approval_flag := 'A';
2117 END IF;
2118
2119 END LOOP;
2120 l_debug_info := 'Closing approval_cursor';
2121 CLOSE approval_cursor;
2122
2123 IF (invoice_approval_flag = 'A') THEN
2124 invoice_approval_status := 'APPROVED';
2125 ELSIF (invoice_approval_flag is null) THEN
2126 invoice_approval_status := 'NEVER APPROVED';
2127 ELSIF (invoice_approval_flag = 'N') THEN
2128 invoice_approval_status := 'NEEDS REAPPROVAL';
2129 ELSIF (invoice_approval_flag = 'T') THEN
2130 IF (encumbrance_flag = 'Y') THEN
2131 invoice_approval_status := 'NEEDS REAPPROVAL';
2132 ELSE
2133 invoice_approval_status := 'APPROVED';
2134 END IF;
2135 END IF; -- invoice_approval_flag
2136
2137 -- bug7244642
2138 IF ((invoice_approval_status = 'APPROVED') AND
2139 (l_force_revalidation_flag = 'Y')) THEN
2140 invoice_approval_status := 'NEEDS REAPPROVAL';
2141 END IF;
2142
2143 IF (invoice_approval_status = 'APPROVED') THEN
2144 invoice_status := 'UNACCOUNTED';
2145 ELSE
2146 invoice_status := 'UNAPPROVED';
2147 END IF;
2148
2149 RETURN(invoice_status);
2150 EXCEPTION
2151 WHEN OTHERS THEN
2152 if (SQLCODE <> -20001) then
2153 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
2154 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
2155 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
2156 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2157 'Invoice id = '||to_char(p_invoice_id)
2158 ||', Org id = '||l_org_id
2159 ||', Encumbrance flag = '||encumbrance_flag);
2160 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2161 end if;
2162 APP_EXCEPTION.RAISE_EXCEPTION;
2163 END get_invoice_status;
2164 ---------------------------------------------------------------------
2165
2166 -- Added by Bug:2022200
2167 -- Function net_invoice_amount returns the Net Invoice amount
2168 -- after subtracting the Prepayment amount applied and the
2169 -- Amount Withheld from the original Invoice amount.
2170 -- This function has been created because till release 11.0
2171 -- we used to reduce the Invoice amount by the prepaid amount
2172 -- and the amount withheld. This was discontinued in release 11.5.
2173 -- From release 11.5 Invoice amount is not changed. So to use
2174 -- any code from earlier releases where the reduced invoice amount
2175 -- is important, this function can be used.
2176 -- Modify this function to get the prepay_amount from the lines
2177 -- instead of distributions. The prepay_invoice_id and prepay_line_number
2178 -- columns will be populated for exclusive tax lines created for
2179 -- prepayment application. For the inclusive case the PREPAY line
2180 -- will include the tax amount applied.
2181
2182 FUNCTION net_invoice_amount(p_invoice_id IN NUMBER)
2183 RETURN NUMBER
2184 IS
2185 l_prepay_amount NUMBER := 0;
2186 l_net_inv_amount NUMBER := 0;
2187 BEGIN
2188 SELECT nvl((0 - sum(AIL.amount)),0)
2189 INTO l_prepay_amount
2190 FROM ap_invoice_lines_all AIL
2191 WHERE AIL.invoice_id = p_invoice_id
2192 AND nvl(AIL.invoice_includes_prepay_flag,'N') = 'N'
2193 AND (AIL.line_type_lookup_code = 'PREPAY'
2194 OR (AIL.line_type_lookup_code = 'TAX'
2195 AND AIL.prepay_invoice_id IS NOT NULL
2196 AND AIL.prepay_line_number IS NOT NULL));
2197
2198 SELECT nvl(AI.invoice_amount,0)- l_prepay_amount
2199 - nvl(AP_INVOICES_UTILITY_PKG.get_amount_withheld(p_invoice_id),0)
2200 INTO l_net_inv_amount
2201 FROM ap_invoices_all AI
2202 WHERE AI.invoice_id = p_invoice_id;
2203
2204
2205 RETURN(l_net_inv_amount);
2206
2207 END net_invoice_amount;
2208
2209 -------------------------------------------------------------------------------
2210 -- PROCEDURE Build_Offset_Account
2211 -- Given the base account and the overlay account, this procedure builds the new
2212 -- offSET account by overlaying them in the appropriate way determined by the
2213 -- auto-offset system option.
2214
2215 -- Parameters
2216 ----------
2217 -- Base_CCID - the account on which the overlaying will be done. In the
2218 -- case of invoices, this is the liability account.
2219 -- Overlay_CCID - the account whose segments will be used to do the
2220 -- overlaying onto the base account. In the case of invoices,
2221 -- this is the expense account.
2222 -- Accounting_Date - The date the flexbuilder will validate the ccid.
2223 -- Result_CCID - OUT NOCOPY param fOR the resulting offSET account
2224 -- Reason_Unbuilt_Flex - IN/OUT param. If (and only if) the account could
2225 -- not be built, it sends back the reason why
2226 -- flexbuilding failed. Otherwise, it goes back with
2227 -- the same value it came in with.
2228 -------------------------------------------------------------------------------
2229 PROCEDURE BUILD_OFFSET_ACCOUNT
2230 (P_base_ccid IN NUMBER
2231 ,P_overlay_ccid IN NUMBER
2232 ,P_accounting_date IN DATE
2233 ,P_result_ccid OUT NOCOPY NUMBER
2234 ,P_Reason_Unbuilt_Flex OUT NOCOPY VARCHAR2
2235 ,P_calling_sequence IN VARCHAR2
2236 ) IS
2237
2238 l_base_segments FND_FLEX_EXT.SEGMENTARRAY ;
2239 l_overlay_segments FND_FLEX_EXT.SEGMENTARRAY ;
2240 l_segments FND_FLEX_EXT.SEGMENTARRAY ;
2241 l_num_of_segments NUMBER ;
2242 l_result BOOLEAN ;
2243 l_curr_calling_sequence VARCHAR2(2000);
2244 G_flex_qualifier_name VARCHAR2(100);
2245 l_primary_sob_id AP_SYSTEM_PARAMETERS.set_of_books_id%TYPE;
2246 l_liability_post_lookup_code AP_SYSTEM_PARAMETERS.liability_post_lookup_code%TYPE;
2247 l_chart_of_accts_id GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE;
2248 G_flex_segment_num NUMBER;
2249
2250
2251 BEGIN
2252
2253 SELECT set_of_books_id,
2254 nvl(liability_post_lookup_code, 'NONE')
2255 INTO l_primary_sob_id,
2256 l_liability_post_lookup_code
2257 FROM ap_system_parameters
2258 where org_id = nvl(AP_UTILITIES_PKG.g_org_id,org_id); /*Bug11720134*/
2259
2260 SELECT chart_of_accounts_id
2261 INTO l_chart_of_accts_id
2262 FROM gl_sets_of_books
2263 WHERE set_of_books_id = l_primary_sob_id;
2264
2265 -- Get flexfield qualifier segment number
2266 IF (l_Liability_Post_Lookup_Code = 'ACCOUNT_SEGMENT_VALUE') THEN
2267
2268 G_flex_qualifier_name := 'GL_ACCOUNT' ;
2269
2270 ELSIF (l_Liability_Post_Lookup_Code = 'BALANCING_SEGMENT') THEN
2271
2272 G_flex_qualifier_name := 'GL_BALANCING' ;
2273
2274 END IF;
2275
2276 l_result := FND_FLEX_KEY_API.GET_SEG_ORDER_BY_QUAL_NAME(
2277 101, 'GL#',
2278 l_chart_of_accts_id,
2279 G_flex_qualifier_name,
2280 G_flex_segment_num);
2281
2282
2283 l_curr_calling_sequence := 'AP_ACCOUNTING_MAIN_PKG.Build_Offset_Account<-'
2284 || P_calling_sequence;
2285
2286 -- Get the segments of the two given accounts
2287 IF (NOT FND_FLEX_EXT.GET_SEGMENTS('SQLGL', 'GL#',
2288 l_chart_of_accts_id,
2289 P_base_ccid, l_num_of_segments,
2290 l_base_segments)
2291 ) THEN
2292
2293 -- Print reason why flex failed
2294 P_result_ccid := -1;
2295 P_reason_unbuilt_flex := 'INVALID ACCOUNT';
2296 RETURN ;
2297
2298 END IF;
2299
2300
2301 IF (NOT FND_FLEX_EXT.GET_SEGMENTS('SQLGL', 'GL#',
2302 l_chart_of_accts_id,
2303 P_overlay_ccid, l_num_of_segments,
2304 l_overlay_segments)
2305 ) THEN
2306 -- Print reason why flex failed
2307 P_result_ccid := -1;
2308 P_reason_unbuilt_flex := 'INVALID ACCOUNT';
2309 RETURN ;
2310
2311 END IF;
2312
2313 /*
2314 Overlay segments depending on system option
2315 Case 1: Account Segment Overlay
2316 Base A A [A] A
2317 Overlay B B [B] B
2318 Result B B [A] B
2319
2320 Case 2: Balancing Segment Overlay
2321 Base [A] A A A
2322 Overlay [B] B B B
2323 Result [B] A A A
2324 */
2325
2326 FOR i IN 1.. l_num_of_segments LOOP
2327
2328
2329 IF (G_Flex_Qualifier_Name = 'GL_ACCOUNT') THEN
2330
2331 -- Case 1: Account segment overlay
2332 IF (i = G_flex_segment_num) THEN
2333 l_segments(i) := l_base_segments(i);
2334 ELSE
2335 l_segments(i) := l_overlay_segments(i);
2336 END IF;
2337
2338 ELSIF (G_Flex_Qualifier_Name = 'GL_BALANCING') THEN
2339
2340 -- Case 2: Balancing segment overlay
2341 IF (i = G_flex_segment_num) THEN
2342 l_segments(i) := l_overlay_segments(i);
2343 ELSE
2344 l_segments(i) := l_base_segments(i);
2345 END IF;
2346
2347 END IF;
2348
2349 END LOOP;
2350
2351 -- Get ccid fOR overlayed segments
2352 l_result := FND_FLEX_EXT.GET_COMBINATION_ID('SQLGL', 'GL#',
2353 l_chart_of_accts_id,
2354 P_accounting_date, l_num_of_segments,
2355 l_segments, P_result_ccid) ;
2356
2357 IF (NOT l_result) THEN
2358
2359 -- Store reason why flex failed
2360 P_result_ccid := -1;
2361 P_reason_unbuilt_flex := 'INVALID ACCOUNT';
2362
2363 END IF;
2364
2365 EXCEPTION
2366 WHEN OTHERS THEN
2367 IF (SQLCODE <> -20001) THEN
2368 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
2369 'CALLING_SEQUENCE', l_curr_calling_sequence,
2370 FALSE);
2371 END IF;
2372 APP_EXCEPTION.RAISE_EXCEPTION;
2373
2374 END Build_Offset_Account;
2375
2376 -----------------------------------------------------------------------------------------------------
2377 -- Function get_auto_offsets_segments returns either ACCOUNTING or BALANCING segment of the
2378 -- input ccid.
2379 --
2380 -- For e.g. Accounting Flexfield Structure is
2381 -- Balancing Segment- Cost Center - Account
2382 -- 100 - 213 - 3000
2383 --
2384 -- Case 1 : Auto-offsets to Balancing
2385 -- Function returns "100" i.e Balancing Segment
2386 --
2387 --
2388 -- Case 2 : Auto-offsets to Accounting
2389 -- Function returns "100213" i.e. Concatenated segments except Accounting Segment
2390 --
2391 --
2392 ----------------------------------------------------------------------------------------
2393 FUNCTION get_auto_offsets_segments
2394 (P_base_ccid IN NUMBER) return varchar2 is
2395
2396
2397 l_base_segments FND_FLEX_EXT.SEGMENTARRAY ;
2398 l_overlay_segments FND_FLEX_EXT.SEGMENTARRAY ;
2399 l_segments FND_FLEX_EXT.SEGMENTARRAY ;
2400 l_num_of_segments NUMBER ;
2401 l_result BOOLEAN ;
2402 l_curr_calling_sequence VARCHAR2(2000);
2403 G_flex_qualifier_name VARCHAR2(100);
2404 l_primary_sob_id AP_SYSTEM_PARAMETERS.set_of_books_id%TYPE;
2405 l_liability_post_lookup_code AP_SYSTEM_PARAMETERS.liability_post_lookup_code%TYPE;
2406 l_chart_of_accts_id GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE;
2407 G_flex_segment_num NUMBER;
2408 l_return_segments varchar2(200) := null;
2409
2410
2411 BEGIN
2412
2413 ----------------------------------------------------------------------------------------
2414 -- Get Set of Books and Auto-offsets Option info
2415
2416 SELECT set_of_books_id,
2417 nvl(liability_post_lookup_code, 'NONE')
2418 INTO l_primary_sob_id,
2419 l_liability_post_lookup_code
2420 FROM ap_system_parameters;
2421
2422 -----------------------------------------------------------------------------------------
2423 -- Get Chart of Accounts Information
2424
2425 SELECT chart_of_accounts_id
2426 INTO l_chart_of_accts_id
2427 FROM gl_sets_of_books
2428 WHERE set_of_books_id = l_primary_sob_id;
2429
2430 -----------------------------------------------------------------------------------------
2431 -- Get flexfield qualifier segment number
2432
2433 IF (l_Liability_Post_Lookup_Code = 'ACCOUNT_SEGMENT_VALUE') THEN
2434
2435 G_flex_qualifier_name := 'GL_ACCOUNT' ;
2436
2437 ELSIF (l_Liability_Post_Lookup_Code = 'BALANCING_SEGMENT') THEN
2438
2439 G_flex_qualifier_name := 'GL_BALANCING' ;
2440
2441 ELSIF (l_liability_post_lookup_code = 'NONE') then
2442
2443 return null;
2444
2445 END IF;
2446
2447 l_result := FND_FLEX_KEY_API.GET_SEG_ORDER_BY_QUAL_NAME(
2448 101, 'GL#',
2449 l_chart_of_accts_id,
2450 G_flex_qualifier_name,
2451 G_flex_segment_num);
2452
2453 -----------------------------------------------------------------------------------------
2454 -- Get the segments of the given account
2455
2456 IF (NOT FND_FLEX_EXT.GET_SEGMENTS('SQLGL', 'GL#',
2457 l_chart_of_accts_id,
2458 P_base_ccid, l_num_of_segments,
2459 l_base_segments)
2460 ) THEN
2461
2462 -- Print reason why flex failed
2463 --P_result_ccid := -1;
2464 --P_reason_unbuilt_flex := 'INVALID ACCOUNT';
2465 RETURN -1 ;
2466
2467 END IF;
2468
2469 ---------------------------------------------------------------------------------------
2470 -- Get the Balancing Segment or Accounting Segment based on the auto-offset option
2471
2472
2473 FOR i IN 1.. l_num_of_segments LOOP
2474
2475 IF (G_Flex_Qualifier_Name = 'GL_BALANCING') THEN
2476
2477 IF (i = G_flex_segment_num) THEN
2478 l_segments(i) := l_base_segments(i);
2479 l_return_segments := l_segments(i);
2480 END IF;
2481
2482 ELSIF (G_Flex_Qualifier_Name = 'GL_ACCOUNT') THEN
2483
2484 IF (i = G_flex_segment_num) THEN
2485 l_segments(i) := l_base_segments(i);
2486 ELSE
2487 l_segments(i) := l_base_segments(i);
2488 l_return_segments := l_return_segments || l_segments(i) ;
2489 END IF;
2490
2491 END IF;
2492
2493 END LOOP;
2494
2495 return l_return_segments;
2496
2497 EXCEPTION
2498 WHEN OTHERS THEN
2499 IF (SQLCODE <> -20001) THEN
2500 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
2501 'CALLING_SEQUENCE', l_curr_calling_sequence,
2502 FALSE);
2503 END IF;
2504 -- return l_number;
2505 APP_EXCEPTION.RAISE_EXCEPTION;
2506
2507
2508 END get_auto_offsets_segments;
2509
2510 -----------------------------------------------------------------------------------------------------
2511 -- This function is a modified version of the original one above.
2512 -- We modified it to accept more parameters but do less work.
2513 -- Created for bug 2475913.
2514 ----------------------------------------------------------------------------------------
2515 FUNCTION get_auto_offsets_segments
2516 (P_base_ccid IN NUMBER,
2517 P_flex_qualifier_name IN VARCHAR2,
2518 P_flex_segment_num IN NUMBER,
2519 P_chart_of_accts_id IN GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE
2520 ) return varchar2 is
2521
2522
2523 l_base_segments FND_FLEX_EXT.SEGMENTARRAY ;
2524 l_overlay_segments FND_FLEX_EXT.SEGMENTARRAY ;
2525 l_segments FND_FLEX_EXT.SEGMENTARRAY ;
2526 l_result BOOLEAN ;
2527 l_curr_calling_sequence VARCHAR2(2000);
2528 l_return_segments varchar2(200) := null;
2529 l_num_of_segments NUMBER;
2530
2531
2532 BEGIN
2533
2534 -----------------------------------------------------------------------------------------
2535 -- Get the segments of the given account
2536
2537 IF (NOT FND_FLEX_EXT.GET_SEGMENTS('SQLGL', 'GL#',
2538 P_chart_of_accts_id,
2539 P_base_ccid, l_num_of_segments,
2540 l_base_segments)
2541 ) THEN
2542
2543 -- Print reason why flex failed
2544 --P_result_ccid := -1;
2545 --P_reason_unbuilt_flex := 'INVALID ACCOUNT';
2546 RETURN -1 ;
2547
2548 END IF;
2549
2550 ---------------------------------------------------------------------------------------
2551 -- Get the Balancing Segment or Accounting Segment based on the auto-offset option
2552
2553
2554 FOR i IN 1.. l_num_of_segments LOOP
2555
2556 IF (P_Flex_Qualifier_Name = 'GL_BALANCING') THEN
2557
2558 IF (i = P_flex_segment_num) THEN
2559 l_segments(i) := l_base_segments(i);
2560 l_return_segments := l_segments(i);
2561 END IF;
2562
2563 ELSIF (P_Flex_Qualifier_Name = 'GL_ACCOUNT') THEN
2564
2565 IF (i = P_flex_segment_num) THEN
2566 l_segments(i) := l_base_segments(i);
2567 ELSE
2568 l_segments(i) := l_base_segments(i);
2569 l_return_segments := l_return_segments || l_segments(i) ;
2570 END IF;
2571
2572 END IF;
2573
2574 END LOOP;
2575
2576 return l_return_segments;
2577
2578 EXCEPTION
2579 WHEN OTHERS THEN
2580 IF (SQLCODE <> -20001) THEN
2581 AP_Debug_Pkg.Print('Y','SQLAP','AP_DEBUG','ERROR',SQLERRM,
2582 'CALLING_SEQUENCE', l_curr_calling_sequence,
2583 FALSE);
2584 END IF;
2585
2586 -- return l_number;
2587 APP_EXCEPTION.RAISE_EXCEPTION;
2588
2589
2590 END get_auto_offsets_segments;
2591
2592 FUNCTION delete_invoice_from_interface(p_invoice_id_table in number_table_type,
2593 p_invoice_line_id_table in number_table_type,
2594 p_calling_sequence VARCHAR2) return boolean as
2595
2596 current_calling_sequence VARCHAR2(2000);
2597 debug_info VARCHAR2(500);
2598
2599 BEGIN
2600 current_calling_sequence :=
2601 'AP_UTILITIES_PKG.delete_invoice_from_interface<-'||P_calling_sequence;
2602 debug_info := 'Delete records from rejection and interface tables';
2603
2604 /* Delete invoices from interface */
2605 forall i in nvl(p_invoice_id_table.first,0)..nvl(p_invoice_id_table.last,0)
2606 delete from ap_invoices_interface where invoice_id = p_invoice_id_table(i);
2607 /* Delete invoice lines from interface */
2608 forall i in nvl(p_invoice_id_table.first,0)..nvl(p_invoice_id_table.last,0)
2609 delete from ap_invoice_lines_interface where invoice_id = p_invoice_id_table(i);
2610
2611 /* Delete invoice rejections from the rejections table */
2612 forall i in nvl(p_invoice_id_table.first,0)..nvl(p_invoice_id_table.last,0)
2613 delete from ap_interface_rejections
2614 where parent_id = p_invoice_id_table(i) and
2615 parent_table = 'AP_INVOICES_INTERFACE';
2616
2617 /* Delete invoice lines rejections from the rejections table */
2618 forall i in nvl(p_invoice_line_id_table.first,0)..nvl(p_invoice_line_id_table.last,0)
2619 delete from ap_interface_rejections
2620 where parent_id = p_invoice_line_id_table(i) and
2621 parent_table = 'AP_INVOICE_LINES_INTERFACE';
2622 return TRUE;
2623 EXCEPTION
2624 WHEN OTHERS THEN
2625 if (SQLCODE <> -20001) then
2626 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
2627 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
2628 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
2629 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
2630 end if;
2631 RETURN(FALSE);
2632 END delete_invoice_from_interface ;
2633
2634 -- Added following function for Exchange Rate Calculation Project.
2635 FUNCTION calculate_user_xrate(P_invoice_curr IN VARCHAR2,
2636 P_base_curr IN VARCHAR2,
2637 P_exchange_date IN DATE,
2638 P_exchange_rate_type IN VARCHAR2)
2639 RETURN VARCHAR2
2640 IS
2641 l_calc_user_xrate VARCHAR2(1);
2642
2643 BEGIN
2644
2645 SELECT nvl(calc_user_xrate, 'N')
2646 INTO l_calc_user_xrate
2647 FROM ap_system_parameters;
2648
2649 IF (P_exchange_rate_type = 'User') THEN
2650 IF (gl_euro_user_rate_api.is_cross_rate(P_invoice_curr,
2651 P_base_curr,
2652 P_exchange_date,
2653 P_exchange_rate_type) = 'N') THEN
2654 RETURN l_calc_user_xrate;
2655 END IF;
2656 END IF;
2657
2658 RETURN 'N';
2659
2660 END calculate_user_xrate;
2661
2662 -- Added the following function to get the GL Batch Name
2663 -- Function Called by Posted Payment Register and Posted Invoice Register.
2664 -- Inputs : Batch Id , GL and Subledger Link Id
2665 -- Output : GL batch Name for that Batch Id.
2666 FUNCTION get_gl_batch_name(P_batch_id IN NUMBER, P_GL_SL_link_id IN NUMBER, P_ledger_id IN NUMBER)
2667 RETURN varchar2 AS
2668 ret_val varchar2(100);
2669 BEGIN
2670 IF P_batch_id = -999 and P_gl_sl_link_id is not NULL THEN
2671 BEGIN
2672 /* Definitely a Batch Name is avaialble with the LINK */
2673 SELECT jb.name INTO ret_val
2674 FROM gl_je_batches jb
2675 WHERE JB.je_batch_id IN (SELECT IR.je_batch_id
2676 FROM gl_import_references IR
2677 WHERE IR.gl_sl_link_id = P_GL_SL_link_id
2678 AND IR.gl_sl_link_table = 'APECL');
2679 EXCEPTION
2680 WHEN NO_DATA_FOUND THEN
2681 /* Link is available and No Batch Name then We don't
2682 want such rows */
2683 SELECT '-999' into ret_val
2684 FROM DUAL
2685 WHERE NOT EXISTS (SELECT 'this link id exists in IR'
2686 FROM gl_import_references IR
2687 WHERE IR.gl_sl_link_id=P_gl_sl_link_id
2688 AND IR.gl_sl_link_table = 'APECL');
2689 END;
2690 RETURN ret_val;
2691 ELSIF P_batch_id = -999 and P_gl_sl_link_id is NULL THEN
2692 /* No Link NO Batch, we would print such rows */
2693 return '-999';
2694 ELSE
2695 /* The Batch Name is Provided */
2696 SELECT jb.name INTO ret_val
2697 FROM gl_je_batches jb
2698 WHERE JB.je_batch_id = P_batch_id
2699 and P_GL_SL_link_id IN
2700 (SELECT IR.gl_sl_link_id
2701 FROM gl_import_references IR, gl_je_headers JEH
2702 WHERE IR.je_header_id = JEH.je_header_id
2703 AND JEH.ledger_id = P_Ledger_id
2704 AND JEH.je_batch_id = P_batch_id
2705 AND IR.gl_sl_link_table = 'APECL');
2706 RETURN ret_val;
2707 END IF;
2708 EXCEPTION
2709 WHEN OTHERS THEN
2710 return NULL;
2711 END get_gl_batch_name;
2712
2713 -- Bug 2249806
2714 -- Code modified by MSWAMINA
2715 -- Added a new stored function for the performance reasons
2716 -- This will get the Lookup code and the lookup type
2717 -- as the input arguments and will return the corresponding
2718 -- Meaning as output.
2719 -- This function is created inorder to avoid the reparsing
2720 -- of these simple/common SQLs in our reports.
2721
2722 FUNCTION FND_Get_Displayed_Field
2723 (LookupType IN VARCHAR2
2724 ,LookupCode IN VARCHAR2
2725 ) RETURN VARCHAR2 IS
2726
2727 CURSOR c_lookup is
2728 SELECT meaning
2729 FROM fnd_lookups
2730 WHERE (lookup_code = LookupCode)
2731 AND (lookup_type = LookupType);
2732 output_string fnd_lookups.meaning%TYPE;
2733 --
2734 BEGIN
2735 --
2736 open c_lookup;
2737 fetch c_lookup into output_string;
2738 --
2739 IF c_lookup%NOTFOUND THEN
2740 raise NO_DATA_FOUND;
2741 END IF;
2742 --
2743 close c_lookup;
2744 return(output_string);
2745
2746 EXCEPTION
2747 WHEN NO_DATA_FOUND THEN
2748 return NULL;
2749
2750 END FND_Get_Displayed_Field;
2751
2752 -- Bug 2693900. Forward porting Bug 2610252.
2753 --Bug2610252 The function get_reversal_gl_date is added to get
2754 --gl date for reversed distribution. If the date passed is not
2755 --in an open period then first day of the next open period will
2756 --be returned otherwise an error message will be displayed.
2757 -----------------------------------------------------------------------
2758 --function get_reversal_gl_date takes argument P_Date and
2759 --returns gl date
2760 -----------------------------------------------------------------------
2761 -- Bug 5584997. Added the P_org_id
2762 Function get_reversal_gl_date(P_date in date,
2763 P_Org_Id In Number) return date
2764 is
2765 l_open_gl_date date :='';
2766 l_period_name gl_period_statuses.period_name%TYPE := '';
2767 begin
2768 l_period_name := ap_utilities_pkg.get_current_gl_date(p_date, P_org_id);
2769 if l_period_name is null then
2770 ap_utilities_pkg.get_open_gl_date(p_date,
2771 l_period_name,
2772 l_open_gl_date,
2773 P_org_id);
2774 if l_period_name is null then
2775 RAISE NO_DATA_FOUND;
2776 end if;
2777 else
2778 l_open_gl_date := p_date;
2779 end if;
2780 return l_open_gl_date;
2781 EXCEPTION
2782 WHEN NO_DATA_FOUND THEN
2783 FND_MESSAGE.SET_NAME('SQLAP', 'AP_CANCEL_NO_OPEN_FUT_PERIOD');
2784 FND_MESSAGE.SET_TOKEN('DATE', to_char(p_date, 'dd-mon-yyyy'));
2785 APP_EXCEPTION.RAISE_EXCEPTION;
2786 End get_reversal_gl_date;
2787
2788 --Bug2610252 The function get_reversal_period is added to get
2789 --period for reversed distribution. If the date passed is not
2790 --in an open period then period name of the next open period will
2791 --be returned otherwise an error message will be displayed.
2792 -----------------------------------------------------------------------
2793 --function get_reversal_period takes argument P_Date and
2794 --returns period name
2795 -----------------------------------------------------------------------
2796 -- Bug 5584997. Added the P_org_id
2797 Function get_reversal_period(P_date in date,
2798 P_org_id In Number) return varchar2
2799 is
2800 l_open_gl_date date :='';
2801 l_period_name gl_period_statuses.period_name%TYPE := '';
2802 begin
2803 l_period_name := ap_utilities_pkg.get_current_gl_date(p_date, p_org_id);
2804 if l_period_name is null then
2805 ap_utilities_pkg.get_open_gl_date(p_date,
2806 l_period_name,
2807 l_open_gl_date,
2808 p_org_id);
2809 if l_period_name is null then
2810 RAISE NO_DATA_FOUND;
2811 end if;
2812 else
2813 l_open_gl_date := p_date;
2814 end if;
2815 return l_period_name;
2816 EXCEPTION
2817 WHEN NO_DATA_FOUND THEN
2818 FND_MESSAGE.SET_NAME('SQLAP', 'AP_CANCEL_NO_OPEN_FUT_PERIOD');
2819 FND_MESSAGE.SET_TOKEN('DATE', to_char(p_date, 'dd-mon-yyyy'));
2820 APP_EXCEPTION.RAISE_EXCEPTION;
2821 End get_reversal_period;
2822
2823 /* =======================================================================*/
2824 /* New Function pa_flexbuild was created for in the scope of the Invoice */
2825 /* Lines Project - Stage 1 */
2826 /* =======================================================================*/
2827
2828 FUNCTION pa_flexbuild(
2829 p_vendor_id IN NUMBER,
2830 p_employee_id IN NUMBER,
2831 p_set_of_books_id IN NUMBER,
2832 p_chart_of_accounts_id IN NUMBER,
2833 p_base_currency_code IN VARCHAR2,
2834 p_Accounting_date IN DATE,
2835 p_award_id IN NUMBER,
2836 P_project_id IN AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
2837 p_task_id IN AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
2838 p_expenditure_type IN
2839 AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
2840 p_expenditure_org_id IN
2841 AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
2842 p_expenditure_item_date IN
2843 AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ITEM_DATE%TYPE,
2844 p_invoice_attribute_rec IN AP_UTILITIES_PKG.r_invoice_attribute_rec, --bug 8713737
2845 p_billable_flag IN VARCHAR2, --Bug6523162
2846 p_employee_ccid IN NUMBER, --Bug5003249
2847 p_web_parameter_id IN NUMBER, --Bug5003249
2848 p_invoice_type_lookup_code IN VARCHAR2, --Bug5003249
2849 p_default_last_updated_by IN NUMBER,
2850 p_default_last_update_login IN NUMBER,
2851 p_pa_default_dist_ccid OUT NOCOPY NUMBER,
2852 p_pa_concatenated_segments OUT NOCOPY VARCHAR2,
2853 p_debug_Info OUT NOCOPY VARCHAR2,
2854 p_debug_Context OUT NOCOPY VARCHAR2,
2855 p_calling_sequence IN VARCHAR2,
2856 p_default_dist_ccid IN AP_INVOICE_LINES.DEFAULT_DIST_CCID%TYPE --bug 5386396
2857 ) RETURN BOOLEAN
2858 IS
2859 procedure_billable_flag VARCHAR2(60) := '';
2860 l_concat_ids VARCHAR2(2000); --bug9064023
2861 l_errmsg VARCHAR2(2000); --bug9064023
2862 l_concat_descrs VARCHAR2(2000); --bug9064023
2863 l_concat_segs VARCHAR2(2000);
2864 current_calling_sequence VARCHAR2(2000);
2865 debug_info VARCHAR2(2000); --bug9064023
2866 l_app_short_name VARCHAR2(10); --bug 8980626
2867 l_message_name VARCHAR2(200); --bug 8980626
2868
2869 l_pa_installed VARCHAR2(10);
2870 l_status VARCHAR2(10);
2871 l_industry VARCHAR2(10);
2872 l_attribute_category AP_INVOICES_ALL.attribute_category%TYPE;
2873 l_attribute1 AP_INVOICES_ALL.attribute1%TYPE;
2874 l_attribute2 AP_INVOICES_ALL.attribute2%TYPE;
2875 l_attribute3 AP_INVOICES_ALL.attribute3%TYPE;
2876 l_attribute4 AP_INVOICES_ALL.attribute4%TYPE;
2877 l_attribute5 AP_INVOICES_ALL.attribute5%TYPE;
2878 l_attribute6 AP_INVOICES_ALL.attribute6%TYPE;
2879 l_attribute7 AP_INVOICES_ALL.attribute7%TYPE;
2880 l_attribute8 AP_INVOICES_ALL.attribute8%TYPE;
2881 l_attribute9 AP_INVOICES_ALL.attribute9%TYPE;
2882 l_attribute10 AP_INVOICES_ALL.attribute10%TYPE;
2883 l_attribute11 AP_INVOICES_ALL.attribute11%TYPE;
2884 l_attribute12 AP_INVOICES_ALL.attribute12%TYPE;
2885 l_attribute13 AP_INVOICES_ALL.attribute13%TYPE;
2886 l_attribute14 AP_INVOICES_ALL.attribute14%TYPE;
2887 l_attribute15 AP_INVOICES_ALL.attribute15%TYPE;
2888 li_attribute_category AP_INVOICES_ALL.attribute_category%TYPE;
2889 li_attribute1 AP_INVOICE_LINES_ALL.attribute1%TYPE;
2890 li_attribute2 AP_INVOICE_LINES_ALL.attribute2%TYPE;
2891 li_attribute3 AP_INVOICE_LINES_ALL.attribute3%TYPE;
2892 li_attribute4 AP_INVOICE_LINES_ALL.attribute4%TYPE;
2893 li_attribute5 AP_INVOICE_LINES_ALL.attribute5%TYPE;
2894 li_attribute6 AP_INVOICE_LINES_ALL.attribute6%TYPE;
2895 li_attribute7 AP_INVOICE_LINES_ALL.attribute7%TYPE;
2896 li_attribute8 AP_INVOICE_LINES_ALL.attribute8%TYPE;
2897 li_attribute9 AP_INVOICE_LINES_ALL.attribute9%TYPE;
2898 li_attribute10 AP_INVOICE_LINES_ALL.attribute10%TYPE;
2899 li_attribute11 AP_INVOICE_LINES_ALL.attribute11%TYPE;
2900 li_attribute12 AP_INVOICE_LINES_ALL.attribute12%TYPE;
2901 li_attribute13 AP_INVOICE_LINES_ALL.attribute13%TYPE;
2902 li_attribute14 AP_INVOICE_LINES_ALL.attribute14%TYPE;
2903 li_attribute15 AP_INVOICE_LINES_ALL.attribute15%TYPE;
2904
2905 BEGIN
2906
2907 -- Update the calling sequence
2908 --
2909 current_calling_sequence :=
2910 'AP_IMPORT_UTILITIES_PKG.pa_flexbuild<-'||P_calling_sequence;
2911
2912 --------------------------------------------------------------------------
2913 -- Step 1 - Flexbuild
2914 --------------------------------------------------------------------------
2915
2916 -- Flexbuild using Workflow.
2917
2918 debug_info := '(PA Flexbuild 1) Call pa_acc_gen_wf_pkg.'||
2919 'ap_inv_generate_account for flexbuilding';
2920 --Bug5003249 Start
2921 If (p_invoice_type_lookup_code = 'EXPENSE REPORT') then
2922 IF ( NOT pa_acc_gen_wf_pkg.ap_er_generate_account (
2923 p_project_id => p_project_id,
2924 p_task_id => p_task_id,
2925 p_expenditure_type => p_expenditure_type,
2926 p_vendor_id => P_VENDOR_ID,
2927 p_expenditure_organization_id => P_EXPENDITURE_ORG_ID,
2928 p_expenditure_item_date => P_EXPENDITURE_ITEM_DATE,
2929 p_billable_flag => p_billable_flag, --Bug6523162
2930 p_chart_of_accounts_id => P_CHART_OF_ACCOUNTS_ID,
2931 p_calling_module => 'APXINWKB',
2932 p_employee_id => P_employee_id,
2933 p_employee_ccid => p_employee_ccid,
2934 p_expense_type => p_web_parameter_id,
2935 p_expense_cc => null,
2936 /* bug 8713737 Passing p_invoice_attribute_rec */
2937 P_ATTRIBUTE_CATEGORY => p_invoice_attribute_rec.attribute_category,
2938 P_ATTRIBUTE1 => p_invoice_attribute_rec.attribute1,
2939 P_ATTRIBUTE2 => p_invoice_attribute_rec.attribute2,
2940 P_ATTRIBUTE3 => p_invoice_attribute_rec.attribute3,
2941 P_ATTRIBUTE4 => p_invoice_attribute_rec.attribute4,
2942 P_ATTRIBUTE5 => p_invoice_attribute_rec.attribute5,
2943 P_ATTRIBUTE6 => p_invoice_attribute_rec.attribute6,
2944 P_ATTRIBUTE7 => p_invoice_attribute_rec.attribute7,
2945 P_ATTRIBUTE8 => p_invoice_attribute_rec.attribute8,
2946 P_ATTRIBUTE9 => p_invoice_attribute_rec.attribute9,
2947 P_ATTRIBUTE10 => p_invoice_attribute_rec.attribute10,
2948 P_ATTRIBUTE11 => p_invoice_attribute_rec.attribute11,
2949 P_ATTRIBUTE12 => p_invoice_attribute_rec.attribute12,
2950 P_ATTRIBUTE13 => p_invoice_attribute_rec.attribute13,
2951 P_ATTRIBUTE14 => p_invoice_attribute_rec.attribute14,
2952 P_ATTRIBUTE15 => p_invoice_attribute_rec.attribute15,
2953 P_LINE_ATTRIBUTE_CATEGORY => p_invoice_attribute_rec.line_attribute_CATEGORY,
2954 P_LINE_ATTRIBUTE1 => p_invoice_attribute_rec.line_attribute1,
2955 P_LINE_ATTRIBUTE2 => p_invoice_attribute_rec.line_attribute2,
2956 P_LINE_ATTRIBUTE3 => p_invoice_attribute_rec.line_attribute3,
2957 P_LINE_ATTRIBUTE4 => p_invoice_attribute_rec.line_attribute4,
2958 P_LINE_ATTRIBUTE5 => p_invoice_attribute_rec.line_attribute5,
2959 P_LINE_ATTRIBUTE6 => p_invoice_attribute_rec.line_attribute6,
2960 P_LINE_ATTRIBUTE7 => p_invoice_attribute_rec.line_attribute7,
2961 P_LINE_ATTRIBUTE8 => p_invoice_attribute_rec.line_attribute8,
2962 P_LINE_ATTRIBUTE9 => p_invoice_attribute_rec.line_attribute9,
2963 P_LINE_ATTRIBUTE10 => p_invoice_attribute_rec.line_attribute10,
2964 P_LINE_ATTRIBUTE11 => p_invoice_attribute_rec.line_attribute11,
2965 P_LINE_ATTRIBUTE12 => p_invoice_attribute_rec.line_attribute12,
2966 P_LINE_ATTRIBUTE13 => p_invoice_attribute_rec.line_attribute13,
2967 P_LINE_ATTRIBUTE14 => p_invoice_attribute_rec.line_attribute14,
2968 P_LINE_ATTRIBUTE15 => p_invoice_attribute_rec.line_attribute15,
2969 x_return_ccid => P_PA_DEFAULT_DIST_CCID,
2970 x_concat_segs => l_concat_segs,
2971 x_concat_ids => l_concat_ids,
2972 x_concat_descrs => l_concat_descrs,
2973 x_error_message => l_errmsg,
2974 P_award_id => P_AWARD_ID, --Bug5198018
2975 p_input_ccid => p_default_dist_ccid --bug 5386396
2976 ))THEN
2977 --
2978 -- Show error message
2979 --
2980 /*debug_info :=
2981 '(PA Flexbuild 1 ) pa_acc_gen_wf_pkg.ap_er_generate_account Failed ';
2982 p_debug_info := debug_info || ': Error encountered';*/
2983
2984 --start bug 8980626
2985 /*p_debug_info := l_errmsg;*/ --bug 8320268
2986 fnd_message.parse_encoded(l_errmsg, l_app_short_name, l_message_name);
2987 fnd_message.set_name(l_app_short_name, l_message_name);
2988 p_debug_info := fnd_message.get;
2989 --end bug 8980626
2990
2991 p_debug_Context := current_calling_sequence;
2992 RETURN(FALSE);
2993
2994 END IF;
2995 else
2996
2997 debug_info := ' Call pa_acc_gen_wf_pkg.ap_inv_generate_account with billable flag '||p_billable_flag; --Bug6523162
2998
2999 IF ( NOT pa_acc_gen_wf_pkg.ap_inv_generate_account (
3000 p_project_id => p_project_id,
3001 p_task_id => p_task_id,
3002 P_AWARD_ID => p_award_id, --Bug5198018
3003 p_expenditure_type => p_expenditure_type,
3004 p_vendor_id => p_vendor_id,
3005 p_expenditure_organization_id => p_expenditure_org_id,
3006 p_expenditure_item_date => p_expenditure_item_date,
3007 p_billable_flag => p_billable_flag, --Bug6523162
3008 p_chart_of_accounts_id => P_chart_of_accounts_id,
3009 p_accounting_date => P_accounting_date,
3010 /* bug 8713737 Passing p_invoice_attribute_rec */
3011 P_ATTRIBUTE_CATEGORY => p_invoice_attribute_rec.attribute_category,
3012 P_ATTRIBUTE1 => p_invoice_attribute_rec.attribute1,
3013 P_ATTRIBUTE2 => p_invoice_attribute_rec.attribute2,
3014 P_ATTRIBUTE3 => p_invoice_attribute_rec.attribute3,
3015 P_ATTRIBUTE4 => p_invoice_attribute_rec.attribute4,
3016 P_ATTRIBUTE5 => p_invoice_attribute_rec.attribute5,
3017 P_ATTRIBUTE6 => p_invoice_attribute_rec.attribute6,
3018 P_ATTRIBUTE7 => p_invoice_attribute_rec.attribute7,
3019 P_ATTRIBUTE8 => p_invoice_attribute_rec.attribute8,
3020 P_ATTRIBUTE9 => p_invoice_attribute_rec.attribute9,
3021 P_ATTRIBUTE10 => p_invoice_attribute_rec.attribute10,
3022 P_ATTRIBUTE11 => p_invoice_attribute_rec.attribute11,
3023 P_ATTRIBUTE12 => p_invoice_attribute_rec.attribute12,
3024 P_ATTRIBUTE13 => p_invoice_attribute_rec.attribute13,
3025 P_ATTRIBUTE14 => p_invoice_attribute_rec.attribute14,
3026 P_ATTRIBUTE15 => p_invoice_attribute_rec.attribute15,
3027 P_DIST_ATTRIBUTE_CATEGORY => p_invoice_attribute_rec.line_attribute_category,
3028 P_DIST_ATTRIBUTE1 => p_invoice_attribute_rec.line_attribute1,
3029 P_DIST_ATTRIBUTE2 => p_invoice_attribute_rec.line_attribute2,
3030 P_DIST_ATTRIBUTE3 => p_invoice_attribute_rec.line_attribute3,
3031 P_DIST_ATTRIBUTE4 => p_invoice_attribute_rec.line_attribute4,
3032 P_DIST_ATTRIBUTE5 => p_invoice_attribute_rec.line_attribute5,
3033 P_DIST_ATTRIBUTE6 => p_invoice_attribute_rec.line_attribute6,
3034 P_DIST_ATTRIBUTE7 => p_invoice_attribute_rec.line_attribute7,
3035 P_DIST_ATTRIBUTE8 => p_invoice_attribute_rec.line_attribute8,
3036 P_DIST_ATTRIBUTE9 => p_invoice_attribute_rec.line_attribute9,
3037 P_DIST_ATTRIBUTE10 => p_invoice_attribute_rec.line_attribute10,
3038 P_DIST_ATTRIBUTE11 => p_invoice_attribute_rec.line_attribute11,
3039 P_DIST_ATTRIBUTE12 => p_invoice_attribute_rec.line_attribute12,
3040 P_DIST_ATTRIBUTE13 => p_invoice_attribute_rec.line_attribute13,
3041 P_DIST_ATTRIBUTE14 => p_invoice_attribute_rec.line_attribute14,
3042 P_DIST_ATTRIBUTE15 => p_invoice_attribute_rec.line_attribute15,
3043 x_return_ccid => P_PA_DEFAULT_DIST_CCID, -- OUT
3044 x_concat_segs => l_concat_segs, -- OUT NOCOPY
3045 x_concat_ids => l_concat_ids, -- OUT NOCOPY
3046 x_concat_descrs => l_concat_descrs, -- OUT NOCOPY
3047 x_error_message => l_errmsg, -- OUT NOCOPY
3048 p_input_ccid => p_default_dist_ccid)) THEN /* IN for bug#9010924 */
3049
3050 -- Show error message
3051 /* debug_info :=
3052 '(PA Flexbuild 1 ) pa_acc_gen_wf_pkg.ap_inv_generate_account Failed ';
3053 p_debug_info := debug_info || ': Error encountered';*/
3054
3055 --start bug 8980626
3056 /*p_debug_info := l_errmsg;*/ --bug 8320268
3057 fnd_message.parse_encoded(l_errmsg, l_app_short_name, l_message_name);
3058 fnd_message.set_name(l_app_short_name, l_message_name);
3059 p_debug_info := fnd_message.get;
3060 --end bug 8980626
3061
3062 p_debug_Context := current_calling_sequence;
3063 RETURN(FALSE);
3064 END IF;
3065 End if;
3066
3067 -------------------------------------------------------------------------
3068 -- Step 2 - Return Concatenated Segments
3069 --------------------------------------------------------------------------
3070 debug_info := '(PA Flexbuild 2) Return Concatenated Segments';
3071 P_PA_CONCATENATED_SEGMENTS := l_concat_segs;
3072 debug_info :=
3073 'p_pa_default_dist_ccid = '||to_char(p_pa_default_dist_ccid)
3074 ||' p_pa_concatenated_segments = '||p_pa_concatenated_segments
3075 ||' l_concat_segs = ' ||l_concat_segs
3076 ||' l_concat_ids = ' ||l_concat_ids
3077 ||' p_billable_flag = ' ||p_billable_flag --Bug6523162
3078 ||' l_concat_descrs = ' ||l_concat_descrs
3079 ||' l_errmsg = ' ||l_errmsg;
3080
3081 RETURN(TRUE);
3082
3083 EXCEPTION
3084 WHEN OTHERS THEN
3085 p_debug_info := debug_info || ': Error encountered in Flexbuild';
3086 p_debug_Context := current_calling_sequence;
3087
3088 IF (SQLCODE < 0) then
3089 IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
3090 AP_IMPORT_UTILITIES_PKG.print(
3091 AP_IMPORT_INVOICES_PKG.g_debug_switch, SQLERRM);
3092 END IF;
3093 END IF;
3094
3095 RETURN (FALSE);
3096 END pa_flexbuild;
3097
3098 PROCEDURE Get_Invoice_LE (
3099 p_vendor_site_id IN NUMBER,
3100 p_inv_liab_ccid IN NUMBER,
3101 p_org_id IN NUMBER,
3102 p_le_id OUT NOCOPY NUMBER) IS
3103
3104 l_ptop_le_info XLE_BUSINESSINFO_GRP.ptop_le_rec;
3105 l_le_return_status varchar2(1);
3106 l_msg_data varchar2(1000);
3107
3108 l_bill_to_location_id NUMBER(15);
3109 l_supp_site_liab_ccid NUMBER(15);
3110 l_ccid_to_api NUMBER(15);
3111 l_valid_le VARCHAR2(100);
3112
3113 BEGIN
3114 -- Get Bill TO Location and Liab Acct from Supplier Site
3115 BEGIN
3116 SELECT bill_to_location_id,
3117 accts_pay_code_combination_id
3118 INTO l_bill_to_location_id,
3119 l_supp_site_liab_ccid
3120 FROM po_vendor_sites
3121 WHERE vendor_site_id = p_vendor_site_id;
3122
3123 l_ccid_to_api := NVL(p_inv_liab_ccid,
3124 l_supp_site_liab_ccid);
3125 EXCEPTION
3126 WHEN OTHERS THEN
3127 l_bill_to_location_id := NULL;
3128 l_ccid_to_api := p_inv_liab_ccid;
3129 END;
3130 --
3131 -- Call LE API
3132 XLE_BUSINESSINFO_GRP.Get_PurchasetoPay_Info
3133 (l_le_return_status,
3134 l_msg_data,
3135 null,
3136 null,
3137 l_bill_to_location_id,
3138 l_ccid_to_api,
3139 p_org_id,
3140 l_ptop_le_info);
3141
3142 IF (l_le_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3143 p_le_id := l_ptop_le_info.legal_entity_id;
3144 END IF;
3145
3146 EXCEPTION
3147 WHEN OTHERS THEN
3148 APP_EXCEPTION.RAISE_EXCEPTION;
3149 END Get_Invoice_LE;
3150
3151 FUNCTION Get_Check_LE (
3152 p_bank_acct_use_id IN NUMBER)
3153 RETURN NUMBER IS
3154 l_legal_entity_id NUMBER;
3155 BEGIN
3156
3157 SELECT account_owner_org_id
3158 INTO l_legal_entity_id
3159 FROM ce_bank_accounts cba,
3160 ce_bank_acct_uses_all cbau
3161 WHERE cbau.bank_account_id = cba.bank_account_id
3162 AND cbau.bank_acct_use_id = p_bank_acct_use_id;
3163
3164 RETURN (l_legal_entity_id);
3165
3166 EXCEPTION
3167 WHEN OTHERS THEN
3168 APP_EXCEPTION.RAISE_EXCEPTION;
3169 END Get_Check_LE;
3170
3171 /*==========================================================
3172 | PROCEDURE - getInvoiceLEInfo
3173 | This is a wrapper for get_invoice_le()
3174 | with more detailed L.E. info returned
3175 *=========================================================*/
3176 PROCEDURE getInvoiceLEInfo (
3177 p_vendor_site_id IN NUMBER,
3178 p_inv_liab_ccid IN NUMBER,
3179 p_org_id IN NUMBER,
3180 p_le_id OUT NOCOPY NUMBER,
3181 p_le_name OUT NOCOPY VARCHAR2,
3182 p_le_registration_num OUT NOCOPY VARCHAR2,
3183 p_le_address1 OUT NOCOPY VARCHAR2,
3184 p_le_city OUT NOCOPY VARCHAR2,
3185 p_le_postal_code OUT NOCOPY VARCHAR2,
3186 p_le_country OUT NOCOPY VARCHAR2) IS
3187
3188 l_ptop_le_info XLE_BUSINESSINFO_GRP.ptop_le_rec;
3189 l_le_return_status varchar2(1);
3190 l_msg_data varchar2(1000);
3191
3192 l_bill_to_location_id NUMBER(15);
3193 l_supp_site_liab_ccid NUMBER(15);
3194 l_ccid_to_api NUMBER(15);
3195 l_valid_le VARCHAR2(100);
3196
3197 BEGIN
3198 -- Get Bill TO Location and Liab Acct from Supplier Site
3199 BEGIN
3200 SELECT bill_to_location_id,
3201 accts_pay_code_combination_id
3202 INTO l_bill_to_location_id,
3203 l_supp_site_liab_ccid
3204 FROM po_vendor_sites
3205 WHERE vendor_site_id = p_vendor_site_id;
3206
3207 l_ccid_to_api := NVL(p_inv_liab_ccid,
3208 l_supp_site_liab_ccid);
3209 EXCEPTION
3210 WHEN OTHERS THEN
3211 l_bill_to_location_id := NULL;
3212 l_ccid_to_api := p_inv_liab_ccid;
3213 END;
3214 --
3215 -- Call LE API
3216 XLE_BUSINESSINFO_GRP.Get_PurchasetoPay_Info
3217 (l_le_return_status,
3218 l_msg_data,
3219 null,
3220 null,
3221 l_bill_to_location_id,
3222 l_ccid_to_api,
3223 p_org_id,
3224 l_ptop_le_info);
3225
3226 IF (l_le_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3227 p_le_id := l_ptop_le_info.legal_entity_id;
3228 p_le_name := l_ptop_le_info.name;
3229 p_le_registration_num := l_ptop_le_info.registration_number;
3230 -- p_le_party_id := l_ptop_le_info.party_id;
3231 p_le_address1 := l_ptop_le_info.address_line_1;
3232 p_le_city := l_ptop_le_info.town_or_city;
3233 p_le_postal_code := l_ptop_le_info.postal_code;
3234 p_le_country := l_ptop_le_info.country;
3235 END IF;
3236
3237 EXCEPTION
3238 WHEN OTHERS THEN
3239 APP_EXCEPTION.RAISE_EXCEPTION;
3240 END getInvoiceLEInfo;
3241
3242 PROCEDURE Delete_AP_Profiles
3243 (P_Profile_Option_Name IN VARCHAR2)
3244 IS
3245 BEGIN
3246 FND_PROFILE_OPTIONS_PKG.Delete_Row(P_Profile_Option_Name);
3247 EXCEPTION
3248 WHEN NO_DATA_FOUND THEN
3249 NULL;
3250 WHEN OTHERS THEN
3251 NULL;
3252 END Delete_AP_Profiles;
3253
3254 /*
3255 3881457 : Create new function that returns the closing status of the
3256 period that the input date is in.
3257 */
3258 FUNCTION PERIOD_STATUS (p_gl_date IN DATE)
3259 RETURN VARCHAR2 IS
3260
3261 CURSOR c_input_period IS
3262 SELECT GLPS.closing_status
3263 FROM gl_period_statuses GLPS,
3264 ap_system_parameters SP
3265 WHERE GLPS.application_id = 200
3266 AND GLPS.set_of_books_id = SP.set_of_books_id
3267 AND TRUNC(p_gl_date) BETWEEN GLPS.start_date AND GLPS.end_date
3268 AND NVL(GLPS.adjustment_period_flag, 'N') = 'N';
3269
3270 v_closing_status GL_PERIOD_STATUSES.CLOSING_STATUS%TYPE;
3271
3272 BEGIN
3273
3274 OPEN c_input_period;
3275
3276 FETCH c_input_period
3277 INTO v_closing_status;
3278
3279 CLOSE c_input_period;
3280
3281 RETURN v_closing_status;
3282
3283 END PERIOD_STATUS;
3284
3285
3286 PROCEDURE clob_to_file
3287 (p_xml_clob IN CLOB) IS
3288
3289 l_clob_size NUMBER;
3290 l_offset NUMBER;
3291 l_chunk_size INTEGER;
3292 l_chunk VARCHAR2(32767);
3293 l_log_module VARCHAR2(240);
3294
3295 BEGIN
3296
3297
3298 l_clob_size := dbms_lob.getlength(p_xml_clob);
3299
3300 IF (l_clob_size = 0) THEN
3301 RETURN;
3302 END IF;
3303
3304 l_offset := 1;
3305 l_chunk_size := 3000;
3306
3307 WHILE (l_clob_size > 0) LOOP
3308 l_chunk := dbms_lob.substr (p_xml_clob, l_chunk_size, l_offset);
3309 fnd_file.put
3310 (which => fnd_file.output
3311 ,buff => l_chunk);
3312
3313 l_clob_size := l_clob_size - l_chunk_size;
3314 l_offset := l_offset + l_chunk_size;
3315 END LOOP;
3316
3317 fnd_file.new_line(fnd_file.output,1);
3318
3319 EXCEPTION
3320 WHEN OTHERS THEN
3321 APP_EXCEPTION.RAISE_EXCEPTION;
3322
3323 END clob_to_file;
3324
3325 FUNCTION pa_period_status(
3326 p_gl_date IN DATE,
3327 p_org_id IN number default
3328 mo_global.get_current_org_id) RETURN varchar2 IS
3329
3330 CURSOR c_closing_status IS
3331 SELECT GLPS.closing_status
3332 FROM gl_period_statuses GLPS,
3333 ap_system_parameters SP
3334 WHERE GLPS.application_id = 8721
3335 AND SP.org_id = P_Org_Id
3336 AND GLPS.set_of_books_id = SP.set_of_books_id
3337 AND TRUNC(p_gl_date) BETWEEN GLPS.start_date AND GLPS.end_date
3338 AND NVL(GLPS.adjustment_period_flag, 'N') = 'N';
3339
3340 v_closing_status GL_PERIOD_STATUSES.CLOSING_STATUS%TYPE;
3341
3342 BEGIN
3343 OPEN c_closing_status;
3344
3345 FETCH c_closing_status
3346 INTO v_closing_status;
3347
3348 CLOSE c_closing_status;
3349
3350 RETURN v_closing_status;
3351 END pa_period_status;
3352
3353 /*============================================================================
3354 | FUNCTION - Get_PO_REVERSED_ENCUMB_AMOUNT
3355 |
3356 | DESCRIPTION
3357 | fetch the amount of PO encumbrance reversed against the given PO
3358 | distribution from all invoices for a given date range in functional
3359 | currency. Calculation includes PO encumbrance which are in GL only.
3360 | In case Invoice encumbrance type is the same as PO encumbrance, we
3361 | need to exclude the variance.
3362 | it returns actual amount or 0 if there is po reversed encumbrance
3363 | line existing, otherwise returns NULL.
3364 |
3365 | This function is only applicable to pre 11i and 11i data. Due to R12
3366 | new funds/encumbrance solution. AP will be maintaining this function
3367 | for pre-upgrade data because of PSA team decide no upgrade for
3368 | AP_ENCUMBRANCE_LINES_ALL table.
3369 |
3370 | PARAMETERS
3371 | P_Po_distribution_id - po_distribution_id (in)
3372 | P_Start_date - Start gl date (in)
3373 | P_End_date - End gl date (in)
3374 | P_Calling_Sequence - debug usage
3375 |
3376 | KNOWN ISSUES:
3377 |
3378 | NOTES:
3379 |
3380 | 1. In case user changes the purchase order encumbrance
3381 | type or Invoice encumbrance type after invoice is
3382 | validated, this API might not return a valid value.
3383 |
3384 | MODIFICATION HISTORY
3385 | Date Author Description of Change
3386 | 2-18-06 sfeng move function to different pkg and
3387 | back out the change of bug 3851654
3388 |
3389 *==========================================================================*/
3390
3391 FUNCTION Get_PO_Reversed_Encumb_Amount(
3392 P_Po_Distribution_Id IN NUMBER,
3393 P_Start_gl_Date IN DATE,
3394 P_End_gl_Date IN DATE,
3395 P_Calling_Sequence IN VARCHAR2 DEFAULT NULL)
3396
3397 RETURN NUMBER
3398 IS
3399
3400 l_current_calling_sequence VARCHAR2(2000);
3401 l_procedure_name CONSTANT VARCHAR2(60) := 'Get_PO_Reversed_Encumb_Amount';
3402 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3403
3404 l_unencumbered_amount NUMBER;
3405 l_upg_unencumbered_amount NUMBER;
3406 l_total_unencumbered_amount NUMBER;
3407
3408 CURSOR po_enc_reversed_cur IS
3409 SELECT sum(nvl(ael.accounted_cr,0) - nvl(ael.accounted_dr,0) )
3410 FROM (SELECT DISTINCT old_distribution_id, encumbered_flag, org_id
3411 FROM AP_INVOICE_DISTRIBUTIONS dist
3412 WHERE po_distribution_id = p_po_distribution_id
3413 ) aid,
3414 AP_ENCUMBRANCE_LINES ael,
3415 financials_system_parameters fsp
3416 WHERE 1=1 --aid.po_distribution_id = P_po_distribution_id --commented for bug12962585
3417 AND aid.old_distribution_id = ael.invoice_distribution_id -- added for bug12962585
3418 -- AND aid.invoice_distribution_id = ael.invoice_distribution_id --commented for bug12962585
3419 AND ( ( p_start_gl_date is not null
3420 and p_start_gl_date <= ael.accounting_date ) or
3421 ( p_start_gl_date is null ) )
3422 AND ( (p_end_gl_date is not null
3423 and p_end_gl_date >= ael.accounting_date ) or
3424 (p_end_gl_date is null ) )
3425 --AND ael.encumbrance_line_type not in ('IPV', 'ERV', 'QV','AV') --commented for bug12962585
3426 AND nvl(aid.org_id,-1) = nvl(fsp.org_id,-1)
3427 AND ael.encumbrance_type_id = fsp.purch_encumbrance_type_id
3428 --added below condition for bug12962585
3429 AND ( (ael.ae_header_id is null and aid.encumbered_flag = 'Y' ) or
3430 (ael.ae_header_id is not null and
3431 'Y' = ( select gl_transfer_flag
3432 from ap_ae_headers aeh
3433 where aeh.ae_header_id = ael.ae_header_id ) )
3434 )
3435 ;
3436
3437
3438 -- Bug 7004146, added the condition on the historical flag
3439 -- and line types, to make sure that only the pre-11i
3440 -- distributions are picked up.
3441 CURSOR upgraded_po_enc_rev_cur IS
3442 SELECT sum (nvl(nvl(aid.base_amount,aid.amount),0) -
3443 nvl(aid.base_invoice_price_variance ,0) -
3444 nvl(aid.exchange_rate_variance,0) -
3445 nvl(aid.base_quantity_variance,0))
3446 FROM ap_invoice_dists_arch aid, --bug12962585, changed to old table
3447 po_distributions pd,
3448 financials_system_parameters fs
3449 where aid.po_distribution_id = p_po_distribution_id
3450 and aid.po_distribution_id = pd.po_distribution_id
3451 and nvl(aid.org_id,-1) = nvl(fs.org_id,-1)
3452 /* and fs.inv_encumbrance_type_id <> fs.purch_encumbrance_type_id Bug 14063588*/
3453 and NVL(PD.accrue_on_receipt_flag,'N') = 'N'
3454 AND AID.po_distribution_id is not null
3455 AND nvl(aid.match_status_flag, 'N') = 'A'
3456 AND nvl(aid.encumbered_flag, 'N') = 'Y'
3457 /* AND nvl(aid.historical_flag, 'N') = 'Y' Bug 14063588*/
3458 AND aid.line_type_lookup_code NOT IN ('IPV', 'ERV', 'TIPV', 'TERV', 'TRV', 'QV', 'AV')
3459 AND (aid.accrual_posted_flag = 'Y' or aid.cash_posted_flag = 'Y')
3460 AND (( p_start_gl_date is not null and p_start_gl_date <= aid.accounting_date) or (p_start_gl_date is null))
3461 AND ((p_end_gl_date is not null and p_end_gl_date >= aid.accounting_date) or (p_end_gl_date is null))
3462 AND NOT EXISTS (SELECT 'release 11.5 encumbrance'
3463 from ap_encumbrance_lines_all ael
3464 where ael.invoice_distribution_id = aid.invoice_distribution_id)
3465 -- bug 7225570
3466 AND aid.bc_event_id is null
3467 AND NOT EXISTS (SELECT 'release 11.5 encumbrance tax'
3468 from ap_encumbrance_lines_all ael
3469 where ael.invoice_distribution_id = aid.charge_applicable_to_dist_id);
3470
3471 BEGIN
3472
3473 l_current_calling_sequence := 'AP_UTILITIES_PKG.'
3474 || 'Get_PO_Reversed_Encumb_Amount<-'
3475 || P_calling_sequence;
3476
3477 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3478
3479 l_log_msg := 'Begin of procedure '|| l_procedure_name;
3480 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3481 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3482 G_MODULE_NAME||l_procedure_name||'.begin',
3483 l_log_msg);
3484 END IF;
3485
3486 -----------------------------------------------------------
3487 l_log_msg := 'Start to Open the po_encumbrance_cur' ;
3488 -----------------------------------------------------------
3489 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3490 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3491 G_MODULE_NAME||l_procedure_name||'.begin',
3492 l_log_msg);
3493 END IF;
3494
3495
3496 OPEN po_enc_reversed_cur;
3497 FETCH po_enc_reversed_cur INTO
3498 l_unencumbered_amount;
3499
3500 IF (po_enc_reversed_cur%NOTFOUND) THEN
3501 -----------------------------------------------------------
3502 l_log_msg := 'NO encumbrance line exists' ;
3503 -----------------------------------------------------------
3504 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3505 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3506 G_MODULE_NAME||l_procedure_name||'.begin',
3507 l_log_msg);
3508 END IF;
3509
3510 l_unencumbered_amount := NULL;
3511 END IF;
3512
3513 CLOSE po_enc_reversed_cur;
3514
3515 -----------------------------------------------------------
3516 l_log_msg := 'close the cursor po_enc_reversed_cur' ;
3517 -----------------------------------------------------------
3518 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3519 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3520 G_MODULE_NAME||l_procedure_name||'.begin',
3521 l_log_msg);
3522 END IF;
3523
3524
3525 OPEN upgraded_po_enc_rev_cur;
3526 -----------------------------------------------------------
3527 l_log_msg := 'Open upgraded_po_enc_rev_cur' ;
3528 -----------------------------------------------------------
3529 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3530 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3531 G_MODULE_NAME||l_procedure_name||'.begin',
3532 l_log_msg);
3533 END IF;
3534
3535 FETCH upgraded_po_enc_rev_cur INTO
3536 l_upg_unencumbered_amount;
3537
3538 IF (upgraded_po_enc_rev_cur%NOTFOUND) THEN
3539 -----------------------------------------------------------
3540 l_log_msg := 'NO upgraded encumbrance reversals exist' ;
3541 -----------------------------------------------------------
3542 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3543 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3544 G_MODULE_NAME||l_procedure_name||'.begin',
3545 l_log_msg);
3546 END IF;
3547
3548 l_upg_unencumbered_amount := NULL;
3549 END IF;
3550
3551 CLOSE upgraded_po_enc_rev_cur;
3552
3553
3554 IF (l_unencumbered_amount is not null or l_upg_unencumbered_amount is not null) THEN
3555 l_total_unencumbered_amount := nvl(l_unencumbered_amount,0) + nvl(l_upg_unencumbered_amount,0);
3556 ELSE
3557 l_total_unencumbered_amount := NULL;
3558 END IF;
3559
3560 RETURN (l_total_unencumbered_amount);
3561
3562 EXCEPTION
3563 WHEN OTHERS THEN
3564
3565 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
3566 FND_LOG.STRING(G_LEVEL_EXCEPTION,
3567 G_MODULE_NAME || l_procedure_name,
3568 'EXCEPTION');
3569 END IF;
3570
3571 IF ( po_enc_reversed_cur%ISOPEN ) THEN
3572 CLOSE po_enc_reversed_cur;
3573 END IF;
3574
3575 IF ( upgraded_po_enc_rev_cur%ISOPEN ) THEN
3576 CLOSE upgraded_po_enc_rev_cur;
3577 END IF;
3578
3579 RAISE;
3580 END Get_PO_Reversed_Encumb_Amount;
3581
3582 /* Bug 5572876. Asset Book for Ledger is cached */
3583 Function Ledger_Asset_Book (P_ledger_id IN Number)
3584 Return Varchar2
3585 IS
3586 l_asset_book fa_book_controls.book_type_code%TYPE;
3587
3588 BEGIN
3589
3590
3591 If g_asset_book_code_t.count > 0 Then
3592
3593 If g_asset_book_code_t.exists(p_ledger_id) Then
3594
3595 l_asset_book := g_asset_book_code_t(p_ledger_id).asset_book_code;
3596
3597 Else
3598
3599 Begin
3600 SELECT book_type_code
3601 INTO l_asset_book
3602 FROM fa_book_controls fc
3603 WHERE fc.book_class = 'CORPORATE' -- bug 8843743: modify
3604 AND fc.set_of_books_id = p_ledger_id
3605 AND fc.date_ineffective IS NULL;
3606 Exception
3607 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
3608 l_asset_book := NULL;
3609 End;
3610
3611 g_asset_book_code_t(p_ledger_id).asset_book_code := l_asset_book;
3612
3613 End If;
3614
3615 Else
3616
3617 Begin
3618 SELECT book_type_code
3619 INTO l_asset_book
3620 FROM fa_book_controls fc
3621 WHERE fc.book_class = 'CORPORATE' -- bug 8843743: modify
3622 AND fc.set_of_books_id = p_ledger_id
3623 AND fc.date_ineffective IS NULL;
3624 Exception
3625 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
3626 l_asset_book := NULL;
3627 End;
3628
3629 g_asset_book_code_t(p_ledger_id).asset_book_code := l_asset_book;
3630
3631 End If;
3632
3633 Return (l_asset_book);
3634
3635 End Ledger_Asset_Book;
3636
3637
3638 --Function Get_CCR_Status, added for the R12 FSIO gap--
3639 --bug6053476
3640
3641 FUNCTION get_ccr_status(P_object_id IN NUMBER,
3642 P_object_type IN VARCHAR2
3643 )
3644 return VARCHAR2 IS
3645
3646 l_return_status VARCHAR2(255);
3647 l_msg_count NUMBER;
3648 l_msg_data VARCHAR2(255);
3649 l_ccr_id NUMBER;
3650 l_out_status VARCHAR2(1);
3651 l_error_code NUMBER;
3652
3653 BEGIN
3654
3655 FV_CCR_GRP.fv_is_ccr(
3656 p_api_version => 1.0,
3657 p_init_msg_list => FND_API.G_FALSE,
3658 P_object_id => P_object_id,
3659 P_object_type => P_object_type,
3660 x_return_status => l_return_status,
3661 x_msg_count => l_msg_count,
3662 x_msg_data => l_msg_data,
3663 x_ccr_id => l_ccr_id,
3664 x_out_status => l_out_status,
3665 x_error_code => l_error_code
3666 );
3667
3668 IF l_out_status is not Null THEN
3669 Return l_out_status;
3670 ELSE
3671 Return 'F';
3672 END IF;
3673
3674 EXCEPTION
3675 When Others Then
3676 Return 'F';
3677 END get_ccr_status;
3678
3679 /*--------------------------------------------------------------------------
3680 * * Function get_gl_natural_account
3681 * * Input parameters:
3682 * * p_coa_id: Chart of Accounts ID
3683 * * p_ccid: Code Combination ID
3684 * * This function returns the value of the natural segment
3685 * * of a CCID that is passed as input parameter to it.
3686 * *
3687 * * Remarks: Bug 6980939 - Added the Function.
3688 * *------------------------------------------------------------------------*/
3689
3690 FUNCTION get_gl_natural_account(
3691 p_coa_id IN NUMBER,
3692 p_ccid IN NUMBER,
3693 P_calling_sequence IN VARCHAR2 DEFAULT NULL
3694 )
3695 RETURN VARCHAR2 IS
3696
3697 l_success BOOLEAN;
3698 l_segment_num VARCHAR2(15);
3699 l_nat_account VARCHAR2(25);
3700 l_coa_ccid VARCHAR2(35); -- bug 7172942
3701 l_current_calling_sequence VARCHAR2(2000);
3702 l_debug_info VARCHAR2(2000);
3703 l_api_name CONSTANT VARCHAR2(100) := 'get_gl_natural_account';
3704 e_api_failure EXCEPTION;
3705
3706 BEGIN
3707
3708 l_current_calling_sequence := P_calling_sequence||'->'||'get_gl_natural_account';
3709
3710 l_debug_info := 'Begin of get_gl_natural_account';
3711 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3712 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
3713 END IF;
3714
3715 /* Bug 7172942 - Added caching logic to improve performance. */
3716
3717 l_coa_ccid := to_char(p_coa_id)||'-'||to_char(p_ccid);
3718
3719 IF (( g_natural_acct_seg_t.count > 0 ) AND (g_natural_acct_seg_t.exists(l_coa_ccid))) Then
3720 l_nat_account := g_natural_acct_seg_t(l_coa_ccid).natural_acct_seg;
3721 ELSE
3722 --Bug 7172942
3723 l_debug_info := 'Natural Segment not found in Cache';
3724 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3725 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
3726 END IF;
3727
3728 l_success := FND_FLEX_APIS.get_segment_column(101,
3729 'GL#',
3730 p_coa_id,
3731 'GL_ACCOUNT',
3732 l_segment_num);
3733
3734 l_debug_info := 'FND API returned Natural Account: '||To_Char(l_segment_num);
3735 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3736 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
3737 END IF;
3738
3739 IF (l_success = FALSE) THEN
3740 l_debug_info := 'FND API Failed';
3741 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3742 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
3743 END IF;
3744 RAISE e_api_failure;
3745 END IF;
3746
3747 l_debug_info := 'Dyn SQL to be run for ccid: '||p_ccid||' and CoA ID:'||p_coa_id;
3748 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3749 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
3750 END IF;
3751
3752 EXECUTE IMMEDIATE 'SELECT '|| l_segment_num ||
3753 ' from gl_code_combinations where code_combination_id = :a '
3754 INTO l_nat_account USING p_ccid;
3755
3756 l_debug_info := 'Natural account: '||l_nat_account||'. end of function call. ';
3757 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3758 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
3759 END IF;
3760
3761 --Bug 7172942
3762 g_natural_acct_seg_t(l_coa_ccid).natural_acct_seg := l_nat_account ;
3763 END IF;
3764 --End Bug 7172942
3765
3766 RETURN l_nat_account;
3767
3768 EXCEPTION
3769 WHEN OTHERS THEN
3770 IF (SQLCODE <> -20001 ) THEN
3771 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3772 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3773 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence);
3774 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
3775
3776 APP_EXCEPTION.RAISE_EXCEPTION;
3777
3778 END IF;
3779
3780 END get_gl_natural_account;
3781
3782 -- bug 7531219
3783 -- Function to validate balancing segment to the ledger
3784 FUNCTION is_balancing_segment_valid (
3785 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%type,
3786 p_balancing_segment_value IN gl_ledger_segment_values.segment_value%type,
3787 p_date IN DATE,
3788 p_calling_sequence IN VARCHAR2)
3789 RETURN BOOLEAN IS
3790 l_valid varchar2(1) := 'N';
3791 l_current_calling_sequence VARCHAR2(2000);
3792 l_debug_info VARCHAR2(2000);
3793 l_bal_seg_value_option_code varchar2(1);
3794 l_api_name CONSTANT VARCHAR2(100) := 'is_balancing_segment_valid';
3795
3796 BEGIN
3797 l_current_calling_sequence := P_calling_sequence||'->'||'is_balancing_segment_valid';
3798
3799 l_debug_info := 'Begin of is_balancing_segment_valid';
3800 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3801 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
3802 END IF;
3803
3804 select bal_seg_value_option_code
3805 into l_bal_seg_value_option_code
3806 from gl_ledgers
3807 where ledger_id = p_set_of_books_id;
3808
3809 if nvl(l_bal_seg_value_option_code, 'A') <> 'A'
3810 then
3811 begin
3812
3813 SELECT 'Y'
3814 INTO l_valid
3815 FROM gl_ledger_segment_values glsv
3816 WHERE glsv.segment_value = p_balancing_segment_value
3817 AND glsv.segment_type_code = 'B'
3818 AND glsv.ledger_id = p_set_of_books_id
3819 AND p_date BETWEEN NVL(glsv.start_date, p_date)
3820 AND NVL(glsv.end_date, p_date)
3821 AND rownum = 1;
3822
3823 exception
3824 when others then
3825 l_debug_info := 'invalid balancing segment: '||p_balancing_segment_value||' to the ledger: '||p_set_of_books_id;
3826 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3827 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
3828 END IF;
3829 return false;
3830 end;
3831 end if;
3832
3833 return true;
3834
3835 EXCEPTION
3836 WHEN OTHERS THEN
3837 IF (SQLCODE <> -20001 ) THEN
3838 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3839 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3840 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence);
3841 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
3842
3843 APP_EXCEPTION.RAISE_EXCEPTION;
3844
3845 END IF;
3846 END is_balancing_segment_valid;
3847
3848 -- Added for bug 8408345.
3849
3850 PROCEDURE Get_gl_date_and_period_1 (
3851 P_Date IN DATE,
3852 P_Receipt_Date IN DATE DEFAULT NULL,
3853 P_Period_Name OUT NOCOPY VARCHAR2,
3854 P_GL_Date OUT NOCOPY DATE,
3855 P_Batch_GL_Date IN DATE DEFAULT NULL,
3856 P_Org_Id IN NUMBER DEFAULT
3857 MO_GLOBAL.GET_CURRENT_ORG_ID)
3858 IS
3859 l_period_name gl_period_statuses.period_name%TYPE := '';
3860 l_current_date date := '';
3861 l_gl_date date := '';
3862 y_date date := '';
3863 n_date date := '';
3864
3865 BEGIN
3866
3867 -- Determine which date we should be using
3868
3869 -- First set up temporary variables y_date and n_date
3870
3871 IF (P_Batch_GL_Date is null) THEN
3872 IF (P_Receipt_Date is null) THEN
3873 y_date := TRUNC(SYSDATE);
3874 n_date := TRUNC(P_date);
3875 ELSE
3876 y_date := TRUNC(P_Receipt_Date);
3877 n_date := TRUNC(P_Receipt_Date);
3878 END IF;
3879 END IF;
3880
3881 -- MOAC. Added org_id parameter and predicate to select statement
3882 SELECT NVL(P_Batch_GL_Date,
3883 DECODE(SP.gl_date_from_receipt_flag,
3884 'S',TRUNC(SYSDATE),
3885 'Y',y_date,
3886 'N',n_date,
3887 TRUNC(P_Date)))
3888 INTO l_current_date
3889 FROM ap_system_parameters_all SP --5126689
3890 WHERE sp.org_id = p_org_id;
3891
3892 -- Initialize the IN OUT NOCOPY variables
3893 P_GL_Date := '';
3894 P_Period_Name := '';
3895
3896 -- See IF the period corresponding to P_Date is open
3897 -- Added org_id parameter to this call MOAC
3898 l_period_name := get_current_gl_date_no_cache(
3899 l_current_date, P_Org_Id);
3900
3901 IF (l_period_name is null) THEN
3902
3903 -- The date is in a closed period, roll forward until we find one
3904 -- MOAC. Added org_id parameter to call
3905 get_open_gl_date_no_cache
3906 (l_current_date,
3907 l_period_name,
3908 l_gl_date,
3909 P_Org_Id);
3910 ELSE
3911 -- No need to call the function. The GL date will be the
3912 -- date passed to the function
3913 l_gl_date := l_current_date;
3914 END IF;
3915
3916 P_Period_Name := l_Period_Name;
3917 P_GL_Date := l_GL_Date;
3918
3919 END Get_gl_date_and_period_1;
3920
3921 -- Added for bug 8408345.
3922
3923 function get_current_gl_date_no_cache (P_Date IN date,
3924 P_Org_ID IN number default
3925 mo_global.get_current_org_id) return varchar2
3926 is
3927 cursor l_current_cursor is
3928 SELECT period_name
3929 FROM gl_period_statuses GLPS,
3930 ap_system_parameters_all SP
3931 WHERE application_id = 200
3932 AND sp.org_id = P_Org_Id
3933 AND GLPS.set_of_books_id = SP.set_of_books_id
3934 AND trunc(P_Date) BETWEEN start_date AND end_date
3935 AND closing_status in ('O', 'F')
3936 AND NVL(adjustment_period_flag, 'N') = 'N';
3937
3938 l_period_name gl_period_statuses.period_name%TYPE := '';
3939 l_api_name CONSTANT VARCHAR2(200) := 'Get_Current_Gl_Date_No_Cache';
3940 l_debug_info Varchar2(2000);
3941
3942 begin
3943
3944 l_debug_info := 'Begining of Function';
3945 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3946 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3947 END IF;
3948
3949 open l_current_cursor;
3950 fetch l_current_cursor into l_period_name;
3951 close l_current_cursor;
3952
3953
3954 return (l_period_name);
3955
3956 end get_current_gl_date_no_cache;
3957
3958 -- Added for bug 8408345.
3959
3960 procedure get_open_gl_date_no_cache
3961 (P_Date IN date
3962 ,P_Period_Name OUT NOCOPY varchar2
3963 ,P_GL_Date OUT NOCOPY date
3964 ,P_Org_Id IN number DEFAULT
3965 mo_global.get_current_org_id)
3966 is
3967 cursor l_open_cursor is
3968 SELECT MIN(start_date),
3969 period_name
3970 FROM gl_period_statuses GLPS,
3971 ap_system_parameters_all SP
3972 WHERE application_id = 200
3973 AND sp.org_id = P_Org_Id
3974 AND GLPS.set_of_books_id = SP.set_of_books_id
3975 AND end_date >= P_Date --Bug6809792
3976 AND closing_status in ('O', 'F')
3977 AND NVL(adjustment_period_flag, 'N') = 'N'
3978 GROUP BY period_name
3979 ORDER BY MIN(start_date);
3980
3981 l_start_date date := '';
3982 l_period_name gl_period_statuses.period_name%TYPE := '';
3983 l_api_name CONSTANT VARCHAR2(200) := 'Get_Open_Gl_Date_No_Cache';
3984 l_debug_info Varchar2(2000);
3985
3986
3987 begin
3988
3989 l_debug_info := 'Begining of Function';
3990 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3991 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3992 END IF;
3993
3994 open l_open_cursor;
3995 fetch l_open_cursor into l_start_date, l_period_name;
3996 close l_open_cursor;
3997
3998 P_Period_Name := l_period_name;
3999 P_GL_Date := l_start_date;
4000
4001 end get_open_gl_date_no_cache;
4002
4003 /****************** FUNCTION get_ccr_reg_status ****************************/
4004
4005 --Start 8691645
4006
4007 FUNCTION get_ccr_reg_status(p_vendor_site_id IN AP_INVOICES.VENDOR_SITE_ID%TYPE)
4008 return VARCHAR2 IS
4009
4010 l_init_msg_list varchar2(1000);
4011 l_return_status varchar2(1);
4012 l_msg_count NUMBER;
4013 l_msg_data VARCHAR2(1000);
4014 l_vndr_ccr_status varchar2(1);
4015 l_error_code NUMBER;
4016 l_api_name CONSTANT VARCHAR2(200) := 'get_ccr_reg_status';
4017 l_debug_info Varchar2(2000);
4018
4019 BEGIN
4020
4021 l_debug_info := 'Begining of Function';
4022 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4023 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4024 END IF;
4025
4026 FV_CCR_GRP.FV_CCR_REG_STATUS(
4027 p_api_version => '1.0',
4028 p_init_msg_list => l_init_msg_list,
4029 p_vendor_site_id => p_vendor_site_id,
4030 x_return_status => l_return_status,
4031 x_msg_count => l_msg_count,
4032 x_msg_data => l_msg_data,
4033 x_ccr_status => l_vndr_ccr_status,
4034 x_error_code => l_error_code);
4035
4036 Return l_vndr_ccr_status;
4037 EXCEPTION
4038 When Others Then
4039 Return 'U'; --unexpected error
4040 END get_ccr_reg_status;
4041
4042 --End 8691645
4043
4044
4045 END AP_UTILITIES_PKG;
[Home] [Help]