Mutexes in Oracle Database2016-03-16 23:45:25
Triggers in Oracle Forms2012-11-02 14:53:49Triggers in Oracle FormsTriggers are blocks of PL/SQL code that are written to perform tasks when a specific event occurs within an application. In effect, an Oracle Forms trigger is an event-handler
Triggers in Oracle Forms
Triggers are blocks of PL/SQL code that are written to perform tasks when a specific event occurs within an application. In effect, an Oracle Forms trigger is an event-handler written in PL/SQL to augment (or occasionally replace) the default processing behavior. Every trigger has a name, and contains one or more PL/SQL statements. A trigger encapsulates PL/SQL code so that it can be associated with an event and executed and maintained as a distinct object.
Block Processing Triggers:
Block processing triggers fire in response to events related to record management in a block.
- When-Create-Record Perform an action whenever Oracle Forms attempts to create a new record in a block.
- When-Clear-Block Perform an action whenever Oracle Forms flushes the current block; that is, removes all records from the block.
- When-Database-Record Perform an action whenever Oracle Forms changes a record’s status to Insert or Update, thus indicating that the record should be processed by the next COMMIT_FORM operation.
Interface Event Triggers:
Interface event triggers fire in response to events that occur in the form interface. Some of these triggers, such as When-Button-Pressed, fire only in response to operator input or manipulation. Others, like When-Window-Activated, can fire in response to both operator input and programmatic control.
- When-Button-Pressed Initiate an action when an operator selects a button, either with the mouse or through keyboard selection.
- When-Checkbox-Changed Initiate an action when the operator toggles the state of a check box, either with the mouse or through keyboard selection.
- When-Image-Activated Initiate an action whenever the operator double-clicks an image item.
- When-Image-Pressed Initiate an action whenever an operator clicks on an image item.
- When-Radio-Changed Initiate an action when an operator changes the current radio button selected in a radio group item.
- When-Window-Activated Initiate an action whenever an operator or the application activates a window.
- When-Window-Closed Initiate an action whenever an operator closes a window with the window manager’s Close command.
- When-Window-Deactivated Initiate an action whenever a window is deactivated as a result of another window becoming the active window.
Oracle Forms generates master/detail triggers automatically when a master/detail relation is defined between blocks. The default master/detail triggers enforce coordination between records in a detail block and the master record in a master block. Unless developing custom block-coordination schemes, you do not need to define these triggers.
- On-Check-Delete-Master Fires when Oracle Forms attempts to delete a record in a block that is a master block in a master/detail relation.
- On-Clear-Details Fires when Oracle Forms needs to clear records in a block that is a detail block in a master/detail relation because those records no longer correspond to the current record in the master block.
- On-Populate-Details Fires when Oracle Forms needs to fetch records into a block that is the detail block in a master/detail relation so that detail records are synchronized with the current record in the master block.
Oracle Forms automatically issues appropriate error and informational messages in response to runtime events. Message handling triggers fire in response to these default messaging events.
- On-Error Replace a default error message with a custom error message, or to trap and recover from an error.
- On-Message To trap and respond to a message; for example, to replace a default message issued by Oracle Forms with a custom message.
Validation triggers fire when Oracle Forms validates data in an item or record. Oracle Forms performs validation checks during navigation that occurs in response to operator input, programmatic control, or default processing, such as a Commit operation.
Navigational triggers fire in response to navigational events. Navigational triggers can be further sub-divided into two categories: Pre- and Post- triggers, and When-New-Instance triggers. Pre- and Post- Triggers fire as Oracle Forms navigates internally through different levels of the object hierarchy. When-New-Instance-Triggers fire at the end of a navigational sequence that places the input focus on a different item.
- Pre-Form Perform an action just before Oracle Forms navigates to the form from “outside” the form, such as at form startup.
- Pre-Block Perform an action before Oracle Forms navigates to the block level from the form level.
- Pre-Record Perform an action before Oracle Forms navigates to the record level from the block level.
- Pre-Text-Item Perform an action before Oracle Forms navigates to a text item from the record level.
- Post-Text-Item Manipulate an item when Oracle Forms leaves a text item and navigates to the record level.
- Post-Record Manipulate a record when Oracle Forms leaves a record and navigates to the block level.
- Post-Block Manipulate the current record when Oracle Forms leaves a block and navigates to the form level.
- Post-Form Perform an action before Oracle Forms navigates to “outside” the form, such as when exiting the form.
- When-New-Form-Instance Perform an action at form start-up. (Occurs after the Pre-Form trigger fires).
- When-New-Block-Instance Perform an action immediately after the input focus moves to an item in a block other than the block that previously had input focus.
- When-New-Record-Instance Perform an action immediately after the input focus moves to an item in a different record.
- When-New-Item-Instance Perform an action immediately after the input focus moves to a different item.
Transactional triggers fire in response to a wide variety of events that occur as a form interacts with the data source.
Query-time triggers fire just before and just after the operator or the application executes a query in a block.
- Pre-Query Validate the current query criteria or provide additional query criteria programmatically, just before sending the SELECT statement to the database.
- Post-Query Perform an action after fetching a record, such as looking up values in other tables based on a value in the current record. Fires once for each record fetched into the block.
AutoInvoice in Oracle Apps R122012-06-26 22:55:30AutoInvoice in Oracle Apps R12 AutoInvoice is a powerful, flexible tool you can use to import and validate transaction data from other financial systems and create invoices, debit memos, credit m
AutoInvoice in Oracle Apps R12AutoInvoice is a powerful, flexible tool you can use to import and validate transaction data from other financial systems and create invoices, debit memos, credit memos, and on–account credits in Oracle Receivables. You use a custom feeder program to transfers transaction data from an external system into the AutoInvoice interface tables. AutoInvoice then selects data from the interface tables and creates transaction in Receivables. Receivables rejects transactions with invalid information to ensure the integrity of your data. You can run AutoInvoice together with Customer Interface or separately.Oracle Receivables can create invoices from the following Oracle applications: Oracle Order Management, Oracle Projects, Oracle Service, Oracle Property Manager.
Oracle Receivables can create invoices from the following non-Oracle applications: Legacy system (for transaction history), Non-Oracle billing applications, Non-Oracle order entry applications.Oracle Receivables uses three interface tables for AutoInvoice:
AutoInvoice transfers transaction data from the above three interface tables into the following Receivables tables:
- RA_CUSTOMER_TRX _ALL
- RA_CUSTOMER_TRX_LINES _ALL
- This table contains information relating to all transactions to be processed by AutoInvoice.
- Transactions include invoices, debit memos, credit memos, and on-account credits.
- Each record contains line, tax, freight, or finance charges information.
- The Line_Type field identifies the type of information contained in the record.
- A record can be a parent record: Line, Header Freight, or Charges; or a child record: Tax or linelevel Freight.
- A child record is linked to the parent record using the Link-To Transaction flexfield.
- This table contains accounting distributions to be used by the transactions defined in RA_INTERFACE_LINES.
- Accounts defined in this table override any accounts created using AutoAccounting.
- You can choose to pass some or all account information to AutoInvoice. Any accounts that are not passed will be derived using AutoAccounting.
- Records in this table are linked to records in the RA_INTERFACE_LINES table using the Transaction flexfield.
- Not required if AutoAccounting determines GL distributions.
What occurs during auto-invoice:
- This table contains all sales credit information for the transactions in the RA_INTERFACE_LINES table.
- The two tables are linked using the Transaction flexfield.
- Not required if not tracking sales credit.
When run, AutoInvoice produces the AutoInvoice Execution Report and the AutoInvoice Validation Report.Any entries which failed validation can be reviewed in Oracle Receivables’ AutoInvoice Interface Exceptions window. Depending on the error, changes may need to be made in Receivables, the feeder program or the imported records in the interface tables.Transaction flexfields:
- Populates the RA_Interface_Lines, RA_Interface_distribution and RA_Interface_salescredit tables.
- Lines are grouped and ordered by the grouping rule and line ordering line. Grouping rules are mandatory and determine how transaction lines are grouped into transactions. Optionally, you can use line-ordering rules to determine the order in which lines are displayed on a transaction.
- Tax, freight, commitments and credit memos are linked to transaction line by transaction line, transaction reference and transaction link-to descriptive flex fields. To uniquely identify imported transactions and link the tax, freight, commitments and credit memos, define the Transaction flexfields.
- GL date is determined.
- GL accounting code combinations are assigned using auto accounting rule.
- TAX is determined.
- All transactions are batched.
- Validated lines are used to create the transaction.
Types of transaction flexfields:
- Transaction flexfields are descriptive flexfields that AutoInvoice uses to uniquely identify transaction lines.
- Because they are unique for each transaction line, they can also be used to reference and link to other lines.
- Receivables lets you determine how you want to build your transaction flexfield structure and what information you want to capture.
- Define a flexfield for each import source. Specify which one to use during import.
What is inside AutoInvoice?AutoInvoice is a tool consists of 3 main programs. Each program will have unique nature of work to do and they are called internally except Purge program whose execution is derived on the setup otherwise ready to execute stand alone.
- Invoice Header (optional): Specifies invoice header information
- Line (required): Uniquely identifies invoice lines
- Link-To (optional): Link tax and freight to invoice lines
- Reference (optional): Links credit memos to transactions
1] Auto Invoice Master program:Selects and marks records in the interface tables to be processed based on the parameters the user entered and then calls the AutoInvoice Import program. Auto Invoice Master program has no report output.
- Master (RAXMTR)
- Import (RAXTRX)
- Purge (RAXDEL)
2] Auto Invoice Import Program:Validates the selected record and creates transaction if it passes validation. Any record that fails validation is left in the interface table with an error code. Depending on the setup, related records may be rejected as well. This program has an output file called Auto Invoice Execution report, which you can view by clicking the View Report button in the Requests window.
- Gathers statistics, it means it gathers the stats on interface tables and set the stats on certain indices on interface tables.
- Marks interface records for processing by marking request_id.
- Submits multiple workers for Parallel Processing by creating instances for request.
3] Auto Invoice Purge Program:Deletes records from the interface tables. If you set the Purge Interface Table system option to No in Define System Option window, Auto Invoice does not delete processed records from the interface tables after each run, and we must submit Auto Invoice Purge Program periodically to clean up the interface tables. This program only deletes transaction lines that have been successfully imported.AutoInvoice Exception Handling:Records that fail validation are called ‘Exceptions’
- Workhorse of Auto invoice
- Validates data
- Inserts records
- Deletes interface data
- Only when system option purge set to ‘Y’
AutoInvoice Exception Handling Windows:
- Exceptions stay in Interface Tables which is RA_INTERFACE_ERRORS_ALL.
- Errors can be corrected in the Exception Handling window.
- Once corrections are made, Auto invoice must be resubmitted.
- Records that pass validation get transferred to Receivables tables.
- Interface Exception window displays exception messages associated with all invalid records.
- Interface Lines window displays records that fail validation, provides an error message and can be used to correct the errors.
- The Line Errors windows displays errors associated with a specific line, and can only be opened from Interface Lines window.
- Interface Exceptions window displays Interface Id, Exception Type, Error Message and Invalid Value associated to the error.
- Data cannot be edited in this window, but error can be viewed and corrected by clicking the Details button.
- Error Message and Column name with invalid data are displayed in the Message column, and the invalid value that needs to be corrected is displayed in the Invalid Value column.
Bloom Filter in Oracle Database2011-03-07 21:18:00Bloom Filter in Oracle Database
Bloom Filter用的范围很广，不仅在Search Engine中大量采用，在Data Deduplication中也频频遇到。那么它在数据库中的应用情况怎么样呢？放狗找一下，这个比较有意思：http://antognini.ch/papers/BloomFilters20080620.pdf。
今天在和同事讨论问题的时候突然想起来，这个玩意就是以前在Database Machine论文中见过的Bit vector啊：http://academic.research.microsoft.com/Publication/803587
Job Completion/Return Process in Oracle MES2013-01-31 11:20:06How to perform a WIP job Completion in Oracle MES ,Below is basic flow for completion.Define BOM,and Routing, Then Create a Job in Oracle Application(Supply type:Assembly Pull for this demo case)Respo
Enabling SSL or TLS in Oracle E-Business Suite Release 122015-06-16 16:08:23Enabling SSL or TLS in Oracle E-Business Suite Release 12 In This Document Section 1: IntroductionSection 2: Concepts and TerminologySection 3: Application Tier SetupSection 4: Database Tier Set
DROP TABLE IF EXISTS” in Oracle and SQL Server2013-07-29 12:41:07“DROP TABLE IF EXISTS” in Oracle and SQL Server In MySQL it is pretty easy to drop a table if it exists already. In Oracle and Microsoft’s SQL Server it is a little more complicated. ...
Two Security Models in Oracle EBS:Function Security + Role-Based Access Control2013-04-02 10:47:47There are two main security models in Oracle E-Business Suite: “traditional” Oracle E-Business Suite responsibility-based security (usually referred to as “function security”) and Role-Based ...
The Oracle Home "xxx" is not found in Oracle Inventory as an Oracle Grid Infrastructure Home.2018-07-12 21:53:14在搭建oracle 12c rac过程中，已成功安装了grid，在安装database软件时，因为中途出现意外，只能进行重装，将/u01/app/oraInventory目录下文件全部的删除后，database已成功安装在两个节点，但在创建asm磁盘运行...
OPMN in Oracle Applications R122013-03-09 00:33:25OPMN consists of two main components (Oracle Process Manager) and (Oracle Notification Server).Oracle Process Manager is responsible for1) starting2) stopping3) restarting4) monitoring the services it
Material Status Enhancement in Oracle EBS R12.22012-09-11 12:32:07Material Status Enhancement Introduction ...The material status feature was originally developed for Oracle Warehouse Management (WMS). Since R12, the material status feature is also available in Or
Start/Stop the Workflow Services in Oracle Apps2013-02-27 11:48:22Navigation:Navigate to Workflow Administrator> Oracle Application Manager > Workflow Manager > Service Components > Workflow Agent Listener Services Start and Stop the Oracle Workflow Services--------
Placement of Voting disk and OCR Files in Oracle RAC 10g and 11gR1 [ID 293819.1]2010-11-26 12:58:00Placement of Voting disk and OCR Files in Oracle RAC 10g and 11gR1 [ID 293819.1] Modified 27-OCT-2010 Type BULLETIN Status PUBLISHED In this Document ...
Unit Of Measure(UOM) in Oracle Applications Inventory2012-10-23 17:12:41in Class P to Base UOM in Base UOM in Class S 5. ConvertBase UOM in Class S to Secondary UOM inClass S (this gives Secondary UOM) From the Two cases, it is evident that a conversion from ...
.trc and .trm files in Oracle 11g2012-10-12 11:33:05The files located in the TRACE directory, with the “.trm” extensions, are called Trace Metadata files. The metadata in .trm files describe the trace records stored inside of .trc trace files. The ...
Installing Oracle Data Integrator 220.127.116.11 in Oracle Linux 6 u3(64bit)2013-12-13 17:02:34This blog covers installing ODI 18.104.22.168 as well as Weblogic 10.3.6. ...1. Oracle database 11gR2 installed. 2. sysdba privilege of Oracle database 11gR2. 3. Check ODI 22.214.171.124 for c
调试经验——如何查看Oracle自定义函数 （How to view definition of user defined functions in Oracle）2018-10-18 23:15:53问题描述： 现有的Query中似乎使用了一个自定义函数String_to_list，为了排查问题，需要查看这个函数的定义。 方法： --新建的function，并未保存在All_ojbects表中，而是保存在user_objects表中 ...
Using Class of Secure Transport (COST) to Restrict Instance Registration in Oracle RAC (Doc ID 134082013-11-04 12:24:25Applies to: Oracle Net Services - Version 10.2.0.3 to 126.96.36.199 [Release 10.2 to 11.2] ...Information in this document applies to any platform. Goal To demonstrate how the COST parameter "SECURE_RE
How to switch to another database in oracle 11g(如何在oracle中从一个数据库切换到另一个数据库)2011-03-08 17:13:001.quit from sqlplus2 excute set oracle_sid="new_sid"3.run sqlplus to confirm it.
Oracle中的in 和 not in2019-04-16 14:56:17Oracle中的in和not in 在日常的数据统计中，尽量不要用not in 来查询数据，可以考虑使用exist或者left join 来代替。但是如果用到in 和 not in 时，必须要考虑null值的影响，不能简单理解为not in 是in的差集。举例 ...
oracle中代替in 和not in 高效方法2018-09-07 10:51:40在oracle中效率排行：表连接>exist>not exist>in>not in; 因此如果简单提高效率可以用exist代替in进行操作，当然换成表连接可以更快地提高效率，具体是用left join代替not in 和...
【Oracle】ORACLE 12c DB In-Memory简述及启用2015-11-08 23:23:55Oracle DB In-Memory是预装在Oracle Database 12c（188.8.131.52之后的版本）中的，不需要安装其他软件或者是重新编译现有的的数据库软件。这是因为In-Memory选项是作为SGA的一个新组件无缝集成在Oracle Database...
Oracle的in查询2018-07-28 08:32:17In查询 SELECT ID, ASSAY_ITEM_NAME FROM DIC_ASSAY_ITEM WHERE ID IN ( SELECT DISTINCT ASSAY_ITEM_ID FROM DIC_ASSAY_ITEM_METHOD ) 或者 Mapper Li...
Oracle存储过程拼接in语句 & 自定义split函数2016-05-20 09:55:34关于Oracle存储过程中拼接in语句以及自定义split函数
oracle中使用join表连接查询代替 in和not in 查询2018-06-25 11:00:46首先，在oracle中效率排行：表连接>exist>not exist>in>not in,而且使用in查询会有查询条件数量不能超过1000的限制;因此如果简单提高效率可以用exist代替in进行操作，当然换成表连接...
Oracle IN MEMORY OPTION2014-11-04 00:23:23众所周知，ORACLE是行存储数据库。
oracle not in 查不出数据2015-08-21 16:20:38在oracle中，sql语句如下： select * from xa_ajxx where ajxx_uuid not in (select ajxx_uuid from sl_zdajbgxq ) 却查不出应有的数据，查询结果为空。 原因是 sl_zdajbgxq中存在着ajxx_uuid为空的数据。...
Oracle Database 12c In-Memory 学习推荐博文2016-05-17 17:11:54Oracle官方In-Memory Blog的系列文章Getting started with Oracle Database In-Memory非常值得一读。Getting started with Oracle Database In-Memory包括以下文章： Part I - Installing & Enabling 介绍如何启用...
oracle for in loop2012-07-30 09:17:52function get_irc_pn(p_irc in pnrreg.irc%type, p_partid in pnrreg.partid%type) return varchar2 is o_irc_pn varchar2(200); -- p_pnrreg pnrreg%rowtype;
Oracle存储过程in、out、in out 模式参数2016-11-14 11:59:381、in模式参数 输入模式的参数，用于接收参数，在子程序内部，不能进行修改。默认的参数模式：in。--定义打印的存储过程 CREATE OR REPLACE PROCEDURE println (str VARCHAR) AS BEGIN dbms_output.put_line(str);...