APPS.GMD_SPREAD_CALCULATE_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 18

      SELECT d.*, p.tech_parm_name, p.data_type, p.lm_unit_code,p.sort_seq,p.tech_parm_id
      FROM   gmd_material_details_gtmp d, gmd_technical_parameter_gtmp p
      WHERE  d.entity_id = V_entity_id
      AND    p.data_type > 3 AND (p.data_type = 4 OR d.line_type = 1);

Line: 28

    DELETE FROM GMD_SPREAD_ERRORS_GTMP;

Line: 55

        rollup_cost_update(V_entity_id	   => V_entity_id,
                           V_line_id	   => l_rec.line_id,
                           V_parm_name	   => l_rec.tech_parm_name,
                           V_parm_id	   => l_rec.tech_parm_id,
                           V_primary_qty   => l_rec.primary_qty,
                           V_sort_seq	   => l_rec.sort_seq,
                           X_return_status => X_return_status);

Line: 72

        rollup_update (V_entity_id	=> V_entity_id,
                       V_line_id	=> l_rec.line_id,
                       V_parm_name	=> l_rec.tech_parm_name,
                       V_parm_id	=> l_rec.tech_parm_id,
                       V_sort_seq	=> l_rec.sort_seq,
                       X_return_status	=> X_return_status);

Line: 84

        INSERT INTO GMD_SPREAD_ERRORS_GTMP
             (ENTITY_ID,LINE_ID,LINE_TYPE,INVENTORY_ITEM_ID,CONCATENATED_SEGMENTS,LOT_NUMBER,
              TECH_PARM_ID,TECH_PARM_NAME,ERROR_MESSAGE,EXPRESSION_TYPE)
        VALUES
             (V_entity_id,l_rec.line_id,l_rec.line_type,l_rec.inventory_item_id,
              l_rec.concatenated_segments,l_rec.lot_number,
	      l_rec.tech_parm_id,l_rec.tech_parm_name,l_data,l_expression);

Line: 113

      SELECT NVL(SUM(weight), 0), NVL(SUM(weightpct), 0)
      FROM
       (SELECT qty_mass weight, qty_mass * value weightpct
        FROM   gmd_material_details_gtmp d, gmd_technical_data_gtmp t
        WHERE  line_type <> 1
        AND    (line_type = V_line_type OR line_type = 3)
        AND    d.line_id = t.line_id (+)
        AND    d.entity_id = t.entity_id (+)
        AND    d.entity_id = V_entity_id
        AND    t.tech_parm_id (+) = V_parm_id
        AND    rollup_ind = 1
        AND EXISTS (SELECT 1
                    FROM   gmd_material_details_gtmp d1
                    WHERE  line_type = V_line_type
                    AND    d1.parent_line_id = d.parent_line_id));

Line: 149

    UPDATE gmd_technical_data_gtmp
    SET value = X_rollup, num_data = X_rollup
    WHERE line_id = V_line_id
    AND   tech_parm_id = V_parm_id;

Line: 155

          INSERT INTO GMD_TECHNICAL_DATA_GTMP
               (ENTITY_ID,
  		LINE_ID,
  		SORT_SEQ,
  		TECH_PARM_NAME,
  		TECH_PARM_ID,
		VALUE,
		NUM_DATA)
      VALUES
		(V_entity_id,
                 V_line_id,
                 V_sort_seq,
                 V_parm_name,
                 V_parm_id,
		 X_rollup,
		 X_rollup);

Line: 213

    UPDATE gmd_technical_data_gtmp
    SET value = l_value, num_data = l_value
    WHERE line_id = V_line_id
    AND   tech_parm_id = V_parm_id;

Line: 219

          INSERT INTO GMD_TECHNICAL_DATA_GTMP
               (ENTITY_ID,
  		LINE_ID,
  		SORT_SEQ,
  		TECH_PARM_NAME,
  		TECH_PARM_ID,
		VALUE,
		NUM_DATA)
      VALUES
		(V_entity_id,
                 V_line_id,
                 V_sort_seq,
                 V_parm_name,
                 V_parm_id,
		 l_value,
		 l_value);

Line: 267

      SELECT SUM(volume), SUM(volumepct)
      FROM
       (SELECT qty_vol volume, qty_vol * value volumepct
        FROM   gmd_material_details_gtmp d, gmd_technical_data_gtmp t
        WHERE  line_type <> 1
        AND    (line_type = V_line_type OR line_type = 3)
        AND    d.line_id = t.line_id (+)
        AND    d.entity_id = t.entity_id (+)
        AND    d.entity_id = V_entity_id
	AND    t.tech_parm_id (+) = V_parm_id
        AND    rollup_ind = 1
        AND EXISTS (SELECT 1
                    FROM   gmd_material_details_gtmp d1
                    WHERE  line_type = V_line_type
                    AND    d1.parent_line_id = d.parent_line_id));

Line: 283

      SELECT uom_code
      FROM   mtl_units_of_measure
      WHERE  uom_class = V_uom_type;

