Oracle Financials important tables

Oracle General Ledger

Gl_code_combinations

 Setup > Accounts > Combinations

This table stores the valid account combinations.

The value in your chart of account segments are stored in the columns segment1 to segment30 depending on your application configuration.

For example, say your chart of accounts is
Company – Cost Centre – Account
then segment1 = company, segment 2 = cost centre and segment3 = account.
However, this sequencing of segments is not guaranteed therefore, its best to check your configuration.

Another important column is the account_type which signifies your account is an Asset, Liability, Revenue, Expense or Owners Equity account.

Gl_je_batches

 Journals > Enter

This table stores the journal entry batches. Journal entries are batched in General Ledger.

Some columns of interest includes :

• Name
• Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
• Status
• Default_period_name
• Posted_date
• Posting_run_id

Gl_je_headers

 Journals > Enter

This table stores the journal entry headers. There is always two journal lines for each journal header.

Some columns of interest includes :

• Je_category
• Period_name
• Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
• Posted_flag
• Je_source
• Name
• Status

Gl_je_lines

 Journals > Enter

This table stores the journal entry lines.

The entered_dr and entered_cr stores the amount in the entered currency whereas the accounted_dr and accounted_cr stores the amount in the functional currency.

Other columns of interest includes :

• Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
• Period_name
• Status
• Description
• Reference_1..reference10 (these columns links back to your Subledgers)

For example, for Purchasing transactions

Reference_1 = ‘PO’
Reference_2 = po_headers_all.po_header_id
Reference_3 = po_distributions_all.po_distribution_id
Reference_4 = po_headers_all.segment? (this is the purchase order number)

Oracle Payables

Ap_invoices_all

 Invoices > Entry > Invoices

This table stores all the invoices you enter. For an invoice to be approved, the total invoice amount must be stored in ap_invoice_distributions_all and ap_payment_schedules_all.

Some columns of interest includes :

• Invoice_num
• Invoice_date
• Amount_paid
• Invoice_currency_code
• Invoice_type_lookup_code
• Payment_status_flag

Ap_invoice_distributions_all

 Invoices > Entry > Invoices

This table stores the accounting information for the invoice you have entered. There is one row for each invoice disribution, that is this table corresponds to the Distributions window.

Some columns of interest includes :

• Line_type_lookup_code
• Dist_code_combination_id (credit entry)
• Accts_pay_code_combination_id (debit_entry)
• Base_amount (in functional currency)

Ap_checks_all

 Payments > Entry > Payments

This table stores payments to suppliers.

Some columns of interest includes :

• Amount (in functional currency)
• Check_date
• Bank_account_name
• Check_number
• Payment_method_lookup_code
• Payment_type_flag

Ap_invoice_payments_all

 Payments > Entry > Payments

This table stores invoice payments to suppliers. This table is updated when you confirm an automatic payment batch, enter a manual payment or process a Quick Payment. Void payments are represented as a negative of the original payment line.

Some columns of interest includes :

• Accounting_date
• Period_name
• Amount
• Payment_num

Ap_payment_distributions_all

 Payments > Entry > Payments

This table stores accounting information for payments. There is at least one CASH payment distribution for each invoice payment. Additional rows may include DISCOUNT, GAIN and LOSS distributions where appropriate.

Some columns of interest includes :

• Line_type_lookup_code (CASH/DISCOUNT/GAIN/LOSS)
• Base_amount

Oracle Purchasing

Po_vendors

 Supply Base > Suppliers

This table stores supplier information.

Some columns of interest includes :

• Segment1 (supplier number)
• Vendor_name
• Terms_id
• Vendor_type
• Ship_to_location (link to hr_locations for location information)
• Bill_to_location (link to hr_locations for location information)

Po_vendor_sites_all

 Supply Base > Suppliers

This table stores supplier sites information.

Some columns of interest includes :

• Pay_site_flag
• Purchasing_site_flag
• Address_line1 to address_line3
• City
• State
• Area_code
• Zip

Po_headers_all

 Purchase Orders > Purchase Orders

This table stores the seven types of purchasing documents such as Purchase Order and Blanket Agreement.

Segment1 is the document number (i.e. purchase order number)

Some columns of interest includes :

• Agent_id (link to per_people_f for the buyer)
• Type_lookup_code

Po_lines_all

 Purchase Orders > Purchase Orders

This table stores purchasing document lines.

Some columns of interest includes :

• Line_num
• Item_description
• Unit_price
• Unit_meas_lookup_code (unit of measure)
• Quantity
• Item_id (link to mtl_system_items for the item number)
• Category_id (link to mtl_categories for the category name)

Po_line_locations_all

 Purchase Orders > Purchase Orders

This table stores purchase order shipment schedules and blanket agreement price breaks. A purchase order is closed when QUANTITY is equal to QUANTITY_RECEIVED.

Some columns of interest includes :

