1 PACKAGE pay_fr_sick_pay_processing AS
2 /* $Header: pyfrsppr.pkh 120.1 2005/08/29 07:37:49 ayegappa noship $ */
3 --
4 --
5 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
6 -- Constants for the types of guarantee (numeric).
7 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
8 --
9 cs_GN CONSTANT NUMBER := 10; -- Garantie au Net
10 cs_LE CONSTANT NUMBER := 20; -- Legal
11 cs_CA_G_ADJ CONSTANT NUMBER := 30; -- Collectively agreed gross with adjustment
12 cs_CA_G_NOADJ CONSTANT NUMBER := 40; -- Collectively agreed gross without adjustment
13 cs_CA_N CONSTANT NUMBER := 50; -- Collectively agreed net
14 cs_NO_G CONSTANT NUMBER := 60; -- No guarantee
15 cs_FINAL CONSTANT NUMBER := 100; -- Final Run
16 --
17 --
18 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
19 -- Custom data types
20 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
21 --
22
23 TYPE t_asg IS RECORD
24 (assignment_id NUMBER
25 ,sick_net NUMBER
26 ,base_net NUMBER
27 ,assignment_action_id NUMBER
28 ,action_start_date DATE
29 ,action_end_date DATE
30 ,business_group_id NUMBER
31 ,payroll_action_id NUMBER
32 ,payroll_id NUMBER
33 ,element_type_id NUMBER
34 ,deduct_formula NUMBER
35 ,ded_ref_salary NUMBER
36 ,lg_ref_salary NUMBER);
37
38 --
39
40 TYPE t_absence_arch IS RECORD
41 (element_entry_id NUMBER
42 ,date_earned DATE
43 ,ID NUMBER
44 ,sick_deduction NUMBER
45 ,sick_deduction_rate NUMBER
46 ,sick_deduction_base NUMBER
47 ,sick_deduct_start_date DATE
48 ,sick_deduct_end_date DATE
49 ,ijss_net NUMBER
50 ,ijss_estimated VARCHAR2(1)
51 ,ijss_payment NUMBER
52 ,parent_absence_id NUMBER
53 ,gi_adjustment NUMBER
54 ,gross_ijss_adjustment NUMBER
55 -- added for days paid balances
56 ,partial_paid_days NUMBER
57 ,unpaid_days NUMBER
58 );
59
60 --
61 TYPE t_coverage IS RECORD
62 (g_type NUMBER
63 ,cagr_id NUMBER
64 ,gi_payment1 NUMBER
65 ,gi_rate1 NUMBER
66 ,gi_days1 NUMBER
67 ,gi_from_date1 DATE
68 ,gi_to_date1 DATE
69 ,gi_payment2 NUMBER
70 ,gi_rate2 NUMBER
71 ,gi_days2 NUMBER
72 ,gi_from_date2 DATE
73 ,gi_to_date2 DATE
74 ,sick_adj NUMBER
75 ,net NUMBER
76 ,ijss_gross1 NUMBER
77 ,ijss_gross_rate1 NUMBER
78 ,ijss_gross_days1 NUMBER
79 ,ijss_from_date1 DATE
80 ,ijss_to_date1 DATE
81 ,ijss_gross2 NUMBER
82 ,ijss_gross_rate2 NUMBER
83 ,ijss_gross_days2 NUMBER
84 ,ijss_from_date2 DATE
85 ,ijss_to_date2 DATE
86 ,band1 NUMBER
87 ,band2 NUMBER
88 ,band3 NUMBER
89 ,band4 NUMBER
90 ,best_method VARCHAR2(1)
91 ,processed_flag VARCHAR2(1)
92 ,previous_net NUMBER
93 ,previous_gi_payment NUMBER
94 ,previous_sick_adj NUMBER
95 ,previous_ijss_gross NUMBER
96 ,ijss_net_adjustment NUMBER);
97
98 --
99 TYPE t_coverages IS TABLE OF t_coverage INDEX BY BINARY_INTEGER;
100 --
101 TYPE t_rule IS RECORD
102 (net NUMBER
103 ,repeat NUMBER
104 ,stop NUMBER);
105 --
106 TYPE t_rules IS TABLE OF t_rule INDEX BY BINARY_INTEGER;
107 --
108 TYPE t_iter_rule IS RECORD
109 (ijss_payment VARCHAR2(1)
110 ,deduct_for_sick VARCHAR2(1)
111 ,gi_payment VARCHAR2(1)
112 ,sick_adj VARCHAR2(1)
113 ,gi_adj VARCHAR2(1)
114 ,gross_ijss_adj VARCHAR2(1));
115
116 --
117 TYPE t_iter_rules IS TABLE OF t_iter_rule INDEX BY BINARY_INTEGER;
118 --
119 TYPE t_ctl IS RECORD
120 (g_idx NUMBER
121 ,iter NUMBER
122 ,p_mode NUMBER
123 ,audit_g_idx NUMBER);
124 --
125 TYPE t_guarantee_type_lookups IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
126 --
127 FUNCTION iterate
128 (p_assignment_id NUMBER
129 ,p_element_entry_id NUMBER
130 ,p_date_earned DATE
131 ,p_assignment_action_id NUMBER
132 ,p_business_group_id NUMBER
133 ,p_payroll_action_id NUMBER
134 ,p_payroll_id NUMBER
135 ,p_element_type_id NUMBER
136 ,p_net_pay NUMBER
137 ,p_deduct_formula NUMBER
138 ,p_action_start_date DATE
139 ,p_action_end_date DATE
140 ,p_ded_ref_salary NUMBER
141 ,p_lg_ref_salary NUMBER
142 ,p_stop_processing OUT NOCOPY VARCHAR2) RETURN NUMBER;
143
144
145 FUNCTION indirects
146 (
147 P_ABSENCE_ID OUT NOCOPY NUMBER,
148 P_ijss_net OUT NOCOPY NUMBER,
149 P_ijss_payment OUT NOCOPY NUMBER,
150 P_ijss_gross1 OUT NOCOPY NUMBER,
151 P_ijss_gross2 OUT NOCOPY NUMBER,
152 p_ijss_estmtd OUT NOCOPY VARCHAR2,
153 P_ijss_gross1_rate OUT NOCOPY NUMBER,
154 P_ijss_gross2_rate OUT NOCOPY NUMBER,
155 P_ijss_gross1_base OUT NOCOPY NUMBER,
156 P_ijss_gross2_base OUT NOCOPY NUMBER,
157 P_ijss_gross1_fromdate OUT NOCOPY DATE,
158 P_ijss_gross2_fromdate OUT NOCOPY DATE,
159 P_ijss_gross1_todate OUT NOCOPY DATE,
160 P_ijss_gross2_todate OUT NOCOPY DATE,
161 P_gi_payment1 OUT NOCOPY NUMBER,
162 P_gi_payment2 OUT NOCOPY NUMBER,
163 P_gi_payment1_rate OUT NOCOPY NUMBER,
164 P_gi_payment2_rate OUT NOCOPY NUMBER,
165 P_gi_payment1_base OUT NOCOPY NUMBER,
166 P_gi_payment2_base OUT NOCOPY NUMBER,
167 P_gi_payment1_fromdate OUT NOCOPY DATE,
168 P_gi_payment2_fromdate OUT NOCOPY DATE,
169 P_gi_payment1_todate OUT NOCOPY DATE,
170 P_gi_payment2_todate OUT NOCOPY DATE,
171 P_sick_adj OUT NOCOPY NUMBER,
172 P_sick_deduct OUT NOCOPY NUMBER,
173 P_sick_deduct_rate OUT NOCOPY NUMBER,
174 P_sick_deduct_base OUT NOCOPY NUMBER,
175 P_gi_adjustment OUT NOCOPY NUMBER,
176 P_gross_ijss_adj OUT NOCOPY NUMBER,
177 P_audit OUT NOCOPY VARCHAR2,
178 P_deduct_start_date OUT NOCOPY DATE,
179 P_deduct_end_date OUT NOCOPY DATE,
180 -- added for paid days balances
181 p_red_partial_days OUT NOCOPY NUMBER,
182 p_red_unpaid_days OUT NOCOPY NUMBER,
183 -- Obtains the current Rate Input value 4504304
184 p_net OUT NOCOPY NUMBER
185 --
186 ) RETURN NUMBER ;
187
188
189 FUNCTION audit
190 (p_parent_abs_id OUT NOCOPY NUMBER
191 ,p_guarantee_type OUT NOCOPY VARCHAR2
192 ,p_cagr_id OUT NOCOPY NUMBER
193 ,p_net OUT NOCOPY NUMBER
194 ,p_gi_payment OUT NOCOPY NUMBER
195 ,p_band1 OUT NOCOPY NUMBER
196 ,p_band2 OUT NOCOPY NUMBER
197 ,p_band3 OUT NOCOPY NUMBER
198 ,p_band4 OUT NOCOPY NUMBER
199 ,p_best_method OUT NOCOPY VARCHAR2
200 ,p_sick_adjustment OUT NOCOPY NUMBER
201 ,p_gi_adjustment OUT NOCOPY NUMBER
202 ,p_gross_ijss_adj OUT NOCOPY NUMBER
203 ,p_ijss_gross OUT NOCOPY NUMBER
204 ,p_audit OUT NOCOPY VARCHAR2
205 ,p_payment_start_date OUT NOCOPY DATE
206 ,p_payment_end_date OUT NOCOPY DATE
207 ) RETURN NUMBER ;
208
209 FUNCTION get_guarantee_id(
210 P_type IN VARCHAR2 ) RETURN NUMBER ;
211
212 END pay_fr_sick_pay_processing;