[Home] [Help]
PACKAGE BODY: APPS.HR_KI_HNM_LOAD_API
Source
1 Package Body HR_KI_HNM_LOAD_API as
2 /* $Header: hrkihnml.pkb 120.1 2006/06/27 15:59:48 avarri noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(31) := 'HR_KI_HNM_LOAD_API';
7 --
8
9 procedure INSERT_ROW (
10 X_ROWID in out nocopy VARCHAR2,
11 X_hierarchy_node_map_id in out nocopy NUMBER,
12 X_TOPIC_ID in NUMBER,
13 X_HIERARCHY_ID in NUMBER,
14 X_USER_INTERFACE_ID in number,
15 X_CREATED_BY in NUMBER,
16 X_CREATION_DATE in DATE,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER
20
21 ) is
22
23 cursor C is select ROWID from HR_KI_HIERARCHY_NODE_MAPS
24 where hierarchy_node_map_id = x_hierarchy_node_map_id;
25
26 begin
27
28 select HR_KI_HIERARCHY_NODE_MAPS_S.NEXTVAL into x_hierarchy_node_map_id from sys.dual;
29
30 insert into HR_KI_HIERARCHY_NODE_MAPS (
31 hierarchy_node_map_id,
32 TOPIC_ID,
33 HIERARCHY_ID,
34 USER_INTERFACE_ID,
35 CREATION_DATE,
36 CREATED_BY,
37 LAST_UPDATE_DATE,
38 LAST_UPDATED_BY,
39 LAST_UPDATE_LOGIN,
40 OBJECT_VERSION_NUMBER
41 ) values (
42 X_hierarchy_node_map_id,
43 X_TOPIC_ID,
44 X_HIERARCHY_ID,
45 X_USER_INTERFACE_ID,
46 X_CREATION_DATE,
47 X_CREATED_BY,
48 X_LAST_UPDATE_DATE,
49 X_LAST_UPDATED_BY,
50 X_LAST_UPDATE_LOGIN,
51 1
52 );
53
54 open c;
55 fetch c into X_ROWID;
56 if (c%notfound) then
57 close c;
58 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
59 hr_utility.set_message_token('PROCEDURE',
60 'HR_KI_HIERARCHY_NODE_MAPS.insert_row');
61 hr_utility.set_message_token('STEP','1');
62 hr_utility.raise_error;
63 end if;
64 close c;
65
66
67 end INSERT_ROW;
68
69 procedure validate_keys
70 (
71 X_TOPIC_KEY VARCHAR2
72 ,X_HIERARCHY_KEY VARCHAR2
73 ,X_USER_INTERFACE_KEY VARCHAR2
74 ,X_TOPIC_ID in out nocopy number
75 ,X_HIERARCHY_ID in out nocopy number
76 ,X_USER_INTERFACE_ID in out nocopy number
77
78 )
79 is
80
81 l_proc VARCHAR2(35) := 'HR_KI_HNM_LOAD_API.VALIDATE_KEYS';
82
83 CURSOR C_VAL_TPC IS
84 select topic_id
85 from HR_KI_TOPICS
86 where upper(topic_key) = upper(X_TOPIC_KEY);
87
88 CURSOR C_VAL_HI IS
89 select HIERARCHY_ID
90 from HR_KI_HIERARCHIES
91 where upper(HIERARCHY_KEY) = upper(X_HIERARCHY_KEY);
92
93 CURSOR C_VAL_UI IS
94 select USER_INTERFACE_ID
95 from HR_KI_USER_INTERFACES
96 where upper(USER_INTERFACE_KEY) = upper(X_USER_INTERFACE_KEY);
97 begin
98
99 if X_TOPIC_KEY is not null then
100 open C_VAL_TPC;
101 fetch C_VAL_TPC into X_TOPIC_ID;
102
103 If C_VAL_TPC%NOTFOUND then
104 close C_VAL_TPC;
105 fnd_message.set_name( 'PER','PER_449923_HNM_TPCPRNT_ABSNT');
106 fnd_message.raise_error;
107 End If;
108
109 close C_VAL_TPC;
110 end if;
111
112
113 if X_HIERARCHY_KEY is not null then
114 open C_VAL_HI;
115 fetch C_VAL_HI into X_HIERARCHY_ID;
116
117 If C_VAL_HI%NOTFOUND then
118 close C_VAL_HI;
119 fnd_message.set_name( 'PER','PER_449922_HNM_HRCPRNT_ABSNT');
120 fnd_message.raise_error;
121 End If;
122
123 close C_VAL_HI;
124 end if;
125
126 if X_USER_INTERFACE_KEY is not null then
127 open C_VAL_UI;
128 fetch C_VAL_UI into X_USER_INTERFACE_ID;
129
130 If C_VAL_UI%NOTFOUND then
131 close C_VAL_UI;
132 fnd_message.set_name( 'PER','PER_449924_HNM_INTPRNT_ABSNT');
133 fnd_message.raise_error;
134 End If;
135
136 close C_VAL_UI;
137 end if;
138
139 end validate_keys;
140
141 procedure LOAD_ROW
142 (
143 X_HIERARCHY_KEY in VARCHAR2,
144 X_TOPIC_KEY in VARCHAR2,
145 X_USER_INTERFACE_KEY in VARCHAR2,
146 X_LAST_UPDATE_DATE in VARCHAR2,
147 X_CUSTOM_MODE in VARCHAR2,
148 X_OWNER in VARCHAR2
149 )
150 is
151 l_proc VARCHAR2(31) := 'HR_KI_HNM_LOAD_API.LOAD_ROW';
152 l_rowid rowid;
153 l_created_by HR_KI_HIERARCHY_NODE_MAPS.created_by%TYPE := 0;
154 l_creation_date HR_KI_HIERARCHY_NODE_MAPS.creation_date%TYPE := SYSDATE;
155 l_last_update_date HR_KI_HIERARCHY_NODE_MAPS.last_update_date%TYPE := SYSDATE;
156 l_last_updated_by HR_KI_HIERARCHY_NODE_MAPS.last_updated_by%TYPE := 0;
157 l_last_update_login HR_KI_HIERARCHY_NODE_MAPS.last_update_login%TYPE := 0;
158 l_hierarchy_node_map_id HR_KI_HIERARCHY_NODE_MAPS.hierarchy_node_map_id%TYPE;
159
160
161 l_topic_id HR_KI_TOPICS.topic_id%TYPE;
162 l_hierarchy_id HR_KI_HIERARCHIES.hierarchy_id%TYPE;
163 l_user_interface_id HR_KI_USER_INTERFACES.user_interface_id%TYPE;
164
165 db_luby number; -- entity owner in db
166 db_ludate date; -- entity update date in db
167
168 CURSOR C_APPL IS
169 select nm.hierarchy_node_map_id
170 from hr_ki_hierarchy_node_maps nm,
171 hr_ki_hierarchies h,
172 hr_ki_topics top
173 where nm.hierarchy_id = h.hierarchy_id
174 and nm.topic_id = top.topic_id
175 and
176 top.topic_key = x_topic_key
177 and
178 h.hierarchy_key = x_hierarchy_key
179
180 union
181 select nm.hierarchy_node_map_id
182 from hr_ki_hierarchy_node_maps nm,
183 hr_ki_topics top,
184 hr_ki_user_interfaces ui
185 where nm.topic_id = top.topic_id
186 and nm.user_interface_id = ui.user_interface_id
187 and top.topic_key = x_topic_key
188 and ui.user_interface_key =x_user_interface_key
189
190 union
191 select nm.hierarchy_node_map_id
192 from hr_ki_hierarchy_node_maps nm,
193 hr_ki_user_interfaces ui,
194 hr_ki_hierarchies hi
195 where nm.hierarchy_id = hi.hierarchy_id
196 and nm.user_interface_id = ui.user_interface_id
197 and ui.user_interface_key = x_user_interface_key
198 and hi.hierarchy_key = x_hierarchy_key;
199
200 begin
201 --
202 -- added for 5354277
203 hr_general.g_data_migrator_mode := 'Y';
204 --
205 --validate keys
206 validate_keys(
207 X_TOPIC_KEY => X_TOPIC_KEY
208 ,X_HIERARCHY_KEY => X_HIERARCHY_KEY
209 ,X_USER_INTERFACE_KEY => X_USER_INTERFACE_KEY
210 ,X_TOPIC_ID => l_topic_id
211 ,X_HIERARCHY_ID => l_hierarchy_id
212 ,X_USER_INTERFACE_ID => l_user_interface_id
213 );
214
215 -- Translate owner to file_last_updated_by
216 l_last_updated_by := fnd_load_util.owner_id(X_OWNER);
217 l_created_by := fnd_load_util.owner_id(X_OWNER);
218
219 -- Translate char last_update_date to date
220 l_last_update_date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD hh24:mi:ss'), sysdate);
221
222
223 -- Update or insert row as appropriate
224
225 OPEN C_APPL;
226 FETCH C_APPL INTO l_hierarchy_node_map_id;
227
228
229 if C_APPL%notfound then
230 close C_APPL;
231 INSERT_ROW
232 (
233 X_ROWID => l_rowid
234 ,X_HIERARCHY_NODE_MAP_ID => l_hierarchy_node_map_id
235 ,X_TOPIC_ID => l_topic_id
236 ,X_HIERARCHY_ID => l_hierarchy_id
237 ,X_USER_INTERFACE_ID => l_user_interface_id
238 ,X_CREATED_BY => l_created_by
239 ,X_CREATION_DATE => l_creation_date
240 ,X_LAST_UPDATE_DATE => l_last_update_date
241 ,X_LAST_UPDATED_BY => l_last_updated_by
242 ,X_LAST_UPDATE_LOGIN => l_last_update_login
243 );
244
245
246 else
247 close C_APPL;
248 --we can not provide update functionality.
249 --This is since we can not determine the correct row to be updated by
250 --using data in the ldt files
251
252 --Hence we will be updating the table by inserting a new row.
253 --Customer needs to delete the row by running some sql script.
254
255 end if;
256
257 --
258 end LOAD_ROW;
259
260 END HR_KI_HNM_LOAD_API;