[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM wsh_carriers
WHERE carrier_id = p_carrier_id;
SELECT vendor_id, vendor_name
FROM po_vendors
WHERE party_id = p_partyId;
Update_Entities_During_Merge
(
p_to_id => l_toVendorID,
p_from_id => l_fromVendorID,
p_from_party_id => p_from_id ,
p_to_party_id => p_to_id ,
p_to_site_id => NULL,
p_from_site_id => NULL,
p_site_merge => FALSE,
p_from_supplier_name => l_fromSupName,
x_return_status => l_return_status
);
DELETE wsh_calendar_assignments
WHERE vendor_id = l_fromVendorID
AND vendor_site_id IS NULL;
SELECT 1
FROM wsh_carrier_sites
WHERE carrier_site_id = p_carrier_site_id;
SELECT location_owner_id
FROM wsh_location_owners
WHERE wsh_location_id = p_location_id
AND owner_party_id = p_party_id;
SELECT wsh_location_id
FROM wsh_location_owners
WHERE location_owner_id = p_loc_owner_id;
SELECT owner_type
FROM wsh_location_owners
WHERE owner_party_id = p_party_id;
SELECT 3
FROM wsh_carriers c
WHERE c.carrier_id = c_party_id
UNION ALL
SELECT 4
FROM hz_relationships r, po_vendors v
WHERE r.relationship_type = 'POS_VENDOR_PARTY' AND
r.subject_id = v.vendor_id AND
r.object_id = c_party_id;
CURSOR get_loc_owners_for_update(p_from_party_id IN NUMBER,p_from_id IN NUMBER) IS
SELECT owner_party_id,
owner_type,
last_update_date,
last_updated_by,
last_update_login
FROM wsh_location_owners
WHERE location_owner_id = p_from_id
AND owner_party_id = p_from_party_id
FOR UPDATE NOWAIT;
l_loc_owners_rec get_loc_owners_for_update%rowtype;
OPEN get_loc_owners_for_update( p_from_fk_id,p_from_id);
FETCH get_loc_owners_for_update INTO l_loc_owners_rec;
IF get_loc_owners_for_update%FOUND THEN
--No Wait will raise the exception
UPDATE wsh_location_owners
SET owner_party_id = p_to_fk_id,
owner_type = l_owner_type_to,
last_update_date = hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.request_id
WHERE owner_party_id = p_from_fk_id
AND location_owner_id = p_from_id;
IF get_loc_owners_for_update%ISOPEN THEN
CLOSE get_loc_owners_for_update;
IF get_loc_owners_for_update%ISOPEN THEN
CLOSE get_loc_owners_for_update;
SELECT party_id
FROM hz_party_Sites
WHERE party_site_id = p_party_site_id;
SELECT vendor_id
FROM po_vendors
WHERE party_id = p_party_id;
SELECT location_id
FROM hz_party_sites
WHERE party_site_id = p_from_fk_id;
SELECT 1
INTO l_query_count
FROM wsh_delivery_details wdd,
hz_party_Sites hps,
wsh_locations wl
WHERE hps.party_site_id = p_from_fk_id
AND wdd.ship_from_location_id = wl.wsh_location_id
AND hps.location_id = wl.source_location_id
AND wdd.party_id = hps.party_id
AND rownum =1;
PROCEDURE Update_Entities_during_Merge
(
p_to_id IN NUMBER,
p_from_id IN NUMBER,
p_from_party_id IN NUMBER,
p_to_party_id IN NUMBER,
p_to_site_id IN NUMBER,
p_from_site_id IN NUMBER,
p_site_merge IN BOOLEAN,
p_from_supplier_name IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
--
CURSOR check_calendar IS
SELECT calendar_type,
calendar_assignment_id,
vendor_site_id,
association_type,
freight_code
FROM wsh_calendar_assignments a
WHERE vendor_id = p_from_id
AND vendor_site_id IS NULL;
SELECT 1
FROM wsh_calendar_assignments
WHERE vendor_id = p_vendor_id
AND calendar_type=p_calendar_type
AND nvl( vendor_site_id,-999999 ) = nvl( p_vendor_site_id,-999999 )
AND association_type = p_association_type
AND nvl( freight_code, '!!!' ) = nvl( p_freight_code, '!!!' );
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.update_entities_during_merge';
UPDATE wsh_carriers
SET supplier_id = p_to_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE supplier_id = p_from_id
AND supplier_site_id IS NULL;
wsh_debug_sv.log(l_module_name, 'No. of rows in WSH_carriers that were updated', SQL%ROWCOUNT);
UPDATE wsh_calendar_assignments
SET vendor_id = p_to_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE calendar_assignment_id = check_calendar_rec.calendar_assignment_id;
WSH_DEBUG_SV.log(l_module_name, 'Calendar Assgn ID updated', check_calendar_rec.calendar_assignment_id);
WSH_DEBUG_SV.log(l_module_name,'Number of Rows updated is', sql%rowcount);
DELETE wsh_calendar_assignments
WHERE calendar_assignment_id = check_calendar_rec.calendar_assignment_id;
WSH_DEBUG_SV.log(l_module_name, 'Deleted cal. assgn ID',
check_calendar_rec.calendar_assignment_id);
WSH_DEBUG_SV.log(l_module_name,'Number of Rows deleted is', sql%rowcount);
wsh_util_core.default_handler('WSH_PARTY_MERGE.Update_Entities_during_merge');
END Update_Entities_During_Merge;
Update_Hash_Exp EXCEPTION;
l_grp_attr_tab_type.DELETE(1);
RAISE Update_Hash_Exp;
WHEN Update_Hash_Exp THEN
x_return_status := l_return_status;
WSH_DEBUG_SV.log(l_module_name, 'Update_Hash_Exp Exception occurred in Get_Delivery_Hash');
SELECT DISTINCT wdd.organization_id
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
hz_cust_acct_sites_all ca,
hz_cust_site_uses_all su
where wda.parent_delivery_detail_id is not null
and wda.delivery_id is null
and wda.delivery_detail_id = wdd.delivery_detail_id
and nvl(wdd.line_direction, 'O') in ( 'O', 'IO' )
and wdd.container_flag = 'N'
and wdd.released_status = 'Y'
and wdd.ship_to_location_id = p_location_id
and wdd.ship_to_site_use_id = su.site_use_id
and su.cust_acct_site_id = ca.cust_acct_site_id
and ca.party_site_id = p_party_site_id;
SELECT DISTINCT wdd.organization_id
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
hz_cust_acct_sites_all ca,
hz_cust_site_uses_all su
where wda.parent_delivery_detail_id is not null
and wda.delivery_id is not null
and wda.delivery_detail_id = wdd.delivery_detail_id
and nvl(wdd.line_direction, 'O') in ( 'O', 'IO' )
and wdd.container_flag = 'N'
and wdd.released_status = 'Y'
and wdd.ship_to_location_id = p_location_id
and wdd.ship_to_site_use_id = su.site_use_id
and su.cust_acct_site_id = ca.cust_acct_site_id
and ca.party_site_id = p_party_site_id
and exists
( select 'X'
from wsh_delivery_details det,
wsh_delivery_assignments_v asgn
where det.ship_to_site_use_id <> wdd.ship_to_site_use_id
and det.delivery_detail_id = asgn.delivery_detail_id
and asgn.delivery_id = wda.delivery_id );
SELECT Party_Id, Party_Site_Id, Location_Id
FROM HZ_PARTY_SITES HPS
WHERE PARTY_SITE_ID = p_party_site_id;
SELECT Wdd.Rowid Del_Detail_Rowid, Wnd.RowId Delivery_RowId, Wts.Rowid Stop_RowId,
Wnd.Delivery_Id Delivery_Id, Wts.Stop_Id Stop_Id,
Wdd.Delivery_Detail_Id Delivery_Detail_Id,
Wda.Parent_Delivery_Detail_Id Parent_Delivery_Detail_Id,
Wdd.Net_Weight, Wdd.Gross_Weight, Wdd.Volume,
Wdd.Weight_Uom_code, Wdd.Volume_Uom_Code, Wdd.Inventory_Item_Id,
Wdd.Organization_Id, WDA.Rowid Wda_Rowid
FROM WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS_V WDA,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_LEGS WDL,
WSH_TRIP_STOPS WTS,
HZ_CUST_ACCT_SITES_ALL CA,
HZ_CUST_SITE_USES_ALL SU
WHERE SU.Cust_Acct_Site_Id = CA.Cust_Acct_Site_Id
AND CA.Party_Site_Id = p_party_site_id
AND WDD.Container_Flag = 'N'
AND NVL(WDD.Line_Direction, 'O') in ( 'O', 'IO' )
AND WDD.Ship_To_Location_id = p_location_id
AND WDD.Ship_To_Site_Use_Id = SU.Site_Use_Id
AND WDD.Released_Status IN ( 'R', 'N', 'X', 'Y', 'S', 'B' )
AND WDA.Delivery_Detail_Id = WDD.Delivery_Detail_Id
AND NVL(WND.Status_Code, 'OP') IN ( 'OP', 'CO' )
AND WND.Delivery_Id (+) = WDA.Delivery_Id
AND WDL.Delivery_Id (+) = WND.Delivery_Id
AND NVL(WTS.Stop_Location_Id, WDD.Ship_To_Location_Id) = WDD.Ship_To_Location_Id
AND NVL(WTS.Status_Code, 'OP') = 'OP'
AND WTS.Stop_Id (+) = WDL.Drop_Off_Stop_Id
FOR UPDATE of Wdd.Delivery_Detail_Id, Wnd.Delivery_Id, Wts.Stop_Id NOWAIT;
SELECT Wda.rowid Del_Assignments_Rowid, Wda.Delivery_id,
Wdd.Delivery_Detail_Id Delivery_Detail_Id,
Wda.Parent_Delivery_Detail_Id Parent_Delivery_Detail_Id,
Wdd.Net_Weight, Wdd.Gross_Weight, Wdd.Volume,
Wdd.Weight_Uom_code, Wdd.Volume_Uom_Code, Wdd.Inventory_Item_Id,
Wdd.Organization_Id, Wnd.Name Delivery_Name,
Wdd.Move_Order_Line_Id, Wdd.Released_Status
FROM WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS_V WDA,
WSH_NEW_DELIVERIES WND,
HZ_CUST_ACCT_SITES_ALL CA,
HZ_CUST_SITE_USES_ALL SU
WHERE SU.Cust_Acct_Site_Id = CA.Cust_Acct_Site_Id
AND CA.Party_Site_Id = p_party_site_id
AND WDD.Container_Flag = 'N'
AND NVL(WDD.Line_Direction, 'O') in ( 'O', 'IO' )
AND WDD.Ship_To_Location_id = p_location_id
AND WDD.Ship_To_Site_Use_Id = SU.Site_Use_Id
AND WDD.Released_Status IN ( 'R', 'N', 'X', 'Y', 'S', 'B' )
AND WND.Ultimate_Dropoff_Location_Id <> p_location_id
AND WDA.Delivery_Detail_Id = WDD.Delivery_Detail_Id
AND WND.Status_Code IN ( 'OP', 'CO' )
AND WDA.delivery_id is not null
AND WND.Delivery_Id = WDA.Delivery_Id
AND exists (
SELECT 'x'
FROM WSH_DELIVERY_ASSIGNMENTS_V 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, Wnd.Delivery_Id NOWAIT;
SELECT Wdd.Rowid
FROM Wsh_Delivery_Details Wdd,
Wsh_Delivery_Assignments_V Wda,
Wsh_Tmp Tmp
WHERE Wdd.container_flag = 'Y'
AND NVL(WDD.Line_Direction, 'O') in ( 'O', 'IO' )
AND Wdd.delivery_detail_id = Wda.Delivery_Detail_Id
AND Wda.Delivery_Id = Tmp.Column1
FOR UPDATE OF Wdd.Delivery_Detail_id NOWAIT;
SELECT Wdl.Delivery_Id, Wts.Stop_Id, Wts.Trip_Id,
Wdl.Delivery_Leg_Id, Wnd.organization_id,
Wnd.Net_Weight, Wnd.Gross_Weight, Wnd.Volume,
Wdl.Rowid, Tmp.Rowid
FROM Wsh_Trip_Stops Wts,
Wsh_New_Deliveries Wnd,
Wsh_Delivery_Legs Wdl,
Wsh_Tmp Tmp
WHERE Wnd.Ultimate_DropOff_Location_Id = p_to_location_id
AND nvl(Wnd.Shipment_Direction, 'O') in ( 'O', 'IO' )
AND Wnd.Delivery_Id = Wdl.Delivery_Id
AND Wts.Stop_Location_Id = p_from_location_id
AND Wts.Stop_Id = Wdl.Drop_Off_Stop_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_to_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 WDD.Rowid
FROM WSH_DELIVERY_DETAILS WDD,
HZ_CUST_ACCT_SITES_ALL CA,
HZ_CUST_SITE_USES_ALL SU
WHERE SU.Cust_Acct_Site_Id = CA.Cust_Acct_Site_Id
AND CA.Party_Site_Id = p_party_site_id
AND NVL(WDD.Line_Direction, 'O') in ( 'O', 'IO' )
AND WDD.Deliver_To_Location_id = p_location_id
AND WDD.Deliver_To_Site_Use_Id = SU.Site_Use_Id
AND WDD.Deliver_To_Site_Use_Id <> WDD.Ship_To_Site_Use_Id
AND WDD.Released_Status IN ( 'R', 'N', 'X', 'Y', 'S', 'B' )
FOR UPDATE of Wdd.Delivery_Detail_Id NOWAIT;
SELECT WDA.Parent_Delivery_Detail_Id
FROM WSH_DELIVERY_ASSIGNMENTS_V 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,
HZ_CUST_ACCT_SITES_ALL CA,
HZ_CUST_SITE_USES_ALL SU
WHERE SU.Cust_Acct_Site_Id = CA.Cust_Acct_Site_Id
AND CA.Party_Site_Id = p_party_site_id
AND WDD.Container_Flag = 'N'
AND NVL(WDD.Line_Direction, 'O') in ( 'O', 'IO' )
AND WDD.Deliver_To_Location_id = p_location_id
AND WDD.Deliver_To_Site_Use_Id = SU.Site_Use_Id
AND WDD.Deliver_To_Site_Use_Id <> WDD.Ship_To_Site_Use_Id
AND WDD.Released_Status IN ( 'R', 'N', 'X', 'Y', 'S', 'B' ) )
FOR UPDATE OF Wda.Delivery_Detail_Id;
SELECT Wsh_Delivery_Group_S.NEXTVAL
FROM Dual;
SELECT wnd.delivery_id, wnd.rowid, to_number(wt.Column3) delivery_detail_id
FROM Wsh_Tmp wt,
Wsh_New_Deliveries Wnd
WHERE Wnd.Delivery_Id = to_number(Wt.Column1);
DELETE FROM WSH_TMP;
WSH_DEBUG_SV.log(l_module_name, 'Starting updates on shipping tables', TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'));
THEN -- { WDD Update
FORALL i IN l_del_detail_rowid_tab.FIRST..l_del_detail_rowid_tab.LAST
UPDATE WSH_DELIVERY_DETAILS Wdd
SET ship_to_location_id = l_to_location_id,
deliver_to_location_id = decode( nvl(deliver_to_site_use_id, ship_to_site_use_id),
ship_to_site_use_id, l_to_location_id,
deliver_to_location_id ),
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 WDD.Rowid = l_del_detail_rowid_tab(i);
WSH_DEBUG_SV.logmsg(l_module_name, 'No of Rows Updated in WDD : '
|| sql%rowcount || ', Time : '
|| TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
END IF; -- } WDD Update
THEN -- { WDA Update
l_return_status := NULL;
l_delivery_leg_id_tab.delete;
UPDATE WSH_DELIVERY_ASSIGNMENTS
SET parent_delivery_detail_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_del_assignments_rowid_tab(i)
AND Parent_Delivery_Detail_Id IS NOT NULL
AND Delivery_Id IS NULL;
END IF; -- } WDA Update
INSERT INTO wsh_tmp(column1, column2, column3)
SELECT l_delivery_id_tab(i), l_stop_id_tab(i), l_del_detail_id_tab(i)
FROM dual
WHERE l_delivery_id_tab(i) is not null
AND NOT EXISTS
( SELECT 'x'
FROM Wsh_Tmp
WHERE Column1 = l_delivery_id_tab(i)
AND ( Column2 = l_stop_id_tab(i) OR l_stop_id_tab(i) IS NULL ) );
WSH_DEBUG_SV.logmsg(l_module_name, 'No of rows inserted into wsh_temp : '
|| sql%rowcount || ', Time : '
|| TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
THEN -- { WDA Update
l_return_status := NULL;
l_delivery_leg_id_tab.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_del_assignments_rowid_tab(i);
END IF; -- } WDA Update
DELETE FROM wsh_tmp WHERE column1 = l_delivery_id_tab(i);
WSH_DEBUG_SV.logmsg(l_module_name, 'No of records deleted from wsh_tmp table after unassigning from delivery : '
|| sql%rowcount || ', Time : '
|| TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
SELECT COUNT(*)
INTO l_tmp_cnt
FROM WSH_TMP;
UPDATE WSH_DELIVERY_DETAILS Wdd
SET ship_to_location_id = l_to_location_id,
deliver_to_location_id = decode(deliver_to_location_id,
ship_to_location_id, l_to_location_id,
deliver_to_location_id),
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 Wdd.Rowid = l_del_detail_rowid_tab(updCnt);
WSH_DEBUG_SV.logmsg(l_module_name, 'No of Container records Updated in WDD : '
|| sql%rowcount || ', Time : '
|| TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
WSH_DEBUG_SV.log(l_module_name, 'After Container records Update', TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'));
l_delivery_rec.last_update_date := SYSDATE;
l_delivery_rec.last_updated_by := fnd_global.user_id;
l_delivery_rec.last_update_login := fnd_global.conc_login_id;
l_delivery_rec.program_update_date := SYSDATE;
WSH_DEBUG_SV.logmsg(l_module_name, 'Calling API WSH_NEW_DELIVERIES_PVT.Update_Delivery', WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_NEW_DELIVERIES_PVT.Update_Delivery(
p_rowid => TmpDelRec.Rowid,
p_delivery_info => l_delivery_rec,
x_return_status => l_return_status );
WSH_DEBUG_SV.log(l_module_name, 'Error returned from WSH_NEW_DELIVERIES_PVT.Update_Delivery : ' || l_return_status);
DELETE FROM Wsh_Tmp
WHERE Rowid = l_tmp_rowid_tab(updCnt);
WSH_DEBUG_SV.logmsg(l_module_name, 'No of records deleted from Wsh_Tmp : '
|| sql%rowcount || ', Time : '
|| TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
UPDATE WSH_TRIP_STOPS Wts
SET stop_location_id = l_to_location_id,
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 Wts.Stop_Id in (
SELECT Column2
FROM WSH_TMP
WHERE Column2 IS NOT NULL);
WSH_DEBUG_SV.logmsg(l_module_name, 'No of Stop records Updated in WTS : '
|| sql%rowcount || ', Time : '
|| TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
DELETE FROM wsh_tmp;
WSH_DEBUG_SV.logmsg(l_module_name, 'No of records deleted from temp table : '
|| sql%rowcount || ', Time : '
|| TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
THEN -- { WDD Update
FORALL i IN l_del_detail_rowid_tab.FIRST..l_del_detail_rowid_tab.LAST
UPDATE WSH_DELIVERY_DETAILS Wdd
SET deliver_to_location_id = l_to_location_id,
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 WDD.Rowid = l_del_detail_rowid_tab(i);
WSH_DEBUG_SV.logmsg(l_module_name, 'No of Rows Updated in WDD : '
|| sql%rowcount || ', Time : '
|| TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
END IF; -- } WDD Update
THEN -- { WDD Cont Update
FORALL i IN l_del_detail_id_tab.FIRST..l_del_detail_id_tab.LAST
UPDATE WSH_DELIVERY_DETAILS Wdd
SET deliver_to_location_id = l_to_location_id,
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 WDD.Delivery_Detail_Id = l_del_detail_id_tab(i)
AND WDD.Container_Flag = 'Y'; -- To make sure that we are updating container records
WSH_DEBUG_SV.logmsg(l_module_name, 'No of Rows Updated in WDD Cont Del Loc : '
|| sql%rowcount || ', Time : '
|| TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );
END IF; -- } WDD Cont Update
WSH_DEBUG_SV.log(l_module_name, 'Before WPR Update', TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'));
UPDATE WSH_PICKING_RULES WPR
SET ship_to_location_id = l_to_location_id,
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 ship_to_location_id = l_from_location_id
AND EXISTS
( SELECT 'X'
FROM HZ_CUST_ACCT_SITES_ALL CA
WHERE CA.Party_Site_Id = l_to_party_site_id
AND CA.Cust_Account_Id = WPR.Customer_Id );
WSH_DEBUG_SV.logmsg(l_module_name, 'No of rows updated in WPR : '
|| sql%rowcount || ', Time : '
|| TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') );