Search This Blog

Saturday, December 4, 2010

Items not Costed – Oracle inventory

select * from mtl_material transaction  where costed_flag is not null

Remove Orphan Transactions/Move Orders

This is script to clear Open move order lines which are not linked to Delivery details, clean Orphan transactions in Mtl_material_transactions_temp and remove reservations link to Mtl_material_transactions_temp if reservations do not exist.
Below is the script:
REM $Header: INVCLRMO.sql 115.5 2002/03/09 16:23:25 kadavi noship $
REM
REM (c) Copyright Oracle Corporation 2001
REM All Rights Reserved
REM
REM HISTORY
REM This is script to clear Open move order lines which
REM are not linked to Delivery details ,clean Orphan suggestions
REM in Mtl_material_transactions_temp and remove reservations
REM link to Mtl_material_transactions_temp if reservations are
REM not existing.
REM
REM Also this script creates and drop temp tables so Ct
REM need to manually run this script.
REM dbdrv: sql ~PROD ~PATH ~FILE none none none sqlplus_single phase=dat \
REM dbdrv: checkfile:~PROD:~PATH:~FILE
WHENEVER SQLERROR CONTINUE ROLLBACK; –when an error occurred during the sql executing, rollback
prompt dropping tables --showing “droping tables”
DROP TABLE mtl_mmtt_backup; –clean/drop the previous backup table
DROP TABLE mtl_mtrl_backup;
DROP TABLE mtl_msnt_backup;
DROP TABLE mtl_mtlt_backup;
prompt create table for MMTT backup –start to create backup tables
CREATE TABLE mtl_mmtt_backup AS
(SELECT mmtt.*
FROM mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
WHERE mmtt.move_order_line_id IS NOT NULL
AND mmtt.move_order_line_id    = mtrl.line_id
AND mtrl.line_status           = 7 –preapproved
AND mtrl.header_id             = mtrh.header_id
AND mtrh.move_order_type       = 3 –pick-wave
AND NOT EXISTS
(SELECT ‘Y’
FROM wsh_delivery_details
WHERE move_order_line_id = mtrl.line_id
AND released_status      = ‘S’ –released to warehouse
)
)
/
–select to backup the lines whose linked move order line status is pre-approved, the move order is
–generated from realeasing SO, meantime there’s no line in the delivery details or there is
–but it is has been released to warehouse
prompt select allocation records for closed move order
INSERT
INTO mtl_mmtt_backup
(SELECT mmtt.*
FROM mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mtrl
WHERE mmtt.move_order_line_id = mtrl.line_id
AND mtrl.line_status          = 5
)
/ –select to backup the lines whose move order line status is closed
prompt select allocation records with missing move order
INSERT
INTO mtl_mmtt_backup
(SELECT mmtt.*
FROM mtl_material_transactions_temp mmtt
WHERE move_order_line_id IS NOT NULL
AND NOT EXISTS
(SELECT mtrl.line_id
FROM mtl_txn_request_lines mtrl
WHERE mtrl.line_id = mmtt.move_order_line_id
)
)
/ –select to backup the lines who has a move order link, but corresponding move order is not existing now.
prompt create backup table for move order lines
CREATE TABLE mtl_mtrl_backup AS
(SELECT mtrl.*
FROM mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
WHERE mtrl.line_status   = 7
AND mtrl.header_id       = mtrh.header_id
AND mtrh.move_order_type = 3
AND NOT EXISTS
(SELECT ‘Y’
FROM wsh_delivery_details
WHERE move_order_line_id = mtrl.line_id
AND released_status      = ‘S’
)
)
/
–select to backup the move order lines whose status is pre-approved, the move order is
–generated from realeasing SO, meantime there’s no line in the delivery details or there is
–but it is has been released to warehouse
prompt create backup table for serial number allocations
CREATE TABLE mtl_msnt_backup AS
(SELECT msnt.*
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id IN
(SELECT transaction_temp_id FROM mtl_mmtt_backup
)
)
/
–backup all of the series records under the backuped  mtl_material_transactions_temp
prompt create backup table for lot number allocations
CREATE TABLE mtl_mtlt_backup AS
(SELECT mtlt.*
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id IN
(SELECT transaction_temp_Id FROM mtl_mmtt_backup
)
)
/
–backup all of the lots records under the backuped mtl_material_transactions_temp
prompt select serial number allocations for lot controlled items
INSERT
INTO mtl_msnt_backup
(SELECT msnt.*
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id IN
(SELECT serial_transaction_temp_id FROM mtl_mtlt_backup
)
)
/
–backup all of the serials records for the lot and serial control items
prompt delete serial number allocations
DELETE
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id IN
(SELECT transaction_temp_id FROM mtl_msnt_backup
) /
–delete all of the serials records which has been backuped
prompt delete lot number allocations
DELETE
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id IN
(SELECT transaction_temp_id FROM mtl_mtlt_backup
) /
–delete all of the lot records which has been backuped
prompt delete allocations
DELETE
FROM mtl_material_transactions_temp
WHERE transaction_temp_id IN
(SELECT transaction_temp_id FROM mtl_mmtt_backup
) /
–delete all of the allocations (records in mmtt table)which has been backuped
prompt close move order lines
UPDATE mtl_txn_request_lines
SET quantity   = NVL(quantity_detailed, 0) ,
line_status  = 5
WHERE line_id IN
(SELECT line_id FROM mtl_mtrl_backup
) /
–close all of the move order which has been backuped in mtl_mtrl_backup
prompt update transaction source on the move order line
UPDATE mtl_txn_request_lines mtrl
SET mtrl.txn_source_line_id =
(SELECT DISTINCT(source_line_id)
FROM wsh_delivery_details
WHERE move_order_line_id = mtrl.line_id
AND released_status      = ‘S’
)
WHERE mtrl.line_status = 7
AND EXISTS
(SELECT delivery_detail_id
FROM wsh_delivery_details wdd
WHERE move_order_line_Id = mtrl.line_Id
AND wdd.source_line_id  <> mtrl.txn_source_line_id
AND wdd.source_line_id   > 0
AND wdd.released_status  = ‘S’
) /
–The move order has linked with delivery details and delivery details has been released to warehouse
–In the delivery details, there’s a source line id but the source line id is different with the one in move order
–And the move order is in the status of pre-approved.
–If above is true, change the source line id in move order line to the one in delivery details
prompt update transaction source on the allocation
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.trx_source_line_id =
(SELECT txn_source_line_id
FROM mtl_txn_request_lines
WHERE line_id = mmtt.move_order_line_id
)
WHERE mmtt.transaction_type_id IN (52, 53)
AND mmtt.move_order_line_id    IS NOT NULL
AND EXISTS
(SELECT line_id
FROM mtl_txn_request_lines
WHERE line_status       = 7
AND line_id             = mmtt.move_order_line_id
AND txn_source_line_id <> mmtt.trx_source_line_id
) /
–pending transaction is from internal order pick and sales order pick
–pending transaction is linked with move order, move order is in Pre-approved
–source line id in pending transaction is different with the one in move order
–If above is true, change the source line id in pending transaction to the one in move order
prompt update allocations for missing reservations
UPDATE mtl_material_transactions_temp mmtt
SET reservation_id         = NULL
WHERE mmtt.reservation_id IS NOT NULL
AND NOT EXISTS
(SELECT mr.reservation_id
FROM mtl_reservations mr
WHERE reservation_id = mmtt.reservation_id –there’s a reservation id in pending transaction,
)
/

COMMIT / EXIT; –but there’s no in reservation record; If above is true, change it to null.

/

Wednesday, December 1, 2010

External Tables Loading Data from Flat Files to Oracle

External Tables let you query data in a flat file as though the file were an Oracle table. In 9i, only read operations were permitted; in 10g, you can also write out data to an external table, although you can't write to an existing table.
While external tables can be queried, they're not usable in many ways regular Oracle tables are. You cannot perform any DML operations on external tables other than table creation; one consequence is that you can't create an index on an external table. External tables are largely used as a convenient way of moving data into and out of the database.
Oracle uses SQL*Loader functionality, through the ORACLE_LOADER access driver to move data from the flat file into the database; it uses a Data Pump access driver to move data out of the db into a file in an Oracle-proprietary format, and back into the database from files of that format. While there are some behavior differences and restrictions, you can think of external tables as a convenient, SQL-based way to use SQL*Loader and Data Pump functionality.
For example, suppose that you receive a daily .csv report from another department. Instead of writing a SQL*Loader script to import each day's .csv file into your database, you can simply create an external table and write an "insert ... select" SQL query to insert the data directly into your tables. Place the day's CSV file in the location specified in the external table definition, run the query, and you're done.

Since an external table's data is in the operating system, its data file needs to be in a place Oracle can access it. So the first step is to create a directory and grant access to it.
First create the directory in the operating system, or choose an existing directory. It must be a real directory. Make sure that the OS user that the Oracle binaries run as has read-write access to this directory. Note: Be sure not to use a directory you should be keeping secure, such as an Oracle data file, program, log or configuration file directory. And if the data you'll be putting there is sensitive, make sure that other OS users don't have permissions on this directory

$ cd /oracle/feeds/
$ mkdir xtern
$ mkdir xtern/mySID
$ mkdir xtern/mySID/data
$ ls  -l /oracle/feeds/xtern/mySID
total 8

