[Home] [Help]
PACKAGE BODY: APPS.AME_ACTION_TYPE_USAGES_API
Source
1 PACKAGE BODY AME_ACTION_TYPE_USAGES_API AS
2 /* $Header: amecuapi.pkb 120.2 2005/10/14 04:12:33 ubhat noship $ */
3 X_AME_INSTALLATION_LEVEL varchar2(255);
4 procedure OWNER_TO_WHO (
5 X_OWNER in VARCHAR2,
6 X_CREATED_BY out nocopy NUMBER,
7 X_LAST_UPDATED_BY out nocopy NUMBER,
8 X_LAST_UPDATE_LOGIN out nocopy NUMBER
9 ) is
10 begin
11 X_CREATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
12 X_LAST_UPDATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
13 X_LAST_UPDATE_LOGIN := 0;
14 end OWNER_TO_WHO;
15 procedure KEY_TO_IDS (
16 X_ACTION_TYPE_NAME in VARCHAR2,
17 X_RULE_TYPE in VARCHAR2,
18 X_ACTION_USAGE_ROWID out nocopy VARCHAR2,
19 X_ACTION_TYPE_ID out nocopy NUMBER,
20 X_CURRENT_OWNER out nocopy NUMBER,
21 X_CURRENT_LAST_UPDATE_DATE out nocopy VARCHAR2,
22 X_CURRENT_OVN out nocopy NUMBER
23 ) is
24 cursor CSR_GET_ACTION_TYPE_ID
25 (
26 X_ACTION_TYPE_NAME in VARCHAR2
27 ) is
28 select ACTION_TYPE_ID
29 from AME_ACTION_TYPES
30 where NAME = X_ACTION_TYPE_NAME
31 and sysdate between START_DATE
32 and nvl(END_DATE - (1/86400), sysdate);
33 cursor CSR_GET_CURRENT_ACTION_USAGE
34 (
35 X_ACTION_TYPE_ID in NUMBER,
36 X_RULE_TYPE in VARCHAR2
37 ) is select ROWID,
38 LAST_UPDATED_BY,
39 to_char(LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
40 nvl(OBJECT_VERSION_NUMBER,1)
41 from AME_ACTION_TYPE_USAGES
42 where ACTION_TYPE_ID = X_ACTION_TYPE_ID
43 and RULE_TYPE = X_RULE_TYPE
44 and sysdate between START_DATE
45 and nvl(END_DATE - (1/86400), sysdate);
46 begin
47 X_CURRENT_OVN := 1;
48 open CSR_GET_ACTION_TYPE_ID (
49 X_ACTION_TYPE_NAME
50 );
51 fetch CSR_GET_ACTION_TYPE_ID into X_ACTION_TYPE_ID;
52 if (CSR_GET_ACTION_TYPE_ID%notfound) then
53 X_ACTION_TYPE_ID := null;
54 end if;
55 close CSR_GET_ACTION_TYPE_ID;
56
57 if X_ACTION_TYPE_ID is not null
58 then
59 open CSR_GET_CURRENT_ACTION_USAGE (
60 X_ACTION_TYPE_ID,
61 X_RULE_TYPE
62 );
63 fetch CSR_GET_CURRENT_ACTION_USAGE into X_ACTION_USAGE_ROWID,
64 X_CURRENT_OWNER,
65 X_CURRENT_LAST_UPDATE_DATE,
66 X_CURRENT_OVN;
67 if (CSR_GET_CURRENT_ACTION_USAGE%notfound) then
68 X_ACTION_USAGE_ROWID := null;
69 end if;
70 close CSR_GET_CURRENT_ACTION_USAGE;
71 end if;
72 end KEY_TO_IDS;
73 function DO_UPDATE_INSERT(X_OWNER in NUMBER,
74 X_CURRENT_OWNER in NUMBER,
75 X_LAST_UPDATE_DATE in VARCHAR2,
76 X_CURRENT_LAST_UPDATE_DATE in VARCHAR2,
77 X_CUSTOM_MODE in VARCHAR2)
78 return boolean as
79 begin
80 return AME_SEED_UTILITY.MERGE_ROW_TEST
81 (X_OWNER => X_OWNER
82 ,X_CURRENT_OWNER => X_CURRENT_OWNER
83 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
84 ,X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE
85 ,X_CUSTOM_MODE => X_CUSTOM_MODE
86 );
87 end DO_UPDATE_INSERT;
88 procedure VALIDATE_RULE_TYPE (
89 X_RULE_TYPE in NUMBER
90 ) is
91 invalidRuleTypeException exception;
92 errorCode integer;
93 errorMessage ame_util.longestStringType;
94 begin
95 --ame_util.productionRuleType is not a valid rule type for 1159 ame.
96 if (X_RULE_TYPE <> ame_util.authorityRuleType)
97 and (X_RULE_TYPE <> ame_util.exceptionRuleType)
98 and (X_RULE_TYPE <> ame_util.listModRuleType)
99 and (X_RULE_TYPE <> ame_util.substitutionRuleType)
100 and (X_RULE_TYPE <> ame_util.preListGroupRuleType)
101 and (X_RULE_TYPE <> ame_util.postListGroupRuleType)
102 and (
103 (X_RULE_TYPE <> ame_util.productionRuleType
104 and X_AME_INSTALLATION_LEVEL is not null)
105 or (X_AME_INSTALLATION_LEVEL is null)
106 ) then
107 raise invalidRuleTypeException;
108 end if;
109 exception
110 when invalidRuleTypeException then
111 errorCode := -20001;
112 errorMessage := 'OAM is attempting to upload an invalid rule type. ';
113 ame_util.runtimeException(packageNameIn => 'ame_action_type_usages_api',
114 routineNameIn => 'validate_rule_type',
115 exceptionNumberIn => errorCode,
116 exceptionStringIn => errorMessage);
117 raise_application_error(errorCode,
118 errorMessage);
119 when others then
120 ame_util.runtimeException('ame_action_type_usages_api',
121 'validate_rule_type',
122 sqlcode,
123 sqlerrm);
124 raise;
125 end VALIDATE_RULE_TYPE;
126 procedure INSERT_ROW (
127 X_ACTION_TYPE_ID in NUMBER,
128 X_RULE_TYPE in NUMBER,
129 X_CREATED_BY in NUMBER,
130 X_CREATION_DATE in DATE,
131 X_LAST_UPDATED_BY in NUMBER,
132 X_LAST_UPDATE_DATE in DATE,
133 X_LAST_UPDATE_LOGIN in NUMBER,
134 X_START_DATE in DATE,
135 X_OBJECT_VERSION_NUMBER in NUMBER
136 )
137 is
138 begin
139 insert into AME_ACTION_TYPE_USAGES
140 (
141 ACTION_TYPE_ID,
142 RULE_TYPE,
143 CREATED_BY,
144 CREATION_DATE,
145 LAST_UPDATED_BY,
146 LAST_UPDATE_DATE,
147 LAST_UPDATE_LOGIN,
148 START_DATE,
149 END_DATE,
150 OBJECT_VERSION_NUMBER
151 ) values (
152 X_ACTION_TYPE_ID,
153 X_RULE_TYPE,
154 X_CREATED_BY,
155 X_CREATION_DATE,
156 X_LAST_UPDATED_BY,
157 X_LAST_UPDATE_DATE,
158 X_LAST_UPDATE_LOGIN,
159 X_START_DATE,
160 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
161 X_OBJECT_VERSION_NUMBER);
162 end INSERT_ROW;
163
164 procedure UPDATE_ROW (
165 X_ACTION_USAGE_ROWID in VARCHAR2,
166 X_END_DATE in DATE)
167 is
168 begin
169 update AME_ACTION_TYPE_USAGES set
170 END_DATE = X_END_DATE
171 where ROWID = X_ACTION_USAGE_ROWID;
172 end UPDATE_ROW;
173
174 procedure DELETE_ROW (
175 X_ACTION_TYPE_ID in NUMBER,
176 X_RULE_TYPE in NUMBER
177 ) is
178 begin
179 delete from AME_ACTION_TYPE_USAGES
180 where ACTION_TYPE_ID = X_ACTION_TYPE_ID
181 and RULE_TYPE = X_RULE_TYPE;
182
183 if (sql%notfound) then
184 raise no_data_found;
185 end if;
186 end DELETE_ROW;
187
188 procedure LOAD_ROW (
189 X_ACTION_TYPE_NAME in VARCHAR2,
190 X_RULE_TYPE in VARCHAR2,
191 X_OWNER in VARCHAR2,
192 X_LAST_UPDATE_DATE in VARCHAR2,
193 X_CUSTOM_MODE in VARCHAR2
194 )
195 is
196 X_ACTION_USAGE_ROWID ROWID;
197 X_ACTION_TYPE_ID NUMBER;
198 X_CREATED_BY NUMBER;
199 X_CURRENT_LAST_UPDATE_DATE VARCHAR2(19);
200 X_CURRENT_OWNER NUMBER;
201 X_LAST_UPDATED_BY NUMBER;
202 X_LAST_UPDATE_LOGIN NUMBER;
203 X_CURRENT_OVN NUMBER;
204 begin
205 --find current ame installation level
206 X_AME_INSTALLATION_LEVEL:= fnd_profile.value('AME_INSTALLATION_LEVEL');
207 -- retrieve information for the current row
208 KEY_TO_IDS (
209 X_ACTION_TYPE_NAME,
210 X_RULE_TYPE,
211 X_ACTION_USAGE_ROWID,
212 X_ACTION_TYPE_ID,
213 X_CURRENT_OWNER,
214 X_CURRENT_LAST_UPDATE_DATE,
215 X_CURRENT_OVN
216 );
217 VALIDATE_RULE_TYPE (
218 X_RULE_TYPE
219 );
220 -- obtain who column details
221 OWNER_TO_WHO (
222 X_OWNER,
223 X_CREATED_BY,
224 X_LAST_UPDATED_BY,
225 X_LAST_UPDATE_LOGIN
226 );
227 begin
228 -- the current row was not found insert a new row
229 if X_ACTION_TYPE_ID is not null then
230 if X_ACTION_USAGE_ROWID is null then
231 INSERT_ROW (
232 X_ACTION_TYPE_ID,
233 X_RULE_TYPE,
234 X_CREATED_BY,
235 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
236 X_LAST_UPDATED_BY,
237 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
238 X_LAST_UPDATE_LOGIN,
239 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
240 1);
241 else
242 -- the current row was found end date the current row
243 -- insert a row with the same action type id
244 if DO_UPDATE_INSERT
245 (AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
246 X_CURRENT_OWNER,
247 X_LAST_UPDATE_DATE,
248 X_CURRENT_LAST_UPDATE_DATE,
249 X_CUSTOM_MODE) then
250 UPDATE_ROW (
251 X_ACTION_USAGE_ROWID,
252 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')-(1/86400));
253 INSERT_ROW (
254 X_ACTION_TYPE_ID,
255 X_RULE_TYPE,
256 X_CREATED_BY,
257 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
258 X_LAST_UPDATED_BY,
259 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
260 X_LAST_UPDATE_LOGIN,
261 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
262 X_CURRENT_OVN + 1);
263 end if;
264 end if;
265 else
266 -- nothing was found do not process
267 null;
268 end if;
269 end;
270 exception
271 when others then
272 ame_util.runtimeException('ame_action_types_usages_api',
273 'load_row',
274 sqlcode,
275 sqlerrm);
276 raise;
277 end LOAD_ROW;
278 END AME_ACTION_TYPE_USAGES_API;