[Home] [Help]
PACKAGE BODY: APPS.AME_MAN_ATTRIBUTES_API
Source
1 PACKAGE BODY AME_MAN_ATTRIBUTES_API AS
2 /* $Header: amemaapi.pkb 120.2 2005/10/14 04:13:11 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
16 procedure KEY_TO_IDS (
17 X_ATTRIBUTE_NAME in VARCHAR2,
18 X_ACTION_TYPE_NAME in VARCHAR2,
19 X_MAN_ATTRIBUTE_ROWID out nocopy VARCHAR2,
20 X_ATTRIBUTE_ID out nocopy NUMBER,
21 X_ACTION_TYPE_ID out nocopy NUMBER,
22 X_CURRENT_OWNER out nocopy NUMBER,
23 X_CURRENT_LAST_UPDATE_DATE out nocopy VARCHAR2,
24 X_CURRENT_OVN out nocopy NUMBER
25 ) is
26 cursor CSR_GET_ATTRIBUTE_ID
27 (
28 X_ATTRIBUTE_NAME in VARCHAR2
29 ) is
30 select ATTRIBUTE_ID
31 from AME_ATTRIBUTES
32 where NAME = X_ATTRIBUTE_NAME
33 and sysdate between START_DATE
34 and nvl(END_DATE - (1/86400), sysdate);
35
36 cursor CSR_GET_ACTION_TYPE_ID
37 (
38 X_ACTION_TYPE_NAME in VARCHAR2
39 ) is
40 select nvl(ACTION_TYPE_ID, null)
41 from AME_ACTION_TYPES
42 where NAME = X_ACTION_TYPE_NAME
43 and sysdate between START_DATE
44 and nvl(END_DATE - (1/86400), sysdate);
45
46 cursor CSR_GET_CURRENT_MAN_ATTRIBUTE
47 (
48 X_ATTRIBUTE_ID in NUMBER,
49 X_ACTION_TYPE_ID in NUMBER
50 )
51 is select ROWID,
52 LAST_UPDATED_BY,
53 to_char(LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
54 nvl(OBJECT_VERSION_NUMBER,1)
55 from AME_MANDATORY_ATTRIBUTES
56 where ATTRIBUTE_ID = X_ATTRIBUTE_ID
57 and ACTION_TYPE_ID = X_ACTION_TYPE_ID
58 and sysdate between START_DATE
59 and nvl(END_DATE - (1/86400), sysdate);
60 begin
61 open CSR_GET_ATTRIBUTE_ID (
62 X_ATTRIBUTE_NAME
63 );
64 fetch CSR_GET_ATTRIBUTE_ID into X_ATTRIBUTE_ID;
65 if (CSR_GET_ATTRIBUTE_ID%notfound) then
66 X_ATTRIBUTE_ID := null;
67 end if;
68 close CSR_GET_ATTRIBUTE_ID;
69
70 if X_ACTION_TYPE_NAME = 'MANDATORY_ATTRIBUTE' THEN
71 X_ACTION_TYPE_ID := -1;
72 else
73 open CSR_GET_ACTION_TYPE_ID (
74 X_ACTION_TYPE_NAME
75 );
76 fetch CSR_GET_ACTION_TYPE_ID into X_ACTION_TYPE_ID;
77 close CSR_GET_ACTION_TYPE_ID;
78 end if;
79
80 if X_ATTRIBUTE_ID is not null and X_ACTION_TYPE_ID is not null then
81 open CSR_GET_CURRENT_MAN_ATTRIBUTE
82 (
83 X_ATTRIBUTE_ID,
84 X_ACTION_TYPE_ID
85 );
86 fetch CSR_GET_CURRENT_MAN_ATTRIBUTE into X_MAN_ATTRIBUTE_ROWID,
87 X_CURRENT_OWNER,
88 X_CURRENT_LAST_UPDATE_DATE,
89 X_CURRENT_OVN;
90 if (CSR_GET_CURRENT_MAN_ATTRIBUTE%notfound) then
91 X_MAN_ATTRIBUTE_ROWID := null;
92 end if;
93 close CSR_GET_CURRENT_MAN_ATTRIBUTE;
94 end if;
95
96 end KEY_TO_IDS;
97 function DO_UPDATE_INSERT(X_OWNER in NUMBER,
98 X_CURRENT_OWNER in NUMBER,
99 X_LAST_UPDATE_DATE in VARCHAR2,
100 X_CURRENT_LAST_UPDATE_DATE in VARCHAR2,
101 X_CUSTOM_MODE in varchar2 default null)
102 return boolean as
103 begin
104 return AME_SEED_UTILITY.MERGE_ROW_TEST
105 (X_OWNER => X_OWNER
106 ,X_CURRENT_OWNER => X_CURRENT_OWNER
107 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
108 ,X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE
109 ,X_CUSTOM_MODE => X_CUSTOM_MODE
110 );
111 end DO_UPDATE_INSERT;
112
113 procedure FORCE_UPDATE_ROW (
114 X_ROWID 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_END_DATE in DATE,
122 X_OBJECT_VERSION_NUMBER in NUMBER
123 ) is
124 begin
125 update AME_MANDATORY_ATTRIBUTES
126 set CREATED_BY = X_CREATED_BY,
127 CREATION_DATE = X_CREATION_DATE,
128 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
129 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
130 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
131 START_DATE = X_START_DATE,
132 END_DATE = X_END_DATE,
133 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
134 where ROWID = X_ROWID;
135 end FORCE_UPDATE_ROW;
136
137 procedure INSERT_ROW (
138 X_ATTRIBUTE_ID in NUMBER,
139 X_ACTION_TYPE_ID in NUMBER,
140 X_CREATED_BY in NUMBER,
141 X_CREATION_DATE in DATE,
142 X_LAST_UPDATED_BY in NUMBER,
143 X_LAST_UPDATE_DATE in DATE,
144 X_LAST_UPDATE_LOGIN in NUMBER,
145 X_START_DATE in DATE,
146 X_OBJECT_VERSION_NUMBER in NUMBER
147 )
148 is
149 lockHandle varchar2(500);
150 returnValue integer;
151 begin
152
153 DBMS_LOCK.ALLOCATE_UNIQUE (lockname =>'AME_MANDATORY_ATTRIBUTES.'||to_char(X_ATTRIBUTE_ID)
154 ||'.'||to_char(X_ACTION_TYPE_ID),lockhandle => lockHandle);
155 returnValue := DBMS_LOCK.REQUEST(lockhandle => lockHandle,timeout => 0,release_on_commit => true);
156 if returnValue = 0 then
157 insert into AME_MANDATORY_ATTRIBUTES
158 (
159 ATTRIBUTE_ID,
160 ACTION_TYPE_ID,
161 CREATED_BY,
162 CREATION_DATE,
163 LAST_UPDATED_BY,
164 LAST_UPDATE_DATE,
165 LAST_UPDATE_LOGIN,
166 START_DATE,
167 END_DATE,
168 OBJECT_VERSION_NUMBER
169 ) values (
170 X_ATTRIBUTE_ID,
171 X_ACTION_TYPE_ID,
172 X_CREATED_BY,
173 X_CREATION_DATE,
174 X_LAST_UPDATED_BY,
175 X_LAST_UPDATE_DATE,
176 X_LAST_UPDATE_LOGIN,
177 X_START_DATE,
178 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
179 X_OBJECT_VERSION_NUMBER);
180 end if;
181 end INSERT_ROW;
182
183 procedure UPDATE_ROW (
184 X_MAN_ATTRIBUTE_ROWID in VARCHAR2,
185 X_END_DATE in DATE)
186 is
187 begin
188 update AME_MANDATORY_ATTRIBUTES set
189 END_DATE = X_END_DATE
190 where ROWID = X_MAN_ATTRIBUTE_ROWID;
191 end UPDATE_ROW;
192
193 procedure DELETE_ROW (
194 X_ATTRIBUTE_ID in NUMBER,
195 X_ACTION_TYPE_ID in NUMBER
196 ) is
197 begin
198 delete from AME_MANDATORY_ATTRIBUTES
199 where ATTRIBUTE_ID = X_ATTRIBUTE_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
207 procedure LOAD_ROW (
208 X_ATTRIBUTE_NAME in VARCHAR2,
209 X_ACTION_TYPE_NAME in VARCHAR2,
210 X_OWNER in VARCHAR2,
211 X_LAST_UPDATE_DATE in VARCHAR2,
212 X_CUSTOM_MODE in VARCHAR2
213 )
214 is
215 X_MAN_ATTRIBUTE_ROWID ROWID;
216 X_ATTRIBUTE_ID NUMBER;
217 X_ACTION_TYPE_ID NUMBER;
218 X_CREATED_BY NUMBER;
219 X_CURRENT_LAST_UPDATE_DATE VARCHAR2(19);
220 X_CURRENT_OWNER NUMBER;
221 X_LAST_UPDATED_BY NUMBER;
222 X_LAST_UPDATE_LOGIN NUMBER;
223 X_LINE_ATTRIBUTE_NAME ame_attributes.name%type := null;
224 X_CURRENT_OVN NUMBER;
225 begin
226 --detect if ame full patch has been applied
227 X_AME_INSTALLATION_LEVEL:=fnd_profile.value('AME_INSTALLATION_LEVEL');
228 if X_AME_INSTALLATION_LEVEL is not null then
229 if (X_ATTRIBUTE_NAME in (
230 ame_util.transactionDateAttribute,
231 ame_util.transactionGroupAttribute,
232 ame_util.transactionOrgAttribute,
233 ame_util.transactionRequestorAttribute,
234 ame_util.transactionReqUserAttribute,
235 ame_util.transactionSetOfBooksAttribute
236 ) and
237 X_ACTION_TYPE_NAME = 'MANDATORY_ATTRIBUTE') then
238 return;
239 end if;
240 --
241 -- checking for EVALUATE_PRIORITIES_PER_LINE_ITEM
242 -- and USE_RESTRICTIVE_LINE_ITEM_EVALUATION attributes
243 -- being uploaded
244 --
245 if X_ATTRIBUTE_NAME = ame_util.evalPrioritiesPerLIAttribute
246 then X_LINE_ATTRIBUTE_NAME := ame_util.evalPrioritiesPerItemAttribute;
247 end if;
248 if X_ATTRIBUTE_NAME = ame_util.restrictiveLIEvalAttribute
249 then X_LINE_ATTRIBUTE_NAME := ame_util.restrictiveItemEvalAttribute;
250 end if;
251 end if;
252
253 if X_AME_INSTALLATION_LEVEL is null then
254 if X_ATTRIBUTE_NAME in (ame_util.useWorkflowAttribute
255 ,ame_util.rejectionResponseAttribute
256 ,'REPEAT_SUBSTITUTIONS'
257 ,ame_util.nonDefStartingPointPosAttr
258 ,ame_util.nonDefPosStructureAttr
259 ,ame_util.transactionReqPositionAttr
260 ,ame_util.topPositionIdAttribute)then
261 return;
262 end if;
263 if (X_ATTRIBUTE_NAME in (
264 ame_util.transactionDateAttribute,
265 ame_util.transactionGroupAttribute,
266 ame_util.transactionOrgAttribute,
267 ame_util.transactionRequestorAttribute,
268 ame_util.transactionReqUserAttribute,
269 ame_util.transactionSetOfBooksAttribute
270 ) and
271 X_ACTION_TYPE_NAME <> 'MANDATORY_ATTRIBUTE') then
272 return;
273 end if;
274 -- checking for EVALUATE_PRIORITIES_PER_ITEM
275 -- and USE_RESTRICTIVE_ITEM_EVALUATION attributes
276 -- being uploaded
277 --
278 if X_ATTRIBUTE_NAME = ame_util.evalPrioritiesPerItemAttribute
279 then X_LINE_ATTRIBUTE_NAME := ame_util.evalPrioritiesPerLIAttribute;
280 end if;
281 if X_ATTRIBUTE_NAME = ame_util.restrictiveItemEvalAttribute
282 then X_LINE_ATTRIBUTE_NAME := ame_util.restrictiveLIEvalAttribute;
283 end if;
284 end if;
285 -- retrieve information for the current row
286 KEY_TO_IDS (
287 nvl(X_LINE_ATTRIBUTE_NAME,X_ATTRIBUTE_NAME),
288 X_ACTION_TYPE_NAME,
289 X_MAN_ATTRIBUTE_ROWID,
290 X_ATTRIBUTE_ID,
291 X_ACTION_TYPE_ID,
292 X_CURRENT_OWNER,
293 X_CURRENT_LAST_UPDATE_DATE,
294 X_CURRENT_OVN
295 );
296 -- obtain who column details
297 OWNER_TO_WHO (
298 X_OWNER,
299 X_CREATED_BY,
300 X_LAST_UPDATED_BY,
301 X_LAST_UPDATE_LOGIN
302 );
303 -- the current row was not found insert a new row
304 if (X_ATTRIBUTE_ID is not null) and
305 (X_ACTION_TYPE_ID is not null) then
306 if X_MAN_ATTRIBUTE_ROWID is null then
307 INSERT_ROW (
308 X_ATTRIBUTE_ID,
309 X_ACTION_TYPE_ID,
310 X_CREATED_BY,
311 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
312 X_LAST_UPDATED_BY,
313 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
314 X_LAST_UPDATE_LOGIN,
315 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
316 1);
317 -- the current row was found end date the current row
318 -- insert a row with the same action type id
319 else
320 if X_CUSTOM_MODE = 'FORCE' then
321 FORCE_UPDATE_ROW (
322 X_MAN_ATTRIBUTE_ROWID,
323 X_CREATED_BY,
324 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
325 X_LAST_UPDATED_BY,
326 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
327 X_LAST_UPDATE_LOGIN,
328 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
329 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
330 X_CURRENT_OVN + 1);
331 else
332 if DO_UPDATE_INSERT
333 (AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
334 X_CURRENT_OWNER,
335 X_LAST_UPDATE_DATE,
336 X_CURRENT_LAST_UPDATE_DATE) then
337 UPDATE_ROW (
338 X_MAN_ATTRIBUTE_ROWID,
339 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')-(1/86400));
340 INSERT_ROW (
341 X_ATTRIBUTE_ID,
342 X_ACTION_TYPE_ID,
343 X_CREATED_BY,
344 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
345 X_LAST_UPDATED_BY,
346 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
347 X_LAST_UPDATE_LOGIN,
348 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
349 X_CURRENT_OVN + 1);
350 end if;
351 end if;
352 end if;
353 else
354 null;
355 end if;
356
357 exception
358 when others then
359 ame_util.runtimeException('ame_mandatory_attributes_api',
360 'load_row',
361 sqlcode,
362 sqlerrm);
363 raise;
364 end LOAD_ROW;
365
366 procedure LOAD_SEED_ROW (
367 X_ATTRIBUTE_NAME in VARCHAR2,
368 X_ACTION_TYPE_NAME in VARCHAR2,
369 X_OWNER in VARCHAR2,
370 X_LAST_UPDATE_DATE in VARCHAR2,
371 X_UPLOAD_MODE in VARCHAR2,
372 X_CUSTOM_MODE in VARCHAR2) as
373 begin
374 if X_UPLOAD_MODE = 'NLS' then
375 null;
376 else
377 LOAD_ROW
378 (X_ATTRIBUTE_NAME => X_ATTRIBUTE_NAME
379 ,X_ACTION_TYPE_NAME => X_ACTION_TYPE_NAME
380 ,X_OWNER => X_OWNER
381 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
382 ,X_CUSTOM_MODE => X_CUSTOM_MODE
383 );
384 end if;
385 end LOAD_SEED_ROW;
386 END AME_MAN_ATTRIBUTES_API;