APPS.BOM_CALC_CYNP SQL Statements

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

Line: 61

      	p_update_events		IN	NUMBER
);

Line: 67

      	p_update_events		IN	NUMBER,
	p_ind			IN	NUMBER
);

Line: 189

     select  value
     INTO l_utl_file_dir
     FROM v$parameter
     WHERE name = 'utl_file_dir';

Line: 253

      	p_update_events		IN	NUMBER,
      	x_token_tbl		OUT NOCOPY Error_Handler.Token_Tbl_Type,
      	x_err_msg		OUT NOCOPY VARCHAR2,
      	x_return_status		OUT NOCOPY VARCHAR2
) IS
  -- Select all the operations in the routing that do NOT have a FROM
  -- operation.  These are valid starting points for multiple paths (i.e.
  -- it includes feeder lines)
/*  CURSOR start_ops_cur  IS
    SELECT DISTINCT from_op_seq_id start_op_seq_id,
			from_seq_num start_op_seq_num,
			nvl(yield, 1) start_op_yield
    FROM bom_operation_networks_v bonv,
	 bom_operation_sequences bos
    WHERE bonv.routing_sequence_id = p_routing_sequence_id
      AND   bonv.operation_type = p_operation_type
      AND   bonv.transition_type <> 3
      AND   bonv.from_op_seq_id = bos.operation_sequence_id
      AND NOT EXISTS (SELECT NULL
                        FROM  bom_operation_networks net
                        WHERE net.to_op_seq_id = bonv.from_op_seq_id
			AND   net.transition_type <> 3)
    ORDER BY from_seq_num;*/

Line: 278

    SELECT DISTINCT from_op_seq_id start_op_seq_id,
			from_seq_num start_op_seq_num,
			DECODE(borv.cfm_routing_flag, 3, (DECODE(bos.operation_yield_enabled, 1, NVL(bos.yield, 1), 1)), NVL(bos.yield, 1)) start_op_yield
    FROM bom_operation_networks_v bonv,
	 bom_operation_sequences bos,
	 bom_operational_routings_v borv
    WHERE bonv.routing_sequence_id = borv.routing_Sequence_id
      AND   bonv.routing_sequence_id = p_routing_sequence_id
      AND   bonv.operation_type = p_operation_type
      AND   bonv.transition_type <> 3
      AND   bonv.from_op_seq_id = bos.operation_sequence_id
      AND NOT EXISTS (SELECT NULL
                        FROM  bom_operation_networks net
                        WHERE net.to_op_seq_id = bonv.from_op_seq_id
			AND   net.transition_type <> 3)
    ORDER BY from_seq_num;

Line: 296

      SELECT null FROM DUAL
      WHERE exists
      ( SELECT null FROM bom_operation_networks_v bonv
        WHERE bonv.routing_sequence_id = p_routing_sequence_id
        AND   bonv.operation_type = p_operation_type
	AND   bonv.transition_type <> 3 );

Line: 304

      SELECT cfm_routing_flag
      FROM BOM_OPERATIONAL_ROUTINGS bor
      WHERE bor.routing_sequence_id = p_routing_sequence_id;

Line: 310

      SELECT COUNT(*) FROM bom_operation_networks
      WHERE from_op_seq_id IN
	( SELECT operation_sequence_id FROM bom_operation_sequences
	  WHERE routing_sequence_id = p_rtg_seq_id )
      OR to_op_SEQ_ID IN
	( SELECT operation_sequence_id FROM bom_operation_sequences
	  WHERE routing_sequence_id = p_rtg_seq_id );

Line: 319

      SELECT nvl(cfm_routing_flag, 2)
      FROM bom_operational_routings
      WHERE routing_sequence_id = p_rtg_seq_id;

Line: 334

  start_tbl.DELETE;

Line: 335

  ntwk_op_tbl.DELETE;

Line: 336

  main_ntwk_op_tbl.DELETE;

Line: 337

  prim_path_tbl.DELETE;

Line: 338

  rework_effect_tbl.DELETE;

