ASP.Net | Freeze GridView header row using java script & CSS

Hi guys

As I started developing a web application for our business, One of the toughest requirements from end users were to “freeze” the header row of data grid views. I left .Net development almost 11 years back and was struggling to catch up with the whole set of changes .Net has came up with.

I’ve scavenged through dozen’s of articles explaining different hacks to freeze/lock the header row of grid views & was surprised to see that Microsoft didn’t address this requirement throughout their .Net iterations. Few of the workarounds those I tried out were ONLY applicable to static set of data, not at all applicable to data grids those have more than three or four columns and failed while column contents exceeded certain number of characters.

Finally, after a number of days’ search & trying out various solutions I came across a post at https://www.aspsnippets.com/Articles/Dynamically-freeze-ASP.Net-Gridview-header-using-JavaScript.aspx

The post has a VS solution download (Dated 21st February 2011) which was giving me multiple reference errors while opened with VS 2019 Community edition. So I decided to open the .aspx and code behind pages manually and create a new solution using VS 2019

Although the java script does freeze the header rows (while no themes applied to the grid & has limited columns), the moment a theme applied to the grid, the header row alignment with the data columns go for a toast. You will able to see how it works with attached example that you can download from the provided link by the bottom of the post.

I’ve created a huge employee list with 20 columns, where few of the columns have varying data lengths (like employee name, department, section name & job positions) as an XML source and throughout the examples I will be using the same data source to demonstrate how each page adjusts the GridView with minor hacks using both java script and CSS styling.

Example one, no java script, no themes gridview

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="FreezeGridViewHeader.Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
                <Columns>
<asp:BoundField DataField="EmployeeNumber" HeaderText="Code" />
<asp:BoundField DataField="fullname" HeaderText="Name"  />
<asp:BoundField DataField="department" HeaderText="Department"  />
<asp:BoundField DataField="section" HeaderText="Section"  />
<asp:BoundField DataField="position" HeaderText="Position"  />
<asp:BoundField DataField="basicsalary" HeaderText="Salary"  />
<asp:BoundField DataField="other" HeaderText="other"  />
<asp:BoundField DataField="petrol" HeaderText="petrol"  />
<asp:BoundField DataField="mobile" HeaderText="mobile"  />
<asp:BoundField DataField="car" HeaderText="car"  />
<asp:BoundField DataField="transport" HeaderText="transport"  />
<asp:BoundField DataField="sign" HeaderText="sign"  />
<asp:BoundField DataField="house" HeaderText="house"  />
<asp:BoundField DataField="acco" HeaderText="acco"  />
<asp:BoundField DataField="driving" HeaderText="driving"  />
<asp:BoundField DataField="monthly" HeaderText="monthly"  />
<asp:BoundField DataField="oncall" HeaderText="oncall"  />
<asp:BoundField DataField="engineer" HeaderText="engineer"  />
<asp:BoundField DataField="special" HeaderText="special"  />
<asp:BoundField DataField="total" HeaderText="total"  />
</Columns>
            </asp:GridView>

        </div>
    </form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace FreezeGridViewHeader
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            if (!IsPostBack)
            {
                DataSet ds = new DataSet();
                ds.ReadXml(Server.MapPath("~/App_Data/employees.xml"));
                GridView1.DataSource = ds;
                GridView1.DataBind();
            }

          
        }
    }
}

Executing Default.aspx brings up a page like below

Now, we will use the java script to see how far the intended results are achieved.

Example Two, Calling java script function to freeze the header row

Gridjs.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Gridjs.aspx.cs" Inherits="FreezeGridViewHeader.Gridjs" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="FreeGrid.js" type="text/javascript"></script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical">
                <AlternatingRowStyle BackColor="#DCDCDC" />
                <Columns>
                    <asp:BoundField DataField="EmployeeNumber" HeaderText="Code" />
                    <asp:BoundField DataField="fullname" HeaderText="Name" />
                    <asp:BoundField DataField="department" HeaderText="Department" />
                    <asp:BoundField DataField="section" HeaderText="Section" />
                    <asp:BoundField DataField="position" HeaderText="Position" />
                    <asp:BoundField DataField="basicsalary" HeaderText="Salary" />
                    <asp:BoundField DataField="other" HeaderText="other" />
                    <asp:BoundField DataField="petrol" HeaderText="petrol" />
                    <asp:BoundField DataField="mobile" HeaderText="mobile" />
                    <asp:BoundField DataField="car" HeaderText="car" />
                    <asp:BoundField DataField="transport" HeaderText="transport" />
                    <asp:BoundField DataField="sign" HeaderText="sign" />
                    <asp:BoundField DataField="house" HeaderText="house" />
                    <asp:BoundField DataField="acco" HeaderText="acco" />
                    <asp:BoundField DataField="driving" HeaderText="driving" />
                    <asp:BoundField DataField="monthly" HeaderText="monthly" />
                    <asp:BoundField DataField="oncall" HeaderText="oncall" />
                    <asp:BoundField DataField="engineer" HeaderText="engineer" />
                    <asp:BoundField DataField="special" HeaderText="special" />
                    <asp:BoundField DataField="total" HeaderText="total" />
                </Columns>
                <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
                <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
                <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
                <SortedAscendingCellStyle BackColor="#F1F1F1" />
                <SortedAscendingHeaderStyle BackColor="#0000A9" />
                <SortedDescendingCellStyle BackColor="#CAC9C9" />
                <SortedDescendingHeaderStyle BackColor="#000065" />
            </asp:GridView>
            <script type="text/JavaScript">
                window.onload = function () {
                    this.FreezeGrid("<%=GridView1.ClientID %>",500);

                }
            </script>
        </div>
    </form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace FreezeGridViewHeader
{
    public partial class Gridjs : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DataSet ds = new DataSet();
                ds.ReadXml(Server.MapPath("~/App_Data/employees.xml"));
                GridView1.DataSource = ds;
                GridView1.DataBind();
            }
        }
    }
}

