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);

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

    }

}

Oracle WebADI -II

Oracle Integrator (WEB-Adi) – Part II

Sample code of previously used API.

Table script:-

CREATE TABLE XX_TEST_WEBADI_T

(

   ORG_ID             NUMBER,

   VENDOR_NAME        VARCHAR2 (100),

   VENDOR_SITE_CODE   VARCHAR2 (100)

);

Package Specification:-

CREATE OR REPLACE PACKAGE XX_TEST_WEBADI_PKG

IS

   PROCEDURE XX_TEST_WEBADI_PRC (P_ORG_ID         NUMBER,

                                 P_VENDOR_NAME    VARCHAR2,

                                 P_VENDOR_SITE VARCHAR2);

END;

Package Body:-

CREATE OR REPLACE PACKAGE BODY XX_TEST_WEBADI_PKG

IS

   PROCEDURE XX_TEST_WEBADI_PRC (P_ORG_ID         NUMBER,

                                 P_VENDOR_NAME    VARCHAR2,

                                 P_VENDOR_SITE    VARCHAR2)

   AS

   BEGIN

      INSERT INTO XX_TEST_WEBADI_T

        VALUES   (P_ORG_ID, P_VENDOR_NAME, P_VENDOR_SITE);

   EXCEPTION

      WHEN OTHERS

      THEN

         RAISE_APPLICATION_ERROR (-20001, SQLCODE || ' ' || SQLERRM);

   END XX_TEST_WEBADI_PRC;

END XX_TEST_WEBADI_PKG;   

Create functions for webadi:-

1.  Enter the Function and User function name

2. Enter Properties details,  Set Type “SSWA servlet function” always.

3. Enter form details and set Parameters value as below.

bne:page=BneCreateDoc&bne:viewer=BNE:EXCEL2003&bne:reporting=N&bne:integrator=USER_NAME:XX_TEST_WEBADI&bne:noreview=Yes

4. Enter Web HTML Details and set HTML Call as “BneApplicationService”.

 Save it and Assign this function to Menu of a responsibility.

Adding LOV Validation on ORG_ID

Query used:-

select * from bne_attributes where attribute_code like 'XX_TEST%'

select * from BNE_INTERFACES_TL where interface_code like 'XX_TEST%' and language='US'

Script to add LOV on Org_id:-

declare

  P_APPLICATION_ID       number := 201;  -- application id of Webadi application

  P_INTERFACE_CODE       VARCHAR2(20) := 'XX_TEST_XINTG_INTF1';  --  interface code fetch from above query.

  P_INTERFACE_COL_NAME   VARCHAR2(200) := 'P_ORG_ID';--parameter for lov

  P_ID_COL               VARCHAR2(200) := 'ORGANIZATION_ID';

  P_MEAN_COL             VARCHAR2(200) := 'NAME';

  P_DESC_COL             VARCHAR2(200) := NULL;

  P_TABLE                VARCHAR2(200) := 'HR_OPERATING_UNITS';

  P_ADDL_W_C             VARCHAR2(200) := '1=1';

  P_WINDOW_CAPTION       VARCHAR2(200) := 'OPERATING UNIT';

  P_WINDOW_WIDTH         NUMBER := 400;

  P_WINDOW_HEIGHT        NUMBER := 300;

  P_TABLE_BLOCK_SIZE     NUMBER := 10;

  P_TABLE_SORT_ORDER     VARCHAR2(200) := 'ascending';

  P_USER_ID              number := 1883;  -- user id from fnd_user

  P_TABLE_COLUMNS        VARCHAR2(200) := NULL;

  P_TABLE_SELECT_COLUMNS VARCHAR2(200) := NULL;

  P_TABLE_COLUMN_ALIAS   VARCHAR2(200) := NULL;

  P_TABLE_HEADERS        VARCHAR2(200) := NULL;

  P_POPLIST_FLAG         VARCHAR2(200) := 'N';

