[Home] [Help]
PACKAGE BODY: APPS.WMS_SEARCH_ORDER_GLOBALS_PVT
Source
1 Package Body WMS_SEARCH_ORDER_GLOBALS_PVT AS
2 /* $Header: WMSSOGBB.pls 120.2 2005/07/18 05:22:17 ajohnson noship $ */
3
4
5 -- File : WMSSOGBB.pls
6 -- Content : WMS_SEARCH_ORDER_GLOBALS_PVT package body
7 -- Description : This API is created to store Rules Engine Process flow
8 -- Variabls. This API Gobal Variable will be updated by
9 --- WMS_RULES_ENGINE_PVT and to be refrenced by WMSRLSIM.fmb
10
11 -- Notes :
12 -- Created By : Grao 06/16/01 Created
13 -- --------- ------ ------------------------------------------
14
15 --Procedures for logging messages
16 PROCEDURE log_event(p_api_name VARCHAR2, p_label VARCHAR2, p_message VARCHAR2) IS
17 g_pkg_name constant VARCHAR2(50) := 'WMS_SEARCH_ORDER_GLOBALS_PVT';
18 l_module VARCHAR2(255);
19
20 BEGIN
21 --l_progress := l_progress + 10;
22 l_module:= 'wms.plsql.'||g_pkg_name || '.' || p_api_name || '.' || p_label;
23 inv_log_util.trace(p_message, l_module, 9);
24 END log_event;
25
26 PROCEDURE log_error(p_api_name VARCHAR2, p_label VARCHAR2, p_message VARCHAR2) IS
27 g_pkg_name constant VARCHAR2(50) := 'WMS_SEARCH_ORDER_GLOBALS_PVT';
28 l_module VARCHAR2(255);
29
30 BEGIN
31 l_module:= 'wms.plsql.'||g_pkg_name || '.' || p_api_name || '.' || p_label;
32 inv_log_util.trace(p_message, l_module, 9);
33 END log_error;
34
35 PROCEDURE log_error_msg(p_api_name VARCHAR2, p_label VARCHAR2) IS
36 g_pkg_name constant VARCHAR2(50) := 'WMS_SEARCH_ORDER_GLOBALS_PVT';
37 l_module VARCHAR2(255);
38 BEGIN
39 l_module:= 'wms.plsql.'|| g_pkg_name ||'.' || p_api_name || '.' || p_label;
40 inv_log_util.trace(p_label, l_module, 9);
41 END log_error_msg;
42
43 PROCEDURE log_procedure(p_api_name VARCHAR2, p_label VARCHAR2,
44 p_message VARCHAR2) IS
45 g_pkg_name constant VARCHAR2(50) := 'WMS_SEARCH_ORDER_GLOBALS_PVT';
46 l_module VARCHAR2(255);
47 BEGIN
48
49 l_module:= 'wms.plsql.'||g_pkg_name || '.' || p_api_name || '.' || p_label;
50 inv_log_util.trace(p_message, l_module, 9);
51 END log_procedure;
52
53 PROCEDURE log_statement(p_api_name VARCHAR2, p_label VARCHAR2, p_message VARCHAR2) IS
54 g_pkg_name constant VARCHAR2(50) := 'WMS_SEARCH_ORDER_GLOBALS_PVT';
55 l_module VARCHAR2(255);
56 BEGIN
57
58 l_module := 'wms.plsql.' || g_pkg_name || '.' || p_api_name || '.' || p_label;
59 inv_log_util.trace(p_message, l_module, 9);
60 END log_statement;
61
62 --- Function to get the business_object_type name based on the G_PICK_BUSINESS_OBJECT_ID / G_PUTAWAY_BUSINESS_OBJECT_ID
63 --- G_PICK_BUSINESS_OBJECT_ID / G_PUTAWAY_BUSINESS_OBJECT_ID is get updated by Rules Engine API
64
65 FUNCTION get_object_type ( engine_type IN VARCHAR2)
66 RETURN VARCHAR2 is
67 l_object_type VARCHAR2(80) := NULL;
68 l_object_type_id NUMBER := NULL;
69 l_engine_type VARCHAR2(20) := NULL;
70 g_pkg_name constant VARCHAR2(50) := 'WMS_SEARCH_ORDER_GLOBALS_PVT';
71 l_api_name constant VARCHAR2(30) := 'get_object_type';
72
73 BEGIN
74 l_engine_type := engine_type;
75 --
76 --
77 if (l_engine_type = 'PICK' ) then
78 l_object_type_id := G_PICK_BUSINESS_OBJECT_ID ;
79 elsif (l_engine_type = 'PUTAWAY' ) then
80 l_object_type_id := G_PUTAWAY_BUSINESS_OBJECT_ID ;
81 elsif (l_engine_type = 'COSTGROUP' ) then
82 l_object_type_id := G_COSTGROUP_BUSINESS_OBJECT_ID ;
83 else
84 return Null;
85 end if;
86 --
87 if (l_object_type_id > 0 ) then
88 select name into l_object_type
89 from wms_objects
90 where object_id = l_object_type_id ;
91 else
92 return Null;
93 end if;
94 return l_object_type;
95 EXCEPTION
96 WHEN OTHERS THEN
97 if (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) then
98 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
99 end if;
100 RETURN NULL;
101 END;
102 -----------------------------------------
103 --- Function to get the object_ name based on the G_PICK_BUSINESS_OBJECT_ID /
104 ---- G_PUTAWAY_BUSINESS_OBJECT_ID/G_COSTGROUP_BUSINESS_OBJECT_ID
105 --- G_PICK_BUSINESS_OBJECT_ID / G_PUTAWAY_BUSINESS_OBJECT_IDis /
106 ---- G_COSTGROUP_BUSINESS_OBJECT_ID , ORGANIZATIONS_ID, PK_VALUES get updated by Rules Engine API
107
108 FUNCTION get_object_name ( engine_type IN VARCHAR2,
109 org_id IN NUMBER )
110
111 RETURN VARCHAR2 is
112 l_object_type VARCHAR2(80) := NULL;
113 l_object_type_id NUMBER := NULL;
114 l_object_name VARCHAR2(4000);
115 l_engine_type VARCHAR2(20) := NULL;
116 l_org_id NUMBER;
117 l_pk1_value VARCHAR2(150);
118 l_pk2_value VARCHAR2(150);
119 l_pk3_value VARCHAR2(150);
120 l_pk4_value VARCHAR2(150);
121 l_pk5_value VARCHAR2(150);
122
123 g_pkg_name constant VARCHAR2(50) := 'WMS_SEARCH_ORDER_GLOBALS_PVT';
124 l_api_name constant VARCHAR2(30) := 'get_object_NAME';
125
126 BEGIN
127
128 l_engine_type := engine_type;
129 l_org_id := org_id;
130 --
131 --
132 if (l_engine_type = 'PICK' ) then
133 l_object_type_id := G_PICK_BUSINESS_OBJECT_ID ;
134 l_pk1_value := G_PICK_PK1_VALUE;
135 l_pk2_value := G_PICK_PK2_VALUE;
136 l_pk3_value := G_PICK_PK3_VALUE;
137 l_pk4_value := G_PICK_PK4_VALUE;
138 l_pk5_value := G_PICK_PK5_VALUE;
139
140 elsif (l_engine_type = 'PUTAWAY' ) then
141 l_object_type_id := G_PUTAWAY_BUSINESS_OBJECT_ID ;
142 l_pk1_value := G_PUTAWAY_PK1_VALUE;
143 l_pk2_value := G_PUTAWAY_PK2_VALUE;
144 l_pk3_value := G_PUTAWAY_PK3_VALUE;
145 l_pk4_value := G_PUTAWAY_PK4_VALUE;
146 l_pk5_value := G_PUTAWAY_PK5_VALUE;
147
148 elsif (l_engine_type = 'COSTGROUP' ) then
149 l_object_type_id := G_COSTGROUP_BUSINESS_OBJECT_ID ;
150 l_pk1_value := G_COSTGROUP_PK1_VALUE;
151 l_pk2_value := G_COSTGROUP_PK2_VALUE;
152 l_pk3_value := G_COSTGROUP_PK3_VALUE;
153 l_pk4_value := G_COSTGROUP_PK4_VALUE;
154 l_pk5_value := G_COSTGROUP_PK5_VALUE;
155
156 else
157 return Null;
158 end if;
159 --
160 if l_org_id > 0 then
161 if (l_object_type_id > 0 and l_pk1_value is not null ) then
162
163 l_object_name := WMS_Assignment_PVT.GetObjectValueName ( 1,
164 L_OBJECT_TYPE_ID ,
165 L_PK1_VALUE ,
166 L_PK2_VALUE ,
167 L_PK3_VALUE ,
168 L_PK4_VALUE ,
169 L_PK5_VALUE );
170 else
171 return Null;
172 end if;
173 end if;
174 return l_object_name;
175
176 EXCEPTION
177 WHEN OTHERS THEN
178 if (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) then
179 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
180 end if;
181 RETURN NULL;
182 END;
183
184 --- Function to get the Strategy name based on the G_PICK_STRATEGY_ID /G_PUTAWAY_STRATEGY_ID
185 --- G_PICK_STRATEGY_ID /G_PUTAWAY_STRATEGY_ID is get updated by Rules Engine API
186
187 FUNCTION get_strategy_name ( engine_type IN VARCHAR2
188 ,org_id IN NUMBER)
189 RETURN VARCHAR2 is
190 l_strategy VARCHAR2(80) := NULL;
191 l_strategy_id NUMBER ;
192 l_org_id NUMBER ;
193 l_engine_type VARCHAR2(20) := NULL;
194 g_pkg_name constant VARCHAR2(50) := 'WMS_SEARCH_ORDER_GLOBALS_PVT';
195 l_api_name constant VARCHAR2(30) := 'get_strategy_name';
196 BEGIN
197 l_engine_type := engine_type;
198 l_org_id := org_id;
199
200 if (l_engine_type = 'PICK' ) then
201 l_strategy_id := G_PICK_STRATEGY_ID;
202 elsif (l_engine_type = 'PUTAWAY' ) then
203 l_strategy_id := G_PUTAWAY_STRATEGY_ID ;
204 elsif (l_engine_type = 'COSTGROUP' ) then
205 l_strategy_id:= G_COSTGROUP_STRATEGY_ID ;
206 else
207 return Null;
208 end if;
209 --
210 if (l_strategy_id = -999) then
211 l_strategy := 'DEFAULT' ;
212 elsif (l_strategy_id > 0 ) then
213 select distinct name into l_strategy
214 from wms_strategies_vl
215 where organization_id in (l_org_id, -1)
216 and type_code = decode (l_engine_type, 'PICK',2,'PUTAWAY' ,1)
217 and strategy_id = l_strategy_id;
218 else
219 return Null;
220 --return G_PICK_STRATEGY_ID;
221 end if;
222 return l_strategy;
223 EXCEPTION
224 WHEN OTHERS THEN
225 if (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) then
226 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
227 end if;
228 RETURN NULL;
229 END;
230
231
232 -- Function to get the rule name based on the G_Costgroup_ID
233 --- G_costgroup_rule_id is updated by Rules Engine API
234
235 FUNCTION get_rule_name ( engine_type IN VARCHAR2
236 ,org_id IN NUMBER)
237 RETURN VARCHAR2 is
238 l_rule VARCHAR2(80) := NULL;
239 l_rule_id NUMBER ;
240 l_org_id NUMBER ;
241 l_engine_type VARCHAR2(20) := NULL;
242 g_pkg_name constant VARCHAR2(50) := 'WMS_SEARCH_ORDER_GLOBALS_PVT';
243 l_api_name constant VARCHAR2(30) := 'get_rule_name';
244 BEGIN
245 l_engine_type := engine_type;
246 l_org_id := org_id;
247
248 if (l_engine_type = 'PICK' ) then
249 l_rule_id := G_PICK_RULE_ID;
250 elsif (l_engine_type = 'PUTAWAY' ) then
251 l_rule_id := G_PUTAWAY_RULE_ID ;
252 elsif (l_engine_type = 'COSTGROUP' ) then
253 l_rule_id := G_COSTGROUP_RULE_ID ;
254 else
255 return Null;
256 end if;
257 --
258 if (l_rule_id <> 0 ) then
259 select name into l_rule
260 from wms_rules_vl
261 where organization_id in (l_org_id, -1)
262 and rule_id = l_rule_id;
263 else
264 return Null;
265 end if;
266 return l_rule;
267 EXCEPTION
268 WHEN OTHERS THEN
269 if (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) then
270 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
271 end if;
272 RETURN NULL;
273 END;
274
275
276 -- Function to get the costgroup name based on the G_Costgroup_ID
277 --- G_costgroup_id is updated by Rules Engine API
278
279 FUNCTION get_costgroup_name ( engine_type IN VARCHAR2
280 ,org_id IN NUMBER)
281 RETURN VARCHAR2 is
282 l_costgroup VARCHAR2(80) := NULL;
283 l_costgroup_id NUMBER ;
284 l_org_id NUMBER ;
285 l_engine_type VARCHAR2(20) := NULL;
286 g_pkg_name constant VARCHAR2(50) := 'WMS_SEARCH_ORDER_GLOBALS_PVT';
287 l_api_name constant VARCHAR2(30) := 'get_rule_name';
288 BEGIN
289 l_engine_type := engine_type;
290 l_org_id := org_id;
291 l_costgroup_id := G_COSTGROUP_ID ;
292
293 --
294 if (l_costgroup_id <> 0 ) then
295 select cost_group into l_costgroup
296 from cst_cost_groups
297 where organization_id in (l_org_id, -1)
298 and cost_group_id = l_costgroup_id;
299 else
300 return Null;
301 end if;
302 return l_costgroup;
303 EXCEPTION
304 WHEN OTHERS THEN
305 if (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) then
306 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
307 end if;
308 RETURN NULL;
309 END;
310
311
312
313 -- Function to get the costgroup Desc based on the G_Costgroup_ID
314 --- G_costgroup_id is updated by Rules Engine API
315
316 FUNCTION get_costgroup_desc ( engine_type IN VARCHAR2
317 ,org_id IN NUMBER)
318 RETURN VARCHAR2 is
319 l_costgroup_desc VARCHAR2(80) := NULL;
320 l_costgroup_id NUMBER ;
321 l_org_id NUMBER ;
322 l_engine_type VARCHAR2(20) := NULL;
323 g_pkg_name constant VARCHAR2(50) := 'WMS_SEARCH_ORDER_GLOBALS_PVT';
324 l_api_name constant VARCHAR2(30) := 'get_rule_name';
325 BEGIN
326 l_engine_type := engine_type;
327 l_org_id := org_id;
328 l_costgroup_id := G_COSTGROUP_ID ;
329
330 --
331 if (l_costgroup_id <> 0 ) then
332 select cost_group into l_costgroup_desc
333 from cst_cost_groups
334 where organization_id in (l_org_id, -1)
335 and cost_group_id = l_costgroup_id;
336 else
337 return Null;
338 end if;
339 return l_costgroup_desc;
340 EXCEPTION
341 WHEN OTHERS THEN
342 if (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) then
343 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
344 end if;
345 RETURN NULL;
346 END;
347
348 --- Initilize Global Variables
349 ------------------------------------------------------
350 Procedure init_global_variables is
351 begin
352 --- Pick Search order Global Variables
353
354 G_PICK_BUSINESS_OBJECT_ID := NULL;
355 G_PICK_OBJECT := NULL;
356 G_PICK_PK1_VALUE := NULL;
357 G_PICK_PK2_VALUE := NULL;
358 G_PICK_PK3_VALUE := NULL;
359 G_PICK_PK4_VALUE := NULL;
360 G_PICK_PK5_VALUE := NULL;
361 G_PICK_STRATEGY_ID := NULL;
362 G_PICK_RULE_ID := NULL;
363 G_PICK_HEADER_ID := NULL;
364 G_PICK_SEQ_NUM := NULL;
365
366 --- Putaway Search order Global Variables
367
368 G_PUTAWAY_BUSINESS_OBJECT_ID := null;
369 G_PUTAWAY_OBJECT := null;
370 G_PUTAWAY_PK1_VALUE := NULL;
371 G_PUTAWAY_PK2_VALUE := NULL;
372 G_PUTAWAY_PK3_VALUE := NULL;
373 G_PUTAWAY_PK4_VALUE := NULL;
374 G_PUTAWAY_PK5_VALUE := NULL;
375 G_PUTAWAY_STRATEGY_ID := null;
376 G_PUTAWAY_RULE_ID := NULL;
377 G_PUTAWAY_HEADER_ID := NULL;
378 G_PUTAWAY_SEQ_NUM := NULL;
379
380 --- Cost Group Search order Global Variables
381
382 G_COSTGROUP_BUSINESS_OBJECT_ID := null;
383 G_COSTGROUP_OBJECT := null;
384 G_COSTGROUP_PK1_VALUE := NULL;
385 G_COSTGROUP_PK2_VALUE := NULL;
386 G_COSTGROUP_PK3_VALUE := NULL;
387 G_COSTGROUP_PK4_VALUE := NULL;
388 G_COSTGROUP_PK5_VALUE := NULL;
389 G_COSTGROUP_STRATEGY_ID := null;
390 G_COSTGROUP_RULE_ID := NULL;
391 G_COSTGROUP_SEQ_NUM := NULL;
392
393
394 G_COSTGROUP_ID := NULL;
395 ----
396 G_SIMULATION_MODE := 'N' ;
397 end init_global_variables ;
398 -------------------------------------------------------
399
400 Procedure Simulate_rules ( p_mo_line_id IN VARCHAR2,
401 p_simulation_flag IN NUMBER,
402 p_simulation_id IN NUMBER,
403 x_msg_data OUT NOCOPY varchar2,
404 x_return_status OUT NOCOPY varchar2,
405 x_return_status_qty OUT NOCOPY varchar2
406 ) is
407
408 l_return_status_qty varchar2(1);
409 l_return_status varchar2(1);
410 l_msg_count number;
411 l_msg_data varchar2(240);
412 l_start date;
413 l_end date;
414 l_message varchar2(1000);
415 l_changed number;
416 l_reservations inv_reservation_global.mtl_reservation_tbl_type;
417 l_line_id NUMBER;
418 l_simulation_mode NUMBER;
419 l_simulation_id NUMBER;
420 l_rec_count Number;
421
422 l_debug NUMBER;
423
424 l_organization_id number;
425 l_inventory_item_id number;
426 begin
427
428 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
429 --
430 If l_debug = 1 THEN
431 log_procedure('WMS_SEARCH_ORDER_GLOBALS_PVT', 'Simulate Rule', 'Start Simulate Rules');
432 log_event('WMS_SEARCH_ORDER_GLOBALS_PVT', 'init_global_variables', 'Populate Item details in Suggestion Block');
433 log_event('WMS_SEARCH_ORDER_GLOBALS_PVT', 'Delete', 'Deleteting all records from WTT');
434 End if;
435 --- Populate Item details in Suggestion Block ---
436 l_line_id := p_mo_line_id;
437 l_simulation_id := p_simulation_id;
438 l_simulation_mode := p_simulation_flag;
439
440 ---- Initilizeing Search Order Global Variables
441 wms_search_order_globals_pvt.init_global_variables;
442
443 rollback;
444 inv_quantity_tree_pvt.clear_quantity_cache;
445 --- Deleteting all records from WTT
446 delete wms_transactions_temp ;
447
448 --- Deleting All record from mmtt
449 delete mtl_material_transactions_temp
450 where move_order_line_id = l_line_id;
451
452 ---- Deleting trace header and line records based on Gobal Variables
453
454 DELETE_TRACE_ROWS; --(l_line_id);
455 G_SIMULATION_MODE := 'Y' ;
456
457 if l_debug = 1 then
458 log_procedure('WMS_SEARCH_ORDER_GLOBALS_PVT', 'create_suggestions',
459 'Calling wms_engine_pvt.create_suggestions ');
460 log_statement('SEARCH_ORDER_GLOBALS', 'p_transaction_temp_id =>' ,to_char(l_line_id));
461 log_statement('', 'p_simulation_id =>' ,to_char(l_simulation_id));
462 log_statement('', 'p_simulation_mode =>' ,to_char(l_simulation_id));
463 end if;
464
465 wms_engine_pvt.create_suggestions
466 (
467 p_api_version => 1.0,
468 p_init_msg_list => fnd_api.g_true,
469 p_commit => fnd_api.g_false,
470 p_validation_level => fnd_api.g_valid_level_full,
471 x_return_status => l_return_status,
472 x_msg_count => l_msg_count,
473 x_msg_data => l_msg_data,
474 p_transaction_temp_id => l_line_id,
475 p_reservations => l_reservations,
476 p_suggest_serial => fnd_api.g_true,
477 p_simulation_id => l_simulation_id,
478 p_simulation_mode => l_simulation_mode );
479
480 x_return_status := l_return_status;
481 x_msg_data := l_msg_data;
482
483 if l_debug = 1 then
484 log_statement('', 'x_return_status =>' ,l_return_status );
485 end if;
486
487 ---------------
488
489 if ( l_return_status = 'S' ) then
490
491 select organization_id, inventory_item_id
492 into l_organization_id, l_inventory_item_id
493 from mtl_txn_request_lines
494 where line_id = l_line_id;
495
496 if l_debug = 1 then
497 log_procedure('WMS_SEARCH_ORDER_GLOBALS_PVT', 'release_lock',
498 'Calling inv_quantity_tree_pvt.release_lock');
499 end if;
500
501 inv_quantity_tree_pvt.release_lock(
502 p_api_version_number => 1.0
503 , p_init_msg_lst => fnd_api.g_false
504 , x_return_status => l_return_status_qty
505 , x_msg_count => l_msg_count
506 , x_msg_data => l_msg_data
507 , p_organization_id => l_organization_id
508 , p_inventory_item_id => l_inventory_item_id);
509
510 end if;
511
512 x_return_status_qty := l_return_status_qty;
513 if l_debug = 1 then
514 log_procedure('', 'End ',
515 ' End of Simulate Rules');
516 end if;
517 end;
518 -----------------------------------------------------------------------------------
519 ----- This procedure is called by Create Suggestions for inserting Trace data
520 ----- in Header and lines tables. Data is inserted into trace tables in simulation
521 ----- mode as well as in production mode it the debug flag is set to 'Y'
522
523 ----- One record is inserted for Picking Simulation and one record is inserted for
524 ----- Putaway simulation. In case of Transfer Picking_header_id is stored in the putaway
525 ----- record
526 -----------------------------------------------------------------------------------
527 procedure insert_trace_header
528 (
529 p_api_version in NUMBER
530 ,p_init_msg_list in VARCHAR2 DEFAULT fnd_api.g_false
531 ,p_validation_level in NUMBER DEFAULT fnd_api.g_valid_level_full
532 ,x_return_status out NOCOPY VARCHAR2
533 ,x_msg_count out NOCOPY number
534 ,x_msg_data out NOCOPY varchar2
535 ,x_header_id out NOCOPY NUMBER
536 ,p_pick_header_id in NUMBER
537 ,p_move_order_line_id in NUMBER
538 ,p_total_qty in NUMBER
539 ,p_secondary_total_qty in NUMBER
540 ,p_type_code in NUMBER
541 ,p_business_object_id in NUMBER
542 ,p_object_id in NUMBER
543 ,p_strategy_id in NUMBER
544 ) IS
545 -- API standard variables
546 l_api_version constant number := 1.0;
547 g_pkg_name constant VARCHAR2(50) := 'WMS_SEARCH_ORDER_GLOBALS_PVT';
548 l_api_name constant varchar2(30) := ' insert_trace_header';
549 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
550 l_row_id VARCHAR2(20);
551 l_object_name VARCHAR2(4000);
552 l_engine_type VARCHAR2(20) := NULL;
553 l_date DATE;
554 l_user_id NUMBER;
555 l_login_id NUMBER;
556 l_found BOOLEAN;
557 l_header_id NUMBER;
558 l_sid NUMBER;
559
560 begin
561
562 -- Standard call to check for call compatibility
563 if not fnd_api.compatible_api_call( l_api_version
564 ,l_api_version
565 ,l_api_name
566 ,g_pkg_name ) then
567 raise fnd_api.g_exc_unexpected_error;
568 end if;
569
570 -- Initialize message list if p_init_msg_list is set to TRUE
571 if fnd_api.to_boolean( p_init_msg_list ) then
572 fnd_msg_pub.initialize;
573 end if;
574
575 IF (nvl(p_move_order_line_id,0) > 0) then
576
577 SELECT wms_rule_trace_header_s.NEXTVAL INTO l_header_id FROM dual;
578
579 /* get who column information */
580
581 SELECT Sysdate INTO l_date FROM dual;
582
583 l_user_id := fnd_global.user_id;
584 l_login_id := fnd_global.login_id;
585
586 -- select rawtohex(dbms_session.unique_session_id) into l_sid from dual;
587
588
589 if p_type_code = 2 then
590 l_engine_type := 'PICK';
591 elsif p_type_code = 1 then
592 l_engine_type := 'PUTAWAY';
593 end if;
594
595 l_object_name := get_object_name(l_engine_type, 1);
596
597
598 /* call the table insert row to do the insert */
599 insert_headers_row
600 (
601 x_header_id => l_header_id
602 ,x_pick_header_id => p_pick_header_id
603 ,x_move_order_line_id => p_move_order_line_id
604 ,x_total_qty => p_total_qty
605 ,x_secondary_total_qty => p_secondary_total_qty
606 ,x_type_code => p_type_code
607 ,x_business_object_id => p_business_object_id
608 ,x_object_id => p_object_id
609 ,x_strategy_id => p_strategy_id
610 ,x_creation_date => l_date
611 ,x_created_by => l_user_id
612 ,x_last_update_date => l_date
613 ,x_last_updated_by => l_user_id
614 ,x_last_update_login => l_login_id
615 ,x_object_name => l_object_name
616 ,x_simulation_mode => G_simulation_mode
617 ,x_sid => l_login_id
618 );
619
620 x_return_status := l_return_status;
621 x_header_id := l_header_id;
622
623 ---- Storing header ID in Global Variables for deleting the trace records
624 ---- before next simulation
625 if ( nvl(p_pick_header_id,0) > 0 ) then
626 G_PUTAWAY_HEADER_ID := l_header_id;
627 else
628 G_PICK_HEADER_ID := l_header_id;
629 end if ;
630 end if;
631 EXCEPTION
632 when fnd_api.g_exc_error then
633 x_return_status := fnd_api.g_ret_sts_error;
634 fnd_msg_pub.count_and_get( p_count => x_msg_count
635 ,p_data => x_msg_data );
636
637 when fnd_api.g_exc_unexpected_error then
638 x_return_status := fnd_api.g_ret_sts_unexp_error;
639 fnd_msg_pub.count_and_get( p_count => x_msg_count
640 ,p_data => x_msg_data );
641
642 when others then
643 x_return_status := fnd_api.g_ret_sts_unexp_error;
644 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
645 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
646 end if;
647 /* fnd_msg_pub.count_and_get( p_count => x_msg_count
648 ,p_data => x_msg_data );*/
649
650 end insert_trace_header;
651 ------------------------------------------------------------------------------------
652 ---- Based on the rows im the WMS_SEARCH_ORDER_GLOBALS_PVT.pre_suggestions_record_tbl
653 ---- equal number of records are created in the trace lines table for each Header Id
654 --------------------------------------------------------------------------------------
655 procedure insert_trace_lines
656 (
657 p_api_version in NUMBER
658 ,p_init_msg_list in VARCHAR2 DEFAULT fnd_api.g_false
659 ,p_validation_level in NUMBER DEFAULT fnd_api.g_valid_level_full
660 ,x_return_status out NOCOPY VARCHAR2
661 ,x_msg_count out NOCOPY number
662 ,x_msg_data out NOCOPY varchar2
663 ,p_header_id in NUMBER
664 ,p_rule_id in NUMBER
665 ,p_pre_suggestions in WMS_SEARCH_ORDER_GLOBALS_PVT.pre_suggestions_record_tbl
666 ) IS
667 -- API standard variables
668 g_pkg_name constant VARCHAR2(50) := 'WMS_SEARCH_ORDER_GLOBALS_PVT';
669 l_api_version constant number := 1.0;
670 l_api_name constant varchar2(30) := 'insert_trace_lines';
671 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
672 l_date DATE;
673 l_user_id NUMBER;
674 l_login_id NUMBER;
675 l_found BOOLEAN;
676 l_line_id NUMBER;
677 l_pre_suggestions WMS_SEARCH_ORDER_GLOBALS_PVT.pre_suggestions_record ;
678 l_header_id NUMBER;
679 l_rule_id NUMBER;
680 l_index BINARY_INTEGER;
681 begin
682
683 -- Standard call to check for call compatibility
684 if not fnd_api.compatible_api_call( l_api_version
685 ,p_api_version
686 ,l_api_name
687 ,g_pkg_name ) then
688 raise fnd_api.g_exc_unexpected_error;
689 end if;
690
691 -- Initialize message list if p_init_msg_list is set to TRUE
692 if fnd_api.to_boolean( p_init_msg_list ) then
693 fnd_msg_pub.initialize;
694 end if;
695
696 if ( nvl(p_header_id ,0 ) > 0 and nvl(p_rule_id,0) > 0 ) then
697
698 /* get who column information */
699 SELECT Sysdate INTO l_date FROM dual;
700 l_user_id := fnd_global.user_id;
701 l_login_id := fnd_global.login_id;
702
703 -- l_pre_suggestions := p_pre_suggestions ;
704 l_header_id := p_header_id;
705 l_rule_id := p_rule_id;
706
707 l_index := p_pre_suggestions.FIRST;
708 LOOP
709 -- Bug #3107777
710 if l_index is null then
711 exit;
712 end if;
713 SELECT wms_rule_trace_lines_s.NEXTVAL INTO l_line_id FROM dual;
714
715 /* call the table insert procedure to do the insert */
716 insert_lines_row
717 (
718 x_header_id => l_header_id
719 ,x_line_id => l_line_id
720 ,x_rule_id => l_rule_id
721 ,x_quantity => p_pre_suggestions(l_index).quantity
722 ,x_revision => p_pre_suggestions(l_index).revision
723 ,x_lot_number => p_pre_suggestions(l_index).lot_number
724 ,x_lot_expiration_date => p_pre_suggestions(l_index).lot_expiration_date
725 ,x_serial_number => p_pre_suggestions(l_index).serial_number
726 ,x_subinventory_code => p_pre_suggestions(l_index).subinventory_code
727 ,x_locator_id => p_pre_suggestions(l_index).locator_id
728 ,x_lpn_id => p_pre_suggestions(l_index).lpn_id
729 ,x_cost_group_id => p_pre_suggestions(l_index).cost_group_id
730 ,x_uom_code => p_pre_suggestions(l_index).uom_code
731 ,x_remaining_qty => p_pre_suggestions(l_index).remaining_qty
732 ,x_ATT_qty => p_pre_suggestions(l_index).ATT_qty
733 ,x_suggested_qty => p_pre_suggestions(l_index).suggested_qty
734 ,x_sec_uom_code => p_pre_suggestions(l_index).secondary_uom_code --new
735 ,x_sec_qty => p_pre_suggestions(l_index).secondary_qty --new
736 ,x_sec_ATT_qty => p_pre_suggestions(l_index).secondary_ATT_qty --new
737 ,x_sec_suggested_qty => p_pre_suggestions(l_index).secondary_suggested_qty --new
738 ,x_grade_code => p_pre_suggestions(l_index).grade_code --new
739 ,x_same_subinv_loc_flag => p_pre_suggestions(l_index).same_subinv_loc_flag
740 ,x_ATT_qty_flag => p_pre_suggestions(l_index).ATT_qty_flag
741 ,x_consist_string_flag => p_pre_suggestions(l_index).consist_string_flag
742 ,x_order_string_flag => p_pre_suggestions(l_index).order_string_flag
743 ,x_Material_status_flag => p_pre_suggestions(l_index).Material_status_flag
744 ,x_Pick_UOM_flag => p_pre_suggestions(l_index).Pick_UOM_flag
745 ,x_partial_pick_flag => p_pre_suggestions(l_index).partial_pick_flag
746 ,x_Serial_number_used_flag => p_pre_suggestions(l_index).Serial_number_used_flag
747 ,x_CG_comingle_flag => p_pre_suggestions(l_index).CG_comingle_flag
748 ,x_entire_lpn_flag => p_pre_suggestions(l_index).entire_lpn_flag
749 ,x_comments => p_pre_suggestions(l_index).comments
750 ,x_creation_date => l_date
751 ,x_created_by => l_user_id
752 ,x_last_update_date => l_date
753 ,x_last_updated_by => l_user_id
754 ,x_last_update_login => l_login_id
755 );
756 EXIT WHEN l_index = p_pre_suggestions.LAST;
757 l_index := p_pre_suggestions.NEXT(l_index);
758
759 END LOOP;
760 x_return_status := l_return_status;
761 end if;
762 EXCEPTION
763 when fnd_api.g_exc_error then
764 x_return_status := fnd_api.g_ret_sts_error;
765 fnd_msg_pub.count_and_get( p_count => x_msg_count
766 ,p_data => x_msg_data );
767
768 when fnd_api.g_exc_unexpected_error then
769 x_return_status := fnd_api.g_ret_sts_unexp_error;
770 fnd_msg_pub.count_and_get( p_count => x_msg_count
771 ,p_data => x_msg_data );
772
773 when others then
774 x_return_status := fnd_api.g_ret_sts_unexp_error;
775 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
776 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
777 end if;
778 /*fnd_msg_pub.count_and_get( p_count => x_msg_count
779 ,p_data => x_msg_data ); */
780
781 end insert_trace_lines;
782
783 -----------------------------------------------------------------------
784 ------------------------------------------------------------------------
785 Procedure insert_headers_row
786 (
787 x_header_id IN NUMBER,
788 x_pick_header_id IN NUMBER,
789 x_move_order_line_id IN NUMBER,
790 x_total_qty IN NUMBER,
791 x_secondary_total_qty IN NUMBER,
792 x_type_code IN NUMBER,
793 x_business_object_id IN NUMBER,
794 x_object_id IN NUMBER,
795 x_strategy_id IN NUMBER,
796 x_last_updated_by IN NUMBER,
797 x_last_update_date IN DATE ,
798 x_created_by IN NUMBER ,
799 x_creation_date IN DATE ,
800 x_last_update_login IN NUMBER ,
801 x_object_name IN VARCHAR2,
802 x_simulation_mode IN VARCHAR2,
803 x_sid IN NUMBER
804 )
805 is
806 ---- This API Call is a Autonomous Procedure ---------------
807
808 PRAGMA AUTONOMOUS_TRANSACTION;
809 -----
810 begin
811 INSERT into WMS_RULE_TRACE_HEADERS (
812 header_id
813 ,pick_header_id
814 ,move_order_line_id
815 ,total_qty
816 ,secondary_total_qty
817 ,type_code
818 ,business_object_id
819 ,object_id
820 ,strategy_id
821 ,creation_date
822 ,created_by
823 ,last_update_date
824 ,last_updated_by
825 ,last_update_login
826 ,object_name
827 ,simulation_mode
828 ,sid
829 ) VALUES
830 (
831 x_header_id
832 ,x_pick_header_id
833 ,x_move_order_line_id
834 ,x_total_qty
835 ,x_secondary_total_qty
836 ,x_type_code
837 ,x_business_object_id
838 ,x_object_id
839 ,x_strategy_id
840 ,x_creation_date
841 ,x_created_by
842 ,x_last_update_date
843 ,x_last_updated_by
844 ,x_last_update_login
845 ,x_object_name
846 ,x_simulation_mode
847 ,x_sid
848 );
849 commit;
850 end insert_headers_row;
851
852 -----------------------------------------------------
853 Procedure insert_lines_row
854 (
855 x_header_id IN NUMBER
856 ,x_line_id IN NUMBER
857 ,x_rule_id IN NUMBER
858 ,x_quantity IN NUMBER
859 ,x_revision IN VARCHAR2
860 ,x_lot_number IN VARCHAR2
861 ,x_lot_expiration_date IN DATE
862 ,x_serial_number IN VARCHAR2
863 ,x_subinventory_code IN VARCHAR2
864 ,x_locator_id IN NUMBER
865 ,x_lpn_id IN NUMBER
866 ,x_cost_group_id IN NUMBER
867 ,x_uom_code IN VARCHAR2
868 ,x_remaining_qty IN NUMBER
869 ,x_ATT_qty IN NUMBER
870 ,x_suggested_qty IN NUMBER
871 ,x_sec_uom_code IN VARCHAR2 -- new
872 ,x_sec_qty IN NUMBER -- new
873 ,x_sec_ATT_qty IN NUMBER -- new
874 ,x_sec_suggested_qty IN NUMBER -- new
875 ,x_grade_code IN VARCHAR2 -- new
876 ,x_same_subinv_loc_flag IN VARCHAR2
877 ,x_ATT_qty_flag IN VARCHAR2
878 ,x_consist_string_flag IN VARCHAR2
879 ,x_order_string_flag IN VARCHAR2
880 ,x_Material_status_flag IN VARCHAR2
881 ,x_Pick_UOM_flag IN VARCHAR2
882 ,x_partial_pick_flag IN VARCHAR2
883 ,x_Serial_number_used_flag IN VARCHAR2
884 ,x_CG_comingle_flag IN VARCHAR2
885 ,x_entire_lpn_flag IN VARCHAR2
886 ,x_comments IN VARCHAR2
887 ,x_creation_date IN DATE
888 ,x_created_by IN NUMBER
889 ,x_last_update_date IN DATE
890 ,x_last_updated_by IN NUMBER
891 ,x_last_update_login IN NUMBER
892 )
893 is
894 ---- This API Call is a Autonomous Procedure ---------------
895
896 PRAGMA AUTONOMOUS_TRANSACTION;
897 ----
898 begin
899 insert into WMS_RULE_TRACE_LINES
900 (
901 header_id
902 ,line_id
903 ,rule_id
904 ,quantity
905 ,revision
906 ,lot_number
907 ,lot_expiration_date
908 ,serial_number
909 ,subinventory_code
910 ,locator_id
911 ,lpn_id
912 ,cost_group_id
913 ,uom_code
914 ,remaining_qty
915 ,ATT_qty
916 ,suggested_qty -- new
917 ,secondary_uom_code -- new
918 ,secondary_quantity -- new
919 ,secondary_ATT_qty -- new
920 ,secondary_suggested_qty -- new
921 ,grade_code -- new
922 ,same_subinv_loc_flag
923 ,ATT_qty_flag
924 ,consist_string_flag
925 ,order_string_flag
926 ,Material_status_flag
927 ,Pick_UOM_flag
928 ,partial_pick_flag
929 ,Serial_number_used_flag
930 ,CG_comingle_flag
931 ,entire_lpn_flag
932 ,comments
933 ,last_updated_by
934 ,last_update_date
935 ,created_by
936 ,creation_date
937 ,last_update_login
938 ) VALUES
939 (
940 x_header_id
941 ,x_line_id
942 ,x_rule_id
943 ,x_quantity
944 ,x_revision
945 ,x_lot_number
946 ,x_lot_expiration_date
947 ,x_serial_number
948 ,x_subinventory_code
949 ,x_locator_id
950 ,x_lpn_id
951 ,x_cost_group_id
952 ,x_uom_code
953 ,x_remaining_qty
954 ,x_ATT_qty
955 ,x_suggested_qty
956 ,x_sec_uom_code -- new
957 ,x_sec_qty -- new
958 ,x_sec_ATT_qty -- new
959 ,x_sec_suggested_qty -- new
960 ,x_grade_code
961 ,x_same_subinv_loc_flag
962 ,x_ATT_qty_flag
963 ,x_consist_string_flag
964 ,x_order_string_flag
965 ,x_Material_status_flag
966 ,x_Pick_UOM_flag
967 ,x_partial_pick_flag
968 ,x_Serial_number_used_flag
969 ,x_CG_comingle_flag
970 ,x_entire_lpn_flag
971 ,x_comments
972 ,x_last_updated_by
973 ,x_last_update_date
974 ,x_created_by
975 ,x_creation_date
976 ,x_last_update_login
977 );
978 commit;
979 end insert_lines_row;
980 ------------------------------------------------------
981 ----------------------------------
982 -- Function that return 'Y' and 'N' if the passed item_id is
983 -- in Global Variables
984
985 FUNCTION IS_Object_selected ( p_move_order_line_id number,
986 p_engine_type Varchar2,
987 p_object_type varchar2,
988 p_object_id number )
989 RETURN VARCHAR2 is
990 l_object_type VARCHAR2(80) := NULL;
991 l_object_id NUMBER := NULL;
992 l_engine_type VARCHAR2(20) := NULL;
993 l_return_status VARCHAR2(1) := 'N';
994 l_rule_id NUMBER;
995 l_line_id NUMBER;
996
997 g_pkg_name constant VARCHAR2(50) := 'WMS_SEARCH_ORDER_GLOBALS_PVT';
998 l_api_name constant VARCHAR2(30) := 'IS_object_Selected';
999
1000 BEGIN
1001 l_engine_type := p_engine_type;
1002 l_object_type := p_object_type;
1003 l_object_id := p_object_id;
1004 l_line_id := p_move_order_line_id ;
1005 --
1006 --
1007 if (l_engine_type = 'PICK' ) then
1008
1009 if ( l_object_type = 'SO' ) then
1010 if (l_object_id = G_PICK_BUSINESS_OBJECT_ID ) then
1011 l_return_status := 'Y';
1012 end if;
1013 elsif ( l_object_type = 'STG' ) then
1014 if (l_object_id = G_PICK_STRATEGY_ID ) then
1015 l_return_status := 'Y';
1016 end if;
1017 elsif ( l_object_type = 'RULE' ) then
1018 begin
1019 select distinct pick_rule_id into l_rule_id
1020 from wms_suggestions_temp_v
1021 where pick_rule_id = l_object_id ;
1022 -- and move_order_line_id = l_line_id;
1023
1024 if (l_object_id = l_rule_id ) then
1025 l_return_status := 'Y';
1026 end if;
1027 exception
1028 when no_data_found then
1029 l_return_status := 'N';
1030
1031 end ;
1032
1033 end if;
1034
1035 elsif (l_engine_type = 'PUTAWAY' ) then
1036
1037 if ( l_object_type = 'SO' ) then
1038 if (l_object_id = G_PUTAWAY_BUSINESS_OBJECT_ID ) then
1039 l_return_status := 'Y';
1040 end if;
1041 elsif ( l_object_type = 'STG' ) then
1042 if (l_object_id = G_PUTAWAY_STRATEGY_ID ) then
1043 l_return_status := 'Y';
1044 end if;
1045 end if;
1046
1047 elsif (l_engine_type = 'COSTGROUP' ) then
1048
1049 if ( l_object_type = 'SO' ) then
1050 if (l_object_id = G_COSTGROUP_BUSINESS_OBJECT_ID ) then
1051 l_return_status := 'Y';
1052 end if;
1053 elsif ( l_object_type = 'STG' ) then
1054 if (l_object_id = G_COSTGROUP_STRATEGY_ID ) then
1055 l_return_status := 'Y';
1056 end if;
1057 elsif ( l_object_type = 'RULE' ) then
1058 if (l_object_id = G_COSTGROUP_RULE_ID ) then
1059 l_return_status := 'Y';
1060 end if;
1061
1062 end if;
1063
1064 end if;
1065 return l_return_status;
1066 EXCEPTION
1067 WHEN OTHERS THEN
1068 if (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) then
1069 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1070 end if;
1071 RETURN 'N';
1072 END IS_Object_selected;
1073
1074 ---------------------------------
1075 --- Overloaded Function ---------
1076 -- Function that return 'Y' and 'N' if the passed item_id is
1077 -- in Global Variables
1078
1079 FUNCTION IS_BO_Object_selected ( p_move_order_line_id number,
1080 p_engine_type Varchar2, /*'PICK', 'PUTAWAY' .. */
1081 p_object_type varchar2, /* 'SO', 'BO' ... */
1082 p_object varchar2 ) /* object name*/
1083 RETURN VARCHAR2 is
1084 l_object_type VARCHAR2(80) := NULL;
1085 l_object varchar2(50) := NULL;
1086 l_engine_type VARCHAR2(20) := NULL;
1087 l_return_status VARCHAR2(1) := 'N';
1088 l_rule_id NUMBER;
1089 l_line_id NUMBER;
1090 l_org_id NUMBER;
1091 l_g_object VARCHAR2(50); /* to Hold the G_PICK_OBJECT/ G_PUTAWAY_OBJECT /G_COSTGROUP_OBJECT value */
1092
1093 g_pkg_name constant VARCHAR2(50) := 'WMS_SEARCH_ORDER_GLOBALS_PVT';
1094 l_api_name constant VARCHAR2(30) := 'IS_BO_object_Selected';
1095
1096
1097 BEGIN
1098 l_engine_type := p_engine_type;
1099 l_object_type := p_object_type;
1100 l_line_id := p_move_order_line_id ;
1101 l_object := p_object;
1102 --
1103 --
1104
1105 if l_line_id > 0 then
1106 select organization_id into l_org_id
1107 from mtl_txn_request_lines
1108 where line_id = l_line_id;
1109 end if;
1110
1111 if (l_engine_type = 'PICK' ) then
1112 if (WMS_SEARCH_ORDER_GLOBALS_PVT.G_PICK_OBJECT is not null ) then
1113 l_g_object := WMS_SEARCH_ORDER_GLOBALS_PVT.G_PICK_OBJECT;
1114 else
1115 WMS_SEARCH_ORDER_GLOBALS_PVT.G_PICK_OBJECT := get_object_name(l_engine_type, l_org_id);
1116 l_g_object := WMS_SEARCH_ORDER_GLOBALS_PVT.G_PICK_OBJECT;
1117 end if;
1118 elsif (l_engine_type = 'PUTAWAY') then
1119 if (WMS_SEARCH_ORDER_GLOBALS_PVT.G_PUTAWAY_OBJECT is not null ) then
1120 l_g_object := WMS_SEARCH_ORDER_GLOBALS_PVT.G_PUTAWAY_OBJECT;
1121 else
1122 WMS_SEARCH_ORDER_GLOBALS_PVT.G_PUTAWAY_OBJECT := get_object_name(l_engine_type, l_org_id);
1123 l_g_object := WMS_SEARCH_ORDER_GLOBALS_PVT.G_PUTAWAY_OBJECT;
1124 end if;
1125 elsif (l_engine_type = 'COSTGROUP') then
1126 if (WMS_SEARCH_ORDER_GLOBALS_PVT.G_COSTGROUP_OBJECT is not null ) then
1127 l_g_object := WMS_SEARCH_ORDER_GLOBALS_PVT.G_COSTGROUP_OBJECT;
1128 else
1129 WMS_SEARCH_ORDER_GLOBALS_PVT.G_COSTGROUP_OBJECT := get_object_name(l_engine_type, l_org_id);
1130 l_g_object := WMS_SEARCH_ORDER_GLOBALS_PVT.G_COSTGROUP_OBJECT;
1131 end if;
1132 end if;
1133 IF (l_g_object = l_object ) then
1134 l_return_status := 'Y';
1135 else
1136 l_return_status := 'N';
1137 end if;
1138
1139 return l_return_status;
1140 EXCEPTION
1141 WHEN OTHERS THEN
1142 if (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) then
1143 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1144 end if;
1145 RETURN 'N';
1146
1147 END IS_BO_Object_selected;
1148 -----------------------------------------------------------------------------
1149 --- Trace Tables to be deleted - WMS_RULE_TRACE_HEADERS, WMS_RULE_TRACE_LINES
1150 -- Deleting trace header and line records based on Gobal Variables
1151 --- stored at the time of Insert header row for Picking and Putaway
1152 --- Records. After deleting the trace records G_PICK_HEADER_ID and
1153 --- G_PUTAWAY_HEADER_ID is initilized to 0
1154
1155 PROCEDURE DELETE_TRACE_ROWS is
1156 PRAGMA AUTONOMOUS_TRANSACTION;
1157 l_user_id number;
1158 l_login_id number;
1159 BEGIN
1160
1161 l_user_id := fnd_global.user_id;
1162 l_login_id := fnd_global.login_id;
1163 -- Deleteing all trace records created in simulation mode for this user
1164 delete wms_rule_trace_lines
1165 where header_id in (select header_id
1166 from wms_rule_trace_headers
1167 where simulation_mode = 'Y'
1168 and sid = l_login_id);
1169 delete wms_rule_trace_headers
1170 where simulation_mode = 'Y'
1171 and sid = l_login_id;
1172 COMMIT;
1173 --end if;
1174 END DELETE_TRACE_ROWS;
1175 --------------------------
1176 -------------------------------------------------
1177 --- This procedure call is used to populate records into
1178 --- Three temp table one for each material suggestions, lot numbers
1179 --- and serial number tables. Lot and Serial Tables will be populated
1180 --- based on the lot_insert_flag and serial_insert_flags
1181 --- '0' - for no records to be inserted and '1' for records to be inserted
1182 --- The data in these three tables will be used by Run Time trace form
1183
1184 procedure insert_txn_trace_rows(
1185 p_api_version in NUMBER
1186 ,p_init_msg_list in VARCHAR2 DEFAULT fnd_api.g_false
1187 ,p_validation_level in NUMBER DEFAULT fnd_api.g_valid_level_full
1188 ,x_return_status out NOCOPY VARCHAR2
1189 ,x_msg_count out NOCOPY number
1190 ,x_msg_data out NOCOPY varchar2
1191 ,p_txn_header_id in number
1192 ,p_insert_lot_flag in number
1193 ,p_insert_serial_flag in number) is
1194
1195 ---- This API Call is a Autonomous Procedure ---------------
1196 --- PRAGMA AUTONOMOUS_TRANSACTION;
1197
1198 -- API standard variables
1199 l_api_version constant number := 1.0;
1200 g_pkg_name constant VARCHAR2(50) := 'WMS_SEARCH_ORDER_GLOBALS_PVT';
1201 l_api_name constant varchar2(30) := 'insert_txn_trace_rows';
1202 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1203 l_txn_header_id number;
1204 l_insert_lot_flag number;
1205 l_insert_serial_flag number;
1206
1207 begin
1208
1209 l_txn_header_id := nvl(p_txn_header_id,0);
1210 l_insert_lot_flag := nvl(p_insert_lot_flag,0);
1211 l_insert_serial_flag := nvl(p_insert_serial_flag,0);
1212
1213 -- Standard call to check for call compatibility
1214
1215 if not fnd_api.compatible_api_call( l_api_version
1216 ,l_api_version
1217 ,l_api_name
1218 ,g_pkg_name ) then
1219 raise fnd_api.g_exc_unexpected_error;
1220 end if;
1221
1222 --- If transaction_header id passed is not null records inserted into following tables
1223 ---
1224
1225 If ( l_txn_header_id > 0) then
1226
1227 insert into WMS_MATERIAL_TXN_TRACE
1228 (
1229 TRANSACTION_HEADER_ID ,
1230 TRANSACTION_TEMP_ID ,
1231 SOURCE_CODE ,
1232 SOURCE_LINE_ID ,
1233 TRANSACTION_MODE ,
1234 LOCK_FLAG ,
1235 LAST_UPDATE_DATE ,
1236 LAST_UPDATED_BY ,
1237 CREATION_DATE ,
1238 CREATED_BY ,
1239 LAST_UPDATE_LOGIN ,
1240 REQUEST_ID ,
1241 PROGRAM_APPLICATION_ID ,
1242 PROGRAM_ID ,
1243 PROGRAM_UPDATE_DATE ,
1244 INVENTORY_ITEM_ID ,
1245 REVISION ,
1246 ORGANIZATION_ID ,
1247 SUBINVENTORY_CODE ,
1248 LOCATOR_ID ,
1249 TRANSACTION_QUANTITY ,
1250 PRIMARY_QUANTITY ,
1251 TRANSACTION_UOM ,
1252 TRANSACTION_COST ,
1253 TRANSACTION_TYPE_ID ,
1254 TRANSACTION_ACTION_ID ,
1255 TRANSACTION_SOURCE_TYPE_ID ,
1256 TRANSACTION_SOURCE_ID ,
1257 TRANSACTION_SOURCE_NAME ,
1258 TRANSACTION_DATE ,
1259 ACCT_PERIOD_ID ,
1260 DISTRIBUTION_ACCOUNT_ID ,
1261 TRANSACTION_REFERENCE ,
1262 REQUISITION_LINE_ID ,
1263 REQUISITION_DISTRIBUTION_ID ,
1264 REASON_ID ,
1265 LOT_NUMBER ,
1266 LOT_EXPIRATION_DATE ,
1267 SERIAL_NUMBER ,
1268 RECEIVING_DOCUMENT ,
1269 DEMAND_ID ,
1270 RCV_TRANSACTION_ID ,
1271 MOVE_TRANSACTION_ID ,
1272 COMPLETION_TRANSACTION_ID ,
1273 WIP_ENTITY_TYPE ,
1274 SCHEDULE_ID ,
1275 REPETITIVE_LINE_ID ,
1276 EMPLOYEE_CODE ,
1277 PRIMARY_SWITCH ,
1278 SCHEDULE_UPDATE_CODE ,
1279 SETUP_TEARDOWN_CODE ,
1280 ITEM_ORDERING ,
1281 NEGATIVE_REQ_FLAG ,
1282 OPERATION_SEQ_NUM ,
1283 PICKING_LINE_ID ,
1284 TRX_SOURCE_LINE_ID ,
1285 TRX_SOURCE_DELIVERY_ID ,
1286 PHYSICAL_ADJUSTMENT_ID ,
1287 CYCLE_COUNT_ID ,
1288 RMA_LINE_ID ,
1289 CUSTOMER_SHIP_ID ,
1290 CURRENCY_CODE ,
1291 CURRENCY_CONVERSION_RATE ,
1292 CURRENCY_CONVERSION_TYPE ,
1293 CURRENCY_CONVERSION_DATE ,
1294 USSGL_TRANSACTION_CODE ,
1295 VENDOR_LOT_NUMBER ,
1296 ENCUMBRANCE_ACCOUNT ,
1297 ENCUMBRANCE_AMOUNT ,
1298 SHIP_TO_LOCATION ,
1299 SHIPMENT_NUMBER ,
1300 TRANSFER_COST ,
1301 TRANSPORTATION_COST ,
1302 TRANSPORTATION_ACCOUNT ,
1303 FREIGHT_CODE ,
1304 CONTAINERS ,
1305 WAYBILL_AIRBILL ,
1306 EXPECTED_ARRIVAL_DATE ,
1307 TRANSFER_SUBINVENTORY ,
1308 TRANSFER_ORGANIZATION ,
1309 TRANSFER_TO_LOCATION ,
1310 NEW_AVERAGE_COST ,
1311 VALUE_CHANGE ,
1312 PERCENTAGE_CHANGE ,
1313 MATERIAL_ALLOCATION_TEMP_ID ,
1314 DEMAND_SOURCE_HEADER_ID ,
1315 DEMAND_SOURCE_LINE ,
1316 DEMAND_SOURCE_DELIVERY ,
1317 ITEM_SEGMENTS ,
1318 ITEM_DESCRIPTION ,
1319 ITEM_TRX_ENABLED_FLAG ,
1320 ITEM_LOCATION_CONTROL_CODE ,
1321 ITEM_RESTRICT_SUBINV_CODE ,
1322 ITEM_RESTRICT_LOCATORS_CODE ,
1323 ITEM_REVISION_QTY_CONTROL_CODE ,
1324 ITEM_PRIMARY_UOM_CODE ,
1325 ITEM_UOM_CLASS ,
1326 ITEM_SHELF_LIFE_CODE ,
1327 ITEM_SHELF_LIFE_DAYS ,
1328 ITEM_LOT_CONTROL_CODE ,
1329 ITEM_SERIAL_CONTROL_CODE ,
1330 ITEM_INVENTORY_ASSET_FLAG ,
1331 ALLOWED_UNITS_LOOKUP_CODE ,
1332 DEPARTMENT_ID ,
1333 DEPARTMENT_CODE ,
1334 WIP_SUPPLY_TYPE ,
1335 SUPPLY_SUBINVENTORY ,
1336 SUPPLY_LOCATOR_ID ,
1337 VALID_SUBINVENTORY_FLAG ,
1338 VALID_LOCATOR_FLAG ,
1339 LOCATOR_SEGMENTS ,
1340 CURRENT_LOCATOR_CONTROL_CODE ,
1341 NUMBER_OF_LOTS_ENTERED ,
1342 WIP_COMMIT_FLAG ,
1343 NEXT_LOT_NUMBER ,
1344 LOT_ALPHA_PREFIX ,
1345 NEXT_SERIAL_NUMBER ,
1346 SERIAL_ALPHA_PREFIX ,
1347 SHIPPABLE_FLAG ,
1348 POSTING_FLAG ,
1349 REQUIRED_FLAG ,
1350 PROCESS_FLAG ,
1351 ERROR_CODE ,
1352 ERROR_EXPLANATION ,
1353 ATTRIBUTE_CATEGORY ,
1354 ATTRIBUTE1 ,
1355 ATTRIBUTE2 ,
1356 ATTRIBUTE3 ,
1357 ATTRIBUTE4 ,
1358 ATTRIBUTE5 ,
1359 ATTRIBUTE6 ,
1360 ATTRIBUTE7 ,
1361 ATTRIBUTE8 ,
1362 ATTRIBUTE9 ,
1363 ATTRIBUTE10 ,
1364 ATTRIBUTE11 ,
1365 ATTRIBUTE12 ,
1366 ATTRIBUTE13 ,
1367 ATTRIBUTE14 ,
1368 ATTRIBUTE15 ,
1369 MOVEMENT_ID ,
1370 RESERVATION_QUANTITY ,
1371 SHIPPED_QUANTITY ,
1372 TRANSACTION_LINE_NUMBER ,
1373 TASK_ID ,
1374 TO_TASK_ID ,
1375 SOURCE_TASK_ID ,
1376 PROJECT_ID ,
1377 SOURCE_PROJECT_ID ,
1378 PA_EXPENDITURE_ORG_ID ,
1379 TO_PROJECT_ID ,
1380 EXPENDITURE_TYPE ,
1381 FINAL_COMPLETION_FLAG ,
1382 TRANSFER_PERCENTAGE ,
1383 TRANSACTION_SEQUENCE_ID ,
1384 MATERIAL_ACCOUNT ,
1385 MATERIAL_OVERHEAD_ACCOUNT ,
1386 RESOURCE_ACCOUNT ,
1387 OUTSIDE_PROCESSING_ACCOUNT ,
1388 OVERHEAD_ACCOUNT ,
1389 FLOW_SCHEDULE ,
1390 COST_GROUP_ID ,
1391 DEMAND_CLASS ,
1392 QA_COLLECTION_ID ,
1393 KANBAN_CARD_ID ,
1394 OVERCOMPLETION_TRANSACTION_QTY ,
1395 OVERCOMPLETION_PRIMARY_QTY ,
1396 OVERCOMPLETION_TRANSACTION_ID ,
1397 END_ITEM_UNIT_NUMBER ,
1398 SCHEDULED_PAYBACK_DATE ,
1399 LINE_TYPE_CODE ,
1400 PARENT_TRANSACTION_TEMP_ID ,
1401 PUT_AWAY_STRATEGY_ID ,
1402 PUT_AWAY_RULE_ID ,
1403 PICK_STRATEGY_ID ,
1404 PICK_RULE_ID ,
1405 MOVE_ORDER_LINE_ID ,
1406 TASK_GROUP_ID ,
1407 PICK_SLIP_NUMBER ,
1408 RESERVATION_ID ,
1409 COMMON_BOM_SEQ_ID ,
1410 COMMON_ROUTING_SEQ_ID ,
1411 ORG_COST_GROUP_ID ,
1412 COST_TYPE_ID ,
1413 TRANSACTION_STATUS ,
1414 STANDARD_OPERATION_ID ,
1415 TASK_PRIORITY ,
1416 WMS_TASK_TYPE ,
1417 PARENT_LINE_ID ,
1418 SOURCE_LOT_NUMBER ,
1419 TRANSFER_COST_GROUP_ID ,
1420 LPN_ID ,
1421 TRANSFER_LPN_ID ,
1422 WMS_TASK_STATUS ,
1423 CONTENT_LPN_ID ,
1424 CONTAINER_ITEM_ID ,
1425 CARTONIZATION_ID ,
1426 PICK_SLIP_DATE ,
1427 REBUILD_ITEM_ID ,
1428 REBUILD_SERIAL_NUMBER ,
1429 REBUILD_ACTIVITY_ID ,
1430 REBUILD_JOB_NAME ,
1431 ORGANIZATION_TYPE ,
1432 TRANSFER_ORGANIZATION_TYPE ,
1433 OWNING_ORGANIZATION_ID ,
1434 OWNING_TP_TYPE ,
1435 XFR_OWNING_ORGANIZATION_ID ,
1436 TRANSFER_OWNING_TP_TYPE ,
1437 PLANNING_ORGANIZATION_ID ,
1438 PLANNING_TP_TYPE ,
1439 XFR_PLANNING_ORGANIZATION_ID ,
1440 TRANSFER_PLANNING_TP_TYPE ,
1441 SECONDARY_UOM_CODE , -- new
1442 SECONDARY_TRANSACTION_QUANTITY , -- new
1443 ALLOCATED_LPN_ID ,
1444 SCHEDULE_NUMBER ,
1445 SCHEDULED_FLAG ,
1446 CLASS_CODE ,
1447 SCHEDULE_GROUP ,
1448 BUILD_SEQUENCE ,
1449 BOM_REVISION ,
1450 ROUTING_REVISION ,
1451 BOM_REVISION_DATE ,
1452 ROUTING_REVISION_DATE ,
1453 ALTERNATE_BOM_DESIGNATOR ,
1454 ALTERNATE_ROUTING_DESIGNATOR
1455 )
1456 select
1457 TRANSACTION_HEADER_ID ,
1458 TRANSACTION_TEMP_ID ,
1459 SOURCE_CODE ,
1460 SOURCE_LINE_ID ,
1461 TRANSACTION_MODE ,
1462 LOCK_FLAG ,
1463 LAST_UPDATE_DATE ,
1464 LAST_UPDATED_BY ,
1465 CREATION_DATE ,
1466 CREATED_BY ,
1467 LAST_UPDATE_LOGIN ,
1468 REQUEST_ID ,
1469 PROGRAM_APPLICATION_ID ,
1470 PROGRAM_ID ,
1471 PROGRAM_UPDATE_DATE ,
1472 INVENTORY_ITEM_ID ,
1473 REVISION ,
1474 ORGANIZATION_ID ,
1475 SUBINVENTORY_CODE ,
1476 LOCATOR_ID ,
1477 TRANSACTION_QUANTITY ,
1478 PRIMARY_QUANTITY ,
1479 TRANSACTION_UOM ,
1480 TRANSACTION_COST ,
1481 TRANSACTION_TYPE_ID ,
1482 TRANSACTION_ACTION_ID ,
1483 TRANSACTION_SOURCE_TYPE_ID ,
1484 TRANSACTION_SOURCE_ID ,
1485 TRANSACTION_SOURCE_NAME ,
1486 TRANSACTION_DATE ,
1487 ACCT_PERIOD_ID ,
1488 DISTRIBUTION_ACCOUNT_ID ,
1489 TRANSACTION_REFERENCE ,
1490 REQUISITION_LINE_ID ,
1491 REQUISITION_DISTRIBUTION_ID ,
1492 REASON_ID ,
1493 LOT_NUMBER ,
1494 LOT_EXPIRATION_DATE ,
1495 SERIAL_NUMBER ,
1496 RECEIVING_DOCUMENT ,
1497 DEMAND_ID ,
1498 RCV_TRANSACTION_ID ,
1499 MOVE_TRANSACTION_ID ,
1500 COMPLETION_TRANSACTION_ID ,
1501 WIP_ENTITY_TYPE ,
1502 SCHEDULE_ID ,
1503 REPETITIVE_LINE_ID ,
1504 EMPLOYEE_CODE ,
1505 PRIMARY_SWITCH ,
1506 SCHEDULE_UPDATE_CODE ,
1507 SETUP_TEARDOWN_CODE ,
1508 ITEM_ORDERING ,
1509 NEGATIVE_REQ_FLAG ,
1510 OPERATION_SEQ_NUM ,
1511 PICKING_LINE_ID ,
1512 TRX_SOURCE_LINE_ID ,
1513 TRX_SOURCE_DELIVERY_ID ,
1514 PHYSICAL_ADJUSTMENT_ID ,
1515 CYCLE_COUNT_ID ,
1516 RMA_LINE_ID ,
1517 CUSTOMER_SHIP_ID ,
1518 CURRENCY_CODE ,
1519 CURRENCY_CONVERSION_RATE ,
1520 CURRENCY_CONVERSION_TYPE ,
1521 CURRENCY_CONVERSION_DATE ,
1522 USSGL_TRANSACTION_CODE ,
1523 VENDOR_LOT_NUMBER ,
1524 ENCUMBRANCE_ACCOUNT ,
1525 ENCUMBRANCE_AMOUNT ,
1526 SHIP_TO_LOCATION ,
1527 SHIPMENT_NUMBER ,
1528 TRANSFER_COST ,
1529 TRANSPORTATION_COST ,
1530 TRANSPORTATION_ACCOUNT ,
1531 FREIGHT_CODE ,
1532 CONTAINERS ,
1533 WAYBILL_AIRBILL ,
1534 EXPECTED_ARRIVAL_DATE ,
1535 TRANSFER_SUBINVENTORY ,
1536 TRANSFER_ORGANIZATION ,
1537 TRANSFER_TO_LOCATION ,
1538 NEW_AVERAGE_COST ,
1539 VALUE_CHANGE ,
1540 PERCENTAGE_CHANGE ,
1541 MATERIAL_ALLOCATION_TEMP_ID ,
1542 DEMAND_SOURCE_HEADER_ID ,
1543 DEMAND_SOURCE_LINE ,
1544 DEMAND_SOURCE_DELIVERY ,
1545 ITEM_SEGMENTS ,
1546 ITEM_DESCRIPTION ,
1547 ITEM_TRX_ENABLED_FLAG ,
1548 ITEM_LOCATION_CONTROL_CODE ,
1549 ITEM_RESTRICT_SUBINV_CODE ,
1550 ITEM_RESTRICT_LOCATORS_CODE ,
1551 ITEM_REVISION_QTY_CONTROL_CODE ,
1552 ITEM_PRIMARY_UOM_CODE ,
1553 ITEM_UOM_CLASS ,
1554 ITEM_SHELF_LIFE_CODE ,
1555 ITEM_SHELF_LIFE_DAYS ,
1556 ITEM_LOT_CONTROL_CODE ,
1557 ITEM_SERIAL_CONTROL_CODE ,
1558 ITEM_INVENTORY_ASSET_FLAG ,
1559 ALLOWED_UNITS_LOOKUP_CODE ,
1560 DEPARTMENT_ID ,
1561 DEPARTMENT_CODE ,
1562 WIP_SUPPLY_TYPE ,
1563 SUPPLY_SUBINVENTORY ,
1564 SUPPLY_LOCATOR_ID ,
1565 VALID_SUBINVENTORY_FLAG ,
1566 VALID_LOCATOR_FLAG ,
1567 LOCATOR_SEGMENTS ,
1568 CURRENT_LOCATOR_CONTROL_CODE ,
1569 NUMBER_OF_LOTS_ENTERED ,
1570 WIP_COMMIT_FLAG ,
1571 NEXT_LOT_NUMBER ,
1572 LOT_ALPHA_PREFIX ,
1573 NEXT_SERIAL_NUMBER ,
1574 SERIAL_ALPHA_PREFIX ,
1575 SHIPPABLE_FLAG ,
1576 POSTING_FLAG ,
1577 REQUIRED_FLAG ,
1578 PROCESS_FLAG ,
1579 ERROR_CODE ,
1580 ERROR_EXPLANATION ,
1581 ATTRIBUTE_CATEGORY ,
1582 ATTRIBUTE1 ,
1583 ATTRIBUTE2 ,
1584 ATTRIBUTE3 ,
1585 ATTRIBUTE4 ,
1586 ATTRIBUTE5 ,
1587 ATTRIBUTE6 ,
1588 ATTRIBUTE7 ,
1589 ATTRIBUTE8 ,
1590 ATTRIBUTE9 ,
1591 ATTRIBUTE10 ,
1592 ATTRIBUTE11 ,
1593 ATTRIBUTE12 ,
1594 ATTRIBUTE13 ,
1595 ATTRIBUTE14 ,
1596 ATTRIBUTE15 ,
1597 MOVEMENT_ID ,
1598 RESERVATION_QUANTITY ,
1599 SHIPPED_QUANTITY ,
1600 TRANSACTION_LINE_NUMBER ,
1601 TASK_ID ,
1602 TO_TASK_ID ,
1603 SOURCE_TASK_ID ,
1604 PROJECT_ID ,
1605 SOURCE_PROJECT_ID ,
1606 PA_EXPENDITURE_ORG_ID ,
1607 TO_PROJECT_ID ,
1608 EXPENDITURE_TYPE ,
1609 FINAL_COMPLETION_FLAG ,
1610 TRANSFER_PERCENTAGE ,
1611 TRANSACTION_SEQUENCE_ID ,
1612 MATERIAL_ACCOUNT ,
1613 MATERIAL_OVERHEAD_ACCOUNT ,
1614 RESOURCE_ACCOUNT ,
1615 OUTSIDE_PROCESSING_ACCOUNT ,
1616 OVERHEAD_ACCOUNT ,
1617 FLOW_SCHEDULE ,
1618 COST_GROUP_ID ,
1619 DEMAND_CLASS ,
1620 QA_COLLECTION_ID ,
1621 KANBAN_CARD_ID ,
1622 OVERCOMPLETION_TRANSACTION_QTY ,
1623 OVERCOMPLETION_PRIMARY_QTY ,
1624 OVERCOMPLETION_TRANSACTION_ID ,
1625 END_ITEM_UNIT_NUMBER ,
1626 SCHEDULED_PAYBACK_DATE ,
1627 LINE_TYPE_CODE ,
1628 PARENT_TRANSACTION_TEMP_ID ,
1629 PUT_AWAY_STRATEGY_ID ,
1630 PUT_AWAY_RULE_ID ,
1631 PICK_STRATEGY_ID ,
1632 PICK_RULE_ID ,
1633 MOVE_ORDER_LINE_ID ,
1634 TASK_GROUP_ID ,
1635 PICK_SLIP_NUMBER ,
1636 RESERVATION_ID ,
1637 COMMON_BOM_SEQ_ID ,
1638 COMMON_ROUTING_SEQ_ID ,
1639 ORG_COST_GROUP_ID ,
1640 COST_TYPE_ID ,
1641 TRANSACTION_STATUS ,
1642 STANDARD_OPERATION_ID ,
1643 TASK_PRIORITY ,
1644 WMS_TASK_TYPE ,
1645 PARENT_LINE_ID ,
1646 ' ' ,
1647 TRANSFER_COST_GROUP_ID ,
1648 LPN_ID ,
1649 TRANSFER_LPN_ID ,
1650 WMS_TASK_STATUS ,
1651 CONTENT_LPN_ID ,
1652 CONTAINER_ITEM_ID ,
1653 CARTONIZATION_ID ,
1654 PICK_SLIP_DATE ,
1655 REBUILD_ITEM_ID ,
1656 REBUILD_SERIAL_NUMBER ,
1657 REBUILD_ACTIVITY_ID ,
1658 REBUILD_JOB_NAME ,
1659 ORGANIZATION_TYPE ,
1660 TRANSFER_ORGANIZATION_TYPE ,
1661 OWNING_ORGANIZATION_ID ,
1662 OWNING_TP_TYPE ,
1663 XFR_OWNING_ORGANIZATION_ID ,
1664 TRANSFER_OWNING_TP_TYPE ,
1665 PLANNING_ORGANIZATION_ID ,
1666 PLANNING_TP_TYPE ,
1667 XFR_PLANNING_ORGANIZATION_ID ,
1668 TRANSFER_PLANNING_TP_TYPE ,
1669 SECONDARY_UOM_CODE , -- new
1670 SECONDARY_TRANSACTION_QUANTITY , -- new
1671 ALLOCATED_LPN_ID ,
1672 SCHEDULE_NUMBER ,
1673 SCHEDULED_FLAG ,
1674 CLASS_CODE ,
1675 SCHEDULE_GROUP ,
1676 BUILD_SEQUENCE ,
1677 BOM_REVISION ,
1678 ROUTING_REVISION ,
1679 BOM_REVISION_DATE ,
1680 ROUTING_REVISION_DATE ,
1681 ALTERNATE_BOM_DESIGNATOR ,
1682 ALTERNATE_ROUTING_DESIGNATOR
1683 from mtl_material_transactions_temp
1684 where transaction_header_id = l_txn_header_id;
1685 --- If lot controlled item, insert records into lot_trace table
1686
1687 If (l_insert_lot_flag = 1) then
1688 insert into wms_transaction_lots_trace
1689 (
1690 TRANSACTION_TEMP_ID,
1691 LAST_UPDATE_DATE,
1692 LAST_UPDATED_BY,
1693 CREATION_DATE,
1694 CREATED_BY,
1695 LAST_UPDATE_LOGIN ,
1696 REQUEST_ID ,
1697 PROGRAM_APPLICATION_ID ,
1698 PROGRAM_ID ,
1699 PROGRAM_UPDATE_DATE ,
1700 TRANSACTION_QUANTITY,
1701 PRIMARY_QUANTITY,
1702 SECONDARY_QUANTITY, -- new
1703 GRADE_CODE, -- new
1704 LOT_NUMBER ,
1705 LOT_EXPIRATION_DATE ,
1706 ERROR_CODE ,
1707 SERIAL_TRANSACTION_TEMP_ID ,
1708 GROUP_HEADER_ID ,
1709 PUT_AWAY_RULE_ID ,
1710 PICK_RULE_ID ,
1711 DESCRIPTION ,
1712 VENDOR_NAME ,
1713 SUPPLIER_LOT_NUMBER,
1714 ORIGINATION_DATE ,
1715 DATE_CODE ,
1716 CHANGE_DATE ,
1717 MATURITY_DATE ,
1718 STATUS_ID ,
1719 RETEST_DATE ,
1720 AGE ,
1721 ITEM_SIZE ,
1722 COLOR ,
1723 VOLUME ,
1724 VOLUME_UOM ,
1725 PLACE_OF_ORIGIN ,
1726 BEST_BY_DATE ,
1727 LENGTH ,
1728 LENGTH_UOM ,
1729 RECYCLED_CONTENT ,
1730 THICKNESS ,
1731 THICKNESS_UOM ,
1732 WIDTH ,
1733 WIDTH_UOM ,
1734 CURL_WRINKLE_FOLD ,
1735 LOT_ATTRIBUTE_CATEGORY,
1736 C_ATTRIBUTE1 ,
1737 C_ATTRIBUTE2 ,
1738 C_ATTRIBUTE3 ,
1739 C_ATTRIBUTE4 ,
1740 C_ATTRIBUTE5 ,
1741 C_ATTRIBUTE6 ,
1742 C_ATTRIBUTE7 ,
1743 C_ATTRIBUTE8 ,
1744 C_ATTRIBUTE9 ,
1745 C_ATTRIBUTE10 ,
1746 C_ATTRIBUTE11 ,
1747 C_ATTRIBUTE12 ,
1748 C_ATTRIBUTE13 ,
1749 C_ATTRIBUTE14 ,
1750 C_ATTRIBUTE15 ,
1751 C_ATTRIBUTE16 ,
1752 C_ATTRIBUTE17 ,
1753 C_ATTRIBUTE18 ,
1754 C_ATTRIBUTE19 ,
1755 C_ATTRIBUTE20 ,
1756 D_ATTRIBUTE1 ,
1757 D_ATTRIBUTE2 ,
1758 D_ATTRIBUTE3 ,
1759 D_ATTRIBUTE4 ,
1760 D_ATTRIBUTE5 ,
1761 D_ATTRIBUTE6 ,
1762 D_ATTRIBUTE7 ,
1763 D_ATTRIBUTE8 ,
1764 D_ATTRIBUTE9 ,
1765 D_ATTRIBUTE10 ,
1766 N_ATTRIBUTE1 ,
1767 N_ATTRIBUTE2 ,
1768 N_ATTRIBUTE3 ,
1769 N_ATTRIBUTE4 ,
1770 N_ATTRIBUTE5 ,
1771 N_ATTRIBUTE6 ,
1772 N_ATTRIBUTE7 ,
1773 N_ATTRIBUTE8 ,
1774 N_ATTRIBUTE9 ,
1775 N_ATTRIBUTE10 ,
1776 VENDOR_ID ,
1777 TERRITORY_CODE ,
1778 SUBLOT_NUM ,
1779 SECONDARY_UNIT_OF_MEASURE ,
1780 QC_GRADE )
1781 select
1782 TRANSACTION_TEMP_ID,
1783 LAST_UPDATE_DATE,
1784 LAST_UPDATED_BY,
1785 CREATION_DATE,
1786 CREATED_BY,
1787 LAST_UPDATE_LOGIN ,
1788 REQUEST_ID ,
1789 PROGRAM_APPLICATION_ID ,
1790 PROGRAM_ID ,
1791 PROGRAM_UPDATE_DATE ,
1792 TRANSACTION_QUANTITY,
1793 PRIMARY_QUANTITY,
1794 SECONDARY_QUANTITY, -- new
1795 GRADE_CODE, -- new
1796 LOT_NUMBER ,
1797 LOT_EXPIRATION_DATE ,
1798 ERROR_CODE ,
1799 SERIAL_TRANSACTION_TEMP_ID ,
1800 GROUP_HEADER_ID ,
1801 PUT_AWAY_RULE_ID ,
1802 PICK_RULE_ID ,
1803 DESCRIPTION ,
1804 VENDOR_NAME ,
1805 SUPPLIER_LOT_NUMBER,
1806 ORIGINATION_DATE ,
1807 DATE_CODE ,
1808 CHANGE_DATE ,
1809 MATURITY_DATE ,
1810 STATUS_ID ,
1811 RETEST_DATE ,
1812 AGE ,
1813 ITEM_SIZE ,
1814 COLOR ,
1815 VOLUME ,
1816 VOLUME_UOM ,
1817 PLACE_OF_ORIGIN ,
1818 BEST_BY_DATE ,
1819 LENGTH ,
1820 LENGTH_UOM ,
1821 RECYCLED_CONTENT ,
1822 THICKNESS ,
1823 THICKNESS_UOM ,
1824 WIDTH ,
1825 WIDTH_UOM ,
1826 CURL_WRINKLE_FOLD ,
1827 LOT_ATTRIBUTE_CATEGORY,
1828 C_ATTRIBUTE1 ,
1829 C_ATTRIBUTE2 ,
1830 C_ATTRIBUTE3 ,
1831 C_ATTRIBUTE4 ,
1832 C_ATTRIBUTE5 ,
1833 C_ATTRIBUTE6 ,
1834 C_ATTRIBUTE7 ,
1835 C_ATTRIBUTE8 ,
1836 C_ATTRIBUTE9 ,
1837 C_ATTRIBUTE10 ,
1838 C_ATTRIBUTE11 ,
1839 C_ATTRIBUTE12 ,
1840 C_ATTRIBUTE13 ,
1841 C_ATTRIBUTE14 ,
1842 C_ATTRIBUTE15 ,
1843 C_ATTRIBUTE16 ,
1844 C_ATTRIBUTE17 ,
1845 C_ATTRIBUTE18 ,
1846 C_ATTRIBUTE19 ,
1847 C_ATTRIBUTE20 ,
1848 D_ATTRIBUTE1 ,
1849 D_ATTRIBUTE2 ,
1850 D_ATTRIBUTE3 ,
1851 D_ATTRIBUTE4 ,
1852 D_ATTRIBUTE5 ,
1853 D_ATTRIBUTE6 ,
1854 D_ATTRIBUTE7 ,
1855 D_ATTRIBUTE8 ,
1856 D_ATTRIBUTE9 ,
1857 D_ATTRIBUTE10 ,
1858 N_ATTRIBUTE1 ,
1859 N_ATTRIBUTE2 ,
1860 N_ATTRIBUTE3 ,
1861 N_ATTRIBUTE4 ,
1862 N_ATTRIBUTE5 ,
1863 N_ATTRIBUTE6 ,
1864 N_ATTRIBUTE7 ,
1865 N_ATTRIBUTE8 ,
1866 N_ATTRIBUTE9 ,
1867 N_ATTRIBUTE10 ,
1868 VENDOR_ID ,
1869 TERRITORY_CODE ,
1870 SUBLOT_NUM ,
1871 SECONDARY_UNIT_OF_MEASURE ,
1872 QC_GRADE
1873 from mtl_transaction_lots_temp
1874 where transaction_temp_id in ( select transaction_temp_id
1875 from wms_material_txn_trace
1876 where transaction_header_id = l_txn_header_id);
1877
1878
1879 End if;
1880
1881 --- If only serial controlled item , insert records into serial_trace table
1882
1883 If (l_insert_serial_flag = 1 and l_insert_lot_flag = 0) then
1884 insert into wms_serial_numbers_trace
1885 (
1886 TRANSACTION_TEMP_ID ,
1887 LAST_UPDATE_DATE ,
1888 LAST_UPDATED_BY ,
1889 CREATION_DATE ,
1890 CREATED_BY ,
1891 LAST_UPDATE_LOGIN ,
1892 REQUEST_ID ,
1893 PROGRAM_APPLICATION_ID ,
1894 PROGRAM_ID ,
1895 PROGRAM_UPDATE_DATE ,
1896 VENDOR_SERIAL_NUMBER ,
1897 VENDOR_LOT_NUMBER ,
1898 FM_SERIAL_NUMBER ,
1899 TO_SERIAL_NUMBER ,
1900 SERIAL_PREFIX ,
1901 ERROR_CODE ,
1902 GROUP_HEADER_ID ,
1903 PARENT_SERIAL_NUMBER ,
1904 END_ITEM_UNIT_NUMBER ,
1905 SERIAL_ATTRIBUTE_CATEGORY ,
1906 ORIGINATION_DATE ,
1907 C_ATTRIBUTE1 ,
1908 C_ATTRIBUTE2 ,
1909 C_ATTRIBUTE3 ,
1910 C_ATTRIBUTE4 ,
1911 C_ATTRIBUTE5 ,
1912 C_ATTRIBUTE6 ,
1913 C_ATTRIBUTE7 ,
1914 C_ATTRIBUTE8 ,
1915 C_ATTRIBUTE9 ,
1916 C_ATTRIBUTE10 ,
1917 C_ATTRIBUTE11 ,
1918 C_ATTRIBUTE12 ,
1919 C_ATTRIBUTE13 ,
1920 C_ATTRIBUTE14 ,
1921 C_ATTRIBUTE15 ,
1922 C_ATTRIBUTE16 ,
1923 C_ATTRIBUTE17 ,
1924 C_ATTRIBUTE18 ,
1925 C_ATTRIBUTE19 ,
1926 C_ATTRIBUTE20 ,
1927 D_ATTRIBUTE1 ,
1928 D_ATTRIBUTE2 ,
1929 D_ATTRIBUTE3 ,
1930 D_ATTRIBUTE4 ,
1931 D_ATTRIBUTE5 ,
1932 D_ATTRIBUTE6 ,
1933 D_ATTRIBUTE7 ,
1934 D_ATTRIBUTE8 ,
1935 D_ATTRIBUTE9 ,
1936 D_ATTRIBUTE10 ,
1937 N_ATTRIBUTE1 ,
1938 N_ATTRIBUTE2 ,
1939 N_ATTRIBUTE3 ,
1940 N_ATTRIBUTE4 ,
1941 N_ATTRIBUTE5 ,
1942 N_ATTRIBUTE6 ,
1943 N_ATTRIBUTE7 ,
1944 N_ATTRIBUTE8 ,
1945 N_ATTRIBUTE9 ,
1946 N_ATTRIBUTE10 ,
1947 STATUS_ID ,
1948 TERRITORY_CODE ,
1949 TIME_SINCE_NEW ,
1950 CYCLES_SINCE_NEW ,
1951 TIME_SINCE_OVERHAUL,
1952 CYCLES_SINCE_OVERHAUL,
1953 TIME_SINCE_REPAIR ,
1954 CYCLES_SINCE_REPAIR ,
1955 TIME_SINCE_VISIT ,
1956 CYCLES_SINCE_VISIT ,
1957 TIME_SINCE_MARK ,
1958 CYCLES_SINCE_MARK ,
1959 NUMBER_OF_REPAIRS
1960 )
1961 select
1962 TRANSACTION_TEMP_ID ,
1963 LAST_UPDATE_DATE ,
1964 LAST_UPDATED_BY ,
1965 CREATION_DATE ,
1966 CREATED_BY ,
1967 LAST_UPDATE_LOGIN ,
1968 REQUEST_ID ,
1969 PROGRAM_APPLICATION_ID ,
1970 PROGRAM_ID ,
1971 PROGRAM_UPDATE_DATE ,
1972 VENDOR_SERIAL_NUMBER ,
1973 VENDOR_LOT_NUMBER ,
1974 FM_SERIAL_NUMBER ,
1975 TO_SERIAL_NUMBER ,
1976 SERIAL_PREFIX ,
1977 ERROR_CODE ,
1978 GROUP_HEADER_ID ,
1979 PARENT_SERIAL_NUMBER ,
1980 END_ITEM_UNIT_NUMBER ,
1981 SERIAL_ATTRIBUTE_CATEGORY ,
1982 ORIGINATION_DATE ,
1983 C_ATTRIBUTE1 ,
1984 C_ATTRIBUTE2 ,
1985 C_ATTRIBUTE3 ,
1986 C_ATTRIBUTE4 ,
1987 C_ATTRIBUTE5 ,
1988 C_ATTRIBUTE6 ,
1989 C_ATTRIBUTE7 ,
1990 C_ATTRIBUTE8 ,
1991 C_ATTRIBUTE9 ,
1992 C_ATTRIBUTE10 ,
1993 C_ATTRIBUTE11 ,
1994 C_ATTRIBUTE12 ,
1995 C_ATTRIBUTE13 ,
1996 C_ATTRIBUTE14 ,
1997 C_ATTRIBUTE15 ,
1998 C_ATTRIBUTE16 ,
1999 C_ATTRIBUTE17 ,
2000 C_ATTRIBUTE18 ,
2001 C_ATTRIBUTE19 ,
2002 C_ATTRIBUTE20 ,
2003 D_ATTRIBUTE1 ,
2004 D_ATTRIBUTE2 ,
2005 D_ATTRIBUTE3 ,
2006 D_ATTRIBUTE4 ,
2007 D_ATTRIBUTE5 ,
2008 D_ATTRIBUTE6 ,
2009 D_ATTRIBUTE7 ,
2010 D_ATTRIBUTE8 ,
2011 D_ATTRIBUTE9 ,
2012 D_ATTRIBUTE10 ,
2013 N_ATTRIBUTE1 ,
2014 N_ATTRIBUTE2 ,
2015 N_ATTRIBUTE3 ,
2016 N_ATTRIBUTE4 ,
2017 N_ATTRIBUTE5 ,
2018 N_ATTRIBUTE6 ,
2019 N_ATTRIBUTE7 ,
2020 N_ATTRIBUTE8 ,
2021 N_ATTRIBUTE9 ,
2022 N_ATTRIBUTE10 ,
2023 STATUS_ID ,
2024 TERRITORY_CODE ,
2025 TIME_SINCE_NEW ,
2026 CYCLES_SINCE_NEW ,
2027 TIME_SINCE_OVERHAUL,
2028 CYCLES_SINCE_OVERHAUL,
2029 TIME_SINCE_REPAIR ,
2030 CYCLES_SINCE_REPAIR ,
2031 TIME_SINCE_VISIT ,
2032 CYCLES_SINCE_VISIT ,
2033 TIME_SINCE_MARK ,
2034 CYCLES_SINCE_MARK ,
2035 NUMBER_OF_REPAIRS
2036 from mtl_serial_numbers_temp
2037 where transaction_temp_id in ( select transaction_temp_id
2038 from wms_material_txn_trace
2039 where transaction_header_id = l_txn_header_id);
2040
2041
2042 End if;
2043
2044 --- If lot and serial controlled item , insert records into serial_trace table
2045
2046 If (l_insert_serial_flag = 1 and l_insert_lot_flag = 1) then
2047 insert into wms_serial_numbers_trace
2048 (
2049 TRANSACTION_TEMP_ID ,
2050 LAST_UPDATE_DATE ,
2051 LAST_UPDATED_BY ,
2052 CREATION_DATE ,
2053 CREATED_BY ,
2054 LAST_UPDATE_LOGIN ,
2055 REQUEST_ID ,
2056 PROGRAM_APPLICATION_ID ,
2057 PROGRAM_ID ,
2058 PROGRAM_UPDATE_DATE ,
2059 VENDOR_SERIAL_NUMBER ,
2060 VENDOR_LOT_NUMBER ,
2061 FM_SERIAL_NUMBER ,
2062 TO_SERIAL_NUMBER ,
2063 SERIAL_PREFIX ,
2064 ERROR_CODE ,
2065 GROUP_HEADER_ID ,
2066 PARENT_SERIAL_NUMBER ,
2067 END_ITEM_UNIT_NUMBER ,
2068 SERIAL_ATTRIBUTE_CATEGORY ,
2069 ORIGINATION_DATE ,
2070 C_ATTRIBUTE1 ,
2071 C_ATTRIBUTE2 ,
2072 C_ATTRIBUTE3 ,
2073 C_ATTRIBUTE4 ,
2074 C_ATTRIBUTE5 ,
2075 C_ATTRIBUTE6 ,
2076 C_ATTRIBUTE7 ,
2077 C_ATTRIBUTE8 ,
2078 C_ATTRIBUTE9 ,
2079 C_ATTRIBUTE10 ,
2080 C_ATTRIBUTE11 ,
2081 C_ATTRIBUTE12 ,
2082 C_ATTRIBUTE13 ,
2083 C_ATTRIBUTE14 ,
2084 C_ATTRIBUTE15 ,
2085 C_ATTRIBUTE16 ,
2086 C_ATTRIBUTE17 ,
2087 C_ATTRIBUTE18 ,
2088 C_ATTRIBUTE19 ,
2089 C_ATTRIBUTE20 ,
2090 D_ATTRIBUTE1 ,
2091 D_ATTRIBUTE2 ,
2092 D_ATTRIBUTE3 ,
2093 D_ATTRIBUTE4 ,
2094 D_ATTRIBUTE5 ,
2095 D_ATTRIBUTE6 ,
2096 D_ATTRIBUTE7 ,
2097 D_ATTRIBUTE8 ,
2098 D_ATTRIBUTE9 ,
2099 D_ATTRIBUTE10 ,
2100 N_ATTRIBUTE1 ,
2101 N_ATTRIBUTE2 ,
2102 N_ATTRIBUTE3 ,
2103 N_ATTRIBUTE4 ,
2104 N_ATTRIBUTE5 ,
2105 N_ATTRIBUTE6 ,
2106 N_ATTRIBUTE7 ,
2107 N_ATTRIBUTE8 ,
2108 N_ATTRIBUTE9 ,
2109 N_ATTRIBUTE10 ,
2110 STATUS_ID ,
2111 TERRITORY_CODE ,
2112 TIME_SINCE_NEW ,
2113 CYCLES_SINCE_NEW ,
2114 TIME_SINCE_OVERHAUL,
2115 CYCLES_SINCE_OVERHAUL,
2116 TIME_SINCE_REPAIR ,
2117 CYCLES_SINCE_REPAIR ,
2118 TIME_SINCE_VISIT ,
2119 CYCLES_SINCE_VISIT ,
2120 TIME_SINCE_MARK ,
2121 CYCLES_SINCE_MARK ,
2122 NUMBER_OF_REPAIRS
2123 )
2124 select
2125 TRANSACTION_TEMP_ID ,
2126 LAST_UPDATE_DATE ,
2127 LAST_UPDATED_BY ,
2128 CREATION_DATE ,
2129 CREATED_BY ,
2130 LAST_UPDATE_LOGIN ,
2131 REQUEST_ID ,
2132 PROGRAM_APPLICATION_ID ,
2133 PROGRAM_ID ,
2134 PROGRAM_UPDATE_DATE ,
2135 VENDOR_SERIAL_NUMBER ,
2136 VENDOR_LOT_NUMBER ,
2137 FM_SERIAL_NUMBER ,
2138 TO_SERIAL_NUMBER ,
2139 SERIAL_PREFIX ,
2140 ERROR_CODE ,
2141 GROUP_HEADER_ID ,
2142 PARENT_SERIAL_NUMBER ,
2143 END_ITEM_UNIT_NUMBER ,
2144 SERIAL_ATTRIBUTE_CATEGORY ,
2145 ORIGINATION_DATE ,
2146 C_ATTRIBUTE1 ,
2147 C_ATTRIBUTE2 ,
2148 C_ATTRIBUTE3 ,
2149 C_ATTRIBUTE4 ,
2150 C_ATTRIBUTE5 ,
2151 C_ATTRIBUTE6 ,
2152 C_ATTRIBUTE7 ,
2153 C_ATTRIBUTE8 ,
2154 C_ATTRIBUTE9 ,
2155 C_ATTRIBUTE10 ,
2156 C_ATTRIBUTE11 ,
2157 C_ATTRIBUTE12 ,
2158 C_ATTRIBUTE13 ,
2159 C_ATTRIBUTE14 ,
2160 C_ATTRIBUTE15 ,
2161 C_ATTRIBUTE16 ,
2162 C_ATTRIBUTE17 ,
2163 C_ATTRIBUTE18 ,
2164 C_ATTRIBUTE19 ,
2165 C_ATTRIBUTE20 ,
2166 D_ATTRIBUTE1 ,
2167 D_ATTRIBUTE2 ,
2168 D_ATTRIBUTE3 ,
2169 D_ATTRIBUTE4 ,
2170 D_ATTRIBUTE5 ,
2171 D_ATTRIBUTE6 ,
2172 D_ATTRIBUTE7 ,
2173 D_ATTRIBUTE8 ,
2174 D_ATTRIBUTE9 ,
2175 D_ATTRIBUTE10 ,
2176 N_ATTRIBUTE1 ,
2177 N_ATTRIBUTE2 ,
2178 N_ATTRIBUTE3 ,
2179 N_ATTRIBUTE4 ,
2180 N_ATTRIBUTE5 ,
2181 N_ATTRIBUTE6 ,
2182 N_ATTRIBUTE7 ,
2183 N_ATTRIBUTE8 ,
2184 N_ATTRIBUTE9 ,
2185 N_ATTRIBUTE10 ,
2186 STATUS_ID ,
2187 TERRITORY_CODE ,
2188 TIME_SINCE_NEW ,
2189 CYCLES_SINCE_NEW ,
2190 TIME_SINCE_OVERHAUL,
2191 CYCLES_SINCE_OVERHAUL,
2192 TIME_SINCE_REPAIR ,
2193 CYCLES_SINCE_REPAIR ,
2194 TIME_SINCE_VISIT ,
2195 CYCLES_SINCE_VISIT ,
2196 TIME_SINCE_MARK ,
2197 CYCLES_SINCE_MARK ,
2198 NUMBER_OF_REPAIRS
2199 from mtl_serial_numbers_temp
2200 where transaction_temp_id in ( select wtlt.serial_transaction_temp_id
2201 from wms_material_txn_trace wmtt,
2202 wms_transaction_lots_trace wtlt
2203 where wmtt.transaction_header_id = l_txn_header_id
2204 and wmtt.transaction_temp_id = wtlt.transaction_temp_id);
2205 End if;
2206
2207 End if;
2208
2209 EXCEPTION
2210 when fnd_api.g_exc_error then
2211 x_return_status := fnd_api.g_ret_sts_error;
2212 fnd_msg_pub.count_and_get( p_count => x_msg_count
2213 ,p_data => x_msg_data );
2214
2215 when fnd_api.g_exc_unexpected_error then
2216 x_return_status := fnd_api.g_ret_sts_unexp_error;
2217 fnd_msg_pub.count_and_get( p_count => x_msg_count
2218 ,p_data => x_msg_data );
2219
2220 when others then
2221 x_return_status := fnd_api.g_ret_sts_unexp_error;
2222 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
2223 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2224 end if;
2225 End insert_txn_trace_rows;
2226 ---------------------------------------------------------------------------------------
2227 ---- This procedure is used by 'Run time trace form' to set the
2228 ---- global variables based on WMS_RULE_TRACE_HEADERS record for a given move order
2229 ---- so that the actual traceed path could be shown in the form.
2230
2231 procedure set_global_variables(
2232 p_move_order_line_id in NUMBER
2233 ,p_trace_date in DATE
2234 ,x_return_status out NOCOPY VARCHAR2) is
2235
2236 cursor trace_globals(p_move_order_id number, p_trace_date date ) is
2237 select header_id,
2238 strategy_id,
2239 object_id,
2240 type_code,
2241 pick_header_id,
2242 creation_date
2243 from wms_rule_trace_headers
2244 where move_order_line_id = p_move_order_line_id
2245 and to_char(creation_date, 'HH:MI:SS') = to_char(p_trace_date, 'HH:MI:SS')
2246 and simulation_mode = 'N'
2247 order by type_code ;
2248
2249 --l_pick_header_id number := 0;
2250 begin
2251 --- Pick Search order Global Variables
2252
2253 for c_trace in trace_globals(p_move_order_line_id, p_trace_date) loop
2254
2255 if ( c_trace.type_code = 2 ) then
2256 if (c_trace.header_id = G_PICK_HEADER_ID) then
2257
2258 G_PICK_SEQ_NUM := c_trace.object_id; ---- currently used for sequence number
2259 G_PICK_STRATEGY_ID := c_trace.strategy_id;
2260
2261
2262 end if;
2263 elsif ( c_trace.type_code = 1 and c_trace.creation_date = p_trace_date ) then
2264
2265
2266 G_PUTAWAY_SEQ_NUM := c_trace.object_id; ---- currently used for sequence number
2267 G_PUTAWAY_STRATEGY_ID := c_trace.strategy_id;
2268 G_PICK_HEADER_ID := c_trace.pick_header_id;
2269 G_PUTAWAY_HEADER_ID := c_trace.header_id;
2270 end if;
2271
2272 end loop;
2273 x_return_status := 'Y' ;
2274 exception
2275 when others then
2276 x_return_status := 'N' ;
2277
2278 end set_global_variables;
2279 -------------------------------------------------
2280 --- get Pick or Putaway header id from global variables
2281
2282 FUNCTION get_trace_line_header_id( engine_type IN VARCHAR2 )
2283 RETURN NUMBER is
2284 l_header_id number := 0;
2285 begin
2286 if (engine_type = 'PICK') then
2287 l_header_id := G_PICK_HEADER_ID;
2288 elsif (engine_type = 'PUTAWAY') then
2289 l_header_id := G_PUTAWAY_HEADER_ID;
2290 end if;
2291 RETURN l_header_id;
2292 END get_trace_line_header_id;
2293 ------------------------------------------------
2294
2295 FUNCTION get_strategy_id( p_rule_type IN NUMBER )
2296 RETURN NUMBER is
2297 l_strategy_id number;
2298 Begin
2299 IF p_rule_type = 2 THEN
2300 l_strategy_id := G_PICK_STRATEGY_ID;
2301 ELSIF p_rule_type = 1 THEN
2302 l_strategy_id := G_PUTAWAY_STRATEGY_ID;
2303 ELSIF p_rule_type = 5 THEN
2304 l_strategy_id := G_COSTGROUP_STRATEGY_ID;
2305 END IF;
2306 RETURN l_strategy_id;
2307 END get_strategy_id;
2308
2309
2310
2311 FUNCTION get_rule_id( p_rule_type IN NUMBER )
2312 RETURN NUMBER is
2313 l_rule_id number;
2314 Begin
2315 IF p_rule_type = 2 THEN
2316 l_rule_id := G_PICK_RULE_ID;
2317 ELSIF p_rule_type = 1 THEN
2318 l_rule_id := G_PUTAWAY_RULE_ID;
2319 ELSIF p_rule_type = 5 THEN
2320 l_rule_id := G_COSTGROUP_RULE_ID;
2321 END IF;
2322 RETURN l_rule_id;
2323 END get_rule_id;
2324
2325 -----------------------
2326 FUNCTION get_seq_num( p_rule_type IN NUMBER )
2327 RETURN NUMBER is
2328 l_seq_num number := 0;
2329 Begin
2330 IF p_rule_type = 2 THEN
2331 l_seq_num := G_PICK_SEQ_NUM;
2332 ELSIF p_rule_type = 1 THEN
2333 l_seq_num := G_PUTAWAY_SEQ_NUM;
2334 ELSIF p_rule_type = 5 THEN
2335 l_seq_num := G_COSTGROUP_SEQ_NUM ;
2336 END IF;
2337 RETURN l_seq_num;
2338 END get_seq_num;
2339 ---
2340 END; -- Package Body WMS_SEARCH_ORDER_GLOBALS_PVT