APPS.AHL_VWP_VISIT_CST_PR_PVT SQL Statements

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

Line: 66

    SELECT status_code, outside_party_flag, price_list_id, actual_price, estimated_price,
           any_task_chg_flag, service_request_id, start_date_time, close_date_time, cst_est_request_id
    FROM ahl_visits_b
    WHERE visit_id = p_visit_id;

Line: 73

    SELECT customer_id FROM cs_incidents_all_b
    WHERE incident_id = p_service_request_id;

Line: 77

    SELECT 'x' FROM ahl_visit_tasks_b
    WHERE visit_id = p_visit_id
    and nvl(status_code, 'x') <>'DELETED'
    and task_type_code <> 'STAGE'; -- added the condition by TCHIMIRA on 12-JUL-2011 -VWP: ER:12673125

Line: 84

    SELECT name from qp_list_headers_vl
    WHERE list_header_id = p_price_list_id;

Line: 89

    SELECT FLS.MEANING CP_STATUS, FLP.MEANING CP_PHASE, FCR.REQUEST_DATE CP_REQUEST_DATE
         , FCR.PHASE_CODE CP_PHASE_CODE
    FROM FND_CONCURRENT_REQUESTS FCR, FND_LOOKUPS FLS, FND_LOOKUPS FLP
    WHERE FCR.REQUEST_ID = x_cp_req_id
    AND ((FCR.STATUS_CODE = FLS.LOOKUP_CODE
    AND FLS.LOOKUP_TYPE = 'CP_STATUS_CODE')
    AND  (FCR.PHASE_CODE = FLP.LOOKUP_CODE
    AND  FLP.LOOKUP_TYPE = 'CP_PHASE_CODE'));

Line: 144

      'Got request for update visit cost details of mr session ID : ' || p_x_cost_price_rec.mr_session_id
    );

Line: 150

      'Got request for update visit cost details of cost session ID : ' || p_x_cost_price_rec.cost_session_id
    );

Line: 174

          l_cost_price_rec.Is_Cst_Struc_updated, l_cost_price_rec.service_request_id,
          l_cost_price_rec.visit_start_date, l_cost_price_rec.visit_end_date,
          l_cost_price_rec.Cp_Request_Id; --salogan::Fetched and stored request id in cost record CP ER 9299910::01-JUN-2009

Line: 306

     IF(l_cost_price_rec.Is_Cst_Struc_updated = 'N') AND (l_cost_price_rec.workorder_id IS NOT NULL) THEN

          AHL_VWP_COST_PVT.Calculate_Visit_Cost
          (
            p_visit_id          => l_cost_price_rec.visit_id,
            p_Session_id      => l_cost_price_rec.mr_session_id,
            x_Actual_cost     => l_cost_price_rec.actual_cost,
            x_Estimated_cost    => l_cost_price_rec.estimated_cost,
            x_return_status     => x_return_status
          );

Line: 372

     IF(l_cost_price_rec.Is_Cst_Struc_updated = 'N') THEN
         p_x_cost_price_rec.actual_price          := l_cost_price_rec.actual_price;

Line: 384

     p_x_cost_price_rec.Is_Cst_Struc_updated  := l_cost_price_rec.Is_Cst_Struc_updated;

Line: 446

