[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE do_delete_address(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_party_id IN NUMBER,
p_party_site_id IN NUMBER,
p_ps_object_version_number IN NUMBER,
p_bill_object_version_number IN NUMBER,
p_ship_object_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2);
UPDATE
IBE_ORD_ONECLICK_ALL
SET
LAST_UPDATE_DATE = sysdate,
SHIP_TO_PTY_SITE_ID = x_party_site_id
WHERE
party_id = p_party_site.party_id
and SHIP_TO_PTY_SITE_ID is null;
UPDATE
IBE_ORD_ONECLICK_ALL
SET
LAST_UPDATE_DATE = sysdate,
BILL_TO_PTY_SITE_ID = x_party_site_id
WHERE
party_id = p_party_site.party_id
and BILL_TO_PTY_SITE_ID is null;
PROCEDURE update_address(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_party_site_id IN NUMBER,
p_ps_object_version_number IN NUMBER,
p_bill_object_version_number IN NUMBER,
p_ship_object_version_number IN NUMBER,
p_location IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
p_party_site IN HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE,
p_primary_billto IN VARCHAR2 := NULL,
p_primary_shipto IN VARCHAR2 := NULL,
p_billto IN VARCHAR2 := NULL,
p_shipto IN VARCHAR2 := NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_location_id OUT NOCOPY NUMBER,
x_party_site_id OUT NOCOPY NUMBER)
IS
l_api_name VARCHAR2(30) := 'update_address';
SELECT object_version_number
FROM hz_party_site_uses
WHERE party_site_use_id = l_site_use_id
ORDER BY party_site_use_id DESC;
Select object_version_number
from hz_locations
where location_id = l_location_id;
IBE_UTIL.debug('enter IBE_ADDRESS_V2PVT.update_address');
SAVEPOINT update_address_pvt;
IBE_UTIL.debug('no need to update anything');
IBE_UTIL.debug('party_site update status: ' || x_return_status);
HZ_PARTY_SITE_V2PUB.update_party_site(
p_init_msg_list,
l_chk_ps,
l_ps_object_version_number,
x_return_status,
x_msg_count,
x_msg_data
);
HZ_PARTY_SITE_V2PUB.update_party_site_use(
p_init_msg_list,
l_chk_psu,
l_bill_object_version_number,
x_return_status,
x_msg_count,
x_msg_data
);
HZ_PARTY_SITE_V2PUB.update_party_site_use(
p_init_msg_list,
l_chk_psu,
l_ship_object_version_number,
x_return_status,
x_msg_count,
x_msg_data
);
IBE_UTIL.debug('IBE_ADDRESS_V2PVT.update_address, location changed but no PS change');
HZ_LOCATION_V2PUB.update_location(
p_init_msg_list,
l_chk_loc,
l_loc_object_version_number,
x_return_status,
x_msg_count,
x_msg_data
);
HZ_PARTY_SITE_V2PUB.update_party_site_use(
p_init_msg_list,
l_chk_psu,
l_bill_object_version_number,
x_return_status,
x_msg_count,
x_msg_data
);
HZ_PARTY_SITE_V2PUB.update_party_site_use(
p_init_msg_list,
l_chk_psu,
l_ship_object_version_number,
x_return_status,
x_msg_count,
x_msg_data
);
IBE_UTIL.debug('IBE_ADDRESS_V2PVT.delete_address(+)');
do_delete_address(
p_api_version,
p_init_msg_list,
p_commit,
p_party_site.party_id,
p_party_site_id,
l_ps_object_version_number,
l_bill_object_version_number,
l_ship_object_version_number,
x_return_status,
x_msg_count,
x_msg_data
);
IBE_UTIL.debug('IBE_ADDRESS_V2PVT.delete_address(-)');
IBE_UTIL.debug('party_site_id: ' || to_char(p_party_site_id) || ' deleted');
UPDATE
IBE_ORD_ONECLICK_ALL
SET
LAST_UPDATE_DATE = sysdate,
SHIP_TO_PTY_SITE_ID = x_party_site_id
WHERE
SHIP_TO_PTY_SITE_ID = p_party_site_id;
UPDATE
IBE_ORD_ONECLICK_ALL
SET
LAST_UPDATE_DATE = sysdate,
BILL_TO_PTY_SITE_ID = x_party_site_id
WHERE
BILL_TO_PTY_SITE_ID = p_party_site_id;
IBE_UTIL.debug('exit IBE_ADDRESS_V2PVT.update_address');
ROLLBACK TO update_address_pvt;
ROLLBACK TO update_address_pvt;
ROLLBACK TO update_address_pvt;
PROCEDURE delete_address(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_party_id IN NUMBER,
p_party_site_id IN NUMBER,
p_ps_object_version_number IN NUMBER,
p_bill_object_version_number IN NUMBER,
p_ship_object_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_address';
IBE_UTIL.debug('enter IBE_ADDRESS_V2PVT.delete_address');
SAVEPOINT delete_address_pvt;
IBE_UTIL.debug('IBE_ADDRESS_V2PVT.delete_address(+)');
do_delete_address(
p_api_version,
p_init_msg_list,
p_commit,
p_party_id,
p_party_site_id,
p_ps_object_version_number,
p_bill_object_version_number,
p_ship_object_version_number,
x_return_status,
x_msg_count,
x_msg_data
);
IBE_UTIL.debug('IBE_ADDRESS_V2PVT.do_delete_address(-)');
IBE_UTIL.debug('party_site_id: ' || to_char(p_party_site_id) || ' deleted');
UPDATE
IBE_ORD_ONECLICK_ALL
SET
ENABLED_FLAG = 'N',
LAST_UPDATE_DATE = sysdate,
SHIP_TO_PTY_SITE_ID = null
WHERE
SHIP_TO_PTY_SITE_ID = p_party_site_id;
UPDATE
IBE_ORD_ONECLICK_ALL
SET
ENABLED_FLAG = 'N',
LAST_UPDATE_DATE = sysdate,
BILL_TO_PTY_SITE_ID = null
WHERE
BILL_TO_PTY_SITE_ID = p_party_site_id;
IBE_UTIL.debug('exit IBE_ADDRESS_V2PVT.delete_address');
ROLLBACK TO delete_address_pvt;
ROLLBACK TO delete_address_pvt;
ROLLBACK TO delete_address_pvt;
SELECT party_site_use_id, object_version_number
FROM ( SELECT party_site_use_id, object_version_number
FROM hz_party_site_uses
WHERE party_site_id = p_party_site_id
AND site_use_type = p_site_use_type
ORDER BY status, party_site_use_id DESC
)
WHERE rownum = 1;
HZ_PARTY_SITE_V2PUB.update_party_site_use (
p_init_msg_list,
l_party_site_use,
l_object_version_number,
x_return_status,
x_msg_count,
x_msg_data);
SELECT ps.party_site_id
FROM hz_party_sites ps, hz_party_site_uses psu, hz_locations loc, hr_organization_information hr
WHERE
ps.party_id = l_party_id AND
ps.status = 'A' AND
ps.location_id = loc.location_id AND
ps.party_site_id = psu.party_site_id AND
psu.primary_per_type = 'Y' AND
psu.site_use_type = site_type AND
psu.status = 'A' AND
NVL(psu.end_date,sysdate+1) > sysdate AND
hr.organization_id = l_org_id AND
hr.org_information_context = hr_type AND
hr.org_information1 = loc.country AND
( NOT EXISTS (
SELECT 1 FROM hz_cust_acct_sites_all cas1
WHERE cas1.party_site_id = ps.party_site_id
AND cas1.org_id = MO_GLOBAL.get_current_org_id()) OR
( EXISTS ( SELECT 1 FROM hz_cust_acct_sites_all
WHERE party_site_id = ps.party_site_id
AND org_id = MO_GLOBAL.get_current_org_id()
AND status = 'A') AND
(
NOT EXISTS (
SELECT 1 FROM hz_cust_acct_sites_all cas2, hz_cust_site_uses_all csu2
WHERE csu2.cust_acct_site_id = cas2.cust_acct_site_id
AND cas2.party_site_id = ps.party_site_id
AND cas2.org_id = MO_GLOBAL.get_current_org_id()
AND csu2.site_use_code = site_type) OR
EXISTS (
SELECT 1
FROM hz_cust_acct_sites_all cas, hz_cust_site_uses_all csu
WHERE cas.party_site_id = ps.party_site_id AND
cas.org_id = MO_GLOBAL.get_current_org_id() AND
csu.cust_acct_site_id (+) = cas.cust_acct_site_id AND
NVL(csu.status,'A') = 'A' AND
NVL(csu.site_use_code,site_type) = site_type)
)
)
);
SELECT ps.party_site_id
FROM hz_party_sites ps, hz_party_site_uses psu
WHERE
ps.party_id = l_party_id AND
ps.status = 'A' AND
ps.party_site_id = psu.party_site_id AND
psu.primary_per_type = 'Y' AND
psu.site_use_type = site_type AND
psu.status = 'A' AND
NVL(psu.end_date,sysdate+1) > sysdate AND
NOT EXISTS (
SELECT 1
FROM hr_organization_information hr
WHERE
hr.organization_id = l_org_id AND
hr.org_information_context = hr_type AND
rownum = 1) AND
( NOT EXISTS (
SELECT 1 FROM hz_cust_acct_sites_all cas1
WHERE cas1.party_site_id = ps.party_site_id
AND cas1.org_id = MO_GLOBAL.get_current_org_id()) OR
( EXISTS ( SELECT 1 FROM hz_cust_acct_sites_all
WHERE party_site_id = ps.party_site_id
AND org_id = MO_GLOBAL.get_current_org_id()
AND status = 'A') AND
(
NOT EXISTS (
SELECT 1 FROM hz_cust_acct_sites_all cas2, hz_cust_site_uses_all csu2
WHERE csu2.cust_acct_site_id = cas2.cust_acct_site_id
AND cas2.party_site_id = ps.party_site_id
AND cas2.org_id = MO_GLOBAL.get_current_org_id()
AND csu2.site_use_code = site_type) OR
EXISTS (
SELECT 1
FROM hz_cust_acct_sites_all cas, hz_cust_site_uses_all csu
WHERE cas.party_site_id = ps.party_site_id AND
cas.org_id = MO_GLOBAL.get_current_org_id() AND
csu.cust_acct_site_id (+) = cas.cust_acct_site_id AND
NVL(csu.status,'A') = 'A' AND
NVL(csu.site_use_code,site_type) = site_type)
)
)
);
select subject_id from hz_relationships
where party_id = l_party_id and subject_type = 'ORGANIZATION';
SELECT DISTINCT
party_site_id, location_id
INTO
x_party_site_id, x_location_id
FROM
hz_party_sites_v
WHERE
party_id = p_party_id AND
site_use_type = p_site_use_type AND
status = 'A' AND
primary_per_type = 'Y';
SELECT to_char(hz_party_site_number_s.nextval)
INTO l_party_site_number
FROM dual;
SELECT COUNT(*) INTO l_count
FROM hz_party_sites_v
WHERE party_site_number = l_party_site_number;
PROCEDURE do_delete_address(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_party_id IN NUMBER,
p_party_site_id IN NUMBER,
p_ps_object_version_number IN NUMBER,
p_bill_object_version_number IN NUMBER,
p_ship_object_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
cursor c_psu is
select party_site_use_id,site_use_type,object_version_number
from hz_party_site_uses
where party_site_id = p_party_site_id
for update nowait;
l_api_name CONSTANT VARCHAR2(30) := 'delete_address';
IBE_UTIL.debug('enter IBE_ADDRESS_V2PVT.do_delete_address');
IBE_UTIL.debug('hz_party_v2pub.update_party_site(+)');
HZ_PARTY_SITE_V2PUB.update_party_site (
p_init_msg_list,
l_party_site,
l_ps_object_version_number,
x_return_status,
x_msg_count,
x_msg_data);
IBE_UTIL.debug('hz_party_v2pub.update_party_site(-)');
IBE_UTIL.debug('hz_party_v2pub.update_party_site_use(+)');
HZ_PARTY_SITE_V2PUB.update_party_site_use(
p_init_msg_list,
l_party_site_use,
l_psu_object_version_number,
x_return_status,
x_msg_count,
x_msg_data
);
IBE_UTIL.debug('hz_party_v2pub.update_party_site_use(-)');
IBE_UTIL.debug('hz_party_v2pub.update_party_site(-)');
IBE_UTIL.debug('exit IBE_ADDRESS_V2PVT.do_delete_address');
SELECT location_id INTO l_dummy
FROM hz_locations
WHERE location_id = p_location.location_id
AND nvl(address1, l_gmiss) = nvl(p_location.address1, l_gmiss)
AND nvl(address2, l_gmiss) = nvl(p_location.address2, l_gmiss)
AND nvl(address3, l_gmiss) = nvl(p_location.address3, l_gmiss)
AND nvl(address4, l_gmiss) = nvl(p_location.address4, l_gmiss)
AND nvl(city, l_gmiss) = nvl(p_location.city, l_gmiss)
AND nvl(county, l_gmiss) = nvl(p_location.county, l_gmiss)
AND nvl(province, l_gmiss) = nvl(p_location.province, l_gmiss)
AND nvl(state, l_gmiss) = nvl(p_location.state, l_gmiss)
AND nvl(postal_code, l_gmiss) = nvl(p_location.postal_code, l_gmiss)
AND nvl(country, l_gmiss) = nvl(p_location.country, l_gmiss)
AND nvl(address_lines_phonetic, l_gmiss) = nvl(p_location.address_lines_phonetic, l_gmiss);
SELECT party_site_id INTO l_dummy
FROM hz_party_sites
WHERE party_site_id = p_party_site.party_site_id
AND nvl(party_site_name,l_gmiss)=nvl(p_party_site.party_site_name,l_gmiss)
AND nvl(addressee,l_gmiss)= nvl(p_party_site.addressee,l_gmiss)
AND (identifying_address_flag = 'Y' or p_party_site.identifying_address_flag='N');
SELECT party_site_use_id
INTO l_psu_id
FROM ( SELECT distinct party_site_use_id,status FROM hz_party_site_uses
WHERE site_use_type = p_party_site_use.site_use_type
AND party_site_id = p_party_site_use.party_site_id
AND (primary_per_type = 'N' OR primary_per_type = 'Y')
ORDER BY status,party_site_use_id desc)
WHERE rownum=1;
SELECT distinct party_site_use_id INTO l_psu_id
FROM hz_party_site_uses
WHERE NVL(status, 'A') = p_party_site_use.status
AND party_site_use_id = p_party_site_use.party_site_use_id
AND primary_per_type = p_party_site_use.primary_per_type;
IS SELECT country FROM hz_locations
WHERE location_id IN
(SELECT location_id FROM hz_party_sites
WHERE party_site_id = l_c_party_site_id);
IS SELECT org_information1 FROM hr_organization_information
WHERE organization_id = l_c_operating_unit_id
AND org_information_context = l_c_usage_code;