Oracle Apps technical

Saturday, 26 August 2017

AP New TDS Query - Oracle Apps

AP TDS Query :-

This is new AP TDS query for new GST Regime :-

SELECT vendor_num, supplier_invoice_no, gl_date,
tds_auth_inv_gl_date, tds_inv_amt, tds_section,
default_tds_section, tds_tax_code, inv_doc_num,
supp_inv_date, supp_cm_inv_num, supp_cm_no_date,
supp_gl_date, description, vendor_site, vendor_name,
tds_auth_ven_name, tds_auth_ven_site, tds_auth_ven_no,
tds_auth_inv_date, tds_tax_name, tds_tax_rate, supp_pan_no,
currency, supp_hdr_inv_amt,sup_tax_inv_no,
SUM (supp_line_amt) supp_line_amt,
SUM (total_tds_cm_amt) total_tds_cm_amt,
SUM (tds_std_inv_amt) tds_std_inv_amt,
SUM (net_pay_inv_amt) net_pay_inv_amt,
CASE
WHEN tds_pay_date IS NULL
AND tds_prepay_date IS NULL
THEN NULL
WHEN tds_pay_date IS NOT NULL AND tds_prepay_date IS NULL
THEN TO_CHAR (tds_pay_date, 'DD/MM/YYYY')
WHEN tds_pay_date IS NULL AND tds_prepay_date IS NOT NULL
THEN TO_CHAR (tds_prepay_date, 'DD/MM/YYYY')
WHEN tds_pay_date IS NOT NULL
AND tds_prepay_date IS NOT NULL
THEN TO_CHAR (tds_pay_date, 'DD/MM/YYYY')
|| ' - '
|| TO_CHAR (tds_prepay_date, 'DD/MM/YYYY')
END AS tds_pay_date,
std_ven_site_code,
SUM (tds_std_inv_unp_paid) tds_std_inv_unp_paid, status,
vendor_type,
gl_account_tds, gl_account_dm
FROM
(SELECT aid.invoice_distribution_id,
pv.segment1 vendor_num,
ai.invoice_num supplier_invoice_no,
TO_CHAR (ai.gl_date, 'DD/MM/YYYY') gl_date,
TO_CHAR (ai_tds.gl_date, 'DD/MM/YYYY') tds_auth_inv_gl_date,
(ai_tds.invoice_amount * NVL (ai_tds.exchange_rate, 1)) tds_inv_amt,
jti.tds_section tds_section,
( SELECT jprl.DEFAULT_SECTION_CODE
FROM apps.jai_party_regs jpr, apps.jai_party_reg_lines jprl
WHERE jprl.PARTY_REG_ID = jpr.PARTY_REG_ID
AND jpr.party_id = pv.vendor_id
AND NVL (jprl.effective_from, SYSDATE) <= SYSDATE
AND NVL (jprl.effective_to, SYSDATE) >= SYSDATE
AND REGISTRATION_TYPE_CODE IN ('PAN')
AND jpr.party_type_code IN ('THIRD_PARTY')
and rownum=1)default_tds_section,
jai.ACTUAL_TAX_ID tds_tax_code,
ai.doc_sequence_value inv_doc_num,
TO_CHAR (ai.invoice_date, 'DD/MM/YYYY') supp_inv_date,
jti.dm_invoice_num supp_cm_inv_num,
TO_CHAR (ai_dm.invoice_date, 'DD/MM/YYYY') supp_cm_no_date,
TO_CHAR (ai_dm.gl_date, 'DD/MM/YYYY') supp_gl_date,
ai.description description,
(SELECT vendor_site_code
FROM apps.po_vendor_sites_all
WHERE vendor_site_id = ai.vendor_site_id AND ROWNUM = 1)
vendor_site,
pv.vendor_name vendor_name,
pv_tds.vendor_name tds_auth_ven_name,
(SELECT vendor_site_code
FROM apps.po_vendor_sites_all
WHERE vendor_site_id = ai_tds.vendor_site_id AND ROWNUM = 1)
tds_auth_ven_site,
jti.tds_invoice_num tds_auth_ven_no,
TO_CHAR (ai_tds.invoice_date, 'DD/MM/YYYY') tds_auth_inv_date,
(SELECT jtr.tax_rate_name
FROM jai_tax_rates jtr, jai_tax_rate_details jtrd
WHERE jtr.tax_rate_id = jtrd.tax_rate_id
AND jtr.tax_rate_id = jai.ACTUAL_TAX_ID
AND TRUNC (jtrd.effective_from) <= aid.accounting_date
AND (aid.accounting_date) <=
NVL (jtrd.effective_to, aid.accounting_date))
tds_tax_name,
(SELECT jtrd.tax_rate_percentage
FROM jai_tax_rates jtr, jai_tax_rate_details jtrd
WHERE jtr.tax_rate_id = jtrd.tax_rate_id
AND jtr.tax_rate_id = jai.ACTUAL_TAX_ID
AND TRUNC (jtrd.effective_from) <= aid.accounting_date
AND (aid.accounting_date) <=
NVL (jtrd.effective_to, aid.accounting_date))
tds_tax_rate,
( SELECT jprl.REGISTRATION_NUMBER
FROM jai_party_regs jpr, jai_party_reg_lines jprl
WHERE jprl.PARTY_REG_ID = jpr.PARTY_REG_ID
AND jpr.party_id = pv.vendor_id
AND NVL (jprl.effective_from, SYSDATE) <= SYSDATE
AND NVL (jprl.effective_to, SYSDATE) >= SYSDATE
AND REGISTRATION_TYPE_CODE IN ('PAN')
AND jpr.party_type_code IN ('THIRD_PARTY')
and rownum=1) supp_pan_no,
ai_tds.invoice_currency_code currency,
(ai.invoice_amount * NVL (ai.exchange_rate, 1)) supp_hdr_inv_amt,
( (aid.amount) * NVL ( /*aid.exchange_rate*/
ai.exchange_rate, 1))
supp_line_amt,
- (SELECT CASE
WHEN jtrd.tax_rate_percentage <> 0
THEN
( ( (aid.amount) * NVL (/*aid.exchange_rate*/
ai.exchange_rate, 1))
* jtrd.tax_rate_percentage)
/ 100
WHEN jtrd.tax_rate_percentage = 0
THEN
0
END
AS tds_tax
FROM jai_tax_rates jtr, jai_tax_rate_details jtrd
WHERE jtr.tax_rate_id = jtrd.tax_rate_id
AND jtr.tax_rate_id = jai.ACTUAL_TAX_ID
AND TRUNC (jtrd.effective_from) <= aid.accounting_date
AND (aid.accounting_date) <=
NVL (jtrd.effective_to, aid.accounting_date))
total_tds_cm_amt,
(SELECT CASE
WHEN jtrd.tax_rate_percentage <> 0
THEN
( (aid.amount * NVL (/*aid.exchange_rate*/
ai.exchange_rate, 1))
* jtrd.tax_rate_percentage)
/ 100
WHEN jtrd.tax_rate_percentage = 0
THEN
0
END
AS tds_tax
FROM jai_tax_rates jtr, jai_tax_rate_details jtrd
WHERE jtr.tax_rate_id = jtrd.tax_rate_id
AND jtr.tax_rate_id = jai.ACTUAL_TAX_ID
AND TRUNC (jtrd.effective_from) <= aid.accounting_date
AND (aid.accounting_date) <=
NVL (jtrd.effective_to, aid.accounting_date))
tds_std_inv_amt,
CASE
WHEN (ai_tds.amount_paid * NVL (ai_tds.exchange_rate, 1)) <>
(ai_tds.invoice_amount * NVL (ai_tds.exchange_rate, 1))
THEN
0
WHEN (ai_tds.amount_paid * NVL (ai_tds.exchange_rate, 1)) =
(ai_tds.invoice_amount * NVL (ai_tds.exchange_rate, 1))
THEN
(SELECT CASE
WHEN jtrd.tax_rate_percentage <> 0
THEN
( (aid.amount * NVL (/*aid.exchange_rate*/
ai.exchange_rate, 1))
* jtrd.tax_rate_percentage)
/ 100
WHEN jtrd.tax_rate_percentage = 0
THEN
0
END
AS tds_tax
FROM jai_tax_rates jtr, jai_tax_rate_details jtrd
WHERE jtr.tax_rate_id = jtrd.tax_rate_id
AND jtr.tax_rate_id = jai.ACTUAL_TAX_ID
AND TRUNC (jtrd.effective_from) <=
aid.accounting_date
AND (aid.accounting_date) <=
NVL (jtrd.effective_to, aid.accounting_date))
END
AS net_pay_inv_amt,
CASE
WHEN (ai_tds.amount_paid * NVL (ai_tds.exchange_rate, 1)) <> 0
THEN
(SELECT DISTINCT TRUNC (MAX (saipa.accounting_date))
FROM ap_invoice_payments_all saipa
WHERE saipa.invoice_id = ai_tds.invoice_id)
WHEN ai_tds.amount_paid = 0
THEN
NULL
END
AS tds_pay_date,
CASE
WHEN (ai_tds.amount_paid * NVL (ai_tds.exchange_rate, 1)) <> 0
THEN
(SELECT DISTINCT TRUNC (MAX (saia_prepay.gl_date))
FROM apps.ap_invoice_distributions_all saida,
apps.ap_invoice_distributions_all saida_prepay,
ap_invoices_all saia_prepay
WHERE saida.invoice_id = ai_tds.invoice_id
AND saida.line_type_lookup_code = 'PREPAY'
AND NVL (saida.reversal_flag, 'N') != 'Y'
AND saida_prepay.invoice_distribution_id =
saida.prepay_distribution_id
AND saia_prepay.invoice_id = saida_prepay.invoice_id)
WHEN ai_tds.amount_paid = 0
THEN
NULL
END
AS tds_prepay_date,
(SELECT vendor_site_code
FROM apps.po_vendor_sites_all
WHERE vendor_site_id = ai.vendor_site_id)
std_ven_site_code,
CASE
WHEN (ai_tds.amount_paid * NVL (ai_tds.exchange_rate, 1)) =
(ai_tds.invoice_amount * NVL (ai_tds.exchange_rate, 1))
THEN
0
WHEN (ai_tds.amount_paid * NVL (ai_tds.exchange_rate, 1)) <>
(ai_tds.invoice_amount * NVL (ai_tds.exchange_rate, 1))
THEN
(SELECT CASE
WHEN jtrd.tax_rate_percentage <> 0
THEN
( (aid.amount * NVL (/*aid.exchange_rate*/
ai.exchange_rate, 1))
* jtrd.tax_rate_percentage)
/ 100
WHEN jtrd.tax_rate_percentage = 0
THEN
0
END
AS tds_tax
FROM jai_tax_rates jtr, jai_tax_rate_details jtrd
WHERE jtr.tax_rate_id = jtrd.tax_rate_id
AND jtr.tax_rate_id = jai.ACTUAL_TAX_ID
AND TRUNC (jtrd.effective_from) <=
aid.accounting_date
AND (aid.accounting_date) <=
NVL (jtrd.effective_to, aid.accounting_date))
END
AS tds_std_inv_unp_paid,
DECODE (ai_tds.amount_paid, 0, 'NOT PAID', 'PAID') status,
ai.attribute1 sup_tax_inv_no,
( gcc_tds.segment1
|| '.'
|| gcc_tds.segment2
|| '.'
|| gcc_tds.segment3
|| '.'
|| gcc_tds.segment4
|| '.'
|| gcc_tds.segment5
|| '.'
|| gcc_tds.segment6
|| '.'
|| gcc_tds.segment7)
gl_account_tds,
( gcc_dm.segment1
|| '.'
|| gcc_dm.segment2
|| '.'
|| gcc_dm.segment3
|| '.'
|| gcc_dm.segment4
|| '.'
|| gcc_dm.segment5
|| '.'
|| gcc_dm.segment6
|| '.'
|| gcc_dm.segment7)
gl_account_dm,
pv.VENDOR_TYPE_LOOKUP_CODE vendor_type
FROM apps.ap_invoices_all ai_tds,
apps.ap_invoice_distributions_all aid_tds,
apps.po_vendors pv_tds,
apps.JAI_AP_TDS_INVOICES jti,
apps.JAI_AP_TDS_THHOLD_TRXS TRSH,
apps.ap_invoices_all ai,
apps.ap_invoice_distributions_all aid,
apps.po_vendors pv,
apps.JAI_AP_WTHLD_INV_TAXES jai,
apps.ap_invoices_all ai_dm,
apps.ap_invoice_distributions_all aid_dm,
apps.gl_code_combinations_kfv gcc_tds,
apps.gl_code_combinations_kfv gcc_dm
WHERE 1 = 1
--AND jti.invoice_id = 3078412
-- and ai_tds.invoice_num='37-TDS-SI-253737'
AND ai_tds.cancelled_date IS NULL
AND jti.tds_invoice_num = ai_tds.invoice_num
and ai_tds.invoice_id=aid_tds.invoice_id
AND pv_tds.vendor_id = ai_tds.vendor_id
AND ai.invoice_id = jti.invoice_id
and ai.invoice_id = TRSH.invoice_id
and jti.tds_invoice_num=TRSH.INVOICE_TO_TDS_AUTHORITY_NUM
AND ai.invoice_num NOT LIKE '%ESIC%'
AND ai.invoice_id = aid.invoice_id
AND aid.global_attribute_category = 'JA.IN.APXINWKB.DISTRIBUTIONS'
AND AID.LINE_TYPE_LOOKUP_CODE NOT IN
('PREPAY', 'TIPV', 'TERV', 'TAX', 'MISCELLANEOUS')
AND AID.AMOUNT > 0
AND NVL (AID.REVERSAL_FLAG, 'N') <> 'Y'
AND NOT EXISTS
(SELECT '1'
FROM AP_INVOICES_ALL
WHERE INVOICE_ID = AID.INVOICE_ID AND SOURCE = 'INDIA TDS')
AND jai.invoice_id = ai.invoice_id
AND aid.INVOICE_DISTRIBUTION_ID = jai.INVOICE_DISTRIBUTION_ID
AND pv.vendor_id = ai.vendor_id
and TRSH.Threshold_Trx_Id=jai.Threshold_Trx_Id
AND ai_dm.invoice_num = jti.dm_invoice_num
and ai_dm.invoice_id=aid_dm.invoice_id
AND ai_dm.org_id = ai_tds.org_id
AND gcc_tds.code_combination_id = aid_tds.dist_code_combination_id

AND gcc_dm.code_combination_id = aid_dm.dist_code_combination_id);

