APPS.WSH_CREATE_CARRIERS_PKG SQL Statements

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

Line: 29

     insert_failed              exception;

Line: 53

     SELECT rowid
     FROM   wsh_carriers
     WHERE  carrier_id = p_carrier_id;

Line: 233

     l_call_procedure := 'Inserting into WSH_CARRIERS table';

Line: 235

         INSERT INTO WSH_CARRIERS
         (
            CARRIER_ID,
            --Bug2313801      NAME,
            FREIGHT_CODE,
            SCAC_CODE,
            MANIFESTING_ENABLED_FLAG,
            CURRENCY_CODE,
            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,
            -- Pack J
            MAX_NUM_STOPS_PERMITTED,
            MAX_TOTAL_DISTANCE,
            MAX_TOTAL_TIME,
            ALLOW_INTERSPERSE_LOAD,
            MAX_LAYOVER_TIME,
            MIN_LAYOVER_TIME,
            MAX_TOTAL_DISTANCE_IN_24HR,
            MAX_DRIVING_TIME_IN_24HR,
            MAX_DUTY_TIME_IN_24HR,
            MAX_CM_DISTANCE,
            MAX_CM_TIME,
            MAX_CM_DH_DISTANCE,
            MAX_SIZE_WIDTH,
            MAX_SIZE_HEIGHT,
            MAX_SIZE_LENGTH,
            MIN_SIZE_WIDTH,
            MIN_SIZE_HEIGHT,
            MIN_SIZE_LENGTH,
            TIME_UOM,
            DIMENSION_UOM,
            DISTANCE_UOM,
            MAX_OUT_OF_ROUTE,
            CM_FREE_DH_MILEAGE,
            MIN_CM_DISTANCE,
            CM_FIRST_LOAD_DISCOUNT,
            MIN_CM_TIME,
            UNIT_RATE_BASIS,
            WEIGHT_UOM,
            VOLUME_UOM,
            GENERIC_FLAG,
            FREIGHT_BILL_AUTO_APPROVAL,
            FREIGHT_AUDIT_LINE_LEVEL,
            SUPPLIER_ID,
            SUPPLIER_SITE_ID,
            CM_RATE_VARIANT,
            DISTANCE_CALCULATION_METHOD,
            ALLOW_CONTINUOUS_MOVE,
            MAX_CM_DH_TIME,
            ORIGIN_DSTN_SURCHARGE_LEVEL)
         VALUES (
            l_carrier_party_id,
            --Bug2313801  p_carrier_name,
            P_Carrier_info.FREIGHT_CODE,
            P_Carrier_info.SCAC_CODE,
            P_Carrier_info.MANIFESTING_ENABLED,
            P_Carrier_info.CURRENCY_CODE,
            P_Carrier_info.Attribute_Category,
            P_Carrier_info.Attribute1,
            P_Carrier_info.Attribute2,
            P_Carrier_info.Attribute3,
            P_Carrier_info.Attribute4,
            P_Carrier_info.Attribute5,
            P_Carrier_info.Attribute6,
            P_Carrier_info.Attribute7,
            P_Carrier_info.Attribute8,
            P_Carrier_info.Attribute9,
            P_Carrier_info.Attribute10,
            P_Carrier_info.Attribute11,
            P_Carrier_info.Attribute12,
            P_Carrier_info.Attribute13,
            P_Carrier_info.Attribute14,
            P_Carrier_info.Attribute15,
            sysdate,
            FND_GLOBAL.USER_ID,
            sysdate,
            FND_GLOBAL.USER_ID,
            FND_GLOBAL.LOGIN_ID,
            -- Pack J
            P_Carrier_info.MAX_NUM_STOPS_PERMITTED,
            P_Carrier_info.MAX_TOTAL_DISTANCE,
            P_Carrier_info.MAX_TOTAL_TIME,
            P_Carrier_info.ALLOW_INTERSPERSE_LOAD,
            P_Carrier_info.MAX_LAYOVER_TIME,
            P_Carrier_info.MIN_LAYOVER_TIME,
            P_Carrier_info.MAX_TOTAL_DISTANCE_IN_24HR,
            P_Carrier_info.MAX_DRIVING_TIME_IN_24HR,
            P_Carrier_info.MAX_DUTY_TIME_IN_24HR,
            P_Carrier_info.MAX_CM_DISTANCE,
            P_Carrier_info.MAX_CM_TIME,
            P_Carrier_info.MAX_CM_DH_DISTANCE,
            P_Carrier_info.MAX_SIZE_WIDTH,
            P_Carrier_info.MAX_SIZE_HEIGHT,
            P_Carrier_info.MAX_SIZE_LENGTH,
            P_Carrier_info.MIN_SIZE_WIDTH,
            P_Carrier_info.MIN_SIZE_HEIGHT,
            P_Carrier_info.MIN_SIZE_LENGTH,
            P_Carrier_info.TIME_UOM,
            P_Carrier_info.DIMENSION_UOM,
            P_Carrier_info.DISTANCE_UOM,
            P_Carrier_info.MAX_OUT_OF_ROUTE,
            P_Carrier_info.CM_FREE_DH_MILEAGE,
            P_Carrier_info.MIN_CM_DISTANCE,
            P_Carrier_info.CM_FIRST_LOAD_DISCOUNT,
            P_Carrier_info.MIN_CM_TIME,
            P_Carrier_info.UNIT_RATE_BASIS,
            P_Carrier_info.WEIGHT_UOM,
            P_Carrier_info.VOLUME_UOM,
            P_Carrier_info.GENERIC_FLAG,
            P_Carrier_info.FREIGHT_BILL_AUTO_APPROVAL,
            P_Carrier_info.FREIGHT_AUDIT_LINE_LEVEL,
            P_Carrier_info.SUPPLIER_ID,
            P_Carrier_info.SUPPLIER_SITE_ID,
            P_Carrier_info.CM_RATE_VARIANT,
            P_Carrier_info.DISTANCE_CALCULATION_METHOD,
            P_Carrier_info.ALLOW_CONTINUOUS_MOVE,
            P_Carrier_info.MAX_CM_DH_TIME,
            P_Carrier_info.ORIGIN_DSTN_SURCHARGE_LEVEL);

