4 -- File : WMSVPPAB.pls
1 PACKAGE BODY wms_assignment_pvt AS
2 /* $Header: WMSVPPAB.pls 120.2 2005/08/29 23:04:01 abshukla noship $ */
3 --
5 -- Content : WMS_Assignment_PVT package body
6 -- Description : Private API functions and procedures needed for wms rules
7 -- engine strategy assignment implementation.
8 -- Notes :
9 -- Modified : 02/08/99 mzeckzer created
10 --
11 -- Package global to store package name
12 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_Assignment_PVT';
13 -- API name : GetObjectValueName
14 -- Type : Private
15 -- Function : Returns the current name of the business object instance a
16 -- wms strategy is assigned to.
17 -- ( Needed for forms base views of strategy assignment forms )
18 -- Input Parameters:
19 -- p_object_type_code: 1 - system defined ; 2 - user defined
20 -- p_object_id : object identifier
21 -- p_pk1_value : primary key value 1
22 -- p_pk2_value : primary key value 2
23 -- p_pk3_value : primary key value 3
24 -- p_pk4_value : primary key value 4
25 -- p_pk5_value : primary key value 5
26 --
27 -- Notes : Since it is not possible to use dynamic SQL within package
28 -- functions without violating the WNPS pragma, cursors are
29 -- hard coded instead of getting the actual SQL statement from
30 -- WMS_OBJECTS_B table ( analogous to LOV for insert in setup
31 -- form ) to be able to use function together with 'where' and
32 -- 'order by' clauses in regular SQL.
33 -- Important:
34 -- EACH AND EVERY BUSINESS OBJECT IN WMS_OBJECTS_B ENABLED
35 -- TO TIE STRATEGIES TO IT MUST BE REPRESENTED WITHIN THIS
36 -- FUNCTION APROPRIATELY IN ORDER TO BE ABLE TO RE-QUERY SET UP
37 -- STRATEGY ASSIGNMENTS !
38 -- More: The parama is no longer needed for Oracle 8i
39 FUNCTION GetObjectValueName
40 ( p_object_type_code IN NUMBER DEFAULT g_miss_num
41 ,p_object_id IN NUMBER DEFAULT g_miss_num
42 ,p_pk1_value IN VARCHAR2 DEFAULT g_miss_char
43 ,p_pk2_value IN VARCHAR2 DEFAULT NULL
44 ,p_pk3_value IN VARCHAR2 DEFAULT NULL
45 ,p_pk4_value IN VARCHAR2 DEFAULT NULL
46 ,p_pk5_value IN VARCHAR2 DEFAULT NULL
47 ) RETURN VARCHAR2
48 IS
49 l_value_name VARCHAR2(2000);
50 --
51 CURSOR obj3 IS -- org / opposing org
52 SELECT mp.organization_code
53 FROM mtl_parameters mp
54 WHERE mp.organization_id =
55 fnd_number.canonical_to_number(p_pk1_value)
56 ;
57 --
58 CURSOR obj4 IS -- item
59 SELECT msik.concatenated_segments
60 FROM mtl_system_items_kfv msik
61 WHERE msik.organization_id = fnd_number.canonical_to_number(p_pk1_value)
62 AND msik.inventory_item_id = fnd_number.canonical_to_number(p_pk2_value)
63 ;
64 --
65 CURSOR obj7 IS -- sub
66 SELECT msi.secondary_inventory_name
67 FROM mtl_secondary_inventories msi
68 WHERE msi.organization_id = fnd_number.canonical_to_number(p_pk1_value)
69 AND msi.secondary_inventory_name = p_pk2_value
70 ;
71 --
72 CURSOR obj9 IS -- item-sub
73 SELECT msik.concatenated_segments||' / '||msi.SECONDARY_INVENTORY_NAME
74 FROM mtl_system_items_kfv msik
75 ,mtl_secondary_inventories msi
76 WHERE msi.organization_id = msik.organization_id
77 AND msi.secondary_inventory_name = p_pk3_value
78 AND msik.organization_id = fnd_number.canonical_to_number(p_pk1_value)
79 AND msik.inventory_item_id = fnd_number.canonical_to_number(p_pk2_value)
80 ;
81 --
82 CURSOR obj11 IS -- txn source type
83 SELECT mtst.transaction_source_type_name
84 FROM mtl_txn_source_types mtst
85 WHERE mtst.transaction_source_type_id = fnd_number.canonical_to_number(p_pk1_value)
86 ;
90 FROM mtl_transaction_types mtt
87 --
88 CURSOR obj12 IS -- txn type
89 SELECT mtt.transaction_type_name
91 WHERE mtt.transaction_type_id = fnd_number.canonical_to_number(p_pk1_value)
92 ;
93 --
94 CURSOR obj13 IS -- source project
95 SELECT ppa.segment1
96 FROM pa_projects_all ppa
97 WHERE ppa.project_id = fnd_number.canonical_to_number(p_pk1_value)
98 ;
99 --
100 CURSOR obj14 IS -- source task
101 SELECT ppa.segment1||' / '||pt.task_number
102 FROM pa_projects_all ppa
103 ,pa_tasks pt
104 where pt.task_id = fnd_number.canonical_to_number(p_pk1_value)
105 and ppa.project_id = pt.project_id
106 ;
107 --
108 CURSOR obj15 IS -- txn reason
109 SELECT mtr.reason_name
110 FROM mtl_transaction_reasons mtr
111 WHERE mtr.reason_id = fnd_number.canonical_to_number(p_pk1_value)
112 ;
113 --
114 CURSOR obj16 IS -- user
115 SELECT fu.user_name
116 FROM fnd_user fu
117 WHERE fu.user_id = fnd_number.canonical_to_number(p_pk1_value)
118 ;
119 --
120 CURSOR obj17 IS -- txn action
121 SELECT ml.meaning
122 FROM mfg_lookups ml
123 WHERE ml.lookup_type = 'MTL_TRANSACTION_ACTION'
124 AND ml.lookup_code = fnd_number.canonical_to_number(p_pk1_value)
125 ;
126 --
127 CURSOR obj19 IS -- opposing sub
128 SELECT mp.organization_code||' / '||msi.secondary_inventory_name
129 FROM mtl_parameters mp
130 ,mtl_secondary_inventories msi
131 WHERE msi.organization_id = fnd_number.canonical_to_number(p_pk1_value)
132 AND msi.secondary_inventory_name = p_pk2_value
133 AND mp.organization_id = msi.organization_id
134 ;
135 --
136 CURSOR obj21 IS -- uom
137 SELECT muom.unit_of_measure_tl
138 FROM mtl_units_of_measure muom
139 WHERE muom.uom_code = p_pk1_value
140 ;
141 --
142 CURSOR obj22 IS -- uom class
143 SELECT muc.uom_class_tl
144 FROM mtl_uom_classes muc
145 WHERE muc.uom_class = p_pk1_value
146 ;
147 --
148 CURSOR obj23 IS -- freight carrier
149 SELECT ofv.freight_code_tl
150 FROM org_freight ofv
151 WHERE ofv.organization_id = fnd_number.canonical_to_number(p_pk1_value)
152 AND ofv.freight_code = p_pk2_value
153 ;
154 --
155 CURSOR obj52 IS -- cat set / cat
156 SELECT mcs.category_set_name ||' / '||mck.concatenated_segments
157 FROM mtl_categories_kfv mck
158 ,mtl_category_sets_vl mcs
159 WHERE mcs.category_set_id = fnd_number.canonical_to_number(p_pk2_value)
160 AND mck.category_id = fnd_number.canonical_to_number(p_pk3_value)
161 ;
162 --
163 CURSOR obj55 IS -- ABC group / class
164 SELECT maag.assignment_group_name||' / '||mac.abc_class_name
165 FROM mtl_abc_classes mac
166 ,mtl_abc_assignment_groups maag
167 WHERE mac.abc_class_id = fnd_number.canonical_to_number(p_pk2_value)
168 AND maag.assignment_group_id = fnd_number.canonical_to_number(p_pk1_value)
169 ;
170 --Bug4579790
171 CURSOR obj30 IS ---Customer
172 SELECT party.party_name --rc.customer_name
173 FROM hz_parties party
174 ,hz_cust_accounts cust_acct --ra_customers rc
175 WHERE party.party_id = fnd_number.canonical_to_number(p_pk1_value)
176 AND cust_acct.party_id = party.party_id;
177 --
178 CURSOR obj56 IS ---Item Type
179 SELECT ml.meaning
180 FROM fnd_common_lookups ml
181 WHERE ml.lookup_type = 'ITEM_TYPE'
182 AND ml.lookup_code = rtrim(ltrim(p_pk1_value));
183
184 --
185 CURSOR obj1005 IS ---Order Type
186 SELECT ottv.name
187 FROM oe_transaction_types_vl ottv
188 WHERE ottv.transaction_type_id = fnd_number.canonical_to_number(p_pk1_value);
189
190 CURSOR obj100 IS
191 SELECT VENDOR_NAME
192 FROM PO_VENDORS
193 WHERE vendor_id = fnd_number.canonical_to_number(p_pk1_value);
194
195
196 BEGIN
197 -- validate input parameters
198 IF p_object_type_code IS NULL
199 OR p_object_type_code = g_miss_num
200 OR p_object_id IS NULL
201 OR p_object_id = g_miss_num
202 OR p_pk1_value IS NULL
203 OR p_pk1_value = g_miss_char
204 THEN
205 RETURN 'Insufficient parameters passed to '||
206 'WMS_Assignment_PVT.GetObjectValueName' ;
207 END IF;
208 --
209 -- function works for system-defined business objects only
210 IF p_object_type_code <> 1 THEN
211 RETURN NULL ;
212 END IF;
213 --
214 IF p_object_id = 0 THEN
215 l_value_name := NULL;
216 --
217 ELSIF p_object_id in (3,18) THEN
218 OPEN obj3;
219 FETCH obj3 INTO l_value_name;
220 IF obj3%notfound THEN
221 l_value_name := NULL;
222 END IF;
223 CLOSE obj3;
224 ELSIF p_object_id = 4 THEN
225 OPEN obj4;
226 FETCH obj4 INTO l_value_name;
227 IF obj4%notfound THEN
228 l_value_name := NULL ;
229 END IF;
230 CLOSE obj4;
231 ELSIF p_object_id = 7 THEN
232 OPEN obj7;
233 FETCH obj7 INTO l_value_name;
234 IF obj7%notfound THEN
235 l_value_name := NULL ;
236 END IF;
237 CLOSE obj7;
238 ELSIF p_object_id = 9 THEN
239 OPEN obj9;
240 FETCH obj9 INTO l_value_name;
241 IF obj9%notfound THEN
242 l_value_name := NULL ;
243 END IF;
244 CLOSE obj9;
245 ELSIF p_object_id = 11 THEN
246 OPEN obj11;
247 FETCH obj11 INTO l_value_name;
248 IF obj11%notfound THEN
249 l_value_name := NULL ;
250 END IF;
251 CLOSE obj11;
252 ELSIF p_object_id = 12 THEN
253 OPEN obj12;
254 FETCH obj12 INTO l_value_name;
255 IF obj12%notfound THEN
256 l_value_name := NULL ;
257 END IF;
258 CLOSE obj12;
259 ELSIF p_object_id = 13 THEN
260 OPEN obj13;
261 FETCH obj13 INTO l_value_name;
262 IF obj13%notfound THEN
263 l_value_name := NULL ;
264 END IF;
265 CLOSE obj13;
266 ELSIF p_object_id = 14 THEN
267 OPEN obj14;
268 FETCH obj14 INTO l_value_name;
269 IF obj14%notfound THEN
270 l_value_name := NULL;
271 END IF;
272 CLOSE obj14;
273 ELSIF p_object_id = 15 THEN
274 OPEN obj15;
275 FETCH obj15 INTO l_value_name;
276 IF obj15%notfound THEN
277 l_value_name := NULL;
278 END IF;
279 CLOSE obj15;
280 ELSIF p_object_id = 16 THEN
281 OPEN obj16;
282 FETCH obj16 INTO l_value_name;
283 IF obj16%notfound THEN
284 l_value_name := NULL;
285 END IF;
286 CLOSE obj16;
287 ELSIF p_object_id = 17 THEN
288 OPEN obj17;
289 FETCH obj17 INTO l_value_name;
290 IF obj17%notfound THEN
291 l_value_name := NULL;
292 END IF;
293 CLOSE obj17;
294 ELSIF p_object_id = 19 THEN
295 OPEN obj19;
296 FETCH obj19 INTO l_value_name;
297 IF obj19%notfound THEN
298 l_value_name := NULL;
299 END IF;
300 CLOSE obj19;
301 ELSIF p_object_id = 21 THEN
302 OPEN obj21;
303 FETCH obj21 INTO l_value_name;
304 IF obj21%notfound THEN
305 l_value_name := NULL;
306 END IF;
307 CLOSE obj21;
308 ELSIF p_object_id = 22 THEN
309 OPEN obj22;
310 FETCH obj22 INTO l_value_name;
311 IF obj22%notfound THEN
312 l_value_name := NULL;
313 END IF;
314 CLOSE obj22;
315 ELSIF p_object_id = 23 THEN
316 OPEN obj23;
317 FETCH obj23 INTO l_value_name;
318 IF obj23%notfound THEN
319 l_value_name := NULL;
320 END IF;
321 CLOSE obj23;
322 ELSIF p_object_id = 52 THEN
323 OPEN obj52;
324 FETCH obj52 INTO l_value_name;
325 IF obj52%notfound THEN
326 l_value_name := NULL;
327 END IF;
328 CLOSE obj52;
329 ELSIF p_object_id = 55 THEN
330 OPEN obj55;
331 FETCH obj55 INTO l_value_name;
332 IF obj55%notfound THEN
333 l_value_name := NULL;
334 END IF;
335 CLOSE obj55;
336 ELSIF p_object_id = 30 THEN
337 OPEN obj30;
338 FETCH obj30 INTO l_value_name;
339 IF obj30%notfound THEN
340 l_value_name := NULL;
341 END IF;
342 CLOSE obj30;
343 ELSIF p_object_id = 56 THEN
344 OPEN obj56;
345 FETCH obj56 INTO l_value_name;
346 IF obj56%notfound THEN
347 l_value_name := NULL;
348 END IF;
349 CLOSE obj56;
350 ELSIF p_object_id = 1005 THEN
351 OPEN obj1005;
352 FETCH obj1005 INTO l_value_name;
353 IF obj1005%notfound THEN
354 l_value_name := NULL;
355 END IF;
356 CLOSE obj1005;
357
358 ELSIF p_object_id = 100 THEN
359 OPEN obj100;
360 FETCH obj100 INTO l_value_name;
361 IF obj100%notfound THEN
362 l_value_name := NULL;
363 END IF;
364 CLOSE obj100;
365
366 ELSE
367 l_value_name := 'Missing code section for this object in '||
368 'WMS_Assignment_PVT.GetObjectValueName';
369 END IF;
370 RETURN l_value_name ;
371 EXCEPTION
372 WHEN OTHERS THEN
373 IF obj3%isopen THEN
374 CLOSE obj3;
375 END IF;
376 IF obj4%isopen THEN
377 CLOSE obj4;
378 END IF;
379 IF obj7%isopen THEN
380 CLOSE obj7;
381 END IF;
382 IF obj9%isopen THEN
383 CLOSE obj9;
384 END IF;
385 IF obj11%isopen THEN
386 CLOSE obj11;
387 END IF;
388 IF obj12%isopen THEN
389 CLOSE obj12;
390 END IF;
391 IF obj13%isopen THEN
392 CLOSE obj13;
393 END IF;
394 IF obj14%isopen THEN
395 CLOSE obj14;
396 END IF;
397 IF obj15%isopen THEN
398 CLOSE obj15;
399 END IF;
400 IF obj16%isopen THEN
401 CLOSE obj16;
402 END IF;
403 IF obj17%isopen THEN
404 CLOSE obj17;
405 END IF;
406 IF obj19%isopen THEN
407 CLOSE obj19;
408 END IF;
409 IF obj21%isopen THEN
410 CLOSE obj21;
411 END IF;
412 IF obj22%isopen THEN
413 CLOSE obj22;
414 END IF;
415 IF obj23%isopen THEN
416 CLOSE obj23;
417 END IF;
418 IF obj52%isopen THEN
419 CLOSE obj52;
420 END IF;
421 IF obj55%isopen THEN
422 CLOSE obj55;
423 END IF;
424 IF obj1005%isopen THEN
425 CLOSE obj1005;
426 END IF;
427 IF obj100%isopen THEN
428 CLOSE obj100;
429 END IF;
430 RETURN 'Error in WMS_Assignment_PVT' ;
431 END GetObjectValueName;
432 END WMS_Assignment_PVT;
[Home] [Help]