RVTPT-020: Subroutine rvtoe_RmaPushApi() – EQuantity cannot be greater than original ordered quantity. returned error

A single music track, movie, event, product… changes the life for many & in my case it was a single API by Oracle!

“oe_order_pub.process_order” that comes with Oracle Applications (We are using EBS R12)

The firm for which I work is using an inhouse developed module for the complete retailing & I take the credit of developing the full solution using this ONE API/around this API.

From a mere “Oracle Forms & Reports” developer with some knowledge about Oracle database, developing around this single API to facilitate Sales Orders and Returns (RMA) slowly shaped me in to whatever I am today.

We are using this custom module from last 11 years and I wouldn’t say there were no issues. We’ve many sales outlets and most of them are connected to the datacenter using ADSL data lines. Sometimes the connections caused, other times code caused, few other times some internal bugs caused problems were there, however against the volume of sales transactions those we make yearly, limited to numbers those could be counted in fingers.

So, recently I was contacted by the sales team, to resolve an issue with a sales return, with lines stuck “Awaiting Return” status. While inspecting the transaction, I realized that the salesmen tried to return this SO multiple times and instead of 2 lines against the sales order, there were 20 lines (10 attempts). I cleared whole those lines with errors and tried to receive the materials once again, bringing up the error:

RVTPT-020: Subroutine rvtoe_RmaPushApi() - EQuantity cannot be greater than original ordered quantity. returned error
 Cause:        Subroutine rvtoe_RmaPushApi() - EQuantity cannot be greater than original ordered quantity. returned an in

Eventually, I landed upon the Oracle support document “RMA Receipt Error:RVTPT-020: Subroutine rvtoe_RmaPushApi() – EQuantity Cannot Be Greater Than Original Ordered Quantity (Doc ID 2409611.1)” & according to the document, this situation arises when there are multiple transactions trying to do a RMA against the same quantities! So I ran couple of quick queries like below:

Select * from oe_order_lines_all where header_id  = (Select header_id from oe_order_headers_all where order_number='18016698');

Fetched all the line ids from the lines table against the order number & then tried to see where exactly the line ids were refernced. Whenever a RMA is facilitated the lines table fills in the columns “REFERENCE_HEADER_ID” and “REFERENCE_LINE_ID” with the header_id and line_id values from the original sales order. All I had to make sure that the line ids were referenced multiple times.

Select * from oe_order_lines_all where reference_line_id IN (4656844, 4656845);

As expected, I was able to find four lines (expected 2 lines only) and was able to track down the 2nd RMA that was automatically created by the API due to some unknown reasons (I said there were few problems using the API)

Based on the suggestions available with the support document, I cancelled the duplicate RMA transaction (Actually another Sales Order with the next immediate document number) & created a new receipt for the Sales Order that was stuck with lines having “Awaiting Return” flow status.

Hope this helps few out there.

rajesh

EBS R12 Cloned Instance | opatch

Not being a DBA has it’s own perks ;) Freedom to try to fix everything that do not look “legit” from all angles.

My last attempt was to rebuild “Central Inventory” for a cloned instance of Oracle Applications R12 (EBS 12.0.6) on Linux. This instance was cloned from a certified platform, to a totally unsupported platform. In addition to, the DBAs those migrated from 10g R2 to 11g R2 left much of the post clone activities pending immediately once after the instance came online.

So, trying to patch Oracle database 11gR2(11.2.0.4) brought me to a situation that never existed:

  • “opatch” was “not” recognized as a command!
  • “opatch lsinventory” run always screamed about corrupt/missing central inventory location.

The “opatch” not being recongized as a command was due to $ORACLE_HOME/OPatch path missing, easily fixed by adding the path to the existing $PATH environment variable for the Oracle user.

One of the other issues you might face up with 11.2.0.4 could be the unsupported opatch version. You need to replace the current opatch version with the latest supported. Please refer to patch #6880880 & Install the patch marked in the below image

Please read the “README” to know how to replace the existing version with the latest supported.

Once the opatch stack taken care of, we still need to fix the central inventory, that keeps reported as being missing.

[oratest@bak clone]$ opatch lsinventory
 Oracle Interim Patch Installer version 11.2.0.3.25
 Copyright (c) 2020, Oracle Corporation.  All rights reserved.
 Oracle Home       : /u01/oratest/TEST/db/tech_st/11.2.0
 Central Inventory : /u01/oratest/TEST/db/tech_st/11.2.0/inventory
    from           : /u01/oratest/TEST/db/tech_st/11.2.0/oraInst.loc
 OPatch version    : 11.2.0.3.25
 OUI version       : 11.2.0.4.0
 Log file location : /u01/oratest/TEST/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2020-11-11_10-35-04AM_1.log
 LsInventorySession failed: OPatch failed to locate Central Inventory.
 Possible causes are:
     The Central Inventory is corrupted
     The oraInst.loc file specified is not valid.
 OPatch failed with error code 73

