1 PACKAGE BODY AP_WEB_MATCHING_RULE_PKG AS
2 /* $Header: apwmachb.pls 120.3 2011/05/31 07:26:39 meesubra ship $ */
3
4 --
5 -- Cursor used to lock CARD record
6 cursor ccard(p_card_id number) is
7 select card_id from ap_cards_all where card_id = p_card_id
8 for update of card_id;
9
10
11 --
12 -- Breaks the employee name into first/last.
13 -- If the employee name consists of one strings, it is assumed to be
14 -- the last name.
15 -- If the employee name consists of exactly two strings, it matches
16 -- as the first name and last name.
17 -- If the employee name consists of more than two strings, it matches
18 -- the first two strings to the first and last name, and the rest
19 -- are ignored.
20 PROCEDURE GET_EMPLOYEE_NAME2(p_employee_name IN VARCHAR2,
21 p_first_name OUT NOCOPY VARCHAR2,
22 p_last_name OUT NOCOPY VARCHAR2)
23 IS
24 space1 NUMBER;
25 space2 NUMBER;
26 BEGIN
27 p_first_name := null;
28 p_last_name := null;
29
30 space1 := 1;
31 space2 := instr(p_employee_name, ' ');
32
33 if space2 = 0 then
34 p_last_name := p_employee_name;
35 return;
36 end if;
37
38 p_first_name := substr(p_employee_name, space1, space2-space1);
39
40 space1 := space2+1;
41 space2 := instr(p_employee_name, ' ', space1);
42 if space2 = 0 then
43 p_last_name := substr(p_employee_name, space1);
44 else
45 p_last_name := substr(p_employee_name, space1, space2-space1);
46 end if;
47 END GET_EMPLOYEE_NAME2;
48
49 --
50 -- Breaks the employee name into first/middle/last names.
51 -- If there is one string, it is the last name.
52 -- If there are two strings, it is the first and last names.
53 -- If there are three strings, it is the first/middle/last names.
54 -- If there are more than three strings, the first three strings
55 -- are matched to the first/middle/last, and the rest are ignored.
56 PROCEDURE GET_EMPLOYEE_NAME3(p_employee_name IN VARCHAR2,
57 p_first_name OUT NOCOPY VARCHAR2,
58 p_middle_name OUT NOCOPY VARCHAR2,
59 p_last_name OUT NOCOPY VARCHAR2)
60 IS
61 space1 NUMBER;
62 space2 NUMBER;
63 BEGIN
64 space1 := 1;
65 space2 := instr(p_employee_name, ' ');
66
67 if space2 = 0 then
68 p_last_name := p_employee_name;
69 return;
70 end if;
71 p_first_name := substr(p_employee_name, space1, space2-space1);
72
73 space1 := space2+1;
74 space2 := instr(p_employee_name, ' ', space1);
75 if space2 = 0 then
76 p_last_name := substr(p_employee_name, space1);
77 return;
78 end if;
79 p_middle_name := substr(p_employee_name, space1, space2-space1);
80
81 space1 := space2+1;
82 space2 := instr(p_employee_name, ' ', space1);
83 if space2 = 0 then
84 p_last_name := substr(p_employee_name, space1);
85 else
86 p_last_name := substr(p_employee_name, space1, space2-space1);
87 end if;
88
89 END GET_EMPLOYEE_NAME3;
90
91 --
92 -- Locks the card so that multiple calls won't get in the way
93 -- of each other.
94 -- It essentially protects ap_card_emp_candidates table.
95 PROCEDURE LOCK_CARD(p_card_id NUMBER) IS
96 l_card_id number;
97 BEGIN
98 OPEN ccard(p_card_id);
99 FETCH ccard INTO l_card_id;
100
101 delete from ap_card_emp_candidates where card_id = p_card_id;
102 EXCEPTION
103 WHEN OTHERS THEN
104 IF ccard%ISOPEN then
105 close ccard;
106 END IF;
107 END lock_card;
108
109 --
110 -- Unlock the card.
111 PROCEDURE UNLOCK_CARD IS
112 BEGIN
113 if (ccard%isopen) then
114 CLOSE ccard;
115 end if;
116 END UNLOCK_CARD;
117
118
119 --
120 -- Execute Query
121 PROCEDURE EXECUTE_QUERY(P_CARD_ID IN NUMBER,
122 P_FIRST_NAME IN VARCHAR2,
123 P_MIDDLE_NAME IN VARCHAR2,
124 P_LAST_NAME IN VARCHAR2,
125 P_NATIONAL_ID IN VARCHAR2,
126 P_EMPLOYEE_NUM IN VARCHAR2) IS
127 l_full_name VARCHAR2(240);
128 l_where_clause VARCHAR2(500);
129 l_execute boolean := false; -- bug 5224047(2)
130 BEGIN
131 IF p_card_id IS NULL THEN
132 RETURN;
133 END IF;
134
135 IF p_last_name IS NOT NULL OR p_first_name IS NOT NULL OR p_middle_name IS NOT NULL THEN
136 l_where_clause := ' AND UPPER(FULL_NAME) LIKE :fullName';
137 l_full_name := upper(p_last_name) ||'%' ||upper(p_first_name)||'%'||upper(p_middle_name);
138 l_execute := true;
139 ELSE
140 l_where_clause := ' AND FULL_NAME IS NULL AND :fullName IS NULL';
141 l_full_name := NULL;
142 END IF;
143 IF p_national_id IS NOT NULL THEN
144 l_where_clause := l_where_clause || ' AND regexp_replace(NATIONAL_IDENTIFIER,''[^0-9a-zA-Z]'','''') = :nationalId';
145 l_execute := true;
146 ELSE
147 l_where_clause := l_where_clause || ' AND NATIONAL_IDENTIFIER IS NULL AND :nationalId IS NULL';
148 END IF;
149 IF p_employee_num IS NOT NULL THEN
150 l_where_clause := l_where_clause || ' AND EMPLOYEE_NUMBER = :empNum';
151 l_execute := true;
152 ELSE
153 l_where_clause := l_where_clause || ' AND EMPLOYEE_NUMBER IS NULL AND :empNum IS NULL';
154 END IF;
155
156 IF (l_execute) THEN
157 lock_card(p_card_id);
158 execute immediate
159 'INSERT INTO ap_card_emp_candidates(card_id, employee_id, created_by, creation_date, last_updated_by, last_update_date, last_update_login)
160 SELECT card.card_id,
161 pap.person_id,
162 fnd_global.user_id,
163 trunc(sysdate),
164 fnd_global.user_id,
165 trunc(sysdate),
166 fnd_global.login_id
167 FROM PER_ALL_PEOPLE_F pap, FINANCIALS_SYSTEM_PARAMS_ALL fsp, PER_PERSON_TYPES ppt, AP_CARDS_ALL card
168 WHERE
169 card.CARD_ID = :cardId
170 AND card.ORG_ID = fsp.ORG_ID
171 AND TRUNC(sysdate) BETWEEN pap.effective_start_date AND pap.effective_end_date
172 AND pap.business_group_id = fsp.business_group_id
173 AND pap.business_group_id = ppt.business_group_id
174 AND UPPER(ppt.user_person_type) <> ''CONTACT''
175 AND pap.person_type_id = ppt.person_type_id'||l_where_clause
176 using p_card_id, l_full_name, p_national_id, p_employee_num;
177
178 unlock_card;
179 END IF;
180 END EXECUTE_QUERY;
181
182 ------------------------------------------------------------
183 -- Default matching rule
184 -- If FIRST_NAME, MIDDLE_NAME, or LAST_NAME is populated
185 -- then it uses that to match employee name. Otherwise it uses
186 -- the FULL_NAME and tries to break it apart into its component
187 -- assuming a "First Middle Last" format. Name always uses
188 -- case insensitive matching.
189 -- National identifier and employee number are passed directly.
190 -------------------------------------------------------------
191 PROCEDURE GET_EMPLOYEE_MATCHES(p_card_id IN NUMBER) IS
192 CURSOR ccard IS SELECT full_name, first_name, middle_name, last_name, employee_number, national_identifier
193 FROM ap_card_details
194 WHERE card_id = p_card_id;
195 l_full_name varchar2(80);
196 l_employee_number varchar2(30);
197 l_national_identifier varchar2(30);
198 l_first_name VARCHAR2(150);
199 l_middle_name VARCHAR2(150);
200 l_last_name VARCHAR2(150);
201 BEGIN
202 OPEN ccard;
203 FETCH ccard INTO l_full_name, l_first_name, l_middle_name, l_last_name, l_employee_number, l_national_identifier;
204 IF ccard%FOUND THEN
205 IF l_first_name IS NULL AND l_middle_name IS NULL AND l_last_name IS NULL AND
206 l_full_name IS NOT NULL
207 THEN
208 get_employee_name3(l_full_name,
209 l_first_name,
210 l_middle_name,
211 l_last_name);
212 END IF;
213 execute_query(p_card_id, l_first_name, l_middle_name, l_last_name, l_national_identifier, l_employee_number);
214 END IF;
215 CLOSE ccard;
216 END GET_EMPLOYEE_MATCHES;
217
218
219 END AP_WEB_MATCHING_RULE_PKG;
[Home] [Help]