1 PACKAGE WSH_CONTAINER_PUB AUTHID CURRENT_USER AS
2 /* $Header: WSHCOPBS.pls 120.0 2005/05/26 18:12:54 appldev noship $ */
3 /*#
4 * This package provides the APIs for execution of various container functions,
5 * including creation, updation of containers and other actions.
6 * @rep:scope public
7 * @rep:product WSH
8 * @rep:displayname Container
9 * @rep:lifecycle active
10 * @rep:category BUSINESS_ENTITY WSH_DELIVERY_LINE
11 * @rep:category BUSINESS_ENTITY WSH_DELIVERY
12 */
13
14 TYPE CONT_REC_TYPE IS RECORD
15 (delivery_detail_id NUMBER,
16 source_code VARCHAR2(30),
17 source_header_id NUMBER,
18 source_line_id NUMBER,
19 customer_id NUMBER,
20 sold_to_contact_id NUMBER,
21 inventory_item_id NUMBER,
22 item_description VARCHAR2(250),
23 hazard_class_id NUMBER,
24 country_of_origin VARCHAR2(150),
25 classification VARCHAR2(30),
26 ship_from_location_id NUMBER,
27 ship_to_location_id NUMBER,
28 ship_to_contact_id NUMBER,
29 deliver_to_location_id NUMBER,
30 deliver_to_contact_id NUMBER,
31 intmed_ship_to_location_id NUMBER,
32 intmed_ship_to_contact_id NUMBER,
33 hold_code VARCHAR2(1),
34 ship_tolerance_above NUMBER,
35 ship_tolerance_below NUMBER,
36 requested_quantity NUMBER,
37 shipped_quantity NUMBER,
38 delivered_quantity NUMBER,
39 requested_quantity_uom VARCHAR2(3),
40 subinventory VARCHAR2(10),
41 revision VARCHAR2(3),
42 -- HW OPMCONV. Need to expand length of lot_number to 80
43 lot_number VARCHAR2(80),
44 customer_requested_lot_flag VARCHAR2(1),
45 serial_number VARCHAR2(30),
46 locator_id NUMBER,
47 date_requested DATE,
48 date_scheduled DATE,
49 master_container_item_id NUMBER,
50 detail_container_item_id NUMBER,
51 load_seq_number NUMBER,
52 ship_method_code VARCHAR2(30),
53 carrier_id NUMBER,
54 freight_terms_code VARCHAR2(30),
55 shipment_priority_code VARCHAR2(30),
56 fob_code VARCHAR2(30),
57 customer_item_id NUMBER,
58 dep_plan_required_flag VARCHAR2(1),
59 customer_prod_seq VARCHAR2(50),
60 customer_dock_code VARCHAR2(30),
61 net_weight NUMBER,
62 weight_uom_code VARCHAR2(3),
63 volume NUMBER,
64 volume_uom_code VARCHAR2(3),
65 tp_attribute_category VARCHAR2(240),
66 tp_attribute1 VARCHAR2(240),
67 tp_attribute2 VARCHAR2(240),
68 tp_attribute3 VARCHAR2(240),
69 tp_attribute4 VARCHAR2(240),
70 tp_attribute5 VARCHAR2(240),
71 tp_attribute6 VARCHAR2(240),
72 tp_attribute7 VARCHAR2(240),
73 tp_attribute8 VARCHAR2(240),
74 tp_attribute9 VARCHAR2(240),
75 tp_attribute10 VARCHAR2(240),
76 tp_attribute11 VARCHAR2(240),
77 tp_attribute12 VARCHAR2(240),
78 tp_attribute13 VARCHAR2(240),
79 tp_attribute14 VARCHAR2(240),
80 tp_attribute15 VARCHAR2(240),
81 attribute_category VARCHAR2(150),
82 attribute1 VARCHAR2(150),
83 attribute2 VARCHAR2(150),
84 attribute3 VARCHAR2(150),
85 attribute4 VARCHAR2(150),
86 attribute5 VARCHAR2(150),
87 attribute6 VARCHAR2(150),
88 attribute7 VARCHAR2(150),
89 attribute8 VARCHAR2(150),
90 attribute9 VARCHAR2(150),
91 attribute10 VARCHAR2(150),
92 attribute11 VARCHAR2(150),
93 attribute12 VARCHAR2(150),
94 attribute13 VARCHAR2(150),
95 attribute14 VARCHAR2(150),
96 attribute15 VARCHAR2(150),
97 created_by NUMBER,
98 creation_date DATE,
99 last_update_date DATE,
100 last_update_login NUMBER,
101 last_updated_by NUMBER,
102 program_application_id NUMBER,
103 program_id NUMBER,
104 program_update_date DATE,
105 request_id NUMBER,
106 mvt_stat_status VARCHAR2(30),
107 released_flag VARCHAR2(1),
108 organization_id NUMBER,
109 transaction_temp_id NUMBER,
110 ship_set_id NUMBER,
111 arrival_set_id NUMBER,
112 ship_model_complete_flag VARCHAR2(1),
113 top_model_line_id NUMBER,
114 source_header_number VARCHAR2(150),
115 source_header_type_id NUMBER,
116 source_header_type_name VARCHAR2(240),
117 cust_po_number VARCHAR2(50),
118 ato_line_id NUMBER,
119 src_requested_quantity NUMBER,
120 src_requested_quantity_uom VARCHAR2(3),
121 move_order_line_id NUMBER,
122 cancelled_quantity NUMBER,
123 quality_control_quantity NUMBER,
124 cycle_count_quantity NUMBER,
125 tracking_number NUMBER,
126 movement_id NUMBER,
127 shipping_instructions VARCHAR2(2000),
128 packing_instructions VARCHAR2(2000),
129 project_id NUMBER,
130 task_id NUMBER,
131 org_id NUMBER,
132 oe_interfaced_flag VARCHAR2(1),
133 split_from_detail_id NUMBER,
134 inv_interfaced_flag VARCHAR2(1),
135 source_line_number VARCHAR2(150),
136 released_status VARCHAR2(1),
137 container_flag VARCHAR2(1),
138 container_type_code VARCHAR2(30),
139 container_name VARCHAR2(30),
140 fill_percent NUMBER,
141 gross_weight NUMBER,
142 master_serial_number VARCHAR2(30),
143 maximum_load_weight NUMBER,
144 maximum_volume NUMBER,
145 minimum_fill_percent NUMBER,
146 seal_code VARCHAR2(30),
147 unit_number VARCHAR2(30),
148 unit_price NUMBER,
149 currency_code VARCHAR2(15),
150 freight_class_cat_id NUMBER,
151 commodity_code_cat_id NUMBER ,
152 inspection_flag VARCHAR2(1)
153 );
154
155
156 ------------------------------------------------------------------------------
157 -- Procedure: Create_Containers
158 --
159 -- Parameters: 1) container_item_id (key flex id)
160 -- 2) container_item_name (concatinated name for container item)
161 -- 3) container_item_seg (flex field seg array for item name)
162 -- 4) organization_id - organization id for container
163 -- 5) organization_code - organization code for container
164 -- 6) name_prefix - container name prefix
165 -- 7) name_suffix - container name suffix
166 -- 8) base_number - starting number for numeric portion of name
167 -- 9) num_digits - precision for number of digits
168 -- 10) quantity - number of containers
169 -- 11) container_name - container name if creating 1 container
170 -- 12) table of container ids - out table of ids
171 -- 13) other standard parameters
172 --
173 -- Description: This procedure takes in a container item id or container item
174 -- name and other necessary parameters to create one or more containers and
175 -- creates the required containers. It returns a table of container instance
176 -- ids (delivery detail ids) along with the standard out parameters.
177 ------------------------------------------------------------------------------
178
179 /*#
180 * This procedure is used to create containers. More than one container can be created
181 * with a single procedure call by passing in the required parameters.
182
183 * @param p_api_version version number of the API
184 * @param p_init_msg_list messages will be initialized, if set as true
185 * @param p_commit commits the transaction, if set as true
189 * @param x_msg_data message text, if any
186 * @param p_validation_level validation level will be set as none if set as 0
187 * @param x_return_status return status of the API
188 * @param x_msg_count number of messages, if any
190 * @param p_container_item_id container inventory item id (key flex id)
191 * @param p_container_item_name container item name
192 * @param p_container_item_seg flex field seg array for item name
193 * @param p_organization_id organization id for container
194 * @param p_organization_code organization code for container
195 * @param p_name_prefix container name prefix
196 * @param p_name_suffix container name suffix
197 * @param p_base_number starting number for numeric portion of name
198 * @param p_num_digits precision for number of digits
199 * @param p_quantity number of containers
200 * @param p_container_name container name if creating 1 container
201 * @param x_container_ids output table of container ids (delivery detail ids) created
202 * @rep:scope public
203 * @rep:lifecycle active
204 * @rep:displayname Create Containers
205 */
206 PROCEDURE Create_Containers (
207 -- Standard parameters
208 p_api_version IN NUMBER,
209 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
210 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
211 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
212 x_return_status OUT NOCOPY VARCHAR2,
213 x_msg_count OUT NOCOPY NUMBER,
214 x_msg_data OUT NOCOPY VARCHAR2,
215
216 -- program specific parameters
217 p_container_item_id IN NUMBER,
218 p_container_item_name IN VARCHAR2,
219 p_container_item_seg IN FND_FLEX_EXT.SegmentArray,
220 p_organization_id IN NUMBER,
221 p_organization_code IN VARCHAR2,
222 p_name_prefix IN VARCHAR2,
223 p_name_suffix IN VARCHAR2,
224 p_base_number IN NUMBER,
225 p_num_digits IN NUMBER,
226 p_quantity IN NUMBER,
227 p_container_name IN VARCHAR2,
228
229 -- program specific out parameters
230 x_container_ids OUT NOCOPY WSH_UTIL_CORE.ID_TAB_TYPE,
231 p_ucc_128_suffix_flag IN NUMBER DEFAULT 2
232 );
233
234
235 ------------------------------------------------------------------------------
236 -- Procedure: Update_Container
237 --
238 -- Parameters: 1) container_rec - container record of type
239 -- wsh_delivery_details_pub.changedattributerectype
240 -- 2) other standard parameters
241 --
242 -- Description: This procedure takes in a record of container attributes that
243 -- contains the name and delivery detail id of container to update the
244 -- container record in WSH_DELIVERY_DETAILS with the attributes input in the
245 -- container rec type. The API validates the container name and detail id and
246 -- calls the wsh_delivery_details_pub.update_shipping_attributes public API.
247 ------------------------------------------------------------------------------
248 /*#
249 * This procedure is used to update a container. A record of container
250 * attributes can be passed in to update the container record.
251 * @param p_api_version version number of the API
252 * @param p_init_msg_list messages will be initialized, if set as true
253 * @param p_commit commits the transaction, if set as true
254 * @param p_validation_level validation level will be set as none if set as 0
255 * @param x_return_status return status of the API
256 * @param x_msg_count number of messages, if any
257 * @param x_msg_data message text, if any
258 * @param p_container_rec record of container attributes to be updated
259 * @rep:scope public
260 * @rep:lifecycle active
261 * @rep:displayname Update Container
262 */
263 PROCEDURE Update_Container (
264 -- Standard parameters
265 p_api_version IN NUMBER,
266 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
267 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
268 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
269 x_return_status OUT NOCOPY VARCHAR2,
270 x_msg_count OUT NOCOPY NUMBER,
271 x_msg_data OUT NOCOPY VARCHAR2,
272
273 -- program specific parameters
274 p_container_rec IN WSH_DELIVERY_DETAILS_PUB.CHANGEDATTRIBUTERECTYPE
275
276 );
277
278
279 ------------------------------------------------------------------------------
280 -- Procedure: Auto_Pack
281 --
282 -- Parameters: 1) entity_tab - table of ids of either lines or containers or
283 -- deliveries that need to be autopacked
284 -- 2) entity_type - type of entity id contained in the entity_tab
285 -- that needs to be autopacked ('L' - lines,
286 -- 'C' - containers OR 'D' - deliveries)
287 -- 3) group_id_tab - table of ids (numbers that determine
288 -- the grouping of lines for packing into containers)
289 -- 4) container_instance_tab - table of delivery detail ids of
290 -- containers that are created during the autopacking
291 -- 5) pack cont flag - a 'Y' or 'N' value to determine whether to
292 -- to autopack the detail containers that are created into
293 -- parent containers.
294 -- 6) other standard parameters
295 --
296 -- Description: This procedure takes in a table of ids of either delivery lines
297 -- or container or deliveries and autopacks the lines/containers/deliveries
298 -- into detail containers. The grouping id table is used only if the input
299 -- table of entities are lines or containers only. The packing of lines and
300 -- containers into parent containers is determined by the grouping id for each
301 -- line/container. If the grouping id table is not input, the API determines
302 -- the grouping ids for the lines/containers based on the grouping attributes
303 -- of the lines/containers. The lines/containers are then autopacked into
304 -- detail containers and the detail containers are packed into parent/master
305 -- containers based on whether the pack cont flag is set to 'Y' or 'N'. The
306 -- API returns a table of container instance ids created during the autopacking
307 -- operation. If the detail containers are packed into parent containers, the
308 -- output table of ids will contain both the detail and parent containers'
309 -- delivery detail ids.
310 ------------------------------------------------------------------------------
311 /*#
312 * This procedure takes in a table of ids of either delivery lines
313 * or container or deliveries and autopacks the lines/containers/deliveries
314 * into detail containers. The grouping id table is used only if the input
315 * table of entities are lines or containers. The packing of lines and
316 * containers into parent containers is determined by the grouping id for each
317 * line/container. If the grouping id table is not input, the API determines
318 * the grouping ids for the lines/containers based on the grouping attributes
319 * of the lines/containers. The lines/containers are then autopacked into
320 * detail containers and the detail containers are packed into parent/master
321 * containers based on whether the pack cont flag is set to 'Y' or 'N'. The
322 * API returns a table of container instance ids created during the autopacking
323 * operation. If the detail containers are packed into parent containers, the
324 * output table of ids will contain both the detail and parent containers'
325 * delivery detail ids.
326 * @param p_api_version version number of the API
327 * @param p_init_msg_list messages will be initialized, if set as true
328 * @param p_commit commits the transaction, if set as true
329 * @param p_validation_level validation level will be set as none if set as 0
330 * @param x_return_status return status of the API
331 * @param x_msg_count number of messages, if any
332 * @param x_msg_data message text, if any
333 * @param p_entity_tab table of ids of either lines or containers or deliveries that need to be autopacked
334 * @param p_entity_type type of entity - 'L' for lines, 'C' for containers OR 'D' for deliveries
335 * @param p_group_id_tab table of ids that determine the grouping of lines
336 * @param p_pack_cont_flag 'Y' or 'N' value whether to autopack the detail containers that are created
337 * @param x_cont_inst_tab output table of ids that contains both detail and parent containers delivery detail ids.
338 * @rep:scope public
339 * @rep:lifecycle active
340 * @rep:displayname Autopack Lines/Containers/Deliveries
341 */
342 PROCEDURE Auto_Pack (
343 -- Standard parameters
344 p_api_version IN NUMBER,
345 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
346 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
347 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
348 x_return_status OUT NOCOPY VARCHAR2,
349 x_msg_count OUT NOCOPY NUMBER,
350 x_msg_data OUT NOCOPY VARCHAR2,
351
352 -- program specific parameters
353 p_entity_tab IN WSH_UTIL_CORE.ID_TAB_TYPE,
354 p_entity_type IN VARCHAR2,
355 p_group_id_tab IN WSH_UTIL_CORE.ID_TAB_TYPE,
356 p_pack_cont_flag IN VARCHAR2,
357
358 -- program specific out parameters
359 x_cont_inst_tab OUT NOCOPY WSH_UTIL_CORE.ID_TAB_TYPE
360
361 );
362
363
364
365 ------------------------------------------------------------------------------
366 -- Procedure: Container_Actions
367 --
368 -- Parameters: 1) detail_tab - input table of delivery detail ids
369 -- 2) container_instance_id - delivery detail id of parent
370 -- container that is being packed.
371 -- 3) container_name - container name if id is not known
372 -- 4) container_flag - 'Y' or 'N' depending on whether to unpack
373 -- or not. ('Y' is unpack)
374 -- 5) delivery_flag - 'Y' or 'N' if container needs to be
375 -- unassigned from delivery. ('Y' if unassign from del)
376 -- 6) delivery_id - delivery id to assign container to.
377 -- 7) delivery_name - name of delivery that container is being
378 -- assigned to.
379 -- 8) action_code - action code 'Pack', 'Assign', 'Unpack' or
380 -- 'Unassign' to specify what action to perform.
381 -- 9) other standard parameters
382 --
383 -- Description: This procedure takes in a table of delivery detail ids and
384 -- name and/or delivery detail id of container to pack. If the action code is
385 -- is assign then delivery id and delivery name must be specified. The API
386 -- determines what action to perform based on the action code and then calls
387 -- appropriate private pack/assign/unpack/unassign API.
388 -- The input table of ids could be lines or containers. The delivery lines and
389 -- containers are separated from the input table and validated before the
390 -- appropriate private APIs are called
391 ------------------------------------------------------------------------------
392 /*#
393 * Procedure for performing the container actions such as Pack, Assign,
394 * Unpack, Unassign based on the action code passed in. The input table
395 * of ids could be lines or containers.
396 * @param p_api_version version number of the API
397 * @param p_init_msg_list messages will be initialized, if set as true
398 * @param p_commit commits the transaction, if set as true
399 * @param p_validation_level validation level will be set as none if set as 0
400 * @param x_return_status return status of the API
401 * @param x_msg_count number of messages, if any
402 * @param x_msg_data message text, if any
403 * @param p_detail_tab input table of delivery detail ids
404 * @param p_container_name container name if id is not known
405 * @param p_cont_instance_id delivery detail id of parent container to pack the details/containers in
406 * @param p_container_flag value should be 'Y' if unpack also needs to be performed
407 * @param p_delivery_flag value should be 'Y' if container needs to be unassigned from delivery else should be 'N'
408 * @param p_delivery_id delivery id to which the container is to be assigned
409 * @param p_delivery_name delivery name to which the container is to be assigned
410 * @param p_action_code action code to specify what action to perform.
411 * @rep:scope public
412 * @rep:lifecycle active
413 * @rep:displayname Container Actions
414 */
415 PROCEDURE Container_Actions (
416 -- Standard parameters
417 p_api_version IN NUMBER,
418 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
419 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
420 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
421 x_return_status OUT NOCOPY VARCHAR2,
422 x_msg_count OUT NOCOPY NUMBER,
423 x_msg_data OUT NOCOPY VARCHAR2,
424
425 -- program specific parameters
426 p_detail_tab IN WSH_UTIL_CORE.ID_TAB_TYPE,
427 p_container_name IN VARCHAR2 DEFAULT NULL,
428 p_cont_instance_id IN NUMBER DEFAULT NULL,
429 p_container_flag IN VARCHAR2 DEFAULT 'N',
430 p_delivery_flag IN VARCHAR2 DEFAULT 'N',
431 p_delivery_id IN NUMBER DEFAULT NULL,
432 p_delivery_name IN VARCHAR2 DEFAULT NULL,
433 p_action_code IN VARCHAR2
434
435 );
436
437
438 END WSH_CONTAINER_PUB;