[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure do_create_update_loc_assign(
p_location_id IN NUMBER,
p_create_update_flag IN VARCHAR2,
x_loc_id OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2, /* Changed from OUT to IN OUT*/
p_lock_flag IN VARCHAR2 := FND_API.G_FALSE
) IS
l_org_id NUMBER;
l_wh_update_date DATE;
db_wh_update_date DATE;
l_allow_update_std VARCHAR2(1);
select * /* Bug Fix 2020712 */
into arp_standard.sysparm
from ar_system_parameters;
SELECT
NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
INTO l_org_id
FROM dual;
SELECT country, city, state, county, province, postal_code,
attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10,
wh_update_date
, actual_content_source
, date_validated
, validation_status_code
INTO l_country, l_city, l_state, l_county, l_province, l_postal_code,
l_attribute1,l_attribute2,l_attribute3,l_attribute4,l_attribute5,
l_attribute6,l_attribute7,l_attribute8,l_attribute9,l_attribute10,
l_wh_update_date
, l_actual_content_source
, l_date_validated
, l_validation_status_code
FROM HZ_LOCATIONS
WHERE location_id = p_location_id
FOR UPDATE OF location_id NOWAIT;
END; -- end of SELECT
SELECT country, city, state, county, province, postal_code,
attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10,
wh_update_date
, actual_content_source
INTO l_country, l_city, l_state, l_county, l_province, l_postal_code,
l_attribute1,l_attribute2,l_attribute3,l_attribute4,l_attribute5,
l_attribute6,l_attribute7,l_attribute8,l_attribute9,l_attribute10,
l_wh_update_date
, l_actual_content_source
FROM HZ_LOCATIONS
WHERE location_id = p_location_id;
END; -- end of SELECT ;
l_allow_update_std := nvl(fnd_profile.value('HZ_UPDATE_STD_ADDRESS'), 'Y');
IF(l_allow_update_std = 'N' AND
l_date_validated IS NOT NULL AND
l_validation_status_code IS NOT NULL) THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_LOC_NO_UPDATE');
db_wh_update_date:= l_wh_update_date;
SELECT 'Y'
INTO l_is_remit_to_location
FROM DUAL
WHERE EXISTS ( SELECT 1
FROM hz_party_sites ps
WHERE ps.location_id = p_location_id
AND ps.party_id = -1);
UPDATE hz_locations SET
country = l_country,
city = l_city ,
state = l_state ,
county = l_county ,
province = l_province,
postal_code = l_postal_code,
attribute1 = l_attribute1,
attribute2 = l_attribute2,
attribute3 = l_attribute3,
attribute4 = l_attribute4,
attribute5 = l_attribute5,
attribute6 = l_attribute6,
attribute7 = l_attribute7,
attribute8 = l_attribute8,
attribute9 = l_attribute9,
attribute10 = l_attribute10
WHERE location_id = p_location_id;
hz_location_profile_pvt.update_location_profile (
p_location_profile_rec => l_location_profile_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
IF p_create_update_flag = 'C' THEN
-- check if the combination of location_id and org_id exists in database,
-- if it exists, then tax group has created a record for a party without
-- any customer account site. we just update the loc_id accordingly for
-- that record.
BEGIN
l_count := 0;
SELECT count(*)
INTO l_count
FROM hz_loc_assignments
WHERE location_id = p_location_id
AND nvl(org_id, l_org_id) = l_org_id;
HZ_LOC_ASSIGNMENTS_PKG.INSERT_ROW(
X_Rowid => l_rowid,
X_LOCATION_ID => p_location_id,
X_LOC_ID => x_loc_id,
X_ORG_ID => l_org_id,
X_CREATED_BY => hz_utility_pub.CREATED_BY,
X_CREATION_DATE => hz_utility_pub.CREATION_DATE,
X_LAST_UPDATE_LOGIN => hz_utility_pub.LAST_UPDATE_LOGIN,
X_LAST_UPDATE_DATE => hz_utility_pub.LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => hz_utility_pub.LAST_UPDATED_BY,
X_REQUEST_ID => hz_utility_pub.REQUEST_ID,
X_PROGRAM_APPLICATION_ID => hz_utility_pub.PROGRAM_APPLICATION_ID,
X_PROGRAM_ID => hz_utility_pub.PROGRAM_ID,
X_PROGRAM_UPDATE_DATE => hz_utility_pub.PROGRAM_UPDATE_DATE,
X_WH_UPDATE_DATE => l_wh_update_date
);
else -- update loc_id for an existing location.
--Select rowid.
select rowid INTO l_rowid FROM hz_loc_assignments
WHERE location_id = p_location_id
AND nvl(org_id, l_org_id) = l_org_id;
HZ_LOC_ASSIGNMENTS_PKG.UPDATE_ROW(
X_Rowid => l_rowid,
X_LOCATION_ID => p_location_id,
X_LOC_ID => x_loc_id,
X_ORG_ID => l_org_id,
X_CREATED_BY => FND_API.G_MISS_NUM,
X_CREATION_DATE => FND_API.G_MISS_DATE,
X_LAST_UPDATE_LOGIN => hz_utility_pub.LAST_UPDATE_LOGIN,
X_LAST_UPDATE_DATE => hz_utility_pub.LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => hz_utility_pub.LAST_UPDATED_BY,
X_REQUEST_ID => hz_utility_pub.REQUEST_ID,
X_PROGRAM_APPLICATION_ID => hz_utility_pub.PROGRAM_APPLICATION_ID,
X_PROGRAM_ID => hz_utility_pub.PROGRAM_ID,
X_PROGRAM_UPDATE_DATE => hz_utility_pub.PROGRAM_UPDATE_DATE,
X_WH_UPDATE_DATE => l_wh_update_date
);
END; -- end of p_create_update_flag = 'C'
ELSIF p_create_update_flag = 'U' THEN
--Select rowid.
select rowid INTO l_rowid FROM hz_loc_assignments
WHERE location_id = p_location_id
AND nvl(org_id, l_org_id) = l_org_id;
HZ_LOC_ASSIGNMENTS_PKG.UPDATE_ROW(
X_Rowid => l_rowid,
X_LOCATION_ID => p_location_id,
X_LOC_ID => x_loc_id,
X_ORG_ID => l_org_id,
X_CREATED_BY => FND_API.G_MISS_NUM,
X_CREATION_DATE => FND_API.G_MISS_DATE,
X_LAST_UPDATE_LOGIN => hz_utility_pub.LAST_UPDATE_LOGIN,
X_LAST_UPDATE_DATE => hz_utility_pub.LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => hz_utility_pub.LAST_UPDATED_BY,
X_REQUEST_ID => hz_utility_pub.REQUEST_ID,
X_PROGRAM_APPLICATION_ID => hz_utility_pub.PROGRAM_APPLICATION_ID,
X_PROGRAM_ID => hz_utility_pub.PROGRAM_ID,
X_PROGRAM_UPDATE_DATE => hz_utility_pub.PROGRAM_UPDATE_DATE,
X_WH_UPDATE_DATE => l_wh_update_date
);
end do_create_update_loc_assign;
do_create_update_loc_assign( p_location_id,
'C',
x_loc_id,
x_return_status,
p_lock_flag);
procedure update_loc_assignment(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
p_commit IN VARCHAR2:= FND_API.G_FALSE,
p_location_id IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2, /* Changed from OUT to IN OUT*/
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_loc_id OUT NOCOPY NUMBER,
p_lock_flag IN VARCHAR2 :=FND_API.G_TRUE
) IS
l_api_name CONSTANT VARCHAR2(30) := 'update loc assignment';
SAVEPOINT update_loc_assignment_pub;
do_create_update_loc_assign( l_location_id,
'U',
x_loc_id,
x_return_status,
p_lock_flag );
HZ_TAX_ASSIGNMENT_V2PUB.update_loc_assignment(
p_location_id => l_location_id,
p_lock_flag => p_lock_flag,
p_created_by_module => 'TCA_V1_API',
p_application_id => -222,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loc_id => x_loc_id
);
ROLLBACK TO update_loc_assignment_pub;
ROLLBACK TO update_loc_assignment_pub;
ROLLBACK TO update_loc_assignment_pub;
ROLLBACK TO update_loc_assignment_pub;
END update_loc_assignment;