Friday, October 31, 2014

How to Create Partition Table in Oracle Schema and Add Partition in Table.


Step 1: Connect schema using sql plus

sql> scott/tiger;

Step 2: Create table

sql> create table employee
       (
        empcode number,
        empname number,
        joindate date,
        joined_year number(4)
       )
       tablespace system
       pctused 40
       pctfree 10
       initrans 1
       maxtrans 255
       logging
       partition by range (joined_year)
       (
        partition employee2014 values less than (2015)
        logging
        nocompress
        tablespace system
        pctused 0
        pctfree 20
        initrans 1
        maxtrans 255
        storage (
        initial 64K
        minextents 1
        maxextents 2147483645
        freelists 1
        freelist groups 1
        buffer_pool default
        )
       );

Step 3: Create Procedure

sql> create or replace procedure proc_gen_partition(p_year number) as
       cursor cur_gen_par is
       select distinct object_name from all_objects
        where owner in ('SCOTT')
        and object_name is not null
        and object_type = 'TABLE PARTITION';

        v_objname varchar2(100);
        v_qry varchar2(4000);
        v_error varchar2(4000);
        v_partname varchar2(100);
        v_maxbal number;

       begin
         open cur_gen_par;
         loop
            fetch cur_gen_par into var_objname;
            exit when cur_gen_par%notfound;
         begin
            v_partname := var_objname||p_year;
            v_maxval := p_year+1;
            dbms_output.put_line       (var_objname||p_year);
            v_qry := 'alter table '||        var_objname||' add partition
            '||v_partname||' values less than ('||to_char(p_year+1)||') tablespace system';

            execute immediate v_qry;
            exception
            when others then
            v_error := sqlerrm;
           dbms_output.put_line('Partiton already created for '||v_partname);
          end;
         end loop;
         close cur_gen_par;
         exception
          when others then
          v_error := sqlerrm;
         end;

Step 4: Execute Procedure

sql> exec proc_gen_partition(2015);

Step 5: Confirm that Partition created successfully

sql>select partition_name
       from user_tab_partitions
       where lower(table_name) = 'employee';

Thursday, October 30, 2014

Script to find who has changed the sysadmin Password



SELECT substr(d.user_name,1,30) || '~'||substr(f.user_name,1,10)||'~'||substr(d.last_update_date,1,10) key, count(*) value
FROM apps.fnd_user d, apps.fnd_user f
WHERE
trunc(d.last_update_date) = trunc(sysdate)
and d.user_name = 'SYSADMIN'
and d.last_updated_by = f.user_id
GROUP BY substr(d.user_name,1,30) || '~'||substr(f.user_name,1,10)||'~'||substr(d.last_update_date,1,10);

Monday, October 27, 2014

FRM-92101 there was a failure in the forms server during startup

Solution 1:
  1. Go to directory where you installed E- Business Suite “E:\Oracle\VIS or PROD\apps\tech_st\10.1.2\forms” and check the folder “mesg” whether it exists.
  2. If  "mesg" folder does not exist, then create new folder "mesg" and copy all the “.msb” files from “E:\Oracle\VIS or PROD\apps\tech_st\10.1.2\forms” to “E:\Oracle\VIS or PRDO\apps\tech_st\10.1.2\forms\mesg”.
  3. Restart the server. 
Solution 2:

To avoid this problem Disable the XSS Filter option 

 Internet Explorer →Tools →Internet Options→Security→Local Intranet→Custom Level→Enable XSS filter→Disable




Sunday, October 26, 2014

Application blocked by security settings


In the Windows environment:

1) First uninstall java

Click Start -->Control Panel --> Add Remove Programs --> Click on Java --> Press Remove Button.

2) Login in to Application and it will display message to you for installing Java, click on start and follow the steps.

The file you are trying to open 'rwservlet' is in different format than specified by the file extension.

In the Windows environment:

1) Start -> Run -> Regedit (to open the registry)
2) Locate the following key path:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\Main\FeatureControl\ FEATURE_ZONE_ELEVATION\iexplore.exe

3) Change the value from '1' to '0'
4) Ensure that the following settings for the .xls & .html file types are unchecked:
a) Open Windows Explorer
b) Goto Tools/Folder Options/File Types tab
c) Select the Extension 'XLS' (or HTML) and Click on the Advanced button
d) Un-check the option 'Confirm Open after Download' & 'Browse in same window'
5) Select 'OK', then 'Apply'

REP-52266: The in-process Reports Server failed to start

Start --> run--> %temp% --> Press Enter and copy path

go to regedit -->oracle_home-->HKEY_LOCAL_MACHINE -->SOFTWARE -->report_temp
Replace the temp path and re start oc4j.




Tuesday, October 21, 2014

Find all objects in schema using column name

SELECT A.OWNER , TABLE_NAME, A.COLUMN_NAME
FROM DBA_TAB_COLUMNS A
WHERE COLUMN_NAME = 'ENAME';

How to find tables having foreign key to a table in Oracle?

SELECT TABLE_NAME, CONSTRAINT_NAME,
STATUS, OWNER
FROM ALL_CONSTRAINTS
WHERE R_OWNER = 'SCOTT'
AND CONSTRAINT_TYPE = 'R'
AND R_CONSTRAINT_NAME IN
(
SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_TYPE IN ('P')
AND TABLE_NAME = 'EMP'
AND OWNER = 'SCOTT'
)
ORDER BY TABLE_NAME, CONSTRAINT_NAME;


How to find table size on Oracle Database?

SELECT segment_name, tablespace_name,
ROUND((BYTEs/1073741824*100)/100) sizeGB,
ROUND((BYTEs/1048576*100)/100) sizeMB,
ROUND((BYTES/1024*100)/100) sizeKB
FROM USER_SEGMENTS
order by sizeKB desc;

Shortcut Keys in Oracle EBS Forms

