Hierarchial query example in oracle or CONNECT BY PRIOR or LEVEL or Start with

http://welcometooracle.wordpress.com/2012/10/18/hierarchical/

How to set column width (size) in sqlplus

Aging Reports in Oracle Apps Receivables - Quick Overview (Aging Buckets in Oracle Receivables)

Overview

Aging reports are used to review information about your open items so as to know how much funds have been held up and with whom. Aging reports display the customers who have outstanding balances and the amount each customer owes us which also helps to expedite the collection process.  Receivables aging reports Do Not include customers with a zero outstanding balance.

These reports can print both detail and summary information about your customer’s current and past due invoices, debit memos, and charge backs.  Receivables also gives the option to see credit memos, on–account credits, unidentified payments, and on–account and unapplied cash amounts.

As mentioned above, Oracle Receivables ages the transactions according to due date. The aging reports however select transactions according to gl date. 

Receivables aging reports includes all open items whose gl_date is before or the same as the “as of date” entered, and whose gl_date_closed is after the “as of date”. The default value for “as of date” is the current date.

 Categories of Aging Reports 

Receivables aging reports are categorized as follows:

• Aging – By Account
• Aging – By Amount
• Aging – By Collector
• Aging – By Salesperson 

 Report Parameters for Aging Reports 

Aging Bucket Name:  

Specify the bucket set from which the report information needs to be printed. The default bucket set is 'Standard'.

As of Date:

Specify the date as of which the transactions need to be aged. Receivables includes all open items whose GL date is before or the same as this date. The default is the current date.

Order By:

The option you want Receivables to use to sort your information. For example, you can sort by:

·        Customer Name (Aging - 4 and 7 Bucket reports)

·         Transaction Type (Aging - 4 and 7 Bucket reports)

·        Balance Due (only for 7 Buckets - By Amount report)

·        Salesperson (only for 7 Buckets - By Salesperson report)

Report Format:

The “Brief” format prints customer name and customer number with item information while the “Detailed” format prints address and contact address as well.

Report Summary:

The “Invoice Summary” option prints information on all customers' debit items.

The “Customer Summary” option prints customers' names with their total debit item balances.

Show on Account:

Specify whether to print credit items for your customers.

·        Do Not Show: Receivables does not display any of your identified or unidentified payments, or on-account credit memos.

·        Age: Receivables ages your credit items and includes the credit amounts in the appropriate aging bucket columns.

·        Summarize: Receivables displays the sum of your credit items in the Customer Credit Memos, Customer Payments, and the Customer Balance rows. This is the default option.

Show Receipts at Risk:

Receipts at Risk are receipts that have either not been cleared or factored receipts that have not been risk eliminated. Select one of the following values for your report:

·        Age:- Include receipts at risk in this report. Receivables displays the receipts at risk with other open receipts in the appropriate bucket and includes them when determining the customer's balance.

·        Summarize:- Receivables displays the sum of your receipts at risk in the Customer Credit Memos, Customer Payments, and the Customer Balance rows.

Do Not Show :- Receipts at risk will not be included in this report. This value is used as the default.

MY COLLEAGUES

PL/SQL EXCEPTIONS

PL/SQL EXCEPTIONS : WHEN NO_DATA_FOUND

EXAMPLES :

  1  DECLARE

  2    V_NUMBER NUMBER(4);

  3    V_NAME VARCHAR2(20);

  4    V_SAL   NUMBER(5);

  5  BEGIN

  6    SELECT EMPNO,ENAME,SAL INTO V_NUMBER,V_NAME,V_SAL FROM EMP WHERE EMPNO = 7839;

  7  DBMS_OUTPUT.PUT_LINE(V_NUMBER ||  V_NAME ||   V_SAL);

  8  EXCEPTION

  9  WHEN NO_DATA_FOUND

 10  THEN

 11  DBMS_OUTPUT.PUT_LINE('NO EMPLOYEE EXISTS IN THIS NAME');

 12* END;

SQL> /

7839 KING 5000

PL/SQL procedure successfully completed.

