This chapter covers the following topics:
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.
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;
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);
}
}
}
x_return_status = S x_msg_count = 0 x_msg_data = x_party_id = 1272023 x_party_number = 1268621 x_profile_id = 99782
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.
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;
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);
}
}
}
x_location_id = 359086 x_return_status = S x_msg_count = 0 x_msg_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 |
The following examples use the organizations and locations created in the previous examples.
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;
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);
}
}
}
x_party_site_id = 349327 x_party_site_number = 347092 x_return_status = S x_msg_count = 0 x_msg_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.
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;
Using the same party site created in the previous example.
The following example uses the party site created in the previous 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;
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);
}
}
}
x_party_site_use_id = 13523754 x_return_status = S x_msg_count = 0 x_msg_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.
This procedure creates a phone number for the same organization created in the first 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;
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);
}
}
}
x_contact_point_id = 429523 x_return_status = S x_msg_count = 0 x_msg_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 |
This procedure assumes that a person with party ID 16077 exists in the database.
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;
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);
}
}
}
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 =
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 |
This procedure updates the new location and sets address2 to null and county to 'San Mateo'.
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;
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 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 |
The following sample code explains the method of passing parameter for updating Geometry column value in HZ_LOCATIONS table.
DECLARE x_geo mdsys.sdo_geometry ; l_longitude number; l_latitude number; p_location_rec hz_location_v2pub.location_rec_type ; BEGIN l_longitude := &longitude_value_of_the_location; l_latitude := &latitude_value_of_the_location; x_geo := MDSYS.SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(l_longitude, l_latitude, NULL), NULL, NULL); -- Assign x_geo to geometry column in p_location_rec record_type if (l_longitude is not null AND l_latitude is not null AND x_geo is not null ) THEN p_location_rec.geometry := x_geo ; end if; -- Call create / update location API as ususal -- hz_location_v2pub.create_location ( p_location_rec ....); OR -- hz_location_v2pub.update_location ( p_location_rec ....); END; /
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;
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);
}
}
}
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 =
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 |
This procedure creates a site for the account created in previous example, using an existing site.
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;
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);
}
}
}
x_return_status = S x_msg_count = 0 x_msg_data = x_cust_acct_site_id = 3580
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 |
This procedure creates a customer account site use for the customer account site created in the previous 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;
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);
}
}
}
x_site_use_id = 3756 x_return_status = S x_msg_count = 0 x_msg_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 |
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.
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;
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);
}
}
}
p_object_version_number = 2 x_return_status = S x_msg_count = 0 x_msg_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 |
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