Let us see how to rebuild the Central Inventory for the EBS R12 cloned instance now.

If your current server (the one that has the cloned instance of the application) never had an Oracle installation executed from the same, will not have /etc/oraInst.loc file, instead the file must be available under $ORACLE_HOME & the file MUST BE pointing towards the source server paths. For example, my cloned instance oraInst.loc contents are like below:

>cat oraInst.loc
 inventory_loc=/u05/oraprod/oraInventory
 inst_group=oinstall

where inventory_loc path wrong for the current server.

You may create something equivalent to what already exists with the oraInst.loc file, for example (in my case) a path like /u01/oratest/oraInventory as “Oracle” user.

So, you need to amend the oraInst.loc file that is available with $ORACLE_HOME path like below

>cat oraInst.loc
 inventory_loc=/u01/oratest/oraInventory
 inst_group=oinstall

Now you can proceed building the Central Inventory.

logon as Oracle user

SHUTDOWN THE DATABASE & ALL OTHER ORACLE DATABASE RELATED SERVICES

  1. source the environment
  2. switch to $ORACLE_HOME/appsutil/clone
  3. execute ouicli.pl (./ouicli.pl)

Now, We will attach the current ORACLE_HOME with the Central Inventory. Many things could go wrong here, so make sure that you refer the document “How to create, update or rebuild the Central Inventory for Applications R12” in case if you are stuck for reasons those are not mentioned in this post.

As Oracle user

switch to $ORACLE_HOME/oui/bin

Execute

./runInstaller -ignoreSysPrereqs -silent -attachHome -invPtrLoc $ORACLE_HOME/oraInst.loc \
 ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=""

Example:

./runInstaller -ignoreSysPrereqs -silent -attachHome -invPtrLoc $ORACLE_HOME/oraInst.loc \
 ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME="TEST_Oracle_HOME"

If there are no issues, you should get ” ‘AttachHome’ was successful ” confirmation once after the installer finishes the execution.

Test opatch once again

>opatch lsinventory

The above should provide you an output like below:

[oratest@bak bin]$ opatch lsinventory
 Oracle Interim Patch Installer version 11.2.0.3.25
 Copyright (c) 2020, Oracle Corporation.  All rights reserved.
 Oracle Home       : /u01/oratest/TEST/db/tech_st/11.2.0
 Central Inventory : /u01/oratest/oraInventory
    from           : /u01/oratest/TEST/db/tech_st/11.2.0/oraInst.loc
 OPatch version    : 11.2.0.3.25
 OUI version       : 11.2.0.4.0
 Log file location : /u01/oratest/TEST/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2020-11-11_10-42-05AM_1.log
 Lsinventory Output file location : /u01/oratest/TEST/db/tech_st/11.2.0/cfgtoollogs/opatch/lsinv/lsinventory2020-11-11_10-42-
 Local Machine Information::
 Hostname: bak.localdomain.com
 ARU platform id: 0
 ARU platform description::
 There are no Interim patches installed in this Oracle Home.
 
 OPatch succeeded.

That’s all folks. You can proceed with your patching for the database now! Hope this helps few certified DBAs out there ;)

rajesh

FRM-41826: Cannot Replace Group;columns Don’t Match Lov

I was trying to revamp a “fully functional” custom module for our Oracle EBS R12 Order Management & came across “FRM-41826: Cannot Replace Group;columns Don’t Match Lov” while a new record group was set for an existing LOV during runtime based on the user choice.

I was pretty sure that the query is flawless as all I did was to removing some UNIONs and thus stripping off rows from the result set!.

After spending almost hour time, Finally I decided to check the columns returned by the record group. My first record group has the below SQL

select CUSTOMER_ID party_id, CUSTOMER_NUMBER, CUSTOMER_NUMBER ACCOUNT_NUMBER, CUSTOMER_NAME, null CASH_CUSTOMER_ID
from OMS_SHIPTO_ADDRESS_V where org_id = :PARAMETER.ORG_ID
AND CUSTOMER_ID NOT IN (Select account_id from OMS_ORGS_CASH_ACCOUNTS where organization_id = :PARAMETER.ORG_ID)
UNION ALL
SELECT oocc.account_id party_id, occ.PHONE_1 CUSTOMER_NUMBER, TO_CHAR(oocc.ACCOUNT_NUMBER) ACCOUNT_NUMBER, occ.CUSTOMER_NAME, occ.CUSTOMER_ID CASH_CUSTOMER_ID FROM OMS_CASH_CUSTOMERS occ, OMS_ORGS_CASH_ACCOUNTS oocc
WHERE occ.PHONE_1 IS NOT NULL
AND occ.ORGANIZATION_ID = :PARAMETER.ORG_ID
AND oocc.organization_id = occ.ORGANIZATION_ID
AND PRINT_PHONE_1 = 'Y'
UNION ALL
SELECT oocc.account_id party_id, occ.PHONE_2 CUSTOMER_NUMBER, TO_CHAR(oocc.ACCOUNT_NUMBER) ACCOUNT_NUMBER, occ.CUSTOMER_NAME, occ.CUSTOMER_ID CASH_CUSTOMER_ID FROM OMS_CASH_CUSTOMERS occ, OMS_ORGS_CASH_ACCOUNTS oocc
WHERE occ.PHONE_2 IS NOT NULL
AND occ.ORGANIZATION_ID = :PARAMETER.ORG_ID
AND oocc.organization_id = occ.ORGANIZATION_ID
AND PRINT_PHONE_2 = 'Y'
ORDER BY 2

