[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO hz_party_interface_errors (
interface_error_id,
party_interface_id,
message_text,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (
hz_party_interface_errors_s.nextval,
G_PARTY_INTERFACE_ID,
l_message_text,
hz_utility_v2pub.created_by,
hz_utility_v2pub.creation_date,
hz_utility_v2pub.last_updated_by,
hz_utility_v2pub.last_update_date,
hz_utility_v2pub.last_update_login);
| o Inserts a record in hz_party_interface_errors for each field
| rectified.
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| o set_hz_dnb_invalid_data
|
| ARGUMENTS : IN:
| OUT:
| IN/ OUT: p_interface_rec
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
|
| 18-AUG-2004 Rajib Ranjan Borah o Bug 3107162. Created.
| 13-SEP-2004 Rajib Ranjan Borah o Bug 3848365. Dynamic lookups will be
| created for invalid sic code values
| instead of nullifying them.
| 13-OCT-2004 Sravanthi A o Bug 3107162: Commented out NOCOPY code for
| validation of det_history_ind
| Commented out NOCOPY code that sets local_activity_code
| of interface record to 'NACE'.
+===========================================================================*/
PROCEDURE rectify_error_fields(
p_interface_rec IN OUT NOCOPY HZ_PARTY_INTERFACE%ROWTYPE
) IS
CURSOR c_currency_field (p_currency_code IN VARCHAR2) IS
SELECT '1'
FROM FND_CURRENCIES
WHERE currency_code = p_currency_code AND
currency_flag = 'Y' AND
enabled_flag IN ('Y','N') AND
(
start_date_active IS NULL OR
start_date_active <= SYSDATE
) AND
(
end_date_active IS NULL OR
end_date_active >= SYSDATE
);
SELECT '1'
FROM AR_LOOKUPS
WHERE lookup_type = p_lookup_type AND
lookup_code = p_lookup_code AND
enabled_flag = 'Y' AND
(
start_date_active IS NULL OR
start_date_active <= SYSDATE
) AND
(
end_date_active IS NULL OR
END_DATE_ACTIVE >= SYSDATE
);
SELECT '1'
INTO l_temp_cur_var
FROM HZ_PHONE_COUNTRY_CODES
WHERE phone_country_code = p_interface_rec.phone_country_code AND
ROWNUM = 1;
* select/de-select data sources for other entities is obsoleted.
FND_ACCESS_CONTROL_UTIL.ADD_POLICY(l_ar_schema, 'HZ_RELATIONSHIPS', 'content_source_type_sec', l_apps_schema, 'hz_common_pub.content_source_type_security');
| Insert the a corresponding record into requst log.
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| p_interface_rec
| p_request_log_id
| OUT:
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
|
+===========================================================================*/
procedure do_create_request_log(
p_interface_rec IN HZ_PARTY_INTERFACE%ROWTYPE,
l_request_log_id OUT NOCOPY NUMBER
) IS PRAGMA AUTONOMOUS_TRANSACTION;
INSERT INTO hz_dnb_request_log(
REQUEST_ID,
PARTY_ID,
REQUESTED_PRODUCT,
DUNS_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE )
VALUES(
HZ_DNB_REQUEST_LOG_S.nextval,
p_interface_rec.PARTY_ID,
p_interface_rec.GDP_NAME,
p_interface_rec.DUNS_NUMBER,
hz_utility_v2pub.CREATED_BY,
hz_utility_v2pub.CREATION_DATE,
hz_utility_v2pub.LAST_UPDATED_BY,
hz_utility_v2pub.LAST_UPDATE_DATE )
RETURNING REQUEST_ID INTO l_request_log_id;
UPDATE hz_party_interface
SET REQUEST_LOG_ID = l_request_log_id
WHERE party_interface_id = p_interface_rec.party_interface_id;
| do_update_request_log
|
| DESCRIPTION
| update the a corresponding record in requst log.
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| p_interface_rec
| p_request_log_id
| OUT:
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
|
+===========================================================================*/
procedure do_update_request_log(
p_request_log_id IN NUMBER,
p_party_id IN NUMBER,
p_status IN VARCHAR2
) IS PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE hz_dnb_request_log
SET PARTY_ID = p_party_id,
STATUS = p_status,
LAST_UPDATED_BY = hz_utility_v2pub.LAST_UPDATED_BY,
LAST_UPDATE_DATE = hz_utility_v2pub.LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN = hz_utility_v2pub.LAST_UPDATE_LOGIN
WHERE REQUEST_ID = p_request_log_id;
END do_update_request_log;
| o If it is an existing party, update corresponding 'DNB'
| info. for this party.
|
| When DUNS <> ENQUIRY DUNS, we need to use the current row in
| party interface table to:
| o Create/update HQ party. We create 'USER_ENTERED' and 'DNB'
| organization profiles and party sites (through DNB location)
| and other DNB stuff if HQ party do not exist. Otherwise, we
| update HQ DNB info.
| o Create/update BRANCH party. We create 'USER_ENTERED' and
| 'DNB' organization profiles.
| o Create/update party relationship between HQ and BRANCH.
|
| MODIFICATION HISTORY
|
| 19-AUG-2004 Rajib Ranjan Borah o Bug 3107162. Added call to
| rectify_error_fields
| o Status in HZ_PARTY_INTERFACE will
| be set to W1 and not P1 if any column
| value passed by DNB is invalid.
| o IF G_ERROR_FLAG = 'Y', push message
| HZ_DNB_INVALID_NULL to stack.
| 14-OCT-2004 Sravanthi A o Bug 3107162: Added local variable to store
| p_inactivate_flag and make it 'Y' if
| passed value is NULL.
+===========================================================================*/
procedure map(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
p_commit IN VARCHAR2:= FND_API.G_FALSE,
p_group_id IN NUMBER := NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_inactivate_flag IN VARCHAR2, --4227564 := 'Y',
p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL
) IS
l_api_name CONSTANT VARCHAR2(30) := 'map';
SELECT * FROM
HZ_PARTY_INTERFACE
WHERE NVL(group_id, FND_API.G_MISS_NUM) = NVL(p_group_id, FND_API.G_MISS_NUM)
AND status = 'N';
update hz_party_interface set party_id = l_final_party_id
where party_interface_id = l_interface_rec.party_interface_id;
SELECT 'Y' INTO l_exist
FROM hz_organization_profiles
-- Bug 4956756 : use DUNS_NUMBER_C
WHERE DUNS_NUMBER_C = lpad(to_char(l_interface_rec.DUNS_NUMBER), 9, '0')
AND actual_content_source = l_interface_rec.content_source_type
AND (SYSDATE BETWEEN effective_start_date
AND NVL(effective_end_date, to_date('12/31/4712','MM/DD/YYYY')))
AND ROWNUM=1;
SELECT displayed_duns_party_id
INTO l_displayed_duns_party_id
FROM hz_organization_profiles
WHERE party_id = l_original_party_id
AND actual_content_source = l_interface_rec.content_source_type
AND (SYSDATE BETWEEN effective_start_date
AND NVL(effective_end_date, to_date('12/31/4712','MM/DD/YYYY')));
SELECT duns_number_c INTO l_duns_number
FROM hz_organization_profiles
WHERE party_id = l_displayed_duns_party_id
AND actual_content_source = l_interface_rec.content_source_type
AND (SYSDATE BETWEEN effective_start_date
AND NVL(effective_end_date, to_date('12/31/4712','MM/DD/YYYY')));
SELECT party_id
INTO l_displayed_duns_party_id
FROM hz_organization_profiles
-- Bug 4956756 : use DUNS_NUMBER_C
WHERE DUNS_NUMBER_C = lpad(to_char(l_interface_rec.DUNS_NUMBER), 9, '0')
AND actual_content_source = l_interface_rec.content_source_type
AND ( SYSDATE BETWEEN effective_start_date
AND NVL(effective_end_date, to_date('12/31/4712','MM/DD/YYYY')))
AND last_update_date = (
SELECT max(last_update_date)
FROM hz_organization_profiles
-- Bug 4956756 : use DUNS_NUMBER_C
WHERE DUNS_NUMBER_C = lpad(to_char(l_interface_rec.DUNS_NUMBER), 9, '0')
AND actual_content_source = l_interface_rec.content_source_type
AND (SYSDATE BETWEEN effective_start_date
AND NVL(effective_end_date, to_date('12/31/4712','MM/DD/YYYY'))))
AND ROWNUM = 1;
--Update HQ's DNB info.
l_interface_rec.party_id := l_displayed_duns_party_id;
arp_util.debug('HZ_MAP_PARTY_PUB.MAP: update DNB for HQs');
arp_util.debug('HZ_MAP_PARTY_PUB.MAP: DUNS <> ENQUIRY_DUNS: create/ update party/org profile for the original party');
UPDATE hz_party_interface
SET status = 'P1',
party_id = l_final_party_id -- Bug 5440525
WHERE party_interface_id = l_interface_rec.party_interface_id;
UPDATE hz_party_interface
SET status = 'W1',
party_id = l_final_party_id -- Bug 5440525
WHERE party_interface_id = l_interface_rec.party_interface_id;
do_update_request_log(
l_interface_rec.request_log_id,
l_interface_rec.party_id,
'S1' );
do_update_request_log(
l_interface_rec.request_log_id,
l_original_party_id,
'E1' );
do_update_request_log(
l_interface_rec.request_log_id,
l_original_party_id,
'E1' );
do_update_request_log(
l_interface_rec.request_log_id,
l_original_party_id,
'E1' );
do_update_request_log(
l_interface_rec.request_log_id,
l_original_party_id,
'E1' );
SELECT hosr.orig_system_ref_id,
hosr.object_version_number,
hosr.start_date_active,
hosr.orig_system,
hosr.orig_system_reference,
hosr.owner_table_name
FROM -- hz_organization_profiles org_pro,
hz_orig_sys_references hosr
WHERE -- nvl(org_pro.party_id, '-999') = hosr.owner_table_id AND
hosr.orig_system = 'DNB'
AND hosr.owner_table_name = 'HZ_PARTIES'
AND trunc(nvl(hosr.end_date_active, sysdate)) >= trunc(sysdate)
AND hosr.status='A'
AND hosr.orig_system_reference = p_duns_number_c
AND hosr.party_id <> nvl(p_party_id,-1);
SELECT hosr.orig_system_ref_id,
hosr.object_version_number,
hosr.start_date_active,
hosr.orig_system,
hosr.orig_system_reference,
hosr.owner_table_name
FROM --hz_party_sites ps,
hz_orig_sys_references hosr
WHERE --nvl(ps.party_site_id, '-999') = hosr.owner_table_id
--AND ps.status = 'A' AND
hosr.orig_system = 'DNB'
AND hosr.owner_table_name = 'HZ_PARTY_SITES'
AND trunc(nvl(hosr.end_date_active, sysdate)) >= trunc(sysdate)
AND hosr.status='A'
AND hosr.orig_system_reference like p_duns_number_c || '%'
AND hosr.party_id <> nvl(p_party_id,-1);
SELECT hosr.orig_system_ref_id,
hosr.object_version_number,
hosr.start_date_active,
hosr.orig_system,
hosr.orig_system_reference,
hosr.owner_table_name
FROM -- hz_contact_points cp,
hz_orig_sys_references hosr
WHERE --nvl(cp.contact_point_id, '-999') = hosr.owner_table_id
-- AND cp.status = 'A' AND
hosr.orig_system = 'DNB'
AND hosr.owner_table_name = 'HZ_CONTACT_POINTS'
AND trunc(nvl(hosr.end_date_active, sysdate)) >= trunc(sysdate)
AND hosr.status='A'
AND hosr.orig_system_reference like p_duns_number_c || '%'
AND hosr.party_id <> nvl(p_party_id,-1);
HZ_ORIG_SYSTEM_REF_PUB.update_orig_system_reference(
p_init_msg_list => 'T',
p_orig_sys_reference_rec => l_orig_sys_reference_rec,
p_object_version_number => l_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
HZ_ORIG_SYSTEM_REF_PUB.update_orig_system_reference(
p_init_msg_list => 'T',
p_orig_sys_reference_rec => l_orig_sys_reference_rec,
p_object_version_number => l_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
HZ_ORIG_SYSTEM_REF_PUB.update_orig_system_reference(
p_init_msg_list => 'T',
p_orig_sys_reference_rec => l_orig_sys_reference_rec,
p_object_version_number => l_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT hosr.orig_system_ref_id,
hosr.object_version_number,
hosr.start_date_active,
hosr.orig_system,
hosr.orig_system_reference,
hosr.owner_table_name,
hosr.owner_table_id
FROM
--hz_organization_profiles org_pro,
hz_orig_sys_references hosr
WHERE
--nvl(org_pro.party_id, '-999') = hosr.owner_table_id AND
hosr.orig_system = 'DNB'
AND hosr.owner_table_name = 'HZ_PARTIES'
-- AND trunc(nvl(hosr.end_date_active, sysdate)) >= trunc(sysdate)
AND hosr.status='A'
AND hosr.orig_system_reference <> p_duns_number_c
AND hosr.owner_table_id = p_organization_rec.party_rec.party_id;
SELECT hosr.orig_system_ref_id,
hosr.object_version_number,
hosr.start_date_active,
hosr.orig_system,
hosr.orig_system_reference,
hosr.owner_table_name,
hosr.owner_table_id
FROM
--hz_party_sites ps,
hz_orig_sys_references hosr
WHERE
--nvl(ps.party_site_id, '-999') = hosr.owner_table_id AND
hosr.orig_system = 'DNB'
AND hosr.owner_table_name = 'HZ_PARTY_SITES'
-- AND trunc(nvl(hosr.end_date_active, sysdate)) >= trunc(sysdate)
AND hosr.status='A'
AND hosr.party_id = p_organization_rec.party_rec.party_id
AND hosr.orig_system_reference not like p_duns_number_c || '%';
SELECT hosr.orig_system_ref_id,
hosr.object_version_number,
hosr.start_date_active,
hosr.orig_system,
hosr.orig_system_reference,
hosr.owner_table_name,
hosr.owner_table_id
FROM
--hz_contact_points cp,
hz_orig_sys_references hosr
WHERE
--nvl(cp.contact_point_id, '-999') = hosr.owner_table_id AND
hosr.orig_system = 'DNB'
AND hosr.owner_table_name = 'HZ_CONTACT_POINTS'
-- AND trunc(nvl(hosr.end_date_active, sysdate)) >= trunc(sysdate)
AND hosr.status='A'
AND hosr.party_id = p_organization_rec.party_rec.party_id
AND hosr.orig_system_reference not like p_duns_number_c || '%';
SELECT hosr.orig_system_ref_id INTO l_orig_sys_ref_id
FROM hz_orig_sys_references hosr
WHERE hosr.orig_system = 'DNB'
AND hosr.owner_table_name = 'HZ_PARTIES'
AND hosr.status='A'
AND hosr.owner_table_id = l_organization_rec.party_rec.party_id;
l_credit_ratings_rec.SPCL_EVENT_UPDATE_DATE := p_interface_rec.SPCL_EVENT_UPDATE_DATE;
select 'Y'
from hz_orig_sys_references
where orig_system = p_orig_system
and orig_system_reference = p_orig_system_ref
and owner_table_name = p_owner_table_name
and status = 'A'
and trunc(nvl(end_date_active, sysdate)) >= trunc(sysdate);
SELECT 'Y' INTO l_ue_exist
FROM hz_organization_profiles
WHERE party_id = p_organization_rec.party_rec.party_id
AND actual_content_source = HZ_PARTY_V2PUB.G_MISS_CONTENT_SOURCE_TYPE
AND effective_end_date is null
AND ROWNUM = 1;
SELECT 'Y' INTO l_exist
FROM hz_organization_profiles
WHERE party_id = p_organization_rec.party_rec.party_id
AND actual_content_source = p_organization_rec.ACTUAL_CONTENT_SOURCE
AND effective_end_date is null
AND ROWNUM = 1;
arp_util.debug('HZ_MAP_PARTY_PUB.STORE_ORG: party does not exist: update DNB and USER_ENTERED displayed_duns_party_id');
UPDATE hz_organization_profiles
SET displayed_duns_party_id = p_organization_rec.party_rec.party_id
WHERE party_id = p_organization_rec.party_rec.party_id AND
effective_end_date is null;
arp_util.debug('HZ_MAP_PARTY_PUB.STORE_ORG: party does not exist: update DNB displayed_duns_party_id');
UPDATE hz_organization_profiles
SET displayed_duns_party_id = p_organization_rec.party_rec.party_id
WHERE party_id = p_organization_rec.party_rec.party_id AND
actual_content_source = p_organization_rec.ACTUAL_CONTENT_SOURCE AND
effective_end_date is null;
--party exists. org profile exists. Update it.
--
arp_util.debug('HZ_MAP_PARTY_PUB.STORE_ORG: party exists, org profile exists ');
SELECT object_version_number INTO l_object_version_number
FROM hz_parties
WHERE party_id= p_organization_rec.party_rec.party_id;
HZ_PARTY_V2PUB.update_organization(
'F',
p_organization_rec,
l_object_version_number,
x_organization_profile_id,
x_return_status,
l_msg_count,
l_msg_data);
| are not passed in updated data.
| 2. If the existing primary class code is different
| than sic_code1, make the existing one as non primary
| and sic_code1 as primary.
|
+===========================================================================*/
procedure populate_to_classification(
p_code_assignment_rec IN OUT NOCOPY hz_classification_v2pub.code_assignment_rec_type,
p_interface_rec IN OUT NOCOPY HZ_PARTY_INTERFACE%ROWTYPE,
-- p_is_new_party IN BOOLEAN ,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
-- Bug 3397674 : Added local variables and cursor to pick existing code assignments for this party and sic_code_type.
l_msg_count NUMBER;
select code_assignment_id, object_version_number
from hz_code_assignments
where
owner_table_name = p_code_assignment_rec.owner_table_name AND
owner_table_id = p_code_assignment_rec.owner_table_id AND
class_category = p_code_assignment_rec.class_category AND
--bug 4169070
--content_source_type = p_code_assignment_rec.content_source_type AND
actual_content_source = p_code_assignment_rec.actual_content_source AND
class_code not in (
/*nvl(SUBSTRB(replace(p_interface_rec.sic_code1, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
nvl(SUBSTRB(replace(p_interface_rec.sic_code2, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
nvl(SUBSTRB(replace(p_interface_rec.sic_code3, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
nvl(SUBSTRB(replace(p_interface_rec.sic_code4, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
nvl(SUBSTRB(replace(p_interface_rec.sic_code5, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
nvl(SUBSTRB(replace(p_interface_rec.sic_code6, ' ', ''), 1, 4),FND_API.G_MISS_CHAR)
*/
NVL(p_interface_rec.sic_code1,FND_API.G_MISS_CHAR),
NVL(p_interface_rec.sic_code2,FND_API.G_MISS_CHAR),
NVL(p_interface_rec.sic_code3,FND_API.G_MISS_CHAR),
NVL(p_interface_rec.sic_code4,FND_API.G_MISS_CHAR),
NVL(p_interface_rec.sic_code5,FND_API.G_MISS_CHAR),
NVL(p_interface_rec.sic_code6,FND_API.G_MISS_CHAR)
)
AND
-- (END_DATE_ACTIVE IS NULL OR (END_DATE_ACTIVE IS not NULL and trunc(END_DATE_ACTIVE) >= trunc(SYSDATE)));
HZ_CLASSIFICATION_V2PUB.update_code_assignment (
p_code_assignment_rec => p_code_assignment_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
);
select code_assignment_id, object_version_number
into l_code_assignment_id, l_object_version_number
from hz_code_assignments
where
owner_table_name = p_code_assignment_rec.owner_table_name AND
owner_table_id = p_code_assignment_rec.owner_table_id AND
class_category = p_code_assignment_rec.class_category AND
--bug 4169070
--content_source_type = p_code_assignment_rec.content_source_type AND
actual_content_source = p_code_assignment_rec.actual_content_source AND
class_code <> nvl(p_interface_rec.sic_code1,FND_API.G_MISS_CHAR) AND
primary_flag = 'Y' AND
(END_DATE_ACTIVE IS NULL OR (END_DATE_ACTIVE IS not NULL and
END_DATE_ACTIVE >= SYSDATE));
HZ_CLASSIFICATION_V2PUB.update_code_assignment (
p_code_assignment_rec => p_code_assignment_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
);
| create/update assignment code
|
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
|
| OUT:
| IN/ OUT:
| p_code_assignment_rec
| x_return_status
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY Kate Shan Created
|
+===========================================================================*/
procedure store_classification(
p_code_assignment_rec IN OUT NOCOPY hz_classification_v2pub.code_assignment_rec_type,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_msg_count NUMBER;
/* update code assignment or create code assignment */
arp_util.debug('HZ_MAP_PARTY_PUB.STORE_CLASSIFICATION (+) ');
select code_assignment_id, object_version_number , primary_flag
into l_code_assignment_id, l_object_version_number, l_primary_flag
from hz_code_assignments
where
owner_table_name = l_code_assignment_rec.owner_table_name AND
owner_table_id = l_code_assignment_rec.owner_table_id AND
class_category = l_code_assignment_rec.class_category AND
class_code = l_code_assignment_rec.class_code AND
--bug 4169070
--content_source_type = l_code_assignment_rec.content_source_type AND
actual_content_source = l_code_assignment_rec.actual_content_source AND
(END_DATE_ACTIVE IS NULL OR
(END_DATE_ACTIVE IS not NULL and
END_DATE_ACTIVE >= SYSDATE))
--AND trunc(END_DATE_ACTIVE) >= trunc(SYSDATE)))
AND rownum = 1; --bug 4287144: removed trunc
HZ_CLASSIFICATION_V2PUB.update_code_assignment (
p_code_assignment_rec => l_code_assignment_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 location or create/update party_site or
| create location and party site
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| p_party_id
| OUT:
| IN/ OUT:
| p_location_rec
| x_return_status
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
|
+===========================================================================*/
procedure store_location(
p_location_rec IN OUT NOCOPY hz_location_v2pub.location_rec_type,
p_party_id IN NUMBER,
p_create_new IN BOOLEAN,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_msg_count NUMBER;
/* update location or create party_site or (create location and party site). */
arp_util.debug('HZ_MAP_PARTY_PUB.STORE_LOCATION (+) ');
SELECT ps.location_id, ps.party_site_id, loc.object_version_number,loc.address1
INTO p_location_rec.location_id, l_party_site_id, l_object_version_number, l_address1
FROM hz_party_sites ps, hz_locations loc
WHERE ps.party_id = p_party_id
AND ps.location_id = loc.location_id
AND loc.actual_content_source = p_location_rec.actual_content_source
AND ps.end_date_active is NULL
-- Bug 3473497 : Added condition status='A' in where clause
AND ps.status = 'A'
AND rownum = 1;
-- if the existing location record has same taxable components, update this record
/* SELECT ps.location_id, loc.object_version_number
INTO p_location_rec.location_id, l_object_version_number
FROM hz_party_sites ps, hz_locations loc
WHERE ps.party_id = p_party_id
AND ps.location_id = loc.location_id
AND loc.actual_content_source = p_location_rec.actual_content_source
AND UPPER(loc.ADDRESS1 || loc.ADDRESS2 || loc.ADDRESS3 || loc.ADDRESS4 ||
loc.CITY || loc.STATE || loc.COUNTY || loc.POSTAL_CODE || loc.PROVINCE) =
UPPER( p_location_rec.ADDRESS1 || p_location_rec.ADDRESS2 || p_location_rec.ADDRESS3 || p_location_rec.ADDRESS4 ||
p_location_rec.CITY || p_location_rec.STATE || p_location_rec.COUNTY ||
p_location_rec.POSTAL_CODE || p_location_rec.PROVINCE )
AND rownum =1;
arp_util.debug('HZ_MAP_PARTY_PUB.STORE_LOCATION: update Location and party site');
hz_location_v2pub.update_location(
'F',
p_location_rec,
l_object_version_number,
x_return_status,
l_msg_count,
l_msg_data);
SELECT hosr.orig_system_ref_id INTO l_orig_sys_ref_id
FROM hz_orig_sys_references hosr
WHERE hosr.orig_system = 'DNB'
AND hosr.owner_table_name = 'HZ_PARTY_SITES'
AND hosr.status='A'
AND hosr.owner_table_id = l_party_site_id;
select count(1)
into l_count
from hz_orig_sys_references
where owner_table_name = 'HZ_PARTY_SITES'
and orig_system = 'DNB'
and status = 'A'
and trunc(nvl(end_date_active, sysdate)) >= trunc(sysdate)
and orig_system_reference like l_duns_number_c || '%';
END store_location; /* update location or create party_site or (create location and party site). */
SELECT location_id INTO p_location_rec.location_id
FROM hz_locations
WHERE UPPER(ADDRESS1) = UPPER(p_location_rec.ADDRESS1) AND
UPPER(NVL(ADDRESS2, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS2, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS2)) AND
UPPER(NVL(ADDRESS3, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS3, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS3)) AND
UPPER(NVL(ADDRESS4, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS4, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS4)) AND
UPPER(NVL(CITY, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.CITY, NULL, FND_API.G_MISS_CHAR, p_location_rec.CITY)) AND
UPPER(NVL(STATE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.STATE, NULL, FND_API.G_MISS_CHAR, p_location_rec.STATE)) AND
UPPER(NVL(COUNTY, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.COUNTY, NULL, FND_API.G_MISS_CHAR, p_location_rec.COUNTY)) AND
UPPER(NVL(POSTAL_CODE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.POSTAL_CODE, NULL, FND_API.G_MISS_CHAR, p_location_rec.POSTAL_CODE)) AND
UPPER(COUNTRY) = UPPER(p_location_rec.COUNTRY) AND
UPPER(NVL(PROVINCE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.PROVINCE, NULL, FND_API.G_MISS_CHAR, p_location_rec.PROVINCE)) AND
actual_content_source = p_location_rec.actual_content_source AND
rownum =1;
SELECT 'Y' INTO l_party_site_exist
FROM HZ_PARTY_SITES
WHERE location_id = p_location_rec.location_id
AND party_id = p_party_id
AND actual_content_source = p_location_rec.actual_content_source
AND status = 'A'
AND (SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, to_date('12/31/4712','MM/DD/YYYY')))
AND ROWNUM = 1;
UPDATE hz_party_sites SET END_DATE_ACTIVE = sysdate , status = 'I' WHERE party_site_id = p_old_party_site_id;
select 'Y'
from hz_orig_sys_references
where orig_system = p_orig_system
and orig_system_reference = p_orig_system_ref
and owner_table_name = p_owner_table_name
and status = 'A'
and trunc(nvl(end_date_active, sysdate)) >= trunc(sysdate);
select count(1)
into l_count
from hz_orig_sys_references
where owner_table_name = 'HZ_PARTY_SITES'
and orig_system = 'DNB'
and status = 'A'
and trunc(nvl(end_date_active, sysdate)) >= trunc(sysdate)
and orig_system_reference like l_duns_number_c || '%';
select 'Y'
from hz_orig_sys_references
where orig_system = p_orig_system
and orig_system_reference = p_orig_system_ref
and owner_table_name = p_owner_table_name
and status = 'A'
and trunc(nvl(end_date_active, sysdate)) >= trunc(sysdate);
select count(1)
into l_count
from hz_orig_sys_references
where owner_table_name = 'HZ_CONTACT_POINTS'
and orig_system = 'DNB'
and status = 'A'
and trunc(nvl(end_date_active, sysdate)) >= trunc(sysdate)
and orig_system_reference like l_duns_number_c || '%';
SELECT contact_point_id, object_version_number, orig_system_reference
INTO p_contact_points_rec.contact_point_id, l_object_version_number, l_orig_system_reference
FROM
( SELECT contact_point_id, object_version_number, orig_system_reference,rank() over (
partition by owner_table_id order by last_update_date desc,object_version_number desc,contact_point_id desc) r
FROM hz_contact_points hcp
WHERE owner_table_name = 'HZ_PARTIES'
AND owner_table_id = p_contact_points_rec.owner_table_id
AND contact_point_type = 'PHONE'
AND phone_line_type = p_phone_rec.PHONE_LINE_TYPE
AND actual_content_source = p_contact_points_rec.actual_content_source
AND status = 'A'
)
WHERE r=1;
arp_util.debug('HZ_MAP_PARTY_PUB.STORE_CONTACT_POINT: update contact point');
hz_contact_point_v2pub.update_contact_point(
'F',
p_contact_points_rec,
l_edi_rec,
l_email_rec,
p_phone_rec,
l_telex_rec,
l_web_rec,
l_object_version_number,
x_return_status,
l_msg_count,
l_msg_data);
SELECT hosr.orig_system_ref_id INTO l_orig_sys_ref_id
FROM hz_orig_sys_references hosr
WHERE hosr.orig_system = 'DNB'
AND hosr.owner_table_name = 'HZ_CONTACT_POINTS'
AND hosr.status='A'
AND hosr.owner_table_id = p_contact_points_rec.contact_point_id;
select count(1)
into l_count
from hz_orig_sys_references
where owner_table_name = 'HZ_CONTACT_POINTS'
and orig_system = 'DNB'
and status = 'A'
and trunc(nvl(end_date_active, sysdate)) >= trunc(sysdate)
and orig_system_reference like l_duns_number_c || '%';*/
l_last_update_date1 DATE;
SELECT MAX(rated_as_of_date)
INTO l_max_rated_as_of_date
FROM hz_credit_ratings
WHERE party_id = p_credit_ratings_rec.party_id
AND actual_content_source = p_credit_ratings_rec.actual_content_source;
SELECT rated_as_of_date, credit_rating_id, last_update_date, object_version_number
INTO l_rated_as_of_date, l_credit_rating_id, l_last_update_date1, l_ovn
FROM hz_credit_ratings
WHERE party_id = p_credit_ratings_rec.party_id
AND actual_content_source = p_credit_ratings_rec.actual_content_source
AND NVL(rated_as_of_date, TO_DATE('31-12-4712', 'DD-MM-YYYY'))=
NVL(l_max_rated_as_of_date, TO_DATE('31-12-4712', 'DD-MM-YYYY'))
AND rownum = 1;
HZ_PARTY_INFO_V2PUB.update_credit_rating(
'F',
p_credit_ratings_rec,
l_ovn,
x_return_status,
l_msg_count,
l_msg_data);
hz_party_info_pub.update_credit_ratings(
1,
'F',
'F',
p_credit_ratings_rec,
l_last_update_date1,
x_return_status,
l_msg_count,
l_msg_data);
l_last_update_date1 DATE;
SELECT financial_report_id, last_update_date, object_version_number
INTO p_fin_rep_rec.financial_report_id, l_last_update_date1, l_ovn
FROM hz_financial_reports
WHERE party_id = p_fin_rep_rec.party_id
AND ACTUAL_CONTENT_SOURCE = p_fin_rep_rec.ACTUAL_CONTENT_SOURCE
-- Bug 3202840 : User trunc while checking for DATE_REPORT_ISSED so that if it is purchased on
-- same day, the report gets updated.
-- Bug 3223038 : Modify NVL conditions for columns DATE_REPORT_ISSUED, REPORT_START_DATE
-- REPORT_END_DATE and ISSUED_PERIOD.
AND (NVL(trunc(DATE_REPORT_ISSUED), to_date('12/30/4712','MM/DD/YYYY'))
= NVL(trunc(p_fin_rep_rec.DATE_REPORT_ISSUED), to_date('12/31/4712','MM/DD/YYYY'))
OR (NVL(REPORT_START_DATE, to_date('12/30/4712','MM/DD/YYYY'))
= NVL(p_fin_rep_rec.REPORT_START_DATE, to_date('12/31/4712','MM/DD/YYYY'))
AND NVL(REPORT_END_DATE, to_date('12/30/4712','MM/DD/YYYY'))
= NVL(p_fin_rep_rec.REPORT_END_DATE, to_date('12/31/4712','MM/DD/YYYY')))
OR NVL(ISSUED_PERIOD, 'Y') = NVL(p_fin_rep_rec.ISSUED_PERIOD, 'X'))
AND NVL(TYPE_OF_FINANCIAL_REPORT, 'X') = NVL(p_fin_rep_rec.TYPE_OF_FINANCIAL_REPORT, 'X')
AND rownum=1;
arp_util.debug('HZ_MAP_PARTY_PUB.STORE_FINANCIAL_REPORT: update financial report for DNB');
HZ_ORGANIZATION_INFO_V2PUB.update_financial_report(
'F',
p_fin_rep_rec,
l_ovn,
x_return_status,
l_msg_count,
l_msg_data);
hz_org_info_pub.update_financial_reports(
1,
'F',
'F',
p_fin_rep_rec,
l_last_update_date1,
x_return_status,
l_msg_count,
l_msg_data);
SELECT *
FROM hz_financial_numbers
WHERE financial_report_id = p_fin_num_rec.financial_report_id;
l_last_update_date1 DATE;
l_last_update_date1 := p_fin_num_tab(i).last_update_date;
HZ_ORGANIZATION_INFO_V2PUB.update_financial_number(
'F',
p_fin_num_rec,
l_ovn,
x_return_status,
l_msg_count,
l_msg_data);
hz_org_info_pub.update_financial_numbers(
1,
'F',
'F',
p_fin_num_rec,
l_last_update_date1,
x_return_status,
l_msg_count,
l_msg_data);
SELECT * FROM
HZ_PARTY_INTERFACE
WHERE NVL(group_id, FND_API.G_MISS_NUM) = NVL(p_group_id, FND_API.G_MISS_NUM)
AND status IN ('P1','W1'); -- Bug 3107162
UPDATE hz_party_interface
SET status = 'W2'
WHERE party_interface_id = l_interface_rec.party_interface_id;
UPDATE hz_party_interface
SET status = 'P2'
WHERE party_interface_id = l_interface_rec.party_interface_id;
arp_util.debug('HZ_MAP_PARTY_PUB.STORE_RELATED_DUNS: update party interface status to P2 for party_interface_id = ' || l_interface_rec.party_interface_id );
do_update_request_log(
l_interface_rec.request_log_id,
l_interface_rec.party_id,
'S'
);
arp_util.debug('HZ_MAP_PARTY_PUB.STORE_RELATED_DUNS: update request log status to S');
do_update_request_log(
l_interface_rec.request_log_id,
l_interface_rec.party_id,
'E2'
);
do_update_request_log(
l_interface_rec.request_log_id,
l_interface_rec.party_id,
'E2'
);
do_update_request_log(
l_interface_rec.request_log_id,
l_interface_rec.party_id,
'E2'
);
do_update_request_log(
l_interface_rec.request_log_id,
l_interface_rec.party_id,
'E2'
);
SELECT org.party_id
FROM hz_organization_profiles org, hz_orig_sys_references ssm
WHERE org.duns_number_c = lpad(to_char(p_related_duns_rec.DUNS_NUMBER),9,'0')
AND org.actual_content_source = p_related_duns_rec.content_source_type
AND org.effective_end_date is NULL
AND org.party_id = ssm.owner_table_id
AND ssm.owner_table_name = 'HZ_PARTIES'
AND ssm.orig_system = 'DNB'
AND ssm.status = 'A'
AND rownum = 1;
l_last_update_date1 DATE;
SELECT COUNT(*) INTO l_count
FROM hz_organization_profiles
WHERE duns_number_c = lpad(to_char(p_related_duns_rec.DUNS_NUMBER),9,'0')
AND actual_content_source = p_related_duns_rec.content_source_type
AND (SYSDATE BETWEEN effective_start_date AND NVL(effective_end_date, to_date('12/31/4712','MM/DD/YYYY')));
UPDATE hz_organization_profiles
SET displayed_duns_party_id = l_organization_rec.party_rec.party_id
WHERE organization_profile_id = l_organization_profile_id;
SELECT rel.relationship_id, rel.subject_id
FROM hz_relationships rel
WHERE rel.object_id = p_party_rel_rec.object_id
AND rel.relationship_code = p_party_rel_rec.relationship_code
AND rel.relationship_type = p_party_rel_rec.relationship_type
AND rel.actual_content_source = p_party_rel_rec.actual_content_source
AND rel.start_date <= sysdate
AND NVL(rel.end_date, to_date('12/31/4712','MM/DD/YYYY')) > sysdate
AND rel.subject_table_name = 'HZ_PARTIES'
AND rel.object_table_name = 'HZ_PARTIES';
SELECT rel.relationship_id
FROM hz_relationships rel
WHERE rel.object_id = p_party_rel_rec.object_id
AND rel.relationship_code = l_rel_code
AND rel.relationship_type = l_rel_type
AND rel.actual_content_source = p_party_rel_rec.actual_content_source
AND rel.start_date <= sysdate
AND NVL(rel.end_date, to_date('12/31/4712','MM/DD/YYYY')) > sysdate
AND rel.subject_table_name = 'HZ_PARTIES'
AND rel.object_table_name = 'HZ_PARTIES';
SELECT object_version_number INTO l_obj_ver_no
FROM hz_relationships
WHERE relationship_id = l_rel_id
AND subject_table_name = 'HZ_PARTIES'
AND object_table_name = 'HZ_PARTIES'
AND DIRECTIONAL_FLAG = 'F';
HZ_RELATIONSHIP_V2PUB.update_relationship(
'F',
l_party_rel_rec,
l_obj_ver_no,
l_object_version_number2,
x_return_status,
l_msg_count,
l_msg_data);
SELECT duns_number_c INTO l_old_duns_number
FROM hz_organization_profiles o
WHERE o.party_id = l_old_subject_id
AND o.effective_end_date IS NULL
AND o.actual_content_source = p_party_rel_rec.actual_content_source;
SELECT duns_number_c INTO l_new_duns_number
FROM hz_organization_profiles o
WHERE o.party_id = p_party_rel_rec.subject_id
AND o.effective_end_date IS NULL
AND o.actual_content_source = p_party_rel_rec.actual_content_source;
SELECT object_version_number INTO l_object_version_number1
FROM hz_relationships
WHERE relationship_id = l_old_rel_ids(i)
AND subject_table_name = 'HZ_PARTIES'
AND object_table_name = 'HZ_PARTIES'
AND DIRECTIONAL_FLAG = 'F';
--do not need to pass in LUD of parties as party is not updated.
l_party_rel_rec.actual_content_source := p_party_rel_rec.actual_content_source;
HZ_RELATIONSHIP_V2PUB.update_relationship(
'F',
l_party_rel_rec,
l_object_version_number1,
l_object_version_number2,
x_return_status,
l_msg_count,
l_msg_data);
UPDATE hz_party_interface
SET status = p_status
WHERE party_interface_id = p_party_interface_id;
INSERT INTO hz_party_interface_errors (
interface_error_id,
party_interface_id,
message_text,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (
hz_party_interface_errors_s.nextval ,
p_party_interface_id,
l_message_text,
hz_utility_v2pub.created_by,
hz_utility_v2pub.creation_date,
hz_utility_v2pub.last_updated_by,
hz_utility_v2pub.last_update_date,
hz_utility_v2pub.last_update_login);
COMMIT; /* commit update to hz_party_interface and inserts into hz_party_interface_errors */
UPDATE hz_organization_profiles
SET business_report = p_business_report
WHERE organization_profile_id = p_organization_profile_id;
SELECT 'Y'
FROM fnd_lookup_values_vl
-- WHERE upper(lookup_type) = upper(p_lookup_type)
-- AND upper(lookup_code) = upper(p_lookup_code)
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
AND view_application_id = 222
AND (enabled_flag = 'Y' AND
trunc(sysdate) BETWEEN
trunc(NVL(start_date_active, sysdate)) AND
trunc(NVL(end_date_active, sysdate))
);
Fnd_Lookup_Values_Pkg.Insert_Row(
X_ROWID => row_id,
X_LOOKUP_TYPE => p_lookup_type,
X_SECURITY_GROUP_ID => 0,
X_VIEW_APPLICATION_ID => /*HZ_UTILITY_V2PUB.application_id*/222,-- Bug 3107162
X_LOOKUP_CODE => p_lookup_code,
X_TAG => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ENABLED_FLAG => 'Y',
X_START_DATE_ACTIVE => sysdate,
X_END_DATE_ACTIVE => null,
X_TERRITORY_CODE => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_MEANING => p_lookup_code,
X_DESCRIPTION => p_lookup_code,
X_CREATION_DATE => HZ_UTILITY_V2PUB.CREATION_DATE,
X_CREATED_BY => HZ_UTILITY_V2PUB.CREATED_BY,
X_LAST_UPDATE_DATE => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN);