IGNORE_ROW_ON_DUPKEY_INDEX Hint for INSERT Statement 11g new sql feature

With INSERT INTO TARGET...SELECT...FROM SOURCE, a unique key for some to-be-inserted rows may collide with existing rows. The IGNORE_ROW_ON_DUPKEY_INDEX allows the collisions to be silently ignored and the non-colliding rows to be inserted. A PL/SQL program could achieve the same effect by first selecting the source rows and by then inserting them one-by-one into the target in a block that has a null handler for the DUP_VAL_ON_INDEX exception. However, the PL/SQL approach would take effort to program and is much slower than the single SQL statement that this hint allows. This hint improves performance and ease-of-programming when implementing an online application upgrade script using edition-based redefinition.

How to define Order Import source in Order Management

When you are planning to import the orders from different sources, you need to setup the order source.
Here are steps to setup of the Order Import Source.

Once you login to Order Management switch to Order Management Superuser.

Setup --> Orders --> Import Sources

Once the Import Source form is opened, Click on the new button or press Ctrl+ down arrow and then
Enter the Order Import Source , Description and the check the Enabled check box. Here is screen shot for the same.

REUSE SETTINGS in the alter trigger

Prevents Oracle Database from dropping and reacquiring compiler switch settings. With this clause, Oracle preserves the existing settings and uses them for the recompilation of any parameters for which values are not specified elsewhere in this statement.

ALTER TRIGGER oe.get_bal COMPILE REUSE SETTINGS

PURGE new SQL feature in Oracle 11g

Purpose

Use the PURGE statement to remove a table or index from your recycle bin and release all of the space associated with the object, or to remove the entire recycle bin, or to remove part of all of a dropped tablespace from the recycle bin.

To see the contents of your recycle bin, query the USER_RECYCLEBIN data dictionary review. You can use the RECYCLEBIN synonym instead. The following two statements return the same rows:

SELECT * FROM RECYCLEBIN;

SELECT * FROM USER_RECYCLEBIN;

Caution:

You cannot roll back a PURGE statement, nor can you recover an object after it is purged.

Prerequisites

The database object must reside in your own schema or you must have the DROP ANY ... system privilege for the type of object to be purged, or you must have the SYSDBA system privilege.

Semantics

TABLE or INDEX

Specify the name of the table or index in the recycle bin that you want to purge. You can specify either the original user-specified name or the system-generated name Oracle Database assigned to the object when it was dropped.

• If you specify the user-specified name, and if the recycle bin contains more than one object of that name, then the database purges the object that has been in the recycle bin the longest.
• System-generated recycle bin object names are unique. Therefore, if you specify the system-generated name, then the database purges that specified object.

When the database purges a table, all table partitions, LOBs and LOB partitions, indexes, and other dependent objects of that table are also purged.

RECYCLEBIN

Use this clause to purge the current user's recycle bin. Oracle Database will remove all objects from the user's recycle bin and release all space associated with objects in the recycle bin.

DBA_RECYCLEBIN

This clause is valid only if you have SYSDBA system privilege. It lets you remove all objects from the system-wide recycle bin, and is equivalent to purging the recycle bin of every user. This operation is useful, for example, before backward migration.

TABLESPACE tablespace

Use this clause to purge all the objects residing in the specified tablespace from the recycle bin.

USER user Use this clause to reclaim space in a tablespace for a specified user. This operation is useful when a particular user is running low on disk quota for the specified tablespace.

Examples

Remove a File From Your Recycle Bin: Example The following statement removes the table test from the recycle bin. If more than one version of test resides in the recycle bin, then Oracle Database removes the version that has been there the longest:

PURGE TABLE test;

To determine system-generated name of the table you want removed from your recycle bin, issue a SELECT statement on your recycle bin. Using that object name, you can remove the table by issuing a statement similar to 

the following statement. (The system-generated name will differ from the one shown in the example.)

PURGE TABLE RB$$33750$TABLE$0;

Remove the Contents of Your Recycle Bin: Example To remove the entire contents of your recycle bin, issue the following statement:

PURGE RECYCLEBIN;

