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!

sql help

Discussion in 'SQL PL/SQL' started by neelambhumij, Jul 31, 2014.

  1. neelambhumij

    neelambhumij Active Member

    Messages:
    51
    Likes Received:
    2
    Trophy Points:
    160
    Location:
    JOHANNESBURG
    Hi

    need a help on this sql query....
    basic query is--


    select person_id,employee_number

    from per_all_people_f

    where trunc(last_update_date)= trunc(sysdate)



    -----


    but i have to make... like


    select person_id,employee_number

    from per_all_people_f

    where trunc(last_update_date)= trunc(sysdate)

    +

    not exist

    if der is change on attribute14 only(no change on any other column)


    here we will be doing a mass update on attribute 14 and i didnot want to trigger the above query..
    Any Help is appreciated .


    Thanks and Regards
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    provide here the full text of query(trigger,procedure and etc) and formulate full conditions of your task
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I wonder how you expect to discover if attribute14 has been changed in this table. Is there an update_dt column? Is there some sort of transactional history in place for this table? Oracle does not keep such information so if you don't implement some way to track changes you won't know which rows need to be updated.
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Neelam,

    Isn't this post rather for Oracle Apps. technical forum (as per_all_people_f table belongs to Oracle HRMS and your requirement is quite confusing)?

    You haven't described your issue clearly. If I may venture into Oracle Apps. world, there are database triggers (audit triggers) to update last_update_date and updated_by type columns.

    Correct me if I'm wrong, but if I guess what you wish to achieve is that you don't want these triggers to fire when you update ATTRIBUTE14.

    Oracle Apps. provides a feature - a global variable "hr_general.g_data_migrator_mode" which prevents the trigger from firing when set to "Y".

    So in your script, just add:

    hr_general.g_data_migrator_mode := 'Y';

    before your updates.
     
    neelambhumij likes this.
  5. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    By virtue of mentioning Attribute14 indicates the use of flexfields. I certainly hope you are not considering a direct sql update of the EBS HR table. There are APIs for exactly that.

    Also, your query is missing the join condition to the context lookup defining the flexfield to which Attribute14 applies to w/ respect to the data that's physically there. So how will you know if the Attribute14 value you're going to update actually applies to the data record in question? If you have no idea what that means...plz don't proceed any further. Go find an EBS developer/dba.

    And Rajen is right...this is an EBS forum question.

    HTH

    CJ
     
    neelambhumij likes this.
  6. neelambhumij

    neelambhumij Active Member

    Messages:
    51
    Likes Received:
    2
    Trophy Points:
    160
    Location:
    JOHANNESBURG
    Hi All

    Thanks For your suggestions... Appology for posting here...I only wanted to know the update dates on columns through sql perspective...
    But fortunately all these r making me more clear on this requirement.

    Thanks CJ ,I wont be creating any direct update....using hr_person_api for the mass update.

    Thanks Rajen for this valuable info.
    i ll be using hr_general.g_data_migrator_mode := 'Y'; befor the mass update....
    ok...
    Can I create a before update trigger of attribute12 on per_all_people_f and include hr_general.g_data_migrator_mode := 'Y' only in the sql stattement block.
    (wondering if any body changes the personal information dff from front end.)
    Will it be helpful..bcoz per_all_people_f_who is also a before insert/update trigger..
    Please rectify even if it goes silly.


    Thanks and Regards
     
  7. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Methinks you should avoid creating or customizing the trigger on per_all_people_f. That sets a bad precedent. But that's just me coming from a background where the client customized darn near everything...and is now paying for it...in spades.

    IMO...you need to ask yourself why you need a trigger on the table for that column value. Would it be better to code a custom PL/SQL process that accomplishes the same thing w/o mucking about w/ the delivered architecture? Then register that process and call as needed.

    HTH

    CJ