[Home] [Help]
PACKAGE BODY: APPS.POR_LOAD_EMP_TEMP_TABLE
Source
1 PACKAGE BODY POR_LOAD_EMP_TEMP_TABLE as
2 /* $Header: PORLEMTB.pls 115.4 2001/06/28 18:57:03 pkm ship $ */
3
4 PROCEDURE insert_update_emp_temp_table (
5 x_employee_number IN VARCHAR2,
6 x_first_name IN VARCHAR2,
7 x_last_name IN VARCHAR2,
8 x_sex IN VARCHAR2,
9 x_effective_start_date IN DATE,
10 x_effective_end_date IN DATE,
11 x_business_group_name IN VARCHAR2,
12 x_location_name IN VARCHAR2,
13 x_default_employee_account IN VARCHAR2,
14 x_set_of_books_name IN VARCHAR2,
15 x_supervisor_emp_number IN VARCHAR2,
16 x_job_name IN VARCHAR2,
17 x_user_name IN VARCHAR2,
18 x_password IN VARCHAR2,
19 x_email_address IN VARCHAR2,
20 x_work_telephone IN VARCHAR2)
21 IS
22 BEGIN
23
24
25 IF (get_employee_exists(x_employee_number)) THEN
26
27 UPDATE POR_EMPLOYEE_LOADER_VALUES
28 SET
29 first_name = x_first_name,
30 last_name = x_last_name,
31 sex = x_sex,
32 start_date = x_effective_start_date,
33 end_date = x_effective_end_date,
34 business_group_name = x_business_group_name,
35 location_code = x_location_name,
36 set_of_books = x_set_of_books_name,
37 default_expense_account = x_default_employee_account,
38 job_name = x_job_name,
39 supervisor_emp_num = x_supervisor_emp_number,
40 user_name = x_user_name,
41 password = x_password,
42 email_address = x_email_address,
43 work_telephone = x_work_telephone,
44 loader_status = 'unloaded',
45 last_update_date = sysdate
46
47 WHERE
48 employee_number = x_employee_number;
49
50
51 ELSE
52
53 INSERT INTO POR_EMPLOYEE_LOADER_VALUES (
54 employee_number,
55 first_name,
56 last_name,
57 sex,
58 start_date,
59 end_date,
60 business_group_name,
61 location_code,
62 set_of_books,
63 default_expense_account,
64 job_name,
65 supervisor_emp_num,
66 user_name,
67 password,
68 email_address,
69 work_telephone,
70 loader_status,
71 last_update_date,
72 last_updated_by,
73 creation_date,
74 created_by)
75 VALUES (
76 x_employee_number,
77 x_first_name,
78 x_last_name,
79 x_sex,
80 x_effective_start_date,
81 x_effective_end_date,
82 x_business_group_name,
83 x_location_name,
84 x_set_of_books_name,
85 x_default_employee_account,
86 x_job_name,
87 x_supervisor_emp_number,
88 x_user_name,
89 x_password,
90 x_email_address,
91 x_work_telephone,
92 'unloaded',
93 sysdate,
94 0,
95 sysdate,
96 0
97 );
98
99 END IF;
100
101 EXCEPTION
102 WHEN OTHERS THEN
103 RAISE;
104
105 commit;
106
107 END insert_update_emp_temp_table;
108
109 FUNCTION get_employee_exists (p_employee_number IN VARCHAR2) RETURN BOOLEAN IS
110 l_exists NUMBER;
111
112 BEGIN
113
114 SELECT 1 INTO l_exists FROM por_employee_loader_values
115 WHERE employee_number = p_employee_number;
116
117 RETURN true;
118
119 EXCEPTION
120 WHEN NO_DATA_FOUND THEN
121 RETURN false;
122
123 END get_employee_exists;
124
125 END POR_LOAD_EMP_TEMP_TABLE;
126