1 PACKAGE WSH_PICKING_BATCHES_PUB AS
2 -- $Header: WSHPRPBS.pls 120.3 2007/12/21 14:24:46 mvudugul 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 );
113
114 --===================
115 -- PROCEDURES
116 --===================
117
118 -- Start of comments
119 --
120 -- API Name : Create_Batch
121 -- Type : Public
122 -- Purpose
123 -- Pre-reqs : None.
124 -- Function : The procedure takes in a rule_id / rule_name and brings up the default
125 -- values for a new batch to be created.
126 -- It then uses the information in the in parameter p_batch_rec and replaces the
127 -- values it picked up from the rule with the not null members of p_batch_rec
128 -- It then creates a new batch_id and inserts a new batch in the picking batch table
129 -- It will do some basic validations on the the input parameters
130 --
131 --
132 -- PARAMETERS : p_api_version_number known api version number
133 -- p_init_msg_list FND_API.G_TRUE to reset list
134 -- p_commit FND_API.G_TRUE to perform a commit
135 -- x_return_status return status
136 -- x_msg_count number of messages in the list
137 -- x_msg_data text of messages
138 -- p_rule_id Pick Release Rule Id --For Defaulting purpose
139 -- p_rule_name Pick Release Rule Name --For Defaulting purpose
140 -- p_batch_rec which contains all the Picking Batch parameters.
141 -- p_batch_prefix Which used to prefix for the Batch Name
142 -- i.e, Batch_Name becomes p_batch_prefix-batch_id
143 -- x_batch_id Returns the batch Id created
144 -- VERSION : current version 1.0
145 -- initial version 1.0
146 -- End of comments
147
148 /*#
149 * This procedure is used to create a picking batch. The procedure takes in a pick release
150 * rule_id / rule_name and brings up the default values for a new batch to be created.
151 * It then uses the information in the parameter p_batch_rec and replaces the values it
152 * picked up from the rule with the not null values of p_batch_rec. A new batch is
153 * inserted in the picking batch table.
154 * @param p_api_version version number of the API
155 * @param p_init_msg_list messages will be initialized, if set as true
156 * @param p_commit commits the transaction, if set as true
157 * @param x_return_status return status of the API
158 * @param x_msg_count number of messages, if any
159 * @param x_msg_data message text, if any
160 * @param p_rule_id pick release rule id for getting the defaulting rule to be used
161 * @param p_rule_name pick release rule name for getting the defaulting rule to be used
162 * @param p_batch_rec record structure for picking batch parameters.
163 * @param p_batch_prefix prefix for the batch name
164 * @param x_batch_id batch_id that is created by this procedure
165 * @rep:scope public
166 * @rep:lifecycle active
167 * @rep:displayname Create Picking Batch
168 */
169 PROCEDURE Create_Batch (
170 ----- Standard parameters
171 p_api_version IN NUMBER,
172 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
173 p_commit IN VARCHAR2 DEFAULT NULL,
174 x_return_status OUT NOCOPY VARCHAR2,
175 x_msg_count OUT NOCOPY NUMBER,
176 x_msg_data OUT NOCOPY VARCHAR2,
177
178 ---- Program specific paramters.
179 p_rule_id IN NUMBER DEFAULT NULL,
180 p_rule_name IN VARCHAR2 DEFAULT NULL,
181 p_batch_rec IN WSH_PICKING_BATCHES_PUB.Batch_Info_Rec,
182 p_batch_prefix IN VARCHAR2 DEFAULT NULL,
183 x_batch_id OUT NOCOPY NUMBER
184
185 );
186
187
188 -- Start of comments
189 --
190 -- API Name : Release_Batch
191 -- Type : Public
192 -- Purpose
193 -- Pre-reqs : None.
194 -- Function : The procedure takes in a Batch_id/ Batch_name and depending on the p_release_mode
195 -- value it process the batch. p_log_level value should be greator than 0 when
196 -- customer want to get the pick release log incase of concurrent pick release
197 --
198 -- It will do some basic validations on the the input parameters
202 -- p_init_msg_list FND_API.G_TRUE to reset list
199 -- like log_level should be positive ,and on the not null values of p_batch_rec.
200 --
201 -- PARAMETERS : p_api_version_number known api version number
203 -- p_commit FND_API.G_TRUE to perform a commit
204 -- x_return_status return status
205 -- x_msg_count number of messages in the list
206 -- x_msg_data text of messages
207 -- p_batch_id Picking Batch Id which is used to get Batch
208 -- information from the wsh_picking_batches table.
209 -- p_batch_name Picking Batch Name which is used to get Batch
210 -- information from the wsh_picking_batches table.
211 -- p_log_level Controlls the log message generated by cuncurrent
212 -- pick release process.
213 -- p_release_mode Used to do ONLINE or CONCURRENT pick release,
214 -- Default is "CONCURREN"
215 -- x_request_id Returns request Id for concurrent pick release request
216 -- VERSION : current version 1.0
217 -- initial version 1.0
218 -- End of comments
219
220 /*#
221 * This procedure is used to release a picking batch. The procedure takes in a pick release
222 * Batch_id/ Batch_name and depending on the pick release mode value it processes the batch.
223 * Log level value should be greater than 0 when the pick release log is needed.
224 * @param p_api_version version number of the API
225 * @param p_init_msg_list messages will be initialized, if set as true
226 * @param p_commit commits the transaction, if set as true
227 * @param x_return_status return status of the API
228 * @param x_msg_count number of messages, if any
229 * @param x_msg_data message text, if any
230 * @param p_batch_id Picking Batch Id to be released.
231 * @param p_batch_name Picking Batch Name to be released.
232 * @param p_log_level log level to control the log messages generated by concurrent pick release process
233 * @param p_release_mode pick release mode 'ONLINE' or 'CONCURRENT'
234 * @param x_request_id request id for concurrent pick release request
235 * @rep:scope public
236 * @rep:lifecycle active
237 * @rep:displayname Release Picking Batch
238 */
239 PROCEDURE Release_Batch (
240 -- Standard parameters
241 p_api_version IN NUMBER,
242 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
243 p_commit IN VARCHAR2 DEFAULT NULL,
244 x_return_status OUT NOCOPY VARCHAR2,
245 x_msg_count OUT NOCOPY NUMBER,
246 x_msg_data OUT NOCOPY VARCHAR2,
247 -- program specific paramters.
248 p_batch_id IN NUMBER DEFAULT NULL,
249 p_batch_name IN VARCHAR2 DEFAULT NULL,
250 p_log_level IN NUMBER DEFAULT NULL,
251 p_release_mode IN VARCHAR2 DEFAULT 'CONCURRENT',
252 x_request_id OUT NOCOPY NUMBER
253 ) ;
254
255
256
257 -- Start of comments
258 --
259 -- API Name : Get_Batch_Record
260 -- Type : Public
261 -- Purpose
262 -- Pre-reqs : None.
263 -- Function : The procedure takes in a Batch_id or Batch_name and retrieves
264 -- the batch record from wsh_picking_batches.
265 -- Note: Non-database attributes will be NULL.
266 --
267 -- PARAMETERS : p_api_version_number known api version number
268 -- p_init_msg_list FND_API.G_TRUE to reset list
269 -- p_commit FND_API.G_TRUE to perform a commit
270 -- x_return_status return status
271 -- x_msg_count number of messages in the list
272 -- x_msg_data text of messages
273 -- p_batch_id Picking Batch Id which is used to get Batch
274 -- information from the wsh_picking_batches table.
275 -- p_batch_name Picking Batch Name which is used to get Batch
276 -- information from the wsh_picking_batches table.
277 -- x_batch_rec Batch record from wsh_picking_batches
278 -- VERSION : current version 1.0
279 -- initial version 1.0
280 -- End of comments
281
282 /*#
283 * This procedure is used to get a picking batch record. The procedure takes in a pick release
284 * Batch_id/ Batch_name and retrieves the batch record from wsh_picking_batches.
285 * @param p_api_version version number of the API
286 * @param p_init_msg_list messages will be initialized, if set as true
287 * @param p_commit commits the transaction, if set as true
288 * @param x_return_status return status of the API
289 * @param x_msg_count number of messages, if any
290 * @param x_msg_data message text, if any
291 * @param p_batch_id Picking Batch Id used to get the picking batch information.
292 * @param p_batch_name Picking Batch Name used to get the picking batch information
293 * @param x_batch_rec Picking batch record
294 * @rep:scope public
295 * @rep:lifecycle active
296 * @rep:displayname Retrieve Picking Batch Record
297 */
298 PROCEDURE Get_Batch_Record(
299 -- Standard parameters
300 p_api_version IN NUMBER,
301 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
302 p_commit IN VARCHAR2 DEFAULT NULL,
303 x_return_status OUT NOCOPY VARCHAR2,
307 p_batch_id IN NUMBER DEFAULT NULL,
304 x_msg_count OUT NOCOPY NUMBER,
305 x_msg_data OUT NOCOPY VARCHAR2,
306 -- program specific paramters.
308 p_batch_name IN VARCHAR2 DEFAULT NULL,
309 x_batch_rec OUT NOCOPY WSH_PICKING_BATCHES_PUB.Batch_Info_Rec
310 ) ;
311
312
313
314 END WSH_PICKING_BATCHES_PUB;