I’ve applied one of the built-in themes available for GridView control with the above example, which causes the header and data rows misalignment.

However, the java script has done what it was meant to do! Freezing the header row and providing a scrolling function without breaking much sweat. I have noticed one thing, if the theme is removed from the GridView control, everything goes back to as expected, header row is perfectly aligned with the data row & the scrolling works as expected. Please note, if your grid is populated with huge volume of data, then it will take few moments before the java script manages to bring up the effects in place. I’ve tested this solution using Microsoft Edge Chromium, Mozilla Firefox & Internet Explorer. Both the modern browsers deal with the java script much efficiently while Internet Explorer experiences were NOT that good.

Example Three, fixing the misalignment issues by using some simple CSS styling.

Gridjscss.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Gridjscss.aspx.cs" Inherits="FreezeGridViewHeader.Gridjscss" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="FreeGrid.js" type="text/javascript"></script>
    <style>
        .Colsmall {
            width: 60px;
            max-width: 60px;
            min-width: 60px;
        }

        .Colmedium {
            width: 80px;
            max-width: 80px;
            min-width: 80px;
        }

        .Colbig {
            width: 150px;
            max-width: 150px;
            min-width: 150px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical">
                <AlternatingRowStyle BackColor="#DCDCDC" />
                <Columns>
                    <asp:BoundField DataField="EmployeeNumber" HeaderText="Code" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" />
                    <asp:BoundField DataField="fullname" HeaderText="Name" ItemStyle-CssClass="Colbig" HeaderStyle-CssClass="Colbig" />
                    <asp:BoundField DataField="department" HeaderText="Department" ItemStyle-CssClass="Colbig" HeaderStyle-CssClass="Colbig" />
                    <asp:BoundField DataField="section" HeaderText="Section" ItemStyle-CssClass="Colbig" HeaderStyle-CssClass="Colbig" />
                    <asp:BoundField DataField="position" HeaderText="Position" ItemStyle-CssClass="Colbig" HeaderStyle-CssClass="Colbig" />
                    <asp:BoundField DataField="basicsalary" HeaderText="Salary" ItemStyle-CssClass="Colmedium" HeaderStyle-CssClass="Colmedium" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="other" HeaderText="other" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="petrol" HeaderText="petrol" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="mobile" HeaderText="mobile" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="car" HeaderText="car" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="transport" HeaderText="transport" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="sign" HeaderText="sign" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="house" HeaderText="house" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="acco" HeaderText="acco" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="driving" HeaderText="driving" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="monthly" HeaderText="monthly" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="oncall" HeaderText="oncall" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="engineer" HeaderText="engineer" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="special" HeaderText="special" ItemStyle-CssClass="Colsmall" HeaderStyle-CssClass="Colsmall" ItemStyle-HorizontalAlign="Right" />
                    <asp:BoundField DataField="total" HeaderText="total" ItemStyle-CssClass="Colmedium" HeaderStyle-CssClass="Colmedium" ItemStyle-HorizontalAlign="Right" />
                </Columns>
                <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
                <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
                <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
                <SortedAscendingCellStyle BackColor="#F1F1F1" />
                <SortedAscendingHeaderStyle BackColor="#0000A9" />
                <SortedDescendingCellStyle BackColor="#CAC9C9" />
                <SortedDescendingHeaderStyle BackColor="#000065" />
            </asp:GridView>
            <script type="text/JavaScript">
                window.onload = function () {
                    this.FreezeGrid("<%=GridView1.ClientID %>", 500);

                }
            </script>
        </div>
    </form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace FreezeGridViewHeader
{
    public partial class Gridjscss : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DataSet ds = new DataSet();
                ds.ReadXml(Server.MapPath("~/App_Data/employees.xml"));
                GridView1.DataSource = ds;
                GridView1.DataBind();
            }
        }
    }
}

Now, everything looks fine! A theme is applied, header and data rows are in perfect sync & the header row is locked when the data rows are scrolled up and down.

There are many alternatives, modern approaches using jQuery and other, which I do not understand at this point of learning. Once I am confident enough to, will sure try them and get back to you guys. Happy coding!

Download VS Solution

Please note, this solution has been developed using .Net 4.7.2 & I have modified the java script in a way that I can call the script from any page by passing couple of parameters to it. The whole credit for the java script goes to original coder.

regards,

rajesh

Advertisements

Transact SQL | Virtual Calendar

Hello guys

We’re integrating MS SQL servers everywhere for our business along with long trusted technology partner Oracle & will try to include what we have “learned” in our labs within this blogosphere.

Our bio metric attendance system reports are designed with virtual dates (weekends when the employees are not punching, yet the reporting requires the whole month dates appearing in the final output, marking the date and day name for the date) from a virtual calendar created using connect by level pseudocolumn

