1 PACKAGE WSH_USA_INV_PVT AUTHID CURRENT_USER as
2 /* $Header: WSHUSAIS.pls 120.7 2010/09/15 05:11:45 brana ship $ */
3
4 G_PACKAGE_NAME CONSTANT VARCHAR2(50) := 'WSH_USA_INV_PVT';
5
6 -- Variables used to store the Previous Ship Set / SMC record information during Backorder
7 g_ss_smc_found BOOLEAN := FALSE;
8 g_move_order_line_id NUMBER;
9 g_ship_set_id NUMBER;
10 g_ship_model_id NUMBER;
11 g_backordered_item VARCHAR2(2000);
12 g_top_model_item VARCHAR2(2000);
13 g_ship_set_name VARCHAR2(30);
14 g_delivery_detail_id NUMBER;
15
16
17 -- This record type contains inventory level information for a delivery detail
18 TYPE DeliveryDetailInvRecType IS RECORD
19 (delivery_detail_id NUMBER ,
20 released_status VARCHAR2(1) ,
21 move_order_line_id NUMBER ,
22 organization_id NUMBER ,
23 inventory_item_id NUMBER ,
24 subinventory VARCHAR2(10) ,
25 revision VARCHAR2(3) ,
26 lot_number WSH_DELIVERY_DETAILS.LOT_NUMBER%TYPE ,
27 locator_id NUMBER ,
28 lpn_id NUMBER
29 );
30
31 TYPE Back_Det_Rec IS RECORD (
32 DELIVERY_DETAIL_ID NUMBER,
33 DELIVERY_ID NUMBER,
34 CONTAINER_ID NUMBER,
35 ORGANIZATION_ID NUMBER,
36 LINE_DIRECTION VARCHAR2(10),
37 GROSS_WEIGHT NUMBER,
38 NET_WEIGHT NUMBER,
39 VOLUME NUMBER,
40 PLANNED_FLAG VARCHAR2(1),
41 DEL_BATCH_ID NUMBER
42 );
43
44 TYPE Back_Det_Rec_Tbl IS TABLE OF Back_Det_Rec INDEX BY BINARY_INTEGER;
45
46 /**
47 Procedure handles unassigning of delivery detail from delivery/container
48 This procedure is called after detail is set to Backordered status to handle
49 Wt/Vol adjustments as well as any other processing logic.
50 The backordered delivery detail is unassigned from the delivery
51 if the delivery is not planned
52 The backordered delivery detail is unpacked if the org is wms enabled
53 or if the org is not wms enabled and the delivery is not planned.
54 Parameters :
55 p_backorder_rec_tbl - Input Table of Records with the following record structure:
56 delivery_detail_id - Delivery Detail which is getting backordered
57 delivery_id - Delivery of the backordered detail
58 container_id - Immediate Parent Container of the backordered detail
59 organization_id - Delivery Detail's Organization
60 line_direction - Line Direction
61 planned_flag - Delivery is Planned or not (Y/N)
62 gross_weight - Detail's Gross Weight
63 net_weight - Detail's Net Weight
64 volume - Detail's Volume
65 del_batch_id - Delivery's Pick Release Batch Id (whether created during Pick Release process)
66 x_return_status - Return Status (Success/Unexpected Error)
67 */
68 PROCEDURE Unassign_Backordered_Details (
69 p_backorder_rec_tbl IN Back_Det_Rec_Tbl,
70 p_org_info_tbl IN WSH_PICK_LIST.Org_Params_Rec_Tbl,
71 x_return_status OUT NOCOPY VARCHAR2
72 );
73
74 -- This procedure returns the total reservations that exist on a source_line_id in an organization
75
79 PROCEDURE Get_total_reserved_quantity (p_source_code IN VARCHAR2 ,
76 -- HW OPMCONV - Need to get Qty2 reserved.
77 -- Added x_total_rsv2 and added NOCOPY To x_total_rsv and x_total_rsv2
78 --
80 p_source_header_id IN NUMBER ,
81 p_source_line_id IN NUMBER ,
82 p_organization_id IN NUMBER ,
83 x_total_rsv IN OUT NOCOPY NUMBER ,
84 x_total_rsv2 IN OUT NOCOPY NUMBER ,
85 x_return_status IN OUT NOCOPY VARCHAR2);
86
87
88 -- This is a wrapper on inv_reservation_pub.query_reservation
89 -- X-dock add new parameter p_delivery_detail_id as reservations for
90 -- X-dock lines will be on the basis of delivery detail id
91 PROCEDURE query_reservations (
92 p_source_code IN VARCHAR2,
93 p_source_header_id IN NUMBER,
94 p_source_line_id IN NUMBER,
95 p_organization_id IN NUMBER,
96 p_lock_records IN VARCHAR2 DEFAULT FND_API.G_FALSE,
97 p_cancel_order_mode IN NUMBER DEFAULT INV_RESERVATION_GLOBAL.G_CANCEL_ORDER_NO,
98 p_direction_flag IN VARCHAR2 DEFAULT 'U',
99 p_delivery_detail_id IN NUMBER,
100 x_mtl_reservation_tbl OUT NOCOPY INV_RESERVATION_GLOBAL.MTL_RESERVATION_TBL_TYPE,
101 x_mtl_reservation_tbl_count OUT NOCOPY NUMBER,
102 x_return_status OUT NOCOPY VARCHAR2);
103
104 -- This is a wrapper on private delete_reservation
105 PROCEDURE delete_reservation (
106 p_query_input IN inv_reservation_global.mtl_reservation_rec_type,
107 x_return_status OUT NOCOPY VARCHAR2);
108
109 -- This is a wrapper on inv_reservation_pub.create_reservation
110 -- HW OPMCONV Pass Qty2 in order to call the correct INV_create_reservation
111 PROCEDURE create_reservation (
112 p_query_input IN inv_reservation_global.mtl_reservation_rec_type,
113 p_qty2 IN NUMBER default NULL,
114 x_reservation_id OUT NOCOPY NUMBER,
115 x_qty_reserved OUT NOCOPY NUMBER,
116 x_return_status OUT NOCOPY VARCHAR2);
117
118 -- This is a wrapper on inv_reservation_pub.update_reservation
119 PROCEDURE update_reservation (
120 p_query_input IN inv_reservation_global.mtl_reservation_rec_type,
121 p_new_resv_rec IN inv_reservation_global.mtl_reservation_rec_type,
122 x_return_status OUT NOCOPY VARCHAR2);
123
124 FUNCTION check_allocations (
125 p_move_order_line_id IN NUMBER) RETURN BOOLEAN;
126
127 -- This procedure takes care that staged reservations get reduced by the cancellation quantity from
128 -- appropriate inventory controls when some quantity from a staged delivery line is reduced
129 -- HW OPMCONV - Pass Qty2
130 PROCEDURE cancel_staged_reservation (
131 p_source_code IN VARCHAR2,
132 p_source_header_id IN NUMBER,
133 p_source_line_id IN NUMBER,
134 p_delivery_detail_split_rec IN DeliveryDetailInvRecType,
135 p_cancellation_quantity IN NUMBER,
136 p_cancellation_quantity2 IN NUMBER,
137 x_return_status OUT NOCOPY VARCHAR2);
138
139
140 -- This procedure takes care that non staged reservations get reduced by the cancellation quantity
141 -- when some quantity from one or more non staged delivery line is reduced
142 PROCEDURE cancel_nonstaged_reservation (
143 p_source_code IN VARCHAR2,
144 p_source_header_id IN NUMBER,
145 p_source_line_id IN NUMBER,
146 p_delivery_detail_id IN NUMBER, --Bug3012297
147 p_organization_id IN NUMBER,
148 p_cancellation_quantity IN NUMBER,
149 p_cancellation_quantity2 IN NUMBER,
150 x_return_status OUT NOCOPY VARCHAR2);
151
152
153 -- This procedure takes care that reservations get updated/transferred by the split quantity
154 -- when an order line gets split.
155 -- Bug 2540015: Added p_move_order_line_status. Expected values are 'TRANSFER', 'CANCEL'
156 -- Added parameter p_shipped_flag for bug 10105817
157 PROCEDURE split_reservation (
158 p_delivery_detail_split_rec IN DeliveryDetailInvRecType,
159 p_source_code IN VARCHAR2,
160 p_source_header_id IN NUMBER,
161 p_original_source_line_id IN NUMBER,
162 p_split_source_line_id IN NUMBER,
163 p_split_quantity IN NUMBER, -- Pass p_changed_attrinute.ordered_quantity
164 p_split_quantity2 IN NUMBER,
165 p_move_order_line_status IN VARCHAR2,
166 p_direction_flag IN VARCHAR2 default 'U',
167 p_shipped_flag IN VARCHAR2 default 'N',
168 x_return_status OUT NOCOPY VARCHAR2);
169
170
171 PROCEDURE update_serial_numbers(
172 p_delivery_detail_id IN NUMBER,
173 p_serial_number IN VARCHAR2,
174 p_transaction_temp_id IN NUMBER,
175 x_return_status OUT NOCOPY VARCHAR2);
176
177
178 PROCEDURE update_inventory_info(
179 p_Changed_Attributes IN WSH_INTERFACE.ChangedAttributeTabType,
180 x_return_status OUT NOCOPY VARCHAR2);
181
182 -- HW OPMCONV Make get_detailed a procedure to pass Qty2
183
184 -- PROCEDURE get_detailed_quantity
185 -- Parameters: p_mo_line_id - Move order line id
186 -- x_detailed_qty - Primary Qty
187 -- x_detailed_qty2 - Secondary Qty
188 -- x_return_status - Return Status
189
190 -- Description: This procedure was originally a function but because of
191 -- OPM Convergence project, it was converted to a procedure to return
192 -- Qty1 and Qty2.
193 -- This procedure checks if Qtys are detailed in mtl_material_transactions_temp
194 -- for a specific move order line id
195 --
196 PROCEDURE get_detailed_quantity(
197 p_mo_line_id IN NUMBER,
198 x_detailed_qty OUT NOCOPY NUMBER,
199 x_detailed_qty2 OUT NOCOPY NUMBER,
200 x_return_status OUT NOCOPY VARCHAR2);
201
202 --X-dock
203
204 -- Function: Based on Move Order Line id, determine the move order type
205 -- Return 'Y' if the MO type = PUTAWAY (for X-dock scenario)
206 -- Parameters : p_move_order_line_id - Move Order Line id
207 --
208 FUNCTION is_mo_type_putaway (p_move_order_line_id IN NUMBER) RETURN VARCHAR2;
209
210 -- PROCEDURE get_putaway_detail_id
211 -- Parameters : p_detail_id - Delivery detail id
212 -- p_released_status - Released Status
213 -- p_move_order_line_id - Move Order Line id
214 -- x_return_status - Return Status
215 -- x_detail_id - Output delivery detail id
216 -- Description : This procedure checks for move_order_type associated
217 -- with move order line id and returns delivery_detail_id
218 -- if the move_order_type is PUTAWAY and null if
219 -- move order type is not PUTAWAY (eg. PICK_WAVE)
220 --
221 PROCEDURE get_putaway_detail_id(
222 p_detail_id IN NUMBER,
223 p_released_status IN VARCHAR2,
224 p_move_order_line_id IN NUMBER,
225 x_detail_id OUT NOCOPY NUMBER,
226 x_return_status OUT NOCOPY VARCHAR2);
227
228 END WSH_USA_INV_PVT;