Grant script to grant Tables, Views and packages to other schema in Oracle Apps R12.2.X

From Oracle R12.2.X release we can use the below Oracle recommended API/package to give grants on objects in APPS schema

exec AD_ZD.GRANT_PRIVS( X_PERMISSIONS ,

                                                    X_OBJECT_NAME,

                                                    X_GRANTEE ,

                                                    X_OPTIONS,

                                                    X_GRANT_TO_TABLE)

• X_PERMISSIONS:- refer to permissions to be granted like SELECT, INSERT, EXECUTE etc.
• X_OBJECT_NAME:- refers to the object name from APPS schema for which Grants would be given. Like the table name, view name. package name etc.
• X_GRANTEE: Refers to Grantee or schema to which grants would be given. Like Custom schema
• X_OPTIONS: Refers to Grant option, Default value is NULL
•  X_GRANT_TO_TABLE:

1.   This parameter is applicable only when the target object on which the grant needed is EV.

2.   This parameter decides whether permission to be granted on the underlying table also

• Values cane passed are: TRUE(default): Grant permission on EV and its underlying table, FALSE: Grant permission to EV only.

Below are few examples for granting SELECT on table and EXECUTE On package:

-- Grant Select on Custom Table

BEGIN

/* First three parameters are passed and other two are Default values*/

   AD_ZD.GRANT_PRIVS ('SELECT',

                            'XX_CUST_TABLE',

                            'XXSCH');

END;

-- Grant Execute on Custom Package

BEGIN

/* First three parameters are passed and other two are Default values*/

   AD_ZD.GRANT_PRIVS ('EXECUTE',

                            'XX_CUST_PKG',

                            'XXSCH');

END;

Query to derive Basic Information for Oracle PN (Property Management) Leases

SELECT lease.lease_num lease_number,
       lease.NAME lease_name,
       lease.lease_class_code,
       hou.NAME operating_unit,
       tower.location_code site_number,
       tower.building site_name,
       lease.lease_type_code,
       lease.lease_status,
       lease.status,
       detail.lease_execution_date execution_date,
       detail.lease_commencement_date commencement_date,
       detail.lease_termination_date termination_date,
       tower.lease_or_owned,
       tower.CLASS,
       address.county,
       address.state,
       address.country,
       address.zip_code
  FROM pn.pn_leases_all lease,
       pn.pn_lease_details_all detail,
       apps.hr_operating_units hou,
       apps.pn_tenancies_all tenancies,
       apps.pn_locations_all locations,
       apps.pn_locations_all tower,
       apps.pn_addresses_all address
 WHERE     1 = 1
       AND lease.lease_id = detail.lease_id
       AND tenancies.primary_flag(+) = 'Y'
       AND lease.org_id = hou.organization_id
       AND lease.lease_id = tenancies.lease_id
       AND tenancies.location_id = locations.location_id
       AND locations.PARENT_LOCATION_ID = tower.location_id
       AND tower.address_id = address.address_id

Query to find Concurrent Program Executable

SELECT fcpt.user_concurrent_program_name,
       fcp.CONCURRENT_PROGRAM_NAME,
       fe.execution_file_name,
       eflv.meaning execution_method
  FROM apps.fnd_concurrent_programs_tl fcpt,
       apps.fnd_concurrent_programs fcp,
       apps.fnd_executables fe,
       apps.fnd_lookup_values eflv
 WHERE     1 = 1
       AND fcpt.concurrent_program_id = fcp.concurrent_program_id
       AND fcp.executable_id = fe.executable_id
       AND fe.execution_method_code = eflv.lookup_code
       AND eflv.language = 'US'
       AND eflv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
       AND fcpt.user_concurrent_program_name LIKE '%XX%'

Script to create Oracle User account from Backend

DECLARE
   v_user_id   NUMBER;
BEGIN
   v_user_id :=
      fnd_user_pkg.createuserid (x_user_name              => 'FIRSTNAME.LASTNAME',
                                 x_owner                  => 'X',
                                 x_unencrypted_password   => 'welcome',
                                 x_description            => ' ',
                                 x_start_date             => SYSDATE);

   fnd_user_resp_groups_api.insert_assignment (
      user_id                         => v_user_id,
      responsibility_id               => 20420,  -- SYSADMIN Responsibility ID
      responsibility_application_id   => 1,         -- SYSADMIN APPLICATION ID
      start_date                      => SYSDATE,
      end_date                        => NULL,
      description                     => NULL);
   COMMIT;
   DBMS_OUTPUT.put_line (
      'Successfully created user and assigned Sysadmin responsibility');
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.put_line (SQLERRM);
END;
/

Procure to Pay (P2P) Life Cycle

Procure to Pay process (also known as P2P cycle) is followed by any organization in any ERP system if they would like to purchase any products (Goods, raw materials, Services, office supplies, infrastructure,  etc). When you purchase any product there should be a payment towards the purchase. This entire process of purchasing a product and making payment for that product to supplier is called procure to pay process. 

In brief in P2P life cycle an organization buys and receives goods or services from its vendor and makes corresponding payments. 

