1 PACKAGE INV_SHIPPING_TRANSACTION_PUB AUTHID CURRENT_USER AS
2 /* $Header: INVPWSHS.pls 120.4.12020000.2 2012/07/09 08:13:52 asugandh ship $ */
3 --
4 TYPE t_genref IS REF CURSOR;
5
6 G_PKG_NAME constant VARCHAR2(30) := 'INV_SHIPPING_TRANSACTION_PUB';
7
8 FUNCTION GET_SHIPMETHOD_MEANING(p_ship_method_code IN VARCHAR2)
9 RETURN VARCHAR2;
10
11 --Transportation enhancement for patchset I.
12 --This procedure calls wsh_fte_comp_constraint_grp.validate_constraint
13 --This procdure is called in the fieldExit for the ShipMethold field
14 PROCEDURE validate_ship_method(p_shipmethod_code IN VARCHAR2,
15 p_delivery_id IN NUMBER,
16 x_return_status OUT nocopy VARCHAR2,
17 x_msg_count OUT nocopy NUMBER,
18 x_msg_data OUT nocopy varchar2);
19 --
20
21 PROCEDURE GET_VALID_DELIVERY(x_deliveryLOV OUT NOCOPY t_genref,
22 p_delivery_name IN VARCHAR2,
23 p_organization_id IN NUMBER);
24
25 PROCEDURE GET_VALID_DELIVERY_VIA_LPN(x_deliveryLOV OUT NOCOPY t_genref,
26 p_delivery_name IN VARCHAR2,
27 p_organization_id IN NUMBER,
28 p_lpn_id IN NUMBER);
29
30 PROCEDURE GET_VALID_DELIVERY_LINE(x_deliveryLineLOV OUT NOCOPY t_genref,
31 p_delivery_id IN NUMBER,
32 p_inventory_item_id IN NUMBER);
33
34 PROCEDURE GET_VALID_CARRIER(x_carrierLOV OUT NOCOPY t_genref,
35 p_carrier_name IN VARCHAR2);
36
37 PROCEDURE GET_SHIP_METHOD_LOV(x_shipMethodLOV OUT NOCOPY t_genref,
38 p_organization_id IN NUMBER,
39 p_ship_method_name IN VARCHAR2);
40
41 PROCEDURE GET_DELIVERY_INFO(x_delivery_info OUT NOCOPY t_genref,
42 p_delivery_id IN NUMBER);
43
44 PROCEDURE INV_DELIVERY_LINE_INFO(x_deliveryLineInfo OUT NOCOPY t_genref,
45 p_delivery_id IN NUMBER,
46 p_inventory_item_id IN NUMBER,
47 p_serial_flag IN VARCHAR2,
48 x_return_Status OUT NOCOPY VARCHAR2);
49
50 PROCEDURE SERIAL_AT_SALES_CHECK(x_result OUT NOCOPY NUMBER,
51 x_item_name OUT NOCOPY VARCHAR2,
52 p_delivery_id IN NUMBER);
53
54 PROCEDURE GET_DELIVERY_LINE_SERIAL_INFO(
55 p_delivery_detail_id IN NUMBER,
56 x_return_Status OUT NOCOPY VARCHAR2,
57 x_inventory_item_id OUT NOCOPY NUMBER,
58 x_transaction_Temp_id OUT NOCOPY NUMBER,
59 x_subinventory_code OUT NOCOPY VARCHAR2,
60 x_revision OUT NOCOPY VARCHAR2,
61 x_locator_id OUT NOCOPY NUMBER,
62 x_lot_number OUT NOCOPY VARCHAR2,
63 x_num_serial_record OUT NOCOPY NUMBER
64 );
65
66 PROCEDURE GET_TRIP_NAME(p_delivery_id IN NUMBER,
67 x_trip_name OUT NOCOPY VARCHAR2,
68 x_trip_id OUT NOCOPY NUMBER);
69
70 PROCEDURE GET_TRIP_LOV(x_trip_lov OUT NOCOPY t_genref,
71 p_trip_name IN VARCHAR2);
72
73 PROCEDURE GET_DOCK_DOOR( x_dock_door OUT NOCOPY t_genref,
74 p_trip_id IN NUMBER);
75
76 PROCEDURE GET_ITEMS_IN_LPN(x_items OUT NOCOPY t_genref,
77 p_lpn_id IN NUMBER);
78
79 --Returns an entire delivery to stock. No partial shipment
80 PROCEDURE INV_RETURN_TO_STOCK(p_delivery_id IN NUMBER,
81 x_return_status OUT NOCOPY VARCHAR2,
82 x_msg_data OUT NOCOPY VARCHAR2,
83 x_msg_count OUT NOCOPY NUMBER
84 );
85
86 PROCEDURE INV_DELAY_SHIPMENT(p_delivery_id IN NUMBER,
87 p_delivery_line_id IN NUMBER,
88 p_shipped_quantity IN NUMBER,
89 x_return_status OUT NOCOPY VARCHAR2,
90 x_msg_data OUT NOCOPY VARCHAR2,
91 x_msg_count OUT NOCOPY NUMBER);
92
93 PROCEDURE INV_LINE_RETURN_TO_STOCK(p_delivery_id IN NUMBER,
94 p_delivery_line_id IN NUMBER,
95 p_shipped_quantity IN NUMBER,
96 x_return_status OUT NOCOPY VARCHAR2,
97 x_msg_data OUT NOCOPY VARCHAR2,
98 x_msg_count OUT NOCOPY NUMBER,
99 p_commit_flag IN VARCHAR2 DEFAULT FND_API.g_true,
100 p_relieve_rsv IN VARCHAR2 DEFAULT 'Y');
101 /**
102 Bug No 3952081
103 Overriding INV_LINE_RETURN_TO_STOCK to include duom attributes
104 **/
105 PROCEDURE INV_LINE_RETURN_TO_STOCK(p_delivery_id IN NUMBER,
106 p_delivery_line_id IN NUMBER,
107 p_shipped_quantity IN NUMBER,
108 p_sec_shipped_quantity IN NUMBER,
109 x_return_status OUT NOCOPY VARCHAR2,
110 x_msg_data OUT NOCOPY VARCHAR2,
111 x_msg_count OUT NOCOPY NUMBER,
112 p_commit_flag IN VARCHAR2 DEFAULT FND_API.g_true,
113 p_relieve_rsv IN VARCHAR2 DEFAULT 'Y');
114
115 PROCEDURE INV_REPORT_MISSING_QTY(
116 p_delivery_line_id IN NUMBER,
117 p_missing_quantity IN NUMBER,
118 x_return_status OUT NOCOPY VARCHAR2,
119 x_msg_data OUT NOCOPY VARCHAR2,
120 x_msg_count OUT NOCOPY NUMBER);
121
122 PROCEDURE INV_REPORT_MISSING_QTY(
123 p_delivery_line_id IN NUMBER,
124 p_missing_quantity IN NUMBER,
125 p_sec_missing_quantity IN NUMBER,
126 x_return_status OUT NOCOPY VARCHAR2,
127 x_msg_data OUT NOCOPY VARCHAR2,
128 x_msg_count OUT NOCOPY NUMBER);
129
130 PROCEDURE SUBMIT_DELIVERY_LINE(p_delivery_line_id IN NUMBER,
131 p_quantity IN NUMBER,
132 p_trackingNumber IN VARCHAR2,
133 x_return_status OUT NOCOPY VARCHAR2,
134 x_msg_data OUT NOCOPY VARCHAR2,
135 x_msg_count OUT NOCOPY NUMBER);
136
137 PROCEDURE SUBMIT_DELIVERY_LINE(p_delivery_line_id IN NUMBER,
138 p_quantity IN NUMBER,
139 p_sec_quantity IN NUMBER,
140 p_trackingNumber IN VARCHAR2,
141 x_return_status OUT NOCOPY VARCHAR2,
142 x_msg_data OUT NOCOPY VARCHAR2,
143 x_msg_count OUT NOCOPY NUMBER);
144 FUNCTION GET_LINE_TRANSACTION_TYPE(
145 p_order_line_id IN NUMBER,
146 x_trx_source_type_id OUT NOCOPY NUMBER,
147 x_trx_Action_id OUT NOCOPY NUMBER,
148 x_return_status OUT NOCOPY VARCHAR2 ) return NUMBER;
149 FUNCTION GET_DELIVERY_TRANSACTION_TYPE(
150 p_delivery_detail_id IN NUMBER,
151 x_trx_source_type_id OUT NOCOPY NUMBER,
152 x_trx_Action_id OUT NOCOPY NUMBER,
153 x_return_status OUT NOCOPY VARCHAR2 ) return NUMBER;
154 PROCEDURE CHECK_DELIVERY_LOADED(
155 p_delivery_id IN NUMBER,
156 x_return_Status OUT NOCOPY VARCHAR2);
157 --bug13581767
158 PROCEDURE CHECK_LAST_DEL_TRIP(p_delivery_id IN NUMBER,
159 x_return_Status OUT NOCOPY VARCHAR2);
160 --end bug13581767
161
162 PROCEDURE CHECK_DELIVERY_STATUS(
163 p_delivery_id IN NUMBER,
164 x_return_Status OUT NOCOPY VARCHAR2,
165 x_error_msg OUT NOCOPY VARCHAR2);
166 PROCEDURE CHECK_SHIP_SET(
167 p_delivery_id IN NUMBER,
168 x_ship_set OUT NOCOPY VARCHAR2,
169 x_return_Status OUT NOCOPY VARCHAR2,
170 x_error_msg OUT NOCOPY VARCHAR2);
171
172 PROCEDURE CHECK_COMPLETE_DELVIERY(
173 p_delivery_id IN NUMBER,
174 x_return_Status OUT NOCOPY VARCHAR2,
175 x_error_msg OUT NOCOPY VARCHAR2);
176
177 PROCEDURE UNASSIGN_DELIVERY_LINES(
178 p_delivery_id IN NUMBER,
179 x_return_Status OUT NOCOPY VARCHAR2,
180 x_error_msg OUT NOCOPY VARCHAR2);
181
182 PROCEDURE CHECK_ENTIRE_EZ_DELIVERY(
183 p_delivery_id IN NUMBER,
184 x_return_Status OUT NOCOPY VARCHAR2,
185 x_error_msg OUT NOCOPY VARCHAR2);
186
187 PROCEDURE CHECK_EZ_SHIP_DELIVERY(
188 p_delivery_id IN NUMBER,
189 x_item_name OUT NOCOPY VARCHAR2,
190 x_return_Status OUT NOCOPY VARCHAR2,
191 x_error_code OUT NOCOPY NUMBER,
192 x_error_msg OUT NOCOPY VARCHAR2);
193
194 PROCEDURE CONFIRM_DELIVERY (
195 p_ship_delivery IN VARCHAR2 DEFAULT NULL,
196 p_delivery_id IN NUMBER,
197 p_organization_id IN NUMBER,
198 p_delivery_name IN VARCHAR2,
199 p_carrier_id IN NUMBER,
200 p_ship_method_code IN VARCHAR2,
201 p_gross_weight IN NUMBER,
202 p_gross_weight_uom IN VARCHAR2,
203 p_bol IN VARCHAR2,
204 p_waybill IN VARCHAR2,
205 p_action_flag IN VARCHAR2,
206 x_return_status OUT NOCOPY VARCHAR2,
207 x_ret_code OUT NOCOPY NUMBER,
208 x_msg_data OUT NOCOPY VARCHAR2,
209 x_msg_count OUT NOCOPY NUMBER);
210
211 PROCEDURE UNASSIGN_LINES_AND_CONFIRM (
212 p_delivery_id IN NUMBER,
213 p_organization_id IN NUMBER,
214 p_delivery_name IN VARCHAR2,
215 p_carrier_id IN NUMBER,
216 p_ship_method_code IN VARCHAR2,
217 p_gross_weight IN NUMBER,
218 p_gross_weight_uom IN VARCHAR2,
219 p_bol IN VARCHAR2,
220 p_waybill IN VARCHAR2,
221 p_action_flag IN VARCHAR2,
222 x_return_status OUT NOCOPY VARCHAR2,
223 x_msg_data OUT NOCOPY VARCHAR2,
224 x_msg_count OUT NOCOPY NUMBER);
225
226 /** ssia 10/17/2002 Added the following method for serial shipping enhancement project
227 The procedure split delivery line is used when user wants to ship short and does not
228 report any missing quantity, i.e, when user wants to delay shipment or want to return
229 some quantity to stock.
230 In that case, we need to split the delivery line to two lines.
231 The original delivery will be the one with the ship quantity, the second delivery
232 is the remaining quantity
233 **/
234 PROCEDURE INV_SPLIT_DELIVERY_LINE(
235 p_delivery_detail_id IN NUMBER,
236 p_ship_quantity IN NUMBER,
237 p_requested_quantity IN NUMBER,
238 x_return_status OUT NOCOPY VARCHAR2,
239 x_msg_count OUT NOCOPY NUMBER,
240 x_msg_data OUT NOCOPY VARCHAR2,
241 x_new_delivery_detail_id OUT NOCOPY NUMBER,
242 x_new_transaction_temp_id OUT NOCOPY NUMBER);
243 /**
244 Bug No 3952081
245 Overriding the procedure INV_SPLIT_DELIVERY_LINE to include
246 DUOM attribute as input arguments
247 **/
248 PROCEDURE INV_SPLIT_DELIVERY_LINE(
249 p_delivery_detail_id IN NUMBER,
250 p_ship_quantity IN NUMBER,
251 p_requested_quantity IN NUMBER,
252 p_sec_ship_quantity IN NUMBER,
253 p_sec_requested_quantity IN NUMBER,
254 x_return_status OUT NOCOPY VARCHAR2,
255 x_msg_count OUT NOCOPY NUMBER,
256 x_msg_data OUT NOCOPY VARCHAR2,
257 x_new_delivery_detail_id OUT NOCOPY NUMBER,
258 x_new_transaction_temp_id OUT NOCOPY NUMBER);
259
260
261 /** ssia 10/17/2002 This is added for serial shipping enhancement project.
262 The procedure is used to delete the serial numbers in msnt table on a
263 particular delivery if user wants to ship short, have a new selected
264 serial numbers. In that case, we just delete the serial numbers for that
265 delivery and insert the new selected serial numbers.
266 This procedures only handles the deletion. The insert serial number
267 is handled by inv_trx_util_pub.insert_ser_trx
268 **/
269
270 PROCEDURE INV_PROCESS_SERIALS(
271 p_transaction_temp_id IN NUMBER,
272 p_delivery_detail_id IN NUMBER,
273 x_return_status OUT NOCOPY VARCHAR2,
274 x_msg_count OUT NOCOPY NUMBER,
275 x_msg_data OUT NOCOPY VARCHAR2);
276
277
278 /** This procedure gets the enforce_ship_method parameter from shipping**/
279 PROCEDURE get_enforce_ship(p_org_id IN NUMBER,
280 x_enforce_ship OUT NOCOPY VARCHAR2,
281 x_return_status OUT nocopy VARCHAR2,
282 x_msg_data OUT nocopy VARCHAR,
283 x_msg_count OUT nocopy NUMBER);
284
285
286 /** This procedure gets the enforce_ship_method parameter from shipping
287 * and Ship Method at trip level, if trip exists for this Delivery**/
288 PROCEDURE get_shipmethod_details
289 (p_org_id IN NUMBER,
290 p_delivery_id IN NUMBER,
291 p_enforce_shipmethod IN OUT NOCOPY VARCHAR2,
292 p_trip_id IN OUT NOCOPY NUMBER,
293 x_trip_shipmethod_code OUT NOCOPY VARCHAR2,
294 x_trip_shipmethod_meaning OUT NOCOPY VARCHAR2,
295 x_return_status OUT NOCOPY VARCHAR2,
296 x_msg_data OUT NOCOPY VARCHAR,
297 x_msg_count OUT NOCOPY NUMBER) ;
298 -- Start of fix for 4629955
299 FUNCTION GET_FREIGHT_CODE(p_carrier_id IN NUMBER)
300 RETURN VARCHAR2;
301 -- End of fix for 4629955
302
303 /* The following API will check whether lot specific conversion defined
304 Parameters and meanings:
305 p_delivery_detail_id - IN parameter, delivery detail ID
306 x_lot_number - OUT parameter, lot_number associated with the delivery detail ID
307 Return values and meanings :
308 0 - No conversion defined
309 1 - The secondary qty
310 */
311
312 FUNCTION is_lotspec_conv(p_delivery_detail_id IN NUMBER, x_lot_number OUT NOCOPY VARCHAR2) RETURN NUMBER;
313
314 END INV_SHIPPING_TRANSACTION_PUB;