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