1 PACKAGE BODY AP_WEB_EMP_NUM_MATCH_PKG as
2 /* $Header: apwenmhb.pls 120.1 2006/03/24 17:07:14 abordia noship $ */
3
4 PROCEDURE GET_EMPLOYEE_MATCHES(p_card_id IN NUMBER,
5 p_employee_number IN VARCHAR2);
6
7
8 PROCEDURE GET_EMPLOYEE_MATCHES(p_card_id IN NUMBER) IS
9 CURSOR ccard IS SELECT employee_number
10 FROM ap_card_details
11 WHERE card_id = p_card_id
12 AND EMPLOYEE_NUMBER IS NOT NULL;
13 l_full_name varchar2(80);
14 l_employee_number varchar2(30);
15 l_national_identifier varchar2(30);
16 BEGIN
17 OPEN ccard;
18 FETCH ccard INTO l_employee_number;
19 IF ccard%FOUND THEN
20 GET_EMPLOYEE_MATCHES(p_card_id, l_employee_number);
21 END IF;
22 CLOSE ccard;
23 END GET_EMPLOYEE_MATCHES;
24
25 -- matches only on employee number
26 PROCEDURE GET_EMPLOYEE_MATCHES(p_card_id IN NUMBER,
27 p_employee_number IN VARCHAR2) IS
28
29 BEGIN
30 AP_WEB_MATCHING_RULE_PKG.lock_card(p_card_id);
31 INSERT INTO ap_card_emp_candidates(card_id, employee_id, created_by, creation_date, last_updated_by, last_update_date, last_update_login)
32 SELECT card.card_id,
33 pap.person_id,
34 fnd_global.user_id,
35 trunc(sysdate),
36 fnd_global.user_id,
37 trunc(sysdate),
38 fnd_global.login_id
39 FROM PER_ALL_PEOPLE_F pap, FINANCIALS_SYSTEM_PARAMS_ALL fsp, PER_PERSON_TYPES ppt, AP_CARDS_ALL card
40 WHERE
41 card.CARD_ID = p_card_id
42 AND card.ORG_ID = fsp.ORG_ID
43 AND TRUNC(sysdate) BETWEEN pap.effective_start_date AND pap.effective_end_date
44 AND pap.business_group_id = fsp.business_group_id
45 AND pap.business_group_id = ppt.business_group_id
46 AND UPPER(ppt.user_person_type) <> 'CONTACT'
47 AND pap.person_type_id = ppt.person_type_id
48 AND EMPLOYEE_NUMBER = p_employee_number
49 ;
50
51 AP_WEB_MATCHING_RULE_PKG.unlock_card;
52 END GET_EMPLOYEE_MATCHES;
53
54 END AP_WEB_EMP_NUM_MATCH_PKG;
[Home] [Help]