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

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

Line: 363


 SELECT ITEM_COMP_DETAIL_ID
 FROM AHL_ITEM_COMP_DETAILS
 WHERE ITEM_GROUP_ID = P_ITEM_GROUP_ID AND
        ITEM_COMPOSITION_ID = (
                        SELECT IC.ITEM_COMPOSITION_ID FROM
                        AHL_ROUTE_EFFECTIVITIES RE, AHL_ITEM_COMPOSITIONS IC
                        WHERE RE.INVENTORY_ITEM_ID=IC.INVENTORY_ITEM_ID(+) AND
                        RE.INVENTORY_MASTER_ORG_ID=IC.INVENTORY_MASTER_ORG_ID(+) AND
                        IC.APPROVAL_STATUS_CODE(+)='COMPLETE' AND
                        ROUTE_EFFECTIVITY_ID = P_ROUTE_EFFECITIVITY_ID ) AND
      EFFECTIVE_END_DATE IS NULL;

Line: 375


/*SELECT item_comp_detail_id
FROM AHL_ITEM_COMP_DETAILS
WHERE item_group_id = p_item_group_id AND
      item_composition_id = (
                 SELECT item_composition_id FROM AHL_ROUTE_EFFECTIVITIES_V
                 WHERE route_effectivity_id = p_route_effecitivity_id
                 ) AND
      effective_end_date IS NULL;*/

Line: 947


SELECT  item_group_id,
        item_group_name,
        inventory_item_id,
        inventory_org_id,
        item_number,
        item_comp_detail_id,
        position_path_id,
        position_path,
        uom_code,
        uom,
        quantity,
        attribute_category,
        attribute1,
        attribute2,
        attribute3,
        attribute4,
        attribute5,
        attribute6,
        attribute7,
        attribute8,
        attribute9,
        attribute10,
        attribute11,
        attribute12,
        attribute13,
        attribute14,
        attribute15,
        exclude_flag,
        rework_percent,
       replace_percent,
       in_service --pdoki added for OGMA 105 issue
FROM    AHL_RT_OPER_MATERIALS_V
WHERE   rt_oper_material_id = c_rt_oper_material_id;

Line: 1232


SELECT
inventory_item_id
, inventory_master_org_id
, concatenated_segments
, item_description
, organization_code
, mc_id
, mc_name
, mc_revision
, mc_header_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
from ahl_route_effectivities_v
WHERE   ROUTE_EFFECTIVITY_ID = C_ROUTE_EFFECTIVITY_ID
;

Line: 1464


SELECT DISTINCT MI.comms_nl_trackable_flag
FROM            MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
WHERE           MP.organization_id = MI.organization_id
AND             MI.inventory_item_id = c_inventory_item_id
AND             MI.organization_id = c_inventory_org_id
AND             MI.enabled_flag = 'Y'
AND             SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
                        AND     NVL( MI.end_date_active, SYSDATE );

Line: 1902


SELECT
         ROMV.ASSOCIATION_TYPE_CODE,
         ROMV.POSITION_PATH_ID,
         ROMV.item_group_id,
         ROMV.inventory_item_id,
         ROMV.inventory_org_id
FROM
   AHL_RT_OPER_MATERIALS ROMV
WHERE
  ROMV.object_id = c_object_id AND
  ROMV.association_type_code = c_association_type AND
  NOT EXISTS
   (SELECT item_comp_detail_id
    FROM ahl_item_comp_details
    WHERE item_comp_detail_id = ROMV.item_comp_detail_id
    AND
    effective_end_date is not null)
GROUP BY
         ROMV.ASSOCIATION_TYPE_CODE,
         ROMV.POSITION_PATH_ID,
         ROMV.item_group_id,
         ROMV.inventory_item_id,
         ROMV.inventory_org_id
HAVING   count(*) > 1;

Line: 1984


SELECT
         mc_id,
         mc_name,
         MC_REVISION,
         mc_header_id,
         organization_code,
         inventory_item_id,
         inventory_master_org_id,
         CONCATENATED_SEGMENTS,
         item_description
FROM     AHL_ROUTE_EFFECTIVITIES_V
WHERE    ROUTE_ID = c_object_id
GROUP BY
         mc_id,
         mc_name,
         MC_REVISION,
         mc_header_id,
         organization_code,
         inventory_item_id,
         inventory_master_org_id,
         CONCATENATED_SEGMENTS,
         item_description
