1 PACKAGE BODY STGLN_ASSG_PKG AS
2 /* $Header: WMSDSLNB.pls 120.1 2005/06/20 05:50:52 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
5 X_DOCK_DOOR_ID in NUMBER,
6 X_STAGE_LANE_ID in NUMBER,
7 X_DOCK_DOOR_ORGANIZATION_ID in NUMBER,
8 X_STAGING_LANE_ORGANIZATION_ID in NUMBER,
9 X_ENTRY_SEQUENCE in NUMBER,
10 X_ENABLED in VARCHAR2,
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 X_ATTRIBUTE_CATEGORY in VARCHAR2,
17 X_ATTRIBUTE1 in VARCHAR2,
18 X_ATTRIBUTE2 in VARCHAR2,
19 X_ATTRIBUTE3 in VARCHAR2,
20 X_ATTRIBUTE4 in VARCHAR2,
21 X_ATTRIBUTE5 in VARCHAR2,
22 X_ATTRIBUTE6 in VARCHAR2,
23 X_ATTRIBUTE7 in VARCHAR2,
24 X_ATTRIBUTE8 in VARCHAR2,
25 X_ATTRIBUTE9 in VARCHAR2,
26 X_ATTRIBUTE10 in VARCHAR2,
27 X_ATTRIBUTE11 in VARCHAR2,
28 X_ATTRIBUTE12 in VARCHAR2,
29 X_ATTRIBUTE13 in VARCHAR2,
30 X_ATTRIBUTE14 in VARCHAR2,
31 X_ATTRIBUTE15 in VARCHAR2
32 ) is
33 cursor get_row is
34 select ROWID from WMS_STAGINGLANES_ASSIGNMENTS
35 where DOCK_DOOR_ID = X_DOCK_DOOR_ID
36 and DOCK_DOOR_ORGANIZATION_ID = X_DOCK_DOOR_ORGANIZATION_ID
37 AND STAGE_LANE_ID = X_STAGE_LANE_ID
38 and STAGING_LANE_ORGANIZATION_ID = X_STAGING_LANE_ORGANIZATION_ID
39 and ENTRY_SEQUENCE = X_ENTRY_SEQUENCE;
40 begin
41 insert into WMS_STAGINGLANES_ASSIGNMENTS (
42 DOCK_DOOR_ID,
43 STAGE_LANE_ID,
44 DOCK_DOOR_ORGANIZATION_ID,
45 STAGING_LANE_ORGANIZATION_ID,
46 ENTRY_SEQUENCE,
47 ENABLED,
48 CREATION_DATE,
49 CREATED_BY,
50 LAST_UPDATE_DATE,
51 LAST_UPDATED_BY,
52 LAST_UPDATE_LOGIN,
53 ATTRIBUTE_CATEGORY,
54 ATTRIBUTE1,
55 ATTRIBUTE2,
56 ATTRIBUTE3,
57 ATTRIBUTE4,
58 ATTRIBUTE5,
59 ATTRIBUTE6,
60 ATTRIBUTE7,
61 ATTRIBUTE8,
62 ATTRIBUTE9,
63 ATTRIBUTE10,
64 ATTRIBUTE11,
65 ATTRIBUTE12,
66 ATTRIBUTE13,
67 ATTRIBUTE14,
68 ATTRIBUTE15)
69 values (
70 X_DOCK_DOOR_ID,
71 X_STAGE_LANE_ID,
72 X_DOCK_DOOR_ORGANIZATION_ID,
73 X_STAGING_LANE_ORGANIZATION_ID,
74 X_ENTRY_SEQUENCE,
75 X_ENABLED,
76 X_CREATION_DATE,
77 X_CREATED_BY,
78 X_LAST_UPDATE_DATE,
79 X_LAST_UPDATED_BY,
80 X_LAST_UPDATE_LOGIN,
81 X_ATTRIBUTE_CATEGORY,
82 X_ATTRIBUTE1,
83 X_ATTRIBUTE2,
84 X_ATTRIBUTE3,
85 X_ATTRIBUTE4,
86 X_ATTRIBUTE5,
87 X_ATTRIBUTE6,
88 X_ATTRIBUTE7,
89 X_ATTRIBUTE8,
90 X_ATTRIBUTE9,
91 X_ATTRIBUTE10,
92 X_ATTRIBUTE11,
93 X_ATTRIBUTE12,
94 X_ATTRIBUTE13,
95 X_ATTRIBUTE14,
96 X_ATTRIBUTE15
97 );
98 open get_row;
99 fetch get_row into X_ROWID;
100 if (get_row%notfound) then
101 close get_row;
102 raise no_data_found;
103 end if;
104 close get_row;
105
106 end insert_row;
107
108 /*****************************************************************/
109
110 procedure LOCK_ROW (
111 X_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
112 X_DOCK_DOOR_ID in NUMBER,
113 X_STAGE_LANE_ID in NUMBER,
114 X_DOCK_DOOR_ORGANIZATION_ID in NUMBER,
115 X_STAGING_LANE_ORGANIZATION_ID in NUMBER,
116 X_ENTRY_SEQUENCE in NUMBER,
117 X_ENABLED in VARCHAR2,
118 X_CREATION_DATE in DATE,
119 X_CREATED_BY in NUMBER,
120 X_LAST_UPDATE_DATE in DATE,
121 X_LAST_UPDATED_BY in NUMBER,
122 X_LAST_UPDATE_LOGIN in NUMBER,
123 X_ATTRIBUTE_CATEGORY in VARCHAR2,
124 X_ATTRIBUTE1 in VARCHAR2,
125 X_ATTRIBUTE2 in VARCHAR2,
126 X_ATTRIBUTE3 in VARCHAR2,
127 X_ATTRIBUTE4 in VARCHAR2,
128 X_ATTRIBUTE5 in VARCHAR2,
129 X_ATTRIBUTE6 in VARCHAR2,
130 X_ATTRIBUTE7 in VARCHAR2,
131 X_ATTRIBUTE8 in VARCHAR2,
132 X_ATTRIBUTE9 in VARCHAR2,
133 X_ATTRIBUTE10 in VARCHAR2,
134 X_ATTRIBUTE11 in VARCHAR2,
135 X_ATTRIBUTE12 in VARCHAR2,
136 X_ATTRIBUTE13 in VARCHAR2,
137 X_ATTRIBUTE14 in VARCHAR2,
138 X_ATTRIBUTE15 in VARCHAR2)
139 is
140 cursor get_lock is select
141 DOCK_DOOR_ID,
142 STAGE_LANE_ID,
143 DOCK_DOOR_ORGANIZATION_ID,
144 STAGING_LANE_ORGANIZATION_ID,
145 ENTRY_SEQUENCE,
146 ENABLED,
147 CREATION_DATE,
148 CREATED_BY,
149 LAST_UPDATE_DATE,
150 LAST_UPDATED_BY,
151 LAST_UPDATE_LOGIN,
152 ATTRIBUTE_CATEGORY,
153 ATTRIBUTE1,
154 ATTRIBUTE2,
155 ATTRIBUTE3,
156 ATTRIBUTE4,
157 ATTRIBUTE5,
158 ATTRIBUTE6,
159 ATTRIBUTE7,
160 ATTRIBUTE8,
161 ATTRIBUTE9,
162 ATTRIBUTE10,
163 ATTRIBUTE11,
164 ATTRIBUTE12,
165 ATTRIBUTE13,
166 ATTRIBUTE14,
167 ATTRIBUTE15
168 from WMS_STAGINGLANES_ASSIGNMENTS
169 where ROWID = X_ROWID
170 for update of DOCK_DOOR_ID nowait;
171 recinfo get_lock%rowtype;
172
173 begin
174 open get_lock;
175 fetch get_lock into recinfo;
176 if (get_lock%notfound) then
177 close get_lock;
178 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
179 app_exception.raise_exception;
180 end if;
181 close get_lock;
182
183 if
184 -- check if the mandatory columns match values in the form
185 ((recinfo.DOCK_DOOR_ID = X_DOCK_DOOR_ID)
186 AND (recinfo.STAGE_LANE_ID = X_STAGE_LANE_ID)
187 AND (recinfo.DOCK_DOOR_ORGANIZATION_ID = X_DOCK_DOOR_ORGANIZATION_ID)
188 AND (recinfo.STAGING_LANE_ORGANIZATION_ID = X_STAGING_LANE_ORGANIZATION_ID)
189 AND (recinfo.ENTRY_SEQUENCE = X_ENTRY_SEQUENCE)
190 AND (recinfo.ENABLED = X_ENABLED)
191 AND (recinfo.CREATION_DATE = X_CREATION_DATE)
192 AND (recinfo.CREATED_BY = X_CREATED_BY)
193 AND (recinfo.LAST_UPDATE_DATE = X_LAST_UPDATE_DATE)
194 AND (recinfo.LAST_UPDATED_BY = X_LAST_UPDATED_BY)
195 AND (recinfo.LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN)
196 -- check if the non-mandatory columns match values in the form
197 AND ((recinfo.attribute_category = X_attribute_category)
198 OR ((recinfo.attribute_category is null) AND (X_attribute_category is null)))
199 AND ((recinfo.attribute1 = X_attribute1)
200 OR ((recinfo.attribute1 is null) AND (X_attribute1 is null)))
201 AND ((recinfo.attribute2 = X_attribute2)
202 OR ((recinfo.attribute2 is null) AND (X_attribute2 is null)))
203 AND ((recinfo.attribute3 = X_attribute3)
204 OR ((recinfo.attribute3 is null) AND (X_attribute3 is null)))
205 AND ((recinfo.attribute4 = X_attribute4)
206 OR ((recinfo.attribute4 is null) AND (X_attribute4 is null)))
207 AND ((recinfo.attribute5 = X_attribute5)
208 OR ((recinfo.attribute5 is null) AND (X_attribute5 is null)))
209 AND ((recinfo.attribute6 = X_attribute6)
210 OR ((recinfo.attribute6 is null) AND (X_attribute6 is null)))
211 AND ((recinfo.attribute7 = X_attribute7)
212 OR ((recinfo.attribute7 is null) AND (X_attribute7 is null)))
213 AND ((recinfo.attribute8 = X_attribute8)
214 OR ((recinfo.attribute8 is null) AND (X_attribute8 is null)))
215 AND ((recinfo.attribute9 = X_attribute9)
216 OR ((recinfo.attribute9 is null) AND (X_attribute9 is null)))
217 AND ((recinfo.attribute10 = X_attribute10)
218 OR ((recinfo.attribute10 is null) AND (X_attribute10 is null)))
219 )
220 then
221 return;
222 else
223 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
224 app_exception.raise_exception;
225 end if;
226
227 end LOCK_ROW;
228
229 /*******************************************************************************/
230
231 procedure UPDATE_ROW (
232 X_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
233 X_DOCK_DOOR_ID in NUMBER,
234 X_STAGE_LANE_ID in NUMBER,
235 X_DOCK_DOOR_ORGANIZATION_ID in NUMBER,
236 X_STAGING_LANE_ORGANIZATION_ID in NUMBER,
237 X_ENTRY_SEQUENCE in NUMBER,
238 X_ENABLED in VARCHAR2,
239 X_LAST_UPDATE_DATE in DATE,
240 X_LAST_UPDATED_BY in NUMBER,
241 X_LAST_UPDATE_LOGIN in NUMBER,
242 X_ATTRIBUTE_CATEGORY in VARCHAR2,
243 X_ATTRIBUTE1 in VARCHAR2,
244 X_ATTRIBUTE2 in VARCHAR2,
245 X_ATTRIBUTE3 in VARCHAR2,
246 X_ATTRIBUTE4 in VARCHAR2,
247 X_ATTRIBUTE5 in VARCHAR2,
248 X_ATTRIBUTE6 in VARCHAR2,
249 X_ATTRIBUTE7 in VARCHAR2,
250 X_ATTRIBUTE8 in VARCHAR2,
251 X_ATTRIBUTE9 in VARCHAR2,
252 X_ATTRIBUTE10 in VARCHAR2,
253 X_ATTRIBUTE11 in VARCHAR2,
254 X_ATTRIBUTE12 in VARCHAR2,
255 X_ATTRIBUTE13 in VARCHAR2,
256 X_ATTRIBUTE14 in VARCHAR2,
257 X_ATTRIBUTE15 in VARCHAR2)
258 is
259 begin
260 update WMS_STAGINGLANES_ASSIGNMENTS set
261 STAGE_LANE_ID = X_STAGE_LANE_ID,
262 STAGING_LANE_ORGANIZATION_ID = X_STAGING_LANE_ORGANIZATION_ID,
263 ENABLED = X_ENABLED,
264 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
265 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
266 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
267 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
268 ATTRIBUTE1 = X_ATTRIBUTE1,
269 ATTRIBUTE2 = X_ATTRIBUTE2,
270 ATTRIBUTE3 = X_ATTRIBUTE3,
271 ATTRIBUTE4 = X_ATTRIBUTE4,
272 ATTRIBUTE5 = X_ATTRIBUTE5,
273 ATTRIBUTE6 = X_ATTRIBUTE6,
274 ATTRIBUTE7 = X_ATTRIBUTE7,
275 ATTRIBUTE8 = X_ATTRIBUTE8,
276 ATTRIBUTE9 = X_ATTRIBUTE9,
277 ATTRIBUTE10 = X_ATTRIBUTE10,
278 ATTRIBUTE11 = X_ATTRIBUTE11,
279 ATTRIBUTE12 = X_ATTRIBUTE12,
280 ATTRIBUTE13 = X_ATTRIBUTE13,
281 ATTRIBUTE14 = X_ATTRIBUTE14,
282 ATTRIBUTE15 = X_ATTRIBUTE15
283 where DOCK_DOOR_ID = X_DOCK_DOOR_ID
284 and DOCK_DOOR_ORGANIZATION_ID = X_DOCK_DOOR_ORGANIZATION_ID
285 and ENTRY_SEQUENCE = X_ENTRY_SEQUENCE;
286
287 if (sql%notfound) then
288 raise no_data_found;
289 end if;
290
291 end UPDATE_ROW;
292
293 /********************************************************************************/
294
295 procedure DELETE_ROW (
296 X_DOCK_DOOR_ID in NUMBER,
297 X_DOCK_DOOR_ORGANIZATION_ID in NUMBER,
298 X_ENTRY_SEQUENCE in NUMBER)
299 is
300 begin
301 delete from WMS_STAGINGLANES_ASSIGNMENTS
302 where DOCK_DOOR_ID = X_DOCK_DOOR_ID
303 and DOCK_DOOR_ORGANIZATION_ID = X_DOCK_DOOR_ORGANIZATION_ID
304 and ENTRY_SEQUENCE = X_ENTRY_SEQUENCE;
305
306 if (sql%notfound) then
307 raise no_data_found;
308 end if;
309
310 end DELETE_ROW;
311
312 /*******************************************************************************/
313
314 end STGLN_ASSG_PKG;
[Home] [Help]