Function Shortcut Key
Show Shortcut Keys Ctrl + K
Enter Query F11
Execute Query Ctrl + F11
Count Query F12
Clear Block F7
Clear Field F5
Clear Form F8
Clear Record F6
Edit Ctrl + E
Update Record Ctrl + U
Insert Record Ctrl + Down
Delete Record Ctrl + Up
Commit Ctrl + S
Duplicate Field Shift + F5
Duplicate Record Shift + F6
List of values Ctrl + L
List Tab Pages F2
Next Field Tab
Next Record Down Arrow Key
Next Primary Key Shift + F7
Next Set of Records Shift + F8
Previous Field Shift + Tab
Previous Record Up Arrow Key
Previous Block Shift+PageUp
Scroll Down PageDown
Scroll Up PageUp
Up Up Arrow Key
Down Down Arrow Key
Print Ctrl + P
Exit F4
Help Ctrl + H
Block Menu Ctrl + B
Display Error Shift + Ctrl + E


Create Job Groups

Job Groups

The Job Groups are a collection of jobs. Every business group must have a default job group so that all the jobs can be grouped under the same. However in a case where there is a different line of jobs needed, we can go for a new job group altogether.
Defining Job Groups.
Responsibility: US Super HRMS Manager
Navigation: Work Structure -> Job -> Job Group








Job Group Name of the Job Group; should be unique
Job KFF Structure The Job KFF it uses. We might have more than one Job KFFs.
Business Group Add a Business Group. If the “HR:Cross Business Group” profile option is set to ‘Y’, this should populate automatically.
Master Job Group This is to make the Job Group as a Master Job Group. Master Job Groups are used in Oracle Projects to make job mappings. So it is advised to contact to PA consultant before making the Job group a master one.


Monday, October 20, 2014

Create Job Key flexfield

Job

Job is a generic Role within a business group that tells us more about the assignment carried out by the employee.
It is independent of a Division / Department. Like, CEO, Director and CFO are Jobs. Jobs are stored in PER_JOBS table. The Job Id is the primary key here, and acts as the foreign key to the PER_ALL_ASSIGNMENTS_F, to link the job to a particular assignment. So let’s shift our focus on how to create and use a job.

Job Key FlexField

This is the first step. Job KFF stores the basic information related to the jobs available in the firm; like we have job like a CEO, CFO, and Director etc. So this is the place where we define those jobs. The first thing that we need is the list of segments we want to use in the Flex Field. So the question we ask ourselves / the business is, what all do we want to store in a Job? Do we need the job name, the occupational function, the title s/he shares, etc? Once the segments are determined, the next step is to figure out the valid values for each and every segment; and create corresponding value sets.

Now we will define Key Flexfield

Responsibility: Application Developer
Navigation: Flex Field -> Key -> Segments

Steps:
Query (Enter Query (F11) & Execute Query (CTL+F11)) for the FlexField Titled: Job Flex field
Create a new row with appropriate data as defined below in the picture



We have already discussed the steps to create a Key FlexField, so we are not going to revisit that; however we must understand the application of the segments, which will be added to the Job Key FlexField. Now we will click on Segments button and start adding the segments one by one in column field, with a sequence. The name and window prompts are self explanatory. Column is the segment with which the data will actually be stored in the table. We can choose segment 1 to 30. And value set field is the place where we attach our value set. If we wish we can save a segment without a value set that will allow the users to enter any alphanumeric
value in it, up to 150 chars. However it is always advisable to create a value set even if it’s a free text.
We created three segments as given below in the picture.



Once the Segments are defined, close the segments window and freeze the Key FlexField Definition. And Compile it.
Finally run the Run Create Key Flex field Database Items Process. Do not forget to update our lookup types used in our Value sets with available values.


Sunday, October 19, 2014

Block developers from using TOAD and other tools on production databases

This is the AFTER LOGON trigger create ON DATABASE as follows:

CONNECT / AS SYSDBA;

CREATE OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE

DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT program INTO v_prog
FROM sys.v_$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 -- Don't Check SYS Connections
AND ROWNUM = 1; -- Parallel processes will have the same AUDSID's

IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR -- SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
UPPER(v_prog) LIKE '%BUSOBJ%' OR -- Business Objects
UPPER(v_prog) LIKE '%EXCEL%' -- MS-Excel plug-in
THEN
RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
END IF;
END;
/

Step by Step installation of Oracle E-Business Suite R12 on Microsoft Windows 2003 using Oracle Virtual Box.

Software/Hardware Prerequisites:

 Virtual Box
 Windows Server 2003
 Microsoft Visual C++
 Perl (Active Perl)
 MKS Toolkit or Cygwin
 JDK

Oracle R12 E-Business Installer
Hardware: 1 CPU, 2.00 Ghz, with 2 GB of RAM. Oracle E-business suite with Vision
data takes 247.38 GB on disk. Its recommend more disk space should be available if want development tools on the same computer. 45 GB for database, 28 GB for Application Tier without the Vision Database.

Preparing OS/Installing prerequisite software:

1. Install Oracle Virtual Box goes with Virtual Box its free to download on local disk.
2. Install Windows 2003 Server and Install Lan Card driver if not auto plug & play.
3. Alter Installing/updating Windows Server 2003, Change computer name and domain. Set
  computer name as  EBS and domain oracleebs.com

  Right click on ‘My Computer’ > Properties >Computer Name> Change
  Set Computer Name to oracleebs.com
  Click on More Set a Primary DNS Suffix of this Computer to oracleebs.com

4. Edit file C:/windows/system32/drivers/etc/hosts and add (ip address and computer name)

    Example: 192.168.1.177 oracleebs.com

5. Logout from local administrator and login as oracle user before starting oracle E-business      suite installer.

6. Install loopback adaptor

In Control Panel, double-click Add Hardware, and then click Next. Click Yes, I have already connected the hardware, and then click Next.
At the bottom of the Installed hardware list, click Add a new hardware device, and then click Next.
Click Install the hardware that I manually select from a list, and then click Next.
In the Common hardware types list, click Network adapters, and then click Next.
In the Manufacturers list box, click Microsoft.
In the Network Adapter list box, click Microsoft Loopback Adapter, and then click Next.
Click Next to start installing the drivers for your hardware.
Click Finish.
Click Start, click Settings, and then click Network Connections to verify.

7. Download Active Perl and run installer download.

