APPS.AHL_UC_UTILIZATION_PVT SQL Statements

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

Line: 26

PROCEDURE update_reading_id(p_utilization_rec IN utilization_rec_type);

Line: 32

PROCEDURE update_reading_ins(p_utilization_rec IN utilization_rec_type);

Line: 38

PROCEDURE update_reading_cn(p_utilization_rec IN utilization_rec_type);

Line: 41

PROCEDURE update_reading_all(p_utilization_rec IN utilization_rec_type,
                             p_based_on        IN VARCHAR2);

Line: 98

PROCEDURE update_utilization(p_api_version      IN NUMBER,
                             p_init_msg_list    IN VARCHAR2 := FND_API.G_FALSE,
                             p_commit           IN VARCHAR2 := FND_API.G_FALSE,
                             p_validation_level IN NUMBER   := FND_API.G_VALID_LEVEL_FULL,
                             p_utilization_tbl  IN AHL_UC_UTILIZATION_PVT.utilization_tbl_type,
                             x_return_status    OUT NOCOPY VARCHAR2,
                             x_msg_count        OUT NOCOPY NUMBER,
                             x_msg_data         OUT NOCOPY VARCHAR2)
IS
  l_api_name        CONSTANT VARCHAR2(30) := 'update_utilization';

Line: 114

  L_DEBUG_KEY              CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Utilization';

Line: 127

  SAVEPOINT update_utilization;

Line: 171

                     'About to call update_reading_all for record ' || i ||
                     ', l_based_on = ' || l_based_on);

Line: 174

    update_reading_all(l_utilization_rec, l_based_on);

Line: 180

                   'After successful completion of update_reading_all calls for all records in table.');

Line: 207

    ROLLBACK to update_utilization;

Line: 213

    ROLLBACK to update_utilization;

Line: 219

    ROLLBACK to update_utilization;

Line: 222

                              p_procedure_name => 'update_utilization',
                              p_error_text     => SQLERRM);

Line: 228

END update_utilization;

Line: 237

    SELECT organization_id
      FROM mtl_parameters
     WHERE organization_code = c_org_code;

Line: 244

    SELECT inventory_item_id
      FROM ahl_mtl_items_ou_v
     WHERE concatenated_segments = c_item_number
       AND inventory_org_id = c_inv_organization_id;

Line: 251

    SELECT instance_id
      FROM csi_item_instances
     WHERE instance_number = c_instance_number;

Line: 379

    SELECT location_type_code
      FROM csi_item_instances
     WHERE instance_id = c_instance_id
       AND TRUNC(sysdate) < TRUNC(NVL(active_end_date, sysdate+1));

Line: 386

     SELECT cgrp.source_object_id
       FROM cs_counters ctr, cs_counter_groups cgrp
      WHERE cgrp.counter_group_id = ctr.counter_group_id
        AND cgrp.source_object_code = 'CP'
        AND ctr.counter_id = c_counter_id
        AND trunc(sysdate) >= trunc(nvl(ctr.start_date_active,sysdate))
        AND trunc(sysdate) < trunc(nvl(ctr.end_date_active,sysdate+1));

Line: 397

     SELECT instance_id,
            instance_usage_code,
            active_start_date,
            active_end_date
       FROM csi_item_instances csi
      WHERE inventory_item_id = c_inventory_item_id
        AND last_vld_organization_id = c_organization_id
        AND serial_number = c_serial_number;

Line: 407

     SELECT 'X'
       FROM mtl_units_of_measure_vl
      WHERE uom_code = c_uom_code;

Line: 413

     SELECT 'X'
       FROM cs_counters ctr
      WHERE ctr.name = c_counter_name
        AND trunc(sysdate) >= trunc(nvl(ctr.start_date_active,sysdate))
        AND trunc(sysdate) < trunc(nvl(ctr.end_date_active,sysdate+1));

Line: 420

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

Line: 770

PROCEDURE update_reading_id(p_utilization_rec IN utilization_rec_type) IS
  -- Get current counter reading values based on the counter_id
  CURSOR get_current_value_id(c_counter_id NUMBER) IS
 -- Changed by jaramana on 03-DEC-2008 for bug 7426643 (FP of 7263702)
 -- To take advantage of the fix made in Counters bug 7561677 (FP of 7374316)
 -- and also to ignore disabled counter readings
