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: 125


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

Line: 132


   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: 165


   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: 173


   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: 194


   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: 211


   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: 237


     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: 247


     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,
        'DEFERRAL_PENDING', DECODE(NVL(udf.cancel_flag,'N'),'Y','CANCEL_PENDING','DEFERRAL_PENDING'),
        'DEFERRAL_REJECTED', DECODE(NVL(udf.cancel_flag,'N'),'Y','CANCEL_REJECTED','DEFERRAL_REJECTED'),
        udf.approval_status_code)
        --and fk.lookup_type = 'AHL_PRD_DF_APPR_STATUS_TYPES'
        and fk.lookup_type = 'AHL_UNIT_EFFECTIVITY_STATUS'
        and udf.unit_deferral_type = 'DEFERRAL';

Line: 260


     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: 282


     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: 291


     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: 305


   select mr_status_code, effective_to, version_number from ahl_mr_headers_b
   where mr_header_id = mr_id;

Line: 320


   l_mr_select_sql_string VARCHAR2(4000);

Line: 321


   l_select_sql_string    VARCHAR2(4000);

Line: 322


   l_mr_select_flag   BOOLEAN;

Line: 323


   l_nr_select_sql_string  VARCHAR2(4000);

Line: 505


    l_mr_select_flag := FALSE;

Line: 536


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

Line: 541


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

Line: 542


          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: 543


          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: 544


          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: 551


                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: 552


                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: 553


                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: 562


                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: 563


                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: 564


               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: 571


               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: 572


               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: 573


               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: 582


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

Line: 584


          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: 589


                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: 590


                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: 599


                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: 600


                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: 601


                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: 609


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

Line: 610


               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: 630


           l_mr_select_flag := TRUE;

Line: 650


          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: 656


                (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: 661


          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: 663


          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: 666


              l_mr_select_flag := TRUE;

Line: 687


          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: 698


          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: 702


          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: 805


          l_sql_string := l_sql_string || ' SELECT ''x'' FROM AHL_UNIT_EFFECTIVITIES_B PARENT_UE, AHL_UE_RELATIONSHIPS CHILD_UER';

Line: 814


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

Line: 818


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

Line: 828


      DELETE AHL_APPLICABLE_INSTANCES;

Line: 841


            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: 924


        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: 926


        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: 934


        L_SQL_STRING := L_SQL_STRING || ' and exists (select ''x'' from AHL_ITEM_GROUPS_V IG, AHL_ITEM_ASSOCIATIONS_B IA , CSI_ITEM_INSTANCES CSIB where UMP.CSI_ITEM_INSTANCE_ID = CSIB.INSTANCE_ID ';

Line: 942


        l_sql_string := l_sql_string || ' and exists (select ''x'' from AHL_MR_HEADERS_B MRB ';

Line: 955


			l_sql_string := l_sql_string || ' and exists (select ''x'' from AHL_MR_HEADERS_B MRB ';

Line: 974


			l_sql_string := l_sql_string || ' and exists (select ''x'' from AHL_MR_HEADERS_B MRB ';

Line: 990


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

Line: 992


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

Line: 996


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

Line: 1001


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

Line: 1005


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

Line: 1013


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

Line: 1015


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

Line: 1018


      l_sql_string := l_select_sql_string || l_sql_string;

Line: 1131


           SELECT preceding_ue_id, terminating_ue_id, accomplish_trigger_type,
		   (SELECT mr.title FROM ahl_mr_headers_b mr,ahl_unit_effectivities_b dep
		   WHERE dep.mr_header_id = mr.mr_header_id
		   AND dep.unit_effectivity_id = eff.terminating_ue_id) termtitle
		   INTO l_prec_ue_id, l_term_ue_id,l_acc_trig_reln_type,l_term_title
		   FROM ahl_unit_effectivities_b eff
		   WHERE unit_effectivity_id = l_unit_effectivity_id;

Line: 1335


     l_ue_id_tbl.DELETE;

Line: 1404


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

Line: 1425


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

Line: 1430


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

Line: 1491


        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: 1507


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

Line: 1509


         l_inst_tbl.DELETE;

Line: 1524


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

Line: 1564


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

Line: 1568


         l_unit_related_ii_sql := ' Select DISTINCT ahlu.csi_item_instance_id from ahl_unit_config_headers ahlu,ahl_unit_schedules aus
                                    where AUS.UNIT_CONFIG_HEADER_ID = AHLU.UNIT_CONFIG_HEADER_ID and UPPER(ahlu.name) like (:UNIT_NAME) ';

Line: 1572


         l_unit_related_ii_sql := l_unit_related_ii_sql  || ' AND AUS.ARRIVAL_ORG_ID IN (SELECT ORGANIZATION_ID FROM HR_ALL_ORGANIZATION_UNITS HROU
                                                            WHERE HROU.NAME LIKE :ORG_NAME) ';

