深入浅出Oracle之常用接口开发参考(5)

2018-12-17 16:29

Oracle ERP最佳技术实践

l_iface_prof_rec.credit_hold := 'N';

l_iface_prof_rec.org_id := l_iface_rec.org_id;

INSERT INTO ra_customer_profiles_int_all VALUES l_iface_prof_rec;

END;

Doc Ref:

June 25, 2008

Open Inerface最简单例子（仅新增Address和Site Use）（11.5.9环境测试通过）脚本如下：

DECLARE

l_iface_rec ra_customers_interface_all%ROWTYPE; l_iface_prof_rec ra_customer_profiles_int_all%ROWTYPE; l_org_id NUMBER := 81;

BEGIN

--Who

l_iface_rec.last_update_date := SYSDATE; l_iface_rec.last_updated_by := 0;

l_iface_rec.creation_date := SYSDATE; l_iface_rec.created_by := 0; l_iface_rec.last_update_login := -1;

l_iface_prof_rec.last_update_date := SYSDATE; l_iface_prof_rec.last_updated_by := 0;

l_iface_prof_rec.creation_date := SYSDATE; l_iface_prof_rec.created_by := 0; l_iface_prof_rec.last_update_login := -1;

--Customer

SELECT decode(nvl(fnd_profile.VALUE('HZ_GENERATE_PARTY_NUMBER'), 'Y'), 'Y', NULL, 'P0001') INTO l_iface_rec.party_number FROM dual;

SELECT decode(generate_customer_number, 'Y', NULL, 'C0001') INTO l_iface_rec.customer_number FROM ar_system_parameters_all WHERE org_id = l_org_id;

l_iface_rec.insert_update_flag := 'I'; l_iface_rec.validated_flag := NULL;

l_iface_rec.orig_system_party_ref := 'PART0001'; l_iface_rec.orig_system_customer_ref := 'CUST0001';

l_iface_rec.customer_name := 'Customer 0001'; l_iface_rec.customer_status := 'A'; l_iface_rec.customer_type := 'R'; l_iface_rec.person_flag := 'N';

l_iface_rec.org_id := l_org_id;

--Address&Site Use

SELECT decode(nvl(fnd_profile.VALUE('HZ_GENERATE_PARTY_SITE_NUMBER'), 'Y'), 'Y', NULL,

'SITE0001')

INTO l_iface_rec.party_site_number FROM dual;

SELECT decode(auto_site_numbering, 'Y', NULL, 'SUSE0001') INTO l_iface_rec.location

FROM ar_system_parameters_all WHERE org_id = l_org_id;

l_iface_rec.site_use_code := 'BILL_TO'; l_iface_rec.primary_site_use_flag := 'Y';

l_iface_rec.orig_system_address_ref := 'ADDR0001'; l_iface_rec.bill_to_orig_address_ref := NULL;

l_iface_rec.address1 := 'Address1'; l_iface_rec.country := 'CN';

--Can not update site use

IF l_iface_rec.insert_update_flag = 'U' THEN l_iface_rec.site_use_code := NULL; l_iface_rec.primary_site_use_flag := NULL; END IF;

接口开发参考

AR Customers客户【Open Interface】File Ref: 37325617.doc (v. DRAFT 1A )

Company Confidential - For internal use only

16 of 164

Doc Ref: Oracle ERP最佳技术实践

June 25, 2008 INSERT INTO ra_customers_interface_all VALUES l_iface_rec;

--Profile

l_iface_prof_rec.insert_update_flag := 'I';

l_iface_prof_rec.orig_system_customer_ref := l_iface_rec.orig_system_customer_ref; l_iface_prof_rec.orig_system_address_ref := l_iface_rec.orig_system_address_ref; l_iface_prof_rec.customer_profile_class_name := 'DEFAULT'; l_iface_prof_rec.credit_hold := 'N';

l_iface_prof_rec.org_id := l_iface_rec.org_id;

INSERT INTO ra_customer_profiles_int_all VALUES l_iface_prof_rec;

END;

Open Inerface最简单例子（同时新增客户和Site）（11.5.9环境测试通过）脚本如下：

DECLARE

l_iface_rec ra_customers_interface_all%ROWTYPE; l_iface_prof_rec ra_customer_profiles_int_all%ROWTYPE; l_org_id NUMBER := 81;

BEGIN

--Who