CREATE OR REPLACE FORCE VIEW XXFPAUTOCALNDR_V
(
   EMPLOYEE_NUMBER,
   DAY_DATE,
   PUNCH_PERIOD
)
AS
       SELECT NULL EMPLOYEE_NUMBER,
              TRUNC (TO_DATE ('01-JAN-2009', 'DD-MON-YYYY') + LEVEL) day_date,
              TO_CHAR (TRUNC (TO_DATE ('01-JAN-2009', 'DD-MON-YYYY') + LEVEL),
                       'MMYYYY')
                 punch_period
         FROM DUAL
   CONNECT BY TO_DATE ('01-JAN-2009', 'DD-MON-YYYY') + LEVEL <=
                 LAST_DAY (TRUNC (SYSDATE));

Well, MS SQL doesn’t provide connect by level approach, instead recursive CTE (common table expression) & let us see how we can create a virtual calendar using CTE on MS SQL

CREATE VIEW UnisVCalendar
as
WITH CTE(virtual_date) AS (
  SELECT cast('1/1/2016' as datetime) as virtual_date
  UNION ALL
  SELECT virtual_date+1
  FROM CTE 
  WHERE virtual_date <= GETDATE() 
)
SELECT * FROM CTE
GO

CTE allows referring the table itself, thus indirectly providing a level sort of reference.

Well, the downside of such a virtual calendar is, one should make sure that to use option(maxrecursion 0) to avoid “The maximum recursion 100 has been exhausted before statement completion” error

Select CONVERT(date,virtual_date,105) from UnisVCalendar
WHERE CONVERT(date,virtual_date,105) BETWEEN '01-JAN-2020' AND '31-JAN-2020'
option (maxrecursion 0)
GO

Now you can safely refer this virtual calendar at applicable situations.

Featured

We are going COM

Hello guys

We just completed 10 years of blogging with WORDPRESS & it was a great experience! Although we’ve started the blog ONLY to vent out our frustrations with Windows 7 OS, over the years we’ve discussed many other technology areas.

Well…err hmm, about many things those WERE not significant at all, however many of you have seen them ;) . Now you may ask why switching from a life long free blog to a COM (commercial site). Answer is simple, WE are going to have few advertisements running over the blog, which might fetch us some income!

We’ve been enthusiastically doing many charities and currently planning to setup an educational fund for under privileged expat students in Kuwait. We will utilize every penny that comes through the ads for this purpose.

So if you find an interesting ad on our pages, please check them out. We’ve never commercialized any of our efforts & will remain to provide you the hacks/solutions free.

regards,

Windows 10 | Mobile HotSpot| We (don’t) have a solution

Hello guys

Cannot get the mobile HotSpot fully functional on Windows 10? Were you searching for a single stop solution, then you are at a very wrong place(?) move on. We are going to discuss about a strange behavior associated with, else almost a perfect solution provided by Microsoft for tethering the available internet connectivity.

At our corporate LAN, we use two different sets of IP addresses. One provided by the ISP (front facing) and the Class C IP addresses for the domain member computers and other devices.

We had a sysadmin laptop that is configured to use internet without filtering (AKA Watch Guard Edge device) by letting the traffic through the ISP router & a Class C IP address for LAN connectivity. Most of the sysadmins use the same settings for ease and had some difficulties with this particular laptop.

Mobile HotSpot starts, however the connected devices do not get IP addresses issued and no internet connectivity. Resetting IP & Winsock and windows provided troubleshooters didn’t fix anything & on a “divine” call we decided to change the IP addresses used.

For example, We had

168.x.x.204/255.255.255.224 (ISP) and 131.x.x.46/24 (LAN, don’t frown, we had these public IP ranges used within the LAN, dating back to 1990s) for the single network interface which didn’t work for Mobile HotSpot, so we changed the ISP IP address with an IP from another set of readily available public IPs and viola! the Mobile HotSpot sprang to life immediately.

We did route table matches between both IP settings and couldn’t find any differences, however, the first setup completely failing to issue IP addresses & connectivity. As we failed do the maths, gave it a rest and started “enjoying” the internet. Yay! So if you are in the same situation and have spare IP addresses, give it a try.

regards,

rajesh

Oracle SQL Developer 19.x.x & ORA-20001: Oracle error -6502: ORA-06502: PL/SQL: numeric or value error: character string buffer too small has been detected in fnd_global.set_nls

Hi guys

Okay, so you switched to Oracle SQL Developer for the main reason that the suite is from Oracle & it is absolutely free and putting loads of efforts to get accustomed with certain “JAVA” platform limitations (as per developers).

Everything is fine until you start getting “ORA-20001: Oracle error -6502: ORA-06502: PL/SQL: numeric or value error: character string buffer too small has been detected in fnd_global.set_nls” or errors those WERE never happening while you were trying to execute years old PL/SQL blocks which were always completing successfully using Quest TOAD or at SQL prompt itself.

Before scavenging through your archives to find the Toad Installer for a re-installation, give the following exercise a try.

Applicable to Windows ONLY!

Go to user specific “AppData\Roaming” folder, eg: C:\Users\rajesh\AppData\Roaming

and delete both the folders, “SQL Developer” & “sqldeveloper”

Usually whenever you download and start the latest version of SQL Developer, the new version checks for the older versions under Roaming profile & if found, prompts the user asking whether the existing preferences should be copied (that includes already saved connections and other setting you may have made), which may cause errors like the ONE I have had once after migrating 19.2.x to 19.4.x

