Oracle Apps Module Wise Main Tables Description

                                             
                                                         INV – Inventory
MTL_SYSTEM_ITEMS_B Inventory item definitions
MTL_CATEGORIES_B Code combinations table for Item Category
MTL_CATEGORY_SETS_B Category Sets
MTL_CUSTOMER_ITEMS Customer item Information
MTL_CUSTOMER_ITEM_XREFS Relationships between customer items and inventory items
MTL_DEMAND Sales order demand and reservations
MTL_DEMAND_HISTORIES Sales order demand and reservations
MTL_ITEM_LOCATIONS Definitions for stock locators
MTL_ITEM_REVISIONS_B Item revisions
MTL_ITEM_TEMPLATES_B Item template definitions
MTL_ITEM_TEMPL_ATTRIBUTES Item attributes and attribute values for a template
MTL_LOT_NUMBERS Lot number definitions
MTL_MATERIAL_TRANSACTIONS Material transaction table
MTL_MATERIAL_TRANSACTIONS_TEMP Temporary table for processing material transactions
MTL_ONHAND_QUANTITIES_DETAIL FIFO quantities by control level and receipt
MTL_PARAMETERS Inventory control options and defaults
MTL_RESERVATIONS Reservations
MTL_SECONDARY_INVENTORIES Subinventory definitions
MTL_SECONDARY_LOCATORS Item-subinventory-locator assignments
MTL_SERIAL_NUMBERS Serial number definitions
MTL_TRANSACTION_ACCOUNTS Material transaction distributions
MTL_TRANSACTION_TYPES Inventory Transaction Types Table
MTL_TXN_REQUEST_HEADERS Move Order headers table
MTL_TXN_REQUEST_LINES Move order lines table
MTL_UNIT_TRANSACTIONS Serial number transactions


                                                 ONT- Order Management
OE_ORDER_HEADERS_ALL stores header information for orders in Order Management.
OE_ORDER_LINES_ALL stores information for all order lines in Oracle Order Management.
OE_ORDER_SOURCES Feeder System Names that create orders in Order Management tables.
OE_ORDER_HOLDS_ALL This table stores information of all the orders and lines that are on hold and the link to hold sources and hold releases.
OE_SALES_CREDITS This table stores information about sales credits.
OE_TRANSACTION_TYPES_ALL This table stores information about the order and line transaction types
WSH_DELIVERY_ASSIGNMENTS Delivery Assignments
WSH_DELIVERY_DETAILS Delivery Details
WSH_NEW_DELIVERIES Deliveries
WSH_TRIPS Trips
WSH_TRIP_STOPS Trip Stops
WSH_DELIVERY_LEGS
WSH_PICKUP_BATCHES


                                                    PO - Purchasing
PO_HEADERS_ALL: Purchase Order information with Supplier, Site and status
PO_LINES_ALL: PO Lines with Item Information and quantity
PO_LINE_LOCATIONS_ALL: PO Information on Shipments Destination
PO_DISTRIBUTIONS_ALL: Purchase order Distributions related to Accounts 
PO_LINE_LOCATIONS_ARCHIVE_ALL: table updated for history on Shipment/Destination Location Information
PO_LINE_TYPES_B: PO Types used in the PO_LINES_ALL
PO_RELEASES_ALL: Stores revision number for the PO
PO_HEADERS_ARCHIVE_ALL: table updated for the history on the status and PO Header changes, Lines, Location and PO Accounts Distribution
PO_LINES_ARCHIVE_ALL: table updated for the history on the lines
PO_DISTRIBUTIONS_ARCHIVE_ALL: table updated for the history on the account distribution
PO_AGENTS_ALL_V: Buyers Info
PO_VENDORS: Supplier Table
PO_VENDOR_SITES: Supplier Sites
PO_VENDOR_CONTACTS: Supplier Contacts
PO_HAZARD_CLASSES: contains code and description for hazardous items which gets automatically printed into purchase order, RFQ and Receipt Travelers
PO_REQUISITION_LINES_ALL: Requisition Lines
PO_REQUISITION_HEADERS_ALL: Requisition Headers
PO_REQ_DISTRIBUTIONS_ALL: Distribution Lines of Requisition where accounts are
PO_APPROVED_SUPPLIER_LIST: Supplier List for Auto-Sourcing
PO_ASL_DOCUMENTS: Advanced Shipment Documents
PO_APPROVAL_LIST_HEADERS: PO Approval Path
PO_APPROVAL_LIST_LINES: PO Approval Path
PO_ACTION_HISTORY Document approval and control action history table

                                                          PO Related Tables 
RCV_SHIPMENT_HEADERS: Shipment Table Header with grouping information
RCV_SHIPMENT_LINES: Shipment Table lines with item information
RCV_TRANSACTIONS: PO Lines or Requisition received in destination or transit
RCV_ACCOUNTING_EVENTS: Receiving information on accounts
RCV_RECEIVING_SUB_LEDGER: Accounting entries generated for the receiving transactions
RCV_SUB_LEDGER_DETAILS: Detail accounting entries generated for the receiving transactions
RCV_LOT_SUPPLY: Parent for RCV_LOT_TRANSACTIONS
RCV_LOT_TRANSACTIONS: Table housing the information what lot the item is received


                                            AR- Accounts Receivables
