APPS.AHL_UC_POS_NECES_PVT SQL Statements

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

Line: 69

  SELECT csi_item_instance_id
   FROM   ahl_unit_config_headers
  WHERE  unit_config_header_id = p_uc_header_id
  AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
  AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));

Line: 77

    SELECT subject_id
    FROM   csi_ii_relationships
    WHERE position_reference is null
    START WITH object_id = p_csi_item_instance_id
        AND relationship_type_code = 'COMPONENT-OF'
        AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
        AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
    CONNECT BY PRIOR subject_id = object_id
        AND relationship_type_code = 'COMPONENT-OF'
        AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
        AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));

Line: 91

    SELECT subject_id
    FROM   csi_ii_relationships
    START WITH object_id = p_csi_item_instance_id
        AND relationship_type_code = 'COMPONENT-OF'
        AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
        AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
    CONNECT BY PRIOR subject_id = object_id
        AND relationship_type_code = 'COMPONENT-OF'
        AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
        AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));

Line: 103

    SELECT M.concatenated_segments, C.serial_number
      FROM csi_item_instances C,
           mtl_system_items_kfv M
     WHERE instance_id = p_csi_instance_id
       AND M.inventory_item_id = C.inventory_item_id
       AND M.organization_id = c.inv_master_organization_id;

Line: 112

    SELECT object_id
     FROM csi_ii_relationships
     WHERE object_id IN
      ( SELECT csi_item_instance_id
	 FROM ahl_unit_config_headers
        WHERE trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
          AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))
     START WITH subject_id = p_csi_instance_id
        AND relationship_type_code = 'COMPONENT-OF'
        AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
        AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
     CONNECT BY subject_id = PRIOR object_id
        AND relationship_type_code = 'COMPONENT-OF'
        AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
        AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));

Line: 130

 SELECT uch.name
    FROM   ahl_unit_config_headers uch
    WHERE uch.csi_item_instance_id=p_csi_instance_id;

Line: 319

  SELECT csi_item_instance_id
   FROM   ahl_unit_config_headers
  WHERE  unit_config_header_id = p_uc_header_id
  AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
  AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));

Line: 327

    SELECT 'X'
    FROM   csi_ii_relationships
    WHERE position_reference is null
    START WITH object_id = p_csi_item_instance_id
        AND relationship_type_code = 'COMPONENT-OF'
        AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
        AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
    CONNECT BY PRIOR subject_id = object_id
        AND relationship_type_code = 'COMPONENT-OF'
        AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
        AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));

Line: 466

  SELECT csi_item_instance_id
   FROM   ahl_unit_config_headers
  WHERE  unit_config_header_id = p_uc_header_id
  AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
  AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));

Line: 474

  SELECT c_csi_instance_id FROM dual
 UNION ALL
  SELECT subject_id
    FROM   csi_ii_relationships
    START WITH object_id = c_csi_instance_id
        AND relationship_type_code = 'COMPONENT-OF'
        AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
        AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
    CONNECT BY PRIOR subject_id = object_id
        AND relationship_type_code = 'COMPONENT-OF'
        AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
        AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
        AND position_reference is not null;

Line: 491

  SELECT  mc.relationship_id
    FROM  ahl_unit_config_headers uc, ahl_mc_relationships mc
    WHERE uc.master_config_id = mc.mc_header_id
       AND mc.parent_relationship_id is null
       AND uc.csi_item_instance_id = c_csi_instance_id
       AND trunc(nvl(uc.active_start_date,sysdate)) <= trunc(sysdate)
       AND trunc(sysdate) < trunc(nvl(uc.active_end_date, sysdate+1));

Line: 501

  SELECT TO_NUMBER(position_reference)
   FROM csi_ii_relationships csi
  WHERE position_reference is not null
    AND  subject_id = c_csi_instance_id
    AND relationship_type_code = 'COMPONENT-OF'
    AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
    AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));

Line: 513

     SELECT 'X'
     FROM ahl_mc_relationships mc
     WHERE mc.parent_relationship_id = c_relationship_id
       AND mc.position_necessity_code = 'MANDATORY'
       AND trunc(nvl(mc.active_start_date,sysdate)) <= trunc(sysdate)
       AND trunc(sysdate) < trunc(nvl(mc.active_end_date, sysdate+1))
       AND  NOT EXISTS (
       SELECT 'X'
         FROM   csi_ii_relationships csi
        WHERE  object_id = c_parent_instance_id
          AND TO_NUMBER(position_reference) = mc.relationship_id
          AND relationship_type_code = 'COMPONENT-OF'
          AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
          AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)));

Line: 684

  SELECT csi_item_instance_id
   FROM   ahl_unit_config_headers
  WHERE  unit_config_header_id = p_uc_header_id
  AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
  AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));

Line: 692

  SELECT c_csi_instance_id FROM dual
 UNION ALL
  SELECT subject_id
    FROM   csi_ii_relationships
    START WITH object_id = c_csi_instance_id
        AND relationship_type_code = 'COMPONENT-OF'
        AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
        AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
    CONNECT BY PRIOR subject_id = object_id
        AND relationship_type_code = 'COMPONENT-OF'
        AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
        AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
	AND position_reference is not null;

