[Home] [Help]
PACKAGE: APPS.WMS_DEVICE_INTEGRATION_PVT
Source
1 PACKAGE WMS_Device_Integration_PVT AUTHID CURRENT_USER AS
2 /* $Header: WMSDEVPS.pls 120.2.12010000.4 2009/09/25 13:25:39 pbonthu ship $ */
3
4
5 ---------------------------------------------------------------------
6 -- Global constants for Business Events
7 -- These constants defined in MFG_LOOKUP for type WMS_BUS_EVENT_TYPE
8 ---------------------------------------------------------------------
9 WMS_BE_DIRECT_RECEIPT CONSTANT NUMBER := 1;
10 WMS_BE_INSPECTION CONSTANT NUMBER := 2;
11 WMS_BE_PUTAWAY_DROP CONSTANT NUMBER := 3;
12 WMS_BE_CYCLE_COUNT CONSTANT NUMBER := 4;
13 WMS_BE_RECEIPT CONSTANT NUMBER := 5;
14 WMS_BE_ISSUE CONSTANT NUMBER := 6;
15 WMS_BE_SUB_XFR CONSTANT NUMBER := 7;
16 WMS_BE_ORG_XFR CONSTANT NUMBER := 8;
17 WMS_BE_PICK_DROP CONSTANT NUMBER := 9;
18 WMS_BE_PICK_LOAD CONSTANT NUMBER := 10;
19 WMS_BE_PICK_RELEASE CONSTANT NUMBER := 11;
20 WMS_BE_WIP_PICK_RELEASE CONSTANT NUMBER := 12;
21 WMS_BE_SHIP_CONFIRM CONSTANT NUMBER := 13;
22 wms_be_mo_task_alloc CONSTANT NUMBER := 17;
23
24 -- Following 4 numbers have been changed in patch set I
25 WMS_BE_TASK_COMPLETE CONSTANT NUMBER := 51;
26 WMS_BE_TASK_SKIP CONSTANT NUMBER := 52;
27 WMS_BE_TASK_CANCEL CONSTANT NUMBER := 53;
28 WMS_BE_TASK_CONFIRM CONSTANT NUMBER := 54;
29 wms_be_load_confirm CONSTANT NUMBER := 56;
30
31 -- Following 4 numbers have been added in patch set J
32 WMS_BE_TRUCK_LOAD CONSTANT NUMBER := 14;
33 WMS_BE_TRUCK_LOAD_SHIP CONSTANT NUMBER := 15;
34 WMS_BE_STD_INSP_RECEIPT CONSTANT NUMBER := 16;
35
36 WMS_BE_RFID_ERROR CONSTANT NUMBER := 55;
37
38
39
40 ---------------------------------------------------------------------
41 -- Global constants for WMS IO Types
42 -- These constants defined in MFG_LOOKUP for type WMS_DEVICE_IO_TYPES
43 ---------------------------------------------------------------------
44 WMS_DEV_IO_XML CONSTANT NUMBER := 1;
45 WMS_DEV_IO_API CONSTANT NUMBER := 2;
46 WMS_DEV_IO_TABLE CONSTANT NUMBER := 3;
47 WMS_DEV_IO_CSV CONSTANT NUMBER := 4;
48
49
50 ---------------------------------------------------------------------
51 -- Global constants for Device Level Types for mapping to Business Events
52 --
53 ---------------------------------------------------------------------
54 DEVICE_LEVEL_NONE CONSTANT NUMBER := 0;
55 DEVICE_LEVEL_ORG CONSTANT NUMBER := 100;
56 DEVICE_LEVEL_SUB CONSTANT NUMBER := 200;
57 DEVICE_LEVEL_LOCATOR CONSTANT NUMBER := 300;
58 DEVICE_LEVEL_USER CONSTANT NUMBER := 400;
59
60 --Global variable to indicate whether to take any device_request specific
61 --action, OR to call DEVICE_REQUEST api
62 wms_call_device_request NUMBER; -- 0 = No, 1 = Yes
63 --Global variable for pick_release only, to stamp request_id for the
64 --complete batch inside allocation engine
65 wms_pkRel_dev_req_id NUMBER;
66 --Global variable
67 wms_insert_lotSer_rec_WDR NUMBER :=0;-- 0 = No, 1 = Yes
68
69
70 ---------------------------------------------------------------------
71 -- Global constants for XML tag for DEVICE and TASK
72 ---------------------------------------------------------------------
73 XML_HEADER CONSTANT VARCHAR2(100) := '<?xml version = ''1.0''?>';
74 TAG_E CONSTANT VARCHAR2(1) := '>';
75 DEVICEH_TB CONSTANT VARCHAR2(15) := '<DEVICE';
76 DEVICE_TE CONSTANT VARCHAR2(15) := '</DEVICE>';
77 TASK_TB CONSTANT VARCHAR2(15) := ' <TASK>';
78 TASK_TE CONSTANT VARCHAR2(15) := ' </TASK>';
79 DEVICE_TB CONSTANT VARCHAR2(15) := ' <DEVICE>';
80 REQUESTID_TB CONSTANT VARCHAR2(15) := ' <REQUESTID>';
81 REQUESTID_TE CONSTANT VARCHAR2(15) := '</REQUESTID>';
82 RELATIONID_TB CONSTANT VARCHAR2(15) := ' <RELATIONID>';
83 RELATIONID_TE CONSTANT VARCHAR2(15) := '</RELATIONID>';
84 TASKTYPE_TB CONSTANT VARCHAR2(15) := ' <TASKTYPE>';
85 TASKTYPE_TE CONSTANT VARCHAR2(15) := '</TASKTYPE>';
86 BUSINESSEVENT_TB CONSTANT VARCHAR2(20) := ' <BUSINESSEVENT>';
87 BUSINESSEVENT_TE CONSTANT VARCHAR2(20) := '</BUSINESSEVENT>';
88 TASKID_TB CONSTANT VARCHAR2(15) := ' <TASKID>';
89 TASKID_TE CONSTANT VARCHAR2(15) := '</TASKID>';
90 SEQUENCEID_TB CONSTANT VARCHAR2(15) := ' <SEQUENCEID>';
91 SEQUENCEID_TE CONSTANT VARCHAR2(15) := '</SEQUENCEID>';
92 ORG_TB CONSTANT VARCHAR2(15) := ' <ORG>';
93 ORG_TE CONSTANT VARCHAR2(15) := '</ORG>';
94 SUB_TB CONSTANT VARCHAR2(15) := ' <SUB>';
95 SUB_TE CONSTANT VARCHAR2(15) := '</SUB>';
96 LOC_TB CONSTANT VARCHAR2(15) := ' <LOC>';
97 LOC_TE CONSTANT VARCHAR2(15) := '</LOC>';
98 TRANSFERORG_TB CONSTANT VARCHAR2(15) := ' <TRANSFERORG>';
99 TRANSFERORG_TE CONSTANT VARCHAR2(15) := '</TRANSFERORG>';
100 TRANSFERSUB_TB CONSTANT VARCHAR2(15) := ' <TRANSFERSUB>';
101 TRANSFERSUB_TE CONSTANT VARCHAR2(15) := '</TRANSFERSUB>';
102 TRANSFERLOC_TB CONSTANT VARCHAR2(15) := ' <TRANSFERLOC>';
103 TRANSFERLOC_TE CONSTANT VARCHAR2(15) := '</TRANSFERLOC>';
104 LPN_TB CONSTANT VARCHAR2(15) := ' <LPN>';
105 LPN_TE CONSTANT VARCHAR2(15) := '</LPN>';
106 XFERLPN_TB CONSTANT VARCHAR2(15) := ' <XFERLPN>'; --Added for Bug#8512121
107 XFERLPN_TE CONSTANT VARCHAR2(15) := '</XFERLPN>'; --Added for Bug#8512121
108 ITEM_TB CONSTANT VARCHAR2(15) := ' <ITEM>';
109 ITEM_TE CONSTANT VARCHAR2(15) := '</ITEM>';
110 REVISION_TB CONSTANT VARCHAR2(15) := ' <REVISION>';
111 REVISION_TE CONSTANT VARCHAR2(15) := '</REVISION>';
112 QUANTITY_TB CONSTANT VARCHAR2(15) := ' <QUANTITY>';
113 QUANTITY_TE CONSTANT VARCHAR2(15) := '</QUANTITY>';
114 UOM_TB CONSTANT VARCHAR2(15) := ' <UOM>';
115 UOM_TE CONSTANT VARCHAR2(15) := '</UOM>';
116 LOT_TB CONSTANT VARCHAR2(15) := ' <LOT>';
117 LOT_TE CONSTANT VARCHAR2(15) := '</LOT>';
118 LOTQTY_TB CONSTANT VARCHAR2(15) := ' <LOTQTY>';
119 LOTQTY_TE CONSTANT VARCHAR2(15) := '</LOTQTY>';
120 SERIAL_TB CONSTANT VARCHAR2(15) := ' <SERIAL>';
121 SERIAL_TE CONSTANT VARCHAR2(15) := '</SERIAL>';
122 SO_TB CONSTANT VARCHAR2(15) := ' <ORDERNUMBER>';
123 SO_TE CONSTANT VARCHAR2(15) := '</ORDERNUMBER>';
124
125 --For RFID
126 TIMESTAMP_TB CONSTANT VARCHAR2(15) := '<TIMESTAMP>';
127 TIMESTAMP_TE CONSTANT VARCHAR2(15) := '</TIMESTAMP>';
128 ERRORCODE_TB CONSTANT VARCHAR2(15) := '<ERRORCODE>';
129 ERRORCODE_TE CONSTANT VARCHAR2(15) := '</ERRORCODE>';
130
131 ---------------------------------------------------------------------
132 -- Global constants for Calling Context
133 ---------------------------------------------------------------------
134 DEV_REQ_AUTO CONSTANT VARCHAR2(10) := 'A'; -- Auto
135 DEV_REQ_USER CONSTANT VARCHAR2(10) := 'U'; -- User Initiated
136
137
138
139
140 ---------------------------------------------------------------------
141 -- PROCEDURE DEVICE_REQUEST
142 --
143 -- Purpose
144 -- To initiate a request to a device, in the context of processing
145 -- a transaction. The details of the transactions can be passed
146 -- either directly or with reference to a transaction table. If Lot or
147 -- Serial details have to be passed, then the Transaction-reference
148 -- HAS to be used.
149 -- The context of the transaction is specified by the BusinessEvent
150 -- and the Calling-Context. BusinessEvent roughly specified the
151 -- transaction type. Calling-Context indicates whether this request
152 -- is being made in response to the user explicitly pressing some
153 -- control-key or automatically from the Mobile page.
154 --
155 -- Input Parameters
156 -- p_bus_event : Business Event in the context of which this request
157 -- is initiated (Globals defined above )
158 -- p_call_ctx : Calling Context :Automatic, User-Initiated
159 -- (Globals defined above)
160 -- p_task_trx_id : Reference to Transaction
161 -- DELIVERY_DETAIL_ID of WSH_DELIVERY_DETAILS for BusEvent:Ship-Confirm
162 -- HEADER_ID of MTL_TXN_REQUEST_LINES for BusEvent:PickRelease, WIPPickRelease
163 -- TRANSACTION_TEMP_ID of MTL_MATERIAL_TRANSACTIONS_TEMP: for all other BusEvnts
164 -- p_org_id : Orgainzation Id
165 -- p_item_id : Inventory Item Id
166 -- p_subinv : Subinventory Code
167 -- p_locator_id : Locator Id
168 -- p_lpn_id : LPN Id
169 -- p_xfr_org_id : Transfer Organization Id
170 -- p_xfr_subinv : Transfer Subinventory Code
171 -- p_xfr_locator_id : Transfer Locator Id
172 -- p_trx_qty : Transaction Quantity
173 -- p_trx_uom : Transaction UOM
174 -- p_rev : Revision
175 --
176 -- Input/Output Parameter
177 -- p_request_id : For business event 'Task Complete', it is an input parameter
178 -- indicating the parent request ID
179 -- For other busniess event, it is an output parameter
180 -- indicating the request ID of the new device request
181 --
182 -- Output Parameters
183 -- x_return_status : Return status: FND_API.G_RET_STS_SUCCESS, .G_RET_STS_FAILURE
184 -- x_request_msg : Any informational message from device
185 -- x_msg_count : Error Message Count
186 -- x_msg_data : Error Message
187 --
188 --
189 ---------------------------------------------------------------------
190 PROCEDURE DEVICE_REQUEST(
191 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
192 p_bus_event IN NUMBER,
193 p_call_ctx IN VARCHAR2 ,
194 p_task_trx_id IN NUMBER := NULL,
195 p_org_id IN NUMBER := NULL,
196 p_item_id IN NUMBER := NULL,
197 p_subinv IN VARCHAR2 := NULL,
198 p_locator_id IN NUMBER := NULL,
199 p_lpn_id IN NUMBER := NULL,
200 p_xfer_lpn_id IN NUMBER := NULL, --Added for Bug#8778050
201 p_xfr_org_id IN NUMBER := NULL,
202 p_xfr_subinv IN VARCHAR2 := NULL,
203 p_xfr_locator_id IN NUMBER := NULL,
204 p_trx_qty IN NUMBER := NULL,
205 p_trx_uom IN VARCHAR2 := NULL,
206 p_rev IN VARCHAR2 := NULL,
207 x_request_msg OUT NOCOPY VARCHAR2,
208 x_return_status OUT NOCOPY VARCHAR2,
209 x_msg_count OUT NOCOPY NUMBER,
210 x_msg_data OUT NOCOPY VARCHAR2,
211 p_request_id IN OUT NOCOPY NUMBER
212 );
213
214 PROCEDURE DEVICE_REQUEST(
215 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
216 p_bus_event IN NUMBER,
217 p_call_ctx IN VARCHAR2 ,
218 p_task_trx_id IN NUMBER := NULL,
219 p_org_id IN NUMBER := NULL,
220 p_item_id IN NUMBER := NULL,
221 p_subinv IN VARCHAR2 := NULL,
222 p_locator_id IN NUMBER := NULL,
223 p_lpn_id IN NUMBER := NULL,
224 p_xfr_org_id IN NUMBER := NULL,
225 p_xfr_subinv IN VARCHAR2 := NULL,
226 p_xfr_locator_id IN NUMBER := NULL,
227 p_trx_qty IN NUMBER := NULL,
228 p_trx_uom IN VARCHAR2 := NULL,
229 p_rev IN VARCHAR2 := NULL,
230 x_request_msg OUT NOCOPY VARCHAR2,
231 x_return_status OUT NOCOPY VARCHAR2,
232 x_msg_count OUT NOCOPY NUMBER,
233 x_msg_data OUT NOCOPY VARCHAR2
234 );
235
236 --WMS-OPM
237 PROCEDURE DEVICE_REQUEST(
238 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
239 p_bus_event IN NUMBER,
240 p_call_ctx IN VARCHAR2 ,
241 p_task_trx_id IN NUMBER := NULL,
242 p_org_id IN NUMBER := NULL,
243 p_item_id IN NUMBER := NULL,
244 p_subinv IN VARCHAR2 := NULL,
245 p_locator_id IN NUMBER := NULL,
246 p_lpn_id IN NUMBER := NULL,
247 p_xfr_org_id IN NUMBER := NULL,
248 p_xfr_subinv IN VARCHAR2 := NULL,
249 p_xfr_locator_id IN NUMBER := NULL,
250 p_trx_qty IN NUMBER := NULL,
251 p_trx_uom IN VARCHAR2 := NULL,
252 p_rev IN VARCHAR2 := NULL,
253 x_request_msg OUT NOCOPY VARCHAR2,
254 x_return_status OUT NOCOPY VARCHAR2,
255 x_msg_count OUT NOCOPY NUMBER,
256 x_msg_data OUT NOCOPY VARCHAR2,
257 p_request_id IN OUT NOCOPY NUMBER,
258 p_device_id IN NUMBER
259 );
260
261 ---------------------------------------------------------------------
262 -- PROCEDURE RESUBMIT_REQUEST
263 --
264 -- Purpose: To resubmit a device request .
265 -- It is used by concurrent program WMSDEVRR
266 --
267 -- Input Parameters
268 -- p_request_id, p_task_trx_id, p_sequence_id to identify the request line
269 -- Output Parameters
270 -- return status and error messages
271 ---------------------------------------------------------------------
272 PROCEDURE RESUBMIT_REQUEST(
273 x_retcode OUT NOCOPY VARCHAR2,
274 x_errbuf OUT NOCOPY VARCHAR2,
275 p_request_id IN NUMBER,
276 p_device_id IN NUMBER := null,
277 p_task_trx_id IN NUMBER := null,
278 p_sequence_id IN NUMBER := NULL,
279 P_business_event_id IN NUMBER
280 );
281
282
283
284 /* OBSOLETED :this procedure has been moved to WMSPURGS.pls/WMSPURGB.pls */
285 -------------------------------------------------------------------------
286 -- PROCEDURE TO purge wms TABLES
287 --
288 -- Purpose: Concurrent Program to puge obsolete data from WMS tables
289 -- The following tables are obsoleted
290 -- * wms_device_requests_hist
291 -- * wms_lpn_histories
292 -- * wms_dispatched_tasks_history
293 -- * wms_exceptions
294 -- * wms_lpn_process_temp
295 --
296 --------------------------------------------------------------------------
297 /*PROCEDURE purge_wms(
298 x_errbuf OUT NOCOPY VARCHAR2,
299 x_retcode OUT NOCOPY NUMBER,
300 p_purge_date IN DATE,
301 p_orgid IN NUMBER,
302 p_purge_name IN VARCHAR2
303 );
304 */
305
306 -------------------------------------------------------------------------
307 -- PROCEDURE populate_history
308 --
309 -- Purpose: to populate the history table from wms_device_request table
310 -------------------------------------------------------------------------
311 PROCEDURE populate_history(
312 p_call_ctx IN VARCHAR2 := NULL,
313 p_bus_event IN NUMBER := NULL,
314 x_device_records_exist OUT NOCOPY VARCHAR2
315 );
316
317 PROCEDURE trace(p_msg IN VARCHAR2
318 , p_level IN NUMBER DEFAULT 1);
319
320
321 PROCEDURE is_device_set_up(p_org_id NUMBER,
322 p_bus_event_id NUMBER DEFAULT NULL,
323 x_return_status OUT NOCOPY VARCHAR2 );
324
325
326 FUNCTION select_Device(wdrrec WMS_DEVICE_REQUESTS%ROWTYPE,
327 p_autoenable VARCHAR2,
328 p_parent_request_id NUMBER
329 ) return NUMBER;
330
331
332 FUNCTION generate_xml_csv(p_device_id NUMBER,
333 p_iotype NUMBER
334 ) return NUMBER;
335
336 END WMS_Device_Integration_PVT;
337
338
339
340