(GIVE WRONG EMPLOYEE NUMBER THEN YOU WILL GET THIS ERROR);

  1  DECLARE

  2    V_NUMBER NUMBER(4);

  3    V_NAME VARCHAR2(20);

  4    V_SAL   NUMBER(5);

  5  BEGIN

  6    SELECT EMPNO,ENAME,SAL INTO V_NUMBER,V_NAME,V_SAL FROM EMP WHERE EMPNO = 333;

  7  DBMS_OUTPUT.PUT_LINE(V_NUMBER ||  V_NAME ||   V_SAL);

  8  EXCEPTION

  9  WHEN NO_DATA_FOUND

 10  THEN

 11  DBMS_OUTPUT.PUT_LINE('NO EMPLOYEE EXISTS IN THIS NAME');

 12* END;

SQL> /

NO EMPLOYEE EXISTS IN THIS NAME

PL/SQL procedure successfully completed.

TOO_MANY_ROWS EXCEPTIONS

EXAMPLE:

DECLARE

   V_NUMBER NUMBER(4);

BEGIN

   SELECT EMPNO INTO V_NUMBER FROM EMP E;

DBMS_OUTPUT.PUT_LINE(V_NUMBER);

EXCEPTION

WHEN TOO_MANY_ROWS

THEN

DBMS_OUTPUT.PUT_LINE('THERE ARE TOO MANY ROWS   RETRIVING');

END;

/

To show the top 4 employees salary information from emp table

To find out highest salary of Three Persons in Organization(N th) for Oracle SQL Query :
or We want fin out Top 2 highest salary in company :
Example Query : To show the top 4 employees salary information from emp table

SELECT ENAME, EMPNO,SAL FROM  (SELECT ENAME,EMPNO,SAL FROM EMP  ORDER BY SAL DESC) WHERE ROWNUM <5;

Find component Version in Apps 11i/R12/12i

 

Find component Version in Apps 11i/R12/12i

Q. How to find Apps Version (11i/R12/12i).
A. Connect to database as user apps
SQL> select release_name from apps.fnd_product_groups;
Output like 12.0.4 or 11.5.10.2

Q. Web Server/Apache or Application Server in Apps 11i/R12
A. Log in as Application user, set environment variable and run below query $IAS_ORACLE_HOME/Apache/Apache/bin/httpd -version
Output for 11i should be like
Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built:   Jan 26 2005 11:06:44 (iAS 1.0.2.2.2 rollup 5)
Output for R12 should be like
Server version: Oracle-Application-Server-10g/10.1.3.0.0Oracle-HTTP-Server
Server built:   Dec  4 2006 14:44:38

Q. Forms & Report version (aka developer 6i) in 11i
A. Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/f60run | grep Version | grep Forms
output like
Forms 6.0 (Forms Runtime) Version 6.0.8.25.2 (Production)
Check fourth character in version 25 which means Forms 6i patchset 16 (25-9)
.
Q. Forms & Report version in R12/12i
A. Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/rwrun | grep Release
Output should be like
Report Builder: Release 10.1.2.2.0
You can safely ignore warnings.

Q. Database Version in 11i/R12/12i
A. Go to database section below.

Q. Oracle Jinitiator in 11i/R12/12i
A. Log in as Application user, set environment variable and run below query
grep jinit_ver_comma $CONTEXT_FILE

(Default is Java Plug-In for R12/12i )
Q. Oracle Java Plug-in in 11i/R12/12i
A. Log in as Application user, set environment variable and run below query
grep plugin $CONTEXT_FILE.

Q. File Version on file system
adident Header
or
strings | grep Header
Here adident is AD Utility (Oracle Apps) and strings is Unix utility.

Q. Version of pld file
*.pld are source code of *.pll which are inturn source of *.plx.  *.pll is in $AU_TOP/resource and to find its version check
adident Header $AU_TOP/resource/.pll
IGSAU012.pll:
$Header IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $
or
strings $AU_TOP/resource/.pll | grep -i header
FDRCSID(’$Header: IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $’);

