Learn Oracle Fusion, OAF and ADF by Raju Bandam: January 2016

Thursday, 21 January 2016

Updating DFF in PFR:

package xxigfmis.oracle.apps.irc.vacancy.webui;

import java.io.Serializable;

import java.sql.CallableStatement;

import java.sql.SQLException;
import java.sql.Types;

import oracle.jdbc.OracleCallableStatement;
import java.util.Enumeration;
import java.util.Hashtable;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.server.OADBTransaction;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.OAWebBeanConstants;
import oracle.apps.fnd.framework.webui.beans.OADescriptiveFlexBean;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.fnd.framework.webui.beans.message.OAMessageChoiceBean;
import oracle.apps.fnd.framework.webui.beans.message.OAMessageTextInputBean;
import oracle.apps.irc.vacancy.server.IrcEditVacancyVOImpl;
import oracle.apps.irc.vacancy.server.VacancyAMImpl;
import oracle.jbo.Row;
import oracle.apps.irc.vacancy.webui.VacNewDetsPageCO;

// Referenced classes of package oracle.apps.irc.vacancy.webui:
// VacancyCO

public class xxVacNewDetsPageCO extends VacNewDetsPageCO
{

public xxVacNewDetsPageCO()
{
}

public void processFormRequest(OAPageContext oapagecontext, OAWebBean oawebbean)
{

super.processFormRequest(oapagecontext, oawebbean);

VacancyAMImpl vacancyamimpl = (VacancyAMImpl)oapagecontext.getRootApplicationModule();
IrcEditVacancyVOImpl cvo = vacancyamimpl.getIrcEditVacancyVO();
String outParamValue1 = null;
String outParamValue2 = null;
String outParamValue3 = null;
String outParamValue4 = null;
String outParamValue5 = null;
String outParamValue6 = null;
String outParamValue7 = null;
String outParamValue8 = null;
String outParamValue9 = null;
String outParamValue10 = null;
String outParamValue11= null;

Row row=cvo.first();
if(row !=null){

}else{

row=cvo.getCurrentRow();

}
//row.setAttribute("Attribute1", "1");
//row.setAttribute("Attribute2", "2");
//row.setAttribute("Attribute3", "3");
//row.setAttribute("Attribute4", "4");
oapagecontext.writeDiagnostics("xxVacNewDetsPageCO",row.getAttribute("BudgetMeasurementValue")+"Raju",2);

oapagecontext.writeDiagnostics("xxVacNewDetsPageCO",oapagecontext.getLovInputSourceId()+"Raju",2);
if("FndLocation".equals(oapagecontext.getLovInputSourceId())){

OADBTransaction txn = vacancyamimpl.getOADBTransaction();

CallableStatement callableStatement =

txn.createCallableStatement("begin xxigfmis_irec_pkg.xxigfmis_vac_reservation(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12); end;",OADBTransaction.DEFAULT);

try {

callableStatement.registerOutParameter(2, Types.VARCHAR);
callableStatement.registerOutParameter(3, Types.VARCHAR);
callableStatement.registerOutParameter(4, Types.VARCHAR);
callableStatement.registerOutParameter(5, Types.VARCHAR);
callableStatement.registerOutParameter(6, Types.VARCHAR);
callableStatement.registerOutParameter(7, Types.VARCHAR);
callableStatement.registerOutParameter(8, Types.VARCHAR);
callableStatement.registerOutParameter(9, Types.VARCHAR);
callableStatement.registerOutParameter(10, Types.VARCHAR);
callableStatement.registerOutParameter(11, Types.VARCHAR);
callableStatement.registerOutParameter(12, Types.VARCHAR);

callableStatement.setString(1, row.getAttribute("BudgetMeasurementValue").toString());

callableStatement.execute();

outParamValue1 = callableStatement.getString(2);
outParamValue2 = callableStatement.getString(3);
outParamValue3 = callableStatement.getString(4);
outParamValue4 = callableStatement.getString(5);
outParamValue5 = callableStatement.getString(6);
outParamValue6 = callableStatement.getString(7);
outParamValue7 = callableStatement.getString(8);
outParamValue8 = callableStatement.getString(9);
outParamValue9 = callableStatement.getString(10);
outParamValue10 = callableStatement.getString(11);
outParamValue11 = callableStatement.getString(12);

oapagecontext.writeDiagnostics("xxVacNewDetsPageCO",outParamValue1+"raju",2);
oapagecontext.writeDiagnostics("xxVacNewDetsPageCO",outParamValue2+"raju",2);
oapagecontext.writeDiagnostics("xxVacNewDetsPageCO",outParamValue3+"raju",2);
oapagecontext.writeDiagnostics("xxVacNewDetsPageCO",outParamValue4+"raju",2);
oapagecontext.writeDiagnostics("xxVacNewDetsPageCO",outParamValue5+"raju",2);
oapagecontext.writeDiagnostics("xxVacNewDetsPageCO",outParamValue6+"raju",2);
oapagecontext.writeDiagnostics("xxVacNewDetsPageCO",outParamValue7+"raju",2);
oapagecontext.writeDiagnostics("xxVacNewDetsPageCO",outParamValue8+"raju",2);
oapagecontext.writeDiagnostics("xxVacNewDetsPageCO",outParamValue9+"raju",2);
oapagecontext.writeDiagnostics("xxVacNewDetsPageCO",outParamValue10+"raju",2);
oapagecontext.writeDiagnostics("xxVacNewDetsPageCO",outParamValue11+"raju",2);
callableStatement.close();

} catch (SQLException sqle) {
oapagecontext.writeDiagnostics("xxVacNewDetsPageCO",sqle.getMessage()+"raju",2);
//callableStatement.close();

}
OADescriptiveFlexBean oaDFF = (OADescriptiveFlexBean)oawebbean.findChildRecursive("FndFlexField"); // put your dff web bean name here

OAMessageTextInputBean msg0=(OAMessageTextInputBean)oaDFF.findChildRecursive("FndFlexField0");
msg0.setText(outParamValue1);
OAMessageTextInputBean msg1=(OAMessageTextInputBean)oaDFF.findChildRecursive("FndFlexField1");
msg1.setText(outParamValue2);
OAMessageTextInputBean msg2=(OAMessageTextInputBean)oaDFF.findChildRecursive("FndFlexField2");
msg2.setText(outParamValue3);
OAMessageTextInputBean msg3=(OAMessageTextInputBean)oaDFF.findChildRecursive("FndFlexField3");
msg3.setText(outParamValue4);
OAMessageTextInputBean msg4=(OAMessageTextInputBean)oaDFF.findChildRecursive("FndFlexField4");
msg4.setText(outParamValue5);
OAMessageTextInputBean msg5=(OAMessageTextInputBean)oaDFF.findChildRecursive("FndFlexField5");
msg5.setText(outParamValue6);
OAMessageTextInputBean msg6=(OAMessageTextInputBean)oaDFF.findChildRecursive("FndFlexField6");
msg6.setText(outParamValue7);
OAMessageTextInputBean msg7=(OAMessageTextInputBean)oaDFF.findChildRecursive("FndFlexField7");
msg7.setText(outParamValue8);
OAMessageTextInputBean msg8=(OAMessageTextInputBean)oaDFF.findChildRecursive("FndFlexField8");
msg8.setText(outParamValue9);
OAMessageTextInputBean msg9=(OAMessageTextInputBean)oaDFF.findChildRecursive("FndFlexField9");
msg9.setText(outParamValue10);
OAMessageTextInputBean msg10=(OAMessageTextInputBean)oaDFF.findChildRecursive("FndFlexField10");
msg10.setText(outParamValue11);
}//end lov event

}

}

