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