Customer API
CREATE OR REPLACE PACKAGE oracle_cdh IS
PROCEDURE create_party(p_party_id VARCHAR2,
p_party_type VARCHAR2,
p_party_name VARCHAR2,
p_party_number VARCHAR2,
p_person_pre_name_adjunct VARCHAR2,
p_person_first_name VARCHAR2,
p_person_middle_name VARCHAR2,
p_person_last_name VARCHAR2,
p_person_title VARCHAR2,
p_created_by_module VARCHAR2,
p_person_name_suffix VARCHAR2,
p_person_academic_title VARCHAR2,
p_person_previous_last_name VARCHAR2,
p_known_as VARCHAR2,
p_known_as2 VARCHAR2,
p_tax_reference VARCHAR2,
p_person_iden_type VARCHAR2,
p_person_identifier VARCHAR2,
p_status VARCHAR2,
p_category_code VARCHAR2,
p_orig_system_reference VARCHAR2,
p_orig_system VARCHAR2
);
PROCEDURE update_party(p_party_id VARCHAR2,
p_party_type VARCHAR2,
p_party_name VARCHAR2,
p_party_number VARCHAR2,
p_person_pre_name_adjunct VARCHAR2,
p_person_first_name VARCHAR2,
p_person_middle_name VARCHAR2,
p_person_last_name VARCHAR2,
p_person_title VARCHAR2,
p_created_by_module VARCHAR2,
p_person_name_suffix VARCHAR2,
p_person_academic_title VARCHAR2,
p_person_previous_last_name VARCHAR2,
p_known_as VARCHAR2,
p_known_as2 VARCHAR2,
p_tax_reference VARCHAR2,
p_person_iden_type VARCHAR2,
p_person_identifier VARCHAR2,
p_status VARCHAR2,
p_category_code VARCHAR2,
p_orig_system_reference VARCHAR2,
p_orig_system VARCHAR2
);
PROCEDURE create_cust_account(p_party_number VARCHAR2,
p_account_number VARCHAR2,
p_account_name VARCHAR2,
p_status VARCHAR2,
p_customer_type VARCHAR2,
p_customer_class_code VARCHAR2,
p_created_by_module VARCHAR2,
p_orig_system VARCHAR2
);
PROCEDURE update_cust_account(p_party_number VARCHAR2,
p_account_number VARCHAR2,
p_account_name VARCHAR2,
p_status VARCHAR2,
p_customer_type VARCHAR2,
p_customer_class_code VARCHAR2,
p_created_by_module VARCHAR2,
p_orig_system VARCHAR2
);
PROCEDURE create_location(p_location_id VARCHAR2,
p_address1 VARCHAR2,
p_address2 VARCHAR2,
p_address3 VARCHAR2,
p_address4 VARCHAR2,
p_city VARCHAR2,
p_postal_code VARCHAR2,
p_state VARCHAR2,
p_province VARCHAR2,
p_county VARCHAR2,
p_street_suffix VARCHAR2,
p_street VARCHAR2,
p_street_number VARCHAR2,
p_floor VARCHAR2,
p_country VARCHAR2,
p_created_by_module VARCHAR2,
p_orig_system VARCHAR2
);
PROCEDURE update_location(p_location_id VARCHAR2,
p_address1 VARCHAR2,
p_address2 VARCHAR2,
p_address3 VARCHAR2,
p_address4 VARCHAR2,
p_city VARCHAR2,
p_postal_code VARCHAR2,
p_state VARCHAR2,
p_province VARCHAR2,
p_county VARCHAR2,
p_street_suffix VARCHAR2,
p_street VARCHAR2,
p_street_number VARCHAR2,
p_floor VARCHAR2,
p_country VARCHAR2,
p_created_by_module VARCHAR2,
p_orig_system VARCHAR2
);
PROCEDURE create_party_site(p_party_number VARCHAR2,
p_location_id VARCHAR2,
p_status VARCHAR2,
p_party_site_name VARCHAR2,
p_attribute_category VARCHAR2,
p_addressee VARCHAR2,
p_created_by_module VARCHAR2,
p_orig_system VARCHAR2,
p_party_site_number VARCHAR2,
p_party_site_id VARCHAR2
);
PROCEDURE update_party_site(p_party_number VARCHAR2,
p_location_id VARCHAR2,
p_status VARCHAR2,
p_party_site_name VARCHAR2,
p_attribute_category VARCHAR2,
p_addressee VARCHAR2,
p_created_by_module VARCHAR2,
p_orig_system VARCHAR2,
p_party_site_number VARCHAR2,
p_party_site_id VARCHAR2
);
procedure create_cust_acct_site(p_party_site_number VARCHAR2,
p_party_site_id VARCHAR2,
p_account_number VARCHAR2,
p_cust_acct_site_id VARCHAR2,
p_status VARCHAR2,
p_bill_to_flag VARCHAR2,
p_market_flag VARCHAR2,
p_ship_to_flag VARCHAR2,
p_customer_category_code VARCHAR2,
p_territory VARCHAR2,
p_translated_customer_name VARCHAR2,
p_created_by_module VARCHAR2,
p_orig_system VARCHAR2
);
procedure update_cust_acct_site(p_party_site_number VARCHAR2,
p_party_site_id VARCHAR2,
p_account_number VARCHAR2,
p_cust_acct_site_id VARCHAR2,
p_status VARCHAR2,
p_bill_to_flag VARCHAR2,
p_market_flag VARCHAR2,
p_ship_to_flag VARCHAR2,
p_customer_category_code VARCHAR2,
p_territory VARCHAR2,
p_translated_customer_name VARCHAR2,
p_created_by_module VARCHAR2,
p_orig_system VARCHAR2
);
PROCEDURE create_merge_batch(p_batch_id VARCHAR2,
p_rule_set_name VARCHAR2,
p_batch_name VARCHAR2,
p_request_id VARCHAR2,
p_batch_status VARCHAR2,
p_batch_commit VARCHAR2,
p_batch_delete VARCHAR2,
p_merge_reason_code VARCHAR2,
p_created_by_module VARCHAR2 );
PROCEDURE create_merge_parties(p_batch_party_id VARCHAR2,
p_batch_id VARCHAR2,
p_merge_type VARCHAR2,
p_from_party_num VARCHAR2,
p_to_party_num VARCHAR2,
p_merge_reason_code VARCHAR2,
p_merge_status VARCHAR2
);
PROCEDURE create_merge_party_details(p_batch_party_id VARCHAR2,
p_entity_name VARCHAR2,
p_merge_from_entity_num VARCHAR2,
p_merge_to_entity_num VARCHAR2,
p_mandatory_merge VARCHAR2,
p_object_version_number VARCHAR2 );
PROCEDURE create_merge_parties_sugg(p_batch_id VARCHAR2,
p_batch_party_id VARCHAR2,
p_merge_type VARCHAR2,
p_from_party_num VARCHAR2,
p_to_party_num VARCHAR2,
p_merge_reason_code VARCHAR2,
p_merge_status VARCHAR2
);
PROCEDURE create_merge_entity_att(p_merge_batch_id VARCHAR2,
p_merge_to_party_num VARCHAR2,
p_attribute_name VARCHAR2,
p_attribute_value VARCHAR2,
p_attribute_type VARCHAR2,
p_attribute_party_num VARCHAR2,
p_entity_name VARCHAR2,
p_object_version_number VARCHAR2
);
PROCEDURE create_dup_batch(p_dup_batch_name VARCHAR2,
p_match_rule_id VARCHAR2,
p_application_id VARCHAR2,
p_request_type VARCHAR2,
p_parties_total VARCHAR2,
p_automerge_flag VARCHAR2,
p_dup_batch_id VARCHAR2
);
PROCEDURE create_dup_sets(p_dup_batch_id VARCHAR2,
p_winner_party_num VARCHAR2,
p_status VARCHAR2,
p_merge_type VARCHAR2,
p_object_version_number VARCHAR2,
p_dup_set_id VARCHAR2
);
PROCEDURE create_dup_set_parties(p_dup_party_num VARCHAR2,
p_dup_set_id VARCHAR2,
p_merge_seq_id VARCHAR2,
p_merge_batch_id VARCHAR2,
p_score VARCHAR2,
p_merge_flag VARCHAR2,
p_not_dup VARCHAR2,
p_merge_batch_name VARCHAR2
) ;
PROCEDURE submit_customer_merge(p_customer_number VARCHAR2,
p_customer_name VARCHAR2,
p_duplicate_number VARCHAR2,
p_duplicate_name VARCHAR2,
p_delete_duplicate_flag VARCHAR2,
p_process_flag VARCHAR2,
p_customer_first_name VARCHAR2,
p_customer_last_name VARCHAR2,
p_customer_type VARCHAR2,
p_duplicate_first_name VARCHAR2,
p_duplicate_last_name VARCHAR2,
p_duplicate_type VARCHAR2,
p_merge_reason_code VARCHAR2,
p_merge_fail_msg VARCHAR2
);
END oracle_cdh;
/
CREATE OR REPLACE PACKAGE BODY oracle_cdh IS
--Jun Peng, 10-Mar-2005,iTech ShenZhen
PROCEDURE create_party(p_party_id VARCHAR2,
p_party_type VARCHAR2,
p_party_name VARCHAR2,
p_party_number VARCHAR2,
p_person_pre_name_adjunct VARCHAR2,
p_person_first_name VARCHAR2,
p_person_middle_name VARCHAR2,
p_person_last_name VARCHAR2,
p_person_title VARCHAR2,
p_created_by_module VARCHAR2,
p_person_name_suffix VARCHAR2,
p_person_academic_title VARCHAR2,
p_person_previous_last_name VARCHAR2,
p_known_as VARCHAR2,
p_known_as2 VARCHAR2,
p_tax_reference VARCHAR2,
p_person_iden_type VARCHAR2,
p_person_identifier VARCHAR2,
p_status VARCHAR2,
p_category_code VARCHAR2,
p_orig_system_reference VARCHAR2,
p_orig_system VARCHAR2
)
AS
l_person_rec hz_party_v2pub.person_rec_type;
l_organization_rec hz_party_v2pub.organization_rec_type;
x_party_id NUMBER;
x_party_number VARCHAR2(100);
x_profile_id NUMBER;
x_return_status VARCHAR2(1000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(1000);
l_error_message VARCHAR2(1000);
BEGIN
IF p_party_type = 'PERSON' THEN
l_person_rec.person_pre_name_adjunct := p_person_pre_name_adjunct;
l_person_rec.person_first_name := p_person_first_name;
l_person_rec.person_middle_name := p_person_middle_name;
l_person_rec.person_last_name := p_person_last_name;
l_person_rec.person_name_suffix := p_person_name_suffix;
l_person_rec.person_title := p_person_title;
l_person_rec.person_academic_title := p_person_academic_title;
l_person_rec.person_previous_last_name := p_person_previous_last_name;
l_person_rec.known_as := p_known_as;
l_person_rec.known_as2 := p_known_as2;
l_person_rec.tax_reference := p_tax_reference;
l_person_rec.person_iden_type := p_person_iden_type;
l_person_rec.person_identifier := p_person_identifier;
l_person_rec.created_by_module := p_created_by_module;
l_person_rec.party_rec.status := p_status;
l_person_rec.party_rec.category_code := p_category_code;
l_person_rec.party_rec.orig_system_reference := p_party_number;
l_person_rec.party_rec.orig_system := p_orig_system;
hz_party_v2pub.create_person(
p_init_msg_list => 'T',
p_person_rec =>l_person_rec,
x_party_id =>x_party_id,
x_party_number =>x_party_number,
x_profile_id =>x_profile_id,
x_return_status =>x_return_status,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data );
ELSIF p_party_type = 'ORGANIZATION' THEN
l_organization_rec.organization_name := p_party_name;
l_organization_rec.created_by_module := p_created_by_module;
l_organization_rec.party_rec.orig_system_reference := p_party_number;
l_organization_rec.party_rec.orig_system := p_orig_system;
hz_party_v2pub.create_organization(
p_init_msg_list => 'T',
p_organization_rec => l_organization_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_party_id => x_party_id,
x_party_number => x_party_number,
x_profile_id => x_profile_id );
END IF;
oracle_error('create_party', 'Party Type: ' || p_party_type|| ' Party Name: ' || p_party_name || ' ' || x_msg_data);
IF x_msg_count>1 THEN
FOR I IN 1..x_msg_count
LOOP
l_error_message := I ||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255);
oracle_error('create_party', 'Party Type: ' || p_party_type|| ' Party Name: ' || p_party_name || ' ' || l_error_message);
END LOOP;
END IF;
END create_party;
PROCEDURE update_party(p_party_id VARCHAR2,
p_party_type VARCHAR2,
p_party_name VARCHAR2,
p_party_number VARCHAR2,
p_person_pre_name_adjunct VARCHAR2,
p_person_first_name VARCHAR2,
p_person_middle_name VARCHAR2,
p_person_last_name VARCHAR2,
p_person_title VARCHAR2,
p_created_by_module VARCHAR2,
p_person_name_suffix VARCHAR2,
p_person_academic_title VARCHAR2,
p_person_previous_last_name VARCHAR2,
p_known_as VARCHAR2,
p_known_as2 VARCHAR2,
p_tax_reference VARCHAR2,
p_person_iden_type VARCHAR2,
p_person_identifier VARCHAR2,
p_status VARCHAR2,
p_category_code VARCHAR2,
p_orig_system_reference VARCHAR2,
p_orig_system VARCHAR2
)
AS
l_person_rec hz_party_v2pub.person_rec_type;
l_organization_rec hz_party_v2pub.organization_rec_type;
l_version_number NUMBER;
l_party_id NUMBER;
x_party_number VARCHAR2(100);
x_party_id NUMBER;
x_profile_id NUMBER;
x_return_status VARCHAR2(1000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(1000);
l_error_message VARCHAR2(1000);
BEGIN
BEGIN
SELECT party_id, object_version_number
INTO l_party_id, l_version_number
FROM hz_parties
WHERE orig_system_reference=p_party_number;
EXCEPTION
WHEN OTHERS THEN
oracle_error('update party, get party id error', 'Orig Party Number'|| p_party_number);
RAISE;
END;
IF p_party_type = 'PERSON' THEN
l_person_rec.person_name_suffix := p_person_name_suffix;
l_person_rec.person_title := p_person_title;
l_person_rec.person_academic_title := p_person_academic_title;
l_person_rec.person_previous_last_name := p_person_previous_last_name;
l_person_rec.person_first_name := p_person_first_name;
l_person_rec.person_middle_name := p_person_middle_name;
l_person_rec.person_last_name := p_person_last_name;
l_person_rec.person_name_suffix := p_person_name_suffix;
l_person_rec.person_previous_last_name := p_person_previous_last_name;
l_person_rec.known_as := p_known_as;
l_person_rec.known_as2 := p_known_as2;
l_person_rec.tax_reference := p_tax_reference;
l_person_rec.person_iden_type := p_person_iden_type;
l_person_rec.person_identifier := p_person_identifier;
l_person_rec.created_by_module := p_created_by_module;
l_person_rec.party_rec.status := p_status;
l_person_rec.party_rec.category_code := p_category_code;
l_person_rec.party_rec.party_id := l_party_id;
hz_party_v2pub.update_person(
p_init_msg_list => FND_API.G_TRUE,
p_person_rec => l_person_rec,
p_party_object_version_number => l_version_number,
x_profile_id => x_profile_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
ELSIF p_party_type = 'ORGANIZATION' THEN
l_organization_rec.organization_name := p_party_name;
l_organization_rec.created_by_module := p_created_by_module;
l_organization_rec.party_rec.status := p_status;
l_organization_rec.party_rec.category_code := p_category_code;
l_organization_rec.party_rec.party_id := l_party_id;
hz_party_v2pub.update_organization(
p_init_msg_list => FND_API.G_TRUE,
p_organization_rec => l_organization_rec,
p_party_object_version_number => l_version_number,
x_profile_id => x_profile_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
END IF;
oracle_error('update_party', 'Party Type: ' || p_party_type|| ' Party Name: ' || p_party_name || ' ' || x_msg_data);
IF x_msg_count>1 THEN
FOR I IN 1..x_msg_count
LOOP
l_error_message := I ||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255);
oracle_error('update_party', 'Party Type: ' || p_party_type|| ' Party Name: ' || p_party_name || ' ' || l_error_message);
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END update_party;
PROCEDURE create_cust_account(p_party_number VARCHAR2,
p_account_number VARCHAR2,
p_account_name VARCHAR2,
p_status VARCHAR2,
p_customer_type VARCHAR2,
p_customer_class_code VARCHAR2,
p_created_by_module VARCHAR2,
p_orig_system VARCHAR2
)
AS
l_party_type VARCHAR2(50);
l_party_id NUMBER;
p_cust_account_rec hz_cust_account_v2pub.cust_account_rec_type;
p_person_rec hz_party_v2pub.person_rec_type;
p_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
p_organization_rec hz_party_v2pub.organization_rec_type;
x_cust_account_id NUMBER;
x_account_number NUMBER;
x_party_id NUMBER;
x_party_number NUMBER;
x_profile_id NUMBER;
x_return_status VARCHAR2(1000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(1000);
l_error_message VARCHAR2(1000);
BEGIN
dbms_application_info.set_client_info('81');
p_cust_account_rec.account_name := p_account_name;
p_cust_account_rec.status := p_status;
p_cust_account_rec.customer_type := p_customer_type;
p_cust_account_rec.customer_class_code := p_customer_class_code;
p_cust_account_rec.created_by_module := p_created_by_module;
p_cust_account_rec.orig_system := p_orig_system;
p_cust_account_rec.orig_system_reference := p_account_number;
BEGIN
SELECT party_id, party_type
INTO l_party_id, l_party_type
FROM hz_parties
WHERE orig_system_reference=p_party_number;
EXCEPTION
WHEN OTHERS THEN
oracle_error('create_cust_account, get party id error', 'Orig Pary Number: ' || p_party_number);
END;
IF l_party_type='PERSON' THEN
p_person_rec.party_rec.party_id := l_party_id;
hz_cust_account_v2pub.create_cust_account(
p_init_msg_list => FND_API.G_TRUE,
p_cust_account_rec => p_cust_account_rec,
p_person_rec => p_person_rec,
p_customer_profile_rec => p_customer_profile_rec,
p_create_profile_amt => FND_API.G_TRUE,
x_cust_account_id => x_cust_account_id,
x_account_number => x_account_number,
x_party_id => x_party_id,
x_party_number => x_party_number,
x_profile_id => x_profile_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ELSIF l_party_type='ORGANIZATION' THEN
p_organization_rec.party_rec.party_id := l_party_id;
hz_cust_account_v2pub.create_cust_account(
p_init_msg_list => FND_API.G_TRUE,
p_cust_account_rec => p_cust_account_rec,
p_organization_rec => p_organization_rec,
p_customer_profile_rec => p_customer_profile_rec,
p_create_profile_amt => FND_API.G_TRUE,
x_cust_account_id => x_cust_account_id,
x_account_number => x_account_number,
x_party_id => x_party_id,
x_party_number => x_party_number,
x_profile_id => x_profile_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
END IF;
oracle_error('create_cust_account', p_party_number|| ' ' || p_account_number || ' ' || x_msg_data);
IF x_msg_count>1 THEN
FOR I IN 1..x_msg_count
LOOP
l_error_message := I ||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255);
oracle_error('create_cust_account', p_party_number|| ' ' || p_account_number || ' ' || l_error_message);
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END create_cust_account;
PROCEDURE update_cust_account(p_party_number VARCHAR2,
p_account_number VARCHAR2,
p_account_name VARCHAR2,
p_status VARCHAR2,
p_customer_type VARCHAR2,
p_customer_class_code VARCHAR2,
p_created_by_module VARCHAR2,
p_orig_system VARCHAR2
)
AS
l_party_type VARCHAR2(50);
l_cust_account_id NUMBER;
l_object_version_number NUMBER;
p_cust_account_rec hz_cust_account_v2pub.cust_account_rec_type;
p_person_rec hz_party_v2pub.person_rec_type;
p_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
p_organization_rec hz_party_v2pub.organization_rec_type;
x_cust_account_id NUMBER;
x_account_number NUMBER;
x_party_id NUMBER;
x_party_number NUMBER;
x_profile_id NUMBER;
x_return_status VARCHAR2(1000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(1000);
l_error_message VARCHAR2(1000);
BEGIN
dbms_application_info.set_client_info('81');
BEGIN
SELECT cust_account_id, object_version_number
INTO l_cust_account_id, l_object_version_number
FROM hz_cust_accounts
WHERE orig_system_reference=p_account_number;
EXCEPTION
WHEN OTHERS THEN
oracle_error('update_cust_account, get cust account id error', 'Orig Account Number: ' || p_party_number);
END;
p_cust_account_rec.cust_account_id := l_cust_account_id;
p_cust_account_rec.account_name := p_account_name;
p_cust_account_rec.status := p_status;
p_cust_account_rec.customer_type := p_customer_type;
p_cust_account_rec.customer_class_code := p_customer_class_code;
p_cust_account_rec.created_by_module := p_created_by_module;
hz_cust_account_v2pub.update_cust_account(
p_init_msg_list => FND_API.G_TRUE,
p_cust_account_rec => p_cust_account_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
);
oracle_error('update_cust_account', p_party_number|| ' ' || p_account_number || ' ' || x_msg_data);
IF x_msg_count>1 THEN
FOR I IN 1..x_msg_count
LOOP
l_error_message := I ||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255);
oracle_error('update_cust_account', p_party_number|| ' ' || p_account_number || ' ' || l_error_message);
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END update_cust_account;
PROCEDURE create_location(p_location_id VARCHAR2,
p_address1 VARCHAR2,
p_address2 VARCHAR2,
p_address3 VARCHAR2,
p_address4 VARCHAR2,
p_city VARCHAR2,
p_postal_code VARCHAR2,
p_state VARCHAR2,
p_province VARCHAR2,
p_county VARCHAR2,
p_street_suffix VARCHAR2,
p_street VARCHAR2,
p_street_number VARCHAR2,
p_floor VARCHAR2,
p_country VARCHAR2,
p_created_by_module VARCHAR2,
p_orig_system VARCHAR2
)
AS
l_location_rec hz_location_v2pub.location_rec_type;
l_location_id NUMBER;
x_return_status VARCHAR2(200);
x_msg_count NUMBER;
x_msg_data VARCHAR2(200);
l_error_message VARCHAR2(1000);
BEGIN
l_location_rec.address1 := p_address1;
l_location_rec.address2 := p_address2;
l_location_rec.address3 := p_address3;
l_location_rec.address4 := p_address4;
l_location_rec.city := p_city;
l_location_rec.postal_code := p_postal_code;
l_location_rec.state := p_state;
l_location_rec.province := p_province;
l_location_rec.county := p_county;
l_location_rec.street_suffix := p_street_suffix;
l_location_rec.street := p_street;
l_location_rec.street_number := p_street_number;
l_location_rec.floor := p_floor;
l_location_rec.country := p_country;
l_location_rec.created_by_module := p_created_by_module;
l_location_rec.orig_system := p_orig_system;
l_location_rec.orig_system_reference := p_location_id;
hz_location_v2pub.create_location(
p_location_rec => l_location_rec,
x_location_id => l_location_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
oracle_error('create_location', 'Orig Location ID: ' || p_location_id || ' ' || x_msg_data);
IF x_msg_count>1 THEN
FOR I IN 1..x_msg_count
LOOP
l_error_message := I ||' . '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255);
oracle_error('create_location', 'Orig Location ID: ' || p_location_id || ' ' || l_error_message);
END LOOP;
END IF;
END create_location;
PROCEDURE update_location(p_location_id VARCHAR2,
p_address1 VARCHAR2,
p_address2 VARCHAR2,
p_address3 VARCHAR2,
p_address4 VARCHAR2,
p_city VARCHAR2,
p_postal_code VARCHAR2,
p_state VARCHAR2,
p_province VARCHAR2,
p_county VARCHAR2,
p_street_suffix VARCHAR2,
p_street VARCHAR2,
p_street_number VARCHAR2,
p_floor VARCHAR2,
p_country VARCHAR2,
p_created_by_module VARCHAR2,
p_orig_system VARCHAR2
)
AS
l_location_rec hz_location_v2pub.location_rec_type;
l_location_id NUMBER;
l_object_version_number NUMBER;
x_return_status VARCHAR2(200);
x_msg_count NUMBER;
x_msg_data VARCHAR2(200);
l_error_message VARCHAR2(1000);
BEGIN
BEGIN
SELECT location_id, object_version_number
INTO l_location_id, l_object_version_number
FROM hz_locations
WHERE orig_system_reference=p_location_id;
EXCEPTION
WHEN OTHERS THEN
oracle_error('update_location, get location id error', 'Orig location id: ' || p_location_id);
END;
l_location_rec.location_id := l_location_id;
l_location_rec.address1 := p_address1;
l_location_rec.address2 := p_address2;
l_location_rec.address3 := p_address3;
l_location_rec.address4 := p_address4;
l_location_rec.city := p_city;
l_location_rec.postal_code := p_postal_code;
l_location_rec.state := p_state;
l_location_rec.province := p_province;
l_location_rec.county := p_county;
l_location_rec.street_suffix := p_street_suffix;
l_location_rec.street := p_street;
l_location_rec.street_number := p_street_number;
l_location_rec.floor := p_floor;
l_location_rec.country := p_country;
l_location_rec.created_by_module := p_created_by_module;
l_location_rec.orig_system := p_orig_system;
l_location_rec.orig_system_reference := p_location_id;
hz_location_v2pub.update_location(
p_location_rec => l_location_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 );
oracle_error('update_location', 'Orig Location ID: ' || p_location_id || ' ' || x_msg_data);
IF x_msg_count>1 THEN
FOR I IN 1..x_msg_count
LOOP
l_error_message := I ||' . '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255);
oracle_error('update_location', 'Orig Location ID: ' || p_location_id || ' ' || l_error_message);
END LOOP;
END IF;
END update_location;
PROCEDURE create_party_site(p_party_number VARCHAR2,
p_location_id VARCHAR2,
p_status VARCHAR2,
p_party_site_name VARCHAR2,
p_attribute_category VARCHAR2,
p_addressee VARCHAR2,
p_created_by_module VARCHAR2,
p_orig_system VARCHAR2,
p_party_site_number VARCHAR2,
p_party_site_id VARCHAR2
)
AS
l_party_site_rec hz_party_site_v2pub.party_site_rec_type;
l_party_site_number VARCHAR2(100);
l_party_id NUMBER;
l_location_id NUMBER;
l_party_site_id NUMBER;
x_return_status VARCHAR2(200);
x_msg_count NUMBER;
x_msg_data VARCHAR2(200);
l_error_message VARCHAR2(1000);
BEGIN
BEGIN
SELECT party_id
INTO l_party_id
FROM hz_parties
WHERE orig_system_reference=p_party_number;
SELECT location_id
INTO l_location_id
FROM hz_locations
WHERE orig_system_reference=p_location_id;
EXCEPTION
WHEN OTHERS THEN
oracle_error('create_party_site, get party/location id error', 'Orig party/location Number: ' || p_party_number || ' ' || p_location_id);
END;
l_party_site_rec.party_id := l_party_id;
l_party_site_rec.location_id := l_location_id;
l_party_site_rec.status := p_status;
l_party_site_rec.party_site_name := p_party_site_name;
l_party_site_rec.attribute_category := p_attribute_category;
l_party_site_rec.addressee := p_addressee;
l_party_site_rec.created_by_module := p_created_by_module;
l_party_site_rec.orig_system := p_orig_system;
l_party_site_rec.orig_system_reference := p_party_site_id;
hz_party_site_v2pub.create_party_site(
p_party_site_rec => l_party_site_rec,
x_party_site_id => l_party_site_id,
x_party_site_number=> l_party_site_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
oracle_error('create_party_site', 'Orig Party Site ID' || p_party_site_id || ' ' || x_msg_data);
IF x_msg_count>1 THEN
FOR I IN 1..x_msg_count
LOOP
l_error_message := I ||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255);
oracle_error('create_party_site', 'Orig Party Site ID' || p_party_site_id || ' ' || l_error_message);
END LOOP;
END IF;
END create_party_site;
PROCEDURE update_party_site(p_party_number VARCHAR2,
p_location_id VARCHAR2,
p_status VARCHAR2,
p_party_site_name VARCHAR2,
p_attribute_category VARCHAR2,
p_addressee VARCHAR2,
p_created_by_module VARCHAR2,
p_orig_system VARCHAR2,
p_party_site_number VARCHAR2,
p_party_site_id VARCHAR2
)
AS
l_party_site_rec hz_party_site_v2pub.party_site_rec_type;
l_party_site_number VARCHAR2(100);
l_party_id NUMBER;
l_location_id NUMBER;
l_party_site_id NUMBER;
l_object_version_number NUMBER;
x_return_status VARCHAR2(200);
x_msg_count NUMBER;
x_msg_data VARCHAR2(200);
l_error_message VARCHAR2(1000);
BEGIN
BEGIN
SELECT party_id
INTO l_party_id
FROM hz_parties
WHERE orig_system_reference=p_party_number;
SELECT location_id
INTO l_location_id
FROM hz_locations
WHERE orig_system_reference=p_location_id;
EXCEPTION
WHEN OTHERS THEN
oracle_error('update_party_site, get party/location error', 'Orig party/location Number: ' || p_party_number || ' ' || p_location_id);
RAISE;
END;
BEGIN
SELECT party_site_id, object_version_number
INTO l_party_site_id, l_object_version_number
FROM hz_party_sites
WHERE orig_system_reference=p_party_site_id;
EXCEPTION
WHEN OTHERS THEN
oracle_error('update_party_site, get party site id/object version number', 'Orig party/location Number: ' || p_party_site_id );
RAISE;
END;
l_party_site_rec.party_site_id := l_party_site_id;
l_party_site_rec.party_id := l_party_id;
l_party_site_rec.location_id := l_location_id;
l_party_site_rec.status := p_status;
l_party_site_rec.party_site_name := p_party_site_name;
l_party_site_rec.attribute_category := p_attribute_category;
l_party_site_rec.addressee := p_addressee;
l_party_site_rec.created_by_module := p_created_by_module;
hz_party_site_v2pub.update_party_site(
p_party_site_rec => l_party_site_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
);
oracle_error('update_party_site', 'Orig Party Site ID' || p_party_site_id || ' ' || x_msg_data);
IF x_msg_count>1 THEN
FOR I IN 1..x_msg_count
LOOP
l_error_message := I ||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255);
oracle_error('update_party_site', 'Orig Party Site ID' || p_party_site_id || ' ' || l_error_message);
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END update_party_site;
procedure create_cust_acct_site(p_party_site_number VARCHAR2,
p_party_site_id VARCHAR2,
p_account_number VARCHAR2,
p_cust_acct_site_id VARCHAR2,
p_status VARCHAR2,
p_bill_to_flag VARCHAR2,
p_market_flag VARCHAR2,
p_ship_to_flag VARCHAR2,
p_customer_category_code VARCHAR2,
p_territory VARCHAR2,
p_translated_customer_name VARCHAR2,
p_created_by_module VARCHAR2,
p_orig_system VARCHAR2
)
AS
l_cust_acct_site_rec hz_cust_account_site_v2pub.CUST_ACCT_SITE_REC_TYPE;
l_party_site_id NUMBER;
l_cust_account_id NUMBER;
l_cust_acct_site_id NUMBER;
x_return_status VARCHAR2(200);
x_msg_count NUMBER;
x_msg_data VARCHAR2(200);
l_error_message VARCHAR2(1000);
BEGIN
dbms_application_info.set_client_info('81');
BEGIN
SELECT party_site_id
INTO l_party_site_id
FROM hz_party_sites
WHERE orig_system_reference = p_party_site_id;
SELECT cust_account_id
INTO l_cust_account_id
FROM hz_cust_accounts
WHERE orig_system_reference = p_account_number;
EXCEPTION
WHEN OTHERS THEN
oracle_error('create_cust_acct_site', 'Get Party Site ID/Cust Account ID error: '
|| ' Orig Party Site Number: ' || p_party_site_number
|| ' Cust Account Number: ' || p_account_number );
END;
l_cust_acct_site_rec.cust_account_id := l_cust_account_id;
l_cust_acct_site_rec.party_site_id := l_party_site_id;
l_cust_acct_site_rec.created_by_module := p_created_by_module;
l_cust_acct_site_rec.orig_system := p_orig_system;
l_cust_acct_site_rec.orig_system_reference := p_cust_acct_site_id;
hz_cust_account_site_v2pub.create_cust_acct_site(
p_init_msg_list => FND_API.G_TRUE,
p_cust_acct_site_rec => l_cust_acct_site_rec ,
x_cust_acct_site_id => l_cust_acct_site_id ,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
oracle_error('create_cust_acct_site', 'Orig Party Site Number: ' || p_party_site_number
|| ' Account Number: ' || p_account_number || x_msg_data);
IF x_msg_count>1 THEN
FOR I IN 1..x_msg_count
LOOP
l_error_message := I ||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255);
oracle_error('create_cust_acct_site', 'Orig Party Site Number: ' || p_party_site_number
|| ' Account Number: ' || p_account_number || x_msg_data || ' ' || l_error_message);
END LOOP;
END IF;
END create_cust_acct_site;
procedure update_cust_acct_site(p_party_site_number VARCHAR2,
p_party_site_id VARCHAR2,
p_account_number VARCHAR2,
p_cust_acct_site_id VARCHAR2,
p_status VARCHAR2,
p_bill_to_flag VARCHAR2,
p_market_flag VARCHAR2,
p_ship_to_flag VARCHAR2,
p_customer_category_code VARCHAR2,
p_territory VARCHAR2,
p_translated_customer_name VARCHAR2,
p_created_by_module VARCHAR2,
p_orig_system VARCHAR2
)
AS
l_cust_acct_site_rec hz_cust_account_site_v2pub.CUST_ACCT_SITE_REC_TYPE;
l_party_site_id NUMBER;
l_cust_account_id NUMBER;
l_cust_acct_site_id NUMBER;
l_object_version_number NUMBER;
x_return_status VARCHAR2(200);
x_msg_count NUMBER;
x_msg_data VARCHAR2(200);
l_error_message VARCHAR2(1000);
BEGIN
dbms_application_info.set_client_info('81');
BEGIN
SELECT party_site_id
INTO l_party_site_id
FROM hz_party_sites
WHERE orig_system_reference = p_party_site_id;
SELECT cust_account_id
INTO l_cust_account_id
FROM hz_cust_accounts
WHERE orig_system_reference = p_account_number;
EXCEPTION
WHEN OTHERS THEN
oracle_error('update_cust_acct_site', 'Get Party Site ID/Cust Account ID error: '
|| ' Orig Party Site Number: ' || p_party_site_number
|| ' Cust Account Number: ' || p_account_number );
END;
BEGIN
SELECT cust_acct_site_id, object_version_number
INTO l_cust_acct_site_id, l_object_version_number
FROM hz_cust_acct_sites
WHERE orig_system_reference=p_cust_acct_site_id ;
EXCEPTION
WHEN OTHERS THEN
oracle_error('update_cust_acct_site', 'Get Cust Acct: ' || p_cust_acct_site_id );
END;
l_cust_acct_site_rec.cust_acct_site_id := l_cust_acct_site_id;
l_cust_acct_site_rec.cust_account_id := l_cust_account_id;
l_cust_acct_site_rec.party_site_id := l_party_site_id;
l_cust_acct_site_rec.created_by_module := p_created_by_module;
l_cust_acct_site_rec.orig_system := p_orig_system;
l_cust_acct_site_rec.orig_system_reference := p_cust_acct_site_id;
hz_cust_account_site_v2pub.update_cust_acct_site(
p_init_msg_list => FND_API.G_TRUE,
p_cust_acct_site_rec => l_cust_acct_site_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
);
oracle_error('update_cust_acct_site', 'Orig Party Site Number: ' || p_party_site_number
|| ' Account Number: ' || p_account_number || x_msg_data);
IF x_msg_count>1 THEN
FOR I IN 1..x_msg_count
LOOP
l_error_message := I ||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255);
oracle_error('update_cust_acct_site', 'Orig Party Site Number: ' || p_party_site_number
|| ' Account Number: ' || p_account_number || x_msg_data || ' ' || l_error_message);
END LOOP;
END IF;
END update_cust_acct_site;
PROCEDURE create_merge_batch(p_batch_id VARCHAR2,
p_rule_set_name VARCHAR2,
p_batch_name VARCHAR2,
p_request_id VARCHAR2,
p_batch_status VARCHAR2,
p_batch_commit VARCHAR2,
p_batch_delete VARCHAR2,
p_merge_reason_code VARCHAR2,
p_created_by_module VARCHAR2 )
AS
l_batch_id NUMBER;
BEGIN
SELECT hz_merge_batch_s.nextval
INTO l_batch_id
FROM dual;
INSERT INTO hz_merge_batch
( BATCH_ID
,RULE_SET_NAME
,BATCH_NAME
,REQUEST_ID
,BATCH_STATUS
,BATCH_COMMIT
,BATCH_DELETE
,MERGE_REASON_CODE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATED_BY_MODULE
,ORIG_SYSTEM_REFERENCE )
VALUES( l_batch_id
,p_rule_set_name
,p_batch_name
,NULL --REQUEST_ID
,p_batch_status --BATCH_STATUS
,p_batch_commit --BATCH_COMMIT
,p_batch_delete --BATCH_DELETE
,p_merge_reason_code --MERGE_REASON_CODE
,'0'
,SYSDATE
,'0'
,SYSDATE
,'0'
,p_created_by_module
,p_batch_id
);
COMMIT;
oracle_error('create_merge_batch, success!', 'Orig Batch ID: ' || p_batch_id );
EXCEPTION
WHEN OTHERS THEN
oracle_error('create_merge_batch, failed!', 'Orig Batch ID: ' || p_batch_id || ' ' || sqlerrm);
END create_merge_batch;
PROCEDURE create_merge_parties(p_batch_party_id VARCHAR2,
p_batch_id VARCHAR2,
p_merge_type VARCHAR2,
p_from_party_num VARCHAR2,
p_to_party_num VARCHAR2,
p_merge_reason_code VARCHAR2,
p_merge_status VARCHAR2
)
AS
l_batch_party_id NUMBER;
l_batch_id NUMBER;
l_from_party_id NUMBER;
l_to_party_id NUMBER;
BEGIN
SELECT hz_merge_parties_s.nextval
INTO l_batch_party_id
FROM dual;
BEGIN
SELECT batch_id
INTO l_batch_id
FROM hz_merge_batch
WHERE orig_system_reference=p_batch_id;
SELECT party_id
INTO l_from_party_id
FROM hz_parties
WHERE orig_system_reference = p_from_party_num;
SELECT party_id
INTO l_to_party_id
FROM hz_parties
WHERE orig_system_reference = p_to_party_num;
EXCEPTION
WHEN OTHERS THEN
oracle_error('create_merge_parties', 'get from party/to party error: ' || p_from_party_num
||' ' || p_to_party_num);
END;
INSERT INTO hz_merge_parties
( BATCH_PARTY_ID
,BATCH_ID
,MERGE_TYPE
,FROM_PARTY_ID
,TO_PARTY_ID
,MERGE_REASON_CODE
,MERGE_STATUS
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,ORIG_SYSTEM_REFERENCE )
VALUES( l_batch_party_id
,l_batch_id
,p_merge_type
,l_from_party_id
,l_to_party_id
,p_merge_reason_code
,p_merge_status
,'0'
,SYSDATE
,'0'
,SYSDATE
,'0'
,p_batch_party_id );
COMMIT;
oracle_error('create_merge_parties, success!', 'Orig Batch Party ID: ' || p_batch_party_id );
EXCEPTION
WHEN OTHERS THEN
oracle_error('create_merge_parties, failed!', 'Orig Batch Party ID: ' || p_batch_party_id || ' ' || sqlerrm);
END create_merge_parties;
PROCEDURE create_merge_parties_sugg(p_batch_id VARCHAR2,
p_batch_party_id VARCHAR2,
p_merge_type VARCHAR2,
p_from_party_num VARCHAR2,
p_to_party_num VARCHAR2,
p_merge_reason_code VARCHAR2,
p_merge_status VARCHAR2 )
AS
l_batch_id NUMBER;
l_batch_party_id NUMBER;
l_from_party_id NUMBER;
l_to_party_id NUMBER;
BEGIN
BEGIN
SELECT batch_party_id
INTO l_batch_party_id
FROM hz_merge_parties
WHERE orig_system_reference = p_batch_party_id;
EXCEPTION
WHEN OTHERS THEN
oracle_error('create_merge_parties_sugg, error', 'Org batch party id:' || p_batch_party_id);
END;
BEGIN
SELECT batch_id
INTO l_batch_id
FROM hz_merge_batch
WHERE orig_system_reference=p_batch_id;
SELECT party_id
INTO l_from_party_id
FROM hz_parties
WHERE orig_system_reference = p_from_party_num;
SELECT party_id
INTO l_to_party_id
FROM hz_parties
WHERE orig_system_reference = p_to_party_num;
EXCEPTION
WHEN OTHERS THEN
oracle_error('create_merge_parties_sugg', 'get from party/to party error: ' || p_from_party_num
||' ' || p_to_party_num);
END;
INSERT INTO hz_merge_parties_sugg
( BATCH_ID
,BATCH_PARTY_ID
,MERGE_TYPE
,FROM_PARTY_ID
,TO_PARTY_ID
,MERGE_REASON_CODE
,MERGE_STATUS
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,ORIG_SYSTEM_REFERENCE )
VALUES( l_batch_id
,l_batch_party_id
,p_merge_type
,l_from_party_id
,l_to_party_id
,p_merge_reason_code
,p_merge_status
,'0'
,SYSDATE
,'0'
,SYSDATE
,'0'
,p_batch_party_id
);
COMMIT;
oracle_error('create_merge_parties_sugg, success!', 'Orig Batch Party ID: ' || p_batch_party_id );
EXCEPTION
WHEN OTHERS THEN
oracle_error('create_merge_parties_sugg, failed!', 'Orig Batch Party ID: ' || p_batch_party_id || ' ' || sqlerrm);
END create_merge_parties_sugg;
PROCEDURE create_merge_party_details(p_batch_party_id VARCHAR2,
p_entity_name VARCHAR2,
p_merge_from_entity_num VARCHAR2,
p_merge_to_entity_num VARCHAR2,
p_mandatory_merge VARCHAR2,
p_object_version_number VARCHAR2 )
AS
l_batch_party_id NUMBER;
l_merge_from_entity_id NUMBER;
l_merge_to_entity_id NUMBER;
BEGIN
BEGIN
SELECT batch_party_id
INTO l_batch_party_id
FROM hz_merge_parties
WHERE orig_system_reference = p_batch_party_id;
EXCEPTION
WHEN OTHERS THEN
oracle_error('create_merge_party_details, error', 'Org batch party id:' || p_batch_party_id);
END;
IF p_entity_name = 'HZ_PARTY_SITES' THEN
BEGIN
SELECT party_site_id
INTO l_merge_from_entity_id
FROM hz_party_sites
WHERE orig_system_reference = p_merge_from_entity_num;
SELECT party_site_id
INTO l_merge_to_entity_id
FROM hz_party_sites
WHERE orig_system_reference = p_merge_to_entity_num;
EXCEPTION
WHEN OTHERS THEN
oracle_error('create_merge_party_details, error', 'Org From entity num/To entity num' || p_merge_from_entity_num
||' ' ||p_merge_to_entity_num);
RAISE;
END;
INSERT INTO hz_merge_party_details
( BATCH_PARTY_ID
,ENTITY_NAME
,MERGE_FROM_ENTITY_ID
,MERGE_TO_ENTITY_ID
,MANDATORY_MERGE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,OBJECT_VERSION_NUMBER
,ORIG_SYSTEM_REFERENCE )
VALUES( l_batch_party_id,
p_entity_name,
l_merge_from_entity_id,
l_merge_to_entity_id,
p_mandatory_merge,
'0',
SYSDATE,
'0',
SYSDATE,
'0',
p_object_version_number,
p_batch_party_id
);
END IF;
COMMIT;
oracle_error('create_merge_party_details, success!', 'Orig Batch Party ID: ' || p_batch_party_id );
EXCEPTION
WHEN OTHERS THEN
oracle_error('create_merge_party_details, failed!', 'Orig Batch Party ID: ' || p_batch_party_id || ' ' || sqlerrm);
END create_merge_party_details;
PROCEDURE create_merge_entity_att(p_merge_batch_id VARCHAR2,
p_merge_to_party_num VARCHAR2,
p_attribute_name VARCHAR2,
p_attribute_value VARCHAR2,
p_attribute_type VARCHAR2,
p_attribute_party_num VARCHAR2,
p_entity_name VARCHAR2,
p_object_version_number VARCHAR2)
AS
l_merge_batch_id NUMBER;
l_merge_to_party_id NUMBER;
l_attribute_party_id NUMBER;
BEGIN
BEGIN
SELECT batch_id
INTO l_merge_batch_id
FROM hz_merge_batch
WHERE orig_system_reference = p_merge_batch_id;
EXCEPTION
WHEN OTHERS THEN
oracle_error('create_merge_entity_att, error', 'Org batch id:' || p_merge_batch_id);
END;
BEGIN
SELECT party_id
INTO l_attribute_party_id
FROM hz_parties
WHERE orig_system_reference = p_attribute_party_num;
EXCEPTION
WHEN OTHERS THEN
oracle_error('create_merge_entity_att, error', 'Org party num:' || p_attribute_party_num);
END;
BEGIN
SELECT party_id
INTO l_merge_to_party_id
FROM hz_parties
WHERE orig_system_reference = p_merge_to_party_num;
EXCEPTION
WHEN OTHERS THEN
oracle_error('create_merge_entity_att, error', 'Org party num:' || p_merge_to_party_num);
END;
/*
INSERT INTO hz_merge_entity_attributes
( MERGE_BATCH_ID
,MERGE_TO_PARTY_ID
,ATTRIBUTE_NAME
,ATTRIBUTE_VALUE
,ATTRIBUTE_TYPE
,ATTRIBUTE_PARTY_ID
,ENTITY_NAME
,DERIVED_LAST_UPDATE_DATE
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,LAST_UPDATE_DATE
,ORIG_SYSTEM_REFERENCE )
VALUES( l_merge_batch_id
,l_merge_to_party_id
,p_attribute_name
,p_attribute_value
,p_attribute_type
,l_attribute_party_id
,p_entity_name
,SYSDATE
,p_object_version_number
,'0'
,SYSDATE
,'0'
,'0'
,SYSDATE
,p_merge_batch_id
);
*/
COMMIT;
oracle_error('create_merge_entity_att, success!', 'Orig Batch ID: ' || p_merge_batch_id );
EXCEPTION
WHEN OTHERS THEN
oracle_error('create_merge_entity_att, failed!', 'Orig Batch ID: ' || p_merge_batch_id || ' ' || sqlerrm);
END create_merge_entity_att;
PROCEDURE create_dup_batch(p_dup_batch_name VARCHAR2,
p_match_rule_id VARCHAR2,
p_application_id VARCHAR2,
p_request_type VARCHAR2,
p_parties_total VARCHAR2,
p_automerge_flag VARCHAR2,
p_dup_batch_id VARCHAR2
)
AS
l_dup_batch_id NUMBER;
BEGIN
SELECT hz_dup_batch_s.nextval
INTO l_dup_batch_id
FROM dual;
INSERT INTO hz_dup_batch
( DUP_BATCH_ID
,DUP_BATCH_NAME
,MATCH_RULE_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,APPLICATION_ID
,REQUEST_TYPE
,REQUEST_ID
,PARTIES_TOTAL
,AUTOMERGE_FLAG
,ORIG_SYSTEM_REFERENCE )
VALUES( l_dup_batch_id
,p_dup_batch_name
,p_match_rule_id
,'0'
,SYSDATE
,'0'
,SYSDATE
,'0'
,p_application_id
,p_request_type
,NULL
,p_parties_total
,p_automerge_flag
,p_dup_batch_id );
COMMIT;
oracle_error('create dup batch, success!', 'Orig dup batch id: ' || p_dup_batch_id );
EXCEPTION
WHEN OTHERS THEN
oracle_error('create dup batch, failed!', 'Orig dup batch id: ' || p_dup_batch_id || ' ' || sqlerrm);
END create_dup_batch;
PROCEDURE create_dup_sets(p_dup_batch_id VARCHAR2,
p_winner_party_num VARCHAR2,
p_status VARCHAR2,
p_merge_type VARCHAR2,
p_object_version_number VARCHAR2,
p_dup_set_id VARCHAR2 )
AS
l_dup_set_id NUMBER;
l_dup_batch_id NUMBER;
l_winner_party_id NUMBER;
BEGIN
SELECT hz_dup_sets_s.nextval
INTO l_dup_set_id
FROM dual;
SELECT dup_batch_id
INTO l_dup_batch_id
FROM hz_dup_batch
WHERE ORIG_SYSTEM_REFERENCE=p_dup_batch_id;
SELECT party_id
INTO l_winner_party_id
FROM hz_parties
WHERE ORIG_SYSTEM_REFERENCE=p_winner_party_num;
INSERT INTO hz_dup_sets
( DUP_SET_ID
,DUP_BATCH_ID
,WINNER_PARTY_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,STATUS
,ASSIGNED_TO_USER_ID
,MERGE_TYPE
,OBJECT_VERSION_NUMBER
,REQUEST_ID
,ORIG_SYSTEM_REFERENCE )
VALUES( l_dup_set_id
,l_dup_batch_id
,l_winner_party_id
,'0'
,SYSDATE
,'0'
,SYSDATE
,'0'
,p_status
,'0'
,p_merge_type
,p_object_version_number
,NULL
,p_dup_set_id ) ;
COMMIT;
oracle_error('create dup sets, success!', 'Orig dup batch set id: ' || l_dup_set_id );
EXCEPTION
WHEN OTHERS THEN
oracle_error('create dup sets, failed!', 'Orig dup batch set id: ' || l_dup_set_id || ' ' || sqlerrm);
END create_dup_sets;
PROCEDURE create_dup_set_parties(p_dup_party_num VARCHAR2,
p_dup_set_id VARCHAR2,
p_merge_seq_id VARCHAR2,
p_merge_batch_id VARCHAR2,
p_score VARCHAR2,
p_merge_flag VARCHAR2,
p_not_dup VARCHAR2,
p_merge_batch_name VARCHAR2 )
AS
l_dup_set_party_id NUMBER;
l_dup_set_id NUMBER;
l_merge_batch_id NUMBER;
BEGIN
SELECT party_id
INTO l_dup_set_party_id
FROM hz_parties
WHERE ORIG_SYSTEM_REFERENCE=p_dup_party_num;
SELECT dup_set_id
INTO l_dup_set_id
FROM hz_dup_sets
WHERE ORIG_SYSTEM_REFERENCE=p_dup_set_id;
BEGIN
SELECT batch_id
INTO l_merge_batch_id
FROM hz_merge_batch
WHERE ORIG_SYSTEM_REFERENCE=p_merge_batch_id;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
INSERT INTO hz_dup_set_parties
( DUP_PARTY_ID
,DUP_SET_ID
,MERGE_SEQ_ID
,MERGE_BATCH_ID
,SCORE
,MERGE_FLAG
,NOT_DUP
,MERGE_BATCH_NAME
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,ORIG_SYSTEM_REFERENCE )
VALUES( l_dup_set_party_id
,l_dup_set_id
,p_merge_seq_id
,l_merge_batch_id
,p_score
,p_merge_flag
,p_not_dup
,p_merge_batch_name
,'0'
,SYSDATE
,'0'
,SYSDATE
,'0'
,p_dup_party_num || '/' || p_dup_set_id );
COMMIT;
oracle_error('create dup batch party, success!', 'Orig dup batch party num: ' || p_dup_party_num );
EXCEPTION
WHEN OTHERS THEN
oracle_error('create dup batch party, failed!', 'Orig dup batch party num: ' || p_dup_party_num || ' ' || sqlerrm);
END create_dup_set_parties;
PROCEDURE submit_customer_merge(p_customer_number VARCHAR2,
p_customer_name VARCHAR2,
p_duplicate_number VARCHAR2,
p_duplicate_name VARCHAR2,
p_delete_duplicate_flag VARCHAR2,
p_process_flag VARCHAR2,
p_customer_first_name VARCHAR2,
p_customer_last_name VARCHAR2,
p_customer_type VARCHAR2,
p_duplicate_first_name VARCHAR2,
p_duplicate_last_name VARCHAR2,
p_duplicate_type VARCHAR2,
p_merge_reason_code VARCHAR2,
p_merge_fail_msg VARCHAR2
)
AS
l_reqid NUMBER;
l_merge_id NUMBER;
l_merge_header_id NUMBER;
l_customer_id NUMBER;
l_customer_number VARCHAR2(50);
l_customer_ref VARCHAR2(50);
l_duplicate_id NUMBER;
l_duplicate_number VARCHAR2(50);
l_duplicate_ref VARCHAR2(50);
BEGIN
BEGIN
SELECT ra_customer_merges_s.nextval
INTO l_merge_id
FROM DUAL;
SELECT ra_customer_merge_headers_s.nextval
INTO l_merge_header_id
FROM DUAL;
SELECT hca.cust_account_id, hca.account_number, hca.orig_system_reference
INTO l_customer_id, l_customer_number, l_customer_ref
FROM hz_cust_accounts hca
WHERE hca.orig_system_reference = p_customer_number;
SELECT hca.cust_account_id, hca.account_number, hca.orig_system_reference
INTO l_duplicate_id, l_duplicate_number, l_duplicate_ref
FROM hz_cust_accounts hca
WHERE hca.orig_system_reference = p_duplicate_number;
EXCEPTION
WHEN OTHERS THEN
oracle_error('submit customer merge', 'get customer id /duplicate id error');
RAISE;
END;
INSERT INTO ra_customer_merge_headers
( CUSTOMER_MERGE_HEADER_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_ID
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/59792/viewspace-1029364/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SAP Cloud for Customer 如何直接消費S/4HANA APICloudAPI
- SAP 電商雲 Spartacus UI 同 SAP Customer Data Cloud 整合執行時的 apiUICloudAPI
- R12 Unable To Query Customer With Partial 'Customer Account Number'
- SAP Cloud for Customer Account和individual customer的區別Cloud
- SAP Cloud for Customer裡BusinessPartner, Customer和Employee這些BO的區別Cloud
- SAP Cloud for Customer的前世今生Cloud
- Customer Exit變數增強變數
- CUSTOMER INTERFACE STATUS ERROR CODESError
- Dependencies for Variables of Type Customer Exit [SAP Help]
- User Exits,Customer Exits,BAdI and BTE
- 顧客細分（Customer Segmentation）(轉載)Segmentation
- BAdI: Customer-defined Functions in Formula BuilderFunctionORMUI
- 如何使用SAP Cloud for Customer裡的Data SourceCloud
- SAP Cloud for Customer 標準培訓課程Cloud
- 機器學習在SAP Cloud for Customer中的應用機器學習Cloud
- SAP Cloud for Customer Price-計價簡介Cloud
- SAP Cloud for Customer的CTI呼叫中心解決方案Cloud
- SAP Cloud for Customer的Mashup位於CustomPane裡Cloud
- SAP Cloud for Customer ABSL的一些優化Cloud優化
- 【Leetcode】1672. Richest Customer WealthLeetCode
- SAP Cloud for Customer框架是如何使用JavaScript Promise的Cloud框架JavaScriptPromise
- 使用soapUI消費SAP Cloud for Customer的web serviceUICloudWeb
- How To Create a Tab in Customer Admin Edit in Magento 2 ?(轉)
- 使用Excel匯入資料到SAP Cloud for Customer系統ExcelCloud
- SAP Cloud for Customer的Container應用設計原理CloudAI
- SAP Cloud for Customer的Account Team裡的role如何配置Cloud
- SAP Cloud for Customer Extensibility的設計與實現Cloud
- Hybris ECP裡Customer對應的資料庫表資料庫
- 如何把SAP Kyma和SAP Cloud for Customer連線起來Cloud
- SAP Cloud for Customer客戶主資料的地圖整合Cloud地圖
- SAP Cloud for Customer裡Sales Order和Sales Quote的建模方式Cloud
- SAP 電商雲 Spartacus UI 同 SAP Customer Data Cloud 的整合UICloud
- 使用nodejs消費SAP Cloud for Customer上的Web serviceNodeJSCloudWeb
- SAP Cloud for Customer(C4C)和微信整合系列教程Cloud
- SAP S4系統建立Customer和Vendor的BAPIAPI
- 如何在SAP Cloud for Customer自定義BO中建立訪問控制Cloud
- SAP CRM和Cloud for Customer中的Event handler（事件處理器）Cloud事件
- SAP Cloud for Customer銷售訂單External Note的建模細節Cloud