DBA Data[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;