There are many behaviors in complex forms that must be enforced dynamically at runtime, either to adhere to the field-level validation model of Oracle E-Business Suite, or to enforce specific business rules.
You should model your form's item and event handlers after these examples.
In most of the item relations you are dynamically disabling and enabling items. For your disabled items, note these Oracle Forms coding issues:
WHEN-VALIDATE-ITEM always fires the first time a user Tabs through each field on a brand new record, even if they do not make a change. Internally Oracle Forms notes that the value changes from unknown to null, therefore it fires WHEN-VALIDATE-ITEM. Also, WHEN-VALIDATE-ITEM fires when a user changes a field from a non-null value to null.
Furthermore, a user can leave a required field null at any time; it is only trapped at record level. Therefore, all WHEN- VALIDATE-ITEM triggers must account for the value of the field being null, and act accordingly. Since you cannot distinguish between the user changing the value to null, or Oracle Forms setting the value to null the first time, both must behave as if the user changed the value.
Most of the time, a disabled item has a null value. Since you account for nulls because of the previous issue, this is not a problem. In those rare cases that a disabled field has a value, and that value got set while it was disabled and the field has not been validated yet, you may need to add logic to WHEN-VALIDATE-ITEM to do nothing.
To create a text item, check box, or poplist that is enabled only when a master item is populated, use the procedure APP_FIELD.SET_ DEPENDENT_FIELD. This routine enforces the following behaviors:
The dependent item is either cleared or made invalid when the master item changes.
If the master item is NULL or the condition is FALSE, the dependent item is disabled.
Create the item handler procedures as shown below and then call the procedures from the specified triggers.
Important: These routines do not apply to display-only text items. To conditionally grey out display-only text items, use the routine APP_ITEM_PROPERTY.SET_VISUAL_ ATTRIBUTE.
See: APP_ITEM_PROPERTY: Property Utilities
In the following example, a block order has items item_type and item_name. Item_name is dependent on item_type, thus item_name is enabled only when item_type is NOT NULL.
Create your item handler procedures as follows:
PACKAGE BODY ORDER IS
PROCEDURE ITEM_TYPE(EVENT VARCHAR2) IS
BEGIN
IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
--- Any validation logic goes here.
ITEM_NAME('INIT');
ELSE
fnd_message.debug('Invalid event passed to
ORDER.ITEM_TYPE: ' || EVENT);
END IF;
END ITEM_TYPE;
PROCEDURE ITEM_NAME(EVENT VARCHAR2) IS
BEGIN
IF ((EVENT = 'PRE-RECORD') OR
(EVENT = 'INIT')) THEN
APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
'ORDER.ITEM_TYPE',
'ORDER.ITEM_NAME');
ELSE
fnd_message.debug('Invalid event passed to
ORDER.ITEM_NAME: ' || EVENT);
END IF;
END ITEM_NAME;
END ORDER;
Call your item handler procedures in:
Trigger: WHEN-VALIDATE-ITEM on item_type:
order.item_type('WHEN-VALIDATE-ITEM');
Trigger: PRE-RECORD on order (Fire in Enter-Query Mode: No):
order.item_name('PRE-RECORD');
If your master and dependent items are in a multi-row block, or if they are items in a single-row block that is a detail of a master block, you must call SET_DEPENDENT_FIELD for the POST-QUERY event as well.
PROCEDURE ITEM_NAME(EVENT VARCHAR2) IS
BEGIN
IF ((EVENT = 'PRE-RECORD') OR
(EVENT = 'INIT') OR
(EVENT = 'POST-QUERY')) THEN
APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
'ORDER.ITEM_TYPE',
'ORDER.ITEM_NAME');
ELSE
fnd_message.debug('Invalid event passed to
ORDER.ITEM_NAME: ' || EVENT);
END IF;
END ITEM_NAME;
Add another call to your item handler procedure in:
Trigger: POST-QUERY
ORDER.ITEM_NAME('POST-QUERY');
Important: In a multi-record block, if the dependent item is the last item in the record, the cursor navigates to the next record when tabbing from the master. To work around this behavior, code a KEY-NEXT-ITEM trigger that does a VALIDATE(Item_scope) and then a NEXT_ITEM.
Important: If the dependent item is a required list or option group, set the "invalidate" parameter in the call to APP_FIELD.SET_DEPENDENT_FIELD to TRUE. When this flag is TRUE, the dependent item is marked as invalid rather than cleared.
A conditionally dependent item is enabled or disabled depending on the particular value of the master item. In this example, the block order has items item_type and item_size. Item_size is enabled only when item_type is "SHOES."
Create your item handler procedures as follows. Note that this item handler is very similar to the simple master/dependent situation, but you specify the condition instead of the name of the master item.
PACKAGE BODY order IS
PROCEDURE ITEM_TYPE(EVENT VARCHAR2) IS
BEGIN
IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
size('INIT');
ELSE
fnd_message.debug('Invalid event passed to
ORDER.ITEM_TYPE: ' || EVENT);
END IF;
END item_type;
PROCEDURE size(EVENT VARCHAR2) IS
BEGIN
IF ((EVENT = 'PRE-RECORD') OR
(EVENT = 'INIT')) THEN
APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
(:order.item_type = 'SHOES'),
'ORDER.SIZE');
ELSE
fnd_message.debug('Invalid event passed to
ORDER.SIZE: ' || EVENT);
END IF;
END size;
END order;
Call your item handler procedures in:
Trigger: PRE-RECORD on order (Fire in Enter-Query Mode: No):
order.item_size('PRE-RECORD');
Trigger: WHEN-VALIDATE-ITEM on item_type:
order.item_type('WHEN-VALIDATE-ITEM');
There are cases where multiple items are dependent on a single master item. For example, only certain item_types can specify a color and size. Therefore, the color and size fields are dependent on the master field item_type, and they are enabled only when item_type is "RAINCOAT."
Create your item handler procedures as follows:
PACKAGE BODY order IS
PROCEDURE item_type(EVENT VARCHAR2) IS
BEGIN
IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
color('INIT');
size('INIT');
ELSE
fnd_message.debug('Invalid event passed to
ORDER.ITEM_TYPE: ' || EVENT);
END IF;
END item_type;
PROCEDURE color(EVENT VARCHAR2) IS
BEGIN
IF (EVENT = 'PRE-RECORD') OR
(EVENT = 'INIT') THEN
APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
(:order.item_type = 'RAINCOAT'),
'ORDER.COLOR');
ELSE
fnd_message.debug('Invalid event passed to
ORDER.COLOR: ' || EVENT);
END IF;
END color;
PROCEDURE size(EVENT VARCHAR2) IS
BEGIN
IF (EVENT = 'PRE-RECORD') OR
(EVENT = 'INIT') THEN
APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
(:order.item_type = 'RAINCOAT'),
'ORDER.SIZE');
ELSE
fnd_message.debug('Invalid event passed to
ORDER.SIZE: ' || EVENT);
END IF;
END size;
END order;
Call your item handler procedures in:
Trigger: WHEN-VALIDATE-ITEM on order.item_type:
order.item_type('WHEN-VALIDATE-ITEM');
Trigger: PRE-RECORD (Fire in Enter-Query Mode: No):
order.color('PRE-RECORD');
order.size('PRE-RECORD');
There may also be cases where an item is dependent on two master items. Suppose that different sizes of sweaters come in different colors. You cannot fill in the color of the sweater until you have filled in both item_type and size. The validation of block.dependent is controlled by the content of both master_1 and master_2.
Create your item handler procedures as follows:
PACKAGE BODY order IS
PROCEDURE item_type(EVENT VARCHAR2) IS
BEGIN
IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
color('INIT'):
ELSE
fnd_message.debug('Invalid event passed to
ORDER.ITEM_TYPE: ' || EVENT);
END IF;
END item_type;
PROCEDURE size(EVENT VARCHAR2) IS
BEGIN
IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
color('INIT');
ELSE
fnd_message.debug('Invalid event passed to
ORDER.SIZE: ' || EVENT);
END IF;
END size;
PROCEDURE color(EVENT VARCHAR2) IS
BEGIN
IF (EVENT = 'PRE-RECORD') OR
(EVENT = 'INIT') THEN
APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
((:order.item_type IS NOT NULL) AND
(:order.size IS NOT NULL)),
'ORDER.COLOR');
ELSE
fnd_message.debug('Invalid event passed to
ORDER.COLOR: ' || EVENT);
END IF;
END color;
END order;
Call your item handler procedures in:
Trigger: WHEN-VALIDATE-ITEM on order.item_type:
order.item_type('WHEN-VALIDATE-ITEM');
Trigger: WHEN-VALIDATE-ITEM on order.size:
order.size('WHEN-VALIDATE-ITEM');
Trigger: PRE-RECORD (Fire in Enter-Query Mode: No):
order.color('PRE-RECORD');
With cascading dependence, item_3 depends on item_2, which in turn depends on item_1. Usually all items are in the same block.
For example, the block order contains the items vendor, site, and contact.
The list of valid sites depends on the current vendor.
Whenever vendor is changed, site is cleared.
Whenever vendor is null, site is disabled.
The list of valid contacts depends on the current site.
Whenever site is changed, contact is cleared.
Whenever site is null, contact is disabled.
To code the correct behavior for these dependent items, follow these steps.
Create your item handler procedures as follows:
PACKAGE BODY order IS
PROCEDURE vendor(EVENT VARCHAR2) IS
BEGIN
IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
SITE('INIT');
ELSE
fnd_message.debug('Invalid event passed to
ORDER.VENDOR: ' || EVENT);
END IF;
END VENDOR;
PROCEDURE SITE(EVENT VARCHAR2) IS
BEGIN
IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
CONTACT('INIT');
ELSIF (EVENT = 'PRE-RECORD') OR
(EVENT = 'INIT') THEN
APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
'ORDER.VENDOR',
'ORDER.SITE');
CONTACT(EVENT);
ELSE
fnd_message.debug('Invalid event passed to
ORDER.SITE: ' || EVENT);
END IF;
END SITE;
PROCEDURE CONTACT(EVENT VARCHAR2) IS
BEGIN
IF (EVENT = 'PRE-RECORD') OR
(EVENT = 'INIT') THEN
APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
'ORDER.SITE',
'ORDER.CONTACT');
ELSE
fnd_message.debug('Invalid event passed to
ORDER.CONTACT: ' || EVENT);
END IF;
END CONTACT;
END ORDER;
Call your item handler procedures in:
Trigger: WHEN-VALIDATE-ITEM on vendor:
order.vendor('WHEN-VALIDATE-ITEM');
Trigger: WHEN-VALIDATE-ITEM on site:
order.site('WHEN-VALIDATE-ITEM');
Trigger: PRE-RECORD on order (Fire in Enter-Query Mode: No):
order.site('PRE-RECORD');
order.contact('PRE-RECORD');
Remember that the following chain of events occurs whenever the VENDOR field is validated:
VENDOR is validated, which calls SITE ('INIT').
SITE ('INIT') causes the state of SITE to change and calls CONTACT ('INIT').
CONTACT ('INIT') causes the state of CONTACT to change.
Use the procedure APP_FIELD.SET_EXCLUSIVE_FIELD to code two items where only one item is valid at a time.
The key to coding an item handler procedure for mutually exclusive items is to realize that mutually exclusive items are logically one item. Whenever one of a pair of mutually exclusive items is dependent on or depended upon by another item, they both are. Their relationship to other items is always identical. Therefore, code a single item handler procedure for the single logical item.
If both mutually exclusive items are NULL, then both items are navigable. If one item is populated, then the other item is unnavigable (you can still click there), and any value in that item is cleared.
If one item must be not null, set the REQUIRED property of both items to be Yes in the Oracle Forms Developer. If both items may be null, set the REQUIRED property of both items to be No. APP_FIELD.SET_ EXCLUSIVE_FIELD reads the initial REQUIRED property and dynamically manages the REQUIRED properties of both items.
You can also use the procedure APP_FIELD.SET_EXCLUSIVE_FIELD for a set of three mutually exclusive items. For more than three items, you must write your own custom logic.
Important: Mutually exclusive check boxes and required lists require mouse operations.
For example, a block lines has mutually exclusive items credit and debit.
Call your item handler procedures in:
PACKAGE BODY lines IS
PROCEDURE credit_debit(EVENT VARCHAR2) IS
BEGIN
IF ((EVENT = 'WHEN-VALIDATE-ITEM') OR
(EVENT = 'PRE-RECORD')) THEN
APP_FIELD.SET_EXCLUSIVE_FIELD(EVENT,
'LINES.CREDIT',
'LINES.DEBIT');
ELSIF (EVENT = 'WHEN-CREATE-RECORD') THEN
SET_ITEM_PROPERTY('lines.credit', ITEM_IS_VALID,
PROPERTY_TRUE);
SET_ITEM_PROPERTY('lines.debit', ITEM_IS_VALID,
PROPERTY_TRUE);
ELSE
fnd_message.debug('Invalid event passed to
Lines.credit_debit: ' || EVENT);
END IF;
END credit_debit;
END lines;
Create your item handler procedures as follows:
Trigger: WHEN-VALIDATE-ITEM on credit:
lines.credit_debit('WHEN-VALIDATE-ITEM');
Trigger: WHEN-VALIDATE-ITEM on debit:
lines.credit_debit('WHEN-VALIDATE-ITEM');
Trigger: PRE-RECORD on lines (Fire in Enter-Query Mode: No):
lines.credit_debit('PRE-RECORD');
Trigger: WHEN-CREATE-RECORD on lines:
lines.credit_debit('WHEN-CREATE-RECORD');
You only need the WHEN-CREATE-RECORD trigger if the resulting one of your mutually-exclusive fields is required. This trigger initially sets all the mutually-exclusive fields of the set to be required. The fields are then reset appropriately once a user enters a value in one of them.
Use APP_FIELD.SET_INCLUSIVE_FIELD to code a set of items where, if any of the items is not null, all of the items are required.
The item values may be entered in any order. If all of the items are null, then the items are optional.
You can use the procedure APP_FIELD.SET_INCLUSIVE_FIELD for up to five mutually inclusive items. For more than five items, you must write your own custom logic.
This example shows a block payment_info with mutually inclusive items payment_type and amount.
Create your item handler procedures as follows:
PACKAGE BODY payment_info IS
PROCEDURE payment_type_amount(EVENT VARCHAR2) IS
BEGIN
IF ((EVENT = 'WHEN-VALIDATE-ITEM') OR
(EVENT = 'PRE-RECORD')) THEN
APP_FIELD.SET_INCLUSIVE_FIELD(EVENT,
'PAYMENT_INFO.PAYMENT_TYPE',
'PAYMENT_INFO.AMOUNT');
ELSE
fnd_message.debug('Invalid event to
payment_info.payment_type_ amount: ' || EVENT);
END IF;
END payment_type_amount;
END payment_info;
Call your item handler procedures in:
Trigger: WHEN-VALIDATE-ITEM on payment_info.payment_type:
payment_info.payment_type_amount('WHEN-VALIDATE-ITEM');
Trigger: WHEN-VALIDATE-ITEM on payment_info.amount:
payment_info.payment_type_amount('WHEN-VALIDATE-ITEM');
Trigger: PRE-RECORD on payment_info (Fire in Enter-Query Mode: No):
payment_info.payment_type_amount('PRE-RECORD');
There are cases where items are dependent on master items, where the master items are mutually inclusive.
See: Item Relations.
This example shows a block payment_info with mutually inclusive items payment_type and amount, just as in the previous example. The block also contains two regions, one for check information and one for credit card information. Check Information has a single item, check_number. Credit Card Information has five items: credit_type, card_holder, number, expiration_date, and approval_code.
Payment Type can be Cash, Check, or Credit.
When Payment Type is Check, the Check Information region is enabled.
When Payment Type is Credit, the Credit Card Information region is enabled.
Create your item handler procedures as follows:
PACKAGE BODY payment_info IS
PROCEDURE payment_type_amount(EVENT VARCHAR2) IS
BEGIN
IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
APP_FIELD.SET_INCLUSIVE_FIELD(EVENT,
'PAYMENT_INFO.PAYMENT_TYPE',
'PAYMENT_INFO.AMOUNT');
IF (:SYSTEM.CURSOR_ITEM =
'payment_info.payment_type') THEN
check_info('INIT');
credit_info('INIT');
END IF;
ELSIF (EVENT = 'PRE-RECORD') THEN
APP_FIELD.SET_INCLUSIVE_FIELD(EVENT,
'PAYMENT_INFO.PAYMENT_TYPE',
'PAYMENT_INFO.AMOUNT');
ELSE
fnd_message.debug('Invalid event in
payment_info.payment_type_amount: ' || EVENT);
END IF;
END payment_type_amount;
PROCEDURE check_info IS
BEGIN
IF ((EVENT = 'PRE-RECORD') OR
(EVENT = 'INIT')) THEN
APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
(:payment_info.payment_type = 'Check'),
'PAYMENT_INFO.CHECK_NUMBER');
ELSE
fnd_message.debug('Invalid event in
payment_info.check_info: ' || EVENT);
END IF;
END check_info;
PROCEDURE credit_info IS
CONDITION BOOLEAN;
BEGIN
IF ((EVENT = 'PRE-RECORD') OR
(EVENT = 'INIT')) THEN
CONDITION := (:payment_info.payment_type = 'Credit');
APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
CONDITION,
'PAYMENT_INFO.CREDIT_TYPE');
APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
CONDITION,
'PAYMENT_INFO.NUMBER');
APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
CONDITION,
'PAYMENT_INFO.CARD_HOLDER');
APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
CONDITION,
'PAYMENT_INFO.EXPIRATION_DATE');
APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
CONDITION,
'PAYMENT_INFO.APPROVAL_CODE');
ELSE
fnd_message.debug('Invalid event in
payment_info.credit_info: ' || EVENT);
END IF;
END credit_info;
END payment_info;
Call your item handler procedures in:
Trigger: WHEN-VALIDATE-ITEM on payment_info.payment_type:
payment_info.payment_type_amount('WHEN-VALIDATE-ITEM');
Trigger: WHEN-VALIDATE-ITEM on payment_info.amount:
payment_info.payment_type_amount('WHEN-VALIDATE-ITEM');
Trigger: PRE-RECORD on payment_info (Fire in Enter-Query Mode: No):
payment_info.payment_type_amount('PRE-RECORD');
payment_info.check_info('PRE-RECORD');
payment_info.credit_info('PRE-RECORD');
Use the procedure APP_FIELD.SET_REQUIRED_FIELD to code an item that is only mandatory when a certain condition is met. If the condition is FALSE, the dependent item is optional. Any value in the dependent item is not cleared. If an item is both conditionally required and dependent, call APP_FIELD.SET_DEPENDENT_FIELD before calling APP_FIELD.SET_REQUIRED_FIELD.
An example demonstrates using APP_FIELD.SET_REQUIRED_FIELD.
A block purchase_order has items total and vp_approval. Vp_approval is required when total is more than $10,000. (Note: quantity * unit_price = total).
Create your item handler procedures as follows:
PACKAGE BODY purchase_order IS
PROCEDURE vp_approval(EVENT VARCHAR2) IS
BEGIN
IF ((EVENT = 'PRE-RECORD') OR
(EVENT = 'INIT')) THEN
APP_FIELD.SET_REQUIRED_FIELD(EVENT,
(:purchase_order.total > 10000),
'PURCHASE_ORDER.VP_APPROVAL');
ELSE
fnd_message.debug('Invalid event in
purchase_order.vp_approval: ' || EVENT);
END IF;
END vp_approval;
PROCEDURE total(EVENT VARCHAR2) IS
BEGIN
IF (EVENT = 'INIT') THEN
:purchase_order.total := :purchase_order.quantity *
:purchase_order.unit_price;
vp_approval('INIT');
ELSE
fnd_message.debug('Invalid event in purchase_order.total: ' || EVENT);
END total;
PROCEDURE quantity(EVENT VARCHAR2) IS
BEGIN
IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
total('INIT');
ELSE
fnd_message.debug('Invalid event in
purchase_order.quantity: ' || EVENT);
END IF;
END quantity;
PROCEDURE unit_price(EVENT VARCHAR2) IS
BEGIN
IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
total('INIT');
ELSE
fnd_message.debug('Invalid event in
purchase_order.unit_price: ' || EVENT);
END IF;
END unit_price;
END purchase_order;
Call your item handler procedures in:
Trigger: PRE-RECORD on purchase_order (Fire in Enter-Query Mode: No):
purchase_order.vp_approval('PRE-RECORD');
Trigger: WHEN-VALIDATE-ITEM on quantity:
purchase_order.quantity('WHEN-VALIDATE-ITEM');
Trigger: WHEN-VALIDATE-ITEM on unit_price:
purchase_order.unit_price('WHEN-VALIDATE-ITEM');
To default values when the user first creates a new record, use the Default values property in the Oracle Forms Designer. For more complex defaulting behavior, follow the example below.
Create your event handler procedure as follows:
PACKAGE block IS
PROCEDURE WHEN_CREATE_RECORD IS
BEGIN
:block.item1 := default_value1;
:block.item2 := default_value2;
...
END WHEN_CREATE_RECORD;
END block;
Call your event handler procedures in:
Trigger: WHEN-CREATE-RECORD:
block.WHEN_CREATE_RECORD;
When you want to set a default for an item whose validation depends on another item (for example, to apply the default when the master value changes), set the default values in the dependent item's INIT event.
This section discusses how to handle:
To do a uniqueness check for a key, use a select statement that is invoked by the WHEN-VALIDATE-ITEM event.
Note that a uniqueness check done in WHEN-VALIDATE-ITEM does not catch duplicates residing in uncommitted rows (for instance, a user enters uncommitted, duplicate rows in a detail block). The database constraints will catch this situation, as well as the situation where an identical key is committed by someone else between the time that the WHEN-VALIDATE-ITEM fired and your record is committed. For this reason, you do not need to write a uniqueness check in PRE-UPDATE or PRE-INSERT.
If there is a single unique key field, always call the CHECK_UNIQUE package from WHEN-VALIDATE-ITEM for that field.
If the unique combination is comprised of multiple fields, call the CHECK_UNIQUE package from the WHEN-VALIDATE- RECORD trigger.
Example:
PROCEDURE CHECK_UNIQUE(X_ROWID VARCHAR2,
pkey1 type1, pkey2 type2, ...) IS
DUMMY NUMBER;
BEGIN
SELECT COUNT(1)
INTO DUMMY
FROM table
WHERE pkeycol1 = pkey1
AND pkeycol2 = pkey2
...
AND ((X_ROWID IS NULL) OR (ROWID != X_ROWID));
IF (DUMMY >= 1) then
FND_MESSAGE.SET_NAME('prod', 'message_name');
APP_EXCEPTION.RAISE_EXCEPTION;
END IF;
END CHECK_UNIQUE;
Create your item handler procedure as follows:
PACKAGE BODY block IS
PROCEDURE item(EVENT VARCHAR2) IS
BEGIN
IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
table_PKG.CHECK_UNIQUE(:block.row_id,
:block.pkey1, :block.pkey2, ...);
ELSE
message('Invalid event in block.item');
END IF
END item;
END block;
When deleting a record, you must be concerned about the existence of other records that may be referencing that record. For example, if an item has already been placed on a Purchase Order, what should occur when you attempt to delete the item? Three possible answers are:
Don't allow the item to be deleted.
Also delete the Purchase Order.
Allow the item to be deleted, and null out the reference to it on the Purchase Order.
Most of the time, the first solution is both the most practical and sensible. To do this, create a procedure that detects these referenced cases, and raise an exception.
To give a warning when detail records will be deleted, create CHECK_REFERENCES as a function which returns FALSE if detail records exist (CHECK_REFERENCES should still raise an exception if deleting the row would cause a referential integrity error).
If a table contains subtypes, you must decide whether you need one CHECK_REFERENCES procedure or one CHECK_REFERENCES procedure per subtype.
If the subtypes share most of the foreign key references with some subtype-specific foreign key references, then create just one CHECK_REFERENCES procedure with the first parameter a subtype discriminator.
If the subtypes are orthogonal, then create a CHECK_subtype_REFERENCES procedure for each subtype.
Create your table handler procedures as follows:
CREATE OR REPLACE PACKAGE BODY table_PKG AS
PROCEDURE CHECK_REFERENCES(pkey1 type1, pkey2 type2, ...) IS
MESSAGE_NAME VARCHAR2(80);
DUMMY credit;
BEGIN
MESSAGE_NAME := 'message_name1';
SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
(SELECT 1 FROM referencing_table1
WHERE ref_key1 = pkey1
AND ref_key2 = pkey2
...
);
MESSAGE_NAME := 'message_name2';
SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
(SELECT 1 FROM referencing_table2
WHERE ref_key1 = pkey1
AND ref_key2 = pkey2
...
);
...
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_MESSAGE.SET_NAME('prod', MESSAGE_NAME);
APP_EXCEPTION.RAISE_EXCEPTION;
END CHECK_REFERENCES;
END table_PKG;
Create your event handler procedures as follows:
PACKAGE BODY block IS
PROCEDURE key_delete IS
BEGIN
--
-- First make sure its possible to delete this record.
-- An exception will be raised if its not.
--
table_PKG.CHECK_REFRENCES(pkey1, pkey2, ...);
--
-- Since it is possible to delete the row, ask the
-- user if they really want to,
-- and delete it if they respond with 'OK'.
--
app_record.delete_row;
END key_delete;
END block;
Call the event handler:
Trigger: KEY-DELETE:
block.dey_delete;
Tip: You should do similar steps again with the ON-DELETE trigger. It is possible that between the time a user requested the delete, and actually saved the transaction, a record was entered elsewhere that will cause referential integrity problems. Remember that KEY-DELETE fires in response to the user initiating a delete, but it does not actually perform the delete; it just flags the record to be deleted and clears it from the screen. The ON-DELETE trigger fires at commit time and actually performs the delete.
The Calendar is a standard object that allows selection of date and time values from a Calendar. It also allows the developer to specify validation rules ensuring that only valid dates can be selected. Both the List and Edit functions should invoke the Calendar on any date field.
For each date field within a form, you should provide the code necessary for the user to call the Calendar feature. However, the calendar is not a replacement for validating the data in the field.
The Calendar is automatically included in the TEMPLATE form.
For more information on the user interface standards for the Calendar, see the Oracle E-Business Suite User Interface Standards for Forms-Based Products.
Date and datetime fields should enable the List lamp. When the user invokes List on these fields, the form opens the Calendar window.
Date fields should use the ENABLE_LIST_LAMP LOV, which is included in the TEMPLATE form. This setting enables the menu and Toolbar List of Values entries for your date fields. Set '"Validate from List" to No on fields that use this LOV. If you leave "Validate from List" set to Yes, you will see an LOV that has no columns.
Each date field within a form needs to have the following code:
Trigger: KEY-LISTVAL:
calendar.show([first_date]);
By default, the Calendar shows the month of the value in the date field (if a value exists) upon first opening. If no specific date is supplied, the Calendar shows the current month.
Do not pass the current field into CALENDAR.SHOW as a parameter, as this forces validation of the field. The current field is used as a default. Generally, the code in KEY-LISTVAL should be:
calendar.show;
Important: Never pass the value of the current date field as the argument to CALENDAR.SHOW. Because the calendar actually disables all Oracle Forms validation momentarily, if the user has entered an invalid date then immediately invokes the calendar, a PL/SQL error occurs. SHOW automatically handles this case when no argument is passed to it.
The KEY-LISTVAL trigger must have Execution Hierarchy "Override," and should not fire in enter-query mode.
See: CALENDAR: Calendar Package.
The entire calendar can be run in a display-only mode, where it is used to show one or more dates as Selected, rather than allowing the user to select a particular date. For example, it can be used to show all dates on which an employee was absent.
In this mode, characteristics of the field the cursor is on are ignored. All the user can do is change the month and year shown, and press 'OK' to close the window (no value is ever written back to the form).
To invoke this mode, the following calls are required in addition to those listed above:
Trigger: KEY-LISTVAL:
calendar.setup('DISPLAY');
calendar.setup('TITLE', null, null,
'<translated text for window title>');
Additional CALENDAR.SETUP calls are required after these two calls to establish those dates that should be shown as selected.
You can incorporate optional features into your Calendar call. If you use any of the optional calls, they must be placed before the mandatory calendar.show call.
The following examples customize the Calendar to show or disable specific dates.
To disable weekends (where the weekend is defined as Saturday and Sunday):
calendar.setup('WEEKEND');
To disable specific date ranges where the dates are either hard-coded or references to other fields on the form:
calendar.setup(<30 char identifying name>, <low_date>, <high_date>);
This call can be repeated as many times as needed. A null LOW_DATE is treated as the beginning of time; a null HIGH_DATE is treated as the end of time.
To disable specific date ranges where the dates are contained in a table:
calendar.setup(<30 char identifying name>, null, null, <SQL>);
This call may be made only once per field, but may return multiple rows. A null LOW_DATE is treated as the beginning of time; a null HIGH_DATE is treated as the end of time. Use NVL in your SQL statement if this is not the desired behavior.
Restrictions from several tables can be performed by using UNION SQL statements. The selected columns must be aliased to LOW_DATE and HIGH_DATE.
Tip: Ordering on the LOW_DATE column may improve performance. Restricting the dates returned to a small range near the anticipated selected value also improves performance.
If you need to be able to activate the Calendar from a field that is not explicitly declared as a DATE or DATETIME field (such as a CHAR text item that serves multiple purposes depending on context), write the Calendar calls as normal. The Calendar acts as if invoked from a DATE field, and when the user selects a value the date is written back to the field in the format "DD-MON-YYYY."
Then user-named trigger CALENDAR_WROTE_DATE fires. Create that trigger at the item level, and add any code you need to process the value (typically you need to apply a mask to it).
In this example, you want to open the Calendar to show either the date currently displayed in the DATE item, or the current month if no date is displayed. Additionally, you want to disable weekends (Saturdays and Sundays).
Trigger: KEY-LISTVAL:
calendar.setup('WEEKEND');
calendar.show;
In a form with the field SHIP_BY_DATE, you want to open the Calendar and customize it to:
Disable all holidays defined in the ORG_HOLIDAYS table
Disable weekends
Show the month corresponding to the date in field "LINES.NEED_BY_DATE" when the Calendar is opened
The code to implement this is:
Trigger: KEY-LISTVAL:
calendar.setup('WEEKEND');
calendar.setup('Manufacturing Holidays', null, null,
'select action_date LOW_DATE,
action_date HIGH_DATE '||
'from org_holidays where
date_type = ''HOLIDAY''');
calendar.show(:lines.need_by_date);
In a form with a field NEED_BY_DATE, you want the Calendar to show the month corresponding to the date in the field LINES.CREATED_DATE + 30 days. You also want to disable all dates before and including: LINES.CREATED_DATE.
The code to implement this is:
Trigger: KEY-LISTVAL:
calendar.setup('After created date', null,
lines.created_date);
calendar.show(:lines.need_by_date + 30);
A form uses a button called "Holidays" to show all Manufacturing holidays. The current month displays initially, and the calendar finds the selected dates in the ORG_DATES table.
The code to implement this is:
Trigger: WHEN-BUTTON-PRESSED on HOLIDAYS:
calendar.setup('TITLE', null, null,
'<translated text for "Manufacturing Holidays">');
calendar.setup('Manufacturing Holidays', null, null,
'select action_date LOW_DATE, action_date HIGH_DATE '||
'from org_dates where date_type = ''HOLIDAY''');
calendar.show;
For standards and examples of coding calendars into your forms, see: The Calendar.
PROCEDURE show (first_date date default null);
This call shows the calendar. Do not pass the current field value into show; this value is used by default.
PROCEDURE setup (new_type varchar2,
low_date date DEFAULT null,
high_date date DEFAULT null,
sql_string varchar2 DEFAULT null);
Important: The WEEKEND argument is hardcoded to mean Saturday and Sunday, so it is not applicable to all countries (such as countries where the weekend is defined as Friday and Saturday).
PROCEDURE event (event varchar2);