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_AMP_WORKBENCH_PVT SQL Statements

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

Line: 22


G_LAST_UPDATE_DATE   DATE          := SYSDATE;

Line: 23


G_LAST_UPDATED_BY   NUMBER(15)    := FND_GLOBAL.user_id;

Line: 24


G_LAST_UPDATE_LOGIN   NUMBER(15)  := FND_GLOBAL.login_id;

Line: 57


 select count(distinct drc.AVAILABLE_DATE) no_of_working_date from AHL_DEPT_RESOURCE_CAPACITY drc where drc.available_date between TRUNC(c_start_date) and TRUNC(c_end_date) AND drc.organization_id = c_org_id  AND drc.department_id = c_dept_id AND
  drc.ASCP_PLAN_DATE  = (SELECT MAX(ASCP_PLAN_DATE) FROM AHL_DEPT_RESOURCE_CAPACITY ) order by drc.available_date;

Line: 85


	SELECT  DISTINCT cal.calendar_date working_date
    FROM mtl_parameters param,
      bom_calendar_dates cal,
      bom_shift_times shifts
    WHERE param.organization_id = c_org_id
    AND TRUNC(cal.calendar_date) >= TRUNC(c_start_date)
    AND cal.calendar_code    = param.calendar_code
    AND cal.exception_set_id = param.calendar_exception_set_id
    AND param.calendar_code  = shifts.calendar_code
      --AND TRUNC(cal.calendar_date) >= TRUNC(sysdate)
    AND ((cal.seq_num IS NOT NULL
    AND NOT EXISTS
      (SELECT 1
      FROM CRP_CAL_SHIFT_DELTA delta1
      WHERE delta1.calendar_code  = shifts.calendar_code
      AND delta1.exception_set_id = param.calendar_exception_set_id
      AND delta1.delta_code       = 1
      AND delta1.calendar_date    = cal.calendar_date
      AND delta1.shift_num        = shifts.shift_num
      ))
    OR (cal.seq_num IS NULL
    AND EXISTS
      (SELECT 1
      FROM CRP_CAL_SHIFT_DELTA delta1
      WHERE delta1.calendar_code  = shifts.calendar_code
      AND delta1.exception_set_id = param.calendar_exception_set_id
      AND delta1.delta_code       = 2
      AND delta1.calendar_date    = cal.calendar_date
      AND delta1.shift_num        = shifts.shift_num
      ))) order by cal.calendar_date;

Line: 303


	  select cal_date from
	  (select distinct drc.AVAILABLE_DATE cal_date from AHL_DEPT_RESOURCE_CAPACITY drc where drc.available_date >=TRUNC(c_start_date)
	  AND drc.organization_id = c_org_id AND drc.department_id = c_dept_id AND drc.ASCP_PLAN_DATE    = (SELECT MAX(ASCP_PLAN_DATE) FROM AHL_DEPT_RESOURCE_CAPACITY )  order by drc.available_date)
	  where  ROWNUM < (c_no_of_days+1) ; /* Rule is :Rownum is executed first then follwed by order by , thats why made a sub query and put row num on top of that*/

Line: 313


     SELECT SUM (AHL_AMP_WORKBENCH_PVT.GET_DAY_REQUIREMENT(NVL(wor.usage_rate_or_amount,0),
	                   NVL(wor.APPLIED_RESOURCE_UNITS,0),wor.organization_id,c_department_id,wor.start_date,wor.completion_date,c_on_date)) req_amt, wor.uom_code
    FROM wip_operation_resources wor,
      wip_discrete_jobs wdj,
      wip_entities wip
    WHERE wor.organization_id                                                 = c_oranization_id
    AND wor.department_id                                                     = c_department_id
    AND wor.resource_id                                                       = c_resource_id
    AND c_on_date   BETWEEN TRUNC(wor.start_date) AND TRUNC(wor.completion_date)
    AND wor.wip_entity_id                                                     = wdj.wip_entity_id
    AND wdj.status_type                                                      IN (17,3,1,6) /* included are Draft(17), Released (3), Unreleased(1) and On Hold (6).*/
    AND wor.wip_entity_id                                                     =wip.wip_entity_id
    AND wip.entity_type                                                      IN (1,2,4,5,6)
  GROUP BY wor.uom_code;

Line: 331


    SELECT DECODE(UOM_CODE,'DAY',CAPACITY_UNITS * 24,CAPACITY_UNITS) CAPACITY_UNITS ,
      UOM_CODE
    FROM AHL_DEPT_RESOURCE_CAPACITY drc
    WHERE drc.ORGANIZATION_ID = c_oranization_id
    AND drc.DEPARTMENT_ID     =c_department_id
    AND drc.RESOURCE_ID       =c_resource_id
    AND drc.AVAILABLE_DATE    =TRUNC(c_avail_dt)
    AND drc.ASCP_PLAN_DATE    =
      (SELECT MAX(ASCP_PLAN_DATE) FROM AHL_DEPT_RESOURCE_CAPACITY
      );