while the 2nd record group has the below SQL

select CUSTOMER_ID party_id, CUSTOMER_NUMBER, CUSTOMER_NUMBER ACCOUNT_NUMBER, CUSTOMER_NAME, null CASH_CUSTOMER_ID
from OMS_SHIPTO_ADDRESS_V where org_id=:PARAMETER.ORG_ID ORDER BY 2

As simple as it is.

After wasting almost an hour time to figure out what went wrong, finally I decided to go through the record group columns, hoping some kind of mismatch between the columns returned by both SQL blocks! (Just hoping)

First RG

Second RG

The first SQL block was returning Customer ID values, which were numeric and the 2nd SQL block was returning NULL, mapping the datatype as Character!

and this was causing FRM-41826.

I fixed it by change the NULL to 0 with my second block.

select CUSTOMER_ID party_id, CUSTOMER_NUMBER, CUSTOMER_NUMBER ACCOUNT_NUMBER, CUSTOMER_NAME, 0 CASH_CUSTOMER_ID
from OMS_SHIPTO_ADDRESS_V where org_id=:PARAMETER.ORG_ID ORDER BY 2

If you ever get stuck with such situation, do a quick analysis on the Column Specification & insure that both record groups are returning the columns with same datatypes!

regards,

rajesh

OneDrive isn’t signed in

Updated on: 23rd November 2020

Well, it looks like the bug that Microsoft is dealing with is not something simple. Yesterday I was forced to change the password for my account, which has Administrator privileges due to some “too much gaming” by my 6 years old ;)

Immediately after the password change and restart, I started getting the message “OneDrive isn’t signed in” & one more thing I noticed was Microsoft Edge’s Sync profile account signed off. I always had a feeling that Microsoft Edge has something to do with the entire mess & I am not sure whether observing the above has any relevance as I am using individual apps to sign in, than using the live account globally.

The one positive thing until is, the entered credentials are still holding up & I was not asked to enter them once again today morning after cold reboot. It looks like Microsoft has messed up the Windows Credential Manager thingy little too deep this time!

**23rd November Update end***

“OneDrive isn’t signed in Please enter your sign-in info to start syncing again.” One of the little annoyances I had to live with post Windows 10, 2004 update.

After long “Googling” I came across multiple suggestions, none of which truly resolved my issues with OneDrive “automatic” signing in. Following couple of leads, I landed on to “Windows Credentials” and found that the OneDrive credentials were missing after a long shutdown & reboot.

This helped me to start finding details about cached credentials being “Expired” after a long interval between shutdown and reboot. I couldn’t find anything very specific explaining about this behavior until this moment.

Then I decided to uninstall OneDrive for a fresh installation. I uninstalled OneDrive, rebooted the box, reinstalled the software and tried various combinations like rebooting, cold reboots for next couple of hours to insure that the cached credentials were holding up. Started this thread and went to sleep the same night & next morning found that the cached credentials were once again missing!

While we were migrating from Windows XP to Windows 7, initial days the OS was plagued with unexplained bugs, causing this blogosphere :). One of the major issues we dealt with was corrupt profile for domain users, those required us to drop and recreate them on client machines!

I applied the same with this case, and did the following:

  • Enabled the “Administrator” account (You need minimum one account with Administrator privileges to carryout below)
  • Made necessary backups for the user account from which I was having OneDrive sign on issues. Please note, by deleting the user folder, you might lose some valuable data. So make sure adequate backups are made prior you delete the user account and related folders
  • Deleted the user account
  • Restarted & deleted the user folder (mandatory)
  • Re-created the user profile with same username!

Now Windows Credential Manager is holding up OneDrive credentials after 72 hours & I think this could be one of the possible fixes among many others!

regards,

rajesh

iPhone 11 stuck in the boot loop

Things go wrong, iPhones get in the boot loop & I had a nightmare two days back with the new iPhone 11 64GB, that was given to me as a corporate contract device.