Q. OA Framework Version
A.http:// hostname.domainName:port/OA_HTML/OAInfo.jsp (Only for 11i)
A. Log in as Application user, set environment variable and run below query
adident Header $FND_TOP/html/OA.jsp
adident Header $OA_HTML/OA.jsp
output for both should look like
$Header OA.jsp 115.60 2006/03/31 00:47:28 atgops1 noship $
120.21 means OA Framework Version (coming soon..)
115.60 means OA Framework Version (coming soon..)
115.56 means OA Framework Version (coming soon..)
115.36 means OA Framework Version 5.7
115.27 means OA Framework Version 5.6E
115.26 means OA Framework Version 5.5.2E

Q. Discoverer Version for 11i (3i or 4i)
A. Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/disc4ws | grep -i Version

Q. Discoverer Version for 11i or R12 (10g AS)
Check under Application Server Section as 10g AS Discoverer is on standalone

Q. Workflow Version with Apps
A. Connect to Database as apps user
SQL> select TEXT Version from   WF_RESOURCES where  NAME = ‘WF_VERSION’;
Output like 2.6.0 means workflow version 2.6.0  Version for Fusion Middleware Component

Identity Management component Version/Release Number

A. Oracle Single Sign On
Connect to database which holds SSO repository
SQL>select version from orasso.wwc_version$;

B. Oracle Internet Directory
There are two component in OID (Software/binaries & Schema/database)
>>> To find software/binary version
$ORACLE_HOME/bin/oidldapd -version
output should look like
oidldapd: Release 10.1.4.0.1 – Production on mon jul 14 14:14:21 2008
Copyright (c) 1982, 2006 Oracle.  All rights reserved.
>>> To find Schema Version/ database use
ldapsearch -h -p -D “cn=orcladmin” -w “” -b “” \
-s base “objectclass=*” orcldirectoryversion
and output should be like
version: 1
dn:
orcldirectoryversion: OID 10.1.4.0.1
or run following query in database
SQL> select attrval from ods.ds_attrstore where entryid = 1 and attrname = ‘orcldirectoryversion’;
Output should be like OID 10.1.4.0.1

C. Application Server
1. Oracle Application Server 10g Rel 3 (10.1.3.X)
cat $ORACLE_HOME/config/ias.properties | grep Version
Version=10.1.3.0.0
2. For Oracle Application Server 10.1.2 (Prior to Oracle WebLogic Server)
If application server is registered in database (Portal, Discoverer) check from database
SQL> select * from ias_versions;
or
SQL>select * from INTERNET_APPSERVER_REGISTRY.SCHEMA_VERSIONS;
.
D. AOC4J (Oracle Container for J2EE)
Set ORACLE_HOME
cd $ORACLE_HOME/j2ee/home
java -jar oc4j.jar -version
.
E. Oracle Portal
SQL> select version from portal.wwc_version$;
.
Database Component
I) Oracle Database
To find database version
SQL> select * from v$version;
or
All component version in database
$ORACLE_HOME/OPatch/opatch lsinventory -detail

.Unix Operating System

Solaris -> cat /etc/release
Red Hat Linux -> cat /etc/redhat-release

