1 PACKAGE BODY PAY_NO_BAL_UPLOAD AS
2 /* $Header: pynobalupl.pkb 120.0.12000000.1 2007/05/22 06:14:46 rajesrin noship $ */
3
4 -- Date Constants
5 START_OF_TIME constant date := to_date('01/01/0001','DD/MM/YYYY');
6 END_OF_TIME constant date := to_date('31/12/4712','DD/MM/YYYY');
7
8 ----------------------------------------------------------------------------
9 -- Name : EXPIRY_DATE
10 -- Type : FUNCTION
11 -- Access : Public
12 -- Description : Function to return the expiry date of the given
13 -- balance dimension relative to a date.
14 -- Arguments :
15 -- IN :
16 -- p_upload_date - the date on which the balance should be correct.
17 -- p_dimension_name - the dimension being set.
18 -- p_assignment_id - the assignment involved.
19 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
20 -- OUT : N/A
21 -- RETURN : Date
22 --
23 -- NOTES
24 -- This is used by pay_balance_upload.dim_expiry_date.
25 -- If the expiry date cannot be derived then it is set to the end of time
26 -- to indicate that a failure has occured. The process that uses the
27 -- expiry date knows this rulw and acts accordingly.
28 --------------------------------------------------------------------------
29
30 FUNCTION expiry_date
31 (p_upload_date IN DATE,
32 p_dimension_name IN VARCHAR2,
33 p_assignment_id IN NUMBER,
34 p_original_entry_id IN NUMBER)
35 RETURN DATE IS
36
37 -- Returns the start date of the current period on the upload date.
38 CURSOR csr_period_start( p_assignment_id NUMBER
39 , p_upload_date DATE
40 ) IS
41 SELECT NVL(PTP.start_date, END_OF_TIME)
42 FROM per_time_periods PTP
43 ,per_assignments_f ASS
44 WHERE ASS.assignment_id = p_assignment_id
45 AND p_upload_date BETWEEN ASS.effective_start_date AND ASS.effective_end_date
46 AND PTP.payroll_id = ASS.payroll_id
47 AND p_upload_date BETWEEN PTP.start_date AND PTP.end_date;
48
49 CURSOR csr_asg_start_date( p_assignment_id NUMBER
50 , p_upload_date DATE
51 , p_expiry_date DATE
52 ) IS
53 SELECT NVL(GREATEST(MIN(ASS.effective_start_date), p_expiry_date),END_OF_TIME)
54 FROM per_assignments_f ASS
55 WHERE ASS.assignment_id = p_assignment_id
56 AND ASS.effective_start_date <= p_upload_date
57 AND ASS.effective_end_date >= p_expiry_date;
58
59
60 l_prd_start_date DATE; -- Holds the start of the period for the upload date.
61 l_expiry_date DATE; -- Holds the expiry date of the dimension.
62 l_cal_yr_start_date DATE; -- Holds the start date of the calendar year for the upload date.
63 l_bi_month_start_date DATE; -- Holds the start date of the bi-monthly period for the upload date.
64
65 BEGIN
66 -- Calculate the expiry date for the specified dimension relative to the
67 -- upload date, taking into account any contexts where appropriate. Each of
68 -- the calculations also takes into account when the assignment is on a
69 -- payroll to ensure that a balance adjustment could be made at that point
70 -- if it were required.
71
72 hr_utility.trace('Entering pay_no_bal_upload.expiry_date');
73
74 IF p_dimension_name IN ('ASSIGNMENT ELEMENT CODE PERIOD TO DATE')
75 THEN
76
77 OPEN csr_period_start( p_assignment_id
78 , p_upload_date
79 );
80 FETCH csr_period_start
81 INTO l_prd_start_date;
82 CLOSE csr_period_start;
83 --
84
85 OPEN csr_asg_start_date( p_assignment_id
86 , p_upload_date
87 , l_prd_start_date
88 );
89 FETCH csr_asg_start_date
90 INTO l_expiry_date;
91 CLOSE csr_asg_start_date;
92
93 ELSIF p_dimension_name IN ('ASSIGNMENT ELEMENT CODE YEAR TO DATE') THEN
94 l_cal_yr_start_date := TRUNC(p_upload_date,'Y');
95 --
96 OPEN csr_asg_start_date( p_assignment_id
97 , p_upload_date
98 , l_cal_yr_start_date
99 );
100 FETCH csr_asg_start_date
101 INTO l_cal_yr_start_date;
102 CLOSE csr_asg_start_date;
103
104 l_expiry_date := l_cal_yr_start_date;
105
106 ELSIF p_dimension_name IN ('ASSIGNMENT BI-MONTHLY TO DATE') THEN
107
108 l_bi_month_start_date := trunc(Add_months(p_upload_date,MOD(TO_NUMBER(TO_CHAR(p_upload_date,'MM')),2)-1),'MM');
109
110 OPEN csr_asg_start_date( p_assignment_id
111 , p_upload_date
112 , l_bi_month_start_date
113 );
114 FETCH csr_asg_start_date
115 INTO l_bi_month_start_date;
116 CLOSE csr_asg_start_date;
117 l_expiry_date := l_bi_month_start_date;
118
119
120 END IF;
121
122 RETURN l_expiry_date;
123
124 hr_utility.trace('Leaving pay_no_bal_upload.expiry_date ');
125
126 EXCEPTION
127 WHEN no_data_found THEN
128 l_expiry_date := END_OF_TIME;
129 RETURN l_expiry_date;
130
131 END expiry_date;
132
133 --------------------------------------------------------------------------
134 -- Name : IS_SUPPORTED
135 -- Type : FUNCTION
136 -- Access : Public
137 -- Description : Function to check if the specified dimension is
138 -- supported by the upload process.
139 --
140 -- Arguments :
141 -- IN : p_dimension_name - the balance dimension to be checked.
142 -- OUT : N/A
143 -- RETURN : Number
144 --
145 -- NOTES
146 -- Only a subset of the NO dimensions are supported.
147 -- This is used by pay_balance_upload.validate_dimension.
148 --------------------------------------------------------------------------
149
150 FUNCTION is_supported
151 (p_dimension_name varchar2)
152 RETURN number IS
153
154 BEGIN
155 hr_utility.trace('Entering pay_no_bal_upload.is_supported');
156
157 IF p_dimension_name in ('ASSIGNMENT ELEMENT CODE PERIOD TO DATE'
158 ,'ASSIGNMENT ELEMENT CODE YEAR TO DATE'
159 ,'ASSIGNMENT BI-MONTHLY TO DATE') THEN
160 RETURN 1;
161 ELSE
162 RETURN 0;
163 END IF;
164 hr_utility.trace('Leaving pay_no_bal_upload.is_supported ');
165 END is_supported;
166
167 --------------------------------------------------------------------------
168 -- Name : INCLUDE_ADJUSTMENT
169 -- Type : FUNCTION
170 -- Access : Public
171 -- Description : Given a dimension, and relevant contexts and details of an existing
172 -- balanmce adjustment, it will find out if the balance adjustment effects
173 -- the dimension to be set. Both the dimension to be set and the adjustment
174 -- are for the same assignment and balance. The adjustment also lies between
175 -- the expiry date of the new balance and the date on which it is to set.
176 --
177 -- Arguments :
178 -- IN :
179 -- p_balance_type_id - the balance to be set.
180 -- p_dimension_name - the balance dimension to be set.
181 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
182 -- p_upload_date
183 -- p_batch_line_id
184 -- p_test_batch_line_id
185 -- OUT : N/A
186 -- RETURN : Number
187 --
188 -- Notes
189 -- all the NO dimensions affect each other when they share the same context
190 -- values so there is no special support required for individual dimensions.
191 -- this is used by pay_balance_upload.get_current_value.
192 --------------------------------------------------------------------------
193 FUNCTION include_adjustment
194 (
195 p_balance_type_id NUMBER
196 ,p_dimension_name VARCHAR2
197 ,p_original_entry_id NUMBER
198 ,p_upload_date DATE
199 ,p_batch_line_id NUMBER
200 ,p_test_batch_line_id NUMBER
201 ) RETURN number IS
202
203 CURSOR csr_bal_adj (p_test_batch_line_id NUMBER, p_batch_line_id NUMBER) IS
204 SELECT tba.source_text , tba.tax_unit_id
205 FROM pay_temp_balance_adjustments tba,
206 pay_balance_batch_lines bbl
207 WHERE tba.balance_type_id = bbl.balance_type_id
208 AND tba.batch_line_id = p_test_batch_line_id
209 AND bbl.batch_line_id = p_batch_line_id
210 AND nvl(tba.source_text ,0) = nvl(bbl.source_text ,0);
211
212
213 l_include_adj Number := 1 ; -- True
214 v_cur_bal_adj_st1 csr_bal_adj%ROWTYPE;
215
216 BEGIN
217 hr_utility.trace(' Entering pay_no_bal_upload.include_adjustment ');
218
219 -- When not to allow adjustment
220 -- Suppose,we want _ASG_ELE_CODE_PTD (Source_text 'test1' and balance value 7000)
221 -- _ASG_ELE_CODE_PTD (Source_text 'test2' and balance value 4000)
222 -- Here we expect _ASG_PTD to be 11000 after upload
223 -- In this case,if adjustment is allowed (True)
224 -- _ASG_PTD = 4000
225 -- (Source_text 'test1' and YTD balance value 7000)
226 -- (Source_text 'test2' and YTD balance value -3000)
227 -- balance values will be adjusted to get make the balance satisy last entry
228 -- Hence donot allow adjustment.
229
230 IF p_dimension_name IN ('ASSIGNMENT BI-MONTHLY TO DATE') THEN
231
232 l_include_adj := 1 ; -- True
233
234 ELSIF p_dimension_name IN ('ASSIGNMENT ELEMENT CODE PERIOD TO DATE'
235 ,'ASSIGNMENT ELEMENT CODE YEAR TO DATE') THEN
236
237 OPEN csr_bal_adj(p_test_batch_line_id => p_test_batch_line_id,
238 p_batch_line_id => p_batch_line_id);
239
240 FETCH csr_bal_adj INTO v_cur_bal_adj_st1;
241
242 IF csr_bal_adj%NOTFOUND THEN
243 l_include_adj := 0 ; -- False
244 END IF;
245 CLOSE csr_bal_adj;
246 ELSE
247 NULL;
248 END IF;
249
250 hr_utility.trace(' Leaving pay_no_bal_upload.include_adjustment' );
251 RETURN l_include_adj;
252
253 END include_adjustment;
254
255 PROCEDURE validate_batch_lines ( p_batch_id IN NUMBER) is
256 BEGIN
257 null;
258 END;
259
260 END PAY_NO_BAL_UPLOAD;
[Home] [Help]