1 PACKAGE wms_zones_pvt AUTHID CURRENT_USER AS
2 /* $Header: WMSZONES.pls 120.0.12010000.2 2009/08/03 06:46:45 ajunnikr ship $ */
3 -- Package : wms_zones_pvt
4 -- File : $RCSfile: WMSZONES.pls,v $
5 -- Content : Contains the
6 -- Description :
7 -- Notes :
8 -- Modified : Mon Jul 14 14:29:40 GMT+05:30 2003
9
10 TYPE wms_zone_loc_tbl_t IS TABLE OF wms_zone_locators_temp%ROWTYPE
11 INDEX BY BINARY_INTEGER;
12
13 TYPE zoneloc_rowid_t IS TABLE OF VARCHAR2(18) INDEX BY BINARY_INTEGER;
14 TYPE zoneloc_messages_t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
15
16 /**
17 * 'Pending removal from Zone' message
18 *
19 **/
20 g_remove_locators_message VARCHAR2(200) := 'Pending removal from Zone';
21
22 /**
23 * 'Pending addition to Zone' message
24 *
25 **/
26 g_add_locators_message VARCHAR2(200) := 'Pending addition to Zone';
27
28 PROCEDURE populate_grid(
29 p_zone_id NUMBER
30 , p_org_id NUMBER
31 , x_record_count OUT NOCOPY NUMBER
32 , x_return_status OUT NOCOPY VARCHAR2
33 , x_msg_data OUT NOCOPY VARCHAR2
34 );
35
36 /**
37 * Using the filter criteria given in the Add Locators form,
38 * inserts the locators into the table WMS_ZONE_LOCATORS_TEMP.
39
40 * @param p_fm_zone_id from_zone_id in the range. Will have a null value if the user doesnt choose a from_zone
41 * @param p_to_zone_id to_zone_id in the range. Can have a null value if the user doesnt choose a to_zone
42 * @param p_current_zone_id The zone_id of the current zone, for which more locators are being added
43 * @param p_fm_sub_code From Subinventory code
44 * @param p_to_sub_code To Subinventory Code
45 * @param p_fm_loc_id From Locator Id in a range of locators. Should contain a value only if either p_fm_sub_code or p_to_sub_code is populated.
46 * @param p_to_loc_id To Locator Id in a range of locators. Should contain a value only if either p_fm_sub_code or p_to_sub_code is populated.
47 * @param p_subinventory_status Status id of the subinventories
48 * @param p_locator_status Status id of the locators
49 * @param p_subinventory_type Subinventory Type
50 * @param p_locator_type Locator Type
51 * @param p_fm_picking_order Picking order of the Locators
52 * @param p_to_picking_order Picking order of the Locators
53 * @param p_fm_dropping_order Dropping order of the Locators
54 * @param p_to_dropping_order Dropping order of the Locators
55 * @param p_all_locators Indicates whether all locators is chosen
56 **/
57 PROCEDURE add_locators_to_grid (
58 p_fm_zone_id IN NUMBER DEFAULT NULL,
59 p_to_zone_id IN NUMBER DEFAULT NULL,
60 p_current_zone_id IN NUMBER DEFAULT NULL,
61 p_fm_sub_code IN VARCHAR2 DEFAULT NULL,
62 p_to_sub_code IN VARCHAR2 DEFAULT NULL,
63 p_fm_loc_id IN NUMBER DEFAULT NULL,
64 p_to_loc_id IN NUMBER DEFAULT NULL,
65 p_subinventory_status IN NUMBER DEFAULT NULL,
66 p_locator_status IN NUMBER DEFAULT NULL,
67 p_subinventory_type IN NUMBER DEFAULT NULL,
68 p_locator_type IN NUMBER DEFAULT NULL,
69 p_fm_picking_order IN NUMBER DEFAULT NULL,
70 p_to_picking_order IN NUMBER DEFAULT NULL,
71 p_fm_dropping_order IN NUMBER DEFAULT NULL,
72 p_to_dropping_order IN NUMBER DEFAULT NULL,
73 p_organization_id IN NUMBER,
74 p_mode IN NUMBER DEFAULT NULL,
75 p_type IN VARCHAR2 default 'A');
76
77 /**
78 * Contains code to insert records into wms_zones_b and
79 * wms_zones_tl
80
81 * @param x_return_status Return Status - Success, Error, Unexpected Error
82 * @param x_msg_data Contains any error messages added to the stack
83 * @param x_msg_count Contains the count of the messages added to the stack
84 * @param p_zone_id Zone_id
85 * @param p_zone_name Name of the new Zone
86 * @param p_description Description of the zone
87 * @param enabled_flag Flag to indicate whether the zone is enabled or not. '
88 Y' indicates that the zone is enabled.
89 'N' indicates that the zone is not enabled.
90 Any other value will be an error
91 * @param disable_date The date when the zone will be disabled.
92 This date cannot be less than the SYSDATE.
93 * @param p_organization_id Current Organization id
94 * @param p_attribute_category Attribute Category of the Zones Descriptive Flexfield
95 * @param p_attribute1 Attribute1
96 * @param p_attribute2 Attribute2
97 * @param p_attribute3 Attribute3
98 * @param p_attribute4 Attribute4
99 * @param p_attribute5 Attribute5
100 * @param p_attribute6 Attribute6
101 * @param p_attribute7 Attribute7
102 * @param p_attribute8 Attribute8
103 * @param p_attribute9 Attribute9
104 * @param p_attribute10 Attribute10
105 * @param p_attribute11 Attribute11
106 * @param p_attribute12 Attribute12
107 * @param p_attribute13 Attribute13
108 * @param p_attribute14 Attribute14
109 * @param p_attribute15 Attribute15
110 **/
111
112 PROCEDURE insert_wms_zones (
113 x_return_status OUT NOCOPY VARCHAR2,
114 x_msg_data OUT NOCOPY VARCHAR2,
115 x_msg_count OUT NOCOPY NUMBER,
116 p_zone_id IN NUMBER,
117 p_zone_name IN VARCHAR2,
118 p_description IN VARCHAR2,
119 p_type in varchar2,
120 p_enabled_flag IN VARCHAR2,
121 p_labor_enabled IN VARCHAR2,
122 p_disable_date IN DATE,
123 p_organization_id IN NUMBER,
124 p_attribute_category IN VARCHAR2,
125 p_attribute1 IN VARCHAR2,
126 p_attribute2 IN VARCHAR2,
127 p_attribute3 IN VARCHAR2,
128 p_attribute4 IN VARCHAR2,
129 p_attribute5 IN VARCHAR2,
130 p_attribute6 IN VARCHAR2,
131 p_attribute7 IN VARCHAR2,
132 p_attribute8 IN VARCHAR2,
133 p_attribute9 IN VARCHAR2,
134 p_attribute10 IN VARCHAR2,
135 p_attribute11 IN VARCHAR2,
136 p_attribute12 IN VARCHAR2,
137 p_attribute13 IN VARCHAR2,
138 p_attribute14 IN VARCHAR2,
139 p_attribute15 IN VARCHAR2,
140 p_creation_date IN DATE,
141 p_created_by IN NUMBER,
142 p_last_update_date IN DATE,
143 p_last_updated_by IN NUMBER,
144 p_last_update_login IN NUMBER
145 );
146
147 /**
148 * Contains code to update records in wms_zones_b and
149 * wms_zones_tl
150
151 * @param x_return_status Return Status - Success, Error, Unexpected Error
152 * @param x_msg_data Contains any error messages added to the stack
153 * @param x_msg_count Contains the count of the messages added to the stack
154 * @param p_zone_id Zone_id
155 * @param p_zone_name Name of the new Zone
156 * @param p_description Description of the zone
157 * @param enabled_flag Flag to indicate whether the zone is enabled or not. 'Y' indicates that the zone is enabled 'N' indicates that the zone is not enabled. Any other value will be an error
158 * @param disable_date The date when the zone will be disabled. This date cannot be less than the SYSDATE.
159 * @param p_organization_id Current Organization id
160 * @param p_attribute_category Attribute Category of the Zones Descriptive Flexfield
161 * @param p_attribute1 Attribute1
162 * @param p_attribute2 Attribute2
163 * @param p_attribute3 Attribute3
164 * @param p_attribute4 Attribute4
165 * @param p_attribute5 Attribute5
166 * @param p_attribute6 Attribute6
167 * @param p_attribute7 Attribute7
168 * @param p_attribute8 Attribute8
169 * @param p_attribute9 Attribute9
170 * @param p_attribute10 Attribute10
171 * @param p_attribute11 Attribute11
172 * @param p_attribute12 Attribute12
173 * @param p_attribute13 Attribute13
174 * @param p_attribute14 Attribute14
175 * @param p_attribute15 Attribute15
176
177
178 **/
179 PROCEDURE update_wms_zones (
180 x_return_status OUT NOCOPY VARCHAR2,
181 x_msg_data OUT NOCOPY VARCHAR2,
182 x_msg_count OUT NOCOPY NUMBER,
183 p_zone_id IN NUMBER,
184 p_zone_name IN VARCHAR2,
185 p_description IN VARCHAR2,
186 p_type in varchar2,
187 p_enabled_flag IN VARCHAR2,
188 p_labor_enabled IN VARCHAR2,
189 p_disable_date IN DATE,
190 p_organization_id IN NUMBER,
191 p_attribute_category IN VARCHAR2,
192 p_attribute1 IN VARCHAR2,
193 p_attribute2 IN VARCHAR2,
194 p_attribute3 IN VARCHAR2,
195 p_attribute4 IN VARCHAR2,
196 p_attribute5 IN VARCHAR2,
197 p_attribute6 IN VARCHAR2,
198 p_attribute7 IN VARCHAR2,
199 p_attribute8 IN VARCHAR2,
200 p_attribute9 IN VARCHAR2,
201 p_attribute10 IN VARCHAR2,
202 p_attribute11 IN VARCHAR2,
203 p_attribute12 IN VARCHAR2,
204 p_attribute13 IN VARCHAR2,
205 p_attribute14 IN VARCHAR2,
206 p_attribute15 IN VARCHAR2,
207 p_creation_date IN DATE,
208 p_created_by IN NUMBER,
209 p_last_update_date IN DATE,
210 p_last_updated_by IN NUMBER,
211 p_last_update_login IN NUMBER
212 );
213
214 /**
215 * This procedure saves the records from
216 * wms_zone_locators_temp to wms_zone_locators. For every
217 * record at a given index in the table p_zoneloc_messages
218 * table, we get the the corresponding rowid from the input
219 * parameter table p_zoneloc_rowid_t for the same index.
220 * If the value in p_zoneloc_messages_t is 0, the
221 * corresponding record will be inserted into the table.
222 * If the value in p_zoneloc_messages_t is 1, the
223 * corresponding record will be deleted from the table.
224 * Else do nothing.
225
226 * @param p_zoneloc_rowid_t Table of records containing the rowids of all the records to be inserted or deleted.
227 * @param p_zoneloc_messages_t Indicates whether the corresponding record should be inserted or deleted.
228 If the value is 0, the corresponding record will be inserted into the table.
229 If the value is 1, the corresponding record will be deleted from the table.
230 Else do nothing.
231 **/
232 PROCEDURE save_sel_locators(
233 p_zoneloc_rowid_t IN wms_zones_pvt.zoneloc_rowid_t,
234 p_zone_id IN wms_zone_locators.zone_id%TYPE);
235
236 /**
237 * This procedure saves all the records from
238 * wms_zone_locators_temp to wms_zone_locators, which have the
239 * message field containing the value 'Pending Addition to
240 * Zone'. All the records whose message field has a value
241 * 'Pending Deletion' will be deleted from the table.
242 **/
243 PROCEDURE save_all_locators(p_zone_id IN wms_zone_locators.zone_id%TYPE,
244 p_org_id IN wms_zone_locators.organization_id%TYPE);
245
246 /**
247 * Lock the record if any attribute changes
248 *
249 * @param x_return_status Return status, this can be 'S' or 'E'
250 * @param x_msg_count Count of messages in stack
251 * @param x_msg_data Message, if the count is 1
252 * @param p_zone_id Zone id
253 * @param p_zone_name Zone name
254 * @param p_description Description
255 * @param p_enabled_flag Enabled flag
256 * @param p_disable_date Disable date
257 * @param p_organization_id Organization id
258 * @param p_attribute_category Zone DFF context field
259 * @param p_attribute1 Zone DFF Attribute
260 * @param p_attribute2 Zone DFF Attribute
261 * @param p_attribute3 Zone DFF Attribute
262 * @param p_attribute4 Zone DFF Attribute
263 * @param p_attribute5 Zone DFF Attribute
264 * @param p_attribute6 Zone DFF Attribute
265 * @param p_attribute7 Zone DFF Attribute
266 * @param p_attribute8 Zone DFF Attribute
267 * @param p_attribute9 Zone DFF Attribute
268 * @param p_attribute10 Zone DFF Attribute
269 * @param p_attribute11 Zone DFF Attribute
270 * @param p_attribute12 Zone DFF Attribute
271 * @param p_attribute13 Zone DFF Attribute
272 * @param p_attribute14 Zone DFF Attribute
273 * @param p_attribute15 Zone DFF Attribute
274 * @param p_creation_date WHO column
275 * @param p_created_by WHO column
276 * @param p_last_update_date WHO column
277 * @param p_last_updated_by WHO column
278 * @param p_last_update_login WHO column
279 *
280 **/
281 PROCEDURE lock_row(
282 x_return_status OUT NOCOPY VARCHAR2,
283 x_msg_data OUT NOCOPY VARCHAR2,
284 x_msg_count OUT NOCOPY NUMBER,
285 p_zone_id IN NUMBER,
286 p_zone_name IN VARCHAR2,
287 p_description IN VARCHAR2,
288 p_type in varchar2,
289 p_enabled_flag IN VARCHAR2,
290 p_labor_enabled IN VARCHAR2,
291 p_disable_date IN DATE,
292 p_organization_id IN NUMBER,
293 p_attribute_category IN VARCHAR2,
294 p_attribute1 IN VARCHAR2,
295 p_attribute2 IN VARCHAR2,
296 p_attribute3 IN VARCHAR2,
297 p_attribute4 IN VARCHAR2,
298 p_attribute5 IN VARCHAR2,
299 p_attribute6 IN VARCHAR2,
300 p_attribute7 IN VARCHAR2,
301 p_attribute8 IN VARCHAR2,
302 p_attribute9 IN VARCHAR2,
303 p_attribute10 IN VARCHAR2,
304 p_attribute11 IN VARCHAR2,
305 p_attribute12 IN VARCHAR2,
306 p_attribute13 IN VARCHAR2,
307 p_attribute14 IN VARCHAR2,
308 p_attribute15 IN VARCHAR2,
309 p_creation_date IN DATE,
310 p_created_by IN NUMBER,
311 p_last_update_date IN DATE,
312 p_last_updated_by IN NUMBER,
313 p_last_update_login IN NUMBER
314 );
315
316 /**
317 * Initialize the data structures needed for procedures of this package to work.
318 *
319 * This procedure must always be called once before any call is made to any other
320 * procedure/function of this package.
321 *
322 * If any exception is raised during the process of initialization, the same will is
323 * propagated
324 *
325 **/
326 PROCEDURE initialize;
327
328 /**
329 * Caches the commonly used message texts in global variables
330 *
331 **/
332 PROCEDURE populate_message_cache;
333
334 /**
335 * Validate the attributes of Zones.
336 *
337 * If any validation fails the procedure sets the x_return_status to 'E'.
338 * If any truncation occurs during validation the x_return status is set to 'W'
339 *
340 * Any exception raised during the process of validation is put on the stack
341 *
342 * @param x_return_status Return status, this can be 'S', 'E' or 'W'
343 * @param x_msg_count Count of messages in stack
344 * @param x_msg_data Message, if the count is 1
345 * @param p_zone_id Zone id
346 * @param p_zone_name Zone name
347 * @param p_description Description
348 * @param p_enabled_flag Enabled flag
349 * @param p_disable_date Disable date
350 * @param p_organization_id Organization id
351 * @param p_attribute_category Zone DFF context field
352 * @param p_attribute1 Zone DFF Attribute
353 * @param p_attribute2 Zone DFF Attribute
354 * @param p_attribute3 Zone DFF Attribute
355 * @param p_attribute4 Zone DFF Attribute
356 * @param p_attribute5 Zone DFF Attribute
357 * @param p_attribute6 Zone DFF Attribute
358 * @param p_attribute7 Zone DFF Attribute
359 * @param p_attribute8 Zone DFF Attribute
360 * @param p_attribute9 Zone DFF Attribute
361 * @param p_attribute10 Zone DFF Attribute
362 * @param p_attribute11 Zone DFF Attribute
363 * @param p_attribute12 Zone DFF Attribute
364 * @param p_attribute13 Zone DFF Attribute
365 * @param p_attribute14 Zone DFF Attribute
366 * @param p_attribute15 Zone DFF Attribute
367 * @param p_creation_date WHO column
368 * @param p_created_by WHO column
369 * @param p_last_update_date WHO column
370 * @param p_last_updated_by WHO column
371 * @param p_last_update_login WHO column
372 *
373 **/
374 PROCEDURE validate_row(
375 x_return_status OUT NOCOPY VARCHAR2,
376 x_msg_data OUT NOCOPY VARCHAR2,
377 x_msg_count OUT NOCOPY NUMBER,
378 p_zone_id IN NUMBER,
379 p_zone_name IN VARCHAR2,
380 p_description IN VARCHAR2,
381 p_enabled_flag IN VARCHAR2,
382 p_disable_date IN DATE,
383 p_organization_id IN NUMBER,
384 p_attribute_category IN VARCHAR2,
385 p_attribute1 IN VARCHAR2,
386 p_attribute2 IN VARCHAR2,
387 p_attribute3 IN VARCHAR2,
388 p_attribute4 IN VARCHAR2,
389 p_attribute5 IN VARCHAR2,
390 p_attribute6 IN VARCHAR2,
391 p_attribute7 IN VARCHAR2,
392 p_attribute8 IN VARCHAR2,
393 p_attribute9 IN VARCHAR2,
394 p_attribute10 IN VARCHAR2,
395 p_attribute11 IN VARCHAR2,
396 p_attribute12 IN VARCHAR2,
397 p_attribute13 IN VARCHAR2,
398 p_attribute14 IN VARCHAR2,
399 p_attribute15 IN VARCHAR2,
400 p_creation_date IN DATE,
401 p_created_by IN NUMBER,
402 p_last_update_date IN DATE,
403 p_last_updated_by IN NUMBER,
404 p_last_update_login IN NUMBER
405 );
406
407 END wms_zones_pvt;
[Home] [Help]