API to assign existing customer sites to other OUs(Operating Unit)

1. Create a custom table to hold error massages.

CREATE TABLE xxcona_cust_acct_sites_error

(

org_id  NUMBER,

cust_account_id NUMBER,

party_site_id NUMBER,

cust_acct_site_id NUMBER,

site_use_id NUMBER,

process_flag VARCHAR2(1),

error_msg  VARCHAR2(4000),

error_api  VARCHAR2(250)

);

2. Use the below procedure.

CREATE OR REPLACE PROCEDURE xxcona_assign_cust_site_to_org (

   p_cust_account_id      IN   NUMBER,

   p_assign_from_org_id   IN   NUMBER,

   p_assign_to_org_id     IN   NUMBER

)

AS

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

-- File name       : xxcona_assign_cust_site_to_org.prc

--

-- Author          : Arjun Prasad 

--

-- Created         : 06-JUN-2015

--

-- Description     : This PL/SQL procedure is used to assign customer sites to another operating units.

--

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

--  Date          Author          Version    Reason

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

--06-JUN-2015    Arjun Prasad     1.0      Initial creation

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

---For create_cust_acct_site creation--------------

   v_customer_acount_site_record   hz_cust_account_site_v2pub.cust_acct_site_rec_type;

   v_cust_acct_site_id             NUMBER                             := NULL;

   v_return_status                 VARCHAR2 (2)                       := NULL;

   v_msg_count                     NUMBER                             := NULL;

   v_msg_data                      VARCHAR2 (32000)                   := NULL;

   ---For cust_site_use_rec  creation--------------

   v_cust_site_use_rec             hz_cust_account_site_v2pub.cust_site_use_rec_type;

   v_customer_profile_rec          hz_customer_profile_v2pub.customer_profile_rec_type;

   v_site_use_id                   NUMBER                             := NULL;

   l_msg_index_out                 NUMBER;

   CURSOR cust_acct_sites

   IS

      SELECT hcas.org_id, hcas.cust_account_id, hcas.party_site_id,

             hcas.cust_acct_site_id

        FROM hz_cust_acct_sites_all hcas

       WHERE hcas.org_id = p_assign_from_org_id

         AND hcas.cust_account_id =

                                 NVL (p_cust_account_id, hcas.cust_account_id);

   CURSOR cust_acct_site_uses (

      xx_cust_acct_site_id    NUMBER,

      xx_assign_from_org_id   NUMBER

   )

   IS

      SELECT hcsu.org_id, hcsu.cust_acct_site_id, hcsu.site_use_id

        FROM hz_cust_site_uses_all hcsu

       WHERE hcsu.cust_acct_site_id = xx_cust_acct_site_id

         AND hcsu.org_id = xx_assign_from_org_id;

BEGIN

   --==============Initializa Application ======================--

   fnd_global.apps_initialize (user_id           => 1737,

                               resp_id           => 20678,

                               resp_appl_id      => 222

                              );

     MO_GLOBAL.SET_POLICY_CONTEXT('S',p_assign_to_org_id);

     MO_GLOBAL.INIT ('AR') ;                            

   FOR cur_rec IN cust_acct_sites

   LOOP

      v_customer_acount_site_record := NULL;

      --==============To get_cust_acct_site_rec =========================--

      hz_cust_account_site_v2pub.get_cust_acct_site_rec

                      (p_init_msg_list           => fnd_api.g_false,

                       p_cust_acct_site_id       => cur_rec.cust_acct_site_id,

                       x_cust_acct_site_rec      => v_customer_acount_site_record,

                       x_return_status           => v_return_status,

                       x_msg_count               => v_msg_count,

                       x_msg_data                => v_msg_data

                      );

      IF NVL (v_return_status, 'E') = 'S'

      THEN

         DBMS_OUTPUT.put_line (   'get_cust_acct_site_rec=>V_RETURN_STATUS:='

                               || v_return_status

                              );

      END IF;