PROCEDURE update_visit_cost_details(
    p_api_version           IN             NUMBER    := 1.0,
    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_x_cost_price_rec      IN OUT NOCOPY  AHL_VWP_VISIT_CST_PR_PVT.cost_price_rec_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: 459

    L_API_NAME             CONSTANT VARCHAR2(30) := 'update_visit_cost_details';

Line: 474

    SELECT outside_party_flag, service_request_id,
           status_code,start_date_time, close_date_time
    FROM ahl_visits_b
    WHERE visit_id = p_visit_id;

Line: 480

    SELECT 'x' FROM ahl_visit_tasks_b
    WHERE visit_id = p_visit_id
    and nvl(status_code, 'x') <>'DELETED'
    and task_type_code <> 'STAGE'; -- added the condition by TCHIMIRA on 12-JUL-2011 -VWP: ER:12673125

Line: 486

    SELECT start_date_active, end_date_active
    FROM qp_list_headers_v
    WHERE list_header_id = p_price_list_id;

Line: 493

    CURSOR update_visit_csr(p_visit_id IN NUMBER)IS
    SELECT * FROM ahl_visits_vl
    WHERE visit_id = p_visit_id
    FOR UPDATE OF object_version_number;

Line: 497

    visit_rec update_visit_csr%ROWTYPE;

Line: 504

      'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details.begin',
      'At the start of PLSQL procedure'
    );

Line: 510

     SAVEPOINT update_visit_cost_details;

Line: 525

      'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
      'Got request for update visit cost details of Visit ID : ' || p_x_cost_price_rec.visit_id
    );

Line: 531

      'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
      'outside party flag : ' || p_x_cost_price_rec.outside_party_flag
    );

Line: 537

      'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
      'Price List Name  : ' || p_x_cost_price_rec.price_list_name
    );

Line: 550

          'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
          'Visit id is mandatory but found null in input '
        );

Line: 565

          'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
          'Input OSP Flag is invalid : ' || p_x_cost_price_rec.outside_party_flag
        );

Line: 589

          'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
          'Visit id not found in ahl_visits_b table'
        );

Line: 604

          'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
          'Visit is closed so can not update outside party flag or price list'
        );

Line: 619

          'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
          'No service request is attached to visit so can not update outside party flag or price list'
        );

Line: 639

          'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
          'Can not modify outside party flag because tasks has already been created'
        );

Line: 662

                'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
                'Price List is mandatory'
            );

Line: 681

                  'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
                 'AHL_VWP_RULES_PVT.Check_Price_List_Name_Or_Id API Threw error'
                );

Line: 695

                'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
                'Price List is mandatory'
           );

Line: 730

              'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
              'Price List is not active on visit start date'
             );

Line: 751

              'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
              'Price List is not active on visit end date'
             );

Line: 773

              'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details',
              'Price List is not active on current todays date'
             );

Line: 788

     OPEN update_visit_csr(p_x_cost_price_rec.visit_id);

Line: 789

     FETCH update_visit_csr INTO visit_rec;

Line: 790

     CLOSE update_visit_csr;--not found condition not possible at this step

