[Home] [Help]
PACKAGE BODY: APPS.PA_CE_INTEGRATION
Source
1 PACKAGE BODY PA_CE_INTEGRATION AS
2 /* $Header: PAXCEINB.pls 120.2 2005/08/23 12:04:45 dlai noship $ */
3
4 -- ==========================================================================
5 -- = PROCEDURE Pa_Ce_Budgets
6 -- ==========================================================================
7
8
9 PROCEDURE Pa_Ce_Budgets ( X_project_id IN NUMBER
10 , X_period_start_date IN DATE
11 , X_period_end_date IN DATE
12 , X_budget_type IN VARCHAR2
13 , X_version IN VARCHAR2
14 , X_cost_amount OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
15 , X_revenue_amount OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
16 , X_currency_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
17 , X_org_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
18 , X_err_stack IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
19 , X_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
20 , X_err_code IN OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
21 IS
22
23 CURSOR ce_budget_lines_csr IS
24 SELECT pbl.start_date, pbl.end_date,
25 nvl(pbl.raw_cost,0)/(pbl.end_date-pbl.start_date+1) per_day_raw_cost ,
26 nvl(pbl.revenue,0)/(pbl.end_date-pbl.start_date+1) per_day_revenue
27 FROM pa_budget_lines pbl ,
28 pa_resource_assignments pra ,
29 pa_budget_versions pbv
30 WHERE decode(X_version,'C', pbv.current_flag,'O',pbv.current_original_flag)='Y'
31 AND pbl.end_date >= X_period_start_date
32 AND pbl.start_date <= X_period_end_date
33 AND pbv.project_id = X_project_id
34 AND pbv.budget_type_code = X_budget_type
35 AND pbl.resource_assignment_id = pra.resource_assignment_id
36 AND pra.budget_version_id = pbv.budget_version_id;
37
38 CURSOR ce_org_id_csr IS
39 SELECT org_id
40 FROM pa_projects_all
41 WHERE project_id = X_project_id;
42
43 budget_line_rec ce_budget_lines_csr%ROWTYPE;
44 l_total_raw_cost number := 0;
45 l_total_revenue number := 0;
46 l_start_date date;
47 l_end_date date;
48
49 l_old_stack varchar2(630);
50
51 BEGIN
52
53 l_old_stack := X_err_stack;
54 X_err_stack := X_err_stack ||'->PA_CE_INTERGRATION.Pa_Ce_Budgets';
55
56 X_currency_code := pa_multi_currency_txn.get_proj_curr_code_sql(X_project_id);
57
58 OPEN ce_org_id_csr;
59
60 FETCH ce_org_id_csr INTO X_org_id;
61
62 CLOSE ce_org_id_csr;
63
64 FOR budget_line_rec IN ce_budget_lines_csr
65
66 LOOP
67 IF (budget_line_rec.start_date < X_period_start_date) THEN
68
69 IF (budget_line_rec.end_date < X_period_end_date) THEN
70 l_start_date := X_period_start_date;
71 l_end_date := budget_line_rec.end_date;
72 ELSE
73 l_start_date := X_period_start_date;
74 l_end_date := X_period_end_date;
75 END IF;
76
77 ELSE
78
79 IF (budget_line_rec.end_date > X_period_end_date) THEN
80 l_start_date := budget_line_rec.start_date;
81 l_end_date := X_period_end_date;
82 ELSE
83 l_start_date := budget_line_rec.start_date;
84 l_end_date := budget_line_rec.end_date;
85 END IF;
86
87 END IF;
88
89 l_total_raw_cost:= l_total_raw_cost
90 + budget_line_rec.per_day_raw_cost * (l_end_date - l_start_date+1);
91 l_total_revenue := l_total_revenue
92 + budget_line_rec.per_day_revenue *(l_end_date - l_start_date + 1);
93
94 END LOOP;
95
96 -- Removed reference to pa_currency.round_currency_amt because this function
97 -- requires an org_id but forecasting should work across orgs
98 /*
99 X_cost_amount := pa_currency.round_currency_amt(l_total_raw_cost);
100 X_revenue_amount := pa_currency.round_currency_amt(l_total_revenue);
101 */
102
103 X_cost_amount := l_total_raw_cost;
104 X_revenue_amount := l_total_revenue;
105
106 -- Restore the old error stack
107
108 X_err_stack := l_old_stack;
109
110 EXCEPTION
111 WHEN OTHERS THEN
112 X_err_code := SQLCODE;
113 RAISE;
114
115 END Pa_Ce_Budgets;
116
117 END PA_CE_INTEGRATION;