[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_pst_selected_datasources VARCHAR2(255);
g_pst_is_datasource_selected VARCHAR2(1) := 'N';
PROCEDURE do_update_party_site(
p_party_site_rec IN OUT NOCOPY PARTY_SITE_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE do_update_party_site_use(
p_party_site_use_rec IN OUT NOCOPY PARTY_SITE_USE_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE do_update_address(
p_party_id IN NUMBER,
p_location_id IN NUMBER
);
PROCEDURE update_acct_sites_status(
p_party_site_id IN NUMBER,
p_new_status IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
);
p_create_update_flag IN VARCHAR2,
p_party_site_rec IN party_site_rec_type,
p_old_party_site_rec IN party_site_rec_type DEFAULT NULL,
x_return_status IN OUT NOCOPY VARCHAR2
);
| select/de-select data-sources is obsoleted.
+===========================================================================*/
PROCEDURE do_create_party_site(
p_party_site_rec IN OUT NOCOPY PARTY_SITE_REC_TYPE,
x_party_site_id OUT NOCOPY NUMBER,
x_party_site_number OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_party_site_id NUMBER := p_party_site_rec.party_site_id;
SELECT 'Y'
INTO l_dummy
FROM HZ_PARTY_SITES
WHERE PARTY_SITE_ID = l_party_site_id;
SELECT 'Y'
INTO l_dummy
FROM HZ_PARTY_SITES
WHERE PARTY_SITE_NUMBER = l_party_site_number;
* For non-profile entities, the concept of select/de-select data-sources is obsoleted.
* There is no need to check if the data-source is selected.
g_pst_is_datasource_selected :=
HZ_MIXNM_UTILITY.isDataSourceSelected (
p_selected_datasources => g_pst_selected_datasources,
p_actual_content_source => l_loc_actual_content_source );
SELECT 'Y' INTO l_dummy
FROM HZ_PARTY_SITES
WHERE PARTY_ID = p_party_site_rec.party_id
AND STATUS = 'A'
/* AND HZ_MIXNM_UTILITY.isDataSourceSelected (
g_pst_selected_datasources, actual_content_source ) = 'Y'*/
AND ROWNUM = 1;
g_pst_is_datasource_selected = 'Y'*/
THEN
-- Cahnged the below call to use the actual parameter name
-- to fix the bug # 5436273
do_unmark_address_flag(p_party_id => p_party_site_rec.party_id, p_mode => 'I');
g_pst_is_datasource_selected = 'Y'*/
THEN
p_party_site_rec.identifying_address_flag := 'Y';
do_update_address(
p_party_site_rec.party_id,
p_party_site_rec.location_id);
hz_utility_v2pub.debug(p_message=>'HZ_PARTY_SITES_PKG.Insert_Row (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
HZ_PARTY_SITES_PKG.Insert_Row (
X_PARTY_SITE_ID => p_party_site_rec.party_site_id,
X_PARTY_ID => p_party_site_rec.party_id,
X_LOCATION_ID => p_party_site_rec.location_id,
X_PARTY_SITE_NUMBER => p_party_site_rec.party_site_number,
X_ATTRIBUTE_CATEGORY => p_party_site_rec.attribute_category,
X_ATTRIBUTE1 => p_party_site_rec.attribute1,
X_ATTRIBUTE2 => p_party_site_rec.attribute2,
X_ATTRIBUTE3 => p_party_site_rec.attribute3,
X_ATTRIBUTE4 => p_party_site_rec.attribute4,
X_ATTRIBUTE5 => p_party_site_rec.attribute5,
X_ATTRIBUTE6 => p_party_site_rec.attribute6,
X_ATTRIBUTE7 => p_party_site_rec.attribute7,
X_ATTRIBUTE8 => p_party_site_rec.attribute8,
X_ATTRIBUTE9 => p_party_site_rec.attribute9,
X_ATTRIBUTE10 => p_party_site_rec.attribute10,
X_ATTRIBUTE11 => p_party_site_rec.attribute11,
X_ATTRIBUTE12 => p_party_site_rec.attribute12,
X_ATTRIBUTE13 => p_party_site_rec.attribute13,
X_ATTRIBUTE14 => p_party_site_rec.attribute14,
X_ATTRIBUTE15 => p_party_site_rec.attribute15,
X_ATTRIBUTE16 => p_party_site_rec.attribute16,
X_ATTRIBUTE17 => p_party_site_rec.attribute17,
X_ATTRIBUTE18 => p_party_site_rec.attribute18,
X_ATTRIBUTE19 => p_party_site_rec.attribute19,
X_ATTRIBUTE20 => p_party_site_rec.attribute20,
X_ORIG_SYSTEM_REFERENCE => p_party_site_rec.orig_system_reference,
X_LANGUAGE => p_party_site_rec.language,
X_MAILSTOP => p_party_site_rec.mailstop,
X_IDENTIFYING_ADDRESS_FLAG => p_party_site_rec.identifying_address_flag,
X_STATUS => p_party_site_rec.status,
X_PARTY_SITE_NAME => p_party_site_rec.party_site_name,
X_ADDRESSEE => p_party_site_rec.addressee,
X_OBJECT_VERSION_NUMBER => 1,
X_CREATED_BY_MODULE => p_party_site_rec.created_by_module,
X_APPLICATION_ID => p_party_site_rec.application_id,
X_ACTUAL_CONTENT_SOURCE => l_loc_actual_content_source,
-- Bug 3175816.
X_GLOBAL_LOCATION_NUMBER => p_party_site_rec.global_location_number,
X_DUNS_NUMBER_C => p_party_site_rec.duns_number_c
);
hz_utility_v2pub.debug(p_message=>'HZ_PARTY_SITES_PKG.Insert_Row (-) ' ||
'x_party_site_id = ' || p_party_site_rec.party_site_id,
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
Select country into l_country_code from hz_locations
where location_id = p_party_site_rec.location_id;
ZX_PARTY_TAX_PROFILE_PKG.insert_row (
P_COLLECTING_AUTHORITY_FLAG => null,
P_PROVIDER_TYPE_CODE => null,
P_CREATE_AWT_DISTS_TYPE_CODE => null,
P_CREATE_AWT_INVOICES_TYPE_COD => null,
P_TAX_CLASSIFICATION_CODE => null,
P_SELF_ASSESS_FLAG => null,
P_ALLOW_OFFSET_TAX_FLAG => null,
P_REP_REGISTRATION_NUMBER => null,
P_EFFECTIVE_FROM_USE_LE => null,
P_RECORD_TYPE_CODE => null,
P_REQUEST_ID => null,
P_ATTRIBUTE1 => null,
P_ATTRIBUTE2 => null,
P_ATTRIBUTE3 => null,
P_ATTRIBUTE4 => null,
P_ATTRIBUTE5 => null,
P_ATTRIBUTE6 => null,
P_ATTRIBUTE7 => null,
P_ATTRIBUTE8 => null,
P_ATTRIBUTE9 => null,
P_ATTRIBUTE10 => null,
P_ATTRIBUTE11 => null,
P_ATTRIBUTE12 => null,
P_ATTRIBUTE13 => null,
P_ATTRIBUTE14 => null,
P_ATTRIBUTE15 => null,
P_ATTRIBUTE_CATEGORY => null,
P_PARTY_ID => x_party_site_id,
P_PROGRAM_LOGIN_ID => null,
P_PARTY_TYPE_CODE => 'THIRD_PARTY_SITE',
P_SUPPLIER_FLAG => null,
P_CUSTOMER_FLAG => null,
P_SITE_FLAG => null,
P_PROCESS_FOR_APPLICABILITY_FL => null,
P_ROUNDING_LEVEL_CODE => null,
P_ROUNDING_RULE_CODE => null,
P_WITHHOLDING_START_DATE => null,
P_INCLUSIVE_TAX_FLAG => null,
P_ALLOW_AWT_FLAG => null,
P_USE_LE_AS_SUBSCRIBER_FLAG => null,
P_LEGAL_ESTABLISHMENT_FLAG => null,
P_FIRST_PARTY_LE_FLAG => null,
P_REPORTING_AUTHORITY_FLAG => null,
X_RETURN_STATUS => x_return_status,
P_REGISTRATION_TYPE_CODE => null,--4742586
P_COUNTRY_CODE => l_country_code --4742586
);
| do_update_party_site
|
| DESCRIPTION
| Updates party_site.
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| OUT:
| IN/ OUT:
| p_party_site_rec
| x_return_statue
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
|
| 04-Dec-2001 Rajeshwari P Added the code to make an identifying address
| as inactive Only if any other active sites are
| are present else raise an error.
| 05-APR-2001 Rajeshwari P Bug 2306201.Partially reverting the fix
| made in Bug 1882511.No error is raised if
| the only site present is made inactive.
| 28-OCT-2003 Ramesh Ch Bug#2914238. Updated who columns.
|
| 19-APR-2004 Rajib Ranjan Borah o Bug 3175816. Added GLOBAL_LOCATION_NUMBER
| to HZ_PARTY_SITES.
| 04-JAN-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
| For non-profile entities, the concept of
| select/de-select data-sources is obsoleted.
|
+===========================================================================*/
PROCEDURE do_update_party_site(
p_party_site_rec IN OUT NOCOPY PARTY_SITE_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_object_version_number NUMBER;
hz_utility_v2pub.debug(p_message=>'do_update_party_site (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
SELECT OBJECT_VERSION_NUMBER,
PARTY_ID,
LOCATION_ID,
IDENTIFYING_ADDRESS_FLAG,
STATUS,
ROWID,
ACTUAL_CONTENT_SOURCE
INTO l_object_version_number,
db_party_id,
db_location_id,
db_identifying_address_flag,
db_status,
l_rowid,
db_actual_content_source
FROM HZ_PARTY_SITES
WHERE PARTY_SITE_ID = p_party_site_rec.party_site_id
FOR UPDATE OF PARTY_SITE_ID NOWAIT;
* For non-profile entities, the concept of select/de-select data-sources is obsoleted.
* There is no need to check if the data-source is selected.
g_pst_is_datasource_selected :=
HZ_MIXNM_UTILITY.isDataSourceSelected (
p_selected_datasources => g_pst_selected_datasources,
p_actual_content_source => db_actual_content_source );
g_pst_is_datasource_selected = 'Y' */
THEN
do_unmark_address_flag(db_party_id, p_party_site_rec.party_site_id, 'U');
g_pst_is_datasource_selected = 'Y' */)
THEN
BEGIN
-- Check if any other active, visible, party site is present.
SELECT ROWID, location_id
INTO ldup_rowid, l_identifying_location_id
FROM HZ_PARTY_SITES
WHERE party_site_id = (
SELECT min(party_site_id)
FROM hz_party_sites
WHERE party_id = db_party_id
AND status = 'A'
AND party_site_id <> p_party_site_rec.party_site_id
-- Bug 2197181: added for mix-n-match project: the identifying_flag
-- can be set to 'Y' only if the party site will be visible.
-- SSM SST Integration and Extension
-- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
-- There is no need to check if the data-source is selected.
/* AND HZ_MIXNM_UTILITY.isDataSourceSelected (
g_pst_selected_datasources,
actual_content_source) = 'Y'*/);
UPDATE HZ_PARTY_SITES
SET IDENTIFYING_ADDRESS_FLAG = 'Y',
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = hz_utility_v2pub.request_id,
program_id = hz_utility_v2pub.program_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_update_date = hz_utility_v2pub.program_update_date
WHERE ROWID = ldup_rowid;
do_update_address(
db_party_id,
l_identifying_location_id);
hz_utility_v2pub.debug(p_message=>'HZ_PARTY_SITES_PKG.Update_Row (+) ',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
HZ_PARTY_SITES_PKG.Update_Row (
X_Rowid => l_rowid,
X_PARTY_SITE_ID => p_party_site_rec.party_site_id,
X_PARTY_ID => p_party_site_rec.party_id,
X_LOCATION_ID => p_party_site_rec.location_id,
X_PARTY_SITE_NUMBER => p_party_site_rec.party_site_number,
X_ATTRIBUTE_CATEGORY => p_party_site_rec.attribute_category,
X_ATTRIBUTE1 => p_party_site_rec.attribute1,
X_ATTRIBUTE2 => p_party_site_rec.attribute2,
X_ATTRIBUTE3 => p_party_site_rec.attribute3,
X_ATTRIBUTE4 => p_party_site_rec.attribute4,
X_ATTRIBUTE5 => p_party_site_rec.attribute5,
X_ATTRIBUTE6 => p_party_site_rec.attribute6,
X_ATTRIBUTE7 => p_party_site_rec.attribute7,
X_ATTRIBUTE8 => p_party_site_rec.attribute8,
X_ATTRIBUTE9 => p_party_site_rec.attribute9,
X_ATTRIBUTE10 => p_party_site_rec.attribute10,
X_ATTRIBUTE11 => p_party_site_rec.attribute11,
X_ATTRIBUTE12 => p_party_site_rec.attribute12,
X_ATTRIBUTE13 => p_party_site_rec.attribute13,
X_ATTRIBUTE14 => p_party_site_rec.attribute14,
X_ATTRIBUTE15 => p_party_site_rec.attribute15,
X_ATTRIBUTE16 => p_party_site_rec.attribute16,
X_ATTRIBUTE17 => p_party_site_rec.attribute17,
X_ATTRIBUTE18 => p_party_site_rec.attribute18,
X_ATTRIBUTE19 => p_party_site_rec.attribute19,
X_ATTRIBUTE20 => p_party_site_rec.attribute20,
X_ORIG_SYSTEM_REFERENCE => p_party_site_rec.orig_system_reference,
X_LANGUAGE => p_party_site_rec.language,
X_MAILSTOP => p_party_site_rec.mailstop,
X_IDENTIFYING_ADDRESS_FLAG => p_party_site_rec.identifying_address_flag,
X_STATUS => p_party_site_rec.status,
X_PARTY_SITE_NAME => p_party_site_rec.party_site_name,
X_ADDRESSEE => p_party_site_rec.addressee,
X_OBJECT_VERSION_NUMBER => p_object_version_number,
X_CREATED_BY_MODULE => p_party_site_rec.created_by_module,
X_APPLICATION_ID => p_party_site_rec.application_id,
-- Bug 2197181 : actual_content_source is non-updateable.
X_ACTUAL_CONTENT_SOURCE => NULL,
-- Bug 3175816
X_GLOBAL_LOCATION_NUMBER => p_party_site_rec.global_location_number,
X_DUNS_NUMBER_C => p_party_site_rec.duns_number_c
);
hz_utility_v2pub.debug(p_message=>'HZ_PARTY_SITES_PKG.Update_Row (-) ',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
SELECT 'X' into g_message_name FROM FND_NEW_MESSAGES
WHERE message_name = 'HZ_INACTIVATE_ACCOUNT_SITE_UI'
AND language_code = userenv('LANG')
AND application_id = 222
AND rownum =1;
hz_utility_v2pub.debug(p_message=> 'do_update_party_site (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END do_update_party_site;
SELECT 'Y'
INTO l_dummy
FROM HZ_PARTY_SITE_USES
WHERE PARTY_SITE_USE_ID = l_party_site_use_id;
SELECT PARTY_ID
INTO l_party_id
FROM HZ_PARTY_SITES
WHERE PARTY_SITE_ID = p_party_site_use_rec.party_site_id;
SELECT 'Y'
INTO l_exist
FROM HZ_PARTY_SITE_USES SU
WHERE PARTY_SITE_ID IN (
SELECT PARTY_SITE_ID
FROM HZ_PARTY_SITES PS
WHERE PS.PARTY_ID = l_party_id )
AND SU.SITE_USE_TYPE = p_party_site_use_rec.site_use_type
AND STATUS = 'A' -- Bug 2065191
AND ROWNUM = 1;
hz_utility_v2pub.debug(p_message=>'HZ_PARTY_SITE_USES_PKG.Insert_Row (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
HZ_PARTY_SITE_USES_PKG.Insert_Row (
X_PARTY_SITE_USE_ID => p_party_site_use_rec.party_site_use_id,
X_COMMENTS => p_party_site_use_rec.comments,
X_PARTY_SITE_ID => p_party_site_use_rec.party_site_id,
X_SITE_USE_TYPE => p_party_site_use_rec.site_use_type,
X_PRIMARY_PER_TYPE => p_party_site_use_rec.primary_per_type,
X_STATUS => p_party_site_use_rec.status,
X_OBJECT_VERSION_NUMBER => 1,
X_CREATED_BY_MODULE => p_party_site_use_rec.created_by_module,
X_APPLICATION_ID => p_party_site_use_rec.application_id
);
hz_utility_v2pub.debug(p_message=>'HZ_PARTY_SITE_USES_PKG.Insert_Row (-) ' ||
'x_party_site_use_id = ' || x_party_site_use_id,
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
| do_update_party_site_use
|
| DESCRIPTION
| Updates party_site_use.
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| OUT:
| IN/ OUT:
| p_party_site_use_rec
| x_return_status
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
+===========================================================================*/
PROCEDURE do_update_party_site_use(
p_party_site_use_rec IN OUT NOCOPY PARTY_SITE_USE_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_object_version_number NUMBER;
hz_utility_v2pub.debug(p_message=>'do_update_party_site_use (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
SELECT OBJECT_VERSION_NUMBER,
PARTY_SITE_ID,
SITE_USE_TYPE,
NVL(PRIMARY_PER_TYPE,'N'),
STATUS,
ROWID
INTO l_object_version_number,
l_party_site_id,
l_site_use_type,
l_primary_per_type,
l_status,
l_rowid
FROM HZ_PARTY_SITE_USES
WHERE PARTY_SITE_USE_ID = p_party_site_use_rec.party_site_use_id
FOR UPDATE OF PARTY_SITE_USE_ID NOWAIT;
SELECT PARTY_ID
INTO l_party_id
FROM HZ_PARTY_SITES
WHERE PARTY_SITE_ID = l_party_site_id;
SELECT ROWID
INTO l_dup_rowid
FROM HZ_PARTY_SITE_USES
WHERE PARTY_SITE_USE_ID = (
SELECT min(PARTY_SITE_USE_ID)
FROM HZ_PARTY_SITE_USES
WHERE PARTY_SITE_ID IN (
SELECT PARTY_SITE_ID
FROM HZ_PARTY_SITES
WHERE PARTY_ID = l_party_id )
AND STATUS = 'A'
AND SITE_USE_TYPE = l_site_use_type
AND PARTY_SITE_USE_ID <> p_party_site_use_rec.party_site_use_id);
UPDATE HZ_PARTY_SITE_USES
SET PRIMARY_PER_TYPE = 'Y',
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login= hz_utility_v2pub.last_update_login,
request_id = hz_utility_v2pub.request_id,
program_id = hz_utility_v2pub.program_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_update_date = hz_utility_v2pub.program_update_date
WHERE ROWID = l_dup_rowid;
hz_utility_v2pub.debug(p_message=>'HZ_PARTY_SITE_USES_PKG.Update_Row (+) ',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
HZ_PARTY_SITE_USES_PKG.Update_Row (
X_Rowid => l_rowid,
X_PARTY_SITE_USE_ID => p_party_site_use_rec.party_site_use_id,
X_COMMENTS => p_party_site_use_rec.comments,
X_PARTY_SITE_ID => p_party_site_use_rec.party_site_id,
X_SITE_USE_TYPE => p_party_site_use_rec.site_use_type,
X_PRIMARY_PER_TYPE => p_party_site_use_rec.primary_per_type,
X_STATUS => p_party_site_use_rec.status,
X_OBJECT_VERSION_NUMBER => p_object_version_number,
X_CREATED_BY_MODULE => p_party_site_use_rec.created_by_module,
X_APPLICATION_ID => p_party_site_use_rec.application_id
);
hz_utility_v2pub.debug(p_message=>'HZ_PARTY_SITE_USES_PKG.Update_Row (-) ',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
hz_utility_v2pub.debug(p_message=>'do_update_party_site_use (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END do_update_party_site_use;
| do_update_address
|
| DESCRIPTION
| Denormalize identifying address to hz_parties
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| p_party_id
| p_location_id
| OUT:
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
| 13-APR-2003 P.Suresh o Bug No 2820991. Populated the who columns when
| denormalizing address into hz_parties.
+===========================================================================*/
PROCEDURE do_update_address(
p_party_id IN NUMBER,
p_location_id IN NUMBER
) IS
CURSOR c_loc IS
SELECT * FROM hz_locations
WHERE location_id = p_location_id;
SELECT 'Y'
FROM hz_parties
WHERE party_id = p_party_id
FOR UPDATE NOWAIT;
UPDATE hz_parties
SET country = l_location_rec.country,
address1 = l_location_rec.address1,
address2 = l_location_rec.address2,
address3 = l_location_rec.address3,
address4 = l_location_rec.address4,
city = l_location_rec.city,
postal_code = l_location_rec.postal_code,
state = l_location_rec.state,
province = l_location_rec.province,
county = l_location_rec.county,
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = hz_utility_v2pub.request_id,
program_id = hz_utility_v2pub.program_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_update_date = hz_utility_v2pub.program_update_date
WHERE party_id = p_party_id;
END do_update_address;
| Jianying Huang 28-FEB-01 Modified the update statement to use rowid
| to do updating.
| Rajib R Borah 16-SEP-03 Updated the who columns.
| Ramesh Ch 28-OCT-03 Removed created_by and creation_date
| columns during update.
| avjha 11-JUL-06 Bug 5203798: Populate BOT incase of direct update.
+===========================================================================*/
PROCEDURE do_unmark_address_flag(
p_party_id IN NUMBER,
p_party_site_id IN NUMBER := NULL,
p_mode IN VARCHAR2 := NULL
) IS
CURSOR c_party_sites IS
SELECT rowid, party_site_id
FROM hz_party_sites
WHERE party_id = p_party_id
AND party_site_id <> nvl(p_party_site_id,-999)
AND identifying_address_flag = 'Y'
AND rownum = 1
FOR UPDATE NOWAIT;
UPDATE hz_party_sites
SET identifying_address_flag= 'N',
--Bug number 2914238 .Updated the who columns.
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = hz_utility_v2pub.request_id,
program_id = hz_utility_v2pub.program_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_update_date = hz_utility_v2pub.program_update_date
WHERE rowid = l_rowid;
| 28-OCT-2003 Ramesh Ch Bug#2914238. Updated who columns.
| 11-JUL-06 avjha Bug 5203798: Populate BOT incase of direct update.
|
+===========================================================================*/
PROCEDURE do_unmark_primary_per_type(
p_party_id IN NUMBER,
p_party_site_id IN NUMBER,
p_site_use_type IN VARCHAR2,
p_mode IN VARCHAR2 := NULL
) IS
CURSOR c_party_site_uses IS
SELECT ROWID, PARTY_SITE_USE_ID
FROM HZ_PARTY_SITE_USES SU
WHERE SU.PARTY_SITE_ID IN (
SELECT PS.PARTY_SITE_ID
FROM HZ_PARTY_SITES PS
WHERE PARTY_ID = p_party_id )
AND SU.PARTY_SITE_ID <> p_party_site_id
AND SU.SITE_USE_TYPE = p_site_use_type
AND SU.PRIMARY_PER_TYPE = 'Y'
AND ROWNUM = 1
FOR UPDATE NOWAIT;
UPDATE HZ_PARTY_SITE_USES
SET PRIMARY_PER_TYPE = 'N',
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = hz_utility_v2pub.request_id,
program_id = hz_utility_v2pub.program_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_update_date = hz_utility_v2pub.program_update_date
WHERE ROWID = l_rowid;
| select/de-select data-sources is obsoleted.
|
+===========================================================================*/
PROCEDURE create_party_site (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_party_site_rec IN PARTY_SITE_REC_TYPE,
x_party_site_id OUT NOCOPY NUMBER,
x_party_site_number OUT NOCOPY VARCHAR2,
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) := 'create_party_site';
* For non-profile entities, the concept of select/de-select data-sources is obsoleted.
HZ_MIXNM_UTILITY.LoadDataSources(
p_entity_name => 'HZ_LOCATIONS',
p_entity_attr_id => g_pst_entity_attr_id,
p_mixnmatch_enabled => g_pst_mixnmatch_enabled,
p_selected_datasources => g_pst_selected_datasources );
p_create_update_flag => 'C',
p_party_site_rec => l_party_site_rec,
x_return_status => x_return_status
);
p_operation_code => 'INSERT',
p_db_object_name => 'HZ_PARTY_SITES',
p_instance_pk1_value => x_party_site_id,
p_user_name => fnd_global.user_name,
x_return_status => dss_return_status,
x_msg_count => dss_msg_count,
x_msg_data => dss_msg_data);
FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_INSERT_PRIVILEGE');
g_pst_is_datasource_selected = 'Y'*/
THEN
IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
HZ_BUSINESS_EVENT_V2PVT.create_party_site_event (
l_party_site_rec );
| update_party_site
|
| DESCRIPTION
| Updates party_site.
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| p_init_msg_list
| p_party_site_rec
| OUT:
| x_return_status
| x_msg_count
| x_msg_data
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
|
| 04-JAN-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
| For non-profile entities, the concept of
| select/de-select data-sources is obsoleted.
+===========================================================================*/
PROCEDURE update_party_site (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_party_site_rec IN PARTY_SITE_REC_TYPE,
p_object_version_number IN OUT NOCOPY 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) := 'update_party_site';
SAVEPOINT update_party_site;
hz_utility_v2pub.debug(p_message=>'update_party_site (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
p_operation_code => 'UPDATE',
p_db_object_name => 'HZ_PARTY_SITES',
p_instance_pk1_value => l_party_site_rec.party_site_id,
p_user_name => fnd_global.user_name,
x_return_status => dss_return_status,
x_msg_count => dss_msg_count,
x_msg_data => dss_msg_data);
FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_UPDATE_PRIVILEGE');
* For non-profile entities, the concept of select/de-select data-sources is obsoleted.
HZ_MIXNM_UTILITY.LoadDataSources(
p_entity_name => 'HZ_LOCATIONS',
p_entity_attr_id => g_pst_entity_attr_id,
p_mixnmatch_enabled => g_pst_mixnmatch_enabled,
p_selected_datasources => g_pst_selected_datasources );
p_create_update_flag => 'U',
p_party_site_rec => l_party_site_rec,
p_old_party_site_rec => l_old_party_site_rec,
x_return_status => x_return_status
);
do_update_party_site(
l_party_site_rec,
p_object_version_number,
x_return_status
);
g_pst_is_datasource_selected = 'Y'*/
THEN
l_old_party_site_rec.orig_system := l_party_site_rec.orig_system;
HZ_BUSINESS_EVENT_V2PVT.update_party_site_event (
l_party_site_rec,
l_old_party_site_rec );
SELECT 'X' into g_message_name FROM FND_NEW_MESSAGES
WHERE message_name = 'HZ_INACTIVATE_ACCOUNT_SITE_UI'
AND language_code = userenv('LANG')
AND application_id = 222
AND rownum =1;
update_acct_sites_status(l_party_site_rec.party_site_id,
l_party_site_rec.status,
x_return_status);
hz_utility_v2pub.debug(p_message=>'update_party_site (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_party_site;
hz_utility_v2pub.debug(p_message=>'update_party_site (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_party_site;
hz_utility_v2pub.debug(p_message=>'update_party_site (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_party_site;
hz_utility_v2pub.debug(p_message=>'update_party_site (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END update_party_site;
| update_party_site_use
|
| DESCRIPTION
| Updates party_site_use.
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| p_init_msg_list
| p_party_site_use_rec
| OUT:
| x_return_status
| x_msg_count
| x_msg_data
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
|
+===========================================================================*/
PROCEDURE update_party_site_use (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_party_site_use_rec IN PARTY_SITE_USE_REC_TYPE,
p_object_version_number IN OUT NOCOPY 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) := 'update_party_site_use';
SAVEPOINT update_party_site_use;
hz_utility_v2pub.debug(p_message=>'update_party_site_use (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
do_update_party_site_use(
l_party_site_use_rec,
p_object_version_number,
x_return_status
);
HZ_BUSINESS_EVENT_V2PVT.update_party_site_use_event (
l_party_site_use_rec , l_old_party_site_use_rec );
hz_utility_v2pub.debug(p_message=> 'update_party_site_use (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_party_site_use;
hz_utility_v2pub.debug(p_message=>'update_party_site_use (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_party_site_use;
hz_utility_v2pub.debug(p_message=>'update_party_site_use (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_party_site_use;
hz_utility_v2pub.debug(p_message=>'update_party_site_use (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END update_party_site_use;
HZ_PARTY_SITES_PKG.Select_Row (
X_PARTY_SITE_ID => x_party_site_rec.party_site_id,
X_PARTY_ID => x_party_site_rec.party_id,
X_LOCATION_ID => x_party_site_rec.location_id,
X_PARTY_SITE_NUMBER => x_party_site_rec.party_site_number,
X_ATTRIBUTE_CATEGORY => x_party_site_rec.attribute_category,
X_ATTRIBUTE1 => x_party_site_rec.attribute1,
X_ATTRIBUTE2 => x_party_site_rec.attribute2,
X_ATTRIBUTE3 => x_party_site_rec.attribute3,
X_ATTRIBUTE4 => x_party_site_rec.attribute4,
X_ATTRIBUTE5 => x_party_site_rec.attribute5,
X_ATTRIBUTE6 => x_party_site_rec.attribute6,
X_ATTRIBUTE7 => x_party_site_rec.attribute7,
X_ATTRIBUTE8 => x_party_site_rec.attribute8,
X_ATTRIBUTE9 => x_party_site_rec.attribute9,
X_ATTRIBUTE10 => x_party_site_rec.attribute10,
X_ATTRIBUTE11 => x_party_site_rec.attribute11,
X_ATTRIBUTE12 => x_party_site_rec.attribute12,
X_ATTRIBUTE13 => x_party_site_rec.attribute13,
X_ATTRIBUTE14 => x_party_site_rec.attribute14,
X_ATTRIBUTE15 => x_party_site_rec.attribute15,
X_ATTRIBUTE16 => x_party_site_rec.attribute16,
X_ATTRIBUTE17 => x_party_site_rec.attribute17,
X_ATTRIBUTE18 => x_party_site_rec.attribute18,
X_ATTRIBUTE19 => x_party_site_rec.attribute19,
X_ATTRIBUTE20 => x_party_site_rec.attribute20,
X_ORIG_SYSTEM_REFERENCE => x_party_site_rec.orig_system_reference,
X_LANGUAGE => x_party_site_rec.language,
X_MAILSTOP => x_party_site_rec.mailstop,
X_IDENTIFYING_ADDRESS_FLAG => x_party_site_rec.identifying_address_flag,
X_STATUS => x_party_site_rec.status,
X_PARTY_SITE_NAME => x_party_site_rec.party_site_name,
X_ADDRESSEE => x_party_site_rec.addressee,
X_CREATED_BY_MODULE => x_party_site_rec.created_by_module,
X_APPLICATION_ID => x_party_site_rec.application_id,
X_ACTUAL_CONTENT_SOURCE => l_actual_content_source,
X_GLOBAL_LOCATION_NUMBER => x_party_site_rec.global_location_number /* Bug 3175816 */,
X_DUNS_NUMBER_C => x_party_site_rec.duns_number_c
);
HZ_PARTY_SITE_USES_PKG.Select_Row (
X_PARTY_SITE_USE_ID => x_party_site_use_rec.party_site_use_id,
X_COMMENTS => x_party_site_use_rec.comments,
X_PARTY_SITE_ID => x_party_site_use_rec.party_site_id,
X_SITE_USE_TYPE => x_party_site_use_rec.site_use_type,
X_PRIMARY_PER_TYPE => x_party_site_use_rec.primary_per_type,
X_STATUS => x_party_site_use_rec.status,
X_CREATED_BY_MODULE => x_party_site_use_rec.created_by_module,
X_APPLICATION_ID => x_party_site_use_rec.application_id
);
| update_acct_sites_status
|
| DESCRIPTION
| Updates acct site status.
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| OUT:
| IN/ OUT:
| p_party_site_use_rec
| x_return_status
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
+===========================================================================*/
PROCEDURE update_acct_sites_status(
p_party_site_id IN NUMBER,
p_new_status IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_object_version_number NUMBER;
hz_utility_v2pub.debug(p_message=> 'update_acct_sites_status (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
UPDATE HZ_CUST_ACCT_SITES_ALL
SET STATUS = p_new_status,
bill_to_flag = NULL,
ship_to_flag = NULL,
market_flag = NULL,
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = hz_utility_v2pub.request_id,
program_id = hz_utility_v2pub.program_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_update_date = hz_utility_v2pub.program_update_date
WHERE PARTY_SITE_ID = p_party_site_id;
hz_utility_v2pub.debug(p_message=>'update_acct_sites_status (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END update_acct_sites_status;
| Updates party site uses status.
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| OUT:
| IN/ OUT:
| x_return_status
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
+===========================================================================*/
PROCEDURE inactivate_party_site_uses(
p_party_site_id IN NUMBER,
p_new_status IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_object_version_number NUMBER;
select party_site_use_id , object_version_number
from hz_party_site_uses
where status = 'A'
and party_site_id = p_party_site_id;
HZ_PARTY_SITE_V2PUB.update_party_site_use
(p_init_msg_list => FND_API.G_TRUE,
p_party_site_use_rec => l_party_site_use_rec,
p_object_version_number => l_object_version_number,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
/* UPDATE HZ_PARTY_SITE_USES
SET STATUS = p_new_status,
PRIMARY_PER_TYPE = null,
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = hz_utility_v2pub.request_id,
program_id = hz_utility_v2pub.program_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_update_date = hz_utility_v2pub.program_update_date
WHERE PARTY_SITE_ID = p_party_site_id;*/
| update_denorm_prim_flag
|
| DESCRIPTION
| Updates primary flag in the acct site uses and denormalize in the
| related cust acct site.
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN: p_site_use_id , p_site_use_code
| OUT:
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
===========================================================================+*/
PROCEDURE update_denorm_prim_flag (
p_site_use_id IN NUMBER,
p_site_use_code IN VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := ''; --'denormalize_site_use_flag'
UPDATE HZ_CUST_SITE_USES_ALL
SET PRIMARY_FLAG = 'Y',
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = hz_utility_v2pub.request_id,
program_id = hz_utility_v2pub.program_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_update_date = hz_utility_v2pub.program_update_date
WHERE SITE_USE_ID = p_site_use_id
RETURNING CUST_ACCT_SITE_ID INTO l_cust_acct_site_id;
UPDATE HZ_CUST_ACCT_SITES_ALL
SET BILL_TO_FLAG = 'P',
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = hz_utility_v2pub.request_id,
program_id = hz_utility_v2pub.program_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_update_date = hz_utility_v2pub.program_update_date
WHERE CUST_ACCT_SITE_ID = l_cust_acct_site_id;
UPDATE HZ_CUST_ACCT_SITES_ALL
SET SHIP_TO_FLAG = 'P',
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = hz_utility_v2pub.request_id,
program_id = hz_utility_v2pub.program_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_update_date = hz_utility_v2pub.program_update_date
WHERE CUST_ACCT_SITE_ID = l_cust_acct_site_id;
UPDATE HZ_CUST_ACCT_SITES_ALL
SET MARKET_FLAG = 'P',
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = hz_utility_v2pub.request_id,
program_id = hz_utility_v2pub.program_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_update_date = hz_utility_v2pub.program_update_date
WHERE CUST_ACCT_SITE_ID = l_cust_acct_site_id;
END update_denorm_prim_flag;
| Updates account site uses status.
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| OUT:
| IN/ OUT:
| p_party_site_use_rec
| x_return_status
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
+===========================================================================*/
PROCEDURE inactivate_account_site_uses(
p_party_site_id IN NUMBER,
p_new_status IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_object_version_number NUMBER;
SELECT MIN(b.site_use_id) , site_use_code
FROM hz_cust_acct_sites_all a , hz_cust_site_uses_all b
WHERE a.cust_account_id in (select cust_account_id
from hz_cust_acct_sites_all cas
where cas.party_site_id = p_party_site_id)
AND a.cust_acct_site_id = b.cust_acct_site_id
AND b.status = 'A'
AND a.status = 'A'
GROUP BY a.cust_Account_id,b.org_id,b.site_use_code
HAVING MAX(nvl(primary_flag,'N')) = 'N';
UPDATE HZ_CUST_SITE_USES_ALL
SET STATUS = p_new_status,
primary_flag = 'N', --Bug 3370874
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = hz_utility_v2pub.request_id,
program_id = hz_utility_v2pub.program_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_update_date = hz_utility_v2pub.program_update_date
WHERE CUST_ACCT_SITE_ID IN (
SELECT CUST_ACCT_SITE_ID
FROM HZ_CUST_ACCT_SITES_ALL
WHERE PARTY_SITE_ID = p_party_site_id);
update_denorm_prim_flag (l_site_use_id , l_site_use_code);
update_acct_sites_status(p_party_site_id,p_new_status,x_return_status);
p_create_update_flag IN VARCHAR2,
p_party_site_rec IN party_site_rec_type,
p_old_party_site_rec IN party_site_rec_type DEFAULT NULL,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
BEGIN
-- check language
IF (p_create_update_flag = 'C' AND
p_party_site_rec.language IS NOT NULL AND
p_party_site_rec.language <> FND_API.G_MISS_CHAR) OR
(p_create_update_flag = 'U' AND
p_party_site_rec.language IS NOT NULL AND
p_party_site_rec.language <> p_old_party_site_rec.language)
THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_OBSOLETE_COLUMN');