HAVING   count(*) > 1;*/

Line: 2011


SELECT   mc_id,
         mc_header_id,
         inventory_item_id,
         inventory_master_org_id
FROM     AHL_ROUTE_EFFECTIVITIES
WHERE    ROUTE_ID = c_object_id
GROUP BY
         mc_id,
         mc_header_id,
         inventory_item_id,
         inventory_master_org_id
HAVING   count(*) > 1;

Line: 2027


    SELECT DISTINCT ORGANIZATION_CODE
    FROM
    mtl_parameters MP
    WHERE
    MP.MASTER_ORGANIZATION_ID = c_inv_mast_org_id AND
    MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID;

Line: 2036


    SELECT mtl.concatenated_segments
    FROM MTL_SYSTEM_ITEMS_KFV MTL
    WHERE
    mtl.ORGANIZATION_ID(+) = c_inventory_item_id AND
    mtl.inventory_item_id(+) = c_inventory_master_org_id;

Line: 2044


    SELECT
        mc.name,
        DECODE(c_mc_header_id, NULL, NULL, mc.revision)
    FROM ahl_mc_headers_b mc
    WHERE
    NVL(c_mc_header_id, c_mc_id)=mc.mc_header_id(+) AND
    mc.CONFIG_STATUS_CODE(+)='COMPLETE';

Line: 2128


select revision_status_code
from ahl_routes_app_v
where route_id = p_route_id;

Line: 2136


select revision_status_code
from ahl_operations_b
where operation_id = p_operation_id;

Line: 2142


SELECT  RM.ROUTE_ID
FROM  ahl_route_effectivities refct, AHL_ROUTES_APP_V RM
WHERE refct.ROUTE_EFFECTIVITY_ID = C_ROUTE_EFFECTIVITY_ID
  AND RM.ROUTE_ID = refct.ROUTE_ID;

Line: 2360


    UPDATE ahl_operations_b
    SET revision_status_code = 'DRAFT'
    WHERE operation_id = p_object_id;

Line: 2399


    UPDATE ahl_routes_b
    SET revision_status_code = 'DRAFT'
    WHERE route_id = l_dummy_number;

Line: 2427


    UPDATE ahl_routes_b
    SET revision_status_code = 'DRAFT'
    WHERE route_id = p_object_id;

Line: 2449


      AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  before insert p_x_material_req_tbl(i).item_group_id ' || p_x_material_req_tbl(i).item_group_id );

Line: 2450


      AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  before insert p_x_material_req_tbl(i).inventory_item_id ' || p_x_material_req_tbl(i).inventory_item_id );

Line: 2451


      AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  before insert p_x_material_req_tbl(i).inventory_org_id ' || p_x_material_req_tbl(i).inventory_org_id );

Line: 2452


      AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  before insert p_x_material_req_tbl(i).uom_code ' || p_x_material_req_tbl(i).uom_code);

