1 PACKAGE BODY WIP_COMMON AS
2 /* $Header: wipcommb.pls 120.0 2005/05/27 09:49:32 appldev noship $ */
3
4 --forward declarations
5 function check_disabled(
6 X_CLASS IN VARCHAR2,
7 X_ORG_ID IN NUMBER,
8 X_ENTITY_TYPE IN NUMBER)
9 return number;
10
11 function check_valid_class(
12 X_CLASS IN VARCHAR2,
13 X_ORG_ID IN NUMBER)
14 return number;
15
16
17 function default_acc_class
18 (X_ORG_ID IN NUMBER,
19 X_ITEM_ID IN NUMBER,
20 X_ENTITY_TYPE IN NUMBER,
21 X_PROJECT_ID IN NUMBER,
22 X_ERR_MESG_1 OUT NOCOPY VARCHAR2,
23 X_ERR_CLASS_1 OUT NOCOPY VARCHAR2,
24 X_ERR_MESG_2 OUT NOCOPY VARCHAR2,
25 X_ERR_CLASS_2 OUT NOCOPY VARCHAR2
26 )
27 return VARCHAR2 IS
28 V_PRODUCT_LINE CONSTANT NUMBER := 8;
29 V_COST_METHOD NUMBER(1);
30 V_COST_GROUP_ID NUMBER;
31 V_DISC_CLASS VARCHAR2(10);
32 V_EAM_CLASS VARCHAR2(10);
33 V_REP_CLASS VARCHAR2(10);
34 V_PRJ_DEF_CLASS VARCHAR2(10);
35 V_DISABLE_DATE DATE;
36 V_RET NUMBER;
37 V_RET1 NUMBER;
38 begin
39 X_ERR_MESG_1 := NULL;
40 X_ERR_CLASS_1 := NULL;
41 X_ERR_MESG_2 := NULL;
42 X_ERR_CLASS_2 := NULL;
43
44 if(x_entity_type = WIP_CONSTANTS.EAM) then
45 begin
46 select default_eam_class
47 into V_EAM_CLASS
48 from wip_eam_parameters
49 where organization_id = X_ORG_ID;
50 return V_EAM_CLASS;
51 exception
52 when no_data_found then
53 return null;
54 end;
55 end if;
56
57 select primary_cost_method
58 into V_COST_METHOD
59 from mtl_parameters
60 where
61 organization_id = X_ORG_ID;
62 if( V_COST_METHOD = WIP_CONSTANTS.COST_STD ) then
63 -- Standard Costing Organization
64 begin
65 select wdcac.std_discrete_class, wdcac.repetitive_assy_class
66 into V_DISC_CLASS, V_REP_CLASS
67 from mtl_default_category_sets mdcs, mtl_item_categories mic,
68 wip_def_cat_acc_classes wdcac
69 where
70 mdcs.functional_area_id = V_PRODUCT_LINE and
71 mdcs.category_set_id = mic.category_set_id and
72 mic.organization_id = X_ORG_ID and
73 mic.inventory_item_id = X_ITEM_ID and
74 wdcac.organization_id = X_ORG_ID and
75 mic.category_id = wdcac.category_id and
76 wdcac.cost_group_id IS NULL;
77
78 if( X_ENTITY_TYPE in (1,4) ) then
79 v_ret := check_disabled(V_DISC_CLASS, X_ORG_ID, X_ENTITY_TYPE);
80 elsif ( X_ENTITY_TYPE = 2) then
81 v_ret := check_disabled(V_REP_CLASS, X_ORG_ID, X_ENTITY_TYPE);
82 end if;
83
84 if( v_ret = 1 ) then
85 if (X_ENTITY_TYPE in (1,4) ) then
86 return(V_DISC_CLASS);
87 else
88 return(V_REP_CLASS);
89 end if;
90 else
91 if( X_ENTITY_TYPE in (1,4) ) then
92 X_ERR_MESG_1 := 'WIP_CLASS_PRODLINE_DISABLED';
93 X_ERR_CLASS_1 := V_DISC_CLASS;
94 V_DISC_CLASS := NULL;
95 elsif( X_ENTITY_TYPE = 2) then
96 X_ERR_MESG_1 := 'WIP_CLASS_PRODLINE_DISABLED';
97 X_ERR_CLASS_1 := V_REP_CLASS;
98 return(NULL);
99 end if;
100 end if;
101
102 exception
103 when NO_DATA_FOUND then
104 if( X_ENTITY_TYPE = 2) then
105 return(NULL);
106 end if;
107 end;
108 begin
109 if X_PROJECT_ID IS NOT NULL then
110 select wip_acct_class_code
111 into V_PRJ_DEF_CLASS
112 from mrp_project_parameters mpp
113 where
114 mpp.project_id = X_PROJECT_ID and
115 mpp.organization_id = X_ORG_ID;
116 end if;
117 exception
118 when NO_DATA_FOUND then
119 NULL;
120 end;
121 elsif( V_COST_METHOD in ( WIP_CONSTANTS.COST_AVG,
122 WIP_CONSTANTS.COST_FIFO,
123 WIP_CONSTANTS.COST_LIFO ) ) then
124 -- Average Costing Organization
125 if X_PROJECT_ID IS NOT NULL then
126 select NVL(costing_group_id,1), wip_acct_class_code
127 into V_COST_GROUP_ID, V_PRJ_DEF_CLASS
128 from mrp_project_parameters mpp
129 where
130 mpp.project_id = X_PROJECT_ID and
131 mpp.organization_id = X_ORG_ID;
132 else
133 /* Fix for bug 2491739
134 Replacing hard-coding of COST_GROUP_ID with a select statement
135 that gets Cost Group ID from mtl_parameters. */
136 /* V_COST_GROUP_ID := 1; */
137 select default_cost_group_id
138 into V_COST_GROUP_ID
139 from mtl_parameters
140 where organization_id = X_ORG_ID;
141 end if;
142
143 begin
144 select wdcac.std_discrete_class
145 into V_DISC_CLASS
146 from mtl_default_category_sets mdcs, mtl_item_categories mic,
147 wip_def_cat_acc_classes wdcac
148 where
149 mdcs.functional_area_id = V_PRODUCT_LINE and
150 mdcs.category_set_id = mic.category_set_id and
151 mic.organization_id = X_ORG_ID and
152 mic.inventory_item_id = X_ITEM_ID and
153 wdcac.organization_id = X_ORG_ID and
154 mic.category_id = wdcac.category_id and
155 wdcac.cost_group_id = V_COST_GROUP_ID;
156
157 v_ret := check_disabled(V_DISC_CLASS, X_ORG_ID, X_ENTITY_TYPE);
158 if( v_ret = 1) then
159 return(V_DISC_CLASS);
160 else
161 X_ERR_MESG_1 := 'WIP_CLASS_PRODLINE_DISABLED';
162 X_ERR_CLASS_1 := V_DISC_CLASS;
163 V_DISC_CLASS := NULL;
164 end if;
165 exception
166 when NO_DATA_FOUND then
167 NULL;
168 end;
169 end if;
170
171 if X_PROJECT_ID is null and V_DISC_CLASS is null then
172 -- Default from wip_parameters IFF there is no project and no class
173 -- defined yet.
174
175 SELECT wp.DEFAULT_DISCRETE_CLASS
176 INTO V_DISC_CLASS
177 FROM WIP_PARAMETERS wp
178 WHERE wp.ORGANIZATION_ID = X_ORG_ID;
179
180 v_ret := check_disabled(V_DISC_CLASS, X_ORG_ID, X_ENTITY_TYPE);
181 if ( v_ret = 0) then
182 X_ERR_MESG_2 := 'ACCT_CLASS_IS_DISABLED';
183 X_ERR_CLASS_2 := V_DISC_CLASS;
184 return(NULL);
185 else
186 v_ret1 := check_valid_class(V_DISC_CLASS, X_ORG_ID);
187 if( v_ret1 = 1) then
188 return(V_DISC_CLASS);
189 else
190 X_ERR_MESG_2 := 'WIP_NO_ASSOC_CLASS_CG';
191 X_ERR_CLASS_2 := V_DISC_CLASS;
192 return(NULL);
193 end if;
194 end if;
195 elsif X_PROJECT_ID is not NULL and V_PRJ_DEF_CLASS is not null then
196 -- Default from mrp_project_parameters
197
198 V_DISC_CLASS := V_PRJ_DEF_CLASS;
199 v_ret := check_disabled(V_DISC_CLASS, X_ORG_ID, X_ENTITY_TYPE);
200 if( v_ret = 1) then
201 return(V_DISC_CLASS);
202 else
203 X_ERR_MESG_2 := 'WIP_CLASS_PROJPARAM_DISABLED';
204 X_ERR_CLASS_2 := V_DISC_CLASS;
205 return(NULL);
206 end if;
207 else
208 return(NULL);
209 -- Project Id is defined but no class defined
210 -- in mrp_project_parameters or wip_def_cat_acc_classes
211 end if;
212
213 exception
214 when NO_DATA_FOUND then
215 return(NULL);
216
217 END;
218
219
220 function check_disabled(
221 X_CLASS IN VARCHAR2,
222 X_ORG_ID IN NUMBER,
223 X_ENTITY_TYPE IN NUMBER)
224 return number is
225 V_DISABLE_DATE DATE;
226
227 BEGIN
228 select nvl(wac.disable_date, SYSDATE + 1) into V_DISABLE_DATE
229 from wip_accounting_classes wac
230 where
231 wac.organization_id = X_ORG_ID and
232 wac.class_type = DECODE(X_ENTITY_TYPE, 1, WIP_CONSTANTS.DISC_CLASS,
233 2, WIP_CONSTANTS.REP_CLASS,
234 4, WIP_CONSTANTS.DISC_CLASS,
235 6, WIP_CONSTANTS.DISC_CLASS) and
236 wac.class_code = X_CLASS;
237 if V_DISABLE_DATE <= SYSDATE then
238 return(0);
239 else
240 return(1);
241 end if;
242
243 END;
244
245
246 function check_valid_class(
247 X_CLASS IN VARCHAR2,
248 X_ORG_ID IN NUMBER)
249 return number is
250 dummy VARCHAR2(40);
251 v_primary_cost_method number;
252 v_project_reference_enabled number;
253 BEGIN
254 select PRIMARY_COST_METHOD, PROJECT_REFERENCE_ENABLED
255 into v_primary_cost_method, v_project_reference_enabled
256 from mtl_parameters mp
257 where
258 mp.organization_id = X_ORG_ID;
259
260 if v_primary_cost_method in ( WIP_CONSTANTS.COST_AVG,
261 WIP_CONSTANTS.COST_FIFO,
262 WIP_CONSTANTS.COST_LIFO ) and
263 v_project_reference_enabled = 1 then
264 begin
265 select distinct class_code
266 into dummy
267 from cst_cg_wip_acct_classes ccwac
268 where
269 ccwac.organization_id = X_ORG_ID and
270 ccwac.class_code = X_CLASS and
271 nvl(ccwac.disable_date, SYSDATE + 1) > SYSDATE;
272 return(1);
273 exception
274 when NO_DATA_FOUND then
275 return(0);
276 end ;
277 else
278 return(1); -- For any other org, we don't care about cost_group
279 end if;
280
281 END;
282
283
284 function Bill_Exists(
285 p_item_id in number,
286 p_org_id in number) return number is
287 x_bom_rev_exists number := 0;
288 begin
289
290 select count(*) into x_bom_rev_exists
291 from bom_bill_of_materials
292 where assembly_item_id = p_item_id
293 and organization_id = p_Org_id
294 and alternate_bom_designator is null ;
295
296 if (x_bom_rev_exists > 0) then
297 RETURN 1;
298 else
299 RETURN 0;
300 end if;
301
302 exception
303 when others then
304 return -1 ;
305
306 end Bill_Exists;
307
308
309
310
311 function Revision_Exists(
312 p_item_id in number,
313 p_org_id in number) return number is
314 x_rev_exists number := 0;
315 begin
316
317 select revision_qty_control_code into x_rev_exists
318 from mtl_system_items
319 where inventory_item_id = p_item_id
320 and organization_id = p_Org_id ;
321
322
323 return x_rev_exists ;
324
325 exception
326 when no_data_found then
327 return -2 ;
328 when others then
329 return -1 ;
330
331 end Revision_Exists;
332
333
334
335
336 function Routing_Exists(
337 p_item_id in number,
338 p_org_id in number,
339 p_eff_date IN DATE := null
340 ) return number is
341 x_rtg_exists number := 0;
342 begin
343
344 -- Modified for ECO implement.
345
346 IF p_eff_date IS NULL THEN
347 select count(*) into x_rtg_exists
348 from mtl_rtg_item_revisions
349 where inventory_item_id = p_item_id
350 and organization_id = p_Org_id
351 and implementation_date is not null;
352 ELSE
353 select count(*) into x_rtg_exists
354 from mtl_rtg_item_revisions
355 where inventory_item_id = p_item_id
356 and organization_id = p_Org_id
357 AND effectivity_date <= p_eff_date
358 and implementation_date is not null;
359
360 END IF;
361
362 if (x_rtg_exists > 0) then
363 RETURN 1;
364 else
365 RETURN 0;
366 end if;
367
368 exception
369 when others then
370 return -1 ;
371
372 end Routing_Exists;
373
374
375
376
377 function Is_Primary_UOM(
378 p_item_id in number,
379 p_org_id in number,
380 p_txn_uom in varchar2,
381 p_pri_uom in out nocopy varchar2) return number is
382 begin
383
384 Select PRIMARY_UOM_CODE INTO p_pri_uom
385 FROM mtl_system_items
386 WHERE inventory_item_Id = p_item_id
387 AND organization_Id = p_org_id;
388
389 if (p_txn_uom = p_pri_uom) then
390 return 1 ;
391 else
392 return 0 ;
393 end if ;
394
395 exception
396 when no_data_found then
397 return -2 ;
398 when others then
399 return -1 ;
400
401 end Is_Primary_UOM ;
402
403 /*=====================================================================+
404 | PROCEDURE
405 | get_total_quantity
406 |
407 | PURPOSE
408 | This procedure would return the total quantity in a job/schedule
409 | in an out nocopy variable. The total quantity is the sum of all assemblies in
410 | all the operations, which may be different from the start quantity.
411 |
412 |
413 | ARGUMENTS
414 |
415 | EXCEPTIONS
416 |
417 | NOTES
418 |
419 +=====================================================================*/
420
421 PROCEDURE get_total_quantity
422 (
423 p_organization_id IN NUMBER,
424 p_wip_entity_id IN NUMBER,
425 p_repetitive_schedule_id IN NUMBER DEFAULT NULL,
426 p_total_quantity OUT NOCOPY NUMBER
427 ) IS
428 BEGIN
429
430 SELECT SUM(quantity_in_queue)
431 + SUM(quantity_running)
432 + SUM(quantity_waiting_to_move)
433 + SUM(quantity_rejected)
434 + SUM(quantity_scrapped)
435 -- + SUM(quantity_completed)
436 -- These assemblies are in some other operation
437 INTO p_total_quantity
438 FROM wip_operations wop
439 WHERE
440 wop.organization_id = p_organization_id
441 AND wop.wip_entity_id = p_wip_entity_id
442 AND
443 Decode(Decode(Nvl(p_repetitive_schedule_id,-1),
444 -1, wip_constants.discrete,
445 wip_constants.repetitive),
446 wip_constants.discrete,0,
447 wip_constants.repetitive,wop.repetitive_schedule_id)
448 = Decode(Decode(Nvl(p_repetitive_schedule_id,-1),
449 -1, wip_constants.discrete,
450 wip_constants.repetitive),
451 wip_constants.discrete,0,
452 wip_constants.repetitive,p_repetitive_schedule_id);
453
454 IF(p_repetitive_schedule_id IS NULL) THEN /* Discrete Job */
455
456 SELECT NVL(p_total_quantity,0) + Nvl(wdj.quantity_completed,0)
457 INTO p_total_quantity
458 FROM wip_discrete_jobs wdj
459 WHERE
460 wdj.organization_id = p_organization_id
461 AND wdj.wip_entity_id = p_wip_entity_id;
462
463 ELSE
464
465 SELECT NVL(p_total_quantity,0) + Nvl(wrs.quantity_completed,0)
466 INTO p_total_quantity
467 FROM wip_repetitive_schedules wrs
468 WHERE
469 wrs.organization_id = p_organization_id
470 AND wrs.wip_entity_id = p_wip_entity_id
471 AND wrs.repetitive_schedule_id = p_repetitive_schedule_id;
472 END IF;
473
474 END;
475
476 /*=====================================================================+
477 | PROCEDURE
478 | Get_Released_Revs_Type_Meaning
479 |
480 | PURPOSE
481 | This procedure would return the value and meaning of profile WIP profile
482 | 'WIP:Exclude ECOs'.
483 |
484 | ARGUMENTS
485 |
486 | EXCEPTIONS
487 |
488 | NOTES
489 |
490 +=====================================================================*/
491
492
493 PROCEDURE Get_Released_Revs_Type_Meaning
494 (
495 x_released_revs_type OUT NOCOPY NUMBER,
496 x_released_revs_meaning OUT NOCOPY Varchar2
497 ) IS
498 BEGIN
499 -- set up release type
500 x_released_revs_type := fnd_profile.value('WIP_RELEASED_REVS');
501 IF (x_released_revs_type is null) then
502 x_released_revs_type := 1;
503 END IF;
504 IF (x_released_revs_type = 2) then
505 x_released_revs_meaning := 'EXCLUDE_HOLD';
506 ELSIF (x_released_revs_type = 1) then
507 x_released_revs_meaning := 'EXCLUDE_OPEN_HOLD';
508 ELSIF (x_released_revs_type = 0) then
509 x_released_revs_meaning := 'EXCLUDE_ALL';
510 END IF;
511
512 END Get_Released_Revs_Type_Meaning;
513
514 END WIP_COMMON;
[Home] [Help]