Line: 1599


            INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES(L_INST_TBL(J),0);

Line: 1601


           L_INST_TBL.DELETE;

Line: 1607


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

Line: 1663


          l_unit_related_ii_sql := l_unit_related_ii_sql  || ' AND AUS.ARRIVAL_ORG_ID IN (SELECT ORGANIZATION_ID FROM HR_ALL_ORGANIZATION_UNITS HROU
                                                            WHERE HROU.NAME LIKE :ORG_NAME) ';

Line: 1679


        L_UNIT_RELATED_II_SQL := L_UNIT_RELATED_II_SQL || 'and exists (select ''x'' from ahl_unit_config_headers uc where uc.CSI_ITEM_INSTANCE_ID = csii.instance_id ) ';

Line: 1681


          l_unit_related_ii_sql := l_unit_related_ii_sql || ' and csii.instance_id IN (select  uc.CSI_ITEM_INSTANCE_ID from ahl_unit_schedules aus,
              ahl_unit_config_headers uc  where AUS.UNIT_CONFIG_HEADER_ID = UC.UNIT_CONFIG_HEADER_ID AND AUS.ARRIVAL_ORG_ID IN
              (SELECT ORGANIZATION_ID FROM HR_ALL_ORGANIZATION_UNITS HROU WHERE HROU.NAME LIKE :ORG_NAME) ';

Line: 1701


        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: 1716


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

Line: 1718


         l_inst_tbl.DELETE;

Line: 1731


   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: 1750


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

Line: 1759


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

Line: 1761


         l_inst_tbl.DELETE;

Line: 1857


    L_DUMMY_SEARCH_QUERY := 'select ''N'' select_flag,
	 ''Show'' details,	null unit_name,null prog_type,null mr_header_id, null title,
   null concatenated_segments,null serial_number,null name,null uom_code,
   null due_counter_value,''View all UOMs'' view_all_uoms,null earliest_due_date,null due_date,null latest_due_date,
   null date_run,SYSDATE scheduledDate,null visit_name,null sr_incident_id,null sr_number,null unit_effectivity_id,null unit_config_header_id,
   null  visit_id,null fleet ,  null operatingOrg,  null mc  ,  null ucPosition ,  null Eng_Org,
   null implementstatus ,null  progsubtype  ,null servicecat  , null mrServiceType ,null mrduration , null MrDurationUOM , null PrimaryVisitType           ,
   null scheduledvisittype ,  null scheduledVisitOrg  from dual  where 1=2';

Line: 1901


       L_MR_SEARCH_QUERY := 'select /*+ dynamic_sampling(AAI1 4) */ ';

Line: 1903


       L_MR_SEARCH_QUERY := 'select ';

Line: 1905


      L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY ||  ' ''N'' select_flag, ''Show'' details,ahl_ump_util_pkg.get_unitname(b.csi_item_instance_id) unit_name,
      (select meaning from fnd_lookup_values  where lookup_code = mrb.PROGRAM_TYPE_CODE and lookup_type = ''AHL_FMP_MR_PROGRAM_TYPE''  and language = userenv(''LANG'')) prog_type ,
      B.MR_HEADER_ID,MRB.TITLE,MTL.CONCATENATED_SEGMENTS concatenated_segments,csib.serial_number,
      (select c.name from ahl_mr_intervals i,csi_counter_template_vl c where b.mr_interval_id = i.mr_interval_id and i.counter_id = c.counter_id) name ,
      (select c.uom_code from ahl_mr_intervals i,csi_counter_template_vl c where b.mr_interval_id = i.mr_interval_id AND I.COUNTER_ID = C.COUNTER_ID) uom_code,
      AHL_UMP_SMRINSTANCE_PVT.GET_UOM_REMAIN(b.unit_effectivity_id) DUE_COUNTER_VALUE, ''View all UOMs'' view_all_uoms, B.EARLIEST_DUE_DATE,B.DUE_DATE,B.LATEST_DUE_DATE,b.date_run,
      (select vst.START_DATE_TIME from ahl_visits_b vst,ahl_visit_tasks_b task where vst.visit_id = task.visit_id
      and task.unit_effectivity_id = b.unit_effectivity_id and task.status_code <> ''DELETED''and rownum < 2) scheduledDate,
      (select visit_number from ahl_visits_b vst,ahl_visit_tasks_b task where vst.visit_id = task.visit_id and task.unit_effectivity_id = B.unit_effectivity_id
      and task.status_code <> ''DELETED'' and rownum < 2) visit_name,
      null sr_incident_id,null sr_number,b.unit_effectivity_id,
      (Select unit_config_header_id from AHL_UNIT_CONFIG_HEADERS where name = ahl_ump_util_pkg.get_unitname(b.csi_item_instance_id)) unit_config_header_id,
      (select vst.visit_id from ahl_visits_b vst,ahl_visit_tasks_b task where vst.visit_id = task.visit_id
      and task.unit_effectivity_id = B.unit_effectivity_id and task.status_code <> ''DELETED''and rownum < 2)  visit_id,
      (select name from ahl_fleet_headers_b flt where flt.fleet_header_id = b.fleet_header_id) fleet ,
      (SELECT HROU.NAME FROM ahl_fleet_headers_b flt, HR_ALL_ORGANIZATION_UNITS HROU where flt.OPERATING_ORG_ID = hrou.organization_id
       and flt.fleet_header_id = b.fleet_header_id and rownum < 2) operatingorg ,
      (select master_config_name from ahl_unit_header_details_v ucd where ucd.name = ahl_ump_util_pkg.get_unitname(b.csi_item_instance_id)) mc,
      (Select position_ref_meaning from ahl_unit_details_v ucd where ucd.csi_item_instance_id =  b.csi_item_instance_id and rownum< 2) ucposition,
      MAINTTORG.MR_MAINTENANCE_ORG_NAME eng_org,
      (select meaning from fnd_lookup_values  where lookup_code = MRB.IMPLEMENT_STATUS_CODE and lookup_type = ''AHL_FMP_MR_IMPLEMENT_STATUS''  and language = userenv(''LANG'')) implementstatus ,
      (select meaning from fnd_lookup_values  where lookup_code = mrb.PROGRAM_SUBTYPE_CODE and lookup_type = ''AHL_FMP_MR_PROGRAM_SUBTYPE''  and language = userenv(''LANG'')) progsubtype ,
      (select meaning from fnd_lookup_values  where lookup_code = mrb.SPACE_CATEGORY_CODE and lookup_type = ''AHL_LTP_SPACE_CATEGORY''  and language = userenv(''LANG'')) servicecat ,
	  (select meaning from fnd_lookup_values  where lookup_code = mrb.SERVICE_TYPE_CODE and lookup_type = ''AHL_FMP_MR_SERVICE_TYPE''  and language = userenv(''LANG'')) mrServiceType ,
      mrb.down_time mrduration , mrb.uom_code MrDurationUOM ,  to_char(null) primaryvisittype ,
      (select flvt.meaning from ahl_visits_b vst,ahl_visit_tasks_b task,fnd_lookup_values flvt where vst.visit_id = task.visit_id
      and task.unit_effectivity_id = B.unit_effectivity_id and task.status_code <> ''DELETED'' and flvt.lookup_type = ''AHL_PLANNING_VISIT_TYPE''
      and flvt.lookup_code        = vst.visit_type_code and flvt.language  = userenv(''LANG'') and rownum < 2) scheduledvisittype ,
      (Select hrou.name  from ahl_visits_b vst,ahl_visit_tasks_b task,hr_all_organization_units hrou
      where vst.visit_id = task.visit_id and task.unit_effectivity_id = B.unit_effectivity_id and task.status_code <> ''DELETED''
      and vst.organization_id  = hrou.organization_id and rownum < 2) scheduledVisitOrg
      FROM AHL_UNIT_EFFECTIVITIES_B B, ahl_mr_headers_b mrb, CSI_ITEM_INSTANCES CSIB, mtl_system_items_kfv mtl,
      (SELECT AUE.unit_effectivity_id, ( CASE WHEN AUE.FLEET_HEADER_ID IS NULL AND AMO.OPERATING_ORG_ID IS NOT NULL
      THEN NULL
      WHEN AUE.FLEET_HEADER_ID IS NOT NULL AND AMO.OPERATING_ORG_ID IS NOT NULL AND AFH.OPERATING_ORG_ID != AMO.OPERATING_ORG_ID
      THEN (SELECT NAME FROM HR_ORGANIZATION_UNITS WHERE ORGANIZATION_ID= AFH.OPERATING_ORG_ID)
      WHEN AUE.FLEET_HEADER_ID IS NOT NULL AND AMO.MR_TITLE IS NULL
      THEN(SELECT NAME FROM HR_ORGANIZATION_UNITS WHERE ORGANIZATION_ID= AFH.OPERATING_ORG_ID)
      ELSE (SELECT NAME FROM HR_ORGANIZATION_UNITS WHERE ORGANIZATION_ID= AMO.MAINTENANCE_ORG_ID) END) MR_MAINTENANCE_ORG_NAME FROM AHL_UNIT_EFFECTIVITIES_B AUE, AHL_MR_HEADERS_B AMH, AHL_MR_ORGANIZATIONS AMO, AHL_FLEET_HEADERS_B AFH
      WHERE AUE.MR_HEADER_ID = AMH.MR_HEADER_ID (+) AND AMH.TITLE = AMO.MR_TITLE (+) AND AUE.FLEET_HEADER_ID = AFH.FLEET_HEADER_ID (+)
      AND ( (AUE.FLEET_HEADER_ID IS NULL AND AMO.OPERATING_ORG_ID IS NULL) OR (AFH.OPERATING_ORG_ID   IS NULL
      AND AMO.OPERATING_ORG_ID   IS NOT NULL AND (0 = (SELECT COUNT(MR_ORGANIZATION_ID) FROM AHL_MR_ORGANIZATIONS
      WHERE MR_TITLE        = AMO.MR_TITLE  AND OPERATING_ORG_ID IS NULL )))  OR (AUE.FLEET_HEADER_ID   IS NOT NULL
      AND ((AFH.OPERATING_ORG_ID = AMO.OPERATING_ORG_ID) OR ( (0  = (SELECT COUNT(MR_ORGANIZATION_ID) FROM AHL_MR_ORGANIZATIONS
      WHERE MR_TITLE       = AMO.MR_TITLE AND OPERATING_ORG_ID = AFH.OPERATING_ORG_ID ))AND AFH.OPERATING_ORG_ID != NVL(AMO.OPERATING_ORG_ID,-1)
      AND AMO.OPERATING_ORG_ID IS NULL))) OR (AUE.FLEET_HEADER_ID  IS NOT NULL AND AMO.OPERATING_ORG_ID IS NOT NULL
      AND ( (0 = (SELECT COUNT(MR_ORGANIZATION_ID) FROM AHL_MR_ORGANIZATIONS WHERE MR_TITLE = AMO.MR_TITLE
      AND (OPERATING_ORG_ID IS NULL OR OPERATING_ORG_ID    = AFH.OPERATING_ORG_ID))))) ) ) MAINTTORG ';