Line: 708

  SELECT  mc.relationship_id
    FROM  ahl_unit_config_headers uc, ahl_mc_relationships mc
    WHERE uc.master_config_id = mc.mc_header_id
       AND mc.parent_relationship_id is null
       AND uc.csi_item_instance_id = c_csi_instance_id
       AND trunc(nvl(uc.active_start_date,sysdate)) <= trunc(sysdate)
       AND trunc(sysdate) < trunc(nvl(uc.active_end_date, sysdate+1));

Line: 718

  SELECT TO_NUMBER(position_reference)
   FROM csi_ii_relationships csi
  WHERE position_reference is not null
    AND  subject_id = c_csi_instance_id
    AND relationship_type_code = 'COMPONENT-OF'
    AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
    AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));

Line: 730

     SELECT mc.relationship_id, mc.position_ref_code
     FROM ahl_mc_relationships mc
     WHERE mc.parent_relationship_id = c_relationship_id
       AND mc.position_necessity_code = 'MANDATORY'
       AND trunc(nvl(mc.active_start_date,sysdate)) <= trunc(sysdate)
       AND trunc(sysdate) < trunc(nvl(mc.active_end_date, sysdate+1))
       AND  NOT EXISTS (
       SELECT 'X'
         FROM   csi_ii_relationships csi
        WHERE  object_id = c_parent_instance_id
          AND TO_NUMBER(position_reference) = mc.relationship_id
          AND relationship_type_code = 'COMPONENT-OF'
          AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
          AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)));

Line: 747

     SELECT  position_ref_code
     FROM ahl_mc_relationships
     START WITH parent_relationship_id = c_relationship_id
        AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
        AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
     CONNECT BY PRIOR relationship_id = parent_relationship_id
        AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
        AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
        AND position_necessity_code = 'MANDATORY';

Line: 961

  SELECT distinct rul.rule_id, rul.rule_name, rul.mc_header_id
  FROM AHL_MC_RULES_B rul, AHL_MC_RULE_STATEMENTS rst,
       AHL_APPLICABLE_INSTANCES ap
   WHERE  rst.rule_id = rul.rule_id
     AND rul.rule_type_code = 'MANDATORY'
     AND rst.subject_type = 'POSITION'
     AND rst.subject_id =  ap.position_id
     AND rst.object_type = 'TOT_CHILD_QUANTITY'
     AND rst.operator = 'MUST_HAVE'
     AND TRUNC(nvl(rul.ACTIVE_START_DATE, sysdate-1)) <= TRUNC(sysdate)
     AND TRUNC(nvl(rul.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate);

Line: 975

  SELECT uch.csi_item_instance_id
   FROM ahl_unit_config_headers uch
   WHERE uch.master_config_id = c_mc_header_id
   AND uch.csi_item_instance_id = c_csi_instance_id
  UNION ALL
  SELECT csi_ii.object_id
    FROM csi_ii_relationships csi_ii
    WHERE csi_ii.object_id IN
    (SELECT csi_item_instance_id
     FROM ahl_unit_config_headers
          WHERE trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
            AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
      AND  master_config_id = c_mc_header_id)
    START WITH csi_ii.subject_id = c_csi_instance_id
      AND CSI_II.RELATIONSHIP_TYPE_CODE  = 'COMPONENT-OF'
      AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
      AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
    CONNECT BY csi_ii.subject_id = PRIOR csi_ii.object_id
      AND CSI_II.RELATIONSHIP_TYPE_CODE  = 'COMPONENT-OF'
      AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
      AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate);

Line: 1013

  EXECUTE IMMEDIATE 'DELETE FROM AHL_APPLICABLE_INSTANCES';

Line: 1133

   SELECT csi_item_instance_id
     FROM ahl_unit_config_headers
    WHERE unit_config_header_id = p_uc_header_id
      AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
      AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));

Line: 1142

   SELECT c_csi_instance_id FROM dual
    WHERE NOT EXISTS (SELECT 1
                        FROM csi_ii_relationships
                       WHERE object_id = c_csi_instance_id
                         AND relationship_type_code = 'COMPONENT-OF'
                         AND trunc(nvl(active_start_date, sysdate-1)) <= trunc(sysdate)
                         AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
                      UNION ALL
                      SELECT 1
                        FROM ahl_unit_config_headers
                       WHERE csi_item_instance_id = c_csi_instance_id
                         AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
                         AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))
   UNION ALL
   SELECT OUTER.subject_id
     FROM csi_ii_relationships OUTER
    WHERE NOT EXISTS (SELECT 1
                        FROM csi_ii_relationships
                       WHERE object_id = OUTER.subject_id
                         AND relationship_type_code = 'COMPONENT-OF'
                         AND trunc(nvl(active_start_date, sysdate-1)) <= trunc(sysdate)
                         AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
                      UNION ALL
                      SELECT 1
                        FROM ahl_unit_config_headers
                       WHERE csi_item_instance_id = OUTER.subject_id
                         AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
                         AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))
    START WITH object_id = c_csi_instance_id
        AND relationship_type_code = 'COMPONENT-OF'
        AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
        AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
        AND position_reference is not null
    CONNECT BY PRIOR subject_id = object_id
        AND relationship_type_code = 'COMPONENT-OF'
        AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
        AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
        AND position_reference is not null;