begin

  apps.BNE_INTEGRATOR_UTILS.CREATE_TABLE_LOV(P_APPLICATION_ID       => P_APPLICATION_ID,

                                             P_INTERFACE_CODE       => P_INTERFACE_CODE, --BNE_INTERFACE_COLS_B.INTERFACE_CODE

                                             P_INTERFACE_COL_NAME   => P_INTERFACE_COL_NAME, --BNE_INTERFACE_COLS_B.INTERFACE_COL_NAME

                                             P_ID_COL               => P_ID_COL,

                                             P_MEAN_COL             => P_MEAN_COL,

                                             P_DESC_COL             => P_DESC_COL,

                                             P_TABLE                => P_TABLE,

                                             P_ADDL_W_C             => P_ADDL_W_C,

                                             P_WINDOW_CAPTION       => P_WINDOW_CAPTION,

                                             P_WINDOW_WIDTH         => P_WINDOW_WIDTH,

                                             P_WINDOW_HEIGHT        => P_WINDOW_HEIGHT,

                                             P_TABLE_BLOCK_SIZE     => P_TABLE_BLOCK_SIZE,

                                             P_TABLE_SORT_ORDER     => P_TABLE_SORT_ORDER,

                                             P_USER_ID              => P_USER_ID,

                                             P_TABLE_COLUMNS        => P_TABLE_COLUMNS,

                                             P_TABLE_SELECT_COLUMNS => P_TABLE_SELECT_COLUMNS,

                                             P_TABLE_COLUMN_ALIAS   => P_TABLE_COLUMN_ALIAS,

                                             P_TABLE_HEADERS        => P_TABLE_HEADERS,

                                             P_POPLIST_FLAG         => P_POPLIST_FLAG);

  commit;

end;

Oracle WebADI - I

Oracle Integrator (WEB-Adi) – Part I

Oracle Webadi is a solution for integrating E business suite application with Desktop applications like MS Excel. Oracle EBS Integrator provides a graphical user interface which you can use to define integrators and associate supporting objects.

The desktop integration framework allows you to:-

• ·         Create integrators using wizard-base user interface.
• ·         Define integrators to upload data through PLSQL API or directly to Tables.
• ·         Define Integrators to download data from text files or using SQL Queries.
• ·         Define data validation rules.
• ·         Define layouts and mappings for custom integrators.

Responsibilities Required:-   

Steps to define a new Integrator:-

Navigation:-  Desktop Integration Manager à Manage Integrators

GST Queries - GRN

GST Related Transaction Queries: -

Good Receipts

Query to fetch the record of Receipt data with header and line GST details: -