OADescriptiveFlexBean oaDFF =(OADescriptiveFlexBean)dffCol.findIndexedChildRecursive("SiteUseDFF");

{

oaDFF.processFlex(pagecontext);

OAWebBean dffbeans0 = (OAWebBean)oaDFF.findChildRecursive("SiteUseDFF0");

if (dffbeans0 != null)
{

dffbeans0.setAttributeValue(OAWebBeanConstants.READ_ONLY_SET, true);
dffbeans0.setAttributeValue(OAWebBeanConstants.READ_ONLY_ATTR, true);
}

OAWebBean dffbeans1 = (OAWebBean)oaDFF.findChildRecursive("SiteUseDFF1");

if (dffbeans1 != null)
{

dffbeans1.setAttributeValue(OAWebBeanConstants.READ_ONLY_SET, true);
dffbeans1.setAttributeValue(OAWebBeanConstants.READ_ONLY_ATTR, true);
}
}

https://community.oracle.com/thread/2263077?tstart=0

package xxclt.oracle.apps.irc.vacancy.webui;

import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.OAWebBeanConstants;
import oracle.apps.fnd.framework.webui.OAWebBeanUtils;
import oracle.apps.fnd.framework.webui.beans.OADescriptiveFlexBean;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.fnd.framework.webui.beans.message.OAMessageCheckBoxBean;
import oracle.apps.fnd.framework.webui.beans.message.OAMessageChoiceBean;
import oracle.apps.fnd.framework.webui.beans.message.OAMessageLovChoiceBean;
import oracle.apps.fnd.framework.webui.beans.message.OAMessageLovInputBean;
import oracle.apps.irc.vacancy.webui.VacNewDetsPageCO;
import oracle.cabo.ui.action.FireAction;
import oracle.jbo.ViewObject;

