[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Geometries (p_location_ids IN ID_Tbl_Type,
p_latitudes IN ID_Tbl_Type,
p_longitudes IN ID_Tbl_Type,
l_debug_on IN BOOLEAN,
x_return_status OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
k NUMBER;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_GEOMETRIES';
UPDATE wsh_locations
SET geometry = l_geometry
WHERE wsh_location_id = p_location_ids(k);
'Update Geometry failed for location id : ' || p_location_ids(k));
x_error_msg := 'UNEXP. Error in Update Geometries: ' || sqlerrm;
x_error_msg := 'UNEXP. Error in Update Geometries: ' || sqlerrm;
pUpdateLocationIdTbl ID_Tbl_Type;
pInsertLocationIdTbl ID_Tbl_Type;
CURSOR Get_Ext_Update_Loc (l_start_date DATE, l_end_date DATE) IS
SELECT SOURCE_LOCATION_ID,
HZ.ADDRESS1,
HZ.ADDRESS2,
HZ.ADDRESS3,
HZ.ADDRESS4,
HZ.COUNTRY,
HZ.STATE,
HZ.PROVINCE,
HZ.COUNTY,
HZ.CITY,
HZ.POSTAL_CODE ,
HZ.ADDRESS_EXPIRATION_DATE ,
HPS.PARTY_SITE_NUMBER,
-- BUGFIX 4604769 added Postal code and country
-- Bugfix 4995478 added Address2
substrb((HPS.PARTY_SITE_NUMBER||' : '||HZ.ADDRESS1||'-'||HZ.ADDRESS2||'-'||HZ.CITY||'-'||NVL(HZ.STATE,HZ.PROVINCE)||'-'|| HZ.POSTAL_CODE||'-'||HZ.COUNTRY),1,500),
HP.PARTY_NAME,
WSH.LATITUDE,
WSH.LONGITUDE,
NVL(TZ.TIMEZONE_CODE, WSH.TIMEZONE_CODE),
SYSDATE
FROM WSH_LOCATIONS WSH, HZ_LOCATIONS HZ, HZ_PARTY_SITES HPS, HZ_PARTIES HP, FND_TIMEZONES_B TZ --3842898 : Replaced fnd_timezones_vl with fnd_timezones_b
WHERE SOURCE_LOCATION_ID between p_from_location and p_to_location
AND nvl(hz.last_update_date,sysdate) >= nvl(l_start_date, nvl(hz.last_update_date,sysdate))
AND nvl(hz.last_update_date,sysdate) < nvl(l_end_date, nvl(hz.last_update_date,sysdate)+1)
AND hz.location_id = wsh.source_location_id
AND LOCATION_SOURCE_CODE = 'HZ'
AND hps.location_id = hz.location_id
AND hp.party_id = hps.party_id
AND hps.party_site_number = wsh.location_code --bug 6281430: considering existing location_code for updation
/* in
(
SELECT first_value(party_site_number)
OVER (ORDER BY status ASC) AS party_site_number
FROM hz_party_sites hps1
WHERE hps1.location_id = hz.location_id
)*/
AND hz.timezone_id = tz.upgrade_tz_id(+)
ORDER BY HZ.LOCATION_ID;
CURSOR Get_Ext_Insert_Loc (l_start_date DATE, l_end_date DATE) IS
SELECT HZ.LOCATION_ID,
HZ.ADDRESS1,
HZ.ADDRESS2,
HZ.ADDRESS3,
HZ.ADDRESS4,
HZ.COUNTRY,
HZ.STATE,
HZ.PROVINCE,
HZ.COUNTY,
HZ.CITY,
HZ.POSTAL_CODE ,
HZ.ADDRESS_EXPIRATION_DATE ,
HPS.PARTY_SITE_NUMBER,
-- BUGFIX 4604769 added Postal code and country
-- Bugfix 4995478 added Address2
substrb((HPS.PARTY_SITE_NUMBER||' : '||HZ.ADDRESS1||'-'||HZ.ADDRESS2||'-'||HZ.CITY||'-'||NVL(HZ.STATE,HZ.PROVINCE)||'-'|| HZ.POSTAL_CODE||'-'||HZ.COUNTRY),1,500),
HP.PARTY_NAME,
NULL,
NULL,
TZ.TIMEZONE_CODE
FROM HZ_LOCATIONS HZ, HZ_PARTY_SITES HPS, HZ_PARTIES HP, FND_TIMEZONES_VL TZ
WHERE HZ.LOCATION_ID between p_from_location and p_to_location
AND nvl(hz.last_update_date,sysdate) >= nvl(l_start_date, nvl(hz.last_update_date,sysdate))
AND nvl(hz.last_update_date,sysdate) < nvl(l_end_date, nvl(hz.last_update_date,sysdate)+1)
AND hz.location_id NOT IN ( select /*+ INDEX (wl,WSH_LOCATIONS_N1) */
wl.source_location_id
from wsh_locations wl
where wl.location_source_code = 'HZ'
and wl.source_location_id = hz.location_id)
AND hps.location_id = hz.location_id
AND hp.party_id = hps.party_id
AND hps.party_site_number in
(
SELECT first_value(party_site_number)
OVER (ORDER BY status ASC) AS party_site_number
FROM hz_party_sites hps1
WHERE hps1.location_id = hz.location_id
)
AND hz.timezone_id = tz.upgrade_tz_id(+)
ORDER BY HZ.LOCATION_ID;
CURSOR Get_Int_Update_Loc (l_start_date DATE, l_end_date DATE) IS
SELECT SOURCE_LOCATION_ID,
hr.ADDRESS_LINE_1,
hr.ADDRESS_LINE_2,
hr.ADDRESS_LINE_3,
hr.LOC_INFORMATION13,
hr.COUNTRY,
DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_2), -- Bug 5108734
DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, hr.REGION_3), -- Bug 5108734
DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_1), -- Bug 5108734
hr.TOWN_OR_CITY,
hr.POSTAL_CODE,
hr.INACTIVE_DATE,
HRTL.LOCATION_CODE,
-- BUGFIX 4604769 added Postal code and country
-- Bugfix 4995478 added Address_line_2
substrb((HRTL.LOCATION_CODE||' : '||HR.ADDRESS_LINE_1||'-'||hr.ADDRESS_LINE_2||'-'||HR.TOWN_OR_CITY||'-'||
DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, REGION_2)||'-'||hr.POSTAL_CODE||'-'||hr.COUNTRY),1,500),
OU.NAME,
WSH.LATITUDE,
WSH.LONGITUDE,
NVL(HR.TIMEZONE_CODE, WSH.TIMEZONE_CODE),
SYSDATE
FROM WSH_LOCATIONS WSH, HR_LOCATIONS_ALL HR, HR_LOCATIONS_ALL_TL HRTL, hr_organization_units ou, mtl_parameters mp
WHERE SOURCE_LOCATION_ID between p_from_location and p_to_location
AND nvl(hr.last_update_date,sysdate) >= nvl(l_start_date, nvl(hr.last_update_date,sysdate))
AND nvl(hr.last_update_date,sysdate) < nvl(l_end_date, nvl(hr.last_update_date,sysdate)+1)
AND hr.location_id = wsh.source_location_id
AND HRTL.LOCATION_ID = HR.LOCATION_ID
AND HRTL.LANGUAGE = USERENV('LANG')
AND LOCATION_SOURCE_CODE = 'HR'
AND hr.location_id = ou.location_id
AND ou.organization_id = mp.organization_id
union
SELECT SOURCE_LOCATION_ID,
hr.ADDRESS_LINE_1,
hr.ADDRESS_LINE_2,
hr.ADDRESS_LINE_3,
hr.LOC_INFORMATION13,
hr.COUNTRY,
DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_2), -- Bug 5108734
DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, hr.REGION_3), -- Bug 5108734
DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_1), -- Bug 5108734
hr.TOWN_OR_CITY,
hr.POSTAL_CODE,
hr.INACTIVE_DATE,
HRTL.LOCATION_CODE,
-- BUGFIX 4604769 added Postal code and country
-- Bugfix 4995478 added Address_line_2
substrb((HRTL.LOCATION_CODE||' : '||HR.ADDRESS_LINE_1||'-'||hr.ADDRESS_LINE_2||'-'||HR.TOWN_OR_CITY||'-'||
DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, REGION_2)||'-'||hr.POSTAL_CODE||'-'||hr.COUNTRY),1,500),
'XXX' NAME,
WSH.LATITUDE,
WSH.LONGITUDE,
NVL(HR.TIMEZONE_CODE, WSH.TIMEZONE_CODE),
SYSDATE
FROM WSH_LOCATIONS WSH, HR_LOCATIONS_ALL HR, HR_LOCATIONS_ALL_TL HRTL,
PO_LOCATION_ASSOCIATIONS_ALL PLA
WHERE SOURCE_LOCATION_ID between p_from_location and p_to_location
AND nvl(hr.last_update_date,sysdate) >= nvl(l_start_date, nvl(hr.last_update_date,sysdate))
AND nvl(hr.last_update_date,sysdate) < nvl(l_end_date, nvl(hr.last_update_date,sysdate)+1)
AND hr.location_id = wsh.source_location_id
AND HRTL.LOCATION_ID = HR.LOCATION_ID
AND HRTL.LANGUAGE = USERENV('LANG')
AND LOCATION_SOURCE_CODE = 'HR'
AND hr.location_id = pla.location_id
AND hr.location_id not in (select ou.location_id from
hr_all_organization_units ou,mtl_parameters mp WHERE ou.organization_id = mp.organization_id
and ou.location_id = hr.location_id)
union
SELECT SOURCE_LOCATION_ID,
hr.ADDRESS_LINE_1,
hr.ADDRESS_LINE_2,
hr.ADDRESS_LINE_3,
hr.LOC_INFORMATION13,
hr.COUNTRY,
DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_2), -- Bug 5108734
DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, hr.REGION_3), -- Bug 5108734
DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_1), -- Bug 5108734
hr.TOWN_OR_CITY,
hr.POSTAL_CODE,
hr.INACTIVE_DATE,
HRTL.LOCATION_CODE,
-- BUGFIX 4604769 added Postal code and country
-- Bugfix 4995478 added ADDRESS_LINE_2
substrb((HRTL.LOCATION_CODE||' : '||HR.ADDRESS_LINE_1||'-'||hr.ADDRESS_LINE_2||'-'||HR.TOWN_OR_CITY||'-'||
DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, REGION_2)||'-'||hr.POSTAL_CODE||'-'||hr.COUNTRY),1,500),
'XXX' NAME,
WSH.LATITUDE,
WSH.LONGITUDE,
NVL(HR.TIMEZONE_CODE, WSH.TIMEZONE_CODE),
SYSDATE
FROM WSH_LOCATIONS WSH, HR_LOCATIONS_ALL HR, HR_LOCATIONS_ALL_TL HRTL
WHERE SOURCE_LOCATION_ID between p_from_location and p_to_location
AND nvl(hr.last_update_date,sysdate) >= nvl(l_start_date, nvl(hr.last_update_date,sysdate))
AND nvl(hr.last_update_date,sysdate) < nvl(l_end_date, nvl(hr.last_update_date,sysdate)+1)
AND hr.location_id = wsh.source_location_id
AND HRTL.LOCATION_ID = HR.LOCATION_ID
AND HRTL.LANGUAGE = USERENV('LANG')
AND LOCATION_SOURCE_CODE = 'HR'
AND p_caller IN ('PO','HR')
AND hr.location_id not in (select ou.location_id from
hr_all_organization_units ou,mtl_parameters mp WHERE ou.organization_id = mp.organization_id
and ou.location_id = hr.location_id)
ORDER BY SOURCE_LOCATION_ID;
CURSOR Get_Int_Insert_Loc (l_start_date DATE, l_end_date DATE) IS
SELECT HR.LOCATION_ID internal_location_id,
hr.ADDRESS_LINE_1,
hr.ADDRESS_LINE_2,
hr.ADDRESS_LINE_3,
hr.LOC_INFORMATION13,
hr.COUNTRY,
DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_2), -- Bug 5108734
DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, hr.REGION_3), -- Bug 5108734
DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_1), -- Bug 5108734
hr.TOWN_OR_CITY,
hr.POSTAL_CODE,
hr.INACTIVE_DATE,
HRTL.LOCATION_CODE,
-- BUGFIX 4604769 added Postal code and country
-- Bugfix 4995478 added ADDRESS_LINE_2
substrb((HRTL.LOCATION_CODE||' : '||HR.ADDRESS_LINE_1||'-'||hr.ADDRESS_LINE_2||'-'||HR.TOWN_OR_CITY||'-'||
DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, REGION_2)||'-'||hr.POSTAL_CODE||'-'||hr.COUNTRY),1,500),
OU.NAME,
NULL,
NULL,
HR.TIMEZONE_CODE
FROM HR_LOCATIONS_ALL HR, HR_LOCATIONS_ALL_TL HRTL, hr_organization_units ou, mtl_parameters mp
WHERE HR.LOCATION_ID between p_from_location and p_to_location
AND nvl(hr.last_update_date,sysdate) >= nvl(l_start_date, nvl(hr.last_update_date,sysdate))
AND nvl(hr.last_update_date,sysdate) < nvl(l_end_date, nvl(hr.last_update_date,sysdate)+1)
AND hr.location_id NOT IN ( select wl.source_location_id from wsh_locations wl
where wl.location_source_code = 'HR'
and wl.source_location_id = hr.location_id)
AND HRTL.LOCATION_ID = HR.LOCATION_ID
AND HRTL.LANGUAGE = USERENV('LANG')
AND hr.location_id = ou.location_id
AND ou.organization_id = mp.organization_id
union
SELECT HR.LOCATION_ID internal_location_id,
hr.ADDRESS_LINE_1,
hr.ADDRESS_LINE_2,
hr.ADDRESS_LINE_3,
hr.LOC_INFORMATION13,
hr.COUNTRY,
DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_2),
DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, hr.REGION_3),
DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_1),
hr.TOWN_OR_CITY,
hr.POSTAL_CODE,
hr.INACTIVE_DATE,
HRTL.LOCATION_CODE,
-- BUGFIX 4604769 added Postal code and country
-- Bugfix 4995478 added ADDRESS_LINE_2
substrb((HRTL.LOCATION_CODE||' : '||HR.ADDRESS_LINE_1||'-'||hr.ADDRESS_LINE_2||'-'||HR.TOWN_OR_CITY||'-'||
DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, REGION_2)||'-'||hr.POSTAL_CODE||'-'||hr.COUNTRY),1,500),
'XXX' NAME,
NULL,
NULL,
HR.TIMEZONE_CODE
FROM HR_LOCATIONS_ALL HR, HR_LOCATIONS_ALL_TL HRTL,
PO_LOCATION_ASSOCIATIONS_ALL PLA
WHERE HR.LOCATION_ID between p_from_location and p_to_location
AND nvl(hr.last_update_date,sysdate) >= nvl(l_start_date, nvl(hr.last_update_date,sysdate))
AND nvl(hr.last_update_date,sysdate) < nvl(l_end_date, nvl(hr.last_update_date,sysdate)+1)
AND hr.location_id NOT IN ( select wl.source_location_id from wsh_locations wl
where wl.location_source_code = 'HR'
and wl.source_location_id = hr.location_id)
AND HRTL.LOCATION_ID = HR.LOCATION_ID
AND HRTL.LANGUAGE = USERENV('LANG')
AND hr.location_id = pla.location_id
AND hr.location_id not in (select ou.location_id from
hr_all_organization_units ou,mtl_parameters mp WHERE ou.organization_id = mp.organization_id
and ou.location_id = hr.location_id)
union
SELECT HR.LOCATION_ID internal_location_id,
hr.ADDRESS_LINE_1,
hr.ADDRESS_LINE_2,
hr.ADDRESS_LINE_3,
hr.LOC_INFORMATION13,
hr.COUNTRY,
DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_2),
DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, hr.REGION_3),
DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_1),
hr.TOWN_OR_CITY,
hr.POSTAL_CODE,
hr.INACTIVE_DATE,
HRTL.LOCATION_CODE,
-- BUGFIX 4604769 added Postal code and country
-- Bugfix 4995478 added ADDRESS_LINE_2
substrb((HRTL.LOCATION_CODE||' : '||HR.ADDRESS_LINE_1||'-'||hr.ADDRESS_LINE_2||'-'||HR.TOWN_OR_CITY||'-'||
DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, REGION_2)||'-'||hr.POSTAL_CODE||'-'||hr.COUNTRY),1,500),
'XXX' NAME,
NULL,
NULL,
HR.TIMEZONE_CODE
FROM HR_LOCATIONS_ALL HR, HR_LOCATIONS_ALL_TL HRTL
WHERE HR.LOCATION_ID between p_from_location and p_to_location
AND nvl(hr.last_update_date,sysdate) >= nvl(l_start_date, nvl(hr.last_update_date,sysdate))
AND nvl(hr.last_update_date,sysdate) < nvl(l_end_date, nvl(hr.last_update_date,sysdate)+1)
AND hr.location_id NOT IN ( select wl.source_location_id from wsh_locations wl
where wl.location_source_code = 'HR'
and wl.source_location_id = hr.location_id)
AND HRTL.LOCATION_ID = HR.LOCATION_ID
AND HRTL.LANGUAGE = USERENV('LANG')
AND p_caller = 'PO'
AND hr.location_id not in (select ou.location_id from
hr_all_organization_units ou,mtl_parameters mp WHERE ou.organization_id = mp.organization_id
and ou.location_id = hr.location_id)
-- Added following UNION for bug 4337181 (FP of Bug 4255943)
union
SELECT HR.LOCATION_ID internal_location_id,
hr.ADDRESS_LINE_1,
hr.ADDRESS_LINE_2,
hr.ADDRESS_LINE_3,
hr.LOC_INFORMATION13,
hr.COUNTRY,
DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_2),
DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, hr.REGION_3),
DECODE(hr.style, 'CA', NULL, 'CA_GLB', NULL, hr.REGION_1),
hr.TOWN_OR_CITY,
hr.POSTAL_CODE,
hr.INACTIVE_DATE,
HRTL.LOCATION_CODE,
-- BUGFIX 404769 added Postal code and country
-- Bugfix 4995478 added ADDRESS_LINE_2
substrb((HRTL.LOCATION_CODE||' : '||HR.ADDRESS_LINE_1||'-'||hr.ADDRESS_LINE_2||'-'||HR.TOWN_OR_CITY||'-'||
DECODE(hr.style, 'CA', hr.REGION_1, 'CA_GLB', hr.REGION_1, REGION_2)||'-'||hr.POSTAL_CODE||'-'||hr.COUNTRY),1,500),
'XXX' NAME,
NULL,
NULL,
HR.TIMEZONE_CODE
FROM HR_LOCATIONS_ALL HR, HR_LOCATIONS_ALL_TL HRTL
WHERE HR.LOCATION_ID between p_from_location and p_to_location
AND nvl(hr.last_update_date,sysdate) >= nvl(l_start_date, nvl(hr.last_update_date,sysdate))
AND nvl(hr.last_update_date,sysdate) < nvl(l_end_date, nvl(hr.last_update_date,sysdate)+1)
AND hr.location_id NOT IN ( select wl.source_location_id from wsh_locations wl
where wl.location_source_code = 'HR'
and wl.source_location_id = hr.location_id)
AND HR.Ship_To_Site_Flag = 'Y'
AND HRTL.LOCATION_ID = HR.LOCATION_ID
AND HRTL.LANGUAGE = USERENV('LANG')
AND nvl(p_caller, 'WSH') in ( 'WSH', 'HR' )
AND hr.location_id not in (select ou.location_id from
hr_all_organization_units ou,mtl_parameters mp WHERE ou.organization_id = mp.organization_id
and ou.location_id = hr.location_id)
ORDER BY internal_location_id
;
p_location_idTbl => pUpdateLocationIdTbl,
p_address_1Tbl => pUpdateAddress1Tbl ,
p_address_2Tbl => pUpdateAddress2Tbl ,
p_countryTbl => pUpdateCountryTbl,
p_stateTbl => pUpdateStateTbl ,
p_provinceTbl => pUpdateProvinceTbl ,
p_countyTbl => pUpdateCountyTbl ,
p_cityTbl => pUpdateCityTbl ,
p_postal_codeTbl => pUpdatePostalCodeTbl ,
p_party_site_numberTbl => pUpdateLocCodeTbl,
p_location_codeTbl => l_dummytbl,
x_use_custom_ui_location => l_use_custom_ui_location,
x_custom_ui_loc_codeTbl => l_custom_ui_loc_codeTbl) ;
OPEN Get_Ext_Update_Loc (l_start_date, l_end_date);
FETCH Get_Ext_Update_Loc BULK COLLECT INTO
pUpdateLocationIdTbl,
pUpdateAddress1Tbl,
pUpdateAddress2Tbl,
pUpdateAddress3Tbl,
pUpdateAddress4Tbl,
pUpdateCountryTbl,
pUpdateStateTbl,
pUpdateProvinceTbl,
pUpdateCountyTbl,
pUpdateCityTbl,
pUpdatePostalCodeTbl,
pUpdateExpDateTbl,
pUpdateLocCodeTbl,
pUpdateUILocCodeTbl,
pUpdateOwnerNameTbl,
pLatitudeTbl,
pLongitudeTbl,
pTimezoneTbl,
pLastUpdateDateTbl
LIMIT l_Batchsize;
l_current_rows := Get_Ext_Update_Loc%rowcount ;
IF pUpdateLocationIdTbl.COUNT <> 0 THEN
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,' found '||pUpdateLocationIdTbl.COUNT||' hz locations for update ');
l_custom_ui_loc_codeTbl.delete ;
p_location_idTbl => pUpdateLocationIdTbl,
p_address_1Tbl => pUpdateAddress1Tbl,
p_address_2Tbl => pUpdateAddress2Tbl,
p_countryTbl => pUpdateCountryTbl,
p_stateTbl => pUpdateStateTbl,
p_provinceTbl => pUpdateProvinceTbl,
p_countyTbl => pUpdateCountyTbl,
p_cityTbl => pUpdateCityTbl,
p_postal_codeTbl => pUpdatePostalCodeTbl,
p_party_site_numberTbl => pUpdateLocCodeTbl,
p_location_codeTbl => l_dummytbl,
x_use_custom_ui_location => l_use_custom_ui_location,
x_custom_ui_loc_codeTbl => l_custom_ui_loc_codeTbl) ;
pUpdateUILocCodeTbl.delete;
pUpdateUILocCodeTbl := l_custom_ui_loc_codeTbl;
-- pUpDateUILocCode and pUpdateLocCodeTb1 populated in the cursor.
-- get_site_number(pUpdateLocationIdTbl, pUpdateLocCodeTbl, pUpdateUILocCodeTbl);
Get_Missing_Timezones(p_LocationId_Tbl => pUpdateLocationIdTbl,
p_Country_Tbl => pUpdateCountryTbl,
p_State_Tbl => pUpdateStateTbl,
p_Province_Tbl => pUpdateProvinceTbl,
p_County_Tbl => pUpdateCountyTbl,
p_City_Tbl => pUpdateCityTbl,
p_Postal_Code_Tbl => pUpdatePostalCodeTbl,
l_debug_on => l_debug_on,
x_Latitude_Tbl => pLatitudeTbl,
x_Longitude_Tbl => pLongitudeTbl,
x_Timezone_Tbl => pTimezoneTbl,
x_return_status => x_return_status,
x_error_msg => x_sqlerr);
Update_Locations(pUpdateLocationIdTbl,l_location_source_code,x_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Error in Update_Locations ');
WSH_DEBUG_SV.logmsg(l_module_name,'Successful in the update operation');
i := pUpdateLocationIdTbl.COUNT;
WSH_FACILITIES_INTEGRATION.Create_Facilities(p_location_ids => pUpdateLocationIdTbl,
p_company_names => pUpdateOwnerNameTbl,
p_site_names => pUpdateLocCodeTbl,
x_return_status => x_return_status,
x_error_msg => x_sqlerr);
Update_Geometries(p_location_ids => pUpdateLocationIdTbl,
p_latitudes => pLatitudeTbl,
p_longitudes => pLongitudeTbl,
l_debug_on => l_debug_on,
x_return_status => x_return_status,
x_error_msg => x_sqlerr);
WSH_DEBUG_SV.logmsg(l_module_name,'Update_Geometries : ' || x_sqlerr);
EXIT WHEN Get_Ext_Update_Loc%NOTFOUND;
IF Get_Ext_Update_Loc%ISOPEN THEN
CLOSE Get_Ext_Update_Loc;
OPEN Get_Ext_Insert_Loc (l_start_date, l_end_date);
FETCH Get_Ext_Insert_Loc BULK COLLECT INTO
pInsertLocationIdTbl,
pInsertAddress1Tbl,
pInsertAddress2Tbl,
pInsertAddress3Tbl,
pInsertAddress4Tbl,
pInsertCountryTbl,
pInsertStateTbl,
pInsertProvinceTbl,
pInsertCountyTbl,
pInsertCityTbl,
pInsertPostalCodeTbl,
pInsertExpDateTbl,
pInsertLocCodeTbl,
pInsertUILocCodeTbl,
pInsertOwnerNameTbl,
pLatitudeTbl,
pLongitudeTbl,
pTimezoneTbl
LIMIT l_batchsize;
l_current_rows := Get_Ext_Insert_Loc%rowcount ;
IF pInsertLocationIdTbl.COUNT = 0 THEN
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Number of locations to be inserted or updated in this range is 0');
IF pInsertLocationIdTbl.COUNT <> 0 THEN
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,' found '||pInsertLocationIdTbl.COUNT||' hz locations for insert ');
l_custom_ui_loc_codeTbl.delete ;
p_location_idTbl => pInsertLocationIdTbl ,
p_address_1Tbl => pInsertAddress1Tbl,
p_address_2Tbl => pInsertAddress2Tbl,
p_countryTbl => pInsertCountryTbl ,
p_stateTbl => pInsertStateTbl ,
p_provinceTbl => pInsertProvinceTbl,
p_countyTbl => pInsertCountyTbl ,
p_cityTbl => pInsertCityTbl ,
p_postal_codeTbl => pInsertPostalCodeTbl,
p_party_site_numberTbl => pInsertLocCodeTbl,
p_location_codeTbl => l_dummytbl,
x_use_custom_ui_location => l_use_custom_ui_location,
x_custom_ui_loc_codeTbl => l_custom_ui_loc_codeTbl) ;
pInsertUILocCodeTbl.delete;
pInsertUILocCodeTbl := l_custom_ui_loc_codeTbl;
Get_Missing_Timezones(p_LocationId_Tbl => pInsertLocationIdTbl,
p_Country_Tbl => pInsertCountryTbl,
p_State_Tbl => pInsertStateTbl,
p_Province_Tbl => pInsertProvinceTbl,
p_County_Tbl => pInsertCountyTbl,
p_City_Tbl => pInsertCityTbl,
p_Postal_Code_Tbl => pInsertPostalCodeTbl,
l_debug_on => l_debug_on,
x_Latitude_Tbl => pLatitudeTbl,
x_Longitude_Tbl => pLongitudeTbl,
x_Timezone_Tbl => pTimezoneTbl,
x_return_status => x_return_status,
x_error_msg => x_sqlerr);
Insert_Locations(pInsertLocationIdTbl,l_location_source_code,x_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Error in Insert_Locations ');
WSH_DEBUG_SV.logmsg(l_module_name,'Successful in the insert operation');
i := pInsertLocationIdTbl.COUNT;
WSH_FACILITIES_INTEGRATION.Create_Facilities(p_location_ids => pInsertLocationIdTbl,
p_company_names => pInsertOwnerNameTbl,
p_site_names => pInsertLocCodeTbl,
x_return_status => x_return_status,
x_error_msg => x_sqlerr);
Update_Geometries(p_location_ids => pInsertLocationIdTbl,
p_latitudes => pLatitudeTbl,
p_longitudes => pLongitudeTbl,
l_debug_on => l_debug_on,
x_return_status => x_return_status,
x_error_msg => x_sqlerr);
WSH_DEBUG_SV.logmsg(l_module_name,'Update_Geometries : ' || x_sqlerr);
EXIT WHEN Get_Ext_Insert_Loc%NOTFOUND;
IF Get_Ext_Insert_Loc%ISOPEN THEN
CLOSE Get_Ext_Insert_Loc;
OPEN Get_Int_Update_Loc (l_start_date, l_end_date);
FETCH Get_Int_Update_Loc BULK COLLECT INTO
pUpdateLocationIdTbl,
pUpdateAddress1Tbl,
pUpdateAddress2Tbl,
pUpdateAddress3Tbl,
pUpdateAddress4Tbl,
pUpdateCountryTbl,
pUpdateStateTbl,
pUpdateProvinceTbl,
pUpdateCountyTbl,
pUpdateCityTbl,
pUpdatePostalCodeTbl,
pUpdateExpDateTbl,
pUpdateLocCodeTbl,
pUpdateUILocCodeTbl,
pUpdateOwnerNameTbl,
pLatitudeTbl,
pLongitudeTbl,
pTimezoneTbl,
pLastUpdateDateTbl
LIMIT l_Batchsize;
l_current_rows := Get_Int_Update_Loc%rowcount ;
IF pUpdateLocationIdTbl.COUNT <> 0 THEN
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,' found '||pUpdateLocationIdTbl.COUNT||' hr locations for update ');
l_custom_ui_loc_codeTbl.delete ;
p_location_idTbl => pUpdateLocationIdTbl,
p_address_1Tbl => pUpdateAddress1Tbl,
p_address_2Tbl => pUpdateAddress2Tbl,
p_countryTbl => pUpdateCountryTbl,
p_stateTbl => pUpdateStateTbl,
p_provinceTbl => pUpdateProvinceTbl,
p_countyTbl => pUpdateCountyTbl,
p_cityTbl => pUpdateCityTbl,
p_postal_codeTbl => pUpdatePostalCodeTbl,
p_party_site_numberTbl => l_dummytbl,
p_location_codeTbl => pUpdateLocCodeTbl,
x_use_custom_ui_location => l_use_custom_ui_location,
x_custom_ui_loc_codeTbl => l_custom_ui_loc_codeTbl);
pUpdateUILocCodeTbl.delete;
pUpdateUILocCodeTbl := l_custom_ui_loc_codeTbl;
Get_Missing_Timezones(p_LocationId_Tbl => pUpdateLocationIdTbl,
p_Country_Tbl => pUpdateCountryTbl,
p_State_Tbl => pUpdateStateTbl,
p_Province_Tbl => pUpdateProvinceTbl,
p_County_Tbl => pUpdateCountyTbl,
p_City_Tbl => pUpdateCityTbl,
p_Postal_Code_Tbl => pUpdatePostalCodeTbl,
l_debug_on => l_debug_on,
x_Latitude_Tbl => pLatitudeTbl,
x_Longitude_Tbl => pLongitudeTbl,
x_Timezone_Tbl => pTimezoneTbl,
x_return_status => x_return_status,
x_error_msg => x_sqlerr);
Update_Locations(pUpdateLocationIdTbl,l_location_source_code,x_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Error in Update_Locations ');
WSH_DEBUG_SV.logmsg(l_module_name,'Successful in the update operation');
i := pUpdateLocationIdTbl.COUNT;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_FACILITIES_INTEGRATION.CREATE_FACILITIES with ' ||pUpdateOwnerNameTbl.COUNT|| 'Company Names.');
WSH_FACILITIES_INTEGRATION.Create_Facilities(p_location_ids => pUpdateLocationIdTbl,
p_company_names => pUpdateOwnerNameTbl,
p_site_names => pUpdateLocCodeTbl,
x_return_status => x_return_status,
x_error_msg => x_sqlerr);
Update_Geometries(p_location_ids => pUpdateLocationIdTbl,
p_latitudes => pLatitudeTbl,
p_longitudes => pLongitudeTbl,
l_debug_on => l_debug_on,
x_return_status => x_return_status,
x_error_msg => x_sqlerr);
WSH_DEBUG_SV.logmsg(l_module_name,'Update_Geometries : ' || x_sqlerr);
EXIT WHEN Get_Int_Update_Loc%NOTFOUND;
IF Get_Int_Update_Loc%ISOPEN THEN
CLOSE Get_Int_Update_Loc;
OPEN Get_Int_Insert_Loc (l_start_date, l_end_date);
FETCH Get_Int_Insert_Loc BULK COLLECT INTO
pInsertLocationIdTbl,
pInsertAddress1Tbl,
pInsertAddress2Tbl,
pInsertAddress3Tbl,
pInsertAddress4Tbl,
pInsertCountryTbl,
pInsertStateTbl,
pInsertProvinceTbl,
pInsertCountyTbl,
pInsertCityTbl,
pInsertPostalCodeTbl,
pInsertExpDateTbl,
pInsertLocCodeTbl,
pInsertUILocCodeTbl,
--pUpdateOwnerNameTbl,
pInsertOwnerNameTbl,
pLatitudeTbl,
pLongitudeTbl,
pTimezoneTbl
LIMIT l_Batchsize;
l_current_rows := Get_Int_Insert_Loc%rowcount ;
IF pInsertLocationIdTbl.COUNT = 0 THEN
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Number of locations to be inserted or updated in this range is 0');
IF pInsertLocationIdTbl.COUNT <> 0 THEN
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,' found '||pInsertLocationIdTbl.COUNT||' hr locations for insert ');
l_custom_ui_loc_codeTbl.delete ;
p_location_idTbl => pInsertLocationIdTbl,
p_address_1Tbl => pInsertAddress1Tbl,
p_address_2Tbl => pInsertAddress2Tbl,
p_countryTbl => pInsertCountryTbl,
p_stateTbl => pInsertStateTbl,
p_provinceTbl => pInsertProvinceTbl,
p_countyTbl => pInsertCountyTbl,
p_cityTbl => pInsertCityTbl,
p_postal_codeTbl => pInsertPostalCodeTbl,
p_party_site_numberTbl => l_dummytbl,
p_location_codeTbl => pInsertLocCodeTbl,
x_use_custom_ui_location => l_use_custom_ui_location,
x_custom_ui_loc_codeTbl => l_custom_ui_loc_codeTbl);
pInsertUILocCodeTbl.delete;
pInsertUILocCodeTbl := l_custom_ui_loc_codeTbl;
Get_Missing_Timezones(p_LocationId_Tbl => pInsertLocationIdTbl,
p_Country_Tbl => pInsertCountryTbl,
p_State_Tbl => pInsertStateTbl,
p_Province_Tbl => pInsertProvinceTbl,
p_County_Tbl => pInsertCountyTbl,
p_City_Tbl => pInsertCityTbl,
p_Postal_Code_Tbl => pInsertPostalCodeTbl,
l_debug_on => l_debug_on,
x_Latitude_Tbl => pLatitudeTbl,
x_Longitude_Tbl => pLongitudeTbl,
x_Timezone_Tbl => pTimezoneTbl,
x_return_status => x_return_status,
x_error_msg => x_sqlerr);
Insert_Locations(pInsertLocationIdTbl, l_location_source_code,x_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Error in Insert_Locations ');
WSH_DEBUG_SV.logmsg(l_module_name,'Successful in the insert operation');
i := pInsertLocationIdTbl.COUNT;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_FACILITIES_INTEGRATION.CREATE_FACILITIES with ' ||pInsertOwnerNameTbl.COUNT|| 'Company Names.');
WSH_FACILITIES_INTEGRATION.Create_Facilities(p_location_ids => pInsertLocationIdTbl,
p_company_names => pInsertOwnerNameTbl,
p_site_names => pInsertLocCodeTbl,
x_return_status => x_return_status,
x_error_msg => x_sqlerr);
Update_Geometries(p_location_ids => pInsertLocationIdTbl,
p_latitudes => pLatitudeTbl,
p_longitudes => pLongitudeTbl,
l_debug_on => l_debug_on,
x_return_status => x_return_status,
x_error_msg => x_sqlerr);
WSH_DEBUG_SV.logmsg(l_module_name,'Update_Geometries : ' || x_sqlerr);
EXIT WHEN Get_Int_Insert_Loc%NOTFOUND;
IF Get_Int_Insert_Loc%ISOPEN THEN
CLOSE Get_Int_Insert_Loc;
SELECT party_site_number
FROM hz_party_sites hps
WHERE hps.location_id = l_location_id;
PROCEDURE insert_locations(pInsertLocationIdTbl IN ID_Tbl_Type,
p_location_source_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
i NUMBER;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_LOCATIONS';
l_start := pInsertLocationIdTbl.FIRST;
forall i in l_start..pInsertLocationIdTbl.LAST
Insert into WSH_LOCATIONS
(WSH_LOCATION_ID,
SOURCE_LOCATION_ID,
LOCATION_SOURCE_CODE,
LOCATION_CODE,
ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4,
COUNTRY,
STATE,
PROVINCE,
COUNTY,
CITY,
POSTAL_CODE,
INACTIVE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
UI_LOCATION_CODE,
LATITUDE,
LONGITUDE,
TIMEZONE_CODE)
Values (pInsertLocationIdTbl(i),
pInsertLocationIdTbl(i),
p_location_source_code,
pInsertLocCodeTbl(i),
pInsertAddress1Tbl(i),
pInsertAddress2Tbl(i),
pInsertAddress3Tbl(i),
pInsertAddress4Tbl(i),
pInsertCountryTbl(i),
pInsertStateTbl(i),
pInsertProvinceTbl(i),
pInsertCountyTbl(i),
pInsertCityTbl(i),
pInsertPostalCodeTbl(i),
pInsertExpDateTbl(i),
SYSDATE,
1,
SYSDATE,
1,
pInsertUILocCodeTbl(i),
pLatitudeTbl(i),
pLongitudeTbl(i),
pTimezoneTbl(i));
l_loc_id := pInsertLocationIdTbl(l_start + sql%rowcount);
insert_location_owners(pInsertLocationIdTbl, p_location_source_code,x_return_status);
WSH_UTIL_CORE.DEFAULT_HANDLER( 'WSH_LOCATIONS_PKG.insert_locations', l_module_name);
WSH_UTIL_CORE.printmsg('Unexpected error in Insert_Locations');
END insert_locations;
PROCEDURE update_locations(pUpdateLocationIdTbl IN ID_Tbl_Type,
p_location_source_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
i NUMBER;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_LOCATIONS';
forall i in pUpdateLocationIdTbl.FIRST..pUpdateLocationIdTbl.LAST
update WSH_LOCATIONS
set location_code = pUpdateLocCodeTbl(i),
address1 = pUpdateAddress1Tbl(i),
address2 = pUpdateAddress2Tbl(i),
address3 = pUpdateAddress3Tbl(i),
address4 = pUpdateAddress4Tbl(i),
country = pUpdateCountryTbl(i),
state = pUpdateStateTbl(i),
province = pUpdateProvinceTbl(i),
county = pUpdateCountyTbl(i),
city = pUpdateCityTbl(i),
postal_code = pUpdatePostalCodeTbl(i),
inactive_date = pUpdateExpDateTbl(i),
latitude = pLatitudeTbl(i),
longitude = pLongitudeTbl(i),
timezone_code = pTimezoneTbl(i),
ui_location_code = pUpdateUILocCodeTbl(i),
last_update_date = pLastUpdateDateTbl(i)
where SOURCE_LOCATION_ID = pUpdateLocationIdTbl(i)
and location_source_code = p_location_source_code;
l_loc_id := pUpdateLocCodeTbl(pUpdateLocationIdTbl.FIRST + sql%rowcount);
insert_location_owners(pUpdateLocationIdTbl, p_location_source_code,x_return_status);
WSH_UTIL_CORE.DEFAULT_HANDLER( 'WSH_LOCATIONS_PKG.update_locations', l_module_name);
WSH_UTIL_CORE.printmsg('Unexpected error in Update_Locations');
END update_locations;
SELECT location_owner_id
FROM wsh_location_owners
WHERE wsh_location_id = p_location_id
AND owner_type = p_owner_type
AND owner_party_id = p_party_id;
SELECT owner_party_id, location_owner_id
FROM wsh_location_owners
WHERE wsh_location_id = c_location_id;
SELECT distinct ps.party_id
FROM hz_party_sites ps
WHERE ps.location_id = c_location_id;
SELECT ou.organization_id
FROM hr_organization_units ou, mtl_parameters mp
WHERE ou.organization_id = mp.organization_id
AND ou.location_id = c_location_id;
SELECT 3
FROM wsh_carriers c
WHERE c.carrier_id = l_party_id
UNION ALL
SELECT 4
FROM hz_parties hz,
ap_suppliers su,
hz_party_usg_assignments hzusg
WHERE hz.party_id = su.party_id
AND hz.party_id = hzusg.party_id
AND hzusg.PARTY_USAGE_CODE = 'SUPPLIER'
AND su.party_id = l_party_id;
PROCEDURE insert_location_owners(pLocationIdTbl IN ID_Tbl_Type,
p_location_source_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
i NUMBER;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_LOCATION_OWNERS';
pLocLocationIdTbl.delete;
pLocOwnerIdTbl.delete;
pLocOwnerTypeTbl.delete;
WSH_DEBUG_SV.logmsg(l_module_name,' Inserting owner table count '||pLocLocationIdTbl.count);
Insert into WSH_LOCATION_OWNERS
( LOCATION_OWNER_ID,
WSH_LOCATION_ID,
OWNER_PARTY_ID,
OWNER_TYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
Values (
wsh_location_owners_s.nextval,
pLocLocationIdTbl(i),
pLocOwnerIdTbl(i),
pLocOwnerTypeTbl(i),
SYSDATE,
1,
SYSDATE,
1,
1);
END insert_location_owners;
SELECT ploc.LOCATION_ID internal_org_location_id
FROM PO_LOCATION_ASSOCIATIONS_ALL ploc,
hz_cust_site_uses_all site_uses,
hz_cust_acct_sites_all acct_sites,
HZ_PARTY_SITES sites
WHERE ploc.SITE_USE_ID = site_uses.SITE_USE_ID
AND site_uses.CUST_ACCT_SITE_ID = acct_sites.CUST_ACCT_SITE_ID
AND acct_sites.PARTY_SITE_ID = sites.PARTY_SITE_ID
AND ploc.CUSTOMER_ID = acct_sites.CUST_ACCOUNT_ID
AND sites.location_id = p_int_cust_loc_id
AND sites.location_id = p_int_cust_loc_id
AND ploc.CUSTOMER_ID = nvl(p_customer_id,ploc.CUSTOMER_ID);