Oracle: Uploading Read-Only columns in ADI

Standard functionality in Oracle Web Applications Desktop Integrator does not allow uploading columns marked as read-only; this is highlighted in Bug 6607618.

The following customisation can be used as a workaround, however as it requires direct modification of underlying tables it is not supported nor recommended by Oracle (and don't blame me if it goes wrong!).


In the application do NOT set the columns as read-only, instead, after defining the Integrator and Content identify the CONTENT_CODE using the following:

SELECT bint.user_name
     , bint.integrator_code
     , bcon.content_code
FROM   bne_integrators_tl bint
     , bne_contents_b bcon
WHERE  bint.integrator_code = bcon.integrator_code
AND    bint.user_name = &IntegratorName;

Then, update the READ_ONLY_FLAG to ‘Y’ on the table BNE_CONTENT_COLS_B for the columns you want to be read-only (based on the CONTENT_CODE identified in the above script).

Finally, clear the BNE cache (refer to Metalink Note 1075840.1).

Oracle: Submitting BI Publisher Reports using PL/SQL

This simple example shows how to submit concurrent programs that use BI Publisher to render the output.Before calling the fnd_request.submit_request procedure to create the concurrent request the BI Publisher template must be assigned, to do this use the seeded fnd_request.add_layout procedure:

declare
  xml_layout BOOLEAN;
  jreq_id NUMBER;

begin

xml_layout := fnd_request.add_layout ('XXAPPS'
                      , 'XXAPPS_TEMPLATE_CODE'
                      , 'en'
                      , '00'
                      , 'PDF');

jreq_id := fnd_request.submit_request ('XXAPPS'
                      , 'XXAPPS_PROGRAM_CODE'
                      , ''
                      , ''
                      , FALSE
                      , 'PARAM1'
                      , 'PARAM2');

end;


**XXAPPS : Application Short Code
**XXAPPS_TEMPLATE_CODE : Template Code (as defined in XML Publisher Administrator)
**en : Template Language
**00 : Template Territory
**PDF : Output Format


**XXAPPS_PROGRAM_CODE : Concurrent Program Short Code
**PARAM1/2 : Concurrent Program Parameters

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: Concurrent Request Security

In previous releases of Oracle concurrent request access was controlled by the use of profile values, however in Release 12 access is controlled by the use of Roles. In these examples we will create two Roles; one which grants access to the output of concurrent programs based on Responsibility, and one which grants access to all outputs (for use by System Administrators).

First up we will create the Role for Responsibility based security.

Within the Functional Developer responsibility search for the Object named "Concurrent Requests", then select the 'Object Instance Sets' tab and create a new Instance Set:

Name: XXAPPS Responsibility Concurrent Access
Code: XXAPPS_RESP_CR_ACCESS
Description: Responsibility Concurrent Access
Predicate:
&TABLE_ALIAS.request_id IN ( SELECT request_id FROM fnd_concurrent_requests WHERE concurrent_program_id IN (SELECT fru.request_unit_id FROM fnd_responsibility_vl frv , fnd_request_groups frg , fnd_request_group_units fru WHERE frv.request_group_id = frg.request_group_id AND frg.request_group_id = fru.request_group_id AND fru.request_unit_type IN ('P', 'S') AND frv.responsibility_id = fnd_global.resp_id))

To assign this to all users create a new Grant:

Name: XXAPPS Responsibility Concurrent Access
Description: Access to view all concurrent requests in current responsibility
Grantee Type: All Users
Data Security > Object: Concurrent Requests
Data Context Type: Instance Set (select the Instance Set XXAPPS Responsibility Concurrent Access)
Permission Set: Request Operations


Now, create a new Instance Set for the System Administrator access:

Name: XXAPPS System Administrator Concurrent Access
Code: XXAPPS_SYS_ADMIN_CR_ACCESS
Description: System Administrator Concurrent Access
Predicate:
&TABLE_ALIAS.request_id IN (SELECT request_id FROM fnd_concurrent_requests)

This Instance Set will be assigned to named users, so we need to create a Role (within the User Management responsibility):


Role:-
Category: Miscellaneous
Role Code: XXAPPS_SYS_ADMIN_CR_ACCESS
Display Name: XXAPPS System Administrator Concurrent Access
Description: Access to view all concurrent requests
Application: XXAPPS Application

Grant:-
Name: XXAPPS System Administrator Concurrent Access
Description: Access to view all concurrent requests
Data Security > Object: Concurrent Requests
Data Context Type: Instance Set (select the Instance Set XXAPPS System Administrator Concurrent Access)
Permission Set: Request Operations