How to copy the FND tables data from one instance to another instance using FNDLOAD

FNDLOAD apps/apps@devdb 0 Y DOWNLOAD testcfg.lct out.ldt FND_APPLICATION_TL APPSNAME=FND

how to insert the data into FND_TERRITORIES from back end

Using the below package we can insert / update the data into FND_TERRITORIES_TL and FND_TERRITORIES.

   fnd_territories_pkg.load_row (

      x_territory_code=> :territory_code,

      x_eu_code=> :eu_code,

      x_iso_numeric_code=> :iso_numeric_code,

      x_alternate_territory_code=> :alternate_territory_code,

      x_nls_territory=> :nls_territory,

      x_address_style=> :address_style,

      x_address_validation=> :address_validation,

      x_bank_info_style=> :bank_info_style,

      x_bank_info_validation=> :bank_info_validation,

      x_territory_short_name=> :territory_short_name,

      x_description=> :description,

      x_owner=> :owner,

      x_last_update_date=> :last_update_date,

      x_custom_mode=> :custom_mode,

      x_obsolete_flag=> :obsolete_flag,

      x_iso_territory_code=> :iso_territory_code

   );

Apart from the above package we need to use the below package to insert the data into FND_CURRENCIES

fnd_currencies_pkg.LOAD_ROW (  X_CURRENCY_CODE in VARCHAR2,

  X_DERIVE_EFFECTIVE in DATE,

  X_DERIVE_TYPE in VARCHAR2,

  X_GLOBAL_ATTRIBUTE1 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE2 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE3 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE4 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE5 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE6 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE7 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE8 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE9 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE10 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE11 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE12 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE13 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE14 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE15 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE16 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE17 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE18 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE19 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE20 in VARCHAR2,

  X_DERIVE_FACTOR in NUMBER,

  X_ENABLED_FLAG in VARCHAR2,

  X_CURRENCY_FLAG in VARCHAR2,

  X_ISSUING_TERRITORY_CODE in VARCHAR2,

  X_PRECISION in NUMBER,

  X_EXTENDED_PRECISION in NUMBER,

  X_SYMBOL in VARCHAR2,

  X_START_DATE_ACTIVE in DATE,

  X_END_DATE_ACTIVE in DATE,

  X_MINIMUM_ACCOUNTABLE_UNIT in NUMBER,

  X_CONTEXT in VARCHAR2,

  X_ATTRIBUTE1 in VARCHAR2,

  X_ATTRIBUTE2 in VARCHAR2,

  X_ATTRIBUTE3 in VARCHAR2,

  X_ATTRIBUTE4 in VARCHAR2,

  X_ATTRIBUTE5 in VARCHAR2,

  X_ATTRIBUTE6 in VARCHAR2,

  X_ATTRIBUTE7 in VARCHAR2,

  X_ATTRIBUTE8 in VARCHAR2,

  X_ATTRIBUTE9 in VARCHAR2,

  X_ATTRIBUTE10 in VARCHAR2,

  X_ATTRIBUTE11 in VARCHAR2,

  X_ATTRIBUTE12 in VARCHAR2,

  X_ATTRIBUTE13 in VARCHAR2,

  X_ATTRIBUTE14 in VARCHAR2,

  X_ATTRIBUTE15 in VARCHAR2,

  X_ISO_FLAG in VARCHAR2,

  X_GLOBAL_ATTRIBUTE_CATEGORY in VARCHAR2,

  X_NAME in VARCHAR2,

  X_DESCRIPTION in VARCHAR2,

  X_OWNER in VARCHAR2)

dbms_utility.format_error_stack in Oracle 10g

In Oracle database 10g, Oracle added format_error_backtrace which can and should be called from exception handler. It displays the call stack at the point where exception was raised.
 Let's see what happen when exception handled using dbms_utility.format_error_stack procedure P3.

scott@10gR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

scott@10gR2> create or replace procedure p3 as
  2  begin
  3     p2;
  4  exception
  5             when others then
  6             dbms_output.put_line (' calling format error stack from P3 ');
  7             dbms_output.put_line (dbms_utility.FORMAT_ERROR_BACKTRACE);
  8  end;
  9  /

