1 PACKAGE BODY pmi_cost_analz_pack as
2 /* $Header: PMICTANB.pls 120.0 2005/05/24 16:58:01 appldev noship $ */
3
4 FUNCTION gmca_get_cost(
5 item_id_vi IN ic_item_mst.item_id%TYPE,
6 inv_whse_code_vi IN cm_cmpt_dtl.whse_code%TYPE,
7 cost_mthd_code_vi IN cm_cmpt_dtl.cost_mthd_code%TYPE,
8 cost_date_vi IN DATE)
9 RETURN NUMBER
10 IS
11 cost_v NUMBER := 0;
12 cost_whse_v cm_whse_asc.cost_whse_code%TYPE := NULL;
13 cost_calendar_v cm_cldr_dtl.calendar_code%TYPE := NULL;
14 cost_period_v cm_cldr_dtl.period_code%TYPE := NULL;
15
16 CURSOR get_cal_per_cur(
17 cost_whse_v IN cm_whse_asc.cost_whse_code%TYPE,
18 cost_mthd_code_v IN cm_cldr_hdr.cost_mthd_code%TYPE,
19 cost_date_v IN DATE)
20 IS
21 SELECT d.calendar_code, d.period_code
22 FROM cm_cldr_dtl d,
23 cm_cldr_hdr h,
24 sy_orgn_mst o,
25 ic_whse_mst w
26 WHERE
27 w.whse_code = cost_whse_v
28 AND w.orgn_code = o.orgn_code
29 AND o.co_code = h.co_code
30 AND h.cost_mthd_code = cost_mthd_code_v
31 AND h.calendar_code = d.calendar_code
32 AND d.start_date <= cost_date_v
33 AND d.end_date >= cost_date_v
34 AND h.delete_mark = 0
35 AND d.delete_mark = 0;
36
37 CURSOR get_cost_cur(
38 item_id_v IN ic_item_mst.item_id%TYPE,
39 cost_whse_code_v IN cm_cmpt_dtl.whse_code%TYPE,
40 calendar_code_v IN cm_cmpt_dtl.calendar_code%TYPE,
41 period_code_v IN cm_cmpt_dtl.period_code%TYPE,
42 cost_mthd_code_v IN cm_cmpt_dtl.cost_mthd_code%TYPE)
43 IS
44 SELECT sum(cmpnt_cost)
45 FROM cm_cmpt_dtl
46 WHERE
47 item_id = item_id_v
48 AND whse_code = cost_whse_code_v
49 AND calendar_code = calendar_code_v
50 AND period_code = period_code_v
51 AND cost_mthd_code = cost_mthd_code_v
52 AND delete_mark = 0
53 GROUP BY
54 item_id, whse_code, calendar_code, period_code,
55 cost_mthd_code;
56 BEGIN
57 cost_whse_v := gmca_cost_whse(inv_whse_code_vi, cost_date_vi);
58
59 IF NOT get_cal_per_cur%ISOPEN THEN
60 OPEN get_cal_per_cur(cost_whse_v, cost_mthd_code_vi,
61 cost_date_vi);
62 END IF;
63 FETCH get_cal_per_cur INTO cost_calendar_v, cost_period_v;
64 IF get_cal_per_cur%NOTFOUND THEN
65 cost_calendar_v := NULL;
66 END IF;
67 IF get_cal_per_cur%ISOPEN THEN
68 CLOSE get_cal_per_cur;
69 END IF;
70
71 IF cost_calendar_v IS NULL THEN
72 RETURN NULL;
73 END IF;
74
75 IF NOT get_cost_cur%ISOPEN THEN
76 OPEN get_cost_cur(item_id_vi,
77 cost_whse_v, cost_calendar_v, cost_period_v, cost_mthd_code_vi);
78 END IF;
79
80 FETCH get_cost_cur INTO cost_v;
81
82 IF get_cost_cur%NOTFOUND THEN
83 cost_v := 0;
84 END IF;
85
86 IF get_cost_cur%ISOPEN THEN
87 CLOSE get_cost_cur;
88 END IF;
89
90 IF cost_v IS NOT NULL THEN
91 RETURN cost_v;
92 ELSE
93 RETURN 0;
94 END IF;
95
96 EXCEPTION
97 WHEN others THEN
98 IF get_cost_cur%ISOPEN THEN
99 CLOSE get_cost_cur;
100 END IF;
101
102 IF get_cal_per_cur%ISOPEN THEN
103 CLOSE get_cal_per_cur;
104 END IF;
105 RETURN NULL;
106 END gmca_get_cost;
107
108 FUNCTION gmca_get_perbal(
109 tr_date_vi IN DATE,
110 co_code_vi IN sy_orgn_mst.co_code%TYPE,
111 whse_code_vi IN ic_perd_bal.whse_code%TYPE,
112 item_id_vi IN ic_perd_bal.item_id%TYPE)
113 RETURN NUMBER
114 IS
115 period_balance_v NUMBER;
116 CURSOR get_perbal_cur(
117 tr_date_vi IN DATE,
118 co_code_vi IN sy_orgn_mst.co_code%TYPE,
119 whse_code_vi IN ic_perd_bal.whse_code%TYPE,
120 item_id_vi IN ic_perd_bal.item_id%TYPE)
121 IS
122
123 SELECT sum(loct_onhand)
124 FROM
125 ic_perd_bal perbal,
126 pmi_inv_calendar_v precal
127 WHERE (tr_date_vi-1) between precal.start_date and precal.end_date
128 and co_code_vi = precal.orgn_code
129 and precal.fiscal_year = perbal.fiscal_year
130 and precal.period = perbal.period
131 and perbal.item_id =item_id_vi
132 and perbal.whse_code=whse_code_vi
133 GROUP BY
134 perbal.fiscal_year, perbal.period, perbal.whse_code, perbal.item_id;
135 BEGIN
136 OPEN get_perbal_cur(tr_date_vi, co_code_vi, whse_code_vi,item_id_vi);
137 FETCH get_perbal_cur INTO period_balance_v;
138
139 IF get_perbal_cur%NOTFOUND THEN
140 RETURN 0;
141 ELSE
142 RETURN period_balance_v;
143 END IF;
144 CLOSE get_perbal_cur;
145 END gmca_get_perbal;
146
147 FUNCTION gmca_get_meaning(p_lookup_typ IN gem_lookups.lookup_type%TYPE,
148 p_lookup_cd IN gem_lookups.lookup_code%TYPE ) RETURN VARCHAR2 IS
149 CURSOR get_meaning_cur IS
150 SELECT Meaning
151 FROM gem_lookups
152 WHERE
153 lookup_type = p_lookup_typ AND
154 lookup_code = p_lookup_cd AND
155 enabled_flag= 'Y' AND
156 start_date_active <= sysdate AND
157 (end_date_active IS NULL OR end_date_active >= sysdate);
158 l_meaning gem_lookups.meaning%TYPE;
159 BEGIN
160 OPEN get_meaning_cur;
161 FETCH get_meaning_cur INTO l_meaning; --get the value from gem lookups
162 CLOSE get_meaning_cur;
163 return l_meaning;
164 END gmca_get_meaning;
165
166
167 FUNCTION gmca_get_onhandqty(p_whse_code IN ic_loct_inv.whse_code%TYPE,
168 p_item_id IN ic_loct_inv.item_id%TYPE,
169 P_Location IN ic_loct_inv.location%TYPE,
170 p_lot_id IN ic_loct_inv.lot_id%TYPE ) RETURN NUMBER IS
171 CURSOR get_onhand_cur IS
172 SELECT item_id,
173 sum(loct_onhand)
174 FROM ic_loct_inv
175 WHERE p_item_id = item_id AND
176 (p_whse_code IS NULL OR (p_whse_code is not null and p_whse_code = whse_code)) AND
177 (p_location IS NULL OR (p_location is not null and p_location = location)) AND
178 (p_lot_id IS NULL OR (p_lot_id is not null and p_lot_id = lot_id))
179 group by item_id;
180 l_item_id ic_loct_inv.item_id%TYPE;
181 l_qty NUMBER;
182 BEGIN
183 OPEN get_onhand_cur;
184 FETCH get_onhand_cur INTO l_item_id,l_qty; --get the value from gem lookups
185 CLOSE get_onhand_cur;
186 return l_qty;
187 END gmca_get_onhandqty;
188
189
190
191
192 /* Function to get type of the item i.e. PRODUCT, INGREDIENT,CO-PRODUCT,BY-PRODUCT */
193
194
195 FUNCTION gmca_get_line_type(
196 item_id_vi IN ic_perd_bal.item_id%TYPE)
197 RETURN NUMBER
198 IS
199 CURSOR line_type_cur IS
200 SELECT distinct line_type from fm_matl_dtl
201 WHERE item_id = item_id_vi
202 order by line_type desc;
203 l_line_type fm_matl_dtl.line_type%type;
204 l_line_type1 fm_matl_dtl.line_type%type := null;
205 BEGIN
206 open line_type_cur;
207 LOOP
208 FETCH line_type_cur into l_line_type;
209 EXIT WHEN line_type_cur%NOTFOUND;
210 IF l_line_type = 1 THEN
211 EXIT;
212 ELSIF l_line_type = -1 THEN
213 IF l_line_type1 IS NOT NULL THEN
214 l_line_type := l_line_type1;
215 END IF;
216 END IF;
217 l_line_type1 := l_line_type;
218 END LOOP;
219 RETURN l_line_type;
220 END;
221
222 FUNCTION gmca_inv_perend(
223 orgn_code_vi IN ic_cldr_dtl.orgn_code%TYPE,
224 fiscal_year_vi IN ic_cldr_dtl.fiscal_year%TYPE,
225 period_vi IN ic_cldr_dtl.period%TYPE)
226 RETURN DATE
227 IS
228 period_end_date_v DATE := null;
229
230 BEGIN
231 IF period_vi >= 1 THEN
232 SELECT (period_end_date + 1 - 1/(24 * 60 * 60)) INTO period_end_date_v
233 FROM
234 ic_cldr_dtl
235 WHERE
236 orgn_code = orgn_code_vi
237 AND fiscal_year = fiscal_year_vi
238 AND period = period_vi;
239 ELSE
240 RETURN null;
241 END IF;
242
243 RETURN period_end_date_v;
244
245 EXCEPTION
246 WHEN others THEN
247 RETURN null;
248 END gmca_inv_perend;
249
250
251 FUNCTION gmca_variance(
252 batch_id_vi IN gme_batch_header.batch_id%TYPE,
253 formula_id_vi IN fm_form_mst.formula_id%TYPE,
254 batch_line_id_vi IN gme_material_details.material_detail_id%TYPE,
255 cost_mthd_vi IN cm_cmpt_dtl.cost_mthd_code%TYPE,
256 plan_qty_vi IN gme_material_details.plan_qty%TYPE,
257 item_um_vi IN gme_material_details.item_um%TYPE,
258 line_type_vi IN gme_material_details.line_type%TYPE,
259 line_no_vi IN gme_material_details.line_no%TYPE,
260 batch_status_vi IN gme_batch_header.batch_status%TYPE,
261 actual_cmplt_date_vi IN gme_batch_header.actual_cmplt_date%TYPE,
262 batch_close_date_vi IN gme_batch_header.batch_close_date%TYPE,
263 wip_whse_code_vi IN gme_batch_header.wip_whse_code%TYPE,
264 item_id_vi IN gme_material_details.item_id%TYPE,
265 formulaline_id_vi IN gme_material_details.formulaline_id%TYPE,
266 actual_qty_vi IN gme_material_details.actual_qty%TYPE
267 )
268 RETURN NUMBER
269 IS
270 /* Declare and Initialize variables */
271 primary_product_v ic_item_mst.item_id%TYPE := 0;
272 fm_qty_v fm_matl_dtl.qty%TYPE := 0;
273 fm_qty_um_v sy_uoms_mst.um_code%TYPE := ' ';
274 pm_qty_v gme_material_details.plan_qty%TYPE := 0;
275 pm_qty_um_v sy_uoms_mst.um_code%TYPE := ' ';
276 pri_prod_um_v sy_uoms_mst.um_code%TYPE := ' ';
277 batch_item_id_v gme_material_details.item_id%TYPE := 0;
278 batch_formulaline_id_v gme_material_details.formulaline_id%TYPE := 0;
279 batch_item_qty_v gme_material_details.actual_qty%TYPE := 0;
280 scale_factor_v NUMBER := 1; /* Primary Scaling Factor */
281 scaling_error_v NUMBER := 0;
282 variance_v NUMBER := 0;
283
284 /* batch values for cost formula retrieval */
285 batch_status_v gme_batch_header.batch_status%TYPE;
286 batch_completion_dt_v gme_batch_header.actual_cmplt_date%TYPE;
287 batch_close_dt_v gme_batch_header.batch_close_date%TYPE;
288 wip_whse_v gme_batch_header.wip_whse_code%TYPE;
289
290 cost_whse_v cm_whse_asc.cost_whse_code%TYPE;
291 cost_date_v DATE := NULL;
292 cost_calendar_v cm_cldr_dtl.calendar_code%TYPE;
293 cost_period_v cm_cldr_dtl.period_code%TYPE;
294 cost_fmeff_id_v fm_form_eff.fmeff_id%TYPE;
295 cost_formula_id_v fm_form_mst.formula_id%TYPE;
296
297 all_linear BOOLEAN := TRUE;
298 b NUMBER := 0; /* Secondary Scaling Factor */
299
300 fm_line_type_v NUMBER := 1; /* product by default ?? */
301 fm_scale_type_v NUMBER := 1; /* linear by default */
302
303 /* Need a cursor to select primary product from formula since
304 there can be blank lines? */
305 CURSOR primary_product_cur(formula_id_p fm_form_mst.formula_id%TYPE)
306 IS
307 SELECT item_id
308 FROM
309 fm_matl_dtl
310 WHERE
311 formula_id = formula_id_p
312 AND line_type = 1
313 ORDER BY
314 line_no;
315
316 /* Select fm_qty and fm_qty_um from costing formula */
317 CURSOR costing_pri_product_cur(formula_id_p fm_form_mst.formula_id%TYPE,
318 item_id_p ic_item_mst.item_id%TYPE)
319 IS
320 SELECT qty, item_um
321 FROM
322 fm_matl_dtl
323 WHERE
324 formula_id = formula_id_p
325 AND line_type = 1
326 AND item_id = item_id_p
327 ORDER BY
328 line_no;
329 n_scale NUMBER := 0;
330
331 BEGIN
332 IF Pkg_var_Batch_id <> batch_id_vi THEN
333 pkg_var_batch_id := batch_id_vi;
334 pkg_var_scale_factor_v:=1;
335 Pkg_var_b:=0;
336 Pkg_var_fm_qty_v:=0;
337 Pkg_var_fm_qty_um_v:=' ';
338 Pkg_var_pm_qty_v:=0;
339 Pkg_var_pm_qty_um_v:=' ';
340 Pkg_var_all_linear:=TRUE;
341 ELSE
342 Scale_factor_v:=pkg_var_scale_factor_v;
343 b:=Pkg_var_b;
344 fm_qty_v:=Pkg_var_fm_qty_v;
345 fm_qty_um_v:=Pkg_var_fm_qty_um_v;
346 pm_qty_v:=Pkg_var_pm_qty_v;
347 pm_qty_um_v:=Pkg_var_pm_qty_um_v;
348 all_linear:=pkg_var_all_linear;
349 GOTO post_scaling;
350 END IF;
351
352 /* Select primary product from fm_matl_dtl for the formula_id */
353 OPEN primary_product_cur(formula_id_vi);
354 FETCH primary_product_cur INTO primary_product_v;
355 CLOSE primary_product_cur;
356
357 IF line_type_vi =1 and Line_no_vi =1 THEN
358 pm_qty_v :=plan_qty_vi;
359 pm_qty_um_v:= item_um_vi;
360 ELSE
361 SELECT plan_qty,item_um into pm_qty_v,pm_qty_um_v
362 FROM gme_material_details
363 WHERE batch_id = batch_id_vi
364 AND line_type =1
365 AND item_id = primary_product_v;
366 END IF;
367
368 /* Retrieve the batch parameters needed for determining the
369 costing formula */
370
371 batch_status_v:=batch_status_vi;
372 wip_whse_v:=wip_whse_code_vi;
373
374 IF batch_status_v = 3 THEN
375 cost_date_v := actual_cmplt_date_vi;
376 ELSIF batch_status_v = 4 THEN
377 cost_date_v := batch_close_date_vi;
378 END IF;
379
380 /* Determine the Cost Whse */
381 cost_whse_v := gmca_cost_whse(wip_whse_v, cost_date_v);
382 BEGIN
383 /* Determine the appropriate cost calendar/period */
384 SELECT d.calendar_code, d.period_code
385 INTO cost_calendar_v, cost_period_v
386 FROM cm_cldr_dtl d,
387 cm_cldr_hdr h,
388 sy_orgn_mst o,
389 ic_whse_mst w
390 WHERE
391 w.whse_code = cost_whse_v
392 AND w.orgn_code = o.orgn_code
393 AND o.co_code = h.co_code
394 AND h.cost_mthd_code = cost_mthd_vi
395 AND h.calendar_code = d.calendar_code
396 AND d.start_date <= cost_date_v
397 AND d.end_date >= cost_date_v
398 AND h.delete_mark = 0
399 AND d.delete_mark = 0;
400
401 /* Now select the fmeff_id if available */
402 SELECT max(fmeff_id) INTO cost_fmeff_id_v
403 FROM cm_cmpt_dtl
404 WHERE item_id = primary_product_v
405 AND whse_code = cost_whse_v
406 AND calendar_code = cost_calendar_v
407 AND period_code = cost_period_v
408 AND cost_mthd_code = cost_mthd_vi
409 AND delete_mark = 0;
410
411 SELECT formula_id
412 INTO cost_formula_id_v
413 FROM fm_form_eff
414 WHERE fmeff_id = cost_fmeff_id_v;
415 EXCEPTION
416 WHEN others THEN
417 cost_formula_id_v := 0;
418 END;
419 /* end of block to retrieve costing formula */
420 IF cost_formula_id_v <= 0 THEN
421 scaling_error_v := 1;
422 GOTO post_scaling;
423 END IF;
424
425 /* The costing formula is known. Get the fm_qty and fm_qty_um from the costing
426 formula for the primary product */
427 /*SKARIMIS Fixed a bug. Instead of Costing formula id, regular formula id is being passed. fixed the issue) */
428 OPEN costing_pri_product_cur(cost_formula_id_v, primary_product_v);
429 FETCH costing_pri_product_cur INTO fm_qty_v, fm_qty_um_v;
430 CLOSE costing_pri_product_cur;
431
432
433 /* The fm_qty and pm_qty can be in different uoms. do uom conversion
434 to the item's primary uom */
435 /* get the prim product's uom */
436 SELECT item_um INTO pri_prod_um_v
437 FROM ic_item_mst
438 WHERE
439 item_id = primary_product_v;
440
441 /* for now pass 0 as lot id (default lot?) */
442 IF fm_qty_um_v <> pri_prod_um_v THEN
443 /* do uom conversion */
444 fm_qty_v := gmca_uomcv(primary_product_v, 0, fm_qty_um_v,
445 fm_qty_v, pri_prod_um_v);
446 END IF;
447 /*SKARIMIS*/
448 Pkg_var_fm_qty_v:=fm_qty_v;
449 Pkg_var_fm_qty_um_v:=fm_qty_um_v;
450
451 IF pm_qty_um_v <> pri_prod_um_v THEN
452 /* do uom conversion */
453 pm_qty_v := gmca_uomcv(primary_product_v, 0, pm_qty_um_v,
454 pm_qty_v, pri_prod_um_v);
455 END IF;
456 /*skarimis*/
457 Pkg_var_pm_qty_v:=pm_qty_v;
458 Pkg_var_pm_qty_um_v:=pm_qty_um_v;
459
460 /* Now compute the scale factor */
461 IF fm_qty_v <> 0 THEN
462 scale_factor_v := pm_qty_v / fm_qty_v;
463 pkg_var_scale_factor_v:=scale_factor_v;
464 ELSE
465 scaling_error_v := 1;
466 GOTO post_scaling;
467 END IF;
468
469 /* now scale factor is known. scale the formula. */
470 /* begin of nested block for scaling */
471 /* refer to OPM scaling routines for detailed info */
472 DECLARE
473 /* Initialize all variables otherwise null is implicitly used
474 if they are used without assignment!! */
475 num_fixed_items_v NUMBER := 0;
476 num_primaries NUMBER := 0;
477 num_secondaries NUMBER := 0;
478 denominator NUMBER := 0;
479 numerator NUMBER := 0;
480 k NUMBER := 0;
481 P0 NUMBER := 0; P1 NUMBER := 0; S0 NUMBER := 0; S2 NUMBER := 0;
482
483 fm_yield_type_v sy_uoms_typ.um_type%TYPE := ' ';
484 fm_yield_type_um_v sy_uoms_mst.um_code%TYPE := ' ';
485 tmp_qty_v NUMBER := 0;
486
487 /* products are primary items per scaling routine */
488
489 CURSOR prod_cur(formula_id_p fm_form_mst.formula_id%TYPE) IS
490 SELECT formulaline_id, item_id, qty, 0 as scaled_qty, scale_type, item_um
491 FROM fm_matl_dtl
492 WHERE formula_id = formula_id_p
493 AND line_type in (1,2)
494 ORDER BY line_type;
495
496 CURSOR ing_cur(formula_id_p fm_form_mst.formula_id%TYPE) IS
497 SELECT formulaline_id, item_id, qty, 0 as scaled_qty, scale_type, item_um
498 FROM fm_matl_dtl
499 WHERE formula_id = formula_id_p
500 AND line_type = -1;
501
502 BEGIN
503
504 /* If all items in formula are linearly scaled, we dont need any
505 uom conversions. simply multiply the qty with the scale factor.
506 If atleast one item is fixed qty scale type then we need uom
507 conversions to fm_yield_type */
508
509 SELECT count(*) INTO num_fixed_items_v
510 FROM
511 fm_matl_dtl
512 WHERE
513 formula_id = cost_formula_id_v
514 AND scale_type = 0;
515
516 IF num_fixed_items_v <= 0 THEN
517 all_linear := TRUE;
518 /*skarimis*/ pkg_var_all_linear:=all_linear;
519 GOTO scaling_ok;
520 END IF;
521
522 /* Now at least one item of fixed scale type exists. So do
523 necessary uom conversions */
524 /* Get the um_type of fm_yield_type */
525
526 /* 01/12/1999 RS - using profile values instead of sy_cont_tbl */
527 /* skarimis replaced FND_PROFILE.VALUE_WNPS with FND_PROFILE.VALUE */
528 SELECT FND_PROFILE.VALUE('FM_YIELD_TYPE')
529 INTO fm_yield_type_v
530 FROM dual;
531
532 /* RS end changes for rel 11 */
533
534 /* now get the std uom of fm_yield_type */
535 SELECT std_um INTO fm_yield_type_um_v
536 FROM
537 sy_uoms_typ
538 WHERE
539 um_type = fm_yield_type_v;
540
541 /* if there is no fm_yield_type_um then exit scaling with error */
542 IF ((fm_yield_type_v is null) or (fm_yield_type_v = ' ')) THEN
543 scaling_error_v := 1;
544 GOTO scaling_end;
545 END IF;
546
547 /* Convert qtys into fm_yield_type_um_v */
548 FOR scale_rec_tmp IN prod_cur(cost_formula_id_v) LOOP
549 n_scale := n_scale + 1;
550 num_primaries := num_primaries + 1;
551
552 /* scale primaries and compute total */
553 tmp_qty_v := 0;
554 tmp_qty_v := gmca_uomcv(scale_rec_tmp.item_id, 0, scale_rec_tmp.item_um,
555 scale_rec_tmp.qty, fm_yield_type_um_v);
556 P0 := P0 + tmp_qty_v;
557 IF scale_rec_tmp.scale_type <> 0 THEN
558 P1 := P1 + tmp_qty_v * scale_factor_v;
559 ELSE
560 P1 := P1 + tmp_qty_v;
561 all_linear := FALSE;
562 /*skarimis*/ pkg_var_all_linear:=all_linear;
563 END IF;
564
565 END LOOP;
566
567 FOR scale_rec_tmp IN ing_cur(cost_formula_id_v) LOOP
568 n_scale := n_scale + 1;
569 num_secondaries := num_secondaries + 1;
570
571 /* sum secondaries */
572 tmp_qty_v := 0;
573 tmp_qty_v := gmca_uomcv(scale_rec_tmp.item_id, 0, scale_rec_tmp.item_um,
574 scale_rec_tmp.qty, fm_yield_type_um_v);
575 IF scale_rec_tmp.scale_type <> 0 THEN
576 /* sum non_fixed secondaries */
577 denominator := denominator + tmp_qty_v;
578 ELSE
579 S2 := S2 + tmp_qty_v;
580 all_linear := FALSE;
581 /*skarimis*/ pkg_var_all_linear:=all_linear;
582 END IF;
583
584 END LOOP;
585
586 IF denominator = 0 THEN
587 /* No non-fixed secondaries, return error */
588 /* return 0 */
589 scaling_error_v := 1;
590 GOTO scaling_end;
591 END IF;
592
593 S0 := denominator + S2;
594
595 /* now n_scale holds the total num of records, but recs start at 0 */
596 IF all_linear THEN
597 null;
598 ELSE
599 null;
600 END IF;
601
602 IF all_linear THEN
603 /* all items are linearly scaled, hence all are scaled with the
604 same scale factor irrespective of UOM */
605 /* return */
606 null;
607 GOTO scaling_ok;
608 END IF;
609
610 /* some items are fixed scaling type */
611 /* do UOM conversions to FM_YIELD_TYPE : Yet to be Done */
612
613
614 IF (P0 = 0) OR (S0 = 0) THEN
615 /* return error */
616 scaling_error_v := 1;
617 GOTO scaling_end;
618 END IF;
619
620 k := P0 / S0;
621 numerator := (P1 / k) - S2;
622 b := numerator / denominator;
623 pkg_var_b:=b;
624 <<scaling_ok>>
625 /* all ok, return success */
626 scaling_error_v := 0;
627
628 <<scaling_end>>
629 null;
630 EXCEPTION
631 WHEN others THEN
632 scaling_error_v := 1;
633 END;
634 /* End of nested block for scaling */
635
636 <<post_scaling>>
637 /* Now we know the primary scaling factor, a (input) and the
638 secondary scaling factor, b (calculated).
639 Note: fm_qty_v, fm_qty_um_v, pm_qty_v, pm_qty_um_v are reused */
640
641 batch_item_id_v:=item_id_vi;
642 batch_formulaline_id_v:=formulaline_id_vi;
643 batch_item_qty_v:=actual_qty_vi;
644 pm_qty_um_v := item_um_vi;
645
646 fm_qty_v := 0;
647 pm_qty_v := batch_item_qty_v;
648
649 IF scaling_error_v = 1 THEN
650 /* error during scaling, return null */
651 variance_v := 0;
652 RETURN NULL;
653 ELSE
654 /* Select the fm_qty from the fm_matl_dtl table if the batch
655 formulaline_id is non-zero, otherwise fm_qty is 0 */
656 IF batch_formulaline_id_v <> 0 THEN
657 BEGIN
658 /* Should fetch only one row */
659 SELECT qty, line_type, scale_type, item_um
660 INTO fm_qty_v, fm_line_type_v, fm_scale_type_v,
661 fm_qty_um_v
662 FROM fm_matl_dtl
663 WHERE formula_id = formula_id_vi
664 AND item_id = batch_item_id_v
665 AND formulaline_id = batch_formulaline_id_v;
666
667 /* Now multiply by the appropriate factor */
668 IF all_linear THEN
669 fm_qty_v := fm_qty_v * scale_factor_v;
670 ELSE
671 /* some items are fixed scaled */
672 /* If fixed do nothing, if linear multiply
673 by either a or b */
674 IF fm_scale_type_v <> 0 THEN
675 IF fm_line_type_v = -1 THEN
676 /* Ingredient */
677 fm_qty_v := fm_qty_v * b;
678 ELSE
679 /* Prod or By-Prod */
680 fm_qty_v := fm_qty_v * scale_factor_v;
681 END IF;
682 END IF;
683 END IF; /* end if all_linear */
684
685 EXCEPTION
686 WHEN others THEN
687 fm_qty_v := 0;
688 END; /* end of block for getting formula qty */
689 ELSE
690 fm_qty_v := 0;
691 END IF; /* end if batch_formulaline_id */
692
693 /* yield or usage or sub = batch_item_qty - scaled fm_qty */
694 variance_v := pm_qty_v - fm_qty_v;
695 END IF; /* end if scaling_error */
696
697 /* Return the fm_qty since any errors would be easier to detect */
698 /* Convert the fm_qty to the item_uom */
699 fm_qty_v := gmca_iuomcv(batch_item_id_v, 0, fm_qty_um_v, fm_qty_v);
700 RETURN fm_qty_v;
701
702 EXCEPTION
703 WHEN others THEN
704 RETURN NULL;
705 END gmca_variance;
706
707
708
709 FUNCTION gmca_iuomcv(item_id_vi Number ,
710 lot_id_vi Number ,
711 from_uom_vi Varchar2,
712 from_qty_vi number)
713 RETURN NUMBER IS
714
715 -- Variable declaration
716 prim_um_v VARCHAR2(4) ;
717 to_qty_v NUMBER;
718
719 BEGIN
720
721 -- Init vars.:
722 prim_um_v := ' ';
723 to_qty_v := 0;
724
725 -- Get the primary UOM for the item
726 BEGIN
727 SELECT im.item_um
728 INTO prim_um_v
729 FROM ic_item_mst im
730 WHERE im.item_id = item_id_vi;
731
732 to_qty_v := gmca_uomcv(item_id_vi, lot_id_vi,
733 from_uom_vi, from_qty_vi, prim_um_v);
734
735 RETURN to_qty_v;
736
737 EXCEPTION
738 WHEN no_data_found THEN
739 RETURN 0 ;
740 END;
741
742 END gmca_iuomcv ; -- End of Function
743
744 FUNCTION gmca_uomcv(item_id_vi Number ,
745 lot_id_vi Number ,
746 from_uom_vi Varchar2,
747 from_qty_vi number,
748 to_uom_vi varchar2 )
749 RETURN NUMBER IS
750
751 -- Variable declaration
752 prim_um varchar2(4) ;
753 prim_um_type varchar2(4) ;
754 prim_std_um varchar2(4) ;
755 prim_stnd_factor Number;
756
757 from_um_type varchar2(4) ;
758 from_stnd_um varchar2(4) ;
759 from_stnd_factor Number ;
760
761 to_um_type varchar2(4) ;
762 to_stnd_um varchar2(4) ;
763 to_stnd_factor Number ;
764 to_qty Number ;
765
766 type_cnv_factor_from Number ;
767 type_cnv_factor_to Number ;
768
769 error_code NUMBER;
770 error_msg VARCHAR2(241);
771
772 BEGIN
773
774 -- Init vars.:
775 error_code := 0 ;
776 error_msg := '' ;
777 to_qty := 0;
778
779 -- If from to to UOMs are same , no need to convert
780 IF from_uom_vi = to_uom_vi THEN
781 to_qty := from_qty_vi ;
782 -- (not used) error_code := -3110 ;
783 -- (not used) error_msg := 'Do Not Need To Convert !!';
784 RETURN to_qty ;
785 END IF ;
786
787 -- Get the primary UOM for the item
788 BEGIN
789 SELECT im.item_um,
790 um.um_type,
791 ut.std_um,
792 um.std_factor
793 INTO prim_um,
794 prim_um_type,
795 prim_std_um,
796 prim_stnd_factor
797 FROM sy_uoms_typ ut,sy_uoms_mst um, ic_item_mst im
798 WHERE im.item_id = item_id_vi
799 AND um.um_code = im.item_um
800 AND um.um_type = ut.um_type ;
801 EXCEPTION WHEN NO_DATA_FOUND THEN
802 -- The item is invalid
803 error_code := -3111 ;
804 RETURN to_qty ;
805 END;
806
807 -- Get the standard UOM and the conversion factor for the from_UOM
808 BEGIN
809 SELECT um.um_type ,
810 ut.std_um ,
811 um.std_factor
812 INTO
813 from_um_type,
814 from_stnd_um,
815 from_stnd_factor
816 FROM sy_uoms_typ ut,sy_uoms_mst um
817 WHERE um.um_type = ut.um_type
818 and um.um_code = from_uom_vi ;
819 EXCEPTION WHEN NO_DATA_FOUND THEN
820 -- Invalid from_UOM provided for conversion
821 error_code := -3112 ;
822 RETURN 0;
823 END;
824
825 -- Get the standard UOM and the conversion factor for the to_UOM
826 BEGIN
827 SELECT um.um_type ,
828 ut.std_um ,
829 um.std_factor
830 INTO
831 to_um_type,
832 to_stnd_um,
833 to_stnd_factor
834 FROM sy_uoms_typ ut,sy_uoms_mst um
835 WHERE um.um_type = ut.um_type
836 and um.um_code = to_uom_vi ;
837 EXCEPTION WHEN NO_DATA_FOUND THEN
838 -- Invalid to_UOM provided for converion
839 error_code:= -3113 ;
840 RETURN 0 ;
841 END;
842
843 -- Compare the from and to UOM types and decide which processing is required
844
845 IF from_um_type = to_um_type THEN
846 type_cnv_factor_from := 1 ;
847 type_cnv_factor_to := 1 ;
848 ELSE
849 -- If the types do not match, use ic_item_cnv to convert to primary unit
850
851 -- (Case 1): if to uom is same as primary unit
852 IF (to_um_type = prim_um_type) THEN
853 BEGIN
854 SELECT type_factor
855 INTO type_cnv_factor_from
856 FROM ic_item_cnv
857 WHERE item_id = item_id_vi
858 and lot_id = lot_id_vi
859 and um_type = from_um_type
860 ;
861 type_cnv_factor_to := 1 ;
862 EXCEPTION WHEN NO_DATA_FOUND THEN
863 -- No conversion data for from_UOM available
864 error_code := -3114 ;
865 error_msg := 'CONVERSION DATA NOT AVAILABLE !!';
866 RETURN 0;
867 END ;
868
869 -- (Case 2): if from uom is same as primary unit
870 ELSIF from_um_type = prim_um_type THEN
871 BEGIN
872 SELECT type_factor
873 INTO type_cnv_factor_to
874 FROM ic_item_cnv
875 WHERE item_id = item_id_vi
876 and lot_id = lot_id_vi
877 and um_type = to_um_type
878 ;
879 type_cnv_factor_from := 1 ;
880 EXCEPTION WHEN NO_DATA_FOUND THEN
881 -- No conversion data for from_UOM available
882 error_code := -3114 ;
883 error_msg := 'CONVERSION DATA NOT AVAILABLE !!';
884 RETURN 0;
885 END ;
886 ELSE
887 -- (Case 3): if neither from_uom_vi nor to_uom_vi is a primary unit
888 -- first convert to primary um type
889 -- then to std uom in the primary um
890 BEGIN
891 SELECT type_factor
892 INTO type_cnv_factor_from
893 FROM ic_item_cnv
894 WHERE item_id = item_id_vi
895 and lot_id = lot_id_vi
896 and um_type = from_um_type
897 ;
898
899 SELECT type_factor
900 INTO type_cnv_factor_to
901 FROM ic_item_cnv
902 WHERE item_id = item_id_vi
903 and lot_id = lot_id_vi
904 and um_type = to_um_type
905 ;
906
907 EXCEPTION
908 WHEN NO_DATA_FOUND THEN
909 -- No conversion data for to_UOM available
910 error_code := -3114 ;
911 error_msg := 'CONVERSION DATA NOT AVAILABLE !!';
912 RETURN 0 ;
913 END ;
914 END IF;
915
916 -- The conversion formula
917
918 END IF ;
919 /*SKARIMIS*/
920 to_qty := from_qty_vi * (from_stnd_factor * type_cnv_factor_from) / (to_stnd_factor * type_cnv_factor_to) ;
921
922 RETURN to_qty;
923
924 END gmca_uomcv ; -- End of Function
925
926 FUNCTION gmca_cost_whse(
927 inv_whse_vi IN cm_whse_asc.whse_code%TYPE,
928 asc_date_vi IN DATE)
929 RETURN VARCHAR
930 IS
931 cost_whse_v cm_whse_asc.cost_whse_code%TYPE := NULL;
932 CURSOR cost_whse_cur(
933 inv_whse_v cm_whse_asc.whse_code%TYPE,
934 asc_date_v DATE)
935 IS
936 SELECT cost_whse_code
937 FROM cm_whse_asc
938 WHERE whse_code = inv_whse_v
939 AND eff_start_date <= asc_date_v
940 AND eff_end_date >= asc_date_v
941 AND delete_mark = 0;
942 BEGIN
943 IF NOT cost_whse_cur%ISOPEN THEN
944 OPEN cost_whse_cur(inv_whse_vi, asc_date_vi);
945 END IF;
946
947 FETCH cost_whse_cur INTO cost_whse_v;
948
949 IF cost_whse_cur%NOTFOUND THEN
950 cost_whse_v := NULL;
951 END IF;
952
953 IF cost_whse_cur%ISOPEN THEN
954 CLOSE cost_whse_cur;
955 END IF;
956
957 IF cost_whse_v IS NOT NULL THEN
958 RETURN cost_whse_v;
959 ELSE
960 RETURN inv_whse_vi;
961 END IF;
962
963 EXCEPTION
964 WHEN others THEN
965 IF cost_whse_cur%ISOPEN THEN
966 CLOSE cost_whse_cur;
967 END IF;
968
969 RETURN inv_whse_vi;
970
971 END gmca_cost_whse;
972
973 FUNCTION gmca_whse_currency(
974 whse_code_vi IN ic_whse_mst.whse_code%TYPE)
975 RETURN VARCHAR2
976 IS
977 currency_v gl_plcy_mst.base_currency_code%TYPE := null;
978 CURSOR whse_currency_cur(
979 whse_code_vi IN ic_whse_mst.whse_code%TYPE)
980 IS
981 SELECT base_currency_code
982 FROM
983 gl_plcy_mst p,
984 sy_orgn_mst o,
985 ic_whse_mst w
986 WHERE
987 w.whse_code = whse_code_vi
988 AND w.orgn_code = o.orgn_code
989 AND o.co_code = p.co_code ;
990 BEGIN
991 IF NOT whse_currency_cur%ISOPEN THEN
992 OPEN whse_currency_cur(whse_code_vi);
993 END IF;
994
995 FETCH whse_currency_cur INTO currency_v;
996
997 IF whse_currency_cur%ISOPEN THEN
998 CLOSE whse_currency_cur;
999 END IF;
1000
1001 IF currency_v IS NOT NULL THEN
1002 RETURN currency_v;
1003 ELSE
1004 RETURN null;
1005 END IF;
1006
1007 EXCEPTION
1008 WHEN others THEN
1009 IF whse_currency_cur%ISOPEN THEN
1010 CLOSE whse_currency_cur;
1011 END IF;
1012
1013 RETURN null;
1014 END gmca_whse_currency;
1015 END pmi_cost_analz_pack ;
[Home] [Help]