Line: 350


    SELECT MAX(ASCP_PLAN_DATE)
    INTO x_plan_date
    FROM AHL_DEPT_RESOURCE_CAPACITY;

Line: 502


  SELECT SPACE_ID,
    SPACE_NAME,
    DEPARTMENT_ID,
    DEPT_DESCRIPTION,
    ORGANIZATION_ID
  FROM
    (SELECT ASPL.SPACE_ID,
      ASPL.SPACE_NAME,
      ASPL.BOM_DEPARTMENT_ID DEPARTMENT_ID,
      ASPL.ORGANIZATION_ID,
      BDPT.DESCRIPTION DEPT_DESCRIPTION
    FROM AHL_SPACES_VL ASPL,
      BOM_DEPARTMENTS BDPT
    WHERE ASPL.BOM_DEPARTMENT_ID = BDPT.DEPARTMENT_ID
    AND ASPL.ORGANIZATION_ID     = c_org_id
    AND ASPL.INACTIVE_FLAG LIKE 'Y'

    UNION

    SELECT NULL SPACE_ID,
      NULL SPACE_NAME,
      BOM.DEPARTMENT_ID,
      ORG.ORGANIZATION_ID,
      BOM.DESCRIPTION DEPT_DESCRIPTION
    FROM BOM_DEPARTMENTS BOM,
      INV_ORGANIZATION_INFO_V ORG,
      MTL_PARAMETERS MP
    WHERE BOM.ORGANIZATION_ID = ORG.ORGANIZATION_ID
    AND MP.ORGANIZATION_ID    = BOM.ORGANIZATION_ID
    AND MP.EAM_ENABLED_FLAG   = 'Y'
    AND ORG.ORGANIZATION_ID     = c_org_id
    )QRSLT
  WHERE UPPER(DEPT_DESCRIPTION) LIKE UPPER(NVL(c_dept_name, DEPT_DESCRIPTION))
  AND UPPER(NVL(SPACE_NAME,'X')) LIKE UPPER(NVL(c_space_name, NVL(SPACE_NAME,'X')))
  ORDER BY DEPT_DESCRIPTION,
    SPACE_NAME;

Line: 768


  SELECT ASA.SPACE_ID,
    AVL.visit_id ,
    NVL2(ASA.START_FROM , ASA.START_FROM , AVL.START_DATE_TIME) START_DATE_TIME ,
    NVL2(ASA.END_TO , ASA.END_TO , AVL.CLOSE_DATE_TIME) CLOSE_DATE_TIME
  FROM AHL_SPACE_ASSIGNMENTS ASA,
    AHL_VISITS_VL AVL
  WHERE ASA.visit_id      = AVL.visit_id
  AND ASA.SPACE_ID        = c_space_id
  AND AVL.department_id   = c_dept_id
  AND AVL.organization_id = c_org_id
  AND AVL.status_code    IN ('PLANNING','RELEASED','PARTIALLY RELEASED')
  AND AVL.template_flag   = 'N'
  AND TRUNC(c_loop_date) BETWEEN TRUNC(NVL2(ASA.START_FROM, ASA.START_FROM
                                                    , AVL.START_DATE_TIME))
                         AND TRUNC(NVL2(ASA.END_TO, ASA.END_TO
                                            , NVL(AVL.CLOSE_DATE_TIME, c_loop_date + 1)))
  ORDER BY AVL.START_DATE_TIME;

Line: 791


  SELECT AVL.visit_id,
    AVL.START_DATE_TIME,
    AVL.CLOSE_DATE_TIME
  FROM AHL_VISITS_VL AVL
  WHERE AVL.visit_id NOT IN
    (SELECT DISTINCT ASA.VISIT_ID
     FROM AHL_SPACE_ASSIGNMENTS ASA,
      AHL_VISITS_VL AVL
     WHERE TRUNC(c_loop_date) BETWEEN TRUNC(NVL2(ASA.START_FROM, ASA.START_FROM , AVL.START_DATE_TIME))
           AND TRUNC(NVL2(ASA.END_TO, ASA.END_TO , NVL(AVL.CLOSE_DATE_TIME, c_loop_date)))
    )