Procedure created.


And now when I run the procedure P3, I will see the following output.


scott@10gR2> exec p3;
raising error at p1
 calling format error stack from P3
ORA-06512: at "scott.P1", line 4
ORA-06512: at "scott.P2", line 3
ORA-06512: at "scott.P3", line 3

PL/SQL procedure successfully completed.


The information that had previously been available only through an unhandled exception is now retrievable from within the PL/SQL code.

Payment term setup in Order Management

Set up a payment term in for Payment Due with Order. Payment terms that have one or more installments with Days Due set to zero will be used to identify the Payment Due with Order order lines.

Steps

1. As Order Management Super User, navigate to Setup, Orders, Payment Terms.
     Below is the setup screen.
   
   
   
   
   
   
   
   
2. Enter a name for the payment term (e.g., Pay Now).
3. In order for the payment term to be a "pay now" payment term, ensure that no installment is allowed. You specify this by entering 0 (zero) in the Days Due field in the Payment Schedule region).

Forced Replacement of Types in 11g release 2

If you have used types, you must have realized how powerful they can be. You can define your own data type that can be a composite of various other data types, or they can be records to group related pieces of data together, even to match a complete table row. Here is an example of a type called TY_TRANS that defines the elements of a transaction:

create or replace type ty_trans
as object
(
    trans_id    number(2),
    trans_amt   number(10)
)
/

Next, you can a type to hold sales information. Since every sale will have a transaction, you can define a column of type ty_trans, shown below:

create or replace type ty_sales
as object
(
    sales_id    number(2),
    trans_rec   ty_trans
)
/

Once you define the structures this way, TY_SALES becomes a dependent of TY_TRANS. You can confirm that by querying USER_DEPENDENCIES view:

SQL> select referenced_name, dependency_type
  2  from user_dependencies
  3  where name = 'TY_SALES'
  4  /

REFERENCED_NAME       DEPE
---------------------           ----
STANDARD              HARD
TY_TRANS              HARD

This shows that TY_SALES has a “hard” dependency on the type TY_TRANS.

Now, let’s look at a real world possibility. What if you made a mistake in defining the types and defined an attribute with a wrong precision or just want to change the precision keeping with the business needs? Well, not a problem – you simply use the CREATE OR REPLACE statement to recreate that type:

create or replace type ty_sales
as object
(
    sales_id    number(3),
    trans_rec   ty_trans
)
/

Here you recreated the type with the one of the attributes as number(3) instead of number(2), as it was previously. While this operation was successful for this type, what if you had to do the same for ty_trans?

SQL> create or replace type ty_trans
  2  as object
  3  (
  4      trans_id    number (4),
  5      trans_amt   number
  6  )
  7  /
create or replace type ty_trans
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

The error message says it all – you can’t alter this type since it has a dependent, as we saw earlier from the user_dependencies view. It’s sort of a parent-child relationship between the types. If there is at least one child, you can’t drop the parent. So, what are your options for changing the “parent” type definition?

Until Oracle Database 11g Release 2, the only option for modifying that type is the MODIFY ATTRIBUTE clause of ALTER TYPE statement, which is an expensive and potentially error prone proposition. In Release 2, there is a very convenient FORCE clause to replace the type forcibly. Using this, we can alter the type TY_TRANS as:

create or replace type ty_trans
force
as object
(
    trans_id    number (4),
    trans_amt   number
)
/

This will execute successfully and the type will be created, due to the FORCE clause shown in bold above. This makes it very convenient when you deploy applications – you don’t have to worry about which specific attributes have changed; rather, a full replace type takes care of the type definition, changed or not.

Difference between Drop Shipments and Back to back order

Drop Shipments is similar to this back-to-back process in that your sales order line creates a requisition line that becomes a PO sent to your supplier. In a drop shipment; however, you instruct your supplier to send the item or configured item directly to your customer. The items never physically pass through your warehouse, and therefore you do not pick, pack or ship them yourselves. In the back-to-back scenario, you instruct your supplier to send you the goods, and then you ship them on to your customer.