--==============To create_cust_acct_site =========================--

      v_customer_acount_site_record.org_id := p_assign_to_org_id;

      v_customer_acount_site_record.cust_acct_site_id := NULL;

      v_customer_acount_site_record.orig_system_reference := NULL;

      hz_cust_account_site_v2pub.create_cust_acct_site

                                               (fnd_api.g_true,

                                                v_customer_acount_site_record,

                                                v_cust_acct_site_id,

                                                --returned

                                                v_return_status,

                                                --To indicagte success

                                                v_msg_count,                 --

                                                v_msg_data

                                               );

      DBMS_OUTPUT.put_line (   'create_cust_acct_site=>V_RETURN_STATUS:='

                            || v_return_status

                           );

      IF NVL (v_return_status, 'E') = 'S' AND v_cust_acct_site_id IS NOT NULL

      THEN

         v_return_status := NULL;

         FOR site_uses_rec IN cust_acct_site_uses (cur_rec.cust_acct_site_id,

                                                   p_assign_from_org_id

                                                  )

         LOOP

            v_cust_site_use_rec := NULL;

            v_customer_profile_rec := NULL;

            --==============To get_cust_site_use_rec =========================--

            hz_cust_account_site_v2pub.get_cust_site_use_rec

                           (p_init_msg_list             => fnd_api.g_false,

                            p_site_use_id               => site_uses_rec.site_use_id,

                            x_cust_site_use_rec         => v_cust_site_use_rec,

                            x_customer_profile_rec      => v_customer_profile_rec,

                            x_return_status             => v_return_status,

                            x_msg_count                 => v_msg_count,

                            x_msg_data                  => v_msg_data

                           );

            IF NVL (v_return_status, 'E') = 'S'

            THEN

               DBMS_OUTPUT.put_line

                               (   'get_cust_site_use_rec=>V_RETURN_STATUS:='

                                || v_return_status

                               );

            END IF;

            --==============To create_cust_site_use =========================--

            v_cust_site_use_rec.org_id := p_assign_to_org_id;

            v_cust_site_use_rec.site_use_id := NULL;

            v_cust_site_use_rec.cust_acct_site_id := v_cust_acct_site_id;

            v_cust_site_use_rec.orig_system_reference := NULL;

            v_customer_profile_rec.cust_account_profile_id := NULL;

            hz_cust_account_site_v2pub.create_cust_site_use

                            (p_init_msg_list             => fnd_api.g_true,

                             p_cust_site_use_rec         => v_cust_site_use_rec,

                             p_customer_profile_rec      => v_customer_profile_rec,

                             p_create_profile            => fnd_api.g_true,

                             p_create_profile_amt        => fnd_api.g_false,

                             x_site_use_id               => v_site_use_id,

                             x_return_status             => v_return_status,

                             x_msg_count                 => v_msg_count,

                             x_msg_data                  => v_msg_data

                            );

            DBMS_OUTPUT.put_line (   'create_cust_site_use=>V_RETURN_STATUS:='

                                  || v_return_status

                                 );

            IF NVL (v_return_status, 'E') = 'S'

            THEN

               DBMS_OUTPUT.put_line (   'ORG_ID:='

                                     || v_cust_site_use_rec.org_id

                                     || '***'

                                     || 'v_site_use_id:='

                                     || v_site_use_id

                                    );

            ELSE

               FOR i IN 1 .. v_msg_count

               LOOP

                  fnd_msg_pub.get (p_msg_index          => i,

                                   p_data               => v_msg_data,

                                   p_encoded            => fnd_api.g_false,

                                   p_msg_index_out      => l_msg_index_out

                                  );

                  v_msg_data := v_msg_data || ' *** ' || v_msg_data;

               END LOOP;

               DBMS_OUTPUT.put_line (   'create_cust_site_use=>Error: '

                                     || v_msg_data

                                    );

               INSERT INTO xxcona_cust_acct_sites_error

                           (org_id, cust_account_id,

                            party_site_id, cust_acct_site_id,

                            site_use_id, process_flag,

                            error_msg,

                            error_api

                           )

                    VALUES (cur_rec.org_id, cur_rec.cust_account_id,

                            cur_rec.party_site_id, cur_rec.cust_acct_site_id,

                            site_uses_rec.site_use_id, 'N',

                            SUBSTR (v_msg_data, 1, 4000),

                            'create_cust_site_use'

                           );

            END IF;

         END LOOP;

      ELSE

         FOR i IN 1 .. v_msg_count

         LOOP

            fnd_msg_pub.get (p_msg_index          => i,

                             p_data               => v_msg_data,

                             p_encoded            => fnd_api.g_false,

                             p_msg_index_out      => l_msg_index_out

                            );

            v_msg_data := v_msg_data || ' *** ' || v_msg_data;

         END LOOP;

         DBMS_OUTPUT.put_line ('create_cust_acct_site=>Error: ' || v_msg_data);

         INSERT INTO xxcona_cust_acct_sites_error

                     (org_id, cust_account_id,

                      party_site_id, cust_acct_site_id, site_use_id,

                      process_flag, error_msg,

                      error_api

                     )

              VALUES (cur_rec.org_id, cur_rec.cust_account_id,

                      cur_rec.party_site_id, cur_rec.cust_acct_site_id, NULL,

                      'N', SUBSTR (v_msg_data, 1, 4000),

                      'create_cust_acct_site'

                     );

      END IF;

   END LOOP;

   COMMIT;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('ERROR MESSAGE :=' || SQLERRM);

