Record in Oracle

Record gives you a way to store and access values as a group.

• It is composed of one or more fields, each with its own value.
• These fields can be of different datatypes. 
• You can use records to hold related items and pass them to subprograms as a single parameter. 
• When you are creating a record, you are just creating your own datatype. 

PL/SQL supports three different kinds of records:-

       1. Table-based record (table_name % ROWTYPE):-

Based on a table's column structure.Record will have same number of fields as columns in table and each field in record has the same name as a column in a table

      2. Cursor-based record (cursor_name % ROWTYPE):-

Based on the cursor's SELECT statement.Record will have same number of fields as column or expression in the cursor SELECT statement and each field in record has the same name as column or expression in the cursor SELECT statement.

       3. Programmer-defined record :- 

Based on the structure defined by programmer .Each field is defined explicitly (its name and datatype) in the TYPE statement.

Programmer defined composite records are used when

• A field of record needs to be a PL/SQL-specific type, such as BOOLEAN, PLS_INTEGER
• Record type is not dependent on a table or cursor result 
• You want to have a control on number, name and datatype of fields in a record

Cursors in Oracle

There are two types of cursors present in PL/SQL:-

Implicit Cursors:-

• Automatically created whenever a SQL statement (DML) is executed.
• For SELECT statement this implicit cursor consists of rows that will be retrieved.
• For INSERT -->it will be the rows that are going to be inserted
• For UPDATE--> it will be the rows that are going to be affected
• For DELETE--> it will be the rows that will be deleted
• You can find if any row is affected or not and how many rows are affected by using SQL%ATTRIBUTES.

Explicit Cursors:-

• Explicit cursors are explicitly declared in the PL/SQL declare section
• You can access this cursor in the PL/SQL code using cursor name
• Explicit cursors can be defined only for the select statements
• You can find if any row is affected or not and how many rows are affected by using <cursor_name>%ATTRIBUTES

Triggers in Oracle

A Trigger is a stored PL/SQL program unit, which gets implicitly fired by Oracle Database when a triggering event occurs. A Trigger defines an action, which should get executed, when some database related event occurs.

There are different types of triggers.

• Row Triggers and Statement Triggers
• BEFORE and AFTER Triggers
• Compound Triggers
• INSTEAD OF Triggers
• Triggers on System Events and User Events

DML Triggers are associated with a specific database table. The purpose of this trigger is to perform a specific service when a specified DML operation (INSERT, UPDATE, DELETE) occurs on a table.

Unlike the stored procedures or functions which have to be explicitly invoked, these triggers implicitly gets fired or executed whenever the table is affected by any DML operation.

A  Trigger has 3 basic parts:-

Triggering event or Statement:- An SQL statement which causes a trigger to be fired. Every triggering event is associated with trigger timing i.e., when a trigger should execute. According to trigger timing, triggers can be either before or after triggers.

Triggering events can be 

• Before INSERT or After INSERT
• Before UPDATE or After UPDATE
• Before DELETE or After DELETE

Trigger constraint:-This is optional. A Boolean expression which must be TRUE for the trigger to be fired. We can add triggering constraint by using a WHEN clause in trigger.

Trigger action:- Is a PL/SQL code which contains what needs to be done when a trigger is fired.

Structure of Trigger:-
CREATE [OR REPLACE] TRIGGER  <trigger_name> BEFORE | AFTER | INSTEAD OF
DELETE | [OR] INSERT | [OR] UPDATE [ OF <column> [, <column>...]] ON <table>

-- This section is called Triggering event

[ FOR EACH ROW [ WHEN  <condition>] ]

-- When clause will be triggering constraint
BEGIN

-- This PL/SQL block is Triggering action

/*
 PL/SQL Block
 */
...
END;

Row Trigger:- In Row Trigger, the trigger body is executed depending on how many rows were affected by the DML statement. If the DML statement did not affect any rows, the trigger body executes 0 (ZERO) times.

These row triggers will have two pseudo records to get data from triggering event (INSERT, UPDATE, DELETE).

OLD:- references the OLD row before UPDATE or DELETE

NEW:- references the NEW row after INSERT or UPDATE

Row triggers will be used if trigger action depends on the data provided by triggering statement.

Before Trigger:- Before Trigger executes trigger action i.e. PL/SQL code before executing triggering statement INSERT,  UPDATE or  DELETE.

Triggering Constraint:-
If you want any trigger to get fired only on a specific condition, then we can give that condition as Triggering Constraint.Triggering constraint is achieved by adding a WHEN clause in the trigger.

Statement Trigger:- In a statement trigger, the trigger body is executed only once for the statement, irrespective of how many number of rows are affected by the DML statement. 

• FOR EACH ROW clause decides whether it is a row level or statement trigger.
• Statement triggers are useful in complex security check.
• Unlike row triggers, there are no pseudo records associated with statement triggers.

To View the trigger details use below query:

SELECT trigger_name, trigger_type, triggering_event,

    table_name, referencing_names,

    status, trigger_body

FROM   user_triggers

WHERE  trigger_name = 'XX_TRG_NAME';

Drop Trigger using below command:-

DROP TRIGGER <trigger_name>;

Sequence in Oracle

• Sequence is a database object that is used to generate unique numbers. It can be used for generation of primary key values.

• Sequence numbers are generated independently of tables, so the same sequence can be used for one or multiple tables.

Syntax for creating a sequence:-

create sequence <sequence_name>
  [START WITH <integer>]
  [INCREMENT BY <integer>]
  [MAXVALUE <integer>]
  [MINVALUE <integer>]
  [CACHE <integer> | NO CACHE]
  [CYCLE | NO CYCLE];


Sequence Parameters:-

START WITH:- Specifies the first sequence number to be generated.

INCREMENT BY:- Specifies the interval between sequence numbers.

MAXVALUE:- Specifies the maximum value the sequence can generate.

MINVALUE:- Specifies the minimum value of the sequence.

Ex:-

CREATE SEQUENCE xx_seq_id 

 START WITH 1 

 INCREMENT BY 1 

 MAXVALUE 9999;

Once a sequence is created, to retrieve the next value of the sequence use NEXTVAL 

Ex:-

SELECT xx_seq_id.NEXTVAL FROM dual;

To retrieve the current value of the sequence use CURRVAL.

Ex:-
SELECT xx_seq_id.CURRVAL from dual;


Altering a Sequence:-
Syntax:-

ALTER SEQUENCE <sequence_name> <parameter name> <new value>;
Execute the below SQL code to alter the increment value of the sequence xx_seq_id

ALTER SEQUENCE xx_seq_id INCREMENT BY 5;

You cannot alter start value of a sequence.

Deleting a Sequence:-
Syntax:-

DROP SEQUENCE <sequence name>;
Execute the below code to drop the sequence xx_seq_id.

DROP SEQUENCE xx_seq_id.;

How many ways a Function can be invoked in Oracle SQL Statement?

Oracle Function can be invoked from the below clauses of SQL statements
• SELECT clause of SQL statement
• WHERE clause of a SELECT, UPDATE or DELETE statements and HAVING clauses of a SELECT statement
• VALUES clause of an INSERT statement
• SET clause of an UPDATE statement

Anchored Declaration in Oracle

Anchored Declaration is used to set the datatype of your variable based on the datatype of an already defined variable or a column of a table.

PL/SQL offers two kinds of anchoring:

Scalar anchoring :- Use the %TYPE attribute to define your variable based on a table's column or an already declared PL/SQL scalar variable.

Record anchoring :- Use the %ROWTYPE attribute to define your record structure based on a table or a predefined PL/SQL explicit cursor.

MTL_TRANSACTIONS_INTERFACE.PROCESS_FLAG in Oracle Apps(Transactions Interface)

PROCESS_FLAG	MEANING
1	Yet to be processed
2	Processed
3	Error

Query for get Concurrent Program Trace file Path Location in Oracle Apps

