来自:http://www.itpub.net/thread-1772135-1-1.html
1.创建银行
-- Create Bank
DECLARE
p_init_msg_list VARCHAR2(200);
p_country_code VARCHAR2(200);
p_bank_name VARCHAR2(200);
p_bank_number VARCHAR2(200);
p_alternate_bank_name VARCHAR2(200);
p_short_bank_name VARCHAR2(200);
p_description VARCHAR2(200);
p_tax_payer_id VARCHAR2(200);
p_tax_registration_number VARCHAR2(200);
x_bank_id NUMBER;
x_return_status VARCHAR2(200);
x_msg_count NUMBER;
x_msg_data VARCHAR2(200);
p_count NUMBER;
BEGIN
p_init_msg_list := fnd_api.g_true;
p_country_code := 'US';
p_bank_name := 'Bank1';
p_bank_number := '123';
p_alternate_bank_name := 'Alternate Test Bank';
p_short_bank_name := 'B1';
p_description := 'Test Bank Creation API';
ce_bank_pub.create_bank(p_init_msg_list => p_init_msg_list
,p_country_code => p_country_code
,p_bank_name => p_bank_name
,p_bank_number => p_bank_number
,p_alternate_bank_name => p_alternate_bank_name
,p_short_bank_name => p_short_bank_name
,p_description => p_description
,x_bank_id => x_bank_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
dbms_output.put_line('BANK_ID/PARTY_ID = ' || x_bank_id);
dbms_output.put_line('X_RETURN_STATUS = ' || x_return_status);
dbms_output.put_line('X_MSG_COUNT = ' || x_msg_count);
dbms_output.put_line('X_MSG_DATA = ' || x_msg_data);
IF x_msg_count = 1 THEN
dbms_output.put_line('x_msg_data ' || x_msg_data);
ELSIF x_msg_count > 1 THEN
LOOP
p_count := p_count + 1;
x_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next
,fnd_api.g_false);
IF x_msg_data IS NULL THEN
EXIT;
END IF;
dbms_output.put_line('Message' || p_count || ' ---' || x_msg_data);
END LOOP;
END IF;
END;
2.创建银行联系人
-- 创建银行联系人
DECLARE
l_person_rec hz_party_v2pub.person_rec_type;
l_party_rec hz_party_v2pub.party_rec_type;
l_party_id NUMBER;
l_party_number VARCHAR2(30);
l_profile_id NUMBER;
x_return_status VARCHAR2(1);
x_msg_count NUMBER;
x_msg_data VARCHAR2(3000);
BEGIN
IF nvl(fnd_profile.value('HZ_GENERATE_PARTY_NUMBER')
,'Y') = 'N' THEN
l_party_rec.party_number := 'GH9527';
END IF;
l_person_rec.person_pre_name_adjunct := 'SIR';
l_person_rec.person_first_name := 'San';
l_person_rec.person_middle_name := '';
l_person_rec.person_last_name := 'Zhang';
l_person_rec.person_name_suffix := '';
l_person_rec.person_previous_last_name := 'Li';
l_person_rec.person_initials := 'Z';
l_person_rec.known_as := 'Xiao';
l_person_rec.person_name_phonetic := 'zhangsan';
l_person_rec.created_by_module := 'HZ_CPUI';
--l_person_rec.application_id := 200;
l_person_rec.party_rec := l_party_rec;
dbms_output.put_line('Calling the API hz_party_v2pub.create_person');
hz_party_v2pub.create_person(p_init_msg_list => fnd_api.g_true
,p_person_rec => l_person_rec
,x_party_id => l_party_id
,x_party_number => l_party_number
,x_profile_id => l_profile_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
IF x_return_status = fnd_api.g_ret_sts_success THEN
dbms_output.put_line('Creation of Person is Successful');
dbms_output.put_line('Output information ....');
dbms_output.put_line('x_party_id: ' || l_party_id);
dbms_output.put_line('x_party_number: ' || l_party_number);
dbms_output.put_line('x_profile_id: ' || l_profile_id);
dbms_output.put_line('x_return_status: ' || x_return_status);
dbms_output.put_line('x_msg_count: ' || x_msg_count);
dbms_output.put_line('x_msg_data: ' || x_msg_data);
ELSE
dbms_output.put_line('Creation of Person failed:' || x_msg_data);
FOR i IN 1 .. x_msg_count LOOP
x_msg_data := fnd_msg_pub.get(p_msg_index => i
,p_encoded => 'F');
dbms_output.put_line(i || ') ' || x_msg_data);
END LOOP;
END IF;
dbms_output.put_line('Completion of API');
END;
/*x_party_id: 473709
x_party_number: GH9527
x_profile_id: 490830*/
SELECT * FROM ce_banks_v cb WHERE cb.bank_party_id = 473707;
SELECT * FROM hz_parties t WHERE t.party_number = 'GH9527' AND t.party_id = 473709;
SELECT * FROM hz_person_profiles hp WHERE hp.person_profile_id = 490830;
3.将联系人关联到银行
-- 将联系人关联到银行
DECLARE
l_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;
x_org_contact_id NUMBER;
x_party_rel_id NUMBER;
x_party_id NUMBER;
x_party_number VARCHAR2(2000);
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
BEGIN
-- Setting the Context --
mo_global.init('SQLAP');
fnd_global.apps_initialize(user_id => 1013579
,resp_id => 65754
,resp_appl_id => 200);
mo_global.set_policy_context('S'
,7892);
-- Initializing the Mandatory API parameters
l_org_contact_rec.department_code := 'CALL CENTER';
l_org_contact_rec.comments := 'create 473707 contact person';
l_org_contact_rec.department := 'Call Center';
l_org_contact_rec.job_title := 'Chief Financial Officer';
l_org_contact_rec.job_title_code := 'CFO';
l_org_contact_rec.created_by_module := 'HZ_CPUI';
l_org_contact_rec.party_rel_rec.subject_id := 473709; -- contact person id
l_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
l_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
l_org_contact_rec.party_rel_rec.object_id := 473707; -- just bank id
l_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
l_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
l_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
l_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
l_org_contact_rec.party_rel_rec.status := 'A';
dbms_output.put_line('Calling the API hz_party_contact_v2pub.create_org_contact');
hz_party_contact_v2pub.create_org_contact(p_init_msg_list => fnd_api.g_true
,p_org_contact_rec => l_org_contact_rec
,x_org_contact_id => x_org_contact_id
,x_party_rel_id => x_party_rel_id
,x_party_id => x_party_id
,x_party_number => x_party_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
IF x_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
dbms_output.put_line('Creation of Org contact is Successful ');
dbms_output.put_line('Output information ....');
dbms_output.put_line('x_party_rel_id = ' || x_party_rel_id);
dbms_output.put_line('x_org_contact_id = ' || x_org_contact_id);
dbms_output.put_line('x_party_id = ' || x_party_id);
dbms_output.put_line('x_party_number = ' || x_party_number);
ELSE
ROLLBACK;
dbms_output.put_line('Creation of Org Contact failed:' || x_msg_data);
FOR i IN 1 .. x_msg_count LOOP
x_msg_data := fnd_msg_pub.get(p_msg_index => i
,p_encoded => 'F');
dbms_output.put_line(i || ') ' || x_msg_data);
END LOOP;
END IF;
dbms_output.put_line('Completion of API hz_party_contact_v2pub.create_org_contact');
END;
SELECT * FROM hz_parties t WHERE t.party_id = 473710;
SELECT * FROM hz_org_contacts t WHERE t.org_contact_id = 205424;
SELECT * FROM hz_relationships hr WHERE hr.relationship_id = 418370;
SELECT * FROM hz_relationship_types;
4.注册银行联系人角色 |
-- 注册银行联系人角色
DECLARE
l_org_contact_role_rec hz_party_contact_v2pub.org_contact_role_rec_type;
x_org_contact_role_id NUMBER;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
BEGIN
-- Initializing the Mandatory API parameters
l_org_contact_role_rec.role_type := 'BANKING_CONTACT';
l_org_contact_role_rec.org_contact_id := 205424;
l_org_contact_role_rec.created_by_module := 'HZ_CPUI';
l_org_contact_role_rec.appliaction_id := 200;
dbms_output.put_line('Calling the API hz_party_contact_v2pub.create_org_contact');
hz_party_contact_v2pub.create_org_contact_role(p_init_msg_list => fnd_api.g_true
,p_org_contact_role_rec => l_org_contact_role_rec
,x_org_contact_role_id => x_org_contact_role_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
IF x_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
dbms_output.put_line('Creation of Org contact is Successful ');
dbms_output.put_line('Output information ....');
dbms_output.put_line('x_org_contact_role_id = ' || x_org_contact_role_id);
ELSE
dbms_output.put_line('Creation of Org Contact failed:' || x_msg_data);
ROLLBACK;
FOR i IN 1 .. x_msg_count LOOP
x_msg_data := fnd_msg_pub.get(p_msg_index => i
,p_encoded => 'F');
dbms_output.put_line(i || ') ' || x_msg_data);
END LOOP;
END IF;
dbms_output.put_line('Completion of API hz_party_contact_v2pub.create_org_contact');
END;
SELECT * FROM hz_org_contact_roles rol WHERE rol.org_contact_id =206429;--rol.org_contact_role_id = 36720;
5.将关联关系插入关系分配表中
DECLARE
l_assignment_type VARCHAR2(120) := 'BANK';
l_relationship_id NUMBER := 416447;
l_bank_party_id NUMBER := 473707;
BEGIN
INSERT INTO ce_contact_assignments
(contact_assignment_id
,assignment_type
,relationship_id
,bank_party_id
,branch_party_id
,bank_account_id
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by)
VALUES
(ce_contact_assignments_s.nextval
,l_assignment_type
,l_relationship_id
,l_bank_party_id
,NULL
,NULL
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,SYSDATE
,fnd_global.user_id);
dbms_output.put_line('contact_assignment_id :' || ce_contact_assignments_s.currval);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;