Application Object Library(AOL)

1.     Creation of USER:

NAVIGATION: SYSTEM ADMINISTRATOR à SECURITY à USER à DEFINE

CREATE A NEW USER:

Enter user name, password and Re-enter the password. 

QUERIES TO GET BOM DETAILS || ITEM DETAILS || SOURCING RULE|| Sales Order Details|| BOM For Items || Bom Resource|| Routing Details

----------Query For Sales order Details-----------

SELECT    OOHA.HEADER_ID

                ,OOHA.ORDER_NUMBER

               , OOLA.LINE_ID

              , OOLA.ORDERED_ITEM

              ,OOLA.QUANTITY

      ,OOLA.UNIT_PRICE

              , OOLA.ORG_ID

             , MSIB.ORGANIZATION_ID

            , MSIB.SEGMENT1

            , MSIB.INVENTORY_ITEM_ID   

  FROM OE_ORDER_HEADERS_ALL OOHA

             , OE_ORDER_LINES_ALL OOLA

    , MTL_SYSTEM_ITEMS_B MSIB

    , MTL_PARAMETERS MP

WHERE 1=1

  AND OOHA.HEADER_ID = OOLA.HEADER_ID

  AND OOLA.ORDERED_ITEM = MSIB.SEGMENT1

  AND MSIB.ORGANIZATION_ID=MP.ORGANIZATION_ID;

Creation of XML Report | XML Report | XML Publisher Report |XML P Report | BI Publisher | Oracle Apps R12 | Oracle EBS | EBS

                                                                  XML REPORT

An XML report definition typically refers to a structured document that outlines the layout, structure, and content of a report using XML (extensible Markup Language). XML is a popular format for defining such structures due to its flexibility and ease of parsing.

The specifics of an XML report definition can vary widely based on the reporting system or tool being used. Different systems might have their own XML schemas or structures to define reports.

In a generic sense, an XML report definition might include elements to describe:

1. Report Metadata: Information about the report, such as its title, author, description, creation date, etc.

PO Master Detailed Report in Oracle EBS | PO Master Detailed Report in Oracle Apps R12 | Purchasing | Purchase Order | XML | EBS | Oracle Apps R12 | Oracle EBS

 

PO MASTER DETAILED REPORT

 

Registration steps for Oracle Reports

1.   Once we get the Requirement from Business, develop a query in TOAD/SQL Developer.

2.   Once Query has been done, place the query in Data Model SQL Block

       3.   After Adding the Parameter in User Parameters tab and successfull compilation, save the report XXRBCSAMPLE.rdf as extension

     4.     Move the .rdf to Custom Top /reports/US Folder with Executable Permissions by Using WINSCP Tool

       5.     After Login to the Application,Switch to Application Developer--> Concurrent --> Executable, give name, Short Name, application would be Custom Top Application, type is Oracle Reports , Execution file name should be given .rdf name and Save the Changes

Block Structure & Variables in PL/SQL| Basics of PL/SQL | PL/SQL

 

Block & Variables

Q : What is Block?

A : It is a unit of code that is to declare a data operation and create a logical operation for database.

syntax:

Declare

Begin

End;

Declare:  Declare section means, we can write the variables.

Triggers in PL/SQL | BEFORE Triggers | AFTER Triggers | Row-Level Triggers | Statement-Level Triggers | Instead of Triggers | PL/SQL

 

Triggers

 

A collection of instructions that are automatically carried out (or "triggered") in reaction to specified occurrences on a given table or view is known as a trigger. 

 

Triggers are used in databases to maintain data integrity, carry out extra tasks, and enforce business rules. 

 

They can be programmed to run before or after particular events, such INSERT, UPDATE, and DELETE.

 

          Here is a general definition of triggers in PL/SQL:

 

SQL Indexes | Types of indexes | Creating an Index | Dropping an Index | OLAP | LOCKS | Basics of Oracle SQL | SQL

 

INDEXES

What is an Index?

• An index is a schema object.
• Is used by the Oracle server to speed up the retrieval of rows by using a pointer.
• Can reduce disk I/O by using a rapid path access method to locate data quickly. 
•  Is independent of the table it indexes. 
•  Is used and maintained automatically by the Oracle server.

