APPS.AHL_REPORT_UTILS_PVT SQL Statements

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

Line: 35

   SELECT ue.unit_effectivity_id
   FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr
   WHERE ue.object_type = 'MR'
   AND ue.MANUALLY_PLANNED_FLAG = 'N'
   AND ue.csi_item_instance_id = C_INSTANCE_ID
   AND ue.due_date >= sysdate
   AND ue.status_code IS NULL
   AND (((NVL(c_mr_pgm_sub_type, 'ALL') = 'ALL')
         OR((mr.program_subtype_code = c_mr_pgm_sub_type)
             AND mr.mr_header_id = ue.mr_header_id
             AND trunc(nvl(mr.effective_from,sysdate)) <= trunc(sysdate)
             AND trunc(sysdate) <= trunc(nvl(mr.effective_to,sysdate+1))))
             AND mr.mr_status_code = 'COMPLETE')
   AND  ROWNUM < 2
   ORDER BY ue.due_date;

Line: 92

   SELECT ue.unit_effectivity_id
   FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr
   WHERE ue.object_type = 'MR'
   AND ue.manually_planned_flag = 'N'
   AND ue.csi_item_instance_id = c_instance_id
   AND ue.due_date >= sysdate
   AND ue.status_code IS NULL
   AND mr.program_subtype_code in (
        Select parameter_value
        From ahl_parameters
        Where parameter_source_type = 'Reports'
        And parameter_source_sub_type = 'CRF'
        And parameter_name like 'PROGRAM_SUBTYPE%')
   AND mr.mr_header_id = ue.mr_header_id
   AND trunc(nvl(mr.effective_from,sysdate)) <= trunc(sysdate)
   AND trunc(sysdate) <= trunc(nvl(mr.effective_to,sysdate+1))
   AND mr.mr_status_code = 'COMPLETE'
   AND  ROWNUM < 2
   ORDER BY ue.due_date;

Line: 157

    SELECT TO_NUMBER(parameter_value)
    FROM AHL_PARAMETERS
    WHERE parameter_name = c_counter_index
    AND parameter_source_type = 'Reports'
    AND parameter_source_sub_type = c_report_name;

Line: 165

    SELECT UE.DUE_COUNTER_VALUE, UE.CSI_ITEM_INSTANCE_ID
    FROM AHL_MR_INTERVALS AMI, AHL_UNIT_EFFECTIVITIES_B UE
    WHERE AMI.COUNTER_ID = c_counter_id
    AND AMI.MR_INTERVAL_ID = UE.MR_INTERVAL_ID
    AND UE.unit_effectivity_id = c_ue_id;

Line: 172

    SELECT ccr.net_reading
    FROM csi_counter_readings ccr, CSI_COUNTERS_VL C, CSI_COUNTER_ASSOCIATIONS CCA
    WHERE C.CREATED_FROM_COUNTER_TMPL_ID = c_counter_id and
          C.COUNTER_ID = CCA.COUNTER_ID(+) and
          CCA.SOURCE_OBJECT_CODE = 'CP' and
          CCA.SOURCE_OBJECT_ID = c_item_instance_id and
          C.CTR_VAL_MAX_SEQ_NO = CCR.COUNTER_VALUE_ID;

Line: 246

  SELECT TO_NUMBER(parameter_value)

  FROM   AHL_PARAMETERS

  WHERE  parameter_source_type = 'Reports'
         AND parameter_source_sub_type = 'USN'
         AND parameter_name = c_counter_index;

Line: 258

  SELECT AMIV.interval_value

  FROM   AHL_MR_INTERVALS_V AMIV,
         AHL_UNIT_EFFECTIVITIES_B AUEB

  WHERE  AMIV.mr_interval_id = AUEB.mr_interval_id
         AND AUEB.unit_effectivity_id = c_ue_id
         AND AMIV.counter_id = c_counter_id;