• Quantity
• Quantity_accepted
• Quantity_received
• Quantity_cancelled
• Need_by_date
• Ship_to_organization_id (link to org_organization_definitions for the organization code)

Po_distributions_all

 Purchase Orders > Purchase Orders

This table stores the accounting information on a purchase order shipment. This table is used for Standard and Planned Purchase Orders and Planned and Blanket Purchase Order Release.

Some columns of interest includes :

• Quantity_ordered
• Quantity_billed
• Amount_billed
• Quantity_delivered
• Quantity_cancelled
• Destination_organization_id (link to org_organization_definitions for the organization code)
• Destination_subinventory

Rcv_shipment_headers

 Receiving > Receipts

This table stores the receiving information. The three receipt sources are Supplier, Inventory and Internal Order. There is one receipt header per receipt source.

Some columns of interest includes :

• Receipt_num
• Shipment_num
• Receipt_source_code
• Shipped_date
• Ship_to_org_id

Rcv_shipment_lines

 Receiving > Receipts

This table stores information about items that have been shipped and/or received from a receipt source.

Some columns of interest includes :

• Line_num
• Quantity_shipped
• Unit_of_measure
• Item_id (link to mtl_system_items for item number)
• To_organization_id (link to org_organization_definitions for organization code)
• To_subinventory
• Shipment_line_status_code (EXPECTED, FULLY RECEIVED, PARTIALLY RECEIVED)
• Quantity_received
• Quantity_shipped

Oracle Inventory

Org_organization_definitions

 Setup > Organizations > Parameters

This view contains basic information on all inventory organisations.

Some columns of interest includes :

• Organization_code
• Organization_name
• Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
• Inventory_enabled_flag

Mtl_secondary_inventories

 Setup > Organizations > Subinventories

This table stores all subinventory information for an inventory organisation.

Some columns of interest includes :

• Secondary_inventory_name
• Description

Mtl_material_transactions

 Transactions > Material Transactions (Inquiry)

This table stores all inventory transactions including cost updates.

Some columns of interest includes :

• Transaction_quantity
• Transaction_type_id
• Transaction_source_type_id
• Transaction_source_name

Mtl_transaction_accounts

 Transactions > Material Distributions (Inquiry)

This table stores the inventory accounting information. There are two rows in this table for each transaction in mtl_material_transactions.

Some columns of interest includes :

• Transaction_date
• Gl_batch_id
• Accounting_line_type
• Base_transaction_value

Mtl_system_items

 Items > Master Items or Items > Organization Items

This table stores the item definition. An item must exist in an inventory organisation.

Your item number is stored in the columns segment1 to segment20 depending on your application configuration. If you have configured your items to have to segments then you may be using segment1 and segment2

Some columns of interest includes :

• Segment1 to segment20
• Description
• Invetory_item_flag
• Purchasing_item_flag
• Inventory_asset_flag
• Stock_enabled_flag
• Invoiceable_item_flag
• Shippable_item_flag
• So_transaction_flag
• Mtl_transactions_enabled_flag
• Primary_unit_of_measure

Mtl_onhand_quantities

 On-hand, Availability > On-hand Quantities

This table stores quantity on hand in a location for each item.

Some columns of interest includes :

• Date_received
• Transaction_quantity
• Subinventory_code

Cst_item_costs

 Costs > Item Costs

This table stores the item cost information. Note that there can be multiple costs per item and the actual cost is where the cost type is Frozen.

Some columns of interest includes :

• Cost_type_id (link to cst_cost_types)
• Item_cost

Oracle Receivables

Ra_customers

 Customers > Standard

This table stores customer information.

Some columns of interest includes :

• Customer_name
• Customer_number
• Status
• Customer_prospect_code
• Customer_type
• Orig_system_reference (for imported customers from an external source)

Ra_addresses_all

 Customers > Standard

This table stores customer address information and your remit-to addresses.

Some columns of interest includes :

• Status
• Orig_system_reference (for imported customer addresses from an external source)
• Address1 to address4
• City
• State
• Postal_code

Ra_site_uses_all

 Customers > Standard

This table stores the customer’s site and site purpose. You must have one row for each address. A customer must have one bill to address for Receivables. A customer must have one ship to address and one bill to address for Order Entry.

Some columns of interest includes :

• Site_use_code (BILL_TO, SHIP_TO, STMTS, DUN/LEGAL)
• Primary_flag
• Status
• Location

Ra_customer_trx_all

 Transactions > Transactions

This table stores invoice, debit memo, chargeback, commitment and credit memo header information.

Some columns of interest includes :

• Cust_trx_type_id (link to ra_cust_trx_types_all)
• Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
• Terms_id (link to ra_terms)
• Trx_number (invoice number)
• Trx_date (invoice date)

Ra_customer_trx_lines_all

 Transactions > Transactions

