[Home] [Help]
PACKAGE BODY: APPS.AME_APPROVER_TYPE_USAGES_API
Source
1 PACKAGE BODY AME_APPROVER_TYPE_USAGES_API AS
2 /* $Header: ametuapi.pkb 120.2 2005/10/14 04:14:25 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_ACTION_TYPE_NAME in VARCHAR2,
18 X_APPROVER_TYPE_USAGE_ROWID out nocopy VARCHAR2,
19 X_APPROVER_TYPE_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_APPROVER_TYPE_ID
26 (
27 X_ORIG_SYSTEM in VARCHAR2
28 ) is
29 select APPROVER_TYPE_ID
30 from AME_APPROVER_TYPES
31 where ORIG_SYSTEM = X_ORIG_SYSTEM
32 and sysdate between START_DATE
33 and nvl(END_DATE - (1/86400), sysdate);
34 cursor CSR_GET_ACTION_TYPE_ID
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_CURR_APPR_TYPE_USAGE
44 (
45 X_APPROVER_TYPE_ID in NUMBER,
46 X_ACTION_TYPE_ID in NUMBER
47 ) is select ROWID,
48 LAST_UPDATED_BY,
49 to_char(LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
50 nvl(OBJECT_VERSION_NUMBER,1)
51 from AME_APPROVER_TYPE_USAGES
52 where APPROVER_TYPE_ID = X_APPROVER_TYPE_ID
53 and ACTION_TYPE_ID = X_ACTION_TYPE_ID
54 and sysdate between START_DATE
55 and nvl(END_DATE - (1/86400), sysdate);
56 begin
57 X_CURRENT_OVN := 1;
58 if X_ORIG_SYSTEM = 'ALL' then
59 X_APPROVER_TYPE_ID := -1;
60 else
61 open CSR_GET_APPROVER_TYPE_ID (X_ORIG_SYSTEM);
62 fetch CSR_GET_APPROVER_TYPE_ID into X_APPROVER_TYPE_ID;
63 if (CSR_GET_APPROVER_TYPE_ID%notfound) then
64 X_APPROVER_TYPE_ID := null;
65 end if;
66 close CSR_GET_APPROVER_TYPE_ID;
67 end if;
68 open CSR_GET_ACTION_TYPE_ID (
69 X_ACTION_TYPE_NAME
70 );
71 fetch CSR_GET_ACTION_TYPE_ID into X_ACTION_TYPE_ID;
72 if (CSR_GET_ACTION_TYPE_ID%notfound) then
73 X_ACTION_TYPE_ID := null;
74 end if;
75 close CSR_GET_ACTION_TYPE_ID;
76 if (X_APPROVER_TYPE_ID is not null)
77 and (X_ACTION_TYPE_ID is not null)
78 then
79 open CSR_GET_CURR_APPR_TYPE_USAGE (
80 X_APPROVER_TYPE_ID,
81 X_ACTION_TYPE_ID
82 );
83 fetch CSR_GET_CURR_APPR_TYPE_USAGE
84 into X_APPROVER_TYPE_USAGE_ROWID,
85 X_CURRENT_OWNER,
86 X_CURRENT_LAST_UPDATE_DATE,
87 X_CURRENT_OVN;
88 if (CSR_GET_CURR_APPR_TYPE_USAGE%notfound) then
89 X_APPROVER_TYPE_USAGE_ROWID := null;
90 end if;
91 close CSR_GET_CURR_APPR_TYPE_USAGE;
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
110 procedure INSERT_ROW (
111 X_APPROVER_TYPE_ID in NUMBER,
112 X_ACTION_TYPE_ID in NUMBER,
113 X_CREATED_BY in NUMBER,
114 X_CREATION_DATE in DATE,
115 X_LAST_UPDATED_BY in NUMBER,
116 X_LAST_UPDATE_DATE in DATE,
117 X_LAST_UPDATE_LOGIN in NUMBER,
118 X_START_DATE in DATE,
119 X_OBJECT_VERSION_NUMBER in NUMBER
120 )
121 is
122 begin
123 insert into AME_APPROVER_TYPE_USAGES
124 (
125 APPROVER_TYPE_ID,
126 ACTION_TYPE_ID,
127 CREATED_BY,
128 CREATION_DATE,
129 LAST_UPDATED_BY,
130 LAST_UPDATE_DATE,
131 LAST_UPDATE_LOGIN,
132 START_DATE,
133 END_DATE,
134 OBJECT_VERSION_NUMBER
135 ) values (
136 X_APPROVER_TYPE_ID,
137 X_ACTION_TYPE_ID,
138 X_CREATED_BY,
139 X_CREATION_DATE,
140 X_LAST_UPDATED_BY,
141 X_LAST_UPDATE_DATE,
142 X_LAST_UPDATE_LOGIN,
143 X_START_DATE,
144 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
145 X_OBJECT_VERSION_NUMBER);
146
147 end INSERT_ROW;
148
149 procedure UPDATE_ROW (
150 X_APPROVER_TYPE_USAGE_ROWID in VARCHAR2,
151 X_END_DATE in DATE)
152 is
153 begin
154 update AME_APPROVER_TYPE_USAGES set
155 END_DATE = X_END_DATE
156 where ROWID = X_APPROVER_TYPE_USAGE_ROWID;
157 end UPDATE_ROW;
158 procedure DELETE_ROW (
159 X_APPROVER_TYPE_ID in NUMBER,
160 X_ACTION_TYPE_ID in NUMBER
161 ) is
162 begin
163 delete from AME_APPROVER_TYPE_USAGES
164 where APPROVER_TYPE_ID = X_APPROVER_TYPE_ID
165 and ACTION_TYPE_ID = X_ACTION_TYPE_ID;
166
167 if (sql%notfound) then
168 raise no_data_found;
169 end if;
170 end DELETE_ROW;
171 procedure FORCE_UPDATE_ROW (
172 X_ROWID in VARCHAR2,
173 X_CREATED_BY in NUMBER,
174 X_CREATION_DATE in DATE,
175 X_LAST_UPDATED_BY in NUMBER,
176 X_LAST_UPDATE_DATE in DATE,
177 X_LAST_UPDATE_LOGIN in NUMBER,
178 X_START_DATE in DATE,
179 X_END_DATE in DATE,
180 X_OBJECT_VERSION_NUMBER in NUMBER
181 ) is
182 begin
183 update AME_APPROVER_TYPE_USAGES
184 set CREATED_BY = X_CREATED_BY,
185 CREATION_DATE = X_CREATION_DATE,
186 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
187 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
188 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
189 START_DATE = X_START_DATE,
190 END_DATE = X_END_DATE,
191 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
192 where ROWID = X_ROWID;
193 end FORCE_UPDATE_ROW;
194
195 procedure LOAD_ROW (
196 X_ORIG_SYSTEM in VARCHAR2,
197 X_ACTION_TYPE_NAME in VARCHAR2,
198 X_OWNER in VARCHAR2,
199 X_LAST_UPDATE_DATE in VARCHAR2,
200 X_CUSTOM_MODE in VARCHAR2
201 )
202 is
203 X_ACTION_TYPE_ID NUMBER;
204 X_APPROVER_TYPE_ID NUMBER;
205 X_APPROVER_TYPE_USAGE_ROWID ROWID;
206 X_CREATED_BY NUMBER;
207 X_CURRENT_LAST_UPDATE_DATE VARCHAR2(19);
208 X_CURRENT_OWNER NUMBER;
209 X_LAST_UPDATED_BY NUMBER;
210 X_LAST_UPDATE_LOGIN NUMBER;
211 X_CURRENT_OVN NUMBER;
212 begin
213 -- retrieve information for the current row
214 KEY_TO_IDS (
215 X_ORIG_SYSTEM,
216 X_ACTION_TYPE_NAME,
217 X_APPROVER_TYPE_USAGE_ROWID,
218 X_APPROVER_TYPE_ID,
219 X_ACTION_TYPE_ID,
220 X_CURRENT_OWNER,
221 X_CURRENT_LAST_UPDATE_DATE,
222 X_CURRENT_OVN
223 );
224 -- obtain who column details
225 OWNER_TO_WHO (
226 X_OWNER,
227 X_CREATED_BY,
228 X_LAST_UPDATED_BY,
229 X_LAST_UPDATE_LOGIN
230 );
231 begin
232 -- the current row was not found insert a new row
233 if (X_APPROVER_TYPE_ID is not null) and
234 (X_ACTION_TYPE_ID is not null) then
235 if X_APPROVER_TYPE_USAGE_ROWID is null then
236 INSERT_ROW (
237 X_APPROVER_TYPE_ID,
238 X_ACTION_TYPE_ID,
239 X_CREATED_BY,
240 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
241 X_LAST_UPDATED_BY,
242 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
243 X_LAST_UPDATE_LOGIN,
244 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
245 1);
246 else
247 if X_CUSTOM_MODE = 'FORCE' then
248 FORCE_UPDATE_ROW (
249 X_APPROVER_TYPE_USAGE_ROWID,
250 X_CREATED_BY,
251 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
252 X_LAST_UPDATED_BY,
253 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
254 X_LAST_UPDATE_LOGIN,
255 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
256 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
257 X_CURRENT_OVN + 1);
258 else
259 -- the current row was found end date the current row
260 -- insert a row with the same approver type id and action type id
261 if DO_UPDATE_INSERT
262 (AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
263 X_CURRENT_OWNER,
264 X_LAST_UPDATE_DATE,
265 X_CURRENT_LAST_UPDATE_DATE) then
266 UPDATE_ROW (
267 X_APPROVER_TYPE_USAGE_ROWID,
268 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')-(1/86400));
269 INSERT_ROW (
270 X_APPROVER_TYPE_ID,
271 X_ACTION_TYPE_ID,
272 X_CREATED_BY,
273 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
274 X_LAST_UPDATED_BY,
275 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
276 X_LAST_UPDATE_LOGIN,
277 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
278 X_CURRENT_OVN + 1);
279 end if;
280 end if;
281 end if;
282 else
283 -- nothing was found do not process
284 null;
285 end if;
286 end;
287 exception
288 when others then
289 ame_util.runtimeException('ame_approver_types_usages_api',
290 'load_row',
291 sqlcode,
292 sqlerrm);
293 raise;
294 end LOAD_ROW;
295
296 END AME_APPROVER_TYPE_USAGES_API;