[Home] [Help]
PACKAGE BODY: APPS.AME_ITEM_CLASS_USAGES_API
Source
1 PACKAGE BODY AME_ITEM_CLASS_USAGES_API AS
2 /* $Header: ameiuapi.pkb 120.3 2005/10/14 04:13 ubhat noship $ */
3 procedure OWNER_TO_WHO (
4 X_OWNER in VARCHAR2,
5 X_CREATED_BY out nocopy NUMBER,
6 X_LAST_UPDATED_BY out nocopy NUMBER,
7 X_LAST_UPDATE_LOGIN out nocopy NUMBER
8 ) is
9 begin
10 X_CREATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
11 X_LAST_UPDATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
12 X_LAST_UPDATE_LOGIN := 0;
13 end OWNER_TO_WHO;
14
15 procedure KEY_TO_IDS (
16 X_ITEM_CLASS_NAME in VARCHAR2,
17 X_APPLICATION_NAME in VARCHAR2,
18 X_USAGES_ROWID out nocopy VARCHAR2,
19 X_ITEM_CLASS_ID out nocopy NUMBER,
20 X_APPLICATION_ID out nocopy NUMBER,
21 X_CURRENT_OWNER out nocopy NUMBER,
22 X_CURRENT_LAST_UPDATE_DATE out nocopy VARCHAR2,
23 X_CURRENT_OVN out nocopy VARCHAR2
24 ) is
25 cursor CSR_GET_ITEM_CLASS_ID
26 (
27 X_ITEM_CLASS_NAME in VARCHAR2
28 ) is
29 select ITEM_CLASS_ID
30 from AME_ITEM_CLASSES
31 where NAME = X_ITEM_CLASS_NAME
32 and sysdate between START_DATE
33 and nvl(END_DATE - (1/86400), sysdate);
34
35 cursor CSR_GET_APPLICATION_ID
36 (
37 X_APPLICATION_NAME in VARCHAR2
38 ) is
39 select APPLICATION_ID
40 from AME_CALLING_APPS
41 where APPLICATION_NAME = X_APPLICATION_NAME
42 and sysdate between START_DATE
43 and nvl(END_DATE - (1/86400), sysdate);
44
45 cursor CSR_GET_ITEM_CLASS_USAGE
46 (
47 X_ITEM_CLASS_ID in varchar2,
48 X_APPLICATION_ID in varchar2
49 ) is
50 select ROWID,
51 LAST_UPDATED_BY,
52 to_char(LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
53 nvl(OBJECT_VERSION_NUMBER,1)
54 from AME_ITEM_CLASS_USAGES
55 where ITEM_CLASS_ID = X_ITEM_CLASS_ID
56 and APPLICATION_ID = X_APPLICATION_ID
57 and sysdate between START_DATE
58 and nvl(END_DATE - (1/86400), sysdate);
59 begin
60 X_CURRENT_OVN := 1;
61 open CSR_GET_ITEM_CLASS_ID (
62 X_ITEM_CLASS_NAME
63 );
64 fetch CSR_GET_ITEM_CLASS_ID into X_ITEM_CLASS_ID;
65 if (CSR_GET_ITEM_CLASS_ID%notfound) then
66 X_ITEM_CLASS_ID := null;
67 end if;
68 close CSR_GET_ITEM_CLASS_ID;
69
70 open CSR_GET_APPLICATION_ID (
71 X_APPLICATION_NAME
72 );
73 fetch CSR_GET_APPLICATION_ID into X_APPLICATION_ID;
74 if (CSR_GET_APPLICATION_ID%notfound) then
75 X_APPLICATION_ID := null;
76 end if;
77 close CSR_GET_APPLICATION_ID;
78
79 if (X_APPLICATION_ID is not null) and
80 (X_ITEM_CLASS_ID is not null) then
81 open CSR_GET_ITEM_CLASS_USAGE (
82 X_ITEM_CLASS_ID,
83 X_APPLICATION_ID
84 );
85 fetch CSR_GET_ITEM_CLASS_USAGE into X_USAGES_ROWID,
86 X_CURRENT_OWNER,
87 X_CURRENT_LAST_UPDATE_DATE,
88 X_CURRENT_OVN;
89 if (CSR_GET_ITEM_CLASS_USAGE%notfound) then
90 X_USAGES_ROWID := null;
91 end if;
92 close CSR_GET_ITEM_CLASS_USAGE;
93 else
94 X_USAGES_ROWID := null;
95 end if;
96 end KEY_TO_IDS;
97 function DO_UPDATE_INSERT(X_OWNER in NUMBER,
98 X_CURRENT_OWNER in NUMBER,
99 X_LAST_UPDATE_DATE in VARCHAR2,
100 X_CURRENT_LAST_UPDATE_DATE in VARCHAR2,
101 X_CUSTOM_MODE in VARCHAR2 default null)
102 return boolean as
103 begin
104 return AME_SEED_UTILITY.MERGE_ROW_TEST
105 (X_OWNER => X_OWNER
106 ,X_CURRENT_OWNER => X_CURRENT_OWNER
107 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
108 ,X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE
109 ,X_CUSTOM_MODE => X_CUSTOM_MODE
110 );
111 end DO_UPDATE_INSERT;
112 procedure INSERT_ROW (
113 X_ITEM_CLASS_ID in NUMBER,
114 X_APPLICATION_ID in NUMBER,
115 X_ITEM_ID_QUERY in VARCHAR2,
116 X_ITEM_CLASS_ORDER_NUMBER in NUMBER,
117 X_ITEM_CLASS_PAR_MODE in VARCHAR2,
118 X_ITEM_CLASS_SUBLIST_MODE in VARCHAR2,
119 X_CREATED_BY in NUMBER,
120 X_CREATION_DATE in DATE,
121 X_LAST_UPDATED_BY in NUMBER,
122 X_LAST_UPDATE_DATE in DATE,
123 X_LAST_UPDATE_LOGIN in NUMBER,
124 X_START_DATE in DATE,
125 X_OBJECT_VERSION_NUMBER in NUMBER)
126 is
127 begin
128 insert into AME_ITEM_CLASS_USAGES
129 (ITEM_CLASS_ID,
130 APPLICATION_ID,
131 ITEM_ID_QUERY,
132 ITEM_CLASS_ORDER_NUMBER,
133 ITEM_CLASS_PAR_MODE,
134 ITEM_CLASS_SUBLIST_MODE,
135 CREATED_BY,
136 CREATION_DATE,
137 LAST_UPDATED_BY,
138 LAST_UPDATE_DATE,
139 LAST_UPDATE_LOGIN,
140 START_DATE,
141 END_DATE,
142 OBJECT_VERSION_NUMBER
143 ) values (
144 X_ITEM_CLASS_ID,
145 X_APPLICATION_ID,
146 X_ITEM_ID_QUERY,
147 X_ITEM_CLASS_ORDER_NUMBER,
148 X_ITEM_CLASS_PAR_MODE,
149 X_ITEM_CLASS_SUBLIST_MODE,
150 X_CREATED_BY,
151 X_CREATION_DATE,
152 X_LAST_UPDATED_BY,
153 X_LAST_UPDATE_DATE,
154 X_LAST_UPDATE_LOGIN,
155 X_START_DATE,
156 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
157 X_OBJECT_VERSION_NUMBER);
158
159 end INSERT_ROW;
160
161 procedure FORCE_UPDATE_ROW (
162 X_ROWID in VARCHAR2,
163 X_ITEM_ID_QUERY in VARCHAR2,
164 X_ITEM_CLASS_ORDER_NUMBER in NUMBER,
165 X_ITEM_CLASS_SUBLIST_MODE in VARCHAR2,
166 X_ITEM_CLASS_PAR_MODE in VARCHAR2,
167 X_CREATED_BY in NUMBER,
168 X_CREATION_DATE in DATE,
169 X_LAST_UPDATED_BY in NUMBER,
170 X_LAST_UPDATE_DATE in DATE,
171 X_LAST_UPDATE_LOGIN in NUMBER,
172 X_START_DATE in DATE,
173 X_END_DATE in DATE,
174 X_OBJECT_VERSION_NUMBER in NUMBER
175 ) is
176 begin
177 update AME_ITEM_CLASS_USAGES
178 set ITEM_CLASS_ORDER_NUMBER = X_ITEM_CLASS_ORDER_NUMBER,
179 ITEM_ID_QUERY = X_ITEM_ID_QUERY,
180 ITEM_CLASS_SUBLIST_MODE = X_ITEM_CLASS_SUBLIST_MODE,
181 ITEM_CLASS_PAR_MODE = X_ITEM_CLASS_PAR_MODE,
182 CREATED_BY = X_CREATED_BY,
183 CREATION_DATE = X_CREATION_DATE,
184 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
185 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
186 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
187 START_DATE = X_START_DATE,
188 END_DATE = X_END_DATE,
189 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
190 where ROWID = X_ROWID;
191 end FORCE_UPDATE_ROW;
192
193 procedure UPDATE_ROW (
194 X_USAGES_ROWID in VARCHAR2,
195 X_END_DATE in DATE)
196 is
197 begin
198 update AME_ITEM_CLASS_USAGES set
199 END_DATE = X_END_DATE
200 where ROWID = X_USAGES_ROWID;
201 end UPDATE_ROW;
202
203 procedure DELETE_ROW (
204 X_ITEM_CLASS_ID in NUMBER,
205 X_APPLICATION_ID in NUMBER
206 ) is
207 begin
208 delete from AME_ITEM_CLASS_USAGES
209 where ITEM_CLASS_ID = X_ITEM_CLASS_ID
210 and APPLICATION_ID = X_APPLICATION_ID;
211
212 if (sql%notfound) then
213 raise no_data_found;
214 end if;
215 end DELETE_ROW;
216
217 procedure LOAD_ROW (
218 X_ITEM_CLASS_NAME in VARCHAR2,
219 X_APPLICATION_NAME in VARCHAR2,
220 X_ITEM_ID_QUERY in VARCHAR2,
221 X_ITEM_CLASS_ORDER_NUMBER in VARCHAR2,
222 X_ITEM_CLASS_PAR_MODE in VARCHAR2,
223 X_ITEM_CLASS_SUBLIST_MODE in VARCHAR2,
224 X_OWNER in VARCHAR2,
225 X_LAST_UPDATE_DATE in VARCHAR2,
226 X_CUSTOM_MODE in VARCHAR2
227 )
228 is
229 X_ITEM_CLASS_ID NUMBER;
230 X_APPLICATION_ID NUMBER;
231 X_CREATED_BY NUMBER;
232 X_CURRENT_LAST_UPDATE_DATE VARCHAR2(19);
233 X_CURRENT_OWNER NUMBER;
234 X_LAST_UPDATED_BY NUMBER;
235 X_LAST_UPDATE_LOGIN NUMBER;
236 X_USAGES_ROWID ROWID;
237 X_CURRENT_OVN NUMBER;
238 begin
239 -- retrieve information for the current row
240 KEY_TO_IDS (
241 X_ITEM_CLASS_NAME,
242 X_APPLICATION_NAME,
243 X_USAGES_ROWID,
244 X_ITEM_CLASS_ID,
245 X_APPLICATION_ID,
246 X_CURRENT_OWNER,
247 X_CURRENT_LAST_UPDATE_DATE,
248 X_CURRENT_OVN);
249 -- obtain who column details
250 OWNER_TO_WHO (
251 X_OWNER,
252 X_CREATED_BY,
253 X_LAST_UPDATED_BY,
254 X_LAST_UPDATE_LOGIN
255 );
256 begin
257 -- the current row was not found insert a new row
258 -- and there is a valid application and valid attribute detected
259 if (X_ITEM_CLASS_ID is not null) and
260 (X_APPLICATION_ID is not null) then
261 if (X_USAGES_ROWID is null) then
262 INSERT_ROW (
263 X_ITEM_CLASS_ID,
264 X_APPLICATION_ID,
265 X_ITEM_ID_QUERY,
266 X_ITEM_CLASS_ORDER_NUMBER,
267 X_ITEM_CLASS_PAR_MODE,
268 X_ITEM_CLASS_SUBLIST_MODE,
269 X_CREATED_BY,
270 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
271 X_LAST_UPDATED_BY,
272 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
273 X_LAST_UPDATE_LOGIN,
274 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
275 1);
276 -- the current row was found end date the current row
277 -- insert a row with the same attribute id
278 else
279 if X_CUSTOM_MODE = 'FORCE' then
280 FORCE_UPDATE_ROW
281 (
282 X_USAGES_ROWID,
283 X_ITEM_ID_QUERY,
284 X_ITEM_CLASS_ORDER_NUMBER,
285 X_ITEM_CLASS_PAR_MODE,
286 X_ITEM_CLASS_SUBLIST_MODE,
287 X_CREATED_BY,
288 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
289 X_LAST_UPDATED_BY,
290 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
291 X_LAST_UPDATE_LOGIN,
292 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
293 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
294 X_CURRENT_OVN + 1);
295 else
296 if DO_UPDATE_INSERT
297 (AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
298 X_CURRENT_OWNER,
299 X_LAST_UPDATE_DATE,
300 X_CURRENT_LAST_UPDATE_DATE) then
301 UPDATE_ROW (
302 X_USAGES_ROWID,
303 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')-(1/86400));
304 INSERT_ROW (
305 X_ITEM_CLASS_ID,
306 X_APPLICATION_ID,
307 X_ITEM_ID_QUERY,
308 X_ITEM_CLASS_ORDER_NUMBER,
309 X_ITEM_CLASS_PAR_MODE,
310 X_ITEM_CLASS_SUBLIST_MODE,
311 X_CREATED_BY,
312 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
313 X_LAST_UPDATED_BY,
314 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
315 X_LAST_UPDATE_LOGIN,
316 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
317 X_CURRENT_OVN + 1);
318 end if;
319 end if;
320 end if;
321 end if;
322 end;
323 exception
324 when others then
325 ame_util.runtimeException('ame_item_class_usages_api',
326 'load_row',
327 sqlcode,
328 sqlerrm);
329 raise;
330
331 end LOAD_ROW;
332
333 END AME_ITEM_CLASS_USAGES_API;