Line: 796

     AHL_VISITS_PKG.UPDATE_ROW
     (
        X_VISIT_ID => visit_rec.VISIT_ID,
        X_VISIT_NUMBER => visit_rec.VISIT_NUMBER,
        X_VISIT_TYPE_CODE => visit_rec.VISIT_TYPE_CODE,
        X_SIMULATION_PLAN_ID => visit_rec.SIMULATION_PLAN_ID,
        X_ITEM_INSTANCE_ID => visit_rec.ITEM_INSTANCE_ID,
        X_ITEM_ORGANIZATION_ID => visit_rec.ITEM_ORGANIZATION_ID,
        X_INVENTORY_ITEM_ID => visit_rec.INVENTORY_ITEM_ID,
        X_ASSO_PRIMARY_VISIT_ID => visit_rec.ASSO_PRIMARY_VISIT_ID,
        X_SIMULATION_DELETE_FLAG => visit_rec.SIMULATION_DELETE_FLAG,
        X_TEMPLATE_FLAG => visit_rec.TEMPLATE_FLAG,
        X_OUT_OF_SYNC_FLAG => visit_rec.OUT_OF_SYNC_FLAG,
        X_PROJECT_FLAG => visit_rec.PROJECT_FLAG,
        X_PROJECT_ID => visit_rec.PROJECT_ID,
        X_SERVICE_REQUEST_ID => visit_rec.SERVICE_REQUEST_ID,
        X_SPACE_CATEGORY_CODE => visit_rec.SPACE_CATEGORY_CODE,
        X_SCHEDULE_DESIGNATOR => visit_rec.SCHEDULE_DESIGNATOR,
        X_ATTRIBUTE_CATEGORY => visit_rec.ATTRIBUTE_CATEGORY,
        X_ATTRIBUTE1 => visit_rec.ATTRIBUTE1,
        X_ATTRIBUTE2 => visit_rec.ATTRIBUTE2,
        X_ATTRIBUTE3 => visit_rec.ATTRIBUTE3,
        X_ATTRIBUTE4 => visit_rec.ATTRIBUTE4,
        X_ATTRIBUTE5 => visit_rec.ATTRIBUTE5,
        X_ATTRIBUTE6 => visit_rec.ATTRIBUTE6,
        X_ATTRIBUTE7 => visit_rec.ATTRIBUTE7,
        X_ATTRIBUTE8 => visit_rec.ATTRIBUTE8,
        X_ATTRIBUTE9 => visit_rec.ATTRIBUTE9,
        X_ATTRIBUTE10 => visit_rec.ATTRIBUTE10,
        X_ATTRIBUTE11 => visit_rec.ATTRIBUTE11,
        X_ATTRIBUTE12 => visit_rec.ATTRIBUTE12,
        X_ATTRIBUTE13 => visit_rec.ATTRIBUTE13,
        X_ATTRIBUTE14 => visit_rec.ATTRIBUTE14,
        X_ATTRIBUTE15 => visit_rec.ATTRIBUTE15,
        X_OBJECT_VERSION_NUMBER => visit_rec.OBJECT_VERSION_NUMBER + 1,
        X_ORGANIZATION_ID => visit_rec.ORGANIZATION_ID,
        X_DEPARTMENT_ID => visit_rec.DEPARTMENT_ID,
        X_STATUS_CODE => visit_rec.STATUS_CODE,
        X_START_DATE_TIME => visit_rec.START_DATE_TIME,
        X_close_date_time => visit_rec.close_date_time,
        X_PRICE_LIST_ID => p_x_cost_price_rec.PRICE_LIST_ID,
        X_ESTIMATED_PRICE => visit_rec.ESTIMATED_PRICE,
        X_ACTUAL_PRICE => visit_rec.ACTUAL_PRICE,
        X_OUTSIDE_PARTY_FLAG => p_x_cost_price_rec.OUTSIDE_PARTY_FLAG,
        X_ANY_TASK_CHG_FLAG => visit_rec.ANY_TASK_CHG_FLAG,
        X_VISIT_NAME => visit_rec.VISIT_NAME,
        X_DESCRIPTION => visit_rec.DESCRIPTION,
        X_LAST_UPDATE_DATE => SYSDATE,
        X_LAST_UPDATED_BY => fnd_global.user_id,
        X_LAST_UPDATE_LOGIN => fnd_global.login_id,
        X_PRIORITY_CODE     => visit_rec.PRIORITY_CODE,
        X_PROJECT_TEMPLATE_ID  => visit_rec.PROJECT_TEMPLATE_ID,
        X_UNIT_SCHEDULE_ID => visit_rec.unit_schedule_id,
        X_INV_LOCATOR_ID  => visit_rec.INV_LOCATOR_ID, --Added by sowsubra
        -- SATHAPLI::Component Maintenance Planning Project, 02-Nov-2011
        -- set the Planning and In-Repair locators
        X_COMP_PLANNING_LOC_ID  => visit_rec.comp_planning_loc_id,
        X_COMP_INREPAIR_LOC_ID  => visit_rec.comp_inrepair_loc_id,
        X_TOP_PROJECT_TASK_ID => visit_rec.top_project_task_id,  --PRAKKUM :: FP:PIE :: 13-OCT-2010
        --VWPE :: ER 12424063 :: tchimira :: 15-FEB -2011 :: start
        X_PAST_DATED_VISIT_FLAG    => visit_rec.PAST_DATED_VISIT_FLAG,
        X_FIRMED_FLAG        => visit_rec.FIRMED_FLAG,
        X_LOCKED_FLAG        => visit_rec.LOCKED_FLAG,
        --VWPE :: ER 12424063 :: tchimira :: 15-FEB -2011 :: end
        -- ARUNJK MMW changes
        X_ITEM_OWNER_FLAG => visit_rec.ITEM_OWNER_FLAG
     );

