[Home] [Help]
PACKAGE BODY: APPS.PER_CAL_ENTRY_VALUES_LOAD_PKG
Source
1 package body PER_CAL_ENTRY_VALUES_LOAD_PKG as
2 /* $Header: peenvlct.pkb 120.0 2005/05/31 08:10 appldev noship $ */
3
4
5 procedure KEY_TO_IDS (
6 X_ENTRY_IDENTIFIER_KEY in VARCHAR2,
7 X_VALUE_IDENTIFIER_KEY in VARCHAR2,
8 X_HIERARCHY_NODE_NAME in VARCHAR2,
9 X_PARENT_VALUE_ID_KEY in VARCHAR2,
10 X_ORG_HIER_NAME in VARCHAR2,
11 X_ORG_HIER_VERSION in NUMBER,
12 X_ORG_HIER_ELEMENT_PARENT in VARCHAR2,
13 X_ORG_HIER_ELEMENT_CHILD in VARCHAR2,
14 X_ORG_HIER_NODE_NAME in VARCHAR2,
15 X_CAL_ENTRY_VALUE_ID out nocopy NUMBER,
16 X_CALENDAR_ENTRY_ID out nocopy NUMBER,
17 X_HIERARCHY_NODE_ID out nocopy NUMBER,
18 X_PARENT_CAL_ENTRY_VALUE_ID out nocopy NUMBER,
19 X_ORG_STRUCTURE_ELEMENT_ID out nocopy NUMBER,
20 X_ORGANIZATION_ID out nocopy NUMBER) IS
21
22 cursor CSR_CAL_ENTRY_VALUES (X_VALUE IN VARCHAR2) is
23 select ENV.CAL_ENTRY_VALUE_ID
24 from PER_CAL_ENTRY_VALUES ENV
25 where ENV.IDENTIFIER_KEY = X_VALUE;
26
27 cursor CSR_SEQUENCE is
28 select PER_CAL_ENTRY_VALUES_S.nextval
29 from dual;
30
31 cursor CSR_CALENDAR_ENTRY is
32 select ENT.CALENDAR_ENTRY_ID
33 from PER_CALENDAR_ENTRIES ENT
34 where ENT.IDENTIFIER_KEY = X_ENTRY_IDENTIFIER_KEY;
35
36 cursor CSR_HIERARCHY_NODE IS
37 SELECT PGN.HIERARCHY_NODE_ID
38 FROM PER_GEN_HIERARCHY_NODES PGN
39 WHERE PGN.IDENTIFIER_KEY = X_HIERARCHY_NODE_NAME;
40
41 cursor CSR_ORG_HIER_ELEMENT is
42 select POSE.ORG_STRUCTURE_ELEMENT_ID
43 from PER_ORGANIZATION_STRUCTURES POS
44 ,PER_ORG_STRUCTURE_VERSIONS POSV
45 ,PER_ORG_STRUCTURE_ELEMENTS POSE
46 ,HR_ALL_ORGANIZATION_UNITS HOU_P
47 ,HR_ALL_ORGANIZATION_UNITS HOU_C
48 where POS.NAME = X_ORG_HIER_NAME
49 and POS.ORGANIZATION_STRUCTURE_ID = POSV.ORGANIZATION_STRUCTURE_ID
50 and POSV.VERSION_NUMBER = X_ORG_HIER_VERSION
51 and POSV.ORG_STRUCTURE_VERSION_ID = POSE.ORG_STRUCTURE_VERSION_ID
52 and POSE.ORGANIZATION_ID_PARENT = HOU_P.ORGANIZATION_ID
53 and HOU_P.NAME = X_ORG_HIER_ELEMENT_PARENT
54 and POSE.ORGANIZATION_ID_CHILD = HOU_C.ORGANIZATION_ID
55 and HOU_C.NAME = X_ORG_HIER_ELEMENT_CHILD;
56
57 cursor CSR_ORG_HIER_NODE is
58 select HOU.ORGANIZATION_ID
59 from HR_ALL_ORGANIZATION_UNITS HOU
60 where HOU.NAME = X_ORG_HIER_NODE_NAME;
61
62 begin
63
64 -- decode the ENV identifier key, else insert new
65 open CSR_CAL_ENTRY_VALUES(X_VALUE_IDENTIFIER_KEY);
66 fetch CSR_CAL_ENTRY_VALUES into X_CAL_ENTRY_VALUE_ID;
67 if (CSR_CAL_ENTRY_VALUES%notfound) then
68 close CSR_CAL_ENTRY_VALUES;
69 open CSR_SEQUENCE;
70 fetch CSR_SEQUENCE into X_CAL_ENTRY_VALUE_ID;
71 close CSR_SEQUENCE;
72 else
73 close CSR_CAL_ENTRY_VALUES;
74 end if;
75
76 open CSR_CALENDAR_ENTRY;
77 fetch CSR_CALENDAR_ENTRY into X_CALENDAR_ENTRY_ID;
78 close CSR_CALENDAR_ENTRY;
79
80 -- decode of hierarchy node name
81 IF X_HIERARCHY_NODE_NAME IS NOT NULL THEN
82 open CSR_HIERARCHY_NODE;
83 fetch CSR_HIERARCHY_NODE into X_HIERARCHY_NODE_ID;
84 close CSR_HIERARCHY_NODE;
85 END IF;
86
87 IF X_PARENT_VALUE_ID_KEY IS NOT NULL THEN
88 open CSR_CAL_ENTRY_VALUES (X_PARENT_VALUE_ID_KEY);
89 fetch CSR_CAL_ENTRY_VALUES into X_PARENT_CAL_ENTRY_VALUE_ID;
90 close CSR_CAL_ENTRY_VALUES;
91 END IF;
92
93 -- Decode the ORG hierarchy element name
94 IF X_ORG_HIER_NAME IS NOT NULL AND
95 X_ORG_HIER_VERSION IS NOT NULL AND
96 X_ORG_HIER_ELEMENT_PARENT IS NOT NULL AND
97 X_ORG_HIER_ELEMENT_CHILD IS NOT NULL THEN
98 open CSR_ORG_HIER_ELEMENT;
99 fetch CSR_ORG_HIER_ELEMENT into X_ORG_STRUCTURE_ELEMENT_ID;
100 close CSR_ORG_HIER_ELEMENT;
101 END IF;
102
103 -- Decode the ORG hierarchy node name
104 IF X_ORG_HIER_NODE_NAME IS NOT NULL THEN
105 open CSR_ORG_HIER_NODE;
106 fetch CSR_ORG_HIER_NODE into X_ORGANIZATION_ID;
107 close CSR_ORG_HIER_NODE;
108 END IF;
109
110 exception
111 when others then
112 fnd_message.set_name('PAY', 'x_value:' || X_VALUE_IDENTIFIER_KEY);
113 fnd_message.raise_error;
114
115
116 end KEY_TO_IDS;
117
118
119 procedure INSERT_ROW (
120 X_CAL_ENTRY_VALUE_ID in NUMBER,
121 X_CALENDAR_ENTRY_ID in NUMBER,
122 X_HIERARCHY_NODE_ID in NUMBER,
123 X_IDVALUE in VARCHAR2,
124 X_ORG_STRUCTURE_ELEMENT_ID in NUMBER,
125 X_ORGANIZATION_ID in NUMBER,
126 X_OVERRIDE_NAME in VARCHAR2,
127 X_OVERRIDE_TYPE in VARCHAR2,
128 X_PARENT_ENTRY_VALUE_ID in NUMBER,
129 X_USAGE_FLAG in VARCHAR2,
130 X_CREATED_BY in NUMBER,
131 X_LAST_UPDATE_DATE in DATE,
132 X_LAST_UPDATED_BY in NUMBER,
133 X_LAST_UPDATE_LOGIN in NUMBER,
134 X_CREATION_DATE in DATE,
135 X_IDENTIFIER_KEY in VARCHAR2) is
136
137 cursor C is select ROWID from PER_CAL_ENTRY_VALUES
138 where CAL_ENTRY_VALUE_ID = X_CAL_ENTRY_VALUE_ID;
139 csr_row C%rowtype;
140 begin
141 insert into PER_CAL_ENTRY_VALUES (
142 CAL_ENTRY_VALUE_ID,
143 CALENDAR_ENTRY_ID,
144 HIERARCHY_NODE_ID,
145 IDVALUE,
146 ORG_STRUCTURE_ELEMENT_ID,
147 ORGANIZATION_ID,
148 OVERRIDE_NAME,
149 OVERRIDE_TYPE,
150 PARENT_ENTRY_VALUE_ID,
151 USAGE_FLAG,
152 OBJECT_VERSION_NUMBER,
153 CREATION_DATE,
154 CREATED_BY,
155 LAST_UPDATE_DATE,
156 LAST_UPDATED_BY,
157 LAST_UPDATE_LOGIN,
158 IDENTIFIER_KEY
159 ) values (
160 X_CAL_ENTRY_VALUE_ID,
161 X_CALENDAR_ENTRY_ID,
162 X_HIERARCHY_NODE_ID,
163 X_IDVALUE,
164 X_ORG_STRUCTURE_ELEMENT_ID,
165 X_ORGANIZATION_ID,
166 X_OVERRIDE_NAME,
167 X_OVERRIDE_TYPE,
168 X_PARENT_ENTRY_VALUE_ID,
169 X_USAGE_FLAG,
170 1,
171 X_CREATION_DATE,
172 X_CREATED_BY,
173 X_LAST_UPDATE_DATE,
174 X_LAST_UPDATED_BY,
175 X_LAST_UPDATE_LOGIN,
176 X_IDENTIFIER_KEY
177 );
178
179 open c;
180 fetch c into csr_row;
181 if (c%notfound) then
182 close c;
183 raise no_data_found;
184 end if;
185 close c;
186
187 end INSERT_ROW;
188
189
190 procedure LOAD_ROW (
191 X_VALUE_IDENTIFIER_KEY in VARCHAR2,
192 X_PARENT_VALUE_IDENTIFIER_KEY in VARCHAR2,
193 X_ENTRY_IDENTIFIER_KEY in VARCHAR2,
194 X_HIERARCHY_NODE_NAME in VARCHAR2,
195 X_IDVALUE in VARCHAR2,
196 X_ORG_HIER_NAME in VARCHAR2,
197 X_ORG_HIER_VERSION in NUMBER,
198 X_ORG_HIER_ELEMENT_PARENT in VARCHAR2,
199 X_ORG_HIER_ELEMENT_CHILD in VARCHAR2,
200 X_ORG_HIER_NODE_NAME in VARCHAR2,
201 X_OVERRIDE_NAME in VARCHAR2,
202 X_OVERRIDE_TYPE in VARCHAR2,
203 X_USAGE_FLAG in VARCHAR2,
204 X_OWNER in VARCHAR2,
205 X_LAST_UPDATE_DATE in VARCHAR2) IS
206
207 X_ROWID ROWID;
208 user_id number := 0;
209 X_CAL_ENTRY_VALUE_ID NUMBER;
210 X_PARENT_ENTRY_VALUE_ID NUMBER;
211 X_CALENDAR_ENTRY_ID NUMBER;
212 X_HIERARCHY_NODE_ID NUMBER;
213 X_ORG_STRUCTURE_ELEMENT_ID NUMBER;
214 X_ORGANIZATION_ID NUMBER;
215
216 begin
217
218 -- translate keys to IDs
219 -- (if inserting, CAL_ENTRY_VALUE_ID is obtained
220 -- from sequence else from table)
221 KEY_TO_IDS ( X_ENTRY_IDENTIFIER_KEY
222 ,X_VALUE_IDENTIFIER_KEY
223 ,X_HIERARCHY_NODE_NAME
224 ,X_PARENT_VALUE_IDENTIFIER_KEY
225 ,X_ORG_HIER_NAME
226 ,X_ORG_HIER_VERSION
227 ,X_ORG_HIER_ELEMENT_PARENT
228 ,X_ORG_HIER_ELEMENT_CHILD
229 ,X_ORG_HIER_NODE_NAME
230 ,X_CAL_ENTRY_VALUE_ID
231 ,X_CALENDAR_ENTRY_ID
232 ,X_HIERARCHY_NODE_ID
233 ,X_PARENT_ENTRY_VALUE_ID
234 ,X_ORG_STRUCTURE_ELEMENT_ID
235 ,X_ORGANIZATION_ID);
236
237 if (X_OWNER = 'SEED') then
238 user_id := 1;
239 else
240 user_id := 0;
241 end if;
242
243 PER_CAL_ENTRY_VALUES_LOAD_PKG.UPDATE_ROW (
244 X_CAL_ENTRY_VALUE_ID => X_CAL_ENTRY_VALUE_ID
245 ,X_HIERARCHY_NODE_ID => X_HIERARCHY_NODE_ID
246 ,X_IDVALUE => X_IDVALUE
247 ,X_ORG_STRUCTURE_ELEMENT_ID => X_ORG_STRUCTURE_ELEMENT_ID
248 ,X_ORGANIZATION_ID => X_ORGANIZATION_ID
249 ,X_OVERRIDE_NAME => X_OVERRIDE_NAME
250 ,X_OVERRIDE_TYPE => X_OVERRIDE_TYPE
251 ,X_USAGE_FLAG => X_USAGE_FLAG
252 ,X_LAST_UPDATE_DATE => sysdate
253 ,X_LAST_UPDATED_BY => user_id
254 ,X_LAST_UPDATE_LOGIN => user_id);
255
256 exception
257 when NO_DATA_FOUND then
258 PER_CAL_ENTRY_VALUES_LOAD_PKG.INSERT_ROW (
259 X_CAL_ENTRY_VALUE_ID => X_CAL_ENTRY_VALUE_ID
260 ,X_CALENDAR_ENTRY_ID => X_CALENDAR_ENTRY_ID
261 ,X_HIERARCHY_NODE_ID => X_HIERARCHY_NODE_ID
262 ,X_IDVALUE => X_IDVALUE
263 ,X_ORG_STRUCTURE_ELEMENT_ID => X_ORG_STRUCTURE_ELEMENT_ID
264 ,X_ORGANIZATION_ID => X_ORGANIZATION_ID
265 ,X_OVERRIDE_NAME => X_OVERRIDE_NAME
266 ,X_OVERRIDE_TYPE => X_OVERRIDE_TYPE
267 ,X_PARENT_ENTRY_VALUE_ID => X_PARENT_ENTRY_VALUE_ID
268 ,X_USAGE_FLAG => X_USAGE_FLAG
269 ,X_CREATED_BY => user_id
270 ,X_LAST_UPDATE_DATE => sysdate
271 ,X_LAST_UPDATED_BY => user_id
272 ,X_LAST_UPDATE_LOGIN => 0
273 ,X_CREATION_DATE => SYSDATE
274 ,X_IDENTIFIER_KEY => X_VALUE_IDENTIFIER_KEY);
275
276 end LOAD_ROW;
277
278 procedure UPDATE_ROW (
279 X_CAL_ENTRY_VALUE_ID in NUMBER,
280 X_HIERARCHY_NODE_ID in NUMBER,
281 X_IDVALUE in VARCHAR2,
282 X_ORG_STRUCTURE_ELEMENT_ID in NUMBER,
283 X_ORGANIZATION_ID in NUMBER,
284 X_OVERRIDE_NAME in VARCHAR2,
285 X_OVERRIDE_TYPE in VARCHAR2,
286 X_USAGE_FLAG in VARCHAR2,
287 X_LAST_UPDATE_DATE in DATE,
288 X_LAST_UPDATED_BY in NUMBER,
289 X_LAST_UPDATE_LOGIN in NUMBER) IS
290
291 begin
292 update PER_CAL_ENTRY_VALUES set
293 HIERARCHY_NODE_ID = X_HIERARCHY_NODE_ID,
294 IDVALUE = X_IDVALUE,
295 ORG_STRUCTURE_ELEMENT_ID = X_ORG_STRUCTURE_ELEMENT_ID,
296 ORGANIZATION_ID = X_ORGANIZATION_ID,
297 OVERRIDE_NAME = X_OVERRIDE_NAME,
298 OVERRIDE_TYPE = X_OVERRIDE_TYPE,
299 USAGE_FLAG = X_USAGE_FLAG,
300 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
301 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
302 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
303 where CAL_ENTRY_VALUE_ID = X_CAL_ENTRY_VALUE_ID;
304
305 if (sql%notfound) then
306 raise no_data_found;
307 end if;
308
309 end UPDATE_ROW;
310
311 end PER_CAL_ENTRY_VALUES_LOAD_PKG;