l_iface_rec.last_update_date := SYSDATE; l_iface_rec.last_updated_by := 0;

l_iface_rec.creation_date := SYSDATE; l_iface_rec.created_by := 0; l_iface_rec.last_update_login := -1;

l_iface_prof_rec.last_update_date := SYSDATE; l_iface_prof_rec.last_updated_by := 0;

l_iface_prof_rec.creation_date := SYSDATE; l_iface_prof_rec.created_by := 0; l_iface_prof_rec.last_update_login := -1;

--Customer

SELECT decode(nvl(fnd_profile.VALUE('HZ_GENERATE_PARTY_NUMBER'), 'Y'), 'Y', NULL, 'P0002') INTO l_iface_rec.party_number FROM dual;

SELECT decode(generate_customer_number, 'Y', NULL, 'C0002') INTO l_iface_rec.customer_number FROM ar_system_parameters_all WHERE org_id = l_org_id;

l_iface_rec.insert_update_flag := 'I'; l_iface_rec.validated_flag := NULL;

l_iface_rec.orig_system_party_ref := 'PART0002'; l_iface_rec.orig_system_customer_ref := 'CUST0002';

l_iface_rec.customer_name := 'Customer 0002'; l_iface_rec.customer_status := 'A'; l_iface_rec.customer_type := 'R'; l_iface_rec.person_flag := 'N';

l_iface_rec.org_id := l_org_id;

--Address&Site Use

SELECT decode(nvl(fnd_profile.VALUE('HZ_GENERATE_PARTY_SITE_NUMBER'), 'Y'), 'Y', NULL, 'SITE0001') INTO l_iface_rec.party_site_number FROM dual;

SELECT decode(auto_site_numbering, 'Y', NULL, 'SUSE0002') INTO l_iface_rec.location

FROM ar_system_parameters_all WHERE org_id = l_org_id;

l_iface_rec.site_use_code := 'BILL_TO'; l_iface_rec.primary_site_use_flag := 'Y';

l_iface_rec.orig_system_address_ref := 'ADDR0002'; l_iface_rec.bill_to_orig_address_ref := NULL;

l_iface_rec.address1 := 'Address1'; l_iface_rec.country := 'CN';

--Can not update site use

IF l_iface_rec.insert_update_flag = 'U' THEN l_iface_rec.site_use_code := NULL; l_iface_rec.primary_site_use_flag := NULL; 接口开发参考

File Ref: 37325617.doc (v. DRAFT 1A )

AR Customers客户【Open Interface】 17 of 164

Company Confidential - For internal use only

Doc Ref: Oracle ERP最佳技术实践

June 25, 2008 END IF;

INSERT INTO ra_customers_interface_all VALUES l_iface_rec;

--Profile

l_iface_prof_rec.insert_update_flag := 'I';

l_iface_prof_rec.orig_system_customer_ref := l_iface_rec.orig_system_customer_ref; l_iface_prof_rec.customer_profile_class_name := 'DEFAULT'; l_iface_prof_rec.credit_hold := 'N';

l_iface_prof_rec.org_id := l_iface_rec.org_id;

--Custome Profile

l_iface_prof_rec.orig_system_address_ref := NULL;

INSERT INTO ra_customer_profiles_int_all VALUES l_iface_prof_rec;

--Site Use Profile

l_iface_prof_rec.orig_system_address_ref := l_iface_rec.orig_system_address_ref; INSERT INTO ra_customer_profiles_int_all VALUES l_iface_prof_rec;

END;

Open Inerface最简单例子（新增另一OU Site）（11.5.9环境测试通过）脚本如下：

DECLARE

l_iface_rec ra_customers_interface_all%ROWTYPE; l_iface_prof_rec ra_customer_profiles_int_all%ROWTYPE; l_org_id NUMBER := 88;

BEGIN

--Who

l_iface_rec.last_update_date := SYSDATE; l_iface_rec.last_updated_by := 0;

l_iface_rec.creation_date := SYSDATE; l_iface_rec.created_by := 0; l_iface_rec.last_update_login := -1;

l_iface_prof_rec.last_update_date := SYSDATE; l_iface_prof_rec.last_updated_by := 0;

l_iface_prof_rec.creation_date := SYSDATE; l_iface_prof_rec.created_by := 0; l_iface_prof_rec.last_update_login := -1;

--Customer

