1 PACKAGE WSH_CONTAINER_UTILITIES as
2 /* $Header: WSHCMUTS.pls 120.0 2005/05/26 18:05:51 appldev noship $ */
3
4 --<TPA_PUBLIC_NAME=WSH_TPA_CONTAINER_PKG>
5 --<TPA_PUBLIC_FILE_NAME=WSHTPCO>
6
7 -- bug 2381184
8 -- Record Structures
9 -- This is used with estimate detail container api
10 -- with new signature
11 TYPE inrectype
12 IS
13 RECORD
14 (
15 container_instance_id NUMBER,
16 delivery_detail_id NUMBER,
17 organization_id NUMBER
18 );
19 --
20 TYPE outrectype
21 IS
22 RECORD
23 (
24 num_cont NUMBER,
25 max_qty_per_lpn NUMBER,
26 fill_pc_per_lpn NUMBER,
27 fill_pc_flag VARCHAR2(1),
28 indivisible_flag VARCHAR2(1)
29 );
30 --
31 TYPE inoutrectype
32 IS
33 RECORD
34 (
35 container_item_id NUMBER
36 );
37 --
38 -- end bug 2381184
39 /*
40 -----------------------------------------------------------------------------
41 FUNCTION : Get Master Cont Id
42 PARAMETERS : p_container_instance_id - instance id for the container
43 RETURNS : master container instance id
44 DESCRIPTION : This function derives the master container instance id
45 of the container by using a heirarchical SQL query on
46 wsh_delivery_assignments_v table. This function can be used in
47 SELECT statements that need to use the master container id.
48 ------------------------------------------------------------------------------
49 */
50
51 FUNCTION Get_Master_Cont_Id (p_cont_instance_id IN NUMBER) RETURN NUMBER;
52
53 -- The following pragma is used to allow Get_Master_Cont_id to be used
54 -- in a select statement
55 -- WNDS : Write No Database State (does not allow tables to be altered)
56
57 pragma restrict_references (Get_Master_Cont_Id, WNDS);
58
59
60 /*
61 -----------------------------------------------------------------------------
62 FUNCTION : Get Cont Name
63 PARAMETERS : p_cont_instance_id - instance id for the container
64 RETURNS : container name for the container instance id
65 DESCRIPTION : This function derives the container name for the container id
66
67 ------------------------------------------------------------------------------
68 */
69
70
71 FUNCTION Get_Cont_Name (p_cont_instance_id IN NUMBER) RETURN VARCHAR2;
72
73 -- The following pragma is used to allow Get_Cont_Name to be used
74 -- in a select statement
75 -- WNDS : Write No Database State (does not allow tables to be altered)
76
77 pragma restrict_references (Get_Cont_Name, WNDS);
78
79 -- Bug 2381184
80 -- Note There are 2 API with estimate_detail_containers name
81 /*
82 -----------------------------------------------------------------------------
83 PROCEDURE : Estimate Detail Containers
84 PARAMETERS : p_container_instance_id - instance id for the container
85 x_container_item_id - container item for estimation
86 p_delivery_detail_id - the delivery detail id for which the
87 number of containers is being estimated
88 p_organization_id - organization_id
89 x_num_cont - number of containers required to pack the line.
90 x_return_status - return status of API
91 DESCRIPTION : This procedure estimates the number of detail containers that
92 would be required to pack a delivery detail. The container
93 item could be specified or if it is not specified, it is
94 derived from the delivery detail or through the container load
95 relationship. Using the inventory item and quantity on the
96 detail and the container item, the number of containers is
97 calculated/estimated.
98 ------------------------------------------------------------------------------
99 */
100 PROCEDURE Estimate_Detail_Containers(
101 p_in_record IN inrectype,
102 x_inout_record IN OUT NOCOPY inoutrectype,
103 x_out_record OUT NOCOPY outrectype,
104 x_return_status OUT NOCOPY VARCHAR2
105 );
106
107 /*
108 -----------------------------------------------------------------------------
109 PROCEDURE : Estimate Detail Containers
110 PARAMETERS : p_container_instance_id - instance id for the container
111 x_container_item_id - container item for estimation
112 p_delivery_detail_id - the delivery detail id for which the
113 number of containers is being estimated
114 p_organization_id - organization_id
115 x_num_cont - number of containers required to pack the line.
116 x_return_status - return status of API
117 DESCRIPTION : This procedure estimates the number of detail containers that
118 would be required to pack a delivery detail. The container
119 item could be specified or if it is not specified, it is
120 derived from the delivery detail or through the container load
121 relationship. Using the inventory item and quantity on the
122 detail and the container item, the number of containers is
123 calculated/estimated.
124 FOR TPA SELECTOR USE: wsh_tpa_selector_pkg.containerTP
125 ------------------------------------------------------------------------------
126 */
127
128
129 PROCEDURE Estimate_Detail_Containers(
130 p_container_instance_id IN NUMBER DEFAULT NULL,
131 x_container_item_id IN OUT NOCOPY NUMBER,
132 p_delivery_detail_id IN NUMBER,
133 p_organization_id IN NUMBER,
134 x_num_cont IN OUT NOCOPY NUMBER,
135 x_return_status OUT NOCOPY VARCHAR2);
136
137 --<TPA_PUBLIC_NAME>
138 --<TPA_DEFAULT_TPS=WSH_TPA_SELECTOR_PKG.DeliveryDetailTP>
139
140 /*
141 -----------------------------------------------------------------------------
142 PROCEDURE : Estimate Master Containers
143 PARAMETERS : p_container_instance_id - instance id of the detail container
144 x_mast_cont_item_id - master container item id
145 p_det_cont_item_id - detail container item id
146 p_organization_id - organization_id
147 x_num_cont - number of master containers required to pack
148 the detail containers.
149 x_return_status - return status of API
150 DESCRIPTION : This procedure estimates the number of master containers that
151 would be required to pack a number of detail containers. The
152 master container item could be specified or if it is not
153 specified, it is derived from the container load relationship.
154 Using the detail container item id and the derived master
155 container item id the number of master containers is
156 calculated/estimated.
157 FOR TPA SELECTOR USE: wsh_tpa_selector_pkg.containerTP
158 ------------------------------------------------------------------------------
159 */
160
161
162 PROCEDURE Estimate_Master_Containers(
163 p_container_instance_id IN NUMBER,
164 x_mast_cont_item_id IN OUT NOCOPY NUMBER,
165 p_det_cont_item_id IN NUMBER,
166 p_organization_id IN NUMBER,
167 x_num_cont IN OUT NOCOPY NUMBER,
168 x_return_status OUT NOCOPY VARCHAR2);
169
170 --<TPA_PUBLIC_NAME>
171 --<TPA_DEFAULT_TPS=WSH_TPA_SELECTOR_PKG.ContainerTP>
172
173 /*
174 -----------------------------------------------------------------------------
175 PROCEDURE : Get Master Cont Serial
176 PARAMETERS : p_container_instance_id - instance id for the container
177 x_master_container_id - the master container of the container
178 derived using the container hierarchy.
179 x_master_container_name - container name for the master
180 container.
181 x_master_serial_number - serial number of the master container
182 derived using the container hierarchy.
183 x_return_status - return status of API
184 DESCRIPTION : This procedure derives the master container instance id and
185 master serial number of the container. The master serial
186 number and master container instance id is derived from the
187 container instance table using the container heirarchy.
188 ------------------------------------------------------------------------------
189 */
190
191
192 PROCEDURE Get_Master_Cont_Serial (
193 p_container_instance_id IN NUMBER,
194 x_master_container_id IN OUT NOCOPY NUMBER,
195 x_master_container_name IN OUT NOCOPY VARCHAR2,
196 x_master_serial_number IN OUT NOCOPY VARCHAR2,
197 x_return_status OUT NOCOPY VARCHAR2);
198
199
200 /*
201 -----------------------------------------------------------------------------
202 PROCEDURE : Update Child Containers
203 PARAMETERS : p_container_instance_id - instance id for the container
204 x_master_cont_instance_id - master container of the container
205 x_master_serial_number - serial number of the master container
206 x_return_status - return status of API
207 DESCRIPTION : This procedure updates the master container instance id and
208 master serial number of all the child containers. When the
209 master serial number and master container instance id is
210 changed on the master container, all the child containers are
211 updated with the new values using this API.
212 ------------------------------------------------------------------------------
213 */
214
215
216
217 PROCEDURE Update_Child_Containers (
218 p_container_instance_id IN NUMBER,
219 p_master_cont_instance_id IN NUMBER,
220 p_master_serial_number IN VARCHAR2,
221 x_return_status OUT NOCOPY VARCHAR2);
222
223
224 /*
225 -----------------------------------------------------------------------------
226 PROCEDURE : Validate Master Serial Number
227 PARAMETERS : p_container_instance_id - instance id for the container
228 p_master_serial_number - serial number of the master container
229 x_return_status - return status of API
230 DESCRIPTION : This is a dummy procedure created to help customers create
231 a customizable validation API for the master serial number. It
232 currently returns success for all cases.
233 FOR TPA SELECTOR USE: wsh_tpa_selector_pkg.containerTP
234 ------------------------------------------------------------------------------
235 */
236
237
238 PROCEDURE Validate_Master_Serial_Number (
239 p_master_serial_number IN VARCHAR2,
240 p_container_instance_id IN NUMBER,
241 x_return_status OUT NOCOPY VARCHAR2);
242
243 --<TPA_PUBLIC_NAME>
244 --<TPA_DEFAULT_TPS=WSH_TPA_SELECTOR_PKG.ContainerTP>
245
246 /*
247 -----------------------------------------------------------------------------
248 PROCEDURE : Get Master Serial Number
249 PARAMETERS : p_container_instance_id - instance id for the container
250 x_master_serial_number - serial number of the master container
251 x_return_status - return status of API
252 DESCRIPTION : This procedure retrieves the master serial number for a
253 container by getting the serial number of the master container
254 in the container heirarchy.
255 ------------------------------------------------------------------------------
256 */
257
258
259 PROCEDURE Get_Master_Serial_Number (
260 p_container_instance_id IN NUMBER,
261 x_master_serial_number IN OUT NOCOPY VARCHAR2,
262 x_return_status OUT NOCOPY VARCHAR2);
263
264
265 /*
266 -----------------------------------------------------------------------------
267 PROCEDURE : Is Empty
268 PARAMETERS : p_container_instance_id - instance id for the container
269 x_empty_flag - flag to return empty or non-empty
270 x_return_status - return status of API
271 DESCRIPTION : This procedure checks the container to see if there are any
272 lines packed in the container. If there are no lines it returns
273 a true flag to indicate that it is empty.
274 ------------------------------------------------------------------------------
275 */
276
277
278 PROCEDURE Is_Empty (
279 p_container_instance_id IN NUMBER,
280 x_empty_flag IN OUT NOCOPY BOOLEAN,
281 x_return_status OUT NOCOPY VARCHAR2);
282
283
284 /*
285 -----------------------------------------------------------------------------
286 PROCEDURE : Is Empty
287 PARAMETERS : p_container_instance_id - instance id for the container
288 x_empty_flag - flag to return empty or non-empty
289 x_return_status - return status of API
290 DESCRIPTION : This procedure checks the container to see if there are any
291 lines packed in the container. If there are no lines it returns
292 a 'Y' flag to indicate that it is empty.
293 If C1 contains C2 and C3. C2 has C4 which is empty , but C3 has a ddid
294 Based on this API, C1 is not empty, but C2 ind C4 are empty.
295 ------------------------------------------------------------------------------
296 */
297
298 PROCEDURE Is_Empty (
299 p_container_instance_id IN NUMBER,
300 x_empty_flag OUT NOCOPY VARCHAR2,
301 x_return_status OUT NOCOPY VARCHAR2);
302
303 /*
304 -----------------------------------------------------------------------------
305 PROCEDURE : Get Fill Percent
306 PARAMETERS : p_container_instance_id - instance id for the container
307 x_percent_fill - percent fill of the container
308 x_return_status - return status of API
309 DESCRIPTION : This procedure retrieves the percent fill of the container
310 from the container instances table. If the percent fill is
311 null, it recalculates the percent fill for the container.
312 ------------------------------------------------------------------------------
313 */
314
315
316 PROCEDURE Get_Fill_Percent (
317 p_container_instance_id IN NUMBER,
318 x_percent_fill OUT NOCOPY NUMBER,
319 x_return_status OUT NOCOPY VARCHAR2);
320
321
322 /*
323 -----------------------------------------------------------------------------
324 PROCEDURE : Get Delivery Status
325 PARAMETERS : p_container_instance_id - instance id for the container
326 x_delivery_id - delivery id the container is assigned to
327 x_del_status - status of delivery that the container is
328 assigned to.
329 x_return_status - return status of API
330 DESCRIPTION : This procedure retrieves the delivery id and delivery status
331 of the delivery that the container is assigned to.
332 ------------------------------------------------------------------------------
333 */
334
335
336 PROCEDURE Get_Delivery_Status (
337 p_container_instance_id IN NUMBER,
338 x_delivery_id IN OUT NOCOPY NUMBER,
339 x_del_status IN OUT NOCOPY VARCHAR2,
340 x_return_status OUT NOCOPY VARCHAR2);
341
342
343
344 /*
345 -----------------------------------------------------------------------------
346 PROCEDURE : Validate_Hold_Code
347 PARAMETERS : p_delivery_detail_id - delivery detail id
348 x_return_status - return status of API
349 DESCRIPTION : This procedure retrieves the hold code for the delivery detail
350 id and returns a success if there is no hold code and returns
351 an error if there is any invalid hold code.
352 ------------------------------------------------------------------------------
353 */
354
355
356 PROCEDURE Validate_Hold_Code (
357 p_delivery_detail_id IN NUMBER,
358 x_return_status OUT NOCOPY VARCHAR2);
359
360
361 /*
362 -----------------------------------------------------------------------------
363 PROCEDURE : Validate_Hazard_Class
364 PARAMETERS : p_delivery_detail_id - delivery detail id
365 p_container_instance_id - delivery detail id of container
366 x_return_status - return status of API
367 DESCRIPTION : This procedure retrieves the hazard class id of the delivery
368 detail id and checks if there is any incompatability or
369 special restrictions on packing the detail into the specified
370 container. Also checks to see if the hazard class for the
371 detail is incompatible with the other details already in the
372 container. It returns a success if there are no restrictions
373 and returns an error if there is any invalid hazard class.
374 ------------------------------------------------------------------------------
375 */
376
377
378 PROCEDURE Validate_Hazard_Class (
382
379 p_delivery_detail_id IN NUMBER,
380 p_container_instance_id IN NUMBER,
381 x_return_status OUT NOCOPY VARCHAR2);
383
384 /*
385 -----------------------------------------------------------------------------
386 PROCEDURE : Validate_Container
387 PARAMETERS : p_container_name - container name that needs to be validated.
388 p_container_instance_id - the delivery detail id for the
389 container that needs to be updated.
390 x_return_status - return status of API
391 DESCRIPTION : This procedure takes in the container name and existing
392 container id (detail id) and checks to see if the container
393 that is being updated is assigned to a closed, confirmed or
394 in-transit delivery. If it is, no update is allowed - if not,
395 only the container name can be updated if the name is not a
396 duplicate of an existing container.
397 ------------------------------------------------------------------------------
398 */
399
400
401 PROCEDURE Validate_Container (
402 p_container_name IN VARCHAR2,
403 p_container_instance_id IN NUMBER,
404 x_return_status OUT NOCOPY VARCHAR2);
405
406
407 END WSH_CONTAINER_UTILITIES;
[Home] [Help]