1 Package Body GMD_COMMON_VAL AS
2 /* $Header: GMDPCOMB.pls 120.22.12020000.2 2012/07/17 10:31:23 mtou ship $ */
3
4
5 --Bug 3222090, NSRIVAST 20-FEB-2004, BEGIN
6 --Forward declaration.
7 FUNCTION set_debug_flag RETURN VARCHAR2;
8 l_debug VARCHAR2(1) := set_debug_flag;
9
10 FUNCTION set_debug_flag RETURN VARCHAR2 IS
11 l_debug VARCHAR2(1):= 'N';
12 BEGIN
13 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
14 l_debug := 'Y';
15 END IF;
16 RETURN l_debug;
17 END set_debug_flag;
18 --Bug 3222090, NSRIVAST 20-FEB-2004, END
19
20 /* ********************************************************************* */
21 /* Purpose: Validation functions and procedures used by more than one */
22 /* part of GMD (Routings, Ops, Formula, QC, Recipes, Lab) */
23 /* */
24 /* Some code common to more than one module can be found in GMA_VALID_GRP*/
25 /* (ex: validate_um, validate_orgn_code, validate_type) */
26 /* */
27 /* check_from_date */
28 /* check_date */
29 /* check_date_range */
30 /* get_customer_id */
31 /* customer_exists */
32 /* check_project_id */
33 /* check_user_id */
34 /* action_code */
35 /* */
36 /* */
37 /* MODIFICATION HISTORY */
38 /* Person Date Comments */
39 /* --------- ------ ------------------------------------------ */
40 /* 14Nov2000 Created */
41 /* ********************************************************************* */
42 FUNCTION check_from_date(pfrom_date IN DATE,
43 pcalledby_form IN VARCHAR2) RETURN NUMBER
44 IS
45 BEGIN
46 return 1;
47 END check_from_date;
48
49 FUNCTION check_date(pdate IN DATE,
50 pcalledby_form IN VARCHAR2) RETURN NUMBER
51 IS
52 BEGIN
53 return 1;
54 END check_date;
55
56 FUNCTION check_date_range(pfrom_date IN DATE,
57 pto_date IN DATE,
58 pcalledby_form IN VARCHAR2) RETURN NUMBER
59 IS
60 BEGIN
61 return 1;
62
63 END;
64
65 /* ======================================================================= */
66 /* PROCEDURE: */
67 /* get_customer_id */
68 /* */
69 /* DESCRIPTION: */
70 /* This PL/SQL function is responsible for */
71 /* retrieving a customer's surrogate key unique number */
72 /* based on the passed in customer number. */
73 /* */
74 /* CUST_ID is returned in the xvalue parameter */
75 /* and xreturn_code is 0 (zero) upon success. Failure */
76 /* returns xvalue as NULL and xreturn_code contains the */
77 /* error code. */
78 /* */
79 /* SYNOPSIS: */
80 /* iret := GMDFMVAL_PUB.get_customer_id(pcustomer_no, */
81 /* xvalue, */
82 /* xreturn_code); */
83 /* */
84 /* RETURNS: */
85 /* 0 Success */
86 /* -92205 Customer ID not found. */
87 /* < 0 RDBMS error */
88 /* ======================================================================== */
89
90 PROCEDURE get_customer_id(pcustomer_no IN VARCHAR2,
91 xcust_id OUT NOCOPY NUMBER,
92 xsite_id OUT NOCOPY NUMBER,
93 xorg_id OUT NOCOPY NUMBER,
94 xreturn_code OUT NOCOPY NUMBER) IS
95
96 /* Local variables. */
97 l_cust_id hz_cust_accounts.cust_account_id%TYPE := 0;
98 l_site_id hz_cust_site_uses_all.site_use_id%TYPE := 0;
99 l_org_id hz_cust_site_uses_all.org_id%TYPE := 0;
100
101
102 /* Cursor Definitions. */
103 /* =================== */
104 CURSOR get_id IS
105 SELECT cust_acct.cust_account_id, site.site_use_id, site.org_id
106 FROM hz_parties party,
107 hz_cust_accounts cust_acct,
108 hz_cust_acct_sites_all acct_site,
109 hz_cust_site_uses_all site,
110 hz_party_sites party_site,
111 hz_locations loc
112 WHERE acct_site.cust_account_id=cust_acct.cust_account_id
113 and cust_acct.party_id=party.party_id
114 and site.site_use_code='SHIP_TO'
115 and site.cust_acct_site_id=acct_site.cust_acct_site_id
116 and acct_site.status='A'
117 and site.cust_acct_site_id=acct_site.cust_acct_site_id
118 and acct_site.party_site_id=party_site.party_site_id
119 and party_site.location_id=loc.location_id
120 and party.party_number = UPPER(pcustomer_no);
121
122
123 /* ================================================ */
124 BEGIN
125 OPEN get_id;
126 FETCH get_id INTO l_cust_id,l_site_id,l_org_id;
127 IF (get_id%NOTFOUND) THEN
128 xcust_id := NULL;
129 xsite_id := NULL;
130 xorg_id := NULL;
131 xreturn_code := FMVAL_CUSTID_ERR;
132 CLOSE get_id;
133 RETURN;
134 END IF;
135
136 xcust_id := l_cust_id;
137 xsite_id := l_site_id;
138 xorg_id := l_org_id;
139 xreturn_code := 0;
140 CLOSE get_id;
141 RETURN;
142
143 EXCEPTION
144 WHEN OTHERS THEN
145
146 RETURN;
147 END get_customer_id;
148
149
150 /* **************************************************************************
151 * NAME
152 * customer_exists
153 * DESCRIPTION
154 * This procedure will check if given id or name exist in OP_CUST_MST.
155 * If name provided, id will be returned.
156 * Currently used by recipes and QC
157 * PARAMETERS standard + customer_id, customer_no
158 * RETURN VALUES standard + customer_id
159 *
160 * LrJackson 27Dec2000 Copied from recipe_exists
161 * Raju Added new cursors to check siteid and orgid exists for that customer.
162 **************************************************************************** */
163
164 PROCEDURE customer_exists
165 ( p_api_version IN NUMBER,
166 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
167 p_commit IN VARCHAR2 := FND_API.G_FALSE,
168 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
169 p_customer_id IN NUMBER,
170 p_site_id IN NUMBER,
171 p_org_id IN NUMBER,
172 p_customer_no IN VARCHAR2,
173 x_return_status OUT NOCOPY VARCHAR2,
174 x_msg_count OUT NOCOPY NUMBER,
175 x_msg_data OUT NOCOPY VARCHAR2,
176 x_return_code OUT NOCOPY NUMBER,
177 x_customer_id OUT NOCOPY NUMBER) IS
178
179 CURSOR get_record IS
180 SELECT cust_acct.cust_account_id
181 FROM hz_parties party,
182 hz_cust_accounts cust_acct,
183 hz_cust_acct_sites_all acct_site,
184 hz_cust_site_uses_all site,
185 hz_party_sites party_site,
186 hz_locations loc
187 where acct_site.cust_account_id=cust_acct.cust_account_id
188 and cust_acct.party_id=party.party_id
189 and site.site_use_code='SHIP_TO'
190 and site.cust_acct_site_id=acct_site.cust_acct_site_id
191 and acct_site.status='A'
192 and site.cust_acct_site_id=acct_site.cust_acct_site_id
193 and acct_site.party_site_id=party_site.party_site_id
194 and party_site.location_id=loc.location_id
195 and (cust_acct.cust_account_id = p_customer_id OR party.party_number = UPPER(p_customer_no));
196
197 CURSOR get_site IS
198 SELECT site.site_use_id
199 FROM hz_parties party,
200 hz_cust_accounts cust_acct,
201 hz_cust_acct_sites_all acct_site,
202 hz_cust_site_uses_all site,
203 hz_party_sites party_site,
204 hz_locations loc
205 where acct_site.cust_account_id=cust_acct.cust_account_id
206 and cust_acct.party_id=party.party_id
207 and site.site_use_code='SHIP_TO'
208 and site.cust_acct_site_id=acct_site.cust_acct_site_id
209 and acct_site.status='A'
210 and site.cust_acct_site_id=acct_site.cust_acct_site_id
211 and acct_site.party_site_id=party_site.party_site_id
212 and party_site.location_id=loc.location_id
213 and site.site_use_id = p_site_id
214 and (cust_acct.cust_account_id = p_customer_id OR party.party_number = UPPER(p_customer_no));
215
216 CURSOR get_orgid IS
217 SELECT site.org_id
218 FROM hz_parties party,
219 hz_cust_accounts cust_acct,
220 hz_cust_acct_sites_all acct_site,
221 hz_cust_site_uses_all site,
222 hz_party_sites party_site,
223 hz_locations loc
224 where acct_site.cust_account_id=cust_acct.cust_account_id
225 and cust_acct.party_id=party.party_id
226 and site.site_use_code='SHIP_TO'
227 and site.cust_acct_site_id=acct_site.cust_acct_site_id
228 and acct_site.status='A'
229 and site.cust_acct_site_id=acct_site.cust_acct_site_id
230 and acct_site.party_site_id=party_site.party_site_id
231 and party_site.location_id=loc.location_id
232 and site.org_id = p_org_id
233 and (cust_acct.cust_account_id = p_customer_id OR party.party_number = UPPER(p_customer_no));
234
235 /*** Variables ***/
236 l_api_name CONSTANT VARCHAR2(30) := 'CUSTOMER_EXISTS';
237 l_api_version CONSTANT NUMBER := 1.0;
238 l_site_id NUMBER;
239 l_org_id NUMBER;
240
241 BEGIN
242 /* no SAVEPOINT needed because there is no insert/update/delete */
243 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
244 l_api_name, G_PKG_NAME) THEN
245 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
246 END IF;
247 IF FND_API.to_Boolean(p_init_msg_list) THEN
248 FND_MSG_PUB.initialize;
249 END IF;
250 x_return_status := FND_API.G_RET_STS_SUCCESS;
251
252 IF (p_customer_id IS NOT NULL OR p_customer_no IS NOT NULL) THEN
253 OPEN get_record;
254 FETCH get_record into x_customer_id;
255 IF get_record%NOTFOUND THEN
256 RAISE fnd_api.g_exc_error;
257 END IF;
258 CLOSE get_record;
259 END IF;
260
261 IF (p_site_id IS NOT NULL) THEN
262 OPEN get_site;
263 FETCH get_site into l_site_id;
264 IF get_site%NOTFOUND THEN
265 RAISE fnd_api.g_exc_error;
266 END IF;
267 CLOSE get_site;
268 END IF;
269
270 IF (p_org_id IS NOT NULL) THEN
271 OPEN get_orgid;
272 FETCH get_orgid into l_org_id;
273 IF get_orgid%NOTFOUND THEN
274 RAISE fnd_api.g_exc_error;
275 END IF;
276 CLOSE get_orgid;
277 END IF;
278
279 /* no standard check of p_commit because no insert/update/delete */
280
281 /* standard call to get msge cnt, and if cnt is 1, get mesg info */
282 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
283
284 EXCEPTION
285 WHEN FND_API.G_EXC_ERROR THEN
286 X_return_code := SQLCODE;
287 x_return_status := FND_API.G_RET_STS_ERROR;
288 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
289
290 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
291 X_return_code := SQLCODE;
292
293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
294 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
295
296 WHEN OTHERS THEN
297 X_return_code := SQLCODE;
298 x_return_status := FND_API.G_RET_STS_ERROR;
299 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
300
301 END customer_exists;
302
303
304 /* ===================================================== */
305 /* PROCEDURE: check_user_id */
306 /* */
307 /* Send a user_id, get a return code of 0 if user exists,*/
308 /* anything else is an error */
309 /* */
310 /* LeAta Jackson 22Dec2000 Created */
311 /* ===================================================== */
312 PROCEDURE check_user_id (p_api_version IN NUMBER,
313 p_init_msg_list IN VARCHAR2,
314 p_commit IN VARCHAR2,
315 p_validation_level IN NUMBER,
316 p_user_id IN NUMBER,
317 x_return_status OUT NOCOPY VARCHAR2,
318 x_msg_count OUT NOCOPY NUMBER,
319 x_msg_data OUT NOCOPY VARCHAR2,
320 x_return_code OUT NOCOPY NUMBER)
321
322 IS
323 CURSOR get_user IS
324 SELECT user_id
325 FROM fnd_user
326 WHERE user_id = p_user_id;
327
328 l_user_id fnd_user.user_id%TYPE;
329
330 BEGIN
331 OPEN get_user;
332 FETCH get_user INTO l_user_id;
333
334 IF get_user%NOTFOUND THEN
335 x_return_code := -1;
336 x_return_status := 'E';
337 ELSE
338 x_return_code := 0;
339
340 x_return_status := 'S';
341 END IF;
342
343 CLOSE get_user;
344
345 END check_user_id;
346
347
348 /* **************************************************************************
349 * NAME
350 * action_code
351 * DESCRIPTION
352 * This procedure will check that given action code is valid. I, U, D
353 * PARAMETERS - standard +
354 * p_action_code
355 * RETURN VALUES - standard
356 * MODIFICATION HISTORY
357 * Person Date Comments
358 *
359 **************************************************************************** */
360 PROCEDURE action_code
361 ( p_api_version IN NUMBER,
362 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
363 p_commit IN VARCHAR2 := FND_API.G_FALSE,
364 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
365 p_action_code IN VARCHAR2,
366 x_return_status OUT NOCOPY VARCHAR2,
367 x_msg_count OUT NOCOPY NUMBER,
368 x_msg_data OUT NOCOPY VARCHAR2,
369 x_return_code OUT NOCOPY NUMBER)
370 IS
371 /*** Variables ***/
372 l_api_name CONSTANT VARCHAR2(30) := 'ACTION_CODE';
373 l_api_version CONSTANT NUMBER := 1.0;
374
375 BEGIN
376 /* no SAVEPOINT needed because there is no insert/update/delete */
377 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
378 l_api_name, G_PKG_NAME) THEN
379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
380 END IF;
381 IF FND_API.to_Boolean(p_init_msg_list) THEN
382 FND_MSG_PUB.initialize;
383 END IF;
384 x_return_status := FND_API.G_RET_STS_SUCCESS;
385
386
387 IF p_action_code is null or p_action_code not in ('I', 'U', 'D') THEN
388 RAISE fnd_api.g_exc_error;
389 END IF;
390
391 /* no standard check of p_commit because no insert/update/delete */
392
393 /* standard call to get msge cnt, and if cnt is 1, get mesg info */
394 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
395
396 EXCEPTION
397 WHEN FND_API.G_EXC_ERROR THEN
398 X_return_code := SQLCODE;
399 x_return_status := FND_API.G_RET_STS_ERROR;
400 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
401
402 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
403 X_return_code := SQLCODE;
404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
405 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
406
407 WHEN OTHERS THEN
408 X_return_code := SQLCODE;
409 x_return_status := FND_API.G_RET_STS_ERROR;
410
411 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
412
413 END action_code;
414
415 /* ========================================================= */
416 /* Procedure: */
417 /* Get_Status */
418 /* */
419 /* DESCRIPTION: */
420 /* This PL/SQL procedure fetch the status details - */
421 /* it gets the status meaning and description given the */
422 /* status code */
423 /* Return E if no status code exists */
424 /* Return S if status code is found */
425 /* ========================================================= */
426 PROCEDURE Get_Status
427 (
428 Status_code IN GMD_STATUS.Status_code%TYPE ,
429 Meaning OUT NOCOPY GMD_STATUS.Meaning%TYPE ,
430 Description OUT NOCOPY GMD_STATUS.Description%TYPE ,
431 x_return_status OUT NOCOPY VARCHAR2
432 ) IS
433 CURSOR Status_Cur(vStatus_code VARCHAR2) IS
434 SELECT Distinct status_code, Meaning, Description
435 FROM GMD_STATUS
436 Where status_code = vStatus_code;
437
438 l_status_code GMD_STATUS.Status_code%TYPE;
439 l_meaning GMD_STATUS.meaning%TYPE;
440 l_description GMD_STATUS.description%TYPE;
441
442 BEGIN
443
444 OPEN Status_cur(Status_code);
445 FETCH Status_cur INTO l_status_code, l_meaning, l_description;
446 IF (Status_cur%NOTFOUND) THEN
447 x_return_status := 'E';
448 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_STATUS_CODE');
449 FND_MSG_PUB.Add;
450 Else
451 x_return_status := 'S';
452 Meaning := l_meaning;
453 Description := l_description;
454 End If;
455 CLOSE Status_cur;
456
457 END Get_Status;
458
459 /* ************************************************************************ */
460 /* Procedure: */
461 /* Calculate_Process_loss */
462 /* */
463 /* DESCRIPTION: */
464 /* This PL/SQL procedure calculates the process loss value */
465 /* Parameters input */
466 /* Name Type */
467 /* 1) process_loss Record_type */
468 /* This record type comprises of the following fields */
469 /* qty Its the routing or recipe or batch quantity for which the*/
470 /* theo process will be calculated. */
471 /* User could pass a null value and expect the qty value to */
472 /* calculated. e.g recipe quantity can be derived. */
473 /* Recipe_id Null value could be passed, e.g for deriving the theo and*/
474 /* planned process at routing level (used from routing form)*/
475 /* need not have recipe information. */
476 /* If user decides to pass the recipe_id then the formula_id*/
477 /* routing_id information is redundant */
478 /* formula_id Can be null value. However need a value if the user does*/
479 /* not pass the recipe_id. */
480 /* Routing_id Cannot be null. Both theo or planned loss are not */
481 /* calculated when routing_id is not passed. */
482 /* */
483 /* Matrix for all conditions */
484 /* ----------------------------------------------------------------------| */
485 /* | Entity Required Comments | */
486 /* | | */
487 /* | qty No If not null, this value is used for all | */
488 /* | calculations. | */
489 /* | Yes If the calculation of losses is for a BATCH | */
490 /* | | */
491 /* | Recipe_id No If null, formula is required | */
492 /* | Yes If the calculation of losses is for a BATCH | */
493 /* | | */
494 /* | Formula_id No If null, recipe_id is required | */
495 /* | | */
496 /* | Routing_id Yes Passed or derived using Recipe_id | */
497 /* | | */
498 /* |---------------------------------------------------------------------| */
499 /* */
500 /* 2) Entity_type Takes 3 values: Routing, Recipe and Batch. */
501 /* */
502 /* OUT parameters */
503 /* x_recipe_theo_loss :Returns the Recipe theoretical Process loss */
504 /* x_process_loss :Returns the planned process loss for Routing, */
505 /* Recipe or Batch */
506 /* x_return_status */
507 /* Returns E if any error occues during the calculation */
508 /* e.g No routing class is associated with the routing */
509 /* Returns S if process loss is calculated and status is success */
510 /* Returns U if unexpected error occurs */
511 /* */
512 /* Step1 - Based on the routing_id get the routing_class from fm_rout_hdr */
513 /* Step2 - Based on routing_class get the UOM from fm_rout_cls table */
514 /* Step3 - Check if recipe uom can be converted to this UOM and convert */
515 /* total product qty to this UOM */
516 /* Step4 - Get the process loss from gmd_process_loss table. */
517 /* Step5 - apply a prorated values in routing */
518 /* Step 6 - apply prorated values in recipe for batch */
519 /* ======================================================================== */
520 /* HISTORY */
521 /* L.R.Jackson 05Jul2001 Bug 1857225. Initialize message list. */
522 /* Set return status = E when no routing is found */
523 /* Form will show messages as notes in */
524 /* the status bar if return status = E. */
525 /* Shyam 07/12/2001 The Total Output Qty value can be 0. */
526 /* Changed the condition (l_recipe_qty > 0) to */
527 /* (l_recipe_qty >= 0) prior to deriving the */
528 /* process loss (in line#662 ) for l_recipe_qty */
529 /* Shyam 04DEC2001 BUG # 2119151: With OPM Family Pack H changes. */
530 /* This was done to prevent ZERO divide error that */
531 /* have occurred if the theoretical process loss */
532 /* (l_routing_theo_loss) was 0. */
533 /* Uday Phadtare 13-MAR-2008 Bug 6871738. Select ROUTING_CLASS_UOM */
534 /* instead of UOM in Cursor Rout_cls_cur. */
535 /* ************************************************************************ */
536
537 PROCEDURE Calculate_Process_loss
538 ( process_loss IN process_loss_rec ,
539 Entity_type IN VARCHAR2 ,
540 x_recipe_theo_loss OUT NOCOPY GMD_PROCESS_LOSS.process_loss%TYPE ,
541 x_process_loss OUT NOCOPY GMD_PROCESS_LOSS.process_loss%TYPE ,
542 x_return_status OUT NOCOPY VARCHAR2 ,
543 x_msg_count OUT NOCOPY NUMBER ,
544 x_msg_data OUT NOCOPY VARCHAR2
545 ) IS
546
547 l_process_loss NUMBER;
548 l_recipe_qty NUMBER;
549 l_ing_qty NUMBER;
550 l_uom VARCHAR2(4);
551 l_item_um VARCHAR2(3);
552 l_routing_class fm_rout_hdr.routing_class%TYPE;
553 l_routing_uom fm_rout_cls.UOM%TYPE;
554 l_routing_qty fm_rout_hdr.routing_qty%TYPE;
555 l_routing_planned_loss NUMBER;
556 l_routing_theo_loss NUMBER;
557 l_routing_prorate_factor NUMBER := 1;
558 l_recipe_prorate_factor NUMBER := 1;
559 l_qty NUMBER;
560 l_recipe_id NUMBER;
561 l_formula_id NUMBER;
562 l_routing_id NUMBER;
563 l_recipe_pp_loss NUMBER;
564 l_toq_return_status VARCHAR2(5) := 'S';
565 /* Bug 1683702 - Thomas Daniel */
566 l_validity_rule_id NUMBER;
567 l_validity_qty NUMBER;
568 l_recipe_theo_loss NUMBER;
569 l_validity_orgn NUMBER;
570 l_orgn_process_loss NUMBER;
571 l_validity_scale_factor NUMBER;
572 l_item_id NUMBER(10);
573 l_validity_um VARCHAR2(4);
574
575 CURSOR Get_recipe_cur(vRecipe_id NUMBER) IS
576 Select formula_id, routing_id, planned_process_loss
577 From gmd_recipes
578 Where recipe_id = vRecipe_id;
579
580 Cursor Rout_hdr_cur(vRouting_id NUMBER) IS
581 Select Routing_class, Routing_qty, Process_loss, routing_uom
582 From gmd_routings_b
583 Where routing_id = vRouting_id
584 and delete_mark = 0;
585
586 --Bug 6871738. Select ROUTING_CLASS_UOM instead of UOM.
587 Cursor Rout_cls_cur(vRouting_class fm_rout_hdr.routing_class%TYPE) IS
588 Select ROUTING_CLASS_UOM
589 From fm_rout_cls
590 Where routing_class = vRouting_class
591 and delete_mark = 0;
592
593 CURSOR process_loss_cur(vRouting_class VARCHAR2, qty NUMBER) IS
594 SELECT process_loss
595 FROM gmd_process_loss
596 WHERE routing_class = vRouting_class AND
597 (max_quantity >= qty OR
598 max_quantity IS NULL)
599 ORDER BY max_quantity;
600
601 CURSOR get_total_qty_cur(vFormula_id NUMBER) IS
602 SELECT total_output_qty, yield_uom
603 FROM fm_form_mst_b
604 WHERE formula_id = vFormula_id;
605
606 -- NPD Conv.
607 CURSOR Get_validity_cur (vValidity_Rule_Id NUMBER) IS
608 SELECT r.recipe_id, formula_id, routing_id, v.std_qty, v.organization_id, v.inventory_item_id, v.detail_uom
609 FROM gmd_recipes_b r, gmd_recipe_validity_rules v
610 WHERE r.recipe_id = v.recipe_id
611 AND v.recipe_validity_rule_id = vValidity_Rule_Id;
612
613 CURSOR Get_recipe_orgn_loss (vRecipe_id NUMBER, vOrgn_code_id VARCHAR2) IS
614 SELECT process_loss
615 FROM gmd_recipe_process_loss
616 WHERE recipe_id = vRecipe_id
617 AND organization_id = vOrgn_code_id;
618
619 BEGIN
620 IF (l_debug = 'Y') THEN
621 gmd_debug.log_initialize('CalcProcessLoss');
622 END IF;
623 /* Initialize the return status and the message list*/
624 x_return_status := 'S';
625 FND_MSG_PUB.initialize;
626
627 /* get the values from the record that is input */
628 l_qty := process_loss.qty;
629 l_recipe_id := process_loss.recipe_id;
630 l_formula_id := process_loss.formula_id;
631 l_routing_id := process_loss.routing_id;
632
633 /* Bug 1683702 - Thomas Daniel */
634 l_validity_rule_id := process_loss.validity_rule_id;
635
636 /* Condition when recipe_id is not passed */
637 IF ((NVL(l_recipe_id,0) > 0) AND (l_validity_rule_id IS NULL AND
638 l_formula_id IS NULL)) THEN
639 IF (l_debug = 'Y') THEN
640 gmd_debug.put_line(' Fetching the details using recipe id :'||l_recipe_id);
641 END IF;
642 /* Get the formula and routing information based on the recipe*/
643 OPEN Get_recipe_cur(l_Recipe_id);
644 FETCH Get_recipe_cur
645 INTO l_formula_id, l_routing_id, l_recipe_pp_loss;
646 CLOSE Get_recipe_cur;
647 l_item_id := process_loss.inventory_item_id;
648 ELSIF ((NVL(l_validity_rule_id, 0) > 0) AND (l_formula_id IS NULL)) THEN
649 IF (l_debug = 'Y') THEN
650 gmd_debug.put_line(' Fetching the details using validity rule id :' ||l_validity_rule_id);
651 END IF;
652 /* Get the formula and routing information based on the validity rule*/
653 OPEN Get_validity_cur(l_validity_rule_id);
654 FETCH Get_validity_cur
655 INTO l_recipe_id, l_formula_id, l_routing_id, l_validity_qty,
656 l_validity_orgn, l_item_id, l_validity_um;
657 CLOSE Get_validity_cur;
658 END IF;
659
660 IF process_loss.organization_id IS NOT NULL THEN
661 l_validity_orgn := process_loss.organization_id;
662 END IF;
663
664 /* If routing id is null then theo loss cannot be calculated. */
665 IF (l_routing_id IS NOT NULL) THEN
666 /* Check if routing class exists */
667 /* If no routing class exists then there is no theo process loss */
668 OPEN Rout_hdr_cur(l_Routing_id);
669 FETCH Rout_hdr_cur
670 INTO l_routing_class, l_routing_qty, l_routing_planned_loss, l_item_um;
671 IF (Rout_hdr_cur%NOTFOUND) THEN
672 l_routing_class := NULL;
673 /* TPL cannot be calculated */
674 x_return_status := 'E';
675 FND_MESSAGE.SET_NAME('GMD', 'GMD_TPL_WO_ROUT_CLS');
676 FND_MSG_PUB.Add;
677 END IF;
678 CLOSE Rout_hdr_cur;
679 IF (l_debug = 'Y') THEN
680 gmd_debug.put_line(' Rout Class:'||l_routing_class ||'Rout Qty:'||l_routing_qty ||' Planned Loss:'||l_routing_planned_loss);
681 END IF;
682 /* If Routing_class is not null then get its uom from fm_rout_cls table */
683 OPEN Rout_cls_cur(l_routing_class);
684 FETCH Rout_cls_cur INTO l_routing_uom;
685 IF (Rout_cls_cur%NOTFOUND) THEN
686 l_routing_uom := NULL;
687 x_return_status := 'E';
688 FND_MESSAGE.SET_NAME('GMD', 'GMD_TPL_WO_ROUT_UOM');
689 FND_MSG_PUB.Add;
690 END IF;
691 CLOSE Rout_cls_cur;
692 IF (l_debug = 'Y') THEN
693 gmd_debug.put_line(' Routing UOM : '||l_routing_uom);
694 END IF;
695 /* IF routing qty is provided as opposed to the
696 routing_qty stored in the database */
697 IF ((UPPER(Entity_type) = 'ROUTING') AND l_qty > 0) THEN
698 l_routing_qty := l_qty;
699 END IF;
700
701 /* Convert the routing qty from its uom to the routing class UOM */
702 /* Routing UOM needs to be convertible to the routing class UOM */
703 l_routing_qty := INV_CONVERT.inv_um_convert(item_id => 0
704 ,precision => 5
705 ,from_quantity => l_routing_qty
706 ,from_unit => l_item_um
707 ,to_unit => l_routing_uom
708 ,from_name => NULL
709 ,to_name => NULL);
710
711 IF (NVL(l_routing_qty,0) < 0) THEN
712 x_return_status := 'E';
713 FND_MESSAGE.SET_NAME('GMD', 'GMD_TPL_WO_ROUT_UOM');
714 FND_MSG_PUB.Add;
715 END IF;
716
717 OPEN process_loss_cur(l_routing_class,l_routing_qty);
718 FETCH process_loss_cur INTO l_routing_theo_loss;
719 IF (process_loss_cur%NOTFOUND) THEN
720 /* Theo process loss has not been defined. */
721 l_routing_theo_loss := NULL;
722 x_return_status := 'E';
723 FND_MESSAGE.SET_NAME('GMD', 'GMD_TPL_NOT_DEFINED');
724 FND_MSG_PUB.Add;
725 END IF;
726 CLOSE process_loss_cur;
727 IF (l_debug = 'Y') THEN
728 gmd_debug.put_line(' Routing Theoretical Loss:'||l_routing_theo_loss);
729 END IF;
730
731 /* Calculation for recipe theoretical and planned process losses */
732 IF (Upper(Entity_type) <> 'ROUTING') THEN
733 IF ((l_routing_theo_loss IS NOT NULL) AND (l_routing_theo_loss <> 0)) THEN
734 l_routing_prorate_factor := (l_routing_planned_loss/l_routing_theo_loss);
735 ELSE
736 /* under condition when l_routing_theo_loss is 0 or NULL
737 set the proration to 1 */
738 l_routing_prorate_factor := 1;
739 END IF;
740 IF (l_debug = 'Y') THEN
741 gmd_debug.put_line(' Routing pro rate factor :'||l_routing_prorate_factor);
742 END IF;
743 /* Get the total product qty and convert it to routing UOM */
744 /* Are we sure about the total product qty UOM??? */
745 /* Is it the main product UOM ? Or Is it the formula UOM */
746 /* Or is it the formula yield type? */
747 IF ((UPPER(Entity_type) = 'RECIPE') AND l_qty > 0) THEN
748 l_recipe_qty := l_qty;
749 END IF;
750
751 /* Check if the GMD:Yield type UOM can be convertible to the
752 routing class UOM */
753 IF (NVL(l_recipe_qty,0) = 0) THEN
754 OPEN get_total_qty_cur(l_formula_id);
755 FETCH get_total_qty_cur INTO l_recipe_qty, l_uom;
756 CLOSE get_total_qty_cur;
757 IF (l_recipe_qty IS NULL) THEN
758 l_recipe_qty := -20;
759 ELSE
760 l_recipe_qty := INV_CONVERT.inv_um_convert(item_id => 0
761 ,precision => 5
762 ,from_quantity => l_recipe_qty
763 ,from_unit => l_uom
764 ,to_unit => l_routing_uom
765 ,from_name => NULL
766 ,to_name => NULL);
767 END IF;
768 END IF;
769
770 /* If for some reason the yield_uom in formula table is not defined OR
771 the yield UOM is not convertible to routing class uom the
772 l_recipe_qty is -ve value. In such case, check if the each product
773 in this formula can be directly converted into the routing class UOM */
774 IF (l_recipe_qty < 0) THEN
775 Calculate_total_qty(l_formula_id, l_recipe_qty, l_ing_qty, l_routing_uom,
776 l_toq_return_status, x_msg_count, x_msg_data);
777 /* l_recipe_qty is NULL if the products uoms cannot be converted
778 to the routing class UOM */
779 END IF;
780
781 /* Get the theoretical/planned process loss for this recipe qty
782 from gmd_process_loss table Cannot calculate recipe theo/planned
783 loss if the recipe_qty is NULL or < 0 */
784 If (l_recipe_qty >= 0) THEN
785 open process_loss_cur(l_routing_class,l_recipe_qty);
786 fetch process_loss_cur INTO l_recipe_theo_loss;
787 IF (process_loss_cur%NOTFOUND) THEN
788 x_return_status := 'E';
789 FND_MESSAGE.SET_NAME('GMD', 'GMD_TPL_NOT_DEFINED');
790 FND_MSG_PUB.Add;
791 l_recipe_theo_loss := NULL;
792 l_recipe_pp_loss := NULL;
793 ELSE
794 /* After applying the routing loss proration */
795 IF l_recipe_pp_loss IS NULL THEN
796 l_recipe_pp_loss := l_recipe_theo_loss*l_routing_prorate_factor;
797 END IF;
798 END IF;
799 close process_loss_cur;
800 END IF; /* recipe qty is < 0 */
801 IF (l_debug = 'Y') THEN
802 gmd_debug.put_line(' Recipe Theoretical Loss1:'||l_recipe_theo_loss);
803 END IF;
804 /* Bug 1683702 - Thomas Daniel */
805 /* Calculation for validity theoretical and planned process losses* */
806 IF (Upper(Entity_type) <> 'RECIPE') THEN
807 /* We have to calculate the prorate factor which should be applied
808 on the theoretical loss calculated for the validity rule to
809 evaluate the planned process loss of the validity */
810 IF (NVL(l_recipe_theo_loss, 0) > 0) THEN
811 /* The planned process loss which should be used will also depend
812 on any process loss entered at the validity rule orgn level */
813 IF l_validity_orgn IS NOT NULL THEN
814 IF (l_debug = 'Y') THEN
815 gmd_debug.put_line(' Checking process loss for orgn:'||l_validity_orgn);
816 END IF;
817 OPEN Get_recipe_orgn_loss (l_recipe_id, l_validity_orgn);
818 FETCH Get_recipe_orgn_loss INTO l_orgn_process_loss;
819 CLOSE Get_recipe_orgn_loss;
820 IF l_orgn_process_loss IS NOT NULL THEN
821 l_recipe_pp_loss := l_orgn_process_loss;
822 END IF;
823 END IF; /* IF l_validity_orgn IS NOT NULL */
824 IF l_recipe_pp_loss IS NOT NULL THEN
825 l_recipe_prorate_factor := (l_recipe_pp_loss/l_recipe_theo_loss);
826 ELSE
827 l_recipe_prorate_factor := l_routing_prorate_factor;
828 END IF;
829 ELSE
830 /* under condition when l_recipe_theo_loss is 0 or NULL
831 set the proration to 1 */
832 l_recipe_prorate_factor := l_routing_prorate_factor;
833 END IF;
834 IF (l_debug = 'Y') THEN
835 gmd_debug.put_line(' Recipe prorate factor:'||l_recipe_prorate_factor);
836 END IF;
837 /* Lets check if we have to use the qty passed in or use the qty
838 in the database */
839 IF ((UPPER(Entity_type) = 'VALIDITY') AND l_qty > 0) THEN
840 l_validity_qty := l_qty;
841 END IF;
842
843 /* Lets check if we have to use the uom passed in or use the std um
844 in the database */
845 IF ((UPPER(Entity_type) = 'VALIDITY') AND process_loss.UOM IS NOT NULL) THEN
846 l_validity_um := process_loss.UOM;
847 END IF;
848
849 /* Lets get the scale factor between the validity std qty and
850 the formula product qty */
851 gmd_validity_rules.get_validity_scale_factor
852 ( p_recipe_id => l_recipe_id
853 ,p_item_id => l_item_id
854 ,p_std_qty => l_validity_qty
855 ,p_std_um => l_validity_um
856 ,x_scale_factor => l_validity_scale_factor
857 ,x_return_status => x_return_status);
858 IF (l_debug = 'Y') THEN
859 gmd_debug.put_line(' Scale factor :'||l_validity_scale_factor);
860 END IF;
861 /* Get the total product qty and convert it to routing UOM */
862 Calculate_total_qty(formula_id => l_formula_id,
863 x_product_qty => l_validity_qty,
864 x_ingredient_qty => l_ing_qty,
865 x_uom => l_routing_uom,
866 x_return_status => l_toq_return_status,
867 x_msg_count => x_msg_count,
868 x_msg_data => x_msg_data,
869 p_scale_factor => l_validity_scale_factor,
870 p_primaries => 'OUTPUTS');
871 IF (l_debug = 'Y') THEN
872 gmd_debug.put_line(' total qty:'||l_validity_qty
873 ||' rout class:'||l_routing_class
874 ||' recipe prorate:'||l_recipe_prorate_factor);
875 END IF;
876 /* Get the theoretical/planned process loss for this recipe qty */
877 /* from gmd_process_loss table Cannot calculate recipe theo/planned
878 loss if the recipe_qty is NULL or < 0 */
879 If (l_validity_qty >= 0) THEN
880 open process_loss_cur(l_routing_class,l_validity_qty);
881 fetch process_loss_cur INTO x_recipe_theo_loss;
882 IF (process_loss_cur%NOTFOUND) THEN
883 x_return_status := 'E';
884 FND_MESSAGE.SET_NAME('GMD', 'GMD_TPL_NOT_DEFINED');
885 FND_MSG_PUB.Add;
886 x_recipe_theo_loss := NULL;
887 x_process_loss := NULL;
888 ELSE
889 /* After applying the routing loss proration */
890 x_process_loss := x_recipe_theo_loss * l_recipe_prorate_factor;
891 END IF;
892 CLOSE process_loss_cur;
893 ELSE
894 /* May be recipe qty caould not calculated */
895 x_process_loss := NULL;
896 END IF; /* validity qty is < 0 */
897 IF (l_debug = 'Y') THEN
898 gmd_debug.put_line(' Theoretical:'||x_recipe_theo_loss||' Planned:'||x_process_loss);
899 END IF;
900 ELSE
901 /* entity type is recipe */
902 x_recipe_theo_loss := l_recipe_theo_loss;
903 x_process_loss := l_recipe_pp_loss;
904 END IF; /* Condition ends for validity losses calculation */
905 END IF; /* Condition ends for recipe losses calculation */
906
907
908 /* *** CALCULATION FOR BATCH PLANNED PROCESS LOSSES *************** */
909 /* If batch has called this routine then we also need to */
910 /* apply the recipe prorate factor */
911 /* In case of batch we expect the qty value to be provide */
912
913 IF (Entity_type NOT IN ('ROUTING','RECIPE','VALIDITY')) THEN
914 IF ((l_qty > 0) AND (x_process_loss IS NOT NULL)) THEN
915 If ((x_process_loss IS NOT NULL) OR (x_process_loss <> 0)) THEN
916 l_recipe_prorate_factor := (l_recipe_pp_loss/x_process_loss);
917 Else
918 l_recipe_prorate_factor := 1;
919 END IF;
920
921 OPEN process_loss_cur(l_routing_class,l_qty);
922 FETCH process_loss_cur INTO x_process_loss;
923 IF (process_loss_cur%NOTFOUND) THEN
924 /* Theo process loss has not been defined. */
925 x_process_loss := NULL;
926 ELSE
927 /* After applying the recipe loss proration */
928 x_process_loss := x_process_loss*l_recipe_prorate_factor;
929 END IF;
930 CLOSE process_loss_cur;
931 END IF; /* if qty was < 0 planned pp loss for batch is not calculated */
932 END IF; /* Condition ends for batch process loss calculation */
933
934 END IF; /* if routing id was null */
935
936 /* Finally we need to ensure that the recipe and batch planned losses
937 values are always returned */
938 IF ((x_process_loss IS NULL) AND
939 (upper(Entity_type) = 'VALIDITY' OR upper(Entity_type) = 'BATCH')) THEN
940 x_process_loss := l_recipe_pp_loss;
941 ELSIF ((x_process_loss IS NULL) AND (Upper(Entity_type) = 'ROUTING')) THEN
942 x_process_loss := l_routing_theo_loss;
943 END IF;
944
945 /* return the message count and data */
946 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
947 P_data => x_msg_data);
948
949 EXCEPTION
950 WHEN FND_API.g_exc_error THEN
951 NULL;
952 When Others then
953 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
954 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
955 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
956 FND_MSG_PUB.ADD;
957 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
958 P_data => x_msg_data);
959 END Calculate_Process_loss;
960
961
962 /* ====================================================================== */
963 /* Procedure: */
964 /* Calculate_Total_Qty */
965 /* */
966 /* DESCRIPTION: */
967 /* This PL/SQL procedure calculates the process loss value */
968 /* Return E if no status code exists */
969 /* Return S if status code is found */
970 /* */
971 /* Procedure returns the total product and ingredient qty */
972 /* The uom is that of FM_YIELD_TYPE UOM */
973 /* */
974 /* bug13909243 is fixed by passing the precision value as 9. */
975 /* ====================================================================== */
976 PROCEDURE Calculate_Total_Qty
977 ( formula_id IN GMD_RECIPES.Formula_id%TYPE ,
978 x_product_qty OUT NOCOPY NUMBER ,
979 x_ingredient_qty OUT NOCOPY NUMBER ,
980 x_uom IN OUT NOCOPY VARCHAR2 ,
981 x_return_status OUT NOCOPY VARCHAR2 ,
982 x_msg_count OUT NOCOPY NUMBER ,
983 x_msg_data OUT NOCOPY VARCHAR2 ,
984 p_scale_factor IN NUMBER ,
985 p_primaries IN VARCHAR2
986 ) IS
987
988 l_temp_qty NUMBER := 0;
989 l_um_type mtl_units_of_measure.uom_class%TYPE;
990 /*Bug 5667857 - Change the column from unit_of_measure to uom_code */
991 CURSOR get_sy_std_um(pUm_type mtl_units_of_measure.uom_class%TYPE) IS
992 SELECT uom_code
993 FROM mtl_units_of_measure
994 WHERE uom_class = pUm_type
995 AND base_uom_flag = 'Y';
996
997 CURSOR prod_um_cur(vFormula_id NUMBER) IS
998 SELECT detail_uom
999 FROM fm_matl_dtl
1000 WHERE line_no = 1
1001 AND line_type = 1
1002 AND formula_id = vFormula_id;
1003
1004 /* Bug 1683702 - Thomas Daniel */
1005 l_count NUMBER(5) DEFAULT 0;
1006 l_scale_tab GMD_COMMON_SCALE.scale_tab;
1007 l_material_tab GMD_COMMON_SCALE.scale_tab;
1008
1009 -- NPD Conv. Use inventory_iem_id and detail_uom instead of item_id and item_um
1010 CURSOR Get_formula_lines (vFormula_id NUMBER) IS
1011 SELECT line_no, line_type, inventory_item_id, qty, detail_uom, scale_type,
1012 contribute_yield_ind, scale_multiple, scale_rounding_variance,
1013 rounding_direction
1014 FROM fm_matl_dtl
1015 WHERE formula_id = vFormula_id
1016 ORDER BY line_type;
1017
1018 -- NPD Conv.
1019 l_orgn_id NUMBER;
1020 l_return_status VARCHAR2(10);
1021
1022 CURSOR get_formula_owner_orgn_id(vformula_id NUMBER) IS
1023 SELECT owner_organization_id
1024 FROM fm_form_mst_b
1025 WHERE formula_id = vformula_id;
1026
1027 BEGIN
1028
1029 /* Initialize the input and output qtys */
1030 x_product_qty := 0;
1031 x_ingredient_qty := 0;
1032 x_return_status := 'S';
1033
1034 -- NPD Conv. Get formula owner orgn. id
1035 OPEN get_formula_owner_orgn_id(formula_id);
1036 FETCH get_formula_owner_orgn_id INTO l_orgn_id;
1037 CLOSE get_formula_owner_orgn_id;
1038
1039 /* if the x_uom value is not paased as input parameter */
1040 /* then we use the GMD:Yield type std um as the formula uom */
1041 IF (x_uom IS NULL) THEN
1042 -- l_um_type := fnd_profile.value('FM_YIELD_TYPE');
1043
1044 -- NPD Conv. Use the new detch proc. to get FM_YIELD_TYPE
1045 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => l_orgn_id ,
1046 P_parm_name => 'FM_YIELD_TYPE' ,
1047 P_parm_value => l_um_type ,
1048 X_return_status => l_return_status );
1049
1050 IF (l_um_type IS NOT NULL) then
1051 OPEN get_sy_std_um(l_um_type);
1052 FETCH get_sy_std_um INTO x_uom;
1053 IF get_sy_std_um%NOTFOUND then
1054 x_uom := NULL;
1055 End if;
1056 CLOSE get_sy_std_um;
1057 END IF;
1058 END IF;
1059
1060 /* If the GMD:Yield type is not defined use th emain product UOM */
1061 IF (x_uom IS NULL) THEN /* get the main prod um */
1062 /* Determine the main product UOM */
1063 OPEN prod_um_cur(formula_id);
1064 FETCH prod_um_cur INTO x_uom;
1065 IF prod_um_cur%NOTFOUND then
1066 x_uom := NULL;
1067 End if;
1068 CLOSE prod_um_cur;
1069 END IF;
1070
1071 /* If the x_uom is yet NULL then its an error */
1072 IF (x_uom IS NULL) Then
1073 FND_MESSAGE.SET_NAME('GMD', 'FM_SCALE_BAD_YIELD_TYPE');
1074 FND_MSG_PUB.Add;
1075 x_return_status := 'E';
1076 END IF;
1077
1078 /* Bug 1683702 - Thomas Daniel */
1079 FOR l_rec IN Get_formula_lines (formula_id) LOOP
1080 l_count := l_count + 1;
1081 l_scale_tab(l_count).line_no := l_rec.line_no;
1082 l_scale_tab(l_count).line_type := l_rec.line_type;
1083 l_scale_tab(l_count).inventory_item_id := l_rec.inventory_item_id; -- NPD Conv.
1084 l_scale_tab(l_count).qty := l_rec.qty;
1085 l_scale_tab(l_count).detail_uom := l_rec.detail_uom; -- NPD Conv.
1086 l_scale_tab(l_count).scale_type := l_rec.scale_type;
1087 l_scale_tab(l_count).contribute_yield_ind := l_rec.contribute_yield_ind;
1088 l_scale_tab(l_count).scale_multiple := l_rec.scale_multiple;
1089 l_scale_tab(l_count).scale_rounding_variance := l_rec.scale_rounding_variance;
1090 l_scale_tab(l_count).rounding_direction := l_rec.rounding_direction;
1091 END LOOP; /* FOR l_rec IN Get_formula_lines (l_formula_id) */
1092
1093 IF NVL(p_scale_factor, 1) = 1 THEN
1094 l_material_tab := l_scale_tab;
1095 ELSE
1096 -- NPD Conv. Pass orgn_id to the scale proc.
1097 GMD_COMMON_SCALE.scale (p_scale_tab => l_scale_tab
1098 ,p_orgn_id => l_orgn_id
1099 ,p_scale_factor => p_scale_factor
1100 ,p_primaries => p_primaries
1101 ,x_scale_tab => l_material_tab
1102 ,x_return_status => x_return_status);
1103 IF x_return_status <> FND_API.g_ret_sts_success THEN
1104 RAISE FND_API.g_exc_error;
1105 END IF;
1106 END IF; /* IF NVL(p_scale_factor, 1) = 1 */
1107
1108 /* Calculate the ingredient total quantities */
1109 FOR i IN 1..l_material_tab.COUNT LOOP
1110 IF l_material_tab(i).line_type = -1 AND
1111 /*Bug 2880618 - Thomas Daniel */
1112 /*Need to compute only for ingredients contributing to yield */
1113 l_material_tab(i).contribute_yield_ind = 'Y' THEN
1114 -- NPD Conv.
1115 l_temp_qty := INV_CONVERT.inv_um_convert( item_id => l_material_tab(i).inventory_item_id
1116 ,precision => 9
1117 ,from_quantity => l_material_tab(i).qty
1118 ,from_unit => l_material_tab(i).detail_uom
1119 ,to_unit => x_uom
1120 ,from_name => NULL
1121 ,to_name => NULL);
1122 IF l_temp_qty < 0 THEN
1123 x_ingredient_qty := NULL;
1124 x_return_status := 'Q';
1125 FND_MESSAGE.SET_NAME('GMD', 'GMD_CANNOT_CALC_TOQ');
1126 FND_MSG_PUB.Add;
1127 Exit;
1128 ELSE
1129 X_ingredient_qty := X_ingredient_qty + l_temp_qty;
1130 END IF;
1131 END IF;
1132 END LOOP;
1133
1134 /* Now let us calculate the product total quantities */
1135 FOR i IN 1..l_material_tab.COUNT LOOP
1136 IF l_material_tab(i).line_type IN (1,2) THEN
1137 -- NPD Conv.
1138 l_temp_qty := INV_CONVERT.inv_um_convert( item_id => l_material_tab(i).inventory_item_id
1139 ,precision => 9
1140 ,from_quantity => l_material_tab(i).qty
1141 ,from_unit => l_material_tab(i).detail_uom
1142 ,to_unit => x_uom
1143 ,from_name => NULL
1144 ,to_name => NULL);
1145 IF l_temp_qty < 0 THEN
1146 x_product_qty := NULL;
1147 x_return_status := 'Q';
1148 FND_MESSAGE.SET_NAME('GMD', 'GMD_CANNOT_CALC_TOQ');
1149 FND_MSG_PUB.Add;
1150 Exit;
1151 ELSE
1152 x_product_qty := x_product_qty + l_temp_qty;
1153 END IF;
1154 END IF;
1155 END LOOP;
1156
1157 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
1158 P_data => x_msg_data);
1159
1160 EXCEPTION
1161 WHEN FND_API.g_exc_error THEN
1162 x_return_status := 'Q';
1163 WHEN Others THEN
1164 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1165 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1166 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1167 FND_MSG_PUB.ADD;
1168 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
1169 P_data => x_msg_data);
1170
1171 END Calculate_Total_Qty;
1172
1173 /* ==================================================================== */
1174 /* Function */
1175 /* Get_Routing_Scale_Factor */
1176 /* */
1177 /* Description - */
1178 /* Routing qtys are scaled to a value proportional to the Recipe */
1179 /* Total Output Qty. This scale factor is the routing scale factor. */
1180 /* Return value : x_Routing_Scale_Factor NUMBER */
1181 /* */
1182 /* */
1183 /* */
1184 /* ==================================================================== */
1185 FUNCTION Get_Routing_Scale_Factor(vRecipe_id IN NUMBER,
1186 x_return_status OUT NOCOPY VARCHAR2,
1187 vFormula_Id IN NUMBER,
1188 vRouting_Id IN NUMBER)
1189 RETURN NUMBER IS
1190
1191 l_recipe_qty NUMBER ;
1192 x_Routing_Scale_factor NUMBER := 1 ;
1193
1194 CURSOR Cur_get_recipe IS
1195 SELECT formula_id, routing_id, calculate_step_quantity
1196 FROM gmd_recipes_b
1197 WHERE recipe_id = vRecipe_Id;
1198
1199 Cursor get_routrecipe_qty(pFormula_Id NUMBER, pRouting_Id NUMBER) IS
1200 Select rout.routing_qty rout_qty,
1201 form.total_output_qty recipe_qty,
1202 rout.routing_uom rout_uom,
1203 form.formula_id formula_id,
1204 form.yield_uom yield_typ_uom
1205 from fm_form_mst_b form,
1206 gmd_routings_b rout
1207 where form.formula_id = pformula_id and
1208 rout.routing_id = prouting_id ;
1209
1210 l_formula_id NUMBER;
1211 l_routing_id NUMBER;
1212 l_calculate_step_qty NUMBER(5);
1213 l_product_qty NUMBER;
1214 l_ingredient_qty NUMBER;
1215 l_uom mtl_units_of_measure.unit_of_measure%TYPE;
1216 l_return_status VARCHAR2(1);
1217 l_msg_count NUMBER;
1218 l_msg_data VARCHAR2(2000);
1219 BEGIN
1220 X_return_status := FND_API.g_ret_sts_success;
1221 IF vRecipe_Id IS NOT NULL THEN
1222 OPEN Cur_get_recipe;
1223 FETCH Cur_get_recipe INTO l_formula_id, l_routing_id, l_calculate_step_qty;
1224 CLOSE Cur_get_recipe;
1225 ELSE
1226 l_formula_id := vFormula_Id;
1227 l_routing_id := vRouting_Id;
1228 l_calculate_step_qty := 0;
1229 END IF;
1230 --Modified the following code for bug 13112823 by calling Calculate_Total_Qty first
1231 --if product qty is not > 0 then call INV_CONVERT.inv_um_convert.
1232 FOR rout_rec IN get_routrecipe_qty(l_formula_Id, l_routing_Id) LOOP
1233 IF(l_calculate_step_qty <> 1) THEN
1234 IF (rout_rec.rout_uom <> rout_rec.yield_typ_uom) THEN
1235 l_uom := rout_rec.rout_uom;
1236 Calculate_Total_Qty (Formula_Id => rout_rec.formula_id
1237 ,x_product_qty => l_product_qty
1238 ,x_ingredient_qty => l_ingredient_qty
1239 ,x_uom => l_uom
1240 ,x_return_status => l_return_status
1241 ,x_msg_count => l_msg_count
1242 ,x_msg_data => l_msg_data);
1243 IF l_product_qty > 0 THEN
1244 l_recipe_qty := l_product_qty;
1245 ELSE
1246 l_recipe_qty := INV_CONVERT.inv_um_convert(item_id => 0
1247 ,precision => 9
1248 ,from_quantity => rout_rec.recipe_qty
1249 ,from_unit => rout_rec.yield_typ_uom
1250 ,to_unit => rout_rec.rout_uom
1251 ,from_name => NULL
1252 ,to_name => NULL);
1253 IF l_recipe_qty < 0 THEN
1254 /*Bug 2722961 - Thomas Daniel */
1255 /*Changed the checking from return_status to l_product_qty */
1256 /*as the calculate_total_qty routine was passing back the */
1257 /*return status as 'Q' if the ingredient conversions were */
1258 /*not setup, though the product conversions have been setup */
1259 FND_MSG_PUB.INITIALIZE;
1260 FND_MESSAGE.SET_NAME ('GMD', 'GMD_ERR_CALC_ROUT_FACT');
1261 FND_MSG_PUB.add;
1262 x_return_status := 'W';
1263 RETURN 1;
1264 END IF;
1265 END IF;
1266 ELSE
1267 l_recipe_qty := rout_rec.recipe_qty;
1268 END IF;
1269 x_Routing_scale_factor := l_recipe_qty / rout_rec.rout_qty;
1270 ELSE
1271 /* ASQC flag is ON it implies that the recipe step qty have the rout qty
1272 factor is already incorporated */
1273 x_Routing_scale_factor := 1;
1274 END IF;
1275 END LOOP;
1276
1277 return x_Routing_Scale_factor;
1278
1279 /*commented the following code for bug 13112823
1280 IF (rout_rec.rout_uom <> rout_rec.yield_typ_uom) THEN
1281 l_recipe_qty := INV_CONVERT.inv_um_convert(item_id => 0
1282 ,precision => 5
1283 ,from_quantity => rout_rec.recipe_qty
1284 ,from_unit => rout_rec.yield_typ_uom
1285 ,to_unit => rout_rec.rout_uom
1286 ,from_name => NULL
1287 ,to_name => NULL);
1288
1289 This implies that the recipe qty uom and the routing qty uom are
1290 not of the same uom class
1291 so lets recalculate the recipe qty based on the routing uom
1292 IF l_recipe_qty < 0 THEN
1293 l_uom := rout_rec.rout_uom;
1294 Calculate_Total_Qty (Formula_Id => rout_rec.formula_id
1295 ,x_product_qty => l_product_qty
1296 ,x_ingredient_qty => l_ingredient_qty
1297 ,x_uom => l_uom
1298 ,x_return_status => l_return_status
1299 ,x_msg_count => l_msg_count
1300 ,x_msg_data => l_msg_data);
1301
1302 Bug 2722961 - Thomas Daniel
1303 Changed the checking from return_status to l_product_qty
1304 as the calculate_total_qty routine was passing back the
1305 return status as 'Q' if the ingredient conversions were
1306 not setup, though the product conversions have been setup
1307 IF l_product_qty > 0 THEN
1308 l_recipe_qty := l_product_qty;
1309 ELSE
1310 FND_MSG_PUB.INITIALIZE;
1311 FND_MESSAGE.SET_NAME ('GMD', 'GMD_ERR_CALC_ROUT_FACT');
1312 FND_MSG_PUB.add;
1313 x_return_status := 'W';
1314 RETURN 1;
1315 END IF;
1316 END IF;
1317 ELSE
1318 l_recipe_qty := rout_rec.recipe_qty;
1319 END IF;
1320 x_Routing_scale_factor := l_recipe_qty / rout_rec.rout_qty;
1321 ELSE
1322 ASQC flag is ON it implies that the recipe step qty have the rout qty
1323 factor is already incorporated
1324 x_Routing_scale_factor := 1;
1325
1326 END IF;
1327 END LOOP;
1328
1329 return x_Routing_Scale_factor;*/
1330
1331 END Get_Routing_Scale_Factor;
1332
1333
1334 /* =============================================================== */
1335 /* Procedure: */
1336 /* Calculate_Charges */
1337 /* */
1338 /* DESCRIPTION: */
1339 /* */
1340 /* Return E if no status code exists */
1341 /* Return S if status code is found */
1342 /* */
1343 /* Procedure returns the all Charges for a routing step */
1344 /* History : */
1345 /* Shyam 03/29/2002 Bug # 2284643: Added logic to scale the */
1346 /* routing step qty if the total routing step */
1347 /* is not equal to the Recipe qty */
1348 /* Shyam 04/20/2002 Create a new organization based capacity */
1349 /* cursor : Bug # 2272885 */
1350 /* =============================================================== */
1351
1352 PROCEDURE Calculate_Charges
1353 ( Batch_id IN NUMBER ,
1354 Recipe_id IN NUMBER ,
1355 Routing_id IN NUMBER ,
1356 VR_qty IN NUMBER ,
1357 Tolerance IN NUMBER ,
1358 Orgn_id IN NUMBER ,
1359 x_charge_tbl OUT NOCOPY charge_tbl ,
1360 x_return_status OUT NOCOPY VARCHAR2
1361 ) IS
1362
1363 /* Defining all variables */
1364 l_row NUMBER := 0 ;
1365 l_rout_scale_factor NUMBER := 1 ;
1366 l_step_tbl GMD_AUTO_STEP_CALC.step_rec_tbl;
1367 l_return_status VARCHAR2(1);
1368
1369 /* get capacities for resources that belong any orgn (generic) */
1370 CURSOR Step_qty_Cur(vRouting_id NUMBER) IS
1371 SELECT dtl.routingStep_id ,
1372 dtl.step_qty qty ,
1373 opr.process_qty_uom step_um
1374 FROM fm_rout_dtl dtl ,
1375 gmd_operations_b opr
1376 WHERE dtl.oprn_id = opr.oprn_id AND
1377 dtl.routing_id = vRouting_id
1378 ORDER BY dtl.routingStep_id;
1379
1380 CURSOR Get_Recipe_Step_Details(vRecipe_id NUMBER, vRoutingStep_id NUMBER) IS
1381 SELECT step_qty, mass_qty, mass_std_uom, volume_qty, volume_std_uom
1382 FROM gmd_recipe_routing_steps
1383 WHERE recipe_id = vRecipe_id AND
1384 routingstep_id = vRoutingStep_id;
1385
1386 BEGIN
1387 IF (l_debug = 'Y') THEN
1388 gmd_debug.log_initialize('CalcCharges');
1389 END IF;
1390 /* Initialize variables */
1391 /* Get the Routing Scale Factor */
1392 IF (l_debug = 'Y') THEN
1393 gmd_debug.put_line('In calc_charges proc initializing the variables ');
1394 END IF;
1395 x_return_status := FND_API.g_ret_sts_success;
1396
1397 /* Get the Routing Scale Factor */
1398 IF (l_debug = 'Y') THEN
1399 gmd_debug.put_line('In calc_charges proc - before calling routing scale fact '
1400 ||Recipe_id);
1401 END IF;
1402 l_rout_scale_factor := Get_Routing_Scale_Factor(vRecipe_Id => Recipe_id,
1403 x_return_status => l_return_status);
1404
1405
1406 IF (l_debug = 'Y') THEN
1407 gmd_debug.put_line('In calc_charges proc - after calling routing scale fact '
1408 ||l_rout_scale_factor||' '||l_return_status);
1409 END IF;
1410
1411 /* Step1: Get all step details based on the routing_id */
1412 FOR Step_qty_rec IN Step_qty_cur(Routing_id) LOOP
1413
1414 /* Get the routing step value */
1415 l_row := l_row + 1;
1416 l_step_tbl(l_row).step_id := Step_qty_rec.routingstep_id;
1417 l_step_tbl(l_row).step_qty := Step_qty_rec.qty * NVL(l_rout_scale_factor, 1);
1418 l_step_tbl(l_row).step_qty_uom := Step_qty_rec.step_um;
1419
1420 IF (l_debug = 'Y') THEN
1421 gmd_debug.put_line('In CalcCharges Proc - the step qty and its uom is '
1422 ||l_step_tbl(l_row).step_qty||' '||l_step_tbl(l_row).step_qty_uom);
1423 END IF;
1424
1425 /* At Recipe_Routing_Step_level, we store the step qty in three uom */
1426 /* 1) Step qty in its operating uom */
1427 /* 2) As, mass qty in mass reference uom */
1428 /* 3) As, volume qty in volume reference uom */
1429
1430 /* Check if the step qty has been overridden at recipe level */
1431 /* Table gmd_recipe_routing_steps stores step qtys that are overridden */
1432
1433 /* This cursor should return only one row */
1434 FOR Get_Recipe_Step_rec IN
1435 Get_Recipe_Step_Details(Recipe_id, Step_qty_rec.routingStep_id) LOOP
1436
1437 /* Get the recipe routing step qty */
1438 /* Use the overridden step qty */
1439 l_step_tbl(l_row).step_qty := Get_Recipe_Step_rec.step_qty;
1440 l_step_tbl(l_row).step_mass_qty := Get_Recipe_Step_rec.mass_qty;
1441 l_step_tbl(l_row).step_mass_uom := Get_Recipe_Step_rec.mass_std_uom;
1442 l_step_tbl(l_row).step_vol_qty := Get_Recipe_Step_rec.volume_qty;
1443 l_step_tbl(l_row).step_vol_uom := Get_Recipe_Step_rec.volume_std_uom;
1444
1445 IF (l_debug = 'Y') THEN
1446 gmd_debug.put_line('In CalcCharges Proc - the override step qty and its uom is '
1447 ||l_step_tbl(l_row).step_qty||' '||l_step_tbl(l_row).step_qty_uom);
1448 END IF;
1449
1450 END LOOP; /* End loop when routing step details exists at recipe level */
1451
1452 END LOOP;
1453
1454 /* We have the step quantities now lets calculate the charges */
1455 IF l_step_tbl.COUNT > 0 THEN
1456 GMD_COMMON_VAL.calculate_step_charges (p_recipe_id => Recipe_id
1457 ,p_tolerance => tolerance
1458 ,p_orgn_id => Orgn_id
1459 ,p_step_tbl => l_step_tbl
1460 ,x_charge_tbl => x_charge_tbl
1461 ,x_return_status => x_return_status);
1462 IF (l_debug = 'Y') THEN
1463 gmd_debug.put_line('In CalcCharges Proc - after calling Calc_step_chr '
1464 ||x_return_status);
1465 END IF;
1466 END IF; /* If their are rows in the step table */
1467
1468 END Calculate_Charges;
1469
1470
1471 /* =============================================================== */
1472 /* Procedure: */
1473 /* Calculate_Step_Charges */
1474 /* */
1475 /* DESCRIPTION: */
1476 /* */
1477 /* Return E if no status code exists */
1478 /* Return S if status code is found */
1479 /* */
1480 /* Procedure returns the all Charges for a routing step */
1481 /* History : */
1482 /* Thomas 07/23/2002 Bug # 1683702: Added this new procedure */
1483 /* To calculate the charges off the step */
1484 /* quantities passed in */
1485 /* RajaSekhar 02/04/2003 BUG#2365583 Added code to represent */
1486 /* whether the number of charges is defaulted */
1487 /* or calculated properly. */
1488 /* Kalyani 02/06/2006 Bug#5258672 Store max capacity in res UOM */
1489 /* =============================================================== */
1490
1491 PROCEDURE Calculate_Step_Charges
1492 ( P_recipe_id IN NUMBER ,
1493 P_tolerance IN NUMBER ,
1494 P_orgn_id IN NUMBER ,
1495 P_step_tbl IN GMD_AUTO_STEP_CALC.step_rec_tbl,
1496 x_charge_tbl OUT NOCOPY charge_tbl ,
1497 x_return_status OUT NOCOPY VARCHAR2
1498 ) IS
1499
1500 /* Defining all variables */
1501 l_charge INTEGER := 1 ;
1502 l_rough_charge NUMBER ;
1503 l_step_qty_uom VARCHAR2(4) ;
1504 l_max_capacity NUMBER ;
1505 l_max_capacity_old NUMBER ;
1506 l_capacity_uom VARCHAR2(4) ;
1507 l_row NUMBER := 1 ;
1508 m_counter NUMBER := 1 ;
1509 l_step_qty NUMBER ;
1510 l_def_charge VARCHAR2(1) ; --BUG#2365583 RajaSekhar
1511
1512 CURSOR Capacity_cur(vRecipe_id NUMBER, vRoutingstep_id NUMBER,
1513 vOrgn_id NUMBER) IS
1514 /*Bug 3679608 - Thomas Daniel */
1515 /*Modified the select statement to consider the generic resource only */
1516 /*if there are no plant specific overrides */
1517 SELECT MIN( NVL(orgnres.max_capacity, crres.max_capacity) ) max_cap,
1518 crres.capacity_um capacity_um
1519 FROM (SELECT resources, max_capacity, capacity_um, capacity_constraint
1520 FROM cr_rsrc_mst_b m
1521 WHERE capacity_constraint = 1
1522 AND NOT EXISTS (SELECT 1
1523 FROM cr_rsrc_dtl d
1524 WHERE d.organization_id = vOrgn_id
1525 AND d.resources = m.resources)
1526 UNION
1527 SELECT resources, max_capacity, capacity_um, capacity_constraint
1528 FROM cr_rsrc_dtl
1529 WHERE organization_id = vOrgn_id
1530 AND capacity_constraint = 1 ) crres ,
1531 (SELECT max_capacity, resources, routingstep_id
1532 FROM gmd_recipe_orgn_resources
1533 WHERE recipe_id = vRecipe_id
1534 AND organization_id = vOrgn_id) orgnres ,
1535 (SELECT oprn_id, routingStep_id
1536 FROM fm_rout_dtl
1537 WHERE routingstep_id = vRoutingstep_id ) dtl ,
1538 gmd_operation_resources res ,
1539 gmd_operation_activities act ,
1540 gmd_operations_b opr
1541 WHERE crres.resources = res.resources AND
1542 dtl.oprn_id = opr.oprn_id AND
1543 opr.oprn_id = act.oprn_id AND
1544 act.oprn_line_id = res.oprn_line_id AND
1545 (orgnres.routingstep_id IS NULL OR
1546 dtl.routingstep_id = orgnres.routingstep_id ) AND
1547 res.resources = orgnres.resources(+)
1548 GROUP BY crres.capacity_um;
1549
1550 BEGIN
1551 IF (l_debug = 'Y') THEN
1552 gmd_debug.put_line('In CalcCharges Step Proc - ');
1553 END IF;
1554 /* Initialize variables */
1555 x_return_status := FND_API.g_ret_sts_success;
1556 FOR i IN 1..P_step_tbl.COUNT LOOP
1557 /*Bug 3669132 - Thomas Daniel */
1558 /*Initialize default charge variable to identify if the code has entered the following for loop */
1559 l_def_charge := NULL;
1560 /* Let us fetch the min of the max capacities for all the resources belonging */
1561 /* to the current routing step and recipe */
1562 /* Step1: Get all resources details based on the routingStep_id */
1563 FOR Capacity_rec IN Capacity_cur(P_recipe_id,
1564 P_step_tbl(i).step_id,
1565 P_orgn_id) LOOP
1566 l_charge := 1;
1567
1568 /* Get the routing step value */
1569 l_step_qty := P_step_tbl(i).step_qty;
1570 l_step_qty_uom := P_step_tbl(i).step_qty_uom;
1571
1572 l_capacity_uom := Capacity_rec.capacity_um;
1573
1574 /* The min of max_capacity needs to derived for a step */
1575 /* Please remember if the max capacity is -ve */
1576 /* it probably means that the capcity conversion to step qty um was not set */
1577 l_max_capacity := INV_CONVERT.inv_um_convert(item_id => 0
1578 ,precision => 5
1579 ,from_quantity => nvl(Capacity_rec.max_cap,0)
1580 ,from_unit => Capacity_rec.capacity_um
1581 ,to_unit => l_step_qty_uom
1582 ,from_name => NULL
1583 ,to_name => NULL);
1584
1585 IF (l_debug = 'Y') THEN
1586 gmd_debug.put_line('In CalcCharges Step Proc - 1. the max cap and its uom '
1587 ||l_max_capacity||' ' ||l_capacity_uom);
1588 END IF;
1589
1590 IF (l_max_capacity < 0) THEN
1591 /* Set a message in our stack : the capacity conv has not occurred */
1592 x_return_status := 'E';
1593 FND_MESSAGE.SET_NAME('GMD', 'GMD_CANNOT_CONV_CAPACITY_UOM');
1594 FND_MSG_PUB.Add;
1595 END IF; /* when max cap is < 0 */
1596
1597 /* Get the step qty : */
1598 /* If the max capacity is yet -ve its becoz none of the capcity uom where */
1599 /* convertible to the process_um or step operation um */
1600
1601 /* Test if the capacity UOM can be converted into the Mass Qty UOM */
1602 /* It might be possible that the capacity uom is not convertible to the
1603 /* process_um but convertible to the mass_ref_um */
1604 IF (l_max_capacity < 0) THEN
1605 l_step_qty := P_step_tbl(i).step_mass_qty;
1606 l_step_qty_uom := P_step_tbl(i).step_mass_uom;
1607 l_max_capacity := INV_CONVERT.inv_um_convert(item_id => 0
1608 ,precision => 5
1609 ,from_quantity => Capacity_rec.max_cap
1610 ,from_unit => l_capacity_uom
1611 ,to_unit => l_step_qty_uom
1612 ,from_name => NULL
1613 ,to_name => NULL);
1614 IF (l_debug = 'Y') THEN
1615 gmd_debug.put_line('In CalcCharges Step Proc - 2. the max cap and its uom '
1616 ||l_max_capacity||' ' ||l_capacity_uom);
1617 END IF;
1618 END IF; /* when max cap is < 0 */
1619
1620 /* Test if the capacity UOM can be converted into the Volume Qty UOM */
1621 /* It might be possible that the capacity uom is not convertible to the
1622 /* process_um and mass_um but convertible to the volume_ref_um */
1623 IF (l_max_capacity < 0) THEN
1624 l_step_qty := P_step_tbl(i).step_vol_qty;
1625 l_step_qty_uom := P_step_tbl(i).step_vol_uom;
1626 l_max_capacity := INV_CONVERT.inv_um_convert(item_id => 0
1627 ,precision => 5
1628 ,from_quantity => Capacity_rec.max_cap
1629 ,from_unit => l_capacity_uom
1630 ,to_unit => l_step_qty_uom
1631 ,from_name => NULL
1632 ,to_name => NULL);
1633
1634 IF (l_debug = 'Y') THEN
1635 gmd_debug.put_line('In CalcCharges Step Proc - 3. the max cap and its uom '
1636 ||l_max_capacity||' ' ||l_capacity_uom);
1637 END IF;
1638 END IF; /* when max cap is < 0 */
1639
1640 IF (l_debug = 'Y') THEN
1641 gmd_debug.put_line('In CalcCharges Step Proc - 4. the max cap and step_qty '
1642 ||l_max_capacity||' ' ||l_step_qty);
1643 END IF;
1644 /* Calculations after step qty and UOM is found */
1645 IF ((l_step_qty > 0) AND (l_max_capacity > 0)) THEN
1646 /* If the remainder after dividing the step quantity by the
1647 l_max_capacity is greater than tolerance then we round up the
1648 value to the next interger else we truncate to an integer smaller than
1649 the ratio value
1650 */
1651 /* E.g if the step_qty is 110 and the l_max_capacity = 100
1652 the l_rough_charge is 1.1 and the remainder is 10. If the tolerance was
1653 15 (i.e > 10) then the charge would = 1.0 . However, if the tolerance is
1654 5 (i.e < 10) the charge would be = 2.0
1655 */
1656 l_rough_charge := l_step_qty /l_max_capacity;
1657
1658 IF ( MOD(l_step_qty, l_max_capacity) > p_tolerance ) THEN
1659 l_charge := CEIL(l_rough_charge);
1660 ELSE
1661 l_charge := TRUNC(l_rough_charge);
1662 END IF; /* when ratio is greater than tolerance */
1663 l_def_charge :='N'; --BUG#2365583 RajaSekhar
1664 ELSE
1665 l_charge := 1;
1666 l_def_charge :='Y'; --BUG#2365583 RajaSekhar
1667 END IF; /* End condition when step qty > 0 */
1668
1669 /* Resource usage is below capacity. Bug 2183650 */
1670 IF (l_charge <= 0 OR l_charge IS NULL) THEN
1671 l_charge := 1;
1672 END IF; /* when charge is 0 */
1673
1674 /* If Resource capacity is -ve - probably due to non UOM conv set it to NUll */
1675 IF (l_max_capacity < 0) THEN
1676 l_max_capacity := NULL;
1677 END IF;
1678
1679 /* Associate with charges and routing step details with out table */
1680 /* For each RoutingStep_id check for the max charge value */
1681 /* This condition occurs when each set has more than one resource with diff cap UOM */
1682
1683 /* Eliminate duplicate routingStepid rows */
1684 /* For each RoutingStep_id check for the max charge value and remove the
1685 one with lower charge */
1686 /* This condition occurs when each set has more than one resource with diff cap UOM */
1687
1688 IF (m_counter = 1) THEN
1689 x_charge_tbl(l_row).def_charge := l_def_charge; --BUG#2365583 RajaSekhar
1690 x_charge_tbl(l_row).charge := l_charge;
1691 x_charge_tbl(l_row).routingstep_id := P_step_tbl(i).step_id ;
1692 x_charge_tbl(l_row).max_capacity := l_max_capacity;
1693 --x_charge_tbl(l_row).capacity_uom := l_capacity_uom;
1694 /* bug # 2385711 - The uom to be returned is the step qty uom
1695 and not the capacity uom */
1696 x_charge_tbl(l_row).capacity_uom := P_step_tbl(i).step_qty_uom;
1697 m_counter := m_counter + 1;
1698 -- Bug#5258672 Store the capacity value in resource UOM
1699 x_charge_tbl(l_row).Max_Capacity_In_Res_UOM := Capacity_rec.max_cap;
1700 END IF;
1701
1702 IF (x_charge_tbl(l_row).routingstep_id = P_step_tbl(i).Step_id) THEN
1703 IF (x_charge_tbl(l_row).charge < l_charge) THEN
1704 x_charge_tbl(l_row).def_charge := l_def_charge; --BUG#2365583 RajaSekhar
1705 x_charge_tbl(l_row).charge := l_charge;
1706 x_charge_tbl(l_row).routingstep_id := P_step_tbl(i).step_id ;
1707 x_charge_tbl(l_row).max_capacity := l_max_capacity;
1708 --x_charge_tbl(l_row).capacity_uom := l_capacity_uom;
1709 /* bug # 2385711 - The uom to be returned is the step qty uom
1710 and not the capacity uom */
1711 x_charge_tbl(l_row).capacity_uom := P_step_tbl(i).step_qty_uom;
1712 -- Bug#5258672 Store the capacity value in resource UOM
1713 x_charge_tbl(l_row).Max_Capacity_In_Res_UOM := Capacity_rec.max_cap;
1714 END IF; /* when row in previous row is different from current values */
1715 ELSE
1716 l_row := l_row + 1;
1717 x_charge_tbl(l_row).def_charge := l_def_charge; --BUG#2365583 RajaSekhar
1718 x_charge_tbl(l_row).charge := l_charge;
1719 x_charge_tbl(l_row).routingstep_id := P_step_tbl(i).Step_id;
1720 x_charge_tbl(l_row).max_capacity := l_max_capacity;
1721 -- x_charge_tbl(l_row).capacity_uom := l_capacity_uom;
1722 /* bug # 2385711 - The uom to be returned is the step qty uom
1723 and not the capacity uom */
1724 x_charge_tbl(l_row).capacity_uom := P_step_tbl(i).step_qty_uom;
1725 -- Bug#5258672 Store the capacity value in resource UOM
1726 x_charge_tbl(l_row).Max_Capacity_In_Res_UOM := Capacity_rec.max_cap;
1727 END IF; /* When routingStep id are same */
1728
1729 END LOOP; /* End loop for main generic cursor */
1730 /*Bug 3669132 - Thomas Daniel */
1731 /*Added the following code to set the charge as 1 if no capacity constraint */
1732 /*resources are found for this routing step */
1733 IF l_def_charge IS NULL THEN
1734 /*Bug3679608 - Thomas Daniel*/
1735 /*Moved the row incremented up */
1736 IF m_counter <> 1 THEN
1737 l_row := l_row + 1;
1738 ELSE
1739 m_counter := m_counter + 1;
1740 END IF;
1741 x_charge_tbl(l_row).def_charge := 'Y';
1742 x_charge_tbl(l_row).charge := 1;
1743 x_charge_tbl(l_row).routingstep_id := P_step_tbl(i).Step_id;
1744 x_charge_tbl(l_row).max_capacity := NULL;
1745 x_charge_tbl(l_row).capacity_uom := P_step_tbl(i).step_qty_uom;
1746 END IF;
1747 END LOOP; /* FOR i IN 1..Step_tbl.COUNT */
1748
1749 END Calculate_Step_Charges;
1750
1751
1752
1753 FUNCTION UPDATE_ALLOWED(Entity VARCHAR2
1754 ,Entity_id NUMBER
1755 ,Update_Column_Name VARCHAR2 Default Null)
1756 RETURN BOOLEAN IS
1757 l_meaning GMD_STATUS.Meaning%TYPE ;
1758 l_desc GMD_STATUS.Description%TYPE ;
1759 l_return_status VARCHAR2(1);
1760 l_bool BOOLEAN := TRUE;
1761 l_status_code GMD_STATUS.Status_Code%TYPE;
1762 l_delete_mark NUMBER := 0;
1763
1764 l_resp_id NUMBER(15) DEFAULT FND_PROFILE.VALUE('RESP_ID');
1765 l_owner_orgn_id GMD_RECIPES_B.owner_organization_id%TYPE;
1766 l_dummy NUMBER := 0;
1767
1768 Cursor Check_recipe_orgn_access(vresp_id NUMBER, vOwner_orgn_id NUMBER) IS
1769 SELECT 1
1770 FROM org_access_view
1771 WHERE responsibility_id = vresp_id
1772 AND organization_id = vOwner_orgn_id;
1773
1774 BEGIN
1775 IF (l_debug = 'Y') THEN
1776 gmd_debug.put_line('In GMD_COMMON_VAL.UPDATE_ALLOWED - '
1777 ||' Entity = '||Entity||' and Entity id = '
1778 ||Entity_id);
1779 END IF;
1780 IF (Entity = 'FORMULA') THEN
1781 SELECT delete_mark, formula_status
1782 INTO l_delete_mark, l_status_code
1783 FROM fm_form_mst
1784 WHERE formula_id = Entity_id;
1785 ELSIF (Entity = 'RECIPE') THEN
1786
1787 SELECT delete_mark, recipe_status, owner_organization_id
1788 INTO l_delete_mark, l_status_code, l_owner_orgn_id
1789 FROM gmd_recipes_b
1790 WHERE recipe_id = Entity_id;
1791
1792 /* Check if user has access to this Recipe orgn */
1793 OPEN Check_recipe_orgn_access(l_resp_id, l_Owner_orgn_id);
1794 FETCH Check_recipe_orgn_access INTO l_dummy;
1795 IF Check_recipe_orgn_access%NOTFOUND THEN
1796 CLOSE Check_recipe_orgn_access;
1797 Return FALSE;
1798 END IF;
1799 CLOSE Check_recipe_orgn_access;
1800
1801 ELSIF (Entity = 'ROUTING') THEN
1802
1803 SELECT delete_mark, routing_status
1804 INTO l_delete_mark, l_status_code
1805 FROM fm_rout_hdr
1806 WHERE routing_id = Entity_id;
1807 ELSIF (Entity = 'OPERATION') THEN
1808 SELECT delete_mark, operation_status
1809 INTO l_delete_mark, l_status_code
1810 FROM gmd_operations
1811 WHERE oprn_id = Entity_id;
1812 ELSIF (Entity = 'VALIDITY') THEN
1813 SELECT delete_mark, validity_rule_status
1814 INTO l_delete_mark, l_status_code
1815 FROM gmd_recipe_validity_rules
1816 WHERE recipe_validity_rule_id = Entity_id;
1817 END IF;
1818
1819 Get_Status
1820 ( Status_code => l_status_code ,
1821 Meaning => l_meaning ,
1822 Description => l_desc ,
1823 x_return_status => l_return_status
1824 );
1825
1826 -- Added this condition to allow update of end dates for
1827 -- frozen Operations, Routings and Validity Rules
1828 /*--commented for Bug#6861398 start.
1829 IF ((l_status_code between 900 and 999)
1830 AND (Upper(Update_Column_Name) like '%END_DATE%')) THEN
1831 Return TRUE;
1832 ELSIF ((l_status_code between 200 and 299) OR (l_status_code >= 800) OR
1833 (l_status_code between 500 and 599) OR (l_delete_mark = 1)) THEN
1834 Return FALSE;
1835 */ --commented for Bug#6861398 end.
1836 /*Bug#6861398 Added Begin */
1837 IF ((l_status_code between 200 and 299) OR (l_status_code between 800 and 899) OR
1838 (l_status_code between 500 and 599) OR (l_delete_mark = 1)) THEN
1839 Return FALSE;
1840 /*Bug#6861398 Added End */
1841 ELSE
1842 Return TRUE;
1843 END IF;
1844
1845 return false;
1846 EXCEPTION
1847 WHEN NO_DATA_FOUND THEN
1848 RETURN TRUE;
1849 END UPDATE_ALLOWED;
1850
1851
1852 FUNCTION VERSION_CONTROL_STATE(Entity VARCHAR2, Entity_id NUMBER) RETURN VARCHAR2 IS
1853
1854 l_state VARCHAR2(32) := 'N';
1855 l_status VARCHAR2(30);
1856 l_version_enabled VARCHAR2(1) := 'N';
1857
1858 TYPE Status_ref_cur IS REF CURSOR;
1859 Status_cur Status_ref_cur;
1860
1861 CURSOR get_formula_owner_orgn_id(vformula_id NUMBER) IS
1862 SELECT owner_organization_id
1863 FROM fm_form_mst_b
1864 WHERE formula_id = vformula_id;
1865 CURSOR get_recipe_owner_orgn_id(vrecipe_id NUMBER) IS
1866 SELECT owner_organization_id
1867 FROM gmd_recipes_b
1868 WHERE recipe_id = vrecipe_id;
1869 CURSOR get_routing_owner_orgn_id(vrouting_id NUMBER) IS
1870 SELECT owner_organization_id
1871 FROM gmd_routings_b
1872 WHERE routing_id = vrouting_id;
1873 CURSOR get_operation_owner_orgn_id(voprn_id NUMBER) IS
1874 SELECT owner_organization_id
1875 FROM gmd_operations_b
1876 WHERE oprn_id = voprn_id;
1877
1878 CURSOR get_substitution_owner_orgn_id(vsub_id NUMBER) IS
1879 SELECT owner_organization_id
1880 FROM GMD_ITEM_SUBSTITUTION_HDR_B
1881 WHERE SUBSTITUTION_ID = vsub_id;
1882 l_orgn_id NUMBER;
1883 l_return_status VARCHAR2(10);
1884 BEGIN
1885
1886 -- Check for status that allow the version control
1887 -- e.g normally version control is set beyond
1888 -- status = 'Approved for gen use'
1889
1890 IF (Upper(Entity) = 'FORMULA') THEN
1891 OPEN get_formula_owner_orgn_id(entity_id);
1892 FETCH get_formula_owner_orgn_id INTO l_orgn_id;
1893 CLOSE get_formula_owner_orgn_id;
1894 OPEN Status_cur FOR
1895 Select f.formula_status, s.version_enabled
1896 From fm_form_mst f, gmd_status s
1897 Where f.formula_id = Entity_id
1898 And f.formula_status = s.status_code;
1899 FETCH Status_cur INTO l_status, l_version_enabled;
1900 ClOSE Status_cur;
1901
1902 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => l_orgn_id ,
1903 P_parm_name => 'GMD_FORMULA_VERSION_CONTROL',
1904 P_parm_value => l_state ,
1905 X_return_status => l_return_status );
1906
1907 IF ((l_state = 'Y') AND (l_version_enabled = 'Y')) THEN
1908 l_state := 'Y';
1909 ELSIF ((l_state = 'O') AND (l_version_enabled = 'Y')) THEN
1910 l_state := 'O';
1911 ELSE
1912 l_state := 'N';
1913 END IF;
1914
1915 ELSIF (Upper(Entity) = 'RECIPE') THEN
1916 OPEN get_recipe_owner_orgn_id(entity_id);
1917 FETCH get_recipe_owner_orgn_id INTO l_orgn_id;
1918 CLOSE get_recipe_owner_orgn_id;
1919 OPEN Status_cur FOR
1920 Select r.recipe_status, s.version_enabled
1921 From gmd_recipes r, gmd_status s
1922 Where r.recipe_id = Entity_id
1923 And r.recipe_status = s.status_code;
1924 FETCH Status_cur INTO l_status, l_version_enabled;
1925 ClOSE Status_cur;
1926
1927 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => l_orgn_id ,
1928 P_parm_name => 'GMD_RECIPE_VERSION_CONTROL' ,
1929 P_parm_value => l_state ,
1930 X_return_status => l_return_status );
1931
1932 IF ((l_state = 'Y') AND (l_version_enabled = 'Y')) THEN
1933 l_state := 'Y';
1934 ELSIF ((l_state = 'O') AND (l_version_enabled = 'Y')) THEN
1935 l_state := 'O';
1936 ELSE
1937 l_state := 'N';
1938 END IF;
1939
1940 ELSIF (Upper(Entity) = 'ROUTING') THEN
1941 OPEN get_routing_owner_orgn_id(entity_id);
1942 FETCH get_routing_owner_orgn_id INTO l_orgn_id;
1943 CLOSE get_routing_owner_orgn_id;
1944 OPEN Status_cur FOR
1945 Select r.routing_status, s.version_enabled
1946 From fm_rout_hdr r, gmd_status s
1947 Where r.routing_id = Entity_id
1948 And r.routing_status = s.status_code;
1949 FETCH Status_cur INTO l_status, l_version_enabled;
1950 ClOSE Status_cur;
1951
1952 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => l_orgn_id ,
1953 P_parm_name => 'GMD_ROUTING_VERSION_CONTROL' ,
1954 P_parm_value => l_state ,
1955 X_return_status => l_return_status );
1956
1957 IF ((l_state = 'Y') AND (l_version_enabled = 'Y')) THEN
1958 l_state := 'Y';
1959 ELSIF ((l_state = 'O') AND (l_version_enabled = 'Y')) THEN
1960 l_state := 'O';
1961 ELSE
1962 l_state := 'N';
1963 END IF;
1964
1965 ELSIF (Upper(Entity) = 'OPERATION') THEN
1966 OPEN get_operation_owner_orgn_id(entity_id);
1967 FETCH get_operation_owner_orgn_id INTO l_orgn_id;
1968 CLOSE get_operation_owner_orgn_id;
1969 OPEN Status_cur FOR
1970 Select r.operation_status, s.version_enabled
1971 From gmd_operations r, gmd_status s
1972 Where r.oprn_id = Entity_id
1973 And r.operation_status = s.status_code;
1974 FETCH Status_cur INTO l_status, l_version_enabled;
1975 ClOSE Status_cur;
1976
1977 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => l_orgn_id ,
1978 P_parm_name => 'GMD_OPERATION_VERSION_CONTROL' ,
1979 P_parm_value => l_state ,
1980 X_return_status => l_return_status );
1981
1982 /*SELECT TRIM(FND_PROFILE.VALUE('GMD_OPERATION_VERSION_CONTROL'))
1983 INTO l_state
1984 FROM sys.dual;*/
1985
1986 IF ((l_state = 'Y') AND (l_version_enabled = 'Y')) THEN
1987 l_state := 'Y';
1988 ELSIF ((l_state = 'O') AND (l_version_enabled = 'Y')) THEN
1989 l_state := 'O';
1990 ELSE
1991 l_state := 'N';
1992 END IF;
1993 ELSIF (Upper(Entity) = 'SUBSTITUTION') THEN -- Bug number 4479101
1994 OPEN get_substitution_owner_orgn_id(entity_id);
1995 FETCH get_substitution_owner_orgn_id INTO l_orgn_id;
1996 CLOSE get_substitution_owner_orgn_id;
1997
1998 OPEN Status_cur FOR
1999 Select r.substitution_status, s.version_enabled
2000 From gmd_item_substitution_hdr_b r, gmd_status s
2001 Where r.substitution_id = Entity_id
2002 And r.substitution_status = s.status_code;
2003 FETCH Status_cur INTO l_status, l_version_enabled;
2004 ClOSE Status_cur;
2005
2006 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => l_orgn_id ,
2007 P_parm_name => 'GMD_SUBS_VERSION_CONTROL' ,
2008 P_parm_value => l_state ,
2009 X_return_status => l_return_status );
2010
2011 IF ((l_state = 'Y') AND (l_version_enabled = 'Y')) THEN
2012 l_state := 'Y';
2013 ELSIF ((l_state = 'O') AND (l_version_enabled = 'Y')) THEN
2014 l_state := 'O';
2015 ELSE
2016 l_state := 'N';
2017 END IF;
2018
2019 ELSE
2020 l_state := 'N';
2021 END IF;
2022
2023 return l_state;
2024
2025 END VERSION_CONTROL_STATE;
2026
2027
2028 /*****************************************************************************
2029 * PROCEDURE
2030 * set_conc_program_Status
2031 *
2032 * DESCRIPTION
2033 * Sets the concurrent manager completion status
2034 *
2035 * INPUT PARAMETERS
2036 * p_errstat - Completion status, must be one of 'NORMAL', 'WARNING', or
2037 * 'ERROR'
2038 * p_errmsg - Completion message to be passed back
2039 *
2040 * HISTORY
2041 * 05-31-2001 Shyam Sitaraman Created
2042 *
2043 ******************************************************************************/
2044
2045 PROCEDURE set_conc_program_Status (
2046 p_errstat IN VARCHAR2,
2047 p_errmsg IN VARCHAR2
2048 )
2049 IS
2050 l_retval BOOLEAN;
2051 BEGIN
2052
2053 l_retval := fnd_concurrent.set_completion_status(p_errstat,p_errmsg);
2054
2055 END set_conc_program_Status;
2056
2057
2058 /* **********************************************************************
2059 * PROCEDURE
2060 * Run_status_update
2061 *
2062 * Parameter Input
2063 * pCalendar_code - Calendar code set in cm_cldr_dtl
2064 * pPeriod_code - Period code set in cm_cldr_dtl
2065 * pCost_mthd_code - Cost_mthd_code from cm_cldr_hdr
2066 *
2067 * Parameters Output
2068 *
2069 * p_errbuf Completion message to the Concurrent Manager
2070 * p_retcode Return code to the Concurrent Manager
2071 *
2072 *
2073 * Description
2074 *
2075 * Procedure is used by costing to update the GMD tables with frozen status.
2076 * This procedure is registered as a concurrent program
2077 * Whenever costing updates the period status in cm_cldr_dtl table
2078 * from 0 to 1 , the trigger fires and submits a request for a
2079 * concurrent job.
2080 *
2081 * History
2082 * 05/31/2001 Shyam Created
2083 * 11/14/2001 Shyam Added fm context after the cost update.
2084 * 12-FEB-2002 Shyam BUG # 2222882: Changes to Procedure GMD_RUN_STATUS_UPDATE.
2085 * The FORALL condition for BULK update was changed to
2086 * conventional FOR LOOP statement and makes update for each row.
2087 *
2088 * 12-FEB-2002 Shyam Created an NVL ststement for routing_id that is returned after
2089 * the recipe table is updated. Recipe can have null routing_ids and
2090 * returning a NULL routing_id into variable l_routing_id can cause issues.
2091 * 01-MAR-2002 Shyam Added validation for Run_status_Updtae to check if the cost method is 'Standard'
2092 * and period status is 1.
2093 * 01/16/2003 Shyam UPdate made on status that are not obsoleted or on-hold
2094 *
2095 * *********************************************************************** */
2096
2097 PROCEDURE Run_status_update( p_errbuf OUT NOCOPY VARCHAR2,
2098 p_retcode OUT NOCOPY VARCHAR2,
2099 pCalendar_code IN cm_cmpt_dtl.calendar_code%TYPE,
2100 pPeriod_code IN cm_cmpt_dtl.period_code%TYPE,
2101 pCost_mthd_code IN cm_cmpt_dtl.cost_mthd_code%TYPE) IS
2102
2103 x_return_status VARCHAR2(1) := 'S';
2104 l_Recipe_Id NUMBER;
2105 l_formula_Id NUMBER;
2106 l_routing_Id NUMBER;
2107 l_oprn_Id NUMBER;
2108 l_period_cnt NUMBER;
2109 l_cost_type NUMBER;
2110
2111
2112 TYPE VRtbl IS TABLE OF GMD_RECIPE_VALIDITY_RULES.Recipe_Validity_Rule_Id%TYPE;
2113 VRList VRtbl;
2114
2115 CURSOR FROZEN_EFF_CUR IS
2116 SELECT distinct(fmeff_id) fmeff_id from cm_cmpt_dtl
2117 WHERE Calendar_code = pCalendar_code AND
2118 Period_code = pPeriod_code AND
2119 Cost_mthd_code = pCost_mthd_code AND
2120 ROLLOVER_IND = 1;
2121
2122 CURSOR Get_Period_Status IS
2123 SELECT count(*) FROM cm_cldr_dtl
2124 WHERE Calendar_code = pCalendar_code AND
2125 Period_code = pPeriod_code AND
2126 period_status = 1;
2127
2128 CURSOR Get_Cost_type IS
2129 SELECT cost_type from cm_mthd_mst
2130 WHERE cost_mthd_code = pCost_mthd_code;
2131
2132 CURSOR Get_Recipe_id(vValidity_Rule_id NUMBER) IS
2133 SELECT recipe_id from gmd_recipe_validity_rules
2134 WHERE recipe_validity_rule_id = vValidity_Rule_id;
2135
2136 CURSOR Get_FmRout_id(vRecipe_id NUMBER) IS
2137 SELECT formula_id, routing_id From gmd_recipes_b
2138 WHERE recipe_id = vRecipe_id;
2139
2140 Standard_costing_exception EXCEPTION;
2141 Period_status_exception EXCEPTION;
2142
2143 BEGIN
2144 SAVEPOINT update_status;
2145
2146 OPEN Get_Period_Status;
2147 FETCH Get_Period_Status INTO l_period_cnt;
2148 IF ((Get_Period_Status%NOTFOUND) OR (l_period_cnt = 0)) THEN
2149 CLOSE Get_Period_Status;
2150 Raise Period_Status_exception;
2151 END IF;
2152 CLOSE Get_Period_Status;
2153
2154 OPEN Get_Cost_type;
2155 FETCH Get_Cost_type INTO l_cost_type;
2156 IF ((Get_Cost_type%NOTFOUND) OR (l_cost_type = 1)) THEN
2157 CLOSE Get_Cost_type;
2158 Raise Standard_costing_exception;
2159 END IF;
2160 CLOSE Get_Cost_Type;
2161
2162 OPEN FROZEN_EFF_CUR;
2163 FETCH FROZEN_EFF_CUR BULK COLLECT INTO VRList;
2164 CLOSE FROZEN_EFF_CUR;
2165
2166 IF (VRList.count > 0) THEN
2167
2168 FOR i IN 1 .. VRList.count LOOP
2169
2170 /* Update the VR - status field */
2171 Update gmd_recipe_validity_rules
2172 SET validity_rule_status = '900'
2173 WHERE recipe_validity_rule_id = VRList(i)
2174 AND to_number(validity_rule_status) < 800;
2175
2176 OPEN Get_Recipe_id(VRList(i));
2177 FETCH Get_Recipe_id INTO l_Recipe_id;
2178 CLOSE Get_Recipe_id;
2179
2180 /* Update the Recipe - status field */
2181 UPDATE gmd_recipes_b
2182 SET recipe_status = '900'
2183 WHERE recipe_id = l_Recipe_Id
2184 AND to_number(recipe_status) < 800;
2185
2186 OPEN Get_FmRout_id(l_recipe_id);
2187 FETCH Get_FmRout_id INTO l_formula_id, l_routing_id;
2188 CLOSE Get_FmRout_id;
2189
2190 /* Update the formula and routing status */
2191 UPDATE fm_form_mst_b
2192 SET formula_status = '900'
2193 WHERE formula_id = l_formula_id
2194 AND to_number(formula_status) < 800;
2195
2196 UPDATE gmd_routings_b
2197 SET routing_status = '900'
2198 WHERE routing_id = l_routing_id
2199 AND to_number(routing_status) < 800;
2200
2201 /* Update oprns status */
2202 IF (l_routing_id IS NOT NULL) THEN
2203 UPDATE gmd_operations_b
2204 SET operation_status = '900'
2205 WHERE oprn_id IN (SELECT oprn_id
2206 FROM fm_rout_dtl d
2207 WHERE routing_id = l_routing_id)
2208 AND to_number(operation_status) < 800;
2209 END IF;
2210 END LOOP;
2211
2212 ELSE /* when VRList count is <= 0 */
2213 p_retcode := 0;
2214 p_errbuf := NULL;
2215 set_conc_program_Status('NORMAL', 'Did not update the status on GMD tables');
2216
2217 END IF;
2218
2219 /* If o.k until here */
2220 p_retcode := 0;
2221 p_errbuf := NULL;
2222 set_conc_program_Status('NORMAL', NULL);
2223
2224 /* sets the context for formula security */
2225 gmd_p_fs_context.set_additional_attr;
2226
2227 EXCEPTION
2228 WHEN Standard_costing_exception THEN
2229 p_retcode := 3;
2230 p_errbuf := NULL;
2231 set_conc_program_Status('ERROR','Invalid cost method only standard cost methods are allowed ' );
2232 ROLLBACK to update_status;
2233 WHEN Period_status_exception THEN
2234 p_retcode := 3;
2235 p_errbuf := NULL;
2236 set_conc_program_Status('ERROR','Invalid period status only frozen periods are allowed' );
2237 ROLLBACK to update_status;
2238 WHEN OTHERS THEN
2239 p_retcode := 3;
2240 p_errbuf := NULL;
2241 set_conc_program_Status('ERROR',sqlerrm);
2242 ROLLBACK to update_status;
2243
2244 END Run_Status_Update;
2245 /* **********************************************************************
2246 * PROCEDURE
2247 * check_formula_item_access
2248 *
2249 * Parameter Input
2250 * pFormula_id Formula Id
2251 * pInventory_Item_ID Inventory Item Id
2252 * Parameters Output
2253 * X_return_status Return Status
2254 * S when the org access are true
2255 * E when known error
2256 * U when unknown error
2257 *
2258 *
2259 * Description
2260 *
2261 * Procedure is used to identify the organization's item access used
2262 * for the recipe, override organizations and validaity rules.
2263 *
2264 * History
2265 * 05-Dec-2005 KSHUKLA Created
2266 * 30-May-2006 Kalyani Changed the order of variables in cursor fetch.
2267 * 12-Jun-2006 Kalyani Added new parameter pRevision and code to check the orgn access
2268 * for the item revision.
2269 * 21-Apr-2008 RLNAGARA Bug 6982623 Added validation for formula orgn item access.
2270 * *********************************************************************** */
2271 PROCEDURE CHECK_FORMULA_ITEM_ACCESS(pFormula_id IN NUMBER,
2272 pInventory_Item_ID IN NUMBER,
2273 x_return_status OUT NOCOPY VARCHAR2,
2274 pRevision IN VARCHAR2) IS
2275
2276 --RLNAGARA start Bug 6982623 Added validation for formula item access
2277
2278 CURSOR Cur_formula_own (p_formula_id NUMBER, p_inventory_item_id NUMBER) IS
2279 SELECT fm.formula_no, fm.formula_vers, organization_code
2280 FROM fm_form_mst fm, mtl_parameters o
2281 WHERE formula_id =p_formula_id
2282 AND fm.owner_organization_id = o.organization_id
2283 AND formula_status < 1000
2284 AND NOT EXISTS (SELECT 1
2285 FROM mtl_system_items m
2286 WHERE inventory_item_id = p_inventory_item_id
2287 AND recipe_enabled_flag = 'Y'
2288 AND m.organization_id = fm.owner_organization_id);
2289
2290 CURSOR Cur_formula_own_revision (p_formula_id NUMBER, p_inventory_item_id NUMBER,p_revision VARCHAR2) IS
2291 SELECT fm.formula_no, fm.formula_vers, organization_code
2292 FROM fm_form_mst fm, mtl_parameters o
2293 WHERE formula_id =p_formula_id
2294 AND fm.owner_organization_id = o.organization_id
2295 AND formula_status < 1000
2296 AND NOT EXISTS (SELECT 1
2297 FROM mtl_system_items m, mtl_item_revisions mir
2298 WHERE m.inventory_item_id = p_inventory_item_id
2299 AND mir.revision = p_revision
2300 AND m.inventory_item_id = mir.inventory_item_id
2301 AND m.recipe_enabled_flag = 'Y'
2302 AND m.organization_id = fm.owner_organization_id
2303 AND mir.organization_id = m.organization_id);
2304
2305 --RLNAGARA end Bug 6982623
2306
2307 -- Lists the organization id for the recipe which are not owned
2308 -- By the owner organization id of formula
2309 CURSOR Cur_recipe_own (p_formula_id NUMBER, p_inventory_item_id NUMBER) IS
2310 SELECT recipe_no, recipe_version, organization_code
2311 FROM gmd_recipes_b r, mtl_parameters o
2312 WHERE formula_id = p_formula_id
2313 AND r.owner_organization_id = o.organization_id
2314 AND recipe_status < 1000
2315 AND NOT EXISTS (SELECT 1
2316 FROM mtl_system_items m
2317 WHERE inventory_item_id = p_inventory_item_id
2318 AND recipe_enabled_flag = 'Y'
2319 AND m.organization_id = r.owner_organization_id);
2320 -- Bug 5237351 added
2321 CURSOR Cur_recipe_own_revision (p_formula_id NUMBER, p_inventory_item_id NUMBER,p_revision VARCHAR2) IS
2322 SELECT recipe_no, recipe_version, organization_code
2323 FROM gmd_recipes_b r, mtl_parameters o
2324 WHERE formula_id = p_formula_id
2325 AND r.owner_organization_id = o.organization_id
2326 AND recipe_status < 1000
2327 AND NOT EXISTS (SELECT 1
2328 FROM mtl_system_items m, mtl_item_revisions mir
2329 WHERE m.inventory_item_id = p_inventory_item_id
2330 AND mir.revision = p_revision
2331 AND m.inventory_item_id = mir.inventory_item_id
2332 AND m.recipe_enabled_flag = 'Y'
2333 AND m.organization_id = r.owner_organization_id
2334 AND mir.organization_id = m.organization_id
2335 );
2336
2337 -- Cursors to get the recipe over rides for a recipe id
2338 CURSOR Cur_recipe_override(p_formula_id number, p_inventory_item_id number) IS
2339 select r.recipe_no, r.recipe_version, o.organization_code
2340 from gmd_recipe_process_loss rpl, gmd_recipes_b r, mtl_parameters o
2341 where r.recipe_id = rpl.recipe_id
2342 AND r.formula_id = p_formula_id
2343 AND r.owner_organization_id <> rpl.organization_id
2344 AND rpl.organization_id = o.organization_id
2345 AND r.recipe_status < 1000
2346 AND NOT EXISTS (SELECT 1
2347 FROM mtl_system_items m
2348 WHERE inventory_item_id = p_inventory_item_id
2349 AND recipe_enabled_flag = 'Y'
2350 AND m.organization_id = rpl.organization_id);
2351
2352 -- Bug 5237351 added
2353 CURSOR Cur_recipe_override_revision(p_formula_id number, p_inventory_item_id number,p_revision VARCHAR2) IS
2354 select r.recipe_no, r.recipe_version, o.organization_code
2355 from gmd_recipe_process_loss rpl, gmd_recipes_b r, mtl_parameters o
2356 where r.recipe_id = rpl.recipe_id
2357 AND r.formula_id = p_formula_id
2358 AND r.owner_organization_id <> rpl.organization_id
2359 AND rpl.organization_id = o.organization_id
2360 AND r.recipe_status < 1000
2361 AND NOT EXISTS (SELECT 1
2362 FROM mtl_system_items m, mtl_item_revisions mir
2363 WHERE m.inventory_item_id = p_inventory_item_id
2364 AND mir.revision = p_revision
2365 AND m.inventory_item_id = mir.inventory_item_id
2366 AND m.recipe_enabled_flag = 'Y'
2367 AND m.organization_id = rpl.organization_id
2368 AND mir.organization_id = m.organization_id);
2369
2370 -- Cursor to get the validity rules for the recipes
2371 CURSOR Cur_recipe_validity(p_formula_id number,p_inventory_item_id number) IS
2372 select o.organization_code,r.recipe_no, r.recipe_version
2373 from gmd_recipe_validity_rules rvr, gmd_recipes_b r, mtl_parameters o
2374 where r.recipe_id = rvr.recipe_id
2375 and r.formula_id = p_formula_id
2376 and o.organization_id = rvr.organization_id
2377 AND rvr.organization_id <> r.owner_organization_id
2378 AND r.recipe_status < 1000
2379 AND rvr.validity_rule_status < 1000
2380 AND NOT EXISTS (SELECT 1
2381 FROM mtl_system_items m
2382 WHERE inventory_item_id = p_inventory_item_id
2383 AND recipe_enabled_flag = 'Y'
2384 AND m.organization_id = rvr.organization_id);
2385 -- Bug 5237351 added
2386 CURSOR Cur_recipe_validity_revision(p_formula_id number,p_inventory_item_id number,p_revision VARCHAR2) IS
2387 select o.organization_code,r.recipe_no, r.recipe_version
2388 from gmd_recipe_validity_rules rvr, gmd_recipes_b r, mtl_parameters o
2389 where r.recipe_id = rvr.recipe_id
2390 and r.formula_id = p_formula_id
2391 and o.organization_id = rvr.organization_id
2392 AND r.recipe_status < 1000
2393 AND rvr.validity_rule_status < 1000
2394 AND NOT EXISTS (SELECT 1
2395 FROM mtl_system_items m, mtl_item_revisions mir
2396 WHERE m.inventory_item_id = p_inventory_item_id
2397 AND mir.revision = p_revision
2398 AND m.inventory_item_id = mir.inventory_item_id
2399 AND m.recipe_enabled_flag = 'Y'
2400 AND m.organization_id = rvr.organization_id
2401 AND mir.organization_id = m.organization_id
2402 );
2403 -- Bug 5237351 added
2404 CURSOR Cur_item_rev_ctl(v_item_id NUMBER) IS
2405 select revision_qty_control_code
2406 from mtl_system_items_b
2407 where inventory_item_id = v_item_id;
2408
2409 l_rev_ctl number; -- Bug 5237351 added
2410 l_org_id number;
2411 l_organization_code varchar2(3);
2412 l_formula_no varchar2(32);
2413 l_formula_vers number; --RLNAGARA B6982623
2414 l_recipe_no varchar2(32);
2415 l_recipe_version number;
2416 X_global_return_status varchar2(1) := FND_API.g_ret_sts_success;
2417
2418 VALIDATION_FAIL EXCEPTION;
2419 NO_FORMULA EXCEPTION;
2420
2421 BEGIN
2422 --
2423 -- Find the formula no for the formula id
2424 --
2425 -- Check for the organization access to the formula
2426 IF pFormula_id IS NULL THEN
2427 RAISE NO_FORMULA;
2428
2429 END IF;
2430 -- Bug 5237351 added
2431 OPEN Cur_item_rev_ctl(pInventory_item_id);
2432 FETCH Cur_item_rev_ctl INTO l_rev_ctl;
2433 CLOSE Cur_item_rev_ctl;
2434
2435 --RLNAGARA start Bug 6982623 Added validation for formula item access
2436
2437 OPEN Cur_formula_own (pFormula_id, pInventory_item_id);
2438 FETCH Cur_formula_own INTO l_formula_no, l_formula_vers, l_organization_code;
2439 IF Cur_formula_own%FOUND THEN
2440 CLOSE Cur_formula_own;
2441 FND_MESSAGE.set_name('GMD','GMD_FORM_OWNORG_NO_ACCESS');
2442 FND_MESSAGE.set_token('FORMULA_NO',l_formula_no);
2443 FND_MESSAGE.set_token('FORMULA_VERSION',l_formula_vers);
2444 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',l_organization_code);
2445 fnd_msg_pub.ADD;
2446 X_return_status := FND_API.g_ret_sts_error;
2447 RAISE VALIDATION_FAIL;
2448 END IF;
2449 CLOSE Cur_formula_own;
2450
2451 IF l_rev_ctl = 2 and pRevision IS NOT NULL THEN
2452 OPEN Cur_formula_own_revision (pFormula_id, pInventory_item_id,pRevision);
2453 FETCH Cur_formula_own_revision INTO l_formula_no, l_formula_vers, l_organization_code;
2454 IF Cur_formula_own_revision%FOUND THEN
2455 CLOSE Cur_formula_own_revision;
2456 FND_MESSAGE.set_name('GMD','GMD_FORM_REV_OWNORG_NO_ACCESS');
2457 FND_MESSAGE.set_token('FORMULA_NO',l_formula_no);
2458 FND_MESSAGE.set_token('FORMULA_VERSION',l_formula_vers);
2459 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',l_organization_code);
2460 fnd_msg_pub.ADD;
2461 X_return_status := FND_API.g_ret_sts_error;
2462 RAISE VALIDATION_FAIL;
2463 END IF;
2464 CLOSE Cur_formula_own_revision;
2465 END IF;
2466
2467 --RLNAGARA end Bug 6982623
2468
2469 OPEN Cur_recipe_own (pFormula_id, pInventory_item_id);
2470 FETCH Cur_recipe_own INTO l_recipe_no, l_recipe_version, l_organization_code;
2471 IF Cur_recipe_own%FOUND THEN
2472 CLOSE Cur_recipe_own;
2473 FND_MESSAGE.set_name('GMD','GMD_OWNER_ORG_NOT_ACCESSIBLE');
2474 FND_MESSAGE.set_token('RECIPE_NO',l_recipe_no);
2475 FND_MESSAGE.set_token('RECIPE_VERSION',l_recipe_version);
2476 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',l_organization_code);
2477 fnd_msg_pub.ADD;
2478 X_return_status := FND_API.g_ret_sts_error;
2479 RAISE VALIDATION_FAIL;
2480 END IF;
2481 CLOSE Cur_recipe_own;
2482 -- Bug 5237351 added
2483 IF l_rev_ctl = 2 and pRevision IS NOT NULL THEN
2484 OPEN Cur_recipe_own_revision (pFormula_id, pInventory_item_id,pRevision);
2485 FETCH Cur_recipe_own_revision INTO l_recipe_no, l_recipe_version, l_organization_code;
2486 IF Cur_recipe_own_revision%FOUND THEN
2487 CLOSE Cur_recipe_own_revision;
2488 FND_MESSAGE.set_name('GMD','GMD_REV_OWNORG_NOT_ACCESSIBLE');
2489 FND_MESSAGE.set_token('RECIPE_NO',l_recipe_no);
2490 FND_MESSAGE.set_token('RECIPE_VERSION',l_recipe_version);
2491 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',l_organization_code);
2492 fnd_msg_pub.ADD;
2493 X_return_status := FND_API.g_ret_sts_error;
2494 RAISE VALIDATION_FAIL;
2495 END IF;
2496 CLOSE Cur_recipe_own_revision;
2497 END IF;
2498 -- Check the organization access for the override organizations
2499 OPEN Cur_recipe_override (pFormula_id, pInventory_item_id);
2500 -- Bug 5237126 MK Changed the order of variables.
2501 FETCH Cur_recipe_override INTO l_recipe_no, l_recipe_version, l_organization_code;
2502 IF Cur_recipe_override%FOUND THEN
2503 CLOSE Cur_recipe_override;
2504 FND_MESSAGE.set_name('GMD','GMD_OVERRIDE_ORG_NOT_ACCESSIBL');
2505 FND_MESSAGE.set_token('RECIPE_NO',l_recipe_no);
2506 FND_MESSAGE.set_token('RECIPE_VERSION',l_recipe_version);
2507 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',l_organization_code);
2508 fnd_msg_pub.ADD;
2509 X_return_status := FND_API.g_ret_sts_error;
2510 RAISE VALIDATION_FAIL;
2511 END IF;
2512 CLOSE Cur_recipe_override;
2513 -- Bug 5237351 added
2514 IF l_rev_ctl = 2 and pRevision IS NOT NULL THEN
2515 OPEN Cur_recipe_override_revision (pFormula_id, pInventory_item_id, pRevision);
2516 FETCH Cur_recipe_override_revision INTO l_recipe_no, l_recipe_version, l_organization_code;
2517 IF Cur_recipe_override_revision%FOUND THEN
2518 CLOSE Cur_recipe_override_revision;
2519 FND_MESSAGE.set_name('GMD','GMD_REV_OVERORG_NOT_ACCESSIBL');
2520 FND_MESSAGE.set_token('RECIPE_NO',l_recipe_no);
2521 FND_MESSAGE.set_token('RECIPE_VERSION',l_recipe_version);
2522 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',l_organization_code);
2523 fnd_msg_pub.ADD;
2524 X_return_status := FND_API.g_ret_sts_error;
2525 RAISE VALIDATION_FAIL;
2526 END IF;
2527 CLOSE Cur_recipe_override_revision;
2528 END IF;
2529 OPEN Cur_recipe_validity (pFormula_id, pInventory_item_id);
2530 FETCH Cur_recipe_validity INTO l_organization_code, l_recipe_no, l_recipe_version;
2531 IF Cur_recipe_validity%FOUND THEN
2532 CLOSE Cur_recipe_validity;
2533 FND_MESSAGE.set_name('GMD','GMD_VALIDITY_OWNER_ORG_NOT_ACC');
2534 FND_MESSAGE.set_token('RECIPE_NO',l_recipe_no);
2535 FND_MESSAGE.set_token('RECIPE_VERSION',l_recipe_version);
2536 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',l_organization_code);
2537 fnd_msg_pub.ADD;
2538 X_return_status := FND_API.g_ret_sts_error;
2539 RAISE VALIDATION_FAIL;
2540 END IF;
2541 CLOSE Cur_recipe_validity;
2542 -- Bug 5237351 added
2543 IF l_rev_ctl = 2 and pRevision IS NOT NULL THEN
2544 OPEN Cur_recipe_validity_revision (pFormula_id, pInventory_item_id, pRevision);
2545 FETCH Cur_recipe_validity_revision INTO l_organization_code, l_recipe_no, l_recipe_version;
2546 IF Cur_recipe_validity_revision%FOUND THEN
2547 CLOSE Cur_recipe_validity_revision;
2548 FND_MESSAGE.set_name('GMD','GMD_REV_VALIDITY_ORG_NOT_ACC');
2549 FND_MESSAGE.set_token('RECIPE_NO',l_recipe_no);
2550 FND_MESSAGE.set_token('RECIPE_VERSION',l_recipe_version);
2551 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',l_organization_code);
2552 fnd_msg_pub.ADD;
2553 X_return_status := FND_API.g_ret_sts_error;
2554 RAISE VALIDATION_FAIL;
2555 END IF;
2556 CLOSE Cur_recipe_validity_revision;
2557 END IF;
2558
2559 -- Final Return Status of the API
2560 x_return_status := FND_API.G_RET_STS_SUCCESS;
2561 EXCEPTION
2562 WHEN VALIDATION_FAIL THEN
2563 x_return_status := FND_API.G_RET_STS_ERROR;
2564 WHEN NO_FORMULA THEN
2565 x_return_status := FND_API.G_RET_STS_SUCCESS;
2566
2567 WHEN OTHERS THEN
2568 X_return_status := FND_API.g_ret_sts_unexp_error;
2569
2570 END check_formula_item_access;
2571
2572 -- Kapil ME Auto-Prod :Bug# 5716318
2573 /* Api start of comments
2574 +============================================================================
2575 | PROCEDURE NAME
2576 | CALCULATE_TOTAL_PRODUCT_QTY
2577 |
2578 | DESCRIPTION
2579 | Procedure to calculate Product Qty autmatically.
2580 |
2581 | INPUT PARAMETERS
2582 | pFormula_id NUMBER
2583 |
2584 | OUTPUT PARAMETERS
2585 | x_msg_data VARCHAR2
2586 | x_return_status VARCHAR2
2587 | x_msg_count NUMBER
2588 |
2589 | HISTORY
2590 | 05-FEB-2007 Kapil M Bug# 5716318 Created.
2591 |
2592 +=============================================================================
2593 Api end of comments
2594 */
2595 PROCEDURE Calculate_Total_Product_Qty ( p_formula_id IN gmd_recipes.formula_id%TYPE,
2596 x_return_status OUT NOCOPY VARCHAR2,
2597 x_msg_count OUT NOCOPY NUMBER,
2598 x_msg_data OUT NOCOPY VARCHAR2) IS
2599
2600 -- Definition of variables
2601 l_auto_calc VARCHAR2(1) := 'N';
2602 l_count NUMBER (5) DEFAULT 0;
2603 l_material_tab gmd_common_scale.scale_tab;
2604 l_uom VARCHAR2(30);
2605 l_conv_uom VARCHAR2(30);
2606 l_temp_qty NUMBER := 0;
2607
2608 l_ingredient_qty NUMBER := 0;
2609 l_by_product_qty NUMBER := 0;
2610 l_qty_touse NUMBER := 0;
2611 l_prod_cnt NUMBER := 0;
2612 l_prod_ratio NUMBER := 1;
2613 l_one_prodqty NUMBER;
2614 l_prod_fix_cnt NUMBER := 0;
2615 l_prod_fix_qty NUMBER := 0;
2616 l_prod_prop_cnt NUMBER := 0;
2617 l_prod_prop_qty NUMBER := 0;
2618 lhdrqty NUMBER := 0;
2619 l_different_uom VARCHAR2 (1) := 'N';
2620 l_return_status VARCHAR2(1);
2621 p_orgn_id NUMBER;
2622 l_yield_type VARCHAR2(30);
2623 l_uom_class VARCHAR2(10);
2624 l_common_uom_class VARCHAR2(10);
2625
2626 CANNOT_CONVERT EXCEPTION;
2627
2628 --Bug 7243526. Changed orig_uom and cov_uom from VARCHAR2(4) to VARCHAR2(30)
2629 TYPE temp_prod_qty_rec IS RECORD (
2630 inventory_item_id NUMBER,
2631 orig_qty NUMBER,
2632 orig_uom VARCHAR2(30),
2633 cov_qty NUMBER,
2634 cov_uom VARCHAR2(30),
2635 line_no NUMBER,
2636 prod_percent NUMBER
2637 );
2638 TYPE temp_prod_tbl IS TABLE OF temp_prod_qty_rec
2639 INDEX BY BINARY_INTEGER;
2640
2641 temp_prod_tbl1 temp_prod_tbl;
2642 -- Cursor Definitions
2643
2644 CURSOR Cur_get_formula_org IS
2645 SELECT OWNER_ORGANIZATION_ID
2646 FROM FM_FORM_MST
2647 WHERE formula_id = p_formula_id;
2648
2649 CURSOR get_formula_lines (vformula_id NUMBER) IS
2650 SELECT line_no, line_type, inventory_item_id, qty, DETAIL_UOM, scale_type,
2651 contribute_yield_ind, scale_multiple, scale_rounding_variance,
2652 rounding_direction , prod_percent
2653 FROM fm_matl_dtl
2654 WHERE formula_id = p_formula_id
2655 AND contribute_yield_ind = 'Y' /* Added in Bug No.6314028 */
2656 ORDER BY line_type;
2657
2658 CURSOR get_unit_of_measure(v_yield_type VARCHAR2) IS
2659 SELECT uom_code
2660 FROM mtl_units_of_measure
2661 WHERE uom_class = v_yield_type
2662 AND base_uom_flag = 'Y';
2663
2664 BEGIN
2665
2666 x_return_status := FND_API.G_RET_STS_SUCCESS;
2667 FND_MSG_PUB.initialize;
2668
2669 -- Check ORganization Parameters
2670 OPEN Cur_get_formula_org;
2671 FETCH Cur_get_formula_org INTO p_orgn_id;
2672 CLOSE Cur_get_formula_org;
2673 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => p_orgn_id ,
2674 P_parm_name => 'GMD_AUTO_PROD_CALC' ,
2675 P_parm_value => l_auto_calc ,
2676 X_return_status => x_return_status );
2677
2678 IF l_auto_calc = 'Y' THEN -- Perform Auto Calculation
2679
2680 -- Get the Yield type UOM - NPD Convergence
2681 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => p_orgn_id ,
2682 P_parm_name => 'FM_YIELD_TYPE' ,
2683 P_parm_value => l_yield_type ,
2684 X_return_status => x_return_status );
2685 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2686 RETURN;
2687 END IF;
2688
2689 /* Bug no.7145922 - Start */
2690 IF (l_yield_type IS NOT NULL) THEN
2691 OPEN get_unit_of_measure(l_yield_type);
2692 FETCH get_unit_of_measure INTO l_uom;
2693 IF get_unit_of_measure%NOTFOUND THEN
2694 l_uom := NULL;
2695 END IF;
2696 CLOSE get_unit_of_measure;
2697 END IF;
2698 /* Bug no.7145922 - End */
2699
2700 /* Populate a local pl/sql table that will be iterated for further processings. */
2701 FOR l_rec IN get_formula_lines (p_formula_id)
2702 LOOP
2703 l_count := l_count + 1;
2704
2705 IF NVL (l_uom, l_rec.detail_uom) <> l_rec.detail_uom
2706 THEN
2707 l_different_uom := 'Y';
2708 END IF;
2709 l_material_tab (l_count).line_no := l_rec.line_no;
2710 l_material_tab (l_count).line_type := l_rec.line_type;
2711 l_material_tab (l_count).inventory_item_id := l_rec.inventory_item_id;
2712 l_material_tab (l_count).qty := l_rec.qty;
2713 l_material_tab (l_count).detail_uom := l_rec.detail_uom;
2714 l_material_tab (l_count).scale_type := l_rec.scale_type;
2715 l_material_tab (l_count).contribute_yield_ind :=
2716 l_rec.contribute_yield_ind;
2717 l_material_tab (l_count).scale_multiple := l_rec.prod_percent;
2718
2719 l_material_tab (l_count).scale_rounding_variance :=
2720 l_rec.scale_rounding_variance;
2721 l_material_tab (l_count).rounding_direction :=
2722 l_rec.rounding_direction;
2723 -- l_uom := l_rec.detail_uom; /* Commented in Bug No.7145922 */
2724 END LOOP;
2725
2726 /* UOM COnversions - Get the common UOM for conversions */
2727
2728 -- FOR i IN 1 .. l_material_tab.COUNT
2729 -- LOOP
2730 -- IF l_material_tab(i).detail_uom IS NOT NULL THEN
2731 /* Get the common UOM class of the detail lines UOM */
2732 /* SELECT uom_class
2733 INTO l_uom_class
2734 FROM mtl_units_of_measure
2735 where uom_code = l_material_tab (i).detail_uom; */
2736
2737 /* If different UOM - get the yield type UOM for conversions. */
2738 /* IF NVL(l_common_uom_class,l_uom_class) <> l_uom_class THEN
2739 OPEN get_unit_of_measure(l_yield_type);
2740 FETCH get_unit_of_measure INTO l_conv_uom;
2741 CLOSE get_unit_of_measure;
2742 END IF;
2743 l_common_uom_class := l_uom_class;
2744 END IF;
2745 END LOOP;*/
2746 /* If all the UOMs belong to the same class, Get the base UOM for conversion. */
2747 /* IF l_conv_uom IS NULL THEN
2748 OPEN get_unit_of_measure(l_common_uom_class);
2749 FETCH get_unit_of_measure INTO l_conv_uom;
2750 CLOSE get_unit_of_measure;
2751
2752 END IF; */
2753
2754 /* Bug No.7145922 - Commented the above FOR loop and added the below line */
2755 l_conv_uom := l_uom;
2756
2757 -- Calculate the totla material line quantities
2758 FOR i IN 1 .. l_material_tab.COUNT
2759 LOOP
2760 IF l_different_uom = 'Y'
2761 THEN
2762 l_temp_qty := INV_CONVERT.inv_um_convert(item_id => l_material_tab (i).inventory_item_id
2763 ,precision => 5
2764 ,from_quantity => l_material_tab (i).qty
2765 ,from_unit => l_material_tab (i).detail_uom
2766 ,to_unit => l_conv_uom
2767 ,from_name => NULL
2768 ,to_name => NULL);
2769
2770 IF l_temp_qty < 0
2771 THEN
2772 fnd_message.set_name ('GMD', 'GMD_UOM_CONV_ERROR');
2773 fnd_message.set_token('UOM',l_conv_uom);
2774 fnd_msg_pub.ADD;
2775 RAISE CANNOT_CONVERT;
2776 END IF;
2777 ELSE
2778 l_temp_qty := l_material_tab (i).qty;
2779 END IF;
2780
2781 -- If it is ingredient then see if this is contributing to yield or not
2782 IF l_material_tab (i).line_type = -1
2783 AND l_material_tab (i).contribute_yield_ind = 'Y'
2784 THEN
2785 l_ingredient_qty := NVL (l_ingredient_qty, 0) + l_temp_qty;
2786 ELSIF l_material_tab (i).line_type = 2
2787 THEN -- By product
2788 l_by_product_qty := l_by_product_qty + l_temp_qty;
2789 ELSIF l_material_tab (i).line_type = 1
2790 THEN -- Products
2791 l_prod_cnt := l_prod_cnt + 1;
2792
2793 /* See if the product is of scale type fixed, if yes, then do not update and also use to subtract from the total
2794 ingredient qty to be distributed. */
2795 IF l_material_tab (i).scale_type = 0
2796 THEN
2797 l_prod_fix_cnt := l_prod_fix_cnt + 1;
2798 l_prod_fix_qty := l_prod_fix_qty + l_temp_qty;
2799 ELSE
2800 l_prod_prop_cnt := l_prod_prop_cnt + 1;
2801 l_prod_prop_qty := l_prod_prop_qty + l_temp_qty;
2802 temp_prod_tbl1 (l_prod_prop_cnt).inventory_item_id :=
2803 l_material_tab (i).inventory_item_id;
2804 temp_prod_tbl1 (l_prod_prop_cnt).cov_qty := l_temp_qty;
2805 -- Conv UOM
2806 temp_prod_tbl1 (l_prod_prop_cnt).cov_uom := l_conv_uom;
2807 temp_prod_tbl1 (l_prod_prop_cnt).orig_qty :=
2808 l_material_tab (i).qty;
2809 temp_prod_tbl1 (l_prod_prop_cnt).orig_uom :=
2810 l_material_tab (i).detail_uom;
2811 temp_prod_tbl1 (l_prod_prop_cnt).line_no :=
2812 l_material_tab (i).line_no;
2813 temp_prod_tbl1 (l_prod_prop_cnt).prod_percent :=
2814 l_material_tab (i).scale_multiple;
2815
2816 END IF;
2817 END IF;
2818 END LOOP;
2819
2820 /* Get the Quantity to be distributed among Products.
2821 Qty = Sum(INGR) - SUM(BY-PRODS) - SUM(PROD-FIXED) */
2822 l_qty_touse :=
2823 l_ingredient_qty - NVL (l_by_product_qty, 0)
2824 - NVL (l_prod_fix_qty, 0);
2825
2826 /* Now Calculate the Product Qty based on ratio */
2827 FOR i IN 1 .. temp_prod_tbl1.COUNT
2828 LOOP
2829 IF l_prod_prop_qty > 0 THEN
2830 /* Check whether Percentages have been enterd for all Proportional Products. */
2831 IF temp_prod_tbl1 (i).prod_percent IS NULL THEN
2832 fnd_message.set_name ('GMD', 'GMD_ENTER_PERCENTAGE_YES');
2833 fnd_msg_pub.ADD;
2834 RAISE CANNOT_CONVERT;
2835 END IF;
2836 l_prod_ratio := temp_prod_tbl1 (i).prod_percent / 100;
2837 ELSE
2838 l_prod_ratio := 1;
2839 END IF;
2840
2841 -- Calculate the specific prod qty
2842 IF l_qty_touse > 0
2843 THEN
2844 l_one_prodqty := l_qty_touse * l_prod_ratio;
2845 ELSE
2846 l_one_prodqty := temp_prod_tbl1 (i).cov_qty;
2847 END IF;
2848
2849 -- Also keep updating the formula level product qty.
2850 lhdrqty := lhdrqty + l_one_prodqty;
2851 -- Changed
2852 IF l_different_uom = 'Y'
2853 THEN
2854 /* Bug No.9399933 - Changed item_id from l_material_tab(i).inventory_item_id to temp_prod_tbl1 (i).inventory_item_id */
2855 l_temp_qty := INV_CONVERT.inv_um_convert(item_id => temp_prod_tbl1 (i).inventory_item_id
2856 ,precision => 5
2857 ,from_quantity => l_one_prodqty
2858 ,from_unit => l_conv_uom
2859 ,to_unit => temp_prod_tbl1 (i).orig_uom
2860 ,from_name => NULL
2861 ,to_name => NULL);
2862
2863 IF l_temp_qty < 0
2864 THEN
2865 x_return_status := 'Q';
2866 fnd_message.set_name ('GMD', 'GMD_UOM_CONV_ERROR');
2867 fnd_message.set_token('UOM',l_conv_uom);
2868 fnd_msg_pub.ADD;
2869 EXIT;
2870 END IF;
2871 ELSE
2872 l_temp_qty := l_one_prodqty;
2873 END IF;
2874
2875 UPDATE fm_matl_dtl
2876 SET qty = ROUND (l_temp_qty, 5),
2877 DETAIL_UOM = temp_prod_tbl1 (i).orig_uom
2878 WHERE formula_id = p_formula_id
2879 AND line_type = 1
2880 AND inventory_item_id = temp_prod_tbl1 (i).inventory_item_id
2881 AND line_no = temp_prod_tbl1 (i).line_no;
2882 END LOOP;
2883
2884 /* Finally update the formula level product qty as prod qty + by product qty. */
2885 lhdrqty := lhdrqty + l_prod_fix_qty + NVL (l_by_product_qty, 0);
2886
2887 -- Update formula Header also
2888 UPDATE fm_form_mst_b
2889 SET total_output_qty = lhdrqty
2890 WHERE formula_id = p_formula_id;
2891 /* Get the message count from the Message stack */
2892 END IF;
2893 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2894 p_data => x_msg_data);
2895 EXCEPTION
2896 WHEN CANNOT_CONVERT THEN
2897 x_return_status := fnd_api.g_ret_sts_unexp_error;
2898 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2899 p_data => x_msg_data);
2900 END Calculate_Total_Product_Qty ;
2901
2902 /* Bug No.7027512 - Start */
2903 /* **********************************************************************
2904 * PROCEDURE
2905 * Run_status_update
2906 *
2907 * Description
2908 *
2909 * Procedure is used by costing to update the GMD tables with frozen status.
2910 * This procedure is registered as a concurrent program
2911 *
2912 * History
2913 * 17-Jul-2008 Kishore Created
2914 *
2915 * *********************************************************************** */
2916
2917 PROCEDURE Run_status_update( p_errbuf OUT NOCOPY VARCHAR2,
2918 p_retcode OUT NOCOPY VARCHAR2,
2919 pLegal_entity_id IN number,
2920 pCalendar_code IN cm_cmpt_dtl.calendar_code%TYPE,
2921 pPeriod_code IN cm_cmpt_dtl.period_code%TYPE,
2922 pCost_type_id IN cm_cmpt_dtl.Cost_type_id%TYPE) IS
2923
2924 x_return_status VARCHAR2(1) := 'S';
2925 l_Recipe_Id NUMBER;
2926 l_formula_Id NUMBER;
2927 l_routing_Id NUMBER;
2928 l_oprn_Id NUMBER;
2929 l_period_cnt NUMBER;
2930 l_cost_type NUMBER;
2931 l_period_id NUMBER;
2932
2933
2934 TYPE VRtbl IS TABLE OF GMD_RECIPE_VALIDITY_RULES.Recipe_Validity_Rule_Id%TYPE;
2935 VRList VRtbl;
2936
2937 CURSOR Get_period_id IS
2938 SELECT period_id from gmf_period_statuses
2939 WHERE Legal_Entity_Id = pLegal_entity_id AND
2940 Calendar_code = pCalendar_code AND
2941 Period_code = pPeriod_code AND
2942 Cost_type_id = pCost_type_id ;
2943
2944 CURSOR FROZEN_EFF_CUR IS
2945 SELECT distinct(fmeff_id) fmeff_id from cm_cmpt_dtl
2946 WHERE period_id = l_period_id AND
2947 cost_type_id = pCost_type_id AND
2948 ROLLOVER_IND = 1;
2949
2950 CURSOR Get_Period_Status IS
2951 SELECT count(*) FROM gmf_period_statuses
2952 WHERE period_id = l_period_id AND
2953 period_status = 'F';
2954
2955 CURSOR Get_Cost_type IS
2956 SELECT cost_type from cm_mthd_mst
2957 WHERE cost_type_id = pCost_type_id;
2958
2959 CURSOR Get_Recipe_id(vValidity_Rule_id NUMBER) IS
2960 SELECT recipe_id from gmd_recipe_validity_rules
2961 WHERE recipe_validity_rule_id = vValidity_Rule_id;
2962
2963 CURSOR Get_FmRout_id(vRecipe_id NUMBER) IS
2964 SELECT formula_id, routing_id From gmd_recipes_b
2965 WHERE recipe_id = vRecipe_id;
2966
2967 Standard_costing_exception EXCEPTION;
2968 Period_status_exception EXCEPTION;
2969 Period_id_exception EXCEPTION;
2970
2971 BEGIN
2972 SAVEPOINT update_status;
2973
2974 OPEN Get_period_id;
2975 FETCH Get_period_id INTO l_period_id;
2976 IF (Get_period_id%NOTFOUND) THEN
2977 CLOSE Get_period_id;
2978 Raise Period_Id_exception;
2979 END IF;
2980 CLOSE Get_period_id;
2981
2982 OPEN Get_Period_Status;
2983 FETCH Get_Period_Status INTO l_period_cnt;
2984 IF ((Get_Period_Status%NOTFOUND) OR (l_period_cnt = 0)) THEN
2985 CLOSE Get_Period_Status;
2986 Raise Period_Status_exception;
2987 END IF;
2988 CLOSE Get_Period_Status;
2989
2990 OPEN Get_Cost_type;
2991 FETCH Get_Cost_type INTO l_cost_type;
2992 IF ((Get_Cost_type%NOTFOUND) OR (l_cost_type = 1)) THEN
2993 CLOSE Get_Cost_type;
2994 Raise Standard_costing_exception;
2995 END IF;
2996 CLOSE Get_Cost_Type;
2997
2998 OPEN FROZEN_EFF_CUR;
2999 FETCH FROZEN_EFF_CUR BULK COLLECT INTO VRList;
3000 CLOSE FROZEN_EFF_CUR;
3001
3002 IF (VRList.count > 0) THEN
3003
3004 FOR i IN 1 .. VRList.count LOOP
3005
3006 /* Update the VR - status field */
3007 Update gmd_recipe_validity_rules
3008 SET validity_rule_status = '900'
3009 WHERE recipe_validity_rule_id = VRList(i)
3010 AND to_number(validity_rule_status) < 800;
3011
3012 OPEN Get_Recipe_id(VRList(i));
3013 FETCH Get_Recipe_id INTO l_Recipe_id;
3014 CLOSE Get_Recipe_id;
3015
3016 /* Update the Recipe - status field */
3017 UPDATE gmd_recipes_b
3018 SET recipe_status = '900'
3019 WHERE recipe_id = l_Recipe_Id
3020 AND to_number(recipe_status) < 800;
3021
3022 OPEN Get_FmRout_id(l_recipe_id);
3023 FETCH Get_FmRout_id INTO l_formula_id, l_routing_id;
3024 CLOSE Get_FmRout_id;
3025
3026 /* Update the formula and routing status */
3027 UPDATE fm_form_mst_b
3028 SET formula_status = '900'
3029 WHERE formula_id = l_formula_id
3030 AND to_number(formula_status) < 800;
3031
3032 UPDATE gmd_routings_b
3033 SET routing_status = '900'
3034 WHERE routing_id = l_routing_id
3035 AND to_number(routing_status) < 800;
3036
3037 /* Update oprns status */
3038 IF (l_routing_id IS NOT NULL) THEN
3039 UPDATE gmd_operations_b
3040 SET operation_status = '900'
3041 WHERE oprn_id IN (SELECT oprn_id
3042 FROM fm_rout_dtl d
3043 WHERE routing_id = l_routing_id)
3044 AND to_number(operation_status) < 800;
3045 END IF;
3046 END LOOP;
3047
3048 ELSE /* when VRList count is <= 0 */
3049 p_retcode := 0;
3050 p_errbuf := NULL;
3051 set_conc_program_Status('NORMAL', 'Did not update the status on GMD tables');
3052
3053 END IF;
3054
3055 /* If o.k until here */
3056 p_retcode := 0;
3057 p_errbuf := NULL;
3058 set_conc_program_Status('NORMAL', NULL);
3059
3060 /* sets the context for formula security */
3061 gmd_p_fs_context.set_additional_attr;
3062
3063 EXCEPTION
3064 WHEN Standard_costing_exception THEN
3065 p_retcode := 3;
3066 p_errbuf := NULL;
3067 set_conc_program_Status('ERROR','Invalid cost method only standard cost methods are allowed ' );
3068 ROLLBACK to update_status;
3069 WHEN Period_status_exception THEN
3070 p_retcode := 3;
3071 p_errbuf := NULL;
3072 set_conc_program_Status('ERROR','Invalid period status only frozen periods are allowed' );
3073 ROLLBACK to update_status;
3074 WHEN Period_id_exception THEN
3075 p_retcode := 3;
3076 p_errbuf := NULL;
3077 set_conc_program_Status('ERROR','Error while fetching period_id' );
3078 ROLLBACK to update_status;
3079 WHEN OTHERS THEN
3080 p_retcode := 3;
3081 p_errbuf := NULL;
3082 set_conc_program_Status('ERROR',sqlerrm);
3083 ROLLBACK to update_status;
3084
3085 END Run_Status_Update;
3086 /* Bug No.7027512 - End */
3087
3088 END; /* Package Body GMD_COMMON_VAL */
[Home] [Help]