1 PACKAGE BODY wms_re_common_pvt AS
2 /* $Header: WMSVPPCB.pls 120.1 2005/06/07 12:18:50 appldev $ */
3 -- File : WMSVPPCS.pls
4 -- Content : WMS_RE_Common_PVT package specification
5 -- Description : Common private procedures and functions internally used by
6 -- WMS strategy and rule API's.
7 --
8 -- Currently it manages the rule table
9 -- and the input line tables (all plsql tables)
10 -- Notes :
11 -- Modified : 02/08/99 mzeckzer created
12
13 -- API name : InitInputTable
14 -- Type : Private
15 -- Function : Creates and initializes internal table of input records to
16 -- process.
17 --
18 -- Package global variable that stores the package name
19 g_pkg_name constant varchar2(30) := 'WMS_RE_Common_PVT';
20 --
21 -- Necessary variables for input parameters
22 -- [Added the following columns(s) in 'TYPE inp_rec_type IS RECORD..'
23 -- to support serial allocation based on serial reservation ]
24
25 TYPE inp_rec_type IS RECORD
26 ( pp_transaction_temp_id wms_transactions_temp.pp_transaction_temp_id%TYPE
27 ,revision wms_transactions_temp.revision%TYPE
28 ,lot_number wms_transactions_temp.lot_number%TYPE
29 ,lot_expiration_date wms_transactions_temp.lot_expiration_date%TYPE
30 ,from_subinventory_code wms_transactions_temp.from_subinventory_code%TYPE
31 ,from_locator_id wms_transactions_temp.from_locator_id%TYPE
32 ,from_cost_group_id wms_transactions_temp.from_cost_group_id%TYPE
33 ,to_subinventory_code wms_transactions_temp.to_subinventory_code%TYPE
34 ,to_locator_id wms_transactions_temp.to_locator_id%TYPE
35 ,to_cost_group_id wms_transactions_temp.to_cost_group_id%TYPE
36 ,transaction_quantity wms_transactions_temp.transaction_quantity%TYPE
37 ,secondary_quantity wms_transactions_temp.secondary_quantity%TYPE
38 ,grade_code wms_transactions_temp.grade_code%TYPE
39 ,reservation_id wms_transactions_temp.RESERVATION_ID%TYPE
40 ,serial_number wms_transactions_temp.serial_number%TYPE ---- [ added new column - serial_number]
41 ,lpn_id wms_transactions_temp.lpn_id%TYPE
42 ,next_pointer INTEGER
43 );
44 TYPE inp_tbl_type IS TABLE OF inp_rec_type INDEX BY BINARY_INTEGER;
45 g_inp_tbl inp_tbl_type;
46 g_inp_counter INTEGER; -- size of g_inp_tbl
47 g_inp_pointer INTEGER; -- pointer to g_inp_tbl
48 g_inp_first INTEGER; -- first record pointer
49 g_inp_next INTEGER; -- next record pointer
50 g_inp_previous INTEGER; -- previous record pointer
51 --
52 -- Necessary variables for rules
53 TYPE rule_rec_type IS RECORD
54 ( rule_id wms_strategy_members.rule_id%TYPE
55 ,partial_success_allowed_flag
56 wms_strategy_members.partial_success_allowed_flag%TYPE
57 );
58 TYPE rule_tbl_type IS TABLE OF rule_rec_type INDEX BY BINARY_INTEGER;
59 g_rule_tbl rule_tbl_type; -- rule table
60 g_rule_counter INTEGER; -- size of g_rule_tbl
61 g_rule_pointer INTEGER; -- record pointer to g_rule_tbl
62 -- API name : InitInputTable
63 -- Type : Private
64 -- Function : Creates and initializes internal table of input records to
65 -- process.
66 PROCEDURE InitInputTable IS
67 BEGIN
68 g_inp_tbl.DELETE;
69 g_inp_counter := 0;
70 g_inp_first := 0;
71 g_inp_pointer := 0;
72 g_inp_next := 0;
73 g_inp_previous := 0;
74 END InitInputTable;
75 --
76 -- API name : InitInputPointer
77 -- Type : Private
78 -- Function : Initializes next pointer to the first available row in the
79 -- internal table of input records to process.
80 PROCEDURE InitInputPointer IS
81 BEGIN
82 g_inp_pointer := 0;
83 g_inp_next := g_inp_first;
84 g_inp_previous := 0;
85 END InitInputPointer;
86 --
87 -- API name : InitInputLine
88 -- Type : Private
89 -- Function : Creates a new line in the internal table of input records to
90 -- process and initializes all neccessary values.
91 -- [ added the following column in PROCEDURE InitInputLine ]
92 PROCEDURE InitInputLine
93 ( p_pp_transaction_temp_id IN NUMBER
94 ,p_revision IN VARCHAR2
95 ,p_lot_number IN VARCHAR2
96 ,p_lot_expiration_date IN DATE
97 ,p_from_subinventory_code IN VARCHAR2
98 ,p_from_locator_id IN NUMBER
99 ,p_from_cost_group_id IN NUMBER
100 ,p_to_subinventory_code IN VARCHAR2
101 ,p_to_locator_id IN NUMBER
102 ,p_to_cost_group_id IN NUMBER
103 ,p_transaction_quantity IN NUMBER
104 ,p_secondary_quantity IN NUMBER DEFAULT NULL
105 ,p_grade_code IN VARCHAR2 DEFAULT NULL
106 ,p_reservation_id IN NUMBER
107 ,p_serial_number IN VARCHAR2 DEFAULT NULL --- [ Added new column p_serial_number ]
108 ,p_lpn_id IN NUMBER
109 )
110 IS
111 BEGIN
112 -- if first record, then save a pointer to it
113 IF g_inp_counter = 0 THEN
114 g_inp_first := g_inp_counter + 1;
115 -- if not, then update next pointer of previous record
116 ELSE
117 g_inp_tbl(g_inp_counter).next_pointer := g_inp_counter + 1;
118 END IF;
119 -- insert actual record
120 g_inp_counter := g_inp_counter + 1;
121 g_inp_tbl(g_inp_counter).pp_transaction_temp_id := p_pp_transaction_temp_id;
122 g_inp_tbl(g_inp_counter).revision := p_revision;
123 g_inp_tbl(g_inp_counter).lot_number := p_lot_number;
124 g_inp_tbl(g_inp_counter).lot_expiration_date := p_lot_expiration_date;
125 g_inp_tbl(g_inp_counter).from_subinventory_code := p_from_subinventory_code;
126 g_inp_tbl(g_inp_counter).from_locator_id := p_from_locator_id;
127 g_inp_tbl(g_inp_counter).from_cost_group_id := p_from_cost_group_id;
128 g_inp_tbl(g_inp_counter).to_subinventory_code := p_to_subinventory_code;
129 g_inp_tbl(g_inp_counter).to_locator_id := p_to_locator_id;
130 g_inp_tbl(g_inp_counter).to_cost_group_id := p_to_cost_group_id;
131 g_inp_tbl(g_inp_counter).transaction_quantity := p_transaction_quantity;
132 g_inp_tbl(g_inp_counter).secondary_quantity := p_secondary_quantity;
133 g_inp_tbl(g_inp_counter).grade_code := p_grade_code;
134 g_inp_tbl(g_inp_counter).reservation_id := p_reservation_id;
135 g_inp_tbl(g_inp_counter).lpn_id := p_lpn_id;
136 g_inp_tbl(g_inp_counter).serial_number := p_serial_number;
137 ---[new code added g_inp_tbl(g_inp_counter).serial_number := p_serial_number ]
138 -- initialize the next pointer of the actual record
139 g_inp_tbl(g_inp_counter).next_pointer := 0;
140 END InitInputLine;
141 --
142 -- API name : GetCountInputLines
143 -- Type : Private
144 -- Function : Returns number of rows in the internal table of input
145 -- records to process.
146 FUNCTION GetCountInputLines RETURN INTEGER IS
147 BEGIN
148 RETURN g_inp_counter;
149 END GetCountInputLines;
150 --
151 -- API name : GetNextInputLine
152 -- Type : Private
153 -- Function : Returns all values of the next row in the internal table of
154 -- input records to process and sets the next pointer to the
155 -- next available row.
156 PROCEDURE GetNextInputLine
157 ( x_pp_transaction_temp_id OUT NOCOPY NUMBER
158 ,x_revision OUT NOCOPY VARCHAR2
159 ,x_lot_number OUT NOCOPY VARCHAR2
160 ,x_lot_expiration_date OUT NOCOPY DATE
161 ,x_from_subinventory_code OUT NOCOPY VARCHAR2
162 ,x_from_locator_id OUT NOCOPY NUMBER
163 ,x_from_cost_group_id OUT NOCOPY NUMBER
164 ,x_to_subinventory_code OUT NOCOPY VARCHAR2
165 ,x_to_locator_id OUT NOCOPY NUMBER
166 ,x_to_cost_group_id OUT NOCOPY NUMBER
167 ,x_transaction_quantity OUT NOCOPY NUMBER
168 ,x_secondary_quantity OUT NOCOPY NUMBER
169 ,x_grade_code OUT NOCOPY VARCHAR2
170 ,x_reservation_id OUT NOCOPY NUMBER
171 ,x_serial_number OUT NOCOPY VARCHAR2 -- [new column - x_serial_number]
172 ,x_lpn_id OUT NOCOPY NUMBER
173 ) IS
174 BEGIN
175 g_inp_previous := g_inp_pointer;
176 g_inp_pointer := g_inp_next;
177 IF g_inp_pointer = 0 THEN
178 x_pp_transaction_temp_id := NULL;
179 x_revision := NULL;
180 x_lot_number := NULL;
181 x_lot_expiration_date := NULL;
182 x_from_subinventory_code := NULL;
183 x_from_locator_id := NULL;
184 x_from_cost_group_id := NULL;
185 x_to_subinventory_code := NULL;
186 x_to_locator_id := NULL;
187 x_to_cost_group_id := NULL;
188 x_transaction_quantity := NULL;
189 x_secondary_quantity := NULL;
190 x_grade_code := NULL;
191 x_reservation_id := NULL;
192 x_serial_number := NULL;
193 --- [new code x_serial_number = NULL;]
194 x_lpn_id := NULL;
195 ELSE
196 x_pp_transaction_temp_id
197 := g_inp_tbl(g_inp_pointer).pp_transaction_temp_id;
198 x_revision := g_inp_tbl(g_inp_pointer).revision;
199 x_lot_number := g_inp_tbl(g_inp_pointer).lot_number;
200 x_lot_expiration_date := g_inp_tbl(g_inp_pointer).lot_expiration_date;
201 x_from_subinventory_code := g_inp_tbl(g_inp_pointer).from_subinventory_code;
202 x_from_locator_id := g_inp_tbl(g_inp_pointer).from_locator_id;
203 x_from_cost_group_id := g_inp_tbl(g_inp_pointer).from_cost_group_id;
204 x_to_subinventory_code := g_inp_tbl(g_inp_pointer).to_subinventory_code;
205 x_to_locator_id := g_inp_tbl(g_inp_pointer).to_locator_id;
206 x_to_cost_group_id := g_inp_tbl(g_inp_pointer).to_cost_group_id;
207 x_transaction_quantity := g_inp_tbl(g_inp_pointer).transaction_quantity;
208 x_secondary_quantity := g_inp_tbl(g_inp_pointer).secondary_quantity;
209 x_grade_code := g_inp_tbl(g_inp_pointer).grade_code;
210 x_reservation_id := g_inp_tbl(g_inp_pointer).reservation_id;
211 x_serial_number := g_inp_tbl(g_inp_pointer).serial_number;
212 --- [ new code x_serial_number := g_inp_tbl(g_inp_pointer).serial_number; ]
213 x_lpn_id := g_inp_tbl(g_inp_pointer).lpn_id;
214 g_inp_next := g_inp_tbl(g_inp_pointer).next_pointer;
215 END IF;
216 END GetNextInputLine;
217 --
218 -- API name : UpdateInputLine
219 -- Type : Private
220 -- Function : Updates the transaction quantity of the current row
221 -- in the internal table of input records to process
222 -- according to the provided value.
223 PROCEDURE UpdateInputLine (
224 p_transaction_quantity IN NUMBER
225 ,p_secondary_quantity IN NUMBER DEFAULT NULL
226 ) IS
227 BEGIN
228 g_inp_tbl(g_inp_pointer).transaction_quantity := p_transaction_quantity;
229 g_inp_tbl(g_inp_pointer).secondary_quantity := p_secondary_quantity;
230 END UpdateInputLine;
231 --
232 -- API name : DeleteInputLine
233 -- Type : Private
234 -- Function : Deletes the current row in the internal table of input
235 -- records to process.
236 PROCEDURE DeleteInputLine IS
237 BEGIN
238 -- delete first record
239 IF g_inp_pointer = g_inp_first THEN
240 g_inp_first := g_inp_next;
241 ELSE
242 -- delete any other record
243 g_inp_tbl(g_inp_previous).next_pointer := g_inp_next;
244 END IF;
245 g_inp_counter := g_inp_counter - 1;
246 END DeleteInputLine;
247 --
248 -- API name : InitRulesTable
249 -- Type : Private
250 -- Function : Creates and initializes internal table of all rules member
251 -- of the actual wms strategy to execute.
252 PROCEDURE InitRulesTable IS
253 BEGIN
254 g_rule_tbl.delete;
255 g_rule_counter := 0;
256 g_rule_pointer := 0;
257 END InitRulesTable;
258 --
259 -- API name : InitRule
260 -- Type : Private
261 -- Function : Creates a new line in the internal table of strategy members
262 -- and initializes all neccessary values. Returns number of rows
263 -- created so far.
264 PROCEDURE InitRule
265 ( p_rule_id IN NUMBER
266 ,p_partial_success_allowed_flag IN VARCHAR2
267 ,x_rule_counter OUT NOCOPY NUMBER
268 ) IS
269 BEGIN
270 g_rule_counter := g_rule_counter + 1;
271 g_rule_tbl(g_rule_counter).rule_id := p_rule_id;
272 g_rule_tbl(g_rule_counter).partial_success_allowed_flag
273 := p_partial_success_allowed_flag;
274 x_rule_counter := g_rule_counter;
275 END InitRule;
276 --
277 -- API name : GetNextRule
278 -- Type : Private
279 -- Function : Returns all values of the next row in the internal table of
280 -- strategy members and sets the next pointer to the next
281 -- available row.
282 PROCEDURE GetNextRule
283 ( x_rule_id OUT NOCOPY NUMBER
284 ,x_partial_success_allowed_flag OUT NOCOPY VARCHAR2
285 ) IS
286 BEGIN
287 g_rule_pointer := g_rule_pointer + 1;
288 IF g_rule_pointer > g_rule_counter THEN
289 x_rule_id := NULL;
290 x_partial_success_allowed_flag := NULL;
291 ELSE
292 x_rule_id := g_rule_tbl(g_rule_pointer).rule_id;
293 x_partial_success_allowed_flag :=
294 g_rule_tbl(g_rule_pointer).partial_success_allowed_flag;
295 END IF;
296 END GetNextRule;
297 END wms_re_common_pvt;
[Home] [Help]