For Windows Server:
Directly Create directory on C:\Ext_Table
Put the external table's data file in the data directory. In this example, I'll use the following CSV file:
employee_report.csv:
001,Hutt,Jabba,896743856,jabba@thecompany.com,18
002,Simpson,Homer,382947382,homer@thecompany.com,20
003,Kent,Clark,082736194,superman@thecompany.com,5
004,Kid,Billy,928743627,billythkid@thecompany.com,9
005,Stranger,Perfect,389209831,nobody@thecompany.com,23
006,Zoidberg,Dr,094510283,crustacean@thecompany.com,1

You must actually move or copy the file to the data directory; symlinks won't cut it. Again, make sure that if the data is sensitive, only the Oracle user can read or write to it.
The next step is to create these directories in Oracle, and grant read/write access on it to the Oracle user who will be creating the external table. When you create the directory, be sure to use the directory's full path, and don't include any symlinks in the path -- use the actual full path.

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> create or replace directory xtern_data_dir
  2  as '/oracle/feeds/xtern/mySID/data';
Directory created.
SQL> grant read,write on directory xtern_data_dir to apps;
Grant succeeded.

The last step is to create the table. The CREATE TABLE statement for an external table has two parts. The first part, like a normal CREATE TABLE, has the table name and field specs. This is followed by a block of syntax specific to external tables, which lets you tell Oracle how to interpret the data in the external file.
SQL> connect bulkload
Enter password:
Connected.
SQL> create table xtern_empl_rpt
  2      ( empl_id varchar2(3),
  3        last_name varchar2(50),
  4        first_name varchar2(50),
  5        ssn varchar2(9),
  6       email_addr varchar2(100),
  7        years_of_service number(2,0)
  8      )
  9      organization external
 10      ( default directory xtern_data_dir
 11        access parameters
 12        ( records delimited by newline
 13          fields terminated by ','
 14        )
 15        location ('employee_report.csv') 
 16    );

Table created.
At this point, Oracle hasn't actually tried to load any data. It doesn't attempt to check the validity of many of the external-table-specific parameters you pass it. The CREATE TABLE statement will succeed even if the external data file you specify doesn't actually exist.
With the create table statement, you've created table metadata in the data dictionary and instructed Oracle how to direct the ORACLE_LOADER access driver to parse the data in the datafile. Now, kick off the load by accessing the table:
SQL> select * from xtern_empl_rpt ;

EMP LAST_NAME  FIRST_NAME SSN       EMAIL_ADDR                     YEARS_OF_SERVICE
--- ---------- ---------- --------- ------------------------------ ----------------
001 Hutt       Jabba      896743856        jabba@thecompany.com                         18
002 Simpson    Homer      382947382  homer@thecompany.com                         20
003 Kent       Clark      082736194        superman@thecompany.com                       5
004 Kid        Billy      928743627          billythkid@thecompany.com                     9
005 Stranger   Perfect    389209831 nobody@thecompany.com                        23
006 Zoidberg   Dr         094510283 crustacean@thecompany.com                     1

6 rows selected.

Oracle used the ORACLE_LOADER driver to process the file, and just as with SQL*Loader, it's created a log file that you can inspect to see what just happened. The log file -- and the "bad" and "discard" files -- will have been written to the directory you specified as the "default directory" in your CREATE TABLE statement, and the file names default to tablename_ospid :

$ ls -l
total 16
-rw-r--r--  1 oracle oinstall 3652 Mar  1 19:41 XTERN_EMPL_RPT_26797.log
-rw-------  1 oracle oinstall  313 Mar  1 18:34 employee_report.csv

If Oracle was unable to process the data given the access parameters you specified, you'll get an error on the command line and in the log file, and there will also be a bad and/or discard file. (Note: if you're copying and pasting data into your external data file, be sure not to put a newline after the last record, or SQL*Loader will expect a seventh record, and you'll get an error when you try to select from the external table.)
You may want to configure separate directories for the SQL*Loader output files -- the LOG file, the DISCARD file and the BAD file -- as well as for the external table data. You can lump all four in the same directory, as we did in the previous example, although it's a bad idea: a naming mishap could have you overwriting one external table's data file with another's bad file. I like to have one directory for data files, and one for log/bad/discard files:
$ cd xtern/mySID
$ mkdir log
$ ls -l
total 16
drwx------  2 oracle oinstall 4096 Mar  1 17:33 data
drwx------  2 oracle oinstall 4096 Mar  1 17:32 log