Line: 2014


        L_MR_SEARCH_QUERY                          := L_MR_SEARCH_QUERY || ' and exists (select ''x'' from ahl_fleet_headers_b flt
        where flt.fleet_header_id = b.fleet_header_id and flt.name like (:' ||L_BIND_INDEX ||'))';

Line: 2025


          L_MR_SEARCH_QUERY                    := L_MR_SEARCH_QUERY || ' and EXISTS (Select ''x'' from ahl_visit_tasks_b vts where vts.unit_effectivity_id =
          B.unit_effectivity_id AND vts.status_code <> ''DELETED''and rownum < 2) ';

Line: 2028


          L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' and NOT EXISTS (Select ''x'' from ahl_visit_tasks_b vts
          where vts.unit_effectivity_id = B.unit_effectivity_id AND vts.status_code <> ''DELETED''and rownum < 2) ';

Line: 2034


        L_MR_SEARCH_QUERY                          := L_MR_SEARCH_QUERY || ' and EXISTS (select ''x'' from ahl_visits_b vst,ahl_visit_tasks_b vts
        where vst.visit_id = vts.visit_id  and vts.unit_effectivity_id = B.unit_effectivity_id
        and vst.visit_number like (:' || L_BIND_INDEX || ') and vts.status_code <> ''DELETED''and rownum < 2) ';

