Enable Oracle EBS R.12 Standard Attachments

Oracle EBS Standard Attachments 

1) Identify the form, block, table and primary key for the module where you want to setup Oracle EBS Standard Attachments feature.

2) Modify WHEN-NEW-BLOCK-INSTANCE and WHEN-NEW-RECORD-INSTANCE trigger execution hirerechy from Override to Before (IF NECESSARY).

Switch Responsibility to Application Developer

3) Setup Document Category in Attachments -> Document Categories

4) Click on Entities Enter the entity name in the Entity block

5) Setup Attachment Functions in Attachments -> Attachment Functions

• Select Form 
• Select name of the form where Attachment option need to enable. 
• Click on Categories Button
• Select the category setup in step 3

5) Click on Blocks button
Enter the block name in the Block Name block

Click include in Indicator
From the Tabs below navigate to Primary Key Fields
Enter the primary key in Key1 field with Block_name.Primary Key Field

Press Ctrl+S

Check the form Oracle Standard Attachments icon in the top bar should get enabled now.

Important table for Oracle EBS R.12 Attachments Feature:

1. FND_ATTACHED_DOCS_FORM_VL

For any queries feel free to comment.



Tags: Oracle EBS R12 Attachments feature. How to enable Oracle E-Business Suite Release12 Standard Attachments feature. Enable Attachment icon in Oracle E-Business Suite Release 12

Oracle EBS 12.1.3 Manual Clone Steps

Oracle EBS 12.1.3 Manual Clone Steps

If the Oracle Cold Back Up for Clone is generated without running adpreclone.pl script. You can get errors while performing cloning using adcfgclone.pl script. In this case you can make a manual Clone following below mentioned steps.

Extract the .tar backup file in some folder for example SEL

Create an .env file for example zahid.env like below:
vi zahid.env

export ORACLE_HOME=/erp/AFF/SEL/U01/oracle/PROD/db/tech_st/11.1.0
export ORACLE_SID=PROD
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin/PROD_erptest
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

----------------------------------------------------------
Go to: /SEL/U01/oracle/PROD/db/tech_st/11.1.0/network/admin path and run the following command:
mv prod_erpdb prod_erptest
and erptest is the current machine name where manual cloning is being performed
------------------------------------------------------
Go to: /AFF/SEL/U01/oracle/PROD/db/tech_st/11.1.0/network/admin/PROD_erptest and edit listener.ora file and update erpdb entries to erptest

vi listener.ora

:%s/erpdb/erptest/g

Tags: Oracle EBS Manual Clone, Oracle 12.1.2, 12.2.0

How to find the Descriptive Flex Field attached to an item.

How to find the Descriptive Flex Field attached to an item.

Go to examine and check the block name on which you want to find Descriptive Flex Field.

Now select $DESCRIPTIVE_FLEXFIELD$ from the list in block field on examine form

Now the required DFF will be <Block Name>.DESC_FLEX

Select your <Block Name>.DESC_FLEX from the options

Here is your DFF name.

IF it is ‘Receipt Application Information’ whereas ‘(Receivables)’ is the application name.

Tags:
Oracle Descriptive Flex Fields, Oracle EBS DFF

Oracle EBS Rapid cloning Release 12 with 4 Data Top Directories

Target System Base Directory : /erp/AFF/WIP/u02/oracle/PROD

Target System utl_file_dir Directory List : /erp/AFF/WIP/u02/oracle/PROD/db/tech_st/11.2.3/appsutil

Number of DATA_TOP's on the Target System [4] :

Target System DATA_TOP Directory 1 [/u01/oracle/PROD/db/apps_st/data/logs] :
RC-00002: Warning: Directory /u01/oracle/PROD/db/apps_st/data/logs not found.

Target System DATA_TOP Directory 1 [/u01/oracle/PROD/db/apps_st/data/logs] : /erp/AFF/WIP/u01/oracle/PROD/db/apps_st/data/logs

Target System DATA_TOP Directory 2 [/u04/oracle/PROD/db/apps_st/data] : /erp/AFF/WIP/u04/oracle/PROD/db/apps_st/data

