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

Oracle: Universal Work Queue – Advanced Collections

The Universal Work Queue can be integrated with many other Oracle applications, in this example we will be using it to display Task information generated from Oracle Advanced Collections.

Out of the box the Universal Work Queue has a number of nodes that display data relating to Advanced Collections and Delinquencies, however we have a requirement to categorise the Tasks based on Customer Attributes and Collector information.

The first step is to create the Lookup Types that will be used, for this example the custom Lookup Type name that has been created is "XXAPPS IEU Node Labels" (XXAPPS_IEU_NODE_LABELS”). The Lookup Code "Tasks : Customer Category" (XXAPPS_IEU_NEW_TAKS) has been created within this Lookup Type.

Lookup Type:
Name XXAPPS IEU Node Labels
Code XXAPPS_IEU_NODE_LABELS
Description Node Labels for Custom UWQ Nodes
Application XXAPPS Application
Access Extensible
Lookup Codes:
Code XXAPPS_IEU_NEW_TASKS
Description Tasks : Customer Category

The next step is to create the database views that will be used by the custom node, in this example the view is based on the seeded IEU_UWQ_TASKS_V view with additional fields added to return the Customer Attribute information. Next, create the Spreadtable Metadata Definition (within the CRM Administration responsibility), for this example the Datasource Name is XXAPPS_IEU_UWQ_TASKS and the referenced Relational View is XXAPPS_IEU_UWQ_TASKS_V.

Within the CRM Administrator the Work Provider can be created based on the configuration completed so far:
Application Name XXAPPS Application
Node Name Lookup Type XXAPPS_IEU_NODE_LABELS
Node Name Lookup Code XXAPPS_IEU_NEW_TASKS
Node Name Lookup Meaning Tasks
Enumerator UUID XXAPPS_TASK_NODE
Registration Type Non Media

Enumerator Procedure XXAPPS_iex_util_pkg.enumerate_group_uwq_nodes
Refresh Procedure XXAPPS_iex_util_pkg.refresh_group_uwq_nodes
View Name XXAPPS_IEU_UWQ_TASKS_V


The Enumerator and Refresh Procedures defined above reference PL/SQL packages that build the Nodes and populate the UWQ with the relevant data.

The XXAPPS_IEX_UTIL_PKG is defined as:

CREATE OR REPLACE PACKAGE XXAPPS_iex_util_pkg
AS

PROCEDURE enumerate_group_uwq_nodes (P_RESOURCE_ID IN NUMBER
, P_LANGUAGE IN VARCHAR2
, P_SOURCE_LANG IN VARCHAR2
, P_SEL_ENUM_ID IN NUMBER);

PROCEDURE refresh_group_uwq_nodes (P_RESOURCE_ID IN        NUMBER
, p_node_id IN            NUMBER
, P_COUNT      OUT NOCOPY NUMBER);



END XXAPPS_iex_util_pkg;
/

CREATE OR REPLACE PACKAGE BODY XXAPPS_iex_util_pkg
AS


PROCEDURE enumerate_group_uwq_nodes (P_RESOURCE_ID IN NUMBER
, P_LANGUAGE IN VARCHAR2
, P_SOURCE_LANG IN VARCHAR2
, P_SEL_ENUM_ID IN NUMBER)
AS
l_node_label VARCHAR2 (200);
uwq_ld_list IEU_PUB.EnumeratorDataRecordList;
l_bind_list IEU_PUB.BindVariableRecordList;
l_node_counter NUMBER;
v_org_id  NUMBER := iex_org_override;
BEGIN
SAVEPOINT start_node_enumeration;

l_node_counter := 0;

uwq_ld_list (l_node_counter).NODE_LABEL := 'Tasks : Customer Category';
uwq_ld_list (l_node_counter).VIEW_NAME := 'XXAPPS_IEU_UWQ_TASKS_V';
uwq_ld_list (l_node_counter).DATA_SOURCE := 'XXAPPS_IEU_UWQ_TASKS';
uwq_ld_list (l_node_counter).MEDIA_TYPE_ID := '';
uwq_ld_list (l_node_counter).WHERE_CLAUSE :=
'CUSTOMER_ORG_ID = ' || v_org_id;
uwq_ld_list (l_node_counter).NODE_TYPE := 0;
uwq_ld_list (l_node_counter).HIDE_IF_EMPTY := '';
uwq_ld_list (l_node_counter).NODE_DEPTH := 1;

