Sample Granular API Code

This appendix provides sample code that uses the Oracle Trading Community Architecture granular APIs.

This chapter covers the following topics:

Sample Code - Demonstrating the APIs

Sample Code - Demonstrating the APIs

This section provides many sample codes to demonstrate how TCA API can be called to create and update entities. The sample code is provided for PL/SQL API as well as Java API. These are just sample code and do not attempt to cover any complete business case. These sample codes will help developers to quickly code required functionality using TCA API.

Create an Organization

PL/SQL Example

DECLARE
p_organization_rec     HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
x_return_status        VARCHAR2(2000);
x_msg_count            NUMBER;
x_msg_data             VARCHAR2(2000);
x_party_id             NUMBER;
x_party_number         VARCHAR2(2000);
x_profile_id           NUMBER;

BEGIN
p_organization_rec.organization_name := 'ABC Corporation';
p_organization_rec.created_by_module := 'TCA_EXAMPLE';

hz_party_v2pub.create_organization (
'T',
p_organization_rec,
x_return_status,
x_msg_count,
x_msg_data,
x_party_id,
x_party_number,
x_profile_id);

dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));

IF x_msg_count >1 THEN

FOR I IN 1..x_msg_count

LOOP
dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));

END LOOP;
END IF;
END;

Java Example

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.apps.ar.hz.v2api.HzPartyV2Pub;

class CreateOrganization {
  public static void main(String[] args) throws Exception {

    HzPartyV2Pub.OrganizationRec p_organization_rec = new HzPartyV2Pub.OrganizationRec();
    BigDecimal[]                  x_party_id = new BigDecimal[1];
    String[]                            x_party_number = new String[1];
    BigDecimal[]                 x_profile_id = new BigDecimal[1];

    String[]                            x_return_status = new String[1];
    BigDecimal[]                 x_msg_count = new BigDecimal[1];
    String[]                            x_msg_data = new String[1];

    try {
      Class.forName ("oracle.jdbc.driver.OracleDriver");
      OracleConnection conn = (OracleConnection)
        DriverManager.getConnection (
              "jdbc:oracle:thin:@ap103fam:1521:findv115",
              "apps", "apps");

      HzPartyV2Pub partyV2Pub = new HzPartyV2Pub();

      p_organization_rec.organization_name = "ABC Corporation";
      p_organization_rec.created_by_module = "TCA_EXAMPLE";

      partyV2Pub.createOrganization(
        conn
      , "T"
      , p_organization_rec
      , x_return_status
      , x_msg_count
      , x_msg_data
      , x_party_id
      , x_party_number
      , x_profile_id
      );

      System.out.println( "x_return_status = " + x_return_status[0] );
      System.out.println( "x_msg_count = " + x_msg_count[0] );
      System.out.println( "x_msg_data = " + x_msg_data[0] );

      if (x_msg_count[0].intValue() > 1) {
        OracleCallableStatement ocs = null;
        for (int i=0; i<x_msg_count[0].intValue(); i++) {
          ocs = (OracleCallableStatement)conn.prepareCall(
             "begin ? := fnd_msg_pub.get( p_encoded => 'F' ); end;");
          ocs.registerOutParameter(1, OracleTypes.VARCHAR);
          ocs.execute();
          System.out.println((i + 1) + ". " + ocs.getString(1));
        }
      }

       conn.close();
    } catch (ClassNotFoundException e) {
      System.out.println("Driver Not Found: " + e);
    } catch (SQLException e) {
      System.out.println("SQL Error." + e);
   }
  }
}

Out Parameters:

x_return_status = S
x_msg_count = 0
x_msg_data =
x_party_id = 1272023
x_party_number = 1268621
x_profile_id = 99782

Select Statement to Check Data:

select party_id, party_number, party_name, object_version_number, created_by_module
from hz_parties
where party_id = 1272023;

The following table provides information about this select statement.


PARTY_ID	PARTY_NUMBER	PARTY_NAME	OBJ_VER	CREATED_BY_MOD
1272023	1268621	ABC Corporation	1	TCA_EXAMPLE

select organization_profile_id, party_id, organization_name, object_version_number, created_by_module
from hz_organization_profiles 
where party_id = 1272023;

The following table provides information about this select statement.


ORG_PROFILE_ID	PARTY_ID	ORGANIZATION_NAME	OBJ_VER_NUM	CREATED_BY_M
99782	1272023	ABC Corporation	1	TCA_EXAMPLE

Similarly you can call hz_party_v2pub.create_person to create record in the HZ_PARTIES and HZ_PERSON_PROFILES tables and hz_party_v2pub.create_group to create record in the HZ_PARTIES table.

Create a Location

PL/SQL Example

DECLARE
p_location_rec       HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
x_location_id        NUMBER;
x_return_status      VARCHAR2(2000);
x_msg_count          NUMBER;
x_msg_data           VARCHAR2(2000);
BEGIN
p_location_rec.country := 'US';
p_location_rec.address1 := '300 Oracle Parkway';
p_location_rec.address2 := '13th Floor';
p_location_rec.city := 'Redwood Shores';
p_location_rec.postal_code := '94065';
p_location_rec.state := 'CA';
p_location_rec.created_by_module := 'TCA_EXAMPLE';

hz_location_v2pub.create_location(
                                                                                  'T',
                                                                                    p_location_rec,
                                                                                    x_location_id,
                                                                                    x_return_status,
                                                                                    x_msg_count,
                                                                                    x_msg_data);

dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));

IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;

END;

Java Example

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.apps.ar.hz.v2api.HzLocationV2Pub;

class CreateLocation {
  public static void main(String[] args) throws Exception {

    HzLocationV2Pub.LocationRec p_location_rec = new HzLocationV2Pub.LocationRec();
    BigDecimal[]           x_location_id = new BigDecimal[1];

    String[]               x_return_status = new String[1];
    BigDecimal[]           x_msg_count = new BigDecimal[1];
    String[]               x_msg_data = new String[1];

    try {
      Class.forName ("oracle.jdbc.driver.OracleDriver");
      OracleConnection conn = (OracleConnection)
        DriverManager.getConnection (
              "jdbc:oracle:thin:@ap103fam:1521:findv115",
              "apps", "apps");

      HzLocationV2Pub locationV2Pub = new HzLocationV2Pub();

      p_location_rec.country = "US";
      p_location_rec.address1 = "300 Oracle Parkway";
      p_location_rec.address2 = "13th Floor";
      p_location_rec.city = "Redwood Shores";
      p_location_rec.postal_code = "94065";
      p_location_rec.state = "CA";
      p_location_rec.created_by_module = "TCA_EXAMPLE";

      locationV2Pub.createLocation(
        conn
      , "T"
      , p_location_rec
      , x_location_id
      , x_return_status
      , x_msg_count
      , x_msg_data
      );

      System.out.println( "x_return_status = " + x_return_status[0] );
      System.out.println( "x_msg_count = " + x_msg_count[0] );
      System.out.println( "x_msg_data = " + x_msg_data[0] );

      if (x_msg_count[0].intValue() > 1) {
        OracleCallableStatement ocs = null;
        for (int i=0; i<x_msg_count[0].intValue(); i++) {
          ocs = (OracleCallableStatement)conn.prepareCall(
             "begin ? := fnd_msg_pub.get( p_encoded => 'F' ); end;");
          ocs.registerOutParameter(1, OracleTypes.VARCHAR);
          ocs.execute()
          System.out.println((i + 1) + ". " + ocs.getString(1));
        }
      }

       conn.close();
    } catch (ClassNotFoundException e) {
      System.out.println("Driver Not Found: " + e);
    } catch (SQLException e) {
      System.out.println("SQL Error." + e);
    }
  }
}