Line: 379

           RAISE Insert_Failed;

Line: 432

   WHEN INSERT_FAILED THEN
        x_exception_msg := 'Insert Failed Exception';

Line: 443

          WSH_DEBUG_SV.logmsg(l_module_name,'INSERT_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);

Line: 444

          WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INSERT_FAILED');

Line: 475

PROCEDURE UPDATE_CARRIERINFO
  (
      P_Carrier_info              IN  CARecType,
      p_commit                    IN  VARCHAR2 DEFAULT FND_API.G_FALSE,
      x_return_status             OUT NOCOPY  VARCHAR2,
      x_exception_msg             OUT NOCOPY  VARCHAR2,
      x_procedure                 OUT NOCOPY  VARCHAR2,
      x_position                  OUT NOCOPY  NUMBER,
      x_sqlerr                    OUT NOCOPY  VARCHAR2,
      x_sql_code                  OUT NOCOPY  VARCHAR2 ) IS

  l_org_rec                  hz_party_v2pub.organization_rec_type;

Line: 502

    SELECT object_version_number
    FROM   hz_parties
    WHERE  party_id = l_carrier_party_id;

Line: 507

    SELECT active, party_usg_assignment_id
    FROM wsh_carriers_v
    WHERE carrier_id = l_carrier_party_id;

Line: 518

l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CARRIERINFO';

Line: 615

      l_call_procedure := 'Calling TCA API Update_Organization';

Line: 621

          WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_PUB.UPDATE_ORGANIZATION',WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 625

          HZ_PARTY_V2PUB.Update_Organization
           (
             p_init_msg_list                => FND_API.G_TRUE,
             p_organization_rec             => l_org_rec,
             p_party_object_version_number  => l_object_version_number,
             x_profile_id                   => l_profile_id,
             x_return_status                => l_return_status,
             x_msg_count                    => l_msg_count,
             x_msg_data                     => l_msg_data
            );

Line: 685

        UPDATE WSH_CARRIERS
        SET   scac_code                = P_Carrier_info.SCAC_CODE,
              currency_code            = P_Carrier_info.CURRENCY_CODE,
              manifesting_enabled_flag = P_Carrier_info.MANIFESTING_ENABLED,
              attribute_category       = P_Carrier_info.Attribute_Category,
              attribute1               = P_Carrier_info.Attribute1,
              attribute2               = P_Carrier_info.Attribute2,
              attribute3               = P_Carrier_info.Attribute3,
              attribute4               = P_Carrier_info.Attribute4,
              attribute5               = P_Carrier_info.Attribute5,
              attribute6               = P_Carrier_info.Attribute6,
              attribute7               = P_Carrier_info.Attribute7,
              attribute8               = P_Carrier_info.Attribute8,
              attribute9               = P_Carrier_info.Attribute9,
              attribute10              = P_Carrier_info.Attribute10,
              attribute11              = P_Carrier_info.Attribute11,
              attribute12              = P_Carrier_info.Attribute12,
              attribute13              = P_Carrier_info.Attribute13,
              attribute14              = P_Carrier_info.Attribute14,
              attribute15              = P_Carrier_info.Attribute15,
              last_update_date         = sysdate,
              last_updated_by          = FND_GLOBAL.USER_ID,
              last_update_login        = FND_GLOBAL.login_id,
              -- Pack J
              MAX_NUM_STOPS_PERMITTED        = P_Carrier_info.MAX_NUM_STOPS_PERMITTED,
              MAX_TOTAL_DISTANCE             = P_Carrier_info.MAX_TOTAL_DISTANCE,
              MAX_TOTAL_TIME                 = P_Carrier_info.MAX_TOTAL_TIME,
              ALLOW_INTERSPERSE_LOAD         = P_Carrier_info.ALLOW_INTERSPERSE_LOAD,
              MAX_LAYOVER_TIME               = P_Carrier_info.MAX_LAYOVER_TIME,
              MIN_LAYOVER_TIME               = P_Carrier_info.MIN_LAYOVER_TIME,
              MAX_TOTAL_DISTANCE_IN_24HR     = P_Carrier_info.MAX_TOTAL_DISTANCE_IN_24HR,
              MAX_DRIVING_TIME_IN_24HR       = P_Carrier_info.MAX_DRIVING_TIME_IN_24HR,
              MAX_DUTY_TIME_IN_24HR          = P_Carrier_info.MAX_DUTY_TIME_IN_24HR,
              MAX_CM_DISTANCE                = P_Carrier_info.MAX_CM_DISTANCE,
              MAX_CM_TIME                    = P_Carrier_info.MAX_CM_TIME,
              MAX_CM_DH_DISTANCE             = P_Carrier_info.MAX_CM_DH_DISTANCE,
              MAX_SIZE_WIDTH                 = P_Carrier_info.MAX_SIZE_WIDTH,
              MAX_SIZE_HEIGHT                = P_Carrier_info.MAX_SIZE_HEIGHT,
              MAX_SIZE_LENGTH                = P_Carrier_info.MAX_SIZE_LENGTH,
              MIN_SIZE_WIDTH                 = P_Carrier_info.MIN_SIZE_WIDTH,
              MIN_SIZE_HEIGHT                = P_Carrier_info.MIN_SIZE_HEIGHT,
              MIN_SIZE_LENGTH                = P_Carrier_info.MIN_SIZE_LENGTH,
              TIME_UOM                       = P_Carrier_info.TIME_UOM,
              DIMENSION_UOM                  = P_Carrier_info.DIMENSION_UOM,
              DISTANCE_UOM                   = P_Carrier_info.DISTANCE_UOM,
              MAX_OUT_OF_ROUTE               = P_Carrier_info.MAX_OUT_OF_ROUTE,
              CM_FREE_DH_MILEAGE             = P_Carrier_info.CM_FREE_DH_MILEAGE,
              MIN_CM_DISTANCE                = P_Carrier_info.MIN_CM_DISTANCE,
              CM_FIRST_LOAD_DISCOUNT         = P_Carrier_info.CM_FIRST_LOAD_DISCOUNT,
              MIN_CM_TIME                    = P_Carrier_info.MIN_CM_TIME,
              UNIT_RATE_BASIS                = P_Carrier_info.UNIT_RATE_BASIS,
              WEIGHT_UOM                     = P_Carrier_info.WEIGHT_UOM,
              VOLUME_UOM                     = P_Carrier_info.VOLUME_UOM,
              GENERIC_FLAG                   = P_Carrier_info.GENERIC_FLAG,
              FREIGHT_BILL_AUTO_APPROVAL     = P_Carrier_info.FREIGHT_BILL_AUTO_APPROVAL,
              FREIGHT_AUDIT_LINE_LEVEL       = P_Carrier_info.FREIGHT_AUDIT_LINE_LEVEL,
              SUPPLIER_ID                    = P_Carrier_info.SUPPLIER_ID,
              SUPPLIER_SITE_ID               = P_Carrier_info.SUPPLIER_SITE_ID,
              CM_RATE_VARIANT                = P_Carrier_info.CM_RATE_VARIANT,
              DISTANCE_CALCULATION_METHOD    = P_Carrier_info.DISTANCE_CALCULATION_METHOD,
              ALLOW_CONTINUOUS_MOVE          = P_Carrier_info.ALLOW_CONTINUOUS_MOVE,
              MAX_CM_DH_TIME                 = P_Carrier_info.MAX_CM_DH_TIME,
              ORIGIN_DSTN_SURCHARGE_LEVEL    = P_Carrier_info.ORIGIN_DSTN_SURCHARGE_LEVEL
        WHERE carrier_id = P_Carrier_info.CARRIER_ID;

Line: 757

       UPDATE  /*+ index(ORG_FREIGHT_TL ORG_FREIGHT_TL_U1) */ ORG_FREIGHT_TL
       SET    description  =  SUBSTR(P_Carrier_info.CARRIER_NAME,1,80),
              attribute_category       = P_Carrier_info.Attribute_Category,
              attribute1               = P_Carrier_info.Attribute1,
              attribute2               = P_Carrier_info.Attribute2,
              attribute3               = P_Carrier_info.Attribute3,
              attribute4               = P_Carrier_info.Attribute4,
              attribute5               = P_Carrier_info.Attribute5,
              attribute6               = P_Carrier_info.Attribute6,
              attribute7               = P_Carrier_info.Attribute7,
              attribute8               = P_Carrier_info.Attribute8,
              attribute9               = P_Carrier_info.Attribute9,
              attribute10              = P_Carrier_info.Attribute10,
              attribute11              = P_Carrier_info.Attribute11,
              attribute12              = P_Carrier_info.Attribute12,
              attribute13              = P_Carrier_info.Attribute13,
              attribute14              = P_Carrier_info.Attribute14,
              attribute15              = P_Carrier_info.Attribute15,
              last_update_date         = sysdate,
              last_updated_by          = FND_GLOBAL.USER_ID,
              last_update_login        = FND_GLOBAL.login_id
       WHERE  freight_code = P_Carrier_info.FREIGHT_CODE;

Line: 846

END UPDATE_CARRIERINFO;

Line: 856

SELECT *
FROM   wsh_carriers
WHERE  rowid = p_rowid
FOR UPDATE of Carrier_id NOWAIT;

Line: 864

SELECT carrier_name,active
FROM wsh_carriers_v
WHERE  carrier_id =P_Carrier_info.CARRIER_id;

Line: 960

      FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');

Line: 1286

PROCEDURE Update_Code_Assgn(
       p_class_code          IN    VARCHAR2,
       p_enabled             IN    VARCHAR2,
       p_code_assignment_id  IN    NUMBER,
       x_return_status       OUT NOCOPY    VARCHAR2,
       x_position            OUT NOCOPY    NUMBER,
       x_procedure           OUT NOCOPY    VARCHAR2,
       x_exception_msg       OUT NOCOPY    VARCHAR2,
       x_sql_code            OUT NOCOPY    NUMBER,
       x_sqlerr              OUT NOCOPY    VARCHAR2 ) IS

   --  General Declarations

     l_return_status            varchar2(100);

Line: 1313

  SELECT object_version_number
  FROM   hz_code_assignments
  WHERE  code_assignment_id = p_code_assignment_id;

Line: 1329

     HZ_CLASSIFICATION_V2PUB.Update_Code_Assignment(
         p_init_msg_list             => FND_API.G_TRUE,
         p_code_assignment_rec       => l_code_assignment_rec_type,
         p_object_version_number     => l_object_version_number,
         x_return_status             => l_return_status,
         x_msg_count                 => l_msg_count,
         x_msg_data                  => l_msg_data );

Line: 1369

END Update_Code_Assgn;

Line: 1378

  SELECT
    WCS.CARRIER_ID,
    WCS.CARRIER_SITE_ID,
    WCS.EMAIL_ADDRESS,
    WCS.AUTO_ACCEPT_LOAD_TENDER,
    WCS.TENDER_WAIT_TIME,
    WCS.WAIT_TIME_UOM,
    WCS.WEIGHT_THRESHOLD_UPPER,
    WCS.WEIGHT_THRESHOLD_LOWER,
    WCS.VOLUME_THRESHOLD_UPPER,
    WCS.VOLUME_THRESHOLD_LOWER,
    WCS.ENABLE_AUTO_TENDER, -- R12 Code changes
    WCS.TENDER_TRANSMISSION_METHOD
  FROM
    WSH_CARRIER_SITES WCS,
    WSH_ORG_CARRIER_SITES WOCS
  WHERE
    WCS.CARRIER_ID = p_carrier_id AND
    WOCS.ORGANIZATION_ID = p_organization_id AND
    WOCS.CARRIER_SITE_ID = WCS.CARRIER_SITE_ID AND
    WOCS.ENABLED_FLAG = 'Y';

Line: 1486

  SELECT carrier_service_id
  FROM wsh_carrier_services
  WHERE carrier_id = p_carrier_id;

Line: 1491

  SELECT party_site_id
  FROM hz_party_sites
  WHERE party_id=p_carrier_id;

Line: 1497

  select object_version_number
  from   hz_party_sites
  where  party_site_id = p_party_site_id;

Line: 1504

  SELECT LOOKUP_CODE,
         TAG,
         ATTRIBUTE_CATEGORY,
         ATTRIBUTE1,
         ATTRIBUTE2,
         ATTRIBUTE3,
         ATTRIBUTE4,
         START_DATE_ACTIVE,
         END_DATE_ACTIVE,
         TERRITORY_CODE,
         ATTRIBUTE5,
         ATTRIBUTE6,
         ATTRIBUTE7,
         ATTRIBUTE8,
         ATTRIBUTE9,
         ATTRIBUTE10,
         ATTRIBUTE11,
         ATTRIBUTE12,
         ATTRIBUTE13,
         ATTRIBUTE14,
         ATTRIBUTE15,
         MEANING,
         DESCRIPTION
  FROM   fnd_lookup_values
  where lookup_type = 'SHIP_METHOD'
  and security_group_id = 0
  and view_application_id = 3
  and lookup_code = p_ship_method_code;

Line: 1536

  SELECT freight_code
  FROM wsh_carriers
  WHERE carrier_id = p_carrier_id;

Line: 1541

  SELECT ship_method_code
  FROM wsh_carrier_services
  WHERE carrier_service_id = p_carrier_service_id;

Line: 1631

          FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');

Line: 1643

        FND_LOOKUP_VALUES_PKG.UPDATE_ROW
        (
          X_LOOKUP_TYPE         => 'SHIP_METHOD',
          X_SECURITY_GROUP_ID   => 0,
          X_VIEW_APPLICATION_ID => 3,
          X_LOOKUP_CODE         => lookupinfo.LOOKUP_CODE,
          X_TAG                 => lookupinfo.TAG,
          X_ATTRIBUTE_CATEGORY  => lookupinfo.ATTRIBUTE_CATEGORY,
          X_ATTRIBUTE1          => lookupinfo.ATTRIBUTE1,
          X_ATTRIBUTE2          => lookupinfo.ATTRIBUTE2,
          X_ATTRIBUTE3          => lookupinfo.ATTRIBUTE3,
          X_ATTRIBUTE4          => lookupinfo.ATTRIBUTE4,
          X_ENABLED_FLAG        => 'N',
          X_START_DATE_ACTIVE   => lookupinfo.START_DATE_ACTIVE,
          X_END_DATE_ACTIVE     => lookupinfo.END_DATE_ACTIVE,
          X_TERRITORY_CODE      => lookupinfo.TERRITORY_CODE,
          X_ATTRIBUTE5          => lookupinfo.ATTRIBUTE5,
          X_ATTRIBUTE6          => lookupinfo.ATTRIBUTE6,
          X_ATTRIBUTE7          => lookupinfo.ATTRIBUTE7,
          X_ATTRIBUTE8          => lookupinfo.ATTRIBUTE8,
          X_ATTRIBUTE9          => lookupinfo.ATTRIBUTE9,
          X_ATTRIBUTE10         => lookupinfo.ATTRIBUTE10,
          X_ATTRIBUTE11         => lookupinfo.ATTRIBUTE11,
          X_ATTRIBUTE12         => lookupinfo.ATTRIBUTE12,
          X_ATTRIBUTE13         => lookupinfo.ATTRIBUTE13,
          X_ATTRIBUTE14         => lookupinfo.ATTRIBUTE14,
          X_ATTRIBUTE15         => lookupinfo.ATTRIBUTE15,
          X_MEANING             => lookupinfo.MEANING,
          X_DESCRIPTION         => lookupinfo.DESCRIPTION,
          X_LAST_UPDATE_DATE    => sysdate,
          X_LAST_UPDATED_BY     => FND_GLOBAL.USER_ID,
          X_LAST_UPDATE_LOGIN   => FND_GLOBAL.LOGIN_ID
       );

Line: 1682

  UPDATE WSH_CARRIER_SERVICES
  SET ENABLED_FLAG        = 'N'
  WHERE carrier_id= p_carrier_id;

Line: 1710

    UPDATE WSH_ORG_CARRIER_SITES
    SET ENABLED_FLAG = 'N'
    WHERE  CARRIER_SITE_ID = l_carrier_site_id;

Line: 1718

    HZ_PARTY_SITE_V2PUB.Update_Party_Site
    (
      p_init_msg_list         => FND_API.G_TRUE,
      p_party_site_rec        => l_site_rec,
      p_object_version_number => l_site_object_number,
      x_return_status         => l_return_status,
      x_msg_count             => l_msg_count,
      x_msg_data              => l_msg_data
    );

Line: 1737

  UPDATE WSH_CARRIER_VEHICLE_TYPES
  SET    ASSIGNED_FLAG = 'N'
  WHERE  CARRIER_ID = p_carrier_id;

Line: 1741

  UPDATE ORG_FREIGHT_TL
  SET DISABLE_DATE = SYSDATE
  WHERE party_id=p_carrier_id and DISABLE_DATE is NULL;