As I don’t use Apple devices, I decided to exchange it with Wife’s Samsung Galaxy M30s. I setup up the device & came the menace. She had years long Whatsapp chats & asked the “Computer Guy” the question that hurts “You can’t do it right?!”

I bought iTransor & managed to “successfully migrate” the messages from Android to iPhone. The device restarted and that’s it. It got stuck in the boot loop, a partially eaten White Apple flashing againt a dark dark background. (iTransor is a pretty celebrated migration software & I believe things could get complicated at certain times & do not blame their software for the troubles I had. After all, I also develop software those act weird at times ;) )

I watched few YouTube videos, referred few articles etcetera and finally downloaded an app that claimed it would fix any boot related issues with iPhones and over 250 Apple devices. Following their neat instructions, connected the iPhone to my Windows Laptop using the provided cable & I reached to an instruction that asked me to:

  • Press volume up
  • Press volume down
  • Press and hold the Side button (right side single button on iPhone 11) “long”, even after the screen goes blank and Apple logo re-appears. Well in my case, after 10+ seconds, the iPhone screen shown me connect to PC image on the screen & finally I breathed. I was worried that I have to go back to my HR, get hold a copy of the invoice, approach a service center, wait for weeks until they “fix” the unit and returned to me..(Please watch the below video for a demonstration)

Once the connect to PC image appeared, I knew that I don’t need another paid software to “Fix” the unit. I repeated the cycle, this time with iTunes opened. iTunes immediately reported that the iPhone has an issue and to resolve, either the device should be updated or restored. Update failed, restore succeeded. My wife didn’t dare to challenge the computer guy once again and agreed that she would sacrifice her Whatsapp chats for the device.

So if you are ever stuck with an iPhone, have access to a computer & could follow few simple instructions, give the above a try. You may save some real bucks or end up with a truly bricked device ;)

regards,

rajesh

RSAT: Remote Server Administration Tools for Windows 10

The last standalone package for Remote Server Administration Tools was released for Windows 10 Version 1803 & the same was integrated into Windows 10 as optional packages with later versions of Windows.

There are many detailed installation instructions floating over dozens of tech blogs and sites, explaining how to enable/install RSAT on Windows 10. Today we will go through a quick how to without breaking much sweat & details.

As a Windows Domain Administrator, I need the following features at times:

  1. Active Directory Users and Computers
  2. DNS Manager

For the rest, usually I use the remote desktop connection to domain controllers.

Please note, with each version of Windows 10, there might be minor changes with the way software is installed. Hence, make sure that you double check your version of Windows 10 before attempting the below instructions. These instructions are valid for Windows 10, Version 2004 with September 2020 cumulative updates.

Open Add or remove programs (Modern App management console)

Click on “Optional Features”

Click on “Add Feature” that will open up a list of available optional features. Remote Server Administration Tools or RSAT could be filtered by entering RSAT in the search area.

As I needed only “Active Directory users and computers” and “DNS Server” components, I chose them. You might need more components to be available to carry out your server administration. Choose everything that applies to your requirements & Press the “Install” button.

That’s all. Now you can access the features those were installed based on your selection from the start menu or by searching for them in the taskbar search box.

regards,

rajesh

Linux multipath partitions unavailable after cold restart

Yesterday I shutdown one of our legacy system Linux servers after a long interval. May be almost after 1.5 years. Once restarted, I started getting alert emails from cronjobs which were using those mount points. After a quick checking I found that few the UUIDs I have used with fstab were missing when I issued “blkid” command…

Please note, I am not at all a Linux/Storage expert. I consider “finding” this solution as a blind shot as my immediate technical support guys were too busy to answer the calls. You are asked not to copy these to a production instance! I took the risk because the data, the server itself is NOT significant for us and we had the freedom to rebuild it as and when we needed it.

Linux Server IBM x3560 running OEL 6 & the storage device is DS3200 that uses HBA interface to the physical server.

Doing a fdisk -l listed the following for me:

Disk /dev/mapper/mpathb: 644.2 GB, 644245094400 bytes
255 heads, 63 sectors/track, 78325 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 

             Device Boot      Start         End      Blocks   Id  System

Disk /dev/mapper/mpathc: 214.7 GB, 214748364800 bytes
255 heads, 63 sectors/track, 26108 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 
			
			 Device Boot      Start         End      Blocks   Id  System

and I remembered that earlier I had listings like the below

Disk /dev/mapper/mpathb: 644.2 GB, 644245094400 bytes
255 heads, 63 sectors/track, 78325 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 

             Device Boot      Start         End      Blocks   Id  System
/dev/mapper/mpathbp1               1       78325   629145531   83  Linux