Line: 337

   SELECT TO_NUMBER(PARAMETER_VALUE)
     FROM AHL_PARAMETERS
    WHERE PARAMETER_NAME = c_counter_index
      AND PARAMETER_SOURCE_TYPE = 'Reports'
      AND PARAMETER_SOURCE_SUB_TYPE = 'CRF';

Line: 344

    SELECT * FROM (
       SELECT AUEB.UNIT_EFFECTIVITY_ID,AUEB.ACCOMPLISHED_DATE
       FROM AHL_UNIT_EFFECTIVITIES_B AUEB,
            AHL_MR_HEADERS_B MRHB
       WHERE AUEB.STATUS_CODE = 'MR-TERMINATE'
       AND AUEB.CSI_ITEM_INSTANCE_ID = c_instance_id
       AND MRHB.PROGRAM_SUBTYPE_CODE IN (
              Select parameter_value
              From ahl_parameters
              Where parameter_source_type = 'Reports'
              And parameter_source_sub_type = 'CRF'
              And parameter_name like 'PROGRAM_SUBTYPE%')
       AND AUEB.MR_HEADER_ID = MRHB.MR_HEADER_ID
       AND trunc(nvl(MRHB.effective_from,sysdate)) <= trunc(sysdate)
       AND trunc(sysdate) <= trunc(nvl(MRHB.effective_to,sysdate+1))
       AND MRHB.mr_status_code = 'COMPLETE'
       ORDER BY AUEB.ACCOMPLISHED_DATE DESC)
     WHERE ROWNUM < 2;

Line: 364

   SELECT AUA.COUNTER_VALUE
     FROM AHL_UNIT_ACCOMPLISHMNTS AUA,
          AHL_UNIT_EFFECTIVITIES_B AUEB,
          AHL_MR_INTERVALS AMI
    WHERE AMI.COUNTER_ID = c_counter_id
    AND AUEB.UNIT_EFFECTIVITY_ID = c_ue_id
    AND AUEB.MR_INTERVAL_ID = AMI.MR_INTERVAL_ID
    AND AUA.UNIT_EFFECTIVITY_ID = AUEB.UNIT_EFFECTIVITY_ID;

Line: 374

   SELECT CCR.NET_READING
     FROM CSI_COUNTER_READINGS CCR, CSI_COUNTERS_VL C, CSI_COUNTER_ASSOCIATIONS CCA
    WHERE C.CREATED_FROM_COUNTER_TMPL_ID = c_counter_id
      AND C.COUNTER_ID = CCA.COUNTER_ID(+)
      AND CCA.SOURCE_OBJECT_CODE = 'CP'
      AND CCA.SOURCE_OBJECT_ID = c_item_instance_id
      AND C.CTR_VAL_MAX_SEQ_NO = CCR.COUNTER_VALUE_ID;

Line: 451

   SELECT COUNT(distinct aig.name)
   FROM AHL_ITEM_ASSOCIATIONS_B ata,
    AHL_ITEM_GROUPS_B aig,
    CSI_ITEM_INSTANCES inst
   WHERE inst.instance_id                           = c_instance_id
   AND ata.inventory_item_id                        = inst.inventory_item_id
   AND ata.inventory_org_id                         = inst.inv_master_organization_id  --AJPRASAN:: Added this where clause
   AND ata.item_group_id                            = aig.item_group_id
   AND aig.status_code                              = 'COMPLETE'
   AND ata.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE');

Line: 503

  SELECT UNIQUE cir.object_id INST_ID
  FROM csi_ii_relationships cir,
    ahl_unit_config_headers unit
  WHERE cir.object_id IN
    (SELECT csi_item_instance_id FROM ahl_unit_config_headers
    )
  AND c_instance_id NOT IN
    (SELECT csi_item_instance_id FROM ahl_unit_config_headers
    )
  AND ROWNUM                                = 1
    START WITH cir.subject_id               = c_instance_id
  AND cir.relationship_type_code            = 'COMPONENT-OF'
  AND NVL(cir.active_end_date, sysdate + 1) > sysdate
    CONNECT BY cir.subject_id               = PRIOR cir.object_id
  AND cir.relationship_type_code            = 'COMPONENT-OF'
  AND NVL(cir.active_end_date, sysdate + 1) > sysdate

  UNION ALL

  SELECT UNIQUE cirl.object_id
  FROM csi_ii_relationships cirl,
    ahl_unit_config_headers auc
  WHERE cirl.object_id                       = c_instance_id
  AND cirl.relationship_type_code            = 'COMPONENT-OF'
  AND NVL(cirl.active_end_date, sysdate + 1) > sysdate
  AND auc.csi_item_instance_id               = cirl.object_id;

