Query to get Employee Hierarchy.

Query to get Employee Hierarchy

SELECT  fu.user_name, emp.heirachy,emp.path1 ,he.employee_num

  FROM (    SELECT he.full_name,

                   he.employee_num , 

                   level path1,        

                   SYS_CONNECT_BY_PATH (he.full_name, '|') heirachy,

                   SUBSTR (

          SYS_CONNECT_BY_PATH (he.full_name, '|'),

          2,

          DECODE (INSTR (SYS_CONNECT_BY_PATH (he.full_name, '|'), '|', 2),

                  0, 240,

                  INSTR (SYS_CONNECT_BY_PATH (he.full_name, '|'), '|', 2))-2) emp_name                 

              FROM apps.hr_employees he,                  

                   apps.per_assignments_v7 pav                

             WHERE     he.employee_id = pav.person_id

                   AND SYSDATE BETWEEN pav.effective_start_date

                                   AND NVL (pav.effective_end_date, SYSDATE + 1)                                 

        CONNECT BY NOCYCLE PRIOR pav.supervisor_id = he.employee_id) emp,

        apps.hr_employees he ,

        fnd_user fu     

 WHERE 1=1

     AND (emp.full_name = :Emp_name)

    AND he.full_name= emp.emp_name

    AND HE.EMPLOYEE_NUM = fu.user_name(+)

order by emp.path1

Important Queries related to Concurrent Programs.

Important Queries related to Concurrent Programs.

To get Details of Concurrent Program

SELECT *

    FROM fnd_concurrent_programs_vl

WHERE UPPER(user_concurrent_program_name) = :Concurrent_program_name    

To get Details of Concurrent Program Request details

SELECT *

    FROM fnd_concurrent_requests

   WHERE  concurrent_program_id = :concurrent_program_id 

         AND TRUNC (request_date) = TRUNC (SYSDATE)

ORDER BY request_date DESC

To get Details of Concurrent Program Parameter details 

SELECT  fdfcuv.column_seq_num

      , fdfcuv.end_user_column_name

      , fdfcuv.flex_value_set_id

      , ffvs.flex_value_set_name

     , fcpl.user_concurrent_program_name

      , fcp.concurrent_program_name

      , fav.APPLICATION_SHORT_NAME

      , fav.APPLICATION_NAME

      , fav.application_id

      , fdfcuv.form_left_prompt prompt

      , fdfcuv.enabled_flag

      , fdfcuv.required_flag

      , fdfcuv.display_flag

      , flv.meaning default_type

      , fdfcuv.DEFAULT_VALUE     

 FROM   fnd_concurrent_programs fcp

      , fnd_concurrent_programs_tl fcpl

      , fnd_descr_flex_col_usage_vl fdfcuv

      , fnd_flex_value_sets ffvs

      , fnd_lookup_values flv

      , fnd_application_vl fav

WHERE  fcp.concurrent_program_id = fcpl.concurrent_program_id

-- AND    fcpl.user_concurrent_program_name =:conc_prg_name

AND    fcp.concurrent_program_name = :conc_prg_short_name

AND    fcpl.LANGUAGE = 'US'

AND    fav.application_id=fcp.application_id

AND    fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name

AND    ffvs.flex_value_set_id = fdfcuv.flex_value_set_id

AND    flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'

AND    flv.lookup_code(+) = fdfcuv.default_type

AND    flv.LANGUAGE(+) = USERENV ('LANG')

ORDER BY fdfcuv.column_seq_num

To get Details of Valueset details 

Select value_column_name,id_column_name,application_table_name,additional_where_clause

FROM FND_FLEX_VALIDATION_TABLES

WHERE flex_value_set_id =:Value_set_id

To get Details of Concurrent Program, Request_group and Responsibility details 

 SELECT frt.responsibility_name, frg.request_group_name,

    frgu.request_unit_type,frgu.request_unit_id,
    fcpt.user_concurrent_program_name
    FROM apps.fnd_Responsibility fr, apps.fnd_responsibility_tl frt,
    apps.fnd_request_groups frg, apps.fnd_request_group_units frgu,
    apps.fnd_concurrent_programs_tl fcpt
    WHERE frt.responsibility_id = fr.responsibility_id
    AND frg.request_group_id = fr.request_group_id
    AND frgu.request_group_id = frg.request_group_id
    AND fcpt.concurrent_program_id = frgu.request_unit_id
    AND frt.LANGUAGE = USERENV('LANG')
    AND fcpt.LANGUAGE = USERENV('LANG')
   AND fcpt.user_concurrent_program_name = :conc_prg_name
    ORDER BY 1,2,3,4

