[Home] [Help]
PACKAGE BODY: APPS.WMS_STRATEGY_UPGRADE_PVT
Source
1 PACKAGE BODY wms_strategy_upgrade_pvt AS
2 /* $Header: WMSSTGUB.pls 115.6 2003/02/21 01:47:36 grao noship $ */
3 --
4 -- File : WMSSTGUB.pls
5 -- Content : WMS_Strategy_upgrade_PVT package specification
6 -- Description : WMS private API's
7 -- Notes :
8 -- Created : 10/31/02 Grao
9 --
10 -- API name : Upgrade Script for Strategy search order / Strategy Assignments
11 -- Type : Private
12 -- Function : Convert each strategy assignment record into a record in the new table
13 -- WMS_SELECTION_CRITERIA_TXN
14 --
15 --
16 --
17 --
18 --
19 -- Pre-reqs : record in WMS_STAGINGLANES_ASSIGNMENTS
20 --
21 --
22 -- Input Parameters :
23
24 --
25 -- Output Parameter :
26
27 procedure copy_stg_assignments
28 ( x_return_status OUT NOCOPY VARCHAR2
29 ,x_msg_count OUT NOCOPY NUMBER
30 ,x_msg_data OUT NOCOPY VARCHAR2
31 ) IS
32
33
34 Cursor C_stg is select
35 wsa.organization_id
36 ,wsa.object_type_code
37 ,wsa.object_id
38 ,wsa.strategy_type_code
39 ,wsa.strategy_id
40 ,wsa.pk1_value
41 ,wsa.pk2_value
42 ,wsa.pk3_value
43 ,wsa.pk4_value
44 ,wsa.pk5_value
45 ,wsa.effective_from
46 ,wsa.effective_to
47 ,wsa.date_type_code
48 ,wsa.date_type_lookup_type
49 ,wsa.date_type_from
50 ,wsa.date_type_to
51 from wms_strategy_assignments wsa,
52 wms_org_hierarchy_objs woho
53 where wsa.organization_id = woho.organization_id
54 and wsa.object_id = woho.object_id
55 and wsa.strategy_type_code = woho.type_code
56 and wsa.strategy_id in ( select strategy_id from wms_strategies_b
57 where enabled_flag = 'Y')
58 order by wsa.organization_id ,
59 wsa.strategy_type_code,
60 woho.search_order,
61 wsa.pk1_value,
62 wsa.pk2_value,
63 wsa.pk3_value,
64 wsa.pk4_value,
65 wsa.pk5_value,
66 wsa.sequence_number;
67
68
69 Cursor C_wsct is
70 select count(sequence_number)
71 from wms_selection_criteria_txn;
72
73
74
75 TYPE l_rec is RECORD
76 ( organization_id wms_strategy_assignments.organization_id%type
77 ,object_type_code wms_strategy_assignments.object_type_code%type
78 ,object_id wms_strategy_assignments.object_id%type
79 ,strategy_type_code wms_strategy_assignments.strategy_type_code%type
80 ,strategy_id wms_strategy_assignments.strategy_id%type
81 ,pk1_value wms_strategy_assignments.pk1_value%type
82 ,pk2_value wms_strategy_assignments.pk2_value%type
83 ,pk3_value wms_strategy_assignments.pk3_value%type
84 ,pk4_value wms_strategy_assignments.pk4_value%type
85 ,pk5_value wms_strategy_assignments.pk5_value%type
86 ,effective_from wms_strategy_assignments.effective_from%type
87 ,effective_to wms_strategy_assignments.effective_to%type
88 ,date_type_code wms_strategy_assignments.date_type_code%type
89 ,date_type_lookup_type wms_strategy_assignments.date_type_lookup_type%type
90 ,date_type_from wms_strategy_assignments.date_type_from%type
91 ,date_type_to wms_strategy_assignments.date_type_to%type
92 );
93
94 l_stg_rec l_rec;
95
96 l_organization_id NUMBER := NULL;
97 l_type_code NUMBER := NULL;
98 l_counter NUMBER := 1;
99 l_seq NUMBER := 0;
100 l_object_id NUMBER ;
101
102 l_no_recs NUMBER := 0;
103
104 ---local variables
105
106
107 l_stg_assignment_id wms_selection_criteria_txn.stg_assignment_id%type;
108 l_sequence_number wms_selection_criteria_txn.sequence_number%type;
109 l_rule_type_code wms_selection_criteria_txn.rule_type_code%type;
110 l_return_type_code wms_selection_criteria_txn.return_type_code%type;
111 l_return_type_id wms_selection_criteria_txn.return_type_id%type;
112 l_enabled_flag wms_selection_criteria_txn.enabled_flag%type;
113 l_date_type_code wms_selection_criteria_txn.date_type_code%type;
114 l_date_type_from wms_selection_criteria_txn.date_type_from%type;
115 l_date_type_to wms_selection_criteria_txn.date_type_to%type;
116 l_date_type_lookup_type wms_selection_criteria_txn.date_type_lookup_type%type;
117 l_effective_from wms_selection_criteria_txn.effective_from%type;
118 l_effective_to wms_selection_criteria_txn.effective_to%type;
119 l_from_organization_id wms_selection_criteria_txn.from_organization_id%type;
120 l_from_subinventory_name wms_selection_criteria_txn.from_subinventory_name%type;
121 l_to_organization_id wms_selection_criteria_txn.to_organization_id%type;
122 l_to_subinventory_name wms_selection_criteria_txn.to_subinventory_name%type;
123 l_customer_id wms_selection_criteria_txn.customer_id%type;
124 l_freight_code wms_selection_criteria_txn.freight_code%type;
125 l_inventory_item_id wms_selection_criteria_txn.inventory_item_id%type;
126 l_item_type wms_selection_criteria_txn.item_type%type;
127 l_assignment_group_id wms_selection_criteria_txn.assignment_group_id%type;
128 l_abc_class_id wms_selection_criteria_txn.abc_class_id%type;
129 l_category_set_id wms_selection_criteria_txn.category_set_id%type;
130 l_category_id wms_selection_criteria_txn.category_id%type;
131 l_order_type_id wms_selection_criteria_txn.order_type_id%type;
132 l_vendor_id wms_selection_criteria_txn.vendor_id%type;
133 l_project_id wms_selection_criteria_txn.project_id%type;
134 l_task_id wms_selection_criteria_txn.task_id%type;
135 l_user_id wms_selection_criteria_txn.user_id%type;
136 l_transaction_action_id wms_selection_criteria_txn.transaction_action_id%type;
137 l_reason_id wms_selection_criteria_txn.reason_id%type;
138 l_transaction_source_type_id wms_selection_criteria_txn.transaction_source_type_id%type;
139 l_transaction_type_id wms_selection_criteria_txn.transaction_type_id%type;
140 l_uom_code wms_selection_criteria_txn.uom_code%type;
141 l_uom_class wms_selection_criteria_txn.uom_class%type;
142 l_last_updated_by wms_selection_criteria_txn.last_updated_by%type;
143 l_last_update_date wms_selection_criteria_txn.last_update_date%type;
144 l_created_by wms_selection_criteria_txn.created_by%type;
145 l_creation_date wms_selection_criteria_txn.creation_date%type;
146 l_last_update_login wms_selection_criteria_txn.last_update_login%type;
147
148 --
149
150 begin
151 --- Opening Cursor for Checkin if the data is already
152 --- migrated into wms_selection_criteria_txn
153
154 open c_wsct;
155 fetch c_wsct into l_no_recs;
156 close c_wsct;
157
158 open c_stg;
159 fetch c_stg into l_stg_rec;
160
161 --- Process each record, if data found in the source table "wms_strategy_assignments"
162 --- and no data found in the target table "wms_selection_criteria_txn"
163
164 WHILE ( c_stg%found and l_no_recs = 0) LOOP
165
166 --- Initilize the local variables for each record
167
168
169 l_stg_assignment_id := NULL;
170 l_return_type_id := NULL;
171 l_enabled_flag := NULL;
172 l_date_type_code := NULL;
173 l_date_type_from := NULL;
174 l_date_type_to := NULL;
175 l_date_type_lookup_type := NULL;
176 l_effective_from := NULL;
177 l_effective_to := NULL;
178 l_from_organization_id := NULL;
179 l_from_subinventory_name := NULL;
180 l_to_organization_id := NULL;
181 l_to_subinventory_name := NULL;
182 l_customer_id := NULL;
183 l_freight_code := NULL;
184 l_inventory_item_id := NULL;
185 l_item_type := NULL;
186 l_assignment_group_id := NULL;
187 l_abc_class_id := NULL;
188 l_category_set_id := NULL;
189 l_category_id := NULL;
190 l_order_type_id := NULL;
191 l_vendor_id := NULL;
192 l_project_id := NULL;
193 l_task_id := NULL;
194 l_user_id := NULL;
195 l_transaction_action_id := NULL;
196 l_reason_id := NULL;
197 l_transaction_source_type_id := NULL;
198 l_transaction_type_id := NULL;
199 l_uom_code := NULL;
200 l_uom_class := NULL;
201 l_last_updated_by := nvl(to_number(fnd_profile.value('USER_ID')), -1);
202 l_last_update_date := SYSDATE;
203 l_created_by := nvl(to_number(fnd_profile.value('USER_ID')), -1);
204 l_creation_date := SYSDATE;
205 l_last_update_login := to_number(fnd_profile.value('LOGIN_ID'));
206
207 --- Compute sequence_number. The sequence_number is reset, for each
208 --- diffrent rule type or diffrent org
209 ---
210
211 if (( nvl(l_organization_id, -999) = nvl(l_stg_rec.organization_id, -999)) ) and
212 (( nvl(l_type_code, -1) = nvl(l_stg_rec.strategy_type_code, -1))) then
213
214 -- l_counter := l_counter + 1;
215 l_seq := l_seq + 5;
216 else
217 -- l_counter := 1;
218 -- l_organization_id := l_stg_rec.organization_id;
219 -- l_rule_type_code := l_stg_rec.strategy_type_code;
220 l_seq := 5;
221 end if;
222 -- l_counter := l_counter + 1;
223
224 -- Set the other common values
225
226 l_type_code := l_stg_rec.strategy_type_code;
227 l_organization_id := l_stg_rec.organization_id;
228 l_rule_type_code := l_stg_rec.strategy_type_code;
229 l_return_type_code := 'S';
230 l_return_type_id := l_stg_rec.strategy_id;
231 l_object_id := l_stg_rec.object_id;
232 l_date_type_code := l_stg_rec.date_type_code;
233 l_date_type_from := l_stg_rec.date_type_from;
234 l_date_type_to := l_stg_rec.date_type_to;
235 l_date_type_lookup_type := l_stg_rec.date_type_lookup_type;
236 l_effective_from := l_stg_rec.effective_from;
237 l_effective_to := l_stg_rec.effective_to;
238 l_enabled_flag := 1;
239 l_sequence_number := l_seq;
240 l_from_organization_id := l_stg_rec.organization_id;
241
242 select wms_selection_criteria_txn_s.nextval
243 into l_stg_assignment_id
244 from dual;
245
246 --- Set the Pk1, Pk2, Pk3, Pk4 and Pk5 from the wms_strategy_assigenment table to the
247 --- corresponding column in the wms_selection_criteria_txn table
248
249 if l_object_id = 3 then --- Source Organization
250 l_from_organization_id := l_stg_rec.pk1_value;
251 elsif l_object_id = 4 then --- Item
252 l_from_organization_id := l_stg_rec.pk1_value;
253 l_inventory_item_id := l_stg_rec.pk2_value;
254 elsif l_object_id = 7 then --- Source Subinventory
255 l_from_organization_id := l_stg_rec.pk1_value;
256 l_from_subinventory_name := l_stg_rec.pk2_value;
257 elsif l_object_id = 9 then --- Item Subinventory/SECONDARY_INVENTORY_CODE
258 l_from_organization_id := l_stg_rec.pk1_value;
259 l_inventory_item_id := l_stg_rec.pk2_value;
260 l_from_subinventory_name := l_stg_rec.pk3_value;
261 elsif l_object_id = 11 then --- Transaction Source Ty/TRANSACTION_SOURCE_TYPE_ID
262 l_transaction_source_type_id := l_stg_rec.pk1_value;
263 elsif l_object_id = 12 then --- Transaction Type/TRANSACTION_TYPE_ID
264 l_transaction_type_id := l_stg_rec.pk1_value;
265 elsif l_object_id = 13 then --- Source Project/PROJECT_ID
266 l_project_id := l_stg_rec.pk1_value;
267 elsif l_object_id = 14 then --- Source Project Task/TASK_ID
268 l_task_id := l_stg_rec.pk1_value;
269 elsif l_object_id = 15 then --- Transaction Reason/REASON_ID
270 l_reason_id := l_stg_rec.pk1_value;
271 elsif l_object_id = 16 then --- User/USER_ID
272 l_user_id := l_stg_rec.pk1_value;
273 elsif l_object_id = 17 then --- Transaction Action
274 l_transaction_action_id := l_stg_rec.pk1_value;
275 elsif l_object_id = 18 then --- Destination Organizat
276 l_to_organization_id := l_stg_rec.pk1_value;
277 elsif l_object_id = 19 then --- Destination Subinvent
278 l_to_organization_id := l_stg_rec.pk1_value;
279 l_to_subinventory_name := l_stg_rec.pk2_value;
280 elsif l_object_id = 21 then --- UOM
281 l_uom_code := l_stg_rec.pk1_value;
282 elsif l_object_id = 22 then --- UOM Class/UOM_CLASS
283
284 l_uom_class := l_stg_rec.pk1_value;
285 elsif l_object_id = 23 then --- Freight Carrier/FREIGHT_CODE
286 l_from_organization_id := l_stg_rec.pk1_value;
287 l_freight_code := l_stg_rec.pk2_value;
288 elsif l_object_id = 30 then --- Customers/CUSTOMER_ID
289
290 l_customer_id := l_stg_rec.pk1_value;
291 elsif l_object_id = 52 then --- Item Category/CATEGORY_SET_ID, CATEGORY_ID
292 l_from_organization_id := l_stg_rec.pk1_value;
293 l_category_set_id := l_stg_rec.pk2_value;
294 l_category_id := l_stg_rec.pk3_value;
295 elsif l_object_id = 55 then --- Item ABC Assignment /ASSIGNMENT_GROUP_ID, ABC_CLASS_ID
296 l_assignment_group_id := l_stg_rec.pk1_value;
297 l_abc_class_id := l_stg_rec.pk2_value;
301 l_vendor_id := l_stg_rec.pk1_value;
298 elsif l_object_id = 56 then --- Item Type/ITEM_TYPE
299 l_item_type := l_stg_rec.pk1_value;
300 elsif l_object_id = 100 then --- Supplier/VENDOR_ID
302 elsif l_object_id = 1005 then --- Order Type/ORDER_TYPE_ID
303 l_order_type_id:= l_stg_rec.pk1_value;
304 end if;
305 ---
306 insert_row (
307 x_stg_assignment_id => l_stg_assignment_id
308 ,x_sequence_number => l_sequence_number
309 ,x_rule_type_code => l_rule_type_code
310 ,x_return_type_code => l_return_type_code
311 ,x_return_type_id => l_return_type_id
312 ,x_enabled_flag => l_enabled_flag
313 ,x_date_type_code => l_date_type_code
314 ,x_date_type_from => l_date_type_from
315 ,x_date_type_to => l_date_type_to
316 ,x_date_type_lookup_type => l_date_type_lookup_type
317 ,x_effective_from => l_effective_from
318 ,x_effective_to => l_effective_to
319 ,x_from_organization_id => l_from_organization_id
320 ,x_from_subinventory_name => l_from_subinventory_name
321 ,x_to_organization_id => l_to_organization_id
322 ,x_to_subinventory_name => l_to_subinventory_name
323 ,x_customer_id => l_customer_id
324 ,x_freight_code => l_freight_code
325 ,x_inventory_item_id => l_inventory_item_id
326 ,x_item_type => l_item_type
327 ,x_assignment_group_id => l_assignment_group_id
328 ,x_abc_class_id => l_abc_class_id
329 ,x_category_set_id => l_category_set_id
330 ,x_category_id => l_category_id
331 ,x_order_type_id => l_order_type_id
332 ,x_vendor_id => l_vendor_id
333 ,x_project_id => l_project_id
334 ,x_task_id => l_task_id
335 ,x_user_id => l_user_id
336 ,x_transaction_action_id => l_transaction_action_id
337 ,x_reason_id => l_reason_id
338 ,x_transaction_source_type_id => l_transaction_source_type_id
339 ,x_transaction_type_id => l_transaction_type_id
340 ,x_uom_code => l_uom_code
341 ,x_uom_class => l_uom_class
342 ,x_last_updated_by => l_last_updated_by
343 ,x_last_update_date => l_last_update_date
344 ,x_created_by => l_created_by
345 ,x_creation_date => l_creation_date
346 ,x_last_update_login => l_last_update_login);
347 fetch c_stg into l_stg_rec;
348
349 END LOOP;
350 close c_stg;
351 end copy_stg_assignments;
352 --
353 --
354 Procedure insert_row (
355 x_stg_assignment_id in number
356 ,x_sequence_number in number
357 ,x_rule_type_code in number
358 ,x_return_type_code in varchar2
359 ,x_return_type_id in number
360 ,x_enabled_flag in varchar2
361 ,x_date_type_code in varchar2
362 ,x_date_type_from in number
363 ,x_date_type_to in number
364 ,x_date_type_lookup_type in varchar2
365 ,x_effective_from in date
366 ,x_effective_to in date
367 ,x_from_organization_id in number
368 ,x_from_subinventory_name in varchar2
369 ,x_to_organization_id in number
370 ,x_to_subinventory_name in varchar2
371 ,x_customer_id in number
372 ,x_freight_code in varchar2
373 ,x_inventory_item_id in number
374 ,x_item_type in varchar2
375 ,x_assignment_group_id in number
376 ,x_abc_class_id in number
377 ,x_category_set_id in number
378 ,x_category_id in number
379 ,x_order_type_id in number
380 ,x_vendor_id in number
381 ,x_project_id in number
382 ,x_task_id in number
383 ,x_user_id in number
384 ,x_transaction_action_id in number
385 ,x_reason_id in number
386 ,x_transaction_source_type_id in number
387 ,x_transaction_type_id in number
388 ,x_uom_code in varchar2
389 ,x_uom_class in varchar2
390 ,x_last_updated_by in number
391 ,x_last_update_date in date
392 ,x_created_by in number
393 ,x_creation_date in date
394 ,x_last_update_login in number
395 ) is
396
397 x_seq NUMBER;
398
399
400 cursor c is select stg_assignment_id from wms_selection_criteria_txn
401 where sequence_number = x_sequence_number
402 and rule_type_code = x_rule_type_code
403 and return_type_code = x_return_type_code
404 and return_type_id = x_return_type_id;
405
406
407 begin
408
409 insert into wms_selection_criteria_txn (
410 stg_assignment_id
411 ,sequence_number
412 ,rule_type_code
413 ,return_type_code
414 ,return_type_id
415 ,enabled_flag
416 ,date_type_code
417 ,date_type_from
418 ,date_type_to
419 ,date_type_lookup_type
420 ,effective_from
421 ,effective_to
422 ,from_organization_id
423 ,from_subinventory_name
424 ,to_organization_id
425 ,to_subinventory_name
426 ,customer_id
427 ,freight_code
428 ,inventory_item_id
429 ,item_type
430 ,assignment_group_id
431 ,abc_class_id
432 ,category_set_id
433 ,category_id
434 ,order_type_id
435 ,vendor_id
436 ,project_id
437 ,task_id
438 ,user_id
439 ,transaction_action_id
440 ,reason_id
441 ,transaction_source_type_id
442 ,transaction_type_id
443 ,uom_code
444 ,uom_class
445 ,last_updated_by
446 ,last_update_date
447 ,created_by
448 ,creation_date
449 ,last_update_login
450 ) values (
451 x_stg_assignment_id
452 ,x_sequence_number
453 ,x_rule_type_code
454 ,x_return_type_code
455 ,x_return_type_id
456 ,x_enabled_flag
457 ,x_date_type_code
458 ,x_date_type_from
459 ,x_date_type_to
460 ,x_date_type_lookup_type
461 ,x_effective_from
462 ,x_effective_to
463 ,x_from_organization_id
464 ,x_from_subinventory_name
465 ,x_to_organization_id
466 ,x_to_subinventory_name
467 ,x_customer_id
468 ,x_freight_code
469 ,x_inventory_item_id
470 ,x_item_type
471 ,x_assignment_group_id
472 ,x_abc_class_id
473 ,x_category_set_id
474 ,x_category_id
475 ,x_order_type_id
476 ,x_vendor_id
477 ,x_project_id
478 ,x_task_id
479 ,x_user_id
480 ,x_transaction_action_id
481 ,x_reason_id
482 ,x_transaction_source_type_id
483 ,x_transaction_type_id
484 ,x_uom_code
485 ,x_uom_class
486 ,x_last_updated_by
487 ,x_last_update_date
488 ,x_created_by
489 ,x_creation_date
490 ,x_last_update_login );
491
492 open c;
493 fetch c into x_seq;
494 if (c%notfound) then
495 close c;
496 raise no_data_found;
497 end if;
498 close c;
499 end insert_row;
500
501 end WMS_Strategy_upgrade_PVT ;