Line: 385

  calc_primary_network(p_routing_sequence_id,p_operation_type,p_update_events);

Line: 389

    ntwk_op_tbl.DELETE;

Line: 390

    prim_path_tbl.DELETE;

Line: 391

    rework_effect_tbl.DELETE;

Line: 394

    calc_feeder_network(p_routing_sequence_id,p_operation_type,p_update_events, i);

Line: 399

  start_tbl.DELETE;

Line: 400

  ntwk_op_tbl.DELETE;

Line: 401

  main_ntwk_op_tbl.DELETE;

Line: 402

  prim_path_tbl.DELETE;

Line: 403

  rework_effect_tbl.DELETE;

Line: 459

      	p_update_events		IN	NUMBER
) IS
  -- Select all the operations in the routing that do NOT have a FROM
  -- operation.  These are valid starting points for multiple paths (i.e.
  -- it includes feeder lines)
/*  CURSOR start_ops_cur  IS
    SELECT DISTINCT from_op_seq_id start_op_seq_id,
			from_seq_num start_op_seq_num,
			nvl(yield, 1) start_op_yield
    FROM bom_operation_networks_v bonv,
	 bom_operation_sequences bos
    WHERE bonv.routing_sequence_id = p_routing_sequence_id
      AND   bonv.operation_type = p_operation_type
      AND   bonv.transition_type <> 3
      AND   bonv.from_op_seq_id = bos.operation_sequence_id
      AND NOT EXISTS (SELECT NULL
                        FROM  bom_operation_networks net
                        WHERE net.to_op_seq_id = bonv.from_op_seq_id
			AND   net.transition_type <> 3)
    ORDER BY from_seq_num;*/

Line: 481

    SELECT DISTINCT from_op_seq_id start_op_seq_id,
			from_seq_num start_op_seq_num,
			DECODE(borv.cfm_routing_flag, 3, (DECODE(bos.operation_yield_enabled, 1, NVL(bos.yield, 1), 1)), NVL(bos.yield, 1)) start_op_yield
    FROM bom_operation_networks_v bonv,
	 bom_operation_sequences bos,
	 bom_operational_routings_v borv
    WHERE bonv.routing_sequence_id = borv.routing_Sequence_id
      AND   bonv.routing_sequence_id = p_routing_sequence_id
      AND   bonv.operation_type = p_operation_type
      AND   bonv.transition_type <> 3
      AND   bonv.from_op_seq_id = bos.operation_sequence_id
      AND NOT EXISTS (SELECT NULL
                        FROM  bom_operation_networks net
                        WHERE net.to_op_seq_id = bonv.from_op_seq_id
			AND   net.transition_type <> 3)
    ORDER BY from_seq_num;

Line: 499

      SELECT null FROM DUAL
      WHERE exists
      ( SELECT null FROM bom_operation_networks_v bonv
        WHERE bonv.routing_sequence_id = p_routing_sequence_id
        AND   bonv.operation_type = p_operation_type
	AND   bonv.transition_type <> 3 );

Line: 507

      SELECT COUNT(*) FROM bom_operation_networks
      WHERE from_op_seq_id IN
	( SELECT operation_sequence_id FROM bom_operation_sequences
	  WHERE routing_sequence_id = p_rtg_seq_id )
      OR to_op_SEQ_ID IN
	( SELECT operation_sequence_id FROM bom_operation_sequences
	  WHERE routing_sequence_id = p_rtg_seq_id );

Line: 516

      SELECT nvl(cfm_routing_flag, 2)
      FROM bom_operational_routings
      WHERE routing_sequence_id = p_rtg_seq_id;

Line: 523

      SELECT cfm_routing_flag
      FROM BOM_OPERATIONAL_ROUTINGS bor
      WHERE bor.routing_sequence_id = p_routing_sequence_id;

Line: 537

  start_tbl.DELETE;

Line: 538

  ntwk_op_tbl.DELETE;

Line: 539

  main_ntwk_op_tbl.DELETE;

Line: 540

  prim_path_tbl.DELETE;

