1 PACKAGE INV_SHIPPING_TRANSACTION_PUB AS
2 /* $Header: INVPWSHS.pls 120.4.12010000.1 2008/07/24 01:44:25 appldev 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
158 PROCEDURE CHECK_DELIVERY_STATUS(
159 p_delivery_id IN NUMBER,
160 x_return_Status OUT NOCOPY VARCHAR2,
161 x_error_msg OUT NOCOPY VARCHAR2);
162 PROCEDURE CHECK_SHIP_SET(
163 p_delivery_id IN NUMBER,
164 x_ship_set OUT NOCOPY VARCHAR2,
165 x_return_Status OUT NOCOPY VARCHAR2,
166 x_error_msg OUT NOCOPY VARCHAR2);
167
168 PROCEDURE CHECK_COMPLETE_DELVIERY(
169 p_delivery_id IN NUMBER,
170 x_return_Status OUT NOCOPY VARCHAR2,
171 x_error_msg OUT NOCOPY VARCHAR2);
172
173 PROCEDURE UNASSIGN_DELIVERY_LINES(
174 p_delivery_id IN NUMBER,
175 x_return_Status OUT NOCOPY VARCHAR2,
176 x_error_msg OUT NOCOPY VARCHAR2);
177
178 PROCEDURE CHECK_ENTIRE_EZ_DELIVERY(
179 p_delivery_id IN NUMBER,
180 x_return_Status OUT NOCOPY VARCHAR2,
181 x_error_msg OUT NOCOPY VARCHAR2);
182
183 PROCEDURE CHECK_EZ_SHIP_DELIVERY(
184 p_delivery_id IN NUMBER,
185 x_item_name OUT NOCOPY VARCHAR2,
186 x_return_Status OUT NOCOPY VARCHAR2,
187 x_error_code OUT NOCOPY NUMBER,
188 x_error_msg OUT NOCOPY VARCHAR2);
189
190 PROCEDURE CONFIRM_DELIVERY (
191 p_ship_delivery IN VARCHAR2 DEFAULT NULL,
192 p_delivery_id IN NUMBER,
193 p_organization_id IN NUMBER,
194 p_delivery_name IN VARCHAR2,
195 p_carrier_id IN NUMBER,
196 p_ship_method_code IN VARCHAR2,
197 p_gross_weight IN NUMBER,
198 p_gross_weight_uom IN VARCHAR2,
199 p_bol IN VARCHAR2,
200 p_waybill IN VARCHAR2,
201 p_action_flag IN VARCHAR2,
202 x_return_status OUT NOCOPY VARCHAR2,
203 x_ret_code OUT NOCOPY NUMBER,
204 x_msg_data OUT NOCOPY VARCHAR2,
205 x_msg_count OUT NOCOPY NUMBER);
206
207 PROCEDURE UNASSIGN_LINES_AND_CONFIRM (
208 p_delivery_id IN NUMBER,
209 p_organization_id IN NUMBER,
210 p_delivery_name IN VARCHAR2,
211 p_carrier_id IN NUMBER,
212 p_ship_method_code IN VARCHAR2,
213 p_gross_weight IN NUMBER,
214 p_gross_weight_uom IN VARCHAR2,
215 p_bol IN VARCHAR2,
216 p_waybill IN VARCHAR2,
217 p_action_flag IN VARCHAR2,
218 x_return_status OUT NOCOPY VARCHAR2,
219 x_msg_data OUT NOCOPY VARCHAR2,
220 x_msg_count OUT NOCOPY NUMBER);
221
222 /** ssia 10/17/2002 Added the following method for serial shipping enhancement project
223 The procedure split delivery line is used when user wants to ship short and does not
224 report any missing quantity, i.e, when user wants to delay shipment or want to return
225 some quantity to stock.
226 In that case, we need to split the delivery line to two lines.
227 The original delivery will be the one with the ship quantity, the second delivery
228 is the remaining quantity
229 **/
230 PROCEDURE INV_SPLIT_DELIVERY_LINE(
231 p_delivery_detail_id IN NUMBER,
232 p_ship_quantity IN NUMBER,
233 p_requested_quantity IN NUMBER,
234 x_return_status OUT NOCOPY VARCHAR2,
235 x_msg_count OUT NOCOPY NUMBER,
236 x_msg_data OUT NOCOPY VARCHAR2,
237 x_new_delivery_detail_id OUT NOCOPY NUMBER,
238 x_new_transaction_temp_id OUT NOCOPY NUMBER);
239 /**
240 Bug No 3952081
241 Overriding the procedure INV_SPLIT_DELIVERY_LINE to include
242 DUOM attribute as input arguments
243 **/
244 PROCEDURE INV_SPLIT_DELIVERY_LINE(
245 p_delivery_detail_id IN NUMBER,
246 p_ship_quantity IN NUMBER,
247 p_requested_quantity IN NUMBER,
248 p_sec_ship_quantity IN NUMBER,
249 p_sec_requested_quantity IN NUMBER,
250 x_return_status OUT NOCOPY VARCHAR2,
251 x_msg_count OUT NOCOPY NUMBER,
252 x_msg_data OUT NOCOPY VARCHAR2,
253 x_new_delivery_detail_id OUT NOCOPY NUMBER,
254 x_new_transaction_temp_id OUT NOCOPY NUMBER);
255
256 /** ssia 10/17/2002 This is added for serial shipping enhancement project.
257 The procedure is used to delete the serial numbers in msnt table on a
258 particular delivery if user wants to ship short, have a new selected
259 serial numbers. In that case, we just delete the serial numbers for that
260 delivery and insert the new selected serial numbers.
261 This procedures only handles the deletion. The insert serial number
262 is handled by inv_trx_util_pub.insert_ser_trx
263 **/
264
265 PROCEDURE INV_PROCESS_SERIALS(
266 p_transaction_temp_id IN NUMBER,
267 p_delivery_detail_id IN NUMBER,
268 x_return_status OUT NOCOPY VARCHAR2,
269 x_msg_count OUT NOCOPY NUMBER,
270 x_msg_data OUT NOCOPY VARCHAR2);
271
272
273 /** This procedure gets the enforce_ship_method parameter from shipping**/
274 PROCEDURE get_enforce_ship(p_org_id IN NUMBER,
275 x_enforce_ship OUT NOCOPY VARCHAR2,
276 x_return_status OUT nocopy VARCHAR2,
277 x_msg_data OUT nocopy VARCHAR,
278 x_msg_count OUT nocopy NUMBER);
279
280
281 /** This procedure gets the enforce_ship_method parameter from shipping
282 * and Ship Method at trip level, if trip exists for this Delivery**/
283 PROCEDURE get_shipmethod_details
284 (p_org_id IN NUMBER,
285 p_delivery_id IN NUMBER,
286 p_enforce_shipmethod IN OUT NOCOPY VARCHAR2,
287 p_trip_id IN OUT NOCOPY NUMBER,
288 x_trip_shipmethod_code OUT NOCOPY VARCHAR2,
289 x_trip_shipmethod_meaning OUT NOCOPY VARCHAR2,
290 x_return_status OUT NOCOPY VARCHAR2,
291 x_msg_data OUT NOCOPY VARCHAR,
292 x_msg_count OUT NOCOPY NUMBER) ;
293 -- Start of fix for 4629955
294 FUNCTION GET_FREIGHT_CODE(p_carrier_id IN NUMBER)
295 RETURN VARCHAR2;
296 -- End of fix for 4629955
297
298 /* The following API will check whether lot specific conversion defined
299 Parameters and meanings:
300 p_delivery_detail_id - IN parameter, delivery detail ID
301 x_lot_number - OUT parameter, lot_number associated with the delivery detail ID
302 Return values and meanings :
303 0 - No conversion defined
304 1 - The secondary qty
305 */
306
307 FUNCTION is_lotspec_conv(p_delivery_detail_id IN NUMBER, x_lot_number OUT NOCOPY VARCHAR2) RETURN NUMBER;
308
309 END INV_SHIPPING_TRANSACTION_PUB;