When a retailer decides to go ahead with Oracle Retail they have to decide which applications they need and what the order of installation will be. The decision for the first question the is entirely up to the retailer. For the second question can be a little bit more complicated. The order in which the various applications will be installed can determine the success or failure of the entire project. There are dependencies between projects and they need to be defined in advance. There are always constraints from the customer side that affect the order of the installation.

According to Oracle Retail the ideal order of installation of the entire Oracle Retail application suite is the following:

1. Oracle Retail Merchandising System (RMS), Oracle Retail Trade Management (RTM), Oracle Retail Sales Audit (ReSA)
2. Oracle Retail Service Layer (RSL)
3. Oracle Retail Extract, Transform, Load (RETL)
4. Oracle Retail Active Retail Intelligence (ARI)
5. Oracle Retail Warehouse Management System (RWMS)
6. Oracle Retail Allocation
7. Oracle Retail Invoice Matching (ReIM)
8. Oracle Retail Price Management (RPM)
9. Oracle Retail Central Office (ORCO)
10. Oracle Retail Back Office (ORBO)
11. Oracle Retail Store Inventory Management (SIM)
12. Oracle Retail Integration Bus (RIB)
13. Oracle Retail Point-of-Service (ORPOS)
14. Oracle Retail Analytics Applications
15. Oracle Retail Advanced Inventory Planning (AIP)
16. Oracle Retail Predictive Application Server (RPAS)
17. Oracle Retail Data Warehouse (RDW)
18. Oracle Retail Workspace (ORW)

In my case it is no great surprise as my latest project  had been  running on borrowed time since the start of the UK general election campaign when both the Liberals and Conservatives pledged to scrap it if they won, so with the advent of the coalition it was a bit like dead- man walking.

Whilst preparing for my next assignment and congratulating myself that my current contract lasted nearly three years I discovered that this is relatively short-term. I now know of contractors who have lasted 4 or 5 years – with repeated extensions – in the same organisation and wonder if there are any even longer ones. Maybe there’s an Oracle Contractor somewhere out there who holds the record for the most long-lasting continuous contract. 

Happy Friday!!!

I am pleased to announce the next release in our extensive oracle related White Paper Library of:

Installing Oracle GRC Applications: Factors to Consider Before and/or During the Installation

Authored by Roger Drolet

‘The Objective of Roger’s paper is to not provide you with step by step instructions to install the Oracle GRC Application Controls Suite.  Roger wrote this white paper to help you address some, not all, of the technical issues that you need to consider before and/or during the installation of the Oracle GRC Application Controls Suite.’

This paper is now available in our White Paper Library please regisiter or logon to request a copy today, Otherwise feel free to email me on rebecca.bragg@oraclecontractors.com for a copy.

A big thank you to Roger for putting together this paper.

Fast Deployment of Oracle Demantra 7.3 on Windows Environment

Authored by Horatiu Cirtita

Purpose of Horatiu’s Paper;

This papers intends to introduce readers into the world of demand planning envisioned by Oracle Demantra, a suite of tools that enables companies to model future demand, define and optimise trade promotions, manage deductions and settlements and support Sales and Operations Planning (SOP), serving as a visual toolkit for those professionals that want to install and implement the latest release (Oracle Demantra 7.3).

Please feel free to drop by the White Paper library to request your copy of the White Paper today. Remember to email Horatiu with any feedback you may have relating to this white paper or post it to this blog!

Horatiu thank you for sharing your knowledge with us!

Oracle EBS R12 Receivables Closing Period Procedures

Authored by Mohamed Nazih

Purpose of Mohamed’s Paper;

This paper will help you understand the steps, order, and reports required to close Oracle Receivables period. Proper closing each month helps minimizing year end time needed to issue financial statements.

Please feel free to drop by the White Paper library to request your copy of the White Paper today.

Remember to email Mohamed with any feedback you may have relating to this white paper or post it to this blog!

Thanks Mohamed

An item is ALWAYS created in a defined master organization in PIM. Though it is not coincidence that it is an Oracle EBS configured master organization. It is mandatory to define the item in a master organization.

Do not concern yourself with the Inventory module. When you create an item in PIM, it is the same item that is created in the Inventory module. When you create an item in the Inventory module’s Master Item form, that same item is fully available in the PIM html forms. They both share the same mtl_system_items_b table as well as others.

It is only when you define attribute groups that you have additional information in PIM regarding the item.

In fact, item catalog categories and item catalog groups are the same thing. But, you get more functionality in PIM regarding their usage.

Please consider PIM as a mandatory module to purchase in your next upgrade or implementation.  It has proven to be highly useful in multiple industries. 

Have a great one!

Bob 

create database link dblink1 connect to system identified by password1 using ‘ORCL1′;

These commands create a link called “dblink1” which connects as the user SYSTEM to a remote database - using the connect string “ORCL1”.  The connect string “ORCL1” is an entry in the tnsnames.ora file.

Once we’ve created our link, we can then run commands to check that we can access the remote database.   If the account used for the database link has privileges to access v$database, then we are able to use the query below to check the name of the remote database. 

select name from v$database@dblink1;

Alternatively, we could run a query to select data that we know only exists in the remote database.

Once our database link has been created, we then create a synonym (alias) for any remote objects referenced by the link. 

e.g.  create synonym emp2 for scott.emp@dblink1; 

This makes query writing more straightforward and also hides complexity from the end users.

Sometimes, for security or confidentiality reasons, we want to create a link, but don’t want to add an entry to the tnsnames.ora file.

To do this, we can create a link by specifying the full connection string, as shown below:

create database link dblink1 connect to system identified by password1 using ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oserver1)(PORT=1526))(CONNECT_DATA
=(SID=ORCL1)))’;

An alternative solution is to use the EZCONNECT naming method, which is available from Oracle 10g onwards:

create database link dblink1 connect to system identified by password1 using ‘oserver1:1526/ORCL1′;

Notes:

(i) This scenario assumes that even if we were using other methods for client name resolution - such as Oracle Internet Directory, or an Oracle Names Server - then we don’t want to add permanent entries to these either.
(ii) For ease of explanation, we are assuming that GLOBAL_NAMES is set to be FALSE in both databases.  If it were set to TRUE, then our database link would need to have the same name as the global_name of the target database.  (Select * from global_name; - to check this value).
(iii) The EZCONNECT syntax assumes that we have an entry for
“EZCONNECT” in our sqlnet.ora file, as a valid naming method under the NAMES.DIRECTORY_PATH parameter.
(iv) The tnsnames.ora file is usually located in   $ORACLE_HOME/network/admin on Unix or  %ORACLE_HOME%/network/admin on Windows. If we had set the TNS_ADMIN environment variable or registry key, then it would be at the location specified by this parameter.

I have had clients complain that average costing changes the entire way they do “cost management”.  The complaint has been that average costing is impossible for them to manage.  Simply because they say systematically, they cannot trace back to the “per unit” cost calculation that established the current system derived “per unit” average cost.

I say, “let the computer do all the work” and “let the computer report its calculations in detail back to you”.  You should be watching changes anyway.

People got to get used to faster reaction “moving average” with average costing methods.  This is as opposed to adjusting at the end of the year.  Standard costing is very slow to react.  Book value is “misrepresented” in standard costing because the computer and transaction are not adjusting the cost and value, people are.

Monitoring changes in purchasing and mfg cost is better monitored and reflected in inventory value with average cost.  But since there is no real variance account balances, the client complains that they have no “off the shelf” way of knowing when things are not right.
 

That is where bigger business intelligence monitoring takes over in average costing.  Monitoring specific events or jobs compared to historical performance in the past helps to make decisions.
 

Two things:
 

1)      Market commodity dependency on purchased material:  If price fluctuates greatly then go to average.  If customer price based on cost then go to layer costing.  If purchase price stable then stick with standard costing
2)      If manufacturing can build the same thing in a lot of different ways producing relatively the same production costs then stick with standard costing.  If mfg can build the same thing in a lot of different ways producing large fluctuation in production costs then consider average to amortize it into total inventory value or consider layer costing to tie cost to specific lots or serial numbers of the same item. 
 

But there is naturally many other things to assist in the choice….
 

Financially, standard cost causes more adjustments than average costing.  Operationally, average cost can hide increasing or decreasing trends in costs.  Layer costing requires more “tailor made” placement of cost in transactions (cost hook) but will help adjust trends. 
 

Accuracy(less to greater value representation):  Standard then Average then Layer (FIFO/LIFO)
Maintenance (less to more after go-live):  Layer then Average then Standard
Best practices and future direction (oldest to future): Standard to Average to Layer costing
Cost to implement in Oracle(least to most upfront costs):  Average to Standard to Layer costing
 

Long term trend in decreasing inventory cost: Standard
Long term inventory holding: Standard or Layer
Cost Based pricing in high competitive market: Layer
Short term trend in decreasing inventory cost: Average
Short term inventory holding: Average or Layer
 

