1 PACKAGE BODY AP_WEB_AMEX_PKG as
2 /* $Header: apwamexb.pls 115.1 2003/08/29 16:51:42 kmizuta noship $ */
3
4 PROCEDURE GET_EMPLOYEE_MATCHES(p_card_id IN NUMBER,
5 p_employee_name IN VARCHAR2,
6 p_employee_number IN VARCHAR2,
7 p_national_identifier IN VARCHAR2);
8
9 ------------------------------------------------------------------
10 -- American Express sends us name, employee number, and
11 -- national identifier (e.g., social security number)
12 -- Match against all.
13 --
14 -- Special treatment is made for national identifier.
15 -- If national identifier starts with a 0, it matches the last
16 -- 9 characters only. Also, it strips any '-' characters from the
17 -- value in the database. This allows the following to match
18 -- Feed = 0123456789, Database=123-456-789
19 -- NOTE: This works for social security numbers (US)
20 -- But could pose problems if used in countries with a 10
21 -- character id. It would fail to match the following
22 -- Feed = 0123456789, Database=0123456789
23 ------------------------------------------------------------------
24 PROCEDURE GET_EMPLOYEE_MATCHES(p_card_id IN NUMBER) IS
25 CURSOR ccard IS SELECT full_name, employee_number, national_identifier
26 FROM ap_card_details
27 WHERE card_id = p_card_id;
28 l_full_name varchar2(80);
29 l_employee_number varchar2(30);
30 l_national_identifier varchar2(30);
31 BEGIN
32 OPEN ccard;
33 FETCH ccard INTO l_full_name, l_employee_number, l_national_identifier;
34 IF ccard%FOUND THEN
35 GET_EMPLOYEE_MATCHES(p_card_id, l_full_name, l_employee_number, l_national_identifier);
36 END IF;
37 CLOSE ccard;
38 END GET_EMPLOYEE_MATCHES;
39
40 PROCEDURE GET_EMPLOYEE_MATCHES(p_card_id IN NUMBER,
41 p_employee_name IN VARCHAR2,
42 p_employee_number IN VARCHAR2,
43 p_national_identifier IN VARCHAR2) IS
44
45 l_first_name VARCHAR2(150);
46 l_middle_name VARCHAR2(150);
47 l_last_name VARCHAR2(150);
48 l_social_security_num VARCHAR2(20);
49 BEGIN
50 ap_web_matching_rule_pkg.get_employee_name3(p_employee_name,
51 l_first_name,
52 l_middle_name,
53 l_last_name);
54 IF Substr(p_national_identifier, 1, 1) = '0' THEN
55 l_social_security_num := Substr(p_national_identifier, 2);
56 ELSE
57 l_social_security_num := p_national_identifier;
58 END IF;
59
60 ap_web_matching_rule_pkg.execute_query(p_card_id, l_first_name, l_middle_name, l_last_name, l_social_security_num, p_employee_number);
61 END GET_EMPLOYEE_MATCHES;
62
63 END AP_WEB_AMEX_PKG;
[Home] [Help]