Target System DATA_TOP Directory 3 [/u03/oracle/PROD/db/apps_st/data] : /erp/AFF/WIP/u03/oracle/PROD/db/apps_st/data

Target System DATA_TOP Directory 4 [/u01/oracle/PROD/db/apps_st/data/archives] : /erp/AFF/WIP/u01/oracle/PROD/db/apps_st/data/archives

Target System RDBMS ORACLE_HOME Directory [/erp/AFF/WIP/u02/oracle/PROD/db/tech_st/11.2.0] : /erp/AFF/WIP/u02/oracle/PROD/db/tech_st/11.2.3

ERRORS
RC-00110: Fatal: Error occurred while relinking of ApplyDBTechStack

ERROR while running Apply...
Thu Jan 22 12:47:20 2015

 ERROR: Failed to execute /erp/AFF/WIP/u02/oracle/PROD/db/tech_st/11.2.3/appsutil/clone/bin/adclone.pl

AutoConfig is exiting with status 1

RC-50013: Fatal: Instantiate driver did not complete successfully.
/erp/AFF/WIP/u02/oracle/PROD/db/tech_st/11.2.3/appsutil/driver/regclone.drv

Query to find org_id attached to a responsibility

Query to find org_id attached to a responsibility

SELECT * --fpov.profile_option_value
             FROM applsys.fnd_profile_option_values fpov
                , applsys.fnd_profile_options fpo
                , applsys.fnd_profile_options_tl fpot
                , applsys.fnd_responsibility_tl fr
            WHERE 1 = 1
              AND fpo.profile_option_name = fpot.profile_option_name
              AND fpo.profile_option_id = fpov.profile_option_id
              AND fr.responsibility_id(+) = fpov.level_value
             -- AND fr.responsibility_id = :$PROFILES$.RESP_ID
              AND fpot.profile_option_name = 'ORG_ID'
              --and fpov.profile_option_value='11'
              --and fr.responsibility_name like '%U1%Order%Super%User%';
                and fr.responsibility_id = 51973;
                

A Brief Introduction to Materialized Views.

Oracle Materialized View

This article is taken from: http://practical-tech.blogspot.com/2012/01/brief-introduction-to-materialized.html

The Following tutorial is prepared on Oracle Database 11gR2.

Materialized views facilitate you to execute a SQl query and 
save its results either locally or in a remote database.

After the Materialized view is initially populated it can be
refreshed later on to store fresh results into the underlying 
table.

Materialized Views are mainly used for two reasons:

1) Replication of data to separate remote databases.

2) For improving the performance of queries by computing
   and storing the results of complex aggregations of data.

   In situations where complex sql queries are performed
   mainly in reporting or datawarehouse environments
   Materialized Views are really helpful in improving 
   performance.
  
   Because whenever a SQL query is executed oracle database
   has to lot of work in order to retrieve the data, For
   example it may have to do sorting (Memory or Disk Based),
   it has to decide the execution plan for the sql statement
   (Do a full tables scan or a indexed based scan) and lots
   of other stuff before retrieving the requested data.

   These type of queries if performed repeatedly will
   affect the performance of the server in a negative
   way.

   But with Materialized Views the performance can be improved
   significantly, because when a materialized view is created
   it stores all the data along with the execution plans.
   So even if the query is executed repeatedly it will not eat
   up all the resources as it did earlier.
   

The Materialized view can be created on the base of tables,
views or other materialized views.

When a Materialized View is created, oracle also create a 
table with the same name as that of the materialized view
and also creates a materialized view object.
   
For the sake of simplicity we will only cover two
types of materialized views:

1) Complete - Refreshable Materialzed Views
2) Fast-refresh Materialized Views



COMPLETE REFERSH MATERIALIZED VIEWS


In this type of materialized view there is a complete
refresh of data at periodic intervals.


SQL> alter user scott identified by tiger account unlock;

User altered.

SQL> grant create materialized view to scott;

Grant succeeded.

SQL> conn scott;
Enter password: 
Connected.


SQL> create table sales(
  2  sales_id int,  
  3  sales_amt int,
  4  region_id int,
  5  sales_dtt timestamp,
  6  constraint sales_pk primary key (sales_id));

Table created.