This table stores the invoice, debit memo, chargeback, commitment and credit memo line information.

Some columns of interest includes :

• Line_number
• Description
• Quantity_ordered
• Quantity_credited
• Quantity_invoiced
• Unit_standard_price
• Unit_selling_price
• Line_type
• Extended_amount
• Revenue_amount

Ra_cust_trx_line_gl_dist_all

 Transactions > Transactions

This table stores the accounting information for revenue, unearned revenue, unbilled receivables, receivables, charges, freight and tax for each invoice or credit memo line.

Some columns of interest includes :

• Amount_gl_date
• Gl_posted_date
• Account_class (CHARGES/FREIGHT/TAX/REC/REV/UNBILL/UNEARN)
• Acctd_amount (functional currency)

Ar_cash_receipts

 Receipts > Receipts

This table stores the payment information.

Some columns of interest includes :

• Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
• Status (APP, UNAPP, UNID, NSF, STOP, REV)
• Type (CASH, MISC)
• Receipt_number
• Amount
• Currency_code
• Pay_from_customer
• Receipt_date

Ar_receivable_applications

 Receipts > Receipts

This table stores accounting entries for cash and credit memo applications.

Some columns of interest includes :

• Amount_applied
• Line_applied
• Tax_applied
• Application_type
• Display
• Gl_date
• Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)

Ar_payment_schedules

 Transactions > Transactions and Receipts > Receipts

This table stores all transactions except adjustments and miscellaneous cash receipts. This table is updated when a transaction occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, or receipt.

Some columns of interest includes :

• Amount_due_original
• Status
• Class (DEP, DM, PMT, GUAR, CM, CB, INV)
• Due_date
• Amount_due_remaining
• Invoice_currency_code
• Amount_applied
• Anmount_credited
• Amount_adjusted

HRMS: Oracle Fast Formula

Oracle Fast Formula (FF) is a simple way to write formulas using English words & basic mathematical functions. You can use information from your database in formulas without learning the database structure or a programming language. Oracle stores fast formulas as a database packages

 
Common tables
SELECT *
FROM all_objects
WHERE object_type = 'TABLE' AND object_name LIKE 'FF%'

FF_FUNCTIONS
FF_FUNCTION_PARAMETERS
FF_FORMULAS_F
FF_FORMULA_TYPES
FF_DATABASE_ITEMS
FF_GLOBALS_F


SELECT *
FROM all_objects
WHERE object_type like  'Pack%' 
AND (object_name LIKE 'FFP_%' or object_name LIKE 'FFW_%')


Uses of Oracle FastFormula
In HRMS, Oracle FastFormula is used for validation, to perform calculations, and to specify rules. Here are some examples.

In Payroll, you use formulas to:
• Validate element inputs
• Calculate element pay values and run results during payroll processing
• Specify the rules for skipping an element during payroll processing
• Perform legislative checks during a payroll run

In Compensation and Benefits Management, you use formulas to:
• Specify the rules for Paid Time Off accrual plans, such as how much time is accrued and over what period, when new starters are eligible to begin accruing time, and how much time can be carried over to the next accrual term.
• Define custom calculations for benefits administration.
• Calculate the duration of an absence given the start and end dates and times.
• Create rules for benefits administration such as eligibility determination.

In People Management, fast formulas are used to:
• Check that element entry values are valid for an assignment
• Specify the criteria for including an assignment in an assignment set and to edit assignment sets
• Configure the people management templates in a number of ways such as supplying additional information to be available from fields on the template and validating field entries.
• Define collective agreements
• Generate custom global person number sequences for employees, applicants, and contingent workers.

Components of Formulas
Formulas are made up of a number of different components. These can include assignment statements, different types of input including database items, functions, nested expressions, and conditions.

1) Assignment and Return Statements.
To start with a simple example, suppose you wanted to calculate the pay value for the element Wage by multiplying the number of hours an employee works each week by hourly rate. You could write this formula:
wage = hours_worked * hourly_rate
RETURN wage
The first line is an Assignment statement that simply assigns a value to the element Wage. The second line is a Return statement that passes back the Wage value to the payroll run.

2) Constants and Variables.
In this example, the Wage value is calculated, but it could be a constant value, such as: wage = 200. To calculate the Wage value, Oracle FastFormula needs to get values for the variables hours_worked and hourly_rate.

3) Data Types.
Both variables and constants can be one of three data types:
• Numeric
• Text
• Date

4) Types of Input.
Values for the variables hours_worked and hourly_rate can be populated using three ways:
• Receiving them as input when the formula is called.
• Finding the values in the database from database items.
• Using global values, which you enter in the Globals window.

To use a database item or global value in your formula, you simply refer to it by name. You can browse through lists of database items in the Formulas window. To use a value passed to the formula at run time, you must write an Inputs statement.

