APPS.AHL_RA_NR_PROFILE_PVT SQL Statements

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

Line: 27

SELECT fleet_header_id
FROM   ahl_unit_effectivities_b
WHERE unit_effectivity_id = c_ue_id
AND csi_item_instance_id = c_instance_id ;

Line: 34

SELECT fleet_header_id
FROM   ahl_ue_simulations
WHERE simulation_ue_id = c_ue_id
AND csi_item_instance_id = c_instance_id ;

Line: 41

SELECT  fhb.operating_org_id, fhb.operations_type_code
FROM  ahl_fleet_headers_b fhb
WHERE fhb.fleet_header_id = c_fleet_header_id
AND fhb.status_code = 'COMPLETE';

Line: 48

SELECT nph.nr_profile_header_id, nph.ident_seq_third , nph.ident_seq_fourth
FROM ahl_mr_headers_b mrh, ahl_nr_profile_headers nph
WHERE mrh.title = nph.mr_title
    AND mrh.mr_header_id = c_mr_header_id
    AND nph.status_code = 'COMPLETE';

Line: 56

SELECT nr_profile_det_id, inventory_item_id, uom_code, required_qty, stage_type_code,
fleet_header_id, operating_org_id, operations_type_code
FROM ahl_nr_profile_details
WHERE nr_profile_header_id = c_nr_profile_header_id
AND inventory_item_id IS NOT NULL
AND excluded_date IS NULL
ORDER BY stage_type_code, inventory_item_id, fleet_header_id, operating_org_id, operations_type_code;

Line: 66

SELECT nr_profile_det_id, inventory_item_id, uom_code, required_qty, stage_type_code
FROM ahl_nr_profile_details
WHERE nr_profile_header_id = c_nr_profile_header_id
AND inventory_item_id IS NOT NULL
AND excluded_date IS NULL
AND fleet_header_id  IS NULL
AND operating_org_id  IS NULL
AND operations_type_code  IS NULL
ORDER BY stage_type_code, inventory_item_id;

Line: 78

SELECT primary_plan_flag
FROM ahl_simulation_plans_b
WHERE simulation_plan_id  = c_plan_id
AND status_code = 'ACTIVE';

Line: 364

SELECT FLEET_HEADER_ID
FROM   ahl_unit_effectivities_b
WHERE unit_effectivity_id = c_ue_id
AND csi_item_instance_id = c_instance_id;

Line: 371

SELECT fleet_header_id
FROM   ahl_ue_simulations
WHERE simulation_ue_id = c_ue_id
AND csi_item_instance_id = c_instance_id ;

Line: 378

SELECT  fhb.operating_org_id, fhb.operations_type_code
FROM  ahl_fleet_headers_b fhb
WHERE fhb.fleet_header_id = c_fleet_header_id
AND fhb.status_code = 'COMPLETE';

Line: 385

SELECT nph.nr_profile_header_id, nph.ident_seq_third , nph.ident_seq_fourth
FROM ahl_mr_headers_b mrh, ahl_nr_profile_headers nph
WHERE mrh.title = nph.mr_title
    AND mrh.mr_header_id = c_mr_header_id
    AND nph.status_code = 'COMPLETE';

Line: 393

SELECT nr_profile_det_id, cmro_resource_id, required_qty, stage_type_code,
fleet_header_id, operating_org_id, operations_type_code
FROM ahl_nr_profile_details
WHERE nr_profile_header_id = c_nr_profile_header_id
AND excluded_date IS NULL
AND cmro_resource_id IS NOT NULL
ORDER BY stage_type_code, cmro_resource_id, fleet_header_id, operating_org_id, operations_type_code;

Line: 403

SELECT nr_profile_det_id, cmro_resource_id, required_qty, stage_type_code
FROM ahl_nr_profile_details
WHERE nr_profile_header_id = c_nr_profile_header_id
AND excluded_date IS NULL
AND cmro_resource_id IS NOT NULL
AND fleet_header_id  IS NULL
AND operating_org_id  IS NULL
AND operations_type_code  IS NULL
ORDER BY stage_type_code, cmro_resource_id;