SELECT req.request_id,
       req.logfile_node_name node,
       req.oracle_Process_id,
       req.enable_trace,
          dest.VALUE
       || '/'
       || LOWER (dbnm.VALUE)
       || '_ora_'
       || oracle_process_id
       || '.trc'
          trace_filename,
       prog.user_concurrent_program_name,
       execname.execution_file_name,
       execname.subroutine_name,
       phase_code,
       status_code,
       ses.SID,
       ses.serial#,
       ses.module,
       ses.machine
  FROM fnd_concurrent_requests req,
       v$session ses,
       v$process proc,
       v$parameter dest,
       v$parameter dbnm,
       fnd_concurrent_programs_vl prog,
       fnd_executables execname
 WHERE     1 = 1
       AND req.request_id = &request      --Concurrent Request ID
       AND req.oracle_process_id = proc.spid(+)
       AND proc.addr = ses.paddr(+)
       AND dest.NAME = 'user_dump_dest'
       AND dbnm.NAME = 'db_name'
       AND req.concurrent_program_id = prog.concurrent_program_id
       AND req.program_application_id = prog.application_id
       AND prog.application_id = execname.application_id
       AND prog.executable_id = execname.executable_id

Access Control in Oracle EBS and Oracle Fusion

Access Control
Oracle EBS	Oracle Fusion
Responsibility	Data Role
Top Level Menu	Job Role Abstract Role
Sub Menu	Duty Role
Form Function	Privilege
Executable	Permission

Organization Structure in Oracle EBS and Oracle Fusion

Oracle EBS R12:-

Oracle Fusion 11g:-

API Script to fetch Table Structure from Backend in Oracle

Please Note that this script will fetch only Table Structure but not Primary Key, Foreign Keys, Unique Keys, Indexes..etc.

DECLARE
   x_table_header      VARCHAR2 (50);
   x_data_type_row   VARCHAR2 (100);
   x_data_type           VARCHAR2 (100);
   tab_count               NUMBER := 0;
   x_column_id          NUMBER := 0;

   CURSOR tab
   IS
      SELECT ao.object_name
        FROM all_objects ao
       WHERE ao.object_type = 'TABLE'
         AND ao.object_name = 'XX_TEST1';

   CURSOR cols (p_table_name VARCHAR2)
   IS
        SELECT *
          FROM all_tab_columns atc
         WHERE atc.table_name = p_table_name
      ORDER BY column_id;
BEGIN
   DBMS_OUTPUT.enable (10000000);

   FOR i IN tab
   LOOP
      tab_count := tab_count + 1;
      x_table_header := 'CREATE TABLE ' || i.object_name || ' (';

      DBMS_OUTPUT.PUT_LINE (x_table_header);

      FOR j IN cols (i.object_name)
      LOOP
         SELECT MAX (atc.column_id)
           INTO x_column_id
           FROM all_tab_columns atc
          WHERE atc.table_name = i.object_name;

         IF j.column_id <> x_column_id
         THEN
            SELECT    j.data_type
                   || DECODE (j.data_type,
                              'VARCHAR2', '(' || j.data_length || '),',
                              ',')
              INTO x_data_type
              FROM dual;

            x_data_type_row :=
               RPAD (j.column_name, 30, ' ') || '      ' || x_data_type;
         ELSIF j.column_id = x_column_id
         THEN
            SELECT    j.data_type
                   || DECODE (j.data_type,
                              'VARCHAR2', '(' || j.data_length || '));',
                              ');')
              INTO x_data_type
              FROM dual;

            x_data_type_row :=
               RPAD (j.column_name, 30, ' ') || '      ' || x_data_type;
         END IF;

         DBMS_OUTPUT.PUT_LINE (x_data_type_row);
      END LOOP;

      DBMS_OUTPUT.PUT_LINE (' ');
   END LOOP;
END;

How to copy Directories with cp Command in Linux

We can copy directories with the Linux cp command, we need to use -r option. For example, this command copies the directory named xxdir to the /home/xxuser directory:

cp -r xxdir /tmp/xxuser

If you try to copy a directory without using the -r argument we will get xxdir is a directory(not copied) error message as shown in below screenshot.

What's the difference between Primary Quantity and Secondary Quantity?

• Primary Quantity is the quantity of items in the primary unit of measure.

• Secondary Quantity is the quantity of items in the secondary unit of measure.

Concurrent Processes to Import Suppliers in Oracle Fusion

After  inserting data into Interface tables Supplier Interface Tables in Oracle Fusion the following concurrent processes must be run to import the supplier profile data in Oracle Fusion Suppliers:

• Import Suppliers
• Import Supplier Addresses
• Import Supplier Sites
• Import Supplier Site Assignments
• Import Supplier Contacts
• Import Supplier Business Classifications
• Import Supplier Products and Services Categories
• Import Supplier Attachments