Line: 2460


        INSERT INTO AHL_RT_OPER_MATERIALS
        (
          RT_OPER_MATERIAL_ID,
          OBJECT_VERSION_NUMBER,
          OBJECT_ID,
          ASSOCIATION_TYPE_CODE,
          POSITION_PATH_ID,
          ITEM_GROUP_ID,
          INVENTORY_ITEM_ID,
          INVENTORY_ORG_ID,
          UOM_CODE,
          QUANTITY,
          ITEM_COMP_DETAIL_ID,
          EXCLUDE_FLAG,
          REWORK_PERCENT,
          REPLACE_PERCENT,
          ATTRIBUTE_CATEGORY,
          ATTRIBUTE1,
          ATTRIBUTE2,
          ATTRIBUTE3,
          ATTRIBUTE4,
          ATTRIBUTE5,
          ATTRIBUTE6,
          ATTRIBUTE7,
          ATTRIBUTE8,
          ATTRIBUTE9,
          ATTRIBUTE10,
          ATTRIBUTE11,
          ATTRIBUTE12,
          ATTRIBUTE13,
          ATTRIBUTE14,
          ATTRIBUTE15,
          LAST_UPDATE_DATE,
          LAST_UPDATED_BY,
          CREATION_DATE,
          CREATED_BY,
          LAST_UPDATE_LOGIN,
          IN_SERVICE --pdoki added for OGMA 105 issue
        ) VALUES
        (
          AHL_RT_OPER_MATERIALS_S.NEXTVAL,
          p_x_material_req_tbl(i).object_version_number,
          p_object_id,
          p_association_type,
          p_x_material_req_tbl(i).position_path_id ,
          p_x_material_req_tbl(i).item_group_id,
          p_x_material_req_tbl(i).inventory_item_id,
          p_x_material_req_tbl(i).inventory_org_id,
          p_x_material_req_tbl(i).uom_code,
          p_x_material_req_tbl(i).quantity,
          p_x_material_req_tbl(i).item_comp_detail_id,
          p_x_material_req_tbl(i).exclude_flag,
          p_x_material_req_tbl(i).rework_percent,
          p_x_material_req_tbl(i).replace_percent,
          p_x_material_req_tbl(i).attribute_category,
          p_x_material_req_tbl(i).attribute1,
          p_x_material_req_tbl(i).attribute2,
          p_x_material_req_tbl(i).attribute3,
          p_x_material_req_tbl(i).attribute4,
          p_x_material_req_tbl(i).attribute5,
          p_x_material_req_tbl(i).attribute6,
          p_x_material_req_tbl(i).attribute7,
          p_x_material_req_tbl(i).attribute8,
          p_x_material_req_tbl(i).attribute9,
          p_x_material_req_tbl(i).attribute10,
          p_x_material_req_tbl(i).attribute11,
          p_x_material_req_tbl(i).attribute12,
          p_x_material_req_tbl(i).attribute13,
          p_x_material_req_tbl(i).attribute14,
          p_x_material_req_tbl(i).attribute15,
          SYSDATE,
          FND_GLOBAL.user_id,
          SYSDATE,
          FND_GLOBAL.user_id,
          FND_GLOBAL.login_id,
          p_x_material_req_tbl(i).in_service --pdoki added for OGMA 105 issue
        ) RETURNING rt_oper_material_id INTO l_rt_oper_material_id;

Line: 2539


          AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  after insert p_object_id ' || p_object_id );

Line: 2540


          AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  after insert p_association_type ' || p_association_type );

Line: 2541


          AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  after insert p_x_material_req_tbl(i).position_path_id ' || p_x_material_req_tbl(i).position_path_id );

Line: 2542


          AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  after insert p_x_material_req_tbl(i).item_group_id ' || p_x_material_req_tbl(i).item_group_id );

Line: 2543


          AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  after insert p_x_material_req_tbl(i).inventory_item_id ' || p_x_material_req_tbl(i).inventory_item_id );

Line: 2544


          AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  after insert p_x_material_req_tbl(i).inventory_org_id ' || p_x_material_req_tbl(i).inventory_org_id );

Line: 2545


          AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  after insert p_x_material_req_tbl(i).uom_code ' || p_x_material_req_tbl(i).uom_code);

Line: 2546


          AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  after insert p_x_material_req_tbl(i).quantity ' || p_x_material_req_tbl(i).quantity);

Line: 2547


          AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  after insert p_x_material_req_tbl(i).item_comp_detail_id ' || p_x_material_req_tbl(i).item_comp_detail_id);

Line: 2548


          AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  after insert p_x_material_req_tbl(i).exclude_flag ' || p_x_material_req_tbl(i).exclude_flag );

Line: 2549


          AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  after insert p_x_material_req_tbl(i).rework_percent ' || p_x_material_req_tbl(i).rework_percent );

Line: 2550


          AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  after insert p_x_material_req_tbl(i).replace_percent ' || p_x_material_req_tbl(i).replace_percent );

Line: 2551


          AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  after insert l_rt_oper_material_id' || l_rt_oper_material_id);

Line: 2570


        'AHL_RT_OPER_MATERIALS insert error = ['||SQLERRM||']'
      );