Line: 415

SELECT primary_plan_flag
FROM ahl_simulation_plans_b
WHERE simulation_plan_id  = c_plan_id
AND status_code = 'ACTIVE';

Line: 723

    IF ( p_nr_prof_est_rec.demantra_updated_date IS NOT NULL AND
         p_nr_prof_est_rec.demantra_updated_date <> FND_API.G_MISS_DATE ) THEN
      l_record_identifier := l_record_identifier || p_nr_prof_est_rec.demantra_updated_date;

Line: 765

SELECT resource_id
FROM ahl_resources ar
WHERE ar.name = c_resource_name;

Line: 771

SELECT 'X'
FROM ahl_resources
WHERE resource_id = c_resource_id;

Line: 777

SELECT inventory_item_id
FROM mtl_system_items_kfv
WHERE concatenated_segments = c_item_name;

Line: 783

SELECT 'X'
FROM mtl_system_items_kfv
WHERE inventory_item_id = c_iteme_id;

Line: 789

SELECT DISTINCT uom_code
FROM ahl_item_class_uom_v
WHERE unit_of_measure = c_uom;

Line: 795

SELECT 'X'
FROM ahl_item_class_uom_v
WHERE uom_code = c_uom_code;

Line: 801

SELECT fleet_header_id
FROM ahl_fleet_headers_vl flt
WHERE flt.name = c_fleet_name
AND status_code = 'COMPLETE';

Line: 808

SELECT 'X'
FROM ahl_fleet_headers_b
WHERE fleet_header_id = c_fleet_header_id
AND status_code = 'COMPLETE';

Line: 815

SELECT organization_id
FROM hr_all_organization_units hr
WHERE hr.name = c_operating_org_name;

Line: 821

SELECT 'X'
FROM hr_all_organization_units
WHERE organization_id = c_operating_org_id;

Line: 827

SELECT object_version_number
FROM ahl_nr_profile_details
WHERE nr_profile_det_id = c_nr_profile_det_id
AND object_version_number = c_object_version_number;

Line: 1244

  SELECT nr_profile_det_id,required_qty,excluded_date
  FROM ahl_nr_profile_details
  WHERE NR_PROFILE_HEADER_ID            = c_nr_profile_header_id
  AND NVL(CMRO_RESOURCE_ID,-1)          = c_cmro_resource_id
  AND NVL(INVENTORY_ITEM_ID ,-1)         = c_inventory_item_id
  AND NVL(STAGE_TYPE_CODE,'X')            = c_stage_type_code
  AND NVL(FLEET_HEADER_ID,-1)             = c_fleet_header_id
  AND NVL(OPERATING_ORG_ID,-1)          =  c_operating_org_id
  AND NVL(OPERATIONS_TYPE_CODE,'X')   = c_operations_type_code
  AND NVL2(ANALYSIS_QTY, -2,-1)            = c_analysis_qty;

Line: 1257

  SELECT nr_profile_det_id,required_qty,excluded_date
  FROM ahl_nr_profile_details
  WHERE NR_PROFILE_HEADER_ID            = c_nr_profile_header_id
  AND NVL(CMRO_RESOURCE_ID,-1)          = c_cmro_resource_id
  AND NVL(INVENTORY_ITEM_ID ,-1)         = c_inventory_item_id
  AND NVL(STAGE_TYPE_CODE,'X')            = c_stage_type_code
  AND NVL(FLEET_HEADER_ID,-1)             = c_fleet_header_id
  AND NVL(OPERATING_ORG_ID,-1)          =  c_operating_org_id
  AND NVL(OPERATIONS_TYPE_CODE,'X')   = c_operations_type_code
  AND NVL2(ANALYSIS_QTY, -2,-1)            = c_analysis_qty
  AND excluded_date IS NOT NULL;