Disk /dev/mapper/mpathc: 214.7 GB, 214748364800 bytes
255 heads, 63 sectors/track, 26108 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 

             Device Boot      Start         End      Blocks   Id  System
/dev/mapper/mpathcp1               1       26108   209712478+  83  Linux

In addition to, I have noticed that there were two devices /dev/sdd, /dev/sde with the same size of the multipath sizes, however without any paritions.

After rebooting twice, I decided to create new partitions under /dev/sdd & /dev/sde which succeeded. However, when I tried to format these newly created partitions, I started getting “/dev/sdd1 is apparently in use by the system; will not make a filesystem here” and “/dev/sde1 is apparently in use by the system; will not make a filesystem here”, that forced me to restart the server once again.

To my utter surprises, once the machine booted up, all my mount points were back online once again without doing anything else.

[root@xyz multipath]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sdk2              59G   11G   46G  20% /
tmpfs                  10G  228K   10G   1% /dev/shm
/dev/sdk1             2.0G  330M  1.5G  18% /boot
/dev/sdk5             738G  632G   69G  91% /u01
/dev/mapper/mpathbp1  591G  332G  229G  60% /u02
/dev/mapper/mpathcp1  197G  134G   54G  72% /u03
/dev/sdg1             591G   70M  561G   1% /u04
/dev/sdj1             269G   59M  256G   1% /u05
/dev/sda1             917G  765G  107G  88% /usbdrive
/dev/sdb1             1.8T  642G  1.1T  37% /RDX
Disk /dev/sdd: 644.2 GB, 644245094400 bytes
255 heads, 63 sectors/track, 78325 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00035652

   Device Boot      Start         End      Blocks   Id  System
/dev/sdd1               1       78325   629145531   83  Linux

Disk /dev/sde: 214.7 GB, 214748364800 bytes
255 heads, 63 sectors/track, 26108 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0002b47b

   Device Boot      Start         End      Blocks   Id  System
/dev/sde1               1       26108   209712478+  83  Linux

Disk /dev/mapper/mpathb: 644.2 GB, 644245094400 bytes
255 heads, 63 sectors/track, 78325 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00035652

             Device Boot      Start         End      Blocks   Id  System
/dev/mapper/mpathbp1               1       78325   629145531   83  Linux

Disk /dev/mapper/mpathc: 214.7 GB, 214748364800 bytes
255 heads, 63 sectors/track, 26108 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0002b47b

             Device Boot      Start         End      Blocks   Id  System
/dev/mapper/mpathcp1               1       26108   209712478+  83  Linux

Prior deciding to create the partitions once again, I cross verified that multipath daemon was loaded and I can see the information. For a primarily a Windows OS person, the whole thing looked like a messed up “File Allocation Table”

[root@erp-prodbak ~]# multipath -ll
mpathc () dm-1 IBM,1726-2xx  FAStT
size=200G features='1 queue_if_no_path' hwhandler='1 rdac' wp=rw
|-+- policy='round-robin 0' prio=6 status=active
| `- 2:0:0:2  sde 8:64  active ready running
`-+- policy='round-robin 0' prio=1 status=enabled
  `- 3:0:0:2  sdi 8:128 active ghost running
mpathb () dm-0 IBM,1726-2xx  FAStT
size=600G features='1 queue_if_no_path' hwhandler='1 rdac' wp=rw
|-+- policy='round-robin 0' prio=6 status=active
| `- 2:0:0:1  sdd 8:48  active ready running
`-+- policy='round-robin 0' prio=1 status=enabled
  `- 3:0:0:1  sdh 8:112 active ghost running

I have confirmed that the ids in use were same as /etc/multipath/wwids and bindings files.

Well, may be I was truly lucky to “get it done” this time without understanding what actually went wrong. You may not apply this solution to production environment in case if you are dealing with important data!

regards,

rajesh

aspnet_client | Windows copying hangs

Hello guys

After weeks long considerations, finally I decided to give my development machine a fresh Windows installation using the Windows 10 version 2004 media. First time in my life, I opted GPT for Windows and switched to UEFI with secure boot.

While restoring my .Net project files, I noticed that the entire copying process getting hanged up while trying to copy “aspnet_client” folder from the backup disk to new location.

aspnet_client is a symbolic link to system_drive:\inetpub\wwwroot\aspnet_client folder, that is created within a project when SAP Crystal Reports for Visual Studio is referenced in a VS solution. I’m not sure whether the same symbolic link is created while using other 3rd party tools.

As my Crystal Reports experiments were recent and had multiple difficulties to get it finally work through my projects, immediately I setup IIS, which in turn created the “inetpub\wwwroot\aspnet_client” path on my box.

Next attempt, I was able to successfully copy the solutions to my box without any issues. It looks like, while symbolic links are used, the copying process tries to verify the paths & if the path doesn’t exist, process gets locked up.

