Important Questions in Oracle Payables

• Types of Invoices in Oracle Apps?
• Standard
• Prepayment
• Expense Report
• Mixed Invoice
• Credit Invoice
• Debit Invoice

• What is Mixed Type Invoice in Oracle Apps?
• Mixed Invoice is one of the Invoice Type in the Oracle Payables. 
• You can enter both the Negative(-) and the Positive(+) amount for this Mixed Type Invoice. 
• This Payment type is not rigid like Standard, Prepayment, Credit & Debit Memo Invoices to enter the amount in the specific signs (Positive or Negative).

• Difference between the Manual Hold and the System Hold?
• System Hold apply to the Invoice if something mismatched in the Invoice as per the Standard Process like Invoice Header Total and Line Total Should be equal other System Hold for Example related to Invoice Matching if something goes about the Invoice Tolerance Limit then System put the Hold. 
• System hold is something related to setup Controls but whereas Manual Hold is something which put manually in the Invoice due to any reason like Product received from the Supplier is damaged so need to hold the Payment for that Invoice.

• What is Pay alone in AP Invoice?
• Pay alone is something related to Invoice Payment. 
• This is the Flag we set for AP Invoice, it means this invoice will be paid alone. 
• For example, Supplier XYZ has two Invoices, but for one Invoice we have enabled the Pay Alone Flag then when we will run the Payment Batch then System will create one check for one invoices and separate one check for Pay Alone Invoice. This is the working of Pay Alone.