8. Install Microsoft Visual C++ 2008

 Download and start setup for visual studio 2008
 Select custom installation
 Set product install path to c:\mvsc\
 Select C++ and NET Framework SDK only
 Right click on ‘My Computer’ > Properties >Advance>Environment Variables >scroll
down system variables  list > Select Path > Edit > add C:\msv\VC\bin

9. Install Cygwin

You need to install MKS Tool Kit or cygwin. We go with cygwin its free download on
local disk and start cygwin installer (setup.exe ). If we select cygwin then we do not need gnumake or make with cygwin
Start the setup
Select Download without Installing and click Next
Set path where you want to save and click Next
Select Internet Connection type
Choose A Download Site i.e http://mirror.calvin.edu/ or select another from list and click Next
Select all default and following mandatory packages:

All cygwin default
  
  • Archive:
  • all default + zip
      
  • Base:
  • all default + ash/bash, coreutils, diffutils, findutils, gawk, grep, sed, tar and which
      
  • Devel:
  • all default + binutils, gcc, gcc-core, gcc-g++, make and mktemp (if it available)
      
  • Doc:
  • all default + cygwin-doc and man
      
  • Editors:
  • all defaults + vim
      
  • Interpreters:
  • all default + gawk
      
  • Shells:
  • all default + ash/bash and tcsh
      
  • Utils:
  • all default + cygutils and file

    Click Next > Downloading will start wait till completion > click Finish
    Once again start cygwin installer by clicking on setup.exe >
    Select install from local directory > click Next >
    Set root path c:\cygwing and click Next Select local package directory (where you download cygwin packages) click Next>
    Now installer will start click Finish when completed.
    Add C:\cygwing\bin directory to path variable – C:\> set path=%path%; C:\cygwing\bin
    Copy gawk.exe awk.exe (delete existing awk.exe before copying can user xcopy)
    copy grep.exe egrep.exe
    copy make.exe gnumake.exe
    copy gcc.exe cc.exe (delete existing cc.exe before copying)

    10. Download and Install JDK (Java development Kit)

    11. Login at http://edelivery.oracle.com/ > Continue >Select a Product Pack &

    Platform >Go>Oracle E- Business Suite Release 12.1.1 Media Pack for Microsoft Windows (32-bit) Download following:

    Microsoft Windows (32-bit) Rapid Install Start Here (Part 1,2,3,4)
    Microsoft Windows (32-bit) Rapid Install RDBMS – Disk 1,2
    Microsoft Windows (32-bit) Rapid Install Databases – Disk 1,2,3,4,5,6,7
    Microsoft Windows (32-bit) Rapid Install Tools – Disk 1
    Microsoft Windows (32-bit) Rapid Install APPL_TOP – Disk 1,2





    12. Start the installation

       Now just go directory StagesR12 and start installation.
       D:\> cd StageR12\startCD\Disk1\rapidwiz
       D:\> StageR12\startCD\Disk1\rapidwiz> RapidWiz.cmd
       The rapid installation wizard will start, now follow installation wizrad















    Select “Installl Oracle Application Release 12.1.1, then click “Next” botton.















    Skip/Leave blank email id and support passward, if you have not then click “Next” botton.
















    Select the “Create a new configuration” option, then click the “Next” button.
















    Accept the default port pool by clicking the “Next” button.
















    Select the “Vision Demo Database” Enter Database SID, hostname, OS and base directory then click the “Next” button.
     Note: Browse and select correct path for MKS (C:\cygwin\bin) and Microsoft visual studio(c:\msvc\VC\bin)















    1. Click the “Next” button on node information screen
    2. Select the “Suite Licensing” option, then click the “Next” button.
    3. Accept the default products by clicking the “Next” button.
    4. Select the appropriate country functionality, and then click the “Next” button.
    5. Select the appropriate territory and characterset information, and then click the “Next” button.
    6. Check the node configuration settings, and then click the “Next” button.
    7. Accept the node information by clicking the “Next” button.
    8. Wait for the system check status to complete.
    9. Assuming all the system checks were completed successfully, click the “Next” button.
    10. Click the “Next” button on the installation review screen.
    11. Click the “Yes” button to start the installation.
    12. Wait while the installation takes place.
    13. Click the “Finish” button to exit the Rapid Install Wizard.
    Once the installation is complete, the Rapid Installation Portal can be accessed using the following information.

    URL: http://oracleebs.com:8000/OA_HTML/AppsLogin

    Username: SYSADMIN
    Password: SYSADMIN



    Script To get Oracle API’s for any module



    Here is the script used to get the API’s for any module


    select a.owner, a.name, a.type,
              u.status Status, u.last_ddl_time,
              substr(text, 1, 80) Description
       from dba_source a, dba_objects u
     where u.object_name = a.name
        and a.text like '%Header%'
        and a.type = u.object_type
        and a.name like 'AR_%API%' -- you can changed module wise
      order by a.owner, a.name;

    Saturday, October 18, 2014

    HR Look-Ups (Oracle HRMS)

    HR Look Up Codes and Description.

    Navigate to Global HRMS Manager > Other Definitions > Application Utilities Lookups.

    Lookup Code Description
    TITLE Represents TITLE in People Screen.
    SEX Represents Gender in People Screen.
    PERSON_TYPE Represents Person Types in People Screen.
    ORG_TYPE Represents the Organization Type.
    MAR_STATUS Represents Marital Status in People Screen.
    NATIONALITY Represents Nationality in People Screen.
    REGISTERED_DISABLED Represents Registered Disabled in People Screen.
    ADDRESS_TYPE Represents Address Type in People Screen.
    ABSENCE_CATEGORY Represents Absence Category in Absence Screen.
    ABSENCE_REASON Represents Absence Reason in Absence Screen.
    APL_ASSIGN_REASON Represents Assignment Change Reason in Application Screen.
    APL_INTERVIEW_TYPE Represents Interview Type in Interview Screen.
    EMP_EVENT_TYPE Represents Type in Bookings Screen. PER_CM_MTHD Represents Delivery Methods in Communication Delivery Methods Screen.
    PROFICIENCY_SOURCE Represents Source of Proficiency Rating in Competence Profile Screen.
    CERTIFICATION_METHOD Represents Source of Certification Method in Competence Profile Screen.
    CONTACT Represents Relationship Type in Contact Screen.
    CONTRACT_TYPE Represents Contract Type in Contract Screen.
    CONTRACT_STATUS Represents Contract Status in Contract Screen.
    CONTRACT_STATUS_REASON Represents Contract Status Reason in Contract Screen.
    DOCUMENT_STATUS Represents Document Status in Contract Screen.
    CONTRACT_START_REASON Represents Contract Start Reason in Contract Screen.
    CONTRACT_END_REASON Represents Contract End Reason in Contract Screen.
    DISABILITY_CATEGORY Represents Disability Category in Disability Screen.
    DISABILITY_REASON Represents Disability Reason in Disability Screen.
    TERM_APL_REASON Represents Reason for Ending an Application in End Application Screen.
    LEAV_REAS Represents Leaving Reason in End Employment Screen.
    CONSULTATION_TYPE Represents Consultation Type in Medical Assessment Screen.
    CONSULTATION_RESULT Represents Consultation Result in Medical Assessment Screen.
    PHONE_TYPE Represents Phone Type in Phones Screen.
    PER_SUBJECT_STATUSES Represents Status of Qualification in Qualifications Screen.
    PER_SUBJECT Represents Subject in Qualification Screen.
    PER_WORK_HOURS Represents Work Hours in Work Preferences Screen.
    PER_WORK_SCHEDULE Represents Work Schedule in Work Preferences Screen.
    PER_FTE_CAPACITY Represents FTE Capacity in Work Preferences Screen.
    VACANCY_CATEGORY Represents Vacancy Category in Requisition and Vacancy Screen.
    VACANCY_STATUS Represents Vacancy Status in Requisition and Vacancy Screen.
    REC_TYPE Represents Source Type for Recruitment Activity in Requisition and Vacancy Screen.
    FREQUENCY Represents Frequency for Review Salary, Review Performance, and Working Hours in Assignment Screen.
    QUALIFYING UNITS Represents Units for Probation Period and Notice Period in Assignment Screen.
    PQP_EMPLOYER_TYPE Represents Employer Type in Employment History Screen.
    PQP_EMPLOYER_SUBTYPE Represents Employer Sub Type in Employment History Screen.
    DOCUMENT_CATEGORY Represents Document Category in Documents of Record Screen.


    Payroll Table Name and Description

    Table Name Description
    PAY_ACCRUAL_BANDS Length of service bands used in calculating accrual of paid time off.
    PAY_ACCRUAL_BANDS Length of service bands used in calculating accrual of paid time off.
    PAY_ACCRUAL_BANDS Length of service bands used in calculating accrual of paid time off.
    PAY_ACTION_CONTEXTS Assignment Action Contexts.
    PAY_ACTION_INFORMATION Archived data stored by legislation.
    PAY_ACTION_INTERLOCKS Assignment action interlock definitions to control rollback processing..
    PAY_ACTION_PARAMETERS Global parameters to control process execution.
    PAY_ACTION_PARAMETER_GROUPS Groups of Pay Action Parameters.
    PAY_ACTION_PARAMETER_VALUES Values for the specified action parameters.
    PAY_AC_VENDOR_MAPPINGS North American Table to control the mapping of internal Values to External Vendor Values
    PAY_ALL_PAYROLLS_F Payroll group definitions.
    PAY_ASSIGNMENT_ACTIONS Action or process results, showing which assignments have been processed by a specific payroll action, or process.
    PAY_ASSIGNMENT_LATEST_BALANCES Denormalised assignment level latest balances.
    PAY_ASSIGNMENT_LINK_USAGES_F Intersection between PAY_ELEMENT_LINKS_F and PER_ALL_ASSIGNMENTS_F.
    PAY_AU_MODULES Defines the processes that can be executed by the generic code caller.
    PAY_AU_MODULE_PARAMETERS . Defines the parameters associated with the module used in the generic code
    PAY_AU_MODULE_TYPES Defines the module types used in the generic code caller
    PAY_AU_PROCESSES This table defines the processes that can be executed by the generic code caller.
    PAY_AU_PROCESS_MODULES Defines the intersection between processes and modules used by the generic code caller.
    PAY_AU_PROCESS_PARAMETERS Defines the parameters for a process.
    PAY_BACKPAY_RULES Balances to be recalculated by a Retro Pay process.
    PAY_BACKPAY_SETS Identifies backpay, or RetroPay sets.
    PAY_BALANCE_ATTRIBUTES Holds mappings between attributes and defined balances.
    PAY_BALANCE_BATCH_HEADERS Batch header information for balance upload batch.
    PAY_BALANCE_BATCH_LINES Individual batch lines for the balance upload process.
    PAY_BALANCE_CATEGORIES_F Holds seeded categories for balances.
    PAY_BALANCE_CLASSIFICATIONS Information on which element classifications feed a balance.
    PAY_BALANCE_CONTEXT_VALUES Localization balance contexts.
    PAY_BALANCE_DIMENSIONS Information allowing the summation of a balance.
    PAY_BALANCE_FEEDS_F Controls which input values can feed a balance type.
    PAY_BALANCE_SETS Allows related balances to be grouped for reporting purposes.
    PAY_BALANCE_SET_MEMBERS Individual members of the balance set
    PAY_BALANCE_TYPES Balance information.
    PAY_BALANCE_TYPES_EFC This is a copy of the PAY_BALANCE_TYPES table which is populated by the EFC (Euro as a Functional Currency) process.
    PAY_BALANCE_TYPES_TL Translated balance type definitions
    PAY_BALANCE_VALIDATION Balance Validity information
    PAY_BAL_ATTRIBUTE_DEFAULTS Balance attribution defaulted according to values in this table.
    PAY_BAL_ATTRIBUTE_DEFINITIONS Balance attributes help to identify which balances should be usedin which reports.
    PAY_BANK_BRANCHES Stores bank branch information to enable entry of bank account details with the correct branch information (e.g. GB bank, sort code, branch).
    PAY_BATCH_CONTROL_TOTALS Holds user defined control totals for the Batch Element Entry process.
    PAY_BATCH_HEADERS Header information for a Batch Element Entry batch.
    PAY_BATCH_LINES Batch lines for a Batch Element Entry batch.
    PAY_CALENDARS Details of user defined budgetary calendars.
    PAY_CA_EMP_FED_TAX_INFO_F Canadian federal tax information
    PAY_CA_EMP_PROV_TAX_INFO_F Canadian provincial tax information
    PAY_CA_FILE_CREATION_NUMBERS Used by Canadian direct deposit
    PAY_CA_LEGISLATION_INFO Canadian legislation specific data
    PAY_CA_PMED_ACCOUNTS Canadian Provincial Medical account information
    PAY_CE_RECONCILED_PAYMENTS Holds reconciliation information for payments processed through Oracle Cash Management.
    PAY_COIN_ANAL_ELEMENTS Monetary unit quantities for automatic make-up of cash payments.
    PAY_COMPARISON_ROWS .
    PAY_CONSOLIDATION_SETS Consolidation set of results of payroll processing.
    PAY_COSTS Cost details and values for run results.
    PAY_COSTS_EFC This is a copy of the PAY_COSTS table which is populated by the EFC (Euro as a Functional Currency) process.
    PAY_COST_ALLOCATIONS_F Cost allocation details for an assignment.
    PAY_COST_ALLOCATION_KEYFLEX Cost Allocation key flexfield combinations table.
    PAY_CUSTOMIZED_RESTRICTIONS CustomForm restrictions for specific forms.
    PAY_CUSTOM_RESTRICTIONS_TL Translated data for the table PAY_CUSTOMIZED_RESTRICTIONS
    PAY_DATED_TABLES Holds details of datetracked columns
    PAY_DATETRACKED_EVENTS Stores details of events to track on HRMS Datetrack tables
    PAY_DEFINED_BALANCES Intersection between PAY_BALANCE_TYPES and PAY_BALANCE_DIMENSIONS.
    PAY_DIMENSION_ROUTES Stores balance dimension relationships.
    PAY_ELEMENT_CLASSIFICATIONS Element classifications for legislation and information needs.
    PAY_ELEMENT_CLASSIFICATIONS_TL Translated element classification definitions
    PAY_ELEMENT_ENTRIES_F Element entry list for each assignment.
    PAY_ELEMENT_ENTRY_VALUES_F Actual input values for specific element entries.
    PAY_ELEMENT_ENTRY_VALUES_F_EFC This is a copy of the PAY_ELEMENT_ENTRY_VALUES_F table which is populated by the EFC (Euro as a Functional Currency) process.
    PAY_ELEMENT_LINKS_F Eligibility rules for an element type.
    PAY_ELEMENT_SETS Element sets. Used to restrict payroll runs, customize windows, or as a distribution set for costs.
    PAY_ELEMENT_SPAN_USAGES .
    PAY_ELEMENT_TEMPLATES Element Templates
    PAY_ELEMENT_TYPES_F Element definitions.
    PAY_ELEMENT_TYPES_F_EFC This is a copy of the PAY_ELEMENT_TYPES_F table which is populated by the EFC (Euro as a Functional Currency) process.
    PAY_ELEMENT_TYPES_F_TL Translated element definitions
    PAY_ELEMENT_TYPE_EXTRA_INFO Stores extra information for an element
    PAY_ELEMENT_TYPE_INFO_TYPES Types of extra information that may be held against an element.
    PAY_ELEMENT_TYPE_RULES Include and exclude rules for specific elements in an element set.
    PAY_ELEMENT_TYPE_USAGES_F Used to store elements included or excluded from a defined run type.
    PAY_ELE_CLASSIFICATION_RULES Intersection table for PAY_ELEMENT_SETS and PAY_ELEMENT_CLASSIFICATIONS.
    PAY_ELE_PAYROLL_FREQ_RULES Frequency rules for a deduction/payroll combination.
    PAY_ENTRY_PROCESS_DETAILS Internal processing details for certain element entries
    PAY_EVENT_GROUPS Provides grouping for user control of event monitoring
    PAY_EVENT_PROCEDURES Code to execute if event detected.
    PAY_EVENT_QUALIFIERS_F Event Qualification definitions
    PAY_EVENT_UPDATES Process event update transactions
    PAY_EVENT_VALUE_CHANGES_F Values changes that cause an event
    PAY_EXTERNAL_ACCOUNTS Bank account details that enable payments to be made.
    PAY_FILE_DETAILS Report file details that have been saved in the system
    PAY_FORMULA_RESULT_RULES_F Rules for specific formula results.
    PAY_FREQ_RULE_PERIODS Stores frequency rule for a deduction/payroll combination.
    PAY_FR_CONTRIBUTION_USAGES holds the definition of statutory payroll contributions in the French legislation.
    PAY_FUNCTIONAL_AREAS Holds definitions of functional areas
    PAY_FUNCTIONAL_TRIGGERS Defines the triggers contained in a functional area
    PAY_FUNCTIONAL_USAGES Enables functional areas for specific legislations, business groups and payrolls
    PAY_GB_SOY_OUTPUTS Temporary table for GB Start of Year process outputs.
    PAY_GB_TAX_CODE_INTERFACE Interface table for the UK Start of Year process.
    PAY_GB_YEAR_END_ASSIGNMENTS Extraction table for UK End of Year processing, which holds information about assignments.
    PAY_GB_YEAR_END_PAYROLLS Payroll information for the UK EOY process.
    PAY_GB_YEAR_END_VALUES Extraction table for the UK End of Year process that holds information about the NI balances at the year end.
    PAY_GL_INTERFACE Costed details to be passed to the General Ledger
    PAY_GRADE_RULES_F Stores the values for grade or progression point rates.
    PAY_GRADE_RULES_F_EFC This is a copy of the PAY_GRADE_RULES_F table which is populated by the EFC (Euro as a Functional Currency) process.
    PAY_GROSSUP_BAL_EXCLUSIONS Stores balances which will be excluded for gross up by the net to gross process
    PAY_IE_PAYE_DETAILS_F Holds the PAYE Tax Details for an assignment. It is a Date Tracked table.
    PAY_IE_PRSI_DETAILS_F Holds the PRSI Details for an assignment. It is a Date Tracked table.
    PAY_IE_SOCIAL_BENEFITS_F Holds the social benefit details for an assignment.This is a date tracked table
    PAY_IE_TAX_BODY_INTERFACE Interface table used for uploading data into PAYE tables from a flat file.
    PAY_IE_TAX_ERROR Table used to populate errors occured during uploading PAYE details.
    PAY_IE_TAX_HEADER_INTERFACE Interface table used for uploading data into PAYE tables from a flat file.
    PAY_IE_TAX_TRAILER_INTERFACE Interface table used for uploading data into PAYE tables from a flat file.
    PAY_INPUT_VALUES_F Input value definitions for specific elements.
    PAY_INPUT_VALUES_F_EFC This is a copy of the PAY_INPUT_VALUES_F table which is populated by the EFC (Euro as a Functional Currency) process.
    PAY_INPUT_VALUES_F_TL Translated input value definitions
    PAY_ITERATIVE_RULES_F .
    PAY_INPUT_VALUES_F_TL Holds the processing rules of iterative elements.
    PAY_JOB_WC_CODE_USAGES Workers Compensation codes for specific job and state combinations.
    PAY_JP_BANKS This table is used for Japanese bank information.
    PAY_JP_BANK_BRANCHES This table is used for Japanese bank branch information.
    PAY_JP_PRE_TAX This table is a temporary table for Japanese legislative reports.
    PAY_JP_SWOT_NUMBERS Holds Japanese Tax Special Withholding Obligation Taxpayer Numbers.
    PAY_LEGISLATION_CONTEXTS Maps core contexts to legislative names
    PAY_LEGISLATION_RULES Legislation specific rules and structure identifiers.
    PAY_LEGISLATIVE_FIELD_INFO Controls legislative rules on individual form fields
    PAY_LINK_INPUT_VALUES_F Input value overrides for a specific element link.
    PAY_LINK_INPUT_VALUES_F_EFC This is a copy of the PAY_LINK_INPUT_VALUES_F table which is populated by the EFC (Euro as a Functional Currency) process.
    PAY_MAGNETIC_BLOCKS Driving table for fixed format version of the magnetic tape process.
    PAY_MAGNETIC_RECORDS Controls the detailed formatting of the fixed format version of the magnetic tape process.
    PAY_MESSAGE_LINES Error messages from running a process.
    PAY_MONETARY_UNITS Valid denominations for currencies.
    PAY_MONETARY_UNITS_TL Translated data for the table PAY_MONETARY_UNITS_TL
    PAY_MONITOR_BALANCE_RETRIEVALS Monitors the source of balance retrievals
    PAY_MX_EARN_EXEMPTION_RULES_F Used to hold the Earnings exemption rules for Mexico
    PAY_MX_LEGISLATION_INFO_F Mexican legislation specific data
    PAY_NET_CALCULATION_RULES Element entry values which contribute to the net value of Paid Time Off.
    PAY_NL_IZA_UPLD_STATUS Holds the Status of the Data Records in the Processed IZA File
    PAY_ORG_PAYMENT_METHODS_F Payment methods used by a Business Group.
    PAY_ORG_PAYMENT_METHODS_F_EFC This is a copy of the PAY_ORG_PAYMENT_METHODS_F table which is populated by the EFC (Euro as a Functional Currency) process.
    PAY_ORG_PAYMENT_METHODS_F_TL Translated payment method information
    PAY_ORG_PAY_METHOD_USAGES_F Payment methods available to assignments on a specific payroll.
    PAY_PATCH_STATUS Used to track the application of patches.
    PAY_PAYMENT_TYPES Types of payment that can be processed by the system.
    PAY_PAYMENT_TYPES_TL Translated payment type details
    PAY_PAYROLL_ACTIONS Holds information about a payroll process.
    PAY_PAYROLL_ACTIONS_EFC This is a copy of the PAY_PAYROLL_ACTIONS table which is populated by the EFC (Euro as a Functional Currency) process.
    PAY_PAYROLL_GL_FLEX_MAPS Payroll to GL key flexfield segment mappings.
    PAY_PAYROLL_LIST List of payrolls that a secure user can access.
    PAY_PEOPLE_GROUPS People group flexfield information.
    PAY_PERSONAL_PAYMENT_METHODS_F Personal payment method details for an employee.
    PAY_PERSONAL_PAYMENT_METHO_EFC This is a copy of the PAY_PERSONAL_PAYMENT_METHODS_F table which is populated by the EFC (Euro as a Functional Currency) process.
    PAY_PERSON_LATEST_BALANCES Latest balance values for a person.
    PAY_POPULATION_RANGES .
    PERSON_ID ranges for parallel processing.
    PAY_PRE_PAYMENTS Pre-Payment details for an assignment, including the currency, the amount and the specific payment method.
    PAY_PRE_PAYMENTS_EFC This is a copy of the PAY_PRE_PAYMENTS table which is populated by the EFC (Euro as a Functional Currency) process.
    PAY_PROCESS_EVENTS Process event capture table.
    PAY_PROCESS_GROUPS Defines groups of processes
    PAY_PROCESS_GROUP_ACTIONS Processes within the Process Group
    PAY_PSS_TRANSACTION_STEPS Table holding (denormalised) work-in-progress for Payroll Payments self-service.
    PAY_PURGE_ACTION_TYPES Details of the processing order required to purge action types.
    PAY_PURGE_ROLLUP_BALANCES Populated during Purge. Stores details of the balance values being removed.
    PAY_QUICKPAY_EXCLUSIONS List of element entries that are to be excluded from a QuickPay run.
    PAY_QUICKPAY_INCLUSIONS List of element entries that can be included in a QuickPay run.
    PAY_RATES Definitions of pay rates, or pay scales that may be applied to grades.
    PAY_RECORDED_REQUESTS Dated process information.
    PAY_REPORT_FORMAT_ITEMS_F Individual items for the report mapping.
    PAY_REPORT_FORMAT_MAPPINGS_F Maps a report for a given jurisdiction to the fixed format defined for the magnetic tape.
    PAY_REPORT_TOTALS .
    PAY_RESTRICTION_PARAMETERS Restrictions to the rows retrieved by a customized form.
    PAY_RESTRICTION_VALUES The specific values to be used to customize a form.
    PAY_RETRO_ASSIGNMENTS Identifies assignment for reprocessing
    PAY_RETRO_COMPONENTS .
    PAY_RETRO_COMPONENT_USAGES .
    PAY_RETRO_DEFINITIONS .
    PAY_RETRO_DEFN_COMPONENTS . .
    PAY_RETRO_ENTRIES Identifies the Entries required for re-processing.
    PAY_RETRO_NOTIF_REPORTS Populated and used in the RetroNotification Report
    PAY_ROUTE_TO_DESCR_FLEXS Store of routes to Descriptive Flexfields
    PAY_RUN_BALANCES Store of run level balances.
    PAY_RUN_RESULTS Result of processing a single element entry.
    PAY_RUN_RESULT_VALUES Result values from processing a single element entry.
    PAY_RUN_RESULT_VALUES_EFC This is a copy of the PAY_RUN_RESULT_VALUES table which is populated by the EFC (Euro as a Functional Currency) process.
    PAY_RUN_TYPES_F The different types of Payroll Run processing
    PAY_RUN_TYPES_F_TL Translated run type descriptions
    PAY_RUN_TYPE_ORG_METHODS_F Organisation level payment methods associated with a particular run type.
    PAY_RUN_TYPE_ORG_METHODS_F_EFC This is a copy of the PAY_RUN_TYPE_ORG_METHODS_F table which is populated by the EFC (Euro as a Functional Currency) process.
    PAY_RUN_TYPE_USAGES_F Holds child run types where the run type parent is of type Cumulative.
    PAY_SECURITY_PAYROLLS List of payrolls and security profile access rules.
    PAY_SHADOW_BALANCE_CLASS Element Template Shadow Balance Classifications
    PAY_SHADOW_BALANCE_FEEDS Element Template Shadow Balance Feeds
    PAY_SHADOW_BALANCE_TYPES Element Template Shadow Balance Types
    PAY_SHADOW_BAL_ATTRIBUTES .
    PAY_SHADOW_DEFINED_BALANCES Element Template Shadow Defined Balances
    PAY_SHADOW_ELEMENT_TYPES Element Template Shadow Element Type
    PAY_SHADOW_ELE_TYPE_USAGES Element Template Shadow Element Type Usages
    PAY_SHADOW_FORMULAS Element Template Shadow Formulas
    PAY_SHADOW_FORMULA_RULES Element Template Shadow Formula Result Rules
    PAY_SHADOW_GU_BAL_EXCLUSIONS Element Template Grossup Balance Exclusions
    PAY_SHADOW_INPUT_VALUES Element Template Shadow Input Values
    PAY_SHADOW_ITERATIVE_RULES Element Template Shadow Iterative Rules
    PAY_SHADOW_SUB_CLASSI_RULES Element Template Shadow Sub-Classification Rules
    PAY_STATE_RULES US state tax information.
    PAY_STATUS_PROCESSING_RULES_F Assignment status rules for processing specific elements.
    PAY_STAT_TRANS_AUDIT .
    PAY_SUB_CLASSIFICATION_RULES_F .
    Rules to determine the inclusion of an element in a secondary element classification.
    PAY_TAXABILITY_RULES Taxability rules for categories of earnings and deductions.
    PAY_TAXABILITY_RULES_DATES Maintains taxability rules date-effectively, to allow for end-of-year updates.
    PAY_TEMPLATE_CORE_OBJECTS Keeps track of the core schema objects generated from an element template.
    PAY_TEMPLATE_EXCLUSION_RULES Controls the exclusion of an object from an Element Template
    PAY_TEMPLATE_FF_USAGES Allows multiple formulas to be associated with a shadow object.
    PAY_TEMP_BALANCE_ADJUSTMENTS Holds temporary data used by the balance initialization process.
    PAY_TEST_CONTEXTS .
    PAY_TEST_EXPIRY_INFO .
    PAY_TEST_FEED_INFO .
    PAY_TEST_PARAMETERS .
    PAY_TEST_PARAMETER_USAGES .
    PAY_TEST_RUNS .
    PAY_TEST_STAGES .
    PAY_TEST_STEPS .
    PAY_TEST_SUITE_USAGES .
    PAY_TRIGGER_COMPONENTS Stored procedures that are executed from dynamic triggers.
    PAY_TRIGGER_DECLARATIONS Variables declared in dynamic triggers
    PAY_TRIGGER_EVENTS Definitions of triggers that are dynamically generated
    PAY_TRIGGER_INITIALISATIONS How dynamically generated triggers initialise their local variables
    PAY_TRIGGER_PARAMETERS Parameter mappings for dynamically generated procedure and function calls
    PAY_TRIGGER_SUPPORT Allows support package for dynamically generated trigger to be linked to the trigger
    PAY_USER_COLUMNS Column definitions for user defined tables.
    PAY_USER_COLUMN_INSTANCES_F Actual values for each row and column combination in a user defined table.
    PAY_USER_ROWS_F Row definitions for user defined tables.
    PAY_USER_TABLES Table definitions for user defined tables.
    PAY_US_ASG_REPORTING Denormalised performance table for accessing balances.
    PAY_US_CITY_GEOCODES Holds all distinct combinations of state code, county code and city code.
    PAY_US_CITY_NAMES US cities and their VERTEX geocodes, used to validate US addresses.
    PAY_US_CITY_SCHOOL_DSTS VERTEX geocodes for school districts within a city.
    PAY_US_CITY_TAX_INFO_F Holds city tax information.
    PAY_US_CONTRIBUTION_HISTORY Contribution History stores yearly consolidated contribution data for a Person per GRE per contribution type
    PAY_US_COUNTIES US counties and their VERTEX geocodes, used to validate US addresses.
    PAY_US_COUNTY_SCHOOL_DSTS VERTEX geocodes for school districts that span counties.
    PAY_US_COUNTY_TAX_INFO_F Holds county tax information.
    PAY_US_EMP_CITY_TAX_RULES_F City level tax details for employee assignments.
    PAY_US_EMP_COUNTY_TAX_RULES_F County level tax details for employee assignments.
    PAY_US_EMP_FED_TAX_RULES_F Federal level tax details for employee assignments.
    PAY_US_EMP_STATE_TAX_RULES_F State level tax details for employee assignments.
    PAY_US_FEDERAL_TAX_INFO_F Holds federal tax information.
    PAY_US_GARN_ARREARS_RULES_F Used for holding the Garnishment Arrears Rules
    PAY_US_GARN_EXEMPTION_RULES_F Used for holding the Garnishment Exemption Rules
    PAY_US_GARN_FEE_RULES_F Used for holding the Garnishment Fee rules
    PAY_US_GARN_LIMIT_RULES_F Used for holding the Garnishment Limit Rules.
    PAY_US_GEO_UPDATE To keep track of changes made to the user data by the geocode patch.
    PAY_US_MODIFIED_GEOCODES US cities and their VERTEX geocodes, used for showing modified geocodes.
    PAY_US_RPT_TOTALS A temporary table to be used in Payroll and Paper W2 Reports.
    PAY_US_SCHOOL_DSTS_TAX_INFO_F The Federal Liaison Services ( FLS ) agency codes have been mapped to the School District Jurisdiction Information Codes
    PAY_US_STATES US states and their VERTEX geocodes, used to validate US addresses.
    PAY_US_STATE_TAX_INFO_F Holds the state tax information.
    PAY_US_TAX_BALANCES Tax balances used by US Payroll Tax balance reporting.
    PAY_US_TAX_REPORT_BALANCES Identifies tax balances for US Payroll Tax Summary Listing report.
    PAY_US_TAX_REPORT_DIMENSIONS Balance dimensions used by the US Payroll Tax Summary Listing report.
    PAY_US_TAX_TYPES US Tax Types used by US Payroll Tax Balance reporting.
    PAY_US_ZIP_CODES Valid zip code ranges for VERTEX cities, used to validate US addresses.
    PAY_WCI_ACCOUNTS Workers Compensation Accounts table.
    PAY_WCI_OCCUPATIONS Workers Compensation occupations table.
    PAY_WCI_RATES Workers Compensation rates table. NB. This table is currently used for Canadian workers compensation, but is designed to be used globally.
    PAY_WC_FUNDS Workers Compensation Funds table.
    PAY_WC_RATES Workers Compensation Rates table.
    PAY_WC_STATE_SURCHARGES Workers Compensation Surcharges table.
    PAY_ZA_ACB_USER_GEN_NOS Holds the ACB generation number information needed when submitting magnetic tapes to ACB.
    PAY_ZA_BRANCH_CDV_DETAILS Stores Bank and Branch information used to verify Bank account numbers.
    PAY_ZA_CDV_PARAMETERS Holds information needed to perform the CDV on bank account numbers.
    PAY_ZA_IRP5_BAL_CODES Table holds codes for IRP5 balances
    PAY_ZA_TAXREG_BAL_TEMP Table holds balance types for specific assignments
    PAY_ZA_TAX_REGISTERS Used for the South African Tax Register Report.
    PAY_ZA_TAX_TRACES Holds tax trace data calculated during the payroll run. Only the most recent run’s data will be stored.
    PAY_ZA_TEMP_BRANCH_DETAILS Temporary table that holds Bank and Branch information used to verify Bank account numbers, used in the population of PAY_ZA_BRANCH_CDV_DETAILS.
    PAY_ZA_TYS_PROCESSES Stores information about each Tax Year Start Process that was run to ensure that the process will not be run for the same payroll and tax year, and to ensure that a rollback on the process will not occur if the specific

    API to remove responsibilities from a user


    DECLARE

     

    v_user_name VARCHAR2 (100) := 'SHAHANI';

    v_responsibility_name VARCHAR2 (100) := 'US Super HRMS Manager';

    v_application_name VARCHAR2 (100) := NULL;

    v_responsibility_key VARCHAR2 (100) := NULL;

    v_security_group VARCHAR2 (100) := NULL;

    v_description VARCHAR2 (100) := NULL;




    BEGIN
     
     

    SELECT fa.application_short_name,

    fr.responsibility_key,

    frg.security_group_key,

    frt.description,

    frt.responsibility_name

    INTO v_application_name,

    v_responsibility_key,

    v_security_group,

    v_description,



    V_responsibility_name
     
    FROM fnd_responsibility fr,

    fnd_application fa,

    fnd_security_groups frg,



    fnd_responsibility_tl frt
     
    WHERE fr.application_id = fa.application_id

    AND fr.data_group_id = frg.security_group_id

    AND fr.responsibility_id = frt.responsibility_id

    AND frt.LANGUAGE = USERENV ('LANG')

    AND frt.responsibility_name = v_responsibility_name;

    fnd_user_pkg.delresp (username => v_user_name,

    resp_app => v_application_name,

    resp_key => v_responsibility_key,

    security_group => v_security_group

    );

    COMMIT;

      
    DBMS_OUTPUT.put_line ( 'Responsiblity '



    || v_responsibility_name
     
    || ' is removed from the user '



    || v_user_name
     
    || ' Successfully'

    );




    EXCEPTION
     
     

    WHEN OTHERS

    THEN



    DBMS_OUTPUT.put_line
     
    ( 'Error encountered while deleting responsibilty from the user and the error is '

    || SQLERRM

    );




    END;
     
     


     
    SELECT count(1)

    FROM fnd_user_resp_groups furg,

    fnd_user fu,



    fnd_responsibility_tl frt
     
    WHERE furg.user_id = fu.user_id

    AND furg.responsibility_id = frt.responsibility_id

    AND fu.user_name = 'SHAHANI'

    AND frt.responsibility_name = 'US Super HRMS Manager'