Line: 881

      'ahl.plsql.AHL_VWP_VISIT_CST_PR_PVT.update_visit_cost_details.end',
      'At the end of PLSQL procedure'
    );

Line: 889

   ROLLBACK TO update_visit_cost_details;

Line: 897

   ROLLBACK TO update_visit_cost_details;

Line: 905

    ROLLBACK TO update_visit_cost_details;

Line: 908

                               p_procedure_name => 'update_visit_cost_details',
                               p_error_text     => SUBSTR(SQLERRM,1,500));

Line: 914

END update_visit_cost_details;

Line: 1113

     p_x_cost_price_rec.Is_Cst_Struc_updated  := l_cost_price_rec.Is_Cst_Struc_updated;

Line: 1171

      SELECT visit_task_id FROM ahl_visit_tasks_b VST
          WHERE  VST.task_type_code = 'SUMMARY'
      AND VST.originating_task_id =p_task_id
      AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
      AND VST.mr_id IS NOT NULL;

Line: 1180

      SELECT visit_task_id, start_date_time, end_date_time FROM ahl_visit_tasks_b VST
          WHERE  VST.task_type_code = 'PLANNED'
      AND VST.originating_task_id =p_task_id
      AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X');

Line: 1187

      SELECT min(start_date_time), max(end_date_time)
      FROM ahl_visit_tasks_b VST
      START WITH visit_task_id  = p_task_id
      AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
      CONNECT BY originating_task_id = PRIOR visit_task_id;

Line: 1366

                        Update AHL_VISIT_TASKS_B
                        set actual_price=l_actual_price,
                            estimated_price=l_estimated_price
                        where visit_task_id=l_cost_price_rec.visit_task_id;

Line: 1426

    Select VISIT_ID,
       PRICE_LIST_ID,
       SERVICE_REQUEST_ID,
       OUTSIDE_PARTY_FLAG,
       ORGANIZATION_ID,
       any_task_chg_flag
    From ahl_visits_b
    where visit_id=p_visit_id;

Line: 1438

      SELECT customer_id FROM cs_incidents_all
           WHERE incident_id = p_service_request_id;

Line: 1444

      SELECT visit_task_id FROM ahl_visit_tasks_b VST
          WHERE VST.visit_id = p_visit_id
      AND VST.task_type_code = 'SUMMARY'
      AND VST.originating_task_id IS NULL
      AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
      AND VST.mr_id IS NOT NULL;

Line: 1456

      SELECT visit_task_id FROM ahl_visit_tasks_b VST
          WHERE VST.visit_id = p_visit_id
      AND VST.task_type_code = 'SUMMARY'
      AND VST.originating_task_id IS NULL
      AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
      AND VST.mr_id IS NULL
      AND VST.unit_effectivity_id IS NOT NULL;

Line: 1466

      SELECT min(start_date_time), max(end_date_time)
      FROM ahl_visit_tasks_b VST
      START WITH visit_task_id  = p_task_id
      AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
      CONNECT BY originating_task_id = PRIOR visit_task_id;

Line: 1477

     SELECT visit_task_id, start_date_time, end_date_time FROM ahl_visit_tasks_b VST
         WHERE VST.visit_id = p_visit_id
     AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
     AND VST.task_type_code = 'UNASSOCIATED';