Line: 571

  SELECT UNIQUE cir.object_id INST_ID
  FROM csi_ii_relationships cir,
    ahl_unit_config_headers unit
  /*WHERE cir.object_id IN (SELECT csi_item_instance_id FROM ahl_unit_config_headers)
  AND cir.object_id NOT IN (SELECT subject_id FROM csi_ii_relationships)*/
  WHERE EXISTS(SELECT csi_item_instance_id FROM ahl_unit_config_headers where csi_item_instance_id = cir.object_id)
  AND NOT EXISTS (SELECT subject_id FROM csi_ii_relationships where subject_id = cir.object_id)
  AND ROWNUM                                =1
    START WITH cir.object_id                = c_instance_id
  AND cir.relationship_type_code            = 'COMPONENT-OF'
  AND NVL(cir.active_end_date, sysdate + 1) > sysdate
    CONNECT BY cir.subject_id               = PRIOR cir.object_id
  AND cir.relationship_type_code            = 'COMPONENT-OF'
  AND NVL(cir.active_end_date, sysdate + 1) > sysdate;

Line: 636

PROCEDURE Insert_Rem_Counters (
   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_rem_counter_tbl       IN  OUT NOCOPY Rem_Counter_Tbl_Type,
   x_return_status     OUT NOCOPY VARCHAR2,
   x_msg_count         OUT NOCOPY NUMBER,
   x_msg_data          OUT NOCOPY VARCHAR2
)
IS
   -- Define local Variables
   L_API_VERSION          CONSTANT NUMBER := 1.0;

Line: 649

   L_API_NAME             CONSTANT VARCHAR2(30) := 'Insert_Rem_Counters';

Line: 664

 SELECT 1 FROM   Ahl_Parameters
 WHERE  PARAMETER_ID = x_id;

Line: 670

   SELECT count(parameter_value)
   FROM ahl_parameters
   WHERE parameter_source_type = 'Reports'
   AND parameter_name like  x_parameter_name
   GROUP BY parameter_value, parameter_source_sub_type
   HAVING ( COUNT(parameter_value) > 1 );

Line: 680

  SAVEPOINT Insert_Rem_Counters;

Line: 725

      UPDATE ahl_parameters
      SET --parameter_name         = p_rem_counter_tbl(i).counter_parameter_name,
          parameter_value        = p_rem_counter_tbl(i).counter_parameter_value,
          OBJECT_VERSION_NUMBER  = object_version_number + 1,
          LAST_UPDATE_DATE       = SYSDATE,
          LAST_UPDATED_BY        = Fnd_Global.USER_ID,
          LAST_UPDATE_LOGIN      = Fnd_Global.LOGIN_ID
      WHERE  parameter_id = p_rem_counter_tbl(i).PARAMETER_ID;

Line: 791

      ROLLBACK TO Insert_Rem_Counters;

Line: 799

      ROLLBACK TO Insert_Rem_Counters;

Line: 807

      ROLLBACK TO Insert_Rem_Counters;

Line: 818

END Insert_Rem_Counters;

Line: 828

PROCEDURE Insert_Records(p_report_name IN VARCHAR,p_parameter_name IN VARCHAR,p_description IN VARCHAR2,p_count IN NUMBER)
IS

l_parameter_name VARCHAR2(200);

Line: 836

  DELETE FROM AHL_PARAMETERS
  WHERE Parameter_Source_type = 'Reports'
  AND Parameter_Source_Sub_Type = p_report_name
  AND parameter_name like p_parameter_name;

