eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AHL_UMP_SMRINSTANCE_PVT SQL Statements

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

Line: 120


   SELECT unit_effectivity_id
   FROM ahl_unit_effectivities_b
   WHERE defer_from_ue_id = p_unit_effectivity_id
   AND rownum < 2;

Line: 127


   SELECT UMP.program_type, UMP.Title, UMP.part_number, UMP.serial_number,
          -- R12: Fix for bug# 5231770.
          -- Due to CSI counter schema changes, the latest net_reading is
          -- no longer available in csi_cp_counters_v. Net_reading will be
          -- queried seperately to calculate uom_remain.
          -- In this cursor, UMP.due_counter_value will be retrieved instead.
          --(UMP.due_counter_value - nvl(UMP.net_reading,0)) uom_remain,
          UMP.due_counter_value uom_remain,
          UMP.counter_name, UMP.earliest_due_date, UMP.due_date, UMP.latest_due_date,
          UMP.tolerance, UMP.status_code, UMP.status, UMP.originator_title, UMP.dependant_title,
          UMP.unit_effectivity_id, UMP.mr_header_id, UMP.csi_item_instance_id, UMP.instance_number,
          UMP.mr_interval_id, UMP.unit_name, UMP.program_title, UMP.contract_number,
          UMP.defer_from_ue_id, UMP.object_type, UMP.counter_id, UMP.MANUALLY_PLANNED_FLAG,
          UMP.MANUALLY_PLANNED_DESC,
          UMP.cs_incident_id, UMP.cs_incident_number,
          -- added for bug# 6530920.
          UMP.orig_ue_instance_id
   FROM ahl_unit_effectivities_v UMP
   WHERE UMP.unit_effectivity_id = p_unit_effectivity_id;

Line: 154


   SELECT cc.counter_template_name counter_name,
          nvl(cv.net_reading,0) net_reading
   FROM csi_counter_values_v cv, csi_counters_vl cc
   WHERE cv.counter_id = cc.counter_id
     AND cv.counter_id = p_counter_id
     AND rownum < 2;

Line: 162


   SELECT cc.counter_template_name counter_name,
          (select ccr.net_reading from csi_counter_readings ccr
           where ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO
             and nvl(ccr.disabled_flag,'N') = 'N')
   FROM csi_counters_vl cc
   WHERE cc.counter_id = p_counter_id;

Line: 183


   SELECT vst.start_date_time, vst.visit_number
   FROM ahl_visit_tasks_b tsk,
        (select vst1.* from
         ahl_visits_b vst1, ahl_simulation_plans_b sim
         where vst1.simulation_plan_id = sim.simulation_plan_id
           and sim.primary_plan_flag = 'Y'
         UNION ALL
         select vst1.* from
         ahl_visits_b vst1
         where simulation_plan_id IS NULL)vst
   WHERE vst.visit_id = tsk.visit_id
   AND NVL(vst.status_code,'x') NOT IN ('DELETED','CANCELLED')
   AND NVL(tsk.status_code,'x') NOT IN ('DELETED','CANCELLED')
   AND tsk.unit_effectivity_id = p_ue_id;

Line: 200


   SELECT vst.start_date_time, vst.visit_number, vst.visit_id
   FROM ahl_visit_tasks_b tsk,
        (select vst1.* from
         ahl_visits_b vst1, ahl_simulation_plans_b sim
         where vst1.simulation_plan_id = sim.simulation_plan_id
           and sim.primary_plan_flag = 'Y'
         UNION ALL
         select vst1.* from
         ahl_visits_b vst1
         where simulation_plan_id IS NULL)vst,
         hr_all_organization_units hrou,
         bom_departments bdpt
   WHERE vst.visit_id = tsk.visit_id
   AND NVL(vst.status_code,'x') NOT IN ('DELETED','CANCELLED')
   AND NVL(tsk.status_code,'x') NOT IN ('DELETED','CANCELLED')
   AND tsk.unit_effectivity_id = p_ue_id
   AND vst.organization_id    = hrou.organization_id(+)
   AND ((vst.organization_id IS NULL AND p_visit_org_name IS NULL) OR upper(hrou.name) LIKE NVL(upper(p_visit_org_name),upper(hrou.name)))
   AND vst.department_id    = bdpt.department_id(+)
   AND ((vst.department_id IS NULL AND p_visit_dept_name IS NULL) OR upper(bdpt.description) LIKE NVL(upper(p_visit_dept_name),upper(bdpt.description)))
   AND vst.visit_number like nvl(p_visit_num,vst.visit_number);