/*
    SELECT nvl(counter_reading,0) counter_reading,
           counter_group_id
      FROM csi_cp_counters_v
     WHERE counter_id = c_counter_id
     -- order by added by jaramana on June 13, 2007 to fix bug 6123549
     -- Since csi_cp_counters_v was modified by CSI in R12 to get all
     -- counter readings and not just the most recent one.
     ORDER BY value_timestamp desc;

Line: 786

     SELECT (select ccr.net_reading
               from csi_counter_readings ccr
              where ccr.counter_value_id = c.ctr_val_max_seq_no) counter_reading,
            DEFAULTED_GROUP_ID counter_group_id
       FROM CSI_COUNTERS_B C
      WHERE counter_id = c_counter_id;

Line: 795

    select 'Y' from CSI_COUNTERS_B
    where counter_id = c_counter_id
      and reading_type = 2;

Line: 813

  L_DEBUG_KEY       CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Reading_Id';

Line: 917

END update_reading_id;

Line: 924

PROCEDURE update_reading_ins(p_utilization_rec IN utilization_rec_type) IS
  CURSOR csi_relationships_csr(c_csi_item_instance_id NUMBER) IS
    SELECT subject_id csi_item_instance_id, position_reference
      FROM csi_ii_relationships
START WITH object_id = c_csi_item_instance_id
       AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
       AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
       AND relationship_type_code = 'COMPONENT-OF'
CONNECT BY object_id = PRIOR subject_id
       AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
       AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
       AND relationship_type_code = 'COMPONENT-OF';

Line: 942

     SELECT nvl(counter_reading,0) counter_reading,
            counter_group_id,
            uom_code
       FROM csi_cp_counters_v
      WHERE counter_id = c_counter_id;

Line: 955

     SELECT nvl(counter_reading,0) counter_reading,
            counter_group_id,
            counter_id,
            start_date_active,
            end_date_active,
            uom_code
       FROM csi_cp_counters_v
      WHERE customer_product_id = c_instance_id
        AND counter_template_name = c_counter_name
        AND trunc(nvl(start_date_active, sysdate)) <= trunc(sysdate)
        AND trunc(nvl(end_date_active, sysdate+1)) > trunc(sysdate)
   ORDER BY value_timestamp desc;

Line: 968

     SELECT C.DEFAULTED_GROUP_ID counter_group_id,
            (select ccr.net_reading
               from csi_counter_readings ccr
              where ccr.counter_value_id = c.ctr_val_max_seq_no) counter_reading,
            C.COUNTER_ID counter_id,
            C.START_DATE_ACTIVE start_date_active,
            C.END_DATE_ACTIVE end_date_active,
            C.UOM_CODE uom_code
       FROM CSI_COUNTERS_VL C, CSI_COUNTER_ASSOCIATIONS CCA
      WHERE C.COUNTER_ID = CCA.COUNTER_ID(+)
        AND CCA.SOURCE_OBJECT_CODE = 'CP'
        AND CCA.SOURCE_OBJECT_ID = c_instance_id
        AND C.COUNTER_TEMPLATE_NAME = c_counter_name
        AND trunc(nvl(C.start_date_active, sysdate)) <= trunc(sysdate)
        AND trunc(nvl(C.end_date_active, sysdate+1)) > trunc(sysdate);

Line: 988

     select 'Y' from CSI_COUNTERS_B
     where counter_id = c_counter_id
       and reading_type = 2;

Line: 1008

   L_DEBUG_KEY       CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Reading_Ins';

Line: 1058

  update_reading_id(l_utilization_rec);

Line: 1061

                   L_DEBUG_KEY, 'Returned from call to update_reading_id');

Line: 1200

END update_reading_ins;

Line: 1204

PROCEDURE update_reading_cn(p_utilization_rec IN utilization_rec_type) IS
  TYPE instance_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

Line: 1211

    SELECT CI.subject_id
      FROM csi_ii_relationships CI
     WHERE EXISTS (SELECT 'X'
/*
                     FROM cs_counters CC,
                          cs_counter_groups CG
                    WHERE CC.counter_group_id = CG.counter_group_id
                      AND CG.source_object_id = CI.subject_id
                      AND CG.source_object_code = 'CP'
                      AND CC.name = c_counter_name
                      AND trunc(nvl(CC.start_date_active,sysdate)) <= trunc(sysdate)
                      AND trunc(nvl(CC.end_date_active,sysdate+1)) > trunc(sysdate))
*/
/*
                     FROM CSI_CP_COUNTERS_V CCCV
                    WHERE CCCV.CUSTOMER_PRODUCT_ID = CI.subject_id
                      AND CCCV.COUNTER_TEMPLATE_NAME = c_counter_name
                      AND trunc(nvl(CCCV.start_date_active, sysdate)) <= trunc(sysdate)
                      AND trunc(nvl(CCCV.end_date_active, sysdate+1)) > trunc(sysdate))
*/
                     FROM CSI_COUNTERS_VL C, CSI_COUNTER_ASSOCIATIONS CCA
                    WHERE CCA.SOURCE_OBJECT_ID = CI.subject_id
                      AND C.COUNTER_ID = CCA.COUNTER_ID(+)
                      AND CCA.SOURCE_OBJECT_CODE = 'CP'
                      AND C.COUNTER_TEMPLATE_NAME = c_counter_name
                      AND trunc(nvl(C.start_date_active, sysdate)) <= trunc(sysdate)
                      AND trunc(nvl(C.end_date_active, sysdate+1)) > trunc(sysdate))