Logging Message in FND_LOG_MESSAGES

Usage of FND_LOG_MESSAGES table for adding Log messages:

Profiles to be set for the Log messages to be inserted.

FND: Debug Log Enabled       –   Yes

FND: Debug Log Level        -   Statement

FND: Debug Log Module       - <Module name can be provided by you>

In case of Multiple Module if you want to debug then separate the Module name with ,(comma).

Procedure to insert the log message

fnd_log.STRING (log_level   => 1,

                module      => <Module name can be provided by you same as above>,

                MESSAGE     => <Log message>);

Oracle ERP Customer- Tables and Relations , Queries and API

Oracle ERP Customer- Basic Tables and Relations , Queries and API

HZ_PARTIES :- Parties are organizations, people, relationship  and groups.

HZ_LOCATIONS:- Address Information is stored in this table.

HZ_PARTY_SITES:- Party Site information and connection between Party and  Address(Location)

It has 1:N relationship with Party. There can be N party sites for one Party connected by Party_id field. It has 1:1 relationship with Location. For one entry in Location table there can be only one entry in Party site connected by Location_id field.

HZ_CUST_ACCOUNTS:- Customer account Number information. Is has 1:N relationship with Party. There can be Many Accounts for a Party connected by Party_id.

HZ_CUST_ACCT_SITES_ALL:- Account Site Information. It is Connection between Party Site, Account and OU.  There can be only 1 Customer Account site for the combination of OU,Party site and cust account connected by Cust_Account_id ,Party_site_id and Org_id.

HZ_CUST_SITE_USES_ALL:- Usage information of the site like Bill to, Ship to. It has 1:N relationship with Customer Account sites. There can be many Site uses for an Account site connected by Cust_Acct_Site_id.

Basic Queries for tables

select * from apps.hz_parties
where party_id =
and party_name =

select * from apps.hz_cust_accounts
where account_number =
and party_id =
and cust_account_id =

select * from apps.hz_party_sites
where party_id =
and party_site_number =

select * from apps.hz_locations where location_id =

select cust_acct_site_id,cust_account_id,party_site_id,org_id from apps.hz_cust_acct_sites_all
where cust_acct_site_id =
and cust_account_id =
and party_site_id =

select * from apps.hz_cust_site_uses_all where cust_acct_site_id =
and site_use_id =

Query to get the Sites details based on account number and OU

select *
  from hz_cust_accounts hca,
       hz_cust_acct_sites_all hcasa,
       hz_party_sites hps,
       hz_cust_site_uses_all hcsua     
 where     hca.cust_account_id = hcasa.cust_account_id
       and hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
       and hps.party_site_id = hcasa.party_site_id
       and hca.status = 'A'
       and hps.status = 'A'
       and hcasa.status = 'A'
       and hcsua.site_use_code = 'BILL_TO'
       and hcsua.status = 'A'
       and hca.account_number =
       and hcasa.org_id =


API to create Party and Account

HZ_CUST_ACCOUNT_V2PUB.create_cust_account (
         p_init_msg_list          => FND_API.G_TRUE,
         p_cust_account_rec       => acct_rec,
         p_organization_rec       => org_rec,
         p_customer_profile_rec   => prof_rec,
         p_create_profile_amt     => FND_API.G_FALSE,
         x_cust_account_id        => p_out_num_cust_account_id,
         x_account_number         => p_out_chr_cust_account_number,
         x_party_id               => p_out_num_party_id,
         x_party_number           => p_out_chr_party_number,
         x_profile_id             => p_organization_profile_id,
         x_return_status          => p_return_status,
         x_msg_count              => p_msg_count,

         x_msg_data               => p_msg_data);

acct_rec    HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
party_rec   HZ_PARTY_V2PUB.PARTY_REC_TYPE;
org_rec     HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
prof_rec    HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;

