1 PACKAGE BODY PA_MISC AS
2 /* $Header: PAMISCB.pls 115.1 99/07/16 15:08:02 porting ship $ */
3
4 --- Function get_exp_cycle_start_day_code
5
6 --- This function returns expenditure cycle start day code
7 --- A NULL is returned in case of error
8
9 FUNCTION get_exp_cycle_start_day_code RETURN NUMBER IS
10 x_exp_cycle_start_day_code NUMBER;
11 BEGIN
12
13 SELECT exp_cycle_start_day_code
14 INTO x_exp_cycle_start_day_code
15 FROM pa_implementations;
16
17 RETURN x_exp_cycle_start_day_code;
18 EXCEPTION
19 WHEN OTHERS THEN
20 RETURN NULL;
21 END get_exp_cycle_start_day_code;
22
23 ----------------------------------------------------------
24
25 --- Function get_set_of_books_id
26
27 --- This function returns set of books id
28 --- A NULL is returned in case of error
29
30 FUNCTION get_set_of_books_id RETURN NUMBER IS
31 x_set_of_books_id NUMBER;
32 BEGIN
33
34 SELECT set_of_books_id
35 INTO x_set_of_books_id
36 FROM pa_implementations;
37
38 RETURN x_set_of_books_id;
39 EXCEPTION
40 WHEN OTHERS THEN
41 RETURN NULL;
42 END get_set_of_books_id;
43
44 ----------------------------------------------------------
45
46 --- Function get_job_id
47
48 --- This function returns job_id
49 --- A NULL is returned in case of error or no Job_id is found
50
51 FUNCTION get_job_id (
52 x_person_id IN NUMBER,
53 x_task_id IN NUMBER,
54 x_project_id IN NUMBER,
55 x_effective_date IN DATE
56 )
57 RETURN NUMBER IS
58 x_job_id NUMBER := NULL;
59 BEGIN
60
61 <<task_level_override>>
62 BEGIN
63 --- get the job_id from task level overrides
64
65 SELECT job_id
66 INTO x_job_id
67 FROM pa_job_assignment_overrides pjao
68 WHERE x_person_id = pjao.person_id
69 AND x_task_id = pjao.task_id
70 AND x_effective_date BETWEEN pjao.start_date_active
71 AND pjao.end_date_active;
72
73 EXCEPTION
74 WHEN NO_DATA_FOUND THEN
75 NULL;
76 WHEN OTHERS THEN
77 RETURN NULL;
78 END task_level_override;
79
80 IF x_job_id IS NULL THEN
81 <<project_level_override>>
82 BEGIN
83 --- get the job_id from project level overrides
84
85 SELECT job_id
86 INTO x_job_id
87 FROM pa_job_assignment_overrides pjao
88 WHERE x_person_id = pjao.person_id
89 AND x_project_id = pjao.project_id
90 AND x_effective_date BETWEEN pjao.start_date_active
91 AND pjao.end_date_active;
92
93 EXCEPTION
94 WHEN NO_DATA_FOUND THEN
95 NULL;
96 WHEN OTHERS THEN
97 RETURN NULL;
98 END project_level_override;
99 END IF;
100
101 IF x_job_id IS NULL THEN
102 <<primary_job_assignment>>
103 BEGIN
104 --- get the job_id from primary job assignments
105
106 SELECT job_id
107 INTO x_job_id
108 FROM pa_implementations imp,
109 per_assignments_f asn
110 WHERE x_person_id = asn.person_id
111 AND asn.primary_flag = 'Y'
112 AND asn.business_group_id+0 = imp.business_group_id
113 AND x_effective_date BETWEEN asn.effective_start_date
114 AND asn.effective_end_date;
115 -- tsaifee 01/30/97 Bug 442419 : 0 added to business_group_id to avoid
116 -- the use of the index for performance reasons.
117
118 EXCEPTION
119 WHEN NO_DATA_FOUND THEN
120 NULL;
121 WHEN OTHERS THEN
122 RETURN NULL;
123 END primary_job_assignment;
124 END IF;
125
126 RETURN x_job_id;
127 EXCEPTION
128 WHEN OTHERS THEN
129 RETURN NULL;
130 END get_job_id;
131
132 ----------------------------------------------------------
133
134 --- Function get_week_ending_date
135
136 --- This function returns week_ending_date
137 --- A NULL is returned in case of error
138
139 FUNCTION get_week_ending_date (
140 x_expenditure_item_date IN DATE
141 )
142 RETURN DATE IS
143 x_week_ending_date DATE;
144 BEGIN
145
146 SELECT pa_utils.GetWeekEnding(x_expenditure_item_date)
147 INTO x_week_ending_date
148 FROM SYS.DUAL;
149 RETURN x_week_ending_date;
150 EXCEPTION
151 WHEN OTHERS THEN
152 RETURN NULL;
153 END get_week_ending_date;
154
155 ----------------------------------------------------------
156
157 --- Function get_month_ending_date
158
159 --- This function returns month_ending_date
160 --- A NULL is returned in case of error
161
162 FUNCTION get_month_ending_date (
163 x_expenditure_item_date IN DATE
164 )
165 RETURN DATE IS
166 x_month_ending_date DATE;
167 BEGIN
168
169 SELECT LAST_DAY(x_expenditure_item_date)
170 INTO x_month_ending_date
171 FROM SYS.DUAL;
172 RETURN x_month_ending_date;
173 EXCEPTION
174 WHEN OTHERS THEN
175 RETURN NULL;
176 END get_month_ending_date;
177 ----------------------------------------------------------
178
179 --- Function get_pa_period
180
181 --- This function returns pa_period
182 --- A NULL is returned in case of error
183
184 FUNCTION get_pa_period (
185 x_pa_date IN DATE
186 )
187 RETURN VARCHAR2 IS
188 x_pa_period pa_periods.period_name%TYPE;
189 BEGIN
190
191 SELECT pp.period_name
192 INTO x_pa_period
193 FROM pa_periods pp
194 WHERE x_pa_date between pp.start_date and pp.end_date;
195 RETURN x_pa_period;
196
197 EXCEPTION
198 WHEN OTHERS THEN
199 RETURN NULL;
200 END get_pa_period;
201
202 ----------------------------------------------------------
203
204 --- Function get_gl_period
205
206 --- This function returns gl_period
207 --- A NULL is returned in case of error
208
209 FUNCTION get_gl_period (
210 x_gl_date IN DATE
211 )
212 RETURN VARCHAR2 IS
213 x_gl_period gl_period_statuses.period_name%TYPE;
214 BEGIN
215
216 SELECT gps.period_name
217 INTO x_gl_period
218 FROM gl_period_statuses gps
219 WHERE x_gl_date between gps.start_date and gps.end_date
220 AND gps.application_id+0 = 101
221 AND gps.adjustment_period_flag||'' <> 'Y'
222 AND gps.set_of_books_id = pa_misc.get_set_of_books_id;
223
224 RETURN x_gl_period;
225
226 EXCEPTION
227 WHEN OTHERS THEN
228 RETURN NULL;
229 END get_gl_period;
230
231 --- Spread Function
232
233 FUNCTION spread_amount (
234 x_type_of_spread IN VARCHAR2,
235 x_start_date IN DATE,
236 x_end_date IN DATE,
237 x_start_pa_date IN DATE,
238 x_end_pa_date IN DATE,
239 x_amount IN NUMBER)
240 RETURN NUMBER
241 IS
242 BEGIN
243
244 IF x_type_of_spread = 'L' THEN
245
246 -- Linear Spread
247
248 IF ( x_start_date <= x_start_pa_date ) AND
249 ( x_end_date >= x_End_pa_date ) THEN
250
251 -- PA_PERIOD is within or identical to other period
252
253 RETURN pa_currency.round_currency_amt((x_end_pa_date - x_start_pa_date + 1) * x_amount/
254 (x_end_date - x_start_date+ 1));
255
256 ELSIF ( x_start_pa_date <= x_start_date) AND
257 ( x_end_pa_date <= x_End_date ) THEN
258
259 RETURN pa_currency.round_currency_amt(( x_end_pa_date - x_start_date+ 1) * x_amount /
260 (x_end_date - x_start_date + 1));
261
262 ELSIF ( x_start_pa_date >= x_start_date) AND
263 ( x_end_pa_date >= x_End_date ) THEN
264
265 RETURN pa_currency.round_currency_amt(( x_end_date - x_start_pa_date + 1) * x_amount /
266 (x_end_date - x_start_date + 1));
267
268 ELSIF ( x_start_pa_date <= x_start_date ) AND
269 ( x_end_pa_date >= x_End_date ) THEN
270
271 -- PA_PERIOD bigger or identical to other period
272
273 RETURN pa_currency.round_currency_amt(x_amount);
274
275 ELSIF ( x_end_pa_date <= x_start_date ) OR
276 ( x_start_pa_Date >= x_end_date ) OR
277 ( x_start_pa_date = x_end_pa_date )OR
278 ( x_start_date = x_end_date ) THEN
279
280 -- Non Overlapping PA period and amount periods
281 -- OR Zero Days PA period
282
283 RETURN 0;
284
285 END IF;
286
287 END IF;
288
289 RETURN 0;
290 EXCEPTION
291 WHEN OTHERS THEN
292 RETURN NULL;
293 END spread_amount;
294
295 END PA_MISC;
[Home] [Help]