[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT location_id,
hps.party_site_id,
hps.object_version_number
FROM hz_party_sites hps,
hz_party_site_uses hpsu
WHERE hps.party_id = p_party_id
AND hps.party_site_id = hpsu.party_site_id
AND hpsu.site_use_type = 'SUPPLIER_SHIP_FROM'
AND hpsu.status = 'A'
AND hps.party_site_id = NVL(p_site_id, hps.party_site_id);
SELECT 1
INTO l_query_count
FROM wsh_delivery_details wdd,
wsh_locations wl
WHERE wdd.ship_from_location_id = wl.wsh_location_id
AND wl.source_location_id = get_party_site_rec.location_id
AND wdd.party_id = p_party_id
AND rownum=1;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_SITE_V2PUB.UPDATE_PARTY_SITE_USE',WSH_DEBUG_SV.C_PROC_LEVEL);
hz_party_site_v2pub.update_party_site
(
p_party_site_rec => l_from_party_rec,
p_object_version_number => l_object_version_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg
);
WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from HZ_PARTY_SITE_V2PUB.UPDATE_PARTY_SITE is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
SELECT 'x', hps.party_site_id
FROM hz_party_sites hps,
hz_party_site_uses hpsu
WHERE hps.party_id = p_to_id
AND hps.location_id = p_location_id
AND hps.party_site_id = hpsu.party_site_id
AND hpsu.site_use_type = 'SUPPLIER_SHIP_FROM';
SELECT substr(party_site_number, 1,
instr(party_site_number, '|')-1) location_code,
hps.party_site_id
FROM hz_party_sites hps,
hz_party_site_uses hpsu
WHERE hps.location_id = p_location_id
AND hps.party_id = p_from_id
AND hps.party_site_id = hpsu.party_site_id
AND hpsu.site_use_type = 'SUPPLIER_SHIP_FROM';
SELECT 'x'
FROM hz_party_sites hps,
hz_party_site_uses hpsu
WHERE hps.party_id = p_to_id
AND hps.party_site_number = p_site_number
AND hps.party_site_id = hpsu.party_site_id
AND hpsu.site_use_type = 'SUPPLIER_SHIP_FROM';
SELECT contact_person.party_name shipper_name,
phone_record.phone_number phone_number,
email_record.email_address email_address
FROM hz_party_sites hps,
hz_parties contact_person,
hz_org_contacts supplier_contact,
hz_contact_points phone_record,
hz_contact_points email_record,
hz_relationships hrel
WHERE hrel.subject_id = contact_person.party_id
AND hrel.subject_table_name = 'HZ_PARTIES'
AND hrel.subject_type = 'PERSON'
AND hrel.object_id = hps.party_id
AND hrel.object_table_name = 'HZ_PARTIES'
AND hrel.object_type = 'ORGANIZATION'
AND hrel.relationship_code = 'CONTACT_OF'
AND hrel.directional_flag = 'F'
AND supplier_contact.party_relationship_id =hrel.relationship_id
AND supplier_contact.party_site_id = hps.party_site_id
AND phone_record.owner_table_name(+) = 'HZ_PARTIES'
AND phone_record.owner_table_id(+) = hrel.party_id
AND phone_record.contact_point_type(+) = 'PHONE'
AND email_record.owner_table_name = 'HZ_PARTIES'
AND email_record.owner_table_id = hrel.party_id
AND email_record.contact_point_type = 'EMAIL'
AND hps.party_site_id =p_party_site_id
AND hps.party_id = p_party_id;
SELECT vendor_name
FROM po_vendors
WHERE vendor_id = p_vendor_id;
l_LocationIdTbl.DELETE;
WSH_LOCATIONS_PKG.Insert_Location_Owners
(
pLocationIdTbl => l_LocationIdTbl,
p_location_source_code => 'HZ',
x_return_status => l_return_status
);
PROCEDURE Update_New_Delivery (
p_from_id IN NUMBER,
p_to_id IN NUMBER,
p_to_party_id IN NUMBER,
p_from_party_id IN NUMBER,
p_delivery_id IN NUMBER,
p_from_site_id IN NUMBER,
p_old_delivery_id IN NUMBER,
p_temp_update_flag IN VARCHAR2,
p_location_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
--
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_NEW_DELIVERY';
WSH_DEBUG_SV.log(l_module_name,'P_TEMP_UPDATE_FLAG', p_temp_update_flag );
UPDATE wsh_new_deliveries
SET vendor_id = p_to_id,
party_id = p_to_party_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE delivery_id = p_delivery_id
RETURNING name INTO l_dlvy_name;
WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_new_deliveries. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
UPDATE wsh_new_deliveries
SET vendor_id = p_to_id,
party_id = p_to_party_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE delivery_id
IN ( SELECT delivery_id
FROM wsh_delivery_assignments
WHERE delivery_detail_id
IN ( SELECT delivery_detail_id
FROM wsh_delivery_details
WHERE source_code = 'PO'
AND vendor_id = p_from_id
AND ship_from_site_id = p_from_site_id
AND source_header_id
IN (SELECT po_header_id
FROM po_headers_all
WHERE vendor_id = p_to_id
)
)
)
AND vendor_id = p_from_id;
WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_new_deliveries. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
IF (p_temp_update_flag = 'Y') THEN
--{
UPDATE wsh_wms_sync_tmp
SET temp_col = 'Y',
parent_delivery_detail_id = p_delivery_id
WHERE delivery_id = p_old_delivery_id
AND operation_type = 'VENDOR_MRG';
ELSIF (p_temp_update_flag = 'N') THEN
UPDATE wsh_wms_sync_tmp
SET temp_col = 'Y'
WHERE delivery_id = p_old_delivery_id
AND operation_type = 'VENDOR_MRG';
WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_wms_sync_tmp. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
SELECT source_location_id INTO l_location_id
FROM wsh_locations
WHERE wsh_location_id = p_location_id;
wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.Update_New_Delivery');
END Update_New_Delivery;
PROCEDURE Update_Non_PO_Entities(
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
l_return_status VARCHAR2(1);
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_NON_PO_ENTITIES';
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 a.calendar_type,
a.calendar_assignment_id,
a.vendor_site_id,
a.association_type,
a.freight_code,
b.vendor_site_code,
a.calendar_code
FROM wsh_calendar_assignments a,
po_vendor_sites_all b
WHERE a.vendor_id = p_from_id
AND a.vendor_site_id = p_from_site_id
AND b.vendor_site_id = a.vendor_site_id;
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, '!!!' );
SELECT location_id,
hps.party_site_id,
hps.object_version_number
FROM hz_party_sites hps,
hz_party_site_uses hpsu
WHERE hps.party_id = p_party_id
AND hps.party_site_id = hpsu.party_site_id
AND hpsu.site_use_type = 'SUPPLIER_SHIP_FROM'
AND hpsu.status = 'A';
SELECT 1
FROM wsh_calendar_assignments
WHERE vendor_id = p_vendorID
AND association_type = p_assnType
AND calendar_type = p_calType
AND vendor_site_id IS NULL;
UPDATE wsh_carriers
SET supplier_id = p_to_id,
supplier_site_id = p_to_site_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 = p_from_site_id;
WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_carriers. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
UPDATE wsh_carrier_sites
SET supplier_site_id = p_to_site_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE supplier_site_id = p_from_site_id;
WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_carrier_sites. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
WSH_PARTY_MERGE.Update_Entities_During_Merge
(
p_to_id => p_to_id,
p_from_id => p_from_id,
p_from_party_id => p_from_party_id ,
p_to_party_id => p_to_party_id ,
p_to_site_id => p_to_site_id,
p_from_site_id => p_from_site_id,
p_site_merge => p_site_merge,
p_from_supplier_name => p_from_supplier_name,
x_return_status => l_return_status
);
UPDATE wsh_calendar_assignments
SET vendor_id = p_to_id,
vendor_site_id = p_to_site_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE calendar_assignment_id = check_site_calendar_rec.calendar_assignment_id;
WSH_DEBUG_SV.log(l_module_name, 'Calendar Assgn ID updated',
check_site_calendar_rec.calendar_assignment_id);
WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_calendar_assignments. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
DELETE wsh_calendar_assignments
WHERE calendar_assignment_id = check_site_calendar_rec.calendar_assignment_id;
WSH_DEBUG_SV.log(l_module_name, 'Calendar Assgn ID deleted',
check_site_calendar_rec.calendar_assignment_id);
WSH_DEBUG_SV.logmsg(l_module_name,'Deleted record(s) from wsh_calendar_assignments. Number of Rows deleted is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
wsh_util_core.default_handler('WSH_VENDOR_PARTY_MERGE_PKG.Update_Non_PO_Entities');
END Update_Non_PO_Entities;
SELECT end_date_active,
vendor_name
FROM po_vendors
WHERE vendor_id = p_vendor_id;
SELECT process
FROM ap_duplicate_vendors_all
WHERE vendor_id = p_to_id
AND vendor_site_id = p_to_site_id
AND duplicate_vendor_id = p_from_id
AND duplicate_vendor_site_id = p_from_site_id;
SELECT w.delivery_id,
d.delivery_detail_id
FROM po_headers_all p,
wsh_delivery_details d,
wsh_delivery_assignments w,
Wsh_new_deliveries wnd
WHERE p.vendor_id = p_to_id
AND d.source_code = 'PO'
AND p.po_header_id = d.source_header_id
AND p.vendor_site_id = p_to_site_id
AND d.vendor_id = p_from_id
AND d.ship_From_site_id=p_from_site_id
AND d.delivery_detail_id = w.delivery_detail_id
AND w.delivery_id = wnd.delivery_id(+)
AND nvl(w.type, 'S') IN ('S' ,'O');
SELECT distinct delivery_id
FROM wsh_wms_sync_tmp
WHERE operation_type = 'VENDOR_MRG'
AND temp_col IS NULL
AND delivery_id IS NOT NULL;
SELECT 'Y' ,
wnd.initial_pickup_location_id,
wnd.status_code,
wnd.routing_response_id,
wnd.name,
wnd.ultimate_dropoff_location_id
FROM wsh_new_deliveries wnd,
wsh_delivery_details wdd,
wsh_delivery_assignments wda
WHERE wnd.delivery_id = p_del_id
AND wnd.delivery_id = wda.delivery_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.ship_from_site_id <> p_from_site_id
AND wdd.ship_from_site_id <> p_to_site_id
AND wdd.vendor_id = p_from_id
AND nvl(wda.type,'S') IN ('S' , 'O')
AND NOT EXISTS (SELECT 1
FROM ap_duplicate_vendors_all
WHERE process_flag IN ('S', 'D')
AND process IN ('P','B')
AND duplicate_vendor_id = wdd.vendor_id
AND duplicate_vendor_site_id = wdd.ship_from_site_id
AND vendor_id = p_to_id
);
SELECT parent_delivery_detail_id
FROM wsh_wms_sync_tmp wwst,
wsh_delivery_details wdd
WHERE wwst.delivery_id = p_delivery_id
AND wwst.temp_col IS NOT NULL
AND operation_type = 'VENDOR_MRG'
AND wdd.delivery_detail_id = wwst.delivery_detail_id
AND wdd.vendor_id = p_to_id
AND wwst.parent_delivery_detail_id IS NOT NULL;
SELECT delivery_detail_id
FROM wsh_wms_sync_tmp
WHERE delivery_id = p_delivery_id
AND operation_type = 'VENDOR_MRG'
AND temp_col IS NULL;
SELECT wdd.delivery_detail_id,
wdd.routing_req_id,
wdd.vendor_id,
wth.receipt_number rr_number,
wth.revision_number,
wnd.ultimate_dropoff_location_id,
wnd.name
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda ,
wsh_inbound_txn_history wth,
wsh_new_deliveries wnd
WHERE wda.delivery_id = p_delivery_id
AND nvl(wda.type,'S') IN ('S','O')
AND wda.delivery_detail_Id = wdd.delivery_detail_id
AND wdd.routing_req_id = wth.transaction_id
AND wth.transaction_type='ROUTING_REQUEST'
AND wdd.vendor_id <> wth.supplier_id
AND wnd.delivery_id = wda.delivery_id
ORDER BY routing_req_id;
SELECT transaction_id,
revision_number,
parent_shipment_header_id
FROM wsh_inbound_txn_history
WHERE supplier_id = p_supplier_id
AND receipt_number = p_rr_number
AND transaction_type='ROUTING_REQUEST'
ORDER BY revision_number DESC;
l_numRowsUpdated NUMBER;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_VENDOR_PARTY_MERGE_PKG.UPDATE_NON_PO_ENTITIES',WSH_DEBUG_SV.C_PROC_LEVEL);
Update_Non_PO_Entities(
p_to_id => p_to_id,
p_from_id => p_from_id,
p_from_party_id => p_from_party_id ,
p_to_party_id => p_to_party_id ,
p_to_site_id => p_to_site_id,
p_from_site_id => p_from_site_id,
p_site_merge => l_site_merge,
p_from_supplier_name => l_supplier_name,
X_return_status => l_return_status
);
INSERT INTO wsh_wms_sync_tmp
( delivery_detail_id,
delivery_id,
operation_type,
creation_date )
VALUES ( l_dd_list(j),
l_delivery_list(j),
'VENDOR_MRG',
sysdate );
WSH_DEBUG_SV.logmsg(l_module_name,'Inserted records into wsh_wms_sync_tmp. Number of Rows inserted is ' || l_dd_list.count, WSH_DEBUG_SV.C_STMT_LEVEL);
UPDATE wsh_delivery_details
SET vendor_id = p_to_id,
ship_from_site_id = p_to_site_id,
party_id = p_to_party_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE delivery_detail_id = l_dd_list(j);
WSH_DEBUG_SV.log(l_module_name, 'Updated WDD records with vendor/vendor site', p_to_id || ' - ' || p_To_site_id);
WSH_DEBUG_SV.logmsg( l_module_name,'Out of ' || l_dd_list.count || ' delivery details, only ' || sql%rowcount || ' were updated.', WSH_DEBUG_SV.C_STMT_LEVEL);
fnd_message.set_token( 'NUM_DETAILS_UPDATED' , to_char(SQL%ROWCOUNT) );
WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_details. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_VENDOR_PARTY_MERGE_PKG.UPDATE_NEW_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
Update_new_delivery(
p_from_id => p_from_id,
p_to_id => p_to_id,
p_to_party_id => p_to_party_id,
p_from_party_id => p_from_party_id,
p_delivery_id => l_new_delivery_id,
p_from_site_id => p_from_site_id,
p_old_delivery_id => l_delivery_rec.delivery_id,
p_temp_update_flag => 'Y',
p_location_id => l_location_id,
x_return_status => l_return_status
);
WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_VENDOR_PARTY_MERGE_PKG.UPDATE_NEW_DELIVERY is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
SELECT initial_pickup_location_id, routing_response_id,
name, status_code
INTO l_location_id, l_routing_response_id, l_dlvy_name, l_dlvy_status_code
FROM wsh_new_deliveries
WHERE delivery_id = l_delivery_rec.delivery_id;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_VENDOR_PARTY_MERGE_PKG.UPDATE_NEW_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
Update_new_delivery(
p_from_id => p_from_id,
p_to_id => p_to_id,
p_to_party_id => p_to_party_id,
p_from_party_id => p_from_party_id,
p_delivery_id => l_delivery_rec.delivery_id,
p_from_site_id => p_from_site_id,
p_old_delivery_id=> l_delivery_rec.delivery_id,
p_temp_update_flag=> 'N',
p_location_id =>l_location_id,
x_return_status => l_return_status
);
WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from WSH_VENDOR_PARTY_MERGE_PKG.UPDATE_NEW_DELIVERY is ' || l_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
UPDATE wsh_inbound_txn_history
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 transaction_type = 'ROUTING_RESPONSE'
AND shipment_header_id = l_delivery_rec.delivery_id
AND supplier_id = p_from_id;
WSH_DEBUG_SV.log(l_module_name, 'Updated ROUTING_RESP record for', l_delivery_rec.delivery_id);
WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_inbound_txn_history. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
UPDATE wsh_delivery_details
SET vendor_id = p_to_id,
party_id = p_to_party_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE container_flag = 'Y'
AND vendor_id = p_from_id
AND delivery_detail_id
IN (
SELECT delivery_detail_id
FROM wsh_delivery_assignments
WHERE nvl(type,'S') in ('S','O')
AND delivery_id = l_chk_delivery_id
);
wsh_debug_sv.log(l_module_name, 'No. of container records updated', SQL%ROWCOUNT);
wsh_debug_sv.log(l_module_name, 'WDD ID to update', l_dlvy_rr_rec.delivery_detail_id);
wsh_debug_sv.log(l_module_name, 'Routing Req ID to update with', l_new_rr_id);
UPDATE wsh_delivery_details
SET routing_req_Id = l_new_rr_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE delivery_detail_id = l_dlvy_rr_rec.delivery_detail_id;
WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_details. Number of Rows updated is ' || sql%rowcount, WSH_DEBUG_SV.C_STMT_LEVEL);
UPDATE wsh_inbound_txn_history a
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 transaction_type not in ('ROUTING_REQUEST','ROUTING_RESPONSE')
AND exists (SELECT shipment_header_id
FROM rcv_shipment_headers b
WHERE b.shipment_header_id = a.shipment_header_id
AND b.vendor_id = p_to_id
);
l_numRowsUpdated := SQL%ROWCOUNT;
WSH_DEBUG_SV.log(l_module_name, 'Updated ASN/RECEIPT records with vendor', p_to_id);
WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_inbound_txn_history. Number of Rows updated is ' || l_numRowsUpdated, WSH_DEBUG_SV.C_STMT_LEVEL);
SELECT organization_id
INTO l_dlvy_rr_tbl(l_respIndex).organization_id
FROM wsh_new_deliveries
WHERE delivery_id = l_dlvy_rr_tbl(l_respIndex).delivery_id;
p_update_flag => 'Y',
p_calc_wv_if_frozen => 'N',
x_return_status => l_return_status
);
UPDATE wsh_delivery_legs
SET reprice_required = 'Y',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE delivery_id = l_dlvy_tbl(i);
UPDATE fte_invoice_headers a
SET supplier_id = p_to_id,
supplier_site_id = p_to_site_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 = p_from_site_id
AND exists (SELECT 1
FROM ap_invoices_all
WHERE vendor_id = p_to_id
AND vendor_site_id = p_to_site_id
AND invoice_num = a.bill_number
); */
SELECT party_id
FROM po_vendors
WHERE vendor_id = p_vendorId;
g_LocChangeTab.DELETE;
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;
WSH_DEBUG_SV.logmsg(l_module_name,'WSH_VENDOR_PARTY_MERGE_PKG.Get_Delivery_Hash()+ Update_Hash_Exp - ' || getTimeStamp );
SELECT pvs.location_id, hzp.party_site_use_id
FROM po_vendors pov,
po_vendor_sites pvs,
hz_locations hz,
hz_party_site_uses hzp
WHERE pov.vendor_id = pvs.vendor_id
AND pvs.location_id = hz.location_id
AND pov.vendor_id = c_vendor_id
AND (c_vendor_site is null or pvs.vendor_site_id = c_vendor_site)
AND hzp.party_site_id = pvs.party_site_id
AND hzp.site_use_type = 'PURCHASING';
SELECT wdd.rowid,wda.rowid, wdd.delivery_detail_id,
wdd.inventory_item_id,wdd.organization_id,
wdd.gross_weight, wdd.net_weight, wdd.volume,
wdd.weight_uom_code,wdd.volume_uom_code,
wda.parent_delivery_detail_id, wda.delivery_id, wts.stop_id
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
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.source_code = 'RTV'
AND wdd.released_status = 'X'
AND nvl(wdd.consignee_flag, 'C') = 'V'
AND wdd.customer_id = c_from_vendor_id
AND wdd.ship_to_site_use_id = c_party_site_id
AND wdd.ship_to_location_id = c_location_id
FOR UPDATE OF Wdd.Delivery_Detail_Id, Wda.Delivery_Detail_Id,
Wnd.Delivery_Id, Wts.Stop_Id NOWAIT;
SELECT wda.delivery_id
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
wsh_new_deliveries wnd
WHERE wnd.status_code IN ( 'CO', 'IT', 'CL' )
AND wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_code = 'RTV'
AND wdd.released_status = 'C'
AND nvl(wdd.consignee_flag, 'C') = 'V'
AND wdd.customer_id = c_from_vendor_id
AND wdd.ship_to_site_use_id = c_party_site_id
AND wdd.ship_to_location_id = c_location_id
FOR UPDATE OF WDD.DELIVERY_DETAIL_ID NOWAIT;
SELECT wda.rowid, wda.delivery_id, wnd.name,wda.delivery_detail_id,
wda.parent_delivery_detail_id,
wdd.inventory_item_id,wdd.organization_id,
wdd.gross_weight, wdd.net_weight, wdd.volume,
wdd.weight_uom_code,wdd.volume_uom_code
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_tmp tmp
WHERE wdd.container_flag = 'N'
AND wdd.source_code = 'RTV'
AND wdd.released_status = 'X'
AND nvl(wdd.consignee_flag, 'C') = 'V'
AND wdd.customer_id = c_to_vendor_id
AND wdd.ship_to_site_use_id = c_to_party_site_use_id
AND wdd.ship_to_location_id = c_to_location_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wnd.ultimate_dropoff_location_id <> c_to_location_id
AND wnd.status_code = 'OP'
AND wnd.delivery_id = tmp.column1
AND EXISTS
( SELECT 'x'
FROM wsh_delivery_assignments assgn,
Wsh_delivery_details det
WHERE det.container_flag = 'N'
AND det.delivery_detail_id = assgn.delivery_detail_id
AND assgn.delivery_id = wnd.delivery_id
AND det.ship_to_location_id = wnd.ultimate_dropoff_location_id )
FOR UPDATE OF WDA.DELIVERY_DETAIL_ID NOWAIT;
SELECT 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 wda.parent_delivery_detail_id IS NOT NULL
AND wda.delivery_id = tmp.column1
FOR UPDATE OF wdd.delivery_detail_id NOWAIT;
SELECT wdl.rowid, tmp.rowid, wnd.delivery_id, wdl.delivery_leg_id, wts.trip_id,
wts.stop_id, wnd.gross_weight, wnd.net_weight, wnd.volume
FROM wsh_delivery_legs wdl,
wsh_new_deliveries wnd,
Wsh_trip_stops wts,
wsh_tmp tmp
WHERE wnd.ultimate_dropoff_location_id = c_to_location_id
AND wts.stop_id = wdl.drop_off_stop_id
AND wdl.delivery_id = wnd.delivery_id
AND wnd.delivery_id = tmp.column1
AND EXISTS
( SELECT 'x'
FROM wsh_new_deliveries del,
wsh_delivery_legs legs
WHERE del.ultimate_dropoff_location_id <> c_to_location_id
AND del.delivery_id = legs.delivery_id
AND legs.drop_off_stop_id = wdl.drop_off_stop_id );
SELECT wnd.delivery_id,wnd.initial_pickup_location_id,wnd.customer_id,
wnd.intmed_ship_to_location_id,wnd.fob_code,wnd.freight_terms_code,
wnd.ship_method_code,wnd.carrier_id,wnd.source_header_id,wnd.organization_id,
wnd.initial_pickup_date,wnd.ultimate_dropoff_date,wnd.ignore_for_planning,
wnd.shipment_direction,wnd.shipping_control,wnd.party_id,wnd.client_id
FROM wsh_new_deliveries wnd
WHERE nvl(Wnd.Customer_Id, c_vendor_id) = c_vendor_id
AND nvl(wnd.consignee_flag, 'C') = 'V'
AND Wnd.Ultimate_Dropoff_Location_Id = c_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 NOWAIT;
select 'x'
from dual
where exists (
select 1
from wsh_delivery_details
where consignee_flag = 'V'
and customer_id = c_vendor_id);
select 'x'
from dual
where exists (
select 1
from wsh_new_deliveries
where consignee_flag = 'V'
and customer_id = c_vendor_id);
UPDATE wsh_delivery_details
SET customer_id = decode(customer_id,
p_from_vendor_id, p_to_vendor_id,
customer_id),
ship_to_site_use_id = decode(container_flag,
'N', l_to_party_site_use_id,
ship_to_site_use_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 delivery_detail_id IN
( SELECT wda.delivery_detail_id
FROM wsh_delivery_assignments wda
WHERE delivery_id = l_delivery_id(i) );
WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_details. Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
l_delivery_id.delete;
UPDATE wsh_delivery_details
SET customer_id = decode(customer_id,
p_from_vendor_id, p_to_vendor_id,
customer_id),
ship_to_site_use_id = l_to_party_site_use_id,
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 ROWID = l_wdd_rowid(i);
WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_details. Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
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_wda_rowid(i)
AND parent_delivery_detail_id IS NOT NULL
AND delivery_id IS NULL;
WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_assignments. Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
WSH_DEBUG_SV.log(l_module_name, 'Inserting Deliveries and Trip Stops into temp table for processing');
INSERT INTO wsh_tmp ( column1, column2, column3 )
SELECT l_delivery_id(i), l_stop_id(i), l_delivery_detail_id(i)
FROM dual
WHERE l_delivery_id(i) IS NOT NULL
AND NOT EXISTS
( SELECT 'x'
FROM wsh_tmp
WHERE column1 = l_delivery_id(i)
AND ( column2 = l_stop_id(i) OR l_stop_id(i) IS NULL ) );
WSH_DEBUG_SV.logmsg(l_module_name,'inserted into wsh_tmp. Number of Rows inserted is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
l_wdd_rowid.delete;
l_delivery_detail_id.delete ;
l_gross_weight.delete;
l_net_weight.delete;
l_volume.delete;
l_parent_delivery_detail_id.delete;
l_delivery_id.delete;
l_stop_id.delete;
l_wda_rowid.delete;
l_inventory_item_id.delete;
l_organization_id.delete;
l_weight_uom.delete;
l_volume_uom.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_wda_rowid(i);
WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_assignments(unassign n unpack from del). Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
DELETE FROM wsh_tmp
WHERE column1 = l_delivery_id(i);
WSH_DEBUG_SV.logmsg(l_module_name,'deleted from wsh_tmp. Number of Rows deleted is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
l_delivery_detail_id.delete ;
l_gross_weight.delete;
l_net_weight.delete;
l_volume.delete;
l_parent_delivery_detail_id.delete;
l_delivery_id.delete;
l_wda_rowid.delete;
l_delivery_name.delete;
l_inventory_item_id.delete;
l_organization_id.delete;
l_weight_uom.delete;
l_volume_uom.delete;
UPDATE wsh_delivery_details
SET customer_id = decode(customer_id,
p_from_vendor_id, p_to_vendor_id,
customer_id),
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 ROWID = l_wdd_rowid(i);
WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_delivery_details (Containers). Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
l_delivery_id.delete;
l_delivery_detail_id.delete;
l_wdd_rowid.delete;
l_hash_value_tab.DELETE;
l_hash_string_tab.DELETE;
l_delivery_id_tab.DELETE;
UPDATE wsh_new_deliveries
SET customer_id = decode(customer_id,
p_from_vendor_id, p_to_vendor_id,
customer_id ),
ultimate_dropoff_location_id = l_to_location_id,
hash_value = l_hash_value_tab(i),
hash_string = l_hash_string_tab(i),
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 delivery_id = l_delivery_id_tab(i);
WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_new_deliveries. Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
DELETE FROM wsh_delivery_legs
WHERE ROWID = l_wdl_rowid(i);
WSH_DEBUG_SV.logmsg(l_module_name,'deleted from wsh_delivery_legs. Number of Rows deleted is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
DELETE FROM wsh_tmp
WHERE ROWID = l_tmp_rowid(i);
WSH_DEBUG_SV.logmsg(l_module_name,'deleted from wsh_tmp. Number of Rows deleted is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
l_wdl_rowid.delete;
l_tmp_rowid.delete;
l_delivery_id.delete;
l_delivery_leg_id.delete;
l_trip_id.delete;
l_trip_stop_id.delete;
l_gross_weight.delete;
l_net_weight.delete;
l_volume.delete;
UPDATE wsh_trip_stops
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 Stop_Id in (
SELECT Column2
FROM WSH_TMP
WHERE Column2 IS NOT NULL );
DELETE FROM Wsh_Tmp;
UPDATE wsh_new_deliveries
SET customer_id = decode(customer_id,
p_from_vendor_id, p_to_vendor_id,
customer_id ),
ultimate_dropoff_location_id = l_to_location_id,
hash_value = l_grp_attr_tab(i).l1_hash_value,
hash_string = l_grp_attr_tab(i).l1_hash_string,
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 delivery_id = l_delivery_id(i);
WSH_DEBUG_SV.logmsg(l_module_name,'Updated wsh_new_deliveries(Empty deliveries). Number of Rows updated is ' || l_sql_count, WSH_DEBUG_SV.C_STMT_LEVEL);
l_delivery_id.delete;
l_initial_pickup_location_id.delete;
l_customer_id.delete;
l_intmed_ship_to_location_id.delete;
l_fob_code.delete;
l_freight_terms_code.delete;
l_ship_method_code.delete;
l_carrier_id.delete;
l_source_header_id.delete;
l_organization_id.delete;
l_initial_pickup_date.delete;
l_ultimate_dropoff_date.delete;
l_ignore_for_planning.delete;
l_shipment_direction.delete;
l_shipping_control.delete;
l_party_id.delete;
l_client_id.delete;