[Home] [Help]
PACKAGE BODY: APPS.WSH_OE_CONSTRAINTS
Source
1 PACKAGE BODY WSH_OE_CONSTRAINTS as
2 /* $Header: WSHOECOB.pls 115.4 2002/11/13 20:10:50 nparikh ship $ */
3
4 G_PKG_NAME VARCHAR2(100) := 'WSH_OE_CONSTRAINTS';
5
6
7
8 PROCEDURE Validate_Reservations
9 (
10 p_application_id IN NUMBER
11 , p_entity_short_name IN VARCHAR2
12 , p_validation_entity_short_name IN VARCHAR2
13 , p_validation_tmplt_short_name IN VARCHAR2
14 , p_record_set_short_name IN VARCHAR2
15 , p_scope IN VARCHAR2
16 , x_result_out OUT NOCOPY NUMBER
17 ) IS
18
19 -- Find out if we have delivery details
20 -- that require checking the reservations.
21 CURSOR c_details(x_line_id IN NUMBER) IS
22 SELECT released_status
23 FROM wsh_delivery_details
24 WHERE source_code = 'OE'
25 AND source_line_id = x_line_id
26 AND released_status IN ('S', 'Y', 'X')
27 AND rownum = 1;
28
29 -- Check the reservations
30 CURSOR c_reservations(x_line_id IN NUMBER) IS
31 SELECT staged_flag
32 FROM mtl_reservations
33 WHERE demand_source_line_id = x_line_id;
34
35 l_details c_details%ROWTYPE;
36 l_reservations c_reservations%ROWTYPE;
37 l_cms_profile VARCHAR2(100);
38
39 lc_allowed CONSTANT NUMBER := 0;
40 lc_disallowed CONSTANT NUMBER := 1;
41
42 --
43 l_debug_on BOOLEAN;
44 --
45 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_RESERVATIONS';
46 --
47 BEGIN
48
49 -- need to check that patchset level is pre-G.
50 --
51 -- Debug Statements
52 --
53 --
54 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
55 --
56 IF l_debug_on IS NULL
57 THEN
58 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
59 END IF;
60 --
61 IF l_debug_on THEN
62 WSH_DEBUG_SV.push(l_module_name);
63 --
64 WSH_DEBUG_SV.log(l_module_name,'P_APPLICATION_ID',P_APPLICATION_ID);
65 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_SHORT_NAME',P_ENTITY_SHORT_NAME);
66 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_ENTITY_SHORT_NAME',P_VALIDATION_ENTITY_SHORT_NAME);
67 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_TMPLT_SHORT_NAME',P_VALIDATION_TMPLT_SHORT_NAME);
68 WSH_DEBUG_SV.log(l_module_name,'P_RECORD_SET_SHORT_NAME',P_RECORD_SET_SHORT_NAME);
69 WSH_DEBUG_SV.log(l_module_name,'P_SCOPE',P_SCOPE);
70 END IF;
71 --
72 fnd_profile.get('ONT_ACTIVATE_CMS', l_cms_profile);
73 IF l_cms_profile = 'Y' THEN
74 x_result_out := lc_allowed;
75 --
76 -- Debug Statements
77 --
78 IF l_debug_on THEN
79 WSH_DEBUG_SV.log(l_module_name,'X_RESULT_OUT',x_result_out);
80 WSH_DEBUG_SV.pop(l_module_name);
81 END IF;
82 --
83 RETURN;
84 END IF;
85
86
87 -- Full cancellation?
88 IF OE_LINE_SECURITY.g_record.ordered_quantity = 0 THEN
89 -- allow full cancellation
90 x_result_out := lc_allowed;
91 --
92 -- Debug Statements
93 --
94 IF l_debug_on THEN
95 WSH_DEBUG_SV.log(l_module_name,'X_RESULT_OUT',x_result_out);
96 WSH_DEBUG_SV.pop(l_module_name);
97 END IF;
98 --
99 RETURN;
100 END IF;
101
102
103
104 OPEN c_details(OE_LINE_SECURITY.g_record.line_id);
105 FETCH c_details INTO l_details;
106
107 IF c_details%NOTFOUND THEN
108 -- OK to do the changes if nothing is found
109 CLOSE c_details;
110 x_result_out := lc_allowed;
111 --
112 -- Debug Statements
113 --
114 IF l_debug_on THEN
115 WSH_DEBUG_SV.log(l_module_name,'X_RESULT_OUT',x_result_out);
116 WSH_DEBUG_SV.pop(l_module_name);
117 END IF;
118 --
119 RETURN;
120 END IF;
121
122 CLOSE c_details;
123
124
125
126 OPEN c_reservations(OE_LINE_SECURITY.g_record.line_id);
127 FETCH c_reservations INTO l_reservations;
128
129 IF c_reservations%NOTFOUND THEN
130 -- OK to do the changes because there are no reservations
131 x_result_out := lc_allowed;
132 ELSE
133 -- Make sure there is only one reservation record.
134 FETCH c_reservations INTO l_reservations;
135 IF c_reservations%NOTFOUND THEN
136 x_result_out := lc_allowed;
137 ELSE
138 x_result_out := lc_disallowed;
139 END IF;
140 END IF;
141
142 CLOSE c_reservations;
143
144
145 --
146 -- Debug Statements
147 --
148 IF l_debug_on THEN
149 WSH_DEBUG_SV.log(l_module_name,'X_RESULT_OUT',x_result_out);
150 WSH_DEBUG_SV.pop(l_module_name);
151 END IF;
152 --
153 EXCEPTION
154 WHEN OTHERS THEN
155 --
156 -- Debug Statements
157 --
158 IF l_debug_on THEN
159 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit OE_MSG_PUB.CHECK_MSG_LEVEL',WSH_DEBUG_SV.C_PROC_LEVEL);
160 END IF;
161 --
162 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
163 THEN
164 --
165 -- Debug Statements
166 --
167 IF l_debug_on THEN
168 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit OE_MSG_PUB.ADD_EXC_MSG',WSH_DEBUG_SV.C_PROC_LEVEL);
169 END IF;
170 --
171 OE_MSG_PUB.Add_Exc_Msg
172 ( G_PKG_NAME
173 , 'Validate_Reservations'
174 );
175 END IF;
176 --
177 -- Debug Statements
178 --
179 IF l_debug_on THEN
180 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 100 ) );
181 END IF;
182 --
183 IF c_details%ISOPEN THEN
184 CLOSE c_details;
185 END IF;
186 IF c_reservations%ISOPEN THEN
187 CLOSE c_reservations;
188 END IF;
189
190 --
191 -- Debug Statements
192 --
193 IF l_debug_on THEN
194 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
195 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
196 END IF;
197 --
198 END Validate_Reservations;
199
200
201
202 PROCEDURE Validate_Sub_Change
203 (
204 p_application_id IN NUMBER
205 , p_entity_short_name IN VARCHAR2
206 , p_validation_entity_short_name IN VARCHAR2
207 , p_validation_tmplt_short_name IN VARCHAR2
208 , p_record_set_short_name IN VARCHAR2
209 , p_scope IN VARCHAR2
210 , x_result_out OUT NOCOPY NUMBER
211 ) IS
212
213 -- Find out if we have delivery details
214 -- that require checking the reservations.
215 CURSOR c_details(x_line_id IN NUMBER) IS
216 SELECT released_status
217 FROM wsh_delivery_details
218 WHERE source_code = 'OE'
219 AND source_line_id = x_line_id
220 AND released_status IN ('S', 'Y', 'C')
221 AND rownum = 1;
222
223
224 l_details c_details%ROWTYPE;
225
226 l_cms_profile VARCHAR2(100);
227
228 lc_allowed CONSTANT NUMBER := 0;
229 lc_disallowed CONSTANT NUMBER := 1;
230
231 --
232 l_debug_on BOOLEAN;
233 --
234 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_SUB_CHANGE';
235 --
236 BEGIN
237 --
238 -- Debug Statements
239 --
240 --
241 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
242 --
243 IF l_debug_on IS NULL
244 THEN
245 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
246 END IF;
247 --
248 IF l_debug_on THEN
249 WSH_DEBUG_SV.push(l_module_name);
250 --
251 WSH_DEBUG_SV.log(l_module_name,'P_APPLICATION_ID',P_APPLICATION_ID);
252 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_SHORT_NAME',P_ENTITY_SHORT_NAME);
253 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_ENTITY_SHORT_NAME',P_VALIDATION_ENTITY_SHORT_NAME);
254 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_TMPLT_SHORT_NAME',P_VALIDATION_TMPLT_SHORT_NAME);
255 WSH_DEBUG_SV.log(l_module_name,'P_RECORD_SET_SHORT_NAME',P_RECORD_SET_SHORT_NAME);
256 WSH_DEBUG_SV.log(l_module_name,'P_SCOPE',P_SCOPE);
257 END IF;
258 --
259 x_result_out := lc_disallowed;
260
261 -- need to check that patchset level is pre-G.
262 fnd_profile.get('ONT_ACTIVATE_CMS', l_cms_profile);
263 IF l_cms_profile = 'Y' THEN
264 x_result_out := lc_allowed;
265 --
266 -- Debug Statements
267 --
268 IF l_debug_on THEN
269 WSH_DEBUG_SV.log(l_module_name,'X_RESULT_OUT',x_result_out);
270 WSH_DEBUG_SV.pop(l_module_name);
271 END IF;
272 --
273 RETURN;
274 END IF;
275
276
277 OPEN c_details(OE_LINE_SECURITY.g_record.line_id);
278 FETCH c_details INTO l_details;
279
280 IF c_details%NOTFOUND THEN
281 -- OK to do the changes if nothing is found
282 CLOSE c_details;
283 x_result_out := lc_allowed;
284 --
285 -- Debug Statements
286 --
287 IF l_debug_on THEN
288 WSH_DEBUG_SV.log(l_module_name,'X_RESULT_OUT',x_result_out);
289 WSH_DEBUG_SV.pop(l_module_name);
290 END IF;
291 --
292 RETURN;
293 END IF;
294
295 CLOSE c_details;
296
297
298 --
299 -- Debug Statements
300 --
301 IF l_debug_on THEN
302 WSH_DEBUG_SV.log(l_module_name,'X_RESULT_OUT',x_result_out);
303 WSH_DEBUG_SV.pop(l_module_name);
304 END IF;
305 --
306 EXCEPTION
307 WHEN OTHERS THEN
308 --
309 -- Debug Statements
310 --
311 IF l_debug_on THEN
312 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit OE_MSG_PUB.CHECK_MSG_LEVEL',WSH_DEBUG_SV.C_PROC_LEVEL);
313 END IF;
314 --
315 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
316 THEN
317 --
318 -- Debug Statements
319 --
320 IF l_debug_on THEN
321 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit OE_MSG_PUB.ADD_EXC_MSG',WSH_DEBUG_SV.C_PROC_LEVEL);
322 END IF;
323 --
324 OE_MSG_PUB.Add_Exc_Msg
325 ( G_PKG_NAME
326 , 'Validate_Sub_Change'
327 );
328 END IF;
329 --
330 -- Debug Statements
331 --
332 IF l_debug_on THEN
333 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 100 ) );
334 END IF;
335 --
336
337 IF c_details%ISOPEN THEN
338 CLOSE c_details;
339 END IF;
340
341
342 --
343 -- Debug Statements
344 --
345 IF l_debug_on THEN
346 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
347 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
348 END IF;
349 --
350 END Validate_Sub_Change;
351
352
353 END WSH_OE_CONSTRAINTS;