APPS.AHL_UC_TREE_PVT SQL Statements

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

Line: 51

    SELECT 'x'
      FROM ahl_unit_config_headers U,
           ahl_mc_relationships R
     WHERE U.master_config_id = R.mc_header_id
       AND R.parent_relationship_id IS NULL
       AND U.csi_item_instance_id = p_uc_parent_rec.instance_id
       AND R.relationship_id = p_uc_parent_rec.relationship_id
       AND nvl(trunc(U.active_end_date), trunc(SYSDATE)+1) > trunc(SYSDATE)
     UNION
    SELECT 'x'
      FROM csi_ii_relationships
     WHERE subject_id =  p_uc_parent_rec.instance_id
       AND position_reference = to_char(p_uc_parent_rec.relationship_id)
       AND nvl(trunc(active_end_date), trunc(SYSDATE)+1) > trunc(SYSDATE);

Line: 66

    SELECT 'x'
      FROM ahl_mc_relationships
     WHERE relationship_id = p_uc_parent_rec.relationship_id;

Line: 70

    SELECT 'x'
      FROM csi_item_instances
     WHERE instance_id = p_uc_parent_rec.instance_id;

Line: 74

    SELECT B.relationship_id
      FROM ahl_unit_config_headers A,
           ahl_mc_relationships B
     WHERE A.master_config_id = B.mc_header_id
       AND B.parent_relationship_id IS NULL
       AND A.csi_item_instance_id = c_instance_id;

Line: 82

    SELECT 'X' node_type, subject_id instance_id, NULL relationship_id
      FROM csi_ii_relationships
     WHERE object_id = c_instance_id
       AND position_reference IS NULL
     UNION
    SELECT 'I' node_type, subject_id instance_id, to_number(position_reference) relationship_id
      FROM csi_ii_relationships
     WHERE object_id = c_instance_id
       AND position_reference IS NOT NULL
       AND nvl(trunc(active_end_date), trunc(SYSDATE)+1) > trunc(SYSDATE)
     UNION
    SELECT 'E' node_type, NULL instance_id, relationship_id
      FROM ahl_mc_relationships
     WHERE parent_relationship_id = c_relationship_id
       AND relationship_id NOT IN (SELECT position_reference
                                     FROM csi_ii_relationships
                                    WHERE object_id = c_instance_id
                                      AND position_reference IS NOT NULL
                                      AND nvl(trunc(active_end_date), trunc(SYSDATE)+1) > trunc(SYSDATE));

Line: 104

    SELECT 'E' node_type, NULL instance_id, relationship_id
      FROM ahl_mc_relationships
     WHERE parent_relationship_id = c_relationship_id;

Line: 109

    SELECT 'X' node_type, subject_id instance_id, to_number(position_reference) relationship_id
      FROM csi_ii_relationships
     WHERE object_id  = c_instance_id
       AND nvl(trunc(active_end_date), trunc(SYSDATE)+1) > trunc(SYSDATE);

Line: 233

        SELECT count(relationship_id) INTO l_children_no
          FROM ahl_mc_relationships
         WHERE parent_relationship_id = x_uc_child_tbl(j).relationship_id;

Line: 241

          SELECT count(mc_header_id) INTO l_children_no
            FROM ahl_mc_config_relations
           WHERE relationship_id = x_uc_child_tbl(j).relationship_id;

Line: 252

        SELECT count(subject_id) INTO l_children_no
          FROM csi_ii_relationships
         WHERE object_id = x_uc_child_tbl(j).instance_id;

Line: 373

    SELECT A.csi_item_instance_id instance_id,
           A.master_config_id,
           B.relationship_id,
           B.ata_code
      FROM ahl_unit_config_headers A,
           ahl_mc_relationships B
     WHERE A.unit_config_header_id = c_uc_header_id
       AND A.master_config_id = B.mc_header_id
       AND B.parent_relationship_id IS NULL;