SQL> insert into sales values(1,101,100,sysdate-50);
SQL> insert into sales values(2,511,200,sysdate-20)
SQL> insert into sales values(3,11,100,sysdate)
SQL> commit;

Now lets create a materialized view.

SQL> create materialized view sales_mv
  2  refresh
  3  complete
  4  next sysdate+1/1440
  5  as
  6  select sales_amt, sales_dtt from sales;

Materialized view created.


So above we have created a materialized view based on the sales
table, which will completely refresh itself after every one minute.



SQL> select mview_name, refresh_method, refresh_mode, build_mode,
fast_refreshable from user_mviews
where mview_name = 'SALES_MV';

MVIEW_NAME           REFRESH_ REFRESH_MODE BUILD_MOD FAST_REFRESHABLE
-------------------- -------- ------------ --------- ----------------
SALES_MV             COMPLETE DEMAND       IMMEDIATE NO

Materialized views can also be refreshed by (ON DEMAND or ON COMMIT).
Since i did not mention either of these clauses the default refresh is on
demand as seen above in REFRESH_MODE column.

If you query the user_objects you can see that several objects have 
been created.


SQL> col object_name format a20
SQL> select object_name, object_type from user_objects
     where object_name like 'SALES%'
     order by object_name;


OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------
SALES                TABLE
SALES_MV             MATERIALIZED VIEW
SALES_MV             TABLE
SALES_PK             INDEX



The materialized view is basically a logical container that
stores data in a regular table.

If you query the USER_SEGMENTS view you will find the base table
its primary-key and the table that stores the data returned by
the Materialized View.


SQL> select segment_name,segment_type from user_segments
  2  where segment_name like 'SALES%'
  3  order by segment_name;

SEGMENT_NAME    SEGMENT_TYPE
------------    ---------------
SALES           TABLE
SALES_MV        TABLE
SALES_PK        INDEX


Now lets check the already existing data and some more.

SQL> select sales_amt, to_char(sales_dtt,'dd-mon-yyyy') from sales_mv;


 SALES_AMT TO_CHAR(SAL
---------- -----------
       101 22-nov-2011
       511 22-dec-2011
        11 11-jan-2012


SQL> insert into sales values(4,99,200,sysdate);

1 row created.


SQL>insert into sales values(5,127,300,sysdate);

1 row created.

SQL> commit;

Commit complete.


After one minute the materialized view will get updated.

SQL> select sales_amt, to_char(sales_dtt,'dd-mon-yyyy') from sales_mv;

SQL>  SALES_AMT  TO_CHAR(SAL
      ---------- -----------
             101 22-nov-2011
             511 22-dec-2011
              11 11-jan-2012
              99 11-jan-2012
             127 11-jan-2012

 
if you have set a longer refresh interval and you do not
want to wait that long you can order the refresh manually
through the following command.


SQL> exec dbms_mview.refresh('SALES_MV','C');

PL/SQL procedure successfully completed.

# Where "C" stands for complete refresh.


So the whole process can ve summarized as following.

1) User or application creates transactions.
2) Base table is updated because of the transactions.
3) A complete refresh occurs or is done manually and
   the data in the materialized view is deleted and completely
   refreshed with the contents of the master table(SALES).
4) The User or application can query the materialized view
   which contains a point in time snapshot of the base table's data.



FAST REFRESH MATERIALIZED VIEW

Fast refreshable materialized views work a little bit differently.
When a fast refresh materialized view is created it initially
populates the materialized view table with data from the base or
master table.

After the initial data is populated only modified data is applied
to the materialized view table after each refresh, Instead of a
complete refresh like that in Complete refresh materialized views.

Three basic steps are required to create a fast refresh
materialized view.

1) Create a base or master table if it does not exist.
2) Create a Materialized view log on the base table.
3) Create a fast refresh materialized view.

Since i have already created a materialized view and base table 
i am going to drop them and make a fresh start.

SQL> drop materialized view sales_mv;

Materialized view dropped.

SQL> drop table sales purge;

Table dropped.


SQL> create table sales(
  2  sales_id int,
  3  sales_amt int,
  4  region_id int,
  5  sales_dtt timestamp
  6 );


SQL> alter table sales add constraint sales_pk primary key(sales_id);

