Steps by Step to Upload Values in Lookup by Using Package FND_LOOKUP_VALUES_PKG in Oracle Apps EBS R12

Steps by Step to Upload Values in Lookup by Using Package FND_LOOKUP_VALUES_PKG in Oracle Apps EBS R12

Step 1: 
Create Lookup in Application

Step 2: 

-------------*********  Create Custom Table for Lookup **************----------------

DROP TABLE TEMP_LOOKUP_T

--- Create Custom Table for Lookup ---

CREATE TABLE TEMP_LOOKUP_T(
LOOKUP_TYPE         VARCHAR2 (30 Byte)    ,
LANGUAGE            VARCHAR2 (30 Byte)    ,
LOOKUP_CODE         VARCHAR2 (30 Byte)    ,
MEANING             VARCHAR2 (80 Byte)    ,
DESCRIPTION         VARCHAR2 (240 Byte)    ,
ENABLED_FLAG        VARCHAR2 (1 Byte)    ,
START_DATE_ACTIVE   DATE    ,
END_DATE_ACTIVE     DATE    ,
CREATED_BY          NUMBER (15)    ,
CREATION_DATE       DATE    ,
LAST_UPDATED_BY     NUMBER (15)    ,
LAST_UPDATE_LOGIN   NUMBER (15)    ,
LAST_UPDATE_DATE    DATE    ,
SOURCE_LANG         VARCHAR2 (4 Byte)    ,
SECURITY_GROUP_ID   NUMBER (15)    ,
VIEW_APPLICATION_ID    NUMBER (15)    ,
TERRITORY_CODE      VARCHAR2 (2 Byte)    ,
ATTRIBUTE_CATEGORY  VARCHAR2 (30 Byte)    ,
ATTRIBUTE1          VARCHAR2 (150 Byte)    ,
ATTRIBUTE2          VARCHAR2 (150 Byte)    ,
ATTRIBUTE3          VARCHAR2 (150 Byte)    ,
ATTRIBUTE4          VARCHAR2 (150 Byte)    ,
ATTRIBUTE5          VARCHAR2 (150 Byte)    ,
ATTRIBUTE6          VARCHAR2 (150 Byte)    ,
ATTRIBUTE7          VARCHAR2 (150 Byte)    ,
ATTRIBUTE8          VARCHAR2 (150 Byte)    ,
ATTRIBUTE9          VARCHAR2 (150 Byte)    ,
ATTRIBUTE10         VARCHAR2 (150 Byte)    ,
ATTRIBUTE11         VARCHAR2 (150 Byte)    ,
ATTRIBUTE12         VARCHAR2 (150 Byte)    ,
ATTRIBUTE13         VARCHAR2 (150 Byte)    ,
ATTRIBUTE14         VARCHAR2 (150 Byte)    ,
ATTRIBUTE15         VARCHAR2 (150 Byte)    ,
TAG                 VARCHAR2 (150 Byte)    ,
LEAF_NODE           VARCHAR2 (1 Byte)      ,
ERROR_MESSAGE     VARCHAR2 (2000 BYTE)   ,
STATUS             VARCHAR2 (1 BYTE)      ,
SLNO             NUMBER
);


SELECT * FROM TEMP_LOOKUP_T;

-- Insert Data into Custom Table TEMP_LOOKUP_T --
-- User can do this by using SQLLDR.
-- For Sample data I will take following 3 lookup Values

-------------*********  END the Script of Custom Table for Lookup **************----------------

Step 3 :  Make a ANOMALOUS PROGRAM 

-------------- START ANOMALOUS PROGRAM FOR LOAD THE LOOKUP DATA ---------

DECLARE
L_BUSINESS_GROUP_ID NUMBER := 0;

/* OUT PARAMETERS */

L_STATUS            VARCHAR2 (5) := 'True';
L_ERROR_MESSAGE     VARCHAR2 (300);
L_VALIDATE_CNT      NUMBER;
L_TOTAL_RECORDS     NUMBER := 0;
L_SUCCESS_RECORDS   NUMBER := 0;
L_FAILURE_RECORDS   NUMBER := 0;
X_ROWID             VARCHAR2 (2000) := NULL;
V_CODE              CHAR (5) := '00000';

        /* Get_lookup_details */
/*
        CURSOR get_lookup_details
        IS
   
        SELECT ltype.application_id,
               ltype.customization_level,
               ltype.creation_date,
               ltype.created_by,
               ltype.last_update_date,
               ltype.last_updated_by,
               ltype.last_update_login,
               tl.lookup_type,
               tl.security_group_id,
               tl.view_application_id,
               tl.description,
               tl.meaning
          FROM fnd_lookup_types_tl tl, fnd_lookup_types ltype
         WHERE ltype.lookup_type = 'XX_LOAN_MASTER'
           AND ltype.lookup_type = tl.lookup_type;
    */
             
    /* CURSOR TO FETCH TEMP RECORDS */

            CURSOR CUR_TEMP_LOOKUP
            IS
         
            SELECT *
            FROM TEMP_LOOKUP_T T;
         
    BEGIN
        DBMS_OUTPUT.PUT_LINE ('#############################################################');
        DBMS_OUTPUT.PUT_LINE ('Data Migration Of Lookups :');
        DBMS_OUTPUT.PUT_LINE ('#############################################################');

        FOR CUR_REC IN CUR_TEMP_LOOKUP
        LOOP
             
             
            BEGIN
                L_TOTAL_RECORDS := L_TOTAL_RECORDS + 1;
                L_ERROR_MESSAGE := '';
                L_STATUS := 'True';
             
                 IF (L_STATUS = 'True') THEN
                       FND_LOOKUP_VALUES_PKG.INSERT_ROW
                      (X_ROWID                 => X_ROWID,
                       X_LOOKUP_TYPE           => 'XX_LOAN_MASTER',     -- LOOKUP TYPE
                       X_SECURITY_GROUP_ID     => 0,                             -- SECURITY GROUP ID
                       X_VIEW_APPLICATION_ID   => 260,                           -- APPLICATION ID (0-AOL)
                       X_LOOKUP_CODE           => CUR_REC.LOOKUP_CODE,           --'9471408289001',
                       X_TAG                   => CUR_REC.TAG,
                       X_ATTRIBUTE_CATEGORY    => CUR_REC.ATTRIBUTE_CATEGORY,    --'LOAN TEMPLATE',
                       X_ATTRIBUTE1            => CUR_REC.ATTRIBUTE1,
                       X_ATTRIBUTE2            => CUR_REC.ATTRIBUTE2,
                       X_ATTRIBUTE3            => CUR_REC.ATTRIBUTE3,
                       X_ATTRIBUTE4            => CUR_REC.ATTRIBUTE4,
                       X_ENABLED_FLAG          => 'Y',
                       X_START_DATE_ACTIVE     => TO_DATE ('30-SEP-2015','DD-MON-YYYY'),
                       X_END_DATE_ACTIVE       => TO_DATE ('31-JAN-2018','DD-MON-YYYY'),
                       X_TERRITORY_CODE        => NULL,
                       X_ATTRIBUTE5            => CUR_REC.ATTRIBUTE5,
                       X_ATTRIBUTE6            => CUR_REC.ATTRIBUTE6,
                       X_ATTRIBUTE7            => CUR_REC.ATTRIBUTE7,
                       X_ATTRIBUTE8            => CUR_REC.ATTRIBUTE8,
                       X_ATTRIBUTE9            => CUR_REC.ATTRIBUTE9,
                       X_ATTRIBUTE10           => CUR_REC.ATTRIBUTE10,
                       X_ATTRIBUTE11           => CUR_REC.ATTRIBUTE11,
                       X_ATTRIBUTE12           => CUR_REC.ATTRIBUTE12,
                       X_ATTRIBUTE13           => CUR_REC.ATTRIBUTE13,
                       X_ATTRIBUTE14           => CUR_REC.ATTRIBUTE14,
                       X_ATTRIBUTE15           => CUR_REC.ATTRIBUTE15,
                       X_MEANING               => CUR_REC.MEANING,               --'LTL', --LOOKUP MEANING
                       X_DESCRIPTION           => CUR_REC.DESCRIPTION,            --'LONG TERM LOAN',
                       X_CREATION_DATE         => SYSDATE,
                       X_CREATED_BY            => 0,
                       X_LAST_UPDATE_DATE      => SYSDATE,
                       X_LAST_UPDATED_BY       => 0,
                       X_LAST_UPDATE_LOGIN     => -1
                    );

                    COMMIT;
                 END IF;
               
        EXCEPTION
        WHEN OTHERS
        THEN
        L_FAILURE_RECORDS := L_FAILURE_RECORDS + 1;
        L_ERROR_MESSAGE := SUBSTR (SQLERRM, 1, 200);
     
        UPDATE TEMP_LOOKUP_T
        SET ERROR_MESSAGE = L_ERROR_MESSAGE,
        STATUS = 'E'
        WHERE SLNO = CUR_REC.SLNO;
        COMMIT;
     
        END;
     
        ---- IF Successfull Data Load Then Update the Status 'P' --
     
        UPDATE TEMP_LOOKUP_T
        SET ERROR_MESSAGE = L_ERROR_MESSAGE,
        STATUS = 'P'
        WHERE SLNO = CUR_REC.SLNO;
        COMMIT;
     
        END LOOP;
    END;

-------------- END ANOMALOUS PROGRAM FOR LOAD THE LOOKUP DATA --------- 

When I Creating Internal Purchase Order in the system using interface then it got error in PO_INTERFACE_ERRORS like 'Item is missing, invalid, or not internally order able for the purchasing organization. Cause The item is missing, invalid or not internal-order enabled for the purchasing organization. This is required for a source type of 'INVENTORY' Action Enter a valid, internal-order-enabled item for the purchasing organization.'

When I Creating Internal Purchase Order in the system using interface then it got error in PO_INTERFACE_ERRORS like

Item is missing, invalid, or not internally orderable for the purchasing organization.

Cause        The item is missing, invalid or not internal-order enabled for the purchasing organization. This is required for a source type of 'INVENTORY'

Action        Enter a valid, internal-order-enabled item for the purchasing organization.


Solution :

Step 1: Open the Item from the Inventory Responsibility.
Error for this Item code: FF.100.SJ100BC40R.TL01.0065.100.000.000 

Then click on the Order Management Tab.

Internal Order                 -- Checked.

Internal Order Enabled  -- Checked.

Responsibilities Name wise User Name and User Id in Oracle Apps EBS R12

----****************************************************************************----
----           Responsibilities Name wise User Name and User Id in Oracle Apps EBS R12                 ----
----****************************************************************************----

SELECT USR.USER_ID,
       USR.USER_NAME,
       RES.RESPONSIBILITY_ID,
       RES.RESPONSIBILITY_NAME
  FROM APPS.FND_USER USR,
       APPS.FND_RESPONSIBILITY_TL RES,
       APPS.FND_USER_RESP_GROUPS GRP
 WHERE GRP.RESPONSIBILITY_ID   = RES.RESPONSIBILITY_ID
   AND GRP.USER_ID             = USR.USER_ID
   AND RES.RESPONSIBILITY_NAME = NVL(:RESPONSIBILITY_NAME,RES.RESPONSIBILITY_NAME)
   AND USR.USER_NAME           = NVL(:USER_NAME,USR.USER_NAME);

Supplier Name Wise Invoice withholding Tax Group Name in Oracle Apps EBS R12

----***************************************************************************----
----  Supplier Name Wise Invoice withholding Tax Group Name in Oracle Apps EBS R12            ----
----***************************************************************************----


SELECT APS.VENDOR_ID,APS.VENDOR_NAME,APSS.VENDOR_SITE_CODE, AWTG.DESCRIPTION
FROM AP_SUPPLIERS APS,
     AP_SUPPLIER_SITES_ALL APSS,
     AP_AWT_GROUPS AWTG
WHERE APS.VENDOR_ID       = APSS.VENDOR_ID
AND   APSS.ALLOW_AWT_FLAG = 'Y'
AND   APSS.AWT_GROUP_ID   = AWTG.GROUP_ID
AND   APS.VENDOR_NAME like  '%Maan Engineering%';

AP Invoice Data Delete From the Base Table Using 'AP_AI_TABLE_HANDLER_PKG.Delete_Row(i.rowid,'APXINWKB')' in Oracle Apps EBS R12.

AP Invoice Data Delete From the Base Table Using 'AP_AI_TABLE_HANDLER_PKG.Delete_Row(i.rowid,'APXINWKB')'
in Oracle Apps EBS R12.

-------  This Script can be used when invoice accounting is not done.

DECLARE
cursor CUR1 is

-- Here we write the script for Which AP Invoice Data, We are Deleting from the Oracle Base Table ---
       SELECT aia.rowid,aia.* from ap_invoices_all aia where aia.BATCH_ID <> 13000;
     
BEGIN
   mo_global.set_policy_context('S',101);  -- Change the org id as per instance setup.
for i in cur1 loop

    AP_AI_TABLE_HANDLER_PKG.Delete_Row(i.rowid,'APXINWKB');
end loop;
    commit;
END;

Find Customer Bill To and Ship to Address Script in Oracle Apps EBS R12

Find Customer Bill To and Ship to Address Script in Oracle Apps EBS R12


SELECT HP.PARTY_NAME CUSTOMER_NAME,
       DECODE (HCSUA.SITE_USE_CODE,'SHIP_TO', HCSUA.LOCATION) CUSTOMER_BILL_TO_CODE,   
       DECODE (HCSUA.SITE_USE_CODE,'SHIP_TO', HL.ADDRESS1||' '||HL.ADDRESS2 ||' '|| HL.CITY||' '|| HL.STATE||' '|| HL.COUNTRY||' '|| HL.POSTAL_CODE)  CUSTOMER_BILL_TO_ADDRESS,
       HL.ADDRESS1,
       HL.ADDRESS2,
       HL.ADDRESS3,
       HL.ADDRESS4,
       HL.CITY,
       HL.STATE
FROM   HZ_CUST_ACCT_SITES_ALL HCSA,
       HZ_PARTY_SITES  HPS,
       HZ_CUST_SITE_USES_ALL HCSUA,
       HZ_CUST_ACCOUNTS    HCA,
       HZ_PARTIES  HP,
       HZ_LOCATIONS HL
WHERE  HPS.PARTY_SITE_ID =  HCSA.PARTY_SITE_ID
AND   HCSA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
AND   HCA.CUST_ACCOUNT_ID = HCSA.CUST_ACCOUNT_ID
AND   HP.PARTY_ID = HCA.PARTY_ID
AND   HL.LOCATION_ID = HPS.LOCATION_ID       
AND   HCSUA.SITE_USE_CODE = 'SHIP_TO'
AND   HP.PARTY_NAME  =:P_CUST_NAME
UNION
SELECT HP.PARTY_NAME CUSTOMER_NAME,
       DECODE (HCSUA.SITE_USE_CODE,'BILL_TO', HCSUA.LOCATION) CUSTOMER_BILL_TO_CODE,   
       DECODE (HCSUA.SITE_USE_CODE,'BILL_TO', HL.ADDRESS1||' '||HL.ADDRESS2 ||' '|| HL.CITY||' '|| HL.STATE||' '|| HL.COUNTRY||' '|| HL.POSTAL_CODE)  CUSTOMER_BILL_TO_ADDRESS,
       HL.ADDRESS1,
       HL.ADDRESS2,
       HL.ADDRESS3,
       HL.ADDRESS4,
       HL.CITY,
       HL.STATE
FROM   HZ_CUST_ACCT_SITES_ALL HCSA,
       HZ_PARTY_SITES  HPS,
       HZ_CUST_SITE_USES_ALL HCSUA,
       HZ_CUST_ACCOUNTS    HCA,
       HZ_PARTIES  HP,
       HZ_LOCATIONS HL
WHERE  HPS.PARTY_SITE_ID =  HCSA.PARTY_SITE_ID
AND   HCSA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
AND   HCA.CUST_ACCOUNT_ID = HCSA.CUST_ACCOUNT_ID
AND   HP.PARTY_ID = HCA.PARTY_ID
AND   HL.LOCATION_ID = HPS.LOCATION_ID       
AND   HCSUA.SITE_USE_CODE = 'BILL_TO'
AND   HP.PARTY_NAME  =:P_CUST_NAME
--AND   HCSUA.LOCATION = 1059

AR invoice Data delete script from the AR Base table using 'ar_invoice_api_pub.delete_transaction' in Oracle APPS EBS R12

Use following API for 

Delete AR invoices.

AR invoice Data delete script from the AR Base table using 'ar_invoice_api_pub.delete_transaction' in Oracle APPS EBS R12

Step 1:  incomplete the invoices

                       -- For R12 ------  AR_TRANSACTION_GRP.INCOMPLETE_TRANSACTION

using this script to 

------------------------------------------ START -- Incomplete the Invoices ---------------------------------------------

DECLARE 

IS

   l_return_status    VARCHAR2 (100);

   l_message_count    NUMBER;

   l_message_data     VARCHAR2 (1000);

   lv_error_message   VARCHAR2 (800);

BEGIN

   mo_global.set_policy_context ('S', 201);

--fnd_global.apps_initialize(2373,50603,222);

   ar_transaction_grp.incomplete_transaction

                           (p_api_version           => '1.0',

                            p_init_msg_list         => fnd_api.g_true,

                            p_commit                => fnd_api.g_false,

                            p_validation_level      => fnd_api.g_valid_level_none,

                            p_customer_trx_id       => 2424,

                            x_return_status         => l_return_status,

                            x_msg_count             => l_message_count,

                            x_msg_data              => l_message_data

                           );

   COMMIT;

   IF l_return_status IN ('E', 'U')

   THEN

      FOR i IN 1 .. l_message_count

      LOOP

         lv_error_message :=

                         lv_error_message || '--' || fnd_msg_pub.get (i, 'F');

         DBMS_OUTPUT.put_line ('l_return_status-' || l_return_status);

      END LOOP;

      DBMS_OUTPUT.put_line (   'API Failed. Error:'

                            || SUBSTR (lv_error_message, 1, 800)

                           );

   ELSE

      DBMS_OUTPUT.put_line ('AR Invoice Incomplete sucessfully');

      DBMS_OUTPUT.put_line ('lv_return_status-' || l_return_status);

   END IF;

END ;

--------------------------------------- END -- Incomplete the Invoices ---------------------------------------------

Step 2: Check the following Prerequisites :

1. Check if 'Allow Transaction Deletion' flag is Yes, 

    if no, check the flag for respective operating unit.

  Navigation : Receivables -> Setup -> System -> System Options

              >> Query for respective operating unit 

              >> Click on 'Trans and Customers' > Check for 'Allow Transaction Deletion' flag.

2. Transaction Should be incomplete.

3. There should be no activity against the transaction like it must neither be applied, printed or posted to GL etc

Step 3:  Sample script for invoice deletion

Step 3(a): Please make a custom table for your Deleting AP Invoice Data.

-------------------- Create Custom Table ---- For your Deleting Data Set --------------

CREATE TABLE XX_DELETE_TRANSACTION

AS

   SELECT CUSTOMER_TRX_ID,

          TRX_DATE,

          TRX_NUMBER,

          BILL_TO_CUSTOMER_ID,

          BILL_TO_SITE_USE_ID,

          ORG_ID,

          CREATION_DATE,

          CREATED_BY,

          'N' V_RET_STATUS,

          'N' V_MSG_COUNT,

          'N' V_MSG_DATA,

          'N' V_MESSAGE_TBL

     FROM RA_CUSTOMER_TRX_ALL     

    WHERE ORG_ID = 101 

      AND CUST_TRX_TYPE_ID = 4000 -- MIG LOCAL INVOICE 

      AND TRUNC (CREATION_DATE) = '04-FEB-18';

   --   AND TRX_NUMBER = 'JMFL/IFL/066/17';

    

    

--DROP TABLE FROM  XX_DELETE_TRANSACTION;

          

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

Step 3(B) :

---------------- START --- AR INVOICE DATA DELETE PROGRAM ---------------------

DECLARE

   CURSOR cur_all_trx

   IS

     

    SELECT ROWID,

             NULL PARTY_NUMBER,

             RCT.ORG_ID,

             RCT.CUSTOMER_TRX_ID,

             RCT.TRX_NUMBER

        FROM XX_DELETE_TRANSACTION RCT

       WHERE NVL (V_RET_STATUS, 0) <> 'S'

         AND TRX_NUMBER = 'JMFL/IFL/066/17';

       

   xv_msg_data     VARCHAR2 (4000) := NULL;

   xv_msg_count    NUMBER := 0;

   v_msg_index     NUMBER := 0;

   xv_ret_status   VARCHAR2 (1) := NULL;

   v_message_tbl   arp_trx_validate.message_tbl_type;

   v_res           VARCHAR2 (4000) := NULL;

   v_res_name      VARCHAR2 (4000) := NULL;

   v_app           VARCHAR2 (4000) := NULL;

   v_user          NUMBER := 1110;