Line: 2041


        L_MR_SEARCH_QUERY                          := L_MR_SEARCH_QUERY || ' and EXISTS (select ''x'' from ahl_visits_b vst,ahl_visit_tasks_b task,fnd_lookup_values flvt where vst.visit_id = task.visit_id
        and task.unit_effectivity_id = B.unit_effectivity_id and flvt.lookup_type = ''AHL_PLANNING_VISIT_TYPE''
        and flvt.lookup_code = vst.visit_type_code and flvt.language  = userenv(''LANG'') and flvt.meaning like (:' || L_BIND_INDEX || ') and task.status_code <> ''DELETED''and rownum < 2) ';

Line: 2048


        L_MR_SEARCH_QUERY                          := L_MR_SEARCH_QUERY || ' and EXISTS (select ''x'' from ahl_visits_b vst,ahl_visit_tasks_b task ,hr_all_organization_units hrou
        where vst.visit_id = task.visit_id and task.unit_effectivity_id = B.unit_effectivity_id and
        vst.organization_id  = hrou.organization_id
        and hrou.name like (:' || L_BIND_INDEX || ') and task.status_code <> ''DELETED''and rownum < 2) ';

Line: 2190


      l_mr_search_query := l_mr_search_query || ' and EXISTS (select ''x'' FROM AHL_FLEET_HEADERS_B FLT, HR_ALL_ORGANIZATION_UNITS HROU where
      FLT.OPERATING_ORG_ID = HROU.ORGANIZATION_ID and flt.fleet_header_id = b.fleet_header_id and HROU.NAME like (:' || l_bind_index  || '))';

Line: 2201


        L_MR_SEARCH_QUERY                          := L_MR_SEARCH_QUERY || ' and EXISTS (select ''x'' from ahl_visits_b vst,ahl_visit_tasks_b task where vst.visit_id = task.visit_id
        and task.unit_effectivity_id = B.unit_effectivity_id
        and task.status_code <> ''DELETED'' and B.due_date IS NOT NULL AND TRUNC(B.DUE_DATE) < TRUNC(vst.START_DATE_TIME) ) ';

Line: 2209


       L_NR_SEARCH_QUERY := 'select /*+ dynamic_sampling(AAI1 4) */ ';

Line: 2211


       L_NR_SEARCH_QUERY := 'select ';