1.Q: How to find Oracle Applications Web Server Version ?
Or
How to find Version of Apache used with oracle apps 11i ?
Ans: Log to Application tier as Operating System Usually called as APPLMGR ; go to location $IAS_ORACLE_HOME/Apache/Apache/bin and run command ./httpd -version
You will see output like:
Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built: Dec 6 2005 14:59:13 (iAS 1.0.2.2.2 rollup 5)
Which means you are on iAS Version 1.0.2.2.2 with patchset rollup 5 with Apache Version 1.3.19
Server version: Oracle-Application-Server-10g/10.1.2.0.2 Oracle-HTTP-Server
Above is output If you have installed 10g Application Server with 11i
2.Q: How to find Jinitiator Version ?
Ans:Check for file like appsweb_SID_HOSTNAME.cfg under $OA_HTML/bin
defined by environment variable FORMS60_WEB_CONFIG_FILE and
search for entry like jinit_ver_name , you will see entry like
jinit_ver_name=Version=1,3,1,23
which means Jinitiator version is 1.3.1.23 ;
if your version is 1.3.1.18 you will see entry like 1,3,1,18
3.Q: How to find Forms Version in 11i from front end?
Login to forms from frontend , on top menu bar of forms click on \”Help\”
and Select \”About Oracle Applications\” go to \”Forms Server \” section.
You should see entry like below depending on your forms version
Oracle Forms Version : 6.0.8.26.0
Which mean you are on forms version 6.0.8.26 .
If you want to know whats your forms patchset level then subtract 9 from fourth digit,
which means for above case form patchset 17 is applied.
4.Q: How to find Forms Version in Apps from command Line ?
Ans:Enter \”f60gen\” on Forms Server and check for first line in output like
Forms 6.0 (Form Compiler) Version 6.0.8.26.0 (Production)
This confirms that you are on forms server version 6.0.8.26.0 and patch set 17. ( Patch Set = Fourth Digit – 9)
5.Q: How to find Version of any file in Oracle Apps 11i ? or
Q: How to find any Reports Version 11i ?
Ans:In Oracle Applications under ad utilities there is utility called as adident,
Used for Identification purpose.
to find out file version use:
adident Header
for ex. inorder to to find file version of one AR form i.e. ARXGLCOR.fmx
adident Header ARXGLCOR.fmx
You should see output like
$Header APPSTAND.fmb 115.33 2002/04/04 11:13:40 pkm ship
$ $Header ARXGLCOR.fmb 115.15 2005/01/31 13:48 mraymond ship
Which means above form executable consist of two forms whose version is 115.33 & 115.15 resp.
Similarly you can use adident to find version of any report in 11i.
6.Q: Can I run 64 bit application on 32 bit Operating system ?
Ans:You can run 32 bit application (like oracle application server, web server,
all oracle application server are 32 bit ) on both 32 /64 bit operating system
but a 64 bit application like 64 bit database can run only on 64 bit operating system.
7.Q How to find if your database is 32 bit or 64 bit(Useful in applying Patches) ?
Ans:execute \”file $ORACLE_HOME/bin/oracle\” ,
you should see output like
/u01/db/bin/oracle: ELF 64-bit MSB executable SPARCV9 Version 1
which means you are on 64 bit oracle
If your oracle is 32 bit you should see output like
oracle: ELF 32-bit MSB executable SPARC Version 1
Now you know what should be bit of patch to download
8.Q: How to find OUI version ?
OUI stands for Oracle Universal Installer.
In order to find Installer version you have to execute
./runInstaller -help ( From OUI location)
You will get output like
Oracle Universal Installer, Version 10.1.0.4.0 Production Copyright (C) 1999, 2005, Oracle. All rights reserved.
That means OUI version in above case is 10.1.0.4
OUI location is
$ORACLE_HOME/oui/bin
9.Q: How to find Database version ?
Ans:SQL> select * from v$version;
The command returns the release information, such as the following:
Oracle9i Enterprise Edition Release 9.2.0.7.0 – Production
PL/SQL Release 9.2.0.7.0 – Production
CORE 9.2.0.7.0 Production
TNS for 32-bit Windows: Version 9.2.0.7.0 – Production
NLSRTL Version 9.2.0.7.0 – Production
10.Q: How to find Oracle Workflow Cartridge Release Version ?
Ans:Log in to the database as the owf_mgr user and issue
select wf_core.translate(\’WF_VERSION\’) from dual;
11.Q: How to find opatch Version ?
Ans:opatch is utility to apply database patch , In order to find opatch version execute
\”$ORACLE_HOME/OPatch/opatch version\”
You should see output like OPatch Version: 1.0.0.0.52
which means your opatch version is 1.0.0.0.52
12.Q How to Discoverer Version installed with Apps ?
Ans:Discoverer with Apps installed in ORACLE_HOME same as 806 is usually 3i or 4i.
To find Version login to Application Tier & go to $ORACLE_HOME/discwb4/bin and execute
strings dis4ws | grep -i \’discoverer version\’
You should see output like
Discoverer Version:Session 4.1.47.09.00
Which means you are on discoverer 4i version 4.1.47.09
13. Q. How to find Version of Apps 11i ?
Ans:Run following SQL from apps user ;
SQL> select RELEASE_NAME from fnd_product_groups;
You should see output like
RELEASE_NAME
———————–
11.5.10.2
Which means you are on Apps Version 11.5.10.2
14.Q. How to find Workflow Version embedded in Apps 11i ?
Ans:Run following SQL from apps user ;
SQL>select TEXT from WF_RESOURCES where NAME=\’WF_VERSION\’;
You should see output like
TEXT
———————-
2.6.0
Which means you are on Workflow Version 2.6.0
You can also use script wfver.sql in FND_TOP/sql to find version of workflow in Apps.
15.Q: How to find version of JDK Installed on Apps ?
Ans:There might be multiple JDK installed on Operating System .
Like JDK 1.3.1, 1.4.2 or 1.5 but in order to find which Version of JDK your Apps is using
Open your Context File $SID_$HOSTNAME.xml under $APPL_TOP/admin and look for variable
JDK_TOP oa_var=\”s_jdktop\” what
so ever value assigned against that parameter go to that directory & cd bin & execute command
./java -version
so lets assume entry above is /usr/jdk then cd /usr/jdk/bin & ./java -version , you will see output like
java version \”1.4.2_10\”
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_10-b03)
Java HotSpot(TM) Client VM (build 1.4.2_10-b03, mixed mode)
Which means you are using JDK 1.4.2 in Oracle Applications 11i.

