[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
wddi.FREIGHT_CLASS_CAT_CODE,
wddi.HAZARD_CLASS_CODE,
wddi.INTMED_SHIP_TO_LOCATION_CODE,
wddi.ITEM_NUMBER,
wddi.LOCATOR_CODE,
wddi.MASTER_CONTAINER_ITEM_NUMBER,
wddi.ORGANIZATION_CODE,
wddi.SHIP_FROM_LOCATION_CODE,
wddi.SHIP_TO_LOCATION_CODE,
wddi.PROJECT_ID,
wddi.SEAL_CODE,
wddi.SHIP_TO_SITE_USE_ID,
wddi.SHIPPING_INSTRUCTIONS,
wddi.SOURCE_LINE_NUMBER,
wddi.TO_SERIAL_NUMBER,
wddi.TRACKING_NUMBER,
wddi.UNIT_NUMBER,
wddi.FILL_PERCENT,
wddi.FREIGHT_CLASS_CAT_ID,
wddi.INSPECTION_FLAG,
wddi.LPN_CONTENT_ID,
wddi.LPN_ID,
wddi.MASTER_SERIAL_NUMBER,
wddi.MAXIMUM_LOAD_WEIGHT,
wddi.MAXIMUM_VOLUME,
wddi.MINIMUM_FILL_PERCENT,
wddi.UNIT_PRICE,
wddi.COMMODITY_CODE_CAT_ID,
wddi.TP_ATTRIBUTE9,
wddi.TP_ATTRIBUTE10,
wddi.TP_ATTRIBUTE11,
wddi.TP_ATTRIBUTE12,
wddi.TP_ATTRIBUTE13,
wddi.TP_ATTRIBUTE14,
wddi.TP_ATTRIBUTE15,
wddi.ATTRIBUTE_CATEGORY,
wddi.ATTRIBUTE1,
wddi.ATTRIBUTE2,
wddi.ATTRIBUTE3,
wddi.ATTRIBUTE4,
wddi.ATTRIBUTE5,
wddi.ATTRIBUTE6,
wddi.ATTRIBUTE7,
wddi.ATTRIBUTE8,
wddi.ATTRIBUTE9,
wddi.ATTRIBUTE10,
wddi.ATTRIBUTE11,
wddi.ATTRIBUTE12,
wddi.ATTRIBUTE13,
wddi.ATTRIBUTE14,
wddi.ATTRIBUTE15,
wddi.CREATION_DATE,
wddi.CREATED_BY,
wddi.LAST_UPDATE_DATE,
wddi.LAST_UPDATED_BY,
wddi.LAST_UPDATE_LOGIN,
wddi.PROGRAM_APPLICATION_ID,
wddi.PROGRAM_ID,
wddi.PROGRAM_UPDATE_DATE,
wddi.REQUEST_ID,
wddi.INTERFACE_ACTION_CODE,
wddi.LOCK_FLAG,
wddi.PROCESS_FLAG,
wddi.PROCESS_MODE,
wddi.DELETE_FLAG,
wddi.PROCESS_STATUS_FLAG,
wddi.SOURCE_HEADER_NUMBER,
wddi.SOURCE_HEADER_TYPE_ID,
wddi.SOURCE_HEADER_TYPE_NAME,
wddi.CUST_PO_NUMBER,
wddi.SHIP_SET_ID,
wddi.ARRIVAL_SET_ID,
wddi.TOP_MODEL_LINE_ID,
wddi.ATO_LINE_ID,
wddi.SHIP_MODEL_COMPLETE_FLAG,
wddi.HAZARD_CLASS_ID,
wddi.CLASSIFICATION,
wddi.ORGANIZATION_ID,
wddi.SRC_REQUESTED_QUANTITY,
wddi.SRC_REQUESTED_QUANTITY_UOM,
wddi.QUALITY_CONTROL_QUANTITY,
wddi.CYCLE_COUNT_QUANTITY,
wddi.MOVE_ORDER_LINE_ID,
wddi.LOCATOR_ID,
wddi.MVT_STAT_STATUS,
wddi.TRANSACTION_TEMP_ID,
wddi.PREFERRED_GRADE,
wddi.SRC_REQUESTED_QUANTITY2,
wddi.SRC_REQUESTED_QUANTITY_UOM2,
wddi.REQUESTED_QUANTITY2,
wddi.SHIPPED_QUANTITY2,
wddi.DELIVERED_QUANTITY2,
wddi.CANCELLED_QUANTITY2,
wddi.QUALITY_CONTROL_QUANTITY2,
wddi.CYCLE_COUNT_QUANTITY2,
wddi.REQUESTED_QUANTITY_UOM2,
-- HW OPMCONV - No need for sublot_number
--wddi.SUBLOT_NUMBER,
wddi.SPLIT_FROM_DELIVERY_DETAIL_ID,
wddi.CARRIER_CODE,
wddi.COMMODITY_CODE_CAT_CODE,
wddi.CUSTOMER_NUMBER,
wddi.CUSTOMER_ITEM_NUMBER,
wddi.DELIVER_TO_LOCATION_CODE,
wddi.CUSTOMER_PRODUCTION_LINE,
wddi.DELIVER_TO_SITE_USE_ID,
wddi.MOVEMENT_ID,
wddi.ORG_ID,
wddi.ORIGINAL_SUBINVENTORY,
wddi.PACKING_INSTRUCTIONS,
wddi.PICKED_QUANTITY,
wddi.PICKED_QUANTITY2,
wddi.DELIVERY_DETAIL_INTERFACE_ID,
wddi.DELIVERY_DETAIL_ID,
wddi.SOURCE_CODE,
wddi.SOURCE_HEADER_ID,
wddi.SOURCE_LINE_ID,
wddi.CUSTOMER_ID,
wddi.SOLD_TO_CONTACT_ID,
wddi.INVENTORY_ITEM_ID,
wddi.ITEM_DESCRIPTION,
wddi.COUNTRY_OF_ORIGIN,
wddi.SHIP_FROM_LOCATION_ID,
wddi.SHIP_TO_LOCATION_ID,
wddi.SHIP_TO_CONTACT_ID,
wddi.DELIVER_TO_LOCATION_ID,
wddi.DELIVER_TO_CONTACT_ID,
wddi.INTMED_SHIP_TO_LOCATION_ID,
wddi.INTMED_SHIP_TO_CONTACT_ID,
wddi.SHIP_TOLERANCE_ABOVE,
wddi.SHIP_TOLERANCE_BELOW,
wddi.REQUESTED_QUANTITY,
wddi.CANCELLED_QUANTITY,
wddi.SHIPPED_QUANTITY,
wddi.DELIVERED_QUANTITY,
wddi.REQUESTED_QUANTITY_UOM,
wddi.SHIPPING_QUANTITY_UOM,
wddi.SUBINVENTORY,
wddi.REVISION,
wddi.LOT_NUMBER,
wddi.CUSTOMER_REQUESTED_LOT_FLAG,
wddi.SERIAL_NUMBER,
wddi.DATE_REQUESTED,
wddi.DATE_SCHEDULED,
wddi.MASTER_CONTAINER_ITEM_ID,
wddi.DETAIL_CONTAINER_ITEM_ID,
wddi.LOAD_SEQ_NUMBER,
wddi.SHIP_METHOD_CODE,
wddi.CARRIER_ID,
wddi.FREIGHT_TERMS_CODE,
wddi.SHIPMENT_PRIORITY_CODE,
wddi.FOB_CODE,
wddi.CUSTOMER_ITEM_ID,
wddi.DEP_PLAN_REQUIRED_FLAG,
wddi.CUSTOMER_PROD_SEQ,
wddi.CUSTOMER_DOCK_CODE,
wddi.GROSS_WEIGHT,
wddi.NET_WEIGHT,
wddi.WEIGHT_UOM_CODE,
wddi.VOLUME,
wddi.VOLUME_UOM_CODE,
wddi.TP_ATTRIBUTE_CATEGORY,
wddi.TP_ATTRIBUTE1,
wddi.TP_ATTRIBUTE2,
wddi.TP_ATTRIBUTE3,
wddi.TP_ATTRIBUTE4,
wddi.TP_ATTRIBUTE5,
wddi.TP_ATTRIBUTE6,
wddi.TP_ATTRIBUTE7,
wddi.TP_ATTRIBUTE8,
wddi.DETAIL_CONTAINER_ITEM_CODE,
wddi.TASK_ID,
wddi.CUSTOMER_JOB,
wddi.CONTAINER_FLAG,
wddi.CONTAINER_NAME,
wddi.CONTAINER_TYPE_CODE,
wddi.CURRENCY_CODE,
wddi.CUST_MODEL_SERIAL_NUMBER,
-- J: W/V Changes
wddi.filled_volume,
wddi.wv_frozen_flag,
--Bug 3458160
wddi.LINE_DIRECTION,
wddi.REQUEST_DATE_TYPE_CODE,
wddi.EARLIEST_PICKUP_DATE ,
wddi.LATEST_PICKUP_DATE ,
wddi.EARLIEST_DROPOFF_DATE ,
wddi.LATEST_DROPOFF_DATE
FROM wsh_del_details_interface wddi,
wsh_del_assgn_interface wdai
WHERE wddi.delivery_detail_id= nvl(l_detail_id, wddi.delivery_detail_id)
AND wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
AND wdai.delivery_interface_id= l_dlvy_interface_id
AND WDDI.INTERFACE_ACTION_CODE = '94X_INBOUND'
AND WDAI.INTERFACE_ACTION_CODE = '94X_INBOUND'
ORDER BY wddi.delivery_detail_id, wddi.source_line_id;
PROCEDURE Add_To_Update_Table(
p_del_det_int_rec IN del_det_int_cur%ROWTYPE,
p_update_mode IN VARCHAR2 DEFAULT 'UPDATE',
p_delivery_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2);
PROCEDURE Update_Delivery_Details(
p_source_code IN VARCHAR2 DEFAULT 'OE',
p_delivery_interface_id IN NUMBER DEFAULT NULL,
p_action_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2);
PROCEDURE : Update_Contnr_Int_Assignments
PARAMETERS : p_parent_delivery_detail_id
p_parent_detail_interface_id
x_return_status - return status of API
DESCRIPTION :
- This procedure is called in the Inbound Map, to relate the SHIPITEM records
with the SHIPUNIT/CONTAINER records through the parent_detail_interface_id.
- This procedure updates the wsh_del_assgn_interface table.
- This takes the parent_delivery_detail_id and parent_detail_interface_id.
- For those records which have parent_delivery_detail_id is equal to the
parameter value, the parent_detail_interface_id is updated with the give
value.
------------------------------------------------------------------------------
*/
PROCEDURE Update_Contnr_Int_Assignments(
p_parent_delivery_detail_id IN NUMBER,
p_parent_detail_interface_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
--
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CONTNR_INT_ASSIGNMENTS';
wsh_debug_sv.push(l_module_name, 'Update_Contnr_Int_Assignments');
UPDATE wsh_del_assgn_interface
SET parent_detail_interface_id = p_parent_detail_interface_id
WHERE parent_delivery_detail_id = p_parent_delivery_detail_id;
END Update_Contnr_Int_Assignments;
p_action_code - Action code 'CREATE' or 'UPDATE'
x_del_detail_id - Delivery_Detail_ID of the detail created
- using Create_Shipment_Lines api
x_return_status - return status of API
DESCRIPTION :
- This procedure is used to process the delivery details in the wsh_del_details_interface
table.
- If the action is CREATE, then we take the interface record columns and call
the 'Create_Shipment_Lines' api.
- If the action is UPDATE, then we do the following:
-- Do count(*) of the records for the given delivery_detail_id (p_del_detail_id)
-- If the count=1, then we take the interface record columns and call
Update_Shipping_Attributes
-- If the count>1, then we have multiple delivery detail records in the interface
table for one record in the base table.
-- Base records need to be split before the update. So we split the base
table record based on the quantities in the interface table records
-- After every split, we call Update_Shipping_Attributes to update the
newly created base record with the corresponding interface record values
-- If the interface delivery detail is packed, then we do the following:
-- create container instance in base tables using the container inv.item
-- pack the base records into the newly created container instances.
------------------------------------------------------------------------------
*/
PROCEDURE Process_Interfaced_Del_Details(
p_delivery_interface_id IN NUMBER,
p_delivery_id IN NUMBER,
p_new_delivery_id IN NUMBER,
p_action_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
-- procedure specific variables
l_return_status VARCHAR2(30);
IF (p_action_code = 'UPDATE' and p_delivery_id IS NULL) THEN
raise invalid_input;
IF(p_action_code = 'UPDATE') THEN
Process_Splits(
p_delivery_interface_id => p_delivery_interface_id,
p_delivery_id => p_delivery_id,
x_return_status => l_return_status);
Update_Delivery_Details(
p_delivery_interface_id => p_delivery_interface_id,
p_action_code => p_action_code,
x_return_status => l_return_status);
wsh_debug_sv.log (l_module_name, 'Return Status from Update Del Details', l_return_status);
SELECT count(*), wdai.delivery_detail_id, wddi.container_flag
FROM wsh_del_assgn_interface wdai, wsh_del_details_interface wddi
WHERE wdai.delivery_interface_id = p_delivery_interface_id
AND wdai.delivery_detail_interface_id = wddi.delivery_detail_interface_id
AND WDAI.INTERFACE_ACTION_CODE = '94X_INBOUND'
AND WDDI.INTERFACE_ACTION_CODE = '94X_INBOUND'
GROUP BY wdai.delivery_detail_id, wddi.container_flag
HAVING count(*) = 1
ORDER BY wddi.container_flag desc;
SELECT count(*)
FROM wsh_delivery_details wdd, wsh_delivery_assignments_v wda
WHERE wdd.source_line_id = l_del_det_id
AND wdd.source_code = 'WSH'
AND wdd.container_flag = 'Y'
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = p_delivery_id; -- check this
SELECT decode(mtl_transactions_enabled_flag,'Y','Y','N'),
-- J: W/V Changes
unit_weight,
unit_volume
FROM mtl_system_items m
WHERE m.inventory_item_id = p_inventory_item_id
AND m.organization_id = p_organization_id;
SELECT to_number(org_information3)
FROM hr_organization_information
WHERE organization_id = p_organization_id
AND org_information_context = 'Accounting Information';
add_to_update_failed exception;
IF(p_action_code = 'UPDATE') THEN
IF (p_delivery_id IS NOT NULL) THEN
l_delivery_id := p_delivery_id;
l_new_detail_ids.delete;
/* do we need to send the who columns for create/update ??
l_del_details_info.created_by := del_det_int_rec.created_by;
l_del_details_info.last_update_date := del_det_int_rec.last_update_date;
l_del_details_info.last_update_login := del_det_int_rec.last_update_login;
l_del_details_info.program_update_date := del_det_int_rec.program_update_date;
WSH_INTERFACE_GRP.Create_Update_Delivery_Detail(
p_api_version_number => l_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_detail_info_tab => l_detail_info_tab,
p_IN_rec => l_detail_in_rec,
x_OUT_rec => l_detail_out_rec);
ELSIF(p_action_code = 'UPDATE') THEN --}{
IF l_debug_on THEN
wsh_debug_sv.logmsg(l_module_name,'Starting Update Action');
-- Just add to the global update table.
null;
-- need to update the source_line_id of the newly created container instance
UPDATE wsh_delivery_details
SET source_line_id = del_det_int_rec.delivery_detail_id
WHERE delivery_detail_id = l_cont_instance_id;
-- Need to update the record's delivery_detail id with the newly created
-- delivery_detail_id. Because this record will be sent to USA for updating
-- the newly created container instance with the data from the interface table record.
del_det_int_rec.delivery_detail_id := l_cont_instance_id;
ELSE -- not a container, plain update
null;
-- Add to the global table here. Because we need to call USA for all three update cases viz.
-- 1. newly created container instance
-- 2. existing container instance
-- 3. existing non-container delivery details
Add_To_Update_Table(
p_del_det_int_rec => del_det_int_rec,
p_update_mode => 'UPDATE',
p_delivery_id => l_delivery_id,
x_return_status => l_return_status);
wsh_debug_sv.log (l_module_name, 'Add_To_Update_Table l_return_status',l_return_status);
raise add_to_update_failed;
SELECT count(*) INTO l_det_freight_costs
FROM wsh_freight_costs_interface
WHERE delivery_detail_interface_id = del_det_int_rec.delivery_detail_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
SELECT COUNT(*)
FROM wsh_del_details_interface wddi, wsh_del_assgn_interface wdai
WHERE wddi.delivery_Detail_interface_id = wdai.delivery_detail_interface_id
AND wdai.delivery_interface_id = p_dlvy_int_id
AND wddi.delivery_detail_id=p_detail_id
AND WDAI.INTERFACE_ACTION_CODE = '94X_INBOUND'
AND WDDI.INTERFACE_ACTION_CODE = '94X_INBOUND';
add_to_update_table(p_del_det_int_rec => l_prev_int_rec,
p_update_mode => 'UPDATE', p_delivery_id => l_delivery_id,
x_return_status => l_return_status);
l_serial_range_tab.delete;
add_to_update_table(p_del_det_int_rec => l_prev_int_rec,
p_update_mode => 'UPDATE', p_delivery_id => l_delivery_id,
x_return_status => l_return_status);
l_serial_range_tab.delete;
add_to_update_table(p_del_det_int_rec => l_prev_int_rec,
p_update_mode => 'UPDATE', p_delivery_id => l_delivery_id,
x_return_status => l_return_status);
l_serial_range_tab.delete;
SELECT COUNT(*)
INTO l_det_freight_costs
FROM wsh_freight_costs_interface
WHERE delivery_detail_interface_id =
del_det_int_rec.delivery_detail_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
add_to_update_table(
p_del_det_int_rec => l_prev_int_rec,
p_update_mode => 'UPDATE',
p_delivery_id => l_delivery_id,
x_return_status => l_return_status);
l_serial_range_tab.delete;
add_to_update_table(p_del_det_int_rec => l_prev_int_rec,
p_update_mode => 'UPDATE', p_delivery_id => l_delivery_id,
x_return_status => l_return_status);
l_serial_range_tab.delete;
-- Need to delete the table because we pass only one delivery detail per call
l_del_detail_tab.delete;
p_action_code CREATE or UPDATE
x_dlvy_id The delivery id that is created
x_return_status OUT VARCHAR2)
DESCRIPTION :
-- This procedure is called by the wrapper, to process the interfaced deliveries
-- If the action is CREATE, then the interface record is fetched and a base
record is created by calling the public api
-- If the action is UPDATE, then , using the interface record values, the
base record is updated by calling the public api.
------------------------------------------------------------------------------
*/
PROCEDURE Process_Interfaced_Deliveries(
p_delivery_interface_id IN NUMBER,
p_action_code IN VARCHAR2,
x_dlvy_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
-- Bug 2753330
l_in_rec WSH_DELIVERIES_GRP.Del_In_Rec_Type;
SELECT
POOLED_SHIP_TO_LOCATION_CODE,
ULTIMATE_DROPOFF_LOCATION_CODE,
CUSTOMER_NUMBER,
FOB_LOCATION_CODE,
INITIAL_PICKUP_LOCATION_CODE,
INTMED_SHIP_TO_LOCATION_CODE,
ORGANIZATION_CODE,
BATCH_ID,
BILL_FREIGHT_TO,
BOOKING_NUMBER
CARRIED_BY,
COD_AMOUNT,
COD_CHARGE_PAID_BY,
COD_CURRENCY_CODE,
COD_REMIT_TO,
DESCRIPTION,
ENTRY_NUMBER,
FTZ_NUMBER,
HASH_VALUE,
IN_BOND_CODE,
LOADING_ORDER_FLAG,
LOADING_SEQUENCE,
NUMBER_OF_LPN,
PORT_OF_DISCHARGE,
PORT_OF_LOADING,
PROBLEM_CONTACT_REFERENCE,
REASON_OF_TRANSPORT,
ROUTED_EXPORT_TXN,
ROUTING_INSTRUCTIONS,
SERVICE_CONTRACT,
SHIPPING_MARKS,
SOURCE_HEADER_ID,
CARRIER_CODE,
NAME,
PLANNED_FLAG,
STATUS_CODE,
INITIAL_PICKUP_DATE,
INITIAL_PICKUP_LOCATION_ID,
ULTIMATE_DROPOFF_LOCATION_ID,
ULTIMATE_DROPOFF_DATE,
CUSTOMER_ID,
INTMED_SHIP_TO_LOCATION_ID,
POOLED_SHIP_TO_LOCATION_ID,
FREIGHT_TERMS_CODE,
FOB_CODE,
FOB_LOCATION_ID,
WAYBILL,
LOAD_TENDER_FLAG,
ACCEPTANCE_FLAG,
ACCEPTED_BY,
ACCEPTED_DATE,
ACKNOWLEDGED_BY,
CONFIRMED_BY,
ASN_DATE_SENT,
ASN_STATUS_CODE,
ASN_SEQ_NUMBER,
GROSS_WEIGHT,
NET_WEIGHT,
WEIGHT_UOM_CODE,
VOLUME,
VOLUME_UOM_CODE,
ADDITIONAL_SHIPMENT_INFO,
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,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
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,
INTERFACE_ACTION_CODE,
LOCK_FLAG,
PROCESS_FLAG,
PROCESS_MODE,
DELETE_FLAG,
PROCESS_STATUS_FLAG,
CURRENCY_CODE,
DELIVERY_TYPE,
ORGANIZATION_ID,
CARRIER_ID,
SHIP_METHOD_CODE,
DOCK_CODE,
CONFIRM_DATE,
DELIVERY_INTERFACE_ID,
DELIVERY_ID,
SERVICE_LEVEL,
MODE_OF_TRANSPORT,
-- J: W/V Changes
WV_FROZEN_FLAG,
--Bug 3458160
SHIPMENT_DIRECTION,
DELIVERED_DATE
FROM WSH_NEW_DEL_INTERFACE
WHERE delivery_interface_id = p_delivery_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
select distinct carrier_id , manifesting_enabled_flag
from wsh_carriers_v
where carrier_name = p_carrier_name;
select enforce_ship_method
from wsh_global_parameters;
select 'Y'
from wsh_carrier_services wcs,
wsh_org_carrier_services wocs
where wcs.carrier_service_id = wocs.carrier_service_id
and wcs.ship_method_code = p_ship_method_code
and wocs.organization_id = p_organization_id;
IF p_action_code NOT IN ('CREATE', 'UPDATE')
THEN
FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_ACTION_CODE');
-- need to send delivery id and name only for update
--Bug Bug 3458160
IF(p_action_code = 'UPDATE') THEN
l_dlvy_attr_tab(l_index).DELIVERY_ID := l_del_int_rec.delivery_id;
END IF; -- if p_action_code=update
IF(p_action_code = 'UPDATE')
THEN
-- {
-- These changes are made to allow the manifesting system to send a changed
-- combo of carrier, service level, and mode of transport.
IF ( nvl(l_del_int_rec.carrier_code, fnd_api.g_miss_char) = fnd_api.g_miss_char) THEN
--{
IF l_debug_on THEN
wsh_debug_sv.logmsg(l_module_name, 'Carrier is null');
p_api_name =>'Process_Interfaced_Deliveries, Action=UPDATE' ,
x_return_status => l_return_status);
SELECT ship_method_code INTO l_curr_ship_method
FROM wsh_new_deliveries
WHERE delivery_id = l_del_int_rec.delivery_id;
SELECT count(*) INTO l_del_freight_costs
FROM wsh_freight_costs_interface
WHERE delivery_interface_id = p_delivery_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
wsh_interface_grp.Create_Update_Delivery(
p_api_version_number => l_api_version_number,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_in_rec => l_in_rec,
p_rec_attr_tab => l_dlvy_attr_tab,
x_del_out_rec_tab => l_dlvy_out_rec_tab,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
wsh_debug_sv.log (l_module_name, 'Return status from create_Update_delivery',l_return_status);
wsh_debug_sv.log (l_module_name, 'Create Update Delivery api msg count', l_msg_count);
wsh_debug_sv.log (l_module_name, 'Create Update Delivery api msg', l_msg_data);
p_api_name => 'WSH_INTERFACE_GRP.Create_Update_Delivery' ,
x_return_status => l_return_status);
ELSIF p_action_code = 'UPDATE'
THEN
FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_DLVY_ERROR');
p_action_code CREATE or UPDATE or CANCEL
x_return_status OUT VARCHAR2)
DESCRIPTION :
-- This is the wrapper procedure that will be called by the Process_Inbound
for Shipment_Advice or Shipment_Request.
-- This takes in a delivery_interface_id and the action code
-- If the action code is CREATE, then the delivery is created in the base
tables based on the data in the delivery-interface tables
- Then for each of the delivery details in the interface tables, a corresponding
delivery detail is created in the base tables.
-- Then the newly created base delivery details are assigned to the newly
created base delivery.
-- If the action code is UPDATE, then the base delivery is updated first
-- Followed by updates of base delivery details
------------------------------------------------------------------------------
*/
PROCEDURE Delivery_Interface_Wrapper(
p_delivery_interface_id IN NUMBER,
p_action_code IN VARCHAR2,
x_delivery_id IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
-- variables
l_return_status VARCHAR2(30);
SELECT delivery_id ,
-- J: W/V Changes
gross_weight,
net_weight,
volume,
wv_frozen_flag
FROM wsh_new_del_interface
WHERE delivery_interface_id = p_delivery_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
SELECT NVL(gross_weight,0),
NVL(net_weight,0),
NVL(volume,0)
FROM wsh_new_deliveries
WHERE delivery_id = c_del_id;
IF(l_action_code IN ('CREATE', 'UPDATE')) THEN
IF(p_delivery_interface_id IS NULL) THEN
raise invalid_input;
IF(l_action_code = 'UPDATE') THEN
--Lock the records
Lock_Delivery_And_Details(
p_delivery_id => l_delivery_id,
x_return_status => l_return_status);
p_api_name =>'Delivery_Interface_Wrapper, Action=UPDATE' ,
x_return_status => l_return_status);
IF(l_action_code = 'UPDATE') THEN
l_tmp_del_id := l_delivery_id;
UPDATE wsh_new_deliveries
SET gross_weight = l_gross_weight,
net_weight = l_net_weight,
volume = l_volume,
wv_frozen_flag = l_wv_frozen_flag
WHERE delivery_id = l_tmp_del_id;
and inserts into the base wsh_freight_costs table.
-- This takes in as input the interface_id for a delivery or detail or stop or trip
-- This will be called by the procedures for processing delivery and delivery details
------------------------------------------------------------------------------
*/
PROCEDURE Process_Int_Freight_Costs(
p_delivery_interface_id IN NUMBER, -- DEFAULT NULL in spec,
p_del_detail_interface_id IN NUMBER, -- DEFAULT NULL in spec
p_stop_interface_id IN NUMBER, -- DEFAULT NULL in spec
p_trip_interface_id IN NUMBER, -- DEFAULT NULL in spec
x_return_status OUT NOCOPY VARCHAR2) IS
-- variables
l_freight_costs_info WSH_FREIGHT_COSTS_PUB.PubFreightCostRecType;
SELECT FREIGHT_COST_INTERFACE_ID,
FREIGHT_COST_ID,
FREIGHT_COST_TYPE_ID,
FREIGHT_COST_TYPE_CODE,
UNIT_AMOUNT,
CALCULATION_METHOD,
UOM,
QUANTITY,
TOTAL_AMOUNT,
CURRENCY_CODE,
CONVERSION_DATE,
CONVERSION_RATE,
CONVERSION_TYPE_CODE,
TRIP_INTERFACE_ID,
STOP_INTERFACE_ID,
DELIVERY_INTERFACE_ID,
DELIVERY_LEG_INTERFACE_ID,
DELIVERY_DETAIL_INTERFACE_ID,
TRIP_ID,
STOP_ID,
DELIVERY_ID,
DELIVERY_LEG_ID,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
DELIVERY_DETAIL_ID,
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,
FREIGHT_CODE,
INTERFACE_ACTION_CODE
FROM wsh_freight_costs_interface
WHERE (delivery_detail_interface_id = NVL(p_del_detail_interface_id, -99999))
OR (delivery_interface_id = NVL(p_delivery_interface_id, -99999))
OR (stop_interface_id = NVL(p_stop_interface_id, -99999))
OR (trip_interface_id = NVL(p_trip_interface_id, -99999))
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
SELECT freight_cost_type_id
FROM wsh_freight_cost_types
WHERE name = l_fc_type_code;
SELECT delivery_detail_id INTO l_del_detail_id
FROM wsh_del_details_interface
WHERE delivery_detail_interface_id = p_del_detail_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
SELECT delivery_id INTO l_delivery_id
FROM wsh_new_del_interface
WHERE delivery_interface_id = p_delivery_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
p_api_name => 'WSH_FREIGHT_COSTS_PUB.Create_Update_Freight_Costs',
x_return_status => l_return_status);
WSH_FREIGHT_COSTS_PUB.Create_Update_Freight_Costs (
p_api_version_number => 1.0,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_pub_freight_costs => l_freight_costs_info,
p_action_code => 'CREATE',
x_freight_cost_id => l_freight_cost_id);
wsh_debug_sv.log (l_module_name, 'Create Update Freight Costs api msg count', l_msg_count);
wsh_debug_sv.log (l_module_name, 'Create Update Freight Costs api msg', l_msg_data);
p_api_name => 'WSH_FREIGHT_COSTS_PUB.Create_Update_Freight_Costs',
x_return_status => l_return_status);
PROCEDURE : Update_Delivery_Details
PARAMETERS : p_changed_det_attributes IN WSH_INTERFACE.ChangedAttributeTabType
x_return_status - return status of API
DESCRIPTION :
-- This is an internal procedure, used by Process_Interfaced_Del_Details
-- This will be called for any updates of delivery details
-- history: 1/13/03 jckwok added a parameter for action_code to distinguish
-- between UPDATE and CANCEL actions.
------------------------------------------------------------------------------
*/
PROCEDURE Update_Delivery_Details(
p_source_code IN VARCHAR2, -- DEFAULT 'OE' in spec
p_delivery_interface_id IN NUMBER,
p_action_code IN VARCHAR2, -- jckwok
x_return_status OUT NOCOPY VARCHAR2
) IS
-- public api variables
l_msg_count NUMBER;
update_shipping_att_failed exception;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DELIVERY_DETAILS';
wsh_debug_sv.push(l_module_name,'Update_Delivery_Details');
wsh_debug_sv.log (l_module_name, 'Update Table Count', G_Update_Attributes_Tab.count);
-- Use the global table to call create_update Group API
IF(G_Update_Attributes_Tab.count > 0 ) THEN
l_in_rec.caller := 'WSH_INBOUND';
wsh_delivery_Details_grp.create_update_delivery_detail(
P_API_VERSION_NUMBER => 1.0,
P_INIT_MSG_LIST => FND_API.G_FALSE,
P_COMMIT => FND_API.G_FALSE,
x_RETURN_STATUS => l_RETURN_STATUS,
X_MSG_COUNT => l_MSG_COUNT,
X_MSG_DATA => l_MSG_DATA,
P_DETAIL_INFO_TAB => G_Update_Attributes_Tab,
P_IN_REC => l_IN_REC,
X_OUT_REC => l_OUT_REC,
P_SERIAL_RANGE_TAB => G_SERIAL_RANGE_TAB
);
wsh_debug_sv.log (l_module_name,'Return Status from create_update group api', l_return_status);
wsh_debug_sv.log (l_module_name, 'Update Ship Attr api msg count', l_msg_count);
wsh_debug_sv.log (l_module_name, 'Update Ship Attr api msg data', l_msg_data);
-- Need to insert record in interface errors table only
-- for 'OE' source code, i.e during 945 inbound
-- For 940 inbound - cancel case, there may not be any
-- data in interface tables.
IF(p_delivery_interface_id IS NOT NULL) THEN
l_loc_interface_error_rec.p_interface_table_name := 'WSH_NEW_DEL_INTERFACE';
p_api_name =>'WSH_DELIVERY_DETAILS_GRP.Create_Update_Delivery_Detail',
x_return_status => l_return_status);
raise update_shipping_att_failed;
END IF; -- if G_Update_Attributes_Tab.count
WHEN update_shipping_att_failed THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
WSH_DEBUG_SV.logmsg(l_module_name,'update_shipping_att_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:update_shipping_att_failed');
END Update_Delivery_Details;
PROCEDURE : Create_Update_Trip_For_Dlvy
PARAMETERS : p_delivery_id
x_return_status - return status of API
DESCRIPTION :
- This procedure is called to create/update the trip for the delivery
which has been updated with the inbound 945 transaction data
- If a trip already exists for the delivery in the base tables, then
this procedure just updates the trip and trip_stop tables based on the
values in the trip interface table and trip_stop interface table.
- If a trip does not already exist, then this procedure first calls
autocreate_trip to create a trip for the delivery.
-- Then it updates the newly created trip and trip_stops with the
values from the interface table data
------------------------------------------------------------------------------
*/
PROCEDURE Create_Update_Trip_For_Dlvy(
p_delivery_id IN NUMBER,
x_pickup_stop_id OUT NOCOPY NUMBER,
x_dropoff_stop_id OUT NOCOPY NUMBER,
x_trip_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
-- variables
l_del_rows wsh_util_core.id_tab_type;
SELECT wdg.pick_up_stop_id, wdg.drop_off_stop_id, wts.trip_id
FROM wsh_delivery_legs wdg, wsh_trip_stops wts
WHERE wdg.delivery_id = p_delivery_id
AND wdg.pick_up_stop_id = wts.stop_id;
SELECT wdli.pick_up_stop_interface_id, wdli.drop_off_stop_interface_id, wtsi.trip_interface_id
FROM wsh_del_legs_interface wdli, wsh_trip_stops_interface wtsi
WHERE wdli.delivery_id = p_delivery_id
AND wdli.pick_up_stop_interface_id = wtsi.stop_interface_id
AND WDLI.INTERFACE_ACTION_CODE = '94X_INBOUND'
AND WTSI.INTERFACE_ACTION_CODE = '94X_INBOUND';
SELECT actual_departure_date, departure_seal_code
FROM wsh_trip_stops_interface
WHERE stop_interface_id = l_stop_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
SELECT actual_arrival_date
FROM wsh_trip_stops_interface
WHERE stop_interface_id = l_stop_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
SELECT vehicle_number, vehicle_num_prefix, route_id, routing_instructions,
--Bug 3458160
operator
FROM wsh_trips_interface
WHERE trip_interface_id = l_trip_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_TRIP_FOR_DLVY';
wsh_debug_sv.push(l_module_name,'Create_Update_Trip_For_Dlvy');
-- update the base trip_stops
IF l_pickup_stop_id IS NOT NULL THEN
UPDATE wsh_trip_stops
SET actual_departure_date = int_pickup_stop_rec.actual_departure_date,
departure_seal_code = int_pickup_stop_rec.departure_seal_code
WHERE stop_id = l_pickup_stop_id;
WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
(p_stop_id_tab => l_stop_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
UPDATE wsh_trip_stops
SET actual_arrival_date = int_dropoff_stop_rec.actual_arrival_date
WHERE stop_id = l_dropoff_stop_id;
WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
(p_stop_id_tab => l_stop_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
-- update the base trip
IF l_trip_id IS NOT NULL THEN
UPDATE wsh_trips
SET vehicle_num_prefix = int_trip_rec.vehicle_num_prefix,
vehicle_number = int_trip_rec.vehicle_number,
route_id = int_trip_rec.route_id,
routing_instructions = int_trip_rec.routing_instructions,
--Bug 3458160
operator = int_trip_rec.operator
WHERE trip_id = l_trip_id;
END Create_Update_Trip_For_Dlvy;
SELECT
WSH_DEL_LEGS_INTERFACE_S.nextval,
WSH_TRIP_STOPS_INTERFACE_S.nextval,
WSH_TRIPS_INTERFACE_S.nextval
INTO l_del_leg_interface_id,
l_pickup_stop_interface_id,
l_trip_interface_id
FROM dual;
SELECT
WSH_TRIP_STOPS_INTERFACE_S.nextval
INTO l_dropoff_stop_interface_id
FROM dual;
-- insert record into wsh_del_legs_interface
INSERT into wsh_del_legs_interface(
DELIVERY_LEG_INTERFACE_ID,
DELIVERY_INTERFACE_ID,
PICK_UP_STOP_INTERFACE_ID,
DROP_OFF_STOP_INTERFACE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
INTERFACE_ACTION_CODE)
VALUES(
l_del_leg_interface_id,
p_delivery_interface_id,
l_pickup_stop_interface_id,
l_dropoff_stop_interface_id,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
'94X_INBOUND');
-- insert records into wsh_trip_stops_interface
-- first the pickup stop
INSERT INTO wsh_trip_stops_interface(
STOP_INTERFACE_ID,
TRIP_INTERFACE_ID,
ACTUAL_DEPARTURE_DATE,
DEPARTURE_SEAL_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
INTERFACE_ACTION_CODE)
VALUES(
l_pickup_stop_interface_id,
l_trip_interface_id,
p_act_dep_date,
p_dep_seal_code,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
'94X_INBOUND');
INSERT INTO wsh_trip_stops_interface(
STOP_INTERFACE_ID,
TRIP_INTERFACE_ID,
ACTUAL_ARRIVAL_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
INTERFACE_ACTION_CODE)
VALUES(
l_dropoff_stop_interface_id,
l_trip_interface_id,
p_act_arr_date,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
'94X_INBOUND');
-- insert records into wsh_trips_interface
INSERT INTO wsh_trips_interface(
TRIP_INTERFACE_ID,
VEHICLE_NUM_PREFIX,
VEHICLE_NUMBER,
ROUTE_ID,
ROUTING_INSTRUCTIONS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
INTERFACE_ACTION_CODE,
--Bug 3458160
operator)
VALUES (
l_trip_interface_id,
p_trip_veh_num_pfx,
p_trip_vehicle_num,
p_trip_route_id,
p_trip_routing_ins,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
'94X_INBOUND',
p_operator);
PROCEDURE Add_To_Update_Table
(p_del_det_int_rec IN del_det_int_cur%ROWTYPE,
p_update_mode IN VARCHAR2 DEFAULT 'UPDATE',
p_delivery_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
-- variables
--l_changed_attributes WSH_INTERFACE.ChangedAttributeRecType;
SELECT wdd.delivery_detail_id
FROM wsh_delivery_details wdd
WHERE source_line_id = l_cont_inst_id
AND wdd.source_code = 'WSH'
AND wdd.container_flag = 'Y'
AND wdd.organization_id = p_del_det_int_rec.organization_id;
SELECT wdai.parent_delivery_detail_id
FROM wsh_del_assgn_interface wdai
WHERE wdai.delivery_detail_interface_id = p_del_det_int_rec.delivery_detail_interface_id
AND WDAI.INTERFACE_ACTION_CODE = '94X_INBOUND';
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ADD_TO_UPDATE_TABLE';
wsh_debug_sv.push(l_module_name, 'Add_To_Update_Table');
wsh_debug_sv.log (l_module_name, 'Update Mode' , p_update_mode);
-- Add To Packing Table, only for update cases
IF(p_update_mode = 'UPDATE') THEN
OPEN intf_parent_det_cur;
END IF; -- if p_udpate_mode is UPDATE
G_Update_Attributes_Tab((G_Update_Attributes_Tab.count)+1) := l_changed_attributes;
/* Patchset I: passing serial numbers to group api. so no need for the direct update of to_serial_number
-- kvenkate. Removed the code for direct update.
*/
IF l_debug_on THEN
wsh_debug_sv.pop(l_module_name);
END Add_To_Update_Table;
SELECT wdd.delivery_detail_id,
wdd.source_line_id,
wdd.source_code,
wdd.container_flag,
wdd.requested_quantity_uom
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = p_delivery_id;
-- select the delivery lines
-- Add to the global update table
FOR del_details_rec IN del_details_cur LOOP
l_del_det_int_rec.delivery_detail_id := del_details_rec.delivery_detail_id;
Add_To_Update_Table(
l_del_det_int_rec,
'CANCEL',
p_delivery_id,
l_return_status);
wsh_debug_sv.log (l_module_name, 'return status from add_to_update_tbl', l_return_status);
-- call update_delivery_details
Update_Delivery_Details(
p_source_code => 'WSH',
p_action_code => 'CANCEL',
x_return_status => l_return_status
);
wsh_debug_sv.log (l_module_name, 'Update_Delivery_Details l_return_status',l_return_status);
SELECT wdd.delivery_detail_id
FROM wsh_delivery_details wdd, wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = p_delivery_id
FOR UPDATE NOWAIT;
SELECT delivery_id
INTO l_dummy_id
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id
FOR UPDATE NOWAIT;
SELECT requested_quantity
FROM wsh_delivery_details
WHERE delivery_detail_id =l_del_detail_id;