Line: 2213


      L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' ''N'' select_flag, ''Show'' details,ahl_ump_util_pkg.get_unitname(b.csi_item_instance_id) unit_name,
      (select meaning from fnd_lookup_values where lookup_type = ''AHL_FMP_MR_PROGRAM_TYPE'' and lookup_code = ''NON-ROUTINE''
      AND LANGUAGE = USERENV(''LANG'')) prog_type,
      null mr_header_id,(CITT.NAME || ''-''  || CSB.INCIDENT_NUMBER) TITLE,MTL.CONCATENATED_SEGMENTS concatenated_segments,
      csib.serial_number,null name ,null uom_code,AHL_UMP_SMRINSTANCE_PVT.GET_UOM_REMAIN(b.unit_effectivity_id) due_counter_value,''View all UOMs'' view_all_uoms,
      B.EARLIEST_DUE_DATE,B.DUE_DATE,B.LATEST_DUE_DATE,b.date_run,
      (select vst.START_DATE_TIME from ahl_visits_b vst,ahl_visit_tasks_b task where vst.visit_id = task.visit_id
      and task.unit_effectivity_id = b.unit_effectivity_id and task.status_code <> ''DELETED''and rownum < 2) scheduledDate,
      (select visit_number from ahl_visits_b vst,ahl_visit_tasks_b task where vst.visit_id = task.visit_id
      and task.unit_effectivity_id = b.unit_effectivity_id and task.status_code <> ''DELETED''and rownum < 2) visit_name,
      B.CS_INCIDENT_ID sr_incident_id,CSB.INCIDENT_NUMBER sr_number,b.unit_effectivity_id,
      (Select unit_config_header_id from AHL_UNIT_CONFIG_HEADERS where name = ahl_ump_util_pkg.get_unitname(b.csi_item_instance_id)) unit_config_header_id,
      (select vst.visit_id from ahl_visits_b vst,ahl_visit_tasks_b task where vst.visit_id = task.visit_id
      and task.unit_effectivity_id = b.unit_effectivity_id and task.status_code <> ''DELETED''and rownum < 2)  visit_id,
      (select name from ahl_fleet_headers_b flt where flt.fleet_header_id = b.fleet_header_id) fleet ,
      (SELECT HROU.NAME FROM ahl_fleet_headers_b flt, HR_ALL_ORGANIZATION_UNITS HROU where flt.OPERATING_ORG_ID = hrou.organization_id
       and flt.fleet_header_id = b.fleet_header_id and rownum < 2) operatingorg,
      (select master_config_name from ahl_unit_header_details_v ucd where ucd.name = ahl_ump_util_pkg.get_unitname(b.csi_item_instance_id)) mc,
      (Select position_ref_meaning from ahl_unit_details_v ucd where ucd.csi_item_instance_id =  b.csi_item_instance_id and rownum < 2) ucposition,
      null eng_org,
      null implementstatus ,null progsubtype , null servicecat, null mrServiceType,   null mrduration,
      null MrDurationUOM , to_char(null) primaryvisittype ,
      (select flvt.meaning from ahl_visits_b vst,ahl_visit_tasks_b task,fnd_lookup_values flvt
      where vst.visit_id = task.visit_id and task.unit_effectivity_id = b.unit_effectivity_id and task.status_code <> ''DELETED''
      and flvt.lookup_type = ''AHL_PLANNING_VISIT_TYPE'' and flvt.lookup_code = vst.visit_type_code and flvt.language = userenv(''LANG'')
      and rownum < 2) scheduledvisittype ,
      (Select hrou.name  from ahl_visits_b vst,ahl_visit_tasks_b task,hr_all_organization_units hrou
      where vst.visit_id = task.visit_id and task.unit_effectivity_id = b.unit_effectivity_id and task.status_code <> ''DELETED''
      and vst.organization_id       = hrou.organization_id and rownum < 2) scheduledVisitOrg
      FROM AHL_UNIT_EFFECTIVITIES_B B,
      CS_INCIDENT_TYPES_B CITB,
      cs_incident_types_tl citt,
      CS_INCIDENTS_ALL_B CSB,
      CSI_ITEM_INSTANCES CSIB,
      mtl_system_items_kfv mtl ';