Line: 226


     select decode(unit_deferral_type, 'MEL', 'MEL ' || fk.meaning,
                                  'CDL', 'CDL ' || fk.meaning,
                                   fk.meaning) deferral_meaning
        from ahl_unit_deferrals_b, fnd_lookup_values_vl fk
        where unit_effectivity_id = p_ue_id
          and fk.lookup_type = 'AHL_PRD_DF_APPR_STATUS_TYPES'
          and fk.lookup_code = approval_status_code;

Line: 236


     select fk.meaning defer_meaning
       from ahl_unit_deferrals_b udf, fnd_lookup_values_vl fk
      where udf.unit_effectivity_id = p_ue_id
        and fk.lookup_code = decode(udf.approval_status_code, 'DRAFT',
                                    'DEFERRAL_DRAFT',udf.approval_status_code)
        and fk.lookup_type = 'AHL_PRD_DF_APPR_STATUS_TYPES'
        and udf.unit_deferral_type = 'DEFERRAL';

Line: 246


     select unit_deferral_type || ' ' || fk.meaning defer_meaning
       from ahl_unit_deferrals_b udf, fnd_lookup_values_vl fk
      where udf.unit_effectivity_id = p_ue_id
        and fk.lookup_code = decode(udf.approval_status_code, 'DEFERRED',
                                    'APPROVED',udf.approval_status_code)
        and fk.lookup_type = 'AHL_PRD_DF_APPR_STATUS_TYPES'
        and udf.unit_deferral_type IN ('MEL','CDL') ;

Line: 268


     SELECT (select ccr.net_reading from csi_counter_readings ccr
             where ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO
               and nvl(ccr.disabled_flag,'N') = 'N') net_reading
     FROM CSI_COUNTER_ASSOCIATIONS CCA, CSI_COUNTERS_VL CC
     WHERE CCA.COUNTER_ID = CC.COUNTER_ID
       AND CCA.SOURCE_OBJECT_ID = p_csi_item_instance_id
       AND CCA.SOURCE_OBJECT_CODE = 'CP'
       AND CC.COUNTER_TEMPLATE_NAME = p_ctr_template_name;

Line: 277


     SELECT nvl(CV.NET_READING, 0)
     FROM CSI_COUNTER_READINGS CV, CSI_COUNTER_ASSOCIATIONS CCA, CSI_COUNTERS_VL CC
     WHERE CCA.SOURCE_OBJECT_CODE = 'CP'
       AND CCA.COUNTER_ID = CV.COUNTER_ID
       --AND CC.COUNTER_ID = CV.COUNTER_ID
       AND CC.CTR_VAL_MAX_SEQ_NO = CV.counter_value_id
       AND CCA.SOURCE_OBJECT_ID = p_csi_item_instance_id
       AND CC.COUNTER_TEMPLATE_NAME = p_ctr_template_name;

Line: 294


   l_mr_select_sql_string VARCHAR2(4000);

Line: 295


   l_select_sql_string    VARCHAR2(4000);

Line: 296


   l_mr_select_flag   BOOLEAN;

Line: 297


   l_nr_select_sql_string  VARCHAR2(4000);

Line: 451


    l_mr_select_flag := FALSE;

Line: 470


       l_sql_string := 'SELECT  UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_V UMP WHERE 0=0 ';

Line: 475


          l_select_sql_string := 'SELECT /*+ dynamic_sampling(AAI1 4) */ UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_APPLICABLE_INSTANCES AAI1 ';

Line: 476


          l_select_sql_string := l_select_sql_string || 'WHERE UMP.application_usg_code= :APPL_USG_CODE AND AAI1.csi_item_instance_id = UMP.csi_item_instance_id ';

Line: 478


          l_mr_select_sql_string := 'SELECT /*+ dynamic_sampling(AAI1 4) */ UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_APPLICABLE_INSTANCES AAI1, AHL_MR_HEADERS_B MR';

Line: 479


          l_mr_select_sql_string := l_mr_select_sql_string || ' WHERE UMP.application_usg_code= :APPL_USG_CODE AND AAI1.csi_item_instance_id = UMP.csi_item_instance_id AND UMP.mr_header_id = MR.mr_header_id ';

Line: 486


                l_nr_select_sql_string := 'SELECT /*+ dynamic_sampling(AAI1 4) */ UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP , AHL_APPLICABLE_INSTANCES AAI1, CS_INCIDENTS_ALL_B CI, CS_INCIDENT_TYPES_VL CIT ';

Line: 487


                l_nr_select_sql_string := l_nr_select_sql_string || 'WHERE UMP.application_usg_code= :APPL_USG_CODE AND AAI1.csi_item_instance_id = UMP.csi_item_instance_id AND ';

Line: 488


                l_nr_select_sql_string := l_nr_select_sql_string || 'UMP.cs_incident_id = CI.incident_id AND CI.incident_number like :CS_SERVC_NUM AND CI.incident_type_id = CIT.incident_type_id AND CIT.incident_type_id = :INC_TYPE_ID ';

Line: 497


                l_nr_select_sql_string := 'SELECT /*+ dynamic_sampling(AAI1 4) */ UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_APPLICABLE_INSTANCES AAI1, CS_INCIDENTS_ALL_B CI,CS_INCIDENT_TYPES_VL CIT ';

Line: 498


                l_nr_select_sql_string := l_nr_select_sql_string || 'WHERE UMP.application_usg_code= :APPL_USG_CODE AND AAI1.csi_item_instance_id = UMP.csi_item_instance_id AND UMP.cs_incident_id = CI.incident_id';

Line: 499


               l_nr_select_sql_string := l_nr_select_sql_string || ' AND CI.incident_type_id = CIT.incident_type_id AND CIT.incident_type_id = :INC_TYPE_ID ';

Line: 506


               l_nr_select_sql_string := 'SELECT /*+ dynamic_sampling(AAI1 4) */ UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP,AHL_APPLICABLE_INSTANCES AAI1, CS_INCIDENTS_ALL_B CI ';

Line: 507


               l_nr_select_sql_string := l_nr_select_sql_string || 'WHERE UMP.application_usg_code= :APPL_USG_CODE AND AAI1.csi_item_instance_id = UMP.csi_item_instance_id ';

Line: 508


               l_nr_select_sql_string := l_nr_select_sql_string || 'AND UMP.cs_incident_id = CI.incident_id AND CI.incident_number like :CS_SERVC_NUM ';

Line: 517


          l_select_sql_string := 'SELECT  UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP WHERE UMP.application_usg_code= :APPL_USG_CODE ';

Line: 519


          l_mr_select_sql_string := 'SELECT  UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_MR_HEADERS_B MR  WHERE UMP.application_usg_code= :APPL_USG_CODE AND UMP.mr_header_id = MR.mr_header_id ';

Line: 524


                l_nr_select_sql_string := 'SELECT  UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, CS_INCIDENTS_ALL_B CI, CS_INCIDENT_TYPES_VL CIT WHERE UMP.application_usg_code= :APPL_USG_CODE ';