Line: 314

    UPDATE gmd_technical_data_gtmp
    SET    value = X_rollup,
           num_data = X_rollup
    WHERE  line_id = V_line_id
           AND tech_parm_id = V_parm_id;

Line: 321

          INSERT INTO GMD_TECHNICAL_DATA_GTMP
               (ENTITY_ID,
  		LINE_ID,
  		SORT_SEQ,
  		TECH_PARM_NAME,
  		TECH_PARM_ID,
		VALUE,
		NUM_DATA)
           VALUES
		(V_entity_id,
                V_line_id,
                V_sort_seq,
                V_parm_name,
                V_parm_id,
		X_rollup,
		X_rollup);

Line: 368

     gmd_spread_fetch_pkg.update_line_mass_vol_qty (V_orgn_id	        => V_orgn_id,
                                                    V_line_id	        => V_line_id,
                                                    V_density_parameter => X_density_parameter,
                                                    V_mass_uom	        => X_mass_uom,
                                                    V_vol_uom	        => X_vol_uom,
                                                    X_return_status	=> X_return_status);

Line: 390

  #	rollup_cost_update
  # SYNOPSIS
  #	proc   rollup_cost_update
  # DESCRIPTION
  #      This procedure gets the values for the products for the
  #      by performing the cost units rollup and updates the same.
  ###############################################################*/

  PROCEDURE rollup_cost_update(V_entity_id		IN		NUMBER,
                               V_line_id 		IN		NUMBER,
                               V_parm_name 		IN		VARCHAR2,
                               V_parm_id		IN		NUMBER,
                               V_primary_qty 		IN		VARCHAR2,
                               V_sort_seq	        IN		NUMBER,
                               X_return_status		OUT NOCOPY	VARCHAR2) IS
    X_rollup_cost NUMBER;

Line: 413

    UPDATE gmd_technical_data_gtmp
    SET    value = X_rollup_cost,
           num_data = X_rollup_cost
    WHERE  line_id = V_line_id
    AND    tech_parm_id = V_parm_id;

Line: 420

          INSERT INTO GMD_TECHNICAL_DATA_GTMP
               (ENTITY_ID,
  		LINE_ID,
  		SORT_SEQ,
  		TECH_PARM_NAME,
  		TECH_PARM_ID,
		VALUE,
		NUM_DATA)
          VALUES
		(V_entity_id,
                 V_line_id,
                 V_sort_seq,
                 V_parm_name,
                 V_parm_id,
		 X_rollup_cost,
		 X_rollup_cost);

Line: 437

  END rollup_cost_update;

Line: 441

  #	rollup_update
  # SYNOPSIS
  #	proc   rollup_update
  # DESCRIPTION
  #      This procedure gets the values for the products for the
  #      by performing the cost units rollup and updates the same.
  ###############################################################*/

  PROCEDURE rollup_update (V_entity_id		IN		NUMBER,
                           V_line_id 		IN		NUMBER,
                           V_parm_name 		IN		VARCHAR2,
                           V_parm_id		IN		NUMBER,
                           V_sort_seq	        IN		NUMBER,
                           X_return_status	OUT NOCOPY	VARCHAR2) IS
    X_rollup_cost NUMBER;

Line: 459

    UPDATE gmd_technical_data_gtmp
    SET    value = X_rollup_cost,
           num_data = X_rollup_cost
    WHERE  line_id = V_line_id
    AND    tech_parm_id = V_parm_id;

Line: 466

          INSERT INTO GMD_TECHNICAL_DATA_GTMP
               (ENTITY_ID,
  		LINE_ID,
  		SORT_SEQ,
  		TECH_PARM_NAME,
  		TECH_PARM_ID,
		VALUE,
		NUM_DATA)
          VALUES
		(V_entity_id,
                V_line_id,
                V_sort_seq,
                V_parm_name,
                V_parm_id,
		X_rollup_cost,
		X_rollup_cost);

Line: 483

  END rollup_update;

Line: 502

      SELECT NVL(SUM(volumepct), 0)
      FROM
       (SELECT primary_qty * value volumepct
        FROM   gmd_material_details_gtmp d, gmd_technical_data_gtmp t
        WHERE  line_type <> 1
        AND    (line_type = V_line_type OR line_type = 3)
        AND    d.line_id = t.line_id (+)
        AND    d.entity_id = t.entity_id (+)
        AND    d.entity_id = V_entity_id
	AND    t.tech_parm_id (+) = V_parm_id
        AND    rollup_ind = 1
        AND EXISTS (SELECT 1
                    FROM   gmd_material_details_gtmp d1
                    WHERE  line_type = V_line_type
                    AND    d1.parent_line_id = d.parent_line_id));

Line: 567

      SELECT   qty,detail_uom,value,inventory_item_id,lot_number,tpformula_id
        FROM   gmd_material_details_gtmp d, gmd_technical_data_gtmp t
        WHERE  line_type <> 1
        AND    (line_type = V_line_type OR line_type = 3)
        AND    d.line_id = t.line_id (+)
        AND    d.entity_id = t.entity_id (+)
        AND    d.entity_id = V_entity_id
	AND    t.tech_parm_id (+) = V_parm_id
        AND    t.value IS NOT NULL
        AND    rollup_ind = 1
        AND EXISTS (SELECT 1
                    FROM   gmd_material_details_gtmp d1
                    WHERE  line_type = V_line_type
                    AND    d1.parent_line_id = d.parent_line_id);

