D2K/RDF Reports - Some points to care of in R12

2013-03-15T01:14:00.000-07:00

Are you developing report in R12?
Does this report contain Data Source as RDF(D2K report)?
Don't you know basic difference between 11i and R12 RDF programming?
Don't you know how to achieve Organization Security in R12 RDF?

If yes, please take care of below things,

1) For R12, download Oracle Developer Suite 10g(10.1.2.0.2) from OTN. Version upgrades on this install can be done using patches mentioned in Oracle Support Note 437878.1, although it is not mandatory.

2)If you are developing an organization security enabled RDF,

a)use VPD(Virtual Private Database) enabled synonyms if present in query e.g, po_headers,po_lines,etc.There are still some Org-Based views

in R12 particularly HR views e.g, PER_PEOPLE_F,etc .Don't use base tables like _all tables.

    b)if it is VPD enabled SYNONYM in query, call FND SRWINIT user exit in after parameter form trigger.If it is Org-Based View in query,call FND SRWINIT user exit in before report trigger.
It was always before report trigger in 11i, where we used to call FND SRWINIT user exit.
I have seen guys working on R12 RDFs, calling FND SRWINIT user exit in before report trigger for VPD enables synonyms also, and because it does not enable org security, wrongly calling MO_GLOBAL or FND_GLOBAL.APPS_INITIALIZE apis.

    c)if it is Operating Unit security, set Operating Unit Mode for the report concurrent program using System Administration mode(Point 3 elaborates more on this).

    d)Inventory Org security in Concurrent Program Reports is enabled by passing MFG_ORGANIZATION_ID profile value as parameter and adding it
to the where clause of query.

3)R12 has the facility to access/view multiple operating units'(set at Context) data .We can set up report concurrent program to have Operating
Unit parameter using,
System Administration -> Concurrent Programs ->Search Concurrent Program ->
Click on Update button present in row of required concurrent program ->
Request tab-> Operating Unit mode.
If Single is selected at Operating Unit Mode , Operating Unit parameter will automatically be added.When multiple is selected, data of all Operating Units in context will be shown in report.

4)Don't add Operating Unit parameter manually in Define Concurrent Program parameters window.It will be automatically added if Operating Unit Mode is selected as Single.

Dependent LOV(List of Values) in Oracle Web ADI with steps and source code

2012-09-09T13:24:00.000-07:00

Hello,

Achieving Dependent LOV in Oracle Web ADI is quite tedious task. But don't worry, I am sharing the steps to create one.

You must know basics of Oracle Web-ADI before trying below steps.

Dependent LOV in Oracle Web ADI is created by writing 3 Java Classes as,
1)SQL Class
2)Validator Class
3)Component Class
The Java Source Code of these classes is present below,

Scenario-
Consider we have two columns in Web-ADI,
1)Supplier
2)Supplier Site

We want to show only Supplier Sites of selected Supplier, i.e., we want to filter Supplier Sites as per the Supplier selection.

We will create Supplier as static LOV.We can also create Supplier as static pop-list.

The steps are as below,
(1)Create Web-ADI containing Supplier and Supplier Site as plain enterable columns, i.e., on which LOV is not set yet.

(2)Create Static LOV (Table LOV) for Supplier using following API,
begin
BNE_INTEGRATOR_UTILS.CREATE_TABLE_LOV
(P_APPLICATION_ID => 200,
P_INTERFACE_CODE => 'GENERAL_1_INTF', --BNE_INTERFACE_COLS_B.INTERFACE_CODE
P_INTERFACE_COL_NAME => 'P_SUP', --BNE_INTERFACE_COLS_B.INTERFACE_COL_NAME
P_ID_COL => 'SEGMENT1',
P_MEAN_COL => 'VENDOR_NAME',
P_DESC_COL =>NULL,
P_TABLE => 'AP_SUPPLIERS',
P_ADDL_W_C => null,
P_WINDOW_CAPTION => 'Suppliers',
P_WINDOW_WIDTH => 400,
P_WINDOW_HEIGHT => 300,
P_TABLE_BLOCK_SIZE => 10,
P_TABLE_SORT_ORDER => 'ascending',
P_USER_ID => -1
,P_TABLE_COLUMNS =>NULL,
P_TABLE_SELECT_COLUMNS =>NULL,
P_TABLE_COLUMN_ALIAS =>NULL,
P_TABLE_HEADERS =>NULL,
P_POPLIST_FLAG =>'N'
);
commit;
end;