How to check different version in Oracle Apps R12

1) Database Version

SQL> desc v$version

 Name                                      Null?    Type

 —————————————– ——– —————————-

 BANNER                                             VARCHAR2(80)

SQL> select banner from v$version;

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production

PL/SQL Release 11.1.0.7.0 – Production

CORE    11.1.0.7.0      Production

TNS for Linux: Version 11.1.0.7.0 – Production

NLSRTL Version 11.1.0.7.0 – Production

2) Application Release.

SQL> select release_name from apps.fnd_product_groups;

RELEASE_NAME

————————————————–

12.1.1

3)  Web Server/Apache or Application Server in Apps 11i/R12

A. Log in as Application user, set environment variable and run below query$IAS_ORACLE_HOME/Apache/Apache/bin/httpd –version

[appl201@prdcl201 bin]$ httpd -version

Server version: Oracle-Application-Server-10g/10.1.3.4.0 Oracle-HTTP-Server

Server built:   Jul  7 2008 14:58:00

[appl201@prdcl201 bin]$ pwd

/patches/applprod/apps/tech_st/10.1.3/Apache/Apache/bin

4A)  Forms & Report version (aka developer 6i) in 11i
A. Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/f60run | grep Version | grep Forms
output like
Forms 6.0 (Forms Runtime) Version 6.0.8.25.2 (Production)
Check fourth character in version 25 which means Forms 6i patchset 16 (25-9)
.
4B)  Forms & Report version in R12

A. Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/rwrun | grep Release
Output should be like

[appl201@prdcl201 bin]$  rwrun | grep Release

REP-0503: You did not specify the name of a report.

Report Builder: Release 10.1.2.3.0 – Production on Tue Jul 19 10:36:21 2011

[appl201@prdcl201 bin]$ pwd

/patches/applprod/apps/tech_st/10.1.2/bin

5) OracleJinitiator in11i/R12/12i
A. Log in as Application user, set environment variable and run below query
[appl201@prdcl201 bin]$ grep jinit_ver_comma $CONTEXT_FILE

         <jinit_ver_comma oa_var=”s_jinit_ver_comma”>1.6.0_07</jinit_ver_comma>

6) RED HAT RELEASE

[appl201@prdcl201 bin]$ cat /etc/redhat-release

Red Hat Enterprise Linux Server release 5.4 (Tikanga)