SELECT decode(nvl(fnd_profile.VALUE('HZ_GENERATE_PARTY_NUMBER'), 'Y'), 'Y', NULL, 'P0002') INTO l_iface_rec.party_number FROM dual;

SELECT decode(generate_customer_number, 'Y', NULL, 'C0002') INTO l_iface_rec.customer_number FROM ar_system_parameters_all WHERE org_id = l_org_id;

l_iface_rec.insert_update_flag := 'I'; l_iface_rec.validated_flag := NULL;

l_iface_rec.orig_system_party_ref := 'PART0002'; l_iface_rec.orig_system_customer_ref := 'CUST0002';

l_iface_rec.customer_name := 'Customer 0002'; l_iface_rec.customer_status := 'A'; l_iface_rec.customer_type := 'R'; l_iface_rec.person_flag := 'N';

l_iface_rec.org_id := l_org_id;

--Address&Site Use

SELECT decode(nvl(fnd_profile.VALUE('HZ_GENERATE_PARTY_SITE_NUMBER'), 'Y'), 'Y', NULL, 'SITE0001') INTO l_iface_rec.party_site_number FROM dual;

SELECT decode(auto_site_numbering, 'Y', NULL, 'SUSE0002') INTO l_iface_rec.location

FROM ar_system_parameters_all WHERE org_id = l_org_id;

l_iface_rec.site_use_code := 'BILL_TO'; l_iface_rec.primary_site_use_flag := 'Y';

l_iface_rec.orig_system_address_ref := 'ADDR0002'; l_iface_rec.bill_to_orig_address_ref := NULL; 接口开发参考

File Ref: 37325617.doc (v. DRAFT 1A )

AR Customers客户【Open Interface】 18 of 164

Company Confidential - For internal use only

Doc Ref: Oracle ERP最佳技术实践

June 25, 2008 l_iface_rec.address1 := 'Address1'; l_iface_rec.country := 'CN';

--Can not update site use

IF l_iface_rec.insert_update_flag = 'U' THEN l_iface_rec.site_use_code := NULL; l_iface_rec.primary_site_use_flag := NULL; END IF;

INSERT INTO ra_customers_interface_all VALUES l_iface_rec;

--Profile

l_iface_prof_rec.insert_update_flag := 'I';

l_iface_prof_rec.orig_system_customer_ref := l_iface_rec.orig_system_customer_ref; l_iface_prof_rec.customer_profile_class_name := 'DEFAULT'; l_iface_prof_rec.credit_hold := 'N';

l_iface_prof_rec.org_id := l_iface_rec.org_id;

--Site Use Profile

l_iface_prof_rec.orig_system_address_ref := l_iface_rec.orig_system_address_ref; INSERT INTO ra_customer_profiles_int_all VALUES l_iface_prof_rec;

END;

Open Inerface最简单例子（新增客户、Site、联系人电话）（11.5.9环境测试通过）脚本如下：

DECLARE

l_iface_rec ra_customers_interface_all%ROWTYPE; l_iface_prof_rec ra_customer_profiles_int_all%ROWTYPE; l_iface_phone_rec ra_contact_phones_int_all%ROWTYPE; l_org_id NUMBER := 81;

BEGIN

--Who

l_iface_rec.last_update_date := SYSDATE; l_iface_rec.last_updated_by := 0;

l_iface_rec.creation_date := SYSDATE; l_iface_rec.created_by := 0; l_iface_rec.last_update_login := -1;

l_iface_prof_rec.last_update_date := SYSDATE; l_iface_prof_rec.last_updated_by := 0;

l_iface_prof_rec.creation_date := SYSDATE; l_iface_prof_rec.created_by := 0; l_iface_prof_rec.last_update_login := -1;

l_iface_phone_rec.last_update_date := SYSDATE; l_iface_phone_rec.last_updated_by := 0;

l_iface_phone_rec.creation_date := SYSDATE; l_iface_phone_rec.created_by := 0; l_iface_phone_rec.last_update_login := -1;

--Customer

SELECT decode(nvl(fnd_profile.VALUE('HZ_GENERATE_PARTY_NUMBER'), 'Y'), 'Y', NULL, 'PART0004') INTO l_iface_rec.party_number FROM dual;

SELECT decode(generate_customer_number, 'Y', NULL, 'CUST0004') INTO l_iface_rec.customer_number FROM ar_system_parameters_all WHERE org_id = l_org_id;