AND AVL.department_id   = c_dept_id
AND AVL.organization_id = c_org_id
AND AVL.status_code    IN ('PLANNING','RELEASED','PARTIALLY RELEASED')
AND AVL.TEMPLATE_FLAG   = 'N'
AND TRUNC(c_loop_date) BETWEEN TRUNC(AVL.START_DATE_TIME)
                       AND TRUNC(NVL(AVL.CLOSE_DATE_TIME, c_loop_date + 1))
ORDER BY AVL.START_DATE_TIME;

Line: 867


       l_sch_visits_tbl.DELETE;

Line: 1024


  SELECT DISTINCT UCH.NAME UNIT_NAME,
    UCH.UNIT_CONFIG_HEADER_ID UNIT_ID
  FROM AHL_UNIT_CONFIG_HEADERS UCH,
    AHL_FLEET_HEADERS_B FHB,
    AHL_FLEET_UNIT_ASSOCS FUA,
    AHL_MC_HEADERS_B MC
  WHERE FUA.UNIT_CONFIG_HEADER_ID = UCH.UNIT_CONFIG_HEADER_ID
  AND FUA.FLEET_HEADER_ID         = FHB.FLEET_HEADER_ID
  AND UCH.MASTER_CONFIG_ID        = MC.MC_HEADER_ID
  AND FUA.SIMULATION_PLAN_ID      =
    (SELECT ASP.SIMULATION_PLAN_ID
    FROM AHL_SIMULATION_PLANS_B ASP
    WHERE ASP.PRIMARY_PLAN_FLAG = 'Y'
    AND ASP.status_code         = 'ACTIVE'
	AND ASP.simulation_type = 'UMP' -- Sthilak added this extra filter condition AMP-AutoVisit Changes
    )
AND UCH.UNIT_CONFIG_STATUS_CODE           <> 'DRAFT'
AND TRUNC(NVL(UCH.ACTIVE_END_DATE,c_end_date+1)) > TRUNC(c_end_date)
AND FHB.STATUS_CODE                       = 'COMPLETE'
AND FHB.FLEET_HEADER_ID = c_fleet_id
AND UPPER(UCH.NAME) LIKE UPPER(NVL(c_unit_name,UCH.NAME))
AND UPPER(MC.NAME) LIKE UPPER(NVL(c_mc_name,MC.NAME))
AND (TRUNC(c_start_date) BETWEEN TRUNC(FUA.association_start) AND TRUNC(NVL(FUA.association_end, c_end_date))
     OR TRUNC(c_end_date) BETWEEN TRUNC(FUA.association_start) AND TRUNC(NVL(FUA.association_end, c_end_date))
     OR TRUNC(FUA.association_start) BETWEEN TRUNC(c_start_date) AND TRUNC(c_end_date))
ORDER BY UCH.NAME;

Line: 1280


  SELECT AVT.visit_id,
    AVT.START_DATE_TIME,
    AVT.CLOSE_DATE_TIME
  FROM
    (SELECT AVL.visit_id,
      AVL.START_DATE_TIME,
      AVL.CLOSE_DATE_TIME,
      UCH.UNIT_CONFIG_HEADER_ID,
      TRUNC((AVL.CLOSE_DATE_TIME - AVL.START_DATE_TIME)*24) visit_duration
    FROM ahl_visits_vl AVL,
      AHL_UNIT_CONFIG_HEADERS UCH
    WHERE AVL.status_code       IN ('PLANNING','RELEASED','PARTIALLY RELEASED')
    AND AVL.template_flag        = 'N'
    AND UCH.csi_item_instance_id = AVL.Item_Instance_id
    ) AVT
WHERE NVL(AVT.visit_duration,10000) > NVL(c_duration,0)
AND AVT.UNIT_CONFIG_HEADER_ID   = c_unit_id
AND TRUNC(c_loop_date) BETWEEN TRUNC(AVT.START_DATE_TIME)
                       AND TRUNC(NVL(AVT.CLOSE_DATE_TIME, c_loop_date + 1))
ORDER BY AVT.START_DATE_TIME;

Line: 1305


  SELECT 'X'
  FROM AHL_UNIT_CONFIG_HEADERS UCH,
    AHL_FLEET_HEADERS_B FHB,
    AHL_FLEET_UNIT_ASSOCS FUA
  WHERE fua.unit_config_header_id = uch.unit_config_header_id
  AND FUA.FLEET_HEADER_ID         = FHB.FLEET_HEADER_ID
  AND FUA.SIMULATION_PLAN_ID      =
    (SELECT ASP.SIMULATION_PLAN_ID
    FROM AHL_SIMULATION_PLANS_B ASP
    WHERE ASP.PRIMARY_PLAN_FLAG = 'Y'
    AND ASP.status_code         = 'ACTIVE'
	AND ASP.simulation_type = 'UMP' -- Sthilak added this extra filter condition AMP-AutoVisit Changes
    )