Line: 1271

  SELECT analysis_qty ,excluded_date, cmro_resource_id, inventory_item_id, stage_type_code, fleet_header_id ,operating_org_id, operations_type_code
  FROM ahl_nr_profile_details
  WHERE nr_profile_det_id = c_nr_profile_det_id;

Line: 1391

            INSERT INTO AHL_NR_PROFILE_DETAILS
            (
              NR_PROFILE_DET_ID,
              OBJECT_VERSION_NUMBER,
              LAST_UPDATE_DATE,
              LAST_UPDATED_BY,
              CREATION_DATE,
              CREATED_BY,
              LAST_UPDATE_LOGIN,
              NR_PROFILE_HEADER_ID,
              STAGE_TYPE_CODE,
              INVENTORY_ITEM_ID,
              UOM_CODE,
              CMRO_RESOURCE_ID,
              ANALYSIS_QTY,
              REQUIRED_QTY,
              FLEET_HEADER_ID,
              OPERATING_ORG_ID,
              OPERATIONS_TYPE_CODE,
              DEMANTRA_UPDATED_DATE,
              EXCLUDED_DATE,
              SECURITY_GROUP_ID
            )
            VALUES
            (
              p_x_nr_prof_est_tbl(i).nr_profile_det_id,
              p_x_nr_prof_est_tbl(i).object_version_number,
              SYSDATE,
              FND_GLOBAL.user_id,
              SYSDATE,
              FND_GLOBAL.user_id,
              FND_GLOBAL.login_id,
              p_x_nr_prof_est_tbl(i).nr_profile_header_id,
              p_x_nr_prof_est_tbl(i).stage_type_code,
              p_x_nr_prof_est_tbl(i).inventory_item_id,
              p_x_nr_prof_est_tbl(i).uom_code,
              p_x_nr_prof_est_tbl(i).cmro_resource_id,
              p_x_nr_prof_est_tbl(i).analysis_qty,
              p_x_nr_prof_est_tbl(i).required_qty,
              p_x_nr_prof_est_tbl(i).fleet_header_id,
              p_x_nr_prof_est_tbl(i).operating_org_id,
              p_x_nr_prof_est_tbl(i).operations_type_code,
              p_x_nr_prof_est_tbl(i).demantra_updated_date,
              NULL,
              p_x_nr_prof_est_tbl(i).security_group_id
            );

Line: 1460

            UPDATE AHL_NR_PROFILE_DETAILS
            SET analysis_qty = p_x_nr_prof_est_tbl(i).analysis_qty,
                  required_qty = p_x_nr_prof_est_tbl(i).required_qty,
                  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,
                  demantra_updated_date = p_x_nr_prof_est_tbl(i).demantra_updated_date,
                  excluded_date               = NULL
            WHERE NR_PROFILE_DET_ID = l_nr_profile_det_id ;

Line: 1489

              DELETE FROM AHL_NR_PROFILE_DETAILS
              WHERE NR_PROFILE_DET_ID = l_nr_profile_det_id;

Line: 1501

            INSERT INTO AHL_NR_PROFILE_DETAILS
            (
              NR_PROFILE_DET_ID,
              OBJECT_VERSION_NUMBER,
              LAST_UPDATE_DATE,
              LAST_UPDATED_BY,
              CREATION_DATE,
              CREATED_BY,
              LAST_UPDATE_LOGIN,
              NR_PROFILE_HEADER_ID,
              STAGE_TYPE_CODE,
              INVENTORY_ITEM_ID,
              UOM_CODE,
              CMRO_RESOURCE_ID,
              ANALYSIS_QTY,
              REQUIRED_QTY,
              FLEET_HEADER_ID,
              OPERATING_ORG_ID,
              OPERATIONS_TYPE_CODE,
              DEMANTRA_UPDATED_DATE,
              EXCLUDED_DATE,
              SECURITY_GROUP_ID
            )
            VALUES
            (
              p_x_nr_prof_est_tbl(i).nr_profile_det_id,
              p_x_nr_prof_est_tbl(i).object_version_number,
              SYSDATE,
              FND_GLOBAL.user_id,
              SYSDATE,
              FND_GLOBAL.user_id,
              FND_GLOBAL.login_id,
              p_x_nr_prof_est_tbl(i).nr_profile_header_id,
              p_x_nr_prof_est_tbl(i).stage_type_code,
              p_x_nr_prof_est_tbl(i).inventory_item_id,
              p_x_nr_prof_est_tbl(i).uom_code,
              p_x_nr_prof_est_tbl(i).cmro_resource_id,
              p_x_nr_prof_est_tbl(i).analysis_qty,
              p_x_nr_prof_est_tbl(i).required_qty,
              p_x_nr_prof_est_tbl(i).fleet_header_id,
              p_x_nr_prof_est_tbl(i).operating_org_id,
              p_x_nr_prof_est_tbl(i).operations_type_code,
              p_x_nr_prof_est_tbl(i).demantra_updated_date,
              NULL,
              p_x_nr_prof_est_tbl(i).security_group_id
            );