l_iface_rec.insert_update_flag := 'I'; l_iface_rec.validated_flag := NULL;

l_iface_rec.orig_system_party_ref := 'PART0004'; l_iface_rec.orig_system_customer_ref := 'CUST0004';

l_iface_rec.customer_name := 'Customer 0004'; l_iface_rec.customer_status := 'A'; l_iface_rec.customer_type := 'R'; l_iface_rec.person_flag := 'N';

l_iface_rec.org_id := l_org_id;

--Address&Site Use

SELECT decode(nvl(fnd_profile.VALUE('HZ_GENERATE_PARTY_SITE_NUMBER'), 'Y'), 'Y', NULL, 'SITE0004') INTO l_iface_rec.party_site_number 接口开发参考

File Ref: 37325617.doc (v. DRAFT 1A )

AR Customers客户【Open Interface】 19 of 164

Company Confidential - For internal use only

Doc Ref: Oracle ERP最佳技术实践

June 25, 2008 FROM dual;

SELECT decode(auto_site_numbering, 'Y', NULL, 'SUSE0004') INTO l_iface_rec.location

FROM ar_system_parameters_all WHERE org_id = l_org_id;

l_iface_rec.site_use_code := 'BILL_TO'; l_iface_rec.primary_site_use_flag := 'Y';

l_iface_rec.orig_system_address_ref := 'ADDR0004'; l_iface_rec.bill_to_orig_address_ref := NULL;

l_iface_rec.address1 := 'Address1'; l_iface_rec.country := 'CN';

--Can not update site use

IF l_iface_rec.insert_update_flag = 'U' THEN l_iface_rec.site_use_code := NULL; l_iface_rec.primary_site_use_flag := NULL; END IF;

INSERT INTO ra_customers_interface_all VALUES l_iface_rec;

--Profile

l_iface_prof_rec.insert_update_flag := 'I';

l_iface_prof_rec.orig_system_customer_ref := l_iface_rec.orig_system_customer_ref; l_iface_prof_rec.customer_profile_class_name := 'DEFAULT'; l_iface_prof_rec.credit_hold := 'N';

l_iface_prof_rec.org_id := l_iface_rec.org_id;

--Custome Profile

l_iface_prof_rec.orig_system_address_ref := NULL;

INSERT INTO ra_customer_profiles_int_all VALUES l_iface_prof_rec;

--Site Use Profile

l_iface_prof_rec.orig_system_address_ref := l_iface_rec.orig_system_address_ref; INSERT INTO ra_customer_profiles_int_all VALUES l_iface_prof_rec;

--Contact

l_iface_phone_rec.insert_update_flag := 'I';

l_iface_phone_rec.orig_system_customer_ref := l_iface_rec.orig_system_customer_ref; l_iface_phone_rec.orig_system_address_ref := l_iface_rec.orig_system_address_ref; l_iface_phone_rec.orig_system_contact_ref := 'CONT0004'; l_iface_phone_rec.orig_system_telephone_ref := 'TELE0004'; l_iface_phone_rec.contact_last_name := 'Huang'; l_iface_phone_rec.telephone := '38953345';

l_iface_phone_rec.telephone_type := 'GEN'; --Telephone l_iface_phone_rec.org_id := l_iface_rec.org_id;

INSERT INTO ra_contact_phones_int_all VALUES l_iface_phone_rec;

END;

4.4. 已知问题

字段关系

新建Customer时，ra_customer_profiles_int_all需要有记录。

orig_system_party_ref相同的话，Customer_Number不同，可以在一个Party下建多个Customer。

ship to的bill_to_orig_address_ref可用bill to的orig_system_address_ref，这样可以自动关联。

insert_update_flag

ra_customers_interface_all表的insert_update_flag判断如下：

FUNCTION get_cust_iface_flag(p_cust_ref IN VARCHAR2, p_addr_ref IN VARCHAR2, p_site_use_code IN VARCHAR2) 接口开发参考

File Ref: 37325617.doc (v. DRAFT 1A )

AR Customers客户【Open Interface】 20 of 164

Company Confidential - For internal use only

共8页:

深入浅出Oracle之常用接口开发参考(5).doc 将本文的Word文档下载到电脑下载失败或者文档不完整，请联系客服人员解决！

下载这篇word文档