1 PACKAGE BODY PA_CLIENT_EXTN_CAP_INT AS
2 -- $Header: PACINTXB.pls 120.1 2005/08/17 12:56:14 ramurthy noship $
3
4
5 PROCEDURE check_thresholds
6 (p_project_id IN NUMBER
7 ,p_task_id IN NUMBER
8 ,p_rate_name IN VARCHAR2
9 ,p_start_date IN DATE
10 ,p_end_date IN DATE
11 ,p_threshold_amt_type IN VARCHAR2
12 ,p_budget_type IN VARCHAR2
13 ,p_fin_plan_type_id IN NUMBER
14 ,p_interest_calc_method IN VARCHAR2
15 ,p_cip_cost_type IN VARCHAR2
16 ,x_duration_threshold IN OUT NOCOPY NUMBER
17 ,x_amt_threshold IN OUT NOCOPY NUMBER
18 ,x_return_status OUT NOCOPY VARCHAR2
19 ,x_error_msg_count OUT NOCOPY NUMBER
20 ,x_error_msg_code OUT NOCOPY VARCHAR2)
21 IS
22 BEGIN
23 /* This client extension contains no default code, but can be used by customers
24 to modify duration or amount thresholds for project and task from the delivered
25 values. The returned values will be used by the capitalized interest calculation
26 to determine whether the project or task has met the threshold requirements.
27
28 One example of such logic might be to set the duration or amount threshold values
29 for a specific project or task to values other than those set at the implementation
30 level. Through the delivered setup, customers can set duration and amount thresholds
31 at the implementation level, but not at any lower levels. Therefore, all projects
32 and tasks within that implementation are compared against these overall values.
33 If special threshold values, different than the implementation values, should be
34 used on a project or task (e.g. due to special funding), the values could be
35 stored in descriptive flexfields, lookup sets or custom tables. These values could
36 then be retrieved in this client extension and used in the threshold comparison.
37
38 The mandatory OUT Parameter x_return_status indicates the return status of the API.
39 The following values are valid:
40 'S' for Success
41 'E' for Error
42 'U' for Unexpected Error
43 */
44
45 x_return_status := 'S';
46 x_error_msg_count := 0;
47 x_error_msg_code := NULL;
48 EXCEPTION
49 WHEN OTHERS THEN
50 x_return_status := 'U';
51 x_error_msg_count := 1;
52 x_error_msg_code := SQLERRM;
53 END;
54
55
56
57 PROCEDURE get_target_task
58 (p_source_task_id IN NUMBER
59 ,p_source_task_num IN VARCHAR2
60 ,p_rate_name IN VARCHAR2
61 ,x_target_task_id OUT NOCOPY NUMBER
62 ,x_target_task_num OUT NOCOPY VARCHAR2
63 ,x_return_status OUT NOCOPY VARCHAR2
64 ,x_error_msg_count OUT NOCOPY NUMBER
65 ,x_error_msg_code OUT NOCOPY VARCHAR2)
66 IS
67 BEGIN
68 /* This client extension is delivered to return the source task as the target task
69 for Capitalized Interest transactions. However, if the customer wants to redirect
70 these interest transactions to a task other than that to which the basis expenditures
71 belong, that new target task id and number can be specified in the OUT parameters
72
73 The mandatory OUT Parameter x_return_status indicates the return status of the API.
74 The following values are valid:
75 'S' for Success
76 'E' for Error
77 'U' for Unexpected Error
78 */
79 x_target_task_id := p_source_task_id;
80 x_target_task_num := p_source_task_num;
81
82 x_return_status := 'S';
83 x_error_msg_count := 0;
84 x_error_msg_code := NULL;
85 EXCEPTION
86 WHEN OTHERS THEN
87 x_return_status := 'U';
88 x_error_msg_count := 1;
89 x_error_msg_code := SQLERRM;
90 END;
91
92
93
94 FUNCTION grouping_method
95 (p_gl_period IN VARCHAR2
96 ,p_project_id IN NUMBER
97 ,p_source_task_id IN NUMBER
98 ,p_expenditure_item_id IN NUMBER
99 ,p_line_num IN NUMBER
100 ,p_expenditure_id IN NUMBER
101 ,p_expenditure_type IN VARCHAR2
102 ,p_expenditure_category IN VARCHAR2
103 ,p_attribute1 IN VARCHAR2
104 ,p_attribute2 IN VARCHAR2
105 ,p_attribute3 IN VARCHAR2
106 ,p_attribute4 IN VARCHAR2
107 ,p_attribute5 IN VARCHAR2
108 ,p_attribute6 IN VARCHAR2
109 ,p_attribute7 IN VARCHAR2
110 ,p_attribute8 IN VARCHAR2
111 ,p_attribute9 IN VARCHAR2
112 ,p_attribute10 IN VARCHAR2
113 ,p_attribute_category IN VARCHAR2
114 ,p_transaction_source IN VARCHAR2
115 ,p_rate_name IN VARCHAR2)
116 RETURN VARCHAR2
117 IS
118 lv_return VARCHAR2(2000);
119 lv_string VARCHAR2(2000);
120 BEGIN
121 /* This client extension provides the ability to specify groups of cost distribution
122 lines for specific treatment in the Capitalized Interest algorithm. The grouping
123 method is delivered to return 'ALL' for all cost distribution lines. However,
124 one use of this could be the specification of grouping methods formerly used by CRL.
125 Separate interest transactions would be created for each of these groupings, and these
126 groupings could further be used to populate descriptive flexfields on these resulting
127 interest transactions.
128
129 Another possible use for this client extension would be to identify groups of cost
130 distribution lines that should be excluded from creating interest transactions for
131 some custom reason. This client extension could identify those lines and return a
132 grouping method value of 'DELETE'. The client extension for calculating interest
133 using custom logic could identify these groups (by the value 'DELETE' in the grouping
134 method) and return a capital interest amount of zero, effectively eliminating the
135 associated expenditures from being used in the interest basis.
136
137 If multiple values are to be used to create a custom grouping method, it is recommended
138 that each column should be separated by a special character, e.g. a tilde (~). This
139 will simplify the processing of parsing these columns values out if desired.
140 */
141
142 lv_return := 'ALL';
143
144
145 /* Uncomment this code in implement the cRL grouping method
146 lv_string := ipa_client_exten_cci_grouping.client_grouping_method
147 (p_project_id
148 ,p_source_task_id
149 ,p_expenditure_item_id
150 ,p_expenditure_id
151 ,p_expenditure_type
152 ,p_expenditure_category
153 ,p_attribute1
154 ,p_attribute2
155 ,p_attribute3
156 ,p_attribute4
157 ,p_attribute5
158 ,p_attribute6
159 ,p_attribute7
160 ,p_attribute8
161 ,p_attribute9
162 ,p_attribute10
163 ,p_attribute_category
164 ,p_transaction_source);
165
166 lv_return := SUBSTR(lv_return||lv_string,1,2000);
167 */
168
169
170 /* This is sample code for retrieving the DR CCID and including it in the grouping method
171 SELECT TO_CHAR(pcdl.dr_code_combination_id)
172 INTO lv_string
173 FROM pa_cost_distribution_lines_all pcdl
174 WHERE pcdl.line_num = p_line_num
175 AND pcdl.expenditure_item_id = p_expenditure_item_id;
176
177 lv_return := SUBSTR(lv_return||lv_string,1,2000);
178 */
179
180
181
182 /* This is sample code to mark certain expenditures to be included from the interest calculation.
183 The client extension CALCULATE_CAP_INTEREST can use this grouping method value DELETE to
184 identify these expenditures and return an interest amount of zero.
185
186 IF <place criteria here to identify such expenditures, e.g. exp type and gl period> THEN
187 lv_return := 'DELETE';
188 END IF;
189 */
190
191
192 RETURN lv_return;
193 EXCEPTION
194 WHEN OTHERS THEN
195 RETURN 'ALL';
196 END;
197
198
199
200 PROCEDURE get_txn_attributes
201 (p_project_id IN NUMBER
202 ,p_source_task_id IN NUMBER
203 ,p_target_task_id IN NUMBER
204 ,p_rate_name IN VARCHAR2
205 ,p_grouping_method IN VARCHAR2
206 ,x_attribute_category OUT NOCOPY VARCHAR2
207 ,x_attribute1 OUT NOCOPY VARCHAR2
208 ,x_attribute2 OUT NOCOPY VARCHAR2
209 ,x_attribute3 OUT NOCOPY VARCHAR2
210 ,x_attribute4 OUT NOCOPY VARCHAR2
211 ,x_attribute5 OUT NOCOPY VARCHAR2
212 ,x_attribute6 OUT NOCOPY VARCHAR2
213 ,x_attribute7 OUT NOCOPY VARCHAR2
214 ,x_attribute8 OUT NOCOPY VARCHAR2
215 ,x_attribute9 OUT NOCOPY VARCHAR2
216 ,x_attribute10 OUT NOCOPY VARCHAR2
217 ,x_return_status OUT NOCOPY VARCHAR2
218 ,x_error_msg_count OUT NOCOPY NUMBER
219 ,x_error_msg_code OUT NOCOPY VARCHAR2)
220 IS
221 BEGIN
222 /* This client extension is delivered to return NULL values for the descriptive
223 flexfield attributes and category. However, the customer can populate these OUT
224 parameters with whatever values are appropriate. One possibility might be to
225 parse the IN grouping method parameter for specific values set by the grouping
226 method customization (e.g. various ATTRIBUTE values used by CRL).
227
228 The mandatory OUT Parameter x_return_status indicates the return status of the API.
229 The following values are valid:
230 'S' for Success
231 'E' for Error
232 'U' for Unexpected Error
233 */
234 x_attribute_category := NULL;
235 x_attribute1 := NULL;
236 x_attribute2 := NULL;
237 x_attribute3 := NULL;
238 x_attribute4 := NULL;
239 x_attribute5 := NULL;
240 x_attribute6 := NULL;
241 x_attribute7 := NULL;
242 x_attribute8 := NULL;
243 x_attribute9 := NULL;
244 x_attribute10 := NULL;
245
246 x_return_status := 'S';
247 x_error_msg_count := 0;
248 x_error_msg_code := NULL;
249 EXCEPTION
250 WHEN OTHERS THEN
251 x_return_status := 'U';
252 x_error_msg_count := 1;
253 x_error_msg_code := SQLERRM;
254 END;
255
256
257
258 PROCEDURE calculate_cap_interest
259 (p_gl_period IN VARCHAR2
260 ,p_rate_name IN VARCHAR2
261 ,p_curr_period_mult IN NUMBER
262 ,p_period_mult IN NUMBER
263 ,p_project_id IN NUMBER
264 ,p_source_task_id IN NUMBER
265 ,p_target_task_id IN NUMBER
266 ,p_exp_org_id IN NUMBER
267 ,p_exp_item_date IN DATE
268 ,p_prior_period_amt IN NUMBER
269 ,p_curr_period_amt IN NUMBER
270 ,p_grouping_method IN VARCHAR2
271 ,p_rate_mult IN NUMBER
272 ,x_cap_int_amt IN OUT NOCOPY NUMBER
273 ,x_return_status OUT NOCOPY VARCHAR2
274 ,x_error_msg_count OUT NOCOPY NUMBER
275 ,x_error_msg_code OUT NOCOPY VARCHAR2)
276 IS
277 BEGIN
278 /* This client extension can be used to perform a custom calculation of Capitalized
279 Interest rather than the delivered algorithm. The client extension is delivered
280 to leave the incoming capitalized interest amount unchanged.
281
282 The returned value in parameter x_cap_int_amt will have the following effect in the
283 capitalized interest calculation
284 0 => prevent this interest transaction from being created (zero amount transactions are not created)
285 any other number => uses this as the interest amount
286 NULL => treated like zero above
287
288 The mandatory OUT Parameter x_return_status indicates the return status of the API.
289 The following values are valid:
290 'S' for Success
291 'E' for Error
292 'U' for Unexpected Error
293 */
294
295
296
297 /* This is sample code to remove certain expenditures from being included in the interest calculation.
298 The assumption is that the client extension GROUPING_METHOD set the grouping method value to
299 'DELETE' in order to identify such expenditures. Setting the returned amount to zero effectively
300 excludes the associated expenditures from creating interest transactions.
301
302 IF p_grouping_method = 'DELETE' THEN
303 x_cap_int_amt := 0;
307 END IF;
304 x_return_status := 'S';
305 x_error_msg_count := 0;
306 x_error_msg_code := NULL;
308 */
309
310 x_return_status := 'S';
311 x_error_msg_count := 0;
312 x_error_msg_code := NULL;
313 EXCEPTION
314 WHEN OTHERS THEN
315 x_return_status := 'U';
316 x_error_msg_count := 1;
317 x_error_msg_code := SQLERRM;
318 END;
319
320
321
322 FUNCTION expenditure_org
323 (p_expenditure_item_id IN NUMBER
324 ,p_line_num IN NUMBER
325 ,p_rate_name IN VARCHAR2)
326 RETURN NUMBER
327 IS
328 BEGIN
329 /* This client extension is delivered to return a NULL value for the capitalized
330 interest expenditure organization id. If 'CLIENT EXTENSION' is specified in the
331 setup as the expenditure organization source, a non-NULL value must be returned
332 by this extension. If 'CLIENT EXTENSION' is not specified in this setup, the
333 return value of this function will have no effect.
334 */
335 RETURN TO_NUMBER(NULL);
336 EXCEPTION
337 WHEN OTHERS THEN
338 RETURN TO_NUMBER(NULL);
339 END;
340
341
342
343 FUNCTION rate_multiplier
344 (p_expenditure_item_id IN NUMBER
345 ,p_line_num IN NUMBER
346 ,p_rate_name IN VARCHAR2)
347 RETURN NUMBER
348 IS
349 BEGIN
350 /* This client extension is delivered to return a NULL value for the capitalized
351 interest rate. If a non-NULL value is returned, this will be used as the annualized
352 rate for the interest calculation. If NULL is returned, than the rate will be
353 determined from the interest schedule using the task or project organization.
354 */
355 RETURN TO_NUMBER(NULL);
356 EXCEPTION
357 WHEN OTHERS THEN
358 RETURN TO_NUMBER(NULL);
359 END;
360
361
362 END PA_CLIENT_EXTN_CAP_INT;
[Home] [Help]