[Home] [Help]
PACKAGE BODY: APPS.WSH_ORG_CARRIER_SITES_PKG
Source
1 PACKAGE BODY WSH_ORG_CARRIER_SITES_PKG as
2 /* $Header: WSHOSTHB.pls 115.1 2002/11/13 20:11:27 nparikh noship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_ORG_CARRIER_SITES_PKG';
6 --
7
8 PROCEDURE ASSIGN_ORG_CARRIER_SITE(
9 p_Org_Carrier_Site_info IN OCSRecType
10 , x_Rowid IN OUT NOCOPY VARCHAR2
11 , x_Org_Carrier_site_id IN OUT NOCOPY NUMBER
12 , x_Return_Status OUT NOCOPY VARCHAR2
13 , x_position OUT NOCOPY VARCHAR2
14 , x_procedure OUT NOCOPY VARCHAR2
15 , x_sqlerr OUT NOCOPY VARCHAR2
16 , x_sql_code OUT NOCOPY VARCHAR2
17 , x_exception_msg OUT NOCOPY VARCHAR2 )
18
19 IS
20
21 CURSOR C_Next_id
22 IS
23 SELECT wsh_org_Carrier_sites_s.nextval
24 FROM sys.dual;
25
26
27 CURSOR C_New_Rowid(p_org_carrier_site_id NUMBER)
28 IS
29 SELECT rowid
30 FROM WSH_org_Carrier_sites
31 WHERE org_Carrier_site_id = p_org_Carrier_site_id;
32
33 l_org_carrier_site_id NUMBER;
34 l_rowid rowid;
35 l_disable_date DATE;
36 l_oft_rowid rowid;
37 l_csm_rowid VARCHAR2(40);
38 l_procedure VARCHAR2(500);
39 l_position NUMBER;
40
41 NO_DATA_FOUND EXCEPTION;
42 OTHERS EXCEPTION;
43 l_return_status VARCHAR2(10);
44
45 --
46 l_debug_on BOOLEAN;
47 --
48 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ASSIGN_ORG_CARRIER_SITE';
49 --
50
51 BEGIN
52
53 --
54 --
55 --
56 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
57 --
58 IF l_debug_on IS NULL
59 THEN
60 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
61 END IF;
62 --
63 IF l_debug_on THEN
64 WSH_DEBUG_SV.push(l_module_name);
65 WSH_DEBUG_SV.log(l_module_name,'Carrier_Site_ID',p_org_Carrier_site_Info.Carrier_Site_ID);
66 WSH_DEBUG_SV.log(l_module_name,'Organization_ID',p_org_Carrier_site_Info.Organization_id);
67 WSH_DEBUG_SV.log(l_module_name,'Attribute_Category',p_org_Carrier_site_Info.Attribute_Category);
68 WSH_DEBUG_SV.log(l_module_name,'attribute1',p_org_Carrier_site_Info.attribute1);
69 WSH_DEBUG_SV.log(l_module_name,'attribute2',p_org_Carrier_site_Info.attribute2);
70 WSH_DEBUG_SV.log(l_module_name,'attribute3',p_org_Carrier_site_Info.attribute3);
71 WSH_DEBUG_SV.log(l_module_name,'attribute4',p_org_Carrier_site_Info.attribute4);
72 WSH_DEBUG_SV.log(l_module_name,'attribute5',p_org_Carrier_site_Info.attribute5);
73 WSH_DEBUG_SV.log(l_module_name,'attribute6',p_org_Carrier_site_Info.attribute6);
74 WSH_DEBUG_SV.log(l_module_name,'attribute7',p_org_Carrier_site_Info.attribute7);
75 WSH_DEBUG_SV.log(l_module_name,'attribute8',p_org_Carrier_site_Info.attribute8);
76 WSH_DEBUG_SV.log(l_module_name,'attribute9',p_org_Carrier_site_Info.attribute9);
77 WSH_DEBUG_SV.log(l_module_name,'attribute10',p_org_Carrier_site_Info.attribute10);
78 WSH_DEBUG_SV.log(l_module_name,'attribute11',p_org_Carrier_site_Info.attribute11);
79 WSH_DEBUG_SV.log(l_module_name,'attribute12',p_org_Carrier_site_Info.attribute12);
80 WSH_DEBUG_SV.log(l_module_name,'attribute13',p_org_Carrier_site_Info.attribute13);
81 WSH_DEBUG_SV.log(l_module_name,'attribute14',p_org_Carrier_site_Info.attribute14);
82 WSH_DEBUG_SV.log(l_module_name,'attribute15',p_org_Carrier_site_Info.attribute15);
83 END IF;
84 --
85 --
86
87
88 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
89
90 IF (X_Org_Carrier_site_id is NULL) THEN
91 OPEN C_Next_id;
92 FETCH C_Next_id INTO l_Org_Carrier_site_Id;
93 CLOSE C_Next_id;
94 l_position := 10;
95 l_procedure := 'Inserting into Wsh_org_carrier_sites';
96
97 --
98 -- Debug Statements
99 --
100 IF l_debug_on THEN
101 WSH_DEBUG_SV.logmsg(l_module_name,'Inserting into WSH_ORG_CARRIER_SITES',WSH_DEBUG_SV.C_PROC_LEVEL);
102 END IF;
103 --
104
105 INSERT INTO WSH_ORG_CARRIER_SITES
106 ( org_Carrier_site_id,
107 carrier_site_id,
108 organization_id,
109 enabled_flag,
110 attribute_category,
111 attribute1,
112 attribute2,
113 attribute3,
114 attribute4,
115 attribute5,
116 attribute6,
117 attribute7,
118 attribute8,
119 attribute9,
120 attribute10,
121 attribute11,
122 attribute12,
123 attribute13,
124 attribute14,
125 attribute15,
126 creation_date,
127 created_by,
128 last_update_date,
129 last_updated_by,
130 last_update_login
131 ) VALUES (
132 l_org_Carrier_Site_id,
133 p_org_Carrier_site_Info.carrier_site_id,
134 p_org_Carrier_site_Info.organization_id,
135 p_org_Carrier_site_Info.Enabled_Flag,
136 p_org_Carrier_site_Info.Attribute_Category,
137 p_org_Carrier_site_Info.Attribute1,
138 p_org_Carrier_site_Info.Attribute2,
139 p_org_Carrier_site_Info.Attribute3,
140 p_org_Carrier_site_Info.Attribute4,
141 p_org_Carrier_site_Info.Attribute5,
142 p_org_Carrier_site_Info.Attribute6,
143 p_org_Carrier_site_Info.Attribute7,
144 p_org_Carrier_site_Info.Attribute8,
145 p_org_Carrier_site_Info.Attribute9,
146 p_org_Carrier_site_Info.Attribute10,
147 p_org_Carrier_site_Info.Attribute11,
148 p_org_Carrier_site_Info.Attribute12,
149 p_org_Carrier_site_Info.Attribute13,
150 p_org_Carrier_site_Info.Attribute14,
151 p_org_Carrier_site_Info.Attribute15,
152 sysdate,
153 fnd_global.user_id,
154 sysdate,
155 fnd_global.user_id,
156 fnd_global.login_id);
157
158 OPEN C_New_Rowid(l_org_carrier_site_id);
159 FETCH C_New_Rowid INTO l_rowid;
160 IF (C_New_Rowid%NOTFOUND) THEN
161 CLOSE C_New_Rowid;
162 RAISE others;
163 END IF;
164
165 x_org_carrier_site_id := l_org_carrier_site_id;
166 x_rowid := l_rowid;
167 ELSE
168
169 L_POSITION := 20;
170 L_PROCEDURE := 'Updating WSH_ORG_CARRIER_SITES';
171
172 --
173 -- Debug Statements
174 --
175 IF l_debug_on THEN
176 WSH_DEBUG_SV.logmsg(l_module_name,'Updating WSH_ORG_CARRIER_SITES',WSH_DEBUG_SV.C_PROC_LEVEL);
177 END IF;
178 --
179
180 UPDATE WSH_ORG_CARRIER_SITES
181 SET
182 enabled_flag = p_org_Carrier_site_Info.Enabled_Flag,
183 attribute_category = p_org_Carrier_site_Info.Attribute_Category,
184 attribute1 = p_org_Carrier_site_Info.Attribute1,
185 attribute2 = p_org_Carrier_site_Info.Attribute2,
186 attribute3 = p_org_Carrier_site_Info.Attribute3,
187 attribute4 = p_org_Carrier_site_Info.Attribute4,
188 attribute5 = p_org_Carrier_site_Info.Attribute5,
189 attribute6 = p_org_Carrier_site_Info.Attribute6,
190 attribute7 = p_org_Carrier_site_Info.Attribute7,
191 attribute8 = p_org_Carrier_site_Info.Attribute8,
192 attribute9 = p_org_Carrier_site_Info.Attribute9,
193 attribute10 = p_org_Carrier_site_Info.Attribute10,
194 attribute11 = p_org_Carrier_site_Info.Attribute11,
195 attribute12 = p_org_Carrier_site_Info.Attribute12,
196 attribute13 = p_org_Carrier_site_Info.Attribute13,
197 attribute14 = p_org_Carrier_site_Info.Attribute14,
198 attribute15 = p_org_Carrier_site_Info.Attribute15,
199 last_update_date = sysdate,
200 last_updated_by = fnd_global.user_id,
201 last_update_login = fnd_global.login_id
202 WHERE rowid = x_rowid;
203
204 IF (SQL%NOTFOUND) THEN
205 RAISE NO_DATA_FOUND;
206 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
207 END IF;
208
209 END IF;
210
211 COMMIT;
212
213 --
214 -- Debug Statements
215 --
216 IF l_debug_on THEN
217 WSH_DEBUG_SV.pop(l_module_name);
218 END IF;
219 --
220
221 EXCEPTION
222 WHEN NO_DATA_FOUND THEN
223 x_exception_msg := 'EXCEPTION : No Data Found';
224 x_position := l_position;
225 x_procedure := l_procedure;
226 x_sqlerr := sqlerrm;
227 x_sql_code := sqlcode;
228 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
229
230 --
231 -- Debug Statements
232 --
233 IF l_debug_on THEN
234 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
235 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
236 END IF;
237 --
238
239 WHEN OTHERS THEN
240 x_exception_msg := 'EXCEPTION : Others';
241 x_position := l_position;
242 x_procedure := l_procedure;
243 x_sqlerr := sqlerrm;
244 x_sql_code := sqlcode;
245 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
246
247 --
248 -- Debug Statements
249 --
250 IF l_debug_on THEN
251 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
252 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
253 END IF;
254 --
255
256 END Assign_Org_Carrier_Site;
257
258 PROCEDURE Lock_Org_Carrier_Site (
259 p_rowid IN VARCHAR2
260 , p_org_Carrier_site_Info IN OCSRecType
261 , x_Return_Status OUT NOCOPY VARCHAR2
262 )
263 IS
264
265 CURSOR C_lock_row IS
266 SELECT *
267 FROM wsh_Org_Carrier_sites
268 WHERE rowid = p_rowid
269 FOR UPDATE of Org_Carrier_site_id NOWAIT;
270
271 Recinfo C_lock_row%ROWTYPE;
272
273 others Exception;
274
275 --
276 l_debug_on BOOLEAN;
277 --
278 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_ORG_CARRIER_SITE';
279 --
280
281 BEGIN
282
283 --
284 --
285 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
286 --
287 IF l_debug_on IS NULL
288 THEN
289 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
290 END IF;
291 --
292 IF l_debug_on THEN
293 WSH_DEBUG_SV.push(l_module_name);
294 --
295 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
296 END IF;
297 --
298
299 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
300
301 IF (p_rowid is not null) THEN
302 OPEN C_lock_row;
303 FETCH C_lock_row INTO Recinfo;
304
305 IF (C_lock_row%NOTFOUND) THEN
306 CLOSE C_lock_row;
307 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
308 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
309 WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
310
311 --
312 IF l_debug_on THEN
313 WSH_DEBUG_SV.pop(l_module_name,'x_return_status');
314 END IF;
315 --
316 RETURN;
317 END IF;
318
319 CLOSE C_lock_row;
320
321 IF ( (Recinfo.Enabled_Flag = p_org_Carrier_site_Info.Enabled_Flag)
322 AND ( (Recinfo.Attribute_Category = p_org_Carrier_site_Info.Attribute_Category)
323 OR ( (Recinfo.Attribute_Category is NULL)
324 AND (p_org_Carrier_site_Info.Attribute_Category IS NULL)))
325 AND ( (Recinfo.Attribute1 = p_org_Carrier_site_Info.Attribute1)
326 OR ( (Recinfo.Attribute1 IS NULL)
327 AND (p_org_Carrier_site_Info.Attribute1 is NULL)))
328 AND ( (Recinfo.Attribute2 = p_org_Carrier_site_Info.Attribute2)
329 OR ( (Recinfo.Attribute2 IS NULL)
330 AND (p_org_Carrier_site_Info.Attribute2 is NULL)))
331 AND ( (Recinfo.Attribute3 = p_org_Carrier_site_Info.Attribute3)
332 OR ( (Recinfo.Attribute3 IS NULL)
333 AND (p_org_Carrier_site_Info.Attribute3 is NULL)))
334 AND ( (Recinfo.Attribute4 = p_org_Carrier_site_Info.Attribute4)
335 OR ( (Recinfo.Attribute4 IS NULL)
336 AND (p_org_Carrier_site_Info.Attribute4 is NULL)))
337 AND ( (Recinfo.Attribute5 = p_org_Carrier_site_Info.Attribute5)
338 OR ( (Recinfo.Attribute5 IS NULL)
339 AND (p_org_Carrier_site_Info.Attribute5 is NULL)))
340 AND ( (Recinfo.Attribute6 = p_org_Carrier_site_Info.Attribute6)
341 OR ( (Recinfo.Attribute6 IS NULL)
342 AND (p_org_Carrier_site_Info.Attribute6 is NULL)))
343 AND ( (Recinfo.Attribute7 = p_org_Carrier_site_Info.Attribute7)
344 OR ( (Recinfo.Attribute7 IS NULL)
345 AND (p_org_Carrier_site_Info.Attribute7 is NULL)))
346 AND ( (Recinfo.Attribute8 = p_org_Carrier_site_Info.Attribute8)
347 OR ( (Recinfo.Attribute8 IS NULL)
348 AND (p_org_Carrier_site_Info.Attribute8 is NULL)))
349 AND ( (Recinfo.Attribute9 = p_org_Carrier_site_Info.Attribute9)
350 OR ( (Recinfo.Attribute9 IS NULL)
351 AND (p_org_Carrier_site_Info.Attribute9 is NULL)))
352 AND ( (Recinfo.Attribute10 = p_org_Carrier_site_Info.Attribute10)
353 OR ( (Recinfo.Attribute10 IS NULL)
354 AND (p_org_Carrier_site_Info.Attribute10 is NULL)))
355 AND ( (Recinfo.Attribute11 = p_org_Carrier_site_Info.Attribute11)
356 OR ( (Recinfo.Attribute11 IS NULL)
357 AND (p_org_Carrier_site_Info.Attribute11 is NULL)))
358 AND ( (Recinfo.Attribute12 = p_org_Carrier_site_Info.Attribute12)
359 OR ( (Recinfo.Attribute12 IS NULL)
360 AND (p_org_Carrier_site_Info.Attribute12 is NULL)))
361 AND ( (Recinfo.Attribute13 = p_org_Carrier_site_Info.Attribute13)
362 OR ( (Recinfo.Attribute13 IS NULL)
363 AND (p_org_Carrier_site_Info.Attribute13 is NULL)))
364 AND ( (Recinfo.Attribute14 = p_org_Carrier_site_Info.Attribute14)
365 OR ( (Recinfo.Attribute14 IS NULL)
366 AND (p_org_Carrier_site_Info.Attribute14 is NULL)))
367 AND ( (Recinfo.Attribute15 = p_org_Carrier_site_Info.Attribute15)
368 OR ( (Recinfo.Attribute15 IS NULL)
369 AND (p_org_Carrier_site_Info.Attribute15 is NULL)))
370 ) THEN
371
372 --
373 IF l_debug_on THEN
374 WSH_DEBUG_SV.pop(l_module_name,'Nothing changed');
375 END IF;
376 --
377 RETURN;
378
379 ELSE
380
381 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
382
383 IF l_debug_on THEN
384 WSH_DEBUG_SV.log(l_module_name,'FORM_RECORD_CHANGED');
385 END IF;
386
387 APP_EXCEPTION.Raise_Exception;
388 END IF;
389 END IF;
390
391 --
392 IF l_debug_on THEN
393 WSH_DEBUG_SV.pop(l_module_name);
394 END IF;
395 --
396
397 EXCEPTION
398 WHEN others THEN
399 x_Return_Status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
400 WSH_UTIL_CORE.Default_Handler('WSH_ORG_CARRIER_SITES_PKG.Lock_Org_Carrier_Site',l_module_name);
401
402 --
403 IF l_debug_on THEN
404 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
405 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
406 END IF;
407 --
408
409 END Lock_Org_Carrier_Site;
410
411 END WSH_ORG_CARRIER_SITES_PKG;