Oracle: Adding a message box with Form Personalisations

The seeded Payment Overview form (APXPWALL) in Oracle Payables shows limited details relating to the payee, in this example we will make the payee bank details visible on this form.

We could use Custom PLL to add additional fields to the form, however the simpler approach is to use Form Personalisations to display the details in a Pop-up message box.

From the Payment Overview form select Form Personalisations and create an action that will enable a new Menu option:

Trigger Event: WHEN-NEW-FORM-INSTANCE
Action Type: Menu
Menu Entry: SPECIAL3
Menu Label: Payment Details

Then create an action that will display the message box:

Trigger Event: SPECIAL3
Action Type: Message
Message Type: Show
Message Text: =SELECT xxapps_forms_util_pkg.inv_payee_bank_details(:PAYMENT.CHECK_ID,:PAYMENT.PAYMENT_METHOD) FROM dual

The above action calls a function in a custom package to return the formatted details, the function is defined as:


FUNCTION inv_payee_bank_details (p_check_id IN NUMBER
, p_pay_method IN VARCHAR2)
RETURN VARCHAR2
IS
v_payee_details VARCHAR2 (250);
BEGIN
IF p_pay_method = 'Electronic'
THEN
SELECT 'Bank Name: '
|| xba.bank_name
|| CHR (10)
|| 'Sort Code: '
|| xba.branch_number_fmt
|| CHR (10)
|| 'Account Num: '
|| xba.bank_account_num
|| CHR (10)
|| 'Account Name: '
|| xba.bank_account_name
INTO v_payee_details
FROM xxapps_iby_bank_accounts_v xba
, ap_checks_all aca
WHERE aca.external_bank_account_id = xba.ext_bank_account_id
AND aca.check_id = p_check_id;
ELSE
v_payee_details := 'Paid by Cheque';
END IF;

RETURN v_payee_details;
EXCEPTION
WHEN OTHERS
THEN
RETURN 'No Data Found!';
END inv_payee_bank_details;


Now, from within the Payment Overview form select Tools > Payment Details to view the message

Oracle: Using Forms Personalisations to restrict a List of Values

In this scenario there is a requirement for end users in the Payables Department to be able to amend and create Flex Values (using the seeded Segment Values form). As there are many Flex Values that these users should not be given access to, Forms Personalisations is used to restrict the form (this personalisation can be adapted to any LOV in any form).

The value set is called XXAPPS_CUST_ID, and the form to be personalised is Segment Values (FNDFFMSV).

As we will be modifying the seeded Record Group attached to the LOV the first step is to download the FMB file from the application server and open it in Forms Developer to identify the query used in the record group. The record group attached to the LOV is FLEX_VALUE_SET_NAME_QF and the query is:

select v.validation_type, v.flex_value_set_name,
l.meaning, v.parent_flex_value_set_id, v.flex_value_set_id
from fnd_flex_value_sets v,
fnd_lookups l,
fnd_flex_validation_tables t
where v.flex_value_set_id = t.flex_value_set_id (+)
and ((v.validation_type in ('D','I','X','Y')) or
(v.validation_type = 'F' and t.summary_allowed_flag = 'Y'))
and l.lookup_code = v.validation_type
and l.lookup_type = 'SEG_VAL_TYPES'
order by v.flex_value_set_name

Within Forms Personalisations create a new personalisation with a Trigger Event of WHEN-NEW-FORM-INSTANCE and the following Actions;

Action Type: Builtin
Builtin Type: Create Record Group from Query
Argument:
select v.validation_type, v.flex_value_set_name,
l.meaning, v.parent_flex_value_set_id, v.flex_value_set_id
from fnd_flex_value_sets v,
fnd_lookups l,
fnd_flex_validation_tables t
where v.flex_value_set_id = t.flex_value_set_id (+)
and ((v.validation_type in ('D','I','X','Y')) or
(v.validation_type = 'F' and t.summary_allowed_flag = 'Y'))
and l.lookup_code = v.validation_type
and l.lookup_type = 'SEG_VAL_TYPES'
and v.flex_value_set_name in ('XXAPPS_CUST_ID')
order by v.flex_value_set_name
Group Name: XXAPPS_LOV
This creates a new Record Group (based on the seeded query but restricted to only the XXAPPS_CUST_ID value set).

Action Type: Property
Object Type: LOV
Target Object: FLEX_VALUE_SET_NAME_QF
Property Name: GROUP_NAME
Value: XXAPPS_LOV
This assigns the Records Group to the List Of Values.

These steps are all that is required to restrict the LOV, however to fully restrict the form we also need to amend the DEFAULT_WHERE (to stop users running ‘non-restricted’ queries IE: CTRL+F11) and also disable the other Radio Buttons in the Form.

To amend the DEFAULT_WHERE create the following action:

Action Type: Property
Object Type: Block
Target Object: VSET
Property Name: DEFAULT_WHERE
Click in the Value field and select 'Get Value' – this will return the seeded value, this can then be modified to restrict the results:
((validation_type IN ('I', 'D', 'X', 'Y') OR (validation_type = 'F' AND summary_allowed_flag = 'Y')) and parent_value_set_name in ('XXAPPS_CUST_ID'))

To disable the other Radio Buttons create actions to set the Property ENABLED to False for the following items:
QUERY_FIND.REGION_POPUP.KEY
QUERY_FIND.REGION_POPUP.DESCR
QUERY_FIND.REGION_POPUP.CONC
CONTROL.REGION_POPUP.KEY
CONTROL.REGION_POPUP.DESCR
CONTROL.REGION_POPUP.CONC


Now when the form is opened the only available option is Values Sets and the LOV and Default Where is restricted to the XXAPPS_CUST_ID value.