Oracle applications

2011-06-19T15:53:00.001+05:30

2011-05-25T00:48:00.001+05:30

How Much Normalization is Enough?
I'm pretty sure you've already had enough of this topic as well. I have had a number of contacts from accidental DBAs and new developers in appreciation of the great lessons shared from real world problem solving.

J writes in with an ERD (Entity Relationship Diagram) emphasizing how detailed you can get with your normalization. J submits the following design with a nice big smile.

I decided to share this last example for the newbies to demonstrate the extremes to which you can take normalization. What I find really interesting about this schema is that it emphasizes the fact that one size does not fit all. I have actually worked on two databases in the last 25 years where this idea was the correct design (out of hundreds of different databases).

This schema represents the North America Numbering Plan. It highly reflects the actual switching hardware used by telephone companies to make connections from one phone device to another over a switched network.

Each of the three segments of a number have meaning and restrictions. For example, the number 0 is never allowed for the first digit of the NPA (Number Plan Area Code) or NXX (Central Office Exchange Code). This is to simplify the complexity of the switch allowing a user to dial 0 to contact a switchboard operator. See the link above for more rules and a history of how they were created.

The database I worked with was for telephone billing. Therefore, the components of the phone number were significant. I had the option to store them all in a single field (column) and use substrings to break the component parts out. Or, I could easily put them in their own column as represented by the Phone Numbers table.

I did have NPA and NXX tables because they are real entities. An NPA represents a physical region as a collection of Exchanges. An NXX represents a central office hosting the switches connected to customer telecom devices. It was important that I be able to validate accuracy at the NPA and NXX level; so separate tables were essential.

Why don't I recommend this schema for every database? Because, normally I don't care. Normally the breakout of a phone number is not relevant within the data I am storing. I can use a phone to call someone without knowing that an Area Code determines long distance or a toll call; that the NXX determines an exchange routing the call, and that the number is specifically assigned to subscriber. I simply dial 10 (or 11 digits if I also specify the country code) and my call magically connects with my intended party.

Additionally, this schema does not lend itself for use outside NAPN countries. A string or number field is adequate, and is flexible to the switching devices worldwide. A string value allow for the storing of the format associated with the number. A Numeric value requires an additional field for the format to be used when presenting the number. Either way works.

This article is extracted from below link
http://www.sswug.org/editorials/default.aspx?id=2208

2011-05-24T18:25:00.004+05:30

ORACLE PURCHASING TABLES in 11.5.X

segment1 - is the system–assigned number you use to identify in forms and reports.

Table Name Columns

PO_REQUISITION_HEADERS_ALL : REQUISITION_HEADER_ID, PREPARER_ID, SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG

Stores information about requisition headers. You need one row for each requisition header you

create. Each row contains the requisition number, preparer, status, and description.SEGMENT1 is the number you use to identify the requisition in forms and reports(unique).

PO_REQUISITION_LINES_ALL REQUISITION_LINE_ID,REQUISITION_HEADER_ID, LINE_NUM,LINE_TYPE_ID,CATEGORY_ID, ITEM_DESCRIPTION,UNIT_MEAS_LOOKUP_CODE ,UNIT_PRICE, QUANTITY, DELIVER_TO_LOCATION_ID, TO_PERSON_ID, SOURCE_TYPE_CODE

stores information about requisition lines.line number, item number, item category, item description,

need–by date, deliver–to location, item quantities, units, prices, requestor, notes, and suggested supplier information for the requisition line.

LINE_LOCATION_ID - purchase order shipment line on which you placed the requisition. it is null if you have not placed the requisition line on a purchase order.

BLANKET_PO_HEADER_ID and BLANKET_PO_LINE_NUM store the suggested blanket purchase agreement or catalog quotation line information for the requisition line.

PARENT_REQ_LINE_ID contains the REQUISITION_LINE_ID from the original requisition line if you exploded or multisourced this requisition line.

PO_HEADERS_ALL PO_HEADER_ID, AGENT_ID, TYPE_LOOKUP_CODE,SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG

information for your purchasing documents.There are six types of documents that use PO_HEADERS_ALL

RFQs, Quotations, Standard purchase orders, Planned purchase orders, Blanket purchase orders, Contracts

can uniquely identify a row in PO_HEADERS_ALL using SEGMENT1 and TYPE_LOOKUP_CODE or using PO_HEADER_ID.BLANKET_TOTAL_AMOUNT for blanket purchase orders or contract purchase orders .

if we use copy document Oracle Purchasing stores the foreign key to your original RFQ in FROM_HEADER_ID.

PO_LINES_ALL : PO_LINE_ID, PO_HEADER_ID, LINE_TYPE_ID, LINE_NUM

stores current information about each purchase order line. CONTRACT_NUM reference a contract

purchase order from a standard purchase order line.

PO_VENDORS : VENDOR_ID, VENDOR_NAME, SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG

information about your suppliers.purchasing, receiving, payment, accounting, tax, classification, and general information.

PO_VENDOR_SITES_ALL VENDOR_SITE_ID, VENDOR_ID, VENDOR_SITE_CODE

information about your supplier sites.a row for each supplier site you define. Each row includes the site address, supplier reference, purchasing, payment, bank, and general information. Oracle Purchasing uses this information to store supplier address information.

PO_DISTRIBUTIONS_ALL PO_DISTRIBUTION_ID, PO_HEADER_ID, PO_LINE_ID,LINE_LOCATION_ID, SET_OF_BOOKS_ID,

CODE_COMBINATION_ID,QUANTITY_ORDERED,

DISTRIBUTION_NUM

contains accounting distribution information fora purchase order shipment line.You need one row for each distribution line you attach to a purchase order shipment.

There are four types of documents using distributions in Oracle Purchasing:

Standard Purchase Orders, Planned Purchase Orders, Planned Purchase Order Releases, Blanket Purchase Order Releases includes the destination type, requestor ID, quantity ordered and deliver–to location for the distribution.

PO_RELEASES_ALL PO_RELEASE_ID, PO_HEADER_ID, RELEASE_NUM, AGENT_ID, RELEASE_DATE

contains information about blanket and planned purchase order releases. You need one row for each release you issue

for a blanket or planned purchase order. Each row includes the buyer, date, release status, and release number. Each release must have at least one purchase order shipment

PO_VENDOR_CONTACTS VENDOR_CONTACT_ID, VENDOR_SITE_ID

stores information about contacts for a supplier site. You need one row for each supplier contact you define.

Each row includes the contact name and site.

PO_ACTION_HISTORY OBJECT_ID, OBJECT_TYPE_CODE , OBJECT_SUB_TYPE_CODE, SEQUENCE_NUM

information about the approval and control history of your purchasing documents. There is one record in

this table for each approval or control action an employee takes on a purchase order, purchase agreement, release, or requisition.

stores object_id -- Document header identifier,OBJECT_TYPE_CODE --- Document type, OBJECT_SUB_TYPE_CODE --Document subtype SEQUENCE_NUM --Sequence of the approval or control action for a document

PO_REQ_DISTRIBUTIONS_ALL DISTRIBUTION_ID, REQUISITION_LINE_ID, SET_OF_BOOKS_ID,CODE_COMBINATION_ID,

REQ_LINE_QUANTITY,DISTRIBUTION_NUM

stores information about the accounting distributions associated with each requisition line.

PO_LINE_LOCATIONS_ALL LINE_LOCATION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, PO_HEADER_ID, PO_LINE_ID, SHIPMENT_TYPE

contains information about purchase order shipment schedules and blanket agreement price breaks. You need one row for each schedule or price break you attach to a document line. There are seven types of documents that use shipment schedules:

RFQs,Quotations,Standard purchase orders,Planned purchase orders,Planned purchase order releases,Blanket purchase orders, Blanket purchase order releases

Each row includes the location, quantity, and dates for each shipment schedule. Oracle Purchasing uses this information to record delivery schedule information for purchase orders, and price break information for blanket purchase orders, quotations and RFQs.

2011-05-09T20:39:00.002+05:30

Lexical Parameter LP_D D=Description

Bind Parameter P_D D=Description

Query Q_D D=Description Description should reflect the query selection.

Example :

Q_Payments

Group G_D D=Description Description should be the column Name on which the Group is based

Formula Column CF_D D=Description Follow same guidelines as function naming.

Summary Column CS_D_XXX D=Source of the Column

