[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| This procedure selects the minimum and maximum location id |
| and fires the child concurrent program depending on the |
| value of parameter p_num_of_instances |
| |
+===========================================================================*/
G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_MAP_LOCATION_REGION_PKG';
l_insert_flag VARCHAR2(1);
EXECUTE IMMEDIATE 'SELECT MIN(LOCATION_ID),MAX(LOCATION_ID)
FROM ( SELECT location_id, NTILE(:num_instances) OVER (ORDER BY location_id) worker
FROM HZ_LOCATIONS
WHERE last_update_date >= nvl(:start_date, last_update_date)
AND last_update_date < nvl(:end_date, last_update_date+1)
)
GROUP BY WORKER'
BULK COLLECT INTO l_worker_min_tab, l_worker_max_tab
USING l_num_of_instances, l_start_date, l_end_date;
EXECUTE IMMEDIATE 'SELECT MIN(LOCATION_ID),MAX(LOCATION_ID)
FROM ( SELECT location_id, NTILE(:num_instances) OVER (ORDER BY location_id) worker
FROM HR_LOCATIONS_ALL
WHERE last_update_date >= nvl(:start_date, last_update_date)
AND last_update_date < nvl(:end_date, last_update_date+1)
)
GROUP BY WORKER'
BULK COLLECT INTO l_worker_min_tab, l_worker_max_tab
USING l_num_of_instances, l_start_date, l_end_date;
EXECUTE IMMEDIATE 'SELECT MIN(LOCATION_ID),MAX(LOCATION_ID)
FROM ( SELECT location_id, NTILE(:num_instances) OVER (ORDER BY location_id) worker
FROM WSH_HR_LOCATIONS_V
WHERE last_update_date >= nvl(:start_date, last_update_date)
AND last_update_date < nvl(:end_date, last_update_date+1)
)
GROUP BY WORKER'
BULK COLLECT INTO l_worker_min_tab, l_worker_max_tab
USING l_num_of_instances, l_start_date, l_end_date;
p_insert_flag => l_insert_flag,
p_location_type => p_location_type,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_num_of_instances => p_num_of_instances
);
p_insert_flag => l_insert_flag,
p_location_type => p_location_type,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_num_of_instances => p_num_of_instances
);
| Insert_records |
| |
| DESCRIPTION |
| This API bulk inserts data into WSH_REGION_LOCATIONS table |
| |
+===========================================================================*/
PROCEDURE Insert_records
(
x_return_status OUT NOCOPY VARCHAR2
) IS
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_RECORDS';
wsh_debug_sv.logmsg(l_module_name, 'before bulk insert:' ||to_char(sysdate, 'dd/mm/yyyy hh:mi:ss'));
wsh_debug_sv.logmsg(l_module_name, 'Number of records to be bulk inserted '||l_location_id_tab.count);
INSERT INTO WSH_REGION_LOCATIONS(
region_id,
location_id,
exception_type,
region_type,
parent_region_flag,
location_source,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login )
values (
l_region_id_tab(i),
l_location_id_tab(i),
l_exceptions_tab(i),
l_region_type_tab(i),
l_parent_region_tab(i),
l_location_source_tab(i),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
);
wsh_debug_sv.logmsg(l_module_name, 'after bulk insert:' ||to_char(sysdate,'dd/mm/yyyy hh:mi:ss'));
WSH_UTIL_CORE.printmsg(' Error in Insert_records : ' || sqlerrm);
END Insert_records;
| Delete_records |
| |
| DESCRIPTION |
| This API bulk delete records from WSH_REGION_LOCATIONS table |
| |
+===========================================================================*/
PROCEDURE Delete_records
(
x_return_status OUT NOCOPY VARCHAR2
) IS
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_RECORDS';
wsh_debug_sv.log(l_module_name, 'Number of records to be bulk deleted', l_del_loc_id_tab.count);
wsh_debug_sv.logmsg(l_module_name, 'before bulk delete:' ||to_char(sysdate, 'dd/mm/yyyy hh:mi:ss'));
DELETE from wsh_region_locations where location_id = l_del_loc_id_tab(i)
and ( ( region_id in (select wrt.region_id from wsh_regions_tl wrt,
wsh_regions wr
where wrt.region_id = wr.region_id
and wrt.language = USERENV('LANG'))
) OR region_id IS NULL);
wsh_debug_sv.logmsg(l_module_name, 'after bulk delete:' ||to_char(sysdate,'dd/mm/yyyy hh:mi:ss'));
WSH_UTIL_CORE.printmsg(' Error in Delete_records: ' || sqlerrm);
END Delete_records;
| Update_records |
| |
| DESCRIPTION |
| This API updates records in WSH_REGION_LOCATIONS table |
| |
+===========================================================================*/
PROCEDURE Update_records
(
x_return_status OUT NOCOPY VARCHAR2
) IS
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_RECORDS';
wsh_debug_sv.logmsg(l_module_name, 'before bulk update:' ||to_char(sysdate, 'dd/mm/yyyy hh:mi:ss'));
UPDATE wsh_region_locations
SET exception_type = 'Y'
WHERE location_id = l_upd_loc_id_tab(cnt)
and region_id in (select wrt.region_id from wsh_regions_tl wrt,
wsh_regions wr
where wrt.region_id = wr.region_id
and wrt.language = USERENV('LANG'));
wsh_debug_sv.logmsg(l_module_name, 'after bulk update:' ||to_char(sysdate,'dd/mm/yyyy hh:mi:ss'));
WSH_UTIL_CORE.printmsg(' Error in Update_records: ' || sqlerrm);
END Update_records;
| This API inserts/updates/deletes records in |
| WSH_REGION_LOCATIONS table. |
| Calls APIs Insert_records/Update_records and delete_records |
| |
+===========================================================================*/
PROCEDURE Process_records
(
x_return_status OUT NOCOPY VARCHAR2
) IS
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_RECORDS';
delete_failed EXCEPTION;
insert_failed EXCEPTION;
update_failed EXCEPTION;
SAVEPOINT before_db_update;
Delete_records(x_return_status => l_return_status);
RAISE delete_failed;
Insert_Records(x_return_status => l_return_status);
RAISE insert_failed;
Update_Records(x_return_status => l_return_status);
RAISE update_failed;
l_location_id_tab.delete;
l_region_id_tab.delete;
l_region_type_tab.delete;
l_exceptions_tab.delete;
l_location_source_tab.delete;
l_parent_region_tab.delete;
l_del_loc_id_tab.delete;
l_upd_loc_id_tab.delete;
l_upd_loc_excp_tab.delete;
WHEN delete_failed THEN
WSH_UTIL_CORE.printmsg(' Error in Process_records - delete: ' || sqlerrm);
ROLLBACK to before_db_update;
WHEN insert_failed THEN
WSH_UTIL_CORE.printmsg(' Error in Process_records - insert: ' || sqlerrm);
ROLLBACK to before_db_update;
WHEN update_failed THEN
WSH_UTIL_CORE.printmsg(' Error in Process_records - update: ' || sqlerrm);
ROLLBACK to before_db_update;
| This API selects all the location data into PL/SQL table |
| types and calls the Map_Location_To_Region by passing the |
| location information |
| |
+===========================================================================*/
PROCEDURE Mapping_Regions_Main (
p_location_type IN VARCHAR2,
p_from_location IN NUMBER,
p_to_location IN NUMBER,
p_start_date IN VARCHAR2,
p_end_date IN VARCHAR2,
p_insert_flag IN BOOLEAN default TRUE, -- Bug 4722963
x_return_status OUT NOCOPY VARCHAR2,
x_sqlcode OUT NOCOPY NUMBER,
x_sqlerr out NOCOPY varchar2) IS
l_return_status VARCHAR2(20);
SELECT
l.wsh_location_id,
t.territory_short_name,
t.territory_code,
nvl(l.state, l.province) state,
l.city city,
l.postal_code,
l.inactive_date
FROM
wsh_locations l,
fnd_territories_tl t
WHERE
t.territory_code = l.country and
t.language = userenv('LANG') and
l.wsh_location_id between p_from_location and p_to_location and
l.location_source_code = 'HZ' and
l.last_update_date >= nvl(l_start_date, l.last_update_date) and
l.last_update_date < nvl(l_end_date, l.last_update_date+1)
order by t.territory_code;
SELECT
l.wsh_location_id,
t.territory_short_name,
t.territory_code,
nvl(l.state, l.province) state,
l.city city,
l.postal_code,
l.inactive_date
FROM
wsh_locations l,
fnd_territories_tl t
WHERE
t.territory_code = l.country and
t.language = userenv('LANG') and
l.wsh_location_id between p_from_location and p_to_location and
l.location_source_code = 'HR' and
l.last_update_date >= nvl(l_start_date, l.last_update_date) and
l.last_update_date < nvl(l_end_date, l.last_update_date+1)
order by t.territory_code;
SELECT
l.wsh_location_id,
t.territory_short_name,
t.territory_code,
nvl(l.state, l.province) state,
l.city city,
l.postal_code,
l.location_source_code source,
l.inactive_date
FROM
wsh_locations l,
fnd_territories_tl t
WHERE
t.territory_code = l.country and
t.language = userenv('LANG') and
l.wsh_location_id between p_from_location and p_to_location and
l.last_update_date >= nvl(l_start_date, l.last_update_date) and
l.last_update_date < nvl(l_end_date, l.last_update_date+1)
order by t.territory_code;
p_insert_flag => p_insert_flag, -- Bug 4722963
x_return_status => l_return_status,
x_sqlcode => l_sqlcode,
x_sqlerr => l_sqlerr );
IF NOT p_insert_flag AND
(l_location_id_tab.COUNT > 0 OR l_del_loc_id_tab.COUNT > 0 OR l_upd_loc_id_tab.COUNT > 0 )
THEN
Process_Records(x_return_status => l_return_status);
p_insert_flag => p_insert_flag,
x_return_status => l_return_status,
x_sqlcode => l_sqlcode,
x_sqlerr => l_sqlerr );
IF NOT p_insert_flag AND
(l_location_id_tab.COUNT > 0 OR l_del_loc_id_tab.COUNT > 0 OR l_upd_loc_id_tab.COUNT > 0 )
THEN
Process_Records(x_return_status => l_return_status);
p_insert_flag => p_insert_flag,
x_return_status => l_return_status,
x_sqlcode => l_sqlcode,
x_sqlerr => l_sqlerr );
IF NOT p_insert_flag AND
(l_location_id_tab.COUNT > 0 OR l_del_loc_id_tab.COUNT > 0 OR l_upd_loc_id_tab.COUNT > 0 )
THEN
Process_Records(x_return_status => l_return_status);
| are also obtained and all these are inserted into the |
| intersection table. |
| |
+===========================================================================*/
PROCEDURE Map_Location_To_Region (
p_country IN VARCHAR2,
p_country_code IN VARCHAR2,
p_state IN VARCHAR2,
p_city IN VARCHAR2,
p_postal_code IN VARCHAR2,
p_location_id IN NUMBER,
p_location_source IN VARCHAR2,
p_inactive_date IN DATE,
p_insert_flag IN BOOLEAN DEFAULT TRUE, -- Bug 4722963
x_return_status OUT NOCOPY VARCHAR2,
x_sqlcode OUT NOCOPY NUMBER,
x_sqlerr OUT NOCOPY VARCHAR2) IS
l_region_info WSH_REGIONS_SEARCH_PKG.region_rec;
Insert_Failed EXCEPTION;
l_insert BOOLEAN;
select 'exists'
from wsh_region_locations
where location_id = c_location_id;
SELECT count(*)
FROM wsh_region_locations
WHERE location_id = p_location_id
AND ( ( region_id IN
(
SELECT wrt.region_id
FROM wsh_regions_tl wrt, wsh_regions wr
WHERE wrt.region_id = wr.region_id
AND wrt.language = USERENV('LANG'))
)
OR region_id IS NULL);
select region_id, region_type, exception_type, parent_region_flag
BULK COLLECT INTO l_region_detail_tab
from wsh_region_locations
where location_id = p_location_id
and ( ( region_id in
( select wrt.region_id
from wsh_regions_tl wrt,
wsh_regions wr
where wrt.region_id = wr.region_id
and wrt.language = USERENV('LANG') )
) OR region_id IS NULL )
order by region_type desc;
UPDATE wsh_region_locations
SET exception_type = 'Y',
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.login_id
where location_id = p_location_id;
IF p_insert_flag THEN
IF ( l_region_detail_tab.COUNT > 1 ) THEN
DELETE from wsh_region_locations where location_id = p_location_id
and ( ( region_id in (select wrt.region_id from wsh_regions_tl wrt,
wsh_regions wr
where wrt.region_id = wr.region_id
and wrt.language = USERENV('LANG'))
) OR region_id IS NULL);
IF p_insert_flag THEN
Insert_Record (
p_location_id => p_location_id,
p_region_id => NULL,
p_region_type => NULL,
p_exception => 'Y',
p_location_source => l_location_source,
p_parent_region => l_parent_region,
x_return_status => l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Insert failed for Null Region for Location : '||p_location_id);
WSH_UTIL_CORE.printmsg('Insert failed for Null Region for Location : '||p_location_id);
RAISE Insert_Failed;
DELETE from wsh_region_locations where location_id = p_location_id
and ( ( region_id in (select wrt.region_id from wsh_regions_tl wrt,
wsh_regions wr
where wrt.region_id = wr.region_id
and wrt.language = USERENV('LANG'))
) OR region_id IS NULL);
WSH_DEBUG_SV.log(l_module_name,'No of rows deleted : ' || sql%rowcount );
WSH_DEBUG_SV.logmsg(l_module_name,' Looping over l_region_table for inserting into the intersection table ');
l_insert := TRUE;
UPDATE wsh_region_locations
SET region_id = l_region_table(i).region_id,
exception_type = 'N',
parent_region_flag = l_parent_region,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.login_id
WHERE location_id = p_location_id
AND region_type = l_region_table(i).region_type;
l_insert := FALSE;
l_insert := TRUE;
DELETE FROM wsh_region_locations
WHERE location_id = p_location_id
AND region_type = l_region_detail_tab(l_region_counter).region_type
AND region_id in
( select wrt.region_id
from wsh_regions_tl wrt,
wsh_regions wr
where wr.region_id = wrt.region_id
and wrt.language = USERENV('LANG') );
WSH_DEBUG_SV.logmsg(l_module_name,'No of Regions deleted : ' || sql%rowcount );
IF l_insert THEN --{
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling Insert_Record for location id : ' || p_location_id ||
' Region Id : ' || l_region_table(i).region_id ||
' Region Type : ' || l_region_table(i).region_type ||
' Parent Region : ' || l_parent_region);
IF p_insert_flag THEN
Insert_Record (
p_location_id => p_location_id,
p_region_id => l_region_table(i).region_id,
p_region_type => l_region_table(i).region_type,
p_exception => 'N',
p_location_source => l_location_source,
p_parent_region => l_parent_region,
x_return_status => l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'After calling Insert_Record for location id :' || p_location_id);
WSH_DEBUG_SV.logmsg(l_module_name,'Insert failed for Region '||l_region_table(i).region_id||' for Location : '||p_location_id);
WSH_UTIL_CORE.printmsg('Insert failed for Region '||l_region_table(i).region_id||' for Location : '||p_location_id);
RAISE Insert_Failed;
WSH_DEBUG_SV.log(l_module_name,'No. of mapped regions after deletion and reinsert ', l_rows_after);
IF p_insert_flag THEN
UPDATE wsh_region_locations
SET exception_type = 'Y',
--Added for bug 5125837
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE location_id = p_location_id
and region_id in (select wrt.region_id from wsh_regions_tl wrt,
wsh_regions wr
where wrt.region_id = wr.region_id
and wrt.language = USERENV('LANG'));
WHEN Insert_Failed THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
WSH_DEBUG_SV.logmsg(l_module_name,'Failed in API Insert_Record');
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INSERT_FAILED');
WSH_UTIL_CORE.printmsg('Failed in API Insert_Record');
| Insert_Record |
| |
| DESCRIPTION |
| This API just inserts the record into intersection table |
| |
+===========================================================================*/
Procedure Insert_Record
(
p_location_id IN NUMBER,
p_region_id IN NUMBER,
p_region_type IN NUMBER,
p_exception IN VARCHAR2,
p_location_source IN VARCHAR2,
p_parent_region IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_region_id NUMBER := 0;
INSERT INTO WSH_REGION_LOCATIONS(
region_id,
location_id,
exception_type,
region_type,
parent_region_flag,
location_source,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login )
values (
p_region_id,
p_location_id,
p_exception,
p_region_type,
p_parent_region,
p_location_source,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
);
WSH_UTIL_CORE.printmsg(' Insert into WSH_REGION_LOCATIONS failed : ' || sqlerrm);
End Insert_Record;
| # oracle.apps.ar.hz.Location.update |
| This calls the Mapping_Regions_Main API to recreate the |
| mapping once a location gets created or a location gets |
| updated. |
| |
+===========================================================================*/
FUNCTION Rule_Location(
p_subscription_guid in raw,
p_event in out NOCOPY wf_event_t)
RETURN VARCHAR2 IS
i_status varchar2(200);
, p_insert_flag => TRUE
, x_return_status => l_return_status
, x_sqlcode => l_sqlcode
, x_sqlerr => l_sqlerr );
SELECT wsh_location_id,
source_location_id,
location_source_code,
location_code,
ui_location_code,
address1,
address2,
address3,
address4,
country,
state,
province,
county,
city,
postal_code,
inactive_date
FROM wsh_locations
WHERE source_location_id = p_source_location_id;
SELECT wsh_location_id,
source_location_id,
location_source_code,
location_code,
ui_location_code,
address1,
address2,
address3,
address4,
country,
state,
province,
county,
city,
postal_code,
inactive_date
FROM wsh_locations
WHERE wsh_location_id = p_source_location_id;
p_insert_flag => TRUE,
x_return_status => l_return_status,
x_sqlcode => l_sqlcode,
x_sqlerr => l_sqlerr);
| This API is called by Create_Location/Update_Location |
| Business Process - After Process User Hook. |
| This ensures that the whenever HR location is created or |
| updated, corresponding changes in WSH_LOCATIONS and |
| WSH_REGION_LOCATIONS happens |
+===========================================================================*/
PROCEDURE Location_User_Hook_API(
p_location_id IN NUMBER) IS
l_return_status VARCHAR2(15);
SELECT
l.location_id,
t.territory_short_name,
t.territory_code,
l.region_2 state,
l.town_or_city city,
l.postal_code,
l.inactive_date
FROM
hr_locations_all l,
fnd_territories_tl t
WHERE
t.territory_code = l.country and
t.language = userenv('LANG') and
l.location_id = p_location_id;
SELECT
l.location_id
FROM
hr_locations_all l
WHERE
l.location_id = p_location_id;
SELECT wsh_location_id
FROM wsh_locations
WHERE source_location_id = c_loc_id
AND location_source_code = 'HR';
SELECT ou.organization_id
FROM hr_all_organization_units ou,
mtl_parameters mp
WHERE mp.organization_id = ou.organization_id
AND ou.location_id = c_loc_id;
DELETE FROM wsh_locations
WHERE wsh_location_id = p_location_id;
SELECT mrp_atp_schedule_temp_s.nextVal
FROM dual;
select intransit_time
from mtl_interorg_ship_methods
where from_location_id = p_from_loc_id
and to_location_id = p_to_loc_id
and ship_method = p_ship_method_code;
SELECT intransit_time
FROM mtl_interorg_ship_methods
WHERE from_region_id = p_from_region_id
AND to_location_id = p_to_loc_id
AND ship_method = p_ship_method_code;
SELECT intransit_time
FROM mtl_interorg_ship_methods
WHERE from_location_id = p_from_loc_id
AND to_region_id = p_to_region_id
AND ship_method = p_ship_method_code;
SELECT intransit_time
FROM mtl_interorg_ship_methods
WHERE from_region_id = p_from_region_id
AND to_region_id = p_to_region_id
AND ship_method = p_ship_method_code;
SELECT ship_method_code
FROM wsh_carrier_services
WHERE carrier_id = p_carrier_id
AND enabled_flag = 'Y'
AND ( (p_mode_code IS NULL AND mode_of_transport IS NULL)
OR (p_mode_code IS NOT NULL AND mode_of_transport = p_mode_code)
)
AND ( (p_service_code IS NULL AND service_level IS NULL)
OR (p_service_code IS NOT NULL AND service_level = p_service_code)
);
v_delete_permitted varchar2(1) := NULL;
SELECT 'N'
INTO v_delete_permitted
FROM WSH_DELIVERY_DETAILS
WHERE SHIP_FROM_LOCATION_ID = P_LOCATION_ID
AND ROWNUM =1;
IF v_delete_permitted IS NOT NULL THEN
RAISE WSH_LOC_EXISTS ;
v_delete_permitted := NULL;
SELECT 'N'
INTO v_delete_permitted
FROM WSH_CALENDAR_ASSIGNMENTS WCA, WSH_LOCATIONS WSH
WHERE WCA.LOCATION_ID = P_LOCATION_ID
AND WSH.WSH_LOCATION_ID = WCA.LOCATION_ID
AND WSH.LOCATION_SOURCE_CODE = 'HR'
AND ROWNUM = 1;
IF v_delete_permitted IS NOT NULL THEN
RAISE WSH_LOC_EXISTS ;
v_delete_permitted := NULL;
SELECT 'N'
INTO v_delete_permitted
FROM WSH_DOC_SEQUENCE_CATEGORIES WDO, WSH_LOCATIONS WSH
WHERE WDO.LOCATION_ID = P_LOCATION_ID
AND WSH.WSH_LOCATION_ID = WDO.LOCATION_ID
AND WSH.LOCATION_SOURCE_CODE = 'HR'
AND ROWNUM = 1;
IF v_delete_permitted IS NOT NULL THEN
RAISE WSH_LOC_EXISTS ;
v_delete_permitted := NULL;
SELECT 'N'
INTO v_delete_permitted
FROM WSH_PICKING_RULES
WHERE SHIP_FROM_LOCATION_ID = P_LOCATION_ID
AND ROWNUM = 1;
IF v_delete_permitted IS NOT NULL THEN
RAISE WSH_LOC_EXISTS ;
v_delete_permitted := NULL;
SELECT 'N'
INTO v_delete_permitted
FROM WSH_REGION_LOCATIONS
WHERE LOCATION_ID = P_LOCATION_ID
AND REGION_ID IS NOT NULL
AND ROWNUM = 1 ;
IF v_delete_permitted IS NOT NULL THEN
RAISE WSH_LOC_EXISTS ;
v_delete_permitted := NULL;
SELECT 'N'
INTO v_delete_permitted
FROM WSH_SHIPPING_PARAMETERS
WHERE LOCATION_ID = P_LOCATION_ID
AND ROWNUM = 1 ;
IF v_delete_permitted IS NOT NULL THEN
RAISE WSH_LOC_EXISTS ;