RA_CUST_TRX_TYPES_ALL Transaction type for invoices, commitments and credit memos
RA_CUSTOMER_TRX_ALL Header-level information about invoices, debit memos, chargebacks, commitments and credit memos
RA_CUSTOMER_TRX_LINES_ALL Invoice, debit memo, chargeback, credit memo and commitment lines
RA_CUST_TRX_LINE_GL_DIST_ALL Accounting records for revenue, unearned revenue and unbilled receivables
RA_CUST_TRX_LINE_SALESREPS_ALL Sales credit assignments for transactions
AR_ADJUSTMENTS_ALL Pending and approved invoice adjustments
RA_BATCHES_ALL
AR_CASH_RECEIPTS_ALL Detailed receipt information
AR_CASH_RECEIPT_HISTORY_ALL History of actions and status changes in the life cycle of a receipt
AR_PAYMENT_SCHEDULES_ALL All transactions except adjustments and miscellaneous cash receipts
AR_RECEIVABLE_APPLICATIONS_ALL Accounting information for cash and credit memo applications
AR_TRANSACTION_HISTORY_ALL Life cycle of a transaction
HZ_CUST_ACCOUNTS Stores information about customer accounts.
HZ_CUSTOMER_PROFILES Credit information for customer accounts and customer account sites
HZ_CUST_ACCT_SITES_ALL Stores all customer account sites across all operating units
HZ_CUST_ACCT_RELATE_ALL Relationships between customer accounts
HZ_CUST_CONTACT_POINTS This table is no longer used
HZ_CUST_PROF_CLASS_AMTS Customer profile class amount limits for each currency
HZ_CUST_SITE_USES_ALL Stores business purposes assigned to customer account sites.
HZ_LOCATIONS Physical addresses
HZ_ORG_CONTACTS People as contacts for parties
HZ_ORG_CONTACT_ROLES Roles played by organization contacts
HZ_PARTIES Information about parties such as organizations, people, and groups
HZ_PARTY_SITES Links party to physical locations
HZ_PARTY_SITE_USES The way that a party uses a particular site or address
HZ_RELATIONSHIPS Relationships between entities
HZ_RELATIONSHIP_TYPES Relationship types


                                                AP- Accounts Payables
AP_ACCOUNTING_EVENTS_ALL Accounting events table
AP_AE_HEADERS_ALL Accounting entry headers table
AP_AE_LINES_ALL Accounting entry lines table
AP_BANK_ACCOUNTS_ALL Bank Account Details
AP_BANK_ACCOUNT_USES_ALL Bank Account Uses Information
AP_BANK_BRANCHES Bank Branches
AP_BATCHES_ALL Summary invoice batch information
AP_CHECKS_ALL Supplier payment data
AP_HOLDS_ALL Invoice hold information
AP_INVOICES_ALL Detailed invoice records
AP_INVOICE_LINES_ALL AP_INVOICE_LINES_ALL contains records for invoice lines entered manually, generated automatically or imported from the Open Interface.
AP_INVOICE_DISTRIBUTIONS_ALL Invoice distribution line information
AP.AP_INVOICE_PAYMENTS_ALL Invoice payment records
AP_PAYMENT_DISTRIBUTIONS_ALL Payment distribution information
AP_PAYMENT_HISTORY_ALL Maturity and reconciliation history for payments
AP_PAYMENT_SCHEDULES_ALL Scheduled payment information on invoices
AP_INTERFACE_REJECTIONS Information about data that could not be loaded by Payables Open Interface Import
AP_INVOICES_INTERFACE Information used to create an invoice using Payables Open Interface Import
AP_INVOICE_LINES_INTERFACE Information used to create one or more invoice distributions
AP_SUPPLIERS AP_SUPPLIERS stores information about your supplier level attributes.
AP_SUPPLIER_SITES_ALL AP_SUPPLIER_SITES_ALL stores information about your supplier site level attributes.
AP_SUPPLIER_CONTACTS Stores Supplier Contacts


                                              XLA - Subledger Accounting
XLA_EVENTS The XLA_EVENTS table record all information related to a specific event. This table is created as a type XLA_ARRAY_EVENT_TYPE.
XLA_TRANSACTION_ENTITIES The table XLA_ENTITIES contains information about sub-ledger document or transactions.
XLA_AE_HEADERS The XLA_AE_HEADERS table stores subledger journal entries. There is a one-to-many relationship between accounting events and journal entry headers.
XLA_AE_LINES The XLA_AE_LINES table stores the subledger journal entry lines. There is a one-to-many relationship between subledger journal entry headers and subledger journal entry lines.
XLA_DISTRIBUTION_LINKS The XLA_DISTRIBUTION_LINKS table stores the link between transactions and subledger journal entry lines.
XLA_ACCOUNTING_ERRORS The XLA_ACCOUNTING_ERRORS table stores the errors encountered during execution of the Accounting Program.
XLA_ACCTG_METHODS_B The XLA_ACCTG_METHODS_B table stores Subledger Accounting Methods (SLAM) across products. SLAMs provided by development are not chart of accounts specific. Enabled SLAMs are assigned to ledgers.
XLA_EVENT_TYPES_B The XLA_EVENT_TYPES_B table stores all event types that belong to an event class.
XLA_GL_LEDGERS This table contains ledger information used by subledger accounting.


                                                      GL- General Ledger