• Can we pay the AP invoice before Due Date?
• Yes, we can pay the Invoice before Due date. 
• For Manual Payment, there is no concern but for Payment Batch, when we are running the Payment Process Request((PPR) then we need to Enter the Pay Through Date this Date is Very Important, If we have set the Pay Through Date “15-Aug” then this Payment Request will pick only those AP invoices which has been due before “15-Aug-YYYY”.

• How AP Invoice Payment Due Date Calculates in Oracle Payables?
• Invoice Payment Due date depends on the two factors.
• Payment Terms attached to the Invoice
• Invoice Date.
• For Example, if Payment Terms is 30 Days and the Invoice Date is 01-Aug-2020 then Payment Due Date Will be 01-Sep-2020

• What is Primary and Secondary Ledger in Oracle Payables?
• Primary Ledger is the main Ledger and Secondary Ledger is the replica of the Primary Ledger. 
• Transactions will be done in the Primary Ledger. 
• The main reason of using Primary and Secondary Ledger is the difference in the Organization requirement and the statuary requirement. 
• For Ex, One US based company office is in India and as per US, their Calendar works from Oct to Sep but the in India their calendar works from April to March. So in this Kind of requirement, Primary and Secondary Ledger concept comes. Where we can design the Primary calendar as per the US based but can design the Secondary calendar as per India statuary requirement. 

• What is Recurring Invoice in Oracle?
• As its name represents ‘Recurring’. Recurring means again and again. If any Organization books the Office rent invoice every month for the same amount or any other fixed expenses every month then oracle has provided the Recurring Invoice. We just need to do the recurring Invoice setup for that amount and system will create the invoice automatically on the First day of the Months.

• What is the use of Payables Trial Balance Report ?
• Payables Trial Balance Report shows the Total Liability or the Supplier Outstanding in the System. 
• This Report shows the Liability in the System supplier and Site Level. 
• This Provide the Summary Information’s for all the Unpaid amount for the Supplier Invoices which are validated.

• What we do in the AP and GL reconciliation ?
• In the AP and GL reconciliation, we try to match the Total Liability from the Payables with the Liability accounts total in the GL. 
• We have some set of Liability accounts in the Payables, which we only use in the Invoice Headers to book the Liability and we match only these Liability GL accounts in the AP and GL reconciliation report.
• We took the help of Payables Trial Balance report to find the Total AP liability and then run the GL Trial Balance report to match the Payables Trial Balance Report Total with the GL trial Liability Accounts.

• Distribution Set in Oracle Payables?
• Distribution set is the combination of multiple Distribution lines using different -2 GL accounts Combination. 
• For Example, we booked most of the AP invoices in two different GL accounts combination so each time we need to enter two lines in the invoice distributions to book the AP invoices expenses in these two GL accounts but this process can be make query quickly and easy. 
• Oracle have functionality like Distributions set in Oracle Payables where we can any number of GL accounts line for a Given Distribution sets and then in the Oracle Payables Invoices we don’t need to create Distribution lines manually. 
• We just need to enter the Distributions Set in the AP Invoice Lines and oracle system automatically creates the Invoice distribution lines with GL accounts given in the distribution set in Oracle Payables R12.

HRMS Assignment API in Oracle APPS

 PROCEDURE update_emp_asg

  (p_validate                     IN     BOOLEAN  DEFAULT FALSE

  ,p_effective_date               IN     DATE

  ,p_datetrack_update_mode        IN     VARCHAR2

  ,p_assignment_id                IN     NUMBER

  ,p_object_version_number        IN OUT NUMBER

  ,p_supervisor_id                IN     NUMBER   DEFAULT hr_api.g_number

  ,p_assignment_number            IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_change_reason                IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_comments                     IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_date_probation_end           IN     DATE     DEFAULT hr_api.g_date

  ,p_default_code_comb_id         IN     NUMBER   DEFAULT hr_api.g_number

  ,p_frequency                    IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_internal_address_line        IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_manager_flag                 IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_normal_hours                 IN     NUMBER   DEFAULT hr_api.g_number

  ,p_perf_review_period           IN     NUMBER   DEFAULT hr_api.g_number

  ,p_perf_review_period_frequency IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_probation_period             IN     NUMBER   DEFAULT hr_api.g_number

  ,p_probation_unit               IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_sal_review_period            IN     NUMBER   DEFAULT hr_api.g_number

  ,p_sal_review_period_frequency  IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_set_of_books_id              IN     NUMBER   DEFAULT hr_api.g_number

  ,p_source_type                  IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_time_normal_finish           IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_time_normal_start            IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_bargaining_unit_code         IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_labour_union_member_flag     IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_hourly_salaried_code         IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute_category       IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute1               IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute2               IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute3               IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute4               IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute5               IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute6               IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute7               IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute8               IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute9               IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute10              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute11              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute12              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute13              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute14              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute15              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute16              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute17              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute18              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute19              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute20              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute21              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute22              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute23              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute24              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute25              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute26              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute27              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute28              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute29              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_ass_attribute30              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_title                        IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_tax_unit                     IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_timecard_approver            IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_timecard_required            IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_work_schedule                IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_shift                        IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_spouse_salary                IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_legal_representative         IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_wc_override_code             IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_eeo_1_establishment          IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_comment_id                   OUT NUMBER

  ,p_soft_coding_keyflex_id       OUT    NUMBER

  ,p_effective_start_date         OUT    DATE

  ,p_effective_end_date           OUT    DATE

  ,p_concatenated_segments           OUT VARCHAR2

  ,p_concat_segments              IN     VARCHAR2 DEFAULT hr_api.g_varchar2

  ,p_no_managers_warning             OUT BOOLEAN

  ,p_other_manager_warning           OUT BOOLEAN

  )

IS

  -- Declare cursors and local variables

  l_proc                       VARCHAR2(72) := g_package||'update_emp_asg';

  l_effective_date             DATE;

  l_legislation_code           per_business_groups.legislation_code%TYPE;

  CURSOR check_legislation

    (c_assignment_id  per_assignments_f.assignment_id%TYPE,

     c_effective_date DATE

    )

  IS

    SELECT bgp.legislation_code

      FROM per_assignments_f asg

          ,per_business_groups bgp

     WHERE asg.business_group_id = bgp.business_group_id

       AND asg.assignment_id = c_assignment_id

       AND c_effective_date BETWEEN effective_start_date AND effective_end_date;

  --

BEGIN

  -- Truncate date variables

  l_effective_date := TRUNC(p_effective_date);

  

  OPEN check_legislation(p_assignment_id, l_effective_date);

  FETCH check_legislation INTO l_legislation_code;

IF check_legislation%NOTFOUND THEN

CLOSE check_legislation;

--Print Error Message here

END IF;

  CLOSE check_legislation;

  

  -- Check that the legislation of the specified business group is 'US'.

  IF l_legislation_code <> 'US' THEN

    --Print Error Message here

  END IF;

  -- Call update_emp_asg business process

  

  hr_assignment_api.update_emp_asg

(p_validate                     => p_validate

,p_effective_date               => p_effective_date

,p_datetrack_update_mode        => p_datetrack_update_mode

,p_assignment_id                => p_assignment_id

,p_object_version_number        => p_object_version_number

,p_supervisor_id                => p_supervisor_id

,p_assignment_number            => p_assignment_number

,p_change_reason                => p_change_reason

,p_comments                     => p_comments

,p_date_probation_end           => p_date_probation_end

,p_default_code_comb_id         => p_default_code_comb_id

,p_frequency                    => p_frequency

,p_internal_address_line        => p_internal_address_line

,p_manager_flag                 => p_manager_flag

,p_normal_hours                 => p_normal_hours

,p_perf_review_period           => p_perf_review_period

,p_perf_review_period_frequency => p_perf_review_period_frequency

,p_probation_period             => p_probation_period

,p_probation_unit               => p_probation_unit

,p_sal_review_period            => p_sal_review_period

,p_sal_review_period_frequency  => p_sal_review_period_frequency

,p_set_of_books_id              => p_set_of_books_id

,p_source_type                  => p_source_type

,p_time_normal_finish           => p_time_normal_finish

,p_time_normal_start            => p_time_normal_start

,p_bargaining_unit_code         => p_bargaining_unit_code

,p_labour_union_member_flag     => p_labour_union_member_flag

,p_hourly_salaried_code         => p_hourly_salaried_code

,p_ass_attribute_category       => p_ass_attribute_category

,p_ass_attribute1               => p_ass_attribute1

,p_ass_attribute2               => p_ass_attribute2

,p_ass_attribute3               => p_ass_attribute3

,p_ass_attribute4               => p_ass_attribute4

,p_ass_attribute5               => p_ass_attribute5

,p_ass_attribute6               => p_ass_attribute6

,p_ass_attribute7               => p_ass_attribute7

,p_ass_attribute8               => p_ass_attribute8

,p_ass_attribute9               => p_ass_attribute9

,p_ass_attribute10              => p_ass_attribute10

,p_ass_attribute11              => p_ass_attribute11

,p_ass_attribute12              => p_ass_attribute12

,p_ass_attribute13              => p_ass_attribute13

,p_ass_attribute14              => p_ass_attribute14

,p_ass_attribute15              => p_ass_attribute15

,p_ass_attribute16              => p_ass_attribute16

,p_ass_attribute17              => p_ass_attribute17

,p_ass_attribute18              => p_ass_attribute18

,p_ass_attribute19              => p_ass_attribute19

,p_ass_attribute20              => p_ass_attribute20

,p_ass_attribute21              => p_ass_attribute21

,p_ass_attribute22              => p_ass_attribute22

,p_ass_attribute23              => p_ass_attribute23

,p_ass_attribute24              => p_ass_attribute24

,p_ass_attribute25              => p_ass_attribute25

,p_ass_attribute26              => p_ass_attribute26

,p_ass_attribute27              => p_ass_attribute27

,p_ass_attribute28              => p_ass_attribute28

,p_ass_attribute29              => p_ass_attribute29

,p_ass_attribute30              => p_ass_attribute30

,p_title                        => p_title

,p_segment1                     => p_tax_unit

,p_segment2                     => p_timecard_approver

,p_segment3                     => p_timecard_required

,p_segment4                     => p_work_schedule

,p_segment5                     => p_shift

,p_segment6                     => p_spouse_salary

,p_segment7                     => p_legal_representative

,p_segment8                     => p_wc_override_code

,p_segment9                     => p_eeo_1_establishment

,p_soft_coding_keyflex_id       => p_soft_coding_keyflex_id

,p_comment_id                   => p_comment_id

,p_effective_start_date         => p_effective_start_date

,p_effective_end_date           => p_effective_end_date

,p_concatenated_segments        => p_concatenated_segments

,p_concat_segments              => p_concat_segments

,p_no_managers_warning          => p_no_managers_warning

,p_other_manager_warning        => p_other_manager_warning

);

END update_emp_asg;

15 commonly asked SQL & PL/SQL Interview Questions in Oracle

1. Difference between TRUNCATE and DELETE?
2. Difference between DECODE and CASE?
3. What is RANK and DENSE RANK?
4. What is cursor and its attributes?
5. What is BULK COLLECT and how does it work?
6. Difference between various Collection Types?
7. What are the various ways to do the Exception Handling in Oracle?
8. Few performance tuning techniques?
9. What is the use of Index, its types and how does indexes help in performance improvement?
10. Describe the Triggers and its types with example?
11. What is View and Materialized view?
12. Describe ref cursor using example?
13. How does queries execute in Oracle? Describe the process of it’s execution?
14. What would you check if the query is running long than expected?
15. What is Mutating Trigger?

dbms_trace in Oracle

• dbms_trace provides subprograms to start and stop PL/SQL tracing in a session. The trace data is collected as the program executes, and it is written out to data dictionary tables.

• set_plsql_trace: This procedure starts tracing data dumping in a session. Provide the trace level at which you want your PL/SQL code traced as an IN parameter.

• clear_plsql_trace: This procedure stops trace data dumping in a session.

• plsql_trace_version: This procedure returns the version number of the trace package as an OUT parameter

• A typical trace session involves:
• Enabling specific subprograms for trace data collection. This is optional.
• Starting the PL/SQL tracing session dbms_trace.set_plsql_trace
• Running the application that is to be traced
• Stopping the PL/SQL tracing session dbms_trace.clear_plsql_trace

• Specifying a Trace Level: During the trace session, there are 2 levels that you can specify to trace calls, exceptions, SQL, and lines of code.
• Trace Calls
• Level 1: Trace all calls. This corresponds to the constant trace_all_calls.
• Level 2: Trace calls only to enabled program units. This corresponds to the constant trace_enabled_calls.

• Trace Exceptions:
• Level 1: Trace all exceptions. This corresponds to trace_all_exceptions.
• Level 2: Trace exceptions raised only in enabled program units. This corresponds to trace_enabled_exceptions.

• Trace SQL:
• Level 1: Trace all SQL. This corresponds to the constant trace_all_sql.
• Level 2: Trace SQL in only enabled program units. This corresponds to the constant trace_enabled_sql.

• Trace Lines:
• Level 1: Trace all lines. This corresponds to the constant trace_all_lines.
• Level 2: Trace lines only in enabled program units. This corresponds to the constant trace_enabled_lines.

• Steps to Trace PL/SQL Code:
• There are five steps to trace PL/SQL code using the dbms_trace package:
• Enable specific program units for trace data collection.
• Use dbms_trace.set_plsql_trace to identify a trace level.
• Run your PL/SQL code.
• Use dbms_trace.clear_plsql_trace to stop tracing data.
• Read and interpret the trace information.

DBMS_DESCRIBE in Oracle

DBMS_DESCRIBE: DBMS_DESCRIBE is the Oracle-supplied package to obtain information about a PL/SQL object. The package contains the DESCRIBE_PROCEDURE procedure, which provides a brief description of a PL/SQL stored procedure. It takes the name of a stored procedure and returns information about each parameter of that procedure.

ALL_DEPENDENCIES in Oracle

ALL_DEPENDENCIES: Is one of the several views that give you information about the dependencies between database objects.

ALL_PROCEDURES in Oracle

ALL_PROCEDURES: Contains the list of procedures and functions that you can execute

ALL_ARGUMENTS in Oracle

ALL_ARGUMENTS: Includes information about the parameters for the procedures and functions that you can call.

ALL_SOURCE in oracle

ALL_SOURCE: Includes the lines of source code for all programs that you modify.

SQL Query Result Cache in Oracle

• SQL Query Result Cache can improve the performance of your queries by caching the results of a query in memory, and then using the cached results in future executions of the query or query fragments. 
• The cached results reside in the result cache memory portion of the SGA. 
• This feature is designed to speed up query execution on systems with large memories.

• SQL result caching is useful when queries need to analyze a large number of rows to return a small number of rows or a single row.
• Two new optimizer hints are available to turn on and turn off SQL result caching:
• /*+ result_cache */
• /*+ no_result_cache */
• These hints enable you to override the settings of the RESULT_CACHE_MODE initialization parameter.
• You can execute DBMS_RESULT_CACHE.MEMORY_REPORT to produce a memory usage report of the result cache.

DBMS_RESULT_CACHE package in Oracle

• The DBMS_RESULT_CACHE package provides an interface for a DBA to manage memory allocation for SQL query result cache and the PL/SQL function result cache. It is used to perform various operations such as bypassing the cache, retrieving statistics on the cache memory usage, and flushing the cache. To view the memory allocation statistics, use dbms_result_cache.memory_report. 

DBMS_ASSERT in Oracle

• DBMS_ASSERT is an Oracle-supplied PL/SQL package containing seven functions that can be used to filter and sanitize input strings, particularly those that are meant to be used as Oracle identifiers.
• NOOP: NOOP does not perform any validation and returns the string unchanged. Allows developers to mark some data as trusted, and thereby, disable some SQL injection checks in the source scanning tools. Avoid using this function unless approved by the product security compliance team. 
• ENQUOTE_LITERAL: ENQUOTE_LITERAL encloses string literal in single quotes
• ENQUOTE_NAME: ENQUOTE_NAME encloses string literal in double quotes
• SIMPLE_SQL_NAME: SIMPLE_SQL_NAME verifies that the string is a simple SQL name
• QUALIFIED_SQL_NAME: QUALIFIED_SQL_NAME verifies that the string is a qualified SQL name
• SCHEMA_NAME: SCHEMA_NAME verifies that the string is an existing schema name
• SQL_OBJECT_NAME: SQL_OBJECT_NAME verifies that the string is a qualified identifier of an existing SQL object
• While two of these functions can be used to filter and sanitize any input strings, the majority of them are specifically crafted to validate Oracle identifiers. These are the ENQUOTE_LITERAL and the ENQUOTE_NAME functions. The other functions either do nothing i.e. the NOOP function or return the input string unchanged if the verification algorithm does not raise any exceptions.
• When using the DBMS_ASSERT package, always specify the SYS schema rather than relying on a public synonym.

SQL Injection in Oracle

• SQL Injection is a technique for maliciously exploiting applications that use client-supplied data in SQL statements.
• Attackers trick the SQL engine into executing unintended commands via supplying specially crafted string input, thereby gaining unauthorized access to a database in order to view or manipulate restricted data.
• SQL Injection techniques may differ, but they all exploit a single vulnerability in the application.
• String literals that are incorrectly validated or not validated are concatenated into a dynamic SQL statement and interpreted as code by the SQL engine.
• To immunize your code against SQL injection attacks, you must use bind arguments i.e. either automatically with static SQL, or explicitly with dynamic SQL or validate all input concatenated to dynamic SQL.
• A program or an application may be vulnerable to SQL Injection
• Web applications are at a higher risk, because an attacker can perpetrate SQL injection attacks without any database or application authentication.

Large Object(LOB) in Oracle

• A LOB is a data type that is used to store large, unstructured data such as Text(CLOB), Graphic Images(BLOB), Video Clippings(BFILE),..etc. Structured data, such as a customer record, may be a few hundred bytes large, but even small amounts of multimedia data can be thousands of times larger. 
• Also, multimedia data may reside in operating system (OS) files, which may need to be accessed from a database.
• There are four large object data types:
• BLOB: BLOB represents a binary large object, such as a video clip.
• CLOB: CLOB represents a character large object.
• NCLOB: NCLOB represents a multiple-byte character large object.
• BFILE: BFILE represents a binary file stored in an OS binary file outside the database. The BFILE column or attribute stores a file locator that points to the external file.
• LOBs are characterized in two ways, according to their interpretations by the Oracle server i.e. binary or character and their storage aspects. 
• LOBs can be stored internally i.e. inside the database or in host files. 
• There are two categories of LOBs:
• Internal LOBs (CLOB, NCLOB, BLOB): Stored in the database
• External files (BFILE): Stored outside the database
• Oracle Database 10g performs implicit conversion between CLOB and VARCHAR2 data types.

• The other implicit conversions between LOBs are not possible. For Ex:, if the user creates a table XYZ with a CLOB column and a table ABC with a BLOB column, the data is not directly transferable between these two columns.
• BFILEs can be accessed only in read-only mode from an Oracle server.
• There are two parts to a LOB:
• LOB value: The data that constitutes the real object being stored
• LOB locator: A pointer to the location of the LOB value that is stored in the database
• Irrespective of where the LOB value is stored, a locator is stored in the row. We can think of a LOB locator as a pointer to the actual location of the LOB value.
• A LOB column does not contain the data; it contains the locator of the LOB value.
• When a user creates an internal LOB, the value is stored in the LOB segment and a locator to the
• out-of-line LOB value is placed in the LOB column of the corresponding row in the table.
• External LOBs store the data outside the database, so only a locator to the LOB value is stored in the table.
• To access and manipulate LOBs without SQL data manipulation language (DML), we have to create a LOB locator. The programmatic interfaces operate on the LOB values by using these locators in a manner similar to OS file handles.

Components of Fusion Accounting Hub

Oracle Fusion Sub Ledger Accounting to perform accounting transformations on external system data

• Oracle Fusion General Ledger
• Oracle Fusion Financial Reporting Center
• Integration with Oracle Hyperion Data Management, Fusion Edition for chart of accounts and hierarchy maintenance
• Applications coexistence integration with the Oracle E-Business Suite and Oracle PeopleSoft General Ledgers
• Integration with Oracle Hyperion Planning, Fusion Edition and Oracle Hyperion Financial Management, Fusion Edition.

Technology Stack of Fusion Application

Following table shows different components which varies from Oracle e-Business Suite to Oracle Fusion Applications. 

Component	Oracle e-Business Suite	Oracle Fusion Applications
Database	Oracle Database 10g	Oracle Database 11g
Application Server	Oracle Application Server 10.1.2 (Forms) Oracle Application  Server 10.1.3 (OC4J)	Oracle WebLogic
User Interface	Forms, JSPs	Oracle ADF + ADF Java Server Faces
Portal	Oracle Portal	Oracle Webcenter
Data Model	Oracle eBS Data Model	Oracle eBS Data Model + Siebel + PoepleSoft (Trees, Data Effectivity, person Model)
Attachments/Imaging	BLOBs	UCM/Stellant
Workflow/Approval	PL/SQL	BPEL
Reports	Reports (11i), Discoverer	BI Publisher
Analytics	Discoverer	OBIEE
Financial Reporting	Financial Statement Generator	Hyperion
Integration	AIA	AIA + BPEL + More web services
XML Gateway	XML Gateway	BPEL, B2B Adapter

 

Technology Difference between E-business Suite and Fusion Applications

Component	Oracle E-Business Suite	Oracle Fusion Applications
Database	Oracle Database 10G	Oracle Database 11G
Application Server	Oracle Application server 10.1.2	Oracle WebLogic
User Interface	Forms, JSPs	Oracle ADF+ADF Java Server Pages
Portal	Oracle Portal	Oracle Web Center
Data Model	Oracle EBS Data Model	Oracle EBS Data Model + Siebel + PeopleSoft (Trees, Date Effectivity, Person Model)
Attachments / Imaging	BLOBs	Stellant
Workflow / Approval	PL /SQL	BPEL
Reports	Reports (11i), Discoverer	BI Publisher
Analytics	Discoverer	OBIEE
Financial Reporting	Financial Statement Generator	Hyperion
Integration	AIA	AIA +BPEL + more web services
XML Gateway	XML Gateway	BPEL w/B2B Adapter

 

Oracle Fusion Middleware Components

Below are the Oracle Fusion Middleware Components: 

• Oracle WebLogic Server: Java application server that helps to deploy multiple applications in a robust and scalable environment.
• Oracle WebLogic Communication Services: provides click-to-dial functionality through contextual Actions which further helps users with information and actions.
• Oracle Identity Management: automatically manages user access and provides services and interfaces for third party application developments.
• Oracle Web Center: combines user interface technologies for building social applications.
• Oracle Business Intelligence: offers a complete to generates and delivers analyses for fusion application.
• Oracle SOA Suite: comprehensive software for building and deploying service-oriented architecture.
• Oracle Application Development Framework: a java framework to simplify application development by minimizing code writing
• Oracle JDeveloper: for building service-oriented applications using industry standards 

Fusion Accounting Hub

• Oracle Fusion Accounting Hub (FAH) is a single source for managing all financial requirement as well as Reporting requirements against transaction and provides complete set of accounting tools and access to financial data. 
• FAH receives data from multiple third party transactional system and also from the various Fusion Applications Products [ Sub ledgers ] and applies accounting rules to meet the regulatory reporting and business reporting requirements.
• Oracle Fusion Accounting Hub is integrated with Oracle Hyperion Financial Management (HFM) as the latter is a powerful consolidation solution to collect other non-financial information. 
• Oracle acquired Hyperion and started using benefits of Hyperion for consolidation, allocation and reporting in Fusion. 
• Hyperion has been leader in financial domain for years together.
• Fusion Accounting Hub (FAH) acts as integration platform for centralized accounting and its ability to generate multiple accounting representations (e.g., IFRS, local GAAP, industry, regulatory) for a single sub ledger transaction. 
• FAH is a Next-Generation Financial Analysis for Centralized Reporting. 
• Configurable accounting rules helps to capture transaction information used to create accounting. 
• This feature of Fusion is highly configurable to satisfy capturing of transactions and to create different representation requirement for a company.
• Fusion Accounting Hub deals with transactional details and is intended for day-to-day operational reporting and analytics. 
• Hyperion Financial Management (HFM) deals with period-end balances, not transactional details, and is generally not used as an intra-period reporting system.

Fusion Vs EBS R12.

• Date track in EBS is known as Data effectivity in Fusion
• Operating Unit in EBS is known as Business Unit.
• Major improvement is there is no need to create multiple COA. Single COA structure in fusion can handle multiple COA. this is the major improvement compare to R12.
• Reference Data to move configuration across business unit. This is Set ID. Application needs to be setup in Fusion with reference data before make it available transactions.
• Functional setup manager to move configuration across the instances.
• No more WebADI in Fusion. It is handled by ADFDI in Fusion. This fits with ADF architecture.

 

Approval Status of Lease in Oracle Property Manager

A lease can have either Draft or Final approval status. The approval status of the lease determines what actions you can take regarding two areas of the lease abstraction process: making changes to the lease and generating payments and billings.

• Changes to the Lease: If the lease is in draft status, you can modify, delete, or add to the existing lease information. If the lease is in final status, you cannot make changes to the lease unless you perform an edit or create a lease amendment.

• Payment and Billing Schedules: When you enter payment or billing terms in the Leases window and change the lease approval status from Draft to Final, you initiate the process that creates scheduled payments or scheduled billings. 

Lease Statuses

The lease status describes the position of the lease in the lease life cycle. Oracle Property Manager provides the following lease statuses:

• Active: Signifies that the lease is within the agreed lease term.
• Holdover: Signifies that the tenant retains possession of leased property after the lease expires. The landlord, by accepting rent, agrees to the tenant's continued occupancy. 
• Lease Ordered: Signifies that the landlord and tenant have committed to the general terms of a lease. However, they have not finalized the lease because of certain open issues. You can make or receive one time non-normalized payments, such as security deposits.
• Month-to-Month: Signifies that the tenant leases the property for one month at a time. You can create a new lease in this status. However, this status is used most commonly when a lease expires and the landlord and tenant have not finalized a new agreement to continue the tenancy.
• Signed: Signifies that the landlord and tenant have signed the lease. However, certain pending issues may prevent you from finalizing the lease. As in the case of the Lease Ordered status, you can make or receive one-time non-normalized rent payments. 
• Terminated: Signifies that the lease agreement has ended.

Using the Lease Ordered and Signed Lease Statuses

• Use the Lease Ordered (LOF) and Signed (SGN) lease statuses to schedule payments or billings and transfer transactions to Oracle Payables or Oracle Receivables before you finalize the lease. To finalize the lease, you must also change the Approval Status of the lease. 
• When you save a lease with a status of LOF or SGN, Oracle Property Manager creates schedules and items for all one-time non-normalized terms.
• Accounting guidelines state that you cannot normalize payment or billing terms until you finalize a lease. Accordingly, Oracle Property Manager does not normalize terms while a lease is in SGN or LOF lease status.

• Oracle Property Manager runs the Schedules and Items concurrent program each time you create one-time payment or billing terms. After you save your work, the limitations for modifying terms in a finalized lease apply to modifying these one-time lease terms. 
• In other respects, the lease functions as a draft lease. Therefore, you can freely edit other lease attributes, including the lease commencement date and the attributes of the other lease terms.
• If you change the lease commencement date, Oracle Property Manager does not automatically change the dates for any of the lease terms.