Regardless whether you were using previous versions or NOT, deleting all folders for “SQL Developer” under the roaming profile will force the latest version of Oracle SQL Developer to start afresh & most probably will take care of unreliable error messages (Confirmed by running the PL/SQL blocks without outputting the same errors using Quest TOAD)

Thank me later ;)

rajesh

Mitel | E1 Trunk noise issues after MiVoice Business Upgrade

Recently we faced an issue with our Mitel IP telephony system after upgrading from version 7 to 8, then to 9.

We’ve Nortel (Currently Avaya) legacy digital telephony system across our businesses and the Mitel IP telephony was implemented with an intention to roll out IP based telephones. Somehow, the solution was partially implemented, forcing us to integrate two different technologies, so that we can make and receive calls from and to both systems.

The digital trunk was created using E1 interface card and everything was working until the software upgrade. We started experiencing a kind of annoying noise, almost impossible to hear the other party & our support partner from Mitel spent 3 weeks trying to figure out what went wrong after the software upgrade.

After 3 weeks of involving different “engineers”, they gave us 3 different possibilities for the noise:

  • Controller’s motherboard have issues
  • Controller’s hard disk is having issues
  • LAN is having bad POE switches

We’ve downgraded from version 9 to 8, “successfully” inheriting the noise issues & and we had a new problem in addition to the noise issues. Handsets started reloading the application once in 2-3 minutes. This is the time we (internal IT team) decided to intervene.

We’ve identified the application reload was happening due to a power failure, which resulted in the IP address used by the Layer 2 (L2) Switch being issued to one of the PCs in the network as it was not excluded with DHCP scope. After spending couple of hours being assigned to address this situation, support partner concluded that “it looks like a hard disk failure”

We took over & started checking for the IPs assigned to the controller and found that it was merely an IP conflict and fixed the reloading issues by releasing the IP (added an exclusion scope with the DHCP server to avoid future conflicts)

However, we had the bigger issue still not resolved, about the noise. As we realized that the support partner is JUST making vague and unrealistic conclusions, requested for the details about codecs and the settings for trunks, which they happily shared with us.

By default, the codec settings for our controller running version 8 has values as seen with the below image.

We toggled all the switches (allowed ones) to No first (Saved), then Yes (Saved) followed by the default settings (refer the image)

Went ahead to “Trunks -> Digital -> Digital Link Descriptors” & re-assigned Voice Encoding to “ADI” (Which the “engineers” set as Nil during their extended support efforts)

Saved the changes and 3 weeks long noise issues were gone in a second.

Although we can’t confirm (technically), it looks like the backup restore after the software upgrade somehow interferes with the codec filtering that needed a manual intervention like the one we did by toggling the switches to NO and Yes & defaults under Codecs section.

If you were frantically searching for solution such a situation like ours, before falling victim to your support partner’s offer to replace the current controller for a discounted price like 4000$, please spend a moment to give what we have tried and succeeded a try.

regards,

Oracle Application R12 | Print custom report using CUSTOM.pll

Hello guys,

Happy New Year!

Today we will see how we can use CUSTOM.pll for enabling special menus and printing a custom report by invoking the special menu that we activate using the library.

We are going to use Oracle’s seeded form “POXPOEPO” AKA Purchase Orders.

Please make sure that you make a backup for the CUSTOM.pll prior making below said modifications. CUSTOM.pll is “always” found uner $AU_TOP/resources folder

We’ll enable the SPECIAL15 menu item for the exercise.

Load up CUSTOM.pll using Oracle Forms Designer & make sure you are connected to database before loading the library file.

Attach APPCORE, APPCORE2 libraries with your copy of CUSTOM.pll

Attach FNDCONC.pll library for calling printing related activities

Your CUSTOM.pll should look like the above after attaching said libraries.

Add the block as seen with the image by the bottom of your CUSTOM.pll package body.

BEGIN
  IF (event_name = 'WHEN-NEW-FORM-INSTANCE') THEN
     IF (l_form_name = 'POXPOEPO' AND l_block_name = 'PO_HEADERS') THEN	 
    -- 	fnd_message.debug(l_form_name);
     	app_special2.instantiate('SPECIAL15', '&Print', 'prord',TRUE,'LINE');
     END IF;
  END IF;
  
END;

Now you can proceed with writing code for what happens when “SPECIAL15” event happens

BEGIN
  IF (event_name = 'SPECIAL15') THEN
     IF (l_form_name = 'POXPOEPO' AND l_block_name = 'PO_HEADERS') THEN	 
     	print_po(name_in('PO_HEADERS.ORG_ID'),name_in('PO_HEADERS.SEGMENT1'));
     	--fnd_message.debug('Will Print This Order');
    --app_special2.instantiate('SPECIAL15', '&Print', 'prord',TRUE,'LINE');
     END IF;
  END IF;
  
END;

Here, I am calling a procedure that I defined with CUSTOM.pll for handling print requests.

and the package body is as following (not another image, I am going to save some efforts for you)

