oracle concepts for you: TCA Create Party Package

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;
/

oracle concepts for you

Monday, 23 June 2014

TCA Create Party Package

No comments:

Post a Comment