Line: 846

    Insert into AHL_PARAMETERS (PARAMETER_ID,PARAMETER_SOURCE_TYPE,PARAMETER_SOURCE_SUB_TYPE,PARAMETER_NAME,PARAMETER_VALUE,PARAMETER_DESCRIPTION,OBJECT_VERSION_NUMBER,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN)
    values (AHL_PARAMETERS_S.nextval,'Reports',p_report_name,l_parameter_name,null,l_description,1,sysdate,1,sysdate,1,1);

Line: 851

END Insert_Records;

Line: 861

PROCEDURE Insert_Ahl_Parameters
IS
l_get_count NUMBER;

Line: 867

  SELECT count(*) FROM   Ahl_Parameters
  WHERE Parameter_Source_type = 'Reports'
  AND Parameter_Source_Sub_Type = c_report_name
  AND parameter_name like c_parameter_name;

Line: 872

L_API_NAME         CONSTANT VARCHAR2(30)   := 'Insert_Ahl_Parameters';

Line: 888

    insert_records('CRF','SINCE_REP_COUNTER_','Since Repair Counter ',l_since_repair_counter);

Line: 896

    insert_records('CRF','REM_COUNTER_','Remaining Counter ',l_remaining_counter);

Line: 904

    insert_records('CRF','PROGRAM_SUBTYPE_','Program Subtype ',l_program_subtype);

Line: 912

    insert_records('PNR','PROGRAM_SUBTYPE_','Program Subtype ',l_program_subtype);

Line: 920

    insert_records('ABOM','REM_COUNTER_','Remaining Counter ',l_remaining_counter);

Line: 928

    insert_records('USN','SPECLIMIT_COUNTER_','Spec Limit Counter ',l_spec_limit_counter);

Line: 936

    insert_records('CRIH','COLLECTION_PLAN_NAME_','Collection Plan Name ',l_quality_plan);

Line: 939

END Insert_Ahl_Parameters;

Line: 970

    SELECT object_id,
      level "HR_LEVEL"
    FROM csi_ii_relationships
    WHERE object_id IN
      (SELECT csi_item_instance_id
      FROM ahl_unit_config_headers
      WHERE TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
      )
    START WITH subject_id                     = p_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);

Line: 989

    SELECT name, master_config_id
    FROM ahl_unit_config_headers
    WHERE csi_item_instance_id                 = c_instance_id
    AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);

Line: 1022

        SELECT COUNT(object_id) INTO l_masterConfig FROM csi_ii_relationships WHERE object_id = P_INSTANCE_ID AND relationship_type_code = 'COMPONENT-OF';

Line: 1050

  SELECT COUNT(subject_id) INTO l_child_inst FROM csi_ii_relationships WHERE subject_id = p_instance_id;

Line: 1053

    SELECT position_ref_code INTO l_position_ref_cd FROM ahl_mc_relationships WHERE relationship_id IN (SELECT position_reference FROM csi_ii_relationships WHERE subject_id = p_instance_id AND relationship_type_code = 'COMPONENT-OF') AND ROWNUM < 2;

Line: 1055

    SELECT position_ref_code INTO l_position_ref_cd FROM ahl_mc_relationships WHERE mc_header_id IN (SELECT master_config_id FROM ahl_unit_config_headers WHERE csi_item_instance_id = P_INSTANCE_ID) AND item_group_id = P_ITEM_GRP_ID AND ROWNUM < 2;

Line: 1059

    SELECT meaning INTO x_position FROM fnd_lookups fnd WHERE fnd.lookup_type = 'AHL_POSITION_REFERENCE' AND fnd.lookup_code = l_position_ref_cd;

Line: 1062

    SELECT concatenated_segments INTO x_position FROM mtl_item_locations_kfv k WHERE inventory_location_id = (SELECT INV_LOCATOR_ID FROM csi_item_instances WHERE instance_id = P_INSTANCE_ID);