[Home] [Help]
PACKAGE BODY: APPS.PA_ORG_FCST_ELEMENTS_PKG
Source
1 PACKAGE BODY pa_org_fcst_elements_pkg as
2 /* $Header: PAFPFETB.pls 120.1 2005/08/19 16:26:33 mwasowic noship $ */
3 -- Start of Comments
4 -- Package name : PA_ORG_FCST_ELEMENTS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PA_ORG_FCST_ELEMENTS_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pafpfetb.pls';
13
14 PROCEDURE Insert_Row
15 ( px_forecast_element_id IN OUT NOCOPY pa_org_fcst_elements.forecast_element_id%TYPE --File.Sql.39 bug 4440895
16 ,p_organization_id IN pa_org_fcst_elements.organization_id%TYPE
17 := FND_API.G_MISS_NUM
18 ,p_budget_version_id IN pa_org_fcst_elements.budget_version_id%TYPE
19 := FND_API.G_MISS_NUM
20 ,p_project_id IN pa_org_fcst_elements.project_id%TYPE
21 := FND_API.G_MISS_NUM
22 ,p_task_id IN pa_org_fcst_elements.task_id%TYPE
23 := FND_API.G_MISS_NUM
24 ,p_provider_receiver_code IN pa_org_fcst_elements.provider_receiver_code%TYPE
25 := FND_API.G_MISS_CHAR
26 ,p_other_organization_id IN pa_org_fcst_elements.other_organization_id%TYPE
27 := FND_API.G_MISS_NUM
28 ,p_txn_project_id IN pa_org_fcst_elements.txn_project_id%TYPE
29 := FND_API.G_MISS_NUM
30 ,p_assignment_id IN pa_org_fcst_elements.assignment_id%TYPE
31 := FND_API.G_MISS_NUM
32 ,p_resource_id IN pa_org_fcst_elements.resource_id%TYPE
33 := FND_API.G_MISS_NUM
34 ,x_row_id OUT NOCOPY ROWID --File.Sql.39 bug 4440895
35 ,x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
36 IS
37 CURSOR C2 IS SELECT pa_org_fcst_elements_s.nextval FROM sys.dual;
38 BEGIN
39 IF (px_forecast_element_id IS NULL) OR
40 (px_forecast_element_id = FND_API.G_MISS_NUM) THEN
41 OPEN C2;
42 FETCH C2 INTO px_forecast_element_id;
43 CLOSE C2;
44 END IF;
45 INSERT INTO pa_org_fcst_elements(
46 forecast_element_id
47 ,record_version_number
48 ,creation_date
49 ,created_by
50 ,last_update_login
51 ,last_updated_by
52 ,last_update_date
53 ,organization_id
54 ,budget_version_id
55 ,project_id
56 ,task_id
57 ,provider_receiver_code
58 ,other_organization_id
59 ,txn_project_id
60 ,assignment_id
61 ,resource_id
62 ) values (
63 px_forecast_element_id
64 ,1
65 ,sysdate
66 ,fnd_global.user_id
67 ,fnd_global.login_id
68 ,fnd_global.user_id
69 ,sysdate
70 ,DECODE( p_organization_id, FND_API.G_MISS_NUM, NULL, p_organization_id)
71 ,DECODE( p_budget_version_id, FND_API.G_MISS_NUM, NULL, p_budget_version_id)
72 ,DECODE( p_project_id, FND_API.G_MISS_NUM, NULL, p_project_id)
73 ,DECODE( p_task_id, FND_API.G_MISS_NUM, NULL, p_task_id)
74 ,DECODE( p_provider_receiver_code, FND_API.G_MISS_CHAR, NULL,
75 p_provider_receiver_code)
76 ,DECODE( p_other_organization_id, FND_API.G_MISS_NUM, NULL,
77 p_other_organization_id)
78 ,DECODE( p_txn_project_id, FND_API.G_MISS_NUM, NULL, p_txn_project_id)
79 ,DECODE( p_assignment_id, FND_API.G_MISS_NUM, NULL, p_assignment_id)
80 ,DECODE( p_resource_id, FND_API.G_MISS_NUM, NULL, p_resource_id));
81 EXCEPTION
82 WHEN OTHERS THEN
83 FND_MSG_PUB.add_exc_msg( p_pkg_name
84 => 'PA_ORG_FCST_ELEMENTS_PKG.Insert_Row'
85 ,p_procedure_name
86 => PA_DEBUG.G_Err_Stack);
87 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
88 RAISE;
89 END Insert_Row;
90
91 PROCEDURE update_row
92 ( p_forecast_element_id IN pa_org_fcst_elements.forecast_element_id%TYPE
93 := FND_API.G_MISS_NUM
94 ,p_record_version_number IN NUMBER
95 := NULL
96 ,p_organization_id IN pa_org_fcst_elements.organization_id%TYPE
97 := FND_API.G_MISS_NUM
98 ,p_budget_version_id IN pa_org_fcst_elements.budget_version_id%TYPE
99 := FND_API.G_MISS_NUM
100 ,p_project_id IN pa_org_fcst_elements.project_id%TYPE
101 := FND_API.G_MISS_NUM
102 ,p_task_id IN pa_org_fcst_elements.task_id%TYPE
103 := FND_API.G_MISS_NUM
104 ,p_provider_receiver_code IN pa_org_fcst_elements.provider_receiver_code%TYPE
105 := FND_API.G_MISS_CHAR
106 ,p_other_organization_id IN pa_org_fcst_elements.other_organization_id%TYPE
107 := FND_API.G_MISS_NUM
108 ,p_txn_project_id IN pa_org_fcst_elements.txn_project_id%TYPE
109 := FND_API.G_MISS_NUM
110 ,p_assignment_id IN pa_org_fcst_elements.assignment_id%TYPE
111 := FND_API.G_MISS_NUM
112 ,p_resource_id IN pa_org_fcst_elements.resource_id%TYPE
113 := FND_API.G_MISS_NUM
114 ,p_row_id IN ROWID
115 := NULL
116 ,x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
117 IS
118 BEGIN
119 UPDATE pa_org_fcst_elements
120 SET
121 record_version_number = nvl(record_version_number,0) +1
122 ,last_update_login = fnd_global.login_id
123 ,last_updated_by = fnd_global.user_id
124 ,last_update_date = sysdate
125 ,forecast_element_id = DECODE( p_forecast_element_id, FND_API.G_MISS_NUM,
126 forecast_element_id, p_forecast_element_id)
127 ,organization_id = DECODE( p_organization_id, FND_API.G_MISS_NUM,
128 organization_id, p_organization_id)
129 ,budget_version_id = DECODE( p_budget_version_id, FND_API.G_MISS_NUM,
130 budget_version_id, p_budget_version_id)
131 ,project_id = DECODE( p_project_id, FND_API.G_MISS_NUM, project_id,
132 p_project_id)
133 ,task_id = DECODE( p_task_id, FND_API.G_MISS_NUM, task_id, p_task_id)
134 ,provider_receiver_code = DECODE( p_provider_receiver_code,
135 FND_API.G_MISS_CHAR,
136 provider_receiver_code,
137 p_provider_receiver_code)
138 ,other_organization_id = DECODE( p_other_organization_id, FND_API.G_MISS_NUM,
139 other_organization_id,
140 p_other_organization_id)
141 ,txn_project_id = DECODE( p_txn_project_id, FND_API.G_MISS_NUM, txn_project_id,
142 p_txn_project_id)
143 ,assignment_id = DECODE( p_assignment_id, FND_API.G_MISS_NUM, assignment_id,
144 p_assignment_id)
145 ,resource_id = DECODE( p_resource_id, FND_API.G_MISS_NUM, resource_id,
146 p_resource_id)
147 WHERE forecast_element_id = p_forecast_element_id
148 AND nvl(p_record_version_number, nvl(record_version_number,0)) =
149 nvl(record_version_number,0);
150
151 IF (SQL%NOTFOUND) THEN
152 PA_UTILS.Add_message ( p_app_short_name => 'PA'
153 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
154 x_return_status := FND_API.G_RET_STS_ERROR;
155 END IF;
156
157 EXCEPTION
158 WHEN OTHERS THEN
159 FND_MSG_PUB.add_exc_msg( p_pkg_name
160 => 'PA_ORG_FCST_ELEMENTS_PKG.Update_Row'
161 ,p_procedure_name
162 => PA_DEBUG.G_Err_Stack);
163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
164 RAISE;
165 END Update_Row;
166
167 PROCEDURE Delete_Row
168 ( p_forecast_element_id IN pa_org_fcst_elements.forecast_element_id%TYPE
169 := FND_API.G_MISS_NUM
170 ,p_record_version_number IN NUMBER
171 := NULL
172 ,p_row_id IN ROWID
173 := NULL
174 ,x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
175 IS
176 BEGIN
177 IF (p_forecast_element_id IS NOT NULL OR
178 p_forecast_element_id <> FND_API.G_MISS_NUM) THEN
179
180 DELETE FROM pa_org_fcst_elements
181 WHERE forecast_element_id = p_forecast_element_id
182 AND nvl(p_record_version_number, nvl(record_version_number,0)) =
183 nvl(record_version_number,0);
184
185 ELSIF (p_row_id IS NOT NULL) THEN
186
187 DELETE FROM pa_org_fcst_elements
188 WHERE rowid = p_row_id
189 AND nvl(p_record_version_number, nvl(record_version_number,0)) =
190 nvl(record_version_number,0);
191 END IF;
192
193 IF (SQL%NOTFOUND) THEN
194 PA_UTILS.Add_message ( p_app_short_name => 'PA'
195 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
196 x_return_status := FND_API.G_RET_STS_ERROR;
197 END IF;
198
199 EXCEPTION
200 WHEN OTHERS THEN
201 FND_MSG_PUB.add_exc_msg( p_pkg_name
202 => 'PA_ORG_FCST_ELEMENTS_PKG.Delete_Row'
203 ,p_procedure_name
204 => PA_DEBUG.G_Err_Stack);
205 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
206 RAISE;
207 END Delete_Row;
208
209 PROCEDURE Lock_Row
210 ( p_forecast_element_id IN pa_org_fcst_elements.forecast_element_id%TYPE
211 := FND_API.G_MISS_NUM
212 ,p_record_version_number IN NUMBER
213 := NULL
214 ,p_row_id IN ROWID
215 := NULL
216 ,x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
217 IS
218 l_row_id ROWID;
219 BEGIN
220 SELECT rowid into l_row_id
221 FROM pa_org_fcst_elements
222 WHERE forecast_element_id = p_forecast_element_id
223 OR rowid = p_row_id
224 FOR UPDATE NOWAIT;
225
226 EXCEPTION
227 WHEN OTHERS THEN
228 FND_MSG_PUB.add_exc_msg( p_pkg_name
229 => 'PA_ORG_FCST_ELEMENTS_PKG.Lock_Row'
230 ,p_procedure_name
231 => PA_DEBUG.G_Err_Stack);
232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
233 RAISE;
234 END Lock_Row;
235
236 End pa_org_fcst_elements_pkg;