Adding Custom EmojiOne Emoticon Plugins to TinyMCE


Sections

  1. Introduction
  2. Step 1 – Create a MySQL table to store the EmojiOne Emoji details
  3. Step 2 – Populate the Database Table
  4. Step 3 – Decide which TinyMCE Plugins to create
  5. Step 4 – Decide which category icons to use for the TinyMCE buttons
  6. Step 5 – Edit TinyMCE CSS files
  7. Step 6 – Include FontAwesome CSS
  8. Step 7 – Review the standard Emoticons Plugin
  9. Step 8 – Create a Custom EmojiOne Plugin
  10. Step 9 – Download
  11. Step 10 – Other Stuff

Introduction

Screenshots

In this post, I describe how I created some custom plugins for TinyMCE (version 4) which can be used to insert the very nice and lovely emojiOne artwork into your TinyMCE content.

This is what they look like:

Toobar icons (there are 2 rows for the 2 different versions – read on for info)

Toobar

Click on an icon to view the Emoji

Click to view Emoji

Click Emoji to insert into TinyMCE content (title and alt attributes also added)

Inserted Emoji

Background

If you use TinyMCE you might have seen the Emoticons plugin which allows you to add simple emoticons to TinyMCE.

See the Pen TinyMCE Basic Example with Emoticons Plugin by Paper Knees (@paperknees) on CodePen.

That’s a pretty limited selection of emojis for people used to the vast selection available these days on smart phones.

EmojiOne

At around the time I started using TinyMCE on something I was tinkering with I came across EmojiOne.

EmojiOne provide a huge selection of Emojis which are more in line with those people are familiar with you use smart phones, where the emojis are split into standard categories, such as people, activities, travel, food, nature, symbols, flags etc.

As well as providing the artwork files, they also have a tonne of useful resources which can be incorporated into websites, as described on this EmojiOne Github page.

EmojiOne Versions

EmojiOne makes their content available as packaged releases, in much the same way software providers release software in major and minor releases.

Version 1 was released on 4th September 2014. As at the end of October 2017, Release 3.1.2 is the latest version.

Up to and including Version 2.2.7 it used to be possible to download the EmojiOne emojis in various forms, such as:

  • PNG, 64 * 64
  • PNG, 64 * 64, Black and White
  • PNG, 128 * 128
  • PNG, 512 * 512
  • Sprites
  • SVG
  • SVG – Black and White

From Version 3 onwards, the free version includes PNG files only, in three sizes (32*32, 64*64 and 128*128), which is fair enough as a lot of work must go into creating and maintaining such a huge library of images.

For example – here are two of the same emojis, set at 256 pixels in the image definition in the HTML tag for the image:

PNG SVG
PNG SVG

The advantage of the SVG versions is that they can be scaled up without losing any of their quality, since they are Scalable Vector Graphics.

EmojiOne via CDN

Another great thing about EmojiOne (version 2) is that it’s possible to use the PNG and SVG images hosted on CDNs, or Content Delivery Networks. That means if the images are hosted on a CDN, they are being downloaded from a fast website, and any bandwidth being used up from downloading the images is not taken up on your hosting package, but on the CDNs. I haven’t found anywhere hosting the PNGs from Version 3 yet.

I have used cloudflare.com and jsdelivr.net – for example:

  1. https://cdnjs.cloudflare.com/ajax/libs/emojione/2.2.7/assets/svg/1f60e.svg
  2. https://cdnjs.cloudflare.com/ajax/libs/emojione/2.2.7/assets/png/1f60e.png
  3. https://cdn.jsdelivr.net/emojione/assets/svg/1f60e.svg
  4. https://cdn.jsdelivr.net/emojione/assets/png/1f60e.png

I wanted to work out how to integrate EmojiOne emojis with TinyMCE – I’ve done two versions, one for Version 2.2.7 (with SVGs) and another for Version 3.1 (with PNGs).

I’m not a programmer, so my disclaimer here is the process I followed is probably basic and full of flaws, but it works for me.

Step 1 – Create a MySQL table to store the EmojiOne Emoji details

Since I’m working with 2 different versions of EmojiOne, I want to split out the data into 2 different tables, so I store the details of the emojis in these MySQL tables:

  1. Version 2.2.7: xx_emoji_2
  2. Version 3 onwards: xx_emoji_3

Table definitions:


-- For version 2.2.7:

CREATE TABLE `xx_emoji_2` (
  `fld_id` int(11) NOT NULL AUTO_INCREMENT,
  `unicode` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `shortname` varchar(255) DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `emoji_order` int(5) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `fld_last_used` datetime DEFAULT NULL,
  `fld_use_count` int(20) DEFAULT '0',
  PRIMARY KEY (`fld_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- For version 3 onwards:

CREATE TABLE `xx_emoji_3` (
  `fld_id` int(11) NOT NULL AUTO_INCREMENT,
  `unicode` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `shortname` varchar(255) DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `emoji_order` int(5) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `fld_last_used` datetime DEFAULT NULL,
  `fld_use_count` int(20) DEFAULT '0',
  PRIMARY KEY (`fld_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The next step covers populating the tables with data.

Step 2 – Populate the Database Table

I’ve provided 2 sets of instructions here, since the process varies slightly depending on the EmojiOne version you decide to use.

2.1 – Populate the table by looping through Version 2.2.7 emoji.json file with PHP

The advantage of Version 2.2.7 is that the SVG files are still available to be used for free (with attribution), and are available via a CDN.

Verion 2.2.7 includes 1,832 files, but only 1,820 are categorised in the json file. Each of the files belongs to a category, and has associated information with it, such as its name, unicode, shortcode etc.

Thankfully EmojiOne releases contain a JSONEmojiOne page on Github.

Once unzipped, in the root of the folder there’s a file called emoji.json. This file contains details for each emoji. Here’s the structure used, for a single Emoji:


/* Version 2.27 */
{
   "grinning":{
      "unicode":"1f600",
      "unicode_alt":"",
      "code_decimal":"😀",
      "name":"grinning face",
      "shortname":":grinning:",
      "category":"people",
      "emoji_order":"1",
      "aliases":[

      ],
      "aliases_ascii":[

      ],
      "keywords":[
         "happy",
         "smiley",
         "emotion"
      ]
   }
}

Each release also has a emoji_strategy.json file – I’m not sure why you’d use one instead of the other, but this is the format used for a single emoji, from that file:


/* Version 2.2.7 */
{
   "grinning":{
      "unicode":"1f600",
      "shortname":":grinning:",
      "aliases":"",
      "keywords":"grinning face happy smiley emotion"
   }
}

I saved that file into the relevant folder and then run some PHP code to loop through the JSON file and load the records into the MySQL table.

// link to connection file
// ... not included here

// point to the json file
$str = file_get_contents('http://localhost:8080/public_html/_dev/emoji/2.2.7/emoji.json');

// Takes a JSON encoded string and converts it into a PHP variable:
$json_a = json_decode($str, true);

// loop through the JSON data:
foreach($json_a as $key => $val) {
    
	// assign variables and give them values based on the JSON data:
	$unicode = $val['unicode'];
    $name = $val['name'];
    $shortname = $val['shortname'];
    $category = $val['category'];
    $emoji_order = $val['emoji_order'];
    $keywords = implode(',', $val['keywords']);

	// define the SQL statement
	$sql = "INSERT INTO xx_emoji_2 (unicode
								 , name
								 , shortname
								 , category
								 , keywords
								 , emoji_order) 
						   VALUES (:unicode
								 , :name
								 , :shortname
								 , :category
								 , :keywords
								 , :emoji_order)";
	
	// load the data into the MySQL table using PDO
	
	$stmt = $pdo->prepare($sql);
	
	$stmt->bindParam(':unicode', $unicode);
	$stmt->bindParam(':name', $name);
	$stmt->bindParam(':shortname', $shortname);
	$stmt->bindParam(':category', $category);
	$stmt->bindParam(':keywords', $keywords);
	$stmt->bindParam(':emoji_order', $emoji_order);
	
	$stmt->execute();

}

2.2 – Populate the table by looping through Version 3 emoji.json file with PHP

The advantage of version 3 is that it contains many more emoji designs, and the whole set has been redesigned with a new look and feel. The downside is that the files are only available as PNG files for free.

Verion 3.1.2 includes 2,666 files. Each of the files belongs to a category, and has associated information with it, such as its name, unicode, shortcode etc.

Thankfully EmojiOne releases contain a JSON file, which provides detailed information about each emoji. Therefore I downloaded the release zip file from the EmojiOne page on Github.

Once unzipped, in the root of the folder there’s a file called emoji.json. This file contains details for each emoji. Here’s the structure used, for a single Emoji (note the format is slightly different to that used in version 2.2.7:


/* Version 3.1.2 */
{
   "1f600":{
      "name":"grinning face",
      "unicode_version":6.1,
      "category":"people",
      "order":1,
      "display":1,
      "shortname":":grinning:",
      "shortname_alternates":[

      ],
      "ascii":[

      ],
      "diversity":null,
      "diversities":[

      ],
      "gender":null,
      "genders":[

      ],
      "code_points":{
         "base":"1f600",
         "fully_qualified":"1f600",
         "non_fully_qualified":"1f600",
         "output":"1f600",
         "default_matches":[
            "1f600"
         ],
         "greedy_matches":[
            "1f600"
         ],
         "decimal":""
      },
      "keywords":[
         "face",
         "grin"
      ]
   }
}

Each release also has a emoji_strategy.json file – I’m not sure why you’d use one instead of the other, but this is the format used for a single emoji, from that file:


/* Version 3.1.2 */
{
   "1f600":{
      "name":"grinning face",
      "shortname":":grinning:",
      "shortname_alternates":[

      ],
      "keywords":[
         "face",
         "grin"
      ],
      "unicode_output":"1f600"
   }
}

I saved that file into the relevant folder and then run some PHP code to loop through the JSON file and load the records into the MySQL table.

// link to connection file
// ... not included here

// point to the json file
$str = file_get_contents('http://localhost:8080/public_html/_dev/emoji/emoji_3.1.json');

// Takes a JSON encoded string and converts it into a PHP variable:
$json_a = json_decode($str, true);

// loop through the JSON data:
foreach($json_a as $key => $val) {
    
	$unicode = $val['code_points']['base'];
    $name = $val['name'];
    $shortname = $val['shortname'];
    $category = $val['category'];
    $emoji_order = $val['order'];
    $keywords = implode(',', $val['keywords']);
	 
     $sql = "INSERT INTO xx_emoji_3 (unicode
								 , name
								 , shortname
								 , category
								 , keywords
								 , emoji_order) 
						   VALUES (:unicode
								 , :name
								 , :shortname
								 , :category
								 , :keywords
								 , :emoji_order)"; 
	
 	$stmt = $pdo->prepare($sql);
	
	$stmt->bindParam(':unicode', $unicode);
	$stmt->bindParam(':name', $name);
	$stmt->bindParam(':shortname', $shortname);
	$stmt->bindParam(':category', $category);
	$stmt->bindParam(':emoji_order', $emoji_order);
	$stmt->bindParam(':keywords', $keywords);
	
	$stmt->execute();

}

Step 3 – Decide which TinyMCE Plugins to create

Once data was available in a database table, I started looking at how to edit TinyMCE to access it.

To create new buttons on the TinyMCE toolbar, I needed to write some new TinyMCE Plugins.

I’m not a programmer, as I said, so had to hack about to get this to work.

Each emoji belongs to a category. You can see the count of emojis per category via this SQL:

       SELECT category
	    , COUNT(*)
	  FROM xx_emoji_2
      GROUP BY category
      ORDER BY category;
category  count(*)  
--------  ----------
activity         139
flags            257
food              85
modifier           5
nature           160
objects          180
people           575
regional          26
symbols          275
travel           118

A lot of emojis are available in 6 different skin tonesfor example:

SVG SVG SVG SVG SVG SVG

Therefore the SQL can be revised to ignore the skin tone duplicates, assuming that’s what you want to do:

       SELECT category
	    , COUNT(*)
	  FROM xx_emoji_2
	 WHERE `unicode` NOT LIKE '%-1f3f%' 
      GROUP BY category
      ORDER BY category;
category  COUNT(*)  
--------  ----------
activity          69
flags            257
food              85
modifier           5
nature           160
objects          180
people           225
regional          26
symbols          275
travel           118

I then had a better idea about the categories I wanted to work with.

Step 4 – Decide which category icons to use for the TinyMCE buttons

I wanted to a button on the TinyMCE toolbar for each EmojiOne category.

There are various options for adding images to TinyMCE buttons – I chose to edit some of the core TinyMCE CSS and use icons from Font Awesome.

However, I also found that you can just add them directly into your plugin Javascript – for example:

    a.addButton("emojione_2_activity", {
        type: "panelbutton",
		image: 'https://oracle101.co.uk/assets/images/category_icons/v2/symbols.png',
		style: 'padding-top:4px;',
        panel: {
            role: "application",
            autohide: !0,
            html: c,
            onclick: function(b) {
                var c = a.dom.getParent(b.target, "a");
                c && (a.insertContent('' + c.getAttribute('), this.hide())
            }
        },
        tooltip: "EmojiOne - v2 - Activity"
    })

Anyway, back to using the FontAwesome method…

I had seen that it was possible to add a custom icon to TinyMCE from a question on Stack Overflow.

From the SQL above, I could see that EmojiOne’s emojis belonged to 9 categories (I didn’t create a category for ‘modifier’).

I also wanted to work out how to add “Recent” and “Popular” categories to match the type of functionality that users are familiar with on smart phones, so I needed icons for new buttons.

I had to get the CSS rule value for the Font Awesome icon I wanted to use, so used the Font Awesome Cheat Sheet to search for the icons and then copy the rule.

In the end I decided on the following:

Category Font Awesome Code Icon
Popular content: "\f077";
Recent content: "\f017";
People content: "\f118";
Nature content: "\f1b0";
Food content: "\f179";
Activity content: "\f1e3";
Travel and Places content: "\f1b9";
Objects content: "\f0eb";
Symbols content: "\f295";
Flags content: "\f024";
Letters content: "\f031";

Step 5 – Edit TinyMCE CSS files

In order for the icons to be available for TinyMCE to use, two CSS files need to be edited.

  1. tinymce\skins\lightgray\skin.min.css
  2. tinymce\skins\lightgray\skin.mobile.min.css

I wanted to split out the Version 2 and 3 emojis, so I added the following to the end of each CSS file (the versions only different in that the version 3 icons are blue, version 2 are black):

/* ###################################################################

   TinyMCE Buttons for EmojiOne Plugins, using FontAwesome Icons
   EmojiOne Version 2
   http://stackoverflow.com/questions/25191791/adding-a-custom-icon-to-a-tinymce-button 
   
   ###################################################################*/

/* poular */
.mce-i-emojione_2__popular:before {
    content: "\f077";
    font-family: FontAwesome;
	color:#000;
}

/* recent */
.mce-i-emojione_2__recent:before {
    content: "\f017";
    font-family: FontAwesome;
	color:#000;
}

/* people */
.mce-i-emojione_2_people:before {
    content: "\f118";
    font-family: FontAwesome;
	color:#000;
}

/* nature */
.mce-i-emojione_2_nature:before {
    content: "\f1b0";
    font-family: FontAwesome;
	color:#000;
}

/* food */
.mce-i-emojione_2_food:before {
    content: "\f179";
    font-family: FontAwesome;
	color:#000;
}

/* activity */
.mce-i-emojione_2_activity:before {
    content: "\f1e3";
    font-family: FontAwesome;
	color:#000;
}

/* travel */
.mce-i-emojione_2_travel:before {
    content: "\f1b9";
    font-family: FontAwesome;
	color:#000;
}

/* objects */
.mce-i-emojione_2_objects:before {
    content: "\f0eb";
    font-family: FontAwesome;
	padding-left:3px;
	color:#000;
}

/* symbols */
.mce-i-emojione_2_symbols:before {
    content: "\f295";
    font-family: FontAwesome;
	color:#000;
}

/* flags */
.mce-i-emojione_2_flags:before {
    content: "\f024";
    font-family: FontAwesome;
	color:#000;
}

/* letters */
.mce-i-emojione_2_letters:before {
    content: "\f031";
    font-family: FontAwesome;
	color:#000;
}

/* ###################################################################

   TinyMCE Buttons for EmojiOne Plugins, using FontAwesome Icons
   EmojiOne Version 3
   http://stackoverflow.com/questions/25191791/adding-a-custom-icon-to-a-tinymce-button 
   
   ###################################################################*/

/* poular */
.mce-i-emojione_3__popular:before {
    content: "\f077";
    font-family: FontAwesome;
	color:#306090;
}

/* recent */
.mce-i-emojione_3__recent:before {
    content: "\f017";
    font-family: FontAwesome;
	color:#306090;
}

/* people */
.mce-i-emojione_3_people:before {
    content: "\f118";
    font-family: FontAwesome;
	color:#306090;
}

/* nature */
.mce-i-emojione_3_nature:before {
    content: "\f1b0";
    font-family: FontAwesome;
	color:#306090;
}

/* food */
.mce-i-emojione_3_food:before {
    content: "\f179";
    font-family: FontAwesome;
	color:#306090;
}

/* activity */
.mce-i-emojione_3_activity:before {
    content: "\f1e3";
    font-family: FontAwesome;
	color:#306090;
}

/* travel */
.mce-i-emojione_3_travel:before {
    content: "\f1b9";
    font-family: FontAwesome;
	color:#306090;
}

/* objects */
.mce-i-emojione_3_objects:before {
    content: "\f0eb";
    font-family: FontAwesome;
	padding-left:3px;
	color:#306090;
}

/* symbols */
.mce-i-emojione_3_symbols:before {
    content: "\f295";
    font-family: FontAwesome;
	color:#306090;
}

/* flags */
.mce-i-emojione_3_flags:before {
    content: "\f024";
    font-family: FontAwesome;
	color:#306090;
}

/* letters */
.mce-i-emojione_3_letters:before {
    content: "\f031";
    font-family: FontAwesome;
	color:#306090;
}

Step 6 – Include FontAwesome CSS

The icons will not appear until you have included the FontAwesome CSS file in the head tags of the page that contains your TinyMCE form. Therefore, make sure to include a link to the relevant CSS file with something like this:

Once that had been done, I started looking at the process of creating the Plugins.

Step 7 – Review the standard Emoticons Plugin

TinyMCE Plugins are stored in the tinymce\plugins directory – the emoticons plugin is in tinymce\plugins\emoticons

The standard format on a plugin is that the name of the plugin will be the same as it’s plugin folder.

Therefore all Plugins have the same naming convention – they all contain a file inside the Plugin folder called plugin.min.js.

The “out of the box” emoticons plugin’s plugin.min.js file looked like this in Version 4.3.12 of TinyMCE – it has since changed (as of November 2017):

tinymce.PluginManager.add("emoticons", function(a, b) {
    function c() {
        var a;
        return a = '', tinymce.each(d, function(c) {
            a += "", tinymce.each(c, function(c) {
                var d = b + "/img/smiley-" + c + ".gif";
                a += ''
            }), a += ""
        }), a += "
" } var d = [ ["cool", "cry", "embarassed", "foot-in-mouth"], ["frown", "innocent", "kiss", "laughing"], ["money-mouth", "sealed", "smile", "surprised"], ["tongue-out", "undecided", "wink", "yell"] ]; a.addButton("emoticons", { type: "panelbutton", panel: { role: "application", autohide: !0, html: c, onclick: function(b) { var c = a.dom.getParent(b.target, "a"); c && (a.insertContent('' + c.getAttribute('), this.hide()) } }, tooltip: "Emoticons" }) });

This is my non-programmers way of explaining what the code does:

  1. The list of emoticons are defined in the d variable – e.g. cool, cry, embarassed etc.
  2. The c function loops through each set of square brackets in d and for each row, it creates a set of code for an HTML table cell. Therefore in this case you can see that the resulting table will contain 4 rows, each containing 4 emojis, since there are 4 rows of square brackets, each containing 4 values.
  3. It does a whole load of other stuff which I’m not smart enough to explain, though I have a tentative understanding of it…

Step 8 – Create a Custom EmojiOne Plugin

There are 2 ways I could approach this:

  1. Create a plugin where the list of emojis were hard coded in the JS file
    • Advantage:
      • The emojis are diplayed faster as they don’t need to be extracted from a server-side script
      • No back-end server-side scripting required
    • Disadvantage:
      • Long-winded manual process required to define the hard-coded values
      • Any changes to the content would require manual editing of the plugin JS file
      • Though you might not need to make changes very often, so the above 2 disadvantages aren’t major
  2. Create a plugin where the list of emojis were returned by a server-side script (e.g. php):
    • Advantage:
      • Changes to the content are easily made by changing the query in the PHP file or the contents of the database table linked to the PHP file
    • Disadvantage:
      • Back-end server-side scripting required

I have documented both routes here.

8.1 – PHP Version

In order to create my own version of the plugin using a PHP script to deliver the list of emojis, this is what I wanted it to do:

  1. Populate the list of emoji (variable d) from the database table – using an Ajax Request. That is – load the list of emoji into TinyMCE from the MySQL table without reloading the entire page each time I wanted to click on an emoji toolbar button
  2. Take data from the emoji database table and also include HTML title and alt tags.
  3. Use the SVG versions of the EmojiOne emojis, hosted on JSDelivr.

This is the code from the plugin.min.js used on the food plugin:

tinymce.PluginManager.add("newmoji_food", function(a, b) {
    function c() {
        var a;
        
        return a = '', tinymce.each(food, function(c) {

            a += "", tinymce.each(c, function(c) {

                var str_len = c.length;
                var pipe_pos = c.indexOf("|");
                var var_unicode = c.substr(0, pipe_pos);
                var var_title = c.substr(pipe_pos+1, str_len);
                var img_src = "https://cdn.jsdelivr.net/emojione/assets/svg/" + var_unicode + ".svg";

                a += ''

            }), a += ""
        }), a += "
" } tinymce.util.XHR.send({ url: 'emoji.php?cat=food', success: function(returnedData) { MyData = JSON.parse(returnedData); food = MyData; } }); a.addButton("newmoji_food", { type: "panelbutton", panel: { role: "application", autohide: !0, html: c, onclick: function(b) { var c = a.dom.getParent(b.target, "a"); c && (a.insertContent(''), this.hide()) } }, tooltip: "Emoticons - Food" }) });

I got it working in the end – it was a process of trial and error.

Key points:

  1. You need a emoji.php PHP page to be in the same directory as the page that your TinyMCE form is on, not in the directory that the plugin JS file is located in. So if your TinyMCE form is on example.com/working/test.php, then the emoji file needs to be in the working folder.
  2. Lines 1 and 27 contain the name of the Plugin – newmoji_food in this case, and must contain the same values, and match the name of the plugin used when you edited your CSS in Step 6, plus must match the folder name for the plugin.
  3. Lines 5 and 24 both contain an internal variable, food which must be a unique name across your emoji plugins. The value must be the same in lines 5 and 24 within each plugin.
  4. Line 13 contains the base link to the SVG images on JSDelivr.
  5. The data returned by the PHP file contains the emoji unicode name (e.g. 1f93e) and the text for the emoji’s title attribute (e.g. handball), each separated by a pipe (|)
  6. The code in the plugin splits out each emoji, assigning the unicode to one variable (var_unicode) and the title text to another (var_title).
  7. The plugin ensure that each emoji has a title attribute so that when you mouse over it after clicking a plugin button, the title attribute provides details about the emoji.
  8. The code in the plugin inserts title and alt tags into the HTML for an image into the HTML in the TinyMCE content.

The completed plugins are all available to download at the end of this post.

Create PHP file to read the data from the MySQL table

The plugins all use data from the emoji.php page. That returns the data from the table and provides it in the correct format for the plugin to split out the data and display it correctly in TinyMCE.

// link to connection file
// ... not included here

// get the querystring value
$cat = $_GET['cat'];

if (isset($_GET['cat'])) {

	// based on the category value, define the SQL statement to run
	if ($cat == "recent") {
		$sql = "SELECT unicode, name, fld_last_used FROM xx_emoji WHERE fld_last_used IS NOT NULL ORDER BY fld_last_used DESC LIMIT 72";
	} elseif ($cat == "popular") {
		$sql = "SELECT unicode, name, fld_use_count FROM xx_emoji WHERE fld_use_count > 0 ORDER BY fld_use_count DESC LIMIT 72";
	} elseif ($cat == "regional") {
		$sql = "SELECT unicode, fld_id, name FROM xx_emoji WHERE category = :cat AND unicode NOT LIKE '%-1f3f%' ORDER BY fld_id DESC";
	} else {
		$sql = "SELECT unicode, name FROM xx_emoji WHERE category = :cat AND unicode NOT LIKE '%-1f3f%'";
	}
	
	// run the SQL statement using PDO
	$stmt = $pdo->prepare($sql);
	$stmt->bindParam(':cat', $cat);
	$stmt->execute();
	$ct = $stmt->rowCount();

	$str = NULL;
	$max = $ct;
	$row_count = 18; // split the results into columns of 18 - change to suit your own requirements
	$i = 1;

	while ($row = $stmt->fetch()){
		
		// join unicode and name together, with pipe delimiter
		
		$emoji = $row['unicode'];
		$emoji_name = $row['name'];

		// for recent category, include the date the emoji was last used
		if ($cat == "recent") {
			$emoji_name = $row['name'] . " @ " . $row['fld_last_used'];
		}

		// for popular category, include the emoji's use count
		if ($cat == "popular") {
			$emoji_name = $row['name'] . ": " . $row['fld_use_count'];
		}

		// join the emoji unicode and name variables, split by the pipe
		$new = $emoji . "|" . $emoji_name;
		
		// formatting to ensure that the data is split into blocks of 18, each with a square bracket at the start end end
		$rem = $i % $row_count;
		
		if ($rem === 1) {
			$str .= "[";
		}
		
		if ($rem > 0 && $i != $max) {
			$str .= "\"$new\",";
		} else {
			$str .= "\"$new\"";
		}

		// if we still have more records to return, and are at the end of the block of 18, add a comma after the ending square bracket
		if ($rem === 0 && $i < $max) {
			$str .= "],\n";
		}
		
		// otherwise, just display the square bracket
		if ($i === $max) {
			$str .= "]\n";
		}
		
		$i++;
		
	}

	// wrap all of the output in square brackets again
	$str = "[" . $str . "]";

	// print the output to the page
	echo $str;

}

It might be that you don't want to use a scripting page to populate the list of emojis in each category, because you might not have access to a server to run a scripting language on, or might think it's overkill to use this method. If that's the case I've provided versions of the plugins which have the emoji files hard-coded in the javascript.

If you don't like the way the hard-codinng has been done, in that

Track emoji usage in xx_emoji table

In order for the emoticons_00_recent and emoticons_00_popular plugins to work, we need to track the usage of each emoji as the TinyMCE form data is submitted.

We can then track the use count for each emoji, and the last used data.

This is a summary of how I do it.

// ###############################################
// 	PROCESS FORM DATA
// ###############################################

if ($_SERVER['REQUEST_METHOD'] == 'POST') {

	$mode = $_POST['method'];
	
	if ($mode = "send") {
		
		$config = 	HTMLPurifier_Config::createDefault(); // assumes you are using HTMLPurifier https://github.com/ezyang/htmlpurifier
		$purifier = new HTMLPurifier($config);
		
		$recBody = 	$_POST['recBody'];
		$recBody = 	str_replace('

 

','',$recBody); $recBody = $purifier->purify($recBody); // get other form data and save in relevant database table //track emojis $doc = new DOMDocument(); $doc->loadHTML($recBody); $imageTags = $doc->getElementsByTagName('img'); // look at img tags foreach($imageTags as $tag) { $img = basename($tag->getAttribute('src')) . PHP_EOL; // get data from src section $img = trim(str_replace('.svg', '', $img)); // look for the.svg file extension in the img src // search to see if that .svg file is a defined emoji $sql2 = "SELECT unicode FROM xx_emoji WHERE unicode = :img"; $stmt2 = $pdo->prepare($sql2); $stmt2->bindParam(':img', $img); $stmt2->execute(); $row = $stmt2->fetch(PDO::FETCH_ASSOC); // if it is, update the stats... if ($row) { $sql3 = "UPDATE xx_emoji SET fld_use_count = fld_use_count + 1, fld_last_used = now() WHERE unicode = :img"; $stmt3 = $pdo->prepare($sql3); $stmt3->bindParam(':img', $img); $stmt3->execute(); } } // do other stuff if required } }

8.2 - Hard-Coded Version

The JS for the plugin is very similar to that uses above - the difference is that this section:

    tinymce.util.XHR.send({
        url: 'emoji.php?cat=food',
        success: function(returnedData) {
			MyData = JSON.parse(returnedData);
            food = MyData;
        }
    });

Is replaced with:

	var food = [
    [
        "1f347|grapes",
        "1f348|melon",
        "1f349|watermelon",
        "1f34a|tangerine"
    ]
];

Except in the working version, the list of items in the food variable is a lot longer. I shortened it above to make the code more readable.

That's all there is to it.

Rather than me list all of the code above, you can download the final version of the hard-coded plugins at the end of this post.

Step 9 - Download

Download

I have bundled together the files for this post into the zip file you can download via the button above.

This is a summary of the content of the zip file:

/*
|   
+---css
|       css-for-icons.css			contains CSS to create the toolbar icons
|       
+---php_json
|       emoji_2.php					php page to serve ajax content for version2 emoji designs
|       emoji_3.php					php page to serve ajax content for version3 emoji designs
|       emoji_load_2.php			php page to parse emojione version 2.2.7 emoji.json file and save data into a database table
|       emoji_load_3.php			php page to parse emojione version 3 emoji.json file and save data into a database table
|       init.php					php page containing connection to mySQL database and PDO config
|       
+---plugins
|       plugins-dynamic.zip			zipped collection of 22 plugins, where content is created via ajax-linked PHP pages (emoji_2.php and emoji_3.php above)
|       plugins-hard-coded.zip		zipped collection of 22 plugins, where content is created via hard-coded lists of emoji designs (apart from the popular / recent plugins)
|       
\---sql
        emojione-v2-data.sql		SQL to create version 2 MySQL table and populate with 1,820 emojis
        emojione-v3-data.sql		SQL to create version 3 MySQL table and populate with 2,666 emojis

*/

Step 10 - Other Stuff

Category Lists

You can hack about with the category lists to suit - I realise the number of emojis in each category is pretty huge and unwieldy.

Inserted Image Dimensions

The size of the images once inserted into the TinyMCE content window are probably too big.

You can edit that in the `plugin.min.js` file each each plugin directory, by setting the dimensions on this line:

c && (a.insertContent('' + c.getAttribute('), this.hide())

Auto Smiley Conversion

Unfortunately this solution doesn't include anything fancy like auto smiley conversion. I've seen other solutions offering the same, such as the Smileys project on Github. I'm not smart enough to figure out how to do that.

Future Plans

I'd really like to be able to add another plugin which allows users to be able to easily search through the emojis, in much the same way has been done on the react emoji picker for use with emojione, but I have no idea how I'd start to go about that.

I have a rough idea how to go about it, but am stuck on that as at the time of writing this on 19th November 2017. I asked about it on Stackoverflow but haven't figured it out yet.

License

MIT License

Emojione is used under the Creative Commons License (CC-BY 4.0) - If you use these plugins you should also include attribute to Emojione somewhere within your website or application to satisfy the terms of the license.

AP Suppliers

Suppliers – Basic

-- ##############################################################################
--      SUPPLIERS - BASIC
-- ############################################################################*/
 
        SELECT bus_gp.NAME org
             , pv.segment1, pv.vendor_id
             , pv.vendor_name
             , pvsa.vendor_site_code site
             , pvsa.vendor_site_id
             , pvsa.org_id
             , pvsa.supplier_notif_method
             , pvsa.email_address email
             , pvsa.invoice_currency_code
             , pvsa.payment_currency_code
             , fu.description last_udated_by
             , pv.end_date_active header_end_date
             , pvsa.inactive_date site_end_date
          FROM ap.ap_suppliers pv
          JOIN ap.ap_supplier_sites_all pvsa ON pv.vendor_id = pvsa.vendor_id
          JOIN hr.hr_all_organization_units_tl bus_gp ON pvsa.org_id = bus_gp.organization_id
          JOIN applsys.fnd_user fu ON pvsa.last_updated_by = fu.user_id
         WHERE 1 = 1
--           AND pvsa.vendor_site_code = :site_code
           AND pv.segment1 = :supplier_number
      ORDER BY bus_gp.NAME
             , pv.vendor_name
             , pvsa.vendor_site_code;

Suppliers – Contact Details

-- ##############################################################################
--      SUPPLIERS - INCLUDING CONTACT DETAILS
-- ############################################################################*/
 
    SELECT   pv.segment1 supplier_num
           , pv.vendor_name
           , (SELECT COUNT(*) po_ct
                FROM po.po_headers_all pha
               WHERE pha.vendor_id = pv.vendor_id
                 AND pha.vendor_site_id = pvsa.vendor_site_id
                 AND pha.type_lookup_code = 'STANDARD'
                 AND pha.creation_date >= SYSDATE - 365) po_count_last_365_day
           , pv.creation_date
           , pv.vendor_type_lookup_code TYPE
           , pvsa.vendor_site_code site_name
           , pvsa.purchasing_site_flag flag_purch
           , pvsa.rfq_only_site_flag flag_rfq
           , pvsa.pay_site_flag flag_pay
           , pvsa.address_line1 site_add_1
           , pvsa.address_line2 site_add_2
           , pvsa.address_line3 site_add_3
           , pvsa.city site_city
           , pvsa.state site_state
           , pvsa.zip site_zip
           , pvsa.email_address site_email_address
           , '----------'
           , fu.description site_last_updated_by
           , pvsa.last_update_date site_last_updated_on
           , pvc.first_name contact_first_name
           , pvc.last_name contact_last_name
           , pvc.phone contact_phone
           , pvc.email_address contact_email
        FROM ap.ap_suppliers pv
        JOIN ap.ap_supplier_sites_all pvsa ON pv.vendor_id = pvsa.vendor_id
   LEFT JOIN apps.po_vendor_contacts pvc ON pvsa.vendor_site_id = pvc.vendor_site_id
        JOIN applsys.fnd_user fu ON pvsa.last_updated_by = fu.user_id
       WHERE pv.end_date_active IS NULL
         AND pvsa.inactive_date IS NULL
         AND pv.vendor_name LIKE 'A%'
--         AND ((pvsa.email_address IS NOT NULL) OR (pvc.email_address IS NOT NULL))
    ORDER BY pv.vendor_name;

Suppliers – Site Count

-- ##############################################################################
--      SUPPLIERS - SITE COUNT
-- ############################################################################*/
 
SELECT   pv.vendor_name
       , pv.vendor_type_lookup_code
       , pv.segment1 supplier_number
       , COUNT(pvsa.vendor_site_id) site_count
    FROM ap.ap_suppliers pv
    JOIN ap.ap_supplier_sites_all pvsa ON pv.vendor_id = pvsa.vendor_id
   WHERE NVL(pv.end_date_active, SYSDATE + 1) > SYSDATE
     AND NVL(pvsa.inactive_date, SYSDATE + 1) > SYSDATE
     AND pv.vendor_type_lookup_code = 'VENDOR'
GROUP BY pv.vendor_name
       , pv.vendor_type_lookup_code
       , pv.segment1
ORDER BY pv.vendor_name;

Suppliers – Invoice and Purchase Order Count

-- ##############################################################################
--      SUPPLIERS - INVOICE AND PURCHASE ORDER COUNT
-- ############################################################################*/
 
  SELECT pv.vendor_name
       , pv.vendor_type_lookup_code
       , pv.segment1 supplier_number
       , pvsa.vendor_site_code
       , pvsa.supplier_notif_method
       , pvsa.email_address email_po_address
       , pv.creation_date header_creation_date
       , pvsa.creation_date site_creation_date
       , pvsa.purchasing_site_flag
       , pvsa.pay_site_flag
       , pvsa.address_line1
       , pvsa.address_line2
       , pvsa.address_line3
       , pvsa.city
       , pvsa.state
       , pvsa.zip
       , (SELECT COUNT(*)
            FROM po.po_headers_all pha
           WHERE pha.vendor_id = pv.vendor_id
             AND pha.vendor_site_id = pvsa.vendor_site_id
             AND pha.creation_date >= :dt) po_count
       , (SELECT MAX(pha.creation_date)
            FROM po.po_headers_all pha
           WHERE pha.vendor_id = pv.vendor_id
             AND pha.vendor_site_id = pvsa.vendor_site_id
             AND pha.creation_date >= :dt) latest_po_date
       , ROUND(SYSDATE
            - (SELECT MAX(pha.creation_date)
                 FROM po.po_headers_all pha
                WHERE pha.vendor_id = pv.vendor_id
                  AND pha.vendor_site_id = pvsa.vendor_site_id
                  AND pha.creation_date >= :dt)
          , 2) days_since_last_po
       , (SELECT COUNT(*)
            FROM ap.ap_invoices_all aia
           WHERE aia.vendor_id = pv.vendor_id
             AND aia.vendor_site_id = pvsa.vendor_site_id
             AND aia.creation_date >= :dt) inv_count
       , (SELECT MAX(aia.creation_date)
            FROM ap.ap_invoices_all aia
           WHERE aia.vendor_id = pv.vendor_id
             AND aia.vendor_site_id = pvsa.vendor_site_id
             AND aia.creation_date >= :dt) latest_inv_date
       , ROUND(SYSDATE
            - (SELECT MAX(aia.creation_date)
                 FROM ap.ap_invoices_all aia
                WHERE aia.vendor_id = pv.vendor_id
                  AND aia.vendor_site_id = pvsa.vendor_site_id
                  AND aia.creation_date >= :dt)
          , 2) days_since_last_inv
    FROM ap.ap_suppliers pv
    JOIN ap.ap_supplier_sites_all pvsa ON pv.vendor_id = pvsa.vendor_id
   WHERE NVL(pv.end_date_active, SYSDATE + 1) > SYSDATE
     AND NVL(pvsa.inactive_date, SYSDATE + 1) > SYSDATE
     AND pv.vendor_name LIKE 'Blue%'
     AND pv.vendor_type_lookup_code = 'VENDOR'
ORDER BY pv.vendor_name

Application Accounting Definitions

Sometimes when you apply a big patch (e.g. RPC patches), Application Accounting Definitions can become invalid.

After the patching you then have to run the “Validate Application Accounting Definitions” job to validate the definitions again.

I find it can be useful to run the Count SQL below to compare which definitions are invalid on the patched instance, with PROD, to see what has changed, and to make sure things line up again after you have run the “Validate Application Accounting Definitions” job.

-- ##################################################################
-- Application Accounting Definitions
-- ##################################################################
 
select xprf.application_name appl
--     , xprf.*
     , xprf.description
     , xprf.compile_status_dsp hdr_status
     , xprf.enabled_flag enbl
     , xprf.last_update_date hdr_upd_dt
     , xpahf.event_class_name ev_class
     , xpahf.event_type_name ev_type
     , xpahf.accounting_required_flag crt_acct
     , xpahf.locking_status_flag locked
     , xpahf.validation_status_dsp status
     , xpahf.last_update_date upd_dt
  from apps.xla_product_rules_fvl xprf
     , apps.xla_prod_acct_headers_fvl xpahf
 WHERE xprf.application_id = xpahf.application_id
   AND xprf.product_rule_code = xpahf.product_rule_code
--   AND xprf.application_name = 'Assets'
   AND xprf.compile_status_dsp <> 'Valid'
--   AND xprf.description IN ('Standard Accounting for Inflation','Standard Accounting for United Kingdom Local Authorities')
--   AND xpahf.event_class_name = 'Borrowed and Lent'
--   AND xprf.description = 'China Projects Standard Accounting'
ORDER BY xprf.application_name
       , xprf.description
       , xpahf.event_class_name;
 
-- count summary
 
    select count(*) ct
         , xprf.application_name
         , xprf.compile_status_dsp
      from apps.xla_product_rules_fvl xprf
         , apps.xla_prod_acct_headers_fvl xpahf
     WHERE xprf.application_id = xpahf.application_id
       AND xprf.product_rule_code = xpahf.product_rule_code
       AND xprf.compile_status_dsp = 'Invalid'
  GROUP BY xprf.compile_status_dsp
         , xprf.application_name
  ORDER BY xprf.compile_status_dsp
         , xprf.application_name;
 
select xprf.application_name appl
     , xprf.description
     , count(*) ct
  from apps.xla_product_rules_fvl xprf
     , apps.xla_prod_acct_headers_fvl xpahf
 WHERE xprf.application_id = xpahf.application_id
   AND xprf.product_rule_code = xpahf.product_rule_code
   AND xprf.compile_status_dsp = 'Valid'
GROUP BY xprf.application_name
     , xprf.description
ORDER BY 3 DESC
       , xprf.application_name
     , xprf.description;
 
-- XLA Application
 
select application_id
     , application_name
     , name
     , compile_status_dsp
     , enabled_flag
     , last_update_date 
  from apps.xla_product_rules_fvl xprf;
 
select xpahf.*
from apps.xla_prod_acct_headers_fvl xpahf;

Making AR Transactions Credit Memo Reason field on ARXTWMAI mandatory

I recently had to personalize the Release 12.1.3 ARXTWMAI AR Transactions form, to make the Reason field on the Credit Memo section mandatory.

In the end I got this to work, by following the advice from both of these notes:

  • Forms Personalization Setup for Mandatory Credit Memo Reason Field Worked in 11i but Not in R12 (Doc ID 2124384.1)
  • Transactions Form Personalization: How to Make Reason Mandatory in Credit Transactions Form (Doc ID 1164213.1)

The following got it work without a problem:

Personalisation Header

Note – make sure Level is at Form, not Function level!

Set up these 2 Actions:

Personalisation Actions

Object Type: Item
Target Object: TCMI_HEADER.AL_REASON_MEANING
Property Name: REQUIRED (APPLICATIONS COVER)
Value: TRUE

Object Type: Item
Target Object: TCMI_HEADER.AL_REASON_MEANING
Property Name: REQUIRED (ITEM-INSTANCE)
Value: TRUE

Having the two together seemed to get things to work okay.

Now when I go to the Credit Memo form, the Reason field is yellow:

Add new Credit Memo Reasons in AR

I had to add some new Credit Memo Reasons in Release 12.1.3 for the Accounts Receivable module.

A colleague advised that could be done by going to:

Receivables Super User > Setup > Sytem > QuickCodes > Receivables

Once in that section, search for Type value of CREDIT_MEMO_REASON

Then add in new values, and you’re sorted.

Customise EBS Look and Feel

Background

When using different Oracle instances, errors can happen when changes are made if you mistakenly think you’re in a test system, but are logged into your production instance. There are a few options you can use to help differentiate between your PROD and other instances, both for your Core Applications forms sessions, and also your HTML based OAF pages.

Forms

Profile: Site Name

Risk can be reduced when using Core Applications Forms sessions, by using the Site Name Profile option. That then appears at the top of your Core Applications Forms:

Profile: Site Name

Profile: Java Color Scheme

You can also use the Java Color Scheme profile to change your forms to a different colour to your PROD instance, which helps differentiate between your instances. In Release 12, there are a number of different options:

Java Color Scheme Value Example
blue Blue
khaki Khaki
olive Olive
purple Purple
red Red
swan Swan
teal Teal
titanium Titanium

HTML / OAF Web Pages

Doing something similar with EBS Web pages isn’t as simple, but there are a still a number of options.

When viewing an HTML page in EBS, there is normally a “branding” image in the top-left of every page – for example:

FNDSSCORP

The standard branding image has a name of FNDSSCORP.gif and resides in the following directory: http(s)://your.ebs.site.com:/OA_MEDIA/FNDSSCORP.gif

Custom Logo Option

Say we have three PROD clones, called UAT1, UAT2, UAT3.

You could in theory create a new GIF image for each of those sites, with the site name written on them. Upload them to the OA_MEDIA directory. Then after a PROD clone on e.g. UAT1, you could update the Corporate Branding Image for Oracle Applications profile, so that it said “UAT1.gif”.

Then when you browse to e.g. iProc, you would see e.g:

Alternative Branding Image

Advantages

  1. System wide setting – works for all users and only requires 1 profile change per clone
  2. Quick and easy to set up

Disadvantages

  1. If you don’t have a solution in place already (e.g. your site’s cloned instances all have the same branding image), you would need to work with the DBAs to get new images in place for your cloned instances.
  2. Access required to upload files to the OA_MEDIA directory.
  3. The <title> HTML Attribute cannot be updated to include the Instance Name – e.g. it’d be useful for HTML Page Titles to start with the Instance Name, to provide additional clarification about the instance being used.
  4. Not all pages are updated via this profile option – pages like Application Diagnostics and Oracle Application Manager / OAM do not use the image defined in the “Corporate Branding Image for Oracle Applications” profile, but instead use the “FNDSSCORP.gif” header image. That cannot be replaced with a profile option, though you could in theory just overwrite it with a custom image.

Firefox and Greasemonkey Option

There is a Firefox Add On called Greasemonkey which allows users to install scripts that make on-the-fly changes to web page content after or before the page is loaded in the browser (also known as augmented browsing).

Greasemonkey scripts are written using Javascript. Each time an EBS page is loaded, Greasemonkey reads the user script, and processes the script to make changes to the way the HTML is rendered on the page.

Advantages

  1. Changes can be made on a per-user basis. User A might want different colours / messages / settings to User B.
  2. No changes are made to your EBS Instance. Changes are made on a per-session basis, local to each user’s browser session. The changes are not invasive.

Disadvantages

  1. Changes have to be made on a per-user basis – they cannot be applied centrally to a number of different computers
  2. The setup can be fiddly.
  3. The changes can only be made for people using the Firefox browser.

Key Step

The key thing that the script does is to look at the HTML title attribute of the header image on your EBS web pages, and it then replaces it with custom text that can include your instance name. You can also set a custom background colour for the text, which helps differentiate between your instances.

The header image and name are taken from your instance’s Corporate Branding Image for Oracle Applications profile value.

A quick way to find the value of the Corporate Branding Image for Oracle Applications profile option is to go to an OAF Web Page and put your mouse over the header image, right-click, and choose “Inspect Element”:

Inspect Element

Something like this will open:

HTML Snippet

If you set a custom image using the Corporate Branding Image for Oracle Applications profile, then the value of the profile will be the same as the value of the title attribute. For example, if you set the profile value to be XX_CUSTOM_LOGO.gif, then the HTML for the header image will be:

<img src="/OA_MEDIA/XX_CUSTOM_LOGO.gif" title="XX_CUSTOM_LOGO.gif" border="0">

Therefore the image title attribute value of “XX_CUSTOM_LOGO.gif” is what you’ll need to look for in your Greasemonkey Script.

However, if you don’t set the profile, and use the standard FNDSSOCORP.gif, the HTML looks like this:

<img src="/OA_MEDIA/FNDSSCORP.gif" title="Oracle" alt="Oracle" border="0">

Therefore the image title attribute value of “Oracle” is what you’ll need to look for in your Greasemonkey Script.

If you have a custom image set on the Corporate Branding Image for Oracle Applications profile, pages on some parts of some responsibilities )e.g. Oracle Application Manager accessed via the System Administrator responsibility) still use the standard FNDSSOCORP.gif logo, and so have the image title attribute set to “Oracle”.

Therefore, if you do use a custom image, your script needs to be able to look for both variations of the image title attribute, covered in the example below.

Detailed Instructions

  • The user needs to have Firefox installed
  • Press ALT key to show menu (if the menu is hidden)
  • Choose “Add Ons” menu
  • Choose “Get Add-ons”
  • Search for “greasemonkey” and click “Install” against the item in the search results against the Greasemonkey result:
  • After it has been installed, restart Firefox
  • Once it has restarted, press ALT key to show menu (if the menu is hidden)
  • Choose Tools > Greasemonkey > New User Script…
  • Fill in the New User Script form:
Name Update Oracle Pages to Include Instance Name
Namespace Namespace
Description Show Instance Name
Includes http://*your.site.com*
  • Replace the your.site.com address in the Includes line with the URL for your site
  • Click OK
  • A text editor will open up. Select all of the text in it, and replace with this javascript code:
// ==UserScript==
// @name        Update Oracle Pages to Include Instance Name
// @namespace   Namespace
// @include     http://*your.site.com*
// @grant       none
// ==/UserScript==

// current page
var url_loc = window.location.href;

// Check URL and set the "env_label" to match the instance you're viewing
// Also set start and finish colours for gradient filled background (c1 and c2)
if (url_loc.indexOf("http://your.site.com")  == 0)     {var env_label = "PROD";      var c1 = "#ff0000"; var c2 = "#ff0000";}
if (url_loc.indexOf("http://your.site.com:8000") == 0) {var env_label = "DBA";       var c1 = "#1589ff"; var c2 = "#3bb9ff";}
if (url_loc.indexOf("http://your.site.com:8001") == 0) {var env_label = "DEV";       var c1 = "#7931df"; var c2 = "#dddddc";}
if (url_loc.indexOf("http://your.site.com:8002") == 0) {var env_label = "UAT";       var c1 = "#87f717"; var c2 = "#3bb9ff";}

// Replace header images with env label and set label colour

// here we look for any images which have a title attribute of "FNDSSOCORP.gif" 
//   OR "Oracle", since the Oracle Application Manager pages also use the "FNDSSOCORP.gif" header image, but 
//   set the title attribute to be "Oracle" rather than "FNDSSOCORP.gif"
// 
// The "||" operator = "OR" so this line:
//   imgs[i].title == 'FNDSSOCORP.gif' || imgs[i].title == 'Oracle'
//
// Is saying "If any images title have a title of 'FNDSSOCORP.gif' or 'Oracle'..., then do something

var imgs=document.getElementsByTagName('img');
for(var i=imgs.length-1;i>=0;i--){
    if(imgs[i].title == 'FNDSSOCORP.gif' || imgs[i].title == 'Oracle' || imgs[i].title == 'Discoverer Viewer' || imgs[i].title == 'Discoverer Plus') {
      var h1 = document.createElement('h1');
      h1.innerHTML = env_label;
      imgs[i].parentNode.insertBefore( h1, imgs[i] );
      imgs[i].parentNode.removeChild(imgs[i]);
      h1.style.color = "white";
      h1.style.padding = "2 10 4 8";
      h1.style.backgroundImage = "linear-gradient(to left, "+c1+", "+c2+")";
      h1.style.margin = "0 5 0 5";
      h1.style.fontSize = "30px";
      h1.style.fontFamily = "Arial";
      h1.style.borderRadius = "5px";
      h1.style.textAlign = "center";
      h1.style.textShadow = "1px 1px 1px white, 3px 3px 5px black";
    }
}

/*##############################################################################
   UPDATE PAGE TITLE TO INCLUDE ENV NAME
/*############################################################################*/

var dt = document.title;                                                // get page title
var url = window.location.search;                                       // get page querystring
var current_path = window.location.pathname.split('/').pop();           // get page name
var tid = url.indexOf("temp_id");                                       // viewing output from concurrent request

if (tid > 0 && current_path == "FNDWRR.exe") {                           // if viewing conc job output
    dt = env_label + ": Conc Job"                                       // page title shows you are viewing the output of a concurrent request
} else {
    var dtl = dt.length;                                                // page title length
    if (dtl > 0) { dt = env_label + ": " + dt; }                     // if page title is populated, prefix it with instance name
    if (dtl == 0) { dt = env_label + " ####"; }                         // if page title is blank...
}

// update the page title
document.title = dt;

/*##############################################################################
#     HEADER TEXT STYLING
/*############################################################################*/
var cells = document.getElementsByClassName("x4i")
for (var i = 0; i < cells.length; i++) {
        cells[i].setAttribute('style','font-size:30px; text-shadow: 1px 1px 1px white, 3px 3px 5px black;');
}

/*##############################################################################
#     FOCUS TEXT ON USERNAME FIELD
/*############################################################################*/

function getFocus(){
    if(document.getElementById("unamebean")){
        document.getElementById("unamebean").focus();
        return;
    }
    setTimeout(function(){
        getFocus();
    },100);
}
getFocus();

To customise the script:

  1. Add or delete any lines to match the number of instances you have at your site.
  2. Replace any dummy URLs mentioned in the text file (e.g. your.site.com) to the URL for your site.
  3. Change the “env_label” variable values to match the internal instance names for your site
  4. Change the “env_color” colours to suit.
  5. If you have a custom image set in the “Corporate Branding Image for Oracle Applications” profile, replace “FNDSSOCORP.gif” with your image name, including the file type at the end.
  6. Leave in the search for the “Oracle” image title attribute, since that replaces the images in some system pages.
  7. Save changes to file and close it down
  8. When you go to any Oracle EBS pages, the instance name will appear in the top-left of the screen, and the page-title will start with the instance name.
  9. The script also increases the size of general text – e.g. “E-Business Suite”, but if not required, it can be removed by deleting the “HEADER TEXT STYLING” section from the script.

End Result

The end result of that is that the R12 homepage will now contain a custom text header wih a gradient background so that you can easily see which environment you’re logged into:

Custom Header

The HTML title attribute has been updated to start with the instance name – so you can easily see which instance you’re logged into – e.g.

Custom Page Title

Another advantage of that is that the instance name appears in the browser history so that you can easily see which instances you have logged into during the day.

Copying Syntax Highlighted Text From Notepad++

If you write SQL, you might often find that you want to copy some SQL from your SQL editor, e.g. TOAD or SQL Developer.

If you use TOAD, I think you can copy SQL from the editor window, and paste it into an email or a Word document, and the syntax highlight is preserved.

If you use SQL Developer, I haven’t found a way to do that – if you copy the SQL, and paste it into something else, you just get plain text.

I found you can do it using Notepad++, via an answer on Stackoverflow.

Instructions

Select the text, and go to Plugins > NppExport > Copy HTML to clipboard, though copying in RTF format also works just as well…

Here’s an example:

Starting with pretty SQL:

Select the text, and choose the relevant options in Notepad++:

Select and copy the SQL

Now paste into e.g MS Word:

Exported SQL preserving syntax highlighting

This works just as well for any other syntax highlighted text you might be writing in Notepad++.

Invoice Headers and Holds

-- ##############################################################################
--      INVOICE - MATCH / HOLD INFORMATION
-- ############################################################################*/
 
SELECT aia.invoice_id
     , aia.invoice_num
     , aia.doc_sequence_value inv_voucher
     , aia.invoice_amount
     , aia.invoice_date
     , ah.hold_lookup_code
     , ah.hold_reason
     , ah.hold_date
     , ah.creation_date hold_created
     , ah.last_update_date hold_updated
     , ah.release_lookup_code
     , ah.release_reason
  FROM ap.ap_invoices_all aia
  JOIN ap.ap_holds_all ah ON aia.invoice_id = ah.invoice_id
 WHERE 1 = 1
   AND ah.hold_lookup_code IN ('CANT FUNDS CHECK')
--   AND aia.doc_sequence_value = 2445122
--   AND aha.hold_date > '01-OCT-2013'
   AND 1 = 1;

Using Oracle SQL instead of ANSI SQL:

-- ##############################################################################
--      INVOICE - MATCH / HOLD INFORMATION
-- ############################################################################

SELECT aia.invoice_id
     , aia.invoice_num
     , aia.invoice_amount
     , aia.invoice_date
     , ah.*
     , ah.hold_id
     , ah.release_lookup_code
     , ah.release_reason
     , ah.status_flag
     , ah.last_update_date
  FROM ap.ap_invoices_all aia
     , ap.ap_holds_all ah
 WHERE aia.invoice_id = ah.invoice_id
   AND aia.doc_sequence_value = 2439927
--   AND aha.hold_date > '01-OCT-2013'
   AND 1 = 1;

Invoice Headers Matched to Purchase Orders

-- ##############################################################################
--      INVOICES MATCHED TO PURCHASE ORDERS
-- ############################################################################*/
 
    SELECT aia.invoice_id
         , aia.invoice_num
         , aia.doc_sequence_value
         , aida.pa_addition_flag
         , aida.invoice_distribution_id
         , aida.creation_date
         , aida.last_updated_by
         , aida.distribution_line_number dist_line
         , aida.line_type_lookup_code dist_line_type
         , aida.period_name dist_line_period
         , aida.po_distribution_id
         , aida.match_status_flag
         , aida.posted_flag
         , aida.quantity_invoiced
         , aida.amount dist_amt
         , pha.segment1 po
         , pha.po_header_id
         , pha.last_update_date po
         , pda.last_update_date pda
         , pv.vendor_name supplier
         , ppa.segment1 project
      FROM ap.ap_invoices_all aia
      JOIN ap.ap_invoice_distributions_all aida ON aia.invoice_id =          aida.invoice_id
      JOIN ap.ap_terms_tl att                   ON aia.terms_id =            att.term_id
      JOIN applsys.fnd_user fu                  ON aia.created_by =          fu.user_id
      JOIN ap.ap_suppliers pv                   ON aia.vendor_id =           pv.vendor_id
      JOIN ap.ap_supplier_sites_all pvsa        ON aia.vendor_site_id =      pvsa.vendor_site_id AND pv.vendor_id = pvsa.vendor_id
      JOIN po.po_distributions_all pda          ON aida.po_distribution_id = pda.po_distribution_id
      JOIN po.po_lines_all pla                  ON pda.po_line_id =          pla.po_line_id
      JOIN po.po_headers_all pha                ON pla.po_header_id =        pha.po_header_id
 LEFT JOIN pa.pa_projects_all ppa               ON pda.project_id =          ppa.project_id
     WHERE 1 = 1
    --   AND pha.segment1 IN (1508482)
       AND aia.invoice_id IN (1966606)                                      -- ##   ID   ## --
       AND ppa.segment1 = 'ABC12345'
    --   AND aia.invoice_num = '373018'                                      -- ## NUMBER ## --
    --   and AIA.DOC_SEQUENCE_VALUE IN (2735587)                             -- ## VOUCHER ## --
    --   AND aida.invoice_distribution_id IN (37543846,37543847,37543849,37543851)
       AND 1 = 1;

Using Oracle SQL instead of ANSI SQL:

-- ##############################################################################
--      Invoices including POs
-- ############################################################################*/

SELECT aia.invoice_id
     , aia.invoice_num
     , aida.pa_addition_flag
     , aida.invoice_distribution_id
     , aida.creation_date
     , aida.last_updated_by
     , aida.distribution_line_number dist_line
     , aida.line_type_lookup_code dist_line_type
     , aida.period_name dist_line_period
     , aida.po_distribution_id
     , aida.match_status_flag
     , aida.posted_flag
     , aida.quantity_invoiced
     , aida.amount dist_amt
     , pha.segment1 po
     , pha.po_header_id
     , pha.last_update_date po
     , pda.last_update_date pda
--     , pv.vendor_name supplier
  FROM ap.ap_invoices_all aia
     , ap.ap_invoice_distributions_all aida
--     , ap.ap_terms_tl att
--     , applsys.fnd_user fu
--     , ap.ap_suppliers pv
--     , ap.ap_supplier_sites_all pvsa
     , po.po_headers_all pha
     , po.po_lines_all pla
     , po.po_distributions_all pda
 WHERE aia.invoice_id = aida.invoice_id
--   AND aia.terms_id = att.term_id
--   AND aia.created_by = fu.user_id
--   AND aia.vendor_id = pv.vendor_id
--   AND aia.vendor_site_id = pvsa.vendor_site_id
--   AND pv.vendor_id = pvsa.vendor_id
   AND aida.po_distribution_id = pda.po_distribution_id
   AND pda.po_line_id = pla.po_line_id
   AND pla.po_header_id = pha.po_header_id
--   AND pha.segment1 IN (1372292,1361420)
--   AND aia.invoice_id IN (373018)                                          -- ##   ID   ## --
--   AND aia.invoice_num = '373018'                                       -- ## NUMBER ## --
   and AIA.DOC_SEQUENCE_VALUE IN (2439927)                                    -- ## VOUCHER ## --
   AND 1 = 1;

-- add a distinct and keep to bare minimum to only return Invoice, PO and Supplier info

SELECT DISTINCT
       aia.invoice_id
     , aia.invoice_num
     , pha.segment1 po
     , pha.po_header_id
     , pv.vendor_name supplier
  FROM ap.ap_invoices_all aia
     , ap.ap_invoice_distributions_all aida
     , ap.ap_terms_tl att
     , applsys.fnd_user fu
     , apps.po_vendors pv
     , apps.po_vendor_sites_all pvsa
     , po.po_headers_all pha
     , po.po_lines_all pla
     , po.po_distributions_all pda
 WHERE aia.invoice_id = aida.invoice_id
   AND aia.terms_id = att.term_id
   AND aia.created_by = fu.user_id
   AND aia.vendor_id = pv.vendor_id
   AND aia.vendor_site_id = pvsa.vendor_site_id
   AND pv.vendor_id = pvsa.vendor_id
   AND aida.po_distribution_id = pda.po_distribution_id
   AND pda.po_line_id = pla.po_line_id
   AND pla.po_header_id = pha.po_header_id
   AND pha.segment1 IN (1372292,1361420)
--   AND aia.invoice_id IN (373018)                                          -- ##   ID   ## --
--   AND aia.invoice_num = '373018'                                       -- ## NUMBER ## --
--   and AIA.DOC_SEQUENCE_VALUE IN (2221144)                                    -- ## VOUCHER ## --
   AND 1 = 1;

Invoice Headers and Payment Numbers

-- ##############################################################################
--      INVOICE DETAILS AND PAYMENT NUMBERS (AP_INVOICE_PAYMENT_HISTORY_V)
-- ############################################################################*/
 
SELECT aia.invoice_id
     , aia.invoice_num
     , aia.doc_sequence_value voucher
     , pv.vendor_name supplier
     , pv.segment1 supplier_num
     , DECODE(aipa.invoice_payment_type, 'PREPAY', aia.invoice_num, aca.check_number) document_number
     , aipa.amount
     , aipa.accounting_date
     , aipa.period_name
     , aipa.posted_flag
     , aipa.check_id
     , aipa.creation_date
  FROM ap.ap_invoice_payments_all aipa
  JOIN ap.ap_invoices_all aia ON aipa.invoice_id = aia.invoice_id
  JOIN ap.ap_checks_all aca   ON aipa.check_id =   aca.check_id
  JOIN ap.ap_suppliers pv     ON aia.vendor_id =   pv.vendor_id
 WHERE 1 = 1
--   AND aia.doc_sequence_value IN (257578)         -- ## VOUCHER ## --
   AND aia.invoice_id = 2809692
--   AND aipa.last_update_date > '09-APR-2015'
--   AND DECODE(aipa.invoice_payment_type, 'PREPAY', aia.invoice_num, aca.check_number) IN ('705907')
   AND 1 = 1;

Using Oracle SQL instead of ANSI SQL:

-- ##############################################################################
--      INVOICE DETAILS AND PAYMENT NUMBERS (AP_INVOICE_PAYMENT_HISTORY_V)
-- ############################################################################*/

select aia.invoice_id
     , aia.invoice_num
     , aia.doc_sequence_value voucher
     , pv.vendor_name supplier
     , pv.segment1 supplier_num
     , DECODE(aipa.invoice_payment_type, 'PREPAY', aia.invoice_num, aca.check_number) document_number
     , aipa.amount
     , aipa.accounting_date
     , aipa.period_name
     , aipa.posted_flag
  from ap.ap_invoice_payments_all aipa
     , ap.ap_invoices_all aia
     , ap.ap_checks_all aca
     , ap.ap_suppliers pv
 WHERE aipa.invoice_id = aia.invoice_id
   AND aipa.check_id = aca.check_id
   AND aia.vendor_id = pv.vendor_id
--   AND aia.doc_sequence_value IN (1966859,1934769)         -- ## VOUCHER ## --
--   AND aia.invoice_id = 2127401
--   AND aipa.last_update_date > '09-APR-2015'
   AND DECODE(aipa.invoice_payment_type, 'PREPAY', aia.invoice_num, aca.check_number) IN ('10005561')
   AND 1 = 1;