If you are ever getting locked up trying to copy “aspnet_client”, I hope you will come across this post and happily end up with your tasks.

rajesh

Crystal Reports | Passing parameters from a .Net Application

Hi guys

This is the 2nd part of my experiences with SAP Crystal Reports for Visual Studio. I’ve posted about installing & creating a simple Crystal Report earlier, if you haven’t read it yet, please find it here.

One of the major confusions around passing values to a Crystal Report was contributed by multiple questions and answers available on StackOverFlow. The ones which came nearest to my requirements had only “teasers”, not real solutions! Finally on the 3rd or 4th day, I realized that, the parameters were called based on the index numbers and the finding the index numbers for parameters were as simple as counting the 1st parameter as “0” & incrementing the index number with 1 for subsequent parameters.

Example Scenario:

I am using a stored procedure with the crystal report that accepts more than 1 parameter to select data from different tables

Here with this example, I am trying to list the salaries for employees based on the year, month & company section in which they are working as filters. An end user will pick up those filters from a web form and pass to the crystal report before the final report is generated.

Crystal report has a very simple method to pass the parameters & I am limiting this post to “Stored Procedure” based reports. The same should apply to table/dataset based reports as well until you have sub-reports involved.

In this example, you could see that I have @pProcessYear, @pProcessMonth & @pProcessSection as parameters for the report. Notice the order they are listed (refer the picture)

@pProcessYear is listed first, followed by @pProcessMonth & @pProcessSection. So the index number for @pProcessYear is 0 and @pProcessMonth is 1 & @pProcessSection is 2!

Now, let us see how we will pass values to these parameters from a WebForm.

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Table = CrystalDecisions.CrystalReports.Engine.Table;

namespace insert_demo
{
    public partial class ShowEmp : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                ReportDocument reportDocument = new ReportDocument();
                reportDocument.Load(Server.MapPath(@"~/CrystalReport2.rpt"));
                reportDocument.SetParameterValue(0, 2020);
                reportDocument.SetParameterValue(1, 1);
                reportDocument.SetParameterValue(2, 3);

                reportDocument.DataSourceConnections[0].SetConnection("RAJESH-PC", "MenaSS", true);
                CrystalReportViewer1.ReportSource = reportDocument;

            }
        }
	}
}

Notice the intellisense suggestion for Crystal Reports document parameter. Definitely “ReportDocument.SetParameterValue” expects an integer value for the index, when much of the examples available with StackOverFlow were passing report parameter names in the place of index, which didn’t work at all. I am forced to believe, Crystal Reports used to accept this for previous versions of Crystal Reports for Visual Studio & may be not many are using Crystal Reports with Visual Studio anymore…? Interestingly most of the questions related to the subject were many years old.

So, it is as simple is going through the order of the parameters as they are listed on the report design form (the order never changes, unless you delete and recreate them), starting with the index number 0 for the first parameter, increase by 1 until the last parameter & pass the values accordingly & you are done!

With my next post, I will try to talk about sub-reports and complex formatting using available Crystal Report tools.

regards,

rajesh

SAP Crystal Reports for Visual Studio

Hello guys

My .Net developments were ALWAYS at risk! Whenever I am doing it good, the project gets cancelled & I return to the Oracle world.

Anyway, for the last project that is about be shelved, I chose SAP Crystal Reports in addition to Microsoft’s RDLC for few reasons. Microsoft has stopped shipping Report components with their IDE Visual Studio & getting it work by installing Nuget packages and extension methods are not going to be very easy for most of the newbies (I am a life long newbie when it is all about .NET development)

On the other hand, installing & going online with SAP Crystal Reports for Visual Studio is pretty straight forward. Download the package from SAP, install it and you are all set to go. Well definitely not!

Let us quickly see how to install & get the most out of SAP Crystal Reports.

  1. Installation
  2. Server Runtime
  3. Your 1st Crystal Report
  4. POST BACK and hacks

Installation

Register & download SAP Crystal Reports for Visual Studio. Make sure you install the runtime engine which is prompted during the installation. Simple as it is.

Server Runtime

If you are publishing your application with SAP reports from another computer/Server make sure you install the Runtime package.

If you’ve installed the runtime, this will create the below structure under inetpub\wwwroot folder

Your 1st Crystal Report

If you are a .Net developer, already familiar with dragging and dropping controls from the Toolbox node(s). Crystal Reports is not different. Just drag and drop “CrystalReportViewer” control to your page & you are all set to go.

Dropping the control to the page adds many references to your project. You may scroll through the references to identify them. All the references have “Crystal” in the name.

Now let us create a sample report & view the report on the web.

If this is your 1st attempt to create a report, select the default & continue. Based on the connections already available in the environment, you will be provided existing connections or an option to create a new connection.

