/* CUSTOMER INFORMATION : Actually for each customer that is created, an account is created as well(hz_cust_accounts) and the corresponding sites are hz_cust_acct_sites. And the customer can make any of these sites as billto,shipto etc. These are called party site uses. And this information goes into hz_cust_site_uses. Not quite sure why two different sets of tables are maintained.?? Actually historically all the customer information is owned and stored by AR.So the data is stored in tables like ra_customers, ra_contacts etc. However since customer information is shared universally by all products a central common repository of customer data is needed which is what is TCA. All the tca tables start with hz_ i.e hz_parties, hz_party_sites etc. And the ra_customers etc tables converted now into view from tables and these views will now base on these hz related tables. A Word About Oracle TCA (Trading Community Architecture). Before we explain the TCA,let us take an example of a company like cisco which uses multiple tools and applications over the years and has generated the customer data in multiple applications(called Source Systems). Hence if it has to answer the question of what are my top 150 customers , then it needs to have a single comprehensive repository of Customer information. Here this single repository of customers will be called Parties. So more than one source system customers will be mapped to Parties or customer to party is a many-to-one relationship. Oracle TCA stores the parties in a table hz_parties. However while creating a customer using the Oracle 11i forms, a record is created both in ra_customers as wells as hz_parties. And it will store the customer id in ra_customers as the orig_system_reference in the hz_parties table. A Party is an entity that can enter into business and can be of type Organization or Person. A Customer is of type Oraganization or Person with whom you have a selling relationship. A customer account represents the business relationship between one party and another. As an ex,you can have a commercial account and reseller account for a party (ex Vision Distribution). Customer Creation in Oracle AR : ------------------------------- This can be done simply from the (customers => summary). While entering the customer information, we need to give a whole bunch of mandatory and optional information like taxpayerid, profile, freight terms(shipping terms). A profile class basically gives us the informationn about how good a customer is (from customers menu), who should be the collector, where the dunning letters and statements to be sent,payment terms, finance charges for overdue invoices etc. Now for each customer we can enter more than one addresses or multiple addresses.The communication and contact information entered for the customer is different from the comm and contact information for each of the customer addresses. Now suppose say we have entered all the information for a customer,like comm,contact etc and also two addresses. Now we open one of these addresses and create a business purpose for this address i.e whether this business should be used as a bill_to or ship_to etc and enter the corresponding sales territory information for the bill to address. */ -- This table stores all the customers that are created. select customer_id,customer_name, customer_number, orig_system_reference,status, party_id, party_type, party_last_update_date from ra_customers where customer_id = 331317 /* While creating the customers, we need to create the customer addresses which are called the locations and they are stored in */ select *--location_id,address1, city,state, country, orig_system_reference, validated_flag,application_id,creation_date from hz_locations where address1 like '700 SILVER SEVEN RD%' order by creation_date desc -- 170, 172 -- Parties are created select * --party_id,party_name,party_number,party_type,validated_flag, orig_system_reference ,creation_date from hz_parties order by creation_date desc /* Party_sites stores the relation ship between the parties and locations and the relation ship is a many-many relationship. i.e 1 party can have many locations(or addresses) and 1 address can be used by many parties.*/ select party_site_id, party_site_number, party_site_name, party_id, location_id, orig_system_reference, creation_date, status from hz_party_sites order by creation_date desc /* The hz_party_site_uses table stores information about how a party site is used. Party sites can have multiple uses, for example Ship-To and Bill-To.*/ select party_site_use_id, party_site_id,site_use_type,application_id, creation_date from hz_party_site_uses /* For every customer that is created, a customer account is also created. In general, each party can have multiple accounts like one for mfg, one for distribution.Similarly an individual can have multiple accounts like personal,family. */ select cust_account_id, orig_system_reference,status,customer_type, party_id,account_number,creation_date from hz_cust_accounts where 1 = 1 --and cust_account_id = 331317 and account_number = '206700' order by creation_date desc select * from ar.hz_cust_acct_sites_all where cust_account_id = 331317 select location,site_use_id, cust_acct_site_id, site_use_code, status,bill_to_site_use_id,orig_system_reference,org_id ,location from ar.hz_cust_site_uses_all where cust_acct_site_id = 501995 -- where cust_acct_site_id = 466132 begin fnd_client_info.set_org_context(fnd_profile.value('ORG_ID')); end; select set_of_books_id, rowid from ar_system_parameters /*So having done all the above stuff, we should be able to succesfully create an transaction batch and an invoice in it. The data goes into the "ra_customer_trx_all" and if the lines are also created, the info goes into "ra_customer_trx_lines_all" Just as we define the banks as internal or external ,even customers can be defined as Internal or External. Internal Customers : Internal means you can define your own company as a customer,so that we can place internal sales orders. External customers : are the which are external. Whether it is internal or external customer, when we are creating a ship_to purpose for a particular address we can associate it to a location which is inventory location. That is we can understand it as, this particular customer will be shipped from this particular inventory location. */ --Difference between bill-to, ship-to ,sold-to etc. : /* Bill-To : is the customer who the order is billed to. he will pay the money Ship-To : is the customer who the order is shipped to . He will get the items,products of the order. Sold-To : is generally the customer who is the main parent company.ex, if the AT&T Cables has placed an order, then AT&T can be the Sold-To company, which is the main parent company. I believe the contacts are only defined for the sites, i.e for the bill-to and ship-to sites. */ /* Parties and Org Id's (Operating Units)*/ If a party is setup in an operating unit under a business group, will the party name be visible to other operating units under a different business group. This makes us feel that the party id is striped by org id. A party is not OU specific. when we create a party it will be visible in all the OU's, but its sites will only be visible in the OU in which they are created i.e. if a party has a party site in X OU,then this site will only be visible in X OU and not in any other OU. Relationship Manager : TCA Registry, which is the single source of trading community information for Oracle E-Business Suite applications. The key entities in the TCA are Parties ,Party sites, customers, Customer accounts, customer account sites, location,contacts, contact points. Bulk Data Import : Import Batch to TCA registry : this program basically imports the data at the party level and NOT the customer level. If you want tcustomer level data to be imported, use the customer interface. There are two kinds of import Party Import and Customer Import. The import that you do in the TCA is Party Import. Customer Import is what you do in AR Receivables. Some of the interface tables that are used in the Bulk data import are hz_imp_parties_int hz_imp_contacts_int After the data is loaded into the interface tables, we run the Import program. One thing we need to remember is that the customer import that we run in the AR does not care about this import. So when the customer import runs, it will create a customer and whenever it creates a new customer, it also creates a new party, even though if there is an associated party existing. This can result in the duplication of the parties. And for the same reason, we have the de-duplication programs. Import Batch to TCA Registry => HZ_BATCH_IMPORT_PKG.import_batch Customer Interface
Total Pageviews
Saturday, March 9, 2013
Subscribe to:
Post Comments (Atom)
Choose font carefully
ReplyDelete