procedure print_po(p_org_id IN NUMBER, p_order_number IN VARCHAR2) is
		l_ord_num               NUMBER := 0;
		l_ord_type_name         VARCHAR2 (240);
		l_req_id_Rep            NUMBER;
		l_request_completed     BOOLEAN := FALSE;
		l_req_phase             VARCHAR2 (20);
		l_req_status            VARCHAR2 (1000);
		l_req_dev_phase         VARCHAR2 (1000);
		l_req_dev_status        VARCHAR2 (1000);
		l_req_message           VARCHAR2 (1000);
		l_conc_mgr_status       NUMBER;
		p_call_stat             NUMBER;
		p_activep_stat          NUMBER;
		
		l_order_category_code   NUMBER;
		l_report_name       VARCHAR2 (40);
		l_ret_report_name       VARCHAR2 (40);
		l_req_id 								NUMBER;
		l_order_type_name				VARCHAR2(30);
		
		--
		
		l_signing_person VARCHAR2(240);
		l_person_designation VARCHAR2(240);
		
		
BEGIN
--fnd_message.debug('Will Print This Order');

/*This is a custom procedure that checks whether the concurrent manager is online or not, you can safely comment this line
   --Check the status of Concurrent Manager
   apps.xx_conc_mgr_status_p (p_call_stat, p_activep_stat);


   IF p_call_stat <> 0
   THEN
      fnd_message.set_string ('Concurrent Manager Status Unknown');
      fnd_message.show;
   ELSE
      IF p_activep_stat > 0
      THEN
         NULL;                       --Message('ICM is running' || l_activep);
      ELSE
         fnd_message.set_string (
            'Concurrent Manager is down, Please try printing the invoice later');
         fnd_message.show;
         RAISE form_trigger_failure;
      END IF;
   END IF;

   --Checking concurrent manager status end----
 --  MESSAGE ('Concurrent manager status: up & running');
*/

 BEGIN
/* I am picking up the reports names (concurrent_program_name from FND_CONCURRENT_PROGRAMS_VL view as we have different layouts for companies
you can set up a value for l_report_name while variable is defined
---
--
   SELECT execution_file_name,STRING1, STRING2
     INTO l_report_name, l_signing_person, l_person_designation
     FROM omspoprintreg
    WHERE 1 = 1 AND organization_id = p_org_id
          AND TRUNC (SYSDATE) BETWEEN start_date_active
                                  AND NVL (end_date_active, SYSDATE);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      fnd_message.set_string (
         'No reports defined for this type of transaction, Please contact OM Super User');
      fnd_message.show;
      RAISE form_trigger_failure;
END;

--   FND_MESSAGE.DEBUG('Printing Order '||p_org_id||' order number '||p_order_number);
*/
--
--   
   l_req_id :=
      fnd_request.submit_request ('PO',
                                  l_report_name,
                                  NULL,
                                  SYSDATE,
                                  FALSE,
                                  P_ORG_ID,
                                  P_ORDER_NUMBER, 
                                  l_signing_person,
                                  l_person_designation,
                                  chr(0));
--You cannot setup :SYSTEM.MESSAGE_LEVEL within CUSTOM.pll, hence use COPY 
--to suppress messages like 'Two records saved'

   COPY('25','SYSTEM.MESSAGE_LEVEL');
   COMMIT;
  
  -- FND_MESSAGE.DEBUG('Your request id is '||l_req_id);


   l_request_completed :=
      fnd_concurrent.wait_for_request (request_id   => l_req_id,
                                       INTERVAL     => 1,
                                       phase        => l_req_phase,
                                       status       => l_req_status,
                                       dev_phase    => l_req_dev_phase,
                                       dev_status   => l_req_dev_status,
                                       MESSAGE      => l_req_message);
  --:SYSTEM.Message_Level := '25';
  COMMIT;
  editor_pkg.report (l_req_id, 'Y');

end print_po;

That’s it!

Now copy the CUSTOM.pll to $AU_TOP/resources & compile it

frmcmp_batch module=CUSTOM.pll userid=apps/apps output_file=CUSTOM.plx module_type=LIBRARY batch=yes compile_all=special

If you don’t have syntax errors or other, you must have the fresh CUSTOM.pll

Please make sure that no users are currently online while you are compiling the CUSTOM.pll (This is only applicable to cases where the CUSTOM.pll is already deployed for different forms)

Log on to the instance, access Purchase Orders form & you should see a new menu item under “Tools” menu enabled

While CUSTOM.pll implements “editor_pkg.report” by attaching FNDCONC.pll, FORMS personalization will fail to provide the same functionality as most of the seeded forms do not have FNDCONC library attached to them by default. If you don’t want to use editor_pkg.report to open the completed report, you may create a database level procedure to submit the request(s) and call the same against SPECIAL(n) menu item through FORMS personalization.

Enjoy!

rajesh

Windows 10 | Surround Sound & VLC Player

Hello guys

One of the most complicated “problems” I have had with Windows 10 was, using built-in “Movies & TV” instead of my favorite VLC Player, just because the later “fails” to play 5.1 surround sound, which I love

It looks like Microsoft Windows “Movies & TV” by default enables HDMI audio passthrough, thus surround sound is channeled rightfully.

Let us see a typical scenario:

  • Your PC is connected to a SmartTV that has Android TV (on HDMI Port 2)
  • Your TV is connected to AVR on ARC using HDMI (on HDMI Port 3)
  • Your PC shows your TV as your default “Speaker”
  • You setup 5.1 surround sound (unfortunately, you cannot test the setup us Windows doesn’t do the HDMI audio passthrough, instead the applications those are equipped with HDMI audio passthrough)