START WITH object_id = c_instance_id
       AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
       AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
       AND relationship_type_code = 'COMPONENT-OF'
CONNECT BY object_id = PRIOR subject_id
       AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
       AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
       AND relationship_type_code = 'COMPONENT-OF';

Line: 1248

    SELECT object_id
      FROM csi_ii_relationships
START WITH subject_id = c_desc_instance_id
       AND object_id <> c_ance_instance_id
       -- This condition is really required because of the extreme case in which
       -- subject_id = c_desc_instance_id and object_id happens to be c_ance_instance_id
       -- thus it will include c_ance_instance_id and probably all of its ancestors if it has.
       AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
       AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
       AND relationship_type_code = 'COMPONENT-OF'
CONNECT BY subject_id = PRIOR object_id
       AND object_id <> c_ance_instance_id
       AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
       AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
       AND relationship_type_code = 'COMPONENT-OF';

Line: 1267

      SELECT ctr.name counter_name, ctr.counter_id, ctr.uom_code, cgrp.counter_group_id
      FROM cs_counter_groups cgrp, cs_counters ctr
      WHERE cgrp.counter_group_id = ctr.counter_group_id
      AND  cgrp.source_object_code = 'CP'
      AND  cgrp.source_object_id = c_csi_item_instance_id
      AND  ctr.name = c_name
      AND  trunc(sysdate) >= trunc(nvl(ctr.start_date_active,sysdate))
      AND  trunc(sysdate) <= trunc(nvl(ctr.end_date_active,sysdate+1));

Line: 1276

      SELECT CCCV.COUNTER_TEMPLATE_NAME counter_name, CCCV.counter_id, CCCV.uom_code, CCCV.counter_group_id
        FROM CSI_CP_COUNTERS_V CCCV
       WHERE CCCV.CUSTOMER_PRODUCT_ID = c_csi_item_instance_id
         AND CCCV.COUNTER_TEMPLATE_NAME = c_name
         AND trunc(nvl(CCCV.start_date_active, sysdate)) <= trunc(sysdate)
         AND trunc(nvl(CCCV.end_date_active, sysdate+1)) > trunc(sysdate);

Line: 1290

     SELECT nvl(counter_reading,0) counter_reading,
            counter_group_id,
            uom_code
       FROM csi_cp_counters_v
      WHERE counter_id = c_counter_id;

Line: 1311

   L_DEBUG_KEY       CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Reading_Cn';

Line: 1326

                     'cs_counters_name_csr%FOUND. Calling update_reading_ins');

Line: 1328

    update_reading_ins(l_utilization_rec);

Line: 1378

              l_parents_tbl.DELETE;

Line: 1398

              l_instance_tbl.DELETE(i);

Line: 1411

            update_reading_ins(l_utilization_rec);

Line: 1426

END update_reading_cn;

Line: 1430

PROCEDURE update_reading_all(p_utilization_rec IN utilization_rec_type,
                             p_based_on        IN VARCHAR2)
IS
  l_utilization_rec  utilization_rec_type;

Line: 1435

  L_DEBUG_KEY       CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Reading_All';

