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

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;

Query to get Cycle Count Information of an Item

select
  mp.organization_code "Org",
  cch.cycle_count_header_name "Cycle Count",
  cce.count_list_sequence "Count Seq",
  msi.segment1 "Item",
  cce.abc_class_name "Class",
  cce.subinventory "Subinventory",
  mil.segment1 "Locator",
  cce.lot_number "Lot",
  nvl(cce.count_uom_current, cce.count_uom_prior) "UOM",
  nvl(cce.count_quantity_current, cce.count_quantity_prior) "Count Qty",
  nvl(cce.system_quantity_current, cce.system_quantity_prior) "System Qty",
  cce.adjustment_quantity "Adjust Qty",
  round(cce.adjustment_amount, 2) "Adjust Value",
  nvl(cce.number_of_counts, 0) "# of Cnts",
  cce.creation_date "Count Creation Date",
  cce.count_date_first "First Count Date",
  cce.count_date_prior "Previous Count Date",
  nvl(cce.count_date_current, cce.count_date_prior) "Last Count Date",
  cce.count_status "Status",
  cce.approval_date "Approval Date",
  decode(cce.count_status, 'Rejected', cce.last_update_date, null) "Reject Date"
from
  mtl_cycle_count_entries_v cce,
  mtl_system_items_b msi,
  mtl_cycle_count_headers cch,
  mtl_parameters mp,
  mtl_item_locations mil
where 1=1
  and cce.inventory_item_id = msi.inventory_item_id
  and cce.organization_id = msi.organization_id
  and cce.cycle_count_header_id = cch.cycle_count_header_id
  and cch.organization_id = mp.organization_id
  and cce.locator_id = mil.inventory_location_id(+)
  and msi.segment1 = '&ITEM'   --
  and cce.count_status not in('Completed','Rejected')
order by 1,2,3;

Script to add responsibility to a FND USER

DECLARE
  v_user_name  VARCHAR2(30) := upper('GK0714');
  CURSOR ABC
  IS
  select application_short_name,responsibility_key,responsibility_id
  from
   fnd_responsibility_vl a,
   fnd_application_vl b
  where a.application_id = b.application_id
  and responsibility_name in ('CRM Administrator'
                              'Customer Support',
                              'Alert Manager',
                              'General Ledger Super User');
  CURSOR CDE
  IS
  select responsibility_id
  from fnd_user_resp_groups a,
   fnd_user b
  where a.user_id  = b.user_id
  and b.user_name = v_user_name;
 
BEGIN

FOR CBA IN ABC LOOP
 FOR EDC IN CDE LOOP
   IF (EDC.responsibility_id <> CBA.responsibility_id) THEN
    fnd_user_pkg.addresp (username         => v_user_name,
                         resp_app         => cba.application_short_name,
                         resp_key         => cba.responsibility_key,
                         security_group   => 'STANDARD',
                         description      => NULL,
                         start_date       => SYSDATE-1,
                         end_date         => NULL);
  END IF;
 END LOOP;
END LOOP;
COMMIT;
END;

Script to Update the Servive Request in CRM

DECLARE
l_return_status              VARCHAR2(1);
l_msg_count                  NUMBER;
l_msg_data                   VARCHAR2(1000);
l_msg_dummy                  VARCHAR2 (4000) ;
l_output                     VARCHAR2 (15000);
l_last_update_date           DATE   := SYSDATE ;
l_service_request_rec        cs_servicerequest_pub.service_request_rec_type;
l_notes                      cs_servicerequest_pub.notes_table;
l_contacts                   cs_servicerequest_pub.contacts_table;
l_sr_update_out_rec          cs_servicerequest_pub.sr_update_out_rec_type;
l_workflow_process_id       NUMBER;
l_interaction_id            NUMBER;
l_user_id                   NUMBER;
l_resp_id                   NUMBER;
l_resp_appl_id              NUMBER;
BEGIN
   BEGIN
      SELECT user_id
      INTO l_user_id
      FROM fnd_user
      WHERE user_name = 'CONVERSION';
   EXCEPTION
     WHEN OTHERS THEN
       l_user_id := NULL;
   END;
  
   BEGIN
      SELECT responsibility_id, application_id
      INTO l_resp_id, l_resp_appl_id
      FROM fnd_responsibility_vl
      WHERE responsibility_name = 'Customer Support';
   EXCEPTION
     WHEN OTHERS THEN
       l_resp_id       := NULL;
       l_resp_appl_id  := NULL;
   END;

