APPS.AHL_SB_RULES_PVT SQL Statements

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

Line: 54

    SELECT name
    FROM   AHL_UNIT_CONFIG_HEADERS
    WHERE  unit_config_header_id                   = c_uc_header_id
    AND    TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
    AND    TRUNC(NVL(active_end_date, SYSDATE+1))  > TRUNC(SYSDATE);

Line: 62

    SELECT 'X'
    FROM   AHL_VISITS_B
    WHERE  visit_id = c_visit_id;

Line: 68

    SELECT SB.rule_id,
           SB.rule_name,
           SB.relationship_id
    FROM   AHL_SB_POSITION_RULES SB,
           AHL_UNIT_CONFIG_HEADERS UC
    WHERE  UC.unit_config_header_id = c_uc_header_id
    AND    SB.mc_header_id          = UC.master_config_id
    -- take only the rules which have MRs accomplished on the unit
    AND    EXISTS (SELECT 'X'
                   FROM   AHL_UNIT_EFFECTIVITIES_B UE
                   WHERE  UE.mr_header_id         = SB.mr_header_id
                   AND    UE.csi_item_instance_id = UC.csi_item_instance_id -- SB MRs are effective on root node
                   AND    UE.accomplished_date    IS NOT NULL)
    -- take only the MAX sequence rules for a position, among the accomplished MRs
    AND    SB.rule_sequence = (SELECT MAX(SB2.rule_sequence)
                               FROM   AHL_SB_POSITION_RULES SB2
                               WHERE  SB2.relationship_id = SB.relationship_id
                               AND    EXISTS (SELECT 'X'
                                              FROM   AHL_UNIT_EFFECTIVITIES_B UE
                                              WHERE  UE.mr_header_id         = SB2.mr_header_id
                                              AND    UE.csi_item_instance_id = UC.csi_item_instance_id
                                              AND    UE.accomplished_date    IS NOT NULL))
    AND    TRUNC(NVL(SB.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
    AND    TRUNC(NVL(SB.active_end_date, SYSDATE+1))  > TRUNC(SYSDATE);

Line: 96

    SELECT SB.rule_id,
           SB.rule_name,
           SB.relationship_id
    FROM   AHL_SB_POSITION_RULES SB,
           AHL_UNIT_CONFIG_HEADERS UC
    WHERE  UC.unit_config_header_id = c_uc_header_id
    AND    SB.mc_header_id          = UC.master_config_id
    -- take only the rules which have MRs accomplished on the unit
    AND    EXISTS (SELECT 'X'
                   FROM   AHL_UNIT_EFFECTIVITIES_B UE
                   WHERE  UE.mr_header_id         = SB.mr_header_id
                   AND    UE.csi_item_instance_id = UC.csi_item_instance_id -- SB MRs are effective on root node
                   AND    UE.accomplished_date    IS NOT NULL)
    -- take only the MAX sequence rules for a position, among the accomplished MRs
    AND    SB.rule_sequence = (SELECT MAX(SB2.rule_sequence)
                               FROM   AHL_SB_POSITION_RULES SB2
                               WHERE  SB2.relationship_id = SB.relationship_id
                               AND    EXISTS (SELECT 'X'
                                              FROM   AHL_UNIT_EFFECTIVITIES_B UE
                                              WHERE  UE.mr_header_id         = SB2.mr_header_id
                                              AND    UE.csi_item_instance_id = UC.csi_item_instance_id
                                              AND    UE.accomplished_date    IS NOT NULL))
    AND    TRUNC(NVL(SB.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
    AND    TRUNC(NVL(SB.active_end_date, SYSDATE+1))  > TRUNC(SYSDATE)
    UNION
    SELECT SB.rule_id,
           SB.rule_name,
           SB.relationship_id
    FROM   AHL_SB_POSITION_RULES SB,
           AHL_VISIT_TASKS_B VT,
           AHL_UNIT_CONFIG_HEADERS UC
    WHERE  UC.unit_config_header_id = c_uc_header_id
    AND    SB.mc_header_id          = UC.master_config_id
    -- take only the rules which have MRs attached to the visit
    AND    VT.visit_id              = c_visit_id
    AND    VT.mr_id                 = SB.mr_header_id
    AND    VT.status_code           NOT IN ('CANCELLED', 'DELETED')
    -- take only the MAX sequence rules for a position
    AND    SB.rule_sequence = (SELECT MAX(SB2.rule_sequence)
                               FROM   AHL_SB_POSITION_RULES SB2
                               WHERE  SB2.relationship_id = SB.relationship_id
                               AND    SB2.mr_header_id    = SB.mr_header_id)
    AND    TRUNC(NVL(SB.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
    AND    TRUNC(NVL(SB.active_end_date, SYSDATE+1))  > TRUNC(SYSDATE);

Line: 145

    SELECT 'X'
    FROM   AHL_SB_RULE_ITEMS SBI,
           AHL_ITEM_ASSOCIATIONS_B IAB,
           CSI_ITEM_INSTANCES CSI,
           CSI_II_RELATIONSHIPS CSR
    WHERE  SBI.rule_id                                         = c_rule_id
    AND    IAB.item_association_id                             = SBI.item_association_id
    AND    CSI.inventory_item_id                               = IAB.inventory_item_id
    AND    CSI.inv_master_organization_id                      = IAB.inventory_org_id
    AND    CSR.subject_id                                      = CSI.instance_id -- SB rules are created for non-root positions
    AND    CSR.position_reference                              = TO_CHAR(c_relationship_id)
    AND    CSR.relationship_type_code                          = 'COMPONENT-OF'
    AND    TRUNC(NVL(CSR.active_end_date, SYSDATE+1))          > TRUNC(SYSDATE)
    AND    AHL_UTIL_UC_PKG.get_sub_uc_header_id(CSR.object_id) = c_uc_header_id;

Line: 366

    SELECT 'X'
    FROM   CSI_ITEM_INSTANCES
    WHERE  instance_id                             = c_instance_id
    AND    TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
    AND    TRUNC(NVL(active_end_date, SYSDATE+1))  > TRUNC(SYSDATE);

Line: 374

    SELECT name
    FROM   AHL_UNIT_CONFIG_HEADERS
    WHERE  unit_config_header_id                   = c_uc_header_id
    AND    TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
    AND    TRUNC(NVL(active_end_date, SYSDATE+1))  > TRUNC(SYSDATE);

Line: 383

    SELECT SB.rule_id,
           SB.rule_name,
           SB.relationship_id
    FROM   AHL_SB_POSITION_RULES SB,
           AHL_UNIT_CONFIG_HEADERS UC
    WHERE  UC.unit_config_header_id = c_uc_header_id
    AND    SB.mc_header_id          = UC.master_config_id
    -- take only the rules which have MRs accomplished on the unit
    AND    EXISTS (SELECT 'X'
                   FROM   AHL_UNIT_EFFECTIVITIES_B UE
                   WHERE  UE.mr_header_id         = SB.mr_header_id
                   AND    UE.csi_item_instance_id = UC.csi_item_instance_id -- SB MRs are effective on root node
                   AND    UE.accomplished_date    IS NOT NULL)
    -- take only the MAX sequence rules for a position, among the accomplished MRs
    AND    SB.rule_sequence = (SELECT MAX(SB2.rule_sequence)
                               FROM   AHL_SB_POSITION_RULES SB2
                               WHERE  SB2.relationship_id = SB.relationship_id
                               AND    EXISTS (SELECT 'X'
                                              FROM   AHL_UNIT_EFFECTIVITIES_B UE
                                              WHERE  UE.mr_header_id         = SB2.mr_header_id
                                              AND    UE.csi_item_instance_id = UC.csi_item_instance_id
                                              AND    UE.accomplished_date    IS NOT NULL))
    AND    EXISTS (SELECT 'X'
                   FROM   CSI_II_RELATIONSHIPS
                   WHERE  subject_id                             = c_instance_id -- SB rules are created for non-root positions
                   AND    TO_NUMBER(position_reference)          = SB.relationship_id
                   AND    relationship_type_code                 = 'COMPONENT-OF'
                   AND    TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE))
    AND    TRUNC(NVL(SB.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
    AND    TRUNC(NVL(SB.active_end_date, SYSDATE+1))  > TRUNC(SYSDATE);

Line: 417

    SELECT 'X'
    FROM   AHL_SB_RULE_ITEMS SBI,
           AHL_ITEM_ASSOCIATIONS_B IAB,
           CSI_ITEM_INSTANCES CSI
    WHERE  SBI.rule_id                    = c_rule_id
    AND    IAB.item_association_id        = SBI.item_association_id
    AND    CSI.inventory_item_id          = IAB.inventory_item_id
    AND    CSI.inv_master_organization_id = IAB.inventory_org_id
    AND    CSI.instance_id                = c_instance_id;

Line: 635

    SELECT relationship_id
    FROM   AHL_MC_RELATIONSHIPS
    WHERE  mc_header_id                            = c_mc_header_id
    AND    position_key                            = c_pos_key
    AND    TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
    AND    TRUNC(NVL(active_end_date, SYSDATE+1))  > TRUNC(SYSDATE);

Line: 645

    SELECT unit_config_header_id,
           name
    FROM   AHL_UNIT_CONFIG_HEADERS
    WHERE  master_config_id                        = c_mc_header_id
    AND    TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
    AND    TRUNC(NVL(active_end_date, SYSDATE+1))  > TRUNC(SYSDATE)
    AND    unit_config_header_id                   IN (SELECT unit_config_header_id
                                                       FROM   AHL_UNIT_CONFIG_HEADERS
                                                       START WITH unit_config_header_id = c_uc_header_id
                                                       CONNECT BY parent_uc_header_id   = PRIOR unit_config_header_id);

Line: 659

    SELECT SB.rule_id,
           SB.rule_name
    FROM   AHL_SB_POSITION_RULES SB,
           AHL_UNIT_CONFIG_HEADERS UC
    WHERE  UC.unit_config_header_id = c_uc_header_id
    AND    SB.mc_header_id          = UC.master_config_id
    -- take only the rules which have MRs accomplished on the unit
    AND    EXISTS (SELECT 'X'
                   FROM   AHL_UNIT_EFFECTIVITIES_B UE
                   WHERE  UE.mr_header_id         = SB.mr_header_id
                   AND    UE.csi_item_instance_id = UC.csi_item_instance_id -- SB MRs are effective on root node
                   AND    UE.accomplished_date    IS NOT NULL)
    -- take only the MAX sequence rules for a position, among the accomplished MRs
    AND    SB.rule_sequence = (SELECT MAX(SB2.rule_sequence)
                               FROM   AHL_SB_POSITION_RULES SB2
                               WHERE  SB2.relationship_id = SB.relationship_id
                               AND    EXISTS (SELECT 'X'
                                              FROM   AHL_UNIT_EFFECTIVITIES_B UE
                                              WHERE  UE.mr_header_id         = SB2.mr_header_id
                                              AND    UE.csi_item_instance_id = UC.csi_item_instance_id
                                              AND    UE.accomplished_date    IS NOT NULL))
    AND    SB.relationship_id                         = c_relationship_id
    AND    TRUNC(NVL(SB.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
    AND    TRUNC(NVL(SB.active_end_date, SYSDATE+1))  > TRUNC(SYSDATE);

Line: 688

    SELECT 'X'
    FROM   AHL_SB_RULE_ITEMS SBI,
           AHL_ITEM_ASSOCIATIONS_B IAB,
           MTL_SYSTEM_ITEMS_B MTL
    WHERE  SBI.rule_id             = c_rule_id
    AND    IAB.item_association_id = SBI.item_association_id
    AND    MTL.inventory_item_id   = IAB.inventory_item_id
    AND    IAB.inventory_item_id   = c_inv_item_id
    AND    MTL.organization_id     = c_inv_org_id;

Line: 819

    SELECT 'X'
    FROM   AHL_MR_HEADERS_B
    WHERE  mr_header_id      = c_mr_header_id
    AND    program_type_code = 'SERV_BLTN'
    AND    mr_status_code   <> 'TERMINATED';

Line: 827

    SELECT *
    FROM   AHL_SB_POSITION_RULES
    WHERE  mr_header_id = c_mr_header_id;

Line: 833

    SELECT *
    FROM   AHL_SB_RULE_ITEMS
    WHERE  rule_id = c_rule_id;

Line: 920

        INSERT INTO AHL_SB_POSITION_RULES(
            rule_id, object_version_number,
            last_update_date, last_updated_by, creation_date, created_by, last_update_login,
            rule_name, mr_header_id,
            mc_header_id, relationship_id,
            rule_sequence, active_start_date, active_end_date)
        VALUES(
            l_rule_id, 1,
            SYSDATE, FND_GLOBAL.User_Id, SYSDATE, FND_GLOBAL.User_Id, FND_GLOBAL.Login_Id,
            SUBSTR(l_copy||'_'||l_get_rule_det_csr_rec.rule_name, 0, 80), p_dst_mr_header_id,
            l_get_rule_det_csr_rec.mc_header_id, l_get_rule_det_csr_rec.relationship_id,
            l_get_rule_det_csr_rec.rule_sequence, SYSDATE, NULL);

Line: 939

            INSERT INTO AHL_SB_RULE_ITEMS(
                rule_association_id, object_version_number,
                last_update_date, last_updated_by, creation_date, created_by, last_update_login,
                rule_id, item_group_id,
                item_association_id, sub_config_item)
            VALUES(
                l_rule_association_id, 1,
                SYSDATE, FND_GLOBAL.User_Id, SYSDATE, FND_GLOBAL.User_Id, FND_GLOBAL.Login_Id,
                l_rule_id, l_get_rule_items_det_csr_rec.item_group_id,
                l_get_rule_items_det_csr_rec.item_association_id, l_get_rule_items_det_csr_rec.sub_config_item);

Line: 1018

PROCEDURE Update_Rules_For_MR (
    p_api_version                 IN             NUMBER    := 1.0,
    p_commit                      IN             VARCHAR2  := FND_API.G_FALSE,
    p_mr_header_id                IN             NUMBER,
    x_return_status               OUT    NOCOPY  VARCHAR2,
    x_msg_count                   OUT    NOCOPY  NUMBER,
    x_msg_data                    OUT    NOCOPY  VARCHAR2
) IS

-- Modified by SURRKUMA :: 12896263 :: 16-FEB-2012
-- cursor to check the given MR header id
CURSOR chk_mr_header_id_csr (c_mr_header_id NUMBER) IS
    SELECT 'X'
    FROM   AHL_MR_HEADERS_B
    WHERE  mr_header_id      = c_mr_header_id
    AND    program_type_code = 'SERV_BLTN';

Line: 1037

    SELECT master_config_id
    FROM   AHL_UNIT_CONFIG_HEADERS
    WHERE  unit_config_header_id = c_uc_header_id;

Line: 1043

    SELECT DISTINCT mc_header_id
    FROM   AHL_SB_POSITION_RULES
    WHERE  mr_header_id = c_mr_header_id;

Line: 1050

    SELECT rule_id
    FROM   AHL_SB_POSITION_RULES
    WHERE  mc_header_id = c_mc_header_id
    AND    mr_header_id = c_mr_header_id;

Line: 1057

l_api_name        CONSTANT VARCHAR2(30)  := 'Update_Rules_For_MR';

Line: 1076

    SAVEPOINT Update_Rules_For_MR_Pvt;

Line: 1169

                        DELETE FROM AHL_SB_RULE_ITEMS WHERE rule_id = l_get_rule_id_csr_rec.rule_id;

Line: 1172

                        DELETE FROM AHL_SB_POSITION_RULES WHERE rule_id = l_get_rule_id_csr_rec.rule_id;

Line: 1175

                            FND_LOG.string(l_log_statement, l_full_name, 'rule deleted = '||l_get_rule_id_csr_rec.rule_id);

Line: 1194

        ROLLBACK TO Update_Rules_For_MR_Pvt;

Line: 1204

        ROLLBACK TO Update_Rules_For_MR_Pvt;

Line: 1214

        ROLLBACK TO Update_Rules_For_MR_Pvt;

Line: 1228

END Update_Rules_For_MR;

Line: 1245

PROCEDURE Delete_Rules_For_MR (
    p_api_version                 IN             NUMBER    := 1.0,
    p_commit                      IN             VARCHAR2  := FND_API.G_FALSE,
    p_mr_header_id                IN             NUMBER,
    x_return_status               OUT    NOCOPY  VARCHAR2,
    x_msg_count                   OUT    NOCOPY  NUMBER,
    x_msg_data                    OUT    NOCOPY  VARCHAR2
) IS

-- cursor to check the given MR header id
CURSOR chk_mr_header_id_csr (c_mr_header_id NUMBER) IS
    SELECT 'X'
    FROM   AHL_MR_HEADERS_B
    WHERE  mr_header_id      = c_mr_header_id
    AND    program_type_code = 'SERV_BLTN'
    AND    mr_status_code    = 'DRAFT';

Line: 1264

    SELECT rule_id
    FROM   AHL_SB_POSITION_RULES
    WHERE  mr_header_id = c_mr_header_id;

Line: 1270

l_api_name        CONSTANT VARCHAR2(30)  := 'Delete_Rules_For_MR';

Line: 1285

    SAVEPOINT Delete_Rules_For_MR_Pvt;

Line: 1328

            DELETE FROM AHL_SB_RULE_ITEMS WHERE rule_id = l_rule_id_tbl(i);

Line: 1331

            DELETE FROM AHL_SB_POSITION_RULES WHERE rule_id = l_rule_id_tbl(i);

Line: 1346

        ROLLBACK TO Delete_Rules_For_MR_Pvt;

Line: 1356

        ROLLBACK TO Delete_Rules_For_MR_Pvt;

Line: 1366

        ROLLBACK TO Delete_Rules_For_MR_Pvt;

Line: 1380

END Delete_Rules_For_MR;

Line: 1402

PROCEDURE Update_Rules_For_IG (
    p_frk_or_mrg_flg              IN             VARCHAR2,
    p_item_group_id               IN             NUMBER,
    p_src_item_group_id           IN             NUMBER
) IS

-- cursor to check whether SB rule update is required
CURSOR chk_rule_upd_required (c_src_item_group_id NUMBER) IS
    SELECT 'X'
    FROM   AHL_SB_RULE_ITEMS
    WHERE  item_group_id = c_src_item_group_id;

Line: 1416

    SELECT 'X'
    FROM   AHL_ITEM_GROUPS_B
    WHERE  item_group_id = c_item_group_id;

Line: 1422

    SELECT DISTINCT SBR.rule_id
    FROM   AHL_SB_POSITION_RULES SBR,
           AHL_SB_RULE_ITEMS SBI
    WHERE  SBR.rule_id       = SBI.rule_id
    AND    SBI.item_group_id = c_src_item_group_id
    AND    EXISTS (SELECT 'X'
                   FROM   AHL_MC_RELATIONSHIPS MCR
                   WHERE  MCR.relationship_id    = SBR.relationship_id
                   AND    MCR.item_group_id      = c_src_item_group_id
                   AND    MCR.temp_item_group_id IS NOT NULL);

Line: 1435

    SELECT rule_id
    FROM   AHL_SB_POSITION_RULES
    WHERE  TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
    AND    TRUNC(NVL(active_end_date, SYSDATE+1))  > TRUNC(SYSDATE);

Line: 1442

    SELECT item_association_id,
           TO_CHAR(inventory_item_id)||'-'||revision||'-'||TO_CHAR(inventory_org_id) item_pattern
    FROM   AHL_ITEM_ASSOCIATIONS_B
    WHERE  item_group_id = c_item_group_id;

Line: 1449

    SELECT 'X'
    FROM   AHL_SB_RULE_ITEMS
    WHERE  rule_id = c_rule_id;

Line: 1455

    SELECT 'X'
    FROM   AHL_SB_RULE_ITEMS
    WHERE  item_association_id = c_item_asso_id;

Line: 1460

l_api_name        CONSTANT VARCHAR2(30)  := 'Update_Rules_For_IG';

Line: 1545

                DELETE FROM AHL_SB_RULE_ITEMS
                WHERE  rule_id       = l_rule_id_for_fork_tbl(i)
                AND    item_group_id = p_src_item_group_id;

Line: 1550

                    FND_LOG.string(l_log_statement, l_full_name, 'applicable rule items deleted');

Line: 1560

                    DELETE FROM AHL_SB_POSITION_RULES WHERE rule_id = l_rule_id_for_fork_tbl(i);

Line: 1563

                        FND_LOG.string(l_log_statement, l_full_name, 'rule deleted = '||l_rule_id_for_fork_tbl(i));

Line: 1597

                        DELETE FROM AHL_SB_RULE_ITEMS WHERE item_association_id = l_src_item_asso_id_tbl(i);

Line: 1598

                        l_action := 'deleted';

Line: 1601

                        UPDATE AHL_SB_RULE_ITEMS
                        SET    item_association_id = l_item_asso_id_tbl(l_src_item_pattern_tbl(i))
                        WHERE  item_association_id = l_src_item_asso_id_tbl(i);

Line: 1604

                        l_action := 'updated';

Line: 1629

                    DELETE FROM AHL_SB_POSITION_RULES WHERE rule_id = l_rule_id_for_merge_tbl(i);

Line: 1632

                        FND_LOG.string(l_log_statement, l_full_name, 'rule deleted = '||l_rule_id_for_merge_tbl(i));

Line: 1643

END Update_Rules_For_IG;

Line: 1673

    SELECT 'X'
    FROM   AHL_UNIT_CONFIG_HEADERS
    WHERE  unit_config_header_id                   = c_uc_header_id
    AND    TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
    AND    TRUNC(NVL(active_end_date, SYSDATE+1))  > TRUNC(SYSDATE);

Line: 1681

    SELECT SB.rule_id,
           SB.relationship_id
    FROM   AHL_SB_POSITION_RULES SB,
           AHL_UNIT_CONFIG_HEADERS UC
    WHERE  UC.unit_config_header_id = c_uc_header_id
    AND    SB.mc_header_id          = UC.master_config_id
    -- take only the rules which have MRs accomplished on the unit
    AND    EXISTS (SELECT 'X'
                   FROM   AHL_UNIT_EFFECTIVITIES_B UE
                   WHERE  UE.mr_header_id         = SB.mr_header_id
                   AND    UE.csi_item_instance_id = UC.csi_item_instance_id -- SB MRs are effective on root node
                   AND    UE.accomplished_date    IS NOT NULL)
    -- take only the MAX sequence rules for a position, among the accomplished MRs
    AND    SB.rule_sequence = (SELECT MAX(SB2.rule_sequence)
                               FROM   AHL_SB_POSITION_RULES SB2
                               WHERE  SB2.relationship_id = SB.relationship_id
                               AND    EXISTS (SELECT 'X'
                                              FROM   AHL_UNIT_EFFECTIVITIES_B UE
                                              WHERE  UE.mr_header_id         = SB2.mr_header_id
                                              AND    UE.csi_item_instance_id = UC.csi_item_instance_id
                                              AND    UE.accomplished_date    IS NOT NULL))
    AND    TRUNC(NVL(SB.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
    AND    TRUNC(NVL(SB.active_end_date, SYSDATE+1))  > TRUNC(SYSDATE);

Line: 1709

    SELECT 'X'
    FROM   AHL_SB_RULE_ITEMS SBI,
           AHL_ITEM_ASSOCIATIONS_B IAB,
           CSI_ITEM_INSTANCES CSI,
           CSI_II_RELATIONSHIPS CSR
    WHERE  SBI.rule_id                                         = c_rule_id
    AND    IAB.item_association_id                             = SBI.item_association_id
    AND    CSI.inventory_item_id                               = IAB.inventory_item_id
    AND    CSI.inv_master_organization_id                      = IAB.inventory_org_id
    AND    CSR.subject_id                                      = CSI.instance_id -- SB rules are created for non-root positions
    AND    CSR.position_reference                              = TO_CHAR(c_relationship_id)
    AND    CSR.relationship_type_code                          = 'COMPONENT-OF'
    AND    TRUNC(NVL(CSR.active_end_date, SYSDATE+1))          > TRUNC(SYSDATE)
    AND    AHL_UTIL_UC_PKG.get_sub_uc_header_id(CSR.object_id) = c_uc_header_id;

Line: 1727

    SELECT subject_id
    FROM   CSI_II_RELATIONSHIPS
    WHERE  position_reference                              = TO_CHAR(c_relationship_id)
    AND    relationship_type_code                          = 'COMPONENT-OF'
    AND    TRUNC(NVL(active_end_date, SYSDATE+1))          > TRUNC(SYSDATE)
    AND    AHL_UTIL_UC_PKG.get_sub_uc_header_id(object_id) = c_uc_header_id; -- SB rules are created for non-root positions

Line: 1897

    SELECT 'X'
    FROM   AHL_MR_HEADERS_B
    WHERE  mr_header_id      = c_mr_header_id
    AND    program_type_code = 'SERV_BLTN';

Line: 1904

    SELECT master_config_id
    FROM   AHL_UNIT_CONFIG_HEADERS
    WHERE  unit_config_header_id = c_uc_header_id;