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