(3)Copy below 3 java files to JAVA_TOP and compile in given order only

a)SagarSupplierSiteNameSQL.java-below is source code

package sagar.oracle.apps.lovtest.sql;

import java.sql.Connection;
import java.sql.SQLException;

import oracle.apps.bne.exception.BneException;
import oracle.apps.bne.framework.BneWebAppsContext;
import oracle.apps.bne.utilities.sql.BneBaseSQL;

public class SagarSupplierSiteNameSQL extends BneBaseSQL {
public SagarSupplierSiteNameSQL(BneWebAppsContext paramBneWebAppsContext,
String paramString) throws SQLException,
BneException {
Connection connection = paramBneWebAppsContext.getJDBCConnection();
StringBuffer stringBuffer = new StringBuffer();

stringBuffer.append("SELECT ss.vendor_site_code, ss.address_line1 || ',' || ss.city || ',' || ss.state address FROM ap_suppliers s,ap_supplier_sites_all ss WHERE ss.vendor_id = s.vendor_id AND s.vendor_name = :1");

if ((paramString != null) && (!paramString.trim().equals(""))) {
stringBuffer.append("AND " + paramString);
}

//stringBuffer.append(" ORDER BY VENDOR_SITE_CODE, ADDRESS ");

setQuery(connection, stringBuffer.toString());
}
}

b)SagarSupplierSiteNameValidator.java-below is source code

package sagar.oracle.apps.lovtest.validator;

import java.util.Hashtable;

import oracle.apps.bne.exception.BneException;
import oracle.apps.bne.exception.BneFatalException;
import oracle.apps.bne.exception.BneMissingParameterException;
import oracle.apps.bne.framework.BneWebAppsContext;
import oracle.apps.bne.integrator.validators.BneUploadValidator;
import oracle.apps.bne.utilities.sql.BneCompositeSQLCriteria;
import oracle.apps.bne.utilities.sql.BneResultSet;
import oracle.apps.bne.utilities.sql.BneSQLStatement;

import sagar.oracle.apps.lovtest.sql.SagarSupplierSiteNameSQL;

public class SagarSupplierSiteNameValidator extends BneUploadValidator {

public String[] getDomainParameters() {
return new String[] { "P_SUP" };
}

public BneResultSet getDomainValues(BneWebAppsContext paramBneWebAppsContext,
Hashtable paramHashtable,
BneCompositeSQLCriteria paramBneCompositeSQLCriteria) throws BneException {

SagarSupplierSiteNameSQL sagarSupplierSiteNameSQL = null;
BneResultSet bneResultSet = null;
BneSQLStatement bneSQLStatement1 = new BneSQLStatement();

if (paramBneCompositeSQLCriteria != null) {
bneSQLStatement1 =
paramBneCompositeSQLCriteria.evaluate(bneSQLStatement1);
}

String str1 = (String)paramHashtable.get("P_SUP");

if (str1 == null) {
throw new BneMissingParameterException("Supplier Field Error");
}

try {
sagarSupplierSiteNameSQL =
new SagarSupplierSiteNameSQL(paramBneWebAppsContext,
bneSQLStatement1.getStatement());
BneSQLStatement bneSQLStatement2 =
new BneSQLStatement(sagarSupplierSiteNameSQL.getQuery(),
new Object[] { str1 });

bneSQLStatement2.append("", bneSQLStatement1.getBindValues());
bneResultSet =
sagarSupplierSiteNameSQL.getBneResultSet(bneSQLStatement2.getBindValuesAsArray());
} catch (Exception exception) {
throw new BneFatalException(exception.toString());
}

return bneResultSet;
}

}

