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