XXX=Function of Calculation

Example :

CS_sal_sum

Placeholder Column CP_D D=Description

2011-04-19T01:34:00.002+05:30

This release introduces Oracle Subledger Accounting, E-Business Tax, Ledgers, Banks, and

other common data model components that are used by Oracle Payables.

Suppliers in Trading Community Architecture

· Suppliers are now defined as TCA Parties. During the upgrade, TCA Party records are

created / updated for all suppliers, they are linked to their records in the existing supplier

entities, and the payment and banking details are migrated into the Oracle Payments

data model. Although the underlying data model has changed, you still enter and

manage suppliers in the Suppliers windows.

Invoice Lines

· Oracle Payables introduces invoice lines as an entity between the invoice header and

invoice distributions. With the new model, the invoice header remains unchanged, and

continues to store information about the supplier who sent the invoice, the invoice

attributes, and remittance information.

· Invoice lines represent the goods (direct or indirect materials), service(s), and/or

associated tax/freight/miscellaneous charges invoiced. Invoice distributions store the

accounting, allocation and other detail information that makes up the invoice line. The

charge allocation table used in prior releases to manage accounting allocations is

obsolete.

· During the upgrade, Oracle Payables creates invoice lines for all existing invoices,

creating one line for every distribution available in the Release 11i distributions table,

except in the case of reversal pairs. In those cases, Payables creates one line with a

zero amount.

Centralized Banks and Bank Accounts Definitions

· All internal banks and bank accounts that you had defined in Release 11i are

automatically migrated to the central Cash Management entities. The bank accounts and

their payment documents are owned by a legal entity rather than by an operating unit.

See Cash Management in this appendix for more information.

· Also, the banks and bank branches are centralized in Cash Management entities as

described in the preceding paragraph. However, the bank accounts you had defined for

your suppliers are migrated from the Payables entities to the central Payments entities.

Payments centralize and secure all payment instrument data, including external bank

accounts, credit cards, and debit cards. See Payments in this appendix for more

information.

Payment Document Sequencing

· If you used document sequencing for payment documents in Release 11i, your

document sequence category is migrated from the payment document, which is

associated with a bank account and, hence a legal entity, to the bank account uses

entity. This change preserves the option of having document sequence categories vary

across operating units.

· Integration with Payments for Funds Disbursements

· Oracle Payments can be used by Oracle Payables for processing invoice payments. See

Payments in this appendix for more information.

· Payment Features Controlled by Global Descriptive Flexfields

· Many European payment features that were implemented using global descriptive

flexfields in Release 11i are migrated to the Oracle Payables, Oracle Payments, and

Oracle Cash Management data models.

Integration with Subledger Accounting (SLA)

· This release introduces Oracle Subledger Accounting (SLA) to manage accounting

across subledger transactions. Payables no longer create any accounting entries. During

the upgrade, accounting options and their settings, and the existing accounting entries in

the Payables data model, are moved to the new SLA accounting data model to ensure a

continuous business operation between the two releases.

· During the upgrade, all accounting events, headers, and lines from the Release 11i data

model are upgraded to the new Subledger Accounting events, headers, and lines data

model, regardless of the period range you set for the upgrade.

Integration with E-Business Tax

· Oracle E-Business Tax manages tax across the E-Business Suite. In prior releases, the

setup, defaulting, and calculation of tax for Payables was managed within Payables

using tax codes, their associated rates, and a hierarchy of defaulting options. This

method is still available in this release. During the upgrade, E-Business Tax migrates the

tax codes as appropriate within E-Business Tax so that your tax processing can work the

same way after the upgrade as it did before.

· New fields are added to the supplier, invoice, and invoice lines entities to track tax

attributes used by E-Business Tax. Many of these attributes were implemented with

global descriptive flexfields in prior releases and are upgraded to regular fields on these

entities.

2011-03-29T23:58:00.002+05:30

What is the purpose of MO: Security Option
profile option ?
MO:Operating Unit Profile Option is used to Link
Responsiblity with Operating unit.
MO: Security Profile option is used to enable "MOAC" - Multi-Org Access
Control.
Example Case:Payment manager wants to pay for 2 different OUs.
In 11i:
--------
* We should have 2 different responsibility. ie.,OU1_payables manager &
OU2_Payables manager.
* These responsibilities has to be assigned to that particular user
(Payment manager)
* That particular user (Payment manager) will login to the
OU1_payables manager responsibility and does the payment.
*Similarly, will login to the OU2_payables manager responsibility and
does the payment.
In R12:
-------
* Create only one responsibility. say, Payables manager resposibility.
* Assign this responsibility to the user (payment manager)
* Define a 'security profile' in HR in which you will attach the OUs
that needs MOAC (Multi-Org Access Control)
* Now go into the " MO: Security Profile" option and attache that
'security profile' at responsibilty level.
* Now the user can access two different OUs and make payments, sitting
at a single responsibility without changing the responsibility.

2011-02-21T19:15:00.000+05:30

Unidentified receipt: you received the payment and you don't know who is the customer.

Unapplied receipt: when u receipt the payment from customer, you know the customer and don't know the invoice number. These type of payments can be placed in unapplied receipts.

On account: if the customer makes the payment as on account and not for a specific invoice, you can place the receipts as on account and specify account combination accordingly.

Remittance: it is referred as is it cleared at the bank. These types of transactions can be in a different bucket

2011-01-28T16:31:00.000+05:30

We will need to define three Rules for each of the forms used to achieve this. Each Rules will have their own set of Conditions and Actions.

The Rules definition logic will be as follows:

For the PO form

Create Menu Item

Check for NULL value

Open the Supplier form

For the Supplier form

Set initial value of Global Variable

Go to Block VNDR and enter Query mode

Execute query and set value of Global Variable back to NULL

How to create a Zoom from PO form to Supplier form

Navigate to the Purchase Orders window using the Purchasing SuperUser responsibility.

Enter the Form Personalization form. (M) Help > Diagnostics > Custom Code > Personalize.

Rule Seq: 10

Description: Create Menu Item

Trigger Event: WHEN-NEW-FORM-INSTANCE

