The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wsh_freight_costs_s.nextval
FROM sys.dual;
SELECT rowid
FROM wsh_freight_costs
WHERE freight_cost_id = x_freight_cost_id;
INSERT INTO wsh_freight_costs(
freight_cost_id,
freight_cost_type_id,
unit_amount,
/* H Integration: datamodel changes wrudge uncommented 4 columns*/
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,
/* H Integration: datamodel changes wrudge */
pricing_list_header_id,
pricing_list_line_id,
applied_to_charge_id,
charge_unit_value,
charge_source_code,
line_type_code,
estimated_flag,
commodity_category_id,
/* R12 new attributes */
billable_quantity,
billable_uom,
billable_basis
) VALUES (
x_freight_cost_id,
p_freight_cost_info.freight_cost_type_id,
p_freight_cost_info.unit_amount,
/* H Integration: datamodel changes wrudge uncommented 4 columns*/
p_freight_cost_info.calculation_method,
p_freight_cost_info.uom,
p_freight_cost_info.quantity,
p_freight_cost_info.total_amount,
p_freight_cost_info.currency_code,
p_freight_cost_info.conversion_date,
p_freight_cost_info.conversion_rate,
p_freight_cost_info.conversion_type_code,
p_freight_cost_info.trip_id,
p_freight_cost_info.stop_id,
p_freight_cost_info.delivery_id,
p_freight_cost_info.delivery_leg_id,
p_freight_cost_info.delivery_detail_id,
p_freight_cost_info.attribute_category,
p_freight_cost_info.attribute1,
p_freight_cost_info.attribute2,
p_freight_cost_info.attribute3,
p_freight_cost_info.attribute4,
p_freight_cost_info.attribute5,
p_freight_cost_info.attribute6,
p_freight_cost_info.attribute7,
p_freight_cost_info.attribute8,
p_freight_cost_info.attribute9,
p_freight_cost_info.attribute10,
p_freight_cost_info.attribute11,
p_freight_cost_info.attribute12,
p_freight_cost_info.attribute13,
p_freight_cost_info.attribute14,
p_freight_cost_info.attribute15,
p_freight_cost_info.creation_date,
p_freight_cost_info.created_by,
p_freight_cost_info.last_update_date,
p_freight_cost_info.last_updated_by,
p_freight_cost_info.last_update_login,
p_freight_cost_info.program_application_id,
p_freight_cost_info.program_id,
p_freight_cost_info.program_update_date,
p_freight_cost_info.request_id,
/* H Integration: datamodel changes wrudge */
p_freight_cost_info.pricing_list_header_id,
p_freight_cost_info.pricing_list_line_id,
p_freight_cost_info.applied_to_charge_id,
p_freight_cost_info.charge_unit_value,
p_freight_cost_info.charge_source_code,
p_freight_cost_info.line_type_code,
p_freight_cost_info.estimated_flag,
p_freight_cost_info.commodity_category_id,
/* R12 new attributes */
p_freight_cost_info.billable_quantity,
p_freight_cost_info.billable_uom,
p_freight_cost_info.billable_basis
) RETURNING rowid INTO x_rowid;
PROCEDURE Update_Freight_Cost(
p_rowid IN VARCHAR2
, p_freight_cost_info IN Freight_Cost_Rec_Type
, x_return_status OUT NOCOPY VARCHAR2
)
IS
--
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_FREIGHT_COST';
WSH_DEBUG_SV.log(l_module_name,'last_update_date ',p_freight_cost_info.last_update_date);
WSH_DEBUG_SV.log(l_module_name,'last_updated_by ',p_freight_cost_info.last_updated_by);
WSH_DEBUG_SV.log(l_module_name,'last_update_login ',p_freight_cost_info.last_update_login);
WSH_DEBUG_SV.log(l_module_name,'program_update_date ',p_freight_cost_info.program_update_date);
UPDATE wsh_freight_costs
SET
freight_cost_id = p_freight_cost_info.freight_cost_id,
freight_cost_type_id = p_freight_cost_info.freight_cost_type_id,
unit_amount = p_freight_cost_info.unit_amount,
/* H Integration: datamodel changes wrudge uncommented 4 columns*/
calculation_method = p_freight_cost_info.calculation_method,
uom = p_freight_cost_info.uom,
quantity = p_freight_cost_info.quantity,
total_amount = p_freight_cost_info.total_amount,
currency_code = p_freight_cost_info.currency_code,
conversion_date = p_freight_cost_info.conversion_date,
conversion_rate = p_freight_cost_info.conversion_rate,
conversion_type_code = p_freight_cost_info.conversion_type_code,
trip_id = p_freight_cost_info.trip_id,
stop_id = p_freight_cost_info.stop_id,
delivery_id = p_freight_cost_info.delivery_id,
delivery_leg_id = p_freight_cost_info.delivery_leg_id,
delivery_detail_id = p_freight_cost_info.delivery_detail_id,
attribute_category = p_freight_cost_info.attribute_category,
attribute1 = p_freight_cost_info.attribute1,
attribute2 = p_freight_cost_info.attribute2,
attribute3 = p_freight_cost_info.attribute3,
attribute4 = p_freight_cost_info.attribute4,
attribute5 = p_freight_cost_info.attribute5,
attribute6 = p_freight_cost_info.attribute6,
attribute7 = p_freight_cost_info.attribute7,
attribute8 = p_freight_cost_info.attribute8,
attribute9 = p_freight_cost_info.attribute9,
attribute10 = p_freight_cost_info.attribute10,
attribute11 = p_freight_cost_info.attribute11,
attribute12 = p_freight_cost_info.attribute12,
attribute13 = p_freight_cost_info.attribute13,
attribute14 = p_freight_cost_info.attribute14,
attribute15 = p_freight_cost_info.attribute15,
last_update_date = p_freight_cost_info.last_update_date,
last_updated_by = p_freight_cost_info.last_updated_by,
last_update_login = p_freight_cost_info.last_update_login,
program_application_id = p_freight_cost_info.program_application_id,
program_id = p_freight_cost_info.program_id,
program_update_date = p_freight_cost_info.program_update_date,
request_id = p_freight_cost_info.request_id,
/* H Integration: datamodel changes wrudge */
pricing_list_header_id = p_freight_cost_info.pricing_list_header_id,
pricing_list_line_id = p_freight_cost_info.pricing_list_line_id,
applied_to_charge_id = p_freight_cost_info.applied_to_charge_id,
charge_unit_value = p_freight_cost_info.charge_unit_value,
charge_source_code = p_freight_cost_info.charge_source_code,
line_type_code = p_freight_cost_info.line_type_code,
estimated_flag = p_freight_cost_info.estimated_flag,
commodity_category_id = p_freight_cost_info.commodity_category_id,
/* R12 new attributes */
billable_quantity = p_freight_cost_info.billable_quantity,
billable_uom = p_freight_cost_info.billable_uom,
billable_basis = p_freight_cost_info.billable_basis
WHERE freight_cost_id = p_freight_cost_info.freight_cost_id;
wsh_util_core.default_handler ('WSH_FREIGHT_COSTS_PVT.Update_Freight_Cost');
END Update_Freight_Cost;
SELECT
freight_cost_id,
freight_cost_type_id,
unit_amount,
/* H Integration: datamodel changes wrudge uncommented 4 columns*/
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,
/* H Integration: datamodel changes wrudge */
pricing_list_header_id,
pricing_list_line_id,
applied_to_charge_id,
charge_unit_value,
charge_source_code,
line_type_code,
estimated_flag,
commodity_category_id,
/* R12 new attributes */
billable_quantity,
billable_uom,
billable_basis
FROM wsh_freight_costs
WHERE rowid = p_rowid
FOR UPDATE OF freight_cost_id NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
WSH_DEBUG_SV.logmsg(l_module_name,'FORM_RECORD_DELETED Error has occured',WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FORM_RECORD_DELETED');
AND (Recinfo.last_update_date = p_freight_cost_info.last_update_date)
AND (Recinfo.last_update_login = p_freight_cost_info.last_update_login)
/* H Integration: datamodel changes wrudge */
AND ( (Recinfo.pricing_list_header_id = p_freight_cost_info.pricing_list_header_id)
or (Recinfo.pricing_list_header_id is NULL
and p_freight_cost_info.pricing_list_header_id is NULL))
AND ( (Recinfo.pricing_list_line_id = p_freight_cost_info.pricing_list_line_id)
or (Recinfo.pricing_list_line_id is NULL
and p_freight_cost_info.pricing_list_line_id is NULL))
AND ( (Recinfo.applied_to_charge_id = p_freight_cost_info.applied_to_charge_id)
or (Recinfo.applied_to_charge_id is NULL
and p_freight_cost_info.applied_to_charge_id is NULL))
AND ( (Recinfo.charge_unit_value = p_freight_cost_info.charge_unit_value)
or (Recinfo.charge_unit_value is NULL
and p_freight_cost_info.charge_unit_value is NULL))
AND ( (Recinfo.charge_source_code = p_freight_cost_info.charge_source_code)
or (Recinfo.charge_source_code is NULL
and p_freight_cost_info.charge_source_code is NULL))
AND ( (Recinfo.line_type_code = p_freight_cost_info.line_type_code)
or (Recinfo.line_type_code is NULL
and p_freight_cost_info.line_type_code is NULL))
AND ( (Recinfo.estimated_flag = p_freight_cost_info.estimated_flag)
or (Recinfo.estimated_flag is NULL
and p_freight_cost_info.estimated_flag is NULL))
AND ( (Recinfo.commodity_category_id = p_freight_cost_info.commodity_category_id)
or (Recinfo.commodity_category_id is NULL
and p_freight_cost_info.commodity_category_id is NULL))
/* R12 new attributes */
AND ( (Recinfo.billable_quantity = p_freight_cost_info.billable_quantity)
or (Recinfo.billable_quantity is NULL
and p_freight_cost_info.billable_quantity is NULL))
AND ( (Recinfo.billable_uom = p_freight_cost_info.billable_uom)
or (Recinfo.billable_uom is NULL
and p_freight_cost_info.billable_uom is NULL))
AND ( (Recinfo.billable_basis = p_freight_cost_info.billable_basis)
or (Recinfo.billable_basis is NULL
and p_freight_cost_info.billable_basis is NULL))
) THEN
--
-- Debug Statements
--
IF l_debug_on THEN
WSH_DEBUG_SV.pop(l_module_name);
WSH_DEBUG_SV.logmsg(l_module_name,'FORM_RECORD_DELETED Error has occured',WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
PROCEDURE Delete_Freight_Cost(
p_rowid IN VARCHAR2
, p_freight_cost_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
)
IS
CURSOR C_Get_Freight_cost_id
IS
SELECT freight_cost_id
FROM wsh_freight_costs
WHERE rowid = p_rowid;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_FREIGHT_COST';
DELETE FROM wsh_freight_costs
WHERE freight_cost_id = p_freight_cost_id;
wsh_util_core.default_handler('WSH_FREIGHT_COSTS_PVT.DELETE_FREIGHT_COST');
END Delete_Freight_Cost;
SELECT
FREIGHT_COST_TYPE_ID,
UNIT_AMOUNT,
CURRENCY_CODE,
CALCULATION_METHOD,
UOM,
QUANTITY,
CONVERSION_DATE,
CONVERSION_RATE,
CONVERSION_TYPE_CODE,
TRIP_ID,
STOP_ID,
DELIVERY_ID,
DELIVERY_LEG_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
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,
COMMODITY_CATEGORY_ID
FROM WSH_FREIGHT_COSTS
WHERE FREIGHT_COST_ID = c_freight_cost_id FOR UPDATE;
SELECT wsh_freight_costs_s.nextval INTO l_freight_cost_id FROM sys.dual;
l_new_freight_cost_rec.LAST_UPDATE_DATE := sysdate;
l_new_freight_cost_rec.LAST_UPDATED_BY := l_from_freight_cost_rec.LAST_UPDATED_BY ;
l_new_freight_cost_rec.LAST_UPDATE_LOGIN := l_from_freight_cost_rec.LAST_UPDATE_LOGIN ;
l_new_freight_cost_rec.PROGRAM_UPDATE_DATE := l_from_freight_cost_rec.PROGRAM_UPDATE_DATE ;
UPDATE WSH_FREIGHT_COSTS
SET UNIT_AMOUNT = l_remained_unit_amount
WHERE CURRENT OF c_freight_cost;
SELECT unit_amount,
currency_code,
NVL(conversion_type_code, 'Corporate'),
NVL(conversion_date, SYSDATE),
conversion_rate
FROM wsh_freight_costs
WHERE delivery_id = c_delivery_id AND
NVL(charge_source_code, 'MANUAL') IN ('PRICING_ENGINE','MANUAL') AND
NVL(line_type_code, 'CHARGE') IN ('CHARGE', 'PRICE') AND
freight_cost_type_id <> -1 AND
unit_amount IS NOT NULL;
SELECT a.unit_amount,
a.currency_code,
NVL(conversion_type_code, 'Corporate'),
NVL(conversion_date, SYSDATE),
conversion_rate
FROM wsh_freight_costs a
WHERE a.delivery_detail_id in (
SELECT c.delivery_detail_id
FROM wsh_delivery_assignments_v b,
wsh_delivery_details c
WHERE b.delivery_id = c_delivery_id AND
c.delivery_detail_id = b.delivery_detail_id AND
c.released_status <> 'D' ) AND
NVL(a.charge_source_code, 'MANUAL' ) = 'MANUAL' AND
NVL(a.line_type_code, 'CHARGE') = 'CHARGE' AND
a.freight_cost_type_id <> -1 AND
a.unit_amount is NOT NULL;
SELECT a.unit_amount,
a.currency_code,
NVL(conversion_type_code, 'Corporate'),
NVL(conversion_date, SYSDATE),
conversion_rate
FROM wsh_freight_costs a, wsh_delivery_details wdd
WHERE wdd.delivery_detail_id=c_det_id AND
wdd.released_status <> 'D' AND
a.delivery_detail_id = wdd.delivery_detail_id AND
NVL(a.charge_source_code, 'MANUAL') IN ('PRICING_ENGINE','MANUAL') AND
NVL(a.line_type_code, 'CHARGE') IN ('CHARGE', 'PRICE') AND
a.freight_cost_type_id <> -1 AND
a.unit_amount > 0 ;
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v wda
WHERE LEVEL <= 10
START WITH delivery_detail_id = p_detailid
CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;
select 'Y'
from wsh_delivery_details
where delivery_detail_id=p_detailid
and container_flag='Y';
SELECT unit_amount,
currency_code,
NVL(conversion_type_code, 'Corporate'),
NVL(conversion_date, SYSDATE),
conversion_rate
FROM wsh_freight_costs
WHERE delivery_id = c_delivery_id AND
NVL(charge_source_code, 'MANUAL') IN ('PRICING_ENGINE','MANUAL') AND
NVL(line_type_code, 'CHARGE') IN ('CHARGE', 'PRICE') AND
freight_cost_type_id <> -1 AND
unit_amount > 0 AND
delivery_detail_id is null;
select wdd.delivery_detail_id
from wsh_delivery_assignments_v wda, wsh_delivery_details wdd
where wda.delivery_detail_id=wdd.delivery_detail_id
and wdd.container_flag= 'Y'
and wda.delivery_id=p_delid
and wda.parent_delivery_detail_id is null;
select wdd.delivery_detail_id
from wsh_delivery_assignments_v wda, wsh_delivery_details wdd
where wda.delivery_detail_id=wdd.delivery_detail_id
and wdd.container_flag= 'N'
and wda.delivery_id=p_delid
and wda.parent_delivery_detail_id is null;
SELECT freight_cost_id,
unit_amount,
currency_code,
NVL(conversion_type_code, 'Corporate'),
NVL(conversion_date, SYSDATE),
conversion_rate
FROM wsh_freight_costs
WHERE stop_id = c_stop_id AND
NVL(charge_source_code, 'MANUAL') IN ('PRICING_ENGINE','MANUAL') AND
NVL(line_type_code, 'CHARGE') IN ('CHARGE', 'PRICE') AND
freight_cost_type_id <> -1 AND
unit_amount > 0
union
SELECT wfc.freight_cost_id,
wfc.unit_amount,
wfc.currency_code,
NVL(wfc.conversion_type_code, 'Corporate'),
NVL(wfc.conversion_date, SYSDATE),
wfc.conversion_rate
FROM wsh_freight_costs wfc, wsh_freight_cost_types wfct
WHERE wfc.stop_id = c_stop_id AND
NVL(wfc.charge_source_code, 'MANUAL') = 'PRICING_ENGINE' AND
NVL(wfc.line_type_code, 'CHARGE') ='SUMMARY' AND
wfc.freight_cost_type_id <> -1 AND
wfc.unit_amount > 0 AND
wfct.FREIGHT_COST_TYPE_ID = wfc.FREIGHT_COST_TYPE_ID AND
NOT (wfct.name='SUMMARY' and wfct.freight_cost_type_code='FTESUMMARY')
;
select stop_id
from wsh_trip_stops
where trip_id=p_tripid;
select delivery_id
from wsh_delivery_legs wdl, wsh_trip_stops wts
where wdl.pick_up_stop_id=wts.stop_id
and wts.trip_id=p_tripid;
SELECT freight_cost_id,
unit_amount,
currency_code,
NVL(conversion_type_code, 'Corporate'),
NVL(conversion_date, SYSDATE),
conversion_rate
FROM wsh_freight_costs
WHERE trip_id = c_trip_id AND
NVL(charge_source_code, 'MANUAL') IN ('PRICING_ENGINE','MANUAL') AND
NVL(line_type_code, 'CHARGE') IN ('CHARGE', 'PRICE') AND
freight_cost_type_id <> -1 AND
unit_amount > 0
union
SELECT wfc.freight_cost_id,
wfc.unit_amount,
wfc.currency_code,
NVL(wfc.conversion_type_code, 'Corporate'),
NVL(wfc.conversion_date, SYSDATE),
wfc.conversion_rate
FROM wsh_freight_costs wfc, wsh_freight_cost_types wfct
WHERE wfc.trip_id = c_trip_id AND
NVL(wfc.charge_source_code, 'MANUAL') = 'PRICING_ENGINE' AND
NVL(wfc.line_type_code, 'CHARGE') ='SUMMARY' AND
wfc.freight_cost_type_id <> -1 AND
wfc.unit_amount > 0 AND
wfct.FREIGHT_COST_TYPE_ID = wfc.FREIGHT_COST_TYPE_ID AND
NOT (wfct.name='SUMMARY' and wfct.freight_cost_type_code='FTESUMMARY');
SELECT a.unit_amount,
a.currency_code,
NVL(conversion_type_code, 'Corporate'),
NVL(conversion_date, SYSDATE),
conversion_rate
FROM wsh_freight_costs a
WHERE a.delivery_detail_id in (
SELECT c.delivery_detail_id
FROM wsh_delivery_assignments_v b,
wsh_delivery_details c
WHERE b.delivery_id = c_delivery_id AND
c.delivery_detail_id = b.delivery_detail_id AND
c.released_status <> 'D') AND
NVL(a.charge_source_code, 'MANUAL' ) in('MANUAL', 'PRICING_ENGINE') AND
NVL(a.line_type_code, 'CHARGE') in ('CHARGE', 'PRICE') AND
a.freight_cost_type_id <> -1 AND
a.unit_amount is NOT NULL;
SELECT 1
FROM wsh_delivery_legs wshlg, wsh_delivery_assignments_v wshda,
wsh_new_deliveries wshnd
WHERE wshlg.delivery_id = c_delivery_id AND
wshlg.reprice_required = 'Y' AND
wshnd.delivery_id = wshlg.delivery_id and
wshda.delivery_id = wshnd.delivery_id and
wshda.delivery_detail_id is not null and
rownum = 1;
SELECT freight_cost_id
FROM wsh_freight_costs
WHERE delivery_detail_id = c_delivery_detail_id AND
charge_source_code = 'PRICING_ENGINE'
FOR UPDATE NOWAIT;
SELECT 1
FROM wsh_freight_costs
WHERE delivery_detail_id = c_delivery_detail_id AND
charge_source_code = 'PRICING_ENGINE' AND
rownum = 1;
SAVEPOINT befor_delete;
DELETE wsh_freight_costs WHERE freight_cost_id = l_freight_cost_id;
ROLLBACK TO before_delete;
SELECT freight_cost_id,
unit_amount,
currency_code,
NVL(conversion_type_code, 'Corporate'),
NVL(conversion_date, SYSDATE),
conversion_rate
FROM wsh_freight_costs
WHERE trip_id = c_trip_id AND
NVL(charge_source_code, 'MANUAL') = 'MANUAL' AND
NVL(line_type_code, 'CHARGE') IN ('CHARGE', 'PRICE') AND
freight_cost_type_id <> -1 AND
unit_amount > 0;