None of these are industry specific nor industry specific best practices.  These are just guidelines used to help me and my client choose.

Just don’t make the decision solely on how your current reports are used or “That’s the way we’ve been doing it for years”. 

Part 3 discussed the use of RMAN (Recovery Manager) and in the final part we’ll be using DBMS_STREAMS_TABLESPACE_ADM to clone a tablespace.  

For all of this work, our local database is called ORCL and our remote database is ORCL2. 

If you have carried out the procedures in parts 1, 2 or 3 then run the commands below on the local and remote databases, otherwise skip these drop commands.

Drop tablespace TTS_TEST including contents and datafiles;
Drop tablespace TTS_TEST_IDX including contents and datafiles;
Drop user tts_user cascade;

Create Example Tablespaces in the local database

We’ll begin by creating some tablespaces in our local database.  Run the commands below as SYSTEM.

CREATE TABLESPACE TTS_TEST DATAFILE
‘/u02/oradata/ORCL/TTS_TEST01.dbf’ size 10M autoextend on maxsize 100M;

CREATE TABLESPACE TTS_TEST_IDX DATAFILE
‘/u02/oradata/ORCL/TTS_TEST_IDX01.dbf’ size 10M autoextend on maxsize 100M;
  
Create an Example user in the local and remote databases

Now we’ll create an example user in our local and remote databases.

CREATE USER TTS_USER IDENTIFIED BY tester1 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

 GRANT CREATE SESSION, CREATE TABLE TO TTS_USER;
 
Next - just on the local database – we’ll run the commands:

ALTER USER TTS_USER DEFAULT TABLESPACE TTS_TEST;
ALTER USER TTS_USER QUOTA UNLIMITED ON TTS_TEST;
ALTER USER TTS_USER QUOTA UNLIMITED ON TTS_TEST_IDX;

Create an Example Table in the local database

We can then create a very basic table with an associated index.  The table and index will be in separate tablespaces and will use the default storage options for the tablespace.  
 
CREATE TABLE TTS_USER.T1  (C1 NUMBER(10) ,  C2 VARCHAR2(10) )
TABLESPACE TTS_TEST ;

ALTER TABLE TTS_USER.T1 ADD (CONSTRAINT t1_pk PRIMARY KEY(C1) USING INDEX TABLESPACE TTS_TEST_IDX);

INSERT INTO TTS_USER.T1 VALUES (1,’TESTROW1′);
INSERT INTO TTS_USER.T1 VALUES (2,’TESTROW2′);
COMMIT;

Using DBMS_STREAMS_TABLESPACE_ADM to clone tablespaces

You can create a clone of an existing tablespace and then attach it to a remote database.   (These methods would normally be used for tablespace versioning and are available from 10g Release 1).

Note: It is also possible to detach and then re-attach a tablespace, but as we wish to keep our original tablespace in this scenario, the DETACH option will not be discussed.   

(You should be aware that you can’t use these procedures for objects owned by SYS). 

CLONE TABLESPACE

(i) Create an operating system and corresponding database directory for the new version of the tablespace.  

e.g.        mkdir /u01/oradata/ORCL/copy_datafiles

Then login to the database as SYS as sysdba and run the commands:

CREATE OR REPLACE DIRECTORY OBJ_COPY AS ‘/u01/oradata/ORCL/copy_datafiles’;

This will be used as the setting for the TABLESPACE_DIRECTORY_OBJECT parameter later on.