7) JAVA PLUGIN

[appl201@prdcl201 bin]$ grep plugin $CONTEXT_FILE

         <!– JDK plugins –>

         <sun_plugin_ver oa_var=”s_sun_plugin_ver”>1.6.0_07</sun_plugin_ver>

         <sun_plugin_type oa_var=”s_sun_plugin_type”>jdk</sun_plugin_type>

8) APPLICATION SERVER VERSION

[appl201@prdcl201 12.0.0]$ cat $ORACLE_HOME/config/ias.properties | grep Version

Version=10.1.2.3.0

9) OC4J(Oracle Container for J2EE)

[appl201@prdcl201 home]$ java -jar oc4j.jar -version

Oracle Application Server Containers for J2EE 10g (10.1.2.3.0) (build 080228.2305.2133)

[appl201@prdcl201 home]$ pwd

/patches/applprod/apps/tech_st/10.1.2/j2ee/home

Simple Accounting Entries in Payables

  At the time of invoice (For inventory item)

Inventory AP accrual a/c Dr
To AP liability a/c Cr
At the time of invoice (For Expense item) Charge a/c Dr
To AP liability a/c Cr
At the of Payment AP liability a/c Dr
To Cash Clearing a/c
Prepayment Entry Prepaid a/c Dr
To AP liability a/c Cr
AP liability a/c Dr
To Cash Clearing a/c Cr
Cash Clearing a/c Dr
To Cash or bank a/c Cr
Adjustment of prepayment against Invoice AP liability a/c Dr
To prepayment a/c Cr
Credit memo or debit memo entry AP liability a/c Dr
To charge a/c or Inv AP accrual a/c
With holding Tax Entry AP liability a/c Dr
To Withholding Tax a/c Cr
Releasing With holding Tax Withholding Tax a/c Dr
To AP Liability a/c
Purchase price variance Entry Material a/c Dr
To Receiving a/c Cr
To PO price Variance a/c Cr
If standard price is less than PO price
Material a/c Dr
To PO price variance a/c Cr
To Receiving a/c Cr
Currency Gain Entry AP liability a/c Dr
To realized gain a/c Cr
To Cash a/c Cr

Currency LossAP liability a/c Dr
Realized Loss a/c Dr
To Cash a/c Cr
For future dated Payment AP liability a/c Dr
To Future dated payment a/c Cr
At payment Maturity Future Payment a/c Dr
To cash a/c Cr
Discount takenAP Liability a/c Dr
To System discount a/c Cr
To cash a/c Cr
Cross currency Entry Charge a/c Dr
To Liability a/c Cr
Cross currency Payment time Entry Liability a/c Dr
Rounding a/c Dr
To cash clearing a/c  

Module wise Functional Changes from in R12 & 11i

 Inventory 1- Deferred Cost of Goods Sold (COGS) Recognition
    

Starting in this release, Oracle Inventory can defer the recognition of COGS until all

contract contingencies are filled and Receivables has recognized the revenue. Oracle

Inventory holds incurred costs in a deferred COGS account until Receivables recognizes

it per the revenue recognition rules. This enables you to recognize both COGS and

revenue in the same accounting period. The new accounting rules also support

customer returns.

All sales order issue transactions are debited to the deferred COGS account except:

• Internal sales orders: For internal sales orders, the COGS account is debited directly

when the new accounting rule is not enabled.

• Intercompany transactions: For all intercompany transactions (external drop

shipments, internal drop shipments, and non-ship flows) COGS is debited directly

if the new accounting rule is not enabled.

Oracle Cost Management moves incurred costs from the deferred COGS to the COGS

account based on the revenue recognition events or order close events. The remaining
2- Material Workbench

In Release 11.5.10, Oracle Inventory introduced the option to use the Material

Workbench to view material that resides in receiving in addition to on-hand material. In

this release, you can use the Material Workbench to view detailed information about

material that resides in receiving, in-transit material, and on hand material. When you

choose to view in-transit material, you can view the following document types:

• Purchase orders

