[Home] [Help]
123: -- type-compare in remove/transfer row
124: -- (local) procedures between hr_application
125: -- ownerships and surrogate keys.
126: -- 110.5 A.Mills 30-Sep-1998 Added extra validation on delete
127: -- from pay_balance_feeds_f to ensure
128: -- the legislative rows aren't deleted
129: -- incorrectly.
130: -- 110.6 I. Harding 16/11/98 Commented out calls that insert lookups.
131: -- 110.7 M.Reid 27/01/99 Tidied up balance checks for visible
202: -- feeds (thereby avoiding needless loss
203: -- of latest balances). We don't now delete
204: -- from hr_s_balance_feeds_f ... we only
205: -- select back those feeds that don't match
206: -- rows in pay_balance_feeds_f instead.
207: -- Bug 1490386.
208: -- 115.40 amills 21-Dec-2000 1550308. Handle exception whereby
209: -- legislative user rows on live table
210: -- have same row_low_range_or_name or
605: and pdb.balance_type_id = pbt.balance_type_id
606: and replace(ltrim(rtrim(upper(pbt.BALANCE_NAME))), ' ', '_') = replace(ltrim(rtrim(upper(P_SURROGATE_KEY_NAME))), ' ', '_')
607: and replace(ltrim(rtrim(upper(pbd.dimension_name))), ' ', '_') = replace(ltrim(rtrim(upper(P_ADDL_SURROGATE_KEY_NAME1))), ' ', '_'));
608:
609: elsif l_table_name='PAY_BALANCE_FEEDS_F' then
610:
611: Select 'Y' into v_exists from dual where exists(
612: select null
613: from pay_balance_feeds_f bf,
609: elsif l_table_name='PAY_BALANCE_FEEDS_F' then
610:
611: Select 'Y' into v_exists from dual where exists(
612: select null
613: from pay_balance_feeds_f bf,
614: pay_input_values_f piv,
615: pay_balance_types bt
616: where piv.input_value_id = bf.INPUT_VALUE_ID
617: and bt.balance_type_id = bf.balance_type_id
1766:
1767: CURSOR feed (bal_type_id number)
1768: IS
1769: -- Cursor to install child balance feeds
1770: -- that don't already exist in pay_balance_feeds_f
1771:
1772: select distinct *
1773: from hr_s_balance_feeds_f hrs
1774: where hrs.balance_type_id = bal_type_id
1773: from hr_s_balance_feeds_f hrs
1774: where hrs.balance_type_id = bal_type_id
1775: and not exists (
1776: select null
1777: from pay_balance_feeds_f pbf
1778: where pbf.balance_type_id = hrs.balance_type_id
1779: and pbf.input_value_id = hrs.input_value_id
1780: and pbf.effective_start_date = hrs.effective_start_date
1781: and pbf.effective_end_date = hrs.effective_end_date);
2635: -- Start by deleting balance feeds currently installed
2636:
2637: --
2638: -- This statement removes any balance feeds from
2639: -- pay_balance_feeds_f that belong to a balance_type_id
2640: -- in hr_s_balance_types but do not exist in
2641: -- hr_s_balance_feeds_f.
2642:
2643: --PSEUDO
2642:
2643: --PSEUDO
2644: if hr_legislation.g_pseudo_enabled = 'Y' THEN
2645: -- only delete feeds that are Oracle seeded as pseudo seeded won't be redelivered
2646: delete from pay_balance_feeds_f pbf
2647: where pbf.balance_type_id = stu_rec.c_surrogate_key
2648: and pbf.business_group_id is null
2649: and pbf.legislation_code is not null
2650: AND pbf.created_by <= 2
2658: and pbf.effective_start_date = hrs.effective_start_date
2659: and pbf.effective_end_date = hrs.effective_end_date);
2660:
2661: ELSE
2662: delete from pay_balance_feeds_f pbf
2663: where pbf.balance_type_id = stu_rec.c_surrogate_key
2664: and pbf.business_group_id is null
2665: and pbf.legislation_code is not null
2666: and pbf.balance_feed_id <= l_initbfid
2700: HRASSACT.CHECK_LATEST_BALANCES := FALSE;
2701: end if;
2702:
2703:
2704: insert into pay_balance_feeds_f
2705: (balance_feed_id
2706: ,effective_start_date
2707: ,effective_end_date
2708: ,business_group_id