[Home] [Help]
PACKAGE BODY: APPS.PA_FP_EXCLUDED_ELEMENTS_PKG
Source
1 PACKAGE BODY pa_fp_excluded_elements_pkg as
2 /* $Header: PAFPXELB.pls 120.1 2005/08/19 16:32:06 mwasowic noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_FP_EXCLUDED_ELEMENTS_PKG';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'PAFPEXLB.pls';
6
7 PROCEDURE Insert_Row
8 (
9 p_proj_fp_options_id IN pa_fp_excluded_elements.proj_fp_options_id%TYPE
10 ,p_project_id IN pa_fp_excluded_elements.project_id%TYPE
11 ,p_fin_plan_type_id IN pa_fp_excluded_elements.fin_plan_type_id%TYPE
12 ,p_element_type IN pa_fp_excluded_elements.element_type%TYPE
13 ,p_fin_plan_version_id IN pa_fp_excluded_elements.fin_plan_version_id%TYPE
14 ,p_task_id IN pa_fp_excluded_elements.task_id%TYPE
15 ,x_row_id OUT NOCOPY ROWID --File.Sql.39 bug 4440895
16 ,x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
17 IS
18 BEGIN
19 x_return_status := FND_API.G_RET_STS_SUCCESS;
20
21 INSERT INTO pa_fp_excluded_elements(
22 proj_fp_options_id
23 ,project_id
24 ,fin_plan_type_id
25 ,element_type
26 ,fin_plan_version_id
27 ,task_id
28 ,record_version_number
29 ,last_update_date
30 ,last_updated_by
31 ,creation_date
32 ,created_by
33 ,last_update_login
34 ) VALUES (
35 p_proj_fp_options_id
36 ,p_project_id
37 ,p_fin_plan_type_id
38 ,p_element_type
39 ,p_fin_plan_version_id
40 ,p_task_id
41 ,1
42 ,sysdate
43 ,fnd_global.user_id
44 ,sysdate
45 ,fnd_global.user_id
46 ,fnd_global.login_id)
47 RETURNING rowid INTO x_row_id;
48
49 EXCEPTION
50 WHEN OTHERS THEN
51 FND_MSG_PUB.add_exc_msg( p_pkg_name
52 => 'PA_FP_EXCLUDED_ELEMENTS_PKG'
53 ,p_procedure_name
54 => 'Insert_Row');
55 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
56 RAISE;
57 END Insert_Row;
58
59 PROCEDURE Update_Row
60 (
61 p_proj_fp_options_id IN pa_fp_excluded_elements.proj_fp_options_id%TYPE
62 ,p_project_id IN pa_fp_excluded_elements.project_id%TYPE
63 ,p_fin_plan_type_id IN pa_fp_excluded_elements.fin_plan_type_id%TYPE
64 ,p_element_type IN pa_fp_excluded_elements.element_type%TYPE
65 ,p_fin_plan_version_id IN pa_fp_excluded_elements.fin_plan_version_id%TYPE
66 ,p_task_id IN pa_fp_excluded_elements.task_id%TYPE
67 ,p_record_version_number IN pa_fp_excluded_elements.record_version_number%TYPE
68 ,p_row_id IN ROWID
69 ,x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
70 IS
71 BEGIN
72 x_return_status := FND_API.G_RET_STS_SUCCESS;
73
74 UPDATE pa_fp_excluded_elements
75 SET proj_fp_options_id = DECODE(p_proj_fp_options_id,
76 FND_API.G_MISS_NUM, Null,
77 nvl(p_proj_fp_options_id,proj_fp_options_id))
78 ,project_id = DECODE(p_project_id,
79 FND_API.G_MISS_NUM, Null,
80 nvl(p_project_id, project_id))
81 ,fin_plan_type_id = DECODE(p_fin_plan_type_id,
82 FND_API.G_MISS_NUM, Null,
83 nvl(p_fin_plan_type_id,fin_plan_type_id))
84 ,element_type = DECODE(p_element_type,
85 FND_API.G_MISS_CHAR,Null,
86 nvl(p_element_type,element_type))
87 ,fin_plan_version_id = DECODE(p_fin_plan_version_id,
88 FND_API.G_MISS_NUM, Null,
89 nvl(p_fin_plan_version_id, fin_plan_version_id))
90 ,task_id = DECODE(p_task_id,
91 FND_API.G_MISS_NUM, Null,
92 nvl(p_task_id,task_id))
93 ,record_version_number = DECODE(p_record_version_number,
94 FND_API.G_MISS_NUM, Null,
95 nvl(p_record_version_number,nvl(record_version_number,0)) + 1)
96 ,last_update_date = SYSDATE
97 ,last_updated_by = FND_GLOBAL.USER_ID
98 ,last_update_login = FND_GLOBAL.LOGIN_ID
99 WHERE rowid = p_row_id
100 AND nvl(record_version_number,0) = nvl(p_record_version_number, nvl(record_version_number,0));
101
102 IF (SQL%NOTFOUND) THEN
103 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
104 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
105 x_return_status := FND_API.G_RET_STS_ERROR;
106 END IF;
107
108 EXCEPTION
109 WHEN OTHERS THEN
110 FND_MSG_PUB.add_exc_msg( p_pkg_name
111 => 'PA_FP_EXCLUDED_ELEMENTS_PKG'
112 ,p_procedure_name
113 => 'Update_Row');
114 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
115 RAISE;
116 END Update_Row;
117
118 PROCEDURE Delete_Row
119 ( p_proj_fp_options_id IN pa_fp_excluded_elements.proj_fp_options_id%TYPE := Null
120 ,p_element_type IN pa_fp_excluded_elements.element_type%TYPE := Null
121 ,p_task_id IN pa_fp_excluded_elements.task_id%TYPE := Null
122 ,p_row_id IN ROWID
123 ,p_record_version_number IN NUMBER := Null
124 ,x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
125 IS
126 BEGIN
127 x_return_status := FND_API.G_RET_STS_SUCCESS;
128
129 IF p_row_id IS NOT NULL THEN
130
131 DELETE FROM pa_fp_excluded_elements
132 WHERE rowid = p_row_id
133 AND nvl(record_version_number,0) = nvl(p_record_version_number, nvl(record_version_number,0));
134
135 ELSE
136
137 DELETE FROM pa_fp_excluded_elements
138 WHERE proj_fp_options_id = p_proj_fp_options_id
139 AND element_type = p_element_type
140 AND task_id = p_task_id
141 AND nvl(record_version_number,0) = nvl(p_record_version_number, nvl(record_version_number,0));
142
143 END IF;
144
145 IF (SQL%NOTFOUND) THEN
146 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
147 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
148 x_return_status := FND_API.G_RET_STS_ERROR;
149 END IF;
150
151 EXCEPTION
152 WHEN OTHERS THEN
153 FND_MSG_PUB.add_exc_msg( p_pkg_name
154 => 'PA_FP_EXCLUDED_ELEMENTS_PKG'
155 ,p_procedure_name
156 => 'Delete_Row');
157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
158 RAISE;
159 END Delete_Row;
160
161 PROCEDURE Lock_Row
162 ( p_row_id IN ROWID
163 ,p_record_version_number IN NUMBER
164 ,x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
165 IS
166 l_row_id ROWID;
167 BEGIN
168 x_return_status := FND_API.G_RET_STS_SUCCESS;
169
170 SELECT rowid
171 INTO l_row_id
172 FROM pa_fp_excluded_elements
173 WHERE rowid = p_row_id
174 AND nvl(record_version_number,0) = nvl(p_record_version_number, nvl(record_version_number,0))
175 FOR UPDATE NOWAIT;
176
177 EXCEPTION
178 WHEN OTHERS THEN
179 FND_MSG_PUB.add_exc_msg( p_pkg_name
180 => 'PA_FP_EXCLUDED_ELEMENTS_PKG'
181 ,p_procedure_name
182 => 'Lock_Row');
183 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
184 RAISE;
185 END Lock_Row;
186
187 END pa_fp_excluded_elements_pkg;