Above Import Processes are executed by the Enterprise Scheduler Service (ESS), which manages all concurrent processes. Enterprise Scheduler Service allows you to monitor the status of each process and provides access to output reports. 

Below are the Supplier import processes parameters:

Import Options: 

• All
• New and 
• Rejected

Import Options are used to determine if the import process should attempt to import new, rejected, or all rows from the respective open interface tables.

Report Exceptions Only: 

• Yes 
• No. 

Used to determine if the Import Process Reports prints success and rejected information, or just the rejected information. 

When the processes are complete, a .pdf report is generated detailing the status of the records that were imported. If a record could not be imported, then the status is set to Rejected. 

The supplier import processes are used to create new entities, and to update or delete existing supplier profile data. Creation can be done only with attachments. Deletion can be done only with contact addresses, business classifications, and products and service categories.

Supplier import supports supplier numbering of characters that are not numeric to allow supplier records from legacy systems to retain their unique identifier.

Update supports updating the functional key attributes. The columns ending with _NEW are specifically earmarked for updates. Use this only if the underlying functional key attributes must be updated. To update functional keys, the new value should be provided in the _NEW column.

Supplier Interface Tables in Oracle Fusion

• POZ_SUPPLIERS_INT
• POZ_SUP_ADDRESSES_INT
• POZ_SUPPLIER_SITES_INT
• POZ_SITE_ASSIGNMENTS_INT
• POZ_SUP_CONTACTS_INT
• POZ_SUP_CONTACT_ADDRESSES_INT
• POZ_SUP_BUS_CLASS_INT
• POZ_SUP_PROD_SERV_INT
• POZ_SUP_ATTACHMENTS_INT

Supplier, Address and Site Creation in Oracle Fusion

Login to Oracle Fusion Application àSelect Procurement as shown in below screenshot

It will give below options like Purchase Requisitions, Purchase Agreements, Purchase Orders, My Receipts, Negotiations, Catalogs, Suppliers and Supplier Qualification. Here select Suppliers page  as shown in below screenshot.

Select Create Supplier from Task Panel Tab as shown in below screenshot.

You will get below screenshot.

Enter information as shown below

Field Name	Value
Supplier	XX Test Supplier
Business Relationship	Spend Authorized
Tax Organization Type	Corporation
Tax Country	United States
Tax Registration Number	XXTAXREGNUM
Taxpayer ID	XYZTAX1234
D-U-N-S Number	123456789

Note: You must enter 9 digits or 13 digits for D-U-N-S Number.

Click on Create button.

Profiles Details:-

As shown in above screenshot under Profile Details click on Transaction Tax tab. Select the Allow tax applicability check box as shown in below screenshot.

Click on Save button.

Supplier Address & Site Creation:-

Click the Addresses tab.

Click on Create Icon as shown in above screenshot.

Enter below information to create address.

Field Name	Value
Address Name	XX Supplier Site
Country	United States
Address Line 1	XX Main Street
City	Atlanta
State	GA
Postal Code	12345
Language	American English
Address Purpose	Ordering & Remit to
Phone	12345
Fax
Email	xxtestsuppliersite@xyz.xyz

Click on Save button.

In the Address Details section and on the Sites tab, click Add Icon as shown in below screenshot.

You will get below screenshot.

Enter below information

Field Name	Value
Procurement BU	XX Business Unit
Site	XX Supplier Sit
Site Purpose	Purchasing, Pay & Primary Pay

Click on Save and Close button

You will get confirmation as shown in below screenshot.

Creating a Site Assignment:-

Click the Sites tab as shown in below screenshot.

You will get below screen

Click the Site name link as shown in above screenshot. You will get below screen

Click Site Assignments Tab 

Click on Add icon as shown in above screenshot.

Select Client BU and Bill to BU as shown in below screenshot. Click on Save and Close button

You will get below confirmation message. Click on Ok Button.

Invoices in Oracle Fusion Payables

• An Invoice is a list of goods or Services rendered from the Supplier. 
• Oracle Fusion Payables allows us to capture all the attributes of the real-life invoice documents you receive from the suppliers.
• A Payables invoice consists of the following components: 

1. Header
2. Lines
3. Distributions and 
4. Installments. 

Login to Oracle Fusion ApplicationàSelect on Payables as shown in below screenshot

