[Home] [Help]
PACKAGE BODY: APPS.PA_CLIENT_EXTN_STATUS
Source
1 PACKAGE BODY pa_client_extn_status AS
2 /* $Header: PAXVPS2B.pls 120.2 2005/08/31 11:08:27 vmangulu noship $ */
3 --=========================================================
4 --
5 -- ----------------------------------------------------------------------------------------------
6 -- PROJECT, TASK AND RESOURCE CUSTOMIZATION FUNCTIONS
7 --
8 -- These functions determine whether the get_cols procedure should be used by the
9 -- Post-Query triggers in Project Status Inquiry form:
10 -- o Project Status window calls ProjectCustomExtn
11 -- o Task Status window calls TaskCustomExtn
12 -- o Resource Status window calls RsrcCustomExtn
13 --
14 -- The allowable return values are upper-case, 'Y' or 'N'.
15 --
16 -- By DEFAULT, this package sets the return values for all
17 -- functions to 'N' .
18 --
19 -- If a function returns 'N', then the following will occur:
20 --
21 -- o The extension will NOT be called for the corresponding
22 -- block Post_Query trigger.
23 -- o The Project Status Inquiry form will populate
24 -- the block fields from the corresponding view generated by the
25 -- Project Status Column setup form.
26 -- o If the client changes and regenerates the
27 -- corresponding view definition in
28 -- the Project Status Column setup form, the data
29 -- retrieved by the modified view will be reflected in the
30 -- Project Status Inquiry form.
31 --
32 -- ----------------------------------------------------------------------------------------------
33 --
34
35 FUNCTION ProjCustomExtn RETURN VARCHAR2
36 IS
37 BEGIN
38
39 RETURN ('N');
40 END;
41
42 FUNCTION TaskCustomExtn RETURN VARCHAR2
43 IS
44 BEGIN
45
46 RETURN ('N');
47 END;
48
49 FUNCTION RsrcCustomExtn RETURN VARCHAR2
50 IS
51 BEGIN
52
53 RETURN ('N');
54 END;
55
56 --
57 -- ========================================================
58 -- 14-JUL-99, jwhite:
59 --
60 -- Functions to Enable Project Status Totals Processing
61 -- ========================================================
62 --
63 -- PLEASE SEE THE COMMENTS section of this package for more
64 -- details. You may also need to modify a user-defined
65 -- totals view.
66 --
67 -- If the Getcols extension is enabled, then
68 -- you must modify this function to RETURN ('N') if you
69 -- want PSI Totals functionality to be enabled.
70 --
71 -- If you want to enable the Get_Totals procedure,
72 -- you must hardcode the Proj_Tot_Custom_Extn function to
73 -- RETURN ('Y').
74 --
75 --
76
77 FUNCTION Hide_Totals RETURN VARCHAR2
78 IS
79 BEGIN
80 RETURN (ProjCustomExtn);
81 END;
82
83 FUNCTION Proj_Tot_Custom_Extn RETURN VARCHAR2
84 IS
85 BEGIN
86 RETURN ('N');
87 END;
88
89
90
91 --
92 -- ========================================================
93 -- Generic Procedure to Process Derived Columns in Oracle Forms
94 -- Project Status Inquiry
95 -- ========================================================
96 --
97
98 PROCEDURE getcols (x_project_id IN NUMBER
99 , x_task_id IN NUMBER
100 , x_resource_list_member_id IN NUMBER
101 , x_cost_budget_type_code IN VARCHAR2
102 , x_rev_budget_type_code IN VARCHAR2
103 , x_status_view IN VARCHAR2
104 , x_pa_install IN VARCHAR2
105 , x_derived_col_1 OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
106 , x_derived_col_2 OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
107 , x_derived_col_3 OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
108 , x_derived_col_4 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
109 , x_derived_col_5 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
110 , x_derived_col_6 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
111 , x_derived_col_7 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
112 , x_derived_col_8 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
113 , x_derived_col_9 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
114 , x_derived_col_10 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
115 , x_derived_col_11 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
116 , x_derived_col_12 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
117 , x_derived_col_13 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
118 , x_derived_col_14 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
119 , x_derived_col_15 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
120 , x_derived_col_16 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
121 , x_derived_col_17 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
122 , x_derived_col_18 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
123 , x_derived_col_19 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
124 , x_derived_col_20 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
125 , x_derived_col_21 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
126 , x_derived_col_22 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
127 , x_derived_col_23 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
128 , x_derived_col_24 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
129 , x_derived_col_25 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
130 , x_derived_col_26 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
131 , x_derived_col_27 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
132 , x_derived_col_28 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
133 , x_derived_col_29 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
134 , x_derived_col_30 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
135 , x_derived_col_31 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
136 , x_derived_col_32 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
137 , x_derived_col_33 OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
138 IS
139
140 p_burdened_cost_itd NUMBER := 0;
141 p_base_burdened_cost_tot NUMBER := 0;
142 p_actuals_labor_hours_itd NUMBER := 0;
143 p_base_cost_labor_hours_tot NUMBER := 0;
144 p_cmt_burdened_cost_ptd NUMBER := 0;
145 p_base_burdened_cost_itd NUMBER := 0;
146 p_base_revenue_tot NUMBER := 0;
147 p_revenue_itd NUMBER := 0;
148 p_revenue_ptd NUMBER := 0;
149
150 BEGIN
151
152 IF (x_status_view = 'PROJECTS') THEN
153
154 SELECT nvl(burdened_cost_itd, 0)
155 , nvl(baseline_burdened_cost_tot, 0)
156 , nvl(actuals_labor_hours_itd, 0)
157 , nvl(baseline_cost_labor_hours_tot, 0)
158 , nvl(cmt_burdened_cost_ptd, 0)
159 , nvl(baseline_burdened_cost_itd, 0)
160 , nvl(baseline_revenue_tot, 0)
161 , nvl(revenue_itd, 0)
162 , nvl(revenue_ptd, 0)
163 INTO p_burdened_cost_itd
164 , p_base_burdened_cost_tot
165 , p_actuals_labor_hours_itd
166 , p_base_cost_labor_hours_tot
167 , p_cmt_burdened_cost_ptd
168 , p_base_burdened_cost_itd
169 , p_base_revenue_tot
170 , p_revenue_itd
171 , p_revenue_ptd
172 FROM pa_status_proj_v
173 WHERE project_id = x_project_id;
174
175 ELSIF (x_status_view = 'TASKS') THEN
176
177 SELECT nvl(burdened_cost_itd, 0)
178 , nvl(baseline_burdened_cost_tot, 0)
179 , nvl(actuals_labor_hours_itd, 0)
180 , nvl(baseline_cost_labor_hours_tot, 0)
181 , nvl(cmt_burdened_cost_ptd, 0)
182 , nvl(baseline_burdened_cost_itd, 0)
183 , nvl(baseline_revenue_tot, 0)
184 , nvl(revenue_itd, 0)
185 , nvl(revenue_ptd, 0)
186 INTO p_burdened_cost_itd
187 , p_base_burdened_cost_tot
188 , p_actuals_labor_hours_itd
189 , p_base_cost_labor_hours_tot
190 , p_cmt_burdened_cost_ptd
191 , p_base_burdened_cost_itd
192 , p_base_revenue_tot
193 , p_revenue_itd
194 , p_revenue_ptd
195 FROM pa_status_task_v
196 WHERE project_id = x_project_id
197 AND task_id = x_task_id;
198
199 ELSIF (x_status_view = 'RESOURCES') THEN
200
201 IF (x_task_id IS NULL) THEN
202 SELECT nvl(burdened_cost_itd, 0)
203 , nvl(baseline_burdened_cost_tot, 0)
204 , nvl(actuals_labor_hours_itd, 0)
205 , nvl(baseline_cost_labor_hours_tot, 0)
206 , nvl(cmt_burdened_cost_ptd, 0)
207 , nvl(baseline_burdened_cost_itd, 0)
208 , nvl(baseline_revenue_tot, 0)
209 , nvl(revenue_itd, 0)
210 INTO p_burdened_cost_itd
211 , p_base_burdened_cost_tot
212 , p_actuals_labor_hours_itd
213 , p_base_cost_labor_hours_tot
214 , p_cmt_burdened_cost_ptd
215 , p_base_burdened_cost_itd
216 , p_base_revenue_tot
217 , p_revenue_itd
218 FROM pa_status_rsrc_v
219 WHERE project_id = x_project_id
220 AND task_id is null
221 AND resource_list_member_id = x_resource_list_member_id;
222
223 ELSE
224 SELECT nvl(burdened_cost_itd, 0)
225 , nvl(baseline_burdened_cost_tot, 0)
226 , nvl(actuals_labor_hours_itd, 0)
227 , nvl(baseline_cost_labor_hours_tot, 0)
228 , nvl(cmt_burdened_cost_ptd, 0)
229 , nvl(baseline_burdened_cost_itd, 0)
230 , nvl(baseline_revenue_tot, 0)
231 , nvl(revenue_itd, 0)
232 INTO p_burdened_cost_itd
233 , p_base_burdened_cost_tot
234 , p_actuals_labor_hours_itd
235 , p_base_cost_labor_hours_tot
236 , p_cmt_burdened_cost_ptd
237 , p_base_burdened_cost_itd
238 , p_base_revenue_tot
239 , p_revenue_itd
240 FROM pa_status_rsrc_v
241 WHERE project_id = x_project_id
242 AND task_id = x_task_id
243 AND resource_list_member_id = x_resource_list_member_id;
244 END IF;
245 ELSE
246 x_derived_col_3 := '*** INVALID ARGUEMENT ***';
247 Return;
248 END IF;
249 --
250 --=========================================================
251 -- Calculate Derived Amounts and Pass Derived Parameters Back to Form
252 --=========================================================
253 --
254
255 IF (x_status_view = 'RESOURCES') THEN
256
257 -- Extension called from Resources Status window
258
259 --------- Costing Derived Columns ---------------------------------
260
261 -- Financial % Complete
262 IF (p_base_burdened_cost_tot = 0) THEN
263 x_derived_col_24 := NULL;
264 ELSE
265 x_derived_col_24 := ROUND((p_burdened_cost_itd/p_base_burdened_cost_tot) * 100);
266 END IF;
267
268 -- Hours % Complete
269 IF (p_base_cost_labor_hours_tot = 0) THEN
270 x_derived_col_25 := NULL;
271 ELSE
272 x_derived_col_25 := ROUND((p_actuals_labor_hours_itd/ p_base_cost_labor_hours_tot) * 100);
273 END IF;
274
275 -- Estimate To Complete
276 x_derived_col_26 := (ROUND(p_base_burdened_cost_tot - p_cmt_burdened_cost_ptd - p_burdened_cost_itd))/(PA_STATUS.Get_Factor);
277
278 -- Total Cost ITD
279 x_derived_col_27 := (ROUND(p_burdened_cost_itd + p_cmt_burdened_cost_ptd))/(PA_STATUS.Get_Factor);
280
281 -- Over Budget
282 IF ((p_burdened_cost_itd + p_cmt_burdened_cost_ptd ) > (p_base_burdened_cost_itd * 1.1)) THEN
283 x_derived_col_3 := '*';
284 ELSE
285 x_derived_col_3 := NULL;
286 END IF;
287 --
288 ---------------------------------- Billing Derived Columns ------------------
289 --
290 IF (x_pa_install = 'BILLING') THEN
291
292 -- Budgeted/Plan Margin, ITD
293 x_derived_col_28 := (ROUND(p_base_revenue_tot - p_base_burdened_cost_tot)) /(PA_STATUS.Get_Factor);
294
295 -- Actual Margin, ITD
296 x_derived_col_29 := (ROUND(p_revenue_itd - p_burdened_cost_itd)) /(PA_STATUS.Get_Factor);
297
298 ELSE
299
300 x_derived_col_28 := NULL;
301 x_derived_col_29 := NULL;
302
303 END IF;
304 --
305 ELSE
306 -- This Extension must have been called from Project or Task Status
307
308 --------- Costing Derived Columns ---------------------------------
309
310 -- Financial % Complete
311 IF (p_base_burdened_cost_tot = 0) THEN
312 x_derived_col_22 := NULL;
313 ELSE
314 x_derived_col_22 := ROUND((p_burdened_cost_itd/p_base_burdened_cost_tot) * 100);
315 END IF;
316
317 -- Hours % Complete
318 IF (p_base_cost_labor_hours_tot = 0) THEN
319 x_derived_col_23 := NULL;
320 ELSE
321 x_derived_col_23 := ROUND((p_actuals_labor_hours_itd/ p_base_cost_labor_hours_tot) * 100);
322 END IF;
323
324 -- Estimate To Complete
325 x_derived_col_24 := (ROUND(p_base_burdened_cost_tot - p_cmt_burdened_cost_ptd - p_burdened_cost_itd))/(PA_STATUS.Get_Factor);
326
327 -- Total Cost ITD
328 x_derived_col_25 := (ROUND(p_burdened_cost_itd + p_cmt_burdened_cost_ptd))/(PA_STATUS.Get_Factor);
329
330 -- Over Budget
331 IF ((p_burdened_cost_itd + p_cmt_burdened_cost_ptd ) > (p_base_burdened_cost_itd * 1.1)) THEN
332 x_derived_col_3 := '*';
333 ELSE
334 x_derived_col_3 := NULL;
335 END IF;
336 --
337 ---------------------------------- Billing Derived Columns ------------------
338 --
339 IF (x_pa_install = 'BILLING') THEN
340
341 -- Budgeted/Plan Margin, ITD
342 x_derived_col_26 := (ROUND(p_base_revenue_tot - p_base_burdened_cost_tot)) /(PA_STATUS.Get_Factor);
343
344 -- Actual Margin, ITD
345 x_derived_col_27 := (ROUND(p_revenue_itd - p_burdened_cost_itd)) /(PA_STATUS.Get_Factor);
346
347 ELSE
348
349 x_derived_col_26 := NULL;
350 x_derived_col_27 := NULL;
351
352 END IF;
353 ----------------------------------------------------------------------------------------
354 -- Cost Accrual code
355 ----------------------------------------------------------------------------------------
356 --Please UNcomment the call to this procedure if you have cost accrual enabled
357 --and want to display the columns in PSI
358 --this procedure is defined in the cost accrual billing extension template.
359 ----------------------------------------------------------------------------------------
360
361 /***************************************************************************************
362 pa_rev_ca.get_psi_cols (
363 x_project_id
364 , x_task_id
365 , x_resource_list_member_id
366 , x_cost_budget_type_code
367 , x_rev_budget_type_code
368 , x_status_view
369 , x_pa_install
370 , x_derived_col_1
371 , x_derived_col_2
372 , x_derived_col_3
373 , x_derived_col_4
374 , x_derived_col_5
375 , x_derived_col_6
376 , x_derived_col_7
377 , x_derived_col_8
378 , x_derived_col_9
379 , x_derived_col_10
380 , x_derived_col_11
381 , x_derived_col_12
382 , x_derived_col_13
383 , x_derived_col_14
384 , x_derived_col_15
385 , x_derived_col_16
386 , x_derived_col_17
387 , x_derived_col_18
388 , x_derived_col_19
389 , x_derived_col_20
390 , x_derived_col_21
391 , x_derived_col_22
392 , x_derived_col_23
393 , x_derived_col_24
394 , x_derived_col_25
395 , x_derived_col_26
396 , x_derived_col_27
397 , x_derived_col_28
398 , x_derived_col_29
399 , x_derived_col_30
400 , x_derived_col_31
401 , x_derived_col_32
402 , x_derived_col_33
403 , p_revenue_ptd
404 , p_revenue_itd );
405
406 ***************************************************************************************/
407 END IF;
408
409
410 EXCEPTION
411 WHEN NO_DATA_FOUND THEN
412 x_derived_col_3 := '*** NO DATA FOUND ***';
413 Return;
414 WHEN OTHERS THEN
415 x_derived_col_3 := '*** OTHER EXCEPTION ***';
416 Return;
417
418 END getcols;
419 -- ============================================================================
420
421 --
422 -- ========================================================
423 -- Generic Procedure to Process Totals in Oracle Forms
424 -- Project Status Inquiry Project Status window.
425 -- ========================================================
426 --
427
428 PROCEDURE Get_Totals
429 (x_where_clause IN VARCHAR2
430 , x_in_tot_column4 IN NUMBER
431 , x_in_tot_column5 IN NUMBER
432 , x_in_tot_column6 IN NUMBER
433 , x_in_tot_column7 IN NUMBER
434 , x_in_tot_column8 IN NUMBER
435 , x_in_tot_column9 IN NUMBER
436 , x_in_tot_column10 IN NUMBER
437 , x_in_tot_column11 IN NUMBER
438 , x_in_tot_column12 IN NUMBER
439 , x_in_tot_column13 IN NUMBER
440 , x_in_tot_column14 IN NUMBER
441 , x_in_tot_column15 IN NUMBER
442 , x_in_tot_column16 IN NUMBER
443 , x_in_tot_column17 IN NUMBER
444 , x_in_tot_column18 IN NUMBER
445 , x_in_tot_column19 IN NUMBER
446 , x_in_tot_column20 IN NUMBER
447 , x_in_tot_column21 IN NUMBER
448 , x_in_tot_column22 IN NUMBER
449 , x_in_tot_column23 IN NUMBER
450 , x_in_tot_column24 IN NUMBER
451 , x_in_tot_column25 IN NUMBER
452 , x_in_tot_column26 IN NUMBER
453 , x_in_tot_column27 IN NUMBER
454 , x_in_tot_column28 IN NUMBER
455 , x_in_tot_column29 IN NUMBER
456 , x_in_tot_column30 IN NUMBER
457 , x_in_tot_column31 IN NUMBER
458 , x_in_tot_column32 IN NUMBER
459 , x_in_tot_column33 IN NUMBER
460 , x_out_tot_column4 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
461 , x_out_tot_column5 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
462 , x_out_tot_column6 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
463 , x_out_tot_column7 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
464 , x_out_tot_column8 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
465 , x_out_tot_column9 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
466 , x_out_tot_column10 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
467 , x_out_tot_column11 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
468 , x_out_tot_column12 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
469 , x_out_tot_column13 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
470 , x_out_tot_column14 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
471 , x_out_tot_column15 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
472 , x_out_tot_column16 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
473 , x_out_tot_column17 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
474 , x_out_tot_column18 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
475 , x_out_tot_column19 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
476 , x_out_tot_column20 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
477 , x_out_tot_column21 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
478 , x_out_tot_column22 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
479 , x_out_tot_column23 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
480 , x_out_tot_column24 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
481 , x_out_tot_column25 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
482 , x_out_tot_column26 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
483 , x_out_tot_column27 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
484 , x_out_tot_column28 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
485 , x_out_tot_column29 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
486 , x_out_tot_column30 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
487 , x_out_tot_column31 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
488 , x_out_tot_column32 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
489 , x_out_tot_column33 OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
490 , x_error_code OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
491 , x_error_message OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
492 )
493
494 IS
495
496 -- Define your local variables here
497
498 BEGIN
499
500 -- Initialize the output parameters here
501
502 x_error_code := 0;
503 x_error_message := NULL;
504
505 -- Add you business rules here to calculate values for the totals fields
506
507
508 EXCEPTION
509 WHEN OTHERS THEN
510 -- Add your exception handler here
511 -- To raise an Oracle error, assign SQLCODE to x_error_code
512 -- If you assign a message to x_error_message, it will be appended to
513 -- the message displayed by the PSI form.
514 x_error_code := SQLCODE;
515 x_error_message := SQLERRM;
516 Return;
517
518 END Get_Totals;
519 -- ==========================================================================
520
521 --
522 --
523 END pa_client_extn_status;