public class XXVacNewDetsPageCO extends VacNewDetsPageCO {
public XXVacNewDetsPageCO() {
}

public void processRequest(OAPageContext pageContext, OAWebBean webBean) {
super.processRequest(pageContext, webBean);
OADescriptiveFlexBean dffBean =
(OADescriptiveFlexBean)webBean.findIndexedChildRecursive("FndFlexField");
dffBean.processFlex(pageContext);
OAMessageChoiceBean segment1 =
(OAMessageChoiceBean)dffBean.findChildRecursive("FndFlexField0");
segment1.setRequired("yes");
segment1.setFireActionForSubmit ("selectBCN",null, null,true, true);

}

public void processFormRequest(OAPageContext pageContext,
OAWebBean webBean) {
super.processFormRequest(pageContext, webBean);

if ("selectBCN".equals(pageContext.getParameter(OAWebBeanConstants.EVENT_PARAM))) {
// The Position poplist PPR change event has fired.
OAApplicationModule am = pageContext.getRootApplicationModule();
String vStatus;
OADescriptiveFlexBean dffBean =
(OADescriptiveFlexBean)webBean.findIndexedChildRecursive("FndFlexField");
dffBean.processFlex(pageContext);
OAMessageChoiceBean bcnSegment =
(OAMessageChoiceBean)dffBean.findChildRecursive("FndFlexField0");
String vBCNValue = bcnSegment.getSelectionValue(pageContext) ;
pageContext.writeDiagnostics("XXCLIENT",vBCNValue,1);
String vacancyInfoQuery =
"SELECT bcn, grade, job_title, vacancy_type, ORGANIZATION FROM xxclt_mbd_bcn_info_v";

//Specify the Where Clause for the same
vacancyInfoQuery = vacancyInfoQuery + " WHERE bcn = :1 ";

//First see if this VO is already attached to view object
ViewObject vacancyInfoVO = am.findViewObject("XXVvacancyInfoVO");
if (vacancyInfoVO == null)
vacancyInfoVO =
am.createViewObjectFromQueryStmt("XXVvacancyInfoVO",
vacancyInfoQuery);
//By now we are sure that the view object exists
vacancyInfoVO.setWhereClauseParams(null);
//Set the where clause
vacancyInfoVO.setWhereClauseParam(0, vBCNValue);
vacancyInfoVO.executeQuery();
oracle.jbo.Row row = vacancyInfoVO.first();
//get the value of description column from View Object record returned
String vGrade = "";
String vJobTitle = "";
String vVacancyType = "";
String vOrganization = "";

if (row != null) {
vGrade = row.getAttribute(1).toString();
vJobTitle = row.getAttribute(2).toString();
vVacancyType = row.getAttribute(3).toString();
vOrganization = row.getAttribute(4).toString();
}

OAMessageLovInputBean vGradeBean =
(OAMessageLovInputBean)webBean.findChildRecursive("FndGrade");
OAMessageLovInputBean vJobTitleBean =
(OAMessageLovInputBean)webBean.findChildRecursive("FndJobTitle");
OAMessageChoiceBean vVacancyTypeBean =
(OAMessageChoiceBean)dffBean.findChildRecursive("FndFlexField1");
OAMessageLovInputBean vOrgBean =
(OAMessageLovInputBean)webBean.findChildRecursive("FndOrganization");

vVacancyTypeBean.setSelectedValue(vVacancyType);
vGradeBean.setText( vGrade);
vJobTitleBean.setText(vJobTitle);
vOrgBean.setText(vOrganization);
// vOrgBean.set
OAMessageCheckBoxBean vEmpChkBox;
OAMessageCheckBoxBean vCntrChkBox;
vEmpChkBox =
(OAMessageCheckBoxBean)webBean.findChildRecursive("FndEmployee");
vCntrChkBox =
(OAMessageCheckBoxBean)webBean.findChildRecursive("FndContractor");

if (vVacancyType.equals("PMR") ) {
vEmpChkBox.setChecked(true);
vCntrChkBox.setChecked(false);
}else if(vVacancyType.equals("TMR") ) {
vCntrChkBox.setChecked(true);
vEmpChkBox.setChecked(false);
}
//Remove the view object, as this is no longer required
vacancyInfoVO.remove();
}
}

}

