First and Last Day of Month – Oracle SQL Syntax Examples

john.kingTechnical Tips8 Comments

First Day of Current Month 1 SELECT trunc(sysdate) – (to_number(to_char(sysdate,’DD’)) – 1) FROM dualselect trunc(sysdate) – (to_number(to_char(sysdate,’DD’)) – 1) from dual Last Day of Current Month 1 SELECT add_months(trunc(sysdate) – (to_number(to_char(sysdate,’DD’)) – 1), 1) -1 FROM dualselect add_months(trunc(sysdate) – (to_number(to_char(sysdate,’DD’)) – 1), 1) -1 from dual

Oracle Enterprise Manager (EM) 11g – Adobe AIR Desktop Widgets

AshokTechnical TipsLeave a Comment

I like seeing Oracle leverage non-Oracle technology, like Adobe Flex and AIR. I hope that trend continues for Oracle…while Microsoft goes off to build things like Silverlight and Apple simply decides to avoid support for Flash altogether on their iPhone and iPad devices. I haven’t played with these widgets myself yet, but I do like the idea and sure we will be leveraging them. Click to Download – Oracle Enterprise Manager 11g Desktop Widgets Click to Blog – Oracle Enterprise Manager 11g Desktop Widgets

Google Apps Reporting API – Not quite this easy with Exchange

AshokIndustry Trends, Technical TipsLeave a Comment

I have been somewhat enamored by Google Apps and its potential. Many enterprises have been happy with Exchange for mail, calendaring, contacts, and more. I have been one of those people. However, I have found Google Apps to be getting better and better for use in the enterprise and I will not be surprised to see it begin to take significantly larger chunk of market share from Microsoft over time.

Convert INT to BOOLEAN in a SQL SELECT for Multi-Tiered Sorting

john.kingTechnical Tips1 Comment

We have a family tree. Some people have children, while others do not. We want to sort results by alphabetically listing individuals with children in an initial group, and then those without children in a second group — also alphabetically. Let’s assume there is not an isParent column (nor the equivalent) on the table, but there is a way to determine how many children each individual has.

Supplier Portal – Oracle EBS and Middleware

AshokCase StudiesLeave a Comment

The Challenge

ABC, a publicly traded services and manufacturing organization, has leveraged Oracle E-Business Suite (EBS) to manage a variety of back-office functions, including their suppliers. Suppliers have been provided data from EBS manually by internal teams — the data sent has varied in both content and as well as format.

Internal teams spend valuable time generating reports manually for external supplier organizations and suppliers are left with the tedious process of generating their own reports. All the while, both groups may be relying on measures out of line with ABC’s priorities.

The Solution

We took a systematic approach to the issue, addressing:

  1. EBS/Data
  2. Discoverer/Reporting Logic
  3. Portal/UI

The Results

Finally, by setting up proper SSO and hooks from Portal to Discoverer and integrating end-to-end all the way down into the database, users in Portal (regardless of whether they are internal or external users) see only what their user account can see. Additionally, by leveraging the latest Portal 10.1.4 features, we were able to provide ABC with a portal that had a completely customized look-and-feel matching their external website exactly.

Within days, the entire solution was architected, designed, and developed. We were all excited about the quick progress and all of ABC’s expectations were exceeded.

Discoverer – Public Connections for Oracle Applications with SSO

AshokTechnical TipsLeave a Comment

While using SSO, in order to create a public connection to Oracle Discoverer for Oracle Applications, I suggest creating a service account (user) in EBS and marking that user for LOCAL authentication (instead of SSO). There may not be many reasons to create a public connection in this scenario, but I did come across one recently and it is not well-documented, so I thought I would share. Of course, with Oracle BI Discoverer (non-Oracle-Applications), connections are made by database users and creating a public connection doesn’t require any EBS changes.

Selected for Oracle iSupplier Portal Integrated Solution

AshokNews and UpdatesLeave a Comment

Bringing together many components of the Oracle Fusion Middleware Suite, as well as Oracle Applications (via E-Business Suite), M&S is uniquely positioned to delivery immense value in a short amount of time. M&S is designing and implementing a security, integration, and user presentation architecture with Oracle’s EBS, Discoverer, Portal, and Identity Management.

BI, Datawarehousing, and ETL

AshokCase StudiesLeave a Comment

The Challenge

  • Senior Management at a silicon wafer manufacturing facility were making decision based on incomplete or inaccurate operational data
  • Management needed data issues resolved to improve plant profitability and drive strategic decisions
  • Technology group was working with a system built before their time – i.e. didn’t fully understand the BI system from source to reporting