Oracle SQL:Converting Row to Columns

Rows to columns..This is second time i came across this requirement of converting rows as columns.


I remember this question in one of the exercises in oracle OCP certification program.The solution was to use Decode and max functionality in combination

Posting it here for easy reference and for people new to oracle.

In the below query our requirement was to get the current quarter and next quarter from a custom calendar in one row..use a similar approach whenever u have similar requirement


SELECT MAX (DECODE (ROWNUM, 1, display_forecast, NULL))
Period1_status_frcst,
MAX (DECODE (ROWNUM, 2, display_forecast, NULL))
Period2_status_frcst,
MAX (DECODE (ROWNUM, 3, display_forecast, NULL))
Period3_status_frcst,
MAX (DECODE (ROWNUM, 1, period_name, NULL)) Period1_disp,
MAX (DECODE (ROWNUM, 1, start_date, NULL)) Period1_start_dt,
MAX (DECODE (ROWNUM, 1, quarter_num, NULL)) Current_quarter,
MAX (DECODE (ROWNUM, 1, end_date, NULL)) Period1_end_dt,
MAX (DECODE (ROWNUM, 2, period_name, NULL)) Period2_disp,
MAX (DECODE (ROWNUM, 2, start_date, NULL)) Period2_start_dt,
MAX (DECODE (ROWNUM, 2, end_date, NULL)) Period2_end_dt,
MAX (DECODE (ROWNUM, 3, period_name, NULL)) Period3_disp,
MAX (DECODE (ROWNUM, 3, start_date, NULL)) Period3_start_dt,
MAX (DECODE (ROWNUM, 3, end_date, NULL)) Period3_end_dt,
MAX (DECODE (ROWNUM, 1, period_name, NULL)) Period1_disp_actual,
MAX (DECODE (ROWNUM, 2, period_name, NULL)) Period2_disp_actual,
MAX (DECODE (ROWNUM, 3, period_name, NULL)) Period3_disp_actual,
MAX (DECODE (ROWNUM, 4, period_name, NULL)) Period4_disp,
MAX (DECODE (ROWNUM, 4, start_date, NULL)) Period4_start_dt,
MAX (DECODE (ROWNUM, 4, end_date, NULL)) Period4_end_dt,
MAX (DECODE (ROWNUM, 4, quarter_num, NULL)) next_quarter,
MAX (DECODE (ROWNUM, 5, period_name, NULL)) Period5_disp,
MAX (DECODE (ROWNUM, 5, start_date, NULL)) Period5_start_dt,
MAX (DECODE (ROWNUM, 5, end_date, NULL)) Period5_end_dt,
MAX (DECODE (ROWNUM, 6, period_name, NULL)) Period6_disp,
MAX (DECODE (ROWNUM, 6, start_date, NULL)) Period6_start_dt,
MAX (DECODE (ROWNUM, 6, end_date, NULL)) Period6_end_dt
FROM ( SELECT CASE
WHEN sysdate > end_date
THEN
'P'
WHEN sysdate BETWEEN start_Date
AND end_date
THEN
'C'
ELSE
'F'
END
display_forecast,
period_name,
start_date,
end_date,
quarter_num
FROM gl_periods
WHERE period_set_name = 'Fiscal Year'
AND ADJUSTMENT_PERIOD_FLAG = 'N'
AND (quarter_num, period_year) IN
(SELECT quarter_num, period_year
FROM gl_periods
WHERE sysdate BETWEEN start_date
AND end_Date
AND period_set_name = 'Fiscal Year'
AND ADJUSTMENT_PERIOD_FLAG = 'N')
OR (quarter_num, period_year) IN
(SELECT DECODE (quarter_num, 4, 1, quarter_num + 1),
DECODE (quarter_num,
4, period_year + 1,
period_year)
FROM gl_periods
WHERE sysdate BETWEEN start_date
AND end_Date
AND period_set_name = 'Fiscal Year'
AND ADJUSTMENT_PERIOD_FLAG = 'N')
)
ORDER BY start_date ASC

OAF:Set Text Color/Highlight record

Lets discuss on some of the common requirements in custom OAF pages

When ever there is a business validation failure display the content(text) in red color/other or mark the text.
This can be achieved by defining a custom css style.

CSSStyle cellBGColor = new CSSStyle();
cellBGColor.setProperty("color","#FF0000");
#FF0000--red color
OAMessageStyledTextBean field12=(OAMessageStyledTextBean)webBean.findChildRecursive("field12");

if(field12 != null )
field12.setInlineStyle(cellBGColor);

-----------------------------------------------------------------

One more requirement was to set color for the records dynamically.
Once user select and submit a record in a advanced table
the region is refreshed and the selected row should be unchecked and record should be highlighted.

That mean when the page refreshes only certain record should be highlighted.



TO achieve this we create a transient attribute in the VO
based on our condition we set the value for it.



--Code in AM
--rowlineVo --row handler
--we try to use existing styles instead of defining new one
rowlineVo.setAttribute("color","OraBGGrayMedium");


--Process request CO
OAMessageStyledTextBean field1 = (OAMessageStyledTextBean)webBean.findChildRecursive("Field1");
OADataBoundValueViewObject css2 = new OADataBoundValueViewObject(field1, "color");
field1.setAttributeValue(UIConstants.STYLE_CLASS_ATTR,css2);

This worked with out any issues..

If you want different colors based on the data we need to use OADataBoundValueViewObject.
OADataBoundValueViewObject css2 = new OADataBoundValueViewObject(field1, "color");

The first parameter the field which we want to apply the color and the second parameter is the vo attribute which need to be used.

Oracle OAF:Date Field -set min and max date

Hi All,


Its long time i have written anything interesting..too much occupied in job and not able find time to write anything...

Now a days i am working some interesting projects.. projects just to enhance user experience in using oracle projects.

Instead of multiple clicks in OOB we are developing a single screen to perform the same transaction...
and one screen to view all the data he need.
 we are working developing Custom OAF page and integrating with Oracle OOB.

OOB-- Out of box(oracle standard pages)

One of the requirements was to control the calendar in the date field..our requirement was to stop user from selecting any date in the past.

There are multiple approaches for doing this either in complete java or JAVA and PLSQl
here i am presenting one of the approaches of getting date from DB
--Get page connection

Connection conn = pageContext.getApplicationModule(webBean).getOADBTransaction().getJdbcConnection();

--sql query

String selectSQL = "select NVL(TO_DATE (fnd_profile.VALUE ('XX_GLOBAL_DATE'),'DD-MON-RRRR'),SYSDATE) date1 FROM DUAL";

System.out.println("SQL: "+selectSQL);

--parse the sql

PreparedStatement stmt = conn.prepareStatement(selectSQL);

--execute query

ResultSet rs = stmt.executeQuery(selectSQL );

System.out.println("RS: "+rs);

while (rs.next())
{
java.sql.Date date = rs.getDate("date1");
--get the date field handler
OAMessageDateFieldBean dateField = (OAMessageDateFieldBean)webBean.findIndexedChildRecursive(stat_date);
--set the minimun value
dateField.setMinValue(date);
}
similary we can set the max date also ...But make sure the date is in sql date type.

But one catch is  if user dont use the calendar and type date in the field the system will allow a past date.To over come this issue
you need to have a validation logic defined either in the set attribute of the VO or a error raised from DB if you are not comfortable in java :)