Here is the catch. If the database (MSSQL or other) is configured for integrated security & you prefer to continue using the same for your project, you must make sure that from your webform/page you will be sticking to the same authentication method. Said, you cannot use integrated authentication during design and username/password login during the runtime. So be careful when you are creating connections.

We will create a new connection using username and password to local MS SQL server.

You don’t have to change anything here. Just click the “Finish” button to complete the connection.

That’s it. This creates a new connection and the same will be available under “My Connections”.

Now from the connection, you can select a table, view or stored procedure for your report as source. I have selected a table “bal2020” as you could see with the below image.

Once you click the OK button, you will be taken back to object browser window.

You can see that the table you have selected is shown under “Database Fields” node and expanding the Table node will show you the available columns those you could add to the report.

Drag and drop the columns you want to add in the “Section 3 (Details)” area and the act creates relevant titles in the “Section 2 (Page Header)” area. You can always modify the titles.

Unlike RDLC, you can immediately preview your reports from the design window itself by switching to “Main Report Preview” tab.

Let us see how this report viewed from a webform/page.

As we are trying to create a report for the first time, I suggest you to use the GUI for linking your newly created report with the webform/page.

Select “New Report Source” form “Choose Report Source” and select your newly created report. Leave the names as seen for this exercise.

Prior attaching the report, the Crystal Report Viewer control code was minimal like below

<body>
    <form id="form1" runat="server">
        <div>
            <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
        </div>
    </form>
</body>

and after linking the report to the viewer, you will notice that a number of elements are added to the viewer.

 <form id="form1" runat="server">
        <div>
            <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="True" 
                GroupTreeImagesFolderUrl="" Height="1202px" ReportSourceID="CrystalReportSource1" 
                ToolbarImagesFolderUrl="" ToolPanelWidth="200px" Width="1104px" />
            <CR:CrystalReportSource ID="CrystalReportSource1" runat="server">
                <Report FileName="CrystalReport1.rpt">
                </Report>
            </CR:CrystalReportSource>
        </div>
    </form>

As there are not complex code involved as in with stored procedures, your page will load the report. You can save the webform & try to see whether it truly gets loaded on the browser.

Save everything & build the solution & debug the page that you have just created. Do not use Internet Explorer for debugging. You may end up with “N” number of errors by using IE.

Something is wrong, right? let us see why the report is not loading. With this specific case, an empty page in the place of report viewer is due to missing runtime binaries. If you remember, I asked you to install the runtime while installing the report developer. So there is something wrong…

I don’t have the technical knowledge to confidently say it is a bug , however looks like one, with certain solutions, the symbolic link to rootdrive:\inetpub\wwwroot\aspnet_client is not created within the solution folder after Crystal Report Viewer control is added. Failing to find “aspnet_client” symbolic link to the actual path where the runtime binaries are kept results in failing to load the report on the browser, unfortunately without generating visible errors. While missing the symbolic link being one of the major reasons, there could be few other reasons as well like application pool configured for both 32 Bit & 64 Bit. I have noticed that if your default application pool is configured to cater both 32 & 64 Bit, the 64 Bit runtime will not load & errors will be generated while trying to load reports. If you have installed 64 Bit SAP components, create a new application pool exclusively for 64 Bit only.

To resolve the issues due to runtime binaries, we can either create a symbolic link to “rootdrive:\inetpub\wwwroot\aspnet_client” or copy the entire aspnet_client folder to project, which is approximately 17-18 MBs in size.

I will always prefer the 1st option. So let us see how we can create a symbolic link to “rootdrive:\inetpub\wwwroot\aspnet_client” from our project.

The above symbolic link is explained as below.

Your project is created under user’s home folder. For example, my username is Rajesh. Hence Visual Studio has created a path “sources\repos” where all new solutions will be created and stored (unless I change the VS options)

As I have chosen “CrystalReportsDemo” as my solution name, a folder with the same name is created inside “sources\repos” and the solution related files and folders are kept within a subfolder with the solution name. Finally, the path will look like below once after the solution is created.

"C:\Users\Rajesh\source\repos\CrystalReportsDemo\CrystalReportsDemo"

We are expected to create the symbolic link to “aspnet_client” folder inside the “CrystalReportsDemo” subfolder.

The symbolic link must have a name, hence you will provide “aspnet_client” as the link name (no other names please!) and refer the original path of aspnet_client as source.

mklink /D "C:\Users\Rajesh\source\repos\CrystalReportsDemo\CrystalReportsDemo\aspnet_client" "C:\inetpub\wwwroot\aspnet_client"

I know it sounds bit complex and I hope you will get a hang of it with practice. A successfully created symbolic link will look like below. If you have grouping enabled for the files in explorer, the newly created symbolic link will be listed under folders.

Let us try to view the webform once again after creating the symbolic link.

