Search This Blog

Wednesday, March 18, 2009

Customer Import using API's

Following API's are used for creating customers
1) The first step is to create Party. hz_party_v2pub.create_organization is used to create a party.
2) Once party is created then the customer accounts should be created. hz_cust_account_v2pub.create_cust_account API is used to create Customer Accounts. The p_organization_rec should have party information. The orig_system_reference for p_cust_account_Rec should be same as parties orig_system_reference.
3) Now that party and accounts are created, customer account sites and its uses should be created. But before that location and party_sites should be created and attached to party.
4) hz_location_v2pub.create_location API is used to create location. This is a simple API that takes address table type as input and returns location_id as a OUT parameter.
5) API hz_party_site_v2pub.create_party_site is used to create party_site. The party_id created in step 1 and location_id created in step 4 is passed in the party_site_rec parameter. This will return party_site_id as a OUT parameter.
6) Now that we have created the party_sites, its time to create customer site using API hz_cust_account_site_v2pub.create_cust_acct_site. The cust_account_id created in step 2 and party_site_id created in step 5 is inputted in the cust_acct_site_rec record type. This returns cust_acct_site_id as a OUT parameter.
7) The site use(SHIP_TO, BILL_TO etc.) can be created using API hz_cust_account_site_v2pub.create_cust_site_use. The cust_acct_site_id created in step 6
8) For BILL_TO the customer profiles can be created using API hz_customer_profile_v2pub.create_customer_profile

Tuesday, March 10, 2009

Concurrent Program Name with Parameter, Value set

On request here is the query to list concurrent program name with its parameter, values set and default value/type


SELECT fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, par.column_seq_num
, par.end_user_column_name
, par.form_left_prompt prompt
, par.enabled_flag
, par.required_flag
, par.display_flag
, par.flex_value_set_id
, ffvs.flex_value_set_name
, flv.meaning default_type
, par.DEFAULT_VALUE
FROM fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl par
, fnd_flex_value_sets ffvs
, fnd_lookup_values flv
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = :conc_prg_name
AND fcpl.LANGUAGE = 'US'
AND par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
AND ffvs.flex_value_set_id = par.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = par.default_type
AND flv.LANGUAGE(+) = USERENV ('LANG')
ORDER BY par.column_seq_num

Friday, March 6, 2009

Query to get Customer Name, Number and Address

Below query can be handy to get customer related information.
The query will list Party Name, Number, Customer Number and there Bill To and Ship Addresses.


SELECT hp.party_name
, hp.party_number
, hca.account_number
, hca.cust_account_id
, hp.party_id
, hps.party_site_id
, hps.location_id
, hl.address1
, hl.address2
, hl.address3
, hl.city
, hl.state
, hl.country
, hl.postal_code
, hcsu.site_use_code
, hcsu.site_use_id
, hcsa.bill_to_flag
FROM hz_parties hp
, hz_party_sites hps
, hz_locations hl
, hz_cust_accounts_all hca
, hz_cust_acct_sites_all hcsa
, hz_cust_site_uses_all hcsu
WHERE hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hp.party_id = hca.party_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
AND hca.account_number = :customer_number


P.S. The query is not completely tested. Please let me know if you find any problem

Copyright (c) All rights reserved. Presented by Suresh Vaishya