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