[Home] [Help]
PACKAGE BODY: APPS.PA_CONTROL_ITEMS_PKG
Source
1 PACKAGE BODY PA_CONTROL_ITEMS_PKG AS
2 --$Header: PACICITB.pls 120.3 2007/02/08 11:30:15 sukhanna noship $
3
4
5 procedure INSERT_ROW (
6 p_ci_type_id IN NUMBER
7 ,p_summary IN VARCHAR2
8 ,p_status_code IN VARCHAR2
9 ,p_owner_id IN NUMBER
10 ,p_highlighted_flag IN VARCHAR2
11 ,p_progress_status_code IN VARCHAR2
12 ,p_progress_as_of_date IN DATE
13 ,p_classification_code IN NUMBER
14 ,p_reason_code IN NUMBER
15 ,p_project_id IN NUMBER
16 ,p_last_modified_by_id IN NUMBER
17 ,p_object_type IN VARCHAR2 := NULL
18 ,p_object_id IN NUMBER := NULL
19 ,p_ci_number IN VARCHAR2 := NULL
20 ,p_date_required IN DATE := NULL
21 ,p_date_closed IN DATE := NULL
22 ,p_closed_by_id IN NUMBER := NULL
23
24
25 ,p_description IN VARCHAR2 := NULL
26 ,p_status_overview IN VARCHAR2 := NULL
27 ,p_resolution IN VARCHAR2 := NULL
28 ,p_resolution_code IN NUMBER := NULL
29 ,p_priority_code IN VARCHAR2 := NULL
30 ,p_effort_level_code IN VARCHAR2 := NULL
31 ,p_open_action_num IN NUMBER := NULL
32
33 ,p_price IN NUMBER := NULL
34 ,p_price_currency_code IN VARCHAR2 := NULL
35 ,p_source_type_code IN VARCHAR2 := NULL
36 ,p_source_comment IN VARCHAR2 := NULL
37 ,p_source_number IN VARCHAR2 := NULL
38 ,p_source_date_received IN DATE := NULL
39
40 ,p_source_organization IN VARCHAR2 := NULL
41 ,p_source_person IN VARCHAR2 := NULL
42
43 ,p_attribute_category IN VARCHAR2 := NULL
44 ,p_attribute1 IN VARCHAR2 := NULL
45 ,p_attribute2 IN VARCHAR2 := NULL
46 ,p_attribute3 IN VARCHAR2 := NULL
47 ,p_attribute4 IN VARCHAR2 := NULL
48 ,p_attribute5 IN VARCHAR2 := NULL
49 ,p_attribute6 IN VARCHAR2 := NULL
50 ,p_attribute7 IN VARCHAR2 := NULL
51 ,p_attribute8 IN VARCHAR2 := NULL
52 ,p_attribute9 IN VARCHAR2 := NULL
53 ,p_attribute10 IN VARCHAR2 := NULL
54 ,p_attribute11 IN VARCHAR2 := NULL
55 ,p_attribute12 IN VARCHAR2 := NULL
56 ,p_attribute13 IN VARCHAR2 := NULL
57 ,p_attribute14 IN VARCHAR2 := NULL
58 ,p_attribute15 IN VARCHAR2 := NULL
59
60 ,px_ci_id IN OUT NOCOPY NUMBER
61 ,x_return_status OUT NOCOPY VARCHAR2
62 ,x_msg_count OUT NOCOPY NUMBER
63 ,x_msg_data OUT NOCOPY VARCHAR2
64 ,p_orig_system_code IN VARCHAR2 := NULL
65 ,p_orig_system_reference IN VARCHAR2 := NULL
66
67 ) is
68
69 l_rowid ROWID;
70 l_ci_id NUMBER;
71
72
73 cursor C is select ROWID from PA_CONTROL_ITEMS
74 where ci_id = px_ci_id;
75
76 BEGIN
77
78 x_return_status := FND_API.G_RET_STS_SUCCESS;
79
80 --get the unique control item id from the Oracle Sequence
81 IF (px_ci_id is null) THEN
82 SELECT pa_control_items_s.nextval
83 INTO l_ci_id
84 FROM DUAL;
85 px_ci_id := l_ci_id;
86 END IF;
87
88
89 insert into PA_CONTROL_ITEMS (
90 ci_id
91 ,ci_type_id
92 ,summary
93 ,status_code
94 ,owner_id
95 ,highlighted_flag
96 ,progress_status_code
97 ,progress_as_of_date
98 ,classification_code_id
99 ,reason_code_id
100 ,RECORD_VERSION_NUMBER
101 ,project_id
102 ,LAST_MODIFICATION_DATE
103 ,LAST_MODIFIED_BY_ID
104 ,CREATION_DATE
105 ,CREATED_BY
106 ,LAST_UPDATE_DATE
107 ,LAST_UPDATED_BY
108 ,LAST_UPDATE_LOGIN
109
110 ,object_type
111 ,object_id
112 ,ci_number
113 ,date_required
114 ,date_closed
115 ,closed_by_id
116 ,description
117 ,status_overview
118 ,resolution
119 ,resolution_code_id
120 ,priority_code
121 ,effort_level_code
122 ,open_action_num
123 ,price
124 ,price_currency_code
125 ,source_type_code
126 ,source_comment
127 ,source_number
128 ,source_date_received
129 ,source_organization
130 ,source_person
131
132 ,attribute_category
133 ,attribute1
134 ,attribute2
135 ,attribute3
136 ,attribute4
137 ,attribute5
138 ,attribute6
139 ,attribute7
140 ,attribute8
141 ,attribute9
142 ,attribute10
143 ,attribute11
144 ,attribute12
145 ,attribute13
146 ,attribute14
147 ,attribute15
148 ,orig_system_code
149 ,orig_system_reference
150
151 ) VALUES (
152 px_ci_id
153 ,p_ci_type_id
154 ,p_summary
155 ,p_status_code
156 ,p_owner_id
157 ,p_highlighted_flag
158 ,p_progress_status_code
159 ,p_progress_as_of_date
160 ,p_classification_code
161 ,p_reason_code
162 ,1 --record_version_number
163 ,p_project_id
164 ,sysdate --last_modification_date
165 ,p_last_modified_by_id --hz_parties.party_id
166 ,sysdate --creation_date
167 ,fnd_global.user_id --created_by
168 ,sysdate --last_update_date
169 ,fnd_global.user_id --last_updated_by
170 ,fnd_global.user_id --last_update_login
171 ,p_object_type
172 ,p_object_id
173 ,p_ci_number
174 ,p_date_required
175 ,p_date_closed
176 ,p_closed_by_id
177 ,p_description
178 ,p_status_overview
179
180 ,p_resolution
181 ,p_resolution_code
182 ,p_priority_code
183 ,p_effort_level_code
184 ,nvl(p_open_action_num,0)
185 ,p_price
186 ,p_price_currency_code
187 ,p_source_type_code
188 ,p_source_comment
189 ,p_source_number
190 ,p_source_date_received
191 ,p_source_organization
192 ,p_source_person
193
194 ,p_attribute_category
195 ,p_attribute1
196 ,p_attribute2
197 ,p_attribute3
198 ,p_attribute4
199 ,p_attribute5
200 ,p_attribute6
201 ,p_attribute7
202 ,p_attribute8
203 ,p_attribute9
204 ,p_attribute10
205 ,p_attribute11
206 ,p_attribute12
207 ,p_attribute13
208 ,p_attribute14
209 ,p_attribute15
210 ,p_orig_system_code
211 ,p_orig_system_reference
212 );
213
214
215 open c;
216 fetch c into l_ROWID;
217 if (c%notfound) then
218 close c;
219 raise no_data_found;
220 end if;
221 close c;
222 /* px_ci_id := l_ci_id; */ /* Bug#3297238 */
223
224
225 EXCEPTION
226 WHEN OTHERS THEN
227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
228 RAISE;
229
230 end INSERT_ROW;
231
232 procedure UPDATE_ROW (
233 p_ci_id IN NUMBER
234 ,p_ci_type_id IN NUMBER
235 ,p_summary IN VARCHAR2
236 ,p_status_code IN VARCHAR2
237 ,p_owner_id IN NUMBER
238 ,p_highlighted_flag IN VARCHAR2
239 ,p_progress_status_code IN VARCHAR2
240 ,p_progress_as_of_date IN DATE
241 ,p_classification_code IN NUMBER
242 ,p_reason_code IN NUMBER
243 ,p_record_version_number IN NUMBER
244
245 ,p_project_id IN NUMBER
246 ,p_last_modified_by_id IN NUMBER
247 ,p_object_type IN VARCHAR2 := NULL
248 ,p_object_id IN NUMBER := NULL
249 ,p_ci_number IN VARCHAR2 := NULL
250 ,p_date_required IN DATE := NULL
251 ,p_date_closed IN DATE := NULL
252 ,p_closed_by_id IN NUMBER := NULL
253 ,p_description IN VARCHAR2 := NULL
254 ,p_status_overview IN VARCHAR2 := NULL
255
256 ,p_resolution IN VARCHAR2 := NULL
257 ,p_resolution_code IN NUMBER := NULL
258 ,p_priority_code IN VARCHAR2 := NULL
259 ,p_effort_level_code IN VARCHAR2 := NULL
260 ,p_open_action_num IN NUMBER := NULL
261
262 ,p_price IN NUMBER := NULL
263 ,p_price_currency_code IN VARCHAR2 := NULL
264 ,p_source_type_code IN VARCHAR2 := NULL
265 ,p_source_comment IN VARCHAR2 := NULL
266 ,p_source_number IN VARCHAR2 := NULL
267 ,p_source_date_received IN DATE := NULL
268 ,p_source_organization IN VARCHAR2 := NULL
269 ,p_source_person IN VARCHAR2 := NULL
270
271 ,p_attribute_category IN VARCHAR2 := NULL
272
273 ,p_attribute1 IN VARCHAR2 := NULL
274 ,p_attribute2 IN VARCHAR2 := NULL
275 ,p_attribute3 IN VARCHAR2 := NULL
276 ,p_attribute4 IN VARCHAR2 := NULL
277 ,p_attribute5 IN VARCHAR2 := NULL
278 ,p_attribute6 IN VARCHAR2 := NULL
279 ,p_attribute7 IN VARCHAR2 := NULL
280 ,p_attribute8 IN VARCHAR2 := NULL
281 ,p_attribute9 IN VARCHAR2 := NULL
282 ,p_attribute10 IN VARCHAR2 := NULL
283 ,p_attribute11 IN VARCHAR2 := NULL
284 ,p_attribute12 IN VARCHAR2 := NULL
285 ,p_attribute13 IN VARCHAR2 := NULL
286 ,p_attribute14 IN VARCHAR2 := NULL
287 ,p_attribute15 IN VARCHAR2 := NULL
288 ,x_return_status OUT NOCOPY VARCHAR2
289 ,x_msg_count OUT NOCOPY NUMBER
290 ,x_msg_data OUT NOCOPY VARCHAR2
291 ,p_last_updated_by in NUMBER default fnd_global.user_id --Added the parameter for bug# 3877985
292 ,p_last_update_date in DATE default sysdate --Added the parameter for bug# 3877985
293 ,p_last_update_login in NUMBER default fnd_global.user_id --Added the parameter for bug# 3877985
294
295 ) is
296 begin
297 x_return_status := FND_API.G_RET_STS_SUCCESS;
298
299 update PA_CONTROL_ITEMS set
300 ci_type_id = Nvl(p_ci_type_id,ci_type_id)
301 ,summary = Nvl(p_summary,summary)
302 ,status_code = Nvl(p_status_code, status_code)
303 ,owner_id = Nvl(p_owner_id,owner_id)
304 ,highlighted_flag = Nvl(p_highlighted_flag, highlighted_flag)
305 ,progress_status_code = Nvl(p_progress_status_code, progress_status_code)
306 ,progress_as_of_date = Nvl(p_progress_as_of_date, progress_as_of_date)
307 ,classification_code_id = Nvl(p_classification_code,classification_code_id)
308 ,reason_code_id = Nvl(p_reason_code,reason_code_id)
309 ,RECORD_VERSION_NUMBER = record_version_number +1
310 ,project_id = Nvl(p_project_id,project_id)
311 ,LAST_MODIFICATION_DATE = SYSDATE
312 ,last_modified_by_id = p_last_modified_by_id
313 ,LAST_UPDATE_DATE = p_last_update_date --Modified for bug# 3877985
314 ,LAST_UPDATED_BY = p_last_updated_by --Modified for bug# 3877985
315 ,LAST_UPDATE_LOGIN = p_last_update_login --Modified for bug# 3877985
316 ,object_type = p_object_type
317 ,object_id = p_object_id
318 ,ci_number = p_ci_number
319 ,date_required = p_date_required
320 ,date_closed = p_date_closed
321 ,closed_by_id = p_closed_by_id
322 ,description = p_description
323 ,status_overview = p_status_overview
324 ,resolution = p_resolution
325 ,resolution_code_id = p_resolution_code
326 ,priority_code = p_priority_code
327 ,effort_level_code = p_effort_level_code
328 ,open_action_num = nvl(p_open_action_num,open_action_num)
329 ,price = p_price
330 ,price_currency_code = p_price_currency_code
331 ,source_type_code = p_source_type_code
332 ,source_comment = p_source_comment
333 ,source_number = p_source_number
334 ,source_date_received = p_source_date_received
335 ,source_organization = p_source_organization--, source_org_id)
336 ,source_person = p_source_person --, source_person_id)
337
338 ,attribute_category = p_attribute_category--, attribute1)
339
340 ,attribute1 = p_attribute1--, attribute1)
341 ,attribute2 = p_attribute2-- , attribute2)
342 ,attribute3 = p_attribute3--, attribute3)
343 ,attribute4 = p_attribute4--, attribute4)
344 ,attribute5 = p_attribute5--, attribute5)
345 ,attribute6 = p_attribute6--, attribute6)
346 ,attribute7 = p_attribute7--, attribute7)
347 ,attribute8 = p_attribute8--, attribute8)
348 ,attribute9 = p_attribute9--, attribute9)
349 ,attribute10 = p_attribute10--, attribute10)
350 ,attribute11 = p_attribute11--, attribute11)
351 ,attribute12 = p_attribute12--, attribute12)
352 ,attribute13 = p_attribute13--, attribute13)
353 ,attribute14 = p_attribute14--, attribute14)
354 ,attribute15 = p_attribute15--, attribute15)
355 where ci_id = p_ci_id
356 AND record_version_number = Nvl(p_record_version_number, record_version_number);
357
358 if (sql%notfound) then
359 PA_UTILS.Add_Message ( p_app_short_name => 'PA',p_msg_name => 'PA_XC_RECORD_CHANGED');
360 x_return_status := FND_API.G_RET_STS_ERROR;
361 end if;
362
363 EXCEPTION
364 WHEN OTHERS THEN
365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
366 RAISE;
367 end UPDATE_ROW;
368
369
370
371 procedure DELETE_ROW (
372 p_ci_id IN NUMBER
373 ,p_record_version_number IN NUMBER
374 ,x_return_status OUT NOCOPY VARCHAR2
375 ,x_msg_count OUT NOCOPY NUMBER
376 ,x_msg_data OUT NOCOPY VARCHAR2
377
378 ) is
379 begin
380 x_return_status := FND_API.G_RET_STS_SUCCESS;
381
382 DELETE FROM PA_CONTROL_ITEMS
383 where ci_id = p_ci_id
384 and record_version_number = p_record_version_number;
385
386
387 EXCEPTION
388 WHEN OTHERS THEN
389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
390 RAISE;
391 end DELETE_ROW;
392
393 END PA_CONTROL_ITEMS_PKG;