APPS.WSH_FREIGHT_COSTS_GRP SQL Statements

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

Line: 44

			SELECT freight_cost_type_id INTO x_freight_cost_type_id
			FROM wsh_freight_cost_types
			WHERE name = p_freight_cost_type;

Line: 52

		SELECT freight_cost_type_id INTO l_type_id
		FROM wsh_freight_cost_types
		WHERE freight_cost_type_id = x_freight_cost_type_id;

Line: 96

PROCEDURE Delete_Freight_Costs (
  p_api_version_number     IN     NUMBER
, p_init_msg_list          IN     VARCHAR2
, p_commit                 IN     VARCHAR2
, x_return_status             OUT NOCOPY  VARCHAR2
, x_msg_count                 OUT NOCOPY  NUMBER
, x_msg_data                  OUT NOCOPY  VARCHAR2
, p_pub_freight_costs		IN     WSH_FREIGHT_COSTS_GRP.PubFreightCostRecType
)
IS
l_return_status            VARCHAR2(30);

Line: 110

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

Line: 132

	WSH_FREIGHT_COSTS_PVT.Delete_freight_cost(
		p_rowid       			=>   	NULL,
		p_freight_cost_id   	=>   	p_pub_freight_costs.freight_cost_id,
		x_return_status     	=>   	x_return_status);

Line: 163

END Delete_Freight_Costs;

Line: 229

  x_pvt_freight_rec.LAST_UPDATE_DATE		 := p_grp_freight_rec.LAST_UPDATE_DATE;

Line: 230

  x_pvt_freight_rec.LAST_UPDATED_BY		 := p_grp_freight_rec.LAST_UPDATED_BY;

Line: 231

  x_pvt_freight_rec.LAST_UPDATE_LOGIN		 := p_grp_freight_rec.LAST_UPDATE_LOGIN;

Line: 234

  x_pvt_freight_rec.PROGRAM_UPDATE_DATE		 := p_grp_freight_rec.PROGRAM_UPDATE_DATE;

Line: 270

, p_action               IN  VARCHAR2 DEFAULT 'UPDATE'
, p_caller               IN  VARCHAR2
, x_freight_rec          OUT NOCOPY WSH_FREIGHT_COSTS_PVT.Freight_Cost_Rec_Type
, x_return_status        OUT NOCOPY VARCHAR2
) IS

l_debug_on BOOLEAN;

Line: 286

  SELECT  FREIGHT_COST_ID
	, FREIGHT_COST_TYPE_ID
	, UNIT_AMOUNT
	, CALCULATION_METHOD
	, UOM
	, QUANTITY
	, TOTAL_AMOUNT
	, CURRENCY_CODE
	, CONVERSION_DATE
	, CONVERSION_RATE
	, CONVERSION_TYPE_CODE
	, TRIP_ID
	, STOP_ID
	, DELIVERY_ID
	, DELIVERY_LEG_ID
	, DELIVERY_DETAIL_ID
	, ATTRIBUTE_CATEGORY
	, ATTRIBUTE1
	, ATTRIBUTE2
	, ATTRIBUTE3
	, ATTRIBUTE4
	, ATTRIBUTE5
	, ATTRIBUTE6
	, ATTRIBUTE7
	, ATTRIBUTE8
	, ATTRIBUTE9
	, ATTRIBUTE10
	, ATTRIBUTE11
	, ATTRIBUTE12
	, ATTRIBUTE13
	, ATTRIBUTE14
	, ATTRIBUTE15
	, CREATION_DATE
	, CREATED_BY
	, LAST_UPDATE_DATE
	, LAST_UPDATED_BY
	, LAST_UPDATE_LOGIN
	, PROGRAM_APPLICATION_ID
	, PROGRAM_ID
	, PROGRAM_UPDATE_DATE
	, REQUEST_ID
	, PRICING_LIST_HEADER_ID
	, PRICING_LIST_LINE_ID
	, APPLIED_TO_CHARGE_ID
	, CHARGE_UNIT_VALUE
	, CHARGE_SOURCE_CODE
	, LINE_TYPE_CODE
	, ESTIMATED_FLAG
	, FREIGHT_CODE
	, NULL TRIP_NAME
	, NULL DELIVERY_NAME
	, NULL FREIGHT_COST_TYPE
	, NULL STOP_LOCATION_ID
	, NULL PLANNED_DEP_DATE
        , COMMODITY_CATEGORY_ID
        , BILLABLE_QUANTITY
        , BILLABLE_UOM
        , BILLABLE_BASIS
  FROM  wsh_freight_costs
  WHERE FREIGHT_COST_ID= p_freight_rec.freight_cost_id;

Line: 367

  IF  p_action = 'UPDATE' THEN
   IF p_freight_rec.freight_cost_id <> FND_API.G_MISS_NUM THEN -- Added for Bug 13590798
     OPEN c_tbl_rec;

Line: 394

           SELECT conversion_type INTO l_conversion_type
           FROM gl_daily_conversion_types
           WHERE conversion_type = p_freight_rec.conversion_type_code;

