/*
SELECT   a.vc_item_code, a.vc_item_desc, a.vc_unit, b.vc_group_desc,
         c.vc_sub_group_desc, b.vc_group_code, c.vc_sub_group_code,
         b.vc_scl_group_code, c.vc_scl_sub_group_code
   
   ,TO_CHAR (SYSDATE, 'DD-MON-RRRR') TO_DATE,
           item_rate ('02', a.vc_item_code) item_rate_bhilai,
         cf_closing ('02', a.vc_item_code, SYSDATE) cl_stk_bhilai,
         item_rate ('03', a.vc_item_code) item_rate_urla,
         cf_closing ('03', a.vc_item_code, SYSDATE) cl_stk_urla,
         item_rate ('05', a.vc_item_code) item_rate_unit_iii,
         cf_closing ('05', a.vc_item_code, SYSDATE) cl_stk_unit_iii
   
    FROM makess.mst_item a, mst_group b, mst_sub_group c
   WHERE a.vc_comp_code = b.vc_comp_code
     AND a.vc_item_group = b.vc_group_code
     AND a.vc_comp_code = '01'
     AND LENGTH (a.vc_item_code) = '7'
     AND NOT EXISTS (
            SELECT vc_item_code
              FROM makess.mst_item_info
             WHERE vc_comp_code = a.vc_comp_code
               AND vc_item_code = a.vc_item_code
               AND NVL (ch_inactive_item_code, 'N') = 'Y')
     AND a.vc_item_code NOT LIKE ('22%%%%%')
     AND a.vc_comp_code = c.vc_comp_code
     AND a.vc_item_sub_group = c.vc_sub_group_code
     AND b.vc_comp_code = c.vc_comp_code
     AND b.vc_group_code = c.vc_group_code
ORDER BY a.vc_item_code ASC
*/
/*
CREATE OR REPLACE FUNCTION cf_closing (
   v_comp_code   VARCHAR2,
   v_item_code   VARCHAR2,
   d2            DATE
)
   RETURN NUMBER
IS
   temp_rec      NUMBER (12, 3) := 0;
   temp_iss      NUMBER (12, 3) := 0;
   temp_iss_st   NUMBER (12, 3) := 0;
   balance       NUMBER (12, 3) := 0;
   year_open     NUMBER         := 0;
   cst_date      DATE;
   pst_date      DATE;
   n_value       NUMBER;
   sale_val      NUMBER         := 0;
BEGIN
   SELECT dt_current_start_date, dt_previous_start_date
     INTO cst_date, pst_date
     FROM finance.fin_account_year
    WHERE vc_comp_code = v_comp_code
      AND d2 >= dt_current_start_date
      AND d2 <= dt_current_end_date;

   BEGIN
      SELECT SUM (DECODE (a.ch_tran_flag, 'M', a.nu_qty)) receive,
             SUM (DECODE (a.ch_tran_flag, 'I', a.nu_qty)) issue,
             SUM (DECODE (a.ch_tran_flag, 'U', a.nu_qty)) issue_st,
             SUM (DECODE (a.ch_tran_flag, 'C', a.nu_qty)) sale_val,
             SUM (NVL (nu_value, 0))
        INTO temp_rec,
             temp_iss,
             temp_iss_st,
             sale_val,
             n_value
        FROM invent.month_summary a
       WHERE a.vc_comp_code = v_comp_code
         AND a.vc_item_code = v_item_code
         AND dt_document_date BETWEEN cst_date AND d2;
   EXCEPTION
      WHEN OTHERS
      THEN
         temp_rec := 0;
         temp_iss := 0;
   END;

   BEGIN
      SELECT NVL (nu_year_open_balance, 0)
        INTO year_open
        FROM mst_month_summary b
       WHERE b.vc_comp_code = v_comp_code
         AND b.vc_item_code = v_item_code
         AND b.dt_fin_start_date = cst_date;
   EXCEPTION
      WHEN OTHERS
      THEN
         year_open := 0;
   END;

   balance :=
        NVL (temp_rec, 0)
      + NVL (year_open, 0)
      - NVL (temp_iss, 0)
      - NVL (temp_iss_st, 0)
      - NVL (sale_val, 0);
   RETURN (NVL (balance, 0));
END;
/
*/
/*
CREATE OR REPLACE FUNCTION item_rate (
   v_comp_code   VARCHAR2,
   v_item_code   VARCHAR2
)
   RETURN NUMBER
IS
   comp_code    VARCHAR2 (2) := v_comp_code;
   comp_code1   VARCHAR2 (2);
   p_cost       NUMBER       := 0;
   i            NUMBER       := 0;
BEGIN
   BEGIN
      p_cost := 0;

      SELECT nu_cost
        INTO p_cost
        FROM stk_lot_summary
       WHERE vc_comp_code = comp_code
         AND NVL (nu_cost, 0) > 0
         AND vc_item_code = v_item_code
         AND TO_NUMBER (vc_lot_no) =
                (SELECT MAX (TO_NUMBER (vc_lot_no))
                   FROM stk_lot_summary
                  WHERE vc_comp_code = comp_code
                    AND NVL (nu_cost, 0) > 0
                    AND vc_item_code = v_item_code);
   EXCEPTION
      WHEN OTHERS
      THEN
         BEGIN
            SELECT nu_price
              INTO p_cost
              FROM purchase.dt_pur_order
             WHERE vc_comp_code = comp_code
               AND vc_item_code = v_item_code
               AND ROWNUM < 2;
         EXCEPTION
            WHEN OTHERS
            THEN
               BEGIN
                  SELECT nu_eval_rate
                    INTO p_cost
                    FROM makess.mst_item
                   WHERE vc_comp_code = v_comp_code
                     AND vc_item_code = v_item_code;

                  IF NVL (p_cost, 0) = 0
                  THEN
                     SELECT SUM (NVL (nu_value, 0)) / SUM (NVL (nu_qty, 0))
                       INTO p_cost
                       FROM month_summary
                      WHERE vc_comp_code = v_comp_code
                        AND vc_item_code = v_item_code
                        AND nu_qty > 0
                        AND nu_value > 0;
                  END IF;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     p_cost := 0;
               END;
         END;
   END;

   RETURN (p_cost);
END;
/
*/