Out Parameters

x_location_id = 359086
x_return_status = S
x_msg_count  = 0
x_msg_data =

Select Statement to Check Data

select location_id, address1, address2, city, state, postal_code, country,
          object_version_number, created_by_module
from hz_locations
where location_id = 359086;

The following tables provide information about this select statement.


LOCATION_ID	ADDRESS1	ADDRESS2	CITY	STATE	POSTAL_CODE
359086	300 Oracle Parkway	13th Floor	Redwood Shores	CA	94065


COUNTRY	OBJECT_VERSION_NUMBER	CREATED_BY_MODULE
US	1	TCA_EXAMPLE

Create a Party Site

The following examples use the organizations and locations created in the previous examples.

PL/SQL Example

DECLARE
p_party_site_rec                                HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
x_party_site_id                                 NUMBER;
x_party_site_number                             VARCHAR2(2000);
x_return_status                                 VARCHAR2(2000);
x_msg_count                                     NUMBER;
x_msg_data                                      VARCHAR2(2000);
BEGIN
p_party_site_rec.party_id := 1272023;
p_party_site_rec.location_id := 359086;
p_party_site_rec.identifying_address_flag := 'Y';
p_party_site_rec.created_by_module := 'TCA_EXAMPLE';

hz_party_site_v2pub.create_party_site(
'T',
p_party_site_rec,
x_party_site_id,
x_party_site_number,
x_return_status,
x_msg_count,
x_msg_data);

dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));

IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;

END;

Java Example

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.apps.ar.hz.v2api.HzPartySiteV2Pub;

class CreatePartySite {
  public static void main(String[] args) throws Exception {

    HzPartySiteV2Pub.PartySiteRec p_party_site_rec = new HzPartySiteV2Pub.PartySiteRec();
    BigDecimal[]                 x_party_site_id = new BigDecimal[1];
    String[]                             x_party_site_number = new String[1];

    String[]                             x_return_status = new String[1];
    BigDecimal[]                 x_msg_count = new BigDecimal[1];
    String[]                             x_msg_data = new String[1];

    try {
      Class.forName ("oracle.jdbc.driver.OracleDriver");
      OracleConnection conn = (OracleConnection)
        DriverManager.getConnection (
              "jdbc:oracle:thin:@ap103fam:1521:findv115",
              "apps", "apps");

      HzPartySiteV2Pub partysiteV2Pub = new HzPartySiteV2Pub();

      p_party_site_rec.party_id = new BigDecimal("1272023");
      p_party_site_rec.location_id = new BigDecimal ("359086");
      p_party_site_rec.identifying_address_flag = "Y";
      p_party_site_rec.created_by_module = "TCA_EXAMPLE";

      partysiteV2Pub.createPartySite(
      conn
      , "T"
      , p_party_site_rec
      , x_party_site_id
      , x_party_site_number
      , x_return_status
      , x_msg_count
      , x_msg_data
      );

      System.out.println( "x_return_status = " + x_return_status[0] );
      System.out.println( "x_msg_count = " + x_msg_count[0] );
      System.out.println( "x_msg_data = " + x_msg_data[0] );

      if (x_msg_count[0].intValue() > 1) {
        OracleCallableStatement ocs = null;
        for (int i=0; i<x_msg_count[0].intValue(); i++) {
          ocs = (OracleCallableStatement)conn.prepareCall(
             "begin ? := fnd_msg_pub.get( p_encoded => 'F' ); end;");
          ocs.registerOutParameter(1, OracleTypes.VARCHAR);
          ocs.execute();
          System.out.println((i + 1) + ". " + ocs.getString(1));
        }
      }

      conn.close();
    } catch (ClassNotFoundException e) {
      System.out.println("Driver Not Found: " + e);
    } catch (SQLException e) {
      System.out.println("SQL Error." + e);
    }
  }
}

Out Parameters

x_party_site_id = 349327
x_party_site_number = 347092
x_return_status = S
x_msg_count = 0
x_msg_data =

Select Statement to Check Data

select party_site_id, party_id, location_id, party_site_number
from hz_party_sites
where party_site_id = 349327;

The following table provides information about this select statement.


PARTY_SITE_ID	PARTY_ID	LOCATION_ID	PARTY_SITE_NUMBER
349327	1272023	359086	347092

Because the party site is created with the identifying_address_flag column set to Y, location information is denormalized in the HZ_PARTIES table.

Select Statement to Check Data

The following table provides information about this select statement.


ADDRESS1	ADDRESS2	CITY	STATE	POSTAL_CODE	COUNTRY
300 Oracle Parkway	13th Floor	Redwood Shores	CA	940655	US

select address1, address2, city, state, postal_code, country
from hz_parties
where party_id = 1272023;

Create a Party Site Use

Using the same party site created in the previous example.

The following example uses the party site created in the previous example.

PL/SQL Example

DECLARE
p_party_site_use_rec         HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE;
x_party_site_use_id          NUMBER;
x_return_status              VARCHAR2(2000);
x_msg_count                  NUMBER;
x_msg_data                   VARCHAR2(2000);
BEGIN
p_party_site_use_rec.site_use_type := 'SHIP_TO';
p_party_site_use_rec.party_site_id := 349327;
p_party_site_use_rec.created_by_module := 'TCA_EXAMPLE';

hz_party_site_v2pub.create_party_site_use(
'T',
p_party_site_use_rec,
x_party_site_use_id,
x_return_status,
x_msg_count,
x_msg_data);

dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));

IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;

END;

Java Example

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.apps.ar.hz.v2api.HzPartySiteV2Pub;

