[Home] [Help]
PACKAGE BODY: APPS.WSH_SHIP_CONFIRM_RULES_PVT
Source
1 PACKAGE BODY WSH_SHIP_CONFIRM_RULES_PVT AS
2 /* $Header: WSHSCTHB.pls 120.1 2005/07/29 19:22:47 wrudge noship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_SHIP_CONFIRM_RULES_PVT';
6
7 PROCEDURE Insert_Row (
8 p_ship_confirm_rule_info IN ship_confirm_rule_rectype,
9 x_rule_id OUT NOCOPY NUMBER,
10 x_row_id OUT NOCOPY VARCHAR2,
11 x_return_status OUT NOCOPY VARCHAR2) IS
12
13 wsh_duplicate_name EXCEPTION;
14 l_dup_id NUMBER;
15
16 CURSOR c_dup_name IS
17 SELECT ship_confirm_rule_id
18 FROM WSH_SHIP_CONFIRM_RULES
19 WHERE name = p_ship_confirm_rule_info.name;
20
21 Begin
22
23 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
24
25 OPEN c_dup_name;
26 FETCH c_dup_name INTO l_dup_id;
27 IF c_dup_name%NOTFOUND THEN
28 l_dup_id := NULL;
29 END IF;
30 CLOSE c_dup_name;
31
32 IF l_dup_id IS NOT NULL THEN
33 RAISE WSH_DUPLICATE_NAME;
34 END IF;
35
36
37 INSERT INTO WSH_SHIP_CONFIRM_RULES
38 (
39 ship_confirm_rule_id,
40 effective_start_date,
41 name,
42 effective_end_date,
43 action_flag,
44 stage_del_flag,
45 ship_method_default_flag,
46 ship_method_code,
47 ac_actual_dep_date_default,
48 ac_intransit_flag,
49 ac_close_trip_flag,
50 ac_bol_flag,
51 ac_defer_interface_flag,
52 mc_intransit_flag,
53 mc_close_trip_flag,
54 mc_defer_interface_flag,
55 mc_bol_flag,
56 report_set_id,
57 send_945_flag,
58 creation_date,
59 created_by,
60 last_updated_by,
61 last_update_date
62 )
63 VALUES
64 (
65 wsh_ship_confirm_rules_s.nextval,
66 p_ship_confirm_rule_info.effective_start_date,
67 p_ship_confirm_rule_info.name,
68 p_ship_confirm_rule_info.effective_end_date,
69 p_ship_confirm_rule_info.action_flag,
70 p_ship_confirm_rule_info.stage_del_flag,
71 p_ship_confirm_rule_info.ship_method_default_flag,
72 p_ship_confirm_rule_info.ship_method_code,
73 p_ship_confirm_rule_info.ac_actual_dep_date_default,
74 p_ship_confirm_rule_info.ac_intransit_flag,
75 p_ship_confirm_rule_info.ac_close_trip_flag,
76 p_ship_confirm_rule_info.ac_bol_flag,
77 p_ship_confirm_rule_info.ac_defer_interface_flag,
78 p_ship_confirm_rule_info.mc_intransit_flag,
79 p_ship_confirm_rule_info.mc_close_trip_flag,
80 p_ship_confirm_rule_info.mc_defer_interface_flag,
81 p_ship_confirm_rule_info.mc_bol_flag,
82 p_ship_confirm_rule_info.report_set_id,
83 p_ship_confirm_rule_info.send_945_flag,
84 sysdate,
85 p_ship_confirm_rule_info.created_by,
86 p_ship_confirm_rule_info.last_updated_by,
87 sysdate
88 ) returning ship_confirm_rule_id, rowid into x_rule_id, x_row_id;
89
90 EXCEPTION
91 WHEN wsh_duplicate_name THEN
92 IF c_dup_name%ISOPEN THEN
93 CLOSE c_dup_name;
94 END IF;
95 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
96 FND_MESSAGE.Set_Name('FND', 'FORM_DUPLICATE_KEY_IN_INDEX');
97 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
98
99
100 WHEN others THEN
101 IF c_dup_name%ISOPEN THEN
102 CLOSE c_dup_name;
103 END IF;
104 wsh_util_core.default_handler('WSH_SHIP_CONFIRM_RULES_PVT.Insert_Row');
105 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
106
107 End Insert_Row;
108
109 PROCEDURE Update_Row (
110 p_ship_confirm_rule_info IN ship_confirm_rule_rectype,
111 x_return_status OUT NOCOPY VARCHAR2) IS
112
113
114 wsh_duplicate_name EXCEPTION;
115 l_dup_id NUMBER;
116
117 CURSOR c_dup_name IS
118 SELECT ship_confirm_rule_id
119 FROM WSH_SHIP_CONFIRM_RULES
120 WHERE name = p_ship_confirm_rule_info.name
121 and ship_confirm_rule_id <> p_ship_confirm_rule_info.ship_confirm_rule_id ;
122
123 Begin
124
125 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
126
127 OPEN c_dup_name;
128 FETCH c_dup_name INTO l_dup_id;
129 IF c_dup_name%NOTFOUND THEN
130 l_dup_id := NULL;
131 END IF;
132 CLOSE c_dup_name;
133
134 IF l_dup_id IS NOT NULL THEN
135 RAISE WSH_DUPLICATE_NAME;
136 END IF;
137
138
139
140 Update WSH_SHIP_CONFIRM_RULES
141
142 SET
143
144 EFFECTIVE_START_DATE = p_ship_confirm_rule_info.effective_start_date,
145 NAME = p_ship_confirm_rule_info.name,
146 EFFECTIVE_END_DATE = p_ship_confirm_rule_info.effective_end_date,
147 ACTION_FLAG = p_ship_confirm_rule_info.action_flag,
148 STAGE_DEL_FLAG = p_ship_confirm_rule_info.stage_del_flag,
149 SHIP_METHOD_DEFAULT_FLAG = p_ship_confirm_rule_info.ship_method_default_flag,
150 SHIP_METHOD_CODE = p_ship_confirm_rule_info.ship_method_code,
151 AC_ACTUAL_DEP_DATE_DEFAULT = p_ship_confirm_rule_info.ac_actual_dep_date_default,
152 AC_INTRANSIT_FLAG = p_ship_confirm_rule_info.ac_intransit_flag,
153 AC_CLOSE_TRIP_FLAG = p_ship_confirm_rule_info.ac_close_trip_flag,
154 AC_BOL_FLAG = p_ship_confirm_rule_info.ac_bol_flag,
155 AC_DEFER_INTERFACE_FLAG = p_ship_confirm_rule_info.ac_defer_interface_flag,
156 MC_INTRANSIT_FLAG = p_ship_confirm_rule_info.mc_intransit_flag,
157 MC_CLOSE_TRIP_FLAG = p_ship_confirm_rule_info.mc_close_trip_flag,
158 MC_DEFER_INTERFACE_FLAG = p_ship_confirm_rule_info.mc_defer_interface_flag,
159 MC_BOL_FLAG = p_ship_confirm_rule_info.mc_bol_flag,
160 REPORT_SET_ID = p_ship_confirm_rule_info.report_set_id,
161 SEND_945_FLAG = p_ship_confirm_rule_info.send_945_flag,
162 LAST_UPDATED_BY = p_ship_confirm_rule_info.last_updated_by,
163 LAST_UPDATE_DATE = SYSDATE
164 WHERE SHIP_CONFIRM_RULE_ID = p_ship_confirm_rule_info.ship_confirm_rule_id;
165
166 EXCEPTION
167 WHEN wsh_duplicate_name THEN
168 IF c_dup_name%ISOPEN THEN
169 CLOSE c_dup_name;
170 END IF;
171 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
172 FND_MESSAGE.Set_Name('FND', 'FORM_DUPLICATE_KEY_IN_INDEX');
173 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
174
175
176 WHEN others THEN
177 IF c_dup_name%ISOPEN THEN
178 CLOSE c_dup_name;
179 END IF;
180 wsh_util_core.default_handler('WSH_SHIP_CONFIRM_RULES_PVT.Update_Row');
181 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
182 End Update_Row;
183
184
185 PROCEDURE Lock_Row(
186 p_rowid IN VARCHAR2,
187 p_ship_confirm_rule_info IN ship_confirm_rule_rectype) IS
188
189 CURSOR C_Lock_Row IS
190 SELECT
191 SHIP_CONFIRM_RULE_ID,
192 EFFECTIVE_START_DATE,
193 NAME,
194 EFFECTIVE_END_DATE,
195 ACTION_FLAG,
196 STAGE_DEL_FLAG,
197 SHIP_METHOD_DEFAULT_FLAG,
198 SHIP_METHOD_CODE,
199 AC_ACTUAL_DEP_DATE_DEFAULT,
200 AC_INTRANSIT_FLAG,
201 AC_CLOSE_TRIP_FLAG,
202 AC_BOL_FLAG,
203 AC_DEFER_INTERFACE_FLAG,
204 MC_INTRANSIT_FLAG,
205 MC_CLOSE_TRIP_FLAG,
206 MC_DEFER_INTERFACE_FLAG,
207 MC_BOL_FLAG,
208 REPORT_SET_ID,
209 SEND_945_FLAG,
210 CREATION_DATE,
211 CREATED_BY,
212 LAST_UPDATED_BY,
213 LAST_UPDATE_DATE
214 FROM WSH_SHIP_CONFIRM_RULES
215 WHERE rowid = p_rowid
216 FOR UPDATE OF SHIP_CONFIRM_RULE_ID NOWAIT;
217
218 recinfo C_Lock_Row%ROWTYPE;
219 --
220 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
221 --
222 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_ROW';
223 --
224
225
226 BEGIN
227
228 --
229 IF l_debug_on THEN
230 WSH_DEBUG_SV.push(l_module_name);
231 --
232 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
233 END IF;
234 --
235
236 OPEN C_Lock_Row;
237 FETCH C_Lock_Row INTO recinfo;
238 IF C_Lock_Row%NOTFOUND THEN
239 CLOSE C_Lock_Row;
240 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
241 IF l_debug_on THEN
242
243 WSH_DEBUG_SV.log(l_module_name,'FORM_RECORD_DELETED');
244
245 END IF;
246 app_exception.raise_exception;
247 END IF;
248 CLOSE C_Lock_Row;
249
250 IF
251 (( p_ship_confirm_rule_info.EFFECTIVE_START_DATE = recinfo.EFFECTIVE_START_DATE) or
252 (( p_ship_confirm_rule_info.EFFECTIVE_START_DATE IS NULL) and
253 ( recinfo.EFFECTIVE_START_DATE IS NULL))) and
254 (( p_ship_confirm_rule_info.NAME = recinfo.NAME) or
255 ((p_ship_confirm_rule_info.NAME IS NULL) and
256 ( recinfo.NAME IS NULL))) and
257 (( p_ship_confirm_rule_info.EFFECTIVE_END_DATE = recinfo.EFFECTIVE_END_DATE) or
258 (( p_ship_confirm_rule_info.EFFECTIVE_END_DATE IS NULL) and
259 ( recinfo.EFFECTIVE_END_DATE IS NULL))) and
260 (( p_ship_confirm_rule_info.ACTION_FLAG = recinfo.ACTION_FLAG) or
261 (( p_ship_confirm_rule_info.ACTION_FLAG IS NULL) and
262 ( recinfo.ACTION_FLAG IS NULL))) and
263 (( p_ship_confirm_rule_info.STAGE_DEL_FLAG = recinfo.STAGE_DEL_FLAG) or
264 (( p_ship_confirm_rule_info.STAGE_DEL_FLAG IS NULL) and
265 ( recinfo.STAGE_DEL_FLAG IS NULL))) and
266 (( p_ship_confirm_rule_info.SHIP_METHOD_DEFAULT_FLAG = recinfo.SHIP_METHOD_DEFAULT_FLAG) or
267 (( p_ship_confirm_rule_info.SHIP_METHOD_DEFAULT_FLAG IS NULL) and
268 ( recinfo.SHIP_METHOD_DEFAULT_FLAG IS NULL))) and
269 (( p_ship_confirm_rule_info.SHIP_METHOD_CODE = recinfo.SHIP_METHOD_CODE) or
270 (( p_ship_confirm_rule_info.SHIP_METHOD_CODE IS NULL) and
271 ( recinfo.SHIP_METHOD_CODE IS NULL))) and
272 (( p_ship_confirm_rule_info.AC_ACTUAL_DEP_DATE_DEFAULT = recinfo.AC_ACTUAL_DEP_DATE_DEFAULT) or
273 (( p_ship_confirm_rule_info.AC_ACTUAL_DEP_DATE_DEFAULT IS NULL)and
274 ( recinfo.AC_ACTUAL_DEP_DATE_DEFAULT IS NULL))) and
275 (( p_ship_confirm_rule_info.AC_INTRANSIT_FLAG = recinfo.AC_INTRANSIT_FLAG) or
276 (( p_ship_confirm_rule_info.AC_INTRANSIT_FLAG IS NULL) and
277 ( recinfo.AC_INTRANSIT_FLAG IS NULL))) and
278 (( p_ship_confirm_rule_info.AC_CLOSE_TRIP_FLAG = recinfo.AC_CLOSE_TRIP_FLAG) or
279 (( p_ship_confirm_rule_info.AC_CLOSE_TRIP_FLAG IS NULL) and
280 ( recinfo.AC_CLOSE_TRIP_FLAG IS NULL))) and
281 (( p_ship_confirm_rule_info.AC_BOL_FLAG = recinfo.AC_BOL_FLAG) or
282 (( p_ship_confirm_rule_info.AC_BOL_FLAG IS NULL) and
283 ( recinfo.AC_BOL_FLAG IS NULL))) and
284 (( p_ship_confirm_rule_info.AC_DEFER_INTERFACE_FLAG = recinfo.AC_DEFER_INTERFACE_FLAG) or
285 (( p_ship_confirm_rule_info.AC_DEFER_INTERFACE_FLAG IS NULL) and
286 ( recinfo.AC_DEFER_INTERFACE_FLAG IS NULL))) and
287 (( p_ship_confirm_rule_info.MC_DEFER_INTERFACE_FLAG = recinfo.MC_DEFER_INTERFACE_FLAG) or
288 (( p_ship_confirm_rule_info.MC_DEFER_INTERFACE_FLAG IS NULL) and
289 ( recinfo.MC_DEFER_INTERFACE_FLAG IS NULL))) and
290 (( p_ship_confirm_rule_info.MC_INTRANSIT_FLAG = recinfo.MC_INTRANSIT_FLAG) or
291 (( p_ship_confirm_rule_info.MC_INTRANSIT_FLAG IS NULL) and
292 ( recinfo.MC_INTRANSIT_FLAG IS NULL))) and
293 (( p_ship_confirm_rule_info.MC_CLOSE_TRIP_FLAG = recinfo.MC_CLOSE_TRIP_FLAG) or
294 (( p_ship_confirm_rule_info.MC_CLOSE_TRIP_FLAG IS NULL) and
295 ( recinfo.MC_CLOSE_TRIP_FLAG IS NULL))) and
296 (( p_ship_confirm_rule_info.MC_BOL_FLAG = recinfo.MC_BOL_FLAG) or
297 (( p_ship_confirm_rule_info.MC_BOL_FLAG IS NULL) and
298 ( recinfo.MC_BOL_FLAG IS NULL))) and
299 (( p_ship_confirm_rule_info.REPORT_SET_ID = recinfo.REPORT_SET_ID) or
300 (( p_ship_confirm_rule_info.REPORT_SET_ID IS NULL) and
301 ( recinfo.REPORT_SET_ID IS NULL))) and
302 (( p_ship_confirm_rule_info.SEND_945_FLAG = recinfo.SEND_945_FLAG) or
303 (( p_ship_confirm_rule_info.SEND_945_FLAG IS NULL) and
304 ( recinfo.SEND_945_FLAG IS NULL))) /*and
305 (( p_ship_confirm_rule_info.CREATION_DATE = recinfo.CREATION_DATE) or
306 (( p_ship_confirm_rule_info.CREATION_DATE IS NULL) and
307 ( recinfo.CREATION_DATE IS NULL))) and
308 (( p_ship_confirm_rule_info.CREATED_BY = recinfo.CREATED_BY) or
309 (( p_ship_confirm_rule_info.CREATED_BY IS NULL) and
310 ( recinfo.CREATED_BY IS NULL))) and
311 (( p_ship_confirm_rule_info.LAST_UPDATED_BY = recinfo.LAST_UPDATED_BY) or
312 (( p_ship_confirm_rule_info.LAST_UPDATED_BY IS NULL) and
313 ( recinfo.LAST_UPDATED_BY IS NULL))) and
314 (( p_ship_confirm_rule_info.LAST_UPDATE_DATE = recinfo.LAST_UPDATE_DATE) or
315 (( p_ship_confirm_rule_info.LAST_UPDATE_DATE IS NULL) and
316 ( recinfo.LAST_UPDATE_DATE IS NULL)))*/ THEN
317 --
318 IF l_debug_on THEN
319 WSH_DEBUG_SV.log(l_module_name,'Nothing has changed');
320 WSH_DEBUG_SV.pop(l_module_name);
321 END IF;
322 --
323 RETURN;
324 ELSE
325 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
326 app_exception.raise_exception;
327 END IF;
328 --
329 IF l_debug_on THEN
330 WSH_DEBUG_SV.pop(l_module_name);
331 END IF;
332 --
333 EXCEPTION
334 WHEN app_exception.application_exception or app_exception.record_lock_exception THEN
335
336 if (c_lock_row%ISOPEN) then
337 close c_lock_row;
338 end if;
339
340
341 --
342 IF l_debug_on THEN
343 WSH_DEBUG_SV.logmsg(l_module_name,'APP_EXCEPTION.APPLICATION_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
344 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:APP_EXCEPTION.APPLICATION_EXCEPTION');
345 END IF;
346 --
347 RAISE;
348
349
350 WHEN others THEN
351
352
353 if (c_lock_row%ISOPEN) then
354 close c_lock_row;
355 end if;
356
357 FND_MESSAGE.SET_NAME('WSH','WSH_UNEXP_ERROR');
358 FND_MESSAGE.Set_Token('PACKAGE', 'WSH_SHIP_CONFRIM_RULES_PVT.LOCK_ROW');
359 FND_MESSAGE.Set_Token('ORA_ERROR',sqlcode);
360 FND_MESSAGE.Set_Token('ORA_TEXT',sqlerrm);
361
362 --
363 IF l_debug_on THEN
364 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
365 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
366 END IF;
367 --
368 RAISE;
369
370 --
371 IF l_debug_on THEN
372 WSH_DEBUG_SV.pop(l_module_name);
373 END IF;
374 --
375 END Lock_Row;
376
377
378
379
380
381 PROCEDURE Delete_Row(
382 p_rowid IN VARCHAR2,
383 x_return_status OUT NOCOPY VARCHAR2) IS
384 --
385 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
386 --
387 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '. ' || 'DELETE_ROW';
388 --
389 BEGIN
390
391 --
392 -- Debug Statements
393 --
394 IF l_debug_on THEN
395 WSH_DEBUG_SV.push(l_module_name);
396 --
397 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
398 END IF;
399 --
400 DELETE FROM WSH_SHIP_CONFIRM_RULES
401 WHERE rowid = p_rowid;
402 IF SQL%NOTFOUND THEN
403 RAISE NO_DATA_FOUND;
404 END IF;
405 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
406
407 --
408 -- Debug Statements
409 --
410 IF l_debug_on THEN
411 WSH_DEBUG_SV.pop(l_module_name);
412 END IF;
413 --
414 --
415 EXCEPTION
416 WHEN others THEN
417 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
418 wsh_util_core.default_handler('WSH_SHIP_CONFIRM_RULES_PVT.DELETE_ROW');
419
420 --
421 -- Debug Statements
422 --
423 IF l_debug_on THEN
424 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
425 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
426 END IF;
427 --
428 END Delete_Row;
429
430
431
432
433
434
435 END WSH_SHIP_CONFIRM_RULES_PVT;