GL_CODE_COMBINATIONS Stores valid account combinations
GL_SETS_OF_BOOKS Stores information about the sets of books
GL_IMPORT_REFERENCES Stores individual transactions from subledgers
GL_DAILY_RATES Stores the daily conversion rates for foreign currency Transactions
GL_PERIODS Stores information about the accounting periods
GL_JE_HEADERS Stores journal entries
GL_JE_LINES Stores the journal entry lines that you enter in the Enter Journals form
GL_JE_BATCHES Stores journal entry batches
GL_BALANCES Stores actual, budget, and encumbrance balances for detail and summary accounts
GL_BUDGETS Stores Budget definitions
GL_INTERFACE Import journal entry batches
GL_BUDGET_INTERFACE Upload budget data from external sources
GL_DAILY_RATES_INTERFACE Import daily conversion rates

                
                                                    CE- Cash Management
CE_BANK_ACCOUNTS This table contains bank account information. Each bank account must be affiliated with one bank branch.
CE_BANK_ACCT_BALANCES This table stores the internal bank account balances
CE_BANK_ACCT_USES_ALL This table stores information about your bank account uses.
CE_STATEMENT_HEADERS Bank statements
CE_STATEMENT_LINES Bank statement lines
CE_STATEMENT_HEADERS_INT Open interface for bank statements
CE_STATEMENT_LINES_INTERFACE Open interface for bank statement lines
CE_TRANSACTION_CODES Bank transaction codes


                                                     FA – Fixed Assets
FA_ADDITIONS_B Descriptive information about assets
FA_ADJUSTMENTS Information used by the posting program to generate journal entry lines in the general ledger
FA_ASSET_HISTORY Historical information about asset reclassifications and unit adjustments
FA_ASSET_INVOICES Accounts payable and purchasing information for each asset
FA_BOOKS Financial information of each asset
FA_BOOK_CONTROLS Control information that affects all assets in a depreciation book
FA_CALENDAR_PERIODS Detailed calendar information
FA_CALENDAR_TYPES General calendar information
FA_CATEGORIES_B Default financial information for asset categories
FA_CATEGORY_BOOKS Default financial information for an asset category and depreciation book combination
FA_DEPRN_DETAIL Depreciation amounts charged to the depreciation expense account in each distribution line
FA_DEPRN_PERIODS Information about each depreciation period
FA_DEPRN_EVENTS Information about depreciation accounting events.
FA_DEPRN_SUMMARY Depreciation information at the asset level
FA_DISTRIBUTION_ACCOUNTS Table to store account ccids for all distributions for a book
FA_DISTRIBUTION_DEFAULTS Distribution set information
FA_DISTRIBUTION_HISTORY Employee, location, and Accounting Flexfield values assigned to each asset
FA_DISTRIBUTION_SETS Header information for distribution sets
FA_FORMULAS Depreciation rates for formula-based methods
FA_LOCATIONS Location flexfield segment value combinations
FA_MASS_ADDITIONS Information about assets that you want to automatically add to Oracle Assets from another system
FA_METHODS Depreciation method information
FA_RETIREMENTS Information about asset retirements and reinstatements


                                      HRMS- Human Resource Management System
HR_ALL_ORGANIZATION_UNITS Organization unit definitions.
HR_ALL_POSITIONS_F Position definition information.
HR_LOCATIONS_ALL Work location definitions.
PER_ADDRESSES Address information for people
PER_ALL_PEOPLE_F DateTracked table holding personal information for employees, applicants and other people.
PER_ALL_ASSIGNMENTS_F Allocated Tasks
PER_ANALYSIS_CRITERIA Flexfield combination table for the personal analysis key flexfield.
PER_ASSIGNMENT_EXTRA_INFO Extra information for an assignment.
PER_ASSIGNMENT_STATUS_TYPES Predefined and user defined assignment status types.
PER_CONTRACTS_F The details of a persons contract of employment
PER_CONTACT_RELATIONSHIPS Contacts and relationship details for dependents, beneficiaries, emergency contacts, parents etc.
PER_GRADES Grade definitions for a business group.
PER_JOBS Jobs defined for a Business Group
PER_PAY_BASES Definitions of specific salary bases
PER_PAY_PROPOSALS Salary proposals and performance review information for employee assignments
PER_PEOPLE_EXTRA_INFO Extra information for a person
PER_PERIODS_OF_PLACEMENT Periods of placement details for a non-payrolled worker
PER_PERIODS_OF_SERVICE Period of service details for an employee.
PER_PERSON_ANALYSES Special information types for a person
PER_PERSON_TYPES Person types visible to specific Business Groups.
PER_PERSON_TYPE_USAGES_F Identifies the types a person may be.
PER_PHONES PER_PHONES holds phone numbers for current and ex-employees, current and ex-applicants and employee contacts.
PER_SECURITY_PROFILES Security profile definitions to restrict user access to specific HRMS records



                                                  FND – Appication Object Library
 FND_USER Application users
FND_APPLICATION Applications registered with Oracle Application Object Library
FND_CONCURRENT_PROGRAMS Concurrent programs
FND_CONCURRENT_REQUESTS Concurrent requests information
FND_CURRENCIES Currencies enabled for use at your site
FND_DATA_GROUPS Data groups registered with Oracle Application Object Library
FND_FLEX_VALUES Valid values for flexfield segments
FND_FLEX_VALUE_HIERARCHIES Child value ranges for key flexfield segment values
FND_FLEX_VALUE_SETS Value sets used by both key and descriptive flexfields
FND_FORM Application forms registered with Oracle Application Object Library
FND_FORM_FUNCTIONS Functionality groupings
FND_ID_FLEXS Registration information about key flexfields
FND_ID_FLEX_SEGMENTS Key flexfield segments setup information and correspondences between table columns and key flexfield segments
FND_ID_FLEX_STRUCTURES Key flexfield structure information
FND_LOOKUP_TYPES Oracle Application Object Library QuickCodes
FND_LOOKUP_VALUES QuickCode values
FND_MENUS: It lists the menus that appear in the Navigate Window, as determined by the System Administrator when defining responsibilities for function security.
FND_MENUS_TL: Stores translated information about the menus in FND_MENUS.
FND_MENU_ENTRIES: Stores information about individual entries in the menus in FND_MENUS.
FND_PROFILE_OPTIONS User profile options
FND_PROFILE_OPTION_VALUES Values of user profile options defined at different profile levels
FND_REQUEST_GROUPS: Stores information about report security groups.
FND_REQUEST_SETS: Stores information about report sets.
FND_SECURITY_GROUPS: Stores information about security groups used to partition data in Service Bureau architecture.
FND_REQUEST_SET_PROGRAMS Reports within report sets
FND_REQUEST_SET_STAGES Stores request set stages
FND_RESPONSIBILITY: Stores information about responsibilities. Each row includes the name and description of the responsibility, the application it belongs to, and values that identify the main menu, and the first form that it uses.
FND_RESPONSIBILITY_TL: Stores translated information about responsibilities.
FND_RESP_FUNCTIONS: Stores security exclusion rules for function security menus. Security exclusion rules are lists of functions and menus inaccessible to a particular responsibility.
FND_APPLICATION: Stores applications registered with Oracle Application Object Library.
FND_APPLICATION_TL: Stores translated information about all the applications registered with Oracle Application Object Library.
FND_APP_SERVERS: This table will track the servers used by the E-Business Suite system.
FND_ATTACHED_DOCUMENTS: Stores information relating a document to an application entity.
FND_CONCURRENT_PROCESSES: Stores information about concurrent managers.
FND_CONCURRENT_PROCESSORS: Stores information about immediate (subroutine) concurrent program libraries.
FND_CONCURRENT_PROGRAMS: Stores information about concurrent programs. Each row includes a name and description of the concurrent program.
FND_CONCURRENT_PROGRAMS_TL: Stores translated information about concurrent programs in each of the installed languages.
FND_CONCURRENT_QUEUES: Stores information about concurrent managers.
FND_CONCURRENT_QUEUE_SIZE: Stores information about the number of requests a concurrent manager can process at once, according to its work shift.
FND_CONCURRENT_REQUESTS: Stores information about individual concurrent requests.
FND_CONCURRENT_REQUEST_CLASS: Stores information about concurrent request types.
FND_CONC_REQ_OUTPUTS: This table stores output files created by Concurrent Request.
FND_CURRENCIES: Stores information about currencies.
FND_DATABASES: It tracks the databases employed by the eBusiness suite. This table stores information about the database that is not instance specific.
FND_DATABASE_INSTANCES: Stores instance specific information. Every database has one or more instance.
FND_DESCRIPTIVE_FLEXS: Stores setup information about descriptive flex fields.
FND_DESCRIPTIVE_FLEXS_TL: Stores translated setup information about descriptive flex fields.
FND_DOCUMENTS: Stores language-independent information about a document.
FND_EXECUTABLES: Stores information about concurrent program executable.
FND_LANGUAGES: Stores information regarding languages and dialects.
FND_PROFILE_OPTIONS: Stores information about user profile options.
FND_SEQUENCES: Stores information about the registered sequences in your applications.
FND_TABLES: Stores information about the registered tables in your applications.
FND_TERRITORIES: Stores information for countries, alternatively known as territories.
FND_USER: Stores information about application users.
FND_VIEWS: Stores information about the registered views in your applications.


                                                  PA-Oracle Project Accounting 
PA_PROJECTS_ALL: Information about projects
PA_AGREEMENTS_ALL: Customer contracts that serve as the basis for work authorization
PA_BILL_RATES_ALL: Information about bill rates and markups of standard bill rate schedules
PA_BILLING_ASSIGNMENTS_ALL: Assignments of billing extensions to a project type, project, or task
PA_COST_DISTRIBUTION_LINES_ALL: Information about expenditure item cost distribution
PA_CUST_REV_DIST_LINES_ALL: Information about expenditure item revenue distribution
PA_DRAFT_INVOICE_DETAILS_ALL: Inter company invoice details for cross charged transactions
PA_DRAFT_INVOICES_ALL: Information about draft invoices generated for projects
PA_DRAFT_REVENUES_ALL: Information about draft revenue generated for projects
PA_EXPENDITURE_COST_RATES_ALL: Cost rates for non-labor expenditure types
PA_EXPENDITURE_GROUPS_ALL: Groups of pre-approved expenditures
PA_EXPENDITURE_ITEMS_ALL: The smallest units of expenditure charged to projects and tasks
PA_EXPENDITURES_ALL: Groups of expenditure items incurred by employees or organizations for an expenditure period
PA_PERIODS_ALL: Implementation-defined periods against which project performance is measured
PA_PROJECT_ASSET_LINES_ALL: Summarized project CIP costs
PA_PROJECT_ASSETS_ALL: Assets defined for capital projects
PA_EXPENDITURE_TYPES: Implementation-defined classifications of expenditures charged to projects and tasks
PA_EXPENDITURE_CATEGORIES: Implementation-defined groupings of expenditure types by type of cost
PA_CLASS_CATEGORIES: Implementation-defined categories for classifying projects
PA_CLASS_CODES: Implementation-defined values within class categories that can be used to classify projects
PA_PROJECT_CLASSES: Class codes of class categories that are used to classify projects
PA_BUDGET_VERSIONS: Versions of project budgets
PA_BUDGET_LINES: Detail lines of project and task budgets

Different Types of Table Suffixes in EBS

_ALL: Table holds all the information about different operating units. Multi-Org environment. You can also set the client info to specific operating unit to see the data specific to that operating unit only.

_TL: are tables corresponding to another table with the same name minus the _TL. These tables provide multiple language support. For each item in the table without _TL there can be many rows in the _TL table, but all with different values in the LANGUAGE column.

_B: these are the BASE tables.
They are very important and the data is stored in the table with all validations.
It is supposed that these tables will always contain the perfect format data.
If anything happens to the BASE table data, then it is a data corruption issue.

_F: these are date tracked tables, which occur in HR and Payroll. For these there are two date columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE which together with the PK identifies a row uniquely. The date intervals cannot overlap. Many think they are Secured data. Guess someone from Oracle confirms.

_V: tables are the views created on base tables

_VL: are views for multi language tables which combines the row of the base table with the corresponding row of the _TL table where the LANGUAGE = USERENV (’LANG’).

_S: are sequences, used for finding new values for the primary key of a table.

_A: are Audit Shadow Tables

_AVN and _ACN: are Audit Shadow Views

Regarding DECODE FUNCTION VS CASE and EXISTS VS IN

DECODE FUNCTION VS CASE:

Decode Function and Case Statement in Oracle: Decode Function and Case Statement are used to transform data values at retrieval time. DECODE and CASE are both analogous to the "IF THEN ELSE" conditional statement.

History of DECODE and CASE:

Before version 8.1, the DECODE was the only thing providing IF-THEN-ELSE functionality in Oracle SQL. Because DECODE can only compare discrete values (not ranges), continuous data had to be contorted into discreet values using functions like FLOOR and SIGN. In version 8.1, Oracle introduced the searched CASE statement, which allowed the use of operators like > and BETWEEN (eliminating most of the contortions) and allowing different values to be compared in different branches of the statement (eliminating most nesting). In version 9.0, Oracle introduced the simple CASE statement, that reduces some of the verbosity of the CASE statement, but reduces its power to that of DECODE.

Decode Function and Case Statement Example: 

Example with DECODE function

Say we have a column named REGION, with values of N, S, W and E. When we run SQL queries, we want to transform these values into North, South, East and West. Here is how we do this with the decode function:

select

decode (

region,

‘N’,’North’,

‘S’,’South’,

‘E’,’East’,

‘W’,’West’,

‘UNKNOWN’

)

from

customer;

Note that Oracle decode starts by specifying the column name, followed by set of matched-pairs of transformation values. At the end of the decode statement we find a default value. The default value tells decode what to display if a column values is not in the paired list.

Example with CASE statement

select

case

region

when ‘N’ then ’North’

when ‘S’ then ’South’

when ‘E’ then ’East’,

when ‘W’ then ’West’

else ‘UNKNOWN’

end

from

customer;

Difference between DECODE and CASE:

Everything DECODE can do, CASE can. There is a lot more that you can do with CASE, though, which DECODE cannot. Differences between them are listed below:

1. DECODE can work with only scaler values but CASE can work with logical oprators, predicates and searchable subqueries.

2. CASE can work as a PL/SQL construct but DECODE is used only in SQL statement.CASE can be used as parameter of a function/procedure.

3. CASE expects datatype consistency, DECODE does not.

4. CASE complies with ANSI SQL. DECODE is proprietary to Oracle.

5. CASE executes faster in the optimizer than does DECODE.

6. CASE is a statement while DECODE is a fucntion.

CASE can work with logical operators other than ‘=’

DECODE performs an equality check only. CASE is capable of other logical comparisons such as < > etc. It takes some complex coding – forcing ranges of data into discrete form – to achieve the same effect with DECODE.

An example of putting employees in grade brackets based on their salaries. This can be done elegantly with CASE.

SQL> select ename

  2       , case

  3           when sal < 1000

  4                then 'Grade I'

  5           when (sal >=1000 and sal < 2000)

  6                then 'Grade II'

  7           when (sal >= 2000 and sal < 3000)

  8                then 'Grade III'

  9           else 'Grade IV'

 10         end sal_grade

 11  from emp

 12  where rownum < 4;

ENAME      SAL_GRADE

---------- ---------

SMITH      Grade I

ALLEN      Grade II

WARD       Grade II

CASE can work with predicates and searchable subqueries

DECODE works with expressions that are scalar values only. CASE can work with predicates and subqueries in searchable form.

An example of categorizing employees based on reporting relationship, showing these two uses of CASE.

SQL> select e.ename,

  2         case

  3           -- predicate with "in"

  4           -- mark the category based on ename list

  5           when e.ename in ('KING','SMITH','WARD')

  6                then 'Top Bosses'

  7           -- searchable subquery

  8           -- identify if this emp has a reportee

  9           when exists (select 1 from emp emp1

 10                        where emp1.mgr = e.empno)

 11                then 'Managers'

 12           else

 13               'General Employees'

 14         end emp_category

 15  from emp e

 16  where rownum < 5;

ENAME      EMP_CATEGORY

---------- -----------------

SMITH      Top Bosses

ALLEN      General Employees

WARD       Top Bosses

JONES      Managers

CASE can work as a PL/SQL construct

DECODE can work as a function inside SQL only. CASE can be an efficient substitute for IF-THEN-ELSE in PL/SQL.

SQL> declare

  2    grade char(1);

  3  begin

  4    grade := 'b';

  5    case grade

  6      when 'a' then dbms_output.put_line('excellent');

  7      when 'b' then dbms_output.put_line('very good');

  8      when 'c' then dbms_output.put_line('good');

  9      when 'd' then dbms_output.put_line('fair');

 10      when 'f' then dbms_output.put_line('poor');

 11      else dbms_output.put_line('no such grade');

 12    end case;

 13  end;

 14  /

PL/SQL procedure successfully completed.

CASE can even work as a parameter to a procedure call, while DECODE cannot.

SQL> var a varchar2(5);

SQL> exec :a := 'THREE';

PL/SQL procedure successfully completed.

SQL>

SQL> create or replace procedure proc_test (i number)

  2  as

  3  begin

  4    dbms_output.put_line('output = '||i);

  5  end;

  6  /

Procedure created.

SQL> exec proc_test(decode(:a,'THREE',3,0));

BEGIN proc_test(decode(:a,'THREE',3,0)); END;

                *

ERROR at line 1:

ORA-06550: line 1, column 17:

PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL

statement only

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

SQL> exec proc_test(case :a when 'THREE' then 3 else 0 end);

output = 3

PL/SQL procedure successfully completed.

Careful! CASE handles NULL differently

Check out the different results with DECODE vs NULL.

SQL> select decode(null

  2              , null, 'NULL'

  3                    , 'NOT NULL'

  4               ) null_test

  5  from dual;

NULL

----

NULL

SQL> select case null

  2         when null

  3         then 'NULL'

  4         else 'NOT NULL'

  5         end null_test

  6  from dual;

NULL_TES

--------

NOT NULL

The “searched CASE” works as does DECODE.

SQL>  select case

  2         when null is null

  3         then 'NULL'

  4         else 'NOT NULL'

  5         end null_test

  6* from dual

SQL> /

NULL_TES

--------

NULL

CASE expects datatype consistency, DECODE does not

Compare the two examples below- DECODE gives you a result, CASE gives a datatype mismatch error.

SQL> select decode(2,1,1,

  2                 '2','2',

  3                 '3') t

  4  from dual; 

         T

----------

         2 

SQL> select case 2 when 1 then '1'

  2              when '2' then '2'

  3              else '3'

  4         end

  5  from dual;

            when '2' then '2'

                 *

ERROR at line 2:

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

CASE is ANSI SQL-compliant

CASE complies with ANSI SQL. DECODE is proprietary to Oracle.

7. The difference in readability

In very simple situations, DECODE is shorter and easier to understand than CASE.

SQL> -- An example where DECODE and CASE

SQL> -- can work equally well, and 

SQL> -- DECODE is cleaner

SQL> select ename

  2       , decode (deptno, 10, 'Accounting',

  3                         20, 'Research',

  4                         30, 'Sales',

  5                             'Unknown') as department

  6  from   emp

  7  where rownum < 4;

ENAME      DEPARTMENT

---------- ----------

SMITH      Research

ALLEN      Sales

WARD       Sales

SQL> select ename

  2       , case deptno

  3           when 10 then 'Accounting'

  4           when 20 then 'Research'

  5           when 30 then 'Sales'

  6           else         'Unknown'

  7           end as department

  8  from emp

  9  where rownum < 4;

ENAME      DEPARTMENT

---------- ----------

SMITH      Research

ALLEN      Sales

WARD       Sales

IN VS EXISTS

Sample example at which situation

IN is better than exist, and vice versa. 

Select * from T1 where x in ( select y from T2 )

is typically processed as:

select * 

  from t1, ( select distinct y from t2 ) t2

 where t1.x = t2.y;

The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to 

the original table -- typically.

As opposed to 

select * from t1 where exists ( select null from t2 where y = x )

That is processed more like:

   for x in ( select * from t1 )

   loop

      if ( exists ( select null from t2 where y = x.x )

      then 

         OUTPUT THE RECORD

      end if

   end loop

It always results in a full scan of T1 whereas the first query can make use of an index 

on T1(x).

ABOUT EXISTS:- 

Lets say the result of the subquery

    ( select y from T2 )

is "huge" and takes a long time.  But the table T1 is relatively small and executing ( 

select null from t2 where y = x.x ) is very very fast (nice index on t2(y)).  Then the 

exists will be faster as the time to full scan T1 and do the index probe into T2 could be 

less then the time to simply full scan T2 to build the subquery we need to distinct on.

Lets say the result of the subquery is small -- then IN is typicaly more appropriate.

If both the subquery and the outer table are huge -- either might work as well as the 

other -- depends on the indexes and other factors. 

API For Updating the Purchase Order Details

Sample Example Provided for Updating of Purchase Order Details through API:-

Parameters Description of below API

PO_NUM:
Purchase order number.
RELEASE_NUM:
Required if the purchase order is a release. The pass-in value must be a number.
REVISION_NUM:
Which revision of the purchase order/release is being acted upon.
LINE_NUM:
Purchase order line number to update.
SHIPMENT_NUM:
If provided, indicates the update occurs at shipment level, otherwise it's at line level
NEW_QUANTITY:
Indicates the new value of quantity ordered that the order should be updated to
NEW_PRICE:
Indicates the new value of unit price that the order should be updated to.
NEW_PROMISED_DATE:
Indicates the new value of promised date that the order should be updated to. Must be in the format of 'MM/DD/YY' or 'MM-DD-YY'
LAUNCH_APPROVALS_FLAG:
Indicates if you want to launch APPROVAL workflow after the update. Its value could be either 'Y' or 'N'. If not provided, the default value is 'N'.
SOURCE_OF_UPDATE:
Reserved for future use to record the source of the update. Purchase Order Change APIs
TRANSACTION_ID:
Used to fetch any error messages recorded in PO_INTERFACE_ERRORS table if the update process fails. If not provided, a default value will be used.
VERSION:
Version of the current API (currently 1.0)


Using the PO_CHANGE_API1_S.update_po, the following columns can be updated in a Purchase Order.

- Promised_date
- Need_by_date
- Quantity
- Unit_price

Importance of LAUNCH_APPROVALS_FLAG
=================================

- Indicates if you want to launch APPROVAL workflow after the update. Its value could be either 'Y' or 'N'. If not provided, the default value is 'N'.
- If we are passing launch_approvals_flag =>'N', the PO is remaining in the Requires Reapproval status.

Post Update PO Validation:
====================
1. Set the PO status to REQUIRES REAPPROVAL.
2. Increment revision number if the PO was in APPROVED status before the update.
3. Launch the PO Approval workflow if LAUNCH_APPROVALS_FLAG = 'Y'.
4. If the quantity was adjusted down to be equal to the total quantity received or billed, then set the appropriate closed code and roll up the closed code to line and header levels.

-- R12 - PO - Sample Script to Update PO Using po_change_api1_s
--=================================================


DECLARE

CURSOR po_date_update
IS

SELECT pha.segment1 po_number, pha.revision_num,pha.po_header_id,
pha.authorization_status, pla.po_line_id, pla.line_num, pha.org_id,
pla.unit_price, pola.line_location_id, pola.shipment_num,
pola.quantity, pola.promised_date, pola.need_by_date,
pha.closed_code
FROM po_headers_all pha, po_lines_all pla,
po_line_locations_all pola
WHERE pha.po_header_id = pla.po_header_id
AND pla.po_line_id = pola.po_line_id
AND NVL (pha.approved_flag, 'N') = 'Y'
AND NVL (pola.cancel_flag, 'N') <> 'Y'
AND NVL (pola.closed_code, 'OPEN') = 'OPEN'
AND NVL (pola.quantity_received, 0) = 0
AND NVL (pola.quantity_billed, 0) = 0
AND pha.type_lookup_code = 'STANDARD';

l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_result NUMBER;
l_api_errors po_api_errors_rec_type;
l_revision_num NUMBER;
l_promised_date DATE;
l_need_by_date DATE;
l_price po_lines_all.unit_price%TYPE;
l_quantity po_line_locations_all.quantity%TYPE;

BEGIN

SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = 'VISION';

SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = 'Purchasing Super User';

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR i IN po_date_update

LOOP

mo_global.set_policy_context ('S', i.org_id);
mo_global.init ('PO');

l_promised_date := i.promised_date + 30;
l_need_by_date := i.need_by_date + 35;
l_quantity := i.quantity + 100;
l_price := i.unit_price - 1;

DBMS_OUTPUT.put_line ('Calling po_change_api1_s.update_po To Update PO');
DBMS_OUTPUT.put_line ('===================================');
DBMS_OUTPUT.put_line ('Retrieving the Current Revision Number of PO');

select revision_num
into l_revision_num
from po_headers_all
where segment1 = i.po_number;

l_result :=
po_change_api1_s.update_po
(x_po_number => i.po_number, --Enter the PO Number
x_release_number => NULL, --Enter the Release Num
x_revision_number => l_revision_num, --Enter the Revision Number
x_line_number => i.line_num, --Enter the Line Number
x_shipment_number => i.shipment_num, --Enter the Shipment Number
new_quantity => l_quantity, --Enter the new quantity
new_price => l_price, --Enter the new price,
new_promised_date => l_promised_date, --Enter the new promised date,
new_need_by_date => l_need_by_date, --Enter the new need by date,
launch_approvals_flag => 'Y',
update_source => NULL,
VERSION => '1.0',
x_override_date => NULL,
x_api_errors => l_api_errors,
p_buyer_name => NULL,
p_secondary_quantity => NULL,
p_preferred_grade => NULL,
p_org_id => i.org_id
);

DBMS_OUTPUT.put_line (l_result);

IF (l_result = 1)
THEN
DBMS_OUTPUT.put_line('Successfully update the PO :=>');
END IF;

IF (l_result <> 1)
THEN
DBMS_OUTPUT.put_line ('Failed to update the PO Due to Following Reasons');
-- Display the errors
FOR j IN 1 .. l_api_errors.MESSAGE_TEXT.COUNT
LOOP
DBMS_OUTPUT.put_line (l_api_errors.MESSAGE_TEXT (j));
END LOOP;
END IF;

END LOOP;

END;

Simple queries on Previous/Current/Upcoming Date,Month and Year..

--First day of current week(sunday)
select TRUNC(SYSDATE, 'Day') from dual;
--First day of next week(sunday)
select TRUNC(SYSDATE+7 , 'Day') from dual;
--First day of previous week(sunday)
select TRUNC(SYSDATE-7 , 'Day') from dual;
--First day of current month
select TRUNC(SYSDATE , 'Month') from dual;
--First day of previous month
select TRUNC(TRUNC(SYSDATE , 'Month')-1 , 'Month') from dual;
--First day of next month
select TRUNC(LAST_DAY(SYSDATE)+1 , 'Month') from dual;
--First day of current year
select TRUNC(SYSDATE , 'Year') from dual;
--First day of previous year
select TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year') from dual;
--First day of next year
select ADD_MONTHS(TRUNC(SYSDATE , 'Year'),12) from dual;
-- First Day of Current quater 
select TRUNC(SYSDATE , 'Q') from dual;
--  First Day of Previous Quarter
select ADD_MONTHS(TRUNC(SYSDATE , 'Q'),-3) from dual;
--  First Day of Next Quarter
select ADD_MONTHS(TRUNC(SYSDATE , 'Q'),3) from dual;

--Last day of current week(sunday)
select TRUNC(SYSDATE, 'Day')+6 from dual;
--Last day of next week(sunday)
select TRUNC(SYSDATE+7 , 'Day')+6 from dual;
--Last day of previous week(sunday)
select TRUNC(SYSDATE-7 , 'Day')+6 from dual;
--Last day of current month
select LAST_DAY(TRUNC(SYSDATE , 'Month')) from dual;
--Last day of previous month
select LAST_DAY(TRUNC(TRUNC(SYSDATE , 'Month')-1 , 'Month')) from dual;
--Last day of next month
select LAST_DAY(TRUNC(LAST_DAY(SYSDATE)+1 , 'Month')) from dual;
--Last day of current year
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Year'),11)) from dual;
--Last day of previous year
select LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year'),11)) from dual;
--Last day of next year
select LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year'),-13)) from dual;
-- Last Day of Current quater 
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Q'),2)) from dual;
--  Last Day of Previous Quarter
select TRUNC(SYSDATE , 'Q')-1 from dual;
--  Last Day of Next Quarter
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Q'),5)) from dual;

--Fetches the First day of previous,current and Upcoming months--
SELECT  to_char(trunc(add_months(sysdate,-6),'mm') ,'DD-Mon-YYYY') FROM Dual;

--Fetches the Last day of previous,current and Upcoming months--
SELECT  to_char(add_months(last_day(sysdate),-6),'DD-Mon-YYYY') FROM Dual;


--Fetches First Day of Previous,Current,Upcoming Months Query--
SELECT
  TO_CHAR(TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-3))+1),'DD-MON-YY') AS "One Way"
, TO_CHAR(TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-3)),'DD-MON-YY') AS "Another Way"
FROM  dual;

--fetches First Day of Previous,Current,Upcoming Dateandyear Query--
select  trunc(add_months(sysdate,-2),'mm') as first_day from dual;

--fetches Last Day of Previous,Current,Upcoming Dateandyear Query--
SELECT add_months(last_day(sysdate),-1) FROM Dual;

--To get current month--
select to_char(sysdate,'MM') from dual;

--Fetches Previous,Current,Upcoming Months Query--
SELECT  to_char(add_months(last_day(sysdate),-1),'MM') FROM Dual;

--Fetching Current,Previous and upcoming Year--
select TO_CHAR(add_months( to_date(SYSDATE), -1),'DD-Mon-YYYY')   from dual;