[Home] [Help]
PACKAGE BODY: APPS.RLM_EXTINTERFACE_SV
Source
1 PACKAGE BODY RLM_EXTINTERFACE_SV as
2 /*$Header: RLMEINTB.pls 120.5 2007/10/31 12:54:46 sunilku noship $*/
3 /*===========================================================================*/
4
5 --
6 l_DEBUG NUMBER := NVL(fnd_profile.value('RLM_DEBUG_MODE'),-1);
7 g_wheretab RLM_CORE_SV.t_dynamic_tab;
8 --
9 /*==============================================================================
10
11 PROCEDURE BuildOELineTab(x_Op_tab IN rlm_rd_sv.t_generic_tab,
12 x_return_status OUT NOCOPY VARCHAR2)
13
14 ==============================================================================*/
15
16 PROCEDURE BuildOELineTab(x_Op_tab IN rlm_rd_sv.t_generic_tab, x_return_status out NOCOPY VARCHAR2)
17 IS
18
19 x_linecount NUMBER;
20
21 BEGIN
22 --
23 IF (l_debug <> -1) THEN
24 rlm_core_sv.dpush(k_SDEBUG,'BuildOELineTab');
25 rlm_core_sv.dlog(k_DEBUG,'no of lines in oe interface table',
26 x_Op_Tab.COUNT);
27 END IF;
28 --
29 x_return_status := 'S';
30 g_oe_line_tbl.delete;
31 --
32 FOR counter IN 1..x_Op_Tab.COUNT LOOP
33 --
34 IF (l_debug <> -1) THEN
35 rlm_core_sv.dlog(k_DEBUG,'Loading the oe lines tables PL/SQL table: ', counter );
36 END IF;
37 --
38 x_linecount := counter;
39 --
40 g_oe_line_tbl(x_linecount) := oe_order_pub.g_miss_line_rec;
41 --
42 RLM_TPA_SV.BuildOELine(g_oe_line_tbl(x_linecount),x_Op_tab(counter));
43 RLM_TPA_SV.BuildTpOELine(g_oe_line_tbl(x_linecount),x_Op_tab(counter));
44 --
45 END LOOP;
46 --
47 x_return_status := 'S';
48 --
49 IF (l_debug <> -1) THEN
50 rlm_core_sv.dpop(k_SDEBUG);
51 END IF;
52 --
53 EXCEPTION
54 --
55 WHEN OTHERS THEN
56 --
57 x_return_status := 'E';
58 --
59 IF (l_debug <> -1) THEN
60 rlm_core_sv.dlog(k_DEBUG,'When Other Exception',substr(sqlerrm,1,200));
61 rlm_core_sv.dpop(k_SDEBUG);
62 END IF;
63
64 END BuildOELineTab;
65
66
67 /*===========================================================================
68 PROCEDURE ProcessOperation(x_Op_tab IN rlm_rd_sv.t_generic_tab,
69 x_header_id IN NUMBER,
70 x_return_status IN OUT NOCOPY VARCHAR2)
71
72 ===========================================================================*/
73 PROCEDURE ProcessOperation(x_Op_tab IN rlm_rd_sv.t_generic_tab,
74 x_header_id IN NUMBER,
75 x_return_status IN OUT NOCOPY VARCHAR2)
76 IS
77 --
78 x_msg_count NUMBER;
79 l_return_status VARCHAR2(1);
80 x_msg_data VARCHAR2(4000);
81 x_oe_api_version NUMBER:=1;
82 e_BuildOELineTab EXCEPTION;
83 e_ProcessOrderFailed EXCEPTION;
84 v_DebugMode NUMBER;
85 v_FileName VARCHAR2(2000);
86 l_control_rec OE_GLOBALS.Control_Rec_Type := OE_GLOBALS.G_MISS_CONTROL_REC;
87 l_line_adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
88 v_line_id NUMBER;
89 x_msg_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
90 x_token FND_NEW_MESSAGES.TYPE%TYPE;
91 v_msg_text VARCHAR2(32000);
92 l_start_time NUMBER;
93 l_end_time NUMBER;
94 l_oe_line_tbl_out oe_order_pub.line_tbl_type;
95 --
96 BEGIN
97 --
98 IF (l_debug <> -1) THEN
99 rlm_core_sv.dpush(k_SDEBUG,'ProcessOperation');
100 END IF;
101 --
102 BuildOELineTab(x_Op_Tab,x_Return_Status);
103 --
104 IF x_Return_Status = 'E' THEN
105 raise e_BuildOELineTab;
106 END IF;
107 --
108 IF g_oe_line_tbl.count >0 THEN
109 --
110 IF (l_debug <> -1) THEN
111 rlm_core_sv.dlog(k_DEBUG,'starting insert oe lines');
112 END IF;
113 --
114 fnd_profile.get(rlm_core_sv.C_DEBUG_PROFILE, v_DebugMode);
115 --
116 oe_debug_pub.add('Calling Process_order from DSP',1);
117 --
118 IF (l_debug <> -1) THEN
119 rlm_core_sv.dlog(k_DEBUG,'RLM DEBUG PROFILE :',v_DebugMode);
120 rlm_core_sv.dlog(k_DEBUG,'IS OM Debug On:', OE_DEBUG_PUB.IsDebugOn);
121 rlm_core_sv.dlog(k_DEBUG,'OM Debug Level:', to_char(OE_DEBUG_PUB.G_DEBUG_LEVEL));
122 rlm_core_sv.dlog(k_DEBUG,'G_UI_FLAG',OE_GLOBALS.G_UI_FLAG);
123 rlm_core_sv.dlog(k_DEBUG,'G_DEBUG_MODE',OE_DEBUG_PUB.G_DEBUG_MODE);
124 rlm_core_sv.dlog(k_DEBUG,'See OE DEBUG FILE for details on process Order API errors');
125 rlm_core_sv.dlog(k_DEBUG,'Om Debug File dir:',OE_DEBUG_PUB.G_DIR);
126 END IF;
127 --
128 v_FileName := OE_DEBUG_PUB.set_debug_mode ('FILE');
129 --
130 IF (l_debug <> -1) THEN
131 rlm_core_sv.dlog(k_DEBUG,'Om Debug File name:',v_FileName);
132 END IF;
133 --
134 -- This setting ensures the pl/sql table returned by Process
135 -- order api contains messages from workflow scheduling activities
136
137 OE_STANDARD_WF.Save_Messages_Off;
138 --
139 /* setting the debug mode back CONC so we could see
140 the debug messages in both the log file and the request log */
141 --
142 v_FileName := OE_DEBUG_PUB.set_debug_mode ('CONC');
143 --
144 IF (l_debug <> -1) THEN
145 rlm_core_sv.dlog(k_DEBUG,'x_Op_tab(1).schedule_type', x_Op_tab(1).schedule_type);
146 END IF;
147 --
148 SELECT hsecs INTO l_start_time from v$timer;
149 --
150 IF x_Op_tab(1).schedule_type <> k_SEQUENCED THEN
151 --
152 OE_Order_GRP.Process_order
153 ( p_api_version_number => x_oe_api_version
154 , p_init_msg_list => FND_API.G_TRUE
155 , p_return_values => FND_API.G_FALSE
156 , p_commit => FND_API.G_FALSE
157 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
158 , p_control_rec => OE_GLOBALS.G_MISS_CONTROL_REC
159 , p_api_service_level => OE_GLOBALS.G_ALL_SERVICE
160 , x_return_status => l_return_status
161 , x_msg_count => x_msg_count
162 , x_msg_data => x_msg_data
163 , p_line_tbl => g_oe_line_tbl
164 , x_header_rec => g_oe_header_out_rec
165 , x_header_val_rec => g_oe_header_val_out_rec
166 , x_Header_Adj_tbl => g_oe_Header_Adj_out_tbl
167 , x_Header_Adj_val_tbl => g_oe_Header_Adj_val_out_tbl
168 , x_Header_price_Att_tbl => g_oe_Header_price_Att_out_tbl
169 , x_Header_Adj_Att_tbl => g_oe_Header_Adj_Att_out_tbl
170 , x_Header_Adj_Assoc_tbl => g_oe_Header_Adj_Assoc_out_tbl
171 , x_Header_Scredit_tbl => g_oe_Header_Scredit_out_tbl
172 , x_Header_Scredit_val_tbl => g_oe_Hdr_Scdt_val_out_tbl
173 , x_line_tbl => l_oe_line_tbl_out
174 , x_line_val_tbl => g_oe_line_val_out_tbl
175 , x_Line_Adj_tbl => g_oe_line_Adj_out_tbl
176 , x_Line_Adj_val_tbl => g_oe_line_Adj_val_out_tbl
177 , x_Line_price_Att_tbl => g_oe_Line_price_Att_out_tbl
178 , x_Line_Adj_Att_tbl => g_oe_Line_Adj_Att_out_tbl
179 , x_Line_Adj_Assoc_tbl => g_oe_Line_Adj_Assoc_out_tbl
180 , x_Line_Scredit_tbl => g_oe_line_scredit_out_tbl
181 , x_Line_Scredit_val_tbl => g_oe_line_scredit_val_out_tbl
182 , x_Lot_Serial_tbl => g_oe_lot_serial_out_tbl
183 , x_Lot_Serial_val_tbl => g_oe_lot_serial_val_out_tbl
184 , x_Action_Request_tbl => g_oe_Action_Request_out_Tbl
185 );
186 --
187 ELSE
188 --
189 l_control_rec.controlled_operation := TRUE;
190 l_control_rec.process_partial := TRUE;
191 --
192 OE_Order_GRP.Process_order
193 ( p_api_version_number => x_oe_api_version
194 , p_init_msg_list => FND_API.G_TRUE
195 , p_control_rec => l_control_rec
196 , x_return_status => l_return_status
197 , x_msg_count => x_msg_count
198 , x_msg_data => x_msg_data
199 , p_line_tbl => g_oe_line_tbl
200 , p_line_adj_tbl => l_line_adj_tbl
201 , x_header_rec => g_oe_header_out_rec
202 , x_header_val_rec => g_oe_header_val_out_rec
203 , x_Header_Adj_tbl => g_oe_Header_Adj_out_tbl
204 , x_Header_Adj_val_tbl => g_oe_Header_Adj_val_out_tbl
205 , x_Header_price_Att_tbl => g_oe_Header_price_Att_out_tbl
206 , x_Header_Adj_Att_tbl => g_oe_Header_Adj_Att_out_tbl
207 , x_Header_Adj_Assoc_tbl => g_oe_Header_Adj_Assoc_out_tbl
208 , x_Header_Scredit_tbl => g_oe_Header_Scredit_out_tbl
209 , x_Header_Scredit_val_tbl => g_oe_Hdr_Scdt_val_out_tbl
210 , x_line_tbl => l_oe_line_tbl_out
211 , x_line_val_tbl => g_oe_line_val_out_tbl
212 , x_Line_Adj_tbl => g_oe_line_Adj_out_tbl
213 , x_Line_Adj_val_tbl => g_oe_line_Adj_val_out_tbl
214 , x_Line_price_Att_tbl => g_oe_Line_price_Att_out_tbl
215 , x_Line_Adj_Att_tbl => g_oe_Line_Adj_Att_out_tbl
216 , x_Line_Adj_Assoc_tbl => g_oe_Line_Adj_Assoc_out_tbl
217 , x_Line_Scredit_tbl => g_oe_line_scredit_out_tbl
218 , x_Line_Scredit_val_tbl => g_oe_line_scredit_val_out_tbl
219 , x_Lot_Serial_tbl => g_oe_lot_serial_out_tbl
220 , x_Lot_Serial_val_tbl => g_oe_lot_serial_val_out_tbl
221 , x_Action_Request_tbl => g_oe_Action_Request_out_Tbl
222 );
223 --
224 END IF;
225 --
226 SELECT hsecs INTO l_end_time from v$timer;
227 --
228 v_msg_text :='no of lines in g_oe_line_tbl - '|| g_oe_line_tbl.LAST;
229 fnd_file.put_line(fnd_file.log, v_msg_text);
230 v_msg_text := 'Return Status - ' || l_return_status;
231 fnd_file.put_line(fnd_file.log, v_msg_text);
232 --
233 v_msg_text :='Time spent in OE call - '|| (l_end_time-l_start_time)/100;
234 fnd_file.put_line(fnd_file.log, v_msg_text);
235 --
236 x_return_status := l_return_status;
237 --
238 IF (l_debug <> -1) THEN
239 --
240 rlm_core_sv.dlog(k_DEBUG,'# of lines in input tbl',g_oe_line_tbl.LAST);
241 rlm_core_sv.dlog(k_DEBUG,'# of lines in output table',l_oe_line_tbl_out.LAST);
242 rlm_core_sv.dlog(k_DEBUG,'Process Order return Status',x_return_Status);
243 rlm_core_sv.dlog(k_DEBUG,'Process Order Error Count',x_msg_count);
244 rlm_core_sv.dlog(k_DEBUG,'Process Order Error',x_msg_data);
245 END IF;
246 --
247 g_total_lines := g_total_lines + g_oe_line_tbl.LAST;
248 g_total_time := g_total_time + ((l_end_time-l_start_time)/100);
249 --
250 v_msg_text := 'Total number of lines sent to OM - '||g_total_lines ;
251 fnd_file.put_line(fnd_file.log, v_msg_text);
252 v_msg_text := 'Total Time - ' || g_total_time;
253 fnd_file.put_line(fnd_file.log, v_msg_text);
254 --
255 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
256 l_return_status = FND_API.G_RET_STS_ERROR THEN
257 --
258 RAISE e_ProcessOrderFailed;
259 --
260 END IF;
261 --
262 x_token := 'WARN';
263 x_msg_name := 'RLM_PROCESS_ORDER_WARN';
264 --
265 InsertOMMessages(x_header_id,
266 x_Op_tab(1).customer_item_id,
267 x_msg_count,rlm_message_sv.k_warn_level,
268 x_token,
269 x_msg_name);
270 --
271 END IF;
272 --
273 IF (l_debug <> -1) THEN
274 rlm_core_sv.dpop(k_SDEBUG,'successful');
275 END IF;
276 --
277 --x_return_status := 'E';
278 --
279 EXCEPTION
280 --
281 WHEN e_ProcessOrderFailed THEN
282 --
283 x_token := 'ERROR';
284 x_msg_name := 'RLM_OE_API_FAILED';
285 --
286 InsertOMMessages(x_header_id,
287 x_Op_tab(1).customer_item_id,
288 x_msg_count,rlm_message_sv.k_error_level,
289 x_token,
290 x_msg_name);
291 --
292 IF x_Op_tab(1).schedule_type = k_SEQUENCED THEN/*2342919*/
293 --
294 FOR s IN 1 .. l_oe_line_tbl_out.COUNT LOOP
295 --
296 IF (l_debug <> -1) THEN
297 rlm_core_sv.dlog(k_DEBUG, 'Line return status', l_oe_line_tbl_out(s).return_status);
298 END IF;
299 --
300 IF l_oe_line_tbl_out(s).return_status <> FND_API.G_RET_STS_SUCCESS THEN
301 --
302 UPDATE rlm_schedule_lines_all
303 SET process_status = rlm_core_sv.k_PS_ERROR
304 WHERE line_id = l_oe_line_tbl_out(s).source_document_line_id;
305 --
306 BEGIN
307 --
308 UPDATE rlm_interface_lines_all
309 SET process_status = rlm_core_sv.k_PS_ERROR
310 WHERE line_id =
311 (
312 SELECT interface_line_id
313 FROM rlm_schedule_lines_all
314 WHERE line_id = l_oe_line_tbl_out(s).source_document_line_id
315 );
316 --
317 IF (l_debug <> -1) THEN
318 rlm_core_sv.dlog(k_DEBUG, 'No of Interface Lines updated', SQL%ROWCOUNT);
319 END IF;
320 --
321 EXCEPTION
322 --
323 WHEN OTHERS THEN
324 --
325 IF (l_debug <> -1) THEN
326 rlm_core_sv.dlog(k_DEBUG, 'Interface Line not found for
327 Source Document Line Id', l_oe_line_tbl_out(s).source_document_line_id);
328 END IF;
329 --
330 END;
331 --
332 ELSE
333 --
334 UPDATE rlm_schedule_lines_all
335 SET process_status = rlm_core_sv.k_PS_PROCESSED
336 WHERE line_id = l_oe_line_tbl_out(s).source_document_line_id;
337 --
338 BEGIN
339 --
340 UPDATE rlm_interface_lines_all
341 SET process_status = rlm_core_sv.k_PS_PROCESSED
342 WHERE line_id =
343 (
344 SELECT interface_line_id
345 FROM rlm_schedule_lines_all
346 WHERE line_id = l_oe_line_tbl_out(s).source_document_line_id
347 );
348 --
349 IF (l_debug <> -1) THEN
350 rlm_core_sv.dlog(k_DEBUG, 'No of Interface Lines updated', SQL%ROWCOUNT);
351 END IF;
352 --
353 EXCEPTION
354 --
355 WHEN OTHERS THEN
356 --
357 IF (l_debug <> -1) THEN
358 rlm_core_sv.dlog(k_DEBUG, 'Interface Line not found for
359 Source Document Line Id', l_oe_line_tbl_out(s).source_document_line_id);
360 END IF;
361 --
362 END;
363 --
364 END IF;
365 --
366 END LOOP;
367 --
368 END IF;/*2342919*/
369 --
370 IF (l_debug <> -1) THEN
371 rlm_core_sv.dpop(k_SDEBUG);
372 END IF;
373 --
374 WHEN e_BuildOELineTab THEN
375 --
376 rlm_message_sv.app_error(
377 x_ExceptionLevel => rlm_message_sv.k_error_level,
378 x_MessageName => 'RLM_BUILD_OE_LINE_TAB',
379 x_InterfaceHeaderId => x_header_id,
380 x_InterfaceLineId => NULL,
381 x_ScheduleHeaderId => x_Op_Tab(1).schedule_header_id,
382 x_ScheduleLineId => NULL,
383 x_OrderHeaderId => NULL,
384 x_OrderLineId => NULL,
385 x_Token1 => 'ERROR',
386 x_value1 => substr(sqlerrm,1,200));
387 --
388 IF (l_debug <> -1) THEN
389 rlm_core_sv.dlog(k_DEBUG,'Build OE Line Tab returned failed', x_return_Status);
390 rlm_core_sv.dlog(k_DEBUG,'Error',substr(sqlerrm,1,200));
391 rlm_core_sv.dpop(k_SDEBUG);
392 END IF;
393 --
394 WHEN OTHERS THEN
395 --
396 IF (l_debug <> -1) THEN
397 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
398 END IF;
399 --
400 raise;
401 --
402 END ProcessOperation;
403
404 /*===========================================================================
405
406 PROCEDURE InsertOMMessages
407
408 ===========================================================================*/
409
410 PROCEDURE InsertOMMessages(x_header_id IN NUMBER,
411 x_customer_item_id IN NUMBER,
412 x_msg_count IN NUMBER,
413 x_msg_level IN VARCHAR2,
414 x_token IN VARCHAR2,
415 x_msg_name IN VARCHAR2)
416 IS
417 --
418 x_msg VARCHAR2(4000);
419 v_interface_line_id NUMBER;
420 v_schedule_header_id NUMBER;
421 v_order_header_id NUMBER;
422 v_request_date VARCHAR2(150);
423 l_entity_code VARCHAR2(30);
424 l_entity_ref VARCHAR2(50);
425 l_entity_id NUMBER;
426 l_header_id NUMBER;
427 l_line_id NUMBER;
428 l_order_source_id NUMBER;
429 l_orig_sys_document_ref VARCHAR2(50);
430 l_orig_sys_line_ref VARCHAR2(50);
431 l_orig_sys_shipment_ref VARCHAR2(50);
432 l_change_sequence VARCHAR2(50);
433 l_source_document_type_id NUMBER;
434 l_source_document_id NUMBER;
435 l_source_document_line_id NUMBER;
436 l_attribute_code VARCHAR2(30);
437 l_constraint_id NUMBER;
438 l_process_activity NUMBER;
439 l_transaction_id NUMBER;
440 l_notification_flag VARCHAR2(1) := 'N' ;
441 l_type VARCHAR2(30) ;
442 l_msg_level VARCHAR2(10); -- 4129069
443 v_PO_msg VARCHAR2(200); -- Bug 4297984
444 --
445 BEGIN
446 --
447 IF (l_debug <> -1) THEN
448 rlm_core_sv.dpush(k_SDEBUG,'InsertOMMessages');
449 rlm_core_sv.dlog(k_DEBUG,'x_msg_count',x_msg_count);
450 rlm_core_sv.dlog(k_DEBUG,'x_msg_name',x_msg_name);
451 rlm_core_sv.dlog(k_DEBUG,'x_msg_level',x_msg_level);
452 rlm_core_sv.dlog(k_DEBUG,'x_token',x_token);
453 rlm_core_sv.dlog(k_DEBUG,'x_header_id',x_header_id);
454 rlm_core_sv.dlog(k_DEBUG,'x_customer_item_id', x_customer_item_id);
455 rlm_core_sv.dlog(k_DEBUG,'oe_msg_pub.count_msg',oe_msg_pub.count_msg);
456 END IF;
457 --
458 -- Get message count and data
459 -- Bug 4297984
460 fnd_message.set_name ('ONT','OE_VAL_DUP_PO_NUMBER');
461 v_PO_msg := fnd_message.get;
462 --
463 IF (l_debug <> -1) THEN
464 rlm_core_sv.dlog(k_DEBUG,'v_PO_msg',v_PO_msg);
465 END IF;
466 --
467 IF x_msg_count > 0 THEN
468 --{
469 FOR I in 1..x_msg_count LOOP
470 --
471 x_msg := oe_msg_pub.get(p_msg_index => I,
472 p_encoded => 'F');
473 --
474 IF (l_debug <> -1) THEN
475 rlm_core_sv.dlog(k_DEBUG, 'Message at index', I);
476 rlm_core_sv.dlog(k_DEBUG,'Message Found', substr(x_msg,1,200));
477 rlm_core_sv.dlog(k_DEBUG,'get message context');
478 END IF;
479 --
480 IF (substr(x_msg,1,200) <> v_PO_msg) THEN -- Bug 4297984
481 --
482 oe_msg_pub.Get_msg_context(
483 p_msg_index => I
484 ,x_entity_code => l_entity_code
485 ,x_entity_ref => l_entity_ref
486 ,x_entity_id => l_entity_id
487 ,x_header_id => l_header_id
488 ,x_line_id => l_line_id
489 ,x_order_source_id => l_order_source_id
490 ,x_orig_sys_document_ref => l_orig_sys_document_ref
491 ,x_orig_sys_line_ref => l_orig_sys_line_ref
492 ,x_orig_sys_shipment_ref => l_orig_sys_shipment_ref
493 ,x_change_sequence => l_change_sequence
494 ,x_source_document_type_id => l_source_document_type_id
495 ,x_source_document_id => l_source_document_id
496 ,x_source_document_line_id => l_source_document_line_id
497 ,x_attribute_code => l_attribute_code
498 ,x_constraint_id => l_constraint_id
499 ,x_process_activity => l_process_activity
500 ,x_notification_flag => l_notification_flag
501 ,x_type => l_type
502 );
503 --
504 IF (l_debug <> -1) THEN
505 rlm_core_sv.dlog(k_DEBUG, 'schedule line',
506 l_source_document_line_id);
507 rlm_core_sv.dlog(k_DEBUG, 'ProcessOrderAPI Error',
508 substr(x_msg,1,200));
509 END IF;
510 --
511 IF NVL(l_source_document_line_id,FND_API.G_MISS_NUM) <>
512 FND_API.G_MISS_NUM THEN
513 BEGIN
514 --
515 SELECT interface_line_id, header_id, order_header_id, industry_attribute2
516 INTO v_interface_line_id, v_schedule_header_id, v_order_header_id, v_request_date
517 FROM rlm_schedule_lines
518 WHERE line_id = l_source_document_line_id;
519 --
520 IF (l_debug <> -1) THEN
521 rlm_core_sv.dlog(k_DEBUG,'interface line',v_interface_line_id);
522 END IF;
523 --
524 EXCEPTION
525 --
526 WHEN OTHERS THEN
527 --
528 IF (l_debug <> -1) THEN
529 rlm_core_sv.dlog(k_DEBUG,'Could not get interface line');
530 END IF;
531 --
532 END;
533 END IF;
534 --
535 IF (l_debug <> -1) THEN
536 rlm_core_sv.dlog(k_DEBUG,'l_source_document_id', l_source_document_id);
537 rlm_core_sv.dlog(k_DEBUG,'schedule_header_id', v_schedule_header_id);
538 rlm_core_sv.dlog(k_DEBUG,'l_header_id', l_header_id);
539 rlm_core_sv.dlog(k_DEBUG,'order_header_id', v_order_header_id);
540 rlm_core_sv.dlog(k_DEBUG,'x_msg_level', x_msg_level);
541 rlm_core_sv.dlog(k_DEBUG,'x_msg_name', x_msg_name);
542 END IF;
543 --
544 oe_debug_pub.add(substr(x_msg,1,200));
545 --
546 --
547 -- Bug 4129069 : Set the message level depending on the seeded error
548 -- type only if Process Order API returned Error Status.
549 --
550 IF x_msg_name = 'RLM_OE_API_FAILED' THEN
551 --
552 IF l_type = 'ERROR' THEN
553 l_msg_level := x_msg_level;
554 ELSE
555 l_msg_level := rlm_message_sv.k_info_level;
556 END IF;
557 --
558 ELSE
559 l_msg_level := x_msg_level;
560 END IF;
561 --
562 IF (l_debug<> -1) THEN
563 rlm_core_sv.dlog(k_DEBUG, 'l_msg_level', l_msg_level);
564 END IF;
565 --
566 rlm_message_sv.app_error(
567 x_ExceptionLevel => l_msg_level,
568 x_MessageName => x_msg_name,
569 x_InterfaceHeaderId => x_header_id,
570 x_InterfaceLineId => v_interface_line_id,
571 x_ScheduleHeaderId => v_schedule_header_id,
572 x_ScheduleLineId => l_source_document_line_id,
573 x_OrderHeaderId => l_header_id,
574 x_OrderLineId => l_line_id,
575 x_Token1 => x_token,
576 x_value1 => substr(x_msg,1,200),
577 x_Token2 => 'CUST_ITEM',
578 x_value2 => rlm_core_sv.get_item_number(x_customer_item_id),
579 x_Token3 => 'REQ_DATE',
580 x_value3 => v_request_date);
581 --
582 IF (l_debug <> -1) THEN
583 rlm_core_sv.dlog(k_DEBUG,'x_msg', substr(x_msg,1,200));
584 END IF;
585 --
586 END IF; -- Bug 4297984
587 --
588 END LOOP;
589 --}
590 ELSIF (x_msg_count = 0 AND
591 (x_msg_level = 'U' OR x_msg_level = 'E')) THEN
592 --{
593 IF (l_debug <> -1) THEN
594 rlm_core_sv.dlog(k_DEBUG, 'Inserting RLM_PROCESS_ORDER_ERROR_E msg');
595 END IF;
596 --
597 rlm_message_sv.app_error(
598 x_ExceptionLevel => rlm_message_sv.k_error_level,
599 x_MessageName => 'RLM_PROCESS_ORDER_ERROR_E',
600 x_InterfaceHeaderId => x_header_id,
601 x_InterfaceLineId => NULL,
602 x_ScheduleHeaderId => NULL,
603 x_ScheduleLineId => NULL,
604 x_OrderHeaderId => NULL,
605 x_OrderLineId => NULL,
606 x_Token1 => 'ERROR',
607 x_value1 => substr(sqlerrm,1,200),
608 x_Token2 => 'CUST_ITEM',
609 x_Value2 => rlm_core_sv.get_item_number(x_customer_item_id)
610 );
611 --}
612 END IF;
613 --
614 IF (l_debug <> -1) THEN
615 rlm_core_sv.dpop(k_SDEBUG,'successful');
616 END IF;
617 --
618 EXCEPTION
619 --
620 WHEN OTHERS THEN
621 --
622 IF (l_debug <> -1) THEN
623 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
624 END IF;
625 --
626 raise;
627 END InsertOMMessages;
628
629 /*===========================================================================
630
631 FUNCTION CallProcessConstraintAPI
632
633 ===========================================================================*/
634
635 FUNCTION CallProcessConstraintAPI(x_Key_rec IN rlm_rd_sv.t_Key_rec,
636 x_Qty_rec OUT NOCOPY rlm_rd_sv.t_Qty_rec,
637 x_Operation IN VARCHAR2,
638 x_OperationQty IN NUMBER)
639 RETURN BOOLEAN
640 IS
641 x_msg_count NUMBER;
642 x_msg VARCHAR2(32000);
643 l_return_status VARCHAR2(1);
644 x_msg_data VARCHAR2(4000);
645 x_oe_api_version NUMBER:=1;
646 x_api_service_level VARCHAR2(30):= OE_GLOBALS.G_CHECK_SECURITY_ONLY;
647 e_ProcessConstraint EXCEPTION;
648 l_control_rec OE_GLOBALS.control_rec_type;
649 l_line_rec oe_order_pub.line_rec_type;
650 l_line_tbl oe_order_pub.Line_Tbl_Type;
651 l_header_out_rec oe_order_pub.Header_Rec_Type;
652 l_header_adj_out_tbl oe_order_pub.Header_Adj_Tbl_Type;
653 l_header_val_rec oe_order_pub.header_val_rec_type;
654 l_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type;
655 l_Header_price_Att_out_tbl oe_order_pub.Header_Price_Att_Tbl_Type;
656 l_Header_Adj_Att_out_tbl oe_order_pub.Header_Adj_Att_Tbl_Type;
657 l_Header_Adj_Assoc_out_tbl oe_order_pub.Header_Adj_Assoc_Tbl_Type;
658 l_Header_Scredit_out_tbl oe_order_pub.Header_Scredit_Tbl_Type;
659 l_header_Scredit_val_tbl oe_order_pub.header_Scredit_val_Tbl_Type;
660 l_line_out_tbl oe_order_pub.Line_Tbl_Type;
661 l_line_val_tbl oe_order_pub.Line_val_Tbl_Type;
662 l_line_adj_val_tbl oe_order_pub.line_Adj_val_Tbl_Type;
663 l_line_adj_out_tbl oe_order_pub.line_Adj_Tbl_Type;
664 l_Line_price_Att_out_tbl oe_order_pub.Line_price_Att_Tbl_Type;
665 l_Line_Adj_Att_out_tbl oe_order_pub.Line_Adj_Att_Tbl_Type;
666 l_Line_Adj_Assoc_out_tbl oe_order_pub.Line_Adj_Assoc_Tbl_Type;
667 l_Line_Scredit_out_tbl oe_order_pub.Line_Scredit_Tbl_Type;
668 l_Line_Scredit_val_tbl oe_order_pub.Line_Scredit_val_Tbl_Type;
669 l_action_request_out_tbl oe_order_pub.request_tbl_type;
670 l_Lot_Serial_tbl oe_order_pub.Lot_Serial_Tbl_Type;
671 l_Lot_Serial_val_tbl oe_order_pub.Lot_Serial_val_Tbl_Type;
672 b_Result BOOLEAN := FALSE;
673 v_DebugMode NUMBER;
674 x_api_version_number NUMBER := 1;
675 v_interface_line NUMBER;
676 x_msg_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
677 x_token FND_NEW_MESSAGES.TYPE%TYPE;
678 x_msg_level VARCHAR2(10);
679 --
680 BEGIN
681 --
682 IF (l_debug <> -1) THEN
683 rlm_core_sv.dpush(k_SDEBUG,'CallProcessConstraintAPI');
684 rlm_core_sv.dlog(k_DEBUG,'x_Operation',x_Operation);
685 END IF;
686 --
687 l_line_tbl(1) := oe_order_pub.g_miss_line_rec;
688 RLM_TPA_SV.BuildOELine(l_line_tbl(1),x_Key_rec.req_rec);
689 l_line_tbl(1).line_id := x_Key_rec.dem_rec.line_id;
690 l_line_tbl(1).operation := x_Operation;
691 l_line_tbl(1).ordered_quantity := x_OperationQty;
692 --
693 IF (l_debug <> -1) THEN
694 rlm_core_sv.dlog(k_DEBUG,'l_line_tbl(1).operation',
695 l_line_tbl(1).operation);
696 rlm_core_sv.dlog(k_DEBUG,'l_line_tbl(1).line_id',
697 l_line_tbl(1).line_id);
698 rlm_core_sv.dlog(k_DEBUG,'l_line_tbl(1).ordered_quantity',
699 l_line_tbl(1).ordered_quantity);
700 END IF;
701 --
702 fnd_profile.get(rlm_core_sv.C_DEBUG_PROFILE, v_DebugMode);
703 --
704 oe_debug_pub.add('Calling Process_order from DSP');
705 --
706 IF (l_debug <> -1) THEN
707 rlm_core_sv.dlog(k_DEBUG,'IS OM Debug On:', OE_DEBUG_PUB.G_DEBUG);
708 rlm_core_sv.dlog(k_DEBUG,'OM Debug Level:', to_char(OE_DEBUG_PUB.G_DEBUG_LEVEL));
709 rlm_core_sv.dlog(k_DEBUG,'G_UI_FLAG',OE_GLOBALS.G_UI_FLAG);
710 rlm_core_sv.dlog(k_DEBUG,'ISDebugOn',OE_DEBUG_PUB.ISDebugOn);
711 rlm_core_sv.dlog(k_DEBUG,'G_DEBUG_MODE',OE_DEBUG_PUB.G_DEBUG_MODE);
712 rlm_core_sv.dlog(k_DEBUG,'G_FILE',OE_DEBUG_PUB.G_FILE);
713 rlm_core_sv.dlog(k_DEBUG,'See OE DEBUG FILE for process Constraints in DSP concurrent request log');
714 END IF;
715 --
716 oe_order_grp.Process_order
717 ( p_api_version_number => x_api_version_number
718 , p_api_service_level => x_api_service_level
719 , p_init_msg_list => FND_API.G_TRUE
720 , x_return_status => l_return_status
721 , x_msg_count => x_msg_count
722 , x_msg_data => x_msg_data
723 , p_line_tbl => l_line_tbl
724 , x_header_rec => l_header_out_rec
725 , x_header_val_rec => l_header_val_rec
726 , x_Header_Adj_tbl => l_Header_Adj_out_tbl
727 , x_Header_Adj_val_tbl => l_Header_Adj_val_tbl
728 , x_Header_price_Att_tbl => l_Header_price_Att_out_tbl
729 , x_Header_Adj_Att_tbl => l_Header_Adj_Att_out_tbl
730 , x_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_out_tbl
731 , x_Header_Scredit_tbl => l_Header_Scredit_out_tbl
732 , x_Header_Scredit_val_tbl => l_Header_Scredit_val_tbl
733 , x_line_tbl => l_line_out_tbl
734 , x_line_val_tbl => l_line_val_tbl
735 , x_Line_Adj_tbl => l_Line_Adj_out_tbl
736 , x_Line_Adj_val_tbl => l_Line_Adj_val_tbl
737 , x_Line_price_Att_tbl => l_Line_price_Att_out_tbl
738 , x_Line_Adj_Att_tbl => l_Line_Adj_Att_out_tbl
739 , x_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_out_tbl
740 , x_Line_Scredit_tbl => l_Line_Scredit_out_tbl
741 , x_Line_Scredit_val_tbl => l_Line_Scredit_val_tbl
742 , x_Action_Request_tbl => l_Action_Request_out_Tbl
743 , x_lot_serial_tbl => l_lot_serial_tbl
744 , x_lot_serial_val_tbl => l_lot_serial_val_tbl
745 );
746 --
747 IF (l_debug <> -1) THEN
748 rlm_core_sv.dlog(k_DEBUG,'File Name',OE_DEBUG_PUB.G_FILE);
749 rlm_core_sv.dlog(k_DEBUG,'l_return_status',l_return_status);
750 END IF;
751 --
752 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
753 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
754 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
755 RAISE FND_API.G_EXC_ERROR;
756 ELSE
757 --
758 b_result := FALSE;
759 --
760 IF (l_debug <> -1) THEN
761 rlm_core_sv.dlog(k_DEBUG,'Process Order Error Count',x_msg_count);
762 rlm_core_sv.dlog(k_DEBUG,'Process Order Error',x_msg_data);
763 rlm_core_sv.dlog(k_DEBUG,'no of lines in g_oe_line_tbl',g_oe_line_tbl.LAST);
764 rlm_core_sv.dpop(k_SDEBUG,' no process constraints found -- returning false');
765 END IF;
766 --
767 END IF;
768 --
769 RETURN(b_Result);
770 --
771 EXCEPTION
772 --
773 WHEN FND_API.G_EXC_ERROR THEN
774 --
775 b_result := TRUE;
776 --
777 IF (l_debug <> -1) THEN
778 rlm_core_sv.dlog(k_DEBUG,'Process Order return Status
779 FND_API.G_RET_STS_ERROR');
780 rlm_core_sv.dlog(k_DEBUG,'Process Order Error Count',x_msg_count);
781 rlm_core_sv.dlog(k_DEBUG,'Process Order Error',x_msg_data);
782 END IF;
783 --
784 --x_Qty_rec.reconcile := l_line_out_Tbl(1).ordered_quantity;
785 --x_Qty_rec.available_to_cancel := l_line_out_Tbl(1).ordered_quantity;
786 --x_Qty_rec.shipped := l_line_out_Tbl(1).shipped_quantity;
787 --
788 x_msg_level := rlm_message_sv.k_warn_level;
789 x_msg_name := 'RLM_PROC_CONS_FOUND';
790 x_Token := 'CONSTRAINT';
791 --
792 IF (l_debug <> -1) THEN
793 rlm_core_sv.dlog(k_DEBUG,'header_id',x_Key_rec.req_rec.header_id);
794 rlm_core_sv.dlog(k_DEBUG,'line_id',x_Key_rec.req_rec.line_id);
795 rlm_core_sv.dlog(k_DEBUG,'schedule_header_id',
796 x_Key_rec.req_rec.schedule_header_id);
797 rlm_core_sv.dlog(k_DEBUG,'schedule_line_id',
798 x_Key_rec.req_rec.schedule_line_id);
799 rlm_core_sv.dlog(k_DEBUG,'dem_rec.header_id', x_Key_rec.dem_rec.header_id);
800 rlm_core_sv.dlog(k_DEBUG,'dem_rec.line_id', x_Key_rec.dem_rec.line_id);
801 END IF;
802 --
803 InsertOMMessages(x_Key_rec.req_rec.header_id,x_Key_rec.req_rec.customer_item_id,x_msg_count,
804 x_msg_level, x_token, x_msg_name);
805 --
806 IF (l_debug <> -1) THEN
807 rlm_core_sv.dpop(k_SDEBUG,'in process Constraint found');
808 END IF;
809 --
810 RETURN(b_Result);
811 --
812 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
813 --
814 b_result := TRUE;
815 --
816 IF (l_debug <> -1) THEN
817 rlm_core_sv.dlog(k_DEBUG,'Process Order return Status
818 FND_API.G_RET_STS_UNEXP_ERROR');
819 rlm_core_sv.dlog(k_DEBUG,'Process Order Error Count',x_msg_count);
820 rlm_core_sv.dlog(k_DEBUG,'Process Order Error',x_msg_data);
821 rlm_core_sv.dlog(k_DEBUG,'header_id',x_Key_rec.req_rec.header_id);
822 rlm_core_sv.dlog(k_DEBUG,'line_id',x_Key_rec.req_rec.line_id);
823 rlm_core_sv.dlog(k_DEBUG,'schedule_header_id',
824 x_Key_rec.req_rec.schedule_header_id);
825 rlm_core_sv.dlog(k_DEBUG,'schedule_line_id',
826 x_Key_rec.req_rec.schedule_line_id);
827 rlm_core_sv.dlog(k_DEBUG,'dem_rec.header_id',
828 x_Key_rec.dem_rec.header_id);
829 rlm_core_sv.dlog(k_DEBUG,'dem_rec.line_id',
830 x_Key_rec.dem_rec.line_id);
831 END IF;
832 --
833 x_msg_level := rlm_message_sv.k_warn_level;
834 x_msg_name := 'RLM_OE_API_ERROR';
835 x_Token := 'CONSTRAINT';
836 --
837 InsertOMMessages(x_Key_rec.req_rec.header_id,x_Key_rec.req_rec.customer_item_id,x_msg_count,
838 x_msg_level,x_token, x_msg_name);
839 --
840 IF (l_debug <> -1) THEN
841 rlm_core_sv.dpop(k_SDEBUG,'In process Constraint Unexpected error');
842 END IF;
843 --
844 RETURN(b_Result);
845 --
846 WHEN OTHERS THEN
847 --
848 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
849 --
850 IF (l_debug <> -1) THEN
851 rlm_core_sv.dlog(k_DEBUG,'Process Order return Status', l_return_status);
852 rlm_core_sv.dlog(k_DEBUG,'EXCEPTION',SUBSTR(SQLERRM,1,200));
853 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
854 END IF;
855 --
856 raise;
857
858 END CallProcessConstraintAPI;
859
860 /*===========================================================================
861
862 PROCEDURE GetIntransitQty
863
864 ===========================================================================*/
865 PROCEDURE GetIntransitQty (x_CustomerId In NUMBER,
866 x_ShipToId In NUMBER,
867 x_intmed_ship_to_org_id In NUMBER,--Bugfix 5911991
868 x_ShipFromOrgId In NUMBER,
869 x_InventoryItemId In NUMBER,
870 x_CustomerItemId In NUMBER,
871 x_OrderHeaderId In NUMBER,
872 x_BlanketNumber In NUMBER,
873 x_OrgId In NUMBER,
874 x_SchedType In VARCHAR2,
875 x_ShipperRecs In WSH_RLM_INTERFACE.t_shipper_rec,
876 x_ShipmentDate IN DATE,
877 x_MatchWithin IN RLM_CORE_SV.T_MATCH_REC,
878 x_MatchAcross IN RLM_CORE_SV.T_MATCH_REC,
879 x_Match_Rec IN WSH_RLM_INTERFACE.t_optional_match_rec,
880 x_header_id IN NUMBER,
881 x_InTransitQty OUT NOCOPY NUMBER,
882 x_return_status OUT NOCOPY VARCHAR2)
883 IS
884
885 e_APIExpError EXCEPTION;
886 e_APIUnExpError EXCEPTION;
887 v_summary VARCHAR2(3000);
888 v_details VARCHAR2(3000);
889 v_get_msg_count NUMBER;
890 -- global_atp
891 v_ship_from_org_id NUMBER;
892 v_MatchWithin RLM_CORE_SV.T_MATCH_REC;
893 v_MatchAcross RLM_CORE_SV.T_MATCH_REC;
894
895 BEGIN
896 --
897 IF (l_debug <> -1) THEN
898 rlm_core_sv.dpush(k_SDEBUG,'GetIntransitQty');
899 --
900 -- Call shipping API
901 --
902 rlm_core_sv.dlog(k_DEBUG,'x_CustomerId', x_CustomerId);
903 rlm_core_sv.dlog(k_DEBUG,'x_ShipToId', x_ShipToId);
904 rlm_core_sv.dlog(k_DEBUG,'x_intmed_ship_to_org_id', x_intmed_ship_to_org_id); --Bugfix 5911991
905 rlm_core_sv.dlog(k_DEBUG,'x_ShipFromOrgId', x_ShipFromOrgId);
906 rlm_core_sv.dlog(k_DEBUG,'x_InventoryItemId', x_InventoryItemId);
907 rlm_core_sv.dlog(k_DEBUG,'x_CustomerItemId', x_CustomerItemId);
908 rlm_core_sv.dlog(k_DEBUG,'x_OrderHeaderId', x_OrderHeaderId);
909 rlm_core_sv.dlog(k_DEBUG,'x_BlanketNumber', x_BlanketNumber);
910 rlm_core_sv.dlog(k_DEBUG,'x_return_status', x_return_status);
911 rlm_core_sv.dlog(k_DEBUG,'x_ShipmentDate', x_ShipmentDate);
912 rlm_core_sv.dlog(k_DEBUG,'x_orgId', x_OrgId);
913 END IF;
914 --
915 --global_atp
916
917 v_MatchWithin := x_MatchWithin;
918 v_MatchAcross := x_MatchAcross;
919
920 IF RLM_MANAGE_DEMAND_SV.IsATPItem(x_ShipFromOrgId,
921 x_InventoryItemId) THEN
922 --
923 v_ship_from_org_id := NULL;
924 v_MatchWithin.industry_attribute15 := 'N';
925 v_MatchAcross.industry_attribute15 := 'N';
926 --
927 ELSE
928 --
929 v_ship_from_org_id := x_ShipFromOrgId;
930 --
931 END IF;
932 --
933
934 -- To calculate intransit, do not match on industry_attribute2
935 v_MatchWithin.industry_attribute2 := 'N';
936 v_MatchAcross.industry_attribute2 := 'N';
937
938 -- To calculate intransit, do not match on request_date
939 v_MatchWithin.request_date := 'N';
940 v_MatchAcross.request_date := 'N';
941
942 -- To calculate intransit, do not match on schedule_date
943 v_MatchWithin.schedule_date := 'N';
944 v_MatchAcross.schedule_date := 'N';
945 --
946 IF (l_debug <> -1) THEN
947 rlm_core_sv.dlog(k_DEBUG,'v_MatchWithin.industry_attribute2', v_MatchWithin.industry_attribute2);
948 rlm_core_sv.dlog(k_DEBUG,'v_MatchAcross.industry_attribute2', v_MatchAcross.industry_attribute2);
949 rlm_core_sv.dlog(k_DEBUG,'v_MatchWithin.industry_attribute15', v_MatchWithin.industry_attribute15);
950 rlm_core_sv.dlog(k_DEBUG,'v_MatchAcross.industry_attribute15', v_MatchAcross.industry_attribute15);
951 rlm_core_sv.dlog(k_DEBUG,'v_MatchWithin.request_date', v_MatchWithin.request_date);
952 rlm_core_sv.dlog(k_DEBUG,'v_MatchAcross.request_date', v_MatchAcross.request_date);
953 rlm_core_sv.dlog(k_DEBUG,'v_MatchWithin.schedule_date', v_MatchWithin.schedule_date);
954 rlm_core_sv.dlog(k_DEBUG,'v_MatchAcross.schedule_date', v_MatchAcross.schedule_date);
955 END IF;
956 --
957 WSH_RLM_INTERFACE.Get_In_Transit_Qty(
958 p_source_code => 'OE',
959 p_customer_id => x_CustomerId,
960 p_ship_to_org_id => x_ShipToId,
961 p_intmed_ship_to_org_id => x_intmed_ship_to_org_id,--Bugfix 5911991
962 p_ship_from_org_id => v_ship_from_org_id,
963 p_inventory_item_id => x_InventoryItemId,
964 p_customer_item_id => x_CustomerItemId,
965 p_order_header_id => x_OrderHeaderId,
966 p_blanket_number => x_BlanketNumber,
967 p_org_id => x_OrgId,
968 p_schedule_type => x_SchedType,
969 p_shipper_recs => x_ShipperRecs,
970 p_shipment_date => x_ShipmentDate,
971 p_match_within_rule => v_MatchWithin,
972 p_match_across_rule => v_MatchAcross,
973 p_optional_match_rec => x_Match_Rec,
974 x_in_transit_qty => x_InTransitQty,
975 x_return_status => x_return_status);
976 --
977 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
978 --
979 RAISE e_APIExpError;
980 --
981 ELSIF x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
982 --
983 RAISE e_APIUnExpError;
984 --
985 ELSE
986 --
987 IF (l_debug <> -1) THEN
988 rlm_core_sv.dlog(k_DEBUG,'x_InTransitQty', x_InTransitQty);
989 rlm_core_sv.dlog(k_DEBUG,'x_return_status', x_return_status);
990 END IF;
991 --
992 END IF;
993 --
994 IF (l_debug <> -1) THEN
995 rlm_core_sv.dpop(k_SDEBUG);
996 END IF;
997 --
998 EXCEPTION
999 --
1000 WHEN e_APIExpError THEN
1001 --
1002 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1003 WSH_UTIL_CORE.Get_Messages('N',v_summary, v_details, v_get_msg_count);
1004 --
1005 IF (l_debug <> -1) THEN
1006 rlm_core_sv.dlog(k_DEBUG,'v_summary', v_summary );
1007 rlm_core_sv.dlog(k_DEBUG,'v_details', v_details );
1008 rlm_core_sv.dlog(k_DEBUG,'v_get_msg_count', v_get_msg_count );
1009 END IF;
1010 --
1011 rlm_message_sv.app_error(
1012 x_ExceptionLevel => rlm_message_sv.k_error_level,
1013 x_MessageName => 'RLM_INTRANSIT_API_FAILED',
1014 x_InterfaceHeaderId => x_header_id,
1015 x_InterfaceLineId => NULL,
1016 x_ScheduleHeaderId =>NULL,
1017 x_ScheduleLineId => NULL,
1018 x_OrderHeaderId => x_OrderheaderId,
1019 x_OrderLineId => NULL,
1020 x_Token1 => 'ERROR',
1021 x_value1 => substr(v_summary,1,200));
1022 --
1023 IF (l_debug <> -1) THEN
1024 rlm_core_sv.dlog(k_DEBUG,'x_return_status', x_return_status);
1025 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: GetInstransitQty');
1026 END IF;
1027 --
1028 WHEN e_APIUnExpError THEN
1029 --
1030 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1031 --
1032 WSH_UTIL_CORE.Get_Messages('N',v_summary, v_details, v_get_msg_count);
1033 --
1034 IF (l_debug <> -1) THEN
1035 rlm_core_sv.dlog(k_DEBUG,'v_summary', v_summary );
1036 rlm_core_sv.dlog(k_DEBUG,'v_details', v_details );
1037 rlm_core_sv.dlog(k_DEBUG,'v_get_msg_count', v_get_msg_count );
1038 END IF;
1039 --
1040 rlm_message_sv.app_error(
1041 x_ExceptionLevel => rlm_message_sv.k_error_level,
1042 x_MessageName => 'RLM_INTRANSIT_API_FAILED',
1043 x_InterfaceHeaderId => x_header_id,
1044 x_InterfaceLineId => NULL,
1045 x_ScheduleHeaderId =>NULL,
1046 x_ScheduleLineId => NULL,
1047 x_OrderHeaderId => x_OrderheaderId,
1048 x_OrderLineId => NULL,
1049 x_Token1 => 'ERROR',
1050 x_value1 => substr(v_summary,1,200));
1051 --
1052 IF (l_debug <> -1) THEN
1053 rlm_core_sv.dlog(k_DEBUG,'x_return_status', x_return_status);
1054 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: GetInstransitQty');
1055 END IF;
1056 --
1057 WHEN OTHERS THEN
1058 --
1059 IF (l_debug <> -1) THEN
1060 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
1061 END IF;
1062 --
1063 raise;
1064 --
1065 END GetIntransitQty;
1066
1067
1068 /*===========================================================================
1069
1070 PROCEDURE CheckShippingConstraints
1071
1072 ===========================================================================*/
1073
1074 PROCEDURE CheckShippingConstraints (
1075 x_source_code IN VARCHAR2,
1076 x_changed_attributes IN WSH_SHIPPING_CONSTRAINTS_PKG.ChangedAttributeRecType,
1077 x_return_status OUT NOCOPY VARCHAR2,
1078 x_action_allowed OUT NOCOPY VARCHAR2,
1079 x_action_message OUT NOCOPY VARCHAR2,
1080 x_ord_qty_allowed OUT NOCOPY NUMBER,
1081 x_log_level IN NUMBER,
1082 x_header_id IN NUMBER,
1083 x_order_header_id IN NUMBER)
1084 IS
1085
1086 e_APIUnExpError EXCEPTION;
1087 e_APIExpError EXCEPTION;
1088
1089 BEGIN
1090 --
1091 IF (l_debug <> -1) THEN
1092 rlm_core_sv.dpush(k_SDEBUG,'CheckShippingConstraints');
1093 --
1094 -- Call shipping API
1095 --
1096 rlm_core_sv.dlog(k_DEBUG,'x_source_code', x_source_code);
1097 rlm_core_sv.dlog(k_DEBUG,'x_changed_attributes.source_line_id',x_changed_attributes.source_line_id);
1098 rlm_core_sv.dlog(k_DEBUG,'x_changed_attributes.action_flag', x_changed_attributes.action_flag);
1099 rlm_core_sv.dlog(k_DEBUG,'x_log_level', x_log_level);
1100 rlm_core_sv.dlog(k_DEBUG,'x_header_id', x_header_id);
1101 rlm_core_sv.dlog(k_DEBUG,'x_order_header_id', x_order_header_id);
1102 END IF;
1103 --
1104 WSH_SHIPPING_CONSTRAINTS_PKG.check_shipping_constraints(
1105 p_source_code => x_source_code,
1106 p_changed_attributes => x_changed_attributes,
1107 x_return_status => x_return_status,
1108 x_action_allowed => x_action_allowed,
1109 x_action_message => x_action_message,
1110 x_ord_qty_allowed => x_ord_qty_allowed
1111 );
1112 --
1113 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1114 --
1115 RAISE e_APIUnExpError;
1116 --
1117 ELSIF x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
1118 --
1119 RAISE e_APIExpError;
1120 --
1121 ELSE
1122 --
1123 IF (l_debug <> -1) THEN
1124 rlm_core_sv.dlog(k_DEBUG,'x_return_status', x_return_status);
1125 rlm_core_sv.dlog(k_DEBUG,'x_action_allowed', x_action_allowed);
1126 rlm_core_sv.dlog(k_DEBUG,'x_action_message', x_action_message);
1127 rlm_core_sv.dlog(k_DEBUG,'x_ord_qty_allowed', x_ord_qty_allowed);
1128 END IF;
1129 --
1130 END IF;
1131 --
1132 IF (l_debug <> -1) THEN
1133 rlm_core_sv.dpop(k_SDEBUG);
1134 END IF;
1135 --
1136 EXCEPTION
1137 --
1138 WHEN e_APIUnExpError THEN
1139 --
1140 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1141 --
1142 --WSH_UTIL_CORE.Get_Messages('N',v_summary, v_details, v_get_msg_count);
1143 --
1144 IF (l_debug <> -1) THEN
1145 rlm_core_sv.dlog(k_DEBUG,'x_return_status', x_return_status);
1146 rlm_core_sv.dlog(k_DEBUG,'x_action_allowed', x_action_allowed);
1147 rlm_core_sv.dlog(k_DEBUG,'x_action_message', x_action_message);
1148 rlm_core_sv.dlog(k_DEBUG,'x_ord_qty_allowed', x_ord_qty_allowed);
1149 END IF;
1150 --
1151 rlm_message_sv.app_error(
1152 x_ExceptionLevel => rlm_message_sv.k_error_level,
1153 x_MessageName => 'RLM_WSH_CONSTRAINT_API_FAILED',
1154 x_InterfaceHeaderId => x_header_id,
1155 x_InterfaceLineId => NULL,
1156 x_ScheduleHeaderId =>NULL,
1157 x_ScheduleLineId => NULL,
1158 x_OrderHeaderId => x_order_header_id,
1159 x_OrderLineId => x_changed_attributes.source_line_id,
1160 x_Token1 => 'ERROR',
1161 x_value1 => SUBSTR(SQLERRM,1,200));
1162 --
1163 IF (l_debug <> -1) THEN
1164 rlm_core_sv.dlog(k_DEBUG,'x_return_status', x_return_status);
1165 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: CallShippingConstraintsAPI');
1166 END IF;
1167 --
1168
1169 WHEN e_APIExpError THEN
1170 --
1171 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1172 --
1173 --WSH_UTIL_CORE.Get_Messages('N',v_summary, v_details, v_get_msg_count);
1174 --
1175 IF (l_debug <> -1) THEN
1176 rlm_core_sv.dlog(k_DEBUG,'x_return_status', x_return_status);
1177 rlm_core_sv.dlog(k_DEBUG,'x_action_allowed', x_action_allowed);
1178 rlm_core_sv.dlog(k_DEBUG,'x_action_message', x_action_message);
1179 rlm_core_sv.dlog(k_DEBUG,'x_ord_qty_allowed', x_ord_qty_allowed);
1180 END IF;
1181 --
1182 rlm_message_sv.app_error(
1183 x_ExceptionLevel => rlm_message_sv.k_error_level,
1184 x_MessageName => 'RLM_WSH_CONSTRAINT_API_FAILED',
1185 x_InterfaceHeaderId => x_header_id,
1186 x_InterfaceLineId => NULL,
1187 x_ScheduleHeaderId =>NULL,
1188 x_ScheduleLineId => NULL,
1189 x_OrderHeaderId => x_order_header_id,
1190 x_OrderLineId => x_changed_attributes.source_line_id,
1191 x_Token1 => 'ERROR',
1192 x_value1 => x_action_message);
1193 --
1194 IF (l_debug <> -1) THEN
1195 rlm_core_sv.dlog(k_DEBUG,'x_return_status', x_return_status);
1196 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: CallShippingConstraintsAPI');
1197 END IF;
1198 --
1199
1200 WHEN OTHERS THEN
1201 --
1202 IF (l_debug <> -1) THEN
1203 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
1204 END IF;
1205 --
1206 RAISE;
1207 --
1208 END CheckShippingConstraints;
1209
1210
1211 /*===========================================================================
1212
1213 PROCEDURE SubmitDemandProcessor
1214
1215 ===========================================================================*/
1216 FUNCTION SubmitDemandProcessor(p_schedule_purpose_code VARCHAR2,
1217 p_from_date DATE,
1218 p_to_date DATE,
1219 p_from_customer_ext VARCHAR2,
1220 p_to_customer_ext VARCHAR2,
1221 p_from_ship_to_ext VARCHAR2,
1222 p_to_ship_to_ext VARCHAR2,
1223 p_run_edi_loader BOOLEAN) return NUMBER is
1224 x_req_id number;
1225 BEGIN
1226 if (p_run_edi_loader = FALSE) then
1227 x_req_id :=fnd_request.submit_request ('RLM',
1228 'RLMDSP',
1229 NULL,
1230 NULL,
1231 TRUE,
1232 p_schedule_purpose_code,
1233 p_from_date,
1234 p_to_date,
1235 p_from_customer_ext,
1236 p_to_customer_ext,
1237 p_from_ship_to_ext,
1238 p_to_ship_to_ext );
1239 commit;
1240 return x_req_id;
1241 else
1242 -- Submit EDI Loader
1243 -- Wait for Completion and then submit DSP
1244 null;
1245
1246 end if;
1247
1248 END SubmitDemandProcessor;
1249
1250 /*===========================================================================
1251
1252 PROCEDURE BuildTPOELine
1253
1254 ===========================================================================*/
1255 PROCEDURE BuildTPOELine(x_oe_line_rec IN OUT NOCOPY oe_order_pub.line_rec_type,
1256 x_Op_rec IN rlm_rd_sv.t_generic_rec)
1257 IS
1258 BEGIN
1259 --
1260 IF (l_debug <> -1) THEN
1261 rlm_core_sv.dpush(k_SDEBUG,'BuildTPOELine');
1262 END IF;
1263 --
1264 x_oe_line_rec.tp_attribute1 := x_Op_rec.tp_attribute1;
1265 x_oe_line_rec.tp_attribute2 := x_Op_rec.tp_attribute2;
1266 x_oe_line_rec.tp_attribute3:= x_Op_rec.tp_attribute3;
1267 x_oe_line_rec.tp_attribute4:= x_Op_rec.tp_attribute4;
1268 x_oe_line_rec.tp_attribute5:= x_Op_rec.tp_attribute5;
1269 x_oe_line_rec.tp_attribute6:= x_Op_rec.tp_attribute6;
1270 x_oe_line_rec.tp_attribute7:= x_Op_rec.tp_attribute7;
1271 x_oe_line_rec.tp_attribute8:= x_Op_rec.tp_attribute8;
1272 x_oe_line_rec.tp_attribute9:= x_Op_rec.tp_attribute9;
1273 x_oe_line_rec.tp_attribute10:= x_Op_rec.tp_attribute10;
1274 x_oe_line_rec.tp_attribute11:= x_Op_rec.tp_attribute11;
1275 x_oe_line_rec.tp_attribute12:= x_Op_rec.tp_attribute12;
1276 x_oe_line_rec.tp_attribute13:= x_Op_rec.tp_attribute13;
1277 x_oe_line_rec.tp_attribute14:= x_Op_rec.tp_attribute14;
1278 x_oe_line_rec.tp_attribute15:= x_Op_rec.tp_attribute15;
1279 x_oe_line_rec.tp_context:= x_Op_rec.tp_attribute_category;
1280 --
1281 IF (l_debug <> -1) THEN
1282 rlm_core_sv.dlog(k_DEBUG,'tp_attribute1',
1283 x_Op_rec.tp_attribute1);
1284 rlm_core_sv.dlog(k_DEBUG,'tp_attribute2',
1285 x_Op_rec.tp_attribute2);
1286 rlm_core_sv.dlog(k_DEBUG,'tp_attribute3',
1287 x_Op_rec.tp_attribute3);
1288 rlm_core_sv.dlog(k_DEBUG,'tp_attribute4',
1289 x_Op_rec.tp_attribute4);
1290 rlm_core_sv.dlog(k_DEBUG,'tp_attribute5',
1291 x_Op_rec.tp_attribute5);
1292 rlm_core_sv.dlog(k_DEBUG,'tp_attribute6',
1293 x_Op_rec.tp_attribute6);
1294 rlm_core_sv.dlog(k_DEBUG,'tp_attribute7',
1295 x_Op_rec.tp_attribute7);
1296 rlm_core_sv.dlog(k_DEBUG,'tp_attribute8',
1297 x_Op_rec.tp_attribute8);
1298 rlm_core_sv.dlog(k_DEBUG,'tp_attribute9',
1299 x_Op_rec.tp_attribute9);
1300 rlm_core_sv.dlog(k_DEBUG,'tp_attribute10',
1301 x_Op_rec.tp_attribute10);
1302 rlm_core_sv.dlog(k_DEBUG,'tp_attribute11',
1303 x_Op_rec.tp_attribute11);
1304 rlm_core_sv.dlog(k_DEBUG,'tp_attribute12',
1305 x_Op_rec.tp_attribute12);
1306 rlm_core_sv.dlog(k_DEBUG,'tp_attribute13',
1307 x_Op_rec.tp_attribute13);
1308 rlm_core_sv.dlog(k_DEBUG,'tp_attribute14',
1309 x_Op_rec.tp_attribute14);
1310 rlm_core_sv.dlog(k_DEBUG,'tp_attribute15',
1311 x_Op_rec.tp_attribute15);
1312 rlm_core_sv.dlog(k_DEBUG,'tp_context',
1313 x_Op_rec.tp_attribute_category);
1314 rlm_core_sv.dpop(k_SDEBUG);
1315 END IF;
1316 --
1317 EXCEPTION
1318 WHEN OTHERS THEN
1319 --
1320 IF (l_debug <> -1) THEN
1321 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
1322 END IF;
1323 --
1324 raise;
1325
1326 END BuildTpOELine;
1327
1328 /*===========================================================================
1329
1330 PROCEDURE BuildOELine
1331
1332 ===========================================================================*/
1333 PROCEDURE BuildOELine(x_oe_line_rec IN OUT NOCOPY oe_order_pub.line_rec_type,
1334 x_Op_rec IN rlm_rd_sv.t_generic_rec)
1335 IS
1336 b_ATP BOOLEAN;
1337 BEGIN
1338 --
1339 IF (l_debug <> -1) THEN
1340 rlm_core_sv.dpush(k_SDEBUG,'BuildOELine');
1341 END IF;
1342 --
1343 -- Added by JAUTOMO 11/02/00 Bug# 1467525
1344 -- Added by JAUTOMO 11/08/01 Bug# 2096968
1345
1346 -- global_atp
1347 -- Find out NOCOPY if the item is an ATP item or not
1348 IF x_Op_rec.ship_from_org_id IS NOT NULL
1349 AND x_Op_rec.inventory_item_id IS NOT NULL THEN
1350
1351 b_ATP := RLM_MANAGE_DEMAND_SV.IsATPItem(x_Op_rec.ship_from_org_id,
1352 x_Op_rec.inventory_item_id);
1353 ELSE
1354 IF RLM_RD_SV.g_ATP = RLM_RD_SV.k_ATP THEN
1355 b_ATP := TRUE;
1356 ELSE
1357 b_ATP := FALSE;
1358 END IF;
1359 END IF;
1360
1361 IF( x_Op_rec.ordered_quantity = 0
1362 AND x_Op_rec.operation = OE_GLOBALS.G_OPR_UPDATE) THEN
1363 --
1364 x_oe_line_rec.operation := x_Op_rec.operation;
1365 x_oe_line_rec.change_reason := 'EDI CANCELLATION';
1366 x_oe_line_rec.ordered_quantity := x_Op_rec.ordered_quantity;
1367 x_oe_line_rec.header_id := x_Op_rec.order_header_id;
1368 x_oe_line_rec.line_id := x_Op_rec.line_id;
1369 --
1370 IF (l_debug <> -1) THEN
1371 rlm_core_sv.dlog(k_DEBUG,'operation ',x_oe_line_rec.operation);
1372 rlm_core_sv.dlog(k_DEBUG,'ordered_quantity ',x_oe_line_rec.ordered_quantity);
1373 rlm_core_sv.dlog(k_DEBUG,'line_id ',x_oe_line_rec.line_id);
1374 END IF;
1375 --
1376 ELSE
1377 --
1378 IF x_Op_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
1379 --
1380 x_oe_line_rec.change_reason := 'EDI CANCELLATION';
1381 --
1382 END IF;
1383 --
1384 -- To UPDATE ATP item, DSP should pass MISSING ship_from_org_id
1385 -- and schedule_ship_date; DSP should not pass NULL.
1386
1387 -- global_atp
1388 --Bug 3675750 jckwok
1389
1390 IF NOT b_ATP THEN
1391 --
1392 IF x_Op_rec.ship_from_org_id IS NOT NULL THEN
1393 x_oe_line_rec.ship_from_org_id := x_Op_rec.ship_from_org_id;
1394 END IF;
1395 --
1396 IF x_Op_rec.schedule_date IS NOT NULL THEN
1397 x_oe_line_rec.schedule_ship_date := x_Op_rec.schedule_date;
1398 END IF;
1399 --
1400 END IF;
1401 --
1402 --
1403 IF (l_debug <> -1) THEN
1404 rlm_core_sv.dlog(k_DEBUG,'b_ATP', b_ATP);
1405 rlm_core_sv.dlog(k_DEBUG,'schedule_date',
1406 x_oe_line_rec.schedule_ship_date);
1407 rlm_core_sv.dlog(k_DEBUG,'ship_from_org_id',
1408 x_oe_line_rec.ship_from_org_id);
1409 rlm_core_sv.dlog(k_DEBUG,'schedule_action_code',
1410 x_oe_line_rec.schedule_action_code);
1411 END IF;
1412 --
1413 --
1414 IF x_Op_rec.agreement_id IS NOT NULL THEN
1415 x_oe_line_rec.agreement_id:= x_Op_rec.agreement_id;
1416 END IF;
1417 --
1418 IF x_Op_rec.attribute1 IS NOT NULL THEN
1419 x_oe_line_rec.attribute1 := x_Op_rec.attribute1;
1420 END IF;
1421 --
1422 IF x_Op_rec.attribute2 IS NOT NULL THEN
1423 x_oe_line_rec.attribute2 := x_Op_rec.attribute2;
1424 END IF;
1425 --
1426 IF x_Op_rec.attribute3 IS NOT NULL THEN
1427 x_oe_line_rec.attribute3 := x_Op_rec.attribute3;
1428 END IF;
1429 --
1430 IF x_Op_rec.attribute4 IS NOT NULL THEN
1431 x_oe_line_rec.attribute4 := x_Op_rec.attribute4;
1432 END IF;
1433 --
1434 IF x_Op_rec.attribute5 IS NOT NULL THEN
1435 x_oe_line_rec.attribute5 := x_Op_rec.attribute5;
1436 END IF;
1437 --
1438 IF x_Op_rec.attribute6 IS NOT NULL THEN
1439 x_oe_line_rec.attribute6 := x_Op_rec.attribute6;
1440 END IF;
1441 --
1442 IF x_Op_rec.attribute7 IS NOT NULL THEN
1443 x_oe_line_rec.attribute7 := x_Op_rec.attribute7;
1444 END IF;
1445 --
1446 IF x_Op_rec.attribute8 IS NOT NULL THEN
1447 x_oe_line_rec.attribute8 := x_Op_rec.attribute8;
1448 END IF;
1449 --
1450 IF x_Op_rec.attribute9 IS NOT NULL THEN
1451 x_oe_line_rec.attribute9 := x_Op_rec.attribute9;
1452 END IF;
1453 --
1454 IF x_Op_rec.attribute10 IS NOT NULL THEN
1455 x_oe_line_rec.attribute10:= x_Op_rec.attribute10;
1456 END IF;
1457 --
1458 IF x_Op_rec.attribute11 IS NOT NULL THEN
1459 x_oe_line_rec.attribute11:= x_Op_rec.attribute11;
1460 END IF;
1461 --
1462 IF x_Op_rec.attribute12 IS NOT NULL THEN
1463 x_oe_line_rec.attribute12:= x_Op_rec.attribute12;
1464 END IF;
1465 --
1466 IF x_Op_rec.attribute13 IS NOT NULL THEN
1467 x_oe_line_rec.attribute13:= x_Op_rec.attribute13;
1468 END IF;
1469 --
1470 IF x_Op_rec.attribute14 IS NOT NULL THEN
1471 x_oe_line_rec.attribute14:= x_Op_rec.attribute14;
1472 END IF;
1473 --
1474 IF x_Op_rec.attribute15 IS NOT NULL THEN
1475 x_oe_line_rec.attribute15:= x_Op_rec.attribute15;
1476 END IF;
1477 --
1478 IF x_Op_rec.attribute_category IS NOT NULL THEN
1479 x_oe_line_rec.context:= x_Op_rec.attribute_category;
1480 END IF;
1481 --
1482 x_oe_line_rec.created_by := FND_GLOBAL.USER_ID;
1483 x_oe_line_rec.creation_date := SYSDATE;
1484 x_oe_line_rec.last_updated_by := FND_GLOBAL.USER_ID;
1485 x_oe_line_rec.last_update_date := SYSDATE;
1486 x_oe_line_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
1487 IF x_Op_rec.customer_dock_code IS NOT NULL THEN
1488 x_oe_line_rec.customer_dock_code:= x_Op_rec.customer_dock_code;
1489 END IF;
1490 --
1491 IF x_Op_rec.customer_job IS NOT NULL THEN
1492 x_oe_line_rec.customer_job := x_Op_rec.customer_job;
1493 END IF;
1494 --
1495 IF x_Op_rec.cust_production_line IS NOT NULL THEN
1496 x_oe_line_rec.customer_production_line:= x_Op_rec.cust_production_line;
1497 END IF;
1498 --
1499 IF x_Op_rec.cust_model_serial_number IS NOT NULL THEN
1500 x_oe_line_rec.cust_model_serial_number:= x_Op_rec.cust_model_serial_number;
1501 END IF;
1502 --
1503 IF x_Op_rec.cust_po_number IS NOT NULL THEN
1504 x_oe_line_rec.cust_po_number := x_Op_rec.cust_po_number;
1505 END IF;
1506 --
1507 IF x_Op_rec.delivery_lead_time IS NOT NULL THEN
1508 x_oe_line_rec.delivery_lead_time:= x_Op_rec.delivery_lead_time;
1509 END IF;
1510 --
1511 x_oe_line_rec.header_id := x_Op_rec.order_header_id;
1512 --
1513 IF x_Op_rec.industry_attribute1 IS NOT NULL THEN
1514 x_oe_line_rec.industry_attribute1 := x_Op_rec.industry_attribute1;
1515 END IF;
1516 --
1517 IF x_Op_rec.industry_attribute10 IS NOT NULL THEN
1518 x_oe_line_rec.industry_attribute10:= x_Op_rec.industry_attribute10;
1519 END IF;
1520 --
1521 IF x_Op_rec.industry_attribute11 IS NOT NULL THEN
1522 x_oe_line_rec.industry_attribute11:= x_Op_rec.industry_attribute11;
1523 END IF;
1524 --
1525 IF x_Op_rec.industry_attribute12 IS NOT NULL THEN
1526 x_oe_line_rec.industry_attribute12:= x_Op_rec.industry_attribute12;
1527 END IF;
1528 --
1529 IF x_Op_rec.industry_attribute13 IS NOT NULL THEN
1530 x_oe_line_rec.industry_attribute13:= x_Op_rec.industry_attribute13;
1531 END IF;
1532 --
1533 IF x_Op_rec.industry_attribute14 IS NOT NULL THEN
1534 x_oe_line_rec.industry_attribute14:= x_Op_rec.industry_attribute14;
1535 END IF;
1536 --
1537 IF x_Op_rec.industry_attribute15 IS NOT NULL THEN
1538 x_oe_line_rec.industry_attribute15:= x_Op_rec.industry_attribute15;
1539 END IF;
1540 --
1541 IF x_Op_rec.industry_attribute2 IS NOT NULL THEN
1542 x_oe_line_rec.industry_attribute2 := x_Op_rec.industry_attribute2;
1543 END IF;
1544 --
1545 IF x_Op_rec.industry_attribute3 IS NOT NULL THEN
1546 x_oe_line_rec.industry_attribute3 := x_Op_rec.industry_attribute3;
1547 END IF;
1548 --
1549 IF x_Op_rec.industry_attribute4 IS NOT NULL THEN
1550 x_oe_line_rec.industry_attribute4 := x_Op_rec.industry_attribute4;
1551 END IF;
1552 --
1553 IF x_Op_rec.industry_attribute5 IS NOT NULL THEN
1554 x_oe_line_rec.industry_attribute5 := x_Op_rec.industry_attribute5;
1555 END IF;
1556 --
1557 IF x_Op_rec.industry_attribute6 IS NOT NULL THEN
1558 x_oe_line_rec.industry_attribute6 := x_Op_rec.industry_attribute6;
1559 END IF;
1560 --
1561 IF x_Op_rec.industry_attribute7 IS NOT NULL THEN
1562 x_oe_line_rec.industry_attribute7 := x_Op_rec.industry_attribute7;
1563 END IF;
1564 --
1565 IF x_Op_rec.industry_attribute8 IS NOT NULL THEN
1566 x_oe_line_rec.industry_attribute8 := x_Op_rec.industry_attribute8;
1567 END IF;
1568 --
1569 IF x_Op_rec.industry_attribute9 IS NOT NULL THEN
1570 x_oe_line_rec.industry_attribute9 := x_Op_rec.industry_attribute9;
1571 END IF;
1572 --
1573 -- RLM industry context can now be passed. Bug 1495522 fixed the issue
1574 -- of bug 1421573 and 1495573
1575
1576 x_oe_line_rec.industry_context := NVL(x_Op_rec.industry_context, 'RLM');
1577 --
1578 IF x_Op_rec.invoice_to_org_id IS NOT NULL THEN
1579 x_oe_line_rec.invoice_to_org_id := x_Op_rec.invoice_to_org_id;
1580 END IF;
1581 --
1582 IF x_Op_rec.item_detail_subtype IS NOT NULL THEN
1583 x_oe_line_rec.demand_bucket_type_code := x_Op_rec.item_detail_subtype;
1584 END IF;
1585 --
1586 IF x_Op_rec.inventory_item_id IS NOT NULL THEN
1587 x_oe_line_rec.inventory_item_id := x_Op_rec.inventory_item_id;
1588 END IF;
1589 --
1590 IF x_Op_rec.customer_item_id IS NOT NULL THEN
1591 x_oe_line_rec.ordered_item_id := x_Op_rec.customer_item_id;
1592 END IF;
1593 --
1594 IF x_Op_rec.customer_item_ext IS NOT NULL THEN
1595 x_oe_line_rec.ordered_item := x_Op_rec.customer_item_ext;
1596 ELSIF x_Op_rec.supplier_item_ext is not null THEN
1597 x_oe_line_rec.ordered_item := x_Op_rec.supplier_item_ext;
1598 END IF;
1599 --
1600 --x_oe_line_rec.item_type_code := fnd_api.g_miss_char;
1601 IF (l_debug <> -1) THEN
1602 rlm_core_sv.dlog(k_DEBUG,'customer_item_revision', x_Op_rec.customer_item_revision);
1603 END IF;
1604 --
1605 IF x_Op_rec.customer_item_revision IS NOT NULL THEN
1606 x_oe_line_rec.item_revision := x_Op_rec.customer_item_revision;
1607 END IF;
1608 --
1609 IF x_Op_rec.line_id IS NOT NULL THEN
1610 x_oe_line_rec.line_id := x_Op_rec.line_id;
1611 END IF;
1612 --
1613 IF x_Op_rec.ordered_quantity IS NOT NULL THEN
1614 x_oe_line_rec.ordered_quantity := x_Op_rec.ordered_quantity;
1615 END IF;
1616 --
1617 IF x_Op_rec.request_date IS NOT NULL THEN
1618 x_oe_line_rec.pricing_date := x_Op_rec.request_date;
1619 x_oe_line_rec.request_date := x_Op_rec.request_date;
1620 END IF;
1621 --
1622 IF x_Op_rec.price_list_id IS NOT NULL THEN
1623 x_oe_line_rec.price_list_id := x_Op_rec.price_list_id;
1624 END IF;
1625 --
1626 IF x_Op_rec.authorized_to_ship_flag IS NOT NULL THEN
1627 x_oe_line_rec.authorized_to_ship_flag := x_Op_rec.authorized_to_ship_flag;
1628 END IF;
1629 --
1630 IF x_Op_rec.cust_production_seq_num IS NOT NULL THEN
1631 x_oe_line_rec.cust_production_seq_num := x_Op_rec.cust_production_seq_num;
1632 END IF;
1633 --
1634 x_oe_line_rec.program_application_id := FND_GLOBAL.PROG_APPL_ID;
1635 x_oe_line_rec.program_id := FND_GLOBAL.CONC_PROGRAM_ID;
1636 x_oe_line_rec.program_update_date := SYSDATE;
1637 --
1638 IF x_Op_rec.schedule_type IS NOT NULL THEN
1639 x_oe_line_rec.rla_schedule_type_code := x_Op_rec.schedule_type;
1640 END IF;
1641 --
1642 x_oe_line_rec.request_id := FND_GLOBAL.CONC_REQUEST_ID;
1643 --
1644 IF x_Op_rec.customer_id IS NOT NULL THEN
1645 x_oe_line_rec.sold_to_org_id := x_Op_rec.customer_id;
1646 END IF;
1647 --
1648 IF x_Op_rec.intmed_ship_to_org_id IS NOT NULL THEN
1649 x_oe_line_rec.intermed_ship_to_org_id := x_Op_rec.intmed_ship_to_org_id;
1650 END IF;
1651 --
1652 IF x_Op_rec.ship_to_org_id IS NOT NULL THEN
1653 x_oe_line_rec.ship_to_org_id := x_Op_rec.ship_to_org_id;
1654 END IF;
1655 --
1656 x_oe_line_rec.operation:= x_Op_rec.operation;
1657 --
1658 --
1659 IF x_Op_rec.uom_code IS NOT NULL THEN
1660 x_oe_line_rec.order_quantity_uom := x_Op_rec.uom_code;
1661 END IF;
1662 --
1663 x_oe_line_rec.item_identifier_type:= x_Op_rec.item_identifier_type;
1664 --
1665 -- required for the link between OE and rlm lines
1666 x_oe_line_rec.source_document_type_id:= k_OE_DOCUMENT_TYPE;
1667 --
1668 IF x_Op_rec.schedule_line_id IS NOT NULL THEN
1669 x_oe_line_rec.source_document_line_id := x_Op_rec.schedule_line_id;
1670 END IF;
1671 --
1672 IF x_Op_rec.schedule_header_id IS NOT NULL THEN
1673 x_oe_line_rec.source_document_id := x_Op_rec.schedule_header_id;
1674 END IF;
1675 --
1676 IF x_Op_rec.cust_po_line_num is NOT NULL THEN
1677 x_oe_line_rec.customer_line_number := x_Op_rec.cust_po_line_num;
1678 END IF;
1679 --
1680 -- blankets
1681 --
1682 IF x_Op_rec.blanket_number is NOT NULL THEN
1683 x_oe_line_rec.blanket_number := x_Op_rec.blanket_number;
1684 END IF;
1685 --
1686 x_oe_line_rec.org_id := MO_GLOBAL.get_current_org_id;
1687 --
1688 IF (l_debug <> -1) THEN
1689 rlm_core_sv.dlog(k_DEBUG,'created_by',FND_GLOBAL.USER_ID);
1690 rlm_core_sv.dlog(k_DEBUG,'creation_date',SYSDATE);
1691 rlm_core_sv.dlog(k_DEBUG,'cust_dock_code',x_oe_line_rec.customer_dock_code);
1692 rlm_core_sv.dlog(k_DEBUG,'Customer job', x_oe_line_rec.customer_job);
1693 rlm_core_sv.dlog(k_DEBUG,'delivery_lead_time',
1694 x_oe_line_rec.delivery_lead_time);
1695 rlm_core_sv.dlog(k_DEBUG,'ordered_quantity ',
1696 x_oe_line_rec.ordered_quantity);
1697 rlm_core_sv.dlog(k_DEBUG,'uom_code',
1698 x_Op_rec.uom_code);
1699 rlm_core_sv.dlog(k_DEBUG,'operation ',x_oe_line_rec.operation);
1700 rlm_core_sv.dlog(k_DEBUG,'change_reason ',x_oe_line_rec.change_reason);
1701 rlm_core_sv.dlog(k_DEBUG,'pricing_date',x_oe_line_rec.pricing_date);
1702 rlm_core_sv.dlog(k_DEBUG,'request_date',x_oe_line_rec.request_date);
1703 rlm_core_sv.dlog(k_DEBUG,'promise_date',x_oe_line_rec.promise_date);
1704 rlm_core_sv.dlog(k_DEBUG,'intmed_ship_to_org_id',x_oe_line_rec.intermed_ship_to_org_id);
1705 rlm_core_sv.dlog(k_DEBUG,'schedule_date',
1706 x_oe_line_rec.schedule_ship_date);
1707 rlm_core_sv.dlog(k_DEBUG,'deliver_to_org_id',
1708 x_oe_line_rec.deliver_to_org_id);
1709 rlm_core_sv.dlog(k_DEBUG,'ship_from_org_id',
1710 x_oe_line_rec.ship_from_org_id);
1711 rlm_core_sv.dlog(k_DEBUG,'ship_to_org_id',
1712 x_oe_line_rec.ship_to_org_id);
1713 rlm_core_sv.dlog(k_DEBUG,'invoice to org id ',
1714 x_oe_line_rec.invoice_to_org_id);
1715 rlm_core_sv.dlog(k_DEBUG,'authorized_to_ship_flag',
1716 x_oe_line_rec.authorized_to_ship_flag);
1717 rlm_core_sv.dlog(k_DEBUG,'Header ID',x_oe_line_rec.header_id);
1718 rlm_core_sv.dlog(k_DEBUG,'Inventory_item_id',
1719 x_oe_line_rec.inventory_item_id);
1720 rlm_core_sv.dlog(k_DEBUG,'item_identifier_type ',
1721 x_oe_line_rec.item_identifier_type);
1722 rlm_core_sv.dlog(k_DEBUG,'ordered_item_id ',
1723 x_oe_line_rec.ordered_item_id);
1724 rlm_core_sv.dlog(k_DEBUG,'ordered_item',
1725 x_oe_line_rec.ordered_item);
1726 rlm_core_sv.dlog(k_DEBUG,'item_detail_type',
1727 x_oe_line_rec.item_type_code);
1728 rlm_core_sv.dlog(k_DEBUG,'line_id ',
1729 x_oe_line_rec.line_id);
1730 rlm_core_sv.dlog(k_DEBUG,'agreement_id ',
1731 x_oe_line_rec.agreement_id);
1732 rlm_core_sv.dlog(k_DEBUG,'price_list_id ',
1733 x_oe_line_rec.price_list_id);
1734 rlm_core_sv.dlog(k_DEBUG,'sold_to_org_id ',
1735 x_oe_line_rec.sold_to_org_id);
1736 rlm_core_sv.dlog(k_DEBUG,'source_document_line_id ',
1737 x_oe_line_rec.source_document_line_id);
1738 rlm_core_sv.dlog(k_DEBUG,'source_document_id ',
1739 x_oe_line_rec.source_document_id);
1740 rlm_core_sv.dlog(k_DEBUG,'source_document_type_id ',
1741 x_oe_line_rec.source_document_type_id);
1742 rlm_core_sv.dlog(k_DEBUG,'PO Line number ',
1743 x_oe_line_rec.customer_line_number);
1744 rlm_core_sv.dlog(k_DEBUG,'customer_production_line', x_oe_line_rec.customer_production_line);
1745 rlm_core_sv.dlog(k_DEBUG,'cust_model_serial_number', x_oe_line_rec.cust_model_serial_number);
1746 rlm_core_sv.dlog(k_DEBUG,'cust_po_number', x_oe_line_rec.cust_po_number);
1747 rlm_core_sv.dlog(k_DEBUG,'demand_bucket_type_code', x_oe_line_rec.demand_bucket_type_code);
1748 rlm_core_sv.dlog(k_DEBUG,'cust_production_seq_num', x_oe_line_rec.cust_production_seq_num);
1749 rlm_core_sv.dlog(k_DEBUG,'item_identifier_type', x_oe_line_rec.item_identifier_type);
1750 rlm_core_sv.dlog(k_DEBUG,'industry_attribute1 ', x_oe_line_rec.industry_attribute1);
1751 rlm_core_sv.dlog(k_DEBUG,'industry_attribute2 ', x_oe_line_rec.industry_attribute2);
1752 rlm_core_sv.dlog(k_DEBUG,'industry_attribute3 ', x_oe_line_rec.industry_attribute3);
1753 rlm_core_sv.dlog(k_DEBUG,'industry_attribute4 ', x_oe_line_rec.industry_attribute4);
1754 rlm_core_sv.dlog(k_DEBUG,'industry_attribute5 ', x_oe_line_rec.industry_attribute5);
1755 rlm_core_sv.dlog(k_DEBUG,'industry_attribute6 ', x_oe_line_rec.industry_attribute6);
1756 rlm_core_sv.dlog(k_DEBUG,'industry_attribute7 ', x_oe_line_rec.industry_attribute7);
1757 rlm_core_sv.dlog(k_DEBUG,'industry_attribute8 ', x_oe_line_rec.industry_attribute8);
1758 rlm_core_sv.dlog(k_DEBUG,'industry_attribute9 ', x_oe_line_rec.industry_attribute9);
1759 rlm_core_sv.dlog(k_DEBUG,'industry_attribute10', x_oe_line_rec.industry_attribute10);
1760 rlm_core_sv.dlog(k_DEBUG,'industry_attribute11', x_oe_line_rec.industry_attribute11);
1761 rlm_core_sv.dlog(k_DEBUG,'industry_attribute12', x_oe_line_rec.industry_attribute12);
1762 rlm_core_sv.dlog(k_DEBUG,'industry_attribute13', x_oe_line_rec.industry_attribute13);
1763 rlm_core_sv.dlog(k_DEBUG,'industry_attribute14', x_oe_line_rec.industry_attribute14);
1764 rlm_core_sv.dlog(k_DEBUG,'industry_attribute15', x_oe_line_rec.industry_attribute15);
1765 rlm_core_sv.dlog(k_DEBUG,'industry_context', x_oe_line_rec.industry_context);
1766 rlm_core_sv.dlog(k_DEBUG,'attribute1 ', x_oe_line_rec.attribute1);
1767 rlm_core_sv.dlog(k_DEBUG,'attribute2', x_oe_line_rec.attribute2);
1768 rlm_core_sv.dlog(k_DEBUG,'attribute3 ', x_oe_line_rec.attribute3);
1769 rlm_core_sv.dlog(k_DEBUG,'attribute4 ', x_oe_line_rec.attribute4);
1770 rlm_core_sv.dlog(k_DEBUG,'attribute5 ', x_oe_line_rec.attribute5);
1771 rlm_core_sv.dlog(k_DEBUG,'attribute6', x_oe_line_rec.attribute6);
1772 rlm_core_sv.dlog(k_DEBUG,'attribute7 ', x_oe_line_rec.attribute7);
1773 rlm_core_sv.dlog(k_DEBUG,'attribute8 ', x_oe_line_rec.attribute8);
1774 rlm_core_sv.dlog(k_DEBUG,'attribute9 ', x_oe_line_rec.attribute9);
1775 rlm_core_sv.dlog(k_DEBUG,'attribute10', x_oe_line_rec.attribute10);
1776 rlm_core_sv.dlog(k_DEBUG,'attribute11', x_oe_line_rec.attribute11);
1777 rlm_core_sv.dlog(k_DEBUG,'attribute12', x_oe_line_rec.attribute12);
1778 rlm_core_sv.dlog(k_DEBUG,'attribute13', x_oe_line_rec.attribute13);
1779 rlm_core_sv.dlog(k_DEBUG,'attribute14', x_oe_line_rec.attribute14);
1780 rlm_core_sv.dlog(k_DEBUG,'attribute15', x_oe_line_rec.attribute15);
1781 rlm_core_sv.dlog(k_DEBUG,'attribute_category',x_oe_line_rec.context);
1782 rlm_core_sv.dlog(k_DEBUG,'blanket_number',x_oe_line_rec.blanket_number);
1783 rlm_core_sv.dlog(k_DEBUG,'Org ID', x_oe_line_rec.org_id);
1784 END IF;
1785 END IF;
1786 --
1787 IF (l_debug <> -1) THEN
1788 rlm_core_sv.dpop(k_SDEBUG);
1789 END IF;
1790 --
1791 EXCEPTION
1792 WHEN OTHERS THEN
1793 --
1794 IF (l_debug <> -1) THEN
1795 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
1796 END IF;
1797 --
1798 raise;
1799
1800 END BuildOELine;
1801
1802 /*===========================================================================
1803
1804 PROCEDURE GetLineStatus
1805
1806 ===========================================================================*/
1807 FUNCTION GetLineStatus(x_ScheduleLineId In NUMBER, x_OrderLineId In NUMBER)
1808 RETURN VARCHAR2
1809 IS
1810
1811 v_line_status VARCHAR2(80);
1812 v_status_code VARCHAR2(80);
1813 v_total_count NUMBER;
1814 v_released_count NUMBER;
1815
1816 BEGIN
1817
1818 -- Required currently for Demand Status Inquiry Report
1819 -- Adding the select statement below to fix bug 1509014.
1820 -- Making the change to reflect the changes OM made for in OEXFLINB.pls for 1172817
1821 --
1822 SELECT flow_status_code
1823 INTO v_status_code
1824 FROM oe_order_lines_all
1825 where line_id = x_OrderLineId;
1826 --
1827 IF v_status_code <> 'AWAITING_SHIPPING' AND
1828 v_status_code <> 'PRODUCTION_COMPLETE' AND
1829 v_status_code <> 'PICKED' AND
1830 v_status_code <> 'PICKED_PARTIAL' THEN
1831 --
1832 SELECT a.meaning
1833 INTO v_line_status
1834 FROM oe_lookups a, oe_order_lines_all b
1835 WHERE a.lookup_type like 'LINE_FLOW_STATUS'
1836 AND a.lookup_code = b.flow_status_code
1837 AND b.line_id = x_OrderLineId;
1838 --
1839 ELSE
1840 --
1841 SELECT sum(decode(released_status, 'Y', 1, 0)), sum(1)
1842 INTO v_released_count, v_total_count
1843 FROM wsh_delivery_details
1844 WHERE source_line_id = x_OrderLineId
1845 AND source_code = 'OE'
1846 AND released_status <> 'D';
1847
1848 IF v_released_count = v_total_count THEN
1849 --
1850 SELECT meaning
1851 INTO v_line_status
1852 FROM fnd_lookup_values lv
1853 WHERE lookup_type = 'LINE_FLOW_STATUS'
1854 AND lookup_code = 'PICKED'
1855 AND LANGUAGE = userenv('LANG')
1856 AND VIEW_APPLICATION_ID = 660
1857 AND SECURITY_GROUP_ID =
1858 fnd_global.Lookup_Security_Group(lv.lookup_type,
1859 lv.view_application_id);
1860 --
1861 ELSIF v_released_count < v_total_count and v_released_count <> 0 THEN
1862 --
1863 SELECT meaning
1864 INTO v_line_status
1865 FROM fnd_lookup_values lv
1866 WHERE lookup_type = 'LINE_FLOW_STATUS'
1867 AND lookup_code = 'PICKED_PARTIAL'
1868 AND LANGUAGE = userenv('LANG')
1869 AND VIEW_APPLICATION_ID = 660
1870 AND SECURITY_GROUP_ID =
1871 fnd_global.Lookup_Security_Group(lv.lookup_type,
1872 lv.view_application_id);
1873 --
1874 ELSE
1875 --
1876 SELECT meaning
1877 INTO v_line_status
1878 FROM fnd_lookup_values lv
1879 WHERE lookup_type = 'LINE_FLOW_STATUS'
1880 AND lookup_code = v_status_code
1881 AND LANGUAGE = userenv('LANG')
1882 AND VIEW_APPLICATION_ID = 660
1883 AND SECURITY_GROUP_ID =
1884 fnd_global.Lookup_Security_Group(lv.lookup_type,
1885 lv.view_application_id);
1886 --
1887 END IF;
1888 --
1889 END IF;
1890 --
1891 RETURN v_line_status;
1892 --
1893 EXCEPTION
1894 --
1895 WHEN OTHERS THEN
1896 --
1897 IF (l_debug <> -1) THEN
1898 rlm_core_sv.dlog(k_DEBUG,'EXCEPTION',SUBSTR(SQLERRM,1,200));
1899 END IF;
1900 --
1901 raise;
1902
1903 END;
1904
1905 /*===========================================================================
1906
1907 PROCEDURE GetLocation
1908
1909 ===========================================================================*/
1910
1911 FUNCTION GetLocation(x_OrgId In NUMBER)
1912 RETURN VARCHAR2
1913 IS
1914
1915 v_location VARCHAR2(80);
1916 BEGIN
1917 -- Required currently for Demand Status Inquiry Report
1918
1919 IF (x_OrgId IS NOT NULL) THEN
1920 -- Following query is changed as per TCA obsolescence project.
1921 select location
1922 into v_location
1923 from HZ_CUST_SITE_USES_ALL
1924 where site_use_code = 'SHIP_TO'
1925 and site_use_id = x_OrgId;
1926
1927 return v_location;
1928
1929 ELSE
1930 return NULL;
1931
1932 END IF;
1933
1934 END;
1935
1936 /*===========================================================================
1937
1938 PROCEDURE GetAddress1
1939
1940 ===========================================================================*/
1941
1942 FUNCTION GetAddress1(x_OrgId In NUMBER)
1943 RETURN VARCHAR2
1944 IS
1945
1946 v_address1 VARCHAR2(80);
1947 BEGIN
1948 -- Required currently for Demand Status Inquiry Report
1949 -- x_OrgId is either Ship_To_Org_id / Intrmd_ShipTo_OrgId
1950
1951 IF (x_OrgId IS NOT NULL) THEN
1952 --
1953 -- Following query is changed as per TCA obsolescence project.
1954 select loc.address1
1955 into v_address1
1956 from HZ_CUST_SITE_USES_ALL cust_site,
1957 HZ_PARTY_SITES PARTY_SITE,
1958 HZ_LOCATIONS LOC,
1959 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1960 where cust_site.site_use_code = 'SHIP_TO'
1961 and cust_site.site_use_id = x_OrgId
1962 and cust_site.CUST_ACCT_SITE_ID = acct_site.CUST_ACCT_SITE_ID
1963 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1964 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID;
1965 --
1966 return v_address1;
1967
1968 ELSE
1969 return NULL;
1970
1971 END IF;
1972
1973 END;
1974
1975 /*===========================================================================
1976
1977 PROCEDURE GetTpContext
1978
1979 ===========================================================================*/
1980 PROCEDURE GetTPContext( x_Op_rec IN rlm_rd_sv.t_generic_rec ,
1981 x_customer_number OUT NOCOPY VARCHAR2,
1982 x_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
1983 x_bill_to_ece_locn_code OUT NOCOPY VARCHAR2,
1984 x_inter_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
1985 x_tp_group_code OUT NOCOPY VARCHAR2)
1986 IS
1987 --
1988 v_Progress VARCHAR2(3) := '010';
1989
1990 --
1991 -- Following cursor is changed as per TCA obsolescence project.
1992 CURSOR C is
1993 SELECT ETG.tp_group_code
1994 FROM ece_tp_headers ETH,
1995 ece_tp_group ETG,
1996 HZ_CUST_SITE_USES_ALL cust_site,
1997 HZ_CUST_ACCT_SITES ACCT_SITE
1998 WHERE ACCT_SITE.CUST_ACCOUNT_ID = x_Op_rec.customer_id
1999 AND cust_site.site_use_id = x_Op_rec.ship_to_org_id
2000 and cust_site.CUST_ACCT_SITE_ID = acct_site.CUST_ACCT_SITE_ID
2001 AND ETH.tp_header_id = ACCT_SITE.tp_header_id
2002 AND ETG.tp_group_id = ETH.tp_group_id
2003 AND cust_site.site_use_code = 'SHIP_TO';
2004 --
2005 BEGIN
2006 --
2007 IF (l_debug <> -1) THEN
2008 rlm_core_sv.dpush(k_SDEBUG,'GetTPContext');
2009 rlm_core_sv.dlog(k_DEBUG,'customer_id', x_Op_rec.customer_id);
2010 rlm_core_sv.dlog(k_DEBUG,'x_Op_rec.ship_to_org_id',
2011 x_Op_rec.ship_to_org_id);
2012 rlm_core_sv.dlog(k_DEBUG,'x_Op_rec.intmed_ship_to_org_id',
2013 x_Op_rec.intmed_ship_to_org_id);
2014 rlm_core_sv.dlog(k_DEBUG,'x_Op_rec.invoice_to_org_id',
2015 x_Op_rec.invoice_to_org_id);
2016 END IF;
2017 --
2018 BEGIN
2019 --
2020 -- Following query is changed as per TCA obsolescence project.
2021 SELECT ACCT_SITE.ece_tp_location_code
2022 INTO x_ship_to_ece_locn_code
2023 FROM HZ_CUST_ACCT_SITES ACCT_SITE ,
2024 HZ_CUST_SITE_USES_ALL CUST_SITE
2025 WHERE cust_site.site_use_id = x_Op_rec.ship_to_org_id
2026 AND cust_site.site_use_code = 'SHIP_TO'
2027 AND CUST_SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID;
2028 --
2029 EXCEPTION
2030 WHEN NO_DATA_FOUND THEN
2031 x_ship_to_ece_locn_code := NULL;
2032 END;
2033 --
2034 BEGIN
2035 --
2036 -- Following query is changed as per TCA obsolescence project.
2037 SELECT ACCT_SITE.ece_tp_location_code
2038 INTO x_bill_to_ece_locn_code
2039 FROM HZ_CUST_ACCT_SITES ACCT_SITE ,
2040 HZ_CUST_SITE_USES_ALL CUST_SITE
2041 WHERE cust_site.site_use_id = x_Op_rec.invoice_to_org_id
2042 AND cust_site.site_use_code = 'BILL_TO'
2043 AND CUST_SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID;
2044 --
2045
2046 EXCEPTION
2047 --
2048 WHEN NO_DATA_FOUND THEN
2049 x_bill_to_ece_locn_code := NULL;
2050 WHEN OTHERS THEN
2051 --
2052 IF (l_debug <> -1) THEN
2053 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
2054 END IF;
2055 --
2056 raise;
2057 --
2058 END;
2059
2060 BEGIN
2061 --
2062 -- Following query is changed as per TCA obsolescence project.
2063 SELECT ACCT_SITE.ece_tp_location_code
2064 INTO x_inter_ship_to_ece_locn_code
2065 FROM HZ_CUST_ACCT_SITES ACCT_SITE ,
2066 HZ_CUST_SITE_USES_ALL CUST_SITE
2067 WHERE cust_site.site_use_id = x_Op_rec.intmed_ship_to_org_id
2068 AND cust_site.site_use_code = 'SHIP_TO'
2069 AND CUST_SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID;
2070 --
2071 EXCEPTION
2072 --
2073 WHEN NO_DATA_FOUND THEN
2074 x_inter_ship_to_ece_locn_code := NULL;
2075 --
2076 END;
2077 --
2078 IF x_Op_rec.customer_id is NOT NULL THEN
2079 --
2080 OPEN C;
2081 FETCH C INTO x_tp_Group_code;
2082 IF C%NOTFOUND THEN
2083 raise NO_DATA_FOUND;
2084 END IF;
2085 CLOSE C;
2086 --
2087 BEGIN
2088 --
2089 -- Following query is changed as per TCA obsolescence project.
2090 SELECT account_number
2091 INTO x_customer_number
2092 FROM HZ_CUST_ACCOUNTS CUST_ACCT
2093 WHERE CUST_ACCT.CUST_ACCOUNT_ID = x_Op_rec.Customer_Id;
2094 --
2095 EXCEPTION
2096 --
2097 WHEN NO_DATA_FOUND THEN
2098 x_customer_number := NULL;
2099 WHEN OTHERS THEN
2100 --
2101 IF (l_debug <> -1) THEN
2102 rlm_core_sv.dlog(k_DEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
2103 END IF;
2104 --
2105 raise;
2106 END;
2107
2108 END IF;
2109 --
2110 IF (l_debug <> -1) THEN
2111 rlm_core_sv.dlog(k_DEBUG, 'customer_number', x_customer_number);
2112 rlm_core_sv.dlog(k_DEBUG,'x_ship_to_ece_locn_code', x_ship_to_ece_locn_code);
2113 rlm_core_sv.dlog(k_DEBUG,'x_bill_to_ece_locn_code', x_bill_to_ece_locn_code);
2114 rlm_core_sv.dlog(k_DEBUG,'x_inter_ship_to_ece_locn_code',
2115 x_inter_ship_to_ece_locn_code);
2116 rlm_core_sv.dlog(k_DEBUG,'x_tp_Group_code', x_tp_Group_code);
2117 rlm_core_sv.dpop(k_SDEBUG);
2118 END IF;
2119 --
2120 EXCEPTION
2121 --
2122 WHEN NO_DATA_FOUND THEN
2123 --
2124 x_customer_number := NULL;
2125 --
2126 IF (l_debug <> -1) THEN
2127 rlm_core_sv.dlog(k_DEBUG, 'No data found for' , x_Op_rec.customer_id);
2128 rlm_core_sv.dpop(k_SDEBUG);
2129 END IF;
2130 --
2131 WHEN OTHERS THEN
2132 --
2133 rlm_message_sv.sql_error('rlm_validatedemand_sv.GetTPContext',v_Progress);
2134 --
2135 IF (l_debug <> -1) THEN
2136 rlm_core_sv.dpop(k_SDEBUG,'EXCEPTION: '||SUBSTR(SQLERRM,1,200));
2137 END IF;
2138 --
2139 raise;
2140 --
2141 END GetTPContext;
2142 --
2143 /*===========================================================================
2144
2145 PROCEDURE GetIntransitShippedLines
2146
2147 ===========================================================================*/
2148 --
2149 PROCEDURE GetIntransitShippedLines (x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
2150 x_Group_rec IN rlm_dp_sv.t_Group_rec,
2151 x_optional_match_rec IN RLM_RD_SV.t_generic_rec,
2152 x_min_horizon_date IN VARCHAR2,
2153 x_intransit_qty IN OUT NOCOPY NUMBER
2154 )
2155 IS
2156
2157 v_select_clause VARCHAR2(32000);
2158 v_where_clause VARCHAR2(32000);
2159 v_final_sql VARCHAR2(32000);
2160 v_ship_from_org_id NUMBER;
2161 l_effective_start_date DATE; --Bugfix 6485729
2162 l_effective_end_date DATE; --Bugfix 6485729
2163
2164 TYPE t_Cursor_ref IS REF CURSOR;
2165 c_sum t_Cursor_ref;
2166
2167 e EXCEPTION;
2168
2169 BEGIN
2170
2171 --
2172 IF (l_debug <> -1) THEN
2173 rlm_core_sv.dpush(k_SDEBUG,'GetIntransitShippedLines');
2174 END IF;
2175
2176 --ITS should be complete before a WDD/OM line can be considered intransit.
2177 --OE tables are referenced while considered shipped lines.
2178 -- Bugfix 5608510 added oe_intefaced_flag check
2179 v_select_clause := 'SELECT SUM(NVL(o.shipped_quantity,0))
2180 FROM oe_order_lines o';
2181
2182 --Bugfix 6485729 Start
2183 --This condition has been added to reconcile against other BSO's for the past due demands falling under different orders.
2184 IF x_Group_rec.blanket_number IS NOT NULL THEN
2185
2186 SELECT effective_start_date, effective_end_date
2187 INTO l_effective_start_date, l_effective_end_date
2188 FROM rlm_blanket_rso
2189 WHERE blanket_number = x_Group_rec.blanket_number
2190 AND rso_hdr_id = x_Group_rec.order_header_id;
2191
2192 IF TO_DATE(x_optional_match_rec.industry_attribute2,'RRRR/MM/DD HH24:MI:SS') < trunc(l_effective_start_date) THEN
2193
2194 IF (l_debug <> -1) THEN
2195 rlm_core_sv.dlog(k_DEBUG,'Reconciling against all the Blanket Release orders');
2196 END IF;
2197
2198 v_where_clause := ' WHERE o.header_id IN'||
2199 ' (SELECT rbr.rso_hdr_id FROM rlm_blanket_rso rbr,oe_order_headers oh WHERE rbr.blanket_number = '||x_Group_rec.blanket_number||
2200 ' AND oh.header_id = rbr.rso_hdr_id AND oh.open_flag = '||'''Y'''||')';
2201
2202 ELSE
2203 v_where_clause := ' WHERE o.header_id = :order_header_id' ;
2204 g_WhereTab(g_WhereTab.COUNT+1) := x_Group_rec.order_header_id;
2205 END IF;
2206
2207 ELSE
2208 v_where_clause := ' WHERE o.header_id = :order_header_id' ;
2209 g_WhereTab(g_WhereTab.COUNT+1) := x_Group_rec.order_header_id;
2210 END IF;
2211 --Bugfix 6485729 End
2212
2213 v_where_clause := v_where_clause ||
2214 ' AND o.ship_to_org_id = :ship_to_org_id' ||
2215 ' AND NVL(o.intmed_ship_to_org_id,'||k_NNULL||') = NVL(:intmed_ship_to_org_id,'||k_NNULL||')'||--Bugfix 5911991
2216 ' AND o.ordered_item_id = :customer_item_id ' ||
2217 ' AND o.inventory_item_id = :inventory_item_id ' ||
2218 ' AND o.source_document_type_id = 5 ' ||
2219 ' AND TO_DATE(o.industry_attribute2,''RRRR/MM/DD HH24:MI:SS'')
2220 BETWEEN TO_DATE(:sched_horizon_start_date,''RRRR/MM/DD HH24:MI:SS'')
2221 AND TO_DATE(:sched_horizon_end_date,''RRRR/MM/DD HH24:MI:SS'') '||
2222 ' AND o.shipped_quantity IS NOT NULL'||
2223 ' AND o.ACTUAL_SHIPMENT_DATE IS NOT NULL';
2224
2225 --Add bind var to table
2226 -- g_WhereTab(g_WhereTab.COUNT+1) := x_Group_rec.order_header_id; --Bugfix 6485729
2227 g_WhereTab(g_WhereTab.COUNT+1) := x_Group_rec.ship_to_org_id;
2228 g_WhereTab(g_WhereTab.COUNT+1) := x_Group_rec.intmed_ship_to_org_id; --Bugfix 5911991
2229 g_WhereTab(g_WhereTab.COUNT+1) := x_Group_rec.customer_item_id;
2230 g_WhereTab(g_WhereTab.COUNT+1) := x_Group_rec.inventory_item_id;
2231 g_WhereTab(g_WhereTab.COUNT+1) := x_min_horizon_date;
2232 g_WhereTab(g_WhereTab.COUNT+1) := TO_CHAR(TRUNC(x_Sched_rec.sched_horizon_end_date)+1, 'RRRR/MM/DD HH24:MI:SS');
2233
2234 --optional attributes (schedule_date is not considered for intransit calc based on shipped lines)
2235
2236 IF x_group_rec.match_across_rec.request_date = 'Y' THEN
2237 --
2238 v_where_clause := v_where_clause ||
2239 ' AND o.request_date = TO_DATE(:request_date,''RRRR/MM/DD HH24:MI:SS'')';
2240 g_WhereTab(g_WhereTab.COUNT+1) := to_char(x_optional_match_rec.request_date,'RRRR/MM/DD HH24:MI:SS');
2241 --
2242 ELSE
2243 --
2244 IF x_group_rec.match_within_rec.request_date = 'Y' THEN
2245 --
2246 v_where_clause := v_where_clause ||
2247 ' AND o.request_date = DECODE(o.rla_schedule_type_code,:schedule_type, TO_DATE(:request_date,''RRRR/MM/DD HH24:MI:SS''), o.request_date)';
2248 --
2249 g_WhereTab(g_WhereTab.COUNT+1) := x_Sched_rec.schedule_type;
2250 g_WhereTab(g_WhereTab.COUNT+1) := to_char(x_optional_match_rec.request_date,'RRRR/MM/DD HH24:MI:SS');
2251 --
2252 END IF;
2253 --
2254 END IF;
2255 --
2256
2257 IF x_group_rec.match_across_rec.cust_production_line = 'Y' THEN
2258 --
2259 v_where_clause := v_where_clause ||
2260 ' AND NVL(o.customer_production_line,'''||k_VNULL||
2261 ''') = NVL(:cust_production_line,'''||k_VNULL||''')';
2262
2263 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.cust_production_line;
2264 --
2265 ELSE
2266 --
2267 IF x_group_rec.match_within_rec.cust_production_line = 'Y' THEN
2268 --
2269 v_where_clause := v_where_clause ||
2270 ' AND NVL(o.customer_production_line,'''||k_VNULL||
2271 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:cust_production_line,'''||k_VNULL||'''), NVL(o.customer_production_line,'''||k_VNULL||'''))';
2272
2273 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2274 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.cust_production_line;
2275 --
2276 END IF;
2277 --
2278 END IF;
2279
2280 --
2281 IF x_group_rec.match_across_rec.customer_dock_code = 'Y' THEN
2282 --
2283 v_where_clause := v_where_clause ||
2284 ' AND NVL(o.customer_dock_code,'''||k_VNULL||''') = NVL(:customer_dock_code,'''||k_VNULL||''')';
2285
2286 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.customer_dock_code;
2287 --
2288 ELSE
2289 --
2290 IF x_group_rec.match_within_rec.customer_dock_code = 'Y' THEN
2291 --
2292 v_where_clause := v_where_clause ||
2293 ' AND NVL(o.customer_dock_code,'''||k_VNULL||''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:customer_dock_code,'''||k_VNULL||'''),NVL(o.customer_dock_code,'''||k_VNULL||'''))';
2294
2295 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2296 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.customer_dock_code;
2297
2298 --
2299 END IF;
2300 --
2301 END IF;
2302
2303 --
2304 IF x_group_rec.match_across_rec.cust_po_number = 'Y' THEN
2305 --
2306 v_where_clause := v_where_clause ||
2307 ' AND NVL(o.cust_po_number,'''||k_VNULL||''') = NVL(:cust_po_number,'''||k_VNULL||''')';
2308
2309 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.cust_po_number;
2310 --
2311 ELSE
2312 --
2313 IF x_group_rec.match_within_rec.cust_po_number = 'Y' THEN
2314 --
2315 v_where_clause := v_where_clause ||
2316 ' AND NVL(o.cust_po_number,'''||k_VNULL||
2317 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:cust_po_number,'''||k_VNULL||'''),NVL(o.cust_po_number,'''||k_VNULL||'''))';
2318
2319 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2320 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.cust_po_number;
2321
2322 --
2323 END IF;
2324 --
2325 END IF;
2326
2327 --
2328 IF x_group_rec.match_across_rec.customer_item_revision = 'Y' THEN
2329 --
2330 v_where_clause := v_where_clause ||
2331 ' AND NVL(o.item_revision,'''||k_VNULL||''') = NVL(:customer_item_revision,'''||k_VNULL||''')';
2332
2333 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.customer_item_revision;
2334 --
2335 ELSE
2336 --
2337 IF x_group_rec.match_within_rec.customer_item_revision = 'Y' THEN
2338 --
2339 v_where_clause := v_where_clause ||
2340 ' AND NVL(o.item_revision,'''||k_VNULL||
2341 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:customer_item_revision,'''||k_VNULL||'''),NVL(o.item_revision,'''||k_VNULL||'''))';
2342
2343 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2344 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.customer_item_revision;
2345
2346 --
2347 END IF;
2348 --
2349 END IF;
2350 --
2351
2352 --
2353 IF x_group_rec.match_across_rec.customer_job = 'Y' THEN
2354 --
2355 v_where_clause := v_where_clause ||
2356 ' AND NVL(o.customer_job,'''||k_VNULL||''') = NVL(:customer_job,'''||k_VNULL||''')';
2357
2358 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.customer_job;
2359 --
2360 ELSE
2361 --
2362 IF x_group_rec.match_within_rec.customer_job = 'Y' THEN
2363 --
2364 v_where_clause := v_where_clause ||
2365 ' AND NVL(o.customer_job,'''||k_VNULL||
2366 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:customer_job,'''||k_VNULL||'''),NVL(o.customer_job,'''||k_VNULL||'''))';
2367
2368 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2369 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.customer_job;
2370
2371 --
2372 END IF;
2373 --
2374 END IF;
2375 --
2376
2377
2378 --
2379 IF x_group_rec.match_across_rec.cust_model_serial_number = 'Y' THEN
2380 --
2381 v_where_clause := v_where_clause ||
2382 ' AND NVL(o.cust_model_serial_number,'''||k_VNULL||''') = NVL(:cust_model_serial_number,'''||k_VNULL||''')';
2383
2384 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.cust_model_serial_number;
2385 --
2386 ELSE
2387 --
2388 IF x_group_rec.match_within_rec.cust_model_serial_number = 'Y' THEN
2389 --
2390 v_where_clause := v_where_clause ||
2391 ' AND NVL(o.cust_model_serial_number,'''||k_VNULL||
2392 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:cust_model_serial_number,'''||k_VNULL||'''),NVL(o.cust_model_serial_number,'''||k_VNULL||'''))';
2393
2394 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2395 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.cust_model_serial_number;
2396
2397 --
2398 END IF;
2399 --
2400 END IF;
2401 --
2402
2403 --
2404 IF x_group_rec.match_across_rec.cust_production_seq_num = 'Y' THEN
2405 --
2406 v_where_clause := v_where_clause ||
2407 ' AND NVL(o.cust_production_seq_num,'''||k_VNULL||''') = NVL(:cust_production_seq_num,'''||k_VNULL||''')';
2408
2409 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.cust_production_seq_num;
2410 --
2411 ELSE
2412 --
2413 IF x_group_rec.match_within_rec.cust_production_seq_num = 'Y' THEN
2414 --
2415 v_where_clause := v_where_clause ||
2416 ' AND NVL(o.cust_production_seq_num,'''||k_VNULL||
2417 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:cust_production_seq_num,'''||k_VNULL||'''),NVL(o.cust_production_seq_num,'''||k_VNULL||'''))';
2418
2419 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2420 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.cust_production_seq_num;
2421
2422 --
2423 END IF;
2424 --
2425 END IF;
2426 --
2427
2428 --
2429 IF x_group_rec.match_across_rec.industry_attribute1 = 'Y' THEN
2430 --
2431 v_where_clause := v_where_clause ||
2432 ' AND NVL(o.industry_attribute1,'''||k_VNULL||''') = NVL(:industry_attribute1,'''||k_VNULL||''')';
2433
2434 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.industry_attribute1;
2435 --
2436 ELSE
2437 --
2438 IF x_group_rec.match_within_rec.industry_attribute1 = 'Y' THEN
2439 --
2440 v_where_clause := v_where_clause ||
2441 ' AND NVL(o.industry_attribute1,'''||k_VNULL||
2442 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:industry_attribute1,'''||k_VNULL||'''),NVL(o.industry_attribute1,'''||k_VNULL||'''))';
2443
2444 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2445 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.industry_attribute1;
2446
2447 --
2448 END IF;
2449 --
2450 END IF;
2451 --
2452 --
2453 IF x_group_rec.match_across_rec.industry_attribute2 = 'Y' THEN
2454 --
2455 v_where_clause := v_where_clause ||
2456 ' AND NVL(o.industry_attribute2,'''||k_VNULL||''') = NVL(:industry_attribute2,'''||k_VNULL||''')';
2457
2458 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.industry_attribute2;
2459 --
2460 ELSE
2461 --
2462 IF x_group_rec.match_within_rec.industry_attribute2 = 'Y' THEN
2463 --
2464 v_where_clause := v_where_clause ||
2465 ' AND NVL(o.industry_attribute2,'''||k_VNULL||
2466 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:industry_attribute2,'''||k_VNULL||'''),NVL(o.industry_attribute2,'''||k_VNULL||'''))';
2467
2468 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2469 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.industry_attribute2;
2470
2471 --
2472 END IF;
2473 --
2474 END IF;
2475 --
2476 --
2477 IF x_group_rec.match_across_rec.industry_attribute4 = 'Y' THEN
2478 --
2479 v_where_clause := v_where_clause ||
2480 ' AND NVL(o.industry_attribute4,'''||k_VNULL||''') = NVL(:industry_attribute4,'''||k_VNULL||''')';
2481
2482 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.industry_attribute4;
2483 --
2484 ELSE
2485 --
2486 IF x_group_rec.match_within_rec.industry_attribute4 = 'Y' THEN
2487 --
2488 v_where_clause := v_where_clause ||
2489 ' AND NVL(o.industry_attribute4,'''||k_VNULL||
2490 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:industry_attribute4,'''||k_VNULL||'''),NVL(o.industry_attribute4,'''||k_VNULL||'''))';
2491
2492 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2493 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.industry_attribute4;
2494
2495 --
2496 END IF;
2497 --
2498 END IF;
2499 --
2500
2501 --
2502 IF x_group_rec.match_across_rec.industry_attribute5 = 'Y' THEN
2503 --
2504 v_where_clause := v_where_clause ||
2505 ' AND NVL(o.industry_attribute5,'''||k_VNULL||''') = NVL(:industry_attribute5,'''||k_VNULL||''')';
2506
2507 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.industry_attribute5;
2508 --
2509 ELSE
2510 --
2511 IF x_group_rec.match_within_rec.industry_attribute5 = 'Y' THEN
2512 --
2513 v_where_clause := v_where_clause ||
2514 ' AND NVL(o.industry_attribute5,'''||k_VNULL||
2515 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:industry_attribute5,'''||k_VNULL||'''),NVL(o.industry_attribute5,'''||k_VNULL||'''))';
2516
2517 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2518 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.industry_attribute5;
2519
2520 --
2521 END IF;
2522 --
2523 END IF;
2524 --
2525 --
2526 IF x_group_rec.match_across_rec.industry_attribute6 = 'Y' THEN
2527 --
2528 v_where_clause := v_where_clause ||
2529 ' AND NVL(o.industry_attribute6,'''||k_VNULL||''') = NVL(:industry_attribute6,'''||k_VNULL||''')';
2530
2531 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.industry_attribute6;
2532 --
2533 ELSE
2534 --
2535 IF x_group_rec.match_within_rec.industry_attribute6 = 'Y' THEN
2536 --
2537 v_where_clause := v_where_clause ||
2538 ' AND NVL(o.industry_attribute6,'''||k_VNULL||
2539 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:industry_attribute6,'''||k_VNULL||'''),NVL(o.industry_attribute6,'''||k_VNULL||'''))';
2540
2541 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2542 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.industry_attribute6;
2543
2544 --
2545 END IF;
2546 --
2547 END IF;
2548 --
2549
2550 --
2551 IF x_group_rec.match_across_rec.industry_attribute10 = 'Y' THEN
2552 --
2553 v_where_clause := v_where_clause ||
2554 ' AND NVL(o.industry_attribute10,'''||k_VNULL||''') = NVL(:industry_attribute10,'''||k_VNULL||''')';
2555
2556 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.industry_attribute10;
2557 --
2558 ELSE
2559 --
2560 IF x_group_rec.match_within_rec.industry_attribute10 = 'Y' THEN
2561 --
2562 v_where_clause := v_where_clause ||
2563 ' AND NVL(o.industry_attribute10,'''||k_VNULL||
2564 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:industry_attribute10,'''||k_VNULL||'''),NVL(o.industry_attribute10,'''||k_VNULL||'''))';
2565
2566 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2567 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.industry_attribute10;
2568
2569 --
2570 END IF;
2571 --
2572 END IF;
2573 --
2574 --
2575 IF x_group_rec.match_across_rec.industry_attribute11 = 'Y' THEN
2576 --
2577 v_where_clause := v_where_clause ||
2578 ' AND NVL(o.industry_attribute11,'''||k_VNULL||''') = NVL(:industry_attribute11,'''||k_VNULL||''')';
2579
2580 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.industry_attribute11;
2581 --
2582 ELSE
2583 --
2584 IF x_group_rec.match_within_rec.industry_attribute11 = 'Y' THEN
2585 --
2586 v_where_clause := v_where_clause ||
2587 ' AND NVL(o.industry_attribute11,'''||k_VNULL||
2588 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:industry_attribute11,'''||k_VNULL||'''),NVL(o.industry_attribute11,'''||k_VNULL||'''))';
2589
2590 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2591 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.industry_attribute11;
2592
2593 --
2594 END IF;
2595 --
2596 END IF;
2597 --
2598 --
2599 IF x_group_rec.match_across_rec.industry_attribute12 = 'Y' THEN
2600 --
2601 v_where_clause := v_where_clause ||
2602 ' AND NVL(o.industry_attribute12,'''||k_VNULL||''') = NVL(:industry_attribute12,'''||k_VNULL||''')';
2603
2604 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.industry_attribute12;
2605 --
2606 ELSE
2607 --
2608 IF x_group_rec.match_within_rec.industry_attribute12 = 'Y' THEN
2609 --
2610 v_where_clause := v_where_clause ||
2611 ' AND NVL(o.industry_attribute12,'''||k_VNULL||
2612 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:industry_attribute12,'''||k_VNULL||'''),NVL(o.industry_attribute12,'''||k_VNULL||'''))';
2613
2614 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2615 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.industry_attribute12;
2616
2617 --
2618 END IF;
2619 --
2620 END IF;
2621 --
2622 --
2623 IF x_group_rec.match_across_rec.industry_attribute13 = 'Y' THEN
2624 --
2625 v_where_clause := v_where_clause ||
2626 ' AND NVL(o.industry_attribute13,'''||k_VNULL||''') = NVL(:industry_attribute13,'''||k_VNULL||''')';
2627
2628 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.industry_attribute13;
2629 --
2630 ELSE
2631 --
2632 IF x_group_rec.match_within_rec.industry_attribute13 = 'Y' THEN
2633 --
2634 v_where_clause := v_where_clause ||
2635 ' AND NVL(o.industry_attribute13,'''||k_VNULL||
2636 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:industry_attribute13,'''||k_VNULL||'''),NVL(o.industry_attribute13,'''||k_VNULL||'''))';
2637
2638 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2639 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.industry_attribute13;
2640
2641 --
2642 END IF;
2643 --
2644 END IF;
2645 --
2646 --
2647 IF x_group_rec.match_across_rec.industry_attribute14 = 'Y' THEN
2648 --
2649 v_where_clause := v_where_clause ||
2650 ' AND NVL(o.industry_attribute14,'''||k_VNULL||''') = NVL(:industry_attribute14,'''||k_VNULL||''')';
2651
2652 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.industry_attribute14;
2653 --
2654 ELSE
2655 --
2656 IF x_group_rec.match_within_rec.industry_attribute14 = 'Y' THEN
2657 --
2658 v_where_clause := v_where_clause ||
2659 ' AND NVL(o.industry_attribute14,'''||k_VNULL||
2660 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:industry_attribute14,'''||k_VNULL||'''),NVL(o.industry_attribute14,'''||k_VNULL||'''))';
2661
2662 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2663 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.industry_attribute14;
2664
2665 --
2666 END IF;
2667 --
2668 END IF;
2669 --
2670 --
2671 IF x_group_rec.match_across_rec.attribute1 = 'Y' THEN
2672 --
2673 v_where_clause := v_where_clause ||
2674 ' AND NVL(o.attribute1,'''||k_VNULL||''') = NVL(:attribute1,'''||k_VNULL||''')';
2675
2676 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute1;
2677 --
2678 ELSE
2679 --
2680 IF x_group_rec.match_within_rec.attribute1 = 'Y' THEN
2681 --
2682 v_where_clause := v_where_clause ||
2683 ' AND NVL(o.attribute1,'''||k_VNULL||
2684 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:attribute1,'''||k_VNULL||'''),NVL(o.attribute1,'''||k_VNULL||'''))';
2685
2686 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2687 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute1;
2688
2689 --
2690 END IF;
2691 --
2692 END IF;
2693 --
2694 --
2695 IF x_group_rec.match_across_rec.attribute2 = 'Y' THEN
2696 --
2697 v_where_clause := v_where_clause ||
2698 ' AND NVL(o.attribute2,'''||k_VNULL||''') = NVL(:attribute2,'''||k_VNULL||''')';
2699
2700 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute2;
2701 --
2702 ELSE
2703 --
2704 IF x_group_rec.match_within_rec.attribute2 = 'Y' THEN
2705 --
2706 v_where_clause := v_where_clause ||
2707 ' AND NVL(o.attribute2,'''||k_VNULL||
2708 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:attribute2,'''||k_VNULL||'''),NVL(o.attribute2,'''||k_VNULL||'''))';
2709
2710 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2711 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute2;
2712
2713 --
2714 END IF;
2715 --
2716 END IF;
2717 --
2718 --
2719 IF x_group_rec.match_across_rec.attribute3 = 'Y' THEN
2720 --
2721 v_where_clause := v_where_clause ||
2722 ' AND NVL(o.attribute3,'''||k_VNULL||''') = NVL(:attribute3,'''||k_VNULL||''')';
2723
2724 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute3;
2725 --
2726 ELSE
2727 --
2728 IF x_group_rec.match_within_rec.attribute3 = 'Y' THEN
2729 --
2730 v_where_clause := v_where_clause ||
2731 ' AND NVL(o.attribute3,'''||k_VNULL||
2732 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:attribute3,'''||k_VNULL||'''),NVL(o.attribute3,'''||k_VNULL||'''))';
2733
2734 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2735 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute3;
2736
2737 --
2738 END IF;
2739 --
2740 END IF;
2741 --
2742 --
2743 IF x_group_rec.match_across_rec.attribute4 = 'Y' THEN
2744 --
2745 v_where_clause := v_where_clause ||
2746 ' AND NVL(o.attribute4,'''||k_VNULL||''') = NVL(:attribute4,'''||k_VNULL||''')';
2747
2748 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute4;
2749 --
2750 ELSE
2751 --
2752 IF x_group_rec.match_within_rec.attribute4 = 'Y' THEN
2753 --
2754 v_where_clause := v_where_clause ||
2755 ' AND NVL(o.attribute4,'''||k_VNULL||
2756 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:attribute4,'''||k_VNULL||'''),NVL(o.attribute4,'''||k_VNULL||'''))';
2757
2758 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2759 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute4;
2760
2761 --
2762 END IF;
2763 --
2764 END IF;
2765 --
2766 --
2767 IF x_group_rec.match_across_rec.attribute5 = 'Y' THEN
2768 --
2769 v_where_clause := v_where_clause ||
2770 ' AND NVL(o.attribute5,'''||k_VNULL||''') = NVL(:attribute5,'''||k_VNULL||''')';
2771
2772 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute5;
2773 --
2774 ELSE
2775 --
2776 IF x_group_rec.match_within_rec.attribute5 = 'Y' THEN
2777 --
2778 v_where_clause := v_where_clause ||
2779 ' AND NVL(o.attribute5,'''||k_VNULL||
2780 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:attribute5,'''||k_VNULL||'''),NVL(o.attribute5,'''||k_VNULL||'''))';
2781
2782 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2783 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute5;
2784
2785 --
2786 END IF;
2787 --
2788 END IF;
2789 --
2790 --
2791 IF x_group_rec.match_across_rec.attribute6 = 'Y' THEN
2792 --
2793 v_where_clause := v_where_clause ||
2794 ' AND NVL(o.attribute6,'''||k_VNULL||''') = NVL(:attribute6,'''||k_VNULL||''')';
2795
2796 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute6;
2797 --
2798 ELSE
2799 --
2800 IF x_group_rec.match_within_rec.attribute6 = 'Y' THEN
2801 --
2802 v_where_clause := v_where_clause ||
2803 ' AND NVL(o.attribute6,'''||k_VNULL||
2804 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:attribute6,'''||k_VNULL||'''),NVL(o.attribute6,'''||k_VNULL||'''))';
2805
2806 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2807 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute6;
2808
2809 --
2810 END IF;
2811 --
2812 END IF;
2813 --
2814 --
2815 IF x_group_rec.match_across_rec.attribute7 = 'Y' THEN
2816 --
2817 v_where_clause := v_where_clause ||
2818 ' AND NVL(o.attribute7,'''||k_VNULL||''') = NVL(:attribute7,'''||k_VNULL||''')';
2819
2820 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute7;
2821 --
2822 ELSE
2823 --
2824 IF x_group_rec.match_within_rec.attribute7 = 'Y' THEN
2825 --
2826 v_where_clause := v_where_clause ||
2827 ' AND NVL(o.attribute7,'''||k_VNULL||
2828 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:attribute7,'''||k_VNULL||'''),NVL(o.attribute7,'''||k_VNULL||'''))';
2829
2830 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2831 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute7;
2832
2833 --
2834 END IF;
2835 --
2836 END IF;
2837 --
2838 --
2839 IF x_group_rec.match_across_rec.attribute8 = 'Y' THEN
2840 --
2841 v_where_clause := v_where_clause ||
2842 ' AND NVL(o.attribute8,'''||k_VNULL||''') = NVL(:attribute8,'''||k_VNULL||''')';
2843
2844 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute8;
2845 --
2846 ELSE
2847 --
2848 IF x_group_rec.match_within_rec.attribute8 = 'Y' THEN
2849 --
2850 v_where_clause := v_where_clause ||
2851 ' AND NVL(o.attribute8,'''||k_VNULL||
2852 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:attribute8,'''||k_VNULL||'''),NVL(o.attribute8,'''||k_VNULL||'''))';
2853
2854 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2855 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute8;
2856
2857 --
2858 END IF;
2859 --
2860 END IF;
2861 --
2862 --
2863 IF x_group_rec.match_across_rec.attribute9 = 'Y' THEN
2864 --
2865 v_where_clause := v_where_clause ||
2866 ' AND NVL(o.attribute9,'''||k_VNULL||''') = NVL(:attribute9,'''||k_VNULL||''')';
2867
2868 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute9;
2869 --
2870 ELSE
2871 --
2872 IF x_group_rec.match_within_rec.attribute9 = 'Y' THEN
2873 --
2874 v_where_clause := v_where_clause ||
2875 ' AND NVL(o.attribute9,'''||k_VNULL||
2876 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:attribute9,'''||k_VNULL||'''),NVL(o.attribute9,'''||k_VNULL||'''))';
2877
2878 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2879 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute9;
2880
2881 --
2882 END IF;
2883 --
2884 END IF;
2885 --
2886 --
2887 IF x_group_rec.match_across_rec.attribute10 = 'Y' THEN
2888 --
2889 v_where_clause := v_where_clause ||
2890 ' AND NVL(o.attribute10,'''||k_VNULL||''') = NVL(:attribute10,'''||k_VNULL||''')';
2891
2892 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute10;
2893 --
2894 ELSE
2895 --
2896 IF x_group_rec.match_within_rec.attribute10 = 'Y' THEN
2897 --
2898 v_where_clause := v_where_clause ||
2899 ' AND NVL(o.attribute10,'''||k_VNULL||
2900 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:attribute10,'''||k_VNULL||'''),NVL(o.attribute10,'''||k_VNULL||'''))';
2901
2902 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2903 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute10;
2904
2905 --
2906 END IF;
2907 --
2908 END IF;
2909 --
2910 --
2911 IF x_group_rec.match_across_rec.attribute11 = 'Y' THEN
2912 --
2913 v_where_clause := v_where_clause ||
2914 ' AND NVL(o.attribute11,'''||k_VNULL||''') = NVL(:attribute11,'''||k_VNULL||''')';
2915
2916 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute11;
2917 --
2918 ELSE
2919 --
2920 IF x_group_rec.match_within_rec.attribute11 = 'Y' THEN
2921 --
2922 v_where_clause := v_where_clause ||
2923 ' AND NVL(o.attribute11,'''||k_VNULL||
2924 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:attribute11,'''||k_VNULL||'''),NVL(o.attribute11,'''||k_VNULL||'''))';
2925
2926 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2927 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute11;
2928
2929 --
2930 END IF;
2931 --
2932 END IF;
2933 --
2934 --
2935 IF x_group_rec.match_across_rec.attribute12 = 'Y' THEN
2936 --
2937 v_where_clause := v_where_clause ||
2938 ' AND NVL(o.attribute12,'''||k_VNULL||''') = NVL(:attribute12,'''||k_VNULL||''')';
2939
2940 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute12;
2941 --
2942 ELSE
2943 --
2944 IF x_group_rec.match_within_rec.attribute12 = 'Y' THEN
2945 --
2946 v_where_clause := v_where_clause ||
2947 ' AND NVL(o.attribute12,'''||k_VNULL||
2948 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:attribute12,'''||k_VNULL||'''),NVL(o.attribute12,'''||k_VNULL||'''))';
2949
2950 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2951 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute12;
2952
2953 --
2954 END IF;
2955 --
2956 END IF;
2957 --
2958 --
2959 IF x_group_rec.match_across_rec.attribute13 = 'Y' THEN
2960 --
2961 v_where_clause := v_where_clause ||
2962 ' AND NVL(o.attribute13,'''||k_VNULL||''') = NVL(:attribute13,'''||k_VNULL||''')';
2963
2964 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute13;
2965 --
2966 ELSE
2967 --
2968 IF x_group_rec.match_within_rec.attribute13 = 'Y' THEN
2969 --
2970 v_where_clause := v_where_clause ||
2971 ' AND NVL(o.attribute13,'''||k_VNULL||
2972 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:attribute13,'''||k_VNULL||'''),NVL(o.attribute13,'''||k_VNULL||'''))';
2973
2974 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2975 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute13;
2976
2977 --
2978 END IF;
2979 --
2980 END IF;
2981 --
2982 --
2983 IF x_group_rec.match_across_rec.attribute14 = 'Y' THEN
2984 --
2985 v_where_clause := v_where_clause ||
2986 ' AND NVL(o.attribute14,'''||k_VNULL||''') = NVL(:attribute14,'''||k_VNULL||''')';
2987
2988 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute14;
2989 --
2990 ELSE
2991 --
2992 IF x_group_rec.match_within_rec.attribute14 = 'Y' THEN
2993 --
2994 v_where_clause := v_where_clause ||
2995 ' AND NVL(o.attribute14,'''||k_VNULL||
2996 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:attribute14,'''||k_VNULL||'''),NVL(o.attribute14,'''||k_VNULL||'''))';
2997
2998 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
2999 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute14;
3000
3001 --
3002 END IF;
3003 --
3004 END IF;
3005 --
3006 --
3007 IF x_group_rec.match_across_rec.attribute15 = 'Y' THEN
3008 --
3009 v_where_clause := v_where_clause ||
3010 ' AND NVL(o.attribute15,'''||k_VNULL||''') = NVL(:attribute15,'''||k_VNULL||''')';
3011
3012 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute15;
3013 --
3014 ELSE
3015 --
3016 IF x_group_rec.match_within_rec.attribute15 = 'Y' THEN
3017 --
3018 v_where_clause := v_where_clause ||
3019 ' AND NVL(o.attribute15,'''||k_VNULL||
3020 ''') = DECODE(o.rla_schedule_type_code, :schedule_type, NVL(:attribute15,'''||k_VNULL||'''),NVL(o.attribute15,'''||k_VNULL||'''))';
3021
3022 g_WhereTab(g_WhereTab.COUNT+1):=x_Sched_rec.schedule_type;
3023 g_WhereTab(g_WhereTab.COUNT+1):=x_optional_match_rec.attribute15;
3024
3025 --
3026 END IF;
3027 --
3028 END IF;
3029 --
3030 --end optional
3031
3032 --ATP
3033
3034 IF RLM_MANAGE_DEMAND_SV.IsATPItem(x_group_rec.ship_from_org_id,
3035 x_group_rec.inventory_item_id) THEN
3036 --
3037 v_ship_from_org_id := NULL;
3038 --
3039 ELSE
3040 --
3041 v_ship_from_org_id := x_group_rec.ship_from_org_id;
3042 --
3043 END IF;
3044
3045 IF v_ship_from_org_id IS NOT NULL THEN
3046 --
3047 v_where_clause := v_where_clause ||
3048 ' AND o.ship_from_org_id = :ship_from_org_id';
3049 --
3050 g_WhereTab(g_WhereTab.COUNT+1) := x_Group_rec.ship_from_org_id;
3051 --
3052 v_where_clause := v_where_clause ||
3053 ' AND NVL(o.industry_attribute15,'''||k_VNULL||
3054 ''') = NVL(:industry_attribute15,'''||k_VNULL|| ''')';
3055
3056 g_WhereTab(g_WhereTab.COUNT+1) := x_optional_match_rec.industry_attribute15;
3057
3058 --
3059 END IF;
3060 --
3061 -- blankets
3062 --
3063 IF x_Group_rec.blanket_number IS NOT NULL THEN
3064 v_where_clause := v_where_clause || ' AND o.blanket_number = :blanket_number';
3065 g_whereTab(g_whereTab.COUNT+1) := x_Group_rec.blanket_number;
3066 --
3067 END IF;
3068 --
3069 v_final_sql := v_select_clause||v_where_clause;
3070 --
3071 IF (l_debug <> -1) THEN
3072 rlm_core_sv.dlog(k_DEBUG,'Dynamic SQL',v_final_sql);
3073 rlm_core_sv.dlog(k_DEBUG,'g_wheretab count', g_whereTab.COUNT);
3074 END IF;
3075 -- print bind variables
3076 FOR i in 1..g_whereTab.COUNT
3077 LOOP
3078 IF (l_debug <> -1) THEN
3079 rlm_core_sv.dlog(k_DEBUG,'Value for g_where_tab('||to_char(i)||')', g_whereTab(i));
3080 END IF;
3081 END LOOP;
3082 --
3083 RLM_CORE_SV.OpenDynamicCursor(c_sum,v_final_sql,g_wheretab);
3084 FETCH c_sum INTO x_intransit_qty;
3085 CLOSE c_sum;
3086 --
3087 g_wheretab.delete;
3088 --
3089 IF (l_debug <> -1) THEN
3090 rlm_core_sv.dlog(k_DEBUG,'Intransit Qty', x_intransit_qty);
3091 rlm_core_sv.dpop(k_SDEBUG,'GetIntransitShippedLines');
3092 END IF;
3093 --
3094 EXCEPTION
3095 When others then
3096 IF (l_debug <> -1) THEN
3097 rlm_core_sv.dpop(k_SDEBUG,'GetIntransitShippedLines'||substr(sqlerrm,1,200));
3098 END IF;
3099 raise;
3100 END GetIntransitShippedLines;
3101
3102 END RLM_EXTINTERFACE_SV;