DECLARE
CURSOR c1
IS
SELECT   customer_type
FROM     (SELECT lookup_code customer_type
FROM   ar_lookups
WHERE  lookup_type = 'ADDRESS_CATEGORY'
UNION
SELECT   'GENERAL' FROM DUAL)
WHERE    EXISTS
(SELECT 'x'
FROM   jtf_tasks_b tasks_b
, hz_cust_site_uses_all casu
, hz_cust_acct_sites_all cas
WHERE  casu.cust_acct_site_id = cas.cust_acct_site_id
AND    casu.site_use_id = tasks_b.source_object_id
AND    TRUNC (
NVL (tasks_b.planned_start_date, SYSDATE)) <=
                                   SYSDATE
                         AND    NVL (tasks_b.deleted_flag, 'N') = 'N'
                         AND    tasks_b.open_flag = 'Y'
                         AND    tasks_b.entity = 'TASK'
                         AND    cas.org_id = v_org_id
                         AND    NVL (
                                   NVL (cas.customer_category_code
                                      , UPPER (cas.attribute_category))
                                 , 'GENERAL') = customer_type
                         AND    tasks_b.owner_type_code = 'RS_GROUP')
            ORDER BY 1;
      BEGIN
         FOR x IN c1
         LOOP
            l_node_counter := l_node_counter + 1;

            uwq_ld_list (l_node_counter).NODE_LABEL := x.customer_type;
            uwq_ld_list (l_node_counter).VIEW_NAME := 'XXAPPS_IEU_UWQ_TASKS_V';
            uwq_ld_list (l_node_counter).DATA_SOURCE := 'XXAPPS_IEU_UWQ_TASKS';
            uwq_ld_list (l_node_counter).MEDIA_TYPE_ID := '';
            uwq_ld_list (l_node_counter).WHERE_CLAUSE :=
                  'CUSTOMER_TYPE = '
               || ''''
               || x.customer_type
               || ''''
               || ' and CUSTOMER_ORG_ID = '
               || v_org_id;
            uwq_ld_list (l_node_counter).NODE_TYPE := 0;
            uwq_ld_list (l_node_counter).HIDE_IF_EMPTY := 'Y';
            uwq_ld_list (l_node_counter).NODE_DEPTH := 2;
         END LOOP;
      END;

      IEU_PUB.ADD_UWQ_NODE_DATA (P_RESOURCE_ID, P_SEL_ENUM_ID, uwq_ld_list);
   EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK TO start_node_enumeration;
         RAISE;
   END enumerate_group_uwq_nodes;


   PROCEDURE refresh_group_uwq_nodes (p_resource_id IN        NUMBER
                                    , p_node_id IN            NUMBER
                                    , p_count      OUT NOCOPY NUMBER)
   IS
      sql_stmt  VARCHAR2 (500);
      l_view_name VARCHAR2 (500);
      l_label   VARCHAR2 (500);
      l_where   VARCHAR2 (500);
      l_depth   NUMBER;
      v_count   NUMBER;
      v_org_id  NUMBER := iex_org_override;
   BEGIN
      BEGIN
         SELECT where_clause
              , node_label
              , node_depth
         INTO   l_where
              , l_label
              , l_depth
         FROM   IEU_UWQ_SEL_RT_NODES
         WHERE  node_id = p_node_id
         AND    resource_id = p_resource_id
         AND    data_source = 'XXAPPS_IEU_UWQ_TASKS';
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            l_where     := NULL;
            l_label     := NULL;
      END;


      IF l_depth = 1
      THEN
         SELECT COUNT ( * )
         INTO   p_count
         FROM   jtf_tasks_b tasks_b
              , hz_cust_site_uses_all casu
              , hz_cust_acct_sites_all cas
         WHERE  casu.cust_acct_site_id = cas.cust_acct_site_id
         AND    casu.site_use_id = tasks_b.source_object_id
         AND    NVL (tasks_b.deleted_flag, 'N') = 'N'
         AND    tasks_b.open_flag = 'Y'
         AND    tasks_b.entity = 'TASK'
         AND    cas.org_id = v_org_id
         AND    tasks_b.owner_type_code = 'RS_GROUP'
         AND    TRUNC (NVL (tasks_b.planned_start_date, SYSDATE)) <= SYSDATE;
      ELSE
         SELECT COUNT ( * )
         INTO   p_count
         FROM   jtf_tasks_b tasks_b
              , hz_cust_site_uses_all casu
              , hz_cust_acct_sites_all cas
         WHERE  casu.cust_acct_site_id = cas.cust_acct_site_id
         AND    casu.site_use_id = tasks_b.source_object_id
         AND    NVL (tasks_b.deleted_flag, 'N') = 'N'
         AND    tasks_b.open_flag = 'Y'
         AND    tasks_b.entity = 'TASK'
         AND    cas.org_id = v_org_id
         AND    NVL (
                   NVL (cas.customer_category_code
                      , UPPER (cas.attribute_category))
                 , 'GENERAL') = l_label
         AND    tasks_b.owner_type_code = 'RS_GROUP'
         AND    TRUNC (NVL (tasks_b.planned_start_date, SYSDATE)) <= SYSDATE;
      END IF;
   END refresh_group_uwq_nodes;
