APPS.AHL_VWP_COST_PVT SQL Statements

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

Line: 89

      SELECT workorder_id,wip_entity_id, visit_id
      FROM ahl_workorders
     WHERE visit_id = C_VISIT_ID AND VISIT_TASK_ID IS NULL
       AND master_workorder_flag = 'Y'
      AND STATUS_CODE NOT IN ('22','7');

Line: 98

  SELECT visit_task_id,cost_parent_id,level,
         task_type_code,originating_task_id
    FROM ahl_visit_tasks_b
  WHERE visit_id = C_VISIT_ID
   AND NVL(status_code,'Y') <> 'DELETED'
   START WITH cost_parent_id IS NULL
   CONNECT BY PRIOR visit_task_id = cost_parent_id
   ORDER BY LEVEL;

Line: 114

  SELECT workorder_id,wip_entity_id,visit_task_id
    FROM ahl_workorders
  WHERE visit_task_id = C_VISIT_TASK_ID
--    AND STATUS_CODE NOT IN ('22','7');

Line: 119

      ORDER BY LAST_UPDATE_DATE DESC;

Line: 258

                          'Before Calling Insert Cst Wo Hierarchy ' ||l_cst_job_tbl.count);

Line: 262

      Insert_Cst_Wo_Hierarchy (
        p_cst_job_tbl        => l_cst_job_tbl,
        p_commit             => p_commit,
        x_session_id         => x_cost_session_id,
        x_return_status      => l_return_status);

Line: 271

                        'After Calling Insert cost workorder hierarchy, Return Status = ' || l_return_status ||
                        'Session id :' ||x_cost_session_id);

Line: 280

                          'Errors from Insert cost workorder hierarchy' || x_msg_count);

Line: 358

   SELECT     wip_entity_id
   FROM       AHL_WORKORDERS
   WHERE      visit_id = c_visit_id
   AND        visit_task_id IS NULL
   AND        STATUS_CODE NOT IN ('22','7')
   AND        master_workorder_flag = 'Y';

Line: 371

   SELECT     parent_object_id,
                     child_object_id,
                     level
   FROM       WIP_SCHED_RELATIONSHIPS
   WHERE      parent_object_type_id = 1
   AND        child_object_type_id = 1
   AND        not exists (select 1 from ahl_workorders awo1
                                                where awo1.wip_entity_id = parent_object_id
                                                 and ( awo1.status_code ='22'
                                                 OR (awo1.status_code ='7'
                                               AND awo1.LAST_UPDATE_DATE <> (select MAX(LAST_UPDATE_DATE)
                                                                               from ahl_workorders awo2
                                                                              where visit_task_id = awo1.visit_task_id)))
                   )
   AND        not exists (select 1 from ahl_workorders awo1
                                                where awo1.wip_entity_id = child_object_id
                                                 and ( awo1.status_code ='22'
                                                 OR (awo1.status_code ='7'
                                               AND awo1.LAST_UPDATE_DATE <> (select MAX(LAST_UPDATE_DATE)
                                                                               from ahl_workorders awo2
                                                                              where visit_task_id = awo1.visit_task_id)))
                   )
   START WITH parent_object_id = c_wip_entity_id
              AND  relationship_type = 1
   CONNECT BY parent_object_id = PRIOR child_object_id
              AND  relationship_type = 1
   ORDER BY   level;

Line: 457

                    'Before Calling Insert_Cst_Wo_Hierarchy');

Line: 460

  Insert_Cst_Wo_Hierarchy
  (
    p_cst_job_tbl        => l_cst_job_tbl,
    p_commit             => FND_API.G_FALSE,
    x_session_id         => x_MR_session_id,
    x_return_status      => l_return_status
  );

Line: 471

                    'After Calling Insert_Cst_Wo_Hierarchy, Return Status = ' || l_return_status);

Line: 478

                          'Errors from Insert cost workorder hierarchy' || x_msg_count);

Line: 538

PROCEDURE Insert_Cst_Wo_Hierarchy (
    p_cst_job_tbl            IN           Cst_Job_Tbl,
    p_commit                 IN           VARCHAR2  := Fnd_Api.G_FALSE,
    x_session_id             OUT NOCOPY   NUMBER,
    x_return_status          OUT NOCOPY   VARCHAR2)
 IS

  l_api_name	      CONSTANT	VARCHAR2(30)	:= 'Insert_Cst_Wo_Hierarchy';

Line: 568

	SELECT MTL_EAM_ASSET_ACTIVITIES_S.nextval INTO l_group_id
	FROM DUAL;

