[Home] [Help]
PACKAGE BODY: APPS.WSH_RLM_INTERFACE
Source
1 PACKAGE BODY WSH_RLM_INTERFACE as
2 /* $Header: WSHRLMIB.pls 120.2.12000000.2 2007/04/09 10:15:31 sunilku ship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_RLM_INTERFACE';
6
7 g_col_tab RLM_CORE_SV.t_dynamic_tab;
8
9 PROCEDURE BuildQuery(
10 p_customer_id IN NUMBER,
11 p_ship_to_location_id IN NUMBER,
12 p_intmed_ship_to_org_id IN NUMBER,--Bugfix 5911991
13 p_ship_from_location_id IN NUMBER,
14 p_inventory_item_id IN NUMBER,
15 p_customer_item_id IN NUMBER,
16 p_order_header_id IN NUMBER,
17 p_blanket_number IN NUMBER,
18 p_org_id IN NUMBER,
19 p_schedule_type IN VARCHAR2,
20 p_match_within_rule IN RLM_CORE_SV.t_match_rec,
21 p_match_across_rule IN RLM_CORE_SV.t_match_rec,
22 p_optional_match_rec IN t_optional_match_rec,
23 x_c_transit_detail OUT NOCOPY VARCHAR2)
24 IS
25 v_select_clause VARCHAR2(32000);
26 v_where_clause VARCHAR2(32000);
27 v_request_date_str VARCHAR2(30);
28 v_schedule_date_str VARCHAR2(30);
29 BEGIN
30 --
31 rlm_core_sv.dlog('Entering WSH_RLM_INTERFACE.BuildQuery');
32 --
33 -- Starting where-clause
34
35 v_where_clause :=
36
37 'WHERE s.stop_id = dl.pick_up_stop_id'||
38 ' AND dl.delivery_id = nd.delivery_id'||
39 ' AND nd.delivery_id = da.delivery_id'||
40 ' AND da.delivery_detail_id = dd.delivery_detail_id'||
41 ' AND s.stop_location_id = nd.initial_pickup_location_id'||
42 ' AND dd.customer_id = :customer_id' ||
43 ' AND dd.ship_to_location_id = :ship_to_location_id' ||
44 ' AND NVL(ol.intmed_ship_to_org_id,'||k_NNULL||') = NVL(:intmed_ship_to_org_id,'||k_NNULL||')'|| --Bugfix 5911991
45 ' AND dd.inventory_item_id = :inventory_item_id' ||
46 ' AND dd.customer_item_id = :customer_item_id' ||
47 ' AND dd.source_line_id = ol.line_id' ||
48 ' AND dd.source_code = ''OE''' ||
49 ' AND s.actual_departure_date IS NOT NULL' ||
50 ' AND ol.shipped_quantity IS NOT NULL';
51
52 g_col_tab(g_col_tab.COUNT+1) := p_customer_id;
53 g_col_tab(g_col_tab.COUNT+1) := p_ship_to_location_id;
54 g_col_tab(g_col_tab.COUNT+1) := p_intmed_ship_to_org_id; --Bugfix 5911991
55 g_col_tab(g_col_tab.COUNT+1 ):= p_inventory_item_id;
56 g_col_tab(g_col_tab.COUNT+1) := p_customer_item_id;
57 --
58 IF p_org_id IS NOT NULL THEN
59 --
60 v_where_clause := v_where_clause || ' AND ol.org_id = :org_id ';
61 g_col_tab(g_col_tab.COUNT+1) := p_org_id;
62 --
63 END IF;
64 --
65 -- global_atp
66 IF p_ship_from_location_id IS NOT NULL THEN
67
68 v_where_clause := v_where_clause ||
69 ' AND dd.ship_from_location_id = :ship_from_location_id';
70
71 g_col_tab(g_col_tab.COUNT+1):=p_ship_from_location_id;
72
73 END IF;
74
75 v_request_date_str := TO_CHAR(p_optional_match_rec.request_date,'RRRR/MM/DD HH24:MI:SS');
76 v_schedule_date_str := TO_CHAR(p_optional_match_rec.schedule_date,'RRRR/MM/DD HH24:MI:SS');
77
78
79
80 IF p_match_across_rule.request_date = 'Y' THEN
81 --
82 v_where_clause := v_where_clause ||
83 ' AND dd.date_requested = TO_DATE(:v_request_date_str,''RRRR/MM/DD HH24:MI:SS'')';
84
85 g_col_tab(g_col_tab.COUNT+1):=v_request_date_str;
86 --
87 ELSE
88 --
89 IF p_match_within_rule.request_date = 'Y' THEN
90 --
91 v_where_clause := v_where_clause ||
92 ' AND dd.date_requested = DECODE(ol.rla_schedule_type_code, :schedule_type, TO_DATE(:v_request_date_str,''RRRR/MM/DD HH24:MI:SS'')'||', dd.date_requested)';
93
94 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
95 g_col_tab(g_col_tab.COUNT+1):=v_request_date_str;
96 --
97 END IF;
98 --
99 END IF;
100 --
101 IF p_match_across_rule.schedule_date = 'Y' THEN
102 --
103 v_where_clause := v_where_clause ||
104 ' AND dd.date_scheduled = TO_DATE(:v_schedule_date_str,''RRRR/MM/DD HH24:MI:SS'')';
105
106 g_col_tab(g_col_tab.COUNT+1):=v_schedule_date_str;
107 --
108 ELSE
109 --
110 IF p_match_within_rule.schedule_date = 'Y' THEN
111 --
112 v_where_clause := v_where_clause ||
113 ' AND dd.date_scheduled = DECODE(ol.rla_schedule_type_code,:schedule_typ, TO_DATE(:v_schedule_date_str,''RRRR/MM/DD HH24:MI:SS'')'||', dd.date_scheduled)';
114
115 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
116 g_col_tab(g_col_tab.COUNT+1):=v_schedule_date_str;
117 --
118 END IF;
119 --
120 END IF;
121 --
122
123
124 IF p_match_across_rule.cust_production_line = 'Y' THEN
125 --
126 v_where_clause := v_where_clause ||
127 ' AND NVL(ol.customer_production_line,'''||k_VNULL||
128 ''') = NVL(:cust_production_line,'''||k_VNULL||''')';
129
130 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.cust_production_line;
131
132 --
133 ELSE
134 --
135 IF p_match_within_rule.cust_production_line = 'Y' THEN
136 --
137 v_where_clause := v_where_clause ||
138 ' AND NVL(ol.customer_production_line,'''||k_VNULL||
139 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:cust_production_line,'''||k_VNULL||'''), NVL(ol.customer_production_line,'''||k_VNULL||'''))';
140
141 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
142 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.cust_production_line;
143 --
144 END IF;
145 --
146 END IF;
147
148
149 --
150 IF p_match_across_rule.customer_dock_code = 'Y' THEN
151 --
152 v_where_clause := v_where_clause ||
153 ' AND NVL(dd.customer_dock_code,'''||k_VNULL||''') = NVL(:customer_dock_code,'''||k_VNULL||''')';
154
155 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.customer_dock_code;
156 --
157 ELSE
158 --
159 IF p_match_within_rule.customer_dock_code = 'Y' THEN
160 --
161 v_where_clause := v_where_clause ||
162 ' AND NVL(dd.customer_dock_code,'''||k_VNULL||''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:customer_dock_code,'''||k_VNULL||'''),NVL(dd.customer_dock_code,'''||k_VNULL||'''))';
163
164 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
165 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.customer_dock_code;
166
167 --
168 END IF;
169 --
170 END IF;
171
172 --
173 IF p_match_across_rule.cust_po_number = 'Y' THEN
174 --
175 v_where_clause := v_where_clause ||
176 ' AND NVL(dd.cust_po_number,'''||k_VNULL||''') = NVL(:cust_po_number,'''||k_VNULL||''')';
177
178 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.cust_po_number;
179 --
180 ELSE
181 --
182 IF p_match_within_rule.cust_po_number = 'Y' THEN
183 --
184 v_where_clause := v_where_clause ||
185 ' AND NVL(dd.cust_po_number,'''||k_VNULL||
186 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:cust_po_number,'''||k_VNULL||'''),NVL(dd.cust_po_number,'''||k_VNULL||'''))';
187
188 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
189 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.cust_po_number;
190
191 --
192 END IF;
193 --
194 END IF;
195
196 --
197 IF p_match_across_rule.customer_item_revision = 'Y' THEN
198 --
199 v_where_clause := v_where_clause ||
200 ' AND NVL(dd.revision,'''||k_VNULL||''') = NVL(:customer_item_revision,'''||k_VNULL||''')';
201
202 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.customer_item_revision;
203 --
204 ELSE
205 --
206 IF p_match_within_rule.customer_item_revision = 'Y' THEN
207 --
208 v_where_clause := v_where_clause ||
209 ' AND NVL(dd.revision,'''||k_VNULL||
210 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:customer_item_revision,'''||k_VNULL||'''),NVL(dd.revision,'''||k_VNULL||'''))';
211
212 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
213 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.customer_item_revision;
214
215 --
216 END IF;
217 --
218 END IF;
219 --
220
221 --
222 IF p_match_across_rule.customer_job = 'Y' THEN
223 --
224 v_where_clause := v_where_clause ||
225 ' AND NVL(ol.customer_job,'''||k_VNULL||''') = NVL(:customer_job,'''||k_VNULL||''')';
226
227 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.customer_job;
228 --
229 ELSE
230 --
231 IF p_match_within_rule.customer_job = 'Y' THEN
232 --
233 v_where_clause := v_where_clause ||
234 ' AND NVL(ol.customer_job,'''||k_VNULL||
235 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:customer_job,'''||k_VNULL||'''),NVL(ol.customer_job,'''||k_VNULL||'''))';
236
237 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
238 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.customer_job;
239
240 --
241 END IF;
242 --
243 END IF;
244 --
245
246
247 --
248 IF p_match_across_rule.cust_model_serial_number = 'Y' THEN
249 --
250 v_where_clause := v_where_clause ||
251 ' AND NVL(ol.cust_model_serial_number,'''||k_VNULL||''') = NVL(:cust_model_serial_number,'''||k_VNULL||''')';
252
253 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.cust_model_serial_number;
254 --
255 ELSE
256 --
257 IF p_match_within_rule.cust_model_serial_number = 'Y' THEN
258 --
259 v_where_clause := v_where_clause ||
260 ' AND NVL(ol.cust_model_serial_number,'''||k_VNULL||
261 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:cust_model_serial_number,'''||k_VNULL||'''),NVL(ol.cust_model_serial_number,'''||k_VNULL||'''))';
262
263 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
264 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.cust_model_serial_number;
265
266 --
267 END IF;
268 --
269 END IF;
270 --
271
272 --
273 IF p_match_across_rule.cust_production_seq_num = 'Y' THEN
274 --
275 v_where_clause := v_where_clause ||
276 ' AND NVL(dd.customer_prod_seq,'''||k_VNULL||''') = NVL(:cust_production_seq_num,'''||k_VNULL||''')';
277
278 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.cust_production_seq_num;
279 --
280 ELSE
281 --
282 IF p_match_within_rule.cust_production_seq_num = 'Y' THEN
283 --
284 v_where_clause := v_where_clause ||
285 ' AND NVL(dd.customer_prod_seq,'''||k_VNULL||
286 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:cust_production_seq_num,'''||k_VNULL||'''),NVL(dd.customer_prod_seq,'''||k_VNULL||'''))';
287
288 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
289 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.cust_production_seq_num;
290
291 --
292 END IF;
293 --
294 END IF;
295 --
296
297 --
298 IF p_match_across_rule.industry_attribute1 = 'Y' THEN
299 --
300 v_where_clause := v_where_clause ||
301 ' AND NVL(ol.industry_attribute1,'''||k_VNULL||''') = NVL(:industry_attribute1,'''||k_VNULL||''')';
302
303 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.industry_attribute1;
304 --
305 ELSE
306 --
307 IF p_match_within_rule.industry_attribute1 = 'Y' THEN
308 --
309 v_where_clause := v_where_clause ||
310 ' AND NVL(ol.industry_attribute1,'''||k_VNULL||
311 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:industry_attribute1,'''||k_VNULL||'''),NVL(ol.industry_attribute1,'''||k_VNULL||'''))';
312
313 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
314 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.industry_attribute1;
315
316 --
317 END IF;
318 --
319 END IF;
320 --
321 --
322 IF p_match_across_rule.industry_attribute2 = 'Y' THEN
323 --
324 v_where_clause := v_where_clause ||
325 ' AND NVL(ol.industry_attribute2,'''||k_VNULL||''') = NVL(:industry_attribute2,'''||k_VNULL||''')';
326
327 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.industry_attribute2;
328 --
329 ELSE
330 --
331 IF p_match_within_rule.industry_attribute2 = 'Y' THEN
332 --
333 v_where_clause := v_where_clause ||
334 ' AND NVL(ol.industry_attribute2,'''||k_VNULL||
335 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:industry_attribute2,'''||k_VNULL||'''),NVL(ol.industry_attribute2,'''||k_VNULL||'''))';
336
337 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
338 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.industry_attribute2;
339
340 --
341 END IF;
342 --
343 END IF;
344 --
345 --
346 IF p_match_across_rule.industry_attribute4 = 'Y' THEN
347 --
348 v_where_clause := v_where_clause ||
349 ' AND NVL(ol.industry_attribute4,'''||k_VNULL||''') = NVL(:industry_attribute4,'''||k_VNULL||''')';
350
351 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.industry_attribute4;
352 --
353 ELSE
354 --
355 IF p_match_within_rule.industry_attribute4 = 'Y' THEN
356 --
357 v_where_clause := v_where_clause ||
358 ' AND NVL(ol.industry_attribute4,'''||k_VNULL||
359 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:industry_attribute4,'''||k_VNULL||'''),NVL(ol.industry_attribute4,'''||k_VNULL||'''))';
360
361 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
362 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.industry_attribute4;
363
364 --
365 END IF;
366 --
367 END IF;
368 --
369
370 --
371 IF p_match_across_rule.industry_attribute5 = 'Y' THEN
372 --
373 v_where_clause := v_where_clause ||
374 ' AND NVL(ol.industry_attribute5,'''||k_VNULL||''') = NVL(:industry_attribute5,'''||k_VNULL||''')';
375
376 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.industry_attribute5;
377 --
378 ELSE
379 --
380 IF p_match_within_rule.industry_attribute5 = 'Y' THEN
381 --
382 v_where_clause := v_where_clause ||
383 ' AND NVL(ol.industry_attribute5,'''||k_VNULL||
384 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:industry_attribute5,'''||k_VNULL||'''),NVL(ol.industry_attribute5,'''||k_VNULL||'''))';
385
386 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
387 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.industry_attribute5;
388
389 --
390 END IF;
391 --
392 END IF;
393 --
394 --
395 IF p_match_across_rule.industry_attribute6 = 'Y' THEN
396 --
397 v_where_clause := v_where_clause ||
398 ' AND NVL(ol.industry_attribute6,'''||k_VNULL||''') = NVL(:industry_attribute6,'''||k_VNULL||''')';
399
400 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.industry_attribute6;
401 --
402 ELSE
403 --
404 IF p_match_within_rule.industry_attribute6 = 'Y' THEN
405 --
406 v_where_clause := v_where_clause ||
407 ' AND NVL(ol.industry_attribute6,'''||k_VNULL||
408 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:industry_attribute6,'''||k_VNULL||'''),NVL(ol.industry_attribute6,'''||k_VNULL||'''))';
409
410 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
411 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.industry_attribute6;
412
413 --
414 END IF;
415 --
416 END IF;
417 --
418
419 --
420 IF p_match_across_rule.industry_attribute10 = 'Y' THEN
421 --
422 v_where_clause := v_where_clause ||
423 ' AND NVL(ol.industry_attribute10,'''||k_VNULL||''') = NVL(:industry_attribute10,'''||k_VNULL||''')';
424
425 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.industry_attribute10;
426 --
427 ELSE
428 --
429 IF p_match_within_rule.industry_attribute10 = 'Y' THEN
430 --
431 v_where_clause := v_where_clause ||
432 ' AND NVL(ol.industry_attribute10,'''||k_VNULL||
433 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:industry_attribute10,'''||k_VNULL||'''),NVL(ol.industry_attribute10,'''||k_VNULL||'''))';
434
435 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
436 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.industry_attribute10;
437
438 --
439 END IF;
440 --
441 END IF;
442 --
443 --
444 IF p_match_across_rule.industry_attribute11 = 'Y' THEN
445 --
446 v_where_clause := v_where_clause ||
447 ' AND NVL(ol.industry_attribute11,'''||k_VNULL||''') = NVL(:industry_attribute11,'''||k_VNULL||''')';
448
449 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.industry_attribute11;
450 --
451 ELSE
452 --
453 IF p_match_within_rule.industry_attribute11 = 'Y' THEN
454 --
455 v_where_clause := v_where_clause ||
456 ' AND NVL(ol.industry_attribute11,'''||k_VNULL||
457 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:industry_attribute11,'''||k_VNULL||'''),NVL(ol.industry_attribute11,'''||k_VNULL||'''))';
458
459 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
460 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.industry_attribute11;
461
462 --
463 END IF;
464 --
465 END IF;
466 --
467 --
468 IF p_match_across_rule.industry_attribute12 = 'Y' THEN
469 --
470 v_where_clause := v_where_clause ||
471 ' AND NVL(ol.industry_attribute12,'''||k_VNULL||''') = NVL(:industry_attribute12,'''||k_VNULL||''')';
472
473 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.industry_attribute12;
474 --
475 ELSE
476 --
477 IF p_match_within_rule.industry_attribute12 = 'Y' THEN
478 --
479 v_where_clause := v_where_clause ||
480 ' AND NVL(ol.industry_attribute12,'''||k_VNULL||
481 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:industry_attribute12,'''||k_VNULL||'''),NVL(ol.industry_attribute12,'''||k_VNULL||'''))';
482
483 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
484 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.industry_attribute12;
485
486 --
487 END IF;
488 --
489 END IF;
490 --
491 --
492 IF p_match_across_rule.industry_attribute13 = 'Y' THEN
493 --
494 v_where_clause := v_where_clause ||
495 ' AND NVL(ol.industry_attribute13,'''||k_VNULL||''') = NVL(:industry_attribute13,'''||k_VNULL||''')';
496
497 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.industry_attribute13;
498 --
499 ELSE
500 --
501 IF p_match_within_rule.industry_attribute13 = 'Y' THEN
502 --
503 v_where_clause := v_where_clause ||
504 ' AND NVL(ol.industry_attribute13,'''||k_VNULL||
505 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:industry_attribute13,'''||k_VNULL||'''),NVL(ol.industry_attribute13,'''||k_VNULL||'''))';
506
507 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
508 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.industry_attribute13;
509
510 --
511 END IF;
512 --
513 END IF;
514 --
515 --
516 IF p_match_across_rule.industry_attribute14 = 'Y' THEN
517 --
518 v_where_clause := v_where_clause ||
519 ' AND NVL(ol.industry_attribute14,'''||k_VNULL||''') = NVL(:industry_attribute14,'''||k_VNULL||''')';
520
521 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.industry_attribute14;
522 --
523 ELSE
524 --
525 IF p_match_within_rule.industry_attribute14 = 'Y' THEN
526 --
527 v_where_clause := v_where_clause ||
528 ' AND NVL(ol.industry_attribute14,'''||k_VNULL||
529 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:industry_attribute14,'''||k_VNULL||'''),NVL(ol.industry_attribute14,'''||k_VNULL||'''))';
530
531 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
532 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.industry_attribute14;
533
534 --
535 END IF;
536 --
537 END IF;
538 --
539 --
540 IF p_match_across_rule.attribute1 = 'Y' THEN
541 --
542 v_where_clause := v_where_clause ||
543 ' AND NVL(dd.attribute1,'''||k_VNULL||''') = NVL(:attribute1,'''||k_VNULL||''')';
544
545 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute1;
546 --
547 ELSE
548 --
549 IF p_match_within_rule.attribute1 = 'Y' THEN
550 --
551 v_where_clause := v_where_clause ||
552 ' AND NVL(dd.attribute1,'''||k_VNULL||
553 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:attribute1,'''||k_VNULL||'''),NVL(dd.attribute1,'''||k_VNULL||'''))';
554
555 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
556 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute1;
557
558 --
559 END IF;
560 --
561 END IF;
562 --
563 --
564 IF p_match_across_rule.attribute2 = 'Y' THEN
565 --
566 v_where_clause := v_where_clause ||
567 ' AND NVL(dd.attribute2,'''||k_VNULL||''') = NVL(:attribute2,'''||k_VNULL||''')';
568
569 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute2;
570 --
571 ELSE
572 --
573 IF p_match_within_rule.attribute2 = 'Y' THEN
574 --
575 v_where_clause := v_where_clause ||
576 ' AND NVL(dd.attribute2,'''||k_VNULL||
577 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:attribute2,'''||k_VNULL||'''),NVL(dd.attribute2,'''||k_VNULL||'''))';
578
579 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
580 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute2;
581
582 --
583 END IF;
584 --
585 END IF;
586 --
587 --
588 IF p_match_across_rule.attribute3 = 'Y' THEN
589 --
590 v_where_clause := v_where_clause ||
591 ' AND NVL(dd.attribute3,'''||k_VNULL||''') = NVL(:attribute3,'''||k_VNULL||''')';
592
593 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute3;
594 --
595 ELSE
596 --
597 IF p_match_within_rule.attribute3 = 'Y' THEN
598 --
599 v_where_clause := v_where_clause ||
600 ' AND NVL(dd.attribute3,'''||k_VNULL||
601 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:attribute3,'''||k_VNULL||'''),NVL(dd.attribute3,'''||k_VNULL||'''))';
602
603 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
604 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute3;
605
606 --
607 END IF;
608 --
609 END IF;
610 --
611 --
612 IF p_match_across_rule.attribute4 = 'Y' THEN
613 --
614 v_where_clause := v_where_clause ||
615 ' AND NVL(dd.attribute4,'''||k_VNULL||''') = NVL(:attribute4,'''||k_VNULL||''')';
616
617 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute4;
618 --
619 ELSE
620 --
621 IF p_match_within_rule.attribute4 = 'Y' THEN
622 --
623 v_where_clause := v_where_clause ||
624 ' AND NVL(dd.attribute4,'''||k_VNULL||
625 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:attribute4,'''||k_VNULL||'''),NVL(dd.attribute4,'''||k_VNULL||'''))';
626
627 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
628 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute4;
629
630 --
631 END IF;
632 --
633 END IF;
634 --
635 --
636 IF p_match_across_rule.attribute5 = 'Y' THEN
637 --
638 v_where_clause := v_where_clause ||
639 ' AND NVL(dd.attribute5,'''||k_VNULL||''') = NVL(:attribute5,'''||k_VNULL||''')';
640
641 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute5;
642 --
643 ELSE
644 --
645 IF p_match_within_rule.attribute5 = 'Y' THEN
646 --
647 v_where_clause := v_where_clause ||
648 ' AND NVL(dd.attribute5,'''||k_VNULL||
649 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:attribute5,'''||k_VNULL||'''),NVL(dd.attribute5,'''||k_VNULL||'''))';
650
651 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
652 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute5;
653
654 --
655 END IF;
656 --
657 END IF;
658 --
659 --
660 IF p_match_across_rule.attribute6 = 'Y' THEN
661 --
662 v_where_clause := v_where_clause ||
663 ' AND NVL(dd.attribute6,'''||k_VNULL||''') = NVL(:attribute6,'''||k_VNULL||''')';
664
665 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute6;
666 --
667 ELSE
668 --
669 IF p_match_within_rule.attribute6 = 'Y' THEN
670 --
671 v_where_clause := v_where_clause ||
672 ' AND NVL(dd.attribute6,'''||k_VNULL||
673 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:attribute6,'''||k_VNULL||'''),NVL(dd.attribute6,'''||k_VNULL||'''))';
674
675 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
676 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute6;
677
678 --
679 END IF;
680 --
681 END IF;
682 --
683 --
684 IF p_match_across_rule.attribute7 = 'Y' THEN
685 --
686 v_where_clause := v_where_clause ||
687 ' AND NVL(dd.attribute7,'''||k_VNULL||''') = NVL(:attribute7,'''||k_VNULL||''')';
688
689 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute7;
690 --
691 ELSE
692 --
693 IF p_match_within_rule.attribute7 = 'Y' THEN
694 --
695 v_where_clause := v_where_clause ||
696 ' AND NVL(dd.attribute7,'''||k_VNULL||
697 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:attribute7,'''||k_VNULL||'''),NVL(dd.attribute7,'''||k_VNULL||'''))';
698
699 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
700 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute7;
701
702 --
703 END IF;
704 --
705 END IF;
706 --
707 --
708 IF p_match_across_rule.attribute8 = 'Y' THEN
709 --
710 v_where_clause := v_where_clause ||
711 ' AND NVL(dd.attribute8,'''||k_VNULL||''') = NVL(:attribute8,'''||k_VNULL||''')';
712
713 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute8;
714 --
715 ELSE
716 --
717 IF p_match_within_rule.attribute8 = 'Y' THEN
718 --
719 v_where_clause := v_where_clause ||
720 ' AND NVL(dd.attribute8,'''||k_VNULL||
721 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:attribute8,'''||k_VNULL||'''),NVL(dd.attribute8,'''||k_VNULL||'''))';
722
723 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
724 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute8;
725
726 --
727 END IF;
728 --
729 END IF;
730 --
731 --
732 IF p_match_across_rule.attribute9 = 'Y' THEN
733 --
734 v_where_clause := v_where_clause ||
735 ' AND NVL(dd.attribute9,'''||k_VNULL||''') = NVL(:attribute9,'''||k_VNULL||''')';
736
737 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute9;
738 --
739 ELSE
740 --
741 IF p_match_within_rule.attribute9 = 'Y' THEN
742 --
743 v_where_clause := v_where_clause ||
744 ' AND NVL(dd.attribute9,'''||k_VNULL||
745 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:attribute9,'''||k_VNULL||'''),NVL(dd.attribute9,'''||k_VNULL||'''))';
746
747 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
748 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute9;
749
750 --
751 END IF;
752 --
753 END IF;
754 --
755 --
756 IF p_match_across_rule.attribute10 = 'Y' THEN
757 --
758 v_where_clause := v_where_clause ||
759 ' AND NVL(dd.attribute10,'''||k_VNULL||''') = NVL(:attribute10,'''||k_VNULL||''')';
760
761 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute10;
762 --
763 ELSE
764 --
765 IF p_match_within_rule.attribute10 = 'Y' THEN
766 --
767 v_where_clause := v_where_clause ||
768 ' AND NVL(dd.attribute10,'''||k_VNULL||
769 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:attribute10,'''||k_VNULL||'''),NVL(dd.attribute10,'''||k_VNULL||'''))';
770
771 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
772 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute10;
773
774 --
775 END IF;
776 --
777 END IF;
778 --
779 --
780 IF p_match_across_rule.attribute11 = 'Y' THEN
781 --
782 v_where_clause := v_where_clause ||
783 ' AND NVL(dd.attribute11,'''||k_VNULL||''') = NVL(:attribute11,'''||k_VNULL||''')';
784
785 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute11;
786 --
787 ELSE
788 --
789 IF p_match_within_rule.attribute11 = 'Y' THEN
790 --
791 v_where_clause := v_where_clause ||
792 ' AND NVL(dd.attribute11,'''||k_VNULL||
793 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:attribute11,'''||k_VNULL||'''),NVL(dd.attribute11,'''||k_VNULL||'''))';
794
795 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
796 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute11;
797
798 --
799 END IF;
800 --
801 END IF;
802 --
803 --
804 IF p_match_across_rule.attribute12 = 'Y' THEN
805 --
806 v_where_clause := v_where_clause ||
807 ' AND NVL(dd.attribute12,'''||k_VNULL||''') = NVL(:attribute12,'''||k_VNULL||''')';
808
809 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute12;
810 --
811 ELSE
812 --
813 IF p_match_within_rule.attribute12 = 'Y' THEN
814 --
815 v_where_clause := v_where_clause ||
816 ' AND NVL(dd.attribute12,'''||k_VNULL||
817 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:attribute12,'''||k_VNULL||'''),NVL(dd.attribute12,'''||k_VNULL||'''))';
818
819 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
820 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute12;
821
822 --
823 END IF;
824 --
825 END IF;
826 --
827 --
828 IF p_match_across_rule.attribute13 = 'Y' THEN
829 --
830 v_where_clause := v_where_clause ||
831 ' AND NVL(dd.attribute13,'''||k_VNULL||''') = NVL(:attribute13,'''||k_VNULL||''')';
832
833 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute13;
834 --
835 ELSE
836 --
837 IF p_match_within_rule.attribute13 = 'Y' THEN
838 --
839 v_where_clause := v_where_clause ||
840 ' AND NVL(dd.attribute13,'''||k_VNULL||
841 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:attribute13,'''||k_VNULL||'''),NVL(dd.attribute13,'''||k_VNULL||'''))';
842
843 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
844 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute13;
845
846 --
847 END IF;
848 --
849 END IF;
850 --
851 --
852 IF p_match_across_rule.attribute14 = 'Y' THEN
853 --
854 v_where_clause := v_where_clause ||
855 ' AND NVL(dd.attribute14,'''||k_VNULL||''') = NVL(:attribute14,'''||k_VNULL||''')';
856
857 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute14;
858 --
859 ELSE
860 --
861 IF p_match_within_rule.attribute14 = 'Y' THEN
862 --
863 v_where_clause := v_where_clause ||
864 ' AND NVL(dd.attribute14,'''||k_VNULL||
865 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:attribute14,'''||k_VNULL||'''),NVL(dd.attribute14,'''||k_VNULL||'''))';
866
867 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
868 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute14;
869
870 --
871 END IF;
872 --
873 END IF;
874 --
875 --
876 IF p_match_across_rule.attribute15 = 'Y' THEN
877 --
878 v_where_clause := v_where_clause ||
879 ' AND NVL(dd.attribute15,'''||k_VNULL||''') = NVL(:attribute15,'''||k_VNULL||''')';
880
881 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute15;
882 --
883 ELSE
884 --
885 IF p_match_within_rule.attribute15 = 'Y' THEN
886 --
887 v_where_clause := v_where_clause ||
888 ' AND NVL(dd.attribute15,'''||k_VNULL||
889 ''') = DECODE(ol.rla_schedule_type_code, :schedule_type, NVL(:attribute15,'''||k_VNULL||'''),NVL(dd.attribute15,'''||k_VNULL||'''))';
890
891 g_col_tab(g_col_tab.COUNT+1):=p_schedule_type;
892 g_col_tab(g_col_tab.COUNT+1):=p_optional_match_rec.attribute15;
893
894 --
895 END IF;
896 --
897 END IF;
898 --
899
900 /*end of matching attributes*/
901
902 --
903 IF p_blanket_number = k_NNULL THEN
904 --
905 v_select_clause :=
906
907 'SELECT dd.delivery_detail_id,s.stop_id,s.actual_departure_date,'||
908 'nd.name,dd.shipped_quantity, NULL, ol.ordered_item_id, ol.header_id order_hdr_id '||
909 'FROM wsh_delivery_Details dd,wsh_trip_stops s,wsh_delivery_legs dl,'||
910 'wsh_delivery_assignments_v da,wsh_new_deliveries nd,oe_order_lines_all ol ';
911 --
912 v_where_clause := v_where_clause ||
913 ' AND dd.source_header_id = :order_header_id';
914
915 g_col_tab(g_col_tab.COUNT+1):=p_order_header_id;
916
917
918 --
919 ELSE
920 --
921 v_select_clause :=
922
923 'SELECT dd.delivery_detail_id,s.stop_id,s.actual_departure_date,'||
924 'nd.name,dd.shipped_quantity,ol.blanket_number,ol.ordered_item_id,'||
925 'ol.header_id order_hdr_id '||
926 'FROM wsh_delivery_Details dd,wsh_trip_stops s,wsh_delivery_legs dl,'||
927 'wsh_delivery_assignments_v da,wsh_new_deliveries nd,oe_order_lines_all ol ';
928 --
929 v_where_clause := v_where_clause ||
930 ' AND ol.blanket_number = :blanket_number' ||
931 ' AND dd.source_header_id IN (select rso_hdr_id FROM rlm_blanket_rso '||
932 ' WHERE blanket_number = :blanket_number' ||
933 ' AND customer_id = dd.customer_id '||
934 ' AND (customer_item_id = dd.customer_item_id '||
935 ' OR customer_item_id = '''|| k_NNULL|| '''))';
936
937 g_col_tab(g_col_tab.COUNT+1):=p_blanket_number;
938 g_col_tab(g_col_tab.COUNT+1):=p_blanket_number;
939
940 END IF;
941 --
942 x_c_transit_detail := v_select_clause || v_where_clause;
943 --
944 rlm_core_sv.dlog('g_col_tab.count', g_col_tab.COUNT);
945 rlm_core_sv.dlog('x_c_transit_detail', substr(x_c_transit_detail,1,800));
946 rlm_core_sv.dlog('x_c_transit_detail Contd.', substr(x_c_transit_detail,801,1600));
947 rlm_core_sv.dlog('x_c_transit_detail Contd.', substr(x_c_transit_detail,1601,2400));
948 rlm_core_sv.dlog('x_c_transit_detail Contd.', substr(x_c_transit_detail,2401,3200));
949 rlm_core_sv.dlog('x_c_transit_detail Contd.', substr(x_c_transit_detail,3201,4000));
950 rlm_core_sv.dlog('Exiting WSH_RLM_INTERFACE.BuildQuery');
951 --
952 EXCEPTION
953 --
954 WHEN OTHERS THEN
955 rlm_core_sv.dlog('When others exception');
956 RAISE;
957
958 END BuildQuery;
959
960 PROCEDURE Get_In_Transit_Qty(
961 p_source_code IN VARCHAR2 DEFAULT 'OE',
962 p_customer_id IN NUMBER,
963 p_ship_to_org_id IN NUMBER,
964 p_intmed_ship_to_org_id IN NUMBER, --Bugfix 5911991
965 p_ship_from_org_id IN NUMBER,
966 p_inventory_item_id IN NUMBER,
967 p_customer_item_id IN NUMBER,
968 p_order_header_id IN NUMBER,
969 p_blanket_number IN NUMBER,
970 p_org_id IN NUMBER DEFAULT NULL,
971 p_schedule_type IN VARCHAR2,
972 p_shipper_recs IN t_shipper_rec,
973 p_shipment_date IN DATE,
974 p_match_within_rule IN RLM_CORE_SV.t_match_rec,
975 p_match_across_rule IN RLM_CORE_SV.t_match_rec,
976 p_optional_match_rec IN t_optional_match_rec,
977 x_in_transit_qty OUT NOCOPY NUMBER,
978 x_return_status OUT NOCOPY VARCHAR2)
979
980 IS
981 --
982 l_ship_to_location_id NUMBER;
983 l_ship_from_location_id NUMBER;
984 invalid_org EXCEPTION;
985 invalid_cust_site EXCEPTION;
986 l_location_status VARCHAR2(30);
987 l_total_qty_in_transit NUMBER;
988 l_departure_date DATE;
989 l_latest_departure_date DATE;
990 --
991 CURSOR c_get_actual_departure_date(c_del_name VARCHAR ) IS
992 SELECT MIN(STP.ACTUAL_DEPARTURE_DATE)
993 FROM WSH_TRIP_STOPS STP
994 WHERE STP.STOP_ID in
995 ( SELECT distinct(LEG.PICK_UP_STOP_ID)
996 FROM
997 WSH_DELIVERY_DETAILS DET,
998 WSH_NEW_DELIVERIES DEL,
999 WSH_DELIVERY_LEGS LEG,
1000 wsh_delivery_assignments_v ASG
1001 WHERE
1002 DEL.DELIVERY_ID = ASG.DELIVERY_ID AND
1003 ASG.DELIVERY_DETAIL_ID = DET.DELIVERY_DETAIL_ID AND
1004 LEG.DELIVERY_ID = DEL.DELIVERY_ID AND
1005 DEL.NAME = c_del_name
1006 )
1007 AND STP.ACTUAL_DEPARTURE_DATE IS NOT NULL;
1008 --
1009 TYPE t_transit_detail IS RECORD(
1010 delivery_detail_id NUMBER,
1011 stop_id NUMBER,
1012 actual_departure_date DATE,
1013 name VARCHAR2(30),
1014 shipped_quantity NUMBER,
1015 blanket_number NUMBER,
1016 ordered_item_id NUMBER,
1017 order_hdr_id NUMBER
1018 );
1019 --
1020 l_transit_detail t_transit_detail;
1021 x_c_transit_detail VARCHAR2(32000);
1022 --
1023 TYPE t_Cursor_ref IS REF CURSOR;
1024 c_transit_detail t_Cursor_ref;
1025 --
1026 l_debug_on BOOLEAN;
1027 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_IN_TRANSIT_QTY';
1028 --
1029 BEGIN
1030 --
1031 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1032 --
1033 IF l_debug_on IS NULL
1034 THEN
1035 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1036 END IF;
1037 --
1038 IF l_debug_on THEN
1039 WSH_DEBUG_SV.push(l_module_name);
1040 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
1041 WSH_DEBUG_SV.log(l_module_name,'P_CUSTOMER_ID',P_CUSTOMER_ID);
1042 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_TO_ORG_ID',P_SHIP_TO_ORG_ID);
1043 WSH_DEBUG_SV.log(l_module_name,'P_INTMED_SHIP_TO_ORG_ID',P_INTMED_SHIP_TO_ORG_ID); --Bugfix 5911991
1044 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_ORG_ID',P_SHIP_FROM_ORG_ID);
1045 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
1046 WSH_DEBUG_SV.log(l_module_name,'P_CUSTOMER_ITEM_ID',P_CUSTOMER_ITEM_ID);
1047 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_HEADER_ID',P_ORDER_HEADER_ID);
1048 WSH_DEBUG_SV.log(l_module_name,'P_BLANKET_NUMBER',P_BLANKET_NUMBER);
1049 WSH_DEBUG_SV.log(l_module_name,'P_ORG_ID',P_ORG_ID);
1050 WSH_DEBUG_SV.log(l_module_name,'P_SCHEDULE_TYPE',P_SCHEDULE_TYPE);
1051 WSH_DEBUG_SV.log(l_module_name,'P_SHIPMENT_DATE',P_SHIPMENT_DATE);
1052 WSH_DEBUG_SV.log(l_module_name,'P_OPTIONAL_MATCH_REC.CUST_PRODUCTION_LINE',P_OPTIONAL_MATCH_REC.CUST_PRODUCTION_LINE);
1053 WSH_DEBUG_SV.log(l_module_name,'P_OPTIONAL_MATCH_REC.CUSTOMER_DOCK_CODE',P_OPTIONAL_MATCH_REC.CUSTOMER_DOCK_CODE);
1054 WSH_DEBUG_SV.log(l_module_name,'P_OPTIONAL_MATCH_REC.CUST_PO_NUMBER',P_OPTIONAL_MATCH_REC.CUST_PO_NUMBER);
1055 WSH_DEBUG_SV.log(l_module_name,'P_OPTIONAL_MATCH_REC.CUSTOMER_JOB',P_OPTIONAL_MATCH_REC.CUSTOMER_JOB);
1056 WSH_DEBUG_SV.log(l_module_name,'P_OPTIONAL_MATCH_REC.CUST_MODEL_SERIAL_NUMBER',P_OPTIONAL_MATCH_REC.CUST_MODEL_SERIAL_NUMBER);
1057 WSH_DEBUG_SV.log(l_module_name,'P_OPTIONAL_MATCH_REC.CUST_PRODUCTION_SEQ_NUM',P_OPTIONAL_MATCH_REC.CUST_PRODUCTION_SEQ_NUM);
1058 END IF;
1059 --
1060 rlm_core_sv.dlog('Entering WSH_RLM_INTERFACE.Get_In_Transit_Qty');
1061 rlm_core_sv.dlog('Blanket Number = ' || p_blanket_number);
1062 --
1063 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1064 l_total_qty_in_transit := 0;
1065 --
1066 IF p_ship_from_org_id IS NULL THEN
1067 -- global_atp
1068 l_ship_from_location_id := NULL;
1069 l_location_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1070 --
1071 rlm_core_sv.dlog('Intransit Calculation for ATP Item');
1072 ELSE
1073 --
1074 IF l_debug_on THEN
1075 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_LOCATION_ID',WSH_DEBUG_SV.C_PROC_LEVEL);
1076 END IF;
1077 --
1078 wsh_util_core.get_location_id('ORG',p_ship_from_org_id,
1079 l_ship_from_location_id,
1080 l_location_status,
1081 FALSE);
1082 END IF;
1083
1084 IF (l_location_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1085 IF (l_location_status = WSH_UTIL_CORE.G_RET_STS_WARNING )
1086 AND (l_ship_from_location_id IS NULL) THEN
1087 x_in_transit_qty := 0 ;
1088 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1089 IF l_debug_on THEN
1090 WSH_DEBUG_SV.log(l_module_name,'X_INTRANSIT_QTY',
1091 x_in_transit_qty);
1092 WSH_DEBUG_SV.pop(l_module_name);
1093 END IF;
1094 RETURN;
1095 ELSE
1096 raise INVALID_ORG;
1097 END IF;
1098 END IF;
1099
1100 IF p_source_code = 'OE' THEN
1101 --
1102 IF l_debug_on THEN
1103 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_LOCATION_ID',WSH_DEBUG_SV.C_PROC_LEVEL);
1104 END IF;
1105 --
1106 wsh_util_core.get_location_id('CUSTOMER SITE',p_ship_to_org_id,
1107 l_ship_to_location_id,
1108 l_location_status,
1109 FALSE);
1110 --
1111 IF (l_location_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1112 IF (l_location_status = WSH_UTIL_CORE.G_RET_STS_WARNING )
1113 AND (l_ship_to_location_id IS NULL) THEN
1114 x_in_transit_qty := 0 ;
1115 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1116 IF l_debug_on THEN
1117 WSH_DEBUG_SV.log(l_module_name,'X_INTRANSIT_QTY', x_in_transit_qty);
1118 WSH_DEBUG_SV.pop(l_module_name);
1119 END IF;
1120 RETURN;
1121 ELSE
1122 raise invalid_cust_site;
1123 END IF;
1124 END IF;
1125
1126 IF (p_shipper_Recs.shipper_id1 is not null) THEN
1127 --
1128 rlm_core_sv.dlog('Shipper ID1 = ' || p_shipper_Recs.shipper_id1);
1129 OPEN c_get_actual_departure_date(p_shipper_Recs.shipper_id1);
1130 FETCH c_get_actual_departure_date INTO l_departure_date;
1131 CLOSE c_get_actual_departure_date;
1132 IF l_departure_date IS NOT NULL THEN
1133 l_latest_departure_date := l_departure_date;
1134 END IF;
1135 --
1136 END IF;
1137
1138 IF (p_shipper_Recs.shipper_id2 is not null) THEN
1139 --
1140 rlm_core_sv.dlog('Shipper ID2 = '|| p_shipper_Recs.shipper_id2);
1141 l_departure_date := NULL;
1142 OPEN c_get_actual_departure_date(p_shipper_Recs.shipper_id2);
1143 FETCH c_get_actual_departure_date INTO l_departure_date;
1144 CLOSE c_get_actual_departure_date;
1145 l_latest_departure_date := NVL(l_latest_departure_date, l_departure_date);
1146 IF l_departure_date IS NOT NULL AND
1147 l_departure_date > l_latest_departure_date THEN
1148 l_latest_departure_date := l_departure_date;
1149 END IF;
1150 --
1151 END IF;
1152
1153 IF (p_shipper_Recs.shipper_id3 is not null) THEN
1154 --
1155 rlm_core_sv.dlog('Shipper ID3 = '|| p_shipper_Recs.shipper_id3);
1156 l_departure_date := NULL;
1157 OPEN c_get_actual_departure_date(p_shipper_Recs.shipper_id3);
1158 FETCH c_get_actual_departure_date INTO l_departure_date;
1159 CLOSE c_get_actual_departure_date;
1160 l_latest_departure_date := NVL(l_latest_departure_date, l_departure_date);
1161 IF l_departure_date > l_latest_departure_date THEN
1162 l_latest_departure_date := l_departure_date;
1163 END IF;
1164 --
1165 END IF;
1166
1167 IF (p_shipper_Recs.shipper_id4 is not null) THEN
1168 --
1169 rlm_core_sv.dlog('Shipper ID4 = '|| p_shipper_Recs.shipper_id4);
1170 l_departure_date := NULL;
1171 OPEN c_get_actual_departure_date(p_shipper_Recs.shipper_id4);
1172 FETCH c_get_actual_departure_date INTO l_departure_date;
1173 CLOSE c_get_actual_departure_date;
1174 l_latest_departure_date := NVL(l_latest_departure_date, l_departure_date);
1175 IF l_departure_date > l_latest_departure_date THEN
1176 l_latest_departure_date := l_departure_date;
1177 END IF;
1178 --
1179 END IF;
1180
1181 IF (p_shipper_Recs.shipper_id5 is not null) THEN
1182 --
1183 rlm_core_sv.dlog('Shipper ID5 = '|| p_shipper_Recs.shipper_id5);
1184 l_departure_date := NULL;
1185 OPEN c_get_actual_departure_date(p_shipper_Recs.shipper_id5);
1186 FETCH c_get_actual_departure_date INTO l_departure_date;
1187 CLOSE c_get_actual_departure_date;
1188 l_latest_departure_date := NVL(l_latest_departure_date, l_departure_date);
1189 IF l_departure_date is not NULL and
1190 l_departure_date > l_latest_departure_date THEN
1191 l_latest_departure_date := l_departure_date;
1192 END IF;
1193 --
1194 END IF;
1195
1196 IF (l_latest_departure_date IS NULL) AND (p_shipment_date IS NOT NULL) THEN
1197 l_latest_departure_date := p_shipment_date;
1198 END IF;
1199 --
1200 rlm_core_sv.dlog('Latest Departure date ' ||
1201 to_char(l_latest_departure_date, 'MM/DD/YYYY HH24:MI:SS'));
1202 --
1203 IF (l_latest_departure_date IS NOT NULL ) THEN
1204 --
1205 rlm_core_sv.dlog('Get intransits for this Order : ' || p_order_header_id);
1206 --
1207 g_col_tab.DELETE; /* Bug 2946919 */
1208 --
1209 BuildQuery(p_customer_id,
1210 l_ship_to_location_id,
1211 p_intmed_ship_to_org_id,--Bugfix 5911991
1212 l_ship_from_location_id,
1213 p_inventory_item_id,
1214 p_customer_item_id,
1215 p_order_header_id,
1216 p_blanket_number,
1217 p_org_id,
1218 p_schedule_type,
1219 p_match_within_rule,
1220 p_match_across_rule,
1221 p_optional_match_rec,
1222 x_c_transit_detail);
1223 --
1224 RLM_CORE_SV.OpenDynamicCursor(c_transit_detail,x_c_transit_detail,g_col_tab);
1225 --
1226 FETCH c_transit_detail INTO l_transit_detail;
1227 --
1228 WHILE c_transit_detail%FOUND LOOP
1229 --
1230 rlm_core_sv.dlog('Delivery name ' || l_transit_detail.name);
1231 rlm_core_sv.dlog('Actual dep date ' ||
1232 to_char(l_transit_detail.actual_departure_date, 'MM/DD/YYYY HH24:MI:SS'));
1233 rlm_core_sv.dlog('Quantity shipped ' || l_transit_detail.shipped_quantity);
1234 --
1235 IF l_transit_detail.actual_departure_date > l_latest_departure_date THEN
1236 --
1237 l_total_qty_in_transit := l_total_qty_in_transit + l_transit_detail.shipped_quantity;
1238 --
1239 END IF;
1240 --
1241 FETCH c_transit_Detail INTO l_transit_Detail;
1242 --
1243 END LOOP;
1244 --
1245 CLOSE c_transit_detail;
1246 --
1247 x_in_transit_qty := l_total_qty_in_transit;
1248 --
1249 ELSE
1250 --
1251 x_in_transit_qty := 0;
1252 --
1253 END IF;
1254
1255 ELSE
1256 --
1257 x_in_transit_qty := 0;
1258 --
1259 END IF; /* source code = 'OE' */
1260 --
1261 rlm_core_sv.dlog('intransit qty = '|| x_in_transit_qty);
1262 rlm_core_sv.dlog('Exiting WSH_RLM_INTERFACE.get_in_transit_qty');
1263 --
1264 IF l_debug_on THEN
1265 WSH_DEBUG_SV.log(l_module_name,'X_INTRANSIT_QTY',x_in_transit_qty);
1266 WSH_DEBUG_SV.pop(l_module_name);
1267 END IF;
1268 --
1269 EXCEPTION
1270
1271 WHEN invalid_org THEN
1272 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1273 fnd_message.set_name('WSH', 'WSH_DET_NO_LOCATION_FOR_ORG');
1274 WSH_UTIL_CORE.add_message (x_return_status);
1275 --
1276 -- Debug Statements
1277 --
1278 IF l_debug_on THEN
1279 WSH_DEBUG_SV.logmsg(l_module_name,'INVALID_ORG exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1280 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_ORG');
1281 END IF;
1282 --
1283 WHEN invalid_cust_site THEN
1284 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1285 fnd_message.set_name('WSH', 'WSH_DET_NO_LOCATION_FOR_SITE');
1286 WSH_UTIL_CORE.add_message (x_return_status);
1287 --
1288 -- Debug Statements
1289 --
1290 IF l_debug_on THEN
1291 WSH_DEBUG_SV.logmsg(l_module_name,'INVALID_CUST_SITE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1292 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_CUST_SITE');
1293 END IF;
1294 --
1295 WHEN others THEN
1296 wsh_util_core.default_handler('WSH_RLM_INTERFACE.Get_In_Transit_Qty');
1297 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1298
1299 --
1300 -- Debug Statements
1301 --
1302 IF l_debug_on THEN
1303 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1304 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1305 END IF;
1306 --
1307 END Get_In_Transit_Qty;
1308
1309
1310 END WSH_RLM_INTERFACE;