[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select delivery_leg_id, delivery_id from
wsh_Delivery_legs where parent_delivery_leg_id = c_parent_delivery_leg_id;
SELECT wnd.delivery_id, wnd.organization_id FROM
wsh_delivery_legs wdl, wsh_new_deliveries wnd
WHERE wdl.delivery_id = wnd.delivery_id AND
wdl.delivery_leg_id = c_delivery_leg_id;
select FTE_TRACKING_TRANSACTION_S.nextval into l_transaction_id_s from dual;
select WSH_DELIVERY_LEG_ACTIVITIES_S.nextval into l_activity_id_s from dual;
--Insert into Activities table
insert into wsh_delivery_leg_activities
(activity_id, delivery_leg_id, activity_date, activity_type,
creation_date, created_by, last_update_date, last_updated_by)
values
(l_activity_id_s, l_delivery_leg_id, sysdate, 'TRACKING',
sysdate, p_carrier_id, sysdate, p_carrier_id);
--Insert into Headers table
insert into fte_shipment_status_headers(
TRANSACTION_ID, delivery_leg_id, delivery_id, MESSAGE_TYPE, SHIPMENT_STATUS_ID,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CARRIER_NAME, GENERATION_DATE , STATUS , TRACKING_ID, TRACKING_ID_TYPE,
BILL_OF_LADING , CARRIER_SERVICE_LEVEL, CONTAINER_ID , CONTAINER_SEAL ,
ARRIVAL_DATE, RECEIVED_DATE , DELIVERY_SCHEDULED_DATE , DEPARTURE_DATE,
ESTIMATED_ARRIVAL_DATE, ESTIMATED_DEPARTURE_DATE, BEGIN_LOADING_DATE,
END_LOADING_DATE , PROMISED_SHIPMENT_DATE , PROMISED_DELIVERY_DATE ,
SHIPPED_DATE, EXPECTED_SHIPMENT_DATE, BEGIN_UNLOADING_DATE, END_UNLOADING_DATE ,
DESCRIPTION , FREIGHT_CLASS, HAZARDOUS_MATERIAL, LOAD_POINT, NOTES1,NOTES2 , NOTES3,
NOTES4 ,NOTES5, NOTES6, NOTES7 , NOTES8 , NOTES9,
SHIP_UNIT_QUANTITY, SHIP_UNIT_UOM, VOLUME , VOLUME_UOM ,
WEIGHT, WEIGHT_UOM, ROUTE_ID , ROUTE_TYPE,
SHIP_NOTES, SHIPPER_NUMBER , SHIP_POINT, SPECIAL_HANDLING,
STOP_NUMBER, SHIPPING_METHOD, SHIP_FROM_PARTNER, SHIP_TO_PARTNER,
CARRIER_PARTNER, BILL_TO_PARTNER, NOTIFY_PARTNER, HOLD_AT_PARTNER, RETURN_TO_PARTNER, MARK_FOR_PARTNER,
IMPORTER_PARTNER, EXPORTER_PARTNER, DELIVERY_DETAIL_ID, LICENSE_PLATE_NUMBER, REASON_CODE,
ACTIVITY_ID
)
select
l_transaction_id_s, l_delivery_leg_id,l_delivery_id,
MESSAGE_TYPE, FTE_TRACKING_STATUS_S.nextval,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CARRIER_NAME, GENERATION_DATE , STATUS , TRACKING_ID, TRACKING_ID_TYPE,
BILL_OF_LADING , CARRIER_SERVICE_LEVEL, CONTAINER_ID , CONTAINER_SEAL ,
ARRIVAL_DATE, RECEIVED_DATE , DELIVERY_SCHEDULED_DATE , DEPARTURE_DATE,
ESTIMATED_ARRIVAL_DATE, ESTIMATED_DEPARTURE_DATE, BEGIN_LOADING_DATE,
END_LOADING_DATE , PROMISED_SHIPMENT_DATE , PROMISED_DELIVERY_DATE ,
SHIPPED_DATE, EXPECTED_SHIPMENT_DATE, BEGIN_UNLOADING_DATE, END_UNLOADING_DATE ,
DESCRIPTION , FREIGHT_CLASS, HAZARDOUS_MATERIAL, LOAD_POINT, NOTES1,NOTES2 , NOTES3,
NOTES4 ,NOTES5, NOTES6, NOTES7 , NOTES8 , NOTES9,
SHIP_UNIT_QUANTITY, SHIP_UNIT_UOM, VOLUME , VOLUME_UOM ,
WEIGHT, WEIGHT_UOM, ROUTE_ID , ROUTE_TYPE,
SHIP_NOTES, SHIPPER_NUMBER , SHIP_POINT, SPECIAL_HANDLING,
STOP_NUMBER, SHIPPING_METHOD, SHIP_FROM_PARTNER, SHIP_TO_PARTNER,
CARRIER_PARTNER, BILL_TO_PARTNER, NOTIFY_PARTNER, HOLD_AT_PARTNER, RETURN_TO_PARTNER, MARK_FOR_PARTNER,
IMPORTER_PARTNER, EXPORTER_PARTNER, DELIVERY_DETAIL_ID, LICENSE_PLATE_NUMBER, REASON_CODE,
l_activity_id_s
from fte_shipment_status_headers where transaction_id = p_transaction_id;
-- Insert into details table
BEGIN
select fte_tracking_status_s.nextval into l_content_details_id from dual;
insert into fte_shipment_status_details(
SHIPMENT_STATUS_DETAIL_ID,
DELIVERY_LEG_ID,
TRANSACTION_ID,
REPORT_DATE,
SHIPMENT_STATUS,
CHANGED_STATUS_DATE,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SHIP_UNIT_SEQ,
SHIP_UNIT_TOTAL,
TRACKING_ID,
TRACKING_ID_TYPE)
SELECT
l_content_details_id,
l_delivery_leg_id,
l_transaction_id_s,
REPORT_DATE,
SHIPMENT_STATUS,
CHANGED_STATUS_DATE,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SHIP_UNIT_SEQ,
SHIP_UNIT_TOTAL,
TRACKING_ID,
TRACKING_ID_TYPE
FROM fte_shipment_status_details WHERE transaction_id = p_transaction_id;
-- Insert into delivery proof table
BEGIN
select fte_tracking_status_s.nextval into l_content_pod_id from dual;
insert into fte_delivery_proof(
ID,
TRANSACTION_ID,
RECEIVED_DATE,
--NAME1, -- to fix Bug#5031206
SHIP_UNIT_QUANTITY,
SHIP_UNIT_UOM,
NOTES1,
NOTES2,
NOTES3,
NOTES4,
NOTES5,
NOTES6,
NOTES7,
NOTES8,
NOTES9,
SHIP_UNIT_SEQ,
SHIP_UNIT_TOTAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
TRACKING_ID,
TRACKING_ID_TYPE,
CONSIGNEE_NAME,
STATUS,
SHIPMENT_WEIGHT,
SHIPMENT_VOLUME,
LOCATION
)
SELECT
l_content_pod_id,
l_transaction_id_s,
RECEIVED_DATE,
--NAME1, --To fix bug#5031206
SHIP_UNIT_QUANTITY,
SHIP_UNIT_UOM,
NOTES1,
NOTES2,
NOTES3,
NOTES4,
NOTES5,
NOTES6,
NOTES7,
NOTES8,
NOTES9,
SHIP_UNIT_SEQ,
SHIP_UNIT_TOTAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
TRACKING_ID,
TRACKING_ID_TYPE,
CONSIGNEE_NAME,
STATUS,
SHIPMENT_WEIGHT,
SHIPMENT_VOLUME,
LOCATION
FROM fte_delivery_proof WHERE transaction_id = p_transaction_id;
--Inserting into fte_message partner as per FTEFSSI XGM to content
BEGIN
select fte_tracking_status_s.nextval into l_address_to_id from dual;
INSERT INTO
FTE_MESSAGE_PARTNER (
ID,
NAME1 ,
NAME2 ,
NAME3 ,
NAME4 ,
NAME5 ,
NAME6 ,
NAME7 ,
NAME8 ,
NAME9 ,
ONETIME ,
PARTNER_ID ,
PARTNER_TYPE ,
ACTIVE ,
CURRENCY ,
DESCRIPTION ,
DUNS_NUMBER ,
GL_ENTITIES ,
PARENT_ID ,
PARTNER_ID_X ,
PARTNER_RATING,
PARTNER_ROLE ,
PAYMENT_METHOD,
TAX_EXEMPT ,
TAX_ID ,
TERM_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN ,
SHIPPER_ACCOUNT_NUMBER,
TRANSACTION_ID)
SELECT
l_address_to_id,
NAME1 ,
NAME2 ,
NAME3 ,
NAME4 ,
NAME5 ,
NAME6 ,
NAME7 ,
NAME8 ,
NAME9 ,
ONETIME ,
PARTNER_ID ,
PARTNER_TYPE ,
ACTIVE ,
CURRENCY ,
DESCRIPTION ,
DUNS_NUMBER ,
GL_ENTITIES ,
PARENT_ID ,
PARTNER_ID_X ,
PARTNER_RATING,
PARTNER_ROLE ,
PAYMENT_METHOD,
TAX_EXEMPT ,
TAX_ID ,
TERM_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN ,
SHIPPER_ACCOUNT_NUMBER,
l_transaction_id_s from
fte_message_partner WHERE TRANSACTION_ID = l_transaction_id_s ;
INSERT INTO
FTE_MESSAGE_CONTACT(
CONTACT_ID ,
PARTNER_ID ,
NAME1 ,
NAME2 ,
NAME3 ,
NAME4 ,
NAME5 ,
NAME6 ,
NAME7 ,
NAME8 ,
NAME9 ,
CONTACT_TYPE ,
DESCRIPTION ,
EMAIL ,
FAX1 ,
FAX2 ,
FAX3 ,
FAX4 ,
FAX5 ,
FAX6 ,
FAX7 ,
FAX8 ,
FAX9 ,
TELEPHONE1 ,
TELEPHONE2 ,
TELEPHONE3 ,
TELEPHONE4 ,
TELEPHONE5 ,
TELEPHONE6 ,
TELEPHONE7 ,
TELEPHONE8 ,
TELEPHONE9 ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
TRANSACTION_ID
)
SELECT
fte_tracking_status_s.nextval,
l_address_to_id,
NAME1 ,
NAME2 ,
NAME3 ,
NAME4 ,
NAME5 ,
NAME6 ,
NAME7 ,
NAME8 ,
NAME9 ,
CONTACT_TYPE ,
DESCRIPTION ,
EMAIL ,
FAX1 ,
FAX2 ,
FAX3 ,
FAX4 ,
FAX5 ,
FAX6 ,
FAX7 ,
FAX8 ,
FAX9 ,
TELEPHONE1 ,
TELEPHONE2 ,
TELEPHONE3 ,
TELEPHONE4 ,
TELEPHONE5 ,
TELEPHONE6 ,
TELEPHONE7 ,
TELEPHONE8 ,
TELEPHONE9 ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
l_transaction_id_s
FROM
FTE_MESSAGE_CONTACT WHERE
TRANSACTION_ID = p_transaction_id;
INSERT INTO FTE_MESSAGE_LOCATION (
location_to_id,
ID ,
DESCRIPTION ,
GEOCOORDINATES ,
GEOCOORDINATES_TYPE ,
LOCATION_ID ,
LOCATION_ID_TYPE ,
SITELEVEL1 ,
SITELEVEL2 ,
SITELEVEL3 ,
SITELEVEL4 ,
SITELEVEL5 ,
SITELEVEL6 ,
SITELEVEL7 ,
SITELEVEL8 ,
SITELEVEL9 ,
ADDRESS_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
TRANSACTION_ID ,
LOCATION_TO_TABLE
)
SELECT
DECODE(location_to_table,'FTE_DELIVERY_PROOF',l_content_pod_id,l_content_details_id),
fte_tracking_status_s.nextval,
DESCRIPTION ,
GEOCOORDINATES ,
GEOCOORDINATES_TYPE ,
LOCATION_ID ,
LOCATION_ID_TYPE ,
SITELEVEL1 ,
SITELEVEL2 ,
SITELEVEL3 ,
SITELEVEL4 ,
SITELEVEL5 ,
SITELEVEL6 ,
SITELEVEL7 ,
SITELEVEL8 ,
SITELEVEL9 ,
ADDRESS_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
l_transaction_id_s ,
LOCATION_TO_TABLE
from
FTE_MESSAGE_LOCATION
where transaction_id = p_transaction_id;
INSERT INTO
FTE_MESSAGE_ADDRESS(
ADDRESS_TO_ID,ID ,
TRANSACTION_ID ,
ADDRESS_TO_TABLE ,
ADDR_LINE1 ,
ADDR_LINE2 ,
ADDR_LINE3 ,
ADDR_LINE4 ,
ADDR_LINE5 ,
ADDR_LINE6 ,
ADDR_LINE7 ,
ADDR_LINE8 ,
ADDR_LINE9 ,
ADDR_TYPE ,
ADDR_CITY ,
ADDR_COUNTY ,
ADDR_COUNTRY ,
ADDR_DESCRIPTION ,
ADDR_POSTAL_CODE ,
ADDR_REGION ,
ADDR_STATE ,
ADDR_TAX_JURISDICTION ,
ADDR_URL ,
FAX1 ,
FAX2 ,
FAX3 ,
FAX4 ,
FAX5 ,
FAX6 ,
FAX7 ,
FAX8 ,
FAX9 ,
TELEPHONE1 ,
TELEPHONE2 ,
TELEPHONE3 ,
TELEPHONE4 ,
TELEPHONE5 ,
TELEPHONE6 ,
TELEPHONE7 ,
TELEPHONE8 ,
TELEPHONE9 ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN )
SELECT
decode (ADDRESS_TO_TABLE,'FTE_MESSAGE_PARTNER',
l_address_to_id,
(select id from fte_message_location where
location_to_table =( SELECT fl.location_to_table
FROM fte_message_location fl WHERE fl.id= fa.address_to_id
AND transaction_id = p_transaction_id ) and
transaction_id = l_transaction_id_s
)
),
fte_tracking_status_s.nextval,
l_transaction_id_s ,
ADDRESS_TO_TABLE ,
ADDR_LINE1 ,
ADDR_LINE2 ,
ADDR_LINE3 ,
ADDR_LINE4 ,
ADDR_LINE5 ,
ADDR_LINE6 ,
ADDR_LINE7 ,
ADDR_LINE8 ,
ADDR_LINE9 ,
ADDR_TYPE ,
ADDR_CITY ,
ADDR_COUNTY ,
ADDR_COUNTRY ,
ADDR_DESCRIPTION ,
ADDR_POSTAL_CODE ,
ADDR_REGION ,
ADDR_STATE ,
ADDR_TAX_JURISDICTION ,
ADDR_URL ,
FAX1 ,
FAX2 ,
FAX3 ,
FAX4 ,
FAX5 ,
FAX6 ,
FAX7 ,
FAX8 ,
FAX9 ,
TELEPHONE1 ,
TELEPHONE2 ,
TELEPHONE3 ,
TELEPHONE4 ,
TELEPHONE5 ,
TELEPHONE6 ,
TELEPHONE7 ,
TELEPHONE8 ,
TELEPHONE9 ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
from fte_message_address fa
where transaction_id =p_transaction_id ;
INSERT INTO FTE_SHIPMENT_STATUS_EXCEPTIONS
(
DETAIL_ID ,
EXCEPTION_ID ,
EXCEPTION_DATE ,
DESCRIPTION ,
REASON_CODE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
LADING_QUANTITY ,
LADING_QUANTITY_UOM ,
TRANSACTION_ID
)
SELECT
l_content_details_id,
fte_tracking_status_s.nextval,
EXCEPTION_DATE ,
DESCRIPTION ,
REASON_CODE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
LADING_QUANTITY ,
LADING_QUANTITY_UOM ,
l_transaction_id_s
FROM FTE_SHIPMENT_STATUS_EXCEPTIONS
WHERE TRANSACTION_ID = P_TRANSACTION_ID ;
select fte_tracking_status_s.currval into l_content_exceptions_id from dual;
INSERT INTO WSH_MESSAGE_ATTACHMENT
(
ATTACHMENT_ID ,
ATTACH_TO_ID ,
ATTACH_TO_TABLE ,
DESCRIPTION ,
FILETYPE ,
TITLE ,
FILE_CREATION_DATE ,
FILESIZE ,
FILESIZE_UOM ,
FILENAME ,
URI ,
COMPRESSION_TYPE ,
COMPRESSION_ID ,
NOTES1 ,
NOTES2 ,
NOTES3 ,
NOTES4 ,
NOTES5 ,
NOTES6 ,
NOTES7 ,
NOTES8 ,
NOTES9 ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
TRANSACTION_ID
)
SELECT
fte_tracking_status_s.nextval ,
DECODE(ATTACH_TO_TABLE,'FTE_MESSAGE_PARTNER',l_address_to_id,
'FTE_SHIPMENT_STATUS_DETAILS',l_content_details_id,
'FTE_SHIPMENT_STATUS_EXCEPTIONS',l_content_exceptions_id,
l_content_pod_id),
ATTACH_TO_TABLE ,
DESCRIPTION ,
FILETYPE ,
TITLE ,
FILE_CREATION_DATE ,
FILESIZE ,
FILESIZE_UOM ,
FILENAME ,
URI ,
COMPRESSION_TYPE ,
COMPRESSION_ID ,
NOTES1 ,
NOTES2 ,
NOTES3 ,
NOTES4 ,
NOTES5 ,
NOTES6 ,
NOTES7 ,
NOTES8 ,
NOTES9 ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
l_transaction_id_s
FROM
WSH_MESSAGE_ATTACHMENT WHERE TRANSACTION_ID = P_TRANSACTION_ID ;
-- Call the procedure get_delivery_details to delete all the
-- details and POD information for a delivery leg and retain only the
-- last detail and POD information
FTE_TRACKING_WRAPPER.get_delivery_details ( l_transaction_id_s, p_carrier_id,
l_exception_message,
l_return_status, l_error_token_text);
-- To update the recieced date for the last leg of a delivery when POD is received
BEGIN
select received_date,1 into l_received_date,l_flag
from fte_delivery_proof where transaction_id= l_transaction_id_s;
select wsh_interface_errors_s.nextval into l_error_id
from dual;
FTE_TRACKING_WRAPPER.insert_error_status
(l_error_id, 'FTE_SHIPMEMT_STATUS_HEADERS',
p_transaction_id, 3, 'ERROR', 'NO CHILD DELIVERY FOUND',
p_carrier_id);
select wsh_interface_errors_s.nextval into l_error_id
from dual;
FTE_TRACKING_WRAPPER.insert_error_status
(l_error_id, 'FTE_SHIPMEMT_STATUS_HEADERS',
p_transaction_id, 5, 'ERROR', 'OTHER EXCEPTION',
p_carrier_id);
SELECT ADDR_CITY,
ADDR_STATE,
ADDR_COUNTRY
FROM
FTE_MESSAGE_ADDRESS
WHERE
ADDRESS_TO_ID=P_LOC_ID;
SELECT wnd.delivery_id, wnd.organization_id FROM
wsh_delivery_legs wdl, wsh_new_deliveries wnd
WHERE wdl.delivery_id = wnd.delivery_id AND
wdl.delivery_leg_id = c_delivery_leg_id;
SELECT tracking_id, tracking_id_type, carrier_name, status, arrival_date,
departure_date, estimated_arrival_date, estimated_departure_date,
end_loading_date, begin_loading_date, end_unloading_date,
begin_unloading_date,
(notes1||notes2||notes3||notes4||notes5||notes6||notes7||notes8||notes9) as notes,
description
INTO l_tracking_id, l_tracking_id_type, l_carrier_name, l_shipment_status,
l_arrival_date, l_departure_date, l_estimated_arrival_date, l_estimated_departure_date,
l_end_loading_date, l_begin_loading_date, l_end_unloading_date, l_begin_unloading_date,
l_notes, l_description
FROM fte_shipment_status_headers
WHERE transaction_id = p_transaction_id;
SELECT carrier_name INTO l_carrier_name
FROM fte_shipment_status_headers WHERE transaction_id = p_transaction_id;
SELECT h.party_id INTO l_carrier_id
FROM hz_parties h, wsh_carriers w
WHERE h.party_id = w.carrier_id
AND party_name = l_carrier_name;
SELECT /*+ first_rows ordered */
wsh_delivery_legs.delivery_id, wsh_trips.carrier_id, wsh_new_deliveries.waybill,
wsh_delivery_legs.delivery_leg_id
INTO l_delivery_id, l_carrier_id, l_waybill, l_delivery_leg_id
FROM wsh_trips, wsh_trip_stops, wsh_delivery_legs , wsh_new_deliveries
WHERE wsh_delivery_legs.delivery_id = wsh_new_deliveries.delivery_id
AND wsh_delivery_legs.pick_up_stop_id = wsh_trip_stops.stop_id
AND wsh_trip_stops.trip_id = wsh_trips.trip_id
AND wsh_trips.carrier_id = l_carrier_id
AND wsh_new_deliveries.waybill = l_tracking_id
AND wsh_delivery_legs.parent_delivery_leg_id is null;--Rel 12 MDC Changes
INSERT INTO wsh_delivery_leg_activities
(activity_id, delivery_leg_id, activity_date, activity_type,
creation_date, created_by, last_update_date, last_updated_by)
VALUES
(WSH_DELIVERY_LEG_ACTIVITIES_S.NEXTVAL, l_delivery_leg_id, sysdate, 'TRACKING',
sysdate, l_carrier_id, sysdate, l_carrier_id);
UPDATE fte_shipment_status_headers
SET delivery_id = l_delivery_id,
delivery_leg_id = l_delivery_leg_id,
message_type = 'DELIVERY',
creation_date = sysdate,
created_by = FND_GLOBAL.USER_ID,
last_update_date= sysdate,
last_updated_by =FND_GLOBAL.USER_ID,
last_update_login=FND_GLOBAL.USER_ID,
activity_id = WSH_DELIVERY_LEG_ACTIVITIES_S.CURRVAL
WHERE transaction_id = p_transaction_id;
UPDATE fte_shipment_status_details
SET delivery_leg_id = l_delivery_leg_id,
creation_date = sysdate,
created_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login=FND_GLOBAL.USER_ID
WHERE transaction_id = p_transaction_id;
SELECT id INTO l_location_id
FROM fte_message_location
WHERE transaction_id=p_transaction_id
and location_to_table = 'FTE_DELIVERY_PROOF';
-- Update the POD information
UPDATE fte_delivery_proof
SET creation_date = sysdate,
created_by =FND_GLOBAL.USER_ID,
last_update_date=sysdate,
last_updated_by =FND_GLOBAL.USER_ID,
last_update_login=FND_GLOBAL.USER_ID,
location =l_address
WHERE transaction_id= p_transaction_id;
SELECT /*+ first_rows ordered */
wsh_delivery_legs.delivery_id, wsh_trips.carrier_id,
wsh_delivery_legs.booking_number, wsh_delivery_legs.delivery_leg_id
INTO l_delivery_id, l_carrier_id, l_booking_number, l_delivery_leg_id
FROM wsh_trips, wsh_trip_stops, wsh_delivery_legs , wsh_new_deliveries
WHERE wsh_delivery_legs.delivery_id = wsh_new_deliveries.delivery_id
AND wsh_delivery_legs.pick_up_stop_id = wsh_trip_stops.stop_id
AND wsh_trip_stops.trip_id = wsh_trips.trip_id
AND wsh_trips.carrier_id = l_carrier_id
AND wsh_delivery_legs.booking_number = l_tracking_id
AND wsh_delivery_legs.parent_delivery_leg_id is null;--Rel 12 MDC Changes
insert into wsh_delivery_leg_activities
(activity_id, delivery_leg_id, activity_date, activity_type,
creation_date, created_by, last_update_date, last_updated_by)
values
(WSH_DELIVERY_LEG_ACTIVITIES_S.nextval, l_delivery_leg_id, sysdate, 'TRACKING',
sysdate, l_carrier_id, sysdate, l_carrier_id);
update fte_shipment_status_headers
set delivery_id = l_delivery_id,
delivery_leg_id = l_delivery_leg_id,
message_type = 'DELIVERY',
creation_date = sysdate,
created_by =FND_GLOBAL.USER_ID,
last_update_date=sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login=FND_GLOBAL.USER_ID,
activity_id = WSH_DELIVERY_LEG_ACTIVITIES_S.currval
where transaction_id = p_transaction_id;
update fte_shipment_status_details
set delivery_leg_id = l_delivery_leg_id,
creation_date = sysdate,
created_by =FND_GLOBAL.USER_ID,
last_update_date= sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login=FND_GLOBAL.USER_ID
where transaction_id = p_transaction_id;
select id into l_location_id
from fte_message_location
where transaction_id=p_transaction_id
and location_to_table = 'FTE_DELIVERY_PROOF';
update fte_delivery_proof
set creation_date=sysdate,
created_by =FND_GLOBAL.USER_ID,
last_update_date=sysdate,
last_updated_by=FND_GLOBAL.USER_ID,
last_update_login=FND_GLOBAL.USER_ID,
location = l_address
where transaction_id= p_transaction_id;
select wsh_new_deliveries.delivery_id, wsh_delivery_legs.delivery_leg_id,
wsh_trips.carrier_id
into l_delivery_id, l_delivery_leg_id, l_carrier_id
from wsh_new_deliveries, wsh_bols_rd_v, wsh_delivery_legs, wsh_trips, wsh_trip_stops
where wsh_bols_rd_v.delivery_leg_id = wsh_delivery_legs.delivery_leg_id
and wsh_delivery_legs.delivery_id = wsh_new_deliveries.delivery_id
and wsh_delivery_legs.pick_up_stop_id = wsh_trip_stops.stop_id
and wsh_trip_stops.trip_Id = wsh_trips.trip_id
and wsh_trips.carrier_id = l_carrier_id
and wsh_bols_rd_v.bill_of_lading_number = l_tracking_id
and wsh_delivery_legs.parent_delivery_leg_id is null;--Rel 12 MDC Changes
insert into wsh_delivery_leg_activities
(activity_id, delivery_leg_id, activity_date, activity_type,
creation_date, created_by, last_update_date, last_updated_by)
values
(WSH_DELIVERY_LEG_ACTIVITIES_S.nextval, l_delivery_leg_id, sysdate, 'TRACKING',
sysdate, l_carrier_id, sysdate, l_carrier_id);
update fte_shipment_status_headers
set delivery_id = l_delivery_id,
delivery_leg_id = l_delivery_leg_id,
message_type = 'DELIVERY',
creation_date = sysdate,
created_by = FND_GLOBAL.USER_ID,
last_update_date= sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login=FND_GLOBAL.USER_ID,
activity_id = WSH_DELIVERY_LEG_ACTIVITIES_S.currval
where transaction_id = p_transaction_id;
update fte_shipment_status_details
set delivery_leg_id = l_delivery_leg_id,
creation_date = sysdate,
created_by = FND_GLOBAL.USER_ID,
last_update_date= sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login=FND_GLOBAL.USER_ID
where transaction_id = p_transaction_id;
select id into l_location_id
from fte_message_location
where transaction_id=p_transaction_id
and location_to_table = 'FTE_DELIVERY_PROOF';
update fte_delivery_proof
set creation_date = sysdate,
created_by = FND_GLOBAL.USER_ID,
last_update_date=sysdate,
last_updated_by=FND_GLOBAL.USER_ID,
last_update_login=FND_GLOBAL.USER_ID,
location = l_address
where transaction_id= p_transaction_id;
select wsh_new_deliveries.delivery_id, wsh_delivery_legs.delivery_leg_id,
wsh_trips.carrier_id, wsh_delivery_details.container_name,
wsh_delivery_details.delivery_detail_id
into l_delivery_id, l_delivery_leg_id, l_carrier_id, l_container_name,
l_delivery_detail_id
from wsh_new_deliveries, wsh_delivery_legs, wsh_trips, wsh_trip_stops,
wsh_delivery_details, wsh_delivery_assignments
where wsh_delivery_assignments.DELIVERY_ID = wsh_new_deliveries.DELIVERY_ID
and wsh_delivery_assignments.DELIVERY_DETAIL_ID =
wsh_delivery_details.DELIVERY_DETAIL_ID
and wsh_delivery_legs.delivery_id = wsh_new_deliveries.delivery_id
and wsh_delivery_legs.pick_up_stop_id = wsh_trip_stops.stop_id
and wsh_trip_stops.trip_Id = wsh_trips.trip_id
and wsh_trips.carrier_id = l_carrier_id
and wsh_delivery_details.container_name = l_tracking_id
and wsh_delivery_legs.parent_delivery_leg_id is null;--Rel 12 MDC Changes
insert into wsh_delivery_leg_activities
(activity_id, delivery_leg_id, activity_date, activity_type,
creation_date, created_by, last_update_date, last_updated_by)
values
(WSH_DELIVERY_LEG_ACTIVITIES_S.nextval, l_delivery_leg_id, sysdate, 'TRACKING',
sysdate, l_carrier_id, sysdate, l_carrier_id);
update fte_shipment_status_headers
set delivery_id = l_delivery_id,
delivery_leg_id = l_delivery_leg_id,
delivery_detail_id = l_delivery_detail_id,
license_plate_number = l_container_name,
message_type = 'CONTAINER',
creation_date = sysdate,
activity_id = WSH_DELIVERY_LEG_ACTIVITIES_S.currval
where transaction_id = p_transaction_id;
update fte_shipment_status_details
set delivery_leg_id = l_delivery_leg_id,
creation_date = sysdate
where transaction_id = p_transaction_id;
select /*+ORDERED INDEX(wdl WSH_DELIVERY_LEGS_N2)*/
wnd.delivery_id,
wdl.delivery_leg_id,
wt.carrier_id,
wdd.seal_code,
wdd.delivery_detail_id
into l_delivery_id, l_delivery_leg_id, l_carrier_id, l_seal_code,
l_delivery_detail_id
from
wsh_trips wt,
wsh_trip_stops wts,
wsh_delivery_legs wdl,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd
where wda.DELIVERY_ID = wnd.DELIVERY_ID
and wda.DELIVERY_DETAIL_ID = wdd.DELIVERY_DETAIL_ID
and wdl.delivery_id = wnd.delivery_id
and wdl.pick_up_stop_id = wts.stop_id
and wts.trip_Id = wt.trip_id
and wt.carrier_id = l_carrier_id
and wdd.seal_code = l_tracking_id
and wdl.parent_delivery_leg_id is null;--Rel 12 MDC Changes
insert into wsh_delivery_leg_activities
(activity_id, delivery_leg_id, activity_date, activity_type,
creation_date, created_by, last_update_date, last_updated_by)
values
(WSH_DELIVERY_LEG_ACTIVITIES_S.nextval, l_delivery_leg_id, sysdate, 'TRACKING',
sysdate, l_carrier_id, sysdate, l_carrier_id);
select container_name into l_container_name from wsh_delivery_details
where delivery_detail_id = l_delivery_detail_id;
update fte_shipment_status_headers
set delivery_id = l_delivery_id,
delivery_leg_id = l_delivery_leg_id,
delivery_detail_id = l_delivery_detail_id,
container_seal = l_seal_code,
message_type = 'CONTAINER',
creation_date = sysdate,
activity_id = WSH_DELIVERY_LEG_ACTIVITIES_S.currval,
license_plate_number = l_container_name
where transaction_id = p_transaction_id;
update fte_shipment_status_details
set delivery_leg_id = l_delivery_leg_id,
creation_date = sysdate
where transaction_id = p_transaction_id;
select wsh_new_deliveries.delivery_id, wsh_delivery_legs.delivery_leg_id,
wsh_trips.carrier_id, wsh_delivery_details.tracking_number,
wsh_delivery_details.delivery_detail_id
into l_delivery_id, l_delivery_leg_id, l_carrier_id, l_tracking_number,
l_delivery_detail_id
from wsh_new_deliveries, wsh_delivery_legs, wsh_trips,
wsh_trip_stops, wsh_delivery_details, wsh_delivery_assignments
where wsh_delivery_assignments.DELIVERY_ID = wsh_new_deliveries.DELIVERY_ID
and wsh_delivery_assignments.DELIVERY_DETAIL_ID =
wsh_delivery_details.DELIVERY_DETAIL_ID
and wsh_delivery_legs.delivery_id = wsh_new_deliveries.delivery_id
and wsh_delivery_legs.pick_up_stop_id = wsh_trip_stops.stop_id
and wsh_trip_stops.trip_Id = wsh_trips.trip_id
and wsh_trips.carrier_id = l_carrier_id
and wsh_delivery_details.tracking_number = l_tracking_id
and wsh_delivery_legs.parent_delivery_leg_id is null;--Rel 12 MDC Changes
insert into wsh_delivery_leg_activities
(activity_id, delivery_leg_id, activity_date, activity_type,
creation_date, created_by, last_update_date, last_updated_by)
values
(WSH_DELIVERY_LEG_ACTIVITIES_S.nextval, l_delivery_leg_id, sysdate, 'TRACKING',
sysdate, l_carrier_id, sysdate, l_carrier_id);
select container_name into l_container_name from wsh_delivery_details
where delivery_detail_id = l_delivery_detail_id;
update fte_shipment_status_headers
set delivery_id = l_delivery_id,
delivery_leg_id = l_delivery_leg_id,
delivery_detail_id = l_delivery_detail_id,
message_type = 'CONTAINER',
creation_date = sysdate,
activity_id = WSH_DELIVERY_LEG_ACTIVITIES_S.currval,
license_plate_number = l_container_name
where transaction_id = p_transaction_id;
update fte_shipment_status_details
set delivery_leg_id = l_delivery_leg_id,
creation_date = sysdate
where transaction_id = p_transaction_id;
select id into l_location_id
from fte_message_location
where transaction_id=p_transaction_id
and location_to_table = 'FTE_DELIVERY_PROOF';
update fte_delivery_proof
set creation_date = sysdate,
created_by = FND_GLOBAL.USER_ID,
last_update_date=sysdate,
last_updated_by=FND_GLOBAL.USER_ID,
last_update_login=FND_GLOBAL.USER_ID,
location = l_address
where transaction_id= p_transaction_id;
update wsh_trip_stops
set actual_departure_date = l_departure_date,
carrier_est_departure_date = l_estimated_departure_date,
loading_start_datetime = l_begin_loading_date,
loading_end_datetime = l_end_loading_date
where wsh_trip_stops.stop_id =
(select pick_up_stop_id
from wsh_delivery_legs
where delivery_leg_id = l_delivery_leg_id);
update wsh_trip_stops
set actual_arrival_date = l_arrival_date,
carrier_est_arrival_date = l_estimated_arrival_date,
unloading_start_datetime = l_begin_unloading_date,
unloading_end_datetime = l_end_unloading_date
where wsh_trip_stops.stop_id =
(select drop_off_stop_id
from wsh_delivery_legs
where delivery_leg_id = l_delivery_leg_id);
select received_date,1 into l_received_date,l_flag
from
fte_delivery_proof where transaction_id= p_transaction_id;
select wsh_interface_errors_s.nextval into l_error_id
from dual;
FTE_TRACKING_WRAPPER.insert_error_status
(l_error_id, 'FTE_SHIPMEMT_STATUS_HEADERS',
p_transaction_id, 1, 'ERROR', 'INVALID CARRIER',
0);
select wsh_interface_errors_s.nextval into l_error_id
from dual;
FTE_TRACKING_WRAPPER.insert_error_status
(l_error_id, 'FTE_SHIPMEMT_STATUS_HEADERS',
p_transaction_id, 2, 'ERROR', 'NO LICENSE PLATE NUMBER',
0);
select wsh_interface_errors_s.nextval into l_error_id
from dual;
FTE_TRACKING_WRAPPER.insert_error_status
(l_error_id, 'FTE_SHIPMEMT_STATUS_HEADERS',
p_transaction_id, 3, 'ERROR', 'NO DELIVERY FOUND',
l_carrier_id);
select wsh_interface_errors_s.nextval into l_error_id
from dual;
FTE_TRACKING_WRAPPER.insert_error_status
(l_error_id, 'FTE_SHIPMEMT_STATUS_HEADERS',
p_transaction_id, 4, 'ERROR', 'INVALID TRACKING ID TYPE',
l_carrier_id);
select wsh_interface_errors_s.nextval into l_error_id
from dual;
FTE_TRACKING_WRAPPER.insert_error_status
(l_error_id, 'FTE_SHIPMEMT_STATUS_HEADERS',
p_transaction_id, 5, 'ERROR', 'OTHER EXCEPTION',
l_carrier_id);
select delivery_leg_id, creation_date
from fte_shipment_status_headers
where transaction_id = p_transaction_id;
SELECT
fh.transaction_id
FROM
fte_shipment_status_headers fh,
fte_shipment_status_details fs
WHERE
fh.transaction_id = fs.transaction_id and
fh.transaction_id <> c_transaction_id and
fh.delivery_leg_id = c_delivery_leg_id
UNION
SELECT
fp.transaction_id
FROM
fte_shipment_status_headers fh,
fte_delivery_proof fp
WHERE
fh.transaction_id = fp.transaction_id and
fh.transaction_id <> c_transaction_id and
fh.delivery_leg_id = c_delivery_leg_id;
select tracking_id, message_type
into l_tracking_id, l_message_type
from fte_shipment_status_headers
where transaction_id = p_transaction_id;
select shipment_status_detail_id
from fte_shipment_status_details
where delivery_leg_id = l_delivery_leg_id
and transaction_id <> p_transaction_id;
delete from fte_shipment_status_exceptions
where detail_id = l_shipment_detail_id;
select count (*)
into l_rows
from fte_shipment_status_details
where transaction_id = l_previous_transaction_id;
delete
from fte_shipment_status_details
where transaction_id = l_previous_transaction_id;
delete
from fte_delivery_proof where
transaction_id =l_previous_transaction_id;
select tracking_id, message_type
into l_tracking_id, l_message_type
from fte_shipment_status_headers
where transaction_id = p_transaction_id;
select count (*)
into l_rows
from fte_shipment_status_details
where transaction_id = l_previous_transaction_id;
select shipment_status_detail_id
from fte_shipment_status_details
where delivery_leg_id = l_delivery_leg_id
and transaction_id = l_previous_transaction_id;
delete from fte_shipment_status_exceptions
where detail_id = l_shipment_detail_id;
delete
from fte_shipment_status_details
where transaction_id = l_previous_transaction_id;
delete
from fte_delivery_proof where
transaction_id =l_previous_transaction_id;
select wsh_interface_errors_s.nextval into l_error_id
from dual;
FTE_TRACKING_WRAPPER.insert_error_status
(l_error_id, 'FTE_SHIPMEMT_STATUS_DETAILS',
p_transaction_id, 2, 'ERROR', 'NO DELIVERY DETAILS FOUND',
p_carrier_id);
PROCEDURE insert_error_status
(
p_interface_error_id IN NUMBER,
p_interface_table_name IN VARCHAR2,
p_interface_id IN NUMBER,
p_message_code IN NUMBER,
p_message_name IN VARCHAR2,
p_error_message IN VARCHAR2,
p_carrier_id IN NUMBER
)
IS
BEGIN
-- message_code 0 = completed with success, 1 = completed with error
INSERT INTO WSH_INTERFACE_ERRORS
(INTERFACE_ERROR_ID, INTERFACE_ERROR_GROUP_ID, INTERFACE_TABLE_NAME,
INTERFACE_ID, MESSAGE_CODE, MESSAGE_NAME, ERROR_MESSAGE,
CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
VALUES (p_interface_error_id, 1, p_interface_table_name,
p_interface_id, p_message_code, p_message_name, p_error_message,
sysdate, p_carrier_id, sysdate, p_carrier_id);
END insert_error_status;
SELECT
DELIVERY_LEG_ID, DROP_OFF_STOP_ID,
WTL.STATUS_CODE,WND.ULTIMATE_DROPOFF_LOCATION_ID,
WTL.STOP_LOCATION_ID, WND.ORGANIZATION_ID, WND.DELIVERY_ID
FROM
WSH_DELIVERY_LEGS WDL ,
WSH_NEW_DELIVERIES WND,
WSH_TRIP_STOPS WTL
WHERE
WND.DELIVERY_ID= WDL.DELIVERY_ID AND
WDL.DROP_OFF_STOP_ID=WTL.STOP_ID AND
WDL.delivery_leg_id = l_delivery_leg_id;
SELECT wnd.delivery_id, wnd.organization_id FROM
wsh_delivery_legs wdl, wsh_new_deliveries wnd
WHERE wdl.delivery_id = wnd.delivery_id AND
wdl.delivery_leg_id = c_delivery_leg_id;
-- so no need to update pod flag
IF (l_last_stop_location_id = l_dlvy_ud_location_id) THEN
l_in_rec.action_code := 'UPDATE';
WSH_INTERFACE_GRP.Update_Delivery_Leg(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => NULL,
p_delivery_leg_tab => l_leg_Tab,
p_in_rec => l_in_rec,
x_out_rec => l_out_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
WSH_DEBUG_SV.logmsg(l_module_name,' Stop is closed nothing to update ' || l_last_stop_id);
p_action_prms.action_code := 'UPDATE-STATUS';