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
,PROGRAM_APPLICATION_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,CUSTOMER_ID
,CUSTOMER_NAME
,CUSTOMER_NUMBER
,CUSTOMER_REF
,DUPLICATE_ID
,DUPLICATE_NAME
,DUPLICATE_NUMBER
,DUPLICATE_REF
,DELETE_DUPLICATE_FLAG
,PROCESS_FLAG
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,CUSTOMER_FIRST_NAME
,CUSTOMER_LAST_NAME
,CUSTOMER_TYPE
,DUPLICATE_FIRST_NAME
,DUPLICATE_LAST_NAME
,DUPLICATE_TYPE
,MERGE_REASON_CODE
,MERGE_FAIL_MSG )
VALUES( l_merge_header_id
,SYSDATE
,'0' --created_by
,SYSDATE
,'0'
,'0' --last_update_login
,NULL
,NULL
,NULL
,NULL --request_id
,l_customer_id --customer_id
,p_customer_name
,l_customer_number
,l_customer_ref
,l_duplicate_id
,p_duplicate_name
,l_duplicate_number
,l_duplicate_ref
,p_delete_duplicate_flag
,p_process_flag
,NULL --attribute_category
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,p_customer_first_name
,p_customer_last_name
,p_customer_type
,p_duplicate_first_name
,p_duplicate_last_name
,p_duplicate_type
,p_merge_reason_code
,p_merge_fail_msg
);
/* INSERT INTO ra_customer_merges
( l_merge_id --CUSTOMER_MERGE_ID
,SYSDATE --CREATION_DATE
,'0' --CREATED_BY
,SYSDATE --LAST_UPDATE_DATE
,'0' --LAST_UPDATED_BY
,'0' --LAST_UPDATE_LOGIN
,p_process_flag --PROCESS_FLAG
,l_customer_id --CUSTOMER_ID
,p_customer_name --CUSTOMER_NAME
,l_customer_number --CUSTOMER_NUMBER
,l_customer_ref --CUSTOMER_REF
,CUSTOMER_ADDRESS_ID
,CUSTOMER_ADDRESS
,CUSTOMER_SITE_ID
,CUSTOMER_SITE_CODE
,NULL --ATTRIBUTE_CATEGORY
,NULL --ATTRIBUTE1
,NULL --ATTRIBUTE2
,NULL --ATTRIBUTE3
,NULL --ATTRIBUTE4
,NULL --ATTRIBUTE5
,NULL --ATTRIBUTE6
,NULL --ATTRIBUTE7
,NULL --ATTRIBUTE8
,NULL --ATTRIBUTE9
,NULL --ATTRIBUTE10
,NULL --REQUEST_ID
,NULL --PROGRAM_APPLICATION_ID
,NULL --PROGRAM_ID
,NULL --PROGRAM_UPDATE_DATE
,NULL --ATTRIBUTE11
,NULL --ATTRIBUTE12
,NULL --ATTRIBUTE13
,NULL --ATTRIBUTE14
,NULL --ATTRIBUTE15
,CUSTOMER_LOCATION
,CUSTOMER_PRIMARY_FLAG
,DELETE_DUPLICATE_FLAG
,DUPLICATE_ADDRESS
,DUPLICATE_ADDRESS_ID
,DUPLICATE_ID
,DUPLICATE_LOCATION
,DUPLICATE_NAME
,DUPLICATE_NUMBER
,DUPLICATE_PRIMARY_FLAG
,DUPLICATE_REF
,DUPLICATE_SITE_CODE
,DUPLICATE_SITE_ID
,SET_NUMBER
,CUSTOMER_MERGE_HEADER_ID
,CUSTOMER_FIRST_NAME
,CUSTOMER_LAST_NAME
,CUSTOMER_TYPE
,DUPLICATE_FIRST_NAME
,DUPLICATE_LAST_NAME
,DUPLICATE_TYPE
,CUSTOMER_CREATESAME )
VALUES( CUSTOMER_MERGE_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROCESS_FLAG
,CUSTOMER_ID
,CUSTOMER_NAME
,CUSTOMER_NUMBER
,CUSTOMER_REF
,CUSTOMER_ADDRESS_ID
,CUSTOMER_ADDRESS
,CUSTOMER_SITE_ID
,CUSTOMER_SITE_CODE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,CUSTOMER_LOCATION
,CUSTOMER_PRIMARY_FLAG
,DELETE_DUPLICATE_FLAG
,DUPLICATE_ADDRESS
,DUPLICATE_ADDRESS_ID
,DUPLICATE_ID
,DUPLICATE_LOCATION
,DUPLICATE_NAME
,DUPLICATE_NUMBER
,DUPLICATE_PRIMARY_FLAG
,DUPLICATE_REF
,DUPLICATE_SITE_CODE
,DUPLICATE_SITE_ID
,SET_NUMBER
,CUSTOMER_MERGE_HEADER_ID
,CUSTOMER_FIRST_NAME
,CUSTOMER_LAST_NAME
,CUSTOMER_TYPE
,DUPLICATE_FIRST_NAME
,DUPLICATE_LAST_NAME
,DUPLICATE_TYPE
,CUSTOMER_CREATESAME ) */
l_reqid := FND_REQUEST.SUBMIT_REQUEST
( application => 'AR'
,program => 'RAXMRG'
,Description => 'Customer Merge'
,start_time => NULL
,sub_request => FALSE
);
END;
END oracle_cdh;
/
reference:http://www.imcmethods.com/blog/wp-content/uploads/2008/02/oracle_cdh