Line: 525


                l_nr_select_sql_string := l_nr_select_sql_string || ' AND UMP.cs_incident_id = CI.incident_id  AND CI.incident_number like :CS_SERVC_NUM AND CI.incident_type_id = CIT.incident_type_id AND CIT.incident_type_id = :INC_TYPE_ID ';

Line: 534


                l_nr_select_sql_string := 'SELECT  UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP,CS_INCIDENTS_ALL_B CI,CS_INCIDENT_TYPES_VL CIT ';

Line: 535


                l_nr_select_sql_string := l_nr_select_sql_string || 'WHERE UMP.application_usg_code= :APPL_USG_CODE AND UMP.cs_incident_id = CI.incident_id ';

Line: 536


                l_nr_select_sql_string := l_nr_select_sql_string || 'AND CI.incident_type_id = CIT.incident_type_id AND CIT.incident_type_id = :INC_TYPE_ID ';

Line: 544


               l_nr_select_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP,CS_INCIDENTS_ALL_B CI ';

Line: 545


               l_nr_select_sql_string := l_nr_select_sql_string || 'WHERE UMP.application_usg_code= :APPL_USG_CODE AND UMP.cs_incident_id = CI.incident_id AND CI.incident_number like :CS_SERVC_NUM ';

Line: 565


           l_mr_select_flag := TRUE;

Line: 585


          IF (p_search_mr_instance_rec.program_type = 'NON-ROUTINE' AND l_mr_select_flag = FALSE AND
              l_nr_select_sql_string IS NULL)  THEN
               --If the Prgram_type_code is NON_ROUTINE
               l_sql_string := l_sql_string || ' AND UMP.OBJECT_TYPE IN (''SR'',''MR'')  ';

Line: 591


                (select MR.program_type_code from AHL_MR_HEADERS_B MR where MR.mr_header_id = UMP.mr_header_id)) like :FMP_PROGRAM_TYPE ';

Line: 596


          ELSIF (p_search_mr_instance_rec.program_type = 'NON-ROUTINE' AND l_nr_select_sql_string IS NOT NULL) THEN
               null; -- filter not required.

Line: 598


          ELSIF (l_nr_select_sql_string IS NULL) THEN
              -- only MRs to be selected
              l_sql_string := l_sql_string || ' AND MR.PROGRAM_TYPE_CODE like :FMP_PROGRAM_TYPE ';

Line: 601


              l_mr_select_flag := TRUE;

Line: 619


          l_sql_string := l_sql_string || ' AND EXISTS (Select ''x'' FROM ahl_unit_deferrals_b WHERE unit_effectivity_id = UMP.unit_effectivity_id AND approval_status_code = :DEFERRAL_STATUS) ';

Line: 624


          l_sql_string := l_sql_string || ' AND EXISTS (Select ''x'' FROM ahl_unit_deferrals_b WHERE unit_effectivity_id = UMP.unit_effectivity_id AND unit_deferral_type = ''CDL'' AND approval_status_code = :CDL_STATUS) ';

Line: 628


          l_sql_string := l_sql_string || ' AND EXISTS (Select ''x'' FROM ahl_unit_deferrals_b WHERE unit_effectivity_id = UMP.unit_effectivity_id AND unit_deferral_type = ''MEL'' AND approval_status_code = :MEL_STATUS) ';

Line: 731


         l_sql_string := l_sql_string || ' AND  NOT EXISTS (SELECT ''x'' FROM AHL_UE_RELATIONSHIPS WHERE RELATED_UE_ID = UMP.unit_effectivity_id)';

Line: 735


         l_sql_string := l_sql_string || ' AND NOT EXISTS (SELECT ''x'' FROM AHL_UNIT_EFFECTIVITIES_B PARENT_UE, AHL_UE_RELATIONSHIPS CHILD_UER';

Line: 745


      DELETE AHL_APPLICABLE_INSTANCES;

Line: 758


            l_sql_string := l_sql_string || ' AND EXISTS (Select ''x'' From csi_unit_instances_v csiu WHERE csiu.instance_id = UMP.csi_item_instance_id )';

