[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_DB_AP_INT_PKG
Source
1 PACKAGE BODY AP_WEB_DB_AP_INT_PKG AS
2 /* $Header: apwdbapb.pls 120.51.12020000.2 2012/07/05 14:12:36 rveliche ship $ */
3
4 /* -------------------------------------------------------------------
5 -- Function to get from ap_system_parameters:
6 -- 1. func currency
7 -- 2. set of books id
8 -- 3. default template id
9 -- 4. default exchange rate type
10 -- 5. auto tax calc flag
11 -- 6. auto tax calc flag override
12 -- 7. amount includes tax override
13 -- ---------------------------------------------------------------- */
14 FUNCTION get_ap_system_params(
15 p_base_curr_code OUT NOCOPY apSetUp_baseCurrencyCode,
16 p_set_of_books_id OUT NOCOPY apSetUp_setOfBooksID,
17 p_expense_report_id OUT NOCOPY apSetUp_expenseReportID,
18 p_default_exch_rate_type OUT NOCOPY apSetUp_defaultExchRateType) RETURN BOOLEAN IS
19 begin
20
21 begin
22 select base_currency_code,
23 set_of_books_id,
24 expense_report_id,
25 default_exchange_rate_type
26 into p_base_curr_code,
27 p_set_of_books_id,
28 p_expense_report_id,
29 p_default_exch_rate_type
30 from ap_system_parameters;
31
32 return TRUE;
33
34 exception
35 when others then
36 p_base_curr_code := NULL;
37 p_set_of_books_id := NULL;
38 p_expense_report_id := NULL;
39 p_default_exch_rate_type := NULL;
40 return FALSE;
41 end;
42
43 end get_ap_system_params;
44
45 --------------------------------------------------------------------------------
46 FUNCTION GetCurrNameForCurrCode(
47 p_curr_code IN FND_CURRENCIES_VL.currency_code%TYPE,
48 p_curr_name OUT NOCOPY FND_CURRENCIES_VL.name%TYPE
49 ) RETURN BOOLEAN IS
50 --------------------------------------------------------------------------------
51 BEGIN
52 SELECT name
53 INTO p_curr_name
54 FROM fnd_currencies_vl
55 WHERE currency_code = p_curr_code;
56
57 RETURN TRUE;
58 EXCEPTION
59 WHEN NO_DATA_FOUND THEN
60 RETURN FALSE;
61
62 WHEN OTHERS THEN
63 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetCurrNameForCurrCode' );
64 APP_EXCEPTION.RAISE_EXCEPTION;
65 RETURN FALSE;
66 END GetCurrNameForCurrCode;
67
68 --------------------------------------------------------------------------------
69 FUNCTION GetBaseCurrInfo(
70 p_base_curr_code OUT NOCOPY apSetUp_baseCurrencyCode
71 ) RETURN BOOLEAN IS
72 --------------------------------------------------------------------------------
73 l_set_of_books_id apSetup_setOfBooksID;
74 l_expense_report_id apSetup_expenseReportID;
75 l_default_exch_rate_type apSetUp_defaultExchRateType;
76 BEGIN
77 return AP_WEB_DB_AP_INT_PKG.get_ap_system_params(p_base_curr_code => p_base_curr_code,
78 p_set_of_books_id => l_set_of_books_id,
79 p_expense_report_id => l_expense_report_id,
80 p_default_exch_rate_type => l_default_exch_rate_type);
81
82 EXCEPTION
83 WHEN NO_DATA_FOUND THEN
84 RETURN FALSE;
85
86 WHEN OTHERS THEN
87 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetBaseCurrInfo' );
88 APP_EXCEPTION.RAISE_EXCEPTION;
89 RETURN FALSE;
90
91 END GetBaseCurrInfo;
92
93 -------------------------------------------------------------------
94 FUNCTION GetSOB(
95 p_set_of_books_id OUT NOCOPY glsob_setOfBooksID
96 ) RETURN BOOLEAN IS
97 -------------------------------------------------------------------
98 l_base_curr_code apSetup_baseCurrencyCode;
99 l_expense_report_id apSetup_expenseReportID;
100 l_default_exch_rate_type apSetUp_defaultExchRateType;
101
102 BEGIN
103 return AP_WEB_DB_AP_INT_PKG.get_ap_system_params(p_base_curr_code => l_base_curr_code,
104 p_set_of_books_id => p_set_of_books_id,
105 p_expense_report_id => l_expense_report_id,
106 p_default_exch_rate_type => l_default_exch_rate_type);
107
108 EXCEPTION
109 WHEN NO_DATA_FOUND THEN
110 RETURN FALSE;
111
112 WHEN OTHERS THEN
113 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetSOB' );
114 APP_EXCEPTION.RAISE_EXCEPTION;
115 RETURN FALSE;
116
117 END GetSOB;
118
119
120 -------------------------------------------------------------------
121 FUNCTION GetCOAofSOB(
122 p_chart_of_accounts OUT NOCOPY glsob_chartOfAccountsID
123 ) RETURN BOOLEAN IS
124 -------------------------------------------------------------------
125 BEGIN
126 select GS.chart_of_accounts_id
127 into p_chart_of_accounts
128 from ap_system_parameters S,
129 gl_sets_of_books GS
130 where GS.set_of_books_id = S.set_of_books_id;
131
132 return TRUE;
133
134 EXCEPTION
135 WHEN NO_DATA_FOUND THEN
136 RETURN FALSE;
137
138 WHEN OTHERS THEN
139 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetCOAOfSOB' );
140 APP_EXCEPTION.RAISE_EXCEPTION;
141 RETURN FALSE;
142
143 END GetCOAofSOB;
144
145 -------------------------------------------------------------------
146 FUNCTION GetAPSysCurrencySetupInfo(p_sys_info_rec OUT NOCOPY APSysInfoRec
147 ) RETURN BOOLEAN IS
148 -------------------------------------------------------------------
149 l_default_exch_rate_type apSetUp_defaultExchRateType;
150
151 BEGIN
152 AP_WEB_DB_AP_INT_PKG.GetDefaultExchange(l_default_exch_rate_type);
153
154 SELECT s.base_currency_code,
155 l_default_exch_rate_type,
156 c.name,
157 nvl(s.multi_currency_flag, 'N')
158 INTO p_sys_info_rec.base_currency,
159 p_sys_info_rec.default_exchange_rate_type,
160 p_sys_info_rec.base_curr_name,
161 p_sys_info_rec.sys_multi_curr_flag
162 FROM ap_system_parameters s,
163 fnd_currencies_vl c
164 WHERE c.currency_code = s.base_currency_code;
165
166 RETURN TRUE;
167 EXCEPTION
168 WHEN NO_DATA_FOUND THEN
169 RETURN FALSE;
170 WHEN OTHERS THEN
171 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetAPSysCUrrencySetupInfo' );
172 APP_EXCEPTION.RAISE_EXCEPTION;
173 RETURN FALSE;
174
175 END GetAPSysCurrencySetupInfo;
176
177 ------------------------------------------------------------------
178 PROCEDURE GetDefaultExchange(
179 p_default_exchange_rate_type OUT NOCOPY VARCHAR2
180 ) IS
181 ------------------------------------------------------------------
182 BEGIN
183 -- Bug 8463457 To default the Exchange rate type from OIE Setup,
184 -- if NO_DATA_FOUND then from AP Setup
185 SELECT s.exchange_rate_type
186 INTO p_default_exchange_rate_type
187 FROM ap_pol_exrate_options s
188 WHERE enabled = 'Y';
189
190 EXCEPTION
191 WHEN NO_DATA_FOUND THEN
192
193 BEGIN
194 SELECT s.default_exchange_rate_type
195 INTO p_default_exchange_rate_type
196 FROM ap_system_parameters s,
197 fnd_currencies_vl c
198 WHERE c.currency_code = s.base_currency_code;
199
200 EXCEPTION
201 WHEN NO_DATA_FOUND THEN
202 null;
203 WHEN OTHERS THEN
204 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetDefaultExchange' );
205 APP_EXCEPTION.RAISE_EXCEPTION;
206 END;
207
208 WHEN OTHERS THEN
209 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetDefaultExchange' );
210 APP_EXCEPTION.RAISE_EXCEPTION;
211
212 END GetDefaultExchange;
213
214
215
216
217
218 ----------------------------------------------------------
219 FUNCTION GetVendorInfoOfEmp(
220 p_employee_id IN vendors_employeeID,
221 p_vendor_id OUT NOCOPY vendors_vendorID,
222 p_vend_pay_curr_code OUT NOCOPY vendors_paymentCurrCode,
223 p_vend_pay_curr_name OUT NOCOPY FND_CURRENCIES_VL.name%TYPE
224 ) RETURN BOOLEAN IS
225 ----------------------------------------------------------
226 l_expense_check_address_flag VARCHAR2(1);
227 l_get_from_vendor VARCHAR2(1) := 'N';
228 l_vendor_id VARCHAR2(15);
229 BEGIN
230 -- 3176205: Inactive Employees and contingent workers
231 -- The following query includes all workers except for
232 -- terminated contingent workers and terminated employees
233 -- who are now active contingent workers.
234 BEGIN
235 -- Bug 6978871(sodash) get the vendor_id
236 SELECT expense_check_address_flag, vendor_id
237 INTO l_expense_check_address_flag, l_vendor_id
238 FROM (
239 SELECT emp.expense_check_address_flag, null vendor_id
240 FROM per_employees_x emp
241 WHERE emp.employee_id = p_employee_id
242 AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
243 UNION ALL
244 SELECT emp.expense_check_address_flag, vendor_id
245 FROM per_cont_workers_current_x emp
246 WHERE emp.person_id = p_employee_id
247 );
248
249 IF l_expense_check_address_flag IS NULL THEN -- Bug 6978871(sodash) if not set at emp then get from financial option
250 SELECT expense_check_address_flag
251 INTO l_expense_check_address_flag
252 FROM financials_system_parameters;
253 END IF;
254
255 IF l_expense_check_address_flag IS NOT NULL THEN
256 BEGIN
257 --Bug 13604446: To default the currency properly, when the language changed
258 SELECT vdr.vendor_id, site.payment_currency_code, c.name
259 INTO p_vendor_id, p_vend_pay_curr_code, p_vend_pay_curr_name
260 FROM ap_suppliers vdr, ap_supplier_sites site, fnd_currencies_vl c
261 WHERE site.vendor_id = vdr.vendor_id
262 AND ( (vdr.employee_id = p_employee_id and l_vendor_id is null ) OR (l_vendor_id is not null and l_vendor_id = vdr.vendor_id)) -- Bug 6978871(sodash) ,#12916260
263 AND c.currency_code(+) = site.payment_currency_code
264 AND upper(site.vendor_site_code) IN
265 (SELECT upper(meaning)
266 FROM fnd_lookup_values
267 WHERE lookup_code = l_expense_check_address_flag
268 AND lookup_type = 'HOME_OFFICE');
269 IF p_vend_pay_curr_code IS NULL THEN
270 l_get_from_vendor := 'Y';
271 END IF;
272 EXCEPTION
273 WHEN no_data_found THEN
274 l_get_from_vendor := 'Y';
275 END;
276 ELSE
277 l_get_from_vendor := 'Y';
278 END IF;
279
280 EXCEPTION
281 WHEN no_data_found THEN
282 l_get_from_vendor := 'Y';
283 END;
284
285 IF l_get_from_vendor = 'Y' THEN
286 SELECT vendor_id,
287 payment_currency_code,
288 c.name
289 INTO p_vendor_id,
290 p_vend_pay_curr_code,
291 p_vend_pay_curr_name
292 FROM ap_suppliers v,
293 fnd_currencies_vl c
294 WHERE ( ( v.employee_id = p_employee_id and l_vendor_id is null ) OR (l_vendor_id is not null and l_vendor_id = v.vendor_id)) -- Bug 6978871(sodash),#12916260
295 AND c.currency_code(+) = v.payment_currency_code;
296 END IF;
297
298
299 RETURN TRUE;
300 EXCEPTION
301 WHEN NO_DATA_FOUND THEN
302 RETURN FALSE;
303
304 WHEN OTHERS THEN
305 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetVendorInfoOfEmp' );
306 APP_EXCEPTION.RAISE_EXCEPTION;
307 RETURN FALSE;
308
309 END GetVendorInfoOfEmp;
310
311
312 ----------------------------------------------------------
313 FUNCTION GetVendorAWTSetupForExpRpt(
314 p_report_header_id IN expHdr_headerID,
315 p_ven_allow_awt_flag OUT NOCOPY vendors_allowAWTFlag,
316 p_ven_awt_group_id OUT NOCOPY vendors_awtGroupID
317 ) RETURN BOOLEAN IS
318 ----------------------------------------------------------
319 BEGIN
320 SELECT nvl(PV.allow_awt_flag, 'N'),
321 PV.awt_group_id
322 INTO p_ven_allow_awt_flag,
323 p_ven_awt_group_id
324 FROM ap_suppliers PV,
325 ap_expense_report_headers RH
326 WHERE RH.report_header_id = p_report_header_id
327 AND PV.employee_id = RH.employee_id;
328
329 RETURN TRUE;
330
331 EXCEPTION
332 WHEN NO_DATA_FOUND THEN
333 RETURN FALSE;
334
335 WHEN OTHERS THEN
336 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetVendorAWTSetupForExpRpt' );
337 APP_EXCEPTION.RAISE_EXCEPTION;
338 RETURN FALSE;
339
340 END GetVendorAWTSetupForExpRpt;
341
342
343 -------------------------------------------------------------------
344 FUNCTION GetVendorID(
345 p_employee_id IN vendors_employeeID,
346 p_vendor_id OUT NOCOPY vendors_vendorID
347 ) RETURN BOOLEAN IS
348 -------------------------------------------------------------------
349 BEGIN
350 SELECT vendor_id
351 INTO p_vendor_id
352 FROM ap_suppliers
353 WHERE employee_id = p_employee_id;
354
355 return TRUE;
356
357 EXCEPTION
358 WHEN NO_DATA_FOUND THEN
359 RETURN FALSE;
360
361 WHEN OTHERS THEN
362 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetVendorID' );
363 APP_EXCEPTION.RAISE_EXCEPTION;
364 RETURN FALSE;
365 END GetVendorID;
366
367 -------------------------------------------------------------------
368 FUNCTION GetVendorSitesCodeCombID(
369 p_vendor_site_id IN vendorSites_vendorSiteID,
370 p_code_comb_id OUT NOCOPY vendorSites_acctsPayCodeCombID
371 ) RETURN BOOLEAN IS
372 -------------------------------------------------------------------
373 BEGIN
374 SELECT accts_pay_code_combination_id
375 INTO p_code_comb_id
376 FROM ap_supplier_sites
377 WHERE vendor_site_id = p_vendor_site_id;
378
379 return TRUE;
380
381 EXCEPTION
382 WHEN NO_DATA_FOUND THEN
383 RETURN FALSE;
384 WHEN OTHERS THEN
385 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetVendorSitesCodeCombID' );
386 APP_EXCEPTION.RAISE_EXCEPTION;
387 RETURN FALSE;
388 END GetVendorSitesCodeCombID;
389
390 -------------------------------------------------------------------
391 FUNCTION GetVendorCodeCombID(
392 p_vendor_id IN vendors_vendorID,
393 p_accts_pay OUT NOCOPY vendors_acctsPayCodeCombID
394 ) RETURN BOOLEAN IS
395 -------------------------------------------------------------------
396 BEGIN
397 SELECT accts_pay_code_combination_id
398 INTO p_accts_pay
399 FROM ap_suppliers
400 WHERE vendor_id = p_vendor_id;
401
402 return TRUE;
403
404 EXCEPTION
405 WHEN NO_DATA_FOUND THEN
406 RETURN FALSE;
407 WHEN OTHERS THEN
408 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetVendorCodeCombID' );
409 APP_EXCEPTION.RAISE_EXCEPTION;
410 RETURN FALSE;
411 END GetVendorCodeCombID;
412
413 -------------------------------------------------------------------
414 FUNCTION GetPayGroupLookupCode(
415 p_vendor_id IN vendorSites_vendorID,
416 p_vendor_site_id IN vendorSites_vendorSiteID,
417 p_pay_group_code OUT NOCOPY vendorSites_payGroupLookupCode
418 ) RETURN BOOLEAN IS
419 -------------------------------------------------------------------
420 BEGIN
421 SELECT pay_group_lookup_code
422 INTO p_pay_group_code
423 FROM ap_supplier_sites
424 WHERE vendor_id = p_vendor_id
425 AND vendor_site_id = p_vendor_site_id;
426
427 return TRUE;
428
429 EXCEPTION
430 WHEN NO_DATA_FOUND THEN
431 RETURN FALSE;
432
433 WHEN OTHERS THEN
434 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetPayGroupLookupCode' );
435 APP_EXCEPTION.RAISE_EXCEPTION;
436 RETURN FALSE;
437 END GetPayGroupLookupCode;
438
439 --------------------------------------------------------------------------------
440 FUNCTION GetNextInvoiceId(
441 p_invoice_id OUT NOCOPY NUMBER
442 ) RETURN BOOLEAN IS
443 --------------------------------------------------------------------------------
444 BEGIN
445 SELECT ap_invoices_interface_s.nextval
446 INTO p_invoice_id
447 FROM sys.dual;
448
449 return TRUE;
450
451 EXCEPTION
452 WHEN NO_DATA_FOUND THEN
453 return FALSE;
454 WHEN OTHERS THEN
455 AP_WEB_DB_UTIL_PKG.RaiseException('GetNextInvoiceId');
456 APP_EXCEPTION.RAISE_EXCEPTION;
457 return FALSE;
458 END GetNextInvoiceId;
459
460 --------------------------------------------------------------------------------
461 FUNCTION GetNextInvoiceLineId(p_invoice_line_id OUT NOCOPY NUMBER) RETURN BOOLEAN IS
462 --------------------------------------------------------------------------------
463 BEGIN
464 SELECT ap_invoice_lines_interface_s.nextval
465 INTO p_invoice_line_id
466 FROM sys.dual;
467
468 return TRUE;
469 EXCEPTION
470 WHEN NO_DATA_FOUND THEN
471 return FALSE;
472 WHEN OTHERS THEN
473 AP_WEB_DB_UTIL_PKG.RaiseException('GetNextInvoiceLineId');
474 APP_EXCEPTION.RAISE_EXCEPTION;
475 return FALSE;
476 END GetNextInvoiceLineId;
477
478
479 -------------------------------------------------------------------
480 FUNCTION ApproverHasAuthority(
481 p_approver_id IN signingLimits_employeeID,
482 p_doc_cost_center IN signingLimits_costCenter,
483 p_approval_amount IN NUMBER,
484 p_reimb_precision IN FND_CURRENCIES_VL.PRECISION%TYPE,
485 p_item_type IN signingLimits_docType,
486 p_payment_curr_code IN VARCHAR2,
487 p_week_end_date IN DATE,
488 p_has_authority OUT NOCOPY BOOLEAN
489 ) RETURN BOOLEAN IS
490 -------------------------------------------------------------------
491 l_signingLimitRate NUMBER;
492
493 TYPE SigningLimitRecTyp IS RECORD (
494 l_signingLimit NUMBER,
495 l_signingCurrencyCode ap_system_parameters_all.base_currency_code%TYPE,
496 l_exchangeRateType VARCHAR2(30));
497
498 TYPE SigningLimitCurTyp IS REF CURSOR RETURN SigningLimitRecTyp;
499
500 SigningLimit SigningLimitRecTyp;
501 SigningCurrencyCodes SigningLimitCurTyp; -- Cursor Variable
502
503 l_doc_cost_center signingLimits_costCenter;
504 l_alphanumeric_allowed_flag fnd_flex_value_sets.alphanumeric_allowed_flag%type;
505 l_uppercase_only_flag fnd_flex_value_sets.uppercase_only_flag%type;
506 l_numeric_mode_enabled_flag fnd_flex_value_sets.numeric_mode_enabled_flag%type;
507 l_max_size fnd_flex_value_sets.maximum_size%type;
508
509 BEGIN
510
511 AP_WEB_ACCTG_PKG.GetCostCenterApprovalRule(
512 p_alphanumeric_allowed_flag => l_alphanumeric_allowed_flag,
513 p_uppercase_only_flag => l_uppercase_only_flag,
514 p_numeric_mode_enabled_flag => l_numeric_mode_enabled_flag,
515 p_maximum_size => l_max_size);
516
517 -- Bug : 2234644 as per AOL team when the valueset is upper case only.
518 -- For that type of value set, upper case values are allowed to be
519 -- defined and if user enters a lower case then we make some assumptions.
520 -- hence converting to upper case when Uppercase Only (A-Z) is checked.
521 -- making same assumption as AOL team
522 IF (l_uppercase_only_flag = 'Y') THEN
523 l_doc_cost_center := upper(p_doc_cost_center);
524 ELSE
525 l_doc_cost_center := p_doc_cost_center;
526 END IF;
527
528 --Bug 3484668:Support for Right Justify Zero Fill is enabled.
529
530 IF (l_numeric_mode_enabled_flag = 'Y') THEN
531 --
532 -- Right Justify Zero Fill is enabled.
533 --
534 l_doc_cost_center := Nvl(Rtrim(Ltrim(l_doc_cost_center)),'0');
535 -- Bug: 5586280
536 IF (NOT AP_WEB_UTILITIES_PKG.ContainsChars(l_doc_cost_center)) THEN
537 l_doc_cost_center := Lpad(l_doc_cost_center, l_max_size, '0');
538 END IF;
539 END IF;
540
541 -- N=>Numbers Only (is checked for the valueset)
542 -- Y=>Numbers Only (is unchecked for the valueset)
543 IF (l_alphanumeric_allowed_flag = 'N') THEN
544 OPEN SigningCurrencyCodes FOR
545 select s.signing_limit, p.base_currency_code,
546 nvl((SELECT exchange_rate_type FROM ap_pol_exrate_options WHERE enabled = 'Y'),p.default_exchange_rate_type)
547 from ap_system_parameters p,
548 ap_web_signing_limits s
549 where employee_id = p_approver_id
550 and to_number(cost_center) = to_number(l_doc_cost_center)
551 and document_type = p_item_type;
552 ELSE
553 OPEN SigningCurrencyCodes FOR
554 select s.signing_limit, p.base_currency_code,
555 nvl((SELECT exchange_rate_type FROM ap_pol_exrate_options WHERE enabled = 'Y'),p.default_exchange_rate_type)
556 from ap_system_parameters p,
557 ap_web_signing_limits s
558 where employee_id = p_approver_id
559 and cost_center = l_doc_cost_center
560 and document_type = p_item_type;
561 END IF;
562
563 p_has_authority := FALSE;
564
565 /* loop throught all of the rows where employee_id, cost_center, and
566 document_type match with the expense report but the approver might
567 under different orgs */
568 LOOP
569 FETCH SigningCurrencyCodes
570 into SigningLimit;
571 EXIT WHEN SigningCurrencyCodes%NOTFOUND;
572
573 -- get exchange rate between the signing limit currency code and
574 -- reimbursement currency code
575 l_signingLimitRate := AP_UTILITIES_PKG.get_exchange_rate(
576 SigningLimit.l_SigningCurrencyCode,
577 p_payment_curr_code,
578 SigningLimit.l_exchangeRateType,
579 p_week_end_date,
580 'ApproverHasAuthority');
581
582 IF round(SigningLimit.l_signingLimit * l_signingLimitRate, p_reimb_precision) >= round(p_approval_amount, p_reimb_precision) THEN
583 p_has_authority := TRUE;
584 EXIT;
585 END IF;
586
587 END LOOP;
588
589 CLOSE SigningCurrencyCodes;
590
591 RETURN TRUE;
592
593 EXCEPTION
594 WHEN NO_DATA_FOUND THEN
595 RETURN FALSE;
596
597 WHEN OTHERS THEN
598 AP_WEB_DB_UTIL_PKG.RaiseException( 'ApproverHasAuthority');
599 APP_EXCEPTION.RAISE_EXCEPTION;
600 return FALSE;
601
602 END ApproverHasAuthority;
603
604
605
606 /*Bug 2743726: New procedure for checking cost center value set
607 alphanumeric_flag is checked or not.
608 */
609
610 -------------------------------------------------------------------
611 PROCEDURE IsCostCenterUpperCase(
612 p_doc_cost_center IN VARCHAR2,
613 Is_Cost_Center_UpperCase_flag OUT NOCOPY VARCHAR2
614 ) IS
615 -------------------------------------------------------------------
616
617 l_alphanumeric_allowed_flag fnd_flex_value_sets.alphanumeric_allowed_flag%type;
618 l_uppercase_only_flag fnd_flex_value_sets.uppercase_only_flag%type;
619 l_numeric_mode_enabled_flag fnd_flex_value_sets.numeric_mode_enabled_flag%type;
620 l_max_size fnd_flex_value_sets.maximum_size%type;
621 l_doc_cost_center VARCHAR2(2000);
622
623 BEGIN
624
625 AP_WEB_ACCTG_PKG.GetCostCenterApprovalRule(
626 p_alphanumeric_allowed_flag => l_alphanumeric_allowed_flag,
627 p_uppercase_only_flag => l_uppercase_only_flag,
628 p_numeric_mode_enabled_flag => l_numeric_mode_enabled_flag,
629 p_maximum_size => l_max_size);
630
631 Is_Cost_Center_UpperCase_flag := nvl(l_uppercase_only_flag, 'N');
632
633 EXCEPTION
634 WHEN NO_DATA_FOUND THEN
635 Is_Cost_Center_UpperCase_flag := 'N';
636 WHEN OTHERS THEN
637 Is_Cost_Center_UpperCase_flag := 'N';
638
639 END IsCostCenterUpperCase;
640
641 --------------------------------------------------------------------------------
642
643 --------------------------------------------------------------------------------
644 FUNCTION CostCenterValid(
645 p_cost_center IN expFeedDists_costCenter,
646 p_valid OUT NOCOPY BOOLEAN,
647 p_employee_id IN NUMBER
648 ) RETURN BOOLEAN IS
649 --------------------------------------------------------------------------------
650 l_valid VARCHAR2(1) := 'N';
651
652 l_employee_id number;
653 l_chart_of_accounts_id AP_WEB_DB_AP_INT_PKG.glsob_chartOfAccountsID;
654 l_default_emp_ccid AP_WEB_DB_HR_INT_PKG.empCurrent_defaultCodeCombID;
655
656 /*Bug 2690715 : variable declarations */
657
658
659 l_parent_flex_value_set_id fnd_flex_value_sets.parent_flex_value_set_id%type;
660
661 l_emp_set_of_books_id AP_WEB_DB_AP_INT_PKG.glsob_setOfBooksID;
662 l_ou_chart_of_accounts_id AP_WEB_DB_AP_INT_PKG.glsob_chartOfAccountsID;
663 l_emp_chart_of_accounts_id AP_WEB_DB_AP_INT_PKG.glsob_chartOfAccountsID;
664
665 /*Bug 2699258:Added proper join conditions between S,GS,HR
666 so that Merge Join Cartesians.
667 */
668
669 -- 3176205: Inactive Employees and contingent workers
670 -- The following query includes all workers.
671 /* Bug 3916349/4042775 - comment
672 cursor c1(p_employee_id IN number) is
673 SELECT GS.chart_of_accounts_id,
674 HR.default_code_combination_id
675 FROM ap_system_parameters S,
676 gl_sets_of_books GS,
677 per_workforce_x HR
678 WHERE HR.person_id = p_employee_id
679 AND GS.set_of_books_id = S.set_of_books_id
680 AND S.set_of_books_id = nvl(HR.set_of_books_id,S.set_of_books_id)
681 AND rownum = 1;
682 */
683
684 BEGIN
685
686 l_employee_id := nvl(p_employee_id, AP_WEB_DB_HR_INT_PKG.getemployeeid);
687
688 /* Bug 3916349/4042775 - comment
689 for i in c1(l_employee_id) loop
690 l_chart_of_accounts_id := i.chart_of_accounts_id;
691 l_default_emp_ccid := i.default_code_combination_id;
692 end loop;
693 */
694
695 SELECT set_of_books_id, default_code_combination_id
696 INTO l_emp_set_of_books_id, l_default_emp_ccid
697 FROM (
698 SELECT emp.set_of_books_id, emp.default_code_combination_id
699 FROM per_employees_x emp
700 WHERE emp.employee_id = l_employee_id
701 AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
702 UNION ALL
703 SELECT emp.set_of_books_id, emp.default_code_combination_id
704 FROM per_cont_workers_current_x emp
705 WHERE emp.person_id = l_employee_id
706 );
707
708 -- Get the chart_of_account_id from system parameters
709 IF (NOT AP_WEB_DB_AP_INT_PKG.GetCOAofSOB(l_ou_chart_of_accounts_id)) THEN
710 l_ou_chart_of_accounts_id := null;
711 END IF;
712
713 IF (l_emp_set_of_books_id is not null) THEN
714 SELECT GS.chart_of_accounts_id
715 INTO l_emp_chart_of_accounts_id
716 FROM gl_sets_of_books GS
717 WHERE GS.set_of_books_id = l_emp_set_of_books_id;
718
719 IF (l_emp_chart_of_accounts_id <> l_ou_chart_of_accounts_id) THEN
720 p_valid := FALSE;
721 return FALSE;
722 END IF;
723 END IF;
724
725 l_chart_of_accounts_id := l_ou_chart_of_accounts_id;
726
727 AP_WEB_ACCTG_PKG.ValidateCostCenter(
728 p_cost_center => p_cost_center,
729 p_employee_id => l_employee_id,
730 p_emp_set_of_books_id =>l_emp_set_of_books_id,
731 p_default_emp_ccid => l_default_emp_ccid,
732 p_chart_of_accounts_id => l_chart_of_accounts_id,
733 p_cost_center_valid => p_valid);
734
735 return p_valid;
736
737 EXCEPTION
738 WHEN NO_DATA_FOUND THEN
739 p_valid := FALSE;
740 RETURN FALSE;
741
742 WHEN OTHERS THEN
743 AP_WEB_DB_UTIL_PKG.RaiseException( 'CostCenterValid' );
744 APP_EXCEPTION.RAISE_EXCEPTION;
745 return FALSE;
746
747 END CostCenterValid;
748
749
750 --------------------------------------------------------------------------------
751 FUNCTION GetExpenseClearingCCID(
752 p_ccid OUT NOCOPY NUMBER,
753 p_card_program_id IN NUMBER,
754 p_employee_id IN NUMBER,
755 p_as_of_date IN DATE
756 ) RETURN BOOLEAN IS
757 -------------------------------------------------------------------------------
758 l_data_feed_level_code ap_card_programs.data_feed_level_code%type;
759 l_default_emp_ccid ap_expense_report_headers_all.employee_ccid%type;
760 l_expense_clearing_ccid ap_expense_report_headers_all.employee_ccid%type;
761 l_chart_of_accounts_id NUMBER;
762 l_num_segments NUMBER;
763 l_company_seg_num NUMBER;
764 l_flex_segment_delimiter varchar2(1);
765 l_concatenated_segments varchar2(2000);
766 l_debug_info varchar2(2000);
767
768 l_cp_clearing_account_segments FND_FLEX_EXT.SEGMENTARRAY;
769 l_default_emp_segments FND_FLEX_EXT.SEGMENTARRAY;
770
771 BEGIN
772
773 -- Bug: 11743748
774 SELECT expense_clearing_ccid, nvl(data_feed_level_code,'N')
775 INTO l_expense_clearing_ccid, l_data_feed_level_code
776 FROM ap_card_programs_all
777 WHERE card_program_id = p_card_program_id;
778
779 if (l_expense_clearing_ccid IS NULL) then
780 SELECT EXPENSE_CLEARING_CCID
781 INTO l_expense_clearing_ccid
782 FROM FINANCIALS_SYSTEM_PARAMETERS;
783 end if;
784
785 -- if data_feed_level_code is Y then overlay company segment from employee a/c
786 if l_data_feed_level_code = 'Y' then
787 -----------------------------------------------------
788 l_debug_info := 'Get the HR defaulted Employee CCID';
789 -----------------------------------------------------
790 begin
791 SELECT pera.default_code_comb_id
792 INTO l_default_emp_ccid
793 FROM per_assignments_f pera,
794 per_assignment_status_types peras
795 WHERE pera.person_id = p_employee_id
796 AND pera.assignment_status_type_id = peras.assignment_status_type_id
797 AND trunc(p_as_of_date) between pera.effective_start_date and pera.effective_end_date
798 AND pera.assignment_type in ('C', 'E')
799 AND pera.primary_flag='Y'
800 AND peras.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK');
801 exception
802 when no_data_found then
803 FND_MESSAGE.Set_Name('SQLAP', 'AP_WEB_EXP_MISSING_EMP_CCID');
804 RAISE AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
805 end;
806
807 -----------------------------------------------------
808 l_debug_info := 'Get the Employee Chart of Accounts ID';
809 -----------------------------------------------------
810 IF (NOT AP_WEB_DB_EXPRPT_PKG.GetChartOfAccountsID(
811 p_employee_id => p_employee_id,
812 p_chart_of_accounts_id => l_chart_of_accounts_id)) THEN
813 NULL;
814 END IF;
815
816 IF (l_chart_of_accounts_id is null) THEN
817 FND_MESSAGE.Set_Name('SQLAP', 'OIE_MISS_CHART_OF_ACC_ID');
818 RAISE AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
819 END IF;
820
821 -----------------------------------------------------------------
822 l_debug_info := 'Get employee default ccid account segments';
823 -----------------------------------------------------------------
824 IF (l_default_emp_ccid IS NOT NULL) THEN
825 IF (NOT FND_FLEX_EXT.GET_SEGMENTS(
826 'SQLGL',
827 'GL#',
828 l_chart_of_accounts_id,
829 l_default_emp_ccid,
830 l_num_segments,
831 l_default_emp_segments)) THEN
832 RAISE AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
833 END IF; /* GET_SEGMENTS */
834 END IF;
835
836
837 -----------------------------------------------------------------
838 l_debug_info := 'Get card program clearing account segments';
839 -----------------------------------------------------------------
840 IF (l_expense_clearing_ccid IS NOT NULL) THEN
841 IF (NOT FND_FLEX_EXT.GET_SEGMENTS(
842 'SQLGL',
843 'GL#',
844 l_chart_of_accounts_id,
845 l_expense_clearing_ccid,
846 l_num_segments,
847 l_cp_clearing_account_segments)) THEN
848 RAISE AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
849 END IF; /* GET_SEGMENTS */
850 END IF;
851
852 ----------------------------------------
853 l_debug_info := 'Get Company Segment';
854 ----------------------------------------
855 IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
856 101,
857 'GL#',
858 l_chart_of_accounts_id,
859 'GL_BALANCING',
860 l_company_seg_num)) THEN
861 null;
862 END IF;
863
864
865 -- Overlay cardprogram clearing a/c company segment with
866 -- employee default a/c company segment
867 if l_company_seg_num is not null then
868 l_cp_clearing_account_segments(l_company_seg_num) := l_default_emp_segments(l_company_seg_num);
869 end if;
870
871 ----------------------------------------
872 l_debug_info := 'Get Segment Delimiter like .';
873 ----------------------------------------
874 l_flex_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
875 'SQLGL',
876 'GL#',
877 l_chart_of_accounts_id);
878
879 IF (l_flex_segment_delimiter IS NULL) THEN
880 RAISE AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
881 END IF;
882
883
884 --------------------------------------------------------------
885 l_debug_info := 'Get Concatenate segments to retrieve new clearing ccid';
886 --------------------------------------------------------------
887 l_concatenated_segments := FND_FLEX_EXT.concatenate_segments(l_num_segments,
888 l_cp_clearing_account_segments,
889 l_flex_segment_delimiter);
890
891 ------------------------------------------------------------------------
892 l_debug_info := 'calling FND_FLEX_KEYVAL.validate_segs';
893 ------------------------------------------------------------------------
894 -- Bug: 7699146, Replaced CREATE_COMB_NO_AT with CREATE_COMBINATION
895 IF (FND_FLEX_KEYVAL.validate_segs('CREATE_COMBINATION',
896 'SQLGL',
897 'GL#',
898 l_chart_of_accounts_id,
899 l_concatenated_segments)) THEN
900
901 p_ccid := FND_FLEX_KEYVAL.combination_id;
902
903 ELSE
904 l_debug_info := substr(FND_FLEX_KEYVAL.error_message, 1800);
905 FND_MESSAGE.set_encoded(FND_FLEX_KEYVAL.encoded_error_message);
906 fnd_msg_pub.add();
907 RAISE AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR;
908
909 END IF; /* FND_FLEX_KEYVAL.validate_segs */
910
911
912 else -- if l_data_feed_level_code = 'Y' then
913
914 p_ccid := l_expense_clearing_ccid;
915
916 end if;
917
918 RETURN TRUE;
919 EXCEPTION
920 WHEN AP_WEB_OA_MAINFLOW_PKG.G_EXC_ERROR THEN
921 AP_WEB_DB_UTIL_PKG.RaiseException(nvl(FND_MESSAGE.Get,l_debug_info));
922 APP_EXCEPTION.RAISE_EXCEPTION;
923 return FALSE;
924 WHEN OTHERS THEN
925 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetExpenseClearingCCID' );
926 APP_EXCEPTION.RAISE_EXCEPTION;
927 return FALSE;
928
929 END GetExpenseClearingCCID;
930
931 FUNCTION GetRoundingErrorCCID(
932 p_ccid OUT NOCOPY NUMBER
933 ) RETURN BOOLEAN IS
934 -------------------------------------------------------------------------------
935 BEGIN
936 SELECT ROUNDING_ERROR_CCID
937 INTO p_ccid
938 FROM ap_system_parameters;
939
940 RETURN TRUE;
941
942 EXCEPTION
943 WHEN NO_DATA_FOUND THEN
944 RETURN FALSE;
945
946 WHEN OTHERS THEN
947 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetRoundingErrorCCID' );
948 APP_EXCEPTION.RAISE_EXCEPTION;
949 return FALSE;
950
951 END GetRoundingErrorCCID;
952
953 /* move to interface with AP system */
954 -----------------------------------------------------
955 FUNCTION GetAvailablePrepayments(
956 p_employee_id IN vendors_employeeID,
957 p_default_currency_code IN invoices_invCurrCode,
958 p_available_prepays OUT NOCOPY NUMBER
959 ) RETURN BOOLEAN IS
960 -----------------------------------------------------
961 BEGIN
962 SELECT nvl(sum(decode(payment_status_flag, 'Y',
963 decode(sign(earliest_settlement_date - sysdate),1,0,1),
964 0)),
965 0)
966 INTO p_available_prepays
967 FROM ap_invoices I,
968 ap_suppliers PV
969 WHERE (select sum(aid.prepay_amount_remaining)
970 from ap_invoice_distributions aid
971 where aid.invoice_id = I.invoice_id
972 AND aid.line_type_lookup_code IN ('ITEM','TAX')
973 AND NVL(aid.reversal_flag,'N') <> 'Y'
974 ) > 0
975 AND I.vendor_id = PV.vendor_id
976 AND PV.employee_id = p_employee_id
977 AND I.invoice_type_lookup_code = 'PREPAYMENT'
978 AND earliest_settlement_date IS NOT NULL
979 AND I.invoice_amount > 0
980 AND I.invoice_currency_code = p_default_currency_code;
981
982 return TRUE;
983
984 EXCEPTION
985 WHEN NO_DATA_FOUND THEN
986 RETURN FALSE;
987
988 WHEN OTHERS THEN
989 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetAvailablePrepayments' );
990 APP_EXCEPTION.RAISE_EXCEPTION;
991 return FALSE;
992
993 END GetAvailablePrepayments;
994
995
996 --------------------------------------------------------------------------------
997 FUNCTION InsertInvoiceInterface(
998 p_invoice_id IN invIntf_invID,
999 p_party_id IN invIntf_partyID,
1000 p_vendor_id IN invIntf_vendorID,
1001 p_vendor_site_id IN invIntf_vendorSiteID,
1002 p_sum IN invIntf_invAmt,
1003 p_invoice_curr_code IN invIntf_invCurrCode,
1004 p_source IN invIntf_source,
1005 p_pay_group_lookup_code IN vendorSites_payGroupLookupCode,
1006 p_org_id IN NUMBER,
1007 p_doc_category_code IN invIntf_docCategoryCode,
1008 p_invoice_type_lookup_code IN invIntf_invTypeCode,
1009 p_accts_pay_ccid IN invIntf_acctsPayCCID,
1010 p_party_site_id IN invIntf_partySiteID default null,
1011 p_terms_id IN AP_TERMS.TERM_ID%TYPE default null
1012 ) RETURN BOOLEAN IS
1013 --------------------------------------------------------------------------------
1014 l_payment_priority ap_supplier_sites_all.payment_priority%TYPE;
1015 BEGIN
1016 -- Bug 6838894
1017 -- Bug: 7234744 populate terms-id in the interface table.
1018 BEGIN
1019 if (p_vendor_site_id IS NOT NULL) then
1020 select payment_priority
1021 into l_payment_priority
1022 from ap_supplier_sites_all
1023 where vendor_site_id = p_vendor_site_id;
1024 else
1025 l_payment_priority := NULL;
1026 end if;
1027 EXCEPTION
1028 WHEN OTHERS THEN
1029 l_payment_priority := NULL;
1030 END;
1031 INSERT INTO AP_INVOICES_INTERFACE
1032 (INVOICE_ID,
1033 INVOICE_NUM,
1034 PARTY_ID,
1035 PARTY_SITE_ID,
1036 VENDOR_ID,
1037 VENDOR_SITE_ID,
1038 INVOICE_AMOUNT,
1039 INVOICE_CURRENCY_CODE,
1040 SOURCE,
1041 PAY_GROUP_LOOKUP_CODE,
1042 ORG_ID,
1043 DOC_CATEGORY_CODE,
1044 INVOICE_TYPE_LOOKUP_CODE,
1045 ACCTS_PAY_CODE_COMBINATION_ID,
1046 TERMS_ID,
1047 PAYMENT_PRIORITY)
1048 VALUES
1049 (p_invoice_id,
1050 substrb(to_char(p_invoice_id)||'-'||to_char(sysdate), 1, 50),
1051 p_party_id,
1052 p_party_site_id,
1053 p_vendor_id,
1054 p_vendor_site_id,
1055 p_sum,
1056 p_invoice_curr_code,
1057 p_source,
1058 p_pay_group_lookup_code,
1059 p_org_id,
1060 p_doc_category_code,
1061 p_invoice_type_lookup_code,
1062 p_accts_pay_ccid,
1063 p_terms_id,
1064 l_payment_priority);
1065
1066 RETURN TRUE;
1067
1068 EXCEPTION
1069 WHEN NO_DATA_FOUND THEN
1070 RETURN FALSE;
1071
1072 WHEN OTHERS THEN
1073 AP_WEB_DB_UTIL_PKG.RaiseException( 'UpdateInvoiceInterface' );
1074 APP_EXCEPTION.RAISE_EXCEPTION;
1075 return FALSE;
1076
1077 END InsertInvoiceInterface;
1078
1079 --------------------------------------------------------------------------------
1080 FUNCTION InsertInvoiceLinesInterface(
1081 p_invoice_id IN invLines_invID,
1082 p_invoice_line_id IN invLines_invLineID,
1083 p_count IN invLines_lineNum,
1084 p_linetype IN invLines_lineTypeLookupCode,
1085 p_amount IN invLines_amount,
1086 p_trxn_date IN invLines_accountingDate,
1087 p_ccid IN invLines_distCodeCombID,
1088 p_card_trxn_id IN invLines_crdCardTrxID,
1089 p_description IN invLines_description,
1090 p_org_id IN NUMBER
1091 ) RETURN BOOLEAN IS
1092 --------------------------------------------------------------------------------
1093 BEGIN
1094
1095 INSERT INTO AP_INVOICE_LINES_INTERFACE(
1096 INVOICE_ID,
1097 INVOICE_LINE_ID,
1098 LINE_NUMBER,
1099 LINE_TYPE_LOOKUP_CODE,
1100 AMOUNT,
1101 ACCOUNTING_DATE,
1102 DIST_CODE_COMBINATION_ID,
1103 CREDIT_CARD_TRX_ID,
1104 DESCRIPTION,
1105 ORG_ID)
1106 VALUES
1107 (p_invoice_id,
1108 p_invoice_line_id,
1109 p_count,
1110 p_linetype,
1111 p_amount,
1112 p_trxn_date,
1113 p_ccid,
1114 p_card_trxn_id,
1115 p_description,
1116 p_org_id);
1117
1118 RETURN TRUE;
1119 EXCEPTION
1120 WHEN NO_DATA_FOUND THEN
1121 RETURN FALSE;
1122
1123 WHEN OTHERS THEN
1124 AP_WEB_DB_UTIL_PKG.RaiseException( 'InsertInvoiceLinesInerface' );
1125 APP_EXCEPTION.RAISE_EXCEPTION;
1126 return FALSE;
1127
1128 END InsertInvoiceLinesInterface;
1129
1130
1131 -------------------------------------------------------------------
1132 FUNCTION IsTaxCodeWebEnabled(
1133 P_ExpTypeDefaultTaxCode IN taxCodes_name,
1134 p_tax_web_enabled OUT NOCOPY taxCodes_webEnabledFlag
1135 ) RETURN BOOLEAN IS
1136 -------------------------------------------------------------------
1137 BEGIN
1138
1139 -- Clear out the default tax if it is not web enabled. This is a setup
1140 -- error.
1141 -- If web_enabled_flag is null should assume P_Default_No_Flag.
1142 SELECT NVL(WEB_ENABLED_FLAG, 'N')
1143 INTO p_tax_web_enabled
1144 FROM AP_TAX_CODES
1145 WHERE NAME = P_ExpTypeDefaultTaxCode
1146 AND nvl(enabled_flag, 'Y') = 'Y'
1147 AND nvl(web_enabled_flag,'N') = 'Y';
1148
1149 RETURN TRUE;
1150
1151 EXCEPTION
1152
1153 WHEN TOO_MANY_ROWS THEN
1154 p_tax_web_enabled := 'Y';
1155 RETURN TRUE;
1156
1157 WHEN NO_DATA_FOUND THEN
1158 RETURN FALSE;
1159
1160 WHEN OTHERS THEN
1161 AP_WEB_DB_UTIL_PKG.RaiseException( 'IsTaxCodeWebEnabled' );
1162 APP_EXCEPTION.RAISE_EXCEPTION;
1163 return FALSE;
1164
1165 END IsTaxCodeWebEnabled;
1166
1167
1168 -----------------------------------------------------
1169 PROCEDURE GenTaxFunctions
1170 -----------------------------------------------------
1171 IS
1172
1173 BEGIN
1174
1175 --This is a wrapper procedure for 11.0.3 backport
1176 AP_WEB_WRAPPER_PKG.GenTaxFunctions;
1177
1178
1179 EXCEPTION
1180
1181 WHEN OTHERS THEN
1182 AP_WEB_DB_UTIL_PKG.RaiseException( 'AP_WEB_DB_AP_INT_PKG.GenTaxFunctions' );
1183 APP_EXCEPTION.RAISE_EXCEPTION;
1184
1185 END GenTaxFunctions;
1186
1187
1188 FUNCTION GetInvoiceAmt(
1189 p_invoiceId IN invAll_id,
1190 p_invoiceAmt OUT NOCOPY invLines_amount,
1191 p_exchangeRate OUT NOCOPY invAll_exchangeRate,
1192 p_minAcctUnit OUT NOCOPY FND_CURRENCIES_VL.minimum_accountable_unit%TYPE,
1193 p_precision OUT NOCOPY FND_CURRENCIES_VL.PRECISION%TYPE
1194 ) RETURN BOOLEAN IS
1195 BEGIN
1196
1197 SELECT inv.invoice_amount, inv.exchange_rate,
1198 F.minimum_accountable_unit, F.precision
1199 INTO p_invoiceAmt, p_exchangeRate, p_minAcctUnit, p_precision
1200 FROM AP_INVOICES inv, ap_system_parameters sp, fnd_currencies F
1201 WHERE inv.invoice_id = p_invoiceId
1202 AND inv.set_of_books_id = sp.set_of_books_id
1203 AND F.currency_code = sp.base_currency_code;
1204
1205 RETURN TRUE;
1206
1207 EXCEPTION
1208
1209 WHEN NO_DATA_FOUND THEN
1210 RETURN FALSE;
1211
1212 WHEN OTHERS THEN
1213 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetInvoiceAmt' );
1214 APP_EXCEPTION.RAISE_EXCEPTION;
1215 return FALSE;
1216
1217 END GetInvoiceAmt;
1218
1219 FUNCTION SetInvoiceAmount(p_invoiceId IN invAll_id,
1220 p_invoiceAmt IN invAll_invoiceAmount,
1221 p_baseAmt IN invAll_baseAmount) RETURN BOOLEAN IS
1222 BEGIN
1223 UPDATE AP_INVOICES
1224 SET invoice_amount = p_invoiceAmt,
1225 pay_curr_invoice_amount = ap_web_utilities_pkg.oie_round_currency(
1226 p_invoiceAmt * payment_cross_rate,
1227 payment_currency_code),
1228 base_amount = p_baseAmt
1229 WHERE invoice_id = p_invoiceId;
1230
1231 RETURN TRUE;
1232
1233 EXCEPTION
1234 WHEN NO_DATA_FOUND THEN
1235 return FALSE;
1236 WHEN OTHERS THEN
1237 AP_WEB_DB_UTIL_PKG.RaiseException( 'SetInvoiceAmount' );
1238 APP_EXCEPTION.RAISE_EXCEPTION;
1239 return FALSE;
1240
1241 END SetInvoiceAmount;
1242
1243 -------------------------------------------------------------------
1244 FUNCTION GetVatCode(
1245 P_TaxCodeID IN taxCodes_taxID,
1246 P_VatCode OUT NOCOPY taxCodes_name
1247 ) RETURN BOOLEAN IS
1248 -------------------------------------------------------------------
1249 BEGIN
1250
1251
1252 -- Return Vat Code according to Tax Code ID
1253 SELECT NAME
1254 INTO P_VatCode
1255 FROM AP_TAX_CODES
1256 WHERE TAX_ID = P_TaxCodeID;
1257
1258 RETURN TRUE;
1259
1260 EXCEPTION
1261
1262 WHEN NO_DATA_FOUND THEN
1263 RETURN FALSE;
1264
1265 WHEN OTHERS THEN
1266 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetVatCode' );
1267 APP_EXCEPTION.RAISE_EXCEPTION;
1268 return FALSE;
1269
1270 END GetVatCode;
1271
1272 -------------------------------------------------------------------
1273 FUNCTION GetTaxCodeID(
1274 P_VatCode IN taxCodes_name,
1275 P_TaxCodeID OUT NOCOPY taxCodes_taxID
1276 ) RETURN BOOLEAN IS
1277 -------------------------------------------------------------------
1278 BEGIN
1279
1280
1281 -- Return Tax Code Id using Vat Code
1282 SELECT TAX_ID
1283 INTO P_TAXCodeID
1284 FROM AP_TAX_CODES
1285 WHERE NAME = P_VatCode;
1286
1287 RETURN TRUE;
1288
1289 EXCEPTION
1290
1291 WHEN NO_DATA_FOUND THEN
1292 RETURN FALSE;
1293
1294 WHEN OTHERS THEN
1295 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetTaxCodeID' );
1296 APP_EXCEPTION.RAISE_EXCEPTION;
1297 return FALSE;
1298
1299 END GetTaxCodeID;
1300
1301 -------------------------------------------------------------------
1302 FUNCTION GetTaxCodeID(
1303 P_VatCode IN taxCodes_name,
1304 P_ExpLine_Date IN DATE,
1305 P_TaxCodeID OUT NOCOPY taxCodes_taxID
1306 ) RETURN BOOLEAN IS
1307 -------------------------------------------------------------------
1308 BEGIN
1309
1310
1311 -- Return Tax Code Id using Vat Code
1312 SELECT TAX_ID
1313 INTO P_TAXCodeID
1314 FROM AP_TAX_CODES
1315 WHERE NAME = P_VatCode
1316 AND nvl(enabled_flag, 'Y') = 'Y'
1317 AND nvl(P_ExpLine_Date,sysdate) BETWEEN
1318 nvl(start_date,nvl(P_ExpLine_Date,sysdate)) AND
1319 nvl(inactive_date,nvl(P_ExpLine_Date,sysdate));
1320
1321 RETURN TRUE;
1322
1323 EXCEPTION
1324
1325 WHEN NO_DATA_FOUND THEN
1326 RETURN FALSE;
1327
1328 WHEN OTHERS THEN
1329 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetTaxCodeID' );
1330 APP_EXCEPTION.RAISE_EXCEPTION;
1331 return FALSE;
1332
1333 END GetTaxCodeID;
1334
1335 FUNCTION getTemplateCostCenter(
1336 p_parameter_id IN NUMBER
1337 ) RETURN VARCHAR2 IS
1338 --------------------------------------------------------------------------------
1339 l_cost_center varchar2(200);
1340
1341 BEGIN
1342
1343 AP_WEB_ACCTG_PKG.GetExpenseTypeCostCenter(
1344 p_exp_type_parameter_id => p_parameter_id,
1345 p_cost_center => l_cost_center);
1346
1347 return l_cost_center;
1348
1349 EXCEPTION
1350 when others then
1351 return l_cost_center;
1352 END getTemplateCostCenter;
1353
1354 FUNCTION isCostCenterExistOnTemplate(
1355 p_expense_report_id IN NUMBER
1356 ) RETURN VARCHAR2 IS
1357 --------------------------------------------------------------------------------
1358 l_cost_center varchar2(200);
1359 l_cc_exist varchar2(1) := 'N';
1360 l_expense_types_cursor AP_WEB_DB_EXPTEMPLATE_PKG.ExpenseTypesCursor;
1361 l_parameter_id AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_paramID;
1362 l_web_FriendlyPrompt AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_webFriendlyPrompt;
1363 l_require_receipt_amount AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_requireReceiptAmt;
1364 l_card_exp_type_lookup_code AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_cardExpTypeLookupCode;
1365 l_amount_includes_tax_flag AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_amtInclTaxFlag;
1366 l_justif_req AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_justificationReqdFlag;
1367
1368 BEGIN
1369
1370 IF (AP_WEB_DB_EXPTEMPLATE_PKG.GetExpTypesCursor(p_expense_report_id, l_expense_types_cursor)) THEN
1371 LOOP
1372 FETCH l_expense_types_cursor
1373 INTO l_parameter_id, l_web_FriendlyPrompt,
1374 l_require_receipt_amount,
1375 l_card_exp_type_lookup_code,
1376 l_amount_includes_tax_flag,
1377 l_justif_req;
1378 EXIT WHEN l_expense_types_cursor%NOTFOUND;
1379 begin
1380 AP_WEB_ACCTG_PKG.GetExpenseTypeCostCenter(
1381 p_exp_type_parameter_id => l_parameter_id,
1382 p_cost_center => l_cost_center);
1383
1384 IF (l_cost_center IS NOT NULL) THEN
1385 l_cc_exist := 'Y';
1386 EXIT;
1387 END IF;
1388 exception
1389 when others then
1390 null;
1391 end;
1392 END LOOP; --end for arrExpType
1393 END IF;
1394 CLOSE l_expense_types_cursor;
1395
1396 return l_cc_exist;
1397
1398 EXCEPTION
1399 when others then
1400 AP_WEB_DB_UTIL_PKG.RaiseException('isCostCenterExistOnTemplate');
1401 APP_EXCEPTION.RAISE_EXCEPTION;
1402 return l_cc_exist;
1403 END isCostCenterExistOnTemplate;
1404
1405 -------------------------------------------------------------------
1406 FUNCTION GetExpenseClearingCCID(
1407 p_trx_id NUMBER
1408 ) RETURN NUMBER IS
1409 -------------------------------------------------------------------
1410 l_employee_id number;
1411 l_card_program_id number;
1412 l_transaction_date ap_credit_card_trxns.transaction_date%type;
1413 l_ccid ap_expense_report_headers_all.employee_ccid%type;
1414 BEGIN
1415
1416 SELECT employee_id, txn.card_program_id, transaction_date
1417 INTO l_employee_id, l_card_program_id, l_transaction_date
1418 FROM ap_cards card,
1419 ap_credit_card_trxns txn
1420 WHERE card.card_program_id = txn.card_program_id
1421 AND card.card_id = txn.card_id
1422 AND txn.trx_id = p_trx_id;
1423
1424 IF NOT AP_WEB_DB_AP_INT_PKG.GetExpenseClearingCCID(p_ccid => l_ccid,
1425 p_card_program_id => l_card_program_id,
1426 p_employee_id => l_employee_id,
1427 p_as_of_date => l_transaction_date) THEN
1428 l_ccid := null;
1429 END IF;
1430
1431
1432 RETURN l_ccid;
1433 EXCEPTION
1434 WHEN OTHERS THEN
1435 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetExpenseClearingCCID' );
1436 APP_EXCEPTION.RAISE_EXCEPTION;
1437 return 0;
1438
1439 END GetExpenseClearingCCID;
1440
1441 -------------------------------------------------------------------
1442
1443 ----------------------------------------------------------------------------------------
1444 -- Bug# 9182883: Procedure to find whether the default exchange rates is enabled or not
1445 -- also used to get the allowance rate
1446 ----------------------------------------------------------------------------------------
1447 PROCEDURE GetDefaultExchangeRates(
1448 p_default_exchange_rates OUT NOCOPY VARCHAR2,
1449 p_exchange_rate_allowance OUT NOCOPY NUMBER
1450 ) IS
1451 -------------------------------------------------------
1452 l_default_exchange_rates VARCHAR2(1);
1453 l_default_exchange_rate_flag VARCHAR2(1);
1454 l_exchange_rate_allowance NUMBER;
1455 l_employee_id NUMBER;
1456 BEGIN
1457 p_default_exchange_rates := 'N';
1458 p_exchange_rate_allowance := 0;
1459
1460 SELECT default_exchange_rates, exchange_rate_allowance
1461 INTO l_default_exchange_rates, l_exchange_rate_allowance
1462 FROM ap_pol_exrate_options WHERE enabled = 'Y';
1463
1464 IF l_default_exchange_rates = 'U' THEN
1465 l_employee_id := AP_WEB_DB_HR_INT_PKG.getEmployeeID;
1466
1467 SELECT default_exchange_rate_flag INTO l_default_exchange_rate_flag
1468 FROM ap_web_preferences WHERE employee_id = l_employee_id;
1469
1470 IF l_default_exchange_rate_flag = 'Y' THEN
1471 p_default_exchange_rates := 'Y';
1472 p_exchange_rate_allowance := l_exchange_rate_allowance;
1473 END IF;
1474
1475 ELSIF l_default_exchange_rates <> 'N' THEN
1476 p_default_exchange_rates := 'Y';
1477 IF l_default_exchange_rates = 'Y' THEN
1478 p_exchange_rate_allowance := l_exchange_rate_allowance;
1479 END IF;
1480
1481 END IF;
1482
1483 EXCEPTION
1484 WHEN NO_DATA_FOUND THEN
1485 null;
1486 WHEN OTHERS THEN
1487 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetDefaultExchangeRates' );
1488 APP_EXCEPTION.RAISE_EXCEPTION;
1489
1490 END GetDefaultExchangeRates;
1491
1492 -------------------------------------------------------------------------------------
1493
1494
1495 END AP_WEB_DB_AP_INT_PKG;