1 PACKAGE BODY WMS_ORG_HIER_OBJS_PKG AS
2 /* $Header: WMSPOHOB.pls 120.1 2005/06/20 03:15:27 appldev ship $ */
3 --
4 PROCEDURE INSERT_ROW (
5 x_rowid IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6 ,x_organization_id IN NUMBER
7 ,x_object_id IN NUMBER
8 ,x_last_updated_by IN NUMBER
9 ,x_last_update_date IN DATE
10 ,x_created_by IN NUMBER
11 ,x_creation_date IN DATE
12 ,x_last_update_login IN NUMBER
13 ,x_search_order IN NUMBER
14 ,x_attribute_category IN VARCHAR2
15 ,x_attribute1 IN VARCHAR2
16 ,x_attribute2 IN VARCHAR2
17 ,x_attribute3 IN VARCHAR2
18 ,x_attribute4 IN VARCHAR2
19 ,x_attribute5 IN VARCHAR2
20 ,x_attribute6 IN VARCHAR2
21 ,x_attribute7 IN VARCHAR2
22 ,x_attribute8 IN VARCHAR2
23 ,x_attribute9 IN VARCHAR2
24 ,x_attribute10 IN VARCHAR2
25 ,x_attribute11 IN VARCHAR2
26 ,x_attribute12 IN VARCHAR2
27 ,x_attribute13 IN VARCHAR2
28 ,x_attribute14 IN VARCHAR2
29 ,x_attribute15 IN VARCHAR2
30 ,x_type_code IN NUMBER
31 )IS
32 CURSOR C IS SELECT ROWID FROM WMS_ORG_HIERARCHY_OBJS
33 WHERE organization_id = x_organization_id
34 AND object_id = x_object_id
35 AND type_code = x_type_code;
36 BEGIN
37
38 INSERT INTO WMS_ORG_HIERARCHY_OBJS (
39 organization_id
40 ,object_id
41 ,last_updated_by
42 ,last_update_date
43 ,created_by
44 ,creation_date
45 ,last_update_login
46 ,search_order
47 ,attribute_category
48 ,attribute1
49 ,attribute2
50 ,attribute3
51 ,attribute4
52 ,attribute5
53 ,attribute6
54 ,attribute7
55 ,attribute8
56 ,attribute9
57 ,attribute10
58 ,attribute11
59 ,attribute12
60 ,attribute13
61 ,attribute14
62 ,attribute15
63 ,type_code
64 ) values (
65 x_organization_id
66 ,x_object_id
67 ,x_last_updated_by
68 ,x_last_update_date
69 ,x_created_by
70 ,x_creation_date
71 ,x_last_update_login
72 ,x_search_order
73 ,x_attribute_category
74 ,x_attribute1
75 ,x_attribute2
76 ,x_attribute3
77 ,x_attribute4
78 ,x_attribute5
79 ,x_attribute6
80 ,x_attribute7
81 ,x_attribute8
82 ,x_attribute9
83 ,x_attribute10
84 ,x_attribute11
85 ,x_attribute12
86 ,x_attribute13
87 ,x_attribute14
88 ,x_attribute15
89 ,x_type_code
90 );
91
92 OPEN C;
93 FETCH C INTO x_rowid;
94 IF (C%NOTFOUND) THEN
95 CLOSE C;
96 RAISE NO_DATA_FOUND;
97 END IF;
98 CLOSE C;
99 END INSERT_ROW;
100 --
101 PROCEDURE LOCK_ROW (
102 x_rowid IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
103 ,x_organization_id IN NUMBER
104 ,x_object_id IN NUMBER
105 ,x_search_order IN NUMBER
106 ,x_attribute_category IN VARCHAR2
107 ,x_attribute1 IN VARCHAR2
108 ,x_attribute2 IN VARCHAR2
109 ,x_attribute3 IN VARCHAR2
110 ,x_attribute4 IN VARCHAR2
111 ,x_attribute5 IN VARCHAR2
112 ,x_attribute6 IN VARCHAR2
113 ,x_attribute7 IN VARCHAR2
114 ,x_attribute8 IN VARCHAR2
115 ,x_attribute9 IN VARCHAR2
116 ,x_attribute10 IN VARCHAR2
117 ,x_attribute11 IN VARCHAR2
118 ,x_attribute12 IN VARCHAR2
119 ,x_attribute13 IN VARCHAR2
120 ,x_attribute14 IN VARCHAR2
121 ,x_attribute15 IN VARCHAR2
122 ,x_type_code IN NUMBER
123 )IS
124 CURSOR C IS SELECT
125 organization_id
126 ,object_id
127 ,search_order
128 ,attribute_category
129 ,attribute1
130 ,attribute2
131 ,attribute3
132 ,attribute4
133 ,attribute5
134 ,attribute6
135 ,attribute7
136 ,attribute8
137 ,attribute9
138 ,attribute10
139 ,attribute11
140 ,attribute12
141 ,attribute13
142 ,attribute14
143 ,attribute15
144 ,type_code
145 FROM WMS_ORG_HIERARCHY_OBJS
146 WHERE rowid = x_rowid
147 FOR UPDATE OF organization_id NOWAIT;
148
149 recinfo c%ROWTYPE;
150 BEGIN
151 OPEN c;
152 FETCH c INTO recinfo;
153 IF (c%notfound) THEN
154 CLOSE c;
155 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
156 app_exception.raise_exception;
157 END IF;
158 CLOSE c;
159 IF ( (recinfo.organization_id = x_organization_id)
160 AND (recinfo.object_id = x_object_id)
161 AND ((recinfo.search_order = x_search_order)
162 OR ((recinfo.search_order IS NULL)
163 AND (x_search_order IS NULL)))
164 AND ((recinfo.attribute_category = x_attribute_category)
165 OR ((recinfo.attribute_category IS NULL)
166 AND (x_attribute_category IS NULL)))
167 AND ((recinfo.attribute1 = x_attribute1)
168 OR ((recinfo.attribute1 IS NULL)
169 AND (x_attribute1 IS NULL)))
170 AND ((recinfo.attribute2 = x_attribute2)
171 OR ((recinfo.attribute2 IS NULL)
172 AND (x_attribute2 IS NULL)))
173 AND ((recinfo.attribute3 = x_attribute3)
174 OR ((recinfo.attribute3 IS NULL)
175 AND (x_attribute3 IS NULL)))
176 AND ((recinfo.attribute4 = x_attribute4)
177 OR ((recinfo.attribute4 IS NULL)
178 AND (x_attribute4 IS NULL)))
179 AND ((recinfo.attribute5 = x_attribute5)
180 OR ((recinfo.attribute5 IS NULL)
181 AND (x_attribute5 IS NULL)))
182 AND ((recinfo.attribute6 = x_attribute6)
183 OR ((recinfo.attribute6 IS NULL)
184 AND (x_attribute6 IS NULL)))
185 AND ((recinfo.attribute7 = x_attribute7)
186 OR ((recinfo.attribute7 IS NULL)
187 AND (x_attribute7 IS NULL)))
188 AND ((recinfo.attribute8 = x_attribute8)
189 OR ((recinfo.attribute8 IS NULL)
190 AND (x_attribute8 IS NULL)))
191 AND ((recinfo.attribute9 = x_attribute9)
192 OR ((recinfo.attribute9 IS NULL)
193 AND (x_attribute9 IS NULL)))
194 AND ((recinfo.attribute10 = x_attribute10)
195 OR ((recinfo.attribute10 IS NULL)
196 AND (x_attribute10 IS NULL)))
197 AND ((recinfo.attribute11 = x_attribute11)
198 OR ((recinfo.attribute11 IS NULL)
199 AND (x_attribute11 IS NULL)))
200 AND ((recinfo.attribute12 = x_attribute12)
201 OR ((recinfo.attribute12 IS NULL)
202 AND (x_attribute12 IS NULL)))
203 AND ((recinfo.attribute13 = x_attribute13)
204 OR ((recinfo.attribute13 IS NULL)
205 AND (x_attribute13 IS NULL)))
206 AND ((recinfo.attribute14 = x_attribute14)
207 OR ((recinfo.attribute14 IS NULL)
208 AND (x_attribute14 IS NULL)))
209 AND ((recinfo.attribute15 = x_attribute15)
210 OR ((recinfo.attribute15 IS NULL)
211 AND (x_attribute15 IS NULL)))
212 AND (recinfo.type_code = x_type_code)
213 ) THEN
214 NULL;
215 ELSE
216 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
217 app_exception.raise_exception;
218 END IF;
219 END LOCK_ROW;
220 --
221 PROCEDURE UPDATE_ROW (
222 x_rowid IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
223 ,x_organization_id IN NUMBER
224 ,x_object_id IN NUMBER
225 ,x_last_updated_by IN NUMBER
226 ,x_last_update_date IN DATE
227 ,x_last_update_login IN NUMBER
228 ,x_search_order IN NUMBER
229 ,x_attribute_category IN VARCHAR2
230 ,x_attribute1 IN VARCHAR2
231 ,x_attribute2 IN VARCHAR2
232 ,x_attribute3 IN VARCHAR2
233 ,x_attribute4 IN VARCHAR2
234 ,x_attribute5 IN VARCHAR2
235 ,x_attribute6 IN VARCHAR2
236 ,x_attribute7 IN VARCHAR2
237 ,x_attribute8 IN VARCHAR2
238 ,x_attribute9 IN VARCHAR2
239 ,x_attribute10 IN VARCHAR2
240 ,x_attribute11 IN VARCHAR2
241 ,x_attribute12 IN VARCHAR2
242 ,x_attribute13 IN VARCHAR2
243 ,x_attribute14 IN VARCHAR2
244 ,x_attribute15 IN VARCHAR2
245 ,x_type_code IN NUMBER
246 )IS
247 BEGIN
248 UPDATE WMS_ORG_HIERARCHY_OBJS SET
249 organization_id = x_organization_id
250 ,object_id = x_object_id
251 ,last_updated_by = x_last_updated_by
252 ,last_update_date = x_last_update_date
253 ,last_update_login = x_last_update_login
254 ,search_order = x_search_order
255 ,attribute_category = x_attribute_category
256 ,attribute1 = x_attribute1
257 ,attribute2 = x_attribute2
258 ,attribute3 = x_attribute3
259 ,attribute4 = x_attribute4
260 ,attribute5 = x_attribute5
261 ,attribute6 = x_attribute6
262 ,attribute7 = x_attribute7
263 ,attribute8 = x_attribute8
264 ,attribute9 = x_attribute9
265 ,attribute10 = x_attribute10
266 ,attribute11 = x_attribute11
267 ,attribute12 = x_attribute12
268 ,attribute13 = x_attribute13
269 ,attribute14 = x_attribute14
270 ,attribute15 = x_attribute15
271 ,type_code = x_type_code
272 WHERE rowid = x_rowid;
273
274 IF (SQL%NOTFOUND) THEN
275 RAISE NO_DATA_FOUND;
276 END IF;
277 END UPDATE_ROW;--
278
279 PROCEDURE DELETE_ROW (
280 x_rowid IN VARCHAR2
281 )IS
282 BEGIN
283
284 DELETE FROM WMS_ORG_HIERARCHY_OBJS
285 WHERE rowid = x_rowid;
286
287 IF (SQL%NOTFOUND) THEN
288 RAISE NO_DATA_FOUND;
289 END IF;
290 END DELETE_ROW;
291 END WMS_ORG_HIER_OBJS_PKG;