O pen Interface
What is the difference between data migration and interfaces?
Data Migration is a one-time activity however interfaces are ongoing processes that run regularly. Hence error handling must be well thought when designing interfaces.
Best practices for interfaces?
1. Usually an interface must have well defined error reporting mechanism.
2. Data errors must be fixed at the source once the transaction gets rejected.
3. If your support team is often modifying transactions in interface tables using sql, then your interface design is flawed.
4. If you log support issues with any interface on a relatively regular basis, then its time to re-visit your design.
5. Interfaces must be designed in a manner such that, once the original errors are fixed, those transactions must get re-processed during the next run (or on demand)
6. If possible an interface mechanism/infrastructure must be in place, so that all interfaces are written in similar manner.
7. There must not be any hard coding for mapping from source system data to EBS. You can either use oracle's lookup screen to define mappings, or use a custom mapping screen.
8. In some cases you will have a generic screen which facilitates one-to-one, one-to-many, many-to-one, many-to-many mappings.
9. There must exist a simple mechanism to identify the duplicate processing of any transaction must exist. One way this can either be achieved is by making your source system specify a unique identifier for each record.
10. Do not store references to rowid in any part of your interfaces.
11. Think upfront, design and write your interface in such a manner that once it goes to production, you never receive an email to fix any stuck records. If at all you do receive data fixing requests, then ensure that interface program is changed in a manner it gets handled without programmers intervention in future.
12. Have proper debugging. You could add a parameter for debug flag, so that debug messages aren’t generated unnecessarily. Surely, don't forget to use FND Logging, which is delivered out of the box by Oracle.
13. Make the concurrent program end with warning in case of errors. This can be done by passing retcode=1. Use the out-of-the-box concurrent program notification facility can be used to inform. end users of the errors encountered during processing.
By doing so, you can make the monitoring automatic, i.e. when submitting/scheduling interface concurrent program, you can attach a workflow role.
What tools can I use for point to point interfaces?
Point to Point interface methodology is not ideal for a large Organization. However to keep the costs low, when number of interfaces are very low, then Point to Point Interface approach could be justified. However please note that Organizations do grow in size , specially after implementing Oracle ERP. Hence Point 2 Point Interfaces can not be a long term strategy.
Please find p2p interface approach listed:-
1. File tables..
--You can map the structure of an ASCII file to table structure.
2. XML Gateway
--You can use XML Gateway and XML Message designer if you have too many interfaces having XML source. Although keep in mind that there may not be any future for XML Gateway in Fusion applications.
--Avoid for interfaces as error reporting is not user friendly. Use this for data migration as SQL*Loader is very efficient.
4. DB link for intra company different Oracle systems
--DBA's often raise questions regarding security of database links. However security concerns can be minimized by making them non-public and make them connect to not specific schemas with minimum privileges.
5. Java concurrent program with jdbc
If your source system is in a database like SQL*Server, or DB2 or any other database which supports JDBC, this approach could be ideal.
The source system can create a specialized schema and expose the necessary views or tables/synonyms, such that you can pull the data from that system, load it into your system using API. All this can happen seamlessly, all within one program.
6. Java concurrent program with FTP, csv parser
All the above steps can be done within one single program.
Usual bad practices:-
1. Each developer writing their own mapping tables and screens.
2. Hard coding in interface
3. No thoughtful process for Error Reporting and Error Correction(deviation from fix at source philosophy)
4. Calling SQL*Plus sessions from Host programs, without due considerations for APPS Password Security
5. No debugging
6. Each interface program using its own File Dequeue mechanism.
Step1: Loader the source data to the Oracle Interface Table through the specific loader programs. For example, converting the internal purchase request to the internal sales order need to run ‘ 创建内部销售订单流程 ’, 以便将内部采购申请发送并插入订单管理系统的接口表 . 如果是从某些电子表格如 EXCEL 等导入，则需要先使用专门的 SQL*LOAD 工具将数据格式转换后直接插入接口表。
Step 2: 系统将存在于接口表中的数据导入正式的业务数据表，如 EBS 订单管理模块的 “ 订单导入 ” ，库存管理模块的 “ 导入 Item” 等等。在从接口表导入 “ 正式表 ” 或数据装入 “ 接口表 ” 过程中因数据校验而产生的错误或失败信息，如系统提供专门的业务管理视图，则可以在其中进行查看、更正、重新提交，如 EBS 的 “ 订单导入更正 ” 窗口等。如系统未提供管理视图，则可以在并发程序请求的 “ 输出 ” 文件中查看结果。下图 37 所示 “ 应付款管理模块费用报表类发票导入流程图 ” 是一个典型的应用过程示例：
Private APIs : Private API's are one which Oracle normally using internal, development purpose only. Details are not provided to anyone outside of the immediate development environment, nor are they intended for use by anyone outside of the e-Business Suite development environment.
Public APIs : These are designed for customers and Oracle consultants to integrate non-Oracle systems into Oracle e-Business Suite or to extend the functionality of the base products. Oracle does not support public APIs unless they are published in a reference manual.
Public, published APIs : These are one which Oracle guaranteed to remain valid from release to release, and patches will not alter the API behaviour. Public, published APIs are supported by Oracle to the same extent as released software.
Is there any way find out whether a standard API is PUBLIC or not in Oracle Application?
Yes, there is way, what you have do ,once you are able to find the information for API from irep , the next you have to find the file name and then you need to pull all information from specification header to know which one is public.
Take a simple case, you need to find API FND_USER_PKG which is defined in file AFSCUSRB.pls
logon to Unix box, and release this sort of command
grep -i public $FND_TOP/patch/115/sql/AFSCUSRB.pls
Based on the above result one can determine whether API is PUBLIC or not.
Simple example for checking AR Public APIs for finding the status
grep -i public $AR_TOP/patch/115/sql/ARXPRELB.pls
grep -i public $AR_TOP/patch/115/sql/ARXPRELS.pls
grep -i public $AR_TOP/patch/115/sql/ARXPRECS.pls
grep -i public $AR_TOP/patch/115/sql/ARXPRECB.pls
Important to Note:
For non-published APIs, Oracle expressly does not provide any guarantees regarding consistency of naming, usage, or behavior. of any API (public or private) between releases.
It Might be possible that a patch could alter any characteristic of any non-published e-Business Suite API.
Where are APIs located?
For Oracle release 10.7, the APIs are located in the operating system directories such as:
For Oracle release 11 and release 11i, the APIs are located in the operating system directories:
Where xxx represents the release 110 or 115.
Is there any tracking mechanism for API versions in different Applications releases?
As confirmed by some time back by Oracle support team , there is no such database object in Oracle Applications that keep such kind of information.
All APIs are owned and managed by different product groups within Oracle.
Normally each release comes with either product update notes, or and "About" note. You would need to review these documents for each E-Business Product.
The most comprehensive are the family pack "About" notes, as they in turn reference each individual product "About" note, which lists things like "Changes".
Oracle General Ledger
1. Budget Upload (Refer to Uploading Budgets section of the Budgets chapter of the Oracle General Ledger User Guide)
2. Importing Journals (Refer to Journal Import section of the Journals chapter of the Oracle General Ledger User Guide)
3. Loading Daily Rates (Refer to Loading Daily Rates section of the Multi?Currency chapter of the Oracle General Ledger User Guide)
1 Credit Card Transaction Interface Table (Refer to Payable Credit Card Transactions Table appendix in the Oracle Payables User Guide)
2 Invoice Import Interface. This interface is no longer supported for importing invoices. Use the Payables Open Interface instead. If you want to review column descriptions for the table, refer to the Invoice Import Interface Tables appendix in the Oracle Payables User?s Guide)
3 Payables Open Interface (Refer to Payables Open Interface Tables appendix in the Oracle Payables User Guide)
4 Purchase Order Matching (Refer to Purchase Order Matching Database Tables appendix in the Oracle Payables User Guide)
AutoInvoice (Refer to Importing Invoice Information Using AutoInvoice in the Oracle Receivables User Guide)
2 AutoLockbox (Refer to Using AutoLockbox in the Oracle Receivables User Guide)
3 Customer Interface (Refer to Customer Interface in the Oracle Receivables User Guide)
4 Sales Tax Rate Interface (Refer to Importing Address Validation Data and Sales Tax Rates in the Oracle Receivables Tax Manual)
5 Tax Vendor Extension (Refer to Implementing the Tax Vendor Extension in the Oracle Receivables Tax Manual)
ACE Interface (Refer to About the ACE Interface section of the Oracle Assets User Guide)
2 Budget Open Interface (Refer to Budget Open Interface section of the Oracle Assets User Guide)
3 Mass Additions Interface (Refer to About the Mass Additions Interface section of the Oracle Assets User Guide)
4 Production Interface (Refer to Using the Production Interface section of the Oracle Assets User Guide)
5 Physical Inventory (Refer to Loading Physical Inventory Data section of the Oracle Assets User Guide)
Oracle Cash Management
Bank Statement Open Interface (Refer to Bank Statement Open Interface section of the Oracle Cash Management User Guide)
2 Forecasting Open Interface (Refer to Forecasting Open Interface section of the Oracle Cash Management User Guide)
3 Reconciliation Open Interface (Refer to Reconciliation Open Interface section of the Oracle Cash Management User Guide)
1 Requisitions Open Interface (Refer to Purchasing in the Oracle Manufacturing and Distributions Open Interfaces Manual)
2 Purchasing Documents Open Interface (Refer to Purchasing in the Oracle Manufacturing and Distributions Open Interfaces Manual)
3 Receiving Open Interface (Refer to Purchasing in the Oracle Manufacturing and Distributions Open Interfaces Manual)
Customer Item Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
2 Open Item Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
3 Open Replenishment Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
4 Open Transaction Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
5 Cycle Count Open Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
6 Reservations Open Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
7 Move Orders Open Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
1 Activity Management Gateway (Refer to Activity Management Gateway Technical Reference Manual)
2 Client Extensions (Refer to Client Extensions in the Oracle Projects User Guide)
3 Transaction Import (Refer to Intergrating Oracle Projects with non?Oracle Products in the Oracle Projects User Guide)
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17087603/viewspace-687602/，如需转载，请注明出处，否则将追究法律责任。