Line: 388

    SELECT R.subject_id instance_id,
           to_number(R.position_reference) relationship_id,
           M.concatenated_segments||'-'||NVL(C.serial_number, C.instance_number) part_info,
           'N'
      FROM csi_ii_relationships R,
           csi_item_instances C,
           mtl_system_items_kfv M
     WHERE R.object_id                              = c_instance_id
       AND R.subject_id                             = C.instance_id
       AND C.inventory_item_id                      = M.inventory_item_id
       AND C.inv_master_organization_id             = M.organization_id
       AND R.relationship_type_code                 = 'COMPONENT-OF'
       AND trunc(nvl(R.active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(R.active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 406

    SELECT 'X'
      FROM csi_ii_relationships
     WHERE object_id = c_instance_id
       AND relationship_type_code = 'COMPONENT-OF'
       AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 420

    SELECT rel.mc_header_id,
           rel.parent_relationship_id parent_rel_id,
           rel.relationship_id,
           rel.display_order,
           rel.ata_code,
           (SELECT fnd.meaning
            FROM   fnd_lookups fnd
            WHERE  fnd.lookup_type                            = 'AHL_POSITION_REFERENCE'
              AND  fnd.lookup_code                            = rel.position_ref_code
              AND  trunc(nvl(fnd.start_date_active, SYSDATE)) <= trunc(SYSDATE)
              AND  trunc(nvl(fnd.end_date_active, SYSDATE+1))  > trunc(SYSDATE)
           ) pos_ref_meaning,
           (SELECT fnd.meaning
            FROM   fnd_lookups fnd
            WHERE  fnd.lookup_type                             = 'AHL_POSITION_NECESSITY'
              AND  fnd.lookup_code                             = rel.position_necessity_code
              AND  trunc(nvl(fnd.start_date_active, SYSDATE)) <= trunc(SYSDATE)
              AND  trunc(nvl(fnd.end_date_active, SYSDATE+1))  > trunc(SYSDATE)
           ) pos_necessity
      FROM ahl_mc_relationships rel
     WHERE rel.parent_relationship_id                  = c_relationship_id
       AND trunc(nvl(rel.active_start_date,SYSDATE))  <= trunc(SYSDATE)
       AND trunc(nvl(rel.active_end_date, SYSDATE+1))  > trunc(SYSDATE)
  ORDER BY display_order;

Line: 448

    SELECT 'X'
      FROM ahl_mc_relationships
     WHERE parent_relationship_id = c_relationship_id
       AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 459

    SELECT rel.parent_relationship_id parent_rel_id,
           rel.relationship_id,
           rel.display_order,
           rel.ata_code,
           (SELECT fnd.meaning
            FROM   fnd_lookups fnd
            WHERE  fnd.lookup_type                            = 'AHL_POSITION_REFERENCE'
              AND  fnd.lookup_code                            = rel.position_ref_code
              AND  trunc(nvl(fnd.start_date_active, SYSDATE)) <= trunc(SYSDATE)
              AND  trunc(nvl(fnd.end_date_active, SYSDATE+1))  > trunc(SYSDATE)
           ) pos_ref_meaning,
           (SELECT fnd.meaning
            FROM   fnd_lookups fnd
            WHERE  fnd.lookup_type                             = 'AHL_POSITION_NECESSITY'
              AND  fnd.lookup_code                             = rel.position_necessity_code
              AND  trunc(nvl(fnd.start_date_active, SYSDATE)) <= trunc(SYSDATE)
              AND  trunc(nvl(fnd.end_date_active, SYSDATE+1))  > trunc(SYSDATE)
           ) pos_necessity
      FROM ahl_mc_relationships rel
START WITH rel.parent_relationship_id                  = c_relationship_id
       AND trunc(nvl(rel.active_start_date,SYSDATE))  <= trunc(SYSDATE)
       AND trunc(nvl(rel.active_end_date, SYSDATE+1))  > trunc(SYSDATE)
CONNECT BY rel.parent_relationship_id                  = PRIOR rel.relationship_id
       AND trunc(nvl(rel.active_start_date,SYSDATE))  <= trunc(SYSDATE)
       AND trunc(nvl(rel.active_end_date, SYSDATE+1))  > trunc(SYSDATE)
    ORDER BY LEVEL, display_order;

Line: 492

    SELECT A.unit_config_header_id,
           A.master_config_id,
           B.relationship_id
      FROM ahl_unit_config_headers A,
           ahl_mc_relationships B
     WHERE A.csi_item_instance_id = c_instance_id
       AND trunc(nvl(A.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
       AND B.mc_header_id = A.master_config_id
       AND B.parent_relationship_id IS NULL
       AND trunc(nvl(B.active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(B.active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 507

    SELECT 'X'
      FROM ahl_mc_config_relations
     WHERE relationship_id = c_relationship_id
       AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
       AND rownum = 1;

Line: 517

    SELECT fnd.meaning
    FROM   ahl_mc_relationships rel, fnd_lookups fnd
    WHERE  rel.relationship_id                         = c_relationship_id AND
           fnd.lookup_code                             = rel.position_ref_code AND
           fnd.lookup_type                             = 'AHL_POSITION_REFERENCE' AND
           TRUNC(NVL(fnd.start_date_active, SYSDATE)) <= TRUNC(SYSDATE) AND
           TRUNC(NVL(fnd.end_date_active, SYSDATE+1))  > TRUNC(SYSDATE);

Line: 529

SELECT  TO_NUMBER(NULL) PARENT_INSTANCE_ID,
        B.CSI_ITEM_INSTANCE_ID INSTANCE_ID,
        A.RELATIONSHIP_ID,
         'I' NODE_TYPE,
        0 OWN_LEVEL
FROM    AHL_UNIT_CONFIG_HEADERS B,
        AHL_MC_RELATIONSHIPS A
WHERE   B.UNIT_CONFIG_HEADER_ID = p_uc_header_id  /*UC header id*/
    AND B.CSI_ITEM_INSTANCE_ID  = c_instance_id  /*root instance id*/
    AND A.MC_HEADER_ID          = B.MASTER_CONFIG_ID
    AND A.PARENT_RELATIONSHIP_ID IS NULL
UNION ALL
SELECT  OBJECT_ID PARENT_INSTANCE_ID,
        SUBJECT_ID INSTANCE_ID,
        TO_NUMBER(POSITION_REFERENCE) RELATIONSHIP_ID,
        DECODE(POSITION_REFERENCE, NULL, 'X', 'I') NODE_TYPE,
        LEVEL OWN_LEVEL
FROM    CSI_II_RELATIONSHIPS A
WHERE   (
         EXISTS (SELECT 'x'
                 FROM    CSI_II_RELATIONSHIPS B
                 WHERE   B.OBJECT_ID                              = A.SUBJECT_ID
                   AND   B.RELATIONSHIP_TYPE_CODE                 = 'COMPONENT-OF'
                   AND   TRUNC(NVL(B.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
                   AND   TRUNC(NVL(B.ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
               )
        )
        OR
        (
         EXISTS (SELECT 'x'
                 FROM    AHL_MC_RELATIONSHIPS D
                 WHERE   D.PARENT_RELATIONSHIP_ID = TO_NUMBER(A.POSITION_REFERENCE)
                   AND   TRUNC(NVL(D.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
                   AND   TRUNC(NVL(D.ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
                )
         OR
         EXISTS (SELECT 'x'
                 FROM    AHL_MC_RELATIONSHIPS D
                 WHERE   D.RELATIONSHIP_ID      = TO_NUMBER(A.POSITION_REFERENCE)
                   AND   EXISTS (SELECT 'x'
                                 FROM    AHL_UNIT_CONFIG_HEADERS E
                                 WHERE   CSI_ITEM_INSTANCE_ID                     = A.SUBJECT_ID
                                   AND   TRUNC(NVL(E.ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
                                )
                   AND   TRUNC(NVL(D.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
                   AND   TRUNC(NVL(D.ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
                )
        )
START WITH OBJECT_ID                           = c_instance_id  /*root instance id*/
    AND RELATIONSHIP_TYPE_CODE                 = 'COMPONENT-OF'
    AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
    AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID
    AND RELATIONSHIP_TYPE_CODE                 = 'COMPONENT-OF'
    AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
    AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
ORDER BY OWN_LEVEL, NODE_TYPE;

Line: 594

    SELECT F.meaning
      FROM ahl_mc_relationships A,
           fnd_lookup_values_vl F
     WHERE A.relationship_id = c_relationship_id
       AND A.position_necessity_code = F.lookup_code (+)
       AND F.lookup_type (+) = 'AHL_POSITION_NECESSITY';

Line: 624

      SELECT M.concatenated_segments||'-'||NVL(C.serial_number, C.instance_number) part_info
        FROM mtl_system_items_kfv M,
             csi_item_instances C
       WHERE C.instance_id = c_instance_id
         AND C.inventory_item_id = M.inventory_item_id
         AND C.inv_master_organization_id = M.organization_id;

Line: 767

        l_uc_children_tbl.DELETE;

Line: 781

        l_child_inst_tbl.DELETE;

Line: 782

        l_child_rel_tbl.DELETE;

Line: 783

        l_child_matchflag_tbl.DELETE;

Line: 784

        l_child_partinfo_tbl.DELETE;

Line: 1159

    SELECT object_id, subject_id, position_reference
      FROM csi_ii_relationships
     WHERE subject_id IN (SELECT csi_item_instance_id
                            FROM ahl_unit_config_headers
                           WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
        OR position_reference IS NULL
START WITH subject_id = c_instance_id
       AND relationship_type_code = 'COMPONENT-OF'
       AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY subject_id = PRIOR object_id
       AND relationship_type_code = 'COMPONENT-OF'
       AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
       AND subject_id <> c_top_instance_id;

Line: 1177

    SELECT U.unit_config_status_code uc_status_code,
           U.active_uc_status_code,
           U.csi_item_instance_id instance_id,
           U.master_config_id mc_header_id,
           U.object_version_number,
           C.inventory_item_id inventory_item_id,
           C.inv_master_organization_id inventory_org_id,
           C.inventory_revision,
           C.quantity,
           C.unit_of_measure
      FROM ahl_unit_config_headers U,
           csi_item_instances C
     WHERE U.unit_config_header_id = p_uc_header_id
       AND trunc(nvl(U.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
       AND U.csi_item_instance_id = C.instance_id;

Line: 1194

    SELECT H.mc_header_id,
           H.config_status_code,
           R.relationship_id,
           H.name,
           H.revision
      FROM ahl_mc_headers_b H,
           ahl_mc_relationships R
     WHERE H.mc_header_id = p_mc_header_id
       AND R.mc_header_id = H.mc_header_id
       AND R.parent_relationship_id IS NULL
       AND trunc(nvl(R.active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(R.active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 1207

    SELECT M.relationship_id,
           I.inventory_item_id,
           I.inventory_org_id
      FROM ahl_mc_relationships M,
           ahl_item_associations_b I
     WHERE M.mc_header_id = c_mc_header_id
       AND M.parent_relationship_id IS NULL
       AND M.item_group_id = I.item_group_id;

Line: 1217

    SELECT inventory_item_id,
           inventory_org_id
      FROM ahl_item_associations_b
     WHERE item_group_id = c_item_group_id;

Line: 1226

    SELECT C.relationship_id csi_ii_relationship_id,
           C.object_version_number csi_ii_relationship_ovn,
           C.object_id parent_instance_id,
           C.subject_id instance_id,
           to_number(C.position_reference) relationship_id,
           M.position_key,
           M.position_ref_code,
           I.inventory_item_id,
           I.inv_master_organization_id inventory_org_id,
           I.inventory_revision,
           I.quantity,
           I.unit_of_measure
      FROM csi_ii_relationships C,
           ahl_mc_relationships M,
           csi_item_instances I
     WHERE to_number(C.position_reference) = M.relationship_id (+)
       AND C.subject_id = I.instance_id
       AND C.object_id = c_instance_id
       AND C.relationship_type_code = 'COMPONENT-OF'
       AND trunc(nvl(C.active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(C.active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 1249

    SELECT parent_relationship_id parent_rel_id,
           relationship_id,
           position_key,
           position_ref_code,
           item_group_id
      FROM ahl_mc_relationships
     WHERE parent_relationship_id = c_relationship_id
       AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 1261

    SELECT A.master_config_id mc_header_id,
           B.relationship_id
      FROM ahl_unit_config_headers A,
           ahl_mc_relationships B
     WHERE A.csi_item_instance_id = c_instance_id
       AND trunc(nvl(A.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
       AND B.mc_header_id = A.master_config_id
       AND B.parent_relationship_id IS NULL
       AND trunc(nvl(B.active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(B.active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 1274

    SELECT mc_header_id
      FROM ahl_mc_config_relations
     WHERE relationship_id = c_relationship_id
       AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 1281

    SELECT TO_NUMBER(NULL) parent_instance_id, --just include the root uc node
           B.csi_item_instance_id instance_id,
           A.relationship_id
      FROM ahl_unit_config_headers B,
           ahl_mc_relationships A
     WHERE B.csi_item_instance_id = c_instance_id
       AND trunc(nvl(B.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
       AND A.mc_header_id = B.master_config_id
       AND A.parent_relationship_id IS NULL
     UNION ALL
    SELECT object_id parent_instance_id,
           subject_id instance_id,
           to_number(position_reference) relationship_id
      FROM csi_ii_relationships A
      --remove all of the leaf node after finishing the hierarchical query
     WHERE EXISTS (SELECT 'x'
                     FROM csi_ii_relationships B
                    WHERE B.object_id = A.subject_id
                      AND relationship_type_code = 'COMPONENT-OF'
                      AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
                      AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
START WITH object_id = c_instance_id
       AND relationship_type_code = 'COMPONENT-OF'
       AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY object_id = PRIOR subject_id
       AND relationship_type_code = 'COMPONENT-OF'
       AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 1317

    SELECT relationship_id csi_ii_relationship_id,
           object_version_number csi_ii_relationship_ovn,
           to_number(position_reference) relationship_id
      FROM csi_ii_relationships
     WHERE object_id = c_object_id
       AND subject_id = c_subject_id
       AND relationship_type_code = 'COMPONENT-OF'
       AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 1329

    SELECT relationship_id csi_ii_relationship_id,
           object_version_number csi_ii_relationship_ovn,
           to_number(position_reference) relationship_id
      FROM csi_ii_relationships
     WHERE subject_id = c_subject_id
    START WITH object_id = c_object_id
       AND relationship_type_code = 'COMPONENT-OF'
       AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
    CONNECT BY object_id = PRIOR subject_id
       AND relationship_type_code = 'COMPONENT-OF'
       AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 1447

  AHL_UTIL_UC_PKG.getcsi_transaction_id('UC_UPDATE',l_transaction_type_id, l_return_value);

Line: 1533

          CSI_II_RELATIONSHIPS_PUB.update_relationship(
                                   p_api_version      => 1.0,
                                   p_relationship_tbl => l_csi_relationship_tbl,
                                   p_txn_rec          => l_csi_transaction_rec,
                                   x_return_status    => l_return_status,
                                   x_msg_count        => l_msg_count,
                                   x_msg_data         => l_msg_data);

Line: 1577

    UPDATE ahl_unit_config_headers
       SET master_config_id = p_mc_header_id,
           last_updated_by = fnd_global.user_id,
           last_update_date = SYSDATE,
           last_update_login = fnd_global.login_id
     WHERE unit_config_header_id = p_uc_header_id
       AND object_version_number = l_uc_header_attr.object_version_number;

Line: 1585

    UPDATE ahl_unit_config_headers
       SET master_config_id = p_mc_header_id,
           object_version_number = object_version_number + 1,
           last_updated_by = fnd_global.user_id,
           last_update_date = SYSDATE,
           last_update_login = fnd_global.login_id
     WHERE unit_config_header_id = p_uc_header_id
       AND object_version_number = l_uc_header_attr.object_version_number;

Line: 1629

        SELECT to_number(position_reference) into l_new_relationship_id
          FROM csi_ii_relationships
         WHERE subject_id = l_get_non_leaf_node.instance_id
           AND relationship_type_code='COMPONENT-OF'
           AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
           AND trunc(nvl(active_end_date, sysdate+1)) > trunc(sysdate);

Line: 1713

          CSI_II_RELATIONSHIPS_PUB.update_relationship(
                                   p_api_version      => 1.0,
                                   p_relationship_tbl => l_csi_relationship_tbl,
                                   p_txn_rec          => l_csi_transaction_rec,
                                   x_return_status    => l_return_status,
                                   x_msg_count        => l_msg_count,
                                   x_msg_data         => l_msg_data);

Line: 1756

  select object_version_number into l_root_uc_ovn
    from ahl_unit_config_headers
   where unit_config_header_id = l_root_uc_header_id;

Line: 1763

    UPDATE ahl_unit_config_headers
       SET unit_config_status_code = 'COMPLETE',
           active_uc_status_code = 'UNAPPROVED',
           object_version_number = object_version_number + 1,
           last_updated_by = fnd_global.user_id,
           last_update_date = SYSDATE,
           last_update_login = fnd_global.login_id
     WHERE unit_config_header_id = l_root_uc_header_id
       AND object_version_number = l_root_uc_ovn;

Line: 1786

    UPDATE ahl_unit_config_headers
       SET unit_config_status_code = 'INCOMPLETE',
           active_uc_status_code = 'UNAPPROVED',
           object_version_number = object_version_number + 1,
           last_updated_by = fnd_global.user_id,
           last_update_date = SYSDATE,
           last_update_login = fnd_global.login_id
     WHERE unit_config_header_id = l_root_uc_header_id
       AND object_version_number = l_root_uc_ovn;

Line: 1811

    UPDATE ahl_unit_config_headers
       SET active_uc_status_code = 'UNAPPROVED',
           object_version_number = object_version_number + 1,
           last_updated_by = fnd_global.user_id,
           last_update_date = SYSDATE,
           last_update_login = fnd_global.login_id
     WHERE unit_config_header_id = l_root_uc_header_id
       AND object_version_number = l_root_uc_ovn;

Line: 1934

    SELECT object_id, subject_id, position_reference
      FROM csi_ii_relationships
     WHERE subject_id IN (SELECT csi_item_instance_id
                            FROM ahl_unit_config_headers
                           WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
        OR position_reference IS NULL
START WITH subject_id = c_instance_id
       AND relationship_type_code = 'COMPONENT-OF'
       AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY subject_id = PRIOR object_id
       AND subject_id <> c_top_instance_id
       --This hierarchy query is from bottom up and the top node is not a UC root node, so we
       --have to discontinue the hierarchy query when it comes to the given node
       AND relationship_type_code = 'COMPONENT-OF'
       AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 1954

    SELECT R.object_id instance_id,
           to_number(R.position_reference) relationship_id,
           C.inventory_item_id inventory_item_id,
           C.inv_master_organization_id inventory_org_id,
           C.inventory_revision,
           C.quantity,
           C.unit_of_measure
      FROM csi_ii_relationships R,
           csi_item_instances C
     WHERE R.object_id = p_instance_id
       AND R.object_id = C.instance_id
       AND R.relationship_type_code = 'COMPONENT-OF'
       AND trunc(nvl(R.active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(R.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
       AND NOT EXISTS (SELECT 'X'
                         FROM ahl_unit_config_headers
                        WHERE csi_item_instance_id = R.object_id
                          AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE));

Line: 1975

    SELECT M.relationship_id,
           I.inventory_item_id,
           I.inventory_org_id
      FROM ahl_mc_relationships M,
           ahl_item_associations_b I
     WHERE M.relationship_id = p_relationship_id
       AND M.item_group_id = I.item_group_id
       AND trunc(nvl(M.active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(M.active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 1988

    SELECT inventory_item_id,
           inventory_org_id
      FROM ahl_item_associations_b
     WHERE item_group_id = c_item_group_id;

Line: 1996

    SELECT C.relationship_id csi_ii_relationship_id,
           C.object_version_number csi_ii_relationship_ovn,
           C.object_id parent_instance_id,
           C.subject_id instance_id,
           to_number(C.position_reference) relationship_id,
           M.position_key,
           M.position_ref_code,
           I.inventory_item_id,
           I.inv_master_organization_id inventory_org_id,
           I.inventory_revision,
           I.quantity,
           I.unit_of_measure
      FROM csi_ii_relationships C,
           ahl_mc_relationships M,
           csi_item_instances I
     WHERE to_number(C.position_reference) = M.relationship_id (+)
       AND C.subject_id = I.instance_id
       AND C.object_id = c_instance_id
       AND C.relationship_type_code = 'COMPONENT-OF'
       AND trunc(nvl(C.active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(C.active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 2019

    SELECT parent_relationship_id parent_rel_id,
           relationship_id,
           position_key,
           position_ref_code,
           item_group_id
      FROM ahl_mc_relationships
     WHERE parent_relationship_id = c_relationship_id
       AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 2031

    SELECT A.master_config_id mc_header_id,
           B.relationship_id
      FROM ahl_unit_config_headers A,
           ahl_mc_relationships B
     WHERE A.csi_item_instance_id = c_instance_id
       AND trunc(nvl(A.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
       AND B.mc_header_id = A.master_config_id
       AND B.parent_relationship_id IS NULL
       AND trunc(nvl(B.active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(B.active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 2044

    SELECT mc_header_id
      FROM ahl_mc_config_relations
     WHERE relationship_id = c_relationship_id
       AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 2051

    SELECT object_id parent_instance_id,
           subject_id instance_id,
           to_number(position_reference) relationship_id
      FROM csi_ii_relationships A
      --remove all of the leaf node after finishing the hierarchical query
     WHERE EXISTS (SELECT 'X'
                     FROM csi_ii_relationships B
                    WHERE B.object_id = A.subject_id
                      AND relationship_type_code = 'COMPONENT-OF'
                      AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
                      AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
START WITH subject_id = c_instance_id
       AND relationship_type_code = 'COMPONENT-OF'
       AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY object_id = PRIOR subject_id
       AND relationship_type_code = 'COMPONENT-OF'
       AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 2162

        SELECT to_number(position_reference) into l_new_relationship_id
          FROM csi_ii_relationships
         WHERE subject_id = l_get_non_leaf_node.instance_id
           AND relationship_type_code='COMPONENT-OF'
           AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
           AND trunc(nvl(active_end_date, sysdate+1)) > trunc(sysdate);

Line: 2242

          AHL_UTIL_UC_PKG.getcsi_transaction_id('UC_UPDATE',l_transaction_type_id, l_return_value);

Line: 2257

          CSI_II_RELATIONSHIPS_PUB.update_relationship(
                                   p_api_version      => 1.0,
                                   p_relationship_tbl => l_csi_relationship_tbl,
                                   p_txn_rec          => l_csi_transaction_rec,
                                   x_return_status    => l_return_status,
                                   x_msg_count        => l_msg_count,
                                   x_msg_data         => l_msg_data);

Line: 2338

   SELECT subject_id
     FROM csi_ii_relationships
    WHERE object_id = p_instance_id
      AND relationship_type_code = 'COMPONENT-OF'
      AND position_reference IS NULL
      AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
      AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
      AND NOT EXISTS (SELECT 'X'
                        FROM ahl_unit_config_headers
                       WHERE csi_item_instance_id = subject_id
                         AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE));

Line: 2352

   SELECT subject_id, position_reference
     FROM csi_ii_relationships
    WHERE object_id = p_instance_id
      AND relationship_type_code = 'COMPONENT-OF'
      AND position_reference IS NOT NULL
      AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
      AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
      AND NOT EXISTS (SELECT 'X'
                        FROM ahl_unit_config_headers
                       WHERE csi_item_instance_id = subject_id
                         AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE));

Line: 2366

   SELECT 1 from ahl_mc_relationships
   where relationship_id = c_child_relationship_id
     AND parent_relationship_id = p_relationship_id
     AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
     AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 2374

   SELECT relationship_id
     FROM ahl_mc_relationships
     WHERE parent_relationship_id = p_relationship_id
       AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
   MINUS
   SELECT to_number(position_reference) relationship_id
    FROM csi_ii_relationships relationship_id
    WHERE object_id = p_instance_id
     AND relationship_type_code = 'COMPONENT-OF'
     AND position_reference IS NOT NULL
     AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
     AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 2389

   SELECT C.relationship_id,
          C.object_version_number,
          C.object_id,
          C.subject_id
     FROM csi_ii_relationships C
    WHERE C.subject_id = c_instance_id
      AND C.relationship_type_code = 'COMPONENT-OF'
      AND trunc(nvl(C.active_start_date,SYSDATE)) <= trunc(SYSDATE)
      AND trunc(nvl(C.active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 2421

  AHL_UTIL_UC_PKG.getcsi_transaction_id('UC_UPDATE', l_transaction_type_id, l_return_value);

Line: 2454

                       'About to call CSI_II_RELATIONSHIPS_PUB.update_relationship to update CSI II relationship with id ' ||
                       l_ii_rel_dtls.relationship_id || ' between ' ||
                       l_ii_rel_dtls.object_id || ' (object) and ' ||
                       l_ii_rel_dtls.subject_id || '(subject) with NULL position_reference ');

Line: 2459

      CSI_II_RELATIONSHIPS_PUB.update_relationship(
                               p_api_version      => 1.0,
                               p_relationship_tbl => l_csi_relationship_tbl,
                               p_txn_rec          => l_csi_transaction_rec,
                               x_return_status    => l_return_status,
                               x_msg_count        => l_msg_count,
                               x_msg_data         => l_msg_data);

Line: 2536

   SELECT inventory_item_id,
          inv_master_organization_id,
          inventory_revision,
          quantity,
          unit_of_measure
     FROM csi_item_instances
     WHERE instance_id = c_instance_id;

Line: 2545

   SELECT C.relationship_id,
          C.object_version_number,
          C.object_id,
          C.subject_id
     FROM csi_ii_relationships C
    WHERE C.subject_id = c_instance_id
      AND C.relationship_type_code = 'COMPONENT-OF'
      AND trunc(nvl(C.active_start_date,SYSDATE)) <= trunc(SYSDATE)
      AND trunc(nvl(C.active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 2581

  AHL_UTIL_UC_PKG.getcsi_transaction_id('UC_UPDATE', l_transaction_type_id, l_return_value);

Line: 2635

      p_x_extra_instances_tbl.DELETE(i);

Line: 2669

                         'About to call CSI_II_RELATIONSHIPS_PUB.update_relationship to update CSI II relationship with id ' ||
                         l_ii_rel_dtls.relationship_id || ' between ' ||
                         l_ii_rel_dtls.object_id || ' (object) and ' ||
                         l_ii_rel_dtls.subject_id || '(subject) with position_reference ' ||
                         to_char(l_current_position));

Line: 2675

        CSI_II_RELATIONSHIPS_PUB.update_relationship(
                                 p_api_version      => 1.0,
                                 p_relationship_tbl => l_csi_relationship_tbl,
                                 p_txn_rec          => l_csi_transaction_rec,
                                 x_return_status    => l_return_status,
                                 x_msg_count        => l_msg_count,
                                 x_msg_data         => l_msg_data);

Line: 2702

            p_x_relations_tbl.DELETE(j);

Line: 2708

        p_x_extra_instances_tbl.DELETE(i);

Line: 2716

  l_map_tbl.DELETE;  -- Clear up the temporary Associative Array