[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;