Line: 2293


        L_NR_SEARCH_QUERY                          := L_NR_SEARCH_QUERY || ' and exists (select ''x'' from ahl_fleet_headers_b flt where flt.fleet_header_id = b.fleet_header_id and flt.name like (:' ||L_BIND_INDEX ||'))';

Line: 2298


          L_NR_SEARCH_QUERY                    := L_NR_SEARCH_QUERY || ' and EXISTS (Select ''x'' from ahl_visit_tasks_b vts
            where vts.unit_effectivity_id = B.unit_effectivity_id AND vts.status_code <> ''DELETED''and rownum < 2) ';

Line: 2301


          L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' and NOT EXISTS (Select ''x'' from ahl_visit_tasks_b vts
          where vts.unit_effectivity_id = B.unit_effectivity_id and vts.status_code <> ''DELETED''and rownum < 2) ';

Line: 2307


        L_NR_SEARCH_QUERY                          := L_NR_SEARCH_QUERY || ' and EXISTS (select ''x'' from ahl_visits_b vst,ahl_visit_tasks_b task
        where vst.visit_id = task.visit_id  and task.unit_effectivity_id = B.unit_effectivity_id and vst.visit_number like (:' || L_BIND_INDEX || ') and task.status_code <> ''DELETED''and rownum < 2) ';

Line: 2313


        L_NR_SEARCH_QUERY                          := L_NR_SEARCH_QUERY || ' and EXISTS (select ''x'' from ahl_visits_b vst,ahl_visit_tasks_b task,fnd_lookup_values flvt
        where vst.visit_id = task.visit_id  and task.unit_effectivity_id = B.unit_effectivity_id and flvt.lookup_type = ''AHL_PLANNING_VISIT_TYPE''
        and flvt.lookup_code = vst.visit_type_code and flvt.language  = userenv(''LANG'')
        and flvt.meaning like (:' || L_BIND_INDEX || ') and task.status_code <> ''DELETED''and rownum < 2) ';

Line: 2321


        L_NR_SEARCH_QUERY                          := L_NR_SEARCH_QUERY || ' and EXISTS (select ''x'' from ahl_visits_b vst,ahl_visit_tasks_b task ,hr_all_organization_units hrou
        where vst.visit_id = task.visit_id and task.unit_effectivity_id = B.unit_effectivity_id and vst.organization_id  = hrou.organization_id
        and hrou.name like (:' || L_BIND_INDEX || ') and task.status_code <> ''DELETED''and rownum < 2) ';

Line: 2422


      l_nr_search_query := l_nr_search_query || ' and EXISTS (select ''x'' FROM AHL_FLEET_HEADERS_B FLT, HR_ALL_ORGANIZATION_UNITS HROU where FLT.OPERATING_ORG_ID = HROU.ORGANIZATION_ID
       and flt.fleet_header_id = b.fleet_header_id and HROU.NAME like (:' || l_bind_index  || '))';

Line: 2427


        L_NR_SEARCH_QUERY                          := L_NR_SEARCH_QUERY || ' and EXISTS (select ''x'' from ahl_visits_b vst,ahl_visit_tasks_b task where vst.visit_id = task.visit_id
        and task.unit_effectivity_id = B.unit_effectivity_id
        and task.status_code <> ''DELETED'' and B.due_date IS NOT NULL AND TRUNC(B.DUE_DATE) < TRUNC(vst.START_DATE_TIME) ) ';

Line: 2448


    DELETE AHL_APPLICABLE_INSTANCES;

Line: 2516


   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: 2525


   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: 2535


   SELECT UMP.due_counter_value,
          UMP.counter_name,
          UMP.status_code, UMP.originator_title, UMP.csi_item_instance_id,
          UMP.COUNTER_ID,
          UMP.orig_ue_instance_id
   FROM AHL_UNIT_EFFECTIVITIES_V UMP
   WHERE UMP.UNIT_EFFECTIVITY_ID = P_UNIT_EFFECTIVITY_ID;