fnd_global.apps_initialize (l_user_id, l_resp_id, l_resp_appl_id);
MO_GLOBAL.INIT ('CS');
cs_servicerequest_pub.initialize_rec( l_service_request_rec);
l_service_request_rec.request_attribute_8 := 'Y';
CS_ServiceRequest_PUB.Update_ServiceRequest (p_api_version            => 3.0,
                                             p_init_msg_list          => FND_API.G_FALSE,
                                             p_commit                 => FND_API.G_FALSE,
                                             x_return_status          => l_return_status,
                                             x_msg_count              => l_msg_count,
                                             x_msg_data               => l_msg_data,
                                             p_request_id             => &incident_id,
                                             p_request_number         => NULL,
                                             p_audit_comments         => NULL,
                                             p_object_version_number  => &object_version_number,
                                             p_resp_appl_id           => NULL,                                                                                                                                                                                                                                         --fnd_global.resp_appl_id,
                                             p_resp_id                => NULL,                                                                                                                                                                                                                                                                                           --fnd_global.resp_id,
                                             p_last_updated_by        => NULL,                                                                                                                                                                                                                                                                                   --fnd_global.user_id,
                                             p_last_update_login      => NULL,
                                             p_workflow_process_id    => NULL,
                                             p_last_update_date       => l_last_update_date,
                                             p_service_request_rec    => l_service_request_rec,
                                             p_notes                  => l_notes,
                                             p_contacts               => l_contacts,
                                             p_called_by_workflow     => FND_API.G_FALSE,
                                             x_workflow_process_id    => l_workflow_process_id,
                                             x_interaction_id         => l_interaction_id );
      IF (l_return_status = 'S') then
        COMMIT;
      ELSE
        ROLLBACK;
         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;
         END;
               DBMS_OUTPUT.PUT_LINE ('Error :'||l_output);
       END IF;
    DBMS_OUTPUT.PUT_LINE ('Status :'||l_return_status);
END;
/

Script to update Service Request Note in CRM

SET SERVEROUTPUT ON;
DECLARE
 lc_return_status             VARCHAR2(1);
 ln_msg_count                 NUMBER;
 lc_msg_data                  VARCHAR2(5000);
 lc_msg_dummy                 VARCHAR2(5000);
 lc_output                    VARCHAR2(5000);
BEGIN
fnd_global. apps_initialize (1238, 57180, 514);
JTF_NOTES_PUB.UPDATE_NOTE
                         ( p_api_version           => 1.0,
                           p_init_msg_list         => 'F',
                           p_commit                => 'F',
                           x_return_status         => lc_return_status,
                           x_msg_count             => ln_msg_count,
                           x_msg_data              => lc_msg_data,
                           p_jtf_note_id           => 14934254,
                           p_entered_by            => 24209,
                           p_last_updated_by       => 1238,               -- 'CONVERSION' User
                           p_notes                 => 'Distributor has sent the SO141656 to send the sapre part to them . MIR.',
                           p_notes_detail          => NULL,
                           p_note_status           => 'E',
                           p_note_type             => 'CS_PROBLEM'
);
  IF (lc_return_status = 'S') THEN
   dbms_output.put_line('Note Updated Sucessfully');
   COMMIT;
  ELSE
      BEGIN
          FOR i IN 1 .. ln_msg_count
          LOOP
            fnd_msg_pub.get (i, fnd_api.g_false, lc_msg_data, lc_msg_dummy);
            lc_output := (TO_CHAR (i) || ': ' || lc_msg_data);
          END LOOP;
            dbms_output.put_line('Error :'||lc_output);
      END;
    ROLLBACK;
   END IF;
END;
/

Script to import a user as a CRM resource

DECLARE
 lc_user_name                 FND_USER.USER_NAME%TYPE           := 'GK0714';
 lc_sex                       PER_ALL_PEOPLE_F.SEX%TYPE         := 'M';
 lc_first_name                PER_ALL_PEOPLE_F.FIRST_NAME%TYPE  := 'Gaurav';
 lc_last_name                 PER_ALL_PEOPLE_F.LAST_NAME%TYPE   := 'Kumar';
 lc_employee_number           PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%TYPE;
 ln_person_id                 PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
 ln_assignment_id             PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
 ln_object_ver_number         PER_ALL_ASSIGNMENTS_F.OBJECT_VERSION_NUMBER%TYPE;
 ln_asg_ovn                   NUMBER;
 ld_per_effective_start_date  PER_ALL_PEOPLE_F.EFFECTIVE_START_DATE%TYPE;
 ld_per_effective_end_date    PER_ALL_PEOPLE_F.EFFECTIVE_END_DATE%TYPE;
 lc_full_name                 PER_ALL_PEOPLE_F.FULL_NAME%TYPE;
 ln_per_comment_id            PER_ALL_PEOPLE_F.COMMENT_ID%TYPE;
 ln_assignment_sequence       PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE%TYPE;
 lc_assignment_number         PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE;
 lb_name_combination_warning  BOOLEAN;
 lb_assign_payroll_warning    BOOLEAN;
 lb_orig_hire_warning         BOOLEAN;
 ln_user_id                   FND_USER.USER_ID%TYPE;
 lc_return_status             VARCHAR2(1);
 ln_msg_count                 NUMBER;
 lc_msg_data                  VARCHAR2(5000);
 lc_msg_dummy                 VARCHAR2(5000);
 lc_output                    VARCHAR2(5000);
 ln_resource_id               JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
 lc_resource_number           JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE;
 ln_responsibility_id         NUMBER;
 ln_application_id            NUMBER;
