Total Pageviews

Saturday, March 9, 2013



/* 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

1 comment: