Oracle Application Blog

Creating Custom Menus and inserting to base tables through API

Step1: Create custom menus in source instance. Use the below API’s to move the menus and menu entries to the target instance. Step2: The custom menus can be passed to the below API’s to insert data to Oracle standard tables fnd_menus,fnd_menus_tl and fnd_menu_entries Ø fnd_menus_pkg.insert_row Ø fnd_menu_entries_pkg.insert_row Step3: Sample script with description to upload the Menus and menu entries are explained below Menus ———– DECLARE l_new_menu_id NUMBER; l_exist_menu_flag NUMBER; v_rowid VARCHAR2 (20); BEGIN SELECT MAX (menu_id) INTO l_new_menu_id FROM apps.fnd_menus_vl WHERE menu_name = ‘XX_TEST_MENU’; DBMS_OUTPUT.put_line (‘Before IF ‘); IF (l_new_menu_id IS NOT NULL) THEN l_exist_menu_flag := 1; — menu already exists DBMS_OUTPUT.put_line (‘Menu already FOUND, Duplicates not allowed’); ELSE –create a new menu l_new_menu_id := apps.fnd_menus_s.NEXTVAL; DBMS_OUTPUT.put_line (‘Before calling Standard API ‘); apps.fnd_menus_pkg.insert_row (x_rowid => v_rowid, x_menu_id => l_new_menu_id, x_menu_name => ‘XX_TEST_MENU’,…

Enabling Custom DFF in Custom Oracle Forms

We can create a custom Descriptive Flexfield on custom tables and use it in custom form. Here we have illustrated the steps required to create the DFF and register it in Oracle Apps. Step1: Create the custom table in Custom Schema create table xx_order_details (order_id number, order_number number, order_date date, customer_id number, attribute_category VARCHAR2(150), attribute1 VARCHAR2(240), attribute2 VARCHAR2(240), attribute3 VARCHAR2(240), attribute4 VARCHAR2(240), attribute5 VARCHAR2(240) ) We will use attribute1 to be registered as descriptive flexfields. Step2 : Grant Permission to Apps User Grant all on xx_order_details to APPS. Step 3: Create synonym in APPS CREATE SYNONYM xx_order_details FOR <CUSTOM SCHEMA>.xx_order_details Step 4: Register table with Apps using AD_DD Pakage BEGIN AD_DD.REGISTER_TABLE(‘FND’,’XX_ORDER_DETAILS’,’T’); END; Step5 : Register Column with Apps Using AD_DD Package BEGIN AD_DD.REGISTER_COLUMN(‘FND’,’XX_ORDER_DETAILS’,’ORDER_ID’,1,’NUMBER’,10,’N’,’N’); AD_DD.REGISTER_COLUMN(‘FND’,’XX_ORDER_DETAILS’,’ORDER_NUMBER’,2,’NUMBER’,10,’N’,’N’); AD_DD.REGISTER_COLUMN(‘FND’,’XX_ORDER_DETAILS’,’ORDER_DATE’,3,’DATE’,9,’N’,’N’); AD_DD.REGISTER_COLUMN(‘FND’,’XX_ORDER_DETAILS’,’CUSTOMER_ID’,4,’NUMBER’,10,’N’,’N’);…

How to set background in Oracle Apex 5 application using CSS

Objective: To set background in Oracle Apex 5 application using CSS (Cascading Style Sheets). Step 1: Create a HTML Region. Step 2: Put a required image into Shared Components. Step 3: In this region source CSS can be added for setting background. Output:

Multiple selection in select list item – Oracle Apex 4.2

Objective: To select multiple value from select list item in Oracle Application Express 4.2. Step 1: Create Select list item. Step 2: Step 3: Output:

Search on Double Click in IR Report

Place the below code in HTML Header: <script type=”text/javascript”> $(function(){ $(‘.apexir_WORKSHEET_DATA td’).live(‘dblclick’,function(){ $(‘#apexir_SEARCH’).val($(this).text()); gReport.search(‘SEARCH’); }); }); </script>

DDL operation while execution

While query execution we cannot do the ddl operation in the earlier versions From 11g we can use the below script to lock the object for mentioned seconds and perform the ddl operation. ALTER SESSION SET DDL_LOCK_TIMEOUT=60;

Copy / Paste Tabular Form Report

Copy / Paste Tabular Form Report is typical requirement as User will Copy From Excel and paste in tabular form. Some typical challenges to be faced are: 1. Data from Clipboard will not be splitted properly. 2. Keeping track of column in tabular form is tedious. Following Steps are involved in process: I have used 3 items in this process: P35_FROM_CURRENCY – Holds Whole tabular form data from Clipboard P35_COL_ID – Holds Current Column P35_TO_CURRENCY – Holds headings which are been copied from Excel. Step:1: Create Static Content region and add HTML Code for splitting and retrieval of value Process. <html> <head> <script> function Disable_Control_C() { $(“#P35_FROMCURRENCY”).val(“”); var keystroke = String.fromCharCode(event.keyCode).toLowerCase(); if (event.ctrlKey && keystroke == ‘v’) { // alert(“Start”); $(“#P35_FROMCURRENCY”).css(“display”,”block”); $(“#P35_FROMCURRENCY”).focus(); var a = $(“#P35_FROMCURRENCY”).val(); setTimeout(function(){ if (a != ” “) { var tes = $(“#P35_FROMCURRENCY”).val().split(“t”); $(“#P35_FROMCURRENCY”).val(tes); var k=$(“#P35_FROMCURRENCY”).val();…

Tabular Form Text with MultiSelect Option

Step 1: Create a tabular form. Step 2: In Page Header add the JQuery Library File. <script src=”https://code.jquery.com/jquery-1.9.1.js”></script> Step 3: Create the text field to store the list of values with Comma Separated Values. Step 4: In Source Type, Select SQL Query(Return Single Value) Example: SELECT LISTAGG(job, ‘, ‘) WITHIN GROUP (ORDER BY job) “job” FROM mul_sel; Step 5: In Dynamic Action, “OnClick” of the Text Item, Use the Below Java Script Code for the Performing the Auto Complete and Multi Selection. Code: function split( val ) { return val.split( /,s*/ ); } function extractLast( term ) { return split( term ).pop(); } var availableTags; var a = this.triggeringElement.id; var b = “#” + a; //alert(b); $(b).bind( “keydown”, function( event ) { var c = $(“#P40_VALUES”).val(); var temp = new Array(); temp = c.split(“,”); availableTags = temp;…

Progress Bar in Reports in APEX5

APEX can create “Percentage Bars” within a report. This can be acheived using HTML Code in your Report SQL Query. Steps: 1- Create your report: Create an Interactive or Classic Report using SQL query : SELECT CASE WHEN COLUMN <= 30 THEN ‘<div class=”a-Report-percentChart” style=”background-color:#000000;width:100%;”><div class=”a-Report-percentChart-fill” style=”width:’ || COLUMN || ‘% ; background-color: 330099;”></div><span class=”u-VisuallyHidden”>’ || COLUMN || ‘</span></div>’ || COLUMN || ‘%’ WHEN COLUMN BETWEEN 30 AND 50 THEN ‘<div class=”a-Report-percentChart” style=”background-color:#000000;width:100%;”><div class=”a-Report-percentChart-fill” style=”width:’ || COLUMN || ‘% ; background-color:CC0000;”></div><span class=”u-VisuallyHidden”>’ || COLUMN || ‘</span></div>’ || COLUMN || ‘%’ WHEN COLUMN BETWEEN 55 AND 70 THEN ‘<div class=”a-Report-percentChart” style=”background-color:#000000;width:100%;”><div class=”a-Report-percentChart-fill” style=”width:’ || COLUMN || ‘% ; background-color:#99eb47;”></div><span class=”u-VisuallyHidden”>’ || COLUMN || ‘</span></div>’ || COLUMN || ‘%’ END AS “Progress bar”…

Mail Level Approval Process

Direct Mail Approval is the trending process as we don’t need to go back to application and then moving to approval process. Steps involved in following process are: Step:1: Create application with authentication scheme as “No Authentication”. Also create a process for updation with point as before header. Updation process should be done with values in following items P1_SENDER – // Sender for whom updation needs to be done P1_APPROVER – // Approver who approves the mail P1_STATUS – // Status of Approval -whether approved /rejected Step:2: Create mail process with following PL/SQL Code which frames the content of mail. DECLARE lv_to_email VARCHAR2 (100) := ‘richard.dalvi@staging.doyensys.com’; lv_from_email VARCHAR2 (100) := ‘karthik.venkatachalam@staging.doyensys.com’; lv_body VARCHAR2 (32767); BEGIN lv_body := ‘<html> <body> Hi,’ || CHR (10) || ‘Kindly Approve my leave on 15-DEC-2015’ || CHR (10) || ‘<br/> <br/><a href=”https://apex.oracle.com/pls/apex/f?p=24370:1:&SESSION.::&DEBUG.:P1_TO_EMAIL,P1_STATUS,P1_FROM_EMAIL:’ || lv_from_email || ‘,Approved,’ || lv_to_email || ‘” id=”h1″><button>Approve</button></a> <a href=”https://apex.oracle.com/pls/apex/f?p=24370:1:&SESSION.::&DEBUG.:P1_TO_EMAIL,P1_STATUS,P1_FROM_EMAIL:’ || lv_from_email || ‘,Rejected,’…

We take you from best practices to next practices of software implementation

Solutions that Ensure You Stay Ahead of Competition

Your Partner for Strategic Technology Solutions

Guiding Your Digital Transformation Journey