[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_ROW_IN_LOC_GTMP
(
p_location_id IN NUMBER,
p_corporation_id IN NUMBER,
p_location_type IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
--{
--}
--
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_ROW_IN_LOC_GTMP';
insert into WSH_OTM_LOCATIONS_GTMP
(location_id, corporation_id, location_type)
values( p_location_id,p_corporation_id,p_location_type);
END INSERT_ROW_IN_LOC_GTMP;
SELECT HOU.ORGANIZATION_ID
FROM HR_ALL_ORGANIZATION_UNITS HOU,
WSH_LOCATIONS WL,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_LEGS WDL,
WSH_TRIP_STOPS WTS
WHERE WTS.STOP_ID = p_stop_id
AND HOU.LOCATION_ID = WL.SOURCE_LOCATION_ID
AND WL.LOCATION_SOURCE_CODE = 'HR'
AND WL.WSH_LOCATION_ID = nvl(WTS.PHYSICAL_LOCATION_ID,WTS.STOP_LOCATION_ID)
AND (WTS.STOP_ID = WDL.PICK_UP_STOP_ID
OR WTS.STOP_ID = WDL.DROP_OFF_STOP_ID
)
AND WDL.DELIVERY_ID = WND.DELIVERY_ID
AND HOU.ORGANIZATION_ID = WND.ORGANIZATION_ID;
SELECT HL.INVENTORY_ORGANIZATION_ID
FROM HR_LOCATIONS_ALL HL,
WSH_LOCATIONS WL,
WSH_TRIP_STOPS WTS
WHERE WTS.STOP_ID = p_stop_id
AND HL.LOCATION_ID = WL.SOURCE_LOCATION_ID
AND WL.LOCATION_SOURCE_CODE = 'HR'
AND WL.WSH_LOCATION_ID = nvl(WTS.PHYSICAL_LOCATION_ID,WTS.STOP_LOCATION_ID);
SELECT HCA.CUST_ACCOUNT_ID
FROM HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP,
HZ_PARTY_SITES HPS,
WSH_LOCATIONS WL,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_LEGS WDL,
WSH_TRIP_STOPS WTS,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_DELIVERY_DETAILS WDD
WHERE WTS.STOP_ID = p_stop_id
AND HPS.LOCATION_ID = WL.SOURCE_LOCATION_ID
AND WL.LOCATION_SOURCE_CODE = 'HZ'
AND WL.WSH_LOCATION_ID = nvl(WTS.PHYSICAL_LOCATION_ID,WTS.STOP_LOCATION_ID)
AND HPS.PARTY_ID = HP.PARTY_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND (WTS.STOP_ID = WDL.PICK_UP_STOP_ID
OR WTS.STOP_ID = WDL.DROP_OFF_STOP_ID
)
AND WDL.DELIVERY_ID = WND.DELIVERY_ID
AND WND.DELIVERY_ID = WDA.DELIVERY_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND HCA.CUST_ACCOUNT_ID = WDD.CUSTOMER_ID;
SELECT HCA.CUST_ACCOUNT_ID
FROM HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP,
HZ_PARTY_SITES HPS,
WSH_LOCATIONS WL,
WSH_TRIP_STOPS WTS
WHERE WTS.STOP_ID = p_stop_id
AND HPS.LOCATION_ID = WL.SOURCE_LOCATION_ID
AND WL.LOCATION_SOURCE_CODE = 'HZ'
AND WL.WSH_LOCATION_ID = nvl(WTS.PHYSICAL_LOCATION_ID,WTS.STOP_LOCATION_ID)
AND HPS.PARTY_ID = HP.PARTY_ID
AND HP.PARTY_ID = HCA.PARTY_ID;
SELECT WND.ULTIMATE_DROPOFF_LOCATION_ID SHIP_TO_LOCATION_ID,
ca.cust_account_id CUSTOMER_ID,
--WND.CUSTOMER_ID,
WND.INITIAL_PICKUP_LOCATION_ID SHIP_FROM_LOCATION_ID,
WND.ORGANIZATION_ID,
WLT1.LOCATION_ID WLT1_ST_LOC_ID,
WLT1.CORPORATION_ID WLT1_CUST_ID,
WLT2.LOCATION_ID WLT2_SF_LOC_ID,
WLT2.CORPORATION_ID WLT2_ORG_ID
FROM WSH_NEW_DELIVERIES WND,
WSH_OTM_LOCATIONS_GTMP WLT1,
WSH_OTM_LOCATIONS_GTMP WLT2,
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA,
HZ_CUST_ACCT_SITES_ALL CA,
HZ_CUST_SITE_USES_ALL SU
WHERE CA.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND SU.SITE_USE_ID = WDD.SHIP_TO_SITE_USE_ID
AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID = WDA.DELIVERY_ID
AND WND.DELIVERY_ID = p_delivery_id
AND WND.ULTIMATE_DROPOFF_LOCATION_ID = WLT1.LOCATION_ID (+)
AND WLT1.CORPORATION_ID (+) = p_cust_acct_id
AND WND.INITIAL_PICKUP_LOCATION_ID = WLT2.LOCATION_ID (+)
AND WND.ORGANIZATION_ID = WLT2.CORPORATION_ID (+)
AND WLT1.LOCATION_TYPE (+) = 'CUST_LOC'
AND WLT2.LOCATION_TYPE (+) = 'ORG_LOC'
AND ROWNUM = 1;
SELECT ca.cust_account_id customer_id
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
hz_cust_acct_sites_all ca,
hz_cust_site_uses_all su
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = p_delivery_id
AND su.site_use_id = WDD.SHIP_TO_SITE_USE_ID
AND su.cust_acct_site_id = ca.cust_acct_site_id
AND ROWNUM =1;
SELECT DISTINCT WDD.SHIP_TO_LOCATION_ID,
--WDD.CUSTOMER_ID
ca.cust_account_id CUSTOMER_ID
FROM WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA,
hz_cust_acct_sites_all ca,
hz_cust_site_uses_all su
WHERE WDA.DELIVERY_ID = p_delivery_id
AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND WDD.container_flag = 'N'
AND su.site_use_id =WDD.SHIP_TO_SITE_USE_ID
AND su.cust_acct_site_id = ca.cust_acct_site_id
AND NOT EXISTS (
SELECT 'X'
FROM WSH_OTM_LOCATIONS_GTMP
WHERE LOCATION_TYPE = 'CUST_LOC'
AND LOCATION_ID = WDD.SHIP_TO_LOCATION_ID
AND CORPORATION_ID = ca.cust_account_id
);
SELECT WDD.SHIP_TO_SITE_USE_ID, COUNT(*) CNT
FROM WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_DELIVERY_DETAILS WDD
WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID = P_DELIVERY_ID
AND WDD.CONTAINER_FLAG = 'N'
GROUP BY WDD.SHIP_TO_SITE_USE_ID
ORDER BY CNT desc;
SELECT DISTINCT HCAR.CUST_ACCOUNT_ROLE_ID CONTACT_ID,
WDD.CUSTOMER_ID CUSTOMER_ID
FROM WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA,
HZ_PARTY_SITES HPS,
WSH_LOCATIONS WSL,
HZ_CUST_ACCT_SITES_ALL HCAS,
HZ_CUST_ACCOUNT_ROLES HCAR
WHERE WDA.delivery_id = p_delivery_id
AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND WDD.SHIP_TO_LOCATION_ID = WSL.WSH_LOCATION_ID
AND WSL.SOURCE_LOCATION_ID = HPS.LOCATION_ID
AND HPS.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
AND HCAS.CUST_ACCT_SITE_ID = HCAR.CUST_ACCT_SITE_ID
AND HCAR.STATUS = 'A';
SELECT 'X'
FROM WSH_OTM_LOC_CONTACTS_GTMP
WHERE CONTACT_ID = p_contact_id
AND LOCATION_ID = p_location_id
AND CORPORATION_ID = p_corporation_id
AND LOCATION_TYPE = 'CUST_LOC';
SELECT inventory_organization_id
FROM hr_locations_all
WHERE location_id = p_loc_id;
SELECT 'X'
FROM WSH_OTM_LOCATIONS_GTMP
WHERE LOCATION_ID = p_location_id
AND (p_corporation_id is NULL OR CORPORATION_ID = p_corporation_id)
AND LOCATION_TYPE = 'ORG_LOC';
INSERT_ROW_IN_LOC_GTMP
(
p_location_id => l_del_loc_info_rec.SHIP_TO_LOCATION_ID,
p_corporation_id => l_del_loc_info_rec.CUSTOMER_ID,
p_location_type => 'CUST_LOC',
x_return_status => l_return_status
);
INSERT_ROW_IN_LOC_GTMP
(
p_location_id => l_internal_loc_id,
p_corporation_id => l_internal_org_id,
p_location_type => 'ORG_LOC',
x_return_status => l_return_status
);
WSH_DEBUG_SV.log(l_module_name,'Return Status after calling INSERT_ROW_IN_LOC_GTMP is', l_return_status);
INSERT_ROW_IN_LOC_GTMP
(
p_location_id => l_del_loc_info_rec.SHIP_FROM_LOCATION_ID,
p_corporation_id => l_del_loc_info_rec.ORGANIZATION_ID,
p_location_type => 'ORG_LOC',
x_return_status => l_return_status
);
WSH_DEBUG_SV.log(l_module_name,'Return Status after calling INSERT_ROW_IN_LOC_GTMP is', l_return_status);
INSERT_ROW_IN_LOC_GTMP
(
p_location_id => dd_loc_info_rec.SHIP_TO_LOCATION_ID,
p_corporation_id => dd_loc_info_rec.CUSTOMER_ID,
p_location_type => 'CUST_LOC',
x_return_status => l_return_status
);
WSH_DEBUG_SV.log(l_module_name,'Return Status after calling INSERT_ROW_IN_LOC_GTMP is', l_return_status);
insert into WSH_OTM_LOC_CONTACTS_GTMP
(contact_id, location_id, corporation_id, location_type)
values(del_contact_rec.contact_id, l_st_loc_id,del_contact_rec.customer_id, 'CUST_LOC');
SELECT DISTINCT WDL.DELIVERY_ID
FROM WSH_DELIVERY_LEGS WDL,
WSH_TRIP_STOPS WTS,
WSH_OTM_LOCATIONS_GTMP WLT
WHERE WTS.TRIP_ID = WLT.LOCATION_ID
AND WLT.LOCATION_TYPE = 'TRIP'
AND (WTS.STOP_ID = WDL.PICK_UP_STOP_ID
OR WTS.STOP_ID = WDL.DROP_OFF_STOP_ID
);
SELECT WL.LOCATION_SOURCE_CODE LOC_TYPE,
WTS.STOP_LOCATION_ID LOCATION_ID,
WTS.STOP_ID
FROM WSH_TRIP_STOPS WTS,
WSH_OTM_LOCATIONS_GTMP WLT,
WSH_LOCATIONS WL
WHERE WTS.TRIP_ID = WLT.LOCATION_ID
AND WLT.LOCATION_TYPE = 'TRIP'
--bug 6770323: Modified AND condition
AND WL.WSH_LOCATION_ID = NVL(WTS.PHYSICAL_LOCATION_ID, WTS.STOP_LOCATION_ID);
select 'X'
from WSH_OTM_LOCATIONS_GTMP
where location_id = p_location_id
AND (p_corp_id is NULL OR corporation_id = p_corp_id)
and location_type = p_loc_type;
insert into wsh_otm_locations_gtmp
(location_id,
location_type
)
values
(
p_entity_in_rec.entity_id_tbl(i),
'TRIP'
);
INSERT_ROW_IN_LOC_GTMP
(
p_location_id => l_stop_rec.location_id,
p_corporation_id => l_corporation_id,
p_location_type => l_location_type,
x_return_status => l_return_status
);
select location_id
from hz_party_sites
where party_site_id = p_party_site_id
and party_id = p_party_id;
insert into wsh_otm_locations_gtmp
(location_id,
corporation_id,
location_type
)
values
(
p_entity_in_rec.entity_id_tbl(i),
p_entity_in_rec.parent_entity_id_tbl(i),
'CAR_LOC'
);
select SFOV.location_id FROM_LOCATION_ID, WLT.LOCATION_ID GTMP_LOCATION_ID
from WSH_SHIP_FROM_ORGS_V SFOV,
WSH_OTM_LOCATIONS_GTMP WLT
WHERE SFOV.organization_id = c_organization_id
AND SFOV.ORGANIZATION_ID = WLT.CORPORATION_ID (+)
AND WLT.LOCATION_TYPE (+) = 'ORG_LOC';
INSERT_ROW_IN_LOC_GTMP
(
p_location_id => l_location_id,
p_corporation_id => p_entity_in_rec.entity_id_tbl(i),
p_location_type => 'ORG_LOC',
x_return_status => l_return_status
);
WSH_DEBUG_SV.log(l_module_name,'return status from insert into locGtmp ', l_return_status);
select wr.state_code
from wsh_regions wr,
wsh_region_locations wrl,
wsh_locations wl
where wl.wsh_location_id = p_location_id
and wl.wsh_location_id = wrl.location_id
and wrl.region_type = 1
and wrl.region_id = wr.region_id;
l_last_update_date DATE;
select 'ORG-'|| decode(ORG.ORGANIZATION_ID,NULL,'000',ORG.ORGANIZATION_ID)||'-'|| HRL.LOCATION_ID LOCATION_XID,
ORG.NAME LOCATION_NAME,
HRL.ADDRESS_LINE_1,
HRL.ADDRESS_LINE_2,
HRL.ADDRESS_LINE_3,
HRL.TOWN_OR_CITY,
nvl(HRL.REGION_2,HRL.REGION_1) PROVINCE,
--nvl(HRL.REGION_2,HRL.REGION_1) PROVINCE_CODE,
HRL.POSTAL_CODE,
FNDTR.ISO_TERRITORY_CODE COUNTRY,
HRL.TELEPHONE_NUMBER_1 PHONE1,
HRL.TELEPHONE_NUMBER_2 PHONE2,
HRL.TELEPHONE_NUMBER_3 FAX,
ORG.NAME,
MP.ORGANIZATION_CODE,
WL.WSH_LOCATION_ID,
HRL.LOCATION_ID,
ORG.ORGANIZATION_ID,
GREATEST(HRL.LAST_UPDATE_DATE,
nvl(ORG.LAST_UPDATE_DATE, to_date('1900/01/01 00:00:01', 'YYYY/MM/DD HH24:MI:SS'))
) LAST_UPDATE_DATE
from HR_LOCATIONS_ALL HRL,
HR_ALL_ORGANIZATION_UNITS ORG,
--HR_ALL_ORGANIZATION_UNITS_TL ORGL,
--FND_LANGUAGES FNDL,
FND_TERRITORIES FNDTR,
WSH_OTM_LOCATIONS_GTMP WLT, -- global temp table
MTL_PARAMETERS MP,
WSH_LOCATIONS WL
WHERE WLT.LOCATION_ID = WL.WSH_LOCATION_ID
AND WLT.LOCATION_TYPE = 'ORG_LOC'
AND WL.LOCATION_SOURCE_CODE = 'HR'
AND WL.SOURCE_LOCATION_ID = HRL.LOCATION_ID
AND WLT.CORPORATION_ID = ORG.ORGANIZATION_ID (+)
AND ORG.ORGANIZATION_ID = MP.ORGANIZATION_ID (+)
--AND WLT.CORPORATION_ID = ORGL.ORGANIZATION_ID (+)
--AND ORGL.LANGUAGE = FNDL.LANGUAGE_CODE (+)
--AND FNDL.INSTALLED_FLAG (+) = 'B'
AND HRL.COUNTRY = FNDTR.TERRITORY_CODE(+);
SELECT HZL.LOCATION_ID,
HZL.ADDRESS1,
HZL.ADDRESS2,
HZL.ADDRESS3,
HZL.ADDRESS4,
HZL.CITY,
nvl(HZL.STATE,HZL.PROVINCE) PROVINCE,
HZL.STATE,
HZL.POSTAL_CODE POSTAL_CODE,
FNDTR.ISO_TERRITORY_CODE COUNTRY,
HP.PARTY_NAME,
HCA.CUST_ACCOUNT_ID,
HCA.ACCOUNT_NUMBER,
WL.WSH_LOCATION_ID,
GREATEST(HZL.LAST_UPDATE_DATE,
nvl(GREATEST(HP.LAST_UPDATE_DATE,HCA.LAST_UPDATE_DATE),
to_date('1900/01/01 00:00:01', 'YYYY/MM/DD HH24:MI:SS')
)
) LAST_UPDATE_DATE
FROM HZ_LOCATIONS HZL,
WSH_LOCATIONS WL,
FND_TERRITORIES FNDTR,
WSH_OTM_LOCATIONS_GTMP WLT,
HZ_PARTIES HP,
HZ_CUST_ACCOUNTS HCA
WHERE HZL.LOCATION_ID = WL.SOURCE_LOCATION_ID
AND WL.LOCATION_SOURCE_CODE = 'HZ'
AND FNDTR.TERRITORY_CODE (+) = HZL.COUNTRY
AND WL.WSH_LOCATION_ID = WLT.LOCATION_ID
AND WLT.LOCATION_TYPE = 'CUST_LOC'
AND WLT.CORPORATION_ID = HCA.CUST_ACCOUNT_ID (+)
AND HCA.PARTY_ID = HP.PARTY_ID (+);
SELECT PER_CONTACT.PERSON_FIRST_NAME,
PER_CONTACT.PERSON_LAST_NAME,
PHONE_CONTACT.PHONE_COUNTRY_CODE,
PHONE_CONTACT.PHONE_AREA_CODE,
PHONE_CONTACT.PHONE_NUMBER,
PER_CONTACT. EMAIL_ADDRESS,
HOC.JOB_TITLE,
HCAR.CUST_ACCOUNT_ROLE_ID,
GREATEST(
GREATEST(
PHONE_CONTACT.LAST_UPDATE_DATE,
GREATEST(
HREL.LAST_UPDATE_DATE,
GREATEST(
HOC.LAST_UPDATE_DATE,
HCAR.LAST_UPDATE_DATE
)
)
),
PER_CONTACT.LAST_UPDATE_DATE
) LAST_UPDATE_DATE
FROM HZ_CUST_ACCOUNT_ROLES HCAR,
HZ_RELATIONSHIPS HREL,
HZ_ORG_CONTACTS HOC,
HZ_CONTACT_POINTS PHONE_CONTACT,
HZ_PARTIES PER_CONTACT,
WSH_OTM_LOC_CONTACTS_GTMP WLCT
WHERE HREL.PARTY_ID = HCAR.PARTY_ID
AND HCAR.ROLE_TYPE = 'CONTACT'
AND HREL.RELATIONSHIP_ID = HOC.PARTY_RELATIONSHIP_ID
AND HREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HREL.SUBJECT_TYPE = 'PERSON'
AND HREL.DIRECTIONAL_FLAG = 'F'
AND HREL.SUBJECT_ID = PER_CONTACT.PARTY_ID
AND PHONE_CONTACT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND PHONE_CONTACT.OWNER_TABLE_ID(+) = HREL.PARTY_ID
AND PHONE_CONTACT.CONTACT_POINT_TYPE(+) = 'PHONE'
AND PHONE_CONTACT.PHONE_LINE_TYPE(+) = 'GEN'
AND PHONE_CONTACT.PRIMARY_FLAG(+) = 'Y'
AND HCAR.CUST_ACCOUNT_ROLE_ID = WLCT.CONTACT_ID
AND WLCT.LOCATION_TYPE = 'CUST_LOC'
AND WLCT.LOCATION_ID = p_location_id
AND (WLCT.CORPORATION_ID = p_corp_id or p_corp_id is NULL)
ORDER BY LAST_UPDATE_DATE DESC;
SELECT WCV.CARRIER_ID,
HPS.PARTY_SITE_ID CARRIER_SITE_ID,
HP.PARTY_NAME CARRIER_NAME,
substrb(HP.PARTY_NAME,1,10)||','||substrb(HZL.CITY,1,10)||','|| substrb(HZL.STATE,1,4)||','||substrb(HZL.COUNTRY,1,2) LOCATION_NAME,
HZL.ADDRESS1,
HZL.ADDRESS2,
HZL.ADDRESS3,
HZL.ADDRESS4,
HZL.CITY CITY,
nvl(HZL.STATE,HZL.PROVINCE) PROVINCE,
--nvl(HZL.STATE,HZL.PROVINCE) PROVINCE_CODE,
HZL.POSTAL_CODE POSTAL_CODE,
FNDTR.ISO_TERRITORY_CODE COUNTRY,
nvl(WCS.SUPPLIER_SITE_ID,WCV.SUPPLIER_SITE_ID) SUPPLIER_SITE_ID,
WCV.SUPPLIER_ID,
WCV.SUPPLIER_SITE_ID CAR_SUPPLIER_SITE_ID, -- bug#7218387: needs to pass supplier_site_id at carrier level.
WCV.SCAC_CODE,
HZL.LOCATION_ID,
WSL.WSH_LOCATION_ID,
HPS.PARTY_SITE_NUMBER,
HPS.PARTY_SITE_NUMBER CARRIER_SITE_NUMBER,
HZL.LAST_UPDATE_DATE HZL_LAST_UPD_DATE,
WCV.LAST_UPDATE_DATE WCV_LAST_UPD_DATE,
HPS.LAST_UPDATE_DATE HPS_LAST_UPD_DATE,
HP.LAST_UPDATE_DATE HP_LAST_UPD_DATE
FROM HZ_LOCATIONS HZL,
FND_TERRITORIES FNDTR,
WSH_OTM_LOCATIONS_GTMP wlt,
WSH_CARRIERS WCV,
WSH_CARRIER_SITES WCS,
HZ_PARTY_SITES HPS,
HZ_PARTIES HP,
WSH_LOCATIONS WSL
WHERE WCV.CARRIER_ID = HPS.PARTY_ID
AND HPS.LOCATION_ID = HZL.LOCATION_ID
AND WSL.SOURCE_LOCATION_ID = HZL.LOCATION_ID
AND WSL.LOCATION_SOURCE_CODE = 'HZ'
AND FNDTR.TERRITORY_CODE (+) = HZL.COUNTRY
AND HPS.PARTY_SITE_ID = WLT.LOCATION_ID
AND WLT.LOCATION_TYPE = 'CAR_LOC'
AND WCV.CARRIER_ID = WLT.CORPORATION_ID
AND WCV.CARRIER_ID = HP.PARTY_ID
AND HPS.PARTY_SITE_ID = WCS.CARRIER_SITE_ID(+);
SELECT org_id
FROM ap_supplier_sites_all
WHERE vendor_site_id = p_supplier_site_id
AND ROWNUM =1;
WSH_DEBUG_SV.log(l_module_name,'org_loc_rec.LAST_UPDATE_DATE',org_loc_rec.LAST_UPDATE_DATE);
P_ENTITY_UPDATED_DATE => org_loc_rec.last_update_date,
X_SUBSTITUTE_ENTITY => l_substitute_entity,
P_TRANSMISSION_ID => P_TRANSMISSION_ID,
X_SEND_ALLOWED => l_send_allowed,
X_RETURN_STATUS => l_return_status
);
l_contact_first_name_tbl.delete;
l_contact_last_name_tbl.delete;
l_contact_ph_cntr_code_tbl.delete;
l_contact_ph_area_code_tbl.delete;
l_contact_ph_number_tbl.delete;
l_contact_email_addr_tbl.delete;
l_contact_job_title_tbl.delete;
l_contact_id_tbl.delete;
l_contact_last_upd_date_tbl.delete;
l_last_update_date := greatest(nvl(l_contact_last_upd_date_tbl(1),cust_loc_rec.last_update_date),cust_loc_rec.last_update_date);
l_last_update_date := cust_loc_rec.last_update_date;
P_ENTITY_UPDATED_DATE => l_last_update_date,
X_SUBSTITUTE_ENTITY => l_substitute_entity,
P_TRANSMISSION_ID => P_TRANSMISSION_ID,
X_SEND_ALLOWED => l_send_allowed,
X_RETURN_STATUS => l_return_status
);
WSH_DEBUG_SV.log(l_module_name,'cust_loc_rec.LAST_UPDATE_DATE',cust_loc_rec.LAST_UPDATE_DATE);
WSH_DEBUG_SV.log(l_module_name,'l_last_update_date', l_last_update_date);
l_last_update_date := GREATEST (carrier_loc_rec.HZL_LAST_UPD_DATE, GREATEST (carrier_loc_rec.WCV_LAST_UPD_DATE, GREATEST (carrier_loc_rec.HPS_LAST_UPD_DATE, carrier_loc_rec.HP_LAST_UPD_DATE)));
P_ENTITY_UPDATED_DATE => l_last_update_date,
X_SUBSTITUTE_ENTITY => l_substitute_entity,
P_TRANSMISSION_ID => P_TRANSMISSION_ID,
X_SEND_ALLOWED => l_send_allowed,
X_RETURN_STATUS => l_return_status
);
WSH_DEBUG_SV.log(l_module_name,'l_last_update_date', l_last_update_date);
select WSH_OTM_SYNC_REF_DATA_LOG_S.nextval into l_transmission_id from dual;
SELECT WL.LOCATION_SOURCE_CODE LOC_TYPE,
WL.SOURCE_LOCATION_ID LOCATION_ID
FROM WSH_TRIP_STOPS WTS,
WSH_LOCATIONS WL
WHERE WTS.STOP_ID = p_stop_id
AND WL.WSH_LOCATION_ID = nvl(WTS.PHYSICAL_LOCATION_ID,WTS.STOP_LOCATION_ID);
SELECT inventory_organization_id
FROM hr_locations_all
WHERE location_id = p_loc_id;