[Home] [Help]
PACKAGE BODY: APPS.HR_H2PI_MAP
Source
1 PACKAGE BODY hr_h2pi_map AS
2 /* $Header: hrh2pimp.pkb 115.1 2002/03/07 15:35:34 pkm ship $ */
3 --
4 --Declaring the local variables for the package
5 --
6 l_mesg VARCHAR2(200);
7 g_package VARCHAR2(33) := ' hr_h2pi_map.';
8 MAPPING_ID_MISSING EXCEPTION;
9 MAPPING_ID_MISSING_NUMBER CONSTANT NUMBER := -20010;
10 PRAGMA EXCEPTION_INIT (MAPPING_ID_MISSING, -20010);
11 --
12 --
13 -- ----------------------------------------------------------------------------
14 -- |--< Create_Id_Mapping >---------------------------------------------------|
15 -- ----------------------------------------------------------------------------
16 -- Description: Insert a record into HR_H2PI_ID_MAPPING to map internal ids
17 -- from the HR to Payroll system.
18 -- ----------------------------------------------------------------------------
19 PROCEDURE create_id_mapping (p_table_name VARCHAR2,
20 p_from_id NUMBER,
21 p_to_id NUMBER) IS
22
23 l_proc VARCHAR2(72) := g_package||'create_id_mapping';
24 INVALID_PARAM EXCEPTION;
25 PRAGMA EXCEPTION_INIT(INVALID_PARAM,-20001);
26
27 BEGIN
28 hr_utility.set_location('Entering:'|| l_proc, 10);
29 --
30 IF p_table_name IS NULL THEN
31 hr_utility.set_location(l_proc, 20);
32 l_mesg :='FATAL - CREATE_ID_MAPPING:' ||
33 ' The Parameter Value for TABLE_NAME cannot be null';
34 RAISE INVALID_PARAM;
35 ELSIF p_from_id IS NULL THEN
36 hr_utility.set_location(l_proc, 30);
37 l_mesg := 'FATAL - CREATE_ID_MAPPING: '||
38 'The Parameter Value for FROM_ID cannot be null';
39 RAISE INVALID_PARAM;
40 ELSIF p_to_id IS NULL THEN
41 hr_utility.set_location(l_proc, 40);
42 l_mesg := 'FATAL - CREATE_ID_MAPPING: '||
43 'The Parameter Value for TO_ID cannot be null';
44 RAISE INVALID_PARAM;
45 END IF;
46
47 INSERT INTO hr_h2pi_id_mapping
48 (to_business_group_id, from_id, to_id, table_name)
49 SELECT distinct hr_h2pi_upload.g_to_business_group_id,
50 p_from_id,
51 p_to_id,
52 p_table_name
53 FROM hr_h2pi_id_mapping im1
54 WHERE NOT EXISTS (SELECT 1
55 FROM hr_h2pi_id_mapping im2
56 WHERE to_business_group_id =
57 hr_h2pi_upload.g_to_business_group_id
58 AND from_id = p_from_id
59 AND to_id = p_to_id
60 AND table_name = p_table_name);
61 hr_utility.set_location('Leaving:'|| l_proc, 50);
62 EXCEPTION
63 WHEN INVALID_PARAM THEN
64 hr_utility.set_location(l_proc, 60);
65 fnd_file.put_line(FND_FILE.LOG,l_mesg);
66 RAISE;
67 WHEN OTHERS THEN
68 hr_utility.set_location(l_proc, 70);
69 fnd_file.put_line(FND_FILE.LOG, 'FATAL - ' || SQLERRM);
70 RAISE;
71 END create_id_mapping;
72 --
73 --
74 -- ----------------------------------------------------------------------------
75 -- |--< Get_to_Id >-----------------------------------------------------------|
76 -- ----------------------------------------------------------------------------
77 -- Description: Returns the internal id of Payroll system for the passed
78 -- internal id of HR system and table name.
79 -- ----------------------------------------------------------------------------
80 FUNCTION get_to_id (p_table_name VARCHAR2,
81 p_from_id NUMBER,
82 p_report_error BOOLEAN DEFAULT FALSE) RETURN NUMBER IS
83
84 l_proc VARCHAR2(72) := g_package||'get_to_id';
85 l_to_id hr_h2pi_id_mapping.to_id%type;
86 INVALID_PARAM EXCEPTION;
87 PRAGMA EXCEPTION_INIT(INVALID_PARAM,-20001);
88
89 BEGIN
90 IF p_table_name IS NULL THEN
91 l_mesg := 'FATAL - GET_TO_ID: '||
92 'The Parameter Value for TABLE_NAME cannot be null';
93 RAISE INVALID_PARAM;
94 ELSIF p_from_id IS NULL THEN
95 RETURN NULL;
96 END IF;
97
98 SELECT to_id INTO l_to_id
99 FROM hr_h2pi_id_mapping
100 WHERE to_business_group_id = hr_h2pi_upload.g_to_business_group_id
101 AND table_name = p_table_name
102 AND from_id = p_from_id;
103 RETURN l_to_id;
104
105 EXCEPTION
106 WHEN INVALID_PARAM THEN
107 fnd_file.put_line(FND_FILE.LOG,l_mesg);
108 RAISE;
109 WHEN NO_DATA_FOUND THEN
110 IF p_report_error THEN
111 ROLLBACK;
112 hr_h2pi_error.data_error(
113 p_from_id => p_from_id
114 ,p_table_name => p_table_name
115 ,p_message_level => 'FATAL'
116 ,p_message_name => 'HR_289241_MAPPING_ID_MISSING');
117 COMMIT;
118 raise_application_error(MAPPING_ID_MISSING_NUMBER,'MAPPING_ID_MISSING');
119 END IF;
120 return -1;
121 WHEN OTHERS THEN
122 fnd_file.put_line(FND_FILE.LOG, ' GET_TO_ID : ' || SQLERRM);
123 return -1;
124 END get_to_id;
125 --
126 --
127 -- ----------------------------------------------------------------------------
128 -- |--< Date_Error >-----------------------------------------------------------|
129 -- ----------------------------------------------------------------------------
130 -- Description: Returns the internal id of HR system for the passed
131 -- internal id of Payroll System and table name.
132 -- ----------------------------------------------------------------------------
133
134 FUNCTION get_from_id (p_table_name VARCHAR2,
135 p_to_id NUMBER,
136 p_report_error BOOLEAN DEFAULT FALSE) RETURN NUMBER IS
137
138 l_proc VARCHAR2(72) := g_package||'get_from_id';
139 l_from_id hr_h2pi_id_mapping.from_id%type;
140 INVALID_PARAM EXCEPTION;
141 PRAGMA EXCEPTION_INIT(INVALID_PARAM,-20001);
142
143 BEGIN
144 IF p_table_name is null then
145 l_mesg := 'FATAL - GET_FROM_ID : '||
146 'The Parameter Value for TABLE_NAME cannot be null';
147 RAISE INVALID_PARAM;
148 ELSIF p_to_id is null then
149 RETURN NULL;
150 END IF;
151
152 SELECT from_id INTO l_from_id
153 FROM hr_h2pi_id_mapping
154 WHERE to_business_group_id = hr_h2pi_upload.g_to_business_group_id
155 AND table_name = p_table_name
156 AND to_id = p_to_id;
157
158 RETURN l_from_id;
159
160 EXCEPTION
161 WHEN INVALID_PARAM THEN
162 fnd_file.put_line(FND_FILE.LOG,l_mesg);
163 RAISE;
164 WHEN NO_DATA_FOUND THEN
165 IF p_report_error THEN
166 ROLLBACK;
167 hr_h2pi_error.data_error(
168 p_from_id => p_to_id
169 ,p_table_name => p_table_name
170 ,p_message_level => 'FATAL'
171 ,p_message_name => 'HR_289241_MAPPING_ID_MISSING');
172 COMMIT;
173 raise_application_error(MAPPING_ID_MISSING_NUMBER,'MAPPING_ID_MISSING');
174 END IF;
175 RETURN -1;
176 WHEN OTHERS THEN
177 fnd_file.put_line(FND_FILE.LOG, ' GET_TO_ID : ' || SQLERRM);
178 RETURN -1;
179 END get_from_id;
180 --
181 END hr_h2pi_map;