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