Context: User and set the value to your user name (don't forget to do this for every Rule you define)

Select the Actions tab

Actions Seq: 10

Type: Menu

Menu Entry: SPECIAL31

Menu Label: Zoom to Supplier Form
----------------------------------------------

Rule Seq: 20

Description: Check for Null

Trigger Event: SPECIAL31

Condition: :PO_HEADERS.VENDOR_NAME IS NULL

Context: User and set the value to your user name

Actions Seq: 10

Type: Message

Message Type: Error

Message Text: Supplier Name cannot be Null. Please enter or query a value.
----------------------------------------------

Rule Seq: 30

Description: Open the Supplier Form

Trigger Event: SPECIAL31

Context: User and set the value to your user name

Actions Seq: 10

Type: Property

Object Type: Global Variable

Target Object: XX_VENDOR (or name it wathever you want)

Property Name: VALUE

Value: =:PO_HEADERS.VENDOR_ID (using Object's identifier ID's as the Global Variable's value makes for a more robust custom)

Actions Seq: 20

Type: Builtin

Builtin Type: Launch a Function

Function Code: AP_APXVDMVD

Function Name: Suppliers

Save your work and exit all the forms.

At this point, you should test what you have done so far. From the Navigator, reopen the Purchase Orders form. From the menu bar, select Actions > Zoom to Supplier form (the menu item you defined earlier). Because you do not yet have a Supplier entered on your new PO and you're trying to Zoom to the Supplier form, you should see the error message that you defined in the Rule Seq 20. If this works, then continue with following:

Navigate to the Suppliers window using the Purchasing SuperUser responsibility.

Enter the Form Personalization form. (M) Help > Diagnostics > Custom Code > Personalize.

Rule Seq: 10

Description: Set initial value of Global Variable

Trigger Event: WHEN-NEW-FORM-INSTANCE

Context: User and set the value to your user name

Select the Actions tab

Actions Seq: 10

Type: Property

Object Type: Global Variable

Target Object: XX_VENDOR (The Global Variable you defined earlier)

Property Name: INITIAL VALUE

Value: =NULL
----------------------------------------------

Rule Seq: 20

Description: Go to Block VNDR and enter Query mode

Trigger Event: WHEN-NEW-FORM-INSTANCE

Condition: :GLOBAL.XX_VENDOR IS NOT NULL

Context: User and set the value to your user name

Actions Seq: 10

Type: Builtin

Builtin Type: GO_BLOCK

Argument: VNDR

Actions Seq: 20

Type: Builtin

Builtin Type: DO_KEY

Argument: ENTER_QUERY
----------------------------------------------

Rule Seq: 30

Description: Execute query, go to Block SITE and reset Global Variable value to NULL

Trigger Event: WHEN-NEW-RECORD-INSTANCE

Trigger Object: VNDR

Condition: :GLOBAL.XX_VENDOR IS NOT NULL

Processing Mode: Both

Context: User and set the value to your user name

Actions Seq: 10

Type: Property

Object Type: Item

Target Object: VNDR.VENDOR_ID

Property Name: VALUE

Value: =:GLOBAL.XX_VENDOR

Actions Seq: 20

Type: Builtin

Builtin Type: DO_KEY

Argument: EXECUTE_QUERY

Actions Seq: 30

Type: Property

Object Type: Global Variable

Target Object: XX_VENDOR

Property Name: VALUE

Value: =NULL

Now go ahead and test your custom. Open the Purchase Orders form and start defining a new standard PO, enter a supplier in the Supplier field (in my example, I used "ABC Corp"). Click on (M) Actions > Zoom to Supplier Form

Bingo!, you are automatically taken to the Supplier form for "ABC Corp".

Once again, this is another example of how flexible Oracle Form Personalization is and don't forget how much time I have saved. It took me about 20 minutes to do this, including defining and testing. In the past, I would have had to send functional specs over to the Technical analyst and it would have taken a couple of days to have this done thru the Custom Library.

Try it, it's easy.

ORACLE Technology Information: Create accounitng and transfer JE's to GL using SL...

2011-01-28T15:53:00.000+05:30

ORACLE Technology Information: Create accounitng and transfer JE's to GL using SL...: "I Create Accounting Program The Create Accounting program processes eligible accounting events to create subledger journal entries. To cre..."

2011-01-07T16:04:00.004+05:30

General Ledger:

Simultaneous Accounting for Multiple Reporting Requirements
Centralized Accounting Setup
Enhanced Reporting Currency Functionality
Simultaneous Data Access to Multiple Legal Entities and Ledgers
Simultaneous Opening and Closing of Periods for Multiple Ledgers
Cross-Ledger and Foreign Currency Allocations
Simultaneous Currency Translation of Multiple Ledgers
Financial Reporting Across Ledgers
Automatic Journal Copy
Streamline Automatic Posting
Streamline AutoReversal Criteria Setup
Streamline Consolidation Mappings
Replacement for Disabled Accounts
Accounting and Reporting Sequencing
Journal Line Reconciliation
Entered Currency Reporting and Analysis
Foreign Currency Recurring Journals
Intercompany Balancing Support for Encumbrances
New iSetup API’s

Oracle General Ledger adds the following iSetup API’s to enable you to quickly create your setup data and definitions:

• Ledger Sets

• Data Access Sets

Integration with Subledger Accounting

Enhanced Intercompany

The Global Intercompany System (GIS) feature from previous releases has

been incorporated into the Oracle Advanced Intercompany System product

Account Analysis and Drilldown

Account Analysis & Drilldown is a new web-based interface that allows you

to easily review and analyze your general ledger financial data. You can view

the balances of multiple detail or summary accounts in a single page and drill

down to supporting journal entries and subledger transactions all within a

browser window.

2011-01-07T15:51:00.008+05:30

• It provides a single access point for defining and managing internal bank accounts for OraclePayables, Oracle Receivables, Oracle Payroll, Oracle Cash Management, and Oracle Treasury.

• Many new bank account balance types are supported for all internal bank accounts including ledger, available, value dated, 1-day float, 2-day float, and projected balances

• Now, bank account transfers are supported directly in Oracle Cash Management. This feature allows users to create these types of cash transfers between internal bank accounts manually or automatically through physical cash pools.

• Oracle Cash Management allows users to define and manage these types of bank account structures called Self-Initiated Physical Cash Pools

• Oracle Cash Management allows users to define and manage these types of bank account structures called Bank-Initiated Physical Cash Pools.

• Organizations may choose to utilize notional cash pool arrangements offered by banks that track not only individual account balances but also the net balance across all accounts. This technique is common in some countries and does not require physical cash transfers to be made between accounts for concentration purposes.

• Oracle Cash Management allows users to define and manage these types of bank account structures called Notional Cash Pools.

Bank Statement Accounting

This feature allows users to define mapping rules that can automatically create and reconcile transactions in Oracle Cash Management based on reported prior day bank statement lines.

Bank Account Signing Authorities
This feature allows Oracle Cash Management users to enter, maintain, and report on those people in their organizations with bank account signing authority

Cash Positioning Intra-day Activities
This feature provides additional flexibility in how users view intra-day bank statement activity in their cash positions

2010-12-10T12:00:00.001+05:30

In order to improve overall application performance, it’s very important to construct SQL queries in the most efficient way. There can be many different ways to write a SQL query. Here are few tips that can help you in writing efficient and reusable SQL queries. All examples given below are based on Oracle default demo tables EMP and DEPT. You can create these tables in your local schema from the following directory in windows environment.

%ORACLE_HOME%\sqlplus\demo\demobld.sql

Order of the tables in Joins:
If you specify 2 or more tables in the FROM clause of a SELECT
statement, then Oracle parser will process the tables from right to left, so the table name you specify last will be processed first. In this case you have to choose one table as driving table.
Always choose the table with less number of records as the driving table.
Name the Columns in a Query: There are three good reasons why it is better to name the columns in a query rather than to use "select * from ...".

1. Network traffic is reduced.

This can have a significant impact on performance if the table has a large number of columns, or the table has a long or long raw column (both of which can be up to 2 GB in length). These types of columns will take a long time to transfer over the network and so they should not be fetched from the database unless they are specifically required.

2. The code is easier to understand.

3. It could save the need for changes in the future. If any columns is added to or removed from the base table/view, then “select * “statement can produce wrong results set and statement may
fail.Use table alias: Always use table alias and prefix all column names with the aliases when you are using more than one table.

Never compare NULL to anything else: All expressions return NULL if one of the operands is NULL. This is applicable for all operators except Concatenation operator (||).

Use Bind Variables: It is also better to use bind variables in queries. That way the query becomes generic and therefore re-usable. For example, instead of writing a query like -

SELECT ename, sal
FROM emp
WHERE deptno = 20;

Change it to -

SELECT ename, sal
FROM emp
WHERE deptno = :deptno;
The first query can be re-used for deptno number 20 only, whereas the second query can be reused for any other deptno also.

3

SQL Writing Convention: It is a good practice to use a standard syntax for wiring SQL queries.
I will recommend following standards to use while writing SQL queries.
Write all standard SQL TEXT in upper case:

For example:
SELECT ename, sal
FROM emp
WHERE deptno = 20;

Write all non standard SQL TEXT (Table name, Column name etc) in lower case:
For example:

SELECT ename, sal
FROM emp
WHERE deptno = 20;

Formatter Plus in Toad can be use to format SQL statements in this format. Select the complete
SQL statement and right click on the “Format Code” menu.

Note: It is important to write similar SQL statement in same case.

For example: Oracle will reparse following queries as they are not written in the same case
Select * from EMP;
Select * from emp;

Use EXISTS instead of DISTINCT: Use EXISTS in place of DISTINCT if you want the result
set to contain distinct values while joining tables.
For example:

SELECT DISTINCT d.deptno, d.dname
FROM dept d, emp e
WHERE d.deptno = e.deptno;

The following SQL statement is a better alternative.

SELECT d.deptno, d.dname
FROM dept d
WHERE EXISTS (SELECT e.deptno
FROM emp e WHERE d.deptno = e.deptno);

Use of expressions and indexes: The optimizer fully evaluates expressions whenever
possible and translates certain syntactic constructs into equivalent constructs. This is done
either because Oracle can more quickly evaluate the resulting expression than the original

4 expression or because the original expression is merely a syntactic equivalent of the resulting expression.
Any computation of constants is performed only once when the statement is optimized rather than each time the statement is executed. Consider these conditions that test for salaries greater than $2000.sal > 24000/12
sal > 2000

sal*12 > 24000

If a SQL statement contains the first condition, the optimizer simplifies it into the second condition.
Please note that optimizer does not simplify expressions across comparison operators. The optimizer does not simplify the third expression into the second. For this reason, we should write conditions that compare columns with constants whenever possible, rather than conditions with expressions involving columns.

The Optimizer does not use index for the following statement:
SELECT *
FROM emp
WHERE sal*12 > 24000 ;

Instead of this use the following statement:

SELECT *
FROM emp
WHERE sal > 24000/12 ;

Use of NOT operator on indexed columns: Never use NOT operator on an indexed column.
Whenever Oracle encounters a NOT on an index column, it will perform full-table scan.

For Example:
SELECT *
FROM emp
WHERE NOT deptno = 0;

Instead use the following:

SELECT *
FROM emp
WHERE deptno > 0;
Function or Calculation on indexed columns: Never use a function or calculation on an indexed column. If there is any function is used on an index column, optimizer will not use index.
For Example:

5
Do not use until need exactly match string:
SELECT *
FROM emp
WHERE SUBSTR (ename, 1, 3) = 'MIL';

Use following instead:

SELECT *
FROM emp
WHERE ename LIKE 'MIL%';
Do not use the following as || is the concatenate function. Like other functions and it disables index.

SELECT *
FROM emp
WHERE ename || job = 'MILLERCLERK';

Use the following instead

SELECT *
FROM emp
WHERE ename = 'MILLER' AND job = 'CLERK';.
Avoid Transformed Columns in the WHERE Clause: Use untransformed column values.

For example, use:
WHERE a.order_no = b.order_no
Rather than
WHERE TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1))
= TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1))
Combine Multiples Scans with CASE Statements: Often, it is necessary to calculate
different aggregates on various sets of tables. Usually, this is done with multiple scans on the table, but it is easy to calculate all the aggregates with one single scan. Eliminating n-1 scans
can greatly improve performance.

