Record Type Variable Declaration
v_organization_rec HZ_PARTY_V2PUB.organization_rec_type;
v_customer_account_rec HZ_CUST_ACCOUNT_V2PUB.cust_account_rec_type;
v_party_rec HZ_PaRTY_V2PUB.party_rec_type;
v_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
v_location_rec hz_location_v2pub.location_rec_type;
v_party_site_rec HZ_PARTY_SITE_V2PUB.party_site_rec_type;
v_party_site_use_rec HZ_PARTY_SITE_V2PUB.party_site_use_rec_type;
v_customer_profile_amt HZ_CUSTOMER_PROFILE_V2PUB.cust_profile_amt_rec_type;
v_cust_acct_site_rec HZ_CUST_ACCOUNT_SITE_V2PUB.cust_acct_site_rec_type;
v_customer_site_use_rec HZ_CUST_ACCOUNT_SITE_V2PUB.cust_site_use_rec_type;
v_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
v_phone_rec hz_contact_point_v2pub.phone_rec_type;
v_person_rec hz_party_v2pub.person_rec_type;
v_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;
Note- Columns values are assigned to record type variable from various Cursor Staging Tables
-- Create the Party Organization Record
v_organization_rec.organization_name := rec_party.customer_name;
v_organization_rec.party_rec.orig_system_reference := rec_party.orig_system_reference ;
v_organization_rec.party_rec.attribute2 := rec_party.attribute2;
v_organization_rec.party_rec.attribute3 := rec_party.attribute3;
v_organization_rec.party_rec.attribute4 := rec_party.attribute4;
v_organization_rec.party_rec.attribute5 := rec_party.attribute5;
v_organization_rec.created_by_module := 'TCA_V1_API';
-- Create Customer Account Record
v_customer_account_rec.account_name:= rec_party.customer_name;
v_customer_account_rec.account_number:= rec_party.customer_number;
v_customer_account_rec.orig_system_reference := rec_party.orig_system_reference;
v_customer_account_rec.created_by_module:='TCA_V1_API';
-- Create Customer Profile Record
v_customer_profile_rec.party_id := v_party_id;
v_customer_profile_rec.status := rec_profile.status;
v_customer_profile_rec.credit_checking := rec_profile.credit_checking;
v_customer_profile_rec.tolerance := rec_profile.tolerance;
v_customer_profile_rec.discount_terms := rec_profile.discount_terms;
v_customer_profile_rec.dunning_letters := rec_profile.dunning_letters;
v_customer_profile_rec.interest_charges := rec_profile.interest_charges;
v_customer_profile_rec.credit_balance_statements := rec_profile.credit_balance_statements;
v_customer_profile_rec.credit_hold := rec_profile.credit_hold;
v_customer_profile_rec.credit_rating := rec_profile.credit_rating;
v_customer_profile_rec.risk_code := rec_profile.risk_code;
v_customer_profile_rec.override_terms := rec_profile.override_terms;
v_customer_profile_rec.payment_grace_days := rec_profile.payment_grace_days;
v_customer_profile_rec.discount_grace_days := rec_profile.discount_grace_days;
v_customer_profile_rec.account_status := rec_profile.account_status;
v_customer_profile_rec.auto_rec_incl_disputed_flag := rec_profile.auto_rec_incl_disputed_flag;
v_customer_profile_rec.tax_printing_option := rec_profile.tax_printing_option;
v_customer_profile_rec.jgzz_attribute5 := rec_profile.jgzz_attribute5;
v_customer_profile_rec.jgzz_attribute6 := rec_profile.jgzz_attribute6;
v_customer_profile_rec.created_by_module := 'TCA_V1_API';
Below API creates record for Party organization, customer account and customer profile all in one
HZ_CUST_ACCOUNT_V2PUB.create_cust_account
(
p_init_msg_list =>FND_API.G_FALSE,
p_cust_account_rec => v_customer_account_rec, -- Customer Account Record
p_organization_rec => v_organization_rec, -- Party Organization Record
p_customer_profile_rec => v_customer_profile_rec, -- Customer Profile Record
p_create_profile_amt => FND_API.G_FALSE,
x_cust_account_id => v_customer_account_id,
x_account_number => v_customer_account_number,
x_party_id => v_party_id,
x_party_number => v_party_number,
x_profile_id => v_profile_id,
x_return_status =>v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
-- Create Customer Profile Amount Record
v_customer_profile_amt.cust_account_profile_id := v_cust_account_profile_id;
v_customer_profile_amt.cust_account_id := v_customer_account_id;
v_customer_profile_amt.currency_code := rec_customer_prof_amt_staging.currency_code;
v_customer_profile_amt.trx_credit_limit := rec_customer_prof_amt_staging.trx_credit_limit;
v_customer_profile_amt.overall_credit_limit := rec_customer_prof_amt_staging.overall_credit_limit;
v_customer_profile_amt.min_dunning_amount := rec_customer_prof_amt_staging.min_dunning_amount;
v_customer_profile_amt.min_statement_amount := rec_customer_prof_amt_staging.min_statement_amount;
v_customer_profile_amt.created_by_module := 'TCA_V1_API';
HZ_CUSTOMER_PROFILE_V2PUB.create_cust_profile_amt
(
p_init_msg_list => 'T' ,
p_check_foreign_key => FND_API.G_TRUE,
p_cust_profile_amt_rec => v_customer_profile_amt,
x_cust_acct_profile_amt_id => v_cust_act_prof_amt_id,
x_return_status =>v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
-- Create Location Record
v_location_rec.address1 := rec_addresses.address1;
v_location_rec.address2 := rec_addresses.address2;
v_location_rec.address3 := rec_addresses.address3;
v_location_rec.address4 := rec_addresses.address4;
v_location_rec.city := rec_addresses.city;
v_location_rec.country := rec_addresses.country;
v_location_rec.orig_system_reference := rec_addresses.orig_address_reference;
v_location_rec.postal_code := rec_addresses.postal_code;
v_location_rec.created_by_module :='TCA_V1_API';
HZ_LOCATION_V2PUB.create_location
(
p_init_msg_list => 'T',
p_location_rec => v_location_rec,
x_location_id => v_location_id,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
-- Create Party Site Record
v_party_site_rec.party_id := v_party_id;
v_party_site_rec.location_id := v_location_id;
v_party_site_rec.orig_system_reference := rec_addresses.orig_address_reference;
v_party_site_id := NULL;
v_party_site_rec.created_by_module := 'TCA_V1_API';
HZ_PARTY_SITE_V2PUB.create_party_site
(
p_init_msg_list => 'T',
p_party_site_rec => v_party_site_rec,
x_party_site_id => v_party_site_id,
x_party_site_number => v_party_site_no,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
-- Create Contact Point and Phone Record
v_contact_point_rec.contact_point_type := 'PHONE';
v_contact_point_rec.status := rec_phones.status;
v_contact_point_rec.owner_table_name := 'HZ_PARTIES';
v_contact_point_rec.owner_table_id := v_party_id;
v_contact_point_rec.primary_flag := rec_phones.primary_flag;
v_contact_point_rec.orig_system_reference := rec_phones.orig_system_reference;
v_contact_point_rec.created_by_module := 'TCA_V1_API';
v_phone_rec.phone_number := rec_phones.phone_number;
v_phone_rec.phone_line_type := rec_phones.phone_type;
HZ_CONTACT_POINT_V2PUB.create_contact_point
(
p_init_msg_list => 'T',
p_contact_point_rec => v_contact_point_rec,
p_phone_rec => v_phone_rec,
x_contact_point_id => v_contact_point_party_id,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
-- Create Person Record
v_person_rec.person_first_name := rec_contacts.first_name;
v_person_rec.person_last_name := rec_contacts.last_name;
v_person_rec.party_rec.orig_system_reference := rec_contacts.orig_system_reference;
v_person_rec.created_by_module := 'TCA_V1_API';
HZ_PARTY_V2PUB.create_person
(
p_init_msg_list => 'T',
p_person_rec => v_person_rec,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
x_party_id => v_person_party_id,
x_party_number => v_person_party_number,
x_profile_id => v_profile_id
);
-- Create Org Contact Record
v_org_contact_rec.created_by_module := 'TCA_V1_API';
v_org_contact_rec.job_title := rec_contacts.job_title;
v_org_contact_rec.party_rel_rec.subject_id := v_person_party_id ;
v_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
v_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
v_org_contact_rec.party_rel_rec.object_id := v_party_id ;
v_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
v_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
v_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
v_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
v_org_contact_rec.party_rel_rec.start_date := SYSDATE;
HZ_PARTY_CONTACT_V2PUB.create_org_contact
(
p_init_msg_list => 'T',
p_org_contact_rec => v_org_contact_rec,
x_org_contact_id => v_org_contact_id,
x_party_rel_id => v_party_rel_id,
x_party_id => v_party_contact_id,
x_party_number => v_party_rel_number,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
) ;
-- Create Party Site Use Record
v_party_site_use_rec.site_use_type := rec_customer_sites_staging.site_use_code;
v_party_site_use_rec.party_site_id := v_party_site_id;
v_party_site_use_rec.primary_per_type := rec_customer_sites_staging.primary_flag;
v_party_site_use_rec.status := rec_customer_sites_staging.status;
v_party_site_use_rec.created_by_module := 'TCA_V1_API';
HZ_PARTY_SITE_V2PUB.create_party_site_use
(
p_init_msg_list => 'T',
p_party_site_use_rec => v_party_site_use_rec,
x_party_site_use_id => v_party_site_use_id ,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
-- Create Customer Account Site Record
v_cust_acct_site_rec.cust_account_id := v_customer_account_id;
v_cust_acct_site_rec.party_site_id := v_party_site_id;
v_cust_acct_site_rec.orig_system_reference := rec_addresses.orig_address_reference;
v_cust_acct_site_rec.status := rec_customer_sites_staging.status;
v_cust_acct_site_rec.created_by_module := 'TCA_V1_API';
HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_acct_site
(
p_init_msg_list => 'T',
p_cust_acct_site_rec=> v_cust_acct_site_rec,
x_cust_acct_site_id => v_cust_acct_site_id,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
-- Create Customer Account Site Use Record
v_customer_site_use_rec.cust_acct_site_id := v_cust_acct_site_id;
v_customer_site_use_rec.site_use_code := rec_customer_sites_staging.site_use_code;
v_customer_site_use_rec.status := rec_customer_sites_staging.status;
v_customer_site_use_rec.orig_system_reference :=rec_addresses.orig_address_reference;
v_customer_site_use_rec.primary_flag :=rec_customer_sites_staging.primary_flag;
v_customer_site_use_rec.created_by_module := 'TCA_V1_API';
HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_site_use
(
p_init_msg_list => 'T',
p_cust_site_use_rec => v_customer_site_use_rec,
p_customer_profile_rec => v_customer_profile_rec,
p_create_profile => FND_API.G_FALSE,
p_create_profile_amt => FND_API.G_FALSE,
x_site_use_id => v_site_use_id,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
-- Create Customer Profile Amount Record
v_customer_profile_amt := NULL;
v_customer_profile_amt.cust_account_profile_id := v_cust_account_profile_id;
v_customer_profile_amt.cust_account_id := v_customer_account_id;
v_customer_profile_amt.currency_code := rec_cust_prof_amt_stage_sites.currency_code;
v_customer_profile_amt.trx_credit_limit := rec_cust_prof_amt_stage_sites.trx_credit_limit;
v_customer_profile_amt.overall_credit_limit := rec_cust_prof_amt_stage_sites.overall_credit_limit;
v_customer_profile_amt.min_dunning_amount := rec_cust_prof_amt_stage_sites.min_dunning_amount;
v_customer_profile_amt.min_statement_amount := rec_cust_prof_amt_stage_sites.min_statement_amount;
v_customer_profile_amt.site_use_id := v_site_use_id;
v_customer_profile_amt.created_by_module := 'TCA_V1_API';
HZ_CUSTOMER_PROFILE_V2PUB.create_cust_profile_amt
(
p_init_msg_list => 'T' ,
p_check_foreign_key => FND_API.G_TRUE,
p_cust_profile_amt_rec => v_customer_profile_amt,
x_cust_acct_profile_amt_id => v_cust_act_prof_amt_id,
x_return_status =>v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
-- If you want to Create Organization separately in some other program, use below API for reference
HZ_PARTY_V2PUB.create_organization
(
p_init_msg_list => 'T',
p_organization_rec => v_organization_rec,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
x_party_id => v_party_id,
x_party_number => v_party_number,
x_profile_id => v_profile_id
);
There is an issue here.
ReplyDeleteHZ_PARTY_V2PUB.create_organization creates an organization party, and the party id is passed out into v_party_id.
However, the call to HZ_CUST_ACCOUNT_V2PUB.create_cust_account creates the account, but also creates the organization party for a second time aswell!
So the party id that goes into v_party_id this time, is different to that in the first step, and so you end up with 2 org parties for the same org.
Thanks for the correction...I have updated the post now as per your commennt
ReplyDeleteAmit
Hi Amit,
ReplyDeleteI had a requirement to populate the "Credit Limit"
at the Customer site level. All the documentation and examples that i referred did not give me a clear answer.
I finally found it in this site !!!!.
Thanks a lot Amit. You have really made my day.
May you continue to provide such helpful scripts and solutions.
Thanks & regards
Rajagopal Iyer
Thanks Rajgopal...I will continue to update this site based on my knowledge and experience...
ReplyDeleteHi, what API (not interface table) should be used to create or update an AR customer's bank account?
ReplyDeleteHello EbusDeveloper,
DeleteDid you get answer to your question? I need to find out how to same. Kindly post here if you have the answer.
Thanks in advance,
Femi
Hi,
ReplyDeleteWhat API can populate/update/create the date_of_birth and the person_identifier of hz_person_profiles?
Hi,
ReplyDeleteAny one have Full customer conversion script?
If yes please send it to me, jagadekar@gmail.com
kindly mail me the code for importing data from staging table to interface tables of customer
ReplyDeleteon this email id:-deepak.mishra@hyberit.com