Introduction to Oracle Trading Community Architecture
Oracle Trading Community Architecture (TCA) is a data model that allows you to manage complex information about the parties, or customers, who belong to your commercial community, including organizations, locations, and the network of hierarchical relationships among them.
This information is maintained in the TCA Registry, which is the single source of trading community information for Oracle E-Business Suite applications. These applications, as well as TCA itself, provide user interfaces, batch data entry functionality, and other features for you to view, create, and update Registry information. See: Using Oracle Trading Community Architecture.
The key entities in TCA include:
x_return_status := fnd_api.g_ret_sts_success;
p_organization_rec.organization_name := cust_api_tbl(i).party_name;
p_organization_rec.party_rec.category_code := UPPER (cust_api_tbl(i).customer_prospect_code);
p_organization_rec.party_rec.orig_system_reference := cust_api_tbl(i).orig_system_party_ref;
p_organization_rec.party_rec.attribute_category := cust_api_tbl(i).customer_attribute_category;
p_organization_rec.created_by_module := G_CREATED_BY_MODULE;
hz_party_v2pub.create_organization( p_init_msg_list => fnd_api.g_true
, p_organization_rec => p_organization_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_party_id => x_party_id
, x_party_number => x_party_number
, x_profile_id => x_org_profile_id );
2. API to create Customer Account
p_customer_profile_rec.profile_class_id := l_cust_profile_api.profile_class_id;
p_customer_profile_rec.account_status := l_cust_profile_api.account_status;
p_customer_profile_rec.auto_rec_incl_disputed_flag := l_cust_profile_api.auto_rec_incl_disputed_flag;
p_customer_profile_rec.credit_balance_statements := l_cust_profile_api.credit_balance_statements;
p_customer_profile_rec.credit_checking := l_cust_profile_api.credit_checking;
p_customer_profile_rec.credit_hold := l_cust_profile_api.credit_hold;
p_customer_profile_rec.discount_terms := l_cust_profile_api.discount_terms;
p_customer_profile_rec.disputed_transactions_flag := l_cust_profile_api.disputed_transactions_flag;
p_customer_profile_rec.dunning_letters := l_cust_profile_api.dunning_letters;
p_customer_profile_rec.override_terms := l_cust_profile_api.override_terms;
p_customer_profile_rec.percent_collectable := l_cust_profile_api.percent_collectable;
p_customer_profile_rec.statement_cycle_id := l_cust_profile_api.statement_cycle_id;
p_customer_profile_rec.send_statements := l_cust_profile_api.statements;
p_customer_profile_rec.tax_printing_option := l_cust_profile_api.tax_printing_option;
p_customer_profile_rec.standard_terms := l_cust_profile_api.standard_term_id;
p_cust_account_rec.account_number := cust_api_tbl(i).customer_number;
p_cust_account_rec.account_name := cust_api_tbl(i).customer_name;
p_cust_account_rec.orig_system_reference := cust_api_tbl(i).orig_system_customer_ref;
p_cust_account_rec.sales_channel_code := cust_api_tbl(i).sales_channel_code;
p_cust_account_rec.freight_term := cust_api_tbl(i).freight_term_code;
p_cust_account_rec.price_list_id := cust_api_tbl(i).price_list_id;
p_cust_account_rec.date_type_preference := upper(cust_api_tbl(i).request_date_type);
p_cust_account_rec.ship_via := cust_api_tbl(i).cust_ship_via_code;
p_cust_account_rec.status := cust_api_tbl(i).customer_status;
p_cust_account_rec.customer_class_code := cust_api_tbl(i).customer_class_code; --- 'INDIRECT';
p_cust_account_rec.customer_type := cust_api_tbl(i).customer_type;
p_cust_account_rec.created_by_module := G_CREATED_BY_MODULE;
p_organization_rec.party_rec.party_id := cust_api_tbl(i).party_id;
p_organization_rec.created_by_module := G_CREATED_BY_MODULE;
IF NVL(cust_api_tbl(i).status_flag,'I') <> 'C' THEN
hz_cust_account_v2pub.create_cust_account ( p_init_msg_list => fnd_api.g_true
, p_cust_account_rec => p_cust_account_rec
, p_organization_rec => p_organization_rec
, p_customer_profile_rec => p_customer_profile_rec
, p_create_profile_amt => fnd_api.g_false
, x_cust_account_id => x_cust_account_id
, x_account_number => x_account_number
, x_party_id => x_party_id
, x_party_number => x_party_number
, x_profile_id => x_profile_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data );
3. API to create Customer Profile
p_cust_profile_amt_rec_type.currency_code := l_cust_profile_api.currency_code;
p_cust_profile_amt_rec_type.overall_credit_limit := l_cust_profile_api.overall_credit_limit;
p_cust_profile_amt_rec_type.trx_credit_limit := l_cust_profile_api.trx_credit_limit;
hz_customer_profile_v2pub.create_cust_profile_amt ( p_init_msg_list => fnd_api.g_true
, p_check_foreign_key => x_cust_account_id
, p_cust_profile_amt_rec => p_cust_profile_amt_rec_type
, x_cust_acct_profile_amt_id => x_cust_acct_profile_amt_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data ) ;
4. API to create HZ location
p_location_rec.country := cust_site_api_tbl(i).country_code;
p_location_rec.city := cust_site_api_tbl(i).city;
p_location_rec.postal_code := NVL(get_lookup_county_postcode ( cust_site_api_tbl(i).postal_code,'POSTAL_CODE_MAPPING'),cust_site_api_tbl(i).postal_code);
p_location_rec.state := cust_site_api_tbl(i).state;
p_location_rec.province := cust_site_api_tbl(i).province;
p_location_rec.language := cust_site_api_tbl(i).language;
p_location_rec.county := NVL(get_lookup_county_postcode ( cust_site_api_tbl(i).county,'COUNTY_MAPPING'),cust_site_api_tbl(i).county);
p_location_rec.created_by_module := G_CREATED_BY_MODULE;
--p_location_rec.sales_tax_inside_city_limits := cust_site_api_tbl(i).sales_tax_inside_city_limits;
hz_location_v2pub.create_location( p_init_msg_list => fnd_api.g_true
, p_location_rec => p_location_rec
, x_location_id => x_location_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data );
5. API to Create Party Site
p_party_site_rec.party_id := cust_site_api_tbl(i).party_id;
p_party_site_rec.location_id := cust_site_api_tbl(i).location_id;
p_party_site_rec.language := cust_site_api_tbl(i).language;
p_party_site_rec.orig_system_reference := cust_site_api_tbl(i).orig_system_address_ref;
p_party_site_rec.created_by_module := G_CREATED_BY_MODULE;
p_party_site_rec.identifying_address_flag := cust_site_api_tbl(i).identifying_address_flag;
p_party_site_rec.party_site_number := cust_site_api_tbl(i).party_site_number;
hz_party_site_v2pub.create_party_site( p_init_msg_list => fnd_api.g_true
, p_party_site_rec => p_party_site_rec
, x_party_site_id => x_party_site_id
, x_party_site_number => x_party_site_number
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data );
6. API to create party site use
p_party_site_use_rec.site_use_type := cust_site_api_tbl(i).site_use_code;
p_party_site_use_rec.party_site_id := cust_site_api_tbl(i).party_site_id;
p_party_site_use_rec.primary_per_type := cust_site_api_tbl(i).primary_site_use_flag;
p_party_site_use_rec.created_by_module := G_CREATED_BY_MODULE;
p_party_site_use_rec.status := cust_site_api_tbl(i).cust_site_uses_status;
hz_party_site_v2pub.create_party_site_use( p_init_msg_list => fnd_api.g_true
, p_party_site_use_rec => p_party_site_use_rec
, x_party_site_use_id => x_party_site_use_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data );
7. API to create Customer Account Site
p_cust_acct_site_rec.org_id := cust_site_api_tbl(i).org_id;
p_cust_acct_site_rec.cust_account_id := cust_site_api_tbl(i).cust_account_id;
p_cust_acct_site_rec.party_site_id := cust_site_api_tbl(i).party_site_id;
p_cust_acct_site_rec.orig_system_reference := cust_site_api_tbl(i).orig_system_address_ref;
p_cust_acct_site_rec.attribute_category := cust_site_api_tbl(i).address_attribute_category;
p_cust_acct_site_rec.attribute1 := cust_site_api_tbl(i).address_attribute1;
--p_cust_acct_site_rec.language := cust_site_api_tbl(i).language; --language column is obsolete so no mapping required
p_cust_acct_site_rec.ece_tp_location_code := cust_site_api_tbl(i).edi_location;
--p_cust_acct_site_rec.status := cust_site_api_tbl(i).cust_acct_sites_status;
--p_cust_acct_site_rec.territory := cust_site_api_tbl(i).territory; -- Business asked to ignore this on 16th Sep 2013
p_cust_acct_site_rec.created_by_module := G_CREATED_BY_MODULE;
hz_cust_account_site_v2pub.create_cust_acct_site( p_init_msg_list => fnd_api.g_true
, p_cust_acct_site_rec => p_cust_acct_site_rec
, x_cust_acct_site_id => x_cust_acct_site_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data );
8. API to Create Cust Account Site Use
p_su_customer_profile_rec.profile_class_id := l_cust_site_profile_api.profile_class_id;
p_su_customer_profile_rec.account_status := l_cust_site_profile_api.account_status;
p_su_customer_profile_rec.credit_balance_statements := l_cust_site_profile_api.credit_balance_statements;
p_su_customer_profile_rec.credit_checking := l_cust_site_profile_api.credit_checking;
p_su_customer_profile_rec.credit_hold := l_cust_site_profile_api.credit_hold;
p_su_customer_profile_rec.discount_terms := l_cust_site_profile_api.discount_terms;
p_su_customer_profile_rec.disputed_transactions_flag := l_cust_site_profile_api.disputed_transactions_flag;
p_su_customer_profile_rec.dunning_letters := l_cust_site_profile_api.dunning_letters;
p_su_customer_profile_rec.override_terms := l_cust_site_profile_api.override_terms;
p_su_customer_profile_rec.percent_collectable := l_cust_site_profile_api.percent_collectable;
p_su_customer_profile_rec.statement_cycle_id := l_cust_site_profile_api.statement_cycle_id;
p_su_customer_profile_rec.send_statements := l_cust_site_profile_api.statements;
p_su_customer_profile_rec.tax_printing_option := l_cust_site_profile_api.tax_printing_option;
p_su_customer_profile_rec.standard_terms := l_cust_site_profile_api.standard_term_id;
p_cust_site_use_rec.tax_reference := cust_site_api_tbl(i).cust_tax_reference;
p_cust_site_use_rec.org_id := cust_site_api_tbl(i).org_id;
p_cust_site_use_rec.cust_acct_site_id := cust_site_api_tbl(i).cust_acct_site_id;
p_cust_site_use_rec.site_use_code := cust_site_api_tbl(i).site_use_code;
p_cust_site_use_rec.primary_flag := cust_site_api_tbl(i).primary_site_use_flag;
p_cust_site_use_rec.orig_system_reference := cust_site_api_tbl(i).orig_system_address_ref;
p_cust_site_use_rec.freight_term := cust_site_api_tbl(i).freight_term_code;
p_cust_site_use_rec.price_list_id := l_site_price_list_id; ---cust_site_api_tbl(i).price_list_id; Added By Sachin On 11-SEP-2014
p_cust_site_use_rec.demand_class_code := cust_site_api_tbl(i).demand_class_code;
p_cust_site_use_rec.status := 'A' ;
p_cust_site_use_rec.LOCATION := cust_site_api_tbl(i).LOCATION ;
p_cust_site_use_rec.territory_id := cust_site_api_tbl(i).territory_id ;
p_cust_site_use_rec.warehouse_id := cust_site_api_tbl(i).warehouse_id ;
p_cust_site_use_rec.primary_salesrep_id := cust_site_api_tbl(i).salesrep_id ;
p_cust_site_use_rec.created_by_module := G_CREATED_BY_MODULE;
hz_cust_account_site_v2pub.create_cust_site_use( p_init_msg_list => fnd_api.g_true
, p_cust_site_use_rec => p_cust_site_use_rec
, p_customer_profile_rec => p_su_customer_profile_rec
, p_create_profile => fnd_api.g_true
, p_create_profile_amt => fnd_api.g_false
, x_site_use_id => x_site_use_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data );
9. API to create Customer Profile
p_cust_profile_amt_rec_type.currency_code := l_cust_site_profile_api.currency_code;
p_cust_profile_amt_rec_type.overall_credit_limit := l_cust_site_profile_api.overall_credit_limit;
p_cust_profile_amt_rec_type.trx_credit_limit := l_cust_site_profile_api.trx_credit_limit;
p_cust_profile_amt_rec_type.attribute4 := l_cust_site_profile_api.amount_attribute4;
p_cust_profile_amt_rec_type.cust_account_profile_id := l_cust_account_profile_id;
p_cust_profile_amt_rec_type.cust_account_id := cust_site_api_tbl(i).cust_account_id;
hz_customer_profile_v2pub.create_cust_profile_amt (p_init_msg_list => fnd_api.g_true,
p_check_foreign_key => cust_site_api_tbl(i).cust_account_id,
p_cust_profile_amt_rec => p_cust_profile_amt_rec_type,
x_cust_acct_profile_amt_id => x_cust_acct_profile_amt_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
) ;
10. API to create Customer Payment Method
p_rec_mtd_rec.cust_account_id := cust_site_api_tbl(i).cust_account_id;
p_rec_mtd_rec.receipt_method_id := cust_site_api_tbl(i).payment_method_id;
p_rec_mtd_rec.primary_flag := cust_site_api_tbl(i).pay_mthd_primary_flag ;
p_rec_mtd_rec.start_date := cust_site_api_tbl(i).pay_mthd_start_date;
p_rec_mtd_rec.site_use_id := cust_site_api_tbl(i).cust_site_use_id;
hz_payment_method_pub.create_payment_method( p_init_msg_list => fnd_api.g_true
, p_payment_method_rec => p_rec_mtd_rec
, x_cust_receipt_method_id => x_cust_receipt_method_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
11. APIs to create Customer Contact
-- Initialize parameters for hz_party_v2pub.create_person API
p_person_rec.person_first_name := cust_cont_api_tbl(i).contact_first_name;
p_person_rec.person_last_name := cust_cont_api_tbl(i).contact_last_name;
p_person_rec.created_by_module := G_CREATED_BY_MODULE;
hz_party_v2pub.create_person( p_init_msg_list => fnd_api.g_true
, p_person_rec => p_person_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_party_id => x_person_party_id
, x_party_number => x_person_party_number
, x_profile_id => x_person_profile_id );
p_org_contact_rec.job_title_code := cust_cont_api_tbl(i).contact_job_title_code;
p_org_contact_rec.job_title := cust_cont_api_tbl(i).contact_job_title;
p_org_contact_rec.party_rel_rec.subject_id := cust_cont_api_tbl(i).person_party_id;
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 := x_cust_party_id; --- cust_cont_api_tbl(i).party_id;
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;
p_org_contact_rec.created_by_module := G_CREATED_BY_MODULE;
hz_party_contact_v2pub.create_org_contact( p_init_msg_list => fnd_api.g_true
, p_org_contact_rec => p_org_contact_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_org_contact_id => x_org_contact_id
, x_party_rel_id => x_party_rel_id
, x_party_id => x_org_party_id
, x_party_number => x_org_party_number );
p_cust_account_role_rec.party_id := get_relation_party_id(cust_cont_api_tbl(i).party_rel_id);
p_cust_account_role_rec.cust_account_id := cust_cont_api_tbl(i).cust_account_id;
p_cust_account_role_rec.cust_acct_site_id := l_new_cust_acct_site_id;
p_cust_account_role_rec.role_type := 'CONTACT';
p_cust_account_role_rec.created_by_module := G_CREATED_BY_MODULE;
IF (cust_cont_api_tbl(i).rank = '1ST'
OR cust_cont_api_tbl(i).rank = '1') then
p_cust_account_role_rec.primary_flag := 'Y';
ELSE
p_cust_account_role_rec.primary_flag := NULL;
END IF;
hz_cust_account_role_v2pub.create_cust_account_role( p_init_msg_list => fnd_api.g_true
, p_cust_account_role_rec => p_cust_account_role_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_cust_account_role_id => x_cust_account_role_id );
p_role_responsibility_rec.cust_account_role_id := cust_cont_api_tbl(i).cust_account_role_id ;
p_role_responsibility_rec.responsibility_type := cust_cont_api_tbl(i).contact_role_code ;
p_role_responsibility_rec.created_by_module := G_CREATED_BY_MODULE;
hz_cust_account_role_v2pub.create_role_responsibility( p_init_msg_list => fnd_api.g_true
, p_role_responsibility_rec => p_role_responsibility_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_responsibility_id => x_responsibility_id );
12. Create Phone Contact Point API
p_contact_point_rec.contact_point_type := 'PHONE';
p_phone_rec.phone_area_code := cust_cont_api_tbl(i).telephone_area_code;
p_phone_rec.phone_number := cust_cont_api_tbl(i).telephone;
p_phone_rec.phone_extension := cust_cont_api_tbl(i).telephone_extension;
p_phone_rec.phone_line_type := 'GEN';
hz_contact_point_v2pub.create_contact_point ( p_init_msg_list => fnd_api.g_true
, p_contact_point_rec => p_contact_point_rec
, p_phone_rec => p_phone_rec
, p_email_rec => p_email_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_contact_point_id => x_contact_point_id );
Oracle Trading Community Architecture (TCA) is a data model that allows you to manage complex information about the parties, or customers, who belong to your commercial community, including organizations, locations, and the network of hierarchical relationships among them.
This information is maintained in the TCA Registry, which is the single source of trading community information for Oracle E-Business Suite applications. These applications, as well as TCA itself, provide user interfaces, batch data entry functionality, and other features for you to view, create, and update Registry information. See: Using Oracle Trading Community Architecture.
The key entities in TCA include:
- Parties: Entities of type Person or Organization that can enter into business relationships. Parties can also be of type Relationship. For example, Joe as himself is a party of type Person, but Joe as a contact for Vision Corporation is a party of type Relationship. Every party in the TCA Registry has a unique Registry ID.
TCA includes an extensive variety of information for parties, for example party name, addresses, contacts, and contact points. Joe as a person can have a personal phone number that differs from the phone number for the relationship of Joe as a contact. - Party sites: Addresses that parties use for specific purposes, or uses.
- Customers: Parties with whom you have a selling relationship.
- Customer accounts: The business relationships between you and your customers.
- Customer account sites: Party sites used in the context of customer accounts for specific purposes, or uses, for example ship-to and bill-to account sites.
- Locations: Geospatial points, usually defined by an address.
- Contacts: People who have a contact or employment relationship with an organization or person.
- Contact points: Means of contact, for example, phone and e-mail address.
- API to create Organization/Party
x_return_status := fnd_api.g_ret_sts_success;
p_organization_rec.organization_name := cust_api_tbl(i).party_name;
p_organization_rec.party_rec.category_code := UPPER (cust_api_tbl(i).customer_prospect_code);
p_organization_rec.party_rec.orig_system_reference := cust_api_tbl(i).orig_system_party_ref;
p_organization_rec.party_rec.attribute_category := cust_api_tbl(i).customer_attribute_category;
p_organization_rec.created_by_module := G_CREATED_BY_MODULE;
hz_party_v2pub.create_organization( p_init_msg_list => fnd_api.g_true
, p_organization_rec => p_organization_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_party_id => x_party_id
, x_party_number => x_party_number
, x_profile_id => x_org_profile_id );
2. API to create Customer Account
p_customer_profile_rec.profile_class_id := l_cust_profile_api.profile_class_id;
p_customer_profile_rec.account_status := l_cust_profile_api.account_status;
p_customer_profile_rec.auto_rec_incl_disputed_flag := l_cust_profile_api.auto_rec_incl_disputed_flag;
p_customer_profile_rec.credit_balance_statements := l_cust_profile_api.credit_balance_statements;
p_customer_profile_rec.credit_checking := l_cust_profile_api.credit_checking;
p_customer_profile_rec.credit_hold := l_cust_profile_api.credit_hold;
p_customer_profile_rec.discount_terms := l_cust_profile_api.discount_terms;
p_customer_profile_rec.disputed_transactions_flag := l_cust_profile_api.disputed_transactions_flag;
p_customer_profile_rec.dunning_letters := l_cust_profile_api.dunning_letters;
p_customer_profile_rec.override_terms := l_cust_profile_api.override_terms;
p_customer_profile_rec.percent_collectable := l_cust_profile_api.percent_collectable;
p_customer_profile_rec.statement_cycle_id := l_cust_profile_api.statement_cycle_id;
p_customer_profile_rec.send_statements := l_cust_profile_api.statements;
p_customer_profile_rec.tax_printing_option := l_cust_profile_api.tax_printing_option;
p_customer_profile_rec.standard_terms := l_cust_profile_api.standard_term_id;
p_cust_account_rec.account_number := cust_api_tbl(i).customer_number;
p_cust_account_rec.account_name := cust_api_tbl(i).customer_name;
p_cust_account_rec.orig_system_reference := cust_api_tbl(i).orig_system_customer_ref;
p_cust_account_rec.sales_channel_code := cust_api_tbl(i).sales_channel_code;
p_cust_account_rec.freight_term := cust_api_tbl(i).freight_term_code;
p_cust_account_rec.price_list_id := cust_api_tbl(i).price_list_id;
p_cust_account_rec.date_type_preference := upper(cust_api_tbl(i).request_date_type);
p_cust_account_rec.ship_via := cust_api_tbl(i).cust_ship_via_code;
p_cust_account_rec.status := cust_api_tbl(i).customer_status;
p_cust_account_rec.customer_class_code := cust_api_tbl(i).customer_class_code; --- 'INDIRECT';
p_cust_account_rec.customer_type := cust_api_tbl(i).customer_type;
p_cust_account_rec.created_by_module := G_CREATED_BY_MODULE;
p_organization_rec.party_rec.party_id := cust_api_tbl(i).party_id;
p_organization_rec.created_by_module := G_CREATED_BY_MODULE;
IF NVL(cust_api_tbl(i).status_flag,'I') <> 'C' THEN
hz_cust_account_v2pub.create_cust_account ( p_init_msg_list => fnd_api.g_true
, p_cust_account_rec => p_cust_account_rec
, p_organization_rec => p_organization_rec
, p_customer_profile_rec => p_customer_profile_rec
, p_create_profile_amt => fnd_api.g_false
, x_cust_account_id => x_cust_account_id
, x_account_number => x_account_number
, x_party_id => x_party_id
, x_party_number => x_party_number
, x_profile_id => x_profile_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data );
3. API to create Customer Profile
p_cust_profile_amt_rec_type.currency_code := l_cust_profile_api.currency_code;
p_cust_profile_amt_rec_type.overall_credit_limit := l_cust_profile_api.overall_credit_limit;
p_cust_profile_amt_rec_type.trx_credit_limit := l_cust_profile_api.trx_credit_limit;
hz_customer_profile_v2pub.create_cust_profile_amt ( p_init_msg_list => fnd_api.g_true
, p_check_foreign_key => x_cust_account_id
, p_cust_profile_amt_rec => p_cust_profile_amt_rec_type
, x_cust_acct_profile_amt_id => x_cust_acct_profile_amt_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data ) ;
4. API to create HZ location
p_location_rec.country := cust_site_api_tbl(i).country_code;
p_location_rec.city := cust_site_api_tbl(i).city;
p_location_rec.postal_code := NVL(get_lookup_county_postcode ( cust_site_api_tbl(i).postal_code,'POSTAL_CODE_MAPPING'),cust_site_api_tbl(i).postal_code);
p_location_rec.state := cust_site_api_tbl(i).state;
p_location_rec.province := cust_site_api_tbl(i).province;
p_location_rec.language := cust_site_api_tbl(i).language;
p_location_rec.county := NVL(get_lookup_county_postcode ( cust_site_api_tbl(i).county,'COUNTY_MAPPING'),cust_site_api_tbl(i).county);
p_location_rec.created_by_module := G_CREATED_BY_MODULE;
--p_location_rec.sales_tax_inside_city_limits := cust_site_api_tbl(i).sales_tax_inside_city_limits;
hz_location_v2pub.create_location( p_init_msg_list => fnd_api.g_true
, p_location_rec => p_location_rec
, x_location_id => x_location_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data );
5. API to Create Party Site
p_party_site_rec.party_id := cust_site_api_tbl(i).party_id;
p_party_site_rec.location_id := cust_site_api_tbl(i).location_id;
p_party_site_rec.language := cust_site_api_tbl(i).language;
p_party_site_rec.orig_system_reference := cust_site_api_tbl(i).orig_system_address_ref;
p_party_site_rec.created_by_module := G_CREATED_BY_MODULE;
p_party_site_rec.identifying_address_flag := cust_site_api_tbl(i).identifying_address_flag;
p_party_site_rec.party_site_number := cust_site_api_tbl(i).party_site_number;
hz_party_site_v2pub.create_party_site( p_init_msg_list => fnd_api.g_true
, p_party_site_rec => p_party_site_rec
, x_party_site_id => x_party_site_id
, x_party_site_number => x_party_site_number
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data );
6. API to create party site use
p_party_site_use_rec.site_use_type := cust_site_api_tbl(i).site_use_code;
p_party_site_use_rec.party_site_id := cust_site_api_tbl(i).party_site_id;
p_party_site_use_rec.primary_per_type := cust_site_api_tbl(i).primary_site_use_flag;
p_party_site_use_rec.created_by_module := G_CREATED_BY_MODULE;
p_party_site_use_rec.status := cust_site_api_tbl(i).cust_site_uses_status;
hz_party_site_v2pub.create_party_site_use( p_init_msg_list => fnd_api.g_true
, p_party_site_use_rec => p_party_site_use_rec
, x_party_site_use_id => x_party_site_use_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data );
7. API to create Customer Account Site
p_cust_acct_site_rec.org_id := cust_site_api_tbl(i).org_id;
p_cust_acct_site_rec.cust_account_id := cust_site_api_tbl(i).cust_account_id;
p_cust_acct_site_rec.party_site_id := cust_site_api_tbl(i).party_site_id;
p_cust_acct_site_rec.orig_system_reference := cust_site_api_tbl(i).orig_system_address_ref;
p_cust_acct_site_rec.attribute_category := cust_site_api_tbl(i).address_attribute_category;
p_cust_acct_site_rec.attribute1 := cust_site_api_tbl(i).address_attribute1;
--p_cust_acct_site_rec.language := cust_site_api_tbl(i).language; --language column is obsolete so no mapping required
p_cust_acct_site_rec.ece_tp_location_code := cust_site_api_tbl(i).edi_location;
--p_cust_acct_site_rec.status := cust_site_api_tbl(i).cust_acct_sites_status;
--p_cust_acct_site_rec.territory := cust_site_api_tbl(i).territory; -- Business asked to ignore this on 16th Sep 2013
p_cust_acct_site_rec.created_by_module := G_CREATED_BY_MODULE;
hz_cust_account_site_v2pub.create_cust_acct_site( p_init_msg_list => fnd_api.g_true
, p_cust_acct_site_rec => p_cust_acct_site_rec
, x_cust_acct_site_id => x_cust_acct_site_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data );
8. API to Create Cust Account Site Use
p_su_customer_profile_rec.profile_class_id := l_cust_site_profile_api.profile_class_id;
p_su_customer_profile_rec.account_status := l_cust_site_profile_api.account_status;
p_su_customer_profile_rec.credit_balance_statements := l_cust_site_profile_api.credit_balance_statements;
p_su_customer_profile_rec.credit_checking := l_cust_site_profile_api.credit_checking;
p_su_customer_profile_rec.credit_hold := l_cust_site_profile_api.credit_hold;
p_su_customer_profile_rec.discount_terms := l_cust_site_profile_api.discount_terms;
p_su_customer_profile_rec.disputed_transactions_flag := l_cust_site_profile_api.disputed_transactions_flag;
p_su_customer_profile_rec.dunning_letters := l_cust_site_profile_api.dunning_letters;
p_su_customer_profile_rec.override_terms := l_cust_site_profile_api.override_terms;
p_su_customer_profile_rec.percent_collectable := l_cust_site_profile_api.percent_collectable;
p_su_customer_profile_rec.statement_cycle_id := l_cust_site_profile_api.statement_cycle_id;
p_su_customer_profile_rec.send_statements := l_cust_site_profile_api.statements;
p_su_customer_profile_rec.tax_printing_option := l_cust_site_profile_api.tax_printing_option;
p_su_customer_profile_rec.standard_terms := l_cust_site_profile_api.standard_term_id;
p_cust_site_use_rec.tax_reference := cust_site_api_tbl(i).cust_tax_reference;
p_cust_site_use_rec.org_id := cust_site_api_tbl(i).org_id;
p_cust_site_use_rec.cust_acct_site_id := cust_site_api_tbl(i).cust_acct_site_id;
p_cust_site_use_rec.site_use_code := cust_site_api_tbl(i).site_use_code;
p_cust_site_use_rec.primary_flag := cust_site_api_tbl(i).primary_site_use_flag;
p_cust_site_use_rec.orig_system_reference := cust_site_api_tbl(i).orig_system_address_ref;
p_cust_site_use_rec.freight_term := cust_site_api_tbl(i).freight_term_code;
p_cust_site_use_rec.price_list_id := l_site_price_list_id; ---cust_site_api_tbl(i).price_list_id; Added By Sachin On 11-SEP-2014
p_cust_site_use_rec.demand_class_code := cust_site_api_tbl(i).demand_class_code;
p_cust_site_use_rec.status := 'A' ;
p_cust_site_use_rec.LOCATION := cust_site_api_tbl(i).LOCATION ;
p_cust_site_use_rec.territory_id := cust_site_api_tbl(i).territory_id ;
p_cust_site_use_rec.warehouse_id := cust_site_api_tbl(i).warehouse_id ;
p_cust_site_use_rec.primary_salesrep_id := cust_site_api_tbl(i).salesrep_id ;
p_cust_site_use_rec.created_by_module := G_CREATED_BY_MODULE;
hz_cust_account_site_v2pub.create_cust_site_use( p_init_msg_list => fnd_api.g_true
, p_cust_site_use_rec => p_cust_site_use_rec
, p_customer_profile_rec => p_su_customer_profile_rec
, p_create_profile => fnd_api.g_true
, p_create_profile_amt => fnd_api.g_false
, x_site_use_id => x_site_use_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data );
9. API to create Customer Profile
p_cust_profile_amt_rec_type.currency_code := l_cust_site_profile_api.currency_code;
p_cust_profile_amt_rec_type.overall_credit_limit := l_cust_site_profile_api.overall_credit_limit;
p_cust_profile_amt_rec_type.trx_credit_limit := l_cust_site_profile_api.trx_credit_limit;
p_cust_profile_amt_rec_type.attribute4 := l_cust_site_profile_api.amount_attribute4;
p_cust_profile_amt_rec_type.cust_account_profile_id := l_cust_account_profile_id;
p_cust_profile_amt_rec_type.cust_account_id := cust_site_api_tbl(i).cust_account_id;
hz_customer_profile_v2pub.create_cust_profile_amt (p_init_msg_list => fnd_api.g_true,
p_check_foreign_key => cust_site_api_tbl(i).cust_account_id,
p_cust_profile_amt_rec => p_cust_profile_amt_rec_type,
x_cust_acct_profile_amt_id => x_cust_acct_profile_amt_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
) ;
10. API to create Customer Payment Method
p_rec_mtd_rec.cust_account_id := cust_site_api_tbl(i).cust_account_id;
p_rec_mtd_rec.receipt_method_id := cust_site_api_tbl(i).payment_method_id;
p_rec_mtd_rec.primary_flag := cust_site_api_tbl(i).pay_mthd_primary_flag ;
p_rec_mtd_rec.start_date := cust_site_api_tbl(i).pay_mthd_start_date;
p_rec_mtd_rec.site_use_id := cust_site_api_tbl(i).cust_site_use_id;
hz_payment_method_pub.create_payment_method( p_init_msg_list => fnd_api.g_true
, p_payment_method_rec => p_rec_mtd_rec
, x_cust_receipt_method_id => x_cust_receipt_method_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
11. APIs to create Customer Contact
-- Initialize parameters for hz_party_v2pub.create_person API
p_person_rec.person_first_name := cust_cont_api_tbl(i).contact_first_name;
p_person_rec.person_last_name := cust_cont_api_tbl(i).contact_last_name;
p_person_rec.created_by_module := G_CREATED_BY_MODULE;
hz_party_v2pub.create_person( p_init_msg_list => fnd_api.g_true
, p_person_rec => p_person_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_party_id => x_person_party_id
, x_party_number => x_person_party_number
, x_profile_id => x_person_profile_id );
p_org_contact_rec.job_title_code := cust_cont_api_tbl(i).contact_job_title_code;
p_org_contact_rec.job_title := cust_cont_api_tbl(i).contact_job_title;
p_org_contact_rec.party_rel_rec.subject_id := cust_cont_api_tbl(i).person_party_id;
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 := x_cust_party_id; --- cust_cont_api_tbl(i).party_id;
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;
p_org_contact_rec.created_by_module := G_CREATED_BY_MODULE;
hz_party_contact_v2pub.create_org_contact( p_init_msg_list => fnd_api.g_true
, p_org_contact_rec => p_org_contact_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_org_contact_id => x_org_contact_id
, x_party_rel_id => x_party_rel_id
, x_party_id => x_org_party_id
, x_party_number => x_org_party_number );
p_cust_account_role_rec.party_id := get_relation_party_id(cust_cont_api_tbl(i).party_rel_id);
p_cust_account_role_rec.cust_account_id := cust_cont_api_tbl(i).cust_account_id;
p_cust_account_role_rec.cust_acct_site_id := l_new_cust_acct_site_id;
p_cust_account_role_rec.role_type := 'CONTACT';
p_cust_account_role_rec.created_by_module := G_CREATED_BY_MODULE;
IF (cust_cont_api_tbl(i).rank = '1ST'
OR cust_cont_api_tbl(i).rank = '1') then
p_cust_account_role_rec.primary_flag := 'Y';
ELSE
p_cust_account_role_rec.primary_flag := NULL;
END IF;
hz_cust_account_role_v2pub.create_cust_account_role( p_init_msg_list => fnd_api.g_true
, p_cust_account_role_rec => p_cust_account_role_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_cust_account_role_id => x_cust_account_role_id );
p_role_responsibility_rec.cust_account_role_id := cust_cont_api_tbl(i).cust_account_role_id ;
p_role_responsibility_rec.responsibility_type := cust_cont_api_tbl(i).contact_role_code ;
p_role_responsibility_rec.created_by_module := G_CREATED_BY_MODULE;
hz_cust_account_role_v2pub.create_role_responsibility( p_init_msg_list => fnd_api.g_true
, p_role_responsibility_rec => p_role_responsibility_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_responsibility_id => x_responsibility_id );
12. Create Phone Contact Point API
p_contact_point_rec.contact_point_type := 'PHONE';
p_phone_rec.phone_area_code := cust_cont_api_tbl(i).telephone_area_code;
p_phone_rec.phone_number := cust_cont_api_tbl(i).telephone;
p_phone_rec.phone_extension := cust_cont_api_tbl(i).telephone_extension;
p_phone_rec.phone_line_type := 'GEN';
hz_contact_point_v2pub.create_contact_point ( p_init_msg_list => fnd_api.g_true
, p_contact_point_rec => p_contact_point_rec
, p_phone_rec => p_phone_rec
, p_email_rec => p_email_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_contact_point_id => x_contact_point_id );
No comments:
Post a Comment