[Home] [Help]
PACKAGE BODY: APPS.WMS_CACHE
Source
1 PACKAGE BODY wms_cache AS
2 /* $Header: WMSCACHB.pls 120.2.12020000.2 2012/07/04 07:02:45 anviswan ship $ */
3
4 FUNCTION UOM_CONVERT (
5 item_id NUMBER ,
6 precision NUMBER ,
7 from_quantity NUMBER ,
8 from_unit VARCHAR2 ,
9 to_unit VARCHAR2,
10 lot_number VARCHAR2 DEFAULT NULL, -- Added for Bug 9056696
11 organization_id NUMBER DEFAULT NULL) -- Added for Bug 9056696
12 RETURN NUMBER IS
13
14 l_result NUMBER;
15 l_uom_rate NUMBER;
16 l_hash_value NUMBER;
17 l_hash_string VARCHAR2(2000);
18 l_precision NUMBER := precision;
19
20 BEGIN
21
22 -- Added for Bug 9056696
23 IF (lot_number IS NOT NULL) THEN
24 inv_convert.inv_um_conversion( from_unit => from_unit ,
25 to_unit => to_unit ,
26 item_id => item_id ,
27 lot_number => lot_number , -- Added for Bug 9056696
28 organization_id => organization_id , -- Added for Bug 9056696
29 uom_rate => l_uom_rate
30 );
31 ELSE
32 -- End of Bug 9056696
33
34 l_hash_string := from_unit||'-'||to_unit||'-'||item_id;
35
36 l_hash_value := DBMS_UTILITY.get_hash_value
37 ( name => l_hash_string
38 , base => g_hash_base
39 , hash_size => g_hash_size
40 );
41 IF g_from_to_uom_ratio_tbl.EXISTS(l_hash_value)
42 AND g_from_uom_code_tbl(l_hash_value) = from_unit
43 AND g_to_uom_code_tbl(l_hash_value) = to_unit
44 AND g_item_tbl(l_hash_value) = item_id
45 THEN
46 l_uom_rate := g_from_to_uom_ratio_tbl(l_hash_value);
47
48 ELSE
49 -- Compute conversion ratio between transaction UOM and item primary UOM
50 inv_convert.inv_um_conversion( from_unit => from_unit ,
51 to_unit => to_unit ,
52 item_id => item_id ,
53 uom_rate => l_uom_rate
54 );
55
56 IF (l_uom_rate = -99999 ) THEN
57 RETURN l_uom_rate; /*8934647*/
58 END IF;
59
60 g_from_uom_code_tbl(l_hash_value) := from_unit;
61 g_to_uom_code_tbl(l_hash_value) := to_unit;
62 g_item_tbl(l_hash_value) := item_id ;
63 g_from_to_uom_ratio_tbl(l_hash_value) := l_uom_rate;
64
65 --Now store the inverse
66
67 l_hash_string := to_unit||'-'|| from_unit||'-'||item_id;
68 l_hash_value := DBMS_UTILITY.get_hash_value
69 ( name => l_hash_string
70 , base => g_hash_base
71 , hash_size => g_hash_size
72 );
73 g_from_uom_code_tbl(l_hash_value) := to_unit;
74 g_to_uom_code_tbl(l_hash_value) := from_unit;
75 g_item_tbl(l_hash_value) := item_id ;
76 g_from_to_uom_ratio_tbl(l_hash_value) := 1/l_uom_rate;
77
78 END IF;
79 END IF; -- Added for Bug 9056696
80
81 IF ( from_quantity IS NOT NULL ) THEN
82 l_result := from_quantity * l_uom_rate;
83 END IF;
84
85 l_precision := PRECISION;
86
87 IF (l_precision IS NULL) THEN --default precision is 5
88 l_precision := 5 ;
89 END IF;
90
91 RETURN round(l_result , l_precision);
92
93 END UOM_CONVERT;
94
95
96 FUNCTION get_Strategy_from_cache (
97 p_strategy_id IN NUMBER ,
98 x_return_status OUT NOCOPY varchar2,
99 x_msg_count OUT NOCOPY number,
100 x_msg_data OUT NOCOPY varchar2,
101 x_over_alloc_mode OUT NOCOPY NUMBER,
102 x_tolerance out NOCOPY NUMBER
103 ) RETURN NUMBER IS
104
105 l_org_id NUMBER;
106 l_rule_counter NUMBER := 0 ;
107
108 CURSOR rules_cur IS
109 SELECT wsm.rule_id ,
110 wsm.partial_success_allowed_flag
111 FROM wms_strategy_members wsm ,
112 wms_rules_b wrb
113 WHERE wsm.strategy_id = p_strategy_id
114 AND wrb.rule_id = wsm.rule_id
115 AND wrb.enabled_flag = 'Y'
116 AND wms_datecheck_pvt.date_valid (l_org_id,
117 wsm.date_type_code,
118 wsm.date_type_from,
119 wsm.date_type_to,
120 wsm.effective_from,
121 wsm.effective_to) = 'Y' ;
122
123
124
125
126 BEGIN
127 IF (strategy_tbl.EXISTS(p_strategy_id) ) THEN --The strategy is already there in cache
128
129 x_over_alloc_mode := strategy_tbl(p_strategy_id).over_allocation_mode;
130 x_tolerance := strategy_tbl(p_strategy_id).tolerance_value;
131 FOR ii IN strategy_tbl(p_strategy_id).rule_id_tbl.FIRST .. strategy_tbl(p_strategy_id).rule_id_tbl.LAST LOOP
132 /*
133 This will store all the rules into the pl/sql table that has been used by rules engine to work on the current data
134 */
135 Wms_re_common_pvt.InitRule (
136 strategy_tbl(p_strategy_id).rule_id_tbl(ii) ,
137 strategy_tbl(p_strategy_id).partial_succ_flag_tbl(ii),
138 l_rule_counter
139 );
140
141 END LOOP;
142
143
144 ELSE --Need to get strategy details from DB table
145
146 SELECT wsb.organization_id ,
147 NVL(wsb.over_allocation_mode, 1) ,
148 wsb.tolerance_value
149 INTO l_org_id,
150 x_over_alloc_mode,
151 x_tolerance
152 FROM wms_strategies_b wsb
153 WHERE wsb.strategy_id = p_strategy_id;
154
155 strategy_tbl(p_strategy_id).over_allocation_mode := x_over_alloc_mode;
156 strategy_tbl(p_strategy_id).tolerance_value := x_tolerance;
157
158 OPEN rules_cur ; --get rules for the strategy
159 LOOP
160 FETCH rules_cur BULK COLLECT INTO
161 strategy_tbl(p_strategy_id).rule_id_tbl,
162 strategy_tbl(p_strategy_id).partial_succ_flag_tbl
163 LIMIT g_bulk_fetch_limit;
164
165 EXIT WHEN strategy_tbl(p_strategy_id).rule_id_tbl.COUNT = 0;
166 FOR ii IN strategy_tbl(p_strategy_id).rule_id_tbl.FIRST .. strategy_tbl(p_strategy_id).rule_id_tbl.LAST LOOP
167 /*
168 This will store all the rules into the pl/sql table that has been used by rules engine to work on the current data
169 */
170 Wms_re_common_pvt.InitRule (
171 strategy_tbl(p_strategy_id).rule_id_tbl(ii) ,
172 strategy_tbl(p_strategy_id).partial_succ_flag_tbl(ii),
173 l_rule_counter
174 );
175
176 END LOOP;
177
178
179 END LOOP;
180 Close rules_cur;
181 END IF;
182
183
184 RETURN l_rule_counter;
185
186 EXCEPTION
187 WHEN OTHERS THEN
188 RETURN -999;
189 END get_Strategy_from_cache;
190
191 PROCEDURE cleanup_rules_cache IS
192 BEGIN
193 strategy_tbl.DELETE;
194 EXCEPTION
195 WHEN OTHERS THEN
196 NULL;
197
198 END cleanup_rules_cache ;
199
200 END wms_cache;