Line: 591

                                     'Call INSERT CST_EAM_HIERARCHY_SNAPSHOT ');

Line: 593

		  INSERT INTO CST_EAM_HIERARCHY_SNAPSHOT(
		   GROUP_ID,
		   OBJECT_ID,
		   OBJECT_TYPE,
		   PARENT_OBJECT_ID,
		   PARENT_OBJECT_TYPE ,
		   LEVEL_NUM,
		   LAST_UPDATE_DATE,
		   LAST_UPDATED_BY,
		   CREATION_DATE,
		   CREATED_BY,
		   REQUEST_ID,
		   PROGRAM_APPLICATION_ID,
		   LAST_UPDATE_LOGIN)
		   VALUES
		   (
		    l_group_id,
		    l_cst_job_tbl(i).object_id,
		    l_cst_job_tbl(i).object_type,
		    l_cst_job_tbl(i).parent_object_id,
		    l_cst_job_tbl(i).parent_object_type,
		    l_cst_job_tbl(i).level_num,
		    sysdate,
		    fnd_global.user_id,
		    sysdate,
		    fnd_global.user_id,
		    null,
		    l_cst_job_tbl(i).program_application_id,
		    fnd_global.login_id
		   );

Line: 640

END Insert_Cst_Wo_Hierarchy;

Line: 667

     SELECT AWO.WIP_ENTITY_ID
        FROM AHL_WORKORDERS AWO
        WHERE AWO.VISIT_ID = x_id
      AND VISIT_TASK_ID IS NULL
      AND AWO.STATUS_CODE NOT IN ('22','7')
      AND AWO.MASTER_WORKORDER_FLAG = 'Y';

Line: 681

      SELECT AVTB.VISIT_TASK_ID, AWV.WORKORDER_ID
      FROM AHL_VISIT_TASKS_B AVTB, AHL_WORKORDERS AWV
        WHERE AVTB.VISIT_TASK_ID = AWV.VISIT_TASK_ID
        AND AVTB.VISIT_ID = x_id
        AND NVL(AVTB.status_code, 'Y') <> NVL ('DELETED', 'X')
--        AND AWV.STATUS_CODE NOT IN ('22','7')
        AND AWV.STATUS_CODE <> '22'
        AND not (AVTB.MR_ID is Null and AVTB.task_type_code='SUMMARY')
        ORDER BY AWV.LAST_UPDATE_DATE DESC;

Line: 698

      SELECT * FROM AHL_OSP_ORDER_LINES
        WHERE WORKORDER_ID = x_id;

Line: 853

     SELECT * FROM AHL_VISIT_TASKS_B
     WHERE VISIT_TASK_ID = x_id;

Line: 863

     SELECT AWO.WIP_ENTITY_ID
        FROM AHL_WORKORDERS AWO
        WHERE AWO.VISIT_TASK_ID = x_id
--      AND AWO.STATUS_CODE NOT IN ('22','7')
      AND AWO.STATUS_CODE <>'22'
      AND AWO.MASTER_WORKORDER_FLAG = 'Y'
     ORDER BY LAST_UPDATE_DATE DESC;

Line: 876

     SELECT visit_task_id
       FROM ahl_visit_tasks_b
       WHERE mr_id is not null
    START WITH visit_task_id = x_id
    CONNECT BY PRIOR visit_task_id = originating_task_id;

Line: 889

      SELECT AWV.WORKORDER_ID
      FROM AHL_WORKORDERS AWV
        WHERE AWV.VISIT_TASK_ID = x_id
--      AND AWO.STATUS_CODE NOT IN ('22','7')
      AND AWV.STATUS_CODE <>'22'
     ORDER BY LAST_UPDATE_DATE DESC;

Line: 903

      SELECT * FROM AHL_OSP_ORDER_LINES
        WHERE WORKORDER_ID = x_id;

Line: 1066

     SELECT * FROM AHL_VISIT_TASKS_B
     WHERE VISIT_TASK_ID = x_id;

Line: 1076

      SELECT AWV.WORKORDER_ID, AWV.wip_entity_id
      FROM AHL_WORKORDERS AWV
        WHERE AWV.VISIT_TASK_ID = x_id
--      AND AWV.STATUS_CODE NOT IN ('22','7')
      AND AWV.STATUS_CODE <>'22'
     ORDER BY LAST_UPDATE_DATE DESC;

Line: 1089

      SELECT * FROM AHL_OSP_ORDER_LINES
        WHERE WORKORDER_ID = x_id;