Input Statements
In our Wage example, suppose that hours_worked is an input value to the element Wage. To pass the element input values to the formula during a payroll run, you define
an Inputs statement, as follows:
INPUTS ARE hours_worked
wage = hours_worked * hourly_rate
RETURN wage
The name you use in the Inputs statement must be the same as the name of the element input value, and multiple words must be joined by underscores. In this example, the input value hours_worked is numeric. If the input value is not numeric, you must tell Oracle FastFormula whether it is text or date. For example:
INPUTS ARE start_date (date)

Database Items
Suppose that hourly_rate is a standard rate taken from the Grade Rates table. This is an example of a database item. A database item has a label, or special piece of code, telling Oracle FastFormula the path to take to access the data. These items include both information unique to your enterprise, which you hold in flexfield segments & standard information such as assignment numbers and grades etc. In the Formulas window, you pick database items from a list. There are two types of DB Items: Static and Dynamic.

Static database items are shipped with the system and you cannot modify them.
Dynamic database items are created by Oracle HRMS processes whenever you define new elements or other related entities.

Element Database Items:
When you define a new element, Oracle HRMS runs a process to create a number of related database items for it. To ensure easy recognition of these items, the process adds the element name to each one. It also creates further database items for each pay and input value you use .

Global Variables
Use global values to store information that does not change often, but you refer to frequently, such as Company Name, or company-wide percentages used to calculate certain types of bonus. You define the global value and change its value using the Globals window.

Local Variables
Local variables exist in one formula only. You can change the value of a local variable by assigning it a value in an Assignment statement. In the Wage example, the variable wage itself is a local variable. It receives a value within the formula by the Assignment statement:
wage = hours_worked * hourly_rate

Functions
Oracle FastFormula provides functions that manipulate data in different ways.
GREATEST,INITCAP,LEAST LENTH,INSTR,LOWER,RTRIM,LTRIM,ABS,REPLACE SUBSTRING,TRANSLATE, CALCULATE_HOURS_WORKED,FLOOR,ROUND,TRUNC, ETC( For full list refer the Fast Formula Guide).
There are special functions that convert variables from:
• numbers to text (TO_TEXT)
• dates to text (TO_TEXT)
• text to date (TO_DATE)
• text to number (TO_NUM)

Nested Expressions
Each function or calculation is one expression, and you can nest expressions to create more complex calculations. You must use brackets to make clear to Oracle FastFormula the order in which the calculations are performed. For example:
ANNUAL_BONUS = trunc(((((salary_amount/100)*
bonus_percentage)/183)*(days_between(end_period_date,
start_date) + 1)), 2)
Oracle FastFormula begins calculating inside the brackets and from left to right, in the
following steps:

1. salary_amount/100
2. 1. * bonus_percentage
3. 2. / 183
4. days_between (end_period_date, start_date)
5. 4. + 1
6. 3. * 5.
7. TRUNC(6.,2)

Incorporating Conditions
In Wage element example, only one value is returned, and it is calculated in the same way for every assignment. However you may need to perform different calculations depending on the particular group of employee assignments, or the time of the year, or some other factors. You can do this by incorporating conditions in your formula.

Simple Conditions
For example:
IF age < training_allowance =" 30" training_allowance =" 0">

IF (DAYS_BETWEEN(end_period_date, start_date)+1) >= threshold_value WAS DEFAULTED
There is a special type of condition called WAS DEFAULTED. Use this to test whether a default value has been placed in an input value or database item. Default values are placed using the Default statement. For example:
DEFAULT FOR hourly_rate IS 3.00
X = hours_worked * hourly_rate
IF hourly_rate WAS DEFAULTED
THEN
MESG = ‘Warning: hourly rate defaulted’
In this example, if the database item hourly_rate is empty (NULL), the formula uses the default value of 3.00 and issues a warning message.

Combined Conditions
You can combine conditions using the logical operators AND, OR, NOT.

Commenting Formula
You must include comments in your formulas to make them easier to read and understand what the formula does. For example, you can name the formula as:

/* Formula: Calculates Duration of Absence */
Caution: Do not put a comment within a comment. This causes Oracle FastFormula to return a syntax error.

Alias Statements
Sometimes DB Item names are too long to conveniently use in a formula. Set up an alternative shorter name to use within the formula. For example: ALIAS as_qualifying_length_of_service AS as_los In the rest of the formula, you can use the alias (in this example, as_los) as if it were
the actual variable (as_qualifying_length_of_service).

Default Statements
It is used to set a default value for an input value or a database item. The formula uses the default value if the database item is empty or no input value is provided when you run the formula. For example:
DEFAULT FOR hourly_rate IS 3.00
X = hours_worked * hourly_rate
IF hourly_rate WAS DEFAULTED
THEN
MESG = ’Warning: hourly rate defaulted’
This example sets a default of 3.00 for the database item hourly_rate. If hourly_rate is empty (NULL) in the database, the formula uses the default value of 3.00. The formula uses the ’WAS DEFAULTED’ test to detect when a default value is used, in which case it issues a warning message.