Line: 417

  x_freight_rec.LAST_UPDATE_DATE:= SYSDATE;

Line: 418

  x_freight_rec.LAST_UPDATED_BY:= FND_GLOBAL.USER_ID;

Line: 419

  x_freight_rec.LAST_UPDATE_LOGIN:= FND_GLOBAL.USER_ID;

Line: 549

     IF ( p_freight_rec.PROGRAM_UPDATE_DATE <> FND_API.G_MISS_DATE
          OR p_freight_rec.PROGRAM_UPDATE_DATE IS NULL) THEN
        x_freight_rec.PROGRAM_UPDATE_DATE:= p_freight_rec.PROGRAM_UPDATE_DATE;

Line: 676

PROCEDURE Create_Update_Freight_Costs(
p_api_version_number     IN     NUMBER,
p_init_msg_list          IN     VARCHAR2,
p_commit                 IN     VARCHAR2,
p_freight_info_tab       IN     freight_rec_tab_type,
p_in_rec                 IN     freightInRecType,
x_out_tab                OUT    NOCOPY freight_out_tab_type,
x_return_status          OUT    NOCOPY VARCHAR2,
x_msg_count              OUT    NOCOPY NUMBER,
x_msg_data               OUT    NOCOPY VARCHAR2) IS

l_api_version_number   	CONSTANT NUMBER := 1.0;

Line: 688

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

Line: 690

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

Line: 697

 SELECT currency_code, amount
 FROM 	wsh_freight_cost_types
 WHERE  freight_cost_type_id=p_freight_cost_type_id;

Line: 703

SELECT delivery_detail_id ,
       organization_id,
       released_status,
       container_flag,
       source_code,
       lpn_id,
       line_direction,
       ship_from_location_id,
       move_order_line_id, -- R12, X-dock project
       NULL,    -- OTM R12
       client_id -- LSP PROJECT :Just added for dependency
FROM   wsh_delivery_details
WHERE  delivery_detail_id = v_detail_id;

Line: 718

SELECT trip_id,
       NULL,  --       organization_id,
       status_code,
       planned_flag,
       load_tender_status, -- R12 Select Carrier dependent change
       lane_id,
       shipments_type_flag,
       NVL(ignore_for_planning, 'N')  --OTM R12,glog proj
FROM   wsh_trips
WHERE  trip_id = v_trip_id;

Line: 730

SELECT stop_id,
       NULL, --       organization_id,
       status_code,
       shipments_type_flag
FROM wsh_trip_stops
WHERE stop_id = v_stop_id;

Line: 738

SELECT delivery_id,
       organization_id,
       status_code,
       planned_flag,
       shipment_direction,
       delivery_type, -- MDC
       NVL(ignore_for_planning, 'N'),  --OTM R12, glog proj
       NVL(tms_interface_flag,WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT),   --OTM R12, glog proj
       NULL,   -- --OTM R12,
       client_id -- LSP PROJECT : Just added for dependency.
FROM   wsh_new_deliveries
WHERE  delivery_id = v_del_id;

Line: 798

 SAVEPOINT Create_Update_Freight_Costs_Gp;

Line: 833

 IF (p_in_rec.action_code IS NULL OR p_in_rec.action_code NOT IN ('CREATE','UPDATE') ) THEN
    FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');

Line: 868

    SAVEPOINT create_update_freight_loop;

Line: 1053

                   SELECT 'Y',wt.name into l_status,l_name
                     FROM wsh_trips wt, wsh_trip_stops wts, wsh_delivery_legs wdl, wsh_new_deliveries wnd,
                          wsh_delivery_assignments wda, wsh_delivery_details wdd
                    WHERE wt.trip_id = l_trip_id
                      AND wts.trip_id = wt.trip_id
                      AND wdl.pick_up_stop_id = wts.stop_id
                      AND wnd.delivery_id = wdl.delivery_id
                      AND wda.delivery_id = wnd.delivery_id
                      AND wdd.delivery_detail_id = wda.delivery_detail_id
                      AND wdd.oe_interfaced_flag = 'Y'
                      AND ROWNUM = 1;

Line: 1084

          SELECT COUNT(*) INTO l_counts
          FROM wsh_trips
            WHERE trip_id = l_trip_id
          AND ROWNUM = 1;

Line: 1130

                   SELECT 'Y',wts.stop_location_id into l_status,l_stop_loc_id
                     FROM wsh_trip_stops wts, wsh_delivery_legs wdl, wsh_new_deliveries wnd,
                          wsh_delivery_assignments wda, wsh_delivery_details wdd
                    WHERE wts.stop_id = l_stop_id
                      AND (wdl.pick_up_stop_id = wts.stop_id OR wdl.drop_off_stop_id = wts.stop_id)
                      AND wnd.delivery_id = wdl.delivery_id
                      AND wda.delivery_id = wnd.delivery_id
                      AND wdd.delivery_detail_id = wda.delivery_detail_id
                      AND wdd.oe_interfaced_flag = 'Y'
                      AND ROWNUM = 1;

