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