/*===========================================================================+
| XXHCMRei XXursementDetailsCO |
+===========================================================================+
| HISTORY
+===========================================================================*/
package xxhcm.oracle.apps.per.rei XX.webui;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.server.OADBTransactionImpl;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.OAWebBeanConstants;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.fnd.framework.webui.beans.message.OAMessageStyledTextBean;
import oracle.apps.fnd.framework.webui.beans.table.OATableBean;
import oracle.apps.xdo.oa.schema.server.TemplateHelper;
import oracle.cabo.ui.data.DataObject;
import oracle.xml.parser.v2.XMLElement;
import xxhcm.oracle.apps.per.rei XX.server.XXHCMEmpRei XXursementAMImpl;
/**
* Controller for ...
*/
public class XXHCMRei XXursementDetailsCO extends OAControllerImpl {
public static final String RCS_ID = "$Header$";
public static final boolean RCS_ID_RECORDED =
VersionInfo.recordClassVersion(RCS_ID, "%packagename%");
private String APP_NAME = "XX XX";
private String TEMPLATE_CODE = "XX_REI XX_REPORT";
/**
* Layout and page setup logic for a region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processRequest(OAPageContext pageContext, OAWebBean webBean) {
super.processRequest(pageContext, webBean);
/* calling Respective Template depending on Enterprise -- Added By saha Gopal */
try{
XXHCMEmpRei XXursementAMImpl am= (XXHCMEmpRei XXursementAMImpl)pageContext.getApplicationModule(webBean);
String bg_id=am.findViewObject("XXHCMEnterpriseVO1").first().getAttribute(0).toString();
if (bg_id.equalsIgnoreCase("2008") || bg_id.equalsIgnoreCase("1428")){
APP_NAME ="PER";
TEMPLATE_CODE ="XX_REI XX_REPORT";
}
}catch(Exception e)
{
System.out.println("Error ");
}
String mode = pageContext.getParameter("MODE");
if (mode == null) {
return;
}
/*OAFormValueBean form =
(OAFormValueBean)webBean.findIndexedChildRecursive("mode");
form.setValue(mode);*/
String submittedAmount = pageContext.getParameter("SUBMITTED_BILLS");
OAMessageStyledTextBean dBean =
(OAMessageStyledTextBean)webBean.findIndexedChildRecursive("totalSubmitted");
dBean.setText(submittedAmount);
String title = new String();
if (mode.equalsIgnoreCase("PENDING")) {
OAMessageStyledTextBean bean =
(OAMessageStyledTextBean)webBean.findIndexedChildRecursive("totalExempted");
bean.setRendered(false);
bean =
(OAMessageStyledTextBean)webBean.findIndexedChildRecursive("totalPayable");
bean.setRendered(false);
title = "Expenses pending approval";
} else {
String text = pageContext.getParameter("EXEMPTED_AMOUNT");
OAMessageStyledTextBean bean =
(OAMessageStyledTextBean)webBean.findIndexedChildRecursive("totalExempted");
bean.setRendered(true);
bean.setText(text);
// int sAmount = Integer.parseInt(submittedAmount);
// int tAmount = Integer.parseInt(text);
// int total = sAmount - tAmount;
// text = Integer.toString(total);
bean =
(OAMessageStyledTextBean)webBean.findIndexedChildRecursive("totalPayable");
bean.setRendered(true);
bean.setText(text);
}
OATableBean table =
(OATableBean)webBean.findChildRecursive("detailsTable");
table.setText(title);
}
/**
* Procedure to handle form submissions for form elements in
* a region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processFormRequest(OAPageContext pageContext,
OAWebBean webBean) {
super.processFormRequest(pageContext, webBean);
if (pageContext.getParameter("backBtn") != null) {
pageContext.forwardImmediately("OA.jsp?page=/xxhcm/oracle/apps/per/rei XX/webui/XXHCMEmpRei XXursementPG",
null,
OAWebBeanConstants.KEEP_MENU_CONTEXT,
null, null, true,
OAWebBeanConstants.ADD_BREAD_CRU XX_NO);
}
if (pageContext.getParameter("print") != null) {
/*OAFormValueBean form =
(OAFormValueBean)webBean.findIndexedChildRecursive("mode");
String mode = form.getValue();*/
String mode = "PENDING";
OAMessageStyledTextBean bean =
(OAMessageStyledTextBean)webBean.findIndexedChildRecursive("totalExempted");
if (bean != null && bean.isRendered()) {
mode = "APPROVED";
}
XXHCMEmpRei XXursementAMImpl am =
(XXHCMEmpRei XXursementAMImpl)pageContext.getApplicationModule(webBean);
XMLElement root = am.getReportXml(mode);
generateReport(root, pageContext, webBean);
}
}
private void generateReport(XMLElement xmlNode, OAPageContext pageContext,
OAWebBean webBean) {
// Get the HttpServletResponse object from the PageContext. The report output is written to HttpServletResponse.
// pageContext.putParameter("MODE",mode);
DataObject sessionDictionary =
(DataObject)pageContext.getNamedDataObject("_SessionParameters");
HttpServletResponse response =
(HttpServletResponse)sessionDictionary.selectValue(null,
"HttpServletResponse");
try {
ServletOutputStream os = response.getOutputStream();
// Set the Output Report File Name and Content Type
String contentDisposition =
"attachment;filename=Rei XXursement Report.pdf";
response.setHeader("Content-Disposition", contentDisposition);
response.setContentType("application/pdf");
// Get the Data XML File as the XMLNode
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
xmlNode.print(outputStream);
ByteArrayInputStream inputStream =
new ByteArrayInputStream(outputStream.toByteArray());
ByteArrayOutputStream pdfFile = new ByteArrayOutputStream();
//Generate the PDF Report.
System.out.println("APP_NAME :"+APP_NAME);
System.out.println("TEMPLATE_CODE :"+TEMPLATE_CODE);
System.out.println("language :"+((OADBTransactionImpl)pageContext.getApplicationModule(webBean).getOADBTransaction()).getUserLocale().getLanguage());
TemplateHelper.processTemplate(((OADBTransactionImpl)pageContext.getApplicationModule(webBean).getOADBTransaction()).getAppsContext(),
APP_NAME, TEMPLATE_CODE,
((OADBTransactionImpl)pageContext.getApplicationModule(webBean).getOADBTransaction()).getUserLocale().getLanguage(),
((OADBTransactionImpl)pageContext.getApplicationModule(webBean).getOADBTransaction()).getUserLocale().getCountry(),
inputStream,
TemplateHelper.OUTPUT_TYPE_PDF,
null, pdfFile);
// Write the PDF Report to the HttpServletResponse object and flush.
byte[] b = pdfFile.toByteArray();
response.setContentLength(b.length);
os.write(b, 0, b.length);
os.flush();
os.close();
} catch (Exception e) {
response.setContentType("text/html");
throw new OAException(e.getMessage(), OAException.ERROR);
}
pageContext.setDocumentRendered(false);
}
}
======================================================================================================================================================
/*===========================================================================+
| XXHCMEmpDetailsCO: |
+===========================================================================+
| HISTORY
+===========================================================================*/
package xxhcm.oracle.apps.per.rei XX.webui;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import xxhcm.oracle.apps.per.rei XX.server.XXHCMEmpRei XXursementAMImpl;
/**
* Controller for ...
*/
public class XXHCMEmpDetailsCO extends OAControllerImpl {
public static final String RCS_ID = "$Header$";
public static final boolean RCS_ID_RECORDED =
VersionInfo.recordClassVersion(RCS_ID, "%packagename%");
/**
* Layout and page setup logic for a region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processRequest(OAPageContext pageContext, OAWebBean webBean) {
super.processRequest(pageContext, webBean);
/*Here initialize the EmpDetailsVO query*/
XXHCMEmpRei XXursementAMImpl am =
(XXHCMEmpRei XXursementAMImpl)pageContext.getApplicationModule(webBean);
am.invokeMethod("initEmpDetailsVO");
}
/**
* Procedure to handle form submissions for form elements in
* a region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processFormRequest(OAPageContext pageContext,
OAWebBean webBean) {
super.processFormRequest(pageContext, webBean);
}
}
=================================================================================================================================================
/*===========================================================================+
| XXHCMEligibilityDetailsCO |
+===========================================================================+
| HISTORY
+===========================================================================*/
package xxhcm.oracle.apps.per.rei XX.webui;
import java.util.ArrayList;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.fnd.framework.webui.beans.layout.OAMessageComponentLayoutBean;
import oracle.apps.fnd.framework.webui.beans.message.OAMessageStyledTextBean;
import xxhcm.oracle.apps.per.rei XX.server.XXHCMEmpRei XXursementAMImpl;
/**
* Controller for ...
*/
public class XXHCMEligibilityDetailsCO extends OAControllerImpl {
public static final String RCS_ID = "$Header$";
public static final boolean RCS_ID_RECORDED =
VersionInfo.recordClassVersion(RCS_ID, "%packagename%");
/**
* Layout and page setup logic for a region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processRequest(OAPageContext pageContext, OAWebBean webBean) {
super.processRequest(pageContext, webBean);
XXHCMEmpRei XXursementAMImpl am =
(XXHCMEmpRei XXursementAMImpl)pageContext.getApplicationModule(webBean);
//ArrayList eligibilities = am.initEligibilityDetailsVO();
am.initEligibilityDetailsVO();
// createChildren(pageContext, webBean, eligibilities);
}
/**
* Procedure to handle form submissions for form elements in
* a region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processFormRequest(OAPageContext pageContext,
OAWebBean webBean) {
super.processFormRequest(pageContext, webBean);
}
private void createChildren(OAPageContext pageContext, OAWebBean webBean,
ArrayList eligibilities) {
OAMessageComponentLayoutBean bean =
(OAMessageComponentLayoutBean)webBean;
for (int index = 0; index < eligibilities.size(); index += 2) {
String id = "m" + index;
OAMessageStyledTextBean childBean =
(OAMessageStyledTextBean)createWebBean(pageContext,
MESSAGE_STYLED_TEXT_BEAN,
null, id);
childBean.setStyleClass("OraDataText");
childBean.setPrompt((String)eligibilities.get(index));
childBean.setText((String)eligibilities.get(index + 1));
bean.addIndexedChild(childBean);
}
int rowCount = (int)eligibilities.size()/4;
rowCount= rowCount+(eligibilities.size()%2);
bean.setRows(rowCount);
}
}
====================================================================================================================================================
/*===========================================================================+
| XXHCMDetailsTableCO |
+===========================================================================+
| HISTORY
+===========================================================================*/
package xxhcm.oracle.apps.per.rei XX.webui;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import xxhcm.oracle.apps.per.rei XX.server.XXHCMEmpRei XXursementAMImpl;
/**
* Controller for ...
*/
public class XXHCMDetailsTableCO extends OAControllerImpl
{
public static final String RCS_ID="$Header$";
public static final boolean RCS_ID_RECORDED =
VersionInfo.recordClassVersion(RCS_ID, "%packagename%");
/**
* Layout and page setup logic for a region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processRequest(pageContext, webBean);
XXHCMEmpRei XXursementAMImpl am =
(XXHCMEmpRei XXursementAMImpl)pageContext.getApplicationModule(webBean);
String mode = pageContext.getParameter("MODE");
am.initEmployeeRei XXursementVO(mode);
}
/**
* Procedure to handle form submissions for form elements in
* a region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processFormRequest(pageContext, webBean);
}
}
=================================================================================================================================
XXBtnDisableVo:
SELECT XXHCM_REI XXURSMENT_PKG.XXHCM_DISABLE_BTN_FNC BTN_STATUS FROM DUAL;
XXCheckReimEligVo:SELECT xxhcm_rei XXursment_pkg.XXHCM_REIM_ELIG(:1,:2,:3) IS_REI XX_ELIGIBLE FROM DUAL
XXConatactDetailsVO:SELECT PAPF.FULL_NAME CONTACT_NAME
,'Self' RELATIONSHIP
FROM PER_PEOPLE_X PAPF
,FND_USER FU
WHERE PAPF.PERSON_ID=FU.EMPLOYEE_ID
AND FU.USER_ID=:1 --FND_PROFILE.VALUE('USER_ID')
AND PAPF.BUSINESS_GROUP_ID=FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
UNION ALL
SELECT PAPF.FULL_NAME CONTACT_NAME
,HL.MEANING RELATIONSHIP
FROM HR_LOOKUPS HL
,PER_PEOPLE_X PAPF
,PER_CONTACT_RELATIONSHIPS PCR
,FND_USER FU
WHERE HL.LOOKUP_TYPE= 'CONTACT'
AND HL.LOOKUP_CODE=PCR.CONTACT_TYPE
AND FU.USER_ID= :1 --FND_PROFILE.VALUE('USER_ID')
AND FU.EMPLOYEE_ID = PCR.PERSON_ID
AND PAPF.BUSINESS_GROUP_ID=FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND PAPF.PERSON_ID=PCR.CONTACT_PERSON_ID
XXCreateRimVO: SELECT PEI_INFORMATION1 REI XXURSEMENT_TYPE
,PEI_INFORMATION2 BENEFICIARY_NAME
,PEI_INFORMATION3 BILL_NU XXER
,TO_Date(Fnd_conc_date.string_to_date(PEI_INFORMATION4),'DD-Mon-YYYY') BILL_DATE
,PEI_INFORMATION5 BILL_AMOUNT
,PEI_INFORMATION7 EMPLOYEE_COMMENTS
,PEI_INFORMATION8 EMPLOYER_COMMENTS
,PERSON_EXTRA_INFO_ID PERSON_EXTRA_INFO_ID
,OBJECT_VERSION_NU XXER OVN
,PEI_INFORMATION6 BILL_STATUS
FROM PER_PEOPLE_EXTRA_INFO PEI
WHERE PEI.PERSON_ID=:1
AND DECODE(PEI_INFORMATION6,'Pending Approval','U',PEI_INFORMATION6)=:2
AND PEI.PEI_INFORMATION_CATEGORY = 'IN_REI XXURSEMENT_DETAILS'
AND XXHCM_ENTERPRISE_PKG.Ret_Enterprise(FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))= (select ENTERPRISE_ID from per_enterprises where ENTERPRISE_SHORT_CODE like '%BG_NAME%')
UNION
SELECT PEI_INFORMATION1 REI XXURSEMENT_TYPE
,PEI_INFORMATION2 BENEFICIARY_NAME
,PEI_INFORMATION3 BILL_NU XXER
,TO_Date(Fnd_conc_date.string_to_date(PEI_INFORMATION4),'DD-Mon-YYYY') BILL_DATE
,PEI_INFORMATION5 BILL_AMOUNT
,PEI_INFORMATION7 EMPLOYEE_COMMENTS
,PEI_INFORMATION8 EMPLOYER_COMMENTS
,PERSON_EXTRA_INFO_ID PERSON_EXTRA_INFO_ID
,OBJECT_VERSION_NU XXER OVN
,PEI_INFORMATION6 BILL_STATUS
FROM PER_PEOPLE_EXTRA_INFO PEI
WHERE PEI.PERSON_ID=:1
AND DECODE(PEI_INFORMATION6,'Pending Approval','U',PEI_INFORMATION6)=:2
AND PEI.PEI_INFORMATION_CATEGORY='XX XX_IN_REI XXURSEMENT_DETAILS'
AND XXHCM_ENTERPRISE_PKG.Ret_Enterprise(FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))=(select ENTERPRISE_ID from per_enterprises where ENTERPRISE_SHORT_CODE like '%BG_Name%')
XXEligibilityVO:SELECT NVL(
(SELECT 'Y' FROM DUAL
WHERE EXISTS
(
SELECT 1
FROM PER_PEOPLE_X
WHERE to_date(ORIGINAL_DATE_OF_HIRE,'dd-Mon-RRRR') <= to_date(FND_CONC_DATE.STRING_TO_DATE(:1),'dd-Mon-RRRR')
AND PERSON_ID = :2
AND BUSINESS_GROUP_ID =FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
)),'N') IS_OLD_BILL FROM DUAL
XXEnterpriseVo:
SELECT business_group_id bgid
FROM per_business_groups
WHERE
--business_group_id = FND_PROFILE.Value('PER_BUSINESS_GROUP_ID')
NAME LIKE '%BG_NAME%'
XXEntitlementVo: SELECT XXHCM_REI XXURSMENT_PKG.XXHCM_ENTITLE_AMOUNT(:1,:2) ENT_AMNT from dual
XXDateeligibilityVo: SELECT XXHCM_REI XXURSMENT_PKG.XXHCM_REIM_ELIG_YEAR(:1) isBillDateValid
from dual
XXMedBenDetVo:SELECT ROWNUM SL_NO
, PEI.PEI_INFORMATION3 BILL_NU XXER
, TO_CHAR(Fnd_conc_date.string_to_date(PEI.PEI_INFORMATION4),'DD-Mon-YYYY') BILL_DATE
, PEI.PEI_INFORMATION2 BENEFICIARY_NAME
, PEI.PEI_INFORMATION1 REI XXURSEMENT_TYPE
, FU.EMPLOYEE_ID PERSON_ID
, INITCAP(NVL(XXHCM_REI XXURSMENT_PKG.XX XX_DEP_REL_FNC(FU.EMPLOYEE_ID,PEI.PEI_INFORMATION2),'Self')) RELATIONSHIP
, PEI.PEI_INFORMATION5 SUBMITTED_BILL_VALUE
FROM PER_PEOPLE_EXTRA_INFO PEI
, FND_USER FU
WHERE FU.EMPLOYEE_ID =PEI.PERSON_ID
AND FU.USER_ID =:2
AND DECODE(PEI_INFORMATION6,'Pending Approval','PENDING','APPROVED')=:1
AND PEI.PEI_INFORMATION_CATEGORY='XX XX_IN_REI XXURSEMENT_DETAILS'
AND PEI.PEI_INFORMATION1 ='Medical Rei XXursement'
AND XXHCM_ENTERPRISE_PKG.Ret_Enterprise(FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))=
(select ENTERPRISE_ID from per_enterprises where ENTERPRISE_SHORT_CODE like '%MAX%')
AND PEI.CREATION_DATE BETWEEN TO_DATE('01-Apr'||TO_CHAR(SYSDATE,'RRRR')) AND TO_DATE('31-Mar'||(TO_CHAR(SYSDATE,'RRRR')+1))
XXprintDetVO: SELECT REIM_TYPE
, XXHCM_REI XXURSMENT_PKG.XXHCM_REIM_ELIG_VALUE(:1,REIM_TYPE) ANL_ELIG
, XXHCM_REI XXURSMENT_PKG.XX XX_YTD_ELIG(:1,REIM_TYPE) YTD_ELIG
, XXHCM_REI XXURSMENT_PKG.XX XX_YTD_CLAIM_FNC(:1,REIM_TYPE) YTD_CLAIMED
, XXHCM_REI XXURSMENT_PKG.XX XX_CLAIM_UNDER_PROS_FNC(:1,REIM_TYPE) CLAIM_UNDER_PROCESS
FROM(
SELECT DISTINCT PEI_INFORMATION1 REIM_TYPE
FROM PER_PEOPLE_EXTRA_INFO
WHERE person_id = :2
AND TRUNC(creation_date) between TO_DATE('01-'||TO_CHAR(SYSDATE,'Mon')||'-'||TO_CHAR(SYSDATE,'RRRR')) AND LAST_DAY(TRUNC(SYSDATE))
)
XXRei XXStateVo:
SELECT DECODE(SIGN(SYSDATE-PTP.CUT_OFF_DATE),1,'N','Y')STATE
FROM PER_ALL_ASSIGNMENTS_F PAAF
,PER_ALL_PEOPLE_F PAPF
,FND_USER FU
,PAY_ALL_PAYROLLS_F PAPRF
,PER_TIME_PERIODS PTP
WHERE PAPF.PERSON_ID= FU.EMPLOYEE_ID
AND FU.USER_ID=:1 --FND_PROFILE.VALUE('USER_ID')
AND PAPF.PERSON_ID=PAAF.PERSON_ID
AND PAPRF.PAYROLL_ID = PAAF.PAYROLL_ID
AND PTP.PAYROLL_ID= PAAF.PAYROLL_ID
AND (TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE)
AND (TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE)
AND (TRUNC(SYSDATE) BETWEEN PAPRF.EFFECTIVE_START_DATE AND PAPRF.EFFECTIVE_END_DATE)
AND (TRUNC(SYSDATE)>= PTP.START_DATE AND TRUNC(SYSDATE)<=PTP.END_DATE)
XXreimDetailsVo:
SELECT PEI_INFORMATION2 BENEFICIARY_NAME
,PEI_INFORMATION3 BILL_NU XXER
,TO_CHAR(Fnd_conc_date.string_to_date(PEI_INFORMATION4),'DD-Mon-YYYY') BILL_DATE
,PEI_INFORMATION5 SUBMITTED_BILL_VALUE
,PEI_INFORMATION7 EMPLOYEE_COMMENTS
,PEI_INFORMATION8 EMPLOYER_COMMENTS
,PEI_INFORMATION1 REI XXURSEMENT_TYPE
FROM PER_PEOPLE_EXTRA_INFO PEI
,FND_USER FU
WHERE FU.EMPLOYEE_ID=PEI.PERSON_ID
AND FU.USER_ID=:2
AND DECODE(PEI_INFORMATION6,'Pending Approval','PENDING','APPROVED')=:1
AND PEI.PEI_INFORMATION_CATEGORY='IN_REI XXURSEMENT_DETAILS'
AND XXHCM_ENTERPRISE_PKG.Ret_Enterprise(FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))=
(select ENTERPRISE_ID from per_enterprises where ENTERPRISE_SHORT_CODE like '% XX%')
UNION ALL
SELECT PEI_INFORMATION2 BENEFICIARY_NAME
,PEI_INFORMATION3 BILL_NU XXER
,TO_CHAR(Fnd_conc_date.string_to_date(PEI_INFORMATION4),'DD-Mon-YYYY') BILL_DATE
,PEI_INFORMATION5 SUBMITTED_BILL_VALUE
,PEI_INFORMATION7 EMPLOYEE_COMMENTS
,PEI_INFORMATION8 EMPLOYER_COMMENTS
,PEI_INFORMATION1 REI XXURSEMENT_TYPE
FROM PER_PEOPLE_EXTRA_INFO PEI
,FND_USER FU
WHERE FU.EMPLOYEE_ID=PEI.PERSON_ID
AND FU.USER_ID=:2
AND DECODE(PEI_INFORMATION6,'Pending Approval','PENDING','APPROVED')=:1
AND PEI.PEI_INFORMATION_CATEGORY='XX XX_IN_REI XXURSEMENT_DETAILS'
AND XXHCM_ENTERPRISE_PKG.Ret_Enterprise(FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))=
(select ENTERPRISE_ID from per_enterprises where ENTERPRISE_SHORT_CODE like '%BG_Name%')
No comments:
Post a Comment