Line: 541

  rework_effect_tbl.DELETE;

Line: 593

  calc_primary_network(p_routing_sequence_id,p_operation_type,p_update_events);

Line: 597

    ntwk_op_tbl.DELETE;

Line: 598

    prim_path_tbl.DELETE;

Line: 599

    rework_effect_tbl.DELETE;

Line: 602

    calc_feeder_network(p_routing_sequence_id,p_operation_type,p_update_events, i);

Line: 607

  start_tbl.DELETE;

Line: 608

  ntwk_op_tbl.DELETE;

Line: 609

  main_ntwk_op_tbl.DELETE;

Line: 610

  prim_path_tbl.DELETE;

Line: 611

  rework_effect_tbl.DELETE;

Line: 695

	p_update_events		IN	NUMBER
)
IS
i	NUMBER;

Line: 701

    update bom_operation_sequences
    set cumulative_yield = ntwk_op_tbl(i).cumulative_yield,
        reverse_cumulative_yield = ntwk_op_tbl(i).rev_cumulative_yield,
        net_planning_percent = ntwk_op_tbl(i).net_planning_pct
    where operation_sequence_id = ntwk_op_tbl(i).operation_seq_id;

Line: 708

    if p_update_events = 1 then
      if p_operation_type = 2 then
        update bom_operation_sequences
        set cumulative_yield = ntwk_op_tbl(i).cumulative_yield,
            reverse_cumulative_yield = ntwk_op_tbl(i).rev_cumulative_yield,
            net_planning_percent = ntwk_op_tbl(i).net_planning_pct
        where routing_sequence_id = p_routing_sequence_id
        and  process_op_seq_id = ntwk_op_tbl(i).operation_seq_id;

Line: 717

        update bom_operation_sequences
        set cumulative_yield = ntwk_op_tbl(i).cumulative_yield,
            reverse_cumulative_yield = ntwk_op_tbl(i).rev_cumulative_yield,
            net_planning_percent = ntwk_op_tbl(i).net_planning_pct
        where routing_sequence_id = p_routing_sequence_id
        and  line_op_seq_id = ntwk_op_tbl(i).operation_seq_id;

Line: 795

    SELECT to_op_seq_id next_op_seq_id,
	   to_seq_num next_op_seq_num,
	   transition_type
    FROM bom_operation_networks_v bonv
    WHERE routing_sequence_id = g_rtg_seq_id
	AND from_op_seq_id = cv_start_op_seq_id
	AND operation_type = g_op_type
--	AND transition_type <> 3
    ORDER BY transition_type  -- the order is important - primary, alternate and then rework loop shud be considered
    , next_op_seq_num;

Line: 842

        SELECT from_op_seq_id prev_op, planning_pct
        FROM bom_operation_networks
        WHERE to_op_seq_id = cv_to_seq_id
                AND transition_type = 3;

Line: 867

     SELECT bonv.transition_type, bonv.planning_pct
     FROM bom_operation_networks_v bonv
     WHERE routing_sequence_id = g_rtg_seq_id
	AND bonv.from_op_seq_id = cv_start_op_seq_id
	AND bonv.operation_type = g_op_type
	AND bonv.transition_type <> 3;

Line: 901

      	p_update_events		IN	NUMBER
) IS

   CURSOR end_op_cur  IS
    SELECT max(to_op_seq_id) end_op_seq_id
    FROM bom_operation_networks_v bonv
    WHERE bonv.routing_sequence_id = p_routing_sequence_id
      AND   bonv.operation_type = p_operation_type
      AND   bonv.transition_type <> 3
      AND NOT EXISTS (SELECT NULL
                        FROM  bom_operation_networks net
                        WHERE net.from_op_seq_id = bonv.to_op_seq_id
			AND   net.transition_type <> 3);

Line: 919

    SELECT to_op_seq_id next_op_seq_id,
	   to_seq_num next_op_seq_num,
	   transition_type
    FROM bom_operation_networks_v bonv
    WHERE routing_sequence_id = p_routing_sequence_id
	AND from_op_seq_id = cv_start_op_seq_id
	AND operation_type = p_operation_type
	AND transition_type <> 3
    ORDER BY transition_type desc, next_op_seq_num;

