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.

How the Order Management linked with pricing and sales credits

Relationship between orders, lines, order types, line types, and workflow processes

Restriction in FORALL Statements Removed in Oracle 11g

The PLS-00436 restriction has been removed, which means you can now reference the individual elements of a collection within the SET and WHERE clauses of a DML statement in a FORALL construct. To see this in action, create and populates a test table using the following code.

CREATE TABLE forall_test (
  id          NUMBER,
  description VARCHAR2(50)
);

INSERT INTO forall_test VALUES (1, 'ONE');
INSERT INTO forall_test VALUES (2, 'TWO');
INSERT INTO forall_test VALUES (3, 'THREE');
INSERT INTO forall_test VALUES (4, 'FOUR');
INSERT INTO forall_test VALUES (5, 'FIVE');
COMMIT;

The PL/SQL block below populates a collection with the existing data, amends the data in the collection, then updates the table with the amended data. The final query displays the changed data in the table.

DECLARE
  TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;
  l_tab t_forall_test_tab;
BEGIN
  -- Retrieve the existing data into a collection.
  SELECT *
  BULK COLLECT INTO l_tab
  FROM   forall_test;

  -- Alter the data in the collection.
  FOR i IN l_tab.first .. l_tab.last LOOP
    l_tab(i).description := 'Description for ' || i;
  END LOOP;

  -- Update the table using the collection.
  FORALL i IN l_tab.first .. l_tab.last
    UPDATE forall_test
    SET    description = l_tab(i).description
    WHERE  id          = l_tab(i).id;

  COMMIT;
END;
/

SELECT * FROM forall_test;

        ID DESCRIPTION
---------- ---------------------------
         1 Description for 1
         2 Description for 2
         3 Description for 3
         4 Description for 4
         5 Description for 5

5 rows selected.
 Notice both the SET and WHERE clauses contain references to individual columns in the collection. This makes using bulk-binds for DML even easier as we no longer need to maintain multiple collections if we need to reference columns in the WHERE clause. It can also improve performance of updates, as previous versions required updates of the whole row using the ROW keyword, which included potentially unnecessary updates of primary key and foreign key columns. 

SKIP LOCKED clause in Oracle 11g

Oracle 11g introduced SKIP LOCKED clause to query the records from the table which are not locked in any other active session of the database. This looks quite similar to exclusive mode of locking. The SQL statement in Example code (15) queries the unlocked records from EMP table

Example :

SELECT * FROM EMP FOR UPDATE SKIP LOCKED

Read Only Tables In Oracle 11g

 In Oracle 11g, a table can be set READ ONLY mode to restrict write operations on the table. A table can be altered to toggle over READ ONLY and READ WRITE modes.

Example 1:

SQL> ALTER TABLE EMP READ ONLY;

Example 2: 

SQL> ALTER TABLE EMP READ WRITE;

Multitable Inserts feature

Multitable inserts allow a single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables. This statement reduces table scans and PL/SQL code necessary for performing multiple conditional inserts compared to previous versions. It's main use is for the ETL process in data warehouses where it can be parallelized and/or convert non-relational data into a relational format.

-- Unconditional insert into ALL tables

INSERT ALL

  INTO sal_history VALUES(empid,hiredate,sal)

  INTO mgr_history VALUES(empid,mgr,sysdate)

SELECT employee_id AS empid,

       hire_date AS hiredate,

       salary AS sal,

       manager_id AS mgr

FROM   employees

WHERE  employee_id > 200;

-- Conditionally insert into ALL tables

INSERT ALL

  WHEN sal > 10000 THEN

    INTO sal_history VALUES(empid,hiredate,sal)

  WHEN mgr > 200 THEN

    INTO mgr_history VALUES(empid,mgr,sysdate)

  WHEN 1=1 THEN

    INTO full_history VALUES(empid,mgr,sysdate)

SELECT employee_id AS empid,

       hire_date AS hiredate,

       salary AS sal,

       manager_id AS mgr

FROM   employees

WHERE  employee_id > 200;

-- Insert into the FIRST table with a matching condition

INSERT FIRST

  WHEN sal > 25000  THEN

    INTO special_sal VALUES(deptid,sal)

  WHEN hiredate LIKE ('%') THEN

    INTO hiredate_history_00 VALUES(deptid,hiredate)

  WHEN hiredate LIKE ('%99%') THEN

    INTO hiredate_history_99 VALUES(deptid,hiredate)

  ELSE

    INTO hiredate_history_not_99 VALUES(deptid, hiredate)

SELECT department_id AS deptid,

       SUM(salary) AS sal,

       MAX(hire_date) AS hiredate

FROM   employees GROUP BY department_id;

The restrictions on multitable inserts are:

• Multitable inserts can only be performed on tables, not on views or materialized views.
• You cannot perform a multitable insert via a DB link.
• You cannot perform multitable inserts into nested tables.
• The sum of all the INTO columns cannot exceed 999.
• Sequences cannot be used in the subquery of the multitable insert statement.