class CreatePartySiteUse {
  public static void main(String[] args) throws Exception {

    HzPartySiteV2Pub.PartySiteUseRec p_party_site_use_rec = new HzPartySiteV2Pub.PartySiteUseRec();


    BigDecimal[]                 x_party_site_use_id = new BigDecimal[1];

    String[]                            x_return_status = new String[1];
    BigDecimal[]                 x_msg_count = new BigDecimal[1];
    String[]                           x_msg_data = new String[1];

    try {
      Class.forName ("oracle.jdbc.driver.OracleDriver");
      OracleConnection conn = (OracleConnection)
        DriverManager.getConnection (
              "jdbc:oracle:thin:@ap103fam:1521:findv115",
              "apps", "apps");

      HzPartySiteV2Pub partysiteV2Pub = new HzPartySiteV2Pub();

      p_party_site_use_rec.site_use_type = "SHIP_TO";
      p_party_site_use_rec.party_site_id = new BigDecimal("349327");
      p_party_site_use_rec.created_by_module = "TCA_EXAMPLE";

      partysiteV2Pub.createPartySiteUse(
        conn
      , "T"
      , p_party_site_use_rec
      , x_party_site_use_id
      , x_return_status
      , x_msg_count
      , x_msg_data
      );

      System.out.println( "x_return_status = " + x_return_status[0] );
      System.out.println( "x_msg_count = " + x_msg_count[0] );
      System.out.println( "x_msg_data = " + x_msg_data[0] );

      if (x_msg_count[0].intValue() > 1) {
        OracleCallableStatement ocs = null;
        for (int i=0; i<x_msg_count[0].intValue(); i++) {
          ocs = (OracleCallableStatement)conn.prepareCall(
             "begin ? := fnd_msg_pub.get( p_encoded => 'F' ); end;");
          ocs.registerOutParameter(1, OracleTypes.VARCHAR);
          ocs.execute();
          System.out.println((i + 1) + ". " + ocs.getString(1));
        }
      }

       conn.close();
    } catch (ClassNotFoundException e) {
      System.out.println("Driver Not Found: " + e);
    } catch (SQLException e) {
      System.out.println("SQL Error." + e);
    }
  }
}

Out Parameters

x_party_site_use_id = 13523754
x_return_status = S
x_msg_count = 0
x_msg_data =

Select Statement to Check Data

select party_site_use_id, party_site_id, site_use_type, primary_per_type
from hz_party_site_uses
where party_site_use_id = 13523754;

The following table provides information about this select statement.


PARTY_SITE_USE_ID	PARTY_SITE_ID	USE_TYPE	PRIMARY_PER_TYPE
13523754	349327	SHIP_TO	Y

Because the example above is the first SHIP_TO site use created for party ID 1272023, the primary_per_type attribute is set to Y.

Create a Contact Point (Phone)

This procedure creates a phone number for the same organization created in the first example.

PL/SQL Example

DECLARE
p_contact_point_rec    HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
p_edi_rec              HZ_CONTACT_POINT_V2PUB.EDI_REC_TYPE;
p_email_rec            HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE;
p_phone_rec            HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE;
p_telex_rec            HZ_CONTACT_POINT_V2PUB.TELEX_REC_TYPE;
p_web_rec              HZ_CONTACT_POINT_V2PUB.WEB_REC_TYPE;
x_return_status        VARCHAR2(2000);
x_msg_coun t           NUMBER;
x_msg_data             VARCHAR2(2000);
x_contact_point_id     NUMBER;
BEGIN
p_contact_point_rec.contact_point_type := 'PHONE';
p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
p_contact_point_rec.owner_table_id := '1272023';
p_contact_point_rec.primary_flag := 'Y';
p_contact_point_rec.contact_point_purpose := 'BUSINESS';
p_phone_rec.phone_area_code := '650';
p_phone_rec.phone_country_code := '1';
p_phone_rec.phone_number := '506-7000';
p_phone_rec.phone_line_type := 'GEN';
p_contact_point_rec.created_by_module := 'TCA_EXAMPLE';

hz_contact_point_v2pub.create_contact_point(
'T',
p_contact_point_rec,
p_edi_rec,
p_email_rec,
p_phone_rec,
p_telex_rec,
p_web_rec,
x_contact_point_id,
x_return_status,
x_msg_count,
x_msg_data);

dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));

IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;

END;

Java Example

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.driver.OracleConnection;'
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.apps.ar.hz.v2api.HzContactPointV2Pub;

class CreateContactPoint {
  public static void main(String[] args) throws Exception {

    HzContactPointV2Pub.ContactPointRec p_contact_point_rec = new HzContactPointV2Pub.ContactPointRec();
    HzContactPointV2Pub.EdiRec          p_edi_rec = new HzContactPointV2Pub.EdiRec();
    HzContactPointV2Pub.EmailRec      p_email_rec = new HzContactPointV2Pub.EmailRec();
    HzContactPointV2Pub.PhoneRec      p_phone_rec = new HzContactPointV2Pub.PhoneRec();
    HzContactPointV2Pub.TelexRec       p_telex_rec = new HzContactPointV2Pub.TelexRec();
    HzContactPointV2Pub.WebRec         p_web_rec = new HzContactPointV2Pub.WebRec();
    BigDecimal[]                 x_contact_point_id = new BigDecimal[1];
    String[]                             x_return_status = new String[1];
    BigDecimal[]                 x_msg_count = new BigDecimal[1];
    String[]                             x_msg_data = new String[1];

    try {
      Class.forName ("oracle.jdbc.driver.OracleDriver");
      OracleConnection conn = (OracleConnection)
        DriverManager.getConnection (
              "jdbc:oracle:thin:@ap103fam:1521:findv115",
              "apps", "apps");

      HzContactPointV2Pub contactpointV2Pub = new HzContactPointV2Pub();

      p_contact_point_rec.contact_point_type = "PHONE";
      p_contact_point_rec.owner_table_name = "HZ_PARTIES";
      p_contact_point_rec.owner_table_id = new BigDecimal("1272023");
      p_contact_point_rec.primary_flag = "Y";
      p_contact_point_rec.contact_point_purpose = "BUSINESS";
      p_phone_rec.phone_area_code = "650";
      p_phone_rec.phone_country_code = "1";
      p_phone_rec.phone_number = "506-7000";
      p_phone_rec.phone_line_type = "GEN";
      p_contact_point_rec.created_by_module = "TCA_EXAMPLE";

      contactpointV2Pub.createContactPoint(
        conn
        , "T"
        , p_contact_point_rec
        , p_edi_rec
        , p_email_rec
        , p_phone_rec
        , p_telex_rec
        , p_web_rec
        , x_contact_point_id
        , x_return_status
        , x_msg_count
        , x_msg_data
      );

      System.out.println( "x_return_status = " + x_return_status[0] );
      System.out.println( "x_msg_count = " + x_msg_count[0] );
      System.out.println( "x_msg_data = " + x_msg_data[0] );

      if (x_msg_count[0].intValue() > 1) {
        OracleCallableStatement ocs = null;
        for (int i=0; i<x_msg_count[0].intValue(); i++) {
          ocs = (OracleCallableStatement)conn.prepareCall(
             "begin ? := fnd_msg_pub.get( p_encoded => 'F' ); end;");
          ocs.registerOutParameter(1, OracleTypes.VARCHAR);
          ocs.execute();
          System.out.println((i + 1) + ". " + ocs.getString(1));
        }
      }

      conn.close();
    } catch (ClassNotFoundException e) {
      System.out.println("Driver Not Found: " + e);
    } catch (SQLException e) {
      System.out.println("SQL Error." + e);
    }
  }
}

Out Parameters

x_contact_point_id = 429523
x_return_status = S
x_msg_count = 0
x_msg_data =

Select Statement to Check Data

