1 PACKAGE wms_zones_pvt AS
2 /* $Header: WMSZONES.pls 120.0 2005/05/25 02:32:43 appldev noship $ */
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 DEFAULT NULL,
74 p_mode IN NUMBER DEFAULT NULL);
75
76 /**
77 * Contains code to insert records into wms_zones_b and
78 * wms_zones_tl
79
80 * @param x_return_status Return Status - Success, Error, Unexpected Error
81 * @param x_msg_data Contains any error messages added to the stack
82 * @param x_msg_count Contains the count of the messages added to the stack
83 * @param p_zone_id Zone_id
84 * @param p_zone_name Name of the new Zone
85 * @param p_description Description of the zone
86 * @param enabled_flag Flag to indicate whether the zone is enabled or not. '
87 Y' indicates that the zone is enabled.
88 'N' indicates that the zone is not enabled.
89 Any other value will be an error
90 * @param disable_date The date when the zone will be disabled.
91 This date cannot be less than the SYSDATE.
92 * @param p_organization_id Current Organization id
93 * @param p_attribute_category Attribute Category of the Zones Descriptive Flexfield
94 * @param p_attribute1 Attribute1
95 * @param p_attribute2 Attribute2
96 * @param p_attribute3 Attribute3
97 * @param p_attribute4 Attribute4
98 * @param p_attribute5 Attribute5
99 * @param p_attribute6 Attribute6
100 * @param p_attribute7 Attribute7
101 * @param p_attribute8 Attribute8
102 * @param p_attribute9 Attribute9
103 * @param p_attribute10 Attribute10
104 * @param p_attribute11 Attribute11
105 * @param p_attribute12 Attribute12
106 * @param p_attribute13 Attribute13
107 * @param p_attribute14 Attribute14
108 * @param p_attribute15 Attribute15
109 **/
110
111 PROCEDURE insert_wms_zones(
112 x_return_status OUT NOCOPY varchar2,
113 x_msg_data OUT NOCOPY varchar2 ,
114 x_msg_count OUT NOCOPY number,
115 p_zone_id IN number,
116 p_zone_name IN varchar2,
117 p_description IN varchar2,
118 p_enabled_flag IN varchar2,
119 p_labor_enabled IN VARCHAR2,
120 p_disable_date IN date,
121 p_organization_id IN number,
122 p_attribute_category IN varchar2,
123 p_attribute1 IN varchar2,
124 p_attribute2 IN varchar2,
125 p_attribute3 IN varchar2,
126 p_attribute4 IN varchar2,
127 p_attribute5 IN varchar2,
128 p_attribute6 IN varchar2,
129 p_attribute7 IN varchar2,
130 p_attribute8 IN varchar2,
131 p_attribute9 IN varchar2,
132 p_attribute10 IN VARCHAR2,
133 p_attribute11 IN varchar2,
134 p_attribute12 IN varchar2,
135 p_attribute13 IN varchar2,
136 p_attribute14 IN varchar2,
137 p_attribute15 IN varchar2,
138 p_creation_date IN DATE,
139 p_created_by IN NUMBER,
140 p_last_update_date IN DATE,
141 p_last_updated_by IN NUMBER,
142 p_last_update_login IN NUMBER);
143
144 /**
145 * Contains code to update records in wms_zones_b and
146 * wms_zones_tl
147
148 * @param x_return_status Return Status - Success, Error, Unexpected Error
149 * @param x_msg_data Contains any error messages added to the stack
150 * @param x_msg_count Contains the count of the messages added to the stack
151 * @param p_zone_id Zone_id
152 * @param p_zone_name Name of the new Zone
153 * @param p_description Description of the zone
154 * @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
155 * @param disable_date The date when the zone will be disabled. This date cannot be less than the SYSDATE.
156 * @param p_organization_id Current Organization id
157 * @param p_attribute_category Attribute Category of the Zones Descriptive Flexfield
158 * @param p_attribute1 Attribute1
159 * @param p_attribute2 Attribute2
160 * @param p_attribute3 Attribute3
161 * @param p_attribute4 Attribute4
162 * @param p_attribute5 Attribute5
163 * @param p_attribute6 Attribute6
164 * @param p_attribute7 Attribute7
165 * @param p_attribute8 Attribute8
166 * @param p_attribute9 Attribute9
167 * @param p_attribute10 Attribute10
168 * @param p_attribute11 Attribute11
169 * @param p_attribute12 Attribute12
170 * @param p_attribute13 Attribute13
171 * @param p_attribute14 Attribute14
172 * @param p_attribute15 Attribute15
173
174
175 **/
176 PROCEDURE update_wms_zones(
177 x_return_status OUT NOCOPY varchar2,
178 x_msg_data OUT NOCOPY varchar2 ,
179 x_msg_count OUT NOCOPY number,
180 p_zone_id IN number,
181 p_zone_name IN varchar2,
182 p_description IN varchar2,
183 p_enabled_flag IN varchar2,
184 p_labor_enabled IN VARCHAR2,
185 p_disable_date IN date,
186 p_organization_id IN number,
187 p_attribute_category IN varchar2,
188 p_attribute1 IN varchar2,
189 p_attribute2 IN varchar2,
190 p_attribute3 IN varchar2,
191 p_attribute4 IN varchar2,
192 p_attribute5 IN varchar2,
193 p_attribute6 IN varchar2,
194 p_attribute7 IN varchar2,
195 p_attribute8 IN varchar2,
196 p_attribute9 IN varchar2,
197 p_attribute10 IN varchar2,
198 p_attribute11 IN varchar2,
199 p_attribute12 IN varchar2,
200 p_attribute13 IN varchar2,
201 p_attribute14 IN varchar2,
202 p_attribute15 IN varchar2,
203 p_creation_date IN DATE,
204 p_created_by IN NUMBER,
205 p_last_update_date IN DATE,
206 p_last_updated_by IN NUMBER,
207 p_last_update_login IN NUMBER);
208
209 /**
210 * This procedure saves the records from
211 * wms_zone_locators_temp to wms_zone_locators. For every
212 * record at a given index in the table p_zoneloc_messages
213 * table, we get the the corresponding rowid from the input
214 * parameter table p_zoneloc_rowid_t for the same index.
215 * If the value in p_zoneloc_messages_t is 0, the
216 * corresponding record will be inserted into the table.
217 * If the value in p_zoneloc_messages_t is 1, the
218 * corresponding record will be deleted from the table.
219 * Else do nothing.
220
221 * @param p_zoneloc_rowid_t Table of records containing the rowids of all the records to be inserted or deleted.
222 * @param p_zoneloc_messages_t Indicates whether the corresponding record should be inserted or deleted.
223 If the value is 0, the corresponding record will be inserted into the table.
224 If the value is 1, the corresponding record will be deleted from the table.
225 Else do nothing.
226 **/
227 PROCEDURE save_sel_locators(
228 p_zoneloc_rowid_t IN wms_zones_pvt.zoneloc_rowid_t,
229 p_zone_id IN wms_zone_locators.zone_id%TYPE);
230
231 /**
232 * This procedure saves all the records from
233 * wms_zone_locators_temp to wms_zone_locators, which have the
234 * message field containing the value 'Pending Addition to
235 * Zone'. All the records whose message field has a value
236 * 'Pending Deletion' will be deleted from the table.
237 **/
238 PROCEDURE save_all_locators(p_zone_id IN wms_zone_locators.zone_id%TYPE,
239 p_org_id IN wms_zone_locators.organization_id%TYPE);
240
241 /**
242 * Lock the record if any attribute changes
243 *
244 * @param x_return_status Return status, this can be 'S' or 'E'
245 * @param x_msg_count Count of messages in stack
246 * @param x_msg_data Message, if the count is 1
247 * @param p_zone_id Zone id
248 * @param p_zone_name Zone name
249 * @param p_description Description
250 * @param p_enabled_flag Enabled flag
251 * @param p_disable_date Disable date
252 * @param p_organization_id Organization id
253 * @param p_attribute_category Zone DFF context field
254 * @param p_attribute1 Zone DFF Attribute
255 * @param p_attribute2 Zone DFF Attribute
256 * @param p_attribute3 Zone DFF Attribute
257 * @param p_attribute4 Zone DFF Attribute
258 * @param p_attribute5 Zone DFF Attribute
259 * @param p_attribute6 Zone DFF Attribute
260 * @param p_attribute7 Zone DFF Attribute
261 * @param p_attribute8 Zone DFF Attribute
262 * @param p_attribute9 Zone DFF Attribute
263 * @param p_attribute10 Zone DFF Attribute
264 * @param p_attribute11 Zone DFF Attribute
265 * @param p_attribute12 Zone DFF Attribute
266 * @param p_attribute13 Zone DFF Attribute
267 * @param p_attribute14 Zone DFF Attribute
268 * @param p_attribute15 Zone DFF Attribute
269 * @param p_creation_date WHO column
270 * @param p_created_by WHO column
271 * @param p_last_update_date WHO column
272 * @param p_last_updated_by WHO column
273 * @param p_last_update_login WHO column
274 *
275 **/
276 PROCEDURE lock_row(
277 x_return_status OUT NOCOPY VARCHAR2,
278 x_msg_data OUT NOCOPY VARCHAR2,
279 x_msg_count OUT NOCOPY NUMBER,
280 p_zone_id IN NUMBER,
281 p_zone_name IN VARCHAR2,
282 p_description IN VARCHAR2,
283 p_enabled_flag IN VARCHAR2,
284 p_labor_enabled IN VARCHAR2,
285 p_disable_date IN DATE,
286 p_organization_id IN NUMBER,
287 p_attribute_category IN VARCHAR2,
288 p_attribute1 IN VARCHAR2,
289 p_attribute2 IN VARCHAR2,
290 p_attribute3 IN VARCHAR2,
291 p_attribute4 IN VARCHAR2,
292 p_attribute5 IN VARCHAR2,
293 p_attribute6 IN VARCHAR2,
294 p_attribute7 IN VARCHAR2,
295 p_attribute8 IN VARCHAR2,
296 p_attribute9 IN VARCHAR2,
297 p_attribute10 IN VARCHAR2,
298 p_attribute11 IN VARCHAR2,
299 p_attribute12 IN VARCHAR2,
300 p_attribute13 IN VARCHAR2,
301 p_attribute14 IN VARCHAR2,
302 p_attribute15 IN VARCHAR2,
303 p_creation_date IN DATE,
304 p_created_by IN NUMBER,
305 p_last_update_date IN DATE,
306 p_last_updated_by IN NUMBER,
307 p_last_update_login IN NUMBER
308 );
309
310 /**
311 * Initialize the data structures needed for procedures of this package to work.
312 *
313 * This procedure must always be called once before any call is made to any other
314 * procedure/function of this package.
315 *
316 * If any exception is raised during the process of initialization, the same will is
317 * propagated
318 *
319 **/
320 PROCEDURE initialize;
321
322 /**
323 * Caches the commonly used message texts in global variables
324 *
325 **/
326 PROCEDURE populate_message_cache;
327
328 /**
329 * Validate the attributes of Zones.
330 *
331 * If any validation fails the procedure sets the x_return_status to 'E'.
332 * If any truncation occurs during validation the x_return status is set to 'W'
333 *
334 * Any exception raised during the process of validation is put on the stack
335 *
336 * @param x_return_status Return status, this can be 'S', 'E' or 'W'
337 * @param x_msg_count Count of messages in stack
338 * @param x_msg_data Message, if the count is 1
339 * @param p_zone_id Zone id
340 * @param p_zone_name Zone name
341 * @param p_description Description
342 * @param p_enabled_flag Enabled flag
343 * @param p_disable_date Disable date
344 * @param p_organization_id Organization id
345 * @param p_attribute_category Zone DFF context field
346 * @param p_attribute1 Zone DFF Attribute
347 * @param p_attribute2 Zone DFF Attribute
348 * @param p_attribute3 Zone DFF Attribute
352 * @param p_attribute7 Zone DFF Attribute
349 * @param p_attribute4 Zone DFF Attribute
350 * @param p_attribute5 Zone DFF Attribute
351 * @param p_attribute6 Zone DFF Attribute
353 * @param p_attribute8 Zone DFF Attribute
354 * @param p_attribute9 Zone DFF Attribute
355 * @param p_attribute10 Zone DFF Attribute
356 * @param p_attribute11 Zone DFF Attribute
357 * @param p_attribute12 Zone DFF Attribute
358 * @param p_attribute13 Zone DFF Attribute
359 * @param p_attribute14 Zone DFF Attribute
360 * @param p_attribute15 Zone DFF Attribute
361 * @param p_creation_date WHO column
362 * @param p_created_by WHO column
363 * @param p_last_update_date WHO column
364 * @param p_last_updated_by WHO column
365 * @param p_last_update_login WHO column
366 *
367 **/
368 PROCEDURE validate_row(
369 x_return_status OUT NOCOPY VARCHAR2,
370 x_msg_data OUT NOCOPY VARCHAR2,
371 x_msg_count OUT NOCOPY NUMBER,
372 p_zone_id IN NUMBER,
373 p_zone_name IN VARCHAR2,
374 p_description IN VARCHAR2,
375 p_enabled_flag IN VARCHAR2,
376 p_disable_date IN DATE,
377 p_organization_id IN NUMBER,
378 p_attribute_category IN VARCHAR2,
379 p_attribute1 IN VARCHAR2,
380 p_attribute2 IN VARCHAR2,
381 p_attribute3 IN VARCHAR2,
382 p_attribute4 IN VARCHAR2,
383 p_attribute5 IN VARCHAR2,
384 p_attribute6 IN VARCHAR2,
385 p_attribute7 IN VARCHAR2,
386 p_attribute8 IN VARCHAR2,
387 p_attribute9 IN VARCHAR2,
388 p_attribute10 IN VARCHAR2,
389 p_attribute11 IN VARCHAR2,
390 p_attribute12 IN VARCHAR2,
391 p_attribute13 IN VARCHAR2,
392 p_attribute14 IN VARCHAR2,
393 p_attribute15 IN VARCHAR2,
394 p_creation_date IN DATE,
395 p_created_by IN NUMBER,
396 p_last_update_date IN DATE,
397 p_last_updated_by IN NUMBER,
398 p_last_update_login IN NUMBER
399 );
400
401 END wms_zones_pvt;
[Home] [Help]