Combining multiple scans into one scan can be done by moving the WHERE condition of each scan into a CASE statement, which filters the data for the aggregation. For each aggregation, there could be another column that retrieves the data.
The following example has count of all employees who earn less then 2000, between 2000 and 4000, and more than 4000 each month. This can be done with three separate queries.

SELECT COUNT (*)
FROM emp
WHERE sal < 2000; SELECT COUNT (*) FROM emp WHERE sal BETWEEN 2000 AND 4000; SELECT COUNT (*) FROM emp WHERE sal>4000;

However, it is more efficient to run the entire query in a single statement. Each number is calculated as one column. The count uses a filter with the CASE statement to count only the rows where the condition is valid. For example:

SELECT COUNT (CASE WHEN sal < 2000 THEN 1 ELSE null END) count1, COUNT (CASE WHEN sal BETWEEN 2001 AND 4000 THEN 1 ELSE null END) count2, COUNT (CASE WHEN sal > 4000
THEN 1 ELSE null END) count3
FROM emp;

2010-12-07T11:46:00.005+05:30

TCA Changes for Suppliers
New Tables
The following are the new tables added in Release 12 as a part of this initiative. Refer to the Oracle E-Business Suite Electronic Technical Reference Manual for the table and column descriptions..

• AP_SUPPLIERS
• AP_SUPPLIER_SITES_ALL
• AP_SUPPLIER_CONTACTS
• AP_SUPPLIER_INT_REJECTIONS

New Views

The following are the new views added in Release 12 as a part of this initiative. These are backwards compatible views that join the Suppliers table in Payables and the TCA tables, so that any teams using the old suppliers tables have no impact.

• PO_VENDORS
• PO_VENDOR_SITES_ALL
• PO_VENDOR_CONTACTS

The following tables are obsoleted:

• PO_VENDORS
• PO_VENDOR_SITES_ALL
• PO_VENDOR_CONTACTS

2010-12-06T13:30:00.004+05:30

11.5.10 Function	R12 Equivalent
Set of Books	Ledgers
MRC or Multiple Reporting Currencies	Reporting Currencies
Various Accounting Methods in Subledgers	SLA, or Subledger Accounting
GIS or Global Intercompany System	GIS or Global Intercompany System
Client ADI	WebADI and Report Manager
Reporting Set of Books	Reporting Currencies
Global Accounting Engine	SLA, or Subledger Accounting
Global Tax Engine	E-Business Tax
Automatic Tax Calculations	E-Business Tax
General Ledger Automatic Tax Calculation	E-Business Tax

The final field that has upgrade implications between the Reporting and Primary Ledgers is the Suspense Posting Flag. In 11i, these can be different, but not in R12. The Reporting Ledger will adopt the setting from the Primary Ledger, and it will utilize the suspense account on the Primary Ledger as well.

In 11i, Average Balances can be set at either the Primary or Reporting Set of Books. In R12, it is set only at the Primary Ledger and inherited for the Reporting Currencies. EBS will preserve the setting in a table for this, but it will need to be updated if this was set to Average for the Reporting Set of Books and not on the Primary Set of Books, as this inconsistency will cause an error when posting journal entries.

Translated Balances are upgraded as Balance Level Reporting Currencies in the Primary Ledger,and they can continue to be used by performing translations in R12. This process can only be used for upgraded, translated balances. Any new reporting currencies that are added in R12 must be added as reporting currencies under the Ledger and will be translated using the R12 methodology as set up in the Ledger.

Any inconsistencies in Reporting Sets of Books from Oracle’s recommended setup can cause different results or errors during the upgrade. For example, Reporting Sets of Books that are not assigned to a Primary Set of Books will be upgraded as Primary Ledgers. And Reporting Sets of Books that are not constantly used between modules (such as being used in Accounts Payables but not Accounts Receivables) will cause upgrade errors. Reviewing all Reporting and Primary Set of Books setups prior to upgrade and making any adjustments needed to conform to Oracle’s recommended setups will not be wasted time but will make the upgrade results more consistent
and predictable

2010-12-04T00:09:00.002+05:30

BAM uses its active data cache to display real-time dashboards. This active data cache is updated constantly by an underlying refresh mechanism and SOA Suite composite provides a powerful yet simple way of refreshing BAM cache in real-time. On the receiving end of the SOA composite is BAM’s active data cache, which consists of BAM data objects. A Data Object is like a relational table that stores data and must be refreshed constantly. And on the other end, which is the source side of the SOA composite, can be a JMS Topic, a relational schema, a web service or just good old flat files. It entirely depends on the use case you are building. In this sample, I have used a relational table as the data source. Both ends are then connected in a SOA Suite composite using an array of Technology Adapters. More will become evident about technology adapters as we go over the sample application, but to say the least, Technology Adapters are really simple to build and screen developers from many lines of code that may be required in a native application. These adapters are the main cause behind the power and ease of development of SOA Suite composite applications.

2010-12-03T23:47:00.003+05:30

Watch this blog:

http://blogs.oracle.com/FMWForEBusinessSuite/

Cheers,
sekhar

2010-11-25T23:46:00.002+05:30

In order to generate a delimited file output, you need to concatenate columns using the desired delimiter i.e. comma:

Example:
select empno|| ‘,’||ename||’&’||mgr from X;

Other option is using:
SQL> set colsep ‘,’
SQL> spool c:\testexcel.csv
SQL> select * from emp;

Change some of the default SQL*Plus parameters, that will be garbage for Excel:
feedback=off
newpage=none
termout=off
header=off

