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