Thursday, July 30, 2015

Oracle Applications R12 Trading Community Architecture ( TCA )

Background

What is TCA?
Oracle Trading Community Architecture (TCA) is a data model that allows you to manage complex information about the parties, or customers.
Trading Community Architecture is the implementation of technology and applications to allow users to create and maintain relationships among entities. It is a way to understand who your customer interacts with inside and outside the enterprise.

The code

PARTIES:

The related tables are as follows:-
HZ_PARTIES : Stores information about parties.
HZ_FINANCIAL_PROFILE : Stores information about the financial accounts.
HZ_CREDIT_RATINGS : Stores information about the credit rating of parties
HZ_REFERENCES : Stores information about reference given by one party about another.
HZ_CERTIFICATIONS : Stores information about the certifications given by other parties.

PARTIES – TYPE PERSON:

The tables are as follows:-
HZ_PERSON_PROFILES : Stores details information about people.
HZ_PERSON_LANGUAGES :Stores information about the language that a person speaks, reads or writes
HZ_PERSON_INTEREST : Stores information about a person’s personal interests.
HZ_ CITIZENSHIP : Stores information about a person’s claimed nationality.
HZ_EDUCATIONS : Store information about a person educations.
HZ_EMPLOYMENT_HISTORY : Stores information about where the person has been employed.

PARTIES – TYPE ORGANIZATION:

The tables are as follows:-
HZ_ORGANIZATION_PROFILES : Stores details information about credit rating, financial statistics, socio-economic and corporate linkage information.
HZ_STOCK_MARKETS :Stores information about the selling and buying of financial instruments.
HZ_SECURITY_ISSUED : Stores information about financial instruments such as stocks and bonds that has been issued by the organization.
HZ_INDUSTRIAL_CLASS_APP : It is a intersection tables that link industrial classifications stores in HZ_INDUSTRIAL_CLASSES .
HZ_INDUSTRIAL_CLASSES : Stores information about names and descriptions of industrial classifications.
HZ_FINANCIAL_REPORTS : Store information details of financial reports that describe the financial status of the party.
HZ_INDUSTRIAL_REFERENCE : Stores information about industrial reference for organization.

CUSTOMER ACCOUNTS:

The tables are as follows:-
HZ_CUST_ACCOUNTS : Stores information about the relationship, if a party becomes a customer. Basically stores information about customer accounts.
HZ_CUST_ACCT_SITES_ALL : Stores information about customer sites. One customer can have more then multiple sites.
HZ_CUST_SITE_USES_ALL : Stores information about site uses or business purpose. A Single customer site can have multiple sites uses such as Bill To or Ship To.
HZ_CUST_ACCT_RELATE_ALL : Stores information about relationships between customer accounts.
HZ_CUST_ACCOUNT_ROLES : Stores information about the roles that parties perform in customer accounts.
HZ_BILLING_PREFERENCES : It describe the invoicing format preferred by customer accounts or customer account sites.
HZ_CUSTOMER_PROFILES : Stores credit information for a customer account and customer account sites.
HZ_CUST_PROFILE_AMTS : Stores profile amount limits for every currency defined for a customer account or customer account site profile.
HZ_CUST_PROF_CLASS_AMTS :Stores customer profile class amount limits for currency.
HZ_CUST_PROFILE_CLASSES : Stores standard credit profile classes.

CONTACT POINTS:

The tables are as follows:-
HZ_CONTACT_POINTS : Stores electronic methods of communicating with entities such as parties, party site. Each record in this table represents s different means of contacting an entity.
HZ_CUST_CONTACT_POINTS : This table is used to tie a contact point to a customer account, customer account site or customer account role.
HZ_CONTACT_RESTRICTIONS : It stores information about restrictions on contacting parties.

SITES/LOCATIONS:

The tables are as follows:-
HZ_PARTIES_SITES : Stores information about parties and locations. Because there is a many-to-many relationship between parties and locations.
HZ_PARTIES_SITE_USES : Stores information about the party site uses and business purposes. A party site can have multiple site uses such as ‘bill to’ or ‘ship to’ and each site is stores as a record in this table.
HZ_LOCATIONS : Stores information about the locations, namely, address information
HZ_LOC_ASSIGNMENTS : It ties locations stored in HZ_LOCATIONS to a LOC_ID stored in AR_LOCATIONS_COMBINATIONS.

Entity Relationship Diagram:







--=============================================================================
-- Filename   : Customer Data Query
-- Language   : SQL
-- Module     : AR
-- Purpose    : Lists Customer (Party), Customer Account, and Customer Site
--              related information.
--=============================================================================
SELECT
       ----------------------------------------------------------
       -- Party Information
       ----------------------------------------------------------
       hp.party_number                      "Registry ID",
       hp.party_name                        "Party Name",
       hp.party_type                        "Party Type",
       DECODE(hp.status,
              'A''Active',
              'I''Inactive',
              hp.status)                    "Party Status",
       ----------------------------------------------------------
       -- Account Information
       ----------------------------------------------------------
       hca.account_number                   "Account Number",
       DECODE(hca.status,
              'A''Active',
              'I''Inactive',
              hca.status)                   "Account Status",
       hca.account_name                     "Account Description",
       hca.customer_class_code              "Classification",
       DECODE(hca.customer_type,
              'R''External',
              'I''Internal',
              hca.customer_type)            "Account Type",
       ----------------------------------------------------------
       -- Site Information
       ----------------------------------------------------------
       hps.party_site_number                "Customer Site Number",
       DECODE(hcas.status,
              'A''Active',
              'Inactive')                   "Site Status",
       DECODE(hcas.bill_to_flag,
              'P''Primary',
              'Y''Yes',
              hcas.bill_to_flag)            "Bill To Flag",
       DECODE(hcas.ship_to_flag,
              'P''Primary',
              'Y''Yes',
              hcas.ship_to_flag)            "Ship To Flag",
       hcas.cust_acct_site_id               "Customer Acct Site ID",
       ----------------------------------------------------------
       -- Address Information
       ----------------------------------------------------------
       hl.address1                          "Address1",
       hl.address2                          "Address2",
       hl.address3                          "Address3",
       hl.address4                          "Address4",
       hl.city                              "City",
       hl.state                             "State",
       hl.postal_code                       "Zip Code",
       ter.name                             "Territory",
       ----------------------------------------------------------
       -- DFF Information (specific to client)
       ----------------------------------------------------------
       hcas.attribute4                      "SMG Key",
       hcas.attribute8                      "GLN Key",
       hca.attribute3                       "Credit Approval Date",
       hca.attribute7                       "Credit Approved By",
       hca.attribute4                       "Acct Opened Date",
       hca.attribute5                       "Credit Collection Status",
       hca.attribute1                       "BPCS Last Trx Date",
       hca.attribute2                       "BPCS Avg Pay Days",
       hca.attribute6                       "BPCS RCM Reference",
       ----------------------------------------------------------
       -- Collector Information
       ----------------------------------------------------------
       col.name                             "Collector Name",
       ----------------------------------------------------------
       -- Account Profile Information
       ----------------------------------------------------------
       hcp.credit_checking                  "Credit Check Flag",
       hcp.credit_hold                      "Credit Hold Flag",
       hcpa.auto_rec_min_receipt_amount     "Min Receipt Amount",
       hcpa.overall_credit_limit            "Credit Limit",
       hcpa.trx_credit_limit                "Order Credit Limit",
       ----------------------------------------------------------
       -- Attachment Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM fnd_documents_vl doc,
                   fnd_lobs         blo,
                   fnd_attached_documents att
             WHERE doc.media_id = blo.file_id
               AND doc.document_id = att.document_id
               AND att.entity_name = 'AR_CUSTOMERS'
               AND att.pk1_value   = hca.cust_account_id
               AND ROWNUM = 1), 'N'
       ) "Attachment Flag",
       ----------------------------------------------------------
       -- Party Relationship Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM hz_cust_acct_relate_all hzcar
             WHERE hzcar.cust_account_id = hca.cust_account_id
               AND hzcar.relationship_type = 'ALL'
               AND ROWNUM = 1), 'N'
       ) "Party Relationship Flag",
       ----------------------------------------------------------
       -- Account Relationship Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM hz_cust_acct_relate_all hzcar
             WHERE hzcar.cust_account_id = hca.cust_account_id
               AND ROWNUM = 1), 'N'
       ) "Account Relationship Flag",
       ----------------------------------------------------------
       -- Party Contact Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM hz_parties hp2
             WHERE 1=1
               AND hp2.party_id = hp.party_id
               AND (
                    hp2.url IS NOT NULL OR
                    -- LENGTH(TRIM(hp.email_address)) > 5
                    INSTR(hp2.email_address, '@') > 0 OR
                    hp2.primary_phone_purpose IS NOT NULL
                    )
           ), 'N'
       ) "Party Contact Flag",
       ----------------------------------------------------------
       -- Account Contact Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM hz_contact_points
             WHERE status = 'A'
               AND owner_table_id =
                   (SELECT hcar.party_id
                      FROM hz_cust_account_roles   hcar,
                           ar_contacts_v           acv
                     WHERE hcar.cust_account_id   = hca.cust_account_id
                       AND hcar.cust_account_role_id = acv.contact_id
                       AND hcar.cust_acct_site_id IS NULL  -- look for account level only
                       AND ROWNUM = 1 -- add this row to show inactive sites (i.e. with no site id)
                    )
               AND ROWNUM = 1), 'N'
       ) "Account Contact Flag",
       ----------------------------------------------------------
       -- Site Contact Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM hz_contact_points
             WHERE status = 'A'
               AND owner_table_id =
                   (
                      SELECT hcar.party_id
                        FROM hz_cust_account_roles   hcar,
                             ar_contacts_v           acv
                       WHERE hcar.cust_acct_site_id     =  hcas.cust_acct_site_id
                         AND hcar.cust_account_role_id  =  acv.contact_id
                         AND ROWNUM = 1  -- add this row to show inactive sites (i.e. with no site id)
                   )
               AND ROWNUM = 1), 'N'      -- any contact (email, phone, fax) would suffice this condition
       ) "Site Contact Flag"
  FROM
       hz_parties              hp,
       hz_party_sites          hps,
       hz_cust_accounts_all    hca,
       hz_cust_acct_sites_all  hcas,
       hz_customer_profiles    hcp,
       hz_cust_profile_amts    hcpa,
       hz_locations            hl,
       ra_territories          ter,
       ar_collectors           col
 WHERE
    hp.party_id            =  hca.party_id
   AND hca.cust_account_id    =  hcas.cust_account_id(+)
   AND hps.party_site_id(+)   =  hcas.party_site_id
   AND hp.party_id            =  hcp.party_id  
   AND hca.cust_account_id    =  hcp.cust_account_id
   AND hps.location_id        =  hl.location_id(+)
   AND col.collector_id       =  hcp.collector_id
   AND hcas.territory_id      =  ter.territory_id(+)
   AND hcp.cust_account_profile_id = hcpa.cust_account_profile_id
   ----
   AND hp.party_type          = 'ORGANIZATION'    -- only ORGANIZATION Party types
   AND hp.status              = 'A'               -- only Active Parties/Customers
   ----
   -- following conditions are for testing purpose only
   -- comment/uncomment as needed
   ----
   -- AND hp.party_number        = 11530
   -- AND hca.account_number     = 32253 --32396 --31753 --32253 --31038
 ORDER BY TO_NUMBER(hp.party_number), hp.party_name, hca.account_number;

