1 package body AK_CRITERIA_PKG as
2 /* $Header: AKCRTRAB.pls 120.2 2005/09/29 13:59:29 tshort noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_CUSTOMIZATION_APPLICATION_ID in NUMBER,
6 X_CUSTOMIZATION_CODE in VARCHAR2,
7 X_REGION_APPLICATION_ID in NUMBER,
8 X_REGION_CODE in VARCHAR2,
9 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
10 X_ATTRIBUTE_CODE in VARCHAR2,
11 X_SEQUENCE_NUMBER in NUMBER,
12 X_OPERATION in VARCHAR2,
13 X_VALUE_VARCHAR2 in VARCHAR2,
14 X_VALUE_NUMBER in NUMBER,
15 X_VALUE_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_CREATION_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_DATE in DATE,
20 X_LAST_UPDATE_LOGIN in NUMBER,
21 X_START_DATE_ACTIVE in DATE,
22 X_END_DATE_ACTIVE in DATE
23 ) is
24 cursor C is select ROWID from AK_CRITERIA
25 where CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
26 and CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
27 and REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
28 and REGION_CODE = X_REGION_CODE
29 and ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
30 and ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
31 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
32 begin
33 insert into AK_CRITERIA (
34 CUSTOMIZATION_APPLICATION_ID,
35 CUSTOMIZATION_CODE,
36 REGION_APPLICATION_ID,
37 REGION_CODE,
38 ATTRIBUTE_APPLICATION_ID,
39 ATTRIBUTE_CODE,
40 SEQUENCE_NUMBER,
41 OPERATION,
42 VALUE_VARCHAR2,
43 VALUE_NUMBER,
44 VALUE_DATE,
45 CREATED_BY,
46 CREATION_DATE,
47 LAST_UPDATED_BY,
48 LAST_UPDATE_DATE,
49 LAST_UPDATE_LOGIN,
50 START_DATE_ACTIVE,
51 END_DATE_ACTIVE
52 ) values (
53 X_CUSTOMIZATION_APPLICATION_ID,
54 X_CUSTOMIZATION_CODE,
55 X_REGION_APPLICATION_ID,
56 X_REGION_CODE,
57 X_ATTRIBUTE_APPLICATION_ID,
58 X_ATTRIBUTE_CODE,
59 X_SEQUENCE_NUMBER,
60 X_OPERATION,
61 X_VALUE_VARCHAR2,
62 X_VALUE_NUMBER,
63 X_VALUE_DATE,
64 X_CREATED_BY,
65 X_CREATION_DATE,
66 X_LAST_UPDATED_BY,
67 X_LAST_UPDATE_DATE,
68 X_LAST_UPDATE_LOGIN,
69 X_START_DATE_ACTIVE,
70 X_END_DATE_ACTIVE
71 );
72
73 open C;
74 fetch C into X_ROWID;
75 if (C%notfound) then
76 close C;
77 raise no_data_found;
78 end if;
79 close C;
80 end INSERT_ROW;
81
82 procedure LOCK_ROW (
83 X_CUSTOMIZATION_APPLICATION_ID in NUMBER,
84 X_CUSTOMIZATION_CODE in VARCHAR2,
85 X_REGION_APPLICATION_ID in NUMBER,
86 X_REGION_CODE in VARCHAR2,
87 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
88 X_ATTRIBUTE_CODE in VARCHAR2,
89 X_SEQUENCE_NUMBER in NUMBER,
90 X_OPERATION in VARCHAR2,
91 X_VALUE_VARCHAR2 in VARCHAR2,
92 X_VALUE_NUMBER in NUMBER,
93 X_VALUE_DATE in DATE,
94 X_CREATED_BY in NUMBER,
95 X_CREATION_DATE in DATE,
96 X_LAST_UPDATED_BY in NUMBER,
97 X_LAST_UPDATE_DATE in DATE,
98 X_LAST_UPDATE_LOGIN in NUMBER,
99 X_START_DATE_ACTIVE in DATE,
100 X_END_DATE_ACTIVE in DATE
101 ) is
102 cursor C is select
103 CUSTOMIZATION_APPLICATION_ID,
104 CUSTOMIZATION_CODE,
105 REGION_APPLICATION_ID,
106 REGION_CODE,
107 ATTRIBUTE_APPLICATION_ID,
108 ATTRIBUTE_CODE,
109 SEQUENCE_NUMBER,
110 OPERATION,
111 VALUE_VARCHAR2,
112 VALUE_NUMBER,
113 VALUE_DATE
114 from AK_CRITERIA
115 where CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
116 and CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
117 and REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
118 and REGION_CODE = X_REGION_CODE
119 and ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
120 and ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
121 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
122 for update of CUSTOMIZATION_APPLICATION_ID nowait;
123 recinfo C%rowtype;
124 begin
125 open C;
126 fetch C into recinfo;
127 if (C%notfound) then
128 close C;
129 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
130 app_exception.raise_exception;
131 end if;
132 close C;
133
134 if (((recinfo.CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID)
135 OR ((recinfo.CUSTOMIZATION_APPLICATION_ID is null)
136 AND (X_CUSTOMIZATION_APPLICATION_ID is null)))
137 AND ((recinfo.CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE)
138 OR ((recinfo.CUSTOMIZATION_CODE is null)
139 AND (X_CUSTOMIZATION_CODE is null)))
140 AND ((recinfo.REGION_APPLICATION_ID = X_REGION_APPLICATION_ID)
141 OR ((recinfo.REGION_APPLICATION_ID is null)
142 AND (X_REGION_APPLICATION_ID is null)))
143 AND ((recinfo.REGION_CODE = X_REGION_CODE)
144 OR ((recinfo.REGION_CODE is null)
145 AND (X_REGION_CODE is null)))
146 AND ((recinfo.ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID)
147 OR ((recinfo.ATTRIBUTE_APPLICATION_ID is null)
148 AND (X_ATTRIBUTE_APPLICATION_ID is null)))
149 AND ((recinfo.ATTRIBUTE_CODE = X_ATTRIBUTE_CODE)
150 OR ((recinfo.ATTRIBUTE_CODE is null)
151 AND (X_ATTRIBUTE_CODE is null)))
152 AND ((recinfo.SEQUENCE_NUMBER = X_SEQUENCE_NUMBER)
153 OR ((recinfo.SEQUENCE_NUMBER is null)
154 AND (X_SEQUENCE_NUMBER is null)))
155 AND ((recinfo.OPERATION = X_OPERATION)
156 OR ((recinfo.OPERATION is null)
157 AND (X_OPERATION is null)))
158 AND ((recinfo.VALUE_VARCHAR2 = X_VALUE_VARCHAR2)
159 OR ((recinfo.VALUE_VARCHAR2 is null)
160 AND (X_VALUE_VARCHAR2 is null)))
161 AND ((recinfo.VALUE_NUMBER = X_VALUE_NUMBER)
162 OR ((recinfo.VALUE_NUMBER is null)
163 AND (X_VALUE_NUMBER is null)))
164 AND ((recinfo.VALUE_DATE = X_VALUE_DATE)
165 OR ((recinfo.VALUE_DATE is null)
166 AND (X_VALUE_DATE is null)))
167 ) then
168 null;
169 else
170 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
171 app_exception.raise_exception;
172 end if;
173 return;
174 end LOCK_ROW;
175
176 procedure UPDATE_ROW (
177 X_CUSTOMIZATION_APPLICATION_ID in NUMBER,
178 X_CUSTOMIZATION_CODE in VARCHAR2,
179 X_REGION_APPLICATION_ID in NUMBER,
180 X_REGION_CODE in VARCHAR2,
181 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
182 X_ATTRIBUTE_CODE in VARCHAR2,
183 X_SEQUENCE_NUMBER in NUMBER,
184 X_OPERATION in VARCHAR2,
185 X_VALUE_VARCHAR2 in VARCHAR2,
186 X_VALUE_NUMBER in NUMBER,
187 X_VALUE_DATE in DATE,
188 X_LAST_UPDATED_BY in NUMBER,
189 X_LAST_UPDATE_DATE in DATE,
190 X_LAST_UPDATE_LOGIN in NUMBER,
191 X_START_DATE_ACTIVE in DATE,
192 X_END_DATE_ACTIVE in DATE
193 ) is
194 begin
195 update AK_CRITERIA set
196 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID,
197 CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE,
198 REGION_APPLICATION_ID = X_REGION_APPLICATION_ID,
199 REGION_CODE = X_REGION_CODE,
200 ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID,
201 ATTRIBUTE_CODE = X_ATTRIBUTE_CODE,
202 SEQUENCE_NUMBER = X_SEQUENCE_NUMBER,
203 OPERATION = X_OPERATION,
204 VALUE_VARCHAR2 = X_VALUE_VARCHAR2,
205 VALUE_NUMBER = X_VALUE_NUMBER,
206 VALUE_DATE = X_VALUE_DATE,
207 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
208 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
209 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
210 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
211 END_DATE_ACTIVE = X_END_DATE_ACTIVE
212 where CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
213 and CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
214 and REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
215 and REGION_CODE = X_REGION_CODE
216 and ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
217 and ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
218 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
219
220 if (sql%notfound) then
221 raise no_data_found;
222 end if;
223 end UPDATE_ROW;
224
225 procedure DELETE_ROW (
226 X_CUSTOMIZATION_APPLICATION_ID in NUMBER,
227 X_CUSTOMIZATION_CODE in VARCHAR2,
228 X_REGION_APPLICATION_ID in NUMBER,
229 X_REGION_CODE in VARCHAR2,
230 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
231 X_ATTRIBUTE_CODE in VARCHAR2,
232 X_SEQUENCE_NUMBER in NUMBER
233 ) is
234 begin
235 delete from AK_CRITERIA
236 where CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
237 and CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
238 and REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
239 and REGION_CODE = X_REGION_CODE
240 and ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
241 and ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
242 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
243
244 if (sql%notfound) then
245 raise no_data_found;
246 end if;
247 end DELETE_ROW;
248
249 end AK_CRITERIA_PKG;
[Home] [Help]