If some columns are empty, be aware to include the delimiter, too:
nvl(to_char(col2),’,')

2010-11-20T11:10:00.004+05:30

You can find more information on P2P cycle

http://shareoracleapps.blogspot.com/2010/05/procure-to-pay-cycle-in-oracle-apps-r12.html

-Cheers
Sekhar

2010-11-19T12:05:00.005+05:30

11.5.X	R12
AP_INVOICES_ALL AP_PAYMENT_SCHEDULES_ALL	IBY_PAYMENTS_ALL
AP_INVOICES_ALL AP_PAYMENT_SCHEDULES_ALL	IBY_DOCS_PAYABLE_ALL , IBY_PAY_SERVICE_REQUESTS , IBY_PAY_INSTRUCTIONS_ALL
Obsolete Tables
AP_BANK_ACCOUNT_USES_ALL		CE_BANK_ACCT_USES_ALL
AP_CHECK_STOCKS_ALL		CE_PAYMENT_DOCUMENTS
AP_BANK_ACCOUNTS_ALL		IBY_EXTERNAL_PAYEES_ALL, IBY_EXT_BANK_ACCOUNTS, IBY_PMT_INSTR_USES_ALL

2010-11-18T16:14:00.002+05:30

Accounting For Oracle Receivables

Flow of Accounting Information:

- If you are using Oracle Order Entry (without customizations), no accounting information is available until you run AutoInvoice. You pass the transactions to Oracle Receivables using the Receivables Interface. You then run AutoInvoice which creates the actual transactions and uses AutoAccounting to derive the segment values for the GL Accounts. If you are using Oracle Projects the account segment values are derived by a Projects’ process also called AutoAccounting and passed as values to Oracle Receivables via the Streamline process, also using AutoInvoice.

- Whether you are manually entering your receipts or processing them through AutoLockbox, the accounting information is automatically determined by Oracle Receivables when you create and apply the receipts (not when it is still a "QuickCash" batch). The values used are based on the setup values for the bank where the receipts were deposited and the invoices they are paying.

General Ledger Interface:- You pass accounting information from Oracle Receivables to Oracle General Ledger using the General Ledger Interface. If you have properly set up Oracle Receivables, you should never have to create manual journal entries in your General Ledger and the two systems should always be in sync.

- When you invoke the General Ledger Interface process, you initiate multiple programs that:

Finds all of the records for the period you specified that have not yet been passed to the General Ledger;

1. Determines if the debits equal the credits;

2. Passes the data to GL for editing; and

3. Marks the records as having been passed (so they will not pass twice).

- If you have specified that you want the Journal Import to also run, this process verifies that the individual segments and combinations of segments are valid. Only when the Journal Import completes successfully are the Journals available for posting.

Tips:

1. Always run the General Ledger Interface using the starting date of the period through the last day of the period. This is applicable no matter when you are running the process or if you know you will never have activity for that date, since sometimes the system uses dates other than the dates you expect.

2. Depending on which patches you have applied, you may or may not see the Unposted Items Report. If this report does run, always check each page to ensure that you have no items that could not be passed to the General Ledger. If anything besides headings appears, work with your IT department to resolve (since this is usually caused by a bug).

Verify that the amounts in the General Ledger Interface Report are reasonable and that the debits equal the credits.

3. Verify that the Journal Import has a status of "SUCCESS." If not, you had a problem that will need to be resolved or none of the items in the batch will be available for posting. Generally you have a problem if an account was valid when the activity was created, as you know, you cannot save with invalid values but, someone has since disabled either a segment or the combination. An example of this is your Accounts Receivable account that may have been valid when the invoice was originally created but it is not longer valid, and a receipt was just applied against it. When you apply a receipt to an invoice it always causes an offsetting entry against the original Accounts Receivable account.

Should this occur, then

1. Re-enable the segment or combination;

2. Re-run the Journal Import (in GL -- be sure to include the applicable id);

3. Create a manual journal entry (also in GL) to move the activity from the bad account to the proper account (this is my one exception to never creating manual journal entries); and

4. Re-disable the segment or combination.

By making the corrections in this way you are able to keep your GL in sync with your AR activity and you have an audit trial of what you did to make the correction. You have the option to correct in the Import Corrections form (in GL), but you lose the audit trail of what you did and why. Note what you did and why and storing the notes in a handy binder so you will be prepared when the auditors ask why you did what you did.

Journal Entries Reports: The Journal Entries reports are the best way to verify the actual accounting for Oracle Receivables’ activities and the only way to view the accounting for the foreign currency gains and losses. There are actually four reports that give you varying levels of details regarding the journal entries you will be creating or have already created. These reports may be run at anytime before or after you run the General Ledger Interface. Your options are: Detail by Account (very large), Summary by Account, Detail by Category (also large) and Summary by Category.

Tip: Run the Summary by Category and review to insure that there are no invalid or illogical accounts, prior to running the General Ledger Interface. If you find funny accounts, you can correct or create offsetting entries prior to posting. Run the Detail by Category (just for that category and account) to see which specific activities used the funny account. Correct the activity if possible. If not possible (i.e., adjustment), create an offsetting entry using the proper account.

Tip: If you run this report for Unposted Items only, you must leave the Posted Date range blank or nothing will appear on the report.

Period Close Procedures:

Tip:Never have more than one AR period open at one time. There have been problems with entries appearing partially in one period and partially in another. Also, you may accidentally enter activities in a period other than the period you intended.

Create a checklist to insure that you always know where you are and what you have to do next, so you will not forget anything.

Balance your AR activity to the Aging:

Old Aging Balance -----(Aged Trial Balance - 7 Buckets by Account)

________________________________________________

+ New Invoices -------Transaction Register

+ Debit Memos ------- Transaction Register

+ Chargebacks --------Transaction Register

- Credit Memos ------ Transaction Register

- Receipts Applied ---- Unapplied Receipts Register

+/- Adjustments ------Adjustment Register

- Items Not Aged ----- Invoice Exceptions Report

____________________________________

New Aging Balance --- Aged Trial Balance - 7 Buckets by Account

Also balance your AR activity to your GL activity using the Journal Entries Report - Summary by Category and the Account Analysis report (in GL). Note any manual journal entries that used "your" accounts.

Accounting Details

The GL Accounts may or may not appear on the form (depending on what you are doing) but almost every activity you perform has an accounting impact. In order to understand this impact it is necessary to know:

1) what accounts are impacted by each transaction;

2) what are the related set ups;

3) what you may change and/or override and what is out of your control.

AutoAccounting : AutoAccounting a very powerful setup feature that tells Oracle Receivables how to determine the individual segment values for your Transactions (invoices, debit memos, credit memos, chargebacks and commitments) using the rules that you specify. You may use this feature when creating Transactions manually or through AutoInvoice. The types of accounts impacted by AutoAccounting include:

- (Accounts) Receivable

- Revenue

- Tax

- Freight

- Unearned Revenue (for deferred revenue recognition)

- Unbilled Receivable (for deferred receivables recognition)

- AutoInvoice Clearing (for problems with extended amount)

- Possible sources of this information are the values you set up for the following:

- Transaction Types

- Salesreps

- Standard Lines (Items or Memo Lines)

- Taxes

- And/or hard coded values.

You may get one segment value for one type of account from a different place than for another. See Appendix 1 for an example of a typical AutoAccounting setup.

You can use a similar worksheet to test the setup of your AutoAccounting rules. List your Accounting Flexfield segments in the left column. For each type of account select the source of each segment (based on the list of available sources) and fill in that box. Test your theory by listing what all the setup accounts would be for a Transaction Type, Salesrep, Item, Tax and Memo Line. Then use a white-board and fill in each segment, for each type of account, with the values from each of the related sources. Verify that the combinations are actually valid, if not, redesign how they will be set up or redefine your AutoAccounting rules. Once you are satisfied with the results, enter your AutoAccounting rules into your test system and start creating manual invoices. Verify that you have not created invalid account values as the defaults.

Tip: I prefer to assign all segments to sources versus using hard coded values. This seems more flexible for future changes.

Invoices: When you create an invoice either through AutoInvoice or manually, you take advantage of AutoAccounting to provide the default Accounting Flexfield values. For manual invoices you have the option to override the default values.

For a standard Invoice:

DR : AR (AutoAccounting - may override)

CR : Revenue (AutoAccounting - may override)

:Tax (AutoAccounting - may override)

:Freight (AutoAccounting - may override)

You may also create invoices with special accounting and invoicing rules that allow you to defer revenue recognition for the percentage and number of periods that you specify. The following is an example of an invoice created with deferred revenue recognition for $12,000 split evenly over 12 periods:

For invoices with deferred revenue: a) When first created:

DR : AR (AutoAccounting - may override) 12000

CR :Unearned Revenue (AutoAccounting) 1000

DR : Unearned Revenue (AutoAccounting) 12000

CR : Revenue (AutoAccounting - may override) 1000

b) For each of the next 11 periods:

DR : Unearned Revenue (AutoAccounting) 1000

CR : Revenue (AutoAccounting) 1000

If you are using deferred revenue recognition, you need to run the revenue recognition process for each period (Run Revenue Recognition) and runs automatically as part of the General Ledger Interface.

