ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [zz]Oracle EBS API: TCA模型主要对象的API示例

[zz]Oracle EBS API: TCA模型主要对象的API示例

原创 Linux操作系统 作者:idoo 时间:2009-11-30 16:33:58 0 删除 编辑

在Oracle EBS中, TCA(Trading Community Architecture)作为一个重要的基础模型, 用于保存客户, 员工, 供应商, 合作伙伴的数据, 以及其相互关系,  是CRM, OM, PO, Core HR, AP, AR等模块的基础.

本示例列出了TCA中常用对象的API调用方法, 可供技术顾问参考并进一步扩展. 包括: 创建更新个人, 组织, 地点, 帐户;  准备Party合并请求, 合并请求属性, 提交合并请求等.

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

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

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

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

     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_FALSE,
             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
           );

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

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

     hz_customer_profile_v2pub.create_customer_profile (
                p_customer_profile_rec    => l_customer_profile_rec,
                p_create_profile_amt         => FND_API.G_TRUE,
                x_cust_account_profile_id => l_cust_account_profile_id,
                x_return_status                    => x_return_status,
                x_msg_count                         => x_msg_count,
                x_msg_data                           => x_msg_data
                );
具体示例代码请参见附件,  其详细列出具体API调用的输入输出和异常处理:

CREATE OR REPLACE PACKAGE imc_cdh IS

PROCEDURE create_party(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_orig_system VARCHAR2
);

PROCEDURE update_party(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_orig_system VARCHAR2
);

PROCEDURE create_cust_account(p_party_number VARCHAR2,
p_account_name VARCHAR2,
p_created_by_module VARCHAR2);


PROCEDURE create_location(p_street_number VARCHAR2,
p_street_name VARCHAR2,
p_block_number VARCHAR2,
p_unit_number VARCHAR2,
p_city VARCHAR2,
p_postal_code VARCHAR2,
p_block_name VARCHAR2,
p_created_by_module VARCHAR2,
p_country VARCHAR2
);

PROCEDURE create_party_site(p_party_number NUMBER,
p_location_org_sys_ref VARCHAR2,
p_created_by_module VARCHAR2);

END imc_cdh;
/
CREATE OR REPLACE PACKAGE BODY imc_cdh IS

PROCEDURE create_party(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_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.created_by_module := p_created_by_module;
l_person_rec.party_rec.party_number := p_party_number;
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.party_number := p_party_number;
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;

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);
sina_error('create_party', p_party_type || p_party_name || ' ' || l_error_message);
END LOOP;

END create_party;


PROCEDURE update_party(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_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

SELECT party_id, object_version_number
INTO l_party_id, l_version_number
FROM hz_parties
WHERE party_number = p_party_number;

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.created_by_module := p_created_by_module;
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.party_id := l_party_id;
l_organization_rec.party_rec.party_number := p_party_number;
l_organization_rec.party_rec.orig_system_reference := p_party_number;
l_organization_rec.party_rec.orig_system := p_orig_system;

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;

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);
sina_error('update_party', p_party_type || p_party_name || ' ' || l_error_message);
END LOOP;

END update_party;


PROCEDURE create_cust_account(p_party_number VARCHAR2,
p_account_name VARCHAR2,
p_created_by_module 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);

BEGIN

dbms_application_info.set_client_info('82');

p_cust_account_rec.account_name := p_account_name;
p_cust_account_rec.created_by_module := p_created_by_module;

SELECT party_id, party_type
INTO l_party_id, l_party_type
FROM hz_parties
WHERE party_number=p_party_number;

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_FALSE,
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_person_rec => p_person_rec,
p_customer_profile_rec => p_customer_profile_rec,
p_create_profile_amt => FND_API.G_FALSE,
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;

EXCEPTION
WHEN OTHERS THEN
NULL;
END;


PROCEDURE create_location(p_street_number VARCHAR2,
p_street_name VARCHAR2,
p_block_number VARCHAR2,
p_unit_number VARCHAR2,
p_city VARCHAR2,
p_postal_code VARCHAR2,
p_block_name VARCHAR2,
p_created_by_module VARCHAR2,
p_country VARCHAR2
)
AS
l_location_rec hz_location_v2pub.location_rec_type;
x_return_status VARCHAR2(200);
x_msg_count NUMBER;
x_msg_data VARCHAR2(200);
l_location_id NUMBER;
BEGIN
l_location_rec.address1 := p_street_number;
l_location_rec.address2 := p_street_name;
l_location_rec.address3 := p_block_number;
l_location_rec.address4 := p_unit_number;
l_location_rec.city := p_city;
l_location_rec.postal_code := p_postal_code;
l_location_rec.address_lines_phonetic := p_block_name;
l_location_rec.created_by_module := p_created_by_module;
l_location_rec.country := p_country;
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 );
END;


PROCEDURE create_party_site(p_party_number NUMBER,
p_location_org_sys_ref VARCHAR2,
p_created_by_module 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);

BEGIN

SELECT party_id
INTO l_party_id
FROM hz_parties
WHERE party_number=p_party_number;

SELECT location_id
INTO l_location_id
FROM hz_locations
WHERE orig_system_reference=p_location_org_sys_ref;

l_party_site_rec.party_id := l_party_id;
l_party_site_rec.location_id := l_location_id;
l_party_site_rec.created_by_module := p_created_by_module;
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
);

END create_party_site;


procedure create_cust_profile(p_party_id IN NUMBER,
p_cust_acct_id IN NUMBER,
p_statment_cycle_id IN NUMBER)
AS
x_return_status VARCHAR2(200);
x_msg_count NUMBER;
x_msg_data VARCHAR2(200);

l_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
l_cust_account_profile_id NUMBER;
l_lastest_cust_acct_id NUMBER;

BEGIN

SELECT MAX(hca.cust_account_id)
INTO l_lastest_cust_acct_id
FROM hz_cust_accounts hca
WHERE hca.party_id = p_party_id;

l_customer_profile_rec.cust_account_id := l_lastest_cust_acct_id;
l_customer_profile_rec.statement_cycle_id := p_statment_cycle_id;
l_customer_profile_rec.created_by_module := 'AST_RC_ALL';

hz_customer_profile_v2pub.create_customer_profile (
p_customer_profile_rec => l_customer_profile_rec,
p_create_profile_amt => FND_API.G_TRUE,
x_cust_account_profile_id => l_cust_account_profile_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);

UPDATE hz_customer_profiles hcp
SET hcp.statement_cycle_id = p_statment_cycle_id
WHERE hcp.cust_account_id = p_cust_acct_id
AND hcp.site_use_id IS NULL;

END create_cust_profile;




END imc_cdh;


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/318394/viewspace-621027/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-04-13

  • 博文量
    13
  • 访问量
    35622