[Home] [Help]
PACKAGE BODY: APPS.INV_MWB_COST_GROUP_TREE
Source
1 PACKAGE BODY INV_MWB_COST_GROUP_TREE AS
2 /* $Header: INVMWCGB.pls 120.12 2011/06/14 08:45:35 nimisra ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_MWB_COST_GROUP_TREE';
5
6 -- PROCEDURE make_common_queries(p_flag VARCHAR2); -- Bug 6060233
7 --
8 -- private functions
9 --
10 PROCEDURE root_node_event (
11 x_node_value IN OUT NOCOPY NUMBER
12 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
13 , x_tbl_index IN OUT NOCOPY NUMBER
14 ) IS
15
16 l_procedure_name VARCHAR2(30);
17
18 BEGIN
19
20 l_procedure_name := 'ROOT_NODE_EVENT';
21
22 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
23 inv_mwb_tree1.add_cgs(
24 x_node_value
25 , x_node_tbl
26 , x_tbl_index
27 );
28
29 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
30
31 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
32 inv_mwb_globals.g_serial_to IS NOT NULL
33 OR inv_mwb_globals.g_status_id IS NOT NULL -- Bug 6060233
34 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
35 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9365452
36 THEN
37 make_common_queries('MSN_QUERY');
38 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
39 ELSE
40 make_common_queries('MOQD');
41 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
42 END IF;
43 inv_mwb_query_manager.execute_query;
44
45 END IF; -- event
46
47 EXCEPTION
48 WHEN no_data_found THEN
49 NULL;
50 END root_node_event;
51
52 PROCEDURE cost_group_node_event (
53 x_node_value IN OUT NOCOPY NUMBER
54 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
55 , x_tbl_index IN OUT NOCOPY NUMBER
56 ) IS
57
58 l_procedure_name VARCHAR2(30);
59
60 BEGIN
61
62 l_procedure_name := 'COST_GROUP_NODE_EVENT';
63
64 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
65 inv_mwb_tree1.add_orgs(
66 x_node_value
67 , x_node_tbl
68 , x_tbl_index
69 );
70
71 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
72
73 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
74 inv_mwb_globals.g_serial_to IS NOT NULL
75 OR inv_mwb_globals.g_status_id IS NOT NULL -- Bug 6060233
76 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
77 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9365452
78 THEN
79 make_common_queries('MSN_QUERY');
80 inv_mwb_query_manager.add_where_clause('msn.cost_group_id = :onh_tree_cost_group_id' ,'ONHAND');
81 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
82 ELSE
83 make_common_queries('MOQD');
84 inv_mwb_query_manager.add_where_clause('moqd.cost_group_id = :onh_tree_cost_group_id' ,'ONHAND');
85
86 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
87 END IF;
88 inv_mwb_query_manager.add_bind_variable('onh_tree_cost_group_id', inv_mwb_globals.g_tree_cg_id);
89 inv_mwb_query_manager.execute_query;
90
91 END IF; -- event
92
93 EXCEPTION
94 WHEN no_data_found THEN
95 NULL;
96 END cost_group_node_event;
97
98 PROCEDURE org_node_event (
99 x_node_value IN OUT NOCOPY NUMBER
100 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
101 , x_tbl_index IN OUT NOCOPY NUMBER
102 ) IS
103
104 l_procedure_name VARCHAR2(30);
105
106 BEGIN
107
108 l_procedure_name := 'ORG_NODE_EVENT';
109
110 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
111 inv_mwb_tree1.add_items(
112 x_node_value
113 , x_node_tbl
114 , x_tbl_index
115 );
116
117 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
118
119 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
120 inv_mwb_globals.g_serial_to IS NOT NULL
121 OR inv_mwb_globals.g_status_id IS NOT NULL -- Bug 6060233
122 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
123 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9365452
124 THEN
125 make_common_queries('MSN_QUERY');
126 inv_mwb_query_manager.add_where_clause('msn.cost_group_id = :onh_tree_cost_group_id' ,'ONHAND');
127 inv_mwb_query_manager.add_where_clause('msn.current_organization_id = :onh_tree_organization_id' ,'ONHAND');
128 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
129 ELSE
130 make_common_queries('MOQD');
131 inv_mwb_query_manager.add_where_clause('moqd.cost_group_id = :onh_tree_cost_group_id' ,'ONHAND');
132 inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
133
134 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
135 END IF;
136 inv_mwb_query_manager.add_bind_variable('onh_tree_cost_group_id', inv_mwb_globals.g_tree_cg_id);
137 inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
138 inv_mwb_query_manager.execute_query;
139
140 END IF; -- tree event
141
142 EXCEPTION
143 WHEN no_data_found THEN
144 NULL;
145 END org_node_event;
146
147 PROCEDURE item_node_event (
148 x_node_value IN OUT NOCOPY NUMBER
149 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
150 , x_tbl_index IN OUT NOCOPY NUMBER
151 ) IS
152
153 l_procedure_name VARCHAR2(30);
154
155 BEGIN
156
157 l_procedure_name := 'ITEM_NODE_EVENT';
158 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
159
160 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
161 inv_mwb_globals.g_serial_to IS NOT NULL
162 OR inv_mwb_globals.g_status_id IS NOT NULL -- Bug 6060233
163 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
164 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9365452
165 THEN
166 make_common_queries('MSN_QUERY');
167 inv_mwb_query_manager.add_where_clause('msn.cost_group_id = :onh_tree_cost_group_id' ,'ONHAND');
168 inv_mwb_query_manager.add_where_clause('msn.current_organization_id = :onh_tree_organization_id' ,'ONHAND');
169 inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
170 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
171 ELSE
172 make_common_queries('MOQD');
173 inv_mwb_query_manager.add_where_clause('moqd.cost_group_id = :onh_tree_cost_group_id' ,'ONHAND');
174 inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
175 inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
176
177 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
178 END IF;
179 inv_mwb_query_manager.add_bind_variable('onh_tree_cost_group_id', inv_mwb_globals.g_tree_cg_id);
180 inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
181 inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
182 inv_mwb_query_manager.execute_query;
183
184 END IF;
185
186 EXCEPTION
187 WHEN no_data_found THEN
188 NULL;
189 END item_node_event;
190
191 PROCEDURE make_common_queries(p_flag VARCHAR2) IS
192 BEGIN
193
194 IF(inv_mwb_globals.g_chk_onhand = 1) THEN
195
196 CASE p_flag
197 WHEN 'MOQD' THEN
198
199 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
200 'moqd.inventory_item_id';
201
202 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
203 'moqd.organization_id';
204
205 -- inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
206 -- 'moqd.transaction_uom_code';
207
208 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
209 'SUM(moqd.primary_transaction_quantity)';
210
211 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
212 'SUM(DECODE(moqd.containerized_flag, 1, moqd.primary_transaction_quantity, 0))';
213
214 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
215 'SUM(DECODE(moqd.containerized_flag, 1, 0, moqd.primary_transaction_quantity))';
216
217 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UOM_CODE).column_value :=
218 'moqd.secondary_uom_code';
219
220 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_ONHAND).column_value :=
221 'SUM(moqd.secondary_transaction_quantity)';
222
223 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
224 'SUM(DECODE(moqd.containerized_flag, 1, moqd.secondary_transaction_quantity, 0))';
225
226 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
227 'SUM(DECODE(moqd.containerized_flag, 1, 0, moqd.secondary_transaction_quantity))';
228
229 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
230 'moqd.cost_group_id';
231
232 inv_mwb_query_manager.add_from_clause('mtl_onhand_quantities_detail moqd', 'ONHAND');
233
234 inv_mwb_query_manager.add_group_clause('moqd.inventory_item_id', 'ONHAND');
235 inv_mwb_query_manager.add_group_clause('moqd.organization_id', 'ONHAND');
236 -- inv_mwb_query_manager.add_group_clause('moqd.transaction_uom_code', 'ONHAND');
237 inv_mwb_query_manager.add_group_clause('moqd.secondary_uom_code', 'ONHAND');
238 inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
239
240 WHEN 'MSN_QUERY' THEN
241 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
242 'msn.inventory_item_id';
243
244 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
245 'msn.current_organization_id';
246 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
247 '''Ea''';
248
249 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
250 'count(1)';
251 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
252 'count(decode(msn.lpn_id,NULL,0, 1))';
253 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
254 'count(decode(msn.lpn_id,NULL,1, 0))';
255 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.COST_GROUP).column_value :=
256 'msn.cost_group_id';
257 inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn', 'ONHAND');
258 inv_mwb_query_manager.add_group_clause('msn.inventory_item_id' , 'ONHAND');
259 inv_mwb_query_manager.add_group_clause('msn.current_organization_id', 'ONHAND');
260 inv_mwb_query_manager.add_group_clause('''Ea''', 'ONHAND');
261 inv_mwb_query_manager.add_group_clause('msn.cost_group_id', 'ONHAND');
262 END CASE; -- p_flag
263
264 END IF; -- End if for onhand
265
266 END make_common_queries;
267
268 --
269 -- public functions
270 --
271
272 --
273 -- General APPTREE event handler
274 --
275 PROCEDURE event (
276 x_node_value IN OUT NOCOPY NUMBER
277 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
278 , x_tbl_index IN OUT NOCOPY NUMBER
279 ) IS
280 l_procedure_name VARCHAR2(30);
281 BEGIN
282
283 l_procedure_name := 'EVENT';
284
285 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' OR
286 inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
287
288 CASE inv_mwb_globals.g_tree_node_type
289
290 WHEN 'APPTREE_OBJECT_TRUNK' THEN
291 root_node_event (
292 x_node_value
293 , x_node_tbl
294 , x_tbl_index
295 );
296
297 WHEN 'ORG' THEN
298 org_node_event (
299 x_node_value
300 , x_node_tbl
301 , x_tbl_index
302 );
303
304 WHEN 'ITEM' THEN
305 item_node_event (
306 x_node_value
307 , x_node_tbl
308 , x_tbl_index
309 );
310
311 WHEN 'COST_GROUP' THEN
312 cost_group_node_event (
313 x_node_value
314 , x_node_tbl
315 , x_tbl_index
316 );
317
318 END CASE;
319
320 END IF; -- event
321
322 EXCEPTION
323 WHEN no_data_found THEN
324 NULL;
325 END event;
326
327 END inv_mwb_cost_group_tree;