Query to convert Column data into a Row

The following query is very useful for converting the data in a single column into a row so the all the values can be extracted from a table into variables of different name. Using cursor it is difficult to get all the values from single column into variables of different name. 

Example of sample data available - 

Required format into which data needs to be converted -

Query which can be used for above purpose - 


SELECT  MAX(DECODE(level,1,regexp_substr(str,'[^,]+',1,level))) AS val1 ,
 MAX(DECODE(level,2,regexp_substr(str,'[^,]+',1,level))) AS val2 ,
 MAX(DECODE(level,3,regexp_substr(str,'[^,]+',1,level))) AS val3 ,
 MAX(DECODE(Level,4,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val4 ,
 MAX(DECODE(level,5,regexp_substr(str,'[^,]+',1,level))) AS val5 ,
 MAX(DECODE(level,6,regexp_substr(str,'[^,]+',1,level))) AS val6 ,
 MAX(DECODE(level,7,regexp_substr(str,'[^,]+',1,level))) AS val7 ,
 MAX(DECODE(level,8,regexp_substr(str,'[^,]+',1,level))) AS val8 ,
 MAX(DECODE(Level,9,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val9 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val10 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val11 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val12 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val13 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val14 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val15
   INTO l_value_id1 ,
 l_value_id2 ,
 l_value_id3 ,
 l_value_id4 ,
 l_value_id5 ,
 l_value_id6 ,
 l_value_id7 ,
 l_value_id8 ,
 l_value_id9 ,
 l_value_id10 ,
 l_value_id11 ,
 l_value_id12 ,
 l_value_id13 ,
 l_Value_Id14 ,
 l_value_id15
   FROM (
         SELECT Listagg (column_to_convert,',') Within Group (Order By column_to_convert) Str
    FROM Table_Name
   WHERE 1 = 1
   GROUP BY group_by_column
   ) Tmp

   CONNECT BY regexp_substr(str,'[^,]+',1,level) IS NOT NULL;

OAF - GL Diistribution DFF field on OAF page

This post will details the steps required for enabling the functionality of GL DFF as below - 

First step, create a field on OAF page with item type as 'Flex' in a table or region. Attributes of this Flex item needs to be set as below - 

Segment List property value need to setup as below - 

** Here ACCOUNTING_FLEXFIELD is the name of the accounting flexfield defined for GL and can be obtained using below query - 

select id_flex_structure_code, id_flex_code from fnd_id_flex_structures where id_flex_code = 'GL#';

Above field is created in a Advanced table. If you are using a normal OAF table then you will need to populate the VO Instance name of the flex item as well.

Second step, which needs to be performed is adding following code in the processRequest method of the Controller java file - 

OAApplicationModule am = pageContext.getApplicationModule(webBean);

OAKeyFlexBean kffId = (OAKeyFlexBean)webBean.findIndexedChildRecursive("AccountCol");

// Set the code combination lov

kffId.useCodeCombinationLOV(true);

// Set the structure code for the item key flex

kffId.setStructureCode("ACCOUNTING_FLEXFIELD");

// Set the attribute name to the item

kffId.setCCIDAttributeName("DistCodeCombinationId");

Performing the above mentioned steps will help meet the requirement of creating and displaying the GL DFF field on an OAF page.

Project Accounting - Check funds queries at Task, Top Task and Project Level

FOR TASK

  SELECT project_id,
    (SUM(budget) - SUM(actuals)) "funds"
  FROM PA_TASK_FC_RESULTS_V
  WHERE project_id     =<p_project_id>
  AND start_date       > '31-MAR-15'
  AND end_date         < '1-APR-16'
  AND budget_version_id=
    (SELECT MAX(budget_version_id)
    FROM PA_TASK_FC_RESULTS_V
    WHERE project_id=<p_project_id>
    )
  and TASK_ID=<P_TASK_ID>
  GROUP BY project_id;
  
FOR TOP TASK 

  SELECT project_id,
    (SUM(budget) - SUM(actuals)) "funds"
  FROM PA_TOP_TASK_FC_RESULTS_V
  WHERE project_id     =<p_project_id>
  AND start_date       > '31-MAR-15'
  AND end_date         < '1-APR-16'
  AND budget_version_id=
    (SELECT MAX(budget_version_id)
    FROM PA_TOP_TASK_FC_RESULTS_V
    WHERE project_id=<p_project_id>
    )
  and TOP_TASK_ID=<P_TOP_TASK_ID>
  GROUP BY project_id;
  
FOR PROJECT 

  SELECT project_id,
    (SUM(budget) - SUM(actuals)) "funds"
  FROM PA_PRJ_FC_RESULTS_V
  WHERE project_id     =<p_project_id>
  AND start_date       > '31-MAR-15'
  AND end_date         < '1-APR-16'
  AND budget_version_id=
    (SELECT MAX(budget_version_id)
    FROM PA_PRJ_FC_RESULTS_V
    WHERE project_id=<p_project_id>
    )
  GROUP BY PROJECT_ID;

Blocking Outlook Calendar through PLSQL Code

-- Following code can be used for blocking the calendar of the recipients through email 
-- sent using PLSQL Code.
-- Create the following function

CREATE OR REPLACE FUNCTION ical_event (
   p_summary         IN VARCHAR2
 , p_organizer_name  IN VARCHAR2
 , p_organizer_email IN VARCHAR2
 , p_start_date      IN DATE
 , p_end_date        IN DATE
 , p_version         IN VARCHAR2 := NULL
 , p_prodid          IN VARCHAR2 := NULL
 , p_calscale        IN VARCHAR2 := NULL
 , p_method          IN VARCHAR2 := NULL
)
   RETURN VARCHAR2 

AS  

   l_retval VARCHAR2(32767);
   l_lf     CHAR(1) := CHR(10);

BEGIN

   l_retval := ''
      || 'BEGIN:VCALENDAR' || l_lf
      || 'VERSION:' || NVL(p_version,'2.0') || l_lf
      || 'PRODID:' || NVL(p_prodid,'-//Your company name//NONSGML ICAL_EVENT//EN') || l_lf
      || 'CALSCALE:' || NVL(p_calscale,'GREGORIAN') || l_lf
      || 'METHOD:' || NVL(p_method,'REQUEST') || l_lf
      || 'BEGIN:VEVENT' || l_lf
      || 'SUMMARY:' || p_summary || l_lf
      || 'ORGANIZER;CN="' || p_organizer_name || '":MAILTO:' || p_organizer_email || l_lf
      || 'DTSTART:' || TO_CHAR(p_start_date,'YYYYMMDD') || 'T' || TO_CHAR(p_start_date,'HH24MISS') || l_lf
      || 'DTEND:' || TO_CHAR(p_end_date,'YYYYMMDD') || 'T' || TO_CHAR(p_end_date,'HH24MISS') || l_lf
      || 'DTSTAMP:' || TO_CHAR(SYSDATE,'YYYYMMDD') || 'T' || TO_CHAR(SYSDATE,'HH24MISS') || l_lf
      || 'UID:' || RAWTOHEX(SYS_GUID()) || '@yoururl.com' || l_lf
      || 'STATUS:NEEDS-ACTION' ||  l_lf
      || 'END:VEVENT' || l_lf
      || 'END:VCALENDAR';
   
   RETURN l_retval;
      
END ical_event;


--Create the following procedure (replace "yoururl" as needed): 

CREATE OR REPLACE PROCEDURE send_ical_email (
   p_from      IN VARCHAR2
 , p_to        IN VARCHAR2
 , p_subj      IN VARCHAR2
 , p_body_html IN VARCHAR2
 , p_body_ical IN VARCHAR2
)

AS

   l_connection UTL_SMTP.CONNECTION;
   l_mail_serv  VARCHAR2(50) := 'mail.yoururl.com';
   l_mail_port  PLS_INTEGER := '25';
   l_lf         CHAR(1) := CHR(10);
   l_msg_body   VARCHAR2(32767);

BEGIN
   
   l_msg_body :=
         'Content-class: urn:content-classes:calendarmessage' || l_lf
      || 'MIME-Version: 1.0' || l_lf
      || 'Content-Type: multipart/alternative;' || l_lf
      || ' boundary="----_=_NextPart"' || l_lf
      || 'Subject: ' || p_subj || l_lf 
      || 'Date: ' || TO_CHAR(SYSDATE,'DAY, DD-MON-RR HH24:MI') || l_lf
      || 'From: <' || p_from || '> ' || l_lf 
      || 'To: ' || p_to || l_lf 
      || '------_=_NextPart' || l_lf
      || 'Content-Type: text/plain;' || l_lf
      || ' charset="iso-8859-1"' || l_lf
      || 'Content-Transfer-Encoding: quoted-printable' || l_lf
      || l_lf
      || 'You must have an HTML enabled client to view this message.' || l_lf
      || l_lf
      || '------_=_NextPart' || l_lf
      || 'Content-Type: text/html;' || l_lf
      || ' charset="iso-8859-1"' || l_lf
      || 'Content-Transfer-Encoding: quoted-printable' || l_lf
      || l_lf
      || p_body_html || l_lf
      || l_lf
      || '------_=_NextPart' || l_lf
      || 'Content-class: urn:content-classes:calendarmessage' || l_lf
      || 'Content-Type: text/calendar;' || l_lf
      || '  method=REQUEST;' || l_lf
      || '  name="meeting.ics"' || l_lf
      || 'Content-Transfer-Encoding: 8bit' || l_lf
      || l_lf
      || p_body_ical || l_lf
      || l_lf
      || '------_=_NextPart--';
            
   l_connection := utl_smtp.open_connection(l_mail_serv, l_mail_port);
   utl_smtp.helo(l_connection, l_mail_serv);
   utl_smtp.mail(l_connection, p_from);
   utl_smtp.rcpt(l_connection, p_to);
   utl_smtp.data(l_connection, l_msg_body);
   utl_smtp.quit(l_connection);
   
END send_ical_email;

-- Create a page process similar to the following that fires 
-- when the submit button is pressed (this will vary depending on step 3): 

DECLARE

   l_ical_event VARCHAR2(32767);

BEGIN

   l_ical_event := ical_event(
      p_start_date      => TO_DATE(:PXX_START_DATE || :PXX_START_TIME,'DD-MON-YYYYHH:MIPM')
    , p_end_date        => TO_DATE(:PXX_END_DATE || :PXX_END_TIME,'DD-MON-YYYYHH:MIPM')
    , p_summary         => :PXX_SUBJ
    , p_organizer_name  => :PXX_USER_NAME
    , p_organizer_email => :PXX_USER_EMAIL
   );

   send_ical_email( 
      p_to        => :PXX_TO_ADDRESS
    , p_from      => :PXX_USER_EMAIL
    , p_subj      => :PXX_SUBJ
    , p_body_html => :PXX_BODY_HTML 
    , p_body_ical => l_ical_event
   );
   
END;

That should do it. Submit the page to send the request. 

OAF - Redirecting to a sub tab

Redirecting to a sub tab in OAF page by passing the SubTab id in hashmap:

HashMap hashmap = new HashMap(2); hashmap.put("porMode", "display"); 

hashmap.put("OA_SubTabIdx", 1); 

pageContext.forwardImmediately("ICX_POR_CHECKOUT_LINES", (byte)0, null, hashmap, true, null); 

OAF - How to Display multiple errors?

Sometimes you have to show all the error messages at one shot,instead of showing the error messages one by one. In that case, we have to use bundled exception to show multiple error message. Below are the steps:

1. Add the import statements:

import com.sun.java.util.collections.ArrayList;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.common.MessageToken;

2. Declare an array list.

ArrayList errorMsg= new ArrayList();
3. Raise OAException.
for (int i=0; i< hMember.size(); i++)
{
MessageToken[] token = {
new MessageToken("USER", getOADBTransaction().getUserName()),
new MessageToken("NUM",hMember.getChildNumber())
};
errorMsg.add(new OAException((String)(getOADBTransaction().getMessage("XXCH","XXCH_NO_ACCESS",token))));
}
OAException.raiseBundledOAException(errorMsg);

ADF Code Example - Set 1

Question: Committing through java code in your managed bean.
Answer: 
DCBindingContainer bindings = (DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();
 OperationBinding operationBinding = bindings.getOperationBinding(“Commit”);
 operationBinding.execute();

Question: Rollback transaction through java code in your managed bean.
Answer: 
DCBindingContainer bindings = (DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();
 OperationBinding operationBinding = bindings.getOperationBinding(“Rollback”);
 operationBinding.execute();

Pay attention to the operation binding names, if you have CRUD operations from different application modules within the same page, those names will differ (generally: Commit1, Rollback1 etc)

Question: Calling a VO from another VO or AM.
Answer: 
//in you Application module Impl or VOImpl file have the follwing code written inside the //method which will set the value for another vo. Lets say UploadView is the View object that //you would want to set the value

ViewObject vo = findViewObject("tUploadView");

//if its not in Application Module, you will have to get the VO instance either from iterator if //you are using this code in the bean get the rowset of UploadView

RowSet rs = vo.getRowSet();
while(rs.hasNext()){    //iterate through
Row r = rs.next();       //get the row
r.setAttribute("ValueItem", value);  //set the value..
//ValueItem is the Attribute that you want to set
}
}

Question: Write  code to access the current row and/or the view object inside your bean?
Answer: Code to access the current row and or the view object inside your bean:

BindingContainer bc = BindingContext.getCurrent().getCurrentBindingsEntry();
DCBindingContainer dcbc = (DCBindingContainer) bc;
DCIteratorBinding lBinding = dcbc.findIteratorBinding("EmployeesViewIterator");
EmployeesViewRow row = (EmployeesViewRow) lBinding.getCurrentRow();
EmployeesView view = (EmployeesView) lBinding.getViewObject();

Question:How to declare the page navigation (navigation rules) in faces-config.xml file in ADF 10g?
Answer: Navigation rules tells JSF implementation which page to send back to the browser after a form has been submitted. We can declare the page navigation as follows:

<naviagation-rule>
<from-view-id>/index.jsp</from-view-id>
<navigation-case>
<from-outcome>login</from-outcome>
<to-view-id>/welcome.jsp</to-view-id>
</navigation-case>
</naviagation-rule>

This declaration states that the login action navigates to /welcome.jsp, if it occurred inside /index.jsp.

Question: Why 'timeZone' attribute is required when <af:convertDateTime> is used?
Answer: When <af:convertDateTime> is used it takes by default GMT time, for Indian timing we need to take GMT+5.30

<af:inputText id="compId3882"
label="#{messageBean['SS_DATE_OF_BIRTH']}"
disabled="true" maximumLength="50"
value="#{bindings.DateofBirth.inputValue}"
inlineStyle="font-size:smaller; font-weight:normal; font-family:Arial;color:rgb(69,69,69);">
   <af:convertDateTime timeZone="GMT+5:30" pattern="dd/MM/yyyy"/>

 </af:inputText>

Question: How to set the range of table?
Answer: <af:table rows=”#{bindings.LoggedInUserServiceRequests.rangeSize}”…/>

Question: How to Use the pageFlowScope Scope Within Java Code?
Answer: You can access pageFlow scope from within any Java code in your application. Remember to clear the scope once you are finished. To use pageFlowScope in Java code:
1. To get a reference to the pageFlowScope scope, use following method:
org.apache.myfaces.trinidad.context.RequestContext.getPageFlowScope();
2. For example, to retrieve an object from the pageFlowScope scope, you might use the following Java code:
import java.util.Map;
import org.apache.myfaces.trinidad.context.RequestContext;
3. Inside Java Method, write code below :
Map pageFlowScope = RequestContext.getCurrentInstance().getPageFlowScope();
Object myObject = pageFlowScope.get("myObjectName");
4. To clear the pageFlowScope scope, access it and then manually clear it. For example, you might use the following Java code to clear the scope:
RequestContext afContext = RequestContext.getCurrentInstance();
afContext.getPageFlowScope().clear();


Note: If your application uses ADF Controller, then you do not have to manually clear the scope.

SQL Interview Questions

Question: How will you delete duplicating rows from a base table?

Answer: 

DELETE FROM table_name A WHERE rowid > (SELECT MIN(rowid) FROM table_name B WHERE A.key_values = B.key_values);

DELETE FROM emp e WHERE ROWID NOT IN ( SELECT MIN(ROWID) FROM emp a WHERE e.empno = a.empno);

DELETE FROM EMP WHERE ROWID NOT IN (SELCT MAX(ROWID) FROM EMP GROUP BY EMPNO);

Question: Find out nth highest salary from emp table?

Answer: 
SELECT DISTINCT (A.SAL) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (B.SAL)) FROM EMP B WHERE A.SAL<=B.SAL);

SELECT MIN(SAL) FROM (SELECT DISTINCT SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM <=&N;

SELECT * FROM (SELECT RANK() OVER (PARTITION BY SAL ORDER BY SAL DESC NULLS LAST) RN FROM TABLENAME) WHERE RN = &N;

SELECT ENAME, SAL, DEPTNO, JOB FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP WHERE LEVEL = &LEVELNO CONNECT BY PRIOR SAL>SAL GROUP BY LEVEL);

SELECT ROWNUM,SAL FROM (SELECT ROWNUM,SAL FROM EMP ORDER BY SAL DESC )GROUP BY ROWNUM,SAL HAVING ROWNUM=&N;

SELECT * FROM(SELECT EMPNO,ENAME,DEPTNO,SAL,RANK() OVER(ORDER BY SAL) TOPSAL FROM EMP) WHERE TOPSAL=&NTH;

SELECT DISTINCT A.SAL FROM EMP A, (SELECT ROWNUM AS CNT, A.* FROM (SELECT DISTINCT SAL FROM EMP ORDER BY SAL DESC) A) B WHERE A.SAL = B.SAL AND B.CNT = :A;

SELECT LEVEL,MAX(SAL) FROM EMP WHERE LEVEL=&LEVELNO CONNECT BY PRIOR SAL>SAL GROUP BY LEVEL;

Question: Which datatype is used for storing graphics and images?

Answer:  BLOB or BFILE. Long raw is obsolete now.

Question: Which is more faster - IN or EXISTS?

Answer: EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

In many cases, EXISTS is better because it requires you to specify a join condition, which can invoke an index scan. EXISTS is faster when sub-query result is large. IN is often better if the result of sub-query are very small. But using EXISTS is better choice when sub-query result is unpredictable.