[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wsh_new_deliveries_s.nextval
FROM sys.dual;
SELECT delivery_id
FROM wsh_new_deliveries
WHERE name = v_delivery_name;
INSERT INTO wsh_new_deliveries
(
delivery_id
,name
,planned_flag
,status_code
,delivery_type
,loading_sequence
,loading_order_flag
,initial_pickup_date
,initial_pickup_location_id
,organization_id
,ultimate_dropoff_location_id
,ultimate_dropoff_date
,customer_id
,intmed_ship_to_location_id
,pooled_ship_to_location_id
,carrier_id
,ship_method_code
,freight_terms_code
,fob_code
,fob_location_id
,waybill
,dock_code
,acceptance_flag
,accepted_by
,accepted_date
,acknowledged_by
,confirmed_by
,confirm_date
,asn_date_sent
,asn_status_code
,asn_seq_number
,gross_weight
,net_weight
,weight_uom_code
,volume
,volume_uom_code
,additional_shipment_info
,currency_code
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,tp_attribute_category
,tp_attribute1
,tp_attribute2
,tp_attribute3
,tp_attribute4
,tp_attribute5
,tp_attribute6
,tp_attribute7
,tp_attribute8
,tp_attribute9
,tp_attribute10
,tp_attribute11
,tp_attribute12
,tp_attribute13
,tp_attribute14
,tp_attribute15
,global_attribute_category
,global_attribute1
,global_attribute2
,global_attribute3
,global_attribute4
,global_attribute5
,global_attribute6
,global_attribute7
,global_attribute8
,global_attribute9
,global_attribute10
,global_attribute11
,global_attribute12
,global_attribute13
,global_attribute14
,global_attribute15
,global_attribute16
,global_attribute17
,global_attribute18
,global_attribute19
,global_attribute20
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_application_id
,program_id
,program_update_date
,request_id
,batch_id
,hash_value
,source_header_id
,number_of_lpn
/* Changes for Shipping Data Model Bug#1918342*/
,cod_amount
,cod_currency_code
,cod_remit_to
,cod_charge_paid_by
,problem_contact_reference
,port_of_loading
,port_of_discharge
,ftz_number
,routed_export_txn
,entry_number
,routing_instructions
,in_bond_code
,shipping_marks
/* H Integration: datamodel changes wrudge */
,service_level
,mode_of_transport
,assigned_to_fte_trips
,auto_sc_exclude_flag
,auto_ap_exclude_flag
/* J Inbound Logistics jckwok */
,shipment_direction
,vendor_id
,party_id
,routing_response_id
,rcv_shipment_header_id
,asn_shipment_header_id
,shipping_control
/* J TP Release : ttrichy */
,TP_DELIVERY_NUMBER
,EARLIEST_PICKUP_DATE
,LATEST_PICKUP_DATE
,EARLIEST_DROPOFF_DATE
,LATEST_DROPOFF_DATE
,IGNORE_FOR_PLANNING
,TP_PLAN_NAME
-- J: W/V Changes
,WV_FROZEN_FLAG
,HASH_STRING
,delivered_date
-- bug 3667348
,REASON_OF_TRANSPORT
,DESCRIPTION
--OTM R12
,TMS_INTERFACE_FLAG
,TMS_VERSION_NUMBER
--
)
VALUES (
x_delivery_id
,x_name
,nvl(p_delivery_info.planned_flag,'N')
,nvl(p_delivery_info.status_code,'OP')
,nvl(p_delivery_info.delivery_type,'STANDARD')
,p_delivery_info.loading_sequence
,p_delivery_info.loading_order_flag
,p_delivery_info.initial_pickup_date
,p_delivery_info.initial_pickup_location_id
,p_delivery_info.organization_id
,p_delivery_info.ultimate_dropoff_location_id
,p_delivery_info.ultimate_dropoff_date
,p_delivery_info.customer_id
,p_delivery_info.intmed_ship_to_location_id
,p_delivery_info.pooled_ship_to_location_id
,p_delivery_info.carrier_id
,p_delivery_info.ship_method_code
,p_delivery_info.freight_terms_code
,p_delivery_info.fob_code
,p_delivery_info.fob_location_id
,p_delivery_info.waybill
,p_delivery_info.dock_code
,p_delivery_info.acceptance_flag
,p_delivery_info.accepted_by
,p_delivery_info.accepted_date
,p_delivery_info.acknowledged_by
,p_delivery_info.confirmed_by
,p_delivery_info.confirm_date
,p_delivery_info.asn_date_sent
,p_delivery_info.asn_status_code
,p_delivery_info.asn_seq_number
,p_delivery_info.gross_weight
,p_delivery_info.net_weight
,p_delivery_info.weight_uom_code
,p_delivery_info.volume
,p_delivery_info.volume_uom_code
,p_delivery_info.additional_shipment_info
,p_delivery_info.currency_code
,p_delivery_info.attribute_category
,p_delivery_info.attribute1
,p_delivery_info.attribute2
,p_delivery_info.attribute3
,p_delivery_info.attribute4
,p_delivery_info.attribute5
,p_delivery_info.attribute6
,p_delivery_info.attribute7
,p_delivery_info.attribute8
,p_delivery_info.attribute9
,p_delivery_info.attribute10
,p_delivery_info.attribute11
,p_delivery_info.attribute12
,p_delivery_info.attribute13
,p_delivery_info.attribute14
,p_delivery_info.attribute15
,p_delivery_info.tp_attribute_category
,p_delivery_info.tp_attribute1
,p_delivery_info.tp_attribute2
,p_delivery_info.tp_attribute3
,p_delivery_info.tp_attribute4
,p_delivery_info.tp_attribute5
,p_delivery_info.tp_attribute6
,p_delivery_info.tp_attribute7
,p_delivery_info.tp_attribute8
,p_delivery_info.tp_attribute9
,p_delivery_info.tp_attribute10
,p_delivery_info.tp_attribute11
,p_delivery_info.tp_attribute12
,p_delivery_info.tp_attribute13
,p_delivery_info.tp_attribute14
,p_delivery_info.tp_attribute15
,p_delivery_info.global_attribute_category
,p_delivery_info.global_attribute1
,p_delivery_info.global_attribute2
,p_delivery_info.global_attribute3
,p_delivery_info.global_attribute4
,p_delivery_info.global_attribute5
,p_delivery_info.global_attribute6
,p_delivery_info.global_attribute7
,p_delivery_info.global_attribute8
,p_delivery_info.global_attribute9
,p_delivery_info.global_attribute10
,p_delivery_info.global_attribute11
,p_delivery_info.global_attribute12
,p_delivery_info.global_attribute13
,p_delivery_info.global_attribute14
,p_delivery_info.global_attribute15
,p_delivery_info.global_attribute16
,p_delivery_info.global_attribute17
,p_delivery_info.global_attribute18
,p_delivery_info.global_attribute19
,p_delivery_info.global_attribute20
,nvl(p_delivery_info.creation_date, SYSDATE)
,nvl(p_delivery_info.created_by,FND_GLOBAL.USER_ID)
,nvl(p_delivery_info.last_update_date, SYSDATE)
,nvl(p_delivery_info.last_updated_by,FND_GLOBAL.USER_ID)
,nvl(p_delivery_info.last_update_login,FND_GLOBAL.LOGIN_ID)
,p_delivery_info.program_application_id
,p_delivery_info.program_id
,p_delivery_info.program_update_date
,p_delivery_info.request_id
,p_delivery_info.batch_id
,p_delivery_info.hash_value
,p_delivery_info.source_header_id
,p_delivery_info.number_of_lpn
/* Changes for Shipping Data Model Bug#1918342*/
,p_delivery_info.cod_amount
,p_delivery_info.cod_currency_code
,p_delivery_info.cod_remit_to
,p_delivery_info.cod_charge_paid_by
,p_delivery_info.problem_contact_reference
,p_delivery_info.port_of_loading
,p_delivery_info.port_of_discharge
,p_delivery_info.ftz_number
,p_delivery_info.routed_export_txn
,p_delivery_info.entry_number
,p_delivery_info.routing_instructions
,p_delivery_info.in_bond_code
,p_delivery_info.shipping_marks
/* H Integration: datamodel changes wrudge */
,p_delivery_info.service_level
,p_delivery_info.mode_of_transport
,p_delivery_info.assigned_to_fte_trips
,p_delivery_info.auto_sc_exclude_flag
,p_delivery_info.auto_ap_exclude_flag
/* J Inbound Logistics jckwok */
,nvl(p_delivery_info.shipment_direction, 'O')
,p_delivery_info.vendor_id
,p_delivery_info.party_id
,p_delivery_info.routing_response_id
,p_delivery_info.rcv_shipment_header_id
,p_delivery_info.asn_shipment_header_id
,p_delivery_info.shipping_control
/* J TP Release : ttrichy */
,p_delivery_info.TP_DELIVERY_NUMBER
,p_delivery_info.EARLIEST_PICKUP_DATE
,p_delivery_info.LATEST_PICKUP_DATE
,p_delivery_info.EARLIEST_DROPOFF_DATE
,p_delivery_info.LATEST_DROPOFF_DATE
,nvl(p_delivery_info.ignore_for_planning,'N')
,p_delivery_info.TP_PLAN_NAME
-- J: W/V Changes
,nvl(p_delivery_info.wv_frozen_flag, 'N')
,p_delivery_info.hash_string
,p_delivery_info.delivered_date
-- bug 3667348
,p_delivery_info.REASON_OF_TRANSPORT
,p_delivery_info.DESCRIPTION
-- bug 3667348
--OTM R12
,WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT
,1
--
)
RETURNING rowid
INTO x_rowid;
PROCEDURE Update_Delivery
(p_rowid IN VARCHAR2,
p_delivery_info IN Delivery_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2
) IS
-- J: W/V Changes
CURSOR get_del_info IS
SELECT rowid,
gross_weight,
net_weight,
volume,
weight_uom_code,
volume_uom_code,
wv_frozen_flag,
organization_id
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_info.delivery_id;
SELECT organization_id, name
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_info.delivery_id
and (carrier_id <> p_delivery_info.carrier_id
OR ship_method_code <> p_delivery_info.ship_method_code);
l_tms_update VARCHAR2(1);
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DELIVERY';
l_tms_update := 'N';
WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
l_tms_update := 'Y';
l_new_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
l_tms_update := 'N';
UPDATE wsh_new_deliveries
SET
delivery_id = p_delivery_info.delivery_id
,name = p_delivery_info.name
,planned_flag = p_delivery_info.planned_flag
,status_code = p_delivery_info.status_code
,delivery_type = p_delivery_info.delivery_type
,loading_sequence = p_delivery_info.loading_sequence
,loading_order_flag = p_delivery_info.loading_order_flag
,initial_pickup_date = p_delivery_info.initial_pickup_date
,initial_pickup_location_id = p_delivery_info.initial_pickup_location_id
,organization_id = p_delivery_info.organization_id
,ultimate_dropoff_location_id = p_delivery_info.ultimate_dropoff_location_id
,ultimate_dropoff_date = p_delivery_info.ultimate_dropoff_date
,customer_id = p_delivery_info.customer_id
,intmed_ship_to_location_id = p_delivery_info.intmed_ship_to_location_id
,pooled_ship_to_location_id = p_delivery_info.pooled_ship_to_location_id
,carrier_id = p_delivery_info.carrier_id
,ship_method_code = p_delivery_info.ship_method_code
,freight_terms_code = p_delivery_info.freight_terms_code
,fob_code = p_delivery_info.fob_code
,fob_location_id = p_delivery_info.fob_location_id
,waybill = p_delivery_info.waybill
,dock_code = p_delivery_info.dock_code
,acceptance_flag = p_delivery_info.acceptance_flag
,accepted_by = p_delivery_info.accepted_by
,accepted_date = p_delivery_info.accepted_date
,acknowledged_by = p_delivery_info.acknowledged_by
,confirmed_by = p_delivery_info.confirmed_by
,confirm_date = p_delivery_info.confirm_date
,asn_date_sent = p_delivery_info.asn_date_sent
,asn_status_code = p_delivery_info.asn_status_code
,asn_seq_number = p_delivery_info.asn_seq_number
,gross_weight = l_gross_weight -- Bugfix #4587421
,net_weight = l_net_weight -- Bugfix #4587421
,weight_uom_code = p_delivery_info.weight_uom_code
,volume = p_delivery_info.volume
,volume_uom_code = p_delivery_info.volume_uom_code
,additional_shipment_info = p_delivery_info.additional_shipment_info
,currency_code = p_delivery_info.currency_code
,attribute_category = p_delivery_info.attribute_category
,attribute1 = p_delivery_info.attribute1
,attribute2 = p_delivery_info.attribute2
,attribute3 = p_delivery_info.attribute3
,attribute4 = p_delivery_info.attribute4
,attribute5 = p_delivery_info.attribute5
,attribute6 = p_delivery_info.attribute6
,attribute7 = p_delivery_info.attribute7
,attribute8 = p_delivery_info.attribute8
,attribute9 = p_delivery_info.attribute9
,attribute10 = p_delivery_info.attribute10
,attribute11 = p_delivery_info.attribute11
,attribute12 = p_delivery_info.attribute12
,attribute13 = p_delivery_info.attribute13
,attribute14 = p_delivery_info.attribute14
,attribute15 = p_delivery_info.attribute15
,tp_attribute_category = p_delivery_info.tp_attribute_category
,tp_attribute1 = p_delivery_info.tp_attribute1
,tp_attribute2 = p_delivery_info.tp_attribute2
,tp_attribute3 = p_delivery_info.tp_attribute3
,tp_attribute4 = p_delivery_info.tp_attribute4
,tp_attribute5 = p_delivery_info.tp_attribute5
,tp_attribute6 = p_delivery_info.tp_attribute6
,tp_attribute7 = p_delivery_info.tp_attribute7
,tp_attribute8 = p_delivery_info.tp_attribute8
,tp_attribute9 = p_delivery_info.tp_attribute9
,tp_attribute10 = p_delivery_info.tp_attribute10
,tp_attribute11 = p_delivery_info.tp_attribute11
,tp_attribute12 = p_delivery_info.tp_attribute12
,tp_attribute13 = p_delivery_info.tp_attribute13
,tp_attribute14 = p_delivery_info.tp_attribute14
,tp_attribute15 = p_delivery_info.tp_attribute15
,global_attribute_category = p_delivery_info.global_attribute_category
,global_attribute1 = p_delivery_info.global_attribute1
,global_attribute2 = p_delivery_info.global_attribute2
,global_attribute3 = p_delivery_info.global_attribute3
,global_attribute4 = p_delivery_info.global_attribute4
,global_attribute5 = p_delivery_info.global_attribute5
,global_attribute6 = p_delivery_info.global_attribute6
,global_attribute7 = p_delivery_info.global_attribute7
,global_attribute8 = p_delivery_info.global_attribute8
,global_attribute9 = p_delivery_info.global_attribute9
,global_attribute10 = p_delivery_info.global_attribute10
,global_attribute11 = p_delivery_info.global_attribute11
,global_attribute12 = p_delivery_info.global_attribute12
,global_attribute13 = p_delivery_info.global_attribute13
,global_attribute14 = p_delivery_info.global_attribute14
,global_attribute15 = p_delivery_info.global_attribute15
,global_attribute16 = p_delivery_info.global_attribute16
,global_attribute17 = p_delivery_info.global_attribute17
,global_attribute18 = p_delivery_info.global_attribute18
,global_attribute19 = p_delivery_info.global_attribute19
,global_attribute20 = p_delivery_info.global_attribute20
,last_update_date = p_delivery_info.last_update_date
,last_updated_by = p_delivery_info.last_updated_by
,last_update_login = p_delivery_info.last_update_login
,program_application_id = p_delivery_info.program_application_id
,program_id = p_delivery_info.program_id
,program_update_date = p_delivery_info.program_update_date
,request_id = p_delivery_info.request_id
,number_of_lpn = p_delivery_info.number_of_lpn
/* Changes for the Shipping Data Model Bug#1918342*/
,COD_AMOUNT = p_delivery_info.COD_AMOUNT
,COD_CURRENCY_CODE = p_delivery_info.COD_CURRENCY_CODE
,COD_REMIT_TO = p_delivery_info.COD_REMIT_TO
,COD_CHARGE_PAID_BY = p_delivery_info.COD_CHARGE_PAID_BY
,PROBLEM_CONTACT_REFERENCE = p_delivery_info.PROBLEM_CONTACT_REFERENCE
,PORT_OF_LOADING = p_delivery_info.PORT_OF_LOADING
,PORT_OF_DISCHARGE = p_delivery_info.PORT_OF_DISCHARGE
,FTZ_NUMBER = p_delivery_info.FTZ_NUMBER
,ROUTED_EXPORT_TXN = p_delivery_info.ROUTED_EXPORT_TXN
,ENTRY_NUMBER = p_delivery_info.ENTRY_NUMBER
,ROUTING_INSTRUCTIONS = p_delivery_info.ROUTING_INSTRUCTIONS
,IN_BOND_CODE = p_delivery_info.IN_BOND_CODE
,SHIPPING_MARKS = p_delivery_info.SHIPPING_MARKS
/* H Integration: datamodel changes wrudge */
,SERVICE_LEVEL = p_delivery_info.SERVICE_LEVEL
,MODE_OF_TRANSPORT = p_delivery_info.MODE_OF_TRANSPORT
,ASSIGNED_TO_FTE_TRIPS = p_delivery_info.ASSIGNED_TO_FTE_TRIPS
,auto_sc_exclude_flag = p_delivery_info.auto_sc_exclude_flag
,auto_ap_exclude_flag = p_delivery_info.auto_ap_exclude_flag
/* J Inbound Logistics new columns jckwok*/
,SHIPMENT_DIRECTION = nvl(p_delivery_info.SHIPMENT_DIRECTION, 'O')
,VENDOR_ID = p_delivery_info.VENDOR_ID
,PARTY_ID = p_delivery_info.PARTY_ID
,ROUTING_RESPONSE_ID = p_delivery_info.ROUTING_RESPONSE_ID
,RCV_SHIPMENT_HEADER_ID = p_delivery_info.RCV_SHIPMENT_HEADER_ID
,ASN_SHIPMENT_HEADER_ID = p_delivery_info.ASN_SHIPMENT_HEADER_ID
,SHIPPING_CONTROL = p_delivery_info.SHIPPING_CONTROL
/* J TP Release : ttrichy */
,TP_DELIVERY_NUMBER = p_delivery_info.TP_DELIVERY_NUMBER
,EARLIEST_PICKUP_DATE = p_delivery_info.EARLIEST_PICKUP_DATE
,LATEST_PICKUP_DATE = p_delivery_info.LATEST_PICKUP_DATE
,EARLIEST_DROPOFF_DATE = p_delivery_info.EARLIEST_DROPOFF_DATE
,LATEST_DROPOFF_DATE = p_delivery_info.LATEST_DROPOFF_DATE
,IGNORE_FOR_PLANNING = nvl(p_delivery_info.IGNORE_FOR_PLANNING, 'N')
,TP_PLAN_NAME = p_delivery_info.TP_PLAN_NAME
/* J: W/V Changes */
,WV_FROZEN_FLAG = l_frozen_flag
,HASH_VALUE = p_delivery_info.HASH_VALUE
,HASH_STRING = p_delivery_info.HASH_STRING
,delivered_date = p_delivery_info.delivered_date
-- bug 3667348
,REASON_OF_TRANSPORT =p_delivery_info.REASON_OF_TRANSPORT
,DESCRIPTION =p_delivery_info.DESCRIPTION
-- bug 3667348
--OTM R12
,TMS_INTERFACE_FLAG = DECODE(l_tms_update,
'Y', l_new_interface_flag_tab(1),
NVL(TMS_INTERFACE_FLAG, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT))
,TMS_VERSION_NUMBER = DECODE(l_tms_update,
'Y', l_tms_version_number,
NVL(tms_version_number, 1))
--
WHERE rowid = l_rowid;
WSH_DEBUG_SV.log(l_module_name,'Rows updated',SQL%ROWCOUNT);
IF (l_gc3_is_installed = 'Y' AND l_tms_update = 'Y') THEN
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.LOG_OTM_EXCEPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.UPDATE_DELIVERY',l_module_name);
END Update_Delivery;
PROCEDURE Delete_Delivery
(p_rowid IN VARCHAR2 := NULL,
p_delivery_id IN NUMBER := NULL,
x_return_status OUT NOCOPY VARCHAR2,
p_validate_flag IN VARCHAR2 DEFAULT 'Y'
) IS
CURSOR get_del_id_rowid (v_rowid VARCHAR2) IS
SELECT delivery_id
FROM wsh_new_deliveries
WHERE rowid = v_rowid;
SELECT delivery_leg_id, parent_delivery_leg_id
FROM wsh_delivery_legs
WHERE delivery_id = v_delivery_id;
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
WHERE delivery_id = v_delivery_id
and parent_delivery_detail_id is NULL;
cannot_delete_delivery EXCEPTION;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_DELIVERY';
wsh_delivery_validations.check_delete_delivery(
p_delivery_id => l_delivery_id,
x_return_status => l_return_status);
RAISE cannot_delete_delivery;
SAVEPOINT wsh_before_delivery_delete;
p_caller => 'WSH_DELETE_DEL',
p_del_tab => l_mdc_del_tab,
x_return_status => l_return_status);
l_mdc_del_tab.delete;
ROLLBACK TO wsh_before_delivery_delete;
RAISE cannot_delete_delivery;
WSH_DELIVERY_LEGS_PVT.Delete_Delivery_Leg(
p_delivery_leg_id => dl.delivery_leg_id,
x_return_status => l_return_status);
ROLLBACK TO wsh_before_delivery_delete;
RAISE cannot_delete_delivery;
DELETE FROM wsh_freight_costs
WHERE delivery_id = l_delivery_id;
'Freight cost rows deleted', SQL%ROWCOUNT);
-- delete the delivery
DELETE FROM wsh_new_deliveries
WHERE delivery_id = l_delivery_id;
WSH_DEBUG_SV.log(l_module_name,'Rows deleted',SQL%ROWCOUNT);
WHEN cannot_delete_delivery THEN
IF (get_del_id_rowid%ISOPEN) THEN
CLOSE get_del_id_rowid;
FND_MESSAGE.SET_NAME('WSH','WSH_DEL_DELETE_ERROR');
WSH_DEBUG_SV.logmsg(l_module_name,'CANNOT_DELETE_DELIVERY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:CANNOT_DELETE_DELIVERY');
ROLLBACK TO wsh_before_delivery_delete;
wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.DELETE_DELIVERY',l_module_name);
END Delete_Delivery;
SELECT *
FROM wsh_new_deliveries
WHERE rowid = p_rowid
FOR UPDATE OF delivery_id NOWAIT;
FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
WSH_DEBUG_SV.log(l_module_name,'FORM_RECORD_DELETED');
the last updated date gets updated in the database , but not
in the form.
AND (recinfo.last_update_date = p_delivery_info.last_update_date)
AND (recinfo.last_updated_by = p_delivery_info.last_updated_by)
AND ( (recinfo.last_update_login = p_delivery_info.last_update_login)
OR ( (recinfo.last_update_login is NULL)
AND (p_delivery_info.last_update_login is NULL)))
*/
AND ( (recinfo.program_application_id = p_delivery_info.program_application_id)
OR ( (recinfo.program_application_id is NULL)
AND (p_delivery_info.program_application_id is NULL)))
AND ( (recinfo.program_id = p_delivery_info.program_id)
OR ( (recinfo.program_id is NULL)
AND (p_delivery_info.program_id is NULL)))
AND ( (recinfo.program_update_date = p_delivery_info.program_update_date)
OR ( (recinfo.program_update_date is NULL)
AND (p_delivery_info.program_update_date is NULL)))
AND ( (recinfo.request_id = p_delivery_info.request_id)
OR ( (recinfo.request_id is NULL)
AND (p_delivery_info.request_id is NULL)))
AND ( (recinfo.number_of_lpn = p_delivery_info.number_of_lpn) --bugfix 1426086: added number_of_lpn
OR ( (recinfo.number_of_lpn is NULL)
AND (p_delivery_info.number_of_lpn is NULL)))
/* Changes for the shipping data model bug#1918342*/
AND ( (recinfo.cod_amount= p_delivery_info.cod_amount)
OR ( (recinfo.cod_amount is NULL)
AND (p_delivery_info.cod_amount is NULL)))
AND ( (recinfo.cod_currency_code = p_delivery_info.cod_currency_code)
OR ( (recinfo.cod_currency_code is NULL)
AND (p_delivery_info.cod_currency_code is NULL)))
AND ( (recinfo.cod_remit_to = p_delivery_info.cod_remit_to)
OR ( (recinfo.cod_remit_to is NULL)
AND (p_delivery_info.cod_remit_to is NULL)))
AND ( (recinfo.cod_charge_paid_by = p_delivery_info.cod_charge_paid_by)
OR ( (recinfo.cod_charge_paid_by is NULL)
AND (p_delivery_info.cod_charge_paid_by is NULL)))
AND ( (recinfo.problem_contact_reference = p_delivery_info.problem_contact_reference)
OR ( (recinfo.problem_contact_reference is NULL)
AND (p_delivery_info.problem_contact_reference is NULL)))
AND ( (recinfo.port_of_loading = p_delivery_info.port_of_loading)
OR ( (recinfo.port_of_loading is NULL)
AND (p_delivery_info.port_of_loading is NULL)))
AND ( (recinfo.port_of_discharge = p_delivery_info.port_of_discharge)
OR ( (recinfo.port_of_discharge is NULL)
AND (p_delivery_info.port_of_discharge is NULL)))
AND ( (recinfo.ftz_number = p_delivery_info.ftz_number)
OR ( (recinfo.ftz_number is NULL)
AND (p_delivery_info.ftz_number is NULL)))
AND ( (recinfo.routed_export_txn = p_delivery_info.routed_export_txn)
OR ( (recinfo.routed_export_txn is NULL)
AND (p_delivery_info.routed_export_txn is NULL)))
AND ( (recinfo.entry_number = p_delivery_info.entry_number)
OR ( (recinfo.entry_number is NULL)
AND (p_delivery_info.entry_number is NULL)))
AND ( (recinfo.routing_instructions = p_delivery_info.routing_instructions)
OR ( (recinfo.routing_instructions is NULL)
AND (p_delivery_info.routing_instructions is NULL)))
AND ( (recinfo.in_bond_code = p_delivery_info.in_bond_code)
OR ( (recinfo.in_bond_code is NULL)
AND (p_delivery_info.in_bond_code is NULL)))
AND ( (recinfo.shipping_marks = p_delivery_info.shipping_marks)
OR ( (recinfo.shipping_marks is NULL)
AND (p_delivery_info.shipping_marks is NULL)))
/* H Integration: datamodel changes wrudge */
AND ( (recinfo.service_level = p_delivery_info.service_level)
OR ( (recinfo.service_level is NULL)
AND (p_delivery_info.service_level is NULL)))
AND ( (recinfo.mode_of_transport = p_delivery_info.mode_of_transport)
OR ( (recinfo.mode_of_transport is NULL)
AND (p_delivery_info.mode_of_transport is NULL)))
AND ( (recinfo.assigned_to_fte_trips = p_delivery_info.assigned_to_fte_trips)
OR ( (recinfo.assigned_to_fte_trips is NULL)
AND (p_delivery_info.assigned_to_fte_trips is NULL)))
AND ( (recinfo.auto_sc_exclude_flag = p_delivery_info.auto_sc_exclude_flag)
OR ( (recinfo.auto_sc_exclude_flag is NULL)
AND (p_delivery_info.auto_sc_exclude_flag is NULL)))
AND ( (recinfo.auto_ap_exclude_flag = p_delivery_info.auto_ap_exclude_flag)
OR ( (recinfo.auto_ap_exclude_flag is NULL)
AND (p_delivery_info.auto_ap_exclude_flag is NULL)))
AND ( (nvl(recinfo.shipment_direction, 'O') = nvl(p_delivery_info.shipment_direction,'O'))
OR ( (recinfo.shipment_direction is NULL)
AND (p_delivery_info.shipment_direction is NULL)))
AND ( (recinfo.vendor_id = p_delivery_info.vendor_id)
OR ( (recinfo.vendor_id is NULL)
AND (p_delivery_info.vendor_id is NULL)))
AND ( (recinfo.party_id = p_delivery_info.party_id)
OR ( (recinfo.party_id is NULL)
AND (p_delivery_info.party_id is NULL)))
AND ( (recinfo.routing_response_id = p_delivery_info.routing_response_id)
OR ( (recinfo.routing_response_id is NULL)
AND (p_delivery_info.routing_response_id is NULL)))
AND ( (recinfo.rcv_shipment_header_id = p_delivery_info.rcv_shipment_header_id)
OR ( (recinfo.rcv_shipment_header_id is NULL)
AND (p_delivery_info.rcv_shipment_header_id is NULL)))
AND ( (recinfo.asn_shipment_header_id = p_delivery_info.asn_shipment_header_id)
OR ( (recinfo.asn_shipment_header_id is NULL)
AND (p_delivery_info.asn_shipment_header_id is NULL)))
AND ( (recinfo.shipping_control = p_delivery_info.shipping_control)
OR ( (recinfo.shipping_control is NULL)
AND (p_delivery_info.shipping_control is NULL)))
/* J TP Release : ttrichy */
AND ( (recinfo.TP_DELIVERY_NUMBER = p_delivery_info.TP_DELIVERY_NUMBER)
OR ( (recinfo.TP_DELIVERY_NUMBER is NULL)
AND (p_delivery_info.TP_DELIVERY_NUMBER is NULL)))
AND ( (recinfo.EARLIEST_PICKUP_DATE = p_delivery_info.EARLIEST_PICKUP_DATE)
OR ( (recinfo.EARLIEST_PICKUP_DATE is NULL)
AND (p_delivery_info.EARLIEST_PICKUP_DATE is NULL)))
AND ( (recinfo.LATEST_PICKUP_DATE = p_delivery_info.LATEST_PICKUP_DATE)
OR ( (recinfo.LATEST_PICKUP_DATE is NULL)
AND (p_delivery_info.LATEST_PICKUP_DATE is NULL)))
AND ( (recinfo.EARLIEST_DROPOFF_DATE = p_delivery_info.EARLIEST_DROPOFF_DATE)
OR ( (recinfo.EARLIEST_DROPOFF_DATE is NULL)
AND (p_delivery_info.EARLIEST_DROPOFF_DATE is NULL)))
AND ( (recinfo.LATEST_DROPOFF_DATE = p_delivery_info.LATEST_DROPOFF_DATE)
OR ( (recinfo.LATEST_DROPOFF_DATE is NULL)
AND (p_delivery_info.LATEST_DROPOFF_DATE is NULL)))
AND ( (nvl(recinfo.IGNORE_FOR_PLANNING, 'N') = nvl(p_delivery_info.IGNORE_FOR_PLANNING, 'N')))
AND ( (recinfo.TP_PLAN_NAME = p_delivery_info.TP_PLAN_NAME)
OR ( (recinfo.TP_PLAN_NAME is NULL)
AND (p_delivery_info.TP_PLAN_NAME is NULL)))
-- J: W/V Changes
AND ( (recinfo.wv_frozen_flag = p_delivery_info.wv_frozen_flag)
OR ( (recinfo.wv_frozen_flag is NULL)
AND (p_delivery_info.wv_frozen_flag is NULL)))
AND ( (recinfo.delivered_date = p_delivery_info.delivered_date)
OR ( (recinfo.delivered_date is NULL)
AND (p_delivery_info.delivered_date is NULL)))
-- bug 3667348
AND ( (recinfo.REASON_OF_TRANSPORT = p_delivery_info.REASON_OF_TRANSPORT)
OR ( (recinfo.REASON_OF_TRANSPORT is NULL)
AND (p_delivery_info.REASON_OF_TRANSPORT is NULL)))
AND ( (recinfo.DESCRIPTION = p_delivery_info.DESCRIPTION)
OR ( (recinfo.DESCRIPTION is NULL)
AND (p_delivery_info.DESCRIPTION is NULL)))
-- bug 3667348
--OTM R12
AND ( (recinfo.TMS_INTERFACE_FLAG = p_delivery_info.TMS_INTERFACE_FLAG)
OR ( (recinfo.TMS_INTERFACE_FLAG IS NULL)
AND (p_delivery_info.TMS_INTERFACE_FLAG IS NULL)))
AND ( (recinfo.TMS_VERSION_NUMBER = p_delivery_info.TMS_VERSION_NUMBER)
OR ( (recinfo.TMS_VERSION_NUMBER IS NULL)
AND (p_delivery_info.TMS_VERSION_NUMBER IS NULL)))
--
) THEN
--
IF l_debug_on THEN
WSH_DEBUG_SV.log(l_module_name,'Nothing has changed');
SELECT
DELIVERY_ID,
NAME,
PLANNED_FLAG,
STATUS_CODE,
DELIVERY_TYPE,
LOADING_SEQUENCE,
LOADING_ORDER_FLAG,
INITIAL_PICKUP_DATE,
INITIAL_PICKUP_LOCATION_ID,
ORGANIZATION_ID,
ULTIMATE_DROPOFF_LOCATION_ID,
ULTIMATE_DROPOFF_DATE,
CUSTOMER_ID,
INTMED_SHIP_TO_LOCATION_ID,
POOLED_SHIP_TO_LOCATION_ID,
CARRIER_ID,
SHIP_METHOD_CODE,
FREIGHT_TERMS_CODE,
FOB_CODE,
FOB_LOCATION_ID,
WAYBILL,
DOCK_CODE,
ACCEPTANCE_FLAG,
ACCEPTED_BY,
ACCEPTED_DATE,
ACKNOWLEDGED_BY,
CONFIRMED_BY,
CONFIRM_DATE,
ASN_DATE_SENT,
ASN_STATUS_CODE,
ASN_SEQ_NUMBER,
GROSS_WEIGHT,
NET_WEIGHT,
WEIGHT_UOM_CODE,
VOLUME,
VOLUME_UOM_CODE,
ADDITIONAL_SHIPMENT_INFO,
CURRENCY_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
TP_ATTRIBUTE_CATEGORY,
TP_ATTRIBUTE1,
TP_ATTRIBUTE2,
TP_ATTRIBUTE3,
TP_ATTRIBUTE4,
TP_ATTRIBUTE5,
TP_ATTRIBUTE6,
TP_ATTRIBUTE7,
TP_ATTRIBUTE8,
TP_ATTRIBUTE9,
TP_ATTRIBUTE10,
TP_ATTRIBUTE11,
TP_ATTRIBUTE12,
TP_ATTRIBUTE13,
TP_ATTRIBUTE14,
TP_ATTRIBUTE15,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
BATCH_ID,
HASH_VALUE,
SOURCE_HEADER_ID,
NUMBER_OF_LPN,
/* Changes for the Shipping Data Model Bug#1918342*/
COD_AMOUNT,
COD_CURRENCY_CODE,
COD_REMIT_TO,
COD_CHARGE_PAID_BY,
PROBLEM_CONTACT_REFERENCE,
PORT_OF_LOADING,
PORT_OF_DISCHARGE,
FTZ_NUMBER,
ROUTED_EXPORT_TXN,
ENTRY_NUMBER,
ROUTING_INSTRUCTIONS,
IN_BOND_CODE,
SHIPPING_MARKS,
/* H Integration: datamodel changes wrudge */
SERVICE_LEVEL,
MODE_OF_TRANSPORT,
ASSIGNED_TO_FTE_TRIPS,
AUTO_SC_EXCLUDE_FLAG,
AUTO_AP_EXCLUDE_FLAG,
AP_BATCH_ID,
ROWID,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
/* J Inbound Logistics: New columns jckwok */
SHIPMENT_DIRECTION,
VENDOR_ID,
PARTY_ID,
ROUTING_RESPONSE_ID,
RCV_SHIPMENT_HEADER_ID,
ASN_SHIPMENT_HEADER_ID,
SHIPPING_CONTROL
/* J TP Release : ttrichy */
,TP_DELIVERY_NUMBER
,EARLIEST_PICKUP_DATE
,LATEST_PICKUP_DATE
,EARLIEST_DROPOFF_DATE
,LATEST_DROPOFF_DATE
,nvl(IGNORE_FOR_PLANNING, 'N') ignore_for_planning
,TP_PLAN_NAME
-- J: W/V Changes
,WV_FROZEN_FLAG
,hash_string
,delivered_date,
null , -- packing_slip
-- bug 3667348
REASON_OF_TRANSPORT,
DESCRIPTION,
-- bug 3667348
'N',--Non Database field added for "Proration of weight from Delivery to delivery lines" Project(Bug#4254552).
--OTM R12
TMS_INTERFACE_FLAG,
TMS_VERSION_NUMBER
--
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT name
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
does a SELECT using FOR UPDATE NOWAIT
Created: Harmonization Project. Patchset I
----------------------------------------------------------------------- */
PROCEDURE Lock_Dlvy_No_Compare(
p_delivery_id IN NUMBER)
IS
CURSOR c_lock_dlvy(p_dlvy_id NUMBER) IS
SELECT wnd.delivery_id
FROM wsh_new_deliveries wnd
WHERE wnd.delivery_id = p_dlvy_id
FOR UPDATE NOWAIT;
SELECT wsh_new_deliveries_s.nextval
FROM dual;
SELECT rowid
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT delivery_leg_id
FROM wsh_delivery_legs
WHERE delivery_id = p_delivery_id;
SELECT delivery_id
FROM wsh_new_deliveries
WHERE name = p_delivery_name;
INSERT INTO WSH_NEW_DELIVERIES
(
DELIVERY_ID,
NAME,
PLANNED_FLAG,
STATUS_CODE,
DELIVERY_TYPE,
LOADING_SEQUENCE,
LOADING_ORDER_FLAG,
INITIAL_PICKUP_DATE,
INITIAL_PICKUP_LOCATION_ID,
ORGANIZATION_ID,
ULTIMATE_DROPOFF_LOCATION_ID,
ULTIMATE_DROPOFF_DATE,
CUSTOMER_ID,
INTMED_SHIP_TO_LOCATION_ID,
POOLED_SHIP_TO_LOCATION_ID,
CARRIER_ID,
SHIP_METHOD_CODE,
FREIGHT_TERMS_CODE,
FOB_CODE,
FOB_LOCATION_ID,
WAYBILL,
DOCK_CODE,
ACCEPTANCE_FLAG,
ACCEPTED_BY,
ACCEPTED_DATE,
ACKNOWLEDGED_BY,
CONFIRMED_BY,
CONFIRM_DATE,
ASN_DATE_SENT,
ASN_STATUS_CODE,
ASN_SEQ_NUMBER,
GROSS_WEIGHT,
NET_WEIGHT,
WEIGHT_UOM_CODE,
VOLUME,
VOLUME_UOM_CODE,
ADDITIONAL_SHIPMENT_INFO,
CURRENCY_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
TP_ATTRIBUTE_CATEGORY,
TP_ATTRIBUTE1,
TP_ATTRIBUTE2,
TP_ATTRIBUTE3,
TP_ATTRIBUTE4,
TP_ATTRIBUTE5,
TP_ATTRIBUTE6,
TP_ATTRIBUTE7,
TP_ATTRIBUTE8,
TP_ATTRIBUTE9,
TP_ATTRIBUTE10,
TP_ATTRIBUTE11,
TP_ATTRIBUTE12,
TP_ATTRIBUTE13,
TP_ATTRIBUTE14,
TP_ATTRIBUTE15,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
BATCH_ID,
HASH_VALUE,
SOURCE_HEADER_ID,
NUMBER_OF_LPN,
COD_AMOUNT,
COD_CURRENCY_CODE,
COD_REMIT_TO,
COD_CHARGE_PAID_BY,
PROBLEM_CONTACT_REFERENCE,
PORT_OF_LOADING,
PORT_OF_DISCHARGE,
FTZ_NUMBER,
ROUTED_EXPORT_TXN,
ENTRY_NUMBER,
ROUTING_INSTRUCTIONS,
IN_BOND_CODE,
SHIPPING_MARKS,
SERVICE_LEVEL,
MODE_OF_TRANSPORT,
ASSIGNED_TO_FTE_TRIPS,
AUTO_SC_EXCLUDE_FLAG,
AUTO_AP_EXCLUDE_FLAG,
AP_BATCH_ID,
SHIPMENT_DIRECTION,
VENDOR_ID,
PARTY_ID,
ROUTING_RESPONSE_ID,
RCV_SHIPMENT_HEADER_ID,
ASN_SHIPMENT_HEADER_ID,
SHIPPING_CONTROL,
TP_DELIVERY_NUMBER,
EARLIEST_PICKUP_DATE,
LATEST_PICKUP_DATE,
EARLIEST_DROPOFF_DATE,
LATEST_DROPOFF_DATE,
IGNORE_FOR_PLANNING,
TP_PLAN_NAME,
HASH_STRING,
DELIVERED_DATE,
-- bug 3667348
REASON_OF_TRANSPORT,
DESCRIPTION,
-- bug 3667348
ITINERARY_COMPLETE,
--OTM R12
TMS_INTERFACE_FLAG,
TMS_VERSION_NUMBER
--
)
SELECT
l_delivery_id,
--DECODE(p_delivery_rec.NAME,NULL,WND.NAME,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.NAME),
DECODE(l_delivery_NAME,NULL,WND.NAME,FND_API.G_MISS_CHAR,NULL,l_delivery_NAME),
DECODE(p_delivery_rec.PLANNED_FLAG,NULL,WND.PLANNED_FLAG,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.PLANNED_FLAG),
DECODE(p_delivery_rec.STATUS_CODE,NULL,WND.STATUS_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.STATUS_CODE),
DECODE(p_delivery_rec.DELIVERY_TYPE,NULL,WND.DELIVERY_TYPE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.DELIVERY_TYPE),
DECODE(p_delivery_rec.LOADING_SEQUENCE,NULL,WND.LOADING_SEQUENCE,FND_API.G_MISS_NUM,NULL,p_delivery_rec.LOADING_SEQUENCE),
DECODE(p_delivery_rec.LOADING_ORDER_FLAG,NULL,WND.LOADING_ORDER_FLAG,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.LOADING_ORDER_FLAG),
DECODE(p_delivery_rec.INITIAL_PICKUP_DATE,NULL,WND.INITIAL_PICKUP_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.INITIAL_PICKUP_DATE),
DECODE(p_delivery_rec.INITIAL_PICKUP_LOCATION_ID,NULL,WND.INITIAL_PICKUP_LOCATION_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.INITIAL_PICKUP_LOCATION_ID),
DECODE(p_delivery_rec.ORGANIZATION_ID,NULL,WND.ORGANIZATION_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.ORGANIZATION_ID),
DECODE(p_delivery_rec.ULTIMATE_DROPOFF_LOCATION_ID,NULL,WND.ULTIMATE_DROPOFF_LOCATION_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.ULTIMATE_DROPOFF_LOCATION_ID),
DECODE(p_delivery_rec.ULTIMATE_DROPOFF_DATE,NULL,WND.ULTIMATE_DROPOFF_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.ULTIMATE_DROPOFF_DATE),
DECODE(p_delivery_rec.CUSTOMER_ID,NULL,WND.CUSTOMER_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.CUSTOMER_ID),
DECODE(p_delivery_rec.INTMED_SHIP_TO_LOCATION_ID,NULL,WND.INTMED_SHIP_TO_LOCATION_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.INTMED_SHIP_TO_LOCATION_ID),
DECODE(p_delivery_rec.POOLED_SHIP_TO_LOCATION_ID,NULL,WND.POOLED_SHIP_TO_LOCATION_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.POOLED_SHIP_TO_LOCATION_ID),
DECODE(p_delivery_rec.CARRIER_ID,NULL,WND.CARRIER_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.CARRIER_ID),
DECODE(p_delivery_rec.SHIP_METHOD_CODE,NULL,WND.SHIP_METHOD_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.SHIP_METHOD_CODE),
DECODE(p_delivery_rec.FREIGHT_TERMS_CODE,NULL,WND.FREIGHT_TERMS_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.FREIGHT_TERMS_CODE),
DECODE(p_delivery_rec.FOB_CODE,NULL,WND.FOB_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.FOB_CODE),
DECODE(p_delivery_rec.FOB_LOCATION_ID,NULL,WND.FOB_LOCATION_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.FOB_LOCATION_ID),
DECODE(p_delivery_rec.WAYBILL,NULL,WND.WAYBILL,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.WAYBILL),
DECODE(p_delivery_rec.DOCK_CODE,NULL,WND.DOCK_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.DOCK_CODE),
DECODE(p_delivery_rec.ACCEPTANCE_FLAG,NULL,WND.ACCEPTANCE_FLAG,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ACCEPTANCE_FLAG),
DECODE(p_delivery_rec.ACCEPTED_BY,NULL,WND.ACCEPTED_BY,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ACCEPTED_BY),
DECODE(p_delivery_rec.ACCEPTED_DATE,NULL,WND.ACCEPTED_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.ACCEPTED_DATE),
DECODE(p_delivery_rec.ACKNOWLEDGED_BY,NULL,WND.ACKNOWLEDGED_BY,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ACKNOWLEDGED_BY),
DECODE(p_delivery_rec.CONFIRMED_BY,NULL,WND.CONFIRMED_BY,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.CONFIRMED_BY),
DECODE(p_delivery_rec.CONFIRM_DATE,NULL,WND.CONFIRM_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.CONFIRM_DATE),
DECODE(p_delivery_rec.ASN_DATE_SENT,NULL,WND.ASN_DATE_SENT,FND_API.G_MISS_DATE,NULL,p_delivery_rec.ASN_DATE_SENT),
DECODE(p_delivery_rec.ASN_STATUS_CODE,NULL,WND.ASN_STATUS_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ASN_STATUS_CODE),
DECODE(p_delivery_rec.ASN_SEQ_NUMBER,NULL,WND.ASN_SEQ_NUMBER,FND_API.G_MISS_NUM,NULL,p_delivery_rec.ASN_SEQ_NUMBER),
DECODE(p_delivery_rec.GROSS_WEIGHT,NULL,WND.GROSS_WEIGHT,FND_API.G_MISS_NUM,NULL,p_delivery_rec.GROSS_WEIGHT),
DECODE(p_delivery_rec.NET_WEIGHT,NULL,WND.NET_WEIGHT,FND_API.G_MISS_NUM,NULL,p_delivery_rec.NET_WEIGHT),
DECODE(p_delivery_rec.WEIGHT_UOM_CODE,NULL,WND.WEIGHT_UOM_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.WEIGHT_UOM_CODE),
DECODE(p_delivery_rec.VOLUME,NULL,WND.VOLUME,FND_API.G_MISS_NUM,NULL,p_delivery_rec.VOLUME),
DECODE(p_delivery_rec.VOLUME_UOM_CODE,NULL,WND.VOLUME_UOM_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.VOLUME_UOM_CODE),
DECODE(p_delivery_rec.ADDITIONAL_SHIPMENT_INFO,NULL,WND.ADDITIONAL_SHIPMENT_INFO,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ADDITIONAL_SHIPMENT_INFO),
DECODE(p_delivery_rec.CURRENCY_CODE,NULL,WND.CURRENCY_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.CURRENCY_CODE),
DECODE(p_delivery_rec.ATTRIBUTE_CATEGORY,NULL,WND.ATTRIBUTE_CATEGORY,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE_CATEGORY),
DECODE(p_delivery_rec.ATTRIBUTE1,NULL,WND.ATTRIBUTE1,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE1),
DECODE(p_delivery_rec.ATTRIBUTE2,NULL,WND.ATTRIBUTE2,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE2),
DECODE(p_delivery_rec.ATTRIBUTE3,NULL,WND.ATTRIBUTE3,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE3),
DECODE(p_delivery_rec.ATTRIBUTE4,NULL,WND.ATTRIBUTE4,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE4),
DECODE(p_delivery_rec.ATTRIBUTE5,NULL,WND.ATTRIBUTE5,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE5),
DECODE(p_delivery_rec.ATTRIBUTE6,NULL,WND.ATTRIBUTE6,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE6),
DECODE(p_delivery_rec.ATTRIBUTE7,NULL,WND.ATTRIBUTE7,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE7),
DECODE(p_delivery_rec.ATTRIBUTE8,NULL,WND.ATTRIBUTE8,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE8),
DECODE(p_delivery_rec.ATTRIBUTE9,NULL,WND.ATTRIBUTE9,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE9),
DECODE(p_delivery_rec.ATTRIBUTE10,NULL,WND.ATTRIBUTE10,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE10),
DECODE(p_delivery_rec.ATTRIBUTE11,NULL,WND.ATTRIBUTE11,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE11),
DECODE(p_delivery_rec.ATTRIBUTE12,NULL,WND.ATTRIBUTE12,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE12),
DECODE(p_delivery_rec.ATTRIBUTE13,NULL,WND.ATTRIBUTE13,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE13),
DECODE(p_delivery_rec.ATTRIBUTE14,NULL,WND.ATTRIBUTE14,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE14),
DECODE(p_delivery_rec.ATTRIBUTE15,NULL,WND.ATTRIBUTE15,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE15),
DECODE(p_delivery_rec.TP_ATTRIBUTE_CATEGORY,NULL,WND.TP_ATTRIBUTE_CATEGORY,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE_CATEGORY),
DECODE(p_delivery_rec.TP_ATTRIBUTE1,NULL,WND.TP_ATTRIBUTE1,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE1),
DECODE(p_delivery_rec.TP_ATTRIBUTE2,NULL,WND.TP_ATTRIBUTE2,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE2),
DECODE(p_delivery_rec.TP_ATTRIBUTE3,NULL,WND.TP_ATTRIBUTE3,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE3),
DECODE(p_delivery_rec.TP_ATTRIBUTE4,NULL,WND.TP_ATTRIBUTE4,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE4),
DECODE(p_delivery_rec.TP_ATTRIBUTE5,NULL,WND.TP_ATTRIBUTE5,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE5),
DECODE(p_delivery_rec.TP_ATTRIBUTE6,NULL,WND.TP_ATTRIBUTE6,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE6),
DECODE(p_delivery_rec.TP_ATTRIBUTE7,NULL,WND.TP_ATTRIBUTE7,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE7),
DECODE(p_delivery_rec.TP_ATTRIBUTE8,NULL,WND.TP_ATTRIBUTE8,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE8),
DECODE(p_delivery_rec.TP_ATTRIBUTE9,NULL,WND.TP_ATTRIBUTE9,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE9),
DECODE(p_delivery_rec.TP_ATTRIBUTE10,NULL,WND.TP_ATTRIBUTE10,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE10),
DECODE(p_delivery_rec.TP_ATTRIBUTE11,NULL,WND.TP_ATTRIBUTE11,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE11),
DECODE(p_delivery_rec.TP_ATTRIBUTE12,NULL,WND.TP_ATTRIBUTE12,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE12),
DECODE(p_delivery_rec.TP_ATTRIBUTE13,NULL,WND.TP_ATTRIBUTE13,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE13),
DECODE(p_delivery_rec.TP_ATTRIBUTE14,NULL,WND.TP_ATTRIBUTE14,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE14),
DECODE(p_delivery_rec.TP_ATTRIBUTE15,NULL,WND.TP_ATTRIBUTE15,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE15),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE_CATEGORY,NULL,WND.GLOBAL_ATTRIBUTE_CATEGORY,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE_CATEGORY),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE1,NULL,WND.GLOBAL_ATTRIBUTE1,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE1),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE2,NULL,WND.GLOBAL_ATTRIBUTE2,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE2),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE3,NULL,WND.GLOBAL_ATTRIBUTE3,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE3),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE4,NULL,WND.GLOBAL_ATTRIBUTE4,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE4),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE5,NULL,WND.GLOBAL_ATTRIBUTE5,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE5),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE6,NULL,WND.GLOBAL_ATTRIBUTE6,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE6),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE7,NULL,WND.GLOBAL_ATTRIBUTE7,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE7),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE8,NULL,WND.GLOBAL_ATTRIBUTE8,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE8),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE9,NULL,WND.GLOBAL_ATTRIBUTE9,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE9),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE10,NULL,WND.GLOBAL_ATTRIBUTE10,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE10),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE11,NULL,WND.GLOBAL_ATTRIBUTE11,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE11),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE12,NULL,WND.GLOBAL_ATTRIBUTE12,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE12),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE13,NULL,WND.GLOBAL_ATTRIBUTE13,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE13),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE14,NULL,WND.GLOBAL_ATTRIBUTE14,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE14),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE15,NULL,WND.GLOBAL_ATTRIBUTE15,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE15),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE16,NULL,WND.GLOBAL_ATTRIBUTE16,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE16),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE17,NULL,WND.GLOBAL_ATTRIBUTE17,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE17),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE18,NULL,WND.GLOBAL_ATTRIBUTE18,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE18),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE19,NULL,WND.GLOBAL_ATTRIBUTE19,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE19),
DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE20,NULL,WND.GLOBAL_ATTRIBUTE20,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE20),
DECODE(p_delivery_rec.CREATION_DATE,NULL,WND.CREATION_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.CREATION_DATE),
DECODE(p_delivery_rec.CREATED_BY,NULL,WND.CREATED_BY,FND_API.G_MISS_NUM,NULL,p_delivery_rec.CREATED_BY),
DECODE(p_delivery_rec.LAST_UPDATE_DATE,NULL,WND.LAST_UPDATE_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.LAST_UPDATE_DATE),
DECODE(p_delivery_rec.LAST_UPDATED_BY,NULL,WND.LAST_UPDATED_BY,FND_API.G_MISS_NUM,NULL,p_delivery_rec.LAST_UPDATED_BY),
DECODE(p_delivery_rec.LAST_UPDATE_LOGIN,NULL,WND.LAST_UPDATE_LOGIN,FND_API.G_MISS_NUM,NULL,p_delivery_rec.LAST_UPDATE_LOGIN),
DECODE(p_delivery_rec.PROGRAM_APPLICATION_ID,NULL,WND.PROGRAM_APPLICATION_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.PROGRAM_APPLICATION_ID),
DECODE(p_delivery_rec.PROGRAM_ID,NULL,WND.PROGRAM_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.PROGRAM_ID),
DECODE(p_delivery_rec.PROGRAM_UPDATE_DATE,NULL,WND.PROGRAM_UPDATE_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.PROGRAM_UPDATE_DATE),
DECODE(p_delivery_rec.REQUEST_ID,NULL,WND.REQUEST_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.REQUEST_ID),
DECODE(p_delivery_rec.BATCH_ID,NULL,WND.BATCH_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.BATCH_ID),
DECODE(p_delivery_rec.HASH_VALUE,NULL,WND.HASH_VALUE,FND_API.G_MISS_NUM,NULL,p_delivery_rec.HASH_VALUE),
DECODE(p_delivery_rec.SOURCE_HEADER_ID,NULL,WND.SOURCE_HEADER_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.SOURCE_HEADER_ID),
DECODE(p_delivery_rec.NUMBER_OF_LPN,NULL,WND.NUMBER_OF_LPN,FND_API.G_MISS_NUM,NULL,p_delivery_rec.NUMBER_OF_LPN),
DECODE(p_delivery_rec.COD_AMOUNT,NULL,WND.COD_AMOUNT,FND_API.G_MISS_NUM,NULL,p_delivery_rec.COD_AMOUNT),
DECODE(p_delivery_rec.COD_CURRENCY_CODE,NULL,WND.COD_CURRENCY_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.COD_CURRENCY_CODE),
DECODE(p_delivery_rec.COD_REMIT_TO,NULL,WND.COD_REMIT_TO,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.COD_REMIT_TO),
DECODE(p_delivery_rec.COD_CHARGE_PAID_BY,NULL,WND.COD_CHARGE_PAID_BY,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.COD_CHARGE_PAID_BY),
DECODE(p_delivery_rec.PROBLEM_CONTACT_REFERENCE,NULL,WND.PROBLEM_CONTACT_REFERENCE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.PROBLEM_CONTACT_REFERENCE),
DECODE(p_delivery_rec.PORT_OF_LOADING,NULL,WND.PORT_OF_LOADING,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.PORT_OF_LOADING),
DECODE(p_delivery_rec.PORT_OF_DISCHARGE,NULL,WND.PORT_OF_DISCHARGE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.PORT_OF_DISCHARGE),
DECODE(p_delivery_rec.FTZ_NUMBER,NULL,WND.FTZ_NUMBER,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.FTZ_NUMBER),
DECODE(p_delivery_rec.ROUTED_EXPORT_TXN,NULL,WND.ROUTED_EXPORT_TXN,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ROUTED_EXPORT_TXN),
DECODE(p_delivery_rec.ENTRY_NUMBER,NULL,WND.ENTRY_NUMBER,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ENTRY_NUMBER),
DECODE(p_delivery_rec.ROUTING_INSTRUCTIONS,NULL,WND.ROUTING_INSTRUCTIONS,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ROUTING_INSTRUCTIONS),
DECODE(p_delivery_rec.IN_BOND_CODE,NULL,WND.IN_BOND_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.IN_BOND_CODE),
DECODE(p_delivery_rec.SHIPPING_MARKS,NULL,WND.SHIPPING_MARKS,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.SHIPPING_MARKS),
DECODE(p_delivery_rec.SERVICE_LEVEL,NULL,WND.SERVICE_LEVEL,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.SERVICE_LEVEL),
DECODE(p_delivery_rec.MODE_OF_TRANSPORT,NULL,WND.MODE_OF_TRANSPORT,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.MODE_OF_TRANSPORT),
DECODE(p_delivery_rec.ASSIGNED_TO_FTE_TRIPS,NULL,WND.ASSIGNED_TO_FTE_TRIPS,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ASSIGNED_TO_FTE_TRIPS),
DECODE(p_delivery_rec.AUTO_SC_EXCLUDE_FLAG,NULL,WND.AUTO_SC_EXCLUDE_FLAG,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.AUTO_SC_EXCLUDE_FLAG),
DECODE(p_delivery_rec.AUTO_AP_EXCLUDE_FLAG,NULL,WND.AUTO_AP_EXCLUDE_FLAG,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.AUTO_AP_EXCLUDE_FLAG),
DECODE(p_delivery_rec.AP_BATCH_ID,NULL,WND.AP_BATCH_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.AP_BATCH_ID),
DECODE(p_delivery_rec.SHIPMENT_DIRECTION,NULL,WND.SHIPMENT_DIRECTION,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.SHIPMENT_DIRECTION),
DECODE(p_delivery_rec.VENDOR_ID,NULL,WND.VENDOR_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.VENDOR_ID),
DECODE(p_delivery_rec.PARTY_ID,NULL,WND.PARTY_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.PARTY_ID),
DECODE(p_delivery_rec.ROUTING_RESPONSE_ID,NULL,WND.ROUTING_RESPONSE_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.ROUTING_RESPONSE_ID),
DECODE(p_delivery_rec.RCV_SHIPMENT_HEADER_ID,NULL,WND.RCV_SHIPMENT_HEADER_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.RCV_SHIPMENT_HEADER_ID),
DECODE(p_delivery_rec.ASN_SHIPMENT_HEADER_ID,NULL,WND.ASN_SHIPMENT_HEADER_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.ASN_SHIPMENT_HEADER_ID),
DECODE(p_delivery_rec.SHIPPING_CONTROL,NULL,WND.SHIPPING_CONTROL,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.SHIPPING_CONTROL),
DECODE(p_delivery_rec.TP_DELIVERY_NUMBER,NULL,WND.TP_DELIVERY_NUMBER,FND_API.G_MISS_NUM,NULL,p_delivery_rec.TP_DELIVERY_NUMBER),
DECODE(p_delivery_rec.EARLIEST_PICKUP_DATE,NULL,WND.EARLIEST_PICKUP_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.EARLIEST_PICKUP_DATE),
DECODE(p_delivery_rec.LATEST_PICKUP_DATE,NULL,WND.LATEST_PICKUP_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.LATEST_PICKUP_DATE),
DECODE(p_delivery_rec.EARLIEST_DROPOFF_DATE,NULL,WND.EARLIEST_DROPOFF_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.EARLIEST_DROPOFF_DATE),
DECODE(p_delivery_rec.LATEST_DROPOFF_DATE,NULL,WND.LATEST_DROPOFF_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.LATEST_DROPOFF_DATE),
DECODE(p_delivery_rec.IGNORE_FOR_PLANNING,NULL,WND.IGNORE_FOR_PLANNING,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.IGNORE_FOR_PLANNING),
DECODE(p_delivery_rec.TP_PLAN_NAME,NULL,WND.TP_PLAN_NAME,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_PLAN_NAME),
DECODE(p_delivery_rec.hash_string,NULL,WND.hash_string,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.hash_string),
DECODE(p_delivery_rec.delivered_date,NULL,WND.delivered_date,FND_API.G_MISS_DATE,NULL,p_delivery_rec.delivered_date),
-- bug 3667348
DECODE(p_delivery_rec.REASON_OF_TRANSPORT,NULL,WND.REASON_OF_TRANSPORT,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.REASON_OF_TRANSPORT),
DECODE(p_delivery_rec.DESCRIPTION,NULL,WND.DESCRIPTION,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.DESCRIPTION),
DECODE(p_copy_legs, 'Y', WND.ITINERARY_COMPLETE, 'N'),
-- bug 3667348
--OTM R12
DECODE(p_delivery_rec.TMS_INTERFACE_FLAG,NULL,WND.TMS_INTERFACE_FLAG,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TMS_INTERFACE_FLAG),
DECODE(p_delivery_rec.TMS_VERSION_NUMBER,NULL,WND.TMS_VERSION_NUMBER,FND_API.G_MISS_NUM,NULL,p_delivery_rec.TMS_VERSION_NUMBER)
--
FROM WSH_NEW_DELIVERIES WND
WHERE delivery_id = p_delivery_id;
INSERT INTO wsh_delivery_legs
(
DELIVERY_LEG_ID,
DELIVERY_ID,
SEQUENCE_NUMBER,
LOADING_ORDER_FLAG,
PICK_UP_STOP_ID,
DROP_OFF_STOP_ID,
GROSS_WEIGHT,
NET_WEIGHT,
WEIGHT_UOM_CODE,
VOLUME,
VOLUME_UOM_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
LOAD_TENDER_STATUS,
SHIPPER_TITLE,
SHIPPER_PHONE,
POD_FLAG,
POD_BY,
POD_DATE,
EXPECTED_POD_DATE,
BOOKING_OFFICE,
SHIPPER_EXPORT_REF,
CARRIER_EXPORT_REF,
DOC_NOTIFY_PARTY,
AETC_NUMBER,
SHIPPER_SIGNED_BY,
SHIPPER_DATE,
CARRIER_SIGNED_BY,
CARRIER_DATE,
DOC_ISSUE_OFFICE,
DOC_ISSUED_BY,
DOC_DATE_ISSUED,
SHIPPER_HM_BY,
SHIPPER_HM_DATE,
CARRIER_HM_BY,
CARRIER_HM_DATE,
BOOKING_NUMBER,
PORT_OF_LOADING,
PORT_OF_DISCHARGE,
SERVICE_CONTRACT,
BILL_FREIGHT_TO,
FTE_TRIP_ID,
REPRICE_REQUIRED,
ACTUAL_ARRIVAL_DATE,
ACTUAL_DEPARTURE_DATE,
ACTUAL_RECEIPT_DATE,
TRACKING_DRILLDOWN_FLAG,
STATUS_CODE,
TRACKING_REMARKS,
CARRIER_EST_DEPARTURE_DATE,
CARRIER_EST_ARRIVAL_DATE,
LOADING_START_DATETIME,
LOADING_END_DATETIME,
UNLOADING_START_DATETIME,
UNLOADING_END_DATETIME,
DELIVERED_QUANTITY,
LOADED_QUANTITY,
RECEIVED_QUANTITY,
ORIGIN_STOP_ID,
DESTINATION_STOP_ID
)
SELECT
wsh_delivery_legs_s.NEXTVAL,
l_delivery_id,
SEQUENCE_NUMBER,
LOADING_ORDER_FLAG,
PICK_UP_STOP_ID,
DROP_OFF_STOP_ID,
GROSS_WEIGHT,
NET_WEIGHT,
WEIGHT_UOM_CODE,
VOLUME,
VOLUME_UOM_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
LOAD_TENDER_STATUS,
SHIPPER_TITLE,
SHIPPER_PHONE,
POD_FLAG,
POD_BY,
POD_DATE,
EXPECTED_POD_DATE,
BOOKING_OFFICE,
SHIPPER_EXPORT_REF,
CARRIER_EXPORT_REF,
DOC_NOTIFY_PARTY,
AETC_NUMBER,
SHIPPER_SIGNED_BY,
SHIPPER_DATE,
CARRIER_SIGNED_BY,
CARRIER_DATE,
DOC_ISSUE_OFFICE,
DOC_ISSUED_BY,
DOC_DATE_ISSUED,
SHIPPER_HM_BY,
SHIPPER_HM_DATE,
CARRIER_HM_BY,
CARRIER_HM_DATE,
BOOKING_NUMBER,
PORT_OF_LOADING,
PORT_OF_DISCHARGE,
SERVICE_CONTRACT,
BILL_FREIGHT_TO,
FTE_TRIP_ID,
REPRICE_REQUIRED,
ACTUAL_ARRIVAL_DATE,
ACTUAL_DEPARTURE_DATE,
ACTUAL_RECEIPT_DATE,
TRACKING_DRILLDOWN_FLAG,
STATUS_CODE,
TRACKING_REMARKS,
CARRIER_EST_DEPARTURE_DATE,
CARRIER_EST_ARRIVAL_DATE,
LOADING_START_DATETIME,
LOADING_END_DATETIME,
UNLOADING_START_DATETIME,
UNLOADING_END_DATETIME,
DELIVERED_QUANTITY,
LOADED_QUANTITY,
RECEIVED_QUANTITY,
ORIGIN_STOP_ID,
DESTINATION_STOP_ID
FROM WSH_DELIVERY_LEGS
WHERE DELIVERY_ID = p_delivery_id;
WSH_DEBUG_SV.logmsg(l_module_name, 'Inserted ' || SQL%ROWCOUNT || ' Legs' );
SELECT DELIVERY_ID
,NAME
,PLANNED_FLAG
,STATUS_CODE
,DELIVERY_TYPE
,LOADING_SEQUENCE
,LOADING_ORDER_FLAG
,INITIAL_PICKUP_DATE
,INITIAL_PICKUP_LOCATION_ID
,ORGANIZATION_ID
,ULTIMATE_DROPOFF_LOCATION_ID
,ULTIMATE_DROPOFF_DATE
,CUSTOMER_ID
,INTMED_SHIP_TO_LOCATION_ID
,POOLED_SHIP_TO_LOCATION_ID
,CARRIER_ID
,SHIP_METHOD_CODE
,FREIGHT_TERMS_CODE
,FOB_CODE
,FOB_LOCATION_ID
,WAYBILL
,DOCK_CODE
,ACCEPTANCE_FLAG
,ACCEPTED_BY
,ACCEPTED_DATE
,ACKNOWLEDGED_BY
,CONFIRMED_BY
,CONFIRM_DATE
,ASN_DATE_SENT
,ASN_STATUS_CODE
,ASN_SEQ_NUMBER
,GROSS_WEIGHT
,NET_WEIGHT
,WEIGHT_UOM_CODE
,VOLUME
,VOLUME_UOM_CODE
,ADDITIONAL_SHIPMENT_INFO
,CURRENCY_CODE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,TP_ATTRIBUTE_CATEGORY
,TP_ATTRIBUTE1
,TP_ATTRIBUTE2
,TP_ATTRIBUTE3
,TP_ATTRIBUTE4
,TP_ATTRIBUTE5
,TP_ATTRIBUTE6
,TP_ATTRIBUTE7
,TP_ATTRIBUTE8
,TP_ATTRIBUTE9
,TP_ATTRIBUTE10
,TP_ATTRIBUTE11
,TP_ATTRIBUTE12
,TP_ATTRIBUTE13
,TP_ATTRIBUTE14
,TP_ATTRIBUTE15
,GLOBAL_ATTRIBUTE_CATEGORY
,GLOBAL_ATTRIBUTE1
,GLOBAL_ATTRIBUTE2
,GLOBAL_ATTRIBUTE3
,GLOBAL_ATTRIBUTE4
,GLOBAL_ATTRIBUTE5
,GLOBAL_ATTRIBUTE6
,GLOBAL_ATTRIBUTE7
,GLOBAL_ATTRIBUTE8
,GLOBAL_ATTRIBUTE9
,GLOBAL_ATTRIBUTE10
,GLOBAL_ATTRIBUTE11
,GLOBAL_ATTRIBUTE12
,GLOBAL_ATTRIBUTE13
,GLOBAL_ATTRIBUTE14
,GLOBAL_ATTRIBUTE15
,GLOBAL_ATTRIBUTE16
,GLOBAL_ATTRIBUTE17
,GLOBAL_ATTRIBUTE18
,GLOBAL_ATTRIBUTE19
,GLOBAL_ATTRIBUTE20
,CREATION_DATE
,CREATED_BY
,sysdate
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,BATCH_ID
,HASH_VALUE
,SOURCE_HEADER_ID
,NUMBER_OF_LPN
,COD_AMOUNT
,COD_CURRENCY_CODE
,COD_REMIT_TO
,COD_CHARGE_PAID_BY
,PROBLEM_CONTACT_REFERENCE
,PORT_OF_LOADING
,PORT_OF_DISCHARGE
,FTZ_NUMBER
,ROUTED_EXPORT_TXN
,ENTRY_NUMBER
,ROUTING_INSTRUCTIONS
,IN_BOND_CODE
,SHIPPING_MARKS
,SERVICE_LEVEL
,MODE_OF_TRANSPORT
,ASSIGNED_TO_FTE_TRIPS
,AUTO_SC_EXCLUDE_FLAG
,AUTO_AP_EXCLUDE_FLAG
,AP_BATCH_ID
-- The following are non database columns in the rec. structure.
,NULL -- ROWID
,NULL -- LOADING_ORDER_DESC
,NULL -- ORGANIZATION_CODE
,NULL -- ULTIMATE_DROPOFF_LOCATION_CODE
,NULL -- INITIAL_PICKUP_LOCATION_CODE
,NULL -- CUSTOMER_NUMBER
,NULL -- INTMED_SHIP_TO_LOCATION_CODE
,NULL -- POOLED_SHIP_TO_LOCATION_CODE
,NULL -- CARRIER_CODE
,NULL -- SHIP_METHOD_NAME
,NULL -- FREIGHT_TERMS_NAME
,NULL -- FOB_NAME
,NULL -- FOB_LOCATION_CODE
,NULL -- WEIGHT_UOM_DESC
,NULL -- VOLUME_UOM_DESC
,NULL -- CURRENCY_NAME
-- End non database columns in the rec. structure.
,SHIPMENT_DIRECTION
,VENDOR_ID
,PARTY_ID
,ROUTING_RESPONSE_ID
,RCV_SHIPMENT_HEADER_ID
,ASN_SHIPMENT_HEADER_ID
,SHIPPING_CONTROL
,TP_DELIVERY_NUMBER
,EARLIEST_PICKUP_DATE
,LATEST_PICKUP_DATE
,EARLIEST_DROPOFF_DATE
,LATEST_DROPOFF_DATE
,nvl(IGNORE_FOR_PLANNING, 'N')
,TP_PLAN_NAME
,WV_FROZEN_FLAG
,HASH_STRING
,DELIVERED_DATE
-- Non database column
,NULL -- packing_slip
-- bug 3667348
,REASON_OF_TRANSPORT
,DESCRIPTION
-- bug 3667348
,'N'--Non Database field added for "Proration of weight from Delivery to delivery lines" Project(Bug#4254552).
--OTM R12
,TMS_INTERFACE_FLAG
,TMS_VERSION_NUMBER
--
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
PROCEDURE UPDATE_TMS_INTERFACE_FLAG
(p_delivery_id_tab IN WSH_UTIL_CORE.ID_TAB_TYPE,
p_tms_interface_flag_tab IN WSH_UTIL_CORE.COLUMN_TAB_TYPE,
x_return_status OUT NOCOPY VARCHAR2) IS
l_num_error NUMBER;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_TMS_INTERFACE_FLAG';
SAVEPOINT tms_update;
WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED
AND l_delivery_info.tms_interface_flag IN
(WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED,
WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)) THEN
--set to NS if previous flag is CR or NS and new flag is DR, CP might already be sent so set to DR
l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT;
ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED
AND l_delivery_info.tms_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS) THEN
--DP stays in DP
l_new_tms_interface_flag_tab(l_count) := l_delivery_info.tms_interface_flag;
WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED,
WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED)
OR p_tms_interface_flag_tab(i) = l_delivery_info.tms_interface_flag) THEN
--all updates that does not change tms interface flag or are changing to anything besides UR CR DR,
--do not increment the version
l_new_tms_interface_flag_tab(l_count) := p_tms_interface_flag_tab(i);
UPDATE wsh_new_deliveries
SET
TMS_VERSION_NUMBER = l_new_tms_version_number_tab(j)
,TMS_INTERFACE_FLAG = l_new_tms_interface_flag_tab(j)
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.USER_ID
,last_update_login = FND_GLOBAL.LOGIN_ID
WHERE DELIVERY_ID = l_delivery_id_tab(j);
WSH_DEBUG_SV.log(l_module_name,'Rows updated',SQL%ROWCOUNT);
ROLLBACK TO tms_update;
ROLLBACK TO tms_update;
ROLLBACK TO tms_update;
WSH_DEBUG_SV.logmsg(l_module_name,'Record_locked exception has occured. Cannot update delivery tms_interface_flag', WSH_DEBUG_SV.C_EXCEP_LEVEL);
ROLLBACK TO tms_update;
wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG',l_module_name);
END UPDATE_TMS_INTERFACE_FLAG;