select contact_point_id, contact_point_type, owner_table_name, owner_table_id, primary_flag,
phone_area_code, phone_country_code, phone_number, phone_line_type, raw_phone_number
from hz_contact_points
where contact_point_id = 429523;

The following tables provide information about this select statement.


CONTACT_POINT_ID	CONTACT_POINT_TYPE	OWNER_TABLE_NAME
429523	PHONE	HZ_PARTIES


OWNER_TABLE_ID	PRIMARY_FLAG	PHONE_AREA_CODE
1272023	Y	650


PHONE_COUNTRY_CODE	PHONE_NUMBER	PHONE_LINE_TYPE
1	506-7000	GEN


RAW_PHONE_NUMBER
650-506-7000

Create an Organization Contact

This procedure assumes that a person with party ID 16077 exists in the database.

PL/SQL Example

DECLARE
p_org_contact_rec                                HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_ REC_TYPE;
x_org_contact_id                                 NUMBER;
x_party_rel_id                                   NUMBER;
x_party_id                                       NUMBER;
x_party_number                                   VARCHAR2(2000);
x_return_status                                  VARCHAR2(2000);
x_msg_count                                      NUMBER;
x_msg_data                                       VARCHAR2(2000);
BEGIN
p_org_contact_rec.department_code := 'ACCOUNTING';
p_org_contact_rec.job_title := 'ACCOUNTS OFFICER';
p_org_contact_rec.decision_maker_flag := 'Y';
p_org_contact_rec.job_title_code := 'APC';
p_org_contact_rec.created_by_module := 'TCA_EXAMPLE';
p_org_contact_rec.party_rel_rec.subject_id := 16077;
p_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
p_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.object_id := 1272023;
p_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
p_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
p_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
p_org_contact_rec.party_rel_rec.start_date := SYSDATE;

hz_party_contact_v2pub.create_org_contact(
'T',
p_org_contact_rec,
x_org_contact_id,
x_party_rel_id,
x_party_id,
x_party_number,
x_return_status,
x_msg_count,
x_msg_data);

dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));

IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;

END;

Java Example

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Timestamp;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.apps.ar.hz.v2api.HzPartyContactV2Pub;

class CreateOrgContact {
  public static void main(String[] args) throws Exception {



    HzPartyContactV2Pub.OrgContactRec p_org_contact_rec = new HzPartyContactV2Pub.OrgContactRec();
    BigDecimal[]                 x_org_contact_id = new BigDecimal[1];
    BigDecimal[]                 x_party_rel_id = new BigDecimal[1];
    BigDecimal[]                 x_party_id = new BigDecimal[1];
    String[]                     x_party_number  = new String[1];

    String[]                     x_return_status = new String[1];
    BigDecimal[]                 x_msg_count = new BigDecimal[1];
    String[]                     x_msg_data = new String[1];

    try {
      Class.forName ("oracle.jdbc.driver.OracleDriver");
      OracleConnection conn = (OracleConnection)
        DriverManager.getConnection (
              "jdbc:oracle:thin:@ap103fam:1521:findv115",
              "apps", "apps");

      HzPartyContactV2Pub partycontactV2Pub = new HzPartyContactV2Pub();

      p_org_contact_rec.department_code = "ACCOUNTING";
      p_org_contact_rec.job_title = "ACCOUNTS OFFICER";
      p_org_contact_rec.decision_maker_flag = "Y";
      p_org_contact_rec.job_title_code = "APC";
      p_org_contact_rec.created_by_module = "TCA_EXAMPLE";
      p_org_contact_rec.party_rel_rec.subject_id = new BigDecimal("16077");
      p_org_contact_rec.party_rel_rec.subject_type = "PERSON";
      p_org_contact_rec.party_rel_rec.subject_table_name = "HZ_PARTIES";
      p_org_contact_rec.party_rel_rec.object_id = new BigDecimal("1272023");
      p_org_contact_rec.party_rel_rec.object_type = "ORGANIZATION";
      p_org_contact_rec.party_rel_rec.object_table_name = "HZ_PARTIES";
      p_org_contact_rec.party_rel_rec.relationship_code = "CONTACT_OF";
      p_org_contact_rec.party_rel_rec.relationship_type = "CONTACT";
      p_org_contact_rec.party_rel_rec.start_date = Timestamp.valueOf("2001-09-26 00:00:00.0") ;

      partycontactV2Pub.createOrgContact(
        conn
  , "T"
        , p_org_contact_rec
        , x_org_contact_id
        , x_party_rel_id
        , x_party_id
        , x_party_number
        , x_return_status
        , x_msg_count
        , x_msg_data
      );

      System.out.println( "x_return_status = " + x_return_status[0] );
      System.out.println( "x_msg_count = " + x_msg_count[0] );
      System.out.println( "x_msg_data = " + x_msg_data[0] );

      if (x_msg_count[0].intValue() > 1) {
        OracleCallableStatement ocs = null;
        for (int i=0; i<x_msg_count[0].intValue(); i++) {
          ocs = (OracleCallableStatement)conn.prepareCall(
             "begin ? = fnd_msg_pub.get( p_encoded => 'F' ); end;");
          ocs.registerOutParameter(1, OracleTypes.VARCHAR);
          ocs.execute();
          System.out.println((i + 1) + ". " + ocs.getString(1));
        }
      }

       conn.close();
    } catch (ClassNotFoundException e) {
      System.out.println("Driver Not Found: " + e);
    } catch (SQLException e) {
      System.out.println("SQL Error." + e);
    }
  }
}

Out Parameters

x_org_contact_id = 411650
x_party_rel_id = 10091074
x_party_id = 1272029
x_party_number = 1268627
x_return_status = S
x_msg_count = 0
x_msg_data =

Select Statement to Check Data

Organization contact record is created.

select org_contact_id, party_relationship_id, department_code, title, job_title, job_title_code
from hz_org_contacts
where org_contact_id =   411650;

The following tables provide information about this select statement.


ORG_CONTACT_ID	PARTY_RELATIONSHIP_ID	DEPARTMENT_CODE	TITLE
411650	10091074	ACCOUNTING	DR.


JOB_TITLE	JOB_TITLE_CODE
ACCOUNTS OFFICER	APC

Two relationship records are created.

select relationship_id, subject_id, subject_type, subject_table_name,
          object_id, object_type, object_table_name, directional_flag
          relationship_type, relationship_code, party_id
from hz_relationships
where relationship_id = 10091074;

The following tables provide information about this select statement.


RELATIONSHIP_ID	SUBJECT_ID	SUBJECT_TYPE	SUBJECT_TABLE_NAME
10091074	16077	PERSON	HZ_PARTIES
10091074	1272023	ORGANIZATION	HZ_PARTIES


OBJECT_ID	OBJECT_TYPE	OBJECT_TABLE_NAME	DIRECTIONAL_FLAG
1272023	ORGANIZATION	HZ_PARTIES	F
16077	PERSON	HZ_PARTIES	B


RELATIONSHIP_TYPE	RELATIONSHIP_CODE	PARTY_ID
CONTACT	CONTACT_OF	1272029
CONTACT	CONTACT	1272029