Saturday, 19 August 2017

Oracle Integrator (WEB-Adi) – Part IV

Oracle WebADI -IV

Double Dependent LOV in WebADI:

Follow the same process to compile java file as you did in dependent LOV.

SQLFile:-

package xx.oracle.apps.xx.vendorlov;

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 XXPAVendorSiteSQL extends BneBaseSQL {

public XXPAVendorSiteSQL(BneWebAppsContext paramBneWebAppsContext,

String paramString) throws SQLException,

BneException {

Connection connection = paramBneWebAppsContext.getJDBCConnection();

StringBuffer stringBuffer = new StringBuffer();

stringBuffer.append("SELECT s.vendor_site_code, s.vendor_site_id FROM po_vendors v, po_vendor_sites_all s, hr_all_organization_units ou, po_vendor_contacts c WHERE NVL (v.enabled_flag, 'Y') = 'Y' AND NVL (v.vendor_type_lookup_code, 'VENDOR') <> 'EMPLOYEE' AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (v.start_date_active, SYSDATE)) AND TRUNC ( NVL (v.end_date_active, SYSDATE + 1)) AND s.vendor_id(+) = v.vendor_id AND NVL (s.purchasing_site_flag, 'N') <> 'N' AND TRUNC (NVL (s.inactive_date, SYSDATE + 1)) > TRUNC (SYSDATE) AND ou.organization_id = s.org_id AND TRUNC (SYSDATE) BETWEEN TRUNC (ou.date_from) AND TRUNC (NVL (ou.date_to, SYSDATE + 1)) AND c.vendor_site_id(+) = s.vendor_site_id AND TRUNC (NVL (c.inactive_date(+), SYSDATE + 1)) > TRUNC (SYSDATE) and v.vendor_name=:1 AND ou.name=:2");

if ((paramString != null) && (!paramString.trim().equals(""))) {

stringBuffer.append("AND " + paramString);

}

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

setQuery(connection, stringBuffer.toString());

}

ValidatorFile:-

package xx.oracle.apps.xx.vendorlov;

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 xx.oracle.apps.xx.vendorlov.XXPAVendorSiteSQL;

public class XXPAVendorSiteValidator extends BneUploadValidator {

public String[] getDomainParameters() {

return new String[] { "P_VENDOR","P_ENTITY" };

}

public BneResultSet getDomainValues(BneWebAppsContext paramBneWebAppsContext,

Hashtable paramHashtable,

BneCompositeSQLCriteria paramBneCompositeSQLCriteria) throws BneException {

XXPAVendorSiteSQL XXPAVendorSiteSQL = null;

BneResultSet bneResultSet = null;

BneSQLStatement bneSQLStatement1 = new BneSQLStatement();

if (paramBneCompositeSQLCriteria != null) {

bneSQLStatement1 =

paramBneCompositeSQLCriteria.evaluate(bneSQLStatement1);

}

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

if (str1 == null) {

throw new BneMissingParameterException("Vendor Field Error");

}

String str2 = (String)paramHashtable.get("P_ENTITY");

if (str2 == null) {

throw new BneMissingParameterException("Entity Field Error");

}

try {

XXPAVendorSiteSQL =

new XXPAVendorSiteSQL(paramBneWebAppsContext,

bneSQLStatement1.getStatement());

BneSQLStatement bneSQLStatement2 =

new BneSQLStatement(XXPAVendorSiteSQL.getQuery(),

new Object[] { str1,str2 });

bneSQLStatement2.append("", bneSQLStatement1.getBindValues());

bneResultSet =

XXPAVendorSiteSQL.getBneResultSet(bneSQLStatement2.getBindValuesAsArray());

} catch (Exception exception) {

throw new BneFatalException(exception.toString());

}

return bneResultSet;

}

ComponentFile:-

package xx.oracle.apps.xx.vendorlov;

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 xx.oracle.apps.xx.vendorlov.XXPAVendorSiteValidator;

import oracle.cabo.servlet.Page;

import oracle.cabo.servlet.event.PageEvent;

import oracle.cabo.ui.data.DictionaryData;

public class XXPAVendorSiteComponent extends BneAbstractListOfValues {

private XXPAVendorSiteValidator 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 XXPAVendorSiteValidator();

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("SITE_ADDRESS"))

bneSimpleSQLCriteria =

new BneSimpleSQLCriteria(0, "SITE_ADDRESS", 0,

9, FILTERVALUE, 2);

else

bneSimpleSQLCriteria =

new BneSimpleSQLCriteria(0, "VENDOR_SITE_CODE",

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 "SiteAddress";

}

public String getComponentVersion() {

return "R12";

}

Oracle Integrator (WEB-Adi) – Part III

Oracle WebADI -III

Dependent LOV in WebADI:-

To set the Dependent LOV in WebADI, You need to use java code files. It consists of mainly :-

1. SQLFile

2. ValidatorFile

3. ComponentFile

Save all file with extension dot(.)JAVA and keep it in $JAVA_TOP.

To compile the JAVA file . Use javac *.java

Code for SQLFile:-

package xx.oracle.apps.xx.vendorlov;

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 XXPAVendorSQL extends BneBaseSQL {

public XXPAVendorSQL(BneWebAppsContext paramBneWebAppsContext,

String paramString) throws SQLException,

BneException {

Connection connection = paramBneWebAppsContext.getJDBCConnection();

StringBuffer stringBuffer = new StringBuffer();

stringBuffer.append("SELECT distinct v.vendor_name,v.vendor_id FROM po_vendors v, po_vendor_sites_all s, hr_all_organization_units ou, po_vendor_contacts c WHERE NVL (v.enabled_flag, 'Y') = 'Y' AND NVL (v.vendor_type_lookup_code, 'VENDOR') <> 'EMPLOYEE' AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (v.start_date_active, SYSDATE)) AND TRUNC ( NVL (v.end_date_active, SYSDATE + 1)) AND s.vendor_id(+) = v.vendor_id AND NVL (s.purchasing_site_flag, 'N') <> 'N' AND TRUNC (NVL (s.inactive_date, SYSDATE + 1)) > TRUNC (SYSDATE) AND ou.organization_id = s.org_id AND TRUNC (SYSDATE) BETWEEN TRUNC (ou.date_from) AND TRUNC (NVL (ou.date_to, SYSDATE + 1)) AND c.vendor_site_id(+) = s.vendor_site_id AND TRUNC (NVL (c.inactive_date(+), SYSDATE + 1)) > TRUNC (SYSDATE) AND ou.name=:1");

if ((paramString != null) && (!paramString.trim().equals(""))) {

stringBuffer.append("AND " + paramString);

}

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

setQuery(connection, stringBuffer.toString());

}

Code for ValidatorFile:-

package xx.oracle.apps.xx.vendorlov;

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 xx.oracle.apps.xx.vendorlov.XXPAVendorSQL;

public class XXPAVendorValidator extends BneUploadValidator {

public String[] getDomainParameters() {

return new String[] { "P_ENTITY" };

}

public BneResultSet getDomainValues(BneWebAppsContext paramBneWebAppsContext,

Hashtable paramHashtable,

BneCompositeSQLCriteria paramBneCompositeSQLCriteria) throws BneException {

XXPAVendorSQL XXPAVendorSQL = null;

BneResultSet bneResultSet = null;

BneSQLStatement bneSQLStatement1 = new BneSQLStatement();

if (paramBneCompositeSQLCriteria != null) {

bneSQLStatement1 =

paramBneCompositeSQLCriteria.evaluate(bneSQLStatement1);

}

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

if (str1 == null) {

throw new BneMissingParameterException("Entity Field Error");

}

try {

XXPAVendorSQL =

new XXPAVendorSQL(paramBneWebAppsContext,

bneSQLStatement1.getStatement());

BneSQLStatement bneSQLStatement2 =

new BneSQLStatement(XXPAVendorSQL.getQuery(),

new Object[] { str1 });

bneSQLStatement2.append("", bneSQLStatement1.getBindValues());

bneResultSet =

XXPAVendorSQL.getBneResultSet(bneSQLStatement2.getBindValuesAsArray());

} catch (Exception exception) {

throw new BneFatalException(exception.toString());

}

return bneResultSet;

}

Code for ComponentFile:-

package xx.oracle.apps.xx.vendorlov;

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 xx.oracle.apps.xx.vendorlov.XXPAVendorValidator;

import oracle.cabo.servlet.Page;

import oracle.cabo.servlet.event.PageEvent;

import oracle.cabo.ui.data.DictionaryData;

public class XXPAVendorComponent extends BneAbstractListOfValues {

private XXPAVendorValidator 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 XXPAVendorValidator();

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_NAME"))

bneSimpleSQLCriteria =

new BneSimpleSQLCriteria(0, "VENDOR_NAME", 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 "VendorName";

}

public String getComponentVersion() {

return "R12";

}