Friday, 21 August 2015
Tuesday, 9 June 2015
Steps to get all the parameter of a Business Event
1. Create a temp table MY_LOG_TABLE using below script,
this will store parameters information.
2. Compile the below package TEST_BUSINESSEVENT.
3. Create a subscription of that business event using
function TEST_BUSINESSEVENT.UPDATE_ASGN.
4. Perform the steps to fire the business event.
5. Check the table MY_LOG_TABLE for the parameters detail.
Table Script:
create table my_log_table (PARAMETERS VARCHAR2(100));
Package:
create or replace package TEST_BUSINESSEVENT
AS
procedure log_me (t varchar2);
function update_asgn (p_subscription_guid in raw,
p_event in out WF_EVENT_T) return varchar2;
END;
/
create or replace package body TEST_BUSINESSEVENT
AS
procedure log_me (t varchar2) IS pragma autonomous_transaction;
begin
insert into my_log_table values(t);
commit;
end;
function update_asgn (p_subscription_guid in raw,
p_event in out WF_EVENT_T) return varchar2 is
l_wf_parameter_list_t wf_parameter_list_t;
i number := 1;
c number;
l_key varchar2(30);
l_val varchar2(2000);
begin
l_wf_parameter_list_t := p_event.getParameterList();
c := l_wf_parameter_list_t.count();
log_me('event = ' || p_event.getEventName());
log_me('count : ' || c);
while (i<=c)
loop
l_key := l_wf_parameter_list_t(i).getName();
l_val := l_wf_parameter_list_t(i).getValue();
log_me(l_key || '=' || l_val);
i := i + 1;
end loop;
return 'SUCCESS';
exception
when others then
log_me('errm' || sqlerrm);
end;
end;
AS
procedure log_me (t varchar2);
function update_asgn (p_subscription_guid in raw,
p_event in out WF_EVENT_T) return varchar2;
END;
/
create or replace package body TEST_BUSINESSEVENT
AS
procedure log_me (t varchar2) IS pragma autonomous_transaction;
begin
insert into my_log_table values(t);
commit;
end;
function update_asgn (p_subscription_guid in raw,
p_event in out WF_EVENT_T) return varchar2 is
l_wf_parameter_list_t wf_parameter_list_t;
i number := 1;
c number;
l_key varchar2(30);
l_val varchar2(2000);
begin
l_wf_parameter_list_t := p_event.getParameterList();
c := l_wf_parameter_list_t.count();
log_me('event = ' || p_event.getEventName());
log_me('count : ' || c);
while (i<=c)
loop
l_key := l_wf_parameter_list_t(i).getName();
l_val := l_wf_parameter_list_t(i).getValue();
log_me(l_key || '=' || l_val);
i := i + 1;
end loop;
return 'SUCCESS';
exception
when others then
log_me('errm' || sqlerrm);
end;
end;
Monday, 1 June 2015
Dependent LOV in WEB ADI (When LOV is dependent on two fields)
Assume we have three fields first Resource Name, second Destination
Organization and third Destination Sub-inventory. Now we are going to create LOV on Destination Sub-inventory which is dependent on Resource Name and Destination Organization.
For a dependent LOV we need to create 3 java files.
1. SQL
2. Validator
3. Component
1. SQL:
package holx.oracle.apps.icx;
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 HolxDestSubSQL extends BneBaseSQL {
public HolxDestSubSQL(BneWebAppsContext paramBneWebAppsContext, String s1, String s2,
String paramString) throws SQLException,
BneException {
Connection connection = paramBneWebAppsContext.getJDBCConnection();
StringBuffer stringBuffer = new StringBuffer();
stringBuffer.append("SELECT SECONDARY_INVENTORY_NAME, DESCRIPTION FROM MTL_SUBINVENTORIES_VAL_V WHERE SECONDARY_INVENTORY_NAME IN (SELECT DISTINCT SUBINVENTORY_CODE from CSP_RS_SUBINVENTORIES_V WHERE CONDITION_TYPE_MEANING = 'Usable' AND SYSDATE BETWEEN EFFECTIVE_DATE_START AND NVL(EFFECTIVE_DATE_END,SYSDATE) AND UPPER(RESOURCE_NAME) = UPPER(:1) AND UPPER(ORGANIZATION_CODE) = UPPER(:2)) ");
if ((paramString != null) && (!paramString.trim().equals(""))) {
stringBuffer.append(" AND " + paramString);
}
setQuery(connection, stringBuffer.toString());
}
}
2. Validator:
package holx.oracle.apps.icx;
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 holx.oracle.apps.icx.HolxDestSubSQL;
public class HolxDestSubValidator extends BneUploadValidator {
public String[] getDomainParameters() {
return new String[] {"P_RESOURCE_NAME", "P_DESTINATION_ORGANIZATION"};
}
public BneResultSet getDomainValues(BneWebAppsContext paramBneWebAppsContext,
Hashtable paramHashtable,
BneCompositeSQLCriteria paramBneCompositeSQLCriteria) throws BneException {
HolxDestSubSQL HolxDestSubSQL = null;
BneResultSet bneResultSet = null;
BneSQLStatement bneSQLStatement1 = new BneSQLStatement();
if (paramBneCompositeSQLCriteria != null)
bneSQLStatement1 = paramBneCompositeSQLCriteria.evaluate(bneSQLStatement1);
try
{
String s1 = (String)paramHashtable.get("P_RESOURCE_NAME");
String s2 = (String)paramHashtable.get("P_DESTINATION_ORGANIZATION");
if(s1 == null || "".equals(s1) || s2 == null || "".equals(s2)) {
throw new BneMissingParameterException("Error while fetching Source Name in HolxDestSubValidator");
}
HolxDestSubSQL holxdestsubsql =
new HolxDestSubSQL(paramBneWebAppsContext, s1,s2,
bneSQLStatement1.getStatement());
BneSQLStatement bneSQLStatement2 =
new BneSQLStatement(holxdestsubsql.getQuery(),
new Object[] { s1,s2 });
bneSQLStatement2.append("", bneSQLStatement1.getBindValues());
bneResultSet =
holxdestsubsql.getBneResultSet(bneSQLStatement2.getBindValuesAsArray());
} catch (Exception exception) {
throw new BneFatalException(exception.toString());
}
return bneResultSet;
}
}
3. Component:
package holx.oracle.apps.icx;
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 oracle.cabo.servlet.Page;
import oracle.cabo.servlet.event.PageEvent;
import oracle.cabo.ui.data.DictionaryData;
import holx.oracle.apps.icx.HolxDestSubValidator;
public class HolxDestSubComponent extends BneAbstractListOfValues {
private HolxDestSubValidator 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 HolxDestSubValidator();
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("SECONDARY_INVENTORY_NAME"))
bneSimpleSQLCriteria =
new BneSimpleSQLCriteria(0, "SECONDARY_INVENTORY_NAME", 0,
9, FILTERVALUE, 2);
else
bneSimpleSQLCriteria =
new BneSimpleSQLCriteria(0, "DESCRIPTION",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 ="Test" + str1 + ".";
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,
"Error in HolxDestSubComponent: Can't get Source Sub-inventory."),
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 "DestSub";
}
public String getComponentVersion() {
return "R12";
}
}
Keep these three files on server and run the below script.
begin
BNE_INTEGRATOR_UTILS.CREATE_JAVA_LOV
(P_APPLICATION_ID => 20003,
P_INTERFACE_CODE => 'HOLX_PART_XINTG_INTF1',
P_INTERFACE_COL_NAME => 'P_DESTINATION_SUBINVENTORY',
P_JAVA_CLASS => 'holx.oracle.apps.icx.HolxDestSubComponent',
P_WINDOW_CAPTION => 'Destination Subinventory',
P_WINDOW_WIDTH => 500,
P_WINDOW_HEIGHT => 500,
P_TABLE_BLOCK_SIZE => 50,
P_TABLE_COLUMNS => 'SECONDARY_INVENTORY_NAME,DESCRIPTION',
P_TABLE_SELECT_COLUMNS => 'P_DESTINATION_SUBINVENTORY',
P_TABLE_COLUMN_ALIAS => 'P_DESTINATION_SUBINVENTORY,SECONDARY_INVENTORY_NAME',
P_TABLE_HEADERS => 'Sub Inventory,Description',
P_TABLE_SORT_ORDER => 'Yes,Yes',
P_USER_ID => 161909);
commit;
end;
/
For a dependent LOV we need to create 3 java files.
1. SQL
2. Validator
3. Component
1. SQL:
package holx.oracle.apps.icx;
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 HolxDestSubSQL extends BneBaseSQL {
public HolxDestSubSQL(BneWebAppsContext paramBneWebAppsContext, String s1, String s2,
String paramString) throws SQLException,
BneException {
Connection connection = paramBneWebAppsContext.getJDBCConnection();
StringBuffer stringBuffer = new StringBuffer();
stringBuffer.append("SELECT SECONDARY_INVENTORY_NAME, DESCRIPTION FROM MTL_SUBINVENTORIES_VAL_V WHERE SECONDARY_INVENTORY_NAME IN (SELECT DISTINCT SUBINVENTORY_CODE from CSP_RS_SUBINVENTORIES_V WHERE CONDITION_TYPE_MEANING = 'Usable' AND SYSDATE BETWEEN EFFECTIVE_DATE_START AND NVL(EFFECTIVE_DATE_END,SYSDATE) AND UPPER(RESOURCE_NAME) = UPPER(:1) AND UPPER(ORGANIZATION_CODE) = UPPER(:2)) ");
if ((paramString != null) && (!paramString.trim().equals(""))) {
stringBuffer.append(" AND " + paramString);
}
setQuery(connection, stringBuffer.toString());
}
}
2. Validator:
package holx.oracle.apps.icx;
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 holx.oracle.apps.icx.HolxDestSubSQL;
public class HolxDestSubValidator extends BneUploadValidator {
public String[] getDomainParameters() {
return new String[] {"P_RESOURCE_NAME", "P_DESTINATION_ORGANIZATION"};
}
public BneResultSet getDomainValues(BneWebAppsContext paramBneWebAppsContext,
Hashtable paramHashtable,
BneCompositeSQLCriteria paramBneCompositeSQLCriteria) throws BneException {
HolxDestSubSQL HolxDestSubSQL = null;
BneResultSet bneResultSet = null;
BneSQLStatement bneSQLStatement1 = new BneSQLStatement();
if (paramBneCompositeSQLCriteria != null)
bneSQLStatement1 = paramBneCompositeSQLCriteria.evaluate(bneSQLStatement1);
try
{
String s1 = (String)paramHashtable.get("P_RESOURCE_NAME");
String s2 = (String)paramHashtable.get("P_DESTINATION_ORGANIZATION");
if(s1 == null || "".equals(s1) || s2 == null || "".equals(s2)) {
throw new BneMissingParameterException("Error while fetching Source Name in HolxDestSubValidator");
}
HolxDestSubSQL holxdestsubsql =
new HolxDestSubSQL(paramBneWebAppsContext, s1,s2,
bneSQLStatement1.getStatement());
BneSQLStatement bneSQLStatement2 =
new BneSQLStatement(holxdestsubsql.getQuery(),
new Object[] { s1,s2 });
bneSQLStatement2.append("", bneSQLStatement1.getBindValues());
bneResultSet =
holxdestsubsql.getBneResultSet(bneSQLStatement2.getBindValuesAsArray());
} catch (Exception exception) {
throw new BneFatalException(exception.toString());
}
return bneResultSet;
}
}
3. Component:
package holx.oracle.apps.icx;
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 oracle.cabo.servlet.Page;
import oracle.cabo.servlet.event.PageEvent;
import oracle.cabo.ui.data.DictionaryData;
import holx.oracle.apps.icx.HolxDestSubValidator;
public class HolxDestSubComponent extends BneAbstractListOfValues {
private HolxDestSubValidator 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 HolxDestSubValidator();
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("SECONDARY_INVENTORY_NAME"))
bneSimpleSQLCriteria =
new BneSimpleSQLCriteria(0, "SECONDARY_INVENTORY_NAME", 0,
9, FILTERVALUE, 2);
else
bneSimpleSQLCriteria =
new BneSimpleSQLCriteria(0, "DESCRIPTION",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 ="Test" + str1 + ".";
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,
"Error in HolxDestSubComponent: Can't get Source Sub-inventory."),
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 "DestSub";
}
public String getComponentVersion() {
return "R12";
}
}
Keep these three files on server and run the below script.
begin
BNE_INTEGRATOR_UTILS.CREATE_JAVA_LOV
(P_APPLICATION_ID => 20003,
P_INTERFACE_CODE => 'HOLX_PART_XINTG_INTF1',
P_INTERFACE_COL_NAME => 'P_DESTINATION_SUBINVENTORY',
P_JAVA_CLASS => 'holx.oracle.apps.icx.HolxDestSubComponent',
P_WINDOW_CAPTION => 'Destination Subinventory',
P_WINDOW_WIDTH => 500,
P_WINDOW_HEIGHT => 500,
P_TABLE_BLOCK_SIZE => 50,
P_TABLE_COLUMNS => 'SECONDARY_INVENTORY_NAME,DESCRIPTION',
P_TABLE_SELECT_COLUMNS => 'P_DESTINATION_SUBINVENTORY',
P_TABLE_COLUMN_ALIAS => 'P_DESTINATION_SUBINVENTORY,SECONDARY_INVENTORY_NAME',
P_TABLE_HEADERS => 'Sub Inventory,Description',
P_TABLE_SORT_ORDER => 'Yes,Yes',
P_USER_ID => 161909);
commit;
end;
/
Dependent LOV in WEB ADI (When LOV is dependent on only one field)
Assume we have two fields one Resource Name and another Destination Organization. Now we are going to create LOV on Destination Organization which is dependent on Resource Name.
For a dependent LOV we need to create 3 java files.
1. SQL
2. Validator
3. Component
1. SQL:
package holx.oracle.apps.icx;
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 HolxDestOrgSQL extends BneBaseSQL {
public HolxDestOrgSQL(BneWebAppsContext paramBneWebAppsContext,
String paramString) throws SQLException,
BneException {
Connection connection = paramBneWebAppsContext.getJDBCConnection();
StringBuffer stringBuffer = new StringBuffer();
stringBuffer.append("SELECT ORGANIZATION_CODE, ORGANIZATION_NAME FROM (SELECT DISTINCT OOD.ORGANIZATION_CODE, OOD.ORGANIZATION_NAME FROM CSP_RS_SUBINVENTORIES_V CRS, ORG_ORGANIZATION_DEFINITIONS OOD WHERE OOD.ORGANIZATION_ID = CRS.ORGANIZATION_ID AND CRS.CONDITION_TYPE_MEANING = 'Usable' AND SYSDATE BETWEEN EFFECTIVE_DATE_START AND NVL(CRS.EFFECTIVE_DATE_END,SYSDATE) AND UPPER(CRS.RESOURCE_NAME) = UPPER(NVL(:1,CRS.RESOURCE_NAME))) WHERE 1=1 ");
if ((paramString != null) && (!paramString.trim().equals(""))) {
stringBuffer.append(" AND " + paramString);
}
setQuery(connection, stringBuffer.toString());
}
}
2. Validator:
package holx.oracle.apps.icx;
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 holx.oracle.apps.icx.HolxDestOrgSQL;
public class HolxDestOrgValidator extends BneUploadValidator {
public String[] getDomainParameters() {
return new String[] { "P_RESOURCE_NAME" };
}
public BneResultSet getDomainValues(BneWebAppsContext paramBneWebAppsContext,
Hashtable paramHashtable,
BneCompositeSQLCriteria paramBneCompositeSQLCriteria) throws BneException {
HolxDestOrgSQL HolxDestOrgSQL = null;
BneResultSet bneResultSet = null;
BneSQLStatement bneSQLStatement1 = new BneSQLStatement();
if (paramBneCompositeSQLCriteria != null) {
bneSQLStatement1 =
paramBneCompositeSQLCriteria.evaluate(bneSQLStatement1);
}
String str1 = (String)paramHashtable.get("P_RESOURCE_NAME");
if (str1 == null) {
throw new BneMissingParameterException("Error while fetching Source Name in HolxDestOrgValidator");
}
try {
HolxDestOrgSQL =
new HolxDestOrgSQL(paramBneWebAppsContext,
bneSQLStatement1.getStatement());
BneSQLStatement bneSQLStatement2 =
new BneSQLStatement(HolxDestOrgSQL.getQuery(),
new Object[] { str1 });
bneSQLStatement2.append("", bneSQLStatement1.getBindValues());
bneResultSet =
HolxDestOrgSQL.getBneResultSet(bneSQLStatement2.getBindValuesAsArray());
} catch (Exception exception) {
throw new BneFatalException(exception.toString());
}
return bneResultSet;
}
}
3. Component:
package holx.oracle.apps.icx;
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 oracle.cabo.servlet.Page;
import oracle.cabo.servlet.event.PageEvent;
import oracle.cabo.ui.data.DictionaryData;
import holx.oracle.apps.icx.HolxDestOrgValidator;
public class HolxDestOrgComponent extends BneAbstractListOfValues {
private HolxDestOrgValidator 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 HolxDestOrgValidator();
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("ORGANIZATION_CODE"))
bneSimpleSQLCriteria =
new BneSimpleSQLCriteria(0, "ORGANIZATION_CODE", 0,
9, FILTERVALUE, 2);
else
bneSimpleSQLCriteria =
new BneSimpleSQLCriteria(0, "ORGANIZATION_NAME",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 ="Test" + str1 + ".";
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,
"Error in HolxDestOrgComponent: Can't get Destination Origination Code."),
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 "DesOrg";
}
public String getComponentVersion() {
return "R12";
}
}
Keep these three files on server and run the below script.
begin
BNE_INTEGRATOR_UTILS.CREATE_JAVA_LOV
(P_APPLICATION_ID => 20003,
P_INTERFACE_CODE => 'HOLX_PART_XINTG_INTF1',
P_INTERFACE_COL_NAME => 'P_DESTINATION_ORGANIZATION',
P_JAVA_CLASS => 'holx.oracle.apps.icx.HolxDestOrgComponent',
P_WINDOW_CAPTION => 'Destination Organization',
P_WINDOW_WIDTH => 500,
P_WINDOW_HEIGHT => 500,
P_TABLE_BLOCK_SIZE => 50,
P_TABLE_COLUMNS => 'ORGANIZATION_CODE,ORGANIZATION_NAME',
P_TABLE_SELECT_COLUMNS => 'P_DESTINATION_ORGANIZATION',
P_TABLE_COLUMN_ALIAS => 'P_DESTINATION_ORGANIZATION,ORGANIZATION_CODE',
P_TABLE_HEADERS => 'Organization Code, Organization Name',
P_TABLE_SORT_ORDER => 'Yes, Yes',
P_USER_ID => 161909);
commit;
end;
/
For a dependent LOV we need to create 3 java files.
1. SQL
2. Validator
3. Component
1. SQL:
package holx.oracle.apps.icx;
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 HolxDestOrgSQL extends BneBaseSQL {
public HolxDestOrgSQL(BneWebAppsContext paramBneWebAppsContext,
String paramString) throws SQLException,
BneException {
Connection connection = paramBneWebAppsContext.getJDBCConnection();
StringBuffer stringBuffer = new StringBuffer();
stringBuffer.append("SELECT ORGANIZATION_CODE, ORGANIZATION_NAME FROM (SELECT DISTINCT OOD.ORGANIZATION_CODE, OOD.ORGANIZATION_NAME FROM CSP_RS_SUBINVENTORIES_V CRS, ORG_ORGANIZATION_DEFINITIONS OOD WHERE OOD.ORGANIZATION_ID = CRS.ORGANIZATION_ID AND CRS.CONDITION_TYPE_MEANING = 'Usable' AND SYSDATE BETWEEN EFFECTIVE_DATE_START AND NVL(CRS.EFFECTIVE_DATE_END,SYSDATE) AND UPPER(CRS.RESOURCE_NAME) = UPPER(NVL(:1,CRS.RESOURCE_NAME))) WHERE 1=1 ");
if ((paramString != null) && (!paramString.trim().equals(""))) {
stringBuffer.append(" AND " + paramString);
}
setQuery(connection, stringBuffer.toString());
}
}
2. Validator:
package holx.oracle.apps.icx;
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 holx.oracle.apps.icx.HolxDestOrgSQL;
public class HolxDestOrgValidator extends BneUploadValidator {
public String[] getDomainParameters() {
return new String[] { "P_RESOURCE_NAME" };
}
public BneResultSet getDomainValues(BneWebAppsContext paramBneWebAppsContext,
Hashtable paramHashtable,
BneCompositeSQLCriteria paramBneCompositeSQLCriteria) throws BneException {
HolxDestOrgSQL HolxDestOrgSQL = null;
BneResultSet bneResultSet = null;
BneSQLStatement bneSQLStatement1 = new BneSQLStatement();
if (paramBneCompositeSQLCriteria != null) {
bneSQLStatement1 =
paramBneCompositeSQLCriteria.evaluate(bneSQLStatement1);
}
String str1 = (String)paramHashtable.get("P_RESOURCE_NAME");
if (str1 == null) {
throw new BneMissingParameterException("Error while fetching Source Name in HolxDestOrgValidator");
}
try {
HolxDestOrgSQL =
new HolxDestOrgSQL(paramBneWebAppsContext,
bneSQLStatement1.getStatement());
BneSQLStatement bneSQLStatement2 =
new BneSQLStatement(HolxDestOrgSQL.getQuery(),
new Object[] { str1 });
bneSQLStatement2.append("", bneSQLStatement1.getBindValues());
bneResultSet =
HolxDestOrgSQL.getBneResultSet(bneSQLStatement2.getBindValuesAsArray());
} catch (Exception exception) {
throw new BneFatalException(exception.toString());
}
return bneResultSet;
}
}
3. Component:
package holx.oracle.apps.icx;
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 oracle.cabo.servlet.Page;
import oracle.cabo.servlet.event.PageEvent;
import oracle.cabo.ui.data.DictionaryData;
import holx.oracle.apps.icx.HolxDestOrgValidator;
public class HolxDestOrgComponent extends BneAbstractListOfValues {
private HolxDestOrgValidator 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 HolxDestOrgValidator();
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("ORGANIZATION_CODE"))
bneSimpleSQLCriteria =
new BneSimpleSQLCriteria(0, "ORGANIZATION_CODE", 0,
9, FILTERVALUE, 2);
else
bneSimpleSQLCriteria =
new BneSimpleSQLCriteria(0, "ORGANIZATION_NAME",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 ="Test" + str1 + ".";
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,
"Error in HolxDestOrgComponent: Can't get Destination Origination Code."),
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 "DesOrg";
}
public String getComponentVersion() {
return "R12";
}
}
Keep these three files on server and run the below script.
begin
BNE_INTEGRATOR_UTILS.CREATE_JAVA_LOV
(P_APPLICATION_ID => 20003,
P_INTERFACE_CODE => 'HOLX_PART_XINTG_INTF1',
P_INTERFACE_COL_NAME => 'P_DESTINATION_ORGANIZATION',
P_JAVA_CLASS => 'holx.oracle.apps.icx.HolxDestOrgComponent',
P_WINDOW_CAPTION => 'Destination Organization',
P_WINDOW_WIDTH => 500,
P_WINDOW_HEIGHT => 500,
P_TABLE_BLOCK_SIZE => 50,
P_TABLE_COLUMNS => 'ORGANIZATION_CODE,ORGANIZATION_NAME',
P_TABLE_SELECT_COLUMNS => 'P_DESTINATION_ORGANIZATION',
P_TABLE_COLUMN_ALIAS => 'P_DESTINATION_ORGANIZATION,ORGANIZATION_CODE',
P_TABLE_HEADERS => 'Organization Code, Organization Name',
P_TABLE_SORT_ORDER => 'Yes, Yes',
P_USER_ID => 161909);
commit;
end;
/
Tuesday, 26 May 2015
Script to add Billing Type to a coverage of existing contract
DECLARE
l_clev_rec_in okc_contract_pub.clev_rec_type;
l_clev_rec_out okc_contract_pub.clev_rec_type;
l_klnv_rec_in oks_contract_line_pub.klnv_rec_type;
l_klnv_rec_out oks_contract_line_pub.klnv_rec_type;
l_return_status VARCHAR2 (10);
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2 (25000);
l_msg_dummy VARCHAR2 (25000);
l_output VARCHAR2 (25000);
c_api_version CONSTANT NUMBER := 1.0;
c_init_msg_list CONSTANT VARCHAR2 (1000) := apps.okc_api.g_false;
l_err_msg VARCHAR2 (25000);
l_cle_id NUMBER;
l_cimv_rec_in okc_contract_item_pub.cimv_rec_type;
l_cimv_rec_out okc_contract_item_pub.cimv_rec_type;
l_object_id NUMBER;
l_name VARCHAR2 (30);
l_count NUMBER := 0;
c_user_id NUMBER := 1238; -- Conversion
CURSOR cur_contract_details
IS
SELECT okh.id contract_id,
okh.contract_number,
okh.sts_code,
okl1.line_number,
okl1.start_date,
okl1.end_date,
okl3.id bp_id,
okl3.currency_code
FROM okc_k_headers_all_b okh,
okc_k_lines_b okl1,
okc_k_lines_b okl2,
okc_k_lines_b okl3,
okc_k_items oki,
okx_bus_processes_v okbp,
oks_coverages_v okv
WHERE okh.ID = okl1.dnz_chr_id
AND okh.sts_code NOT IN ('TERMINATED' , 'EXPIRED')
AND okl1.sts_code NOT IN ('TERMINATED' , 'EXPIRED')
AND okl1.dnz_chr_id = okl2.dnz_chr_id
AND okl1.chr_id IS NOT NULL
AND okl1.ID = okl2.cle_id
AND okl2.lse_id = 2
AND okl2.ID = okl3.cle_id
AND okl3.lse_id = 3
AND okl3.ID = oki.cle_id
AND oki.object1_id1 = okbp.id1
AND oki.jtot_object1_code = 'OKX_BUSIPROC'
AND okbp.NAME = 'CUSTOMER SUPPORT'
AND okl1.id = okv.service_cle_id
AND okv.template_coverage_chr_id IS NULL
AND okv.name = 'R2-PLATINUM';
CURSOR cur_trx_billing_type
IS
SELECT id1,
billing_type
FROM okx_txn_billing_types_v
WHERE name = 'BDFS-CONTRACT';
BEGIN
FOR rec_contract_details IN cur_contract_details
LOOP
FOR rec_trx_billing_type IN cur_trx_billing_type
LOOP
l_clev_rec_in.ID := NULL;
l_clev_rec_in.dnz_chr_id := rec_contract_details.contract_id;
l_clev_rec_in.chr_id := NULL;
l_clev_rec_in.cle_id := rec_contract_details.bp_id;
l_clev_rec_in.lse_id := 5;
l_clev_rec_in.object_version_number := c_api_version;
l_clev_rec_in.start_date := rec_contract_details.start_date;
l_clev_rec_in.end_date := rec_contract_details.end_date;
l_clev_rec_in.currency_code := rec_contract_details.currency_code;
l_clev_rec_in.created_by := c_user_id;
l_clev_rec_in.creation_date := SYSDATE;
l_clev_rec_in.last_updated_by := c_user_id;
l_clev_rec_in.last_update_date := SYSDATE;
l_clev_rec_in.sts_code := rec_contract_details.sts_code;
l_clev_rec_in.exception_yn := 'N';
l_clev_rec_in.display_sequence := 1;
BEGIN
okc_contract_pub.create_contract_line (p_api_version => c_api_version,
p_init_msg_list => c_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_restricted_update => 'Y',
p_clev_rec => l_clev_rec_in,
x_clev_rec => l_clev_rec_out
);
IF l_return_status <> 'S' THEN
BEGIN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Error1 for Contract#'||rec_contract_details.contract_number|| ' and Line Number#'||rec_contract_details.line_number|| ' and Billing Type - '||rec_trx_billing_type.billing_type||' :'||l_output);
END;
ROLLBACK;
ELSE
BEGIN
l_klnv_rec_in.cle_id := l_clev_rec_out.ID;
l_klnv_rec_in.dnz_chr_id := l_clev_rec_out.dnz_chr_id;
l_klnv_rec_in.discount_percent := 100;
l_klnv_rec_in.created_by := c_user_id;
l_klnv_rec_in.creation_date := SYSDATE;
l_klnv_rec_in.last_updated_by := c_user_id;
l_klnv_rec_in.last_update_date := SYSDATE;
oks_contract_line_pub.create_line (p_api_version => c_api_version,
p_init_msg_list => c_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_klnv_rec => l_klnv_rec_in,
x_klnv_rec => l_klnv_rec_out,
p_validate_yn => 'Y'
);
IF l_return_status <> 'S' THEN
BEGIN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Error2 for Contract#'||rec_contract_details.contract_number|| ' and Line Number#'||rec_contract_details.line_number|| ' and Billing Type - '||rec_trx_billing_type.billing_type||' :'||l_output);
END;
ROLLBACK;
ELSE
BEGIN
l_cimv_rec_in.cle_id := l_clev_rec_out.ID;
l_cimv_rec_in.dnz_chr_id := l_clev_rec_out.dnz_chr_id;
l_cimv_rec_in.object1_id1 := rec_trx_billing_type.id1;
l_cimv_rec_in.object1_id2 := '#';
l_cimv_rec_in.jtot_object1_code := 'OKX_BILLTYPE';
l_cimv_rec_in.exception_yn := 'N';
l_cimv_rec_in.created_by := c_user_id;
l_cimv_rec_in.creation_date := SYSDATE;
l_cimv_rec_in.last_updated_by := c_user_id;
l_cimv_rec_in.last_update_date := SYSDATE;
okc_contract_item_pub.create_contract_item (p_api_version => c_api_version,
p_init_msg_list => c_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_cimv_rec => l_cimv_rec_in,
x_cimv_rec => l_cimv_rec_out
);
IF l_return_status <> 'S' THEN
BEGIN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Error2 for Contract#'||rec_contract_details.contract_number|| ' and Line Number#'||rec_contract_details.line_number|| ' and Billing Type - '||rec_trx_billing_type.billing_type||' :'||l_output);
END;
ROLLBACK;
ELSE
COMMIT;
l_count:= l_count+1;
END IF;
END;
END IF;
END;
END IF;
END;
END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Contracts Updated successfull are :'||l_count);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error in the process:'||SQLERRM);
END;
l_clev_rec_in okc_contract_pub.clev_rec_type;
l_clev_rec_out okc_contract_pub.clev_rec_type;
l_klnv_rec_in oks_contract_line_pub.klnv_rec_type;
l_klnv_rec_out oks_contract_line_pub.klnv_rec_type;
l_return_status VARCHAR2 (10);
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2 (25000);
l_msg_dummy VARCHAR2 (25000);
l_output VARCHAR2 (25000);
c_api_version CONSTANT NUMBER := 1.0;
c_init_msg_list CONSTANT VARCHAR2 (1000) := apps.okc_api.g_false;
l_err_msg VARCHAR2 (25000);
l_cle_id NUMBER;
l_cimv_rec_in okc_contract_item_pub.cimv_rec_type;
l_cimv_rec_out okc_contract_item_pub.cimv_rec_type;
l_object_id NUMBER;
l_name VARCHAR2 (30);
l_count NUMBER := 0;
c_user_id NUMBER := 1238; -- Conversion
CURSOR cur_contract_details
IS
SELECT okh.id contract_id,
okh.contract_number,
okh.sts_code,
okl1.line_number,
okl1.start_date,
okl1.end_date,
okl3.id bp_id,
okl3.currency_code
FROM okc_k_headers_all_b okh,
okc_k_lines_b okl1,
okc_k_lines_b okl2,
okc_k_lines_b okl3,
okc_k_items oki,
okx_bus_processes_v okbp,
oks_coverages_v okv
WHERE okh.ID = okl1.dnz_chr_id
AND okh.sts_code NOT IN ('TERMINATED' , 'EXPIRED')
AND okl1.sts_code NOT IN ('TERMINATED' , 'EXPIRED')
AND okl1.dnz_chr_id = okl2.dnz_chr_id
AND okl1.chr_id IS NOT NULL
AND okl1.ID = okl2.cle_id
AND okl2.lse_id = 2
AND okl2.ID = okl3.cle_id
AND okl3.lse_id = 3
AND okl3.ID = oki.cle_id
AND oki.object1_id1 = okbp.id1
AND oki.jtot_object1_code = 'OKX_BUSIPROC'
AND okbp.NAME = 'CUSTOMER SUPPORT'
AND okl1.id = okv.service_cle_id
AND okv.template_coverage_chr_id IS NULL
AND okv.name = 'R2-PLATINUM';
CURSOR cur_trx_billing_type
IS
SELECT id1,
billing_type
FROM okx_txn_billing_types_v
WHERE name = 'BDFS-CONTRACT';
BEGIN
FOR rec_contract_details IN cur_contract_details
LOOP
FOR rec_trx_billing_type IN cur_trx_billing_type
LOOP
l_clev_rec_in.ID := NULL;
l_clev_rec_in.dnz_chr_id := rec_contract_details.contract_id;
l_clev_rec_in.chr_id := NULL;
l_clev_rec_in.cle_id := rec_contract_details.bp_id;
l_clev_rec_in.lse_id := 5;
l_clev_rec_in.object_version_number := c_api_version;
l_clev_rec_in.start_date := rec_contract_details.start_date;
l_clev_rec_in.end_date := rec_contract_details.end_date;
l_clev_rec_in.currency_code := rec_contract_details.currency_code;
l_clev_rec_in.created_by := c_user_id;
l_clev_rec_in.creation_date := SYSDATE;
l_clev_rec_in.last_updated_by := c_user_id;
l_clev_rec_in.last_update_date := SYSDATE;
l_clev_rec_in.sts_code := rec_contract_details.sts_code;
l_clev_rec_in.exception_yn := 'N';
l_clev_rec_in.display_sequence := 1;
BEGIN
okc_contract_pub.create_contract_line (p_api_version => c_api_version,
p_init_msg_list => c_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_restricted_update => 'Y',
p_clev_rec => l_clev_rec_in,
x_clev_rec => l_clev_rec_out
);
IF l_return_status <> 'S' THEN
BEGIN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Error1 for Contract#'||rec_contract_details.contract_number|| ' and Line Number#'||rec_contract_details.line_number|| ' and Billing Type - '||rec_trx_billing_type.billing_type||' :'||l_output);
END;
ROLLBACK;
ELSE
BEGIN
l_klnv_rec_in.cle_id := l_clev_rec_out.ID;
l_klnv_rec_in.dnz_chr_id := l_clev_rec_out.dnz_chr_id;
l_klnv_rec_in.discount_percent := 100;
l_klnv_rec_in.created_by := c_user_id;
l_klnv_rec_in.creation_date := SYSDATE;
l_klnv_rec_in.last_updated_by := c_user_id;
l_klnv_rec_in.last_update_date := SYSDATE;
oks_contract_line_pub.create_line (p_api_version => c_api_version,
p_init_msg_list => c_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_klnv_rec => l_klnv_rec_in,
x_klnv_rec => l_klnv_rec_out,
p_validate_yn => 'Y'
);
IF l_return_status <> 'S' THEN
BEGIN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Error2 for Contract#'||rec_contract_details.contract_number|| ' and Line Number#'||rec_contract_details.line_number|| ' and Billing Type - '||rec_trx_billing_type.billing_type||' :'||l_output);
END;
ROLLBACK;
ELSE
BEGIN
l_cimv_rec_in.cle_id := l_clev_rec_out.ID;
l_cimv_rec_in.dnz_chr_id := l_clev_rec_out.dnz_chr_id;
l_cimv_rec_in.object1_id1 := rec_trx_billing_type.id1;
l_cimv_rec_in.object1_id2 := '#';
l_cimv_rec_in.jtot_object1_code := 'OKX_BILLTYPE';
l_cimv_rec_in.exception_yn := 'N';
l_cimv_rec_in.created_by := c_user_id;
l_cimv_rec_in.creation_date := SYSDATE;
l_cimv_rec_in.last_updated_by := c_user_id;
l_cimv_rec_in.last_update_date := SYSDATE;
okc_contract_item_pub.create_contract_item (p_api_version => c_api_version,
p_init_msg_list => c_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_cimv_rec => l_cimv_rec_in,
x_cimv_rec => l_cimv_rec_out
);
IF l_return_status <> 'S' THEN
BEGIN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Error2 for Contract#'||rec_contract_details.contract_number|| ' and Line Number#'||rec_contract_details.line_number|| ' and Billing Type - '||rec_trx_billing_type.billing_type||' :'||l_output);
END;
ROLLBACK;
ELSE
COMMIT;
l_count:= l_count+1;
END IF;
END;
END IF;
END;
END IF;
END;
END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Contracts Updated successfull are :'||l_count);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error in the process:'||SQLERRM);
END;
Subscribe to:
Posts (Atom)