A party record is created.

select party_id, party_number, party_name, party_type
from hz_parties
where party_id = 1272029;

The following table provides information about this select statement.


PARTY_ID	PARTY_NUMBER	PARTY_NAME	PARTY_TYPE
1272029	1268627	John Doe -ABC Corporation-1268627	PARTY_RELATIONSHIP

Update a Location Record

This procedure updates the new location and sets address2 to null and county to 'San Mateo'.

PL/SQL Example

To update an attribute to null in the PL/SQL API, you must set the attribute's value to FND_API.G_MISS_XXX.

DECLARE


p_location_rec                                   HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
p_object_version_number                          NUMBER;
x_return_status                                  VARCHAR2(2000);
x_msg_count                                      NUMBER;
x_msg_data                                       VARCHAR2(2000);
BEGIN
p_location_rec.location_id := 359086;
p_location_rec.address2 := FND_API.G_MISS_CHAR;
p_location_rec.county := 'San Mateo';
                   - - get the object version number of the current record
                   select object_version_number
                   into p_object_version_number
                   from hz_locations
                  where location_id = 359086;
hz_location_v2pub.update_location(
'T',
p_location_rec,
p_object_version_number,
x_return_status,
x_msg_count,
x_msg_data);

dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));

IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;
END;

Java Example

To update an attribute to null, you must set the attribute's value to null. This procedure is different from that of the PL/SQL API.

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.apps.ar.hz.v2api.HzLocationV2Pub;
import oracle.apps.ar.hz.v2api.HzConstant;

class UpdateLocation {
  public static void main(String[] args) throws Exception {

    HzLocationV2Pub.LocationRec p_location_rec = new HzLocationV2Pub.LocationRec();

    String[]                             x_return_status = new String[1];
    BigDecimal[]                         x_msg_count = new BigDecimal[1];
    String[]                             x_msg_data = new String[1];
    BigDecimal[]                         p_object_version_number = new BigDecimal[1];

    try {
      Class.forName ("oracle.jdbc.driver.OracleDriver");
      OracleConnection conn = (OracleConnection)
        DriverManager.getConnection (
              "jdbc:oracle:thin:@ap103fam:1521:findv115",
              "apps", "apps");

      HzLocationV2Pub locationV2Pub = new HzLocationV2Pub();

      p_location_rec.location_id = new BigDecimal("359086");
      p_location_rec.address2 = null;
      p_location_rec.county = "San Mateo";

      -- please note, you should pass the current value of object
      -- version number column for this record
      p_object_version_number[0] = new BigDecimal("1");

      locationV2Pub.updateLocation(
        conn
      , "T"
      , p_location_rec
      , p_object_version_number
      , x_return_status
      , x_msg_count
      , x_msg_data
      );

      System.out.println( "x_return_status = " + x_return_status[0] );
      System.out.println( "x_msg_count = " + x_msg_count[0] );
      System.out.println( "x_msg_data = " + x_msg_data[0] );

      if (x_msg_count[0].intValue() > 1) {
        OracleCallableStatement ocs = null;
        for (int i=0; i<x_msg_count[0].intValue(); i++) {
          ocs = (OracleCallableStatement)conn.prepareCall(
             "begin ? := fnd_msg_pub.get( p_encoded => 'F' ); end;");
          ocs.registerOutParameter(1, OracleTypes.VARCHAR);
          ocs.execute();
          System.out.println((i + 1) + ". " + ocs.getString(1));
        }
      }

      conn.close();
    } catch (ClassNotFoundException e) {
      System.out.println("Driver Not Found: " + e);
    } catch (SQLException e) {
      System.out.println("SQL Error." + e);
    }
  }
}

Select Statement to Check Data

select address1, address2, county, object_version_number
from hz_locations
where location_id = 359086;

The following table provides information about this select statement.


ADDRESS1	ADDRESS2	COUNTY	OBJECT_VERSION_NUMBER
300 Oracle Parkway		San Mateo	2

Create a Customer Account

PL/SQL Example

DECLARE
p_cust_account_rec                        HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
p_person_rec                              HZ_PARTY_V2PUB.PERSON_REC_TYPE;
p_customer_profile_rec                    HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE REC_TYPE;
x_cust_account_id                         NUMBER;
x_account_number                          VARCHAR2(2000);
x_party_id                                NUMBER;
x_party_number                            VARCHAR2(2000);
x_profile_id                              NUMBER;
x_return_status                           VARCHAR2(2000);
x_msg_count                               NUMBER;
x_msg_data                                VARCHAR2(2000);
BEGIN
p_cust_account_rec.account_name := 'John''s A/c';
p_cust_account_rec.created_by_module := 'TCA_EXAMPLE';
p_person_rec.person_first_name := 'John';
p_person_rec.person_last_name := 'Smith';

hz_cust_account_v2pub.create_cust_account(
'T',
p_cust_account_rec,
p_person_rec,
p_customer_profile_rec,
'F',
x_cust_account_id,
x_account_number,
x_party_id,
x_party_number,
x_profile_id,
x_return_status,
x_msg_count,
x_msg_data);

dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));

IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;

END;

Java Example

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.apps.ar.hz.v2api.HzCustAccountV2Pub;
import oracle.apps.ar.hz.v2api.HzPartyV2Pub;
import oracle.apps.ar.hz.v2api.HzCustomerProfileV2Pub;

class CreateCustAccount {
  public static void main(String[] args) throws Exception {

    HzCustAccountV2Pub.CustAccountRec p_cust_account_rec = new HzCustAccountV2Pub.CustAccountRec();
    HzPartyV2Pub.PersonRec p_person_rec = new HzPartyV2Pub.PersonRec();
    HzCustomerProfileV2Pub.CustomerProfileRec p_customer_profile_rec = new HzCustomerProfileV2Pub.CustomerProfileRec();
    BigDecimal[]                 x_cust_account_id = new BigDecimal[1];
    String[]                            x_account_number = new String[1];
    BigDecimal[]                 x_party_id = new BigDecimal[1];
    String[]                            x_party_number = new String[1];
    BigDecimal[]                 x_profile_id = new BigDecimal[1];

    String[]                            x_return_status = new String[1];
    BigDecimal[]                 x_msg_count = new BigDecimal[1];
    String[]                            x_msg_data = new String[1];

    try {
      Class.forName ("oracle.jdbc.driver.OracleDriver");
      OracleConnection conn = (OracleConnection)
        DriverManager.getConnection (
              "jdbc:oracle:thin:@ap103fam:1521:findv115",
              "apps", "apps");

      HzCustAccountV2Pub custaccountV2Pub = new HzCustAccountV2Pub();

      p_cust_account_rec.account_name = "John A/c";
      p_person_rec.person_first_name = "John";
      p_person_rec.person_last_name = "Smith";
      p_cust_account_rec.created_by_module = "TCA_EXAMPLE";

      custaccountV2Pub.createCustAccount(
        conn
        , "T"
        , p_cust_account_rec
        , p_person_rec
        , p_customer_profile_rec
        , "F"
        , x_cust_account_id
        , x_account_number
        , x_party_id
        , x_party_number
        , x_profile_id
        , x_return_status
        , x_msg_count
        , x_msg_data
      );

      System.out.println( "x_return_status = " + x_return_status[0] );
      System.out.println( "x_msg_count = " + x_msg_count[0] );
      System.out.println( "x_msg_data = " + x_msg_data[0] );

      if (x_msg_count[0].intValue() > 1) {
        OracleCallableStatement ocs = null;
        for (int i=0; i<x_msg_count[0].intValue(); i++) {
          ocs = (OracleCallableStatement)conn.prepareCall(
             "begin ? := fnd_msg_pub.get( p_encoded => 'F' ); end;");
          ocs.registerOutParameter(1, OracleTypes.VARCHAR);
          ocs.execute();
          System.out.println((i + 1) + ". " + ocs.getString(1));
        }
      }
      conn.close();
    } catch (ClassNotFoundException e) {
      System.out.println("Driver Not Found: " + e);
    } catch (SQLException e) {
      System.out.println("SQL Error." + e);
    }
  }
}

