[Home] [Help]
PACKAGE BODY: APPS.WSH_DEPARTURE_TEMPLATE_ROWS
Source
1 package body wsh_departure_template_rows as
2 /* $Header: WSHTDEPB.pls 115.1 99/07/16 08:22:23 porting shi $ */
3
4
5 -- ===========================================================================
6 --
7 -- Name:
8 --
9 -- insert_row
10 --
11 -- Description:
12 --
13 -- Called by the client to insert a row into the
14 -- WSH_DEPARTURE_TEMPLATES table.
15 --
16 -- ===========================================================================
17
18 procedure insert_row(
19 X_rowid in out varchar2,
20 X_departure_template_id in out number,
21 X_name varchar2,
22 X_organization_id number,
23 X_vehicle_item_id number,
24 X_vehicle_number varchar2,
25 X_freight_carrier_code varchar2,
26 X_planned_frequency varchar2,
27 X_planned_day varchar2,
28 X_planned_time number,
29 X_weight_uom_code varchar2,
30 X_volume_uom_code varchar2,
31 X_routing_instructions varchar2,
32 X_attribute_category varchar2,
33 X_attribute1 varchar2,
34 X_attribute2 varchar2,
35 X_attribute3 varchar2,
36 X_attribute4 varchar2,
37 X_attribute5 varchar2,
38 X_attribute6 varchar2,
39 X_attribute7 varchar2,
40 X_attribute8 varchar2,
41 X_attribute9 varchar2,
42 X_attribute10 varchar2,
43 X_attribute11 varchar2,
44 X_attribute12 varchar2,
45 X_attribute13 varchar2,
46 X_attribute14 varchar2,
47 X_attribute15 varchar2,
48 X_creation_date date,
49 X_created_by number,
50 X_last_update_date date,
51 X_last_updated_by number,
52 X_last_update_login number)
53 is
54
55 cursor id_sequence is
56 select wsh_departure_templates_s.nextval
57 from sys.dual;
58
59 cursor row_id is
60 select rowid
61 from wsh_departure_templates
62 where departure_template_id = X_departure_template_id;
63
64 begin
65
66 open id_sequence;
67 fetch id_sequence into X_departure_template_id;
68 close id_sequence;
69
70 insert into wsh_departure_templates(
71
72 departure_template_id,
73 name,
74 organization_id,
75 vehicle_item_id,
76 vehicle_number ,
77 freight_carrier_code,
78 planned_frequency,
79 planned_day,
80 planned_time,
81 weight_uom_code,
82 volume_uom_code,
83 routing_instructions,
84 attribute_category,
85 attribute1,
86 attribute2,
87 attribute3,
88 attribute4,
89 attribute5,
90 attribute6,
91 attribute7,
92 attribute8,
93 attribute9,
94 attribute10,
95 attribute11,
96 attribute12,
97 attribute13,
98 attribute14,
99 attribute15,
100 creation_date,
101 created_by,
102 last_update_date,
103 last_updated_by,
104 last_update_login)
105
106 values(
107
108 X_departure_template_id,
109 X_name,
110 X_organization_id,
111 X_vehicle_item_id,
112 X_vehicle_number ,
113 X_freight_carrier_code,
114 X_planned_frequency,
115 X_planned_day,
116 X_planned_time,
117 X_weight_uom_code,
118 X_volume_uom_code,
119 X_routing_instructions,
120 X_attribute_category,
121 X_attribute1,
122 X_attribute2,
123 X_attribute3,
124 X_attribute4,
125 X_attribute5,
126 X_attribute6,
127 X_attribute7,
128 X_attribute8,
129 X_attribute9,
130 X_attribute10,
131 X_attribute11,
132 X_attribute12,
133 X_attribute13,
134 X_attribute14,
135 X_attribute15,
136 X_creation_date,
137 X_created_by,
138 X_last_update_date,
139 X_last_updated_by,
140 X_last_update_login
141 );
142
143 open row_id;
144
145 fetch row_id into X_rowid;
146
147 if (row_id%NOTFOUND) then
148 close row_id;
149 raise NO_DATA_FOUND;
150 end if;
151
152 close row_id;
153
154 exception
155 when DUP_VAL_ON_INDEX then
156 fnd_message.set_name('OE', 'WSH_TMPL_DUP_VAL');
157 app_exception.raise_exception;
158
159 end insert_row;
160
161
162 -- ===========================================================================
163 --
164 -- Name:
165 --
166 -- lock_row
167 --
168 -- Description:
169 --
170 -- Called by the client to lock a row into the
171 -- WSH_DEPARTURE_TEMPLATES table.
172 --
173 -- ===========================================================================
174
175 procedure lock_row(
176 X_rowid varchar2,
177 X_departure_template_id number,
178 X_name varchar2,
179 X_organization_id number,
180 X_vehicle_item_id number,
181 X_vehicle_number varchar2,
182 X_freight_carrier_code varchar2,
183 X_planned_frequency varchar2,
184 X_planned_day varchar2,
185 X_planned_time number,
186 X_weight_uom_code varchar2,
187 X_volume_uom_code varchar2,
188 X_routing_instructions varchar2,
189 X_attribute_category varchar2,
190 X_attribute1 varchar2,
191 X_attribute2 varchar2,
192 X_attribute3 varchar2,
193 X_attribute4 varchar2,
194 X_attribute5 varchar2,
195 X_attribute6 varchar2,
196 X_attribute7 varchar2,
197 X_attribute8 varchar2,
198 X_attribute9 varchar2,
199 X_attribute10 varchar2,
200 X_attribute11 varchar2,
201 X_attribute12 varchar2,
202 X_attribute13 varchar2,
203 X_attribute14 varchar2,
204 X_attribute15 varchar2
205 )
206 is
207
208 cursor lock_record is
209 select *
210 from wsh_departure_templates
211 where rowid = X_rowid
212 for update nowait;
213
214 rec_info lock_record%ROWTYPE;
215
216 begin
217
218 open lock_record;
219
220 fetch lock_record into rec_info;
221
222 if (lock_record%NOTFOUND) then
223 close lock_record;
224
225 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
226 app_exception.raise_exception;
227
228 end if;
229
230 close lock_record;
231
232 if (
233 (rec_info.departure_template_id = X_departure_template_id)
234 and
235 ((rec_info.name = X_name)
236 or
237 ((rec_info.name is null)
238 and (X_name is null)))
239 and
240 (rec_info.organization_id = X_organization_id)
241 and
242 ((rec_info.vehicle_item_id = X_vehicle_item_id)
243 or
244 ((rec_info.vehicle_item_id is null)
245 and (X_vehicle_item_id is null)))
246 and
247 ((rec_info.vehicle_number = X_vehicle_number)
248 or
249 ((rec_info.vehicle_number is null)
250 and (X_vehicle_number is null)))
251 and
252 ((rec_info.freight_carrier_code = X_freight_carrier_code)
253 or
254 ((rec_info.freight_carrier_code is null)
255 and (X_freight_carrier_code is null)))
256 and
257 (rec_info.planned_frequency = X_planned_frequency)
258 and
259 ((rec_info.planned_day = X_planned_day)
260 or
261 ((rec_info.planned_day is null)
262 and (X_planned_day is null)))
263 and
264 ((rec_info.planned_time = X_planned_time)
265 or
266 ((rec_info.planned_time is null)
267 and (X_planned_time is null)))
268 and
269 ((rec_info.weight_uom_code = X_weight_uom_code)
270 or
271 ((rec_info.weight_uom_code is null)
272 and (X_weight_uom_code is null)))
273 and
274 ((rec_info.volume_uom_code = X_volume_uom_code)
275 or
276 ((rec_info.volume_uom_code is null)
277 and (X_volume_uom_code is null)))
278 and
279 ((rec_info.routing_instructions = X_routing_instructions)
280 or
281 ((rec_info.routing_instructions is null)
282 and (X_routing_instructions is null)))
283 and
284 ((rec_info.attribute_category = X_attribute_category)
285 or
286 ((rec_info.attribute_category is null)
287 and (X_attribute_category is null)))
288 and
289 ((rec_info.attribute1 = X_attribute1)
290 or
291 ((rec_info.attribute1 is null)
292 and (X_attribute1 is null)))
293 and
294 ((rec_info.attribute2 = X_attribute2)
295 or
296 ((rec_info.attribute2 is null)
297 and (X_attribute2 is null)))
298 and
299 ((rec_info.attribute3 = X_attribute3)
300 or
301 ((rec_info.attribute3 is null)
302 and (X_attribute3 is null)))
303 and
304 ((rec_info.attribute4 = X_attribute4)
305 or
306 ((rec_info.attribute4 is null)
307 and (X_attribute4 is null)))
308 and
309 ((rec_info.attribute5 = X_attribute5)
310 or
311 ((rec_info.attribute5 is null)
312 and (X_attribute5 is null)))
313 and
314 ((rec_info.attribute6 = X_attribute6)
315 or
316 ((rec_info.attribute6 is null)
317 and (X_attribute6 is null)))
318 and
319 ((rec_info.attribute7 = X_attribute7)
320 or
321 ((rec_info.attribute7 is null)
322 and (X_attribute7 is null)))
323 and
324 ((rec_info.attribute8 = X_attribute8)
325 or
326 ((rec_info.attribute8 is null)
327 and (X_attribute8 is null)))
328 and
329 ((rec_info.attribute9 = X_attribute9)
330 or
331 ((rec_info.attribute9 is null)
332 and (X_attribute9 is null)))
333 and
334 ((rec_info.attribute10 = X_attribute10)
335 or
336 ((rec_info.attribute10 is null)
337 and (X_attribute10 is null)))
338 and
339 ((rec_info.attribute11 = X_attribute11)
340 or
341 ((rec_info.attribute11 is null)
342 and (X_attribute11 is null)))
343 and
344 ((rec_info.attribute12 = X_attribute12)
345 or
346 ((rec_info.attribute12 is null)
347 and (X_attribute12 is null)))
348 and
349 ((rec_info.attribute13 = X_attribute13)
350 or
351 ((rec_info.attribute13 is null)
352 and (X_attribute13 is null)))
353 and
354 ((rec_info.attribute14 = X_attribute14)
355 or
356 ((rec_info.attribute14 is null)
357 and (X_attribute14 is null)))
358 and
359 ((rec_info.attribute15 = X_attribute15)
360 or
361 ((rec_info.attribute15 is null)
362 and (X_attribute15 is null)))
363 ) then
364
365 return;
366
367 else
368
369 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
370 app_exception.raise_exception;
371
372 end if;
373
374 end lock_row;
375
376
377 -- ===========================================================================
378 --
379 -- Name:
380 --
381 -- update_row
382 --
383 -- Description:
384 --
385 -- Called by the client to update a row into the
386 -- WSH_DEPARTURE_TEMPLATES table.
387 --
388 -- ===========================================================================
389
390 procedure update_row(
391 X_rowid varchar2,
392 X_departure_template_id number,
393 X_name varchar2,
394 X_organization_id number,
395 X_vehicle_item_id number,
396 X_vehicle_number varchar2,
397 X_freight_carrier_code varchar2,
398 X_planned_frequency varchar2,
399 X_planned_day varchar2,
400 X_planned_time number,
401 X_weight_uom_code varchar2,
402 X_volume_uom_code varchar2,
403 X_routing_instructions varchar2,
404 X_attribute_category varchar2,
405 X_attribute1 varchar2,
406 X_attribute2 varchar2,
407 X_attribute3 varchar2,
408 X_attribute4 varchar2,
409 X_attribute5 varchar2,
410 X_attribute6 varchar2,
411 X_attribute7 varchar2,
412 X_attribute8 varchar2,
413 X_attribute9 varchar2,
414 X_attribute10 varchar2,
415 X_attribute11 varchar2,
416 X_attribute12 varchar2,
417 X_attribute13 varchar2,
418 X_attribute14 varchar2,
419 X_attribute15 varchar2,
420 X_last_update_date date,
421 X_last_updated_by number,
422 X_last_update_login number)
423 is
424 begin
425
426 update wsh_departure_templates set
427
428 departure_template_id = X_departure_template_id,
429 name = X_name,
430 organization_id = X_organization_id,
431 vehicle_item_id = X_vehicle_item_id,
432 vehicle_number = X_vehicle_number ,
433 freight_carrier_code = X_freight_carrier_code,
434 planned_frequency = X_planned_frequency,
435 planned_day = X_planned_day,
436 planned_time = X_planned_time,
437 weight_uom_code = X_weight_uom_code,
438 volume_uom_code = X_volume_uom_code,
439 routing_instructions = X_routing_instructions,
440 attribute_category = X_attribute_category,
441 attribute1 = X_attribute1,
442 attribute2 = X_attribute2,
443 attribute3 = X_attribute3,
444 attribute4 = X_attribute4,
445 attribute5 = X_attribute5,
446 attribute6 = X_attribute6,
447 attribute7 = X_attribute7,
448 attribute8 = X_attribute8,
449 attribute9 = X_attribute9,
450 attribute10 = X_attribute10,
451 attribute11 = X_attribute11,
452 attribute12 = X_attribute12,
453 attribute13 = X_attribute13,
454 attribute14 = X_attribute14,
455 attribute15 = X_attribute15,
456 last_update_date = X_last_update_date,
457 last_updated_by = X_last_updated_by,
458 last_update_login = X_last_update_login
459
460 where rowid = X_rowid;
461
462 if (SQL%NOTFOUND) then
463 raise NO_DATA_FOUND;
464 end if;
465
466 exception
467 when DUP_VAL_ON_INDEX then
468 fnd_message.set_name('OE', 'WSH_TMPL_DUP_VAL');
469 app_exception.raise_exception;
470
471 end update_row;
472
473
474 -- ===========================================================================
475 --
476 -- Name:
477 --
478 -- delete_row
479 --
480 -- Description:
481 --
482 -- Called by the client to delete a row into the
483 -- WSH_DEPARTURE_TEMPLATES table.
484 --
485 -- ===========================================================================
486
487 procedure delete_row(X_rowid varchar2)
488 is
489 begin
490
491 delete from wsh_departure_templates
492 where rowid = X_rowid;
493
494 if (SQL%NOTFOUND) then
495 raise NO_DATA_FOUND;
496 end if;
497
498 end delete_row;
499
500
501 end wsh_departure_template_rows;