Thursday, 14 January 2016

package xxx.oracle.apps.ota.lov.webui;

import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.ota.lov.webui.ResBookingNamesLovCO;
import oracle.apps.ota.lov.server.LovAMImpl;
import oracle.apps.ota.lov.server.ResBookingNamesVOImpl;

public class xxResBookingNamesLovCO extends ResBookingNamesLovCO {
public xxResBookingNamesLovCO() {
}

public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{

String ClassStartTime1 = null;
if(pageContext.getParameter("ClassStartTime1")!=null){
ClassStartTime1= pageContext.getParameter("ClassStartTime1").toString();
pageContext.writeDiagnostics("BANDAM"+ClassStartTime1,"xxResBookingNamesLovCO",2);
}

pageContext.writeDiagnostics("BANDAM is null "+ClassStartTime1,"xxResBookingNamesLovCO",2);
String ClassEndDate1 = null;
if(pageContext.getParameter("ClassEndDate1")!=null){
ClassEndDate1= pageContext.getParameter("ClassEndDate1").toString();
pageContext.writeDiagnostics("BANDAM"+ClassEndDate1,"xxResBookingNamesLovCO",2);
}

pageContext.writeDiagnostics("BANDAM is null "+ClassEndDate1,"xxResBookingNamesLovCO",2);
if(ClassEndDate1!=null && ClassStartTime1 !=null){

LovAMImpl localLovAMImpl = (LovAMImpl)pageContext.getApplicationModule(webBean);
ResBookingNamesVOImpl resvo=(ResBookingNamesVOImpl)localLovAMImpl.getResBookingNamesVO1();
if(resvo !=null){

String condSql =null;

try{
condSql="";
resvo.setFullSqlMode(resvo.FULLSQL_MODE_AUGMENTATION);

resvo.setQuery(condSql);
}
catch(Exception e){

}

}

}

}
}

package xxx.oracle.apps.ota.admin.resource.webui;

import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAViewObject;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.fnd.framework.webui.beans.layout.OAListOfValuesBean;
import oracle.apps.fnd.framework.webui.beans.message.OAMessageLovInputBean;
import oracle.apps.ota.admin.resource.webui.MaintainResourceBookingsCO;
import oracle.apps.ota.lov.server.ResBookingNamesVOImpl;
import oracle.apps.ota.lov.server.LovAMImpl;

public class xxMaintainResourceBookingsCO extends MaintainResourceBookingsCO {
public xxMaintainResourceBookingsCO() {
}

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{

String ClassStartTime1 =
pageContext.getParameter("ClassStartTime1");
pageContext.putSessionValue("ssClassStartTime1",ClassStartTime1);

String ClassEndDate1 =
pageContext.getParameter("ClassEndDate1");
pageContext.putSessionValue("ssClassEndDate1",ClassEndDate1);

super.processFormRequest(pageContext, webBean);

}

}

Wednesday, 13 January 2016

package xxota.oracle.apps.ota.admin.resource.webui;

import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAViewObject;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.ota.admin.resource.webui.MaintainResourceBookingsCO;
import oracle.apps.ota.lov.server.ResBookingNamesVOImpl;
import oracle.apps.ota.lov.server.LovAMImpl;