(ii) Create a clone of the tablespace.   (These commands also add a new version of the tablespace to a filegroup repository called TTS_TEST_FG, which is created automatically.

DECLARE

tbs_set DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;

BEGIN
tbs_set(1) := ‘TTS_TEST’;
tbs_set(2) := ‘TTS_TEST_IDX’;
DBMS_STREAMS_TABLESPACE_ADM.CLONE_TABLESPACES(
tablespace_names => tbs_set,
tablespace_directory_object => ‘OBJ_COPY’,
file_group_name => ‘TTS_TEST_FG’,
version_name => ‘V1′);
END;
/

Notes:  version_name and file_group_name can be set to anything you like.   If you need to change the name of the datafile later, then you can use   DBMS_FILE_GROUP.ALTER_FILE.

If you get any errors when running these procedures, run the query: 

SELECT REASON FROM SYS.PLUGGABLE_SET_CHECK WHERE TS1_NAME IN (’TTS_TEST’, ‘TTS_TEST_IDX’);

(iii) A copy of the datafile, the export dumpfile and the logfile from this work will all go to the OBJ_COPY database directory.   The datafile has been cloned, but is not attached to the database, as we can only have one version of a tablespace attached and online at any time.

(iv) We can query DBA_FILE_GROUP_TABLESPACES and DBA_FILE_GROUP_FILES for information on this new version of the tablespace.

ATTACH TABLESPACE

To be able to use the cloned tablespace in another database, we now need to attach the tablespace.

(i) You can use these procedures when working with tablespaces that have previously been processed by DBMS_STREAMS_TABLESPACE_ADM. DETACH_TABLESPACE and CLONE_TABLESPACE procedures, or with files created by the RMAN or Datapump export transport tablespace commands.

(ii)       Copy the datafile to the datafile location of the remote database.  Copy the export dumpfile to the DATA_PUMP_DIR directory of the remote database.   

(iii)     In the remote database,  create a database directory pointing to the location of the new datafiles as SYS as sysdba:

CREATE OR REPLACE DIRECTORY OBJ_LOAD AS ‘/u02/oradata/ORCL2′;

This will be used as the setting for the TABLESPACE_DIRECTORY_OBJECT parameter later on.

(iv) Run the commands below against the remote database from the DATA_PUMP_DIR location, which contains the export dumpfile. This will attach the tablespace to the database.  (Amend the dump_file.file_name parameter to match the name of your export dumpfile before running the commands).
set serveroutput on

DECLARE
  tbs_files     dbms_streams_tablespace_adm.file_set;
  cvt_files     dbms_streams_tablespace_adm.file_set;
  dump_file     dbms_streams_tablespace_adm.file;
  dp_job_name   VARCHAR2(30) := NULL;
  ts_names       dbms_streams_tablespace_adm.tablespace_set;

BEGIN
  dump_file.file_name :=  ‘expdat22.dmp’;
  dump_file.directory_object := ‘DATA_PUMP_DIR’;
   tbs_files( 1).file_name :=  ‘TTS_TEST01.dbf’;
  tbs_files( 1).directory_object :=  ‘OBJ_LOAD’;
   tbs_files( 2).file_name :=  ‘TTS_TEST_IDX01.dbf’;
  tbs_files( 2).directory_object :=  ‘OBJ_LOAD’;
   dbms_streams_tablespace_adm.attach_tablespaces(
    datapump_job_name      => dp_job_name,
    dump_file              => dump_file,
    tablespace_files       => tbs_files,
    converted_files        => cvt_files,
    tablespace_names       => ts_names);
  IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN
    FOR i IN ts_names.first .. ts_names.last LOOP
      dbms_output.put_line(’imported tablespace ‘|| ts_names(i));
    END LOOP;
  END IF;
END;
/

(v)  If you query DBA_TABLESPACES, you should now see that the tablespaces have been added to your remote database.   (Note: These commands won’t create a new file group in the remote database - they’ll just attach the tablespaces). 

(vi) Finally we just need to put the tablespaces into read write mode.

ALTER TABLESPACE TTS_TEST READ WRITE;

ALTER TABLESPACE TTS_TEST_IDX READ WRITE;

Note: There is also a Transport Tablespace Wizard in Oracle Enterprise Manager for carrying out these tasks.

References

(i)  Oracle documentation at: http://www.oracle.com/technology/documentation/database.html

this might be talking about the obvious but how difficult is the market out there and what are the ‘Do and Don’t’ that await us Contractors in the market.

I have been idling now for a few months and when I actually have a gig it turns out that the client has no intention of paying me which results in more money to the solicitors to chase up payment in a number of courts.

I have been running my own company now for a couple of years and as a full time Contractor, in the past I was always ’shelled’ by an agency or an employer, I have enjoyed a good run until mid last year.

The market has been good until mid last year and then the ‘crisis’ caused by the Banks and by Human Nature has hit all of us very hard. I heard form other Contractors that ‘Oracle is leaving many professional home’ and that ‘SAP seems to have the upper hand’ but in actual facts we are all in the same boat.

I guess we all look forward to the Oracle market picking up and indeed I am having more contacts with new projects however twice now this year this has happened and twice it went quiet again.

What do we think and how can we help the clients in a ‘win-win’ situation rather than having a client driven market that squeezes out the profit from us? How can we maintain a reasonable rate rather then having to slash prices to a level where it is questionable whether it is actually more lucrative to be a contractor? 

I look forward to your comments.

Marco.