This query use to fetch consolidated record of base tables with GST details which is handled in sub query.

  SELECT   TO_CHAR (rch.creation_DATE, 'DD-MON-YYYY') receipt_date,

           pv.segment1 || '-' || pvs.vendor_site_code Seller,

           rch.receipt_num TransactionNo,

           (SELECT   SUM (l.UNIT_PRICE * l.TRX_LINE_QUANTITY)

              FROM   jai_tax_det_factors l

             WHERE       l.trx_id = rch.shipment_header_id

                     AND l.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND l.TRX_TYPE = 'RECEIVE')

              grossamount,

           (SELECT   SUM (l.UNIT_PRICE * l.TRX_LINE_QUANTITY)

              FROM   jai_tax_det_factors l

             WHERE       l.trx_id = rch.shipment_header_id

                     AND l.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND l.TRX_TYPE = 'RECEIVE')

           + (SELECT   NVL (SUM (UNROUND_TAX_AMT_TAX_CURR), 0)

                FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

               WHERE       rch.shipment_header_id = jtl1.TRX_ID

                       AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                       AND jtl1.TRX_TYPE = 'RECEIVE')

              netamount,

           ppx.full_name SalesExec,

           (SELECT   NVL (SUM (UNROUND_TAX_AMT_TAX_CURR), 0)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       rch.shipment_header_id = jtl1.TRX_ID

                     AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.TRX_TYPE = 'RECEIVE'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%CGST%')

           + (SELECT   NVL (SUM (UNROUND_TAX_AMT_TAX_CURR), 0)

                FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

               WHERE       rch.shipment_header_id = jtl1.TRX_ID

                       AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                       AND jtl1.TRX_TYPE = 'RECEIVE'

                       AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                       AND jtt1.TAX_TYPE_NAME LIKE '%SGST%')

           + (SELECT   NVL (SUM (UNROUND_TAX_AMT_TAX_CURR), 0)

                FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

               WHERE       rch.shipment_header_id = jtl1.TRX_ID

                       AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                       AND jtl1.TRX_TYPE = 'RECEIVE'

                       AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                       AND jtt1.TAX_TYPE_NAME LIKE '%IGST%')

              GST_AMT,

           (SELECT   NVL (SUM (UNROUND_TAX_AMT_TAX_CURR), 0)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       rch.shipment_header_id = jtl1.TRX_ID

                     AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.TRX_TYPE = 'RECEIVE'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%CGST%')

              CGST_AMT,

           (SELECT   NVL (SUM (UNROUND_TAX_AMT_TAX_CURR), 0)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       rch.shipment_header_id = jtl1.TRX_ID

                     AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.TRX_TYPE = 'RECEIVE'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%SGST%')

              SGST_AMT,

           (SELECT   NVL (SUM (UNROUND_TAX_AMT_TAX_CURR), 0)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       rch.shipment_header_id = jtl1.TRX_ID

                     AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.TRX_TYPE = 'RECEIVE'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%IGST%')

              IGST_AMT,

           pvs.STATE,

           rcl.LINE_NUM,

           msi.segment1,

           rcl.QUANTITY_RECEIVED,

           rt.po_unit_price Rate,

           (rcl.quantity_received * rt.po_unit_price) line_GrossAmount,

           (rcl.quantity_received * rt.po_unit_price) TaxableValue,

           (SELECT   SUM (UNROUND_TAX_AMT_TAX_CURR)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       rch.shipment_header_id = jtl1.TRX_ID

                     AND rcl.shipment_line_id = jtl1.TRX_LINE_ID

                     AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.TRX_TYPE = 'RECEIVE'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%CGST%')

              LINE_CGST_AMT,

           (SELECT   SUM (UNROUND_TAX_AMT_TAX_CURR)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       rch.shipment_header_id = jtl1.TRX_ID

                     AND rcl.shipment_line_id = jtl1.TRX_LINE_ID

                     AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.TRX_TYPE = 'RECEIVE'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%SGST%')

              line_SGST_AMT,

           (SELECT   SUM (UNROUND_TAX_AMT_TAX_CURR)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       rch.shipment_header_id = jtl1.TRX_ID

                     AND rcl.shipment_line_id = jtl1.TRX_LINE_ID

                     AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.TRX_TYPE = 'RECEIVE'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%IGST%')

              line_IGST_AMT,

           (SELECT   MAX (TAX_RATE_PERCENTAGE)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       rch.shipment_header_id = jtl1.TRX_ID

                     AND rcl.shipment_line_id = jtl1.TRX_LINE_ID

                     AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.TRX_TYPE = 'RECEIVE'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%CGST%')

              LINE_CGST_RATE,

           (SELECT   MAX (TAX_RATE_PERCENTAGE)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       rch.shipment_header_id = jtl1.TRX_ID

                     AND rcl.shipment_line_id = jtl1.TRX_LINE_ID

                     AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.TRX_TYPE = 'RECEIVE'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%SGST%')

              line_SGST_RATE,

           (SELECT   MAX (TAX_RATE_PERCENTAGE)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE       rch.shipment_header_id = jtl1.TRX_ID

                     AND rcl.shipment_line_id = jtl1.TRX_LINE_ID

                     AND jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.TRX_TYPE = 'RECEIVE'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE '%IGST%')

              line_IGST_RATE,

                (SELECT   SUM (UNROUND_TAX_AMT_TAX_CURR)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE      rch.shipment_header_id = jtl1.TRX_ID

                     AND rcl.shipment_line_id = jtl1.TRX_LINE_ID

                     and jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE 'FREIGHT - LOCAL') FREIGHT_LOCAL,

                          (SELECT   SUM (UNROUND_TAX_AMT_TAX_CURR)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE     rch.shipment_header_id = jtl1.TRX_ID

                     AND rcl.shipment_line_id = jtl1.TRX_LINE_ID

                     and jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE 'INSURANCE') INSURANCE,

                        (SELECT   SUM (UNROUND_TAX_AMT_TAX_CURR)

              FROM   jai_tax_lines_all jtl1, jai_tax_types jtt1

             WHERE     rch.shipment_header_id = jtl1.TRX_ID

                     AND rcl.shipment_line_id = jtl1.TRX_LINE_ID

                     and jtl1.ENTITY_CODE = 'RCV_TRANSACTION'

                     AND jtl1.tax_type_id = jtt1.tax_type_id(+)

                     AND jtt1.TAX_TYPE_NAME LIKE 'P&F, FREIGHT & INSURANCE') PF_FREIGHT_INSURANCE,

                 (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 = ood.organization_id

                   AND NVL (jprl.effective_from, SYSDATE) <= SYSDATE

                   AND NVL (jprl.effective_to, SYSDATE) >= SYSDATE

                   AND REGISTRATION_TYPE_CODE IN ('GTIN', 'GSTIN')

                   AND jpr.party_type_code IN ('IO'))

            "GSTN NUMBER of FIRST PARTY",

         ood.organization_code,

         ood.organization_name

    FROM   rcv_shipment_headers rch,

           rcv_shipment_lines rcl,

           rcv_transactions rt,

           per_people_x ppx,

           ap_suppliers pv,

           ap_supplier_sites_all pvs,

           mtl_system_items_b msi,

           org_organization_definitions ood,

           /*Table of india localization Receipt line*/

           jai_tax_det_factors det_fct,

           --           jai_tax_categories def_tax_cat,

           --           jai_tax_categories tax_cat,

           ja_lookups jl

   WHERE       rch.shipment_header_id = rcl.shipment_header_id

           AND rch.shipment_header_id = rt.shipment_header_id

           AND rcl.shipment_line_id = rt.shipment_line_id

           AND rt.transaction_type = 'RECEIVE'

           AND rch.employee_id = ppx.person_id

           AND rch.vendor_id = pv.vendor_id

           AND pv.vendor_id = pvs.vendor_id

           AND rch.vendor_site_id = pvs.vendor_site_id

           /*table of india localization po line*/

           AND rch.shipment_header_id = det_fct.trx_id

           AND rcl.shipment_line_id = det_fct.trx_line_id

           AND det_fct.entity_code = 'RCV_TRANSACTION'

           AND TRX_TYPE = 'RECEIVE'

           --           and det_fct.default_tax_category_id = def_tax_cat.tax_category_id(+)

           --           and det_fct.override_tax_category_id = tax_cat.tax_category_id(+)

           AND det_fct.organization_id = msi.organization_id

           AND det_fct.item_id = msi.inventory_item_id

           and det_fct.organization_id = ood.organization_id

           AND det_fct.intended_use = jl.lookup_code(+)

           AND jl.lookup_type(+) = 'JAI_INTENDED_USE'

GROUP BY   rch.creation_date,

           pv.segment1 || '-' || pvs.vendor_site_code,

           rch.receipt_num,

          rcl.quantity_received,

          rt.po_unit_price,

           rch.shipment_header_id,

           rcl.shipment_line_id,

           pvs.state,

           msi.segment1,

           rcl.LINE_NUM,ppx.full_name,

              ood.organization_code,

         ood.organization_name,

         ood.organization_id