Below are the steps involved in this process:

• Create a Purchase Requisition

• Approve the Purchase Requisition 

• Auto create a Purchase Order from Requisition

• Approve the Purchase Order

• Receive the goods in Inventory (Create the Receipt)

• Inspect the goods

• Create a Payables Invoice

• Pay the Invoice

• Transfer to General Ledger

• Import Journal in to General Ledger

Oracle FOR ALL Insert Performance Testing Using Sample code

/* Timer utility */

CREATE OR REPLACE PACKAGE sf_timer
IS
   PROCEDURE start_timer;

   PROCEDURE show_elapsed_time (message_in IN VARCHAR2 := NULL);
END sf_timer;
/

CREATE OR REPLACE PACKAGE BODY sf_timer
IS
   /* Package variable which stores the last timing made */
   last_timing   NUMBER := NULL;

   PROCEDURE start_timer
   IS
   BEGIN
      last_timing := DBMS_UTILITY.get_cpu_time;
   END;

   PROCEDURE show_elapsed_time (message_in IN VARCHAR2 := NULL)
   IS
   BEGIN
      DBMS_OUTPUT.put_line (
            '"'
         || message_in
         || '" completed in: '
         || (DBMS_UTILITY.get_cpu_time - last_timing) / 100
         || ' seconds');

      start_timer;
   END;
END sf_timer;
/

CREATE TABLE parts
(
   partnum    NUMBER,
   partname   VARCHAR2 (15)
)
/

CREATE TABLE parts2
(
   partnum    NUMBER,
   partname   VARCHAR2 (15)
)
/

DROP TYPE parts_ot FORCE
/

CREATE OR REPLACE TYPE parts_ot IS OBJECT
(
   partnum NUMBER,
   partname VARCHAR2 (15)
)
/

CREATE OR REPLACE TYPE partstab IS TABLE OF parts_ot;
/

DECLARE
   PROCEDURE compare_inserting (num IN INTEGER)
   IS
      TYPE numtab IS TABLE OF parts.partnum%TYPE;

      TYPE nametab IS TABLE OF parts.partname%TYPE;

      TYPE parts_t IS TABLE OF parts%ROWTYPE
         INDEX BY PLS_INTEGER;

      parts_tab   parts_t;

      pnums       numtab := numtab ();
      pnames      nametab := nametab ();
      parts_nt    partstab := partstab ();
   BEGIN
      pnums.EXTEND (num);
      pnames.EXTEND (num);
      parts_nt.EXTEND (num);

      FOR indx IN 1 .. num
      LOOP
         pnums (indx) := indx;
         pnames (indx) := 'Part ' || TO_CHAR (indx);
         parts_nt (indx) := parts_ot (NULL, NULL);
         parts_nt (indx).partnum := indx;
         parts_nt (indx).partname := pnames (indx);
      END LOOP;

      sf_timer.start_timer;

      FOR indx IN 1 .. num
      LOOP
         INSERT INTO parts
              VALUES (pnums (indx), pnames (indx));
      END LOOP;

      sf_timer.show_elapsed_time (
         'FOR loop (row by row)' || num);

      ROLLBACK;

      sf_timer.start_timer;

      FORALL indx IN 1 .. num
         INSERT INTO parts
              VALUES (pnums (indx), pnames (indx));

      sf_timer.show_elapsed_time ('FORALL (bulk)' || num);

      ROLLBACK;

      sf_timer.start_timer;

      INSERT INTO parts
         SELECT * FROM TABLE (parts_nt);

      sf_timer.show_elapsed_time (
         'Insert Select from nested table ' || num);

      ROLLBACK;

      sf_timer.start_timer;

      INSERT /*+ APPEND */
            INTO  parts
         SELECT * FROM TABLE (parts_nt);

      sf_timer.show_elapsed_time (
         'Insert Select WITH DIRECT PATH ' || num);

      ROLLBACK;

      EXECUTE IMMEDIATE 'TRUNCATE TABLE parts';

      /* Load up the table. */
      FOR indx IN 1 .. num
      LOOP
         INSERT INTO parts
              VALUES (indx, 'Part ' || TO_CHAR (indx));
      END LOOP;

      COMMIT;

      DBMS_SESSION.free_unused_user_memory;

      sf_timer.start_timer;

      INSERT INTO parts2
         SELECT * FROM parts;

      sf_timer.show_elapsed_time ('Insert Select 100% SQL');

      EXECUTE IMMEDIATE 'TRUNCATE TABLE parts2';

      DBMS_SESSION.free_unused_user_memory;

      sf_timer.start_timer;

      SELECT *
        BULK COLLECT INTO parts_tab
        FROM parts;

      FORALL indx IN parts_tab.FIRST .. parts_tab.LAST
         INSERT INTO parts2
              VALUES parts_tab (indx);

      sf_timer.show_elapsed_time ('BULK COLLECT - FORALL');
   END;
BEGIN
   compare_inserting (100000);
END;
/

DROP TABLE parts
/

DROP TABLE parts2
/

DROP PACKAGE sf_timer
/