Important: You must use the Default statement for database items that can be empty. The Database Items window includes a check box labeled Default Required. This check box is checked for database items that can be empty. The Database Items window appears when you choose the Show Items button on the Formulas window.
How to Compile FastFormula?
After creating or editing a formula in the Formula window, choose the Verify button to compile it.
If you need to compile many formulas at the same time, you can run the concurrent program “Bulk Compile Formulas process” in the Submit Requests window.

Formula Errors
There are two types of error that can occur when using Oracle FastFormula:
• Verify-time errors occur in the Formulas window when you run the formula verification. An error message explains the nature of the error. Common verify-time errors are syntax errors resulting from typing mistakes.

• Run-time errors occur when a problem arises while a formula is running. The usual cause is a data problem, either in the formula or in the application database. The basic Oracle FastFormula errors that can occur at run-time are:

• Uninitialized Variables: An uninitialized local variable is one that has no value when the formula runs. The term ’uninitialized’ means you have not assigned any value to the variable before you try to use it. This causes an error in all statements except the Return statement. For example:
IF (tax_band < tax =" salary"> 2000)
THEN tax = salary / 10
IF tax > 1000
THEN...
This formula fails with an ’Uninitialized variable’ message (for the variable tax) if the tax band is set to 2000.

• Divide by Zero: Dividing a number by zero is an operation that provides no logical result. If this situation ever arises, Oracle FastFormula passes a code back to the application indicating an error (the application then takes the appropriate action).Always check for the possibility of a divide by zero error if there is any chance it could occur. For example, the formula:
x = salary/contribution_proportion
produces an error if the contribution proportion is set to zero. In this formula, check
for the divide by zero condition as follows:
IF contribution_proportion = 0
THEN

(

message = 'The contribution proportion is not valid.'

RETURN message

)
ELSE x = salary/contribution_proportion

• No Data Found: A database item supposed to be in the database was not found. This
represents an error in the application data.

• Too Many Rows: The database item definition within the application caused more than one value to be fetched from the database.

• Value Exceeded Allowable Range: This can occur for a variety of reasons such as:

• exceeding the maximum allowable length of a string (which is 240 characters)
• rounding up a number to an excessive number of places, for example, round (1,100)
• using an invalid date, for example, 39-DEC-1990.

• Invalid Number: This occurs only when a database item contains an item that does
not make sense as a number.

• Null Data Found: A database item was found to have a null value when it should have had a non-null value. Use the Default statement for database items marked as Default Required in the Database Items window.

Also you can call PL/SQL procedure from the Fast formula.

Unix: Rename multiple files script

cd /Oracle/archives
fList="`ls`"

for fNm in $fList do
     mv  $fNm $fNm.dat
     echo "file $fNm renamed to $fNm ."
done

Oracle: Check database charcterset:

To check the current characterset use the following queries:

SELECT *
FROM   NLS_DATABASE_PARAMETERS
where  parameter like 'NLS_%';

SELECT value$
FROM sys.props$
WHERE name like  'NLS_%';

How to Find Sessions Generating Lots of Redo or Archive logs

To find sessions generating lots of redo, you can use either of the following methods.
Both methods examine the amount of undo generated. When a transaction generates undo,
it will automatically generate redo as well.



The methods are:

1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which
indicates how much blocks have been changed by the session. High values
indicate a session generating lots of redo. The query you can use is:
    SELECT s.sid, s.serial#, s.username, s.program, i.block_changes
    FROM v$session s, v$sess_io i
    WHERE s.sid = i.sid 5
    ORDER BY 5 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence
of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

2) Query V$TRANSACTION. This view contains information about the amount of
undo blocks and undo records accessed by the transaction (as found
in the USED_UBLK and USED_UREC columns). The query you can use is:

    SELECT s.sid, s.serial#, s.username, s.program, 2 t.used_ublk, t.used_urec
    FROM v$session s, v$transaction t
    WHERE s.taddr = t.addr 5 ORDER BY 5 desc, desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence
of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by
the session. You use the first query when you need to check for programs
generating lots of redo when these programs activate more than one
transaction. The latter query can be used to find out which particular
transactions are generating redo.

Oracle Apps: Find the scheduled concurrent requests

SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, cr.resubmit_interval,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
),
'PERIODICALLY', 'EVERY '
|| cr.resubmit_interval
|| ' '
|| cr.resubmit_interval_unit_code
|| ' FROM '
|| cr.resubmit_interval_type_code
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcr.class_info
) schedule,
fu.user_name, requested_start_date
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id;

Oracle workflow notification current log file path from sql

