1 Package Body GMD_COMMON_VAL AS
2 /* $Header: GMDPCOMB.pls 120.15.12010000.2 2008/11/06 18:49:15 rnalla 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;
384 x_return_status := FND_API.G_RET_STS_SUCCESS;
381 IF FND_API.to_Boolean(p_init_msg_list) THEN
382 FND_MSG_PUB.initialize;
383 END IF;
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 /* | | */
490 /* | | */
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 | */
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;
574
571 l_validity_scale_factor NUMBER;
572 l_item_id NUMBER(10);
573 l_validity_um VARCHAR2(4);
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;
695 /* IF routing qty is provided as opposed to the
692 IF (l_debug = 'Y') THEN
693 gmd_debug.put_line(' Routing UOM : '||l_routing_uom);
694 END IF;
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);
770 /* If for some reason the yield_uom in formula table is not defined OR
767 END IF;
768 END IF;
769
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,
869 p_scale_factor => l_validity_scale_factor,
866 x_return_status => l_toq_return_status,
867 x_msg_count => x_msg_count,
868 x_msg_data => x_msg_data,
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 /* */
974 /* ====================================================================== */
971 /* Procedure returns the total product and ingredient qty */
972 /* The uom is that of FM_YIELD_TYPE UOM */
973 /* */
975 PROCEDURE Calculate_Total_Qty
976 ( formula_id IN GMD_RECIPES.Formula_id%TYPE ,
977 x_product_qty OUT NOCOPY NUMBER ,
978 x_ingredient_qty OUT NOCOPY NUMBER ,
979 x_uom IN OUT NOCOPY VARCHAR2 ,
980 x_return_status OUT NOCOPY VARCHAR2 ,
981 x_msg_count OUT NOCOPY NUMBER ,
982 x_msg_data OUT NOCOPY VARCHAR2 ,
983 p_scale_factor IN NUMBER ,
984 p_primaries IN VARCHAR2
985 ) IS
986
987 l_temp_qty NUMBER := 0;
988 l_um_type mtl_units_of_measure.uom_class%TYPE;
989 /*Bug 5667857 - Change the column from unit_of_measure to uom_code */
990 CURSOR get_sy_std_um(pUm_type mtl_units_of_measure.uom_class%TYPE) IS
991 SELECT uom_code
992 FROM mtl_units_of_measure
993 WHERE uom_class = pUm_type
994 AND base_uom_flag = 'Y';
995
996 CURSOR prod_um_cur(vFormula_id NUMBER) IS
997 SELECT detail_uom
998 FROM fm_matl_dtl
999 WHERE line_no = 1
1000 AND line_type = 1
1001 AND formula_id = vFormula_id;
1002
1003 /* Bug 1683702 - Thomas Daniel */
1004 l_count NUMBER(5) DEFAULT 0;
1005 l_scale_tab GMD_COMMON_SCALE.scale_tab;
1006 l_material_tab GMD_COMMON_SCALE.scale_tab;
1007
1008 -- NPD Conv. Use inventory_iem_id and detail_uom instead of item_id and item_um
1009 CURSOR Get_formula_lines (vFormula_id NUMBER) IS
1010 SELECT line_no, line_type, inventory_item_id, qty, detail_uom, scale_type,
1011 contribute_yield_ind, scale_multiple, scale_rounding_variance,
1012 rounding_direction
1013 FROM fm_matl_dtl
1014 WHERE formula_id = vFormula_id
1015 ORDER BY line_type;
1016
1017 -- NPD Conv.
1018 l_orgn_id NUMBER;
1019 l_return_status VARCHAR2(10);
1020
1021 CURSOR get_formula_owner_orgn_id(vformula_id NUMBER) IS
1022 SELECT owner_organization_id
1023 FROM fm_form_mst_b
1024 WHERE formula_id = vformula_id;
1025
1026 BEGIN
1027
1028 /* Initialize the input and output qtys */
1029 x_product_qty := 0;
1030 x_ingredient_qty := 0;
1031 x_return_status := 'S';
1032
1033 -- NPD Conv. Get formula owner orgn. id
1034 OPEN get_formula_owner_orgn_id(formula_id);
1035 FETCH get_formula_owner_orgn_id INTO l_orgn_id;
1036 CLOSE get_formula_owner_orgn_id;
1037
1038 /* if the x_uom value is not paased as input parameter */
1039 /* then we use the GMD:Yield type std um as the formula uom */
1040 IF (x_uom IS NULL) THEN
1041 -- l_um_type := fnd_profile.value('FM_YIELD_TYPE');
1042
1043 -- NPD Conv. Use the new detch proc. to get FM_YIELD_TYPE
1044 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => l_orgn_id ,
1045 P_parm_name => 'FM_YIELD_TYPE' ,
1046 P_parm_value => l_um_type ,
1047 X_return_status => l_return_status );
1048
1049 IF (l_um_type IS NOT NULL) then
1050 OPEN get_sy_std_um(l_um_type);
1051 FETCH get_sy_std_um INTO x_uom;
1052 IF get_sy_std_um%NOTFOUND then
1053 x_uom := NULL;
1054 End if;
1055 CLOSE get_sy_std_um;
1056 END IF;
1057 END IF;
1058
1059 /* If the GMD:Yield type is not defined use th emain product UOM */
1060 IF (x_uom IS NULL) THEN /* get the main prod um */
1061 /* Determine the main product UOM */
1062 OPEN prod_um_cur(formula_id);
1063 FETCH prod_um_cur INTO x_uom;
1064 IF prod_um_cur%NOTFOUND then
1065 x_uom := NULL;
1066 End if;
1067 CLOSE prod_um_cur;
1068 END IF;
1069
1070 /* If the x_uom is yet NULL then its an error */
1071 IF (x_uom IS NULL) Then
1072 FND_MESSAGE.SET_NAME('GMD', 'FM_SCALE_BAD_YIELD_TYPE');
1073 FND_MSG_PUB.Add;
1074 x_return_status := 'E';
1075 END IF;
1076
1077 /* Bug 1683702 - Thomas Daniel */
1078 FOR l_rec IN Get_formula_lines (formula_id) LOOP
1079 l_count := l_count + 1;
1080 l_scale_tab(l_count).line_no := l_rec.line_no;
1081 l_scale_tab(l_count).line_type := l_rec.line_type;
1082 l_scale_tab(l_count).inventory_item_id := l_rec.inventory_item_id; -- NPD Conv.
1083 l_scale_tab(l_count).qty := l_rec.qty;
1084 l_scale_tab(l_count).detail_uom := l_rec.detail_uom; -- NPD Conv.
1085 l_scale_tab(l_count).scale_type := l_rec.scale_type;
1086 l_scale_tab(l_count).contribute_yield_ind := l_rec.contribute_yield_ind;
1087 l_scale_tab(l_count).scale_multiple := l_rec.scale_multiple;
1088 l_scale_tab(l_count).scale_rounding_variance := l_rec.scale_rounding_variance;
1089 l_scale_tab(l_count).rounding_direction := l_rec.rounding_direction;
1090 END LOOP; /* FOR l_rec IN Get_formula_lines (l_formula_id) */
1091
1092 IF NVL(p_scale_factor, 1) = 1 THEN
1093 l_material_tab := l_scale_tab;
1094 ELSE
1098 ,p_scale_factor => p_scale_factor
1095 -- NPD Conv. Pass orgn_id to the scale proc.
1096 GMD_COMMON_SCALE.scale (p_scale_tab => l_scale_tab
1097 ,p_orgn_id => l_orgn_id
1099 ,p_primaries => p_primaries
1100 ,x_scale_tab => l_material_tab
1101 ,x_return_status => x_return_status);
1102 IF x_return_status <> FND_API.g_ret_sts_success THEN
1103 RAISE FND_API.g_exc_error;
1104 END IF;
1105 END IF; /* IF NVL(p_scale_factor, 1) = 1 */
1106
1107 /* Calculate the ingredient total quantities */
1108 FOR i IN 1..l_material_tab.COUNT LOOP
1109 IF l_material_tab(i).line_type = -1 AND
1110 /*Bug 2880618 - Thomas Daniel */
1111 /*Need to compute only for ingredients contributing to yield */
1112 l_material_tab(i).contribute_yield_ind = 'Y' THEN
1113 -- NPD Conv.
1114 l_temp_qty := INV_CONVERT.inv_um_convert( item_id => l_material_tab(i).inventory_item_id
1115 ,precision => 5
1116 ,from_quantity => l_material_tab(i).qty
1117 ,from_unit => l_material_tab(i).detail_uom
1118 ,to_unit => x_uom
1119 ,from_name => NULL
1120 ,to_name => NULL);
1121 IF l_temp_qty < 0 THEN
1122 x_ingredient_qty := NULL;
1123 x_return_status := 'Q';
1124 FND_MESSAGE.SET_NAME('GMD', 'GMD_CANNOT_CALC_TOQ');
1125 FND_MSG_PUB.Add;
1126 Exit;
1127 ELSE
1128 X_ingredient_qty := X_ingredient_qty + l_temp_qty;
1129 END IF;
1130 END IF;
1131 END LOOP;
1132
1133 /* Now let us calculate the product total quantities */
1134 FOR i IN 1..l_material_tab.COUNT LOOP
1135 IF l_material_tab(i).line_type IN (1,2) THEN
1136 -- NPD Conv.
1137 l_temp_qty := INV_CONVERT.inv_um_convert( item_id => l_material_tab(i).inventory_item_id
1138 ,precision => 5
1139 ,from_quantity => l_material_tab(i).qty
1140 ,from_unit => l_material_tab(i).detail_uom
1141 ,to_unit => x_uom
1142 ,from_name => NULL
1143 ,to_name => NULL);
1144 IF l_temp_qty < 0 THEN
1145 x_product_qty := NULL;
1146 x_return_status := 'Q';
1147 FND_MESSAGE.SET_NAME('GMD', 'GMD_CANNOT_CALC_TOQ');
1148 FND_MSG_PUB.Add;
1149 Exit;
1150 ELSE
1151 x_product_qty := x_product_qty + l_temp_qty;
1152 END IF;
1153 END IF;
1154 END LOOP;
1155
1156 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
1157 P_data => x_msg_data);
1158
1159 EXCEPTION
1160 WHEN FND_API.g_exc_error THEN
1161 x_return_status := 'Q';
1162 WHEN Others THEN
1163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1164 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1165 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1166 FND_MSG_PUB.ADD;
1167 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
1168 P_data => x_msg_data);
1169
1170 END Calculate_Total_Qty;
1171
1172 /* ==================================================================== */
1173 /* Function */
1174 /* Get_Routing_Scale_Factor */
1175 /* */
1176 /* Description - */
1177 /* Routing qtys are scaled to a value proportional to the Recipe */
1178 /* Total Output Qty. This scale factor is the routing scale factor. */
1179 /* Return value : x_Routing_Scale_Factor NUMBER */
1180 /* */
1181 /* */
1182 /* */
1183 /* ==================================================================== */
1184 FUNCTION Get_Routing_Scale_Factor(vRecipe_id IN NUMBER,
1185 x_return_status OUT NOCOPY VARCHAR2,
1186 vFormula_Id IN NUMBER,
1187 vRouting_Id IN NUMBER)
1188 RETURN NUMBER IS
1189
1190 l_recipe_qty NUMBER ;
1191 x_Routing_Scale_factor NUMBER := 1 ;
1192
1193 CURSOR Cur_get_recipe IS
1194 SELECT formula_id, routing_id, calculate_step_quantity
1195 FROM gmd_recipes_b
1196 WHERE recipe_id = vRecipe_Id;
1197
1198 Cursor get_routrecipe_qty(pFormula_Id NUMBER, pRouting_Id NUMBER) IS
1199 Select rout.routing_qty rout_qty,
1200 form.total_output_qty recipe_qty,
1201 rout.routing_uom rout_uom,
1202 form.formula_id formula_id,
1203 form.yield_uom yield_typ_uom
1204 from fm_form_mst_b form,
1205 gmd_routings_b rout
1209 l_formula_id NUMBER;
1206 where form.formula_id = pformula_id and
1207 rout.routing_id = prouting_id ;
1208
1210 l_routing_id NUMBER;
1211 l_calculate_step_qty NUMBER(5);
1212 l_product_qty NUMBER;
1213 l_ingredient_qty NUMBER;
1214 l_uom mtl_units_of_measure.unit_of_measure%TYPE;
1215 l_return_status VARCHAR2(1);
1216 l_msg_count NUMBER;
1217 l_msg_data VARCHAR2(2000);
1218 BEGIN
1219 X_return_status := FND_API.g_ret_sts_success;
1220 IF vRecipe_Id IS NOT NULL THEN
1221 OPEN Cur_get_recipe;
1222 FETCH Cur_get_recipe INTO l_formula_id, l_routing_id, l_calculate_step_qty;
1223 CLOSE Cur_get_recipe;
1224 ELSE
1225 l_formula_id := vFormula_Id;
1226 l_routing_id := vRouting_Id;
1227 l_calculate_step_qty := 0;
1228 END IF;
1229 FOR rout_rec IN get_routrecipe_qty(l_formula_Id, l_routing_Id) LOOP
1230 IF(l_calculate_step_qty <> 1) THEN
1231 IF (rout_rec.rout_uom <> rout_rec.yield_typ_uom) THEN
1232 l_recipe_qty := INV_CONVERT.inv_um_convert(item_id => 0
1233 ,precision => 5
1234 ,from_quantity => rout_rec.recipe_qty
1235 ,from_unit => rout_rec.yield_typ_uom
1236 ,to_unit => rout_rec.rout_uom
1237 ,from_name => NULL
1238 ,to_name => NULL);
1239 /*This implies that the recipe qty uom and the routing qty uom are
1240 not of the same uom class */
1241 /*so lets recalculate the recipe qty based on the routing uom */
1242 IF l_recipe_qty < 0 THEN
1243 l_uom := rout_rec.rout_uom;
1244 Calculate_Total_Qty (Formula_Id => rout_rec.formula_id
1245 ,x_product_qty => l_product_qty
1246 ,x_ingredient_qty => l_ingredient_qty
1247 ,x_uom => l_uom
1248 ,x_return_status => l_return_status
1249 ,x_msg_count => l_msg_count
1250 ,x_msg_data => l_msg_data);
1251
1252 /*Bug 2722961 - Thomas Daniel */
1253 /*Changed the checking from return_status to l_product_qty */
1254 /*as the calculate_total_qty routine was passing back the */
1255 /*return status as 'Q' if the ingredient conversions were */
1256 /*not setup, though the product conversions have been setup */
1257 IF l_product_qty > 0 THEN
1258 l_recipe_qty := l_product_qty;
1259 ELSE
1260 FND_MSG_PUB.INITIALIZE;
1261 FND_MESSAGE.SET_NAME ('GMD', 'GMD_ERR_CALC_ROUT_FACT');
1262 FND_MSG_PUB.add;
1263 x_return_status := 'W';
1264 RETURN 1;
1265 END IF;
1266 END IF;
1267 ELSE
1268 l_recipe_qty := rout_rec.recipe_qty;
1269 END IF;
1270 x_Routing_scale_factor := l_recipe_qty / rout_rec.rout_qty;
1271 ELSE
1272 /* ASQC flag is ON it implies that the recipe step qty have the rout qty
1273 factor is already incorporated */
1274 x_Routing_scale_factor := 1;
1275 END IF;
1276 END LOOP;
1277
1278 return x_Routing_Scale_factor;
1279
1280 END Get_Routing_Scale_Factor;
1281
1282
1283 /* =============================================================== */
1284 /* Procedure: */
1285 /* Calculate_Charges */
1286 /* */
1287 /* DESCRIPTION: */
1288 /* */
1289 /* Return E if no status code exists */
1290 /* Return S if status code is found */
1291 /* */
1292 /* Procedure returns the all Charges for a routing step */
1293 /* History : */
1294 /* Shyam 03/29/2002 Bug # 2284643: Added logic to scale the */
1295 /* routing step qty if the total routing step */
1296 /* is not equal to the Recipe qty */
1297 /* Shyam 04/20/2002 Create a new organization based capacity */
1298 /* cursor : Bug # 2272885 */
1299 /* =============================================================== */
1300
1301 PROCEDURE Calculate_Charges
1302 ( Batch_id IN NUMBER ,
1303 Recipe_id IN NUMBER ,
1304 Routing_id IN NUMBER ,
1305 VR_qty IN NUMBER ,
1306 Tolerance IN NUMBER ,
1307 Orgn_id IN NUMBER ,
1308 x_charge_tbl OUT NOCOPY charge_tbl ,
1309 x_return_status OUT NOCOPY VARCHAR2
1310 ) IS
1311
1312 /* Defining all variables */
1313 l_row NUMBER := 0 ;
1314 l_rout_scale_factor NUMBER := 1 ;
1318 /* get capacities for resources that belong any orgn (generic) */
1315 l_step_tbl GMD_AUTO_STEP_CALC.step_rec_tbl;
1316 l_return_status VARCHAR2(1);
1317
1319 CURSOR Step_qty_Cur(vRouting_id NUMBER) IS
1320 SELECT dtl.routingStep_id ,
1321 dtl.step_qty qty ,
1322 opr.process_qty_uom step_um
1323 FROM fm_rout_dtl dtl ,
1324 gmd_operations_b opr
1325 WHERE dtl.oprn_id = opr.oprn_id AND
1326 dtl.routing_id = vRouting_id
1327 ORDER BY dtl.routingStep_id;
1328
1329 CURSOR Get_Recipe_Step_Details(vRecipe_id NUMBER, vRoutingStep_id NUMBER) IS
1330 SELECT step_qty, mass_qty, mass_std_uom, volume_qty, volume_std_uom
1331 FROM gmd_recipe_routing_steps
1332 WHERE recipe_id = vRecipe_id AND
1333 routingstep_id = vRoutingStep_id;
1334
1335 BEGIN
1336 IF (l_debug = 'Y') THEN
1337 gmd_debug.log_initialize('CalcCharges');
1338 END IF;
1339 /* Initialize variables */
1340 /* Get the Routing Scale Factor */
1341 IF (l_debug = 'Y') THEN
1342 gmd_debug.put_line('In calc_charges proc initializing the variables ');
1343 END IF;
1344 x_return_status := FND_API.g_ret_sts_success;
1345
1346 /* Get the Routing Scale Factor */
1347 IF (l_debug = 'Y') THEN
1348 gmd_debug.put_line('In calc_charges proc - before calling routing scale fact '
1349 ||Recipe_id);
1350 END IF;
1351 l_rout_scale_factor := Get_Routing_Scale_Factor(vRecipe_Id => Recipe_id,
1352 x_return_status => l_return_status);
1353
1354
1355 IF (l_debug = 'Y') THEN
1356 gmd_debug.put_line('In calc_charges proc - after calling routing scale fact '
1357 ||l_rout_scale_factor||' '||l_return_status);
1358 END IF;
1359
1360 /* Step1: Get all step details based on the routing_id */
1361 FOR Step_qty_rec IN Step_qty_cur(Routing_id) LOOP
1362
1363 /* Get the routing step value */
1364 l_row := l_row + 1;
1365 l_step_tbl(l_row).step_id := Step_qty_rec.routingstep_id;
1366 l_step_tbl(l_row).step_qty := Step_qty_rec.qty * NVL(l_rout_scale_factor, 1);
1367 l_step_tbl(l_row).step_qty_uom := Step_qty_rec.step_um;
1368
1369 IF (l_debug = 'Y') THEN
1370 gmd_debug.put_line('In CalcCharges Proc - the step qty and its uom is '
1371 ||l_step_tbl(l_row).step_qty||' '||l_step_tbl(l_row).step_qty_uom);
1372 END IF;
1373
1374 /* At Recipe_Routing_Step_level, we store the step qty in three uom */
1375 /* 1) Step qty in its operating uom */
1376 /* 2) As, mass qty in mass reference uom */
1377 /* 3) As, volume qty in volume reference uom */
1378
1379 /* Check if the step qty has been overridden at recipe level */
1380 /* Table gmd_recipe_routing_steps stores step qtys that are overridden */
1381
1382 /* This cursor should return only one row */
1383 FOR Get_Recipe_Step_rec IN
1384 Get_Recipe_Step_Details(Recipe_id, Step_qty_rec.routingStep_id) LOOP
1385
1386 /* Get the recipe routing step qty */
1387 /* Use the overridden step qty */
1388 l_step_tbl(l_row).step_qty := Get_Recipe_Step_rec.step_qty;
1389 l_step_tbl(l_row).step_mass_qty := Get_Recipe_Step_rec.mass_qty;
1390 l_step_tbl(l_row).step_mass_uom := Get_Recipe_Step_rec.mass_std_uom;
1391 l_step_tbl(l_row).step_vol_qty := Get_Recipe_Step_rec.volume_qty;
1392 l_step_tbl(l_row).step_vol_uom := Get_Recipe_Step_rec.volume_std_uom;
1393
1394 IF (l_debug = 'Y') THEN
1395 gmd_debug.put_line('In CalcCharges Proc - the override step qty and its uom is '
1396 ||l_step_tbl(l_row).step_qty||' '||l_step_tbl(l_row).step_qty_uom);
1397 END IF;
1398
1399 END LOOP; /* End loop when routing step details exists at recipe level */
1400
1401 END LOOP;
1402
1403 /* We have the step quantities now lets calculate the charges */
1404 IF l_step_tbl.COUNT > 0 THEN
1405 GMD_COMMON_VAL.calculate_step_charges (p_recipe_id => Recipe_id
1406 ,p_tolerance => tolerance
1407 ,p_orgn_id => Orgn_id
1408 ,p_step_tbl => l_step_tbl
1409 ,x_charge_tbl => x_charge_tbl
1410 ,x_return_status => x_return_status);
1411 IF (l_debug = 'Y') THEN
1412 gmd_debug.put_line('In CalcCharges Proc - after calling Calc_step_chr '
1413 ||x_return_status);
1414 END IF;
1415 END IF; /* If their are rows in the step table */
1416
1417 END Calculate_Charges;
1418
1419
1420 /* =============================================================== */
1421 /* Procedure: */
1422 /* Calculate_Step_Charges */
1423 /* */
1424 /* DESCRIPTION: */
1425 /* */
1426 /* Return E if no status code exists */
1427 /* Return S if status code is found */
1431 /* Thomas 07/23/2002 Bug # 1683702: Added this new procedure */
1428 /* */
1429 /* Procedure returns the all Charges for a routing step */
1430 /* History : */
1432 /* To calculate the charges off the step */
1433 /* quantities passed in */
1434 /* RajaSekhar 02/04/2003 BUG#2365583 Added code to represent */
1435 /* whether the number of charges is defaulted */
1436 /* or calculated properly. */
1437 /* Kalyani 02/06/2006 Bug#5258672 Store max capacity in res UOM */
1438 /* =============================================================== */
1439
1440 PROCEDURE Calculate_Step_Charges
1441 ( P_recipe_id IN NUMBER ,
1442 P_tolerance IN NUMBER ,
1443 P_orgn_id IN NUMBER ,
1444 P_step_tbl IN GMD_AUTO_STEP_CALC.step_rec_tbl,
1445 x_charge_tbl OUT NOCOPY charge_tbl ,
1446 x_return_status OUT NOCOPY VARCHAR2
1447 ) IS
1448
1449 /* Defining all variables */
1450 l_charge INTEGER := 1 ;
1451 l_rough_charge NUMBER ;
1452 l_step_qty_uom VARCHAR2(4) ;
1453 l_max_capacity NUMBER ;
1454 l_max_capacity_old NUMBER ;
1455 l_capacity_uom VARCHAR2(4) ;
1456 l_row NUMBER := 1 ;
1457 m_counter NUMBER := 1 ;
1458 l_step_qty NUMBER ;
1459 l_def_charge VARCHAR2(1) ; --BUG#2365583 RajaSekhar
1460
1461 CURSOR Capacity_cur(vRecipe_id NUMBER, vRoutingstep_id NUMBER,
1462 vOrgn_id NUMBER) IS
1463 /*Bug 3679608 - Thomas Daniel */
1464 /*Modified the select statement to consider the generic resource only */
1465 /*if there are no plant specific overrides */
1466 SELECT MIN( NVL(orgnres.max_capacity, crres.max_capacity) ) max_cap,
1467 crres.capacity_um capacity_um
1468 FROM (SELECT resources, max_capacity, capacity_um, capacity_constraint
1469 FROM cr_rsrc_mst_b m
1470 WHERE capacity_constraint = 1
1471 AND NOT EXISTS (SELECT 1
1472 FROM cr_rsrc_dtl d
1473 WHERE d.organization_id = vOrgn_id
1474 AND d.resources = m.resources)
1475 UNION
1476 SELECT resources, max_capacity, capacity_um, capacity_constraint
1477 FROM cr_rsrc_dtl
1478 WHERE organization_id = vOrgn_id
1479 AND capacity_constraint = 1 ) crres ,
1480 (SELECT max_capacity, resources, routingstep_id
1481 FROM gmd_recipe_orgn_resources
1482 WHERE recipe_id = vRecipe_id
1483 AND organization_id = vOrgn_id) orgnres ,
1484 (SELECT oprn_id, routingStep_id
1485 FROM fm_rout_dtl
1486 WHERE routingstep_id = vRoutingstep_id ) dtl ,
1487 gmd_operation_resources res ,
1488 gmd_operation_activities act ,
1489 gmd_operations_b opr
1490 WHERE crres.resources = res.resources AND
1491 dtl.oprn_id = opr.oprn_id AND
1492 opr.oprn_id = act.oprn_id AND
1493 act.oprn_line_id = res.oprn_line_id AND
1494 (orgnres.routingstep_id IS NULL OR
1495 dtl.routingstep_id = orgnres.routingstep_id ) AND
1496 res.resources = orgnres.resources(+)
1497 GROUP BY crres.capacity_um;
1498
1499 BEGIN
1500 IF (l_debug = 'Y') THEN
1501 gmd_debug.put_line('In CalcCharges Step Proc - ');
1502 END IF;
1503 /* Initialize variables */
1504 x_return_status := FND_API.g_ret_sts_success;
1505 FOR i IN 1..P_step_tbl.COUNT LOOP
1506 /*Bug 3669132 - Thomas Daniel */
1507 /*Initialize default charge variable to identify if the code has entered the following for loop */
1508 l_def_charge := NULL;
1509 /* Let us fetch the min of the max capacities for all the resources belonging */
1510 /* to the current routing step and recipe */
1511 /* Step1: Get all resources details based on the routingStep_id */
1512 FOR Capacity_rec IN Capacity_cur(P_recipe_id,
1513 P_step_tbl(i).step_id,
1514 P_orgn_id) LOOP
1515 l_charge := 1;
1516
1517 /* Get the routing step value */
1518 l_step_qty := P_step_tbl(i).step_qty;
1519 l_step_qty_uom := P_step_tbl(i).step_qty_uom;
1520
1521 l_capacity_uom := Capacity_rec.capacity_um;
1522
1523 /* The min of max_capacity needs to derived for a step */
1524 /* Please remember if the max capacity is -ve */
1525 /* it probably means that the capcity conversion to step qty um was not set */
1526 l_max_capacity := INV_CONVERT.inv_um_convert(item_id => 0
1527 ,precision => 5
1531 ,from_name => NULL
1528 ,from_quantity => nvl(Capacity_rec.max_cap,0)
1529 ,from_unit => Capacity_rec.capacity_um
1530 ,to_unit => l_step_qty_uom
1532 ,to_name => NULL);
1533
1534 IF (l_debug = 'Y') THEN
1535 gmd_debug.put_line('In CalcCharges Step Proc - 1. the max cap and its uom '
1536 ||l_max_capacity||' ' ||l_capacity_uom);
1537 END IF;
1538
1539 IF (l_max_capacity < 0) THEN
1540 /* Set a message in our stack : the capacity conv has not occurred */
1541 x_return_status := 'E';
1542 FND_MESSAGE.SET_NAME('GMD', 'GMD_CANNOT_CONV_CAPACITY_UOM');
1543 FND_MSG_PUB.Add;
1544 END IF; /* when max cap is < 0 */
1545
1546 /* Get the step qty : */
1547 /* If the max capacity is yet -ve its becoz none of the capcity uom where */
1548 /* convertible to the process_um or step operation um */
1549
1550 /* Test if the capacity UOM can be converted into the Mass Qty UOM */
1551 /* It might be possible that the capacity uom is not convertible to the
1552 /* process_um but convertible to the mass_ref_um */
1553 IF (l_max_capacity < 0) THEN
1554 l_step_qty := P_step_tbl(i).step_mass_qty;
1555 l_step_qty_uom := P_step_tbl(i).step_mass_uom;
1556 l_max_capacity := INV_CONVERT.inv_um_convert(item_id => 0
1557 ,precision => 5
1558 ,from_quantity => Capacity_rec.max_cap
1559 ,from_unit => l_capacity_uom
1560 ,to_unit => l_step_qty_uom
1561 ,from_name => NULL
1562 ,to_name => NULL);
1563 IF (l_debug = 'Y') THEN
1564 gmd_debug.put_line('In CalcCharges Step Proc - 2. the max cap and its uom '
1565 ||l_max_capacity||' ' ||l_capacity_uom);
1566 END IF;
1567 END IF; /* when max cap is < 0 */
1568
1569 /* Test if the capacity UOM can be converted into the Volume Qty UOM */
1570 /* It might be possible that the capacity uom is not convertible to the
1571 /* process_um and mass_um but convertible to the volume_ref_um */
1572 IF (l_max_capacity < 0) THEN
1573 l_step_qty := P_step_tbl(i).step_vol_qty;
1574 l_step_qty_uom := P_step_tbl(i).step_vol_uom;
1575 l_max_capacity := INV_CONVERT.inv_um_convert(item_id => 0
1576 ,precision => 5
1577 ,from_quantity => Capacity_rec.max_cap
1578 ,from_unit => l_capacity_uom
1579 ,to_unit => l_step_qty_uom
1580 ,from_name => NULL
1581 ,to_name => NULL);
1582
1583 IF (l_debug = 'Y') THEN
1584 gmd_debug.put_line('In CalcCharges Step Proc - 3. the max cap and its uom '
1585 ||l_max_capacity||' ' ||l_capacity_uom);
1586 END IF;
1587 END IF; /* when max cap is < 0 */
1588
1589 IF (l_debug = 'Y') THEN
1590 gmd_debug.put_line('In CalcCharges Step Proc - 4. the max cap and step_qty '
1591 ||l_max_capacity||' ' ||l_step_qty);
1592 END IF;
1593 /* Calculations after step qty and UOM is found */
1594 IF ((l_step_qty > 0) AND (l_max_capacity > 0)) THEN
1595 /* If the remainder after dividing the step quantity by the
1596 l_max_capacity is greater than tolerance then we round up the
1597 value to the next interger else we truncate to an integer smaller than
1598 the ratio value
1599 */
1600 /* E.g if the step_qty is 110 and the l_max_capacity = 100
1601 the l_rough_charge is 1.1 and the remainder is 10. If the tolerance was
1602 15 (i.e > 10) then the charge would = 1.0 . However, if the tolerance is
1603 5 (i.e < 10) the charge would be = 2.0
1604 */
1605 l_rough_charge := l_step_qty /l_max_capacity;
1606
1607 IF ( MOD(l_step_qty, l_max_capacity) > p_tolerance ) THEN
1608 l_charge := CEIL(l_rough_charge);
1609 ELSE
1610 l_charge := TRUNC(l_rough_charge);
1611 END IF; /* when ratio is greater than tolerance */
1612 l_def_charge :='N'; --BUG#2365583 RajaSekhar
1613 ELSE
1614 l_charge := 1;
1615 l_def_charge :='Y'; --BUG#2365583 RajaSekhar
1616 END IF; /* End condition when step qty > 0 */
1617
1618 /* Resource usage is below capacity. Bug 2183650 */
1619 IF (l_charge <= 0 OR l_charge IS NULL) THEN
1620 l_charge := 1;
1621 END IF; /* when charge is 0 */
1622
1623 /* If Resource capacity is -ve - probably due to non UOM conv set it to NUll */
1624 IF (l_max_capacity < 0) THEN
1625 l_max_capacity := NULL;
1626 END IF;
1627
1628 /* Associate with charges and routing step details with out table */
1632 /* Eliminate duplicate routingStepid rows */
1629 /* For each RoutingStep_id check for the max charge value */
1630 /* This condition occurs when each set has more than one resource with diff cap UOM */
1631
1633 /* For each RoutingStep_id check for the max charge value and remove the
1634 one with lower charge */
1635 /* This condition occurs when each set has more than one resource with diff cap UOM */
1636
1637 IF (m_counter = 1) THEN
1638 x_charge_tbl(l_row).def_charge := l_def_charge; --BUG#2365583 RajaSekhar
1639 x_charge_tbl(l_row).charge := l_charge;
1640 x_charge_tbl(l_row).routingstep_id := P_step_tbl(i).step_id ;
1641 x_charge_tbl(l_row).max_capacity := l_max_capacity;
1642 --x_charge_tbl(l_row).capacity_uom := l_capacity_uom;
1643 /* bug # 2385711 - The uom to be returned is the step qty uom
1644 and not the capacity uom */
1645 x_charge_tbl(l_row).capacity_uom := P_step_tbl(i).step_qty_uom;
1646 m_counter := m_counter + 1;
1647 -- Bug#5258672 Store the capacity value in resource UOM
1648 x_charge_tbl(l_row).Max_Capacity_In_Res_UOM := Capacity_rec.max_cap;
1649 END IF;
1650
1651 IF (x_charge_tbl(l_row).routingstep_id = P_step_tbl(i).Step_id) THEN
1652 IF (x_charge_tbl(l_row).charge < l_charge) THEN
1653 x_charge_tbl(l_row).def_charge := l_def_charge; --BUG#2365583 RajaSekhar
1654 x_charge_tbl(l_row).charge := l_charge;
1655 x_charge_tbl(l_row).routingstep_id := P_step_tbl(i).step_id ;
1656 x_charge_tbl(l_row).max_capacity := l_max_capacity;
1657 --x_charge_tbl(l_row).capacity_uom := l_capacity_uom;
1658 /* bug # 2385711 - The uom to be returned is the step qty uom
1659 and not the capacity uom */
1660 x_charge_tbl(l_row).capacity_uom := P_step_tbl(i).step_qty_uom;
1661 -- Bug#5258672 Store the capacity value in resource UOM
1662 x_charge_tbl(l_row).Max_Capacity_In_Res_UOM := Capacity_rec.max_cap;
1663 END IF; /* when row in previous row is different from current values */
1664 ELSE
1665 l_row := l_row + 1;
1666 x_charge_tbl(l_row).def_charge := l_def_charge; --BUG#2365583 RajaSekhar
1667 x_charge_tbl(l_row).charge := l_charge;
1668 x_charge_tbl(l_row).routingstep_id := P_step_tbl(i).Step_id;
1669 x_charge_tbl(l_row).max_capacity := l_max_capacity;
1670 -- x_charge_tbl(l_row).capacity_uom := l_capacity_uom;
1671 /* bug # 2385711 - The uom to be returned is the step qty uom
1672 and not the capacity uom */
1673 x_charge_tbl(l_row).capacity_uom := P_step_tbl(i).step_qty_uom;
1674 -- Bug#5258672 Store the capacity value in resource UOM
1675 x_charge_tbl(l_row).Max_Capacity_In_Res_UOM := Capacity_rec.max_cap;
1676 END IF; /* When routingStep id are same */
1677
1678 END LOOP; /* End loop for main generic cursor */
1679 /*Bug 3669132 - Thomas Daniel */
1680 /*Added the following code to set the charge as 1 if no capacity constraint */
1681 /*resources are found for this routing step */
1682 IF l_def_charge IS NULL THEN
1683 /*Bug3679608 - Thomas Daniel*/
1684 /*Moved the row incremented up */
1685 IF m_counter <> 1 THEN
1686 l_row := l_row + 1;
1687 ELSE
1688 m_counter := m_counter + 1;
1689 END IF;
1690 x_charge_tbl(l_row).def_charge := 'Y';
1691 x_charge_tbl(l_row).charge := 1;
1692 x_charge_tbl(l_row).routingstep_id := P_step_tbl(i).Step_id;
1693 x_charge_tbl(l_row).max_capacity := NULL;
1694 x_charge_tbl(l_row).capacity_uom := P_step_tbl(i).step_qty_uom;
1695 END IF;
1696 END LOOP; /* FOR i IN 1..Step_tbl.COUNT */
1697
1698 END Calculate_Step_Charges;
1699
1700
1701
1702 FUNCTION UPDATE_ALLOWED(Entity VARCHAR2
1703 ,Entity_id NUMBER
1704 ,Update_Column_Name VARCHAR2 Default Null)
1705 RETURN BOOLEAN IS
1706 l_meaning GMD_STATUS.Meaning%TYPE ;
1707 l_desc GMD_STATUS.Description%TYPE ;
1708 l_return_status VARCHAR2(1);
1709 l_bool BOOLEAN := TRUE;
1710 l_status_code GMD_STATUS.Status_Code%TYPE;
1711 l_delete_mark NUMBER := 0;
1712
1713 l_resp_id NUMBER(15) DEFAULT FND_PROFILE.VALUE('RESP_ID');
1714 l_owner_orgn_id GMD_RECIPES_B.owner_organization_id%TYPE;
1715 l_dummy NUMBER := 0;
1716
1717 Cursor Check_recipe_orgn_access(vresp_id NUMBER, vOwner_orgn_id NUMBER) IS
1718 SELECT 1
1719 FROM org_access_view
1720 WHERE responsibility_id = vresp_id
1721 AND organization_id = vOwner_orgn_id;
1722
1723 BEGIN
1724 IF (l_debug = 'Y') THEN
1725 gmd_debug.put_line('In GMD_COMMON_VAL.UPDATE_ALLOWED - '
1726 ||' Entity = '||Entity||' and Entity id = '
1727 ||Entity_id);
1728 END IF;
1729 IF (Entity = 'FORMULA') THEN
1730 SELECT delete_mark, formula_status
1731 INTO l_delete_mark, l_status_code
1732 FROM fm_form_mst
1733 WHERE formula_id = Entity_id;
1734 ELSIF (Entity = 'RECIPE') THEN
1735
1739 WHERE recipe_id = Entity_id;
1736 SELECT delete_mark, recipe_status, owner_organization_id
1737 INTO l_delete_mark, l_status_code, l_owner_orgn_id
1738 FROM gmd_recipes_b
1740
1741 /* Check if user has access to this Recipe orgn */
1742 OPEN Check_recipe_orgn_access(l_resp_id, l_Owner_orgn_id);
1743 FETCH Check_recipe_orgn_access INTO l_dummy;
1744 IF Check_recipe_orgn_access%NOTFOUND THEN
1745 CLOSE Check_recipe_orgn_access;
1746 Return FALSE;
1747 END IF;
1748 CLOSE Check_recipe_orgn_access;
1749
1750 ELSIF (Entity = 'ROUTING') THEN
1751
1752 SELECT delete_mark, routing_status
1753 INTO l_delete_mark, l_status_code
1754 FROM fm_rout_hdr
1755 WHERE routing_id = Entity_id;
1756 ELSIF (Entity = 'OPERATION') THEN
1757 SELECT delete_mark, operation_status
1758 INTO l_delete_mark, l_status_code
1759 FROM gmd_operations
1760 WHERE oprn_id = Entity_id;
1761 ELSIF (Entity = 'VALIDITY') THEN
1762 SELECT delete_mark, validity_rule_status
1763 INTO l_delete_mark, l_status_code
1764 FROM gmd_recipe_validity_rules
1765 WHERE recipe_validity_rule_id = Entity_id;
1766 END IF;
1767
1768 Get_Status
1769 ( Status_code => l_status_code ,
1770 Meaning => l_meaning ,
1771 Description => l_desc ,
1772 x_return_status => l_return_status
1773 );
1774
1775 -- Added this condition to allow update of end dates for
1776 -- frozen Operations, Routings and Validity Rules
1777 IF ((l_status_code between 900 and 999)
1778 AND (Upper(Update_Column_Name) like '%END_DATE%')) THEN
1779 Return TRUE;
1780 ELSIF ((l_status_code between 200 and 299) OR (l_status_code >= 800) OR
1781 (l_status_code between 500 and 599) OR (l_delete_mark = 1)) THEN
1782 Return FALSE;
1783 ELSE
1784 Return TRUE;
1785 END IF;
1786
1787 return false;
1788 EXCEPTION
1789 WHEN NO_DATA_FOUND THEN
1790 RETURN TRUE;
1791 END UPDATE_ALLOWED;
1792
1793
1794 FUNCTION VERSION_CONTROL_STATE(Entity VARCHAR2, Entity_id NUMBER) RETURN VARCHAR2 IS
1795
1796 l_state VARCHAR2(32) := 'N';
1797 l_status VARCHAR2(30);
1798 l_version_enabled VARCHAR2(1) := 'N';
1799
1800 TYPE Status_ref_cur IS REF CURSOR;
1801 Status_cur Status_ref_cur;
1802
1803 CURSOR get_formula_owner_orgn_id(vformula_id NUMBER) IS
1804 SELECT owner_organization_id
1805 FROM fm_form_mst_b
1806 WHERE formula_id = vformula_id;
1807 CURSOR get_recipe_owner_orgn_id(vrecipe_id NUMBER) IS
1808 SELECT owner_organization_id
1809 FROM gmd_recipes_b
1810 WHERE recipe_id = vrecipe_id;
1811 CURSOR get_routing_owner_orgn_id(vrouting_id NUMBER) IS
1812 SELECT owner_organization_id
1813 FROM gmd_routings_b
1814 WHERE routing_id = vrouting_id;
1815 CURSOR get_operation_owner_orgn_id(voprn_id NUMBER) IS
1816 SELECT owner_organization_id
1817 FROM gmd_operations_b
1818 WHERE oprn_id = voprn_id;
1819
1820 CURSOR get_substitution_owner_orgn_id(vsub_id NUMBER) IS
1821 SELECT owner_organization_id
1822 FROM GMD_ITEM_SUBSTITUTION_HDR_B
1823 WHERE SUBSTITUTION_ID = vsub_id;
1824 l_orgn_id NUMBER;
1825 l_return_status VARCHAR2(10);
1826 BEGIN
1827
1828 -- Check for status that allow the version control
1829 -- e.g normally version control is set beyond
1830 -- status = 'Approved for gen use'
1831
1832 IF (Upper(Entity) = 'FORMULA') THEN
1833 OPEN get_formula_owner_orgn_id(entity_id);
1834 FETCH get_formula_owner_orgn_id INTO l_orgn_id;
1835 CLOSE get_formula_owner_orgn_id;
1836 OPEN Status_cur FOR
1837 Select f.formula_status, s.version_enabled
1838 From fm_form_mst f, gmd_status s
1839 Where f.formula_id = Entity_id
1840 And f.formula_status = s.status_code;
1841 FETCH Status_cur INTO l_status, l_version_enabled;
1842 ClOSE Status_cur;
1843
1844 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => l_orgn_id ,
1845 P_parm_name => 'GMD_FORMULA_VERSION_CONTROL',
1846 P_parm_value => l_state ,
1847 X_return_status => l_return_status );
1848
1849 IF ((l_state = 'Y') AND (l_version_enabled = 'Y')) THEN
1850 l_state := 'Y';
1851 ELSIF ((l_state = 'O') AND (l_version_enabled = 'Y')) THEN
1852 l_state := 'O';
1853 ELSE
1854 l_state := 'N';
1855 END IF;
1856
1857 ELSIF (Upper(Entity) = 'RECIPE') THEN
1858 OPEN get_recipe_owner_orgn_id(entity_id);
1859 FETCH get_recipe_owner_orgn_id INTO l_orgn_id;
1860 CLOSE get_recipe_owner_orgn_id;
1861 OPEN Status_cur FOR
1862 Select r.recipe_status, s.version_enabled
1863 From gmd_recipes r, gmd_status s
1864 Where r.recipe_id = Entity_id
1865 And r.recipe_status = s.status_code;
1866 FETCH Status_cur INTO l_status, l_version_enabled;
1867 ClOSE Status_cur;
1868
1869 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => l_orgn_id ,
1873
1870 P_parm_name => 'GMD_RECIPE_VERSION_CONTROL' ,
1871 P_parm_value => l_state ,
1872 X_return_status => l_return_status );
1874 IF ((l_state = 'Y') AND (l_version_enabled = 'Y')) THEN
1875 l_state := 'Y';
1876 ELSIF ((l_state = 'O') AND (l_version_enabled = 'Y')) THEN
1877 l_state := 'O';
1878 ELSE
1879 l_state := 'N';
1880 END IF;
1881
1882 ELSIF (Upper(Entity) = 'ROUTING') THEN
1883 OPEN get_routing_owner_orgn_id(entity_id);
1884 FETCH get_routing_owner_orgn_id INTO l_orgn_id;
1885 CLOSE get_routing_owner_orgn_id;
1886 OPEN Status_cur FOR
1887 Select r.routing_status, s.version_enabled
1888 From fm_rout_hdr r, gmd_status s
1889 Where r.routing_id = Entity_id
1890 And r.routing_status = s.status_code;
1891 FETCH Status_cur INTO l_status, l_version_enabled;
1892 ClOSE Status_cur;
1893
1894 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => l_orgn_id ,
1895 P_parm_name => 'GMD_ROUTING_VERSION_CONTROL' ,
1896 P_parm_value => l_state ,
1897 X_return_status => l_return_status );
1898
1899 IF ((l_state = 'Y') AND (l_version_enabled = 'Y')) THEN
1900 l_state := 'Y';
1901 ELSIF ((l_state = 'O') AND (l_version_enabled = 'Y')) THEN
1902 l_state := 'O';
1903 ELSE
1904 l_state := 'N';
1905 END IF;
1906
1907 ELSIF (Upper(Entity) = 'OPERATION') THEN
1908 OPEN get_operation_owner_orgn_id(entity_id);
1909 FETCH get_operation_owner_orgn_id INTO l_orgn_id;
1910 CLOSE get_operation_owner_orgn_id;
1911 OPEN Status_cur FOR
1912 Select r.operation_status, s.version_enabled
1913 From gmd_operations r, gmd_status s
1914 Where r.oprn_id = Entity_id
1915 And r.operation_status = s.status_code;
1916 FETCH Status_cur INTO l_status, l_version_enabled;
1917 ClOSE Status_cur;
1918
1919 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => l_orgn_id ,
1920 P_parm_name => 'GMD_OPERATION_VERSION_CONTROL' ,
1921 P_parm_value => l_state ,
1922 X_return_status => l_return_status );
1923
1924 /*SELECT TRIM(FND_PROFILE.VALUE('GMD_OPERATION_VERSION_CONTROL'))
1925 INTO l_state
1926 FROM sys.dual;*/
1927
1928 IF ((l_state = 'Y') AND (l_version_enabled = 'Y')) THEN
1929 l_state := 'Y';
1930 ELSIF ((l_state = 'O') AND (l_version_enabled = 'Y')) THEN
1931 l_state := 'O';
1932 ELSE
1933 l_state := 'N';
1934 END IF;
1935 ELSIF (Upper(Entity) = 'SUBSTITUTION') THEN -- Bug number 4479101
1936 OPEN get_substitution_owner_orgn_id(entity_id);
1937 FETCH get_substitution_owner_orgn_id INTO l_orgn_id;
1938 CLOSE get_substitution_owner_orgn_id;
1939
1940 OPEN Status_cur FOR
1941 Select r.substitution_status, s.version_enabled
1942 From gmd_item_substitution_hdr_b r, gmd_status s
1943 Where r.substitution_id = Entity_id
1944 And r.substitution_status = s.status_code;
1945 FETCH Status_cur INTO l_status, l_version_enabled;
1946 ClOSE Status_cur;
1947
1948 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => l_orgn_id ,
1949 P_parm_name => 'GMD_SUBS_VERSION_CONTROL' ,
1950 P_parm_value => l_state ,
1951 X_return_status => l_return_status );
1952
1953 IF ((l_state = 'Y') AND (l_version_enabled = 'Y')) THEN
1954 l_state := 'Y';
1955 ELSIF ((l_state = 'O') AND (l_version_enabled = 'Y')) THEN
1956 l_state := 'O';
1957 ELSE
1958 l_state := 'N';
1959 END IF;
1960
1961 ELSE
1962 l_state := 'N';
1963 END IF;
1964
1965 return l_state;
1966
1967 END VERSION_CONTROL_STATE;
1968
1969
1970 /*****************************************************************************
1971 * PROCEDURE
1972 * set_conc_program_Status
1973 *
1974 * DESCRIPTION
1975 * Sets the concurrent manager completion status
1976 *
1977 * INPUT PARAMETERS
1978 * p_errstat - Completion status, must be one of 'NORMAL', 'WARNING', or
1979 * 'ERROR'
1980 * p_errmsg - Completion message to be passed back
1981 *
1982 * HISTORY
1983 * 05-31-2001 Shyam Sitaraman Created
1984 *
1985 ******************************************************************************/
1986
1987 PROCEDURE set_conc_program_Status (
1988 p_errstat IN VARCHAR2,
1989 p_errmsg IN VARCHAR2
1990 )
1991 IS
1992 l_retval BOOLEAN;
1993 BEGIN
1994
1995 l_retval := fnd_concurrent.set_completion_status(p_errstat,p_errmsg);
1996
1997 END set_conc_program_Status;
1998
1999
2000 /* **********************************************************************
2001 * PROCEDURE
2002 * Run_status_update
2003 *
2004 * Parameter Input
2005 * pCalendar_code - Calendar code set in cm_cldr_dtl
2006 * pPeriod_code - Period code set in cm_cldr_dtl
2007 * pCost_mthd_code - Cost_mthd_code from cm_cldr_hdr
2008 *
2012 * p_retcode Return code to the Concurrent Manager
2009 * Parameters Output
2010 *
2011 * p_errbuf Completion message to the Concurrent Manager
2013 *
2014 *
2015 * Description
2016 *
2017 * Procedure is used by costing to update the GMD tables with frozen status.
2018 * This procedure is registered as a concurrent program
2019 * Whenever costing updates the period status in cm_cldr_dtl table
2020 * from 0 to 1 , the trigger fires and submits a request for a
2021 * concurrent job.
2022 *
2023 * History
2024 * 05/31/2001 Shyam Created
2025 * 11/14/2001 Shyam Added fm context after the cost update.
2026 * 12-FEB-2002 Shyam BUG # 2222882: Changes to Procedure GMD_RUN_STATUS_UPDATE.
2027 * The FORALL condition for BULK update was changed to
2028 * conventional FOR LOOP statement and makes update for each row.
2029 *
2030 * 12-FEB-2002 Shyam Created an NVL ststement for routing_id that is returned after
2031 * the recipe table is updated. Recipe can have null routing_ids and
2032 * returning a NULL routing_id into variable l_routing_id can cause issues.
2033 * 01-MAR-2002 Shyam Added validation for Run_status_Updtae to check if the cost method is 'Standard'
2034 * and period status is 1.
2035 * 01/16/2003 Shyam UPdate made on status that are not obsoleted or on-hold
2036 *
2037 * *********************************************************************** */
2038
2039 PROCEDURE Run_status_update( p_errbuf OUT NOCOPY VARCHAR2,
2040 p_retcode OUT NOCOPY VARCHAR2,
2041 pCalendar_code IN cm_cmpt_dtl.calendar_code%TYPE,
2042 pPeriod_code IN cm_cmpt_dtl.period_code%TYPE,
2043 pCost_mthd_code IN cm_cmpt_dtl.cost_mthd_code%TYPE) IS
2044
2045 x_return_status VARCHAR2(1) := 'S';
2046 l_Recipe_Id NUMBER;
2047 l_formula_Id NUMBER;
2048 l_routing_Id NUMBER;
2049 l_oprn_Id NUMBER;
2050 l_period_cnt NUMBER;
2051 l_cost_type NUMBER;
2052
2053
2054 TYPE VRtbl IS TABLE OF GMD_RECIPE_VALIDITY_RULES.Recipe_Validity_Rule_Id%TYPE;
2055 VRList VRtbl;
2056
2057 CURSOR FROZEN_EFF_CUR IS
2058 SELECT distinct(fmeff_id) fmeff_id from cm_cmpt_dtl
2059 WHERE Calendar_code = pCalendar_code AND
2060 Period_code = pPeriod_code AND
2061 Cost_mthd_code = pCost_mthd_code AND
2062 ROLLOVER_IND = 1;
2063
2064 CURSOR Get_Period_Status IS
2065 SELECT count(*) FROM cm_cldr_dtl
2066 WHERE Calendar_code = pCalendar_code AND
2067 Period_code = pPeriod_code AND
2068 period_status = 1;
2069
2070 CURSOR Get_Cost_type IS
2071 SELECT cost_type from cm_mthd_mst
2072 WHERE cost_mthd_code = pCost_mthd_code;
2073
2074 CURSOR Get_Recipe_id(vValidity_Rule_id NUMBER) IS
2075 SELECT recipe_id from gmd_recipe_validity_rules
2076 WHERE recipe_validity_rule_id = vValidity_Rule_id;
2077
2078 CURSOR Get_FmRout_id(vRecipe_id NUMBER) IS
2079 SELECT formula_id, routing_id From gmd_recipes_b
2080 WHERE recipe_id = vRecipe_id;
2081
2082 Standard_costing_exception EXCEPTION;
2083 Period_status_exception EXCEPTION;
2084
2085 BEGIN
2086 SAVEPOINT update_status;
2087
2088 OPEN Get_Period_Status;
2089 FETCH Get_Period_Status INTO l_period_cnt;
2090 IF ((Get_Period_Status%NOTFOUND) OR (l_period_cnt = 0)) THEN
2091 CLOSE Get_Period_Status;
2092 Raise Period_Status_exception;
2093 END IF;
2094 CLOSE Get_Period_Status;
2095
2096 OPEN Get_Cost_type;
2097 FETCH Get_Cost_type INTO l_cost_type;
2098 IF ((Get_Cost_type%NOTFOUND) OR (l_cost_type = 1)) THEN
2099 CLOSE Get_Cost_type;
2100 Raise Standard_costing_exception;
2101 END IF;
2102 CLOSE Get_Cost_Type;
2103
2104 OPEN FROZEN_EFF_CUR;
2105 FETCH FROZEN_EFF_CUR BULK COLLECT INTO VRList;
2106 CLOSE FROZEN_EFF_CUR;
2107
2108 IF (VRList.count > 0) THEN
2109
2110 FOR i IN 1 .. VRList.count LOOP
2111
2112 /* Update the VR - status field */
2113 Update gmd_recipe_validity_rules
2114 SET validity_rule_status = '900'
2115 WHERE recipe_validity_rule_id = VRList(i)
2116 AND to_number(validity_rule_status) < 800;
2117
2118 OPEN Get_Recipe_id(VRList(i));
2119 FETCH Get_Recipe_id INTO l_Recipe_id;
2120 CLOSE Get_Recipe_id;
2121
2122 /* Update the Recipe - status field */
2123 UPDATE gmd_recipes_b
2124 SET recipe_status = '900'
2125 WHERE recipe_id = l_Recipe_Id
2126 AND to_number(recipe_status) < 800;
2127
2128 OPEN Get_FmRout_id(l_recipe_id);
2129 FETCH Get_FmRout_id INTO l_formula_id, l_routing_id;
2130 CLOSE Get_FmRout_id;
2131
2135 WHERE formula_id = l_formula_id
2132 /* Update the formula and routing status */
2133 UPDATE fm_form_mst_b
2134 SET formula_status = '900'
2136 AND to_number(formula_status) < 800;
2137
2138 UPDATE gmd_routings_b
2139 SET routing_status = '900'
2140 WHERE routing_id = l_routing_id
2141 AND to_number(routing_status) < 800;
2142
2143 /* Update oprns status */
2144 IF (l_routing_id IS NOT NULL) THEN
2145 UPDATE gmd_operations_b
2146 SET operation_status = '900'
2147 WHERE oprn_id IN (SELECT oprn_id
2148 FROM fm_rout_dtl d
2149 WHERE routing_id = l_routing_id)
2150 AND to_number(operation_status) < 800;
2151 END IF;
2152 END LOOP;
2153
2154 ELSE /* when VRList count is <= 0 */
2155 p_retcode := 0;
2156 p_errbuf := NULL;
2157 set_conc_program_Status('NORMAL', 'Did not update the status on GMD tables');
2158
2159 END IF;
2160
2161 /* If o.k until here */
2162 p_retcode := 0;
2163 p_errbuf := NULL;
2164 set_conc_program_Status('NORMAL', NULL);
2165
2166 /* sets the context for formula security */
2167 gmd_p_fs_context.set_additional_attr;
2168
2169 EXCEPTION
2170 WHEN Standard_costing_exception THEN
2171 p_retcode := 3;
2172 p_errbuf := NULL;
2173 set_conc_program_Status('ERROR','Invalid cost method only standard cost methods are allowed ' );
2174 ROLLBACK to update_status;
2175 WHEN Period_status_exception THEN
2176 p_retcode := 3;
2177 p_errbuf := NULL;
2178 set_conc_program_Status('ERROR','Invalid period status only frozen periods are allowed' );
2179 ROLLBACK to update_status;
2180 WHEN OTHERS THEN
2181 p_retcode := 3;
2182 p_errbuf := NULL;
2183 set_conc_program_Status('ERROR',sqlerrm);
2184 ROLLBACK to update_status;
2185
2186 END Run_Status_Update;
2187 /* **********************************************************************
2188 * PROCEDURE
2189 * check_formula_item_access
2190 *
2191 * Parameter Input
2192 * pFormula_id Formula Id
2193 * pInventory_Item_ID Inventory Item Id
2194 * Parameters Output
2195 * X_return_status Return Status
2196 * S when the org access are true
2197 * E when known error
2198 * U when unknown error
2199 *
2200 *
2201 * Description
2202 *
2203 * Procedure is used to identify the organization's item access used
2204 * for the recipe, override organizations and validaity rules.
2205 *
2206 * History
2207 * 05-Dec-2005 KSHUKLA Created
2208 * 30-May-2006 Kalyani Changed the order of variables in cursor fetch.
2209 * 12-Jun-2006 Kalyani Added new parameter pRevision and code to check the orgn access
2210 * for the item revision.
2211 * 21-Apr-2008 RLNAGARA Bug 6982623 Added validation for formula orgn item access.
2212 * *********************************************************************** */
2213 PROCEDURE CHECK_FORMULA_ITEM_ACCESS(pFormula_id IN NUMBER,
2214 pInventory_Item_ID IN NUMBER,
2215 x_return_status OUT NOCOPY VARCHAR2,
2216 pRevision IN VARCHAR2) IS
2217
2221 SELECT fm.formula_no, fm.formula_vers, organization_code
2218 --RLNAGARA start Bug 6982623 Added validation for formula item access
2219
2220 CURSOR Cur_formula_own (p_formula_id NUMBER, p_inventory_item_id NUMBER) IS
2222 FROM fm_form_mst fm, mtl_parameters o
2223 WHERE formula_id =p_formula_id
2224 AND fm.owner_organization_id = o.organization_id
2225 AND formula_status < 1000
2226 AND NOT EXISTS (SELECT 1
2227 FROM mtl_system_items m
2228 WHERE inventory_item_id = p_inventory_item_id
2229 AND recipe_enabled_flag = 'Y'
2230 AND m.organization_id = fm.owner_organization_id);
2231
2232 CURSOR Cur_formula_own_revision (p_formula_id NUMBER, p_inventory_item_id NUMBER,p_revision VARCHAR2) IS
2233 SELECT fm.formula_no, fm.formula_vers, organization_code
2234 FROM fm_form_mst fm, mtl_parameters o
2235 WHERE formula_id =p_formula_id
2236 AND fm.owner_organization_id = o.organization_id
2237 AND formula_status < 1000
2238 AND NOT EXISTS (SELECT 1
2239 FROM mtl_system_items m, mtl_item_revisions mir
2240 WHERE m.inventory_item_id = p_inventory_item_id
2241 AND mir.revision = p_revision
2242 AND m.inventory_item_id = mir.inventory_item_id
2243 AND m.recipe_enabled_flag = 'Y'
2244 AND m.organization_id = fm.owner_organization_id
2245 AND mir.organization_id = m.organization_id);
2246
2247 --RLNAGARA end Bug 6982623
2248
2249 -- Lists the organization id for the recipe which are not owned
2250 -- By the owner organization id of formula
2251 CURSOR Cur_recipe_own (p_formula_id NUMBER, p_inventory_item_id NUMBER) IS
2252 SELECT recipe_no, recipe_version, organization_code
2253 FROM gmd_recipes_b r, mtl_parameters o
2254 WHERE formula_id = p_formula_id
2255 AND r.owner_organization_id = o.organization_id
2256 AND recipe_status < 1000
2257 AND NOT EXISTS (SELECT 1
2258 FROM mtl_system_items m
2259 WHERE inventory_item_id = p_inventory_item_id
2260 AND recipe_enabled_flag = 'Y'
2261 AND m.organization_id = r.owner_organization_id);
2262 -- Bug 5237351 added
2263 CURSOR Cur_recipe_own_revision (p_formula_id NUMBER, p_inventory_item_id NUMBER,p_revision VARCHAR2) IS
2264 SELECT recipe_no, recipe_version, organization_code
2265 FROM gmd_recipes_b r, mtl_parameters o
2266 WHERE formula_id = p_formula_id
2267 AND r.owner_organization_id = o.organization_id
2268 AND recipe_status < 1000
2269 AND NOT EXISTS (SELECT 1
2270 FROM mtl_system_items m, mtl_item_revisions mir
2271 WHERE m.inventory_item_id = p_inventory_item_id
2272 AND mir.revision = p_revision
2273 AND m.inventory_item_id = mir.inventory_item_id
2274 AND m.recipe_enabled_flag = 'Y'
2275 AND m.organization_id = r.owner_organization_id
2276 AND mir.organization_id = m.organization_id
2277 );
2278
2279 -- Cursors to get the recipe over rides for a recipe id
2280 CURSOR Cur_recipe_override(p_formula_id number, p_inventory_item_id number) IS
2281 select r.recipe_no, r.recipe_version, o.organization_code
2282 from gmd_recipe_process_loss rpl, gmd_recipes_b r, mtl_parameters o
2283 where r.recipe_id = rpl.recipe_id
2284 AND r.formula_id = p_formula_id
2285 AND r.owner_organization_id <> rpl.organization_id
2286 AND rpl.organization_id = o.organization_id
2287 AND r.recipe_status < 1000
2288 AND NOT EXISTS (SELECT 1
2289 FROM mtl_system_items m
2290 WHERE inventory_item_id = p_inventory_item_id
2291 AND recipe_enabled_flag = 'Y'
2292 AND m.organization_id = rpl.organization_id);
2293
2294 -- Bug 5237351 added
2295 CURSOR Cur_recipe_override_revision(p_formula_id number, p_inventory_item_id number,p_revision VARCHAR2) IS
2296 select r.recipe_no, r.recipe_version, o.organization_code
2297 from gmd_recipe_process_loss rpl, gmd_recipes_b r, mtl_parameters o
2298 where r.recipe_id = rpl.recipe_id
2299 AND r.formula_id = p_formula_id
2300 AND r.owner_organization_id <> rpl.organization_id
2301 AND rpl.organization_id = o.organization_id
2302 AND r.recipe_status < 1000
2303 AND NOT EXISTS (SELECT 1
2304 FROM mtl_system_items m, mtl_item_revisions mir
2305 WHERE m.inventory_item_id = p_inventory_item_id
2306 AND mir.revision = p_revision
2307 AND m.inventory_item_id = mir.inventory_item_id
2308 AND m.recipe_enabled_flag = 'Y'
2309 AND m.organization_id = rpl.organization_id
2310 AND mir.organization_id = m.organization_id);
2311
2312 -- Cursor to get the validity rules for the recipes
2313 CURSOR Cur_recipe_validity(p_formula_id number,p_inventory_item_id number) IS
2314 select o.organization_code,r.recipe_no, r.recipe_version
2315 from gmd_recipe_validity_rules rvr, gmd_recipes_b r, mtl_parameters o
2316 where r.recipe_id = rvr.recipe_id
2317 and r.formula_id = p_formula_id
2318 and o.organization_id = rvr.organization_id
2319 AND rvr.organization_id <> r.owner_organization_id
2320 AND r.recipe_status < 1000
2321 AND rvr.validity_rule_status < 1000
2322 AND NOT EXISTS (SELECT 1
2323 FROM mtl_system_items m
2324 WHERE inventory_item_id = p_inventory_item_id
2325 AND recipe_enabled_flag = 'Y'
2329 select o.organization_code,r.recipe_no, r.recipe_version
2326 AND m.organization_id = rvr.organization_id);
2327 -- Bug 5237351 added
2328 CURSOR Cur_recipe_validity_revision(p_formula_id number,p_inventory_item_id number,p_revision VARCHAR2) IS
2330 from gmd_recipe_validity_rules rvr, gmd_recipes_b r, mtl_parameters o
2331 where r.recipe_id = rvr.recipe_id
2332 and r.formula_id = p_formula_id
2333 and o.organization_id = rvr.organization_id
2334 AND r.recipe_status < 1000
2335 AND rvr.validity_rule_status < 1000
2336 AND NOT EXISTS (SELECT 1
2337 FROM mtl_system_items m, mtl_item_revisions mir
2338 WHERE m.inventory_item_id = p_inventory_item_id
2339 AND mir.revision = p_revision
2340 AND m.inventory_item_id = mir.inventory_item_id
2341 AND m.recipe_enabled_flag = 'Y'
2342 AND m.organization_id = rvr.organization_id
2343 AND mir.organization_id = m.organization_id
2344 );
2345 -- Bug 5237351 added
2346 CURSOR Cur_item_rev_ctl(v_item_id NUMBER) IS
2347 select revision_qty_control_code
2348 from mtl_system_items_b
2349 where inventory_item_id = v_item_id;
2350
2351 l_rev_ctl number; -- Bug 5237351 added
2352 l_org_id number;
2353 l_organization_code varchar2(3);
2354 l_formula_no varchar2(32);
2355 l_formula_vers number; --RLNAGARA B6982623
2356 l_recipe_no varchar2(32);
2357 l_recipe_version number;
2358 X_global_return_status varchar2(1) := FND_API.g_ret_sts_success;
2359
2360 VALIDATION_FAIL EXCEPTION;
2361 NO_FORMULA EXCEPTION;
2362
2363 BEGIN
2364 --
2365 -- Find the formula no for the formula id
2366 --
2367 -- Check for the organization access to the formula
2368 IF pFormula_id IS NULL THEN
2369 RAISE NO_FORMULA;
2370
2371 END IF;
2372 -- Bug 5237351 added
2373 OPEN Cur_item_rev_ctl(pInventory_item_id);
2374 FETCH Cur_item_rev_ctl INTO l_rev_ctl;
2375 CLOSE Cur_item_rev_ctl;
2376
2377 --RLNAGARA start Bug 6982623 Added validation for formula item access
2378
2379 OPEN Cur_formula_own (pFormula_id, pInventory_item_id);
2380 FETCH Cur_formula_own INTO l_formula_no, l_formula_vers, l_organization_code;
2381 IF Cur_formula_own%FOUND THEN
2382 CLOSE Cur_formula_own;
2383 FND_MESSAGE.set_name('GMD','GMD_FORM_OWNORG_NO_ACCESS');
2384 FND_MESSAGE.set_token('FORMULA_NO',l_formula_no);
2385 FND_MESSAGE.set_token('FORMULA_VERSION',l_formula_vers);
2386 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',l_organization_code);
2387 fnd_msg_pub.ADD;
2388 X_return_status := FND_API.g_ret_sts_error;
2389 RAISE VALIDATION_FAIL;
2390 END IF;
2391 CLOSE Cur_formula_own;
2392
2393 IF l_rev_ctl = 2 and pRevision IS NOT NULL THEN
2394 OPEN Cur_formula_own_revision (pFormula_id, pInventory_item_id,pRevision);
2395 FETCH Cur_formula_own_revision INTO l_formula_no, l_formula_vers, l_organization_code;
2396 IF Cur_formula_own_revision%FOUND THEN
2397 CLOSE Cur_formula_own_revision;
2398 FND_MESSAGE.set_name('GMD','GMD_FORM_REV_OWNORG_NO_ACCESS');
2399 FND_MESSAGE.set_token('FORMULA_NO',l_formula_no);
2400 FND_MESSAGE.set_token('FORMULA_VERSION',l_formula_vers);
2401 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',l_organization_code);
2402 fnd_msg_pub.ADD;
2403 X_return_status := FND_API.g_ret_sts_error;
2404 RAISE VALIDATION_FAIL;
2405 END IF;
2406 CLOSE Cur_formula_own_revision;
2407 END IF;
2408
2409 --RLNAGARA end Bug 6982623
2410
2411 OPEN Cur_recipe_own (pFormula_id, pInventory_item_id);
2412 FETCH Cur_recipe_own INTO l_recipe_no, l_recipe_version, l_organization_code;
2413 IF Cur_recipe_own%FOUND THEN
2414 CLOSE Cur_recipe_own;
2415 FND_MESSAGE.set_name('GMD','GMD_OWNER_ORG_NOT_ACCESSIBLE');
2416 FND_MESSAGE.set_token('RECIPE_NO',l_recipe_no);
2417 FND_MESSAGE.set_token('RECIPE_VERSION',l_recipe_version);
2418 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',l_organization_code);
2419 fnd_msg_pub.ADD;
2420 X_return_status := FND_API.g_ret_sts_error;
2421 RAISE VALIDATION_FAIL;
2422 END IF;
2423 CLOSE Cur_recipe_own;
2424 -- Bug 5237351 added
2425 IF l_rev_ctl = 2 and pRevision IS NOT NULL THEN
2426 OPEN Cur_recipe_own_revision (pFormula_id, pInventory_item_id,pRevision);
2427 FETCH Cur_recipe_own_revision INTO l_recipe_no, l_recipe_version, l_organization_code;
2428 IF Cur_recipe_own_revision%FOUND THEN
2429 CLOSE Cur_recipe_own_revision;
2430 FND_MESSAGE.set_name('GMD','GMD_REV_OWNORG_NOT_ACCESSIBLE');
2431 FND_MESSAGE.set_token('RECIPE_NO',l_recipe_no);
2432 FND_MESSAGE.set_token('RECIPE_VERSION',l_recipe_version);
2433 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',l_organization_code);
2434 fnd_msg_pub.ADD;
2435 X_return_status := FND_API.g_ret_sts_error;
2436 RAISE VALIDATION_FAIL;
2437 END IF;
2438 CLOSE Cur_recipe_own_revision;
2439 END IF;
2440 -- Check the organization access for the override organizations
2441 OPEN Cur_recipe_override (pFormula_id, pInventory_item_id);
2442 -- Bug 5237126 MK Changed the order of variables.
2443 FETCH Cur_recipe_override INTO l_recipe_no, l_recipe_version, l_organization_code;
2444 IF Cur_recipe_override%FOUND THEN
2445 CLOSE Cur_recipe_override;
2446 FND_MESSAGE.set_name('GMD','GMD_OVERRIDE_ORG_NOT_ACCESSIBL');
2450 fnd_msg_pub.ADD;
2447 FND_MESSAGE.set_token('RECIPE_NO',l_recipe_no);
2448 FND_MESSAGE.set_token('RECIPE_VERSION',l_recipe_version);
2449 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',l_organization_code);
2451 X_return_status := FND_API.g_ret_sts_error;
2452 RAISE VALIDATION_FAIL;
2453 END IF;
2454 CLOSE Cur_recipe_override;
2455 -- Bug 5237351 added
2456 IF l_rev_ctl = 2 and pRevision IS NOT NULL THEN
2457 OPEN Cur_recipe_override_revision (pFormula_id, pInventory_item_id, pRevision);
2458 FETCH Cur_recipe_override_revision INTO l_recipe_no, l_recipe_version, l_organization_code;
2459 IF Cur_recipe_override_revision%FOUND THEN
2460 CLOSE Cur_recipe_override_revision;
2461 FND_MESSAGE.set_name('GMD','GMD_REV_OVERORG_NOT_ACCESSIBL');
2462 FND_MESSAGE.set_token('RECIPE_NO',l_recipe_no);
2463 FND_MESSAGE.set_token('RECIPE_VERSION',l_recipe_version);
2464 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',l_organization_code);
2465 fnd_msg_pub.ADD;
2466 X_return_status := FND_API.g_ret_sts_error;
2467 RAISE VALIDATION_FAIL;
2468 END IF;
2469 CLOSE Cur_recipe_override_revision;
2470 END IF;
2471 OPEN Cur_recipe_validity (pFormula_id, pInventory_item_id);
2472 FETCH Cur_recipe_validity INTO l_organization_code, l_recipe_no, l_recipe_version;
2473 IF Cur_recipe_validity%FOUND THEN
2474 CLOSE Cur_recipe_validity;
2475 FND_MESSAGE.set_name('GMD','GMD_VALIDITY_OWNER_ORG_NOT_ACC');
2476 FND_MESSAGE.set_token('RECIPE_NO',l_recipe_no);
2477 FND_MESSAGE.set_token('RECIPE_VERSION',l_recipe_version);
2478 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',l_organization_code);
2479 fnd_msg_pub.ADD;
2480 X_return_status := FND_API.g_ret_sts_error;
2481 RAISE VALIDATION_FAIL;
2482 END IF;
2483 CLOSE Cur_recipe_validity;
2484 -- Bug 5237351 added
2485 IF l_rev_ctl = 2 and pRevision IS NOT NULL THEN
2486 OPEN Cur_recipe_validity_revision (pFormula_id, pInventory_item_id, pRevision);
2487 FETCH Cur_recipe_validity_revision INTO l_organization_code, l_recipe_no, l_recipe_version;
2488 IF Cur_recipe_validity_revision%FOUND THEN
2489 CLOSE Cur_recipe_validity_revision;
2490 FND_MESSAGE.set_name('GMD','GMD_REV_VALIDITY_ORG_NOT_ACC');
2491 FND_MESSAGE.set_token('RECIPE_NO',l_recipe_no);
2492 FND_MESSAGE.set_token('RECIPE_VERSION',l_recipe_version);
2493 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',l_organization_code);
2494 fnd_msg_pub.ADD;
2495 X_return_status := FND_API.g_ret_sts_error;
2496 RAISE VALIDATION_FAIL;
2497 END IF;
2498 CLOSE Cur_recipe_validity_revision;
2499 END IF;
2500
2501 -- Final Return Status of the API
2502 x_return_status := FND_API.G_RET_STS_SUCCESS;
2503 EXCEPTION
2504 WHEN VALIDATION_FAIL THEN
2505 x_return_status := FND_API.G_RET_STS_ERROR;
2506 WHEN NO_FORMULA THEN
2507 x_return_status := FND_API.G_RET_STS_SUCCESS;
2508
2509 WHEN OTHERS THEN
2510 X_return_status := FND_API.g_ret_sts_unexp_error;
2511
2512 END check_formula_item_access;
2513
2514 -- Kapil ME Auto-Prod :Bug# 5716318
2515 /* Api start of comments
2516 +============================================================================
2517 | PROCEDURE NAME
2518 | CALCULATE_TOTAL_PRODUCT_QTY
2519 |
2520 | DESCRIPTION
2521 | Procedure to calculate Product Qty autmatically.
2522 |
2523 | INPUT PARAMETERS
2524 | pFormula_id NUMBER
2525 |
2526 | OUTPUT PARAMETERS
2527 | x_msg_data VARCHAR2
2528 | x_return_status VARCHAR2
2529 | x_msg_count NUMBER
2530 |
2531 | HISTORY
2532 | 05-FEB-2007 Kapil M Bug# 5716318 Created.
2533 |
2534 +=============================================================================
2535 Api end of comments
2536 */
2537 PROCEDURE Calculate_Total_Product_Qty ( p_formula_id IN gmd_recipes.formula_id%TYPE,
2538 x_return_status OUT NOCOPY VARCHAR2,
2539 x_msg_count OUT NOCOPY NUMBER,
2540 x_msg_data OUT NOCOPY VARCHAR2) IS
2541
2542 -- Definition of variables
2543 l_auto_calc VARCHAR2(1) := 'N';
2544 l_count NUMBER (5) DEFAULT 0;
2545 l_material_tab gmd_common_scale.scale_tab;
2546 l_uom fm_matl_dtl.item_um%TYPE;
2547 l_conv_uom VARCHAR2(30);
2548 l_temp_qty NUMBER := 0;
2549
2550 l_ingredient_qty NUMBER := 0;
2551 l_by_product_qty NUMBER := 0;
2552 l_qty_touse NUMBER := 0;
2553 l_prod_cnt NUMBER := 0;
2554 l_prod_ratio NUMBER := 1;
2555 l_one_prodqty NUMBER;
2556 l_prod_fix_cnt NUMBER := 0;
2557 l_prod_fix_qty NUMBER := 0;
2558 l_prod_prop_cnt NUMBER := 0;
2559 l_prod_prop_qty NUMBER := 0;
2560 lhdrqty NUMBER := 0;
2561 l_different_uom VARCHAR2 (1) := 'N';
2562 l_return_status VARCHAR2(1);
2563 p_orgn_id NUMBER;
2564 l_yield_type VARCHAR2(30);
2565 l_uom_class VARCHAR2(10);
2569
2566 l_common_uom_class VARCHAR2(10);
2567
2568 CANNOT_CONVERT EXCEPTION;
2570 TYPE temp_prod_qty_rec IS RECORD (
2571 inventory_item_id NUMBER,
2572 orig_qty NUMBER,
2573 orig_uom VARCHAR2 (4),
2574 cov_qty NUMBER,
2575 cov_uom VARCHAR2 (4),
2576 line_no NUMBER,
2577 prod_percent NUMBER
2578 );
2579 TYPE temp_prod_tbl IS TABLE OF temp_prod_qty_rec
2580 INDEX BY BINARY_INTEGER;
2581
2582 temp_prod_tbl1 temp_prod_tbl;
2583 -- Cursor Definitions
2584
2585 CURSOR Cur_get_formula_org IS
2586 SELECT OWNER_ORGANIZATION_ID
2587 FROM FM_FORM_MST
2588 WHERE formula_id = p_formula_id;
2589
2590 CURSOR get_formula_lines (vformula_id NUMBER) IS
2591 SELECT line_no, line_type, inventory_item_id, qty, DETAIL_UOM, scale_type,
2592 contribute_yield_ind, scale_multiple, scale_rounding_variance,
2593 rounding_direction , prod_percent
2594 FROM fm_matl_dtl
2595 WHERE formula_id = p_formula_id
2596 AND contribute_yield_ind = 'Y' /* Added in Bug No.6314028 */
2597 ORDER BY line_type;
2598
2599 CURSOR get_unit_of_measure(v_yield_type VARCHAR2) IS
2600 SELECT unit_of_measure
2601 FROM mtl_units_of_measure
2602 WHERE uom_class = v_yield_type
2603 AND base_uom_flag = 'Y';
2604
2605 BEGIN
2606
2607 x_return_status := FND_API.G_RET_STS_SUCCESS;
2608 FND_MSG_PUB.initialize;
2609
2610 -- Check ORganization Parameters
2611 OPEN Cur_get_formula_org;
2612 FETCH Cur_get_formula_org INTO p_orgn_id;
2613 CLOSE Cur_get_formula_org;
2614 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => p_orgn_id ,
2615 P_parm_name => 'GMD_AUTO_PROD_CALC' ,
2616 P_parm_value => l_auto_calc ,
2617 X_return_status => x_return_status );
2618
2619 IF l_auto_calc = 'Y' THEN -- Perform Auto Calculation
2620
2621 -- Get the Yield type UOM - NPD Convergence
2622 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => p_orgn_id ,
2623 P_parm_name => 'FM_YIELD_TYPE' ,
2624 P_parm_value => l_yield_type ,
2625 X_return_status => x_return_status );
2626 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2627 RETURN;
2628 END IF;
2629
2630 /* Populate a local pl/sql table that will be iterated for further processings. */
2631 FOR l_rec IN get_formula_lines (p_formula_id)
2632 LOOP
2633 l_count := l_count + 1;
2634
2635 IF NVL (l_uom, l_rec.detail_uom) <> l_rec.detail_uom
2636 THEN
2637 l_different_uom := 'Y';
2638 END IF;
2639 l_material_tab (l_count).line_no := l_rec.line_no;
2640 l_material_tab (l_count).line_type := l_rec.line_type;
2641 l_material_tab (l_count).inventory_item_id := l_rec.inventory_item_id;
2642 l_material_tab (l_count).qty := l_rec.qty;
2643 l_material_tab (l_count).detail_uom := l_rec.detail_uom;
2644 l_material_tab (l_count).scale_type := l_rec.scale_type;
2645 l_material_tab (l_count).contribute_yield_ind :=
2646 l_rec.contribute_yield_ind;
2647 l_material_tab (l_count).scale_multiple := l_rec.prod_percent;
2648
2649 l_material_tab (l_count).scale_rounding_variance :=
2650 l_rec.scale_rounding_variance;
2651 l_material_tab (l_count).rounding_direction :=
2652 l_rec.rounding_direction;
2653 l_uom := l_rec.detail_uom;
2654 END LOOP;
2655
2656 /* UOM COnversions - Get the common UOM for conversions */
2657
2658 FOR i IN 1 .. l_material_tab.COUNT
2659 LOOP
2660 IF l_material_tab(i).detail_uom IS NOT NULL THEN
2661 /* Get the common UOM class of the detail lines UOM */
2662 SELECT uom_class
2663 INTO l_uom_class
2664 FROM mtl_units_of_measure
2665 where uom_code = l_material_tab (i).detail_uom;
2666
2667 /* If different UOM - get the yield type UOM for conversions. */
2668 IF NVL(l_common_uom_class,l_uom_class) <> l_uom_class THEN
2669 OPEN get_unit_of_measure(l_yield_type);
2670 FETCH get_unit_of_measure INTO l_conv_uom;
2671 CLOSE get_unit_of_measure;
2672 END IF;
2673 l_common_uom_class := l_uom_class;
2674 END IF;
2675 END LOOP;
2676 /* If all the UOMs belong to the same class, Get the base UOM for conversion. */
2677 IF l_conv_uom IS NULL THEN
2678 OPEN get_unit_of_measure(l_common_uom_class);
2679 FETCH get_unit_of_measure INTO l_conv_uom;
2680 CLOSE get_unit_of_measure;
2681
2682 END IF;
2683
2684 -- Calculate the totla material line quantities
2685 FOR i IN 1 .. l_material_tab.COUNT
2686 LOOP
2687 IF l_different_uom = 'Y'
2688 THEN
2689 l_temp_qty := INV_CONVERT.inv_um_convert(item_id => l_material_tab (i).inventory_item_id
2690 ,precision => 5
2691 ,from_quantity => l_material_tab (i).qty
2692 ,from_unit => l_material_tab (i).detail_uom
2696
2693 ,to_unit => l_conv_uom
2694 ,from_name => NULL
2695 ,to_name => NULL);
2697 IF l_temp_qty < 0
2698 THEN
2699 fnd_message.set_name ('GMD', 'GMD_UOM_CONV_ERROR');
2700 fnd_message.set_token('UOM',l_conv_uom);
2701 fnd_msg_pub.ADD;
2702 RAISE CANNOT_CONVERT;
2703 END IF;
2704 ELSE
2705 l_temp_qty := l_material_tab (i).qty;
2706 END IF;
2707
2708 -- If it is ingredient then see if this is contributing to yield or not
2709 IF l_material_tab (i).line_type = -1
2710 AND l_material_tab (i).contribute_yield_ind = 'Y'
2711 THEN
2712 l_ingredient_qty := NVL (l_ingredient_qty, 0) + l_temp_qty;
2713 ELSIF l_material_tab (i).line_type = 2
2714 THEN -- By product
2715 l_by_product_qty := l_by_product_qty + l_temp_qty;
2716 ELSIF l_material_tab (i).line_type = 1
2717 THEN -- Products
2718 l_prod_cnt := l_prod_cnt + 1;
2719
2720 /* See if the product is of scale type fixed, if yes, then do not update and also use to subtract from the total
2721 ingredient qty to be distributed. */
2722 IF l_material_tab (i).scale_type = 0
2723 THEN
2724 l_prod_fix_cnt := l_prod_fix_cnt + 1;
2725 l_prod_fix_qty := l_prod_fix_qty + l_temp_qty;
2726 ELSE
2727 l_prod_prop_cnt := l_prod_prop_cnt + 1;
2728 l_prod_prop_qty := l_prod_prop_qty + l_temp_qty;
2729 temp_prod_tbl1 (l_prod_prop_cnt).inventory_item_id :=
2730 l_material_tab (i).inventory_item_id;
2731 temp_prod_tbl1 (l_prod_prop_cnt).cov_qty := l_temp_qty;
2732 -- Conv UOM
2733 temp_prod_tbl1 (l_prod_prop_cnt).cov_uom := l_conv_uom;
2734 temp_prod_tbl1 (l_prod_prop_cnt).orig_qty :=
2735 l_material_tab (i).qty;
2736 temp_prod_tbl1 (l_prod_prop_cnt).orig_uom :=
2737 l_material_tab (i).detail_uom;
2738 temp_prod_tbl1 (l_prod_prop_cnt).line_no :=
2739 l_material_tab (i).line_no;
2740 temp_prod_tbl1 (l_prod_prop_cnt).prod_percent :=
2741 l_material_tab (i).scale_multiple;
2742
2743 END IF;
2744 END IF;
2745 END LOOP;
2746
2747 /* Get the Quantity to be distributed among Products.
2748 Qty = Sum(INGR) - SUM(BY-PRODS) - SUM(PROD-FIXED) */
2749 l_qty_touse :=
2750 l_ingredient_qty - NVL (l_by_product_qty, 0)
2751 - NVL (l_prod_fix_qty, 0);
2752
2753 /* Now Calculate the Product Qty based on ratio */
2754 FOR i IN 1 .. temp_prod_tbl1.COUNT
2755 LOOP
2756 IF l_prod_prop_qty > 0 THEN
2757 /* Check whether Percentages have been enterd for all Proportional Products. */
2758 IF temp_prod_tbl1 (i).prod_percent IS NULL THEN
2759 fnd_message.set_name ('GMD', 'GMD_ENTER_PERCENTAGE_YES');
2760 fnd_msg_pub.ADD;
2761 RAISE CANNOT_CONVERT;
2762 END IF;
2763 l_prod_ratio := temp_prod_tbl1 (i).prod_percent / 100;
2764 ELSE
2765 l_prod_ratio := 1;
2766 END IF;
2767
2768 -- Calculate the specific prod qty
2769 IF l_qty_touse > 0
2770 THEN
2771 l_one_prodqty := l_qty_touse * l_prod_ratio;
2772 ELSE
2773 l_one_prodqty := temp_prod_tbl1 (i).cov_qty;
2774 END IF;
2775
2776 -- Also keep updating the formula level product qty.
2777 lhdrqty := lhdrqty + l_one_prodqty;
2778 -- Changed
2779 IF l_different_uom = 'Y'
2780 THEN
2781 l_temp_qty := INV_CONVERT.inv_um_convert(item_id => l_material_tab (i).inventory_item_id
2782 ,precision => 5
2783 ,from_quantity => l_one_prodqty
2784 ,from_unit => l_conv_uom
2785 ,to_unit => temp_prod_tbl1 (i).orig_uom
2786 ,from_name => NULL
2787 ,to_name => NULL);
2788
2789 IF l_temp_qty < 0
2790 THEN
2791 x_return_status := 'Q';
2792 fnd_message.set_name ('GMD', 'GMD_UOM_CONV_ERROR');
2793 fnd_message.set_token('UOM',l_conv_uom);
2794 fnd_msg_pub.ADD;
2795 EXIT;
2796 END IF;
2797 ELSE
2798 l_temp_qty := l_one_prodqty;
2799 END IF;
2800
2801 UPDATE fm_matl_dtl
2802 SET qty = ROUND (l_temp_qty, 5),
2803 DETAIL_UOM = temp_prod_tbl1 (i).orig_uom
2804 WHERE formula_id = p_formula_id
2805 AND line_type = 1
2806 AND inventory_item_id = temp_prod_tbl1 (i).inventory_item_id
2807 AND line_no = temp_prod_tbl1 (i).line_no;
2808 END LOOP;
2809
2810 /* Finally update the formula level product qty as prod qty + by product qty. */
2811 lhdrqty := lhdrqty + l_prod_fix_qty + NVL (l_by_product_qty, 0);
2812
2813 -- Update formula Header also
2814 UPDATE fm_form_mst_b
2815 SET total_output_qty = lhdrqty
2816 WHERE formula_id = p_formula_id;
2817 /* Get the message count from the Message stack */
2818 END IF;
2819 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2820 p_data => x_msg_data);
2821 EXCEPTION
2822 WHEN CANNOT_CONVERT THEN
2823 x_return_status := fnd_api.g_ret_sts_unexp_error;
2824 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2825 p_data => x_msg_data);
2826 END Calculate_Total_Product_Qty ;
2827
2828
2829 END; /* Package Body GMD_COMMON_VAL */
[Home] [Help]