1 PACKAGE wms_wave_planning_pvt
2 /* $Header: WMSWVPVS.pls 120.18.12020000.2 2013/03/03 21:39:13 ajunnikr ship $ */
3 as
4 -- PACKAGE TYPES
5 --
6 TYPE line_tbl_typ IS TABLE OF wms_wp_wave_lines%ROWTYPE INDEX BY BINARY_INTEGER;
7 TYPE action_tbl_typ IS TABLE OF varchar2(6) INDEX BY BINARY_INTEGER;
8
9 --bug 11870443
10 type num_t is table of number;
11
12 TYPE relRecTyp IS RECORD(
13 SOURCE_CODE VARCHAR2(30),
14 SOURCE_HEADER_ID NUMBER,
15 SOURCE_LINE_ID NUMBER,
16 SOURCE_HEADER_NUMBER VARCHAR2(150),
17 SOURCE_LINE_NUMBER VARCHAR2(150),
18 SOURCE_HEADER_TYPE_NAME VARCHAR2(240),
19 SOURCE_HEADER_TYPE_ID NUMBER,
20 SOURCE_DOCUMENT_TYPE_ID NUMBER,
21 DELIVERY_DETAIL_ID NUMBER,
22 RELEASED_STATUS VARCHAR2(1),
23 ORGANIZATION_ID NUMBER,
24 INVENTORY_ITEM_ID NUMBER,
25 REQUESTED_QUANTITY NUMBER,
26 REQUESTED_QUANTITY_UOM VARCHAR2(3),
27 PRIMARY_QUANTITY NUMBER,
28 PRIMARY_UOM_CODE VARCHAR2(3),
29 MOVE_ORDER_LINE_ID NUMBER,
30 SHIP_MODEL_COMPLETE_FLAG VARCHAR2(1),
31 TOP_MODEL_LINE_ID NUMBER,
32 SHIP_FROM_LOCATION_ID NUMBER,
33 SHIP_TO_LOCATION_ID NUMBER,
34 SHIP_METHOD_CODE VARCHAR2(30),
35 SHIPMENT_PRIORITY_CODE VARCHAR2(30),
36 SHIP_SET_ID NUMBER,
37 DATE_SCHEDULED DATE,
38 PLANNED_DEPARTURE_DATE DATE,
39 DELIVERY_ID NUMBER,
40 CUSTOMER_ID NUMBER,
41 CARRIER_ID NUMBER,
42 PREFERRED_GRADE VARCHAR2(150),
43 REQUESTED_QUANTITY2 NUMBER,
44 REQUESTED_QUANTITY_UOM2 VARCHAR2(3),
45 PROJECT_ID NUMBER,
46 TASK_ID NUMBER,
47 FROM_SUBINVENTORY_CODE VARCHAR2(10),
48 TO_SUBINVENTORY_CODE VARCHAR2(10),
49 END_ITEM_UNIT_NUMBER VARCHAR2(30),
50 RESERVABLE_TYPE NUMBER,
51 DEMAND_SOURCE_HEADER_ID NUMBER,
52 OUTSTANDING_ORDER_VALUE NUMBER,
53 COUNT_ORDER_LINES NUMBER,
54 NET_WEIGHT_UOM_CODE VARCHAR2(3),
55 NET_WEIGHT NUMBER,
56 NET_WEIGHT_MAX_OL_UOM_CODE NUMBER,
57 VOLUME_UOM_CODE VARCHAR2(3),
58 VOLUME NUMBER,
59 VOLUME_MAX_OL_CUBE_UOM_CODE NUMBER,
60 NET_VALUE NUMBER,
61 DELIVER_SNAPSHOT_DATE DATE);
62
63 TYPE labor_plan_Record IS RECORD(
64 picking_subinventory varchar2(4000),
65 SOURCE_SUBINVENTORY varchar2(4000),
66 picking_uom varchar2(4000),
67 conversion_rate number,
68 att number,
69 demand_qty_picking_uom number default 0,
70 demand_quantity number);
71
72 TYPE labor_time_Record IS RECORD(
73 picking_subinventory varchar2(4000),
74 destination_SUBINVENTORY varchar2(4000),
75 picking_uom varchar2(4000),
76 demand_qty_picking_uom number default 0,
77 operation_plan_id number,
78 inventory_item_id number,
79 standard_operation_id number);
80
81 n1 number := 0;
82
83 TYPE labor_plan_tbl IS TABLE OF labor_plan_Record INDEX BY BINARY_INTEGER;
84
85 TYPE labor_time_tbl IS TABLE OF labor_time_record INDEX BY BINARY_INTEGER;
86
87 x_labor_plan_tbl labor_plan_tbl;
88
89 ideal_labor_plan_tbl labor_plan_tbl;
90
91 x_labor_time_tbl labor_time_tbl;
92
93 c_labor_time_tbl labor_time_tbl;
94
95
96 ---------- RESOURCE CAPACITY DETAILS ---------
97 TYPE resource_capacity_record IS RECORD(
98 resource_name VARCHAR2(100),
99 resource_type NUMBER,
100 total_capacity NUMBER default 0,
101 available_capacity NUMBER default 0,
102 planned_load NUMBER default 0,
103 current_load NUMBER default 0,
104 planned_tasks NUMBER default 0,
105 actual_tasks NUMBER default 0
106 );
107
108 TYPE resource_capacity_tbl IS TABLE OF resource_capacity_record INDEX BY BINARY_INTEGER;
109 x_resource_capacity_tbl resource_capacity_tbl;
110 ---------- RESOURCE CAPACITY DETAILS ---------
111
112 -- labor planning
113
114 /*TYPE labor_details_record IS RECORD(
115 resource_name VARCHAR2(100),
116 source_subinventory VARCHAR2(100),
117 destination_subinventory VARCHAR2(100),
118 pick_uom VARCHAR2(25),
119 transaction_time number,
120 travel_time number,
121 resource_type number);
122
123 type machine_details_record is record(
124 resource_name varchar2(100),
125 source_subinventory varchar2(100),
126 destination_subinventory varchar2(100),
127 pick_uom varchar2(25),
128 resource_type number
129 );
130
131 TYPE labor_dtl_tbl IS TABLE OF labor_details_record INDEX BY BINARY_INTEGER;
132 type machine_dtl_tbl is table of machine_details_record index by binary_integer;
133
134 x_labor_dtl_tbl labor_dtl_tbl;
135 x_machine_dtl_tbl machine_dtl_tbl;
136
137 TYPE labor_statistics_Record IS RECORD(
138 resource_name varchar2(100),
139 total_time_per_Resource NUMBER DEFAULT 0,
140 planned_wave_load NUMBER DEFAULT 0,
141 total_Capacity NUMBER DEFAULT 0,
142 current_workload number default 0, -- sudheer
143 number_of_tasks NUMBER DEFAULT 0, -- sudheer
144 resource_type number, -- sudheer
145 available_capacity NUMBER DEFAULT 0, -- sudheer
146 number_of_planned_tasks NUMBER DEFAULT 0); -- sudheer
147
148 TYPE labor_stats_tbl IS TABLE OF labor_statistics_Record INDEX BY BINARY_INTEGER;
149
150 x_labor_stats_tbl labor_stats_tbl;
151 x_labor_stats_tbl_tmp labor_stats_tbl;
152 */
153 -- Labor Planning
154
155 tbl_index number := 0;
156
157 TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
158 TYPE uom_tab IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
159 TYPE date_tab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
160 TYPE char_tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
161 TYPE char1_tab IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
162 TYPE number_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
163 TYPE relRecTabTyp IS TABLE OF relRecTyp INDEX BY BINARY_INTEGER;
164
165 TYPE query_type_table_type IS TABLE OF wms_saved_queries.query_type%TYPE INDEX BY BINARY_INTEGER;
166 TYPE field_name_table_type IS TABLE OF wms_saved_queries.field_name%TYPE INDEX BY BINARY_INTEGER;
167 TYPE field_value_table_type IS TABLE OF wms_saved_queries.field_value%TYPE INDEX BY BINARY_INTEGER;
168 TYPE organization_id_table_type IS TABLE OF wms_saved_queries.organization_id%TYPE INDEX BY BINARY_INTEGER;
169
170 -- PUBLIC VARIABLES
171 --
172 release_table relRecTabTyp;
173 g_application_id NUMBER;
174 g_program_id NUMBER;
175 g_request_id NUMBER;
176 g_user_id NUMBER;
177 g_login_id NUMBER;
178 g_batch_name VARCHAR2(30);
179 g_to_request_date DATE;
180 g_from_sched_ship_date DATE;
181 g_to_sched_ship_date DATE;
182 g_append_flag VARCHAR2(1);
183 g_ship_set_smc_flag VARCHAR2(1);
184 g_use_delivery_ps VARCHAR2(1) := 'N';
185 g_allocation_method VARCHAR2(1);
186 g_crossdock_criteria_id NUMBER;
187 g_actual_departure_date DATE;
188 g_credit_check_option VARCHAR2(1) := NULL;
189 g_sql_stmt VARCHAR2(32767);
190 g_honor_pick_from VARCHAR2(1) := 'Y';
191 g_dynamic_replenishment_flag VARCHAR2(1);
192 g_initialized BOOLEAN := FALSE;
193 -- g_ordered_psr psrTabTyp;
194 -- g_use_order_ps VARCHAR2(1) := 'Y';
195 -- g_total_pick_criteria NUMBER;
196 g_order_type_id NUMBER;
197 g_order_line_id NUMBER;
198 g_order_header_id NUMBER;
199 g_backorders_flag VARCHAR2(1);
200 g_include_planned_lines VARCHAR2(1);
201 g_del_line_id NUMBER;
202 g_del_lines_list VARCHAR2(100);
203 g_customer_id NUMBER;
204 g_from_request_date DATE;
205 g_existing_rsvs_only_flag VARCHAR2(1);
206 g_inventory_item_id NUMBER;
207 g_shipment_priority VARCHAR2(30);
208 g_ship_method_code VARCHAR2(30);
209 g_ship_set_number NUMBER;
210 g_ship_to_loc_id NUMBER;
211 g_ship_from_loc_id NUMBER;
212 g_project_id NUMBER;
213 g_task_id NUMBER;
214 g_doc_set_id NUMBER;
215 g_Unreleased_SQL VARCHAR(32000) := NULL;
216 g_Backordered_SQL VARCHAR(4000) := NULL;
217 g_Cond_SQL VARCHAR(4000) := NULL;
218 g_orderby_SQL VARCHAR2(500) := NULL;
219 g_from_subinventory VARCHAR2(10);
220 g_from_locator VARCHAR2(10);
221 g_DELIVERY_ID NUMBER;
222 g_trip_id number;
223 g_trip_stop_id number;
224 g_pick_seq_rule_id number;
225 g_pick_grouping_rule_id number;
226 g_to_dock_appoint_date date;
227 g_from_dock_appoint_date date;
228 g_customer_class_id varchar2(100);
229 g_carrier_id NUMBER;
230 g_item_category_id NUMBER;
231 g_add_lines varchar2(1);
232 new_wave_type WSH_PICKING_BATCHES_PUB.Batch_Info_Rec;
233
234 --Variables and Tables for manage tasks
235
236 TYPE transaction_temp_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
237 TYPE task_type_id_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
238 TYPE task_id_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
239 TYPE result_table_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
240 TYPE message_table_type IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
241 g_task_updated wms_wp_tasks_gtmp.error%TYPE;
242 g_plan_cancelled wms_wp_tasks_gtmp.error%TYPE;
243 g_task_saved wms_wp_tasks_gtmp.error%TYPE;
244 TYPE lookup_meaning_table IS TABLE OF mfg_lookups.meaning%TYPE INDEX BY BINARY_INTEGER;
245 TYPE row_ids_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
246 TYPE lookup_meaning_table_type IS TABLE OF mfg_lookups.meaning%TYPE INDEX BY BINARY_INTEGER;
247
248 g_task_types lookup_meaning_table;
249 g_task_types_orig lookup_meaning_table;
250 g_status_codes lookup_meaning_table;
251 g_status_codes_orig lookup_meaning_table;
252 g_plan_task_types lookup_meaning_table;
253 g_plan_task_types_orig lookup_meaning_table;
254 g_plan_status_codes lookup_meaning_table;
255 g_plan_status_codes_orig lookup_meaning_table;
256
257 g_task_type_pick CONSTANT NUMBER := 1;
258 g_task_type_replenish CONSTANT NUMBER := 4;
259 g_planning_criteria_id number;
260
261
262 -- Rule based Planning
263
264
265 g_from_subinventory_plan varchar2(30);
266 g_staging_subinventory_plan VARCHAR2(30);
267 g_pick_subinventory VARCHAR2(30);
268 g_default_stage_subinventory VARCHAR2(30);
269 g_default_stage_locator_id NUMBER;
270 g_enforce_ship_set_and_smc VARCHAR2(1);
271 g_to_subinventory varchar(30);
272 g_to_locator NUMBER;
273 g_staging_subinventory VARCHAR2(30);
274 g_mo_header_id number;
275 g_request_number number;
276
277
278
279 PROCEDURE launch_concurrent_CP(errbuf OUT NOCOPY VARCHAR2,
280 retcode OUT NOCOPY NUMBER,
281 p_wave_template_name in varchar2,
282 p_wave_header_id in number,
283 p_org_id in number);
284
285 PROCEDURE submit_WP_conc_request(p_wave_header_id in number,
286 p_org_id in number,
287 x_request_id OUT NOCOPY number);
288
289 procedure insert_wave_record(p_wave_header_id in OUT NOCOPY number);
290
291 procedure set_global_variable(order_type_id in number,
292 order_header_id in number,
293 backorders_flag in varchar2,
294 include_planned_lines in varchar2,
295 customer_id in number,
296 inventory_item_id in number,
297 shipment_priority_code in varchar2,
298 ship_method_code in varchar2,
299 ship_to_loc_id in number,
300 project_id in number,
301 task_id in number,
302 delivery_id in number,
303 trip_id in number,
304 trip_stop_id in number,
305 pick_seq_rule_id in number,
306 pick_grouping_rule_id number,
307 scheduled_days in number,
308 scheduled_hrs in number,
309 dock_days in number,
310 dock_hours in number,
311 customer_class_id in VARCHAR2,
312 carrier_id in number,
313 category_Set_id in number,
314 add_lines in varchar2 default 'N');
315
316 -- Insert Wave Header requires jus the PL/SQL record wave_header_rec as IN parameter
317
318 procedure insert_wave_header(x_return_status OUT nocopy VARCHAR2,
319 p_wave_header_rec in wms_wp_wave_headers_vl%ROWTYPE);
320
321 procedure launch_online(x_return_status OUT nocopy VARCHAR2,
322 p_wave_header_id IN NUMBER,
323 v_orgid in number,
324 p_release_immediate in varchar2,
325 p_plan_wave in varchar2,
326 p_request_id OUT NOCOPY number,
327 p_add_lines in varchar2 default 'N');
328
329 procedure create_batch_record(x_return_status OUT nocopy varchar2,
330 p_wave_header_id in number);
331
332 PROCEDURE Plan_Wave_CP(errbuf OUT NOCOPY VARCHAR2,
333 retcode OUT NOCOPY NUMBER,
334 p_wave_header_id in number,
335 p_planning_criteria_id in number);
336
337 PROCEDURE Call_Plan_Wave_CP(p_wave_header_id in number,
338 p_planning_criteria_id in number,
339 p_request_id OUT NOCOPY number);
340
341 PROCEDURE Release_Batch_CP(errbuf OUT NOCOPY VARCHAR2,
342 retcode OUT NOCOPY NUMBER,
343 p_wave_header_id in number);
344
345 procedure get_dynamic_sql(p_wave_header_id in number,
346 org_id in number,
347 x_return_status OUT NOCOPY varchar2);
348
349 Procedure Init_Cursor(p_organization_id IN NUMBER,
350 v_advanced_sql in varchar2,
351 v_WAVE_HEADER_ID in NUMBER,
352 x_api_status OUT NOCOPY VARCHAR2);
353
354 PROCEDURE Get_Lines(x_done_flag OUT NOCOPY VARCHAR2,
355 x_api_status OUT NOCOPY VARCHAR2);
356
357 PROCEDURE Insert_RL_Row(x_api_status OUT NOCOPY VARCHAR2);
358 procedure Plan_Wave(p_wave_header_id in number,
359 p_planning_criteria_id in number,
360 x_return_status OUT NOCOPY varchar2);
361 PROCEDURE RELEASE_ONLINE(X_RETURN_STATUS OUT NOCOPY VARCHAR2,
362 P_WAVE_HEADER_ID IN NUMBER);
363
364 procedure get_line_fill_rate(x_return_status OUT NOCOPY varchar2,
365 p_wave_header_id in number);
366
367 procedure get_actual_fill_rate(x_return_status OUT NOCOPY varchar2,
368 p_wave_header_id in number);
369
370 procedure update_wave_header_status(x_return_status OUT NOCOPY varchar2,
371 p_wave_header_id in number,
372 Status in varchar2,
373 p_batch_id in number DEFAULT NULL);
374
375 PROCEDURE update_task(p_transaction_temp_id IN WMS_WAVE_PLANNING_PVT.transaction_temp_table_type,
376 p_task_type_id IN WMS_WAVE_PLANNING_PVT.task_type_id_table_type,
377 p_employee IN VARCHAR2,
378 p_employee_id IN NUMBER,
379 p_user_task_type IN VARCHAR2,
380 p_user_task_type_id IN NUMBER,
381 p_effective_start_date IN DATE,
382 p_effective_end_date IN DATE,
383 p_person_resource_id IN NUMBER,
384 p_person_resource_code IN VARCHAR2,
385 p_force_employee_change IN BOOLEAN,
386 p_to_status IN VARCHAR2,
387 p_to_status_id IN NUMBER,
388 p_priority_type IN varchar2 DEFAULT 'S', --mitgupta
389 p_priority IN NUMBER,
390 p_clear_priority IN VARCHAR2,
391 x_result OUT NOCOPY WMS_WAVE_PLANNING_PVT.result_table_type,
392 x_message OUT NOCOPY WMS_WAVE_PLANNING_PVT.message_table_type,
393 x_task_id OUT NOCOPY WMS_WAVE_PLANNING_PVT.task_id_table_type,
394 x_return_status OUT NOCOPY VARCHAR2,
395 x_return_msg OUT NOCOPY VARCHAR2,
396 x_msg_count OUT NOCOPY NUMBER);
397
398 PROCEDURE save_tasks(p_commit BOOLEAN,
399 p_user_id NUMBER,
400 p_login_id NUMBER,
401 x_save_count OUT NOCOPY NUMBER,
402 x_return_status OUT NOCOPY VARCHAR2,
403 x_msg_data OUT NOCOPY VARCHAR2,
404 x_msg_count OUT NOCOPY NUMBER);
405 -- Sudheer
406 FUNCTION getforcesignonflagvalue(p_transaction_temp_id IN mtl_material_transactions_temp.transaction_temp_id%TYPE,
407 p_device_id OUT NOCOPY NUMBER)
408 RETURN VARCHAR2;
409 PROCEDURE set_num_of_child_tasks;
410
411 PROCEDURE Update_Completion_Status_CP(errbuf OUT NOCOPY VARCHAR2,
412 retcode OUT NOCOPY NUMBER);
413 procedure get_pick_fill_rate(x_return_status OUT NOCOPY varchar2);
414
415 procedure labor_planning(p_wave_header_id in number,
416 p_planning_criteria_id in number,
417 x_return_status OUT NOCOPY varchar2);
418
419 FUNCTION get_conversion_rate(p_item_id IN NUMBER,
420 p_from_uom_code IN VARCHAR2,
421 p_to_uom_code IN VARCHAR2) RETURN NUMBER;
422
423 FUNCTION get_att_for_subinventory(p_sub IN VARCHAR2,
424 p_item_id IN NUMBER,
425 p_org_id IN NUMBER) RETURN NUMBER;
426
427 procedure get_ideal_pick_scenario(p_requested_qty in number,
428 x_return_status OUT NOCOPY varchar2);
429
430 function get_source_subinventory(p_item_id in number,
431 p_subinventory in varchar2)
432 return varchar2;
433
434 procedure synchronize_labor_plan_tables(x_return_status OUT NOCOPY varchar2,
435 p_replenishment_Required in varchar2,
436 v_Destination_subinventory in varchar2);
437
438 procedure update_bulk_labor_record(x_labor_plan_tbl in OUT NOCOPY labor_plan_tbl,
439 v_replenishment_required in varchar2,
440 v_destination_subinventory in varchar2,
441 p_demand_qty in OUT NOCOPY number,
442 x_return_status OUT NOCOPY varchar2);
443
444 procedure Check_wave_closed_status(x_return_status OUT NOCOPY varchar2);
445
446 function get_loaded_status(p_Delivery_Detail_id in number) return number;
447
448 function get_order_weight(p_source_header_id in number,
449 p_orgid in number) return number;
450
451 function get_order_volume(p_source_header_id in number,
452 p_orgid in number) return number;
453
454 procedure get_net_weight_volume(p_wave_header_id in number,
455 p_orgid in number,
456 x_weight OUT NOCOPY varchar2,
457 x_volume OUT NOCOPY varchar2);
458 -- Name
459 -- FUNCTION Outstanding_Order_Value
460 --
461 -- Purpose
462 -- This functions calculates the value of the order, which
463 -- is used in the order by clause for releasing lines.
464 --
465 -- Arguments
466 -- p_header_id ,p_line_id
467 --
468 -- Return Values
469 -- - value of order
470 -- - 0 if failure
471 --
472 -- Notes
473
474 FUNCTION Outstanding_Order_Value(p_header_id IN BINARY_INTEGER,
475 p_line_id IN BINARY_INTEGER)
476 RETURN BINARY_INTEGER;
477
478 PROCEDURE print_debug(p_err_msg VARCHAR2, p_debug NUMBER);
479
480 PROCEDURE print_form_messages(p_err_msg VARCHAR2);
481
482 PROCEDURE SET_QUERY_TASKS_PARAMETERS(p_field_name_table IN wms_wave_planning_pvt.field_name_table_type,
483 p_field_value_table IN wms_wave_planning_pvt.field_value_table_type,
484 p_organization_id_table IN wms_wave_planning_pvt.organization_id_table_type,
485 p_query_type_table IN wms_wave_planning_pvt.query_type_table_type,
486 x_return_status OUT NOCOPY VARCHAR2,
487 x_return_message OUT NOCOPY VARCHAR2);
488
489 PROCEDURE SET_ACTION_TASKS_PARAMETERS(p_field_name_table IN wms_wave_planning_pvt.field_name_table_type,
490 p_field_value_table IN wms_wave_planning_pvt.field_value_table_type,
491 p_query_type_table IN wms_wave_planning_pvt.query_type_table_type,
492 x_return_status OUT NOCOPY VARCHAR2,
493 x_return_message OUT NOCOPY VARCHAR2);
494
495 PROCEDURE Task_Release_CP(errbuf OUT NOCOPY VARCHAR2,
496 retcode OUT NOCOPY NUMBER,
497 p_organization_id in number,
498 p_query_name in varchar2,
499 p_task_release_id in number);
500
501 PROCEDURE catch_wave_exceptions_cp(errbuf OUT nocopy VARCHAR2,
502 retcode OUT nocopy NUMBER,
503 p_exception_name IN VARCHAR2,
504 p_organization_id IN NUMBER,
505 p_wave IN NUMBER,
506 p_exception_entity IN VARCHAR2,
507 p_progress_stage IN VARCHAR2,
508 p_completion_threshold IN NUMBER,
509 p_high_sev_exception_threshold IN NUMBER,
510 p_low_sev_exception_threshold IN NUMBER,
511 p_take_corrective_measures IN VARCHAR2,
512 p_release_back_ordered_lines IN VARCHAR2,
513 p_action_name IN VARCHAR2);
514
515 PROCEDURE insert_purge_exceptions(p_exception_name IN VARCHAR2,
516 p_exception_entity IN VARCHAR2,
517 p_exception_level IN VARCHAR2,
518 p_completion_threshold IN NUMBER,
519 p_progress_stage IN VARCHAR2,
520 p_exception_threshold IN NUMBER,
521 p_wave_id IN NUMBER,
522 p_trip_id IN NUMBER,
523 p_delivery_id IN NUMBER,
524 p_order_number IN NUMBER,
525 p_order_line_id IN NUMBER,
526 x_return_status OUT NOCOPY VARCHAR2);
527
528 PROCEDURE check_so(p_current_order IN NUMBER,
529 p_current_delivery IN NUMBER,
530 p_current_trip IN NUMBER,
531 p_organization_id IN NUMBER);
532
533
534 PROCEDURE check_line(p_current_line in NUMBER,
535 p_current_delivery in NUMBER,
536 p_current_trip in NUMBER,
537 p_organization_id in NUMBER);
538
539
540 PROCEDURE take_corrective_measures(p_exception_id IN NUMBER,
541 p_wave_id IN NUMBER,
542 p_entity IN VARCHAR2,
543 p_entity_value IN NUMBER,
544 p_release_back_ordered_lines IN VARCHAR2,
545 p_action_name IN VARCHAR2,
546 p_organization_id IN NUMBER);
547
548 PROCEDURE actionable_exceptions(p_perfect_lines IN NUMBER,
549 p_total_lines IN NUMBER,
550 p_completion_threshold IN NUMBER,
551 p_low_sev_exception_threshold IN NUMBER,
552 p_high_sev_exception_threshold IN NUMBER,
553 p_date_scheduled IN DATE,
554 p_exception_id IN NUMBER,
555 p_exception_name IN VARCHAR2,
556 p_wave_id IN NUMBER,
557 p_trip_id IN NUMBER,
558 p_delivery_id IN NUMBER,
559 p_order_number IN NUMBER,
560 p_order_line_id IN NUMBER,
561 p_current_order_id IN NUMBER,
562 p_previous_order_id IN OUT NOCOPY NUMBER,
563 p_current_line_id IN NUMBER,
564 p_previous_line_id IN OUT NOCOPY NUMBER,
565 p_entity IN VARCHAR2,
566 p_entity_value IN NUMBER,
567 p_progress_stage IN VARCHAR2,
568 p_take_corrective_measures IN VARCHAR2,
569 p_release_back_ordered_lines IN VARCHAR2,
570 p_action_name IN VARCHAR2,
571 p_organization_id IN NUMBER);
572
573 -- function get_packed_status(p_task_id in number) return boolean;
574
575 function check_min_equip_Capacity(l_mmtt_tbl in num_t)
576 return boolean;
577
578 function get_net_value(p_wave_header_id in number) return varchar2;
579
580 PROCEDURE cancel_mmtt(p_txn_temp_id NUMBER, x_result OUT nocopy VARCHAR2);
581
582 PROCEDURE allocation (errbuf OUT NOCOPY VARCHAR2,
583 retcode OUT NOCOPY NUMBER,
584 p_organization_id IN NUMBER,
585 p_wave_id IN NUMBER,
586 p_mo_header_id IN NUMBER,
587 p_mode IN VARCHAR2,
588 p_worker_id IN NUMBER DEFAULT null
589 );
590
591
592 procedure get_current_work_load(p_resource in varchar2,
593 p_planning_criteria_id in number,
594 p_wave_header_id in number,
595 x_current_workload out nocopy number,
596 x_resource_type out nocopy NUMBER,
597 x_number_of_tasks OUT nocopy NUMBER,
598 x_total_capacity out nocopy NUMBER );
599
600 PROCEDURE update_wave_lines_tbl (p_mo_line_table IN INV_Move_Order_PUB.TROLIN_TBL_TYPE
601 ,x_return_status OUT NOCOPY VARCHAR2);
602
603 --bug 11870443
604 function list_mmtt_release return num_t pipelined;
605
606 --bug12371804 for performance, move the inserts into gtmp table from the pld into here
607 PROCEDURE insert_global_temp(p_wave_header_id IN number);
608
609 --bug 11775489
610 procedure update_line_pick_fill_rate(p_wave_line_id in number);
611 procedure update_line_actual_fill_rate(p_wave_line_id in number);
612
613 procedure launch_wave_endeca(P_WAVE_NAME IN VARCHAR2,
614 P_WAVE_DESCRIPTION IN VARCHAR2,
615 P_start_time IN DATE, -- start time changes
616 P_SHIP_TO_LOCATION_ID IN NUMBER,
617 P_CUSTOMER_CLASS_ID IN VARCHAR2,
618 P_pull_replenishment_flag IN VARCHAR2,
619 P_RELEASE_IMMEDIATELY IN VARCHAR2,
620 P_ORGANIZATION_ID IN NUMBER,
621 P_PICK_SEQ_RULE_ID IN NUMBER,
622 P_PICK_GROUPING_RULE_ID IN NUMBER,
623 P_TRIP_ID IN NUMBER,
624 P_TRIP_STOP_ID IN NUMBER,
625 P_SHIP_METHOD_CODE IN VARCHAR2,
626 P_SHIPMENT_PRIORITY_CODE IN VARCHAR2,
627 P_CARRIER_ID IN NUMBER,
628 P_DELIVERY_ID IN NUMBER,
629 P_FROM_ORDER_HEADER_ID IN NUMBER,
630 P_ORDER_TYPE_ID IN NUMBER,
631 P_CUSTOMER_ID IN NUMBER,
632 P_CATEGORY_SET_ID IN NUMBER,
633 P_CATEGORY_ID IN NUMBER,
634 P_INVENTORY_ITEM_ID IN NUMBER,
635 P_INCLUDE_PLANNED_LINES IN VARCHAR2,
636 P_TASK_PLANNING_FLAG IN VARCHAR2,
637 P_APPEND_DELIVERIES IN VARCHAR2,
638 P_AUTO_CREATE_DELIVERY IN VARCHAR2,
639 P_AUTODELIVERY_CRITERIA IN VARCHAR2,
640 P_TASK_PRIORITY IN NUMBER,
641 P_DEFAULT_STAGE_SUBINVENTORY IN VARCHAR2,
642 P_DEFAULT_STAGE_LOCATOR_ID IN NUMBER,
643 P_DEFAULT_ALLOCATION_METHOD IN VARCHAR2,
644 P_WAVE_FIRMED_FLAG IN VARCHAR2,
645 P_ORDER_NAME IN VARCHAR2,
646 P_CUSTOMER IN VARCHAR2,
647 P_ORDER_TYPE IN VARCHAR2,
648 P_CUSTOMER_CLASS IN VARCHAR2,
649 P_SHIP_METHOD IN VARCHAR2,
650 P_CARRIER IN VARCHAR2,
651 P_SHIP_PRIORITY IN VARCHAR2,
652 P_DELIVERY IN VARCHAR2,
653 P_TRIP IN VARCHAR2,
654 P_TRIP_STOP IN VARCHAR2,
655 P_ITEM IN VARCHAR2,
656 P_ITEM_CATEGORY IN VARCHAR2,
657 P_SCHEDULED_DAYS IN NUMBER,
658 P_SCHEDULED_HRS IN NUMBER,
659 P_DOCK_APPOINTMENT_DAYS IN NUMBER,
660 P_DOCK_APPOINTMENT_HOURS IN NUMBER,
661 P_PICK_SLIP_GROUP IN VARCHAR2,
662 P_RELEASE_SEQ_RULE IN VARCHAR2,
663 P_STAGING_SUBINVENTORY IN VARCHAR2,
664 P_STAGING_LOCATOR IN VARCHAR2,
665 P_CROSS_DOCK_CRITERIA IN VARCHAR2,
666 P_pick_subinventory IN VARCHAR2,
667 x_return_status OUT NOCOPY VARCHAR2,
668 x_wave_header_id OUT NOCOPY NUMBER);
669
670 PROCEDURE release_wave_endeca(p_wave_header_id IN NUMBER,
671 x_request_id OUT NOCOPY NUMBER,
672 x_return_status OUT NOCOPY VARCHAR2,
673 x_message OUT NOCOPY VARCHAR2);
674
675 PROCEDURE remove_lines_endeca(p_wave_header_id IN NUMBER,
676 p_order_number IN VARCHAR2,
677 p_line_number IN VARCHAR2,
678 x_return_status OUT NOCOPY VARCHAR2);
679
680
681
682 END WMS_WAVE_PLANNING_PVT;
683
[Home] [Help]