Table altered.


SQL> desc sales;

 Name                 Null?    Type
 ------------------ -------- ---------------------
 SALES_ID           NOT NULL NUMBER(38)
 SALES_AMT                   NUMBER(38)
 REGION_ID                   NUMBER(38)
 SALES_DTT                   TIMESTAMP(6)



SQL> insert into sales values(1,101,100,sysdate-50);

1 row created.

SQL> insert into sales values(2,511,200,sysdate-20);

1 row created.

SQL> insert into sales values(3,11,100,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> 

When creating a fast refreshable materialized view a materialized
view log is required.

The reason behind this is that the log keeps track of all the 
changes made to the master table, so when a materialized view 
is refreshed only updated data is applied.

It is something similar to the "block change tracking file" 
feature in RMAN.

Further a materialized view can be created on the basis of
primary key or by ROWID.

If the master table has a primary key then primary key clause
can be used otherwise use ROWID.

Now lets create a materialized view log on the master table.

SQL> create materialized view log on sales with primary key;

Materialized view log created.

If your base table does not have a primary key then a following
error will occur.

ORA-12014: table does not contain primary key constraint

In that case make materialized view log based on ROWID.

SQL> create materialized view log sales with rowid;


Also, when creating a materialized view you have to mention whether
the data is refreshed via PRIMARY KEY or ROWID.

We are creating a materialized view based on primary key refresh.

NOTE: The primary key columns must be part of the MV select query from
      the base table.


SQL> create materialized view sales_mv
     refresh
     with primary key
     fast
     next sysdate+3/1440
     as
     select sales_id, sales_amt, sales_dtt from sales


Materialized view created.

Now lets query the USER_OBJECTS view.

SQL> select object_name, object_type from user_objects
  2  where object_name like '%SALES%'
  3  order by object_name;

OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------
MLOG$_SALES          TABLE
RUPD$_SALES          TABLE
SALES                TABLE
SALES_MV             MATERIALIZED VIEW
SALES_MV             TABLE
SALES_PK             INDEX
SALES_PK1            INDEX

7 rows selected.


Explanation.

MLOG$_SALES = This is a table created along with the materialized view.
              It contains data that has changed in the base table.

RUPD$_SALES = This table is created when a materialized view
              uses primary key for fast refresh. This is used
              to support updatable materialized views. But right
              now we are creating Read only MVs so ignore this table. 

SALES_PK1 = This index is automatically created and is based on the
            primary key columns of the base table.



SQL> select sales_amt, to_char(sales_dtt,'dd-mon-yyyy') from sales_mv;

 SALES_AMT TO_CHAR(SAL
---------- -----------
       101 23-nov-2011
       511 23-dec-2011
        11 12-jan-2012

Lets add some records.

SQL> insert into sales values(4,99,200,sysdate);

1 row created.

SQL> insert into sales values(5,127,300,sysdate);

1 row created.

SQL> commit;

Commit complete.


Now before refresh the mlog$_sales table
will contain information about the two changes that
have been made to the base table.


SQL> select count(*) from mlog$_sales;

  COUNT(*)
----------
         2

Wait for three minutes or refresh the view manually.

SQL> exec dbms_mview.refresh('SALES_MV','F');

Lets check the records.

SQL> select sales_amt, to_char(sales_dtt,'dd-mon-yyyy') from sales_mv;

 SALES_AMT TO_CHAR(SAL
---------- -----------
       101 23-nov-2011
       511 23-dec-2011
        11 12-jan-2012
        99 12-jan-2012
       127 12-jan-2012


After the refresh is complete and the data is refreshed
the MLOG$_SALES table will contain no records.

SQL> select count(*) from mlog$_sales;

  COUNT(*)
----------
         0

You can also check time of the last refresh when it happened.

 SQL> select mview_name, last_refresh_type, last_refresh_date
     from user_mviews;

MVIEW_NAME                     LAST_REF LAST_REFR
------------------------------ -------- ---------
SALES_MV                       FAST     12-JAN-12

The whole process above is summarized as following:

1) User or application creates transactions.
2) Data is commited in the base table.
3) Then the MVlog table is populated with the changes.
4) A fast refresh occurs automatically or manually.
5) All the changes that have been made since last refresh
   are applied to the materialized view and rows that are no
   longer required are deleted from MVlog table.