END;

3. Syntax to call above procedure.

BEGIN

   xxcona_assign_cust_site_to_org (p_cust_account_id         => 5042, 

-- Pass NULL to consider all customer sites.

                                   p_assign_from_org_id      => 81,

                                   p_assign_to_org_id        => 221

                                  );

END;

Few New features in Oracle 12C  (As published in asktheoracle.net)

1)Cascading Truncates:-

If you've been using Oracle for a while you probably already know about cascading deletes - when the parent record is deleted any child records are also deleted. This is enabled by specifying the clause ON DELETE CASCADE when defining the foreign key constraints. Here's an (artificial) example using employees and departmnets just to refresh your memory.

CREATE TABLE departments (
department_id NUMBER PRIMARY KEY
,department_name VARCHAR2 (30));

CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY
,employee_name VARCHAR2 (30)
,department_id NUMBER,
CONSTRAINT fk_emp_dept FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE);

INSERT INTO departments VALUES (1,'one');

INSERT INTO employees VALUES (1,'one',1);

DELETE departments ;

SELECT * FROM employees;

The select statememnt returns no rows because the employee record (the child) was deleted (silently) when we deleted the department (the parent) record. Without the ON DELETE CASCADE clause we would get the error ORA-02292: integrity constraint (HR.FK_EMP_DEPT) violated - child record found.

If we attempt to truncate the table we also get an error message -ORA-02266: unique/primary keys in table referenced by enabled foreign keys. However in Oracle 12c we can add the clauseCASCADE to the TRUNCATE statement like so:

TRUNCATE TABLE departments CASACDE

to do a fast, unlogged and, therefore, irreversible, delete on departments and employees as long as the foreign key constraint has been declared with the ON DELETE CASCADE clause, otherwise we getORA-14705: unique or primary keys referenced by enabled foreign keys in table "HR"."EMPLOYEES2".

2)Session-level sequences:-

In Oracle 12c the CREATE SEQUNCE has a new clause to specify the scope of the sequence - either GLOBAL the default, or SESSION. For example CREATE SEQUNCE sec1 SESSION. Session-level sequnces are designed to be used with global temporary tables and generate values that are unique only within the session. Also, as soon as the session ends, the state of the sequence is lost so a new session would generate values starting from the initial value defined for the sequence again.

3)DDL Logging:-

DDL logging means logging of SQL data definition language (DDL) commands and is not to be confused with redo logging of DML commands (insert/update/delete/ctas). In Oracle 12c there is a new initialization parameter ENABLE_DDL_LOGGINGwhich can be set to TRUE or FALSE by use of the ALTER SYSTEMand ALTER SESSIOM commands for database-wide logging or just sesion loging, respectively. When set this has the effect of logging all DDL commands executed in the database to an XML file located in c:\app\oracle\diag\rdbms\orcl\orcl\log\ddl.

Passing table name dynamically to a query

DECLARE
   v_amount    NUMBER;
   v_db_link   VARCHAR2 (200) := NULL;

   TYPE cur_typ IS REF CURSOR;

   c           cur_typ;
BEGIN
   SELECT db_link
     INTO v_db_link
     FROM dba_db_links
    WHERE db_link LIKE '%ASCP%';

   v_db_link := 'mtl_onhand_quantities_detail@' || v_db_link;
   DBMS_OUTPUT.put_line (v_db_link);

   OPEN c FOR    'SELECT NVL(SUM(PRIMARY_TRANSACTION_QUANTITY),0) X   FROM '
              || v_db_link;

   LOOP
      FETCH c
       INTO v_amount;

      EXIT WHEN c%NOTFOUND;
      DBMS_OUTPUT.put_line (v_amount);
   END LOOP;

   CLOSE c;
END;



Note : In this example, our requirement was to fetch name of the DB Link dynamically from table And then using the same into SELECT statement. Moreover, we can not directly use it into a formula column of a RDF report or FORM. For that we have to create a function into database with all the required parameters.