[Home] [Help]
PACKAGE BODY: APPS.AME_APPROVAL_GROUP_CONFIG_API
Source
1 PACKAGE BODY AME_APPROVAL_GROUP_CONFIG_API AS
2 /* $Header: amegcapi.pkb 120.1 2005/10/14 04:12 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_APPROVAL_GROUP_NAME in VARCHAR2,
18 X_CONFIG_ROWID out nocopy VARCHAR2,
19 X_APPLICATION_ID out nocopy NUMBER,
20 X_APPROVAL_GROUP_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_APPROVAL_GROUP
35 (
36 X_APPROVAL_GROUP_NAME in varchar2
37 ) is
38 select APPROVAL_GROUP_ID
39 from AME_APPROVAL_GROUPS
40 where NAME = X_APPROVAL_GROUP_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_APPROVAL_GROUP_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_APPROVAL_GROUP_CONFIG
53 where APPROVAL_GROUP_ID = X_APPROVAL_GROUP_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_APPROVAL_GROUP(
70 X_APPROVAL_GROUP_NAME
71 );
72 fetch CSR_GET_APPROVAL_GROUP into X_APPROVAL_GROUP_ID;
73 if (CSR_GET_APPROVAL_GROUP%notfound) then
74 X_APPROVAL_GROUP_ID := null;
75 end if;
76 close CSR_GET_APPROVAL_GROUP;
77
78 if (X_APPROVAL_GROUP_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_APPROVAL_GROUP_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_APPROVAL_GROUP_ID in NUMBER,
112 X_VOTING_REGIME in VARCHAR2,
113 X_ORDER_NUMBER in NUMBER,
114 X_CREATED_BY in NUMBER,
115 X_CREATION_DATE in DATE,
116 X_LAST_UPDATED_BY in NUMBER,
117 X_LAST_UPDATE_DATE in DATE,
118 X_LAST_UPDATE_LOGIN in NUMBER,
119 X_START_DATE in DATE,
120 X_OBJECT_VERSION_NUMBER in NUMBER)
121 is
122 begin
123 insert into AME_APPROVAL_GROUP_CONFIG
124 (
125 APPLICATION_ID,
126 APPROVAL_GROUP_ID,
127 VOTING_REGIME,
128 ORDER_NUMBER,
129 CREATED_BY,
130 CREATION_DATE,
131 LAST_UPDATED_BY,
132 LAST_UPDATE_DATE,
133 LAST_UPDATE_LOGIN,
134 START_DATE,
135 END_DATE,
136 OBJECT_VERSION_NUMBER
137 ) values (
138 X_APPLICATION_ID,
139 X_APPROVAL_GROUP_ID,
140 X_VOTING_REGIME,
141 X_ORDER_NUMBER,
142 X_CREATED_BY,
143 X_CREATION_DATE,
144 X_LAST_UPDATED_BY,
145 X_LAST_UPDATE_DATE,
146 X_LAST_UPDATE_LOGIN,
147 X_START_DATE,
148 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
149 X_OBJECT_VERSION_NUMBER
150 );
151 end INSERT_ROW;
152 procedure FORCE_UPDATE_ROW (
153 X_ROWID in VARCHAR2,
154 X_VOTING_REGIME in VARCHAR2,
155 X_ORDER_NUMBER in NUMBER,
156 X_CREATED_BY in NUMBER,
157 X_CREATION_DATE in DATE,
158 X_LAST_UPDATED_BY in NUMBER,
159 X_LAST_UPDATE_DATE in DATE,
160 X_LAST_UPDATE_LOGIN in NUMBER,
161 X_START_DATE in DATE,
162 X_END_DATE in DATE,
163 X_OBJECT_VERSION_NUMBER in NUMBER
164 ) is
165 begin
166 update AME_APPROVAL_GROUP_CONFIG
167 set VOTING_REGIME = X_VOTING_REGIME,
168 ORDER_NUMBER = X_ORDER_NUMBER,
169 CREATED_BY = X_CREATED_BY,
170 CREATION_DATE = X_CREATION_DATE,
171 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
172 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
173 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
174 START_DATE = X_START_DATE,
175 END_DATE = X_END_DATE,
176 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
177 where ROWID = X_ROWID;
178 end FORCE_UPDATE_ROW;
179 procedure UPDATE_ROW (
180 X_CONFIG_ROWID in VARCHAR2,
181 X_END_DATE in DATE)
182 is
183 begin
184 update AME_APPROVAL_GROUP_CONFIG set
185 END_DATE = X_END_DATE
186 where ROWID = X_CONFIG_ROWID;
187 end UPDATE_ROW;
188 procedure DELETE_ROW (
189 X_APPLICATION_ID in NUMBER,
190 X_APPROVAL_GROUP_ID in NUMBER
191 ) is
192 begin
193 delete from AME_APPROVAL_GROUP_CONFIG
194 where APPLICATION_ID = X_APPLICATION_ID
195 and APPROVAL_GROUP_ID = X_APPROVAL_GROUP_ID;
196
197 if (sql%notfound) then
198 raise no_data_found;
199 end if;
200 end DELETE_ROW;
201 procedure LOAD_ROW (
202 X_APPLICATION_NAME in VARCHAR2,
203 X_APPROVAL_GROUP_NAME in VARCHAR2,
204 X_VOTING_REGIME in VARCHAR2,
205 X_ORDER_NUMBER in VARCHAR2,
206 X_OWNER in VARCHAR2,
207 X_LAST_UPDATE_DATE in VARCHAR2,
208 X_CUSTOM_MODE in VARCHAR2
209 )
210 is
211 X_APPROVAL_GROUP_ID NUMBER;
212 X_APPLICATION_ID NUMBER;
213 X_CONFIG_ROWID ROWID;
214 X_CREATED_BY NUMBER;
215 X_CURRENT_LAST_UPDATE_DATE VARCHAR2(19);
216 X_CURRENT_OWNER NUMBER;
217 X_LAST_UPDATED_BY NUMBER;
218 X_LAST_UPDATE_LOGIN NUMBER;
219 X_CURRENT_OVN NUMBER;
220 begin
221 KEY_TO_IDS (
222 X_APPLICATION_NAME,
223 X_APPROVAL_GROUP_NAME,
224 X_CONFIG_ROWID,
225 X_APPLICATION_ID,
226 X_APPROVAL_GROUP_ID,
227 X_CURRENT_OWNER,
228 X_CURRENT_LAST_UPDATE_DATE,
229 X_CURRENT_OVN
230 );
231 OWNER_TO_WHO (
232 X_OWNER,
233 X_CREATED_BY,
234 X_LAST_UPDATED_BY,
235 X_LAST_UPDATE_LOGIN
236 );
237 begin
238 -- the current row was not found insert a new row
239 if (X_APPLICATION_ID is not null)
240 and (X_APPROVAL_GROUP_ID is not null) then
241 if (X_CONFIG_ROWID is null) then
242 INSERT_ROW (
243 X_APPLICATION_ID,
244 X_APPROVAL_GROUP_ID,
245 X_VOTING_REGIME,
246 X_ORDER_NUMBER,
247 X_CREATED_BY,
248 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
249 X_LAST_UPDATED_BY,
250 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
251 X_LAST_UPDATE_LOGIN,
252 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
253 1
254 );
255 -- the current row was found end date the current row
256 -- insert a row with the same attribute id
257 else
258 if X_CUSTOM_MODE = 'FORCE' then
259 FORCE_UPDATE_ROW (
260 X_CONFIG_ROWID,
261 X_VOTING_REGIME,
262 X_ORDER_NUMBER,
263 X_CREATED_BY,
264 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
265 X_LAST_UPDATED_BY,
266 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
267 X_LAST_UPDATE_LOGIN,
268 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
269 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
270 X_CURRENT_OVN + 1
271 );
272 else
273 if DO_UPDATE_INSERT
274 (AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
275 X_CURRENT_OWNER,
276 X_LAST_UPDATE_DATE,
277 X_CURRENT_LAST_UPDATE_DATE) then
278 UPDATE_ROW (
279 X_CONFIG_ROWID,
280 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')-(1/86400));
281 INSERT_ROW (
282 X_APPLICATION_ID,
283 X_APPROVAL_GROUP_ID,
284 X_VOTING_REGIME,
285 X_ORDER_NUMBER,
286 X_CREATED_BY,
287 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
288 X_LAST_UPDATED_BY,
289 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
290 X_LAST_UPDATE_LOGIN,
291 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
292 X_CURRENT_OVN + 1
293 );
294 end if;
295 end if;
296 end if;
297 end if;
298 end;
299 exception
300 when others then
301 ame_util.runtimeException('ame_approval_group_config_api',
302 'load_row',
303 sqlcode,
304 sqlerrm);
305 raise;
306 end LOAD_ROW;
307 --
308 END AME_APPROVAL_GROUP_CONFIG_API;