You try to play your video clip that has AC-3 6 channel audio, using VLC. What you hear is stereo only & toggling through the AVR’s different Audio formats do not do anything.

Now, here is the catch. By default VLC doesn’t enable HDMI passthrough. If you are using VLC 3.x.x versions, you can easily turn on the HDMI audio passthrough from Tools ->Preferences -> Audio

Select either “Enabled (AC3/DTS only) or Enabled. Switch to the one works for you.

Try to play the video again. One of the interesting factor I recognized that, once the HDMI/SPDIF audio passthrough enabled, VLC doesn’t care whether your speaker setup is surround (5.1, 7.1, x.x) or not. It just does the audio channel routing.

Try and let me know whether it works for you.

regards,

rajesh

Install Oracle forms & reports 12.2.1.4 with Weblogic 12c 12.2.1.4

Hi guys

This time we are going to see how we could install and configure Oracle forms & reports 12.2.1.4, the latest version using WebLogic Server 12c 12.2.1.4

Side Note: Kindly refer this document from Oracle https://docs.oracle.com/en/middleware/fusion-middleware/12.2.1.3/sysrs/system-requirements-and-specifications.pdf

The Visual C++ requirements mentioned for 12.2.1.3 should suffice 12.2.1.4 also, without which you will NOT able to startup OHS instance.

As a rule of thumb, regardless whether you are going to install and configure Oracle software on LINUX or Windows operating systems, always update the OS to latest patch levels, prior you start with the Oracle software installation(s). I strongly recommend using Oracle VirtualBox based Virtual Machines for the lab & later you may move the tried out solution to your preferred environments (TEST, Development). I don’t recommend any of the suggestions I make through my blog posts to be applied to PRODUCTION environments without consulting a Professional support entity.

Download Software from Oracle

Fusion Middleware Infrastructure Installer (Weblogic)

https://www.oracle.com/middleware/technologies/weblogic-server-installers-downloads.html

Oracle Forms & Reports 12.2.1.4

https://www.oracle.com/middleware/technologies/forms/downloads.html

JAVA SE Development Kit 1.8

https://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

Install JDK

I prefer installing JDK on C: drive & Usually my JDK is installed on C:\Java\JDK folder. The shortest name, the best to handle it later. Once the 64Bit JDK is installed, you need to setup either JAVA_HOME environment variable or add a new entry in to the system path. I prefer a system path modification than user path modification.

Okay, there are hell loads of Oracle software installed on my system. Don’t worry, I manage them. Just make sure the JDK installation path is on top of the list and you are good to go

Install Weblogic Server 12.2.1.4

It may take a while for the jar file to extract and start the installer. Please wait patiently. I was setting up the entire infrastructure using Windows 10 VM & due to the limited resources, hence the entire ordeal was kind of time taking this time for me.

You can safely skip the Auto Update

Naming Oracle installation folders is an art. The complex the folder structures, the complex later configurations would be. I usually prefer to install all my Oracle software on D: drive. Please feel comfortable to format the Oracle home as per your requirements. Maintaining the same format helps me to locate and sometimes replace files from previous installations.

Select “Fusion Middleware Infrastructure”

WebLogic or Middleware installation is NOT complex anymore on Windows 10 OS. There are no special hacks needs to complete the installation.

Install forms & reports 12.2.1.4

Extract the downloaded zip file & you MUST start the installer as “Administrator”.

We’ve to install the Forms and Reports stack on an existing Oracle Home. So please select the Oracle Home where you have installed “WebLogic” Server. Alternatively you can always install the stack on a different Oracle Home, which I never tried earlier.

Select “Forms and Reports Deployment”, as the last two versions of Forms & Reports allow you to install a Standalone Forms Builder.

Select the correct JDK Home

Please make sure that you have Visual C++ libraries pre-installed prior proceeding. If you don’t have the VC++ libraries installed, the OHS instance will not start & you might experience additional problems during installation also.

That completes the Forms & Reports 12.2.1.4 installation.

Create a REPO for the Classic Domain

To create a classic domain, you must have a repository available with the database. Weblogic 12c supports Oracle 11G 11.2.0.4 or later database for the repositories & my pick will be always 12c Database as the 11.2.0.4 is only accessible for customers with valid Oracle support. You may refer this document for a detailed “how to” towards installing Oracle 12c database.

From an elevated command prompt, switch to Oracle Home\oracle_common\bin folder & execute “rcu.bat”

Check the below image for details

Choose the defaults

Provide the connection parameters. If the database is on another server, you don’t have to install additional clients, connection parameters will take care of the connection. Refer the below image for details (Adjust according to your database setup)

My 12c Database already has a repository for Forms & Reports 12c 12.2.1.3, hence I created a new prefix “DEV1”. If you are creating a repo for the first time, you can use “DEV” or any other 3 letter combination (make sure you will able to remember it laters).

Select all the components as seen with the below image. The selected are mandatory, especially the Metadata Services.

Use the same password everywhere! Unless you are setting up a production instance, use the same password everywhere. Example, I use “Abc1234” for the schemas, Weblogic Admin account, node manager etc, which makes the administration much easier. For production, follow the Oracle standards.

That completes the repository creation for a Classic domain.

Create a Classic Domain for deploying forms & reports application

From an elevated command prompt, switch to Oracle Home\oracle_common\common\bin (eg: D:\Weblogic\Middleware\Oracle_Home\oracle_common\common\bin) & execute config.cmd, that will kick start the classic domain configuration wizard.

