1 PACKAGE WMS_Device_Integration_PUB AUTHID CURRENT_USER AS
2 /* $Header: WMSDEVIS.pls 120.2 2005/08/10 19:43:01 htnguyen noship $ */
3 /*#
4 * User defined device integration procedures allows user to process the WMS
5 * request to warehouse automation devices.
6 * @rep:scope public
7 * @rep:product WMS
8 * @rep:lifecycle active
9 * @rep:displayname User defined Device Interfacing API for WMS
10 * @rep:category BUSINESS_ENTITY WMS_DEVICE_INTEGRATION
11 */
12 --
13 -- Name
14 -- PROCEDURE SYNC_DEVICE_REQUEST
15 --
16 -- Purpose
17 -- This procedure is meant to initiate a request to a WMS Device.
18 -- It is expected that 3rd party device vendors would extend this
19 -- package to implement the actual logic required to interface with
20 -- the device.
21 -- The primary source of input to this package would be the Temporary
22 -- table WMS_DEVICE_REQUEST. Depending on the level of details desired,
23 -- the records in this table will be 'join'ed with other tables.
24 --
25 -- MTL_SYSTEM_ITEMS - Item
26 -- MTL_ITEM_LOCATIONS - Location
27 -- MTL_PARAMETERS - Organization
28 -- WMS_LICENSE_PLATE_NUMBERS - LPN
29 --
30 -- Input Parameters
31 -- p_request_id : Request Id
32 -- p_device_id : Device ID of the device this request is initiated for
33 -- p_resubmit_flag : N - Invoked in conjunction with processing transaction
34 -- Y - Invoked to resubmit a request to device
35 --
36 -- Output Parameters
37 -- x_status_code : Status of request. ( S = Success, E = Error)
38 -- x_status_msg : Optional status message related to this specific request
39 -- x_device_status: Optional status message related to the device
40 --
41 -- Implementation Example
42 -- A short example of an implementation of this Stub API is given below.
43 -- This example procedure opens a cursor to WMS_DEVIVE_REQUESTS and join
44 -- of other tables to retrieve details of the request. Each request
45 -- record is then packed and send across a database-pipe. A receiving
46 -- program could then wait on this Pipe and pass the data to
47 -- the actual device.
48 --
49 -- PROCEDURE SYNC_DEVICE_REQUEST(
50 -- p_request_id IN NUMBER,
51 -- p_device_id IN NUMBER,
52 -- p_resubmit_flag IN VARCHAR2,
53 -- x_status_code OUT VARCHAR2,
54 -- x_status_msg OUT VARCHAR2,
55 -- x_device_status OUT VARCHAR2 ) IS
56 -- cursor crs_wdr is
57 -- select wd.name,
58 -- ml1.meaning business_event,
59 -- wdr.subinventory_code ,
60 -- mil.CONCATENATED_SEGMENTS locator,
61 -- WLPN.license_plate_number lpn,
62 -- msi.segment1 item,
63 -- wdr.transaction_quantity quantity
64 -- from wms_devices wd,
65 -- mfg_lookups ml1,
66 -- wms_device_requests wdr,
67 -- mtl_item_locations_kfv mil, WMS_license_plate_numbers WLPN,
68 -- mtl_system_items msi
69 -- where
70 -- ml1.LOOKUP_TYPE = 'WMS_BUS_EVENT_TYPES'
71 -- and ml1.LOOKUP_CODE = wdr.business_event_id
72 -- and wd.device_id = wdr.device_id
73 -- and mil.ORGANIZATION_ID (+) = wdr.organization_id
74 -- and mil.SUBINVENTORY_CODE(+) = wdr.SUBINVENTORY_CODE
75 -- and mil.INVENTORY_LOCATION_ID(+) = wdr.LOCATOR_ID
76 -- and WLPN.lpn_id(+) = wdr.lpn_id
77 -- and msi.organization_id(+) = wdr.organization_id
78 -- and msi.inventory_item_id(+) = wdr.inventory_item_id
79 -- and wdr.device_id = p_device_id;
80 -- l_stat number;
81 -- l_pipename varchar2(32) := 'devoutpipe';
82 -- l_packedonce boolean := false;
83 --BEGIN
84 -- for curreq in crs_wdr loop
85 -- dbms_output.put_line('** start of Line **');
86 -- if ( l_packedonce != true ) then
87 -- dbms_pipe.pack_message(curreq.name);
88 -- l_packedonce := true;
89 -- end if;
90 -- dbms_pipe.pack_message(curreq.business_event);
91 -- dbms_pipe.pack_message(curreq.subinventory_code);
92 -- dbms_pipe.pack_message(substr(curreq.locator, 1, 15));
93 -- dbms_pipe.pack_message(curreq.lpn);
94 -- dbms_pipe.pack_message(curreq.item);
95 -- dbms_pipe.pack_message(curreq.quantity);
96 -- end loop;
97 -- dbms_pipe.pack_message('_STOP_');
98 -- l_stat := dbms_pipe.send_message(l_pipename);
99 -- if l_stat != 0 then
100 -- inv_trx_util_pub.trace('Error in sending to pipe', 'DEVINT', 1);
101 -- x_status_code := 'E';
102 -- else
103 -- inv_trx_util_pub.trace('OK in sending to pipe', 'DEVINT', 6);
104 -- x_status_code := 'S';
105 -- end if;
106 --END;
107 --
108 ---
109 ------------------------------------------------------------------------
110 /*#
111 * This procedure SYNC_DEVICE_REQUEST is meant to initiate a request to a WMS Device.
112 * It is expected that 3rd party device vendors would extend this
113 * package to implement the actual logic required to interface with
114 * the device.
115 * The primary source of input to this package would be the Temporary
116 * table WMS_DEVICE_REQUEST. Depending on the level of details desired,
117 * the records in this table will be joined with other tables.
118 *
119 * MTL_SYSTEM_ITEMS - Item
120 * MTL_ITEM_LOCATIONS - Location
121 * MTL_PARAMETERS - Organization
122 * WMS_LICENSE_PLATE_NUMBERS - LPN
123 * Implementation Example:
124 * A short example of an implementation of this Stub API is given below.
125 * This example procedure opens a cursor to WMS_DEVIVE_REQUESTS and join
126 * of other tables to retrieve details of the request. Each request
127 * record is then packed and send across a database-pipe. A receiving
128 * program could then wait on this Pipe and pass the data to
129 * the actual device.
130 * @ param x_status_code Status of request. ( S = Success, E = Error)
131 * @ paraminfo {@rep:required}
132 * @ param x_status_msg Optional status message related to this specific request
133 * @ paraminfo {@rep:required}
134 * @ param x_device_status Optional status message related to the device
135 * @ paraminfo {@rep:required}
136 * @ param p_request_id Request Id generated by WMS
137 * @ paraminfo {@rep:required}
138 * @ param p_device_id Device ID of the device this request is initiated for
139 * @ paraminfo {@rep:required}
140 * @ param p_resubmit_flag N - Invoked in conjunction with processing transaction,Y - Invoked to resubmit a request to device
141 * @ paraminfo {@rep:required}
142 * @rep:scope public
143 * @rep:lifecycle active
144 * @rep:displayname Process WMS request to hardware devices
145 */
146 PROCEDURE SYNC_DEVICE_REQUEST(
147 p_request_id IN NUMBER,
148 p_device_id IN NUMBER,
149 p_resubmit_flag IN VARCHAR2,
150 x_status_code OUT NOCOPY VARCHAR2,
151 x_status_msg OUT NOCOPY VARCHAR2,
152 x_device_status OUT NOCOPY VARCHAR2
153 );
154
155
156
157
158 --
159 -- Name
160 -- UPDATE_REQUEST
161 --
162 -- Purpose
163 -- This procedure is provided to update the status of a request
164 -- from a transaction cycle seperate from the one in which the
165 -- request was initiated. This is meant to update a device request
166 -- in an asynchronous mode.
167 --
168 -- Input Parameters
169 -- p_request_id : Request Id
170 -- p_device_id : Device Identifier, if data comprising this request was
171 -- directed to multiple devices
172 -- p_status_code : Status of request. ( S = Success, E = Error)
173 -- p_status_msg : Optional status message related to this specific request
174 --
175 --
176 /*#
177 * This procedure UPDATE_REQUEST is used to update device requests records
178 * after device has processed records
179 * @ param p_request_id Request id of the record
180 * @ paraminfo {@rep:required}
181 * @ param p_device_id Device ID associated with the request
182 * @ paraminfo {@rep:optional}
183 * @ param p_status_code Status after device had processed the record
184 * @ paraminfo {@rep:required}
185 * @ param p_status_msg Any message after device has processed the record
186 * @ paraminfo {@rep:optional}
187 * @rep:scope public
188 * @rep:lifecycle active
189 * @rep:displayname Update device history records
190 */
191 PROCEDURE UPDATE_REQUEST(
192 p_request_id IN NUMBER,
193 p_device_id IN NUMBER := NULL,
194 p_status_code IN VARCHAR2,
195 p_status_msg IN VARCHAR2 := NULL
196 );
197
198 -- Name
199 -- PROCEDURE SYNC_DEVICE
200 --
201 -- Purpose
202 -- This procedure is meant to invoke or terminate a WMS Device.
203 -- It is expected that 3rd party device vendors would extend this
204 -- package to implement the actual logic required to interface with
205 -- the device.
206 --
207 -- Input Parameters
208 -- p_organization_id : Request ID
209 -- p_device_id : Device ID of the device
210 -- p_employee_id : Employee ID of the employee who invokes or
211 -- terminates the device.
212 -- p_sign_on_flag : 'Y' when signing on
213 -- 'N' when signing off
214 --
215 -- Output Parameters
216 -- x_status_code : Status of request. ( S = Success, E = Error)
217 -- x_device_status: Optional status message related to the device
218 --
219 --
220 /*#
221 * This procedure SYNC_DEVICE is used to invoke or terminate a WMS
222 * Device requests.
223 * It is expected that 3rd party device vendors would extend this
224 * package to implement the actual logic required to interface with
225 * the device.
226 * @ param p_organization_id organization id for the request
227 * @ paraminfo {@rep:required}
228 * @ param p_device_id Device ID associated with the request
229 * @ paraminfo {@rep:optional}
230 * @ param p_employee_id user identifier who is working (invokes or terminates) on device
231 * @ paraminfo {@rep:required}
232 * @ param p_sign_on_flag 'Y' when signing on,'N' when signing off
233 * @ paraminfo {@rep:required}
234 * @ param x_status_code Status of request. ( S = Success, E = Error)
235 * @ paraminfo {@rep:required}
236 * @ param x_device_status Optional status message related to the device
237 * @ paraminfo {@rep:required}
238 * @rep:scope public
239 * @rep:lifecycle active
240 * @rep:displayname API to invoke or terminate a WMS Device requests
241 */
242 PROCEDURE SYNC_DEVICE(
243 p_organization_id IN NUMBER,
244 p_device_id IN NUMBER,
245 p_employee_id IN NUMBER,
246 p_sign_on_flag IN VARCHAR2,
247 x_status_code OUT NOCOPY VARCHAR2,
248 x_device_status OUT NOCOPY VARCHAR2 );
249
250 END WMS_Device_Integration_PUB;