Line: 1569

            INSERT INTO AHL_NR_PROFILE_DETAILS
              (
                NR_PROFILE_DET_ID,
                OBJECT_VERSION_NUMBER,
                LAST_UPDATE_DATE,
                LAST_UPDATED_BY,
                CREATION_DATE,
                CREATED_BY,
                LAST_UPDATE_LOGIN,
                NR_PROFILE_HEADER_ID,
                STAGE_TYPE_CODE,
                INVENTORY_ITEM_ID,
                UOM_CODE,
                CMRO_RESOURCE_ID,
                ANALYSIS_QTY,
                REQUIRED_QTY,
                FLEET_HEADER_ID,
                OPERATING_ORG_ID,
                OPERATIONS_TYPE_CODE,
                DEMANTRA_UPDATED_DATE,
                EXCLUDED_DATE,
                SECURITY_GROUP_ID
              )
              VALUES
              (
                p_x_nr_prof_est_tbl(i).nr_profile_det_id,
                p_x_nr_prof_est_tbl(i).object_version_number,
                SYSDATE,
                FND_GLOBAL.user_id,
                SYSDATE,
                FND_GLOBAL.user_id,
                FND_GLOBAL.login_id,
                p_x_nr_prof_est_tbl(i).nr_profile_header_id,
                p_x_nr_prof_est_tbl(i).stage_type_code,
                p_x_nr_prof_est_tbl(i).inventory_item_id,
                p_x_nr_prof_est_tbl(i).uom_code,
                p_x_nr_prof_est_tbl(i).cmro_resource_id,
                p_x_nr_prof_est_tbl(i).analysis_qty,
                p_x_nr_prof_est_tbl(i).required_qty,
                p_x_nr_prof_est_tbl(i).fleet_header_id,
                p_x_nr_prof_est_tbl(i).operating_org_id,
                p_x_nr_prof_est_tbl(i).operations_type_code,
                p_x_nr_prof_est_tbl(i).demantra_updated_date,
                p_x_nr_prof_est_tbl(i).excluded_date,
                p_x_nr_prof_est_tbl(i).security_group_id
              );

Line: 1624

        UPDATE AHL_NR_PROFILE_DETAILS
        SET 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,
              required_qty                = p_x_nr_prof_est_tbl(i).required_qty
        WHERE NR_PROFILE_DET_ID = p_x_nr_prof_est_tbl(i).nr_profile_det_id ;

Line: 1647

            DELETE FROM AHL_NR_PROFILE_DETAILS
            WHERE NR_PROFILE_DET_ID = p_x_nr_prof_est_tbl(i).nr_profile_det_id;

Line: 1659

              DELETE FROM AHL_NR_PROFILE_DETAILS
              WHERE nr_profile_det_id = p_x_nr_prof_est_tbl(i).nr_profile_det_id;

Line: 1662

              UPDATE AHL_NR_PROFILE_DETAILS
              SET 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,
                    excluded_date              = SYSDATE
              WHERE nr_profile_det_id = p_x_nr_prof_est_tbl(i).nr_profile_det_id ;