References

http://www.club-oracle.com/threads/oracle-apps-tca-tables.16312/

18 comments:

  1. Thanks for the good topic. Very useful information.
    We IT hub Online Training are good in giving the oracle apps technical Training

    ReplyDelete
  2. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in ORACLE FINANCE TECHNICAL, kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on ORACLE FINANCE TECHNICAL We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Saurabh Srivastava
    MaxMunus
    E-mail: saurabh@maxmunus.com
    Skype id: saurabhmaxmunus
    Ph:+91 8553576305 / 080 - 41103383
    http://www.maxmunus.com/


    ReplyDelete
  3. Australia Best Tutor is one of the best Online Assignment Help providers at an affordable price. Here All Learners or Students are getting best quality assignment help with reference and styles formatting.

    Visit us for more Information

    Australia Best Tutor
    Sydney, NSW, Australia
    Call @ +61-730-407-305
    Live Chat @ https://www.australiabesttutor.com




    Our Services

    Online assignment help
    my assignment help Student
    Assignment help Student
    help with assignment Student
    Students instant assignment help
    Students Assignment help Services

    ReplyDelete
  4. You must take part in a contest for top-of-the-line blogs on the web. I will suggest this web site!

    P0wer bi onlinetraining from india
    P0wer bi onlinetraining

    ReplyDelete
  5. Thanks and I have a neat supply: How To Properly Renovate A House home renovation in canada

    ReplyDelete