Select the defaults, unless you have specifics. We will be creating a new domain with the following example.

Refer the below images and select the same components for a successful domain creation for forms and reports.

As I mentioned earlier, use the same password here, so that you don’t have to worry about forgetting & recovering it at later stages.

We will setup the domain for production (never tried Development, so don’t know how exactly both differ from each other). Leave the JDK selection as it is.

Now, your earlier created repository will come in act. Refer the below image(s) and adjust as per your environment

Make sure all the Schema validations are successful. Next step select components as per the below image

Make sure the Server Group is filled the value as seen with the below image. Don’t miss it!

We’ll add an instance OHS now. Click on the add button and refer the below images for more details

Fill in the Listen Address with “localhost” and leave rest to defaults

That finishes the creation of your classic domain.

You should able to access the newly created WebLogic admin server through the URL http://localhost:7001/console

I’m hurrying to publish the POST without further delays, as I am once again caught up with multiple assignments & keeping up with promises are NOT something I am look up to at this time. However, I will add up the missing configurations for OHS and setting up the Forms and reports very soon an an annexure as early as possible!

19th June 2020

Today I installed & configured another Weblogic Server environment & realized that I was supposed to amend this post with more details, is a long due!

I chose a Container+PDB 12c database instance for my Weblogic Server this time. It was fun, finally learning few things about Container and pluggable databases & I am hoping to write another post about setting up Weblogic, Forms and Reports with such a setup in near future.

Now without wasting more time, let us see how to finalize the freshly configured classic domain for Oracle Forms and Reports deployment.

Switch to the your newly created domain’s bin folder from an elevated command prompt. Eg:

“D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\bin”. Usually I always installed Oracle software on D: drive & follow the same naming structure for the folders. It helps me to backup and restore configuration & other files that help me to finish setting up the environments faster.

Start the nodemanger now by issuing the command by executing the command “startnodemanger.cmd”

It may take few seconds to minutes until the node manager starts for the first time. Please remember, you shouldn’t close this command window, which will automatically stop the node manager.

Open another elevated command prompt window and switch to your classic domain’s bin folder. Start the Weblogic admin server by issuing the command “startWeblogic.cmd”

Okay, with node manager, 99.9% chances that you may not bite bullet. With Weblogic Admin server, this is not the case. For number reasons, the Server will try to start and give up in the middle. So don’t panic. If the Weblogic Admin Server is not coming up to a “RUNNING” status after extended period of waiting, go ahead and restart your computer. Start node manager and then try to start the weblogic admin server once again.

The initial startup could take up to 5-6 minutes (depending upon your hardware, this time could vary) & you should see a status like below

Remember not to close the window, which will immediately stop the Weblogic Admin Server. The status of the window will keep on updated against the activities happening at the server side. Once started, you can safely keep both node manager and Weblogic Admin Server CMD windows minimized.

You can logon to the Weblogic Admin server from http://localhost:7001/console

Once after successfully logging in, this is the window you are presented with. We’ve to configure both Forms & Report servers now, by trying to start them. This process will keep on adding many elements to your start menu. Let us see we can start Forms (WLS_FORMS) & Reports (WLS_REPORTS) servers now

Click on the “Control” tab, select both “WLS_FORMS” & “WLS_REPORTS” from servers and press the “Start” button. Once start button clicked, you can press the refresh button to monitor the status of the Servers. Do not forget to click the refresh button again to stop refreshing once after the servers show the status “Running”

It could take minutes for both the servers to reach the “Running” stage. Keep watching the Admin Server command window for unexpected errors. If you have followed all the steps as I’ve instructed throughout my thread, I can confidently say that you shouldn’t have a single hick up until this moment.

Once both servers are running, we can complete the OHS setup finally. Open another elevated command prompt. Switch to Oracle_Common\common\bin folder

Eg: D:\Weblogic\Middleware\Oracle_Home\oracle_common\common\bin and start Weblogic Scripting Tool (wlst)

Now you can issue the command “ohs_updateInstances()” at the wlst prompt & once the configuration completed, exit wlst by issuing “exit()”

You can try to logon to Weblogic Enterprise Manager console and start the newly setup OHS instance now.

Logon to Weblogic Enterprise Manager from http://localhost:7001/em & click the Navigation menu by the left hand side and expand HTTP Server to view your newly created OHS instance. In our case “ohs1”. Click on “ohs1” to open the control panel for the instance

Click the Start up button to start the OHS instance & after showing you message window stating you cannot stop the starting/stopping process, OHS will start. It could take few seconds to minutes depending upon your hardware and available resources.

Give special attention to “Machine Name”.

Now we will see how to bring OHS instance in action by setting up the few other factors like node manager security, configuration files.

Logon to Weblogic console @ http://localhost:7001/console

Lock & Edit the domain security values, please refer the image below for details

nm

Provide a meaningful name for the node manager account and supply a password. I used the same password for the total 12c deployment, unless you are setting up a PRODUCTION instance, you may use the common password across the 12c instances for easy administration of objects when required. Shutdown the services (better). Once the node manager properties are changed, restart the WLS admin server. Please note, always stop all running servers (forms & reports) services prior shutting down the Admin server & I suggest you gracefully shutdown the Admin server by running “StopWeblogic.cmd” instead of using the Weblogic console.

Move to your Oracle Home folder. I have installed Weblogic server in D:\Weblogic\Middleware folder, hence my Oracle home folder is

D:\Weblogic\Middleware\Oracle_Home\

(Adjust your paths accordingly)

Edit forms.conf file in the folder D:\Weblogic\Middleware\Oracle_Home\forms\templates\config\

By default this template file comes with commented examples, like following

#<Location /forms/>
#        SetHandler weblogic-handler
#        WebLogicHost host
#        WeblogicPort port
#</Location>

You need to uncomment one of the blocks starting from <Location /forms> until </Location> & add properties specific to your deployment, for example, my computer’s name is “RAJESH-PC” and I am using default ports for both forms and reports applications, hence I have the properties set like following

<Location /forms/>
SetHandler weblogic-handler
WebLogicHost HOME-THEATER
WebLogicPort 9001
</Location>

Once altered, copy forms.conf to OHS instance “moduleconf” folder. I have copied the file to following location: (Alter according to your setup)

D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\components\OHS\instances\ohs1\moduleconf

Now we will edit the configuration file for reports

Switch to “D:\Weblogic\Middleware\Oracle_Home\reports\conf” folder & edit “reports_ohs.conf” and add a block like following (alter according to your setup)

<Location /reports/>
SetHandler weblogic-handler
WebLogicHost HOME-THEATER
WebLogicPort 9002
</Location>

Copy the edited file to

D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\components\OHS\instances\ohs1\moduleconf

Now the OHS instance know about the deployments and once after restarted, you can try to start the forms based application through the OHS instance, rather than calling it against the default port (9001)

Once the OHS instance is updated with the new information, try to start the OHS instance from a command prompt using storeUserConfig parameter

D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\bin>startComponent.cmd ohs1 storeUserConfig

ohs_manually

1st run, you will be prompted for node manager password, supply the password you have setup earlier and OHS instance should start without creating a fuzz in usual scenarios

In future if you want to start the OHS instance, unless made changes to the configurations, just run

D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\bin>startComponent.cmd ohs1

Above concludes the 1st of level of configurations for Oracle forms based application. You can use Weblogic 12c Enterprise Manager to configure most of the forms environment related parameters like forms path and other, while I am yet to find out relevant configuration areas for reports.

image

You can manually modify the formsweb.cfg & default.env files to start with as well

Regardless whether you are going to use the in-process report server or a stand alone instance of the reports, many changes should be made to the configuration files, so that you can call the reports server from a Forms module

Generally we will be modifying multiple configuration files, I will try my level best to provide the exact locations for the files which you should change according to your installation preferences

Configuration files path (Please note, through versions, there are minor changes happened to these configuration files. The images below were copied from my previous post about configuring for deployment, however the settings remain the same for 12.2.1.4 (as tested on 19th June 2020))

D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\servers\WLS_REPORTS\applications\reports_12.2.1\configuration

  1. rwservlet.properties – Servlet specific properties
  2. rwserver.conf – Server properties
  3. cgicmd.dat – key mapping file

1. As far you are using in-process reports server, nothing much to alter with “rwservlet.properties” file. Basically you should see entries like following in the opened file:

image


2. rwserver.conf (Please check the below image)

image

3. cgicmd.dat: Many values we supply to call a report using Web.Show_Document method could potentially expose vital information like the database details & many other. “cgicmd.dat” file helps us to map such values to keys and later use them wherever applicable. With this example, I will demonstrate, how the database username, password and service name are mapped using a key. Please check the image attached below

image

Once these configurations files are altered, we need to create a report instance for the WLS_REPORT server. Open an elevated command prompt and switch to “D:\Weblogic\Middleware\Oracle_Home\oracle_common\common\bin”. Start wlst & connect to the server.

Issue the command

createReportsToolsInstance(instanceName='rptools1', machine='AdminServerMachine')

Note, you can choose your own name for the report instance. Once the report instance created, exit wlst and you can try to start the WLS_REPORT server from the weblogic console.

This comprehensive note should help you to quickly start with Oracle Forms & Reports 12c (12.2.1.4)

I am avoiding few elements like publishing a Forms based application for now. If you feel, should be added, please let me know through the comments section.

regards,

rajesh

Windows 10 |The device is being used by another application

Hello guys

Greetings! Yes, I know it was a long break. Trust me, I wanted to post many things, couldn’t as I was too busy (doing my actual job, traveling etc ;) )

Anyway, Windows 10 is never packed with less surprises. Recently I was going through a 4K video clip with multilingual audio tracks & realized that the much celebrated VLC player cannot play it, when the default Movie & TV windows app played the clip without a single break or lag. However, as soon as I tried to switch the default language from “Arabic” to “English” I started getting the error “The device is being used by another application” & the app stopped playing the video (lands on the gallery actually)

I was lucky enough to land on a page with correct solution. https://blog.pcrisk.com/windows/12516-how-to-fix-the-device-is-being-used-by-another-application & “Disable Allow Applications To Take Exclusive Control Of This Device Option” section helped me to finally get over with this major annoyance. Not just that, once done, the app remembers the audio track that you selected & plays the same when you open the same video clip next time! unlike VLC player.

If you landed on this page, looking for a solution, please go through the blog from pcrisk.com & they have multiple solutions to this issue and one of the solutions listed there should help you also.

Hope it helps few out there!

regards,

rajesh