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