Line: 1687

        UPDATE ahl_nr_profile_headers
        SET last_update_date          = SYSDATE,
              last_updated_by           =  FND_GLOBAL.user_id,
              last_update_login         =  FND_GLOBAL.login_id
        WHERE nr_profile_header_id = p_x_nr_prof_est_tbl(i).nr_profile_header_id;

Line: 1763

SELECT DISTINCT mh.title
FROM ahl_mr_headers_v mh
WHERE SYSDATE BETWEEN mh.effective_from AND NVL(mh.effective_to,SYSDATE+1)
AND mh.title NOT IN (SELECT mr_title FROM ahl_nr_profile_headers)
AND mh.title = c_mr_title;

Line: 1771

SELECT DISTINCT mh.title
FROM ahl_mr_headers_v mh
WHERE SYSDATE BETWEEN mh.effective_from AND NVL(mh.effective_to,SYSDATE+1)
AND mh.title  = c_mr_title;

Line: 1778

SELECT mr_title
FROM ahl_nr_profile_headers
WHERE mr_title = c_mr_title;

Line: 1784

SELECT status_code
FROM ahl_nr_profile_headers
WHERE nr_profile_header_id = c_nr_profile_header_id;

Line: 1790

SELECT nr_profile_header_id
FROM ahl_nr_profile_headers
WHERE mr_title = c_mr_title
AND status_code <> 'DELETED';

Line: 1797

SELECT mr_title
FROM ahl_nr_profile_headers
WHERE nr_profile_header_id = c_nr_profile_header_id
AND status_code <> 'DELETED';

Line: 1804

SELECT object_version_number
FROM ahl_nr_profile_headers
WHERE nr_profile_header_id = c_nr_profile_header_id
AND object_version_number = c_object_version_number;

Line: 1809

CURSOR get_delete_mr_title(c_mr_title VARCHAR2)
IS
SELECT max(mh.version_number)
FROM ahl_mr_headers_v mh
WHERE  mh.title  = c_mr_title;

Line: 1951

      IF(l_prof_status_code = 'DELETED' AND (p_x_nr_profile_header_rec.status_code = 'DRAFT' OR p_x_nr_profile_header_rec.status_code = 'COMPLETE'))
      THEN
        OPEN get_valid_mr_title(p_x_nr_profile_header_rec.mr_title);

Line: 1992

    INSERT INTO AHL_NR_PROFILE_HEADERS
    (
      NR_PROFILE_HEADER_ID,
      OBJECT_VERSION_NUMBER,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      CREATION_DATE,
      CREATED_BY,
      LAST_UPDATE_LOGIN,
      MR_TITLE,
      STATUS_CODE,
      IDENT_SEQ_THIRD ,
      IDENT_SEQ_FOURTH  ,
      SECURITY_GROUP_ID,
      ATTRIBUTE_CATEGORY,
      ATTRIBUTE1,
      ATTRIBUTE2,
      ATTRIBUTE3,
      ATTRIBUTE4,
      ATTRIBUTE5,
      ATTRIBUTE6,
      ATTRIBUTE7,
      ATTRIBUTE8,
      ATTRIBUTE9,
      ATTRIBUTE10,
      ATTRIBUTE11,
      ATTRIBUTE12,
      ATTRIBUTE13,
      ATTRIBUTE14,
      ATTRIBUTE15
    )
    VALUES
    (
      p_x_nr_profile_header_rec.nr_profile_header_id,
      p_x_nr_profile_header_rec.object_version_number,
      SYSDATE,
      FND_GLOBAL.user_id,
      SYSDATE,
      FND_GLOBAL.user_id,
      FND_GLOBAL.login_id,
      p_x_nr_profile_header_rec.MR_TITLE,
      p_x_nr_profile_header_rec.STATUS_CODE,
      p_x_nr_profile_header_rec.IDENT_SEQ_THIRD,
      p_x_nr_profile_header_rec.IDENT_SEQ_FOURTH,
      p_x_nr_profile_header_rec.SECURITY_GROUP_ID,
      p_x_nr_profile_header_rec.ATTRIBUTE_CATEGORY,
      p_x_nr_profile_header_rec.ATTRIBUTE1,
      p_x_nr_profile_header_rec.ATTRIBUTE2,
      p_x_nr_profile_header_rec.ATTRIBUTE3,
      p_x_nr_profile_header_rec.ATTRIBUTE4,
      p_x_nr_profile_header_rec.ATTRIBUTE5,
      p_x_nr_profile_header_rec.ATTRIBUTE6,
      p_x_nr_profile_header_rec.ATTRIBUTE7,
      p_x_nr_profile_header_rec.ATTRIBUTE8,
      p_x_nr_profile_header_rec.ATTRIBUTE9,
      p_x_nr_profile_header_rec.ATTRIBUTE10,
      p_x_nr_profile_header_rec.ATTRIBUTE11,
      p_x_nr_profile_header_rec.ATTRIBUTE12,
      p_x_nr_profile_header_rec.ATTRIBUTE13,
      p_x_nr_profile_header_rec.ATTRIBUTE14,
      p_x_nr_profile_header_rec.ATTRIBUTE15
    ) RETURNING nr_profile_header_id INTO p_x_nr_profile_header_rec.nr_profile_header_id;

