1 PACKAGE WSH_SHIP_CONFIRM_ACTIONS as
2 /* $Header: WSHDDSHS.pls 120.2.12010000.1 2008/07/29 05:59:41 appldev ship $ */
3
4 --<TPA_PUBLIC_NAME=WSH_TPA_DELIVERY_DETAILS_PKG>
5 --<TPA_PUBLIC_FILE_NAME=WSHTPDD>
6
7 TYPE t_InterfaceRecord is RECORD (
8 shipping_quantity oe_order_lines_all.shipping_quantity%TYPE,
9 shipping_quantity_UOM oe_order_lines_all.shipping_quantity_uom%TYPE,
10 actual_shipment_date oe_order_lines_all.actual_shipment_date%TYPE,
11 over_ship_reason_code oe_order_lines_all.over_ship_reason_code%TYPE);
12 TYPE process_order_table is table of t_InterfaceRecord INDEX BY BINARY_INTEGER;
13
14 --
15 --Procedure: Ship_Confirm_A_Trip_Stop
16 --Parameters: p_stop_id,
17 -- x_return_status
18 --Description: This procedure will ship confirm the whole trip stop.
19 -- It submits the inventory interface program -- inv_interface
20 PROCEDURE Ship_Confirm_A_Trip_Stop
21 (p_stop_id number,
22 x_return_status out NOCOPY varchar2);
23
24 --
25 --Function: More_Shipment_Exist
26 --Parameters: p_delivery_id,
27 -- p_source_line_id
28 --Description: This function returns a boolean value to indicate
29 -- if there is more shipment exist for the source line
30 -- that is being shipped within the delivery
31
32 FUNCTION More_Shipment_Exist(p_delivery_id number,p_source_code varchar2, p_source_line_id number) RETURN BOOLEAN;
33
34 --
35 --Function: Get_Line_Expected_Qty
36 --Parameters: p_source_line_id
37 -- p_batch_id
38 --Description: This function returns expected shipped quantity
39 -- for a source line besides the shipped quantity in
40 -- the current stop
41
42 FUNCTION Get_Line_Expected_Qty(p_source_line_id number, p_batch_id number) RETURN NUMBER ;
43
44
45 --R12:MOAC made get_new_tolerance internal to this package
46
47
48 --
49 --Function: Part_of_PTO
50 --Parameters: p_source_line_id
51 --Description: This function returns a boolean value to
52 -- indicate if the order line is part of a PTO
53
54 FUNCTION Part_Of_PTO(p_source_code varchar2,p_source_line_id number) RETURN BOOLEAN;
55 FUNCTION Top_Of_Model(p_source_code varchar2,p_source_line_id number) RETURN NUMBER;
56
57 /* OE_interface can be called from the ship confirm program or
58 it can be called from a concurrent program by user in the case of
59 oe interface failed and it needs to be re-run manually by user */
60 --
61 --Procedure: oe_interface
62 -- errbuf
63 -- retcode
64 -- p_stop_id
65 --Description: It loops through all deliveries at the stop and
66 -- call interface_header_to_OM to interface all
67 -- deliveries to Order Management
68
69 PROCEDURE oe_interface(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2, p_stop_id IN number);
70
71
72 -- start bug 1578251: move oe_interface logic to oe_interface_trip_stop and set completion_status
73 --
74 --Procedure: oe_interface_trip_stop
75 -- p_batch_id
76 -- x_completion_status
77 -- x_num_stop_removed, number of stops removed
78 -- from the batch.
79 -- p_stop_tab the table of stop_ids which were in
80 -- the patch before calling this API
81 -- x_stop_tab This table will be populated as follow. If the call
82 -- to Interface_Stop_To_OM returns warning or error, then if there
83 -- some stops that are processed successfully, then these stops will
84 -- be populated in x_stop_tab, if non of the stops are successful
85 -- then x_stop_tab will be populated with p_stop_tab
86 --
87 --
88 --Description: It loops through all deliveries at the stop and
89 -- call interface_header_to_OM to interface all
90 -- deliveries to Order Management
91
92 PROCEDURE oe_interface_trip_stop(p_batch_id IN number,
93 p_stop_tab IN wsh_util_core.id_tab_type,
94 x_stop_tab OUT NOCOPY wsh_util_core.id_tab_type,
95 x_num_stops_removed OUT NOCOPY NUMBER,
96 x_completion_status OUT NOCOPY VARCHAR2);
97 -- end bug 1578251: move oe_interface logic to oe_interface_trip_stop and set completion_status
98
99
100 -- start bug 1578251: Interface_ALL to batch process the stops
101 --
102 --Procedure: Interface_ALL
103 --Parameters: errbuf
104 -- retcode
105 -- p_mode ('INV', 'OM DSNO', 'INV OM DSNO', etc.)
106 -- p_stop_id
107 -- p_delivery_id (supersedes p_stop_id)
108 -- p_log_level
109 --Description: It loops through all stops that need interfacing
110 -- It calls interfaces selected by p_mode
111 procedure interface_ALL(errbuf OUT NOCOPY VARCHAR2,
112 retcode OUT NOCOPY VARCHAR2,
113 p_mode IN VARCHAR2 DEFAULT 'ALL',
114 p_stop_id IN NUMBER DEFAULT NULL,
115 p_delivery_id IN NUMBER DEFAULT NULL,
116 p_log_level IN NUMBER DEFAULT 0,
117 p_batch_id IN NUMBER DEFAULT NULL,
118 p_trip_type IN VARCHAR2 DEFAULT NULL,
119 p_organization_id IN NUMBER DEFAULT NULL,
120 p_stops_per_batch IN NUMBER DEFAULT NULL);
121
122 -- end bug 1578251: Interface_ALL to batch process the stops
123
124
125 --
126 --Procedure: Inv_Interface
127 --Parameters: errbuf
128 -- retcode
129 -- p_stop_id
130 --Description: It is a wrapper for Interface_ALL (bug 1578251)
131 -- This procedure is maintained for backward compatibility
132 procedure inv_interface(errbuf out NOCOPY varchar2,
133 retcode out NOCOPY varchar2,
134 p_stop_id in number);
135
136
137 -- begin bug 1578251: create new procedure Inv_Interface_Trip_stop
138 --
139 --Procedure: Inv_Interface_Trip_Stop
140 --Parameters: p_batch_id
141 -- x_completion_status
142 --Description: It loops through all deliveries at the stop
143 -- and handles INV Interface.
144 procedure inv_interface_trip_stop(p_batch_id IN NUMBER,
145 x_completion_status OUT NOCOPY VARCHAR2);
146 -- end bug 1578251: create new procedure inv_Interface_Trip_Stop
147
148
149 procedure process_inv_online ( p_batch_id in number ,
150 p_transaction_header_id in number ,
151 x_return_status out NOCOPY varchar2 ) ;
152
153 procedure update_interfaced_details ( p_batch_id in number , x_return_status out NOCOPY varchar2 ) ;
154
155 -- Name Get_Account
156 -- Purpose This function will return default account for interface to
157 -- inventory when ship confirm
158 -- Arguments p_delivery_detail_id
159 -- x_return_status result from execution of this function
160 -- Assumption IF account not found, NULL will be returned
161 -- TPA selector WSH_TPA_SELECTOR_PKG.DeliveryDetailTP
162
163 FUNCTION Get_Account(
164 p_delivery_detail_id IN NUMBER
165 , x_return_status OUT NOCOPY VARCHAR2
166 ) RETURN NUMBER;
167 --<TPA_PUBLIC_NAME>
168 --<TPA_DEFAULT_TPS=WSH_TPA_SELECTOR_PKG.DeliveryDetailTP>
169
170
171 FUNCTION ALL_INTERFACED ( p_batch_id in number ) RETURN BOOLEAN ;
172 FUNCTION INV_INTERFACED ( p_batch_id in number ) RETURN BOOLEAN ;
173 FUNCTION OM_INTERFACED ( p_batch_id in number ) RETURN BOOLEAN ;
174
175 -- bug 1651076
176 ont_source_code varchar2(40) := NULL;
177
178 --Added to fix bug 1678453
179 l_currentDate DATE;
180
181
182 --HVOP:INV heali
183 --Recode of table for mtl_transactions_interface
184 TYPE Mtl_txn_if_rec_type IS RECORD(
185 source_code WSH_BULK_TYPES_GRP.tbl_v40,
186 source_header_id WSH_BULK_TYPES_GRP.tbl_num,
187 source_line_id WSH_BULK_TYPES_GRP.tbl_num,
188 inventory_item_id WSH_BULK_TYPES_GRP.tbl_num,
189 subinventory WSH_BULK_TYPES_GRP.tbl_v40,
190 trx_quantity WSH_BULK_TYPES_GRP.tbl_num,
191 trx_date WSH_BULK_TYPES_GRP.tbl_date,
192 organization_id WSH_BULK_TYPES_GRP.tbl_num,
193 trx_source_id WSH_BULK_TYPES_GRP.tbl_num,
194 trx_source_type_id WSH_BULK_TYPES_GRP.tbl_num,
195 trx_action_id WSH_BULK_TYPES_GRP.tbl_num,
196 trx_type_id WSH_BULK_TYPES_GRP.tbl_num,
197 distribution_account_id WSH_BULK_TYPES_GRP.tbl_num,
198 trx_reference WSH_BULK_TYPES_GRP.tbl_v240,
199 trx_header_id WSH_BULK_TYPES_GRP.tbl_num,
200 trx_source_line_id WSH_BULK_TYPES_GRP.tbl_num,
201 trx_source_delivery_id WSH_BULK_TYPES_GRP.tbl_num,
202 revision WSH_BULK_TYPES_GRP.tbl_v3,
203 locator_id WSH_BULK_TYPES_GRP.tbl_num,
204 picking_line_id WSH_BULK_TYPES_GRP.tbl_num,
205 transfer_subinventory WSH_BULK_TYPES_GRP.tbl_v10,
206 transfer_organization WSH_BULK_TYPES_GRP.tbl_num,
207 ship_to_location_id WSH_BULK_TYPES_GRP.tbl_num,
208 requisition_line_id WSH_BULK_TYPES_GRP.tbl_num,
209 requisition_distribution_id WSH_BULK_TYPES_GRP.tbl_num,
210 trx_uom WSH_BULK_TYPES_GRP.tbl_v3,
211 trx_interface_id WSH_BULK_TYPES_GRP.tbl_num,
212 shipment_number WSH_BULK_TYPES_GRP.tbl_v30,
213 expected_arrival_date WSH_BULK_TYPES_GRP.tbl_date,
214 encumbrance_account WSH_BULK_TYPES_GRP.tbl_num,
215 encumbrance_amount WSH_BULK_TYPES_GRP.tbl_num,
216 movement_id WSH_BULK_TYPES_GRP.tbl_num,
217 freight_code WSH_BULK_TYPES_GRP.tbl_v30,
218 waybill_airbill WSH_BULK_TYPES_GRP.tbl_v30,
219 content_lpn_id WSH_BULK_TYPES_GRP.tbl_num,
220 requested_quantity WSH_BULK_TYPES_GRP.tbl_num,
221 inv_interfaced_flag WSH_BULK_TYPES_GRP.tbl_v1,
222 ship_method_code WSH_BULK_TYPES_GRP.tbl_v30,
223 cycle_count_quantity WSH_BULK_TYPES_GRP.tbl_num,
224 src_requested_quantity_uom WSH_BULK_TYPES_GRP.tbl_v3,
225 transaction_temp_id WSH_BULK_TYPES_GRP.tbl_num,
226 -- HW OPMCONV.Use length of 80 for lot_number
227 lot_number WSH_BULK_TYPES_GRP.tbl_v80,
228 serial_number WSH_BULK_TYPES_GRP.tbl_v30,
229 to_serial_number WSH_BULK_TYPES_GRP.tbl_v30,
230 trip_id WSH_BULK_TYPES_GRP.tbl_num,
231 -- HW OPMCONV. No need for sublot anymore
232 -- sublot_number WSH_BULK_TYPES_GRP.tbl_v32,
233 ship_tolerance_above WSH_BULK_TYPES_GRP.tbl_num,
234 ship_tolerance_below WSH_BULK_TYPES_GRP.tbl_num,
235 src_requested_quantity WSH_BULK_TYPES_GRP.tbl_num,
236 org_id WSH_BULK_TYPES_GRP.tbl_num,
237 -- OPM 3064890 added qty2
238 trx_quantity2 WSH_BULK_TYPES_GRP.tbl_num,
239 error_flag WSH_BULK_TYPES_GRP.tbl_v1,
240 -- HW OPMCONV. New variables
241 GRADE_CODE WSH_BULK_TYPES_GRP.tbl_v150,
242 SECONDARY_TRX_UOM WSH_BULK_TYPES_GRP.tbl_v3,
243 --Added for Bug 4538005
244 ship_from_location_id WSH_BULK_TYPES_GRP.tbl_num,
245 ship_to_site_use_id WSH_BULK_TYPES_GRP.tbl_num
246 );
247
248
249
250
251 --Roecode of table for mtl_serial_numbers_interface
252 -- Attributes Added For Bug 3628620
253 TYPE Mtl_ser_txn_if_rec_type IS RECORD(
254 source_code WSH_BULK_TYPES_GRP.tbl_v30,
255 source_line_id WSH_BULK_TYPES_GRP.tbl_num,
256 fm_serial_number WSH_BULK_TYPES_GRP.tbl_v30,
257 to_serial_number WSH_BULK_TYPES_GRP.tbl_v30,
258 transaction_interface_id WSH_BULK_TYPES_GRP.tbl_num,
259 attribute_category WSH_BULK_TYPES_GRP.tbl_v30,
260 attribute1 WSH_BULK_TYPES_GRP.tbl_v150,
261 attribute2 WSH_BULK_TYPES_GRP.tbl_v150,
262 attribute3 WSH_BULK_TYPES_GRP.tbl_v150,
263 attribute4 WSH_BULK_TYPES_GRP.tbl_v150,
264 attribute5 WSH_BULK_TYPES_GRP.tbl_v150,
265 attribute6 WSH_BULK_TYPES_GRP.tbl_v150,
266 attribute7 WSH_BULK_TYPES_GRP.tbl_v150,
267 attribute8 WSH_BULK_TYPES_GRP.tbl_v150,
268 attribute9 WSH_BULK_TYPES_GRP.tbl_v150,
269 attribute10 WSH_BULK_TYPES_GRP.tbl_v150,
270 attribute11 WSH_BULK_TYPES_GRP.tbl_v150,
271 attribute12 WSH_BULK_TYPES_GRP.tbl_v150,
272 attribute13 WSH_BULK_TYPES_GRP.tbl_v150,
273 attribute14 WSH_BULK_TYPES_GRP.tbl_v150,
274 attribute15 WSH_BULK_TYPES_GRP.tbl_v150
275 );
276
277 --Roecode of table for mtl_transaction_lots_interface
278 TYPE Mtl_lot_txn_if_rec_type IS RECORD(
279 source_code WSH_BULK_TYPES_GRP.tbl_v30,
280 source_line_id WSH_BULK_TYPES_GRP.tbl_num,
281 -- HW OPMCONV. Make length of lot 80
282 lot_number WSH_BULK_TYPES_GRP.tbl_v80,
283 trx_quantity WSH_BULK_TYPES_GRP.tbl_num,
284 transaction_interface_id WSH_BULK_TYPES_GRP.tbl_num,
285 serial_transaction_temp_id WSH_BULK_TYPES_GRP.tbl_num,
286 -- HW OPMCONV. New variables
287 secondary_trx_quantity WSH_BULK_TYPES_GRP.tbl_num,
288 grade_code WSH_BULK_TYPES_GRP.tbl_v150
289 );
290
291 -- HW OPMCONV. Removed parameters x_opm_org_exist
292 -- and x_non_opm_org_exist
293 -- HW BUG#:3999479- Need x_non_opm_org_exist
294 PROCEDURE Interface_Detail_To_Inv(
295 p_batch_id IN NUMBER,
296 P_transaction_header_id IN NUMBER,
297 -- x_opm_org_exist OUT NOCOPY BOOLEAN,
298 x_non_opm_org_exist OUT NOCOPY BOOLEAN,
299 x_return_status OUT NOCOPY VARCHAR2);
300
301 --HVOP:INV heali
302
303 --HVOP:OM heali
304 PROCEDURE Interface_Stop_To_OM(
305 p_batch_id IN NUMBER,
306 x_return_status out NOCOPY varchar2);
307
308 --HVOP:OM heali
309
310
311 --
312 --Procedure: Interface_ALL_wrp
313 --Parameters: errbuf
314 -- retcode
315 -- p_mode ('INV', 'OM DSNO', 'INV OM DSNO', etc.)
316 -- p_stop_id
317 -- p_delivery_id (supersedes p_stop_id)
318 -- p_log_level
319 -- p_organization_id
320 -- p_num_requests number of concurrent requests
321 --Description: This is a wrapper to the original procedure interface_all
322 -- and will allow the user to enter the organization_id
323 -- and the number of the concurrent requests needed.
324
325 procedure interface_ALL_wrp(errbuf OUT NOCOPY VARCHAR2,
326 retcode OUT NOCOPY VARCHAR2,
327 p_mode IN VARCHAR2 DEFAULT 'ALL',
328 p_stop_id IN NUMBER DEFAULT NULL,
329 p_delivery_id IN NUMBER DEFAULT NULL,
330 p_log_level IN NUMBER DEFAULT 0,
331 p_batch_id IN NUMBER DEFAULT NULL,
332
333 p_trip_type IN VARCHAR2 DEFAULT NULL,
334 p_organization_id IN NUMBER DEFAULT NULL,
335 p_num_requests IN NUMBER DEFAULT NULL,
336 p_stops_per_batch IN NUMBER DEFAULT NULL);
337 END WSH_SHIP_CONFIRM_ACTIONS;
[Home] [Help]