Oracle_11g_入门教程

1目录OracleERP系统开发工具及开发过程3开放式接口/应用编程接口(API)5如何在系统中注册表单4如何在系统中注册报表6其它2OracleERP系统结构1.三层逻辑体系结构UserInterfaceApplicationlogicDatabaselogic客户端应用服务器数据库服务器客户端Useawebbrowser–Netscape–InternetExplorerOn–Windows–MacintoshTodisplay–Java–HTML应用服务器层MobileJSPFormsReportsDiscovererBC4JServletEngineWebListenerLoginServerIntegrationPortalApplicationServer9i1.0.2.2.2数据库服务器层:8i9i9iR2StoredProceduresPL/SQL运行原理应用服务器数据库服务器WebApplicationsDictionaryListenerDataBrowserPL/SQLGateway客户端WorkflowJavacodedownloadedasneededAppletcachedonclientFormsNetworkTrafficBrowserrunningFormsjavaappletDataServerFormsServerLANWANDial-up系统灵活性历史记录和诊断、检查–用户可以看到当前记录来源于哪个表(视图)、哪个字段和最后一查询的SQL语句等其它的信息。这对客户化开发有极大的帮助。Custom.pll–开发人员只需客户化此文件，而不需要修改Oracle的标准程序来达到客户化的目的。用户自已在FORM中定义字段，来保存其它的附加信息。弹性域–用户自已在FORM中定义字段，来保存其它的附加信息。附件–对每一记录能附上文件、文本信息或WebPage的大容量信息.修改Forms界面的文件功能或Show隐藏字段–用户能显示自已想要显示的字段，并能调整顺序Export–用户能用export功能从Form中export数据到文件中。–通常用来转换到Excel文件中。2.Oracle开发工具及开发过程-Form(SeeAVIFile)-Report(SeeAVIFile)-Discoverer(SeeAVIFile)-Workflow(SeeFollowing)-PL/SQL-FSG(FinancialStatementGenerator)Oracle开发工具及开发过程-Form(SeeAVIFile)-Report(SeeAVIFile)-Discoverer(SeeAVIFile)-Workflow-PL/SQL-FSG(FinancialStatementGenerator)实例：3.如何在系统中注册表单1.编译表单2.登记表单3.注册功能4.挂上菜单(子菜单-菜单)5.分配菜单到职责6.分配职责到用户CompilerForms1..fmb文件上传到Server2.Telnet连接到Server，LoginIn3.切换到$AU_TOP/forms/USCompilerForm4.CompilerFormf60genFormNameapps/appspasswordf60genModule=BD_TEST.fmbuserid=apps/appsoutput_file=/u21/oracle/visappl/po/11.5.0/forms/US/BD_TEST.fmxCompilerForm4.CompilerFormf60genFormNameapps/appspasswordCompilerForm5.Compiler成功,Create.fmx文件用户权限控制结构项目录入(可操作)其它项目管理(子菜单)现有量(可操作)项目成本查询(不可操作)库存管理(菜单)请求组库存管理(职责)采购管理(职责)销售管理用户1,23452、登记表单3、注册功能4.1、挂上菜单(子菜单)4.2、挂上菜单(子系统菜单)4.3、挂上菜单(系统菜单)5、分配菜单到职责6、分配职责到用户客户化表单的界面表单的数据来源(数据表)表单的数据来源(字段)Oracle开发工具及开发过程-Form(SeeAVIFile)-Report(SeeAVIFile)-Discoverer(SeeAVIFile)-Workflow-PL/SQL-FSG(FinancialStatementGenerator)4.如何在系统中注册报表1.注册可执行并发程序2.注册并发程序3.挂上请求组1、注册可执行并发程序2、注册并发程序3、挂上请求组运行报表报表结果Oracle开发工具及开发过程-Form(SeeAVIFile)-Report(SeeAVIFile)-Discoverer(SeeAVIFile)-Workflow-PL/SQL-FSG(FinancialStatementGenerator)Oracle开发工具及开发过程-Form(SeeAVIFile)-Report(SeeAVIFile)-Discoverer(SeeAVIFile)-Workflow(SeeFollowing)-PL/SQL-FSG(FinancialStatementGenerator)Workflow:系统中的Workflow:Oracle开发工具及开发过程-Form(SeeAVIFile)-Report(SeeAVIFile)-Discoverer(SeeAVIFile)-Workflow(SeeFollowing)-PL/SQL-FSG(FinancialStatementGenerator)5.Oracle开放式接口/应用编程接口(API)开放式接口、应用编程接口(API)原理ExcelFileTableOpenInterfaceTablePackageAPIBuildinProcessOracleERPOracleERP系统系统接口数据表和应用编程接口(API)程序ModuleFunctionInterfaceTable/APICommentsAPSuppliersPO_VENDORSPO_VENDOR_SITES_ALLPO_VEDNRO_CONTACTSNoInterface/APIARCustomersRA_CUSTOMERS_INTERFACE_ALLRA_CUSTOMER_PROFILES_INT_ALLRA_CUSTOMER_BANKS_INT_ALLOpeninterfacetableARReceiptsAR_RECEIPT_API_PUBARP_PROCESS_MISC_RECEIPTS.INSERT_MISC_RECEIPTAPIpackagesGLJournalsGL_INTERFACEOpeninterfacetable6.Oracle使用的其它相关工具-ADI(ApplicationDesktopIntegrator)-Dataload()-Sqlloader-Fndload-Toad-SQLDeveloper-etc…Oracle数据字典全部数据字典在如下三个组-DBA_OBJECTS-DBA_TABLES-DBA_VIEWS-ALL_OBJECTS-ALL_TABLES-ALL_VIEWS-USER_OBJECTS-USER_TABLES-USER_VIEWS系统登录用户由系统来定义、分配和控制数据库用户只用APPSOracle系统命名的标准规则TypeRulesExampleTableEnglishPlural,startwithmodulenameAP_INVOICESViewtablename_VAP_INVOICES_VUniqueIndextablename_UnAP_INVOICES_U1Non-UniqueIndextablename_NnAP_INVOICES_N1Sequencegeneratortablename_SAP_INVOICES_STriggertable_name_eventAR_CUSTOMER_PROFILES_BRUOracle系统－数据库触发器CREATEORREPLACETRIGGERar_customer_profiles_bru/*$Header:arplt26.sql70.496/10/2915:04:58portingship$*/BEFOREUPDATEONAR.AR_CUSTOMER_PROFILESFOREACHROWDECLARECreditHoldvarchar2(1):=null;RiskCodevarchar2(30):=null;CreditRatingvarchar2(30):=null;ChangesMadevarchar2(1):='N';BEGINIF((:new.credit_hold!=:old.credit_hold)OR(:new.credit_holdisnulland:old.credit_holdisnotnull)OR(:new.credit_holdisnotnulland:old.credit_holdisnull))THENCreditHold:=:old.credit_hold;/*IFFIELDVALUEOFcredit_holdISCHANGEDTHENSET*//*THEVALUEOFTHISVARIABLETOY*/ChangesMade:='Y';ENDIF;IF((:new.risk_code!=:old.risk_code)OR(:new.risk_codeisnulland:old.risk_codeisnotnull)OR(:new.risk_codeisnotnulland:old.risk_codeisnull))THENRiskCode:=:old.risk_code;ChangesMade:='Y';ENDIF;IF((:new.credit_rating!=:old.credit_rating)OR(:new.credit_ratingisnulland:old.credit_ratingisnotnull)OR(:new.credit_ratingisnotnulland:old.credit_ratingisnull))THENCreditRating:=:old.credit_rating;ChangesMade:='Y';ENDIF;IFChangesMade='Y'THENINSERTINTOar_credit_histories(credit_history_id,last_updated_by,last_update_date,created_by,creation_date,customer_id,on_hold,hold_date,credit_rating,risk_code,site_use_id)VALUES(ar_credit_histories_s.nextval,:old.last_updated_by,:old.last_update_date,:old.last_updated_by,:old.last_update_date,:old.customer_id,CreditHold,decode(CreditHold,'Y',:old.last_update_date,null),CreditRating,RiskCode,:old.site_use_id);ENDIF;END;OracleApplication–StoredProcedureClientServerOracleS1S2S3S4S1S2S3S4OracleAbc(S1,S2,S3,S4)AbcQ&A