Again, these must be actual directories, not symlinks, and be sure to set the permissions appropriately. To eliminate the possibility of any naming mishap, you can grant READ access only on /.../data, and WRITE access only on /..../log, to the user creating the external tables.
You can use ALTER TABLE to change the access parameters without dropping and redefining the whole table:
SQL> alter table xtern_empl_rpt
  2  access parameters
  3        ( records delimited by newline
  4          badfile xtern_log_dir:'xtern_empl_rpt.bad'
  5          logfile xtern_log_dir:'xtern_empl_rpt.log'
  6          discardfile xtern_log_dir:'xtern_empl_rpt.dsc'
  7          fields terminated by ','
  8        ) ;

Table altered.
Alternatively, you can set up the table so that no log, discard or bad files are generated. SELECTing data from the table will still fail if the maximum number of rejects is exceeded, just as in SQL*Loader. You can change the reject limit for an external table with an ALTER TABLE statement:
SQL> ALTER TABLE XTERN_EMPL_RPT SET REJECT_LIMIT 100;
Where external tables really shine are in the ease with which you can load their data into your tables. A particularly nice feature is that you can use any valid function that the current Oracle user has rights on to transform the raw data before loading it into your database tables. For example, suppose you had a function, get_bday_from_ssn (ssn in varchar2) that looked up an employee's birth date given their SSN. You can use that function to populate a BIRTH_DATE column in your local database table in the same step as you load the data into it.
SQL> create table empl_info as
  2  (select empl_id, last_name, first_name, ssn, get_bday_from_ssn (ssn) birth_dt
  3* from xtern_empl_rpt)
SQL> /

Table created.

SQL> select * from empl_info ;

EMP LAST_NAME  FIRST_NAME SSN       BIRTH_DT
--- ---------- ---------- --------- ----------
001 Hutt       Jabba      896743856 03/11/1939
002 Simpson    Homer      382947382 11/01/1967
003 Kent       Clark      082736194 01/15/1925
004 Kid        Billy      928743627 07/20/1954
005 Stranger   Perfect    389209831 10/23/1980
006 Zoidberg   Dr         094510283 04/04/2989

6 rows selected.

Unloading data into an external file...
Oracle 10g lets you create a new external table from data in your database, which goes into a flat file pushed from the database using the ORACLE_DATAPUMP access driver. This flat file is in an Oracle-proprietary format that can be read by DataPump. The syntax is similar to the CREATE TABLE... ORGANIZATION EXTERNAL above, but simpler -- since you can't specify the data format, you can specify very few access_parameters. The key difference is that you must specify the access driver, ORACLE_DATAPUMP, since the access driver defaults to ORACLE_LOADER.
SQL> create table export_empl_info
  2  organization external
  3  ( type oracle_datapump
  4    default directory xtern_data_dir
  5    location ('empl_info_rpt.dmp')
  6* ) as select * from empl_info
SQL> /

Table created.

SQL> select * from export_empl_info ;

EMPL_ID LAST_NAME       FIRST_NAME      SSN       BIRTH_DT
------- --------------- --------------- --------- ----------
001     Hutt            Jabba           896743856 01/01/1979
002     Simpson         Homer           382947382 01/01/1979
003     Kent            Clark           082736194 01/01/1979
004     Kid             Billy           928743627 01/01/1979
005     Stranger        Perfect         389209831 01/01/1979
006     Zoidberg        Dr              094510283 01/01/1979

6 rows selected.

... and back in again
You can now move the file you just created, empl_info_rpt.dmp, to another system and create an external table to read the data:
SQL> connect apps/xxxx@TEST
Connected.
SQL> create table import_empl_info
  2  ( empl_id varchar2(3),
  3    last_name varchar2(50),
  4    first_name varchar2(50),
  5    ssn varchar2(9),
  6    birth_dt date
  7  )
  8  organization external
  9  ( type oracle_datapump
 10    default directory xtern_data_dir
 11    location ('empl_info_rpt.dmp')
 12  ) ;

Table created.

SQL> select * from import_empl_info ;

EMPL_ID LAST_NAME       FIRST_NAME      SSN       BIRTH_DT
------- --------------- --------------- --------- ----------
001     Hutt            Jabba           896743856 01/01/1979
002     Simpson         Homer           382947382 01/01/1979
003     Kent            Clark           082736194 01/01/1979
004     Kid             Billy           928743627 01/01/1979
005     Stranger        Perfect         389209831 01/01/1979
006     Zoidberg        Dr              094510283 01/01/1979

6 rows selected.

Conclusion
We've seen an introduction to loading and unloading data with external tables. External tables in 9i and 10g provide a convenient, seamless way to move data in and out of the database, integrating SQL*Loader and Data Pump functionality with the power, scriptability and ease of SQL statements. It's definitely worth considering external tables the next time you have a daily upload or download to arrange.