[Home] [Help]
PACKAGE BODY: APPS.AMS_COND_STRUCT_RESTR_VAL_PKG
Source
1 package body AMS_COND_STRUCT_RESTR_VAL_PKG as
2 /* $Header: amstqcrb.pls 120.0 2005/05/31 22:42:36 appldev noship $ */
3 procedure INSERT_ROW (
4 X_COND_STRUCT_RESTRICT_ID in NUMBER,
5 X_QUERY_COND_DISP_STRUCT_ID in NUMBER,
6 X_CODE in VARCHAR2,
7 X_INCLUDED_FLAG in VARCHAR2,
8 X_CODE_IND_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_COND_STRUCT_RESTRICT_VALUE (
19 COND_STRUCT_RESTRICT_ID,
20 QUERY_COND_DISP_STRUCT_ID,
21 CODE,
22 INCLUDED_FLAG,
23 CODE_IND_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 (X_COND_STRUCT_RESTRICT_ID,
33 X_QUERY_COND_DISP_STRUCT_ID,
34 X_CODE,
35 X_INCLUDED_FLAG,
36 X_CODE_IND_FLAG,
37 X_LAST_UPDATE_DATE,
38 X_LAST_UPDATED_BY,
39 X_CREATION_DATE,
40 X_CREATED_BY,
41 X_LAST_UPDATE_LOGIN,
42 X_OBJECT_VERSION_NUMBER,
43 X_REQUEST_ID)
44 ;
45
46 end INSERT_ROW;
47
48 procedure LOCK_ROW (
49 X_COND_STRUCT_RESTRICT_ID in NUMBER,
50 X_QUERY_COND_DISP_STRUCT_ID in NUMBER,
51 X_CODE in VARCHAR2,
52 X_INCLUDED_FLAG in VARCHAR2,
53 X_CODE_IND_FLAG in VARCHAR2,
54 X_OBJECT_VERSION_NUMBER in NUMBER,
55 X_REQUEST_ID in NUMBER
56 ) is
57 cursor c1 is select
58 QUERY_COND_DISP_STRUCT_ID,
59 CODE,
60 INCLUDED_FLAG,
61 CODE_IND_FLAG,
62 OBJECT_VERSION_NUMBER
63 from AMS_COND_STRUCT_RESTRICT_VALUE
64 where COND_STRUCT_RESTRICT_ID = X_COND_STRUCT_RESTRICT_ID
65 for update of COND_STRUCT_RESTRICT_ID nowait;
66 begin
67
68 for Recinfo in c1 loop
69 IF (
70 ((Recinfo.QUERY_COND_DISP_STRUCT_ID = X_QUERY_COND_DISP_STRUCT_ID)
71 OR ((Recinfo.QUERY_COND_DISP_STRUCT_ID is null) AND (X_QUERY_COND_DISP_STRUCT_ID is null)))
72 AND ((Recinfo.CODE = X_CODE)
73 OR ((Recinfo.CODE is null) AND (X_CODE is null)))
74 AND ((Recinfo.INCLUDED_FLAG = X_INCLUDED_FLAG)
75 OR ((Recinfo.INCLUDED_FLAG is null) AND (X_INCLUDED_FLAG is null)))
76 AND ((Recinfo.CODE_IND_FLAG = X_CODE_IND_FLAG)
77 OR ((Recinfo.CODE_IND_FLAG is null) AND (X_CODE_IND_FLAG is null)))
78 AND ((Recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
79 OR ((Recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
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_COND_STRUCT_RESTRICT_ID in NUMBER,
92 X_QUERY_COND_DISP_STRUCT_ID in NUMBER,
93 X_CODE in VARCHAR2,
94 X_INCLUDED_FLAG in VARCHAR2,
95 X_CODE_IND_FLAG in VARCHAR2,
96 X_OBJECT_VERSION_NUMBER in NUMBER,
97 X_REQUEST_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_COND_STRUCT_RESTRICT_VALUE set
104 QUERY_COND_DISP_STRUCT_ID = X_QUERY_COND_DISP_STRUCT_ID,
105 CODE = X_CODE,
106 INCLUDED_FLAG = X_INCLUDED_FLAG,
107 CODE_IND_FLAG = X_CODE_IND_FLAG,
108 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
109 REQUEST_ID = X_REQUEST_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 COND_STRUCT_RESTRICT_ID = X_COND_STRUCT_RESTRICT_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_COND_STRUCT_RESTRICT_ID in NUMBER
123 ) is
124 begin
125 delete from AMS_COND_STRUCT_RESTRICT_VALUE
126 where COND_STRUCT_RESTRICT_ID = X_COND_STRUCT_RESTRICT_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_COND_STRUCT_RESTRICT_ID in NUMBER,
136 X_QUERY_COND_DISP_STRUCT_ID in NUMBER,
137 X_CODE in VARCHAR2,
138 X_INCLUDED_FLAG in VARCHAR2,
139 X_CODE_IND_FLAG in VARCHAR2,
140 x_owner IN VARCHAR2,
141 x_custom_mode IN VARCHAR2
142
143 ) is
144 l_user_id number := 0;
145 l_obj_verno number;
146 l_dummy_char varchar2(1);
147 l_row_id varchar2(100);
148 l_COND_STRUCT_RESTRICT_ID number;
149 l_db_luby_id number;
150
151 CURSOR c_obj_verno IS
152 SELECT object_version_number, last_updated_by
153 FROM AMS_COND_STRUCT_RESTRICT_VALUE
154 WHERE COND_STRUCT_RESTRICT_ID = x_COND_STRUCT_RESTRICT_ID;
155
156 CURSOR c_chk_exists is
157 SELECT 'x'
158 FROM AMS_COND_STRUCT_RESTRICT_VALUE
159 WHERE COND_STRUCT_RESTRICT_ID = x_COND_STRUCT_RESTRICT_ID;
160
161 CURSOR c_get_id is
162 SELECT AMS_COND_STRUCT_RESTRICT_VAL_s.NEXTVAL
163 FROM DUAL;
164 BEGIN
165 if X_OWNER = 'SEED' then
166 l_user_id := 1;
167 elsif X_OWNER = 'ORACLE' then
168 l_user_id := 2;
169 elsif X_OWNER = 'SYSADMIN' then
170 l_user_id := 0;
171 end if;
172
173 OPEN c_chk_exists;
174 FETCH c_chk_exists INTO l_dummy_char;
175 IF c_chk_exists%notfound THEN
176 CLOSE c_chk_exists;
177
178 IF x_COND_STRUCT_RESTRICT_ID IS NULL THEN
179 OPEN c_get_id;
180 FETCH c_get_id INTO l_COND_STRUCT_RESTRICT_ID;
181 CLOSE c_get_id;
182 ELSE
183 l_COND_STRUCT_RESTRICT_ID := x_COND_STRUCT_RESTRICT_ID;
184 END IF;
185 l_obj_verno := 1;
186
187 AMS_COND_STRUCT_RESTR_VAL_PKG.insert_row (
188 x_COND_STRUCT_RESTRICT_ID => l_COND_STRUCT_RESTRICT_ID,
189 x_last_update_date => SYSDATE,
190 x_last_updated_by => l_user_id,
191 x_creation_date => SYSDATE,
192 x_created_by => l_user_id,
193 x_last_update_login => 0,
194 x_object_version_number => l_obj_verno,
195 X_QUERY_COND_DISP_STRUCT_ID => X_QUERY_COND_DISP_STRUCT_ID,
196 X_CODE => X_CODE,
197 X_INCLUDED_FLAG => X_INCLUDED_FLAG,
198 X_CODE_IND_FLAG => X_CODE_IND_FLAG,
199 X_REQUEST_ID => 0
200 );
201
202
203 ELSE
204 CLOSE c_chk_exists;
205 OPEN c_obj_verno;
206 FETCH c_obj_verno INTO l_obj_verno, l_db_luby_id;
207 CLOSE c_obj_verno;
208
209 if (l_db_luby_id IN (0, 1, 2) or NVL(x_custom_mode, 'PRESERVE')='FORCE')
210 then
211
212 AMS_COND_STRUCT_RESTR_VAL_PKG.update_row (
213 x_COND_STRUCT_RESTRICT_ID => x_COND_STRUCT_RESTRICT_ID,
214 x_last_update_date => SYSDATE,
215 x_last_updated_by => l_user_id,
216 x_last_update_login => 0,
217 x_object_version_number => l_obj_verno,
218 X_QUERY_COND_DISP_STRUCT_ID => X_QUERY_COND_DISP_STRUCT_ID,
219 X_CODE => X_CODE,
220 X_INCLUDED_FLAG => X_INCLUDED_FLAG,
221 X_CODE_IND_FLAG => X_CODE_IND_FLAG,
222 X_REQUEST_ID => 0
223 );
224 end if;
225
226 END IF;
227 END load_row;
228
229
230 end AMS_COND_STRUCT_RESTR_VAL_PKG;