6) The users can query the materialized view which contains
   point in time snapshot of master tables data.

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

Working script example to test Materialized View

create table custusg.test
(id number(4) primary key,
name varchar2(40),
createdon date);

insert into custusg.test
values(2,'MOHSIN',sysdate);

update custusg.test
set name = 'FAHAD'
where id = 2;

select * from  custusg.test;

drop materialized view custusg.mv_test;

create  materialized view custusg.mv_test
refresh complete
next sysdate+1/1440
as 
select i.inquiry_id,inquiry_number 
from custusg.cust_usg_ont_inquiry i, custusg.cust_usg_ont_pcosting p
where i.inquiry_id = p.inquiry_id;



select * from custusg.mv_test;


select mview_name, refresh_method, refresh_mode, build_mode,
fast_refreshable 
from all_mviews
where mview_name = 'MV_TEST';

Oracle Cash Management Configuration

Oracle Cash Management

Oracle Cash Management is an open integrated solution for managing your company/enterprise-wide cash cycle. Oracle Cash Management is an enterprisewide solution for managing liquidity and controlling cash. Cash Management gives you direct access to expected cash flows from your operational systems. You can quickly analyze enterprisewide cash management, cash requirements and currency exposures, ensuring liquidity and optimal use of cash resources. Using Oracle Cash Management companies can project cash flows from Oracle General Ledger, Oracle Receivables, Oracle Payables, Oracle Payroll, Oracle Projects and Oracle Purchasing. Oracle Cash Management lets you automatically or manually record and reconcile bank statements, matching against system transactions using rules and tolerance levels. You can review and correct any import validation or reconciliation errors online. Oracle Cash Management can automatically reconcile correcting statement lines against error statement lines and provide an audit trail for verifying correction of bank errors. Oracle Cash Management is part of the Oracle Financials family of applications.

Configuration Steps

1. Define System Parameters

Use the System Parameters window to configure your Cash Management system to meet your business needs. System parameters determine, for example, which set of books Cash Management uses, the default options for manual reconciliation windows, and the control settings for the AutoReconciliation program. Navigation path: Cash Management>Setup>System>System Parameters

2. Run Security Wizard

Using the Cash Management Security wizard, an administrator can assign multiple legal entities to a role or roles to set up the following three securities:

• Bank Account Maintenance security – control bank account creation and updates
• Bank Account Use security – control bank account access
• Bank Account Fund Transfers security – control bank account transfers

For this purpose login through SYSADMIN default user and assign legal entity. Otherwise the user has to be given with relevant privileges to run this security wizard. User Name: SYSADMIN Navigation path: User Management>Roles & Role Inheritance

3. Bank Account Lookup Types

In Oracle Cash Management we can define lookup values for Bank Account Type. This lookup is extensible which can be defined current, saving, etc.Navigation path: Cash Management>Lookups

4. Define Bank(s)

The first step in the bank account creation is the bank definition. This means that which are the banks in which a company has accounts. This page allows you to search for existing banks, view and update them or create new banks.Navigation path: Cash Management>Setup>Banks>Banks

5. Define Branch(s)

Bank branch creation is the next step after the bank creation. Under a bank there must be branch(s) at different locations. This setup step allows you to search for existing bank branches, view and update them or create new bank branches. Navigation path:  Cash Management>Setup>Banks>Banks

6. Define Bank Account(s)

Once the bank and branch are created, you can proceed to the bank account setup. Select the bank branch you want to associate to your bank account. Assign the owner of the bank account. There are four areas associated to defining the account: general information, control of the account, security access to the account, and business unit assignment. If this is a Payable or Receivable account, the accounts are identified by business unit, and if a Payroll account, by legal entity. Navigation path: Cash Management>Setup>Banks>Bank Accounts

7. Define Check Book(s)

For each account Check Book is created in Oracle Cash Management. On payment automatic sequence number is assigned to the payment document.Navigation path: Cash Management>Setup>Banks>Bank Accounts After the configuration of these steps we are able to make entries in Oracle Cash Management.