Line: 1483

   CURSOR update_visit_csr(p_visit_id IN NUMBER)IS
     SELECT * FROM ahl_visits_vl
      WHERE visit_id = p_visit_id
    FOR UPDATE OF object_version_number;

Line: 1488

    visit_rec update_visit_csr%ROWTYPE;

Line: 1632

    p_x_cost_price_rec.Is_Cst_Struc_updated := l_cost_price_rec.Is_Cst_Struc_updated;

Line: 1995

     OPEN update_visit_csr(p_x_cost_price_rec.visit_id);

Line: 1996

     FETCH update_visit_csr INTO visit_rec;

Line: 1997

     CLOSE update_visit_csr;--not found condition not possible at this step

Line: 2003

     AHL_VISITS_PKG.UPDATE_ROW
     (
        X_VISIT_ID => visit_rec.VISIT_ID,

        X_VISIT_NUMBER => visit_rec.VISIT_NUMBER,

        X_VISIT_TYPE_CODE => visit_rec.VISIT_TYPE_CODE,

        X_SIMULATION_PLAN_ID => visit_rec.SIMULATION_PLAN_ID,

        X_ITEM_INSTANCE_ID => visit_rec.ITEM_INSTANCE_ID,

        X_ITEM_ORGANIZATION_ID => visit_rec.ITEM_ORGANIZATION_ID,

        X_INVENTORY_ITEM_ID => visit_rec.INVENTORY_ITEM_ID,

        X_ASSO_PRIMARY_VISIT_ID => visit_rec.ASSO_PRIMARY_VISIT_ID,

        X_SIMULATION_DELETE_FLAG => visit_rec.SIMULATION_DELETE_FLAG,

        X_TEMPLATE_FLAG => visit_rec.TEMPLATE_FLAG,

        X_OUT_OF_SYNC_FLAG => visit_rec.OUT_OF_SYNC_FLAG,

        X_PROJECT_FLAG => visit_rec.PROJECT_FLAG,

        X_PROJECT_ID => visit_rec.PROJECT_ID,

        X_SERVICE_REQUEST_ID => visit_rec.SERVICE_REQUEST_ID,

        X_SPACE_CATEGORY_CODE => visit_rec.SPACE_CATEGORY_CODE,

        X_SCHEDULE_DESIGNATOR => visit_rec.SCHEDULE_DESIGNATOR,

        X_ATTRIBUTE_CATEGORY => visit_rec.ATTRIBUTE_CATEGORY,

        X_ATTRIBUTE1 => visit_rec.ATTRIBUTE1,

        X_ATTRIBUTE2 => visit_rec.ATTRIBUTE2,

        X_ATTRIBUTE3 => visit_rec.ATTRIBUTE3,

        X_ATTRIBUTE4 => visit_rec.ATTRIBUTE4,

        X_ATTRIBUTE5 => visit_rec.ATTRIBUTE5,

        X_ATTRIBUTE6 => visit_rec.ATTRIBUTE6,

        X_ATTRIBUTE7 => visit_rec.ATTRIBUTE7,

        X_ATTRIBUTE8 => visit_rec.ATTRIBUTE8,

        X_ATTRIBUTE9 => visit_rec.ATTRIBUTE9,

        X_ATTRIBUTE10 => visit_rec.ATTRIBUTE10,

        X_ATTRIBUTE11 => visit_rec.ATTRIBUTE11,

        X_ATTRIBUTE12 => visit_rec.ATTRIBUTE12,

        X_ATTRIBUTE13 => visit_rec.ATTRIBUTE13,

        X_ATTRIBUTE14 => visit_rec.ATTRIBUTE14,

        X_ATTRIBUTE15 => visit_rec.ATTRIBUTE15,

        X_OBJECT_VERSION_NUMBER => visit_rec.OBJECT_VERSION_NUMBER + 1,

        X_ORGANIZATION_ID => visit_rec.ORGANIZATION_ID,

        X_DEPARTMENT_ID => visit_rec.DEPARTMENT_ID,

        X_STATUS_CODE => visit_rec.STATUS_CODE,

        X_START_DATE_TIME => visit_rec.START_DATE_TIME,

        X_close_date_time => visit_rec.close_date_time,

        X_PRICE_LIST_ID => visit_rec.PRICE_LIST_ID,

        X_ESTIMATED_PRICE => l_estimated_price,

        X_ACTUAL_PRICE => l_actual_price,

        X_OUTSIDE_PARTY_FLAG => visit_rec.OUTSIDE_PARTY_FLAG,

        X_ANY_TASK_CHG_FLAG => visit_rec.ANY_TASK_CHG_FLAG,

        X_VISIT_NAME => visit_rec.VISIT_NAME,

        X_DESCRIPTION => visit_rec.DESCRIPTION,

        X_LAST_UPDATE_DATE => SYSDATE,

        X_LAST_UPDATED_BY => fnd_global.user_id,

        X_LAST_UPDATE_LOGIN => fnd_global.login_id,

        X_PRIORITY_CODE     => visit_rec.PRIORITY_CODE,
        X_PROJECT_TEMPLATE_ID  => visit_rec.PROJECT_TEMPLATE_ID,
        X_UNIT_SCHEDULE_ID => visit_rec.unit_schedule_id,
        X_INV_LOCATOR_ID         => visit_rec.INV_LOCATOR_ID, --Added by sowsubra
        -- SATHAPLI::Component Maintenance Planning Project, 02-Nov-2011
        -- set the Planning and In-Repair locators
        X_COMP_PLANNING_LOC_ID  => visit_rec.comp_planning_loc_id,
        X_COMP_INREPAIR_LOC_ID  => visit_rec.comp_inrepair_loc_id,
        X_TOP_PROJECT_TASK_ID => visit_rec.top_project_task_id,  --Added for PIE:FP -- PRAKKUM -- 13-OCT-2010
        --VWPE :: ER 12424063 :: tchimira :: 15-FEB -2011 :: start
        X_PAST_DATED_VISIT_FLAG    => visit_rec.PAST_DATED_VISIT_FLAG,
        X_FIRMED_FLAG        => visit_rec.FIRMED_FLAG,
        X_LOCKED_FLAG        => visit_rec.LOCKED_FLAG,
        --VWPE :: ER 12424063 :: tchimira :: 15-FEB -2011 :: end
        -- ARUNJK MMW changes
        X_ITEM_OWNER_FLAG => visit_rec.ITEM_OWNER_FLAG
     );

