This chapter covers the following topics:
PL/SQL Package Name: HZ_CUST_ACCOUNT_V2PUB
Java Class Name: HzCustAccountV2Pub
TYPE cust_account_rec_type IS RECORD (
cust_account_id NUMBER,
account_number VARCHAR2(30),
attribute_category VARCHAR2(30),
attribute1 VARCHAR2(150),
attribute2 VARCHAR2(150),
attribute3 VARCHAR2(150),
attribute4 VARCHAR2(150),
attribute5 VARCHAR2(150),
attribute6 VARCHAR2(150),
attribute7 VARCHAR2(150),
attribute8 VARCHAR2(150),
attribute9 VARCHAR2(150),
attribute10 VARCHAR2(150),
attribute11 VARCHAR2(150),
attribute12 VARCHAR2(150),
attribute13 VARCHAR2(150),
attribute14 VARCHAR2(150),
attribute15 VARCHAR2(150),
attribute16 VARCHAR2(150),
attribute17 VARCHAR2(150),
attribute18 VARCHAR2(150),
attribute19 VARCHAR2(150),
attribute20 VARCHAR2(150),
global_attribute_category VARCHAR2(30),
global_attribute1 VARCHAR2(150),
global_attribute2 VARCHAR2(150),
global_attribute3 VARCHAR2(150),
global_attribute4 VARCHAR2(150),
global_attribute5 VARCHAR2(150),
global_attribute6 VARCHAR2(150),
global_attribute7 VARCHAR2(150),
global_attribute8 VARCHAR2(150),
global_attribute9 VARCHAR2(150),
global_attribute10 VARCHAR2(150),
global_attribute11 VARCHAR2(150),
global_attribute12 VARCHAR2(150),
global_attribute13 VARCHAR2(150),
global_attribute14 VARCHAR2(150),
global_attribute15 VARCHAR2(150),
global_attribute16 VARCHAR2(150),
global_attribute17 VARCHAR2(150),
global_attribute18 VARCHAR2(150),
global_attribute19 VARCHAR2(150),
global_attribute20 VARCHAR2(150),
orig_system_reference VARCHAR2(240),
orig_system VARCHAR2(30),
status VARCHAR2(1),
customer_type VARCHAR2(30),
customer_class_code VARCHAR2(30),
primary_salesrep_id NUMBER,
sales_channel_code VARCHAR2(30),
order_type_id NUMBER,
price_list_id NUMBER,
tax_code VARCHAR2(50),
fob_point VARCHAR2(30),
freight_term VARCHAR2(30),
ship_via VARCHAR2(25),
warehouse_id NUMBER,
tax_header_level_flag VARCHAR2(1),
tax_rounding_rule VARCHAR2(30),
coterminate_day_month VARCHAR2(6),
primary_specialist_id NUMBER,
secondary_specialist_id NUMBER,
account_liable_flag VARCHAR2(1),
current_balance NUMBER,
account_established_date DATE,
account_termination_date DATE,
account_activation_date DATE,
department VARCHAR2(30),
held_bill_expiration_date DATE,
hold_bill_flag VARCHAR2(1),
realtime_rate_flag VARCHAR2(1),
acct_life_cycle_status VARCHAR2(30),
account_name VARCHAR2(240),
deposit_refund_method VARCHAR2(20),
dormant_account_flag VARCHAR2(1),
npa_number VARCHAR2(60),
suspension_date DATE,
source_code VARCHAR2(150),
comments VARCHAR2(240),
dates_negative_tolerance NUMBER,
dates_positive_tolerance NUMBER,
date_type_preference VARCHAR2(20),
over_shipment_tolerance NUMBER,
under_shipment_tolerance NUMBER,
over_return_tolerance NUMBER,
under_return_tolerance NUMBER,
item_cross_ref_pref VARCHAR2(30),
ship_sets_include_lines_flag VARCHAR2(1),
arrivalsets_include_lines_flag VARCHAR2(1),
sched_date_push_flag VARCHAR2(1),
invoice_quantity_rule VARCHAR2(30),
status_update_date DATE,
autopay_flag VARCHAR2(1),
notify_flag VARCHAR2(1),
last_batch_id NUMBER,
selling_party_id NUMBER,
created_by_module VARCHAR2(150),
application_id NUMBER
federal_entity_type VARCHAR2(30)
trading_partner_agency_id VARCHAR2(3),
duns_extension VARCHAR2(4),
advance_payment_indicator VARCHAR2(30),
cancel_unshipped_lines_flag VARCHAR2(1)
)
TYPE cust_acct_relate_rec_type IS RECORD (
cust_account_id NUMBER,
related_cust_account_id NUMBER,
relationship_type VARCHAR2(30),
comments VARCHAR2(240),
attribute_category VARCHAR2(30),
attribute1 VARCHAR2(150),
attribute2 VARCHAR2(150),
attribute3 VARCHAR2(150),
attribute4 VARCHAR2(150),
attribute5 VARCHAR2(150),
attribute6 VARCHAR2(150),
attribute7 VARCHAR2(150),
attribute8 VARCHAR2(150),
attribute9 VARCHAR2(150),
attribute10 VARCHAR2(150),
customer_reciprocal_flag VARCHAR2(1),
status VARCHAR2(1),
attribute11 VARCHAR2(150),
attribute12 VARCHAR2(150),
attribute13 VARCHAR2(150),
attribute14 VARCHAR2(150),
attribute15 VARCHAR2(150),
bill_to_flag VARCHAR2(1),
ship_to_flag VARCHAR2(1),
created_by_module VARCHAR2(150),
application_id NUMBER
)
public static class CustAccountRec {
public BigDecimal cust_account_id;
public String account_number;
public String attribute_category;
public String attribute1;
public String attribute2;
public String attribute3;
public String attribute4;
public String attribute5;
public String attribute6;
public String attribute7;
public String attribute8;
public String attribute9;
public String attribute10;
public String attribute11;
public String attribute12;
public String attribute13;
public String attribute14;
public String attribute15;
public String attribute16;
public String attribute17;
public String attribute18;
public String attribute19;
public String attribute20;
public String global_attribute_category;
public String global_attribute1;
public String global_attribute2;
public String global_attribute3;
public String global_attribute4;
public String global_attribute5;
public String global_attribute6;
public String global_attribute7;
public String global_attribute8;
public String global_attribute9;
public String global_attribute10;
public String global_attribute11;
public String global_attribute12;
public String global_attribute13;
public String global_attribute14;
public String global_attribute15;
public String global_attribute16;
public String global_attribute17;
public String global_attribute18;
public String global_attribute19;
public String global_attribute20;
public String orig_system_reference;
public String orig_system;
public String status;
public String customer_type;
public String customer_class_code;
public BigDecimal primary_salesrep_id;
public String sales_channel_code;
public BigDecimal order_type_id;
public BigDecimal price_list_id;
public String tax_code;
public String fob_point;
public String freight_term;
public String ship_via;
public BigDecimal warehouse_id;
public String tax_header_level_flag;
public String tax_rounding_rule;
public String coterminate_day_month;
public BigDecimal primary_specialist_id;
public BigDecimal secondary_specialist_id;
public String account_liable_flag;
public BigDecimal current_balance;
public java.sql.Timestamp account_established_date;
public java.sql.Timestamp account_termination_date;
public java.sql.Timestamp account_activation_date;
public String department;
public java.sql.Timestamp held_bill_expiration_date;
public String hold_bill_flag;
public String realtime_rate_flag;
public String acct_life_cycle_status;
public String account_name;
public String deposit_refund_method;
public String dormant_account_flag;
public String npa_number;
public java.sql.Timestamp suspension_date;
public String source_code;
public String comments;
public BigDecimal dates_negative_tolerance;
public BigDecimal dates_positive_tolerance;
public String date_type_preference;
public BigDecimal over_shipment_tolerance;
public BigDecimal under_shipment_tolerance;
public BigDecimal over_return_tolerance;
public BigDecimal under_return_tolerance;
public String item_cross_ref_pref;
public String ship_sets_include_lines_flag;
public String arrivalsets_include_lines_flag;
public String sched_date_push_flag;
public String invoice_quantity_rule;
public java.sql.Timestamp status_update_date;
public String autopay_flag;
public String notify_flag;
public BigDecimal last_batch_id;
public BigDecimal selling_party_id;
public String created_by_module;
public BigDecimal application_id;
public String federal_entity_type;
public String trading_partner_agency_id;
public String duns_extension;
public String advance_payment_indicator;
public String cancel_unshipped_lines_flag;
public CustAccountRec();
public CustAccountRec(boolean __RosettaUseGMISSValues);
}
public static class CustAcctRelateRec {
public BigDecimal cust_account_id;
public BigDecimal related_cust_account_id;
public String relationship_type;
public String comments;
public String attribute_category;
public String attribute1;
public String attribute2;
public String attribute3;
public String attribute4;
public String attribute5;
public String attribute6;
public String attribute7;
public String attribute8;
public String attribute9;
public String attribute10;
public String customer_reciprocal_flag;
public String status;
public String attribute11;
public String attribute12;
public String attribute13;
public String attribute14;
public String attribute15;
public String bill_to_flag;
public String ship_to_flag;
public String created_by_module;
public BigDecimal application_id;
public CustAcctRelateRec();
public CustAcctRelateRec(boolean __RosettaUseGMISSValues);
}
This routine is used to create a Customer Account. The API creates a record in the HZ_CUST_ACCOUNTS table for party type Person or Organization. Account can be created for an existing party by passing party_id of the party. Alternatively, this routine creates a new party and an account for the party. Customer profile record in the HZ_CUSTOMER_PROFILES table can also be created while calling this routine based on value passed in p_customer_profile_rec. The routine is overloaded for Person and Organization. If an orig_system_reference is passed in, the API creates a record in the HZ_ORIG_SYS_REFERENCES table to store the mapping between the source system reference and the TCA primary key. If orig_system_reference is not passed in, the default is UNKNOWN.
PROCEDURE create_cust_account (
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
p_cust_account_rec IN CUST_ACCOUNT_REC_TYPE,
p_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE,
p_customer_profile_rec IN HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
p_create_profile_amt IN VARCHAR2:= FND_API.G_TRUE,
x_cust_account_id OUT NUMBER,
x_account_number OUT VARCHAR2,
x_party_id OUT NUMBER,
x_party_number OUT VARCHAR2,
x_profile_id OUT NUMBER,
x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2
)
Note: p_create_profile_amt indicates whether to create profile amounts for the customer profile being created. If value equals to FND_API.G_TRUE, profile amounts will be created by copying over the profile amounts for the profile class on which this customer profile is based.
PROCEDURE create_cust_account (
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
p_cust_account_rec IN CUST_ACCOUNT_REC_TYPE,
p_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
p_customer_profile_rec IN HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
p_create_profile_amt IN VARCHAR2:= FND_API.G_TRUE,
x_cust_account_id OUT NUMBER,
x_account_number OUT VARCHAR2,
x_party_id OUT NUMBER,
x_party_number OUT VARCHAR2,
x_profile_id OUT NUMBER,
x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2
)
Note: p_create_profile_amt indicates whether to create profile amounts for the customer profile being created. If value equals to FND_API.G_TRUE, profile amounts will be created by copying over the profile amounts for the profile class on which this customer profile is based.
public static void createCustAccount
OracleConnection_connection,
String p_init_msg_list,
CustAccountRec p_cust_account_rec,
HzPartyV2Pub.PersonRec p_person_rec,
HzCustomerProfileV2Pub.CustomerProfileRec p_customer_profile_rec,
String p_create_profile_amt,
BigDecimal [ ] x_cust_account_id,
String [ ] x_account_number,
BigDecimal [ ] x_party_id,
String [ ] x_party_number,
BigDecimal [ ] x_profile_id,
String [ ] x_return_status,
BigDecimal [ ] x_msg_count,
String [ ] x_msg_data
) throws SQLException;
Note: p_create_profile_amt indicates whether to create profile amounts for the customer profile being created. If value equals to HzConstant.getGTrue(), profile amounts will be created by copying over the profile amounts for the profile class on which this customer profile is based.
public static void createCustAccount(
OracleConnection_connection,
String p_init_msg_list,
CustAccountRec p_cust_account_rec,
HzPartyV2Pub.OrganizationRec p_organization_rec,
HzCustomerProfileV2Pub.CustomerProfileRec p_customer_profile_rec,
String p_create_profile_amt,
BigDecimal [ ] x_cust_account_id,
String [ ] x_account_number,
BigDecimal [ ] x_party_id,
String [ ] x_party_number,
BigDecimal [ ] x_profile_id,
String [ ] x_return_status,
BigDecimal [ ] x_msg_count,
String [ ] x_msg_data
) throws SQLException;
Note: p_create_profile_amt indicates whether to create profile amounts for the customer profile being created. If value equals to HzConstant.getGTrue(), profile amounts will be created by copying over the profile amounts for the profile class on which this customer profile is based.
The following table lists information about the parameters in the Create Customer Account API. The table includes the parameter names, the type of each parameter, the data type of each parameter, the necessity of the parameter, and other information about the parameter such as validation, defaults, and other comments.
| Parameter Name | Type | Data Type | Required | Validation, Default, Comment |
|---|---|---|---|---|
| cust_account_id | IN | NUMBER | N | Validation: unique if passed in, else generated from sequence |
| account_number | IN | VARCHAR2 | Y | Validation:
|
| attribute_category | IN | VARCHAR2 | N | |
| attribute1 | IN | VARCHAR2 | N | |
| attribute2 | IN | VARCHAR2 | N | |
| attribute3 | IN | VARCHAR2 | N | |
| attribute4 | IN | VARCHAR2 | N | |
| attribute5 | IN | VARCHAR2 | N | |
| attribute6 | IN | VARCHAR2 | N | |
| attribute7 | IN | VARCHAR2 | N | |
| attribute8 | IN | VARCHAR2 | N | |
| attribute9 | IN | VARCHAR2 | N | |
| attribute10 | IN | VARCHAR2 | N | |
| attribute11 | IN | VARCHAR2 | N | |
| attribute12 | IN | VARCHAR2 | N | |
| attribute13 | IN | VARCHAR2 | N | |
| attribute14 | IN | VARCHAR2 | N | |
| attribute15 | IN | VARCHAR2 | N | |
| attribute16 | IN | VARCHAR2 | N | |
| attribute17 | IN | VARCHAR2 | N | |
| attribute18 | IN | VARCHAR2 | N | |
| attribute19 | IN | VARCHAR2 | N | |
| attribute20 | IN | VARCHAR2 | N | |
| global_attribute_category | IN | VARCHAR2 | N | |
| global_attribute1 | IN | VARCHAR2 | N | |
| global_attribute2 | IN | VARCHAR2 | N | |
| global_attribute3 | IN | VARCHAR2 | N | |
| global_attribute4 | IN | VARCHAR2 | N | |
| global_attribute5 | IN | VARCHAR2 | N | |
| global_attribute6 | IN | VARCHAR2 | N | |
| global_attribute7 | IN | VARCHAR2 | N | |
| global_attribute8 | IN | VARCHAR2 | N | |
| global_attribute9 | IN | VARCHAR2 | N | |
| global_attribute10 | IN | VARCHAR2 | N | |
| global_attribute11 | IN | VARCHAR2 | N | |
| global_attribute12 | IN | VARCHAR2 | N | |
| global_attribute13 | IN | VARCHAR2 | N | |
| global_attribute14 | IN | VARCHAR2 | N | |
| global_attribute15 | IN | VARCHAR2 | N | |
| global_attribute16 | IN | VARCHAR2 | N | |
| global_attribute17 | IN | VARCHAR2 | N | |
| global_attribute18 | IN | VARCHAR2 | N | |
| global_attribute19 | IN | VARCHAR2 | N | |
| global_attribute20 | IN | VARCHAR2 | N | |
| orig_system_reference | IN | VARCHAR2 | N | Validation: unique if passed in Default: cust_account_id |
| orig_system | IN | VARCHAR2 | N | Validation: Foreign key to HZ_ORIG_SYSTEMS_B.orig_system. Default: UNKNOWN if an orig_system_reference is passed in. |
| status | IN | VARCHAR2 | N | Validation:
|
| customer_type | IN | VARCHAR2 | N | Validation: customer_type is lookup code in AR lookup type CUSTOMER_TYPE |
| customer_class_code | IN | VARCHAR2 | N | VAlidation: Validated against AR lookup type CUSTOMER CLASS |
| primary_salesrep_id | IN | NUMBER | N | Comment: This attribute is no longer used. Use hz_cust_site_uses.primary_salesrep_id instead. |
| sales_channel_code | IN | VARCHAR2 | N | Validation: sales_channel_code is lookup code in lookup type SALES_CHANNEL in so_lookups |
| order_type_id | IN | NUMBER | N | Comment: This attribute is no longer used. Use hz_cust_site_uses.order_type_id instead. |
| price_list_id | IN | NUMBER | N | Validation: Must be a valid price_list_id from SO_PRICE_LISTS table. |
| tax_code | IN | VARCHAR2 | N | Comment: This attribute is no longer used. Use hz_cust_site_uses.tax_code instead. |
| fob_point | IN | VARCHAR2 | N | Validation: Validated against AR lookup type FOB. |
| freight_term | IN | VARCHAR2 | N | Validation: freight_term is lookup code in lookup type FREIGHT_TERMS in so_lookups |
| ship_partial | IN | VARCHAR2 | N | Comment: This attribute is no longer used. Functionality replaced by ship_sets_include_lines_flag. |
| ship_via | IN | VARCHAR2 | N | Validation: ship_via is foreign key to oe_ship_methods_v and can be passed only in single org case |
| warehouse_id | IN | NUMBER | N | Validation: Must be valid organization_id from the ORG_ORGANIZATION_DEFINITIONS table. |
| tax_header_level_flag | IN | VARCHAR2 | N | Validation: tax_header_level_flag is lookup code in lookup type YES/NO. It is defaulted to 'N' if user does not pass value. |
| tax_rounding_rule | IN | VARCHAR2 | N | Validation: Validated against AR lookup type TAX_ROUNDING_RULE. |
| coterminate_day_month | IN | VARCHAR2 | N | |
| primary_specialist_id | IN | NUMBER | N | Validation: primary_specialist_id is foreign key to per_all_people_f |
| secondary_specialist_id | IN | NUMBER | N | Validation: secondary_specialist_id is foreign key to per_all_people_f |
| account_liable_flag | IN | VARCHAR2 | N | Comment: This parameter is no longer used. Validation: account_liable_flag is lookup code in lookup type YES/NO. It is defaulted to 'N' if user does not pass value. |
| current_balance | IN | NUMBER | N | Comment: This parameter is no longer used. |
| account_established_date | IN | DATE | N | |
| account_termination_date | IN | DATE | N | Comment: This parameter is no longer used. Validation:
|
| account_activation_date | IN | DATE | N | Comment: This parameter is no longer used. Validation: account_activation_date should be greater than account_established_date. |
| department | IN | VARCHAR2 | N | Comment: This parameter is no longer used. |
| held_bill_expiration_date | IN | DATE | N | |
| hold_bill_flag | IN | VARCHAR2 | N | Validation: hold_bill_flag is lookup code in lookup type YES/NO Default: N. |
| realtime_rate_flag | IN | VARCHAR2 | N | Comment: This parameter is no longer used. |
| acct_life_cycle_status | IN | VARCHAR2 | N | Comment: This parameter is no longer used. |
| account_name | IN | VARCHAR2 | N | |
| deposit_refund_method | IN | VARCHAR2 | N | |
| dormant_account_flag | IN | VARCHAR2 | N | Comment: This parameter is no longer used. Validation: dormant_account_flag is lookup code in lookup type YES/NO. Default: N. |
| npa_number | IN | VARCHAR2 | N | |
| suspension_date | IN | DATE | N | Comment: This parameter is no longer used. |
| source_code | IN | VARCHAR2 | N | |
| comments | IN | VARCHAR2 | N | |
| dates_negative_tolerance | IN | NUMBER | N | |
| dates_positive_tolerance | IN | NUMBER | N | |
| date_type_preference | IN | VARCHAR2 | N | Validation: Validated against OE lookup type REQUEST_DATE_TYPE |
| over_shipment_tolerance | IN | NUMBER | N | |
| under_shipment_tolerance | IN | NUMBER | N | |
| over_return_tolerance | IN | NUMBER | N | |
| under_return_tolerance | IN | NUMBER | N | |
| item_cross_ref_pref | IN | VARCHAR2 | N | Validation: Allowed values are INT, CUST, and valid cross_reference_type from MTL_CROSS_REFERENCE_TYPES |
| ship_sets_include_lines_flag | IN | VARCHAR2 | N | Validation:
Default: N |
| arrivalsets_include_lines_flag | IN | VARCHAR2 | N | Validation:
|
| sched_date_push_flag | IN | VARCHAR2 | N | Validation: sched_date_push_flag is lookup code in lookup type YES/NO |
| invoice_quantity_rule | IN | VARCHAR2 | N | Validated against OE lookup type INVOICE_BASIS. |
| pricing_event | IN | VARCHAR2 | N | Comment: This attribute is no longer used. |
| status_update_date | IN | DATE | N | |
| autopay_flag | IN | VARCHAR2 | N | Validation: autopay_flag is lookup code in lookup type YES/NO |
| notify_flag | IN | VARCHAR2 | N | Comment: This parameter is no longer used. Validation: notify_flag is lookup code in lookup type YES/NO. |
| last_batch_id | IN | NUMBER | N | |
| selling_party_id | IN | NUMBER | N | Validation: selling_party_id is foreign key of HZ_PARTIES |
| created_by_module | IN | VARCHAR2 | Y | Validation: Mandatory attribute. Validated against AR lookup type HZ_CREATED_BY_MODULE. |
| application_id | IN | NUMBER | N | Comment: Text to indicate application from which creation of record is initiated |
| x_cust_account_id | OUT | NUMBER | N | Comment: Returns cust_account_id of the account record create |
| x_account_number | OUT | NUMBER | N | Comment: Returns account_number of the account record created |
| x_party_id | OUT | NUMBER | N | Comment: Returns party_id of the organization or person party created |
| x_party_number | OUT | NUMBER | N | Comment: Returns party_number of the organization or person created |
| x_profile_id | OUT | NUMBER | N | Comment: Returns profile_id of the organization or person profile created |
| federal_entity_type | IN | VARCHAR2 | N | Validation: Select lookup_code, description from fv_lookup_codes where lookup_type = 'FV_FED_NON_FED_CODE'; Comment: Identifies the type of entity involved in transactions with the reporting entity. |
| trading_partner_agency_id | IN | VARCHAR2 | N | Validation:
SELECT agency_id FROM fv_tp_treasury_symbolsORDER BY agency_id; Comment: ID of the federal agency involved in transactions with the reporting entity. This is required if Federal Entity Type is “F”. |
| duns_extension | IN | VARCHAR2 | N | Validation: Must contain four alphanumeric characters in uppercase, without spaces and underscores. |
| advance_payment_indicator | IN | VARCHAR2 | N | Validation:Lookup is Select lookup_code, description from fv_lookup_codes where lookup_type = 'FV_ADV_PMT_INDICATOR'; |
| cancel_unshipped_lines_flag | IN | VARCHAR2 | N | Validation: cancel_unshipped_lines_flag is lookup code in lookup type YES/NO Comment: Indicates whether to cancel all unshipped orders/lines. Default: Null |
If party referenced by party_id user passes through p_person_rec.party_rec or p_organization_rec.party_rec exists, we will create only account for this existing party. However, if party does not exist or user does not pass party_id (i.e. party_id is null or FND_API.G_MISS_NUM), we will create both party and account. If party has to be created, the validations on p_person_rec and p_organization_rec are same as those in create_person, create_organization in hz_party_v2pub.
Customer profile is mandatory for an account. If user does not pass profile_class_name, we will create a customer profile based on default profile class, which should have ID 0 and in active status. The validations on p_customer_profile_rec are same as those in hz_customer_profile_v2pub.create_customer_profile.
If p_create_profile_amt is FND_API.G_TRUE, we will create customer profile amount when we create customer profile. These customer profile amounts have defaulted value from profile class amounts of the same profile class.
This routine is used to update a Customer Account. The API updates a record in the HZ_CUST_ACCOUNTS table. The account could belong to a party of type Person or Organization:. The same routine updates all types of accounts whether it belongs to a person or an organization.
If the primary key is not passed in, get the primary key from the HZ_ORIG_SYS_REFERENCES table based on orig_system and orig_system_reference if they are not null and unique.
PROCEDURE update_cust_account (
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
p_cust_account_rec IN CUST_ACCOUNT_REC_TYPE,
p_object_version_number IN OUT NUMBER,
x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2
)
public static void updateCustAccount(
OracleConnection_connection,
String p_init_msg_list,
CustAccountRec p_cust_account_rec,
BigDecimal [ ] p_object_version_number,
String [ ] x_return_status,
BigDecimal [ ] x_msg_count,
String [ ] x_msg_data
) throws SQLException;
The following table lists information about the parameters in the Update Customer Account API. The table includes the parameter names, the type of each parameter, the data type of each parameter, the necessity of the parameter, and other information about the parameter such as validation, defaults, and other comments.
| Parameter Name | Type | Data Type | Required | Validation, Default, Comment |
|---|---|---|---|---|
| cust_account_id | IN | NUMBER | Yes | Validation: valid cust_account_id should be passed in Comment: Pass cust_account_id from hz_cust_accounts table |
| account_number | IN | VARCHAR2 | No | Validation: Not updateable if automatic numbering is on. If not, then account_number can be updated. |
| attribute_category | IN | VARCHAR2 | No | |
| attribute1 | IN | VARCHAR2 | No | |
| attribute2 | IN | VARCHAR2 | No | |
| attribute3 | IN | VARCHAR2 | No | |
| attribute4 | IN | VARCHAR2 | No | |
| attribute5 | IN | VARCHAR2 | No | |
| attribute6 | IN | VARCHAR2 | No | |
| attribute7 | IN | VARCHAR2 | No | |
| attribute8 | IN | VARCHAR2 | No | |
| attribute9 | IN | VARCHAR2 | No | |
| attribute10 | IN | VARCHAR2 | No | |
| attribute11 | IN | VARCHAR2 | No | |
| attribute12 | IN | VARCHAR2 | No | |
| attribute13 | IN | VARCHAR2 | No | |
| attribute14 | IN | VARCHAR2 | No | |
| attribute15 | IN | VARCHAR2 | No | |
| attribute16 | IN | VARCHAR2 | No | |
| attribute17 | IN | VARCHAR2 | No | |
| attribute18 | IN | VARCHAR2 | No | |
| attribute19 | IN | VARCHAR2 | No | |
| attribute20 | IN | VARCHAR2 | No | |
| global_attribute_category | IN | VARCHAR2 | No | |
| global_attribute1 | IN | VARCHAR2 | No | |
| global_attribute2 | IN | VARCHAR2 | No | |
| global_attribute3 | IN | VARCHAR2 | No | |
| global_attribute4 | IN | VARCHAR2 | No | |
| global_attribute5 | IN | VARCHAR2 | No | |
| global_attribute6 | IN | VARCHAR2 | No | |
| global_attribute7 | IN | VARCHAR2 | No | |
| global_attribute8 | IN | VARCHAR2 | No | |
| global_attribute9 | IN | VARCHAR2 | No | |
| global_attribute10 | IN | VARCHAR2 | No | |
| global_attribute11 | IN | VARCHAR2 | No | |
| global_attribute12 | IN | VARCHAR2 | No | |
| global_attribute13 | IN | VARCHAR2 | No | |
| global_attribute14 | IN | VARCHAR2 | No | |
| global_attribute15 | IN | VARCHAR2 | No | |
| global_attribute16 | IN | VARCHAR2 | No | |
| global_attribute17 | IN | VARCHAR2 | No | |
| global_attribute18 | IN | VARCHAR2 | No | |
| global_attribute19 | IN | VARCHAR2 | No | |
| global_attribute20 | IN | VARCHAR2 | No | |
| orig_system_reference | IN | VARCHAR2 | No | Validation: Non updateable, but if a primary key is not passed in, you can pass in any ORIG_SYSTEM and ORIG_SYSTEM_REFERENCE that exists in the HZ_ORIG_SYS_REFERENCE table and unique validation is bypassed. The ORIG_SYSTEM_REFERENCE does not change, but persists in the table. |
| orig_system | IN | VARCHAR2 | N | Validation: Foreign key to HZ_ORIG_SYSTEMS_B.orig_system. |
| status | IN | VARCHAR2 | No | Validation: Cannot be set to null during update |
| customer_type | IN | VARCHAR2 | No | Validation: customer_type is lookup code in AR lookup type CUSTOMER_TYPE |
| customer_class_code | IN | VARCHAR2 | No | Validation: Validated against AR lookup type CUSTOMER CLASS |
| primary_salesrep_id | IN | NUMBER | No | Comment: This attribute is no longer used. Use hz_cust_site_uses.primary_salesrep_id instead. |
| sales_channel_code | IN | VARCHAR2 | No | Validation: sales_channel_code is lookup code in lookup type SALES_CHANNEL in so_lookups |
| order_type_id | IN | NUMBER | No | Comment: This attribute is no longer used. Use hz_cust_site_uses.order_type_id instead. |
| price_list_id | IN | NUMBER | No | Validation: Must be valid price_list_id from SO_PRICE_LISTS. |
| tax_code | IN | VARCHAR2 | No | Comment: This attribute is no longer used. Use hz_cust_site_uses.tax_code instead. |
| fob_point | IN | VARCHAR2 | No | Validated against AR lookup type FOB. |
| freight_term | IN | VARCHAR2 | No | Validation: freight_term is lookup code in lookup type FREIGHT_TERMS in so_lookups |
| ship_partial | IN | VARCHAR2 | No | Comment: This attribute is no longer used. Functionality replaced by ship_sets_include_lines_flag. |
| ship_via | IN | VARCHAR2 | No | Validation: ship_via is foreign key to oe_ship_methods_v and can be passed only in single org case |
| warehouse_id | IN | NUMBER | No | Validation: Must be valid organization_id from ORG_ORGANIZATION_DEFINITIONS. |
| tax_header_level_flag | IN | VARCHAR2 | No | Validation: tax_header_level_flag is lookup code in lookup type YES/NO |
| tax_rounding_rule | IN | VARCHAR2 | No | Validation: Validated against AR lookup type TAX_ROUNDING_RULE. |
| coterminate_day_month | IN | VARCHAR2 | No | |
| primary_specialist_id | IN | NUMBER | No | Validation: primary_specialist_id is foreign key to per_all_people_f |
| secondary_specialist_id | IN | NUMBER | No | Validation: secondary_specialist_id is foreign key to per_all_people_f |
| account_liable_flag | IN | VARCHAR2 | No | Comment: This parameter is no longer used. Validation: account_liable_flag is lookup code in lookup type YES/NO. It is defaulted to 'N' if user does not pass value. |
| current_balance | IN | NUMBER | No | Comment: This parameter is no longer used. |
| account_established_date | IN | DATE | No | |
| account_termination_date | IN | DATE | No | Comment: This parameter is no longer used. Validation:
|
| account_activation_date | IN | DATE | No | Comment: This parameter is no longer used. Validation: account_activation_date should be greater than account_established_date. |
| department | IN | VARCHAR2 | No | Comment: This parameter is no longer used. |
| held_bill_expiration_date | IN | DATE | No | |
| hold_bill_flag | IN | VARCHAR2 | No | Validation: hold_bill_flag is lookup code in lookup type YES/NO |
| realtime_rate_flag | IN | VARCHAR2 | No | Comment: This parameter is no longer used. |
| acct_life_cycle_status | IN | VARCHAR2 | No | Comment: This parameter is no longer used. |
| account_name | IN | VARCHAR2 | No | |
| deposit_refund_method | IN | VARCHAR2 | No | |
| dormant_account_flag | IN | VARCHAR2 | No | Comment: This parameter is no longer used. Validation: dormant_account_flag is lookup code in lookup type YES/NO. Default: N. |
| npa_number | IN | VARCHAR2 | No | |
| suspension_date | IN | DATE | No | Comment: This parameter is no longer used. |
| source_code | IN | VARCHAR2 | No | |
| comments | IN | VARCHAR2 | No | |
| dates_negative_tolerance | IN | NUMBER | No | |
| dates_positive_tolerance | IN | NUMBER | No | |
| date_type_preference | IN | VARCHAR2 | No | Validation: Validated against OE lookup type REQUEST_DATE_TYPE. |
| over_shipment_tolerance | IN | NUMBER | No | |
| under_shipment_tolerance | IN | NUMBER | No | |
| over_return_tolerance | IN | NUMBER | No | |
| under_return_tolerance | IN | NUMBER | No | |
| item_cross_ref_pref | IN | VARCHAR2 | No | Validation: Allowed values are INT, CUST, and valid cross_reference_type from MTL_CROSS_REFERENCE_ TYPES. |
| ship_sets_include_lines_flag | IN | VARCHAR2 | No | Validation:
|
| arrivalsets_include_lines_flag | IN | VARCHAR2 | No | Validation:
|
| sched_date_push_flag | IN | VARCHAR2 | No | Validation: sched_date_push_flag is lookup code in lookup type YES/NO |
| invoice_quantity_rule | IN | VARCHAR2 | No | Validation: Validated against OE lookup type INVOICE_BASIS. |
| pricing_event | IN | VARCHAR2 | No | Comment: This attribute is no longer used. |
| status_update_date | IN | DATE | No | |
| autopay_flag | IN | VARCHAR2 | No | Validation: autopay_flag is lookup code in lookup type YES/NO |
| notify_flag | IN | VARCHAR2 | No | Comment: This parameter is no longer used. Validation: notify_flag is lookup code in lookup type YES/NO. |
| last_batch_id | IN | NUMBER | No | |
| selling_party_id | IN | NUMBER | No | Validation: selling_party_id should point to a organization party |
| created_by_module | IN | VARCHAR2 | No | Validation: Non updateable if value exists, else validated against AR lookup type HZ_CREATED_BY_MODULE. |
| application_id | IN | NUMBER | No | Validation: Not updateable if value exists. |
| p_object_version_number | IN | NUMBER | Yes | Validation:
Comment
|
| federal_entity_type | IN | VARCHAR2 | No | Validation: Select lookup_code, description from fv_lookup_codes where lookup_type = 'FV_FED_NON_FED_CODE'; Comment: Identifies the type of entity involved in transactions with the reporting entity. |
| trading_partner_agency_id | IN | VARCHAR2 | No | Validation:
Comment: ID of the federal agency involved in transactions with the reporting entity. This is required if Federal Entity Type is “F”. |
| duns_extension | IN | VARCHAR2 | No | Validation: Must contain four alphanumeric characters in uppercase, without spaces and underscores. |
| advance_payment_indicator | IN | VARCHAR2 | No | Validation: Lookup is Select lookup_code, description from fv_lookup_codes where lookup_type = 'FV_ADV_PMT_INDICATOR'; |
| cancel_unshipped_lines_flag | IN | VARCHAR2 | No | Validation: cancel_unshipped_lines_flag is lookup code in lookup type YES/NO Comment: Indicates whether to cancel all unshipped orders/lines. Default: Null |
This routine is used to create a Customer Account Relationship. The API creates a record in the HZ_CUST_ACCT_RELATE table. You can relate two different customer accounts in this process. This is different from Relationship API that has been discussed earlier.
PROCEDURE create_cust_acct_relate (
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
p_cust_acct_relate_rec IN CUST_ACCT_RELATE_REC_TYPE,
x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2
)
public static void createCustAcctRelate(
OracleConnection_connection,
String p_init_msg_list,
CustAcctRelateRec p_cust_acct_relate_rec,
String [ ] x_return_status,
BigDecimal [ ] x_msg_count,
String [ ] x_msg_data
) throws SQLException;
The following table lists information about the parameters in the Create Customer Account Relationship API. The table includes the parameter names, the type of each parameter, the data type of each parameter, the necessity of the parameter, and other information about the parameter such as validation, defaults, and other comments.
| Parameter Name | Type | Data Type | Required | Validation, Default, Comment |
|---|---|---|---|---|
| cust_account_id | IN | NUMBER | Yes | Validation: Mandatory attribute Comment: Pass cust_account_id hz_cust_accounts |
| related_cust_account_id | IN | NUMBER | Yes | Validation:
|
| relationship_type | IN | VARCHAR2 | No | Validation: relationship_type is lookup code in lookup type RELATIONSHIP_TYPE |
| comments | IN | VARCHAR2 | No | |
| attribute_category | IN | VARCHAR2 | No | |
| attribute1 | IN | VARCHAR2 | No | |
| attribute2 | IN | VARCHAR2 | No | |
| attribute3 | IN | VARCHAR2 | No | |
| attribute4 | IN | VARCHAR2 | No | |
| attribute5 | IN | VARCHAR2 | No | |
| attribute6 | IN | VARCHAR2 | No | |
| attribute7 | IN | VARCHAR2 | No | |
| attribute8 | IN | VARCHAR2 | No | |
| attribute9 | IN | VARCHAR2 | No | |
| attribute10 | IN | VARCHAR2 | No | |
| customer_reciprocal_flag | IN | VARCHAR2 | No | Validation: customer_reciprocal_flag is lookup code in lookup type YES/NO Default: N |
| status | IN | VARCHAR2 | No | Validation: Status is lookup code in lookup type CODE_STATUS Default: A |
| attribute11 | IN | VARCHAR2 | No | |
| attribute12 | IN | VARCHAR2 | No | |
| attribute13 | IN | VARCHAR2 | No | |
| attribute14 | IN | VARCHAR2 | No | |
| attribute15 | IN | VARCHAR2 | No | |
| bill_to_flag | IN | VARCHAR2 | No | |
| ship_to_flag | IN | VARCHAR2 | No | |
| created_by_module | IN | VARCHAR2 | Yes | Validation: Mandatory attribute. Validated against AR lookup type HZ_CREATED_BY_MODULE. |
| application_id | IN | NUMBER | No | Comment: Text to indicate module from which creation of record is initiated |
The combination of cust_account_id and related_cust_account_id should be unique for all active customer accounts in an organization.
This routine is used to update a Customer Account Relationship. The API updates accounts in the HZ_CUST_ACCT_RELATE table.
There are two overloaded procedures for this API. One updates active customer account relationship records only. The other with rowid in the signature updates customer account relationship records based on the rowid.
The p_rowid parameter is only in the overloaded procedure, and is used to identify the exact customer account relationship record to update.
PROCEDURE update_cust_acct_relate (
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
p_cust_acct_relate_rec IN CUST_ACCT_RELATE_REC_TYPE,
p_rowid IN ROWID,
p_object_version_number IN OUT NUMBER,
x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2
)
The p_rowid parameter is only in the overloaded procedure, and is used to identify the exact customer account relationship record to update.
public static void updateCustAcctRelate(
OracleConnection_connection,
String p_init_msg_list,
CustAcctRelateRec p_cust_acct_relate_rec,
String p_rowid,
BigDecimal [ ] p_object_version_number,
String [ ] x_return_status,
BigDecimal [ ] x_msg_count,
String [ ] x_msg_data
) throws SQLException;
The following table lists information about the parameters in the Update Customer Account Relationship API. The table includes the parameter names, the type of each parameter, the data type of each parameter, the necessity of the parameter, and other information about the parameter such as validation, defaults, and other comments.
| Parameter Name | Type | Data Type | Required | Validation, Default, Comment |
|---|---|---|---|---|
| cust_account_id | IN | NUMBER | Yes | Validation: cust_account_id is foreign key of hz_cust_accounts |
| related_cust_account_id | IN | NUMBER | Yes | Validation: related_cust_account_id is foreign key of hz_cust_accounts |
| relationship_type | IN | VARCHAR2 | No | Validation: relationship_type is lookup code in lookup type RELATIONSHIP_TYPE |
| comments | IN | VARCHAR2 | No | |
| attribute_category | IN | VARCHAR2 | No | |
| attribute1 | IN | VARCHAR2 | No | |
| attribute2 | IN | VARCHAR2 | No | |
| attribute3 | IN | VARCHAR2 | No | |
| attribute4 | IN | VARCHAR2 | No | |
| attribute5 | IN | VARCHAR2 | No | |
| attribute6 | IN | VARCHAR2 | No | |
| attribute7 | IN | VARCHAR2 | No | |
| attribute8 | IN | VARCHAR2 | No | |
| attribute9 | IN | VARCHAR2 | No | |
| attribute10 | IN | VARCHAR2 | No | |
| customer_reciprocal_flag | IN | VARCHAR2 | No | Validation: Not updateable |
| status | IN | VARCHAR2 | No | Validation:
|
| attribute11 | IN | VARCHAR2 | No | |
| attribute12 | IN | VARCHAR2 | No | |
| attribute13 | IN | VARCHAR2 | No | |
| attribute14 | IN | VARCHAR2 | No | |
| attribute15 | IN | VARCHAR2 | No | |
| bill_to_flag | IN | VARCHAR2 | No | |
| ship_to_flag | IN | VARCHAR2 | No | |
| created_by_module | IN | VARCHAR2 | No | Validation: Non updateable if value exists, else validated against AR lookup type HZ_CREATED_BY_MODULE. |
| application_id | IN | NUMBER | No | Validation: Not updateable if value exists. |
| p_rowid | IN | ROWID | No | Comment: This is only in the overloaded procedure of this API. |
| p_object_version_number | IN OUT |
NUMBER | Yes | Validation:
Comment:
|
The combination of cust_account_id and related_cust_account_id should be unique for all active customer accounts in an organization.