Note: When you drop a table, corresponding indexes are also dropped.

LISTAGG Funcation | Basics of Oracle SQL | Oracle SQL | SQL

 

Listagg

What is the Listagg?

The LISTAGG function is used to aggregate a set of string values within a group into a single string by appending the string-expression values based on the order that's specified in the 'WITHIN GROUP' clause.

As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.

SQL Sequences | Nextval | Currval | Modifying and Dropping a Sequence | Basics of Oracle SQL | Oracle SQL | SQL

 Sequences

A sequence is a user created database object that can be shared by multiple users to generate unique integers.

A typical usage for sequences is to create a primary key value, which must be unique for each row.

The sequence is generated and incremented (or decremented) by an internal Oracle routine.

Sequence numbers are stored and generated independently of tables. Therefore, the same sequence can be used for multiple tables.

A sequence Speeds up the efficiency of accessing sequence values when cached in memory

A sequence Replaces application code.

 

SQL Views | Types Of Views | Simple View | Complex View | Materialized View | Inline View

Views

What is a View?

•    A view is a logical table based on a table or another view.4
•    A view contains no data of its own but is like a window through which data from tables can          be viewed or changed.
•    The tables on which a view is based are called base tables. The view is stored as a SELECT                 statement in the data dictionary.
•     We can present logical subsets or combinations of data by creating views of tables.

SQL Clauses | Group By Clause | Having Clause | Order By Clause | Distinct Clause | With Clause | Asc | Desc | Oracle SQL | SQL

 CLAUSES

Clauses:

•       Group by Clause

•       Having Clause

•       Order by Clause

•       Distinct Clause

•       With Clause

SQL Sub-Queries| Types of Sub-Queries | Non-correlated Sub-Query | Correlated Sub-Query| Exists | Non-Exists | Oracle SQL | SQL

 Sub Queries

Using a Sub-query :

Main Query: Which employees have salaries greater than Abel’s salary?

Subquery: What is Abel’s salary?

 Sub-query syntax :

The subquery (inner query) executes once before the main query.

The result of the subquery is used by the main query (outer query).

Interfaces | Inbound | Outbound | Purchasing | EBS | Oracle Apps R12 | Oracle EBS

Interfaces

What is Interface?

          Interfaces are used in Oracle Applications to integrate external systems and Data Conversion.

          The interfaces are mainly used to either transfer data from Oracle Applications to a flat file or data from legacy system to Oracle Applications.

          Interfaces in oracle apps r12 are ways to exchange data between Oracle Applications and external systems.

Single-Row Functions | SQL Functions | Number Functions | Character Functions | Date Functions | Conversion Functions

 

 SQL Functions:

What is a function?

Functions are similar to operators in that they manipulate data items and return a result. Functions differ from operators in the format of their arguments. This format enables them to operate on zero, one, two, or more arguments:

Syntax: function (argument, argument, ...)

SQL Loader | Basics of Oracle SQL | Oracle SQL | SQL

SQL Loader

SQL*Loader allows you to load data from an external file into a table in the database. It can parse many delimited file formats such as CSV, tab-delimited, and pipe-delimited.

SQL*Loader provides the following methods to load data:

•       Conventional path loads – construct INSERT statements from the contents of the input data file based on the predefined specification and execute the inserts.

•       Direct path loads – creates data blocks in Oracle database block format from the data file and directly write the data block to the database. This way is much faster than the conventional path but subject to some restrictions.

•       External table loads – create an external table for the data stored in the data file and execute INSERT statements to insert the data from the data file into the target table. The external table loads support parallel loading if data file is big enough.

 

SQL Pesudo Columns | Rowid |Rownum | Oracle SQL | SQL

 

Pseudo Columns

1. ROWID:

•       ROWID is nothing but the physical memory location on which that data/row is stored. ROWID basically returns address of row.

•       ROWID uniquely identifies row in database.

•       ROWID is combination of data object number, data block in data file, position of row and datafile in which row resides.