END XXAPPS_iex_util_pkg;

Now, within System Administrator enable the Profile Option “IEU: Queue: Tasks : Customer Category” (linked to the UWQ Work Provider) and the nodes will be visible within the Universal Work Queue.

Oracle: Subqueries in Discoverer

With no out-of-the box method of using dynamic parameters in SQL queries within Discoverer, reports can become very cumbersome and costly to run. However, there is a work around for this with the use of database context values.

In this example we will create a package to set a context value that can then be referenced within a custom Discoverer workbook.

The first step is to create the custom context and a package that will set and return the context value (in this example xxapps_eul_us is the database user owning the Discoverer EUL):


CREATE OR REPLACE CONTEXT EUL_DISCO USING xxapps_eul_disco
/

CREATE OR REPLACE PACKAGE xxapps_eul_disco AUTHID DEFINER AS

FUNCTION set_context(p_name VARCHAR2
,p_value VARCHAR2)
RETURN VARCHAR2;

FUNCTION show_context(p_name VARCHAR2)
RETURN VARCHAR2;

END xxapps_eul_disco;
/

CREATE OR REPLACE PACKAGE BODY xxapps_eul_disco AS

FUNCTION set_context(p_name VARCHAR2
,p_value VARCHAR2) RETURN VARCHAR2 AS
BEGIN
dbms_session.set_context('EUL_DISCO', p_name, p_value);
RETURN p_value;
END set_context;

FUNCTION show_context(p_name VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN SYS_CONTEXT('EUL_DISCO', p_name);
END show_context;

END xxapps_eul_disco;
/

GRANT ALL ON xxapps_eul_disco TO xxapps_eul_us;



Within Discoverer Administrator select Tools > Register PL/SQL Functions and create a new function:

Function Name: SET_CONTEXT
Display Name: XXAPPS Set Context
Owner: APPS
Package: XXAPPS_EUL_DISCO
Database Link:
Return Type: Varchar
Description: Set Context
Identifier: SET_CONTEXT
Available in Desktop/Plus: True

In this example the context is set based on the value entered as a parameter in the workbook (the parameter is named ‘As of date’). To set the context; create a workbook Condition referencing the XXAPPS Set Context function:

Item: ‘DUMMY’
Condition: <>
Values: =XXAPPS Set Context(‘EFFECTIVE DATE’,:“As of date”)

Now, within the SQL of the workbook the context value can be referenced as a subquery, EG:


SELECT ...

FROM ...

WHERE date <= TO_DATE(xxapps_eul_disco.show_context('EFFECTIVE DATE'),'DD-MON-RRRR')


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.

Oracle: HTML Personalisations (Raw Text and URL Include)

These first simple extensions shows how to add a bespoke 'Message Of The Day' banner to the Oracle Application Home Page.

Example 1 – Raw Text.

Enable the profile option Personalize Self-Service Defn at user level, this enables the personalisation links on HTML forms and pages (after logging out and back in).

On the Application Home page select the Personalize "Home Contenttable” link.

Create a new item within the table layout "Home Contenttable" region.

Select the Item Style Raw Text, enter an ID (e.g.: xx_motd) and enter the static text or HTML code in Text field.

Example 2 – URL Include.

Create an htm file within the $OA_HTML (e.g.: xx_motd.htm) directory on the application server (see below for sample code).

Enable personalisation links and select Personalize "Home Contenttable" then create an item with an Item Style of URL include, enter an ID (e.g.: xx_motd) and in the Source URI enter the path to your htm file (e.g.: /OA_HTML/xx_motd.htm).

Example xx_motd.htm

<!-- ---------------------------------- --> <!-- dbdrv: none                          --> <!-- File : xx_motd.htm               --> <!-- Author : C Hammond             --> <!-- Creation Date : 10-AUG-2010 --> <!-- Message Of The Day HTML Plugin --> <!-- ---------------------------------- --> <td id="MessageOfTheDay" class="x9k" valign="top" width="34%">  <table id="MessageOfTheDay" class="xn" cellpadding="0" cellspacing="0" style="border:1px #87B4D9 solid;" width="100%">  <tr>   <td class="xp" style="background-image:url (/OA_HTML/cabo/images/swan/headingBarBg.gif);">   <h2 class="x75">   <!-- Plugin Header: -->   Message Of The Day   <!-- -->   </h2>   </td>  </tr>  <tr>   <td style="background-color:#D9E5EF">   <br>   <font face="Tahoma" size="2px" color="#147590">   <div style="padding-left: 0.5em;">   <!-- Plugin Text: -->   <STRONG>Latest News</STRONG>   <br>   Your message goes here ...   <!-- -->   </div>   </font>   <br>   </td>  </tr>  </table> </td>