Line: 2220

    SELECT VT.visit_task_id, VT.mr_id,VT.estimated_price,VT.actual_price,VT.visit_task_number, V.visit_number
    FROM ahl_visit_tasks_b VT, ahl_visits_b V
    WHERE VT.VISIT_ID = V.VISIT_ID
    AND NOT (task_type_code = 'SUMMARY' AND mr_id IS NULL)
    AND V.visit_id = p_visit_id
    AND nvl(VT.status_code,'x') <> 'DELETED'
    and VT.visit_task_id not in (
      select VST.visit_task_id
      from ahl_visit_tasks_b VST,
      AHL_MR_HEADERS_APP_V mr
      where vst.mr_id = mr.mr_header_id
      and vst.visit_id =p_visit_id
      AND nvl(VST.status_code,'x') <> 'DELETED'
      and mr.billing_item_id is not null
      and vst.task_type_code <>'SUMMARY');

Line: 2304

     SELECT AHL_SNAPSHOTS_S.NEXTVAL INTO l_snapshot_number FROM DUAL;

Line: 2328

         SELECT AHL_SNAPSHOTS_S.NEXTVAL INTO l_snapshot_id FROM DUAL;

Line: 2331

         AHL_SNAPSHOTS_PKG.INSERT_ROW
          (
            X_SNAPSHOT_ID           => l_snapshot_id,

            X_OBJECT_VERSION_NUMBER => 1,

            X_SNAPSHOT_NUMBER       => l_snapshot_number,

            X_LAST_UPDATE_DATE      => SYSDATE,

            X_LAST_UPDATED_BY       => fnd_global.user_id,

            X_CREATION_DATE         => SYSDATE,

            X_CREATED_BY            => fnd_global.user_id,

            X_LAST_UPDATE_LOGIN     => fnd_global.login_id,

            X_VISIT_ID              => p_visit_id,

            X_VISIT_TASK_ID         => other_tasks_rec.visit_task_id,

            X_MR_ID                 => other_tasks_rec.mr_id,

            X_ESTIMATED_PRICE       => other_tasks_rec.estimated_price,

            X_ACTUAL_PRICE          => other_tasks_rec.actual_price,

            X_ESTIMATED_COST        => null,

            X_ACTUAL_COST           => null,

            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

          );

