[Home] [Help]
PACKAGE BODY: APPS.WMS_CROSSDOCK_GRP
Source
1 PACKAGE BODY wms_crossdock_grp AS
2 /* $Header: WMSXDGRB.pls 120.4 2005/07/06 14:13:33 stdavid noship $ */
3
4 g_pkg_body_ver CONSTANT VARCHAR2(100) := '$Header: WMSXDGRB.pls 120.4 2005/07/06 14:13:33 stdavid noship $';
5 g_newline CONSTANT VARCHAR2(10) := fnd_global.newline;
6
7
8
9 PROCEDURE print_debug
10 ( p_msg IN VARCHAR2
11 , p_api_name IN VARCHAR2
12 ) IS
13 BEGIN
14 inv_log_util.trace
15 ( p_message => p_msg
16 , p_module => g_pkg_name || '.' || p_api_name
17 , p_level => 4
18 );
19 END print_debug;
20
21
22
23 PROCEDURE print_version_info
24 IS
25 BEGIN
26 print_debug ('Spec:: ' || g_pkg_spec_ver, 'print_version_info');
27 print_debug ('Body:: ' || g_pkg_body_ver, 'print_version_info');
28 END print_version_info;
29
30
31
32 PROCEDURE validate_planxdock_crt_id
33 ( x_return_status OUT NOCOPY VARCHAR2
34 , p_criterion_id IN NUMBER
35 ) IS
36
37 l_api_name VARCHAR2(30);
38 l_debug NUMBER;
39 l_dummy NUMBER;
40 l_msg_count NUMBER;
41 l_msg_data VARCHAR2(2000);
42
43 CURSOR c_check_if_planned_criterion
44 ( p_crt_id IN NUMBER
45 ) IS
46 SELECT wcc.criterion_id
47 FROM wms_crossdock_criteria wcc
48 WHERE wcc.criterion_id = p_crt_id
49 AND wcc.criterion_type = wms_xdock_utils_pvt.G_CRT_TYPE_PLAN;
50
51 BEGIN
52 l_api_name := 'validate_planxdock_crt_id';
53 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
54 x_return_status := fnd_api.g_ret_sts_success;
55
56 print_version_info;
57
58 IF l_debug = 1 THEN
59 print_debug
60 ( 'Entered with parameters: ' || g_newline ||
61 'p_criterion_id => ' || to_char(p_criterion_id)
62 , l_api_name
63 );
64 END IF;
65
66 OPEN c_check_if_planned_criterion (p_criterion_id);
67 FETCH c_check_if_planned_criterion INTO l_dummy;
68
69 IF c_check_if_planned_criterion%NOTFOUND
70 THEN
71 IF l_debug = 1 THEN
72 print_debug('Criterion ID not found', l_api_name);
73 END IF;
74 fnd_message.set_name('WMS', 'WMS_XDCRT_INVLD_PLAN_CRT');
75 fnd_msg_pub.ADD;
76 RAISE fnd_api.g_exc_error;
77 END IF;
78
79 CLOSE c_check_if_planned_criterion;
80
81 EXCEPTION
82 WHEN fnd_api.g_exc_error THEN
83 x_return_status := fnd_api.g_ret_sts_error;
84
85 IF c_check_if_planned_criterion%ISOPEN THEN
86 CLOSE c_check_if_planned_criterion;
87 END IF;
88
89 fnd_msg_pub.count_and_get
90 ( p_count => l_msg_count
91 , p_data => l_msg_data
92 , p_encoded => fnd_api.g_false
93 );
94
95 IF l_debug = 1 THEN
96 print_debug (l_msg_data, l_api_name);
97 END IF;
98
99 WHEN OTHERS THEN
100 x_return_status := fnd_api.g_ret_sts_unexp_error;
101
102 IF c_check_if_planned_criterion%ISOPEN THEN
103 CLOSE c_check_if_planned_criterion;
104 END IF;
105
106 IF l_debug = 1 THEN
107 print_debug ('Other error: ' || sqlerrm, l_api_name);
108 END IF;
109
110 END validate_planxdock_crt_id;
111
112
113
114 PROCEDURE chk_planxd_crt_id_name
115 ( x_return_status OUT NOCOPY VARCHAR2
116 , p_criterion_id IN OUT NOCOPY NUMBER
117 , p_criterion_name IN OUT NOCOPY VARCHAR2
118 ) IS
119
120 l_api_name VARCHAR2(30);
121 l_debug NUMBER;
122 l_msg_count NUMBER;
123 l_msg_data VARCHAR2(2000);
124
125 l_crt_id NUMBER;
126 l_crt_name VARCHAR2(80);
127
128 CURSOR c_get_plan_crt_name
129 ( p_crt_id IN NUMBER
130 ) IS
131 SELECT wccv.criterion_name
132 FROM wms_crossdock_criteria_vl wccv
133 WHERE wccv.criterion_id = p_crt_id
134 AND wccv.criterion_type = wms_xdock_utils_pvt.G_CRT_TYPE_PLAN;
135
136 CURSOR c_get_plan_crt_id
137 ( p_crt_name IN VARCHAR2
138 ) IS
139 SELECT wccv.criterion_id
140 FROM wms_crossdock_criteria_vl wccv
141 WHERE wccv.criterion_name = p_crt_name
142 AND wccv.criterion_type = wms_xdock_utils_pvt.G_CRT_TYPE_PLAN;
143
144 BEGIN
145 l_api_name := 'chk_planxd_crt_id_name';
146 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
147 x_return_status := fnd_api.g_ret_sts_success;
148
149 print_version_info;
150
151 IF l_debug = 1 THEN
152 print_debug
153 ( 'Entered with parameters: ' || g_newline ||
154 'p_criterion_id => ' || to_char(p_criterion_id) ||
155 'p_criterion_name => ' || p_criterion_name
156 , l_api_name
157 );
158 END IF;
159
160 IF p_criterion_id IS NOT NULL
161 THEN
162 OPEN c_get_plan_crt_name (p_criterion_id);
163 FETCH c_get_plan_crt_name INTO l_crt_name;
164 IF c_get_plan_crt_name%NOTFOUND
165 THEN
166 IF l_debug = 1 THEN
167 print_debug('Criterion ID not found', l_api_name);
168 END IF;
169 fnd_message.set_name('WMS', 'WMS_XDCRT_INVLD_PLAN_CRT');
170 fnd_msg_pub.ADD;
171 RAISE fnd_api.g_exc_error;
172 END IF;
173 CLOSE c_get_plan_crt_name;
174 p_criterion_name := l_crt_name;
175 ELSIF p_criterion_name IS NOT NULL
176 THEN
177 OPEN c_get_plan_crt_id (p_criterion_name);
178 FETCH c_get_plan_crt_id INTO l_crt_id;
179 IF c_get_plan_crt_id%NOTFOUND
180 THEN
181 IF l_debug = 1 THEN
182 print_debug('Criterion name not found', l_api_name);
183 END IF;
184 fnd_message.set_name('WMS', 'WMS_XDCRT_INVLD_PLAN_CRT');
185 fnd_msg_pub.ADD;
186 RAISE fnd_api.g_exc_error;
187 END IF;
188 CLOSE c_get_plan_crt_id;
189 p_criterion_id := l_crt_id;
190 END IF;
191
192 EXCEPTION
193 WHEN fnd_api.g_exc_error THEN
194 x_return_status := fnd_api.g_ret_sts_error;
195
196 IF c_get_plan_crt_name%ISOPEN THEN
197 CLOSE c_get_plan_crt_name;
198 END IF;
199
200 IF c_get_plan_crt_id%ISOPEN THEN
201 CLOSE c_get_plan_crt_id;
202 END IF;
203
204 fnd_msg_pub.count_and_get
205 ( p_count => l_msg_count
206 , p_data => l_msg_data
207 , p_encoded => fnd_api.g_false
208 );
209
210 IF l_debug = 1 THEN
211 print_debug (l_msg_data, l_api_name);
212 END IF;
213
214 WHEN OTHERS THEN
215 x_return_status := fnd_api.g_ret_sts_unexp_error;
216
217 IF c_get_plan_crt_name%ISOPEN THEN
218 CLOSE c_get_plan_crt_name;
219 END IF;
220
221 IF c_get_plan_crt_id%ISOPEN THEN
222 CLOSE c_get_plan_crt_id;
223 END IF;
224
225 IF l_debug = 1 THEN
226 print_debug ('Other error: ' || sqlerrm, l_api_name);
227 END IF;
228
229 END chk_planxd_crt_id_name;
230
231
232
233 PROCEDURE chk_mo_type
234 ( x_return_status OUT NOCOPY VARCHAR2
235 , x_mo_header_id OUT NOCOPY NUMBER
236 , x_mo_type OUT NOCOPY NUMBER
237 , x_is_putaway_mo OUT NOCOPY VARCHAR2
238 , p_mo_line_id IN NUMBER
239 ) IS
240
241 l_api_name VARCHAR2(30);
242 l_debug NUMBER;
243 l_msg_count NUMBER;
244 l_msg_data VARCHAR2(2000);
245
246 l_mo_header_id NUMBER;
247 l_mo_type NUMBER;
248
249 CURSOR c_get_mo_header_info
250 ( p_line_id IN NUMBER
251 ) IS
252 SELECT mtrh.header_id
253 , mtrh.move_order_type
254 FROM mtl_txn_request_lines mtrl
255 , mtl_txn_request_headers mtrh
256 WHERE mtrl.line_id = p_line_id
257 AND mtrh.header_id = mtrl.header_id;
258
259 BEGIN
260 l_api_name := 'chk_mo_type';
261 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
262 x_return_status := fnd_api.g_ret_sts_success;
263
264 print_version_info;
265
266 IF l_debug = 1 THEN
267 print_debug
268 ( 'Entered with parameters: ' || g_newline ||
269 'p_mo_line_id => ' || to_char(p_mo_line_id)
270 , l_api_name
271 );
272 END IF;
273
274 IF p_mo_line_id IS NULL
275 THEN
276 print_debug('MO line ID is null!', l_api_name);
277 RAISE fnd_api.g_exc_unexpected_error;
278 END IF;
279
280 OPEN c_get_mo_header_info(p_mo_line_id);
281 FETCH c_get_mo_header_info INTO l_mo_header_id,l_mo_type;
282 IF c_get_mo_header_info%NOTFOUND
283 THEN
284 IF l_debug = 1 THEN
285 print_debug('MO line ID not found', l_api_name);
286 END IF;
287 fnd_message.set_name('WMS', 'WMS_MOL_NOT_FOUND');
288 fnd_msg_pub.ADD;
289 RAISE fnd_api.g_exc_error;
290 END IF;
291 CLOSE c_get_mo_header_info;
292
293 x_mo_header_id := l_mo_header_id;
294 x_mo_type := l_mo_type;
295 IF l_mo_type = INV_GLOBALS.g_move_order_put_away
296 THEN
297 x_is_putaway_mo := 'Y';
298 ELSE
299 x_is_putaway_mo := 'N';
300 END IF;
301
302 EXCEPTION
303 WHEN fnd_api.g_exc_error THEN
304 x_return_status := fnd_api.g_ret_sts_error;
305
306 IF c_get_mo_header_info%ISOPEN THEN
307 CLOSE c_get_mo_header_info;
308 END IF;
309
310 fnd_msg_pub.count_and_get
311 ( p_count => l_msg_count
312 , p_data => l_msg_data
313 , p_encoded => fnd_api.g_false
314 );
315
316 IF l_debug = 1 THEN
317 print_debug (l_msg_data, l_api_name);
318 END IF;
319
320 WHEN OTHERS THEN
321 x_return_status := fnd_api.g_ret_sts_unexp_error;
322
323 IF c_get_mo_header_info%ISOPEN THEN
324 CLOSE c_get_mo_header_info;
325 END IF;
326
327 IF l_debug = 1 THEN
328 print_debug ('Other error: ' || sqlerrm, l_api_name);
329 END IF;
330
331 END chk_mo_type;
332
333 END wms_crossdock_grp;