Oracle Apps Knowledge Sharing

                    Before changing a package or procedure or view or table, we use check its dependencies using either of below options. It’s pretty much useful. Hence, shared the same. Hope it helps!!

Table: DBA_DEPENDENCIES

This table contains all basic information about the dependent object at first level. So, if I want to know the direct dependencies of PO_HEADERS_ALL, below query is useful. 

Utility: DBMS_UTILITY.GET_DEPENDENCY

This utility is pretty much useful when we want to know the dependencies in multiple level. Like we need data like below, while searching for dependencies of Y object.

1.  X object is dependent on Y object
   
2.  Z object is dependent on X

Below API can be used to update the organization details of a party in TCA module.

Base Table: HZ_ORGANIZATION_PROFILES

Test Instance: R12

API: HZ_PARTY_V2PUB.UPDATE_ORGANIZATION

Scope: Public

Test Script:

DECLARE
  ln_object_version_number    NUMBER;
  lv_init_msg_list            VARCHAR2(200) DEFAULT fnd_api.g_true;
  lr_organization_rec         apps.hz_party_v2pub.organization_rec_type;  
  ln_msg_count                NUMBER;
  lv_return_status            VARCHAR2(200);
  lv_msg_data                 VARCHAR2(200);
  lv_msg                      VARCHAR2(200);
  ln_profile_id               NUMBER; 
  ln_party_id                 NUMBER DEFAULT 123456;

BEGIN 
   SELECT MAX(object_version_number)
     INTO ln_object_version_number
     FROM hz_parties
    WHERE party_id = ln_party_id;

   lr_organization_rec.party_rec.party_id := ln_party_id;
   lr_organization_rec.year_established   := 2020;   

   -- Calling ORGANIZATION update API
   HZ_PARTY_V2PUB.UPDATE_ORGANIZATION 
   ( p_init_msg_list                  =>  lv_init_msg_list,
     p_organization_rec               =>  lr_organization_rec,
     p_party_object_version_number    =>  ln_object_version_number,
     x_profile_id                     =>  ln_profile_id,
     x_return_status                  =>  lv_return_status,
     x_msg_count                      =>  ln_msg_count,
     x_msg_data                       =>  lv_msg_data
    );

   IF (lv_return_status <> FND_API.G_RET_STS_SUCCESS)
   THEN
     FOR i IN 1 .. FND_MSG_PUB.COUNT_MSG
     LOOP
       lv_msg := lv_msg || FND_MSG_PUB.get(p_msg_index => i, 
                                           p_encoded => FND_API.G_FALSE
                                          ) 
                        || '; ';       
     END LOOP;
     
     DBMS_OUTPUT.PUT_LINE('lv_msg:'||lv_msg);
   END IF;   
END;

                In this post, we have given a basic details about an useful Oracle utility named UTL_MATCH. This utility can be used to find the similarity of two strings with two algorithms given below.

Oracle Document Explanation for quick Byte:

Levenshtein Distance:

"Edit Distance" also known as "Levenshtein Distance "(named after the Russian scientist Vladimir Levenshtein, who devised the algorithm in 1965), is a measure of Similarity between two strings, s1 and s2. The distance is the number of insertions, deletions or substitutions required to transform s1 to s2

Jaro-Winkler algorithm:

The "Jaro-Winkler algorithm" is another way of calculating Edit distance between two strings. This method, developed at the U.S. Census, is a String Comparator measure that gives values of partial agreement between two strings. The string comparator accounts for length of strings and partially accounts for typical human errors made in alphanumeric strings.

Lets do some practical:

Just to understand the functionality of the functions, I tried executing the comparison for below strings.

 Results: 

Oracle Reference:

https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/u_match.htm