Line: 1183

   SELECT TO_NUMBER(position_reference)
    FROM csi_ii_relationships csi
   WHERE position_reference is not null
     AND subject_id = c_csi_instance_id
     AND relationship_type_code = 'COMPONENT-OF'
     AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
     AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));

Line: 1194

   SELECT iasso.quantity Itm_qty,
          iasso.uom_code Itm_uom_code,
          iasso.revision Itm_revision,
          iasso.item_association_id,
          reln.quantity Posn_qty,
          reln.uom_code Posn_uom_code,
          reln.parent_relationship_id,
          reln.position_ref_code,
          csi.INVENTORY_ITEM_ID,
          csi.QUANTITY Inst_qty,
          csi.UNIT_OF_MEASURE Inst_uom_code,
          (select object_id from csi_ii_relationships
            where subject_id = c_instance_id
              and relationship_type_code = 'COMPONENT-OF'
              and trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
              and trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))) parent_instance_id
     FROM ahl_mc_relationships reln, ahl_item_associations_b iasso, csi_item_instances csi
    WHERE csi.INSTANCE_ID = c_instance_id
      AND reln.relationship_id = c_mc_relationship_id
      AND iasso.item_group_id = reln.item_group_id
      AND iasso.inventory_item_id = CSI.INVENTORY_ITEM_ID
      AND (iasso.revision IS NULL OR iasso.revision = CSI.INVENTORY_REVISION)
      AND iasso.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
      AND trunc(nvl(reln.active_start_date, sysdate)) <= trunc(sysdate)
      AND trunc(nvl(reln.active_end_date, sysdate+1)) > trunc(sysdate);

Line: 1468

   SELECT csi_item_instance_id
     FROM ahl_unit_config_headers
    WHERE unit_config_header_id = p_uc_header_id
      AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
      AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));

Line: 1477

   SELECT c_csi_instance_id FROM dual
    WHERE NOT EXISTS (SELECT 1
                        FROM csi_ii_relationships
                       WHERE object_id = c_csi_instance_id
                         AND relationship_type_code = 'COMPONENT-OF'
                         AND trunc(nvl(active_start_date, sysdate-1)) <= trunc(sysdate)
                         AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
                      UNION ALL
                      SELECT 1
                        FROM ahl_unit_config_headers
                       WHERE csi_item_instance_id = c_csi_instance_id
                         AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
                         AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))
   UNION ALL
   SELECT OUTER.subject_id
     FROM csi_ii_relationships OUTER
    WHERE NOT EXISTS (SELECT 1
                        FROM csi_ii_relationships
                       WHERE object_id = OUTER.subject_id
                         AND relationship_type_code = 'COMPONENT-OF'
                         AND trunc(nvl(active_start_date, sysdate-1)) <= trunc(sysdate)
                         AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
                      UNION ALL
                      SELECT 1
                        FROM ahl_unit_config_headers
                       WHERE csi_item_instance_id = OUTER.subject_id
                         AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
                         AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))
    START WITH object_id = c_csi_instance_id
        AND relationship_type_code = 'COMPONENT-OF'
        AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
        AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
        AND position_reference is not null
    CONNECT BY PRIOR subject_id = object_id
        AND relationship_type_code = 'COMPONENT-OF'
        AND trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
        AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
        AND position_reference is not null;

Line: 1518

   SELECT TO_NUMBER(position_reference)
    FROM csi_ii_relationships csi
   WHERE position_reference is not null
     AND subject_id = c_csi_instance_id
     AND relationship_type_code = 'COMPONENT-OF'
     AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
     AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));

Line: 1529

   SELECT iasso.quantity Itm_qty,
          iasso.uom_code Itm_uom_code,
          iasso.revision Itm_revision,
          iasso.item_association_id,
          reln.quantity Posn_qty,
          reln.uom_code Posn_uom_code,
          reln.parent_relationship_id,
          reln.position_ref_code,
          csi.INVENTORY_ITEM_ID,
          csi.QUANTITY Inst_qty,
          csi.UNIT_OF_MEASURE Inst_uom_code,
          (select object_id from csi_ii_relationships
            where subject_id = c_instance_id
              and relationship_type_code = 'COMPONENT-OF'
              and trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
              and trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))) parent_instance_id
     FROM ahl_mc_relationships reln, ahl_item_associations_b iasso, csi_item_instances csi
    WHERE csi.INSTANCE_ID = c_instance_id
      AND reln.relationship_id = c_mc_relationship_id
      AND iasso.item_group_id = reln.item_group_id
      AND iasso.inventory_item_id = CSI.INVENTORY_ITEM_ID
      AND (iasso.revision IS NULL OR iasso.revision = CSI.INVENTORY_REVISION)
      AND iasso.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
      AND trunc(nvl(reln.active_start_date, sysdate)) <= trunc(sysdate)
      AND trunc(nvl(reln.active_end_date, sysdate+1)) > trunc(sysdate);