Out Parameters

x_cust_account_id = 3472
x_account_number = 1745
x_party_id = 3575
x_party_number = 1647
x_profile_id = 1483
x_return_status = S
x_msg_count = 0
x_msg_data =

Select Statement to Check Data

Created a record in the HZ_CUST_ACCOUNTS table.

select cust_account_id, party_id, account_number, account_name
from hz_cust_accounts
where cust_account_id = 3472;

The following table provides information about this select statement.


CUST_ACCOUNT_ID	PARTY_ID	ACCOUNT_NUMBER	ACCOUNT_NAME
3472	3575	1745	John's A/c

Created a record in the HZ_PARTIES table

select party_id, party_number, party_name, party_type
from hz_parties
where party_id = 3575;

The following table provides information about this select statement.


PARTY_ID	PARTY_NUMBER	PARTY_NAME	PARTY_TYPE
3575	1647	John Smith	PERSON

Created a record in hz_person_profiles table.

select person_profile_id, party_id, person_first_name, person_last_name
from hz_person_profiles
where party_id = 3575;

The following table provides information about this select statement.


PERSON_PROFILE_ID	PARTY_ID	PERSON_FIRST_NAME	PERSON_LAST_NAME
1483	3575	John	Smith

Created a record in hz_customer_profiles.

select cust_account_profile_id, cust_account_id, profile_class_id, collector_id
from hz_customer_profiles
where cust_account_id = 3472;

The following table provides information about this select statement.


CUST_ACCOUNT_PROFILE_ID	CUST_ACCOUNT_ID	PROFILE_CLASS_ID	COLLECTOR_ID
3691	3472	0	1000

Create a Customer Account Site

This procedure creates a site for the account created in previous example, using an existing site.

PL/SQL Example

DECLARE
p_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
x_return_status              VARCHAR2(2000);
x_msg_count                  NUMBER;
x_msg_data                   VARCHAR2(2000);
x_cust_acct_site_id          NUMBER;
BEGIN
p_cust_acct_site_rec.cust_account_id := 3472;
p_cust_acct_site_rec.party_site_id := 1024;
p_cust_acct_site_rec.language := 'US';
p_cust_acct_site_rec.created_by_module := 'TCA-EXAMPLE';

hz_cust_account_site_v2pub.create_cust_acct_site(
'T',
p_cust_acct_site_rec,
x_cust_acct_site_id,
x_return_status,
x_msg_count,
x_msg_data);

dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));

IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;

END;

Java Example

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.apps.ar.hz.v2api.HzCustAccountSiteV2Pub;

class CreateCustAcctSite {
  public static void main(String[] args) throws Exception {

    HzCustAccountSiteV2Pub.CustAcctSiteRec p_cust_acct_site_rec = new HzCustAccountSiteV2Pub.CustAcctSiteRec();
    BigDecimal[]                 x_cust_account_site_id = new BigDecimal[1];

    String[]                            x_return_status = new String[1];
    BigDecimal[]                 x_msg_count = new BigDecimal[1];
    String[]                            x_msg_data = new String[1];


    try {
      Class.forName ("oracle.jdbc.driver.OracleDriver");
      OracleConnection conn = (OracleConnection)
        DriverManager.getConnection (
              "jdbc:oracle:thin:@ap103fam:1521:findv115",
              "apps", "apps");

      HzCustAccountSiteV2Pub custaccountsiteV2Pub = new HzCustAccountSiteV2Pub();

      p_cust_acct_site_rec.cust_account_id = new BigDecimal("3472");
      p_cust_acct_site_rec.party_site_id = new BigDecimal("1024");
      p_cust_acct_site_rec.language = "US";
      p_cust_acct_site_rec.created_by_module = "TCA_EXAMPLE";

      custaccountsiteV2Pub.createCustAcctSite(
        conn
        , "T"
        , p_cust_acct_site_rec
        , x_cust_account_site_id
        , x_return_status
        , x_msg_count
        , x_msg_data
      );

      System.out.println( "x_return_status = " + x_return_status[0] );
      System.out.println( "x_msg_count = " + x_msg_count[0] );
      System.out.println( "x_msg_data = " + x_msg_data[0] );

      if (x_msg_count[0].intValue() > 1) {
        OracleCallableStatement ocs = null;
        for (int i=0; i<x_msg_count[0].intValue(); i++) {
          ocs = (OracleCallableStatement)conn.prepareCall(
             "begin ? := fnd_msg_pub.get( p_encoded => 'F' ); end;");
          ocs.registerOutParameter(1, OracleTypes.VARCHAR);
          ocs.execute();
          System.out.println((i + 1) + ". " + ocs.getString(1));
        }
      }

      conn.close();
    } catch (ClassNotFoundException e) {
      System.out.println("Driver Not Found: " + e);
    } catch (SQLException e) {
      System.out.println("SQL Error." + e);
    }
  }
}

Out parameters

x_return_status = S
x_msg_count = 0
x_msg_data =
x_cust_acct_site_id = 3580

Select statement to check data

select cust_acct_site_id, cust_account_id, party_site_id, org_id
from hz_cust_acct_sites_all
where cust_acct_site_id = 3580;

The following table provides information about this select statement.


CUST_ACCT_SITE_ID	CUST_ACCOUNT_ID	PARTY_SITE_ID	ORG_ID
3580	3472	1024	204

Create a Customer Account Site Use

This procedure creates a customer account site use for the customer account site created in the previous example.

PL/SQL Example

DECLARE
p_cust_site_use_rec     HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE;
p_customer_profile_rec  HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
x_site_use_id           NUMBER;
x_return_status         VARCHAR2(2000);
x_msg_count             NUMBER;
x_msg_data              VARCHAR2(2000);
BEGIN
p_cust_site_use_rec.cust_acct_site_id := 3580;
p_cust_site_use_rec.site_use_code := 'INV';
p_cust_site_use_rec.location := 'TCA';
p_cust_site_use_rec.created_by_module := 'TCA_EXAMPLE';
hz_cust_account_site_v2pub.create_cust_site_use(
'T',
p_cust_site_use_rec,
p_customer_profile_rec,
'',
'',
x_site_use_id,
x_return_status,
x_msg_count,
x_msg_data);

dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));

IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;
END;

Java Example

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.apps.ar.hz.v2api.HzCustAccountSiteV2Pub;
import oracle.apps.ar.hz.v2api.HzCustomerProfileV2Pub;;

class CreateCustSiteUse {
  public static void main(String[] args) throws Exception {

    HzCustAccountSiteV2Pub.CustSiteUseRec p_cust_site_use_rec = new HzCustAccountSiteV2Pub.CustSiteUseRec();
    HzCustomerProfileV2Pub.CustomerProfileRec p_customer_profile_rec = new HzCustomerProfileV2Pub.CustomerProfileRec();
    BigDecimal[]                         x_site_use_id = new BigDecimal[1];

    String[]                             x_return_status = new String[1];
    BigDecimal[]                         x_msg_count = new BigDecimal[1];
    String[]                             x_msg_data = new String[1];

    try {
      Class.forName ("oracle.jdbc.driver.OracleDriver");
      OracleConnection conn = (OracleConnection)
        DriverManager.getConnection (
              "jdbc:oracle:thin:@ap103fam:1521:findv115",
              "apps", "apps");

      HzCustAccountSiteV2Pub custaccountsiteV2Pub = new HzCustAccountSiteV2Pub();

      p_cust_site_use_rec.cust_acct_site_id = new BigDecimal("3580");
      p_cust_site_use_rec.site_use_code = "INV";
      p_cust_site_use_rec.location = "TCA";
      p_cust_site_use_rec.created_by_module = "TCA_EXAMPLE";

      custaccountsiteV2Pub.createCustSiteUse(
        conn
        , "T"
        , p_cust_site_use_rec
        , p_customer_profile_rec
        , "T"
        , "T"
        , x_site_use_id
        , x_return_status
        , x_msg_count
        , x_msg_data
      );



      System.out.println( "x_return_status = " + x_return_status[0] );
      System.out.println( "x_msg_count = " + x_msg_count[0] );
      System.out.println( "x_msg_data = " + x_msg_data[0] );

      if (x_msg_count[0].intValue() > 1) {
        OracleCallableStatement ocs = null;
        for (int i=0; i<x_msg_count[0].intValue(); i++) {
          ocs = (OracleCallableStatement)conn.prepareCall(
             "begin ? := fnd_msg_pub.get( p_encoded => 'F' ); end;");
          ocs.registerOutParameter(1, OracleTypes.VARCHAR);
          ocs.execute();
          System.out.println((i + 1) + ". " + ocs.getString(1));
        }
      }

      conn.close();
    } catch (ClassNotFoundException e) {
      System.out.println("Driver Not Found: " + e);
    } catch (SQLException e) {
      System.out.println("SQL Error." + e);
    }
  }
}

Out Parameters

x_site_use_id = 3756
x_return_status = S
x_msg_count = 0
x_msg_data =

Select Statement to Check Data

select site_use_id, cust_acct_site_id, site_use_code, location, org_id
from hz_cust_site_uses_all
where site_use_id = 3756;

The following table provides information about this select statement.


SITE_USE_ID	CUST_ACCT_SITE_ID	SITE_USE_CODE	LOCATION	ORG_ID
3756	3580	INV	TCA	204

Update a Customer Account Record

This example updates the customer account record that was created earlier by setting the account_name column to null and the customer_type column to R.

PL/SQL Example

To update an attribute to null in the PL/SQL API, you need to set the attribute's value to FND_API.G_MISS_XXX.

DECLARE

p_cust_account_rec                           HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
p_object_version_number                      NUMBER;
x_return_status                              VARCHAR2(2000);
x_msg_count                                  NUMBER;
x_msg_data                                   VARCHAR2(2000);
BEGIN
p_cust_account_rec.cust_account_id := 3472;
p_cust_account_rec.customer_type := 'R';
p_cust_account_rec.account_name := FND_API.G_MISS_CHAR;

-- get the object version number of the current record
select object_version_number
into p_object_version_number
from hz_cust_accounts
where cust_account_id = 3472;

hz_cust_account_v2pub.update_cust_account(
'T',
p_cust_account_rec,
p_object_version_number,
x_return_status,
x_msg_count,
x_msg_data);

dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));

IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;

 END;

Java Example

You must set the attribute's value to null to update an attribute to null. This procedure is different from that of the PL/SQL API.

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.jdbc.driver.OracleResultSet;
import oracle.apps.ar.hz.v2api.HzCustAccountV2Pub;

class UpdateCustAccount {
  public static void main(String[] args) throws Exception {

    HzCustAccountV2Pub.CustAccountRec p_cust_account_rec = new HzCustAccountV2Pub.CustAccountRec();

    String[]                            x_return_status = new String[1];
    BigDecimal[]                        x_msg_count = new BigDecimal[1];
    String[]                            x_msg_data = new String[1];
    BigDecimal[]                        p_object_version_number = new BigDecimal[1];

    try {
      Class.forName ("oracle.jdbc.driver.OracleDriver");
      OracleConnection conn = (OracleConnection)
        DriverManager.getConnection (
              "jdbc:oracle:thin:@ap103fam:1521:findv115",
              "apps", "apps");

      HzCustAccountV2Pub custAccountV2Pub = new HzCustAccountV2Pub();

      p_cust_account_rec.cust_account_id = new BigDecimal("3472");
      p_cust_account_rec.customer_type = "R";
      p_cust_account_rec.account_name = null;

      -- please note, you should pass the current value of object
      -- version number column for this record
       p_object_version_number [0]= new BigDecimal("1");

      custAccountV2Pub.updateCustAccount(
        conn
      , "F"
      , p_cust_account_rec
      , p_object_version_number
      , x_return_status
      , x_msg_count
      , x_msg_data
      );

      System.out.println( "x_return_status = " + x_return_status[0] );
      System.out.println( "x_msg_count = " + x_msg_count[0] );
      System.out.println( "x_msg_data = " + x_msg_data[0] );

      if (x_msg_count[0].intValue() > 1) {
        OracleCallableStatement ocs = null;
        for (int i=0; i<x_msg_count[0].intValue(); i++) {
          ocs = (OracleCallableStatement)conn.prepareCall(
             "begin ? := fnd_msg_pub.get( p_encoded => 'F' ); end;");
          ocs.registerOutParameter(1, OracleTypes.VARCHAR);
          ocs.execute();
          System.out.println((i + 1) + ". " + ocs.getString(1));
        }
      }

      conn.close();
    } catch (ClassNotFoundException e) {
      System.out.println("Driver Not Found: " + e);
    } catch (SQLException e) {
      System.out.println("SQL Error." + e);
    }
  }
}

Out Parameters

p_object_version_number = 2
x_return_status = S
x_msg_count = 0
x_msg_data =

Select Statement to Check Data

