1 PACKAGE WSH_USA_INV_PVT as
2 /* $Header: WSHUSAIS.pls 120.6.12010000.1 2008/07/29 06:20:15 appldev 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
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 --
79 PROCEDURE Get_total_reserved_quantity (p_source_code IN VARCHAR2 ,
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 PROCEDURE split_reservation (
157 p_delivery_detail_split_rec IN DeliveryDetailInvRecType,
158 p_source_code IN VARCHAR2,
159 p_source_header_id IN NUMBER,
160 p_original_source_line_id IN NUMBER,
161 p_split_source_line_id IN NUMBER,
162 p_split_quantity IN NUMBER, -- Pass p_changed_attrinute.ordered_quantity
163 p_split_quantity2 IN NUMBER,
164 p_move_order_line_status IN VARCHAR2,
165 p_direction_flag IN VARCHAR2 default 'U',
166 x_return_status OUT NOCOPY VARCHAR2);
167
168
169 PROCEDURE update_serial_numbers(
170 p_delivery_detail_id IN NUMBER,
171 p_serial_number IN VARCHAR2,
172 p_transaction_temp_id IN NUMBER,
173 x_return_status OUT NOCOPY VARCHAR2);
174
175
176 PROCEDURE update_inventory_info(
177 p_Changed_Attributes IN WSH_INTERFACE.ChangedAttributeTabType,
178 x_return_status OUT NOCOPY VARCHAR2);
179
180 -- HW OPMCONV Make get_detailed a procedure to pass Qty2
181
182 -- PROCEDURE get_detailed_quantity
183 -- Parameters: p_mo_line_id - Move order line id
184 -- x_detailed_qty - Primary Qty
185 -- x_detailed_qty2 - Secondary Qty
186 -- x_return_status - Return Status
187
188 -- Description: This procedure was originally a function but because of
189 -- OPM Convergence project, it was converted to a procedure to return
190 -- Qty1 and Qty2.
191 -- This procedure checks if Qtys are detailed in mtl_material_transactions_temp
192 -- for a specific move order line id
193 --
194 PROCEDURE get_detailed_quantity(
195 p_mo_line_id IN NUMBER,
196 x_detailed_qty OUT NOCOPY NUMBER,
197 x_detailed_qty2 OUT NOCOPY NUMBER,
198 x_return_status OUT NOCOPY VARCHAR2);
199
200 --X-dock
201
202 -- Function: Based on Move Order Line id, determine the move order type
203 -- Return 'Y' if the MO type = PUTAWAY (for X-dock scenario)
204 -- Parameters : p_move_order_line_id - Move Order Line id
205 --
206 FUNCTION is_mo_type_putaway (p_move_order_line_id IN NUMBER) RETURN VARCHAR2;
207
208 -- PROCEDURE get_putaway_detail_id
209 -- Parameters : p_detail_id - Delivery detail id
210 -- p_released_status - Released Status
211 -- p_move_order_line_id - Move Order Line id
212 -- x_return_status - Return Status
213 -- x_detail_id - Output delivery detail id
214 -- Description : This procedure checks for move_order_type associated
215 -- with move order line id and returns delivery_detail_id
216 -- if the move_order_type is PUTAWAY and null if
217 -- move order type is not PUTAWAY (eg. PICK_WAVE)
218 --
219 PROCEDURE get_putaway_detail_id(
220 p_detail_id IN NUMBER,
221 p_released_status IN VARCHAR2,
222 p_move_order_line_id IN NUMBER,
223 x_detail_id OUT NOCOPY NUMBER,
224 x_return_status OUT NOCOPY VARCHAR2);
225
226 END WSH_USA_INV_PVT;
[Home] [Help]