Line: 2490

    SELECT visit_task_id, mr_id FROM ahl_visit_tasks_vl
    WHERE mr_id IS NULL
    AND task_type_code = 'SUMMARY'
    AND visit_id = p_visit_id
    AND nvl(status_code,'x') <> 'DELETED';

Line: 2497

    SELECT visit_task_id, mr_id FROM ahl_visit_tasks_vl
    WHERE visit_id = p_visit_id
    AND nvl(status_code,'x') <> 'DELETED'
    AND NOT (task_type_code = 'SUMMARY' AND mr_id IS NULL);

Line: 2508

      SELECT   workorder_id,wip_entity_id
      FROM     ahl_workorders
      WHERE    visit_task_id =  p_visit_task_id
      AND      STATUS_CODE <> '22';

Line: 2580

     SELECT AHL_SNAPSHOTS_S.NEXTVAL INTO l_snapshot_number FROM DUAL;

Line: 2636

          SELECT AHL_SNAPSHOTS_S.NEXTVAL INTO l_snapshot_id FROM DUAL;

Line: 2639

          AHL_SNAPSHOTS_PKG.INSERT_ROW
          (
            X_SNAPSHOT_ID           => l_snapshot_id,
            X_OBJECT_VERSION_NUMBER => 1,
            X_SNAPSHOT_NUMBER       => l_snapshot_number,
            X_LAST_UPDATE_DATE      => SYSDATE,
            X_LAST_UPDATED_BY       => fnd_global.user_id,
            X_CREATION_DATE         => SYSDATE,
            X_CREATED_BY            => fnd_global.user_id,
            X_LAST_UPDATE_LOGIN     => fnd_global.login_id,
            X_VISIT_ID              => p_x_cost_price_rec.visit_id,
            X_VISIT_TASK_ID         => summary_tasks_rec.visit_task_id,
            X_MR_ID                 => summary_tasks_rec.mr_id,
            X_ESTIMATED_PRICE       => null,
            X_ACTUAL_PRICE          => null,
            X_ESTIMATED_COST        => l_estimated_cost,
            X_ACTUAL_COST           => l_actual_cost,
            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
          );

Line: 2703

        SELECT AHL_SNAPSHOTS_S.NEXTVAL INTO l_snapshot_id FROM DUAL;

Line: 2706

        AHL_SNAPSHOTS_PKG.INSERT_ROW
        (
            X_SNAPSHOT_ID           => l_snapshot_id,
            X_OBJECT_VERSION_NUMBER => 1,
            X_SNAPSHOT_NUMBER       => l_snapshot_number,
            X_LAST_UPDATE_DATE      => SYSDATE,
            X_LAST_UPDATED_BY       => fnd_global.user_id,
            X_CREATION_DATE         => SYSDATE,
            X_CREATED_BY            => fnd_global.user_id,
            X_LAST_UPDATE_LOGIN     => fnd_global.login_id,
            X_VISIT_ID              => p_x_cost_price_rec.visit_id,
            X_VISIT_TASK_ID         => other_tasks_rec.visit_task_id,
            X_MR_ID                 => other_tasks_rec.mr_id,
            X_ESTIMATED_PRICE       => null,
            X_ACTUAL_PRICE          => null,
            X_ESTIMATED_COST        => l_estimated_cost,
            X_ACTUAL_COST           => l_actual_cost,
            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
        );

