This content has been marked as final. Show 3 replies
Your SQL isn't bad, although you're including loads of tables you don't need to so it's probably not as fast as it could be. I'm not sure you know what element entry to update so let's look at an example:
Here the one element entry (Id 123) has 4 effective rows. The employee gets $400 in Jan 2009, $500 from Feb 2009 to mid-June 2011, $600 for a few days later in June 2011 and then $700 from 26th June 2011 onwards.
-- | Element Entry Id | OVN | Start Date | End Date | Pay Value | -- +------------------+-----+------------+-------------+-----------+ -- | 123 2 1-Jan-2009 31-Jan-2011 USD 400 -- | 123 1 1-Feb-2009 18-Jun-2011 USD 500 -- | 123 1 19-Jun-2011 25-Jun-2011 USD 600 -- | 123 5 26-Jun-2011 31-Dec-4712 USD 700
So your question to the forum is which one do I update. Well, that depends which one you want to update. If you want to update the 1st Jan 2009 to 31st Jan 2011 you'd pass element_entry_id => 123, ovn => 2, effective_date => to_date('1-Jan-2009', 'DD-MON-YYYY') and datetrack_mode => 'CORRECTION'.
If you wanted to update the $700 row you'd pass OVN 5 and an effective date of 26th June 2011. And so-on.
Now in your SQL you're filtering based on sysdate, which means you'd be updating the $500 row (ovn=1). Is that what you want?
I have simplified your SQL as follows:
This selects both the entry Id and OVN in one go; there's no need to have 2 SQL statements. If multiple entries are allowed, this SQL could return more than one row so just watch out for that. If you don't want the row as of sysdate change the two sysdate joins accordingly.
SELECT pee.element_entry_id ,pee.object_version_number INTO x_element_id_mgr ,x_ele_object_version_number FROM pay_element_entries_f pee ,pay_element_types_f pet WHERE pee.assignment_id = c_staging.assignment_id AND pee.element_type_id = pet.element_type_id AND pet.element_name = 'Mgr Rec Pct' AND trunc(sysdate) BETWEEN pee.effective_start_date AND pee.effective_end_date AND trunc(sysdate) BETWEEN pet.effective_start_date AND pet.effective_end_date;
I hope that helps.
No problem. Then you just tweak the SQL as follows:
Note we're ensuring that the SQL only returns the latest version of the element entry through max(pee2.effective_start_date).
SELECT pee.element_entry_id ,pee.object_version_number INTO x_element_id_mgr ,x_ele_object_version_number FROM pay_element_entries_f pee ,pay_element_types_f pet WHERE pee.assignment_id = c_staging.assignment_id AND pee.element_type_id = pet.element_type_id AND pet.element_name = 'Mgr Rec Pct' AND pee.effective_start_date = (SELECT max(pee2.effective_start_date) FROM pay_element_entries_f pee2 WHERE pee2.element_entry_id = pee.element_entry_id) AND pee.effective_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date;