SELECT fcp.logfile_name
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup
WHERE concurrent_queue_name in ('WFMLRSVC')
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id
AND flkup.lookup_code=fcp.process_status_code
AND lookup_type ='CP_PROCESS_STATUS_CODE'AND meaning='Active';

Steps to Configure a Printer for Oracle Apps

 

 ###############

Host file entry for the Printer    Page 1       12/07/2011

###############

/etc/hosts entry

 Ex-10.5.76.5      lp42

###############

lpstat output --> Define Printer On the OS with a printer queue

###############

bash-2.04$ lpstat

Queue   Dev   Status    Job Files              User         PP %   Blks  Cp Rnk

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

lp42    @lp42 READY

Active connection from  10.5.76.12 lpd Service

Print Queue Name --> lp42

###################

Test OS Level Printing

###################

Make a test file  to print

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

# cat test

Hi From Wipro at 10:13 AM

Fire the Print

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

# lp -d lp42 test

Job number is: 1

Check if the printout has come

www.Fullinterview.com	CONFIGURING THE PRINTER IN 11i

######################################

Define Printer on the 11i Application

######################################

Navigate -> Install - Printer – Types

Navigate ->  Install - Printer – Register

	CONFIGURING THE PRINTER IN 11i

Test using a Concurrent program “Active Users” and choose the

new printer  à”lp42”

	CONFIGURING THE PRINTER IN 11i

Check if the printout has come

Oracle Apps: Apache Overview 11i and R12

Apache Overview 11i and R12

what is web server? A Web Server is a computer program that is responsible for accepting HTTP requests from clients, which are known as web browsers, and serving them HTTP responses along with optional data contents, which usually are web pages such as HTML documents or linked objects (images, etc.).

Basic process looks very simple. Your browser connects to the Web Server and requests a page and the server sends back the requested page.

Example:

When u type www.oracle.com .The Browser Breaks the URL into three Parts:

The protocol ("http")
The server name ("www.oracle.com")
The file name defined under Directory Index ("index.html")

The browser communicates with DNS to translate the server name www.oracle.com into an IP Address, which it uses to connect to the server machine.

The browser then forms a connection to the server at that IP address.

Using the HTTP protocol, the browser then sends a GET request to the server, asking for the file "http://www.oracle.com/index.html."

The server then sends the HTML text for the Web page to the browser.
The browser read the HTML tags and then formats the page onto your screen

What is Apache Web Server then?

For Oracle Applications, the Web Server is the Oracle Application Server, which is based on Apache technology.

Oracle people has Customized Apache by adding extra features/component/plugins like mod_plsql, mod_jserv, mod_osso … in to Apache Web server and released it as Oracle-Apache Server.

The Oracle Application server is also called as “iAS”

Oracle Apache Server, at startup, starts the parent process. This process loads the entire configuration and associated modules and spawns a preconfigured number of child processes. Apache Server, thus, actually runs several copies of itself to handle multiple connections simultaneously. The parent process never listens to any HTTP request. Its sole job is to ensure that the child processes are alive or to manage the child processes in conjunction with load on the system. Each child process at a given time deals with a single HTTP request

Apache is one of the most common Web Server Software. Apache is a freeware and it is highly customizable. In this context, Oracle Apache Server uses several standards as well as Oracle’s proprietary modules that extend the functionality of Apache Server. Oracle Apache server provides key infrastructure for serving the Internet’s HTTP protocol.

Main Components:

There are two main components. HTTP Listener (Web Listener) and Modules

Http Listener is in charge of receiving incoming http requests and servicing these requests by sending them to appropriate processing component. Modules includes some standard Apache modules as well as Oracle proprietary modules & Some add-on modules. Modules extend basic functionality of web server. Modules such as:

Mod_security : protects web server from external attacks
Mod_perl : routes requests to PERL interpreter
Mod_plsql : routes requests to pl/sql engine
Mod_ossl : supports SSL

OHS Components

Apache
mod_plsql
mod_jserv
oproc

File System:

Its location on File System: As you know there are three ORACLE_HOME in Apps 8.0.6, iAS, 9.2.0.
Web Server is under iAS ----- iAS stands for Internet Application server ------ The environment variable to go to this location in apps user is (cd $) IAS_ORACLE_HOME.

Version:-

To find out the version of Apache and iAS, go to this location and type this if environment file is not sourced.
//ora/iAS/Apache/Apache/bin] $ httpd -v

Configuration Files:

httpd.conf - is a key configuration file for Oracle HTTP Server.
httpds.conf - this is configuration file if we use SSL (Secure Socket layer)

How Oracle Apache Server Works?

Apache is a program that runs under a suitable multitasking operating system such as Windows, Netware, OS2 and various unix flavours. The Apache binary is called “httpd” under unix and normally runs in background. On unix OnDemand environments you can see “httpd” executable under $APACHE_TOP/Apache/bin.

Starting and Stopping Apache:

You can start or stop httpd processes using httpd command line however, File “apachectl’ , which is a shell script calls this “httpd” executable and you can thus start or stop Apache program using apachectl start or stop arguments. Generally, in EBSO application, we use “adapcctl.sh” script from $SCRIPT_TOP location. This file reads environment variables and again calls “apachectl” file to start, stop or display status of Apache processes. Thus, finally it is the httpd executable which is being started.

When Apache Web server up it uses the below configuration Files.

adstrtall.sh -> adapcctl.sh ->apachectl-> httpd.conf (which in turns apache web server i.e http server will up)

When httpd executable first starts, this file is processed. Thus httpd.conf is the main configuration file.

httpd.conf -> jserv.conf -> jserv properties -> zone properties
httpd.conf -> oracle_apache.conf -> ojsp.conf -> plsql.conf -> apps.conf
httpd.conf -> oprocmgr.conf



LOCATIONS OF ALL CONFIGURATION FILES RELATED TO APACHE IN 11i :-

adstrtall.sh, adapcctl.sh -. COMMONTOP/admin/scripts/sid/
(adapcctl -> where ad -> application's DBA apc -- Apache ctl -- control)
apachectl -> APACHE TOP/apache/bin/
httpd.conf -> Apache top/apache/conf/
jserv.conf -> Apache top/jserv/etc/
jserv.properties -> Apache top/jserv/etc
zone.properties -> Apache top/jserv/etc/
oracle_apache.conf -> Apache top/apache/conf/
plsql.conf -> Apache/modplsql/cfg
oprocmgr.conf -> Apache top/apache/conf/
wdbsvr.app -> Apache top/modplsql/cfg/
( in this file the apps password is hardcoded).


IMPORTANT FILES/DIRECTIORIES IN IAS DIRECTORY:

We have to keep some concentration on these directories in IAS_ORACLE_HOME SID_Hostname.env, Apache, and network.

Env file is to set environment variable to iAS_ORACLE_HOME (During iAS patching)

Apache directory: -

Apache is main directory where you’re most web server configuration Sit. The
Important Files / dir under this directory are
1) Apache
2) Jserv and
3) modplsql

Mod_jserv related stuff goes in Jserv directory (it caters requesters like Self Service, Servlets, jsp pages), see the below screen

Mod_plsql related stuff goes in modplsql directory (it caters requesters like anything after /pls/ i.e., executing packages & procedures in DB).

Under iAS/Apache/Apache important directory/files is
bin (apachectl, httpd),
conf (httpd.conf, httpd_pls.conf, oracle_apache.conf),
logs (access_log, access_log_pls, error_log, error_log_pls, httpd.pid, httpd_pls.pid)

Under iAS/Apache/Jserv files you want to know are
etc (all configuration files),
logs (jvm, mod_jserv.log)

Under iAS/Apache/modplsql its
cache (cookie, plsql),
cfg (wdbsvr.app, *.conf)




MOD_JSERV

Mod_jserv related stuff goes in Jserv directory (it caters requester like Self Service, Servlets, jsp pages), see the below screen






mod_jserv is controlled by directives in jserv.conf.

• ApJServManual, ApJServLogFile
• ApJServLogLevel
• ApJServGroup
• Ex: ApJServGroup OACoreGroup 4 1 //product/iAS/Apache/Jserv/etc/jserv.properties

Jserv is controled by jserv.properties.

• Port,
• log=[true,false]
• Heapsize ( -Xmx, -Xms )
• JTFDBCFILE
• LONG_RUNNING_JVM
• DCACHEMODE

Java Caching Framework




MOD_PLSQL:

Mod_plsql(PHP) related stuff goes in modplsql directory (it caters requesters like anything after /pls/ i.e., executing packages & procedures in DB).

The following scenario provides overviews of what steps occur when a server receives a plsql related client request







Main Configurations Files and Directives (Apache PL/SQL (PHP)



1)The Oracle HTTP Server receives a PL/SQL Server Page request from a client browser.
2)The Oracle HTTP Server routes the request to mod_plsql.
3)The request is forwarded by mod_plsql to the Oracle Database. By using the configuration information stored in your Database Access Descriptor DAD(wbdsvr.app), mod_plsql connects to the database.
4)mod_plsql prepares the call parameters, and invokes the PL/SQL procedure in the application.
5)The PL/SQL procedure generates an HTML page using data and the PL/SQL Web Toolkit accessed from the database.
6)The response is returned to mod_plsql.
7)The Oracle HTTP Server sends the response to the client browser

EXPLANATION OF SOME IMPORTANT CONFIGURATION FILES in 11i:-


httpd.conf ->This is main Apache configuration file. From this configuration file it identify port definitions, memory settings, loggin levels, log file locations and other configuration options and files.

Port=web_cache_port: Specifies the Oracle Application Server Web Cache listening ports
Listen=Oracle_HTTP_Server_port: Specifies the HTTP and HTTPS ports obtained by Oracle HTTP Server.