Line: 1443

    SELECT DISTINCT CC.NAME counter_name
      FROM cs_counters CC,
           cs_counter_groups CG
     WHERE CC.counter_group_id = CG.counter_group_id
       AND CG.source_object_code = 'CP'
       AND CG.source_object_id = c_instance_id
       AND CC.uom_code = c_uom_code
       AND trunc(nvl(CC.start_date_active, sysdate)) <= trunc(sysdate)
       AND trunc(nvl(CC.end_date_active, sysdate+1)) > trunc(sysdate);

Line: 1453

    SELECT DISTINCT CCCV.COUNTER_TEMPLATE_NAME counter_name
      FROM CSI_CP_COUNTERS_V CCCV
     WHERE CCCV.UOM_CODE = c_uom_code
       AND CCCV.CUSTOMER_PRODUCT_ID = c_instance_id
       AND trunc(nvl(CCCV.start_date_active, sysdate)) <= trunc(sysdate)
       AND trunc(nvl(CCCV.end_date_active, sysdate+1)) > trunc(sysdate);

Line: 1465

    SELECT DISTINCT C.COUNTER_TEMPLATE_NAME counter_name
      FROM CSI_COUNTERS_VL C, CSI_COUNTER_ASSOCIATIONS CCA
     WHERE C.UOM_CODE = c_uom_code
       AND trunc(nvl(C.start_date_active, sysdate)) <= trunc(sysdate)
       AND trunc(nvl(C.end_date_active, sysdate+1)) > trunc(sysdate)
       AND C.COUNTER_ID = CCA.COUNTER_ID(+)
       AND CCA.SOURCE_OBJECT_CODE = 'CP'
       AND CCA.SOURCE_OBJECT_ID IN (SELECT c_instance_id
/*
    SELECT DISTINCT CCCV.COUNTER_TEMPLATE_NAME counter_name
      FROM CSI_CP_COUNTERS_V CCCV
     WHERE CCCV.UOM_CODE = c_uom_code
       AND trunc(nvl(CCCV.start_date_active, sysdate)) <= trunc(sysdate)
       AND trunc(nvl(CCCV.end_date_active, sysdate+1)) > trunc(sysdate)
       AND CCCV.CUSTOMER_PRODUCT_ID IN (SELECT c_instance_id
*/
                                     FROM DUAL
                                UNION ALL
                                   SELECT subject_id
                                     FROM csi_ii_relationships CI
                               START WITH object_id = c_instance_id
                                      AND trunc(nvl(CI.active_start_date,sysdate)) <= trunc(sysdate)
                                      AND trunc(nvl(CI.active_end_date,sysdate+1)) > trunc(sysdate)
                                      AND CI.relationship_type_code = 'COMPONENT-OF'
                               CONNECT BY object_id = PRIOR subject_id
                                      AND trunc(nvl(CI.active_start_date,sysdate)) <= trunc(sysdate)
                                      AND trunc(nvl(CI.active_end_date,sysdate+1)) > trunc(sysdate)
                                      AND CI.relationship_type_code = 'COMPONENT-OF');

Line: 1507

                     'p_based_on is COUNTERID. Calling update_reading_id');

Line: 1509

    update_reading_id(l_utilization_rec);

Line: 1524

                     'p_based_on is COUNTER. Calling update_reading_cn');

Line: 1526

    update_reading_cn(l_utilization_rec);

Line: 1547

                         ', i = ' || i || ', Calling update_reading_cn');

Line: 1549

        update_reading_cn(l_utilization_rec);

Line: 1560

                         ', i = ' || i || ', Calling update_reading_cn');

Line: 1562

        update_reading_cn(l_utilization_rec);

Line: 1575

END update_reading_all;

Line: 1588

    SELECT ratio
      FROM ahl_ctr_update_rules
     WHERE relationship_id = c_relationship_id
       AND rule_code = c_rule_code
       AND uom_code  = c_uom_code;

Line: 1595

    SELECT object_id,
           subject_id,
           to_number(position_reference) relationship_id
      FROM csi_ii_relationships
START WITH subject_id = c_desc_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_start_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: 1609

  l_rule_code             ahl_ctr_update_rules.rule_code%TYPE := p_rule_code;

Line: 1613

  l_uom_code              ahl_ctr_update_rules.uom_code%TYPE;