Line: 635

      UPDATE gmd_technical_data_gtmp
      SET    value = NULL,
             num_data = NULL
      WHERE  line_id = V_line_id
      AND    tech_parm_id = V_parm_id;

Line: 645

      UPDATE gmd_technical_data_gtmp
      SET    value = X_rollup,
             num_data = X_rollup
      WHERE  line_id = V_line_id
      AND    tech_parm_id = V_parm_id;

Line: 652

            INSERT INTO GMD_TECHNICAL_DATA_GTMP
                 (ENTITY_ID,
  		  LINE_ID,
  		  SORT_SEQ,
  		  TECH_PARM_NAME,
  		  TECH_PARM_ID,
		  VALUE,
		  NUM_DATA)
            VALUES
		  (V_entity_id,
                  V_line_id,
                  V_sort_seq,
                  V_parm_name,
                  V_parm_id,
		  X_rollup,
		  X_rollup);

Line: 686

       select *
       from gmd_material_details_gtmp
       where   entity_id = V_entity_id
       AND    rollup_ind = 1;

Line: 707

       select  a.*,b.concatenated_segments,b.lot_number,b.qty
       from    gmd_technical_data_gtmp a, gmd_material_details_gtmp b
       where   a.entity_id = V_entity_id
       AND     a.line_id= b.line_id
       and    (v_line_id is null or a.line_id = v_line_id);

Line: 740

      SELECT FORMULA_ID
      FROM gmd_material_header_gtmp
      WHERE entity_id = V_entity_id;

Line: 747

      SELECT OWNER_ORGANIZATION_ID
      FROM FM_FORM_MST
      WHERE formula_id = l_formula_id;

Line: 752

    SELECT a.qty , a.detail_uom , a.inventory_item_id
    FROM gmd_material_details_gtmp a , fm_matl_dtl b
    where a.entity_id = V_entity_id
    and b.formula_id = l_formula_id
    and a.inventory_item_id = b.inventory_item_id
    and b.CONTRIBUTE_YIELD_IND = 'Y'
    and a.line_type = b.line_type
    and b.line_type = -1
    UNION
    SELECT a.qty , a.detail_uom , a.inventory_item_id
    FROM gmd_material_details_gtmp a
    where a.entity_id = V_entity_id
    and a.line_type = -1
    and a.inventory_item_id NOT IN (SELECT b.inventory_item_id
                                    FROM   fm_matl_dtl b
                                    WHERE  b.formula_id = l_formula_id
                                    and b.line_type = -1);

Line: 772

    SELECT a.qty , a.detail_uom , a.inventory_item_id
    FROM gmd_material_details_gtmp a , fm_matl_dtl b
    where a.entity_id = V_entity_id
    and b.formula_id = l_formula_id
    and a.inventory_item_id = b.inventory_item_id
    and b.CONTRIBUTE_YIELD_IND = 'Y'
    and a.line_type = b.line_type
    and b.line_type = 2
    UNION
    SELECT a.qty , a.detail_uom , a.inventory_item_id
    FROM gmd_material_details_gtmp a
    where a.entity_id = V_entity_id
    and a.line_type = 2
    and a.inventory_item_id NOT IN (SELECT b.inventory_item_id
                                    FROM   fm_matl_dtl b
                                    WHERE  b.formula_id = l_formula_id
                                    and b.line_type = 2);

Line: 791

    SELECT b.qty , b.detail_uom , b.inventory_item_id
    FROM  fm_matl_dtl b
    WHERE b.formula_id = V_entity_id
    and b.CONTRIBUTE_YIELD_IND = 'Y'
    and b.line_type = 1
    and b.scale_type = 0;

Line: 799

    SELECT prod_percent , inventory_item_id , detail_uom
    FROm fm_matl_dtl
    WHERE formula_id = V_entity_id
    AND line_type = 1
    AND line_no = 1;

Line: 806

    SELECT qty , detail_uom
    FROM gmd_material_details_gtmp
    where entity_id = V_entity_id;

Line: 810

/* Bug No.8317833 - Changed the SELECT clause of below cursor from unit_of_measure to uom_code */
    CURSOR get_unit_of_measure(v_yield_type VARCHAR2) IS
     SELECT  uom_code
     FROM    mtl_units_of_measure
     WHERE   uom_class = v_yield_type
     AND     base_uom_flag = 'Y';

Line: 865

	       SELECT   uom_class
          INTO l_uom_class
          FROM    mtl_units_of_measure
          where uom_code = l_rec .detail_uom;

Line: 991

         UPDATE gmd_material_details_gtmp
        SET qty = l_temp_qty
        WHERE line_type = 1
        AND   entity_id = V_entity_id;