Line: 930

    SELECT to_op_seq_id next_op_seq_id,
	   to_seq_num next_op_seq_num,
	   transition_type
    FROM bom_operation_networks_v bonv
    WHERE routing_sequence_id = p_routing_sequence_id
	AND from_op_seq_id = cv_op_seq_id
	AND operation_type = p_operation_type
	AND transition_type = 2
    ORDER BY transition_type desc, next_op_seq_num;

Line: 943

    SELECT to_op_seq_id, to_seq_num,
	   transition_type, nvl(planning_pct, 0) planning_pct,
           from_op_seq_id, from_seq_num
    FROM bom_operation_networks_v bonv
    WHERE routing_sequence_id = p_routing_sequence_id
	AND from_op_seq_id = cv_start_op_seq_id
	AND operation_type = p_operation_type
	AND transition_type = 3
    ORDER BY to_seq_num;

Line: 954

    SELECT to_op_seq_id, to_seq_num,
	   transition_type, nvl(planning_pct, 0) planning_pct,
           from_op_seq_id, from_seq_num
    FROM bom_operation_networks_v bonv
    WHERE routing_sequence_id = p_routing_sequence_id
	AND from_op_seq_id = cv_start_op_seq_id
	AND operation_type = p_operation_type
	AND transition_type = 3
    ORDER BY to_seq_num;

Line: 965

  select bon.to_op_seq_id prim_op_seq_id
  from bom_operation_networks bon
  connect by prior to_op_seq_id = from_op_seq_id
             and
             nvl(bon.transition_type, 0) not in (2, 3)
  start with from_op_seq_id = cv_start_op_seq_id
             and
             nvl(bon.transition_type, 0) not in (2, 3);

Line: 1108

  visited.DELETE;

Line: 1118

  temp_op_tbl.DELETE;

Line: 1131

      select nvl(yield, 1)
        into l_yield
        from bom_operation_sequences
        where operation_sequence_id = ntwk_op_tbl(i).operation_seq_id;*/

Line: 1136

      select DECODE(bor.cfm_routing_flag, 3, (DECODE(bos.operation_yield_enabled, 1, NVL(bos.yield, 1), 1)), NVL(bos.yield, 1))
	into l_yield
	from bom_operational_routings bor, bom_operation_sequences bos
	where bor.routing_sequence_id = bos.routing_sequence_id
	and bos.operation_sequence_id = ntwk_op_tbl(i).operation_seq_id;

Line: 1143

      temp_op_tbl.DELETE;

Line: 1174

  updt_db(p_routing_sequence_id, p_operation_type, p_update_events);

Line: 1184

    SELECT max(to_op_seq_id) end_op_seq_id
    FROM bom_operation_networks_v bonv
    WHERE bonv.routing_sequence_id = p_routing_sequence_id
      AND   bonv.operation_type = p_operation_type
      AND   bonv.transition_type <> 3
      AND NOT EXISTS (SELECT NULL
                        FROM  bom_operation_networks net
                        WHERE net.from_op_seq_id = bonv.to_op_seq_id
			AND   net.transition_type <> 3);

Line: 1198

    SELECT to_op_seq_id next_op_seq_id,
	   to_seq_num next_op_seq_num,
	   transition_type
    FROM bom_operation_networks_v bonv
    WHERE routing_sequence_id = p_routing_sequence_id
	AND from_op_seq_id = cv_start_op_seq_id
	AND operation_type = p_operation_type
	AND transition_type <> 3
    ORDER BY transition_type desc, next_op_seq_num;

Line: 1209

    SELECT to_op_seq_id next_op_seq_id,
	   to_seq_num next_op_seq_num,
	   transition_type
    FROM bom_operation_networks_v bonv
    WHERE routing_sequence_id = p_routing_sequence_id
	AND from_op_seq_id = cv_op_seq_id
	AND operation_type = p_operation_type
	AND transition_type = 2
    ORDER BY transition_type desc, next_op_seq_num;

