1 PACKAGE WSH_PICK_LIST AUTHID CURRENT_USER AS
2 /* $Header: WSHPGSLS.pls 120.10.12020000.3 2013/03/29 10:11:50 suppal ship $ */
3
4 --
5 -- Package
6 -- WSH_PICK_LIST
7 --
8 -- Purpose
9 -- This package does the following:
10 -- - Generate selection list
11 -- - Call Move Order APIs to create reservations and to print Pick Slips
12 --
13
14 --
15 -- DECLARE CONSTANTS FOR X-DOCK
16 --
17 C_INVENTORY_ONLY CONSTANT VARCHAR2(1) := 'I';
18 C_CROSSDOCK_ONLY CONSTANT VARCHAR2(1) := 'C';
19 C_PRIORITIZE_CROSSDOCK CONSTANT VARCHAR2(1) := 'X';
20 C_PRIORITIZE_INVENTORY CONSTANT VARCHAR2(1) := 'N';
21 --
22
23 -- 1729516 G_BATCH_ID is now initialized to NULL
24 G_BATCH_ID NUMBER := NULL;
25 G_BACKORDERED BOOLEAN := FALSE;
26 G_SEED_DOC_SET NUMBER := NULL;
27 G_AUTO_PICK_CONFIRM VARCHAR2(1) := NULL;
28 G_PICK_REL_PARALLEL BOOLEAN := FALSE;
29 G_NUM_WORKERS NUMBER;
30 G_ASSIGNED_DEL_TBL WSH_UTIL_CORE.Id_Tab_Type;
31
32 TYPE unassign_delivery_id_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
33 G_UNASSIGNED_DELIVERY_IDS WSH_PICK_LIST.unassign_delivery_id_type;
34
35 --Bug 7171766
36 TYPE group_match_seq_rec_type IS RECORD (
37 delivery_detail_id NUMBER,
38 match_group_id NUMBER,
39 delivery_group_id NUMBER
40 );
41
42 TYPE group_match_seq_tab_type IS TABLE OF group_match_seq_rec_type
43 INDEX BY BINARY_INTEGER;
44
45 --bug 7171766 till here
46
47
48 TYPE Org_Params_Rec IS RECORD (
49 WMS_ORG VARCHAR2(1),
50 EXPRESS_PICK_FLAG VARCHAR2(1),
51 AUTO_PICK_CONFIRM VARCHAR2(1),
52 AUTODETAIL_FLAG WSH_SHIPPING_PARAMETERS.AUTODETAIL_PR_FLAG%TYPE,
53 AUTOCREATE_DELIVERIES WSH_SHIPPING_PARAMETERS.AUTOCREATE_DEL_ORDERS_PR_FLAG%TYPE,
54 PICK_SEQ_RULE_ID WSH_SHIPPING_PARAMETERS.PICK_SEQUENCE_RULE_ID%TYPE,
55 PICK_GROUPING_RULE_ID WSH_SHIPPING_PARAMETERS.PICK_GROUPING_RULE_ID%TYPE,
56 AUTOPACK_LEVEL WSH_SHIPPING_PARAMETERS.AUTOPACK_LEVEL%TYPE,
57 USE_HEADER_FLAG WSH_SHIPPING_PARAMETERS.AUTOCREATE_DEL_ORDERS_PR_FLAG%TYPE,
58 AUTO_APPLY_ROUTING_RULES WSH_SHIPPING_PARAMETERS.AUTO_APPLY_ROUTING_RULES%TYPE,
59 AUTO_CALC_FGT_RATE_CR_DEL WSH_SHIPPING_PARAMETERS.AUTO_CALC_FGT_RATE_CR_DEL%TYPE,
60 TASK_PLANNING_FLAG WSH_SHIPPING_PARAMETERS.TASK_PLANNING_FLAG%TYPE,
61 PRINT_PICK_SLIP_MODE WSH_SHIPPING_PARAMETERS.PRINT_PICK_SLIP_MODE%TYPE,
62 APPEND_FLAG WSH_SHIPPING_PARAMETERS.APPENDING_LIMIT%TYPE,
63 DOC_SET_ID WSH_SHIPPING_PARAMETERS.PICK_RELEASE_REPORT_SET_ID%TYPE,
64 TO_SUBINVENTORY WSH_SHIPPING_PARAMETERS.DEFAULT_STAGE_SUBINVENTORY%TYPE,
65 TO_LOCATOR WSH_SHIPPING_PARAMETERS.DEFAULT_STAGE_LOCATOR_ID%TYPE,
66 ENFORCE_SHIP_SET_AND_SMC WSH_SHIPPING_PARAMETERS.ENFORCE_SHIP_SET_AND_SMC%TYPE,
67 DYNAMIC_REPLENISHMENT_FLAG WSH_SHIPPING_PARAMETERS.DYNAMIC_REPLENISHMENT_FLAG%TYPE --bug# 6689448 (replenishment project)
68 );
69
70 TYPE Org_Params_Rec_Tbl IS TABLE OF Org_Params_Rec INDEX BY BINARY_INTEGER;
71
72 -- X-dock, moved out of body to spec
73 TYPE DelDetTabTyp IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
74
75 -- PUBLIC FUNCTIONS/PROCEDURES
76
77 -- Start of comments
78 -- API name : Release_Batch_Sub
79 -- Type : Public
80 -- Pre-reqs : None.
81 -- Procedure: This is core api to pick release a batch, api does
82 -- 1. Gets initializes batch criteria.
83 -- 2. Select details lines based on release criteria.
84 -- 3. Calls Inventory Move Order api for allocation and pick confirmation.
85 -- 4. Create/Appened deliveries, pack or ship confirmed based on release criteria.
86 -- 5. Print document sets.
87 -- Parameters :
88 -- IN:
89 -- p_batch_id IN Batch Id.
90 -- p_worker_id IN Worker Id.
91 -- p_mode IN Mode.
92 -- p_log_level IN Set the debug log level.
93 -- OUT:
94 -- errbuf OUT NOCOPY Error message.
95 -- retcode OUT NOCOPY Error Code 1:Successs, 2:Warning and 3:Error.
96 -- End of comments
97 PROCEDURE Release_Batch_Sub(
98 errbuf OUT NOCOPY VARCHAR2,
99 retcode OUT NOCOPY VARCHAR2,
100 p_batch_id IN NUMBER,
101 p_worker_id IN NUMBER,
102 p_mode IN VARCHAR2,
103 p_log_level IN NUMBER DEFAULT 0
104 );
105
106
107 -- Start of comments
108 -- API name : Release_Batch
109 -- Type : Public
110 -- Pre-reqs : None.
111 -- Procedure: This is core api to pick release a batch, api does
112 -- 1. Gets initializes batch criteria.
113 -- 2. Select details lines based on release criteria.
114 -- 3. Calls Inventory Move Order api for allocation and pick confirmation.
115 -- 4. Create/Appened deliveries, pack or ship confirmed based on release criteria.
116 -- 5. Print document sets.
117 -- Parameters :
118 -- IN:
119 -- p_batch_id IN Batch Id.
120 -- p_log_level IN Set the debug log level.
121 -- p_num_workers IN Number of workers
122 -- OUT:
123 -- errbuf OUT NOCOPY Error message.
124 -- retcode OUT NOCOPY Error Code 1:Successs, 2:Warning and 3:Error.
125 -- End of comments
126 PROCEDURE Release_Batch(
127 errbuf OUT NOCOPY VARCHAR2,
128 retcode OUT NOCOPY VARCHAR2,
129 p_batch_id IN NUMBER,
130 p_log_level IN NUMBER DEFAULT 0,
131 p_num_workers IN NUMBER
132 );
133 -- Bug # 2231365 : Defaulted the parameter p_log_level to 0
134
135
136 -- Start of comments
137 -- API name : Release_Batch_SRS
138 -- Type : Public
139 -- Pre-reqs : None.
140 -- Procedure: This procedure gets the defaults from Shipping Parameters and
141 -- generate the batch id. It saves to the picking batch table and
142 -- call Release_Batch. It is called from Pick Release SRS.
143 -- Parameters :
144 -- IN:
145 -- p_batch_prefix IN Batch Id.
146 -- p_rule_id IN Rule Id.
147 -- p_log_level IN Set the debug log level.
148 -- p_num_workers IN Number of workers
149 -- OUT:
150 -- errbuf OUT NOCOPY Error message.
151 -- retcode OUT NOCOPY Error Code 1:Successs, 2:Warning and 3:Error.
152 -- End of comments
153 PROCEDURE Release_Batch_SRS (
154 errbuf OUT NOCOPY VARCHAR2,
155 retcode OUT NOCOPY VARCHAR2,
156 p_rule_id IN NUMBER,
157 p_batch_prefix IN VARCHAR2,
158 p_log_level IN NUMBER, -- log level fix
159 p_ship_confirm_rule_id IN NUMBER DEFAULT NULL,
160 p_actual_departure_date IN VARCHAR2 DEFAULT NULL,
161 p_num_workers IN NUMBER
162 );
163
164
165 -- Start of comments
166 -- API name : Online_Release
167 -- Type : Public
168 -- Pre-reqs : None.
169 -- Procedure: This procedure calls Release_Batch based on batch id passed,
170 -- this is call from Release Sales Order from in online mode.
171 -- Parameters :
172 -- IN:
173 -- p_batch_id IN Batch Id.
174 -- OUT:
175 -- p_pick_result OUT NOCOPY Pick Release Phase, Valid value 'START','MOVE ORDER LINE','SUCCESS'.
176 -- p_pick_phase OUT NOCOPY Pick Release Result,Valid value Success,Warning,Error
177 -- p_pick_skip OUT NOCOPY If Pick Release has been skip, valid value Y/N.
178 -- End of comments
179 PROCEDURE Online_Release (
180 p_batch_id IN NUMBER ,
181 p_pick_result OUT NOCOPY VARCHAR2,
182 p_pick_phase OUT NOCOPY VARCHAR2,
183 p_pick_skip OUT NOCOPY VARCHAR2);
184
185
186 -- Start of comments
187 -- API name : Launch_Pick_Release
188 -- Type : Public
189 -- Pre-reqs : None.
190 -- Procedure: API to launch pick Release process based on input entity passed. Api dose
191 -- 1. Get the Entity name for the passed entity
192 -- 2. Get the entity status.
193 -- 3. Create picking batch by calling WSH_PICKING_BATCHES_PKG.Insert_Row.
194 -- 4. Update the detail lines with batch id.
195 -- 5. Submit request to release batch created by calling WSH_PICKING_BATCHES_PKG.Submit_Release_Request
196 -- Parameters :
197 -- IN:
198 -- p_trip_ids IN Trip id.
199 -- p_stop_ids IN Stop Id.
200 -- p_delivery_ids IN Delivery id.
201 -- p_detail_ids IN Delivery detail Id.
202 -- p_auto_pack_ship IN SC - Auto Ship Confirm after Pick Release
203 -- PS - Auto Pack and Ship Confirm after Pick Release.
204 -- OUT:
205 -- x_return_status OUT NOCOPY Standard to output api status.
206 -- x_request_ids OUT NOCOPY Request ID of concurrent program.
207 -- End of comments
208 -- bug# 6719369 (replenishment project): For dynamic replenishment case, WMS passes the delivery detail ids as well as
209 -- the picking batch id value. In this case it shoud create a new batch by taking the attribute values from the old batch
210 -- information.
211 PROCEDURE Launch_Pick_Release(
212 p_trip_ids IN WSH_UTIL_CORE.Id_Tab_Type,
213 p_stop_ids IN WSH_UTIL_CORE.Id_Tab_Type,
214 p_delivery_ids IN WSH_UTIL_CORE.Id_Tab_Type,
215 p_detail_ids IN WSH_UTIL_CORE.Id_Tab_Type,
216 x_request_ids OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
217 p_auto_pack_ship IN VARCHAR2 DEFAULT NULL,
218 p_batch_id IN NUMBER DEFAULT NULL, -- bug# 6719369 (replenishment project)
219 x_return_status OUT NOCOPY VARCHAR2
220 );
221
222 -- muom: for Overloading (WMS)
223 PROCEDURE Calculate_Reservations(
224 p_demand_source_header_id IN NUMBER,
225 p_demand_source_line_id IN NUMBER,
226 p_requested_quantity IN NUMBER,
227 p_requested_quantity_uom IN VARCHAR2,
228 p_src_requested_quantity_uom IN VARCHAR2,
229 p_src_requested_quantity IN NUMBER,
230 p_inv_item_id IN NUMBER,
231 p_requested_quantity2 IN NUMBER default NULL,
232 x_result OUT NOCOPY NUMBER,
233 x_result2 OUT NOCOPY NUMBER);
234
235
236 -- Start of comments
237 -- API name : Calculate_Reservations
238 -- Type : Public
239 -- Pre-reqs : None.
240 -- Procedure: API to calculate the Reservations quantity, api does
241 -- 1. Scan through global demand table and find out if reservation exists for input header and line.
242 -- 2. If not fetch the reservation from inventory reservation table.
243 -- 3. Calculate the reservation quantity.
244 -- Parameters :
245 -- IN:
246 -- p_demand_source_header_id IN Demand hedaer id.
247 -- p_demand_source_line_id IN Demand line id.
248 -- p_requested_quantity IN Requested Quantity.
249 -- OUT:
250 -- x_result OUT NOCOPY Reservations quantity.
251 -- End of comments
252 -- HW OPMCONV - Added parameters: 1) p_requested_quantity2
253 -- 2) x_result2
254 --Bug 4775539 Added 4 new in parameters
255 PROCEDURE Calculate_Reservations(
256 p_demand_source_header_id IN NUMBER,
257 p_demand_source_line_id IN NUMBER,
258 p_requested_quantity IN NUMBER,
259 --Bug 4775539
260 p_requested_quantity_uom IN VARCHAR2,
261 p_src_requested_quantity_uom IN VARCHAR2,
262 p_src_requested_quantity IN NUMBER,
263 -- muom
264 p_src_requested_quantity2 IN NUMBER,
265 p_inv_item_id IN NUMBER,
266 p_requested_quantity2 IN NUMBER default NULL,
267 -- muom
268 x_fulfill_base OUT NOCOPY VARCHAR2,
269 x_result OUT NOCOPY NUMBER,
270 x_result2 OUT NOCOPY NUMBER);
271
272
273 /* rlanka : Pack J Enhancement */
274 -- Start of comments
275 -- API name : CalcWorkingDay
276 -- Type : Public
277 -- Pre-reqs : None.
278 -- Procedure: This procedure uses the shipping calendar to determine
279 -- the next (or) prior working day. Used if the picking rule has dynamic date components.
280 -- Parameters :
281 -- IN:
282 -- p_orgID IN Organization Id.
283 -- p_days IN Days in Number 1-31.
284 -- p_Time IN Time.
285 -- p_CalCode IN Shipping Calender Code.
286 -- OUT:
287 -- x_date OUT NOCOPY Working days date.
288 -- End of comments
289 PROCEDURE CalcWorkingDay(p_orgID IN NUMBER,
290 p_PickRule IN VARCHAR2, --Added bug 7316707
291 p_days IN NUMBER,
292 p_Time IN NUMBER,
293 p_CalCode IN VARCHAR2,
294 x_date IN OUT NOCOPY DATE);
295 END WSH_PICK_LIST;