public class xxMaintainResourceBookingsCO extends MaintainResourceBookingsCO {
public xxMaintainResourceBookingsCO() {
}

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{

OAApplicationModule am = pageContext.getApplicationModule(webBean);

LovAMImpl lam=(LovAMImpl)am.findApplicationModule("LovAM");

ResBookingNamesVOImpl shipvo =(ResBookingNamesVOImpl)lam.getResourceNamesVO();
String ClassStartTime1 =
pageContext.getParameter("ClassStartTime1");
String ClassEndDate1 =
pageContext.getParameter("ClassEndDate1");
String condSql =null;
if ("ClassName".equals(pageContext.getLovInputSourceId())) {
try{
condSql="SELECT distinct SRL.name, TSR.supplied_resource_id, PER.full_name, TSR.vendor_id, ven.vendor_name, TSR.training_center_id, ota_general.get_org_name (TSR.training_center_id) Training_center_name, TSR.location_id, ota_general.get_location_code (TSR.location_id) Location, TSR.resource_type, TSR.start_date, TSR.end_date, TSR.cost, TSR.currency_code, TSR.cost_unit, TSR.consumable_flag, TSR.special_instruction, CASE WHEN (to_char("+ClassStartTime1+",'DD-MON-YYYY') between REQUIRED_DATE_FROM and REQUIRED_DATE_TO OR to_char("+ClassEndDate1+",'DD-MON-YYYY') between REQUIRED_DATE_FROM and REQUIRED_DATE_TO) AND TIMEZONE_CODE= 'Asia/Calcutta' THEN 'Not Allowed' ELSE 'Allowed' END Status FROM OTA_SUPPLIABLE_RESOURCES TSR, OTA_SUPPLIABLE_RESOURCES_TL SRL, per_all_people_f per, po_vendors ven, ota_resource_bookings orb WHERE SRL.SUPPLIED_RESOURCE_ID = TSR.SUPPLIED_RESOURCE_ID AND tsr.SUPPLIED_RESOURCE_ID = orb.SUPPLIED_RESOURCE_ID(+) AND SRL.LANGUAGE = USERENV ('LANG') AND TSR.BUSINESS_GROUP_ID = ota_general.get_business_group_id AND per.person_id(+) = tsr.trainer_id AND tsr.start_date BETWEEN per.effective_start_date(+) AND per.effective_end_date(+) AND ven.vendor_id(+) = TSR.vendor_id";
shipvo.setFullSqlMode(shipvo.FULLSQL_MODE_AUGMENTATION);

shipvo.setQuery(condSql);
}
catch(Exception e){

}
}

super.processFormRequest(pageContext, webBean);

}

}

Tuesday, 12 January 2016

String ClassStartTime1 =
pageContext.getParameter("ClassStartTime1");
String ClassEndDate1 =
pageContext.getParameter("ClassEndDate1");

if ("ClassName".equals(pageContext.getLovInputSourceId())) {
try{
condSql="";
shipvo.setFullSqlMode(shipvo.FULLSQL_MODE_AUGMENTATION);

shipvo.setQuery(condSql);
}
catch(Exception e){

}
}

Thursday, 7 January 2016

How to add date column on OAF stranded Page:

Most of the cases we will have extra attributes in stranded tables,so in case if you want to insert date type in attributes make sure that you need to change lot of stuff.

1)Need to add transient variable in main VO means you need to extend VO.
2)Then add a message text input using personalization.
3)Then extend your controller and get the current row before applying the save
4)convert the same variable in to varchar then set this value to any attribute in standerd VO.
5)then call save method.

Important note here in case you have search page on same vo,you need to change search page as well.

It is going to impact all areas of this VO.

My suggestion just add field like plain text instead of date type,it will save your page from lot of extensions ,specially in HRMS lot of regions are shared regions in HRMS don't perform any query changes .specially LOVs. 90% LOVs are external LOVs .

function check_double_booking(p_supplied_resource_id in number
,p_required_date_from in date
,p_required_start_time in varchar2
,p_required_date_to in date
,p_required_end_time in varchar2
,p_resource_booking_id in number
,p_book_entire_period_flag in varchar2
,p_timezone in varchar2
,p_last_res_bkng_id in number)return boolean IS
--
l_proc varchar2(72) := g_package||'check_double_booking';
l_exists number;
l_book_entire_period varchar2(1);

l_resource_type varchar2(30);

cursor get_resource_type is
select resource_type
from ota_suppliable_resources
where supplied_resource_id = p_supplied_resource_id;