Line: 1222

    SELECT to_op_seq_id, to_seq_num,
	   transition_type, nvl(planning_pct, 0) planning_pct,
           from_op_seq_id, from_seq_num
    FROM bom_operation_networks_v bonv
    WHERE routing_sequence_id = p_routing_sequence_id
	AND from_op_seq_id = cv_start_op_seq_id
	AND operation_type = p_operation_type
	AND transition_type = 3
    ORDER BY to_seq_num;

Line: 1232

    SELECT to_op_seq_id, to_seq_num,
	   transition_type, nvl(planning_pct, 0) planning_pct,
           from_op_seq_id, from_seq_num
    FROM bom_operation_networks_v bonv
    WHERE routing_sequence_id = p_routing_sequence_id
	AND from_op_seq_id = cv_start_op_seq_id
	AND operation_type = p_operation_type
	AND transition_type = 3
    ORDER BY to_seq_num;

Line: 1372

      	p_update_events		IN	NUMBER,
	p_ind			IN	NUMBER
)
IS
  cursor prim_path_fdr_cur (cv_start_op_seq_id number) is
  select bon.to_op_seq_id prim_op_seq_id
  from bom_operation_networks bon
  connect by prior to_op_seq_id = from_op_seq_id
             and
             nvl(bon.transition_type, 0) not in (2, 3)
  start with from_op_seq_id = cv_start_op_seq_id
             and
             nvl(bon.transition_type, 0) not in (2, 3);

Line: 1414

  visited.DELETE;

Line: 1438

  temp_op_tbl.DELETE;

Line: 1457

      select nvl(yield, 1)
        into l_yield
        from bom_operation_sequences
        where operation_sequence_id = ntwk_op_tbl(i).operation_seq_id;*/

Line: 1462

      select DECODE(bor.cfm_routing_flag, 3, (DECODE(bos.operation_yield_enabled, 1, NVL(bos.yield, 1), 1)), NVL(bos.yield, 1))
	into l_yield
	from bom_operational_routings bor, bom_operation_sequences bos
	where bor.routing_sequence_id = bos.routing_sequence_id
	and bos.operation_sequence_id = ntwk_op_tbl(i).operation_seq_id;

Line: 1469

      temp_op_tbl.DELETE;

Line: 1500

   updt_db(p_routing_sequence_id, p_operation_type, p_update_events);

Line: 1546

	SELECT from_op_seq_id prev_op, planning_pct
	--FROM bom_operation_networks_v
	FROM bom_operation_networks
	WHERE to_op_seq_id = cv_to_seq_id
		AND transition_type <> 3;

Line: 1606

        SELECT from_op_seq_id prev_op, planning_pct
        FROM bom_operation_networks
        WHERE to_op_seq_id = cv_to_seq_id
                AND transition_type <> 3;

Line: 1617

    select nvl(yield, 1)
    into l_yield
    from bom_operation_sequences
    where operation_sequence_id = ntwk_op_tbl(op_ptr).operation_seq_id;*/

Line: 1622

    select DECODE(bor.cfm_routing_flag, 3, (DECODE(bos.operation_yield_enabled, 1, NVL(bos.yield, 1), 1)), NVL(bos.yield, 1))
    into l_yield
    from bom_operational_routings bor, bom_operation_sequences bos
    where bor.routing_sequence_id = bos.routing_sequence_id
    and bos.operation_sequence_id = ntwk_op_tbl(op_ptr).operation_seq_id;

Line: 1652

        SELECT to_op_seq_id next_op, planning_pct
        FROM bom_operation_networks
        WHERE from_op_seq_id = cv_to_seq_id
                AND transition_type <> 3;

Line: 1662

    select nvl(yield, 1)
    into l_yield
    from bom_operation_sequences
    where operation_sequence_id = ntwk_op_tbl(op_ptr).operation_seq_id;*/