Line: 1160

          SELECT COUNT(*) INTO l_counts
          FROM wsh_trip_stops
          WHERE stop_id = l_stop_id
          AND ROWNUM = 1;

Line: 1207

                   SELECT 'Y',wnd.name into l_status,l_name
                     FROM wsh_new_deliveries wnd, wsh_delivery_assignments wda, wsh_delivery_details wdd
                    WHERE wnd.delivery_id = l_delivery_id
                      AND wda.delivery_id = wnd.delivery_id
                      AND wdd.delivery_detail_id = wda.delivery_detail_id
                      AND wdd.oe_interfaced_flag = 'Y'
                      AND ROWNUM = 1;

Line: 1233

          SELECT COUNT(*) INTO l_counts
          FROM wsh_new_deliveries
          WHERE delivery_id = l_delivery_id
          AND ROWNUM = 1;

Line: 1252

          SELECT delivery_id INTO l_counts
          FROM wsh_delivery_legs
          WHERE delivery_leg_id = l_freight_info_tab(l_index).delivery_leg_id
          AND ROWNUM = 1;

Line: 1273

                   SELECT container_flag,container_name into l_con_flag,l_name
                     FROM wsh_delivery_details
                    WHERE delivery_detail_id = l_freight_info_tab(l_index).delivery_detail_id;

Line: 1279

                      SELECT 'Y' into l_status
                        FROM wsh_delivery_details
                       WHERE delivery_detail_id in (SELECT delivery_detail_id
                                                      FROM wsh_delivery_assignments
                                                     WHERE parent_delivery_detail_id = l_freight_info_tab(l_index).delivery_detail_id)
                         AND oe_interfaced_flag = 'Y'
                         AND container_flag = 'N'
                         AND ROWNUM = 1;

Line: 1289

                      SELECT oe_interfaced_flag,delivery_detail_id into l_status,l_name
                        FROM wsh_delivery_details
                       WHERE delivery_detail_id = l_freight_info_tab(l_index).delivery_detail_id;

Line: 1313

          SELECT COUNT(delivery_detail_id) INTO l_counts
          FROM wsh_delivery_details
          WHERE delivery_detail_id = l_freight_info_tab(l_index).delivery_detail_id
          AND ROWNUM = 1;

Line: 1505

    ELSIF (p_in_rec.action_code= 'UPDATE') THEN
       WSH_FREIGHT_COSTS_PVT.Update_Freight_Cost(
                p_rowid			=> NULL,
		p_freight_cost_info    	=> l_freight_info_tab(l_index),
		x_return_status  	=> l_return_status);

Line: 1512

          WSH_DEBUG_SV.log(l_module_name,'Update_Freight_Costs x_return_status',l_return_status);

Line: 1519

                                   p_msg_data         =>  'WSH_PUB_UPDATE_FAILURE',
                                   p_token1           => 'ENTITY',
                                   p_value1           => 'Freight_Cost');

Line: 1542

      ROLLBACK to create_update_freight_loop;

Line: 1561

      ROLLBACK to create_update_freight_loop;

Line: 1580

      ROLLBACK to create_update_freight_loop;

Line: 1622

     Rollback to Create_Update_Freight_Costs_Gp;

Line: 1634

     Rollback to Create_Update_Freight_Costs_Gp;

Line: 1646

     Rollback to Create_Update_Freight_Costs_Gp;

Line: 1661

     wsh_util_core.default_handler ('WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP');

Line: 1671

     Rollback to Create_Update_Freight_Costs_Gp;

Line: 1672

END Create_Update_Freight_Costs;

Line: 1691

PROCEDURE Create_Update_Freight_Costs (
  p_api_version_number     IN     NUMBER
, p_init_msg_list          IN     VARCHAR2
, p_commit                 IN     VARCHAR2
, x_return_status          OUT NOCOPY  VARCHAR2
, x_msg_count              OUT NOCOPY  NUMBER
, x_msg_data               OUT NOCOPY  VARCHAR2
, p_pub_freight_costs	   IN     WSH_FREIGHT_COSTS_GRP.PubFreightCostRecType
, p_action_code            IN     VARCHAR2
, x_freight_cost_id           OUT NOCOPY  NUMBER
)
IS

l_api_version_number   	CONSTANT NUMBER := 1.0;

Line: 1705

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

Line: 1707

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

Line: 1747

   WSH_INTERFACE_GRP.Create_Update_Freight_Costs(
      p_api_version_number     => p_api_version_number,
      p_init_msg_list          => p_init_msg_list,
      p_commit                 => p_commit,
      x_return_status          => x_return_status,
      x_msg_count              => x_msg_count,
      x_msg_data               => x_msg_data,
      p_freight_info_tab       => l_freight_info_tab,
      p_in_rec                 => l_in_rec,
      x_out_tab                => l_out_tab );

Line: 1776

     wsh_util_core.default_handler ('WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP');

Line: 1782

END Create_Update_Freight_Costs;