BEGIN
      BEGIN
           hr_employee_api.create_employee
           (   p_hire_date                       => SYSDATE,
               p_business_group_id               => fnd_profile.value_specific('PER_BUSINESS_GROUP_ID'),
               p_last_name                       => lc_last_name,
               p_first_name                      => lc_first_name,
               p_sex                             => lc_sex,
               p_employee_number                 => lc_employee_number,
               p_person_id                       => ln_person_id,
               p_assignment_id                   => ln_assignment_id,
               p_per_object_version_number       => ln_object_ver_number,
               p_asg_object_version_number       => ln_asg_ovn,
               p_per_effective_start_date        => ld_per_effective_start_date,
               p_per_effective_end_date          => ld_per_effective_end_date,
               p_full_name                       => lc_full_name,
               p_per_comment_id                  => ln_per_comment_id,
               p_assignment_sequence             => ln_assignment_sequence,
               p_assignment_number               => lc_assignment_number,
               p_name_combination_warning        => lb_name_combination_warning,
               p_assign_payroll_warning          => lb_assign_payroll_warning,
               p_orig_hire_warning               => lb_orig_hire_warning
        );
             DBMS_OUTPUT.PUT_LINE ('Person has created with Name :'||lc_full_name);
      EXCEPTION
      WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE ('Error While creating the Employee :'||SQLERRM);
      END;     
  IF (ln_person_id IS NOT NULL) THEN
      BEGIN
           fnd_user_pkg.updateuser
           (   x_user_name                       => lc_user_name,
               x_owner                           => '',
               x_employee_id                     => ln_person_id
           );
      EXCEPTION
      WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE ('Error While updating the User Name :'||SQLERRM);
       ROLLBACK;
      END;
    BEGIN
    SELECT user_id
    INTO ln_user_id
    FROM fnd_user
    WHERE user_name = lc_user_name;
    SELECT responsibility_id, application_id
    INTO ln_responsibility_id, ln_application_id
    FROM fnd_responsibility_vl
    WHERE responsibility_name = 'CRM Administrator';
    END;
    BEGIN
       fnd_global. apps_initialize (ln_user_id, ln_responsibility_id, ln_application_id);
           jtf_rs_resource_pub.create_resource
           (   p_api_version                 => 1.0,
               p_category                     => 'EMPLOYEE',
               p_start_date_active             => SYSDATE,
               p_source_id                    => ln_person_id,
               p_user_id                    => ln_user_id,
               p_user_name                  => lc_user_name,
               p_resource_name              => lc_full_name,
               p_source_number              => lc_employee_number,
               p_source_name                => lc_full_name,
               p_source_first_name          => lc_first_name,
               p_source_last_name           => lc_last_name,
               p_source_business_grp_id        => fnd_profile.value_specific('PER_BUSINESS_GROUP_ID'),  
               x_return_status                 => lc_return_status,
               x_msg_count                     => ln_msg_count,
               x_msg_data                     => lc_msg_data,
               x_resource_id                 => ln_resource_id,
               x_resource_number             => lc_resource_number
           );
         IF (lc_return_status <> 'S') THEN
            BEGIN
               FOR i IN 1 .. ln_msg_count
               LOOP
                  fnd_msg_pub.get (i, fnd_api.g_false, lc_msg_data, lc_msg_dummy);
                  lc_output := (TO_CHAR (i) || ': ' || lc_msg_data);
               END LOOP;
                  dbms_output.put_line('Error :'||lc_output);
            END;
            ROLLBACK;
         ELSE
         dbms_output.put_line('Resource imported sucessfully.');
         COMMIT;
         END IF;
    EXCEPTION
      WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE ('Error While importing the Resource :'||SQLERRM);
       ROLLBACK;
      END;
  END IF;
END;
/