Line: 1667

      select DECODE(bor.cfm_routing_flag, 3, (DECODE(bos.operation_yield_enabled, 1, NVL(bos.yield, 1), 1)), NVL(bos.yield, 1))
	into l_yield
	from bom_operational_routings bor, bom_operation_sequences bos
	where bor.routing_sequence_id = bos.routing_sequence_id
	and bos.operation_sequence_id = ntwk_op_tbl(op_ptr).operation_seq_id;

Line: 1693

    SELECT from_op_seq_id prev_op_seq_id,
           transition_type
    FROM bom_operation_networks bonv
    --WHERE routing_sequence_id = p_routing_sequence_id
    WHERE to_op_seq_id = cv_op_seq_id;

Line: 1701

    SELECT to_op_seq_id next_op_seq_id,
           transition_type
    FROM bom_operation_networks bonv
    --WHERE routing_sequence_id = p_routing_sequence_id
    WHERE from_op_seq_id = cv_op_seq_id;

Line: 1742

        SELECT from_op_seq_id prev_op, planning_pct
        FROM bom_operation_networks
        WHERE to_op_seq_id = cv_to_seq_id
        AND transition_type = 3;

Line: 1768

        SELECT from_op_seq_id prev_op, planning_pct
        FROM bom_operation_networks
        WHERE to_op_seq_id = cv_to_seq_id
        AND transition_type <> 3;

Line: 1773

        SELECT SUM(planning_pct) pct_sum
        FROM bom_operation_networks
        WHERE from_op_seq_id = cv_from_seq_id
        AND transition_type <> 3;

Line: 1809

        SELECT to_op_seq_id next_op
        FROM bom_operation_networks
        WHERE from_op_seq_id = cv_from_seq_id
                AND transition_type <> 3;

Line: 1814

        SELECT  PLANNING_PCT npp
        FROM bom_operation_networks
        WHERE from_op_seq_id = cv_from_seq_id
        AND to_op_seq_id = cv_to_seq_id
                AND transition_type <> 3;

Line: 1858

        SELECT to_op_seq_id next_op
        FROM bom_operation_networks
        WHERE from_op_seq_id = cv_from_seq_id
                AND transition_type <> 3;

Line: 1892

        SELECT from_op_seq_id prev_op, planning_pct
        FROM bom_operation_networks
        WHERE to_op_seq_id = cv_to_seq_id
                AND transition_type <> 3;

Line: 1898

        SELECT  PLANNING_PCT npp
        FROM bom_operation_networks
	        WHERE from_op_seq_id = cv_from_seq_id
        AND to_op_seq_id = cv_to_seq_id
                AND transition_type <> 3;

Line: 1973

  SELECT from_op_seq_id prev_op, planning_pct
  FROM bom_operation_networks
  WHERE to_op_seq_id = cv_to_seq_id
  AND transition_type <> 3;

Line: 2014

  SELECT count(*) count
  FROM bom_operation_networks
  WHERE to_op_seq_id = cv_to_seq_id
  AND transition_type <> 3;

Line: 2020

  SELECT from_op_seq_id prev_op, planning_pct
  FROM bom_operation_networks
  WHERE to_op_seq_id = cv_to_seq_id
  AND transition_type <> 3;

Line: 2055

  SELECT to_op_seq_id next_op, planning_pct
  FROM bom_operation_networks
  WHERE from_op_seq_id = cv_from_seq_id
  AND transition_type = 3;

Line: 2061

  SELECT to_op_seq_id next_op, planning_pct
  FROM bom_operation_networks
  WHERE from_op_seq_id = cv_op_seq_id
  AND transition_type = 3;

Line: 2067

  SELECT to_op_seq_id next_op, planning_pct
  FROM bom_operation_networks
  WHERE from_op_seq_id = cv_op_seq_id
  AND transition_type <> 3;

Line: 2073

  SELECT to_op_seq_id next_op, planning_pct
  FROM bom_operation_networks
  WHERE from_op_seq_id = cv_op_seq_id
  AND transition_type <> 3;

Line: 2091

  rework_effect_tbl.DELETE;

Line: 2154

      rework_effect_tbl.DELETE;