APPS.AHL_VWP_VISITS_STAGES_PVT SQL Statements

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

Line: 128

      SELECT START_DATE_TIME , department_id FROM AHL_VISITS_VL
      WHERE VISIT_ID = x_id;

Line: 133

	select s.stage_id, s.stage_num, s.stage_name,
          s.object_version_number, s.duration
    from ahl_vwp_stages_vl s
	where s.visit_id = c_visit_id
    order by s.stage_num;

Line: 143

	select vt.stage_id,
	--       sum(s.duration) over(order by s.stage_num) CUMUL_DURATION,
	--       min(vt.start_date_time) start_date_time,
	       max(vt.end_date_time) end_date_time
	from ahl_visit_tasks_b vt
	where vt.stage_id = C_STAGE_ID
    AND nvl(vt.status_code,'X') <> 'DELETED'
	group by vt.stage_id;

Line: 442

       AHL_DEBUG_PUB.Debug( l_full_name ||':Insert');

Line: 452

	   Ahl_VWP_Stages_Pkg.Insert_Row (
	     X_ROWID                 => l_rowid,
	     X_VISIT_ID              => P_VISIT_ID,
	     X_STAGE_ID              => Get_Stage_Id,
	     X_STAGE_NUM             => i,
	     X_STAGE_NAME            => i,
	     X_DURATION              => 0,
	     X_OBJECT_VERSION_NUMBER => 1,

   	     X_ATTRIBUTE_CATEGORY      => NULL,
	     X_ATTRIBUTE1              => NULL ,
	     X_ATTRIBUTE2              => NULL ,
	     X_ATTRIBUTE3              => NULL ,
	     X_ATTRIBUTE4              => NULL ,
	     X_ATTRIBUTE5              => NULL ,
	     X_ATTRIBUTE6              => NULL ,
	     X_ATTRIBUTE7              => NULL ,
	     X_ATTRIBUTE8              => NULL ,
	     X_ATTRIBUTE9              => NULL ,
	     X_ATTRIBUTE10             => NULL ,
	     X_ATTRIBUTE11             => NULL ,
	     X_ATTRIBUTE12             => NULL ,
	     X_ATTRIBUTE13             => NULL ,
	     X_ATTRIBUTE14             => NULL ,
	     X_ATTRIBUTE15             => NULL ,

	     X_CREATION_DATE         => SYSDATE,
	     X_CREATED_BY            => Fnd_Global.USER_ID,
	     X_LAST_UPDATE_DATE      => SYSDATE,
	     X_LAST_UPDATED_BY       => Fnd_Global.USER_ID,
	     X_LAST_UPDATE_LOGIN     => Fnd_Global.LOGIN_ID);

Line: 554