(c)SagarSupplierSiteNameComponent.java-below is source code

package sagar.oracle.apps.lovtest.component;

import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import java.util.Hashtable;
import java.util.Vector;

import oracle.apps.bne.exception.BneException;
import oracle.apps.bne.exception.BneMissingParameterException;
import oracle.apps.bne.exception.BneParameterException;
import oracle.apps.bne.exception.BneSQLException;
import oracle.apps.bne.framework.BneBajaContext;
import oracle.apps.bne.framework.BneBajaPage;
import oracle.apps.bne.framework.BneWebAppsContext;
import oracle.apps.bne.integrator.component.BneAbstractListOfValues;
import oracle.apps.bne.parameter.BneParameter;
import oracle.apps.bne.repository.BneResourceString;
import oracle.apps.bne.utilities.BneUIXUtils;
import oracle.apps.bne.utilities.sql.BneCompositeSQLCriteria;
import oracle.apps.bne.utilities.sql.BneResultSet;
import oracle.apps.bne.utilities.sql.BneSimpleSQLCriteria;
import oracle.apps.bne.webui.control.BneLOVControlBean;

import sagar.oracle.apps.lovtest.validator.SagarSupplierSiteNameValidator;

import oracle.cabo.servlet.Page;
import oracle.cabo.servlet.event.PageEvent;
import oracle.cabo.ui.data.DictionaryData;

public class SagarSupplierSiteNameComponent extends BneAbstractListOfValues {

private SagarSupplierSiteNameValidator VALIDATOR = null;
private String[] VALIDATOR_PARAMS = null;

private String FILTERFIELD = null;
private String FILTERVALUE = null;

public String getLOVProcessorType() {
return "TABLE";
}

public void init(BneBajaContext paramBneBajaContext, Page paramPage,
PageEvent paramPageEvent) {
if (VALIDATOR == null) {
VALIDATOR = new SagarSupplierSiteNameValidator();
VALIDATOR_PARAMS = VALIDATOR.getDomainParameters();
}
}

public BneBajaPage handleListOfValues(BneBajaContext paramBneBajaContext,
Page paramPage,
PageEvent paramPageEvent,
BneLOVControlBean paramBneLOVControlBean) throws BneException {
BneWebAppsContext bneWebAppsContext =
paramBneBajaContext.getBneWebAppsContext();
BneCompositeSQLCriteria bneCompositeSQLCriteria = null;
Hashtable hashtable = new Hashtable();

handlePageParameters(paramPageEvent);

for (int i = 0; i < VALIDATOR_PARAMS.length; i++) {
String str2 =
getParameterValue(bneWebAppsContext, VALIDATOR_PARAMS[i]);
if (str2 == null)
continue;
hashtable.put(VALIDATOR_PARAMS[i], str2);
}

if ((FILTERVALUE != null) && (!FILTERVALUE.trim().equals(""))) {
bneCompositeSQLCriteria = new BneCompositeSQLCriteria();
if (FILTERFIELD != null && !FILTERFIELD.equals("")) {
BneSimpleSQLCriteria bneSimpleSQLCriteria;
if (FILTERFIELD.equals("VENDOR_SITE_CODE"))
bneSimpleSQLCriteria =
new BneSimpleSQLCriteria(0, "VENDOR_SITE_CODE", 0,
9, FILTERVALUE, 2);
else
bneSimpleSQLCriteria =
new BneSimpleSQLCriteria(0, "ADDRESS_LINE1 || ',' || CITY || ',' || STATE",
0, 9, FILTERVALUE, 2);
bneSimpleSQLCriteria.setSearchsCaseInsensitivity(true);
bneCompositeSQLCriteria.addCriteria(bneSimpleSQLCriteria);
}

}

setTableFilter(true);
setTableData(getTableData(bneWebAppsContext, paramBneLOVControlBean,
hashtable, bneCompositeSQLCriteria));
return null;
}

public void getListOfValueParameters() throws BneParameterException {
for (int i = 0; i < VALIDATOR_PARAMS.length; i++) {
String str1 = VALIDATOR_PARAMS[i];
String str2 =
"Oracle Applications Sup Sup Site Test." + str1 + " field.";
addComponentParameter(new BneParameter(str1, "", str2));
}
}

private void handlePageParameters(PageEvent paramPageEvent) throws BneException {
FILTERFIELD =
BneUIXUtils.getPageEventParameter(paramPageEvent, "listOfValues:bne:filterField");
FILTERVALUE =
BneUIXUtils.getPageEventParameter(paramPageEvent, "listOfValues:bne:filterValue");
}

public DictionaryData[] getTableData(BneWebAppsContext paramBneWebAppsContext,
BneLOVControlBean paramBneLOVControlBean,
Hashtable paramHashtable,
BneCompositeSQLCriteria paramBneCompositeSQLCriteria) throws BneException {
DictionaryData dictionaryData = null;
Vector vector = new Vector();
BneResultSet bneResultSet = null;
ResultSetMetaData resultSetMetaData = null;
try {
String str = null;

bneResultSet =
VALIDATOR.getDomainValues(paramBneWebAppsContext, paramHashtable,
paramBneCompositeSQLCriteria);

if (bneResultSet != null) {
resultSetMetaData = bneResultSet.getResultSet().getMetaData();

while (bneResultSet.next()) {
dictionaryData = new DictionaryData();

for (int i = 1; i <= resultSetMetaData.getColumnCount();
i++) {
str = bneResultSet.getString(i);

if (str == null) {
dictionaryData.put(resultSetMetaData.getColumnName(i),
"");
} else {
dictionaryData.put(resultSetMetaData.getColumnName(i),
str);
}
}

vector.addElement(dictionaryData);
}
}
} catch (SQLException sqlException) {
throw new BneSQLException(BneResourceString.getMlsString(-1L, -1L,
"Cannot get Supplier Site Name information"),
sqlException);
} catch (BneMissingParameterException bneMissingParameterException) {
paramBneLOVControlBean.addError(bneMissingParameterException.getMessage());
}

DictionaryData[] arrayOfDictionaryData =
new DictionaryData[vector.size()];

for (int i = 0; i < vector.size(); i++) {
arrayOfDictionaryData[i] = ((DictionaryData)vector.elementAt(i));
}

return arrayOfDictionaryData;
}

public String getComponentName() {
return "SupplierSiteName";
}

public String getComponentVersion() {
return "R12";
}
}