BEGIN

   DBMS_OUTPUT.put_line ('Detele Transaction...');

   FOR c_rec IN cur_all_trx

   LOOP

      DBMS_OUTPUT.put_line ('   Transaction No.: ' || c_rec.trx_number);

      DBMS_OUTPUT.put_line ('   Transaction ID : ' || c_rec.customer_trx_id);

      DBMS_OUTPUT.put_line ('   Org ID         : ' || c_rec.org_id);

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

      ---- Setting the org context for the particular session

      apps.mo_global.set_policy_context ('S', c_rec.org_id);

      -- apps.mo_global.init('AR');

      SELECT application_id, responsibility_id

        INTO v_app, v_res

        FROM fnd_responsibility_tl        

       WHERE responsibility_id = 50778;

      ---- Setting the oracle applications context for the particular session

      apps.fnd_global.apps_initialize (v_user, v_res, v_app);

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

      xv_ret_status := NULL;

      xv_msg_count := NULL;

      xv_msg_data := NULL;

      --update the Allow Transaction Deletion to Yes to Delete (As mentioned above, better to do it from application)

      UPDATE ar_system_parameters_all

         SET invoice_deletion_flag = 'Y'

       WHERE org_id = c_rec.org_id;

      ar_invoice_api_pub.delete_transaction (

         p_api_name           => 'Delete_Transaction',

         p_api_version        => 1.0,

         p_init_msg_list      => fnd_api.g_true,

         p_commit             => fnd_api.g_true,

         p_validation_level   => fnd_api.g_valid_level_full,

         p_customer_trx_id    => c_rec.customer_trx_id,

         p_return_status      => xv_ret_status,

         p_msg_count          => xv_msg_count,

         p_msg_data           => xv_msg_data,

         p_errors             => v_message_tbl);

      UPDATE xx_delete_transaction

         SET v_ret_status = xv_ret_status

       WHERE ROWID = c_rec.ROWID;

      UPDATE xx_delete_transaction

         SET v_msg_count = xv_msg_count

       WHERE ROWID = c_rec.ROWID;

      IF xv_ret_status <> 'S'

      THEN

         DBMS_OUTPUT.put_line ('   Status: ' || xv_ret_status);

         UPDATE xx_delete_transaction

            SET v_msg_data = v_ret_status

          WHERE ROWID = c_rec.ROWID;

         FOR i IN 1 .. xv_msg_count

         LOOP

            apps.fnd_msg_pub.get (i,

                                  apps.fnd_api.g_false,

                                  xv_msg_data,

                                  v_msg_index);

            DBMS_OUTPUT.put_line ('   Error : ' || xv_msg_data);

         END LOOP;

         DBMS_OUTPUT.put_line ('   ' || xv_msg_data);

      ELSE

         DBMS_OUTPUT.put_line ('   Deleted.');

         -- Revert back to the original value for the deletion flag

         UPDATE ar_system_parameters_all

            SET invoice_deletion_flag = 'N'

          WHERE org_id = c_rec.org_id;

      END IF;

      DBMS_OUTPUT.put_line ('--------------------');

      COMMIT;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Error : ' || SQLERRM);

END;

---------------- END ---    AR INVOICE DATA DELETE PROGRAM ----------------------

How to Recover Data (Without a Backup) - How to Restore a Whole Table in oracle apps EBS R12. Recover all the data. Using Flashback Table, you can return a whole table to an earlier state.

How to Recover Data (Without a Backup) - How to Restore a Whole Table in oracle apps EBS R12. Recover all the data. Using Flashback Table, you can return a whole table to an earlier state. 


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

flashback table ap_invoices_all to timestamp <when it was good>;

flashback table "AP"."AP_INVOICES_ALL" to timestamp systimestamp - interval '5' hour;


alter table "AP"."AP_INVOICES_ALL"  enable row movement;

----------------
How to Restore a Whole Table
It's a classic rookie mistake: running a delete without a where clause. And then committing it!

Here you need to recover all the data. Using Flashback Table, you can return a whole table to an earlier state. All you need to do is run:

  flashback table <table> to timestamp <when it was good>;
For example, execute:

  flashback table orders to timestamp systimestamp - interval '1' hour;
And Oracle restores the table its state one hour ago. Handy if you’ve just deleted all the rows!

To use this, you must enable row movement:

  alter table <table> enable row movement;
If you haven’t done this, you’ll get the following error:

  ORA-08189: cannot flashback the table because row movement is not enabled
This is great if you’ve accidentally deleted or updated the whole table. But if there are only a handful of rows you need to recover it’s excessive. You’ve used stick of dynamite to kill an ant.

Even if you need to recover a large section of a table, flashing it back loses any changes made after the time you’re restoring it to. In most production systems there will be new rows you want to keep!


So this is handy for worst-case scenarios. It’s also useful for returning a table to a known state after testing. But for small finger trouble issues, it’s more likely you need to recover a handful of rows.