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