1 PACKAGE BODY PA_CLIENT_EXTN_ALLOC AS
2 /* $Header: PAPALCCB.pls 120.2 2005/08/09 16:23:55 dlanka noship $ */
3
4
5 /*Example of source_extn
6
7 PROCEDURE source_extn(p_alloc_rule_id IN NUMBER
8 ,x_source_proj_task_tbl OUT NOCOPY ALLOC_SOURCE_TABTYPE
9 ,x_status OUT NOCOPY NUMBER
10 ,x_error_message OUT NOCOPY VARCHAR2 )
11 IS
12 BEGIN
13 If p_alloc_rule_id = 782 then
14 x_source_proj_task_tbl(1).project_id:=1718;
15 x_source_proj_task_tbl(1).task_id:=2791;
16 x_status:=0;
17 END IF;
18 EXCEPTION
19 WHEN OTHERS THEN
20 x_status := 1;
21 x_error_message :='Your error message';
22 END source_extn;*/
23
24 PROCEDURE source_extn(p_alloc_rule_id IN NUMBER
25 ,x_source_proj_task_tbl OUT NOCOPY ALLOC_SOURCE_TABTYPE
26 ,x_status OUT NOCOPY NUMBER
27 ,x_error_message OUT NOCOPY VARCHAR2)
28 IS
29 -- Declare your local variables here.
30 BEGIN
31 -- Initialize output parameters.
32 -- Define your business rules.
33 -- Attention:The index for the x_source_proj_task_tbl should be numbered
34 -- sequentially starting from 1. The process will fail if it is not numbered sequentially.
35 x_status:=0;
36
37 EXCEPTION
38 WHEN OTHERS THEN
39 -- Define your exception handler here.
40 -- To raise an ORACLE error, assign SQLCODE to x_status.
41 -- To raise an application error, assign a positive number to x_status.
42 -- You can define your own error message and assign it to x_error_message.
43 null;
44 END source_extn;
45
46
47 /* Example of offset_extn
48 PROCEDURE offset_extn( p_alloc_rule_id IN NUMBER
49 , p_offset_amount IN NUMBER
50 , x_offset_proj_task_tbl OUT NOCOPY ALLOC_OFFSET_TABTYPE
51 , x_status OUT NOCOPY NUMBER
52 , x_error_message OUT NOCOPY VARCHAR2 )
53 IS
54 BEGIN
55 IF p_alloc_rule_id = 782 THEN
56 x_offset_proj_task_tbl(1).project_id:=1725;
57 x_offset_proj_task_tbl(1).task_id:=2819;
58 x_offset_proj_task_tbl(1).offset_amount:=-100;
59 x_offset_proj_task_tbl(2).project_id:=1725;
60 x_offset_proj_task_tbl(2).task_id:=2820;
61 x_offset_proj_task_tbl(2).offset_amount:=-100;
62 x_offset_proj_task_tbl(3).project_id:=1725;
63 x_offset_proj_task_tbl(3).task_id:=2821;
64 x_offset_proj_task_tbl(3).offset_amount:=-100;
65 x_offset_proj_task_tbl(4).project_id:=1725;
66 x_offset_proj_task_tbl(4).task_id:=2822;
67 x_offset_proj_task_tbl(4).offset_amount:=p_offset_amount+100+100+100;
68 END IF;
69 x_status:=0;
70 EXCEPTION
71 WHEN OTHERS THEN
72 x_status:=1;
73 x_error_message :='Your error message';
74 END offset_extn; */
75
76 PROCEDURE offset_extn( p_alloc_rule_id IN NUMBER
77 , p_offset_amount IN NUMBER
78 , x_offset_proj_task_tbl OUT NOCOPY ALLOC_OFFSET_TABTYPE
79 , x_status OUT NOCOPY NUMBER
80 , x_error_message OUT NOCOPY VARCHAR2)
81 IS
82 -- Declare your local variables here.
83 BEGIN
84 -- Initialize output parameters.
85 -- Define your business rules.
86 -- Attention:The index for the x_offset_proj_task_tbl should be numbered
87 -- sequentially starting from 1. The process will fail if it is not numbered sequentially.
88 x_status:=0;
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 -- Define your exception handler here.
93 -- To raise an ORACLE error, assign SQLCODE to x_status.
94 -- To raise an application error, assign a positive number to x_status.
95 -- You can define your own error message and assign it to x_error_message.
96 null;
97 END offset_extn;
98
99
100 /* Example of offset_task_extn
101 PROCEDURE offset_task_extn( p_alloc_rule_id IN NUMBER
102 , p_offset_project_id IN NUMBER
103 , x_offset_task_id OUT NOCOPY NUMBER
104 , x_status OUT NOCOPY NUMBER
105 , x_error_message OUT NOCOPY VARCHAR2 ) IS
106 BEGIN
107 If p_alloc_rule_id = 782 then
108 If p_offset_project_id=1718 then
109 x_offset_task_id :=2816;
110 End if;
111 If p_offset_project_id=1726 then
112 x_offset_task_id :=2823;
113 end if;
114 end if;
115 x_status:=0;
116
117 EXCEPTION
118 WHEN OTHERS THEN
119 x_status :=1;
120 x_error_message :='Your error message';
121 END offset_task_extn; */
122
123 PROCEDURE offset_task_extn( p_alloc_rule_id IN NUMBER
124 , p_offset_project_id IN NUMBER
125 , x_offset_task_id OUT NOCOPY NUMBER
126 , x_status OUT NOCOPY NUMBER
127 , x_error_message OUT NOCOPY VARCHAR2 )
128 IS
129 -- Declare your local variables here.
130 BEGIN
131 -- Initialize output parameters.
132 -- Define your business rules.
133 x_status:=0;
134 EXCEPTION
135 WHEN OTHERS THEN
136 -- Define your exception handler here.
137 -- To raise an ORACLE error, assign SQLCODE to x_status.
138 -- To raise an application error, assign a positive number to x_status.
139 -- You can define your own error message and assign it to x_error_message.
140 null;
141 END offset_task_extn;
142
143 /*Example of target_extn
144 PROCEDURE target_extn(p_alloc_rule_id IN NUMBER
145 ,x_target_proj_task_tbl OUT NOCOPY ALLOC_TARGET_TABTYPE
146 ,x_status OUT NOCOPY NUMBER
147 ,x_error_message OUT NOCOPY VARCHAR2 )
148 IS
149 BEGIN
150 If p_alloc_rule_id=782 then
151 x_target_proj_task_tbl(1).project_id:=1728;
152 x_target_proj_task_tbl(1).percent:=40;
153 x_target_proj_task_tbl(2).project_id:=1726;
154 x_target_proj_task_tbl(2).percent:=60;
155 x_target_proj_task_tbl(3).project_id:=1726;
156 x_target_proj_task_tbl(3).task_id:=2823;
157 x_target_proj_task_tbl(3).exclude_flag:='Y';
158 end if;
159 x_status:=0;
160 EXCEPTION
161 WHEN OTHERS THEN
162 x_status :=1;
163 x_error_message :='Your error message';
164 END target_extn;*/
165
166 PROCEDURE target_extn(p_alloc_rule_id IN NUMBER
167 ,x_target_proj_task_tbl OUT NOCOPY ALLOC_TARGET_TABTYPE
168 ,x_status OUT NOCOPY NUMBER
169 ,x_error_message OUT NOCOPY VARCHAR2 )
170 IS
171 -- Declare your local variables here.
172 BEGIN
173 -- Initialize output parameters.
174 -- Define your business rules.
175 -- Attention:The index for the x_target_proj_task_tbl should be numbered
176 -- sequentially starting from 1. The process will fail if it is not numbered sequentially.
177
178 x_status:=0;
179
180 EXCEPTION
181 WHEN OTHERS THEN
182 -- Define your exception handler here.
183 -- To raise an ORACLE error, assign SQLCODE to x_status.
184 -- To raise an application error, assign a positive number to x_status.
185 -- You can define your own error message and assign it to x_error_message.
186 null;
187 END target_extn;
188
189 /*Example of basis_extn
190 PROCEDURE basis_extn(p_alloc_rule_id IN NUMBER
191 , p_project_id IN NUMBER
192 , p_task_id IN NUMBER
193 , x_basis_amount OUT NOCOPY NUMBER
194 , x_status OUT NOCOPY NUMBER
195 , x_error_message OUT NOCOPY VARCHAR2 ) IS
196 BEGIN
197 if p_alloc_rule_id=782 then
198 if p_project_id=1726 and p_task_id=2823 then
199 x_basis_amount:=10;
200 end if;
201 if p_project_id=1726 and p_task_id=2824 then
202 x_basis_amount:=20;
203 end if;
204 if p_project_id=1726 and p_task_id=2825 then
205 x_basis_amount:=30;
206 end if;
207 if p_project_id=1728 and p_task_id=2831 then
208 x_basis_amount:=10;
209 end if;
210 if p_project_id=1728 and p_task_id=2834 then
211 x_basis_amount:=20;
212 end if;
213 end if;
214 x_status:=0;
215 EXCEPTION
216 WHEN OTHERS THEN
217 x_status :=1;
218 x_error_message :='Your error message';
219 END basis_extn;*/
220
221 PROCEDURE basis_extn(p_alloc_rule_id IN NUMBER
222 , p_project_id IN NUMBER
223 , p_task_id IN NUMBER
224 , x_basis_amount OUT NOCOPY NUMBER
225 , x_status OUT NOCOPY NUMBER
226 , x_error_message OUT NOCOPY VARCHAR2 )
227 IS
228 -- Declare your local variables here.
229 BEGIN
230 -- Initialize output parameters.
231 -- Define your business rules.
232 x_status:=0;
233 EXCEPTION
234 WHEN OTHERS THEN
235 -- Define your exception handler here.
236 -- To raise an ORACLE error, assign SQLCODE to x_status.
237 -- To raise an application error, assign a positive number to x_status.
238 -- You can define your own error message and assign it to x_error_message.
239 null;
240 END basis_extn;
241
242 /*Example of txn_dff_extn
243
244 PROCEDURE txn_dff_extn( p_alloc_rule_id IN NUMBER
245 ,p_run_id IN NUMBER
246 ,p_txn_type IN VARCHAR2
247 ,p_project_id IN VARCHAR2
248 ,P_task_id IN VARCHAR2
249 ,p_expnd_org IN VARCHAR2
250 ,p_expnd_type_class IN VARCHAR2
251 ,p_expnd_type IN VARCHAR2
252 ,x_attribute_category OUT NOCOPY VARCHAR2
253 ,x_attribute1 OUT NOCOPY VARCHAR2
254 ,x_attribute2 OUT NOCOPY VARCHAR2
255 ,x_attribute3 OUT NOCOPY VARCHAR2
256 ,x_attribute4 OUT NOCOPY VARCHAR2
257 ,x_attribute5 OUT NOCOPY VARCHAR2
258 ,x_attribute6 OUT NOCOPY VARCHAR2
259 ,x_attribute7 OUT NOCOPY VARCHAR2
260 ,x_attribute8 OUT NOCOPY VARCHAR2
261 ,x_attribute9 OUT NOCOPY VARCHAR2
262 ,x_attribute10 OUT NOCOPY VARCHAR2
263 ,x_status OUT NOCOPY NUMBER
264 ,x_error_message OUT NOCOPY VARCHAR2)
265 IS
266 BEGIN
267 IF p_alloc_rule_id =241
268 and p_txn_type ='T'
269 and p_expnd_org='Consulting'
270 and p_expnd_type_class='PJ'
271 and p_expnd_type ='Allocation'
272 and p_project_id:=1008 THEN
273 x_attribute_category:='test_txn_dff';
274 x_attribute1 :='ATTR01';
275 end if;
276 x_status:=0;
277 --NULL;
278 EXCEPTION
279 WHEN OTHERS THEN
280 x_status :=1;
281 x_error_message :='Your error message';
282
283 END txn_dff_extn ;*/
284
285 PROCEDURE txn_dff_extn( p_alloc_rule_id IN NUMBER
286 ,p_run_id IN NUMBER
287 ,p_txn_type IN VARCHAR2
288 ,p_project_id IN VARCHAR2
289 ,P_task_id IN VARCHAR2
290 ,p_expnd_org IN VARCHAR2
291 ,p_expnd_type_class IN VARCHAR2
292 ,p_expnd_type IN VARCHAR2
293 ,x_attribute_category OUT NOCOPY VARCHAR2
294 ,x_attribute1 OUT NOCOPY VARCHAR2
295 ,x_attribute2 OUT NOCOPY VARCHAR2
296 ,x_attribute3 OUT NOCOPY VARCHAR2
297 ,x_attribute4 OUT NOCOPY VARCHAR2
298 ,x_attribute5 OUT NOCOPY VARCHAR2
299 ,x_attribute6 OUT NOCOPY VARCHAR2
300 ,x_attribute7 OUT NOCOPY VARCHAR2
301 ,x_attribute8 OUT NOCOPY VARCHAR2
302 ,x_attribute9 OUT NOCOPY VARCHAR2
303 ,x_attribute10 OUT NOCOPY VARCHAR2
304 ,x_status OUT NOCOPY NUMBER
305 ,x_error_message OUT NOCOPY VARCHAR2)
306 IS
307 -- Declare your local variables here.
308 BEGIN
309 -- Initialize output parameters.
310 -- Define your business rules.
311 x_status:=0;
312 EXCEPTION
313 WHEN OTHERS THEN
314 -- Define your exception handler here.
315 -- To raise an ORACLE error, assign SQLCODE to x_status.
316 -- To raise an application error, assign a positive number to x_status.
317 -- You can define your own error message and assign it to x_error_message.
318 null;
319 END txn_dff_extn ;
320
321
322
323 /* Example of check_dependency
324 PROCEDURE check_dependency(p_alloc_rule_id IN NUMBER
325 , x_status OUT NOCOPY NUMBER
326 , x_error_message OUT NOCOPY VARCHAR2
327 )
328 IS
329 Cursor check_dependency IS
330 Select 'X'
331 From Dual
332 Where Exists
333 ( Select run_id
334 From pa_alloc_runs
335 Where rule_id=p_alloc_rule_id
336 And run_period='MAR-97'
337 And run_status='RS' ) ;
338 X_check Varchar2(1);
339
340 BEGIN
341 Open check_dependency;
342 Fetch check_dependency Into X_Check ;
343 If check_dependency%Found Then
344 x_status:=0;
345 close check_dependency;
346 Else
347 close check_dependency;
348 Raise No_Data_Found;
349 End If;
350
351 EXCEPTION
352 WHEN No_Data_Found THEN
353 x_status:=100;
354 x_error_message:='Can't generate allocation transaction because the depended run hasn't been completed';
355 return;
356 WHEN OTHERS THEN
357 X_Status := SQLCODE ;
358 return;
359 END check_dependency ;*/
360
361 PROCEDURE check_dependency(p_alloc_rule_id IN NUMBER
362 , x_status OUT NOCOPY NUMBER
363 , x_error_message OUT NOCOPY VARCHAR2
364 )
365 IS
366 -- Declare your local variables here.
367 BEGIN
368 -- Initialize output parameters.
369 -- Define your business rules.
370 x_status:=0;
371
372 EXCEPTION
373 WHEN OTHERS THEN
374 -- Define your exception handler here.
375 -- To raise an ORACLE error, assign SQLCODE to x_status.
376 -- To raise an application error, assign a positive number to x_status.
377 -- You can define your own error message and assign it to x_error_message.
378 NULL ;
379 END check_dependency ;
380
381 END PA_CLIENT_EXTN_ALLOC;