Tip: To reduce the time it takes to close the period, run Revenue Recognition prior to the time when you are actually closing (e.g., the night before the close). This will process the majority of the updates prior to the actual close.

Recurring Invoices (Transaction Copy) are treated like regular invoices, except they have different GL dates. Once you have created an invoice copy, it really is just another invoice with different dates.

Debit Memos: Debit memos work just like standard invoices (you even create them on the same forms) -- taking advantage of AutoAccounting but with overridable segments. If you defined Memo Lines for use with your debit memos, they will provide the default accounting segments if you have set up AutoAccounting to use Standard Lines values for your Revenue accounts.

Credit Memos And On Account Credits: There are two types of credit memos: credit memos that you create to offset an individual invoice are called "Credit Memos." Credit memos that impact a customer’s account but are not initially tied to a specific invoice are called "On-Account Credits." On-account credits may be tied to invoice(s) using the Receipts Applications window, at any time. The accounting for Credit Memos usually offsets the applicable accounts from the original invoice (if you set your System Profile option AR: Use Invoice Account For Credit Memo to "Yes"). Credit memos and on-account credits that are created using AutoInvoice take advantage of AutoAccounting and/or hard coded values. You may override the default values if you are entering manually.

Credit Memo tied to an invoice:

DR : Revenue (from the related invoice - may override)

: AR (from the related invoice - may override)

: Tax (from the related invoice - may override)

CR : Freight (from the related invoice - may override)

On-account credits take advantage of AutoAccounting and Standard Lines (Memo Lines) depending on how you set up your AutoAccounting rules for the default credit and debit GL Accounts. You may override the default values at entry time if you are entering manually.

DR : Revenue (Memo Line - may override)

CR : AR (AutoAccounting - may override)

When you apply an on-account credit to invoice(s), you debit the credit account you used when you created the on-account credit. The Accounts Receivable account for the invoice being offset is credited. You may not override these values.

DR : AR (from the On-Account Credit)

CR : AR (from the invoice)

Cash Receipts (Excluding Miscellaneous Receipts): The accounting for receipts, except for Miscellaneous Receipts, is totally controlled behind the scenes by Oracle Receivables. The GL Accounts are determined by the values you defined in Receipt Class for the batch.

NOTE: You have one Cash, Unapplied, On-Account, Unidentified, Earned Discount and Unearned Discount account for each bank and class, which does not allow you to split the Unapplied, etc. accounts for the applicable cost center or division.

You may set up different values for each bank and class that you use (especially important for the cash account). Or, you may share the GL Accounts for multiple bank accounts (i.e., the unapplied and discount accounts). The key accounts are:

- Your cash account (the default debit account for that bank account);

Tip: Often AP and AR share the same bank account but it is helpful to use a different but sequential GL account for each. This eases the reconciliation but you can roll together for FSG reporting.

- Your unapplied payments account (the default used until you match the payment to an invoice);

- Your on-account account (used to account for pre-payments until you apply them to invoice(s));

- Your unidentified account (used for receipts where you do not know which customer sent the receipt);

Tip: Often companies use the same GL Account for unapplied, on-account and unidentified. This is fine as long as: the account is not used for anything else and it is not an Accounts Receivable or cash account.

- Your earned and unearned discount accounts (used when a client pays invoices in accordance with the early payment terms. These are also often the same. Earned discounts are for payments made within the discount terms, unearned discounts are paid after the discount term but are allowed anyway.

When you match a receipt to an invoice, the cash account (debit) defaults from the Receipt Class for the Receipt batch. The Accounts Receivable account (credit) defaults from the invoice that is being paid.

NOTE: Even if you instantly match a payment to an open invoice, Oracle still creates credits and debits to the unapplied account.

Payment applied to an invoice without discount terms:DR : Cash (Receipt Class)

: Unapplied (Receipt Class)

CR : Unapplied (Receipt Class)

: AR (from the invoice)

Payment applied to an invoice with discount terms:DR : Cash (Receipt Class)

: Unapplied (Receipt Class)

: Discount (Receipt Class)

CR : Unapplied (Receipt Class)

: AR (from the invoice)

When you leave a receipt as unapplied:DR : Cash (Receipt Class)

CR : Unapplied (Receipt Class)

When you identify a receipt is as a pre-payment or deposit:DR : Cash (Receipt Class)

CR : On-Account (Receipt Class)

For unidentified receipts:DR : Cash (Receipt Class)

CR : Unidentified (Receipt Class)

When you apply unapplied, on-account or unidentified receipts, the accounting is determined by the original status. The accounts used are based on the accounts you currently are using for the Receipt Class. The Accounts Receivable account still comes from the invoice.

DR : Unapplied (Receipt Class)

On-Account (Receipt Class)

or Unidentified (Receipt Class)

CR : AR (from the invoice)

When you unapply a receipt, the accounting is just the opposite of the application accounting. You debit the AR account for the original invoice and credit the unapplied account based on the current unapplied account for the Receipt Class:

DR : AR (from the invoice)

CR : Unapplied (Receipt Class)

When you reverse a receipt, you have two possible options: re-open the invoices you previously paid or create a debit memo for the amount of the reversed payment. If you re-open the invoices, the system offsets the accounts used when you originally applied the payment (from the invoice and the cash account). Note that this process also impacts the unapplied account.

DR : Unapplied (Receipt Class)

: AR (from the invoice)

CR : Cash (Receipt Class)

: Unapplied (Receipt Class)

If you create a debit memo, you credit the original cash account but debit the Accounts Receivable Account for the Debit Memo type you selected. You may override the Accounts Receivable account when you enter the payment reversal.

DR : AR (Transaction Type - may override)

CR : Cash (Receipt Class)

Chargebacks: You create Chargebacks when you are applying cash to close the original invoice and create a new invoice for the amount that the customer short paid. By definition, there is a one to one relationship between a Chargeback and the original invoice. You need to set up values for Chargebacks in 3 places: Receivables Activity where you specify the "wash" account used when creating a Chargeback. Transaction Types where you specify the default AR account. A Memo Line ("Chargeback Line") is seeded by Oracle but it is just used for the line description when you print the Chargeback and has no accounting impact. The Accounts Receivable account for the new invoice is based on the Accounts Receivable account for the Chargeback but you may override it at entry item. Oracle credits the Accounts Receivable account for the original invoice (note that these two accounts may be different).

In the Category of Adjustment:DR : Chargeback Adjustment (Receivables Activity)

CR :

In the Category of Adjustment (AR):

DR :

CR : AR (from the original invoice)

In the Category of Chargeback:DR :

CR : Chargeback Adjustment (Receivables Activity)

In the Category of Chargeback (AR):DR : AR (from the chargeback - you may override)

CR :

In the Category of Trade Receipts:DR : Cash (Receipt Class)

CR :

In the Category of Trade Receipts (AR):DR : Unapplied (Receipt Class)

CR : AR (from the original invoice)

: Unapplied (Receipt Class)

Miscellaneous Receipts: Miscellaneous Receipts are any receipts that are not for open receivables. Examples include Cobra payments, T-shirt sales, utility refunds, and returns on investments. Due to the nature of this activity, you may need to credit any account within the chart of accounts. The Distribution Window in the Receipts form allows you to do just that. You may run into an Account Security Rule set up to restrict usage of accounts by application. If you find that you may not use an account that you need, work with your System Administrator to change the Account Security Rules.

You may pre-define the credit accounts that you usually use to speed entry (using Receivables Activity) but you also have the flexibility to override the values at entry time. You also have the ability to split a single receipt into multiple accounts (you may also pre-define those accounts using Distribution Sets).

If you will always be splitting the accounts, you should define a Distribution Set. A distribution set is a name and one or more GL Accounts and percentages that you define. You must create a Receivable Activity that refers to the Distribution Set.

When you enter Miscellaneous Receipts, you refer to the Receivables Activities that you defined above. However, you may override the default GL Accounts, the individual segments, the percentages and/or the amounts. The cash account used defaults based on the Receipt Class for the bank you specified on the Batch Screen, and you may not override or view the value.

DR : Cash (Receipt Class)

CR : Miscellaneous Account(s) (Receivables Activity or Distribution Set - may override)

Receivable Adjustments: Receivable Adjustments are generally write-offs, or changes to the invoice balance due for over- or under-payment by the customer, or the addition of finance charges. Pre-define commonly used adjustment types using the Receivables Activity form. This speeds entry, but you may override the default values as you enter the adjustments. NOTE: Always define a GL Account and not a Distribution Set when you define Receivable Activities for adjustments.

Tip: When entering an adjustment, never use an Accounts Receivable Account. Oracle Receivables already automatically offsets the AR account for the invoice being adjusted and you will create a wash entry.

A Receivables Adjustment is always applied to a specific invoice so it impacts the Accounts Receivable account for that invoice. Receivables adjustments may either be positive (debit AR, and increase the invoice balance) or negative (credit AR and decrease the invoice balance). Examples include:

Add a finance charge (note that this is a positive adjustment that increases the balance due):

DR : AR (from the invoice)

CR : Finance Charges (Receivables Activity - may override)

Reduce the freight amount:

DR : Freight (Receivables Activity - may override)

CR : AR (from the invoice)

Write-off the invoice balance:DR : Cost of Doing Business (Receivables Activity - may override)

CR : AR (from the invoice)

You may use AutoAdjustments to perform mass cleanup of open invoices and on-account credits. The Accounts Receivable account credited is the Accounts Receivable account for the transaction. The account debited is based on the Receivables Activity you select when you submit the AutoAdjustment process. Note that ALL adjustments made during this process will use that exact same "write off" account even if the original invoices are for different companies, or cost centers. This may be a consideration in determining if you can actually utilize AutoAdjustments, or if you want to run multiple passes of AutoAdjustment by Transaction Type and Adjustment Activity.

Foreign Currency Gains and Losses: Transactions that are not in your base currency may cause gains or losses to occur due to fluctuations in the exchange rates. This is automatically accounted for by Oracle Receivables. When you enter the Transaction, the applicable exchange rate for the date you enter it is stored with the transaction. When you enter the related receipt the applicable exchange rate for the date you enter the receipt is stored with the receipt. The gain or loss is determined based on the difference in the value of the money (in your base currency) between when the invoice was created and when the receipt was created. The gain and loss accounts are derived based on the values in your System Options and how you set up Flexbuilder. Note that most companies use the default setup for Flexbuilder. Note that there is no gain or loss if you apply an adjustment since both the adjustment and the invoice use the same rate. You can predict Gains and Losses using the Projected Gains/Losses Report. You can only view the gain/loss accounting activity by running the Journal Entries Report.

Gain - now worth more:DR : Cash (Receipt Class at the receipt rate)

: Unapplied (Receipt Class at the receipt rate)

CR : AR (from the invoice at the invoice rate)

: Unapplied (Receipt Class at the receipt rate)

: Gain (System Options - difference between the invoice and receipt values)

Loss - now worth less:

DR : Cash (Receipt Class at the receipt rate)

: Unapplied (Receipt Class at the receipt rate)

: Loss (System Options - difference between the invoice and receipt values)

CR : AR (from the invoice at the invoice rate)

:Unapplied (Receipt Class at the receipt rate)

2010-11-17T11:38:00.003+05:30

Demantra is a recent acquisition of Oracle, since then Oracle is strategically positioning Demantra in place of Oracle Demand Planning module that was developed by Oracle. Demantra is very well integrated with Oracle apps ERP and Oracle APS modules.
Demand Management covers forecasting of demand using statistical techniques and sales and operations planning to facilitate consensus demand and supply planning. Demand Management consist of three modules that can be licensed and implemented separately.

Demand Management (DM)
Advanced Forecasting & Demand Modeling (AFDM)
Real-Time Sales and Operations Planning (RTSOP)

Demantra Demand Management: Demand management helps in understanding historical demand and predicting the future demand. Demand management cycle consists of

Collecting historical data
Generating forecast
Review and approve forecast
Publish the forecast
Analyze the forecast accuracy

Demantra Advanced Forecasting & Demand Modeling: This is extended module for demantra demand management. It supports six more forecasting methods listed below

Auto and Linear Regression (ARIX)
Auto Regressive Logistic (ARLOGISTIC)
Dual Multiplicative Linear Regression (DMULT)
Multiplicative Monte Carlo Regression for Intermittent
Multiplicative Monte Carlo Regression for Logistic (LOGISTIC)
Multiplicative Monte Carlo Regression for Modified Ridge Regression (MRIDGE)

Demantra Real-Time Sales & Operations Planning: Sales and Operations Planning brings different functions to collaborate in business demand and supply and operational planning. It consists of four critical steps

Demand Review
Supply Review
Finance Review
Executive Review

2010-11-16T13:34:00.003+05:30

As you must be knowing, to create a refund in AR, you have to create a Receivable Activity. It is the GL account provided in this receivable activity which is used for refund accounting. Following are the accounting entries for refund in R12:

1) Unapply receipt from Invoice:
Receivables Dr
Unapplied Receipt Cr