Line: 1095

     SELECT visit_task_id
       FROM ahl_visit_tasks_b
       START WITH visit_task_id = x_id
    CONNECT BY PRIOR visit_task_id = cost_parent_id;

Line: 1295

     SELECT * FROM AHL_VISIT_TASKS_B
     WHERE VISIT_TASK_ID = x_id;

Line: 1305

     SELECT AWO.WIP_ENTITY_ID, AWO.WORKORDER_ID
        FROM AHL_WORKORDERS AWO
        WHERE AWO.VISIT_TASK_ID = x_id
--      AND AWO.STATUS_CODE NOT IN ('22','7')
      AND AWO.STATUS_CODE <>'22'
      AND AWO.MASTER_WORKORDER_FLAG = 'N'
      ORDER BY LAST_UPDATE_DATE DESC;

Line: 1316

      SELECT * FROM AHL_OSP_ORDER_LINES
        WHERE WORKORDER_ID = x_id;

Line: 1498

      SELECT * FROM AHL_VISITS_VL
        WHERE VISIT_ID = x_id;

Line: 1504

      SELECT visit_id FROM AHL_VISIT_TASKS_VL
      WHERE VISIT_TASK_ID = x_T_id;

Line: 1510

      SELECT WORKORDER_ID FROM AHL_WORKORDERS
        WHERE VISIT_ID = x_id
      AND VISIT_TASK_ID IS NULL
      AND STATUS_CODE NOT IN ('22','7')
      AND MASTER_WORKORDER_FLAG = 'Y';

Line: 1592

            l_cost_price_rec.Is_Cst_Struc_updated :=  'Y';

Line: 1594

            l_cost_price_rec.Is_Cst_Struc_updated :=  'N';

Line: 1730

     p_x_cost_price_rec.Is_Cst_Struc_updated := l_cost_price_rec.Is_Cst_Struc_updated;

Line: 1743

                      'Is_Cst_Struc_updated flag: ' || p_x_cost_price_rec.Is_Cst_Struc_updated ||
                      'Work order ID: ' || p_x_cost_price_rec.workorder_id );

Line: 1781

      SELECT * FROM AHL_VISIT_TASKS_VL
      WHERE VISIT_TASK_ID = x_T_id;

Line: 1889

     l_cost_price_rec.Is_Cst_Struc_updated :=  'N';

Line: 1894

     p_x_cost_price_rec.Is_Cst_Struc_updated :=  l_cost_price_rec.Is_Cst_Struc_updated;

Line: 1931

     /*SELECT OBJECT_ID, SUM (ACTUAL_COST) ACTUAL_COST,
            SUM (ESTIMATED_COST) ESTIMATED_COST
      FROM AHL_VWP_ROLLUP_COSTS_V
     WHERE GROUP_ID = x_session_id
      AND OBJECT_ID = x_id
      AND PROGRAM_APPLICATION_ID = x_prg_id
     GROUP BY OBJECT_ID; */

Line: 1939

     SELECT *
      FROM AHL_VWP_ROLLUP_COSTS_V
     WHERE GROUP_ID = x_session_id
      AND OBJECT_ID = x_id
      AND PROGRAM_APPLICATION_ID = x_prg_id;

Line: 2031

      SELECT * FROM AHL_VISITS_VL
      WHERE VISIT_ID = x_id;

Line: 2046

      SELECT wip_entity_id
        FROM ahl_workorders
      WHERE visit_id = x_id AND STATUS_CODE NOT IN ('22','12');

Line: 2104

                           'Before Calling CST_EAMCOST_PUB.Delete_eamPerBal'  );

Line: 2107

        CST_EAMCOST_PUB.Delete_eamPerBal (
                p_api_version       => 1.0,
                p_init_msg_list     => Fnd_Api.G_FALSE,
                p_commit            => Fnd_Api.G_FALSE,
                p_validation_level  => Fnd_Api.G_VALID_LEVEL_FULL,

                x_return_status     => l_return_status,
                x_msg_count         => l_msg_count,
                x_msg_data          => l_msg_data,

    	        p_entity_id_tab     => l_entity_id_tab,
    	        p_org_id            => visit_rec.organization_id,
                p_type              => 1);

Line: 2124

                           'After Calling CST_EAMCOST_PUB.Delete_eamPerBal, Return Status = ' || l_return_status );

Line: 2132

                                   'Errors from CST_EAMCOST_PUB.Delete_eamPerBal API -COST SESSION' || l_msg_count );

Line: 2183

                                        'Errors from CST_EAMCOST_PUB.Delete_eamPerBal API -COST SESSION' || l_msg_count );