Line: 2580


        UPDATE AHL_RT_OPER_MATERIALS SET
          object_version_number   = object_version_number + 1,
          item_comp_detail_id     = p_x_material_req_tbl(i).item_comp_detail_id ,
          position_path_id        = p_x_material_req_tbl(i).position_path_id ,
          item_group_id           = p_x_material_req_tbl(i).item_group_id,
          inventory_item_id       = p_x_material_req_tbl(i).inventory_item_id,
          inventory_org_id        = p_x_material_req_tbl(i).inventory_org_id,
          uom_code                = p_x_material_req_tbl(i).uom_code,
          quantity                = p_x_material_req_tbl(i).quantity,
          exclude_flag            = p_x_material_req_tbl(i).exclude_flag,
          in_service              = p_x_material_req_tbl(i).in_service, --pdoki added for OGMA 105 issue
          rework_percent          = p_x_material_req_tbl(i).rework_percent,
          replace_percent         = p_x_material_req_tbl(i).replace_percent,
          attribute_category      = p_x_material_req_tbl(i).attribute_category,
          attribute1              = p_x_material_req_tbl(i).attribute1,
          attribute2              = p_x_material_req_tbl(i).attribute2,
          attribute3              = p_x_material_req_tbl(i).attribute3,
          attribute4              = p_x_material_req_tbl(i).attribute4,
          attribute5              = p_x_material_req_tbl(i).attribute5,
          attribute6              = p_x_material_req_tbl(i).attribute6,
          attribute7              = p_x_material_req_tbl(i).attribute7,
          attribute8              = p_x_material_req_tbl(i).attribute8,
          attribute9              = p_x_material_req_tbl(i).attribute9,
          attribute10             = p_x_material_req_tbl(i).attribute10,
          attribute11             = p_x_material_req_tbl(i).attribute11,
          attribute12             = p_x_material_req_tbl(i).attribute12,
          attribute13             = p_x_material_req_tbl(i).attribute13,
          attribute14             = p_x_material_req_tbl(i).attribute14,
          attribute15             = p_x_material_req_tbl(i).attribute15,
          last_update_date        = SYSDATE,
          last_updated_by         = FND_GLOBAL.user_id,
          last_update_login       = FND_GLOBAL.login_id
        WHERE rt_oper_material_id = p_x_material_req_tbl(i).rt_oper_material_id
        AND object_version_number = p_x_material_req_tbl(i).object_version_number;

Line: 2640


        'AHL_RT_OPER_MATERIALS update error = ['||SQLERRM||']'
      );

Line: 2649


      DELETE AHL_RT_OPER_MATERIALS
      WHERE rt_oper_material_id = p_x_material_req_tbl(i).rt_oper_material_id
      AND object_version_number = p_x_material_req_tbl(i).object_version_number;

Line: 2788


select revision_status_code
from ahl_routes_app_v
where route_id = p_route_id;

Line: 2984


    UPDATE ahl_routes_b
    SET revision_status_code = 'DRAFT'
    WHERE route_id = p_object_id;

Line: 2997


      AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  before insert p_x_route_efct_tbl(i).INVENTORY_MASTER_ORG_ID ' || p_x_route_efct_tbl(i).INVENTORY_MASTER_ORG_ID );

Line: 2998


      AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  before insert p_x_route_efct_tbl(i).MC_ID ' || p_x_route_efct_tbl(i).MC_ID );

Line: 2999


      AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  before insert p_x_route_efct_tbl(i).MC_HEADER_ID ' || p_x_route_efct_tbl(i).MC_HEADER_ID );

Line: 3000


      AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  before insert p_x_route_efct_tbl(i).object_version_number ' || p_x_route_efct_tbl(i).object_version_number);

