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