[Home] [Help]
PACKAGE BODY: APPS.AMS_QUERY_ALIAS_PKG
Source
1 package body AMS_QUERY_ALIAS_PKG as
2 /* $Header: amstqalb.pls 120.0 2005/06/01 23:39:11 appldev noship $ */
3 procedure INSERT_ROW (
4 X_QUERY_ALIAS_ID in NUMBER,
5 X_OBJECT_VERSION_NUMBER in NUMBER,
6 X_REQUEST_ID in NUMBER,
7 X_OBJECT_NAME in VARCHAR2,
8 X_ALIAS_SEQ in NUMBER,
9 X_PARENT_QUERY_ALIAS_ID in NUMBER,
10 X_TEMPLATE_ID in NUMBER,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 ) is
17 begin
18 insert into AMS_QUERY_ALIAS (
19 CREATION_DATE,
20 CREATED_BY,
21 LAST_UPDATE_LOGIN,
22 OBJECT_VERSION_NUMBER,
23 REQUEST_ID,
24 OBJECT_NAME,
25 ALIAS_SEQ,
26 PARENT_QUERY_ALIAS_ID,
27 QUERY_ALIAS_ID,
28 TEMPLATE_ID,
29 LAST_UPDATE_DATE,
30 LAST_UPDATED_BY
31 ) values(
32 X_CREATION_DATE,
33 X_CREATED_BY,
34 X_LAST_UPDATE_LOGIN,
35 X_OBJECT_VERSION_NUMBER,
36 X_REQUEST_ID,
37 X_OBJECT_NAME,
38 X_ALIAS_SEQ,
39 X_PARENT_QUERY_ALIAS_ID,
40 X_QUERY_ALIAS_ID,
41 X_TEMPLATE_ID,
42 X_LAST_UPDATE_DATE,
43 X_LAST_UPDATED_BY);
44
45 end INSERT_ROW;
46
47 procedure LOCK_ROW (
48 X_QUERY_ALIAS_ID in NUMBER,
49 X_OBJECT_VERSION_NUMBER in NUMBER,
50 X_REQUEST_ID in NUMBER,
51 X_OBJECT_NAME in VARCHAR2,
52 X_ALIAS_SEQ in NUMBER,
53 X_PARENT_QUERY_ALIAS_ID in NUMBER,
54 X_TEMPLATE_ID in NUMBER
55 ) is
56 cursor c1 is select
57 QUERY_ALIAS_ID,
58 OBJECT_VERSION_NUMBER,
59 OBJECT_NAME,
60 ALIAS_SEQ,
61 PARENT_QUERY_ALIAS_ID,
62 TEMPLATE_ID
63 from AMS_QUERY_ALIAS
64 where QUERY_ALIAS_ID = X_QUERY_ALIAS_ID
65 for update of QUERY_ALIAS_ID nowait;
66 begin
67 for Recinfo in c1 loop
68 if (
69 ((Recinfo.QUERY_ALIAS_ID = X_QUERY_ALIAS_ID)
70 OR ((Recinfo.QUERY_ALIAS_ID is null) AND (X_QUERY_ALIAS_ID is null)))
71
72 AND ((Recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
73 OR ((Recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
74 AND ((Recinfo.OBJECT_NAME = X_OBJECT_NAME)
75 OR ((Recinfo.OBJECT_NAME is null) AND (X_OBJECT_NAME is null)))
76 AND (Recinfo.ALIAS_SEQ = X_ALIAS_SEQ)
77 AND ((Recinfo.PARENT_QUERY_ALIAS_ID = X_PARENT_QUERY_ALIAS_ID)
78 OR ((Recinfo.PARENT_QUERY_ALIAS_ID is null) AND (X_PARENT_QUERY_ALIAS_ID is null)))
79 AND (Recinfo.TEMPLATE_ID = X_TEMPLATE_ID)
80 ) then
81 null;
82 else
83 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
84 app_exception.raise_exception;
85 end if;
86 end loop;
87 return;
88 end LOCK_ROW;
89
90 procedure UPDATE_ROW (
91 X_QUERY_ALIAS_ID in NUMBER,
92 X_OBJECT_VERSION_NUMBER in NUMBER,
93 X_REQUEST_ID in NUMBER,
94 X_OBJECT_NAME in VARCHAR2,
95 X_ALIAS_SEQ in NUMBER,
96 X_PARENT_QUERY_ALIAS_ID in NUMBER,
97 X_TEMPLATE_ID in NUMBER,
98 X_LAST_UPDATE_DATE in DATE,
99 X_LAST_UPDATED_BY in NUMBER,
100 X_LAST_UPDATE_LOGIN in NUMBER
101 ) is
102 begin
103 update AMS_QUERY_ALIAS set
104 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
105 REQUEST_ID = X_REQUEST_ID,
106 OBJECT_NAME = X_OBJECT_NAME,
107 ALIAS_SEQ = X_ALIAS_SEQ,
108 PARENT_QUERY_ALIAS_ID = X_PARENT_QUERY_ALIAS_ID,
109 TEMPLATE_ID = X_TEMPLATE_ID,
110 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
111 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
112 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
113 where QUERY_ALIAS_ID = X_QUERY_ALIAS_ID
114 and object_version_number = x_object_version_number;
115
116 if (sql%notfound) then
117 raise no_data_found;
118 end if;
119 end UPDATE_ROW;
120
121 procedure DELETE_ROW (
122 X_QUERY_ALIAS_ID in NUMBER
123 ) is
124 begin
125 delete from AMS_QUERY_ALIAS
126 where QUERY_ALIAS_ID = X_QUERY_ALIAS_ID;
127
128 if (sql%notfound) then
129 raise no_data_found;
130 end if;
131
132 end DELETE_ROW;
133
134 PROCEDURE load_row (
135 X_QUERY_ALIAS_ID in NUMBER,
136 X_REQUEST_ID in NUMBER,
137 X_OBJECT_NAME in VARCHAR2,
138 X_ALIAS_SEQ in NUMBER,
139 X_PARENT_QUERY_ALIAS_ID in NUMBER,
140 X_TEMPLATE_ID in NUMBER,
141 x_owner IN VARCHAR2,
142 x_custom_mode IN VARCHAR2
143
144 ) is
145 l_user_id number := 0;
146 l_obj_verno number;
147 l_dummy_char varchar2(1);
148 l_row_id varchar2(100);
149 l_QUERY_ALIAS_ID number;
150 l_db_luby_id number;
151
152 CURSOR c_obj_verno IS
153 SELECT object_version_number, last_updated_by
154 FROM AMS_QUERY_ALIAS
155 WHERE QUERY_ALIAS_ID = x_QUERY_ALIAS_ID;
156
157 CURSOR c_chk_exists is
158 SELECT 'x'
159 FROM AMS_QUERY_ALIAS
160 WHERE QUERY_ALIAS_ID = x_QUERY_ALIAS_ID;
161
162 CURSOR c_get_id is
163 SELECT AMS_QUERY_ALIAS_s.NEXTVAL
164 FROM DUAL;
165 BEGIN
166 if X_OWNER = 'SEED' then
167 l_user_id := 1;
168 elsif X_OWNER = 'ORACLE' then
169 l_user_id := 2;
170 elsif X_OWNER = 'SYSADMIN' then
171 l_user_id := 0;
172
173 end if;
174
175 OPEN c_chk_exists;
176 FETCH c_chk_exists INTO l_dummy_char;
177 IF c_chk_exists%notfound THEN
178 CLOSE c_chk_exists;
179
180 IF x_QUERY_ALIAS_ID IS NULL THEN
181 OPEN c_get_id;
182 FETCH c_get_id INTO l_QUERY_ALIAS_ID;
183 CLOSE c_get_id;
184 ELSE
185 l_QUERY_ALIAS_ID := x_QUERY_ALIAS_ID;
186 END IF;
187 l_obj_verno := 1;
188
189 AMS_QUERY_ALIAS_PKG.insert_row (
190 x_QUERY_ALIAS_ID => l_QUERY_ALIAS_ID,
191 X_OBJECT_NAME => X_OBJECT_NAME,
192 x_last_update_date => SYSDATE,
193 x_last_updated_by => l_user_id,
194 x_creation_date => SYSDATE,
195 x_created_by => l_user_id,
196 x_last_update_login => 0,
197 x_object_version_number => l_obj_verno,
198 X_ALIAS_SEQ => X_ALIAS_SEQ,
199 X_PARENT_QUERY_ALIAS_ID => X_PARENT_QUERY_ALIAS_ID,
200 X_TEMPLATE_ID => X_TEMPLATE_ID,
201 X_REQUEST_ID => 0
202 );
203
204
205 ELSE
206 CLOSE c_chk_exists;
207 OPEN c_obj_verno;
208 FETCH c_obj_verno INTO l_obj_verno, l_db_luby_id;
209 CLOSE c_obj_verno;
210
211
212 if (l_db_luby_id IN (0, 1, 2) or NVL(x_custom_mode, 'PRESERVE')='FORCE')
213 then
214
215 AMS_QUERY_ALIAS_PKG.update_row (
216 x_QUERY_ALIAS_ID => x_QUERY_ALIAS_ID,
217 X_OBJECT_NAME => X_OBJECT_NAME,
218 x_last_update_date => SYSDATE,
219 x_last_updated_by => l_user_id,
220 x_last_update_login => 0,
221 x_object_version_number => l_obj_verno,
222 X_ALIAS_SEQ => X_ALIAS_SEQ,
223 X_PARENT_QUERY_ALIAS_ID => X_PARENT_QUERY_ALIAS_ID,
224 X_TEMPLATE_ID => X_TEMPLATE_ID,
225 X_REQUEST_ID => 0
226 );
227
228 end if;
229
230 END IF;
231 END load_row;
232
233
234
235 end AMS_QUERY_ALIAS_PKG;