Basically, when you add a crystal report to your solution, it is treated as an “Embedded Resource” & not copied to output directory. Unless you change these properties, your published application will not able to access the reports. Let us see quickly how these changes are made.

Now the output directory will copy the report files each time the solution is built.

PostBack & hacks

What is PostBack? PostBack is the name given to the process of submitting an ASP.NET page to the server for processing. This has a big effect on how Crystal Reports behave.

Consider a case when you have a webform that has few user choices & a crystal report attached to it. By default much of the ASP.Net controls support “AutoPostBack” property & very useful when a developer wants to refer to other controls values programmatically. Well, each instance of AutoPostBack causes the Crystal Report to refresh itself.

As you are already aware ASP.Net is stateless and Crystal Report will loose all functionalities like progressing to next page or previous page etc when associated buttons on the toolbar is pressed immediately after a postback. In addition to losing these functionality, you may be prompted to enter the database connection details. SAP recommends using Page_Init() instead of Page_Load() method in addition to using session variables to load the report after postback. We will see a complex sample now, which is from a production environment.

Scope of the report

Generate a report for Finance department that lists all salary elements for a chosen company, department & few other selections by the end user.

So this particular report has a total of 5 parameters received from the user & clicking the “Print Report” should show the report with relevant data.

The above report uses multiple stored procedures to fetch the relevant data for the report & copying the script here will defy the intend. Hence I will copy a sample that I posted as an answer to my own question over StackOverFlow & explain it.

You may refer to the stackoverflow thread here.

The below example generates the same report sample you have seen above, however much of the parameters are hardcoded & the only user interactable control is the button. This report uses database stored procedures for report data.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="c4.aspx.cs" Inherits="CrystalTest.c4" %>
<%@ Register Assembly="CrystalDecisions.Web, Version=13.0.4000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" Namespace="CrystalDecisions.Web" TagPrefix="CR" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:Button ID="Button1" runat="server" Text="Print Report" OnClick="Button1_Click" />
            <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
        </div>
    </form>
</body>
</html>

Code behind for the webform is like below:

using CrystalDecisions.CrystalReports.Engine;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace CrystalTest
{
    public partial class c4 : System.Web.UI.Page
    {
        protected void Page_Init(object sender, EventArgs e)
        {
            if (IsPostBack)
            {
                CrystalReportViewer1.ReportSource = (ReportDocument)Session["Report"];
            }
        }
        private void ShowReport1()
        {
            string ConnectionString = ConfigurationManager.ConnectionStrings["menass"].ToString();
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("GETMONTHSALARY", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@pProcessYear", SqlDbType.Int).Value = 2020;
                    cmd.Parameters.Add("@pProcessMonth", SqlDbType.Int).Value = 1;
                    cmd.Parameters.Add("@pProcessSection", SqlDbType.VarChar).Value = "9";
                    cmd.Parameters.Add("@pProcessSite", SqlDbType.VarChar).Value = "1";
                    cmd.Parameters.Add("@pProcessCatg", SqlDbType.VarChar).Value = "1";
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds, "SalaryDT");
                    ReportDocument oRpt = new ReportDocument();
                    oRpt.Load(Server.MapPath(@"~/dataset/CrystalReport1.rpt"));
                    oRpt.DataSourceConnections.Clear();
                    oRpt.SetDataSource(ds);
                    oRpt.Subreports[0].SetDataSource(FillOverTime());
                    CrystalReportViewer1.Visible = true;
                    CrystalReportViewer1.ReportSource = oRpt;
                    Session["Report"] = oRpt;
                }
            }
        }
        private DataSet FillOverTime()
        {
            string ConnectionString = ConfigurationManager.ConnectionStrings["menass"].ToString();
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("GetEmployeeOverTime", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@pEmployeeCode", SqlDbType.VarChar).Value = DBNull.Value;
                    cmd.Parameters.Add("@pProcessYear", SqlDbType.Int).Value = 2020;
                    cmd.Parameters.Add("@pProcessMonth", SqlDbType.Int).Value = 1;
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataSet ds1 = new DataSet();
                    adapter.Fill(ds1, "OverTimeDT");
                    return ds1;
                }
            }
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            ShowReport1();
        }
    }
}

The above sample uses 2 different stored procedures to generate data for main report and a sub report. ShowReport1() method is triggered when the button is clicked. Once the report is generated, it is saved into a session object & whenever a post back happens, this saved object is assigned to report source. Simple as it is. Each button click on the reports toolbar is treated as post back, triggering the report source being assigned again and again. You might notice that I don’t have a Page_Load() method with the code behind!

I know this is just a beginning & including more detailing might make the post irrelevant. With my next post, I will try to explain Crystal Reports parameters, sub-reports etc.

2nd Part. Passing parameters to Crystal Reports

regards,

rajesh