-- For entire duration flag null or N
cursor double_booking is
select 1
from ota_resource_bookings trb
where trb.supplied_resource_id = p_supplied_resource_id
and (
(p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
)
/*or
(to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
)*/)
and trb.status = 'C'
and (p_resource_booking_id is null
or (p_resource_booking_id is not null
and p_resource_booking_id <> trb.resource_booking_id and trb.resource_booking_id > nvl(p_last_res_bkng_id,0)));

-- Modified to exclude forum and chat related bookings
-- For entire duration flag null or N
cursor trainer_double_booking is
select 1
from ota_resource_bookings trb
where trb.supplied_resource_id = p_supplied_resource_id
and (
(p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
/*or
(to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
*/)
and trb.status = 'C'
and trb.chat_id is null
and trb.forum_id is null
and (p_resource_booking_id is null
or (p_resource_booking_id is not null
and p_resource_booking_id <> trb.resource_booking_id and trb.resource_booking_id > nvl(p_last_res_bkng_id,0))); --bug 5110895

Cursor csr_chk_date_overlap is
select Book_entire_period_flag,required_end_time,required_start_time,
required_date_from,required_date_to,timezone_code
from ota_resource_bookings trb
where trb.supplied_resource_id = p_supplied_resource_id
and p_required_date_from <= trunc (ota_timezone_util.convert_date
(trb.required_date_to, nvl (trb.required_end_time, '23:59'),
trb.timezone_code, p_timezone)) and p_required_date_to >= trunc (ota_timezone_util.convert_date
(trb.required_date_from, nvl (trb.required_start_time, '00:00'),
trb.timezone_code, p_timezone))
and trb.status = 'C'
and (p_resource_booking_id is null
or (p_resource_booking_id is not null
and p_resource_booking_id <> trb.resource_booking_id and trb.resource_booking_id > nvl(p_last_res_bkng_id,0)));

-- Modified to exclude forum and chat related bookings Cursor csr_chk_trainer_date_overlap is select Book_entire_period_flag,required_end_time,required_start_time,
required_date_from,required_date_to,timezone_code
from ota_resource_bookings trb
where trb.supplied_resource_id = p_supplied_resource_id
and (
(p_required_date_from between
trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
and
trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
or
(p_required_date_to between
trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
and
trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
or
((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))
and
(p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))))
)
and trb.status = 'C'
and trb.chat_id is null
and trb.forum_id is null
and (p_resource_booking_id is null
or (p_resource_booking_id is not null
and p_resource_booking_id <> trb.resource_booking_id and trb.resource_booking_id > nvl(p_last_res_bkng_id,0)));

--
begin
hr_utility.set_location('Entering:'||l_proc,5);
--
open get_resource_type;
fetch get_resource_type into l_resource_type; close get_resource_type;

if (l_resource_type = 'T') then

open trainer_double_booking;
fetch trainer_double_booking into l_exists;

if trainer_double_booking%found then
--
close trainer_double_booking;
return TRUE;
else
close trainer_double_booking;

for trainer_rec in csr_chk_trainer_date_overlap
loop

/*Fetch csr_chk_trainer_date_overlap into l_book_entire_period;
if csr_chk_trainer_date_overlap%NotFound then
close csr_chk_trainer_date_overlap;
--No date overlap
return FALSE;
else
close csr_chk_trainer_date_overlap;*/
--Date overlap present
-- Check new or existing either one is book enire period Y

if ((p_required_date_from <> p_required_date_to)
and (trainer_rec.required_date_from <> trainer_rec.required_date_to)) then
if trainer_rec.book_entire_period_flag = 'Y' or p_book_entire_period_flag = 'Y' then
--check time overlap
if (
(p_required_date_from = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))
and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))
or
(p_required_date_to = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
) then

return false;
else
return TRUE ;
end if;

end if;
--bug 5152139
elsif(
(p_required_date_from = p_required_date_to and trainer_rec.book_entire_period_flag = 'Y')
or (trainer_rec.required_date_from = trainer_rec.required_date_to and p_book_entire_period_flag = 'Y')
) then

--since first cursor didn't give problem this means new and old record dates cannot be equal
--and time
if(
(p_required_date_from = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone)) and
nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
or
(p_required_date_to = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)) and
nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)
)
) then

return false;

else

return True;
end if;
--bug 5116223
elsif((p_required_date_from = p_required_date_to or trainer_rec.required_date_from = trainer_rec.required_date_to ) and trainer_rec.timezone_code <> p_timezone)then
if(trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
<> trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))) then
if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)
and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
) then
return true;
end if;
end if;
end if;
end loop;