PROCEDURE Update_Stages (
   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_module_type             IN     VARCHAR2  := 'JSP',

   p_visit_id                IN     NUMBER,
   p_x_stages_tbl            IN  OUT NOCOPY Visit_Stages_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: 571

   L_API_NAME              CONSTANT VARCHAR2(30) := 'Update Stages';

Line: 584

       SELECT status_code FROM AHL_VISITS_B
                  WHERE VISIT_ID = C_VISIT_ID
                  AND STATUS_CODE IN ('PLANNING', 'PARTIALLY RELEASED', 'RELEASED' );

Line: 591

	select 'x' from ahl_workorders_v
	where visit_task_id in
	   (select DISTINCT VISIT_TASK_ID from AHL_VISIT_TASKS_B
	    where visit_id = C_VISIT_ID
	    and STAGE_ID IN (SELECT STAGE_ID FROM AHL_VWP_STAGES_B WHERE stage_num > C_STAGE_NUM
	                     AND VISIT_ID = C_VISIT_ID))
	and ( job_status_code =3  or  firm_planned_flag = 1 );

Line: 606

   SAVEPOINT Update_Stages;

Line: 674

       AHL_DEBUG_PUB.Debug( l_full_name ||':Insert');

Line: 728

		  -- Invoke the table handler to update the record
		  --
		   Ahl_VWP_stages_Pkg.Update_Row (
		     X_VISIT_ID                => P_VISIT_ID,
		     X_STAGE_ID                => p_x_stages_tbl(i).STAGE_ID,
		     X_STAGE_NUM               => p_x_stages_tbl(i).STAGE_NUM,
		     X_STAGE_NAME              => p_x_stages_tbl(i).STAGE_NAME,
		     X_DURATION                => p_x_stages_tbl(i).DURATION,
		     X_OBJECT_VERSION_NUMBER   => p_x_stages_tbl(i).OBJECT_VERSION_NUMBER+1,
		     X_ATTRIBUTE_CATEGORY      => p_x_stages_tbl(i).ATTRIBUTE_CATEGORY,
		     X_ATTRIBUTE1              => p_x_stages_tbl(i).ATTRIBUTE1,
		     X_ATTRIBUTE2              => p_x_stages_tbl(i).ATTRIBUTE2,
		     X_ATTRIBUTE3              => p_x_stages_tbl(i).ATTRIBUTE3,
		     X_ATTRIBUTE4              => p_x_stages_tbl(i).ATTRIBUTE4,
		     X_ATTRIBUTE5              => p_x_stages_tbl(i).ATTRIBUTE5,
		     X_ATTRIBUTE6              => p_x_stages_tbl(i).ATTRIBUTE6,
		     X_ATTRIBUTE7              => p_x_stages_tbl(i).ATTRIBUTE7,
		     X_ATTRIBUTE8              => p_x_stages_tbl(i).ATTRIBUTE8,
		     X_ATTRIBUTE9              => p_x_stages_tbl(i).ATTRIBUTE9,
		     X_ATTRIBUTE10             => p_x_stages_tbl(i).ATTRIBUTE10,
		     X_ATTRIBUTE11             => p_x_stages_tbl(i).ATTRIBUTE11,
		     X_ATTRIBUTE12             => p_x_stages_tbl(i).ATTRIBUTE12,
		     X_ATTRIBUTE13             => p_x_stages_tbl(i).ATTRIBUTE13,
		     X_ATTRIBUTE14             => p_x_stages_tbl(i).ATTRIBUTE14,
		     X_ATTRIBUTE15             => p_x_stages_tbl(i).ATTRIBUTE15,
		     X_LAST_UPDATE_DATE        => SYSDATE,
		     X_LAST_UPDATED_BY         => Fnd_Global.USER_ID,
		     X_LAST_UPDATE_LOGIN       => Fnd_Global.LOGIN_ID );

Line: 846

      ROLLBACK TO Update_Stages;

Line: 854

      ROLLBACK TO Update_Stages;

Line: 862

      ROLLBACK TO Update_Stages;

Line: 873

END Update_Stages;

Line: 883

PROCEDURE Delete_Stages (
   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_module_type             IN     VARCHAR2  := 'JSP',

   p_visit_id                IN     NUMBER,

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

   L_API_NAME              CONSTANT VARCHAR2(30) := 'Delete Stages';

Line: 906

   SAVEPOINT Delete_Stages;

Line: 938

	    delete from AHL_VWP_STAGES_TL
	    where stage_id
	          in (select stage_id from ahl_vwp_stages_b
	              where visit_id = p_visit_id);

Line: 943

	    delete from AHL_VWP_STAGES_B
	    where visit_id = p_visit_id;

Line: 973

      ROLLBACK TO Delete_Stages;

Line: 981

      ROLLBACK TO Delete_Stages;

Line: 989

      ROLLBACK TO Delete_Stages;

Line: 1002

end delete_stages;

Line: 1030

       SELECT 'x' FROM AHL_VWP_STAGES_VL
                  WHERE VISIT_ID = C_VISIT_ID AND
                  STAGE_ID <> C_STAGE_ID AND
                  STAGE_NAME = C_STAGE_NAME;

Line: 1037

       select stage_name, duration, object_version_number
       from AHL_VWP_STAGES_VL
       where stage_id = c_stage_id;

Line: 1045

       select 'x' from ahl_visit_tasks_b
       where stage_id = c_stage_id
	   and nvl(status_code,'X')<>'DELETED';

Line: 1190

      SELECT Ahl_vwp_stages_B_S.NEXTVAL
      FROM   dual;

Line: 1195

   SELECT 1
   FROM   Ahl_vwp_stages_b
   WHERE  stage_id = x_id;

Line: 1227

PROCEDURE VALIDATE_STAGE_UPDATES(
    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_default               IN            VARCHAR2  := Fnd_Api.G_TRUE,
    p_module_type           IN            VARCHAR2  := NULL,

    p_visit_id              IN            NUMBER,
    p_visit_task_id         IN            NUMBER,
    p_stage_name            IN            VARCHAR2   := NULL, -- defaulted as u may pass id or num

    x_stage_id              OUT NOCOPY  NUMBER            ,
    x_return_status         OUT NOCOPY    VARCHAR2,
    x_msg_count             OUT NOCOPY    NUMBER,
    x_msg_data              OUT NOCOPY    VARCHAR2
)
is

   L_MAX_PARENT  NUMBER;

Line: 1254

   L_API_NAME              CONSTANT VARCHAR2(30) := 'Update Stages';

Line: 1260

	SELECT
	  max_parent_stage_num,
	  min_child_stage_num
	FROM
	  ( SELECT
	  nvl(max(stage_num),1) max_parent_stage_num
	FROM
	  ahl_vwp_stages_b s,
	  AHL_VISIT_TASKS_b t
	WHERE
	  s.stage_id = t.stage_id and
	  t.VISIT_task_id IN
	            ( SELECT  PARENT_TASK_ID FROM AHL_TASK_LINKS WHERE VISIT_TASK_ID =  P_VISIT_TASK_ID )) ,
	  ( SELECT
	  nvl(min(stage_num),l_max_stage_num) min_child_stage_num
	FROM
	  ahl_vwp_stages_b s,
	  AHL_VISIT_TASKS_b t
	WHERE
	  s.stage_id = t.stage_id and
	  t.VISIT_task_id IN
	        ( SELECT  visit_TASK_ID FROM AHL_TASK_LINKS WHERE parent_TASK_ID = P_VISIT_TASK_ID ));

Line: 1286

       SELECT STAGE_NUM, stage_id FROM AHL_VWP_STAGES_VL
       WHERE STAGE_NAME = P_STAGE_NAME AND VISIT_ID = P_VISIT_ID;

Line: 1355

end VALIDATE_STAGE_UPDATES;

Line: 1364

	select * from ahl_vwp_stages_vl where stage_id = c_stage_id;

Line: 1410

          SELECT Stage_Id INTO x_Stage_id
            FROM AHL_VWP_STAGES_VL
          WHERE Visit_Id  = p_visit_id AND Stage_Name = p_Stage_Name;

Line: 1461

      SELECT * FROM AHL_VISITS_VL
      WHERE VISIT_ID = x_id;

Line: 1467

    SELECT COUNT(*) FROM AHL_DEPARTMENT_SHIFTS
    WHERE DEPARTMENT_ID = x_id;

Line: 1473

    SELECT DEPARTMENT_ID FROM AHL_VISIT_TASKS_B WHERE VISIT_ID = x_id
    AND NVL(STATUS_CODE,'X') <> 'DELETED' AND DEPARTMENT_ID IS NOT NULL;

Line: 1480

    SELECT DISTINCT(MR_Route_ID) "ROUTE_ID" FROM AHL_VISIT_TASKS_B TSK
    WHERE VISIT_ID = x_id AND MR_Route_ID IS NOT NULL
    AND NOT EXISTS
    (SELECT DISTINCT(MR_Route_ID) "ROUTE_ID" FROM AHL_MR_ROUTES_V MR
	  where MR.mr_route_id =TSK.mr_route_id) and rownum=1;

Line: 1491

     SELECT 1 from dual WHERE exists(
	    SELECT visit_task_id from ahl_visit_tasks_b
	    Where department_id is not null
		and visit_id =  x_visit_id
		and nvl(status_code,'X')<>'DELETED'
		and department_id not in (select department_id from ahl_department_shifts)
		);