Line: 2829

Select  visit_id,
        outside_party_flag,
        organization_id,
        price_list_id,
        service_request_id
From  ahl_visits_b
where visit_id=c_visit_id;

Line: 2841

Select customer_id
From  CS_INCIDENTS_ALL_B
Where incident_id=c_incident_id;

Line: 2849

Select visit_id,
       visit_task_id,
       originating_task_id
from ahl_visit_tasks_vl
where originating_task_id is null
and visit_id =c_visit_id
and task_type_code ='SUMMARY'
AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X')
and mr_id is not null;

Line: 2864

Select visit_task_id,
       visit_id,
       mr_id
From ahl_visit_tasks_vl
where visit_id=c_visit_id
AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X')
and task_type_code='UNASSOCIATED';

Line: 2876

      SELECT min(start_date_time), max(end_date_time)
      FROM ahl_visit_tasks_vl VST
      START WITH visit_task_id  = p_task_id
      AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
      CONNECT BY originating_task_id = PRIOR visit_task_id;

Line: 3366

Select any_task_chg_flag, status_code
From ahl_visits_b
where visit_id=c_visit_id;

Line: 3375

SELECT vt.visit_id,
vt.visit_task_id,
vt.visit_task_number
FROM ahl_visit_tasks_vl vt
WHERE vt.visit_id = C_VISIT_ID
AND not (vt.task_type_code = 'SUMMARY' AND VT.mr_id IS NULL)
AND NVL(vt.status_code, 'Y') <> NVL ('DELETED', 'X');

Line: 3388

SELECT workorder_id,
workorder_name,
wip_entity_id,
visit_id,
master_workorder_flag
FROM ahl_workorders wo
WHERE wo.visit_id = C_VISIT_ID
AND wo.visit_task_id IS NULL
AND wo.status_code NOT IN (22,7)
AND wo.master_workorder_flag = 'Y';

Line: 3404

SELECT 1
FROM ahl_workorders wo
WHERE wo.visit_task_id = C_VISIT_TASK_ID
AND wo.status_code NOT IN (22,7);

Line: 3613

   SELECT * FROM AHL_VISITS_B
     WHERE VISIT_ID = c_id
     FOR UPDATE OF OBJECT_VERSION_NUMBER;

Line: 3620

   SELECT 'X'
     FROM FND_CONCURRENT_REQUESTS FCR, AHL_VISITS_B AVB
     WHERE FCR.REQUEST_ID IN ( AVB.CST_EST_REQUEST_ID, AVB.REQUEST_ID )
     AND FCR.PHASE_CODE IN ('P', 'R')
     AND AVB.VISIT_ID = c_id;

Line: 3648

    SELECT visit_number, status_code INTO l_visit_num, l_visit_status FROM AHL_VISITS_B WHERE visit_id = p_x_cost_price_rec.Visit_id;

Line: 3745

       UPDATE ahl_visits_b
        SET CST_EST_REQUEST_ID = l_req_id,
        OBJECT_VERSION_NUMBER = object_version_number + 1,
        LAST_UPDATE_DATE      = SYSDATE,
        LAST_UPDATED_BY       = Fnd_Global.USER_ID,
        LAST_UPDATE_LOGIN     = Fnd_Global.LOGIN_ID
        WHERE visit_id = p_x_cost_price_rec.Visit_id;

Line: 3850

   SELECT visit_id INTO l_cost_price_rec.visit_id FROM AHL_VISITS_B WHERE visit_number = p_visit_number;