PACKAGE BODY: APPS.PAY_US_GROSSUP_UPD

Source

1 PACKAGE BODY pay_us_grossup_upd as
2 /* $Header: pyusntgu.pkb 115.4 2004/01/22 23:17:15 ardsouza noship $ */
3 
4 PROCEDURE setup_grossup_bal(p_baltype_id number, p_baldim_id number)
5 IS
6 
7    l_defbal_id number;
8 
9 BEGIN
10 --
11    BEGIN
12       select defined_balance_id
13         into l_defbal_id
14         from pay_defined_balances
15        where balance_type_id = p_baltype_id
16          and balance_dimension_id = p_baldim_id
17          and legislation_code = 'US';
18 --
19       update pay_defined_balances
20          set grossup_allowed_flag = 'Y'
21        where defined_balance_id = l_defbal_id;
22 
23       hr_utility.trace('Updated defined balance id : '||l_defbal_id);
24 --
25    EXCEPTION
26       when no_data_found then
27 --
28          insert into pay_defined_balances
29                        (defined_balance_id,
30                         balance_type_id,
31                         balance_dimension_id,
32                         legislation_code,
33                         force_latest_balance_flag,
34                         grossup_allowed_flag
35                        )
36           values (pay_defined_balances_s.nextval,
37                   p_baltype_id,
38                   p_baldim_id,
39                   'US',
40                   'N',
41                   'Y');
42      hr_utility.trace('Inserted balance_type_id : '||p_baltype_id);
43      hr_utility.trace('Inserted balance_dim_id : '||p_baldim_id);
44 
45    END;
46 --
47 END setup_grossup_bal;
48 
49 
50 PROCEDURE delete_late_bal(p_start_latest_bal_id number,
51                           p_end_latest_bal_id number,
52                           p_dim_id number)
53 IS
54 
55  l_defined_bal_id NUMBER;
56 --
57 -- Bug 3349650 - Forced index to avoid FTS.
58 --
59  CURSOR get_def_bal IS
60   SELECT /*+ INDEX(pay_defined_balances PAY_DEFINED_BALANCES_FK2) */
61          defined_balance_id
62     FROM pay_defined_balances
63    WHERE balance_dimension_id = p_dim_id;
64 
65  CURSOR get_latest_bal IS
66    SELECT latest_balance_id
67      FROM pay_assignment_latest_balances
68     WHERE latest_balance_id BETWEEN p_start_latest_bal_id
69                                 AND p_end_latest_bal_id
70       AND defined_balance_id = l_defined_bal_id;
71 
72  l_count NUMBER := 0;
73 
74 BEGIN
75 
76  FOR def_bal_rec in get_def_bal LOOP
77    l_defined_bal_id := def_bal_rec.defined_balance_id;
78 
79    FOR latebal_rec in get_latest_bal LOOP
80 
81     l_count := l_count + 1;
82 
83    delete from pay_balance_context_values
84     where latest_balance_id = latebal_rec.latest_balance_id;
85 
86    --hr_utility.trace('Deleted context value '||latebal_rec.latest_balance_id);
87 
88 --
89    delete from pay_assignment_latest_balances
90     where latest_balance_id = latebal_rec.latest_balance_id;
91 
92    --hr_utility.trace('Deleted asg late bal '||latebal_rec.latest_balance_id);
93 
94    if l_count = 100 then
95      l_count := 0;
96      commit;
97    end if;
98 
99    END LOOP;
100  END LOOP;
101 
102 END delete_late_bal;
103 
104 END pay_us_grossup_upd;