[Home] [Help]
PACKAGE: APPS.WMS_DEVICE_INTEGRATION_PVT
Source
1 PACKAGE WMS_Device_Integration_PVT AS
2 /* $Header: WMSDEVPS.pls 120.2.12000000.1 2007/01/16 06:52:15 appldev 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 ITEM_TB CONSTANT VARCHAR2(15) := ' <ITEM>';
107 ITEM_TE CONSTANT VARCHAR2(15) := '</ITEM>';
108 REVISION_TB CONSTANT VARCHAR2(15) := ' <REVISION>';
109 REVISION_TE CONSTANT VARCHAR2(15) := '</REVISION>';
110 QUANTITY_TB CONSTANT VARCHAR2(15) := ' <QUANTITY>';
111 QUANTITY_TE CONSTANT VARCHAR2(15) := '</QUANTITY>';
112 UOM_TB CONSTANT VARCHAR2(15) := ' <UOM>';
113 UOM_TE CONSTANT VARCHAR2(15) := '</UOM>';
114 LOT_TB CONSTANT VARCHAR2(15) := ' <LOT>';
115 LOT_TE CONSTANT VARCHAR2(15) := '</LOT>';
116 LOTQTY_TB CONSTANT VARCHAR2(15) := ' <LOTQTY>';
117 LOTQTY_TE CONSTANT VARCHAR2(15) := '</LOTQTY>';
118 SERIAL_TB CONSTANT VARCHAR2(15) := ' <SERIAL>';
119 SERIAL_TE CONSTANT VARCHAR2(15) := '</SERIAL>';
120 SO_TB CONSTANT VARCHAR2(15) := ' <ORDERNUMBER>';
121 SO_TE CONSTANT VARCHAR2(15) := '</ORDERNUMBER>';
122
123 --For RFID
124 TIMESTAMP_TB CONSTANT VARCHAR2(15) := '<TIMESTAMP>';
125 TIMESTAMP_TE CONSTANT VARCHAR2(15) := '</TIMESTAMP>';
126 ERRORCODE_TB CONSTANT VARCHAR2(15) := '<ERRORCODE>';
127 ERRORCODE_TE CONSTANT VARCHAR2(15) := '</ERRORCODE>';
128
129 ---------------------------------------------------------------------
130 -- Global constants for Calling Context
131 ---------------------------------------------------------------------
132 DEV_REQ_AUTO CONSTANT VARCHAR2(10) := 'A'; -- Auto
133 DEV_REQ_USER CONSTANT VARCHAR2(10) := 'U'; -- User Initiated
134
135
136
137
138 ---------------------------------------------------------------------
139 -- PROCEDURE DEVICE_REQUEST
140 --
141 -- Purpose
142 -- To initiate a request to a device, in the context of processing
143 -- a transaction. The details of the transactions can be passed
144 -- either directly or with reference to a transaction table. If Lot or
145 -- Serial details have to be passed, then the Transaction-reference
146 -- HAS to be used.
147 -- The context of the transaction is specified by the BusinessEvent
148 -- and the Calling-Context. BusinessEvent roughly specified the
149 -- transaction type. Calling-Context indicates whether this request
150 -- is being made in response to the user explicitly pressing some
151 -- control-key or automatically from the Mobile page.
152 --
153 -- Input Parameters
154 -- p_bus_event : Business Event in the context of which this request
155 -- is initiated (Globals defined above )
156 -- p_call_ctx : Calling Context :Automatic, User-Initiated
157 -- (Globals defined above)
158 -- p_task_trx_id : Reference to Transaction
159 -- DELIVERY_DETAIL_ID of WSH_DELIVERY_DETAILS for BusEvent:Ship-Confirm
160 -- HEADER_ID of MTL_TXN_REQUEST_LINES for BusEvent:PickRelease, WIPPickRelease
161 -- TRANSACTION_TEMP_ID of MTL_MATERIAL_TRANSACTIONS_TEMP: for all other BusEvnts
162 -- p_org_id : Orgainzation Id
163 -- p_item_id : Inventory Item Id
164 -- p_subinv : Subinventory Code
165 -- p_locator_id : Locator Id
166 -- p_lpn_id : LPN Id
167 -- p_xfr_org_id : Transfer Organization Id
168 -- p_xfr_subinv : Transfer Subinventory Code
169 -- p_xfr_locator_id : Transfer Locator Id
170 -- p_trx_qty : Transaction Quantity
171 -- p_trx_uom : Transaction UOM
172 -- p_rev : Revision
173 --
174 -- Input/Output Parameter
175 -- p_request_id : For business event 'Task Complete', it is an input parameter
176 -- indicating the parent request ID
177 -- For other busniess event, it is an output parameter
178 -- indicating the request ID of the new device request
179 --
180 -- Output Parameters
181 -- x_return_status : Return status: FND_API.G_RET_STS_SUCCESS, .G_RET_STS_FAILURE
182 -- x_request_msg : Any informational message from device
183 -- x_msg_count : Error Message Count
184 -- x_msg_data : Error Message
185 --
186 --
187 ---------------------------------------------------------------------
188 PROCEDURE DEVICE_REQUEST(
189 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
190 p_bus_event IN NUMBER,
191 p_call_ctx IN VARCHAR2 ,
192 p_task_trx_id IN NUMBER := NULL,
193 p_org_id IN NUMBER := NULL,
194 p_item_id IN NUMBER := NULL,
195 p_subinv IN VARCHAR2 := NULL,
196 p_locator_id IN NUMBER := NULL,
197 p_lpn_id IN NUMBER := NULL,
198 p_xfr_org_id IN NUMBER := NULL,
199 p_xfr_subinv IN VARCHAR2 := NULL,
200 p_xfr_locator_id IN NUMBER := NULL,
201 p_trx_qty IN NUMBER := NULL,
202 p_trx_uom IN VARCHAR2 := NULL,
203 p_rev IN VARCHAR2 := NULL,
204 x_request_msg OUT NOCOPY VARCHAR2,
205 x_return_status OUT NOCOPY VARCHAR2,
206 x_msg_count OUT NOCOPY NUMBER,
207 x_msg_data OUT NOCOPY VARCHAR2,
208 p_request_id IN OUT NOCOPY NUMBER
209 );
210
211 PROCEDURE DEVICE_REQUEST(
212 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
213 p_bus_event IN NUMBER,
214 p_call_ctx IN VARCHAR2 ,
215 p_task_trx_id IN NUMBER := NULL,
216 p_org_id IN NUMBER := NULL,
217 p_item_id IN NUMBER := NULL,
218 p_subinv IN VARCHAR2 := NULL,
219 p_locator_id IN NUMBER := NULL,
220 p_lpn_id IN NUMBER := NULL,
221 p_xfr_org_id IN NUMBER := NULL,
222 p_xfr_subinv IN VARCHAR2 := NULL,
223 p_xfr_locator_id IN NUMBER := NULL,
224 p_trx_qty IN NUMBER := NULL,
225 p_trx_uom IN VARCHAR2 := NULL,
226 p_rev IN VARCHAR2 := NULL,
227 x_request_msg OUT NOCOPY VARCHAR2,
228 x_return_status OUT NOCOPY VARCHAR2,
229 x_msg_count OUT NOCOPY NUMBER,
230 x_msg_data OUT NOCOPY VARCHAR2
231 );
232
233 --WMS-OPM
234 PROCEDURE DEVICE_REQUEST(
235 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
236 p_bus_event IN NUMBER,
237 p_call_ctx IN VARCHAR2 ,
238 p_task_trx_id IN NUMBER := NULL,
239 p_org_id IN NUMBER := NULL,
240 p_item_id IN NUMBER := NULL,
241 p_subinv IN VARCHAR2 := NULL,
242 p_locator_id IN NUMBER := NULL,
243 p_lpn_id IN NUMBER := NULL,
244 p_xfr_org_id IN NUMBER := NULL,
245 p_xfr_subinv IN VARCHAR2 := NULL,
246 p_xfr_locator_id IN NUMBER := NULL,
247 p_trx_qty IN NUMBER := NULL,
248 p_trx_uom IN VARCHAR2 := NULL,
249 p_rev IN VARCHAR2 := NULL,
250 x_request_msg OUT NOCOPY VARCHAR2,
251 x_return_status OUT NOCOPY VARCHAR2,
252 x_msg_count OUT NOCOPY NUMBER,
253 x_msg_data OUT NOCOPY VARCHAR2,
254 p_request_id IN OUT NOCOPY NUMBER,
255 p_device_id IN NUMBER
256 );
257
258 ---------------------------------------------------------------------
259 -- PROCEDURE RESUBMIT_REQUEST
260 --
261 -- Purpose: To resubmit a device request .
262 -- It is used by concurrent program WMSDEVRR
263 --
264 -- Input Parameters
265 -- p_request_id, p_task_trx_id, p_sequence_id to identify the request line
266 -- Output Parameters
267 -- return status and error messages
268 ---------------------------------------------------------------------
269 PROCEDURE RESUBMIT_REQUEST(
270 x_retcode OUT NOCOPY VARCHAR2,
271 x_errbuf OUT NOCOPY VARCHAR2,
272 p_request_id IN NUMBER,
273 p_device_id IN NUMBER := null,
274 p_task_trx_id IN NUMBER := null,
275 p_sequence_id IN NUMBER := NULL,
276 P_business_event_id IN NUMBER
277 );
278
279
280
281 /* OBSOLETED :this procedure has been moved to WMSPURGS.pls/WMSPURGB.pls */
282 -------------------------------------------------------------------------
283 -- PROCEDURE TO purge wms TABLES
284 --
285 -- Purpose: Concurrent Program to puge obsolete data from WMS tables
286 -- The following tables are obsoleted
287 -- * wms_device_requests_hist
288 -- * wms_lpn_histories
289 -- * wms_dispatched_tasks_history
290 -- * wms_exceptions
291 -- * wms_lpn_process_temp
292 --
293 --------------------------------------------------------------------------
294 /*PROCEDURE purge_wms(
295 x_errbuf OUT NOCOPY VARCHAR2,
296 x_retcode OUT NOCOPY NUMBER,
297 p_purge_date IN DATE,
298 p_orgid IN NUMBER,
299 p_purge_name IN VARCHAR2
300 );
301 */
302
303 -------------------------------------------------------------------------
304 -- PROCEDURE populate_history
305 --
306 -- Purpose: to populate the history table from wms_device_request table
307 -------------------------------------------------------------------------
308 PROCEDURE populate_history(
309 x_device_records_exist OUT NOCOPY VARCHAR2
310 );
311
312 PROCEDURE trace(p_msg IN VARCHAR2
313 , p_level IN NUMBER DEFAULT 1);
314
315
316 PROCEDURE is_device_set_up(p_org_id NUMBER,
317 p_bus_event_id NUMBER DEFAULT NULL,
318 x_return_status OUT NOCOPY VARCHAR2 );
319
320
321 FUNCTION select_Device(wdrrec WMS_DEVICE_REQUESTS%ROWTYPE,
322 p_autoenable VARCHAR2,
323 p_parent_request_id NUMBER
324 ) return NUMBER;
325
326
327 FUNCTION generate_xml_csv(p_device_id NUMBER,
328 p_iotype NUMBER
329 ) return NUMBER;
330
331 END WMS_Device_Integration_PVT;
332
333
334
335