Line: 2057

    UPDATE ahl_nr_profile_headers
    SET 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,
          status_code              = p_x_nr_profile_header_rec.status_code,
          ident_seq_third         = p_x_nr_profile_header_rec.ident_seq_third,
          ident_seq_fourth       = p_x_nr_profile_header_rec.ident_seq_fourth,
          security_group_id      = p_x_nr_profile_header_rec.security_group_id,
          attribute_category     = p_x_nr_profile_header_rec.attribute_category,
          attribute1                 = p_x_nr_profile_header_rec.attribute1,
          attribute2                 = p_x_nr_profile_header_rec.attribute2,
          attribute3                 = p_x_nr_profile_header_rec.attribute3,
          attribute4                 = p_x_nr_profile_header_rec.attribute4,
          attribute5                 = p_x_nr_profile_header_rec.attribute5,
          attribute6                 = p_x_nr_profile_header_rec.attribute6,
          attribute7                 = p_x_nr_profile_header_rec.attribute7,
          attribute8                 = p_x_nr_profile_header_rec.attribute8,
          attribute9                 = p_x_nr_profile_header_rec.attribute9,
          attribute10                = p_x_nr_profile_header_rec.attribute10,
          attribute11                = p_x_nr_profile_header_rec.attribute11,
          attribute12                = p_x_nr_profile_header_rec.attribute12,
          attribute13                = p_x_nr_profile_header_rec.attribute13,
          attribute14                = p_x_nr_profile_header_rec.attribute14,
          attribute15                = p_x_nr_profile_header_rec.attribute15
    WHERE nr_profile_header_id = p_x_nr_profile_header_rec.nr_profile_header_id;

Line: 2123

    OPEN get_delete_mr_title(l_mr_title);

Line: 2124

    FETCH get_delete_mr_title INTO l_mr_version_number;

Line: 2127

    IF(get_delete_mr_title%FOUND)
    THEN
      IF(l_mr_version_number = 1)
      THEN
        -- Delete Profile details(Resources, Materials)
        DELETE FROM AHL_NR_PROFILE_DETAILS
        WHERE NR_PROFILE_HEADER_ID = l_nr_profile_header_id;

Line: 2136

        DELETE FROM AHL_NR_PROFILE_HEADERS
        WHERE NR_PROFILE_HEADER_ID = l_nr_profile_header_id;

Line: 2141

       UPDATE AHL_NR_PROFILE_HEADERS
       SET 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,
            status_code                 = 'DELETED'
       WHERE nr_profile_header_id = l_nr_profile_header_id;

Line: 2151

    CLOSE get_delete_mr_title;