1 PACKAGE WSH_PICKING_BATCHES_PUB AUTHID CURRENT_USER AS
2 -- $Header: WSHPRPBS.pls 120.3.12010000.2 2009/12/03 14:20:52 gbhargav ship $
3 /*#
4 * This is the public interface for picking batches. This package
5 * has procedures for creating and releasing picking batches.
6 * @rep:scope public
7 * @rep:product WSH
8 * @rep:displayname Picking Batch
9 * @rep:lifecycle active
10 * @rep:category BUSINESS_ENTITY WSH_DELIVERY_LINE
11 * @rep:category BUSINESS_ENTITY WSH_DELIVERY
12 * @rep:category BUSINESS_ENTITY WSH_TRIP
13 * @rep:category BUSINESS_ENTITY WSH_PICKING_BATCHES_PUB
14 */
15 --
16 --===================
17 -- PUBLIC VARS
18 --===================
19 --
20 --
21 TYPE Batch_Info_rec IS Record (
22 Backorders_Only_Flag wsh_picking_batches.Backorders_Only_Flag%TYPE,
23 Document_Set_Id wsh_picking_batches.Document_Set_Id%TYPE,
24 Document_Set_Name wsh_report_sets.name%TYPE,
25 Existing_Rsvs_Only_Flag wsh_picking_batches.Existing_Rsvs_Only_Flag%TYPE,
26 Shipment_Priority_Code wsh_picking_batches.Shipment_Priority_Code%TYPE,
27 Ship_Method_Code wsh_picking_batches.Ship_Method_Code%TYPE,
28 Ship_Method_Name fnd_lookup_values_vl.meaning%TYPE,
29 Customer_Id wsh_picking_batches.Customer_Id%TYPE,
30 --TCA view removal Changes Starts
31 Customer_Number hz_cust_accounts.account_number%TYPE, /*Customer Number*/
32 --TCA view removal Changes End
33 Order_Header_Id wsh_picking_batches.Order_Header_Id%TYPE,
34 Order_Number oe_order_headers_all.Order_Number%TYPE,
35 Ship_Set_Id wsh_picking_batches.Ship_Set_Number%TYPE,
36 Ship_Set_Number oe_sets.set_name%TYPE,
37 Inventory_Item_Id wsh_picking_batches.Inventory_Item_Id%TYPE,
38 Order_Type_Id wsh_picking_batches.Order_Type_Id%TYPE,
39 Order_Type_Name oe_transaction_types_tl.name%TYPE,
40 From_Requested_Date DATE,
41 To_Requested_Date DATE,
42 From_Scheduled_Ship_Date DATE,
43 To_Scheduled_Ship_Date DATE,
44 Ship_To_Location_Id wsh_picking_batches.Ship_To_Location_Id%TYPE,
45 Ship_To_Location_code hr_locations_all_tl.location_code%TYPE,
46 Ship_From_Location_Id wsh_picking_batches.Ship_From_Location_Id%TYPE,
47 Ship_From_Location_code hr_locations_all_tl.location_code%TYPE,
48 Trip_Id wsh_picking_batches.Trip_Id%TYPE,
49 Trip_Name wsh_trips.name%TYPE,
50 Delivery_Id wsh_picking_batches.Delivery_Id%TYPE,
51 Delivery_Name wsh_new_deliveries.name%TYPE,
52 Include_Planned_Lines wsh_picking_batches.Include_Planned_Lines%TYPE,
53 Pick_Grouping_Rule_Id wsh_picking_batches.Pick_Grouping_Rule_Id%TYPE,
54 Pick_Grouping_Rule_Name wsh_pick_grouping_rules.name%TYPE,
55 Pick_Sequence_Rule_Id wsh_picking_batches.Pick_Sequence_Rule_Id%TYPE,
56 Pick_Sequence_Rule_Name wsh_pick_sequence_rules.name%TYPE,
57 Autocreate_Delivery_Flag wsh_picking_batches.Autocreate_Delivery_Flag%TYPE,
58 Attribute_Category wsh_picking_batches.Attribute_Category%TYPE,
59 Attribute1 wsh_picking_batches.Attribute1%TYPE,
60 Attribute2 wsh_picking_batches.Attribute2%TYPE,
61 Attribute3 wsh_picking_batches.Attribute3%TYPE,
62 Attribute4 wsh_picking_batches.Attribute4%TYPE,
63 Attribute5 wsh_picking_batches.Attribute5%TYPE,
64 Attribute6 wsh_picking_batches.Attribute6%TYPE,
65 Attribute7 wsh_picking_batches.Attribute7%TYPE,
66 Attribute8 wsh_picking_batches.Attribute8%TYPE,
67 Attribute9 wsh_picking_batches.Attribute9%TYPE,
68 Attribute10 wsh_picking_batches.Attribute10%TYPE,
69 Attribute11 wsh_picking_batches.Attribute11%TYPE,
70 Attribute12 wsh_picking_batches.Attribute12%TYPE,
71 Attribute13 wsh_picking_batches.Attribute13%TYPE,
72 Attribute14 wsh_picking_batches.Attribute14%TYPE,
73 Attribute15 wsh_picking_batches.Attribute15%TYPE,
74 Autodetail_Pr_Flag wsh_picking_batches.Autodetail_Pr_Flag%TYPE,
75 -- Bug#: 3266659 - Removing carrier params
76 -- Carrier_Id wsh_picking_batches.Carrier_Id%TYPE,
77 -- Carrier_Name wsh_carriers_v.carrier_name%TYPE,
78 Trip_Stop_Id wsh_picking_batches.Trip_Stop_Id%TYPE,
79 Trip_Stop_location_id wsh_trip_stops.Stop_Id%TYPE,
80 Default_Stage_Subinventory wsh_picking_batches.Default_Stage_Subinventory%TYPE,
81 Default_Stage_Locator_Id wsh_picking_batches.Default_Stage_Locator_Id%TYPE,
82 Pick_From_Subinventory wsh_picking_batches.Pick_From_Subinventory%TYPE,
83 Pick_From_locator_Id wsh_picking_batches.Pick_From_locator_Id%TYPE,
84 Auto_Pick_Confirm_Flag wsh_picking_batches.Auto_Pick_Confirm_Flag%TYPE,
85 Delivery_Detail_Id wsh_picking_batches.Delivery_Detail_Id%TYPE,
86 Project_Id wsh_picking_batches.Project_Id%TYPE,
87 Task_Id wsh_picking_batches.Task_Id%TYPE,
88 Organization_Id wsh_picking_batches.Organization_Id%TYPE,
89 Organization_Code org_organization_definitions.organization_code%TYPE,
90 Ship_Confirm_Rule_Id wsh_picking_batches.Ship_Confirm_Rule_Id%TYPE,
91 Ship_Confirm_Rule_Name wsh_Ship_Confirm_rules.name%TYPE,
92 Autopack_Flag wsh_picking_batches.Autopack_Flag%TYPE,
93 Autopack_Level wsh_picking_batches.Autopack_Level%TYPE,
94 Task_Planning_Flag wsh_picking_batches.Task_Planning_Flag%TYPE,
95 -- Non_Picking_flag wsh_picking_batches.Non_Picking_flag%TYPE,
96 Category_Set_ID wsh_picking_batches.Category_Set_ID%TYPE,
97 Category_ID wsh_picking_batches.Category_ID%TYPE,
98 Ship_Set_Smc_Flag wsh_picking_batches.Ship_Set_Smc_Flag%TYPE,
99 -- Bug#: 3266659 - Adding the columns like zone, region, delivery criteria, release
100 -- subinventory and append flag
101 region_ID wsh_picking_batches.region_id%TYPE,
102 zone_ID wsh_picking_batches.zone_id%TYPE,
103 ac_Delivery_Criteria wsh_picking_batches.ac_delivery_criteria%TYPE,
104 rel_subinventory wsh_picking_batches.rel_subinventory%TYPE,
105 append_flag wsh_picking_batches.append_flag%TYPE := 'N',
106 task_priority wsh_picking_batches.task_priority%TYPE,
107 actual_departure_date wsh_picking_batches.actual_departure_date%TYPE,
108 allocation_method wsh_picking_batches.allocation_method%TYPE, -- X-dock
109 crossdock_criteria_id wsh_picking_batches.crossdock_criteria_id%TYPE, -- X-dock
110 crossdock_criteria_name varchar2(80), -- X-dock
111 dynamic_replenishment_flag varchar2(1), --bug# 6689448 (replenishment project)
112 -- LSP PROJECT
113 client_id NUMBER,
114 client_code varchar2(10)
115 -- LSP PROJECT
116 );
117
118 --===================
119 -- PROCEDURES
120 --===================
121
122 -- Start of comments
123 --
124 -- API Name : Create_Batch
125 -- Type : Public
126 -- Purpose
127 -- Pre-reqs : None.
128 -- Function : The procedure takes in a rule_id / rule_name and brings up the default
129 -- values for a new batch to be created.
130 -- It then uses the information in the in parameter p_batch_rec and replaces the
131 -- values it picked up from the rule with the not null members of p_batch_rec
132 -- It then creates a new batch_id and inserts a new batch in the picking batch table
133 -- It will do some basic validations on the the input parameters
134 --
135 --
136 -- PARAMETERS : p_api_version_number known api version number
137 -- p_init_msg_list FND_API.G_TRUE to reset list
138 -- p_commit FND_API.G_TRUE to perform a commit
139 -- x_return_status return status
140 -- x_msg_count number of messages in the list
141 -- x_msg_data text of messages
142 -- p_rule_id Pick Release Rule Id --For Defaulting purpose
143 -- p_rule_name Pick Release Rule Name --For Defaulting purpose
144 -- p_batch_rec which contains all the Picking Batch parameters.
145 -- p_batch_prefix Which used to prefix for the Batch Name
146 -- i.e, Batch_Name becomes p_batch_prefix-batch_id
147 -- x_batch_id Returns the batch Id created
148 -- VERSION : current version 1.0
149 -- initial version 1.0
150 -- End of comments
151
152 /*#
153 * This procedure is used to create a picking batch. The procedure takes in a pick release
154 * rule_id / rule_name and brings up the default values for a new batch to be created.
155 * It then uses the information in the parameter p_batch_rec and replaces the values it
156 * picked up from the rule with the not null values of p_batch_rec. A new batch is
157 * inserted in the picking batch table.
158 * @param p_api_version version number of the API
159 * @param p_init_msg_list messages will be initialized, if set as true
160 * @param p_commit commits the transaction, if set as true
161 * @param x_return_status return status of the API
162 * @param x_msg_count number of messages, if any
163 * @param x_msg_data message text, if any
164 * @param p_rule_id pick release rule id for getting the defaulting rule to be used
165 * @param p_rule_name pick release rule name for getting the defaulting rule to be used
166 * @param p_batch_rec record structure for picking batch parameters.
167 * @param p_batch_prefix prefix for the batch name
168 * @param x_batch_id batch_id that is created by this procedure
169 * @rep:scope public
170 * @rep:lifecycle active
171 * @rep:displayname Create Picking Batch
172 */
173 PROCEDURE Create_Batch (
174 ----- Standard parameters
175 p_api_version IN NUMBER,
176 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
177 p_commit IN VARCHAR2 DEFAULT NULL,
178 x_return_status OUT NOCOPY VARCHAR2,
179 x_msg_count OUT NOCOPY NUMBER,
180 x_msg_data OUT NOCOPY VARCHAR2,
181
182 ---- Program specific paramters.
183 p_rule_id IN NUMBER DEFAULT NULL,
184 p_rule_name IN VARCHAR2 DEFAULT NULL,
185 p_batch_rec IN WSH_PICKING_BATCHES_PUB.Batch_Info_Rec,
186 p_batch_prefix IN VARCHAR2 DEFAULT NULL,
187 x_batch_id OUT NOCOPY NUMBER
188
189 );
190
191
192 -- Start of comments
193 --
194 -- API Name : Release_Batch
195 -- Type : Public
196 -- Purpose
197 -- Pre-reqs : None.
198 -- Function : The procedure takes in a Batch_id/ Batch_name and depending on the p_release_mode
199 -- value it process the batch. p_log_level value should be greator than 0 when
200 -- customer want to get the pick release log incase of concurrent pick release
201 --
202 -- It will do some basic validations on the the input parameters
203 -- like log_level should be positive ,and on the not null values of p_batch_rec.
204 --
205 -- PARAMETERS : p_api_version_number known api version number
206 -- p_init_msg_list FND_API.G_TRUE to reset list
207 -- p_commit FND_API.G_TRUE to perform a commit
208 -- x_return_status return status
209 -- x_msg_count number of messages in the list
210 -- x_msg_data text of messages
211 -- p_batch_id Picking Batch Id which is used to get Batch
212 -- information from the wsh_picking_batches table.
213 -- p_batch_name Picking Batch Name which is used to get Batch
214 -- information from the wsh_picking_batches table.
215 -- p_log_level Controlls the log message generated by cuncurrent
216 -- pick release process.
217 -- p_release_mode Used to do ONLINE or CONCURRENT pick release,
218 -- Default is "CONCURREN"
219 -- x_request_id Returns request Id for concurrent pick release request
220 -- VERSION : current version 1.0
221 -- initial version 1.0
222 -- End of comments
223
224 /*#
225 * This procedure is used to release a picking batch. The procedure takes in a pick release
226 * Batch_id/ Batch_name and depending on the pick release mode value it processes the batch.
227 * Log level value should be greater than 0 when the pick release log is needed.
228 * @param p_api_version version number of the API
229 * @param p_init_msg_list messages will be initialized, if set as true
230 * @param p_commit commits the transaction, if set as true
231 * @param x_return_status return status of the API
232 * @param x_msg_count number of messages, if any
233 * @param x_msg_data message text, if any
234 * @param p_batch_id Picking Batch Id to be released.
235 * @param p_batch_name Picking Batch Name to be released.
236 * @param p_log_level log level to control the log messages generated by concurrent pick release process
237 * @param p_release_mode pick release mode 'ONLINE' or 'CONCURRENT'
238 * @param x_request_id request id for concurrent pick release request
239 * @rep:scope public
240 * @rep:lifecycle active
241 * @rep:displayname Release Picking Batch
242 */
243 PROCEDURE Release_Batch (
244 -- Standard parameters
245 p_api_version IN NUMBER,
246 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
247 p_commit IN VARCHAR2 DEFAULT NULL,
248 x_return_status OUT NOCOPY VARCHAR2,
249 x_msg_count OUT NOCOPY NUMBER,
250 x_msg_data OUT NOCOPY VARCHAR2,
251 -- program specific paramters.
252 p_batch_id IN NUMBER DEFAULT NULL,
253 p_batch_name IN VARCHAR2 DEFAULT NULL,
254 p_log_level IN NUMBER DEFAULT NULL,
255 p_release_mode IN VARCHAR2 DEFAULT 'CONCURRENT',
256 x_request_id OUT NOCOPY NUMBER
257 ) ;
258
259
260
261 -- Start of comments
262 --
263 -- API Name : Get_Batch_Record
264 -- Type : Public
265 -- Purpose
266 -- Pre-reqs : None.
267 -- Function : The procedure takes in a Batch_id or Batch_name and retrieves
268 -- the batch record from wsh_picking_batches.
269 -- Note: Non-database attributes will be NULL.
270 --
271 -- PARAMETERS : p_api_version_number known api version number
272 -- p_init_msg_list FND_API.G_TRUE to reset list
273 -- p_commit FND_API.G_TRUE to perform a commit
274 -- x_return_status return status
275 -- x_msg_count number of messages in the list
276 -- x_msg_data text of messages
277 -- p_batch_id Picking Batch Id which is used to get Batch
278 -- information from the wsh_picking_batches table.
279 -- p_batch_name Picking Batch Name which is used to get Batch
280 -- information from the wsh_picking_batches table.
281 -- x_batch_rec Batch record from wsh_picking_batches
282 -- VERSION : current version 1.0
283 -- initial version 1.0
284 -- End of comments
285
286 /*#
287 * This procedure is used to get a picking batch record. The procedure takes in a pick release
288 * Batch_id/ Batch_name and retrieves the batch record from wsh_picking_batches.
289 * @param p_api_version version number of the API
290 * @param p_init_msg_list messages will be initialized, if set as true
291 * @param p_commit commits the transaction, if set as true
292 * @param x_return_status return status of the API
293 * @param x_msg_count number of messages, if any
294 * @param x_msg_data message text, if any
295 * @param p_batch_id Picking Batch Id used to get the picking batch information.
296 * @param p_batch_name Picking Batch Name used to get the picking batch information
297 * @param x_batch_rec Picking batch record
298 * @rep:scope public
299 * @rep:lifecycle active
300 * @rep:displayname Retrieve Picking Batch Record
301 */
302 PROCEDURE Get_Batch_Record(
303 -- Standard parameters
304 p_api_version IN NUMBER,
305 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
306 p_commit IN VARCHAR2 DEFAULT NULL,
307 x_return_status OUT NOCOPY VARCHAR2,
308 x_msg_count OUT NOCOPY NUMBER,
309 x_msg_data OUT NOCOPY VARCHAR2,
310 -- program specific paramters.
311 p_batch_id IN NUMBER DEFAULT NULL,
312 p_batch_name IN VARCHAR2 DEFAULT NULL,
313 x_batch_rec OUT NOCOPY WSH_PICKING_BATCHES_PUB.Batch_Info_Rec
314 ) ;
315
316
317
318 END WSH_PICKING_BATCHES_PUB;