[Home] [Help]
PACKAGE BODY: APPS.PA_FP_ORG_FORECAST_LINES_PKG
Source
1 PACKAGE BODY pa_fp_org_forecast_lines_pkg as
2 /* $Header: PAFPFLTB.pls 120.1 2005/08/19 16:26:41 mwasowic noship $ */
3 -- Start of Comments
4 -- Package name : PA_FP_ORG_FORECAST_LINES_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PA_FP_ORG_FORECAST_LINES_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pafpfltb.pls';
13
14 PROCEDURE Insert_Row
15 ( px_forecast_line_id IN OUT NOCOPY pa_org_forecast_lines.forecast_line_id%TYPE --File.Sql.39 bug 4440895
16 ,p_forecast_element_id IN pa_org_forecast_lines.forecast_element_id%TYPE
17 := FND_API.G_MISS_NUM
18 ,p_budget_version_id IN pa_org_forecast_lines.budget_version_id%TYPE
19 := FND_API.G_MISS_NUM
20 ,p_project_id IN pa_org_forecast_lines.project_id%TYPE
21 := FND_API.G_MISS_NUM
22 ,p_task_id IN pa_org_forecast_lines.task_id%TYPE
23 := FND_API.G_MISS_NUM
24 ,p_period_name IN pa_org_forecast_lines.period_name%TYPE
25 := FND_API.G_MISS_CHAR
26 ,p_start_date IN pa_org_forecast_lines.start_date%TYPE
27 := FND_API.G_MISS_DATE
28 ,p_end_date IN pa_org_forecast_lines.end_date%TYPE
29 := FND_API.G_MISS_DATE
30 ,p_quantity IN pa_org_forecast_lines.quantity%TYPE
31 := FND_API.G_MISS_NUM
32 ,p_raw_cost IN pa_org_forecast_lines.raw_cost%TYPE
33 := FND_API.G_MISS_NUM
34 ,p_burdened_cost IN pa_org_forecast_lines.burdened_cost%TYPE
35 := FND_API.G_MISS_NUM
36 ,p_tp_cost_in IN pa_org_forecast_lines.tp_cost_in%TYPE
37 := FND_API.G_MISS_NUM
38 ,p_tp_cost_out IN pa_org_forecast_lines.tp_cost_out%TYPE
39 := FND_API.G_MISS_NUM
40 ,p_revenue IN pa_org_forecast_lines.revenue%TYPE
41 := FND_API.G_MISS_NUM
42 ,p_tp_revenue_in IN pa_org_forecast_lines.tp_revenue_in%TYPE
43 := FND_API.G_MISS_NUM
44 ,p_tp_revenue_out IN pa_org_forecast_lines.tp_revenue_out%TYPE
45 := FND_API.G_MISS_NUM
46 ,p_borrowed_revenue IN pa_org_forecast_lines.borrowed_revenue%TYPE
47 := FND_API.G_MISS_NUM
48 ,p_lent_resource_cost IN pa_org_forecast_lines.lent_resource_cost%TYPE
49 := FND_API.G_MISS_NUM
50 ,p_unassigned_time_cost IN pa_org_forecast_lines.unassigned_time_cost%TYPE
51 := FND_API.G_MISS_NUM
52 ,x_row_id OUT NOCOPY ROWID --File.Sql.39 bug 4440895
53 ,x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
54 IS
55 CURSOR C2 IS SELECT pa_org_forecast_lines_s.nextval FROM sys.dual;
56 BEGIN
57 IF (px_forecast_line_id IS NULL) OR
58 (px_forecast_line_id = FND_API.G_MISS_NUM) THEN
59 OPEN C2;
60 FETCH C2 INTO px_forecast_line_id;
61 CLOSE C2;
62 END IF;
63 insert into pa_org_forecast_lines(
64 forecast_line_id
65 ,record_version_number
66 ,creation_date
67 ,created_by
68 ,last_update_login
69 ,last_updated_by
70 ,last_update_date
71 ,forecast_element_id
72 ,budget_version_id
73 ,project_id
74 ,task_id
75 ,period_name
76 ,start_date
77 ,end_date
78 ,quantity
79 ,raw_cost
80 ,burdened_cost
81 ,tp_cost_in
82 ,tp_cost_out
83 ,revenue
84 ,tp_revenue_in
85 ,tp_revenue_out
86 ,borrowed_revenue
87 ,lent_resource_cost
88 ,unassigned_time_cost
89 ) values (
90 px_forecast_line_id
91 ,1
92 ,sysdate
93 ,fnd_global.user_id
94 ,fnd_global.login_id
95 ,fnd_global.user_id
96 ,sysdate
97 ,DECODE( p_forecast_element_id, FND_API.G_MISS_NUM, NULL,
98 p_forecast_element_id)
99 ,DECODE( p_budget_version_id, FND_API.G_MISS_NUM, NULL,
100 p_budget_version_id)
101 ,DECODE( p_project_id, FND_API.G_MISS_NUM, NULL, p_project_id)
102 ,DECODE( p_task_id, FND_API.G_MISS_NUM, NULL, p_task_id)
103 ,DECODE( p_period_name, FND_API.G_MISS_CHAR, NULL, p_period_name)
104 ,DECODE( p_start_date, FND_API.G_MISS_DATE, to_date(null), p_start_date)
105 ,DECODE( p_end_date, FND_API.G_MISS_DATE, to_date(null), p_end_date)
106 ,DECODE( p_quantity, FND_API.G_MISS_NUM, NULL, p_quantity)
107 ,DECODE( p_raw_cost, FND_API.G_MISS_NUM, NULL, p_raw_cost)
108 ,DECODE( p_burdened_cost, FND_API.G_MISS_NUM, NULL, p_burdened_cost)
109 ,DECODE( p_tp_cost_in, FND_API.G_MISS_NUM, NULL, p_tp_cost_in)
110 ,DECODE( p_tp_cost_out, FND_API.G_MISS_NUM, NULL, p_tp_cost_out)
111 ,DECODE( p_revenue, FND_API.G_MISS_NUM, NULL, p_revenue)
112 ,DECODE( p_tp_revenue_in, FND_API.G_MISS_NUM, NULL, p_tp_revenue_in)
113 ,DECODE( p_tp_revenue_out, FND_API.G_MISS_NUM, NULL, p_tp_revenue_out)
114 ,DECODE( p_borrowed_revenue, FND_API.G_MISS_NUM, NULL, p_borrowed_revenue)
115 ,DECODE( p_lent_resource_cost, FND_API.G_MISS_NUM, NULL, p_lent_resource_cost)
116 ,DECODE( p_unassigned_time_cost, FND_API.G_MISS_NUM, NULL, p_unassigned_time_cost));
117 EXCEPTION
118 WHEN OTHERS THEN
119 FND_MSG_PUB.add_exc_msg( p_pkg_name
120 => 'PA_FP_ORG_FORECAST_LINES_PKG.Update_Row'
121 ,p_procedure_name
122 => PA_DEBUG.G_Err_Stack);
123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
124 RAISE;
125 END Insert_Row;
126
127 PROCEDURE Update_Row
128 ( p_forecast_line_id IN pa_org_forecast_lines.forecast_line_id%TYPE
129 := FND_API.G_MISS_NUM
130 ,p_record_version_number IN NUMBER
131 := NULL
132 ,p_forecast_element_id IN pa_org_forecast_lines.forecast_element_id%TYPE
133 := FND_API.G_MISS_NUM
134 ,p_budget_version_id IN pa_org_forecast_lines.budget_version_id%TYPE
135 := FND_API.G_MISS_NUM
136 ,p_project_id IN pa_org_forecast_lines.project_id%TYPE
137 := FND_API.G_MISS_NUM
138 ,p_task_id IN pa_org_forecast_lines.task_id%TYPE
139 := FND_API.G_MISS_NUM
140 ,p_period_name IN pa_org_forecast_lines.period_name%TYPE
141 := FND_API.G_MISS_CHAR
142 ,p_start_date IN pa_org_forecast_lines.start_date%TYPE
143 := FND_API.G_MISS_DATE
144 ,p_end_date IN pa_org_forecast_lines.end_date%TYPE
145 := FND_API.G_MISS_DATE
146 ,p_quantity IN pa_org_forecast_lines.quantity%TYPE
147 := FND_API.G_MISS_NUM
148 ,p_raw_cost IN pa_org_forecast_lines.raw_cost%TYPE
149 := FND_API.G_MISS_NUM
150 ,p_burdened_cost IN pa_org_forecast_lines.burdened_cost%TYPE
151 := FND_API.G_MISS_NUM
152 ,p_tp_cost_in IN pa_org_forecast_lines.tp_cost_in%TYPE
153 := FND_API.G_MISS_NUM
154 ,p_tp_cost_out IN pa_org_forecast_lines.tp_cost_out%TYPE
155 := FND_API.G_MISS_NUM
156 ,p_revenue IN pa_org_forecast_lines.revenue%TYPE
157 := FND_API.G_MISS_NUM
158 ,p_tp_revenue_in IN pa_org_forecast_lines.tp_revenue_in%TYPE
159 := FND_API.G_MISS_NUM
160 ,p_tp_revenue_out IN pa_org_forecast_lines.tp_revenue_out%TYPE
161 := FND_API.G_MISS_NUM
162 ,p_borrowed_revenue IN pa_org_forecast_lines.borrowed_revenue%TYPE
163 := FND_API.G_MISS_NUM
164 ,p_lent_resource_cost IN pa_org_forecast_lines.lent_resource_cost%TYPE
165 := FND_API.G_MISS_NUM
166 ,p_unassigned_time_cost IN pa_org_forecast_lines.unassigned_time_cost%TYPE
167 := FND_API.G_MISS_NUM
168 ,p_row_id IN ROWID
169 := NULL
170 ,x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
171 IS
172 BEGIN
173 UPDATE pa_org_forecast_lines
174 SET
175 record_version_number = nvl(record_version_number,0) +1
176 ,last_update_login = fnd_global.login_id
177 ,last_updated_by = fnd_global.user_id
178 ,last_update_date = sysdate
179 ,forecast_element_id = DECODE( p_forecast_element_id, FND_API.G_MISS_NUM,
180 forecast_element_id, p_forecast_element_id)
181 ,budget_version_id = DECODE( p_budget_version_id, FND_API.G_MISS_NUM,
182 budget_version_id, p_budget_version_id)
183 ,project_id = DECODE( p_project_id, FND_API.G_MISS_NUM, project_id,
184 p_project_id)
185 ,task_id = DECODE( p_task_id, FND_API.G_MISS_NUM, task_id, p_task_id)
186 ,period_name = DECODE( p_period_name, FND_API.G_MISS_CHAR, period_name,
187 p_period_name)
188 ,start_date = DECODE( p_start_date, FND_API.G_MISS_DATE, start_date,
189 p_start_date)
190 ,end_date = DECODE( p_end_date, FND_API.G_MISS_DATE, end_date, p_end_date)
191 ,quantity = DECODE( p_quantity, FND_API.G_MISS_NUM, quantity, p_quantity)
192 ,raw_cost = DECODE( p_raw_cost, FND_API.G_MISS_NUM, raw_cost, p_raw_cost)
193 ,burdened_cost = DECODE( p_burdened_cost, FND_API.G_MISS_NUM, burdened_cost,
194 p_burdened_cost)
195 ,tp_cost_in = DECODE( p_tp_cost_in, FND_API.G_MISS_NUM, tp_cost_in,
196 p_tp_cost_in)
197 ,tp_cost_out = DECODE( p_tp_cost_out, FND_API.G_MISS_NUM, tp_cost_out,
198 p_tp_cost_out)
199 ,revenue = DECODE( p_revenue, FND_API.G_MISS_NUM, revenue, p_revenue)
200 ,tp_revenue_in = DECODE( p_tp_revenue_in, FND_API.G_MISS_NUM, tp_revenue_in,
201 p_tp_revenue_in)
202 ,tp_revenue_out = DECODE( p_tp_revenue_out, FND_API.G_MISS_NUM, tp_revenue_out,
203 p_tp_revenue_out)
204 ,borrowed_revenue = DECODE( p_borrowed_revenue, FND_API.G_MISS_NUM,
205 borrowed_revenue, p_borrowed_revenue)
206 ,lent_resource_cost = DECODE( p_lent_resource_cost, FND_API.G_MISS_NUM,
207 lent_resource_cost, p_lent_resource_cost)
208 ,unassigned_time_cost = DECODE( p_unassigned_time_cost, FND_API.G_MISS_NUM,
209 unassigned_time_cost, p_unassigned_time_cost)
210 WHERE forecast_line_id = p_forecast_line_id
211 AND nvl(p_record_version_number, nvl(record_version_number,0))
212 = nvl(record_version_number,0);
213
214 IF (SQL%NOTFOUND) THEN
215 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
216 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
217 x_return_status := FND_API.G_RET_STS_ERROR;
218 END IF;
219
220 EXCEPTION
221 WHEN OTHERS THEN
222 FND_MSG_PUB.add_exc_msg( p_pkg_name
223 => 'PA_FP_ORG_FORECAST_LINES_PKG.Update_Row'
224 ,p_procedure_name
225 => PA_DEBUG.G_Err_Stack);
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
227 RAISE;
228 END Update_Row;
229
230 PROCEDURE Lock_Row
231 ( p_forecast_line_id IN pa_org_forecast_lines.forecast_line_id%TYPE
232 := FND_API.G_MISS_NUM
233 ,p_record_version_number IN NUMBER
234 := NULL
235 ,p_row_id IN ROWID
236 := NULL
237 ,x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
238 IS
239 l_row_id ROWID;
240 BEGIN
241 SELECT rowid into l_row_id
242 FROM pa_org_forecast_lines
243 WHERE forecast_line_id = p_forecast_line_id
244 OR rowid = p_row_id
245 FOR UPDATE NOWAIT;
246 EXCEPTION
247 WHEN OTHERS THEN
248 FND_MSG_PUB.add_exc_msg( p_pkg_name
249 => 'PA_FP_ORG_FORECAST_LINES_PKG.Update_Row'
250 ,p_procedure_name
251 => PA_DEBUG.G_Err_Stack);
252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
253 RAISE;
254 END Lock_Row;
255
256 PROCEDURE Delete_Row
257 ( p_forecast_line_id IN pa_org_forecast_lines.forecast_line_id%TYPE
258 := FND_API.G_MISS_NUM
259 ,p_record_version_number IN NUMBER
260 := NULL
261 ,p_row_id IN ROWID
262 := NULL
263 ,x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
264 IS
265 BEGIN
266 IF (p_forecast_line_id IS NOT NULL AND p_forecast_line_id <>
267 FND_API.G_MISS_NUM) THEN
268
269 DELETE FROM pa_org_forecast_lines
270 WHERE forecast_line_id = p_forecast_line_id
271 AND nvl(p_record_version_number, nvl(record_version_number,0))
272 = nvl(record_version_number,0);
273 ELSIF (p_row_id IS NOT NULL) THEN
274 DELETE FROM pa_org_forecast_lines
275 WHERE rowid = p_row_id
276 AND nvl(p_record_version_number, nvl(record_version_number,0))
277 = nvl(record_version_number,0);
278 END IF;
279 IF (SQL%NOTFOUND) THEN
280 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
281 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
282 x_return_status := FND_API.G_RET_STS_ERROR;
283 END IF;
284 EXCEPTION
285 WHEN OTHERS THEN
286 FND_MSG_PUB.add_exc_msg( p_pkg_name
287 => 'PA_FP_ORG_FORECAST_LINES_PKG.Delete_Row'
288 ,p_procedure_name
289 => PA_DEBUG.G_Err_Stack);
290 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
291 RAISE;
292 END Delete_Row;
293
294 End pa_fp_org_forecast_lines_pkg;