Line: 842


        l_sql_string := l_sql_string || ' and exists (select ''x'' from ahl_visits_b vst1,ahl_simulation_plans_b sim, ahl_visit_tasks_b tsk where vst1.simulation_plan_id = sim.simulation_plan_id(+) ';

Line: 844


        l_sql_string := l_sql_string || 'IN (''DELETED'',''CANCELLED'') AND NVL(tsk.status_code,''x'') NOT IN (''DELETED'',''CANCELLED'') AND tsk.unit_effectivity_id = UMP.unit_effectivity_id) ' ;

Line: 857


            ELSIF (l_mr_select_flag = TRUE) THEN
              l_sql_string := l_sql_string || ' ORDER BY MR.PROGRAM_TYPE_CODE';

Line: 859


            ELSIF (l_nr_select_sql_string IS NOT NULL) THEN
              l_sql_string := l_sql_string || ' ORDER BY ''NONROUTINE'' ';

Line: 863


              (select MR.program_type_code from AHL_MR_HEADERS_B MR where MR.mr_header_id = UMP.mr_header_id))';

Line: 868


            ELSIF (l_mr_select_flag = TRUE) THEN
              l_sql_string := l_sql_string || ' ORDER BY MR.CATEGORY_CODE';

Line: 872


              (select MR.category_code from AHL_MR_HEADERS_B MR where MR.mr_header_id = UMP.mr_header_id))';

Line: 880


    IF (l_mr_select_flag = TRUE) THEN
      l_sql_string := l_mr_select_sql_string || l_sql_string;

Line: 882


    ELSIF (l_nr_select_sql_string IS NOT NULL) THEN
      l_sql_string := l_nr_select_sql_string || l_sql_string;

Line: 885


      l_sql_string := l_select_sql_string || l_sql_string;

Line: 1144


     l_ue_id_tbl.DELETE;

Line: 1213


    Select ahlu.csi_item_instance_id from ahl_unit_config_headers ahlu
    where UPPER(ahlu.name) like upper(p_unit_name);

Line: 1234


          INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES (only_units_rec.csi_item_instance_id,0);

Line: 1239


      l_unit_related_ii_sql := 'select csii.instance_id from csi_item_instances csii ';

Line: 1300


        l_unit_related_ii_sql := l_unit_related_ii_sql || 'and EXISTS (select ''x'' from ahl_unit_effectivities_b UE where UE.csi_item_instance_id = csii.instance_id)';

Line: 1316


           INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES(l_inst_tbl(j),0);

Line: 1318


         l_inst_tbl.DELETE;

Line: 1333


   SELECT subject_id from csi_ii_relationships csii WHERE
   EXISTS (select 'x' from ahl_unit_effectivities_b UE where UE.csi_item_instance_id = csii.subject_id)
   AND NOT EXISTS (select 'x' from AHL_APPLICABLE_INSTANCES where csi_item_instance_id = csii.subject_id)
   START WITH object_id IN (SELECT csi_item_instance_id FROM  AHL_APPLICABLE_INSTANCES WHERE POSITION_ID = 0)
   AND trunc(nvl(csii.active_start_date, sysdate)) <=  Trunc(sysdate)
   AND trunc(nvl(csii.active_end_date, sysdate+1)) > Trunc(sysdate)
   AND relationship_type_code = 'COMPONENT-OF'
   CONNECT BY PRIOR subject_id = object_id
   AND trunc(nvl(csii.active_start_date, sysdate)) <=  Trunc(sysdate)
   AND trunc(nvl(csii.active_end_date, sysdate+1)) > Trunc(sysdate)
   AND relationship_type_code = 'COMPONENT-OF';

Line: 1352


       INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID)
       VALUES(dependent_component_rec.subject_id,1);

Line: 1361


           INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES(l_inst_tbl(j),1);

Line: 1363


         l_inst_tbl.DELETE;