AND UCH.UNIT_CONFIG_STATUS_CODE                 <> 'DRAFT'
AND TRUNC(NVL(uch.active_end_date,c_loop_date+1)) > TRUNC(c_loop_date)
AND fhb.status_code                              = 'COMPLETE'
AND fua.unit_config_header_id                    = c_unit_id
AND fua.fleet_header_id                          = c_fleet_id
AND c_loop_date BETWEEN TRUNC(FUA.association_start) AND TRUNC(NVL(FUA.association_end, c_loop_date));

Line: 1378


       l_sch_visits_tbl.DELETE;

Line: 1705


  SELECT DISTINCT FHB.NAME FLEET_NAME,
    FUA.association_start
  FROM AHL_UNIT_CONFIG_HEADERS UCH,
    AHL_FLEET_HEADERS_B FHB,
    AHL_FLEET_UNIT_ASSOCS FUA,
    AHL_MC_HEADERS_B MC
  WHERE FUA.UNIT_CONFIG_HEADER_ID = UCH.UNIT_CONFIG_HEADER_ID
  AND FUA.FLEET_HEADER_ID         = FHB.FLEET_HEADER_ID
  AND UCH.MASTER_CONFIG_ID        = MC.MC_HEADER_ID
  AND FUA.SIMULATION_PLAN_ID      =
    (SELECT ASP.SIMULATION_PLAN_ID
    FROM AHL_SIMULATION_PLANS_B ASP
    WHERE ASP.PRIMARY_PLAN_FLAG = 'Y'
    AND ASP.status_code         = 'ACTIVE'
	AND ASP.simulation_type = 'UMP' -- Sthilak added this extra filter condition AMP-AutoVisit Changes
    )
AND UCH.UNIT_CONFIG_STATUS_CODE           <> 'DRAFT'
AND FHB.STATUS_CODE                       = 'COMPLETE'
AND TRUNC(NVL(UCH.ACTIVE_END_DATE,c_end_date+1)) > TRUNC(c_end_date)
AND UCH.csi_item_instance_id              = c_instance_id
AND (TRUNC(c_start_date) BETWEEN TRUNC(FUA.association_start) AND TRUNC(NVL(FUA.association_end, c_end_date))
     OR TRUNC(c_end_date) BETWEEN TRUNC(FUA.association_start) AND TRUNC(NVL(FUA.association_end, c_end_date))
     OR TRUNC(FUA.association_start) BETWEEN TRUNC(c_start_date) AND TRUNC(c_end_date))
ORDER BY association_start;

Line: 1762


  SELECT DISTINCT FHB.NAME FLEET_NAME,
    FUA.association_start,
    FHB.FLEET_HEADER_ID
  FROM AHL_UNIT_CONFIG_HEADERS UCH,
    AHL_FLEET_HEADERS_B FHB,
    AHL_FLEET_UNIT_ASSOCS FUA,
    AHL_MC_HEADERS_B MC
  WHERE FUA.UNIT_CONFIG_HEADER_ID = UCH.UNIT_CONFIG_HEADER_ID
  AND FUA.FLEET_HEADER_ID         = FHB.FLEET_HEADER_ID
  AND UCH.MASTER_CONFIG_ID        = MC.MC_HEADER_ID
  AND FUA.SIMULATION_PLAN_ID      =
    (SELECT ASP.SIMULATION_PLAN_ID
    FROM AHL_SIMULATION_PLANS_B ASP
    WHERE ASP.PRIMARY_PLAN_FLAG = 'Y'
    AND ASP.status_code         = 'ACTIVE'
	AND ASP.simulation_type = 'UMP' -- Sthilak added this extra filter condition AMP-AutoVisit Changes
    )
AND UCH.UNIT_CONFIG_STATUS_CODE           <> 'DRAFT'
AND FHB.STATUS_CODE                       = 'COMPLETE'
AND TRUNC(NVL(UCH.ACTIVE_END_DATE,c_end_date+1)) > TRUNC(c_end_date)
AND UCH.csi_item_instance_id              = c_instance_id
AND (TRUNC(c_start_date) BETWEEN TRUNC(FUA.association_start) AND TRUNC(NVL(FUA.association_end, c_end_date))
     OR TRUNC(c_end_date) BETWEEN TRUNC(FUA.association_start) AND TRUNC(NVL(FUA.association_end, c_end_date))
     OR TRUNC(FUA.association_start) BETWEEN TRUNC(c_start_date) AND TRUNC(c_end_date))
ORDER BY association_start;