• Advanced shipment notices (ASNs)

• Internal Orders

Oracle Inventory calculates availability information according to the material location of

the relevant material. Instead of viewing on hand material, material in receiving, and

in-transit material through separate queries, you can perform one query that displays

an item across different material locations. You can also perform a query that displays

item information across organizations. This provides you with access to a global picture

of inventory for the item, and allows you to make quick decisions regarding item

sourcing and procurement.

3- Picking Rule Enhancements

In this release, the picking rule window enables you to capture individual customer

product quality and material characteristic preferences. For example, one customer may

require premium grade material, while another more price-sensitive client may not

have that restriction. To manage customer preferences, restrictions were added to the

picking rules engine allocation logic in addition to the existing sort criteria for

acceptable material. In this release, the Inventory Picking Rules window enables you to

create picking rules without installing Oracle Warehouse Management.

These rules are a subset of Oracle Warehouse Management rules and have the following

usage and restrictions:

• Allocate based on first in first out (FIFO) or first expired first out (FEFO)

• Ensure only one lot is allocated, or allow multiple lot allocation

• Restrict allocation by shelf life days

• Allow partial allocation, or ensure full allocation

• Specify matching based on item quality data

• Allocate lots in lot number sequence, or no sequence

• Allocate revisions by revision, effective date, or no sequence

• Allocate by sub-inventory, receipt date, or no sequence

• Allocate by locator, receipt date, or no sequence

• Allocate by preferred grade

• Ensure lots of indivisible items are fully consumed

• Allow over allocation

After you create the picking rules, you can use the Rules Workbench page to assign

picking rules in the following combinations:

• Item

• Item category

• Customer

• Source type

• Transaction type

When you enable a rule the system builds a rules package. After the system builds a

rules package, it creates an enabled strategy with the same name and description. If you

disable the rule, then the system automatically disables the strategy. You can only

disable rules that are not used in any disabled strategy assignments. You can also only

modify disabled rules.

Note:

The Rules Workbench available in an inventory-only

organization does not have the full capabilities of the Oracle

Warehouse Management Rules Workbench.

4- Enhanced Reservations

You can create linkages between supply and demand to guarantee material availability.

These linkages are known as reservations. A reservation guarantees the availability of

reserved supply to a specific demand. In previous releases, reservations supported

limited supply and demand types.

In this release, Oracle Inventory introduced the following new supply and demand

types:

Supply types

• Purchase orders

• Internal requisitions

• Discrete jobs

• Process manufacturing batches

• Shop floor jobs

Demand types

• Components for Complex Maintenance Repair and Overhaul work orders

• Components for process manufacturing batches

Reservations supports document validation, availability checks, and change

management for the new supply and demand types. In this release, reservations also

supports crossdocking in the warehouse, and enables you to reserve the most

appropriate inbound receipts for an outbound shipment. The crossdock attribute was

added to the Item Reservations window to link supply to demand. The system creates

crossdock reservations automatically. You cannot delete a crossdock reservation if the

supply type is receiving.

In this release, you can reserve a specific serial number and Oracle Inventory ensures

the system allocates the serial number at pick release. A new Serial Entry window was

added to enable you to reserve multiple serial numbers for a reservation. If you reserve

serial numbers, pick release allocates the serials irrespective of the picking rules. Pick

release allocates the reserved serials first and honors the organization parameter

Allocate Serial Numbers for the remaining demand. Oracle Inventory also allows you to

substitute serial numbers during picking and shipping. If you choose to substitute a

serial number, then the system deletes the reservation for the substituted serial number.

5- Demand Fulfillment Lead Time

Demand fulfillment lead time is the time between order placement and order

fulfillment. You usually set it either to the time allowed by the customer or based on

business practice. You can express a customer service level target in terms of a demand

fulfillment lead time. For example, you can set a 95% service level with a three-day

demand fulfillment lead time.

In previous releases, Oracle Inventory Optimization assumed that the lead time was

zero. And, service levels were specified in different places - item-specific service levels

as a flex field for the item, customer-specific service levels as a flex field for the