API to update Party

   HZ_PARTY_V2PUB.update_organization (
         p_organization_rec              => org_rec,
         p_party_object_version_number   => l_party_object_version,
         x_profile_id                    => p_organization_profile_id,
         x_return_status                 => p_return_status,
         x_msg_count                     => p_msg_count,

         x_msg_data                      => p_msg_data);

party_rec   HZ_PARTY_V2PUB.PARTY_REC_TYPE;
org_rec     HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;

API to update Account

 HZ_CUST_ACCOUNT_V2PUB.update_cust_account (

         p_cust_account_rec        => acct_rec,

         p_object_version_number   => l_cust_acct_object_version,

         x_return_status           => p_return_status,

         x_msg_count               => p_msg_count,

         x_msg_data                => p_msg_data);

acct_rec    HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;

API to create Location

HZ_LOCATION_V2PUB.create_location (

            p_location_rec    => location_rec,

            x_location_id     => p_location_id,

            x_return_status   => p_return_status,

            x_msg_count       => p_msg_count,

            x_msg_data        => p_msg_data);

location_rec        hz_location_v2pub.location_rec_type;

API to update Location

HZ_LOCATION_V2PUB.update_location (

         p_location_rec            => location_rec,

         p_object_version_number   => l_object_version_location,

         x_return_status           => p_return_status,

         x_msg_count               => p_msg_count,

         x_msg_data                => p_msg_data);

location_rec        hz_location_v2pub.location_rec_type;

API to create Party Site

HZ_PARTY_SITE_V2PUB.create_party_site (

         p_party_site_rec      => psite_rec,

         x_party_site_id       => p_party_site_id,

         x_party_site_number   => p_party_site_number,

         x_return_status       => p_return_status,

         x_msg_count           => p_msg_count,

         x_msg_data            => p_msg_data);

 

psite_rec   hz_party_site_v2pub.party_site_rec_type;

API to update Party site

HZ_PARTY_SITE_V2PUB.update_party_site (

         p_party_site_rec          => psite_rec,

         p_object_version_number   => l_object_version_party_site,

         x_return_status           => p_return_status,

         x_msg_count               => p_msg_count,

         x_msg_data                => p_msg_data);

psite_rec                     hz_party_site_v2pub.party_site_rec_type;

API to create Cust Site

  HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_acct_site (

         p_cust_acct_site_rec   => asite_rec,

         x_cust_acct_site_id    => p_cust_acct_site_id,

         x_return_status        => p_return_status,

         x_msg_count            => p_msg_count,

         x_msg_data             => p_msg_data);

 asite_rec           hz_cust_account_site_v2pub.cust_acct_site_rec_type;

API to update Cust Site

 HZ_CUST_ACCOUNT_SITE_V2PUB.update_cust_acct_site (

         p_cust_acct_site_rec      => asite_rec,

         p_object_version_number   => l_object_version_number,

         x_return_status           => p_return_status,

         x_msg_count               => p_msg_count,

         x_msg_data                => p_msg_data);

asite_rec                       hz_cust_account_site_v2pub.cust_acct_site_rec_type;

API to create Cust Site Uses

      HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_site_use (

         p_cust_site_use_rec      => suse_rec,

         p_customer_profile_rec   => profile_rec,

         p_create_profile         => fnd_api.g_false,

         p_create_profile_amt     => fnd_api.g_false,

         x_site_use_id            => p_site_use_id,

         x_return_status          => p_return_status,

         x_msg_count              => p_msg_count,

         x_msg_data               => p_msg_data);

suse_rec                hz_cust_account_site_v2pub.cust_site_use_rec_type;

profile_rec             hz_customer_profile_v2pub.customer_profile_rec_type;

API to update Cust Site Uses

HZ_CUST_ACCOUNT_SITE_V2PUB.update_cust_site_use (

            p_cust_site_use_rec       => suse_rec,

            p_object_version_number   => l_site_object_version_number,

            x_return_status           => p_return_status,

            x_msg_count               => p_msg_count,

            x_msg_data                => p_msg_data);

suse_rec                        hz_cust_account_site_v2pub.cust_site_use_rec_type;