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: 312

      SELECT 1
	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
	)
	AND rownum=1
	UNION ALL
	SELECT 1
	FROM BOM_OPERATION_NETWORKS
	WHERE TO_OP_SEQ_ID IN
	(SELECT OPERATION_SEQUENCE_ID
	FROM BOM_OPERATION_SEQUENCES
	WHERE ROUTING_SEQUENCE_ID = p_rtg_seq_id
	)
	AND rownum=1;

Line: 331

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

Line: 346

  start_tbl.DELETE;

Line: 347

  ntwk_op_tbl.DELETE;

Line: 348

  main_ntwk_op_tbl.DELETE;

Line: 349

  prim_path_tbl.DELETE;

Line: 350

  rework_effect_tbl.DELETE;

Line: 397

  calc_primary_network(p_routing_sequence_id,p_operation_type,p_update_events);

Line: 401

    ntwk_op_tbl.DELETE;

Line: 402

    prim_path_tbl.DELETE;

Line: 403

    rework_effect_tbl.DELETE;

Line: 406

    calc_feeder_network(p_routing_sequence_id,p_operation_type,p_update_events, i);

Line: 411

  start_tbl.DELETE;

Line: 412

  ntwk_op_tbl.DELETE;

Line: 413

  main_ntwk_op_tbl.DELETE;

Line: 414

  prim_path_tbl.DELETE;

Line: 415

  rework_effect_tbl.DELETE;

Line: 471

      	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: 493

    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: 511

      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: 521

      SELECT 1
	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
	)
	AND rownum=1
	UNION ALL
	SELECT 1
	FROM BOM_OPERATION_NETWORKS
	WHERE TO_OP_SEQ_ID IN
	(SELECT OPERATION_SEQUENCE_ID
	FROM BOM_OPERATION_SEQUENCES
	WHERE ROUTING_SEQUENCE_ID = p_rtg_seq_id
	)
	AND rownum=1;

Line: 540

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

Line: 547

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

Line: 562

  start_tbl.DELETE;

Line: 563

  ntwk_op_tbl.DELETE;

Line: 564

  main_ntwk_op_tbl.DELETE;

Line: 565

  prim_path_tbl.DELETE;

Line: 566

  rework_effect_tbl.DELETE;

Line: 624

  calc_primary_network(p_routing_sequence_id,p_operation_type,p_update_events);

Line: 628

    ntwk_op_tbl.DELETE;

Line: 629

    prim_path_tbl.DELETE;

Line: 630

    rework_effect_tbl.DELETE;

Line: 633

    calc_feeder_network(p_routing_sequence_id,p_operation_type,p_update_events, i);

Line: 638

  start_tbl.DELETE;

Line: 639

  ntwk_op_tbl.DELETE;

Line: 640

  main_ntwk_op_tbl.DELETE;

Line: 641

  prim_path_tbl.DELETE;

Line: 642

  rework_effect_tbl.DELETE;

Line: 729

	p_update_events		IN	NUMBER
)
IS
i	NUMBER;

Line: 735

    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: 742

    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: 751

        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: 829

    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: 876

        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: 901

     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: 935

      	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: 953

    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: 964

    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: 977

    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: 988

    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: 999

  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: 1143

  visited.DELETE;

Line: 1154

  SELECT  cfm_routing_flag into l_cfm_routing_flag
  FROM    bom_operational_routings
  WHERE   routing_sequence_id = p_routing_sequence_id;

Line: 1165

  temp_op_tbl.DELETE;

Line: 1178

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

Line: 1183

      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: 1190

      temp_op_tbl.DELETE;

Line: 1221

  updt_db(p_routing_sequence_id, p_operation_type, p_update_events);

Line: 1231

    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: 1245

    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: 1256

    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: 1269

    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: 1279

    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: 1419

      	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: 1461

  visited.DELETE;

Line: 1485

  temp_op_tbl.DELETE;

Line: 1504

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

Line: 1509

      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: 1516

      temp_op_tbl.DELETE;

Line: 1547

   updt_db(p_routing_sequence_id, p_operation_type, p_update_events);

Line: 1593

	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: 1653

        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: 1664

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

Line: 1669

    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: 1699

        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: 1709

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

Line: 1714

      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: 1740

    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: 1748

    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: 1789

        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: 1815

        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: 1820

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

Line: 1856

        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: 1861

        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: 1905

        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: 1939

        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: 1945

        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: 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: 2061

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

Line: 2067

  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: 2102

  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: 2108

  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: 2114

  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: 2120

  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: 2138

  rework_effect_tbl.DELETE;

Line: 2201

      rework_effect_tbl.DELETE;