1 PACKAGE BODY PA_FORECAST_REVENUE as
2 /* $Header: PARFRTRB.pls 120.2 2006/06/30 23:36:09 lkan noship $ */
3
4 -- This procedure will calculate the raw revenue and bill amount from one of the 12 criterias on the basis
5 -- of passed parameters
6 -- Input parameters
7 -- Parameters Type Required Description
8 -- P_project_id NUMBER YES Project Id
9 -- P_task_id NUMBER NO Task Id for the given project
10 -- P_bill_rate_multiplier NUMBER YES Bill rate multiplier for calculating the revenue
11 -- and rate
12 -- P_quantity NUMBER YES Quantity in Hours
13 -- P_person_id NUMBER YES Person Id
14 -- P_raw_cost NUMBER YES Row cost
15 -- P_item_date DATE YES Forecast Item date
16 -- P_labor_schdl_discnt NUMBER NO Labour schedule discount
17 -- P_labor_bill_rate_org_id NUMBER NO Bill rate organization id
18 -- P_labor_std_bill_rate_schdl VARCHAR2 NO Standard bill rate schedule
19 -- P_labor_schdl_fixed_date DATE NO Schedule date
20 -- P_bill_job_grp_id NUMBER NO Project Group Id
21 -- P_forecast_item_id NUMBER YES Unique Identifier for forecast item used in client
22 -- extension
23 -- P_labor_sch_type VARCHAR2 NO Labor schedule type
24 -- P_project_org_id NUMBER NO Project Org ID
25 -- P_project_type VARCHAR2 YES Project Type
26 -- P_expenditure_type VARCHAR2 YES Expenditure Type
27 -- P_exp_func_curr_code VARCHAR2 YES Expenditure functional currency code
28 -- P_incurred_by_organz_id NUMBER YES Incurred by organz id
29 -- P_raw_cost_rate NUMBER YES Raw cost rate
30 -- P_override_to_organz_id NUMBER YES Override to organz id
31 --
32 -- Out parameters
33 --
34 -- X_bill_rate NUMBER YES
35 -- X_raw_revenue NUMBER YES
36 -- X_rev_currency_code VARCHAR2 YES
37
38 PROCEDURE Get_Rev_Amt ( p_project_id IN NUMBER,
39 p_task_id IN NUMBER DEFAULT NULL,
40 p_bill_rate_multiplier IN NUMBER DEFAULT NULL,
41 p_quantity IN NUMBER,
42 p_person_id IN NUMBER,
43 p_raw_cost IN NUMBER DEFAULT NULL,
44 p_item_date IN DATE,
45 p_labor_schdl_discnt IN NUMBER DEFAULT NULL,
46 p_labor_bill_rate_org_id IN NUMBER DEFAULT NULL,
47 p_labor_std_bill_rate_schdl IN VARCHAR2 DEFAULT NULL,
48 p_labor_schdl_fixed_date IN DATE DEFAULT NULL,
49 p_bill_job_grp_id IN NUMBER DEFAULT NULL,
50 p_forecast_item_id IN NUMBER DEFAULT NULL,
51 p_labor_sch_type IN VARCHAR2 DEFAULT NULL,
52 p_project_org_id IN NUMBER DEFAULT NULL,
53 p_project_type IN VARCHAR2 DEFAULT NULL,
54 p_expenditure_type IN VARCHAR2 DEFAULT NULL,
55 p_exp_func_curr_code IN VARCHAR2 DEFAULT NULL,
56 p_incurred_by_organz_id IN NUMBER DEFAULT NULL,
57 p_raw_cost_rate IN NUMBER DEFAULT NULL,
58 p_override_to_organz_id IN NUMBER DEFAULT NULL,
59 p_forecast_job_id IN NUMBER DEFAULT NULL /* Required in case of Requirement,added for Assignment override */,
60 p_forecast_job_group_id IN NUMBER DEFAULT NULL /* Required in case of Requirement,added for Assignment override */,
61 p_expenditure_org_id IN NUMBER DEFAULT NULL /* Required in case of Requirement,added for Assignment override */ ,
65 x_raw_revenue OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
62 p_expenditure_organization_id IN NUMBER DEFAULT NULL /* Required in case of Requirement,added for Assignment override */,
63 p_check_error_flag IN VARCHAR2 DEFAULT 'Y', /* Added for bug 2218386 */
64 x_bill_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
66 x_rev_currency_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
67 x_markup_percentage OUT NOCOPY NUMBER,/* Added for Assignment overridea */ --File.Sql.39 bug 4440895
68 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
69 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
70 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
71 IS
72
73 l_raw_revenue NUMBER; -- It will be used to store the raw revenue
74 -- from one of the raw revenue calculating
75 -- criteria
76 l_bill_rate NUMBER; -- It will be used to store bill amount
77 -- from one of the bill amount calculating
78 -- criteria
79 l_x_return_status VARCHAR2(50); -- It will be used to store the return status
80 -- and used it to validate whether the
81 -- calling procedure has run successfully
82 -- or encounter any error
83
84 l_rev_currency_code pa_projects_all.project_currency_code%TYPE; -- variable to store exp_func_curr_code
85 l_x_msg_data VARCHAR2(30);
86 l_x_msg_count NUMBER;
87 l_x_markup_percentage NUMBER;
88 l_asgn_type VARCHAR2(1);
89 l_exp_res_org_id pa_project_assignments.expenditure_org_id%TYPE;
90 l_exp_orgz_res_id pa_project_assignments.expenditure_organization_id%TYPE;
91 l_exp_type pa_project_assignments.expenditure_type%TYPE;
92 l_org_id pa_projects_all.org_id%TYPE;
93
94 BEGIN
95
96 -- Initializing return status with success sothat if some unexpected error comes
97 -- , we change its status from succes to error sothat we can take necessary step to rectify the problem
98 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
99
100 /* Assigning the check error condition . Added for bug 2218386 */
101 PA_RATE_PVT_PKG.G_add_error_to_stack_flag := p_check_error_flag;
102
103 IF ( p_person_id IS NULL ) THEN
104 l_asgn_type := 'R';
105 ELSE
106 l_asgn_type := 'A';
107 END IF;
108 /* Selecting all the necessary inputs for this api */
109
110 SELECT org_id
111 INTO l_org_id
112 FROM pa_projects_all
113 WHERE project_id = p_project_id;
114
115 SELECT default_assign_exp_type
116 INTO l_exp_type
117 FROM pa_forecasting_options_all
118 WHERE org_id = l_org_id; /*Bug 5368295*/
119
120 IF (l_asgn_type = 'A') THEN
121 SELECT NVL(resource_org_id,-99),resource_organization_id
122 INTO l_exp_res_org_id,l_exp_orgz_res_id
123 FROM pa_resources_denorm
124 WHERE person_id = p_person_id
125 AND ( p_item_date BETWEEN TRUNC(resource_effective_start_date) AND
126 NVL(TRUNC(resource_effective_end_date),p_item_date));
127 ELSIF (l_asgn_type = 'R') THEN
128 l_exp_res_org_id := p_expenditure_org_id;
129 l_exp_orgz_res_id := p_expenditure_organization_id;
130 END IF;
131
132 /* Calling the new rate api, in this case because the forecast_item_id is null so it will
133 not execute the assignment level override */
134
135 PA_RATE_PVT_PKG.get_initial_bill_rate(
136 p_assignment_type => l_asgn_type ,
137 p_asgn_start_date => p_item_date ,
138 p_project_id => p_project_id ,
139 p_quantity => 1 ,
140 p_expenditure_org_id => l_exp_res_org_id ,
141 p_expenditure_type => l_exp_type ,
142 p_expenditure_organization_id => l_exp_orgz_res_id ,
143 p_person_id => p_person_id ,
144 p_forecast_job_id => p_forecast_job_id ,
145 p_forecast_job_group_id => p_forecast_job_group_id,
146 p_calculate_cost_flag => 'N', /* Added to fix bug 2162965 */
147 x_projfunc_bill_rate => l_bill_rate ,
148 x_projfunc_raw_revenue => l_raw_revenue ,
149 x_rev_currency_code => l_rev_currency_code ,
150 x_markup_percentage => l_x_markup_percentage ,
151 x_return_status => l_x_return_status ,
152 x_msg_count => l_x_msg_count ,
153 x_msg_data => l_x_msg_data );
154
155
156 /*
157 Bug 3192856 - When the p_quantity passed is 1 to PA_RATE_PVT_PKG.get_initial_bill_rate,
158 the parameter x_projfunc_raw_revenue (l_raw_revenue) will contain the Revenue and Adjusted Rate.
159 Please refer bug for other details
160 */
161 x_bill_rate := NVL(l_raw_revenue,0); /* 3192856 - Modified l_bill_rate to l_raw_revenue */
162 x_raw_revenue := NVL(l_raw_revenue,0);
163 x_rev_currency_code := l_rev_currency_code;
164 x_markup_percentage := l_x_markup_percentage;
165 x_return_status := l_x_return_status;
166 x_msg_count := l_x_msg_count;
167 x_msg_data := l_x_msg_data;
168
169 IF ( l_x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
170 /* Checking error condition. Added for bug 2218386 */
171 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
172 PA_UTILS.Add_Message ('PA', SUBSTR(l_x_msg_data,1,30));
173 END IF;
174 END IF;
175
176 EXCEPTION
177 WHEN OTHERS THEN
178 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
179 x_msg_count := 1;
180 x_msg_data := substr(SQLERRM,1,240);
181 /* Checking error condition. Added for bug 2218386 */
182 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
183 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FORECAST_REVENUE', /* Moved this here to fix bug 2434663 */
184 p_procedure_name => 'Get_Rev_Amt');
185 RAISE;
186 END IF;
187
188 END Get_Rev_Amt;
189
190
191 END PA_FORECAST_REVENUE;
192
[Home] [Help]