end if;

else

open double_booking;
fetch double_booking into l_exists;

if double_booking%found then
--
close double_booking;
return TRUE;
else
close double_booking;
-- not sure if still conflict or not depending on book_entire_period_flag of existing or new record

--get date overlap record

for rec in csr_chk_date_overlap
loop

/*Fetch csr_chk_trainer_date_overlap into l_book_entire_period;
if csr_chk_trainer_date_overlap%NotFound then
close csr_chk_trainer_date_overlap;
--No date overlap
return FALSE;
else
close csr_chk_trainer_date_overlap;*/
--Date overlap present
-- Check new or existing either one is book enire period Y

/* if ((p_required_date_from <> p_required_date_to)
and (rec.required_date_from <> rec.required_date_to)) then*/
if rec.book_entire_period_flag = 'Y' or p_book_entire_period_flag = 'Y' then
--check time overlap
if (
(p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
or
(p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
) then

return false;
else
return TRUE ;
end if;

elsif(
(p_required_date_from = p_required_date_to and rec.book_entire_period_flag = 'Y')
or (rec.required_date_from = rec.required_date_to and p_book_entire_period_flag = 'Y')
) then

--since first cursor didn't give problem this means new and old record dates cannot be equal
--and time
if(
(p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and
nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
or
(p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and
nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
)
) then

return false;

else

return True;
end if;
--bug 5116223
elsif((p_required_date_from = p_required_date_to or rec.required_date_from = rec.required_date_to) and rec.timezone_code <> p_timezone ) then
if(trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
<> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then
if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
) then
return true;
end if;
end if;
end if;
end loop;

end if;

end if;

--close double_booking;
return FALSE;
--
hr_utility.set_location('Leaving:'||l_proc,10);

end check_double_booking;

SELECT distinct srl.NAME, tsr.supplied_resource_id, per.full_name,
tsr.vendor_id, ven.vendor_name, tsr.training_center_id,
ota_general.get_org_name
(tsr.training_center_id)
training_center_name,
tsr.location_id,
ota_general.get_location_code (tsr.location_id) LOCATION,
tsr.resource_type, tsr.start_date, tsr.end_date, tsr.COST,
tsr.currency_code, tsr.cost_unit, tsr.consumable_flag,
tsr.special_instruction,
(SELECT decode(orb.status,'C','Not Allowed to Book','Available') FROM OTA_RESOURCE_BOOKINGS ORB
where ORB.SUPPLIED_RESOURCE_ID=srl.SUPPLIED_RESOURCE_ID) status
FROM ota_suppliable_resources tsr,
ota_suppliable_resources_tl srl,
per_all_people_f per,
po_vendors ven
WHERE srl.supplied_resource_id = tsr.supplied_resource_id
AND srl.LANGUAGE = USERENV ('LANG')
AND tsr.business_group_id = ota_general.get_business_group_id
AND per.person_id(+) = tsr.trainer_id
AND tsr.start_date BETWEEN per.effective_start_date(+) AND per.effective_end_date(+)
AND ven.vendor_id(+) = tsr.vendor_id

_________________________________________________________________________

select ota_trb_api_procedures.function check_double_booking( ) essss from dual

Wednesday, 6 January 2016

Callable statement in OAF:

import oracle.jdbc.OracleCallableStatement;

String ="SQL STMT";

OracleCallableStatement oraclecallablestatement =
(OracleCallableStatement)oadbtransaction.createCallableStatement(str.toString(),
1);
try {

oraclecallablestatement.setString(1,
bindVeriable);
oraclecallablestatement.setString(2,bindVeriable);
oraclecallablestatement.execute();
oraclecallablestatement.close();

} catch (Exception e) {

throw OAException.wrapperException(e);
}

Monday, 4 January 2016

PreparedStatement in OAF

String query="SQL STMT";

try{
Connection conn = pageContext.getApplicationModule(webBean).getOADBTransaction().getJdbcConnection();
PreparedStatement stmt = conn.prepareStatement(query);
stmt.setString(1, BINDPARAM);

for(ResultSet resultset = stmt.executeQuery(); resultset.next();)
{

billingCoderesult = resultset.getString("GETVALUE");

}
}

catch(Exception exception)

{
throw new OAException("EXE", OAException.ERROR);
}