(4)Create Dynamic LOV (JAVA LOV) for Supplier Site using following API,

begin

BNE_INTEGRATOR_UTILS.CREATE_JAVA_LOV

(P_APPLICATION_ID => 200,

P_INTERFACE_CODE => 'GENERAL_1_INTF', --BNE_INTERFACE_COLS_B.INTERFACE_CODE

P_INTERFACE_COL_NAME => 'P_SUP_SITE', --BNE_INTERFACE_COLS_B.INTERFACE_COL_NAME

P_JAVA_CLASS => 'sagar.oracle.apps.lovtest.component.SagarSupplierSiteNameComponent',

P_WINDOW_CAPTION => 'Supplier Sites',

P_WINDOW_WIDTH => 500,

P_WINDOW_HEIGHT => 500,

P_TABLE_BLOCK_SIZE => 50,

P_TABLE_COLUMNS => 'VENDOR_SITE_CODE,ADDRESS',

P_TABLE_SELECT_COLUMNS => 'P_SUP_SITE',

P_TABLE_COLUMN_ALIAS => 'P_SUP_SITE,ADDRESS',

P_TABLE_HEADERS => 'Vendor Site Code, Address',

P_TABLE_SORT_ORDER => 'yes, yes',

P_USER_ID => -1);

commit;

end;

(5)Bounce the server and test whether Dependent LOV works?

Sagar Katrat's Oracle Apps Knowledge Sharing Blog

D2K/RDF Reports - Some points to care of in R12

Dependent LOV(List of Values) in Oracle Web ADI with steps and source code