[Home] [Help]
PACKAGE BODY: APPS.PA_COPY_CHANGE_DOC_PVT
Source
1 PACKAGE BODY PA_COPY_CHANGE_DOC_PVT AS
2 --$Header: PACICCDB.pls 120.6.12010000.7 2010/05/06 12:02:33 rrambati noship $
3
4 G_EXCEPTION_ERROR EXCEPTION;
5 G_EXCEPTION_UNEXPECTED_ERROR EXCEPTION;
6
7 procedure COPY_CONTROL_ITEM(
8 p_ci_id IN NUMBER
9 ,p_ci_number IN VARCHAR2
10 ,p_version_number IN NUMBER
11 ,p_version_comments IN VARCHAR2
12 ,x_ci_id OUT NOCOPY NUMBER
13 ,x_version_number OUT NOCOPY NUMBER
14
15 ,x_return_status OUT NOCOPY VARCHAR2
16 ,x_msg_count OUT NOCOPY NUMBER
17 ,x_msg_data OUT NOCOPY VARCHAR2
18 ) IS
19 l_reason NUMBER := NULL; -- mwxx VARCHAR2(30):= NULL;
20 l_class_code NUMBER := NULL; -- mwxx VARCHAR2(30):= NULL;
21 p_reason NUMBER := NULL; -- mwxx VARCHAR2(30):= NULL;
22 p_class_code NUMBER := NULL; -- mwxx VARCHAR2(30):= NULL;
23 l_msg_index_out NUMBER;
24 l_from_type_id NUMBER;
25 l_relationship_id NUMBER;
26 l_commit VARCHAR2(1) := 'N';
27 l_old_supp_yn VARCHAR2(1) := 'N';
28 copy_from_row pa_control_items%ROWTYPE;
29
30 l_action_id number := null;
31
32 x_error_msg_code varchar2(100) := NULL;
33
34 l_ci_id number := null;
35 l_ci_number pa_control_items.ci_number%type := p_ci_number;
36 l_version_comments pa_control_items.version_comments%type := p_version_comments;
37 lx_ci_id number := null;
38 x_ci_number pa_control_items.ci_number%type := null;
39 l_audt_hist_num number := 0;
40
41 x_supp_rowid varchar2(50) := null;
42 x_supp_ci_transaction_id number := null;
43
44 x_budget_vers_rowid varchar2(50) := null;
45 x_budget_vers_id number := null;
46 l_budget_vers_id number := null;
47 l_row_id rowid := null;
48 l_relationship_type VARCHAR2(30) := 'CI_INCLUDED_ITEM'; --- relationship type for included items
49 x_relationship_id number := NULL;
50
51 CURSOR c_from_item
52 is
53 SELECT * FROM pa_control_items
54 WHERE ci_id = p_ci_id;
55
56 CURSOR c_action_from
57 is
58 select * from PA_CI_ACTIONS
59 WHERE ci_id = p_ci_id;
60
61 CURSOR c_obj_id_to1
62 is
63 select object_id_to1 from pa_object_relationships
64 WHERE object_id_from1 = p_ci_id;
65
66 CURSOR c_comments_from(p_action_id number)
67 is
68 select * from PA_CI_COMMENTS
69 WHERE ci_id = p_ci_id
70 AND nvl(ci_action_id,-999)=p_action_id;
71
72 CURSOR c_comments_from1
73 is
74 select * from PA_CI_COMMENTS
75 WHERE ci_id = p_ci_id
76 AND ci_action_id is null;
77
78 CURSOR c_supp_dtls_from
79 is
80 select * from PA_CI_SUPPLIER_DETAILS
81 WHERE ci_id = p_ci_id;
82
83 CURSOR c_budget_vers_from
84 is
85 select * from PA_BUDGET_VERSIONS
86 WHERE ci_id = p_ci_id;
87
88 cursor c_vers_num
89 is
90 select max(nvl(version_number,0))
91 from PA_CONTROL_ITEMS
92 where ci_id = p_ci_id;
93
94 cursor c_old_supp
95 is
96 select 'Y'
97 from pa_ci_impact_type_usage Usg,
98 pa_control_items ci
99 where usg.ci_type_id = ci.ci_type_id
100 and ci.ci_id = p_ci_id
101 and usg.impact_type_code = 'SUPPLIER';
102
103 begin
104
105 -- Initialize the Error Stack
106 PA_DEBUG.init_err_stack('PA_COPY_CHANGE_DOC_PVT.COPY_CONTROL_ITEM');
107
108 -- Initialize the return status to success
109 x_return_status := FND_API.G_RET_STS_SUCCESS;
110 /*
111 IF p_commit = FND_API.g_true THEN
112 SAVEPOINT COPY_CONTROL_ITEM;
113 END IF;
114 */
115
116 OPEN c_from_item;
117 FETCH c_from_item INTO copy_from_row;
118 if c_from_item%NOTFOUND then
119 close c_from_item;
120 PA_UTILS.Add_Message( p_app_short_name => 'PA'
121 ,p_msg_name => 'PA_CI_NO_FROM_ITEM');
122 x_return_status := FND_API.G_RET_STS_ERROR;
123 end if;
124 close c_from_item;
125
126 PA_CONTROL_ITEMS_PKG.INSERT_ROW (
127 copy_from_row.ci_type_id
128 ,copy_from_row.summary
129 ,copy_from_row.status_code
130 ,copy_from_row.owner_id
131 ,copy_from_row.highlighted_flag
132 ,NVL(copy_from_row.progress_status_code, 'PROGRESS_STAT_ON_TRACK')
133 ,NVL(copy_from_row.progress_as_of_date,sysdate)
134 ,copy_from_row.classification_code_id
135 ,copy_from_row.reason_code_id
136 ,copy_from_row.project_id
137 -- ,sysdate
138 ,copy_from_row.last_modified_by_id
139 ,copy_from_row.object_type
140 ,copy_from_row.object_id
141 ,l_ci_number
142 ,copy_from_row.date_required
143 ,copy_from_row.date_closed
144 ,copy_from_row.closed_by_id
145 ,copy_from_row.description
146 ,copy_from_row.status_overview
147 ,copy_from_row.resolution
148 ,copy_from_row.resolution_code_id
149 ,copy_from_row.priority_code
150 ,copy_from_row.effort_level_code
151 ,nvl(copy_from_row.open_action_num,0)
152 ,copy_from_row.price
153 ,copy_from_row.price_currency_code
154 ,copy_from_row.source_type_code
155 ,copy_from_row.source_comment
156 ,copy_from_row.source_number
157 ,copy_from_row.source_date_received
158 ,copy_from_row.source_organization
159 ,copy_from_row.source_person
160
161 ,copy_from_row.attribute_category
162
163 ,copy_from_row.attribute1
164 ,copy_from_row.attribute2
165 ,copy_from_row.attribute3
166 ,copy_from_row.attribute4
167 ,copy_from_row.attribute5
168 ,copy_from_row.attribute6
169 ,copy_from_row.attribute7
170 ,copy_from_row.attribute8
171 ,copy_from_row.attribute9
172 ,copy_from_row.attribute10
173 ,copy_from_row.attribute11
174 ,copy_from_row.attribute12
175 ,copy_from_row.attribute13
176 ,copy_from_row.attribute14
177 ,copy_from_row.attribute15
178 ,copy_from_row.PCO_STATUS_CODE
179 ,copy_from_row.APPROVAL_TYPE_CODE
180 ,'N' -- locked flag
181 ,p_version_number
182 ,'Y'
183 ,l_Version_Comments
184 ,copy_from_row.Original_ci_id
185 ,p_ci_id -- source ci id
186 ,lx_ci_id
187 ,x_return_status
188 ,x_msg_count
189 ,x_msg_data
190 ,copy_from_row.orig_system_code
191 ,copy_from_row.orig_system_reference
192 ,copy_from_row.CHANGE_APPROVER --added for bug 9108474
193 );
194
195
196 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
197 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
198 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
199 RAISE G_EXCEPTION_ERROR;
200 END IF;
201
202 x_ci_id := lx_ci_id;
203
204
205 ------- copy impacts
206 if (x_return_status = FND_API.g_ret_sts_success) then
207 pa_ci_impacts_util.copy_impact(p_validate_only => 'F',
208 p_init_msg_list => 'F',
209 P_DEST_CI_ID => x_ci_id,
210 P_Source_ci_id => p_ci_id,
211 P_INCLUDE_FLAG => 'N',
212 x_return_status => x_return_status,
213 x_msg_count => x_msg_count,
214 x_msg_data => x_msg_data);
215 end if;
216
217 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
218 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
219 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
220 RAISE G_EXCEPTION_ERROR;
221 END IF;
222
223 FOR ci_obj_id_to1 IN c_obj_id_to1
224 LOOP
225
226 PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
227 p_user_id => fnd_global.user_id,
228 p_object_type_from => 'PA_CONTROL_ITEMS',
229 p_object_id_from1 => to_char(x_ci_id),
230 p_object_id_from2 => NULL,
231 p_object_id_from3 => NULL,
232 p_object_id_from4 => NULL,
233 p_object_id_from5 => NULL,
234 p_object_type_to => 'PA_CONTROL_ITEMS',
235 p_object_id_to1 => to_char(ci_obj_id_to1.object_id_to1),
236 p_object_id_to2 => NULL,
237 p_object_id_to3 => NULL,
238 p_object_id_to4 => NULL,
239 p_object_id_to5 => NULL,
240 p_relationship_type => l_relationship_type,
241 p_relationship_subtype => NULL,
242 p_lag_day => NULL,
243 p_imported_lag => NULL,
244 p_priority => NULL,
245 p_pm_product_code => NULL,
246 x_object_relationship_id => x_relationship_id,
247 x_return_status => x_return_status);
248
249 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
250 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
251 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
252 RAISE G_EXCEPTION_ERROR;
253 END IF;
254
255 END LOOP;
256
257
258 --Copying document attachments
259 pa_ci_doc_attach_pkg.copy_attachments(
260 p_init_msg_list => 'F',
261 p_validate_only => 'F',
262 p_from_ci_id => p_ci_id,
263 p_to_ci_id => x_ci_id,
264 x_return_status => x_return_status,
265 x_msg_count => x_msg_count,
266 x_msg_data => x_msg_data);
267
268 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
269 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
270 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
271 RAISE G_EXCEPTION_ERROR;
272 END IF;
273
274 --Copying related items
275 pa_control_items_pvt.copy_related_items(
276 p_init_msg_list => 'F',
277 p_validate_only => 'F',
278 p_from_ci_id => p_ci_id,
279 p_to_ci_id => x_ci_id,
280 x_return_status => x_return_status,
281 x_msg_count => x_msg_count,
282 x_msg_data => x_msg_data);
283
284 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
285 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
286 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
287 RAISE G_EXCEPTION_ERROR;
288 END IF;
289
290 FOR ci_actions_from IN c_action_from
291 LOOP
292
293 PA_CI_ACTIONS_PKG.INSERT_ROW(
294 P_CI_ACTION_ID => l_action_id,
295 P_CI_ID => x_CI_ID,
296 P_CI_ACTION_NUMBER => ci_actions_from.ci_action_number,
297 P_STATUS_CODE => ci_actions_from.STATUS_CODE,
298 P_TYPE_CODE => ci_actions_from.TYPE_CODE,
299 P_ASSIGNED_TO => ci_actions_from.ASSIGNED_TO,
300 P_DATE_REQUIRED => ci_actions_from.DATE_REQUIRED,
301 P_SIGN_OFF_REQUIRED_FLAG => ci_actions_from.SIGN_OFF_REQUIRED_FLAG,
302 P_DATE_CLOSED => ci_actions_from.DATE_CLOSED,
303 P_SIGN_OFF_FLAG => ci_actions_from.SIGN_OFF_FLAG,
304 P_SOURCE_CI_ACTION_ID => ci_actions_from.SOURCE_CI_ACTION_ID,
305 P_LAST_UPDATED_BY => fnd_global.user_id,
306 P_CREATED_BY => fnd_global.user_id,
307 P_CREATION_DATE => sysdate,
308 P_LAST_UPDATE_DATE => sysdate,
309 P_LAST_UPDATE_LOGIN => fnd_global.user_id,
310 P_RECORD_VERSION_NUMBER => ci_actions_from.RECORD_VERSION_NUMBER);
311
312 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
313 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
314 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
315 RAISE G_EXCEPTION_ERROR;
316 END IF;
317
318 FOR ci_comments_from IN c_comments_from(ci_actions_from.CI_ACTION_ID)
319 LOOP
320
321 PA_CI_COMMENTS_PKG.INSERT_ROW (
322 P_CI_COMMENT_ID => ci_comments_from.CI_COMMENT_ID,
323 P_CI_ID => x_ci_id,
324 P_TYPE_CODE => ci_comments_from.TYPE_CODE,
325 P_COMMENT_TEXT => ci_comments_from.COMMENT_TEXT,
326 P_LAST_UPDATED_BY => fnd_global.user_id,
327 P_CREATED_BY => fnd_global.user_id,
328 P_CREATION_DATE => trunc(sysdate),
329 P_LAST_UPDATE_DATE => trunc(sysdate),
330 P_LAST_UPDATE_LOGIN => fnd_global.user_id,
331 P_CI_ACTION_ID => l_action_id);
332
333 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
334 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
335 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
336 RAISE G_EXCEPTION_ERROR;
337 END IF;
338
339 END LOOP;
340
341 END LOOP;
342
343
344 FOR ci_comments_from IN c_comments_from1
345 LOOP
346
347 PA_CI_COMMENTS_PKG.INSERT_ROW (
348 P_CI_COMMENT_ID => ci_comments_from.CI_COMMENT_ID,
349 P_CI_ID => x_ci_id,
350 P_TYPE_CODE => ci_comments_from.TYPE_CODE,
351 P_COMMENT_TEXT => ci_comments_from.COMMENT_TEXT,
352 P_LAST_UPDATED_BY => fnd_global.user_id,
353 P_CREATED_BY => fnd_global.user_id,
354 P_CREATION_DATE => trunc(sysdate),
355 P_LAST_UPDATE_DATE => trunc(sysdate),
356 P_LAST_UPDATE_LOGIN => fnd_global.user_id,
357 P_CI_ACTION_ID => null);
358
359 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
360 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
361 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
362 RAISE G_EXCEPTION_ERROR;
363 END IF;
364
365 END LOOP;
366
367 l_old_supp_yn := 'N';
368 open c_old_supp;
369 fetch c_old_supp into l_old_supp_yn;
370 close c_old_supp;
371
372 IF( l_old_supp_yn = 'Y') THEN
373
374 FOR ci_supp_dtls_from IN c_supp_dtls_from
375 LOOP
376
377 PA_CI_SUPPLIER_PKG.insert_row (
378 x_rowid => x_supp_rowid
379 ,x_ci_transaction_id => x_supp_ci_transaction_id
380 ,p_CI_TYPE_ID => ci_supp_dtls_from.ci_type_id
381 ,p_CI_ID => x_ci_id
382 ,p_CI_IMPACT_ID => ci_supp_dtls_from.ci_impact_id
383 ,p_VENDOR_ID => ci_supp_dtls_from.vendor_id
384 ,p_PO_HEADER_ID => ci_supp_dtls_from.po_header_id
385 ,p_PO_LINE_ID => ci_supp_dtls_from.po_line_id
386 ,p_ADJUSTED_TRANSACTION_ID => ci_supp_dtls_from.ADJUSTED_CI_TRANSACTION_ID
387 ,p_CURRENCY_CODE => ci_supp_dtls_from.CURRENCY_CODE
388 ,p_CHANGE_AMOUNT => ci_supp_dtls_from.CHANGE_AMOUNT
389 ,p_CHANGE_TYPE => ci_supp_dtls_from.CHANGE_TYPE
390 ,p_CHANGE_DESCRIPTION => ci_supp_dtls_from.CHANGE_DESCRIPTION
391 ,p_CREATED_BY => FND_GLOBAL.login_id
392 ,p_CREATION_DATE => trunc(sysdate)
393 ,p_LAST_UPDATED_BY => FND_GLOBAL.login_id
394 ,p_LAST_UPDATE_DATE => trunc(sysdate)
395 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
396 ,p_Task_Id => ci_supp_dtls_from.Task_Id
397 ,p_Resource_List_Mem_Id => ci_supp_dtls_from.Resource_List_Member_Id
398 ,p_From_Date => ci_supp_dtls_from.FROM_CHANGE_DATE
399 ,p_To_Date => ci_supp_dtls_from.TO_CHANGE_DATE
400 ,p_Estimated_Cost => ci_supp_dtls_from.Estimated_Cost
401 ,p_Quoted_Cost => ci_supp_dtls_from.Quoted_Cost
402 ,p_Negotiated_Cost => ci_supp_dtls_from.Negotiated_Cost
403 ,p_Burdened_cost => ci_supp_dtls_from.Burdened_cost
404 ,p_revenue_override_rate => ci_supp_dtls_from.revenue_override_rate
405 ,p_audit_history_number => null--nvl(ci_supp_dtls_from.audit_history_number,1)
406 ,p_current_audit_flag => 'Y'
407 ,p_Original_supp_trans_id => null
408 ,p_Source_supp_trans_id => null
409 ,p_ci_status => null
410 ,x_return_status => x_return_status
411 ,x_error_msg_code => x_error_msg_code );
412
413 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
414 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
415 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
416 RAISE G_EXCEPTION_ERROR;
417 END IF;
418
419 END LOOP;
420
421
422 END IF;
423
424
425
426 update PA_CONTROL_ITEMS
427 set Current_Version_flag = 'N'
428 where ci_id = p_ci_id;
429
430 update PA_CI_SUPPLIER_DETAILS
431 set current_audit_flag = 'N'
432 where ci_id = p_ci_id;
433
434 -- Reset the error stack when returning to the calling program
435 PA_DEBUG.Reset_Err_Stack;
436
437
438 EXCEPTION
439 WHEN G_EXCEPTION_ERROR THEN
440 ROLLBACK TO copy_change_doc;
441 x_return_status := FND_API.G_RET_STS_ERROR;
442 FND_MSG_PUB.Count_And_Get
443 (p_count => x_msg_count,
444 p_data => x_msg_data);
445 RAISE;
446
447 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
448 ROLLBACK TO copy_change_doc;
449 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
450 FND_MSG_PUB.Count_And_Get
451 (p_count => x_msg_count,
452 p_data => x_msg_data);
453 RAISE;
454
455 WHEN OTHERS THEN
456 --ROLLBACK TO copy_change_doc;
457 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
458 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_COPY_CHANGE_DOC_PVT',
459 p_procedure_name => 'COPY_CONTROL_ITEM',
460 p_error_text => SUBSTRB(SQLERRM,1,240));
461
462 fnd_msg_pub.count_and_get(p_count => x_msg_count,
463 p_data => x_msg_data);
464 RAISE;
465
466
467
468
469 END COPY_CONTROL_ITEM;
470
471 procedure copy_change_doc(
472 p_init_msg_list IN VARCHAR2 := fnd_api.g_true
473 ,p_commit IN VARCHAR2 := FND_API.g_false
474 ,p_validate_only IN VARCHAR2 := FND_API.g_true
475 ,p_ci_id IN NUMBER
476 ,p_ci_number IN VARCHAR2
477 ,p_version_comments IN VARCHAR2
478 ,x_ci_id OUT NOCOPY NUMBER
479 ,x_version_number OUT NOCOPY NUMBER
480
481 ,x_return_status OUT NOCOPY VARCHAR2
482 ,x_msg_count OUT NOCOPY NUMBER
483 ,x_msg_data OUT NOCOPY VARCHAR2
484 ) IS
485
486 API_ERROR EXCEPTION;
487
488 l_ci_id NUMBER := p_ci_id;
489
490 lx_ci_id NUMBER := null;
491 lx_version_number NUMBER := null;
492
493 l_version_number pa_control_items.version_number%type := null;
494 cursor c_vers_num
495 is
496 select max(nvl(version_number,0))
497 from PA_CONTROL_ITEMS
498 where ci_id = p_ci_id;
499
500 l_sts_yn varchar2(1) := 'N';
501 cursor c_ci_approved
502 is
503 select 'Y'
504 from pa_project_statuses
505 where status_type='CONTROL_ITEM'
506 and project_system_status_code = 'CI_APPROVED'
507 and project_status_code in (select distinct(status_code)
508 from pa_control_items
509 where ci_id =p_ci_id );
510 BEGIN
511
512 -- Initialize the Error Stack
513 PA_DEBUG.init_err_stack('PA_COPY_CHANGE_DOC_PVT.COPY_CHANGE_DOC');
514
515 x_return_status := FND_API.G_RET_STS_SUCCESS;
516
517 -- Issue API savepoint if the transaction is to be committed
518
519 --IF p_commit = FND_API.g_true THEN
520 SAVEPOINT copy_change_doc;
521 --END IF;
522
523 IF p_init_msg_list = FND_API.g_true THEN
524 fnd_msg_pub.initialize;
525 END IF;
526
527 open c_ci_approved;
528 fetch c_ci_approved into l_sts_yn;
529 close c_ci_approved;
530
531 if (l_sts_yn = 'Y') then
532 PA_UTILS.Add_Message( p_app_short_name => 'PA'
533 ,p_msg_name => 'PA_ALL_NO_UPDATE_RECORD');
534 x_return_status := FND_API.G_RET_STS_ERROR;
535 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
536 end if;
537
538 open c_vers_num;
539 fetch c_vers_num into l_version_number;
540 if c_vers_num%NOTFOUND then
541 close c_vers_num;
542 PA_UTILS.Add_Message( p_app_short_name => 'PA'
543 ,p_msg_name => 'PA_CI_NO_VERSION_FOUND');
544 x_return_status := FND_API.G_RET_STS_ERROR;
545 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
546 end if;
547 close c_vers_num;
548
549 l_version_number := l_version_number+1;
550 COPY_CONTROL_ITEM (
551 p_ci_id => p_ci_id -- copy from this
552 ,p_ci_number => p_ci_number
553 ,p_version_number => l_version_number
554 ,p_version_comments => p_version_comments
555 ,x_ci_id => lx_ci_id
556 ,x_version_number => lx_version_number
557
558 ,x_return_status => x_return_status
559 ,x_msg_count => x_msg_count
560 ,x_msg_data => x_msg_data
561 );
562 x_ci_id := lx_ci_id;
563 x_version_number := lx_version_number;
564
565 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
566 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
567 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
568 RAISE G_EXCEPTION_ERROR;
569 END IF;
570 /*
571 IF (p_commit = FND_API.g_true and x_return_status = 'S') THEN
572 commit;
573 END IF;
574 */
575 -- Reset the error stack when returning to the calling program
576 PA_DEBUG.Reset_Err_Stack;
577
578
579
580 EXCEPTION
581 WHEN G_EXCEPTION_ERROR THEN
582 ROLLBACK TO copy_change_doc;
583 x_return_status := FND_API.G_RET_STS_ERROR;
584 FND_MSG_PUB.Count_And_Get
585 (p_count => x_msg_count,
586 p_data => x_msg_data);
587
588
589 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
590 ROLLBACK TO copy_change_doc;
591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
592 FND_MSG_PUB.Count_And_Get
593 (p_count => x_msg_count,
594 p_data => x_msg_data);
595
596
597 WHEN OTHERS THEN
598 --IF p_commit = FND_API.g_true THEN
599 ROLLBACK TO copy_change_doc;
600 --END IF;
601
602 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
603 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_COPY_CHANGE_DOC_PVT',
604 p_procedure_name => 'copy_change_doc',
605 p_error_text => SUBSTRB(SQLERRM,1,240));
606
607 fnd_msg_pub.count_and_get(p_count => x_msg_count,
608 p_data => x_msg_data);
609
610 END copy_change_doc;
611
612 procedure copy_change_doc(
613 p_init_msg_list IN VARCHAR2 := fnd_api.g_true
614 ,p_commit IN VARCHAR2 := FND_API.g_false
615 ,p_validate_only IN VARCHAR2 := FND_API.g_true
616
617 ,p_ci_id IN NUMBER
618 ,p_src_ci_id IN NUMBER
619 ,x_ci_id OUT NOCOPY NUMBER
620 ,x_version_number OUT NOCOPY NUMBER
621
622 ,x_return_status OUT NOCOPY VARCHAR2
623 ,x_msg_count OUT NOCOPY NUMBER
624 ,x_msg_data OUT NOCOPY VARCHAR2
625 ) IS
626
627 API_ERROR EXCEPTION;
628
629 l_ci_id NUMBER := p_ci_id;
630
631 l_ci_number pa_control_items.ci_number%type := null;
632 l_version_comments pa_control_items.version_comments%type := null;
633
634 l_old_supp_yn VARCHAR2(1) := 'N';
635
636 lx_ci_id NUMBER := null;
637 lx_version_number NUMBER := null;
638
639 CURSOR c_from_item
640 is
641 SELECT ci_number,version_comments
642 FROM pa_control_items
643 WHERE ci_id = p_ci_id;
644
645 l_src_ci_id NUMBER := null;
646 CURSOR c_src_item
647 is
648 SELECT ci_id
649 FROM pa_control_items
650 WHERE original_ci_id = p_ci_id;
651
652 l_version_number pa_control_items.version_number%type := null;
653 cursor c_vers_num
654 is
655 select max(nvl(version_number,0))
656 from PA_CONTROL_ITEMS
657 where ci_id = p_src_ci_id;
658
659 cursor c_old_supp
660 is
661 select 'Y'
662 from pa_ci_impact_type_usage Usg,
663 pa_control_items ci
664 where usg.ci_type_id = ci.ci_type_id
665 and ci.ci_id = p_ci_id
666 and usg.impact_type_code = 'SUPPLIER';
667
668 l_sts_yn varchar2(1) := 'N';
669 cursor c_ci_approved
670 is
671 select 'Y'
672 from pa_project_statuses
673 where status_type='CONTROL_ITEM'
674 and project_system_status_code = 'CI_APPROVED'
675 and project_status_code in (select distinct(status_code)
676 from pa_control_items
677 where ci_id =p_src_ci_id );
678
679 BEGIN
680
681 -- Initialize the Error Stack
682 PA_DEBUG.init_err_stack('PA_COPY_CHANGE_DOC_PVT.COPY_CHANGE_DOC');
683
684 x_return_status := FND_API.G_RET_STS_SUCCESS;
685
686 -- Issue API savepoint if the transaction is to be committed
687
688 -- IF p_commit = FND_API.g_true THEN
689 SAVEPOINT copy_change_doc;
690 -- END IF;
691
692 IF p_init_msg_list = FND_API.g_true THEN
693 fnd_msg_pub.initialize;
694 END IF;
695
696 -- throw error for approved change docs
697
698 IF( p_src_ci_id is null) THEN
699 PA_UTILS.Add_Message( p_app_short_name => 'PA'
700 ,p_msg_name => 'PA_SRC_CI_ID_IS_NULL');
701 x_return_status := FND_API.G_RET_STS_ERROR;
702 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
703 END IF;
704
705 open c_ci_approved;
706 fetch c_ci_approved into l_sts_yn;
707 close c_ci_approved;
708
709 if (l_sts_yn = 'Y') then
710 PA_UTILS.Add_Message( p_app_short_name => 'PA'
711 ,p_msg_name => 'PA_ALL_NO_UPDATE_RECORD');
712 x_return_status := FND_API.G_RET_STS_ERROR;
713 RAISE G_EXCEPTION_ERROR;
714 end if;
715
716 open c_vers_num;
717 fetch c_vers_num into l_version_number;
718 if c_vers_num%NOTFOUND then
719 close c_vers_num;
720 PA_UTILS.Add_Message( p_app_short_name => 'PA'
721 ,p_msg_name => 'PA_CI_NO_VERSION_FOUND');
722 x_return_status := FND_API.G_RET_STS_ERROR;
723 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
724 end if;
725 close c_vers_num;
726
727 -- set the current version to no
728 update PA_CONTROL_ITEMS
729 set Current_Version_flag = 'N'
730 where ci_id = p_src_ci_id;
731
732 update PA_CI_SUPPLIER_DETAILS
733 set current_audit_flag = 'N'
734 where ci_id = p_src_ci_id;
735
736 PA_CHNGE_DOC_POLICY_PVT.SET_CHNGE_DOC_VERS; -- sets policy to n
737
738 -- set the selected version for copy current flag to yes
739 update PA_CONTROL_ITEMS
740 set Current_Version_flag = 'Y'
741 where ci_id = p_ci_id;
742
743 PA_CHNGE_DOC_POLICY_PVT.RESET_CHNGE_DOC_VERS; -- sets policy to y
744
745 -- set the selected version for copy current flag to yes
746 PA_CHNGE_DOC_POLICY_PVT.SET_SUPP_AUDT; -- sets policy to n
747
748 update PA_CI_SUPPLIER_DETAILS sdp
749 set current_audit_flag = 'Y'
750 where sdp.ci_id = p_ci_id
751 and sdp.audit_history_number = (select max(sdc.audit_history_number)
752 from PA_CI_SUPPLIER_DETAILS sdc
753 where sdc.original_supp_trans_id = sdp.original_supp_trans_id
754 group by sdc.original_supp_trans_id);
755
756 PA_CHNGE_DOC_POLICY_PVT.RESET_SUPP_AUDT; -- sets policy to y
757
758 open c_from_item;
759 fetch c_from_item into l_ci_number,l_version_comments;
760 close c_from_item;
761
762 l_version_number := l_version_number+1;
763 COPY_CONTROL_ITEM (
764 p_ci_id => p_ci_id -- copy from this
765 ,p_ci_number => l_ci_number
766 ,p_version_number => l_version_number
767 ,p_version_comments => l_version_comments
768
769 ,x_ci_id => lx_ci_id
770 ,x_version_number => lx_version_number
771
772 ,x_return_status => x_return_status
773 ,x_msg_count => x_msg_count
774 ,x_msg_data => x_msg_data
775 );
776 x_ci_id := lx_ci_id;
777 x_version_number := lx_version_number;
778
779 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
780 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
781 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
782 RAISE G_EXCEPTION_ERROR;
783 END IF;
784
785 PA_CHNGE_DOC_POLICY_PVT.SET_CHNGE_DOC_VERS; -- sets policy to n
786 -- sets the current ci id whic got versioned above source ci id
787 update PA_CONTROL_ITEMS
788 set source_ci_id = x_ci_id
789 where ci_id = p_src_ci_id;
790
791 PA_CHNGE_DOC_POLICY_PVT.RESET_CHNGE_DOC_VERS; -- sets policy to y
792 /*
793 IF (p_commit = FND_API.g_true and x_return_status = 'S') THEN
794 commit;
795 END IF;
796 */
797 -- Reset the error stack when returning to the calling program
798 PA_DEBUG.Reset_Err_Stack;
799
800 EXCEPTION
801 WHEN G_EXCEPTION_ERROR THEN
802 ROLLBACK TO copy_change_doc;
803 x_return_status := FND_API.G_RET_STS_ERROR;
804 FND_MSG_PUB.Count_And_Get
805 (p_count => x_msg_count,
806 p_data => x_msg_data);
807
808
809 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
810 ROLLBACK TO copy_change_doc;
811 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
812 FND_MSG_PUB.Count_And_Get
813 (p_count => x_msg_count,
814 p_data => x_msg_data);
815
816
817 WHEN OTHERS THEN
818 --IF p_commit = FND_API.g_true THEN
819 ROLLBACK TO copy_change_doc;
820 --END IF;
821
822 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
823 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_COPY_CHANGE_DOC_PVT',
824 p_procedure_name => 'copy_change_doc',
825 p_error_text => SUBSTRB(SQLERRM,1,240));
826
827 fnd_msg_pub.count_and_get(p_count => x_msg_count,
828 p_data => x_msg_data);
829
830 END copy_change_doc;
831 procedure update_comments(
832 p_init_msg_list IN VARCHAR2 := fnd_api.g_true
833 ,p_commit IN VARCHAR2 := FND_API.g_false
834 ,p_validate_only IN VARCHAR2 := FND_API.g_true
835
836 ,p_ci_id IN NUMBER
837 ,p_version_comments IN VARCHAR2
838
839 ,x_return_status OUT NOCOPY VARCHAR2
840 ,x_msg_count OUT NOCOPY NUMBER
841 ,x_msg_data OUT NOCOPY VARCHAR2
842 ) IS
843
844 API_ERROR EXCEPTION;
845
846 BEGIN
847
848 -- Initialize the Error Stack
849 PA_DEBUG.init_err_stack('PA_COPY_CHANGE_DOC_PVT.update_comments');
850
851 x_return_status := FND_API.G_RET_STS_SUCCESS;
852
853 -- Issue API savepoint if the transaction is to be committed
854
855 --IF p_commit = FND_API.g_true THEN
856 SAVEPOINT update_comments;
857 --END IF;
858
859 IF p_init_msg_list = FND_API.g_true THEN
860 fnd_msg_pub.initialize;
861 END IF;
862
863 IF( p_ci_id is null) THEN
864 PA_UTILS.Add_Message( p_app_short_name => 'PA'
865 ,p_msg_name => 'PA_CI_ID_IS_NULL');
866 x_return_status := FND_API.G_RET_STS_ERROR;
867 END IF;
868
869 PA_CHNGE_DOC_POLICY_PVT.SET_CHNGE_DOC_VERS; -- sets to N end;
870
871 update PA_CONTROL_ITEMS
872 set version_comments = p_version_comments
873 where ci_id = p_ci_id;
874
875 PA_CHNGE_DOC_POLICY_PVT.RESET_CHNGE_DOC_VERS; -- sets to Y
876
877 IF (p_commit = FND_API.g_true and x_return_status = 'S') THEN
878 commit;
879 END IF;
880
881 -- Reset the error stack when returning to the calling program
882 PA_DEBUG.Reset_Err_Stack;
883
884 EXCEPTION
885 WHEN G_EXCEPTION_ERROR THEN
886 ROLLBACK TO update_comments;
887 x_return_status := FND_API.G_RET_STS_ERROR;
888 FND_MSG_PUB.Count_And_Get
889 (p_count => x_msg_count,
890 p_data => x_msg_data);
891
892
893 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
894 ROLLBACK TO update_comments;
895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
896 FND_MSG_PUB.Count_And_Get
897 (p_count => x_msg_count,
898 p_data => x_msg_data);
899
900
901 WHEN OTHERS THEN
902 --IF p_commit = FND_API.g_true THEN
903 ROLLBACK TO update_comments;
904 --END IF;
905
906 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
907 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_COPY_CHANGE_DOC_PVT',
908 p_procedure_name => 'update_comments',
909 p_error_text => SUBSTRB(SQLERRM,1,240));
910
911 fnd_msg_pub.count_and_get(p_count => x_msg_count,
912 p_data => x_msg_data);
913
914 END update_comments;
915
916
917 END PA_COPY_CHANGE_DOC_PVT;