Line: 3005


        INSERT INTO ahl_route_effectivities
        (
          route_effectivity_id
          , route_id
          , inventory_item_id
          , inventory_master_org_id
          , mc_id
          , mc_header_id ,
          OBJECT_VERSION_NUMBER,
          LAST_UPDATE_DATE,
          LAST_UPDATED_BY,
          CREATION_DATE,
          CREATED_BY,
          LAST_UPDATE_LOGIN,
          security_group_id,
          ATTRIBUTE_CATEGORY,
          ATTRIBUTE1,
          ATTRIBUTE2,
          ATTRIBUTE3,
          ATTRIBUTE4,
          ATTRIBUTE5,
          ATTRIBUTE6,
          ATTRIBUTE7,
          ATTRIBUTE8,
          ATTRIBUTE9,
          ATTRIBUTE10,
          ATTRIBUTE11,
          ATTRIBUTE12,
          ATTRIBUTE13,
          ATTRIBUTE14,
          ATTRIBUTE15
        ) VALUES
        (
          ahl_route_effectivities_s.nextval,
          p_object_id,
          p_x_route_efct_tbl(i).INVENTORY_ITEM_ID,
          p_x_route_efct_tbl(i).INVENTORY_MASTER_ORG_ID,
          p_x_route_efct_tbl(i).MC_ID,
          p_x_route_efct_tbl(i).MC_HEADER_ID,
          p_x_route_efct_tbl(i).object_version_number,
          SYSDATE,
          FND_GLOBAL.USER_ID,
          SYSDATE,
          FND_GLOBAL.USER_ID,
          FND_GLOBAL.LOGIN_ID,
          NULL,
          p_x_route_efct_tbl(i).attribute_category,
          p_x_route_efct_tbl(i).attribute1,
          p_x_route_efct_tbl(i).attribute2,
          p_x_route_efct_tbl(i).attribute3,
          p_x_route_efct_tbl(i).attribute4,
          p_x_route_efct_tbl(i).attribute5,
          p_x_route_efct_tbl(i).attribute6,
          p_x_route_efct_tbl(i).attribute7,
          p_x_route_efct_tbl(i).attribute8,
          p_x_route_efct_tbl(i).attribute9,
          p_x_route_efct_tbl(i).attribute10,
          p_x_route_efct_tbl(i).attribute11,
          p_x_route_efct_tbl(i).attribute12,
          p_x_route_efct_tbl(i).attribute13,
          p_x_route_efct_tbl(i).attribute14,
          p_x_route_efct_tbl(i).attribute15
        ) RETURNING route_effectivity_id INTO l_route_effectivitiy_id ;

Line: 3071


      AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  after insert p_x_route_efct_tbl(i).INVENTORY_MASTER_ORG_ID ' || p_x_route_efct_tbl(i).INVENTORY_MASTER_ORG_ID );

Line: 3072


      AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  after insert p_x_route_efct_tbl(i).MC_ID ' || p_x_route_efct_tbl(i).MC_ID );

Line: 3073


      AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  after insert p_x_route_efct_tbl(i).MC_HEADER_ID ' || p_x_route_efct_tbl(i).MC_HEADER_ID );

Line: 3074


      AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' :  after insert p_x_route_efct_tbl(i).object_version_number ' || p_x_route_efct_tbl(i).object_version_number);

Line: 3083


        UPDATE ahl_route_effectivities
        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,
          security_group_id       = p_x_route_efct_tbl(i).security_group_id,
          attribute_category      = p_x_route_efct_tbl(i).attribute_category,
          attribute1              = p_x_route_efct_tbl(i).attribute1,
          attribute2              = p_x_route_efct_tbl(i).attribute2,
          attribute3              = p_x_route_efct_tbl(i).attribute3,
          attribute4              = p_x_route_efct_tbl(i).attribute4,
          attribute5              = p_x_route_efct_tbl(i).attribute5,
          attribute6              = p_x_route_efct_tbl(i).attribute6,
          attribute7              = p_x_route_efct_tbl(i).attribute7,
          attribute8              = p_x_route_efct_tbl(i).attribute8,
          attribute9              = p_x_route_efct_tbl(i).attribute9,
          attribute10             = p_x_route_efct_tbl(i).attribute10,
          attribute11             = p_x_route_efct_tbl(i).attribute11,
          attribute12             = p_x_route_efct_tbl(i).attribute12,
          attribute13             = p_x_route_efct_tbl(i).attribute13,
          attribute14             = p_x_route_efct_tbl(i).attribute14,
          attribute15             = p_x_route_efct_tbl(i).attribute15
        WHERE route_effectivity_id = p_x_route_efct_tbl(i).route_effectivity_id
        AND object_version_number = p_x_route_efct_tbl(i).object_version_number;

Line: 3122


      DELETE FROM AHL_RT_OPER_MATERIALS
      WHERE OBJECT_ID = p_x_route_efct_tbl(i).route_effectivity_id
      AND ASSOCIATION_TYPE_CODE = 'DISPOSITION';

Line: 3133


      DELETE FROM ahl_route_effectivities
      WHERE route_effectivity_id = p_x_route_efct_tbl(i).route_effectivity_id
      AND object_version_number = p_x_route_efct_tbl(i).object_version_number;