1 PACKAGE WSH_DETAILS_VALIDATIONS as
2 /* $Header: WSHDDVLS.pls 120.4.12010000.1 2008/07/29 05:59:50 appldev ship $ */
3
4
5 TYPE MinMaxInRecType is RECORD (
6 api_version_number NUMBER DEFAULT 1.0,
7 source_code WSH_DELIVERY_DETAILS.SOURCE_CODE%TYPE, -- mandatory parameter
8 line_id NUMBER, -- source_line_id
9 source_header_id NUMBER, -- Bug 2181132 new field
10 source_line_set_id NUMBER,
11 ship_tolerance_above NUMBER,
12 ship_tolerance_below NUMBER,
13 action_flag VARCHAR2(1) DEFAULT 'P',
14 lock_flag VARCHAR2(1) DEFAULT 'N',
15 quantity_uom VARCHAR2(3),
16 quantity_uom2 VARCHAR2(3)
17 );
18
19 TYPE MinMaxOutRecType is RECORD (
20 quantity_uom VARCHAR2(3),
21 min_remaining_quantity NUMBER,
22 max_remaining_quantity NUMBER,
23 quantity2_uom VARCHAR2(3),
24 min_remaining_quantity2 NUMBER,
25 max_remaining_quantity2 NUMBER
26 );
27
28 TYPE MinMaxInOutRecType is RECORD (
29 dummy_quantity NUMBER
30 );
31
32 -- Bug 2181132
33 -- change p_out_attributes to x_out_attributes
34 PROCEDURE Get_Min_Max_Tolerance_Quantity
35 ( p_in_attributes IN MinMaxInRecType,
36 x_out_attributes OUT NOCOPY MinMaxOutRecType,
37 p_inout_attributes IN OUT NOCOPY MinMaxInOutRecType,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2
41 );
42
43
44 -- Procedure: check_unassign_from_delivery .
45 -- Parameters: p_detailrows
46 -- x_return_status
47 -- Description: This procedure checks if the list of delivery details
48 -- can be unassigned from the delivery they are unassigned to.
49
50 PROCEDURE check_unassign_from_delivery(
51 p_detail_rows IN wsh_util_core.id_tab_type,
52 x_return_status OUT NOCOPY VARCHAR2) ;
53
54 --
55 -- Function: serial_num_ctl_req
56 -- Parameters: p_inventory_item_id
57 -- p_org_id
58 -- Description: This function returns a boolean value to
59 -- indicate if the inventory item in that org is
60 -- requires a serial number or not
61 --
62
63
64 FUNCTION serial_num_ctl_req(p_inventory_item_id number, p_org_id number) return BOOLEAN;
65
66 -----------------------------------------------------------------------------
67 --
68 -- FUNCTION: Trx_ID
69 -- Parameters: p_mode, p_source_line_id, p_source_document_type_id
70 -- Returns: number
71 -- Trx_ID: It reurns the trx_id depending on the given mode, source
72 -- line id and source_document_type_id
73 -----------------------------------------------------------------------------
74
75 FUNCTION trx_id(
76 p_mode varchar2,
77 p_source_line_id number,
78 p_source_document_type_id number) return number;
79
80 -- 2467416
81 PROCEDURE purge_crd_chk_tab;
82
83 --
84 -- Procedure: Check_Shipped_Quantity
85 -- Parameters: p_ship_above_tolerance number,
86 -- p_requested_quantity number,
87 -- p_picked_quantity number,
88 -- p_shipped_quantity number,
89 -- p_cycle_count_quantity number,
90 -- x_return_status OUT VARCHAR2
91 -- Description: This procedure validates the entered shipped quantity
92
93 PROCEDURE check_shipped_quantity(
94 p_ship_above_tolerance IN number,
95 p_requested_quantity IN number,
96 p_picked_quantity IN NUMBER,
97 p_shipped_quantity IN number,
98 p_cycle_count_quantity IN number,
99 x_return_status OUT NOCOPY VARCHAR2);
100
101 --
102 -- Procedure: Check_Cycle_Count_Quantity
103 -- Parameters: p_ship_above_tolerance number,
104 -- p_requested_quantity number,
105 -- p_picked_quantity number,
106 -- p_shipped_quantity number,
107 -- p_cycle_count_quantity number,
108 -- x_return_status OUT VARCHAR2
109 -- Description: This procedure validates the entered cycle count quantity
110
111 PROCEDURE check_cycle_count_quantity(
112 p_ship_above_tolerance IN number,
113 p_requested_quantity IN number,
114 p_picked_quantity IN NUMBER,
115 p_shipped_quantity IN number,
116 p_cycle_count_quantity IN number,
117 x_return_status OUT NOCOPY VARCHAR2);
118
119 /* Validates and returns the quantity in this manner (the caller does not need
120 to adjust the result):
121 This routine checks to make sure that the input quantity precision does
122 not exceed the decimal precision. Max Precision is: 10 digits before the
123 decimall point and 9 digits after the decimal point.
124 The routine also makes sure that if the item is serial number controlled,
125 the the quantity in primary UOM is an integer number.
126 The routine also makes sure that if the item's indivisible_flag is set
127 to yes, then the item quantity is an integer in the primary UOM
128 The routine also checks if the profile, INV:DETECT TRUNCATION, is set
129 to yes, the item quantity in primary UOM also obeys max precision and that
130 it is not zero.
131 The procedure retruns a correct output quantity in the transaction UOM,
132 returns the primary quantity and returns a status of success, failure, or
133 warning */
134 PROCEDURE check_decimal_quantity(
135 p_item_id number,
136 p_organization_id number,
137 p_input_quantity number,
138 p_uom_code varchar2,
139 x_output_quantity out NOCOPY number,
140 x_return_status out NOCOPY varchar2 ,
141 p_top_model_line_id number default NULL,
142 p_max_decimal_digits IN NUMBER DEFAULT NULL );
143 -- Bug 1890220 : Added p_top_model_line_id at the end so that it works even if its called
144 -- without the parameter
145
146 -----------------------------------------------------------------------------
147 --
148 -- Procedure: check_assign_del_multi
149 -- Parameters: p_detail_rows
150 -- x_del_params
151 -- x_return_status
152 -- Description: Checks for if it is ok to group delivery details
153 -- together for assign to a single delivery. The procedure
154 -- returns an error and sets appropriate messages if any
155 -- assignment cannot happen. If assignment is possible then
156 -- it returns a table of delivery matching parameters.
157 --
158 -----------------------------------------------------------------------------
159
160 PROCEDURE check_assign_del_multi(
161 p_detail_rows IN wsh_util_core.id_tab_type,
162 x_del_params OUT NOCOPY wsh_delivery_autocreate.grp_attr_rec_type,
163 x_return_status OUT NOCOPY VARCHAR2);
164
165
166 -----------------------------------------------------------------------------
167 --
168 -- Procedure: check_credit_holds
169 -- Parameters: p_detail_id
170 -- p_activity_type - 'PICK','PACK','SHIP'
171 -- p_source_line_id - optional
172 -- p_source_header_id - optional
173 -- p_init_flag - 'Y' initializes the table of bad header ids
174 -- x_return_status
175 -- Description: Checks if there are any credit checks or holds on a line.
176 -- Returns a status of FND_API.G_RET_STS_SUCCESS if no such
177 -- checks or holds exist
178 --
179 -----------------------------------------------------------------------------
180
181 PROCEDURE check_credit_holds(
182 p_detail_id IN NUMBER,
183 p_activity_type IN VARCHAR2,
184 p_source_line_id IN NUMBER DEFAULT NULL,
185 p_source_header_id IN NUMBER DEFAULT NULL,
186 p_source_code IN VARCHAR2,
187 p_init_flag IN VARCHAR2 DEFAULT 'Y',
188 x_return_status OUT NOCOPY VARCHAR2);
189
190
191 -----------------------------------------------------------------------------
192 --
193 -- Procedure: check_quantity_to_pick
194 -- Parameters: p_order_line_id, - order line being picked
195 -- p_quantity_to_pick - quantity to transact that
196 -- will be checked
197 -- x_allowed_flag - 'Y' = allowed, 'N' = not allowed
198 -- x_max_quantity_allowed - maximum quantity
199 -- that can be picked
200 -- x_avail_req_quantity - req quantity not yet staged
201 -- x_return_status
202 -- Description: Checks if the quantity to pick is within overshipment
203 -- tolerance, based on the quantities requested and
204 -- staged and assignments to deliveries or containers.
205 -- Also returns the maximum quantity allowed to pick.
206 -- -- History : HW OPM added x_max_quantity2_allowed and x_avail_req_quantity and p_quantity2_to_pick
207 -----------------------------------------------------------------------------
208
209 PROCEDURE check_quantity_to_pick(
210 p_order_line_id IN NUMBER,
211 p_quantity_to_pick IN NUMBER,
212 p_quantity2_to_pick IN NUMBER DEFAULT NULL,
213 x_allowed_flag OUT NOCOPY VARCHAR2,
214 x_max_quantity_allowed OUT NOCOPY NUMBER,
215 x_max_quantity2_allowed OUT NOCOPY NUMBER,
216 x_avail_req_quantity OUT NOCOPY NUMBER,
217 x_avail_req_quantity2 OUT NOCOPY NUMBER,
218 x_return_status OUT NOCOPY VARCHAR2);
219 --
220 -- overloaded check_quantity_to_pick since INV patch G is not
221 -- dependant on WSH patch G.
222 --
223
224 PROCEDURE check_quantity_to_pick(
225 p_order_line_id IN NUMBER,
226 p_quantity_to_pick IN NUMBER,
227 x_allowed_flag OUT NOCOPY VARCHAR2,
228 x_max_quantity_allowed OUT NOCOPY NUMBER,
229 x_avail_req_quantity OUT NOCOPY NUMBER,
230 x_return_status OUT NOCOPY VARCHAR2);
231
232 --
233 -- Procedure: check_zero_req_confirm
234 -- Parameters: p_delivery_id - delivery being confirmed
235 -- x_return_status
236 -- Description: Ensure that delivery details with zero requested
237 -- quantities will not be alone after Ship Confirm.
238 --
239
240 PROCEDURE check_zero_req_confirm(
241 p_delivery_id IN NUMBER,
242 x_return_status OUT NOCOPY VARCHAR2);
243
244
245
246 --
247 -- Procedure: Get_Disabled_List
248 --
249 -- Parameters: p_detail_id -- ID for delivery detail
250 -- p_delivery_id -- delivery the delivery is assigned to
251 -- p_list_type -- 'FORM', will return list of form field names
252 -- 'TABLE', will return list of table column names
253 -- x_return_status -- return status for execution of this API
254 -- x_msg_count
255 -- x_msg_data
256 --
257 PROCEDURE Get_Disabled_List(
258 p_delivery_detail_id IN NUMBER
259 , p_delivery_id IN NUMBER
260 , p_list_type IN VARCHAR2
261 , x_return_status OUT NOCOPY VARCHAR2
262 , x_disabled_list OUT NOCOPY WSH_UTIL_CORE.column_tab_type
263 , x_msg_count OUT NOCOPY NUMBER
264 , x_msg_data OUT NOCOPY VARCHAR2
265 , p_caller IN VARCHAR2 DEFAULT NULL --public api changes
266 );
267
268 --Harmonizing Project
269 TYPE DetailActionsRec IS RECORD(
270 released_status wsh_delivery_details.released_status%TYPE,
271 container_flag wsh_delivery_details.container_flag%TYPE,
272 source_code wsh_delivery_details.source_code%TYPE,
273 caller VARCHAR2(100),
274 action_not_allowed VARCHAR2(100),
275 org_type VARCHAR2(30),
276 message_name VARCHAR2(2000),
277 line_direction VARCHAR2(30),
278 ship_from_location_id NUMBER, -- J-IB-NPARIKH
279 otm_enabled WSH_SHIPPING_PARAMETERS.otm_enabled%TYPE -- OTM R12 - org specificBug#5399341
280 );
281 -- A Column called message_name has been added to the record
282 -- "DetailActionsRec" so that we can set the exact message
283 -- for each record as to why an action is not valid.
284 -- The message_name will contain the message short name
285 -- and appended with its respective tokens with
286 -- "-" as a separator between the message name and the
287 -- tokens and a "," seperator between each of
288 -- the tokens.
289
290 TYPE DetailActionsTabType IS TABLE of DetailActionsRec INDEX BY BINARY_INTEGER;
291
292 TYPE detail_rec_type IS RECORD
293 (delivery_detail_id NUMBER,
294 organization_id NUMBER,
295 released_status VARCHAR2(32000),
296 container_flag VARCHAR2(32000),
297 source_code VARCHAR2(32000),
298 lpn_id NUMBER,
299 line_direction VARCHAR2(30),
300 ship_from_location_id NUMBER, -- J-IB-NPARIKH
301 move_order_line_id WSH_DELIVERY_DETAILS.MOVE_ORDER_LINE_ID%TYPE, -- R12, X-dock project
302 otm_enabled WSH_SHIPPING_PARAMETERS.otm_enabled%TYPE -- OTM R12 - org specific.Bug#5399341
303 );
304
305 TYPE detail_rec_tab_type IS TABLE OF detail_rec_type INDEX BY BINARY_INTEGER;
306
307 TYPE ValidateQuantityAttrRecType IS RECORD
308 (
309 delivery_detail_id NUMBER,
310 requested_quantity NUMBER,
311 requested_quantity2 NUMBER,
312 picked_quantity NUMBER,
313 picked_quantity2 NUMBER,
314 shipped_quantity NUMBER,
315 shipped_quantity2 NUMBER,
316 cycle_count_quantity NUMBER,
317 cycle_count_quantity2 NUMBER,
318 requested_quantity_uom VARCHAR2(3),
319 requested_quantity_uom2 VARCHAR2(3),
320 ship_tolerance_above NUMBER,
321 inventory_item_id NUMBER,
322 organization_id NUMBER,
323 serial_quantity NUMBER,
324 inv_ser_control_code VARCHAR2(1),
325 serial_number VARCHAR2(30),
326 transaction_temp_id NUMBER,
327 top_model_line_id NUMBER,
328 -- for Load tender add fields
329 net_weight NUMBER,
330 gross_weight NUMBER,
331 volume NUMBER,
332 weight_uom_code VARCHAR2(3),
333 volume_uom_code VARCHAR2(3),
334 -- end of Load tender add fields
335 unmark_serial_server VARCHAR2(1) DEFAULT 'Y',
336 unmark_serial_form VARCHAR2(1)
337 -- HW OPMCONV - Removed process_flag
338 );
339
340 PROCEDURE Is_Action_Enabled(
341 p_del_detail_rec_tab IN detail_rec_tab_type,
342 p_action IN VARCHAR2,
343 p_caller IN VARCHAR2,
344 p_deliveryid IN NUMBER DEFAULT null,
345 x_return_status OUT NOCOPY VARCHAR2,
346 x_valid_ids OUT NOCOPY wsh_util_core.id_tab_type,
347 x_error_ids OUT NOCOPY wsh_util_core.id_tab_type,
348 x_valid_index_tab OUT NOCOPY wsh_util_core.id_tab_type );
349
350 --
351 -- Overloaded procedure
352 --
353 PROCEDURE Get_Disabled_List (
354 p_delivery_detail_rec IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type
355 , p_delivery_id IN NUMBER
356 , p_in_rec IN WSH_GLBL_VAR_STRCT_GRP.detailInRecType
357 , x_return_status OUT NOCOPY VARCHAR2
358 , x_msg_count OUT NOCOPY NUMBER
359 , x_msg_data OUT NOCOPY VARCHAR2
360 , x_delivery_detail_rec OUT NOCOPY WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type
361 );
362
363 PROCEDURE Init_Detail_Actions_Tbl (
364 p_action IN VARCHAR2
365 , x_detail_actions_tab OUT NOCOPY DetailActionsTabType
366 , x_return_status OUT NOCOPY VARCHAR2
367 );
368
369 Procedure Validate_Shipped_CC_Quantity
370 (
371 p_flag IN VARCHAR2,
372 x_det_rec IN OUT NOCOPY ValidateQuantityAttrRecType,
373 x_return_status OUT NOCOPY VARCHAR2,
374 x_msg_count OUT NOCOPY NUMBER,
375 x_msg_data OUT NOCOPY VARCHAR2
376 );
377
378 --Harmonizing Project
379
380 -- HW Harmonization project for OPM
381 Procedure Validate_Shipped_CC_Quantity2
382 (
383 p_flag IN VARCHAR2,
384 x_det_rec IN OUT NOCOPY ValidateQuantityAttrRecType,
385 x_return_status OUT NOCOPY VARCHAR2,
386 x_msg_count OUT NOCOPY NUMBER,
387 x_msg_data OUT NOCOPY VARCHAR2
388 );
389
390 --for Load Tender Project
391 /*
392 -----------------------------------------------------------------------------
393 PROCEDURE : Compare_Detail_Attributes
394 PARAMETERS : p_old_table - Table of old records
395 p_new_table - Table of new records
396 p_entity - entity name -DELIVERY_DETAIL
397 p_action_code - action code for each action
398 p_phase - 1 for Before the action is performed, 2 for after.
399 p_caller - where is this API being called from
400 x_changed_id - Table of Changed ids
401 x_return_status - Return Status
402 DESCRIPTION : This procedure compares the attributes for each entity.
403 For Delivery Detail,attributes are - weight/volume,quantity,
404 delivery,parent_delivery_detail
405 Added for Load Tender Project
406 ------------------------------------------------------------------------------
407 */
408 PROCEDURE compare_detail_attributes
409 (p_old_table IN wsh_interface.deliverydetailtab,
410 p_new_table IN wsh_interface.deliverydetailtab,
411 p_action_code IN VARCHAR2,
412 p_phase IN NUMBER,
413 p_caller IN VARCHAR2,
414 x_changed_id_tab OUT NOCOPY wsh_util_core.id_tab_type,
415 x_return_status OUT NOCOPY VARCHAR2
416 );
417
418 --End for Load Tender Project
419
420
421 -- ----------------------------------------------------------------------
422 -- Procedure: validate_secondary_quantity
423 -- Parameters:
424 --
425 -- Description:
426 -- ----------------------------------------------------------------------
427 -- HW OPMCONV - Added p_caller parameter
428 PROCEDURE validate_secondary_quantity
429 (
430 p_delivery_detail_id IN NUMBER,
431 x_quantity IN OUT NOCOPY NUMBER,
432 x_quantity2 IN OUT NOCOPY NUMBER,
433 p_caller IN VARCHAR2,
434 x_return_status OUT NOCOPY VARCHAR2,
435 x_msg_count OUT NOCOPY NUMBER,
436 x_msg_data OUT NOCOPY VARCHAR2
437 );
438
439 -- HW OPMCONV - Added new function to check if line
440 /*
441 -----------------------------------------------------------------------------
442 FUNCTION : is_split_allowed
443 PARAMETERS : p_delivery_detail_id - delivery detail id
444 p_organization_id - organization id
445 p_inventory_item_id - inventory item id
446 p_released_status - released status for this wdd line
447
448 DESCRIPTION : This function checks if delivery detail line
449 is eligible for a split
450 e.g if delivery detail has an item that is lot
451 indivisible and it's staged, split actions will not be permitted
452 ------------------------------------------------------------------------------
453 */
454 FUNCTION is_split_allowed(
455 p_delivery_detail_id IN NUMBER,
456 p_organization_id IN NUMBER,
457 p_inventory_item_id IN NUMBER,
458 p_released_status IN VARCHAR2) RETURN BOOLEAN;
459 /*
460 -----------------------------------------------------------------------------
461 FUNCTION : is_cycle_count_allowed
462 PARAMETERS : p_delivery_detail_id - delivery detail id
463 p_organization_id - organization id
464 p_inventory_item_id - inventory item id
465 p_released_status - released status for this wdd line
466 p_picked_qty - total allocated qty for this wdd line
467 p_cycle_qty - qty to be cycle counted
468
469 DESCRIPTION : This function checks if delivery detail line
470 is eligible for a cycle count.
471 e.g if delivery detail has an item that is lot
472 indivisible and it's staged, only picked qty cycle count is allowed
473 ------------------------------------------------------------------------------
474 */
475 FUNCTION is_cycle_count_allowed(
476 p_delivery_detail_id IN NUMBER,
477 p_organization_id IN NUMBER,
478 p_inventory_item_id IN NUMBER,
479 p_released_status IN VARCHAR2,
480 p_picked_qty IN NUMBER,
481 p_cycle_qty IN NUMBER) RETURN BOOLEAN;
482
483 END WSH_DETAILS_VALIDATIONS;
[Home] [Help]