[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE setARMessageUpdateTable(p_tableName IN VARCHAR2)
IS
BEGIN
arp_message.set_name('AR','AR_UPDATING_TABLE');
END setARMessageUpdateTable;
arp_message.set_name('AR','AR_ROWS_UPDATED');
PROCEDURE Insert_Log_Table (
p_id_tab IN g_number_tbl_type,
p_table_name IN VARCHAR2,
p_req_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2 )
IS
--
l_debug_on BOOLEAN;
arp_message.set_line('WSH_CUST_MERGE.Insert_Log_Table()+' || getTimeStamp );
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID,
NUM_COL1_ORIG,
NUM_COL1_NEW,
NUM_COL2_ORIG,
NUM_COL2_NEW,
NUM_COL3_ORIG,
NUM_COL3_NEW,
NUM_COL4_ORIG,
NUM_COL4_NEW,
NUM_COL5_ORIG,
NUM_COL5_NEW,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY )
VALUES (
HZ_CUSTOMER_MERGE_LOG_S.NextVal,
p_table_name,
G_MERGE_HEADER_ID,
p_id_tab(insCnt),
G_FROM_CUSTOMER_ID,
G_TO_CUSTOMER_ID,
G_FROM_CUST_SITE_ID,
G_TO_CUST_SITE_ID,
G_FROM_LOCATION_ID,
G_TO_LOCATION_ID,
NULL,
NULL,
NULL,
NULL,
'U',
p_req_id,
HZ_UTILITY_PUB.Created_By,
HZ_UTILITY_PUB.Creation_Date,
HZ_UTILITY_PUB.Last_Update_Login,
HZ_UTILITY_PUB.Last_Update_Date,
HZ_UTILITY_PUB.Last_Updated_By );
arp_message.set_line('WSH_CUST_MERGE.Insert_Log_Table()+' || getTimeStamp );
ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Insert_Log_Table()+ Others - ' || getTimeStamp);
END Insert_Log_Table;
SELECT Det.Delivery_Detail_Id, Det.Rowid,
Dlvy.Customer_Id
FROM Wsh_Delivery_Details Det,
Wsh_Delivery_Assignments DelAsgn,
Wsh_New_Deliveries Dlvy
WHERE Dlvy.Delivery_Id = DelAsgn.Delivery_Id
AND DelAsgn.Delivery_Detail_Id = Det.Delivery_Detail_Id
AND Det.Container_Flag = 'Y'
AND Det.Delivery_Detail_Id IN
( SELECT Asgn.Parent_Delivery_Detail_Id
FROM Wsh_Delivery_Assignments Asgn
WHERE Asgn.Parent_Delivery_Detail_Id IS NOT NULL
CONNECT BY PRIOR Asgn.Parent_Delivery_Detail_Id = Asgn.Delivery_Detail_Id
START WITH Asgn.delivery_detail_id IN
( SELECT Wdd.Delivery_Detail_Id
FROM WSH_DELIVERY_DETAILS Wdd,
WSH_DELIVERY_ASSIGNMENTS Wda,
WSH_NEW_DELIVERIES Wnd,
Wsh_Tmp Tmp
WHERE Wdd.Container_Flag = 'N'
AND Wdd.Released_Status IN ( 'R', 'N', 'X', 'Y', 'S', 'B' )
AND Wdd.Customer_id = p_customer_id
AND Wdd.Ship_To_Location_Id = p_location_id
AND Wdd.Ship_To_Site_Use_Id = p_site_use_id
AND Wdd.Delivery_Detail_Id = Wda.Delivery_Detail_Id
AND Wda.delivery_id is not null
AND Wda.Delivery_Id = Wnd.Delivery_Id
AND Wnd.Ultimate_Dropoff_Location_Id <> p_location_id
AND Wnd.Status_Code = 'OP'
AND Wnd.Delivery_Id = Tmp.Column1
AND exists
( SELECT 'x'
FROM WSH_DELIVERY_ASSIGNMENTS WDA1,
WSH_DELIVERY_DETAILS WDD1
WHERE WDD1.DELIVERY_DETAIL_ID = WDA1.DELIVERY_DETAIL_ID
AND WDD1.Container_Flag = 'N'
AND WDA1.Delivery_Id = WND.Delivery_Id
AND WDD1.Ship_To_Location_id = WND.Ultimate_Dropoff_Location_Id ) ) )
FOR UPDATE OF Det.Delivery_Detail_Id NOWAIT;
SELECT Wda.rowid, Wfc.rowid, Wda.Delivery_id, Wnd.Name,
Wdd.Delivery_Detail_Id, Wda.Parent_Delivery_Detail_Id,
Wdd.Gross_Weight, Wdd.Net_Weight, Wdd.Volume,
Wdd.Weight_Uom_Code, Wdd.Volume_Uom_Code,
Wdd.Organization_Id, Wdd.Inventory_Item_Id,
Wdd.Move_Order_Line_Id, Wdd.Released_Status,
Wnd.Ignore_For_Planning -- OTM R12 : unassign delivery detail
FROM WSH_DELIVERY_DETAILS Wdd,
WSH_DELIVERY_ASSIGNMENTS Wda,
WSH_NEW_DELIVERIES Wnd,
Wsh_Freight_Costs Wfc,
Wsh_Tmp Tmp
WHERE Wdd.Container_Flag = 'N'
AND Wdd.Released_Status IN ( 'R', 'N', 'X', 'Y', 'S', 'B' )
AND Wdd.Customer_id = p_customer_id
AND Wdd.Ship_To_Location_Id = p_location_id
AND Wdd.Ship_To_Site_Use_Id = p_site_use_id
AND NVL(Wfc.Charge_Source_Code, 'PRICING_ENGINE') = 'PRICING_ENGINE'
AND Wfc.Delivery_Detail_Id (+) = Wdd.Delivery_Detail_Id
AND Wdd.Delivery_Detail_Id = Wda.Delivery_Detail_Id
AND Wda.delivery_id is not null
AND Wda.Delivery_Id = Wnd.Delivery_Id
AND Wnd.Ultimate_Dropoff_Location_Id <> p_location_id
AND Wnd.Status_Code = 'OP'
AND Wnd.Delivery_Id = Tmp.Column1
AND exists (
SELECT 'x'
FROM WSH_DELIVERY_ASSIGNMENTS WDA1,
WSH_DELIVERY_DETAILS WDD1
WHERE WDD1.DELIVERY_DETAIL_ID = WDA1.DELIVERY_DETAIL_ID
AND WDD1.Container_Flag = 'N'
AND WDA1.Delivery_Id = WND.Delivery_Id
AND WDD1.Ship_To_Location_id = WND.Ultimate_Dropoff_Location_Id)
FOR UPDATE OF Wda.Delivery_Detail_Id, Wfc.Freight_Cost_Id NOWAIT;
SELECT Wsh_Delivery_Group_S.NEXTVAL
FROM Dual;
UPDATE Wsh_Delivery_Details Wdd
SET Customer_Id = l_customerIdTab(updCnt),
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.conc_login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = SYSDATE
WHERE rowid = l_deliveryDetailRowidTab(updCnt);
l_dummyTab.delete;
UPDATE WSH_DELIVERY_ASSIGNMENTS
SET parent_delivery_detail_id = null,
delivery_id = null,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.conc_login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = SYSDATE
WHERE rowid = l_deliveryAssgRowidTab(unassignDelCnt);
l_delivery_id_tab.DELETE;
l_interface_flag_tab.DELETE;
l_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED;
WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG(
p_delivery_id_tab => l_delivery_id_tab,
p_tms_interface_flag_tab => l_interface_flag_tab,
x_return_status => l_return_status);
ARP_MESSAGE.Set_Error('Error returned from API WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG '||l_return_status);
INV_MO_Cancel_PVT.Update_Mol_Carton_Group (
x_return_status => l_return_status,
x_msg_cnt => l_msg_count,
x_msg_data => l_msg_data,
p_line_id => l_moveOrderLineIdTab(wmsCnt),
p_carton_grouping_id => l_carton_grouping_id );
ARP_MESSAGE.Set_Error('Error returned from API INV_MO_Cancel_PVT.Update_Mol_Carton_Group');
DELETE FROM Wsh_Freight_Costs
WHERE Rowid = l_freightCostRowidTab(delCnt);
Insert_Log_Table (
p_id_tab => l_deliveryDetailIdTab,
p_table_name => 'WSH_DELIVERY_ASSIGNMENTS',
p_req_id => p_req_id,
x_return_status => l_return_status );
DELETE FROM wsh_tmp WHERE column1 = l_deliveryIdTab(delCnt);
SELECT to_number(Column1) delivery_id, Column3
FROM Wsh_Tmp;
Update_Hash_Exp EXCEPTION;
RAISE Update_Hash_Exp;
WHEN Update_Hash_Exp THEN
x_return_status := l_return_status;
ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Get_Delivery_Hash()+ Update_Hash_Exp - ' || getTimeStamp );
SELECT Wdd.Delivery_Detail_Id, Wdd.Rowid
FROM Wsh_Delivery_Details Wdd,
Wsh_Delivery_Assignments Wda,
Wsh_Tmp Tmp
WHERE Wdd.Container_Flag = 'Y'
AND Wdd.Delivery_Detail_Id = Wda.Parent_Delivery_Detail_Id
AND Parent_Delivery_Detail_Id IS NOT NULL
AND Wda.Delivery_Id = Tmp.Column1
FOR UPDATE OF Wda.Delivery_Detail_Id NOWAIT;
SELECT Wdl.Delivery_Id, Wdl.Drop_Off_Stop_Id, Wts.Trip_Id,
Wdl.Delivery_Leg_Id, Wnd.Gross_Weight, Wnd.Net_Weight,
Wnd.Volume, Wdl.Rowid, Tmp.Rowid,
NVL(Wnd.ignore_for_planning, 'N') --OTM R12
FROM Wsh_New_Deliveries Wnd,
Wsh_Delivery_Legs Wdl,
Wsh_Trip_Stops Wts,
Wsh_Tmp Tmp
WHERE Wnd.Ultimate_Dropoff_Location_Id = p_location_id
AND Wts.Stop_Id = Wdl.Drop_Off_Stop_Id
AND Wnd.Delivery_Id = Wdl.Delivery_Id
AND Wdl.Delivery_Id = Tmp.Column1
AND exists (
SELECT 'x'
FROM Wsh_New_Deliveries Del,
Wsh_Delivery_Legs Legs
WHERE Del.Ultimate_Dropoff_Location_Id <> p_location_id
AND Del.Delivery_Id = Legs.Delivery_Id
AND Legs.Drop_Off_Stop_Id = Wdl.Drop_Off_Stop_Id
)
FOR UPDATE OF Wdl.Delivery_Leg_Id NOWAIT;
SELECT wdl.delivery_id
FROM wsh_delivery_legs wdl,
wsh_trip_stops wts,
wsh_new_deliveries wnd
WHERE wdl.pick_up_stop_id = wts.stop_id
AND wts.trip_id = p_trip_id
AND wdl.delivery_id = wnd.delivery_id
AND wnd.status_code = 'OP'
AND wnd.tms_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED
AND wnd.delivery_id <> p_exclude_dlvy;
SELECT wnd.delivery_id
FROM wsh_new_deliveries wnd
WHERE NVL(wnd.Customer_Id, p_customer_id) = p_customer_id
AND wnd.ultimate_dropoff_location_Id = p_dropoff_location_id
AND wnd.status_code = 'OP'
AND NOT EXISTS
(
SELECT 1
FROM 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 wdd.container_flag = 'N'
);
SELECT freight_cost_type_id
FROM wsh_freight_cost_types
WHERE name = 'OTM Freight Cost'
AND freight_cost_type_code = 'FREIGHT';
l_tms_update WSH_UTIL_CORE.COLUMN_TAB_TYPE;
Update_Del_Exp EXCEPTION;
UPDATE Wsh_Delivery_Details Wdd
SET customer_id = decode( customer_id,
G_FROM_CUSTOMER_ID, G_TO_CUSTOMER_ID,
customer_id ),
ship_to_location_id = decode( ship_to_location_id,
G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
ship_to_location_id ),
deliver_to_location_id = decode( deliver_to_location_id,
G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
deliver_to_location_id ),
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login,
request_id = p_req_id,
program_application_id = arp_standard.profile.program_application_id ,
program_id = arp_standard.profile.program_id,
program_update_date = SYSDATE
WHERE Wdd.Rowid = l_deliveryDetailRowidTab(updCnt);
Insert_Log_Table (
p_id_tab => l_deliveryDetailIdTab,
p_table_name => 'WSH_DELIVERY_DETAILS',
p_req_id => p_req_id,
x_return_status => l_return_status );
Raise Update_Del_Exp;
l_hash_value_tab.DELETE;
l_hash_string_tab.DELETE;
l_delivery_id_tab.DELETE;
Raise Update_Del_Exp;
l_tms_update(l_index) := 'N';
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED)) THEN
l_tms_update(l_index) := 'Y';
l_new_interface_flag_tab(l_delivery_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
||' l_tms_update-'||l_tms_update(l_index)
||' l_tms_interface_flag-'||l_tms_interface_flag(l_index)
||' l_tms_version_number-'||l_tms_version_number(l_index));
arp_message.set_line('l_tms_update-'||l_tms_update(l_index)
||' l_tms_interface_flag-'||l_tms_interface_flag(l_index)
||' l_tms_version_number-'||l_tms_version_number(l_index));
UPDATE WSH_NEW_DELIVERIES Wnd
SET Hash_Value = nvl(l_hash_value_tab(updCnt), Hash_Value),
Hash_String = nvl(l_hash_string_tab(updCnt), Hash_String),
customer_id = decode(customer_id,
G_FROM_CUSTOMER_ID, G_TO_CUSTOMER_ID,
customer_id),
ultimate_dropoff_location_id = decode(ultimate_dropoff_location_id,
G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
ultimate_dropoff_location_id ),
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login,
request_id = p_req_id,
program_application_id = arp_standard.profile.program_application_id ,
program_id = arp_standard.profile.program_id,
program_update_date = SYSDATE,
-- OTM R12 : update delivery
TMS_INTERFACE_FLAG = decode(l_tms_update(updCnt), 'Y',
l_tms_interface_flag(updCnt),
nvl(TMS_INTERFACE_FLAG, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
TMS_VERSION_NUMBER = decode(l_tms_update(updCnt), 'Y',
l_tms_version_number(updCnt),
nvl(TMS_VERSION_NUMBER, 1))
-- End of OTM R12 : update delivery
WHERE Delivery_Id = l_delivery_id_tab(updCnt)
RETURNING Wnd.Delivery_Id BULK COLLECT INTO l_deliveryIdTab;
WSH_DEBUG_SV.log('my module', 'rows updated', l_deliveryIdTab.COUNT);
WSH_DEBUG_SV.log('my module', 'rows suppose to update', l_hash_value_tab.COUNT);
Insert_Log_Table (
p_id_tab => l_deliveryIdTab,
p_table_name => 'WSH_NEW_DELIVERIES',
p_req_id => p_req_id,
x_return_status => l_return_status );
Raise Update_Del_Exp;
RAISE Update_Del_Exp;
DELETE FROM Wsh_Delivery_Legs
WHERE Rowid = l_new_delivery_leg_tab(delCnt);
DELETE FROM Wsh_Freight_Costs
WHERE Delivery_Leg_Id = l_deliveryLegIdTab(delCnt);
DELETE FROM Wsh_Tmp
WHERE Rowid = l_tempRowidTab(delCnt);
WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED,
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS) AND
l_tms_delivery_info_tab(l_index).status_code = 'OP' AND
l_tms_trip_tab(l_index).status_code = 'OP') THEN
-- it is already assigned to a trip,
-- there will be only one OTM trip for delivery
l_dlvy_id_tab(l_dlvy_id_tab.COUNT+1) := l_tms_delivery_info_tab(l_index).delivery_id;
WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG(
p_delivery_id_tab => l_aw_dlvy_tab,
p_tms_interface_flag_tab => l_aw_interface_flag_tab,
x_return_status => l_return_status);
ARP_MESSAGE.Set_Error('Error returned from API WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG '||l_return_status);
RAISE Update_Del_Exp;
DELETE FROM WSH_DELIVERY_LEGS
WHERE DELIVERY_ID = l_dlvy_id_tab(delCnt);
DELETE FROM Wsh_Freight_Costs
WHERE Delivery_Leg_Id IN
(SELECT wdl.delivery_leg_id
FROM wsh_delivery_legs wdl
WHERE wdl.delivery_id = l_dlvy_id_tab(delCnt))
AND freight_cost_type_id = l_frcost_type_id;
DELETE FROM Wsh_Freight_Costs
WHERE Delivery_Id = l_dlvy_id_tab(delCnt)
AND freight_cost_type_id = l_frcost_type_id;
RAISE Update_Del_Exp;
DELETE FROM wsh_tmp
WHERE column1 = l_dlvy_id_tab(delCnt);
UPDATE WSH_TRIP_STOPS Wts
SET stop_location_id = G_TO_LOCATION_ID,
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login,
request_id = p_req_id,
program_application_id = arp_standard.profile.program_application_id ,
program_id = arp_standard.profile.program_id,
program_update_date = SYSDATE
WHERE Wts.Stop_Id in (
SELECT Column2
FROM WSH_TMP
WHERE Column2 IS NOT NULL )
RETURNING Wts.Stop_Id BULK COLLECT INTO l_stopIdTab;
Insert_Log_Table (
p_id_tab => l_stopIdTab,
p_table_name => 'WSH_TRIP_STOPS',
p_req_id => p_req_id,
x_return_status => l_return_status );
Raise Update_Del_Exp;
WHEN Update_Del_Exp THEN
x_return_status := l_return_status;
ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Process_Deliveries()+ Update_Del_Exp - ' || getTimeStamp );
SELECT Wnd.Delivery_Id, Wnd.Rowid
FROM Wsh_New_Deliveries WND
WHERE nvl(Wnd.Customer_Id, p_customer_id) = p_customer_id
AND Wnd.Ultimate_Dropoff_Location_Id = p_location_id
AND Wnd.Status_Code = 'OP'
AND NOT EXISTS
( SELECT 'x' FROM Wsh_Delivery_Assignments Wda
WHERE Wda.Delivery_Id = Wnd.Delivery_Id )
FOR UPDATE OF Wnd.Delivery_Id NOWAIT;
SELECT Det.Delivery_Detail_Id, Dlvy.Delivery_Id,
Det.Rowid, Dlvy.Rowid
FROM Wsh_Delivery_Details Det,
Wsh_Delivery_Assignments Asg,
Wsh_New_Deliveries Dlvy
WHERE Det.Delivery_Detail_Id = Asg.Delivery_Detail_Id
AND Asg.Delivery_Id = Dlvy.Delivery_Id
AND Dlvy.Delivery_Id in
( SELECT Wnd.Delivery_Id
FROM Wsh_Delivery_Details Wdd,
Wsh_Delivery_Assignments Wda,
Wsh_New_Deliveries Wnd
WHERE Wdd.Container_Flag = 'Y'
AND Wdd.Delivery_Detail_Id = Wda.Delivery_Detail_id
AND Wda.Delivery_Id = Wnd.Delivery_Id
AND nvl(Wnd.Customer_Id, p_customer_id) = p_customer_id
AND Wnd.Ultimate_Dropoff_Location_Id = p_location_id
AND Wnd.Status_Code = 'OP'
AND NOT EXISTS
( SELECT 'X'
FROM Wsh_Delivery_Assignments Asgn
WHERE Asgn.Parent_Delivery_Detail_Id = Wdd.Delivery_Detail_Id
AND Asgn.Delivery_Id = Wnd.Delivery_Id )
AND NOT EXISTS
( SELECT 'X'
FROM Wsh_Delivery_Details dd,
Wsh_Delivery_Assignments da
WHERE dd.Container_Flag = 'N'
AND dd.Delivery_Detail_Id = da.Delivery_Detail_Id
AND da.Delivery_Id = Wnd.Delivery_Id ) )
FOR UPDATE OF Det.Delivery_Detail_Id, Dlvy.Delivery_Id NOWAIT;
UPDATE Wsh_New_Deliveries
SET customer_id = decode(customer_id,
G_FROM_CUSTOMER_ID, G_TO_CUSTOMER_ID,
customer_id),
ultimate_dropoff_location_id = decode(ultimate_dropoff_location_id,
G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
ultimate_dropoff_location_id ),
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login,
request_id = p_req_id,
program_application_id = arp_standard.profile.program_application_id ,
program_id = arp_standard.profile.program_id,
program_update_date = SYSDATE
WHERE Rowid = l_deliveryRowidTab(updCnt);
Insert_Log_Table (
p_id_tab => l_deliveryIdTab,
p_table_name => 'WSH_NEW_DELIVERIES',
p_req_id => p_req_id,
x_return_status => l_return_status );
UPDATE Wsh_New_Deliveries
SET customer_id = decode(customer_id,
G_FROM_CUSTOMER_ID, G_TO_CUSTOMER_ID,
customer_id),
ultimate_dropoff_location_id = decode(ultimate_dropoff_location_id,
G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
ultimate_dropoff_location_id ),
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login,
request_id = p_req_id,
program_application_id = arp_standard.profile.program_application_id ,
program_id = arp_standard.profile.program_id,
program_update_date = SYSDATE
WHERE Rowid = l_deliveryRowidTab(updCnt);
Insert_Log_Table (
p_id_tab => l_deliveryIdTab,
p_table_name => 'WSH_NEW_DELIVERIES',
p_req_id => p_req_id,
x_return_status => l_return_status );
UPDATE Wsh_Delivery_Details Wdd
SET customer_id = decode( customer_id,
G_FROM_CUSTOMER_ID, G_TO_CUSTOMER_ID,
customer_id ),
ship_to_location_id = decode( ship_to_location_id,
G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
ship_to_location_id ),
deliver_to_location_id = decode( deliver_to_location_id,
G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
deliver_to_location_id ),
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login,
request_id = p_req_id,
program_application_id = arp_standard.profile.program_application_id ,
program_id = arp_standard.profile.program_id,
program_update_date = SYSDATE
WHERE Wdd.Rowid = l_deliveryDetailRowidTab(updCnt);
Insert_Log_Table (
p_id_tab => l_deliveryDetailIdTab,
p_table_name => 'WSH_DELIVERY_DETAILS',
p_req_id => p_req_id,
x_return_status => l_return_status );
SELECT WDD.Delivery_Detail_Id, WDA.Parent_Delivery_Detail_Id,
WND.Delivery_Id, WTS.Stop_Id,
WDD.Net_Weight, WDD.Gross_Weight,
WDD.Volume, Wdd.Weight_Uom_code, Wdd.Volume_Uom_Code,
Wdd.Inventory_Item_Id, Wdd.Organization_Id,
WDD.Rowid, WDA.Rowid
FROM Wsh_Delivery_Details WDD,
Wsh_Delivery_Assignments WDA,
Wsh_New_Deliveries WND,
Wsh_Delivery_Legs WDL,
Wsh_Trip_Stops WTS
WHERE WTS.Stop_id (+) = WDL.Drop_Off_Stop_Id
AND WDL.Delivery_Id (+) = WND.Delivery_Id
AND NVL(WND.Status_Code, 'OP') = 'OP'
AND WND.Delivery_Id (+) = WDA.Delivery_Id
AND WDA.Delivery_Detail_Id = WDD.Delivery_Detail_Id
AND WDD.Container_Flag = 'N'
AND (WDD.Customer_Id = p_customer_id
--Bug 5900667: Reverting back from AND to OR
OR ((WDD.Ship_To_Site_Use_Id = p_site_use_id
AND WDD.Ship_To_Location_Id = p_location_id )
OR (WDD.Deliver_To_Site_Use_Id = p_site_use_id
AND WDD.Deliver_To_Location_Id = p_location_id )))
AND WDD.Released_Status IN ( 'R', 'N', 'X', 'Y', 'S', 'B' )
FOR UPDATE OF Wdd.Delivery_Detail_Id, Wda.Delivery_Detail_Id, Wnd.Delivery_Id, Wts.Stop_Id NOWAIT;
UPDATE Wsh_Delivery_Details Wdd
SET customer_id = decode( customer_id,
G_FROM_CUSTOMER_ID, G_TO_CUSTOMER_ID,
customer_id ),
ship_to_site_use_id = decode( ship_to_site_use_id,
G_FROM_CUST_SITE_ID, G_TO_CUST_SITE_ID,
ship_to_site_use_id ),
deliver_to_site_use_id = decode( nvl(deliver_to_site_use_id, ship_to_site_use_id),
G_FROM_CUST_SITE_ID, G_TO_CUST_SITE_ID,
deliver_to_site_use_id ),
ship_to_location_id = decode( ship_to_site_use_id,
G_FROM_CUST_SITE_ID,
decode(ship_to_location_id,
G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
ship_to_location_id ),
ship_to_location_id ),
deliver_to_location_id = decode( nvl(deliver_to_site_use_id, ship_to_site_use_id),
G_FROM_CUST_SITE_ID,
decode(deliver_to_location_id,
G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
deliver_to_location_id ),
deliver_to_location_id ),
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login,
request_id = p_req_id,
program_application_id = arp_standard.profile.program_application_id ,
program_id = arp_standard.profile.program_id,
program_update_date = SYSDATE
WHERE Wdd.Rowid = l_deliveryDetailRowidTab(bulkCnt);
Insert_Log_Table (
p_id_tab => l_deliveryDetailIdTab,
p_table_name => 'WSH_DELIVERY_DETAILS',
p_req_id => p_req_id,
x_return_status => l_return_status );
l_dummyIdTab.delete;
UPDATE WSH_DELIVERY_ASSIGNMENTS
SET parent_delivery_detail_id = null,
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login,
program_application_id = arp_standard.profile.program_application_id,
program_id = arp_standard.profile.program_id,
program_update_date = SYSDATE
WHERE rowid = l_deliveryAssgRowidTab(unassignCnt)
AND Parent_Delivery_Detail_Id IS NOT NULL
AND Delivery_Id IS NULL;
l_dummyIdTab.delete;
INSERT INTO Wsh_Tmp ( Column1, Column2, Column3 )
SELECT l_deliveryIdTab(insCnt), l_stopIdTab(insCnt), l_deliveryDetailIdTab(insCnt)
FROM DUAL
WHERE l_deliveryIdTab(insCnt) IS NOT NULL
--Start of fix for bug 5900667
--Populate details of delivery/stop into table only if location matches.
AND EXISTS
( SELECT 'x'
FROM Wsh_New_Deliveries
WHERE ultimate_dropoff_location_id = G_FROM_LOCATION_ID
AND delivery_id = l_deliveryIdTab(insCnt) )
--End of fix for bug 5900667
AND NOT EXISTS
( SELECT 'x'
FROM Wsh_Tmp
WHERE Column1 = l_deliveryIdTab(insCnt)
AND ( Column2 = l_stopIdTab(insCnt) OR l_stopIdTab(insCnt) is null ) );
SELECT COUNT(*)
INTO l_tmp_cnt
FROM WSH_TMP;
DELETE FROM Wsh_Tmp;
SELECT Det.Delivery_Detail_Id, Det.Rowid
FROM Wsh_Delivery_Details Det
WHERE Det.Container_Flag = 'Y'
AND Det.Delivery_Detail_Id IN
( SELECT Wda.Parent_Delivery_Detail_Id
FROM Wsh_Delivery_Assignments Wda
WHERE Wda.Parent_Delivery_Detail_Id IS NOT NULL
CONNECT BY PRIOR Wda.Parent_Delivery_Detail_Id = Wda.Delivery_Detail_Id
START WITH wda.delivery_detail_id IN
( SELECT WDD.Delivery_Detail_Id
FROM Wsh_Delivery_Details WDD,
Wsh_Delivery_Assignments WDA,
Wsh_New_Deliveries WND
WHERE Wnd.Status_Code in ( 'CO', 'CL', 'IT' )
-- Added Parent_Delivery_Detail_Id for Perf. improvement,
-- as per perf. team suggestion.
AND Wda.Parent_Delivery_Detail_Id IS NOT NULL
AND Wnd.Delivery_Id = Wda.Delivery_Id
AND Wda.Delivery_Detail_Id = Wdd.Delivery_Detail_Id
AND Wdd.Container_Flag = 'N'
AND (Wdd.Customer_Id = p_customer_id
--Bug 5900667: Reverting back from AND to OR
OR ((Wdd.Ship_To_Site_Use_Id = p_site_use_id AND
Wdd.Ship_To_Location_Id = p_location_id )
OR (Wdd.Deliver_To_Site_Use_Id = p_site_use_id AND
Wdd.Deliver_To_Location_Id = p_location_id)))
AND Wdd.Released_Status in ( 'Y', 'C', 'X' ) ) )
FOR UPDATE OF Det.Delivery_Detail_Id NOWAIT;
SELECT Wdd.Delivery_Detail_Id, Wdd.Rowid
FROM Wsh_Delivery_Details WDD,
Wsh_Delivery_Assignments WDA,
Wsh_New_Deliveries WND
WHERE Wnd.Status_Code in ( 'CO', 'CL', 'IT' )
AND Wnd.Delivery_Id = Wda.Delivery_Id
AND Wda.Delivery_Detail_Id = Wdd.Delivery_Detail_Id
AND Wdd.Container_Flag = 'N'
AND (Wdd.Customer_Id = p_customer_id
--Bug 5900667: Reverting back from AND to OR
OR ((Wdd.Ship_To_Site_Use_Id = p_site_use_id
AND Wdd.Ship_To_Location_Id = p_location_id )
OR (Wdd.Deliver_To_Site_Use_Id = p_site_use_id
AND Wdd.Deliver_To_Location_Id = p_location_id)))
AND Wdd.Released_Status in ( 'Y', 'C', 'X' )
FOR UPDATE OF Wdd.Delivery_Detail_Id NOWAIT;
SELECT duplicate_id, customer_id,
duplicate_site_id, customer_site_id, customer_merge_header_id,
customer_site_code
BULK COLLECT INTO l_fromCustomerIdTab, l_toCustomerIdTab,
l_fromCustomerSiteIdTab, l_toCustomerSiteIdTab, l_orgcustomerMergeHeaderIdTab,
l_customerSiteCodeTab
FROM ra_customer_merges
WHERE process_flag = 'N'
AND customer_site_code in ( 'SHIP_TO', 'DELIVER_TO' )
AND request_id = Req_Id
AND set_number = Set_Num;
UPDATE Wsh_Delivery_Details WDD
SET customer_id = decode(customer_id, G_FROM_CUSTOMER_ID,
G_TO_CUSTOMER_ID, customer_id ),
ship_to_site_use_id = decode(ship_to_site_use_id,
G_FROM_CUST_SITE_ID, G_TO_CUST_SITE_ID,
ship_to_site_use_id ),
deliver_to_site_use_id = decode( nvl(deliver_to_site_use_id, ship_to_site_use_id),
G_FROM_CUST_SITE_ID, G_TO_CUST_SITE_ID,
deliver_to_site_use_id ),
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id = arp_standard.profile.program_application_id ,
program_id = arp_standard.profile.program_id,
program_update_date = SYSDATE
WHERE Wdd.Container_Flag = 'Y'
AND Wdd.Rowid = l_deliveryDetailRowidTab(bulkCnt);
Insert_Log_Table (
p_id_tab => l_deliveryDetailIdTab,
p_table_name => 'WSH_DELIVERY_DETAILS',
p_req_id => req_id,
x_return_status => l_return_status );
UPDATE Wsh_Delivery_Details Wdd
SET customer_id = decode(customer_id, G_FROM_CUSTOMER_ID,
G_TO_CUSTOMER_ID, customer_id ),
ship_to_site_use_id = decode(ship_to_site_use_id,
G_FROM_CUST_SITE_ID, G_TO_CUST_SITE_ID,
ship_to_site_use_id ),
deliver_to_site_use_id = decode( nvl(deliver_to_site_use_id, ship_to_site_use_id),
G_FROM_CUST_SITE_ID, G_TO_CUST_SITE_ID,
deliver_to_site_use_id ),
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id = arp_standard.profile.program_application_id ,
program_id = arp_standard.profile.program_id,
program_update_date = SYSDATE
WHERE Wdd.Rowid = l_deliveryDetailRowidTab(bulkCnt);
Insert_Log_Table (
p_id_tab => l_deliveryDetailIdTab,
p_table_name => 'WSH_DELIVERY_DETAILS',
p_req_id => req_id,
x_return_status => l_return_status );
ARP_MESSAGE.Set_Error('Error returned from API Insert_Log_Table');
DELETE FROM Wsh_Tmp;
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,BATCH_ID
,yt.customer_id
FROM WSH_PICKING_BATCHES yt, ra_customer_merges m
WHERE (
yt.customer_id = m.DUPLICATE_ID
) AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID,
NUM_COL1_ORIG,
NUM_COL1_NEW,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
'WSH_PICKING_BATCHES',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY_ID_LIST(I),
NUM_COL1_ORIG_LIST(I),
NUM_COL1_NEW_LIST(I),
'U',
req_id,
hz_utility_pub.CREATED_BY,
hz_utility_pub.CREATION_DATE,
hz_utility_pub.LAST_UPDATE_LOGIN,
hz_utility_pub.LAST_UPDATE_DATE,
hz_utility_pub.LAST_UPDATED_BY
);
UPDATE WSH_PICKING_BATCHES yt SET
customer_id=NUM_COL1_NEW_LIST(I)
, LAST_UPDATE_DATE=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
, REQUEST_ID=req_id
, PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
, PROGRAM_ID=arp_standard.profile.program_id
, PROGRAM_UPDATE_DATE=SYSDATE
WHERE BATCH_ID=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,CALENDAR_ASSIGNMENT_ID
,yt.customer_id
,yt.customer_site_use_id
FROM WSH_CALENDAR_ASSIGNMENTS yt, ra_customer_merges m
WHERE (
yt.customer_id = m.DUPLICATE_ID
OR yt.customer_site_use_id = m.DUPLICATE_SITE_ID
) AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID,
NUM_COL1_ORIG,
NUM_COL1_NEW,
NUM_COL2_ORIG,
NUM_COL2_NEW,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
'WSH_CALENDAR_ASSIGNMENTS',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY_ID_LIST(I),
NUM_COL1_ORIG_LIST(I),
NUM_COL1_NEW_LIST(I),
NUM_COL2_ORIG_LIST(I),
NUM_COL2_NEW_LIST(I),
'U',
req_id,
hz_utility_pub.CREATED_BY,
hz_utility_pub.CREATION_DATE,
hz_utility_pub.LAST_UPDATE_LOGIN,
hz_utility_pub.LAST_UPDATE_DATE,
hz_utility_pub.LAST_UPDATED_BY
);
UPDATE WSH_CALENDAR_ASSIGNMENTS yt SET
customer_id=NUM_COL1_NEW_LIST(I)
,customer_site_use_id=NUM_COL2_NEW_LIST(I)
, LAST_UPDATE_DATE=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
, REQUEST_ID=req_id
, PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
, PROGRAM_ID=arp_standard.profile.program_id
, PROGRAM_UPDATE_DATE=SYSDATE
WHERE CALENDAR_ASSIGNMENT_ID=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,PICKING_RULE_ID
,yt.customer_id
FROM WSH_PICKING_RULES yt, ra_customer_merges m
WHERE (
yt.customer_id = m.DUPLICATE_ID
) AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID,
NUM_COL1_ORIG,
NUM_COL1_NEW,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
'WSH_PICKING_RULES',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY_ID_LIST(I),
NUM_COL1_ORIG_LIST(I),
NUM_COL1_NEW_LIST(I),
'U',
req_id,
hz_utility_pub.CREATED_BY,
hz_utility_pub.CREATION_DATE,
hz_utility_pub.LAST_UPDATE_LOGIN,
hz_utility_pub.LAST_UPDATE_DATE,
hz_utility_pub.LAST_UPDATED_BY
);
UPDATE WSH_PICKING_RULES yt SET
customer_id=NUM_COL1_NEW_LIST(I)
, LAST_UPDATE_DATE=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
, REQUEST_ID=req_id
, PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
, PROGRAM_ID=arp_standard.profile.program_id
, PROGRAM_UPDATE_DATE=SYSDATE
WHERE PICKING_RULE_ID=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT 1 FROM DUAL
WHERE EXISTS
( SELECT 'x'
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
ra_customer_merges rcm,
mtl_parameters mtl
WHERE mtl.wms_enabled_flag = 'Y'
AND mtl.organization_id = wdd.organization_id
AND wda.parent_delivery_detail_id IS NOT NULL
-- AND wda.delivery_id IS NULL
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.customer_id = rcm.duplicate_id
AND wdd.ship_to_location_id = WSH_UTIL_CORE.Cust_Site_To_Location( duplicate_site_id )
AND wdd.container_flag = 'N'
-- AND wdd.released_status IN ( 'R', 'N', 'X', 'Y', 'S', 'B' )
AND wdd.released_status = 'Y'
AND rcm.customer_site_code = 'SHIP_TO'
AND rcm.process_flag = 'N'
AND rcm.request_id = Req_Id
AND rcm.set_number = Set_Num );