You will be able to see below screen

Click on Invoices. Invoice page will open with existing information such as Recent 24 Hours, Holds, Approvals and Prepaid details summary.

Select any invoice record to view the details.

• The Invoice Header has common information, such as Invoice Number and Invoice date, Invoice Type, Supplier Information, Payment Terms..etc.

• Invoice Lines has information about the goods and services.

• Distributions have invoice accounting details.

• Installments consist of payment due and discount information.

Invoice Types in Oracle Fusion Payables

• Standard:- An invoice from a supplier that represents an amount due for goods or services rendered.

• Prepayment:-An advance payment to a supplier.

• Credit Memo:- A document that extends a credit amount from a supplier for goods or services rendered.

• Debit Memo:- A credit from a supplier that does not send you a credit memo.

• Withholding Tax:- An invoice to a tax authority for withholding tax that is automatically generated. You can create withholding tax invoices manually if the option Allow manual withholding is enabled.

• Interest:- An invoice for interest on overdue invoices that is automatically generated.

• Standard invoice request:- An invoice submitted without a purchase order by a supplier through Oracle Fusion Supplier Portal that is pending review and approval by the appropriate persons within the deploying company.

• Credit memo invoice request:- A credit submitted without a purchase order by a supplier through Oracle Fusion Supplier Portal that is pending review and approval by the appropriate persons within the deploying company.

• Payment request:- A request from Oracle Fusion Expense or Oracle Fusion Receivables to disburse funds to a payee that is not defined as a supplier.

Implementation Users in Oracle Fusion

Implementation Users will manage the implementation of Oracle Human Capital Management Cloud (Oracle HCM Cloud).
• Administer Oracle HCM Cloud users and security, both during and after implementation.
• Set up basic enterprise structures for an Oracle HCM Cloud service.
Implementation users have the necessary access for both initial implementation of the Oracle HCM Cloud service and its
ongoing maintenance. You're recommended to create at least one implementation user.

Function Security Privileges in Oracle Fusion HCM

• Each Function Security Privilege secures the code resources that make up the relevant page, such as the Promote Worker page.

• If the predefined security reference implementation doesn't fully represent your enterprise, then you can make changes. For example, the predefined Line Manager abstract role includes compensation management duties. 

• If some of your line managers don't handle compensation, then you can create a custom line manager role without those duties. 

• Alternatively, if a predefined job role is too narrowly defined, then you can create a job role with a greater range of duties than its predefined equivalent.

Data Security Policies in Oracle Fusion HCM

Data Security Policy combines:

• A Duty role. Ex:- Worker Promotion Duty.

• A business object being accessed. Ex:- Person Assignment.

• The condition that must be met for access to be granted. Ex:- Human Resource Specialists can promote workers whose person and assignment records are identified in their person and assignment security profiles. 

• A Data Security Privilege that defines the action being performed. Ex:- Promote Worker.

Duty Roles in Oracle Fusion HCM

• Duty Roles are associated with Function Security Privileges(FSV) and Data Security Policies(DSP).

• In the above screenshot Worker Promotion Duty Role is associated with one Function Security Privilege and two Data Security Policies.

• The FSP Promote Worker secures access to the Promote Worker page. 

• The DSP Promote Worker Data determines the workers whom users with this duty role can promote.

• The DSP Choose Position Data determines the positions into which users with this duty role can promote workers.

Role Inheritance in Oracle Fusion HCM

• Each Role is a Hierarchy of other Roles.
• HCM Data Roles inherit Job or Abstract Roles.
• Job and Abstract Roles inherit Duty Roles.
• Duty Roles inherit other Duty Roles.
• When you assign both Data and Abstract roles to users, they inherit the data and function security associated with those roles.

In above screenshot User ABC has 2 Roles.

1. HR Specialist Vision Corporation which is a data role.
2. Employee which is an abstract role.

• Role HR Specialist Vision Corporation inherits the job role Human Resource Specialist which in turn inherits the duty roles that provide access to the tasks and functions that a human resource specialist performs. The security profile assigned to the data role provides the data access for the role.

• Role Employee inherits the duty roles that provide access to all tasks and functions, unrelated to a specific job, that every employee performs. The security profile assigned to the abstract role provides the data access for the role.