SQL Synonyms | Synonyms | Private Synonym | Public Synonym | SQL | Oracle SQL

 

Synonyms

Creating and Dropping Synonyms

•       Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects.

Syntax:

                CREATE [PUBLIC] SYNONYM synonymname

                FOR object;

SQL Constraints | NOTNULL | UNIQUE | PRIMARY KEY | FOREIGN KEY | On Delete Cascade | CHECK | Basics of Oracle SQL | Oracle SQL | SQL

 Constraints

What are Constraints?

Constraints enforce rules at the table level.

Constraints prevent the deletion of a table if there are dependencies.

The following constraint types are valid:

      Constraint                                Description

     NOT NULL             :     Specifies that the column cannot contain a null value.

     UNIQUE                 :     Specifies a column or combination of columns whose values must be unique

                                           for  all rows in the table.

     PRIMARY KEY        :     Uniquely identifies each row of the table.

     FOREIGN KEY         :     Establishes and enforces a foreign key relationship between  the column and

                                            a column of the referenced table..

     CHECK                     :     Specifies a condition that must be true.

How to Create an APEX Application using Oracle APEX | APEX Application | Oracle APEX

 

 Step-1: 

          Give the Workspace name, Username, Password and click on sign in

 Step-2: 

         After login into the apex application click on create button 

How to Insert BLOB content using FORM And Display Image Interactive Report using Oracle APEX | Form | Interactive Report | BLOB | Oracle APEX

 

Step-1: 

     Create table with blob.

            CREATE TABLE "PRODUCT_INFORMATION" (

                                              "PRODUCT_ID"                       NUMBER,

                                              "PRODUCT_NAME"                VARCHAR2(20),

                                              "FILENAME"                            VARCHAR2(20),

                                              "MIMETYPE"                           VARCHAR2(20),

                                             "IMAGE_LAST_UPDATE"        DATE,

                                            "PRODUCT_IMAGE"                BLOB,

                                             "LIST_PRICE"                           NUMBER,

                                             "PRODUCT_AVAILABILITY"   NUMBER

);

Download Report in Custom Format using Oracle APEX | XLSV | CSV | PDF | Oracle APEX

Step-1: 

        Create Blank Page

Step-2: 

        Create download button in body section.

 

How to Add Grand Total Column using Interactive Report In Oracle APEX | Interactive Report

• Step - 1 : 

        Create blank page.

 

 

 

SQL Joins | Equi Join | Inner Join | Non-equi Join | Outer Joins | Self Joins |

 

Joins

What are Joins?

A join is a query that combines rows from two or more tables, views, or materialized views.

Oracle Database performs a join whenever multiple tables appear in the FROM clause of the query.

The select list of the query can select any columns from any of these tables.

If any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity.

Operators | Arithmetic Operators | Relational operators | Logical Operators | Special Operators | Extended Comparisons| Clause | AND| OR | NOT | BETWEEN | IN | LIKE | IS NULL

OPERATORS 

Arithmetic Operators:

•       Arithmetic Operators are used in number datatype column.

• Addition(+).
• Subtraction (-).
• Division(/).
• Multiplication(*).
• Modulus(%).

• We can also use Arithmetic operator in “WHERE” conditions.

Set Operators | Union | Union All | Intersect | Minus | Oracle SQL

Set Operators

Set operators are used to combine the results of two or more SELECT queries. These operators allow you to perform set operations on the result sets, such as union, intersection, and difference. The main set operators in Oracle SQL are:

UNION:

 

• Combines the results of two SELECT statements, removing duplicate rows.

Interactive Grid Get Selected Rows | Oracle APEX

 

• Step - 1 : 

        Create Blank page

• Step - 2 : 

        Create one page item        -----my page item is P7_DEPT_ID

• Step - 3 : 

        Create region - Type is interactive grid, table name is dept

                                   Select the column primary key

                                   Click on attributes edit is on

Multi Row Functions | Aggregate functions | Types of Multiple Row Functions in Oracle | Avg | Count | Max | Min | Sum

Multi Row Functions / Aggregate Functions

Multiple Row Functions in Oracle

 SQL Functions:

What is a function?

Functions are similar to operators in that they manipulate data items and return a result. Functions differ from operators in the format of their arguments. This format enables them to operate on zero, one, two, or more arguments:

Syntax: function (argument, argument, ...)

Selecting Multiple Records using LOV in Oracle APEX | List of Values | LOV | Oracle APEX | SQL

 Selecting Multiple Records using LOV in Oracle APEX

     Step-1: 

        Create Blank page.

     Step-2: 

        Create region type Static content.

     Step-3: 

         Create Page item and select the type is POPUP LOV

 

How to Display Add Remove, Delete buttons on the Cards Using Classic Report in Oracle APEX | Classic Report | Cards | External Buttons Creation on Oracle APEX

How to Display Add Remove, Delete buttons on the Cards Using Classic Report in Oracle APEX

• Step - 1:

              Create blank page: 

Web ADI (Application Desktop Integrator) | Oracle Apps R12 | Oracle EBS | EBS

WEB ADI

• Step 1

·        First, we need to set profile option is set to Yes!

·        BNE Allow No Security Rule – YES

·        Navigation ➔ System Administration ➔ Profile ➔ System

·        Give Profile Name as BNE Allow No Security Rule and click on Find

·        Change the Site level permission to YES

Oracle Application Express Introduction | Oracle Apex Introduction | Components | Application builder | SQL Workshop | Team Development | Administration

 

Oracle Application Express

• Oracle Application Express is a web-based development and deployment tool that is available with Oracle database. It enables you to create data base- centrist web applications that are reliable, scalable, and secure. It has a number of built-in features and wizards that quicken your development process. Some of the key features are listed in the slide.
•  The tool has a user-friendly graphical interface. Using Oracle Application Express requires minimal programming knowledge.

SQL Languages | Data Definition Language (DDL) | Data Manipulation Language (DML) | Data Control Language (DCL) | Transaction Control Language (TCL) | Data Retrieval Language (DRL) | Data Query Language (DQL)

 SQL Languages

•       Data Definition Language (DDL)

•       Data Manipulation Language (DML)

•       Data Control Language (DCL)

•       Transaction Control Language (TCL)

•       Data Retrieval Language (DRL) / Data Query Language (DQL)

Cursors in PL/SQL | Oracle PL/SQL Cursors | Cursor | Implicit Cursor | Explicit Cursor | %NOTFOUND | %FOUND | %ISOPEN | %ROWCOUNT

 

Cursors

• Cursors can be defined as private memory where the data stores temporarily.  
• Cursors are used to process the multiple records. 
• Cursors holds the multiple rows which is written in SQL statement.

Data base contains two static cursors

1. Implicit Cursor.

2. Explicit Cursor.

Implicit Cursor:

• In Implicit Cursor the DML operations will be performed. 
  
• The implicit cursor cannot be named and they cannot be controlled and referred to another place.
• We can refer the implicit cursor through the attributes only.

Exception Handling | Exceptions | Pre-defined Exceptions | User defined Exceptions | User named Exceptions | NO_DATA_FOUND | TOO_MANY_ROWS | ZERO_DIVIDE | VALUE_ERROR| CURSOR_ALREADY_OPEN | INVALID_CURSOR

 

Exceptions

An exception is an error which disrupts the normal flow of program instructions. PL/SQL provides us the exception block which raises the exception thus helping the programmer to find out the fault and resolve it.

Exceptions are used to handle errors or exceptional conditions that might occur during the execution of a program. They allow you to gracefully manage errors and perform actions based on specific error conditions.

 There are three types of exceptions

1. Pre-defined exceptions

2. User defined exceptions

3. User named exceptions

SQLERRM: It is the return the error message.

SQLCODE: It is return the error code

Classic Report Creation in Oracle APEX | What is Classical report | Basics of Oracle APEX

Classic Report Creation  in Oracle APEX

What is Classical report ?

•    A classic report is a formatted result of a SQL query.
•    Classic report creation is supported by both the Create Application Wizard and Create Page Wizard.
•    End users cannot customize a classic report except for sorting and simple filtering.