customer, and demand class-specific service levels when associating a demand class

with allocation rules.

In this release, service level and demand fulfillment lead time can be specified in one

place as part of a service level set at the following levels: Item - Organization - Demand

class, Item - Demand class, Item - Item category - Demand class, Item - Organization,

Category, Demand class, Customer site, Customer, Organization - Demand class, and

Organization.

You can enter lead time in days as a fractional number. For example, a 4-hour lead time

as 0.167 (4 hours / 24 hours).
6- Lead Time Variability

In previous releases, Inventory Optimization did not consider the variability of lead

times when it calculated safety stock levels. In this release, it calculates these variability

measures from lead times when it calculates safety stock levels:

•

Manufacturing lead-time variability: A standard deviation value that the planning

engine applies to the item processing lead time. You enter item processing lead time

in Collections Workbench form, Item Details window. Oracle Inventory

Optimization assumes that the statistical distribution of the manufacturing lead

time is normal.

•

In-transit lead-time variability: A standard deviation value that the planning

engine takes against the ship method transit time. You enter ship method transit

time in the Transit Times form. Oracle Inventory Optimization assumes that the

statistical distribution of the in-transit lead time is normal.

•

Purchasing lead-time variability: A standard deviation value that the planning

engine takes against the supplier processing lead time. If entered, Oracle Inventory

Optimization assumes that the statistical distribution of supplier variability is

normal. As in previous releases, you can also enter this value by specifying a

histogram of purchasing lead time-probability pairs. If you enter the value using a

histogram, Oracle Inventory Optimization takes the probability distribution from

the user inputs.

Order Management

Obsolete Profile Options

Here are the profile options that are obsolete. All functionality previously provided by

these profile options is now controlled by Oracle Payments.

• OM: Estimated Authorization Validity Period

• OM: Number of Days to Backdate Bank Account Creation

• OM: Payment Method for Credit Card Transactions. Control is now available at the

Payment Type level in the Define Payment Types window.

• OM: Process Payment Immediately at Booking. Control is now available at the

Payment Type level in the Define Payment Types window.

• OM: Risk Factor Threshold for Electronic Payments

Changed Profile Options

These profile options have been converted to Oracle Order Management system

parameters:

• OM: Credit Memo Transaction Type

• OM: Credit Salesperson for Freight on Sales

• OM: Employee for Self-Service Orders

• OM: GSA Discount Violation Action

• OM: Invoice Source

• OM: Invoice Transaction Type

• OM: Non-Delivery Invoice Source

• OM: Overshipment Invoice Basis

• OM: Reservation Time Fence

• OM: Schedule Line on Hold

• OM: Show Discount Details on Invoice

• Tax: Inventory Item for Freight

• Tax: Invoice Freight as Revenue

The system parameters retain the same names as the profile options without the

prefixes (OM: or Tax:). The profile OM: Employee for Self-Service Orders is replaced by

the system parameter called Requestor for Drop Ship Orders created by external user.

These profile options were changed to system parameters to support Multiple

Organization Access Control (MOAC), which allows you to access one or more

operating units using a single responsibility. Some addition benefits include:

• Implementers need to set some application controls at an operating unit level so

that the business flows they operate on can be consistent within that operating unit.

At the same time, the application controls can be set differently for different

operating units. Delivering those controls as system parameters (which are specific

to operating units) instead of as profile options meets this need.

• After you set the values of certain key application controls during an

implementation, you need to ensure that those values are not changed later in the

implementation process. Using system parameters for these controls ensures that

implementers are appropriately warned or disallowed from making such changes.

The upgrade migrates the values of the profile options to system parameter values.

Defaulting Rules

In previous releases of Order Management, seeded defaulting rules defaulted the Order

Type and Salesrep from the Customer. These defaulting rules are deleted. You can still

default the Order Type and Salesrep values from other sources, such as the Customer

Ship-to and Customer Bill-to.

The sources Customer.Order type and Customer.Salesrep are also disabled, so all

custom defaulting rules that used these source are deleted.