1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Delete script in apps

Discussion in 'Oracle Apps Technical' started by vamsioracle, Mar 28, 2009.

  1. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    Hi all,

    I have a requirement to write a delete script in HRMS. First i will the scenario:

    My client has created multiple assignments for few employees and tried to delete them. for some reason a few records were not able to delete. They raised a SR with Oracle. Oracle said, it is no way responsible for data entered by end user and provided an alternative solution. They asked to write a delete script to delete those particular records manually.

    How to write a delete script for apps tables for a record.
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    hi vamsi, you realise it's impossible to say anything based on the information you have given. My question is can't those assignments be end-dated? isn't there an option for effective end date or something? why do you have to delete records?

    Ok and if you want to delete a few records from apps tables. Which table?

    Try this,

    Open the form where the records can be seen.
    Goto View--> Record History to find which table (or view) stores the records
    Open that table or view and take a look at how the recoords are organized.
    If it's a view check out the script for related tables.
    DO NOT DELETE ANYTHING FROM THE BACKEND unless you are ABSOLUTELY SURE.
    And always keep a backup of the records which you delete so that you can insert them if something goes wrong.
     
  3. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    Hi sadik

    I need to delete teh assignment details for a few employees, which i am unable to end date or delete in application.
     
  4. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    hi vamsi do you want to delete the employees?

    If yes, you cannot do so if a payroll has been run for the employee you want to delete. If you verify that a payroll has not been run you can delete an employee as follows:

    1. Navigate to the HRMS Manager Responsibility--> People/Delete Personal Records

    2. Query the employee you would like to delete.

    3. Click on the [Delete Person] button.

    Simple.
     
  5. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    However if you just made a mistake and an assignment change was done on wrong date, then you can delete the assignment as

    1. Goto your HRMS Manager Responsibility > People enter and Maintain > query for the employee that has the issue. In the Assignment Form, date track to the Employee Effective Start Date.

    2. Update the Organization to the correct Organization name.

    3. Click Save, select Correct.

    Now the Assignment Table may contain 2 records for the Employee BUT the first record now is with the correct Organization and on the correct date.

    In order to delete the second record,

    4. Date track to the Employee Effective Start Date + 1 day.

    5. Click on the Delete icon in the Toolbar.

    6. Select Next.

    7. Save.

    Now only the correct record exists for the Employee in the Assignment Table
     
  6. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    Hi all

    My client has done a data error.

    There was an assignment till 15-jan-2008. Now on 16th jan 2008,they thought to change the assigment effective from 02-jan-2008 for an employee and created an assignment for effective start date 02-jan-2008. This new assignment automatically end dated on 15-jan-2008, because there is a record (active) for 16th. so the client in order to make the new assigment extended created two time for effective date 16th jan-2008.

    this has messed up a lot and now they are unable to eitehr end date or delete the records for 16th jan, for some reason.

    I queried per_all_assignments_f table for the assignemtn id and effective_start_date >01-jan-2008. Thre are two record.

    one started on 2nd jan and end dated on 15th and the other started on 16th jan and end dated 12/31/4712(default end date). Now i want to delete the second record and make teh effective end date of the first assignment to 12/31/4712.

    Vamsi
     
  7. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    hi vamsi, in this case i would advise you that you first delete the second record from the frontend. Next you look at the create script for the PER_ASSIGNMENTS_V View. Take a look at all the tables that this view queries from. Wherever the assignment effective start date and effective end date columns are mentioned, backup those tables, and update the effective end date.

    Now go to the fron end and see if your assignment record is as you want it. If you get any error or problem let us know here. In either case you can always restore the values from your backup.