2) Apply the receipt to Refund
Unapplied Receipt Dr
Refund Control A/c (Provided in receivable activity) Cr

3) Automatic creation of Invoice in Payables
Refund Control A/c Dr
Supplier Liability Cr

4) Payment of Invoice in Payables
Supplier Liability Dr
Bank Cr

At this point the AR Invoice is still open. The same can either be adjusted or set of against a credit memo, as when a CM is created from OM.

2010-11-12T16:31:00.005+05:30

Follow ths blog...

http://arunrathod.blogspot.com/

2010-11-12T16:28:00.002+05:30

Self-Assed Tax :
Self Assessed tax differs from regular taxes in one way: as a purchaser, you are responsible for reporting and paying the tax and the supplier is not.
This was also known as USE TAX in previous releases.
For example: You receive an invoice for $1000 which is due to be paid to the supplier. Tax A for 10% was not charged on the invoice however, as the purchaser, you recognize that you are responsible to pay tax A. You would self-assess Tax A for the $100 and include it in your filings to the corresponding tax authority.

Features:
•The flexibility to have self assessed tax automatically assessed (based on tax setup) or to manually mark the calculated tax as self assessed during invoice entry.
•An ability to have recoverable and non-recoverable portions of self assessed tax amounts based on your invoice details.
•The ability to report and account detailed recoverable and non-recoverable self assessed tax AND the corresponding Self Assessed Tax Liabilities when the transaction is accounted.

Benefits:
Improved Fiscal Discipline
•Automatic reporting and accrual helps maintain an audit trail for the tax amounts and the invoices they tie to.
•Separate liability accounts for self assessed taxes translate to more granular and accurate accounting.
Improved Operational Excellence
•By automating previously manual processes and providing functionality available during invoice entry, the propensity for human error or delayed information is reduced.

Self Assessed Tax Predetermined Process:
•During Invoice Entry, Validation, and Import, Payables gathers information known as “tax drivers’ entered on the invoice header and lines and passes that information to the new E-Business Tax module.
•Based on these tax drivers and additional information derived by E-Business Tax such as the supplier’s party tax profile and buyer’s and supplier’s tax registrations, the engine determines if any self assessed tax is applicable to the invoice.
•The self assessed tax will be passed back to Payables along with the recoverable and non-recoverable tax amounts and the General Ledger Accounts for the recoverable tax and self assessed liability.
•Payables displays the self assessed tax amount in a column on the Invoice header in the Invoice Workbench. Payables will also derive the accounts for the non-recoverable portion of the self assessed tax then store all accounts to be used later when the invoice is accounted.
•When the Invoice is Accounted, the self assessed tax and corresponding self assessed tax liabilities will be accounted along with the rest of the invoice.

Self Assessed Tax Manual Determination Process:
•This slide illustrates the process for an invoice where the calculated tax returned by the E-Business Tax engine is expected to be paid to the supplier and the Payables’ user updates the it as Self Assessed instead.
•Just like the first example, Payables gathers tax drivers entered on the invoice header and lines and passes that information to the E-Business Tax module.
•Based on these tax drivers and additional information derived by E-Business Tax, the engine calculates the tax that is expected to be paid to the supplier (in other words: Non-self assessed taxes)