jserv.conf -> Oracle HTTP Server includes a Java Virtual Machine (JVM), which is called
JServ. jserv.conf and jserv.properties are the key configuration files for Jserv

All our Self Service servlets requests are via mod_jserv like discoverer view, xml services or OAM login (So you know now where to look if issue happens in these services) If your Form Server is in servlet Mode then Core Applications are also accessed via Web Server (Jserv Component)
This file calls few properties files like jserv.properties, zone properties, viewer4i.properties, forms.properties, xmlsvcs.properties.
This file calls other configuration files like plsql.conf, immeting.conf, apps.conf.

oprocmgr.conf ->(in Apache/conf) This Oracle module provides process management and load balancing services to JServ processes
This module starts, stops, and detects death of processes (starting new processes to replace them), and provides load balancing services to the processes

oracle_apache.conf -> This configuration file is used to configure Oracle built modules supplied with default Apache like mod_pls, mod_ossl, oem, imeeting. These files are used for mod_pls configuration.

Plsql.conf ->This file is used to configure pls (Plsql). This file defines to forward all requests like /pls/ to dedicated apache listener.
mod_plsql is an Oracle HTTP Server plug-in that communicates with the database. It maps browser requests into database stored procedure calls over a SQL*Net connection. It is often indicated by a /pls/ virtual path.
wdbsvr.app ->Which in my views is named so after Web Database Server for Applications. This file contains your dad (database Access Descriptor) information like database connection description & apps user name & password. If you are changing apps password you use utility FNDCPASS and after changing password you have to manually change apps password in this file.

Configuration Files Under R12.

R12 File System INST_TOP in R12

Remember there are no jserv.properties or jserv.conf or zone.properties in R12 (new techstack), Jserv is replaced by OACORE!

The following will cover the configuration files that will fall under INST TOP directory structure.

All the configuration Files are placed under $ORA_CONFIG_HOME

If you go inside this directory you will see 10.1.2 and 10.1.3

10.1.2 configuration files will be residing in 10.1.2. for e.g $FORMS_WEB_CONFIG_FILE is placed under $ORA_CONFIG_HOME/10.1.2 - in my instance it is in /slot/ems1636/appmgr/inst/apps/tkr12r3d_rws60029rems/ora/10.1.2/forms/server/appsweb.cfg

10.1.3 configuration files will be placed under $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/

httpd.conf is located at $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/

Same location you will see (all apache related configurations files)

Env file:

$ORA_CONFIG_HOME/10.1.3/$CONTEXT_NAME.env

OH config files: Location: $ORA_CONFIG_HOME/10.1.3/config

These files are security related/iAS related parameters. Mostly no modifications required manually. OID/SSO registrations scripts modifies directly.

ias.properties
iasschema.xml

j2ee_instance_jazn.properties
jazn-data.xml
jazn.xml

Apache Related Configuration Files: $IAS_ORACLE_HOME/Apache/Apache/conf

apps.conf
custom.conf
dms.conf
httpd.conf
mod_oc4j.conf - newly introduced for OC4J conf
mod_osso.conf - replaced mod sso conf
oracle_apache.conf
osso
restricted_mode_apache.conf
security.conf
ssl.conf
ssl_terminator.conf
trusted.conf
url_fw.conf

OPMN config file:

$ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml

Used by Oracle Process Manager and Notification Server (OPMN)

Contains details of all the OC4J instances deployed on the server

Location of the log files for OPMN

Various ports used by OPMN
$ORA_CONFIG_HOME/10.1.3/network/tnsnames.ora - Apache connection config file

OC4J configuration files:

There are three OC4J deployments in 10.1.3 oracle Homes
they are oacore oc4j, forms oc4j, oafm oc4j. All oc4j's will have similar configuration files.

application.xml
default-web-site.xml
global-web-application.xml
j2ee-logging.xml
jazn.xml
jms.xml
oc4j-connectors.xml
oc4j.properties
ohwconfig.xml
rmi.xml
server.xml
system-application.xml
system-jazn-data.xml

JavaCache config file:

$ORA_CONFIG_HOME/10.1.3/javacache/admin/javacache.xml

10.1.2 OH related config Files:

Env file:

$ORA_CONFIG_HOME/10.1.2/$CONTEXT_NAME.env

Form Server Configuration Files:
Location: $ORA_CONFIG_HOME/10.1.2/forms/server
default.env (replacement of formservlet.ini in 11i)
socket.env

appsweb.cfg ($FORMS60_WEB_CONFIG_FILE is replaced with $FORMS_WEB_CONFIG_FILE)

Report Builder config file:

$ORA_CONFIG_HOME/10.1.2/reports/conf/rwbuilder.conf

10.1.2 Listener config files:
$ORA_CONFIG_HOME/10.1.2/network/admin