select cust_account_id, customer_type, account_name
from hz_cust_accounts
where cust_account_id = 3472;

The following table provides information about this select statement.


CUST_ACCOUNT_ID	CUSTOMER_TYPE	ACCOUNT_NAME
3472	R

Create an Organization Profile Extensions Record

PL/SQL Example

This example involves processing two attribute groups.

DECLARE
    ------------------------------
    --  Declare Primary Keys    --
    ------------------------------
  l_org_profile_id         NUMBER;

    ---------------------------------------------------
    --  Declare row and data objects and metadata    --
    ---------------------------------------------------
    l_user_attr_data_table   EGO_USER_ATTR_DATA_TABLE;
    l_user_attr_row_table    EGO_USER_ATTR_ROW_TABLE;

    l_application_id        NUMBER;
    l_attr_group_type       VARCHAR2(40);
    l_attr_group1_name      VARCHAR2(30);
    l_attr_group2_name      VARCHAR2(30);

    ---------------------------------------
    -- Declare various testing variables --
    ---------------------------------------
    l_start_time             DATE;
    l_end_time               DATE;
    x_failed_row_id_list     VARCHAR2(10000);
    x_return_status          VARCHAR2(1);
    x_errorcode              NUMBER;
    x_msg_count              NUMBER;
    x_msg_data               VARCHAR2(1000);

BEGIN

    --------------------------------------------------
    --  Initialize variables for this sample run    --
    --------------------------------------------------

    l_org_profile_id    := 204;
    l_application_id    := 222;

    l_attr_group_type   := 'HZ_ORG_PROFILES_GROUP';

    l_attr_group1_name  := 'TCA_SAMPLE_GROUP';

    l_attr_group2_name  := 'TCA_SAMPLE_GROUP2';

    -------------------------------------------------------
    -- Create three row objects. (If any attribute       --
    -- groups were associated at the Revision level,     --
    -- pass the appropriate Revision ID as DATA_LEVEL_1) --
    -------------------------------------------------------
    l_user_attr_row_table := 
      EGO_USER_ATTR_ROW_TABLE(
        EGO_USER_ATTR_ROW_OBJ(
          1                                    --ROW_IDENTIFIER - identifies the row number within the table
         ,null                                 --ATTR_GROUP_ID
         ,l_application_id               --ATTR_GROUP_APP_ID
         ,l_attr_group_type           --ATTR_GROUP_TYPE
         ,l_attr_group1_name       --ATTR_GROUP_NAME
         ,null                                 --DATA_LEVEL_1 
         ,null                                 --DATA_LEVEL_2
         ,null                                 --DATA_LEVEL_3
         ,EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE) --TRANSACTION_TYPE ( This control the Mode(CREATE/UPDATE/DELETE)
       ,EGO_USER_ATTR_ROW_OBJ(
         2
        ,null
        ,l_application_id
        ,l_attr_group_type
        ,l_attr_group2_name
        ,null
        ,null
        ,null
        ,EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE)
      );

    -- NOTE: OTHER ALLOWED MODES
    ------------------------------
    -- G_CREATE_MODE
    -- G_UPDATE_MODE
    -- G_DELETE_MODE
    -- G_SYNC_MODE

    ------------------------------------------------------
    -- Create a data object for each attribute value to --
    -- process in each row.                             --
    ------------------------------------------------------
    l_user_attr_data_table := 
      EGO_USER_ATTR_DATA_TABLE(
        EGO_USER_ATTR_DATA_OBJ(
           1                                --ROW_IDENTIFIER
          ,'Attribute1'                 --ATTR_NAME      ( Internal Name )
          ,'Test Data Updated'   --ATTR_VALUE_STR ( Value for Attribute1 )
          ,null                             --ATTR_VALUE_NUM 
          ,null                             --ATTR_VALUE_DATE 
          ,null                             --ATTR_DISP_VALUE
          ,null                             --ATTR_UNIT_OF_MEASURE
          ,null                             --USER_ROW_IDENTIFIER 
        ),
        EGO_USER_ATTR_DATA_OBJ(
           1
          ,'Attribute2' 
          ,null
          ,100
          ,null
          ,null
          ,null
          ,null
        ),
        EGO_USER_ATTR_DATA_OBJ(
           2
          ,'Attribute1' 
          ,'ACME Materials UPDATED'
          ,null
          ,null
          ,null
          ,null
          ,null
        ),
        EGO_USER_ATTR_DATA_OBJ(
           2
          ,'Attribute2'
          ,'Test Data for Attribute group 2'
          ,null
          ,null
          ,null
          ,null
          ,null
        )
      );

    l_start_time := SYSDATE;

    ------------------------------------------------------------
    -- In the following call, several parameters were left    --
    -- to be defaulted; for more details and the complete     --
    -- parameter list consult the EGO_ITEM_PUB documentation  --
    ------------------------------------------------------------
    HZ_EXTENSIBILITY_PUB.Process_Organization_Record
    (
      p_api_version                   => 1.0
     ,p_org_profile_id               => l_org_profile_id
     ,p_attributes_row_table     => l_user_attr_row_table
     ,p_attributes_data_table     => l_user_attr_data_table
     ,p_debug_level                  => 3
     ,p_commit                        => FND_API.G_TRUE
     ,x_failed_row_id_list         => x_failed_row_id_list
     ,x_return_status                 => x_return_status
     ,x_errorcode                     => x_errorcode
     ,x_msg_count                     => x_msg_count
     ,x_msg_data                      => x_msg_data
    );

    l_end_time := SYSDATE;

    DBMS_OUTPUT.Put_Line('After Process_User_Attrs_For_Item, x_return_status is:
'||x_return_status||', x_msg_count is: '||x_msg_count||' and x_msg_data is: '||x_msg_data);


    IF (LENGTH(x_failed_row_id_list) > 0) THEN
      DBMS_OUTPUT.Put_Line('List of rows that failed: '||x_failed_row_id_list);

      DECLARE 

        l_errors_tbl         ERROR_HANDLER.Error_Tbl_Type;

      BEGIN

        ERROR_HANDLER.Get_Message_List(l_errors_tbl);

        FOR i IN 1..l_errors_tbl.COUNT
        LOOP
          DBMS_OUTPUT.Put_Line('Message:   '||l_errors_tbl(i).message_text);
          DBMS_OUTPUT.Put_Line('Msg Type:  '||l_errors_tbl(i).message_type);
        END LOOP;

      END;

    END IF;

    DBMS_OUTPUT.Put_Line('========== Performance ==========');
    DBMS_OUTPUT.Put_Line('The call started at:
         '||TO_CHAR(l_start_time,EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT));
    DBMS_OUTPUT.Put_Line('The call ended at:      '||TO_CHAR(l_end_time,
        EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT));
    DBMS_OUTPUT.Put_Line('Elapsed time in seconds: '||TO_CHAR((l_end_time -
        l_start_time)*86400));

END;

Run the following query and verify the data.

SELECT * FROM HZ_ORG_PROFILES_EXT_VL WHERE ORGANIZATION_PROFILE_ID = :1