[Home] [Help]
PACKAGE BODY: APPS.PER_CALENDAR_ENTRIES_LOAD_PKG
Source
1 package body PER_CALENDAR_ENTRIES_LOAD_PKG as
2 /* $Header: peentlct.pkb 120.0 2005/05/31 08:08 appldev noship $ */
3
4 procedure KEY_TO_IDS (
5 X_IDENTIFIER_KEY in VARCHAR2,
6 X_FLEX_VALUE_SET_NAME in VARCHAR2,
7 X_HIERARCHY_NAME in VARCHAR2,
8 X_BUS_GRP_NAME in VARCHAR2,
9 X_ORG_HIER_NAME in VARCHAR2,
10 X_ORG_HIER_VERSION in NUMBER,
11 X_CALENDAR_ENTRY_ID out nocopy NUMBER,
12 X_FLEX_VALUE_SET_ID out nocopy NUMBER,
13 X_HIERARCHY_ID out nocopy NUMBER,
14 X_BUS_GRP_ID out nocopy NUMBER,
15 X_ORG_STRUCT_ID out nocopy NUMBER,
16 X_ORG_STRUCT_VER_ID out nocopy NUMBER) is
17
18 cursor CSR_HIERARCHY (
19 X_HIERARCHY_NAME in VARCHAR2
20 ) is
21 select PGH.HIERARCHY_ID
22 from PER_GEN_HIERARCHY PGH
23 where PGH.NAME = X_HIERARCHY_NAME;
24
25 cursor CSR_FLEX_VALUE_SET (
26 X_FLEX_VALUE_SET_NAME in VARCHAR2
27 ) is
28 select FVS.FLEX_VALUE_SET_ID
29 from FND_FLEX_VALUE_SETS FVS
30 where FVS.FLEX_VALUE_SET_NAME = X_FLEX_VALUE_SET_NAME;
31
32 cursor CSR_CALENDAR_ENTRY (
33 X_IDENTIFIER_KEY in VARCHAR2
34 ) is
35 select ENT.CALENDAR_ENTRY_ID
36 from PER_CALENDAR_ENTRIES ENT
37 where ENT.IDENTIFIER_KEY = X_IDENTIFIER_KEY;
38
39 cursor CSR_SEQUENCE is
40 select PER_CALENDAR_ENTRIES_S.nextval
41 from dual;
42
43 cursor CSR_BUS_GRP (
44 X_BUS_GRP_NAME in VARCHAR2
45 )is
46 select HOU.BUSINESS_GROUP_ID
47 from HR_ALL_ORGANIZATION_UNITS HOU
48 where HOU.NAME = X_BUS_GRP_NAME
49 and HOU.ORGANIZATION_ID = HOU.BUSINESS_GROUP_ID;
50
51 cursor CSR_ORG_STRUCT (
52 X_ORG_HIER_NAME in VARCHAR2
53 ) is
54 select POS.ORGANIZATION_STRUCTURE_ID
55 from PER_ORGANIZATION_STRUCTURES POS
56 where POS.NAME = X_ORG_HIER_NAME;
57
58 cursor CSR_ORG_STRUCT_VER (
59 X_ORG_HIER_NAME in VARCHAR2,
60 X_ORG_HIER_VERSION in NUMBER
61 ) is
62 select POSV.ORG_STRUCTURE_VERSION_ID
63 from PER_ORGANIZATION_STRUCTURES POS,
64 PER_ORG_STRUCTURE_VERSIONS POSV
65 where POS.NAME = X_ORG_HIER_NAME
66 and POS.ORGANIZATION_STRUCTURE_ID = POSV.ORGANIZATION_STRUCTURE_ID
67 and POSV.VERSION_NUMBER = X_ORG_HIER_VERSION;
68
69 begin
70
71 open CSR_HIERARCHY (
72 X_HIERARCHY_NAME
73 );
74 fetch CSR_HIERARCHY into X_HIERARCHY_ID;
75 close CSR_HIERARCHY;
76
77 open CSR_FLEX_VALUE_SET (
78 X_FLEX_VALUE_SET_NAME
79 );
80 fetch CSR_FLEX_VALUE_SET into X_FLEX_VALUE_SET_ID;
81 close CSR_FLEX_VALUE_SET;
82
83 open CSR_CALENDAR_ENTRY (
84 X_IDENTIFIER_KEY
85 );
86 fetch CSR_CALENDAR_ENTRY into X_CALENDAR_ENTRY_ID;
87 if (CSR_CALENDAR_ENTRY%notfound) then
88 close CSR_CALENDAR_ENTRY;
89 open CSR_SEQUENCE;
90 fetch CSR_SEQUENCE into X_CALENDAR_ENTRY_ID;
91 close CSR_SEQUENCE;
92 else
93 close CSR_CALENDAR_ENTRY;
94 end if;
95
96 open CSR_BUS_GRP (
97 X_BUS_GRP_NAME
98 );
99 fetch CSR_BUS_GRP into X_BUS_GRP_ID;
100 close CSR_BUS_GRP;
101
102 open CSR_ORG_STRUCT (
103 X_ORG_HIER_NAME
104 );
105 fetch CSR_ORG_STRUCT into X_ORG_STRUCT_ID;
106 close CSR_ORG_STRUCT;
107
108 open CSR_ORG_STRUCT_VER (
109 X_ORG_HIER_NAME,
110 X_ORG_HIER_VERSION
111 );
112 fetch CSR_ORG_STRUCT_VER into X_ORG_STRUCT_VER_ID;
113 close CSR_ORG_STRUCT_VER;
114
115 end KEY_TO_IDS;
116
117
118 procedure INSERT_ROW (
119 X_CALENDAR_ENTRY_ID in NUMBER,
120 X_NAME in VARCHAR2,
121 X_TYPE in VARCHAR2,
122 X_START_DATE in DATE,
123 X_END_DATE in DATE,
124 X_START_HOUR in VARCHAR2,
125 X_START_MIN in VARCHAR2,
126 X_END_HOUR in VARCHAR2,
127 X_END_MIN in VARCHAR2,
128 X_HIERARCHY_ID in NUMBER,
129 X_VALUE_SET_ID in NUMBER,
130 X_ORG_STRUCT_ID in NUMBER,
131 X_ORG_STRUCT_VER_ID in NUMBER,
132 X_DESCRIPTION in VARCHAR2,
133 X_CREATION_DATE in DATE,
134 X_CREATED_BY in NUMBER,
135 X_LAST_UPDATE_DATE in DATE,
136 X_LAST_UPDATED_BY in NUMBER,
137 X_LAST_UPDATE_LOGIN in NUMBER,
138 X_BUS_GRP_ID in NUMBER,
139 X_IDENTIFIER_KEY in VARCHAR2,
140 X_LEGISLATION_CODE in VARCHAR2
141 ) is
142 cursor C is select ROWID from PER_CALENDAR_ENTRIES
143 where CALENDAR_ENTRY_ID = X_CALENDAR_ENTRY_ID
144 ;
145 csr_row C%rowtype;
146 begin
147 insert into PER_CALENDAR_ENTRIES (
148 CALENDAR_ENTRY_ID,
149 NAME,
150 TYPE,
151 START_DATE,
152 END_DATE,
153 START_HOUR,
154 START_MIN,
155 END_HOUR,
156 END_MIN,
157 HIERARCHY_ID,
158 VALUE_SET_ID,
159 ORGANIZATION_STRUCTURE_ID,
160 ORG_STRUCTURE_VERSION_ID,
161 DESCRIPTION,
162 OBJECT_VERSION_NUMBER,
163 CREATION_DATE,
164 CREATED_BY,
165 LAST_UPDATE_DATE,
166 LAST_UPDATED_BY,
167 LAST_UPDATE_LOGIN,
168 BUSINESS_GROUP_ID,
169 IDENTIFIER_KEY,
170 LEGISLATION_CODE
171 ) values (
172 X_CALENDAR_ENTRY_ID,
173 X_NAME,
174 X_TYPE,
175 X_START_DATE,
176 X_END_DATE,
177 X_START_HOUR,
178 X_START_MIN,
179 X_END_HOUR,
180 X_END_MIN,
181 X_HIERARCHY_ID,
182 X_VALUE_SET_ID,
183 X_ORG_STRUCT_ID,
184 X_ORG_STRUCT_VER_ID,
185 X_DESCRIPTION,
186 1,
187 X_CREATION_DATE,
188 X_CREATED_BY,
189 X_LAST_UPDATE_DATE,
190 X_LAST_UPDATED_BY,
191 X_LAST_UPDATE_LOGIN,
192 X_BUS_GRP_ID,
193 X_IDENTIFIER_KEY,
194 X_LEGISLATION_CODE
195 );
196
197 open c;
198 fetch c into csr_row;
199 if (c%notfound) then
200 close c;
201 raise no_data_found;
202 end if;
203 close c;
204
205 end INSERT_ROW;
206
207
208 procedure LOAD_ROW (
209 X_IDENTIFIER_KEY in VARCHAR2,
210 X_LEGISLATION_CODE in VARCHAR2,
211 X_BUS_GRP_NAME in VARCHAR2,
212 X_NAME in VARCHAR2,
213 X_START_DATE in VARCHAR2,
214 X_END_DATE in VARCHAR2,
215 X_TYPE in VARCHAR2,
216 X_START_HOUR in VARCHAR2,
217 X_START_MIN in VARCHAR2,
218 X_END_HOUR in VARCHAR2,
219 X_END_MIN in VARCHAR2,
220 X_HIERARCHY_NAME in VARCHAR2,
221 X_FLEX_VALUE_SET_NAME in VARCHAR2,
222 X_ORG_HIER_NAME in VARCHAR2,
223 X_ORG_HIER_VERSION in NUMBER,
224 X_DESCRIPTION in VARCHAR2,
225 X_OWNER in VARCHAR2,
226 X_LAST_UPDATE_DATE in VARCHAR2)
227
228 is
229
230 X_ROWID ROWID;
231 user_id number := 0;
232 X_CALENDAR_ENTRY_ID NUMBER;
233 X_VALUE_SET_ID NUMBER;
234 X_HIERARCHY_ID NUMBER;
235 X_BUS_GRP_ID NUMBER;
236 X_ORG_STRUCT_ID NUMBER;
237 X_ORG_STRUCT_VER_ID NUMBER;
238
239 begin
240
241 -- translate keys to IDs
242 -- (if inserting, CALENDAR_ENTRY_ID is obtained from sequence
243 -- else from table)
244 KEY_TO_IDS ( X_IDENTIFIER_KEY
245 ,X_FLEX_VALUE_SET_NAME
246 ,X_HIERARCHY_NAME
247 ,X_BUS_GRP_NAME
248 ,X_ORG_HIER_NAME
249 ,X_ORG_HIER_VERSION
250 ,X_CALENDAR_ENTRY_ID
251 ,X_VALUE_SET_ID
252 ,X_HIERARCHY_ID
253 ,X_BUS_GRP_ID
254 ,X_ORG_STRUCT_ID
255 ,X_ORG_STRUCT_VER_ID);
256
257 if (X_OWNER = 'SEED') then
258 user_id := 1;
259 else
260 user_id := 0;
261 end if;
262
263 PER_CALENDAR_ENTRIES_LOAD_PKG.UPDATE_ROW (
264 X_CALENDAR_ENTRY_ID => X_CALENDAR_ENTRY_ID
265 ,X_NAME => X_NAME
266 ,X_TYPE => X_TYPE
267 ,X_START_DATE => to_date(X_START_DATE,'DD/MM/YYYY')
268 ,X_END_DATE => to_date(X_END_DATE,'DD/MM/YYYY')
269 ,X_START_HOUR => X_START_HOUR
270 ,X_START_MIN => X_START_MIN
271 ,X_END_HOUR => X_END_HOUR
272 ,X_END_MIN => X_END_MIN
273 ,X_VALUE_SET_ID => X_VALUE_SET_ID
274 ,X_HIERARCHY_ID => X_HIERARCHY_ID
275 ,X_ORG_STRUCT_ID => X_ORG_STRUCT_ID
276 ,X_ORG_STRUCT_VER_ID => X_ORG_STRUCT_VER_ID
277 ,X_DESCRIPTION => X_DESCRIPTION
278 ,X_LAST_UPDATE_DATE => sysdate
279 ,X_LAST_UPDATED_BY => user_id
280 ,X_LAST_UPDATE_LOGIN => user_id); -- note: ID Key, and LEG Code are not updateable
281
282 exception
283 when NO_DATA_FOUND then
284 -- insert a row with NULL business group (meaningless)
285 -- as data is created at Legislation level.
286 -- (UI restricts on BG, or Leg Code).
287 PER_CALENDAR_ENTRIES_LOAD_PKG.INSERT_ROW (
288 X_CALENDAR_ENTRY_ID => X_CALENDAR_ENTRY_ID
289 ,X_NAME => X_NAME
290 ,X_TYPE => X_TYPE
291 ,X_START_DATE => to_date(X_START_DATE,'DD/MM/YYYY')
292 ,X_END_DATE => to_date(X_END_DATE,'DD/MM/YYYY')
293 ,X_START_HOUR => X_START_HOUR
294 ,X_START_MIN => X_START_MIN
295 ,X_END_HOUR => X_END_HOUR
296 ,X_END_MIN => X_END_MIN
297 ,X_VALUE_SET_ID => X_VALUE_SET_ID
298 ,X_HIERARCHY_ID => X_HIERARCHY_ID
299 ,X_ORG_STRUCT_ID => X_ORG_STRUCT_ID
300 ,X_ORG_STRUCT_VER_ID => X_ORG_STRUCT_VER_ID
301 ,X_DESCRIPTION => X_DESCRIPTION
302 ,X_LAST_UPDATE_DATE => sysdate
303 ,X_LAST_UPDATED_BY => user_id
304 ,X_LAST_UPDATE_LOGIN => 0
305 ,X_CREATION_DATE => SYSDATE
306 ,X_CREATED_BY => user_id
307 ,X_BUS_GRP_ID => X_BUS_GRP_ID
308 ,X_IDENTIFIER_KEY => X_IDENTIFIER_KEY
309 ,X_LEGISLATION_CODE => X_LEGISLATION_CODE);
310
311 end LOAD_ROW;
312
313 procedure UPDATE_ROW (
314 X_CALENDAR_ENTRY_ID in NUMBER,
315 X_NAME in VARCHAR2,
316 X_TYPE in VARCHAR2,
317 X_START_DATE in DATE,
318 X_END_DATE in DATE,
319 X_START_HOUR in VARCHAR2,
320 X_START_MIN in VARCHAR2,
321 X_END_HOUR in VARCHAR2,
322 X_END_MIN in VARCHAR2,
323 X_HIERARCHY_ID in NUMBER,
324 X_VALUE_SET_ID in NUMBER,
325 X_ORG_STRUCT_ID in NUMBER,
326 X_ORG_STRUCT_VER_ID in NUMBER,
327 X_DESCRIPTION in VARCHAR2,
328 X_LAST_UPDATE_DATE in DATE,
329 X_LAST_UPDATED_BY in NUMBER,
330 X_LAST_UPDATE_LOGIN in NUMBER) is
331 begin
332 update PER_CALENDAR_ENTRIES set
333 NAME = X_NAME,
334 TYPE = X_TYPE,
335 START_DATE = X_START_DATE,
336 END_DATE = X_END_DATE,
337 START_HOUR = X_START_HOUR,
338 START_MIN = X_START_MIN,
342 VALUE_SET_ID = X_VALUE_SET_ID,
339 END_HOUR = X_END_HOUR,
340 END_MIN = X_END_MIN,
341 HIERARCHY_ID = HIERARCHY_ID,
343 ORGANIZATION_STRUCTURE_ID = X_ORG_STRUCT_ID,
344 ORG_STRUCTURE_VERSION_ID = X_ORG_STRUCT_VER_ID,
345 DESCRIPTION = X_DESCRIPTION,
346 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
347 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
348 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
349 where CALENDAR_ENTRY_ID = X_CALENDAR_ENTRY_ID;
350
351 if (sql%notfound) then
352 raise no_data_found;
353 end if;
354
355 end UPDATE_ROW;
356
357 end PER_CALENDAR_ENTRIES_LOAD_PKG;