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