Self Assessed Tax: Predetermined Set Up – First Party, Party Tax Profile:To enable the application to automatically assess self assessed taxes for the First Party or for certain Third Party Suppliers, you need to first set up the Party Tax Profile. The Party Tax Profile is party specific, tax related information that can be associated to 1st and 3rd parties. It includes information such as defaults, tax registrations, classifications and tax reporting codes.
Using the Tax Managers responsibility, navigate to the Parties, Party Tax Profiles page. Search based on the Party Type of First Party Legal Establishment and the desired party name.
You have the flexibility to configure First Party Establishments for Self Assessed Taxes at the following levels based on your needs:
•Registration, Regime
•Registration, Regime, Tax
•Registration, Regime, Tax, Tax Jurisdiction
•From the Tax Summary Window, the Payables user marks the tax as self assessed. E-Business Tax updates their records and returns the tax details to Payables.
•Payables stores the GL Accounts, displays the self assessed tax amount in a column on the Invoice header and the validated invoice is ready for accounting.

Self Assessed Tax: Predetermined Set Up – First Party, Party Tax Profile:

•Enable Self Assessment on the Party Tax Profiles tab
•By checking the Set for Self Assessment/Reverse Charge option at a particular level, E-Business Tax returns applicable taxes for supplier invoices that fall within the level
–For example, if you enable this option at the Registration – Regime level, all invoices to be taxed within that regime will be considered Self Assessed tax

You can also set up a particular supplier’s Party Tax Profile by doing either of the following:
•Use the Tax Managers responsibility to query a Third Party
•Navigate to the Tax Details page from the Supplier (Entry) pages from the Payables Responsibility

Implementation Considerations:
E-Business Tax is a common module available with Oracle Financial Applications.
•The E-Business Tax engine is responsible for calculating tax amounts applicable to invoices.
•It also assists in automatically identifying taxes as self assessed and allows setting options for manual determination.
Subledger Accounting is also a common module available with Oracle Financial Applications.
•Subledger Accounting is not specific to this feature but a general tool to configure accounting entries and to provide accounting reports to meet your needs.

2010-11-12T16:17:00.003+05:30

Oracle Projects- Accounts Payables Integration

PA - AP Flow:
Here iam considering the AP invoice integration with Oracle Projects. I will explain about the Expense Reports flow in another topic.
This integration involves 2 sub processes given below:

Interfacing Supplier Invoice From Oracle Payables To Oracle Projects
Interfacing Supplier Invoice Adjustments From Oracle Projects To Oracle Payables

Interfacing Supplier Invoice From Oracle Payables To Oracle Projects:

This process involves :

Creating the invoice.
Approve the invoice.
Account for the invoice in payables.
Run the 'Interface Supplier Costs' process in Oracle Projects which interfaces the invoices from AP to PA.

Interfacing Supplier Invoice Adjustments From Oracle Projects To Oracle Payables:
This process involves:

Adjust the supplier invoice interfaced to Oracle Projects by either splitting the quantity or transferring an invoice from one project/task to another.
Execute the PRC: Distribute Supplier Invoice Adjustment Costs process in Oracle Projects.
Run the PRC: Interface Supplier Invoice Adjustment Costs to Payables process in Oracle Projects.

Important Project Related Fields in AP:

Project Name - Project Name to which the Invoice is accounted in Payables.
Task Number - Task Number of the Project to which the invoice is accounted. Note: If the Task is not chargeable, the system displays the following error message: APP-PA-19270 The Task is Not Chargeable. The same error will be received if the Chargeable Flag is not checked while defining Tasks in Projects Setup or if the task is a Parent Task. Expenditures cannot be created at the Parent Task level.
Expenditure Types - Expenditure Type of the invoice. This is based on the projects expenditure types (pa_expenditure_types table)
Expenditure Item Date - The date of the invoice expenditure item to be created in Projects.
Expenditure Organization - Active Project Expenditure/Event Organization against which the invoice has to be mapped.
The Expenditure Org can be find in PA_ALL_Organizations table with pa_org_use_type = 'EXPENDITURES'. For any organization to be a Expenditure Organization, it has to be classified in HR as 'Expenditure/Event Organization'.
Quantity - Based on the Expenditure Type definition, quantity is verified by checking the PA_QUANTITY column in the AP_INVOICE_DISTRIBUTIONS_ALL. If the COST_RATE_FLAG column in PA_EXPENDITURE_TYPES table is set to Y, then the quantity field in the Payables Invoice Workbench needs to be filled in.

Once all the requisite information has been entered and the invoice distribution saved, the system checks whether the values given in the Project, Task, Expenditure Type and Expenditure Organization fields are active as of the Expenditure Item Date.
The following columns in the AP_INVOICE_DISTRIBUTIONS_ALL table are relevant for project-related supplier invoices:

PROJECT_ACCOUNTING_CONTEXT - This column is set to Yes if the Project ID column is filled.

ASSET_ADDITION_FLAG - If the project-related invoice distribution is charged to a Capital Project, then the ASSET_ADDITION_FLAG is set to P when the PA_ADDITION_FLAG is set to Y, Z or T.

To avoid the same invoice distribution being interfaced to both Projects and Fixed Assets, you must interface any project-related invoice distribution to Oracle Projects before you interface it to Oracle Assets.

PA_ADDITION_FLAG - The PA_ADDITION_FLAG tracks the status of project-related supplier invoice distribution lines and expense report distribution lines. For supplier invoice distributions entered via Oracle Payables, the PA_ADDITION_FLAG is set to N if the distribution is project-related, otherwise it is set to E and it is updated by Oracle Projects when the distribution is processed by the Oracle Projects Interface Supplier Invoice process. Oracle Projects sets the PA_ADDITION_FLAG to Y or Z after the item is successfully processed, or may be set to a rejection code if the line is rejected during transfer to Projects. For supplier invoice adjustment lines interfaced from Projects to Payables (which must net to zero with another line), the value for the PA_ADDITION_FLAG is set to T. Listed below are the Quick Codes available for the PA_ADDITION_FLAG:

If an item is rejected, you must correct the rejection reason and re-run the interface process.

Once the Invoice is interfaced to Projects, the following tables are populated with appropriate values:

PA_EXPENDITURE_GROUPS_ALL
PA_EXPEDITURES_ALL
PA_EXPENDITURE_ITEMS_ALL
PA_COST_DISTRIBUTION_LINES_ALL

The interrelationship between the relevant Payables and Projects tables are as follows:

AP_INVOICES_ALL	PA_EXPENDITURES_ALL	PA_COST_DISTRIBUTION_LINES_ALL
INVOICE_NUM	ORIG_USER_EXP_TXN_REFERENCE
INVOICE_ID	ORIG_EXP_TXN_REFERENCE1	SYSTEM_REFERENCE2
VENDOR_ID	VENDOR_ID	SYSTEM_REFERENCE1
ORG_ID	ORG_ID	ORG_ID

AP_INVOICE_DISTRIBUTIONS_ALL	PA_EXPENDITURE_ITEMS_ALL	PA_COST_DISTRIBUTION_LINES_ALL
DISTRIBUTION_LINE_NUMBER		SYSTEM_REFERENCE3
DIST_CODE_COMBINATION_ID		DR_CODE_COMBINATION_ID
INVOICE_ID		SYSTEM_REFERENCE2
EXPENDITURE_TYPE	EXPENDITURE_TYPE
EXPENDITURE_ORGANIZATION_ID	EXPENDITURE_ORGANIZATION_ID
PROJECT_ID	PROJECT_ID	PROJECT_ID
TASK_ID	TASK_ID	TASK_ID
EXPENDITURE_ITEM_DATE	EXPENDITURE_ITEM_DATE	GL_DATE
ORG_ID	ORG_ID	ORG_ID
PA_QUANTITY	QUANTITY	QUANTITY
AMOUNT	RAW_COST	AMOUNT

It is possible to map between the TRANSFER_STATUS_CODE column in the PA_COST_DISTRIBUTION_LINES_ALL table and the
PA_ADDITION_FLAG column in the AP_INVOICE_DISTRIBUTIONS_ALL table. TRANSFER_STATUS_CODEs available in the PA_COST_DISTRIBUTION_LINES_ALL table for supplier invoices are:

V- Interface from Oracle Payables - Upon creation set to Received
P - Pending - Upon execution of Distribute Cost Process after Supplier Invoice Adjustments
G - If payables rules do not allow adjustments to the invoice (example: if the invoice is cancelled), then distribute supplier invoice adjustments program would create the CDL and set the value to (G), these lines will always reside in Oracle Projects and will not be transferred to Oracle Payables
A - Transfer Costs - If successfully transferred to Oracle Payables, set to Accepted
X - Rejected in transfer to Oracle Payables, set to Rejected in Transfer