SQL Query to get Concatenated Segments in Oracle Fusion (Invoice Tables)

In this post, we will see how to get Concatenated Segments in Oracle Fusion. For this, we will take example of Code Combination available on AP_INVOICES_ALL table in column ACCTS_PAY_CODE_COMBINATION_ID. We can also use other Code Combination columns available in other Invoices table:

 

 To get the concatenated segments we can use the below function:

FND_FLEX_EXT.GET_SEGS(
    application_short_name => v_application_short_name,     --'GL' 
    key_flex_code          => v_key_flex_code,              --'GL#'
    structure_number       => v_structure_number,           --Chart Of Accounts ID
    combination_id         => v_combination_id              
);

 So, to get concatenated segments for ACCTS_PAY_CODE_COMBINATION_ID in table AP_INVOICES_ALL use the below SQL:

SELECT
     aia.invoice_id
    ,aia.invoice_num
    ,aia.description
    ,aia.invoice_type_lookup_code
    ,aia.source
    ,aia.invoice_date
    ,aia.invoice_amount
    ,aia.accts_pay_code_combination_id
    ,(	SELECT  fnd_flex_ext.get_segs(
                    'GL'                            --Application Short Name
                    ,'GL#'                          --Key Flex Code
                    ,gcc.chart_of_accounts_id       --Structure Number
                    ,gcc.code_combination_id
                ) 
        FROM    gl_code_combinations gcc 
        WHERE   gcc.code_combination_id = aia.accts_pay_code_combination_id
     ) accts_pay_code_combination
FROM     
    ap_invoices_all aia
WHERE   aia.invoice_id = <InvoiceId>

 

Similarly, we can extract concatenated segments for other columns in other Invoice Tables.

Source: https://docs.oracle.com 

Types of SQL keys

We have following types of keys in SQL which are used to fetch records from tables and to make relationship among tables or views.

1. Super Key

   Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table.Example :Primary key, Unique key, Alternate key are subset of Super Keys.
   
   

2. Candidate Key

   A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.
   
   

3. Primary Key

   Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It can not accept null, duplicate values. Only one Candidate Key can be Primary Key.
   
   

4. Alternate key

   A Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key.
   
   

5. Composite/Compound Key

   Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.
   
   

6. Unique Key

   Uniquekey is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it can not have duplicate values.
   
   

7. Foreign Key

   Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values.
   
   

What is functional dependency?

A functional dependencies is denoted by X--->Y  between two sets of attributes X and Y that are subsets of R. This means that the value of X component of a tuple uniquely determines the value of component Y

What is 1 NF(Normal Form)?

The domain of attribute must include only atomic(simple, indivisible) values.

What is normalization?

It is a process of analysing the given relation schemas based on their Functional Dependencies(FDs) and primary key to achieve the properties:

• Minimizing redundancy.
• Minimizing insertion, deletion and update anomalies.

What is SDL(Storage Definition Language)?

This language is to specify the internal schema. This language may specify the mapping between two schemas

What is VDL(View Definition Language)?

It specifies user views and their mappings to the conceptual schema.

Integrity rules

In SQL we have two integrity rules:

• Entity Integrity- states that PRIMARY KEY cannot have NULL values.
• Referential Inntegrity- states that foreign key can be either a NULL value or should be PRIMARY KEY value of other relation

Describe subquery

A subquery is a query that is composed of two queries. The first query (inner query) is within the WHERE clause of the outer query. In some cases the inner query provides results for the outer query to process. In other cases, the outer query results provide results for the inner query

What is a cascading update?

Referential integrity constraints require that foreign key value in one table correspond to primary key values in another. If the value of the primary key is changed, that is, updated, the value of the foreign key must immediately be changed to match it. Cascading updates will set this change to be done automatically by the DBMS whenever necessary

What is PL/SQL?

PL/SQL is Oracle's Procedural Language extension to SQL. The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools

What is a Candidate Key?

A table may have more than one combination of columns that could uniquely identify the rows in a table, each combination is a Candidate Key

Difference betwween UNION and UNION ALL

UNION will remove the duplicate rows from the result set while UNION ALL does not.

Difference between TRUNCATE and DELETE

• Both result in deleting of the rows in the table
• TRUNCATE call cannot be rolled back  and all memory space for that table is released back to the server while DELETE call can be rolled back
• TRUNCATE call is DDL command while DELETE call is DML command
• TRUNCATE call is faster than DELETE call

Authentication and Authorisation

Authentication

Authentication is the process of verifying the identity of a user using some credentials like username and password. Authentication merely ensures that the individual is who he or she claims to be, but says nothing about the access rights of the individual.

Authorization

The process of granting or denying access to a network resource.  Authorization determines the parts of the system to which a particular identity has access. 

Authentication is required before Authorization.

For e.g. If an employee authenticates himself with his credentials on a system, authorization will determine if he has the control over just publishing the content or also editing it

What are DQL statements?

DQL(Data Query Language) statement is used to query data from the database.

• SELECT- used to get rows and/or columns from tables or views

What are Embedded SQL statements?

Embedded SQL statements are used to incorporate DDL, DML and TCL statements within the body of a procedural language program. These are:

• DEFINE- used to define cursors
• OPEN-  used to allocate cursors
• DECLARE- used to assign variable names
• EXECUTE- used to execute SQL statements
• FETCH- used to retrieve data from database

What are TCL statements?

TCL(Transaction Control Language) statements manage the change made by DML statements, and group DML statements into transactions. These are:

• COMMIT- used to make a transaction's changes permanent
• ROLLBACK- used to undo changes in a transaction, either since the transaction started or since a savepoint
• SAVEPOINT- used to set point to which a transaction can be rolled back
• SET TRANSACTION- used to establish properties for a transaction

What are DCL statements?

DCL(Data Control Language) are used to grant or revoke privileges from a user. These are:

• GRANT- used to grant a privilege
• REVOKE- used to revoke a privilege
• COMMENT- used to add a comment to the data dictionary

What are DML statements?

DML(Data Manipulation Language) statements enable users to query or manipulate data in existing schema objects. These are:

• DELETE- used to remove rows from tables or views
• INSERT- used to add new rows of data into tables or views
• SELECT- used to retrieve data from one or more tables
• UPDATE- used to change column values in existing rows of a table or view